/* Order.pc */

#include <stdio.h>
#include <unistd.h>
#include <sqlca.h>      /*EXEC SQL INCLUDE sqlca;*/

#include "order.h"

Teil *first = NULL;
BOOL tablesExist = FALSE;

int create_tables(void)
{
 /*  exec sql begin declare section; */
/*     int temp=MAX_NAME_LENGTH; */
/*   exec sql end declare section; */

  if (tablesExist == TRUE)
    return TABLES_ALREADY_EXIST;

  exec sql whenever sqlerror do ORAERRNROLL(SQLERROR);

  /* Costumer(ID)-Relation anlegen*/
  exec sql create table Customer (Customer_ID    number(38)    primary key,
                                  Customer_Name  varchar2(50)  not null);
  exec sql create table Customer_ID (Akt_ID  number(38) default 0);
  exec sql insert into Customer_ID values (0);
                       
  /* Booking(ID)-Relation anlegen*/
  exec sql create table Booking (Booking_ID    number(38)  primary key,
                                 Customer_ID   number(38)  references Customer,
                                 Booking_Date  date        ,
                                 Sent          number(38)  default 0);
  exec sql create table Booking_ID (Akt_ID  number(38) default 0);
  exec sql insert into Booking_ID values (0);
                         
  /* Booking_Item(ID)-Relation anlegen*/
  exec sql create table Booking_Item (Booking_Item_ID  number(38)  primary key,
                                      Booking_ID       number(38)  references Booking,
                                      Teil_Nr          number(38)  not null,
                                      Anzahl           number(38)  default 0);
  exec sql create table Booking_Item_ID (Akt_ID  number(38) default 0);
  exec sql insert into Booking_Item_ID values (0);

  exec sql commit work;

  exec sql whenever sqlerror continue;
  
  tablesExist = TRUE;
  return OK;
}

int drop_tables(void)
{
  /* exec sql whenever sqlerror do ORAERRNROLL(SQLERROR); */
  /* if (tablesExist == FALSE) */
/*     return NO_TABLES; */

  /* Damit alle drop table Befehle ausgefuehrt werden */
  exec sql whenever sqlerror do oraError();

  exec sql drop table Booking_Item;
  exec sql drop table Booking_Item_ID;
  exec sql drop table Booking;
  exec sql drop table Booking_ID;
  exec sql drop table Customer;
  exec sql drop table Customer_ID;

  exec sql commit work;

  exec sql whenever sqlerror continue;
  tablesExist = FALSE;
  return OK;
}

int update_cache(void)
{
  Teil *current = first;
  Teil *prev = NULL;

  exec sql whenever sqlerror do ORAERRNROLL(SQLERROR);

  exec sql begin declare section;
    Teil temp;
  exec sql end declare section;

  exec sql begin declare section;
    int Teil_Nr;
    int Anzahl;
  exec sql end declare section;

  exec sql declare c1 cursor for
    select Teil_Nr, Teil_Name, Teil_Preis, Teil_Gewicht, Teil_Menge from Server.Teil order by Teil_Nr;

  exec sql open c1;
  exec sql whenever not found goto update_cache_closec1;

  /* Server.Teil einfach einlesen */
  while(TRUE)
    {
      exec sql fetch c1
        into :temp.Teil_Nr, :temp.Teil_Name, :temp.Teil_Preis, :temp.Teil_Gewicht, :temp.Teil_Menge;

#ifdef MULTI_CLIENT
      temp.Server_Amount = temp.Teil_Menge;
#endif

      /* Freizeichen in Strings entfernen */
      removeSpaces(temp.Teil_Name);

      /* Cache am Ende */
      if (current == NULL)
        {
          /* neues Teil hinten dran haengen*/
          current = (Teil*) malloc(sizeof(Teil));
          if (current == NULL)
            {
              perror("update_cache (malloc):");
              exec sql close c1;
              return MEMORY_ERROR;
            }

          temp.next = NULL;

          if (prev != NULL)
            prev->next = current;
          else
            first = current;
        }
      else
        {
          temp.next = current->next;
        }
      
      /* Teil ueberschreiben */
      *current = temp;
      
      prev = current;
      current = current->next;
    }

 update_cache_closec1:
  exec sql close c1;
  exec sql whenever not found continue;

  /* restliche Teile im Cache loeschen */
  while (current != NULL)
    {
      prev = current;
      current = current->next;
      free(prev);
    }

  /* Mit Bestellungen abgleichen */
  /* --------------------------- */

  tablesExist=countTables();
  if (!tablesExist)
    return OK;

  exec sql declare c2 cursor for
    select Teil_Nr, Anzahl from Booking_Item order by Teil_Nr;

  exec sql open c2;

  exec sql whenever not found goto update_cache_closec2;
  
  current = first;
  while(TRUE)
    {
      exec sql fetch c2
        into :Teil_Nr, :Anzahl;

      if (current != NULL) /* vorsichtshalber */
        {
          while(current->Teil_Nr < Teil_Nr)
            {
              current = current->next;
              if (current == NULL || current->Teil_Nr > Teil_Nr)
                {
                  /* Teil in Buchung existiert gar nicht (theoretisch dürfte dieser Fehler bei richtigen Tabellen nie auftauchen) */
                  exec sql close c2;
                  return NO_SUCH_ITEM;
                }
            }

          current->Teil_Menge -= Anzahl;
          if (current->Teil_Menge < 0)
            {
              exec sql close c2;
              return DATABASE_INCONSISTENT;
            }
        }
    }

 update_cache_closec2:
  exec sql close c2;
  exec sql whenever not found continue;

  exec sql whenever sqlerror continue;
  return OK;
}

int new_customer(char *customer)
{
  exec sql begin declare section;
    int counter;
    char Kunde[MAX_NAME_LENGTH+1];

    int ID;
  exec sql end declare section;

  if (tablesExist == FALSE)
    return NO_TABLES;

  if (strncpy(Kunde, customer, MAX_NAME_LENGTH) == NULL)
    return MEMORY_ERROR;
  Kunde[MAX_NAME_LENGTH]='\0';

  exec sql whenever sqlerror do ORAERRNROLL(SQLERROR);

  /* gibt es den Kunden schon ? */
  /* Customer_ID muss die ganze Zeit gesperrt sein, damit kein Kunde von verschiedenen Clients doppelt eingetragen werden kann */
  exec sql lock table Customer_ID in exclusive mode;

  exec sql select count(Customer_Name) into :counter from Customer where Customer_Name = :Kunde;

  /* Kunde bereits vorhanden */
  if (counter != 0)
    return CUSTOMER_ALREADY_EXISTS;

  /* neue Customer_ID erstellen */

  exec sql select Akt_ID into :ID from Customer_ID;
  ID++;
  exec sql update Customer_ID set Akt_ID = :ID;

  /* Kunden in Relation eintragen */
  exec sql insert into Customer values (:ID, :Kunde);

  /* Transaktion beenden und locks aufheben */
  exec sql commit work;

  exec sql whenever sqlerror continue;
  return OK;
}

int new_order(char *customer, char *product, int cnt)
{
  Teil *current = first;

  exec sql begin declare section;
    int customerID;
    int counter;
    int ID;
    int itemID;
    BOOL flag;
    int Teil_Nr;
    int Anzahl;
  exec sql end declare section;

  if (tablesExist == FALSE)
    return NO_TABLES;

  exec sql whenever sqlerror do ORAERRNROLL(SQLERROR);

  /* Menge = 0 ist unsinnig */
  if (cnt == 0)
    return AMOUNT_TOO_BIG;

  /* pruefen, ob Produkt ueberhaupt existiert */
  while (current != NULL)
    {
      if (!strcmp(current->Teil_Name, product))
        break;
      current = current->next;
    }

  /* Produkt gibt es nicht */
  if (!current)
    return NO_SUCH_ITEM;

  Teil_Nr = current->Teil_Nr;

  exec sql lock table Booking_ID in exclusive mode;

#ifdef MULTI_CLIENT

  /* Für gewünschtes Produkt Bestellungen abgleichen (damit mehrere Clients nicht dieselben Teile bestellen können)*/
  exec sql select nvl(sum(Anzahl), 0) into :Anzahl from Booking_Item where Teil_Nr = :Teil_Nr;

  current->Teil_Menge = current->Server_Amount - Anzahl;
  if (current->Teil_Menge < 0)
    {
      exec sql rollback;
      return NEED_UPDATE_CACHE;
    }

#endif

  /* Produkt ist nicht in angeforderter Menge vorhanden */
  if (current->Teil_Menge < cnt) /* bei negativen cnt ist Bedingung nie erfüllt, da Teil_Menge >= 0 */
    {
      exec sql rollback;
      return AMOUNT_TOO_BIG;
    }

  /* Kundennummer holen */
  customerID = getCustomerID(customer);
  /* Fehler in Fkt.-Ausfuehrung */
  if (customerID < 0)
    {
      exec sql rollback;
      return customerID;
    }

  /* Kunde existiert gar nicht */
  if (customerID == 0)
    {
      exec sql rollback;
      return NO_SUCH_CUSTOMER;
    }

  /* Bestellung offen ? */
  flag = FALSE;
  exec sql select count(Booking_ID) into :counter from Booking where Sent = :flag and Customer_ID = :customerID;

  if (!counter)
    {
      /* bei geschlossenen Bestellungen kann nichts mehr abbestellt werden */
      if (cnt < 0)
        {
          exec sql rollback;
          return NO_OPEN_ORDER;
        }

      /* neue Buchungs_ID erstellen */
      exec sql select Akt_ID into :ID from Booking_ID;
      ID++;
      exec sql update Booking_ID set Akt_ID = :ID;

      /* neue Buchung erstellen */
      exec sql insert into Booking values (:ID, :customerID, sysdate, :flag);
    }
  else
    {
      /* Booking_ID der offenen Bestellung holen */
      exec sql select Booking_ID into :ID from Booking where Sent = :flag and Customer_ID = :customerID;

      if (cnt < 0)
        {
          /* bestellte Menge des Produkts holen (ob abbestellt werden kann)*/
          exec sql select nvl(sum(Anzahl), 0) into :counter from Booking_Item where Booking_Id = :ID and Teil_Nr = :Teil_Nr;

          if (counter < -cnt)
            {
              exec sql rollback;
              return AMOUNT_TOO_BIG;
            }
        }
      
    }

  /* neue Buchungseintrag-ID erstellen */
  exec sql lock table Booking_Item_ID in exclusive mode;
  exec sql select Akt_ID into :itemID from Booking_Item_ID;
  itemID++;
  exec sql update Booking_Item_ID set Akt_ID = :itemID;
 
  /* neuen Buchungseintrag hinten anhaengen */
  exec sql insert into Booking_Item values (:itemID, :ID, :Teil_Nr, :cnt);

  /* Transaktion beenden und locks aufheben */
  exec sql commit work;

  /* Teilmenge in Cache ändern */
  current->Teil_Menge -= cnt;

  exec sql whenever sqlerror continue;
  return OK;
}

int remove_order(char *customer, char *product, int cnt)
{
  return new_order(customer, product, -cnt);
}

int send_order(char *customer)
{
  exec sql begin declare section;
    int customerID;
    int counter;
    BOOL flag;
    int ID;
    int Teil_Nr;
    int itemID;
    int Anzahl;
    int firstItemID;
  exec sql end declare section;

  if (tablesExist == FALSE)
    return NO_TABLES;

  /* Kundennummer holen */
  customerID = getCustomerID(customer);
  /* Fehler in Fkt.-Ausfuehrung */
  if (customerID < 0)
    return customerID;

  /* Kunde existiert gar nicht */
  if (customerID == 0)
    return NO_SUCH_CUSTOMER;

  exec sql whenever sqlerror do ORAERRNROLL(SQLERROR);

  exec sql lock table Booking_ID in exclusive mode;

  /* Bestellung offen ? */
  flag = FALSE;
  exec sql select count(Booking_ID) into :counter from Booking where Sent = :flag and Customer_ID = :customerID;

  if (!counter)
    {
      /* bei geschlossenen Bestellungen kann nichts mehr geschlossen werden */
      return NO_OPEN_ORDER;
    }

  /* Booking_ID der offenen Bestellung holen */
  exec sql select Booking_ID into :ID from Booking where Sent = :flag and Customer_ID = :customerID;
  
  /* fuer alle Teile in der offenen Liste diese zusammenzaehlen und reinschreiben*/
  exec sql declare c4 cursor for
    select distinct teil_nr from booking_item where booking_id = :ID;
  exec sql open c4;
  exec sql whenever not found goto send_order_closec4;
  
  firstItemID = 0;
  while (TRUE)
    {
      exec sql fetch c4
        into :Teil_Nr;
      
      /* vorsichtshalber ist nvl beim folgenden select mit drin (theoretisch ist es ueberfluessig) */
      exec sql select nvl(sum(anzahl), 0) into :Anzahl from Booking_Item where Booking_ID = :ID and teil_nr = :Teil_Nr;
      
      if (Anzahl != 0)
        {
          /* neue Buchungseintrag-ID erstellen */
          exec sql lock table Booking_Item_ID in exclusive mode;
          exec sql select Akt_ID into :itemID from Booking_Item_ID;
          itemID++;
          exec sql update Booking_Item_ID set Akt_ID = :itemID;
          
          /* neuen Buchungseintrag hinten anhaengen */
          exec sql insert into Booking_Item values (:itemID, :ID, :Teil_Nr, :Anzahl);
          
          if (firstItemID == 0) firstItemID = itemID;
        }
      
    }
  
 send_order_closec4:
  exec sql close c4;
  exec sql whenever not found continue;

  if (firstItemID != 0)
    {
      /* alle alten Eintraege loeschen */
      exec sql delete from Booking_Item where Booking_ID = :ID and Booking_Item_ID < :firstItemID;

      /* Sent-flag aendern */
      flag = TRUE;
      exec sql update Booking set Sent = :flag where Booking_ID = :ID;
    }
  else
    {
      /* alle Buchungseintraege heben sich gegenseitig auf -> Buchung entfernen */
      exec sql delete from Booking_Item where Booking_ID = :ID;
      exec sql delete from Booking where Booking_ID = :ID;
    }

  exec sql commit work;

  exec sql whenever sqlerror continue;
  return OK;
}

int show_customers(void)
{
  exec sql begin declare section;
    int customerID;
    char customerName[MAX_NAME_LENGTH + 1];
  exec sql end declare section;

  if (tablesExist == FALSE)
    return NO_TABLES;
  exec sql whenever sqlerror do ORAERRNROLL(SQLERROR);

  customerID = 0;
  exec sql declare c5 cursor for
    select Customer_ID, Customer_Name from Customer order by Customer_Name;

  exec sql open c5;
  exec sql whenever not found goto show_customers_closec5;

  printf("---------------\n| Kundenliste |\n---------------\n");

  while (TRUE)
    {
      exec sql fetch c5
        into :customerID, :customerName;

      printf("Kundenname: %-50s (Kundennr.: %-10d)\n", customerName, customerID);
    }

 show_customers_closec5:
  exec sql whenever not found continue;

  if (customerID == 0)
    printf("Keine Kunden eingetragen.\n");

  exec sql whenever sqlerror continue;
  return OK;
}

int show_orders(char *customer)
{
  exec sql begin declare section;
    int customerID;
    int bookingID;
    int flag;
    char bookingDate[DATE_LENGTH];

    int Teil_Nr;
    int Anzahl;
  exec sql end declare section;

  Teil *current;
  double sum, all;

  if (tablesExist == FALSE)
    return NO_TABLES;

  /* Kundennummer holen */
  customerID = getCustomerID(customer);
  /* Fehler in Fkt.-Ausfuehrung */
  if (customerID < 0)
    return customerID;

  /* Kunde existiert gar nicht */
  if (customerID == 0)
    return NO_SUCH_CUSTOMER;

  exec sql whenever sqlerror do ORAERRNROLL(SQLERROR);

  exec sql declare c6 cursor for
    select Booking_ID, Booking_Date, Sent from Booking where Customer_ID = :customerID order by Booking_ID;
  
  exec sql open c6;
  exec sql whenever not found goto show_orders_gotoc6;

  printf("-----------------------------------------------------------------------------\n");
  printf("| Bestelliste des Kunden %-50s |\n", customer);
  printf("-----------------------------------------------------------------------------\n");
  
  all = 0;
  bookingID = 0;
  while (TRUE)
    {
      exec sql fetch c6
        into :bookingID, :bookingDate, :flag;

      sum = 0;
      printf("\nBuchungsNr: %d ", bookingID);
      if (flag)
          printf("(abgeschlossene Bestellung)\n");
      else
          printf("(offene Bestellung)\n");
      printf("Datum     : %s\n", bookingDate);

      printf("----------------------------------------------------------------------------------------------------------------\n");
      printf("| Nr.        | %-50s |  EinzelPreis  |    Anzahl     |   Wert     |\n", "Name");
      printf("----------------------------------------------------------------------------------------------------------------\n");

      exec sql declare c7 cursor for
        select Teil_Nr, Anzahl from Booking_Item where Booking_ID = :bookingID order by Booking_Item_ID;

      exec sql open c7;
      exec sql whenever not found do break;

      while (TRUE)
        {
          exec sql fetch c7
            into :Teil_Nr, :Anzahl;

          printf("| %-10d ", Teil_Nr);
          /* Name zu Teilnr suchen und ausgeben*/
          current = first;
          while (current != NULL)
            {
              if (current->Teil_Nr == Teil_Nr)
                {
                  printf("| %-50s ", current->Teil_Name);
                  break;
                }
              current = current->next;
            }
          printf("| %13.2lf | %10d ", current->Teil_Preis, Anzahl);
          if (current != NULL)
            {
              printf("| %13.2lf |\n", Anzahl * current->Teil_Preis);
              sum += Anzahl * current->Teil_Preis;
            }
        }

      printf("----------------------------------------------------------------------------------------------------------------\n");
      printf("Gesamtbestellwert: %13.2lf\n", sum);
      all += sum;
    }

 show_orders_gotoc6:
  exec sql close c6;
  exec sql whenever not found continue;

  if (bookingID == 0)
    printf("Keine Bestellungen vorhanden.\n");
  else
    printf("\nSumme aller Gesamtbestellwerte: %13.2lf\n\n", all);

  exec sql whenever sqlerror continue;
  return OK;
}

int show_products(void)
{
  Teil *current = first;

  printf("----------------\n| Produktliste |\n----------------\n");
  printf("----------------------------------------------------------------------------------------------------------------\n");
  printf("| Nr.        | %-50s |     Preis     |    Gewicht    |   Menge    |\n", "Name");
  printf("----------------------------------------------------------------------------------------------------------------\n");
  while (current != NULL)
    {
      printf("| %-10d | %-50s | %13.2lf | %13.2lf | %10d |\n", current->Teil_Nr, current->Teil_Name, current->Teil_Preis, current->Teil_Gewicht, current->Teil_Menge);
      current = current->next;
    }
  printf("----------------------------------------------------------------------------------------------------------------\n");

  return OK;
}

int current_price(char *customer, double *result)
{
  exec sql begin declare section;
    int customerID;
    int flag;
    int bookingID;

    int Teil_Nr;
    int Anzahl;
  exec sql end declare section;

  Teil *current;

  if (tablesExist == FALSE)
    return NO_TABLES;

  /* Kundennummer holen */
  customerID = getCustomerID(customer);
  /* Fehler in Fkt.-Ausfuehrung */
  if (customerID < 0)
    return customerID;

  /* Kunde existiert gar nicht */
  if (customerID == 0)
    return NO_SUCH_CUSTOMER;

  exec sql whenever sqlerror do ORAERRNROLL(SQLERROR);

  flag = FALSE;
  exec sql select nvl(min(Booking_ID), 0) into :bookingID from Booking where Customer_ID = :customerID and Sent = :flag;
  
  *result = 0;

  if (bookingID == 0)
    {
      /* Keine offene Bestellung */
      return NO_OPEN_ORDER;
    }

  exec sql declare c8 cursor for
    select Teil_Nr, Anzahl from Booking_Item where Booking_ID = :bookingID;

  exec sql open c8;
  exec sql whenever not found goto current_price_closec8;

  while (TRUE)
    {
      exec sql fetch c8
            into :Teil_Nr, :Anzahl;

      /* Name zu Teilnr suchen und ausgeben*/
      current = first;
      while (current != NULL)
        {
          if (current->Teil_Nr == Teil_Nr)
            break;
          current = current->next;
        }

      if (current != NULL)
        {
          *result += Anzahl * current->Teil_Preis;
        }
    }
 
 current_price_closec8:
  exec sql close c8;
  exec sql whenever not found continue;

  exec sql whenever sqlerror continue;
  return OK;
}


BOOL countTables(void)
{
  exec sql whenever sqlerror do ORAERRNROLL(FALSE);

  exec sql begin declare section;
    int counter;
  exec sql end declare section;

  exec sql select count(*) into :counter from cat;

  exec sql whenever sqlerror continue;

  if (counter!=6)
    return FALSE;
  else
    return TRUE;
}

void removeSpaces(char *s)
{
  int i = strlen(s) - 1;

  if (i < 0) return;

  while (s[i] == ' ')
    {
      s[i] = '\0';
      i--;
    }
}

int getCustomerID(char *customer)
{
  exec sql begin declare section;
    char Kunde[MAX_NAME_LENGTH+1];
    int customerID;
  exec sql end declare section;

  if (strncpy(Kunde, customer, MAX_NAME_LENGTH) == NULL)
    return MEMORY_ERROR;
  Kunde[MAX_NAME_LENGTH]='\0';

  exec sql whenever sqlerror do ORAERRNROLL(SQLERROR);

  /* ob ich in der nvl-Fkt. min, max oder sum benutze, ist eigentlich egal, da Kundenname nur einmal existieren darf */
  exec sql select nvl(min(Customer_ID), 0) into :customerID from Customer where Customer_Name = :Kunde;

  exec sql whenever sqlerror continue;

  return customerID;
}

void oraErrNRoll(void)
{
  oraError();
  exec sql rollback;
}