295 lines
12 KiB
ObjectPascal
295 lines
12 KiB
ObjectPascal
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.
|