unit frmGenPolos; interface uses Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics, Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.StdCtrls, Vcl.Mask, Vcl.ExtCtrls, Vcl.Grids, rStringGridEd, rImprovedComps, ddPlugin_TLB; const CRLF = #13#10; type TformGenPolos = class(TForm) sGrid: TrStringGridEd; edtRegCis: TLabeledEdit; edtNazev: TEdit; btnGen: TButton; btnStorno: TButton; edtVPC: TrEditNum; Label1: TLabel; edtOdlitek: TLabeledEdit; Label2: TLabel; procedure btnGenClick(Sender: TObject); procedure FormClose(Sender: TObject; var Action: TCloseAction); procedure FormShow(Sender: TObject); procedure btnStornoClick(Sender: TObject); procedure edtOdlitekExit(Sender: TObject); private function MamVOD: Boolean; public Helios: IHelios; idKZ: integer; genKusovnik: boolean; end; var formGenPolos: TformGenPolos; implementation uses System.StrUtils, helUtils; {$R *.dfm} function TformGenPolos.MamVOD; var i: Integer; begin result:= false; for i:=0 to sGrid.RowCount-1 do begin if (sGrid.IntCells[0, i]=1) then if sGrid.StringCells[1,i]='VOD' then result:= true; end; end; procedure TformGenPolos.btnGenClick(Sender: TObject); var i, i2, iOld, idKZn, idKZold, idKZx, idSS: integer; lSQL, skl, sz, rc, uKod: string; szOdlit: Boolean; begin idKZold:= 0; iOld:= 0; ReseedTable(Helios, tblKZ); ReseedTable(Helios, tblKVaz); ReseedTable(Helios, tblNC); edtOdlitek.Text:= Trim(edtOdlitek.Text); lSQL:= 'MERGE ' + tblParKZ + ' AS T USING ' + tblKZ + ' AS S ON (T.IDKmenZbozi=S.ID)'; lSQL:= lSQL + ' WHEN MATCHED AND S.ID=' + idKZ.ToString + ' AND LEFT(S.SkupZbo,1)=N''V'' THEN UPDATE SET T.TypDilce=0' + CRLF; lSQL:= lSQL + ' WHEN NOT MATCHED BY TARGET THEN INSERT (IdKmenZbozi, TypDilce) VALUES (S.ID, 0);'; Helios.ExecSQL(lSQL); for i:=0 to sGrid.RowCount-1 do begin if (sGrid.IntCells[0, i]=1) then begin sz:= sGrid.StringCells[1,i]; if (((sz='P43') or (sz='P51')) and (MamVOD)) then Continue; szOdlit:= false; if (sz='P41') or (LeftStr(sz,2)='P3') or (LeftStr(sz,2)='P2') or (LeftStr(sz,2)='P1') then szOdlit:= true; rc:= Trim(edtRegCis.Text); if (szOdlit) and (edtOdlitek.Text<>'') then rc:= Trim(edtOdlitek.Text); lSQL:= 'DECLARE @i INT=0' + CRLF + 'IF NOT EXISTS(SELECT id FROM ' + tblKZ + ' WHERE SkupZbo=N' + QuotedStr(sz) + ' AND RegCis=N' + QuotedStr(rc) + ')' + CRLF; lSQL:= lSQL + ' BEGIN' + CRLF + ' INSERT ' + tblKZ + ' (SkupZbo, RegCis, Nazev1, DruhSkladu, Dilec, MJEvidence) SELECT N'; lSQL:= lSQL + QuotedStr(sz) + ', N' + QuotedStr(rc) + ', N' + QuotedStr(edtNazev.Text) + ', 1, 1, N' + QuotedStr('ks') + CRLF; lSQL:= lSQL + ' SET @i=SCOPE_IDENTITY()' + CRLF + ' END' + CRLF + 'SELECT @i'; with Helios.OpenSQL(lSQL) do if (RecordCount=1) and (StrToInt(VarToStr(FieldValues(0)))>0) then begin idKZn:= StrToInt(VarToStr(FieldValues(0))); lSQL:= 'IF NOT EXISTS(SELECT IdKmenZbozi FROM ' + tblParKZ + ' WHERE IDKmenZbozi=' + idKZn.ToString + ') INSERT ' + tblParKZ + ' (IDKmenZbozi, TypDilce)'; lSQL:= lSQL + ' SELECT ' + idKZn.ToString + ', 1'; Helios.ExecSQL(lSQL); lSQL:= 'SELECT sze._Sklad_1 FROM ' + tblSZ + ' sz INNER JOIN ' + tblSZe + ' sze ON (sze.Id=sz.Id) WHERE sz.SkupZbo=N' + QuotedStr(sGrid.StringCells[1,i]); with Helios.OpenSQL(lSQL) do if (RecordCount=1) then begin uKod:= ''; skl:= VarToStr(FieldValues(0)); if (skl<>'') then begin lSQL:= 'SELECT se._UKod FROM ' + tblStrom + ' s INNER JOIN ' + tblStromE + ' se ON (se.Id=s.Id) WHERE s.Cislo=N' + QuotedStr(skl); with Helios.OpenSQL(lSQL) do if (RecordCount=1) then uKod:= VarToStr(FieldValues(0)); idSS:= 0; with Helios.OpenSQL('SELECT id FROM ' + tblSS + ' WHERE IDSklad=N' + QuotedStr(skl) + ' AND IDKmenZbozi=' + IntToStr(idKZn)) do if (RecordCount=1) then idSS:= StrToInt(VarToStr(FieldValues(0))); if (idSS=0) then with Helios.OpenSQL('INSERT ' + tblSS + ' (IDSklad, IDKmenZbozi) SELECT N' + QuotedStr(skl) + ', ' + IntToStr(idKZn) + CRLF + 'SELECT SCOPE_IDENTITY()') do if (RecordCount=1) then idSS:= StrToInt(VarToStr(FieldValues(0))); if (idSS>0) and (uKod<>'') then Helios.ExecSQL('UPDATE ' + tblSS + ' SET UKod=' + uKod + ' WHERE Id=' + IntToStr(idSS)); end; end; lSQL:= 'SELECT sze._Sklad_2 FROM ' + tblSZ + ' sz INNER JOIN ' + tblSZe + ' sze ON (sze.Id=sz.Id) WHERE sz.SkupZbo=N' + QuotedStr(sz); with Helios.OpenSQL(lSQL) do if (RecordCount=1) then begin uKod:= ''; skl:= VarToStr(FieldValues(0)); if (skl<>'') then begin lSQL:= 'SELECT se._UKod FROM ' + tblStrom + ' s INNER JOIN ' + tblStromE + ' se ON (se.Id=s.Id) WHERE s.Cislo=N' + QuotedStr(skl); with Helios.OpenSQL(lSQL) do if (RecordCount=1) then uKod:= VarToStr(FieldValues(0)); idSS:= 0; with Helios.OpenSQL('SELECT id FROM ' + tblSS + ' WHERE IDSklad=N' + QuotedStr(skl) + ' AND IDKmenZbozi=' + IntToStr(idKZn)) do if (RecordCount=1) then idSS:= StrToInt(VarToStr(FieldValues(0))); if (idSS=0) then with Helios.OpenSQL('INSERT ' + tblSS + ' (IDSklad, IDKmenZbozi) SELECT N' + QuotedStr(skl) + ', ' + IntToStr(idKZn) + CRLF + 'SELECT SCOPE_IDENTITY()') do if (RecordCount=1) then idSS:= StrToInt(VarToStr(FieldValues(0))); if (idSS>0) and (uKod<>'') then Helios.ExecSQL('UPDATE ' + tblSS + ' SET UKod=' + uKod + ' WHERE Id=' + IntToStr(idSS)); end; end; if (genKusovnik) then begin if (idKZold>0) and (iOld=i-1) then begin lSQL:= 'IF NOT EXISTS(SELECT id FROM ' + tblKVaz + ' WHERE IDVarianta IS NULL AND vyssi=' + IntToStr(idKZn) + ' AND nizsi=' + IntToStr(idKZold) + ') '; lSQL:= lSQL + 'INSERT ' + tblKVaz + ' (ZmenaOd, vyssi, nizsi, mnozstvi, mnozstviSeZtratou, Prirez) SELECT TOP(1) ID, ' + IntToStr(idKZn); lSQL:= lSQL + ', ' + IntToStr(idKZold) + ', 1, 1, 1 FROM ' + tblCZmen + ' WHERE Platnost=1 ORDER BY datum DESC'; Helios.ExecSQL(lSQL); end; end; idKZold:= idKZn; end; idKZx:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT id FROM ' + tblKZ + ' WHERE SkupZbo=N' + QuotedStr(sz) + ' AND RegCis=N' + QuotedStr(rc)); if (idKZx>0) then begin lSQL:= 'UPDATE ' + tblNC + ' SET CenaKC=' + StringReplace(FloatToStr(sGrid.NumCells[3, i]),',','.',[rfReplaceAll]); lSQL:= lSQL + ' WHERE CenovaUroven=1000 AND IdKmenZbozi=' + IntToStr(idKZx) + '; IF (@@ROWCOUNT=0) INSERT ' + tblNC; lSQL:= lSQL + ' (CenovaUroven, IDKmenZbozi, CenaKC, BezDPH) SELECT 1000, ' + IntToStr(idKZx) + ', '; lSQL:= lSQL + StringReplace(FloatToStr(sGrid.NumCells[3, i]),',','.',[rfReplaceAll]) + ', N' + QuotedStr('A'); Helios.ExecSQL(lSQL); end; iOld:= i; end; end; lSQL:= 'UPDATE ' + tblNC + ' SET CenaKC=' + StringReplace(edtVPC.Value.ToString, ',', '.', [rfReplaceAll]); lSQL:= lSQL + ' WHERE CenovaUroven=1000 AND IdKmenZbozi=' + IntToStr(idKZ) + '; IF (@@ROWCOUNT=0) INSERT ' + tblNC; lSQL:= lSQL + ' (CenovaUroven, IDKmenZbozi, CenaKC, BezDPH) SELECT 1000, ' + IntToStr(idKZ) + ', '; lSQL:= lSQL + StringReplace(FloatToStr(edtVPC.Value),',','.',[rfReplaceAll]) + ', N' + QuotedStr('A'); Helios.ExecSQL(lSQL); if (genKusovnik) then begin if (idKZold>0) and (iOld=sGrid.RowCount-1) then with Helios.OpenSQL('SELECT id FROM ' + tblKVaz + ' WHERE IDVarianta IS NULL AND vyssi=' + IntToStr(idKZ) + ' AND nizsi=' + IntToStr(idKZold)) do if (RecordCount=0) then begin lSQL:= 'INSERT ' + tblKVaz + ' (ZmenaOd, vyssi, nizsi, mnozstvi, mnozstviSeZtratou, Prirez) SELECT TOP(1) ID, ' + IntToStr(idKZ); lSQL:= lSQL + ', ' + IntToStr(idKZold) + ', 1, 1, 1 FROM ' + tblCZmen + ' WHERE Platnost=1 ORDER BY datum DESC'; Helios.ExecSQL(lSQL); end; end; Close; end; procedure TformGenPolos.btnStornoClick(Sender: TObject); begin Close; end; procedure TformGenPolos.edtOdlitekExit(Sender: TObject); var i: integer; lSQL: string; begin if (edtOdlitek.Text<>'') then begin edtOdlitek.Text:= StringReplace(edtOdlitek.Text, ';', '', [rfReplaceAll]); edtOdlitek.Text:= StringReplace(edtOdlitek.Text, '''', '', [rfReplaceAll]); for i:=0 to sGrid.RowCount-1 do begin lSQL:= 'SELECT c.CenaKc FROM ' + tblNC + ' c INNER JOIN ' + tblKZ + ' k ON (k.ID=c.IDKmenZbozi) WHERE c.CenovaUroven=1000 AND k.SkupZbo=N'; lSQL:= lSQL + sGrid.StringCells[1, i].QuotedString + ' AND RegCis=N' + IfThen(edtOdlitek.Text<>'', edtOdlitek.Text, edtRegCis.Text).QuotedString; lSQL:= lSQL + ' AND c.BezDPH=N''A'''; with Helios.OpenSQL(lSQL) do if (RecordCount=1) then sGrid.NumCells[3, i]:= StrToFloat(VarToStr(FieldValues(0))); end; end; end; procedure TformGenPolos.FormClose(Sender: TObject; var Action: TCloseAction); begin Action:= caFree; end; procedure TformGenPolos.FormShow(Sender: TObject); var lSQL: string; i: integer; begin edtVPC.Value:= 0; edtNazev.Text:= ''; edtRegCis.Text:= ''; sGrid.RowCount:= 1; sGrid.Canvas.Font.Size:= 14; if (idKZ>0) then begin edtVPC.Value:= helUtils.getHeliosFloatVal(Helios, 0, 'SELECT CenaKC FROM ' + tblNC + ' WHERE CenovaUroven=1000 AND IdKmenZbozi=' + idKZ.ToString); with Helios.OpenSQL('SELECT RegCis, Nazev1, SkupZbo FROM ' + tblKZ + ' WHERE Id=' + IntToStr(idKZ) + ' AND SkupZbo IN (N''VOB'',N''VOD'')') do if (RecordCount=1) then begin edtRegCis.Text:= VarToStr(FieldValues(0)); edtRegCis.Text:= StringReplace(edtRegCis.Text, ';', '', [rfReplaceAll]); edtRegCis.Text:= StringReplace(edtRegCis.Text, '''', '', [rfReplaceAll]); edtNazev.Text:= VarToStr(FieldValues(1)); if (VarToStr(FieldValues(2))='VOD') then edtRegCis.EditLabel.Caption:= 'Odlitek'; if (VarToStr(FieldValues(2))='VOB') then edtRegCis.EditLabel.Caption:= 'Obrobek'; i:= 0; lSQL:= 'SELECT SkupZbo, Nazev FROM ' + tblSZ + ' WHERE LEFT(SkupZbo,1)=N' + QuotedStr('P') + ' AND SkupZbo NOT IN (N''P10'',N''P20'', N''P30'', N''P42'''; lSQL:= lSQL + ', N''PNA'', N''PDZ'', N''POD'') ORDER BY SkupZbo'; with Helios.OpenSQL(lSQL) do begin sGrid.RowCount:= RecordCount; while not(EOF) do begin sGrid.StringCells[1, i]:= VarToStr(FieldValues(0)); sGrid.StringCells[2, i]:= VarToStr(FieldValues(1)); with Helios.OpenSQL('SELECT id FROM ' + tblKZ + ' WHERE RegCis=N' + QuotedStr(edtRegCis.Text) + ' AND SkupZbo=N' + QuotedStr(sGrid.StringCells[1, i])) do if (RecordCount=0) then sGrid.IntCells[0, i]:= 1 else sGrid.IntCells[0, i]:= 0; lSQL:= 'SELECT c.CenaKc FROM ' + tblNC + ' c INNER JOIN ' + tblKZ + ' k ON (k.ID=c.IDKmenZbozi) WHERE c.CenovaUroven=1000 AND k.SkupZbo=N'; lSQL:= lSQL + sGrid.StringCells[1, i].QuotedString + ' AND RegCis=N' + IfThen(edtOdlitek.Text<>'', edtOdlitek.Text, edtRegCis.Text).QuotedString; lSQL:= lSQL + ' AND c.BezDPH=N''A'''; with Helios.OpenSQL(lSQL) do if (RecordCount=1) then sGrid.NumCells[3, i]:= StrToFloat(VarToStr(FieldValues(0))); Inc(i); Next; end; end; end; end; end; end.