463 lines
12 KiB
ObjectPascal
463 lines
12 KiB
ObjectPascal
unit uSvc_KmenZbozi;
|
|
|
|
interface
|
|
{$I 'GlobalDefs.inc'}
|
|
|
|
uses
|
|
System.Generics.Collections,
|
|
System.SysUtils,
|
|
FireDAC.Stan.Option,
|
|
FireDAC.Comp.Client,
|
|
FireDAC.Stan.Param,
|
|
JsonDataObjects,
|
|
uSvc_Base,
|
|
uCommons,
|
|
uHeoObj_Base;
|
|
|
|
const
|
|
{$I globalConsts.inc}
|
|
|
|
sqlSelKmen = 'ID, SkupZbo, RegCis, Nazev1, Nazev2, Nazev3, Nazev4, MJEvidence, Dilec, Montaz, Naradi, Material, Sluzba, SKP, PLUKod'
|
|
+ ', KontrolaVyrC, Poznamka, Blokovano, Hmotnost, Vyska, Sirka, Hloubka, Vykres, KmenoveStredisko, CelniNomenklatura, IdSortiment'
|
|
+ ', IdVarianta, VychoziMnozstvi, BlokovaniEditoru';
|
|
|
|
|
|
selKmenSpec = '';
|
|
|
|
type
|
|
TSkupinaZboziService = class(TServiceBase)
|
|
public
|
|
function GetAll: TObjectList<TSkupinaKaret>; virtual;
|
|
function GetByID (const AID: Integer; params: TDictionary<string, string>=nil): TSkupinaKaret; virtual;
|
|
function GetByParams (params: TDictionary<string, string>): TObjectList<TSkupinaKaret>; virtual;
|
|
function GetMeta: TJSONObject; virtual;
|
|
end;
|
|
|
|
|
|
|
|
|
|
TKmenZboziService = class(TServiceBase)
|
|
public
|
|
function GetAll: TObjectList<TKmenZbozi>; virtual;
|
|
function GetByID (const AID: Integer; params: TDictionary<string, string>=nil; AConn: TFDConnection = nil): TKmenZbozi; virtual;
|
|
function GetByParams (params: TDictionary<string, string>): TObjectList<TKmenZbozi>; virtual;
|
|
function GetMeta: TJSONObject; virtual;
|
|
function GetPrepocetMJ (idKmen: integer): TObjectList<TPrepocetMJ>; virtual;
|
|
end;
|
|
|
|
|
|
|
|
implementation
|
|
|
|
uses
|
|
System.StrUtils,
|
|
System.Variants,
|
|
Quick.Logger,
|
|
MVCFramework.Commons,
|
|
MVCFramework.FireDAC.Utils,
|
|
MVCFramework.DataSet.Utils,
|
|
MVCFramework.Serializer.Commons,
|
|
helTabsBIDs;
|
|
|
|
|
|
|
|
|
|
|
|
{ TSkupinaKaretService }
|
|
|
|
function TSkupinaZboziService.GetMeta: TJsonObject;
|
|
var lSQL: string;
|
|
lQry: TFDQuery;
|
|
sqlConnX: TFDConnection;
|
|
begin
|
|
sqlConnX:= TFDConnection.Create (nil);
|
|
sqlConnX.ConnectionDefName:= sqlPoolName;
|
|
|
|
lSQL:= 'SELECT ' + GetTabCols('dbo', tblSZ) + ' FROM ' + tblSZ + ' WHERE 1=0';
|
|
lQry:= TFDQuery.Create (nil);
|
|
try
|
|
lQry.FetchOptions.Mode:= fmAll;
|
|
lQry.Connection:= sqlConnX;
|
|
lQry.Open(lSQL);
|
|
result:= lQry.MetadataAsJSONObject();
|
|
finally
|
|
end;
|
|
lQry.Free;
|
|
sqlConnX.Close;
|
|
sqlConnX.Free;
|
|
end;
|
|
|
|
|
|
|
|
|
|
function TSkupinaZboziService.GetByID (const AID: Integer; params: TDictionary<string, string>=nil): TSkupinaKaret;
|
|
var lSQL: string;
|
|
lQry: TFDQuery;
|
|
sqlConnX: TFDConnection;
|
|
begin
|
|
result:= nil;
|
|
|
|
sqlConnX:= TFDConnection.Create (nil);
|
|
sqlConnX.ConnectionDefName:= sqlPoolName;
|
|
|
|
lSQL:= 'SELECT ' + GetTabCols('dbo', tblSZ) + ' FROM ' + tblSZ + ' WHERE ID=:ID';
|
|
lQry:= TFDQuery.Create (nil);
|
|
try
|
|
lQry.FetchOptions.Mode:= fmAll;
|
|
lQry.Connection:= sqlConnX;
|
|
lQry.Open (lSQL, [AID]);
|
|
result:= lQry.AsObject<TSkupinaKaret>;
|
|
finally
|
|
end;
|
|
lQry.Free;
|
|
sqlConnX.Close;
|
|
sqlConnX.Free;
|
|
end;
|
|
|
|
|
|
|
|
|
|
function TSkupinaZboziService.GetAll: TObjectList<TSkupinaKaret>;
|
|
var lSQL: string;
|
|
lQry: TFDQuery;
|
|
sqlConnX: TFDConnection;
|
|
begin
|
|
result:= nil;
|
|
|
|
sqlConnX:= TFDConnection.Create (nil);
|
|
sqlConnX.ConnectionDefName:= sqlPoolName;
|
|
|
|
lSQL:= 'SELECT ' + GetTabCols('dbo', tblSZ) + ' FROM ' + tblSZ + ' ORDER BY SkupZbo';
|
|
lQry:= TFDQuery.Create (nil);
|
|
try
|
|
lQry.FetchOptions.Mode:= fmAll;
|
|
lQry.Connection:= sqlConnX;
|
|
lQry.Open(lSQL);
|
|
result:= lQry.AsObjectList<TSkupinaKaret>;
|
|
finally
|
|
end;
|
|
lQry.Free;
|
|
sqlConnX.Close;
|
|
sqlConnX.Free;
|
|
end;
|
|
|
|
|
|
|
|
|
|
function TSkupinaZboziService.GetByParams (params: TDictionary<string, string>): TObjectList<TSkupinaKaret>;
|
|
var lSQL, where: string;
|
|
aoV: TArray<Variant>;
|
|
lQry: TFDQuery;
|
|
sqlConnX: TFDConnection;
|
|
begin
|
|
result:= nil;
|
|
|
|
lSQL:= 'SELECT ' + GetTabCols('dbo', tblSZ) + ' FROM ' + tblSZ;
|
|
|
|
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('sz')) then
|
|
if (params.Items['sz']<>'') then
|
|
where:= where + IfThen(where<>'', ' AND ', '') + 'SkupZbo LIKE N' + (params.Items['sz'] + '%').QuotedString;
|
|
|
|
if (params.ContainsKey('nazev')) then
|
|
if (params.Items['nazev']<>'') then
|
|
where:= where + IfThen(where<>'', ' AND ', '') + 'Nazev LIKE N' + ('%' + params.Items['nazev'] + '%').QuotedString;
|
|
|
|
|
|
if (where<>'') then
|
|
lSQL:= lSQL + ' WHERE ' + where;
|
|
lSQL:= lSQL + ' ORDER BY SkupZbo';
|
|
|
|
|
|
|
|
sqlConnX:= TFDConnection.Create (nil);
|
|
sqlConnX.ConnectionDefName:= sqlPoolName;
|
|
|
|
lQry:= TFDQuery.Create (nil);
|
|
try
|
|
lQry.FetchOptions.Mode:= fmAll;
|
|
lQry.Connection:= sqlConnX;
|
|
lQry.Open(lSQL);
|
|
result:= lQry.AsObjectList<TSkupinaKaret>;
|
|
finally
|
|
end;
|
|
|
|
lQry.Free;
|
|
sqlConnX.Close;
|
|
sqlConnX.Free;
|
|
end;
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
{ TKmenZboziService }
|
|
|
|
function TKmenZboziService.GetAll: TObjectList<TKmenZbozi>;
|
|
var lSQL: string;
|
|
lQry: TFDQuery;
|
|
sqlConnX: TFDConnection;
|
|
begin
|
|
result:= nil;
|
|
|
|
sqlConnX:= TFDConnection.Create (nil);
|
|
sqlConnX.ConnectionDefName:= sqlPoolName;
|
|
|
|
|
|
lSQL:= 'SELECT ' + sqlSelKmen + ' FROM ' + tblKZ + ' ORDER BY ID';
|
|
|
|
lQry:= TFDQuery.Create (nil);
|
|
try
|
|
lQry.FetchOptions.Mode:= fmAll;
|
|
lQry.Connection:= sqlConnX;
|
|
lQry.Open (lSQL);
|
|
result:= lQry.AsObjectList<TKmenZbozi>;
|
|
finally
|
|
lQry.Free;
|
|
end;
|
|
|
|
sqlConnX.Free;
|
|
end;
|
|
|
|
|
|
|
|
|
|
|
|
function TKmenZboziService.GetByID (const AID: Integer; params: TDictionary<string, string>=nil; AConn: TFDConnection = nil): TKmenZbozi;
|
|
{$IFDEF CUSTOM_CTRL_Rootvin}
|
|
const rtnASOLTrideni = '_ASOL_IdentifTrideni';
|
|
{$ENDIF}
|
|
var lSQL, extInfoStr: string;
|
|
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', tblKZe)) then
|
|
begin
|
|
lSQL:= 'SELECT ' + GetTabCols ('dbo', tblKZe, '', true, 'ID', 0, false) + ' FROM ' + tblKZe + ' WHERE ID=:ID';
|
|
lQry:= TFDQuery.Create (nil);
|
|
lQry.Connection:= sqlConnX;
|
|
try
|
|
lQry.Open (lSQL, [AID]);
|
|
if (lQry.RecordCount>0) then
|
|
begin
|
|
extInfoStr:= lQry.AsJSONObject;
|
|
{$IFDEF CUSTOM_CTRL_Rootvin}
|
|
if (SQLColumnExists('dbo', tblKZe, rtnASOLTrideni)) then
|
|
if not(lQry.FieldByName(rtnASOLTrideni).IsNull) then
|
|
begin
|
|
if (lQry.FieldByName(rtnASOLTrideni).AsString='1') then
|
|
result.TypDilce:= 'surovina';
|
|
if (lQry.FieldByName(rtnASOLTrideni).AsString='2') then
|
|
result.TypDilce:= 'polotovar';
|
|
if (lQry.FieldByName(rtnASOLTrideni).AsString='3') then
|
|
result.TypDilce:= 'obal';
|
|
end;
|
|
{$ENDIF}
|
|
end;
|
|
finally
|
|
lQry.Free;
|
|
end;
|
|
end;
|
|
|
|
|
|
lSQL:= 'SELECT ' + GetTabCols ('dbo', tblKZ, 'main') + IfThen(selKmenSpec<>'', ',' + selKmenSpec, '') + ' FROM ' + tblKZ + ' main WHERE main.ID=:ID';
|
|
lQry:= TFDQuery.Create (nil);
|
|
try
|
|
lQry.FetchOptions.Mode:= fmAll;
|
|
lQry.Connection:= sqlConnX;
|
|
try
|
|
lQry.Open (lSQL, [AID]);
|
|
result:= lQry.AsObject<TKmenZbozi>;
|
|
if (extInfoStr<>'') then
|
|
result.ExtInfo:= ExtInfoStr;
|
|
except on E:Exception do
|
|
raise EServiceException.Create ('Chyba nacitani kmenove karty: ' + E.Message);
|
|
end;
|
|
finally
|
|
lQry.Free;
|
|
end;
|
|
|
|
if (lokalniConnection) then
|
|
sqlConnX.Free;
|
|
|
|
end;
|
|
|
|
|
|
|
|
|
|
function TKmenZboziService.GetByParams (params: TDictionary<string, string>): TObjectList<TKmenZbozi>;
|
|
var lSQL, where, errMsg: string;
|
|
AID, cnt, rNo: integer;
|
|
aoV: TArray<Variant>;
|
|
resList: TObjectList<TKmenZbozi>;
|
|
resObj: TKmenZbozi;
|
|
lQry: TFDQuery;
|
|
sqlConnX: TFDConnection;
|
|
begin
|
|
result:= nil;
|
|
|
|
resList:= TObjectList<TKmenZbozi>.Create;
|
|
|
|
sqlConnX:= TFDConnection.Create(nil);
|
|
sqlConnX.ConnectionDefName:= sqlPoolName;
|
|
|
|
lSQL:= 'SELECT ID, SkupZbo, RegCis FROM ' + tblKZ;
|
|
|
|
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('sz')) then
|
|
if (params.Items['sz']<>'') then
|
|
where:= where + IfThen(where<>'', ' AND ', '') + 'SkupZbo LIKE N' + (params.Items['sz'] + '%').QuotedString;
|
|
|
|
if (params.ContainsKey('sk')) then
|
|
if (params.Items['sk']<>'') then
|
|
where:= where + IfThen(where<>'', ' AND ', '') + 'SkupZbo LIKE N' + (params.Items['sk'] + '%').QuotedString;
|
|
|
|
if (params.ContainsKey('rc')) then
|
|
if (params.Items['rc']<>'') then
|
|
where:= where + IfThen(where<>'', ' AND ', '') + 'RegCis LIKE N' + (params.Items['rc'] + '%').QuotedString;
|
|
|
|
if (params.ContainsKey('cisloZbozi')) then
|
|
if (params.Items['cisloZbozi']<>'') then
|
|
where:= where + IfThen(where<>'', ' AND ', '') + 'CisloZbozi LIKE N' + (params.Items['cisloZbozi'] + '%').QuotedString;
|
|
|
|
if (params.ContainsKey('nazev1')) then
|
|
if (params.Items['nazev1']<>'') then
|
|
where:= where + IfThen(where<>'', ' AND ', '') + 'Nazev1 LIKE N' + ('%' + params.Items['nazev1'] + '%').QuotedString;
|
|
|
|
if (params.ContainsKey('barcode')) then
|
|
if (params.Items['barcode']<>'') then
|
|
where:= where + IfThen(where<>'', ' AND ', '') + 'ID IN (SELECT IDKmenZbo FROM ' + tblBCode + ' WHERE Barcode=N' + params.Items['barcode'].QuotedString + ')';
|
|
|
|
|
|
|
|
if (where<>'') then
|
|
lSQL:= lSQL + ' WHERE ' + where;
|
|
lSQL:= lSQL + ' ORDER BY SkupZbo, RegCis';
|
|
|
|
|
|
lQry:= TFDQuery.Create(nil);
|
|
try
|
|
lQry.FetchOptions.Mode:= fmAll;
|
|
lQry.Connection:= sqlConnX;
|
|
lQry.Open (lSQL);
|
|
cnt:= lQry.RecordCount;
|
|
if (cnt>0) then
|
|
begin
|
|
try
|
|
resObj:= TKmenZbozi.Create;
|
|
resList:= TObjectList<TKmenZbozi>.Create;
|
|
try
|
|
rNo:= 1;
|
|
lQry.First;
|
|
while (rNo<=cnt) do // nouzove reseni, lQry se po nacteni GetbyID sama uzavre !!
|
|
begin
|
|
lQry.RecNo:= rNo;
|
|
AID:= lQry.FieldByName('ID').AsInteger;
|
|
resObj:= self.GetByID (AID);
|
|
resList.Add (resObj);
|
|
if not(lQry.Active) then
|
|
lQry.Open;
|
|
Inc(rNo);
|
|
end;
|
|
result:= resList;
|
|
except on E:Exception do
|
|
begin
|
|
errMsg:= E.Message;
|
|
raise EServiceException.Create ('Chyba nacitani kmenove karty: ' + errMsg);
|
|
end;
|
|
end;
|
|
// FreeAndNil(resList);
|
|
finally
|
|
end;
|
|
end
|
|
else
|
|
raise EServiceException.Create ('Vybranym podminkam neodpovida zadna kmenova karta');
|
|
finally
|
|
|
|
end;
|
|
lQry.Free;
|
|
sqlConnX.Close;
|
|
sqlConnX.Free;
|
|
|
|
end;
|
|
|
|
|
|
|
|
|
|
function TKmenZboziService.GetMeta: TJSONObject;
|
|
var lSQL: string;
|
|
lQry: TFDQuery;
|
|
sqlConnX: TFDConnection;
|
|
begin
|
|
sqlConnX:= TFDConnection.Create (nil);
|
|
sqlConnX.ConnectionDefName:= sqlPoolName;
|
|
|
|
lSQL:= 'SELECT ' + GetTabCols('dbo', tblKZ, '', false) + ' FROM ' + tblKZ + ' WHERE 1=0';
|
|
lQry:= TFDQuery.Create(nil);
|
|
try
|
|
lQry.FetchOptions.Mode:= fmAll;
|
|
lQry.Connection:= sqlConnX;
|
|
lQry.Open (lSQL);
|
|
result:= lQry.MetadataAsJSONObject();
|
|
finally
|
|
end;
|
|
lQry.Free;
|
|
sqlConnX.Close;
|
|
sqlConnX.Free;
|
|
end;
|
|
|
|
|
|
|
|
|
|
function TKmenZboziService.GetPrepocetMJ (idKmen: integer): TObjectList<TPrepocetMJ>;
|
|
var lSQL: string;
|
|
lQry: TFDQuery;
|
|
sqlConnX: TFDConnection;
|
|
begin
|
|
sqlConnX:= TFDConnection.Create (nil);
|
|
sqlConnX.ConnectionDefName:= sqlPoolName;
|
|
|
|
lSQL:= 'SELECT ID, KodMJ1, KodMJ2, PocetHlavni, PocetOdvozene, DatPorizeni FROM ' + tblMJZbo + ' WHERE IDKmenZbozi=:ID';
|
|
lQry:= TFDQuery.Create(nil);
|
|
try
|
|
lQry.FetchOptions.Mode:= fmAll;
|
|
lQry.Connection:= sqlConnX;
|
|
lQry.Open (lSQL, [idKmen]);
|
|
result:= lQry.AsObjectList<TPrepocetMJ>;
|
|
finally
|
|
end;
|
|
lQry.Free;
|
|
sqlConnX.Close;
|
|
sqlConnX.Free;
|
|
end;
|
|
|
|
|
|
end.
|