unit frmDemontaz; interface uses Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics, Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.StdCtrls, Vcl.Mask, RzEdit, RzBtnEdt, Data.DB, Vcl.Buttons, FireDAC.Comp.Client, ddPlugin_TLB, Vcl.ComCtrls, RzDTP, Vcl.Menus, FireDAC.Stan.Intf, FireDAC.Stan.Option, FireDAC.Stan.Param, FireDAC.Stan.Error, FireDAC.DatS, FireDAC.Phys.Intf, FireDAC.DApt.Intf, FireDAC.Comp.DataSet, Vcl.Grids, Vcl.DBGrids, JvExDBGrids, JvDBGrid; const VyrSkl = '200'; VyrRekl = '20000001'; // definice rad dokladu v kodu // 441 - vydej demontaz dilcu/polosestav // 241 - prijem materialu demontaz type TformDemontaz = class(TForm) GroupBox1: TGroupBox; GroupBox2: TGroupBox; btnOK: TButton; btnStorno: TButton; GroupBox3: TGroupBox; Label1: TLabel; Label2: TLabel; selSrcSkl: TRzButtonEdit; selDstSkl: TRzButtonEdit; selSrcSklNazev: TRzButtonEdit; selDstSklNazev: TRzButtonEdit; selRegCis: TRzButtonEdit; Label4: TLabel; lblNazev: TLabel; edtMnoz: TRzNumericEdit; Label5: TLabel; btnRozeber: TSpeedButton; dsDilce: TDataSource; dsMat: TDataSource; cbMaxRozpad: TCheckBox; dtDatum: TRzDateTimePicker; popMenu: TPopupMenu; pmOprav: TMenuItem; pmSmazat: TMenuItem; N1: TMenuItem; Label7: TLabel; selVydejIT: TRzButtonEdit; selSZ: TRzButtonEdit; lblVerze: TLabel; cbTiskRozp: TCheckBox; tabMat: TFDMemTable; colMidKZ: TIntegerField; colMidSS: TIntegerField; colMSZ: TStringField; colMRegCis: TStringField; colMNazev: TStringField; colMMnoz: TFloatField; colMMnozIT: TFloatField; colMCilSklad: TStringField; tabDilce: TFDMemTable; colDidKZ: TIntegerField; colDidSS: TIntegerField; colDSZ: TStringField; colDRegCis: TStringField; colDNazev: TStringField; colDMnoz: TFloatField; lblZpracovavam: TLabel; Label3: TLabel; selCisZam: TRzButtonEdit; lblZamest: TLabel; colMidSSold: TIntegerField; popMat: TPopupMenu; pmAll2IT: TMenuItem; N2: TMenuItem; pmDel2IT: TMenuItem; tabMatCilSkladCislo: TStringField; grdDilce: TJvDBGrid; grdMat: TJvDBGrid; procedure btnStornoClick (Sender: TObject); procedure FormClose (Sender: TObject; var Action: TCloseAction); procedure btnRozeberClick (Sender: TObject); procedure selSZButtonClick (Sender: TObject); procedure selRegCisButtonClick (Sender: TObject); procedure selSZExit (Sender: TObject); procedure selSrcSklButtonClick (Sender: TObject); procedure selDstSklButtonClick (Sender: TObject); procedure FormShow (Sender: TObject); procedure tabDilceAfterPost (DataSet: TDataSet); procedure FormActivate (Sender: TObject); procedure btnOKClick (Sender: TObject); procedure pmOpravClick (Sender: TObject); procedure pmSmazatClick (Sender: TObject); procedure tabDilceAfterDelete (DataSet: TDataSet); procedure selVydejITButtonClick (Sender: TObject); procedure selCisZamButtonClick (Sender: TObject); procedure selCisZamExit (Sender: TObject); procedure selRegCisExit (Sender: TObject); procedure pmAll2ITClick (Sender: TObject); procedure pmDel2ITClick (Sender: TObject); procedure edtMnozKeyDown (Sender: TObject; var Key: Word; Shift: TShiftState); procedure grdDilceDblClick (Sender: TObject); procedure grdMatDblClick (Sender: TObject); private procedure ZjistiKmen; function TestMnozOK: boolean; function TestMatDoIT: boolean; safecall; procedure DemontujVybrane; safecall; // procedure UlozPolozkyMatIT(idDokl: integer); safecall; public Helios: IHelios; idPols: TArray; jeTest: Boolean; end; var formDemontaz: TformDemontaz; idKZ, idSS, idDZVyd: integer; oVar1, oVar2: OleVariant; sklSrc, sklDst, sklVyd, verText: string; implementation uses System.StrUtils, Vcl.Clipbrd, helUtils; {$R *.dfm} procedure TformDemontaz.DemontujVybrane; var lSQL: string; idx: integer; begin if (Length(idPols)>0) then begin tabDilce.EmptyDataSet; for idx:=0 to Length(idPols)-1 do begin idSS:= idPols[idx]; lSQL:= 'SELECT kz.Id, kz.SkupZbo, kz.RegCis, kz.Nazev1, ss.Mnozstvi FROM ' + tblSS + ' ss INNER JOIN '; lSQL:= lSQL + tblKZ + ' kz ON (kz.Id=ss.IdKmenZbozi) WHERE ss.id=' + IntToStr(idSS); with Helios.OpenSQL(lSQL) do begin idKZ:= StrToInt(VarToStr(FieldValues(0))); selSZ.Text:= VarToStr(FieldValues(1)); selRegCis.Text:= VarToStr(FieldValues(2)); lblNazev.Caption:= VarToStr(FieldValues(3)); edtMnoz.Value:= StrToFloat(VarToStr(FieldValues(4))); btnRozeberClick(nil); end; end; end; end; procedure TformDemontaz.edtMnozKeyDown(Sender: TObject; var Key: Word; Shift: TShiftState); begin if (Key=VK_RETURN) then btnRozeberClick(Sender); end; { procedure TformDemontaz.UlozPolozkyMatIT(idDokl: Integer); var kurz,lSkl,mena,vc,dp,cOrg,jc: string; locSQL: string; idSS,idPZ: integer; begin with Helios.OpenSQL('SELECT idMat,MnozIT FROM #TabMaterialyIT WHERE ISNULL(MnozIT,0)>0') do if (RecordCount>0) then begin kurz:= '1'; lSkl:= ''; vc:= '0'; mena:= 'CZK'; with Helios.OpenSQL('SELECT Kurz,IDSklad,Mena,VstupniCena,DruhPohybuZbo,CisloOrg FROM ' + tblDZ + ' WHERE id=' + IntToStr(idDokl)) do begin kurz:= VarToStr(FieldValues(0)); lSkl:= VarToStr(FieldValues(1)); mena:= VarToStr(FieldValues(2)); vc:= VarToStr(FieldValues(3)); dp:= VarToStr(FieldValues(4)); cOrg:= VarToStr(FieldValues(5)); end; kurz:= StringReplace(kurz,',','.',[rfReplaceAll]); First; while not(EOF) do begin idSS:= 0; locSQL:= 'IF NOT EXISTS(SELECT id FROM ' + tblSS + ' WHERE IDKmenZbozi=' + VarToStr(FieldValues(0)); locSQL:= locSQL + ' AND IDSklad=N' + QuotedStr(lSkl) + ') INSERT ' + tblSS + ' (IDSklad,IdKmenZbozi) VALUES (N'; locSQL:= locSQL + QuotedStr(lSkl) + ',' + VarToStr(FieldValues(0)) + ')'; Helios.ExecSQL(locSQL); locSQL:= 'SELECT id FROM ' + tblSS + ' WHERE IDKmenZbozi=' + VarToStr(FieldValues(0)) + ' AND IDSklad=N' + QuotedStr(lSkl); with Helios.OpenSQL(locSQL) do if (RecordCount>0) then begin if not(VarIsNull(FieldValues(0))) then idSS:= StrToInt(VarToStr(FieldValues(0))); end; if (idSS>0) then begin idPZ:= 0; locSQL:= 'DECLARE @idOut INT, @dt DATETIME' + CRLF + 'SET @dt=GETDATE()'; locSQL:= locSQL + CRLF + 'EXEC dbo.hp_InsertPolozkyOZ @idOut OUT,@IDDoklad=' + IntToStr(idDokl) + ',@DruhPohybu=' + dp; locSQL:= locSQL + ',@CisloOrg=' + cOrg + ',@IDZboSklad=' + IntToStr(idSS) + ',@Mena=N' + QuotedStr(mena) + ',@Kurz='; locSQL:= locSQL + kurz + ',@JednotkaMeny=1,@KurzEuro=0,@SazbaSD=0,@SazbaDPH=0,@ZakazanoDPH=0,@VstupniCena='; locSQL:= locSQL + vc + ',@Mnozstvi=' + StringReplace(VarToStr(FieldValues(1)),',','.',[rfReplaceAll]); locSQL:= locSQL + ',@JCbezDaniKC=0,@PovolitDuplicitu=1,@DatPorizeni=@dt,@Selectem=0' + CRLF; locSQL:= locSQL + 'SELECT @idOut'; try with Helios.OpenSQL(locSQL) do idPZ:= StrToInt(VarToStr(FieldValues(0))); except on E:Exception do begin idPZ:= 0; end; end; if (idPZ>0) then begin // update ceny na JC jc:= '0.1'; locSQL:= 'SELECT ISNULL(EcJC,0) FROM ' + tblJC + ' WHERE IdStavSkladu=' + IntToStr(idSS); with Helios.OpenSQL(locSQL) do if (RecordCount=1) then jc:= StringReplace(VarToStr(FieldValues(0)),',','.',[rfReplaceAll]); locSQL:= 'UPDATE ' + tblPZ + ' SET JcBezDaniKc=' + jc + ' WHERE id=' + IntToStr(idPZ); Helios.ExecSQL(locSQL); locSQL:= 'UPDATE ' + tblPZ + ' SET CCbezDaniKc=JcBezDaniKc*Mnozstvi,CCsDPHKc=JcBezDaniKc*Mnozstvi,CCbezDaniVal=JcBezDaniKc*Mnozstvi,'; locSQL:= locSQL + 'CCsDPHVal=JcBezDaniKc*Mnozstvi,CCbezDaniKcPoS=JcBezDaniKc*Mnozstvi,CCsDPHKcPoS=JcBezDaniKc*Mnozstvi,'; locSQL:= locSQL + 'CCbezDaniValPoS=JcBezDaniKc*Mnozstvi,CCsDPHValPoS=JcBezDaniKc*Mnozstvi,CCsSDKc=JcBezDaniKc*Mnozstvi,'; locSQL:= locSQL + 'CCsSDVal=JcBezDaniKc*Mnozstvi,BlokovaniEditoru=NULL WHERE id=' + IntToStr(idPZ); Helios.ExecSQL(locSQL); end; end; Next; end; end; end; } function TformDemontaz.TestMatDoIT; var idx,i: integer; bmk: TBookmark; begin result:= true; tabMat.DisableControls; if (tabMat.RecordCount>0) then begin bmk:= tabMat.GetBookmark; for idx:=1 to tabMat.RecordCount do begin tabMat.RecNo:= idx; if (tabMat.FieldByName('MnozstviIT').AsString<>'') then if (tabMat.FieldByName('MnozstviIT').AsExtended > tabMat.FieldByName('Mnozstvi').AsExtended) then result:= false; end; if (tabMat.BookmarkValid (bmk)) then tabMat.GotoBookmark (bmk); tabmat.FreeBookmark (bmk); end; tabMat.EnableControls; end; function TformDemontaz.TestMnozOK: boolean; var mn: extended; begin mn:= 0; result:= false; if (idSS>0) and (edtMnoz.Value>0) then begin // if tabDilce.Locate('idSS',VarArrayOf([idSS]),[loCaseInsensitive]) then // mn:= tabDilce.FieldByName('Mnozstvi').AsExtended; // jiz pozadovane mnozstvi na demontaz with Helios.OpenSQL('SELECT Mnozstvi FROM ' + tblSS + ' WHERE id=' + IntToStr(idSS)) do if (StrToFloat(VarToStr(FieldValues(0))))>=(edtMnoz.Value) then result:= true; end; end; procedure TformDemontaz.grdDilceDblClick (Sender: TObject); var fldName: string; clCol, clRow: integer; iR, iC: Integer; begin clCol:= grdDilce.MouseCoord (Mouse.CursorPos.X - grdDilce.ClientOrigin.X, Mouse.CursorPos.Y - grdDilce.ClientOrigin.Y).X; clRow:= grdDilce.MouseCoord (Mouse.CursorPos.X - grdDilce.ClientOrigin.X, Mouse.CursorPos.Y - grdDilce.ClientOrigin.Y).X; if (clRow>0) and (clCol>0) then begin fldName:= grdDilce.Columns[clCol-1].FieldName; if (iC=5) then begin idKZ:= tabDilce.FieldByName('idKZ').AsInteger; idSS:= tabDilce.FieldByName('idSS').AsInteger; // vDilce.DataController.FocusedRecordIndex:= iR; pmOpravClick (Sender); grdDilce.Refresh; // gDilce.Refresh; end; // DemontujVybrane; end; end; procedure TformDemontaz.grdMatDblClick (Sender: TObject); var sql, strMn,skl: string; mn, jc: Extended; mnOld, mnIT: Extended; iR, iC: integer; podm, iO: string; clCol, clRow: integer; fldName: string; begin clCol:= grdMat.MouseCoord (Mouse.CursorPos.X - grdMat.ClientOrigin.X, Mouse.CursorPos.Y - grdMat.ClientOrigin.Y).X; clRow:= grdMat.MouseCoord (Mouse.CursorPos.X - grdMat.ClientOrigin.X, Mouse.CursorPos.Y - grdMat.ClientOrigin.Y).X; if (clRow>0) and (clCol>0) and (tabMat.RecordCount>0) then begin fldName:= grdMat.Columns[clCol].FieldName; case helUtils.StringToCaseSelect(fldName, ['CilSklad', 'MnozstviIT']) of 0: begin // cilovy sklad skl:= ''; iO:= IntToStr(Helios.Obdobi); try sql:= 'DECLARE @i INT; DECLARE @dt DATETIME; SET @dt=CONVERT(datetime,N' + QuotedStr(DateToStr(dtDatum.Date)) + ',104); '; sql:= sql + 'EXEC @i=dbo.hp_GetObdobiDleDatumu @Datum=@dt, @JakyDatum=1; SELECT @i'; with Helios.OpenSQL(sql) do iO:= VarToStr(FieldValues(0)); except end; podm:= 'TabStrom.Id IN (SELECT IDStrom FROM dbo.TabStromDef WHERE Blokovano=0 AND Zakazano=0 AND IdObdobi=' + iO; podm:= podm + ') AND TabStrom.Cislo LIKE N' + QuotedStr('2%'); if Helios.Prenos(bidStrom,'Cislo',oVar1,podm,'Vyberte cílový sklad',true) then skl:= VarToStr(oVar1); with Helios.OpenSQL('SELECT e._ShortName FROM ' + tblStrom + ' s INNER JOIN ' + tblStromE + ' e ON (e.Id=s.Id) WHERE s.Cislo=N' + QuotedStr(skl)) do if (RecordCount=1) then begin tabMat.Edit; sql:= 'SELECT s.id FROM ' + tblSS + ' s INNER JOIN ' + tblKZ + ' k ON (k.Id=s.IdKmenZbozi) WHERE s.IDSklad=N' + QuotedStr(skl); sql:= sql + ' AND k.id=' + tabMat.FieldByName('idKZ').AsString; with Helios.OpenSQL(sql) do if (RecordCount=0) then begin sql:= 'INSERT ' + tblSS + ' (IDSklad,IdKmenZbozi) SELECT N' + QuotedStr(skl) + ', ' + tabMat.FieldByName('idKZ').AsString; sql:= sql + '; SELECT SCOPE_IDENTITY()'; with Helios.OpenSQL(sql) do tabMat.FieldByName('idSS').AsString:= VarToStr(FieldValues(0)); end else tabMat.FieldByName('idSS').AsString:= VarToStr(FieldValues(0)); tabMat.FieldByName('CilSkladCislo').AsString:= StringReplace(skl,'.','',[rfReplaceAll]); skl:= VarToStr(FieldValues(0)); tabMat.FieldByName('CilSklad').AsString:= skl; tabMat.Post; end; end; 1: begin // mnozstvi do IT jc:= 0; mnOld:= tabMat.FieldByName('Mnozstvi').AsExtended; // mnOld:= StrToFloat(VarToStr(ACellViewInfo.GridRecord.Values[5])); // tabDilce.FieldByName('Mnozstvi').AsExtended; mnIT:= 0; mn:= mnOld; { if (VarToStr(ACellViewInfo.GridRecord.Values[7])<>'') then mnIT:= StrToFloat(VarToStr(ACellViewInfo.GridRecord.Values[7])); // MnozstviIT } if not(tabMat.FieldByName('MnozstviIT').IsNull) then mnIT:= tabMat.FieldByName('MnozstviIT').AsExtended; if (mnIT>0) then mn:= mnIT; if (FormMnozstviCena('Zadejte nové množství na vývoz',mn,jc,false,false)) then begin if (mn>=0) then begin if (mn<=mnOld) then begin grdMat.LockDrawing; tabMat.Edit; if (mn=0) then tabMat.FieldByName('MnozstviIT').AsString:= '' else tabMat.FieldByName('MnozstviIT').AsExtended:= mn; tabMat.Post; grdMat.UnlockDrawing; strMn:= StringReplace(FloatToStr(mn),',','.',[rfReplaceAll]); sql:= 'UPDATE #TabMaterialyIT SET MnozIT=' + IfThen(mn=0, 'NULL', strMn); sql:= sql + ' WHERE idMat=' + tabMat.FieldByName('idKZ').AsString; Helios.ExecSQL(sql); end else Helios.Error(#1'Nelze zadat množství vyšší než demontované.'#1); end; end; end; end; end; end; procedure TformDemontaz.btnOKClick (Sender: TObject); var idDZv, idDZp, idPZ, idDZPrev, idDZPrev2, idVydVDST, idVydPCO, idVydPikl: integer; rada, cZak, kurz, jc, idSS, idSSold: string; mnV, mnIT, mnZbyt: Extended; locSQL: string; i: integer; cont: Boolean; begin if (tabMat.RecordCount>0) then begin selCisZam.Text:= Trim(selCisZam.Text); Self.Refresh; try locSQL:= 'SELECT idMat FROM #TabMaterialyIT WHERE ISNULL(MnozIT,0)>0'; with Helios.OpenSQL(locSQL) do if (RecordCount>0) then if (selVydejIT.Text='') then begin Helios.Error(#1'Není vybrána výdejka do Lovato IT.'#1); idDZVyd:= 0; selVydejIT.SetFocus; Exit; end; except end; if not(TestMatDoIT) then begin Helios.Error(#1'V kusech pro vývoz je větší množství než v demontáži.'#1); Exit; end; lblZpracovavam.Visible:= true; ReseedTable(Helios,tblDZ); ReseedTable(Helios,tblPZ); ReseedTable(Helios,tblZak); // tabDilce Mnozstvi, idKZ, idSS, SZ, RegCis, Nazev locSQL:= 'IF OBJECT_ID(' + QuotedStr('tempdb..#TabHDCDemont') + ') IS NOT NULL DROP TABLE #TabHDCDemont' + CRLF; locSQL:= locSQL + 'CREATE TABLE #TabHDCDemont (SklSrc NVARCHAR(30), SklDst NVARCHAR(30), IdKmenRozpad INT, Mnozstvi NUMERIC(19,6) DEFAULT 0.0, Datum DATETIME'; locSQL:= locSQL + ', CisloZam INT, IDVydIT INT)'; Helios.ExecSQL(locSQL); i:= tabDilce.RecNo; grdDilce.LockDrawing; tabDilce.First; while not (tabDilce.Eof) do begin locSQL:= 'INSERT #TabHDCDemont (SklSrc, SklDst, IdKmenRozpad, Mnozstvi, Datum, CisloZam, IDVydIT) SELECT N' + QuotedStr(sklSrc) + ', N' + QuotedStr(sklDst); locSQL:= locSQL + ', ' + tabDilce.FieldByName('idKZ').AsString + ', ' + StringReplace(tabDilce.FieldByName('Mnozstvi').AsString,',','.',[rfReplaceAll]); locSQL:= locSQL + ', CONVERT(datetime,N' + QuotedStr(DateToStr(dtDatum.Date)) + ',104), ' + IfThen(selCisZam.Text='','null',selCisZam.Text); locSQL:= locSQL + ', ' + IfThen(idDZVyd=0,'null',IntToStr(idDZVyd)); Helios.ExecSQL(locSQL); tabDilce.Next; end; tabDilce.RecNo:= i; grdDilce.UnlockDrawing; // tabMat - SZ, RegCis, Nazev, idKZ, Mnozstvi, idSSold, idSS, MnozstviIT, CilSklad locSQL:= 'IF OBJECT_ID(' + QuotedStr('tempdb..#TabHDCDemontMat') + ') IS NOT NULL DROP TABLE #TabHDCDemontMat' + CRLF; locSQL:= locSQL + 'CREATE TABLE #TabHDCDemontMat (idKZ INT, idSS INT, idSSold INT, Mnozstvi NUMERIC(19,6) DEFAULT 0.0, MnozstviIT NUMERIC(19,6) DEFAULT 0.0'; locSQL:= locSQL + ', CilSklad NVARCHAR(30))'; Helios.ExecSQL(locSQL); i:= tabDilce.RecNo; grdMat.LockDrawing; tabMat.First; while not (tabMat.Eof) do begin locSQL:= 'INSERT #TabHDCDemontMat (idKZ, idSS, idSSold, Mnozstvi, MnozstviIT, CilSklad) SELECT ' + tabMat.FieldByName('idKZ').AsString; locSQL:= locSQL + ', ' + tabMat.FieldByName('idSS').AsString + ', ' + tabMat.FieldByName('idSSold').AsString; locSQL:= locSQL + ', ' + IfThen(tabMat.FieldByName('Mnozstvi').AsString='', '0', StringReplace(tabMat.FieldByName('Mnozstvi').AsString,',','.',[rfReplaceAll])); locSQL:= locSQL + ', ' + IfThen(tabMat.FieldByName('MnozstviIT').AsString='', '0', StringReplace(tabMat.FieldByName('MnozstviIT').AsString,',','.',[rfReplaceAll])); locSQL:= locSQL + ', N' + QuotedStr(IfThen(tabMat.FieldByName('CilSkladCislo').AsString='', sklDst, tabMat.FieldByName('CilSkladCislo').AsString)); Helios.ExecSQL(locSQL); tabMat.Next; end; tabMat.RecNo:= i; grdMat.LockDrawing; try try // 1 - chyba pri ukladani hlavicky vydejky demontaze / 2 - / 3 - chyba pri realizaci prijemky with Helios.OpenSQL('DECLARE @chyba INT; EXEC @chyba=dbo.ep_HDC_ZpracujDemontaz; SELECT @chyba;') do rada:= VarToStr(FieldValues(0)); except on E:Exception do Helios.Error(#1'Chyba: ' + E.Message + #1); end; finally begin locSQL:= 'IF OBJECT_ID(' + QuotedStr('tempdb..#TabHDCDemontMat') + ') IS NOT NULL DROP TABLE #TabHDCDemontMat'; Helios.ExecSQL(locSQL); locSQL:= 'IF OBJECT_ID(' + QuotedStr('tempdb..#TabHDCDemont') + ') IS NOT NULL DROP TABLE #TabHDCDemont'; Helios.ExecSQL(locSQL); end; end; { if (1=0) then // if Helios.Prenos(bidDDZ,'RadaDokladu',oVar1,'TabDruhDokZbo.DruhPohybuZbo=4','Řada dokladů - Demontáž výdejka dílců/polosestav',true) then begin cont:= true; // rada:= VarToStr(oVar1); rada:= '441'; locSQL:= 'DECLARE @Ident INT,@dt DATETIME'+ CRLF + 'SET @dt=CONVERT(datetime,N' + QuotedStr(DateTimeToStr(dtDatum.DateTime)) + ',104)'; locSQL:= locSQL + CRLF + 'EXEC dbo.hp_InsertHlavickyOZ @Ident OUT,@Sklad=N' + QuotedStr(sklSrc); locSQL:= locSQL + ',@DruhPohybu=4,@RadaDokladu=N' + QuotedStr(rada) +',@Insert=1,@PC=NULL,@Mena=N' + QuotedStr('CZK'); locSQL:= locSQL + ',@CisloOrg=0,@DatumPorizeni=@dt' + CRLF + 'SELECT @Ident'; idDZv:= 0; try with Helios.OpenSQL(locSQL) do idDZv:= StrToInt(VarToStr(FieldValues(0))); // zjisti ID vytvoreneho dokladu except on E:Exception do Helios.Error(#1'Nelze uložit hlavičku Výdejky pro Demontáž.'#1 + CRLF + E.Message + CRLF + locSQL); end; if (idDZv>0) then begin tabDilce.First; while not(tabDilce.Eof) do begin locSQL:= 'DECLARE @idOut INT, @dt DATETIME' + CRLF + 'SET @dt=CONVERT(datetime,N' + QuotedStr(DateTimeToStr(dtDatum.DateTime)) + ',104)'; locSQL:= locSQL + CRLF + 'EXEC dbo.hp_InsertPolozkyOZ @idOut OUT,@IDDoklad=' + IntToStr(idDZv) + ',@DruhPohybu=4,@CisloOrg=0,@IDZboSklad='; locSQL:= locSQL + tabDilce.FieldByName('idSS').AsString + ',@Mena=N' + QuotedStr('CZK') + ',@Kurz=1,@JednotkaMeny=1,@KurzEuro=0'; locSQL:= locSQL + ',@SazbaSD=0,@SazbaDPH=0,@ZakazanoDPH=0,@VstupniCena=0,@Mnozstvi='; locSQL:= locSQL + StringReplace(tabDilce.FieldByName('Mnozstvi').AsString,',','.',[rfReplaceAll]); locSQL:= locSQL + ',@JCbezDaniKC=0,@PovolitDuplicitu=1,@DatPorizeni=@dt,@Selectem=0' + CRLF; locSQL:= locSQL + 'SELECT @idOut'; try with Helios.OpenSQL(locSQL) do idPZ:= StrToInt(VarToStr(FieldValues(0))); except on E:Exception do begin Helios.Error(#1'Nelze uložit položku Příjemky pro Demontáž.'#1 + CRLF + E.Message); cont:= false; end; end; tabDilce.Next; end; if (cont) then // polozky ulozeny OK begin locSQL:= 'DECLARE @BylaChyba BIT' + CRLF + 'EXEC dbo.hp_Realizuj_Vydej @Id=' + IntToStr(idDZv) + ',@DatumRealizace=NULL,@Uzivatel=' + IntToStr(Helios.UserId); locSQL:= locSQL + ',@Hlidat=0,@BylaChyba=@BylaChyba OUT' + CRLF + 'SELECT @BylaChyba'; try with Helios.OpenSQL(locSQL) do if (VarToStr(FieldValues(0))='1') then Helios.Error(#1'Nelze realizovat výdejku.'#1); locSQL:= 'INSERT dbo._TabRekuperace (IdKmenZbozi,Mnozstvi,Autor,SrcSklad) VALUES (' + tabDilce.FieldByName('idKZ').AsString; locSQL:= locSQL + ',' + StringReplace(tabDilce.FieldByName('Mnozstvi').AsString,',','.',[rfReplaceAll]); locSQL:= locSQL + ',N'+QuotedStr(LeftStr(lblZamest.Caption,80)) + ',N' + QuotedStr(selSrcSkl.Text) + ')'; Helios.ExecSQL(locSQL); except on E:Exception do begin Helios.Error(#1'Nelze realizovat výdejku.'#1 + CRLF + E.Message); end; end; end; end; end; // if Helios.Prenos(bidDDZ,'RadaDokladu',oVar1,'TabDruhDokZbo.DruhPohybuZbo=0','Řada dokladů - Demontáž příjemka materiálu',true) then if (cont) then begin // cont:= true; rada:= '241'; // rada:= VarToStr(oVar1); locSQL:= 'DECLARE @Ident INT,@dt DATETIME'+ CRLF + 'SET @dt=CONVERT(datetime,N' + QuotedStr(DateTimeToStr(dtDatum.DateTime)) + ',104)'; locSQL:= locSQL + CRLF + 'EXEC dbo.hp_InsertHlavickyOZ @Ident OUT,@Sklad=N' + QuotedStr(sklDst); locSQL:= locSQL + ',@DruhPohybu=0,@RadaDokladu=N' + QuotedStr(rada) +',@Insert=1,@PC=NULL,@Mena=N' + QuotedStr('CZK'); locSQL:= locSQL + ',@CisloOrg=0,@DatumPorizeni=@dt' + CRLF + 'SELECT @Ident'; idDZp:= 0; try with Helios.OpenSQL(locSQL) do idDZp:= StrToInt(VarToStr(FieldValues(0))); // zjisti ID vytvoreneho dokladu except on E:Exception do Helios.Error(#1'Nelze uložit hlavičku Příjemky pro Demontáž.'#1 + CRLF + E.Message); end; if (idDZp>0) then begin locSQL:= 'IF OBJECT_ID(N''dbo._TabMaterialy'') IS NULL CREATE TABLE dbo._TabMaterialy (idKZ INT,'; locSQL:= locSQL + 'Mnoz NUMERIC(19,6), MnozIT NUMERIC(19,6), Sklad NVARCHAR(30), Autor NVARCHAR(50) DEFAULT SUSER_SNAME() )' + CRLF; locSQL:= locSQL + 'DELETE FROM dbo._TabMaterialy WHERE Autor=SUSER_SNAME()' + CRLF; Helios.ExecSQL(locSQL); tabMat.DisableControls; tabMat.First; while not(tabMat.Eof) do begin mnV:= tabMat.FieldByName('Mnozstvi').AsExtended; mnIT:= 0; mnZbyt:= 0; jc:= '0'; idKZ:= tabMat.FieldByName('idKZ').AsInteger; idSS:= tabMat.FieldByName('idSS').AsString; idSSold:= tabMat.FieldByName('idSSold').AsString; if (idSSold='') and (idKZ>0) then begin locSQL:= 'SELECT id FROM ' + tblSS + ' WHERE IDSklad=N' + QuotedStr(sklSrc) + ' AND IDKmenZbozi=' + IntToStr(idKZ); with Helios.OpenSQL(locSQL) do if (RecordCount=1) then idSSold:= VarToStr(FieldValues(0)); end; with Helios.OpenSQL('SELECT ISNULL(EcJc,0) FROM ' + tblJC + ' WHERE IdStavSkladu=' + idSS) do jc:= StringReplace(VarToStr(FieldValues(0)),',','.',[rfReplaceAll]); if (jc='0') or (jc='') then with Helios.OpenSQL('SELECT ISNULL(Prumer,0) FROM ' + tblSS + ' WHERE id=' + idSS) do jc:= StringReplace(VarToStr(FieldValues(0)),',','.',[rfReplaceAll]); if ((jc='0') or (jc='')) and (idSSold<>'') then with Helios.OpenSQL('SELECT ISNULL(Prumer,0) FROM ' + tblSS + ' WHERE id=' + idSSold) do jc:= StringReplace(VarToStr(FieldValues(0)),',','.',[rfReplaceAll]); if (jc='0') or (jc='') then jc:= '0.1'; mnIT:= 0; with Helios.OpenSQL('SELECT ISNULL(MnozIT,0) FROM #TabMaterialyIT WHERE idMat=' + tabMat.FieldByName('idKZ').AsString) do if (RecordCount>0) then mnIT:= StrToFloat(VarToStr(FieldValues(0))); if (sklDst=VyrRekl) and (idDZVyd>0) then mnIT:= 0; locSQL:= 'INSERT dbo._TabMaterialy (idKZ, Mnoz, MnozIT, Sklad) VALUES (' + tabMat.FieldByName('idKZ').AsString; locSQL:= locSQL + ',' + StringReplace(FloatToStr(mnV),',','.',[rfReplaceAll]); locSQL:= locSQL + ',' + StringReplace(FloatToStr(mnIT),',','.',[rfReplaceAll]) + ','; locSQL:= locSQL + IfThen(tabMat.FieldByName('CilSklad').AsString='','null','N' + QuotedStr(tabMat.FieldByName('CilSklad').AsString)); locSQL:= locSQL + ')'; try Helios.ExecSQL(locSQL); except on E:Exception do Helios.Error(#1'Chyba zápisu: ' + E.Message + #1); end; // prijemka na material na sklad Rekuperace (Reklamace demontované) locSQL:= 'DECLARE @idOut INT, @dt DATETIME' + CRLF + 'SET @dt=CONVERT(datetime,N' + QuotedStr(DateTimeToStr(dtDatum.DateTime)) + ',104)'; locSQL:= locSQL + CRLF + 'EXEC dbo.hp_InsertPolozkyOZ @idOut OUT,@IDDoklad=' + IntToStr(idDZp) + ',@DruhPohybu=0,@CisloOrg=0,@IDZboSklad='; locSQL:= locSQL + tabMat.FieldByName('idSS').AsString + ',@Mena=N' + QuotedStr('CZK') + ',@Kurz=1,@JednotkaMeny=1,@KurzEuro=0'; locSQL:= locSQL + ',@SazbaSD=0,@SazbaDPH=0,@ZakazanoDPH=0,@VstupniCena=0,@Mnozstvi='; locSQL:= locSQL + StringReplace(FloatToStr(mnV),',','.',[rfReplaceAll]); locSQL:= locSQL + ',@JCbezDaniKC=' + StringReplace(jc,',','.',[rfReplaceAll]) + ',@PovolitDuplicitu=1,@DatPorizeni=@dt,@Selectem=0' + CRLF; locSQL:= locSQL + 'SELECT ISNULL(@idOut,0)'; try with Helios.OpenSQL(locSQL) do idPZ:= StrToInt(VarToStr(FieldValues(0))); except on E:Exception do begin Helios.Error(#1'Nelze uložit položku Příjemky pro Demontáž.'#1 + CRLF + E.Message); cont:= false; end; end; if (idPZ>0) then begin locSQL:= 'EXEC dbo.ep_PZ_AktualizujCenu @idPZ=' + IntToStr(idPZ) + ',@typCeny=0,@jc=' + StringReplace(jc,',','.',[rfReplaceAll]); Helios.ExecSQL(locSQL); end; tabMat.Next; end; tabMat.EnableControls; if (cont) then // polozky prijemky mat ulozeny OK begin locSQL:= 'DECLARE @BylaChyba BIT'+ CRLF + 'EXEC dbo.hp_Realizuj_Prijem @Id=' + IntToStr(idDZp); locSQL:= locSQL + ',@DatumRealizace=NULL,@Uzivatel=' + IntToStr(Helios.UserId) + ',@BylaChyba=@BylaChyba OUT' + CRLF + 'SELECT @BylaChyba'; try with Helios.OpenSQL(locSQL) do if (VarToStr(FieldValues(0))='1') then Helios.Error(#1'Nelze realizovat příjemku.'#1); except on E:Exception do Helios.Error(#1'Nelze realizovat příjemku na sklad Demontáž.'#1 + CRLF + E.Message); end; try Helios.ExecSQL('EXEC dbo.ep_ZpracujVydejZDemontaze ' + IntToStr(idDZVyd)); except on E:Exception do Helios.Error(#1'Nelze zpracovat výdejky/převodky pro kooperanty.'#1 + CRLF + E.Message); end; end; end; end; } { if (idDZv>0) and (idDZp>0) then begin locSQL:= 'SELECT ISNULL((SELECT TOP(1) CisloZakazky FROM ' + tblZak + ' WHERE Rada=N' + QuotedStr('300') + ' ORDER BY CisloZakazky DESC),0)'; with Helios.OpenSQL(locSQL) do cZak:= IntToStr(StrToInt(VarToStr(FieldValues(0)))+1); if (cZak='1') then with Helios.OpenSQL('SELECT CisloPrefixDef,CisloDelka FROM [dbo].[TabZakazkaRada] WHERE Rada=N' + QuotedStr('300')) do cZak:= VarToStr(FieldValues(0)) + StringOfChar('0', StrToInt(VarToStr(FieldValues(1))) - Length(cZak)) + cZak; locSQL:= 'INSERT ' + tblZak + ' (Rada,CisloZakazky,Nazev) VALUES (N' + QuotedStr('300') + ',' + cZak + ',N'; locSQL:= locSQL + QuotedStr('Demontáž') + ')'; try Helios.ExecSQL(locSQL); finally Helios.ExecSQL('UPDATE ' + tblDZ + ' SET CisloZakazky=N' + QuotedStr(cZak) + ' WHERE id=' + IntToStr(idDZv)); Helios.ExecSQL('UPDATE ' + tblDZ + ' SET CisloZakazky=N' + QuotedStr(cZak) + ' WHERE id=' + IntToStr(idDZp)); Helios.ExecSQL('UPDATE ' + tblPZ + ' SET IdOldDoklad=' + IntToStr(idDZv) + ' WHERE IdDoklad=' + IntToStr(idDZp)); end; { if (sklDst<>VyrRekl) and (idDZVyd>0) then begin // udelej prijemku na sklad reklamace na polozky odesilane do IT cont:= true; idDZp:= 0; cont:= true; rada:= '241'; // rada:= VarToStr(oVar1); locSQL:= 'DECLARE @Ident INT,@dt DATETIME'+ CRLF + 'SET @dt=CONVERT(datetime,N' + QuotedStr(DateTimeToStr(dtDatum.DateTime)) + ',104)'; locSQL:= locSQL + CRLF + 'EXEC dbo.hp_InsertHlavickyOZ @Ident OUT,@Sklad=N' + QuotedStr(VyrRekl); locSQL:= locSQL + ',@DruhPohybu=0,@RadaDokladu=N' + QuotedStr(rada) +',@Insert=1,@PC=NULL,@Mena=N' + QuotedStr('CZK'); locSQL:= locSQL + ',@CisloOrg=0,@DatumPorizeni=@dt' + CRLF + 'SELECT @Ident'; idDZp:= 0; try with Helios.OpenSQL(locSQL) do if (RecordCount>0) then idDZp:= StrToInt(VarToStr(FieldValues(0))); // zjisti ID vytvoreneho dokladu except on E:Exception do begin Clipboard.AsText:= locSQL; Helios.Error(#1'Nelze uložit hlavičku Příjemky pro Demontáž (na sklad Reklamace).'#1 + CRLF + E.Message); cont:= false; end; end; if (cont) then begin UlozPolozkyMatIT(idDZp); Helios.ExecSQL('UPDATE ' + tblDZ + ' SET CisloZakazky=N' + QuotedStr(cZak) + ' WHERE id=' + IntToStr(idDZp)); Helios.ExecSQL('EXEC dbo.hp_VypCenOZPolozek_IDDokladu @IDDoklad=' + IntToStr(idDZp) + ',@AktualizaceSlev=1'); Helios.ExecSQL('UPDATE ' + tblDZ + ' SET BlokovaniEditoru=null WHERE id=' + IntToStr(idDZp)); locSQL:= 'DECLARE @BylaChyba BIT'+ CRLF + 'EXEC dbo.hp_Realizuj_Prijem ' + IntToStr(idDZp); locSQL:= locSQL + ',NULL,' + IntToStr(Helios.UserId) + ',@BylaChyba OUT' + CRLF + 'SELECT @BylaChyba'; try with Helios.OpenSQL(locSQL) do if (VarToStr(FieldValues(0))='1') then Helios.Error(#1'Nelze realizovat příjemku.'#1); except on E:Exception do begin Clipboard.AsText:= locSQL; Helios.Error(#1'Nelze realizovat příjemku.'#1 + CRLF + E.Message); end; end; end; end; } { with Helios.OpenSQL('SELECT idMat FROM #TabMaterialyIT WHERE ISNULL(MnozIT,0)>0') do if (RecordCount>0) then begin UlozPolozkyMatIT(idDZVyd); // uloz polozky na doklad pro IT Helios.ExecSQL('EXEC dbo.hp_VypCenOZPolozek_IDDokladu @IDDoklad=' + IntToStr(idDZVyd) + ', @AktualizaceSlev=0'); Helios.ExecSQL('UPDATE ' + tblDZ + ' SET BlokovaniEditoru=null WHERE id=' + IntToStr(idDZVyd)); end; Helios.ExecSQL('EXEC dbo.hp_VypCenOZPolozek_IDDokladu @IDDoklad=' + IntToStr(idDZVyd) + ', @AktualizaceSlev=0'); Helios.ExecSQL('UPDATE ' + tblDZ + ' SET BlokovaniEditoru=null WHERE id=' + IntToStr(idDZVyd)); end; } Helios.Refresh(true); Close; end; end; procedure TformDemontaz.btnRozeberClick(Sender: TObject); var lSQL: string; begin if not (TestMnozOK) then begin Helios.Error(#1'Na skladě není požadované množství kusů.'#1 + CRLF + '(množství skladem + požadováno na demontáž)'); Exit; end; if (idKZ>0) and (edtMnoz.Value>0) then begin if not(tabDilce.Active) then tabDilce.Open; if tabDilce.Locate('idKZ',VarArrayOf([idKZ]),[loCaseInsensitive]) then begin tabDilce.Edit; tabDilce.FieldByName('Mnozstvi').AsExtended:= tabDilce.FieldByName('Mnozstvi').AsExtended + edtMnoz.Value; end else begin tabDilce.Append; tabDilce.FieldByName('Mnozstvi').AsExtended:= edtMnoz.Value; tabDilce.FieldByName('idKZ').AsInteger:= idKZ; tabDilce.FieldByName('idSS').AsInteger:= idSS; tabDilce.FieldByName('SZ').AsString:= selSZ.Text; tabDilce.FieldByName('RegCis').AsString:= selRegCis.Text; tabDilce.FieldByName('Nazev').AsString:= ' ' + lblNazev.Caption; end; tabDilce.Post; { lSQL:= 'IF OBJECT_ID(N''tempdb..#TabOznaceneIDProTisk'')IS NULL CREATE TABLE #TabOznaceneIDProTisk(ID INT NOT NULL PRIMARY KEY,' + 'Poradi INT NOT NULL IDENTITY UNIQUE) ELSE TRUNCATE TABLE #TabOznaceneIDProTisk' + CRLF; lSQL:= lSQL + 'INSERT #TabOznaceneIDProTisk(ID) VALUES (' + IntToStr(idKZ) + ')' + CRLF; lSQL:= lSQL + 'IF OBJECT_ID(N''tempdb..#TabOznaceneIDProTiskG'') IS NULL CREATE TABLE #TabOznaceneIDProTiskG(ID INT NOT NULL PRIMARY KEY)'; lSQL:= lSQL + ' ELSE TRUNCATE TABLE #TabOznaceneIDProTiskG' + CRLF + 'INSERT #TabOznaceneIDProTiskG(ID) VALUES (' + IntToStr(idKZ) + ')' + CRLF; lSQL:= lSQL + 'IF OBJECT_ID(N''tempdb..#TabParNapoctuTPV'') IS NULL '; lSQL:= lSQL + 'CREATE TABLE #TabParNapoctuTPV(ID INT IDENTITY NOT NULL, DatumTPV DATETIME DEFAULT GETDATE(),' + 'Mnozstvi NUMERIC(19,6) NULL, RespekPlanZtratyPriVyrobeDilcu BIT NOT NULL DEFAULT 0,' + 'RespekDodatecneProcZtratKV BIT NOT NULL DEFAULT 0, DelitFixniMnozstviOptDavkou BIT NOT NULL DEFAULT 0,' + 'KalkCenyKDnesku BIT NOT NULL DEFAULT 0, RespekAltPriInverVypoctu BIT NOT NULL DEFAULT 0,' + 'RespekZakazModifPriInverVypoctu BIT NOT NULL DEFAULT 0, RespekNedelitMJ BIT NOT NULL DEFAULT 0,' + 'VcetneNulovychKV BIT NOT NULL DEFAULT 0, KalkulaceJednotlivychPolozek BIT NOT NULL DEFAULT 0,' + 'DatumTPV_D AS (DATEPART(DAY,DatumTPV)), DatumTPV_M AS (DATEPART(MONTH,DatumTPV)),' + 'DatumTPV_Y AS (DATEPART(YEAR,DatumTPV)), DatumTPV_Q AS (DATEPART(QUARTER,DatumTPV)),' + 'DatumTPV_W AS (DATEPART(WEEK,DatumTPV)), DatumTPV_X AS (CONVERT(DATETIME,CONVERT(INT,CONVERT(FLOAT,DatumTPV)))) )' + ' ELSE TRUNCATE TABLE #TabParNapoctuTPV' + CRLF; lSQL:= lSQL + 'INSERT INTO #TabParNapoctuTPV(Mnozstvi, KalkCenyKDnesku, RespekNedelitMJ, VcetneNulovychKV) VALUES(' + StringReplace(FloatToStr(edtMnoz.Value),',','.',[rfReplaceAll]) + ', 1, 1, 1)'; Helios.ExecSQL(lSQL); } // Helios.PrintForm(81); if (cbTiskRozp.Checked) and not (Length(idPols)>0) then Helios.PrintForm3(bidDilce,81,'TabKmenZbozi.Id=' + IntToStr(idKZ)); idKZ:= 0; idSS:= 0; selSZ.Text:= ''; selRegCis.Text:= ''; if not (Length(idPols)>0) then selRegCis.SetFocus; lblNazev.Caption:= ''; edtMnoz.Value:= 0; if (tabDilce.RecordCount>0) then begin selRegCis.Enabled:= false; edtMnoz.Enabled:= false; btnRozeber.Enabled:= false; end; end; end; procedure TformDemontaz.ZjistiKmen; begin with Helios.OpenSQL('SELECT SkupZbo,RegCis,Nazev1 FROM ' + tblKZ + ' WHERE id=' + IntToStR(idKZ)) do if (RecordCount=1) then begin if (selSZ.Text='') then selSZ.Text:= VarToStr(FieldValues(0)); if (selRegCis.Text='') then selRegCis.Text:= VarToStr(FieldValues(1)); lblNazev.Caption:= VarToStr(FieldValues(2)); end; end; procedure TformDemontaz.btnStornoClick(Sender: TObject); begin Close; end; procedure TformDemontaz.FormActivate(Sender: TObject); begin if not(tabDilce.Active) then tabDilce.Open; if not(tabMat.Active) then tabMat.Open; verText:= GetFileVersion2(GetModuleName(HInstance)); if (Length(verText)=12) then verText:= LeftStr(verText,9) + '0' + RightStr(verText,3); lblVerze.Caption:= verText; // vDilce.OptionsView.NoDataToDisplayInfoText:= ''; // vMat.OptionsView.NoDataToDisplayInfoText:= ''; sklVyd:= ''; end; procedure TformDemontaz.FormClose(Sender: TObject; var Action: TCloseAction); var locSQL: string; begin if (tabDilce.Active) then tabDilce.Close; if (tabMat.Active) then tabMat.Close; locSQL:= 'IF OBJECT_ID(N' + QuotedStr('tempdb..#TabMaterialyIT') + ') IS NOT NULL DROP TABLE #TabMaterialyIT'; Helios.ExecSQL(locSQL); Action:= caFree; end; procedure TformDemontaz.FormShow(Sender: TObject); var i: integer; begin Application.Handle:= Helios.MainApplicationHandle; Application.Icon.Handle:= Helios.MainApplicationIconHandle; selVydejIT.SetFocus; idKZ:= 0; idSS:= 0; lblNazev.Caption:= ''; dtDatum.DateTime:= Now; sklSrc:= Helios.Sklad; with Helios.OpenSQL('SELECT Nazev,CisloStr FROM ' + tblStrom + ' WHERE Cislo=N' + QuotedStr(sklSrc)) do begin selSrcSklNazev.Text:= VarToStr(FieldValues(0)); selSrcSkl.Text:= VarToStr(FieldValues(1)); end; with Helios.OpenSQL('SELECT Nazev,CisloStr,Cislo FROM ' + tblStrom + ' WHERE LOWER(Nazev) LIKE N' + QuotedStr('%výrob%demont%')) do begin selDstSklNazev.Text:= VarToStr(FieldValues(0)); selDstSkl.Text:= VarToStr(FieldValues(1)); sklDst:= VarToStr(FieldValues(2)); end; cbTiskRozp.Checked:= false; cbTiskRozp.Visible:= true; cbMaxRozpad.Checked:= true; cbTiskRozp.Visible:= true; grdDilce.Height:= 42; grdDilce.Width:= 518; grdDilce.Top:= 80; if (Length(idPols)>0) then begin for i:=0 to Length(idPols)-1 do begin with Helios.OpenSQL('SELECT id FROM ' + tblSS + ' WHERE Mnozstvi>0 AND Id=' + IntToStr(idPols[i])) do if (RecordCount=0) then Delete(idPols,i,1); end; cbTiskRozp.Visible:= false; cbMaxRozpad.Visible:= false; grdDilce.Height:= 109; grdDilce.Width:= 518; grdDilce.Top:= 16; DemontujVybrane; end; end; procedure TformDemontaz.pmAll2ITClick(Sender: TObject); var lSQL: string; i: integer; begin if (tabMat.RecordCount>0) then begin i:= tabMat.RecNo; tabMat.DisableControls; tabMat.First; while not(tabMat.Eof) do begin tabMat.Edit; tabMat.FieldByName('MnozstviIT').AsExtended:= tabMat.FieldByName('Mnozstvi').AsExtended; tabMat.Post; lSQL:= 'IF EXISTS(SELECT idMat FROM #TabMaterialyIT WHERE idMat=' + tabMat.FieldByName('idKZ').AsString + ') '; lSQL:= lSQL +'UPDATE #TabMaterialyIT SET MnozIT=' + StringReplace(tabMat.FieldByName('Mnozstvi').AsString, ',', '.', [rfReplaceAll]); lSQL:= lSQL + ' WHERE idMat=' + tabMat.FieldByName('idKZ').AsString + ' ELSE INSERT #TabMaterialyIT (idMat, MnozIT) SELECT '; lSQL:= lSQL + tabMat.FieldByName('idKZ').AsString + ', ' + StringReplace(tabMat.FieldByName('Mnozstvi').AsString, ',', '.', [rfReplaceAll]); Helios.ExecSQL(lSQL); tabMat.Next; end; tabMat.RecNo:= i; tabMat.EnableControls; end; end; procedure TformDemontaz.pmDel2ITClick(Sender: TObject); var lSQL: string; i: integer; begin i:= tabMat.RecNo; tabMat.DisableControls; tabMat.First; while not(tabMat.Eof) do begin tabMat.Edit; tabMat.FieldByName('MnozstviIT').AsExtended:= 0; tabMat.Post; tabMat.Next; end; Helios.ExecSQL('DELETE FROM #TabMaterialyIT'); tabMat.RecNo:= i; tabMat.EnableControls; end; procedure TformDemontaz.pmOpravClick(Sender: TObject); var strMn, iSS: string; mn, jc, mnSkl: Extended; iR: integer; begin tabDilce.DisableControls; if (tabDilce.RecordCount>0) then begin // iR:= vDilce.DataController.FocusedRecordIndex; mn:= tabDilce.FieldByName('Mnozstvi').AsFloat; iSS:= tabDilce.FieldByName('idSS').AsString; jc:= 0; if (FormMnozstviCena('Zadejte nové množství',mn,jc,false,false)) then begin mnSkl:= 0; with Helios.OpenSQL('SELECT Mnozstvi FROM ' + tblSS + ' WHERE Id=' + iSS) do if (RecordCount=1) then mnSkl:= StrToFloat(VarToStr(FieldValues(0))); if (mn<=mnSkl) then begin tabDilce.Edit; tabDilce.FieldByName('Mnozstvi').AsExtended:= mn; tabDilce.Post; grdDilce.Refresh; end else Helios.Error(#1'Na skladě je pouze ' + FormatFloat('##,##0.###',mnSkl) + ' kusů'#1); end; // vDilce.DataController.FocusedRecordIndex:= iR; end; tabDilce.EnableControls; end; procedure TformDemontaz.pmSmazatClick(Sender: TObject); begin { if Helios.YesNo('Opravdu smazat položku "' + vDilce.Controller.FocusedRecord.Values[3] + '" ?', false) then begin vDilce.BeginUpdate; tabDilce.Delete; if (tabDilce.RecordCount=0) then begin selRegCis.Enabled:= true; edtMnoz.Enabled:= true; btnRozeber.Enabled:= true; end; vDilce.EndUpdate; end; } end; procedure TformDemontaz.selCisZamButtonClick(Sender: TObject); begin if Helios.Prenos(bidCisZam,'Cislo',oVar1,'','Zaměstnanci',true) then begin selCisZam.Text:= VarToStr(oVar1); with Helios.OpenSQL('SELECT PrijmeniJmeno FROM ' + tblCZam + ' WHERE Cislo=' + selCisZam.Text) do lblZamest.Caption:= VarToStr(FieldValues(0)); selRegCis.SetFocus; end else begin selCisZam.Text:= ''; lblZamest.Caption:= ''; end; end; procedure TformDemontaz.selCisZamExit(Sender: TObject); var cZam: string; begin selCisZam.Text:= Trim(selCisZam.Text); if (selCisZam.Text<>'') then begin with Helios.OpenSQL('SELECT PrijmeniJmeno FROM ' + tblCZam + ' WHERE Cislo=' + selCisZam.Text) do if (RecordCount=1) then begin lblZamest.Caption:= VarToStr(FieldValues(0)); selRegCis.SetFocus; end else begin Helios.Error(#1'Toto číslo nebylo nalezena u žádného zaměstnance.'#1); lblZamest.Caption:= ''; selCisZam.Text:= ''; selCisZam.SetFocus; end; end else lblZamest.Caption:= ''; end; procedure TformDemontaz.selDstSklButtonClick(Sender: TObject); var podm: string; begin podm:= 'LEFT(TabStrom.Cislo,1)=N' + QuotedStr('2'); if Helios.Prenos(bidStrom,'Cislo',oVar1,podm,'Organizační struktura',true) then begin if not(VarIsNull(oVar1)) then begin sklDst:= VarToStr(oVar1); with Helios.OpenSQL('SELECT Nazev,CisloStr FROM ' + tblStrom + ' WHERE Cislo=N' + QuotedStr(sklDst)) do begin selDstSklNazev.Text:= VarToStr(FieldValues(0)); selDstSkl.Text:= VarToStr(FieldValues(1)); // Helios.SetSklad(sklDst); end; end; end; end; procedure TformDemontaz.selRegCisButtonClick(Sender: TObject); var podm: string; begin selSZ.Text:= ''; selRegCis.Text:= ''; podm:= 'TabStavSkladu.IDSklad=N' + QuotedStr(sklSrc) + ' AND TabStavSkladu.Mnozstvi>0 AND '; podm:= podm + 'TabStavSkladu.IdKmenZbozi IN (SELECT id FROM TabKmenZbozi WHERE Material=0 AND SkupZbo IN (SELECT sz.SkupZbo FROM ' + tblSZ + ' sz LEFT JOIN ' + tblSZe; podm:= podm + ' sze ON (sze.id=sz.id) WHERE sze._JeVyroba=1)'; if (selSZ.Text<>'') then podm:= ' AND SkupZbo=N' + QuotedStr(selSZ.Text); podm:= podm + ')'; if Helios.Prenos(bidSS,'id',oVar1,podm,'Stav skladu ' + sklSrc,true) then begin idSS:= StrToInt(VarToStr(oVar1)); with Helios.OpenSQL('SELECT idKmenZbozi FROM ' + tblSS + ' WHERE id=' + IntToStr(idSS)) do if (RecordCount=1) then idKZ:= StrToInt(VarToStr(FieldValues(0))); ZjistiKmen; edtMnoz.SetFocus; end; end; procedure TformDemontaz.selRegCisExit(Sender: TObject); var lSQL: string; begin selRegCis.Text:= Trim(selRegCis.Text); if (selRegCis.Text<>'') and (idKZ=0) then begin lSQL:= 'SELECT ss.Id, kz.Id FROM ' + tblSS + ' ss INNER JOIN ' + tblKZ + ' kz ON (kz.Id=ss.IdKmenZbozi)'; lSQL:= lSQL + ' INNER JOIN ' + tblSZ + ' sz ON (sz.SkupZbo=kz.SkupZbo) INNER JOIN ' + tblSZe + ' sze ON (sz.Id=sze.Id)'; lSQL:= lSQL + ' WHERE ss.IDSklad=N' + QuotedStr(sklSrc) + ' AND ss.Mnozstvi>0 AND kz.RegCis=N' + QuotedStr(selRegCis.Text); lSQL:= lSQL + ' AND kz.Material=0 AND sze._JeVyroba=1'; if (selSZ.Text<>'') then lSQL:= ' AND kz.SkupZbo=N' + QuotedStr(selSZ.Text); with Helios.OpenSQL(lSQL) do if (RecordCount=1) then begin idSS:= StrToInt(VarToStr(FieldValues(0))); idKZ:= StrToInt(VarToStr(FieldValues(1))); ZjistiKmen; edtMnoz.SetFocus; end else begin idSS:= 0; idKZ:= 0; Helios.Error(#1'Nelze dohledat kartu zboží (určena pro výrobu, na skladě > 0)'#1); end; end; end; procedure TformDemontaz.selSrcSklButtonClick(Sender: TObject); begin if Helios.Prenos(bidStrom,'Cislo',oVar1,'','Organizační struktura',true) then begin if not(VarIsNull(oVar1)) then begin sklSrc:= VarToStr(oVar1); with Helios.OpenSQL('SELECT Nazev,CisloStr FROM ' + tblStrom + ' WHERE Cislo=N' + QuotedStr(sklSrc)) do begin selSrcSklNazev.Text:= VarToStr(FieldValues(0)); selSrcSkl.Text:= VarToStr(FieldValues(1)); end; end; end; end; procedure TformDemontaz.selSZButtonClick(Sender: TObject); begin if Helios.Prenos(bidSZ,'SkupZbo',oVar1,'','Skupiny zboží',true) then selSZ.Text:= VarToStr(oVar1); end; procedure TformDemontaz.selSZExit(Sender: TObject); begin selSZ.Text:= Trim(selSZ.Text); if (selSZ.Text<>'') then with Helios.OpenSQL('SELECT sz.id FROM ' + tblSZ + ' sz LEFT JOIN ' + tblSZe + ' sze ON (sze.id=sz.id) WHERE sze._JeVyroba=1 AND sz.SkupZbo=N' + QuotedStr(selSZ.Text)) do if (RecordCount=0) then begin selSZ.Text:= ''; Helios.Error(#1'Nelze dohledat Skupinu zboží pro výrobu.'#1); end; end; procedure TformDemontaz.selVydejITButtonClick(Sender: TObject); var podm: string; begin selVydejIT.Text:= ''; if (selVydejIT.Text<>'') and (sklDst<>VyrRekl) then begin Helios.Error(#1'Výdej vadného materiálu z demontáže do IT lze dělat pouze na skladě Výroba REKLAMACE.'#1); Exit; end; // Helios.SetSklad(VyrRekl); podm:= 'TabDokladyZbozi.Obdobi=' + IntToStr(Helios.Obdobi) + ' AND TabDokladyZbozi.DruhPohybuZbo=2 AND TabDokladyZbozi.Realizovano=0'; podm:= podm + ' AND TabDokladyZbozi.RadaDokladu=N' + QuotedStr('420') + ' AND TabDokladyZbozi.CisloOrg=1 AND TabDokladyZbozi.MistoUrceni=1'; podm:= podm + ' AND TabDokladyZbozi.IDSklad=N' + QuotedStr(sklDst); Helios.SetSklad(sklDst); if Helios.Prenos(bidDZVydej,'id',oVar1,podm,'Výdejky do IT',true) then begin // Helios.SetSklad(sklSrc); idDZVyd:= StrToInt(VarToStr(oVar1)); with Helios.OpenSQL('SELECT RadaDokladu + N''-'' + CONVERT(nvarchar,PoradoveCislo),IdSklad FROM ' + tblDZ + ' WHERE id=' + IntToStr(idDZVyd)) do if (RecordCount=1) then begin selVydejIT.Text:= VarToStr(FieldValues(0)); sklVyd:= VarToStr(FieldValues(1)); end; end else begin idDZVyd:= 0; sklVyd:= ''; selVydejIT.Text:= ''; end; Helios.SetSklad(sklSrc); end; procedure TformDemontaz.tabDilceAfterDelete(DataSet: TDataSet); begin tabDilceAfterPost(DataSet); end; procedure TformDemontaz.tabDilceAfterPost(DataSet: TDataSet); var locSQL, strIdMat, strMnoz: string; mnoz,mnozNiz: Extended; matIdSS, matIdSSold: integer; sz,regcis,pop: string; iRowPolos,idx: integer; lp: integer; rozp: IHeQuery; begin // iRowPolos:= vDilce.DataController.FocusedRowIndex; if not(tabMat.Active) then tabMat.Open; tabMat.EmptyDataSet; if (tabDilce.RecordCount=0) then begin selRegCis.SetFocus; Exit; end; locSQL:= 'IF OBJECT_ID(N' + QuotedStr('tempdb..#TabMaterialyIT') + ') IS NULL CREATE TABLE #TabMaterialyIT (idMat INT NOT NULL,'; locSQL:= locSQL + 'MnozIT NUMERIC(19,6) DEFAULT NULL)' + CRLF + 'DELETE FROM #TabMaterialyIT'; Helios.ExecSQL(locSQL); locSQL:= 'IF OBJECT_ID(N' + QuotedStr('tempdb..#TabRozpad') + ') IS NOT NULL DROP TABLE #TabRozpad' + CRLF; locSQL:= locSQL + 'CREATE TABLE #TabRozpad(idMat INT NOT NULL, Mnoz NUMERIC(19,6) DEFAULT 0.0)'; Helios.ExecSQL(locSQL); Helios.ExecSQL('DELETE FROM dbo._TabKus_Rozpad WHERE Autor=SUSER_SNAME()'); for idx:=1 to tabDilce.RecordCount do begin tabDilce.RecNo:= idx; locSQL:= 'DECLARE @dt DATETIME' + CRLF + 'SET @dt=CONVERT(datetime,N' + QuotedStr(DateTimeToStr(dtDatum.DateTime)); locSQL:= locSQL + ',104)' + CRLF + 'IF OBJECT_ID(N''tempdb..#TabSouhKusov'') IS NOT NULL DROP TABLE #TabSouhKusov' + CRLF; locSQL:= locSQL + 'CREATE TABLE #TabSouhKusov (IdKZ INT, mnozstvi NUMERIC(19,6) DEFAULT 0.0)' + CRLF; locSQL:= locSQL + 'EXEC dbo.ep_GetKusovnikProOZ @ID=' + tabDilce.FieldByName('idKZ').AsString + ', @mnozstvi='; locSQL:= locSQL + StringReplace(tabDilce.FieldByName('Mnozstvi').AsString,',','.',[rfReplaceAll]); // locSQL:= locSQL + StringReplace(DataSet.FieldByName('Mnozstvi').AsString,',','.',[rfReplaceAll]); locSQL:= locSQL + ',@datum=@dt,@JenPrvniUroven='; if (cbMaxRozpad.Checked=true) then locSQL:= locSQL + '0' else locSQL:= locSQL + '1'; locSQL:= locSQL + CRLF + 'INSERT #TabRozpad SELECT IdKZ,Mnozstvi FROM #TabSouhKusov'; Helios.ExecSQL(locSQL); { locSQL:= 'INSERT dbo._TabKus_Rozpad(IdKmenZbozi,Mnozstvi,IdKmenZboziV,MnozstviV) VALUES (' + strIdMat + ','; locSQL:= locSQL + StringReplace(strMnoz,',','.',[rfReplaceAll]) + ','; locSQL:= locSQL + tabDilce.FieldByName('idKZ').AsString + ','; locSQL:= locSQL + StringReplace(tabDilce.FieldByName('Mnozstvi').AsString,',','.',[rfReplaceAll]) + ')'; Helios.ExecSQL(locSQL); } end; rozp:= Helios.OpenSQL('SELECT DISTINCT(IdMat),ISNULL(SUM(Mnoz),0) FROM #TabRozpad GROUP BY IdMat'); if (rozp.RecordCount>0) then begin GroupBox2.Caption:= ' Materiály (' + IntToStr(rozp.RecordCount) + ') '; tabMat.DisableControls; rozp.First; while not(rozp.EOF) do begin strIdMat:= VarToStr(rozp.FieldValues(0)); strMnoz:= StringReplace(VarToStr(rozp.FieldValues(1)),',','.',[rfReplaceAll]); // strMnoz:= FloatToStr(StrToFloat(strMnoz) * edtMnoz.Value); tabMat.Append; locSQL:= 'SELECT SkupZbo,RegCis,Nazev1 FROM ' + tblKZ + ' WHERE id=' + strIdMat; with Helios.OpenSQL(locSQL) do if (RecordCount=1) then begin tabMat.FieldByName('SZ').AsString:= VarToStr(FieldValues(0)); tabMat.FieldByName('RegCis').AsString:= VarToStr(FieldValues(1)); tabMat.FieldByName('Nazev').AsString:= ' ' + VarToStr(FieldValues(2)); end; matIdSS:= 0; matIdSSold:= 0; with Helios.OpenSQL('SELECT id FROM ' + tblSS + ' WHERE IdKmenZbozi=' + strIdMat + ' AND IDSklad=N' + QuotedStr(sklDst)) do if (RecordCount=0) then begin locSQL:= 'INSERT ' + tblSS + ' (IdKmenZbozi,IDSklad) VALUES (' + strIdMat + ',N' + QuotedStr(sklDst) + ')'; locSQL:= locSQL + CRLF + 'SELECT SCOPE_IDENTITY()'; with Helios.OpenSQL(locSQL) do matIdSS:= StrToInt(VarToStr(FieldValues(0))); end else matIdSS:= StrToInt(VarToStr(FieldValues(0))); if (matIdSS>0) then begin locSQL:= 'SELECT id FROM ' + tblSS + ' WHERE IDSklad=N' + QuotedStr(sklSrc) + ' AND IdKmenZbozi=' + strIdMat; with Helios.OpenSQL(locSQL) do if (RecordCount>0) then matIdSSold:= StrToInt(VarToStr(FieldValues(0))); end; tabMat.FieldByName('idKZ').AsString:= strIdMat; tabMat.FieldByName('Mnozstvi').AsExtended:= StrToFloat(StringReplace(strMnoz,'.',',',[rfReplaceAll])); tabMat.FieldByName('idSSold').AsInteger:= matIdSSold; tabMat.FieldByName('idSS').AsInteger:= matIdSS; tabMat.FieldByName('MnozstviIT').AsString:= ''; locSQL:= 'SELECT MnozIT FROM #TabMaterialyIT WHERE idMat=' + strIdMat; with Helios.OpenSQL(locSQL) do if (RecordCount=1) then begin if not(VarIsNull(FieldValues(0))) then tabMat.FieldByName('MnozstviIT').AsFloat:= StrToFloat(VarToStr(FieldValues(0))); end else begin locSQL:= 'INSERT #TabMaterialyIT (idMat) VALUES (' + strIdMat + ')'; Helios.ExecSQL(locSQL); end; tabMat.Post; rozp.Next; end; locSQL:= 'SELECT idMat FROM #TabMaterialyIT WHERE MnozIT>0'; with Helios.OpenSQL(locSQL) do if (RecordCount>0) then begin First; while not(EOF) do begin if not(tabMat.Locate('idKZ',VarArrayOf([VarToStr(FieldValues(0))]),[loCaseInsensitive])) then begin locSQL:= 'DELETE FROM #TabMaterialyIT WHERE idMat=' + VarToStr(FieldValues(0)); Helios.ExecSQL(locSQL); end; Next; end; end; tabMat.EnableControls; tabMat.RecNo:= 1; tabMat.Refresh; end; locSQL:= 'IF OBJECT_ID(N''tempdb..#TabRozpad'') IS NOT NULL DROP TABLE #TabRozpad' + CRLF; locSQL:= locSQL + 'IF OBJECT_ID(N''tempdb..#TabSouhKusov'') IS NOT NULL DROP TABLE #TabSouhKusov'; Helios.ExecSQL(locSQL); if not TestMatDoIT then Helios.Error(#1'V kusech pro vývoz je větší množství než v demontáži.'#1); // vDilce.DataController.FocusedRowIndex:= iRowPolos; tabMat.RecNo:= 1; locSQL:= 'IF OBJECT_ID(N''tempdb..#TabRozpad'') IS NOT NULL DROP TABLE #TabRozpad'; Helios.ExecSQL(locSQL); end; end.