/* Copyright © 1995, 2001, Oracle Corporation.  All Rights Reserved. */
/*  ABSTRACT:
* 
*       This program demonstrates the creation of a report from an Oracle Rdb
* 	database using a precompiled C program.
* 
* 	In this sample, the program uses a read-only transaction to attach to
* 	the database and opens a stream file for output.  A cursor is opened
* 	using two views from the database to construct a report detail record.
* 	Rows are fetched from the database until the cursor end-of-stream is
* 	encountered. The records are formatted and written to the report file
* 	with appropriate totals, page and report breaks.  The transaction is
* 	then rolled back and the report file closed.
*
*       If the program runs successfully, it creates a report file named
*       sqlsamp.rpt in the sample directory.
*/ 

#ifdef VMS
#include <ssdef.h>
#include <starlet.h>
#endif

#include <stdio.h>
#include <time.h>
#include <string.h>
#include <stdlib.h>
#include <sql_rdb_headers.h>

#define SQL_SUCCESS 0
#define STREAM_EOF 100
#define TRUE 1
#define FALSE 0

/* File definition for the SQLSAMP.RPT file. */
    FILE    *report_file;
    
/* Declare return status variable for error handling. */
    int	    return_status;

/* Variables for main program use. */

    /* Host variables for SQL calls. */
    char    employee_id[6];
    char    last_name[15];
    char    first_name[11];
    char    job_code[5];
    char    department_code[5];
    float   salary_amount;
    /* Date string for report headers. */
    char    current_date[13];
    /* Accumulators for salary totals. */
    float   job_code_salary_amount = 0.0;
    float   dept_salary_amount = 0.0;
    float   total_salary_amount = 0.0;
    /* Variables for break processing. */
    int	    first_time_through = TRUE;
    int	    job_code_break = FALSE;
    char    last_job_code[5] = "    ";
    char    last_department_code[5] = "    ";
    /* Page and line counters. */
    int	    page = 1, line = 6;

/* Include the SQLCA. */
    EXEC SQL INCLUDE SQLCA;

/* Attach to the database at compile time. */
    EXEC SQL DECLARE ALIAS FILENAME 'personnel';

/* Declare the cursor to be used to create a record stream for the report. */
    EXEC SQL DECLARE REPORT_CURSOR CURSOR FOR
		SELECT J.EMPLOYEE_ID, J.LAST_NAME, J.FIRST_NAME, J.JOB_CODE,
		       J.DEPARTMENT_CODE, S.SALARY_AMOUNT
		FROM CURRENT_JOB J, CURRENT_SALARY S
		WHERE J.EMPLOYEE_ID = S.EMPLOYEE_ID
		ORDER BY J.DEPARTMENT_CODE, J.JOB_CODE;

/* Specify where the program will pass control when an SQL exception condition
*  occurs.
*/
    EXEC SQL WHENEVER SQLERROR GOTO ERROR_HANDLER;

/*
*  Functions used by the main program
*/

/* This function prints the page headers. */
    void page_header() 

    {
    /* Format and print the page header lines. */
    fprintf(report_file, "%25sSALARY DATA%27s%12s\n", " ", " ", current_date);
    fprintf(report_file, "%24sBY DEPARTMENTS%27sPage: %2d\n", " ", " ", page);
    line = 2;
    }


/* This function prints the job code headers. */
    void job_code_head()

    {
    /* Format and print the header lines. */
    fprintf(report_file, "\n\n%9sJob%52sSalary\n", " ", " ");
    fprintf(report_file,
	"Dept%5sCode%5sId%4sLast Name%7sFirst Name%14sAmount\n",
	" ", " ", " ", " ", " ");
    fprintf(report_file, 
	"----%5s----%4s-----%2s--------------%2s----------%9s--------------\n",
	" ", " ", " ", " ", " ");
    line += 5;
    /* Clear the job code break indicator. */
    job_code_break = FALSE;
    }


/* This function prints the job code total. */
    void job_code_foot()

    {
    void page_header();

    if (line > 57)	/* no room on current page for the total */
	{
	fprintf(report_file,"\f");
	page += 1;
	line = 1;
	page_header();
	}
    /* Format and print the job code total. */
    fprintf(report_file, "\n%73s\n", "--------------");
    fprintf(report_file, "%34s Salary Total in %5s:   $ %9.2f\n", last_job_code,
            last_department_code, job_code_salary_amount);
    line += 3;
    /* Clear the job code salary total. */
    job_code_salary_amount = 0.0;
    /* Set the job code break indicator. */
    job_code_break = TRUE;
    }

 
/* This function prints the department total. */
    void dept_code_foot()

    {
    void page_header();

    if (line > 57)	/* no room on current page for total */
	{
	fprintf(report_file,"\f");
	page += 1;
	line = 1;
	page_header();
	}
    /* Format and print the total. */
    fprintf(report_file, "\n%54s-------------------\n", " ");
    fprintf(report_file, "%26sTotal Salary for %4s:%7s$  %13.2f\n",
            " ", last_department_code, " ", dept_salary_amount);
    line += 3;
    /* Clear the department total. */
    dept_salary_amount = 0.0;
    }


/* This function does most of the real work. 
*  It checks for job_code and department_code breaks, controls most of the
*  page spacing and formats the detail lines.
*/
    void detail_line()

    {
    void job_code_foot();
    void dept_code_foot();
    void page_header();
    void job_code_head();

    /* Check for breaks but don't break for first time through. */
    if (first_time_through)
	first_time_through = FALSE;
    /* Check for department_code break. */
    else if ((strcmp(department_code,last_department_code)) != 0)
	{
	job_code_foot();                                /* job code total */
	dept_code_foot();                               /* dept code total */
	}
    /* Check for job_code break. */
    else if ((strcmp(job_code,last_job_code)) != 0)
	     {
	     job_code_foot();                            /* job code total */
	     }
    else
	;                                              /* no break */

    /* Check to see if job code total and a detail line will fit on the page. */
    if ((line > 58) ||
        ((line >= 54) && (job_code_break)))
	{
	fprintf(report_file,"\f");
	page += 1;
	line = 1;
	page_header();                           /* print page */
	job_code_head();                         /*  and job code headers */
	}
    /* Check to see if job code headers are required. */
    else if ((line < 54) && (job_code_break))
	 {
	 job_code_head();
	 }
    else
	;					/* headers not required */

    /* Format and print the detail line. */
    fprintf(report_file, "%4s%9s%10s%15s%12s%10s$%10.2f\n",
            department_code, job_code, employee_id, last_name, first_name,
            " ", salary_amount);
    line += 1;
    /* Save the job and department codes for break processing. */
    strcpy(last_job_code,job_code);
    strcpy(last_department_code,department_code);
    /* Add the salary amount to the totals */
    job_code_salary_amount += salary_amount;
    dept_salary_amount += salary_amount;
    total_salary_amount += salary_amount;
    }


/* This function prints the final total. */
    void final_foot()

    {
    fprintf(report_file, "\n\n%53s-------------------\n", " ");
    fprintf(report_file, "%25sGrand Total Salaries:%9s$  %13.2f\n",
            " ", " ", total_salary_amount);
    }


/*
*  Main program
*/

main()
{

void page_header();
void job_code_head();
void detail_line();
void job_code_foot();
void dept_code_foot();
void final_foot();

/* Start a transaction.  */
EXEC SQL SET TRANSACTION READ ONLY;

/* Initialization for the report; get date and open the report file. */

strcpy (current_date, "13-DEC-1996");

/* Create the report file. */
report_file = fopen("sqlsamp.rpt","w");
		    
/* Open the cursor to form the desired record stream. */
EXEC SQL OPEN REPORT_CURSOR;

/* Begin report by printing the initial headers. */
page_header();
job_code_head();

/* Main loop */
do
    {
    /* Fetch to get a database record. */
    EXEC SQL FETCH REPORT_CURSOR INTO 
		:employee_id, :last_name, :first_name,
		:job_code, :department_code, :salary_amount;

    /* Check return status and take appropriate action. */
    switch (SQLCA.SQLCODE)
	{
	/* If a record was returned, print a detail line */
    	case SQL_SUCCESS :
	    detail_line();
	    break;
	/* If end of stream is encountered, print the final totals. */
    	case STREAM_EOF :
	    job_code_foot();
	    dept_code_foot();
	    final_foot();
	    break;
	/* Any other status is an error condition and will be trapped by the
	   SQL error handler. */
    	default :
	    break;
	}
    }
while (SQLCA.SQLCODE == SQL_SUCCESS);

/* Close the report file. */
fclose(report_file);

/* Close the cursor. */
EXEC SQL CLOSE REPORT_CURSOR;

/* Rollback the transaction. */
EXEC SQL ROLLBACK;

exit(1);

ERROR_HANDLER:
    printf("\nAn unexpected error was encountered %d", SQLCA.SQLCODE);
    sql_signal();
}