unit uSvc_Obecne;

interface
{$I 'GlobalDefs.inc'}

uses
  System.Generics.Collections,
  System.SysUtils,
  JsonDataObjects,
  uCommons,
  uDataMod,
  uHeoObj_Base,
  helTabsBIDs,
  uSvc_Base,
  MVCFramework.Logger
  ;


const
{$I globalConsts.inc}


type

  TDokumentService = class(TServiceBase)
   public
    function GetByID (idDok: integer; jenHlavicka: boolean=true): TDokument; virtual;
    function GetByParams (params: TDictionary<string, string>): TObjectList<TDokument>; virtual;
    function GetMeta: TJSONObject; virtual;
    function GetDokumStrom: TObjectList<TDokumentStrom>; virtual;
  end;


  TPlanKalendarService = class(TServiceBase)
   public
    function GetByID (idPlanKal: integer=0; polozky: boolean=false; p: TDictionary<string, string>=nil): TPlanKalendar; virtual;
    function GetMeta: TJSONObject; virtual;
    function GetByParams (params: TDictionary<string, string>): TObjectList<TPlanKalendar>; virtual;
  end;

  TPolPlanKalendarService = class(TServiceBase)
   public
    function GetByID (idPolPlanKal: integer): TPolozkaPlanKal; virtual;
    function GetMeta: TJSONObject; virtual;
    function GetByParams (params: TDictionary<string, string>): TObjectList<TPolozkaPlanKal>; virtual;
  end;

  TAktivitaService = class(TServiceBase)
   public
    function GetMeta: TJSONObject; virtual;
    function GetByParams (params: TDictionary<string, string>): TObjectList<TAktivita>; virtual;
  end;

  TUkolAktivityService = class(TServiceBase)
   public
    function GetMeta: TJSONObject; virtual;
    function GetByParams (params: TDictionary<string, string>): TObjectList<TUkolAktivity>; virtual;
  end;


implementation

uses
  System.DateUtils,
  System.StrUtils,
  System.RegularExpressions,
  FireDAC.Stan.Option,
  FireDAC.Comp.Client,
  FireDAC.Stan.Param,
  MVCFramework.FireDAC.Utils,
  MVCFramework.DataSet.Utils,
  MVCFramework.Serializer.Commons,
  FireDAC.Stan.Consts
//,  helTabsBIDs
  ;



{ TDokumentService }

 function TDokumentService.GetByID (idDok: integer; jenHlavicka: boolean=true): TDokument;
  var lSQL: string;
      lQry: TFDQuery;
      sqlConnX: TFDConnection;
  begin
   result:= nil;

   lSQL:= 'SELECT TOP(1) d.ID, d.Popis, d.JmenoACesta, CONVERT(bit, CASE WHEN d.Dokument IS NULL THEN 0 ELSE 1 END) AS UlozenoDB, d.VelikostVDB, d.DatPorizeni, d.DatZmeny, d.Autor'
        + ', d.Zmenil, d.SledovatHistorii, d.Dokument FROM '+ tblDokum + ' d WHERE d.ID=' + idDok.ToString;
   if (jenHlavicka) then
    lSQL:= lSQL.Replace(', d.Dokument', '');

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

   lQry:= TFDQuery.Create(nil);
   try
     lQry.Connection:= sqlConnX;
     lQry.Open(lSQL);
     result:= lQry.AsObject<TDokument>;
    finally
     lQry.Free;
   end;

   sqlConnX.Close;
   sqlConnX.Free;
  end;




 function TDokumentService.GetByParams (params: TDictionary<string, string>): TObjectList<TDokument>;
  var lSQL, where: string;
      iId, v_ident, v_idtab: integer;
      lQry: TFDQuery;
      sqlConnX: TFDConnection;
      toBase64: boolean;
  begin
   result:= nil;

   toBase64:= false;


   lSQL:= 'SELECT d.ID, d.Popis, d.JmenoACesta, CONVERT(bit, CASE WHEN d.Dokument IS NULL THEN 0 ELSE 1 END) AS UlozenoDB, d.VelikostVDB, d.DatPorizeni, d.DatZmeny, d.Autor'
        + ', d.Zmenil, d.SledovatHistorii, d.Dokument AS Dokument, d.IDDokumStrom, d.IDDokTyp FROM ' + tblDokum + ' d';
   if (FDM.SQLTableExists(tblDokumE)) then
    lSQL:= lSQL + ' LEFT JOIN ' + tblDokumE + ' dex ON (dex.ID=d.ID)';


   where:= '';

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

   if (params.ContainsKey('pripona')) then
    if (params.Items['pripona']<>'') then
     where:= where + IfThen(where<>'', ' AND ', '') + 'RIGHT(d.JmenoACesta, ' + params.Items['pripona'].Length.ToString + ')=N' + params.Items['pripona'].QuotedString;

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

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


   if (params.ContainsKey('vazba_ident')) and (params.ContainsKey('vazba_idtab')) then
    begin
     v_ident:= 0;
     if not(TryStrToInt(params.Items['vazba_ident'], v_ident)) then
      v_ident:= 0;

     v_idtab:= 0;
     if not(TryStrToInt(params.Items['vazba_idtab'], v_idtab)) then
      v_idtab:= 0;

     if (v_ident>0) and (v_idtab>iId) then
      begin
       where:= where + IfThen(where<>'', ' AND ', '') + 'd.ID IN (SELECT IdDok FROM ' + tblDokumVaz + ' WHERE IdentVazby=' + v_ident.ToString + ' AND IdTab=' + v_idtab.ToString + ')';
      end;
    end;

   if (params.ContainsKey('prednastaveny')) then
    if (params.Items['prednastaveny']='1') then
     if (FDM.SQLTableExists(tblDokumE)) then
      if (FDM.SQLColumnExists(tblDokumE, '_DokladProAPI')) then
       where:= where + IfThen(where<>'', ' AND ', '') + 'dex._DokladProAPI=1';



   if (where<>'') then
    lSQL:= lSQL + ' WHERE ' + where;
   lSQL:= lSQL + ' ORDER BY d.ID';


   if (params.ContainsKey('jenHlavicka')) then
    if (params.Items['jenHlavicka']='1') then
     lSQL:= lSQL.Replace(', d.Dokument AS Dokument', '');

   if (params.ContainsKey('base64')) then
    if (params.Items['base64']='1') then
     lSQL:= lSQL.Replace('d.Dokument A', 'dbo.ef_EncodeBase64(d.Dokument) A');


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

   lQry:= TFDQuery.Create(nil);
   try
     lQry.Connection:= sqlConnX;
     try
       lQry.Open(lSQL);
       result:= lQry.AsObjectList<TDokument>;
      except on E:Exception do
       begin
        Log.Error ('Chyba načítání dokumentů: ' + E.Message + CRLF + lSQL, 'SVCObecne_DokumentService');
        raise EServiceException.Create('Chyba načítání dokumentů: ' + E.Message);
       end;
     end;
    finally
     lQry.Free;
   end;

   sqlConnX.Close;
   sqlConnX.Free;

  end;




 function TDokumentService.GetMeta: TJSONObject;
  var lSQL: string;
      lQry: TFDQuery;
      sqlConnX: TFDConnection;
  begin
   lSQL:= 'SELECT ID, Popis, JmenoACesta, CONVERT(bit, CASE WHEN Dokument IS NULL THEN 0 ELSE 1 END) AS UlozenoDB, VelikostVDB, DatPorizeni, DatZmeny, Autor'
        + ', Zmenil, SledovatHistorii, Dokument , IDDokumStrom FROM ' + tblDokum + ' WHERE 1=0';

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

   lQry:= TFDQuery.Create(nil);
   try
     lQry.Connection:= sqlConnX;
     try
       lQry.Open(lSQL);
       result:= lQry.MetadataAsJSONObject();
      except on E:Exception do
       Log.Error ('Chyba načítání metainformace dokumentů: ' + E.Message + CRLF + lSQL, 'SVCObecne_DokumentService');
     end;
   finally
    lQry.Free;
   end;

   sqlConnX.Close;
   sqlConnX.Free;
  end;




 function TDokumentService.GetDokumStrom: TObjectList<TDokumentStrom>;
  var lSQL: string;
      lQry: TFDQuery;
      sqlConnX: TFDConnection;
  begin
   result:= nil;

   lSQL:= 'SELECT ' + GetTabCols ('dbo', tblDokumStrom, '', false, '') + ' FROM ' + tblDokumStrom + ' ORDER BY ID';

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

   lQry:= TFDQuery.Create(nil);
   try
     lQry.Connection:= sqlConnX;
     try
       lQry.Open(lSQL);
       result:= lQry.AsObjectList<TDokumentStrom>;
      except on E:Exception do
       Log.Error ('Chyba načítání stromu dokumentů: ' + E.Message + CRLF + lSQL, 'SVCObecne_DokumentService');
     end;
    finally
     lQry.Free;
   end;

   sqlConnX.Close;
   sqlConnX.Free;
  end;





{ TPlanKalendarService }
 function TPolPlanKalendarService.GetByID (idPolPlanKal: integer): TPolozkaPlanKal;
  var lSQL: string;
      lQry: TFDQuery;
      polSrv: TPolPlanKalendarService;
      sqlConnX: TFDConnection;
  begin
   result:= nil;

   lSQL:= 'SELECT TOP(1) * FROM '+ tblPlanKalPol + ' WHERE ID=' + idPolPlanKal.ToString;

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

   lQry:= TFDQuery.Create(nil);
   try
     lQry.Connection:= sqlConnX;
     lQry.Open(lSQL);
     result:= lQry.AsObject<TPolozkaPlanKal>;
    finally
     lQry.Free;
   end;

   sqlConnX.Close;
   sqlConnX.Free;
  end;



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



 function TPolPlanKalendarService.GetByParams (params: TDictionary<string, string>): TObjectList<TPolozkaPlanKal>;
  var lSQL, where, odDatum, doDatum: string;
      oznac, idStroj, idKooperace, idVyrPerZdroj: string;
      lQry: TFDQuery;
      sqlConnX: TFDConnection;
      datOd, datDo: TDateTime;
      AID: integer;
      extInfoStr: string;
  begin
   result:= nil;

   where:= '';

   AID:= 0;
   if (params.ContainsKey('idkalendar')) then
    if (params.Items['idkalendar']<>'') then
     begin
      if not(TryStrToInt(params.Items['idkalendar'], AID)) then
       AID:= 0;
      where:= where + 'IDPlanKalend=' + AID.ToString;
     end;


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



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

 {
   if (params.ContainsKey('oznaceni')) then
    if (params.Items['oznaceni']<>'') then
     begin
      oznac:= params.Items['oznaceni'];
      where:= where + IfThen(where<>'', ' AND ', '') + 'IDPlanKalend=(SELECT ID FROM ' + tblPlanKal + ' WHERE Oznaceni=N' + oznac.QuotedString + ')';
     end;

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

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

   if (params.ContainsKey('idvyrperzdroj')) then
    if (params.Items['idvyrperzdroj']<>'') then
     begin
      idVyrPerZdroj:= params.Items['idvyrperzdroj'];
      where:= where + IfThen(where<>'', ' AND ', '') + 'IDVyrPerZdroje=' + idVyrPerZdroj + ')';
     end;
}



   odDatum:= '';
   if (params.ContainsKey('oddatum')) then
    if (params.Items['oddatum']<>'') then
     odDatum:= params.Items['oddatum'].Trim;
   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);

   doDatum:= '';
   if (params.ContainsKey('dodatum')) then
    if (params.Items['dodatum']<>'') then
     doDatum:= params.Items['dodatum'].Trim;
   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:= IncDay(Now, 365);
     end
    else
     datDo:= IncDay(Now, 365);

   where:= where + IfThen(where<>'', ' AND ', '') + 'Datum>=';
   if (datOd<>0) then
     where:= where + 'CONVERT(datetime, N' + FormatDateTime('dd.mm.yyyy hh:nn:ss', datOd).QuotedString + ',104)'
    else
     where:= where + 'DATEADD(day, -1, GETDATE())';
   if (datDo<>0) then
    where:= where + IfThen(where<>'', ' AND ', '') + 'Datum<=CONVERT(datetime, N' + FormatDateTime('dd.mm.yyyy hh:nn:ss', datDo).QuotedString + ',104)';




   if (where<>'') then
    lSQL:= lSQL + ' WHERE ' + where;
   lSQL:= lSQL + ' ORDER BY Datum';

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

   lQry:= TFDQuery.Create(nil);
   try
     lQry.Connection:= sqlConnX;
     try
       lQry.Open(lSQL);
       result:= lQry.AsObjectList<TPolozkaPlanKal>;
      except on E:Exception do
       raise EServiceException.Create('Chyba načítání položek plánovacího kalendáře: ' + E.Message);
     end;
    finally
     lQry.Free;
   end;

   sqlConnX.Close;
   sqlConnX.Free;
  end;




{ TPlanKalendarService }

 function TPlanKalendarService.GetByID (idPlanKal: integer=0; polozky: boolean=false; p: TDictionary<string, string>=nil): TPlanKalendar;
  var lSQL: string;
      lQry: TFDQuery;
      sqlConnX: TFDConnection;
      par: TDictionary<string, string>;
      iIdStroj, iIdKooperace: integer;
      polSrv: TPolPlanKalendarService;
      lPol: TObjectList<TPolozkaPlanKal>;
      extInfoStr: string;
  begin
   result:= nil;

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



//   lSQL:= 'SELECT TOP(1) ' + GetTabCols('', tblPlanKal, '', false, '') + ' FROM '+ tblPlanKal + ' WHERE ID=' + idPlanKal.ToString;
   lSQL:= 'SELECT TOP(1) * FROM '+ tblPlanKal + ' WHERE ID=' + idPlanKal.ToString;

   par:= TDictionary<string, string>.Create;
   par.Add('idkalendar', idPlanKal.ToString);

   if (polozky) then
    begin
     if (p<>nil) then
      begin
       if (p.ContainsKey('oddatum')) then
        if (p.Items['oddatum']<>'') then
         par.Add('oddatum', p.Items['oddatum']);
       if (p.ContainsKey('dodatum')) then
        if (p.Items['dodatum']<>'') then
         par.Add('dodatum', p.Items['dodatum']);
      end;
     polSrv:= TPolPlanKalendarService.Create(self.FDM);
    end;

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

   lQry:= TFDQuery.Create(nil);
   try
     lQry.Connection:= sqlConnX;
     lQry.Open(lSQL);
     result:= lQry.AsObject<TPlanKalendar>;

     if (extInfoStr='') then
      result.ExtInfo:= nil;

     if (polozky) then
      begin
       lPol:= polSrv.GetByParams (par);
       result.Polozky:= lPol;
      end;
    finally
     lQry.Free;
     par.Free; // parametry
     if (polozky) then
      begin
       polSrv.Free; // service pro polozky plan. kalendare
      end;
   end;

   sqlConnX.Close;
   sqlConnX.Free;
  end;


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



 function TPlanKalendarService.GetByParams (params: TDictionary<string, string>): TObjectList<TPlanKalendar>;
  var lSQL, where: string;
      cnt, rNo, iID, iIDStroj, iIdKooperace: integer;
      lQry: TFDQuery;
      sqlConnX: TFDConnection;
      lPlKals: TObjectList<TPlanKalendar>;
      plKal: TPlanKalendar;
      polozky: boolean;
  begin
   result:= nil;

   lSQL:= 'SELECT * FROM ' + tblPlanKal;
   where:= '';

   iId:= 0;
   if (params.ContainsKey('id')) then
    if (params.Items['id']<>'') then
     if not(TryStrToInt(params.Items['id'], iId)) then
      iId:= 0;
   if (iId<>0) then
    where:= 'ID=' + iId.ToString;

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

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

   iIDStroj:= 0;
   if (params.ContainsKey('idstroj')) then
    if (params.Items['idstroj'].Trim<>'') then
     if not(TryStrToInt(params.Items['idstroj'].Trim, iIDStroj)) then
      iIDStroj:= 0;
   if (iIDStroj<>0) then
    where:= where + IfThen(where<>'', ' AND ', '') + 'EXISTS (SELECT 1 FROM ' + tblPlanKalPol + ' WHERE IDStroje=' + iIDStroj.ToString + ')';

   iIdKooperace:= 0;
   if (params.ContainsKey('idkooperace')) then
    if (params.Items['idkooperace'].Trim<>'') then
     if not(TryStrToInt(params.Items['idkooperace'].Trim, iIdKooperace)) then
      iIdKooperace:= 0;
   if (iIdKooperace<>0) then
    where:= where + IfThen(where<>'', ' AND ', '') + 'EXISTS (SELECT 1 FROM ' + tblPlanKalPol + ' WHERE IDKoop=' + iIdKooperace.ToString + ')';


   if (where<>'') then
    lSQL:= lSQL + ' WHERE ' + where;


   lPlKals:= TObjectList<TPlanKalendar>.Create;

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

   lQry:= TFDQuery.Create(nil);
   try
     lQry.Connection:= sqlConnX;
     lQry.Open(lSQL);
     try
      cnt:= lQry.RecordCount;
      if (cnt>0) then
       begin
        lQry.First;
        rNo:= 1;
        while (rNo<=cnt) do
         begin
          lQry.RecNo:= rNo;
          plKal:= self.GetByID (lQry.FieldByName('ID').AsInteger, polozky);
          lPlKals.Add(plKal);

          if not(lQry.Active) then
           lQry.Open;
          Inc(rNo);
         end;
       end;
      except on E:Exception do
       raise EServiceException.Create('Chyba načítání plánovacích kalendářů: ' + E.Message);
     end;
    finally
     lQry.Free;
   end;

   sqlConnX.Close;
   sqlConnX.Free;
  end;







{ TUkolAktivityService }
 function TUkolAktivityService.GetMeta: TJSONObject;
  var lSQL: string;
      lQry: TFDQuery;
  begin
   lSQL:= 'SELECT ' + GetTabCols ('dbo', tblUkoly, '', false, strBlokEdit) + ' FROM ' + tblUkoly + ' WHERE 1=0';
   lQry:= TFDQuery.Create(nil);
   try
     lQry.Connection:= FDM.sqlConn;
     try
       lQry.Open(lSQL);
       result:= lQry.MetadataAsJSONObject();
      except on E:Exception do
       raise EServiceException.Create('Chyba načítání meta údajů úkolu: ' + E.Message);
     end;
    finally
     lQry.Free;
   end;
  end;




 function TUkolAktivityService.GetByParams (params: TDictionary<string, string>): TObjectList<TUkolAktivity>;
  var lSQL, where: string;
      lQry: TFDQuery;
      sqlConnX: TFDConnection;
      iID: integer;
  begin
   result:= nil;

   lSQL:= 'SELECT ' + GetTabCols ('dbo', tblUkoly, '', false, strBlokEdit) + ' FROM ' + tblUkoly;
   where:= '';

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

   if (where<>'') then
    lSQL:= lSQL + ' WHERE ' + where;


   lSQL:= lSQL + ' ORDER BY ISNULL(TerminZahajeni, ISNULL(DatumZahajeni, DatPorizeni))';

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

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

   sqlConnX.Close;
   sqlConnX.Free;

  end;







{ TAktivitaService }
 function TAktivitaService.GetMeta: TJSONObject;
  var lSQL: string;
      lQry: TFDQuery;
  begin
   lSQL:= 'SELECT ' + GetTabCols ('dbo', tblPlanKal, '', false, strBlokEdit) + ' FROM ' + tblPlanKal + ' WHERE 1=0';
   lQry:= TFDQuery.Create(nil);
   try
     lQry.Connection:= FDM.sqlConn;
     try
       lQry.Open(lSQL);
       result:= lQry.MetadataAsJSONObject();
      except on E:Exception do
       raise EServiceException.Create('Chyba načítání meta údajů aktivity: ' + E.Message);
     end;
   finally
    lQry.Free;
   end;
  end;





 function TAktivitaService.GetByParams (params: TDictionary<string, string>): TObjectList<TAktivita>;
  var lSQL, s, datOdS, datDoS, strTemp, where: string;
      datOd, datDo: TDateTime;
      noDatum: boolean;
      lQry: TFDQuery;
      sqlConnX: TFDConnection;
      iID: integer;
  begin
   result:= nil;

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

   where:= '';

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

   if (params.ContainsKey('kategorie')) then
    begin
     s:= params.Items['kategorie'].Trim;
     if (s<>'') then
      if (datMod.SQLRecordExists('SELECT  1 FROM ' + tblKategKJ + ' WHERE Cislo=N' + s.QuotedString)) then
       where:= where + IfThen(where<>'', ' AND ', '') + 'Kategorie=N' + s.QuotedString;
    end;



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

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


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

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

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

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



   if (where<>'') then
    lSQL:= lSQL + ' WHERE ' + where;
   lSQL:= lSQL + ' ORDER BY ISNULL(DatumJednaniOd, DatPorizeni)';

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

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

   sqlConnX.Close;
   sqlConnX.Free;
  end;


end.