unit frmVyrobaMimoPlan; interface uses Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics, Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.ExtCtrls, Data.DB, Vcl.Grids, Vcl.DBGrids, Vcl.StdCtrls, ddPlugin_TLB; type TDBGridHelper = class helper for TDBGrid function GetColumnIDByFieldName (const fldName: string): integer; end; TformVyrobaMimoPlan = class(TForm) GridPanel1: TGridPanel; grdVyrobaMimoPlan: TDBGrid; GridPanel2: TGridPanel; edtSZ: TEdit; edtRegCis: TEdit; edtNazev1: TEdit; edtZaklad: TEdit; edtStroj: TEdit; lblReset: TLabel; procedure grdVyrobaMimoPlanDblClick(Sender: TObject); procedure FormShow(Sender: TObject); procedure lblResetClick(Sender: TObject); procedure edtSZChange(Sender: TObject); procedure edtRegCisChange(Sender: TObject); procedure edtNazev1Change(Sender: TObject); procedure edtZakladChange(Sender: TObject); procedure edtStrojChange(Sender: TObject); procedure grdVyrobaMimoPlanDrawColumnCell(Sender: TObject; const Rect: TRect; DataCol: Integer; Column: TColumn; State: TGridDrawState); private procedure Filtruj; public Helios: IHelios; stroj: string; end; var formVyrobaMimoPlan: TformVyrobaMimoPlan; implementation uses System.StrUtils, System.DateUtils, datMod, helUtils, helTabsBIDs; {$R *.dfm} function TDBGridHelper.GetColumnIDByFieldName (const fldName: string): Integer; var i: integer; begin result:= -1; for i:=0 to self.Columns.Count-1 do if (self.Columns.Items[i].FieldName=fldName) then result:= i; end; procedure TformVyrobaMimoPlan.FormShow (Sender: TObject); begin GridPanel2.ColumnCollection.Items[1].SizeStyle:= ssAuto; GridPanel2.ColumnCollection.Items[1].Value:= grdVyrobaMimoPlan.Columns.Items[grdVyrobaMimoPlan.GetColumnIDByFieldName('colSZ')].Width; Self.Caption:= ' Výroba mimo plán - ' + helUtils.getHeliosStrVal (Helios, '', 'SELECT Nazev FROM ' + tblCStroju + ' WHERE ID=' + datMod.aktIdStroj.ToString); lblResetClick (Sender); dm.NactiVyrobuMimoPlan (self); helUtils.waitEnd; grdVyrobaMimoPlan.SetFocus; end; procedure TformVyrobaMimoPlan.edtNazev1Change (Sender: TObject); begin Filtruj; end; procedure TformVyrobaMimoPlan.edtRegCisChange (Sender: TObject); begin Filtruj; end; procedure TformVyrobaMimoPlan.edtStrojChange (Sender: TObject); begin Filtruj; end; procedure TformVyrobaMimoPlan.edtSZChange (Sender: TObject); begin edtSZ.Text:= edtSZ.Text.Trim; Filtruj; end; procedure TformVyrobaMimoPlan.edtZakladChange (Sender: TObject); begin Filtruj; end; procedure TformVyrobaMimoPlan.Filtruj; var filtr: string; begin filtr:= ''; if (edtSZ.Text<>'') then filtr:= 'UPPER(colSZ) LIKE ''%' + edtSZ.Text.Trim.ToUpper + '%'''; if (edtRegCis.Text<>'') then filtr:= filtr + IfThen(filtr='', '', ' AND ') + 'UPPER(colRegCis) LIKE ''%' + edtRegCis.Text + '%'''; if (edtNazev1.Text<>'') then filtr:= filtr + IfThen(filtr='', '', ' AND ') + 'UPPER(colNazev1) LIKE ''%' + edtNazev1.Text.Trim.ToUpper + '%'''; if (edtStroj.Text<>'') then filtr:= filtr + IfThen(filtr='', '', ' AND ') + 'UPPER(colStroj) LIKE ''%' + edtStroj.Text.Trim.ToUpper + '%'''; if (edtZaklad.Text<>'') then filtr:= filtr + IfThen(filtr='', '', ' AND ') + 'UPPER(colZaklad) LIKE ''%' + edtZaklad.Text.Trim.ToUpper + '%'''; if (filtr='') then begin dm.vtVyrobaMimoPlan.Filter:= filtr; dm.vtVyrobaMimoPlan.Filtered:= false; end else begin dm.vtVyrobaMimoPlan.Filter:= filtr; dm.vtVyrobaMimoPlan.Filtered:= true; end; grdVyrobaMimoPlan.Invalidate; end; procedure TformVyrobaMimoPlan.grdVyrobaMimoPlanDblClick (Sender: TObject); var lSQL, msg, podm, nazev1, mj, altPrP, VPrStr, stroj, nazevPrac: string; bid, idKmen, idVPr, idPrac, idStroj, idEvidROp, idEvidROpPol, doklPrP: Integer; krabVPalete: extended; canCont: boolean; begin idKmen:= grdVyrobaMimoPlan.DataSource.DataSet.FieldByName('colIDKmen').AsInteger; nazev1:= grdVyrobaMimoPlan.DataSource.DataSet.FieldByName('colNazev1').AsString; // krabVPalete:= grdVyrobaMimoPlan.DataSource.DataSet.FieldByName('colBaleni2').AsExtended; idStroj:= datMod.aktIdStroj; stroj:= helUtils.getHeliosStrVal (Helios, '', 'SELECT Nazev FROM ' + tblCStroju + ' WHERE ID=' + idStroj.ToString); // idPrac:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT IDPrac FROM ' + tblCStroju + ' WHERE ID=' + idStroj.ToString); if (Helios.YesNo('Chcete zahájit výrobu 1 palety ' + #1 + nazev1 + #1 + CRLF + 'na stroji ' + #1 + stroj + #1 + ' ?', false)) then begin lSQL:= 'DROP TABLE IF EXISTS #TabVyrobaMimoPlan' + CRLF; lSQL:= lSQL + 'CREATE TABLE #TabVyrobaMimoPlan (ID INT IDENTITY(1,1) NOT NULL, IDKmen INT NOT NULL, IDStroj INT NOT NULL, IDZamest INT NOT NULL)' + CRLF; lSQL:= lSQL + 'INSERT #TabVyrobaMimoPlan (IDKmen, IDStroj, IDZamest) SELECT ' + idKmen.ToString + ', ' + idStroj.ToString + ', ' + datMod.idZamMistr.ToString + CRLF; lSQL:= lSQL + 'IF OBJECT_ID(N''dbo.ep_Vyroba_VyrobaMimoPlan'', N''P'') IS NOT NULL EXEC dbo.ep_Vyroba_VyrobaMimoPlan'; try Helios.ExecSQL(lSQL); except on E:Exception do Helios.Error('Chyba generování příkazů z plánu: ' + CRLF + E.Message); end; end; { lSQL:= 'DECLARE @idEvROp INT, @uroven INT, @maxUroven INT, @iChyba INT, @mamPolotovar BIT, @idPlan INT, @idVPr INT, @prpD INT, @idKmen INT, @szKmen NVARCHAR(3)'; lSQL:= lSQL + ', @prpA NCHAR(1)=N''A'', @mn NUMERIC(19,6)' + CRLF; lSQL:= lSQL + 'SET @mn=' + krabVPalete.ToString.Replace(',', '.') + CRLF; lSQL:= lSQL + 'EXEC @idPlan=dbo.hp_NewVyrobniPlan @IDDilce=' + idKmen.ToString + ', @mnozstvi=@mn, @Poznamka=N''Výroba mimo plán'', @ZdrojPozadavku=0' + CRLF; lSQL:= lSQL + 'IF (@idPlan>0)' + CRLF; lSQL:= lSQL + ' BEGIN' + CRLF; lSQL:= lSQL + ' UPDATE ' + tblPlan + ' SET Datum=dbo.hf_TruncDate(GETDATE()) WHERE ID=@idPlan' + CRLF; lSQL:= lSQL + ' IF OBJECT_ID(N''tempdb..#TabPomSeznamVyrPlanuProZaplanovani'', N''U'') IS NULL CREATE TABLE #TabPomSeznamVyrPlanuProZaplanovani (ID INT NOT NULL)' + CRLF; lSQL:= lSQL + ' IF OBJECT_ID(N''tempdb..#TabGenVyrPrikazy'', N''U'') IS NULL CREATE TABLE #TabGenVyrPrikazy (ID INT NOT NULL, UrovenVnoreni INT NULL)' + CRLF; lSQL:= lSQL + ' INSERT #TabPomSeznamVyrPlanuProZaplanovani (ID) SELECT @idPlan' + CRLF + ' EXEC dbo.hp_VyrPlan_ZaplanujSeznam @GenPlanovaneVyroby=0' + CRLF; lSQL:= lSQL + ' IF EXISTS (SELECT 1 FROM #TabGenVyrPrikazy)' + CRLF; lSQL:= lSQL + ' BEGIN' + CRLF; lSQL:= lSQL + ' UPDATE t SET t.UrovenVnoreni=s.UrovenVnoreni FROM #TabGenVyrPrikazy t, ' + tblVPr + ' s WHERE s.ID=t.ID' + CRLF; lSQL:= lSQL + ' SET @maxUroven=(SELECT MAX(UrovenVnoreni) FROM #TabGenVyrPrikazy)' + CRLF; lSQL:= lSQL + ' DECLARE c CURSOR LOCAL FOR SELECT ID, UrovenVnoreni FROM #TabGenVyrPrikazy' + CRLF; lSQL:= lSQL + ' OPEN c' + CRLF; lSQL:= lSQL + ' WHILE (1=1)' + CRLF; lSQL:= lSQL + ' BEGIN' + CRLF; lSQL:= lSQL + ' FETCH NEXT FROM c INTO @idVPr, @uroven' + CRLF; lSQL:= lSQL + ' IF (@@FETCH_STATUS<>0) BREAK' + CRLF; lSQL:= lSQL + ' SELECT @idKmen=p.IDTabKmen, @szKmen=k.SkupZbo FROM ' + tblVPr + ' p INNER JOIN ' + tblKZ + ' k ON (k.ID=p.IDTabKmen) WHERE p.ID=@idVPr' + CRLF; lSQL:= lSQL + ' SET @mamPolotovar=0' + CRLF; lSQL:= lSQL + ' IF EXISTS (SELECT 1 FROM ' + tblPrKVazby + ' v INNER JOIN ' + tblKZ + ' n ON (v.nizsi=n.ID) WHERE v.IDOdchylkyDo IS NULL AND v.IDPrikaz=@idVPr AND n.SkupZbo'; lSQL:= lSQL + ' LIKE N''7%'' AND n.SkupZbo<>N''701'')' + CRLF; lSQL:= lSQL + ' SET @mamPolotovar=1' + CRLF; lSQL:= lSQL + ' IF (@szKmen NOT IN (N''701'')) AND (@mamPolotovar=0)' + CRLF; lSQL:= lSQL + ' UPDATE ' + tblPrPost + ' SET pracoviste=' + idPrac.ToString + ', IDStroje=' + idStroj.ToString + ' WHERE IDOdchylkyDo IS NULL AND Nazev LIKE N''Peče%'' AND IDPrikaz=@idVPr' + CRLF; lSQL:= lSQL + ' EXEC @iChyba=dbo.hp_ZadaniPrikazuDoVyroby @IDPrikaz=@idVPr, @OnlyPredzpracovani=0' + CRLF; lSQL:= lSQL + ' IF (@iChyba=0) AND (@szKmen NOT IN (N''701''))' + CRLF; lSQL:= lSQL + ' BEGIN' + CRLF; lSQL:= lSQL + ' EXEC @idEvROp=dbo.ep_Vyroba_InsertEvidRozpracOper @IDZamestnance=' + datMod.idZamMistr.ToString + ', @IDPracoviste=' + idPrac.ToString + ', @IDStroje=' + idStroj.ToString + CRLF; lSQL:= lSQL + ' IF (@idEvROp>0)' + CRLF; lSQL:= lSQL + ' BEGIN' + CRLF; lSQL:= lSQL + ' SELECT TOP(1) @prpD=Doklad, @prpA=Alt FROM ' + tblPrPost + ' WHERE IDOdchylkyDo IS NULL AND IDPrikaz=@idVPr' + CRLF; lSQL:= lSQL + ' EXEC dbo.ep_Vyroba_InsertEvidRozpracOperPol @IdEvidRozpOper=@idEvROp, @IdPrikaz=@idVPr, @doklPrPost=@prpD, @altPrPost=@prpA' + CRLF; lSQL:= lSQL + ' END' + CRLF; lSQL:= lSQL + ' END' + CRLF; lSQL:= lSQL + ' END' + CRLF; lSQL:= lSQL + ' CLOSE c' + CRLF; lSQL:= lSQL + ' DEALLOCATE c' + CRLF; lSQL:= lSQL + ' END' + CRLF; lSQL:= lSQL + ' END' + CRLF; try Helios.ExecSQL(lSQL); except end; end; } { msg:= ''; idVPr:= 0; bid:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT DPBID FROM ' + tblObecPrehled + ' WHERE NazevSys=N''hvw_Vyroba_DilceProVyrobu'''); if (bid>0) then begin podm:= ''; if (Helios.Prenos2(bid, 'hvw_Vyroba_DilceProVyrobu.ID', 'hvw_Vyroba_DilceProVyrobu.CisloZbozi', oVar1, oVar2, podm, 'Vyberte co chcete vyrábět', false, false, false, 1)) then begin idKmen:= VarToStr(oVar1).ToInteger; nazev1:= helUtils.getHeliosStrVal(Helios, '', 'SELECT Nazev1 FROM ' + tblKZ + ' WHERE ID=' + idKmen.ToString); canCont:= true; idPrac:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT TOP(1) pracoviste FROM ' + tblPostup + ' WHERE dilec=' + idKmen.ToString + ' AND ZmenaDo IS NULL ORDER BY operace'); if (idPrac>0) then begin if (helUtils.sqlExistsTestGeneral(Helios, 'SELEC 1 FROM ' + tblRozpracOper + ' WHERE Stav<30 AND IDPracoviste=' + idPrac.ToString)) then begin nazevPrac:=helUtils.getHeliosStrVal(Helios, '', 'SELECT nazev FROM ' + tblCPrac + ' WHERE ID=' + idPrac.ToString); if (Helios.YesNo('Na pracovišti ' + nazevPrac + ' se aktuálně vyrábí.' + CRLF + 'Chcete stávající výrobu ukončit a zahájit výrobu ' + nazev1 + '?', false)) then begin idEvidROp:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT TOP(1) ID FROM ' + tblRozpracOper + ' WHERE Stav<30 AND IDPracoviste=' + idPrac.ToString); dm.UkonciEvidRozpracOper (idEvidROp, msg); if (msg<>'') then begin Helios.Error(msg); canCont:= false; end; end else canCont:= false; end; end; if (canCont) then begin mj:= helUtils.getHeliosStrVal (Helios, '', 'SELECT MJEvidence FROM ' + tblKZ + ' WHERE ID=' + idKmen.ToString); lSQL:= 'SELECT ISNULL( (SELECT PocetHlavni FROM ' + tblMJZbo + 'WHERE IDKmenZbozi=' + idKmen.ToString + ' AND KodMJ1=N''KA'' AND KodMJ2=N''pal''), 0)'; krabVPalete:= helUtils.getHeliosFloatVal (Helios, 0, lSQL); if (krabVPalete=0) then Helios.Error (#1'Nelze zadat do výroby, chybí přepočet KA na paletu.'#1) else begin lSQL:= 'DECLARE @idVPr INT' + CRLF + 'EXEC @idVPr=dbo.hp_NewVyrobniPrikaz @IDDilce=' + idKmen.ToString + ', @kusy_zad=' + krabVPalete.ToString.Replace(',', '.').Replace(' ','') + CRLF; lSQL:= lSQL + 'SELECT @idVPr AS newid'; try with Helios.OpenSQL (lSQL) do idVPr:= VarToStr(FieldByNameValues('newid')).ToInteger; except on E:Exception do Helios.Error (#1'Nepodařilo se vytvořit výrobní příkaz'#1 + CRLF + E.Message); end; end; end; end; end; // bid KZ // mam novy prikaz ? if (idVPr>0) then begin lSQL:= 'DECLARE @retVal INT' + CRLF +'EXEC @retVal=dbo.hp_ZadaniPrikazuDoVyroby @IDPrikaz=' + idVPr.ToString; try Helios.ExecSQL(lSQL); idEvidROp:= 0; lSQL:= 'DECLARE @idEROp INT=0, @dat DATETIME=GETDATE()' + CRLF; lSQL:= lSQL + 'IF OBJECT_ID(N''dbo.ep_Vyroba_InsertEvidRozpracOper'', ''P'') IS NOT NULL EXEC @idEROp=dbo.ep_Vyroba_InsertEvidRozpracOper @IDZamestnance='; lSQL:= lSQL + IfThen(idZamMistr>0, idZamMistr.ToString, 'NULL') + ', @IDStroje=' + aktIdStr.ToString + ', @CasZahajeni=@dat' + CRLF + 'SELECT ISNULL(@idEROp,0) AS newid'; try with Helios.OpenSQL(lSQL) do idEvidROp:= VarToStr(FieldByNameValues('newid')).ToInteger; except end; if (idEvidROp>0) then begin idEvidROpPol:= 0; lSQL:= 'SELECT TOP(1) Doklad, Alt FROM ' + tblPrPost + ' WHERE IDOdchylkyDo IS NULL AND IDPrikaz=' + idVPr.ToString; with Helios.OpenSQL(lSQL) do if (RecordCount=1) then begin doklPrP:= VarToStr(FieldByNameValues('Doklad')).ToInteger; altPrP:= VarToStr(FieldByNameValues('Alt')); lSQL:= 'DECLARE @idEROp INT=0' + CRLF + 'IF OBJECT_ID(N''dbo.ep_Vyroba_InsertEvidRozpracOperPol'', ''P'') IS NOT NULL EXEC @idEROp=dbo.ep_Vyroba_InsertEvidRozpracOperPol'; lSQL:= lSQL + ' @IdEvidRozpOper=' + idEvidROp.ToString + ', @IDPrikaz=' + idVPr.ToString + ', @doklPrPost=' + doklPrP.ToString + ', @altPrPost=N' + altPrP.QuotedString; try Helios.ExecSQL(lSQL); except end; end; end; VPrStr:= helUtils.getHeliosStrVal(Helios, '', 'SELECT RadaPrikaz FROM ' + tblVPr + ' WHERE ID=' + idVPr.ToString); Helios.Info('Byl vytvořen nový Výrobní příkaz '#1 + VPrStr + #1 + ' (stroj ' + dm.VratStroj (Helios, 'Nazev', aktIdStr) + ')'); except end; end; } end; procedure TformVyrobaMimoPlan.grdVyrobaMimoPlanDrawColumnCell (Sender: TObject; const Rect: TRect; DataCol: Integer; Column: TColumn; State: TGridDrawState); begin if (not(gdSelected in State)) then begin if (Odd(grdVyrobaMimoPlan.DataSource.DataSet.RecNo)) then TDBGrid(Sender).Canvas.Brush.Color:= clWindow else TDBGrid(Sender).Canvas.Brush.Color:= $00E0E0E0; end; if (Column.FieldName='colNazev1') then begin TDBGrid(Sender).Canvas.Brush.Color:= $00D0FEC6; // zelena if (gdSelected in State) then TDBGrid(Sender).Canvas.Font.Color:= clBlack; // cerne pismo na zelenem pozadi end; TDBGrid(Sender).DefaultDrawColumnCell (Rect, DataCol, Column, State); end; procedure TformVyrobaMimoPlan.lblResetClick (Sender: TObject); begin edtSZ.Text:= ''; edtRegCis.Text:= ''; edtNazev1.Text:= ''; edtStroj.Text:= ''; edtZaklad.Text:= ''; Filtruj; grdVyrobaMimoPlan.SetFocus; end; end.