unit rOrderList;

interface

uses
  System.SysUtils, System.Classes, Data.DB, MemDS, DBAccess, Uni, Common.Logging,
  frxClass, frxExportBaseDialog, frxExportPDF, frCoreClasses, frxDBSet, JS, JSON,
  frxTableObject, frxUtils, DateUtils;

type
  TrptOrderList = class(TDataModule)
    frxOrderList: TfrxReport;
    frxPDFExport1: TfrxPDFExport;
    ucKG: TUniConnection;
    uqOrders: TUniQuery;
    frxDBOrders: TfrxDBDataset;
    uqOrdersORDER_ID: TLongWordField;
    uqOrdersLoc: TStringField;
    uqOrdersCOMPANY_NAME: TStringField;
    uqOrdersJOB_NAME: TStringField;
    uqOrdersORDER_TYPE: TStringField;
    uqOrdersPROOF_DUE: TDateField;
    uqOrdersPROOF_DONE: TDateTimeField;
    uqOrdersART_DUE: TDateField;
    uqOrdersART_DONE: TDateTimeField;
    uqOrdersPLATE_DUE: TDateField;
    uqOrdersPLATE_DONE: TDateTimeField;
    uqOrdersMOUNT_DUE: TDateField;
    uqOrdersMOUNT_DONE: TDateTimeField;
    uqOrdersSHIP_DUE: TDateField;
    uqOrdersSHIP_DONE: TDateTimeField;
    uqOrdersPRICE: TFloatField;
    uqOrdersQB_REF_NUM: TStringField;
    uqOrdersCOLORS: TStringField;
    uqColors: TUniQuery;
    uqOrderspo_number: TStringField;
    uqOrdersquickbooks_item: TStringField;
    uqOrdersNEW_ORDER_DATE: TStringField;
    uqOrdersNEW_PROOF_DONE: TStringField;
    uqOrdersNEW_ART_DONE: TStringField;
    uqOrdersNEW_PLATE_DONE: TStringField;
    uqOrdersNEW_MOUNT_DONE: TStringField;
    uqOrdersNEW_SHIP_DONE: TStringField;
    uqOrdersORDER_DATE: TDateField;
    procedure DataModuleCreate(Sender: TObject);
    procedure uqOrdersCalcFields(DataSet: TDataSet);

  private
    function getColorCount(colors: string): string;

  public
    function PrepareReport(SQL, CompanyName: string): string;
    function GeneratePDF: string;
  end;

var
  rptOrderList: TrptOrderList;

implementation

uses
  uLibrary, Common.Config;

{%CLASSGROUP 'Vcl.Controls.TControl'}

{$R *.dfm}

procedure TrptOrderList.DataModuleCreate(Sender: TObject);
begin
  Logger.Log( 1, 'TAuthDatabase.DataModuleCreate' );
  LoadDatabaseSettings( ucKG, 'kgOrdersServer.ini' );
  try
    ucKG.Connect;
  except
    on E: Exception do
    begin
      Logger.Log(2, '--TrptOrderList.DataModuleCreate -Error connecting to database: ' + E.Message);
    end;
  end;
end;


function TrptOrderList.PrepareReport(SQL, CompanyName: string): string;
var
  memo: TFrxMemoView;
  DateFormat: TFormatSettings;
begin
  Logger.Log(5, 'Generated SQL for Prepare Report: ' + SQL);

  uqOrders.Close;
  uqOrders.SQL.Text := SQL;
  uqOrders.Open;

  uqOrders.Edit;
  memo := frxOrderList.FindObject('CompanyName') as TFrxMemoView;
  memo.Text := CompanyName;

  DateFormat := TFormatSettings.Create;
  DateFormat.ShortDateFormat := 'mm/dd/yyyy';
  DateFormat.DateSeparator := '/';
  DateFormat.TimeSeparator := ':';
  DateFormat.ShortTimeFormat := 'HH:nn';

  uqOrdersORDER_DATE.AsDateTime := RecodeSecond(uqOrdersORDER_DATE.AsDateTime, 0);
  uqOrders.Post;

  //FormatDateTime('yyyy-mm-dd hh:nn', myDate);

  result := GeneratePDF;

  Logger.Log(5, 'Report preparation complete.');
end;



procedure TrptOrderList.uqOrdersCalcFields(DataSet: TDataSet);
var
  ColorType: string;
  SQL: string;
  OrderID: LongWord;
  jsonStr: string;
begin

  OrderID := uqOrdersORDER_ID.AsInteger;
  if uqOrdersORDER_TYPE.AsString = 'web_plate' then
  begin
    ColorType := 'quantity_and_colors_qty_colors';
    SQL := 'SELECT ' + ColorType + ' FROM web_plate_orders WHERE order_id = ' + IntToStr(OrderID);
  end
  else
  begin
    ColorType := 'colors_colors';
    SQL := 'SELECT ' + ColorType + ' FROM corrugated_plate_orders WHERE order_id = ' + IntToStr(OrderID);
  end;

  uqColors.Close;
  uqColors.SQL.Text := SQL;
  try
    uqColors.Open;
    jsonStr := uqColors.FieldByName(ColorType).AsString;
    DataSet.FieldByName('COLORS').AsString := getColorCount(jsonStr);
  finally
    uqColors.Close; // Ensure it is closed
  end;

  if uqOrdersORDER_DATE.AsString <> '' then
    uqOrdersNEW_ORDER_DATE.AsString := FormatDateTime('m/d/yyyy HH:nn', uqOrdersORDER_DATE.AsDateTime)
  else
    uqOrdersNEW_ORDER_DATE.AsString := '';

  if  uqOrdersPROOF_DONE.AsString <> '' then
    uqOrdersNEW_PROOF_DONE.AsString := FormatDateTime('m/d/yyyy HH:nn', uqOrdersPROOF_DONE.AsDateTime)
  else
    uqOrdersNEW_PROOF_DONE.AsString := '';

  if  uqOrdersART_DONE.AsString <> '' then
    uqOrdersNEW_ART_DONE.AsString := FormatDateTime('m/d/yyyy HH:nn', uqOrdersART_DONE.AsDateTime)
  else
    uqOrdersNEW_ART_DONE.AsString := '';

  if  uqOrdersPLATE_DONE.AsString <> '' then
    uqOrdersNEW_PLATE_DONE.AsString := FormatDateTime('m/d/yyyy HH:nn', uqOrdersPLATE_DONE.AsDateTime)
  else
    uqOrdersNEW_PLATE_DONE.AsString := '';

  if  uqOrdersMOUNT_DONE.AsString <> '' then
    uqOrdersNEW_MOUNT_DONE.AsString := FormatDateTime('m/d/yyyy HH:nn', uqOrdersMOUNT_DONE.AsDateTime)
  else
    uqOrdersNEW_MOUNT_DONE.AsString := '';

  if  uqOrdersSHIP_DONE.AsString <> '' then
    uqOrdersNEW_SHIP_DONE.AsString := FormatDateTime('m/d/yyyy HH:nn', uqOrdersSHIP_DONE.AsDateTime)
  else
    uqOrdersNEW_SHIP_DONE.AsString := '';
end;

//create new field called color count
function TrptOrderList.getColorCount(colors: string): string;
var
  colorObject: TJSONObject;
  colorList: TJSONArray;
begin
  if colors = '' then
    result := '0'
  else
  begin
    colorObject := TJSONObject.ParseJSONValue(colors) as TJSONObject;
    try
      colorList := TJSONArray(colorObject.GetValue('items'));
      result := IntToStr(colorList.Count);
    finally
      colorObject.Free;
    end;
  end;
end;

function TrptOrderList.GeneratePDF: string;
var
  ReportDir, ReportFileName: string;
  reportURL: string;
begin
  ReportDir := ServerConfig.reportsFolder;

  if not DirectoryExists(ReportDir + 'reports\') then
  begin
    ForceDirectories(ReportDir + 'reports\');
    Logger.Log(1, 'Reports directory created: ' + ReportDir + 'reports\');
  end;

  reportURL :=  'reports/' + FormatDateTime('yyyymmdd_hhnnss', Now) + '.pdf';

  ReportFileName := reportDir + reportUrl;

  frxPDFExport1.FileName := ReportFileName;
  frxPDFExport1.ShowDialog := False;
  try
    frxOrderList.PrepareReport;
    frxOrderList.Export(frxPDFExport1);
    //frxOrders.ShowPreparedReport;
  finally
     frxOrderList.Clear; // Clears the report to avoid memory bloat
  end;

  Logger.Log(5, 'PDF saved to: ' + ReportFileName);
  result := reportURL;
end;

end.

