program Departments( input, output );

exec sql include sqlca;

{The department table}
exec sql declare dept table
     (name          char(12)   not null,  {Department name}
      totsales      money      not null,  {Total sales}
      employees     smallint   not null); {Number of employees}

{The employee table}
exec sql declare employee table
     (name          char(20)   not null,  {Employee name}
      age           integer1   not null,  {Employee age}
      idno          integer1   not null,  {Unique employee id}
      hired         date       not null,  {Date of hire}
      dept          char(12)   not null,  {Department of work}
      salary        money      not null); {Yearly salary}

{"State-of-Limbo" for employees who lose their department}
exec sql declare toberesolved table
     (name          char(20)   not null,  {Employee name}
      age           integer1   not null,  {Employee age}
      idno          integer1   not null,  {Unique employee id}
      hired         date       not null,  {Date of hire}
      dept          char(12)   not null,  {Department of work}
      salary        money      not null); {Yearly salary}

label
      exit_program;
exec sql begin declare section;
type
     String12 = varying[12] of Char;
     String20 = varying[20] of Char;
     String25 = varying[25] of Char;
     String200 = varying[200] of Char;
     Short_Short_Integer = [byte] -128 .. 127;
     Short_Integer = [word] -32768 .. 32767;
exec sql end declare section;

{
| Procedure: Process_Expenses (MAIN)
| Purpose:   Main body of the application. Initialize the database,
|            process each department, and terminate the session.
| Parameters:
|            None
}

procedure Process_Expenses;
type
    File_type = Text;
var
    log_file: File_type; {Log file to which to write.}

    {
    | Procedure:  Init_Db
    | Purpose:    Initialize the database.
    |             Connect to the database and abort on error.
    |             Before processing departments and employees,
    |             create the table for employees who
    |             lose their department, "toberesolved".
    | Parameters: None
    }

procedure Init_Db;
begin
        exec sql whenever sqlerror stop;
        exec sql connect personnel;

        {Create the table.}
         writeln(log_file,
                 'Creating ''To_Be_Resolved'' table.');
         exec sql create table toberesolved
                 (name    char(20) not null,
                        age     integer1 not null,
                        idno    integer  not null,
                        hired   date not null,
                        dept    char(12) not null,
                        salary  money not null);
end; {Init_Db}

{
| Procedure: End_Db
| Purpose:    Commit the multi-statement transaction and
|             end access to the database.
| Parameters: None
}

procedure End_Db;
begin
    exec sql commit;
    exec sql disconnect;
end; {End_Db}

{
| Procedure:  Close_Down
| Purpose:    Error handler called any time after Init_Db has been
|             successfully completed. In all cases, print the
|             cause of the error and abort the transaction,
|             backing out changes. Note that disconnecting
|             from the database will implicitly close any
|             open cursors.
| Parameters: None.
}

procedure Close_Down;
        exec sql begin declare section;
        var
                errbuf: String200;
        exec sql end declare section;
begin
        {Turn off error handling here}
        exec sql whenever sqlerror continue;

        exec sql inquire_sql (:errbuf = ERRORTEXT);
        writeln( 'Closing Down because of database error.' );
        writeln( errbuf );

        exec sql rollback;
        exec sql disconnect;

        goto exit_program;             {no return}
end; {Close_Down}

{
| Procedure: Process_Employees
| Purpose:   Scan through all the employees for a
|            particular department. Based on given
|            conditions, the employee may be terminated or
|            take a salary reduction.
|            1. If an employee was hired since 1985,
|               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), the employee
|               takes a 5% salary reduction.
|            3. If the employee's department is dissolved
|               and the employee is not terminated,
|               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.
}

procedure Process_Employees
        (dept_name:     Varying[ub] of Char;
         deleted_dept:     Boolean;
         var emps_term: Integer);

    label
        Close_Emp_Csr;
    exec sql begin declare section;
    const
        salary_reduc = 0.95;
    type
        {Emp_Rec corresponds to the "employee" table}
         Emp_Rec = record
            name:       String20;
            age:        Short_Short_Integer;
            idno:       Integer;
            hired:      String25;
            salary:     Real;
            hired_since_85: Integer;
        end;
    var
        erec:  Emp_Rec;
            dname: String12;
    exec sql end declare section;

    const
        min_emp_salary = 14000.00;
        nearly_retired = 58;
    var
        title: String12; {Formatting values}
        descript: String25;

    {
    | Note the use of the INGRES function to find out
    | who has been hired since 1985
    }
    exec sql declare empcsr cursor for
        select name, age, idno, hired, salary,
            int4(interval('days',
                hired-date('01-jan-1985')))
        from employee
        where dept = :dname
        for direct update of name, salary;

begin {Process_Employees}
    dname := dept_name;

    {
    | All errors from this point on close down
    | the application
    }
    exec sql whenever sqlerror call Close_Down;
    exec sql whenever not found goto Close_Emp_Csr;

    exec sql open empcsr;

    emps_term := 0; {Record how many}
    while (sqlca.sqlcode = 0) do
    begin
        exec sql fetch empcsr into :erec;

        if (erec.hired_since_85 > 0) then
            begin
                exec sql delete from employee
                    where current of empcsr;
                title := 'Terminated: ';
                descript := 'Reason: Hired since 1985.';
                emps_term := emps_term + 1;
        end else if (erec.salary > min_emp_salary) then
        begin {Will reduce salary if not nearly retired}
            if (erec.age < nearly_retired) then
            begin
                exec sql update employee
                    set salary =
                        salary * :salary_reduc
                    where current of empcsr;
                title := 'Reduction: ';
                descript := 'Reason: Salary. ';
            end else
            begin
                {Do not reduce salary}
                title := 'No Changes: ';
                descript := 'Reason: Retiring. ';
            end;
        end else {Else leave employee as is}
        begin
            title := 'No Changes: ';
            descript := 'Reason: Salary. ';
        end;

        {Was employee's department dissolved?}
        if (deleted_dept) then
        begin
            exec sql insert into toberesolved
                select *
                from employee
                where idno = :erec.idno;

            exec sql delete from employee
                where current OF empcsr;
        end;

        {Log the employee's information}
        write(log_file, ' ', title, ' ');
        write(log_file, erec.idno:6);
        write(log_file, ', ', erec.name, ', ');
        write(log_file, erec.age:3);
        write(log_file, ', ');
        write(log_file, erec.salary:8:2);
        writeln(log_file, ' ; ', descript);
    end;

Close_Emp_Csr:
    exec sql whenever not found continue;
    exec sql close empcsr;
end;

{
| Procedure: 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.
| Parameters: None
}


procedure Process_Depts;
    exec sql begin declare section;
    type
        {Dept_Rec corresponds to the "dept" table}
         Dept_Rec = record
                name: String12;
                totsales: Double;
                employees: Short_Integer;
        end;
    var
        dept: Dept_Rec;
        emps_term: Integer; {Employees terminated}
    exec sql end declare section;

    label
        Close_Dept_Csr;
    const
        min_tot_sales = 50000.00;
    var
        deleted_dept: Boolean; {Was the dept deleted?}
        dept_format: String20; {Formatting value}
        exec sql declare deptcsr cursor for
             select name, totsales, employees
             from dept
             for direct update of name, employees;
    begin {Process_Depts}
        emps_term := 0;

        {All errors from this point on close down the application}
        exec sql whenever sqlerror call Close_Down;
        exec sql whenever not found goto Close_Dept_Csr;

        exec sql open deptcsr;

        while (sqlca.sqlcode = 0) do
        begin
            exec sql fetch deptcsr into :dept;

            {Did the department reach minimum sales?}
            if (dept.totsales \ min_tot_sales) then
            begin
                exec sql delete from dept
                    where current of deptcsr;
                deleted_dept := TRUE;
                dept_format := ' -- DISSOLVED --';
            end else
            begin
                deleted_dept := FALSE;
                dept_format := ' ';
            end;

            {Log what we have just done}
            write(log_file,
                'Department: ', dept.name, ', Total Sales: ');
            write(log_file, dept.totsales:12:3);
            writeln(log_file, dept_format);

            {Now process each employee in the department}
            Process_Employees(dept.name,
                deleted_dept, emps_term);

            {If employees were terminated, record this fact}
            if ((emps_term > 0) and (not deleted_dept)) then
            begin
                exec sql update dept
                    set employees = :dept.employees - :emps_term
                    where current of deptcsr;
                end;
            end;

Close_Dept_Csr:
        exec sql whenever not found continue;
        exec sql close deptcsr;
end;                        {Process_Depts}

begin                     {Process_Expenses}
        writeln('Entering application to process expenses.');
        open(file_variable := log_file, file_name := 'expenses.log');
        rewrite( log_file );
        Init_Db;
        Process_Depts;
        End_Db;
        close(log_file);
        writeln('Completion of application.');
end;                     {Process_Expenses}

begin                     {MAIN program}
        Process_Expenses;
exit_program:;
 end. {MAIN}