unit qbAPI;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, AdvUtil, Data.DB, Vcl.Grids, AdvObj,
  BaseGrid, AdvGrid, DBAdvGrid, MemDS, DBAccess, Uni, Vcl.StdCtrls, Vcl.Mask,
  vcl.wwdbedit, vcl.wwdotdot, vcl.wwdbcomb, REST.Client, REST.Types, System.JSON,
  System.Generics.Collections, AdvEdit, vcl.wwdblook, vcl.wwdbdatetimepicker,
  System.Hash, Api.Database, Vcl.ExtCtrls, WEBLib.Forms, WEBLib.Controls, WEBLib.StdCtrls,
  WEBLib.ExtCtrls, WEBLib.REST, WEBLib.WebTools,System.Net.HttpClient,
  System.Net.URLClient, System.Net.HttpClientComponent, System.netencoding,
  IdHTTP, IdSSLOpenSSL, IdSSLOpenSSLHeaders, System.DateUtils, System.IniFiles,
  AdvPanel, AdvOfficePager;


type
  TfQB = class(TForm)
    uq: TUniQuery;
    uqORDER_ID: TIntegerField;
    uqCOMPANY_ID: TIntegerField;
    uqUSER_ID: TIntegerField;
    uqORDER_DATE: TDateTimeField;
    uqSTART_DATE: TDateField;
    uqEND_DATE: TDateField;
    uqORDER_STATUS: TStringField;
    uqSCHED_JSON: TStringField;
    uqstaff_fields_order_date: TDateField;
    uqstaff_fields_proof_date: TDateField;
    uqstaff_fields_ship_date: TDateField;
    uqstaff_fields_ship_via: TStringField;
    uqstaff_fields_price: TStringField;
    uqstaff_fields_invoice_to: TStringField;
    uqstaff_fields_invoice_attention: TStringField;
    uqstaff_fields_ship_to: TStringField;
    uqstaff_fields_ship_attention: TStringField;
    uqstaff_fields_po_number: TStringField;
    uqstaff_fields_job_name: TStringField;
    uqstaff_fields_art_due: TDateField;
    uqstaff_fields_plate_due: TDateField;
    uqplates_job_number: TStringField;
    uqsupplied_by_customer_b_w_or_co: TStringField;
    uqsupplied_by_customer_plates: TStringField;
    uqsupplied_by_customer_sample: TStringField;
    uqsupplied_by_customer_dimension: TStringField;
    uqsupplied_by_customer_other: TStringField;
    uqsupplied_by_customer_disk: TStringField;
    uqsupplied_by_customer_e_mail: TStringField;
    uqsupplied_by_customer_ftp: TStringField;
    uqplates_plate_material: TStringField;
    uqplates_thickness: TStringField;
    uqsupplied_by_customer_total_inc: TStringField;
    uqsupplied_by_customer_sheets_us: TStringField;
    uqsupplied_by_customer_initials: TStringField;
    uqproofing_pdf: TStringField;
    uqproofing_pdf_to: TStringField;
    uqproofing_pdf_date_1: TDateField;
    uqproofing_pdf_date_2: TDateField;
    uqproofing_pdf_date_3: TDateField;
    uqproofing_full_size_ink_jet_for: TStringField;
    uqproofing_ink_jet_to: TStringField;
    uqproofing_ink_jet_to_2: TStringField;
    uqproofing_ink_jet_date_1: TDateField;
    uqproofing_ink_jet_date_2: TDateField;
    uqproofing_ink_jet_date_3: TDateField;
    uqproofing_color_contract: TStringField;
    uqproofing_color_contrac_to: TStringField;
    uqproofing_color_contrac_date_1: TDateField;
    uqproofing_color_contrac_date_2: TDateField;
    uqproofing_digital_color_key: TStringField;
    uqproofing_digital_color_to: TStringField;
    uqproofing_digital_color_date_1: TDateField;
    uqquantity_and_colors_press_name: TStringField;
    uqquantity_and_colors_anilox_info: TStringField;
    uqplate_marks_microdots: TStringField;
    uqplate_marks_microdots_comments: TStringField;
    uqplate_marks_crosshairs: TStringField;
    uqplate_marks_crosshairs_comments: TStringField;
    uqplate_marks_color_bars: TStringField;
    uqplate_marks_color_bars_comments: TStringField;
    uqplate_marks_other: TStringField;
    uqplate_marks_other_comments: TStringField;
    uqprint_orientation_print_orient: TStringField;
    uqlayout_around: TStringField;
    uqlayout_accross: TStringField;
    uqlayout_surface_print: TStringField;
    uqlayout_reverse_print: TStringField;
    uqlayout_cylinder_repeat: TStringField;
    uqlayout_cutoff_dimension: TStringField;
    uqlayout_pitch: TStringField;
    uqlayout_teeth: TStringField;
    uqlayout_bleed: TStringField;
    uqlayout_cutback: TStringField;
    uqlayout_minimum_trap_dim: TStringField;
    uqlayout_maximum_trap_dim: TStringField;
    uqupc_size: TStringField;
    uqupc_bar_width_reduction: TStringField;
    uqquantity_and_colors_qty_colors: TStringField;
    uqgeneral_comments: TStringField;
    uqstaff_fields_quickbooks_item: TStringField;
    uqstaff_fields_quantity: TStringField;
    uqupc_distortion_percent: TStringField;
    uqupc_distortion_amount: TStringField;
    uqstaff_fields_art_location: TStringField;
    Splitter1: TSplitter;
    Splitter2: TSplitter;
    Panel1: TPanel;
    Button2: TButton;
    Button3: TButton;
    Button4: TButton;
    Button5: TButton;
    Button6: TButton;
    Button7: TButton;
    Button1: TButton;
    Button8: TButton;
    Button9: TButton;
    Button10: TButton;
    Button11: TButton;
    Button12: TButton;
    AdvPanel2: TAdvPanel;
    asgData2: TAdvStringGrid;
    AdvOfficePager1: TAdvOfficePager;
    AdvOfficePager11: TAdvOfficePage;
    AdvOfficePager12: TAdvOfficePage;
    AdvOfficePager13: TAdvOfficePage;
    Memo1: TMemo;
    Memo2: TMemo;
    Panel2: TPanel;
    Button13: TButton;
    Panel3: TPanel;
    Button14: TButton;
    AdvPanel1: TAdvPanel;
    asgData: TAdvStringGrid;
    Button15: TButton;
    procedure Button1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure Button4Click(Sender: TObject);
    procedure Button5Click(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure Button6Click(Sender: TObject);
    procedure Button7Click(Sender: TObject);
    procedure asgData2ClickCell(Sender: TObject; ARow, ACol: Integer);
    procedure asgDataClickCell(Sender: TObject; ARow, ACol: Integer);
    procedure Button8Click(Sender: TObject);
    procedure Button9Click(Sender: TObject);
    procedure Button10Click(Sender: TObject);
    procedure Button11Click(Sender: TObject);
    procedure Button12Click(Sender: TObject);
    procedure Button13Click(Sender: TObject);
    procedure Button14Click(Sender: TObject);
    procedure Button15Click(Sender: TObject);
  private
    { Private declarations }
    strict private
    ordersDB: TApiDatabase;
     httpReqTokenRefresh: TWebHttpRequest;

     var
      AccessToken,RefreshToken,CompanyID,Client,Secret: string;
      LastRefresh: TDateTime;
      ID, QB_ID: string;
  public
    { Public declarations }
    procedure findMatches(CustomerList: TJSONArray; JSONfield, DBField: string);
    procedure getCompanyInfo();
    procedure LoadJsonArray(jaData: TJSONArray);
    procedure LoadJsonArray2(jaData: TJSONArray);
    function RefreshAccessToken(): string;
    procedure ConfigureSSL(IOHandler: TIdSSLIOHandlerSocketOpenSSL);
    procedure SaveTokens(AccessToken, RefreshToken: string);
    procedure getCustomers();
    procedure addEstimate(orderInfo: string);
    procedure getQBCustomers();
    procedure ShowDeleteConfirm(msg: string);
    procedure DeleteCustomers();
    procedure LinkCustomer();
  end;

var
  fQB: TfQB;

implementation

uses
  Common.Logging, uLibrary, Lookup.Service, Lookup.ServiceImpl;

{$R *.dfm}

procedure TfQB.asgData2ClickCell(Sender: TObject; ARow, ACol: Integer);
var
  point, origin: TPoint;
begin
  origin := TPoint.Create(1,1);
  point := asgData.find(origin, asgData2.Cells[2, ARow], []);
  if point.Y = -1 then
    point.Y := 0;
  if point.X = -1 then
    point.X := 0;
  asgData.Row := point.Y;
  asgData.Col := point.X;

  if ( (point.X <> 0) and (point.Y <> 0) ) then
  begin
    QB_ID := asgData.Cells[1, asgData.Row];
    ID := asgData2.Cells[1, asgData2.Row];
  end
  else
    ID := asgData2.Cells[1, asgData2.Row];


end;

procedure TfQB.asgDataClickCell(Sender: TObject; ARow, ACol: Integer);
var
  point, origin: TPoint;
begin
  QB_ID := asgData.Cells[1, asgData.Row];
end;

procedure TfQB.Button10Click(Sender: TObject);
var
  msg: string;
begin
  Memo1.Clear;
  Memo1.Lines.Add('Linking QB IDs');
  if( ( QB_ID = '' ) or ( ID = '' ) ) then
  begin
    Memo1.Lines.Add('Please select a Quickbooks Customer and a KG Orders Customer');
    Memo1.Lines.Add('Current QB ID: ' + QB_ID);
    Memo1.Lines.Add('Current KG Orders ID: ' + ID);
  end
  else
  begin
    msg := 'Are you sure you want to link QB ID: ' + QB_ID + ' with KG Orders ID: ' + ID;
    if MessageDlg(msg, mtConfirmation, [mbYes, mbNo], 0) = mrYes then
    begin
      // User confirmed
      LinkCustomer();
    end
    else
    begin
      // User canceled
      ShowMessage('Cancelled.');
    end;
  end;
end;

procedure TfQB.LinkCustomer();
var
  sql: string;
begin
  sql := 'select * from customers where CUSTOMER_ID = ' + ID;
  Memo1.Lines.Add('SQL: ' + SQL);
  doQuery(ordersDB.UniQuery1, SQL);
  if ordersDB.UniQuery1.RecordCount = 1 then
  begin
    ordersDB.UniQuery1.Edit;
    ordersDB.UniQuery1.FieldByName('QB_LIST_ID').AsString := QB_ID;
    ordersDB.UniQuery1.Post;
    getCustomers();
    QB_ID := '';
    ID := '';
  end
  else
    MessageDlg('Wrong # of records return', mtConfirmation, [mbOk], 0)
end;

procedure TfQB.Button11Click(Sender: TObject);
var
  restClient: TRESTClient;
  restRequest: TRESTRequest;
  restResponse: TRESTResponse;
  param: TRESTRequestParameter;
  res: string;
  jsValue: TJSONValue;
  jsObj: TJSONObject;
  ItemList: TJSONArray;
  pair: TJSONPair;
  ModifiedList: TJSONArray;
  ParsedCustomer, Item: TJSONObject;
  I: integer;
  BillAddr: TJSONObject;
  ShipAddr: TJSONObject;
  sql: string;
begin
  Memo1.Clear;
  Memo1.Lines.Add('Linking all QB Items to KG Orders');
  restClient := TRESTClient.Create(nil);
  restClient.BaseURL := 'https://sandbox-quickbooks.api.intuit.com';

  restRequest := TRESTRequest.Create(nil);
  restRequest.Client := restClient;

  restResponse := TRESTResponse.Create(nil);
  restRequest.Response := restResponse;


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

  restRequest.Method := rmGET;
  //res := '/v3/company/' + companyID + '/preferences';
  res := '/v3/company/' + companyID + '/query?query=select * from Item&minorversion=75';  // '/customer/58';
  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;

  //memo1.Lines.Add(restresponse.Content);

  jsValue := restResponse.JSONValue;

  jsObj := TJSONObject(jsValue);
  //CustomerList := TJSONArray(restResponse.JSONValue);
  Memo1.Lines.Add( jsObj.Format(2) );

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

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

    sql := 'select * from qb_items where qb_item_name = ' + quotedStr(Item.GetValue<string>('Name'));
    doQuery(ordersDB.UniQuery1, sql);

    if ( not ordersDB.UniQuery1.IsEmpty ) then
    begin
      ordersDB.UniQuery1.Edit;
      ordersDB.uniquery1.FieldByName('qb_items_qb_id').AsString := Item.GetValue<string>('Id');
      ordersDB.UniQuery1.Post;
    end;

  end;



  Memo1.lines.add('Done');
  // Load customer info

  restClient.Free;
  restRequest.Free;
  restResponse.Free;
end;



procedure TfQB.Button12Click(Sender: TObject);
var
  SQL: string;
  cust_count, ship_count: string;
  msg: string;
begin
  SQL := 'select count(*) as cust_count from customers c ' +
          ' WHERE NOT EXISTS (SELECT 1 FROM corrugated_plate_orders cpo WHERE cpo.COMPANY_ID = c.CUSTOMER_ID) '+
          'AND NOT EXISTS (SELECT 1 FROM web_plate_orders wpo WHERE wpo.COMPANY_ID = c.CUSTOMER_ID) ORDER BY c.SHORT_NAME';
  DoQuery(ordersDB.UniQuery1, SQL);
  cust_count := ordersDB.UniQuery1.FieldByName('cust_count').AsString;

  SQL := 'select count(*) as ship_count from customers c  join customers_ship cs on c.CUSTOMER_ID = cs.customer_id' +
          ' WHERE NOT EXISTS (SELECT 1 FROM corrugated_plate_orders cpo WHERE cpo.COMPANY_ID = c.CUSTOMER_ID) '+
          'AND NOT EXISTS (SELECT 1 FROM web_plate_orders wpo WHERE wpo.COMPANY_ID = c.CUSTOMER_ID) ORDER BY c.SHORT_NAME';
  DoQuery(ordersDB.UniQuery1, SQL);
  ship_count := ordersDB.UniQuery1.FieldByName('ship_count').AsString;

  msg := 'You will be deleting ' + cust_count + ' customers and their ' + ship_count + ' assoicated shipping addresses. Are you sure you want to continue?';
  ShowDeleteConfirm(msg);

end;

procedure TfQB.Button13Click(Sender: TObject);
begin
  Memo1.Clear;
end;

procedure TfQB.Button14Click(Sender: TObject);
begin
  Memo2.Clear;
end;

procedure TfQB.Button15Click(Sender: TObject);
var
  SQL, name, password, newPassword: string;
begin
  Memo1.Clear;
  Memo1.Lines.Add('Updating all passwords to no longer store passwords in plain text');
  SQL := 'Select * from users';
  doQuery(ordersDB.UniQuery1, SQL);
  while (not ordersDB.UniQuery1.Eof) do
  begin
    name := ordersDB.UniQuery1.FieldByName('NAME').AsString;
    password := ordersDB.UniQuery1.FieldByName('PASSWORD').AsString;
    newPassword := THashSHA2.GetHashString(name + password, THashSHA2.TSHA2Version.SHA512).ToUpper;
    if length(password) <> 128 then
    begin
      ordersDB.UniQuery1.Edit;
      ordersDB.UniQuery1.FieldByName('PASSWORD').AsString := newPassword;
      ordersDB.UniQuery1.Post;
    end;
    ordersDB.UniQuery1.Next;
  end;
  logger.Log(3, 'Finished updating passwords');
  Memo1.Lines.Add('Finished updating passwords');
end;

procedure TfQB.DeleteCustomers();
var
  SQL: string;
  count: integer;
  ModifiedList: TJSONArray;
  Customer, ParsedCustomer: TJSONObject;
  I: integer;
  ship_count, cust_count: integer;
begin
  Memo1.Clear;
  Memo1.Lines.Add('Beginning to Delete Customers');
  SQL := 'SELECT * FROM customers c ' +
          'WHERE NOT EXISTS (SELECT 1 FROM corrugated_plate_orders cpo WHERE cpo.COMPANY_ID = c.CUSTOMER_ID) '+
          'AND NOT EXISTS (SELECT 1 FROM web_plate_orders wpo WHERE wpo.COMPANY_ID = c.CUSTOMER_ID) ORDER BY c.SHORT_NAME';
  doQuery(ordersDB.UniQuery1, SQL);
  cust_count := 0;
  ship_count := 0;
  while( not ordersDB.UniQuery1.Eof ) do
  begin
    SQL := 'Select * from customers_ship where customer_id = ' + ordersDB.UniQuery1.FieldByName('CUSTOMER_ID').AsString;

    doQuery(ordersDB.UniQuery2, SQL);
    ordersDB.UniQuery2.First;
    while ( not ordersDB.UniQuery2.Eof) do
    begin
      ordersDB.UniQuery2.Delete;
      ship_count := ship_count + 1;
    end;

    cust_count := cust_count + 1;
    ordersDB.UniQuery1.Delete;
  end;
  Memo1.Lines.Add('Customers deleted: ' + inttostr(cust_count));
  memo1.Lines.Add('Shipping Addresses deleted: '+ inttostr(ship_count));
end;

procedure TfQB.ShowDeleteConfirm(msg: string);
begin
  if MessageDlg(msg, mtConfirmation, [mbYes, mbNo], 0) = mrYes then
  begin
    // User confirmed
    DeleteCustomers();
  end
  else
  begin
    // User canceled
    ShowMessage('Cancelled.');
  end;
end;

procedure TfQB.Button1Click(Sender: TObject);
begin
  getCustomers();
  AdvPanel2.Text := 'kgOrders Customers'
end;

procedure TfQB.getCustomers();
var
  SQL: string;
  count: integer;
  ModifiedList: TJSONArray;
  Customer, ParsedCustomer: TJSONObject;
  I: integer;
begin
  Memo1.Clear;
  Memo1.Lines.Add('Retrieving KG Orders Customers');
  SQL := 'SELECT * FROM customers c';
  doQuery(ordersDB.UniQuery1, SQL);
  ModifiedList := TJSONArray.Create;

  while not ordersDB.UniQuery1.Eof do
  begin
    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', ordersDB.UniQuery1.FieldByName('CUSTOMER_ID').AsString);
        ParsedCustomer.AddPair('CompanyName', ordersDB.UniQuery1.FieldByName('NAME').AsString);
        ParsedCustomer.AddPair('BillAddrCity', ordersDB.UniQuery1.FieldByName('BILL_CITY').AsString);
        ParsedCustomer.AddPair('BillAddrLine1',  ordersDB.UniQuery1.FieldByName('BILL_ADDRESS').AsString);
        ParsedCustomer.AddPair('BillAddrState',  ordersDB.UniQuery1.FieldByName('BILL_STATE').AsString);
        ParsedCustomer.AddPair('BillAddrZip',  ordersDB.UniQuery1.FieldByName('BILL_ZIP').AsString);
        ParsedCustomer.AddPair('BillAddr',  ordersDB.UniQuery1.FieldByName('BILL_ADDRESS_BLOCK').AsString);
        ParsedCustomer.AddPair('QB_ID', TJSONString.Create(ordersDB.UniQuery1.FieldByName('QB_LIST_ID').AsString));


        ModifiedList.AddElement(ParsedCustomer);
        ordersDB.UniQuery1.Next;
        except
          ParsedCustomer.Free;
          raise;
        end;
  end;

  LoadJSONArray2(ModifiedList);

end;

procedure TfQB.SaveTokens(AccessToken, RefreshToken: string);
var
  f: TStringList;
  iniStr, line: string;
  iniFile: TIniFile;
begin
  iniFile := TIniFile.Create( ExtractFilePath(Application.ExeName) + 'kgOrdersServer.ini' );
  try
    iniFile.WriteString('Quickbooks', 'RefreshToken', RefreshToken);
    LastRefresh := Now;

  finally
    IniFile.Free;
  end;
end;

procedure TfQB.Button2Click(Sender: TObject);
begin
  GetQBCustomers();
end;

procedure TfQB.Button3Click(Sender: TObject);
var
  restClient: TRESTClient;
  restRequest: TRESTRequest;
  restResponse: TRESTResponse;
  param: TRESTRequestParameter;
  res: string;
  jsValue: TJSONValue;
  Customer: TJSONValue;
  jsObj: TJSONObject;
  CustomerList: TJSONArray;
  pair: TJSONPair;
begin
  Memo1.Clear;
  Memo1.Lines.Add('Retrieving QB Customer Based on ID');
  restClient := TRESTClient.Create(nil);
  restClient.BaseURL := 'https://sandbox-quickbooks.api.intuit.com';

  restRequest := TRESTRequest.Create(nil);
  restRequest.Client := restClient;

  restResponse := TRESTResponse.Create(nil);
  restRequest.Response := restResponse;


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

  restRequest.Method := rmGET;
  //GET /v3/company/<realmId>/customer/<customerId>
  res := '/v3/company/' + companyid + '/customer/58';
  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;

  //memo1.Lines.Add(restresponse.Content);

  jsValue := restResponse.JSONValue;

  jsObj := TJSONObject(jsValue);
  Memo2.Clear;
  Memo2.Lines.Add( jsObj.Format(2) );

 // CustomerList := TJSONArray( TJSONObject( jsObj.GetValue('QueryResponse') ).GetValue('Customer')) ;

 // LoadJSONArray( CustomerList );

  restClient.Free;
  restRequest.Free;
  restResponse.Free;

end;

procedure TfQB.Button4Click(Sender: TObject);
var
  orderJSON: TJSONObject;
begin
  orderJSON := TJSONObject.Create;
  orderJSON.AddPair('ORDER_ID', '19842');
  addEstimate(orderJSON.ToString);
end;

procedure tfQb.addEstimate(orderInfo: string);
var
  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: string;
  table: string;
  ShipMethodRef: TJSONObject;
  unitPrice: double;
begin
  Memo1.Clear;
  Memo1.Lines.Add('Adding Order ' + orderInfo + ' to QB');
  BillAddrJSON := TJSONObject.Create;
  ShipAddrJSON := TJSONObject.Create;
  EstimateJSON := TJSONObject.Create;
  CustomerRefJSON := TJSONObject.Create;
  CustomFields := TJSONArray.Create;
  CustomField := TJSONObject.Create;
  LineArray := TJSONArray.Create;
  LineObj := TJSONObject.Create;
  DetailObj := TJSONObject.Create;
  ItemRefObj := TJSONObject.Create;
  TaxCodeRef := TJSONObject.Create;
  TxnTaxCodeRef := TJSONObject.Create;
  TxnTaxDetail := TJSONObject.Create;

  JSONData := TJSONObject.ParseJSONValue(orderInfo) as TJSONObject;
  ORDER_ID := JSONData.GetValue<string>('ORDER_ID');
  // add logic to figure out what type of order it is
  SQL := 'select * from orders where ORDER_ID = ' + ORDER_ID;
  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 ORDER_ID = ' + ORDER_ID;
  doQuery(ordersDB.UniQuery1, SQL);

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

  // Update the Quickbooks list ID in the database
  // CustomerRef.value :=
  CustomerRefJSON := TJSONObject.Create;
  CustomerRefJSON.AddPair('value', ordersDB.UniQuery1.FieldByName('QB_LIST_ID').AsString);
  //CustomerRef.value := '59'
  ;
  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[0]);
      1: ShipAddrJSON.AddPair('Line2', Lines[1]);
      2: ShipAddrJSON.AddPair('Line3', Lines[2]);
      3: ShipAddrJSON.AddPair('Line4', Lines[3]);
      4: ShipAddrJSON.AddPair('Line5', Lines[4]);
    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;
  if ( table = 'corrugated_plate_orders') or (table = 'cutting_die_orders' ) then
  begin
    if ordersDB.UniQuery1.FieldByName('general_special_instructions').AsString <> '' then
      LineObj.AddPair('Description', ordersDB.UniQuery1.FieldByName('item_desc').AsString + ' - ' + ordersDB.UniQuery1.FieldByName('general_special_instructions').AsString);
  end;

  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;


  restClient := TRESTClient.Create(nil);
  restClient.BaseURL := 'https://sandbox-quickbooks.api.intuit.com';

  restRequest := TRESTRequest.Create(nil);
  restRequest.Client := restClient;

  restResponse := TRESTResponse.Create(nil);
  restRequest.Response := restResponse;


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

  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;

  Memo2.Clear;
  Memo2.Lines.Add( estimateJSON.Format(2) );

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

  restRequest.Execute;

  //memo1.Lines.Add(restresponse.Content);

  jsValue := restResponse.JSONValue;

  jsObj := TJSONObject(jsValue);
  Memo1.Lines.Add( jsObj.Format(2) );
  //CustomerList := TJSONArray(restResponse.JSONValue);

 // CustomerList := TJSONArray( TJSONObject( jsObj.GetValue('QueryResponse') ).GetValue('Customer')) ;

 // LoadJSONArray( CustomerList );

  restClient.Free;
  restRequest.Free;
  restResponse.Free;
end;

procedure TfQB.Button5Click(Sender: TObject);
var
  SQL: string;
  count: integer;
  ModifiedList: TJSONArray;
  Customer, ParsedCustomer: TJSONObject;
  I: integer;
begin
  Memo1.Clear;
  Memo1.Lines.Add('Showing all KG Orders Customers without Orders');
  SQL := 'SELECT * FROM customers c ' +
          'WHERE EXISTS (SELECT 1 FROM corrugated_plate_orders cpo WHERE cpo.COMPANY_ID = c.CUSTOMER_ID) '+
          'OR EXISTS (SELECT 1 FROM web_plate_orders wpo WHERE wpo.COMPANY_ID = c.CUSTOMER_ID) ORDER BY c.SHORT_NAME';
  doQuery(ordersDB.UniQuery1, SQL);
  ModifiedList := TJSONArray.Create;

  while not ordersDB.UniQuery1.Eof do
  begin
    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', ordersDB.UniQuery1.FieldByName('CUSTOMER_ID').AsString);
      ParsedCustomer.AddPair('CompanyName', ordersDB.UniQuery1.FieldByName('NAME').AsString);
      ParsedCustomer.AddPair('BillAddrCity', ordersDB.UniQuery1.FieldByName('BILL_CITY').AsString);
      ParsedCustomer.AddPair('BillAddrLine1',  ordersDB.UniQuery1.FieldByName('BILL_ADDRESS').AsString);
      ParsedCustomer.AddPair('BillAddrState',  ordersDB.UniQuery1.FieldByName('BILL_STATE').AsString);
      ParsedCustomer.AddPair('BillAddrZip',  ordersDB.UniQuery1.FieldByName('BILL_ZIP').AsString);
      ParsedCustomer.AddPair('BillAddr',  ordersDB.UniQuery1.FieldByName('BILL_ADDRESS_BLOCK').AsString);


      ModifiedList.AddElement(ParsedCustomer);
      ordersDB.UniQuery1.Next;
      except
        ParsedCustomer.Free;
        raise;
      end;
  end;

  LoadJSONArray2(ModifiedList);

end;

procedure TfQB.Button6Click(Sender: TObject);
var
  restClient: TRESTClient;
  restRequest: TRESTRequest;
  restResponse: TRESTResponse;
  param: TRESTRequestParameter;
  res: string;
  jsValue: TJSONValue;
  jsObj: TJSONObject;
  CustomerList: TJSONArray;
  pair: TJSONPair;
  ModifiedList: TJSONArray;
  ParsedCustomer, Customer: TJSONObject;
  I: integer;
  BillAddr: TJSONObject;
  ShipAddr: TJSONObject;
  SQL: string;
begin
  restClient := TRESTClient.Create(nil);
  restClient.BaseURL := 'https://sandbox-quickbooks.api.intuit.com';

  restRequest := TRESTRequest.Create(nil);
  restRequest.Client := restClient;

  restResponse := TRESTResponse.Create(nil);
  restRequest.Response := restResponse;


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

  restRequest.Method := rmGET;
  //res := '/v3/company/' + companyID + '/preferences';
  res := '/v3/company/' + companyID + '/query?query=select * from Customer&minorversion=75';  // '/customer/58';
  restRequest.Resource := res;
  ///query?query=select * from Customer where CompanyName = ' + quotedStr('TYOGA CONTAINER') + '&minorversion=75';


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

  restRequest.Execute;

  //memo1.Lines.Add(restresponse.Content);

  jsValue := restResponse.JSONValue;

  jsObj := TJSONObject(jsValue);
  //CustomerList := TJSONArray(restResponse.JSONValue);

  CustomerList := TJSONArray( TJSONObject( jsObj.GetValue('QueryResponse') ).GetValue('Customer')) ;

  //LoadJSONArray( CustomerList );
  findMatches(CustomerList, 'DisplayName', 'Name');
  //findMatches(CustomerList, 'BillAddrLine1', 'BILL_ADDRESS');
  //findMatches(CustomerList, 'BillAddrCity', 'BILL_CITY');
  //findMatches(CustomerList, 'BillAddrState', 'BILL_STATE');
  //findMatches(CustomerList, 'BillAddrZip', 'BILL_ZIP');


end;

procedure TfQB.Button7Click(Sender: TObject);
var
  SQL: string;
  count: integer;
  ModifiedList: TJSONArray;
  Customer, ParsedCustomer: TJSONObject;
  I: integer;
begin
  SQL := 'SELECT * FROM customers c ' +
          'WHERE NOT EXISTS (SELECT 1 FROM corrugated_plate_orders cpo WHERE cpo.COMPANY_ID = c.CUSTOMER_ID) '+
          'AND NOT EXISTS (SELECT 1 FROM web_plate_orders wpo WHERE wpo.COMPANY_ID = c.CUSTOMER_ID) ORDER BY c.SHORT_NAME';
  doQuery(ordersDB.UniQuery1, SQL);
  ModifiedList := TJSONArray.Create;

  while not ordersDB.UniQuery1.Eof do
  begin
    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', ordersDB.UniQuery1.FieldByName('CUSTOMER_ID').AsString);
      ParsedCustomer.AddPair('CompanyName', ordersDB.UniQuery1.FieldByName('NAME').AsString);
      ParsedCustomer.AddPair('BillAddrCity', ordersDB.UniQuery1.FieldByName('BILL_CITY').AsString);
      ParsedCustomer.AddPair('BillAddrLine1',  ordersDB.UniQuery1.FieldByName('BILL_ADDRESS').AsString);
      ParsedCustomer.AddPair('BillAddrState',  ordersDB.UniQuery1.FieldByName('BILL_STATE').AsString);
      ParsedCustomer.AddPair('BillAddrZip',  ordersDB.UniQuery1.FieldByName('BILL_ZIP').AsString);
      ParsedCustomer.AddPair('BillAddr',  ordersDB.UniQuery1.FieldByName('BILL_ADDRESS_BLOCK').AsString);


      ModifiedList.AddElement(ParsedCustomer);
      ordersDB.UniQuery1.Next;
      except
        ParsedCustomer.Free;
        raise;
      end;
  end;

  LoadJSONArray2(ModifiedList);

end;

procedure TfQB.Button8Click(Sender: TObject);
var
  restClient: TRESTClient;
  restRequest: TRESTRequest;
  restResponse: TRESTResponse;
  param: TRESTRequestParameter;
  res, SQL: string;
  jsValue: TJSONValue;
  jsObj: TJSONObject;
  CustomerList: TJSONArray;
  pair: TJSONPair;
  ModifiedList: TJSONArray;
  ParsedCustomer, Customer: TJSONObject;
  I: integer;
  BillAddr: TJSONObject;
  ShipAddr: TJSONObject;
  ItemList: TJSONArray;
  Item: TJSONObject;
begin
  Memo1.Clear;
  memo1.Lines.Add('Getting KG and QB Items');

  restClient := TRESTClient.Create(nil);
  restClient.BaseURL := 'https://sandbox-quickbooks.api.intuit.com';

  restRequest := TRESTRequest.Create(nil);
  restRequest.Client := restClient;

  restResponse := TRESTResponse.Create(nil);
  restRequest.Response := restResponse;


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

  restRequest.Method := rmGET;
  //res := '/v3/company/' + companyID + '/preferences';
  res := '/v3/company/' + companyID + '/query?query=select * from Item&minorversion=75';  // '/customer/58';
  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;

  //memo1.Lines.Add(restresponse.Content);

  jsValue := restResponse.JSONValue;

  jsObj := TJSONObject(jsValue);
  //CustomerList := TJSONArray(restResponse.JSONValue);
  Memo2.Clear;
  Memo2.Lines.Add( jsObj.Format(2) );

  CustomerList := TJSONArray( TJSONObject( jsObj.GetValue('QueryResponse') ).GetValue('Item'));

  loadJSONArray(CustomerList);
  AdvPanel1.Text := 'QB Items';

  // Load customer info

  restClient.Free;
  restRequest.Free;
  restResponse.Free;

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

  doQuery(ordersDB.UniQuery1, SQL);

  ItemList:= TJSONArray.Create;

  while not ordersDB.UniQuery1.Eof do
  begin
    item := TJSONObject.Create;
    item.AddPair('ID', ordersDB.UniQuery1.FieldByName('qb_items_id').AsString);
    item.AddPair('name', ordersDB.UniQuery1.FieldByName('qb_item_name').AsString);
    item.AddPair('description', ordersDB.UniQuery1.FieldByName('item_desc').AsString);
    item.AddPair('status', ordersDB.UniQuery1.FieldByName('status').AsString);
    item.AddPair('QB_ID', ordersDB.UniQuery1.FieldByName('qb_items_qb_id').AsString);
    ItemList.Add(item);
    ordersDB.UniQuery1.Next;
  end;
  LoadJSONArray2(ItemList);
  AdvPanel2.Text := 'KG Orders Items';
  ordersDB.UniQuery1.Close;
end;

procedure TfQB.Button9Click(Sender: TObject);
var
  restClient: TRESTClient;
  restRequest: TRESTRequest;
  restResponse: TRESTResponse;
  param: TRESTRequestParameter;
  res: string;
  jsValue: TJSONValue;
  jsObj: TJSONObject;
  CustomerList: TJSONArray;
  pair: TJSONPair;
  ModifiedList: TJSONArray;
  ParsedCustomer, Customer: TJSONObject;
  I: integer;
  BillAddr: TJSONObject;
  ShipAddr: TJSONObject;
begin
  Memo1.Clear;
  Memo1.Lines.Add('Retrieving Estimate from QB');
  restClient := TRESTClient.Create(nil);
  restClient.BaseURL := 'https://sandbox-quickbooks.api.intuit.com';

  restRequest := TRESTRequest.Create(nil);
  restRequest.Client := restClient;

  restResponse := TRESTResponse.Create(nil);
  restRequest.Response := restResponse;


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

  restRequest.Method := rmGET;
  //GET /v3/company/9341454243251288/query?query=<selectStatement>&minorversion=75
  res := '/v3/company/' + companyID + '/query?query=select * from estimate where DocNumber = ' + quotedStr('1002') + '&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;
  Memo2.Clear;

  jsValue := restResponse.JSONValue;

  jsObj := TJSONObject(jsValue);
  Memo2.Lines.Add( jsObj.Format(2) );


  restClient.Free;
  restRequest.Free;
  restResponse.Free;
end;


procedure TfQB.getQBCustomers();
var
  restClient: TRESTClient;
  restRequest: TRESTRequest;
  restResponse: TRESTResponse;
  param: TRESTRequestParameter;
  res: string;
  jsValue: TJSONValue;
  jsObj: TJSONObject;
  CustomerList: TJSONArray;
  pair: TJSONPair;
  ModifiedList: TJSONArray;
  ParsedCustomer, Customer: TJSONObject;
  I: integer;
  BillAddr: TJSONObject;
  ShipAddr: TJSONObject;
begin
  Memo1.Clear;
  Memo1.Lines.Add('Getting QB Customers');
  restClient := TRESTClient.Create(nil);
  restClient.BaseURL := 'https://sandbox-quickbooks.api.intuit.com';

  restRequest := TRESTRequest.Create(nil);
  restRequest.Client := restClient;

  restResponse := TRESTResponse.Create(nil);
  restRequest.Response := restResponse;


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

  restRequest.Method := rmGET;
  //res := '/v3/company/' + companyID + '/preferences';
  res := '/v3/company/' + companyID + '/query?query=select * from Customer&minorversion=75';  // '/customer/58';
  restRequest.Resource := res;
  ///query?query=select * from Customer where CompanyName = ' + quotedStr('TYOGA CONTAINER') + '&minorversion=75';


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

  restRequest.Execute;

  //memo1.Lines.Add(restresponse.Content);

  jsValue := restResponse.JSONValue;

  jsObj := TJSONObject(jsValue);
  //CustomerList := TJSONArray(restResponse.JSONValue);
  Memo2.Clear;
  Memo2.Lines.Add( jsObj.Format(2) );

  CustomerList := TJSONArray( TJSONObject( jsObj.GetValue('QueryResponse') ).GetValue('Customer')) ;

  // Load customer info
  ModifiedList := TJSONArray.Create;
  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'));

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

      ModifiedList.AddElement(ParsedCustomer);
      except
        ParsedCustomer.Free;
        raise;
      end;
    end;

  LoadJSONArray(ModifiedList);
  AdvPanel1.Text := 'QB Customers';

  restClient.Free;
  restRequest.Free;
  restResponse.Free;


end;

procedure TfQB.ConfigureSSL(IOHandler: TIdSSLIOHandlerSocketOpenSSL);
begin
  // For Indy 10.6.2+ (Delphi 10.2 Tokyo+)
  IOHandler.SSLOptions.Method := sslvTLSv1_2;

  // Set SSL versions - maximum compatibility
  IOHandler.SSLOptions.SSLVersions := [sslvTLSv1_2];

  // For very old Indy versions (fallback)
  if not (sslvTLSv1_2 in IOHandler.SSLOptions.SSLVersions) then
  begin
    IOHandler.SSLOptions.Method := sslvSSLv23;
    IOHandler.SSLOptions.SSLVersions := [sslvTLSv1, sslvTLSv1_1, sslvTLSv1_2];
  end;

  IOHandler.SSLOptions.Mode := sslmClient;
end;

procedure TfQB.FormCreate(Sender: TObject);
var
  iniFile: TIniFile;
begin
  inherited;
  ordersDB := TApiDatabase.Create(nil);
  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', '');
  AccessToken := iniFile.ReadString('Quickbooks', 'AccessToken', '');
  LastRefresh := StrToDateTime(iniFile.ReadString('Quickbooks', 'LastRefresh', ''));
  QB_ID := '';
  ID := '';
end;

procedure TfQB.FormDestroy(Sender: TObject);
begin
  ordersDB.Free;
  inherited;
end;

function TfQB.RefreshAccessToken: string;
// Refresh Token changes so make sure to save refresh token.
var
  IdHTTP: TIdHTTP;
  SSLIO: TIdSSLIOHandlerSocketOpenSSL;
  RequestStream: TStringStream;
  EncodedAuth, EncodedAuth2, PostData, response: string;
  f: TStringList;
  fi: string;
  JSObj: TJSONObject;
  iniFile: TIniFile;
  Encoder: TBase64Encoding;
begin
  // 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
    Exit();
  end;

  EncodedAuth := Encoder.Encode(Client + ':' + Secret);
  Memo1.Lines.Add(EncodedAuth);
  if RefreshToken = '' then
  begin
    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;
        Memo1.Lines.Add('Tokens Successfully Saved');
      except
        on E: EIdHTTPProtocolException do
          Memo1.Lines.Add('Error: ' + E.Message + #13#10 + 'Response: ' + E.ErrorMessage);
      end;
    finally
      RequestStream.Free;
    end;
  finally
    SSLIO.Free;
    IdHTTP.Free;
  end;
end;

procedure TfQB.getCompanyInfo();
var
  restClient: TRESTClient;
  restRequest: TRESTRequest;
  restResponse: TRESTResponse;
  param: TRESTRequestParameter;
  res: string;
  jsValue: TJSONValue;
  jsObj, companyInfo: TJSONObject;
begin
  restClient := TRESTClient.Create(nil);
  restClient.BaseURL := 'https://sandbox-quickbooks.api.intuit.com';

  restRequest := TRESTRequest.Create(nil);
  restRequest.Client := restClient;

  restResponse := TRESTResponse.Create(nil);
  restRequest.Response := restResponse;


  if MinutesBetween(Now, LastRefresh) > 58  then
  begin
    RefreshAccessToken();
  end;
  restRequest.Method := rmGET;
  res := '/v3/company/' + companyID + '/companyinfo/' + companyID;
  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;

  memo1.Lines.Add(restresponse.Content) ;
  //jsValue := restResponse.JSONValue;
  //jsObj := TJSONObject.ParseJSONValue(restresponse.Content) as TJSONObject;
  //companyInfo := TJSONObject(jsObj.GetValue('CompanyInfo'));

  restClient.Free;
  restRequest.Free;
  restResponse.Free;

end;

procedure TfQB.LoadJsonArray(jaData: TJSONArray);
var
  jso: TJSONObject;
  i, j: integer;
  row: integer;
begin
  asgData.FixedRowAlways := true;
  Memo1.Lines.Add( '---------------------------------------------------------------' );
  Memo1.Lines.Add( 'LoadJsonArray into asgData' );

  asgData.ClearAll;
  asgData.RowCount := 1;
  asgData.StartUpdate;

  jso := TJSONObject(jaData.Items[0]);
  asgData.ColCount := jso.Count;
  for i := 0 to jso.Count - 1 do
    asgData.Cells[i+1, 0] := jso.Pairs[i].JsonString.Value;

  for i := 0 to jaData.Count - 1 do
  begin
    jso := TJSONObject(jaData.Items[i]);
    asgData.RowCount := asgData.RowCount + 1;
    row := asgData.RowCount - 1;
    for j := 0 to jso.Count - 1 do
      asgData.Cells[j+1, row] := jso.Pairs[j].JsonValue.Value;
  end;
  asgData.EndUpdate;
  asgData.AutoSizeColumns(true);
end;

procedure TfQB.LoadJsonArray2(jaData: TJSONArray);
var
  jso: TJSONObject;
  i, j: integer;
  row: integer;
begin
  Memo1.Lines.Add( '---------------------------------------------------------------' );
  Memo1.Lines.Add( 'LoadJsonArray into asgData2' );

  asgData2.ClearAll;
  asgData2.RowCount := 1;
  asgData2.StartUpdate;

  jso := TJSONObject(jaData.Items[0]);
  asgData2.ColCount := jso.Count+1;
  for i := 0 to jso.Count - 1 do
  begin
    asgData2.Cells[i+1, 0] := jso.Pairs[i].JsonString.Value;
    Memo1.Lines.Add('Header Key: ' + jso.Pairs[i].JsonString.Value);
  end;

  for i := 0 to jaData.Count - 1 do
  begin
    jso := TJSONObject(jaData.Items[i]);
    asgData2.RowCount := asgData2.RowCount + 1;
    row := asgData2.RowCount - 1;
    for j := 0 to jso.Count - 1 do
      asgData2.Cells[j+1, row] := jso.Pairs[j].JsonValue.Value;
  end;
  asgData2.EndUpdate;
  asgData2.AutoSizeColumns(true);
end;

procedure tfQB.findMatches(CustomerList: TJSONArray; JSONfield, DBField: string);
var
  I: integer;
  Customer: TJSONObject;
  SQL: string;
begin
  Memo1.Clear;
  Memo1.Lines.Add('Matching ' + JSONfield + ' on ' + DBField);
  for I := 0 to CustomerList.Count - 1 do
  begin
    Customer := CustomerList.Items[I] as TJSONObject;

    SQL := 'select COUNT(*) as count from customers where NAME = ' + quotedStr(Customer.GetValue<string>(JSONfield));
    doQuery(ordersDB.UniQuery1, SQL);

    if ordersDB.UniQuery1.FieldByName('count').AsInteger = 1 then
    begin
      SQL := 'select * from customers where ' + DBField + ' = ' + quotedStr(Customer.GetValue<string>(JSONfield));
      doQuery(ordersDB.UniQuery1, SQL);
      Memo1.Lines.Add('Match at ' + ordersDB.UniQuery1.FieldByName('SHORT_NAME').AsString)
    end
    else if ordersDB.UniQuery1.FieldByName('count').AsInteger > 1 then
      Memo1.Lines.Add(ordersDB.UniQuery1.FieldByName('count').AsString + ' Matches at ' + Customer.GetValue<string>(JSONfield) + ' in field ' + JSONfield);
  end;
end;

end.
