258 lines
6.5 KiB
ObjectPascal
258 lines
6.5 KiB
ObjectPascal
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<6E><61>t<EFBFBD>n<EFBFBD> zam<61>stnanc<6E>: ' + 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<61>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<61>stnanec s os.<2E>. ' + 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.
|