unit uSvcCustomSDG;

interface

uses
  System.Generics.Collections,
  System.DateUtils,
  System.SysUtils,
  JsonDataObjects,
  uSvc_Base,
  uSvc_ObehZbozi,
  uCommons, // kvuli EServiceException
  helTabsBIDs,
  uHeOObj_Base,
  uHeOObj_CustomSDG;


const
{$I globalConsts.inc}
  selSpecDoklSD = '(SELECT CisloOrg FROM ' + tblCOrg + ' WHERE ID=main.IDOrg) AS CisloOrg';


type

  TGatemaSDDokladService = class(TServiceBase)
   public
    function GetAll: TObjectList<TGatemaSDDoklad>;
    function GetByParams (params: TDictionary<string, string>): TObjectList<TGatemaSDDoklad>;
    function GetByID (const AID: integer; const bezPolozek: boolean=false): TGatemaSDDoklad;
    function GetMeta: TJSONObject;
  end;


  TGatemaSDScanDataService = class(TServiceBase)
   public
    function GetByParams (params: TDictionary<string, string>): TObjectList<TGatemaSDScanData>;
    function GetByID (const AID: integer): TGatemaSDScanData;
    function GetMeta: TJSONObject;
  end;



  TSDGPolozkaOZService = class(TPolozkaOZService)
    function GetByID (const AID: integer): TPohybOZ;
   end;



implementation

uses
  System.StrUtils,
  FireDAC.Stan.Option,
  FireDAC.Comp.Client,
  FireDAC.Stan.Param,
  MVCFramework.FireDAC.Utils,
  MVCFramework.DataSet.Utils,
  MVCFramework.Serializer.Commons;


{ TGatemaSDDokladService }

 function TGatemaSDDokladService.GetAll:TObjectList<TGatemaSDDoklad>;
  var lSQL: string;
  begin
   lSQL:= 'SELECT ' + GetTabCols('dbo', tblGSDDoklady) + ' FROM ' + tblGSDDoklady + ' ORDER BY ID';
   FDM.sqlQry1.Open(lSQL, []);
   result:= FDM.sqlQry1.AsObjectList<TGatemaSDDoklad>;
   FDM.sqlQry1.Close;
  end;



 function TGatemaSDDokladService.GetByParams(params: TDictionary<string, string>): TObjectList<TGatemaSDDoklad>;
  var lSQL, where, odDatum, doDatum, sklad, formatX, druhPohybuZbo, strTemp: string;
      lQry: TFDQuery;
      datOd, datDo: TDateTime;
      id, iDZ, cnt, rNo: integer;
      jenSeznam: Boolean;
      d: TGatemaSDDoklad;
      dList: TObjectList<TGatemaSDDoklad>;
  begin
   jenSeznam:= false;
   if (params.ContainsKey('seznam')) then
    jenSeznam:= true;

   formatX:= '';
   if (params.ContainsKey('format')) then
    formatX:= params.Items['format'];
   if (formatX<>'') then
     begin
     end
    else
     begin
      if not(jenSeznam) then
        lSQL:= 'SELECT ' + GetTabCols('dbo', tblGSDDoklady, 'main', false) + IfThen(selSpecDoklSD<>'', ',' + selSpecDoklSD, '') + ' FROM ' + tblGSDDoklady + ' main'
       else
        lSQL:= 'SELECT main.ID, main.TypDokladu, main.PodtypDokladu, main.RadaDokladu, main.Sklad, main.RadaDokladuCil, main.SkladCil, main.IDDokZbo, main.IDDokZbo_New'
             + ', main.IDOrg' + IfThen(selSpecDoklSD<>'', ',' + selSpecDoklSD, '') + ', main.DatPorizeni, main.DatGenerovani, main.DatumPripadu, main.PopisDodavky'
             + ' FROM ' + tblGSDDoklady + ' main';
     end;

   where:= '';

   id:= 0;
   if (params.ContainsKey('id')) then
    if (params.Items['id']<>'') then
     if (params.Items['id']<>'0') then
      begin
       id:= params.Items['id'].ToInteger;
       where:= where + 'main.ID=' + id.ToString;
      end;

   if (params.ContainsKey('typDokladu')) then
    if (params.Items['typDokladu']<>'') then
     where:= where + IfThen(where<>'', ' AND ', '') + 'main.TypDokladu=N' + params.Items['typDokladu'];

   if (params.ContainsKey('druhPohybu')) then
    if (params.Items['druhPohybu']<>'') then
     where:= where + IfThen(where<>'', ' AND ', '') + 'main.DruhPohybuZbo=N' + params.Items['druhPohybu'];

   if (params.ContainsKey('sklad')) then
    if (params.Items['sklad']<>'') then
     where:= where + IfThen(where<>'', ' AND ', '') + 'main.Sklad=N' + params.Items['sklad'].QuotedString;


   if (params.ContainsKey('odDatum')) then
    if (params.Items['odDatum']<>'') then
     odDatum:= params.Items['odDatum'];
   if (odDatum<>'') then
     begin
      if (Length(odDatum)=8) then
       odDatum:= odDatum + '000000';
      if (Length(odDatum)=10) then
       odDatum:= odDatum + '00';
      odDatum:= MidStr(odDatum, 7, 2) + '.' + MidStr(odDatum, 5, 2) + '.' + LeftStr(odDatum, 4) + ' ' + MidStr(odDatum, 9, 2) + ':' + MidStr(odDatum, 11, 2) + ':' + MidStr(odDatum, 13, 2);
      if not(TryStrToDateTime(odDatum, datOd)) then
       datOd:= IncDay(Now, -180)
     end
    else
     datOd:= IncDay(Now, -180);

   if (params.ContainsKey('doDatum')) then
    if (params.Items['doDatum']<>'') then
     odDatum:= params.Items['doDatum'];
   if (doDatum<>'') then
     begin
      if (Length(doDatum)=8) then
       doDatum:= doDatum + '235959';
      if (Length(doDatum)=10) then
       doDatum:= doDatum + '59';
      doDatum:= MidStr(doDatum, 7, 2) + '.' + MidStr(doDatum, 5, 2) + '.' + LeftStr(doDatum, 4) + ' ' + MidStr(doDatum, 9, 2) + ':' + MidStr(doDatum, 11, 2) + ':' + MidStr(doDatum, 13, 2);
      if not(TryStrToDateTime(doDatum, datDo)) then
       datDo:= Now
     end
    else
     datDo:= Now;


   strTemp:= '';
   if (odDatum<>'') then
     strTemp:= 'CONVERT(datetime, N' + FormatDateTime('dd.mm.yyyy', datOd).QuotedString + ',104)'
    else
     strTemp:= 'DATEADD(day, -1, GETDATE())';

   where:= where + IfThen(where<>'', ' AND ', '') + 'main.DatPorizeni>=' + strTemp;

   if (doDatum<>'') then
    begin
     strTemp:= 'CONVERT(datetime, N' + FormatDateTime('dd.mm.yyyy', datDo).QuotedString + ',104)';
     where:= where + IfThen(where<>'', ' AND ', '') + 'main.DatPorizeni<=' + strTemp;
    end;


   if (where<>'') then
    lSQL:= lSQL + ' WHERE ' + IfThen(id>0, 'main.ID=' + id.ToString, where);
   lSQL:= lSQL + ' ORDER BY main.ID';

   lSQL:= lSQL.Replace('Gatema_SDDoklady.', 'main.');



   lQry:= TFDQuery.Create(nil);
   try
     lQry.Connection:= FDM.sqlConn;
     lQry.FetchOptions.AutoClose:= false;
     lQry.FetchOptions.Mode:= fmAll;
     lQry.Open(lSQL);

     cnt:= lQry.RecordCount;
     if (cnt>0) then
       begin
          d:= TGatemaSDDoklad.Create;
          dList:= TObjectList<TGatemaSDDoklad>.Create;
          try
            rNo:= 1;
            lQry.First;
            while (rNo<=cnt) do // nouzove reseni, lQry se po nacteni GetbyID sama uzavre !!
             begin
              lQry.RecNo:= rNo;
              iDZ:= lQry.FieldByName('ID').AsInteger;
              d:= self.GetByID(iDZ, jenSeznam);
              dList.Add(d);
              if not(lQry.Active) then
               lQry.Open;
              Inc(rNo);
             end;
            result:= dList;
           except on E:Exception do
            raise EServiceException.Create('Chyba načítání dokladu: ' + E.Message);
          end;
       end
      else
       raise EServiceException.Create('Vybraným podmínkám neodpovídá žádný naskenovaný doklad');
    finally
     FreeAndNil(lQry);
   end;

  end;



 function TGatemaSDDokladService.GetByID (const AID: integer; const bezPolozek: boolean=false): TGatemaSDDoklad;
  var lSQL: string;
      p: TDictionary<string, string>;
      ps: TGatemaSDScanDataService;
      lQry: TFDQuery;
      sqlConnX: TFDConnection;
  begin

   ps:= TGatemaSDScanDataService.Create(self.FDM);
   p:= TDictionary<string, string>.Create;
   p.Add('iddoklad', AID.ToString);

   sqlConnX:= TFDConnection.Create(nil);
   sqlConnX.ConnectionDefName:= sqlPoolName;

   lSQL:= 'SELECT ' + GetTabCols('dbo', tblGSDDoklady, 'main', false) + IfThen(selSpecDoklSD<>'', ',' + selSpecDoklSD, '') + ' FROM ' + tblGSDDoklady + ' main WHERE main.ID=:ID';
   lQry:= TFDQuery.Create(nil);
   try
     lQry.Connection:= sqlConnX;
     lQry.Open (lSQL, [AID]);
     lQry.First;
     if not(lQry.EOF) then
      begin
       result:= lQry.AsObject<TGatemaSDDoklad>;
       if not(bezPolozek) then
        result.ScanData:= ps.GetByParams(p);
      end
     else
       raise EServiceException.Create('Naskenovaný doklad s ID ' + AID.ToString + ' nebyl nalezen.');
    finally
     p.Free;
     ps.Free;
     FreeAndNil(lQry);
   end;

   sqlConnX.Close;
   sqlConnX.Free;
  end;



 function TGatemaSDDokladService.GetMeta: TJSONObject;
  var lSQL: string;
      lQry: TFDQuery;
  begin
   lSQL:= 'SELECT ' + GetTabCols('dbo', tblGSDDoklady, 'main', false) + IfThen(selSpecDoklSD<>'', ',' + selSpecDoklSD, '') + ' FROM ' + tblGSDDoklady + ' main WHERE 1=0';
   lQry:= TFDQuery.Create(nil);
//   lQry.FetchOptions.AutoClose:= false;
//   lQry.FetchOptions.Mode:= fmAll;
   try
     lQry.Connection:= FDM.sqlConn;
     lQry.Open(lSQL);
     Result:= lQry.MetadataAsJSONObject();
    finally
     lQry.Free;
   end;
  end;





{ TGatemaSDScanDataService }

 function TGatemaSDScanDataService.GetByID (const AID: integer): TGatemaSDScanData;
  var lSQL: string;
      lQry: TFDQuery;
  begin
   lSQL:= 'SELECT ' + GetTabCols('dbo', tblGSDScanData) + ' FROM ' + tblGSDScanData + ' WHERE ID=:ID';

   lQry:= TFDQuery.Create(nil);
   try
     lQry.Connection:= FDM.sqlConn;
     lQry.Open(lSQL, [AID]);
     lQry.First;
     if not(lQry.EOF) then
      result:= lQry.AsObject<TGatemaSDScanData>
     else
      raise EServiceException.Create('Naskenovaná položka s ID ' + AID.ToString + ' nebyla nalezena.');
    finally
     lQry.Free;
   end;
  end;



 function TGatemaSDScanDataService.GetMeta: TJSONObject;
  var lSQL: string;
      lQry: TFDQuery;
  begin
   lSQL:= 'SELECT ' + GetTabCols('dbo', tblGSDScanData) + ' FROM ' + tblGSDScanData + ' WHERE 1=0';
   lQry:= TFDQuery.Create(nil);
   lQry.Connection:= FDM.sqlConn;
   lQry.FetchOptions.AutoClose:= false;
   lQry.FetchOptions.Mode:= fmAll;
   try
     lQry.Open(lSQL);
     Result:= lQry.MetadataAsJSONObject();
    finally
     lQry.Free;
   end;
  end;



 function TGatemaSDScanDataService.GetByParams(params: TDictionary<string, string>): TObjectList<TGatemaSDScanData>;
  var lSQL, where: string;
      id,idPZ, cnt, rNo: integer;
      lQry: TFDQuery;
      pList: TObjectList<TGatemaSDScanData>;
      p: TGatemaSDScanData;
  begin
   p:= TGatemaSDScanData.Create;
   pList:= TObjectList<TGatemaSDScanData>.Create;

   lSQL:= 'SELECT ' + GetTabCols('dbo', tblGSDScanData, '', false) + ' FROM ' + tblGSDScanData;

   where:= '';

   id:= 0;
   if (params.ContainsKey('id')) then
    if (params.Items['id']<>'') then
     if (params.Items['id']<>'0') then
      begin
       id:= params.Items['id'].ToInteger;
       where:= where + 'ID=' + id.ToString;
      end;

   if (params.ContainsKey('iddoklad')) then
    if (params.Items['iddoklad']<>'') then
     where:= where + IfThen(where<>'', ' AND ', '') + 'IDDokladSD=' + params.Items['iddoklad'];

   if (where<>'') then
    lSQL:= lSQL + ' WHERE ' + IfThen(id>0, 'ID=' + id.ToString, where);
   lSQL:= lSQL + ' ORDER BY IDDokladSD, ID';

//   lSQL:= lSQL.Replace('TabPohybyZbozi.', 'main.');


   lQry:= TFDQuery.Create(nil);
   lQry.Connection:= FDM.sqlConn;
   lQry.FetchOptions.AutoClose:= false;
   lQry.FetchOptions.Mode:= fmAll;
   lQry.Open(lSQL);
   try
     cnt:= lQry.RecordCount;
     pList:= TObjectList<TGatemaSDScanData>.Create;
     try
       lQry.First;
       rNo:= 1;
       while (rNo<=cnt) do
        begin
         lQry.RecNo:= rNo;
         idPZ:= lQry.FieldByName('ID').AsInteger;
         p:= self.GetByID (idPZ);
         pList.Add(p);
         if not(lQry.Active) then
          lQry.Open;
         Inc(rNo);
        end;
       result:= pList;
      except on E:Exception do
       raise EServiceException.Create('Chyba načítání naskenované položky/položek dokladu: ' + E.Message);
     end;
    finally
     FreeAndNil(lQry);
   end;
  end;





{ TSDGPolozkaOZService }

 function TSDGPolozkaOZService.GetByID (const AID: Integer): TPohybOZ;
  begin
   result:= nil;
  end;


end.