unit uSvc_ObehZbozi;

interface

{$I GlobalDefs.inc}

uses
  System.Generics.Collections,
  System.SysUtils,
  FireDAC.Stan.Option,
  FireDAC.Comp.Client,
  FireDAC.Stan.Param,
  JsonDataObjects,
  uSvc_Base,
  uCommons,
  helTabsBIDs,
  uHeoObj_Base;

const
{$I globalConsts.inc}
  selSpecDZ = 'Realizovano';

  selSpecVyrCP = '(SELECT Nazev1 FROM ' + tblVyrCS + ' WHERE ID=main.IDVyrCis) AS #VyrCis#';

{$IFDEF CUSTOM_CTRL_Rootvin}
  {$I '_custom/Rootvin/uTabs.inc'}
{$ENDIF}

type

  TTSerioveCisloStavService = class(TServiceBase)
   public
    function GetMeta: TJSONObject;
  end;


  TUmisteniService = class(TServiceBase)
   public
    function GetMeta: TJsonObject; virtual;
    function GetByID (AId: integer): TUmisteni; virtual;
  end;

  TSerioveCisloPohybOZService = class(TServiceBase)
   public
    function GetMeta: TJSONObject; virtual;
    function GetByParams (params: TDictionary<string, string>; AConn: TFDConnection = nil): TObjectList<TSerioveCisloPohybOZ>; virtual;
  end;



  TStavSkladuService = class(TServiceBase)
   public
    function GetMeta: TJSONObject; virtual;
    function GetByParams (params: TDictionary<string, string>; AConn: TFDConnection = nil): TObjectList<TStavSkladu>; virtual;
  end;


  TDokladOZService = class(TServiceBase)
   public
    function GetMeta: TJSONObject; virtual;
    function GetByID (const AID: integer; sdServer: boolean=false; params: TDictionary<string, string>=nil; AConn: TFDConnection = nil): TDokladOZ; virtual;
    function GetByParams (params: TDictionary<string, string>; AConn: TFDConnection = nil): TObjectList<TDokladOZ>; virtual;
    function GetDruhyDokladu (druhpohybu: string): TObjectList<TDruhDokZbo>; virtual;
  end;


  TPolozkaOZService = class(TServiceBase)
   public
    function GetMeta: TJSONObject; virtual;
    function GetByID (const AID: integer; sdServer: boolean=false; AConn: TFDConnection = nil): TPohybOZ; virtual;
    function GetByParams (params: TDictionary<string, string>; AConn: TFDConnection = nil): TObjectList<TPohybOZ>; virtual;
  end;




implementation

uses
  System.StrUtils,
  System.DateUtils,
  MVCFramework.FireDAC.Utils,
  MVCFramework.DataSet.Utils,
  MVCFramework.Serializer.Commons;






{ TStavSkladuService }

 function TStavSkladuService.GetByParams (params: TDictionary<string, string>; AConn: TFDConnection = nil): TObjectList<TStavSkladu>;
  var lSQL, where, odDatum, doDatum, strTemp: string;
      datOd, datDo: TDateTime;
      id: integer;
      lQry: TFDQuery;
      sqlConnX: TFDConnection;
      lokalniConnection: boolean;
  begin
   result:= nil;

   lokalniConnection := (AConn = nil);
   if (lokalniConnection) then
     begin
      sqlConnX:= TFDConnection.Create(nil);
      sqlConnX.ConnectionDefName:= sqlPoolName;
     end
    else
     sqlConnX := AConn;


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

   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('idsklad')) then
    if (params.Items['idsklad']<>'') then
     where:= where + IfThen(where<>'', ' AND ', '') + 'IDSklad=N' + params.Items['idsklad'].QuotedString;

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


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

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

   lQry:= TFDQuery.Create(nil);


   try
     sqlConnX.Connected := true;
     lQry.Connection:= sqlConnX;
     try
       lQry.Open(lSQL);
       result:= lQry.AsObjectList<TStavSkladu>;
       lQry.Close;
      except on E:Exception do
       raise EServiceException.Create('Chyba načítání dokladu: ' + E.Message);
     end;
    finally
     lQry.Free;
   end;

   if (lokalniConnection) then
    begin
     sqlConnX.Close;
     sqlConnX.Free;
    end;
  end;




 function TStavSkladuService.GetMeta: TJSONObject;
  var lSQL: string;
      lQry: TFDQuery;
  begin
   lSQL:= 'SELECT ' + GetTabCols('dbo', tblSS) + ' FROM ' + tblSS + ' WHERE 1=0';

   lQry:= TFDQuery.Create(nil);
   try
     lQry.FetchOptions.Mode:= fmAll;
     lQry.Connection:= FDM.sqlConn;
     lQry.Open(lSQL);
     Result:= lQry.MetadataAsJSONObject();
    finally
     lQry.Free;
   end;
  end;








{ TTSerioveCisloStavService }

 function TTSerioveCisloStavService.GetMeta: TJSONObject;
  var lSQL: string;
      lQry: TFDQuery;
  begin
   lSQL:= 'SELECT ' + GetTabCols('dbo', tblVyrCS) + ' FROM ' + tblVyrCS + ' WHERE 1=0';

   lQry:= TFDQuery.Create(nil);
   try
     lQry.FetchOptions.Mode:= fmAll;
     lQry.Connection:= FDM.sqlConn;
     lQry.Open(lSQL);
     result:= lQry.MetadataAsJSONObject();
    finally
     lQry.Free;
   end;
  end;







{ TDokladOZService }

 function TDokladOZService.GetDruhyDokladu (druhpohybu: string): TObjectList<TDruhDokZbo>;
  var lSQL: string;
      i: integer;
      lQry: TFDQuery;
      sqlConnX: TFDConnection;
  begin
   result:= nil;

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

   i:= -1;
   if not(TryStrToInt(druhpohybu, i)) then
    i:= -1;
   if (i>-1) then
    lSQL:= lSQL + ' WHERE DruhPohybuZbo=' + i.ToString;

   lSQL:= lSQL + ' ORDER BY DruhPohybuZbo, RadaDokladu';

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

   lQry:= TFDQuery.Create(nil);
   try
     lQry.FetchOptions.Mode:= fmAll;
     lQry.Connection:= sqlConnX;
     try
       lQry.Open(lSQL);
       result:= lQry.AsObjectList<TDruhDokZbo>;
      except on E:Exception do
       raise EServiceException.Create('Chyba načítání druhů dokladu: ' + E.Message);
     end;
    finally
     lQry.Free;
   end;

   sqlConnX.Close;
   sqlConnX.Free;
  end;




 function TDokladOZService.GetByParams (params: TDictionary<string, string>; AConn: TFDConnection = nil): TObjectList<TDokladOZ>;
  var lSQL, lSQLmin, where, odDatum, doDatum, sklad, strTemp: string;
      lQry: TFDQuery;
      sqlConnX: TFDConnection;
      datOd, datDo: TDateTime;
      id, iDZ, idPrikaz, cnt, rNo: integer;
      jenSeznam, polozky, minimum: Boolean;
      resList: TObjectList<TDokladOZ>;
      resObj: TDokladOZ;
      noDatum, snZSDServeru: boolean;
      radyD, sklady, dpz: string;
      p: TDictionary<string, string>;
      lokalniConnection: boolean;
      lokIDcka: TList<integer>;
  begin
   result:= nil;

   lokalniConnection := (AConn = nil);
   if (lokalniConnection) then
     begin
      sqlConnX:= TFDConnection.Create(nil);
      sqlConnX.ConnectionDefName:= sqlPoolName;
     end
    else
     sqlConnX := AConn;


   p:= TDictionary<string, string>.Create;
   radyD:= '';
   dpz:= '';

   snZSDServeru:= false;
   if (params.ContainsKey('sdServer')) then
    if (params.Items['sdServer']='1') then
     snZSDServeru:= true;

   polozky:= false;
   if (params.ContainsKey('polozky')) then
    begin
     p.Add('polozky', params.Items['polozky']);
     if (params.Items['polozky']='1') then
      begin
       polozky:= true;
       jenSeznam:= false;
      end;
    end;

   minimum:= false;
   if (params.ContainsKey('minimum')) then
    begin
     p.Add('minimum', params.Items['minimum']);
     p.Add('minimumDat', params.Items['minimum']);
     if (params.Items['minimum']='1') then
      minimum:= true;
    end;

   jenSeznam:= false;
   if (params.ContainsKey('seznam')) then
    if (params.Items['seznam']='1') then
     jenSeznam:= true;


   lSQL:= 'SELECT ID, ParovaciZnak FROM ' + tblDZ;
   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;

   idPrikaz:= 0;
   if (params.ContainsKey('idPrikaz')) then
    if (params.Items['idPrikaz']<>'') then
     if not(TryStrToInt(params.Items['idPrikaz'], idPrikaz)) then
      idPrikaz:= 0;
   if (idPrikaz>0) then
    where:= where + IfThen(where<>'', ' AND ', '') + ' (IDPrikaz=' + idPrikaz.ToString + ' OR EXISTS(SELECT 1 FROM ' + tblPZ + ' p WHERE p.IDDoklad=ID AND p.IDPrikaz=IDPrikaz AND p.TypVyrobnihoDokladu IS NOT NULL))';

   noDatum:= (params.ContainsKey('noDatum'));

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

   if (params.ContainsKey('druhpohybu')) then
    if (params.Items['druhpohybu']<>'') then
     begin
      dpz:= params.Items['druhpohybu'];
      where:= where + IfThen(where<>'', ' AND ', '') + 'DruhPohybuZbo=' + dpz;
     end;

   if (params.ContainsKey('radadokladu')) then
    if (params.Items['radadokladu']<>'') then
     begin
      radyD:= params.Items['radadokladu'];
      radyD:= StrToNQuotedList (radyD);
      where:= where + IfThen(where<>'', ' AND ', '') + 'RadaDokladu IN (' + radyD + ')';
     end;

   if (params.ContainsKey('sklad')) then
    if (params.Items['sklad']<>'') then
     begin
      sklady:= params.Items['sklad'];
      sklady:= StrToNQuotedList (sklady);
      where:= where + IfThen(where<>'', ' AND ', '') + 'IDSklad IN (' + sklady + ')';
     end;


   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, -1)
     end
    else
     datOd:= IncDay(Now, -1);


   if (params.ContainsKey('doDatum')) then
    if (params.Items['doDatum']<>'') then
     doDatum:= 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 not(noDatum) then
    begin
     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 ', '') + 'DatPorizeni>=' + strTemp;
    end;

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


   if (params.ContainsKey('jenRealizovane')) then
    if (params.Items['jenRealizovane']='1') then
     where:= where + IfThen(where<>'', ' AND ', '') + 'DatRealizace IS NOT NULL';
   if (params.ContainsKey('jenReal')) then
    if (params.Items['jenReal']='1') then
     where:= where + IfThen(where<>'', ' AND ', '') + 'DatRealizace IS NOT NULL';


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

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


{$IFDEF CUSTOM_CTRL_Rootvin}
   if (radyD<>'') then
    begin
     if (dpz='0') and (radyD.Contains('251') or radyD.Contains('261')) then
      snZSDServeru:= true;
    end;
{$ENDIF}


   lQry:= TFDQuery.Create(nil);
   try
     sqlConnX.Connected := true;
     lQry.FetchOptions.Mode := fmAll;
     lQry.Connection := sqlConnX;
     lQry.Open (lSQL);
     cnt := lQry.RecordCount;
     if (cnt>0) then
       begin
        lQry.Close;
        lokIDcka:= TList<integer>.Create;
        try
          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;
              lokIDcka.Add (iDZ);
              if not(lQry.Active) then
               lQry.Open;
              Inc(rNo);
             end;
            lQry.Close;
           except on E:Exception do
            raise EServiceException.Create('Chyba načítání dokladu: ' + E.Message);
          end;
  //        FreeAndNil(dList);
          resList:= TObjectList<TDokladOZ>.Create;
          try
            for iDZ in lokIDcka do
             begin
              resObj:= self.GetByID (iDZ, snZSDServeru, p, sqlConnX);
              resList.Add (resObj);
             end;
            result:= resList;
            resList:= nil;
           finally
            resList.Free;
          end;

         finally
          lokIDcka.Free;
        end;
       end
      else
       raise EServiceException.Create('Vybraným podmínkám neodpovídá žádný doklad');
    finally
     p.Free;
     lQry.Close;
     lQry.Free;
     if (lokalniConnection) then
      begin
       sqlConnX.Close;
       sqlConnX.Free;
      end;
   end;

  end;




 function TDokladOZService.GetByID (const AID: Integer; sdServer: boolean=false; params: TDictionary<string, string>=nil; AConn: TFDConnection = nil): TDokladOZ;
  var lSQL, extInfoStr, errMsg: string;
      minimumDat, polozky: boolean;
      p: TDictionary<string, string>;
      ps: TPolozkaOZService;
      lQry: TFDQuery;
      sqlConnX: TFDConnection;
      lokalniConnection: boolean;
  begin
   result := nil;

   lokalniConnection := (AConn = nil);
   if (lokalniConnection) then
     begin
      sqlConnX:= TFDConnection.Create(nil);
      sqlConnX.ConnectionDefName:= sqlPoolName;
     end
    else
     sqlConnX:= AConn;


   extInfoStr:= '';
   if (SQLTableExists ('dbo', tblDZe)) then
    begin
     lSQL:= 'SELECT ' + GetTabCols ('dbo', tblDZe, '', true, 'ID') + ' FROM ' + tblDZe + ' WHERE ID=:ID';
     lQry:= TFDQuery.Create(nil);
     lQry.Connection:= FDM.sqlConn;
     try
       lQry.Open(lSQL, [AID]);
       if (lQry.RecordCount>0) then
        extInfoStr:= lQry.AsJSONObject;
       lQry.Close;
      finally
       lQry.Free;
     end;
    end;


   minimumDat:= false;
   polozky:= false;

   ps:= TPolozkaOZService.Create (self.FDM);
   p:= TDictionary<string, string>.Create;
   p.Add ('iddoklad', AID.ToString);
   p.Add ('sdServer', IfThen(sdServer, '1', '0'));
   p.Add ('minimum', IfThen(minimumDat, '1', '0'));

   if Assigned(params) then
    begin
     if (params.ContainsKey('minimumDat')) then
      if (params.Items['minimumDat']='1') then
       minimumDat:= true;

     if (params.ContainsKey('polozky')) then
      if (params.Items['polozky']='1') then
       polozky:= true;

     if (params.ContainsKey('minimum')) and not(p.ContainsKey('minimum')) then
      p.Add ('minimum', params.Items['minimum']);
    end;



   if (minimumDat) then
      lSQL:= 'SELECT ID, DruhPohybuZbo, RadaDokladu, PoradoveCislo, ParovaciZnak, CisloOrg, MistoUrceni, DatPorizeni, DatPorizeniSkut, DatRealizace, Realizovano'
                  + ', RealizovanoMnoz, PrevodRealizovatMno, DatRealMnoz, Splatnost, CisloZakazky, Autor, Mena, Kurz, DUZP, IDSkladPrevodu, DruhPohybuPrevod'
                  + ', AVAReferenceID' + IfThen(selSpecDZ<>'', ',' + selSpecDZ, '')
     else
      lSQL:= 'SELECT ' + GetTabCols('dbo', tblDZ, '', false) + IfThen(selSpecDZ<>'', ',' + selSpecDZ, '');

   lSQL:= lSQL + ' FROM ' + tblDZ + ' WHERE ID=:ID';


   lQry:= TFDQuery.Create(nil);
   try
     sqlConnX.Connected := true;
     lQry.FetchOptions.Mode:= fmAll;
     lQry.Connection:= sqlConnX;
     lQry.Open(lSQL, [AID]);
     lQry.First;
     if not(lQry.EOF) then
      begin
       result:= lQry.AsObject<TDokladOZ>;
       if (extInfoStr<>'') then
        result.ExtInfo:= ExtInfoStr;
       if (polozky) then
        result.PohybyOZ:= ps.GetByParams (p);
      end
     else
      raise EServiceException.Create('Doklad oběhu zboží s ID ' + AID.ToString + ' nebyl nalezen.');
    finally
     p.Free;
     ps.Free;
     lQry.Close;
     lQry.Free;
     if (lokalniConnection) then
      begin
       sqlConnX.Close;
       sqlConnX.Free;
      end;
   end;

  end;




 function TDokladOZService.GetMeta: TJSONObject;
  var lSQL: string;
      lQry: TFDQuery;
  begin
   lSQL:= 'SELECT ' + GetTabCols('dbo', tblDZ, '', false) + ' FROM ' + tblDZ + ' WHERE 1=0';

   lQry:= TFDQuery.Create(nil);
   lQry.Connection:= FDM.sqlConn;
   try
     lQry.Open(lSQL);
     Result:= lQry.MetadataAsJSONObject();
    finally
     lQry.Free;
   end;
  end;







{ TPolozkaOZService }

 function TPolozkaOZService.GetByParams (params: TDictionary<string, string>; AConn: TFDConnection = nil): TObjectList<TPohybOZ>;
  var lSQL, where: string;
      id, AID, cnt, rNo: integer;
      lQry: TFDQuery;
      sqlConnX: TFDConnection;
      resList: TObjectList<TPohybOZ>;
      resObj: TPohybOZ;
      sdServer: boolean;
      lokalniConnection: boolean;
      lokIDcka: TList<integer>;
  begin
   result:= nil;

   lokalniConnection := (AConn = nil);
   if (lokalniConnection) then
     begin
      sqlConnX:= TFDConnection.Create(nil);
      sqlConnX.ConnectionDefName:= sqlPoolName;
     end
    else
     sqlConnX := AConn;


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

   where:= '';

// vyrobni cisla polozky z tabulky Gatema_SDScanData
   sdServer:= false;
   if (params.ContainsKey('sdServer')) then
    if (params.Items['sdServer']='1') then
     sdServer:= true;


   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 ', '') + 'IDDoklad=' + params.Items['iddoklad'];

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

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


   sqlConnX.Connected := true;
   lQry:= TFDQuery.Create(nil);
   try
     lQry.FetchOptions.Mode := fmAll;
     lQry.Connection := sqlConnX;
     lQry.Open (lSQL);
     cnt := lQry.RecordCount;
     lokIDcka := TList<integer>.Create;
     try
       lQry.First;
       rNo := 1;
       while (rNo<=cnt) do
        begin
         lQry.RecNo := rNo;
         AID := lQry.FieldByName('ID').AsInteger;
         lokIDcka.Add (AID);
         if not(lQry.Active) then
          lQry.Open;
         Inc (rNo);
        end;
      except on E:Exception do
       raise EServiceException.Create('Chyba načítání položky/položek dokladu: ' + E.Message);
     end;
//     pList.Free;

     resList := TObjectList<TPohybOZ>.Create;
     try
       for AID in lokIDcka do
        begin
         resObj := self.GetByID (AID, sdServer, sqlConnX);
         resList.Add (resObj);
        end;
       result := resList;
       resList := nil;
      finally
       resList.Free;
     end;

    finally
     lokIDcka.Free;
     lQry.Close;
     FreeAndNil (lQry);
   end;

   if (lokalniConnection) then
    begin
     sqlConnX.Close;
     sqlConnX.Free;
    end;
  end;




 function TPolozkaOZService.GetByID (const AID: Integer; sdServer: boolean=false; AConn: TFDConnection = nil): TPohybOZ;
  var lSQL, lSQL2, sz, rDokl, dpz, skl, sTemp: string;
      p: TDictionary<string, string>;
      pds: TSerioveCisloPohybOZService;
      us: TUmisteniService;
      u, iTemp: Integer;
      lQry, lQry2: TFDQuery;
      sqlConnX, sqlConnX2: TFDConnection;
      extInfoStr: string;
      lokalniConnection: boolean;
      lokIDcka: TList<integer>;
{$IFDEF CUSTOM_CTRL_Rootvin}
      silo: string;
      siloInt, idMzdy: integer;
{$ENDIF}
  begin
   result:= nil;

   lokalniConnection := (AConn = nil);
   if (lokalniConnection) then
     begin
      sqlConnX:= TFDConnection.Create(nil);
      sqlConnX.ConnectionDefName:= sqlPoolName;
     end
    else
     sqlConnX := AConn;


{$IFDEF CUSTOM_CTRL_Rootvin}
   silo:= '';
{$ENDIF}

   sqlConnX.Connected := true;
   lQry:= TFDQuery.Create(nil);
   lQry.FetchOptions.Mode:= fmAll;
   lQry.Connection:= sqlConnX;

   extInfoStr:= '';
   if (SQLTableExists('dbo', tblPZe)) then
    begin
     lSQL:= 'SELECT ' + GetTabCols('dbo', tblPZe, '', true, 'ID') + ' FROM ' + tblPZe + ' WHERE ID=:ID';
     try
       lQry.Open(lSQL, [AID]);
       if (lQry.RecordCount>0) then
        begin
{$IFDEF CUSTOM_CTRL_Rootvin}
         silo:= lQry.FieldByName('_Mouka_Silo').AsString;
{$ENDIF}
         extInfoStr:= lQry.AsJSONObject;
        end;
      finally
     end;

    end;


   sz:= '';
   dpz:= '';
   rDokl:= '';

   try
     lSQL:= 'SELECT p.SkupZbo, p.DruhPohybuZbo, d.RadaDokladu FROM ' + tblPZ + ' p INNER JOIN ' + tblDZ + ' d ON (d.ID=p.IDDoklad) WHERE p.ID=:ID';
     lQry.Open(lSQL, [AID]);
     lQry.First;
     sz:= lQry.FieldByName('SkupZbo').AsString;
     dpz:= lQry.FieldByName('DruhPohybuZbo').AsString;
     rDokl:= lQry.FieldByName('RadaDokladu').AsString;
    finally
   end;



   lSQL:= 'SELECT ' + GetTabCols('dbo', tblPZ, '', false) + ' FROM ' + tblPZ + ' WHERE ID=:ID';

   pds:= TSerioveCisloPohybOZService.Create (self.FDM);
   us:= TUmisteniService.Create (self.FDM);
   p:= TDictionary<string, string>.Create;
   p.Add('idPolozkaOZ', AID.ToString);


   try
     lQry.Open(lSQL, [AID]);
     lQry.First;
     if not(lQry.EOF) then
      begin
       u:= 0;
       if (lQry.FieldByName('IdUmisteni')<>nil) then
        u:= lQry.FieldByName('IdUmisteni').AsInteger;
       result:= lQry.AsObject<TPohybOZ>;

{$IFDEF CUSTOM_CTRL_Rootvin}
       lQry2:= TFDQuery.Create(nil);
       try
         lQry2.FetchOptions.Mode := fmAll;
         lQry2.Connection := sqlConnX;

         if (sz='101') and (dpz='0') and ((rDokl='251') or (rDokl='261')) then
          if (silo='') then
           begin
              lSQL := 'SELECT TOP(1) p.IDUmisteni, u.Kod FROM ' + tblPuvodniVC + ' p INNER JOIN ' + tblUmisteni + ' u ON (u.ID=p.IDUmisteni) WHERE p.IDPohybZbozi=:ID';
              lQry2.Open (lSQL, [AID]);
              lQry2.First;
              u := lQry2.FieldByName('IDUmisteni').AsInteger;
              silo := lQry2.FieldByName('Kod').AsString;
              if (silo<>'') then
               begin
                TryStrToInt (silo, siloInt);
                if (siloInt>0) then
                 begin
                  lSQL := 'IF NOT EXISTS (SELECT 1 FROM ' + tblPZe + ' WHERE ID=:ID) INSERT ' + tblPZe + ' (ID) SELECT :ID' + CRLF;
                  lSQL := lSQL + 'UPDATE ' + tblPZe + ' SET _Mouka_Silo=' + siloInt.ToString + ' WHERE ID=:ID';
                  sqlConnX.ExecSQL (lSQL, [AID]);
                 end;
               end;
           end;

         lSQL := 'SELECT IDMzdy FROM ' + tblPMZGenPZ + ' WHERE IDPohybu=:ID';
         lQry2.Open (lSQL, [AID]);
         if (lQry2.RecordCount=1) then
          result.IDMzdy := lQry2.FieldByName('IDMzdy').AsInteger;

        finally
         lQry2.Close;
         FreeAndNil (lQry2);
       end;


{$ENDIF}

       if (lokalniConnection) then
         begin
          sqlConnX2 := TFDConnection.Create(nil);
          sqlConnX2.ConnectionDefName := sqlPoolName;
         end
        else
         sqlConnX2 := AConn;

       sqlConnX2.Connected := true;
       lQry2 := TFDQuery.Create (nil);
       try
         lQry2.Connection:= sqlConnX2;


{$IFDEF CUSTOM_CTRL_Rootvin}
         if (SQLColumnExists('dbo', tblPZe, '_HDCAPI_PHident')) then
          if (SQLGeneralTest('SELECT 1 FROM  ' + tblPZe + ' WHERE ID=' + AID.ToString + ' AND _HDCAPI_PHident IS NOT NULL')) then
           begin
            lSQL:= 'SELECT ob.IdPHIdent, ob.IdPHIdent2 FROM ' + tblPZe + ' pze INNER JOIN ' + tblVyrobaObjednavky + ' ob ON (ob.ID=pze._HDCAPI_PHident) WHERE pze.ID=' + AID.ToString;
            try
              lQry2.Open(lSQL);
              lQry2.First;
              Result.PHObjIdent:= lQry2.FieldByName('IdPHIdent').AsInteger;
              Result.PHPolIdent:= lQry2.FieldByName('IdPHIdent2').AsInteger;
             finally
            end;
           end;

         sTemp:= '';
         if (SQLColumnExists('dbo', tblPZe, '_Vyroba_PaletList')) then
          if (SQLGeneralTest('SELECT 1 FROM  ' + tblPZe + ' WHERE ID=' + AID.ToString + ' AND _Vyroba_PaletList IS NOT NULL')) then
            begin
             lSQL:= 'SELECT _Vyroba_PaletList FROM ' + tblPZe + ' WHERE ID=' + AID.ToString;
             try
               lQry2.Open(lSQL);
               lQry2.First;
               sTemp:= lQry2.FieldByName('_Vyroba_PaletList').AsString;
              finally
             end;
            end;
         if (sTemp='') then
          begin
           lSQL:= 'SELECT TOP(1) PaletovyList FROM ' + tblPuvodniVC + ' WHERE IDPohybZbozi=:ID';
           try
             lQry2.Open(lSQL, [AID]);
             lQry2.First;
             sTemp:= lQry2.FieldByName('PaletovyList').AsString;
            finally
           end;
          end;
         if (sTemp<>'') then
          begin
           Result.PaletList:= sTemp;
           lSQL:= 'IF NOT EXISTS (SELECT 1 FROM ' + tblPZe + ' WHERE ID=:ID) INSERT ' + tblPZe + ' (ID) SELECT :ID' + CRLF;
           lSQL:= lSQL + 'UPDATE ' + tblPZe + ' SET _Vyroba_PaletList=N' + sTemp.QuotedString + ' WHERE ID=:ID';
           sqlConnX2.ExecSQL (lSQL, [AID]);
          end;


         sTemp:= '';
         if (SQLColumnExists('dbo', tblPZe, '_Vyroba_Sarze')) then
          if (SQLGeneralTest('SELECT 1 FROM  ' + tblPZe + ' WHERE ID=' + AID.ToString + ' AND _Vyroba_Sarze IS NOT NULL')) then
           begin
            lSQL:= 'SELECT _Vyroba_Sarze FROM ' + tblPZe + ' WHERE ID=' + AID.ToString;
            try
              lQry2.Open(lSQL);
              lQry2.First;
              sTemp:= lQry2.FieldByName('_Vyroba_Sarze').AsString;
             finally
            end;
           end;
         if (sTemp='') then
          begin
           lSQL:= 'SELECT TOP(1) Sarze FROM ' + tblPuvodniVC + ' WHERE IDPohybZbozi=:ID';
           try
             lQry2.Open(lSQL, [AID]);
             lQry2.First;
             sTemp:= lQry2.FieldByName('Sarze').AsString;
            finally
           end;
          end;
         if (sTemp<>'') then
          begin
           Result.Sarze:= sTemp;
           lSQL:= 'IF NOT EXISTS (SELECT 1 FROM ' + tblPZe + ' WHERE ID=:ID) INSERT ' + tblPZe + ' (ID) SELECT :ID' + CRLF;
           lSQL:= lSQL + 'UPDATE ' + tblPZe + ' SET _Vyroba_Sarze=N' + sTemp.QuotedString + ' WHERE ID=:ID';
           sqlConnX2.ExecSQL (lSQL, [AID]);
          end;
{$ENDIF}

        finally
         lQry2.Free;
         if (lokalniConnection) then
          begin
           sqlConnX2.Close;
           sqlConnX2.Free;
          end;
       end;

       if (extInfoStr<>'') then
        result.ExtInfo:= ExtInfoStr;
       result.SerialNums:= pds.GetByParams (p, sqlConnX);
       if (u>0) then
        begin
         Result.Umisteni:= us.GetByID (u);
{$IFDEF CUSTOM_CTRL_Rootvin}
       if (silo<>'') then
        begin
         TryStrToInt (silo, siloInt);
         if (siloInt>0) then
          result.Silo:= siloInt;
         Result.Umisteni.Kod:= silo;
         Result.Umisteni.Nazev:= 'Silo ' + silo;
        end;
{$ENDIF}
        end;
      end
     else
       raise EServiceException.Create('Položka dokladu oběhu zboží s ID ' + AID.ToString + ' nebyla nalezena.');
    finally
     lokIDcka.Free;
     p.Free;
     pds.Free;
     lQry.Close;
     FreeAndNil (lQry);
   end;

   if (lokalniConnection) then
    begin
     sqlConnX.Close;
     sqlConnX.Free;
    end;
  end;




 function TPolozkaOZService.GetMeta: TJSONObject;
  var lSQL: string;
      lQry: TFDQuery;
      extInfo: TJSONObject;
      extInfoStr: string;
  begin

   extInfoStr:= '';
   if (SQLTableExists('dbo', tblPZe)) then
    begin
     lSQL:= 'SELECT ' + GetTabCols('dbo', tblPZe, '', true) + ' FROM ' + tblPZe + ' WHERE 1=0';
     lQry:= TFDQuery.Create(nil);
     lQry.Connection:= FDM.sqlConn;
     try
       lQry.Open(lSQL);
       extInfo:= lQry.MetadataAsJSONObject();
       extInfoStr:= extInfo.ToString;
      finally
       lQry.Free;
     end;
    end;


   lSQL:= 'SELECT ' + GetTabCols('dbo', tblPZ, '', false) + ' FROM ' + tblPZ + ' WHERE 1=0';
   lQry:= TFDQuery.Create(nil);
   lQry.Connection:= FDM.sqlConn;
   try
     lQry.Open(lSQL);
     Result:= lQry.MetadataAsJSONObject();
    finally
     lQry.Free;
   end;
  end;




{ TUmisteniService }
 function TUmisteniService.GetMeta: TJSONObject;
  var lSQL: string;
      lQry: TFDQuery;
  begin
   lQry:= TFDQuery.Create(nil);
   lQry.Connection:= FDM.sqlConn;
   try
     lQry.Open('SELECT ' + GetTabCols('dbo', tblUmisteni, '', false) + ' FROM ' + tblUmisteni + ' WHERE 1=0');
     Result:= lQry.MetadataAsJSONObject();
    finally
     lQry.Free;
   end;
  end;



 function TUmisteniService.GetByID (AID: Integer): TUmisteni;
  var lSQL: string;
      lQry: TFDQuery;
  begin
   result:= nil;

   lQry:= TFDQuery.Create(nil);
   lQry.Connection:= FDM.sqlConn;
   try
     lSQL:= 'SELECT ' + GetTabCols('dbo', tblUmisteni, '', false) + ' FROM ' + tblUmisteni + ' WHERE ID=:ID';
     lQry.Open(lSQL, [AID]);
     result:= lQry.AsObject<TUmisteni>;
    finally
     lQry.Free;
   end;
  end;








{ TSerioveCisloPohybOZService }

 function TSerioveCisloPohybOZService.GetMeta: TJsonObject;
  var lSQL, sp, sp2: string;
      lQry: TFDQuery;
      sqlConnX: TFDConnection;
  begin
   sp:= selSpecVyrCP;
   sp2:= '';
   if (sp<>'') then
    begin
     while (ContainsText(sp, '#')) do
      begin
       sp:= MidStr(sp, sp.IndexOf('#')+1, sp.Length);
       sp2:= LeftStr(sp, sp.IndexOf('#')-1) + ',';
       if (sp.IndexOf('#')>0) then
         sp:= MidStr(sp, sp.IndexOf('#')+1, sp.Length);
      end;
     if (RightStr(sp2,1)=',') then
      sp2:= LeftStr(sp2, sp2.Length-1);
    end;

   lSQL:= 'SELECT ' + GetTabCols('dbo', tblVyrCP, '', false) + IfThen(sp<>'', ', ' + sp, '') + ' FROM ' + tblVyrCP + ' WHERE 1=0';

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

   lQry:= TFDQuery.Create(nil);
   try
     lQry.FetchOptions.Mode := fmAll;
     lQry.Connection := sqlConnX;
     lQry.Open (lSQL);
     Result := lQry.MetadataAsJSONObject();
     lQry.Close;
    finally
     lQry.Free;
   end;

   sqlConnX.Close;
   sqlConnX.Free;

  end;





 function TSerioveCisloPohybOZService.GetByParams (params: TDictionary<string, string>; AConn: TFDConnection = nil): TObjectList<TSerioveCisloPohybOZ>;
  var lSQL, lSQL2, where: string;
      lQry: TFDQuery;
      sqlConnX: TFDConnection;
      id, idPZ: integer;
      s: TSerioveCisloPohybOZ;
      sdServer: boolean;
      lokalniConnection: boolean;
      lokIDcka: TList<integer>;
  begin
   result:= nil;

   lokalniConnection := (AConn = nil);
   if (lokalniConnection) then
     begin
      sqlConnX := TFDConnection.Create(nil);
      sqlConnX.ConnectionDefName := sqlPoolName;
     end
    else
     sqlConnX := AConn;


   lSQL := 'SELECT ' + GetTabCols('', tblVyrCP, 'main') + IfThen(selSpecVyrCP<>'', ', ' + selSpecVyrCP.Replace('#', ''), '')
         + ', 0 AS ZdrojSD, 0 AS IDDokladGSD FROM ' + tblVyrCP + ' main';
   where := '';

   sdServer := false;
   if (params.ContainsKey('sdServer')) then
    if (params.Items['sdServer']='1') then
     sdServer := true;


   id := 0;
   if (params.ContainsKey('id')) then
    if (params.Items['id']<>'') then
     if (params.Items['id']<>'0') then
      id := params.Items['id'].ToInteger;

   idPZ := 0;
   if (params.ContainsKey('idPolozkaOZ')) then
    if (params.Items['idPolozkaOZ']<>'') then
     if (params.Items['idPolozkaOZ']<>'0') then
      begin
       idPZ := params.Items['idPolozkaOZ'].ToInteger;
       where := where + IfThen(where<>'', ' AND ', '') + 'main.IDPolozkaDokladu=' + idPZ.ToString;
      end;

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

   lSQL := lSQL.Replace('TabVyrCP.', 'main.');
   if (selSpecVyrCP='') then
    lSQL := lSQL.Replace('main.', '');

   lQry := TFDQuery.Create(nil);

{$IF DEFINED(CUSTOM_CTRL_Rootvin) or DEFINED(CUSTOM_CTRL_GatemaSD)}
// test zda polozky pochazi z SD serveru
   if (idPZ>0) or (sdServer) then
    begin
     try
       lQry.FetchOptions.Mode:= fmAll;
       lQry.Connection:= sqlConnX;
// typ 510=prijemka (potvrzeni/kontrola)
       lSQL2:= 'SELECT 1 AS A FROM ' + tblGSDScanData + ' p INNER JOIN ' + tblGSDDoklady + ' h ON (h.Id=p.IDDokladSD) WHERE h.DatGenerovani IS NOT NULL AND h.TypDokladu IN (500,510) AND p.IDPohZbo_New=' + idPZ.ToString;
       lSQL2:= lSQL2 + ' ORDER BY p.ID';
       lQry.Open (lSQL2);
       if (lQry.RecordCount>0) then
        lSQL:= lSQL2.Replace(' 1 AS A ', ' 1000000000+p.ID AS ID, p.IDPohZbo_New AS IDPolozkaDokladu, 0 AS IDVyrCis, p.VyrCislo AS VyrCis, p.PopisSarze AS Nazev, p.Mnozstvi'
                                       + ', p.MnozstviEvidence AS MnozstviEvid, p.DatPorizeni AS DatVstup, p.DatumExpirace AS DatExpirace, 1 AS ZdrojSD, h.ID AS IDDokladGSD ');
      finally
     end;

    end;
{$ENDIF}

   try
     sqlConnX.Connected:= true;
     lQry.FetchOptions.Mode := fmAll;
     lQry.Connection := sqlConnX;
     lQry.Open (lSQL);
     result := lQry.AsObjectList<TSerioveCisloPohybOZ>;
     lQry.Close;
    finally
     lQry.Free;
   end;

   if (lokalniConnection) then
    begin
     sqlConnX.Close;
     sqlConnX.Free;
    end;
  end;

end.