unit uSvc_Vyroba; interface {$I 'GlobalDefs.inc'} uses System.Generics.Collections, System.SysUtils, Winapi.ActiveX, FireDAC.Stan.Option, FireDAC.Comp.Client, FireDAC.Stan.Param, System.JSON, JsonDataObjects, Quick.Logger, uCommons, uSvc_Base, uSvc_ObehZbozi, helTabsBIDs, // nazvy tabulek a BID prehledu uHeOObj_Base, uHeoObj_Vyroba, uHeoObj_QMS; const {$I globalConsts.inc} { createTabAPIKusovnik = 'CREATE TABLE #apiKusovnik (ID INT IDENTITY(1,1) NOT NULL, Generuj BIT NOT NULL DEFAULT 1, PoKorekciDat BIT NOT NULL DEFAULT 0, IDPrKV INT NOT NULL' + ', Doklad INT NOT NULL, IDPrikaz INT NOT NULL, Prednastaveno BIT NOT NULL DEFAULT 1, Sklad NVARCHAR(30), VyrStredisko NVARCHAR(30)' + ', IDPracoviste INT, mnoz_zad NUMERIC(19,6) NOT NULL DEFAULT 0, Mnoz_nepotrebne NUMERIC(19,6) NOT NULL DEFAULT 0' + ', Mnoz_skut_realizovane NUMERIC(19,6) NOT NULL DEFAULT 0, Cena_real NUMERIC(19,6) NOT NULL DEFAULT 0, Mnoz_odv NUMERIC(19,6) NOT NULL DEFAULT 0' + ', Cena_odv NUMERIC(19,6) NOT NULL DEFAULT 0, MnozstviMax NUMERIC(19,6), PuvodniMnozstviPoz NUMERIC(19,6), MnozstviPoz NUMERIC(19,6) NOT NULL' + ', PomMnozPoz NUMERIC(19,6) NOT NULL DEFAULT 0, IDPohZbo INT, PoznamkaNaDoklad NTEXT, priorita INT NOT NULL DEFAULT 0, vyssi INT NOT NULL, nizsi INT NOT NULL' + ', SZVyssi NVARCHAR(3), RCVyssi NVARCHAR(30), Nazev1Vyssi NVARCHAR(100), SZNizsi NVARCHAR(3), RCNizsi NVARCHAR(30), Nazev1Nizsi NVARCHAR(100)' + ', DavkaTPV NUMERIC(19,6) NOT NULL DEFAULT 1, pozice NVARCHAR(100) DEFAULT N'''', Operace NCHAR(4), FixniMnozstvi NUMERIC(19,6) NOT NULL DEFAULT 0' + ', mnozstvi NUMERIC(19,6) NOT NULL DEFAULT 1, ProcZtrat NUMERIC(5,2) NOT NULL DEFAULT 0, mnozstviSeZtratou NUMERIC(19,6) NOT NULL DEFAULT 0' + ', Prirez NUMERIC(19,6) NOT NULL DEFAULT 1, Poznamka NTEXT, pom_pomer_A NUMERIC(19,6), pom_pomer_B NUMERIC(19,6), Dodavatel INT' + ', mnoz_pozadovane AS (CONVERT(numeric(19,6), CASE WHEN [mnoz_nepotrebne]>[mnoz_zad] THEN 0.0 ELSE [mnoz_zad] - [mnoz_nepotrebne] END))' + ')'; } createTabAPIKusovnik = 'CREATE TABLE #apiKusovnik (vyssi INT NULL, IDKmenZbozi INT NOT NULL, uroven INT NOT NULL, poradi INT NOT NULL, IDKVazby INT NULL, mnozstvi NUMERIC(19,6) NOT NULL,' + ' prirez NUMERIC(19,6) NULL, RezijniMat BIT NOT NULL, VyraditZKalkulace BIT NOT NULL)'; errMaterialy = 'Chyba ncteni materialovych pozadavku - '; errPostupy = 'Chyba nacteni technologickych postupu - '; selPrikazSpec = 'zak.CisloZakazky, kz.SkupZbo AS SZ, kz.RegCis, kz.Nazev1, kz.MJEvidence, zmod.Kod AS KodZakazModif'; selMaterialSpec = 'kzN.SkupZbo AS SZNizsi, kzN.RegCis AS RCNizsi, kzN.Nazev1 AS Nazev1Nizsi, kzN.MJEvidence AS MJNizsi, kzV.SkupZbo AS SZVyssi, kzV.RegCis AS RCVyssi' + ', kzV.Nazev1 AS Nazev1Vyssi, kzV.MJEvidence AS MJVyssi'; selCisKoopSpec = '(SELECT d.CisloOrg FROM ' + tblCOrg + ' d WHERE d.ID=main.dodavatel) AS Dodavatel, (SELECT m.CisloOrg FROM ' + tblCOrg + ' m WHERE m.ID=main.IDMistoUrceni) AS MistoUrceni'; selNazevOperaceEvidROp = '(SELECT TOP(1) Kod FROM ' + tblCisStroj + ' WHERE ID=main.IDStroje) AS KodStroje,' + '(SELECT TOP(1) Nazev FROM ' + tblCisStroj + ' WHERE ID=main.IDStroje) AS NazevStroje,' + '(SELECT TOP(1) pracoviste FROM ' + tblCPrac + ' WHERE ID=main.IDPracoviste) AS KodPracoviste,' + '(SELECT TOP(1) Nazev FROM ' + tblCPrac + ' WHERE ID=main.IDPracoviste) AS NazevPracoviste'; selNazevOperaceEvidROpR = '(SELECT TOP(1) Nazev FROM ' + tblPrPost + ' WITH(NOLOCK) WHERE IDOdchylkyDo IS NULL AND Doklad=main.DokladPrPostup AND Alt=main.AltPrPostup' + ' AND IDPrikaz=main.IDPrikaz /*AND Priorita=0*/ ) AS NazevOperace, (SELECT TOP(1) k.Nazev1 FROM ' + tblPrikaz + ' p JOIN ' + tblKZ + ' k WITH(NOLOCK) ON (k.ID=p.IDTabKmen) WHERE p.ID=main.IDPrikaz) AS Nazev1Dilce, (SELECT TOP(1) k.SkupZbo FROM ' + tblPrikaz + ' p JOIN ' + tblKZ + ' k WITH(NOLOCK) ON (k.ID=p.IDTabKmen) WHERE p.ID=main.IDPrikaz) AS SkupZboDilce, (SELECT TOP(1) k.RegCis FROM ' + tblPrikaz + ' p JOIN ' + tblKZ + ' k WITH(NOLOCK) ON (k.ID=p.IDTabKmen) WHERE p.ID=main.IDPrikaz) AS RegCisDilce' + ', CONVERT(bit, ISNULL( (SELECT Odvadeci FROM ' + tblPrPost + ' WITH(NOLOCK) WHERE IDOdchylkyDo IS NULL AND Doklad=main.DokladPrPostup' + ' AND Alt=main.AltPrPostup AND IDPrikaz=main.IDPrikaz), 0)) AS Odvadeci' + ', ISNULL( (SELECT Operace FROM ' + tblPrPost + ' WITH(NOLOCK) WHERE IDOdchylkyDo IS NULL AND Doklad=main.DokladPrPostup' + ' AND Alt=main.AltPrPostup AND IDPrikaz=main.IDPrikaz), N'''') AS Operace' {$IFDEF CUSTOM_CTRL_INCOSystems} + ', dbo.ef_Vyroba_EvidROpR_MamNeuzavrenouPredchozi (main.IDPrikaz, main.DokladPrPostup, main.AltPrPostup) AS MamNeuzavrenePredchozi' {$ENDIF} ; selOperaceSpec = '(SELECT COUNT(ID) FROM ' + tblKPLHlav + ' WITH(NOLOCK) WHERE DruhDokladu=5 AND IDOperace=TabPrPostup.ID) AS PocetKP' + ', TabPrPostup.Kusy_zad AS _info_Kusy_zad, TabPrPostup.Kusy_nepotrebne AS _info_Kusy_nepotrebne, TabPrPostup.Kusy_pozadovane AS _info_Kusy_pozadovane' + ', TabPrPostup.ProcSplneni AS _info_ProcSplneni, TabPrPostup.Kusy_zive AS _info_Kusy_zive, TabPrPostup.Kusy_odv AS _info_Kusy_odv' + ', TabPrPostup.Kusy_zmet AS _info_Kusy_zmet, TabPrPostup.Kusy_zmet_opr_IO AS _info_Kusy_zmet_opr_IO, TabPrPostup.Kusy_zmet_neopr AS _info_Kusy_zmet_neopr' + ', (SELECT s.Nazev FROM ' + tblCisStroj + ' s WITH(NOLOCK) WHERE s.ID=main.IDStroje) AS StrojNazev, (SELECT s.Kod FROM ' + tblCisStroj + ' s WHERE s.ID=main.IDStroje) AS StrojKod'; selPolKoopObjSpec = ''; selVyrCisPrikazSpec = ''; selEvidOperace = '(SELECT IDEvidRozpracOper FROM ' + tblRozpracOperR + ' WITH(NOLOCK) WHERE ID=main.IDEvidRozpracOperR) AS IDEvidRozpracOper' + ', (SELECT nazev FROM ' + tblPrPost + ' WITH(NOLOCK) WHERE IDPrikaz=main.IDPrikaz AND Doklad=main.DokladPrPostup AND Alt=main.AltPrPostup AND IDOdchylkyDo IS NULL) AS NazevOperace'; selTPVStroj = 'p.pracoviste'; sqlSelKPs = 'SELECT ID, Kategorie, (SELECT k.Popis FROM ' + tblKategKJ + ' k WHERE k.Cislo=Kategorie) AS PopisKategorie, PoradoveCislo, IDKmenZbozi'; sqlSelKPParams = 'SELECT ID, IDKPLHlav, Poradi, TypHodnoty, CASE TypHodnoty WHEN 0 THEN N''Číslo'' WHEN 1 THEN N''Text'' WHEN 2 THEN N''Ano/Ne'' END AS TypHodnotyText' + ', Popis, HodnotaPozadovanaI, ToleranceDolni, ToleranceHorni, HodnotaNamerena, HodnotaText'; sqlSelKPParamsCis = 'SELECT FROM ' + tblKPCisParamsKontr; selEvidRozpOpSpec = ''; type TKooperacniObjednavkaService = class(TServiceBase) public function GetMeta: TJSONObject; function GetByParams (params: TDictionary; AConn: TFDConnection = nil): TObjectList; function GetByID (const AID: integer; const polozky: Boolean=false; AConn: TFDConnection = nil): TKooperacniObjednavka; end; TKooperacniObjednavkaPolozkaService = class(TServiceBase) public function GetMeta: TJSONObject; function GetByParams (params: TDictionary): TObjectList; function GetByID (const AID: integer): TKooperacniObjednavkaPolozka; end; TVyrobniPrikazSearchParam = record id: integer; rada: string; cisZak: string; stav: byte; idZak: Integer; end; TTPVZakazkoveModifikaceService = class(TServiceBase) public function GetMeta: TJSONObject; function GetByParams (params: TDictionary): TObjectList; end; TTPVZakazkoveModifikaceDilceService = class(TServiceBase) public function GetMeta: TJSONObject; function GetByParams (params: TDictionary): TObjectList; end; TTPVPrednastaveniOperaciService = class(TServiceBase) public function GetMeta: TJSONObject; function GetAll (params: TDictionary=nil): TObjectList; function GetByID (const AID: integer=0; params: TDictionary=nil; AConn: TFDConnection = nil): TTPVPrednastaveniOperaci; function GetByParams (params: TDictionary; AConn: TFDConnection = nil): TObjectList; end; TVyrobaEvidRozpracOperaciService = class(TServiceBase) public function GetMeta: TJSONObject; virtual; function RadekGetMeta: TJSONObject; virtual; function GetByID (const AID: integer; incOper: boolean; AConn: TFDConnection = nil): TEvidRozpracOperace; function GetByParams (params: TDictionary; AConn: TFDConnection = nil): TObjectList; end; TVyrobaEvidRozpracOperaciOperaceService = class(TServiceBase) public function GetMeta: TJSONObject; virtual; function GetByID (const AID: integer; incOper: boolean; params: TDictionary=nil; AConn: TFDConnection = nil): TEvidRozpracOperaceOperace; function GetByParams (params: TDictionary): TObjectList; end; TVyrobaEvidenceOperaciService = class(TServiceBase) public function GetMeta: TJSONObject; virtual; function GetByID (const AID: integer; params: TDictionary=nil; AConn: TFDConnection = nil): TVyrobaEvidovanaOperace; function GetByParams (params: TDictionary; AConn: TFDConnection = nil): TObjectList; end; TVyrobniPrikazService = class(TServiceBase) public function GetAll (params: TDictionary=nil; AConn: TFDConnection = nil): TObjectList; function GetByID (const AID: integer; params: TDictionary=nil; AConn: TFDConnection = nil): TVyrobniPrikaz; function GetByParams (params: TDictionary; AConn: TFDConnection = nil): TObjectList; function GetMeta: TJSONObject; virtual; function GetDokumentaci (const AID: integer; jenSeznam: boolean=false; oblast: byte=3; operace: string=''; base64: boolean=false): TObjectList; function GetDokladyOZ (const AID: integer; polozky: Boolean=false; AConn: TFDConnection = nil): TObjectList; function GetKusovnik (idVPr: integer; mnozReq: extended; jenPlatne: boolean=true; idKmen: integer = 0; AConn: TFDConnection = nil): TObjectList; end; TVyrobaVyrCisPrikazService = class(TServiceBase) public function GetByParams (params: TDictionary; AConn: TFDConnection = nil): TObjectList; function GetByID (const AID: integer; params: TDictionary=nil; AConn: TFDConnection = nil): TVyrCisPrikaz; function GetMeta: TJSONObject; virtual; end; TMaterialPrikazuService = class(TServiceBase) public function GetAll (params: TDictionary=nil; jenPlatne: boolean=true; AConn: TFDConnection = nil): TObjectList; function GetByParams (params: TDictionary; AConn: TFDConnection = nil): TObjectList; function GetByID (const AID: Integer; AConn: TFDConnection = nil): TMaterialPrikazu; function GetByIDVPr (idVPr: integer; jenPlatne: boolean=true; AConn: TFDConnection = nil): TObjectList; function GetMeta: TJSONObject; virtual; function GetMetaKusovniku: TJSONObject; end; TTPVKusovnikDilceService = class(TServiceBase) public function GetMeta: TJSONObject; virtual; function GetByParams (params: TDictionary; AConn: TFDConnection = nil): TObjectList; end; TTPVOperaceDilceService = class(TServiceBase) public function GetMeta: TJSONObject; virtual; function GetByParams (params: TDictionary; AConn: TFDConnection = nil): TObjectList; end; TVyrobniOperaceService = class(TServiceBase) public function GetAll (params: TDictionary=nil; jenPlatne: boolean=true; jenZiveVPr: Boolean=true; AConn: TFDConnection = nil): TObjectList; function GetByParams (params: TDictionary; AConn: TFDConnection = nil): TObjectList; virtual; function GetByID (const AID: Integer; params: TDictionary=nil; AConn: TFDConnection = nil): TVyrobniOperace; virtual; function GetByIDPrikazu (const AID: Integer; AConn: TFDConnection = nil): TObjectList; virtual; function GetKPsByID (const AID: Integer): TObjectList; function GetByBarCode (const ABarCode: string; AConn: TFDConnection = nil): TVyrobniOperace; virtual; function GetMeta: TJSONObject; virtual; procedure Update (AOperace: TVyrobniOperace); procedure ZapisDZTasku (jsonData: string; var respString: string); function GetDZTaskyNeevidovane(odDatum, doDatum: string): TObjectList; end; TQMSKontrolniPostupyService = class(TServiceBase) public function GetAll (params: TDictionary=nil): TObjectList; function GetByID (const AID: integer): TQMSKontrolniPostup; function GetListByIDPostup(const AID: integer): TObjectList; function GetMeta: TJSONObject; function GetParamsByIDHlav (const AID: Integer): TObjectList; end; TQMSKontrolniPlanService = class(TServiceBase) public function GetAll (params: TDictionary=nil): TObjectList; function GetByID (const AID: integer): TQMSKontrolniPlan; function GetMeta: TJSONObject; function GetByParams (params: TDictionary): TObjectList; virtual; end; TTPVPracovisteService = class(TServiceBase) public function GetAll (params: TDictionary=nil): TObjectList; function GetByID (const AID: integer): TTPVPracoviste; function GetMeta: TJSONObject; function GetFrontaPrace(const AID: integer): TObjectList; end; TTPVStrojService = class(TServiceBase) public function GetAll (params: TDictionary=nil): TObjectList; function GetMeta: TJSONObject; function GetByID (const AID: integer): TTPVStroj; function GetByParams (params: TDictionary): TObjectList; end; TTPVCisKoopService = class(TServiceBase) public function GetAll (params: TDictionary=nil; AConn: TFDConnection = nil): TObjectList; function GetMeta: TJSONObject; function GetByID (const AID: integer; AConn: TFDConnection = nil): TTPVCisKoop; function GetByParams (params: TDictionary; AConn: TFDConnection = nil): TObjectList; end; TTPVCiselnikZmenService = class(TServiceBase) public function GetMeta: TJSONObject; function GetByID (const AID: Integer): TTPVCiselnikZmen; function GetByParams (params: TDictionary): TObjectList; end; implementation uses System.StrUtils, System.DateUtils, System.RegularExpressions, REST.Json, MVCFramework.FireDAC.Utils, MVCFramework.DataSet.Utils, MVCFramework.Serializer.Commons; { TTPVCisKoopService } function TTPVCisKoopService.GetMeta: TJSONObject; var lSQL: string; sqlConnX: TFDConnection; lQry: TFDQuery; begin lSQL:= 'SELECT main.ID, main.Rada, main.kod, main.Blokovano, main.Nazev' + IfThen(selCisKoopSpec<>'', ', '+ selCisKoopSpec, '') + ' FROM ' + tblCisKoop + ' main WHERE 1=0'; sqlConnX:= TFDConnection.Create(nil); sqlConnX.ConnectionDefName:= sqlPoolName; lQry:= TFDQuery.Create(nil); try sqlConnX.Connected := true; lQry.Connection := sqlConnX; lQry.FetchOptions.Mode := fmAll; lQry.Open (lSQL); result := lQry.MetadataAsJSONObject(); finally lQry.Close; lQry.Free; sqlConnX.Close; sqlConnX.Free; end; end; function TTPVCisKoopService.GetAll (params: TDictionary=nil; AConn: TFDConnection = nil): TObjectList; var lSQL, ordBy, errMsg: string; jenSeznam: boolean; sqlConnX: TFDConnection; lQry: TFDQuery; lokalniConnection: boolean; begin result := nil; lokalniConnection := (AConn=nil); if (lokalniConnection) then begin sqlConnX := TFDConnection.Create (nil); sqlConnX.ConnectionDefName := sqlPoolName; end else sqlConnX := AConn; jenSeznam := false; ordBy := 'main.Rada, main.kod'; if (params<>nil) then begin if (params.ContainsKey('seznam')) then if (params.Items['seznam']='1') then jenSeznam := true; if (params.ContainsKey('ordBy')) then if (params.Items['ordBy']<>'') then ordBy := params.Items['ordBy']; end; lSQL := 'SELECT main.ID, main.Rada, main.kod, main.Blokovano, main.Nazev' + IfThen(selCisKoopSpec<>'', ', '+ selCisKoopSpec, '') + ' FROM ' + tblCisKoop + ' main'; lSQL := lSQL + ' ORDER BY ' + ordBy; lQry := TFDQuery.Create(nil); try lQry.Connection := sqlConnX; lQry.FetchOptions.Mode := fmAll; lQry.Open (lSQL); result:= lQry.AsObjectList; finally lQry.Close; lQry.Free; if (lokalniConnection) then begin sqlConnX.Close; sqlConnX.Free; end; end; end; function TTPVCisKoopService.GetByID (const AID: Integer; AConn: TFDConnection = nil): TTPVCisKoop; var lSQL: string; p: TDictionary; sqlConnX: TFDConnection; lQry: TFDQuery; lokalniConnection: boolean; begin result:= nil; lokalniConnection := (AConn=nil); if (lokalniConnection) then begin sqlConnX := TFDConnection.Create (nil); sqlConnX.ConnectionDefName := sqlPoolName; end else sqlConnX := AConn; lSQL:= 'SELECT main.ID, main.Rada, main.kod, main.Blokovano, main.Nazev' + IfThen(selCisKoopSpec<>'', ', '+ selCisKoopSpec, '') + ' FROM ' + tblCisKoop + ' main WHERE main.ID=:ID'; p := TDictionary.Create; lQry := TFDQuery.Create(nil); try sqlConnX.Connected := true; lQry.FetchOptions.Mode := fmAll; lQry.Connection:= sqlConnX; lQry.Open (lSQL, [AID]); lQry.First; try if (lQry.RecordCount=1) then result := lQry.AsObject else raise EServiceException.Create('Kooperace s ID ' + AID.ToString + ' nebyla nalezena.'); except on E:Exception do raise EServiceException.Create('Kooperace s ID ' + AID.ToString + ' nebyla nalezena - ' + E.Message); end; finally p.Free; lQry.Close; lQry.Free; if (lokalniConnection) then begin sqlConnX.Close; sqlConnX.Free; end; end; end; function TTPVCisKoopService.GetByParams (params: TDictionary; AConn: TFDConnection = nil): TObjectList; var lSQL, where, errMsg, ordBy: string; AID, cnt, rNo: integer; jenSeznam: boolean; sqlConnX: TFDConnection; lQry: TFDQuery; lVPr: TObjectList; vpr: TTPVCisKoop; lokalniConnection: boolean; begin result:= nil; lokalniConnection:= (AConn=nil); if (lokalniConnection) then begin sqlConnX:= TFDConnection.Create (nil); sqlConnX.ConnectionDefName:= sqlPoolName; end else sqlConnX:= AConn; vpr:= TTPVCisKoop.Create; lVPr:= TObjectList.Create; jenSeznam:= false; ordBy:= 'main.Rada, main.Kod'; if (params<>nil) then begin if (params.ContainsKey('seznam')) then if (params.Items['seznam']='1') then jenSeznam:= true; if (params.ContainsKey('ordBy')) then if (params.Items['ordBy']<>'') then ordBy:= params.Items['ordBy']; end; lSQL:= 'SELECT main.ID FROM ' + tblCisKoop + ' main'; if (jenSeznam) then lSQL:= lSQL.Replace('main.ID', 'main.ID, main.Rada, main.kod, main.Blokovano, main.Nazev'); where:= ''; if (params<>nil) then begin if (params.ContainsKey('rada')) then if (params.Items['rada']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.Rada=N' + params.Items['rada'].QuotedString; end; if (where<>'') then lSQL:= lSQL + ' WHERE ' + where; lSQL:= lSQL + ' ORDER BY ' + ordBy; lSQL:= lSQL.Replace('main.', ''); lQry:= TFDQuery.Create(nil); try sqlConnX.Connected := true; lQry.Connection:= sqlConnX; lQry.FetchOptions.Mode:= fmAll; lQry.Open(lSQL); cnt:= lQry.RecordCount; if (cnt>0) then begin try lQry.First; rNo:= 1; while (rNo<=cnt) do begin lQry.RecNo:= rNo; AID:= lQry.FieldByName('ID').AsInteger; vpr:= self.GetByID (AID, sqlConnX); lVPr.Add(vpr); if not(lQry.Active) then lQry.Open; Inc(rNo); end; result:= lVpr; finally lQry.Free; end; end else raise EServiceException.Create('Vybranym podminkam neodpovida zadna kooperace'); finally lQry.Close; lQry.Free; if (lokalniConnection) then begin sqlConnX.Close; sqlConnX.Free; end; end; end; { TKooperacniObjednavkaService } function TKooperacniObjednavkaService.GetMeta: TJSONObject; var lSQL: string; sqlConnX: TFDConnection; lQry: TFDQuery; begin lSQL:= 'SELECT ' + GetTabCols('', tblKObj) + ' FROM ' + tblKObj + ' WHERE 1=0'; sqlConnX := TFDConnection.Create(nil); sqlConnX.ConnectionDefName := sqlPoolName; lQry := TFDQuery.Create(nil); try sqlConnX.Connected := true; lQry.FetchOptions.Mode := fmAll; lQry.Connection := sqlConnX; lQry.Open (lSQL); Result := lQry.MetadataAsJSONObject(); finally lQry.Close; lQry.Free; sqlConnX.Close; sqlConnX.Free; end; end; function TKooperacniObjednavkaService.GetByParams (params: TDictionary; AConn: TFDConnection = nil): TObjectList; var lSQL, where, errMsg, ordBy: string; jenSeznam: boolean; cnt, rNo, pol: integer; bPol: boolean; sqlConnX: TFDConnection; lQry: TFDQuery; lKoopObj: TObjectList; koopObj: TKooperacniObjednavka; lokalniConnection: boolean; begin result:= nil; lokalniConnection:= (AConn=nil); if (lokalniConnection) then begin sqlConnX:= TFDConnection.Create (nil); sqlConnX.ConnectionDefName:= sqlPoolName; end else sqlConnX:= AConn; jenSeznam:= false; ordBy:= 'ID'; if (params<>nil) then begin if (params.ContainsKey('seznam')) then if (params.Items['seznam']='1') then jenSeznam:= true; if (params.ContainsKey('ordBy')) then if (params.Items['ordBy']<>'') then ordBy:= params.Items['ordBy']; end; lSQL:= 'SELECT ID FROM ' + tblKObj; where:= ''; if (params<>nil) then begin if (params.ContainsKey('id')) then if (params.Items['id']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'ID=' + params.Items['id']; if (params.ContainsKey('rada')) then if (params.Items['rada']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'Rada=N' + params.Items['rada'].QuotedString; if (params.ContainsKey('cOrgKoop')) then if (params.Items['cOrgKoop']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'IDOrganizace=(SELECT ID FROM ' + tblCOrg + ' WHERE CisloOrg=' + params.Items['cOrgKoop'] + ')'; if (params.ContainsKey('odDatum')) then if (params.Items['odDatum']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'DatPorizeni>=CONVERT(datetime, N' + params.Items['odDatum'].QuotedString + ', 104)'; if (params.ContainsKey('doDatum')) then if (params.Items['doDatum']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'DatPorizeni<=CONVERT(datetime, N' + params.Items['doDatum'].QuotedString + ', 104)'; end; if (where<>'') then lSQL:= lSQL + ' WHERE ' + where; lSQL:= lSQL + ' ORDER BY ' + ordBy; koopObj:= TKooperacniObjednavka.Create; lKoopObj:= TObjectList.Create; pol:= 0; if (params<>nil) then if (params.ContainsKey('polozky')) then if not(TryStrToInt(params.Items['polozky'], pol)) then pol:= 0; bPol:= (pol=1); lQry:= TFDQuery.Create(nil); try sqlConnX.Connected := true; lQry.FetchOptions.Mode := fmAll; lQry.Connection := sqlConnX; lQry.Open (lSQL); cnt := lQry.RecordCount; if (cnt>0) then begin try lQry.First; rNo := 1; while (rNo<=cnt) do begin lQry.RecNo := rNo; koopObj := self.GetByID(lQry.FieldByName('ID').AsInteger, bPol); lKoopObj.Add (koopObj); if not(lQry.Active) then lQry.Open; Inc (rNo); end; result := lKoopObj; lKoopObj := nil; finally end; end else raise EServiceException.Create('Vybranym podminkam neodpovida zadna kooperacni objednavka'); finally lQry.Close; lQry.Free; if (lokalniConnection) then begin sqlConnX.Close; sqlConnX.Free; end; end; end; function TKooperacniObjednavkaService.GetByID (const AID: Integer; const polozky: boolean=false; AConn: TFDConnection = nil): TKooperacniObjednavka; var lSQL, where: string; sqlConnX: TFDConnection; lQry: TFDQuery; p: TDictionary; pols: TObjectList; koS: TKooperacniObjednavkaPolozkaService; lokalniConnection: boolean; begin result:= nil; lokalniConnection:= (AConn=nil); if (lokalniConnection) then begin sqlConnX:= TFDConnection.Create (nil); sqlConnX.ConnectionDefName:= sqlPoolName; end else sqlConnX:= AConn; if (polozky) then begin p:= TDictionary.Create; koS:= TKooperacniObjednavkaPolozkaService.Create(self.FDM); end; lSQL:= 'SELECT ' + GetTabCols('', tblKObj, '', true) + ' FROM ' + tblKObj + ' WHERE ID=:ID'; lQry:= TFDQuery.Create(nil); try try sqlConnX.Connected := true; lQry.FetchOptions.Mode := fmAll; lQry.Connection := sqlConnX; lQry.Open (lSQL, [AID]); Result := lQry.AsObject; if (polozky) then begin p.Add ('idobj', AID.ToString); pols := koS.GetByParams (p); Result.Polozky := pols; end; except end; finally lQry.Close; lQry.Free; if (lokalniConnection) then begin sqlConnX.Close; sqlConnX.Free; end; end; if (polozky) then begin p.Free; koS.Free; end; end; { TKooperacniObjednavkaPolozkaService } function TKooperacniObjednavkaPolozkaService.GetMeta: TJSONObject; var lSQL: string; sqlConnX: TFDConnection; lQry: TFDQuery; begin lSQL:= 'SELECT ' + GetTabCols('', tblPKObj, 'main') + IfThen(selPolKoopObjSpec<>'', ',' + selPolKoopObjSpec, '') + ' FROM ' + tblPKObj + ' main WHERE 1=0'; lSQL:= lSQL.Replace('TabPolKoopObj.', 'main.'); if (selPolKoopObjSpec='') then lSQL:= lSQL.Replace('main.', '').Replace('main WHE', ' WHE'); sqlConnX:= TFDConnection.Create(nil); sqlConnX.ConnectionDefName:= sqlPoolName; lQry:= TFDQuery.Create(nil); try sqlConnX.Connected := true; lQry.FetchOptions.Mode := fmAll; lQry.Connection := sqlConnX; lQry.Open (lSQL); Result := lQry.MetadataAsJSONObject(); finally lQry.Close; lQry.Free; sqlConnX.Close; sqlConnX.Free; end; end; function TKooperacniObjednavkaPolozkaService.GetByID (const AID: integer): TKooperacniObjednavkaPolozka; begin result:= nil; end; function TKooperacniObjednavkaPolozkaService.GetByParams (params: TDictionary): TObjectList; var lSQL, where, errMsg, ordBy: string; jenSeznam: boolean; sqlConnX: TFDConnection; lQry: TFDQuery; begin result:= nil; jenSeznam:= false; ordBy:= 'main.ID'; if (params<>nil) then begin if (params.ContainsKey('seznam')) then if (params.Items['seznam']='1') then jenSeznam:= true; if (params.ContainsKey('ordBy')) then if (params.Items['ordBy']<>'') then ordBy:= params.Items['ordBy']; end; lSQL:= 'SELECT ' + GetTabCols('', tblPKObj, 'main') + IfThen(selPolKoopObjSpec<>'', ',' + selPolKoopObjSpec, '') + ' FROM ' + tblPKObj + ' main'; where:= ''; if (params<>nil) then begin if (params.ContainsKey('id')) then if (params.Items['id']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.ID=' + params.Items['id']; if (params.ContainsKey('idobj')) then if (params.Items['idobj']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.IDObjednavky=' + params.Items['idobj']; end; lSQL:= lSQL.Replace('TabPolKoopObj.', 'main.'); if (where<>'') then lSQL:= lSQL + ' WHERE ' + where; lSQL:= lSQL + ' ORDER BY ' + ordBy; if (selPolKoopObjSpec='') then lSQL:= lSQL.Replace('main.', '').Replace('main WHE', ' WHE'); 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; finally lQry.Close; lQry.Free; sqlConnX.Close; sqlConnX.Free; end; end; { TVyrobniPrikazService } function TVyrobniPrikazService.GetByID (const AID: integer; params: TDictionary=nil; AConn: TFDConnection = nil): TVyrobniPrikaz; var lSQL, lSQLBak, strTemp, stav: string; id, rNum, iStav: integer; p: TDictionary; debug, stopDebug, operace, material: boolean; lOper: TObjectList; opSrv: TVyrobniOperaceService; lMaterial: TObjectList; matSrv: TMaterialPrikazuService; lVyrCisla: TObjectList; vyrCis: TVyrCisPrikaz; vcSrv: TVyrobaVyrCisPrikazService; lNadrizenePrikazy: TObjectList; lPodrizenePrikazy: TObjectList; vprSrv: TVyrobniPrikazService; lokalniConnection: Boolean; sqlConnX: TFDConnection; lQry: TFDQuery; extInfoStr: string; begin result:= nil; lokalniConnection:= (AConn=nil); if (lokalniConnection) then begin sqlConnX:= TFDConnection.Create (nil); sqlConnX.ConnectionDefName:= sqlPoolName; end else sqlConnX:= AConn; operace:= false; material:= false; debug:= false; stopDebug:= false; stav:= ''; if (params<>nil) then begin if (params.ContainsKey('stav')) then if (params.Items['stav']<>'') then stav:= params.Items['stav']; if (params.ContainsKey('operace')) then if (params.Items['operace']='1') then operace:= true; if (params.ContainsKey('material')) then if (params.Items['material']='1') then material:= true; if (params.ContainsKey('debug')) then if (params.Items['debug']='1') then debug:= true; if (params.ContainsKey('debug')) then if (params.Items['debug']='0') then stopDebug:= true; end; lSQL:= 'SELECT ' + GetTabCols('', tblVPr, 'main', false) + IfThen(selPrikazSpec<>'', ',' + selPrikazSpec, '') + ' FROM ' + tblVPr + ' main' + ' INNER JOIN ' + tblKZ + ' kz ON (kz.ID=main.IDTabKmen) LEFT JOIN ' + tblZak + ' zak ON (zak.ID=main.IDZakazka)' + ' LEFT JOIN ' + tblZakazModif + ' zmod ON (zmod.ID=main.IDZakazModif) WHERE main.ID=:ID'; if (stav<>'') then begin if not(TryStrToInt(stav, iStav)) then iStav:= 0; if (iStav>0) then lSQL:= lSQL + ' AND main.StavPrikazu=' + iStav.ToString; end; if (selPrikazSpec='') then lSQL:= lSQL.Replace('TabPrikaz.', 'main.'); lSQLBak := lSQL; rNum:= 0; lQry:= TFDQuery.Create (nil); try try sqlConnX.Connected := true; lQry.FetchOptions.Mode:= fmAll; lQry.Connection:= sqlConnX; lQry.Open (lSQL, [AID]); rNum:= lQry.RecordCount; except on E:Exception do raise EServiceException.Create ('Vyrobni prikaz s ID ' + AID.ToString + ' nebyl nalezen - ' + E.Message); end; finally lQry.Close; lQry.Free; end; if (rNum=0) then begin if (lokalniConnection) then begin sqlConnX.Close; sqlConnX.Free; end; Exit; end; if (debug) then begin if (self.FDM.Logger=nil) then self.FDM.CreateLogger ('debug.log'); if (self.FDM.Logger<>nil) then try self.FDM.LogInfo (Quick.Logger.TEventType.etDebug, lSQL); except on E:Exception do strTemp:= E.Message; end; end; p:= TDictionary.Create; p.Add('idprikaz', AID.ToString); opSrv:= TVyrobniOperaceService.Create (self.FDM); matSrv:= TMaterialPrikazuService.Create (self.FDM); vcSrv:= TVyrobaVyrCisPrikazService.Create (self.FDM); {$IFDEF CUSTOM_CTRL_Rootvin} if (params.ContainsKey('rtn')) then p.Add('rtn', params.Items['rtn']); if (params.ContainsKey('typPrikazu')) then p.Add('typPrikazu', params.Items['typPrikazu']); if (params.ContainsKey('nazevOp')) then p.Add('nazevOp', params.Items['nazevOp']); {$ENDIF} extInfoStr:= ''; lQry:= TFDQuery.Create(nil); try try sqlConnX.Connected := true; lQry.FetchOptions.Mode := fmAll; lQry.Connection := sqlConnX; if (SQLTableExists ('dbo', tblPrikazE)) then begin lSQL:= 'SELECT ' + GetTabCols ('dbo', tblPrikazE, '', true, 'ID') + ' FROM ' + tblPrikazE + ' WHERE ID=:ID'; try lQry.Open (lSQL, [AID]); if (lQry.RecordCount>0) then extInfoStr:= lQry.AsJSONObject; finally lQry.Close; end; end; if (lSQLBak<>'') then lSQL:= lSQLBak; lQry.Open (lSQL, [AID]); rNum:= lQry.RecordCount; if (rNum>0) then begin lQry.First; if not(lQry.EOF) then begin result:= lQry.AsObject; if (extInfoStr<>'') then result.ExtInfo:= ExtInfoStr; lVyrCisla:= vcSrv.GetByParams (p); try if (lVyrCisla.Count>0) then result.VyrobniCisla:= lVyrCisla; except end; if (operace) then begin lOper:= opSrv.GetByParams (p); try if (lOper.Count>0) then result.VyrobniOperace:= lOper; except end; end; if (material) then begin lMaterial:= matSrv.GetByParams (p); try if (lMaterial.Count>0) then result.Material := lMaterial; except end; end; lSQL:= 'SELECT h.CasZahajeni FROM ' + tblRozpracOperR + ' r INNER JOIN ' + tblRozpracOper + ' h ON (h.ID=r.IDEvidRozpracOper) WHERE r.IDPrikaz=:ID'; lSQL:= lSQL + ' AND h.CasZahajeni IS NOT NULL AND h.CasUkonceni IS NULL'; lQry.Open(lSQL, [AID]); lQry.First; if not(lQry.Eof) then result.EvidRozpracOperStart:= lQry.FieldByName('CasZahajeni').AsDateTime; lSQL:= 'SELECT v.ID, v.IDPrikaz, v.IDPrikazVyssi, v.Mnozstvi, v.KryteMnozstvi, v.PoziceKV, pv.IDTabKmen AS IDDilecVyssi, SkupZboV=kv.SkupZbo'; lSQL:= lSQL + ', RegCisV=kv.RegCis, Nazev1V=kv.Nazev1, v.Autor, v.DatPorizeni, v.Zmenil, v.DatZmeny FROM ' + tblPrikazVazby + ' v INNER JOIN '; lSQL:= lSQL + tblPrikaz + ' pv ON (pv.ID=v.IDPrikazVyssi) INNER JOIN ' + tblKZ + ' kv ON (kv.ID=pv.IDTabKmen) WHERE v.IDPrikaz=:ID'; lQry.Open(lSQL, [AID]); lQry.First; if not(lQry.Eof) then result.NadrizenePrikazy:= lQry.AsObjectList; lSQL:= 'SELECT v.ID, v.IDPrikaz, v.IDPrikazVyssi, v.Mnozstvi, v.KryteMnozstvi, v.PoziceKV, pn.IDTabKmen AS IDDilecNizsi, SkupZboN=kn.SkupZbo'; lSQL:= lSQL + ', RegCisN=kn.RegCis, Nazev1N=kn.Nazev1, v.Autor, v.DatPorizeni, v.Zmenil, v.DatZmeny FROM ' + tblPrikazVazby + ' v INNER JOIN '; lSQL:= lSQL + tblPrikaz + ' pn ON (pn.ID=v.IDPrikaz) INNER JOIN ' + tblKZ + ' kn ON (kn.ID=pn.IDTabKmen) WHERE v.IDPrikazVyssi=:ID'; lQry.Open(lSQL, [AID]); lQry.First; if not(lQry.Eof) then result.PodrizenePrikazy:= lQry.AsObjectList; end end; // else // raise EServiceException.Create ('Vyrobni prikaz s ID ' + AID.ToString + ' nebyl nalezen.'); except on E:Exception do raise EServiceException.Create ('Vyrobni prikaz s ID ' + AID.ToString + ' nebyl nalezen - ' + E.Message); end; finally p.Free; vcSrv.Free; matSrv.Free; opSrv.Free; if (debug) or (stopDebug) then begin self.FDM.Logger.Drain; Sleep(50); self.FDM.Logger.Stop; end; lQry.Close; lQry.Free; if (lokalniConnection) then begin sqlConnX.Close; sqlConnX.Free; end; end; // strTemp:= TJson.ObjectToJsonString(result); end; function TVyrobniPrikazService.GetAll (params: TDictionary=nil; AConn: TFDConnection = nil): TObjectList; var lSQL, errMsg, ordBy: string; jenSeznam: boolean; 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; jenSeznam:= false; ordBy:= 'main.ID'; if (params<>nil) then begin if (params.ContainsKey('seznam')) then if (params.Items['seznam']='1') then jenSeznam:= true; if (params.ContainsKey('ordBy')) then if (params.Items['ordBy']<>'') then ordBy:= params.Items['ordBy']; end; lSQL:= 'SELECT ' + GetTabCols('', tblVPr, 'main') + ' FROM ' + tblVPr + ' main'; lSQL:= lSQL + ' ORDER BY ' + ordBy; lSQL:= lSQL.Replace('TabPrikaz.', 'main.'); lQry:= TFDQuery.Create(nil); try sqlConnX.Connected := true; lQry.FetchOptions.Mode := fmAll; lQry.Connection := sqlConnX; lQry.Open (lSQL); result := lQry.AsObjectList; finally lQry.Close; lQry.Free; if (lokalniConnection) then begin sqlConnX.Close; sqlConnX.Free; end; end; end; function TVyrobniPrikazService.GetDokladyOZ (const AID: Integer; polozky: Boolean = False; AConn: TFDConnection = nil): TObjectList; var lSQL: string; lQry: TFDQuery; sqlConnX: TFDConnection; cnt, rNo: integer; r: TObjectList; d: TDokladOZ; s: uSvc_ObehZbozi.TDokladOZService; lDokl: TObjectList; lokalniConnection: boolean; begin result:= nil; lokalniConnection := (AConn = nil); if (lokalniConnection) then begin sqlConnX:= TFDConnection.Create(nil); sqlConnX.ConnectionDefName:= sqlPoolName; end else sqlConnX := AConn; s:= uSvc_ObehZbozi.TDokladOZService.Create(self.FDM); lSQL:= 'SELECT DISTINCT(d.ID) FROM ' + tblDZ + ' d INNER JOIN ' + tblPZ + ' p ON (p.IDDoklad=d.ID) WHERE d.IDPrikaz=' + AID.ToString; lSQL:= lSQL + ' OR p.IDPrikaz= ' + AID.ToString + ' GROUP BY d.ID ORDER BY d.DatPorizeni'; lQry:= TFDQuery.Create(nil); try sqlConnX.Connected := true; lQry.FetchOptions.Mode := fmAll; lQry.Connection := sqlConnX; lQry.Open(lSQL); cnt:= lQry.RecordCount; if (cnt>0) then begin try lQry.First; rNo:= 1; while (rNo<=cnt) do begin lQry.RecNo:= rNo; d:= s.GetByID (lQry.FieldByName('ID').AsInteger, false); // s polozkami lDokl.Add(d); if not(lQry.Active) then lQry.Open; Inc(rNo); end; result:= lDokl; finally end; end else raise EServiceException.Create('Vybranym podminkam neodpovida zadny doklad OZ'); finally lQry.Close; lQry.Free; if (lokalniConnection) then begin sqlConnX.Close; sqlConnX.Free; end; end; end; function TVyrobniPrikazService.GetDokumentaci (const AID: Integer; jenSeznam: Boolean = False; oblast: byte=3; operace: string=''; base64: boolean=false): TObjectList; var lSQL: string; lQry: TFDQuery; sqlConnX: TFDConnection; a: TArray; i: integer; begin result:= nil; a:= GetTabColsArray('', tblVyrobDokum, 'vd'); if (jenSeznam) then TabColsArrayRemove(a, 'vd.Dokument'); SetLength(a, Length(a)+1); a[Length(a)-1]:= 'vvd.Operace'; lSQL:= 'SELECT ' + string.Join(',', a) + ' FROM ' + tblVyrobDokum + ' vd INNER JOIN ' + tblVazbyVyrobDokum + ' vvd ON (vvd.ID1VyrDokum=vd.ID1)'; lSQL:= lSQL + ' WHERE vd.zmenaDo IS NULL AND vd.Archiv=0 AND vvd.Oblast=' + oblast.ToString + ' AND vvd.RecID=' + AID.ToString; lSQL:= lSQL + ' AND (vvd.RecID2 IS NULL OR vvd.RecID2=0)'; if (operace<>'') then lSQL:= lSQL + ' AND LTRIM(RTRIM(vvd.Operace))=N' + operace.QuotedString; lSQL:= lSQL + ' ORDER BY vd.ID'; if (base64) then lSQL:= lSQL.Replace('vd.Dokument', 'dbo.ef_EncodeBase64(vd.Dokument) AS Dokument'); sqlConnX:= TFDConnection.Create(nil); sqlConnX.ConnectionDefName:= sqlPoolName; lQry:= TFDQuery.Create(nil); try lQry.FetchOptions.Mode:= fmAll; try sqlConnX.Connected := true; lQry.Connection := sqlConnX; lQry.Open (lSQL); result := lQry.AsObjectList; lQry.Close; except end; finally lQry.Close; lQry.Free; sqlConnX.Close; sqlConnX.Free; end; end; function TVyrobniPrikazService.GetByParams (params: TDictionary; AConn: TFDConnection = nil): TObjectList; var lSQL, where, errMsg, ordBy: string; jenSeznam: boolean; operace, material, spustene: boolean; id, AID, iRet, cnt, rNo: integer; lQry: TFDQuery; sqlConnX: TFDConnection; retObj: TVyrobniPrikaz; retList: TObjectList; locParams: TDictionary; lokalniConnection: boolean; lokIDcka: TList; begin result:= nil; lokalniConnection := (AConn = nil); if (lokalniConnection) then begin sqlConnX:= TFDConnection.Create(nil); sqlConnX.ConnectionDefName:= sqlPoolName; end else sqlConnX := AConn; jenSeznam:= false; ordBy:= 'main.ID'; if (params<>nil) then begin if (params.ContainsKey('seznam')) then if (params.Items['seznam']='1') then jenSeznam:= true; if (params.ContainsKey('ordBy')) then if (params.Items['ordBy']<>'') then ordBy:= params.Items['ordBy']; end; locParams:= TDictionary.Create; lSQL:= 'SELECT main.ID FROM ' + tblVPr + ' main' + ' INNER JOIN ' + tblKZ + ' kz ON (kz.ID=main.IDTabKmen) LEFT JOIN ' + tblZak + ' zak ON (zak.ID=main.IDZakazka)'; where:= ''; id:= 0; if (params<>nil) then if (params.ContainsKey('id')) then if (params.Items['id']<>'') then if (params.Items['id']<>'0') then begin id:= params.Items['id'].ToInteger; if (params.ContainsKey('jenId')) then begin if (params.Items['jenId']<>'') then if (params.Items['jenId']='1') then begin lSQL:= 'SELECT main.ID FROM ' + tblVPr + ' main' + ' INNER JOIN ' + tblKZ + ' kz ON (kz.ID=main.IDTabKmen) LEFT JOIN ' + tblZak + ' zak ON (zak.ID=main.IDZakazka)'; where:= 'main.ID IN (SELECT IDPrikazVyssi FROM ' + tblPrikazVazby + ' WHERE IDPrikaz=' + id.ToString + ')'; end end else where:= where + 'main.ID=' + id.ToString; end; spustene:= false; if (params<>nil) then begin if (params.ContainsKey('spustene')) then if (params.Items['spustene']='1') then spustene:= true; if (params.ContainsKey('idDilce')) then if (params.Items['idDilce']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'kz.ID=' + params.Items['idDilce']; if (params.ContainsKey('rada')) then if (params.Items['rada']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.Rada=N' + params.Items['rada'].QuotedString; if (params.ContainsKey('prikaz')) then if (params.Items['prikaz']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.Prikaz=' + params.Items['prikaz']; if (params.ContainsKey('cisloZakazky')) then if (params.Items['cisloZakazky']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'zak.CisloZakazky=N' + params.Items['cisloZakazky'].QuotedString; if (params.ContainsKey('idZakazky')) then if (params.Items['idZakazky']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.IDZakazka=' + params.Items['idZakazky']; { if (params.ContainsKey('idZakazModif')) then begin if (params.Items['idZakazModif']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.IDZakazModif=' + params.Items['idZakazModif'] else where:= where + IfThen(where<>'', ' AND ', '') + 'main.IDZakazModif IS NULL'; end else begin where:= where + IfThen(where<>'', ' AND ', '') + 'main.IDZakazModif IS NULL'; end; } if (params.ContainsKey('stav')) then if (params.Items['stav']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.StavPrikazu=' + params.Items['stav']; if (params.ContainsKey('skDilce')) then if (params.Items['skDilce']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'kz.SkupZbo=N' + params.Items['skDilce'].QuotedString; end; if (spustene) then where:= where + IfThen(where<>'', ' AND ', '') + 'EXISTS (SELECT 1 FROM ' + tblPrPost + ' pp INNER JOIN ' + tblRozpracOperR + ' r ON (r.DokladPrPostup=pp.Doklad AND r.AltPrPostup=pp.Alt AND r.IDPrikaz=pp.IDPrikaz)' + ' INNER JOIN ' + tblRozpracOper + ' h ON (h.ID=r.IDEvidRozpracOper)' + ' WHERE pp.IDOdchylkyDo IS NULL AND pp.IDPrikaz=main.ID AND h.DatumUzavreni IS NULL)'; if (where<>'') then lSQL:= lSQL + ' WHERE ' + where; lSQL:= lSQL + ' ORDER BY ' + ordBy; lSQL:= lSQL.Replace('TabPrikaz.', 'main.'); if (selPrikazSpec='') then lSQL:= lSQL.Replace('main.', '').Replace('main WHE', ' WHE'); operace:= false; material:= false; if (params<>nil) then begin if (params.ContainsKey('operace')) then if (params.Items['operace']='1') then begin operace:= true; locParams.Add ('operace', '1'); end; if (params.ContainsKey('material')) then if (params.Items['material']='1') then begin material:= true; locParams.Add ('material', '1'); end; if (params.ContainsKey('debug')) then if (params.Items['debug']='1') then locParams.Add ('debug', '1'); end; lQry:= TFDQuery.Create(nil); try lokIDcka := TList.Create; try sqlConnX.Connected := true; lQry.FetchOptions.Mode:= fmAll; lQry.Connection:= sqlConnX; lQry.Open(lSQL); cnt:= lQry.RecordCount; lQry.First; rNo:= 1; while (rNo<=cnt) do begin lQry.RecNo:= rNo; AID:= lQry.FieldByName('ID').AsInteger; lokIDcka.Add (AID); if not(lQry.Active) then lQry.Open; Inc(rNo); end; except end; retList := TObjectList.Create; try for AID in lokIDcka do begin retObj:= self.GetByID (AID, locParams, sqlConnX); retList.Add (retObj); end; result:= retList; retList:= nil; finally retList.Free; end; finally lokIDcka.Free; lQry.Close; lQry.Free; if (lokalniConnection) then begin sqlConnX.Close; sqlConnX.Free; end; end; end; function TVyrobniPrikazService.GetMeta: TJSONObject; var lSQL: string; lQry: TFDQuery; begin lSQL:= 'SELECT ' + GetTabCols('', tblVPr, 'main') + IfThen(selPrikazSpec<>'', ',' + selPrikazSpec, '') + ' FROM ' + tblVPr + ' main WHERE 1=0'; lSQL:= lSQL.Replace('TabPrikaz.', 'main.'); if (selPrikazSpec='') then lSQL:= lSQL.Replace('main.', ''); lQry:= TFDQuery.Create(nil); lQry.Connection:= FDM.sqlConn; try lQry.Open(lSQL); result := lQry.MetadataAsJSONObject(); finally lQry.Close; lQry.Free; end; end; function TVyrobniPrikazService.GetKusovnik (idVPr: Integer; mnozReq: Extended; jenPlatne: Boolean = True; idKmen: Integer = 0; AConn: TFDConnection = nil): TObjectList; var lSQL, lSQL2, where: string; lQry: TFDQuery; sqlConnX: TFDConnection; idModif: integer; lokalniConnection: boolean; begin result:= nil; lokalniConnection := (AConn = nil); if (lokalniConnection) then begin sqlConnX:= TFDConnection.Create(nil); sqlConnX.ConnectionDefName:= sqlPoolName; end else sqlConnX := AConn; idModif:= 0; if (idVPr>0) and (idKmen=0) then begin lSQL:= 'SELECT IDTabKmen, IDZakazModif FROM ' + tblVPr + ' WHERE ID=' + idVPr.ToString; lQry:= TFDQuery.Create(nil); try try sqlConnX.Connected := true; lQry.FetchOptions.Mode := fmAll; lQry.Connection := sqlConnX; lQry.Open(lSQL); idKmen:= lQry.FieldByName('IDTabKmen').AsInteger; idModif:= lQry.FieldByName('IDZakazModif').AsInteger; except on E:Exception do raise EServiceException.Create (errMaterialy + E.Message); end; finally lQry.Close; lQry.Free; end; end; // nacti kusovnik z TPV pro pozadovany pocet ks try if ((idKmen>0)) and (mnozReq>=0) then begin lSQL := 'SET NOCOUNT ON' + CRLF + 'DECLARE @dat DATETIME=GETDATE()' + CRLF + 'IF OBJECT_ID(N''tempdb..#apiKusovnik'', N''U'') IS NOT NULL DROP TABLE #apiKusovnik' + CRLF + createTabAPIKusovnik + CRLF + 'INSERT INTO #apiKusovnik EXEC dbo.hp_generujKusovnik @IDFinal=' + idKmen.ToString + ', @MNF=' + mnozReq.ToString + ', @datum=@dat, @Can_raise=0' + CRLF + ', @IDZakazModif=' + IfThen(idModif=0, 'NULL', idModif.ToString); lQry:= TFDQuery.Create(nil); try sqlConnX.Connected := true; lQry.FetchOptions.Mode:= fmAll; lQry.Connection:= sqlConnX; lQry.ExecSQL(lSQL); lQry.ExecSQL('DELETE FROM #apiKusovnik WHERE uroven=0'); lSQL:= 'ALTER TABLE #apiKusovnik ADD SZNizsi NVARCHAR(3)' + CRLF + 'ALTER TABLE #apiKusovnik ADD RCNizsi NVARCHAR(30)' + CRLF + 'ALTER TABLE #apiKusovnik ADD Nazev1Nizsi NVARCHAR(100)' + CRLF // + 'ALTER TABLE #apiKusovnik ADD SZVyssi NVARCHAR(3)' + CRLF // + 'ALTER TABLE #apiKusovnik ADD RCVyssi NVARCHAR(30)' + CRLF + 'ALTER TABLE #apiKusovnik ADD IDPrikaz INT' + CRLF + 'ALTER TABLE #apiKusovnik ADD MJNizsi NVARCHAR(10)' + CRLF + 'ALTER TABLE #apiKusovnik DROP COLUMN prirez' + CRLF + 'ALTER TABLE #apiKusovnik DROP COLUMN RezijniMat' + CRLF + 'ALTER TABLE #apiKusovnik DROP COLUMN VyraditZKalkulace' + CRLF + 'ALTER TABLE #apiKusovnik DROP COLUMN uroven'; lQry.ExecSQL (lSQL); lSQL:= 'MERGE #apiKusovnik AS T USING ' + tblKZ + ' AS S ON (S.ID=T.IDKmenZbozi) WHEN MATCHED AND T.IDKmenZbozi IS NOT NULL THEN UPDATE SET T.SZNizsi=S.SkupZbo' + ', T.RCNizsi=S.RegCis, T.Nazev1Nizsi=S.Nazev1, T.MJNizsi=S.MJEvidence, T.IDPrikaz=' + idVPr.ToString + ';'; lQry.ExecSQL (lSQL); lQry.Open ('SELECT * FROM #apiKusovnik'); result := lQry.AsObjectList; except on E:Exception do raise EServiceException.Create(errMaterialy + E.Message); end; end else raise Exception.Create('Kusovnik nelze vygenerovat, nemam ID kmenove karty nebo je mnozstvi mensi nez 0.'); finally lQry.Close; lQry.Free; if (lokalniConnection) then begin sqlConnX.Close; sqlConnX.Free; end; end; end; { TVyrobaEvidenceOperaciService} function TVyrobaEvidenceOperaciService.GetByParams (params: TDictionary; AConn: TFDConnection = nil): TObjectList; var lSQL, where, ordBy: string; cnt, rNo, AID: integer; retObj: TVyrobaEvidovanaOperace; lsRetObj: TObjectList; jenPosledni: boolean; sqlConnX: TFDConnection; lQry: TFDQuery; lokalniConnection: boolean; begin result:= nil; lokalniConnection := (AConn = nil); if (lokalniConnection) then begin sqlConnX := TFDConnection.Create(nil); sqlConnX.ConnectionDefName := sqlPoolName; end else sqlConnX := AConn; jenPosledni:= false; where:= ''; lSQL:= 'SELECT ID FROM ' + tblPMZ; ordBy:= 'ID'; if (params<>nil) then begin if (params.ContainsKey('ordBy')) then if (params.Items['ordBy']<>'') then ordBy:= params.Items['ordBy']; if (params.ContainsKey('idPrikaz')) then if (params.Items['idPrikaz']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'IDPrikaz=' + params.Items['idPrikaz']; if (params.ContainsKey('kooperace')) then if (params.Items['kooperace']='1') then where:= where + IfThen(where<>'', ' AND ', '') + 'TypMzdy=2'; if not(where.Contains('TypMzdy')) then where:= where + IfThen(where<>'', ' AND ', '') + 'TypMzdy<>2'; if (params.ContainsKey('jenPosledni')) then if (params.Items['jenPosledni']='1') then begin lSQL:= lSQL.Replace('CT ID F', 'CT TOP(1) ID F'); ordBy:= 'DatPorizeni DESC'; end; end; if (where<>'') then lSQL:= lSQL + ' WHERE ' + where; if (ordBy<>'') then lSQL:= lSQL + ' ORDER BY ' + ordBy; if (where<>'') then // poustej to jen s nejakou podminkou, jinak se z toho zblaznis begin lQry:= TFDQuery.Create(nil); try sqlConnX.Connected := true; lQry.FetchOptions.Mode:= fmAll; lQry.Connection:= sqlConnX; lQry.Open(lSQL); cnt:= lQry.RecordCount; try retObj:= TVyrobaEvidovanaOperace.Create; lsRetObj:= TObjectList.Create; lQry.First; rNo:= 1; while (rNo<=cnt) do begin lQry.RecNo:= rNo; AID:= lQry.FieldByName('ID').AsInteger; retObj:= self.GetByID (AID, nil, sqlConnX); lsRetObj.Add (retObj); if not(lQry.Active) then lQry.Open; Inc(rNo); end; result:= lsRetObj; finally end; finally lQry.Close; lQry.Free; if (lokalniConnection) then begin sqlConnX.Close; sqlConnX.Free; end; end; end; end; function TVyrobaEvidenceOperaciService.GetByID (const AID: integer; params: TDictionary=nil; AConn: TFDConnection = nil): TVyrobaEvidovanaOperace; var lSQL: string; p: TDictionary; sqlConnX: TFDConnection; lokalniConnection: boolean; lQry: TFDQuery; extInfoStr: string; 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', tblPMZE)) then begin lSQL:= 'SELECT ' + GetTabCols ('dbo', tblPMZE, '', true, 'ID') + ' FROM ' + tblPMZE + ' WHERE ID=:ID'; lQry:= TFDQuery.Create(nil); try sqlConnX.Connected := true; lQry.FetchOptions.Mode:= fmAll; lQry.Connection:= sqlConnX; lQry.Open(lSQL, [AID]); if (lQry.RecordCount>0) then extInfoStr:= lQry.AsJSONObject; finally lQry.Close; lQry.Free; end; end; lSQL:= 'SELECT ' + GetTabCols('', tblPMZ, 'main') + IfThen(selEvidOperace<>'', ',' + selEvidOperace, '') + ' FROM ' + tblPMZ + ' main WHERE main.ID=:ID'; lQry:= TFDQuery.Create(nil); try try sqlConnX.Connected := true; lQry.FetchOptions.Mode := fmAll; lQry.Connection := sqlConnX; lQry.Open (lSQL, [AID]); lQry.First; if not(lQry.EOF) then begin result:= lQry.AsObject; if (extInfoStr<>'') then result.ExtInfo:= ExtInfoStr; end else raise EServiceException.Create('Evidence vyrobni operace s ID ' + AID.ToString + ' nebyla nalezena.'); except on E:Exception do raise EServiceException.Create('Evidence vyrobni operace s ID ' + AID.ToString + ' nebyla nalezena - ' + E.Message); end; finally lQry.Close; lQry.Free; if (lokalniConnection) then begin sqlConnX.Close; sqlConnX.Free; end; end; end; function TVyrobaEvidenceOperaciService.GetMeta: TJsonObject; var lSQL: string; lQry: TFDQuery; sqlConnX: TFDConnection; begin lSQL:= 'SELECT ' + GetTabCols('', tblPMZ, 'main') + ' FROM ' + tblPMZ + ' main WHERE 1=0'; lSQL:= lSQL.Replace('TabPrikazMzdyAZmetky.', 'main.'); if (selPrikazSpec='') then lSQL:= lSQL.Replace('main.', ''); sqlConnX:= TFDConnection.Create(nil); sqlConnX.ConnectionDefName:= sqlPoolName; lQry:= TFDQuery.Create(nil); try sqlConnX.Connected := true; lQry.FetchOptions.Mode:= fmAll; lQry.Connection:= sqlConnX; lQry.Open(lSQL); result:= lQry.MetadataAsJSONObject(); finally lQry.Close; lQry.Free; sqlConnX.Close; sqlConnX.Free; end; end; { TMaterialPrikazuService } function TMaterialPrikazuService.GetAll (params: TDictionary=nil; jenPlatne: boolean=true; AConn: TFDConnection = nil): TObjectList; var lSQL, errMsg, ordBy: string; jenSeznam: boolean; 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; jenSeznam := false; ordBy := 'main.ID'; if (params<>nil) then begin if (params.ContainsKey('seznam')) then if (params.Items['seznam']='1') then jenSeznam := true; if (params.ContainsKey('ordBy')) then if (params.Items['ordBy']<>'') then ordBy := params.Items['ordBy']; end; lSQL:= 'SELECT ' + GetTabCols('', tblPrKVazby, 'main', false) + IfThen(selMaterialSpec<>'', ',' + selMaterialSpec, '') + ' FROM ' + tblPrKVazby + ' main ' + ' INNER JOIN ' + tblKZ + ' kzN ON (kzN.ID=main.nizsi) INNER JOIN ' + tblKZ + ' kzV ON (kzV.ID=main.vyssi) ' + IfThen(jenPlatne, ' WHERE main.IDOdchylkyDo IS NULL', ''); lSQL:= lSQL + ' ORDER BY ' + ordBy; lSQL:= lSQL.Replace('TabPrKVazby.', 'main.'); lQry:= TFDQuery.Create(nil); try sqlConnX.Connected := true; lQry.FetchOptions.Mode := fmAll; lQry.Connection := sqlConnX; lQry.Open (lSQL); result := lQry.AsObjectList; finally lQry.Close; lQry.Free; if (lokalniConnection) then begin sqlConnX.Close; sqlConnX.Free; end; end; end; function TMaterialPrikazuService.GetByID (const AID: Integer; AConn: TFDConnection = nil): TMaterialPrikazu; var lSQL: string; p: TDictionary; lQry: TFDQuery; sqlConnX: TFDConnection; lokalniConnection: boolean; begin result := nil; lSQL := 'SELECT ' + GetTabCols('', tblPrKVazby, 'main', false) + IfThen(selMaterialSpec<>'', ',' + selMaterialSpec, '') + ' FROM ' + tblPrKVazby + ' main ' + ' INNER JOIN ' + tblKZ + ' kzN ON (kzN.ID=main.nizsi) INNER JOIN ' + tblKZ + ' kzV ON (kzV.ID=main.vyssi) ' + ' WHERE main.ID=:ID AND main.IDOdchylkyDo IS NULL'; lSQL := lSQL.Replace('TabPrKVazby.', 'main.'); p := TDictionary.Create; lokalniConnection := (AConn = nil); if (lokalniConnection) then begin sqlConnX := TFDConnection.Create(nil); sqlConnX.ConnectionDefName := sqlPoolName; end else sqlConnX := AConn; lQry:= TFDQuery.Create(nil); try sqlConnX.Connected := true; lQry.FetchOptions.Mode := fmAll; lQry.Connection := sqlConnX; lQry.Open (lSQL, [AID]); lQry.First; try if not(lQry.EOF) then begin result := lQry.AsObject; {$IFDEF CUSTOM_CTRL_Rootvin} var typDilce: string; var lQry2: TFDQuery; typDilce:= ''; if (SQLColumnExists('dbo', tblKZe, '_ASOL_IdentifTrideni')) then begin lQry2:= TFDQuery.Create(nil); try try lQry2.Connection:= sqlConnX; lSQL:= 'SELECT _ASOL_IdentifTrideni AS TypDilce FROM ' + tblKZe + ' WHERE ID=:ID'; lQry2.Open(lSQL, [lQry.FieldByName('nizsi').AsInteger]); if (lQry2.RecordCount=1) then typDilce:= lQry2.FieldByName('TypDilce').AsString; except end; finally lQry2.Close; FreeAndNil (lQry2); end; if (typDilce='1') then typDilce:= 'surovina' else if (typDilce='2') then typDilce:= 'polotovar' else if (typDilce='3') then typDilce:= 'obal' else typDilce:= ''; end; if (typDilce<>'') then result.typDilce:= typDilce; {$ENDIF} end else raise EServiceException.Create('MateriálovĂ˝ poĹľdavek s ID ' + AID.ToString + ' nebyl nalezen.'); except on E:Exception do raise EServiceException.Create('MateriálovĂ˝ poĹľadavek s ID ' + AID.ToString + ' nebyl nalezen - ' + E.Message); end; finally p.Free; lQry.Close; lQry.Free; if (lokalniConnection) then begin sqlConnX.Close; sqlConnX.Free; end; end; end; function TMaterialPrikazuService.GetMetaKusovniku: TJSONObject; var lSQL: string; sqlConnX: TFDConnection; lQry: TFDQuery; begin lSQL:= 'IF OBJECT_ID(N''tempdb..#apiKusovnik'', N''U'') IS NOT NULL DROP TABLE #apiKusovnik' + CRLF; lSQL:= lSQL + createTabAPIKusovnik + CRLF; lSQL:= 'ALTER TABLE #apiKusovnik ADD SZNizsi NVARCHAR(3)' + CRLF + 'ALTER TABLE #apiKusovnik ADD RCNizsi NVARCHAR(30)' + CRLF + 'ALTER TABLE #apiKusovnik ADD Nazev1Nizsi NVARCHAR(100)' + CRLF + 'ALTER TABLE #apiKusovnik ADD IDPrikaz INT' + CRLF + 'ALTER TABLE #apiKusovnik ADD MJNizsi NVARCHAR(10)' + CRLF + 'ALTER TABLE #apiKusovnik DROP COLUMN prirez' + CRLF + 'ALTER TABLE #apiKusovnik DROP COLUMN RezijniMat' + CRLF + 'ALTER TABLE #apiKusovnik DROP COLUMN VyraditZKalkulace' + CRLF + 'ALTER TABLE #apiKusovnik DROP COLUMN uroven'; lSQL:= lSQL + 'SELECT * FROM #apiKusovnik WHERE 1=0'; sqlConnX:= TFDConnection.Create(nil); sqlConnX.ConnectionName:= sqlPoolName; sqlConnX.Connected := true; lQry := TFDQuery.Create(nil); lQry.FetchOptions.Mode := fmAll; lQry.Connection := sqlConnX; try lQry.Open (lSQL); lQry.First; result:= lQry.MetadataAsJSONObject(); finally lQry.Close; lQry.Free; sqlConnX.Close; sqlConnX.Free; end; end; function TMaterialPrikazuService.GetByParams (params: TDictionary; AConn: TFDConnection = nil): TObjectList; var lSQL, lSQL2, where, errMsg, ordBy: string; AID, cnt, rNo: integer; resList: TObjectList; resObj: TMaterialPrikazu; jenSeznam, vcNeplatnychMat: boolean; lQry: TFDQuery; sqlConnX: TFDConnection; jenPlatne: Boolean; idPrikaz: integer; nazevOp: string; lokalniConnection: boolean; lokIDcka: TList; {$IFDEF CUSTOM_CTRL_Rootvin} typPrikazu: integer; {$ENDIF} begin result:= nil; lokalniConnection := (AConn = nil); if (lokalniConnection) then begin sqlConnX := TFDConnection.Create(nil); sqlConnX.ConnectionDefName := sqlPoolName; end else sqlConnX := AConn; idPrikaz:= 0; nazevOp:= ''; jenSeznam:= false; ordBy:= 'main.ID'; if (params<>nil) then begin if (params.ContainsKey('seznam')) then if (params.Items['seznam']='1') then jenSeznam:= true; if (params.ContainsKey('ordBy')) then if (params.Items['ordBy']<>'') then ordBy:= params.Items['ordBy']; end; lSQL:= 'SELECT main.ID FROM ' + tblPrKVazby + ' main' + ' INNER JOIN ' + tblKZ + ' kzN ON (kzN.ID=main.nizsi) INNER JOIN ' + tblKZ + ' kzV ON (kzV.ID=main.vyssi)' + ' INNER JOIN ' + tblVPr + ' vpr ON (vpr.ID=main.IDPrikaz)' + ' LEFT JOIN ' + tblPrPost + ' pp ON (pp.IDPrikaz=main.IDPrikaz AND pp.Doklad=dbo.hf_GetPrPDokladForPrKV(main.ID) AND pp.Prednastaveno=1 AND pp.IDOdchylkyDo IS NULL)'; where:= ''; {$IFDEF CUSTOM_CTRL_Rootvin} typPrikazu:= 0; if (params.ContainsKey('typPrikazu')) then TryStrToInt(params.Items['typPrikazu'], typPrikazu); if (params.ContainsKey('nazevOp')) then if (params.Items['nazevOp']<>'') then nazevOp:= params.Items['nazevOp']; {$ENDIF} jenPlatne:= true; if (params<>nil) then begin if (params.ContainsKey('jenplatne')) then if (params.Items['jenplatne']='0') then jenPlatne:= false; if (jenPlatne) then where:= 'main.IDOdchylkyDo IS NULL'; if (nazevOp<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'pp.Nazev LIKE N''%' + nazevOp + '%'''; if (params.ContainsKey('stavPrikazu')) then if (params.Items['stavPrikazu']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'vpr.StavPrikazu=' + params.Items['stavPrikazu']; if (params.ContainsKey('skMat')) then if (params.Items['skMat']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'kzN.SkupZbo=N' + params.Items['skMat'].QuotedString; if (params.ContainsKey('rcMat')) then if (params.Items['rcMat']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'kzN.RegCis=N' + params.Items['rcMat'].QuotedString; if (params.ContainsKey('id')) then if (params.Items['id']<>'') then if (params.Items['id']<>'0') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.ID=' + params.Items['id']; if (params.ContainsKey('idprikaz')) then if (params.Items['idprikaz']<>'') then begin idPrikaz:= params.Items['idprikaz'].ToInteger; where:= where + IfThen(where<>'', ' AND ', '') + 'main.IDPrikaz=' + idPrikaz.ToString; end; end; if (where<>'') then lSQL:= lSQL + ' WHERE ' + where; lSQL:= lSQL + ' ORDER BY ' + ordBy; lSQL:= lSQL.Replace('TabPrKVazby.', 'main.'); if (selMaterialSpec='') then lSQL:= lSQL.Replace('main.', '').Replace('main WHE', ' WHE'); lQry:= TFDQuery.Create(nil); try lokIDcka:= TList.Create; try sqlConnX.Connected := true; lQry.FetchOptions.Mode := fmAll; lQry.Connection := sqlConnX; lQry.Open (lSQL); cnt := lQry.RecordCount; if (cnt>0) then begin try lQry.First; rNo:= 1; while (rNo<=cnt) do begin lQry.RecNo:= rNo; AID:= lQry.FieldByName('ID').AsInteger; lokIDcka.Add (AID); if not(lQry.Active) then lQry.Open; Inc (rNo); end; except on E:Exception do raise EServiceException.Create('Chyba nacitani materialovych pozadavku: ' + E.Message); end; resList:= TObjectList.Create; try for AID in lokIDcka do begin resObj:= self.GetByID (AID, sqlConnX); resList.Add (resObj); end; result:= resList; resList:= nil; finally resList.Free; end; end; except on E:Exception do raise EServiceException.Create(errMaterialy + E.Message); end; finally lokIDcka.Free; lQry.Close; lQry.Free; if (lokalniConnection) then begin sqlConnX.Close; sqlConnX.Free; end; end; end; function TMaterialPrikazuService.GetByIDVPr (idVPr: integer; jenPlatne: boolean=true; AConn: TFDConnection = nil): TObjectList; var lSQL: 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; lSQL := 'SELECT ' + GetTabCols('', tblPrKVazby, 'main') + IfThen(selMaterialSpec<>'', ',' + selMaterialSpec, '') + ' FROM ' + tblPrKVazby + ' main' + ' INNER JOIN ' + tblKZ + ' kzV ON (main.vyssi=kzV.ID) INNER JOIN ' + tblKZ + ' kzN ON (kzN.ID=main.nizsi)'; lSQL := lSQL + IfThen(jenPlatne, ' WHERE main.IDOdchylkyDo IS NULL', ''); lSQL := lSQL + IfThen(lSQL.Contains('WHERE'), ' AND ', ' WHERE') + ' main.IDPrikaz=:ID'; lSQL := lSQL.Replace('TabPrKVazby.', 'main.'); lQry := TFDQuery.Create(nil); try try sqlConnX.Connected := true; lQry.FetchOptions.Mode := fmAll; lQry.Connection := sqlConnX; lQry.Open (lSQL, [idVPr]); result:= lQry.AsObjectList; except on E:Exception do raise EServiceException.Create(errMaterialy + E.Message); end; finally lQry.Close; lQry.Free; if (lokalniConnection) then begin sqlConnX.Close; sqlConnX.Free; end; end; end; function TMaterialPrikazuService.GetMeta: TJSONObject; var lSQL: string; lQry: TFDQuery; sqlConnX: TFDConnection; begin lSQL:= 'SELECT ' + GetTabCols('', tblPrKVazby, 'main') + IfThen(selMaterialSpec<>'', ',' + selMaterialSpec, '') + ' FROM ' + tblPrKVazby + ' main' + ' INNER JOIN ' + tblKZ + ' kzV ON (main.vyssi=kzV.ID) INNER JOIN ' + tblKZ + ' kzN ON (kzN.ID=main.nizsi) WHERE 1=0'; sqlConnX:= TFDConnection.Create(nil); sqlConnX.ConnectionDefName:= sqlPoolName; lQry:= TFDQuery.Create(nil); try sqlConnX.Connected := true; lQry.FetchOptions.Mode:= fmAll; lQry.Connection:= sqlConnX; lQry.Open(lSQL); Result := lQry.MetadataAsJSONObject(); finally lQry.Close; lQry.Free; sqlConnX.Close; sqlConnX.Free; end; end; { TVyrobaVyrCisPrikazService } function TVyrobaVyrCisPrikazService.GetMeta: TJsonObject; var lSQL: string; lQry: TFDQuery; sqlConnX: TFDConnection; begin lSQL:= 'SELECT ' + GetTabCols('', tblPrikazVC, 'main') + IfThen(selVyrCisPrikazSpec<>'', ',' + selVyrCisPrikazSpec, '') + ' FROM ' + tblPrikazVC + ' main WHERE 1=0'; lSQL:= lSQL.Replace('TabVyrCisPrikaz.', 'main.'); if (selVyrCisPrikazSpec='') then lSQL:= lSQL.Replace('main.', ''); sqlConnX:= TFDConnection.Create(nil); sqlConnX.ConnectionDefName:= sqlPoolName; lQry:= TFDQuery.Create(nil); try lQry.FetchOptions.Mode:= fmAll; lQry.Connection:= sqlConnX; lQry.Open(lSQL); result:= lQry.MetadataAsJSONObject(); finally lQry.Close; lQry.Free; sqlConnX.Close; sqlConnX.Free; end; end; function TVyrobaVyrCisPrikazService.GetByID (const AID: integer; params: TDictionary=nil; AConn: TFDConnection = nil): TVyrCisPrikaz; 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; lSQL:= 'SELECT ' + GetTabCols('', tblPrikazVC, 'main') + IfThen(selVyrCisPrikazSpec<>'', ',' + selVyrCisPrikazSpec, '') + ' FROM ' + tblPrikazVC + ' main WHERE main.ID=:ID'; lQry:= TFDQuery.Create(nil); try lQry.FetchOptions.Mode:= fmAll; lQry.Connection:= sqlConnX; lQry.Open (lSQL, [AID]); Result := lQry.AsObject; finally lQry.Close; lQry.Free; if (lokalniConnection) then begin sqlConnX.Close; sqlConnX.Free; end; end; end; function TVyrobaVyrCisPrikazService.GetByParams (params: TDictionary; AConn: TFDConnection = nil): TObjectList; var lSQL, where, ordBy: string; AID, cnt, rNo: integer; resList: TObjectList; resObj: TVyrCisPrikaz; lQry: TFDQuery; sqlConnX: TFDConnection; lokalniConnection: boolean; lokIDcka: TList; begin result:= nil; lokalniConnection := (AConn = nil); if (lokalniConnection) then begin sqlConnX := TFDConnection.Create(nil); sqlConnX.ConnectionDefName := sqlPoolName; end else sqlConnX := AConn; ordBy:= 'main.ID'; if (params<>nil) then if (params.ContainsKey('ordBy')) then if (params.Items['ordBy']<>'') then ordBy:= params.Items['ordBy']; lSQL:= 'SELECT main.ID FROM ' + tblPrikazVC + ' main'; where:= ''; if (params<>nil) then if (params.ContainsKey('idprikaz')) then if (params.Items['idprikaz']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.IDPrikaz=' + params.Items['idprikaz']; if (where<>'') then lSQL:= lSQL + ' WHERE ' + where; lSQL:= lSQL + ' ORDER BY ' + ordBy; lQry:= TFDQuery.Create(nil); try lokIDcka := TList.Create; lQry.FetchOptions.Mode := fmAll; lQry.Connection := sqlConnX; lQry.Open (lSQL); cnt:= lQry.RecordCount; if (cnt>0) then begin try lQry.First; rNo := 1; while (rNo<=cnt) do begin lQry.RecNo := rNo; AID := lQry.FieldByName('ID').AsInteger; lokIDcka.Add (AID); if not(lQry.Active) then lQry.Open; Inc (rNo); end; except on E:Exception do raise EServiceException.Create('Chyba nacitani vyrobnich prikazu: ' + E.Message); end; resList:= TObjectList.Create; try for AID in lokIDcka do begin resObj := self.GetByID (AID, nil, sqlConnX); resList.Add (resObj); end; result := resList; resList := nil; finally resList.Free; end; end; finally lokIDcka.Free; lQry.Close; lQry.Free; if (lokalniConnection) then begin sqlConnX.Close; sqlConnX.Free; end; end; end; { TVyrobniOperaceService } function TVyrobniOperaceService.GetAll (params: TDictionary=nil; jenPlatne: boolean=true; jenZiveVpr: boolean=true; AConn: TFDConnection = nil): TObjectList; var lSQL, errMsg, ordBy: string; jenSeznam: boolean; 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; jenSeznam:= false; ordBy:= 'main.ID'; if (params<>nil) then begin if (params.ContainsKey('seznam')) then if (params.Items['seznam']='1') then jenSeznam:= true; if (params.ContainsKey('ordBy')) then if (params.Items['ordBy']<>'') then ordBy:= params.Items['ordBy']; end; lSQL:= 'SELECT ' + GetTabCols('', tblPrPost, 'main') + IfThen(selOperaceSpec<>'', ',' + selOperaceSpec, '') + ' FROM ' + tblPrPost + ' main WHERE main.IDOdchylkyDo IS NULL'; if (jenZiveVPr) then lSQL:= lSQL + ' AND main.IDPrikaz IN (SELECT ID FROM ' + tblVPr + ' WHERE StavPrikazu<=40)'; lSQL:= lSQL + ' ORDER BY ' + ordBy; lSQL:= lSQL.Replace('TabPrPostup.', 'main.'); if (selOperaceSpec='') then lSQL:= lSQL.Replace('main.', '').Replace('main WHE', ' WHE'); lQry:= TFDQuery.Create(nil); try try sqlConnX.Connected := true; lQry.FetchOptions.Mode := fmAll; lQry.Connection := sqlConnX; lQry.Open (lSQL); lQry.First; result := lQry.AsObjectList; lQry.Close; except on E:Exception do raise EServiceException.Create(errPostupy + E.Message); end; finally lQry.Free; if (lokalniConnection) then begin sqlConnX.Close; sqlConnX.Free; end; end; end; function TVyrobniOperaceService.GetByID (const AID: Integer; params: TDictionary=nil; AConn: TFDConnection = nil): TVyrobniOperace; var lSQL: string; lQry: TFDQuery; sqlConnX: TFDConnection; extInfoStr: string; 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', tblPrPostE)) then begin lSQL:= 'SELECT ' + GetTabCols ('dbo', tblPrPostE, '', true, 'ID') + ' FROM ' + tblPrPostE + ' WHERE ID=:ID'; lQry:= TFDQuery.Create (nil); try lQry.FetchOptions.Mode:= fmAll; lQry.Connection := sqlConnX; lQry.Open (lSQL, [AID]); if (lQry.RecordCount>0) then extInfoStr := lQry.AsJSONObject; finally lQry.Free; end; end; lSQL:= 'SELECT ' + GetTabCols('', tblPrPost, 'main') + IfThen(selOperaceSpec<>'', ',' + selOperaceSpec, '') + ' FROM ' + tblPrPost + ' main WHERE main.IDOdchylkyDo IS NULL AND main.ID=:ID'; lSQL:= lSQL.Replace('TabPrPostup.', 'main.'); if (selOperaceSpec='') then lSQL:= lSQL.Replace('main.', '').Replace('main WHE', ' WHE'); lQry:= TFDQuery.Create(nil); try sqlConnX.Connected := true; lQry.FetchOptions.Mode := fmAll; lQry.Connection := sqlConnX; lQry.Open (lSQL, [AID]); lQry.First; try result:= lQry.AsObject; if (extInfoStr<>'') then result.ExtInfo:= extInfoStr; except on E:Exception do raise EServiceException.Create('Vyrobni operace s ID ' + AID.ToString + ' nebyla nalezena - ' + E.Message); end; finally lQry.Free; if (lokalniConnection) then begin sqlConnX.Close; sqlConnX.Free; end; end; end; function TVyrobniOperaceService.GetByParams (params: TDictionary; AConn: TFDConnection = nil): TObjectList; var lSQL, where, errMsg, ordBy: string; jenSeznam: boolean; 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; jenSeznam:= false; ordBy:= 'main.ID'; if (params<>nil) then begin if (params.ContainsKey('seznam')) then if (params.Items['seznam']='1') then jenSeznam:= true; if (params.ContainsKey('ordBy')) then if (params.Items['ordBy']<>'') then ordBy:= params.Items['ordBy']; end; lSQL:= 'SELECT ' + GetTabCols('', tblPrPost, 'main') + IfThen(selOperaceSpec<>'', ',' + selOperaceSpec, '') + ' FROM ' + tblPrPost + ' main'; where:= 'main.IDOdchylkyDo IS NULL'; if (params<>nil) then begin if (params.ContainsKey('id')) then if (params.Items['id']<>'') then if (params.Items['id']<>'0') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.ID=' + params.Items['id']; if (params.ContainsKey('idprikaz')) then if (params.Items['idprikaz']<>'') then if (params.Items['idprikaz']<>'0') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.IDPrikaz=' + params.Items['idprikaz']; if (params.ContainsKey('stavprikazu')) then if (params.Items['stavprikazu']<>'') then if (params.Items['stavprikazu']<>'0') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.IDPrikaz IN (SELECT ID FROM dbo.TabPrikaz WHERE StavPrikazu=' + params.Items['stavprikazu'] + ')'; if (params.ContainsKey('idstroj')) then if (params.Items['idstroj']<>'') then if (params.Items['idstroj']<>'0') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.IDStroje=' + params.Items['idstroj']; if (params.ContainsKey('operace')) then if (params.Items['operace']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'LTRIM(RTRIM(main.Operace))=N' + params.Items['operace'].QuotedString; if (params.ContainsKey('radaprikazu')) then if (params.Items['radaprikazu']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.IDPrikaz IN (SELECT ID FROM ' + tblVPr + ' WHERE Rada=N' + params.Items['radaprikazu'].QuotedString + ' AND StavPrikazu<=40)'; if (params.ContainsKey('barcode')) then if (params.Items['barcode']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.BarCode=N' + params.Items['barcode'].QuotedString; if (params.ContainsKey('jenKoop')) then if (params.Items['jenKoop']='1') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.typ=2'; if (params.ContainsKey('jenZiveVPr')) then if (params.Items['jenZiveVPr']='1') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.IDPrikaz IN (SELECT ID FROM ' + tblPrikaz + ' WHERE StavPrikazu IN (20,30,40))'; end; if (where<>'') then lSQL:= lSQL + ' WHERE ' + where; lSQL:= lSQL + ' ORDER BY ' + ordBy; lSQL:= lSQL.Replace('TabPrPostup.', 'main.'); if (selOperaceSpec='') then lSQL:= lSQL.Replace('main.', '').Replace('main WHE', ' WHE'); lQry:= TFDQuery.Create(nil); try try sqlConnX.Connected := true; lQry.FetchOptions.Mode := fmAll; lQry.Connection := sqlConnX; lQry.Open (lSQL); result := lQry.AsObjectList; lQry.Close; except on E:Exception do raise EServiceException.Create (errPostupy + E.Message); end; finally lQry.Free; if (lokalniConnection) then begin sqlConnX.Close; sqlConnX.Free; end; end; end; function TVyrobniOperaceService.GetKPsByID (const AID: Integer): TObjectList; var lSQL: string; lQry: TFDQuery; begin result:= nil; lSQL:= sqlSelKPs + ' FROM ' + tblKPLHlav + ' WHERE IDOperace=:ID -- AND Kategorie IN (SELECT Cislo FROM ' + tblKategKJ + ' WHERE QMSAgenda=12)'; lQry:= TFDQuery.Create(nil); lQry.Connection:= FDM.sqlConn; try lQry.Open(lSQL, [AID]); result:= lQry.AsObjectList; finally lQry.Free; end; end; function TVyrobniOperaceService.GetByIDPrikazu (const AID: Integer; AConn: TFDConnection = nil): TObjectList; var lSQL: 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; lSQL:= 'SELECT ' + GetTabCols('', tblPrPost, 'main') + ' FROM ' + tblPrPost + ' main WHERE main.IDOdchylkyDo IS NULL AND main.IDPrikaz=:ID'; lSQL:= lSQL.Replace('TabPrPostup.', 'main.'); lQry:= TFDQuery.Create(nil); try sqlConnX.Connected := true; lQry.FetchOptions.Mode := fmAll; lQry.Connection := sqlConnX; lQry.Open (lSQL, [AID]); result := lQry.AsObjectList; finally lQry.Free; end; if (lokalniConnection) then sqlConnX.Free; end; function TVyrobniOperaceService.GetByBarCode (const ABarCode: string; AConn: TFDConnection = nil): TVyrobniOperace; var lSQL: 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; lSQL:= 'SELECT ' + GetTabCols('', tblPrPost, 'main') + ' FROM ' + tblPrPost + ' main WHERE main.IDOdchylkyDo IS NULL AND main.BarCode=:BC'; // + ABarCode.QuotedString; lSQL:= lSQL.Replace('TabPrPostup.', 'main.'); lQry:= TFDQuery.Create(nil); try sqlConnX.Connected := true; lQry.FetchOptions.Mode:= fmAll; lQry.Connection:= sqlConnX; lQry.Open(lSQL, [ABarCode]); lQry.First; if not(lQry.EOF) then result:= lQry.AsObject else raise EServiceException.Create('VĂ˝robnĂ­ operace s čárovĂ˝m kĂłdem ' + ABarCode + ' nebyla nalezena.'); finally lQry.Free; end; if (lokalniConnection) then sqlConnX.Free; end; function TVyrobniOperaceService.GetMeta: TJSONObject; var lSQL: string; lQry: TFDQuery; begin lSQL:= 'SELECT ' + GetTabCols('', tblPrPost, 'main') + ' FROM ' + tblPrPost + ' main WHERE 1=0'; lSQL:= lSQL.Replace('TabPrPostup.', 'main.'); lQry:= TFDQuery.Create(nil); lQry.Connection:= FDM.sqlConn; lQry.Open(lSQL); try result:= lQry.MetadataAsJSONObject(); finally lQry.Free; end; end; procedure TVyrobniOperaceService.ZapisDZTasku (jsonData: string; var respString: string); var lSQL, taskStr, strTmp, strTmp2, code, rs, xxx: string; j, t, a, v, xx, st: JsonDataObjects.TJSONObject; subs: TJSONArray; i, ii, taskDZId, taskId, intTemp: integer; ksOdv: Extended; jeOld: boolean; sqlConnX: TFDConnection; lQry, lQry2: TFDQuery; begin respString:= '['; if (jsonData<>'') then begin sqlConnX:= TFDConnection.Create(nil); sqlConnX.ConnectionDefName:= sqlPoolName; if (FDM.SQLTableExists(tblPrijataJsonData)) then begin lQry:= TFDQuery.Create(nil); try lSQL:= 'INSERT ' + tblPrijataJsonData + ' (Blokovano, JSONData) SELECT 1, N' + jsonData.QuotedString; lQry.FetchOptions.Mode:= fmAll; lQry.Connection:= sqlConnX; lQry.ExecSQL(lSQL); finally lQry.Free; end; end; if (FDM.SQLTableExists(tblDZTasky)) and (FDM.SQLTableExists(tblDZSubtasky)) then begin j:= TJsonObject.Parse(jsonData) as JsonDataObjects.TJsonObject; try if (j<>nil) then if (j.Contains('tasks')) then begin for i:=0 to j['tasks'].Count-1 do begin try t:= j['tasks'].Items[i]; taskDZId:= t.I['id']; // j['tasks'].Items[i].I['id']; taskStr:= j['tasks'].Items[i].ObjectValue.ToString; jeOld:= false; strTmp:= j['tasks'].Items[i].S['startedAt']; if (strTmp<>'') then strTmp:= MidStr(strTmp, 9, 2) + '.' + MidStr(strTmp, 6, 2) + '.' + LeftStr(strTmp, 4) + ' ' + MidStr(strTmp, 12, 8); strTmp2:= j['tasks'].Items[i].S['finishedAt']; if (strTmp2<>'') then strTmp2:= MidStr(strTmp2, 9, 2) + '.' + MidStr(strTmp2, 6, 2) + '.' + LeftStr(strTmp2, 4) + ' ' + MidStr(strTmp2, 12, 8); intTemp:= 0; lSQL:= 'SELECT COUNT(ID) AS Cnt FROM ' + tblDZTasky + ' WHERE IdDataZoneTaskID=' + taskDZId.ToString; lQry:= TFDQuery.Create(nil); lQry.FetchOptions.Mode:= fmAll; lQry.Connection:= sqlConnX; lQry.Open(lSQL); try if (lQry.RecordCount>0) then intTemp:= lQry.FieldByName('Cnt').AsInteger; finally lQry.Close; end; if (intTemp=1) then jeOld:= true; lSQL:= 'IF OBJECT_ID(N' + tblDZTasky.QuotedString + ', N''U'') IS NOT NULL' + CRLF + 'IF NOT EXISTS (SELECT 1 FROM ' + tblDZTasky + ' WHERE IdDataZoneTaskID=' + taskDZId.ToString + ')' + CRLF + 'INSERT ' + tblDZTasky + ' (IdDataZoneTaskID, Kooperace, DeviceID, Obsah, StatusText, DatumStart, DatumKonec, Zamestnanec, KodPracoviste, BarCode)'; lSQL:= lSQL + ' SELECT ' + taskDZId.ToString + ', ' + t.B['kooperace'].ToString(false) + ', N'+ IfThen(t.S['machine']='', 'ULL', t.S['machine'].QuotedString); lSQL:= lSQL + ', CONVERT(varchar(max), N' + taskStr.QuotedString + ')' + ', N' + t.S['status'].QuotedString; lSQL:= lSQL + ', ' + IfThen(strTmp<>'',' CONVERT(datetime, N' + strTmp.QuotedString + ', 104)', 'NULL'); lSQL:= lSQL + ', ' + IfThen(strTmp2<>'',' CONVERT(datetime, N' + strTmp2.QuotedString + ', 104)', 'NULL'); lSQL:= lSQL + ', ' + IfThen(t.Contains('worker'), t['worker'].S['number'], 'NULL'); lSQL:= lSQL + ', ' + IfThen(t.Contains('workplaceCode'), 'N' + t.S['workplaceCode'].QuotedString, 'NULL'); xxx:= ''; if (t.Types['code']=jdtString) then xxx:= t.S['code'] else if (t.Types['code']=jdtObject) then xxx:= ''; lSQL:= lSQL + ', ' + IfThen(xxx<>'', xxx.QuotedString, 'NULL'); sqlConnX.ExecSQL(lSQL); if (taskStr.Contains('"kooperace":true')) then sqlConnX.ExecSQL('UPDATE ' + tblDZTasky + ' SET Kooperace=1 WHERE IdDataZoneTaskID=' + taskDZId.ToString); intTemp:= 0; lSQL:= 'SELECT COUNT(ID) AS Cnt FROM ' + tblDZTasky + ' WHERE IdDataZoneTaskID=' + taskDZId.ToString; lQry.Open(lSQL); if (lQry.RecordCount>0) then intTemp:= lQry.FieldByName('Cnt').AsInteger; lQry.Close; if (intTemp=0) or (intTemp=1) then begin taskId:= 0; lSQL:= 'SELECT ID FROM ' + tblDZTasky + ' WHERE IdDataZoneTaskID=' + taskDZId.ToString; lQry.Open(lSQL); if (lQry.RecordCount>0) then taskId:= lQry.FieldByName('ID').AsInteger; lQry.Close; if (taskId>0) then begin if (t.Contains('code')) then begin code:= ''; if (t.Types['code']=jdtString) then code:= t.S['code'] else if (t.Types['code']=jdtObject) then code:= ''; // code:= t.S['code']; if (code<>'') then begin lSQL:= 'UPDATE t SET t.IDPrikaz=p.IDPrikaz, t.IDPrPostup=p.ID, t.DokladPrP=p.Doklad, t.AltPrP=p.Alt FROM ' + tblDZTasky + ' t' + ', ' + tblPrPost + ' p WHERE p.IDOdchylkyDo IS NULL AND p.BarCode=N' + code.QuotedString + ' AND t.ID=' + taskId.ToString + CRLF +'IF (@@ROWCOUNT=0) UPDATE ' + tblDZTasky + ' SET PosledniChyba=N''Podle barcode nebyla dohledana operace'' WHERE ID=' + taskId.ToString; // FDM.sqlConn.ExecSQL(lSQL); end; end; if (t.Contains('values')) then begin v:= t.O['values']; if (v<>nil) then begin lSQL:= 'UPDATE ' + tblDZTasky + ' SET kusy_odv=' + v.F['kusy_odv'].ToString + ', Kusy_zmet_opr=' + IfThen(v.Contains('kusy_zmet'), v.F['kusy_zmet'].ToString, '0') + ', Kusy_zmet_neopr=' + IfThen(v.Contains('kusy_zmet_neopr'), v.F['kusy_zmet_neopr'].ToString, '0') + ' WHERE ID=' + taskId.ToString; // FDM.sqlConn.ExecSQL(lSQL); end; end; if (t.Contains('subtask')) then if not(t['subtask'].IsNull) then begin subs:= t['subtask'].ArrayValue; for ii:=0 to subs.Count-1 do begin a:= subs.Items[ii].ObjectValue; code:= a['code']; strTmp:= j['tasks'].Items[i].S['startedAt']; if (strTmp<>'') then strTmp:= MidStr(strTmp, 9, 2) + '.' + MidStr(strTmp, 6, 2) + '.' + LeftStr(strTmp, 4) + ' ' + MidStr(strTmp, 12, 8); strTmp2:= j['tasks'].Items[i].S['finishedAt']; if (strTmp2<>'') then strTmp2:= MidStr(strTmp2, 9, 2) + '.' + MidStr(strTmp2, 6, 2) + '.' + LeftStr(strTmp2, 4) + ' ' + MidStr(strTmp2, 12, 8); ksOdv:= 0; if (a.Contains('values')) then if (a['values'].ObjectValue.Contains('kusy_odv')) then ksOdv:= a['values']['kusy_odv'].FloatValue; if (code<>'') then begin lSQL:= 'IF OBJECT_ID(N' + tblDZSubtasky.QuotedString + ', N''U'') IS NOT NULL' + CRLF + 'IF NOT EXISTS(SELECT 1 FROM ' + tblDZSubtasky + ' WHERE BarcodeOperace=N' + code.QuotedString + ' AND IdDataZoneTaskID=' + taskDZId.ToString + ')' + CRLF + 'INSERT ' + tblDZSubtasky + ' (IDTask, IdDataZoneTaskID, DatumStart, DatumKonec, Kusy_odv, BarcodeOperace) SELECT ' + taskId.ToString + ', ' + taskDZId.ToString + ', ' + IfThen(strTmp<>'',' CONVERT(datetime, N' + strTmp.QuotedString + ', 104)', 'NULL') + ', ' + IfThen(strTmp2<>'',' CONVERT(datetime, N' + strTmp2.QuotedString + ', 104)', 'NULL') + ', ' + ksOdv.ToString + ', N' + code.QuotedString; sqlConnX.ExecSQL(lSQL); end; end; end; end; respString:= respString + '{"id":"' + taskDZId.ToString + '", "resp":"' + IfThen(jeOld, 'OLD', 'NEW') + '"}'; end; except on E:Exception do respString:= respString + '{"id":"' + taskDZId.ToString + '", "resp":"NOT OK", "errMsg": "' + E.Message + '"}'; end; end; end; finally respString:= respString + ']'; j.Free; end; end; if (lQry<>nil) then lQry.Free; sqlConnX.Close; sqlConnX.Free; end; end; function TVyrobniOperaceService.GetDZTaskyNeevidovane (odDatum, doDatum: string): TObjectList; var lSQL: string; datOd, datDo: TDateTime; lQry: TFDQuery; sqlConnX: TFDConnection; begin result:= nil; lSQL:= 'SELECT ID, IdDataZoneTaskID, DeviceID, PosledniChyba, Barcode FROM ' + tblDZTasky + ' WHERE (LTRIM(RTRIM(ISNULL(PosledniChyba,N'''')))<>N'''' OR DatZpracovani IS NULL)'; 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); 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:= Now end else datDo:= Now; lSQL:= lSQL + ' AND DatPorizeni>='; if (odDatum<>'') then lSQL:= lSQL + 'CONVERT(datetime, N' + FormatDateTime('dd.mm.yyyy hh:nn:ss', datOd).QuotedString + ',104)' else lSQL:= lSQL + 'DATEADD(day, -1, GETDATE())'; if (doDatum<>'') then lSQL:= lSQL + ' AND DatPorizeni<=CONVERT(datetime, N' + FormatDateTime('dd.mm.yyyy hh:nn:ss', datDo).QuotedString + ',104)'; lSQL:= lSQL + ' ORDER BY ID'; lSQL:= lSQL.Replace('--', '').Replace(';', ''); sqlConnX:= TFDConnection.Create(nil); sqlConnX.ConnectionDefName:= sqlPoolName; lQry:= TFDQuery.Create(nil); try lQry.FetchOptions.Mode:= fmAll; lQry.Connection:= sqlConnX; lQry.Open(lSQL); lQry.First; result:= lQry.AsObjectList; finally lQry.Free; end; sqlConnX.Close; sqlConnX.Free; end; procedure TVyrobniOperaceService.Update (AOperace: TVyrobniOperace); var lSQL: string; updCmd: TFDCustomCommand; begin // AOperace.CheckUpdate; { updCmd:= FDM.sqlUpdate.Commands[arUpdate]; TFireDACUtils.ObjectToParameters(updCmd.Params, AOperace, 'NEW_'); updCmd.ParamByName('OLD_ID').AsInteger := AOperace.ID; updCmd.Execute; if (updCmd.RowsAffected<>1) then raise Exception.Create('Article not found'); } end; { TTPVPracovisteService } function TTPVPracovisteService.GetByID (const AID: integer): TTPVPracoviste; var lSQL: string; lQry: TFDQuery; sqlConnX: TFDConnection; begin result := nil; lSQL := 'SELECT ' + GetTabCols('', tblCPrac, 'main') + ' FROM ' + tblCPrac + ' main WHERE main.ID=:ID'; lSQL := lSQL.Replace('TabCPrac.', 'main.'); sqlConnX := TFDConnection.Create(nil); sqlConnX.ConnectionDefName := sqlPoolName; lQry := TFDQuery.Create(nil); try lQry.FetchOptions.Mode := fmAll; lQry.Connection := sqlConnX; lQry.Open (lSQL, [AID]); result := lQry.AsObject; lQry.Close; finally lQry.Free; end; sqlConnX.Close; sqlConnX.Free; end; function TTPVPracovisteService.GetAll (params: TDictionary=nil): TObjectList; var lSQL, errMsg, ordBy: string; jenSeznam: boolean; lQry: TFDQuery; sqlConnX: TFDConnection; begin result:= nil; jenSeznam:= false; ordBy:= 'main.ID'; if (params<>nil) then begin if (params.ContainsKey('seznam')) then if (params.Items['seznam']='1') then jenSeznam:= true; if (params.ContainsKey('ordBy')) then if (params.Items['ordBy']<>'') then ordBy:= params.Items['ordBy']; end; lSQL:= 'SELECT ' + GetTabCols('', tblCPrac, 'main') + ' FROM ' + tblCPrac + ' main'; lSQL:= lSQL + ' ORDER BY ' + ordBy; lSQL:= lSQL.Replace('TabCPrac.', 'main.'); 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; lQry.Close; finally lQry.Free; sqlConnX.Close; sqlConnX.Free; end; end; function TTPVPracovisteService.GetMeta: TJSONObject; var lSQL: string; lQry: TFDQuery; sqlConnX: TFDConnection; begin lSQL:= 'SELECT ' + GetTabCols('', tblCPrac, 'main') + ' FROM ' + tblCPrac + ' main WHERE 1=0'; lSQL:= lSQL.Replace('TabCPrac.', 'main.'); sqlConnX:= TFDConnection.Create(nil); sqlConnX.ConnectionDefName:= sqlPoolName; lQry:= TFDQuery.Create(nil); try lQry.FetchOptions.Mode:= fmAll; lQry.Connection:= sqlConnX; lQry.Open(lSQL); Result := lQry.MetadataAsJSONObject(); lQry.Close; finally lQry.Free; sqlConnX.Close; sqlConnX.Free; end; end; function TTPVPracovisteService.GetFrontaPrace (const AID: Integer): TObjectList; var lSQL: string; lQry: TFDQuery; sqlConnX: TFDConnection; begin result:= nil; lSQL:= 'SELECT ' + GetTabCols('', tblPrPost, 'main') + selOperaceSpec + ' FROM ' + tblPrPost + ' main WHERE main.Pracoviste=:ID AND main.IDOdchylkyDo IS NULL' + ' AND main.Splneno=0 ORDER BY main.ID'; lSQL:= lSQL.Replace('TabCPrac.', 'main.'); sqlConnX:= TFDConnection.Create(nil); sqlConnX.ConnectionDefName:= sqlPoolName; lQry:= TFDQuery.Create(nil); try lQry.FetchOptions.Mode := fmAll; lQry.Connection := sqlConnX; lQry.Open (lSQL, [AID]); result:= lQry.AsObjectList; lQry.Close; except on E:Exception do raise EServiceException.Create(errPostupy + E.Message); end; lQry.Free; sqlConnX.Close; sqlConnX.Free; end; { TTPVStroj } function TTPVStrojService.GetMeta: TJSONObject; var lSQL: string; lQry: TFDQuery; begin lSQL:= 'SELECT ' + GetTabCols('', tblCisStroj, 'main') + IfThen(selTPVStroj<>'', ',' + selTPVStroj, '') + ' FROM ' + tblCisStroj + ' main'; lSQL:= lSQL.Replace('TabCisStroju.', 'main.'); lSQL:= lSQL + ' INNER JOIN ' + tblCPrac + ' p ON (p.ID=main.IDPrac)'; lSQL:= lSQL + ' WHERE 1=0'; lQry:= TFDQuery.Create(nil); lQry.Connection:= FDM.sqlConn; try lQry.Open(lSQL); Result := lQry.MetadataAsJSONObject(); lQry.Close; finally lQry.Free; end; end; function TTPVStrojService.GetAll (params: TDictionary=nil): TObjectList; var lSQL, errMsg, ordBy: string; jenSeznam: boolean; lQry: TFDQuery; sqlConnX: TFDConnection; begin result:= nil; jenSeznam:= false; ordBy:= 'main.Kod, p.pracoviste'; if (params<>nil) then begin if (params.ContainsKey('seznam')) then if (params.Items['seznam']='1') then jenSeznam:= true; if (params.ContainsKey('ordBy')) then if (params.Items['ordBy']<>'') then ordBy:= params.Items['ordBy']; end; lSQL:= 'SELECT ' + GetTabCols('', tblCisStroj, 'main') + IfThen(selTPVStroj<>'', ',' + selTPVStroj, '') + ' FROM ' + tblCisStroj + ' main'; lSQL:= lSQL.Replace('TabCisStroju.', 'main.'); lSQL:= lSQL + ' INNER JOIN ' + tblCPrac + ' p ON (p.ID=main.IDPrac)'; lSQL:= lSQL + ' ORDER BY ' + ordBy; sqlConnX:= TFDConnection.Create(nil); sqlConnX.ConnectionDefName:= sqlPoolName; lQry:= TFDQuery.Create(nil); try lQry.Connection:= sqlConnX; lQry.Open(lSQL); result:= lQry.AsObjectList; finally lQry.Free; end; sqlConnX.Close; sqlConnX.Free; end; function TTPVStrojService.GetByID (const AID: integer): TTPVStroj; var lSQL: string; lQry: TFDQuery; sqlConnX: TFDConnection; begin result:= nil; lSQL:= 'SELECT ' + GetTabCols('', tblCisStroj, 'main') + IfThen(selTPVStroj<>'', ',' + selTPVStroj, '') + ' FROM ' + tblCisStroj + ' main WHERE main.ID=:ID'; lSQL:= lSQL.Replace('TabCisStroju.', 'main.'); lSQL:= lSQL + ' INNER JOIN ' + tblCPrac + ' p ON (p.ID=main.IDPrac)'; sqlConnX:= TFDConnection.Create(nil); sqlConnX.ConnectionDefName:= sqlPoolName; lQry:= TFDQuery.Create(nil); try lQry.Connection:= sqlConnX; lQry.Open(lSQL, [AID]); result:= lQry.AsObject; finally lQry.Free; end; sqlConnX.Close; sqlConnX.Free; end; function TTPVStrojService.GetByParams (params: TDictionary): TObjectList; var lSQL, where, errMsg, ordBy: string; jenSeznam: boolean; lQry: TFDQuery; sqlConnX: TFDConnection; begin result:= nil; jenSeznam:= false; ordBy:= 'main.Kod, p.pracoviste'; if (params<>nil) then begin if (params.ContainsKey('seznam')) then if (params.Items['seznam']='1') then jenSeznam:= true; if (params.ContainsKey('ordBy')) then if (params.Items['ordBy']<>'') then ordBy:= params.Items['ordBy']; end; lSQL:= 'SELECT ' + GetTabCols('', tblCisStroj, 'main') + IfThen(selTPVStroj<>'', ',' + selTPVStroj, '') + ' FROM ' + tblCisStroj + ' main'; lSQL:= lSQL.Replace('TabCisStroju.', 'main.'); lSQL:= lSQL + ' INNER JOIN ' + tblCPrac + ' p ON (p.ID=main.IDPrac)'; where:= ''; if (params<>nil) then begin if (params.ContainsKey('id')) then if (params.Items['id']<>'') then if (params.Items['id']<>'0') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.ID=' + params.Items['id']; if (params.ContainsKey('idprac')) then if (params.Items['idprac']<>'') then if (params.Items['idprac']<>'0') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.IDPrac=' + params.Items['idprac']; if (params.ContainsKey('prac')) then if (params.Items['prac']<>'') then if (params.Items['prac']<>'0') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.IDPrac=' + params.Items['prac']; end; if (where<>'') then lSQL:= lSQL + ' WHERE ' + where; lSQL:= lSQL + ' ORDER BY ' + ordBy; sqlConnX:= TFDConnection.Create(nil); sqlConnX.ConnectionDefName:= sqlPoolName; lQry:= TFDQuery.Create(nil); try lQry.Connection:= sqlConnX; lQry.Open(lSQL); result:= lQry.AsObjectList; finally lQry.Free; end; sqlConnX.Close; sqlConnX.Free; end; { TQMSKontrolniPostupyService } function TQMSKontrolniPostupyService.GetAll (params: TDictionary=nil): TObjectList; var lSQL, errMsg, ordBy: string; jenSeznam: boolean; lQry: TFDQuery; sqlConnX: TFDConnection; begin result:= nil; jenSeznam:= false; ordBy:= 'ID'; if (params<>nil) then begin if (params.ContainsKey('seznam')) then if (params.Items['seznam']='1') then jenSeznam:= true; if (params.ContainsKey('ordBy')) then if (params.Items['ordBy']<>'') then ordBy:= params.Items['ordBy']; end; lSQL:= sqlSelKPs + ' FROM ' + tblKPLHlav + ' WHERE Kategorie IN (SELECT Cislo FROM ' + tblKategKJ + ' WHERE QMSAgenda=12)'; lSQL:= lSQL + ' ORDER BY ' + ordBy; sqlConnX:= TFDConnection.Create(nil); sqlConnX.ConnectionDefName:= sqlPoolName; lQry:= TFDQuery.Create(nil); try lQry.Connection:= sqlConnX; lQry.Open(lSQL); result:= lQry.AsObjectList; finally lQry.Free; end; sqlConnX.Close; sqlConnX.Free; end; function TQMSKontrolniPostupyService.GetByID (const AID: Integer): TQMSKontrolniPostup; var lSQL: string; lQry: TFDQuery; sqlConnX: TFDConnection; begin result:= nil; lSQL:= sqlSelKPs + ' FROM ' + tblKPLHlav + ' WHERE ID=:ID'; sqlConnX:= TFDConnection.Create(nil); sqlConnX.ConnectionDefName:= sqlPoolName; lQry:= TFDQuery.Create(nil); try lQry.Connection:= sqlConnX; lQry.Open(lSQL, [AID]); result:= lQry.AsObject; finally lQry.Free; end; sqlConnX.Close; sqlConnX.Free; end; function TQMSKontrolniPostupyService.GetListByIDPostup (const AID: Integer): TObjectList; var lSQL: string; lQry: TFDQuery; sqlConnX: TFDConnection; begin result:= nil; lSQL:= sqlSelKPs + ' FROM ' + tblKPLHlav + ' WHERE IDOperace=:ID ORDER BY PoradoveCislo'; sqlConnX:= TFDConnection.Create(nil); sqlConnX.ConnectionDefName:= sqlPoolName; lQry:= TFDQuery.Create(nil); try lQry.Connection:= sqlConnX; lQry.Open(lSQL, [AID]); result:= lQry.AsObjectList; finally lQry.Free; end; sqlConnX.Close; sqlConnX.Free; end; function TQMSKontrolniPostupyService.GetMeta: TJSONObject; var lSQL: string; lQry: TFDQuery; begin lSQL:= sqlSelKPParams + ' FROM ' + tblKPLParams + ' WHERE (1=0)'; lQry:= TFDQuery.Create(nil); lQry.Connection:= FDM.sqlConn; try lQry.Open(lSQL); Result := lQry.MetadataAsJSONObject(); finally lQry.Free; end; end; function TQMSKontrolniPostupyService.GetParamsByIDHlav (const AID: Integer): TObjectList; var lSQL: string; lQry: TFDQuery; sqlConnX: TFDConnection; begin result:= nil; lSQL:= sqlSelKPParams + ' FROM ' + tblKPLParams + ' WHERE IDKPLHlav=:ID ORDER BY Poradi'; sqlConnX:= TFDConnection.Create(nil); sqlConnX.ConnectionDefName:= sqlPoolName; lQry:= TFDQuery.Create(nil); try lQry.Connection:= sqlConnX; lQry.Open(lSQL, [AID]); result:= lQry.AsObjectList; finally lQry.Free; end; sqlConnX.Close; sqlConnX.Free; end; { TVyrobaEvidRozpracOperaciOperaceService } function TVyrobaEvidRozpracOperaciOperaceService.GetByParams (params: TDictionary): TObjectList; var lSQL, where, extInfoStr: string; AID, cnt, rNo: Integer; lQry: TFDQuery; sqlConnX: TFDConnection; retOp: TEvidRozpracOperaceOperace; lsRetOp: TObjectList; incOper: boolean; begin result:= nil; sqlConnX:= TFDConnection.Create(nil); sqlConnX.ConnectionDefName:= sqlPoolName; lSQL:= 'SELECT ID FROM ' + tblRozpracOperR; where:= ''; incOper:= true; if (params<>nil) then begin if (params.ContainsKey('vcetneOper')) then if (params.Items['vcetneOper']='0') then incOper:= false; if (params.ContainsKey('id')) then if (params.Items['id']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'ID=' + params.Items['id']; if (params.ContainsKey('idEvodROp')) then if (params.Items['idEvodROp']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'IDEvidRozpracOper=' + params.Items['idEvodROp']; end; if (where<>'') then lSQL:= lSQL + ' WHERE ' + where; lSQL:= lSQL + ' ORDER BY ID'; lQry:= TFDQuery.Create(nil); try lQry.Connection:= sqlConnX; lQry.Open(lSQL); cnt:= lQry.RecordCount; lsRetOp:= TObjectList.Create; try lQry.First; rNo:= 1; while (rNo<=cnt) do begin lQry.RecNo:= rNo; AID:= lQry.FieldByName('ID').AsInteger; retOp:= self.GetByID (AID, incOper); lsRetOp.Add(retOp); if not(lQry.Active) then lQry.Open; Inc (rNo); end; Result:= lsRetOp; except on E:Exception do raise EServiceException.Create('Chyba nacitani operaci Evidence rozpracovanych operaci: ' + E.Message); end; finally lQry.Free; sqlConnX.Close; sqlConnX.Free; end; end; function TVyrobaEvidRozpracOperaciOperaceService.GetByID (const AID: integer; incOper: boolean; params: TDictionary=nil; AConn: TFDConnection = nil): TEvidRozpracOperaceOperace; var lSQL, extInfoStr: string; lQry: TFDQuery; sqlConnX: TFDConnection; lokalniConnection: boolean; begin result := nil; lokalniConnection := (AConn = nil); if (AID>0) then begin if (lokalniConnection) then begin sqlConnX := TFDConnection.Create(nil); sqlConnX.ConnectionDefName := sqlPoolName; end else sqlConnX := AConn; extInfoStr := ''; if (SQLTableExists ('dbo', tblRozpracOperRE)) then begin lSQL := 'SELECT ' + GetTabCols ('dbo', tblRozpracOperRE, '', true, 'ID') + ' FROM ' + tblRozpracOperRE + ' 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; lSQL := 'SELECT ' + GetTabCols('', tblRozpracOperR, 'main') + IfThen(selNazevOperaceEvidROpR<>'', ',' + selNazevOperaceEvidROpR, ''); lSQL := lSQL + ' FROM ' + tblRozpracOperR + ' main WHERE main.ID=:ID'; lQry := TFDQuery.Create(nil); try lQry.Connection := sqlConnX; lQry.Open (lSQL, [AID]); result := lQry.AsObject; if (extInfoStr<>'') then result.ExtInfo := ExtInfoStr; finally lQry.Free; end; if (lokalniConnection) then begin sqlConnX.Close; sqlConnX.Free; end; end; end; function TVyrobaEvidRozpracOperaciOperaceService.GetMeta; var lSQL: string; lQry: TFDQuery; begin lSQL:= 'SELECT ' + GetTabCols('', tblRozpracOperR, '') + ' FROM ' + tblRozpracOperR + ' WHERE (1=0)'; lQry:= TFDQuery.Create(nil); lQry.Connection:= FDM.sqlConn; try lQry.Open(lSQL); Result := lQry.MetadataAsJSONObject(); finally lQry.Free; end; end; { TVyrobaEvidRozpracOperaciService } function TVyrobaEvidRozpracOperaciService.GetByParams (params: TDictionary; AConn: TFDConnection = nil): TObjectList; var lSQL, where, customSel, datum, odDatum, doDatum: string; AID, cnt, rNo: integer; d, datOd, datDo: TDateTime; incOper: boolean; resList: TObjectList; resObj: TEvidRozpracOperace; 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; customSel:= ''; where:= ''; lSQL:= 'SELECT ' + GetTabCols('', tblRozpracOper, 'main') + IfThen(selEvidRozpOpSpec<>'', ',' + selEvidRozpOpSpec, ''); lSQL:= lSQL + IfThen(customSel<>'', ',' + customSel, '') + ' FROM ' + tblRozpracOper + ' main'; incOper:= true; odDatum:= ''; doDatum:= ''; if (params<>nil) then begin if (params.ContainsKey('vcetneOper')) then if (params.Items['vcetneOper']='0') then incOper:= false; if (params.ContainsKey('id')) then if (params.Items['id']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.ID=' + params.Items['id']; if (params.ContainsKey('idPrikaz')) then if (params.Items['idPrikaz']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.ID IN (SELECT IDEvidRozpracOper FROM ' + tblRozpracOperR + ' WHERE IDPrikaz=' + params.Items['idPrikaz'] + ')'; if (params.ContainsKey('idStroj')) then if (params.Items['idStroj']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.IDStroje=' + params.Items['idStroj']; if (params.ContainsKey('idPrac')) then if (params.Items['idPrac']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.IDPracoviste=' + params.Items['idPrac']; if (params.ContainsKey('stav')) then if (params.Items['stav']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.Stav=' + params.Items['stav']; if (params.ContainsKey('idZam')) then if (params.Items['idZam']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.IDZamestnance=' + params.Items['idZam']; if (params.ContainsKey('cisZam')) then if (params.Items['cisZam']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.IDZamestnance=(SELECT ID FROM ' + tblCisZam + ' WHERE Cislo=' + params.Items['cisZam'] + ')'; if (params.ContainsKey('datumPripadu')) then if (params.Items['datumPripadu']<>'') then begin datum:= params.Items['datumPripadu']; if (datum<>'') then begin if (Length(datum)=8) then datum:= datum + '000000'; if (Length(datum)=10) then datum:= datum + '00'; datum:= MidStr(datum, 7, 2) + '.' + MidStr(datum, 5, 2) + '.' + LeftStr(datum, 4) + ' ' + MidStr(datum, 9, 2) + ':' + MidStr(datum, 11, 2) + ':' + MidStr(datum, 13, 2); if not(TryStrToDateTime(datum, d)) then d:= Now; end else d:= Now; where:= where + IfThen(where<>'', ' AND ', '') + 'dbo.hf_TruncDate(main.DatumPripadu)='; if (datum<>'') then where:= where + 'dbo.hf_TruncDate(CONVERT(datetime, N' + (FormatDateTime('dd.mm.yyyy', d) + ' 00:00:00').QuotedString + ',104))' // else // where:= where + 'DATEADD(day, -3, dbo.hf_TruncDate(GETDATE()))'; end; if (params.ContainsKey('odDatum')) then if (params.Items['odDatum']<>'') then odDatum:= params.Items['odDatum']; 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, -3) end else datOd:= IncDay(Now, -3); if (params.ContainsKey('doDatum')) then if (params.Items['doDatum']<>'') then doDatum:= params.Items['doDatum']; 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:= Now end else datDo:= Now; end; where:= where + IfThen(where<>'', ' AND ', '') + 'main.DatumPripadu>='; if (odDatum<>'') then where:= where + 'CONVERT(datetime, N' + (FormatDateTime('dd.mm.yyyy', datOd) + ' 00:00:00').QuotedString + ',104)' else where:= where + 'DATEADD(day, -3, dbo.hf_TruncDate(GETDATE()))'; if (doDatum<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.DatumPripadu<=CONVERT(datetime, N' + (FormatDateTime('dd.mm.yyyy', datDo) + ' 23:59:59').QuotedString + ',104)'; {$IFDEF CUSTOM_CTRL_INCOSystems} if (params.ContainsKey('datazoneId')) and (SQLTableExists ('dbo', tblRozpracOperE)) then if (SQLColumnExists(tblRozpracOperE, '_PHIdent')) then if (params.Items['datazoneId']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.ID=(SELECT ID FROM ' + tblRozpracOperE + ' WHERE _PHIdent=' + params.Items['datazoneId'] + ')'; {$ENDIF} if (where<>'') then lSQL:= lSQL + ' WHERE ' + where; lSQL:= lSQL + ' ORDER BY main.DatumPripadu, main.CasZahajeni'; lQry:= TFDQuery.Create(nil); try sqlConnX.Connected := true; lQry.Connection:= sqlConnX; lQry.Open (lSQL); cnt:= lQry.RecordCount; resList:= TObjectList.Create; try lQry.First; rNo:= 1; while (rNo<=cnt) do begin lQry.RecNo:= rNo; AID:= lQry.FieldByName('ID').AsInteger; resObj:= self.GetByID (AID, incOper); resList.Add (resObj); Inc (rNo); end; result:= resList; except on E:Exception do raise EServiceException.Create('Chyba nacitani Evidence rozpracovanych operaci: ' + E.Message); end; finally lQry.Free; end; if (lokalniConnection) then begin sqlConnX.Close; sqlConnX.Free; end; end; function TVyrobaEvidRozpracOperaciService.GetByID (const AID: integer; incOper: boolean; AConn: TFDConnection = nil): TEvidRozpracOperace; var lSQL, extInfoStr: string; p: TDictionary; lOper: TObjectList; opSrv: TVyrobaEvidRozpracOperaciOperaceService; lQry: TFDQuery; sqlConnX: TFDConnection; lokalniConnection: boolean; begin result := nil; lokalniConnection := (AConn = nil); if (AID>0) then begin lQry := TFDQuery.Create(nil); try if (lokalniConnection) then begin sqlConnX := TFDConnection.Create(nil); sqlConnX.ConnectionDefName := sqlPoolName; end else sqlConnX := AConn; try extInfoStr := ''; if (SQLTableExists ('dbo', tblRozpracOperE)) then begin lSQL := 'SELECT ' + GetTabCols ('dbo', tblRozpracOperE, '', true, 'ID') + ' FROM ' + tblRozpracOperE + ' 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.Close; end; end; p := TDictionary.Create; p.Add('idEvodROp', AID.ToString); opSrv := TVyrobaEvidRozpracOperaciOperaceService.Create (self.FDM); try sqlConnX.Connected := true; lQry.Connection := sqlConnX; lSQL := 'SELECT ' + GetTabCols('', tblRozpracOper, 'main') + IfThen(selNazevOperaceEvidROp<>'', ',' + selNazevOperaceEvidROp, ''); lSQL := lSQL + ' FROM ' + tblRozpracOper + ' main WHERE main.ID=:ID'; lQry.Open(lSQL, [AID]); lQry.First; if not(lQry.EOF) then begin result := lQry.AsObject; if (extInfoStr<>'') then result.ExtInfo:= ExtInfoStr; if (incOper) then begin lOper := opSrv.GetByParams (p); Result.Operace := lOper; end; p.Free; end else begin p.Free; raise EServiceException.Create('Evidence rozpracovanych operací s ID ' + AID.ToString + ' nebyla nalezena.'); end; finally end; except on E:Exception do raise EServiceException.Create('Evidence rozpracovanych operaci s ID ' + AID.ToString + ' nebyla nalezena - ' + E.Message); end; finally lQry.Free; if (lokalniConnection) then begin sqlConnX.Close; sqlConnX.Free; end; if (opSrv<>nil) then opSrv.Free; end; end; end; function TVyrobaEvidRozpracOperaciService.GetMeta: TJsonObject; var lSQL: string; lQry: TFDQuery; begin lSQL:= 'SELECT ' + GetTabCols('', tblRozpracOper, '') + ' FROM ' + tblRozpracOper + ' WHERE (1=0)'; lQry:= TFDQuery.Create(nil); lQry.Connection:= FDM.sqlConn; try lQry.Open(lSQL); Result := lQry.MetadataAsJSONObject(); finally lQry.Free; end; end; function TVyrobaEvidRozpracOperaciService.RadekGetMeta: TJsonObject; var lSQL: string; lQry: TFDQuery; begin lSQL := 'SELECT ' + GetTabCols('', tblRozpracOperR, '') + ' FROM ' + tblRozpracOperR + ' WHERE (1=0)'; lQry := TFDQuery.Create(nil); lQry.Connection := FDM.sqlConn; try lQry.Open (lSQL); Result := lQry.MetadataAsJSONObject(); finally lQry.Free; end; end; { TTPVCiselnikZmenService } function TTPVCiselnikZmenService.GetMeta: TJsonObject; var lSQL: string; lQry: TFDQuery; // sqlConnX: TFDConnection; begin lSQL:= 'SELECT ' + GetTabCols('', tblCZmen, '') + ' FROM ' + tblCZmen + ' WHERE (1=0)'; lQry:= TFDQuery.Create(nil); lQry.Connection:= FDM.sqlConn; try lQry.Open(lSQL); Result := lQry.MetadataAsJSONObject(); finally lQry.Free; end; end; function TTPVCiselnikZmenService.GetByID (const AID: integer): TTPVCiselnikZmen; var p: TDictionary; i: TObjectList; begin result:= nil; if (AID>0) then begin i:= TObjectList.Create(); p:= TDictionary.Create; p.Add('id', AID.ToString); i:= self.GetByParams(p); if (i.Count>0) then result:= i[0]; p.Free; i.Free; end; end; function TTPVCiselnikZmenService.GetByParams (params: TDictionary): TObjectList; var lSQL, where, datum: string; d: TDateTime; lQry: TFDQuery; sqlConnX: TFDConnection; begin result:= nil; lSQL:= 'SELECT ' + GetTabCols('', tblCZmen, 'main') + ' FROM ' + tblCZmen + ' main'; where:= ''; if (params<>nil) then begin if (params.ContainsKey('id')) then if (params.Items['id']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.ID=' + params.Items['id']; if (params.ContainsKey('rada')) then if (params.Items['rada']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.Rada=' + params.Items['rada'].QuotedString; if (params.ContainsKey('jenPlatne')) then if (params.Items['jenPlatne']='1') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.PlatnostTPV=1'; if (params.ContainsKey('datum')) then if (params.Items['datum']<>'') then begin datum:= params.Items['datum']; if (datum<>'') then begin if (Length(datum)=8) then datum:= datum + '000000'; if (Length(datum)=10) then datum:= datum + '00'; datum:= MidStr(datum, 7, 2) + '.' + MidStr(datum, 5, 2) + '.' + LeftStr(datum, 4) + ' ' + MidStr(datum, 9, 2) + ':' + MidStr(datum, 11, 2) + ':' + MidStr(datum, 13, 2); if not(TryStrToDateTime(datum, d)) then d:= IncDay(Now, -1) end else d:= IncDay(Now, -1); lSQL:= lSQL + ' AND main.Datum>='; if (datum<>'') then lSQL:= lSQL + 'CONVERT(datetime, N' + FormatDateTime('dd.mm.yyyy hh:nn:ss', d).QuotedString + ',104)' else lSQL:= lSQL + 'DATEADD(day, -1, GETDATE())'; end; end; if (where<>'') then lSQL:= lSQL + ' WHERE ' + where; lSQL:= lSQL + ' ORDER BY main.Rada, main.ciszmeny'; sqlConnX:= TFDConnection.Create(nil); sqlConnX.ConnectionDefName:= sqlPoolName; lQry:= TFDQuery.Create(nil); try lQry.Connection:= sqlConnX; lQry.Open(lSQL); result:= lQry.AsObjectList; finally lQry.Free; end; sqlConnX.Close; sqlConnX.Free; end; { TTPVPrednastaveniOperaciService } function TTPVPrednastaveniOperaciService.GetMeta: TJsonObject; var lSQL: string; lQry: TFDQuery; begin lSQL:= 'SELECT ' + GetTabCols('', tblPredna, '') + ' FROM ' + tblPredna + ' WHERE (1=0)'; lQry:= TFDQuery.Create(nil); lQry.Connection:= FDM.sqlConn; try lQry.Open(lSQL); Result := lQry.MetadataAsJSONObject(); finally lQry.Free; end; end; function TTPVPrednastaveniOperaciService.GetAll (params: TDictionary=nil): TObjectList; var lSQL, where, errMsg, ordBy: string; locParams: TDictionary; AID, cnt, rNo: integer; jenSeznam: boolean; resList: TObjectList; resObj: TTPVPrednastaveniOperaci; lQry: TFDQuery; sqlConnX: TFDConnection; lokIDcka: TList; begin result:= TObjectList.Create; locParams:= TDictionary.Create; jenSeznam:= false; ordBy:= 'main.ID'; if (params<>nil) then begin if (params.ContainsKey('seznam')) then if (params.Items['seznam']='1') then jenSeznam:= true; if (params.ContainsKey('minimumDat')) then if (params.Items['minimumDat']='1') then jenSeznam:= true; if (jenSeznam) then locParams.Add ('minimumDat', '1'); if (params.ContainsKey('ordBy')) then if (params.Items['ordBy']<>'') then ordBy:= params.Items['ordBy']; end; lSQL:= 'SELECT ' + GetTabCols('', tblPredna, 'main') + ' FROM ' + tblPredna + ' main'; where:= ''; if (params<>nil) then if (params.ContainsKey('id')) then if (params.Items['id']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.ID=' + params.Items['id']; if (where<>'') then lSQL:= lSQL + ' WHERE ' + where; lSQL:= lSQL + ' ORDER BY ' + ordBy; sqlConnX:= TFDConnection.Create(nil); sqlConnX.ConnectionDefName:= sqlPoolName; lQry:= TFDQuery.Create(nil); try sqlConnX.Open; lQry.Connection:= sqlConnX; lQry.FetchOptions.Mode:= fmAll; lQry.Open(lSQL); cnt:= lQry.RecordCount; if (cnt>0) then begin lokIDcka:= TList.Create; try 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; lokIDcka.Add (AID); if not(lQry.Active) then lQry.Open; Inc(rNo); end; lQry.Close; except on E: Exception do begin {$IF CompilerVersion >= 37.0} // Delphi 13+ raise EServiceException.Create ('Chyba načítání naskenované položky/položek dokladu') from E; {$ELSE} raise EServiceException.Create ('Chyba nacitani seznamu Prednastaveni pro operace postupu: ' + E.Message) at ExceptAddr; {$ENDIF} end; end; try for AID in lokIDcka do result.Add ( self.GetByID (AID, locParams, sqlConnX) ); except on E: Exception do begin result.Free; {$IF CompilerVersion >= 37.0} // Delphi 13+ raise EServiceException.Create ('Chyba načítání naskenované položky/položek dokladu') from E; {$ELSE} raise EServiceException.Create ('Chyba nacitani seznamu Prednastaveni pro operace postupu: ' + E.Message) at ExceptAddr; {$ENDIF} end; end; finally lokIDcka.Free; end; end; // result:= lQry.AsObjectList; finally lQry.Free; sqlConnX.Free; sqlConnX.Close; locParams.Free; end; end; function TTPVPrednastaveniOperaciService.GetByID (const AID: Integer = 0; params: TDictionary=nil; AConn: TFDConnection = nil): TTPVPrednastaveniOperaci; var lSQL, where, extInfoStr, erMsg: string; minimumDat: boolean; 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', tblPrednaE)) then begin lSQL := 'SELECT ' + GetTabCols ('dbo', tblPrednaE, '', true, 'ID') + ' FROM ' + tblPrednaE + ' WHERE ID=:ID'; lQry := TFDQuery.Create(nil); try lQry.FetchOptions.Mode := fmAll; lQry.Connection := sqlConnX; lQry.Open (lSQL, [AID]); if (lQry.RecordCount>0) then extInfoStr := lQry.AsJSONObject; finally lQry.Free; end; end; minimumDat := true; if (params<>nil) then begin if (params.ContainsKey('minimumDat')) then if (params.Items['minimumDat']='0') then minimumDat := false; end; if (minimumDat) then lSQL := 'SELECT ID, Typ, nazev, idPracoviste, idStroje, IDKooperace, Poznamka FROM ' + tblPredna + ' main' else lSQL := 'SELECT ' + GetTabCols('', tblPredna, 'main') + ' FROM ' + tblPredna + ' main'; where := ''; where := where + IfThen(where<>'', ' AND ', '') + 'main.ID=:ID'; if (where<>'') then lSQL := lSQL + ' WHERE ' + where; lQry := TFDQuery.Create(nil); try lQry.Connection := sqlConnX; lQry.FetchOptions.Mode := fmAll; lQry.Open (lSQL, [AID]); result := lQry.AsObject; if (extInfoStr<>'') then Result.ExtInfo := extInfoStr; lQry.Close; finally lQry.Free; end; if (lokalniConnection) then begin sqlConnX.Close; sqlConnX.Free; end; end; function TTPVPrednastaveniOperaciService.GetByParams (params: TDictionary; AConn: TFDConnection = nil): TObjectList; var lSQL, where, errMsg, ordBy: string; {$IFDEF CUSTOM_CTRL_INCOSystems} cislo: string; {$ENDIF} locParams: TDictionary; cnt, rNo, AID: integer; jenSeznam: boolean; lQry: TFDQuery; sqlConnX: TFDConnection; resList: TObjectList; resObj: TTPVPrednastaveniOperaci; lokalniConnection: boolean; lokIDcka: TList; begin result := nil; lokalniConnection := (AConn = nil); if (lokalniConnection) then begin sqlConnX:= TFDConnection.Create(nil); sqlConnX.ConnectionDefName:= sqlPoolName; end else sqlConnX := AConn; locParams:= TDictionary.Create; jenSeznam:= false; ordBy:= 'main.ID'; if (params<>nil) then begin if (params.ContainsKey('seznam')) then if (params.Items['seznam']='1') then jenSeznam:= true; if (params.ContainsKey('ordBy')) then if (params.Items['ordBy']<>'') then ordBy:= 'main.' + params.Items['ordBy']; if (params.ContainsKey('minimumDat')) then if (params.Items['minimumDat']<>'') then locParams.Add ('minimumDat', params.Items['minimumDat']); if (jenSeznam) then locParams.Add ('seznam', ''); end; lSQL:= 'SELECT main.ID FROM ' + tblPredna + ' main'; where:= ''; if (params<>nil) then begin if (params.ContainsKey('id')) then if (params.Items['id']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.ID=' + params.Items['id']; {$IFDEF CUSTOM_CTRL_INCOSystems} if (params.ContainsKey('cislo')) then if (params.Items['cislo']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.ID IN (SELECT ID FROM ' + tblPrednaE + ' WHERE _CisloOperace IS NOT NULL AND TRIM(_CisloOperace)=N' + params.Items['cislo'].QuotedString + ')'; {$ENDIF} end; if (where<>'') then lSQL:= lSQL + ' WHERE ' + where; lSQL:= lSQL + ' ORDER BY ' + ordBy; lQry:= TFDQuery.Create(nil); try lQry.Connection := sqlConnX; lQry.FetchOptions.Mode := fmAll; lQry.Open (lSQL); cnt := lQry.RecordCount; if (cnt>0) then begin lokIDcka:= TList.Create; try 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; lokIDcka.Add (AID); if not(lQry.Active) then lQry.Open; Inc (rNo); end; lQry.Close; except on E: Exception do begin {$IF CompilerVersion >= 37.0} // Delphi 13+ raise EServiceException.Create ('Chyba načítání naskenované položky/položek dokladu') from E; {$ELSE} raise EServiceException.Create ('Chyba nacitani Prednastaveni pro operace postupu: ' + E.Message) at ExceptAddr; {$ENDIF} end; end; resList:= TObjectList.Create; try for AID in lokIDcka do begin resObj := self.GetByID (AID, locParams, sqlConnX); resList.Add (resObj); end; result := resList; resList := nil; finally resList.Free; end; // FreeAndNil(resList); finally lokIDcka.Free; end; end else raise EServiceException.Create('Vybranym podminkam neodpovida zadne Prednastaveni pro operace postupu'); finally lQry.Free; end; locParams.Free; if (lokalniConnection) then begin sqlConnX.Close; sqlConnX.Free; end; end; { TTPVZakazkoveModifikaceService } function TTPVZakazkoveModifikaceService.GetMeta: TJsonObject; var lSQL: string; lQry: TFDQuery; begin lSQL:= 'SELECT ' + GetTabCols('', tblZakazModif, '') + ' FROM ' + tblZakazModif + ' WHERE (1=0)'; lQry:= TFDQuery.Create(nil); lQry.Connection:= FDM.sqlConn; try lQry.Open(lSQL); Result := lQry.MetadataAsJSONObject(); finally lQry.Free; end; end; function TTPVZakazkoveModifikaceService.GetByParams (params: TDictionary): TObjectList; var lSQL, where: string; lQry: TFDQuery; sqlConnX: TFDConnection; begin result:= nil; lSQL:= 'SELECT ' + GetTabCols('', tblZakazModif, 'main') + ' FROM ' + tblZakazModif + ' main'; where:= ''; if (params<>nil) then begin if (params.ContainsKey('id')) then if (params.Items['id']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.ID=' + params.Items['id']; end; if (where<>'') then lSQL:= lSQL + ' WHERE ' + where; lSQL:= lSQL + ' ORDER BY main.ID'; sqlConnX:= TFDConnection.Create(nil); sqlConnX.ConnectionDefName:= sqlPoolName; lQry:= TFDQuery.Create(nil); try lQry.Connection:= sqlConnX; lQry.FetchOptions.Mode:= fmAll; lQry.Open (lSQL); result:= lQry.AsObjectList; finally lQry.Free; end; sqlConnX.Close; sqlConnX.Free; end; { TTPVZakazkoveModifikaceServiceDilce } function TTPVZakazkoveModifikaceDilceService.GetMeta: TJsonObject; var lSQL: string; lQry: TFDQuery; sqlConnX: TFDConnection; begin sqlConnX:= TFDConnection.Create(nil); sqlConnX.ConnectionDefName:= sqlPoolName; lSQL:= 'SELECT ' + GetTabCols('', tblZakazModifDilce, '') + ' FROM ' + tblZakazModifDilce + ' WHERE (1=0)'; lQry:= TFDQuery.Create(nil); try lQry.Connection:= sqlConnX; lQry.FetchOptions.Mode:= fmAll; lQry.Open(lSQL); result:= lQry.MetadataAsJSONObject(); finally lQry.Free; end; sqlConnX.Close; sqlConnX.Free; end; function TTPVZakazkoveModifikaceDilceService.GetByParams (params: TDictionary): TObjectList; var lSQL, where: string; lQry: TFDQuery; sqlConnX: TFDConnection; resList: TObjectList; begin result:= nil; lSQL:= 'SELECT ' + GetTabCols('', tblZakazModifDilce, 'main') + ' FROM ' + tblZakazModifDilce + ' main'; where:= ''; if (params<>nil) then begin if (params.ContainsKey('id')) then if (params.Items['id']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.ID=' + params.Items['id']; end; if (where<>'') then lSQL:= lSQL + ' WHERE ' + where; lSQL:= lSQL + ' ORDER BY main.ID'; sqlConnX:= TFDConnection.Create(nil); sqlConnX.ConnectionDefName:= sqlPoolName; lQry:= TFDQuery.Create(nil); try lQry.Connection:= sqlConnX; lQry.FetchOptions.Mode:= fmAll; lQry.Open(lSQL); result:= lQry.AsObjectList; finally lQry.Free; end; sqlConnX.Close; sqlConnX.Free; end; { TTPVOperaceDilceService } function TTPVOperaceDilceService.GetMeta: TJSONObject; var lSQL: string; lQry: TFDQuery; sqlConnX: TFDConnection; begin sqlConnX:= TFDConnection.Create(nil); sqlConnX.ConnectionDefName:= sqlPoolName; lSQL:= 'SELECT ' + GetTabCols('', tblPrPost, 'main') + ' FROM ' + tblPrPost + ' main WHERE 1=0'; lQry:= TFDQuery.Create(nil); try lQry.Connection:= sqlConnX; lQry.FetchOptions.Mode:= fmAll; lQry.Open(lSQL); result:= lQry.MetadataAsJSONObject(); finally lQry.Free; end; sqlConnX.Close; sqlConnX.Free; end; function TTPVOperaceDilceService.GetByParams (params: TDictionary; AConn: TFDConnection = nil): TObjectList; var lSQL, where: 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; lSQL := 'SELECT ' + GetTabCols('', tblPostup, 'main') + ' FROM ' + tblPostup + ' main'; where := ''; if (params<>nil) then begin if (params.ContainsKey('zmenaDo')) then begin if (params.Items['zmenaDo']<>'') then where := where + IfThen(where<>'', ' AND ', '') + 'main.ZmenaDo=' + params.Items['zmenaDo'] else where := 'ZmenaDo IS NULL'; end else where := 'ZmenaDo IS NULL'; if (params.ContainsKey('id')) then if (params.Items['id']<>'') then where := where + IfThen(where<>'', ' AND ', '') + 'main.ID=' + params.Items['id']; if (params.ContainsKey('dilec')) then if (params.Items['dilec']<>'') then where := where + IfThen(where<>'', ' AND ', '') + 'main.vyssi=' + params.Items['dilec']; if (params.ContainsKey('idZakazModif')) then if (params.Items['idZakazModif']<>'') then where := where + IfThen(where<>'', ' AND ', '') + 'main.IDZakazModif=' + params.Items['idZakazModif']; end; if (where<>'') then lSQL := lSQL + ' WHERE ' + where; lSQL := lSQL + ' ORDER BY main.ID'; lQry := TFDQuery.Create(nil); try sqlConnX.Connected := true; lQry.Connection := sqlConnX; lQry.FetchOptions.Mode := fmAll; lQry.Open (lSQL); result := lQry.AsObjectList; lQry.Close; finally lQry.Free; end; if (lokalniConnection) then begin sqlConnX.Close; sqlConnX.Free; end; end; { TTPVKusovnikDilceService } function TTPVKusovnikDilceService.GetMeta: TJSONObject; var lSQL: string; lQry: TFDQuery; sqlConnX: TFDConnection; begin sqlConnX:= TFDConnection.Create(nil); sqlConnX.ConnectionDefName:= sqlPoolName; lSQL:= 'SELECT ' + GetTabCols('', tblKVaz, 'main') + ' FROM ' + tblKVaz + ' main WHERE 1=0'; lQry:= TFDQuery.Create(nil); try lQry.Connection:= sqlConnX; lQry.Open(lSQL); result:= lQry.MetadataAsJSONObject(); finally lQry.Free; end; sqlConnX.Close; sqlConnX.Free; end; function TTPVKusovnikDilceService.GetByParams (params: TDictionary; AConn: TFDConnection = nil): TObjectList; var lSQL, where: 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; lSQL:= 'SELECT ' + GetTabCols('', tblKVaz, 'main') + ' FROM ' + tblKVaz + ' main'; where:= ''; if (params<>nil) then begin if (params.ContainsKey('zmenaDo')) then begin if (params.Items['zmenaDo']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.ZmenaDo=' + params.Items['zmenaDo'] else where:= 'ZmenaDo IS NULL'; end else where:= 'ZmenaDo IS NULL'; if (params.ContainsKey('id')) then if (params.Items['id']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.ID=' + params.Items['id']; if (params.ContainsKey('dilec')) then if (params.Items['dilec']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.vyssi=' + params.Items['dilec']; if (params.ContainsKey('idZakazModif')) then begin if (params.Items['idZakazModif']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.IDZakazModif=' + params.Items['idZakazModif'] else where:= where + IfThen(where<>'', ' AND ', '') + 'main.IDZakazModif IS NULL'; end else where:= where + IfThen(where<>'', ' AND ', '') + 'main.IDZakazModif IS NULL'; end; if (where<>'') then lSQL:= lSQL + ' WHERE ' + where; lSQL:= lSQL + ' ORDER BY main.ID'; lQry:= TFDQuery.Create(nil); try sqlConnX.Connected := true; lQry.Connection := sqlConnX; lQry.FetchOptions.Mode := fmAll; lQry.Open (lSQL); result := lQry.AsObjectList; finally lQry.Free; end; if (lokalniConnection) then begin sqlCOnnX.Close; sqlConnX.Free; end; end; { TQMSKontrolniPlanService } function TQMSKontrolniPlanService.GetMeta: TJSONObject; var lSQL: string; lQry: TFDQuery; sqlConnX: TFDConnection; begin sqlConnX:= TFDConnection.Create(nil); sqlConnX.ConnectionDefName:= sqlPoolName; lSQL:= 'SELECT ' + GetTabCols('', tblKJ, 'main') + ' FROM ' + tblKVaz + ' main WHERE 1=0'; lQry:= TFDQuery.Create(nil); try lQry.Connection:= sqlConnX; lQry.FetchOptions.Mode:= fmAll; lQry.Open(lSQL); result:= lQry.MetadataAsJSONObject(); finally lQry.Free; end; sqlConnX.Close; sqlConnX.Free; end; function TQMSKontrolniPlanService.GetByParams (params: TDictionary): TObjectList; var lSQL, where: string; AID, cnt, rNo: integer; resList: TObjectList; resObj: TQMSKontrolniPlan; lQry: TFDQuery; sqlConnX: TFDConnection; begin result:= nil; lSQL:= 'SELECT main.ID FROM ' + tblKPLHlav + ' main'; where:= 'main.Kategorie IN (SELECT Cislo FROM ' + tblKategKJ + ' WHERE QMSAgenda=12)' ; if (params<>nil) then begin if (params.ContainsKey('id')) then if (params.Items['id']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.ID=' + params.Items['id']; if (params.ContainsKey('kategorie')) then if (params.Items['kategorie']<>'') then where:= where + IfThen(where<>'', ' AND ', '') + 'main.kategorie=N' + params.Items['kategorie'].QuotedString; end; if (where<>'') then lSQL:= lSQL + ' WHERE ' + where; lSQL:= lSQL + ' ORDER BY main.ID'; sqlConnX:= TFDConnection.Create(nil); sqlConnX.ConnectionDefName:= sqlPoolName; lQry:= TFDQuery.Create(nil); try lQry.Connection:= sqlConnX; lQry.FetchOptions.Mode:= fmAll; lQry.Open(lSQL); cnt:= lQry.RecordCount; if (cnt>0) then begin resList:= TObjectList.Create; try lQry.First; rNo:= 1; while (rNo<=cnt) do 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 raise EServiceException.Create('Chyba nacitani Evidence rozpracovanych operaci: ' + E.Message); end; end; finally lQry.Free; end; sqlConnX.Close; sqlConnX.Free; end; function TQMSKontrolniPlanService.GetAll (params: TDictionary=nil): TObjectList; var lSQL, errMsg, ordBy: string; jenSeznam: boolean; lQry: TFDQuery; sqlConnX: TFDConnection; begin result:= nil; jenSeznam:= false; ordBy:= 'main.ID'; if (params<>nil) then begin if (params.ContainsKey('seznam')) then if (params.Items['seznam']='1') then jenSeznam:= true; if (params.ContainsKey('ordBy')) then if (params.Items['ordBy']<>'') then ordBy:= params.Items['ordBy']; end; lSQL:= 'SELECT ' + GetTabCols('', tblKPLHlav, 'main') + ' FROM ' + tblKPLHlav + ' main'; lSQL:= lSQL + ' ORDER BY ' + ordBy; lSQL:= lSQL.Replace('TabKPLHlav.', 'main.'); 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; finally lQry.Free; end; sqlConnX.Close; sqlConnX.Free; end; function TQMSKontrolniPlanService.GetByID (const AID: Integer): TQMSKontrolniPlan; var lSQL, where, extInfoStr: string; lQry: TFDQuery; sqlConnX: TFDConnection; begin result:= nil; sqlConnX:= TFDConnection.Create(nil); sqlConnX.ConnectionDefName:= sqlPoolName; extInfoStr:= ''; if (SQLTableExists ('dbo', tblKPLHlavE)) then begin lSQL:= 'SELECT ' + GetTabCols ('dbo', tblKPLHlavE, '', true, 'ID') + ' FROM ' + tblKPLHlavE + ' 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; lSQL:= 'SELECT ' + GetTabCols('', tblKPLHlav, 'main') + ' FROM ' + tblKPLHlav + ' main WHERE main.ID=:ID'; lQry:= TFDQuery.Create(nil); try lQry.Connection:= sqlConnX; lQry.FetchOptions.Mode:= fmAll; lQry.Open(lSQL, [AID]); result:= lQry.AsObject; if (extInfoStr<>'') then result.ExtInfo:= ExtInfoStr; finally lQry.Free; end; sqlConnX.Close; sqlConnX.Free; end; end.