/* Copyright © 1995, 2004, Oracle Corporation.  All Rights Reserved. */
/*  ABSTRACT:
* 
*       This program demonstrates the use of a cursor to fetch and update
* 	rows in the database using the precompiled SQL and the C language.
* 
* 	In this sample, the user is prompted for the employee id and 
* 	termination date of an employee until the user asks to exit.
* 	The employee id is used to check for the employee by opening a 
* 	cursor and fetching the employee row.  If the employee is found in the 
* 	database, three tables are updated with the employee's status and
* 	termination date.  The transaction is committed and the user 
* 	is prompted for the next request. 
*/  

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

#define ID_NOT_FOUND 100
#define DEADLOCK -913
#define LOCK_CONFLICT -1003
#define UPDATE_CODE '1'
#define EXIT_CODE '8'
#define TRUE 1
#define FALSE 0

/* Work variables for program use. */
    char              input_buf[256];
    char              employee_id[6];
    SQL_DATE_VMS      job_end;

/* Declare the return status variable. */
    int	    return_status;

/* Declare 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 for fetching employee records. */
    EXEC SQL DECLARE EMPLROW CURSOR FOR
		SELECT EMPLOYEE_ID, STATUS_CODE FROM EMPLOYEES
		WHERE EMPLOYEE_ID = :employee_id;

/* Functions and procedures used by the main program */

    char main_option_input()

    {
    char    option_entry = EXIT_CODE;
    int	    i;
    int     retries = 0;
    auto int not_valid_code = 1;

    while ( not_valid_code && retries < 10 )
	{
    	for (i = 1; i <= 24; i++)
	    printf("\n");
    	printf("INACTIVE STATUS UPDATE PROGRAM\n\n");
        printf("Please enter a selection number.\n");
    	printf("1 : to update an employee's status to inactive\n");
    	printf("8 : to exit this program\n\n");
    	printf("Enter option: ");
        gets(input_buf);
        sscanf(input_buf, " %c", &option_entry);

    	switch (option_entry)
	    {
    	    case UPDATE_CODE :
    	    case EXIT_CODE :
		not_valid_code = FALSE;
		break;
    	    default :
		printf("Invalid option.  Press <RET> to continue. ");
		gets(input_buf);
		retries++;
		break;
	    }
	}
    if ( retries >= 10 )
	printf("\n\nToo many retries. Terminating program.\n\n");
    return (option_entry);
    }


    /* This function initiates a transaction and reads the database for the
    *  requested employee.
    */
    void get_employee_info(gei_emp_id,job_end_date)
    char    *gei_emp_id;
    int	    *job_end_date;

    {
    /* Declare loop counter */
    int	    i;

    /* Variables to construct the date-time string for conversion */
    char    end_date[12];
    char    end_date_time[24];

    for (i = 1; i <= 24; i++)
	printf("\n");
    printf("Please enter the employee id number ");
    printf("of the employee who is now inactive\n\n");
    printf("Employee ID: ");
    gets(input_buf);
    sscanf(input_buf, " %s", gei_emp_id);
    printf("\nPlease enter the job termination date.\n");
    printf("Use format dd-MMM-yyyy (for example 21-OCT-1986).\n\n");
    printf("Date: ");
    gets(input_buf);
    sscanf(input_buf, " %11s", &end_date);
    printf("\n");

    /* Make sure that the month is uppercase. */
    end_date[3] = toupper(end_date[3]);
    end_date[4] = toupper(end_date[4]);
    end_date[5] = toupper(end_date[5]);
    strcpy(end_date_time,end_date);
    strcat(end_date_time," 00:00:00.00");

    EXEC SQL SELECT 
	CAST(substring(:end_date_time FROM 8 FOR 4) ||
         -- Convert the month to a number.
	   (CASE SUBSTRING(:end_date_time  FROM 4 FOR 3)
                   WHEN 'JAN' THEN '01'
                   WHEN 'FEB' THEN '02'
                   WHEN 'MAR' THEN '03'
                   WHEN 'APR' THEN '04'
                   WHEN 'MAY' THEN '05'
                   WHEN 'JUN' THEN '06'
                   WHEN 'JUL' THEN '07'
                   WHEN 'AUG' THEN '08'
                   WHEN 'SEP' THEN '09'
                   WHEN 'OCT' THEN '10'
                   WHEN 'NOV' THEN '11'
                   WHEN 'DEC' THEN '12'
                END) ||
      -- Parse the day, hour, minutes, seconds.
	SUBSTRING(:end_date_time FROM 1 FOR 2)  || 
	SUBSTRING(:end_date_time FROM 13 FOR 2) ||
        SUBSTRING(:end_date_time FROM 16 FOR 2) || 
	SUBSTRING(:end_date_time FROM 19 FOR 2) ||  
	SUBSTRING(:end_date_time FROM 22 for 2)
        AS DATE VMS) into :job_end
        FROM rdb$database LIMIT TO 1 ROW;


    exec sql ROLLBACK;
    return;
    }

                      

/* This function displays a message. */
    void display_atend_message()

    {
    printf("\n\nError occurred while attempting to fetch-- ");
    printf("%s",employee_id);
    printf("\n\nfor employee ID-- ");
    printf("%s",employee_id);
    printf("\nPress <RET> to continue. ");
    gets(input_buf);
    printf("\n");

    return;
    }


/* This function displays the messages returned by SQL for unexpected 
*  error conditions. This program continues after these unexpected errors
*  and allows the user to select the exit program option on the menu.  In
*  your own program, you may prefer to stop the program run automatically.
*/
    void display_sqlget_message()

    {

    char    get_error_buffer[1024];
    long    error_msg_len;

    return_status = sql_get_error_text(get_error_buffer, 1024, &error_msg_len);
    get_error_buffer[error_msg_len] = '\0';

    printf("\n\nThis condition was not expected.\n\n");
    printf("%.*s\n", error_msg_len, get_error_buffer );
    gets(input_buf);
    printf("\n");

    return;
    }


/* This function checks to see if SQL returned a deadlock or lock conflict
*  status.  If so, it outputs a message and rolls back the transaction.
*  Otherwise, it rolls back the transaction and calls a function to fetch
*  and display an error message.
*/
    void lock_error_check()

    {
    void display_sqlget_message();

    switch (SQLCA.SQLCODE)
	{
	case DEADLOCK :
	case LOCK_CONFLICT :
	    EXEC SQL ROLLBACK;
	    printf("\n\nA lock condition has occurred.\n");
	    printf("Press <RET> to continue. ");
	    gets(input_buf);
	    printf("\n");
	    break;
	default :
	    EXEC SQL ROLLBACK;
	    display_sqlget_message();
	    break;
	}
    return;
    }


/* This function reads the database using the employee_id passed in to see if
*  a matching employee record can be found.  If one is found, a success status
*  is passed back.  Otherwise an appropriate message is displayed and a failure
*  status is returned.
*/
    employee_exists()

    {
    int	    employee_found = 1;
    char    e_id[6];
    char    status_code[2];

    void    lock_error_check();
    void    display_atend_message();
    void    display_sqlget_message();

    /* Start an SQL transaction. */
    EXEC SQL SET TRANSACTION READ WRITE;
    if (SQLCA.SQLCODE < 0)
	{
	lock_error_check();
	return(FALSE);
	}

/* Open the cursor that had been previously declared. */
    EXEC SQL OPEN EMPLROW;
    if (SQLCA.SQLCODE < 0)
	{
	lock_error_check();
	return(FALSE);
	}

/* Fetch a row from the opened cursor. */
    EXEC SQL FETCH EMPLROW INTO :e_id, :status_code;
    if (SQLCA.SQLCODE == ID_NOT_FOUND)
	{
	EXEC SQL ROLLBACK;
	display_atend_message();
	return(FALSE);
	}

    if (SQLCA.SQLCODE < 0)
	{
	EXEC SQL ROLLBACK;
	display_sqlget_message();
	return(FALSE);
	}
   
    /* Success to this point means that a row was returned by the FETCH.  This
    *  program assumes that it is the employee to be updated and checks no
    *  further.  It simply returns with the employee_found flag true.
    *  Separate objects are used for employee_id for the input parameter to
    *  this function and the host variable for the fetch so that a test could be
    *  performed if desired.
    */
    return(employee_found);
    }

    void update_database()

    {
    void    lock_error_check();

    /* Update the EMPLOYEES table. */
    EXEC SQL UPDATE EMPLOYEES
		SET STATUS_CODE = '0'
		WHERE CURRENT OF EMPLROW;
    if (SQLCA.SQLCODE < 0)
	{
	lock_error_check();
	return;
	}

    /* Update the JOB_HISTORY table. */
    EXEC SQL UPDATE JOB_HISTORY
		SET JOB_END = :job_end
		WHERE EMPLOYEE_ID = :employee_id
		AND JOB_END IS NULL;
    if (SQLCA.SQLCODE < 0)
	{
	lock_error_check();
	return;
	}

    /* Update the SALARY_HISTORY table. */
    EXEC SQL UPDATE SALARY_HISTORY
		SET SALARY_END = :job_end
		WHERE EMPLOYEE_ID = :employee_id
		AND SALARY_END IS NULL;
    if (SQLCA.SQLCODE < 0)
	{
	lock_error_check();
	return;
	}

    /* Commit the transaction. */
    EXEC SQL COMMIT;
    }


/* Main program begins here.
*  The main program loops until the user asks to exit.  The main option
*  dialog returns an option code that controls the loop.  If the user
*  requests an update, SQL checks the database for the employee id. 
*  If the employee id exists, the employee's status and termination date 
*  are updated. 
*/

main()
{

char main_option_input();
void get_employee_info();
void update_database();

while (TRUE)
    {
    if (main_option_input() == EXIT_CODE)
	exit(1);

    get_employee_info(&employee_id,&job_end);

    if (employee_exists())
	update_database();
    }

}