unit datMod; interface uses System.SysUtils, System.Classes, Data.DB, Datasnap.DBClient, System.ImageList, Vcl.Graphics, Vcl.ImgList, Vcl.VirtualImageList, Vcl.BaseImageCollection, Vcl.ImageCollection, Vcl.Controls, Datasnap.Provider, Vcl.DBCGrids, Vcl.Grids, JvExDBGrids, JvDBGrid, FireDAC.Stan.Option, FireDAC.Stan.Param, FireDAC.Stan.Error, FireDAC.DatS, FireDAC.Phys.Intf, FireDAC.DApt.Intf, FireDAC.Comp.DataSet, FireDAC.Comp.Client, FireDAC.Stan.Intf, ddPlugin_TLB, frmPekarna, frmKontrolaCCP1, frmKontrolaCCP1edt, frmPrijemNestandard, frmPredvyroba, frmPredvyrobaDetail, frmVyrobniDenik, frmVyrobaMimoPlan, frmZahajeniVyrobyPalety, frmZasobaObjednavky, frmObjednavkaMat, frmOdpady, frmSeznamPalet, frmPrehledMicharna, frmSpravaVzorku, frmSarzeVydej; const tblCCP1 = '[dbo].[_TabVyroba_KontrolyCCP1]'; constHvwVyrobaVyrobniDenik = 'hvw_Vyroba_VyrobniDenik'; constHvwVyrobaPalety = 'hvw_Vyroba_Palety'; type recStrojZaznam = record id: integer; stroj, aktualPL, zakazka: string; idKmen: integer; ccp1: boolean; {$IF CompilerVersion>=34} // Sydney a vys class operator Initialize (out Dest: recStrojZaznam); {$ENDIF} end; TPaleta = record id: integer; idVyrCisPrikaz: integer; paletList: string; sarze: string; pocetKA: integer; pocetKS: integer; end; TOperace = record id: integer; doklad: integer; alt: string; mnoz: Extended; koef: Extended; end; Tdm = class(TDataModule) dsPlanDnes: TDataSource; vtPlanDnes: TFDMemTable; vtPlanDnesfIDPlan2: TIntegerField; vtPlanDnesfVyrobek: TStringField; vtPlanDnesfHotovo: TFloatField; vtPlanDnesfZakazka: TStringField; dsStroje: TDataSource; vtStroje: TFDMemTable; vtStrojefIDStroj: TIntegerField; vtStrojefStroj: TStringField; vtStrojefVyrobek: TStringField; vtStrojefAktPaletList: TStringField; vtStrojefVyrobenoKA: TFloatField; vtStrojefZakazka: TStringField; vtStrojefCinnost: TStringField; vtStrojefCCP: TBooleanField; vtStrojefVZ: TSmallintField; vtPlanZitra: TFDMemTable; vtPlanZitrafIDPlan2: TIntegerField; vtPlanZitrafVyrobek: TStringField; vtPlanZitrafHotovo: TFloatField; vtPlanZitrafZakazka: TStringField; dsPlanZitra: TDataSource; vtRozpracPL: TFDMemTable; dsRozpracPL: TDataSource; imgColl: TImageCollection; imgList: TVirtualImageList; imgList2: TImageList; vtVyrobniPozn: TFDMemTable; dsVyrobniPozn: TDataSource; vtVyrobniPoznfID: TIntegerField; vtVyrobniPoznfDatum: TDateTimeField; vtVyrobniPoznfPoznamka: TStringField; dsPrijemNestandard: TDataSource; vtPrijemNestandard: TFDMemTable; vtPrijemNestandardfID: TIntegerField; vtPrijemNestandardfCislo: TIntegerField; vtPrijemNestandardfSZ: TStringField; vtPrijemNestandardfRegCis: TStringField; vtPrijemNestandardfNazev1: TStringField; vtPrijemNestandardfMJ: TStringField; vtStrojefIDEvidRozpOper: TIntegerField; vtStrojefIDPrikaz: TIntegerField; vtStrojefVyrCisloZbozi: TStringField; dsCCP1: TDataSource; vtCCP1: TFDMemTable; vtCCP1colID: TIntegerField; vtCCP1colStroj: TStringField; vtCCP1colSmena: TStringField; vtCCP1colTeplota: TFloatField; vtCCP1colRychlost: TFloatField; vtCCP1colHmotnost: TFloatField; vtCCP1colIDNorma: TIntegerField; vtCCP1colDelka: TFloatField; vtCCP1colPrumer: TFloatField; vtCCP1colVyhovuje: TBooleanField; vtCCP1colVyrobek: TStringField; vtCCP1colNapravneOpatreni: TStringField; vtCCP1colUzavreno: TBooleanField; vtCCP1colDatum: TDateTimeField; vtCCP1colIDStroj: TIntegerField; vtRozpracPLfIDEvidRozp: TIntegerField; vtRozpracPLfIDStroj: TIntegerField; vtRozpracPLfStroj: TStringField; vtRozpracPLfCisloZbozi: TStringField; vtRozpracPLfAktPaletList: TStringField; vtRozpracPLfDatStart: TDateTimeField; vtRozpracPLfColR: TBooleanField; vtStrojefIDKmen: TIntegerField; vtPlanDnesfDatPlanZadani: TDateField; vtPlanDnesfRegCis: TStringField; vtRozpracPLfVyrobek: TStringField; dsPLZakazky: TDataSource; vtPLZakazky: TFDMemTable; vtPLZakazkycolZakazka: TStringField; vtPLZakazkycolDatVyroby: TDateField; vtPLZakazkycolKANaPalete: TIntegerField; vtPLZakazkycolKsNaPalete: TIntegerField; dsVyrobniDenik: TDataSource; vtVyrobniDenik: TFDMemTable; vtVyrobniDenikcolIDKmen: TIntegerField; vtVyrobniDenikcolNazev1: TStringField; vtVyrobniDenikcolIDStroj: TIntegerField; vtVyrobniDenikcolStroj: TStringField; vtVyrobniDenikcolDatum: TDateField; vtVyrobniDenikcolSmena: TSmallintField; vtVyrobniDenikcolMnoz: TFloatField; vtVyrobniDenikcolCisloZbozi: TStringField; vtVyrobniDenikcolMnozPal: TFloatField; vtPredvyroba: TFDMemTable; vtPredvyrobacolIDKmen: TIntegerField; vtPredvyrobacolSZ: TStringField; vtPredvyrobacolNazev1: TStringField; vtPredvyrobacolRegCis: TStringField; vtPredvyrobacolMJ: TStringField; vtPredvyrobacolStroj: TStringField; vtPredvyrobacolBaleni: TIntegerField; dsPredvyroba: TDataSource; vtPredvyrobacolBaleni2: TIntegerField; dsPredvyrobaDetail: TDataSource; vtPredvyrobaDetail: TFDMemTable; vtPredvyrobaDetailidPZ: TIntegerField; vtPredvyrobaDetailcolSZ: TStringField; vtPredvyrobaDetailcolRegCis: TStringField; vtPredvyrobaDetailcolNazev1: TStringField; vtPredvyrobaDetailcolCisloPL: TStringField; vtPredvyrobaDetailcolKA: TIntegerField; vtPredvyrobaDetailcolKs: TIntegerField; vtPredvyrobaDetailcolStav: TSmallintField; vtPredvyrobaDetailcolDatumVyroby: TDateTimeField; vtPredvyrobaDetailcolDatumVyrazeni: TDateTimeField; vtPredvyrobaDetailcolZpracDatVyr: TDateTimeField; vtPredvyrobaDetailcolZpracDatVyraz: TDateTimeField; vtPredvyrobaDetailcolIDPrikaz: TIntegerField; vtPredvyrobaDetailcolCisloZakazky: TStringField; vtVyrobaMimoPlan: TFDMemTable; dsVyrobaMimoPlan: TDataSource; vtVyrobaMimoPlancolIDKmen: TIntegerField; vtVyrobaMimoPlancolSZ: TStringField; vtVyrobaMimoPlancolRegCis: TStringField; vtVyrobaMimoPlancolNazev1: TStringField; vtVyrobaMimoPlancolZaklad: TStringField; vtVyrobaMimoPlancolBaleni1: TIntegerField; vtVyrobaMimoPlancolBaleni2: TIntegerField; vtVyrobaMimoPlancolBaleni3: TIntegerField; vtVyrobaMimoPlancolMJ: TStringField; vtVyrobaMimoPlancolStroj: TStringField; vtVyrobaMimoPlancolIDStroj: TIntegerField; dsZahajeniVyrobyPalety: TDataSource; vtZahajeniVyrobyPalety: TFDMemTable; IntegerField1: TIntegerField; StringField1: TStringField; StringField2: TStringField; StringField3: TStringField; vtZahajeniVyrobyPaletycolIDPrikaz: TIntegerField; vtZahajeniVyrobyPaletycolRada: TStringField; vtZahajeniVyrobyPaletycolPrikaz: TIntegerField; vtZahajeniVyrobyPaletycolDatPlanZahajeni: TDateField; vtZahajeniVyrobyPaletycolMJ: TStringField; vtZahajeniVyrobyPaletycolIDStroj: TIntegerField; dsPaletListy: TDataSource; vtPaletListy: TFDMemTable; vtPaletListycolPaletList: TStringField; vtPaletListycolDatPorizeni: TDateTimeField; vtPaletListycolPoznamka: TStringField; dsZasobyObjednavky: TDataSource; vtZasobyObjednavky: TFDMemTable; vtZasobyObjednavkycolIDKmen: TIntegerField; vtZasobyObjednavkycolSZ: TStringField; vtZasobyObjednavkycolRC: TStringField; vtZasobyObjednavkycolNazev1: TStringField; vtZasobyObjednavkycolMJ: TStringField; vtZasobyObjednavkycolBaleni1: TIntegerField; vtZasobyObjednavkycolBaleni2: TIntegerField; vtZasobyObjednavkycolVyroba: TFloatField; vtZasobyObjednavkycolNaCeste: TFloatField; vtZasobyObjednavkycolSklad: TFloatField; vtZasobyObjednavkycolCelkem: TFloatField; vtZasobyObjednavkycolObjednano: TFloatField; vtZasobyObjednavkycolPalet: TSmallintField; dsObjednavkaMat: TDataSource; vtObjednavkaMat: TFDMemTable; vtObjednavkaMatcolCisloObj: TIntegerField; vtObjednavkaMatcolMnozstvi: TFloatField; vtObjednavkaMatcolPalet: TSmallintField; vtObjednavkaMatcolDodano: TFloatField; vtObjednavkaMatcolPalet2: TSmallintField; vtObjednavkaMatcolDatum: TDateField; vtObjednavkaMatcolObjednatel: TStringField; vtObjednavkaMatcolNeaktivni: TStringField; vtObjednavkaMatcolSplneno: TStringField; vtObjednavkaMatcolStornovano: TStringField; vtObjednavkaMatcolUzavreno: TStringField; vtObjednavkaMatcolVratka: TStringField; vtObjednavkaMatcolPoznamka: TStringField; dsOdpady: TDataSource; vtOdpady: TFDMemTable; vtOdpadycolID: TIntegerField; vtOdpadycolDatum: TDateField; vtOdpadycolSmena: TSmallintField; vtOdpadycolMokry: TFloatField; vtOdpadycolSuchy: TFloatField; vtOdpadycolPoznamka: TStringField; vtOdpadycolStroj: TStringField; dsSeznamPalet: TDataSource; vtSeznamPalet: TFDMemTable; vtSeznamPaletcolID: TIntegerField; vtSeznamPaletcolPaletovyList: TStringField; vtSeznamPaletcolDatum: TDateField; vtSeznamPaletcolSmena: TSmallintField; vtSeznamPaletcolDatPorizeni: TDateTimeField; vtSeznamPaletcolPocetKA: TIntegerField; vtVyrobniDenikcolUdalost: TStringField; vtVyrobniDenikcolMJ: TStringField; vtVyrobniDenikcolMnoz2: TFloatField; vtSeznamPaletcolSZ: TStringField; vtSeznamPaletcolRC: TStringField; vtSeznamPaletcolNazev1: TStringField; vtSeznamPaletcolMnozstvi: TFloatField; dsPrehledMicharna: TDataSource; vtPrehledMicharna: TFDMemTable; dsDetailMicharna: TDataSource; vtDetailMicharna: TFDMemTable; vtDetailMicharnacolSZ: TStringField; vtDetailMicharnacolRC: TStringField; vtDetailMicharnacolNazev1: TStringField; vtDetailMicharnacolMnozstvi: TFloatField; vtDetailMicharnacolPridano: TFloatField; vtPrehledMicharnacolIDPrikaz: TIntegerField; vtPrehledMicharnacolIDStroj: TIntegerField; vtPrehledMicharnacolStroj: TStringField; vtPrehledMicharnacolTesto: TStringField; vtPrehledMicharnacolStartMichani: TDateTimeField; vtPrehledMicharnacolKonecMichani: TDateTimeField; vtPrehledMicharnacolViskozita: TFloatField; vtPrehledMicharnacolViskozita2: TFloatField; vtPrehledMicharnacolIDPMZ: TIntegerField; vtPrehledMicharnacolIDOperStartStop: TIntegerField; dsSpravaVzorku: TDataSource; vtSpravaVzorku: TFDMemTable; vtSpravaVzorkucolID: TIntegerField; vtSpravaVzorkucolIDArchiv: TIntegerField; vtSpravaVzorkucolArchiv: TStringField; vtSpravaVzorkucolStroj: TStringField; vtSpravaVzorkucolSZ: TStringField; vtSpravaVzorkucolRC: TStringField; vtSpravaVzorkucolNazev1: TStringField; vtSpravaVzorkucolKArchivaci: TBooleanField; vtSpravaVzorkucolUkonceno: TBooleanField; vtSpravaVzorkucolVyrazeno: TBooleanField; vtSpravaVzorkucolDatPorizeni: TDateTimeField; vtSpravaVzorkucolMistr: TStringField; vtSeznamPaletcolSklad: TStringField; vtRozpracPLfSZ: TStringField; vtRozpracPLfRC: TStringField; vtRozpracPLfRadaPrikaz: TStringField; vtPlanDnesfMnozZive: TFloatField; vtStrojefSarze: TStringField; vtPrehledMicharnacolIDEvidRozpracOper: TIntegerField; vtRozpracPLfIDPrikaz: TIntegerField; dsSarze: TDataSource; vtSarze: TFDMemTable; vtSarzecolSarze: TStringField; vtSarzecolSarze2: TStringField; vtSarzecolMnoz: TFloatField; vtSarzecolIDKmen: TIntegerField; dsMaterial: TDataSource; vtMaterial: TFDMemTable; vtMaterialcolMnoz: TFloatField; vtMaterialcolIDKmen: TIntegerField; vtMaterialRegCis: TStringField; vtMaterialcolSZ: TStringField; vtMaterialcolNazev1: TStringField; vtMaterialcolMJ: TStringField; vtMaterialcolIDPrKVazba: TIntegerField; vtMaterialcolMamSarzi: TBooleanField; vtMaterialcolCisloZbozi: TStringField; vtMaterialcolIDPohyb: TIntegerField; vtSarzecolIDPohybOZ: TIntegerField; vtStrojefIDPaleta: TIntegerField; vtPlanZitrafIDPrikaz: TIntegerField; vtPlanZitrafHodOd: TSmallintField; vtPlanZitrafHodDo: TSmallintField; vtPlanZitrafMnozstvi: TFloatField; procedure DataModuleCreate (Sender: TObject); procedure vtCCP1BeforeDelete (DataSet: TDataSet); procedure vtCCP1BeforeInsert (DataSet: TDataSet); private public dmCreated: boolean; Helios: IHelios; function VratStroj (const Helios: IHelios; cast: string; const AID: integer): string; function VratNazevTabulky (tabName: string; inclSchema: Boolean = False): string; function PocetStrojuSVyrobou: integer; function apiVratServerReq (const url: string; typ: string; body: TStream): string; function apiVratPaletoveListy (idKmen: integer): string; // procedure DrawTextRotated(ACanvas: TCanvas; Angle, X, Y: Integer; ATextColor: TColor; AText: String); procedure UkonciEvidRozpracOper (const idEvROp: integer; var errMsg: string); procedure NactiVyrobuMimoPlan (const f: TformVyrobaMimoPlan); procedure NactiZasobaObjednavky (const f: TformZasobaObjednavky); procedure NactiObjednavkyPolozky (const idKmen: integer); procedure NactiZahajeniVyrobyPalety (const f: TformZahajeniVyrobyPalety); procedure NactiPredvyrobu (const f: TformPredvyroba); procedure NactiPredvyrobuDetail (const f: TformPredvyrobaDetail; idKmen: integer); procedure NactiDataPaneluStroje (const f: TformPekarna; var a: TArray); procedure NactiStrojeAPraci (const f: TformPekarna); procedure NactiVyrobniPoznamky (const f: TformPekarna); procedure NactiCCP1 (const f: TformKontrolaCCP1); procedure NactiCCP1ProNoveZaznamy (const f: TformKontrolaCCP1edt); procedure NactiPlanStroje (const f: TformPekarna; const idStroj: integer; dnes: boolean=true); procedure NactiRozpracovane (const f: TformPekarna; const idStroj: integer); procedure NactiOdpadStroje (const f: TformPekarna); procedure NactiOdpady (const f: TformOdpady); procedure NactiVyrobkyNestandard (const f: TformPrijemNestandard); procedure NactiPaletoveListy (const typ: integer); function ZapisVyrobenePaletyNestandard (const f: TformPrijemNestandard; idKmen: integer; idStroj: integer): boolean; procedure NactiVyrobniDenik (f: TformVyrobniDenik; den: TDateTime); procedure NactiSeznamPalet (f: TformSeznamPalet; const typ: byte; IdKmen, IdStroj: integer; const dat: TDatetime); procedure NactiVytvorenaTesta (f: TformPrehledMicharna); procedure NactiDetailTesta (f: TformPrehledMicharna; idEvidRozpracOper: integer; idOperStartStop: integer); procedure NactiVzorky (f: TformSpravaVzorku); procedure NactiMaterialyProSarze (idVydejka: integer; sIdPohybyOZ: string); procedure NactiSarzeProMaterial (idKmen: integer; idPohybOZ: integer); end; function getFirstWord (s: string): string; function GetDBGridIndexByFieldName (aGrd: TJvDBGrid; aFld: string; jenVisible: boolean): integer; function getDomainName (myURL: string): string; // function GetDBGridIndexByFieldName (aGrid: TDBGrid; const fldName: string): integer; procedure DrawTextRotated (ACanvas: TCanvas; Angle, X, Y: Integer; ATextColor: TColor; AText: String); var dm: Tdm; apiMod: boolean; apiServer, phServer, compName, url, sqlLang: string; vyrobniDen: TDate; aktIdStroj, aktIdKmen, aktIdPrikaz, aktIDPaleta, aktIdKmenPlan, aktIdKmenRozprac, idZamMistr, cisZamMistr: integer; bmIdStroj, bmIdPlanDnes, bmIdPrikaz: integer; // bookmarky aktZakazka: string; {%CLASSGROUP 'Vcl.Controls.TControl'} implementation uses System.Variants, System.StrUtils, Winapi.Windows, System.DateUtils, IdHTTP, System.JSON, System.JSON.Writers, IdSSLOpenSSL, IdURI, System.RegularExpressions, helTabsBIDs, helUtils; {$R *.dfm} {$IF CompilerVersion>=34} // Sydney a vys class operator recStrojZaznam.Initialize (out Dest: recStrojZaznam); begin Dest.id:= 0; Dest.stroj:= ''; Dest.aktualPL:= ''; Dest.zakazka:= ''; Dest.idKmen:= 0; Dest.ccp1:= false; end; {$ENDIF} { function GetDBGridIndexByFieldName (aGrid: Vcl.TDBGrid; const fldName: string): integer; var i: integer; begin for i:=0 to aGrid.Columns.Count-1 do if (aGrid.Columns.Items[i].FieldName=fldName) then result:= i; end; } procedure DrawTextRotated (ACanvas: TCanvas; Angle, X, Y: Integer; ATextColor: TColor; AText: String); var NewX: Integer; NewY: integer; Escapement: Integer; LogFont: TLogFont; NewFontHandle: HFONT; OldFontHandle: HFONT; begin if not Assigned(ACanvas) then Exit; // Get handle of font and prepare escapement GetObject(ACanvas.Font.Handle, SizeOf(LogFont), @LogFont); if Angle > 360 then Angle := 0; Escapement := Angle * 10; // We must initialise all fields of the record structure LogFont.lfWidth := 0; LogFont.lfHeight := ACanvas.Font.Height; LogFont.lfEscapement := Escapement; LogFont.lfOrientation := 0; if fsBold in ACanvas.Font.Style then LogFont.lfWeight := FW_BOLD else LogFont.lfWeight := FW_NORMAL; LogFont.lfItalic := Byte(fsItalic in ACanvas.Font.Style); LogFont.lfUnderline := Byte(fsUnderline in ACanvas.Font.Style); LogFont.lfStrikeOut := Byte(fsStrikeOut in ACanvas.Font.Style); LogFont.lfCharSet := ACanvas.Font.Charset; LogFont.lfOutPrecision := OUT_DEFAULT_PRECIS; LogFont.lfClipPrecision := CLIP_DEFAULT_PRECIS; LogFont.lfQuality := DEFAULT_QUALITY; LogFont.lfPitchAndFamily := DEFAULT_PITCH; StrPCopy(LogFont.lfFaceName, ACanvas.Font.Name); // Create new font with rotation NewFontHandle := CreateFontIndirect(LogFont); try // Set color of text ACanvas.Font.Color := ATextColor; // Select the new font into the canvas OldFontHandle := SelectObject(ACanvas.Handle, NewFontHandle); try // Output result ACanvas.Brush.Style := VCL.Graphics.bsClear; try ACanvas.TextOut(X, Y, AText); finally ACanvas.Brush.Style := VCL.Graphics.bsSolid; end; finally // Restore font handle NewFontHandle := SelectObject(ACanvas.Handle, OldFontHandle); end; finally // Delete the deselected font object DeleteObject(NewFontHandle); end; end; function getFirstWord (s: string): string; begin result:= s; if (s.Contains(' ')) then result:= LeftStr(s, s.IndexOf(' ')).Trim; end; function GetDBGridIndexByFieldName (aGrd: TJvDBGrid; aFld: string; jenVisible: boolean): integer; var i, ii: integer; begin result:= -1; i:= -1; ii:= -1; while (i'') and (url<>'') then begin hc:= TIdHTTP.Create(nil); try try if (typ='') or (typ.ToLower='xml') then hc.Request.Accept:= 'application/soap+xml'; if (typ.ToLower='json') then hc.Request.Accept:= 'application/json'; hc.Request.ContentType:= hc.Request.Accept + '; charset=utf-8'; hc.HTTPOptions:= hc.HTTPOptions + [hoKeepOrigProtocol] + [hoNoProtocolErrorException]; resp:= hc.Post(url, body); except on E:Exception do begin Helios.Error('Chyba API req: ' + E.Message); end; end; except on E:Exception do begin end; end; hc.Free; end; end; function Tdm.apiVratPaletoveListy (idKmen: Integer): string; var reqBody: TStream; jFin: TJSONObject; begin result:= ''; jFin:= TJSONObject.Create; jFin.AddPair (TJSONPair.Create('idKmen', idKmen)); try result:= apiVratServerReq('/vratPL', 'json', TStringStream.Create(jFin.Value, TEncoding.UTF8)); except end; jFin.Free; end; procedure Tdm.NactiSarzeProMaterial (idKmen: Integer; idPohybOZ: integer); var lSQL: string; lHTTP: TIdHTTP; ssl: TIdSSLIOHandlerSocketOpenSSL; lParamList, respStream: TStringStream; resp, jsonTemp, val1, val2: string; i_loop: integer; joOut, joIn, itemsObj: TJSONObject; ja: TJSONArray; jv: TJSONValue; begin if not(vtSarze.Active) then vtSarze.Open; Exit; vtSarze.EmptyDataset; try // http klient je stejny lHTTP:= TIdHTTP.Create; if (datMod.phServer.Contains('https')) then begin ssl:= TIdSSLIOHandlerSocketOpenSSL.Create(nil); ssl.SSLOptions.Method:= sslvTLSv1_2; ssl.SSLOptions.Mode:= sslmUnassigned; lHTTP.IOHandler:= ssl; end; lHTTP.HTTPOptions:= [hoKeepOrigProtocol, hoForceEncodeParams, hoNoProtocolErrorException, hoWantProtocolErrorContent]; lHTTP.Request.ContentType := 'application/ld+json'; lHTTP.Request.Accept := 'application/ld+json, text/javascript, */*; q=0.01'; // param je stejny lParamList:= TStringStream.Create(''); joOut:= TJSONObject.Create; lSQL:= 'SELECT SkupZbo, RegCis FROM ' + tblKZ + ' WHERE ID=' + idKmen.ToString; with Helios.OpenSQL(lSQL) do begin joOut.AddPair('skupzbo', VarToStr(FieldByNameValues('SkupZbo'))); joOut.AddPair('regcis', VarToStr(FieldByNameValues('RegCis'))); end; joOut.AddPair('id', 0); lParamList.WriteString(joOut.ToString); jsonTemp:= '{"@context":"/api/contexts/Order","@id":"/api/orders/14","@type":"Order","id":14,"heliosId":null,"name":"Cukr krupice - big-bag - VPEK","skupzbo":"101","regcis":' + '"101002","paletovyList":"2010100200005","quantity":"4260.0000","items":[{"@id":"/api/material_order_items/12","@type":"MaterialOrderItem","id":12,"stockItem":{"@id":' + '"/api/stock_items/16","@type":"StockItem","id":16,"heliosId":40,"heliosPrijemkaId":1940},"stockItemFinal":null,"order":"/api/orders/14","name":"Cukr krupice - big-bag' + ' - VPEK","sarze":"645901","paletovyList":"2010100200005","quantity":"4260","dateExp":"2030-08-15T00:00:00+02:00","isCompleted":false,"completedAt":null}],"isCompleted"' + ':false,"isUserCompleted":false,"completedAt":null}'; respStream:= TStringStream.Create(''); lHTTP.Post (datMod.phServer + '/getbatch', lParamList, respStream); resp:= respStream.DataString.Replace('\/', '/'); // resp:= jsonText; if (resp<>'') and (resp.IndexOf('batches')>0) then begin joIn:= TJSONObject.ParseJSONValue(TEncoding.UTF8.GetBytes(resp), 0) as TJSONObject; joIn.TryGetValue('batch', val1); if (joIn.TryGetValue('id', ja) and (ja.Count > 0)) then begin for i_loop:=0 to ja.Count-1 do begin val2:= ja.Items[i_loop].GetValue('id'); if (val2<>'') then begin dm.vtSarze.Append; dm.vtSarze.FieldByName('colSarze').AsString:= ''; dm.vtSarze.FieldByName('colSarze2').AsString:= ''; dm.vtSarze.FieldByName('colMnoz').AsExtended:= 0; dm.vtSarze.FieldByName('colIDKmen').AsInteger:= idKmen; dm.vtSarze.Post; end; end; end; end; finally lPAramList.Free; joOut.Free; if (ssl<>nil) then ssl.Free; lHTTP.Free; end; end; procedure Tdm.NactiMaterialyProSarze (idVydejka: integer; sIdPohybyOZ: string); var lSQL: string; begin if not(vtMaterial.Active) then vtMaterial.Open; vtMaterial.EmptyDataset; lSQL:= 'SELECT p.ID, k.ID AS IDKmen, p.SkupZbo, p.RegCis, p.Nazev1, k.MJEvidence, p.Mnozstvi, k.CisloZbozi FROM ' + tblPZ + ' p INNER JOIN ' + tblSS + ' s ON (s.ID=p.IDZboSklad) INNER JOIN '; lSQL:= lSQL + tblKZ + ' k ON (k.ID=s.IDKmenZbozi) INNER JOIN ' + tblKZe + ' ke ON (ke.ID=k.ID) WHERE ke._Vyroba_ZadavatSarzi=1 AND '; if (sIdPohybyOZ<>'') then lSQL:= lSQL + 'p.ID IN (' + sIdPohybyOZ + ')' else lSQL:= lSQL + 'p.IDDoklad=' + idVydejka.ToString; lSQL:= lSQL + ' ORDER BY k.CisloZbozi'; with Helios.OpenSQL(lSQL) do if (RecordCount>0) then begin First; while not(EOF) do begin vtMaterial.Append; vtMaterial.FieldByName('colIDPohyb').AsString:= VarToStr(FieldByNameValues('ID')); vtMaterial.FieldByName('colIDKmen').AsString:= VarToStr(FieldByNameValues('IDKmen')); vtMaterial.FieldByName('colSZ').AsString:= VarToStr(FieldByNameValues('SkupZbo')); vtMaterial.FieldByName('colRegCis').AsString:= VarToStr(FieldByNameValues('RegCis')); vtMaterial.FieldByName('colNazev1').AsString:= VarToStr(FieldByNameValues('Nazev1')); vtMaterial.FieldByName('colMJ').AsString:= VarToStr(FieldByNameValues('MJEvidence')); vtMaterial.FieldByName('colCisloZbozi').AsString:= VarToStr(FieldByNameValues('CisloZbozi')); vtMaterial.FieldByName('colMnoz').AsExtended:= VarToStr(FieldByNameValues('Mnozstvi')).Replace('.', ',').ToExtended; vtMaterial.FieldByName('colMamSarzi').AsBoolean:= false; vtMaterial.Post; Next; end; end; vtMaterial.First; end; procedure Tdm.NactiVzorky (f: TformSpravaVzorku); var lSQL: string; begin if not(vtSpravaVzorku.Active) then vtSpravaVzorku.Open; vtSpravaVzorku.EmptyDataset; lSQL:= 'SELECT v.ID, v.IDArchiv, dbo.ef_Bit2Int(v.KArchivaci) AS KArchivaci, dbo.ef_Bit2Int(v.Ukonceno) AS Ukonceno, dbo.ef_Bit2Int(v.Vyrazeno) AS Vyrazeno, v.DatPorizeni'; lSQL:= lSQL + ', va.Archiv, k.SkupZbo, k.RegCis, k.Nazev1, s.Nazev, z.Prijmeni FROM ' + tblVzorky; lSQL:= lSQL + ' v INNER JOIN ' + tblVzorkyArchiv + ' va ON (va.Id=v.IdArchiv) INNER JOIN ' + tblCStroju + ' s ON (s.Id=v.IdStroj)'; lSQL:= lSQL + ' INNER JOIN ' + tblKZ + ' k ON (k.Id=v.IdKmenZbozi) INNER JOIN ' + tblCisZam + ' z ON (z.Cislo=v.CisloZamMistr)'; lSQL:= lSQL + ' ORDER BY v.DatPorizeni DESC'; with Helios.OpenSQL(lSQL) do if (RecordCount>0) then begin First; while not(EOF) do begin vtSpravaVzorku.Append; vtSpravaVzorku.FieldByName('colID').AsString:= VarToStr(FieldByNameValues('ID')); vtSpravaVzorku.FieldByName('colIDArchiv').AsString:= VarToStr(FieldByNameValues('IDArchiv')); vtSpravaVzorku.FieldByName('colKArchivaci').AsBoolean:= VarToStr(FieldByNameValues('KArchivaci')).ToBoolean; vtSpravaVzorku.FieldByName('colUkonceno').AsBoolean:= VarToStr(FieldByNameValues('Ukonceno')).ToBoolean; vtSpravaVzorku.FieldByName('colVyrazeno').AsBoolean:= VarToStr(FieldByNameValues('Vyrazeno')).ToBoolean; vtSpravaVzorku.FieldByName('colDatPorizeni').AsString:= VarToStr(FieldByNameValues('DatPorizeni')); vtSpravaVzorku.FieldByName('colArchiv').AsString:= VarToStr(FieldByNameValues('Archiv')); vtSpravaVzorku.FieldByName('colSZ').AsString:= VarToStr(FieldByNameValues('SkupZbo')); vtSpravaVzorku.FieldByName('colRC').AsString:= VarToStr(FieldByNameValues('RegCis')); vtSpravaVzorku.FieldByName('colNazev1').AsString:= VarToStr(FieldByNameValues('Nazev1')); vtSpravaVzorku.FieldByName('colStroj').AsString:= VarToStr(FieldByNameValues('Nazev')); vtSpravaVzorku.FieldByName('colMistr').AsString:= VarToStr(FieldByNameValues('Prijmeni')); vtSpravaVzorku.Post; Next; end; end; vtSpravaVzorku.First; f.grdSpravaVzorku.Invalidate; end; procedure Tdm.NactiVytvorenaTesta (f: TformPrehledMicharna); var lSQL, datStr, idVPrV, idOperStartStop, idEvidRozpracOper, strojCil: string; v1, v2: Extended; mamExt: boolean; begin if not(vtPrehledMicharna.Active) then vtPrehledMicharna.Open; vtPrehledMicharna.EmptyDataset; mamExt:= helUtils.SQLObjectExists(Helios, tblPMZE); lSQL:= 'SELECT pmz.ID, pmz.IDPrikaz, pmz.IDStroje, s.Nazev, k.Nazev1, ISNULL(pmz.DatumZahajeniOp, pmz.Datum) AS DatStart, pmz.DatumUkonceniOp AS DatKonec'; lSQL:= lSQL + ', p.IDPrikazVyssi, ISNULL(oss.IDStroj, 0) AS IDStrojCil, ISNULL(oss.ID, 0) AS IDOperStartStop, ISNULL(oss.IDEvidRozpracOper,0) AS IDEvidRozpracOper'; if (mamExt) then lSQL:= lSQL + ', ISNULL(pmze._TestoViskozita,-1) AS Viskozota1, ISNULL(pmze._TestoViskozita2,-1) AS Viskozota2'; lSQL:= lSQL + ' FROM ' + tblPMZ + ' pmz INNER JOIN ' + tblKZ + ' k ON (k.ID=pmz.IDTabKmen)'; lSQL:= lSQL + ' LEFT JOIN ' + tblCStroju + ' s ON (s.ID=pmz.IDStroje) LEFT JOIN ' + tblOperaceStartStop + ' oss ON (oss.IDMzda=pmz.ID)'; lSQL:= lSQL + ' INNER JOIN ' + tblVPr + ' p ON (p.ID=pmz.IDPrikaz)'; if (mamExt) then lSQL:= lSQL + ' LEFT JOIN ' + tblPMZe + ' pmze ON (pmze.ID=pmz.ID)'; lSQL:= lSQL + ' WHERE k.SkupZbo=N''701'' ORDER BY ISNULL(DatumZahajeniOp, pmz.Datum) DESC'; with Helios.OpenSQL(lSQL) do if (RecordCount>0) then begin First; while not(EOF) do begin idVPrV:= VarToStr(FieldByNameValues('IDPrikazVyssi')); strojCil:= VarToStr(FieldByNameValues('IDStrojCil')); vtPrehledMicharna.Append; vtPrehledMicharna.FieldByName('colIDPMZ').AsString:= VarToStr(FieldByNameValues('ID')); vtPrehledMicharna.FieldByName('colIDPrikaz').AsString:= VarToStr(FieldByNameValues('IDPrikaz')); idOperStartStop:= VarToStr(FieldByNameValues('IDOperStartStop')); if (idOperStartStop<>'') and (idOperStartStop<>'0') then vtPrehledMicharna.FieldByName('colIDOperStartStop').AsString:= idOperStartStop; idEvidRozpracOper:= VarToStr(FieldByNameValues('IDEvidRozpracOper')); if (idEvidRozpracOper='') then idEvidRozpracOper:= '0'; vtPrehledMicharna.FieldByName('colIDEvidRozpracOper').AsString:= idEvidRozpracOper; vtPrehledMicharna.FieldByName('colIDStroj').AsString:= strojCil; if (strojCil<>'') and (strojCil<>'0') then vtPrehledMicharna.FieldByName('colStroj').AsString:= helUtils.getHeliosStrVal(Helios, '', 'SELECT Nazev FROM ' + tblCStroju + ' WHERE ID=' + strojCil); // vtPrehledMicharna.FieldByName('colIDStroj').AsString:= VarToStr(FieldByNameValues('IDStroje')); // vtPrehledMicharna.FieldByName('colStroj').AsString:= VarToStr(FieldByNameValues('Nazev')); vtPrehledMicharna.FieldByName('colTesto').AsString:= VarToStr(FieldByNameValues('Nazev1')); vtPrehledMicharna.FieldByName('colStartMichani').AsString:= VarToStr(FieldByNameValues('DatStart')); vtPrehledMicharna.FieldByName('colKonecMichani').AsString:= VarToStr(FieldByNameValues('DatKonec')); if (mamExt) then begin v1:= VarToStr(FieldByNameValues('Viskozota1')).ToExtended; v2:= VarToStr(FieldByNameValues('Viskozota2')).ToExtended; if (v1>0) then vtPrehledMicharna.FieldByName('colViskozita').AsExtended:= v1; if (v2>0) then vtPrehledMicharna.FieldByName('colViskozita2').AsExtended:= v2; end; vtPrehledMicharna.Post; Next; end; end; vtPrehledMicharna.First; f.grdSeznamTesta.Invalidate; end; procedure Tdm.NactiDetailTesta (f: TformPrehledMicharna; idEvidRozpracOper: integer; idOperStartStop: integer); var lSQL, datStr, idDZ: string; begin if not(vtDetailMicharna.Active) then vtDetailMicharna.Open; vtDetailMicharna.EmptyDataset; idDZ:= helUtils.getHeliosStrVal(Helios, '0', 'SELECT IDDokladOZ FROM ' + tblOperaceStartStop + ' WHERE IDDokladOZ IS NOT NULL AND ID=' + idOperStartStop.ToString); if (idDZ<>'') then begin lSQL:= 'SELECT p.SkupZbo, p.RegCis, p.Nazev1, p.Mnozstvi, ISNULL(v.Mnozstvi-v.MnozstviPuvodni,0) AS Pridano FROM ' + tblPZ + ' p LEFT JOIN ' + tblOperaceStartStopVydej; lSQL:= lSQL + ' v ON (v.IDPohybZbozi=p.ID) WHERE p.IDDoklad=' + idDZ + ' ORDER BY p.Poradi'; with Helios.OpenSQL(lSQL) do begin First; while not(EOF) do begin vtDetailMicharna.Append; vtDetailMicharna.FieldByName('colSZ').AsString:= VarToStr(FieldByNameValues('SkupZbo')); vtDetailMicharna.FieldByName('colRC').AsString:= VarToStr(FieldByNameValues('RegCis')); vtDetailMicharna.FieldByName('colNazev1').AsString:= VarToStr(FieldByNameValues('Nazev1')); vtDetailMicharna.FieldByName('colMnozstvi').AsExtended:= VarToStr(FieldByNameValues('Mnozstvi')).Replace('.', ',').ToExtended; vtDetailMicharna.FieldByName('colPridano').AsExtended:= VarToStr(FieldByNameValues('Pridano')).Replace('.', ',').ToExtended; vtDetailMicharna.Post; Next; end; end; end; vtDetailMicharna.First; f.grdReceptura.Invalidate; end; procedure Tdm.NactiSeznamPalet (f: TformSeznamPalet; const typ: byte; IdKmen, IdStroj: integer; const dat: TDatetime); var lSQL, datStr, sz: string; mn, ksPerKA, KAPerPal: Extended; datPoriz: TDateTime; begin if not(vtSeznamPalet.Active) then vtSeznamPalet.Open; vtSeznamPalet.EmptyDataset; lSQL:= ''; if (typ=1) then begin sz:= helUtils.getHeliosStrVal(Helios, '', 'SELECT SkupZbo FROM ' + tblKZ + ' WHERE ID=' + IdKmen.ToString); ksPerKA:= helUtils.getHeliosFloatVal(Helios, 0, 'SELECT PocetOdvozene FROM ' + tblMJZbo + ' WHERE IDKmenZbozi=' + idKmen.ToString + ' AND KodMJ1=N''KA'' AND KodMJ2=N''Ks'''); KAPerPal:= helUtils.getHeliosFloatVal(Helios, 0, 'SELECT PocetHlavni FROM ' + tblMJZbo + ' WHERE IDKmenZbozi=' + idKmen.ToString + ' AND KodMJ1=N''KA'' AND KodMJ2=N''Pal'''); lSQL:= 'SELECT p.ID, p.Mnozstvi, vcs.Nazev1, ISNULL(vcp.Mnozstvi,-1) AS MnozVCP, p.DatPorizeni, s.IDKmenZbozi AS IDKmen, ISNULL(s.Mnozstvi,0) AS MnozSklad FROM ' + tblPZ; lSQL:= lSQL + ' p INNER JOIN ' + tblSS + ' s ON (s.ID=p.IDZboSklad) INNER JOIN ' + tblDZ + ' d ON (d.ID=p.IDDoklad)'; lSQL:= lSQL + ' LEFT JOIN ' + tblVyrCP + ' vcp ON (vcp.IDPolozkaDokladu=p.ID) INNER JOIN ' + tblVyrCS + ' vcs ON (vcs.ID=vcp.IDVyrCis) WHERE p.DruhPohybuZbo=0'; lSQL:= lSQL + ' AND p.TypVyrobnihoDokladu=0' + IfThen(idKmen>0, ' AND s.IDKmenZbozi=' + IdKmen.ToString, ''); if (dat>0) then begin lSQL:= lSQL + ' AND p.DatPorizeni>=CONVERT(datetime, N' + (FormatDateTime('dd.mm.yyyy', dat.GetDate) + ' 06:00:00').QuotedString + ', 104)'; lSQL:= lSQL + ' AND p.DatPorizeni<=CONVERT(datetime, N' + (FormatDateTime('dd.mm.yyyy', IncDay(dat.GetDate)) + ' 05:59:59').QuotedString + ', 104)'; end; lSQL:= lSQL + ' ORDER BY p.DatPorizeni DESC'; end; if (lSQL<>'') then with Helios.OpenSQL(lSQL) do if (RecordCount>0) then begin First; while not(EOF) do begin vtSeznamPalet.Append; datStr:= VarToStr(FieldByNameValues('DatPorizeni')); if not(TryStrToDateTime(datStr, datPoriz)) then datPoriz:= 0; IdKmen:= VarToStr(FieldByNameValues('IDKmen')).ToInteger; with Helios.OpenSQL('SELECT SkupZbo, RegCis, Nazev1 FROM ' + tblKZ + ' WHERE ID=' + IdKmen.ToString) do begin vtSeznamPalet.FieldByName('colSZ').AsString:= VarToStr(FieldByNameValues('SkupZbo')); vtSeznamPalet.FieldByName('colRC').AsString:= VarToStr(FieldByNameValues('RegCis')); vtSeznamPalet.FieldByName('colNazev1').AsString:= VarToStr(FieldByNameValues('Nazev1')); end; ksPerKA:= helUtils.getHeliosFloatVal(Helios, 0, 'SELECT PocetOdvozene FROM ' + tblMJZbo + ' WHERE IDKmenZbozi=' + idKmen.ToString + ' AND KodMJ1=N''KA'' AND KodMJ2=N''Ks'''); KAPerPal:= helUtils.getHeliosFloatVal(Helios, 0, 'SELECT PocetHlavni FROM ' + tblMJZbo + ' WHERE IDKmenZbozi=' + idKmen.ToString + ' AND KodMJ1=N''KA'' AND KodMJ2=N''Pal'''); vtSeznamPalet.FieldByName('colID').AsInteger:= VarToStr(FieldByNameValues('ID')).ToInteger; vtSeznamPalet.FieldByName('colPaletovyList').AsString:= VarToStr(FieldByNameValues('Nazev1')); vtSeznamPalet.FieldByName('colDatum').AsDateTime:= datPoriz.GetDate; vtSeznamPalet.FieldByName('colSklad').AsString:= VarToStr(FieldByNameValues('MnozSklad')); lSQL:= 'DECLARE @dat DATETIME' + CRLF + 'SET @dat=' + helUtils.SQLDatetimeConvert (datPoriz, false) + CRLF + 'SELECT Smena FROM dbo.ef_GetDatumASmenu (@dat)'; vtSeznamPalet.FieldByName('colSmena').AsInteger:= helUtils.getHeliosIntVal(Helios, 1, lSQL); vtSeznamPalet.FieldByName('colDatPorizeni').AsDateTime:= datPoriz; mn:= VarToStr(FieldByNameValues('MnozVCP')).ToExtended; if (mn=-1) then begin vtSeznamPalet.FieldByName('colPocetKA').AsExtended:= VarToStr(FieldByNameValues('Mnozstvi')).ToExtended; vtSeznamPalet.FieldByName('colMnozstvi').AsExtended:= VarToStr(FieldByNameValues('Mnozstvi')).ToExtended; end else begin vtSeznamPalet.FieldByName('colPocetKA').AsExtended:= mn; vtSeznamPalet.FieldByName('colMnozstvi').AsExtended:= mn * ksPerKA; end; vtSeznamPalet.Post; Next; end; end; vtSeznamPalet.First; f.grdSeznamPalet.Invalidate; end; procedure Tdm.NactiOdpady (const f: TformOdpady); var lSQL: string; begin lSQL:= 'SELECT o.ID, o.OdpadSuchy, o.OdpadMokry, o.Poznamka, s.Nazev, CONVERT(datetime, CONVERT(int, CONVERT(float, o.Datum))) AS Datum, o.Smena FROM ' + tblOdpadPek; lSQL:= lSQL + ' o INNER JOIN ' + tblCStroju + ' s ON (o.IDStroje=s.ID) WHERE o.IDStroje='; lSQL:= lSQL + aktIdStroj.ToString + ' ORDER BY o.DatPorizeni DESC'; with Helios.OpenSQL(lSQL) do begin if (not(vtOdpady.Active)) then vtOdpady.Open; vtOdpady.EmptyDataset; First; while not(EOF) do begin vtOdpady.Append; vtOdpady.FieldByName('colID').AsInteger:= VarToStr(FieldByNameValues('ID')).ToInteger; vtOdpady.FieldByName('colStroj').AsString:= VarToStr(FieldByNameValues('Nazev')); vtOdpady.FieldByName('colDatum').AsString:= VarToStr(FieldByNameValues('Datum')); vtOdpady.FieldByName('colPoznamka').AsString:= VarToStr(FieldByNameValues('Poznamka')); vtOdpady.FieldByName('colSmena').AsInteger:= VarToStr(FieldByNameValues('Smena')).ToInteger; vtOdpady.FieldByName('colMokry').AsExtended:= VarToStr(FieldByNameValues('OdpadMokry')).ToExtended; vtOdpady.FieldByName('colSuchy').AsExtended:= VarToStr(FieldByNameValues('OdpadSuchy')).ToExtended; vtOdpady.Post; Next; end; end; vtOdpady.First; f.grdOdpady.Invalidate; end; procedure Tdm.NactiOdpadStroje (const f: TformPekarna); var lSQL: string; begin lSQL:= 'SELECT OdpadSuchy, OdpadMokry, Poznamka FROM ' + tblOdpadPek + ' WHERE IDStroje=' + aktIdStroj.ToString + ' AND Datum=dbo.hf_TruncDate(GETDATE()) AND Smena=' + f.edtOdpadSmena.Text; with Helios.OpenSQL(lSQL) do if (RecordCount=1) then begin f.edtOdpadSuchy.Value:= VarToStr(FieldByNameValues('OdpadSuchy')).ToExtended; f.edtOdpadMokry.Value:= VarToStr(FieldByNameValues('OdpadMokry')).ToExtended; f.edtOdpadPoznamka.Text:= VarToStr(FieldByNameValues('Poznamka')); end else begin f.edtOdpadSuchy.Value:= 0; f.edtOdpadMokry.Value:= 0; end; end; procedure Tdm.NactiObjednavkyPolozky (const idKmen: integer); var lSQL: string; idObj: integer; begin if not(vtObjednavkaMat.Active) then vtObjednavkaMat.Open; vtObjednavkaMat.EmptyDataset; lSQL:= 'SELECT o.ID, o.SkupZbo, o.RegCis, k.Nazev1, o.Mnozstvi, dbo.hf_TruncDate(o.DatPorizeni) AS DatPorizeni, o.Poznamka, o.Autor FROM ' + tblObjednavky; lSQL:= lSQL + ' o INNER JOIN ' + tblKZ + ' k ON (k.ID=o.IdKmenZbozi) WHERE k.ID=' + idKmen.toString; lSQL:= lSQL + ' ORDER BY o.DatPorizeni'; with Helios.OpenSQL(lSQL) do begin First; while not(EOF) do begin idObj:= VarToStr(FieldByNameValues('ID')).ToInteger; vtObjednavkaMat.Append; vtObjednavkaMat.FieldByName('colCisloObj').AsInteger:= idObj; vtObjednavkaMat.FieldByName('colMnozstvi').AsExtended:= VarToStr(FieldByNameValues('Mnozstvi')).ToExtended; vtObjednavkaMat.FieldByName('colDatum').AsString:= VarToStr(FieldByNameValues('DatPorizeni')); vtObjednavkaMat.FieldByName('colPoznamka').AsString:= VarToStr(FieldByNameValues('Poznamka')); vtObjednavkaMat.FieldByName('colObjednatel').AsString:= VarToStr(FieldByNameValues('Autor')); lSQL:= 'SELECT SUM(pzV.Mnozstvi) FROM ' + tblObjednavky + ' o INNER JOIN ' + tblObjednavkyPol + ' po ON (po.IDHlava=o.ID) INNER JOIN '; lSQL:= lSQL + tblPZ + ' pzV ON (pzV.ID=po.IDPohybOZ AND pzV.DruhPohybuZbo=4) INNER JOIN ' + tblPZ + ' pzP ON (pzV.ID=pzP.IdOldPolozka AND pzP.DruhPohybuZbo=0)'; lSQL:= lSQL + ' WHERE pzP.SkutecneDatReal IS NOT NULL AND o.ID=' + idObj.ToString; vtObjednavkaMat.FieldByName('colDodano').AsExtended:= helUtils.getHeliosFloatVal(Helios, 0, lSQL); vtObjednavkaMat.Post; Next; end; end; vtObjednavkaMat.IndexFieldNames:= 'colCisloObj:D'; vtObjednavkaMat.First; end; procedure Tdm.NactiZasobaObjednavky (const f: TformZasobaObjednavky); var lSQL, regCis: string; cnt, idKmen: integer; begin if not(vtZasobyObjednavky.Active) then vtZasobyObjednavky.Open; vtZasobyObjednavky.EmptyDataset; { lSQL:= 'SELECT ID, SkupZbo, RegCis, Nazev1, MJEvidence FROM ' + tblKZ + ' WHERE Material=1 OR ID IN (SELECT IDKmenZbozi FROM ' + tblSS + ' WHERE IDSklad IN (N''211'',N''311''))'; lSQL:= lSQL + ' OR ID IN (SELECT IDKmenZbozi FROM ' + tblObjednavky + ') ORDER BY Nazev1'; } lSQL:= 'SELECT k.ID, k.SkupZbo, k.RegCis, k.Nazev1, k.MJEvidence FROM ' + tblKZ + ' k INNER JOIN ' + tblKZe + ' ke ON (ke.ID=k.ID)' + ' WHERE ke._Vyroba_ProObjednavku=1 AND k.ID NOT IN (SELECT ID FROM ' + tblKZ + ' WHERE SkupZbo=N''101'' AND RegCis IN (N''111000'', N''112000''))' + ' AND k.ID IN (SELECT IDKmenZbozi FROM ' + tblSS + ' WHERE Mnozstvi>0 AND IDSklad=N''311'')'; with Helios.OpenSQL(lSQL) do begin helUtils.waitSetProgBarMax(RecordCount); helUtils.waitSetProgBar(0); cnt:= 0; First; while not(EOF) do begin idKmen:= VarToStr(FieldByNameValues('ID')).ToInteger; vtZasobyObjednavky.Append; vtZasobyObjednavky.FieldByName('colIDKmen').AsInteger:= idKmen; vtZasobyObjednavky.FieldByName('colSZ').AsString:= VarToStr(FieldByNameValues('SkupZbo')); regCis:= VarToStr(FieldByNameValues('RegCis')); if (regCis='012003') then lSQL:= ''; vtZasobyObjednavky.FieldByName('colRC').AsString:= regCis; vtZasobyObjednavky.FieldByName('colNazev1').AsString:= VarToStr(FieldByNameValues('Nazev1')); vtZasobyObjednavky.FieldByName('colMJ').AsString:= VarToStr(FieldByNameValues('MJEvidence')); vtZasobyObjednavky.FieldByName('colSklad').AsExtended:= helUtils.getHeliosFloatVal(Helios, 0, 'SELECT Mnozstvi FROM ' + tblSS + ' WHERE IDSklad=N''311'' AND IDKmenZbozi=' + idKmen.ToString); lSQL:= 'SELECT SUM(pP.Mnozstvi) FROM ' + tblPZ + ' pV INNER JOIN ' + tblPZ + ' pP ON (pP.IDOldPolozka=pV.ID AND pP.DruhPohybuZbo=0) WHERE pP.SkutecneDatReal IS NULL'; lSQL:= lSQL + ' AND pV.ID (SELECT IDPohybOZ FROM ' + tblObjednavkyPol + ' WHERE IDHlava IN (SELECT ID FROM ' + tblObjednavky + ' WHERE IDKmenZbozi=' + idKmen.ToString + '))'; vtZasobyObjednavky.FieldByName('colNaCeste').AsExtended:= helUtils.getHeliosFloatVal(Helios, 0, lSQL); lSQL:= 'SELECT ISNULL(SUM(o.Mnozstvi),0) FROM ' + tblObjednavky + ' o WHERE o.IDKmenZbozi=' + idKmen.ToString; lSQL:= lSQL + ' AND NOT EXISTS(SELECT 1 FROM ' + tblObjednavkyPol + ' WHERE IDHlava=o.ID)'; // lSQL:= lSQL + ' OR EXISTS (SELECT 1 FROM ' + tblObjednavkyPol + ' op INNER JOIN ' + tblPZ + ' p ON (op.IDPohybOZ=p.ID) WHERE p.DruhPohybuZbo=4 AND p.SkutecneDatReal IS NULL AND op.IDHlava=o.ID) vtZasobyObjednavky.FieldByName('colObjednano').AsExtended:= helUtils.getHeliosFloatVal(Helios, 0, lSQL); vtZasobyObjednavky.Post; Inc(cnt); if (cnt mod 20=0) then helUtils.waitSetProgBar(cnt); Next; end; end; helUtils.waitEnd; vtZasobyObjednavky.First; f.grdZasobaObjednavky.Invalidate; end; procedure Tdm.NactiZahajeniVyrobyPalety (const f: TformZahajeniVyrobyPalety); var lSQL, lSQL2: string; cnt, idPrikaz, idKmen, idStroj: integer; ksVKA, KAnaPal: extended; begin if not(vtZahajeniVyrobyPalety.Active) then vtZahajeniVyrobyPalety.Open; vtZahajeniVyrobyPalety.EmptyDataset; lSQL:= 'SELECT p.ID, p.Rada, p.Prikaz, p.Plan_zadani, k.ID AS idKZ, k.SkupZbo, k.RegCis, k.Nazev1, k.MJEvidence FROM ' + tblVPr + ' p INNER JOIN ' + tblKZ + ' k ON (k.ID=p.IDTabKmen)'; lSQL:= lSQL + ' WHERE p.StavPrikazu=30 ORDER BY p.Plan_zadani'; with Helios.OpenSQL(lSQL) do begin helUtils.waitSetProgBarMax(RecordCount); helUtils.waitSetProgBar(0); cnt:= 0; First; while not(EOF) do begin idPrikaz:= VarToStr(FieldByNameValues('ID')).ToInteger; idKmen:= VarToStr(FieldByNameValues('idKZ')).ToInteger; vtZahajeniVyrobyPalety.Append; vtZahajeniVyrobyPalety.FieldByName('colIDPrikaz').AsInteger:= idPrikaz; vtZahajeniVyrobyPalety.FieldByName('colIDKmen').AsInteger:= idKmen; vtZahajeniVyrobyPalety.FieldByName('colRada').AsString:= VarToStr(FieldByNameValues('Rada')); vtZahajeniVyrobyPalety.FieldByName('colPrikaz').AsInteger:= VarToStr(FieldByNameValues('Prikaz')).ToInteger; vtZahajeniVyrobyPalety.FieldByName('colDatPlanZahajeni').AsDateTime:= StrToDateTime(VarToStr(FieldByNameValues('Plan_zadani'))); vtZahajeniVyrobyPalety.FieldByName('colSZ').AsString:= VarToStr(FieldByNameValues('SkupZbo')); vtZahajeniVyrobyPalety.FieldByName('colRegCis').AsString:= VarToStr(FieldByNameValues('RegCis')); vtZahajeniVyrobyPalety.FieldByName('colNazev1').AsString:= VarToStr(FieldByNameValues('Nazev1')); vtZahajeniVyrobyPalety.FieldByName('colMJ').AsString:= VarToStr(FieldByNameValues('MJEvidence')); if (helUtils.SQLObjectExists(Helios, 'dbo.ef_DilecGetStroj')) then begin idStroj:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT dbo.ef_DilecGetStroj (' + idKmen.ToString + ', default)'); if (idStroj>0) then vtZahajeniVyrobyPalety.FieldByName('colIDStroj').AsInteger:= idStroj; end; vtZahajeniVyrobyPalety.Post; Inc(cnt); if (cnt mod 20=0) then helUtils.waitSetProgBar(cnt); Next; end; end; helUtils.waitEnd; vtZahajeniVyrobyPalety.First; f.grdZahajeniVyrobyPalety.Invalidate; end; procedure Tdm.NactiVyrobuMimoPlan (const f: TformVyrobaMimoPlan); var lSQL, lSQL2: string; cnt, idKmen, idStroj: integer; ksVKA, KAnaPal: extended; begin if not(vtVyrobaMimoPlan.Active) then vtVyrobaMimoPlan.Open; vtVyrobaMimoPlan.EmptyDataset; lSQL:= 'SELECT ID, SkupZbo, RegCis, Nazev1, MJEvidence FROM ' + tblKZ + ' WHERE Dilec=1 AND ISNULL(TRY_PARSE(SkupZbo AS INT),0) BETWEEN 703 AND 799'; // lSQL:= lSQL + ' SkupZbo LIKE N''7%'' AND SkupZbo NOT LIKE N''70%'' AND SkupZbo NOT LIKE N''8%'''; lSQL:= lSQL + ' ORDER BY Nazev1'; with Helios.OpenSQL(lSQL) do begin helUtils.waitSetProgBarMax(RecordCount); helUtils.waitSetProgBar(0); cnt:= 0; First; while not(EOF) do begin idKmen:= VarToStr(FieldByNameValues('ID')).ToInteger; vtVyrobaMimoPlan.Append; vtVyrobaMimoPlan.FieldByName('colIDKmen').AsInteger:= idKmen; vtVyrobaMimoPlan.FieldByName('colSZ').AsString:= VarToStr(FieldByNameValues('SkupZbo')); vtVyrobaMimoPlan.FieldByName('colRegCis').AsString:= VarToStr(FieldByNameValues('RegCis')); vtVyrobaMimoPlan.FieldByName('colNazev1').AsString:= VarToStr(FieldByNameValues('Nazev1')); vtVyrobaMimoPlan.FieldByName('colMJ').AsString:= VarToStr(FieldByNameValues('MJEvidence')); lSQL2:= 'SELECT PocetOdvozene FROM ' + tblMJZbo + ' WHERE IDKmenZbozi=' + idKmen.ToString + ' AND KodMJ1=''KA'' AND KodMJ2=N''ks'''; ksVKA:= helUtils.getHeliosFloatVal(Helios, 0, lSQL2); vtVyrobaMimoPlan.FieldByName('colBaleni1').AsExtended:= ksVKA; lSQL2:= 'SELECT PocetHlavni FROM ' + tblMJZbo + ' WHERE IDKmenZbozi=' + idKmen.ToString + ' AND KodMJ1=''KA'' AND KodMJ2=N''pal'''; KAnaPal:= helUtils.getHeliosFloatVal(Helios, 0, lSQL2); vtVyrobaMimoPlan.FieldByName('colBaleni2').AsExtended:= KAnaPal; vtVyrobaMimoPlan.FieldByName('colBaleni3').AsExtended:= ksVKA * KAnaPal; if (helUtils.SQLObjectExists(Helios, 'dbo.ef_DilecGetStroj')) then begin idStroj:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT dbo.ef_DilecGetStroj (' + idKmen.ToString + ', default)'); if (idStroj>0) then begin vtVyrobaMimoPlan.FieldByName('colIDStroj').AsInteger:= idStroj; vtVyrobaMimoPlan.FieldByName('colStroj').AsString:= helUtils.getHeliosStrVal(Helios, '', 'SELECT Nazev FROM ' + tblCStroju + ' WHERE ID=' + idStroj.ToString); end; end; lSQL2:= 'SELECT 1 FROM ' + tblKVaz + ' WHERE vyssi=' + idKmen.ToString + ' AND nizsi IN (SELECT ID FROM ' + tblKZ + ' WHERE SkupZbo LIKE N''70%'')'; if (helUtils.sqlExistsTestGeneral(Helios, lSQL2)) then begin lSQL2:= 'SELECT TOP(1) n.Nazev1 FROM ' + tblKVaz + ' v INNER JOIN ' + tblKZ + ' n ON (n.ID=v.nizsi) WHERE v.vyssi=' + idKmen.ToString + ' AND n.SkupZbo LIKE N''70%'''; lSQL2:= lSQL2 + ' AND n.MJEvidence LIKE N''1000%'''; vtVyrobaMimoPlan.FieldByName('colZaklad').AsString:= helUtils.getHeliosStrVal(Helios, '', lSQL2); end; vtVyrobaMimoPlan.Post; Inc(cnt); if (cnt mod 20=0) then helUtils.waitSetProgBar(cnt); Next; end; end; helUtils.waitEnd; vtVyrobaMimoPlan.First; f.grdVyrobaMimoPlan.Invalidate; end; procedure Tdm.NactiPredvyrobuDetail (const f: TformPredvyrobaDetail; idKmen: Integer); var lSQL, dVyr: string; idPZ, pocetVC: integer; ksVKA: extended; begin lSQL:= 'SELECT PocetOdvozene FROM ' + tblMJZbo + ' WHERE IDKmenZbozi=' + idKmen.ToString + ' AND KodMJ1=''KA'' AND KodMJ2=N''ks'''; ksVKA:= helUtils.getHeliosFloatVal(Helios, 0, lSQL); if not(vtPredvyrobaDetail.Active) then vtPredvyrobaDetail.Open; vtPredvyrobaDetail.EmptyDataset; lSQL:= 'SELECT p.ID, k.SkupZbo, k.RegCis, k.Nazev1, p.Mnozstvi, p.MJ, FORMAT(p.DatPorizeni, ''dd.MM.yyyy hh:mm'', ''de-de'') AS DatPorizeni FROM ' + tblPZ; lSQL:= lSQL + ' p INNER JOIN ' + tblSS + ' s ON (s.ID=p.IDZboSklad) INNER JOIN ' + tblKZ + ' k ON (k.ID=s.IDKmenZbozi)'; LSQL:= lSQL + ' WHERE k.ID=' + idKmen.ToString + ' ORDER BY p.DatPorizeni DESC'; with Helios.OpenSQL(lSQL) do begin First; while not(EOF) do begin vtPredvyrobaDetail.Append; idPZ:= VarToStr(FieldByNameValues('ID')).ToInteger; vtPredvyrobaDetail.FieldByName('colIDPZ').AsInteger:= idPZ; vtPredvyrobaDetail.FieldByName('colSZ').AsString:= VarToStr(FieldByNameValues('SkupZbo')); vtPredvyrobaDetail.FieldByName('colRegCis').AsString:= VarToStr(FieldByNameValues('RegCis')); vtPredvyrobaDetail.FieldByName('colNazev1').AsString:= VarToStr(FieldByNameValues('Nazev1')); vtPredvyrobaDetail.FieldByName('colKA').AsExtended:= VarToStr(FieldByNameValues('Mnozstvi')).ToExtended; vtPredvyrobaDetail.FieldByName('colKs').AsExtended:= VarToStr(FieldByNameValues('Mnozstvi')).ToExtended * ksVKA; dVyr:= VarToStr(FieldByNameValues('DatPorizeni')); vtPredvyrobaDetail.FieldByName('colDatumVyroby').AsString:= dVyr; if (dVyr<>'') then vtPredvyrobaDetail.FieldByName('colStav').AsInteger:= 0; pocetVC:= helUtils.getHeliosIntVal (Helios, 0, 'SELECT COUNT(ID) FROM ' + tblVyrCP + ' WHERE IDPolozkaDokladu=' + idPZ.ToString); if (pocetVC=1) then begin lSQL:= 'SELECT TOP(1) vcs.Nazev1 FROM ' + tblVyrCP + ' vcp INNER JOIN ' + tblVyrCS + ' vcs ON (vcs.ID=vcp.IDVyrCis) WHERE vcp.IDPolozkaDokladu=' + idPZ.ToString; vtPredvyrobaDetail.FieldByName('colCisloPL').AsString:= helUtils.getHeliosStrVal (Helios, '', lSQL); end; vtPredvyrobaDetail.Post; Next; end; end; vtPredvyrobaDetail.First; f.grdPredvyrobaDetail.Invalidate; end; procedure Tdm.NactiPredvyrobu (const f: TformPredvyroba); var lSQL, lSQL2, stroj: string; idKZ: integer; bal: Extended; begin if not(vtPredvyroba.Active) then vtPredvyroba.Open; vtPredvyroba.EmptyDataset; lSQL:= 'SELECT s.IDKmenZbozi, k.SkupZbo, k.RegCis, k.Nazev1, k.MJEvidence FROM ' + tblSS + ' s INNER JOIN ' + tblKZ + ' k ON (k.ID=s.IDKmenZbozi) WHERE s.IDSklad=N''211'''; lSQL:= lSQL + ' AND k.ID IN (SELECT ID FROM ' + tblKZ + ' WHERE Nazev1 LIKE N''%polotovar%'') ORDER BY k.CisloZbozi'; with Helios.OpenSQL(lSQL) do begin First; while not(EOF) do begin idKZ:= VarToStr(FieldByNameValues('IDKmenZbozi')).ToInteger; vtPredvyroba.Append; vtPredvyroba.FieldByName('colIDKmen').AsInteger:= idKZ; vtPredvyroba.FieldByName('colSZ').AsString:= VarToStr(FieldByNameValues('SkupZbo')); vtPredvyroba.FieldByName('colRegCis').AsString:= VarToStr(FieldByNameValues('RegCis')); vtPredvyroba.FieldByName('colNazev1').AsString:= VarToStr(FieldByNameValues('Nazev1')); vtPredvyroba.FieldByName('colMJ').AsString:= VarToStr(FieldByNameValues('MJEvidence')); lSQL2:= 'SELECT PocetOdvozene FROM ' + tblMJZbo + ' WHERE IDKmenZbozi=' + idKZ.ToString + ' AND KodMJ1=''KA'' AND KodMJ2=N''ks'''; bal:= helUtils.getHeliosFloatVal(Helios, 0, lSQL2); vtPredvyroba.FieldByName('colBaleni').AsExtended:= bal; lSQL2:= 'SELECT PocetHlavni FROM ' + tblMJZbo + ' WHERE IDKmenZbozi=' + idKZ.ToString + ' AND KodMJ1=''KA'' AND KodMJ2=N''pal'''; bal:= helUtils.getHeliosFloatVal(Helios, 0, lSQL2); vtPredvyroba.FieldByName('colBaleni2').AsExtended:= bal; vtPredvyroba.Post; Next; end; end; vtPredvyroba.First; f.grdPredvyroba.Invalidate; end; procedure Tdm.UkonciEvidRozpracOper (const idEvROp: Integer; var errMsg: string); var lSQL: string; begin errMsg:= ''; if (idEvROp>0) and (helUtils.HeliosExistsTestSQL(Helios, 'SELECT 1 FROM ' + tblRozpracOper + ' WHERE Stav<30 AND ID=' + idEvROp.ToString)) then begin lSQL:= 'DECLARE @casKonec=GETDATE(), @casStart DATETIME, @s INT' + CRLF + 'SELECT @casStart=CasZahajeni FROM ' + tblRozpracOper + ' WHERE ID=' + idEvROp.ToString + CRLF; lSQL:= lSQL + 'SET @s = DATEDIFF(s, @casStart, @casKonec)' + CRLF; lSQL:= lSQL + 'UPDATE ' + tblRozpracOper + ' SET CasUkonceni=@casKonec, CelkovyCas=ROUND(@s/60.0, 1) WHERE ID=' + idEvROp.ToString + CRLF; lSQL:= lSQL + 'UPDATE ' + tblRozpracOperR + ' SET Sk_cas=@s, Sk_cas_Obsluhy=@s, Mnoz_odv=0 WHERE IDEvidRozpracOper=' + idEvROp.ToString; lSQL:= lSQL + 'EXEC dbo.hp_EvidRozpracOper_Uzavreni @IDEvidRozpracOper=' + idEvROp.ToString + ', @GenerovatEvidenciOperaci=0' + CRLF; try Helios.ExecSQL(lSQL); except on E:Exception do errMsg:= E.Message; end; end; end; procedure Tdm.NactiVyrobniDenik (f: TformVyrobniDenik; den: TDateTime); var lSQL, datReq, mjEvid: string; bidXVyrobniDenik: integer; kaNaPal, ksVKA: integer; mnozKA: Extended; h,m: byte; begin if not(vtVyrobniDenik.Active) then vtVyrobniDenik.Open; vtVyrobniDenik.EmptyDataset; if (helUtils.SQLObjectExists (Helios, constHvwVyrobaVyrobniDenik)) then begin bidXVyrobniDenik:= helUtils.getHeliosIntVal (Helios, 0, 'SELECT DPBID FROM ' + tblObecPrehled + ' WHERE NazevSys=N' + constHvwVyrobaVyrobniDenik.QuotedString); if (bidXVyrobniDenik>0) then begin lSQL:= helUtils.getHeliosStrVal (Helios, '', 'SELECT DefView FROM ' + tblObecPrehled + ' WHERE NazevSys=N' + constHvwVyrobaVyrobniDenik.QuotedString); if (den=0) then den:= Now; lSQL:= lSQL.Replace('1.1.1999', FormatDateTime ('dd.mm.yyyy', den.GetDate)); end; end; // else // Helios.Error(#1'Neexistuje definovaný přehled ' + constHvwVyrobaVyrobniDenik + #1) lSQL:= 'SELECT DISTINCT(s.Nazev) AS Stroj,' + ' dbo.hf_TruncDate(DATEADD(day, IIF(DATEPART(hour, dz.DatPorizeni)<6, -1, 0), dz.DatPorizeni)) AS Datum,' + ' dbo.ef_VyrobniDenik_GetSmenu(dz.DatPorizeni) AS Smena,' + ' k.ID AS idKZ, k.CisloZbozi, pz.RegCis, pz.Nazev1, SUM(pz.Mnozstvi) AS Mnozstvi, pz.MJEvidence' + ' FROM dbo.TabPrikazMzdyAZmetkyGenPZ pmzg' + ' INNER JOIN dbo.TabPrikazMzdyAZmetky pmz ON (pmz.ID=pmzg.IDMzdy)' + ' INNER JOIN ' + tblPZ + ' pz ON (pmzg.IDPohybu=pz.ID)' + ' INNER JOIN ' + tblSS + ' ss ON (ss.ID=pz.IDZboSklad)' + ' INNER JOIN ' + tblKZ + ' k ON (k.ID=ss.IDKmenZbozi)' + ' INNER JOIN ' + tblDZ + ' dz ON (dz.ID=pz.IDDoklad)' + ' INNER JOIN ' + tblCStroju + ' s ON (pmz.IDStroje=s.ID)' + ' WHERE dz.IDSklad IN (N''211'', N''312'')' // sklad pekarna + hlavni sklad vyrobky zbozi + ' AND dz.DruhPohybuZbo=0' + ' AND pz.TypVyrobnihoDokladu=0' + ' AND ((1=1) OR (dbo.hf_TruncDate(DATEADD(day, IIF(DATEPART(hour, dz.DatPorizeni)<6, -1, 0), dz.DatPorizeni))=CONVERT(datetime, N''1.1.1999'', 104) ) )' + ' GROUP BY s.Nazev,' + ' dbo.hf_TruncDate(DATEADD(day, IIF(DATEPART(hour, dz.DatPorizeni)<6, -1, 0), dz.DatPorizeni)),' + ' dbo.ef_VyrobniDenik_GetSmenu(dz.DatPorizeni),' + ' k.ID, k.CisloZbozi, pz.RegCis, pz.Nazev1, pz.MJEvidence'; if (den=0) then den:= Now; lSQL:= lSQL.Replace('1.1.1999', FormatDateTime ('dd.mm.yyyy', den.GetDate)); h:= HourOf(Now); m:= MinuteOf(Now); lSQL:= 'DECLARE @datOd DATETIME, @datDo DATETIME' + CRLF; if (h>=6) then begin lSQL:= lSQL + 'SET @datOd=DATEADD(hour, 6, dbo.hf_TruncDate(' + helUtils.SQLDatetimeConvert(den) + '))' + CRLF; lSQL:= lSQL + 'SET @datDo=DATEADD(hour, 6, dbo.hf_TruncDate(' + helUtils.SQLDatetimeConvert(den+1) + '))' + CRLF; end else begin lSQL:= lSQL + 'SET @datOd=DATEADD(hour, 6, dbo.hf_TruncDate(' + helUtils.SQLDatetimeConvert(den-1) + '))' + CRLF; lSQL:= lSQL + 'SET @datDo=DATEADD(hour, 6, dbo.hf_TruncDate(' + helUtils.SQLDatetimeConvert(den) + '))' + CRLF; end; lSQL:= lSQL + 'SELECT * FROM ('; lSQL:= lSQL + 'SELECT p.Mnozstvi, k.MJEvidence, k.ID AS idKZ, k.CisloZbozi, k.Nazev1, N'''' AS Udalost, p.DatPorizeni, (SELECT Smena FROM dbo.ef_GetDatumASmenu(p.DatPorizeni)) AS Smena'; lSQL:= lSQL + ', (SELECT Datum FROM dbo.ef_GetDatumASmenu(p.DatPorizeni)) AS Datum, st.Nazev AS Stroj FROM ' + tblPZ + ' p'; lSQL:= lSQL + ' INNER JOIN ' + tblSS + ' s ON (s.ID=p.IDZboSklad) INNER JOIN ' + tblKZ + ' k ON (k.ID=s.IDKmenZbozi) LEFT JOIN ' + tblPMZGenPZ + ' pmzg ON (pmzg.IDPohybu=p.ID)'; lSQL:= lSQL + ' LEFT JOIN ' + tblPMZ + ' pmz ON (pmz.ID=pmzg.IDMzdy) LEFT JOIN ' + tblCStroju + ' st ON (st.ID=pmz.IDStroje)'; lSQL:= lSQL + ' WHERE k.Dilec=1 AND p.DatPorizeni BETWEEN @datOd AND @datDo'; // ORDER BY p.DatPorizeni'; lSQL:= lSQL + CRLF + 'UNION ALL' + CRLF; lSQL:= lSQL + 'SELECT 0, N'''', 0, N'''', N'''', c.Cinnost, c.DatPorizeni, (SELECT Smena FROM dbo.ef_GetDatumASmenu(c.DatPorizeni)) AS Smena'; lSQL:= lSQL + ', (SELECT Datum FROM dbo.ef_GetDatumASmenu(c.DatPorizeni)) AS Datum, st.Nazev AS Stroj FROM ' + tblCinnostStr + ' c'; lSQL:= lSQL + ' LEFT JOIN ' + tblCStroju + ' st ON (st.ID=c.IDStroje)'; lSQL:= lSQL + ' WHERE c.DatPorizeni BETWEEN @datOd AND @datDo'; // ORDER BY c.DatPorizeni'; lSQL:= lSQL + ') x ORDER BY x.DatPorizeni'; if (lSQL<>'') then begin with Helios.OpenSQL(lSQL) do if (RecordCount>0) then begin First; while not(EOF) do begin mjEvid:= VarToStr(FieldByNameValues('MJEvidence')); idKZ:= VarToStr(FieldByNameValues('idKZ')).ToInteger; mnozKA:= VarToStr(FieldByNameValues('Mnozstvi')).ToExtended; kaNaPal:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT PocetHlavni FROM ' + tblMJZbo + ' WHERE IDKmenZbozi=' + idKZ.ToString + ' AND KodMJ1=N''KA'' AND KodMJ2=N''pal'' AND PocetOdvozene=1'); ksVKA:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT PocetOdvozene FROM ' + tblMJZbo + ' WHERE IDKmenZbozi=' + idKZ.ToString + ' AND KodMJ1=N''KA'' AND KodMJ2=N''Ks'' AND PocetHlavni=1'); vtVyrobniDenik.Append; vtVyrobniDenik.FieldByName('colCisloZbozi').AsString:= VarToStr(FieldByNameValues('CisloZbozi')); vtVyrobniDenik.FieldByName('colMJ').AsString:= mjEvid; vtVyrobniDenik.FieldByName('colUdalost').AsString:= VarToStr(FieldByNameValues('Udalost')); vtVyrobniDenik.FieldByName('colNazev1').AsString:= VarToStr(FieldByNameValues('Nazev1')); vtVyrobniDenik.FieldByName('colStroj').AsString:= VarToStr(FieldByNameValues('Stroj')); vtVyrobniDenik.FieldByName('colDatum').AsString:= VarToStr(FieldByNameValues('Datum')); vtVyrobniDenik.FieldByName('colSmena').AsInteger:= VarToStr(FieldByNameValues('Smena')).ToInteger; vtVyrobniDenik.FieldByName('colMnozKA').AsExtended:= 0; vtVyrobniDenik.FieldByName('colMnozPal').AsExtended:= 0; if (mjEvid='KA') then begin vtVyrobniDenik.FieldByName('colMnozKA').AsExtended:= mnozKA; vtVyrobniDenik.FieldByName('colMnozPal').AsExtended:= (mnozKA / kaNaPal); vtVyrobniDenik.FieldByName('colMnoz').AsExtended:= mnozKA * ksVKA; end; vtVyrobniDenik.Post; Next; end; end; end; vtVyrobniDenik.First; f.grdVyrobniDenik.Invalidate; end; function Tdm.ZapisVyrobenePaletyNestandard (const f: TformPrijemNestandard; idKmen: integer; idStroj: integer): boolean; var lSQL: string; begin result:= true; lSQL:= 'DROP TABLE IF EXISTS #TabPrijemNestandard' + CRLF + 'CREATE TABLE #TabPrijemNestandard (IDKmen INT, MnozstviKg NUMERIC(19,6) NOT NULL DEFAULT 0.0, PaletovyList NVARCHAR(50)'; lSQL:= lSQL + ', Sarze NVARCHAR(50), DatumVyroby DATETIME, DatumExpirace DATETIME, Smena TINYINT, IDStroje INT)' + CRLF; lSQL:= lSQL + 'INSERT #TabPrijemNestandard (IDKmen, MnozstviKg, PaletovyList, Sarze, DatumVyroby, DatumExpirace, Smena, IDStroje) SELECT ' + idKmen.ToString; lSQL:= lSQL + ', ' + f.edtKA.Value.ToString.Replace(',', '.') + ', N' + f.edtPaletList.Text.Trim.QuotedString + ', N' + f.edtSarze.Text.Trim.QuotedString; lSQL:= lSQL + ', ' + helUtils.SQLDatetimeConvert (f.edtDatVyroba.DateTime, false) + ', ' + helUtils.SQLDatetimeConvert (f.edtDatSpotreba.DateTime, false); lSQL:= lSQL + ', ' + f.edtSmena.Text.Trim + ', ' + idStroj.ToString + CRLF; lSQL:= lSQL + 'IF OBJECT_ID(N''dbo.ep_Vyroba_PrijemNestandard'', N''P'') IS NOT NULL EXEC dbo.ep_Vyroba_PrijemNestandard'; try Helios.ExecSQL(lSQL); except on E: Exception do begin result:= false; Helios.Error(#1'Chyba zápisu příjmu nestandardu:'#1 + CRLF + E.Message); end; end; end; procedure Tdm.NactiPaletoveListy (const typ: Integer); var lSQL: string; begin if not(vtPaletListy.Active) then vtPaletListy.Open; vtPaletListy.EmptyDataset; lSQL:= ''; if (typ=1) then lSQL:= ''; if (lSQL<>'') then with (Helios.OpenSQL(lSQL)) do begin First; while not(EOF) do begin vtPaletListy.Append; vtPaletListy.FieldByName('colPaletList').AsString:= ''; vtPaletListy.FieldByName('colDatPorizeni').AsString:= VarToStr(FieldByNameValues('SkupZbo')); vtPaletListy.FieldByName('colPoznamka').AsString:= ''; vtPaletListy.Post; Next; end; vtPaletListy.First; end; vtPaletListy.First; end; procedure Tdm.NactiVyrobkyNestandard (const f: TformPrijemNestandard); var lSQL: string; begin if not(vtPrijemNestandard.Active) then vtPrijemNestandard.Open; vtPrijemNestandard.EmptyDataset; lSQL:= 'SELECT ID, SkupZbo, RegCis, Nazev1, MJEvidence FROM ' + tblKZ + ' WHERE Dilec=1 AND SkupZbo NOT IN (N''701'') AND Nazev1 LIKE N''%nestandard%'' ORDER BY CisloZbozi'; with (f.Helios.OpenSQL(lSQL)) do begin First; while not(EOF) do begin vtPrijemNestandard.Append; vtPrijemNestandard.FieldByName('fID').AsString:= VarToStr(FieldByNameValues('ID')); vtPrijemNestandard.FieldByName('fSZ').AsString:= VarToStr(FieldByNameValues('SkupZbo')); vtPrijemNestandard.FieldByName('fRegCis').AsString:= VarToStr(FieldByNameValues('RegCis')); vtPrijemNestandard.FieldByName('fNazev1').AsString:= VarToStr(FieldByNameValues('Nazev1')); vtPrijemNestandard.Post; Next; end; vtPrijemNestandard.First; end; end; procedure Tdm.DataModuleCreate(Sender: TObject); begin dmCreated:= true; end; function Tdm.VratStroj (const Helios: IHelios; cast: string; const AID: integer): string; begin cast:= sqlSanitize(cast); result:= helUtils.getHeliosStrVal(Helios, '', 'SELECT ' + cast + ' FROM ' + tblCStroju + ' WHERE ID=' + AID.ToString); end; procedure Tdm.NactiPlanStroje (const f: TformPekarna; const idStroj: integer; dnes: boolean=true); var lSQL: string; begin if (idStroj>0) then begin lSQL:= 'SELECT FROM ' + tblAdvKPlanDavky + ' WHERE DatumPripadu=' + IfThen(dnes, 'GETDATE()', ''); end; if (idStroj>0) then begin lSQL:= 'SELECT ID FROM ' + tblVPr + ' WHERE StavPrikazu IN (20,30) AND ID NOT IN (SELECT r.IDPrikaz FROM ' + tblRozpracOper + ' h INNER JOIN ' + tblRozpracOperR; lSQL:= lSQL + ' r ON (r.IDEvidRozpracOper=h.ID) INNER JOIN ' + tblCPrac + ' pr ON (pr.ID=h.IDPracoviste) WHERE h.Stav<30)'; lSQL:= lSQL + ' AND Rada NOT IN (SELECT Rada FROM ' + tblPrikazRada + ' WHERE nazev LIKE N''%údržb%'')'; lSQL:= 'SELECT vp.ID, k.ID AS idKZ, k.RegCis, k.SkupZbo + N'' '' + k.RegCis AS CisloZbozi2, k.Nazev1, dbo.hf_TruncDate(vp.Plan_zadani) AS Plan_zadani, vp.kusy_zad,' + ' vp.kusy_zive, vp.kusy_odved, ISNULL(z.CisloZakazky,N'''') AS CisloZakazky, vp.RadaPrikaz FROM ' + tblVPr + ' vp INNER JOIN ' + tblKZ + ' k ON (k.ID=vp.IDTabKmen) LEFT JOIN ' + tblZak + ' z ON (z.ID=vp.IDZakazka) WHERE k.SkupZbo<>N''701'' AND vp.ID IN (' + lSQL + ')' // + tblZak + ' z ON (z.ID=vp.IDZakazka) WHERE k.SkupZbo NOT LIKE N''70%'' AND vp.ID IN (' + lSQL + ')' + ' AND EXISTS(SELECT 1 FROM ' + tblPrPost + ' WHERE IDPrikaz=vp.ID AND IdOdchylkyDo IS NULL AND IDStroje=' + idStroj.ToString +') ORDER BY vp.Plan_zadani'; vtPlanDnes.DisableControls; try with (f.Helios.OpenSQL(lSQL)) do begin First; if not(vtPlanDnes.Active) then vtPlanDnes.Open; vtPlanDnes.EmptyDataset; aktIdKmenPlan:= 0; while not(EOF) do // prikazy co nejsou nastartovane, ale zadane do vyroby begin vtPlanDnes.Append; vtPlanDnes.FieldByName('fIDPlan').AsString:= VarToStr(FieldByNameValues('ID')); // ID prikaz vtPlanDnes.FieldByName('fRegCis').AsString:= VarToStr(FieldByNameValues('CisloZbozi2')); vtPlanDnes.FieldByName('fVyrobek').AsString:= VarToStr(FieldByNameValues('Nazev1')); vtPlanDnes.FieldByName('fHotovo').AsExtended:= VarToStr(FieldByNameValues('kusy_odved')).ToExtended; vtPlanDnes.FieldByName('fMnozZive').AsExtended:= VarToStr(FieldByNameValues('kusy_zive')).ToExtended; vtPlanDnes.FieldByName('fZakazka').AsString:= VarToStr(FieldByNameValues('RadaPrikaz')).Replace(' ', ''); vtPlanDnes.FieldByName('fDatPlanZadani').AsString:= VarToStr(FieldByNameValues('Plan_zadani')); vtPlanDnes.Post; Next; end; end; except on E:Exception do lSQL:= E.Message; end; if (datMod.bmIdPlanDnes=0) then vtPlanDnes.First else if not(vtPlanDnes.Locate('fIDPlan', VarArrayOf([datMod.bmIdPlanDnes]), [loPartialKey])) then begin datMod.bmIdPlanDnes:= 0; vtPlanDnes.First; end; vtPlanDnes.EnableControls; end; end; procedure Tdm.NactiRozpracovane (const f: TformPekarna; const idStroj: integer); var lSQL: string; idVPr, idH, idS, idK, radaPrikaz, cisloPL: string; mnVCP, mnZbytVCPP: Extended; i1, i2: integer; begin lSQL:= 'SELECT DISTINCT(vp.ID) FROM ' + tblPrPost + ' prp INNER JOIN ' + tblPrikaz + ' vp ON (vp.ID=prp.IDPrikaz)'; lSQL:= lSQL + ' WHERE vp.StavPrikazu=40 AND prp.IDOdchylkyDo IS NULL AND prp.typ<2 AND prp.IDStroje=' + aktIdStroj.ToString + ' GROUP BY vp.ID'; { lSQL:= 'SELECT e.ID, e.CasZahajeni, e.IDStroje FROM ' + tblRozpracOper + ' e WHERE e.Stav<30 /*AND IDStroje=' + aktIdStroj.ToString + ' */'; lSQL:= lSQL + ' AND e.ID NOT IN (SELECT er.IDEvidRozpracOper FROM ' + tblRozpracOperR + ' er INNER JOIN ' + tblPrikaz + ' p ON (p.ID=er.IDPrikaz) INNER JOIN ' + tblKZ; lSQL:= lSQL + ' k ON (k.ID=p.IDTabKmen) WHERE k.SkupZbo LIKE N''70%'')'; lSQL:= lSQL + 'ORDER BY e.DatPorizeni'; } lSQL:= 'SELECT vp.ID, vp.IDTabKmen FROM ' + tblPrikaz + ' vp INNER JOIN ' + tblKZ + ' kz ON (kz.ID=vp.IDTabKmen) WHERE vp.ID IN (' + lSQL + ') ORDER BY vp.DatPorizeni'; { lSQL:= 'SELECT DISTINCT(vcp.VyrCislo), vcp.Popis, vcp.ID FROM ' + tblRozpracOperR + ' r INNER JOIN ' + tblRozpracOper + ' h ON (h.ID=r.IDEvidRozpracOper) INNER JOIN ' + tblPrikazVC + ' vcp ON (vcp.ID=r.IDVyrCis)' + ' INNER JOIN ' + tblPrikaz + ' vp ON (vcp.IDPrikaz=vp.ID) INNER JOIN ' + tblKZ + ' k ON (k.ID=vp.IDTabKmen) WHERE h.DatumUzavreni IS NULL AND r.IDVyrCis IS NOT NULL GROUP BY vcp.VyrCislo'; } lSQL:= 'SELECT DISTINCT(vp.ID) AS ID, vp.IDTabKmen FROM ' + tblRozpracOperR + ' r INNER JOIN ' + tblRozpracOper + ' h ON (h.ID=r.IDEvidRozpracOper) INNER JOIN ' + tblPrikazVC + ' vcp ON (vcp.ID=r.IDVyrCis)' + ' INNER JOIN ' + tblPrikaz + ' vp ON (vcp.IDPrikaz=vp.ID) INNER JOIN ' + tblKZ + ' k ON (k.ID=vp.IDTabKmen) WHERE h.DatumUzavreni IS NULL AND r.IDVyrCis IS NOT NULL GROUP BY vp.ID, vp.IDTabKmen'; Helios.ExecSQL ('DELETE FROM ' + tblPalety + ' WHERE IDPrikaz NOT IN (SELECT ID FROM ' + tblVPr + ')'); Helios.ExecSQL ('MERGE ' + tblPalety + ' AS T USING ' + tblPaletyUkonceni + ' AS S ON (S.IDPalety=T.ID) WHEN MATCHED AND T.DatUkonceni IS NULL THEN UPDATE SET T.DatUkonceni=S.DatPorizeni;'); lSQL:= 'SELECT CisloPalety, IDPrikaz, DatZahajeni, IDStroj FROM ' + tblPalety + ' WHERE DatZahajeni IS NOT NULL AND DatUkonceni IS NULL'; with (f.Helios.OpenSQL(lSQL)) do begin if not(vtRozpracPL.Active) then vtRozpracPL.Open; vtRozpracPL.EmptyDataset; aktIdKmenRozprac:= 0; First; while not(EOF) do begin cisloPL:= VarToStr(FieldByNameValues('CisloPalety')); idVPr:= VarToStr(FieldByNameValues('IDPrikaz')); if (idVPr<>'') and (helUtils.IsNumeric(idVPr)) then begin if not(helUtils.sqlExistsTestGeneral(Helios, 'SELECT 1 FROM ' + tblVPr + ' WHERE ID=' + idVPr)) then Next; idK:= helUtils.getHeliosStrVal (Helios, '', 'SELECT IDTabKmen FROM ' + tblPrikaz + ' WHERE ID=' + idVPr); vtRozpracPL.Append; vtRozpracPL.FieldByName('fIDPrikaz').AsString:= idVPr; idS:= VarToStr(FieldByNameValues('IDStroj')); vtRozpracPL.FieldByName('fIDStroj').AsString:= idS; vtRozpracPL.FieldByName('fStroj').AsString:= helUtils.getHeliosStrVal(f.Helios, '', 'SELECT Nazev FROM ' + tblCStroju + ' WHERE ID=' + idS); vtRozpracPL.FieldByName('fAktPaletList').AsString:= cisloPL; vtRozpracPL.FieldByName('fDatStart').AsString:= VarToStr(FieldByNameValues('DatZahajeni')); with Helios.OpenSQL('SELECT SkupZbo, RegCis, Nazev1 FROM ' + tblKZ + ' WHERE ID=' + idK) do begin vtRozpracPL.FieldByName('fSZ').AsString:= VarToStr(FieldByNameValues('SkupZbo')); vtRozpracPL.FieldByName('fRC').AsString:= VarToStr(FieldByNameValues('RegCis')); vtRozpracPL.FieldByName('fCisloZbozi').AsString:= vtRozpracPL.FieldByName('fSZ').AsString + ' ' + vtRozpracPL.FieldByName('fRC').AsString; vtRozpracPL.FieldByName('fVyrobek').AsString:= VarToStr(FieldByNameValues('Nazev1')); end; vtRozpracPL.Post; end; Next; end; end; { with (f.Helios.OpenSQL(lSQL)) do begin if not(vtRozpracPL.Active) then vtRozpracPL.Open; vtRozpracPL.EmptyDataset; aktIdKmenRozprac:= 0; First; while not(EOF) do begin idVPr:= VarToStr(FieldByNameValues('ID')); idK:= VarToStr(FieldByNameValues('IDTabKmen')); radaPrikaz:= helUtils.getHeliosStrVal(Helios, '', 'SELECT RadaPrikaz FROM ' + tblPrikaz + ' WHERE ID=' + idVPr); idS:= '0'; if (helUtils.sqlExistsTestGeneral(Helios, 'SELECT 1 FROM ' + tblPrPost + ' WHERE IDPrikaz=' + idVPr + ' AND IdOdchylkyDo IS NULL AND IDStroje=' + datMod.bmIdStroj.ToString)) then idS:= datMod.bmIdStroj.ToString; cisloPL:= ''; mnVCP:= 0; lSQL:= 'SELECT TOP(1) vcs.Nazev1, vcp.Mnozstvi FROM ' + tblPZ + ' p INNER JOIN ' + tblDZ + ' d ON (d.ID=p.IDDoklad) INNER JOIN ' + tblVPr + ' vp ON (vp.ID=p.IDPrikaz) INNER JOIN ' + tblSS; lSQL:= lSQL + ' s ON (s.ID=p.IDZboSklad) INNER JOIN ' + tblVyrCS + ' vcs ON (vcs.IDStavSkladu=s.ID) INNER JOIN ' + tblVyrCP + ' vcp ON (vcp.IDPolozkaDokladu=p.ID AND vcp.IDVyrCis=vcs.ID)'; lSQL:= lSQL + ' WHERE p.DruhPohybuZbo=0 AND vp.ID=' + idVPr + ' AND p.TypVyrobnihoDokladu=0 ORDER BY p.DatPorizeni DESC'; with Helios.OpenSQL(lSQL) do if (RecordCount=1) then begin cisloPL:= VarToStr(FieldByNameValues('Nazev1')); mnVCP:= VarToStr(FieldByNameValues('Mnozstvi')).ToExtended; end; if not(TryStrToInt(cisloPL, i1)) then i1:= 0; mnZbytVCPP:= helUtils.getHeliosFloatVal(Helios, -1, 'SELECT MnozstviZive FROM ' + tblPrikazVC + ' WHERE IDPrikaz=' + idVPr + ' AND VyrCislo=N' + cisloPL.QuotedString); if (mnZbytVCPP<=0) then // nemam zbytek na VC, tj. paleta byla odvedena kompletni, pro rozpracovanost zkus dohledat dalsi v rade begin lSQL:= 'SELECT VyrCislo FROM ' + tblPrikazVC + ' WHERE MnozstviZive>0 AND IDPrikaz=' + idVPr + ' AND ISNULL(TRY_PARSE(VyrCislo AS INT),0)>' + i1.ToString; lSQL:= lSQL + ' ORDER BY TRY_PARSE(VyrCislo AS INT)'; cisloPL:= helUtils.getHeliosStrVal(Helios, '', lSQL); if (cisloPL<>'') then // pokud mam dalsi paletu, kolik je na ni zbytek pro vyrobu ? mnZbytVCPP:= helUtils.getHeliosFloatVal(Helios, -1, 'SELECT MnozstviZive FROM ' + tblPrikazVC + ' WHERE IDPrikaz=' + idVPr + ' AND VyrCislo=N' + cisloPL.QuotedString); end; vtRozpracPL.Append; vtRozpracPL.FieldByName('fIDPrikaz').AsString:= idVPr; vtRozpracPL.FieldByName('fIDEvidRozp').AsString:= ''; vtRozpracPL.FieldByName('fIDStroj').AsString:= idS; vtRozpracPL.FieldByName('fRadaPrikaz').AsString:= radaPrikaz; vtRozpracPL.FieldByName('fStroj').AsString:= helUtils.getHeliosStrVal(f.Helios, '', 'SELECT Nazev FROM ' + tblCStroju + ' WHERE ID=' + idS); vtRozpracPL.FieldByName('fAktPaletList').AsString:= cisloPL; lSQL:= 'SELECT TOP(1) Datum FROM ' + tblZmenLog + ' WHERE Tabulka=N' + getPlainTabName(tblPrikaz).QuotedString + ' AND IDvTab=' + idVPr + ' ORDER BY Datum DESC'; vtRozpracPL.FieldByName('fDatStart').AsString:= helUtils.getHeliosStrVal(Helios, '', lSQL); vtRozpracPL.FieldByName('fColR').AsBoolean:= false; // rozpracovano ? Pokud TRUE, zobrazit tlacitko pro vyrazeni palety z vyroby with Helios.OpenSQL('SELECT SkupZbo, RegCis, Nazev1 FROM ' + tblKZ + ' WHERE ID=' + idK) do begin vtRozpracPL.FieldByName('fSZ').AsString:= VarToStr(FieldByNameValues('SkupZbo')); vtRozpracPL.FieldByName('fRC').AsString:= VarToStr(FieldByNameValues('RegCis')); vtRozpracPL.FieldByName('fCisloZbozi').AsString:= vtRozpracPL.FieldByName('fSZ').AsString + ' ' + vtRozpracPL.FieldByName('fRC').AsString; vtRozpracPL.FieldByName('fVyrobek').AsString:= VarToStr(FieldByNameValues('Nazev1')); end; vtRozpracPL.Post; Next; end; end; } vtRozpracPL.First; f.grdRozpracovane.Invalidate; end; function Tdm.VratNazevTabulky (tabName: string; inclSchema: Boolean = False): string; begin result:= tabName; result:= result.Replace(']', '').Replace('[', ''); if (not inclSchema) then result:= result.Replace('dbo.', ''); end; procedure Tdm.vtCCP1BeforeDelete (DataSet: TDataSet); begin if (True) then System.SysUtils.Abort; { Exclude dgConfirmDelete from DBGrid1.Options } end; procedure Tdm.vtCCP1BeforeInsert (DataSet: TDataSet); begin if (DataSet.Tag=0) then System.SysUtils.Abort; end; function Tdm.PocetStrojuSVyrobou: Integer; var i: integer; begin result:= 0; dm.vtStroje.DisableControls; i:= dm.vtStroje.RecNo; dm.vtStroje.First; while not(dm.vtStroje.Eof) do begin if (dm.vtStroje.FieldByName('fCinnost').AsString='Výroba') then Inc(Result); dm.vtStroje.Next; end; dm.vtStroje.RecNo:= i; dm.vtStroje.EnableControls; end; procedure Tdm.NactiCCP1ProNoveZaznamy (const f: TformKontrolaCCP1edt); var lSQL: string; i, i2, hod, smena: Integer; cas: TDateTime; begin if (apiMod) and (1=0) then begin url:= apiServer + IfThen(apiServer.EndsWith('/'), '', '/') + 'vyroba/ccp1/?den='; end else begin i:= vtStroje.RecNo; vtStroje.DisableControls; i2:= 0; if not(vtStroje.Active) then vtStroje.Open; vtStroje.First; while not(vtStroje.Eof) do begin if (vtStroje.FieldByName('fCinnost').AsString='Výroba') then begin f.grdCCP1.Cells[0, i2]:= vtStroje.FieldByName('fIDStroj').AsString; f.grdCCP1.Cells[13, i2]:= vtStroje.FieldByName('fIDPrikaz').AsString; f.grdCCP1.Cells[1, i2]:= vtStroje.FieldByName('fStroj').AsString; cas:= Now; hod:= HourOf(cas); if (hod in [0..5]) then smena:= 3 else if (hod in [6..13]) then smena:= 1 else if (hod in [14..21]) then smena:= 2 else smena:= 3; f.grdCCP1.Cells[3, i2]:= smena.ToString; // if (smena=3) and (hod<6) then // cas:= IncDay(cas, -1); f.grdCCP1.Cells[2, i2]:= FormatDateTime('dd.mm.yyyy hh:nn', Now); f.grdCCP1.Cells[10, i2]:= vtStroje.FieldByName('fVyrobek').AsString; f.grdCCP1.Invalidate; Inc(i2); end; f.grdCCP1.Refresh; vtStroje.Next; end; vtStroje.RecNo:= i; vtStroje.EnableControls; end; end; procedure Tdm.NactiCCP1 (const f: TformKontrolaCCP1); var lSQL: string; i: Integer; begin if (apiMod) and (1=0) then begin url:= apiServer + IfThen(apiServer.EndsWith('/'), '', '/') + 'vyroba/ccp1/?den='; end else begin lSQL:= 'DECLARE @dat DATETIME=NULL' + CRLF + IfThen(vyrobniDen>0, 'SET @dat=CONVERT(datetime, N' + FormatDateTime('dd.mm.yyyy hh:nn:ss', vyrobniDen).QuotedString + ',104)' + CRLF, ''); lSQL:= lSQL + 'SELECT c.ID, c.Smena, c.Teplota, c.Rychlost, c.Hmotnost, c.Delka, c.Prumer, c.OdpovidaPredpisu, c.NapravneOpatreni, c.Uzavreno, c.Autor, s.Nazev AS NazevStroje'; lSQL:= lSQL + ', c.DatPorizeni, c.IDStroj, k.Nazev1, FORMAT(c.DatPorizeni, N''dd\.MM\.yyyy HH\.mm'') AS DatPorizeniFmt FROM ' + tblCCP1; lSQL:= lSQL + ' c INNER JOIN ' + tblCStroju + ' s ON (s.ID=c.IDStroj) INNER JOIN ' + tblKZ + ' k ON (k.ID=c.IDKmenZbozi)'; if (vyrobniDen>0) then lSQL:= lSQL + ' WHERE c.DatPorizeni_X=dbo.hf_TruncDate(@dat)'; lSQL:= lSQL + ' ORDER BY c.DatPorizeni'; with (f.Helios.OpenSQL(lSQL)) do begin f.grdCCP1.RowCount:= RecordCount + 1; if (RecordCount>0) then begin i:= 0; First; while not(EOF) do begin f.grdCCP1.Cells[0, i]:= VarToStr(FieldByNameValues('IDStroj')); f.grdCCP1.Cells[1, i]:= VarToStr(FieldByNameValues('NazevStroje')); f.grdCCP1.Cells[2, i]:= VarToStr(FieldByNameValues('DatPorizeniFmt')); f.grdCCP1.Cells[3, i]:= VarToStr(FieldByNameValues('Smena')); f.grdCCP1.Cells[4, i]:= VarToStr(FieldByNameValues('Teplota')); f.grdCCP1.Cells[5, i]:= VarToStr(FieldByNameValues('Rychlost')); f.grdCCP1.Cells[6, i]:= VarToStr(FieldByNameValues('Hmotnost')); f.grdCCP1.Cells[7, i]:= VarToStr(FieldByNameValues('Delka')); f.grdCCP1.Cells[8, i]:= VarToStr(FieldByNameValues('Prumer')); f.grdCCP1.Cells[10, i]:= VarToStr(FieldByNameValues('Nazev1')); f.grdCCP1.Cells[11, i]:= VarToStr(FieldByNameValues('NapravneOpatreni')); Inc(i); Next; end; end; end; end; f.grdCCP1.Refresh; end; procedure Tdm.NactiStrojeAPraci (const f: TformPekarna); var lSQL, cisloPL, sarze: string; idStroj, idVPr, cntVz: integer; begin lSQL:= 'SELECT st.ID, p.Pracoviste, st.Nazev, st.Kod FROM ' + tblCStroju + ' st INNER JOIN ' + tblCPrac + ' p ON (p.ID=IDPrac) WHERE p.IDTabStrom=N' + stredPekarna.ToString.QuotedString; lSQL:= lSQL + ' AND p.Blokovano=0 AND st.Blokovano=0 ORDER BY st.Nazev'; dm.vtStroje.DisableControls; if not(vtStroje.Active) then vtStroje.Open; vtStroje.EmptyDataset; bmIdStroj:= aktIdStroj; with Helios.OpenSQL(lSQL) do begin First; while not(EOF) do begin vtStroje.Append; idStroj:= FieldByNameValues('ID'); if (BOF) then datMod.aktIdStroj:= idStroj; vtStroje.FieldByName('fIDStroj').AsString:= idStroj.ToString; vtStroje.FieldByName('fStroj').AsString:= VarToStr(FieldByNameValues('Nazev')); { vtStroje.FieldByName('fCinnost').AsString:= datMod.getFirstWord (helUtils.getHeliosStrVal(Helios, '', 'SELECT TOP(1) Cinnost FROM ' + tblCinnostStr + ' WHERE IDStroje=' + idStroj.ToString + ' ORDER BY DatPorizeni DESC')); } vtStroje.FieldByName('fCinnost').AsString:= helUtils.getHeliosStrVal(Helios, '', 'SELECT TOP(1) Cinnost FROM ' + tblCinnostStr + ' WHERE IDStroje=' + idStroj.ToString + ' AND DatKonec IS NULL ORDER BY DatPorizeni DESC'); vtStroje.FieldByName('fAktPaletList').AsString:= ''; vtStroje.FieldByName('fVyrobenoKA').AsExtended:= 0; lSQL:= 'SELECT TOP(1) e.ID, vp.ID AS idVPr, ISNULL(z.CisloZakazky, N'''') AS CisloZakazky, k.CisloZbozi, k.ID AS IDKmen, k.Nazev1, k.MJEvidence, vp.RadaPrikaz'; lSQL:= lSQL + ', ISNULL(vc.VyrCislo, N'''') AS VyrCislo, ISNULL(eoe._Sarze, N'''') AS Sarze FROM ' + tblRozpracOper; lSQL:= lSQL + ' e INNER JOIN ' + tblRozpracOperR + ' eo ON (eo.IDEvidRozpracOper=e.ID) INNER JOIN ' + tblVPr + ' vp ON (eo.IDPrikaz=vp.ID) LEFT JOIN ' + tblZak; lSQL:= lSQL + ' z ON (z.ID=vp.IDZakazka) INNER JOIN ' + tblKZ + ' k ON (k.ID=vp.IDTabKmen) LEFT JOIN ' + tblPrikazVC + ' vc ON (vc.ID=eo.IDVyrCis)'; lSQL:= lSQL + ' LEFT JOIN ' + tblRozpracOperRE + ' eoe ON (eoe.ID=eo.ID)'; lSQL:= lSQL + ' WHERE e.DatumUzavreni IS NULL AND e.IDStroje=' + idStroj.ToString + ' AND vp.StavPrikazu=30 ORDER BY e.CasZahajeni DESC'; with Helios.OpenSQL(lSQL) do begin First; while not(EOF) do begin vtStroje.FieldByName('fVyrobek').AsString:= VarToStr(FieldByNameValues('Nazev1')); vtStroje.FieldByName('fZakazka').AsString:= VarToStr(FieldByNameValues('RadaPrikaz')).Replace(' ',''); vtStroje.FieldByName('fIDEvidRozpOper').AsString:= VarToStr(FieldByNameValues('ID')); idVPr:= VarToStr(FieldByNameValues('idVPr')).ToInteger; vtStroje.FieldByName('fIDPrikaz').AsInteger:= idVPr; vtStroje.FieldByName('fCisloZbozi').AsString:= VarToStr(FieldByNameValues('CisloZbozi')); vtStroje.FieldByName('fIDKmen').AsString:= VarToStr(FieldByNameValues('IDKmen')); vtStroje.FieldByName('fCinnost').AsString:= 'Výroba'; cisloPL:= VarToStr(FieldByNameValues('VyrCislo')); vtStroje.FieldByName('fAktPaletList').AsString:= cisloPL; sarze:= VarToStr(FieldByNameValues('Sarze')); vtStroje.FieldByName('fSarze').AsString:= sarze; if (cisloPL<>'') and (sarze<>'') and (idVPr>0) then begin lSQL:= 'SELECT TOP(1) ID FROM ' + tblPalety + ' WHERE IDPrikaz=' + idVPr.ToString + ' AND CisloPalety=N' + cisloPL.QuotedString + ' AND Sarze=N' + sarze.QuotedString + ' AND DatUkonceni IS NULL AND IDStroj=' + idStroj.ToString + ' ORDER BY ID'; vtStroje.FieldByName('fIDPaleta').AsInteger:= helUtils.getHeliosIntVal (Helios, 0, lSQL); end; // vtRozpracPL.Append; // dm.vtRozpracPL.FieldByName('') // vtRozpracPL.Post; Next; end; end; if (helUtils.sqlExistsTestGeneral(Helios, 'SELECT 1 FROM ' + tblCCP1 + ' WHERE IDStroj=' + idStroj.ToString + ' AND DatPorizeni_X=' + helUtils.SQLDatetimeConvert(f.ledtVyrobniDen.DateTime))) then vtStroje.FieldByName('fCCP1').AsBoolean:= true; cntVz:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT COUNT(ID) FROM ' + tblVzorky + ' WHERE IDStroj=' + idStroj.ToString + ' AND Ukonceno=0'); if (cntVz>0) then vtStroje.FieldByName('fVZ').AsInteger:= cntVz; { lSQL:= 'SELECT TOP(1) p.ID FROM ' + tblRozpracOper + ' ro INNER JOIN ' + tblRozpracOperR + ' ror ON (ror.IDEvidRozpracOper=ro.ID) INNER JOIN ' + tblPrPost; lSQL:= lSQL + ' pp ON (pp.Alt=ror.AltPrPostup AND pp.Doklad=ror.DokladPrPostup AND pp.IDPrikaz=ror.IDPrikaz)'; lSQL:= lSQL + ' INNER JOIN ' + tblVPr + ' p ON (p.ID=pp.IDPrikaz) WHERE ro.CasUkonceni IS NULL AND ro.IDStroje=' + idStroj.ToString; idVPr:= helUtils.getHeliosIntVal(Helios, 0, lSQL); dm.vtStroje.FieldByName('fVyrobek').AsString:= helUtils.getHeliosStrVal(Helios, '', lSQL); } vtStroje.Post; Next; end; end; f.runTimerRozprac:= false; f.timerPrehledTimer (f); f.runTimerRozprac:= true; if (datMod.bmIdStroj>0) then dm.vtStroje.Locate('fIDStroj', VarArrayOf([datMod.bmIdStroj]), [loPartialKey]); dm.vtStroje.EnableControls; f.grdStroje.Invalidate; end; procedure Tdm.NactiDataPaneluStroje (const f: TformPekarna; var a: TArray); var lSQL: string; idStroje: integer; begin f.grdStroje.DataSource.DataSet.DisableControls; if (apiMod) and (1=0) then begin url:= apiServer + IfThen(apiServer.EndsWith('/'), '', '/') + 'ciselnik/tpv/stroj/?stredisko=211'; end else begin lSQL:= 'SELECT ID, Kod, Nazev FROM ' + tblCStroju + ' ORDER BY Kod'; with f.Helios.OpenSQL(lSQL) do if (RecordCount>0) then begin if not(vtStroje.Active) then vtStroje.Open; vtStroje.EmptyDataset; SetLength(a, RecordCount); First; while not(EOF) do begin idStroje:= VarToStr(FieldByNameValues('ID')).ToInteger; vtStroje.Append; vtStroje.FieldByName('fIDStroj').AsInteger:= idStroje; vtStroje.FieldByName('fStroj').AsString:= VarToStr(FieldByNameValues('Nazev')); vtStroje.FieldByName('fCinnost').AsString:= helUtils.getHeliosStrVal(Helios, '', 'SELECT TOP(1) Cinnost FROM ' + tblCinnostStr + ' WHERE IDStroje=' + idStroje.ToString + ' ORDER BY DatPorizeni DESC'); vtStroje.Post; a[vtStroje.RecNo-1]:= VarToStr(FieldByNameValues('ID')).ToInteger; Next; end; vtStroje.First; end; end; f.grdStroje.DataSource.DataSet.EnableControls; f.grdStroje.Invalidate; end; procedure Tdm.NactiVyrobniPoznamky (const f: TformPekarna); var lSQL: string; begin // if (Assigned(formPekarna)) then begin if (f.sgInfo.Cells[2, 1]<>'') then begin dsVyrobniPozn.DataSet.DisableControls; lSQL:= 'SELECT ID, VyrobniDen, Poznamka FROM ' + tblVyrPozn + ' WHERE VyrobniDen_X=CONVERT(datetime, N' + f.sgInfo.Cells[2, 1].QuotedString + ', 104) ORDER BY ID'; with f.Helios.OpenSQL(lSQL) do if (RecordCount>0) then begin if not(vtVyrobniPozn.Active) then vtVyrobniPozn.Open; First; while not(EOF) do begin vtVyrobniPozn.Append; vtVyrobniPozn.FieldByName('fID').AsInteger:= VarToStr(FieldByName('ID').Value).ToInteger; vtVyrobniPozn.FieldByName('fDatum').AsString:= VarToStr(FieldByName('VyrobniDen').Value); vtVyrobniPozn.FieldByName('fPoznamka').AsString:= VarToStr(FieldByName('Poznamka').Value); vtVyrobniPozn.Post; Next; end; vtVyrobniPozn.First; end; dsVyrobniPozn.DataSet.EnableControls; end; end; end; end.