6. Embedded QUEL for BASIC : Sample Applications : The Department-Employee Master/Detail Application
 
Share this page                  
The Department-Employee Master/Detail Application
This application that uses two database tables joined on a specific column. This typical example of a department and its employees demonstrates how to process two tables as a master and a detail.
The program scans through all the departments in a database table, in order to reduce expenses. Department information is stored in program variables. Based on certain criteria, the program updates department and employee records. The conditions for updating the data are the following:
Departments:
If a department has made less than $50,000 in sales, the department is dissolved.
Employees:
If an employee was hired since the start of 1985, the employee is terminated.
If the employee's yearly salary is more than the minimum company wage of $14,000 and the employee is not nearing retirement (over 58 years of age), the employee takes a 5% pay cut.
If the employee's department is dissolved and the employee is not terminated, the employee is moved into a state of limbo (the "toberesolved" database table, described below) to be resolved by a supervisor.
This program uses two cursors in a master/detail fashion. The first cursor is for the Department table, and the second is for the Employee table. The create statements used to create the tables are shown below. The cursors retrieve all the information in their respective tables, some of which is updated. The cursor for the Employee table also retrieves an integer date interval whose value is positive if the employee was hired after January 1, 1985.
Each row that is scanned, both from the Department table and the Employee table, is recorded into the system output file. This file serves as a log of the session and as a simplified report of the updates that were made.
Each section of code is commented for the purpose of the application and also to clarify some of the uses of the EQUEL statements. The program illustrates table creation, multi-query transactions, all cursor statements and direct updates. For purposes of brevity, error handling on data manipulation statements is simply to close down the application.
The following two create statements describe the Employee and Department database tables:
##     create dept
##          (name          = c12,       ! Department name
##           totsales      = money,     ! Total sales 
##           employees     = i2)        ! Number of employees

##     create employee
##          (name          = c20,       ! Employee name 
##           age           = i1,        ! Employee age
##           idno          = i4,        ! Unique employee id 
##           hired         = date,      ! Date of hire 
##           dept          = c10,       ! Employee department
##           salary        = money)     ! Yearly salary
10   !
     ! Program: Process_Expenses
     ! Purpose: Main entry of the application. Initialize the database,
     !          process each department, and terminate the session.
     !

     program Process_Expenses

          external byte function Init_Db

          print 'Entering application to process expenses.'
          if (Init_db = 1) then
               call Process_Depts
               call End_Db
                    print 'Successful completion of application.'
          end if

     end program ! Process_Expenses

     !
     ! Function: Init_Db
     ! Purpose:  Initialize the database. Start up the database,
     !           and abort if an error. Before processing employees,
     !           create the table for employees who lose their
     !           department, "toberesolved". Initiate the
     !           multi-statement transaction. 
     ! Returns:
     !           0 - Failed to start application.
     !           1 - Succeeded in starting application.
     !
20     ## function byte Init_Db

     ##       declare integer ing_error 
          external integer Close_Down

     ##       ingres personnel

               print 'Creating "To_Be_Resolved" table.'
     ##        create toberesolved
     ##               (name = char(20),
     ##                age = smallint,
     ##                idno = integer,
     ##                hired = date,
     ##                dept = char(10),
     ##                salary = money)

     ##       inquire_ingres (ing_error = ERRORNO)
              if (ing_error > 0) then

                    print 'Fatal error creating application table.'
                    Init_Db = 0 ! Failed

               else

     ##             begin transaction
              !
              ! Inform Ingres runtime system about error handler
              ! All errors from here on close down the application.
              !
              call IIseterr(Close_Down)
              Init_Db = 1                ! Ok

          end if

     ## end function                ! Init_Db

          !
          ! Subroutine: End_Db
          ! Purpose:      Close off the multi-statement transaction and access
          !               to the database after successful completion of the 
          !               application.
          !
30   ##   sub End_Db

     ##       end transaction
     ##       exit

     ## end sub                          ! End_Db

     !
     ! Subroutine: Process_Depts
     ! Purpose:  Scan through all the departments, processing
     !           each one. If the department has made less
     !           than $50,000 in sales, then the department
     !           is dissolved. 
     !           For each department process all the
     !           employees (they may even be moved to another
     !           database table).
     !           If an employee was terminated, then update
     !           the department's employee counter.
     !
40 ## sub Process_Depts

          ! Dept_Rec corresponds to the "dept" table
     ##   record Dept_Rec
     ##       string dname = 12
     ##         double totsales
     ##         word employees
     ##   end record
     ##   declare Dept_Rec dpt

     ##   declare integer no_rows        ! Cursor loop control
     ##   declare integer emps_term      ! Employees terminated 
          declare byte deleted_dept      ! Was the dept deleted? 
          declare string dept_format     ! Formatting value
          declare byte dept_err

          ! Minimum sales of department
     ##   declare real constant MIN_DEPT_SALES = 50000.0

          no_rows = 0

     ##   range of d is dept
     ##   declare cursor deptcsr for
     ##       retrieve (d.name, d.totsales, d.employees)
     ##       for direct update of (name, employees)
     ##   open cursor deptcsr

          while (no_rows = 0)

     ##        retrieve cursor deptcsr 
     ##             (dpt::dname, dpt::totsales, dpt::employees)

     ##        inquire_equel (no_rows = ENDQUERY)

               if (no_rows = 0) then

                   ! Did department reach minimum sales ?
                   if (dpt::totsales < MIN_DEPT_SALES) then
     ##                  delete cursor deptcsr
                         deleted_dept = 1
                         dept_format = ' -- DISSOLVED --'
                    else
                         deleted_dept = 0
                         dept_format = ''
                    end if

               ! Log what we have just done 
               print 'Department: ' + trm$(dpt::dname) + &
                    ', Total Sales: ';
               print using '$$####.##', dpt::totsales;
               print dept_format

               ! Now process each employee in the department
               call Process_Employees
                    (dpt::dname, deleted_dept, emps_term)

               ! If some employees were terminated,
               ! record this fact
               if (emps_term > 0 and deleted_dept = 0) then
     ##            replace cursor deptcsr
     ##            (employees = dpt::employees - emps_term)
               end if

          end if              ! If a row was retrieved

          next                ! Continue with cursor loop

     ##           close cursor deptcsr

     ## end sub               ! Process_Depts
     !
     ! Subroutine:   Process_Employees
     ! Purpose:      Scan through all the employees for a
     !               particular department. Based on given
     !               conditions the employee may be terminated, or
     !               given a salary reduction.
     !               1. If an employee was hired since 1985 then
     !                  the employee is terminated.
     !               2. If the employee's yearly salary is more
     !                  than the minimum company wage of $14,000
     !                  and the employee is not close to retirement
     !                  (over 58 years of age), then the employee
     !                  takes a 5% salary reduction.
     !               3. If the employee's department is dissolved
     !                  and the employee is not terminated, then
     !                  the employee is moved into the 
     !                 "toberesolved" table.
     ! Parameters:
     !               dept_name - Name of current department.
     !               deleted_dept - Is department dissolved?
     !               emps_term - Set locally to record how many
     !                           employees were terminated
     !                           for the current department.
50 ##      sub Process_Employees(string dept_name,
     ##                          byte deleted_dept,
     ##                          integer emps_term)

          ! Emp_Rec corresponds to the "employee" table
     ##   record Emp_Rec
     ##       string      ename = 20
     ##       byte        age
     ##       integer     idno
     ##       string      hired = 25
     ##       real        salary
     ##       integer     hired_since_85
     ##    end record
     ##    declare Emp_Rec erec

          ! Minimum employee salary
     ##   declare real constant MIN_EMP_SALARY = 14000.00
     ##   declare byte constant NEARLY_RETIRED = 58
     ##   declare real constant SALARY_REDUC = 0.95

     ##   declare byte no_rows             ! For cursor loop control
          declare string title             ! Formatting values
          declare string description

          no_rows = 0
          emps_term = 0                 ! Initialize how many

          !
          !Note the use of the Ingres function to find out who was hired
          !since 1985.
          !
     ##       range of e is employee
     ##       declare cursor empcsr for
     ##       retrieve (e.name, e.age, e.idno, e.hired, e.salary,
     ##            res =
                     int4(interval("days", e.hired - date("01-jan-1985"))))
     ##       where e.dept = dept_name
     ##       for direct update of (name, salary)
     ##   open cursor empcsr

          while (no_rows = 0)

     ##        retrieve cursor empcsr
     ##             (erec::ename, erec::age, erec::idno,
     ##              erec::hired, erec::salary, erec::hired_since_85)

     ## inquire_equel (no_rows = ENDQUERY)

         if (no_rows = 0) then

              ! Terminate if new employee
              if (erec::hired_since_85 > 0) then
     ##           delete cursor empcsr
                  title = 'Terminated: '
                  description = 'Reason: Since 85.'
                  emps_term = emps_term + 1

              ! Reduce salary if not nearly retired 
               else 
                  if (erec::salary > MIN_EMP_SALARY) then

                  if (erec::age < NEARLY_RETIRED) then 
     ##                    replace cursor empcsr
     ##                            (salary = salary * SALARY_REDUC)
                           title = 'Reduction: '
                           description = 'Reason: Salary.'
                  else
                           ! Do not reduce salary 
                           title = 'No Changes: '
                           description = 'Reason: Retiring.'
                  end if

                           ! Else leave employee alone
                   else
                           title = 'No Changes: '
                           description = 'Reason: Salary.'
                   end if
                end if

               ! Was employee's department dissolved ? 
                if (deleted_dept = 1) then

     ##                append to toberesolved (e.all)
     ##                where e.idno = erec::idno
     ##                 delete cursor empcsr
               end if
               ! Log the employee's information 
                     print ' ' + title; 
                     print str$(erec::idno);
                     print ', ' + trm$(erec::ename) + ', ';
                     print str$(erec::age) + ', ';
                     print using '$$####.##', erec::salary;
                     print '; ' + description 
           end if           ! If a row was retrieved

     next                   ! Continue with cursor loop

     ##        close cursor empcsr

     ## end sub           ! Process_Employees

     !
     ! Function:  Close_Down
     ! Purpose:   If an error occurs during the execution of an
     !            EQUEL statement this error handler is called.
     !            Errors are printed and the current database session
     !            is terminated. Any open transactions are implicitly
     !            losed.
     ! Parameters: 
     !            ingerr - Integer containing Ingres error number.
     !
60   ## function integer Close_Down (integer ingerr)
     ##       declare string err_text

     ##       inquire_ingres (err_text = ERRORTEXT)
     ##       exit
              print 'Closing down because of database error: '
              print err_text
              stop                ! Exit BASIC
                  Close_Down = ingerr
     ## end function              ! Close_Down