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

#define PAGELEN 66
#define TAX 0.059

void add_cust();
void cust_info();
void add_new_cust();
void update_tranx();

EXEC SQL BEGIN DECLARE SECTION;

varchar userid[9],password[9];

EXEC SQL END DECLARE SECTION;

EXEC SQL INCLUDE sqlca;
EXEC SQL INCLUDE oraca;
EXEC ORACLE OPTION (ORACA=YES);
   
struct c_rec { /* customer structure */
   int cid;
   char fname[16];
   char lname[16];
   char addr[26];
   char city[26];
   char state[3];
   char zip[11];
   char phone[19];
}x;
  
struct p_rec { /* product structure */
   char id[20];
   char name[45];
   float price; 
   int curqty;
}y;

struct s_rec { /* supplier structure */
   char name[31];
   float rate;
   int hours;
}z;

char trash[256]; /* trash variable to collect un-needed stdin characters */
float total;     /* total for each type of product ordered */
float nettotal; /* grand total */

clearscrn() /* clears the screen when necessary */
{
   int i;

   for(i=0; i<PAGELEN; i++)
      printf("\n");
}

void sql_error(msg) /* called when an error is encountered */
char *msg;
{
   char err_msg[128];
   int buf_len, msg_len;
 
   printf("\n%s\n", msg);
   buf_len = sizeof(err_msg);
   sqlglm(err_msg, &buf_len, &msg_len); 
   printf("%.*s\n", msg_len, err_msg);
 
   EXEC SQL ROLLBACK RELEASE;
   exit(1);
}

/* converts all lowercase alpha characters to upper */
void conv_to_upper(s)
   char *s;
{
   int i;
   
   for (i=0; i<strlen(s); i++) {
      if(isalpha(s[i]) && islower(s[i]))
         s[i] -= 32;
   }
}

/* handles any service transactions and calculates total */              
void
service_info(i)
   int i;
{
   char s[20];  
   
   printf("Enter service name #%d: ", i);
   conv_to_upper(gets(s));

   EXEC SQL SET TRANSACTION READ ONLY;
   EXEC SQL DECLARE c5 CURSOR FOR
            SELECT *
            FROM services
            WHERE Name = :s;

   EXEC SQL OPEN c5;

   EXEC SQL FETCH c5 INTO :z.name, :z.rate, :z.hours; 
   if(sqlca.sqlcode != 0) { 
      EXEC SQL CLOSE c5;
      EXEC SQL COMMIT;
      printf("*** Invalid Service ***\n");
      service_info(i);
   }
   else {       
      total = z.rate * z.hours; /* calculate service total */
      EXEC SQL CLOSE c5;
      EXEC SQL COMMIT;
   }
}
void
product_info(i)
   int i;
{
   int qty;
   char p[20];  
   
   printf("Enter product # for Item #%d: ", i);
   conv_to_upper(gets(p));

   EXEC SQL DECLARE c2 CURSOR FOR
            SELECT *
            FROM products
            WHERE PID = :p;

   EXEC SQL OPEN c2;

   EXEC SQL FETCH c2 INTO :y.id, :y.name, :y.price, :y.curqty; 
   if(!strncmp(y.id, p, strlen(p))) { 
      printf("Enter Quantity for Item #%d: ", i);
      scanf("%d", &qty);
      while(qty > y.curqty) { /* loop until valid qty is entered */
         printf("Have %d of item %s in stock\n", y.curqty, p);
         printf("Reenter Quantity for Item #%d: ", i);
         scanf("%d", &qty);
      }

      /* subtract qty entered from the qty available */    
      EXEC SQL UPDATE products
            SET CurQty = CurQty - :qty 
            WHERE PID = :p;

      total = y.price * qty; /* calculate the total */

      EXEC SQL CLOSE c2;
      EXEC SQL COMMIT;
   }
   else { /* if invalid product name then reenter product name, qty */
      EXEC SQL CLOSE c2;
      EXEC SQL COMMIT;
      printf("*** Invalid Product ***\n");
      product_info(i);
   }
}

/* add new customer to the customer database */
void
add_cust()
{
   char ch, reply[2];
   int i, done = 0, cid, valid=0;

   clearscrn();
   printf("******************** ADD NEW CUSTOMER ********************\n");
   printf("First Name: ");
   conv_to_upper(gets(x.fname));
   printf("Last Name: ");
   conv_to_upper(gets(x.lname));
   printf("Street: ");
   conv_to_upper(gets(x.addr));
   printf("City: ");
   conv_to_upper(gets(x.city));
   printf("State (KS): ");
   conv_to_upper(gets(x.state));
   printf("Zip: ");
   gets(x.zip);
   clearscrn();

/* add white space to strings in order to "force-align" menus */
   for(i=0; i<15; i++) {
      if(x.fname[i]==NULL) {
         x.fname[i]=' ';
         x.fname[i+1]=NULL;
      }
      if(x.lname[i]==NULL) {
         x.lname[i]=' ';
         x.lname[i+1]=NULL;
      }
   }

   for(i=0; i<25; i++) {
      if(x.addr[i]==NULL) {
         x.addr[i]=' ';
         x.addr[i+1]=NULL;
      }
      if(x.city[i]==NULL) {
         x.city[i]=' ';
         x.city[i+1]=NULL;
      }
   }

   for(i=0; i<2; i++) {
      if(x.state[i]==NULL) {
         x.state[i]=' ';
         x.state[i+1]=NULL;
      }
   }
   
   for(i=0; i<10; i++) {
      if(x.zip[i]==NULL) {
         x.zip[i]=' ';
         x.zip[i+1]=NULL;
      }
   }

   for(i=0; i<18; i++) {
      if(x.phone[i]==NULL) {
         x.phone[i]=' ';
         x.phone[i+1]=NULL;
      }
   }


   display_cust_info();
   while(!valid) { /* do until a y/n is entered */
      printf("\nIs ALL customer information correct (y/n)? ");
      conv_to_upper(gets(reply));
      ch = *reply;
 
      switch(ch) 
      {
         case 'Y':
            valid=1;
            break;

         case 'N':
            valid=1;
            add_cust();
            break;
      
         default:
            printf("Invalid Choice\n");
      }
   }

   EXEC SQL DECLARE c4 CURSOR FOR
 	    SELECT CID
  	    FROM customers;

   EXEC SQL OPEN c4;
   EXEC SQL FETCH c4 INTO :cid;
   while(sqlca.sqlcode == 0)
      EXEC SQL FETCH c4 INTO :cid;

   cid++; /* assigns the next customer id */
   x.cid = cid;
   /* insert customer into the customer table */
   EXEC SQL INSERT INTO customers 
        VALUES(:x.cid,:x.fname,:x.lname,:x.addr,:x.city,:x.state,:x.zip,:x.phone);
 
   EXEC SQL CLOSE c4;
   EXEC SQL COMMIT;
}

/* searches for a customer using the phone number as a key */
void
cust_info()
{
   int choice, done = 0;
   char ch, buf[2], phone[19], tmp[19], *charptr;

   printf("Enter Customer Phone # (XXX-XXX-XXXX): ");
   gets(x.phone);
   strcpy(phone, x.phone);
   strcat(phone, "%");

   EXEC SQL DECLARE c1 CURSOR FOR
 	    SELECT *
  	    FROM customers
            WHERE Phone LIKE :phone;

   EXEC SQL OPEN c1;
   EXEC SQL FETCH c1 INTO :x.cid,:x.fname, :x.lname, :x.addr, :x.city, :x.state, 					:x.zip, :x.phone; 
   while(!done) { /* loop until the correct customer is found */
      if(sqlca.sqlcode == 0) {
         display_cust_info();
         printf("Correct Customer (y/n)? ");
         conv_to_upper(gets(buf));
         ch = *buf;
 
         switch(ch) 
         {
            case 'Y':
               done=1;
               break;
  
            case 'N':
               EXEC SQL FETCH c1 INTO :x.cid,:x.fname, :x.lname, :x.addr, 
		:x.city,:x.state, :x.zip, :x.phone; 
               break;
        
            default:
               printf("Invalid Choice:  Correct Customer (y/n)? ");
         }
      }
      else {
         while(!done) {
         /* remove white space from "x.phone" variable */
            strcpy(tmp, x.phone);
            charptr = (char*)strchr(tmp, ' ');
            *charptr = NULL;
            printf("Is %s the correct phone number? ", tmp);
            conv_to_upper(gets(buf));
            ch = *buf;
 
            switch(ch) 
            {
               case 'Y':
                  done=1;
                  add_cust();
                  break;
  
               case 'N':
                  done=1;
                  cust_info();
                  break;
        
               default:
                  printf("Invalid Choice\n");
            }
         }
      }
   }
   
   if(sqlca.sqlcode != 0) 
      add_cust();

   EXEC SQL CLOSE c1;
   EXEC SQL COMMIT;
}
  
/* print customer data to the screen */ 
display_cust_info()
{
printf("*****************************************************************************\n");
printf("*                             Customer Information                          *\n");
printf("*****************************************************************************\n");
printf("* [1] Last Name: %s     [2] First Name: %s        *\n", x.lname, x.fname);
printf("* [3] Address: %s				    *\n", x.addr);
printf("* [4] City: %s[5] State: %s      	 	    *\n", x.city, x.state);
printf("* [6] Phone: %s      [7] Zip: %s  	            *\n", x.phone, x.zip);
printf("*****************************************************************************\n");
}

/* add any purchase or service done into the transaction table */
void   
update_tranx()
{
   time_t now; /* used for date */
   struct tm *ptr; /* used for date */
   char reply[2], buf1[80], str[80], ch;
   int tranx, valid=0;
   float amtpaid;

/* get date and format to match transactions table */
   time(&now);
   ptr = localtime(&now);
   strftime(buf1, 80, "%d-%b-%y", ptr);

/* find the last transaction number in the transaction table */
   EXEC SQL DECLARE c3 CURSOR FOR
            SELECT transaction# FROM transactions;
   EXEC SQL OPEN c3;
   EXEC SQL FETCH c3 INTO :tranx; 
   while(sqlca.sqlcode == 0)
      EXEC SQL FETCH c3 INTO :tranx; 
   
   tranx++; /* set new transaction as the next transaction number */
   while(!valid) { /* insert tranx into transaction table w/ payment */
      printf("Enter Method of Payment ([V]isa, [M]astercard, [C]ash, c[H]eck): ");
      conv_to_upper(gets(reply));
      ch = *reply;

      switch(ch) 
      {
         case 'V':
         case 'M':
            valid=1;
            EXEC SQL INSERT INTO transactions
               VALUES (:tranx, :x.cid, :nettotal, 'CREDIT', :buf1);
            break;

         case 'C':
            valid=1;
            EXEC SQL INSERT INTO transactions(Transaction#, CID, "Total Cost", Payment_type, Transdate)
               VALUES (:tranx, :x.cid, :nettotal, 'CASH', :buf1);
            printf("Amount Tendered: ");
            scanf("%f", &amtpaid);
            printf("Change Due: %.2f\n", (amtpaid - nettotal)); 
            printf("Press <ENTER> to Continue");
            gets(trash);
            gets(trash);
            break;

         case 'H':
            valid=1;
            EXEC SQL INSERT INTO transactions
               VALUES (:tranx, :x.cid, :nettotal, 'CHECK', :buf1);
            break;

         default:
            printf("Invalid\n");
      }
   }

   printf("trans = %d, cid = %d, total = %.2f, type = %c, date = %s\n", tranx,x.cid,nettotal, ch, str);
   EXEC SQL CLOSE c3;
   EXEC SQL COMMIT;
}

/* retrieve and format customer info for ticket display feature */
void
getcust(cid)
   int cid;
{
   EXEC SQL SET TRANSACTION READ ONLY;
   EXEC SQL DECLARE c7 CURSOR FOR
 	    SELECT *
  	    FROM customers
            WHERE CID = :cid;

   EXEC SQL OPEN c7;
   EXEC SQL FETCH c7 INTO :x.cid,:x.fname, :x.lname, :x.addr, :x.city, :x.state, 					:x.zip, :x.phone; 

printf("*  Last Name: %s      First Name: %s      *\n", x.lname, x.fname);
printf("*  Address: %s				    *\n", x.addr);
printf("*  City: %s State: %s      	 	    *\n", x.city, x.state);
printf("*  Phone: %s       Zip: %s  	            *\n", x.phone, x.zip);
 
   EXEC SQL CLOSE c7;
   EXEC SQL COMMIT;
}

/* display the current day's tickets */
void
displaytickets()
{
   time_t now; /* used for system date */
   struct tm *ptr; /* used for system date */
   char curdate[80]; /* used for system date */

struct t_rec {  /* transaction structure */
   int tranx;
   int cid;
   float tcost;
   char ptype[21];
   char date[11];
}t;

/* set date */
   time(&now);
   ptr = localtime(&now);
   strftime(curdate, 80, "%d-%b-%y", ptr);

/* retrieve all tickets with the current date and order by tranx# */
   EXEC SQL SET TRANSACTION READ ONLY;
   EXEC SQL DECLARE c6 CURSOR FOR
 	    SELECT *
  	    FROM transactions 
            WHERE Transdate = :curdate
            ORDER BY Transaction#;

   EXEC SQL OPEN c6;
   EXEC SQL FETCH c6 INTO :t.tranx,:t.cid, :t.tcost, :t.ptype, :t.date;
   while(sqlca.sqlcode == 0) {
printf("*********************************************************************\n");
printf("*  Date: %s                Ticket #%d                       *\n",t.date, t.tranx);
printf("*                                                                   *\n");           
      getcust(t.cid);  /* get and print customer data to screen */
printf("*                                                                   *\n");           
printf("*  Ticket Total: %.2f           Payment Type: %s*\n",t.tcost, t.ptype);
   
printf("*********************************************************************\n");
      printf("\nPress <ENTER> to Continue");
      gets(trash);
      EXEC SQL FETCH c6 INTO :t.tranx,:t.cid, :t.tcost, :t.ptype, :t.date;
   }
   EXEC SQL CLOSE c6;
   EXEC SQL COMMIT;
} 

/* main function for retrieving and displaying cashier info */
int
cashier()
{
   int choice, i, finished=0, done=0, valid=0;
   float salestax;
   char reply[2], ch, ch1, ch2;

   nettotal=total=0;

   /* initialize structures */
   x.cid=y.price=y.curqty=z.hours=z.rate=0; 
   *x.fname=*x.lname=*x.addr=*x.city=*x.state=*x.zip=*x.phone=NULL;
   *y.id=*y.name=*z.name=NULL;
  
   printf("+--------------------------+\n");
   printf("|   1] Cashier Screen      |\n");
   printf("|   2] Today's Tickets     |\n");
   printf("|   0] Exit                |\n");
   printf("+--------------------------+\n\n");
   printf("Enter Selection: ");
   while(!finished) {
      scanf("%d", &choice);
      gets(trash);
 
      switch(choice)   
      {
         case 0:
            clearscrn();
            exit(0);

         case 1: 
            finished=1;
            clearscrn(); /* clear screen */
            cust_info();
            printf("Any products to enter on this ticket (y/n)? ");
            while(!valid) {
               conv_to_upper(gets(reply));
               ch1 = *reply;
               switch(ch1) {
                  case 'Y': 
                     for(i=1; !done; i++) { /* products purchased */
                        product_info(i);
                        nettotal = nettotal + total;
                        total = 0;
                        gets(trash);
                        printf("Have all products been entered (y/n)? ");
                        conv_to_upper(gets(reply));
                        ch = *reply;
                        if(ch == 'Y') 
                           done = 1;
                     }
                     valid=1;
                     break;
                  case 'N':
                     valid=1;
                     break;

                  default:
                     printf("invalid choice -- reenter: ");
               }
            }
            
            done=valid=0;
            printf("Any services to enter on this ticket (y/n)? ");
            while(!valid) {
               conv_to_upper(gets(reply));
               ch2 = *reply;
               switch(ch2) {
                  case 'Y':
                     for(i=1; !done; i++) { /* auto services rendered */
                        service_info(i);
                        nettotal = nettotal + total;
                        total = 0;
                        printf("Have all services been entered (y/n)? ");
                        conv_to_upper(gets(reply));
                        ch = *reply;
                        if(ch == 'Y') 
                           done = 1;
                     }
                     valid=1;
                     break;
                  case 'N':
                     valid=1;
                     break;

                  default:
                     printf("invalid choice -- reenter: ");
               }
            }
            clearscrn();
            printf(" Subtotal: $%.2f\n", nettotal);
            salestax = nettotal * TAX;
            printf("Sales Tax: $%.2f\n", salestax);
            nettotal = nettotal + salestax;
            printf("    Total: $%.2f\n", nettotal);
            if(nettotal==0) 
               clearscrn();
            else 
               update_tranx();
            break;

         case 2:
            displaytickets();
            finished=1;
            break;

         default:
            printf("Invalid option -- Reenter Selection: ");
      }
   }
}

/* connects to oracle and calls the "cashier" function */
main()
{
   strcpy(userid.arr, "oradum11");
   userid.len = strlen(userid.arr);
   strcpy(password.arr, "oradum11");
   password.len = strlen(password.arr);

/* Register sql_error() as the error handler. */
   EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--\n"); 
 
   EXEC SQL CONNECT :userid IDENTIFIED BY :password;

   while(1) {
      cashier();
      clearscrn();
   }
}