// Implementation of the Lookup Service interface used to send information
// to the client. Very overcrowded. Probably should move SQL functions to new
// file.
// Authors:
// Cameron Hayes
// Mac Stephens
// Elias Sarraf
unit Lookup.ServiceImpl;

interface

uses
  XData.Server.Module,
  XData.Service.Common,
  Api.Database, Data.DB, frxClass, frxExportPDF, JS,
  Lookup.Service, System.Hash, System.JSON, Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, MemDS, DBAccess, Uni,
  hyiedefs, hyieutils, iexBitmaps, iesettings, iexLayers, iexRulers,
  iexToolbars, iexUserInteractions, imageenio, imageenproc, QuickRpt, QRCtrls,
  dbimageen, Vcl.ExtCtrls, ieview, imageenview, IdBaseComponent, IdComponent,
  IdTCPConnection, IdTCPClient, IdExplicitTLSClientServerBase, IdFTP,
  iexProcEffects, frxDBSet, frxExportBaseDialog, frCoreClasses, rOrderList, rOrderCorrugated, Common.Logging,
  DateUtils, REST.Client, REST.Types, WEBLib.REST, WEBLib.WebTools,System.Net.HttpClient,
  System.Net.URLClient, System.Net.HttpClientComponent, System.netencoding,
  IdHTTP, IdSSLOpenSSL, IdSSLOpenSSLHeaders, System.IniFiles, System.Generics.Collections;


type
  [ServiceImplementation]
  TLookupService = class(TInterfacedObject, ILookupService)
  strict private
    ordersDB: TApiDatabase;
  private
    function GetItems(searchOptions: string): TItemList;
    function GetUsers(searchOptions: string): TUserList;
    function GetOrders(searchOptions: string): TOrderList;
    function GetCorrugatedOrder(orderInfo: string): TCorrugatedOrder;
    function GetCustomers(customerInfo:string): TCustomerList;
    function GetCustomer(ID: string): TCustomerItem;
    function GetWebOrder(orderInfo: string): TWebOrder;
    function GetCuttingDieOrder(orderInfo: string): TCuttingDie;
    function GetQBCustomers: TJSONArray;
    function GetQBItems: TJSONArray;
    function GetRepUsers(): TList<TUserItem>;


    function EditUser(const editOptions: string): string;

    function GenerateOrderListPDF(searchOptions: string): string;
    function GenerateOrderCorrugatedPDF(orderID: string): string;
    function GenerateOrderWebPDF(orderID: string): string;
    function GenerateOrderCuttingPDF(orderID: string): string;

    function AddUser(userInfo: string): string;
    function AddCustomer(customerInfo: string): TJSONObject;
    function AddItem(itemInfo: string): TJSONObject;
    function DelUser(username: string): string;
    function AddCorrugatedOrder(orderInfo: string): TJSONObject;
    function AddWebOrder(orderInfo: string): TJSONObject;
    function AddCuttingDieOrder(orderInfo: string): TJSONObject;
    function AddShippingAddress(AddressInfo: string): TJSONObject;
    function delOrder(OrderID, OrderType, UserID: string): TJSONObject;
    function DelShippingAddress(AddressID, CustomerID: string): TJSONObject;

    function GenerateSubQuery(currStatus: string): string;

    function AddStatusSchedule(StatusType: string; order: TJSONObject; ORDER_ID: integer): string;
    function GenerateOrdersSQL(searchOptions: string): TSQLQuery;
    function GetColorCount(colors: string): string;
    function GenerateStatusSelectSQL(statusTableShort, statusTableLong, startDate, endDate, statusType: string): string;
    function GenerateStatusWhereSQL(status: TStatusSearchInfo): string;
    function CreateStatusSearchInfo(params: TStringList; statusNum: string): TStatusSearchInfo;
    function SetStatus(statusOptions: string): string;
    procedure AddToOrdersTable(mode, ORDER_TYPE: string; JSONData: TJSONObject);
    procedure AddToRevisionsTable(OrderID, table: string; order: TJSONObject);
    procedure SaveTokens(AccessToken, RefreshToken: string);
    function RefreshAccessToken: string;
    function ImportQBCustomer(CustomerInfo: string): TJSONObject;
    function AddEstimate(orderInfo: string): TJSONObject;
  public
    procedure AfterConstruction; override;
    procedure BeforeDestruction; override;

  end;

implementation

uses
  XData.Sys.Exceptions, uLibrary, rOrderWeb, rOrderCutting;


function TLookupService.addEstimate(orderInfo: string): TJSONObject;
var
  iniFile: TIniFile;
  restClient: TRESTClient;
  restRequest: TRESTRequest;
  restResponse: TRESTResponse;
  param: TRESTRequestParameter;
  res: string;
  i: integer;
  jsValue: TJSONValue;
  Customer: TJSONValue;
  jsObj: TJSONObject;
  CustomerList: TJSONArray;
  pair: TJSONPair;
  estimateJSON: TJSONObject;
  SQL: string;
  estimate: TEstimate;
  CustomerRef: TRef;
  CustomerRefJSON: TJSONObject;
  Lines: TArray<string>;
  BillAddr: TAddress;
  BillAddrJSON: TJSONObject;
  CustomField:TJSONObject;
  CustomFields: TJSONArray;
  ShipAddr: TAddress;
  ShipAddrJSON: TJSONObject;
  Items: TArray<TLine>;
  ItemRef: TRef;
  SalesItemLineDetail: TSalesItemLineDetail;
  LineArray: TJSONArray;
  LineObj, DetailObj, ItemRefObj: TJSONObject;
  TaxCodeRef: TJSONObject;
  TxnTaxCodeRef: TJSONObject;
  TxnTaxDetail: TJSONObject;
  JSONData: TJSONObject;
  ORDER_ID, companyID, RefreshToken, AccessToken, USER_ID: string;
  table, msg: string;
  ShipMethodRef: TJSONObject;
  LastRefresh: TDateTime;
  unitPrice: double;
  errorObj, faultObj: TJSONObject;
  errorArray: TJSONArray;
begin
  BillAddrJSON := TJSONObject.Create;
  ShipAddrJSON := TJSONObject.Create;
  EstimateJSON := TJSONObject.Create;
  CustomFields := TJSONArray.Create;
  CustomField := TJSONObject.Create;
  LineArray := TJSONArray.Create;
  DetailObj := TJSONObject.Create;
  ItemRefObj := TJSONObject.Create;
  TaxCodeRef := TJSONObject.Create;
  TxnTaxCodeRef := TJSONObject.Create;
  TxnTaxDetail := TJSONObject.Create;
  restClient := TRESTClient.Create(nil);
  restRequest := TRESTRequest.Create(nil);
  restResponse := TRESTResponse.Create(nil);
  iniFile := TIniFile.Create(ExtractFilePath(Application.ExeName) + 'kgOrdersServer.ini');
  Logger.Log(3, 'TLookupService.AddEstimate');
  try
    try
      JSONData := TJSONObject.ParseJSONValue(orderInfo) as TJSONObject;
      if JSONData = nil then
        raise Exception.Create('Invalid JSON format');

      ORDER_ID := JSONData.GetValue<string>('ORDER_ID');
      USER_ID := JSONData.GetValue<string>('USER_ID');

      SQL := 'select * from orders where ORDER_ID = ' + ORDER_ID;
      logger.Log(5, 'Retrieving order type with SQL: ' + SQL);
      doQuery(ordersDB.UniQuery1, SQL);
      if ordersDB.UniQuery1.FieldByName('ORDER_TYPE').AsString  = 'corrugated_plate' then
      begin
        table := 'corrugated_plate_orders';
      end
      else if ordersDB.UniQuery1.FieldByName('ORDER_TYPE').AsString  = 'web_plate' then
      begin
        table := 'web_plate_orders';
      end
      else
        table := 'cutting_die_orders';

      SQL := 'select * from ' + table + ' cpo join customers c on cpo.COMPANY_ID = c.CUSTOMER_ID join customers_ship cs on cpo.staff_fields_ship_to = cs.ship_block JOIN qb_items ON cpo.staff_fields_quickbooks_item = qb_items.qb_item_name where cpo.ORDER_ID = ' + ORDER_ID;
      logger.Log(5, 'Retrieveing Customer Info with SQL: ' + SQL);
      doQuery(ordersDB.UniQuery1, SQL);

      estimateJSON.AddPair('TxnDate', ordersDB.UniQuery1.FieldByName('staff_fields_order_date').AsString);

      CustomerRefJSON := TJSONObject.Create;
      CustomerRefJSON.AddPair('value', ordersDB.UniQuery1.FieldByName('QB_LIST_ID').AsString);
      estimateJSON.AddPair('CustomerRef', CustomerRefJSON);

      Lines := ordersDB.UniQuery1.FieldByName('staff_fields_invoice_to').AsString.Split([#10]);
      for i := 0 to Length(Lines) - 2 do
      begin
        case i of
          0: BillAddrJSON.AddPair('Line1', Lines[i].Trim.Replace(#13, '').Replace(#10, ''));
          1: BillAddrJSON.AddPair('Line2', Lines[i].Trim.Replace(#13, '').Replace(#10, ''));
          2: BillAddrJSON.AddPair('Line3', Lines[i].Trim.Replace(#13, '').Replace(#10, ''));
          3: BillAddrJSON.AddPair('Line4', Lines[i].Trim.Replace(#13, '').Replace(#10, ''));
          4: BillAddrJSON.AddPair('Line5', Lines[i].Trim.Replace(#13, '').Replace(#10, ''));
        end;
      end;

      BillAddrJSON.AddPair('City', ordersDB.UniQuery1.FieldByName('BILL_CITY').AsString);
      BillAddrJSON.AddPair('CountrySubDivisionCode', ordersDB.UniQuery1.FieldByName('BILL_STATE').AsString);
      BillAddrJSON.AddPair('PostalCode', ordersDB.UniQuery1.FieldByName('BILL_ZIP').AsString);

      estimateJSON.AddPair('BillAddr', BillAddrJSON);

      CustomField.AddPair('DefinitionId', '1');
      CustomField.AddPair('Name', 'CUSTID');
      CustomField.AddPair('Type', 'StringType');
      CustomField.AddPair('StringValue', ordersDB.UniQuery1.FieldByName('SHORT_NAME').AsString);

      CustomFields.AddElement(CustomField);

      EstimateJSON.AddPair('CustomField', CustomFields);

      Lines := ordersDB.UniQuery1.FieldByName('ship_block').AsString.Split([sLineBreak]);
      for i := 0 to Length(Lines) - 2 do
      begin
        case i of
          0: ShipAddrJSON.AddPair('Line1', Lines[i].Trim.Replace(#13, '').Replace(#10, ''));
          1: ShipAddrJSON.AddPair('Line2', Lines[i].Trim.Replace(#13, '').Replace(#10, ''));
          2: ShipAddrJSON.AddPair('Line3', Lines[i].Trim.Replace(#13, '').Replace(#10, ''));
          3: ShipAddrJSON.AddPair('Line4', Lines[i].Trim.Replace(#13, '').Replace(#10, ''));
          4: ShipAddrJSON.AddPair('Line5', Lines[i].Trim.Replace(#13, '').Replace(#10, ''));
        end;
      end;

      ShipAddrJSON.AddPair('City', ordersDB.UniQuery1.FieldByName('city').AsString);
      ShipAddrJSON.AddPair('CountrySubDivisionCode', ordersDB.UniQuery1.FieldByName('state').AsString);
      ShipAddrJSON.AddPair('PostalCode', ordersDB.UniQuery1.FieldByName('zip').AsString);

      estimateJSON.AddPair('ShipAddr', ShipAddrJSON);

      // 🔹 Line object representing one line item
      LineObj := TJSONObject.Create;

      LineObj.AddPair('Description', ordersDB.UniQuery1.FieldByName('item_desc').AsString + ' - ' + ordersDB.UniQuery1.FieldByName('staff_fields_job_name').AsString);

      LineObj.AddPair('Amount', TJSONNumber.Create(ordersDB.UniQuery1.FieldByName('staff_fields_price').AsFloat));

      LineObj.AddPair('DetailType', 'SalesItemLineDetail');

      ItemRefObj.AddPair('value', ordersDB.UniQuery1.FieldByName('qb_items_qb_id').AsString);
      DetailObj.AddPair('ItemRef', ItemRefObj);

      DetailObj.AddPair('Qty', TJSONNumber.Create(ordersDB.UniQuery1.FieldByName('staff_fields_quantity').AsFloat));
      unitPrice := ordersDB.UniQuery1.FieldByName('staff_fields_price').AsFloat / ordersDB.UniQuery1.FieldByName('staff_fields_quantity').AsInteger;

      DetailObj.AddPair('UnitPrice', TJSONNumber.Create(unitPrice));

      LineObj.AddPair('SalesItemLineDetail', DetailObj);
      LineArray.AddElement(LineObj);

      EstimateJSON.AddPair('Line', LineArray);

      if ordersDB.UniQuery1.FieldByName('staff_fields_ship_date').AsString <> '' then
      begin
        estimateJSON.AddPair('ShipDate', ordersDB.UniQuery1.FieldByName('staff_fields_ship_date').AsString)
      end;

      logger.Log(5, 'estimateJSON finished construction');

      restClient.BaseURL := 'https://sandbox-quickbooks.api.intuit.com';


      restRequest.Client := restClient;

      restRequest.Response := restResponse;

      if iniFile.ReadString('Quickbooks', 'LastRefresh', '') = '' then
            LastRefresh := 0
          else
            LastRefresh := StrToDateTime(iniFile.ReadString('Quickbooks', 'LastRefresh', ''));

      if MinutesBetween(Now, LastRefresh) > 58 then
        RefreshAccessToken();

      CompanyID := iniFile.ReadString('Quickbooks', 'CompanyID', '');
      RefreshToken := iniFile.ReadString('Quickbooks', 'RefreshToken', '');
      AccessToken := iniFile.ReadString('Quickbooks', 'AccessToken', '');

      restRequest.Method := rmPOST;
      res := '/v3/company/' + companyID + '/estimate';
      restRequest.Resource := res;

      param := restRequest.Params.AddItem;
      param.Name := 'Authorization';
      param.Kind := pkHTTPHEADER;
      param.Options := param.Options + [TRESTRequestParameterOption.poDoNotEncode];
      param.Value := 'Bearer ' + AccessToken;

      restRequest.AddBody(estimateJSON.ToJSON, TRESTContentType.ctAPPLICATION_JSON);

      logger.Log(5, 'Executing rest request with JSON: ' + estimateJSON.ToJSON);
      restRequest.Execute;

      jsValue := restResponse.JSONValue;

      jsObj := TJSONObject(jsValue).GetValue<TJSONObject>('Estimate');

      sql := 'select * from orders where ORDER_ID = ' + ORDER_ID;
      logger.Log(5, 'Updating orders QB info');
      doQuery(ordersDB.UniQuery1, SQL);

      ordersDB.UniQuery1.Edit;
      ordersDB.UniQuery1.FieldByName('IN_QB').AsString := 'T';
      ordersDB.UniQuery1.FieldByName('QB_ORDER_NUM').AsString := jsObj.GetValue<string>('DocNumber');
      ordersDB.UniQuery1.FieldByName('QB_ESTIMATE_ID').AsString := jsObj.GetValue<string>('Id');
      ordersDB.UniQuery1.FieldByName('QB_CREATE_DATE').AsDateTime := Now;
      ordersDB.UniQuery1.FieldByName('QB_ORDER_USER').AsString := USER_ID;
      ordersDB.UniQuery1.Post;

      result := TJSONObject.Create;
      Result.AddPair('status','Success:Estimate Successfully Added');
      logger.log(3, 'AddEstimate Finished');
      except
      on E: Exception do
      begin
        logger.Log(1, 'Error when adding Estimate ' + e.Message);
        jsValue := restResponse.JSONValue;

        if not (jsValue is TJSONObject) then
          raise EXDataHttpException.Create(500, 'Unexpected response format');

        jsObj := TJSONObject(jsValue);

        try
          faultObj := jsObj.GetValue<TJSONObject>('Fault');
          errorArray := faultObj.GetValue<TJSONArray>('Error');

          if (errorArray.Count > 0) and (errorArray.Items[0] is TJSONObject) then
          begin
            errorObj := TJSONObject(errorArray.Items[0]);
            msg := errorObj.GetValue<string>('Detail');
          end
          else
            msg := 'QuickBooks error array is empty.';
        except
          on E: Exception do
            msg := 'Unable to parse QuickBooks error: ' + E.Message;
        end;
        logger.Log(1, 'QuickBooks Error Message: ' + msg);
        raise EXDataHttpException.Create(500, 'Unable to add QuickBooks Estimate: A QuickBooks interface error has occurred!');
      end;
    end;
    finally
      iniFile.Free;
      restClient.Free;
      restRequest.Free;
      restResponse.Free;
      estimateJSON.Free;
    end;
end;

procedure TLookupService.AfterConstruction;
begin
  inherited;
  try
    ordersDB := TApiDatabase.Create(nil);
  except
    on E: Exception do
    begin
      Logger.Log(1, 'Error when creating the API database: ' + E.Message);
      raise EXDataHttpException.Create(500, 'Unable to create API database: A KGOrders Server Error has occured!');
    end;
  end;
end;

procedure TLookupService.BeforeDestruction;
begin
  ordersDB.Free;
  inherited;
end;

function TLookupService.DelShippingAddress(AddressID, CustomerID: string): TJSONObject;
var
  SQL: string;
  ADDRESS_LIST: TJSONArray;
  ADDRESS: TJSONObject;
begin
  logger.Log(3, 'TLookupService.DelShippingAddress');
  SQL := 'DELETE FROM customers_ship WHERE customer_ship_id = ' + AddressID;
  logger.Log(5, 'Deleting shipping address with SQL: ' + SQL);
  OrdersDB.UniQuery1.SQL.Text := SQL;
  OrdersDB.UniQuery1.ExecSQL;
  result := TJSONObject.Create;
  result.AddPair('status', 'Success: Address Successfully Deleted');

  logger.Log(5, 'Address deleted retrieving updated address list...');
  SQL := 'select * FROM customers c LEFT JOIN customers_ship s ON c.CUSTOMER_ID = s.customer_id WHERE c.CUSTOMER_ID = ' + CustomerID;
  doQuery(ordersDB.UniQuery1, SQL);
  ADDRESS_LIST := TJSONArray.Create;
  while not ordersDB.UniQuery1.Eof do
  begin
    ADDRESS := TJSONObject.Create;
    ADDRESS.AddPair('ADDRESS', ordersDB.UniQuery1.FieldByName('ship_block').AsString);
    ADDRESS.AddPair('shipping_address', ordersDB.UniQuery1.FieldByName('address').AsString);
    ADDRESS.AddPair('city', ordersDB.UniQuery1.FieldByName('city').AsString);
    ADDRESS.AddPair('state', ordersDB.UniQuery1.FieldByName('state').AsString);
    ADDRESS.AddPair('zip', ordersDB.UniQuery1.FieldByName('zip').AsString);
    ADDRESS.AddPair('contact', ordersDB.UniQuery1.FieldByName('contact').AsString);
    ADDRESS.AddPair('ship_id', ordersDB.UniQuery1.FieldByName('customer_ship_id').AsString);
    ADDRESS_LIST.Add(ADDRESS);
    ordersDB.UniQuery1.Next;
  end;

  Result.AddPair('ADDRESS', ADDRESS_LIST);
  TXDataOperationContext.Current.Handler.ManagedObjects.Add(Result);
  logger.Log(3, 'DelShippingAddress Finished');
end;


function TLookupService.GetCustomers(customerInfo: string): TCustomerList;
// Retrieves a list of customers and sends it to the client in object form.
// The object contains the ID, Name, Short Name, and the shipping address.
var
  SQL, limitSQL: string;
  customer: TCustomerItem;
  params: TStringList;
  PageNum, PageSize: integer;
  offset,limit: string;
begin
  logger.Log(3, 'TLookupService.GetCustomers');
  params := TStringList.Create;
  try
    params.StrictDelimiter := true;
    params.Delimiter := '&';
    params.DelimitedText := customerInfo;
    PageSize := 0;
    PageNum := 0;

    if (params.Values['pagenumber'] <> '') then
      PageNum := StrToInt(params.Values['pagenumber']);

    if params.Values['pagesize'] <> '' then
      PageSize := StrToInt(params.Values['pagesize']);

    if ( ( PageSize <> 0 ) and (PageNum <> 0 ) ) then
    begin
      offset := IntToStr((PageNum - 1) * PageSize);
      limit := IntToStr(PageSize);
      limitSQL :=  ' limit ' + limit + ' offset ' + offset;
    end;

    try
      SQL := 'select * from customers' + limitSQL;
      logger.Log(5, 'retrieving customers with SQL: ' + SQL);
      doQuery(ordersDB.UniQuery1, SQL);

      result := TCustomerList.Create;
      Result.data := TList<TCustomerItem>.Create;
      TXDataOperationContext.Current.Handler.ManagedObjects.Add(Result.data);
      result.count := 0;

      while not ordersDB.UniQuery1.Eof do
      begin
        customer := TCustomerItem.Create;
        TXDataOperationContext.Current.Handler.ManagedObjects.Add(customer);

        customer.NAME := ordersDB.UniQuery1.FieldByName('NAME').AsString;
        customer.CUSTOMER_ID := ordersDB.UniQuery1.FieldByName('CUSTOMER_ID').AsInteger;
        customer.SHORT_NAME := ordersDB.UniQuery1.FieldByName('SHORT_NAME').AsString;
        customer.staff_fields_invoice_to := ordersDB.UniQuery1.FieldByName('BILL_ADDRESS').AsString +
                            ', ' + ordersDB.UniQuery1.FieldByName('BILL_CITY').AsString +
                            ', ' + ordersDB.UniQuery1.FieldByName('BILL_STATE').AsString +
                            ' ' + ordersDB.UniQuery1.FieldByName('BILL_ZIP').AsString;
        customer.START_DATE := ordersDB.UniQuery1.FieldByName('START_DATE').AsString;

        result.data.Add(customer);
        ordersDB.UniQuery1.Next;
      end;
      ordersDB.UniQuery1.Close;
      SQL := 'SELECT COUNT(*) AS total_count from customers';
      doQuery(ordersDB.UniQuery1, SQL);
      Result.count := ordersDB.UniQuery1.FieldByName('total_count').AsInteger;
      ordersDB.UniQuery1.Close;
      logger.Log(3, 'GetCustomers finished');
    except
      on E: Exception do
      begin
        Logger.Log(2, 'Error in GetCustomers: ' + E.Message);
        raise EXDataHttpException.Create(500, 'Unable to retrieve customer list: A KG Orders Database issue has occurred!');
      end;
    end;
  finally
    params.Free;
  end;
end;


function TLookupService.GetCustomer(ID: string): TCustomerItem;
// Gets one specific customer from the ID given by the client. This is used for
// the AddCustomer form and Order Entry Forms.
var
  SQL: string;
  ADDRESS: TAddressItem;
begin
  logger.Log(3, 'TLookupService.GetCustomer');
  try
    if ID = '' then
       SQL := 'select * FROM customers c LEFT JOIN customers_ship s ON c.CUSTOMER_ID = s.customer_id WHERE c.CUSTOMER_ID = -1'
    else
      SQL := 'select * FROM customers c LEFT JOIN customers_ship s ON c.CUSTOMER_ID = s.customer_id WHERE c.CUSTOMER_ID = ' + ID;
    logger.Log(5, 'Getting customer with SQL: ' + SQL);
    doQuery(ordersDB.UniQuery1, SQL);
    result := TCustomerItem.Create;


    result.NAME := ordersDB.UniQuery1.FieldByName('NAME').AsString;
    result.CUSTOMER_ID := ordersDB.UniQuery1.FieldByName('CUSTOMER_ID').AsInteger;
    result.SHORT_NAME := ordersDB.UniQuery1.FieldByName('SHORT_NAME').AsString;
    result.staff_fields_invoice_to := ordersDB.UniQuery1.FieldByName('BILL_ADDRESS_BLOCK').AsString;
    result.START_DATE := ordersDB.UniQuery1.FieldByName('START_DATE').AsString;
    result.BILL_ADDRESS := ordersDB.UniQuery1.FieldByName('BILL_ADDRESS').AsString;
    result.BILL_CITY := ordersDB.UniQuery1.FieldByName('BILL_CITY').AsString;
    result.BILL_STATE :=  ordersDB.UniQuery1.FieldByName('BILL_STATE').AsString;
    result.BILL_ZIP := ordersDB.UniQuery1.FieldByName('BILL_ZIP').AsString;
    result.START_DATE := ordersDB.UniQuery1.FieldByName('START_DATE').AsString;
    result.BILL_CONTACT := ordersDB.UniQuery1.FieldByName('BILL_CONTACT').AsString;
    result.PHONE := ordersDB.UniQuery1.FieldByName('PHONE').AsString;
    result.END_DATE := ordersDB.UniQuery1.FieldByName('END_DATE').AsString;
    result.QB_LIST_ID := ordersDB.UniQuery1.FieldByName('QB_LIST_ID').AsString;
    result.FFAX := ordersDB.UniQuery1.FieldByName('FAX').AsString;
    result.REP_USER_ID := ordersDB.UniQuery1.FieldByName('REP_USER_ID').AsString;

    result.SHIPPING_ADDRESS_LIST := TList<TAddressItem>.Create;
    TXDataOperationContext.Current.Handler.ManagedObjects.Add( Result.SHIPPING_ADDRESS_LIST );
    while not ordersDB.UniQuery1.Eof do
    begin
      ADDRESS := TAddressItem.Create;
      TXDataOperationContext.Current.Handler.ManagedObjects.Add( ADDRESS );
      ADDRESS.ADDRESS := ordersDB.UniQuery1.FieldByName('ship_block').AsString;
      ADDRESS.shipping_address := ordersDB.UniQuery1.FieldByName('address').AsString;
      ADDRESS.city := ordersDB.UniQuery1.FieldByName('city').AsString;
      ADDRESS.state := ordersDB.UniQuery1.FieldByName('state').AsString;
      ADDRESS.zip := ordersDB.UniQuery1.FieldByName('zip').AsString;
      ADDRESS.contact := ordersDB.UniQuery1.FieldByName('contact').AsString;
      ADDRESS.ship_id := ordersDB.UniQuery1.FieldByName('customer_ship_id').AsString;
      result.SHIPPING_ADDRESS_LIST.Add(ADDRESS);
      ordersDB.UniQuery1.Next;
    end;

  logger.Log(3, 'GetCustomer Finished');
  except
    on E: Exception do
    begin
      Logger.Log(2, 'Error in GetCustomer: ' + E.Message);
      raise EXDataHttpException.Create(500, 'Unable to retrieve customer: A KG Orders database issue has occurred!');
    end;
  end;
end;

function TLookupService.GetRepUsers: TList<TUserItem>;
// Gets a list of users that qualify to be reps for companies
var
  USER: TUserItem;
  SQL: string;
begin
  Logger.Log(3, 'TLookupService.GetRepUsers');
  SQL := 'SELECT USER_ID, NAME, STATUS from users ORDER BY NAME';
  result := TList<TUserItem>.Create;
  doQuery(ordersDB.uqUsers, SQL);
  while not ordersDB.uqUsers.Eof do
  begin
    USER := TUserItem.Create;
    TXDataOperationContext.Current.Handler.ManagedObjects.Add( USER );
    USER.userID := ordersDB.uqUsersUSER_ID.AsString;
    USER.full_name := ordersDB.uqUsersNAME.AsString;
    USER.representative := ordersDB.uqUsersREPRESENTATIVE.AsString;
    result.Add(USER);
    ordersDB.uqUsers.Next;
  end;
  logger.Log(3, 'GetRepUsers finished');
end;


function TLookupService.GenerateOrderListPDF(searchOptions: string): string;
// Generates a PDF for the Order Page
var
  SQL: string;
  rptOrderList: TrptOrderList;
  CompanyID, CompanyName: string;
  params: TStringList;
begin
  logger.Log(3, 'TLookupService.GenerateOrderListPDF');
  rptOrderList := TrptOrderList.Create(nil);
  params := TStringList.Create;
  try
    try
      params.StrictDelimiter := true;
      params.Delimiter := '&';
      params.DelimitedText := searchOptions;

      // Strip paging if this is a PDF export
      if params.Values['forPDF'] = 'true' then
      begin
        params.Values['pagesize'] := '';
        params.Values['pagenumber'] := '';
      end;

      companyID := params.Values['companyID'];
      if companyID <> '' then
      begin
        SQL := 'Select NAME from customers c where CUSTOMER_ID = ' + companyID;
        doQuery(ordersDB.UniQuery1, SQL);
        CompanyName := 'Company: ' + ordersDB.UniQuery1.FieldByName('NAME').AsString;
      end
      else
        CompanyName := '';

      SQL := GenerateOrdersSQL(params.DelimitedText).SQL;
      result := rptOrderList.PrepareReport(SQL, CompanyName);

      Logger.log(5, 'PDF Report successfully generated for searchOptions: ' + params.DelimitedText);
    except
      on E: Exception do
      begin
        logger.Log(2, 'An error has occurred in TLookupServiceImpl.GenerateOrderListPDF: ' + E.Message);
        raise EXDataHttpException.Create(500, 'Failed to generate PDF: A KG Orders database issue has occurred!');
      end;
    end;
  finally
    rptOrderList.Free;
    params.Free;
  end;
end;


function TLookupService.AddShippingAddress(AddressInfo: string): TJSONObject;
var
  JSONData: TJSONObject;
  SQL: string;
  Pair: TJSONPair;
  Field: TField;
  ShipID: integer;
  mode: string;
  msg: string;
  ADDRESS: TJSONObject;
  ADDRESS_LIST:TJSONArray;
  CustomerID: string;
begin
  logger.Log(3, 'TLookupSerivce.AddShippingAddress');
  result := TJSONObject.Create;
  JSONData := TJSONObject.ParseJSONValue(AddressInfo) as TJSONObject;

  if JSONData = nil then
    raise Exception.Create('Invalid JSON format');  // If parsing fails, raise an exception
  mode := JSONData.GetValue<string>('mode');
  CustomerID := JSONData.GetValue<string>('customer_id');



  if mode = 'ADD' then
    SQL := 'select * from customers_ship where customer_id = 0 and customer_id <> 0'
  else
  begin
    ShipID := JSONData.GetValue<integer>('customer_ship_id');
    SQL := 'select * from customers_ship where customer_ship_id = ' + IntToStr(ShipID);
  end;
  logger.Log(5, 'Retrieving Address with SQL ' + SQL);
  doQuery(ordersDB.UniQuery1, SQL);

  try
    if mode = 'ADD' then
      ordersDB.UniQuery1.Insert
    else
      ordersDB.UniQuery1.Edit;

    for Pair in JSONData do
    begin
      Field := ordersDB.UniQuery1.FindField(Pair.JsonString.Value); // Checks if the field exists in the dataset
      if Assigned(Field) then
      begin
        if (Field is TDateTimeField) then
        begin
          if (Pair.JsonValue.Value = '') or (Pair.JsonValue.Value = 'null') or (Pair.JsonValue.Value = '12/30/1899') then
            Field.Clear // This sets the field to NULL (empty)
          else
            TDateTimeField(Field).AsDateTime := StrToDate(Pair.JsonValue.Value);
        end
        else if Pair.JsonValue.Value <> '' then
          Field.AsString := Pair.JsonValue.Value;
      end;
    end;
    ordersDB.UniQuery1.Post;

    if mode = 'ADD' then
    begin
      msg := 'Success: Shipping Address Successfully Added';
    end
    else
      msg := 'Success: Shipping Address Successfully Edited';
    logger.Log(3, msg);
    // Sends the updated Address List Back.

    SQL := 'select * FROM customers c LEFT JOIN customers_ship s ON c.CUSTOMER_ID = s.customer_id WHERE c.CUSTOMER_ID = ' + CustomerID;
    logger.Log(5, 'Retrieving updated customer address list with SQL: ' + SQL);
    doQuery(ordersDB.UniQuery1, SQL);
    ADDRESS_LIST := TJSONArray.Create;
    while not ordersDB.UniQuery1.Eof do
    begin
      ADDRESS := TJSONObject.Create;
      ADDRESS.AddPair('ADDRESS', ordersDB.UniQuery1.FieldByName('ship_block').AsString);
      ADDRESS.AddPair('shipping_address', ordersDB.UniQuery1.FieldByName('address').AsString);
      ADDRESS.AddPair('city', ordersDB.UniQuery1.FieldByName('city').AsString);
      ADDRESS.AddPair('state', ordersDB.UniQuery1.FieldByName('state').AsString);
      ADDRESS.AddPair('zip', ordersDB.UniQuery1.FieldByName('zip').AsString);
      ADDRESS.AddPair('contact', ordersDB.UniQuery1.FieldByName('contact').AsString);
      ADDRESS.AddPair('ship_id', ordersDB.UniQuery1.FieldByName('customer_ship_id').AsString);
      ADDRESS_LIST.Add(ADDRESS);
      ordersDB.UniQuery1.Next;
    end;

    Result.AddPair('status', msg);
    Result.AddPair('ADDRESS', ADDRESS_LIST);
    TXDataOperationContext.Current.Handler.ManagedObjects.Add(Result);
    logger.Log(3, 'AddShippingAddress finished');
    except
    on E: Exception do
    begin
      Result.AddPair('error', E.Message);
    end
  end;
end;

function TLookupService.AddCustomer(customerInfo: string): TJSONObject;
var
  JSONData: TJSONObject;
  SQL: string;
  Pair: TJSONPair;
  Field: TField;
  DateFormat: TFormatSettings;
  CustomerID: integer;
  mode: string;
  msg: string;
  unique: boolean;
begin
  logger.Log(3, 'TLookupService.AddCustomer');
  DateFormat := TFormatSettings.Create;
  DateFormat.ShortDateFormat := 'yyyy-mm-dd';
  DateFormat.DateSeparator := '-';
  JSONData := TJSONObject.ParseJSONValue(customerInfo) as TJSONObject;

  if JSONData = nil then
    raise Exception.Create('Invalid JSON format');  // If parsing fails, raise an exception
  mode := JSONData.GetValue<string>('mode');

  if mode = 'ADD' then
  begin
    // Update RevisionID
    SQL := 'UPDATE idfield set KEYVALUE = KEYVALUE + 1 WHERE KEYNAME = ' + quotedStr('GEN_CUSTOMER_ID');
    OrdersDB.UniQuery1.SQL.Text := SQL;
    OrdersDB.UniQuery1.ExecSQL;

    // Retrieve updated RevisionID
    SQL := 'select KEYVALUE from idfield where KEYNAME = ' + quotedStr('GEN_CUSTOMER_ID');
    doQuery(OrdersDB.UniQuery1, SQL);
    CustomerID := OrdersDB.UniQuery1.FieldByName('KEYVALUE').AsInteger;
  end
  else
    CustomerID := JSONData.GetValue<integer>('CUSTOMER_ID');

  SQL := 'select CUSTOMER_ID from customers where SHORT_NAME = ' + quotedStr(JSONData.GetValue<string>('SHORT_NAME'));
  logger.Log(5, 'Retrieving customer with SQL: ' + SQL);
  doQuery(OrdersDB.UniQuery1, SQL);

  if mode = 'ADD' then
  begin
    if OrdersDB.UniQuery1.IsEmpty then
      unique := true
    else
      unique := false;
  end
  else
  begin
    if (  (OrdersDB.UniQuery1.IsEmpty) or (OrdersDB.UniQuery1.FieldByName('CUSTOMER_ID').AsInteger = CustomerID) ) then
      unique := true
    else
      unique := false;

  end;

  if unique then
  begin
    if mode = 'ADD' then
      SQL := 'select * from customers where CUSTOMER_ID = 0 and CUSTOMER_ID <> 0'
    else
    begin
      SQL := 'select * from customers where CUSTOMER_ID = ' + IntToStr(CustomerID);
    end;
    doQuery(ordersDB.UniQuery1, SQL);

    try
      if mode = 'ADD' then
        ordersDB.UniQuery1.Insert
      else
        ordersDB.UniQuery1.Edit;

      for Pair in JSONData do
      begin
        Field := ordersDB.UniQuery1.FindField(Pair.JsonString.Value); // Checks if the field exists in the dataset
        if Assigned(Field) then
        begin
          if (Field is TDateTimeField) then
          begin
            if (Pair.JsonValue.Value = '') or (Pair.JsonValue.Value = 'null') or (Pair.JsonValue.Value = '12/30/1899') then
              Field.Clear // This sets the field to NULL (empty)
            else
              TDateTimeField(Field).AsDateTime := StrToDate(Pair.JsonValue.Value);
          end
          else if Pair.JsonValue.Value <> '' then
            Field.AsString := Pair.JsonValue.Value;
        end;
      end;

      ordersDB.UniQuery1.FieldByName('CUSTOMER_ID').AsInteger := CustomerID;

      // Post the record to the database
      ordersDB.UniQuery1.Post;

      if mode = 'ADD' then
        msg := 'Success: Customer Successfully Added'
      else
        msg := 'Success: Customer Successfully Edited';

      logger.Log(3, msg);

      Result := TJSONObject.Create.AddPair('status', msg);
      Result.AddPair('CustomerID', CustomerID);
      TXDataOperationContext.Current.Handler.ManagedObjects.Add(Result);
      except
      on E: Exception do
      begin
        Result := TJSONObject.Create.AddPair('error', E.Message);
      end
    end;
  end
  else
  begin
    Result := TJSONObject.Create.AddPair('status', 'Failure: Company Account Name Must Be Unique');
    logger.Log(3, 'Failure: Company Account Name Must Be Unique');
  end;
end;

function TLookupService.GenerateOrderCorrugatedPDF(orderID: string): string;
var
  SQL: string;
  rptOrderCorrugated: TrptOrderCorrugated; // Local instance of the report
begin
  rptOrderCorrugated := TrptOrderCorrugated.Create(nil);
  logger.Log(3, 'TLookupService.GenerateOrderCorrugatedPDF');
  try
    try
      // Generate SQL query for a single order
      SQL := 'SELECT * FROM corrugated_plate_orders c join orders o on c.ORDER_ID = o.ORDER_ID WHERE c.ORDER_ID = ' + orderID;

      // Prepare the report with the query
      Result := rptOrderCorrugated.PrepareReport(SQL);

      // Optionally log success
      Logger.Log(3, 'PDF Report successfully generated for order ID: ' + orderID);
    except
      on E: Exception do
      begin
        Logger.Log(2, 'Error generating corrugated PDF: ' + E.Message);
        raise EXDataHttpException.Create(500, 'Error generating corrugated PDF: A KG Orders database issue has occurred!');
      end;
    end;
  finally
    rptOrderCorrugated.Free;
  end;
end;

function TLookupService.GenerateOrderWebPDF(orderID: string): string;
var
  SQL: string;
  rptOrderWeb: TrptOrderWeb; // Local instance of the report
begin
  logger.Log(3, 'TLookupService.GenerateOrderWebPDF');
  rptOrderWeb := TrptOrderWeb.Create(nil);
  try
    try
      // Generate SQL query for a single order
      //SQL := 'SELECT * FROM web_plate_orders w WHERE w.ORDER_ID = ' + orderID ;
      SQL := 'SELECT * FROM web_plate_orders w JOIN orders o ON w.ORDER_ID = o.ORDER_ID WHERE w.ORDER_ID = ' + orderID;

      // Prepare the report with the query
      Result := rptOrderWeb.PrepareReport(SQL);

      // Optionally log success
      Logger.Log(3, 'PDF Report successfully generated for order ID: ' + orderID);
    except
      on E: Exception do
      begin
        Logger.Log(1, 'Error generating web PDF: ' + E.Message);
        raise EXDataHttpException.Create(500, 'Error generating web PDF: A KG Orders database issue has occurred!');
      end;
    end;
  finally
    rptOrderWeb.Free;
  end;
end;

function TLookupService.GenerateOrderCuttingPDF(orderID: string): string;
var
  SQL: string;
  rptOrderCutting: TrptOrderCutting; // Local instance of the report
begin
  rptOrderCutting := TrptOrderCutting.Create(nil);
  logger.Log(3, 'TLookupService.GenerateOrderCuttingPDF');
  try
    try
      // Generate SQL query for a single order
      SQL := 'SELECT * FROM cutting_die_orders c JOIN orders o on c.ORDER_ID = o.ORDER_ID WHERE c.ORDER_ID = ' + orderID;

      // Prepare the report with the query
      Result := rptOrderCutting.PrepareReport(SQL);

      // Optionally log success
      Logger.Log(3, 'PDF Report successfully generated for order ID: ' + orderID);
    except
      on E: Exception do
      begin
        Logger.Log(1, 'Error generating cutting die PDF: ' + E.Message);
        raise EXDataHttpException.Create(500, 'Error generating cutting die PDF: A KG Orders database issue has occurred!');
      end;
    end;
  finally
    rptOrderCutting.Free;
  end;
end;

function TLookupService.generateSubQuery(currStatus: string): string;
// Generates the subquery in order to retrieve all the status due/done dates
// This must be a subquery because there are at most 5 different entries which
// causes the query to return 5 different entries on a join. There is probably
// a work around but I couldn't get the SQL to work with it. Manually goes through
// all 5 statuses.
// currStatus: current status SQL is getting generated for.
begin
  result := '(select oss.STATUS_DATE from orders_status_schedule oss ' +
              'where oss.ORDER_ID = o.ORDER_ID and oss.ORDER_STATUS = ' + quotedStr(currStatus) + ') AS '+ currStatus + '_DUE,';

  result := result +'(select os.STATUS_TIMESTAMP from orders_status os where os.ORDER_ID = o.ORDER_ID and os.ORDER_STATUS = ' +
            quotedStr(currStatus) + ' order by os.STATUS_TIMESTAMP desc LIMIT 1) AS ' + currStatus + '_DONE, ';
end;

function TLookupService.generateStatusSelectSQL(statusTableShort: string; statusTableLong: string; startDate: string; endDate: string; statusType: string): string;
// Generates the SQL query to figure out whether or not an entry exists within
// a given time frame.
// statusTableShort: short name for the status tables (os or oss)
// statusTableLong: full name for the status table
// startDate/endDate: starting date/ending date for when the status is due/done.
begin
  result := 'exists ( select 1 from ' + statusTableLong + ' ' +
              statusTableShort + ' where ' + statusTableShort + '.ORDER_ID = ' +
              'o.ORDER_ID AND ' + statusTableShort + '.ORDER_STATUS = ' +
              quotedStr(statusType);

  if  startDate <> '' then
  begin
    result := result + ' AND ' + quotedStr(startDate) + ' <= STATUS_DATE';
  end;

  if ( ( endDate <> '1899/12/30' ) AND ( endDate <> '' ) ) then
  begin
    result := result + ' AND ' + quotedStr(endDate) + ' >= STATUS_DATE';
  end;

  result := result + ' AND STATUS_DATE IS NOT NULL)';
end;

function TLookupService.createStatusSearchInfo(params: TStringList; statusNum: string): TStatusSearchInfo;
// Takes all the status info received from the client and puts it into an object
// for convenience and to make it easier to expand. Returns said object.
var
  info: TStatusSearchInfo;
  filterValue: string;
  parts: TArray<string>;
begin
  info := TStatusSearchInfo.Create;
  logger.log(3, 'TLookupService.createStatusSearchInfo');
  try
    info.startDate := params.Values['startDate' + statusNum];
    info.endDate := params.Values['endDate' + statusNum];

    if params.Values['null' + statusNum] <> '' then
      info.null := StrToBool(params.Values['null' + statusNum]);

    info.statusType := '';
    info.filterType := '';
    info.statusSuffix := '';

    filterValue := params.Values['filterType' + statusNum];
    if (filterValue <> '') and (filterValue <> 'NONE') then
    begin
      parts := filterValue.Split([' ']);
      if Length(parts) > 0 then
        info.statusType := parts[0];
      if Length(parts) > 1 then
        info.statusSuffix := parts[1];

      if (info.statusType <> '') and (info.statusSuffix <> '') then
        info.filterType := info.statusType + '_' + info.statusSuffix;
    end;

    // Figure out what table the status belongs to
    if info.statusSuffix = 'DUE' then
    begin
      info.statusTableShort := 'oss';
      info.statusTableLong := 'orders_status_schedule';
      info.altStatusTableShort := 'os';
      info.altStatusTableLong := 'orders_status';
    end
    else
    begin
      info.statusTableShort := 'os';
      info.statusTableLong := 'orders_status';
      info.altStatusTableShort := 'oss';
      info.altStatusTableLong := 'orders_status_schedule';
    end;

    Result := info; // hand ownership to the caller
    logger.Log(3, 'StatusSearchInfo generated successfully');
  except
    info.Free;      // avoid leak if anything above raises
    raise;
  end;
end;

function TLookupService.generateStatusWhereSQL(status: TStatusSearchInfo): string;
// Generates the where SQL for each status to apply the filters used from the
// clients search.
// status - status information obtained from the client.
begin
  if status.filterType <> 'ORDER_DATE' then
    begin
    if status.null then
    begin
      result := result + ' AND not exists (select 1 from ' + status.statusTableLong +
                              ' ' + status.statusTableShort + ' where '
                              + status.statusTableShort +'.ORDER_ID = o.ORDER_ID ' +
                              'AND ' + status.statusTableShort + '.ORDER_STATUS = ' +
                              quotedStr(status.statusType) + ')';
    end
    else
      result := result + ' AND ' + generateStatusSelectSQL(status.statusTableShort, status.statusTableLong, status.startDate, status.endDate, status.statusType);
  end
  else
  begin
    if status.null then
    begin
      result := result + ' AND ' + quotedStr(status.startDate) + ' IS NULL';
    end
    else
    begin
      if  status.startDate <> '' then
      begin
        result := result + ' AND ' + quotedStr(status.startDate) + ' <= COALESCE(cpo.staff_fields_order_date, wpo.staff_fields_order_date, cdo.staff_fields_order_date)';
      end;

      if ( ( status.endDate <> '1899/12/30' ) AND ( status.endDate <> '' ) ) then
      begin
        result := result + ' AND ' + quotedStr(status.endDate) + ' >= COALESCE(cpo.staff_fields_order_date, wpo.staff_fields_order_date, cdo.staff_fields_order_date)';
      end;
    end;
  end;
end;

function TLookupService.generateOrdersSQL(searchOptions: string): TSQLQuery;
// Generates the orderSQL to retrieve entries specified by the search recieved
// from the client.
// searchOptions: search information sent form client to be parsed.
var
  params: TStringList;
  PageNum, PageSize: Integer;
  OrderBy, offset, limit: string;
  SQL, whereSQL: string;
  OrderID, CompanyID, JobName, orderType: string;
  status1, status2: TStatusSearchInfo;
  ForPDF: Boolean;
  accessRights, userID: string;
  q: TSQLQuery;
begin
  q := TSQLQuery.Create;
  params := TStringList.Create;
  status1 := nil;
  status2 := nil;
  logger.log(3, 'TLookupService.generatingOrdersSQL');
  try
    try
      params.StrictDelimiter := True;
      params.Delimiter := '&';
      params.DelimitedText := searchOptions;

      ForPDF := SameText(params.Values['forPDF'], 'true');

      if not ForPDF then
      begin
        PageNum := StrToIntDef(params.Values['pagenumber'], 1);
        PageSize := StrToIntDef(params.Values['pagesize'], 500);
        offset := IntToStr((PageNum - 1) * PageSize);
        limit := IntToStr(PageSize);
      end;

      OrderBy := params.Values['orderby'] + ' ' + params.Values['direction'];
      orderType := params.Values['orderType'].ToLower;
      OrderID := params.Values['orderID'];
      CompanyID := params.Values['companyID'];
      JobName := params.Values['jobName'];
      accessRights := params.Values['accessRights'];
      userID := params.Values['userID'];

      status1 := createStatusSearchInfo(params, '1');
      status2 := createStatusSearchInfo(params, '2');

      SQL := 'SELECT o.ORDER_ID, c.SHORT_NAME, o.LOCATION AS Loc, c.NAME AS COMPANY_NAME, ' +
             'o.JOB_NAME, o.ORDER_TYPE, o.IN_QB, o.QB_ORDER_NUM,' +
             generateSubquery('PROOF') +
             generateSubquery('ART') +
             generateSubquery('PLATE') +
             generateSubquery('MOUNT') +
             generateSubquery('SHIP');

      whereSQL := ' FROM orders o JOIN customers c ON c.CUSTOMER_ID = o.COMPANY_ID ' +
                  'LEFT JOIN qb_sales_orders qb ON qb.ORDER_ID = o.ORDER_ID ' +
                  'LEFT JOIN corrugated_plate_orders cpo ON o.ORDER_ID = cpo.ORDER_ID ' +
                  'LEFT JOIN web_plate_orders wpo ON o.ORDER_ID = wpo.ORDER_ID ' +
                  'LEFT JOIN cutting_die_orders cdo ON o.ORDER_ID = cdo.ORDER_ID WHERE 0 = 0';

      if (status1.filterType <> '') and (status1.filterType <> 'NONE') then
        whereSQL := whereSQL + generateStatusWhereSQL(status1);
      if (status2.filterType <> '') and (status2.filterType <> 'NONE') then
        whereSQL := whereSQL + generateStatusWhereSQL(status2);

      if (orderType <> '') and (orderType <> 'any') then
      begin
        if orderType <> 'cutting die' then
          whereSQL := whereSQL + ' AND o.ORDER_TYPE = ' + QuotedStr(orderType + '_plate')
        else
          whereSQL := whereSQL + ' AND o.ORDER_TYPE = ' + QuotedStr('cutting_die');
      end;

      if OrderID <> '' then
        whereSQL := whereSQL + ' AND o.ORDER_ID = ' + OrderID;
      if CompanyID <> '' then
        whereSQL := whereSQL + ' AND c.CUSTOMER_ID = ' + CompanyID;
      if JobName <> '' then
        whereSQL := whereSQL + ' AND o.JOB_NAME LIKE ' + QuotedStr('%' + JobName + '%');

      if accessRights = 'SALES' then
        whereSQL := whereSQL + ' AND c.REP_USER_ID = ' + userID;

      SQL := SQL + ' o.PRICE, qb.QB_REF_NUM, ' +
                   'COALESCE(cpo.staff_fields_po_number, wpo.staff_fields_po_number, cdo.staff_fields_po_number) AS po_number, ' +
                   'COALESCE(cpo.staff_fields_quickbooks_item, wpo.staff_fields_quickbooks_item, cdo.staff_fields_quickbooks_item) AS quickbooks_item, ' +
                   'COALESCE(cpo.staff_fields_order_date, wpo.staff_fields_order_date, cdo.staff_fields_order_date) AS ORDER_DATE ';

      if not ForPDF then
        SQL := SQL + whereSQL + ' ORDER BY ' + OrderBy + ' LIMIT ' + limit + ' OFFSET ' + offset
      else
        SQL := SQL + whereSQL + ' ORDER BY ' + OrderBy;

      q.SQL := SQL;
      q.whereSQL := whereSQL;

      Result := q; // hand ownership of q to the caller
      logger.Log(4, 'Successfully generated orders SQL');
    except
      q.Free;      // only if we never handed it to the caller
      raise;
    end;
  finally
    status1.Free;
    status2.Free;
    params.Free;
  end;
end;

function TLookupService.getColorCount(colors: string): string;
// Colors are stored in a JSON in the database so this function parses the
// stringified JSON and returns the count of colors.
// colors: stringified JSON to be parsed.
var
  colorObject: TJSONObject;
  colorList: TJSONArray;
begin
  if colors = '' then
    Result := '0'
  else
  begin
    colorObject := TJSONObject.ParseJSONValue(colors) as TJSONObject;
    try
      if Assigned(colorObject) then
      begin
        colorList := colorObject.GetValue<TJSONArray>('items');
        if Assigned(colorList) then
          Result := IntToStr(colorList.Count)
        else
          Result := '0';
      end
      else
        Result := '0';
    finally
      colorObject.Free;
    end;
  end;
end;


function TLookupService.GetOrders(searchOptions: string): TOrderList;
var
  SQL: string;
  whereSQL: string;
  Order: TOrderItem;
  colors: string;
  ColorType: string;
  SQLQuery: TSQLQuery;
begin
  logger.Log(3, 'TLookupService.GetOrders');
  SQLQuery := generateOrdersSQL(searchOptions);
  TXDataOperationContext.Current.Handler.ManagedObjects.Add(SQLQuery);

  Result := TOrderList.Create;
  TXDataOperationContext.Current.Handler.ManagedObjects.Add(Result);
  Result.data := TList<TOrderItem>.Create;
  TXDataOperationContext.Current.Handler.ManagedObjects.Add(Result.data);

  try
    SQL := SQLQuery.SQL;
    whereSQL := SQLQuery.whereSQL;
    logger.Log(5, 'Getting orders with SQL query ' + SQL);

    doQuery(ordersDB.UniQuery1, SQL);

    while not ordersDB.UniQuery1.Eof do
    begin
      Order := TOrderItem.Create;
      TXDataOperationContext.Current.Handler.ManagedObjects.Add(Order);
      Result.data.Add(Order);

      with ordersDB.UniQuery1 do
      begin
        Order.DBID       := FieldByName('ORDER_ID').AsString;
        Order.In_QB      := FieldByName('IN_QB').AsString;
        Order.ID         := FieldByName('SHORT_NAME').AsString;
        Order.companyName := FieldByName('COMPANY_NAME').AsString;
        Order.jobName    := FieldByName('JOB_NAME').AsString;
        Order.orderDate  := FieldByName('ORDER_DATE').AsString;
        Order.proofDue   := FieldByName('PROOF_DUE').AsString;
        Order.proofDone  := FieldByName('PROOF_DONE').AsString;
        Order.artDue     := FieldByName('ART_DUE').AsString;
        Order.artDone    := FieldByName('ART_DONE').AsString;
        Order.plateDue   := FieldByName('PLATE_DUE').AsString;
        Order.plateDone  := FieldByName('PLATE_DONE').AsString;
        Order.mountDue   := FieldByName('MOUNT_DUE').AsString;
        Order.mountDone  := FieldByName('MOUNT_DONE').AsString;
        Order.shipDue    := FieldByName('SHIP_DUE').AsString;
        Order.shipDone   := FieldByName('SHIP_DONE').AsString;
        Order.price      := FieldByName('PRICE').AsString;
        Order.qbRefNum   := FieldByName('QB_ORDER_NUM').AsString;
        Order.orderType  := FieldByName('ORDER_TYPE').AsString.Replace('_', ' ');
      end;

      if ordersDB.UniQuery1.FieldByName('ORDER_TYPE').AsString = 'web_plate' then
      begin
        ColorType := 'quantity_and_colors_qty_colors';
        SQL := 'Select quantity_and_colors_qty_colors from web_plate_orders where order_id = ' + Order.DBID;
      end
      else
      begin
        ColorType := 'colors_colors';
        SQL := 'Select colors_colors from corrugated_plate_orders where order_id = ' + Order.DBID;
      end;

      doQuery(ordersDB.UniQuery2, SQL);
      colors := ordersDB.UniQuery2.FieldByName(ColorType).AsString;
      Order.colors := GetColorCount(colors);

      ordersDB.UniQuery1.Next;
    end;

    ordersDB.UniQuery1.Close;

    SQL := 'SELECT COUNT(*) AS total_count ' + whereSQL;
    doQuery(ordersDB.UniQuery1, SQL);
    Result.count := ordersDB.UniQuery1.FieldByName('total_count').AsInteger;
    ordersDB.UniQuery1.Close;
    logger.Log(4, 'Orders successfully retrieved');
  except
    on E: Exception do
    begin
      Logger.Log(1, 'Error in GetOrders: ' + E.Message);
      raise EXDataHttpException.Create(500, 'Unable to retrieve order list: A KG Orders database issue has occurred!');
    end;
  end;
end;


function TLookupService.GetCorrugatedOrder(orderInfo: string): TCorrugatedOrder;
// Gets on singular order from the database for the order entry page.
// orderInfo: the ORDER_ID.
var
  orderID: string;
  SQL: string;
begin
  logger.Log(3,'TLookupService.GetCorrugatedOrder');
  orderID := orderInfo;
  try
    SQL := 'select * from corrugated_plate_orders cpo JOIN customers c ON c.CUSTOMER_ID = cpo.COMPANY_ID join orders o on cpo.ORDER_ID = o.ORDER_ID where cpo.ORDER_ID = ' + quotedStr(orderID);
    doQuery(ordersDB.UniQuery1, SQL);

    result := TCorrugatedOrder.Create;

    // Company
    result.ORDER_ID := ordersDB.UniQuery1.FieldByName('ORDER_ID').AsInteger;
    result.COMPANY_ID := ordersDB.UniQuery1.FieldByName('COMPANY_ID').AsInteger;
    result.NAME := ordersDB.UniQuery1.FieldByName('NAME').AsString;
    result.SHORT_NAME := ordersDB.UniQuery1.FieldByName('SHORT_NAME').AsString;
    result.IN_QB := ordersDB.UniQuery1.FieldByName('IN_QB').AsString;
    result.QB_ORDER_NUM := ordersDB.UniQuery1.FieldByName('QB_ORDER_NUM').AsString;

    // Staff Fields
    result.staff_fields_order_date := ordersDB.UniQuery1.FieldByName('staff_fields_order_date').AsString;
    result.staff_fields_proof_date := ordersDB.UniQuery1.FieldByName('staff_fields_proof_date').AsString;
    result.staff_fields_ship_date := ordersDB.UniQuery1.FieldByName('staff_fields_ship_date').AsString;
    result.staff_fields_ship_via := ordersDB.UniQuery1.FieldByName('staff_fields_ship_via').AsString;
    result.staff_fields_quantity := ordersDB.UniQuery1.FieldByName('staff_fields_quantity').AsString;
    result.staff_fields_ship_to := ordersDB.UniQuery1.FieldByName('staff_fields_ship_to').AsString;
    result.staff_fields_po_number := ordersDB.UniQuery1.FieldByName('staff_fields_po_number').AsString;
    result.staff_fields_job_name := ordersDB.UniQuery1.FieldByName('staff_fields_job_name').AsString;
    result.staff_fields_quickbooks_item := ordersDB.UniQuery1.FieldByName('staff_fields_quickbooks_item').AsString;
    result.staff_fields_art_due := ordersDB.UniQuery1.FieldByName('staff_fields_art_due').AsString;
    result.staff_fields_plate_due := ordersDB.UniQuery1.FieldByName('staff_fields_plate_due').AsString;
    result.staff_fields_price := ordersDB.UniQuery1.FieldByName('staff_fields_price').AsString;
    result.staff_fields_mount_due := ordersDB.UniQuery1.FieldByName('staff_fields_mount_due').AsString;
    result.staff_fields_art_location := ordersDB.UniQuery1.FieldByName('staff_fields_art_location').AsString;
    result.staff_fields_invoice_to := ordersDB.UniQuery1.FieldByName('staff_fields_invoice_to').AsString;

    result.supplied_by_customer_color_copy := ordersDB.UniQuery1.FieldByName('supplied_by_customer_color_copy').AsString;
    result.supplied_by_customer_plates := ordersDB.UniQuery1.FieldByName('supplied_by_customer_plates').AsString;
    result.supplied_by_customer_sample_ca := ordersDB.UniQuery1.FieldByName('supplied_by_customer_sample_ca').AsString;
    result.supplied_by_customer_dimension := ordersDB.UniQuery1.FieldByName('supplied_by_customer_dimension').AsString;
    result.supplied_by_customer_e_mail := ordersDB.UniQuery1.FieldByName('supplied_by_customer_e_mail').AsString;
    result.supplied_by_customer_ftp := ordersDB.UniQuery1.FieldByName('supplied_by_customer_ftp').AsString;
    result.supplied_by_customer_other := ordersDB.UniQuery1.FieldByName('supplied_by_customer_other').AsString;
    result.supplied_by_customer_existing_ := ordersDB.UniQuery1.FieldByName('supplied_by_customer_existing_').AsString;
    result.supplied_by_customer_ref_art_p := ordersDB.UniQuery1.FieldByName('supplied_by_customer_ref_art_p').AsString;
    result.supplied_by_customer_ref_art_a := ordersDB.UniQuery1.FieldByName('supplied_by_customer_ref_art_a').AsString;

    // Layout
    result.layout_rsc_l := ordersDB.UniQuery1.FieldByName('layout_rsc_l').AsString;
    result.layout_rcs_w := ordersDB.UniQuery1.FieldByName('layout_rcs_w').AsString;
    result.layout_rcs_d := ordersDB.UniQuery1.FieldByName('layout_rcs_d').AsString;
    result.layout_die_cut_no := ordersDB.UniQuery1.FieldByName('layout_die_cut_no').AsString;
    result.layout_accross_no := ordersDB.UniQuery1.FieldByName('layout_accross_no').AsString;
    result.layout_around_no := ordersDB.UniQuery1.FieldByName('layout_around_no').AsString;
    result.layout_cad_file := ordersDB.UniQuery1.FieldByName('layout_cad_file').AsString;
    result.layout_excalibur_die := ordersDB.UniQuery1.FieldByName('layout_excalibur_die').AsString;
    result.layout_rsc_style := ordersDB.UniQuery1.FieldByName('layout_rsc_style').AsString;

    // Mounting & Colors & Proofing
    result.mounting_loose := ordersDB.UniQuery1.FieldByName('mounting_loose').AsString;
    result.mounting_sticky_bak := ordersDB.UniQuery1.FieldByName('mounting_sticky_bak').AsString;
    result.mounting_full_mount := ordersDB.UniQuery1.FieldByName('mounting_full_mount').AsString;

    result.mounting_strip_mount := ordersDB.UniQuery1.FieldByName('mounting_strip_mount').AsString;
    result.mounting_standard_setup := ordersDB.UniQuery1.FieldByName('mounting_standard_setup').AsString;
    result.mounting_custom_backing := ordersDB.UniQuery1.FieldByName('mounting_custom_backing').AsString;
    result.mounting_custom_adhesive := ordersDB.UniQuery1.FieldByName('mounting_custom_adhesive').AsString;

    result.colors_cylinder_size := ordersDB.UniQuery1.FieldByName('colors_cylinder_size').AsString;
    result.colors_machine_ident := ordersDB.UniQuery1.FieldByName('colors_machine_ident').AsString;
    result.colors_cross_hairs := ordersDB.UniQuery1.FieldByName('colors_cross_hairs').AsString;
    result.colors_clemson := ordersDB.UniQuery1.FieldByName('colors_clemson').AsString;
    result.colors_colors := ordersDB.UniQuery1.FieldByName('colors_colors').AsString;

    result.proofing_fax := ordersDB.UniQuery1.FieldByName('proofing_fax').AsString;
    result.proofing_fax_attn := ordersDB.UniQuery1.FieldByName('proofing_fax_attn').AsString;
    result.proofing_e_mail := ordersDB.UniQuery1.FieldByName('proofing_e_mail').AsString;
    result.proofing_e_mail_attn := ordersDB.UniQuery1.FieldByName('proofing_e_mail_attn').AsString;
    result.proofing_ship_to := ordersDB.UniQuery1.FieldByName('proofing_ship_to').AsString;
    result.proofing_full_size_panel := ordersDB.UniQuery1.FieldByName('proofing_full_size_panel').AsString;
    result.proofing_print_card := ordersDB.UniQuery1.FieldByName('proofing_print_card').AsString;
    result.proofing_wide_format := ordersDB.UniQuery1.FieldByName('proofing_wide_format').AsString;
    result.proofing_pdf_file := ordersDB.UniQuery1.FieldByName('proofing_pdf_file').AsString;
    result.proofing_other := ordersDB.UniQuery1.FieldByName('proofing_other').AsString;
    result.proofing_art_approved_as_is := ordersDB.UniQuery1.FieldByName('proofing_art_approved_as_is').AsString;
    result.proofing_approved_date := ordersDB.UniQuery1.FieldByName('proofing_approved_date').AsString;

    // Plates
    result.plates_thickness := ordersDB.UniQuery1.FieldByName('plates_thickness').AsString;
    result.plates_plate_material := ordersDB.UniQuery1.FieldByName('plates_plate_material').AsString;
    result.plates_job_number := ordersDB.UniQuery1.FieldByName('plates_job_number').AsString;

    // General
    result.general_special_instructions := ordersDB.UniQuery1.FieldByName('general_special_instructions').AsString;

    ordersDB.UniQuery1.Close;
    logger.Log(4, 'Corrugated order successfully retrieved');
  except
    on E: Exception do
    begin
      Logger.Log(2, 'Error in GetOrder: ' + E.Message);
      raise EXDataHttpException.Create(500, 'Unable to retrieve corrugated order: A KG Orders database issue has occurred!');
    end;
  end;
end;


function TLookupService.GetWebOrder(orderInfo: string): TWebOrder;
var
  orderID: string;
  SQL: string;
begin
  logger.Log(3, 'TLookupService.GetWebOrder');
  try
    orderID := orderInfo;
    SQL := 'select * from web_plate_orders wpo JOIN customers c ON c.CUSTOMER_ID = wpo.COMPANY_ID join orders o on wpo.ORDER_ID = o.ORDER_ID where wpo.ORDER_ID = ' + quotedStr(orderID);
    doQuery(ordersDB.UniQuery1, SQL);

    result := TWebOrder.Create;

    // Company
    result.ORDER_ID := ordersDB.UniQuery1.FieldByName('ORDER_ID').AsInteger;
    result.COMPANY_ID := ordersDB.UniQuery1.FieldByName('COMPANY_ID').AsInteger;
    result.NAME := ordersDB.UniQuery1.FieldByName('NAME').AsString;
    result.SHORT_NAME := ordersDB.UniQuery1.FieldByName('SHORT_NAME').AsString;
    result.IN_QB := ordersDB.UniQuery1.FieldByName('IN_QB').AsString;
    result.QB_ORDER_NUM := ordersDB.UniQuery1.FieldByName('QB_ORDER_NUM').AsString;

    // Staff Fields
    result.staff_fields_order_date := ordersDB.UniQuery1.FieldByName('staff_fields_order_date').AsString;
    result.staff_fields_proof_date := ordersDB.UniQuery1.FieldByName('staff_fields_proof_date').AsString;
    result.staff_fields_ship_date := ordersDB.UniQuery1.FieldByName('staff_fields_ship_date').AsString;
    result.staff_fields_ship_via := ordersDB.UniQuery1.FieldByName('staff_fields_ship_via').AsString;
    result.staff_fields_quantity := ordersDB.UniQuery1.FieldByName('staff_fields_quantity').AsString;
    result.staff_fields_ship_to := ordersDB.UniQuery1.FieldByName('staff_fields_ship_to').AsString;
    result.staff_fields_po_number := ordersDB.UniQuery1.FieldByName('staff_fields_po_number').AsString;
    result.staff_fields_job_name := ordersDB.UniQuery1.FieldByName('staff_fields_job_name').AsString;
    result.staff_fields_quickbooks_item := ordersDB.UniQuery1.FieldByName('staff_fields_quickbooks_item').AsString;
    result.staff_fields_art_due := ordersDB.UniQuery1.FieldByName('staff_fields_art_due').AsString;
    result.staff_fields_plate_due := ordersDB.UniQuery1.FieldByName('staff_fields_plate_due').AsString;
    result.staff_fields_price := ordersDB.UniQuery1.FieldByName('staff_fields_price').AsString;
    result.staff_fields_art_location := ordersDB.UniQuery1.FieldByName('staff_fields_art_location').AsString;
    result.staff_fields_invoice_to := ordersDB.UniQuery1.FieldByName('staff_fields_invoice_to').AsString;

    // Supplied by Customer
    result.supplied_by_customer_b_w_or_co := ordersDB.UniQuery1.FieldByName('supplied_by_customer_b_w_or_co').AsString;
    result.supplied_by_customer_plates := ordersDB.UniQuery1.FieldByName('supplied_by_customer_plates').AsString;
    result.supplied_by_customer_sample := ordersDB.UniQuery1.FieldByName('supplied_by_customer_sample').AsString;
    result.supplied_by_customer_dimension := ordersDB.UniQuery1.FieldByName('supplied_by_customer_dimension').AsString;
    result.supplied_by_customer_other := ordersDB.UniQuery1.FieldByName('supplied_by_customer_other').AsString;
    result.supplied_by_customer_disk := ordersDB.UniQuery1.FieldByName('supplied_by_customer_disk').AsString;
    result.supplied_by_customer_e_mail := ordersDB.UniQuery1.FieldByName('supplied_by_customer_e_mail').AsString;
    result.supplied_by_customer_ftp := ordersDB.UniQuery1.FieldByName('supplied_by_customer_ftp').AsString;
    result.supplied_by_customer_total_inc := ordersDB.UniQuery1.FieldByName('supplied_by_customer_total_inc').AsString;
    result.supplied_by_customer_sheets_us := ordersDB.UniQuery1.FieldByName('supplied_by_customer_sheets_us').AsString;
    result.supplied_by_customer_initials := ordersDB.UniQuery1.FieldByName('supplied_by_customer_initials').AsString;

    // Proofing
    result.proofing_pdf := ordersDB.UniQuery1.FieldByName('proofing_pdf').AsString;
    result.proofing_pdf_to := ordersDB.UniQuery1.FieldByName('proofing_pdf_to').AsString;
    result.proofing_pdf_date_1 := ordersDB.UniQuery1.FieldByName('proofing_pdf_date_1').AsString;
    result.proofing_pdf_date_2 := ordersDB.UniQuery1.FieldByName('proofing_pdf_date_2').AsString;
    result.proofing_pdf_date_3 := ordersDB.UniQuery1.FieldByName('proofing_pdf_date_3').AsString;
    result.proofing_full_size_ink_jet_for := ordersDB.UniQuery1.FieldByName('proofing_full_size_ink_jet_for').AsString;
    result.proofing_ink_jet_to := ordersDB.UniQuery1.FieldByName('proofing_ink_jet_to').AsString;
    result.proofing_ink_jet_to_2 := ordersDB.UniQuery1.FieldByName('proofing_ink_jet_to').AsString;
    result.proofing_ink_jet_date_1 := ordersDB.UniQuery1.FieldByName('proofing_ink_jet_date_1').AsString;
    result.proofing_ink_jet_date_2 := ordersDB.UniQuery1.FieldByName('proofing_ink_jet_date_2').AsString;
    result.proofing_color_contract := ordersDB.UniQuery1.FieldByName('proofing_color_contract').AsString;
    result.proofing_color_contrac_to := ordersDB.UniQuery1.FieldByName('proofing_color_contrac_to').AsString;
    result.proofing_color_contrac_date_1 := ordersDB.UniQuery1.FieldByName('proofing_color_contrac_date_1').AsString;
    result.proofing_color_contrac_date_2 := ordersDB.UniQuery1.FieldByName('proofing_color_contrac_date_2').AsString;
    result.proofing_digital_color_key := ordersDB.UniQuery1.FieldByName('proofing_digital_color_key').AsString;
    result.proofing_digital_color_to := ordersDB.UniQuery1.FieldByName('proofing_digital_color_to').AsString;
    result.proofing_digital_color_date_1 := ordersDB.UniQuery1.FieldByName('proofing_digital_color_date_1').AsString;

    // Colors
    result.quantity_and_colors_press_name := ordersDB.UniQuery1.FieldByName('quantity_and_colors_press_name').AsString;
    result.quantity_and_colors_anilox_info := ordersDB.UniQuery1.FieldByName('quantity_and_colors_anilox_info').AsString;
    result.quantity_and_colors_qty_colors := ordersDB.UniQuery1.FieldByName('quantity_and_colors_qty_colors').AsString;

    // Plate Marks
    result.plate_marks_microdots := ordersDB.UniQuery1.FieldByName('plate_marks_microdots').AsString;
    result.plate_marks_microdots_comments := ordersDB.UniQuery1.FieldByName('plate_marks_microdots_comments').AsString;
    result.plate_marks_crosshairs := ordersDB.UniQuery1.FieldByName('plate_marks_crosshairs').AsString;
    result.plate_marks_crosshairs_comments := ordersDB.UniQuery1.FieldByName('plate_marks_crosshairs').AsString;
    result.plate_marks_color_bars := ordersDB.UniQuery1.FieldByName('plate_marks_color_bars').AsString;
    result.plate_marks_color_bars_comments := ordersDB.UniQuery1.FieldByName('plate_marks_color_bars_comments').AsString;
    result.plate_marks_other := ordersDB.UniQuery1.FieldByName('plate_marks_other').AsString;
    result.plate_marks_other_comments := ordersDB.UniQuery1.FieldByName('plate_marks_other_comments').AsString;

    // Print Orientation
    result.print_orientation_print_orient := ordersDB.UniQuery1.FieldByName('print_orientation_print_orient').AsString;

    // Plate
    result.plates_plate_material := ordersDB.UniQuery1.FieldByName('plates_plate_material').AsString;
    result.plates_thickness := ordersDB.UniQuery1.FieldByName('plates_thickness').AsString;
    result.plates_job_number := ordersDB.UniQuery1.FieldByName('plates_job_number').AsString;

    // Layout
    result.layout_around := ordersDB.UniQuery1.FieldByName('layout_around').AsString;
    result.layout_accross := ordersDB.UniQuery1.FieldByName('layout_accross').AsString;
    result.layout_surface_print := ordersDB.UniQuery1.FieldByName('layout_surface_print').AsString;
    result.layout_reverse_print := ordersDB.UniQuery1.FieldByName('layout_reverse_print').AsString;
    result.layout_cylinder_repeat := ordersDB.UniQuery1.FieldByName('layout_cylinder_repeat').AsString;
    result.layout_cutoff_dimension := ordersDB.UniQuery1.FieldByName('layout_cutoff_dimension').AsString;
    result.layout_pitch := ordersDB.UniQuery1.FieldByName('layout_pitch').AsString;
    result.layout_teeth := ordersDB.UniQuery1.FieldByName('layout_teeth').AsString;
    result.layout_bleed := ordersDB.UniQuery1.FieldByName('layout_bleed').AsString;
    result.layout_cutback := ordersDB.UniQuery1.FieldByName('layout_cutback').AsString;
    result.layout_minimum_trap_dim := ordersDB.UniQuery1.FieldByName('layout_minimum_trap_dim').AsString;
    result.layout_maximum_trap_dim := ordersDB.UniQuery1.FieldByName('layout_maximum_trap_dim').AsString;

    // UPC
    result.upc_size := ordersDB.UniQuery1.FieldByName('upc_size').AsString;
    result.upc_bar_width_reduction := ordersDB.UniQuery1.FieldByName('upc_bar_width_reduction').AsString;
    result.upc_distortion_percent := ordersDB.UniQuery1.FieldByName('upc_distortion_percent').AsString;
    result.upc_distortion_amount := ordersDB.UniQuery1.FieldByName('upc_distortion_amount').AsString;

    // General
    result.general_comments := ordersDB.UniQuery1.FieldByName('general_comments').AsString;

    ordersDB.UniQuery1.Close;
    logger.Log(4, 'Web plate order successfully retrieved');
  except
    on E: Exception do
    begin
      Logger.Log(2, 'Error in GetWebOrder: ' + E.Message);
      raise EXDataHttpException.Create(500, 'Unable to retrieve web order: A KG Orders database issue has occurred!');
    end;
  end;
end;


function TLookupService.GetCuttingDieOrder(orderInfo: string): TCuttingDie;
var
  orderID: string;
  SQL: string;
begin
  logger.Log(3, 'TLookupService.GetCuttingDieOrder');
  try
    orderID := orderInfo;
    SQL := 'select * from cutting_die_orders cdo JOIN customers c ON c.CUSTOMER_ID = cdo.COMPANY_ID join orders o on cdo.ORDER_ID = o.ORDER_ID where cdo.ORDER_ID = ' + quotedStr(orderID);
    doQuery(ordersDB.UniQuery1, SQL);

    result := TCuttingDie.Create;

    // Company
    result.ORDER_ID := ordersDB.UniQuery1.FieldByName('ORDER_ID').AsInteger;
    result.COMPANY_ID := ordersDB.UniQuery1.FieldByName('COMPANY_ID').AsInteger;
    result.NAME := ordersDB.UniQuery1.FieldByName('NAME').AsString;
    result.SHORT_NAME := ordersDB.UniQuery1.FieldByName('SHORT_NAME').AsString;
    result.IN_QB := ordersDB.UniQuery1.FieldByName('IN_QB').AsString;
    result.QB_ORDER_NUM := ordersDB.UniQuery1.FieldByName('QB_ORDER_NUM').AsString;

    // Staff Fields
    result.staff_fields_order_date := ordersDB.UniQuery1.FieldByName('staff_fields_order_date').AsString;
    result.staff_fields_proof_date := ordersDB.UniQuery1.FieldByName('staff_fields_proof_date').AsString;
    result.staff_fields_ship_date := ordersDB.UniQuery1.FieldByName('staff_fields_ship_date').AsString;
    result.staff_fields_ship_via := ordersDB.UniQuery1.FieldByName('staff_fields_ship_via').AsString;
    result.staff_fields_quantity := ordersDB.UniQuery1.FieldByName('staff_fields_quantity').AsString;
    result.staff_fields_ship_to := ordersDB.UniQuery1.FieldByName('staff_fields_ship_to').AsString;
    result.staff_fields_po_number := ordersDB.UniQuery1.FieldByName('staff_fields_po_number').AsString;
    result.staff_fields_job_name := ordersDB.UniQuery1.FieldByName('staff_fields_job_name').AsString;
    result.staff_fields_quickbooks_item := ordersDB.UniQuery1.FieldByName('staff_fields_quickbooks_item').AsString;
    result.staff_fields_price := ordersDB.UniQuery1.FieldByName('staff_fields_price').AsString;
    result.staff_fields_invoice_to := ordersDB.UniQuery1.FieldByName('staff_fields_invoice_to').AsString;

    result.general_special_instructions := ordersDB.UniQuery1.FieldByName('general_special_instructions').AsString;

    ordersDB.UniQuery1.Close;

    logger.Log(4, 'Cutting Die order successfully retrieved');

  except
    on E: Exception do
    begin
      logger.Log(1, 'An Error has occurred in TLookupSerivceImpl.GetCuttingDieOrder: ' + E.Message);
      raise EXDataHttpException.Create(500, 'Could not retrieve cutting die order: A KG Orders database issue has occurred!');
    end;
  end;
end;


function TLookupService.GetItems(searchOptions: string): TItemList;
// retrieves all the quickbooks items for the items page on client.
// searchOptions: probably not needed but adds limits to the page to prevent
// table on client side from getting too long. This table currently has about 27
// entries so probably not needed.
var
  params: TStringList;
  PageNum: integer;
  PageSize: integer;
  OrderBy: string;
  offset: string;
  limit: string;
  SQL: string;
  item: TItemItem;
begin
  logger.Log(3, 'TLookupService.GetItems');
  params := TStringList.Create;
  try
    try
      params.StrictDelimiter := true;
      // parse the searchOptions
      params.Delimiter := '&';
      params.DelimitedText := searchOptions;

      SQL := 'select * from qb_items order by qb_item_name asc';

      if (  ( params.Values['pagenumber'] <> '' ) and ( params.Values['pagesize'] <> '' ) ) then
      begin
        pageNum := StrToInt(params.Values['pagenumber']);
        PageSize := StrToInt(params.Values['pagesize']);
        OrderBy := params.Values['orderby'];

        limit := IntToStr(PageSize);
        offset := IntToStr((PageNum - 1) * PageSize);
        SQL := SQL + ' limit ' + limit + ' offset ' + offset;
      end;

      doQuery(ordersDB.UniQuery1, SQL);

      Result:= TItemList.Create;
      Result.data := TList<TItemItem>.Create;
      TXDataOperationContext.Current.Handler.ManagedObjects.Add( Result.data );

      while not ordersDB.UniQuery1.Eof do
      begin
        item := TItemItem.Create;
        TXDataOperationContext.Current.Handler.ManagedObjects.Add( item );
        Result.data.Add( item );
        item.ID := ordersDB.UniQuery1.FieldByName('qb_items_id').AsString;
        item.name := ordersDB.UniQuery1.FieldByName('qb_item_name').AsString;
        item.description := ordersDB.UniQuery1.FieldByName('item_desc').AsString;
        item.status := ordersDB.UniQuery1.FieldByName('status').AsString;
        item.QB_ID := ordersDB.UniQuery1.FieldByName('qb_items_qb_id').AsString;

        ordersDB.UniQuery1.Next;
      end;
      ordersDB.UniQuery1.Close;
      SQL:= 'select count(*) as total_count from qb_items';
      doQuery(ordersDB.UniQuery1, SQL);
      Result.count := ordersDB.UniQuery1.FieldByName('total_count').AsInteger;
      ordersDB.UniQuery1.Close;

      logger.Log(4, 'Items list successfully retrieved');
    except
      on E: Exception do
      begin
        Logger.Log(2, 'Error in GetItems: ' + E.Message);
        raise EXDataHttpException.Create(500, 'Unable to retrieve item list:A KG Orders database issue has occurred!');
      end;
    end;
  finally
    params.Free;
  end;
end;

function TLookupService.GetUsers(searchOptions: string): TUserList;
// Returns all users, or one specific user when searchOptions <> ''.
// Uses parameterised SQL and the real column name USER_NAME.
var
  user: TUserItem;
begin
  logger.Log(3, 'TLookupService.GetUsers');
  try
    // Prepare and open the query
    ordersDB.UniQuery1.Close;

    if searchOptions = '' then
    begin
      ordersDB.UniQuery1.SQL.Text :=
        'SELECT * FROM users ORDER BY NAME ASC';
    end
    else
    begin
      ordersDB.UniQuery1.SQL.Text :=
        'SELECT * FROM users WHERE USER_NAME = :uname';
      ordersDB.UniQuery1.ParamByName('uname').AsString := searchOptions;
    end;

    ordersDB.UniQuery1.Open;

    // Build result list
    Result       := TUserList.Create;
    Result.data  := TList<TUserItem>.Create;
    TXDataOperationContext.Current.Handler.ManagedObjects.Add(Result.data);

    while not ordersDB.UniQuery1.Eof do
    begin
      user := TUserItem.Create;
      TXDataOperationContext.Current.Handler.ManagedObjects.Add(user);
      Result.data.Add(user);

      user.userID        := ordersDB.UniQuery1.FieldByName('USER_ID').AsString;
      user.username      := ordersDB.UniQuery1.FieldByName('USER_NAME').AsString;
      user.password      := ordersDB.UniQuery1.FieldByName('PASSWORD').AsString;
      user.full_name     := ordersDB.UniQuery1.FieldByName('NAME').AsString;
      user.status        := ordersDB.UniQuery1.FieldByName('STATUS').AsString;
      user.email_address := ordersDB.UniQuery1.FieldByName('EMAIL').AsString;
      user.AType         := ordersDB.UniQuery1.FieldByName('ACCESS_TYPE').AsString;
      user.rights        := ordersDB.UniQuery1.FieldByName('SYSTEM_RIGHTS').AsInteger;
      user.perspectiveID := ordersDB.UniQuery1.FieldByName('PERSPECTIVE_ID').AsString;
      user.QBID          := ordersDB.UniQuery1.FieldByName('QB_ID').AsString;

      ordersDB.UniQuery1.Next;
    end;

    ordersDB.UniQuery1.Close;

    // Get total-row count (keeps old behaviour)
    ordersDB.UniQuery1.SQL.Text :=
      'SELECT COUNT(*) AS total_count FROM users';
    ordersDB.UniQuery1.Open;
    Result.count := ordersDB.UniQuery1.FieldByName('total_count').AsInteger;
    ordersDB.UniQuery1.Close;
    logger.Log(4, 'Users list successfully retrieved');
  except
    on E: Exception do
    begin
      ordersDB.UniQuery1.Close;
      logger.Log(2, 'An error has occurred in TLookupServiceImpl.GetUsers: ' + E.Message);
      raise EXDataHttpException.Create(500,'Unable to retrieve users: A KG Orders database issue has occurred!');
    end;
  end;
end;


function TLookupService.EditUser(const editOptions: string): string;
// Edits the user.
// editOptions: all user information that will be changed.
var
  params: TStringList;
  user: string;
  password: string;
  full_name: string;
  status: string;
  email: string;
  access: string;
  rights: string;
  perspective: string;
  QB: string;
  SQL: string;
  newUser: string;
  hashString: string;
  hashPW: string;
begin
  logger.log(3, 'TLookupService.EditUser');
  params := TStringList.Create;
  try
    params.Delimiter := '&';
    params.StrictDelimiter := true;
    params.DelimitedText := editOptions;
    user := params.Values['username'];
    password := params.Values['password'];
    full_name := params.Values['fullname'];
    status := params.Values['status'];
    email := params.Values['email'];
    access := params.Values['access'];
    rights := params.Values['rights'];
    perspective := params.Values['perspective'];
    QB := params.Values['QB'];
    newUser := params.Values['newuser'];

    SQL := 'select * from users where USER_NAME = ' + QuotedStr(user);
    doQuery(ordersDB.UniQuery1, SQL);

    if ordersDB.UniQuery1.IsEmpty then
      Result := 'Failure:No such user found'
    else
    begin
      ordersDB.UniQuery1.Edit;

      if not newUser.IsEmpty then
        ordersDB.UniQuery1.FieldByName('USER_NAME').AsString := newUser;

      if not full_name.IsEmpty then
        ordersDB.UniQuery1.FieldByName('NAME').AsString := full_name;

      if not status.IsEmpty then
      begin
        if StrToBool(status) then
          ordersDB.UniQuery1.FieldByName('STATUS').AsString := 'ACTIVE'
        else
          ordersDB.UniQuery1.FieldByName('STATUS').AsString := 'INACTIVE'
      end;

      ordersDB.UniQuery1.FieldByName('EMAIL').AsString := email;

      if not access.IsEmpty then
        ordersDB.UniQuery1.FieldByName('ACCESS_TYPE').AsString := Access;

      if not rights.IsEmpty then
        ordersDB.UniQuery1.FieldByName('SYSTEM_RIGHTS').AsInteger := StrToInt(rights);

      if not perspective.IsEmpty then
        ordersDB.UniQuery1.FieldByName('PERSPECTIVE_ID').AsString := perspective;

      ordersDB.UniQuery1.FieldByName('QB_ID').AsString := QB;

      if((not (Password = 'hidden')) and (not (Password.IsEmpty))) then
      begin
        hashString := ordersDB.UniQuery1.FieldByName('NAME').AsString + password;
        hashPW := THashSHA2.GetHashString(hashString, THashSHA2.TSHA2Version.SHA512).ToUpper;
        ordersDB.UniQuery1.FieldByName('password').AsString := hashPW;
      end;

      ordersDB.UniQuery1.Post;
      Result := 'Success: User Successfully Edited';
      logger.Log(4, 'User Successfully Edited');
    end;
    ordersDB.UniQuery1.Close;
  finally
    params.Free;
  end;
end;

procedure TLookupService.AddToOrdersTable(mode, ORDER_TYPE: string; JSONData: TJSONObject);
var
SQL: string;
begin
  logger.Log(3, 'TLookupService.AddToOrdersTable');
  if mode = 'ADD' then
    begin
      SQL := 'select * from orders where ORDER_ID = 0 and ORDER_ID <> 0';
      doQuery(ordersDB.UniQuery1, SQL);
      ordersDB.UniQuery1.Insert;
    end
    else
    begin
      SQL := 'select * from orders where ORDER_ID = ' + JSONData.GetValue<string>('ORDER_ID');
      doQuery(ordersDB.UniQuery1, SQL);
      ordersDB.UniQuery1.Edit;
    end;

    ordersDB.UniQuery1.FieldByName('COMPANY_ID').AsString := JSONData.GetValue<string>('COMPANY_ID');
    ordersDB.UniQuery1.FieldByName('ORDER_TYPE').AsString := ORDER_TYPE;

    if mode = 'ADD' then
      ordersDB.UniQuery1.FieldByName('ORDER_DATE').AsDateTime := Now;

    if JSONData.GetValue<string>('staff_fields_price') = '' then
      ordersDB.UniQuery1.FieldByName('PRICE').AsString := '0'
    else
      ordersDB.UniQuery1.FieldByName('PRICE').AsString := JSONData.GetValue<string>('staff_fields_price');

    ordersDB.UniQuery1.FieldByName('JOB_NAME').AsString := JSONData.GetValue<string>('staff_fields_job_name');
    ordersDB.UniQuery1.FieldByName('USER_ID').AsString := JSONData.GetValue<string>('USER_ID');
    ordersDB.UniQuery1.FieldByName('LOCATION').AsString := '';

    ordersDB.UniQuery1.Post;

    ordersDB.UniQuery1.Close;
    logger.log(4, 'Order successfully added to orders table');
end;

function TLookupService.AddCorrugatedOrder(orderInfo: string): TJSONObject;
// Adds corrugated order to the database. This process is done in 3 different
// tables so if any changes are made make sure to check orders, corrugated_plate_orders
// and orders_status_schedule. This also functions as an edit function.
// orderInfo - all the inputted order information from client side.
var
  JSONData: TJSONObject;
  SQL: string;
  Pair: TJSONPair;
  Field: TField;
  DateFormat: TFormatSettings;
  ORDER_ID: integer;
  mode: string;
  msg: string;
begin
  logger.Log(3, 'TLookupService.AddCorrugatedOrder');
  DateFormat := TFormatSettings.Create;
  DateFormat.ShortDateFormat := 'yyyy-mm-dd';
  DateFormat.DateSeparator := '-';
  JSONData := TJSONObject.ParseJSONValue(orderInfo) as TJSONObject;

  if JSONData = nil then
    raise Exception.Create('Invalid JSON format');  // If parsing fails, raise an exception
  mode := JSONData.GetValue<string>('mode');

  AddToOrdersTable(mode, 'corrugated_plate', JSONData);

  if mode = 'ADD' then
  begin
    ordersDB.UniQuery1.SQL.Text := 'SELECT LAST_INSERT_ID() AS OrderID'; // Use database's method to get the last inserted ID
    ordersDB.UniQuery1.Open;
    ORDER_ID := ordersDB.UniQuery1.FieldByName('OrderID').AsInteger;
  end;

  if mode = 'ADD' then
    SQL := 'select * from corrugated_plate_orders where ORDER_ID = 0 and ORDER_ID <> 0'
  else
  begin
    ORDER_ID := JSONData.GetValue<integer>('ORDER_ID');
    SQL := 'select * from corrugated_plate_orders where ORDER_ID = ' + IntToStr(ORDER_ID);
  end;
  doQuery(ordersDB.UniQuery1, SQL);
  try
    if mode = 'ADD' then
      ordersDB.UniQuery1.Insert
    else
      ordersDB.UniQuery1.Edit;

    for Pair in JSONData do
    begin
      Field := ordersDB.UniQuery1.FindField(Pair.JsonString.Value); // Checks if the field exists in the dataset
      if Assigned(Field) then
      begin
        if (Field is TDateTimeField) then
        begin
          if (Pair.JsonValue.Value = '') or (Pair.JsonValue.Value = 'null') or (Pair.JsonValue.Value = '12/30/1899') then
            Field.Clear // This sets the field to NULL (empty)
          else
            TDateTimeField(Field).AsDateTime := StrToDate(Pair.JsonValue.Value);
        end
        else
          Field.AsString := Pair.JsonValue.Value;
      end;
    end;

    ordersDB.UniQuery1.FieldByName('ORDER_ID').AsInteger := ORDER_ID;
    ordersDB.UniQuery1.Post;

    if ( JSONData.GetValue<string>('staff_fields_proof_date') <> '' ) and ( JSONData.GetValue<string>('staff_fields_proof_date') <> '12/30/1899' ) then
      AddStatusSchedule('PROOF', JSONData, ORDER_ID);
    if ( JSONData.GetValue<string>('staff_fields_ship_date') <> '' ) and ( JSONData.GetValue<string>('staff_fields_ship_date') <> '12/30/1899' ) then
      AddStatusSchedule('SHIP', JSONData, ORDER_ID);
    if ( JSONData.GetValue<string>('staff_fields_art_due') <> '' ) and ( JSONData.GetValue<string>('staff_fields_art_due') <> '12/30/1899' ) then
      AddStatusSchedule('ART', JSONData, ORDER_ID);
    if ( JSONData.GetValue<string>('staff_fields_plate_due') <> '' ) and ( JSONData.GetValue<string>('staff_fields_plate_due') <> '12/30/1899' ) then
      AddStatusSchedule('PLATE', JSONData, ORDER_ID);
    if ( JSONData.GetValue<string>('staff_fields_mount_due') <> '' ) and ( JSONData.GetValue<string>('staff_fields_mount_due') <> '12/30/1899' ) then
      AddStatusSchedule('MOUNT', JSONData, ORDER_ID);

    AddToRevisionsTable(intToStr(ORDER_ID), 'corrugated_plate_orders_revisions', JSONData);

   if mode = 'ADD' then
      msg := 'Success: Order Successfully Added'
    else
      msg := 'Success: Order Successfully Edited';

    logger.Log(4, msg);
    Result := JSONData;
    Result.AddPair('status', msg);
    Result.AddPair('ORDER_ID', ORDER_ID);
    TXDataOperationContext.Current.Handler.ManagedObjects.Add(Result);
    except
    on E: Exception do
    begin
      Logger.Log(2, 'Error in AddCorrugatedOrder: ' + E.Message);
      raise EXDataHttpException.Create(500, 'Unable to add or edit web order: A KG Orders database issue has occurred!');
    end
  end;
end;


function TLookupService.AddStatusSchedule(StatusType: string; order: TJSONObject; ORDER_ID: integer): string;
// Adds/edits orders_status_schedule table.
// StatusType: name of the status getting added to the schedule.
// Order: JSON object containing all of the order info sent from client
// ORDER_ID: order id sent from client if we are in edit mode empty if in add mode
var
  SQL: string;
  change: boolean;
  date: string;
begin
  logger.Log(3, 'TLookupService.AddStatusSchedule');

  SQL := 'select * from orders_status_schedule where ORDER_ID = ' + IntToStr(ORDER_ID) + ' AND ORDER_STATUS = ' + quotedStr(StatusType);
  doQuery(ordersDB.uqOrdersStatusSchedule, SQL);

  if( (StatusType = 'PROOF') or (StatusType = 'SHIP') ) then
  begin
    date := order.GetValue<string>('staff_fields_'+ StatusType.ToLower + '_date');
  end
  else
    date := order.GetValue<string>('staff_fields_'+ StatusType.ToLower +'_due');

  if ordersDB.uqOrdersStatusSchedule.IsEmpty then
  begin
    ordersDB.uqOrdersStatusSchedule.Insert;
    ordersDB.uqOrdersStatusScheduleORDER_REVISION.AsInteger := 1;
    ordersDB.uqOrdersStatusScheduleORIGINAL_STATUS_DATE.AsString := date;
    ordersDB.uqOrdersStatusScheduleUSER_ID.AsString := order.GetValue<string>('USER_ID');
  end
  else
  begin
    ordersDB.uqOrdersStatusSchedule.Edit;
    change := ordersDB.uqOrdersStatusScheduleSTATUS_DATE.AsDateTime <> StrToDateTime(date);
    if change then
    begin
      ordersDB.uqOrdersStatusScheduleORDER_REVISION.AsInteger := ordersDB.uqOrdersStatusScheduleORDER_REVISION.AsInteger + 1;
      ordersDB.uqOrdersStatusScheduleUSER_ID.AsString := order.GetValue<string>('USER_ID');
    end;
  end;
  ordersDB.uqOrdersStatusScheduleSTATUS_DATE.AsDateTime := StrToDateTime(date);
  ordersDB.uqOrdersStatusScheduleORDER_ID.AsInteger := ORDER_ID;
  ordersDB.uqOrdersStatusScheduleORDER_STATUS.AsString := StatusType;

  ordersDB.uqOrdersStatusSchedule.Post;

  logger.Log(4, 'Status schedule succesfully added');
  Result := 'success';
end;

function TLookupService.SetStatus(statusOptions: string): string;
var
  params: TStringList;
  StatusInfo: TJSONObject;
  ORDER_ID: integer;
  Date: String;
  Status: string;
  UserID: string;
  SQL: string;
  table: string;
  OrderType: string;
  order: TJSONObject;
begin
  logger.Log(3, 'TLookupService.SetStatus');
  StatusInfo := TJSONObject.ParseJSONValue(statusOptions) as TJSONObject;
  params := TStringList.Create;
  try
    try
      params.Delimiter := '&';
      params.StrictDelimiter := true;
      params.DelimitedText := statusOptions;
      ORDER_ID := StatusInfo.GetValue<integer>('ORDER_ID');
      Date := StatusInfo.GetValue<string>('date');
      Status := StatusInfo.GetValue<string>('status');
      UserID := StatusInfo.GetValue<string>('USER_ID');
      OrderType := StatusInfo.GetValue<string>('OrderType');

      Date := DateToStr(StrToDate(Date) + 1);

      SQL := 'select * from orders_status where ORDER_ID = ' + IntToStr(ORDER_ID) + ' AND ' +
              'ORDER_STATUS = ' + quotedStr(Status);

      logger.log(5, 'Retrieving order status with SQL: ' + SQL);

      doQuery(ordersDB.UniQuery1, SQL);

      if ordersDB.UniQuery1.IsEmpty then
      // Add Status
      begin
        ordersDB.UniQuery1.Insert;
        ordersDB.UniQuery1.FieldByName('ORDER_ID').AsString := IntToStr(ORDER_ID);
        ordersDB.UniQuery1.FieldByName('ORDER_STATUS').AsString := Status;
        ordersDB.UniQuery1.FieldByName('STATUS_DATE').AsDateTime := StrToDateTime(Date);
        ordersDB.UniQuery1.FieldByName('STATUS_TIMESTAMP').AsDateTime := Now;
        ordersDB.UniQuery1.FieldByName('USER_ID').AsString := UserID;
        ordersDB.UniQuery1.FieldByName('ORDER_REVISION').AsInteger := 1;
      end
      else
      // Edit Status
      begin
        ordersDB.UniQuery1.Edit;
        ordersDB.UniQuery1.FieldByName('STATUS_DATE').AsDateTime := StrToDateTime(Date);
        ordersDB.UniQuery1.FieldByName('STATUS_TIMESTAMP').AsDateTime := Now;
        ordersDB.UniQuery1.FieldByName('ORDER_REVISION').AsInteger := ordersDB.UniQuery1.FieldByName('ORDER_REVISION').AsInteger + 1;
      end;

      ordersDB.UniQuery1.Post;
      logger.Log(4, 'Order Status successfully posted to database');

      if StatusInfo.GetValue<string>('staff_fields_ship_date') <> '12/30/1899' then
        AddStatusSchedule('SHIP', StatusInfo, ORDER_ID);
      if StatusInfo.GetValue<string>('staff_fields_art_due') <> '12/30/1899' then
        AddStatusSchedule('ART', StatusInfo, ORDER_ID);
      if StatusInfo.GetValue<string>('staff_fields_plate_due') <> '12/30/1899' then
        AddStatusSchedule('PLATE', StatusInfo, ORDER_ID);
      if StatusInfo.GetValue<string>('staff_fields_mount_due') <> '12/30/1899' then
        AddStatusSchedule('MOUNT', StatusInfo, ORDER_ID);

      if Status <> 'SHIP' then
      begin
        order := TJSONObject.Create;
        try
          // update the order as well
          if OrderType = 'web plate'  then
            table := 'web_plate_orders'
          else if OrderType = 'cutting die' then
            table := 'cutting_die_orders'
          else
            table := 'corrugated_plate_orders';


          SQL := 'select * from ' + table + ' where ORDER_ID = ' + IntToStr(ORDER_ID);
          doQuery(OrdersDB.UniQuery1, SQL);
          logger.Log(4, 'Updating Orders status as well with SQL: ' + SQL);
          OrdersDB.UniQuery1.Edit;

          if StatusInfo.GetValue<string>('staff_fields_ship_date') <> '12/30/1899' then
            OrdersDB.UniQuery1.FieldByName('staff_fields_ship_date').AsString := StatusInfo.GetValue<string>('staff_fields_ship_date');
          if StatusInfo.GetValue<string>('staff_fields_art_due') <> '12/30/1899' then
            OrdersDB.UniQuery1.FieldByName('staff_fields_art_due').AsString := StatusInfo.GetValue<string>('staff_fields_art_due');
          if StatusInfo.GetValue<string>('staff_fields_plate_due') <> '12/30/1899' then
            OrdersDB.UniQuery1.FieldByName('staff_fields_plate_due').AsString := StatusInfo.GetValue<string>('staff_fields_plate_due');
          if StatusInfo.GetValue<string>('staff_fields_mount_due') <> '12/30/1899' then
            OrdersDB.UniQuery1.FieldByName('staff_fields_mount_due').AsString := StatusInfo.GetValue<string>('staff_fields_mount_due');
          OrdersDB.UniQuery1.Post;
          logger.Log(4, 'Order status successfully set');
        finally
          order.Free;
        end;
      end;

      result := 'success:Status Successfully set';
    except
    on E: Exception do
      logger.Log(2, 'An error occurred when setting status: ' + E.Message);
    end;
  finally
    params.Free;
  end;

end;


function TLookupService.AddUser(userInfo: string): string;
// Adds a user to the database
var
  user: string;
  password: string;
  full_name: string;
  status: string;
  email: string;
  access: string;
  rights: string;
  perspective: string;
  QB: string;
  SQL: string;
  rightsInt: Integer;
  params: TStringList;
begin
  logger.Log(3, 'TLookupService.AddUser');
  params := TStringList.Create;
  try
    try
      params.StrictDelimiter := True;
      params.Delimiter := '&';
      params.DelimitedText := userInfo;

      user := params.Values['username'];
      password := params.Values['password'];
      full_name := params.Values['fullname'];
      status := params.Values['status'];
      email := params.Values['email'];
      access := params.Values['access'];
      rights := params.Values['rights'];
      perspective := params.Values['perspective'];
      QB := params.Values['QB'];

      SQL := 'SELECT * FROM users WHERE USER_NAME = ' + QuotedStr(user.ToLower);
      ordersDB.UniQuery1.Close;
      ordersDB.UniQuery1.SQL.Text := SQL;
      ordersDB.UniQuery1.Open;

      if ordersDB.UniQuery1.IsEmpty then
      begin
        ordersDB.UniQuery1.Insert;

        ordersDB.UniQuery1.FieldByName('USER_NAME').AsString := user;
        ordersDB.UniQuery1.FieldByName('PASSWORD').AsString  := THashSHA2.GetHashString(full_name + password, THashSHA2.TSHA2Version.SHA512).ToUpper;
        ordersDB.UniQuery1.FieldByName('NAME').AsString      := full_name;

        if StrToBoolDef(status, False) then
          ordersDB.UniQuery1.FieldByName('STATUS').AsString := 'ACTIVE'
        else
          ordersDB.UniQuery1.FieldByName('STATUS').AsString := 'INACTIVE';

        ordersDB.UniQuery1.FieldByName('EMAIL').AsString       := email;
        ordersDB.UniQuery1.FieldByName('ACCESS_TYPE').AsString := access;

        if not TryStrToInt(rights, rightsInt) then
          rightsInt := 0;
        ordersDB.UniQuery1.FieldByName('SYSTEM_RIGHTS').AsInteger := rightsInt;

        ordersDB.UniQuery1.FieldByName('PERSPECTIVE_ID').AsString := perspective;
        ordersDB.UniQuery1.FieldByName('QB_ID').AsString           := QB;

        ordersDB.UniQuery1.Post;
        Result := 'Success: User successfully added';
      end
      else
        Result := 'Failure: Username already taken';

      logger.Log(4, Result);
    except
      on E: Exception do
      begin
        logger.Log(2, 'An error occurred in TlookupServiceImpl.AddUser: ' + E.Message);
        raise EXDataHttpException.Create(500, 'Unable to Add User: A KG Orders database issue has occurred!');
      end;
    end;
  finally
    params.Free;
  end;
end;



function TLookupService.AddItem(itemInfo: string): TJSONObject;
// Adds an item to the database
// itemInfo: item info to add to database
var
  JSONData: TJSONObject;
  Name: string;
  Description, mode: string;
  Status: string;
  SQL: string;
  ID: string;

begin
  try
    logger.Log(3, 'TLookupService.AddItem');
    result := TJSONObject.Create;
    JSONData := TJSONObject.ParseJSONValue(itemInfo) as TJSONObject;

    if JSONData = nil then
      raise Exception.Create('Invalid JSON format');  // If parsing fails, raise an exception
    mode := JSONData.GetValue<string>('mode');

    Name := JSONData.GetValue<string>('qb_item_name');
    Description := JSONData.GetValue<string>('item_desc');
    ID := JSONData.GetValue<string>('qb_items_id');
    Status := JSONData.GetValue<string>('status');

    if mode = 'ADD' then
    begin

      SQL := 'select * from qb_items where qb_item_name = ' + QuotedStr(Name);
      doQuery(ordersDB.UniQuery1, SQL);
      if true then //ordersDB.UniQuery1.IsEmpty then
      begin
        ordersDB.UniQuery1.Insert;

        ordersDB.UniQuery1.FieldByName('qb_item_name').AsString := Name;
        ordersDB.UniQuery1.FieldByName('item_desc').AsString := Description;
        ordersDB.UniQuery1.FieldByName('status').AsString := status;

        ordersDB.UniQuery1.FieldByName('qb_items_qb_id').AsString := JSONData.GetValue<string>('qb_items_qb_id');

        ordersDB.UniQuery1.Post;
        Result.AddPair('msg', 'Success: Item successfully added');
        Result.AddPair('description', ordersDB.UniQuery1.FieldByName('item_desc').AsString);
        Result.AddPair('name', ordersDB.UniQuery1.FieldByName('qb_item_name').AsString);
        Result.AddPair('status', ordersDB.UniQuery1.FieldByName('status').AsString);
      end
      else
        Result.AddPair('msg', 'Failure: Item already exists');
    end
    else
    begin
      SQL := 'select * from qb_items where qb_items_id = ' + ID;
      doQuery(ordersDB.UniQuery1, SQL);

      if ( not ordersDB.UniQuery1.IsEmpty ) then
      begin
        ordersDB.UniQuery1.Edit;

        ordersDB.UniQuery1.FieldByName('qb_item_name').AsString := Name;
        ordersDB.UniQuery1.FieldByName('item_desc').AsString := Description;
        ordersDB.UniQuery1.FieldByName('status').AsString := status;

        ordersDB.UniQuery1.Post;
        Result.AddPair('msg', 'Success: Item successfully edited');
      end
      else
        result.AddPair('msg', 'Failure: Item does not exist');

      logger.Log(4, result.GetValue('msg').Value);
    end;
    except
    on E: Exception do
      logger.Log(2, 'An error occurred when adding an item: ' + E.Message);
    end;
end;


function TLookupService.DelUser(username: string): string;
// deletes a user. not currently implemented definitely needs touching up to avoid
// deleting users prematurely.
// username: username to be deleted.
var
  SQL: string;
begin
  logger.Log(3, 'TLookupService.DelUser');
  SQL := 'select * from users where username = ' + QuotedStr(username.toLower);
  ordersDB.UniQuery1.Close;
  ordersDB.UniQuery1.SQL.Text := SQL;
  ordersDB.UniQuery1.Open;
  if ordersDB.UniQuery1.IsEmpty then
  begin
    Result := 'Failure:User does not exist';
  end
  else
  begin
    SQL:= 'DELETE FROM users where username = ' +  QuotedStr(username.toLower);
    ordersDB.UniQuery1.SQL.Text := SQL;
    ordersDB.UniQuery1.ExecSQL;
    Result := 'Success:User deleted';
  end;
end;

function TLookupService.AddWebOrder(orderInfo: string): TJSONObject;
// Adds corrugated order to the database. This process is done in 3 different
// tables so if any changes are made make sure to check orders, corrugated_plate_orders
// and orders_status_schedule. This also functions as an edit function.
// orderInfo - all the inputted order information from client side.
var
  JSONData: TJSONObject;
  SQL: string;
  Pair: TJSONPair;
  Field: TField;
  DateFormat: TFormatSettings;
  ORDER_ID: integer;
  mode: string;
  msg: string;
begin
  logger.Log(3, 'TLookupService.AddWebOrder');
  DateFormat := TFormatSettings.Create;
  DateFormat.ShortDateFormat := 'yyyy-mm-dd';
  DateFormat.DateSeparator := '-';
  JSONData := TJSONObject.ParseJSONValue(orderInfo) as TJSONObject;
  if JSONData = nil then
    raise Exception.Create('Invalid JSON format');
  mode := JSONData.GetValue<string>('mode');

  AddToOrdersTable(mode, 'web_plate', JSONData);

  if mode = 'ADD' then
  begin
    ordersDB.UniQuery1.SQL.Text := 'SELECT LAST_INSERT_ID() AS OrderID';
    ordersDB.UniQuery1.Open;
    ORDER_ID := ordersDB.UniQuery1.FieldByName('OrderID').AsInteger;
  end;

  if mode = 'ADD' then
    SQL := 'select * from web_plate_orders where ORDER_ID = 0 and ORDER_ID <> 0'
  else
  begin
    ORDER_ID := JSONData.GetValue<integer>('ORDER_ID');
    SQL := 'select * from web_plate_orders where ORDER_ID = ' + IntToStr(ORDER_ID);
  end;

  try
    doQuery(ordersDB.UniQuery1, SQL);

    if mode = 'ADD' then
      ordersDB.UniQuery1.Insert
    else
      ordersDB.UniQuery1.Edit;

    for Pair in JSONData do
    begin
      Field := ordersDB.UniQuery1.FindField(Pair.JsonString.Value);
      if Assigned(Field) then
      begin
        if (Field is TDateTimeField) then
        begin
          if (Pair.JsonValue.Value = '') or (Pair.JsonValue.Value = 'null') or (Pair.JsonValue.Value = '12/30/1899') then
            Field.Clear // This sets the field to NULL (empty)
          else
            TDateTimeField(Field).AsDateTime := StrToDate(Pair.JsonValue.Value);
        end
        else
          Field.AsString := Pair.JsonValue.Value;
      end;
    end;

    ordersDB.UniQuery1.FieldByName('ORDER_ID').AsInteger := ORDER_ID;

    ordersDB.UniQuery1.Post;

    if ( JSONData.GetValue<string>('staff_fields_proof_date') <> '' ) and ( JSONData.GetValue<string>('staff_fields_proof_date') <> '12/30/1899' ) then
      AddStatusSchedule('PROOF', JSONData, ORDER_ID);
    if ( JSONData.GetValue<string>('staff_fields_ship_date') <> '' ) and ( JSONData.GetValue<string>('staff_fields_ship_date') <> '12/30/1899' ) then
      AddStatusSchedule('SHIP', JSONData, ORDER_ID);
    if ( JSONData.GetValue<string>('staff_fields_art_due') <> '' ) and ( JSONData.GetValue<string>('staff_fields_art_due') <> '12/30/1899' ) then
      AddStatusSchedule('ART', JSONData, ORDER_ID);
    if ( JSONData.GetValue<string>('staff_fields_plate_due') <> '' ) and ( JSONData.GetValue<string>('staff_fields_plate_due') <> '12/30/1899' ) then
      AddStatusSchedule('PLATE', JSONData, ORDER_ID);

    AddToRevisionsTable(IntToStr(ORDER_ID), 'web_plate_orders_revisions', JSONData);

    if mode = 'ADD' then
      msg := 'Success: Order Successfully Added'
    else
      msg := 'Success: Order Successfully Edited';
    logger.Log(4, msg);

    Result := JSONData;
    Result.AddPair('status', msg);
    Result.AddPair('ORDER_ID', ORDER_ID);
    TXDataOperationContext.Current.Handler.ManagedObjects.Add(Result);
  except
    on E: Exception do
    begin
      Logger.Log(2, 'Error in AddWebOrder: ' + E.Message);
      raise EXDataHttpException.Create(500, 'Unable to add or edit web order: A KG Orders database issue has occurred!');
    end;
  end;
end;


function TLookupService.AddCuttingDieOrder(orderInfo: string): TJSONObject;
var
  JSONData: TJSONObject;
  SQL: string;
  Pair: TJSONPair;
  Field: TField;
  DateFormat: TFormatSettings;
  ORDER_ID: integer;
  mode: string;
  msg: string;
begin
  logger.Log(3, 'TLookupService.AddCuttingDieOrder');
  DateFormat := TFormatSettings.Create;
  DateFormat.ShortDateFormat := 'yyyy-mm-dd';
  DateFormat.DateSeparator := '-';
  JSONData := TJSONObject.ParseJSONValue(orderInfo) as TJSONObject;
  if JSONData = nil then
    raise Exception.Create('Invalid JSON format');  // If parsing fails, raise an exception
  mode := JSONData.GetValue<string>('mode');

  AddToOrdersTable(mode, 'cutting_die', JSONData);

  if mode = 'ADD' then
  begin
    ordersDB.UniQuery1.SQL.Text := 'SELECT LAST_INSERT_ID() AS OrderID'; // Use database's method to get the last inserted ID
    ordersDB.UniQuery1.Open;
    ORDER_ID := ordersDB.UniQuery1.FieldByName('OrderID').AsInteger;
  end;

  if mode = 'ADD' then
    SQL := 'select * from cutting_die_orders where ORDER_ID = 0 and ORDER_ID <> 0'
  else
  begin
    ORDER_ID := JSONData.GetValue<integer>('ORDER_ID');
    SQL := 'select * from cutting_die_orders where ORDER_ID = ' + IntToStr(ORDER_ID);
  end;
  doQuery(ordersDB.UniQuery1, SQL);
  try
    if mode = 'ADD' then
      ordersDB.UniQuery1.Insert
    else
      ordersDB.UniQuery1.Edit;

    for Pair in JSONData do
    begin
      Field := ordersDB.UniQuery1.FindField(Pair.JsonString.Value); // Checks if the field exists in the dataset
      if Assigned(Field) then
      begin
      // handles any dates or datetimes
        if (Field is TDateTimeField) then
        begin
          if (Pair.JsonValue.Value = '') or (Pair.JsonValue.Value = 'null') or (Pair.JsonValue.Value = '12/30/1899') then
            Field.Clear // This sets the field to NULL (empty)
          else
            TDateTimeField(Field).AsDateTime := StrToDate(Pair.JsonValue.Value);
        end
        else if Field is TStringField then
          Field.AsString := Pair.JsonValue.Value
        else
          Field.AsInteger := pair.JsonValue.AsType<Integer>;
      end;
    end;

    ordersDB.UniQuery1.FieldByName('ORDER_ID').AsInteger := ORDER_ID;

    // Post the record to the database
    ordersDB.UniQuery1.Post;

    if ( JSONData.GetValue<string>('staff_fields_proof_date') <> '' ) and ( JSONData.GetValue<string>('staff_fields_proof_date') <> '12/30/1899' ) then
      AddStatusSchedule('PROOF', JSONData, ORDER_ID);
    if ( JSONData.GetValue<string>('staff_fields_ship_date') <> '' ) and ( JSONData.GetValue<string>('staff_fields_ship_date') <> '12/30/1899' ) then
      AddStatusSchedule('SHIP', JSONData, ORDER_ID);

    AddToRevisionsTable(IntToStr(ORDER_ID), 'cutting_die_orders_revisions', JSONData);

    if mode = 'ADD' then
      msg := 'Success: Order Successfully Added'
    else
      msg := 'Success: Order Successfully Edited';
    logger.Log(4, msg);

    Result := JSONData;
    Result.AddPair('status', msg);
    Result.AddPair('ORDER_ID', ORDER_ID);
    TXDataOperationContext.Current.Handler.ManagedObjects.Add(Result);
  except
    on E: Exception do
    begin
      Logger.Log(2, 'Error in AddCuttingDieOrder: ' + E.Message);
      raise EXDataHttpException.Create(500, 'Unable to add cutting die order: A KG Orders database issue has occurred!');
    end;
  end;
end;


function TLookupService.delOrder(OrderID, orderType, UserID: string): TJSONObject;
var
  table, table2, SQL: string;
  stream: TStringStream;
  JSONValue: TJSONValue;
  JSONObject, DataObject, JSONData: TJSONObject;
  JSONArray: TJSONArray;
  Pair: TJSONPair;
  Field: TField;
  RevisionID, rev_num, OrderIDInt: Integer;
begin
  logger.Log(3, 'TLookupService.delOrder');
  try
    // Convert ORDER_ID to integer safely
    OrderIDInt := StrToIntDef(OrderID, -1);
    if OrderIDInt = -1 then
      raise Exception.Create('Invalid OrderID: not a valid integer');

    if orderType = 'corrugated' then
    begin
      table := 'corrugated_plate_orders';
      table2 := 'corrugated_plate_orders_revisions';
    end
    else if orderType = 'web' then
    begin
      table := 'web_plate_orders';
      table2 := 'web_plate_orders_revisions';
    end
    else
    begin
      table := 'cutting_die_orders';
      table2 := 'cutting_die_orders_revisions';
    end;

    // Get new revision ID
    SQL := 'UPDATE idfield SET KEYVALUE = KEYVALUE + 1 WHERE KEYNAME = ' + QuotedStr('GEN_ORDER_REVISION_ID');
    OrdersDB.UniQuery1.SQL.Text := SQL;
    OrdersDB.UniQuery1.ExecSQL;

    SQL := 'SELECT KEYVALUE FROM idfield WHERE KEYNAME = ' + QuotedStr('GEN_ORDER_REVISION_ID');
    doQuery(OrdersDB.UniQuery1, SQL);
    RevisionID := OrdersDB.UniQuery1.FieldByName('KEYVALUE').AsInteger;

    // Fetch the order to archive it
    SQL := 'SELECT * FROM ' + table + ' WHERE ORDER_ID = ' + IntToStr(OrderIDInt);
    doQuery(OrdersDB.UniQuery1, SQL);

    //convert the order into a JSON object to put it in the database using existing code
    logger.Log(4, 'Adding order into the revisions table before deleting...');
    stream := TStringStream.Create('', TEncoding.UTF8);
    try
      OrdersDB.UniQuery1.SaveToJSON(stream);
      stream.Position := 0;
      JSONValue := TJSONObject.ParseJSONValue(stream.DataString);

      if not Assigned(JSONValue) then
        raise Exception.Create('Invalid JSON content');

      if not (JSONValue is TJSONObject) then
        raise Exception.Create('Expected JSON object');

      JSONObject := TJSONObject(JSONValue);

      if not JSONObject.TryGetValue('data', DataObject) then
        raise Exception.Create('Missing "data" object in JSON');

      if not DataObject.TryGetValue('rows', JSONArray) then
        raise Exception.Create('Missing "rows" array in JSON data');

      if JSONArray.Count = 0 then
        raise Exception.Create('No order found with ORDER_ID = ' + IntToStr(OrderIDInt));

      JSONData := JSONArray.Items[0] as TJSONObject;

      // Get current max revision number
      SQL := 'SELECT MAX(REVISION_NUMBER) AS rev_num FROM ' + table2 + ' WHERE ORDER_ID = ' + IntToStr(OrderIDInt);
      doQuery(OrdersDB.UniQuery1, SQL);
      rev_num := OrdersDB.UniQuery1.FieldByName('rev_num').AsInteger + 1;

      // Insert into revisions
      SQL := 'SELECT * FROM ' + table2 + ' WHERE ORDER_ID = ' + IntToStr(OrderIDInt);
      doQuery(OrdersDB.UniQuery1, SQL);
      OrdersDB.UniQuery1.Insert;
      for Pair in JSONData do
      begin
        Field := OrdersDB.UniQuery1.FindField(Pair.JsonString.Value);
        if Assigned(Field) then
        begin
          if (Field is TDateTimeField) and (Pair.JsonValue.Value <> '') then
            TDateTimeField(Field).AsDateTime := ISO8601ToDate(Pair.JsonValue.Value, False)
          else if Pair.JsonValue.Value <> '' then
            Field.AsString := Pair.JsonValue.Value;
        end;
      end;

      OrdersDB.UniQuery1.FieldByName('ORDER_ID').AsInteger := OrderIDInt;
      OrdersDB.UniQuery1.FieldByName('ORDER_STATUS').AsString := 'DELETED';
      OrdersDB.UniQuery1.FieldByName('REVISION_NUMBER').AsInteger := rev_num;
      OrdersDB.UniQuery1.FieldByName('ORDER_REVISION_ID').AsInteger := RevisionID;
      OrdersDB.UniQuery1.FieldByName('REVISION_USER_ID').AsString := UserID;
      OrdersDB.UniQuery1.Post;
      logger.Log(4, 'Order successfully added into revision table, beginning deletion');
    finally
      stream.Free;
      JSONValue.Free;
    end;

    // Delete from order tables
    SQL := 'DELETE FROM ' + table + ' WHERE ORDER_ID = ' + IntToStr(OrderIDInt);
    OrdersDB.UniQuery1.SQL.Text := SQL;
    OrdersDB.UniQuery1.ExecSQL;

    SQL := 'DELETE FROM orders WHERE ORDER_ID = ' + IntToStr(OrderIDInt);
    OrdersDB.UniQuery1.SQL.Text := SQL;
    OrdersDB.UniQuery1.ExecSQL;

    // Return result
    Result := TJSONObject.Create;
    Result.AddPair('status', 'success');
    Result.AddPair('OrderID', IntToStr(OrderIDInt));
    TXDataOperationContext.Current.Handler.ManagedObjects.Add(Result);
    logger.log(4, 'order successfully deleted');
  except
    on E: Exception do
    begin
      Logger.Log(2, 'Error in delOrder: ' + E.Message);
      raise EXDataHttpException.Create(500, 'Unable to delete order: A KG Orders database issue has occurred!');
    end;
  end;
end;


procedure TLookupService.AddToRevisionsTable(OrderID: string; table: string; order: TJSONObject);
var
  SQL: string;
  Pair: TJSONPair;
  rev_num, RevisionID: integer;
  Field: TField;
begin
    // Get Revision Number
    SQL := 'select max(REVISION_NUMBER) as rev_num from ' + table + ' where ORDER_ID = ' + orderID;
    doQuery(ordersDB.UniQuery1, SQL);
    rev_num := ordersDB.UniQuery1.FieldByName('rev_num').AsInteger + 1;

    // Update RevisionID
    SQL := 'UPDATE idfield set KEYVALUE = KEYVALUE + 1 WHERE KEYNAME = ' + quotedStr('GEN_ORDER_REVISION_ID');
    OrdersDB.UniQuery1.SQL.Text := SQL;
    OrdersDB.UniQuery1.ExecSQL;

    // Retrieve updated RevisionID
    SQL := 'select KEYVALUE from idfield where KEYNAME = ' + quotedStr('GEN_ORDER_REVISION_ID');
    doQuery(OrdersDB.UniQuery1, SQL);
    RevisionID := OrdersDB.UniQuery1.FieldByName('KEYVALUE').AsInteger;

    SQL := 'select * from ' + table + ' where ORDER_ID = -1';
    doQuery(ordersDB.UniQuery1, SQL);

    ordersDB.UniQuery1.Insert;
    for Pair in order do
    begin
      Field := ordersDB.UniQuery1.FindField(Pair.JsonString.Value); // Checks if the field exists in the dataset
      if Assigned(Field) then
      begin
      // handles any dates or datetimes
        if (Field is TDateTimeField) then
           if (Pair.JsonValue.Value = '') or (Pair.JsonValue.Value = 'null') or (Pair.JsonValue.Value = '12/30/1899') then
              Field.Clear // This sets the field to NULL (empty)
           else
            TDateTimeField(Field).AsDateTime := StrToDate(Pair.JsonValue.Value)
        else if ( ( field is TStringField ) and ( Pair.JsonValue.Value <> '' ) ) then
          Field.AsString := Pair.JsonValue.Value
        else if field is TIntegerField  then
          Field.AsInteger := Pair.JsonValue.AsType<Integer>;
      end;
    end;

    ordersDB.UniQuery1.FieldByName('ORDER_ID').AsString := OrderID;
    ordersDB.UniQuery1.FieldByName('ORDER_DATE').AsDateTime := Now;
    ordersDB.UniQuery1.FieldByName('ORDER_STATUS').AsString := 'ACTIVE';
    ordersDB.UniQuery1.FieldByName('REVISION_NUMBER').AsInteger := rev_num;
    ordersDB.UniQuery1.FieldByName('ORDER_REVISION_ID').AsInteger := RevisionID;
    ordersDB.UniQuery1.FieldByName('REVISION_USER_ID').AsString := order.GetValue<string>('USER_ID');

    ordersDB.UniQuery1.Post;
end;


function TLookupService.getQBCustomers: TJSONArray;
var
  iniFile: TIniFile;
  restClient: TRESTClient;
  restRequest: TRESTRequest;
  restResponse: TRESTResponse;
  param: TRESTRequestParameter;
  res: string;
  jsValue: TJSONValue;
  ParsedCustomer, Customer, BillAddr: TJSONObject;
  jsObj: TJSONObject;
  PhoneObj: TJSONObject;
  CustomerList: TJSONArray;
  AccessToken, RefreshToken, CompanyID, Client, Secret: string;
  LastRefresh: TDateTime;
  I: integer;
  SQL: string;
begin
  logger.Log(3, 'TLookupService.GetQBCustomers');
  Result := TJSONArray.Create;
  iniFile := TIniFile.Create(ExtractFilePath(Application.ExeName) + 'kgOrdersServer.ini');
  restClient := TRESTClient.Create(nil);
  restRequest := TRESTRequest.Create(nil);
  restResponse := TRESTResponse.Create(nil);

  try
    try
      restRequest.Client := restClient;
      restRequest.Response := restResponse;

      if iniFile.ReadString('Quickbooks', 'LastRefresh', '') = '' then
        LastRefresh := 0
      else
        LastRefresh := StrToDateTime(iniFile.ReadString('Quickbooks', 'LastRefresh', ''));

      if MinutesBetween(Now, LastRefresh) > 58 then
        RefreshAccessToken();

      Client := iniFile.ReadString('Quickbooks', 'ClientID', '');
      Secret := iniFile.ReadString('Quickbooks', 'ClientSecret', '');
      CompanyID := iniFile.ReadString('Quickbooks', 'CompanyID', '');
      RefreshToken := iniFile.ReadString('Quickbooks', 'RefreshToken', '');
      AccessToken := iniFile.ReadString('Quickbooks', 'AccessToken', '');

      restClient.BaseURL := 'https://sandbox-quickbooks.api.intuit.com';
      restRequest.Method := rmGET;
      res := '/v3/company/' + CompanyID + '/query?query=select * from Customer&minorversion=75';
      restRequest.Resource := res;

      param := restRequest.Params.AddItem;
      param.Name := 'Authorization';
      param.Kind := pkHTTPHEADER;
      param.Options := param.Options + [TRESTRequestParameterOption.poDoNotEncode];
      param.Value := 'Bearer ' + AccessToken;

      restRequest.Execute;

      jsValue := restResponse.JSONValue;
      if not Assigned(jsValue) then
        Exit;

      jsObj := jsValue as TJSONObject;
      if not Assigned(jsObj) then
        Exit;

      CustomerList := jsObj.GetValue<TJSONArray>('QueryResponse.Customer');
      if not Assigned(CustomerList) then
        Exit;

      for I := 0 to CustomerList.Count - 1 do
      begin
        Customer := CustomerList.Items[I] as TJSONObject;
        ParsedCustomer := TJSONObject.Create;

        sql := 'select CUSTOMER_ID from customers where QB_LIST_ID = ' + Customer.GetValue<string>('Id');
        doQuery(ordersDB.UniQuery1, SQL);

        try
          ParsedCustomer.AddPair('In KGOrders', not(ordersDB.UniQuery1.IsEmpty));
          ParsedCustomer.AddPair('Id', Customer.GetValue<string>('Id'));
          ParsedCustomer.AddPair('CompanyName', Customer.GetValue<string>('DisplayName'));
          if Customer.TryGetValue<TJSONObject>('PrimaryPhone', PhoneObj) then
            ParsedCustomer.AddPair('PrimaryPhone', PhoneObj.GetValue<string>('FreeFormNumber'));

          // Handle Bill Address
          if Customer.GetValue('BillAddr') is TJSONObject then
          begin
            BillAddr := Customer.GetValue('BillAddr') as TJSONObject;
            ParsedCustomer.AddPair('BillAddrLine1', TJSONString.Create(BillAddr.GetValue<string>('Line1', '')));
            ParsedCustomer.AddPair('BillAddrCity', TJSONString.Create(BillAddr.GetValue<string>('City', '')));
            ParsedCustomer.AddPair('BillAddrState', TJSONString.Create(BillAddr.GetValue<string>('CountrySubDivisionCode', '')));
            ParsedCustomer.AddPair('BillAddrZip', TJSONString.Create(BillAddr.GetValue<string>('PostalCode', '')));
            ParsedCustomer.AddPair('BillAddr',
              TJSONString.Create(
                Customer.GetValue<string>('DisplayName') + sLineBreak +
                BillAddr.GetValue('Line1', '') + ',' + sLineBreak +
                BillAddr.GetValue('City', '') + ', ' +
                BillAddr.GetValue('CountrySubDivisionCode', '') + ' ' +
                BillAddr.GetValue('PostalCode', '')
              )
            );
          end;

          // Handle Ship Address
          if Customer.GetValue('ShipAddr') is TJSONObject then
          begin
            BillAddr := Customer.GetValue('ShipAddr') as TJSONObject;
            ParsedCustomer.AddPair('ShipAddrLine1', TJSONString.Create(BillAddr.GetValue<string>('Line1', '')));
            ParsedCustomer.AddPair('ShipAddrCity', TJSONString.Create(BillAddr.GetValue<string>('City', '')));
            ParsedCustomer.AddPair('ShipAddrState', TJSONString.Create(BillAddr.GetValue<string>('CountrySubDivisionCode', '')));
            ParsedCustomer.AddPair('ShipAddrZip', TJSONString.Create(BillAddr.GetValue<string>('PostalCode', '')));
            ParsedCustomer.AddPair('ShipAddr',
              TJSONString.Create(
                Customer.GetValue<string>('DisplayName') + sLineBreak +
                BillAddr.GetValue('Line1', '') + ',' + sLineBreak +
                BillAddr.GetValue('City', '') + ', ' +
                BillAddr.GetValue('CountrySubDivisionCode', '') + ' ' +
                BillAddr.GetValue('PostalCode', '')
              )
            );
          end;

          Result.AddElement(ParsedCustomer);
        except
          ParsedCustomer.Free;
          raise;
        end;
      end;
    except
      on E: Exception do
      begin
        Logger.Log(2, 'Error in getQBCustomers: ' + E.Message);
        raise EXDataHttpException.Create(500, 'Unable to retrieve QuickBooks Customers: A QuickBooks interface error has occurred!');
      end;
    end;
  finally
    iniFile.Free;
    restClient.Free;
    restRequest.Free;
    restResponse.Free;
  end;
end;


function TLookupService.GetQBItems: TJSONArray;
var
  restClient: TRESTClient;
  restRequest: TRESTRequest;
  restResponse: TRESTResponse;
  param: TRESTRequestParameter;
  res: string;
  jsValue: TJSONValue;
  jsObj: TJSONObject;
  ItemList: TJSONArray;
  ParsedItem, Item: TJSONObject;
  I: integer;
  AccessToken, RefreshToken, CompanyID, Client, Secret, SQL, desc: string;
  LastRefresh: TDateTime;
  iniFile: TIniFile;
begin
  logger.Log(3, 'TLookupService.GetQBItems');
  iniFile := nil;
  restClient := nil;
  restRequest := nil;
  restResponse := nil;
  try
    try
      Result := TJSONArray.Create;
      iniFile := TIniFile.Create(ExtractFilePath(Application.ExeName) + 'kgOrdersServer.ini');
      restClient := TRESTClient.Create(nil);
      restRequest := TRESTRequest.Create(nil);
      restResponse := TRESTResponse.Create(nil);
      restRequest.Client := restClient;
      restRequest.Response := restResponse;

      if iniFile.ReadString('Quickbooks', 'LastRefresh', '') = '' then
        LastRefresh := 0
      else
        LastRefresh := StrToDateTime(iniFile.ReadString('Quickbooks', 'LastRefresh', ''));

      if MinutesBetween(Now, LastRefresh) > 58 then
        RefreshAccessToken();

      Client := iniFile.ReadString('Quickbooks', 'ClientID', '');
      Secret := iniFile.ReadString('Quickbooks', 'ClientSecret', '');
      CompanyID := iniFile.ReadString('Quickbooks', 'CompanyID', '');
      RefreshToken := iniFile.ReadString('Quickbooks', 'RefreshToken', '');
      AccessToken := iniFile.ReadString('Quickbooks', 'AccessToken', '');

      restClient.BaseURL := 'https://sandbox-quickbooks.api.intuit.com';

      restRequest.Method := rmGET;
      res := '/v3/company/' + companyID + '/query?query=select * from Item&minorversion=75';
      restRequest.Resource := res;

      param := restRequest.Params.AddItem;
      param.Name := 'Authorization';
      param.Kind := pkHTTPHEADER;
      param.Options := param.Options + [TRESTRequestParameterOption.poDoNotEncode];
      param.Value := 'Bearer ' + AccessToken;

      restRequest.Execute;

      jsValue := restResponse.JSONValue;
      jsObj := TJSONObject(jsValue);
      ItemList := TJSONArray(TJSONObject(jsObj.GetValue('QueryResponse')).GetValue('Item'));

      for I := 0 to ItemList.Count - 1 do
      begin
        Item := ItemList.Items[I] as TJSONObject;
        ParsedItem := TJSONObject.Create;

        SQL := 'select qb_items_id from qb_items where qb_item_name = ' + QuotedStr(Item.GetValue<string>('Name'));
        doQuery(ordersDB.UniQuery1, SQL);

        ParsedItem.AddPair('qb_items_id', ordersDB.UniQuery1.FieldByName('qb_items_id').AsString);
        ParsedItem.AddPair('qb_item_name', Item.GetValue<string>('Name'));
        if Item.TryGetValue<string>('Description', desc) then
          ParsedItem.AddPair('item_desc', desc)
        else
          ParsedItem.AddPair('item_desc', 'N/A');
        ParsedItem.AddPair('status', Item.GetValue<string>('Active'));
        ParsedItem.AddPair('qb_items_qb_id', Item.GetValue<string>('Id'));

        Result.AddElement(ParsedItem);
      end;

    except
      on E: Exception do
      begin
        Logger.Log(2, 'Error in getQBCustomers: ' + E.Message);
        raise EXDataHttpException.Create(500, 'Unable to retrieve QuickBooks Items: A QuickBooks interface error has occurred!');
      end;
    end;

  finally
    iniFile.Free;
    restClient.Free;
    restRequest.Free;
    restResponse.Free;
  end;
end;

function TLookupService.RefreshAccessToken: string;
// Refresh Token changes so make sure to save refresh token.
var
  IdHTTP: TIdHTTP;
  SSLIO: TIdSSLIOHandlerSocketOpenSSL;
  RequestStream: TStringStream;
  EncodedAuth, PostData, response: string;
  JSObj: TJSONObject;
  iniFile: TIniFile;
  Encoder: TBase64Encoding;
  AccessToken,RefreshToken,CompanyID,Client,Secret: string;
begin
  iniFile := TIniFile.Create( ExtractFilePath(Application.ExeName) + 'kgOrdersServer.ini' );
  Client := iniFile.ReadString('Quickbooks', 'ClientID', '');
  Secret := iniFile.ReadString('Quickbooks', 'ClientSecret', '');
  CompanyID := iniFile.ReadString('Quickbooks', 'CompanyID', '');
  RefreshToken := iniFile.ReadString('Quickbooks', 'RefreshToken', '');
  // 1. Encode credentials (same as working Postman request)

  // TNetEncoding.Base64.Encode adds a new line every 72 chars, this stops that
  Encoder := TBase64Encoding.Create(0);

  if( (Client = '') or (Secret = '') ) then
  begin
    Logger.Log(2, 'Missing Client ID or Client Secret in INI File');
    Exit();
  end;

  EncodedAuth := Encoder.Encode(Client + ':' + Secret);
  if RefreshToken = '' then
  begin
    Logger.Log(3, 'Missing Refresh Token, Please Manually Get a New One and Store in INI File');
    Exit();
  end;

  // 2. Prepare POST data (EXACTLY as in Postman)
  PostData := 'grant_type=refresh_token&refresh_token=' + RefreshToken;

  // 3. Configure HTTP client
  IdHTTP := TIdHTTP.Create(nil);
  SSLIO := TIdSSLIOHandlerSocketOpenSSL.Create(nil);
  try
    // Force TLS 1.2
    SSLIO.SSLOptions.Method := sslvTLSv1_2;
    SSLIO.SSLOptions.SSLVersions := [sslvTLSv1_2];
    IdHTTP.IOHandler := SSLIO;

    // Set headers (EXACT match with Postman)
    IdHTTP.Request.ContentType := 'application/x-www-form-urlencoded';
    IdHTTP.Request.Accept := 'application/json';
    IdHTTP.Request.CustomHeaders.AddValue('Authorization', 'Basic ' + EncodedAuth);

    // 4. Create and send request
    RequestStream := TStringStream.Create(PostData, TEncoding.UTF8);
    try

      // Execute POST
      try
        response := IdHTTP.Post('https://oauth.platform.intuit.com/oauth2/v1/tokens/bearer', RequestStream);
        JSObj :=  TJSONObject.ParseJSONValue(response) as TJSONObject;
        RefreshToken := JSObj.GetValue('refresh_token').ToString.Trim(['"']);
        AccessToken := JSObj.GetValue('access_token').ToString.Trim(['"']);
        SaveTokens(AccessToken, RefreshToken);
        Result := AccessToken;
        Logger.Log(5, 'qbAPI - Tokens Successfully Saved');
      except
        on E: EIdHTTPProtocolException do
         // Memo2.Lines.Add('Error: ' + E.Message + #13#10 + 'Response: ' + E.ErrorMessage);
      end;
    finally
      RequestStream.Free;
    end;
  finally
    SSLIO.Free;
    IdHTTP.Free;
  end;
end;


procedure TLookupService.SaveTokens(AccessToken, RefreshToken: string);
var
  iniFile: TIniFile;
begin
  iniFile := TIniFile.Create( ExtractFilePath(Application.ExeName) + 'kgOrdersServer.ini' );
  try
    iniFile.WriteString('Quickbooks', 'RefreshToken', RefreshToken);
    iniFile.WriteString('Quickbooks', 'AccessToken', AccessToken);
    iniFile.WriteString('Quickbooks', 'LastRefresh', DateTimeToStr(Now));
    Logger.Log(5, 'Tokens Successfully Saved');

  finally
    IniFile.Free;
  end;
end;

function TLookupService.ImportQBCustomer(CustomerInfo: string): TJSONObject;
var
  JSONData: TJSONObject;
  SQL: string;
  Pair: TJSONPair;
  Field: TField;
  DateFormat: TFormatSettings;
  CustomerID: Integer;
  mode: string;
  msg: string;
  QB_LIST_ID: string;
begin
  logger.Log(3, 'TLookupService.ImportQBCustomer');
  DateFormat := TFormatSettings.Create;
  DateFormat.ShortDateFormat := 'yyyy-mm-dd';
  DateFormat.DateSeparator := '-';

  JSONData := TJSONObject.ParseJSONValue(CustomerInfo) as TJSONObject;
  if JSONData = nil then
    raise Exception.Create('Invalid JSON format');

  mode := JSONData.GetValue<string>('mode');
  QB_LIST_ID := JSONData.GetValue<string>('QB_LIST_ID');

  // Update RevisionID
  SQL := 'UPDATE idfield SET KEYVALUE = KEYVALUE + 1 WHERE KEYNAME = ' + QuotedStr('GEN_CUSTOMER_ID');
  OrdersDB.UniQuery1.SQL.Text := SQL;
  OrdersDB.UniQuery1.ExecSQL;

  // Retrieve updated RevisionID
  SQL := 'SELECT KEYVALUE FROM idfield WHERE KEYNAME = ' + QuotedStr('GEN_CUSTOMER_ID');
  doQuery(OrdersDB.UniQuery1, SQL);
  CustomerID := OrdersDB.UniQuery1.FieldByName('KEYVALUE').AsInteger;

  SQL := 'SELECT * FROM customers WHERE QB_LIST_ID = ' + QuotedStr(QB_LIST_ID);
  doQuery(OrdersDB.UniQuery1, SQL);

  try
    if OrdersDB.UniQuery1.IsEmpty then
    begin
      OrdersDB.UniQuery1.Insert;

      for Pair in JSONData do
      begin
        Field := OrdersDB.UniQuery1.FindField(Pair.JsonString.Value);
        if Assigned(Field) then
        begin
          if Field is TDateTimeField then
          begin
            if (Pair.JsonValue.Value = '') or (Pair.JsonValue.Value = 'null') or (Pair.JsonValue.Value = '12/30/1899') then
              Field.Clear
            else
              TDateTimeField(Field).AsDateTime := StrToDate(Pair.JsonValue.Value, DateFormat);
          end
          else if Pair.JsonValue.Value <> '' then
            Field.AsString := Pair.JsonValue.Value;
        end;
      end;

      OrdersDB.UniQuery1.FieldByName('CUSTOMER_ID').AsInteger := CustomerID;
      JSONData.AddPair('customer_id', TJSONNumber.Create(CustomerID));

      OrdersDB.UniQuery1.Post;

      if mode = 'ADD' then
        msg := 'Success: Customer Successfully Added'
      else
        msg := 'Success: Customer Successfully Edited';

      Result := TJSONObject.Create;
      Result.AddPair('status', msg);
      Result.AddPair('CustomerID', CustomerID);
      TXDataOperationContext.Current.Handler.ManagedObjects.Add(Result);

      // Add Shipping Information

      if JSONData.GetValue<string>('ship_block') <> '' then
      begin
        SQL := 'SELECT * FROM customers_ship WHERE customer_id = 0 AND customer_id <> 0';
        doQuery(OrdersDB.UniQuery1, SQL);
        OrdersDB.UniQuery1.Insert;

        for Pair in JSONData do
        begin
          Field := OrdersDB.UniQuery1.FindField(Pair.JsonString.Value);
          if Assigned(Field) then
          begin
            if Field is TDateTimeField then
            begin
              if (Pair.JsonValue.Value = '') or (Pair.JsonValue.Value = 'null') or (Pair.JsonValue.Value = '12/30/1899') then
                Field.Clear
              else
                TDateTimeField(Field).AsDateTime := StrToDate(Pair.JsonValue.Value, DateFormat);
            end
            else if Pair.JsonValue.Value <> '' then
              Field.AsString := Pair.JsonValue.Value;
          end;
        end;
        OrdersDB.UniQuery1.Post;
      end;

    end
    else
    begin
      msg := 'Failure:Customer Already in Database';
      CustomerID := OrdersDB.UniQuery1.FieldByName('CUSTOMER_ID').AsInteger;
      Result := TJSONObject.Create;
      Result.AddPair('status', msg);
      Result.AddPair('CustomerID', CustomerID);
    end;
  except
    on E: Exception do
    begin
      Result := TJSONObject.Create;
      Result.AddPair('error', E.Message);
    end;
  end;
end;


initialization
  RegisterServiceType(TLookupService);
end.

