unit uSvc_Zamestnanci;

interface
{$I 'GlobalDefs.inc'}

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

const
  selZamest = 'ID, Cislo, Prijmeni, Jmeno, TitulPred, TitulZa, Stredisko, NakladovyOkruh, Zakazka';
  selZamestSpec = '(SELECT _DataZone_KodCipu FROM TabCisZam_EXT WHERE ID=main.ID) AS KodCipu';

type

  TZamestnanciService = class(TServiceBase)
   public
    function GetAll: TObjectList<TZamestnanec>;
    function GetByID (const AID: Integer): TZamestnanec; virtual;
    function GetByParams (params: TDictionary<string, string>): TObjectList<TZamestnanec>; virtual;
    function GetByOsCislo (const AOsCislo: Integer): TZamestnanec; virtual;
    function GetMeta: TJSONObject; virtual;
  end;





implementation

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



{ TZamestnanciService }

 function TZamestnanciService.GetAll: TObjectList<TZamestnanec>;
  var lSQL: string;
      lQry: TFDQuery;
      e: boolean;
  begin
   e:= false;
   if (SQLTableExists ('dbo', tblCZamE)) then
    if (SQLColumnExists('dbo', tblCZamE, '_DataZone_KodCipu')) then
     e:= true;

   lSQL:= 'SELECT ' + selZamest;
   if (e) then
    lSQL:= lSQL + IfThen(selZamestSpec<>'', ', ' + selZamestSpec, '');
   lSQL:= lSQL + ' FROM ' + tblCZam + ' ORDER BY ID';

   lQry:= TFDQuery.Create(nil);
   lQry.Connection:= FDM.sqlConn;
   lQry.Open(lSQL);
   try
     result:= lQry.AsObjectList<TZamestnanec>;
    finally
     lQry.Free;
   end;
  end;




 function TZamestnanciService.GetByParams (params: TDictionary<string, string>): TObjectList<TZamestnanec>;
  var lSQL, where: string;
      sqlConnX: TFDConnection;
      lQry: TFDQuery;
      e, e1: boolean;
  begin
   result:= nil;

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


   e1:= false;
   e:= SQLTableExists('dbo', tblCisZamE);
   if (e) then
    e1:= SQLColumnExists(tblCisZamE, '_DataZone_KodCipu');

   lSQL:= 'SELECT ' + GetTabCols('', tblCZam, 'main') + IfThen(selZamestSpec<>'', ', ' + selZamestSpec, '') + ' FROM ' + tblCZam + ' main';

   where:= '';

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

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

   if (params.ContainsKey('prijm')) then
    if (params.Items['prijm']<>'') then
     where:= where + IfThen(where<>'', ' AND ', '') + 'Prijmeni LIKE N' + (params.Items['prijm'] + '%').QuotedString;

   if (e) and (e1) and (params.ContainsKey('kodCipu')) then
    if (params.Items['kodCipu']<>'') then
     where:= where + IfThen(where<>'', ' AND ', '') + 'EXISTS(SELECT 1 FROM ' + tblCZamE + ' WHERE ID=main.ID AND _DataZone_KodCipu=N' + (params.Items['kodCipu']).QuotedString + ')';

   if (params.ContainsKey('veStavu')) then
    if (params.Items['veStavu']='1') then
     where:= where + IfThen(where<>'', ' AND ', '') + 'Id IN (SELECT mk.ZamestnanecID FROM ' + tblZamMzd + ' mk INNER JOIN ' + tblMzdObd + ' o ON (o.ID=mk.IdObdobi)'
                                                           + ' WHERE mk.StavES=0 AND o.Rok=DATEPART(year, GETDATE()) AND o.Mesic=DATEPART(month, GETDATE()))';

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


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

   lQry.Free;
   sqlConnX.Free;

  end;



 function TZamestnanciService.GetByID (const AID: Integer): TZamestnanec;
  var lSQL: string;
      extInfoStr: string;
      sqlConnX: TFDConnection;
      lQry: TFDQuery;
  begin
   result:= nil;

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

   extInfoStr:= '';
   if (SQLTableExists ('dbo', tblCZamE)) then
    begin
     lSQL:= 'SELECT ' + GetTabCols ('dbo', tblCZamE, '', true, 'ID') + ' FROM ' + tblCZamE + ' WHERE ID=:ID';

     lQry:= TFDQuery.Create(nil);
     try
       lQry.Connection:= sqlConnX;
       lQry.Open(lSQL, [AID]);
       if (lQry.RecordCount>0) then
        extInfoStr:= lQry.AsJSONObject;
      finally
       lQry.Free;
     end;
    end;


   lQry:= TFDQuery.Create(nil);
   try
     lQry.Connection:= sqlConnX;
     lQry.Open(lSQL, [AID]);
     if not(lQry.EOF) then
       begin
        result:= lQry.AsObject<TZamestnanec>;
        if (extInfoStr<>'') then
         result.ExtInfo:= ExtInfoStr;
       end
      else
       raise EServiceException.Create ('Zaměstnanec s ID ' + AID.ToString + ' nebyl nalezen.');
    finally
   end;

   lQry.Free;
   sqlConnX.Free;

  end;




 function TZamestnanciService.GetByOsCislo (const AOsCislo: Integer): TZamestnanec;
  var lSQL: string;
      lQry: TFDQuery;
      e: boolean;
  begin
   result:= nil;

   e:= false;
   if (SQLTableExists ('dbo', tblCZamE)) then
    if (SQLColumnExists('dbo', tblCZamE, '_DataZone_KodCipu')) then
     e:= true;

   lSQL:= 'SELECT ' + selZamest;
   if (e) then
    lSQL:= lSQL + IfThen(selZamestSpec<>'', ', ' + selZamestSpec, '');
   lSQL:= lSQL + ' FROM ' + tblCZam + ' WHERE Cislo=:OsCislo';

   lQry:= TFDQuery.Create(nil);
   lQry.Connection:= FDM.sqlConn;
   lQry.Open(lSQL, [AOsCislo]);
   try
     if not(lQry.EOF) then
       result:= lQry.AsObject<TZamestnanec>
     else
       raise EServiceException.Create ('Zaměstnanec s os.č. ' + AOsCislo.ToString + ' nebyl nalezen.');
    finally
     lQry.Free;
   end;
  end;




 function TZamestnanciService.GetMeta: TJSONObject;
  var lSQL: string;
      lQry: TFDQuery;
      e: boolean;
  begin
   e:= false;
   if (SQLTableExists ('dbo', tblCZamE)) then
    if (SQLColumnExists('dbo', tblCZamE, '_DataZone_KodCipu')) then
     e:= true;

   lSQL:= 'SELECT ' + selZamest;
   if (e) then
    lSQL:= lSQL + IfThen(selZamestSpec<>'', ', ' + selZamestSpec, '');
   lSQL:= lSQL + ' FROM ' + tblCZam + ' WHERE 1=0';

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


end.