884 lines
24 KiB
ObjectPascal
884 lines
24 KiB
ObjectPascal
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.
|
|
|