/****************************************************************/
/* Mail Order Application Program (Section 3.6)                 */
/* Chapter 3; Oracle Programming -- A Primer                    */
/*            by R. Sunderraman                                 */
/****************************************************************/

#include <stdio.h>
#include <string.h>
#define TRUE 1
#define FALSE 0

typedef struct {
  int cno; varchar cname[31]; varchar street[31]; 
  int zip; char phone[13];
} customer_record;
typedef struct {
  short cno_ind,cname_ind,street_ind,zip_ind,phone_ind;
} customer_indicator_record;
typedef struct {
  int zip; varchar city[31];
} zipcode_record;
typedef struct {
  int eno; varchar ename[31]; int zip; char hdate[12];
} employee_record;
typedef struct {
  short eno_ind,ename_ind,zip_ind,hdate_ind;
} employee_indicator_record;
typedef struct {
  int ono,cno,eno; char received[12],shipped[12];
} order_record;
typedef struct {
  short ono_ind,cno_ind,eno_ind,received_ind,shipped_ind;
} order_indicator_record;

EXEC SQL INCLUDE sqlca;

void print_menu();
void add_customer();
void print_customers();
void update_customer();
void process_order();
void remove_customer();
void delete_old_orders();
void print_invoice();
void prompt(char [],char []);

void main() {
  EXEC SQL BEGIN DECLARE SECTION;
    varchar userid[10], password[15];
  EXEC SQL END DECLARE SECTION;
  char ch;
  int  done=FALSE,loginok=FALSE,logintries=0;

  do {
    prompt("Enter your USERID: ",userid.arr);
    userid.len = strlen(userid.arr);
    printf("Enter your PASSWORD: ");
    system("stty -echo");
    scanf("%s", password.arr);getchar();
    password.len = strlen(password.arr); 
    system("stty echo");
    printf("\n");
    EXEC SQL CONNECT :userid IDENTIFIED BY :password;
    if (sqlca.sqlcode == 0) 
      loginok = TRUE;
    else 
      printf("Connect Failed\n");
    logintries++;
  } while ((!loginok) && (logintries <3));
  if ((logintries == 3) && (!loginok)) {
    printf("Too many tries at signing on!\n");
    exit(0);      
  }

  while (done == FALSE) {
    print_menu();
    printf("Type in your option: ");
    scanf("%s",&ch); getchar();
    switch (ch) {
      case '1': add_customer(); printf("\n"); break;
      case '2': print_customers(); printf("\n"); break;
      case '3': update_customer(); printf("\n"); break;
      case '4': process_order(); printf("\n"); break;
      case '5': remove_customer(); printf("\n"); break;
      case '6': delete_old_orders(); printf("\n"); break;
      case '7': print_invoice();
                printf("\nPress RETURN to continue"); 
                getchar(); printf("\n"); break;
      case 'q': case 'Q': done = TRUE; break;
      default:  printf("Type in option again\n"); break;
    }
  };            
  EXEC SQL COMMIT RELEASE;
  exit(0);
}

void print_menu() {
  printf("************************************************\n");
  printf("<1> Add a new customer\n");
  printf("<2> Print all customers\n");
  printf("<3> Update customer information\n");
  printf("<4> Process a new order\n");
  printf("<5> Remove a customer\n");
  printf("<6> Delete orders shipped more than 5 year ago\n");
  printf("<7> Print invoice for a given order\n");
  printf("<q> Quit\n");
  printf("***********************************************\n");
}

void add_customer() {
  EXEC SQL BEGIN DECLARE SECTION;
    customer_record crec;
    zipcode_record  zrec;
  EXEC SQL END DECLARE SECTION;

  prompt("Customer Name: ",crec.cname.arr);
  crec.cname.len = strlen(crec.cname.arr);
  prompt("Street       : ",crec.street.arr);
  crec.street.len = strlen(crec.street.arr);
  printf("Zip Code     : ");
  scanf("%d",&crec.zip); getchar();
  prompt("Phone Number : ",crec.phone);

  EXEC SQL SELECT zip, city 
           INTO   :zrec 
           FROM   ZIPCODES 
           WHERE  zip = :crec.zip;
  if (sqlca.sqlcode > 0) { 
     zrec.zip = crec.zip;
     prompt("Zip Code does not exists; Please Enter City: ",zrec.city.arr);
     zrec.city.len = strlen(zrec.city.arr);
     exec sql set transaction read write;
     exec sql insert into zipcodes (zip, city) VALUES (:zrec);
     exec sql commit; 
  }      

  EXEC SQL SET TRANSACTION READ WRITE;
  EXEC SQL INSERT INTO customers VALUES 
    (custseq.nextval,:crec.cname,:crec.street,:crec.zip,
     :crec.phone); 
  if (sqlca.sqlcode < 0) {
    printf("\n\nCUSTOMER (%s) DID NOT GET ADDED\n",
           crec.cname.arr);
    EXEC SQL ROLLBACK WORK; 
    return;
  }
  EXEC SQL COMMIT;
}

void print_customers() {
  EXEC SQL BEGIN DECLARE SECTION;
    customer_record crec;
    customer_indicator_record crecind;
  EXEC SQL END DECLARE SECTION;

  EXEC SQL DECLARE customer_cur CURSOR FOR
           SELECT cno, cname, street, zip, phone
           FROM   customers;

  EXEC SQL SET TRANSACTION READ ONLY;
  EXEC SQL OPEN customer_cur;

  EXEC SQL FETCH customer_cur INTO 
           :crec INDICATOR :crecind;
  printf("\n");
  while (sqlca.sqlcode == 0) {
    crec.cname.arr[crec.cname.len] = '\0';
    crec.street.arr[crec.street.len] = '\0';
    printf("%6d  %10s  %20s  %6d  %15s\n",crec.cno,
       crec.cname.arr,crec.street.arr,crec.zip,crec.phone); 
    EXEC SQL FETCH customer_cur INTO 
             :crec INDICATOR :crecind;
  }
  EXEC SQL CLOSE customer_cur;
  EXEC SQL COMMIT;
}

void update_customer() {
  EXEC SQL BEGIN DECLARE SECTION;
    customer_record crec;
    zipcode_record zrec;
    int cnum;
    varchar st[31];
    char ph[13], zzip[6];
  EXEC SQL END DECLARE SECTION;

  printf("Customer Number to be Updated: ");
  scanf("%d",&cnum);getchar();

  EXEC SQL SELECT * 
           INTO   :crec 
           FROM   CUSTOMERS 
           WHERE  cno = :cnum;
  if (sqlca.sqlcode > 0) {
    printf("Customer (%d) does not exist\n",cnum);
    return;
  }

  crec.street.arr[crec.street.len] = '\0';
  printf("Current Street Value          : %s\n",
         crec.street.arr);
  prompt("New Street (n<ENTER> for Same): ",st.arr);
  if (strlen(st.arr) > 1) {
    strcpy(crec.street.arr,st.arr);
    crec.street.len = strlen(crec.street.arr);
  }

  printf("Current ZIP Value          : %d\n",crec.zip);
  prompt("New ZIP (n<ENTER> for same): ",zzip);
  if (strlen(zzip) > 1) {
    crec.zip = atoi(zzip);
    EXEC SQL SELECT zip, city
             INTO   :zrec
             FROM   ZIPCODES
             WHERE  zip = :crec.zip;
    if (sqlca.sqlcode > 0) {
      zrec.zip = crec.zip;
      prompt("Zip Code does not exists; Please Enter City: ",zrec.city.arr);
      zrec.city.len = strlen(zrec.city.arr);
      EXEC SQL SET TRANSACTION READ WRITE;
      EXEC SQL INSERT INTO zipcodes (zip, city)
         VALUES (:zrec);
      EXEC SQL COMMIT;
    }
  }

  printf("Current Phone Value          : %s\n",crec.phone);
  prompt("New Phone (n<ENTER> for same): ",ph);
  if (strlen(ph) > 1) {
    strcpy(crec.phone,ph);
  }

  EXEC SQL SET TRANSACTION READ WRITE;
  EXEC SQL UPDATE customers SET street = :crec.street,
                                zip    = :crec.zip,
                                phone  = :crec.phone
           WHERE cno = :crec.cno; 
  if (sqlca.sqlcode < 0) {
     printf("\n\nError on Update\n"); 
     EXEC SQL ROLLBACK WORK;
     return; 
  }
  EXEC SQL COMMIT;
  printf("\nCustomer (%d) updated.\n",crec.cno);
}

  
void remove_customer() {          
  EXEC SQL BEGIN DECLARE SECTION;
    customer_record crec;
    int cnum,onum;
  EXEC SQL END DECLARE SECTION;

  printf("Customer Number to be deleted: ");
  scanf("%d",&cnum); getchar();

  EXEC SQL SELECT * 
           INTO :crec 
           FROM CUSTOMERS 
           WHERE cno = :cnum;
  if (sqlca.sqlcode > 0) {
    printf("Customer (%d) does not exist\n",cnum);
    return;
  }

  EXEC SQL DECLARE del_cur CURSOR FOR
      SELECT ono FROM orders WHERE cno = :cnum;

  EXEC SQL SET TRANSACTION READ ONLY;
  EXEC SQL open del_cur;
  EXEC SQL fetch del_cur into :onum;
  if (sqlca.sqlcode == 0) {
      printf("Orders exist for this customer - cannot delete\n");
      EXEC SQL COMMIT;
      return;
  }
  EXEC SQL COMMIT;

  EXEC SQL SET TRANSACTION READ WRITE;
  EXEC SQL DELETE FROM customers
           WHERE cno = :cnum;
  printf("\nCustomer (%d) DELETED\n",cnum);
  EXEC SQL COMMIT;                                             
}


void process_order() {
  EXEC SQL BEGIN DECLARE SECTION;
    customer_record crec;
    int eenum,cnum,pnum,qqty,ord_lev,qqoh;
  EXEC SQL END DECLARE SECTION;

  FILE *f1;
  char ch;
  int nparts;

  EXEC SQL SET TRANSACTION READ ONLY;
  do{
    printf("Employee Number: ");
    scanf("%d",&eenum); getchar();
    EXEC SQL SELECT eno 
             INTO   :eenum
             FROM   employees
             WHERE  eno = :eenum;
    if (sqlca.sqlcode > 0) 
      printf("Employee (%d) does not exist\n",eenum);
  } while (sqlca.sqlcode!=0);
  EXEC SQL COMMIT;   

  do {
    printf("New Customer (y or n)? ");
    scanf("%s",&ch); getchar();
  } while ((ch != 'y') && (ch != 'Y') && (ch != 'n') && (ch != 'N'));
  if ((ch == 'y') || (ch == 'Y')) {
    add_customer();
    EXEC SQL SET TRANSACTION READ ONLY;
    EXEC SQL select custseq.currval
             into   :cnum
             from   dual;
    EXEC SQL COMMIT;   
  }
  else {
    printf("Customer Number: ");
    scanf("%d",&cnum); getchar();
  }

  EXEC SQL SET TRANSACTION READ ONLY;
  EXEC SQL SELECT * 
           INTO  :crec
           FROM  customers
           WHERE cno = :cnum;
  if (sqlca.sqlcode > 0){
    printf("Customer (%d) does not exist\n",cnum);
    EXEC SQL COMMIT;
    return;
 }
 EXEC SQL COMMIT;

 EXEC SQL SET TRANSACTION READ WRITE;
 EXEC SQL INSERT INTO orders (ono,cno,eno,received) 
          values (ordseq.nextval,:cnum,:eenum,sysdate);
 if (sqlca.sqlcode != 0) {
  printf("Error while entering order\n");
  EXEC SQL ROLLBACK WORK;
  return;
 }
 nparts = 0;
 do{
   printf("Please enter part number and quantity,(0,0)to quit:  ");
   scanf("%d%d",&pnum,&qqty); getchar();
   if (pnum != 0) {
     EXEC SQL SELECT qoh,olevel
              INTO   :qqoh,:ord_lev
              FROM   parts
              WHERE  pno=:pnum;
     if (qqoh > qqty) {
       EXEC SQL INSERT INTO odetails values (ordseq.currval,:pnum,:qqty);
       if (sqlca.sqlcode == 0) {
         nparts++;
         EXEC SQL UPDATE parts SET qoh = (qoh - :qqty) WHERE pno=:pnum;
         if (qqoh < ord_lev){
           EXEC SQL UPDATE parts SET qoh = 5*olevel WHERE pno=:pnum;
           f1 = fopen("restock.dat","a");
           fprintf(f1,"Replenish part (%d) by (%d)\n", pnum, 5*ord_lev - qqoh);
           fclose(f1);
         }  
      }
      else  printf("Could not add part (%d) to order\n",pnum);
    }
    else printf("Not enough quantity in stock for (%d)\n",pnum);
   }
 }while(pnum>0);
printf("nparts = %d\n",nparts);
 if (nparts > 0) 
    EXEC SQL COMMIT;
  else
    EXEC SQL ROLLBACK WORK;

 printf("NEW ORDER PROCESSING COMPLETE\n"); 
}


void delete_old_orders() {
/* delete orders that have been shipped more than
   five years ago */

  EXEC SQL SET TRANSACTION READ WRITE;
  EXEC SQL DELETE FROM ospecs
           WHERE ono in (select ono
                         from orders
                         where shipped <  (sysdate - 5*365));

  EXEC SQL DELETE FROM orders
           WHERE shipped < (sysdate- 5*365);

  EXEC SQL COMMIT;
  printf("ORDERS SHIPPED 5 YEARS or EARLIER DELETED!\n");
}

void print_invoice() {
  EXEC SQL BEGIN DECLARE SECTION;
    int zzip,cnum,eenum,onum,pnum,qqty;
    varchar st[31],eename[31],ccname[31],ccity[31],ppname[31];
    char ph[13];
    float sum,pprice;
    order_record orec;
    order_indicator_record orecind;
  EXEC SQL END DECLARE SECTION;

  EXEC SQL DECLARE od_cur CURSOR for
    select parts.pno, pname, qty, price 
    from   odetails, parts
    where  odetails.ono = :onum and odetails.pno = parts.pno;

  printf("Order Number: ");
  scanf("%d",&onum); getchar();

  EXEC SQL SET TRANSACTION READ ONLY;
  EXEC SQL SELECT *
           INTO   :orec INDICATOR :orecind
           FROM   orders
           WHERE  ono = :onum;
  if (sqlca.sqlcode == 0) {
    EXEC SQL SELECT cno, cname, street, city, customers.zip, phone
             INTO   :cnum, :ccname, :st, :ccity, :zzip, :ph
             FROM   customers, zipcodes
             WHERE  cno = :orec.cno and customers.zip = zipcodes.zip;
    ccname.arr[ccname.len] = '\0';
    st.arr[st.len] = '\0';
    ccity.arr[ccity.len] = '\0';
    printf("**************************************************************\n");
    printf("Customer: %s \t Customer Number: %d \n", ccname.arr, cnum);
    printf("Street  : %s \n", st.arr);
    printf("City    : %s \n", ccity.arr);
    printf("ZIP     : %d \n",zzip);
    printf("Phone   : %s \n", ph);
    printf("--------------------------------------------------------------\n");

    EXEC SQL SELECT eno, ename
             INTO   :eenum, :eename
             FROM   employees
             WHERE  eno = :orec.eno;
    eename.arr[eename.len] = '\0';
    printf("Order No: %d \n",orec.ono);
    printf("Taken By: %s (%d)\n",eename.arr, eenum);
    printf("Received On: %s\n",orec.received);
    printf("Shipped  On: %s\n\n",orec.shipped);

    EXEC SQL OPEN od_cur;
    EXEC SQL FETCH od_cur INTO :pnum, :ppname, :qqty, :pprice;
    printf("Part No.              Part Name    Quan.   Price     Ext\n");
    printf("-----------------------------------------------------------\n");
    sum = 0.0;
    while (sqlca.sqlcode == 0) {
      ppname.arr[ppname.len] = '\0';
      printf("%8d%25s%7d%10.2f%10.2f\n",pnum, ppname.arr,
          qqty, pprice, qqty*pprice);
      sum = sum + (qqty*pprice);
      EXEC SQL fetch od_cur into :pnum, :ppname, :qqty, :pprice;
     } 
     EXEC SQL CLOSE od_cur;
     printf("-------------------------------------------------------------\n");
     printf("                                 TOTAL:           %10.2f\n",sum);
     printf("**************************************************************\n");
     EXEC SQL COMMIT;
  }
}

void prompt(char s[], char t[]) {
  char c;
  int i = 0;

  printf("%s",s);
  while ((c = getchar()) != '\n') {
    t[i] = c;
    i++;
  }
  t[i] = '\0';
}