/****************************************************************/
/* 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;

EXEC SQL INCLUDE sqlca;

void print_menu();
void add_customer();
void print_customers();
void remove_customer();
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': remove_customer(); 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> Remove a customer\n");
  printf("<q> Quit\n");
  printf("***********************************************\n");
}

void add_customer() {
  char tmp[100];
  EXEC SQL BEGIN DECLARE SECTION;
    customer_record crec;
    zipcode_record  zrec;
  EXEC SQL END DECLARE SECTION;


  prompt("Customer ID: ",tmp);
  crec.cno=atoi(tmp);

  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.cno,:crec.cname,:crec.street,:crec.zip,
     :crec.phone); 

  if (sqlca.sqlcode < 0) {
    printf("\n\nCUSTOMER (%s) DID NOT GET ADDED\n",
           crec.cname.arr);
    printf("\n\nerror code=%d\n",sqlca.sqlcode);
    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 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 prompt(char s[], char t[]) {
  char c;
  int i = 0;

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