unit frmOdvLis; interface uses Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics, System.StrUtils, Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.StdCtrls, Vcl.Mask, RzEdit, TBPackageU, ddPlugin_TLB, Vcl.ExtCtrls, Vcl.ComCtrls, Data.DB, Vcl.Grids, Vcl.DBGrids, VirtualDataSet, MemDS, VirtualTable, NxGridView6, NxColumns6, NxControls6, NxCustomGrid6, NxTypes6, NxVirtualGrid6, NxDBCells6, NxDBGrid6, NxDBColumns6, NxIntf6, NxEdit6, Vcl.Menus; const tblPlan = '[dbo].[_hdc_TabPlanNasazeniLisu]'; tblPlanR = '[dbo].[_hdc_TabPlanNasazeniLisuR]'; tblOdlitky = '[dbo].[_hdc_TabOdlitky]'; type TZavady = record poz: integer; kod: string; popis: string; end; TformOdvLis = class(TForm) btnOK: TButton; btnStorno: TButton; lblZapisDat: TLabel; edtBarcode: TLabeledEdit; selBarcode: TButtonedEdit; edtCisZam: TLabeledEdit; selCisZam: TButtonedEdit; edtVstriku: TRzNumericEdit; Label1: TLabel; tbl: TVirtualTable; ds: TDataSource; grd: TNextDBGrid6; view: TNxReportGridView6; colRegCis: TNxDBTextColumn6; colNazev: TNxDBTextColumn6; colKsDobre: TNxDBNumberColumn6; tblIDPrPostup: TIntegerField; tblRegCis: TStringField; tblNazev: TStringField; tblKsDobre: TSmallintField; lblPrijmeni: TLabel; lblPlan: TLabel; tblIDVyrCS: TIntegerField; tblFormaSN: TStringField; tblIDradek: TIntegerField; tblIDnaradi: TIntegerField; tblKsZmetky1: TIntegerField; tblKsZmetky2: TIntegerField; tblKsZmetky3: TIntegerField; tblKsNaPlate: TIntegerField; colFormaSN: TNxDBTextColumn6; colZmetky1: TNxDBNumberColumn6; colZmetky2: TNxDBNumberColumn6; colZmetky3: TNxDBNumberColumn6; colKsVeForme: TNxDBNumberColumn6; Label2: TLabel; tblForma: TStringField; tblcolZakazka: TStringField; colZakazka: TNxDBTextColumn6; comboForma: TNxComboBox6; comboSN: TNxComboBox6; colForma: TNxDBTextColumn6; colIdPostup: TNxDBNumberColumn6; tblID: TIntegerField; colIdNaradi: TNxDBNumberColumn6; colIdVyrCis: TNxDBNumberColumn6; tblFormaPouzita: TBooleanField; tblPocetVstriku: TIntegerField; colFormaPouzita: TNxDBCheckBoxColumn6; colPocetVstriku: TNxDBNumberColumn6; lblVerze: TLabel; tblIDVyrCK: TIntegerField; tblParovaForma: TSmallintField; procedure FormShow(Sender: TObject); procedure btnStornoClick(Sender: TObject); procedure FormClose(Sender: TObject; var Action: TCloseAction); procedure btnOKClick(Sender: TObject); procedure edtBarcodeExit(Sender: TObject); procedure selBarcodeClick(Sender: TObject); procedure selCisZamClick(Sender: TObject); procedure selCisZamExit(Sender: TObject); procedure edtCisZamExit(Sender: TObject); procedure viewGetCellColor(Sender: TObject; ACol, ARow: Integer; var CellColor: TColor; State: TNxCellPaintingState); procedure edtVstrikuExit(Sender: TObject); procedure grdCellClick(Sender: TObject; ACol, ARow: Integer; Button: TMouseButton); procedure comboFormaChange(Sender: TObject); procedure comboFormaExit(Sender: TObject); procedure comboSNChange(Sender: TObject); procedure comboSNExit(Sender: TObject); procedure tblAfterPost(DataSet: TDataSet); procedure colFormaPouzitaCheckedChanged(Sender: TObject; ACol, ARow: Integer); private function najdiVyrCis(ARow: integer): integer; procedure zapisIDNaradi(ARow: integer); procedure zapisVyrCis(ARow: integer); public Helios: IHelios; jeTest: Boolean; mVerze: string; end; var formOdvLis: TformOdvLis; idPlan, bidPlan: integer; oVar: OleVariant; zavady: TArray; implementation uses System.DateUtils, helUtils; {$R *.dfm} procedure TformOdvLis.btnOKClick(Sender: TObject); var lSQL, idcko, idckoR: string; begin lblZapisDat.Visible:= true; Self.Repaint; btnOK.Enabled:= false; btnStorno.Enabled:= false; if (tbl.RecordCount>0) then begin { lSQL:= 'IF OBJECT_ID(N''tempdb..#TabOdvodLis'') IS NULL CREATE TABLE #TabOdvodLis (ID INT IDENTITY(1,1) NOT NULL, IDPlan INT NOT NULL, CisZam INT NOT NULL,'; lSQL:= lSQL +' PocetVstriku SMALLINT NOT NULL DEFAULT 0, DeviceID NVARCHAR(40) DEFAULT HOST_NAME(), Autor NVARCHAR(80) NOT NULL DEFAULT SUSER_SNAME(),'; lSQL:= lSQL + ' DatPorizeni DATETIME NOT NULL DEFAULT GETDATE())' + CRLF; } lSQL:= 'INSERT #TabOdvodLis (IDPlan, CisZam, PocetVstriku) VALUES (' + idPlan.ToString + ', ' + edtCisZam.Text + ', '; lSQL:= lSQL + StringReplace(FloatToStr(edtVstriku.Value), ',', '.', [rfReplaceAll]) + ')' + CRLF + 'SELECT SCOPE_IDENTITY()'; if (jeTest) then lSQL:= lSQL.Replace('#TabOdv', 'dbo._temp_TabOdv').Replace('tempdb..', ''); with Helios.OpenSQL(lSQL) do idcko:= VarToStr(FieldValues(0)); { lSQL:= lSQL + 'IF OBJECT_ID(N''tempdb..#TabOdvodLisR'') IS NULL CREATE TABLE #TabOdvodLisR (ID INT IDENTITY(1,1) NOT NULL, IDHlava INT NOT NULL, IDRadek INT,'; lSQL:= lSQL + ' IDVyrCis INT, KsDobre NUMERIC(15,3) NOT NULL DEFAULT 0.0, KsVeForme SMALLINT DEFAULT 1, IDPrPostup INT, IDNaradi INT,'; lSQL:= lSQL + ' FormaPouzita BIT DEFAULT 1, PocetVstriku SMALLINT DEFAULT 0)' + CRLF; } tbl.First; while not(tbl.Eof) do begin lSQL:= 'INSERT #TabOdvodLisR (IDHlava, IDRadek, IDVyrCis, IDVyrCK, KsDobre, KsVeForme, IDPrPostup, IDNaradi, FormaPouzita, PocetVstriku, ParovaForma) SELECT ' + idcko + ', '; lSQL:= lSQL + tbl.FieldByName('IDradek').AsString; lSQL:= lSQL + ', ' + IfThen(tbl.FieldByName('IDVyrCS').AsInteger>0, tbl.FieldByName('IDVyrCS').AsString, 'NULL'); lSQL:= lSQL + ', ' + IfThen(tbl.FieldByName('IDVyrCK').AsInteger>0, tbl.FieldByName('IDVyrCK').AsString, 'NULL'); lSQL:= lSQL + ', ' + tbl.FieldByName('KsDobre').AsExtended.ToString.Replace(',', '.'); lSQL:= lSQL + ', ' + tbl.FieldByName('KsVeForme').AsExtended.ToString.Replace(',', '.'); lSQL:= lSQL + ', ' + tbl.FieldByName('IDPostup').AsString; lSQL:= lSQL + ', ' + IfThen(tbl.FieldByName('IDnaradi').AsInteger>0, tbl.FieldByName('IDnaradi').AsString, 'NULL'); lSQL:= lSQL + ', ' + IfThen(tbl.FieldByName('FormaPouzita').AsBoolean, '1', '0'); lSQL:= lSQL + ', ' + tbl.FieldByName('PocetVstriku').AsExtended.ToString.Replace(',', '.'); lSQL:= lSQL + ', ' + IfThen(tbl.FieldByName('ParovaForma').AsBoolean, '1', '0') + CRLF; lSQL:= lSQL + 'SELECT SCOPE_IDENTITY()'; { lSQL:= lSQL + 'IF OBJECT_ID(N''tempdb..#TabOdvodLisZavady'') IS NULL CREATE TABLE #TabOdvodLisZavady (ID INT IDENTITY(1,1) NOT NULL, IDHlava INT NOT NULL,'; lSQL:= lSQL + ' IDPrPostup INT NOT NULL, IDZavady INT NOT NULL, Mnozstvi NUMERIC(19,6) NOT NULL DEFAULT 0.0, Autor NVARCHAR(80) NOT NULL DEFAULT SUSER_SNAME())'; } if (jeTest) then lSQL:= lSQL.Replace('#TabOdv', 'dbo._temp_TabOdv').Replace('tempdb..', '', [rfReplaceAll]); with Helios.OpenSQL(lSQL) do idckoR:= VarToStr(FieldValues(0)); lSQL:= ''; if (tbl.FieldByName('KsZmetky1').AsFloat>0) and (Length(zavady)>0) then begin lSQL:= 'INSERT #TabOdvodLisZavady (IDRadek, IDPrPostup, IDZavady, Mnozstvi, IDHlava) SELECT ' + idckoR + ', ' + tbl.FieldByName('IDPostup').AsString + ', ID, '; lSQL:= lSQL + tbl.FieldByName('KsZmetky1').AsString.Replace(',', '.') + ', ' + idcko + ' FROM ' + tblCZavad + ' WHERE kod=N' + zavady[0].kod.QuotedString + CRLF; end; if (tbl.FieldByName('KsZmetky2').AsFloat>0) and (Length(zavady)>1) then begin lSQL:= lSQL + 'INSERT #TabOdvodLisZavady (IDRadek, IDPrPostup, IDZavady, Mnozstvi, IDHlava) SELECT ' + tbl.FieldByName('IDPostup').AsString + ', ID, '; lSQL:= lSQL + tbl.FieldByName('KsZmetky2').AsString.Replace(',', '.') + ', ' + idcko + ' FROM ' + tblCZavad + ' WHERE kod=N' + zavady[1].kod.QuotedString + CRLF; end; if (tbl.FieldByName('KsZmetky3').AsFloat>0) and (Length(zavady)>2) then begin lSQL:= lSQL + 'INSERT #TabOdvodLisZavady (IDRadek, IDPrPostup, IDZavady, Mnozstvi, IDHlava) SELECT ' + tbl.FieldByName('IDPostup').AsString + ', ID, '; lSQL:= lSQL + tbl.FieldByName('KsZmetky3').AsString.Replace(',', '.') + ', ' + idcko + ' FROM ' + tblCZavad + ' WHERE kod=N' + zavady[2].kod.QuotedString + CRLF; end; if (jeTest) then lSQL:= lSQL.Replace('#TabOdv', 'dbo._temp_TabOdv').Replace('tempdb..', '', [rfReplaceAll]); if (lSQL<>'') then Helios.ExecSQL(lSQL); tbl.Next; end; end; if not(jeTest) then Helios.ExecSQL('IF OBJECT_ID(''dbo.ep_HDCSD_PlanLisu_Odvod'') IS NOT NULL EXEC dbo.ep_HDCSD_PlanLisu_Odvod'); // Helios.ExecSQL('IF OBJECT_ID(''dbo.ep_Vosk_PlanLisu_Odvod'') IS NOT NULL EXEC dbo.ep_Vosk_PlanLisu_Odvod'); lblZapisDat.Visible:= false; Self.Repaint; btnOK.Enabled:= true; btnStorno.Enabled:= true; Close; end; procedure TformOdvLis.btnStornoClick(Sender: TObject); begin Close; end; function TformOdvLis.najdiVyrCis(ARow: Integer): integer; begin result:= 0 end; procedure TformOdvLis.zapisIDNaradi(ARow: Integer); var lSQL, rc: string; idPrP: integer; i, i2, idVPr, dokl: integer; begin if (ARow>=0) then begin i:= 0; i2:= 0; rc:= grd.Cells[colForma.Index, ARow]; if (rc<>'') then begin idPrP:= grd.Cell[colRegCis.Index, grd.SelectedRow].AsInteger; i:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT ID FROM ' + tblKZ + ' WHERE naradi=1 AND RegCis=N' + QuotedStr(rc)); if (i=0) then i:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT TOP(1) ID FROM ' + tblKZ + ' WHERE SkupZbo LIKE N''F[12]%'' AND RegCis=N' + QuotedStr(rc)); end; tbl.Edit; lSQL:= 'SELECT TOP(1) ID FROM ' + tblVyrCK + ' WHERE IDKmenZbozi=' + i.ToString + ' AND Nazev1=N' + QuotedStr(grd.Cells[colFormaSN.Index, ARow]); i2:= helUtils.getHeliosIntVal(Helios, 0, lSQL); tbl.FieldByName('IDVyrCK').AsInteger:= i2; lSQL:= 'SELECT TOP(1) ID FROM ' + tblVyrCS + ' WHERE IDVyrCK=' + i2.ToString + ' AND Mnozstvi>0'; i2:= helUtils.getHeliosIntVal(Helios, 0, lSQL); tbl.FieldByName('IDVyrCS').AsInteger:= i2; tbl.FieldByName('IDnaradi').AsInteger:= i; tbl.Post; grd.DataSource.DataSet.Refresh; grd.Cell[colIdVyrCis.Index, ARow].AsInteger:= i2; end; end; procedure TformOdvLis.zapisVyrCis(ARow: Integer); var lSQL, rc, vyrcis: string; i, i2, idPrP: integer; begin if (ARow>=0) then begin i:= 0; rc:= grd.Cells[colForma.Index, ARow]; if (rc<>'') then begin idPrP:= grd.Cell[colRegCis.Index, grd.SelectedRow].AsInteger; i:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT ID FROM ' + tblKZ + ' WHERE naradi=1 AND RegCis=N' + QuotedStr(rc)); if (i=0) then i:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT TOP(1) ID FROM ' + tblKZ + ' WHERE SkupZbo LIKE N''F[12]%'' AND RegCis=N' + QuotedStr(rc)); end; i2:= 0; vyrcis:= grd.Cells[colFormaSN.Index, ARow]; if (vyrcis<>'') then begin idPrP:= grd.Cell[colRegCis.Index, grd.SelectedRow].AsInteger; lSQL:= 'SELECT ID FROM ' + tblVyrCS + ' WHERE Nazev1=N' + vyrcis.QuotedString + ' AND IDKmenZbozi' + i.ToString; i2:= helUtils.getHeliosIntVal(Helios, 0, lSQL); end; grd.Cell[colIdVyrCis.Index, ARow].AsInteger:= i2; tbl.Edit; tbl.FieldByName('IDVyrCS').AsInteger:= i2; tbl.FieldByName('IDVyrCK').AsInteger:= i2; tbl.Post; end; end; procedure TformOdvLis.comboFormaChange(Sender: TObject); var ARow: integer; begin ARow:= grd.SelectedRow; if (comboForma.ItemIndex>=0) then grd.SelectedCell.AsString:= comboForma.Items[comboForma.ItemIndex] else grd.SelectedCell.AsString:= ''; grd.Cells[colFormaSN.Index, ARow]:= ''; grd.SetFocus; end; procedure TformOdvLis.comboFormaExit(Sender: TObject); var ARow: integer; begin ARow:= grd.SelectedRow; if (comboForma.ItemIndex>=0) then grd.SelectedCell.AsString:= comboForma.Items[comboForma.ItemIndex] else grd.SelectedCell.AsString:= ''; comboForma.Visible:= False; zapisIDNaradi(ARow); grd.SetFocus; end; procedure TformOdvLis.comboSNChange(Sender: TObject); var ARow: integer; begin ARow:= grd.SelectedRow; if (comboSN.ItemIndex>=0) then grd.SelectedCell.AsString:= comboSN.Items[comboSN.ItemIndex] else grd.SelectedCell.AsString:= ''; grd.SetFocus; end; procedure TformOdvLis.comboSNExit(Sender: TObject); var ARow: integer; begin ARow:= grd.SelectedRow; if (comboSN.ItemIndex>=0) then grd.SelectedCell.AsString:= comboSN.Items[comboSN.ItemIndex] else grd.SelectedCell.AsString:= ''; comboSN.Visible:= False; zapisVyrCis(ARow); grd.SetFocus; end; procedure TformOdvLis.edtBarcodeExit(Sender: TObject); var lSQL, formy, sncka, nDokl, sVPr: string; idVPr, idVOp, dokl, cnt: integer; idPrikaz: integer; begin edtBarcode.Text:= Trim(edtBarcode.Text); edtBarcode.Text:= helUtils.StripChars(edtBarcode.Text, ['''', '"', '-', ';', ' ']); if (edtBarcode.Text<>'') then begin lblPlan.Caption:= ''; idPlan:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT ID FROM ' + tblPlan + ' WHERE BarCode=N' + QuotedStr(edtBarcode.Text)); if (idPlan>0) then begin lSQL:= 'SELECT Lis + N'' / '' + CONVERT(nvarchar, DATEPART(day,Datum)) + N''.'' + CONVERT(nvarchar, DATEPART(month,Datum)) + N''.'''; lSQL:= lSQL + ' + CONVERT(nvarchar, DATEPART(year,Datum)) + N'' / '' + Smena FROM ' + tblPlan + ' WHERE ID=' + idPlan.ToString; lblPlan.Caption:= helUtils.getHeliosStrVal(Helios, '', lSQL); if not(tbl.Active) then tbl.Open; tbl.Edit; tbl.Clear; //Fields; lSQL:= 'SELECT r.IDPrPostup, k.RegCis, k.Nazev1, r.IDVyrCis, vc.Nazev1, r.ID, nv.Naradi, ISNULL(nvke._KsVeForme, ISNULL(ISNULL(o1.KsVeForme, o.KsVeForme),0)), nvk.RegCis' + ', nv.Doklad, vp.ID, z.CisloZakazky, r.IDVyrCK FROM ' + tblPlanR + ' r INNER JOIN ' + tblPrPost + ' p ON ((p.ID1=r.IDPrPostup OR p.ID=r.IDPrPostup) AND p.IDOdchylkyDo IS NULL)' + ' INNER JOIN ' + tblPrikaz + ' vp ON (p.IDPrikaz=vp.ID) INNER JOIN ' + tblKZ + ' k ON (k.ID=vp.IdTabKmen) LEFT JOIN ' + tblOdlitky + ' o ON (o.Odlitek=k.RegCis) LEFT JOIN ' + tblOdlitky + ' o1 ON (o1.Odlitek=o.MatecniOdlitek) LEFT JOIN ' + tblPrNVazby + ' nv ON (p.IDPrikaz=nv.IDPrikaz AND p.Doklad=nv.Doklad) LEFT JOIN dbo.TabVyrCS vc ON (vc.ID=r.IDvyrcis)' + ' LEFT JOIN ' + tblKZ + ' nvk ON (nv.Naradi=nvk.Id) LEFT JOIN ' + tblZak + ' z ON (z.ID=vp.IDZakazka) LEFT JOIN ' + tblKZe + ' nvke ON (nvke.ID=nvk.ID) WHERE r.IDHlava=' + idPlan.ToString + ' ORDER BY r.Poradi, r.ID'; with Helios.OpenSQL(lSQL) do if (RecordCount>0) then begin First; while not(Eof) do begin tbl.Append; tbl.FieldByName('ID').AsInteger:= tbl.RecordCount + 1; tbl.FieldByName('IDPostup').AsString:= VarToStr(FieldValues(0)); tbl.FieldByName('RegCis').AsString:= VarToStr(FieldValues(1)); tbl.FieldByName('Nazev').AsString:= VarToStr(FieldValues(2)); tbl.FieldByName('KsDobre').AsExtended:= 0; tbl.FieldByName('IDVyrCS').AsInteger:= StrToInt(IfThen(VarToStr(FieldValues(3))='', '-1', VarToStr(FieldValues(3)))); tbl.FieldByName('IDVyrCK').AsInteger:= StrToInt(IfThen(VarToStr(FieldValues(12))='', '-1', VarToStr(FieldValues(12)))); tbl.FieldByName('IDradek').AsInteger:= StrToInt(VarToStr(FieldValues(5))); tbl.FieldByName('IDnaradi').AsInteger:= StrToInt(IfThen(VarToStr(FieldValues(6))='', '-1', VarToStr(FieldValues(6)))); tbl.FieldByName('Forma').AsString:= ''; tbl.FieldByName('FormaSN').AsString:= ''; if (LeftStr(VarToStr(FieldValues(1)),3).ToLower='ucp') then tbl.FieldByName('FormaPouzita').AsBoolean:= false else tbl.FieldByName('FormaPouzita').AsBoolean:= true; idVPr:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT IDPrikaz FROM ' + tblPrPost + ' WHERE ID=' + tbl.FieldByName('IDPostup').AsString); dokl:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT Doklad FROM ' + tblPrPost + ' WHERE ID=' + tbl.FieldByName('IDPostup').AsString); if (idVPr>0) and (dokl>0) then begin if (tbl.FieldByName('IDVyrCK').AsInteger<=0) then begin lSQL:= 'SELECT COUNT(ID) FROM ' + tblVyrCK + ' WHERE IDKmenZbozi=' + tbl.FieldByName('IDnaradi').AsString; cnt:= helUtils.getHeliosIntVal(Helios, 0, lSQL); if (cnt=1) then tbl.FieldByName('IDVyrCK').AsInteger:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT TOP(1) ID FROM ' + tblVyrCK + ' WHERE IDKmenZbozi=' + tbl.FieldByName('IDnaradi').AsString); end; lSQL:= 'SELECT k.RegCis, n.Naradi FROM ' + tblPrNVazby + ' n INNER JOIN ' + tblKZ + ' k ON (k.ID=n.Naradi) WHERE n.IDPrikaz='; lSQL:= lSQL + idVPr.ToString + ' AND n.Doklad=' + dokl.ToString + ' AND k.ID IN (SELECT IDKmenZbozi FROM ' + tblSS + ' WHERE Mnozstvi>0) ORDER BY k.RegCis'; with Helios.OpenSQL(lSQL) do if (RecordCount=1) then begin tbl.FieldByName('Forma').AsString:= Trim(VarToStr(FieldValues(0))); tbl.FieldByName('IDnaradi').AsString:= Trim(VarToStr(FieldValues(1))); if (tbl.FieldByName('IDVyrCS').AsInteger<=0) then begin lSQL:= 'SELECT ID, Nazev1 FROM ' + tblVyrCS + ' WHERE IDStavSkladu IN (SELECT ID FROM ' + tblSS + ' WHERE Mnozstvi>0 AND IDKmenZbozi='; lSQL:= lSQL + tbl.FieldByName('IDnaradi').AsString + ')'; with Helios.OpenSQL(lSQL) do if (RecordCount=1) then begin tbl.FieldByName('IDVyrCS').AsString:= VarToStr(FieldValues(0)); tbl.FieldByName('FormaSN').AsString:= VarToStr(FieldValues(1)); tbl.FieldByName('IDVyrCK').AsInteger:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT IDVyrCK FROM ' + tblVyrCS + ' WHERE ID=' + tbl.FieldByName('IDVyrCS').AsString); end; end else tbl.FieldByName('FormaSN').AsString:= helUtils.getHeliosStrVal(Helios, '', 'SELECT Nazev1 FROM ' + tblVyrCS + ' WHERE ID=' + tbl.FieldByName('IDVyrCS').AsString); end; end; tbl.FieldByName('KsZmetky1').AsExtended:= 0; tbl.FieldByName('KsZmetky2').AsExtended:= 0; tbl.FieldByName('KsZmetky3').AsExtended:= 0; tbl.FieldByName('KsVeForme').AsInteger:= StrToInt(VarToStr(FieldValues(7))); tbl.FieldByName('Zakazka').AsString:= VarToStr(FieldValues(11)); tbl.Post; Next; end; tbl.RecNo:= 1; grd.Repaint; end; end; edtCisZam.SetFocus; end; end; procedure TformOdvLis.edtCisZamExit(Sender: TObject); var c: integer; begin lblPrijmeni.Caption:= ''; edtCisZam.Text:= StringReplace(Trim(edtCisZam.Text), ' ', '', [rfReplaceAll]); edtCisZam.Text:= helUtils.StripChars(edtCisZam.Text, ['''', '"', '-', ';', ' ']); if (edtCisZam.Text<>'') then if TryStrToInt(edtCisZam.Text, c) then begin if (helUtils.HeliosExistsTest(Helios, tblCZam, 'Cislo=' + c.ToString)) then begin lblPrijmeni.Caption:= helUtils.getHeliosStrVal(Helios, '', 'SELECT Prijmeni FROM ' + tblCZam + ' WHERE Cislo=' + c.ToString); edtCisZam.Text:= StringOfChar('0', 6-Length(edtCisZam.Text)) + edtCisZam.Text; edtVstriku.SetFocus; end else begin Helios.Error(#1'Zadané osobní číslo nebylo nalezeno !'#1); edtCisZam.SetFocus; edtCisZam.SelectAll; end; end else begin Helios.Error(#1'Zadané znaky nejsou číslo'#1); edtCisZam.SetFocus; edtCisZam.SelectAll; end; end; procedure TformOdvLis.edtVstrikuExit(Sender: TObject); var cell: TGridCoord; i: integer; begin if (grd.RowCount>0) then begin i:= tbl.RecNo; grd.DataSource.DataSet.DisableControls; tbl.First; while not(tbl.Eof) do begin tbl.Edit; tbl.FieldByName('PocetVstriku').AsInteger:= edtVstriku.IntValue; tbl.Post; tbl.Next; end; tbl.RecNo:= i; grd.DataSource.DataSet.EnableControls; grd.Refresh; end; end; procedure TformOdvLis.FormClose(Sender: TObject; var Action: TCloseAction); begin if (tbl.Active) then tbl.Close; Action:= caFree; end; procedure TformOdvLis.FormShow(Sender: TObject); var lSQL: string; i, i2: integer; begin self.ParentWindow:= Helios.MainApplicationHandle; self.Icon.Handle:= Helios.MainApplicationIconHandle; { for i:=0 to Self.ComponentCount-1 do begin if (Self.Components[i] is TButton) then begin (Self.Components[i] as TButton).Font.Name:= Helios.Font; (Self.Components[i] as TButton).Font.Height:= Helios.FontHeight; end; if (Self.Components[i] is TTB_3Button) then begin // (Self.Components[i] as TTB_3Button).Width:= 30; // (Self.Components[i] as TTB_3Button).Height:= 30; end; end; } lblVerze.Caption:= mVerze; { Helios.ExecSQL('IF OBJECT_ID(N''dbo._temp_TabOdvodLis'') IS NOT NULL DELETE FROM dbo._temp_TabOdvodLis WHERE Autor=SUSER_SNAME()'); Helios.ExecSQL('IF OBJECT_ID(N''dbo._temp_TabOdvodLisR'') IS NOT NULL DELETE FROM dbo._temp_TabOdvodLisR WHERE IDHlava NOT IN (SELECT ID FROM dbo._temp_TabOdvodLis)'); Helios.ExecSQL('IF OBJECT_ID(N''dbo._temp_TabOdvodLis'') IS NOT NULL IF NOT EXISTS(SELECT * FROM dbo._temp_TabOdvodLis) DROP TABLE dbo._temp_TabOdvodLis'); Helios.ExecSQL('IF OBJECT_ID(N''dbo._temp_TabOdvodLisR'') IS NOT NULL IF NOT EXISTS(SELECT * FROM dbo._temp_TabOdvodLisR) DROP TABLE dbo._temp_TabOdvodLisR'); } Helios.ExecSQL('IF OBJECT_ID(N''tempdb..#TabOdvodLis'') IS NOT NULL DROP TABLE #TabOdvodLis'); Helios.ExecSQL('IF OBJECT_ID(N''tempdb..#TabOdvodLisR'') IS NOT NULL DROP TABLE #TabOdvodLisR'); Helios.ExecSQL('IF OBJECT_ID(N''tempdb..#TabOdvodLisZavady'') IS NOT NULL DROP TABLE #TabOdvodLisZavady'); lSQL:= 'IF OBJECT_ID(N''tempdb..#TabOdvodLis'') IS NULL CREATE TABLE #TabOdvodLis (ID INT IDENTITY(1,1) NOT NULL, IDPlan INT NOT NULL, CisZam INT NOT NULL,'; lSQL:= lSQL +' PocetVstriku SMALLINT NOT NULL DEFAULT 0, DeviceID NVARCHAR(40) DEFAULT HOST_NAME(), Autor NVARCHAR(80) NOT NULL DEFAULT SUSER_SNAME(),'; lSQL:= lSQL + ' DatPorizeni DATETIME NOT NULL DEFAULT GETDATE())' + CRLF; lSQL:= lSQL + 'IF OBJECT_ID(N''tempdb..#TabOdvodLisR'') IS NULL CREATE TABLE #TabOdvodLisR (ID INT IDENTITY(1,1) NOT NULL, IDHlava INT NOT NULL, IDRadek INT,'; lSQL:= lSQL + ' IDVyrCis INT, IDVyrCK INT, KsDobre NUMERIC(15,3) NOT NULL DEFAULT 0.0, KsVeForme SMALLINT DEFAULT 1, IDPrPostup INT, IDNaradi INT,'; lSQL:= lSQL + ' FormaPouzita BIT DEFAULT 1, PocetVstriku SMALLINT DEFAULT 0, DeviceID NVARCHAR(40) DEFAULT HOST_NAME())' + CRLF; lSQL:= lSQL + 'IF OBJECT_ID(N''tempdb..#TabOdvodLisZavady'') IS NULL CREATE TABLE #TabOdvodLisZavady (ID INT IDENTITY(1,1) NOT NULL, IDHlava INT NOT NULL,'; lSQL:= lSQL + ' IDRadek INT, IDPrPostup INT NOT NULL, IDZavady INT NOT NULL, Mnozstvi NUMERIC(19,6) NOT NULL DEFAULT 0.0, Autor NVARCHAR(80) DEFAULT SUSER_SNAME(),'; lSQL:= lSQL + ' DeviceID NVARCHAR(40) DEFAULT HOST_NAME())'; if (jeTest) then lSQL:= lSQL.Replace('#TabOdv', 'dbo._temp_TabOdv').Replace('tempdb..', ''); Helios.ExecSQL(lSQL); SetLength(zavady, 0); lSQL:= 'SELECT TOP(3) z.kod, e._PopisEvidence, e._PoziceEvidence FROM ' + tblCZavadE + ' e INNER JOIN ' + tblCZavad + ' z ON (z.ID=e.ID)'; lSQL:= lSQL + ' WHERE CHARINDEX(N''- VOSK'', CONVERT(nvarchar,Poznamka))>0 AND e._PoziceEvidence IS NOT NULL ORDER BY e._PoziceEvidence'; with Helios.OpenSQL(lSQL) do if (RecordCount>0) then begin SetLength(zavady, RecordCount); i:= 0; First; while not(EOF) do begin zavady[i].kod:= VarToStr(FieldValues(0)); zavady[i].popis:= VarToStr(FieldValues(1)); zavady[i].poz:= StrToInt(VarToStr(FieldValues(2))); Inc(i); Next; end; end; if (Length(zavady)>0) then begin for i:=0 to Length(zavady)-1 do begin i2:= grd.Columns.ItemBy['colZmetky' + (i+1).ToString].Index; grd.Columns.Item[i2].Header.Caption:= 'Z ' + zavady[i].popis; grd.Columns.Item[i2].Enabled:= true; end; end; grd.Repaint; end; procedure TformOdvLis.grdCellClick(Sender: TObject; ACol, ARow: Integer; Button: TMouseButton); var lSQL, naradiIDcka: string; idVPr, dokl: integer; R: TRect; begin if (ACol=colForma.Index) then begin R:= view.GetCellRect(ACol, ARow); R.Left:= R.Left + grd.Left; R.Right:= R.Right + grd.Left; R.Top:= R.Top + grd.Top; R.Bottom:= R.Bottom + grd.Top; with comboForma do begin Left:= R.Left + 1; Top:= R.Top + 1; Width:= (R.Right + 1) - R.Left; Height:= (R.Bottom + 1) - R.Top + 3; Visible:= true; if (grd.Cells[colIdPostup.Index, ARow]<>'') then begin idVPr:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT IDPrikaz FROM ' + tblPrPost + ' WHERE ID=' + grd.Cells[colIdPostup.Index, ARow]); dokl:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT Doklad FROM ' + tblPrPost + ' WHERE ID=' + grd.Cells[colIdPostup.Index, ARow]); if (idVPr>0) and (dokl>0) then begin lSQL:= 'SELECT k.RegCis, n.Naradi FROM ' + tblPrNVazby + ' n INNER JOIN ' + tblKZ + ' k ON (k.ID=n.Naradi) WHERE n.IDPrikaz='; lSQL:= lSQL + idVPr.ToString + ' AND n.Doklad=' + dokl.ToString + ' ORDER BY k.RegCis'; with Helios.OpenSQL(lSQL) do begin Items.Clear; First; while not(EOF) do begin lSQL:= Trim(VarToStr(FieldValues(0))); if (lSQL<>'') then Items.Add(lSQL); Next; end; if (Items.Count=1) then ItemIndex:= 0; end; end; end; SetFocus; end; end; if (ACol=colFormaSN.Index) and (grd.Cell[colIdNaradi.Index, ARow].AsInteger>0) then begin R:= view.GetCellRect(ACol, ARow); R.Left:= R.Left + grd.Left; R.Right:= R.Right + grd.Left; R.Top:= R.Top + grd.Top; R.Bottom:= R.Bottom + grd.Top; with comboSN do begin Left:= R.Left + 1; Top:= R.Top + 1; Width:= (R.Right + 1) - R.Left; Height:= (R.Bottom + 1) - R.Top + 3; Visible:= True; lSQL:= 'SELECT Nazev1 FROM ' + tblVyrCK + ' WHERE IDKmenZbozi=' + grd.Cells[colIdNaradi.Index, ARow]; with Helios.OpenSQL(lSQL) do begin Items.Clear; First; while not(EOF) do begin lSQL:= Trim(VarToStr(FieldValues(0))); if (lSQL<>'') then Items.Add(lSQL); Next; end; if (Items.Count=1) then ItemIndex:= 0; end; SetFocus; end; end; end; procedure TformOdvLis.selBarcodeClick(Sender: TObject); begin edtBarcode.Text:= Trim(edtBarcode.Text); oVar:= edtBarcode.Text; bidPlan:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT 100000+Cislo FROM ' + tblObecPrehled + ' WHERE NazevSys=N''hvw_PlanNasazeniLisu'''); if (bidPlan>0) then if Helios.Prenos(bidPlan, 'hvw_PlanNasazeniLisu.Barcode', oVar, 'hvw_PlanNasazeniLisu.Ukonceno=0', 'Vyberte plán', true) then begin edtBarcode.Text:= VarToStr(oVar); edtBarcodeExit(Sender); end; edtCisZam.SetFocus; end; procedure TformOdvLis.selCisZamClick(Sender: TObject); begin if Helios.Prenos(bidCisZam, 'TabCisZam.Cislo', oVar, '', 'Vyberte zaměstnance', true) then begin edtCisZam.Text:= VarToStr(oVar); selCisZamExit(Sender); end; end; procedure TformOdvLis.selCisZamExit(Sender: TObject); begin edtCisZam.Text:= helUtils.StripChars(edtCisZam.Text, ['''', '"', '-', ';', ' ']); edtCisZamExit(Sender); edtVstriku.SetFocus; end; procedure TformOdvLis.tblAfterPost(DataSet: TDataSet); var x: TField; begin if (Dataset.RecordCount=0) then Exit; { x:= Dataset.FieldByName('IDVyrCS'); if (x.AsInteger>0) then grd.Cells[colFormaSN.Index, tbl.RecNo-1]:= helUtils.getHeliosStrVal(Helios, '', 'SELECT Nazev1 FROM ' + tblVyrCS + ' WHERE ID=' + x.AsString); } end; procedure TformOdvLis.colFormaPouzitaCheckedChanged(Sender: TObject; ACol, ARow: Integer); var i: integer; begin if (grd.RowCount>=ARow+1) then begin if (grd.Cell[colFormaPouzita.Index, ARow].AsBoolean=false) then begin tbl.Edit; tbl.FieldByName('PocetVstriku').AsInteger:= 0; tbl.Post; for i:=0 to grd.ColCount-1 do begin grd.Cell[i, ARow].Font.Style:= grd.Cell[i, ARow].Font.Style + [fsItalic]; grd.Cell[i, ARow].Font.Color:= clRed; end; end else begin tbl.Edit; tbl.FieldByName('PocetVstriku').AsInteger:= edtVstriku.IntValue; tbl.Post; for i:=0 to grd.ColCount-1 do begin grd.Cell[i, ARow].Font.Style:= grd.Cell[i, ARow].Font.Style - [fsItalic]; grd.Cell[i, ARow].Font.Color:= clBlack; end; end; end; grd.Refresh; end; procedure TformOdvLis.viewGetCellColor(Sender: TObject; ACol, ARow: Integer; var CellColor: TColor; State: TNxCellPaintingState); begin if (grd.RowCount>=ARow+1) then begin if (ACol=colKsVeForme.Index) then if (grd.Cells[ACol, ARow]='0') then begin CellColor:= clRed; grd.Cell[ACol, ARow].Enabled:= true; end else begin // CellColor:= clWindow; grd.Cell[ACol, ARow].Enabled:= false; end; end; end; end.