unit ComObjekt; INTERFACE uses System.Win.ComObj, System.Classes, System.StrUtils, System.SysUtils, ddPlugin_TLB; type TplgKdynium = class(TComObject, IHePlugin) private // function DelphiCompilerVersion: Single; safecall; // function PartnerIdentification: WideString; safecall; procedure Run (const Helios: IHelios); safecall; public procedure ExportProMatrix (const Helios: IHelios); safecall; procedure ImportKmenProMatrix (const Helios: IHelios; sklMatrix2: string); safecall; procedure ImportKmenFormy (const Helios: IHelios); safecall; procedure ImportKmenFormy2 (const Helios: IHelios); safecall; procedure ImportKmenFormyVC (const Helios: IHelios); safecall; procedure ImportKmenMeridla (const Helios: IHelios); safecall; procedure ImportKmenMeridlaVC (const Helios: IHelios); safecall; procedure ImportOperaciDoPripravyPrikazu (const Helios: IHelios); safecall; procedure VyrPrikazyDoVyssiho (const Helios: IHelios; ids:TArray); safecall; procedure ImportVCKmen2VyrPrikaz (const Helios: IHelios; idVPr: Integer); safecall; procedure ZakazkaZobrazTavby (const Helios: IHelios; ids:TArray); safecall; procedure ZakazkaZobrazAtestyTaveb (const Helios: IHelios; ids:TArray); safecall; procedure ImportProcentaZtrat (const Helios: IHelios); safecall; procedure VytvorTempExtKomPar (const Helios: IHelios; id: integer); safecall; procedure RozpRezii_PseudoAutomat (const Helios: IHelios; const arrID: TArray); safecall; procedure ImportObedy (const Helios: IHelios); end; TKmen = record sz, regCis, nazev1, nazev2, nazev3, skp, sortiment, mj, mena, menaRework, v_min, f_ot, f_z, barcode, technologie: string; vyrCis, popisVC, umisteniVC, skutPopis, cOrgReworkS, cOrgReworkSuff: string; kateg, typ, dodLhuta, lhutaNaskl, cOrgDod, cOrgVyr, cOrgRework, balMnoz, minDod, minOdb, minMnoz, maxMnoz: Integer; hmot, jcena, jcenaRework: Extended; obj: boolean; datKalibrace: TDatetime; end; TObed = record cisZam: integer; pocetObedu: integer; end; TForma = record sz, regCis, nazev1, nazev2, vc, popisVc, skutPopVc, cilSklad, vykres: string; jcena: extended; end; TMatrixSupp = record cisOrg: string; suffix: string; kplCisOrg: string; suppName: string; ship: string; mena: string; // koruna/euro end; const Class_Kdynium: TGUID = '{C45BDCA2-0AAF-4B64-A649-8BAA37C3940F}'; sklMatrix = '00100200200200'; sklMeridla = ''; tblPlanLis = '[dbo].[_hdc_TabPlanNasazeniLisu]'; tblVazbyZak = '[dbo].[_hdc_TabVazbyZakazek]'; CRLF = #13#10; clRed = $0000FF; IMPLEMENTATION uses System.Variants, Vcl.Controls, System.Generics.Collections, System.Generics.Defaults, System.Win.ComServ, Vcl.Forms, Winapi.ShlObj, Vcl.Dialogs, Winapi.Windows, Vcl.StdCtrls, System.DateUtils, Vcl.Clipbrd, nExcel, xlsxwrite, helUtils, frmGenPolos, frmOdlitek, frmGenTPV, frmOdvTavby, frmOdvLis, frmDavkyObecne, frmEvidOper, {$IFDEF MadExcept} madExcept, {$ENDIF} frmHrOdvOper, frmSchvaleniZak, frmOOPPohyb; var oVar1, oVar2: OleVariant; LocalFormatSettings: TFormatSettings; jeTest: boolean; cestaExport, verText, skinName: string; skinNum: byte; { function TplgKdynium.DelphiCompilerVersion: Single; begin Result:= System.CompilerVersion; end; function TplgKdynium.PartnerIdentification: WideString; begin Result:= 'HEIQ0100-TEST0'; //místo XXXXX doplnit vaše sériové èíslo urèené pro vývoj, pøidìlené firmou Asseco Solutions end; } function VyberAdresar(var Foldr: string; Title: string): Boolean; var BrowseInfo: TBrowseInfo; ItemIDList: PItemIDList; DisplayName: array[0..MAX_PATH] of Char; begin Result := False; FillChar(BrowseInfo, SizeOf(BrowseInfo), #0); with BrowseInfo do begin hwndOwner := Application.Handle; pszDisplayName := @DisplayName[0]; lpszTitle := PChar(Title); ulFlags := BIF_RETURNONLYFSDIRS; end; ItemIDList := SHBrowseForFolder(BrowseInfo); if Assigned(ItemIDList) then if SHGetPathFromIDList(ItemIDList, DisplayName) then begin Foldr := DisplayName; Result := True; end; end; function OtevriSoubor(flt1,flt2: string; var nazev: string): Boolean; var dlgOpenW7: TFileOpenDialog; // dialog pro Windows Vista a novejsi titulek, filtr1, filtr2: string; iniDir: string; begin result:= false; titulek:= 'Vyberte soubor pro import'; filtr1:= IfThen(flt1<>'',flt1,'XML soubory'); filtr2:= IfThen(flt2<>'',flt2,'*.xml'); nazev:= ''; iniDir:= GetEnvironmentVariable('USERPROFILE') + '\Desktop'; try dlgOpenW7:= TFileOpenDialog.Create(nil); dlgOpenW7.Title:= titulek; dlgOpenW7.OkButtonLabel:= 'Vybrat'; with dlgOpenW7.FileTypes.Add do begin DisplayName:= filtr1; FileMask:= filtr2; end; dlgOpenW7.DefaultFolder:= iniDir; if dlgOpenW7.Execute then begin nazev:= dlgOpenW7.FileName; result:= true; end; finally dlgOpenW7.Free; end; end; function ClearForma(f: TForma): boolean; begin result:= true; try f.sz:= ''; f.regCis:= ''; f.nazev1:= ''; f.nazev2:= ''; f.vc:= ''; f.popisVc:= ''; f.skutPopVc:= ''; f.cilSklad:= ''; f.vykres:= ''; f.jcena:= 0; except result:= false; end; end; function ClearKmen (k: TKmen): boolean; begin result:= true; try k.sz:= ''; k.regCis:= ''; k.nazev1:= ''; k.nazev2:= ''; k.nazev3:= ''; k.technologie:= ''; k.hmot:= 0; k.skp:= ''; k.sortiment:= ''; k.mj:= ''; k.mena:= ''; k.v_min:= ''; k.f_ot:= ''; k.f_z:= ''; k.barcode:= ''; k.kateg:= 0; k.typ:= 0; k.obj:= true; k.cOrgDod:= 0; k.cOrgVyr:= 0; k.cOrgRework:= 0; k.cOrgReworkSuff:= ''; k.cOrgReworkS:= ''; k.menaRework:= ''; k.jcenaRework:= 0; k.balMnoz:= 0; k.minDod:= 0; k.minOdb:= 0; k.dodLhuta:= 0; k.lhutaNaskl:= 0; k.minMnoz:= 0; k.maxMnoz:= 0; k.jcena:= 0; k.f_ot:= ''; k.f_z:= ''; k.popisVC:= ''; k.umisteniVC:= ''; except result:= false; end; end; procedure TplgKdynium.ImportVCKmen2VyrPrikaz (const Helios: IHelios; idVPr: Integer); var stav, idKZ, idVCKm, idVCPr: integer; lSQL, vc, pop, skutPop: string; begin if HeliosExistsTest(Helios, tblPrikaz, 'ID=' + idVPr.ToString) then begin stav:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT StavPrikazu FROM ' + tblPrikaz + ' WHERE ID=' + idVPr.ToString); if (stav=30) then begin idKZ:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT IDTabKmen FROM ' + tblPrikaz + ' WHERE ID=' + idVPr.ToString); if Helios.Prenos(bidVyrCisKm, 'TabVyrCK.ID', oVar1, 'TabVyrCK.IDKmenZbozi=' + idKZ.ToString, 'Vyberte kartu Výrobního čísla', true) then begin vc:= ''; pop:= ''; skutPop:= ''; idVCKm:= StrToInt(VarToStr(oVar1)); lSQL:= 'SELECT k.Nazev1, k.Nazev2, ISNULL(e._SkutecnyPopis,N'''') AS Pop FROM ' + tblVyrCK + ' k LEFT JOIN ' + tblVyrCKE + ' e ON (k.ID=e.ID) WHERE k.ID=' + idVCKm.ToString; with Helios.OpenSQL(lSQL) do if (RecordCount=1) then begin vc:= VarToStr(FieldValues(0)); pop:= VarToStr(FieldValues(1)); skutPop:= VarToStr(FieldValues(2)); end; if not(HeliosExistsTest(Helios, tblPrikazVC, 'IDPrikaz=' + idVPr.ToString + ' AND VyrCislo=N' + QuotedStr(vc))) then begin lSQL:= 'INSERT ' + tblPrikazVC + ' (IDPrikaz, VyrCislo, Popis) SELECT ' + idVPr.ToString + ', N' +QuotedStr(vc) + ', N' + QuotedStr(pop); lSQL:= lSQL + CRLF + ' SELECT SCOPE_IDENTITY()'; try with Helios.OpenSQL(lSQL) do begin idVCPr:= StrToInt(VarToStr(FieldValues(0))); if (skutPop<>'') then try HeliosZapisExtInfo(Helios, tblPrikazVCE, '_SkutecnyPopis=N' + skutPop.QuotedString, idVCPr); { lSQL:= 'IF NOT EXISTS(SELECT ID FROM ' + tblPrikazVCE + ' WHERE ID=' + idVCPr.ToString + ') INSERT ' + tblPrikazVCE + ' (ID) SELECT ' + idVCPr.ToString; Helios.ExecSQL(lSQL); Helios.ExecSQL('UPDATE ' + tblPrikazVCE + ' SET _SkutecnyPopis=N' + skutPop.QuotedString + ' WHERE ID=' + idVCPr.ToString); } except on E:Exception do Helios.Error(#1'Chyba při kopírování ext.info VČ: '#1 + e.Message); end; end; except on E:Exception do Helios.Error(#1'Chyba při kopírování VČ: '#1 + e.Message); end; end; end; end else Helios.Error(#1'Výrobní příkaz není ve stavu Zadáno.'#1); end; end; procedure TplgKdynium.VyrPrikazyDoVyssiho(const Helios: IHelios; ids: TArray); var lSQL, podm: string; idVPr: integer; begin if (Length(ids)>0) then // 19002112 begin lSQL:= helUtils.getHeliosStrVal(Helios, '', 'SELECT k.CisloZbozi FROM ' + tblPrikaz + ' p INNER JOIN ' + tblKZ + ' k ON (k.ID=p.IDTabKmen) WHERE p.ID=' + ids[0].ToString); podm:= 'TabPrikaz.StavPrikazu IN (20,30) AND TabPrikaz.IDZakazka=(SELECT IDZakazka FROM ' + tblPrikaz + ' WHERE ID=' + ids[0].ToString + ')'; podm:= podm + ' AND TabPrikaz.IDTabKmen IN (SELECT ID FROM ' + tblKZ + ' WHERE Dilec=1 AND Blokovano=0)'; // AND SkupZbo IN (SELECT * FROM'; // podm:= podm + ' dbo.ef_GetSkupZboZVyssichVazeb(N' + QuotedStr(lSQL) + ')))'; if Helios.Prenos(bidVyrPrik, 'TabPrikaz.ID', oVar1, podm, 'Vyberte vyšší příkaz (ver.' + verText + ')', true) then begin idVPr:= StrToInt(VarToStr(oVar1)); lSQL:= 'IF OBJECT_ID(N''dbo._temp_TabPrikazyDoVyssiho'') IS NOT NULL DELETE FROM dbo._temp_TabPrikazyDoVyssiho WHERE Autor=SUSER_SNAME()' + CRLF; lSQL:= lSQL + 'IF OBJECT_ID(N''dbo._temp_TabPrikazyDoVyssiho'') IS NOT NULL IF NOT EXISTS(SELECT * FROM dbo._temp_TabPrikazyDoVyssiho)'; lSQL:= lSQL + ' DROP TABLE dbo._temp_TabPrikazyDoVyssiho' + CRLF; lSQL:= lSQL + 'IF OBJECT_ID(N''tempdb..#TabPrikazyDoVyssiho'') IS NOT NULL DROP TABLE #TabPrikazyDoVyssiho'; Helios.ExecSQL(lSQL); lSQL:= 'CREATE TABLE #TabPrikazyDoVyssiho (IDVyssi INT NOT NULL, IDNizsi INT NOT NULL, Autor NVARCHAR(80) DEFAULT SUSER_SNAME())'; if (jeTest) then lSQL:= StringReplace(lSQL, '#TabPri', 'dbo._temp_TabPri', [rfReplaceAll]); Helios.ExecSQL(lSQL); lSQL:= 'INSERT #TabPrikazyDoVyssiho (IDVyssi, IDNizsi) SELECT ' + idVPr.ToString + ', ID FROM ' + tblPrikaz + ' WHERE ID IN (' + helUtils.ArrayToString(ids, ',') + ')'; if (jeTest) then lSQL:= StringReplace(lSQL, '#TabPri', 'dbo._temp_TabPri', [rfReplaceAll]); Helios.ExecSQL(lSQL); if not(jeTest) then Helios.ExecSQL('IF OBJECT_ID(N''dbo.ep_Vyroba_PrikazNizsiDoVyssiho'') IS NOT NULL EXEC dbo.ep_Vyroba_PrikazNizsiDoVyssiho'); end; end; end; procedure TplgKdynium.ImportProcentaZtrat(const Helios: IHelios); var lSQL, radky, zaznam, msg, errMsg, sTemp: String; cnt, sl, idxR, idxS, idSS, idVC, cntIns, cntAkt, overRadek, posunSl: integer; arrRadky: TArray>; cont: Boolean; proc1, proc2: Double; begin overRadek:= 0; posunSl:= 0; if (Clipboard.HasFormat(CF_TEXT)) then begin errMsg:= ''; cont:= true; radky:= ''; try radky:= Clipboard.AsText; sl:= PosCount(#9, LeftStr(radky, Pos(#13, radky)-1)) + 1; // pocet sloupcu except on E:Exception do cont:= false; end; if (radky<>'') and (cont) then begin cont:= true; cntIns:= 0; cntAkt:= 0; cnt:= Length(radky)-Length(StringReplace(radky, Chr(13),'',[rfReplaceAll]))+1; SetLength(arrRadky, cnt, 5); // index od 0, napevno 5 sloupcu, kdyby nepsali nadpis pro SZn a procentaN if (Pos(#13, radky)>0) then zaznam:= LeftStr(radky, Pos(#13, radky)-1) // nazvy sloupcu else zaznam:= radky; if Helios.YesNo('Má importovaná oblast hlavičku ?', false) then begin Delete(radky,1,Pos(#13,radky)+1); // smaze hlavicku SetLength(arrRadky,Length(arrRadky)-1); end; for idxR:=0 to High(arrRadky)-1 do begin if (Pos(#13,radky)>0) then zaznam:= LeftStr(radky,Pos(#13,radky)-1) else zaznam:= radky; zaznam:= Trim(StringReplace(zaznam, #9#9, #9' '#9,[rfReplaceAll])); if (zaznam<>'') then for idxS:=0 to sl-1 do begin if (zaznam<>'') then begin if (Pos(#9, zaznam)>0) then begin arrRadky[idxR,idxS]:= LeftStr(zaznam, Pos(#9, zaznam)-1); Delete(zaznam,1,Pos(#9, zaznam)); end else begin arrRadky[idxR,idxS]:= zaznam; zaznam:= ''; end; end; end; Delete(radky, 1, Pos(#13, radky)+1); end; lSQL:= 'DROP TABLE IF EXISTS #TabImportProcentaZtrat' + CRLF + 'CREATE TABLE #TabImportProcentaZtrat (ID INT IDENTITY(1,1) NOT NULL, SkupZbo NVARCHAR(3) NOT NULL'; lSQL:= lSQL + ', RegCis NVARCHAR(30) NOT NULL, Procento NUMERIC(12,2) DEFAULT 0.0 NOT NULL, SkupZboNizsi NVARCHAR(3), ProcentoNizsi NUMERIC(12,2), Autor NVARCHAR(80) DEFAULT SUSER_SNAME())'; if (jeTest) then lSQL:= lSQL.Replace('#TabImp', 'dbo._TabImp'); Helios.ExecSQL(lSQL); waitStart(nil, 'Import procenta ztrát dílců', Length(arrRadky), clRed); for idxR:=0 to High(arrRadky)-1 do begin arrRadky[idxR, 0]:= helUtils.sqlSanitize(arrRadky[idxR, 0].Trim); // sz arrRadky[idxR, 0]:= arrRadky[idxR, 0].Replace(' ', ''); arrRadky[idxR, 1]:= helUtils.sqlSanitize(arrRadky[idxR, 1].Trim); // rc arrRadky[idxR, 1]:= arrRadky[idxR, 1].Replace(' ', ''); arrRadky[idxR, 2]:= helUtils.sqlSanitize(arrRadky[idxR, 2].Trim); // procento arrRadky[idxR, 2]:= arrRadky[idxR, 2].Replace(' ', ''); arrRadky[idxR, 3]:= helUtils.sqlSanitize(arrRadky[idxR, 3].Trim); // podrizena sz arrRadky[idxR, 3]:= arrRadky[idxR, 3].Replace(' ', ''); arrRadky[idxR, 4]:= helUtils.sqlSanitize(arrRadky[idxR, 4].Trim); // procento arrRadky[idxR, 4]:= arrRadky[idxR, 4].Replace(' ', ''); if (idxR mod 10=0) then waitSetProgBar(idxR); if (arrRadky[idxR, 0]<>'') and (arrRadky[idxR, 1]<>'') then begin try if (arrRadky[idxR, 2]='') then proc1:= 0 else if not(TryStrToFloat(arrRadky[idxR, 2].Replace(' ', '').Replace('.', ','), proc1)) then proc1:= 0; if (arrRadky[idxR, 4]='') then proc2:= 0 else if not(TryStrToFloat(arrRadky[idxR, 4].Replace(' ', '').Replace('.', ','), proc2)) then proc2:= 0; lSQL:= 'INSERT #TabImportProcentaZtrat (SkupZbo, RegCis, Procento, SkupZboNizsi, ProcentoNizsi) SELECT N' + arrRadky[idxR, 0].QuotedString; lSQL:= lSQL + ', N' + arrRadky[idxR, 1].QuotedString + ', ' + proc1.ToString.Replace(',', '.') + ', N' + arrRadky[idxR, 3].QuotedString + ', ' + proc2.ToString.Replace(',', '.'); if (jeTest) then lSQL:= lSQL.Replace('#TabImp', 'dbo._TabImp'); Helios.ExecSQL(lSQL); except on E:Exception do errMsg:= E.Message + CRLF + lSQL; end; end; end; waitEnd; if not(jeTest) then Helios.ExecSQL('IF OBJECT_ID(N''dbo.ep_HDC_TPV_ImportProcentaZtrat'', N''P'') IS NOT NULL EXEC dbo.ep_HDC_TPV_ImportProcentaZtrat'); // Helios.ExecSQL('DROP TABLE IF EXISTS dbo._TabImportProcentaZtrat'); Helios.ExecSQL('DROP TABLE IF EXISTS #TabImportProcentaZtrat'); Helios.Refresh(false); // Helios.Info(#1'Nových karet forem: ' + cntIns.ToString + CRLF + 'Upravených karet forem: ' + cntAkt.ToString + #1); end; end; end; procedure TplgKdynium.ImportKmenFormy2 (const Helios: IHelios); const arrFP: TArray = ['F00', 'F01', 'F10', 'F11', 'F20', 'F21']; var lSQL, radky, zaznam, msg, errMsg, sTemp: String; cnt, sl, idxR, idxS, idKZ, idSS, idVC, cntIns, cntAkt, overRadek, posunSl: integer; arrRadky: TArray>; forma: TForma; cont: Boolean; begin overRadek:= 0; posunSl:= 0; if (Clipboard.HasFormat(CF_TEXT)) then begin errMsg:= ''; cont:= true; radky:= ''; try radky:= Clipboard.AsText; sl:= PosCount(#9, LeftStr(radky, Pos(#13, radky)-1)) + 1; // pocet sloupcu except on E:Exception do cont:= false; end; if (radky<>'') and (cont) then begin cont:= true; cntIns:= 0; cntAkt:= 0; cnt:= Length(radky)-Length(StringReplace(radky, Chr(13),'',[rfReplaceAll]))+1; SetLength(arrRadky, cnt, sl); // index od 0 if (Pos(#13, radky)>0) then zaznam:= LeftStr(radky, Pos(#13, radky)-1) // nazvy sloupcu else zaznam:= radky; if Helios.YesNo('Má importovaná oblast hlavičku ?', false) then begin Delete(radky,1,Pos(#13,radky)+1); // smaze hlavicku SetLength(arrRadky,Length(arrRadky)-1); end; for idxR:=0 to High(arrRadky)-1 do begin if (Pos(#13,radky)>0) then zaznam:= LeftStr(radky,Pos(#13,radky)-1) else zaznam:= radky; zaznam:= Trim(StringReplace(zaznam, #9#9, #9' '#9,[rfReplaceAll])); if (zaznam<>'') then for idxS:=0 to sl-1 do begin if (zaznam<>'') then begin if (Pos(#9, zaznam)>0) then begin arrRadky[idxR,idxS]:= LeftStr(zaznam, Pos(#9, zaznam)-1); Delete(zaznam,1,Pos(#9, zaznam)); end else begin arrRadky[idxR,idxS]:= zaznam; zaznam:= ''; end; end; end; Delete(radky,1,Pos(#13, radky)+1); end; waitStart(nil, 'Import dat karet forem', Length(arrRadky), clRed); for idxR:=0 to High(arrRadky)-1 do begin if (idxR mod 10=0) then waitSetProgBar(idxR); try idKZ:= 0; ClearForma(forma); forma.sz:= Trim(arrRadky[idxR, 0]); posunSl:= 0; if (MatchStr(UpperCase(forma.sz), arrFP)) then posunSl:= 1 else forma.sz:= 'F00'; { if Trim(arrRadky[idxR, 0])<>Trim(arrRadky[idxR, 1]) then kmen.sz:= Trim(arrRadky[idxR, 0]); if (Length(kmen.sz)=2) then kmen.sz:= '0' + kmen.sz; } forma.regCis:= Trim(arrRadky[idxR, 0 + posunSl]); if (forma.regCis='') then continue; forma.nazev1:= Trim(arrRadky[idxR, 1 + posunSl]); forma.vc:= Trim(arrRadky[idxR, 2 + posunSl]); forma.popisVc:= Trim(arrRadky[idxR, 3 + posunSl]); forma.skutPopVc:= Trim(arrRadky[idxR, 4 + posunSl]); forma.vykres:= Trim(arrRadky[idxR, 5 + posunSl]); forma.cilSklad:= Trim(arrRadky[idxR, 7 + posunSl]); forma.jcena:= 0; with Helios.OpenSQL('SELECT ID FROM ' + tblKZ + ' WHERE SkupZbo=N' + QuotedStr(forma.sz) + ' AND RegCis=N' + QuotedStr(forma.regCis)) do if (RecordCount=1) then idKZ:= StrToInt(VarToStr(FieldValues(0))); if (idKZ=0) then begin lSQL:= 'DECLARE @i INT=0' + CRLF + 'EXEC @i=dbo.hp_VytvorPolozkuKmeneZbozi @Dilec=1, @Naradi=1, @SZ=N' + forma.sz.QuotedString; lSQL:= lSQL + IfThen(forma.regCis<>'', ', @RegCis=N' + forma.regCis.QuotedString, ''); lSQL:= lSQL + IfThen(forma.nazev1<>'', ', @Nazev1=N' + forma.nazev1.QuotedString, ''); lSQL:= lSQL + IfThen(forma.vykres<>'', ', @Vykres=N' + forma.vykres.QuotedString, ''); lSQL:= lSQL + CRLF + 'SELECT @i'; // lSQL:= 'INSERT ' + tblKZ + ' (DruhSkladu, SkupZbo, RegCis, Nazev1, Nazev2) SELECT 1, N' + QuotedStr(forma.sz) + ', N' + QuotedStr(forma.regCis); // lSQL:= lSQL + ', N' + QuotedStr(forma.nazev1) + ', N' + QuotedStr(forma.nazev2) + CRLF; // lSQL:= lSQL + 'SELECT SCOPE_IDENTITY()'; try with Helios.OpenSQL(lSQL) do idKZ:= StrToInt(VarToStr(FieldValues(0))); Inc(cntIns); except on E:Exception do begin Helios.Error(#1'Chyba INS: ' + e.Message +#1); idKZ:= 0; end; end; end; if (idKZ>0) then begin if (forma.cilSklad<>'') and (helUtils.HeliosExistsTest(Helios, tblStrom, 'Cislo=N' + forma.cilSklad.QuotedString)) then begin idSS:= 0; lSQL:= 'DECLARE @idSS INT' + CRLF + 'SET @idSS=(SELECT ID FROM ' + tblSS + ' WHERE IDSklad=N' + forma.cilSklad.QuotedString + ' AND IDKmenZbozi=' + idKZ.ToString + ')'; lSQL:= lSQL + CRLF + 'IF (@idSS IS NULL)' + CRLF + ' EXEC dbo.hp_InsertStavSkladu @IDKmen=' + idKZ.ToString + ', @IDSklad=N'; lSQL:= lSQL + forma.cilSklad.QuotedString + ', @IDZboSklad=@idSS OUT' + CRLF + 'SELECT @idSS'; try with Helios.OpenSQL(lSQL) do idSS:= StrToInt(VarToStr(FieldValues(0))); except idSS:= 0; end; if (idSS>0) and (forma.vc<>'') then begin idVC:= 0; lSQL:= 'DECLARE @idVC INT, @idVCK INT' + CRLF + 'SET @idVC=(SELECT ID FROM ' + tblVyrCS + ' WHERE IDStavSkladu=' + idSS.ToString + ' AND Nazev1=N'; lSQL:= lSQL + forma.vc.QuotedString + ')' + CRLF; lSQL:= lSQL + 'IF (@idVC IS NULL)' + CRLF + ' EXEC dbo.hp_OZInsertVyrCS @IDVyrCS=@idVC OUT, @IDVyrCK=@idVCK OUT, @IDZboSklad=' + idSS.ToString; lSQL:= lSQL + ', @Nazev1=N' + forma.vc.QuotedString + CRLF + 'SELECT @idVC'; try with Helios.OpenSQL(lSQL) do idVC:= StrToInt(VarToStr(FieldValues(0))); except idVC:= 0; end; if (idVC>0) and (forma.skutPopVc<>'') then begin lSQL:= 'UPDATE ' + tblVyrCSE + ' SET _SkutecnyPopis=N' + forma.skutPopVc.QuotedString + ' WHERE ID=' + idVC.ToString + CRLF + 'IF (@@ROWCOUNT=0)' + CRLF; lSQL:= lSQL + ' INSERT ' + tblVyrCSE + ' (ID, _SkutecnyPopis) SELECT ' + idVC.ToString + ', N' + forma.skutPopVc.QuotedString; Helios.ExecSQL(lSQL); end; end; end; end; except on E:Exception do errMsg:= E.Message + CRLF + lSQL; end; end; waitEnd; Helios.Refresh(true); Helios.Info(#1'Nových karet forem: ' + cntIns.ToString + CRLF + 'Upravených karet forem: ' + cntAkt.ToString + #1); end; end; end; procedure TplgKdynium.ImportKmenFormy (const Helios: IHelios); const arrFP: TArray = ['F00', 'F01', 'F10', 'F11', 'F20', 'F21']; var lSQL, radky, zaznam, msg, errMsg, sTemp: String; cnt, sl, idxR, idxS, idKZ, idSS, idVC, cntIns, cntAkt, overRadek, posunSl: integer; arrRadky: TArray>; forma: TForma; cont: Boolean; begin overRadek:= 0; posunSl:= 0; if (Clipboard.HasFormat(CF_TEXT)) then begin errMsg:= ''; cont:= true; radky:= ''; try radky:= Clipboard.AsText; sl:= PosCount(#9, LeftStr(radky, Pos(#13, radky)-1)) + 1; // pocet sloupcu except on E:Exception do cont:= false; end; if (radky<>'') and (cont) then begin cont:= true; cntIns:= 0; cntAkt:= 0; cnt:= Length(radky)-Length(StringReplace(radky, Chr(13),'',[rfReplaceAll]))+1; SetLength(arrRadky, cnt, sl); // index od 0 if (Pos(#13, radky)>0) then zaznam:= LeftStr(radky, Pos(#13, radky)-1) // nazvy sloupcu else zaznam:= radky; if Helios.YesNo('Má importovaná oblast hlavičku ?', false) then begin Delete(radky,1,Pos(#13,radky)+1); // smaze hlavicku SetLength(arrRadky,Length(arrRadky)-1); end; for idxR:=0 to High(arrRadky)-1 do begin if (Pos(#13,radky)>0) then zaznam:= LeftStr(radky,Pos(#13,radky)-1) else zaznam:= radky; zaznam:= Trim(StringReplace(zaznam, #9#9, #9' '#9,[rfReplaceAll])); if (zaznam<>'') then for idxS:=0 to sl-1 do begin if (zaznam<>'') then begin if (Pos(#9, zaznam)>0) then begin arrRadky[idxR,idxS]:= LeftStr(zaznam, Pos(#9, zaznam)-1); Delete(zaznam,1,Pos(#9, zaznam)); end else begin arrRadky[idxR,idxS]:= zaznam; zaznam:= ''; end; end; end; Delete(radky,1,Pos(#13, radky)+1); end; waitStart(nil, 'Import dat karet forem', Length(arrRadky), clRed); for idxR:=0 to High(arrRadky)-1 do begin if (idxR mod 10=0) then waitSetProgBar(idxR); try idKZ:= 0; ClearForma(forma); forma.sz:= Trim(arrRadky[idxR, 0]); posunSl:= 0; if (MatchStr(UpperCase(forma.sz), arrFP)) then posunSl:= 1 else forma.sz:= 'F00'; { if Trim(arrRadky[idxR, 0])<>Trim(arrRadky[idxR, 1]) then kmen.sz:= Trim(arrRadky[idxR, 0]); if (Length(kmen.sz)=2) then kmen.sz:= '0' + kmen.sz; } forma.regCis:= Trim(arrRadky[idxR, 0 + posunSl]); if (forma.regCis='') then Continue; forma.nazev1:= Trim(arrRadky[idxR, 1 + posunSl]); // TryStrToFloat(StringReplace(Trim(arrRadky[idxR, 19]),' ','',[rfReplaceAll]), kmen.jcena); // TryStrToInt(Trim(arrRadky[idxR, 23]), kmen.minDod); forma.vc:= Trim(arrRadky[idxR, 2 + posunSl]); forma.popisVc:= Trim(arrRadky[idxR, 3 + posunSl]); forma.skutPopVc:= Trim(arrRadky[idxR, 4 + posunSl]); forma.vykres:= Trim(arrRadky[idxR, 5 + posunSl]); forma.cilSklad:= Trim(arrRadky[idxR, 6 + posunSl]); TryStrToFloat(StringReplace(Trim(arrRadky[idxR, 7 + posunSl]),' ','',[rfReplaceAll]), forma.jcena); with Helios.OpenSQL('SELECT ID FROM ' + tblKZ + ' WHERE SkupZbo=N' + QuotedStr(forma.sz) + ' AND RegCis=N' + QuotedStr(forma.regCis)) do if (RecordCount=1) then idKZ:= StrToInt(VarToStr(FieldValues(0))); if (idKZ=0) then begin lSQL:= 'INSERT ' + tblKZ + ' (DruhSkladu, SkupZbo, RegCis, Nazev1, Nazev2) SELECT 1, N' + QuotedStr(forma.sz) + ', N' + QuotedStr(forma.regCis); lSQL:= lSQL + ', N' + QuotedStr(forma.nazev1) + ', N' + QuotedStr(forma.nazev2) + CRLF; lSQL:= lSQL + 'SELECT SCOPE_IDENTITY()'; try with Helios.OpenSQL(lSQL) do idKZ:= StrToInt(VarToStr(FieldValues(0))); Inc(cntIns); except on E:Exception do begin Helios.Error(#1'Chyba INS: ' + e.Message +#1); idKZ:= 0; end; end; end else begin // lSQL:= 'UPDATE ' + tblKZ + ' SET Nazev1=N' + QuotedStr(forma.nazev1) + ', Nazev2=N' + QuotedStr(forma.nazev2) + ' WHERE ID=' + idKZ.ToString; // Helios.ExecSQL(lSQL); // Inc(cntAkt); end; if (idKZ>0) then begin // lSQL:= 'UPDATE ' + tblKZe + ' SET _Naradi_ReworkOrg=' + IfThen(kmen.cOrgRework=0, 'NULL', kmen.cOrgRework.ToString) + ' WHERE ID=' + idKZ.ToString; // lSQL:= 'UPDATE ' + tblKZ + ' SET Vykres=N' + IfThen(forma.vykres='', 'ULL', QuotedStr(forma.vykres)) + ' WHERE ID=' + idKZ.ToString; // Helios.ExecSQL(lSQL); if (forma.cilSklad<>'') and (helUtils.HeliosExistsTest(Helios, tblStrom, 'Cislo=N' + QuotedStr(forma.cilSklad))) then begin idSS:= 0; lSQL:= 'DECLARE @idSS INT' + CRLF + 'SET @idSS=(SELECT ID FROM ' + tblSS + ' WHERE IDSklad=N' + QuotedStr(forma.cilSklad) + ' AND IDKmenZbozi=' + idKZ.ToString + ')'; lSQL:= lSQL + CRLF + 'IF (@idSS IS NULL)' + CRLF + ' BEGIN' + CRLF + ' INSERT ' + tblSS + ' (IDSklad, IDKmenZbozi) VALUES (N' + QuotedStr(forma.cilSklad)+ ', '; lSQL:= lSQL + idKZ.ToString + ')' + CRLF + ' SET @idSS=SCOPE_IDENTITY()' + CRLF + ' END' + CRLF + 'SELECT @idSS'; try with Helios.OpenSQL(lSQL) do idSS:= StrToInt(VarToStr(FieldValues(0))); except idSS:= 0; end; if (idSS>0) and (forma.vc<>'') then begin idVC:= 0; lSQL:= 'DECLARE @idVC INT' + CRLF + 'SET @idVC=(SELECT ID FROM ' + tblVyrCS + ' WHERE IDStavSkladu=' + idSS.ToString + ' AND Nazev1=N' + QuotedStr(forma.vc) + ')' + CRLF; lSQL:= lSQL + 'IF (@idVC IS NULL)' + CRLF + ' BEGIN' + ' INSERT ' + tblVyrCS + ' (IDStavSkladu, Nazev1) VALUES (' + idSS.ToString + ', N' + QuotedStr(forma.vc) + ')'; lSQL:= lSQL + CRLF + ' SET @idVC=SCOPE_IDENTITY()' + CRLF + ' END' + CRLF + 'SELECT @idVC'; try with Helios.OpenSQL(lSQL) do idVC:= StrToInt(VarToStr(FieldValues(0))); except idVC:= 0; end; if (idVC>0) then Helios.ExecSQL('UPDATE ' + tblVyrCSE + ' SET _SkutecnyPopis=N' + IfThen(forma.skutPopVc='', 'ULL', QuotedStr(forma.skutPopVc)) + ' WHERE ID=' + idVC.ToString); end; end; end; except on E:Exception do errMsg:= E.Message + CRLF + lSQL; end; end; waitEnd; Helios.Refresh(true); Helios.Info(#1'Nových karet forem: ' + cntIns.ToString + CRLF + 'Upravených karet forem: ' + cntAkt.ToString + #1); end; end; end; procedure TplgKdynium.ImportKmenFormyVC (const Helios: IHelios); const arrFP: TArray = ['F00', 'F01', 'F10', 'F11', 'F20', 'F21']; var lSQL, radky, zaznam, msg, errMsg, sTemp: String; cnt, sl, idxR, idxS, idKZ, idSS, idVC, cntIns, cntAkt, overRadek, posunSl: integer; arrRadky: TArray>; forma: TForma; cont, jsemNew: Boolean; begin overRadek:= 0; posunSl:= 0; if (Clipboard.HasFormat(CF_TEXT)) then begin errMsg:= ''; cont:= true; radky:= ''; try radky:= Clipboard.AsText; sl:= PosCount(#9, LeftStr(radky, Pos(#13, radky)-1)) + 1; // pocet sloupcu except on E:Exception do cont:= false; end; if (radky<>'') and (cont) then begin cont:= true; cntIns:= 0; cntAkt:= 0; cnt:= Length(radky)-Length(StringReplace(radky, Chr(13),'',[rfReplaceAll]))+1; SetLength(arrRadky, cnt, sl); // index od 0 if (Pos(#13, radky)>0) then zaznam:= LeftStr(radky, Pos(#13, radky)-1) // nazvy sloupcu else zaznam:= radky; if Helios.YesNo('Má importovaná oblast hlavičku ?', false) then begin Delete(radky,1,Pos(#13,radky)+1); // smaze hlavicku SetLength(arrRadky,Length(arrRadky)-1); end; for idxR:=0 to High(arrRadky)-1 do begin if (Pos(#13,radky)>0) then zaznam:= LeftStr(radky,Pos(#13,radky)-1) else zaznam:= radky; zaznam:= Trim(StringReplace(zaznam, #9#9, #9' '#9,[rfReplaceAll])); if (zaznam<>'') then for idxS:=0 to sl-1 do begin if (zaznam<>'') then begin if (Pos(#9, zaznam)>0) then begin arrRadky[idxR,idxS]:= LeftStr(zaznam, Pos(#9, zaznam)-1); Delete(zaznam,1,Pos(#9, zaznam)); end else begin arrRadky[idxR,idxS]:= zaznam; zaznam:= ''; end; end; end; Delete(radky,1,Pos(#13, radky)+1); end; waitStart(nil, 'Import dat karet forem', Length(arrRadky), clRed); for idxR:=0 to High(arrRadky)-1 do begin if (idxR mod 10=0) then waitSetProgBar(idxR); try idKZ:= 0; jsemNew:= false; ClearForma(forma); forma.sz:= Trim(arrRadky[idxR, 0]); if (forma.sz='') then Continue; posunSl:= 1; forma.regCis:= Trim(arrRadky[idxR, 1 + posunSl]); if (forma.regCis='') then Continue; forma.vc:= Trim(arrRadky[idxR, 2 + posunSl]); forma.popisVc:= Trim(arrRadky[idxR, 3 + posunSl]); forma.skutPopVc:= Trim(arrRadky[idxR, 4 + posunSl]); forma.vykres:= Trim(arrRadky[idxR, 5 + posunSl]); with Helios.OpenSQL('SELECT ID FROM ' + tblKZ + ' WHERE SkupZbo=N' + forma.sz.QuotedString + ' AND RegCis=N' + forma.regCis.QuotedString) do if (RecordCount=1) then idKZ:= StrToInt(VarToStr(FieldValues(0))); if (idKZ=0) then begin lSQL:= 'DECLARE @i INT=0' + CRLF + 'EXEC @i=dbo.hp_VytvorPolozkuKmeneZbozi @Dilec=1, @Naradi=1, @SZ=N' + forma.sz.QuotedString; lSQL:= lSQL + IfThen(forma.regCis<>'', ', @RegCis=N' + forma.regCis.QuotedString, ''); lSQL:= lSQL + IfThen(forma.nazev1<>'', ', @Nazev1=N' + forma.nazev1.QuotedString, ''); lSQL:= lSQL + IfThen(forma.nazev2<>'', ', @Nazev2=N' + forma.nazev2.QuotedString, ''); lSQL:= lSQL + IfThen(forma.vykres<>'', ', @Vykres=N' + forma.vykres.QuotedString, ''); lSQL:= lSQL + CRLF + 'SELECT @i'; try with Helios.OpenSQL(lSQL) do idKZ:= StrToInt(VarToStr(FieldValues(0))); Inc(cntIns); jsemNew:= true; except on E:Exception do begin Helios.Error(#1'Chyba INS: ' + e.Message +#1); idKZ:= 0; end; end; end else begin // lSQL:= 'UPDATE ' + tblKZ + ' SET Nazev1=N' + QuotedStr(forma.nazev1) + ', Nazev2=N' + QuotedStr(forma.nazev2) + ' WHERE ID=' + idKZ.ToString; // Helios.ExecSQL(lSQL); // Inc(cntAkt); end; if (idKZ>0) then begin lSQL:= ''; if (forma.nazev1<>'') then lSQL:= 'UPDATE ' + tblKZ + ' SET Nazev1=N' + forma.nazev1.QuotedString + ' WHERE ID=' + idKZ.ToString + ' AND Nazev1<>N' + forma.nazev1.QuotedString + CRLF; if (forma.nazev2<>'') then lSQL:= lSQL + 'UPDATE ' + tblKZ + ' SET Nazev2=N' + forma.nazev2.QuotedString + ' WHERE ID=' + idKZ.ToString + ' AND Nazev2<>N' + forma.nazev2.QuotedString + CRLF; if (forma.vykres<>'') then lSQL:= lSQL + 'UPDATE ' + tblKZ + ' SET Vykres=N' + forma.vykres.QuotedString + ' WHERE ID=' + idKZ.ToString + ' AND ISNULL(Vykres,N'''')<>N' + forma.vykres.QuotedString + CRLF; if (lSQL<>'') then Helios.ExecSQL(lSQL); if (forma.skutPopVc<>'') then begin lSQL:= 'DECLARE @i INT' + CRLF + 'SET @i=(SELECT id FROM ' + tblVyrCK + ' WHERE IDKmenZbozi=' + idKZ.ToString + ' AND Nazev1=N'; lSQL:= lSQL + forma.vc.QuotedString + ')' + CRLF + 'IF (@i>0) UPDATE ' + tblVyrCK + ' SET Nazev2=N' + forma.popisVc.QuotedString + ' WHERE ID=@i' + CRLF; lSQL:= lSQL + 'IF (@i IS NULL) EXEC dbo.hp_OZInsertVyrCK @IDVyrCK=@i OUT, @IDKmenZbozi=' + idKZ.ToString + ', @Nazev1=N'; lSQL:= lSQL + forma.vc.QuotedString + ', @Nazev2=N' + forma.popisVc.QuotedString + CRLF; lSQL:= lSQL + 'IF (@i>0) AND NOT EXISTS(SELECT ID FROM ' + tblVyrCKE + ' WHERE ID=@i) INSERT ' + tblVyrCKE + ' (ID) VALUES (@i)' + CRLF; lSQL:= lSQL + 'IF (@i>0) UPDATE ' + tblVyrCKE + ' SET _SkutecnyPopis=N' + forma.skutPopVc.QuotedString + ' WHERE ID=@i'; Helios.ExecSQL(lSQL); if not(jsemNew) then Inc(cntAkt); end; end; except on E:Exception do errMsg:= E.Message + CRLF + lSQL; end; end; waitEnd; Helios.Refresh(true); Helios.Info(#1'Import ' + cntIns.ToString + ' nových karet' + CRLF + 'Aktualizací karet: ' + cntAkt.ToString + #1); end; end; end; procedure TplgKdynium.ImportKmenProMatrix (const Helios: IHelios; sklMatrix2: string); var lSQL, radky, zaznam, msg, errMsg, sTemp: String; cnt, sl, posunSl, idxR, idxS, idKZ, cntIns, cntAkt, overRadek: integer; arrRadky: TArray>; kmen: TKmen; cont: Boolean; begin posunSl:= 0; if (sklMatrix2='') then sklMatrix2:= sklMatrix; overRadek:= 0; if (Clipboard.HasFormat(CF_TEXT)) then begin errMsg:= ''; cont:= true; radky:= ''; try radky:= Clipboard.AsText; sl:= PosCount(#9, LeftStr(radky, Pos(#13, radky)-1)) + 1; // pocet sloupcu except on E:Exception do cont:= false; end; if (radky<>'') and (cont) then begin cont:= true; cntIns:= 0; cntAkt:= 0; cnt:= Length(radky)-Length(StringReplace(radky, Chr(13),'',[rfReplaceAll]))+1; SetLength(arrRadky, cnt, sl); // index od 0 if (Pos(#13, radky)>0) then zaznam:= LeftStr(radky, Pos(#13, radky)-1) // nazvy sloupcu else zaznam:= radky; if Helios.YesNo('Má importovaná oblast hlavičku ?', false) then begin Delete(radky,1,Pos(#13,radky)+1); // smaze hlavicku SetLength(arrRadky,Length(arrRadky)-1); end; for idxR:=0 to High(arrRadky)-1 do begin if (Pos(#13,radky)>0) then zaznam:= LeftStr(radky,Pos(#13,radky)-1) else zaznam:= radky; zaznam:= Trim(StringReplace(zaznam, #9#9, #9' '#9,[rfReplaceAll])); if (zaznam<>'') then for idxS:=0 to sl-1 do begin if (zaznam<>'') then begin if (Pos(#9, zaznam)>0) then begin arrRadky[idxR,idxS]:= LeftStr(zaznam, Pos(#9, zaznam)-1); Delete(zaznam,1,Pos(#9, zaznam)); end else begin arrRadky[idxR,idxS]:= zaznam; zaznam:= ''; end; end; end; Delete(radky,1,Pos(#13, radky)+1); end; waitStart(nil, 'Import dat karet nářadí', Length(arrRadky), clRed); for idxR:=0 to High(arrRadky)-1 do begin if (idxR mod 10=0) then waitSetProgBar(idxR); try idKZ:= 0; clearKmen(kmen); { if Trim(arrRadky[idxR, 0])<>Trim(arrRadky[idxR, 1]) then kmen.sz:= Trim(arrRadky[idxR, 0]); if (Length(kmen.sz)=2) then kmen.sz:= '0' + kmen.sz; } kmen.sz:= Trim(arrRadky[idxR, posunSl + 0]); kmen.sz:= '054'; kmen.regCis:= Trim(arrRadky[idxR, posunSl + 1]); kmen.nazev1:= Trim(arrRadky[idxR, posunSl + 2]); if (kmen.regCis='') or (kmen.nazev1='') then continue; kmen.technologie:= Trim(arrRadky[idxR, posunSl + 6]); kmen.nazev3:= Trim(arrRadky[idxR, posunSl + 9]); kmen.skp:= Trim(arrRadky[idxR, posunSl + 3]); kmen.sortiment:= Trim(arrRadky[idxR, posunSl + 5]); if (LowerCase(kmen.nazev3)='katalogová') then kmen.kateg:= 1; if (LowerCase(kmen.nazev3)='speciální') then kmen.kateg:= 2; kmen.nazev3:= ''; if (LowerCase(Trim(arrRadky[idxR, posunSl + 10]))='spotřební') then kmen.typ:= 1; if (LowerCase(Trim(arrRadky[idxR, posunSl + 10]))='obnovitelná') then kmen.typ:= 2; if (LowerCase(Trim(arrRadky[idxR, posunSl + 10]))='trvalá') then kmen.typ:= 3; if (LeftStr(LowerCase(Trim(arrRadky[idxR, posunSl + 10])),4)='klíč') then kmen.typ:= 5; if (LowerCase(Trim(arrRadky[idxR, posunSl + 10]))='měřidlo') then kmen.typ:= 6; TryStrToInt(Trim(arrRadky[idxR, posunSl + 11]), kmen.cOrgDod); TryStrToInt(Trim(arrRadky[idxR, posunSl + 13]), kmen.cOrgVyr); TryStrToInt(Trim(arrRadky[idxR, posunSl + 15]), kmen.dodLhuta); TryStrToInt(Trim(arrRadky[idxR, posunSl + 16]), kmen.lhutaNaskl); kmen.mj:= Trim(arrRadky[idxR, posunSl + 17]); kmen.mena:= LowerCase(Trim(arrRadky[idxR, posunSl + 18])); TryStrToFloat(StringReplace(Trim(arrRadky[idxR, posunSl + 19]),' ','',[rfReplaceAll]), kmen.jcena); TryStrToInt(Trim(arrRadky[idxR, posunSl + 23]), kmen.minDod); TryStrToInt(Trim(arrRadky[idxR, posunSl + 24]), kmen.minOdb); TryStrToInt(Trim(arrRadky[idxR, posunSl + 28]), kmen.minMnoz); TryStrToInt(Trim(arrRadky[idxR, posunSl + 29]), kmen.maxMnoz); kmen.v_min:= Trim(arrRadky[idxR, posunSl + 30]); kmen.f_ot:= Trim(arrRadky[idxR, posunSl + 31]); if (sl>31) then kmen.f_z:= Trim(arrRadky[idxR, posunSl + 32]); if (sl>32) then kmen.barcode:= Trim(arrRadky[idxR, posunSl + 33]); kmen.obj:= arrRadky[idxR, posunSl + 7].ToBoolean(); if (sl>33) then begin kmen.cOrgReworkS:= Trim(arrRadky[idxR, posunSl + 34]); if (kmen.cOrgReworkS.Contains('/')) then begin TryStrToInt(LeftStr(kmen.cOrgReworkS, Pos('/', kmen.cOrgReworkS)-1), kmen.cOrgRework); kmen.cOrgReworkSuff:= MidStr(kmen.cOrgReworkS, Pos('/', kmen.cOrgReworkS)+1, 10); end else TryStrToInt(Trim(arrRadky[idxR, posunSl + 34]), kmen.cOrgRework); end; if (sl>35) then kmen.menaRework:= LowerCase(Trim(arrRadky[idxR, posunSl + 36])); if (sl>36) then TryStrToFloat(StringReplace(Trim(arrRadky[idxR, posunSl + 37]),' ','',[rfReplaceAll]), kmen.jcenaRework); with Helios.OpenSQL('SELECT ID FROM ' + tblKZ + ' WHERE SkupZbo=N' + QuotedStr(kmen.sz) + ' AND RegCis=N' + QuotedStr(kmen.regCis)) do if (RecordCount=1) then idKZ:= StrToInt(VarToStr(FieldValues(0))); if (idKZ=0) then begin lSQL:= 'DECLARE @idKZ INT; EXEC @idKZ=dbo.hp_VytvorPolozkuKmeneZbozi @SZ=N' + kmen.sz.QuotedString + ', @RegCis=N' + kmen.regCis.QuotedString; lSQL:= lSQL + ', @Nazev1=N' + kmen.nazev1.QuotedString + IfThen(kmen.nazev2<>'', ', @Nazev2=N' + kmen.nazev2.QuotedString, ''); lSQL:= lSQL + IfThen(kmen.nazev3<>'', ', @Nazev3=N' + kmen.nazev3.QuotedString, '') + IfThen(kmen.mj<>'', ', @MJEv=N' + kmen.mj.QuotedString, ''); lSQL:= lSQL + IfThen(kmen.skp<>'', ', @SKP=N' + kmen.skp.QuotedString, '') + '; SELECT @idKZ'; try with Helios.OpenSQL(lSQL) do idKZ:= StrToInt(VarToStr(FieldValues(0))); Inc(cntIns); except on E:Exception do Helios.Error(#1'Chyba INS: ' + e.Message +#1); end; if (idKZ>0) then begin if (kmen.cOrgDod<>0) then Helios.ExecSQL('UPDATE ' + tblKZ + ' SET Aktualni_Dodavatel=' + kmen.cOrgDod.ToString + ' WHERE ID=' + idKZ.ToString); if (kmen.cOrgVyr<>0) then Helios.ExecSQL('UPDATE ' + tblKZ + ' SET Vyrobce=' + kmen.cOrgVyr.ToString + ' WHERE ID=' + idKZ.ToString); if (kmen.sortiment<>'') then Helios.ExecSQL('UPDATE ' + tblKZ + ' SET IdSortiment=ISNULL( (SELECT ID FROM ' + tblSortim + ' WHERE KatAllTecky=N' + kmen.sortiment.QuotedString + '), NULL) WHERE ID=' + idKZ.ToString); if (kmen.minDod<>0) then Helios.ExecSQL('UPDATE ' + tblKZ + ' SET Minimum_Baleni_Dodavatel=' + kmen.minDod.ToString + ' WHERE ID=' + idKZ.ToString); if (kmen.dodLhuta<>0) then Helios.ExecSQL('UPDATE ' + tblKZ + ' SET DodaciLhuta=' + kmen.dodLhuta.ToString + ' WHERE ID=' + idKZ.ToString); end; end else begin lSQL:= 'UPDATE ' + tblKZ + ' SET Nazev1=N' + kmen.nazev1.QuotedString + ', Nazev2=N' + kmen.nazev2.QuotedString + ', Nazev3=N' + kmen.nazev3.QuotedString; lSQL:= lSQL + ', SKP=N' + kmen.skp.QuotedString; lSQL:= lSQL + IfThen(kmen.sortiment<>'', ', IdSortiment=ISNULL( (SELECT ID FROM ' + tblSortim + ' WHERE KatAllTecky=N' + kmen.sortiment.QuotedString + '), NULL)', ''); lSQL:= lSQL + ' WHERE ID=' + idKZ.ToString; Helios.ExecSQL(lSQL); Inc(cntAkt); end; if (idKZ>0) then begin // mena nakup lSQL:= kmen.mena; if (kmen.mena='kč') or (kmen.mena='czk') then lSQL:= 'CZK' else if (kmen.mena='€') or (kmen.mena='eur') then lSQL:= 'EUR'; if (lSQL='') then lSQL:= 'CZK'; lSQL:= 'UPDATE ' + tblKZe + ' SET _Matrix_MenaNakup=N' + lSQL.QuotedString + ' WHERE ID=' + idKZ.ToString; Helios.ExecSQL(lSQL); lSQL:= 'IF NOT EXISTS(SELECT id FROM ' + tblNC + ' WHERE CenovaUroven=99 AND IDKmenZbozi=' + idKZ.ToString + ') INSERT ' + tblNC + ' (CenovaUroven, IDKmenZbozi) VALUES (99, ' + idKZ.ToString + ')'; Helios.ExecSQL(lSQL); if (kmen.mena='kč') or (kmen.mena='czk') then Helios.ExecSQL('UPDATE ' + tblNC + ' SET CenaKC=' + StringReplace(kmen.jcena.ToString, ',', '.',[rfReplaceAll]) + ' WHERE CenovaUroven=99 AND IDKmenZbozi=' + idKZ.ToString); if (kmen.mena='€') or (kmen.mena='eur') then begin lSQL:= 'IF NOT EXISTS(SELECT id FROM ' + tblNC + ' WHERE Mena1=N''EUR'' AND CenovaUroven=99 AND IDKmenZbozi=' + idKZ.ToString + ') '; Helios.ExecSQL(lSQL + 'UPDATE ' + tblNC + ' SET Mena1=N''EUR'' WHERE CenovaUroven=99 AND IDKmenZbozi=' + idKZ.ToString); Helios.ExecSQL('UPDATE ' + tblNC + ' SET CenaVal1=' + StringReplace(kmen.jcena.ToString, ',', '.',[rfReplaceAll]) + ' WHERE Mena1=N''EUR'' AND CenovaUroven=99 AND IDKmenZbozi=' + idKZ.ToString); end; if (kmen.barcode<>'') then begin lSQL:= 'IF NOT EXISTS(SELECT ID FROM ' + tblBCode + ' WHERE BarCode=N' + QuotedStr(kmen.barcode) + ') INSERT ' + tblBCode + ' (IDKmenZbo, Prednastaveno, BarCode)'; lSQL:= lSQL + ' SELECT ' + idKZ.ToString + ', 1, N' + QuotedStr(kmen.barcode); Helios.ExecSQL(lSQL); end; lSQL:= 'IF NOT EXISTS(SELECT ID FROM ' + tblKZe + ' WHERE ID=' + idKZ.ToString + ') INSERT ' + tblKZe + ' (ID) VALUES (' + idKZ.ToString + ')'; Helios.ExecSQL(lSQL); lSQL:= 'UPDATE ' + tblKZe + ' SET _Naradi_TypPolozky=' + IfThen(kmen.typ=0, 'NULL', kmen.typ.ToString) + ', _Naradi_Kategorie=' + kmen.kateg.ToString + ', _Naradi_Objednavat=' + kmen.obj.ToString(); lSQL:= lSQL + ' WHERE ID=' + idKZ.ToString; Helios.ExecSQL(lSQL); if (kmen.typ>0) then Helios.ExecSQL('UPDATE ' + tblKZ + ' SET Nazev3=N'''' WHERE id=' + idKZ.ToString); lSQL:= 'UPDATE ' + tblKZe + ' SET _Naradi_F_mn_ot=N' + IfThen(kmen.f_ot='', 'ULL', QuotedStr(kmen.f_ot)) + ', _Naradi_V_m_min=N' + IfThen(kmen.v_min='', 'ULL', QuotedStr(kmen.v_min)); lSQL:= lSQL + ', _Naradi_F_mn_z=N' + IfThen(kmen.f_z='', 'ULL', QuotedStr(kmen.f_z)) + ' WHERE ID=' + idKZ.ToString; Helios.ExecSQL(lSQL); lSQL:= 'UPDATE ' + tblKZe + ' SET _Naradi_TechnologickePouziti=N' + IfThen(kmen.technologie='', 'ULL', QuotedStr(kmen.technologie)); lSQL:= lSQL + ' WHERE ID=' + idKZ.ToString; Helios.ExecSQL(lSQL); if (kmen.technologie<>'') then Helios.ExecSQL('UPDATE ' + tblKZ + ' SET Nazev2=N'''' WHERE id=' + idKZ.ToString); lSQL:= 'IF NOT EXISTS(SELECT ID FROM ' + tblSS + ' WHERE IDSklad=N' + QuotedStr(sklMatrix2) + ' AND IDKmenZbozi=' + idKZ.ToString; lSQL:= lSQL + ') INSERT ' + tblSS + ' (IDSklad, IDKmenZbozi) VALUES (N' + QuotedStr(sklMatrix2) + ', ' + idKZ.ToString + ')' + CRLF; lSQL:= lSQL + ' UPDATE ' + tblSS + ' SET Minimum=' + kmen.minMnoz.ToString + ', Maximum=' + kmen.maxMnoz.ToString; lSQL:= lSQL + ' WHERE IDSklad=N' + QuotedStr(sklMatrix2) + ' AND IDKmenZbozi=' + idKZ.ToString; Helios.ExecSQL(lSQL); lSQL:= 'UPDATE ' + tblKZe + ' SET _Naradi_ReworkOrg=' + IfThen(kmen.cOrgRework=0, 'NULL', kmen.cOrgRework.ToString) + ' WHERE ID=' + idKZ.ToString; Helios.ExecSQL(lSQL); lSQL:= 'UPDATE ' + tblKZe + ' SET _Naradi_ReworkOrgSuff=N' + IfThen(kmen.cOrgReworkSuff='', 'ULL', kmen.cOrgReworkSuff.QuotedString) + ' WHERE ID=' + idKZ.ToString; Helios.ExecSQL(lSQL); // rework cena lSQL:= kmen.menaRework; if (kmen.menaRework='kč') or (kmen.menaRework='czk') then lSQL:= 'CZK' else if (kmen.menaRework='€') or (kmen.menaRework='eur') then lSQL:= 'EUR'; if (lSQL='') then lSQL:= 'CZK'; lSQL:= 'UPDATE ' + tblKZe + ' SET _Matrix_MenaRework=N' + lSQL.QuotedString + ' WHERE ID=' + idKZ.ToString; Helios.ExecSQL(lSQL); lSQL:= 'IF NOT EXISTS(SELECT id FROM ' + tblNC + ' WHERE CenovaUroven=98 AND IDKmenZbozi=' + idKZ.ToString + ') INSERT ' + tblNC + ' (CenovaUroven, IDKmenZbozi) VALUES (98, ' + idKZ.ToString + ')'; Helios.ExecSQL(lSQL); if (kmen.menaRework='kč') or (kmen.menaRework='czk') then Helios.ExecSQL('UPDATE ' + tblNC + ' SET CenaKC=' + StringReplace(kmen.jcenaRework.ToString, ',', '.',[rfReplaceAll]) + ' WHERE CenovaUroven=98 AND IDKmenZbozi=' + idKZ.ToString); if (kmen.menaRework='€') or (kmen.menaRework='eur') then begin lSQL:= 'IF NOT EXISTS(SELECT id FROM ' + tblNC + ' WHERE Mena1=N''EUR'' AND CenovaUroven=98 AND IDKmenZbozi=' + idKZ.ToString + ') '; Helios.ExecSQL(lSQL + 'UPDATE ' + tblNC + ' SET Mena1=N''EUR'' WHERE CenovaUroven=98 AND IDKmenZbozi=' + idKZ.ToString); Helios.ExecSQL('UPDATE ' + tblNC + ' SET CenaVal1=' + StringReplace(kmen.jcenaRework.ToString, ',', '.',[rfReplaceAll]) + ' WHERE Mena1=N''EUR'' AND CenovaUroven=98 AND IDKmenZbozi=' + idKZ.ToString); end; end; except on E:Exception do errMsg:= E.Message + CRLF + lSQL; end; end; waitEnd; Helios.Refresh(true); Helios.Info(#1'Nových karet: ' + cntIns.ToString + CRLF + 'Upravených karet: ' + cntAkt.ToString + #1); end; end; end; procedure TplgKdynium.ExportProMatrix (const Helios: IHelios); var lSQL, mena, menaR, sTypPol, supp, fmaNazev: string; xls: IXLSWorkBook; shUsers, shSupp, shItems, shSerials: IXLSWorksheet; i, idOrg, cOrg, idKZ, iTypPol: integer; tmpStr, tmpStr2: string; supps: TArray; fndIdx: integer; doplnNulu: boolean; begin xls:= TXLSWorkbook.Create; try shUsers:= xls.Sheets.Add; shUsers.Name:= 'Users'; shUsers.Cells.Item[1,1].Value:= 'USER_NAME'; shUsers.Cells.Item[1,2].Value:= 'PASSWORD'; shUsers.Cells.Item[1,3].Value:= 'FULL_NAME'; shUsers.Cells.Item[1,4].Value:= 'LAST_NAME'; shUsers.Cells.Item[1,5].Value:= 'FIRST_NAME'; shUsers.Cells.Item[1,6].Value:= 'GROUP_NAME'; shUsers.Cells.Item[1,7].Value:= 'EMAIL'; shUsers.Cells.Item[1,8].Value:= 'USER_CODE'; lSQL:= 'SELECT z.Jmeno, z.Prijmeni, z.PrijmeniJmeno, z.Cislo FROM ' + tblCZam + ' z INNER JOIN ' + tblZamMzd + ' m ON (m.ZamestnanecID=z.ID)'; lSQL:= lSQL + ' WHERE m.IdObdobi=(SELECT IdObdobi FROM ' + tblMzdObd + ' WHERE Rok=DATEPART(year, GETDATE()) AND Mesic=DATEPART(month,GETDATE()))'; lSQL:= lSQL + ' AND m.StavES=0 AND z.Stredisko=N' + QuotedStr('00100200200') + ' AND z.Cislo<700000 ORDER BY z.Cislo, z.Prijmeni, z.Jmeno'; with Helios.OpenSQL(lSQL) do begin First; i:= 1; while not(EOF) do begin shUsers.Cells.Item[1+i,1].Value:= VarToStr(FieldValues(3)); shUsers.Cells.Item[1+i,3].Value:= VarToStr(FieldValues(2)); shUsers.Cells.Item[1+i,4].Value:= VarToStr(FieldValues(1)); shUsers.Cells.Item[1+i,5].Value:= VarToStr(FieldValues(0)); shUsers.Cells.Item[1+i,8].Value:= VarToStr(FieldValues(3)); Inc(i); Next; end; end; lSQL:= 'IF OBJECT_ID(N''tempdb..#TabMatrixSupps'', N''U'') IS NOT NULL DROP TABLE #TabMatrixSupps' + CRLF + 'CREATE TABLE #TabMatrixSupps (CisloOrg INT, Suffix NVARCHAR(5) DEFAULT N'''' NOT NULL, Nazev NVARCHAR(100)' + ', Mena NVARCHAR(50), Doprava NVARCHAR(100), KplCisloOrg AS (CONVERT(nvarchar, CisloOrg) + IIF(Suffix<>N'''', N''/''+ Suffix, N'''')) )' + CRLF; Helios.ExecSQL(lSQL); shSupp:= xls.Sheets.Add; shSupp.Name:= 'Suppliers'; shSupp.Cells.Item[1,1].Value:= 'SUPPLIER_CODE'; shSupp.Cells.Item[1,2].Value:= 'SUPPLIER_NAME'; shSupp.Cells.Item[1,3].Value:= 'SHIPPING_METHOD_NAME'; shSupp.Cells.Item[1,4].Value:= 'ORDER_METHOD'; shSupp.Cells.Item[1,5].Value:= 'PRICE_NAME'; shSupp.Cells.Item[1,6].Value:= 'SUPPLIER_EMAIL'; i:= 1; lSQL:= 'SELECT DISTINCT(ke._Naradi_ReworkOrg), ISNULL(ke._Naradi_ReworkOrgSuff, N''''), o.Nazev, o.Mena, ISNULL(o.FormaDopravy,N'''') FROM ' + tblKZ + ' k INNER JOIN ' + tblKZe + ' ke ON (ke.ID=k.ID) INNER JOIN ' + tblCOrg + ' o ON (ke._Naradi_ReworkOrg=o.CisloOrg) WHERE ke._Naradi_ReworkOrg IS NOT NULL AND ISNULL(ke._Naradi_ReworkOrgSuff, N'''')<>N''''' + ' AND ((k.SkupZbo=N''054'' AND LEFT(k.RegCis,1)=N''7'') OR (k.SkupZbo=N''MNA'' AND LEFT(k.RegCis,1)=N''M''))' // + ' AND k.RegCis=N''710320''' + ' ORDER BY ke._Naradi_ReworkOrg, ISNULL(ke._Naradi_ReworkOrgSuff, N'''')'; with Helios.OpenSQL(lSQL) do begin First; while not(EOF) do begin tmpStr:= VarToStr(FieldValues(0)); cOrg:= tmpStr.ToInteger; idOrg:= helUtils.getHeliosIntVal(Helios, -1, 'SELECT ID FROM ' + tblCOrg + ' WHERE CisloOrg=' + tmpStr); supp:= VarToStr(FieldValues(1)); if (supp<>'') then tmpStr:= tmpStr + '/' + supp; mena:= ''; if not(tmpStr.Contains('/')) then mena:= helUtils.getHeliosStrVal(Helios, '', 'SELECT _Matrix_MenaNakup FROM ' + tblCOrgE + ' WHERE ID=' + idOrg.ToString) else mena:= helUtils.getHeliosStrVal(Helios, '', 'SELECT _Matrix_MenaRework FROM ' + tblCOrgE + ' WHERE ID=' + idOrg.ToString); if (mena='') then mena:= VarToStr(FieldValues(3)); if (mena='CZK') or (mena='') then mena:= 'koruna'; if (mena='EUR') then mena:= 'euro'; if (mena='USD') then mena:= 'dolar'; fmaNazev:= VarToStr(FieldValues(2)); if (supp<>'') then fmaNazev:= fmaNazev + '/' + supp; lSQL:= 'IF NOT EXISTS(SELECT 1 FROM #TabMatrixSupps WHERE KplCisloOrg=N' + tmpStr.QuotedString + ')' + CRLF + ' INSERT #TabMatrixSupps (CisloOrg, Suffix, Nazev, Mena, Doprava) SELECT ' + cOrg.ToString + ', N' + IfThen(supp<>'', supp, '').QuotedString + ', N' + fmaNazev.QuotedString + ', N' + mena.QuotedString + ', N' + VarToStr(FieldValues(4)).QuotedString; Helios.ExecSQL(lSQL); Next; end; end; lSQL:= 'SELECT DISTINCT(k.Aktualni_Dodavatel), o.Nazev, o.Mena, ISNULL(o.FormaDopravy,N'''') FROM ' + tblKZ + ' k INNER JOIN ' + tblCOrg + ' o ON (k.Aktualni_Dodavatel=o.CisloOrg) WHERE k.Aktualni_Dodavatel IS NOT NULL AND ((k.SkupZbo=N''054''' + ' AND LEFT(k.RegCis,1)=N''7'') OR (k.SkupZbo=N''MNA'' AND LEFT(k.RegCis,1)=N''M''))' // + ' AND k.RegCis=N''710320''' + ' ORDER BY k.Aktualni_Dodavatel'; with Helios.OpenSQL(lSQL) do begin First; // i:= 1; while not(EOF) do begin tmpStr:= VarToStr(FieldValues(0)); cOrg:= tmpStr.ToInteger; lSQL:= 'SELECT TOP(1) 1 FROM ' + tblKZ + ' k INNER JOIN ' + tblKZe + ' ke ON (ke.ID=k.ID) WHERE ke._Naradi_ReworkOrg IS NOT NULL AND ke._Naradi_ReworkOrg=N' + tmpStr.QuotedString // + ' AND k.RegCis=N''710320''' + ' AND ISNULL(ke._Naradi_ReworkOrgSuff, N'''')<>N''0'' AND ISNULL(ke._Naradi_ReworkOrgSuff, N'''')<>N''''' + ' AND ((k.SkupZbo=N''054'' AND LEFT(k.RegCis,1)=N''7'') OR (k.SkupZbo=N''MNA'' AND LEFT(k.RegCis,1)=N''M''))'; if (helUtils.sqlExistsTestGeneral(Helios, lSQL)) and not(ContainsStr(tmpStr, '/')) then tmpStr:= '0' else tmpStr:= ''; mena:= VarToStr(FieldValues(2)); if (mena='CZK') or (mena='') then mena:= 'koruna'; if (mena='EUR') then mena:= 'euro'; if (mena='USD') then mena:= 'dolar'; tmpStr2:= cOrg.ToString + IfTHen(tmpStr<>'', '/' + tmpStr, tmpStr); lSQL:= 'DECLARE @s NVARCHAR(5)' + CRLF + 'SET @s=N' + tmpStr.QuotedString + CRLF + 'IF EXISTS(SELECT 1 FROM #TabMatrixSupps WHERE CisloOrg=' + cOrg.ToString + ')' + CRLF; if (tmpStr='') then lSQL:= lSQL + ' IF EXISTS(SELECT 1 FROM #TabMatrixSupps WHERE CisloOrg=' + cOrg.ToString + ' AND Suffix<>N'''')' + CRLF + ' SET @s=N''0'''; lSQL:= lSQL + ' INSERT #TabMatrixSupps (CisloOrg, Suffix, Nazev, Mena, Doprava) SELECT ' + cOrg.ToString + ', @s, N' + VarToStr(FieldValues(1)).QuotedString + ', N' + mena.QuotedString + ', N' + VarToStr(FieldValues(3)).QuotedString; Helios.ExecSQL(lSQL); Next; end; end; lSQL:= 'SELECT KplCisloOrg, Nazev, Doprava, Mena FROM #TabMatrixSupps ORDER BY KplCisloOrg'; with Helios.OpenSQL(lSQL) do begin First; i:= 1; while not(EOF) do begin shSupp.Cells.Item[1+i,1].Value:= VarToStr(FieldValues(0));; // cislo s moznym suffixem za lomitkem (napr. 11351/1) shSupp.Cells.Item[1+i,2].Value:= VarToStr(FieldValues(1)); // nazev shSupp.Cells.Item[1+i,3].Value:= VarToStr(FieldValues(2)); // doprava shSupp.Cells.Item[1+i,4].Value:= 'E-mail'; shSupp.Cells.Item[1+i,5].Value:= VarToStr(FieldValues(3)); // mena shSupp.Cells.Item[1+i,6].Value:= 'miroslav.simacek@kdynium.cz'; Inc(i); Next; end; end; Helios.ExecSQL('DROP TABLE IF EXISTS #TabMatrixSupps'); shItems:= xls.Sheets.Add; shItems.Name:= 'Items'; shItems.Cells.Item[1,1].Value:= 'ITEM CODE'; shItems.Cells.Item[1,2].Value:= 'DESCRIPTION'; shItems.Cells.Item[1,3].Value:= 'ADDITIONAL_ITEM_CODE'; shItems.Cells.Item[1,4].Value:= 'ITEM_LONG_DESCRIPTION'; shItems.Cells.Item[1,5].Value:= 'TYPE_CODE'; shItems.Cells.Item[1,6].Value:= 'IS_CALC_LEVEL'; shItems.Cells.Item[1,7].Value:= 'GROUP_CODE'; shItems.Cells.Item[1,8].Value:= ''; shItems.Cells.Item[1,9].Value:= 'PACKET_SIZE'; shItems.Cells.Item[1,10].Value:= 'DEFAULT_ISSUE_QTY'; shItems.Cells.Item[1,11].Value:= 'CATEGORY_CODE'; shItems.Cells.Item[1,12].Value:= 'UPC'; shItems.Cells.Item[1,13].Value:= 'SUPPLIER_CODE'; shItems.Cells.Item[1,14].Value:= 'SUPPLIER_PRICE'; shItems.Cells.Item[1,15].Value:= 'REWORK_PRICE'; shItems.Cells.Item[1,16].Value:= 'PACKAGE_SIZE'; shItems.Cells.Item[1,17].Value:= 'MIN_ORDER_QTY'; lSQL:= 'SELECT k.RegCis, k.Nazev1, k.Nazev2, k.SKP, ISNULL(bc.BarCode,N''''), ISNULL(k.Aktualni_Dodavatel,-1), ISNULL(ke._Naradi_Objednavat,0)' + ', k.Minimum_Baleni_Dodavatel, k.ID, so.KatAllTecky, ISNULL(ke._Naradi_Kategorie,0), ISNULL(ke._Naradi_TechnologickePouziti, N''''), ISNULL(co.Mena,N''CZK'')' + ', ISNULL(ke._Naradi_TypPolozky,1), ISNULL(ke._Naradi_ReworkOrg, -1), ISNULL(ke._Naradi_ReworkOrgSuff, N''''), ISNULL(co2.Mena,N''CZK'')' + ', ISNULL(ke._Matrix_MenaNakup, N''''), ISNULL(ke._Matrix_MenaRework, N'''') ' + ' FROM ' + tblKZ + ' k LEFT JOIN ' + tblBCode + ' bc ON (k.ID=bc.IdKmenZbo AND bc.Prednastaveno=1) LEFT JOIN ' + tblKZe + ' ke ON (ke.ID=k.ID) LEFT JOIN ' + tblSS + ' s ON (s.IDKmenZbozi=k.ID AND s.IDSklad=N' + QuotedStr(sklMatrix) + ') LEFT JOIN ' + tblSortim + ' so ON (so.ID=k.IdSortiment) LEFT JOIN ' + tblCOrg + ' co ON (co.CisloOrg=k.Aktualni_Dodavatel)' + ' LEFT JOIN ' + tblCOrg + ' co2 ON (co2.CisloOrg=ke._Naradi_ReworkOrg)' + ' WHERE k.SkupZbo=N''054'' AND LEFT(k.RegCis,1)=N''7''' // + ' AND k.RegCis IN (N''711467'', N''711466'')' + ' ORDER BY k.RegCis'; with Helios.OpenSQL(lSQL) do begin i:=RecordCount; waitStart(nil, 'Export položek', i, clRed); First; i:= 1; while not(EOF) do begin if (i mod 10=0) then waitSetProgBar(i); idKZ:= StrToInt(VarToStr(FieldValues(8))); sTypPol:= 'E'; iTypPol:= StrToInt(VarToStr(FieldValues(13))); case iTypPol of 1: sTypPol:= 'E'; 2: sTypPol:= 'R'; 3: sTypPol:= 'D'; 5: sTypPol:= 'Y'; 6: sTypPol:= 'G'; end; if (VarToStr(FieldValues(14))<>'-1') then begin shItems.Cells.Item[1+i,1].Value:= VarToStr(FieldValues(0)); shItems.Cells.Item[1+i,2].Value:= VarToStr(FieldValues(1)); shItems.Cells.Item[1+i,3].Value:= VarToStr(FieldValues(3)); shItems.Cells.Item[1+i,4].Value:= VarToStr(FieldValues(11)); shItems.Cells.Item[1+i,5].Value:= sTypPol; // typ polozky (obnovitelna, meridlo apod) shItems.Cells.Item[1+i,6].Value:= IfThen(StrToBool(VarToStr(FieldValues(6))),'1','0'); // objednavat shItems.Cells.Item[1+i,7].Value:= VarToStr(FieldValues(9)); // group code = sortiment shItems.Cells.Item[1+i,9].Value:= VarToStr(FieldValues(7)); // def. issue qty shItems.Cells.Item[1+i,10].Value:= VarToStr(FieldValues(7)); // def. issue qty shItems.Cells.Item[1+i,11].Value:= VarToStr(FieldValues(10)); // kategorie naradi (1=katalogova/2=specialni) shItems.Cells.Item[1+i,12].Value:= VarToStr(FieldValues(4)); // barcode / UPC shItems.Cells.Item[1+i,16].Value:= VarToStr(FieldValues(7)); // package size shItems.Cells.Item[1+i,17].Value:= VarToStr(FieldValues(7)); // min.mnoz tmpStr:= VarToStr(FieldValues(14)); // rework organizace idOrg:= helUtils.getHeliosIntVal(Helios, -1, 'SELECT ID FROM ' + tblCOrg + ' WHERE CisloOrg=' + tmpStr); if (VarToStr(FieldValues(15))<>'') then // suffix rework organizace tmpStr:= tmpStr + '/' + VarToStr(FieldValues(15)); shItems.Cells.Item[1+i,13].Value:= tmpStr; // supplier_code menaR:= ''; if (tmpStr.Contains('/')) then menaR:= helUtils.getHeliosStrVal(Helios, '', 'SELECT _Matrix_MenaRework FROM ' + tblCOrgE + ' WHERE ID=' + idOrg.ToString); if (menaR='') then begin menaR:= VarToStr(FieldValues(18)); // mena rework (na karte) if (menaR='') then menaR:= VarToStr(FieldValues(16)); // mena rework organizace end; if (menaR='CZK') then lSQL:= 'SELECT CenaKc FROM ' + tblNC + ' WHERE CenovaUroven=98 AND IdKmenZbozi=' + idKZ.toString else lSQL:= 'SELECT CenaVal1 FROM ' + tblNC + ' WHERE Mena1=N''EUR'' AND CenovaUroven=98 AND IdKmenZbozi=' + idKZ.toString; tmpStr:= helUtils.getHeliosStrVal(Helios, '', lSQL); shItems.Cells.Item[1+i,14].Value:= IfThen(tmpStr='', '0', tmpStr); shItems.Cells.Item[1+i,15].Value:= IfThen(tmpStr='', '0', tmpStr); Inc(i); end; shItems.Cells.Item[1+i,1].Value:= VarToStr(FieldValues(0)); shItems.Cells.Item[1+i,2].Value:= VarToStr(FieldValues(1)); shItems.Cells.Item[1+i,3].Value:= VarToStr(FieldValues(3)); shItems.Cells.Item[1+i,4].Value:= VarToStr(FieldValues(11)); shItems.Cells.Item[1+i,5].Value:= sTypPol; // typ polozky (obnovitelna, meridlo apod) shItems.Cells.Item[1+i,6].Value:= IfThen(StrToBool(VarToStr(FieldValues(6))),'1','0'); // objednavat shItems.Cells.Item[1+i,7].Value:= VarToStr(FieldValues(9)); // group code = sortiment shItems.Cells.Item[1+i,9].Value:= VarToStr(FieldValues(7)); // def. issue qty shItems.Cells.Item[1+i,10].Value:= VarToStr(FieldValues(7)); // def. issue qty shItems.Cells.Item[1+i,11].Value:= VarToStr(FieldValues(10)); // kategorie naradi (1=katalogova/2=specialni) shItems.Cells.Item[1+i,12].Value:= VarToStr(FieldValues(4)); // barcode / UPC tmpStr:= VarToStr(FieldValues(5)); // HEO akt. dodavatel shItems.Cells.Item[1+i,13].Value:= tmpStr; // dodavatel idOrg:= helUtils.getHeliosIntVal(Helios, -1, 'SELECT ID FROM ' + tblCOrg + ' WHERE CisloOrg=' + tmpStr); lSQL:= 'SELECT TOP(1) 1 FROM ' + tblKZ + ' k INNER JOIN ' + tblKZe + ' ke ON (ke.ID=k.ID) WHERE ke._Naradi_ReworkOrg IS NOT NULL AND ke._Naradi_ReworkOrg=N' + IfThen(ContainsStr(tmpStr, '/'), LeftStr(tmpStr, tmpStr.IndexOf('/')), tmpStr).QuotedString + ' AND ISNULL(ke._Naradi_ReworkOrgSuff, N'''') NOT IN (N'''', N''0'') AND ((k.SkupZbo=N''054'' AND LEFT(k.RegCis,1)=N''7'') OR (k.SkupZbo=N''MNA'' AND LEFT(k.RegCis,1)=N''M''))'; if (helUtils.sqlExistsTestGeneral(Helios, lSQL)) then tmpStr:= IfThen(ContainsStr(tmpStr, '/'), LeftStr(tmpStr, tmpStr.IndexOf('/')), tmpStr) + '/0'; shItems.Cells.Item[1+i,13].Value:= tmpStr; // dodavatel mena:= ''; if not(tmpStr.Contains('/')) then mena:= helUtils.getHeliosStrVal(Helios, '', 'SELECT _Matrix_MenaNakup FROM ' + tblCOrgE + ' WHERE ID=' + idOrg.ToString); if (mena='') then begin mena:= VarToStr(FieldValues(17)); // mena dodavatel/nakup (na karte) if (mena='') then mena:= VarToStr(FieldValues(12)); // mena dodavatel/nakup end; if (mena='CZK') then lSQL:= 'SELECT CenaKc FROM ' + tblNC + ' WHERE CenovaUroven=99 AND IdKmenZbozi=' + idKZ.toString else lSQL:= 'SELECT CenaVal1 FROM ' + tblNC + ' WHERE Mena1=N''EUR'' AND CenovaUroven=99 AND IdKmenZbozi=' + idKZ.toString; tmpStr:= helUtils.getHeliosStrVal(Helios, '', lSQL); shItems.Cells.Item[1+i,14].Value:= IfThen(tmpStr='', '0', tmpStr); if (VarToStr(shItems.Cells.Item[i,15].Value)<>'') and (VarToStr(shItems.Cells.Item[i,1].Value)=VarToStr(shItems.Cells.Item[1+i,1].Value)) then shItems.Cells.Item[1+i,15].Value:= shItems.Cells.Item[i,15].Value; shItems.Cells.Item[1+i,16].Value:= VarToStr(FieldValues(7)); // package size shItems.Cells.Item[1+i,17].Value:= VarToStr(FieldValues(7)); // min.mnoz Inc(i); Next; end; waitEnd; end; shSerials:= xls.Sheets.Add; shSerials.Name:= 'Serials'; shSerials.Cells.Item[1,1].Value:= 'ITEM CODE'; shSerials.Cells.Item[1,2].Value:= 'SERIAL_NUMBER'; shSerials.Cells.Item[1,3].Value:= 'NEXT_CALIB_DATE'; lSQL:= 'SELECT k.RegCis, vck.Nazev1, vcke._DatumKalibrace, vcke._SkutecnyPopis FROM ' + tblKZ + ' k INNER JOIN ' + tblVyrCK + ' vck ON (vck.IDKmenZbozi=k.ID)'; lSQL:= lSQL + ' INNER JOIN dbo.TabVyrCK_EXT vcke ON (vcke.ID=vck.ID) WHERE k.SkupZbo=N''MNA'' ORDER BY k.RegCis'; // lSQL:= 'SELECT k.RegCis, vcs.Nazev1, qms.ID FROM ' + tblKZ + ' k INNER JOIN ' + tblSS + ' s ON (s.IDKmenZbozi=k.ID) INNER JOIN ' + tblVyrCS; // lSQL:= lSQL + ' vcs ON (vcs.IDStavSkladu=s.ID) INNER JOIN ' + tblKJ + ' qms ON (qms.IDKmenZbozi=k.id AND qms.Kategorie=N''975'') WHERE s.IDSklad=N' + QuotedStr(sklMatrix); // lSQL:= lSQL + ' AND k.SkupZbo=N''MNA'' ORDER BY k.RegCis'; with Helios.OpenSQL(lSQL) do begin First; i:= 1; while not(EOF) do begin shSerials.Cells.Item[1+i,1].Value:= VarToStr(FieldValues(0)); shSerials.Cells.Item[1+i,2].Value:= VarToStr(FieldValues(1)); shSerials.Cells.Item[1+i,3].Value:= VarToStr(FieldValues(2)); Inc(i); Next; end; end; finally if (DirectoryExists(cestaExport,true)) then xls.SaveAs(cestaExport + '\Import_KDYNIUM_HELIOS.xlsx', xlOpenXMLWorkbook); if not FileExists(cestaExport + '\Import_KDYNIUM_HELIOS.xlsx') then xls.SaveAs(ExtractFilePath(GetModuleName(HInstance)) + 'Import_KDYNIUM_HELIOS.xlsx', xlOpenXMLWorkbook); // FreeAndNil(xls); end; end; procedure TplgKdynium.ImportKmenMeridla (const Helios: IHelios); var lSQL, radky, zaznam, msg, errMsg, sTemp: String; cnt, sl, posunSl, idxR, idxS, idKZ, cntIns, cntAkt, overRadek: integer; arrRadky: TArray>; kmen: TKmen; cont: Boolean; begin posunSl:= 2; overRadek:= 0; if (Clipboard.HasFormat(CF_TEXT)) then begin errMsg:= ''; cont:= true; radky:= ''; try radky:= Clipboard.AsText; sl:= PosCount(#9, LeftStr(radky, Pos(#13, radky)-1)) + 1; // pocet sloupcu except on E:Exception do cont:= false; end; if (radky<>'') and (cont) then begin cont:= true; cntIns:= 0; cntAkt:= 0; cnt:= Length(radky)-Length(StringReplace(radky, Chr(13),'',[rfReplaceAll]))+1; SetLength(arrRadky, cnt, sl); // index od 0 if (Pos(#13, radky)>0) then zaznam:= LeftStr(radky, Pos(#13, radky)-1) // nazvy sloupcu else zaznam:= radky; if Helios.YesNo('Má importovaná oblast hlavičku ?', false) then begin Delete(radky,1,Pos(#13,radky)+1); // smaze hlavicku SetLength(arrRadky,Length(arrRadky)-1); end; for idxR:=0 to High(arrRadky)-1 do begin if (Pos(#13,radky)>0) then zaznam:= LeftStr(radky,Pos(#13,radky)-1) else zaznam:= radky; zaznam:= Trim(StringReplace(zaznam, #9#9, #9' '#9,[rfReplaceAll])); if (zaznam<>'') then for idxS:=0 to sl-1 do begin if (zaznam<>'') then begin if (Pos(#9, zaznam)>0) then begin arrRadky[idxR,idxS]:= LeftStr(zaznam, Pos(#9, zaznam)-1); Delete(zaznam,1,Pos(#9, zaznam)); end else begin arrRadky[idxR,idxS]:= zaznam; zaznam:= ''; end; end; end; Delete(radky,1,Pos(#13, radky)+1); end; waitStart(nil, 'Import dat karet měřidel', Length(arrRadky), clRed); for idxR:=0 to High(arrRadky)-1 do begin if (idxR mod 10=0) then waitSetProgBar(idxR); try idKZ:= 0; clearKmen(kmen); { if Trim(arrRadky[idxR, 0])<>Trim(arrRadky[idxR, 1]) then kmen.sz:= Trim(arrRadky[idxR, 0]); if (Length(kmen.sz)=2) then kmen.sz:= '0' + kmen.sz; } kmen.sz:= 'MNA'; kmen.regCis:= Trim(arrRadky[idxR, posunSl + 1]); kmen.nazev1:= Trim(arrRadky[idxR, posunSl + 2]); if (kmen.regCis='') or (kmen.nazev1='') then continue; kmen.nazev3:= Trim(arrRadky[idxR, posunSl + 9]); kmen.skp:= Trim(arrRadky[idxR, posunSl + 3]); kmen.sortiment:= Trim(arrRadky[idxR, posunSl + 5]); if (kmen.nazev3.ToLower='katalogová') then kmen.kateg:= 1; if (kmen.nazev3.ToLower='speciální') then kmen.kateg:= 2; kmen.nazev3:= ''; if (Trim(arrRadky[idxR, posunSl + 10]).ToLower='spotřební') then kmen.typ:= 1; if (Trim(arrRadky[idxR, posunSl + 10]).ToLower='obnovitelná') then kmen.typ:= 2; if (Trim(arrRadky[idxR, posunSl + 10]).ToLower='trvalá') then kmen.typ:= 3; if (LeftStr(Trim(arrRadky[idxR, posunSl + 10]).ToLower,4)='klíč') then kmen.typ:= 5; if (Trim(arrRadky[idxR, posunSl + 10]).ToLower='měřidlo') then kmen.typ:= 6; TryStrToInt(Trim(arrRadky[idxR, posunSl + 11]), kmen.cOrgDod); TryStrToInt(Trim(arrRadky[idxR, posunSl + 13]), kmen.cOrgVyr); TryStrToInt(Trim(arrRadky[idxR, posunSl + 15]), kmen.dodLhuta); TryStrToInt(Trim(arrRadky[idxR, posunSl + 16]), kmen.lhutaNaskl); kmen.mj:= Trim(arrRadky[idxR, posunSl + 17]); kmen.mena:= LowerCase(Trim(arrRadky[idxR, posunSl + 18])); TryStrToFloat(StringReplace(Trim(arrRadky[idxR, posunSl + 19]),' ','',[rfReplaceAll]), kmen.jcena); TryStrToInt(Trim(arrRadky[idxR, posunSl + 23]), kmen.minDod); TryStrToInt(Trim(arrRadky[idxR, posunSl + 24]), kmen.minOdb); TryStrToInt(Trim(arrRadky[idxR, posunSl + 28]), kmen.minMnoz); TryStrToInt(Trim(arrRadky[idxR, posunSl + 29]), kmen.maxMnoz); kmen.barcode:= Trim(arrRadky[idxR, posunSl + 30]); kmen.obj:= arrRadky[idxR, posunSl + 7].ToBoolean(); with Helios.OpenSQL('SELECT ID FROM ' + tblKZ + ' WHERE SkupZbo=N' + QuotedStr(kmen.sz) + ' AND RegCis=N' + QuotedStr(kmen.regCis)) do if (RecordCount=1) then idKZ:= StrToInt(VarToStr(FieldValues(0))); if (idKZ=0) then begin { lSQL:= 'INSERT ' + tblKZ + ' (DruhSkladu, SkupZbo, RegCis, Nazev1' + IfThen(kmen.nazev2<>'', ', Nazev2', '') + IfThen(kmen.nazev3<>'', ', Nazev3', '') + IfThen(kmen.mj<>'', ', MJEvidence', ''); lSQL:= lSQL + IfThen(kmen.skp<>'', ', SKP', '') + IfThen(kmen.sortiment<>'', ', IdSortiment', '') + IfThen(kmen.cOrgDod<>0, ', Aktualni_Dodavatel', ''); lSQL:= lSQL + IfThen(kmen.cOrgVyr<>0, ', Vyrobce', '') + IfThen(kmen.minDod<>0, ', Minimum_Baleni_Dodavatel', '') + IfThen(kmen.dodLhuta<>0, ', DodaciLhuta', ''); lSQL:= lSQL + ') SELECT 1, N' + QuotedStr(kmen.sz) + ', N' + QuotedStr(kmen.regCis); lSQL:= lSQL + ', N' + QuotedStr(kmen.nazev1) + IfThen(kmen.nazev2<>'', ', N' + QuotedStr(kmen.nazev2), '') + IfThen(kmen.nazev3<>'', ', N' + QuotedStr(kmen.nazev3), '') + IfThen(kmen.mj<>'', ', N' + QuotedStr(kmen.mj), ''); lSQL:= lSQL + IfThen(kmen.skp<>'', ', N' + QuotedStr(kmen.skp), '') + IfThen(kmen.sortiment<>'', ', ISNULL( (SELECT ID FROM ' + tblSortim + ' WHERE KatAllTecky=N' + QuotedStr(kmen.sortiment) + '), NULL)', ''); lSQL:= lSQL + IfThen(kmen.cOrgDod<>0, ', ' + kmen.cOrgDod.ToString, '') + IfThen(kmen.cOrgVyr<>0, ', ' + kmen.cOrgVyr.ToString, '') + IfThen(kmen.minDod<>0, ', ' + kmen.minDod.ToString, ''); lSQL:= lSQL + IfThen(kmen.dodLhuta<>0, ', ' + kmen.dodLhuta.ToString, '') + CRLF; lSQL:= lSQL + 'SELECT SCOPE_IDENTITY()'; try with Helios.OpenSQL(lSQL) do idKZ:= StrToInt(VarToStr(FieldValues(0))); Inc(cntIns); except on E:Exception do begin Helios.Error(#1'Chyba INS: ' + e.Message +#1); idKZ:= 0; end; end; } lSQL:= 'DECLARE @idKZ INT; EXEC @idKZ=dbo.hp_VytvorPolozkuKmeneZbozi @SZ=N' + kmen.sz.QuotedString + ', @RegCis=N' + kmen.regCis.QuotedString; lSQL:= lSQL + ', @Nazev1=N' + kmen.nazev1.QuotedString + IfThen(kmen.nazev2<>'', ', @Nazev2=N' + kmen.nazev2.QuotedString, ''); lSQL:= lSQL + IfThen(kmen.nazev3<>'', ', @Nazev3=N' + kmen.nazev3.QuotedString, '') + IfThen(kmen.mj<>'', ', @MJEv=N' + kmen.mj.QuotedString, ''); lSQL:= lSQL + IfThen(kmen.skp<>'', ', @SKP=N' + kmen.skp.QuotedString, '') + '; SELECT @idKZ'; try with Helios.OpenSQL(lSQL) do idKZ:= StrToInt(VarToStr(FieldValues(0))); Inc(cntIns); except on E:Exception do Helios.Error(#1'Chyba INS: ' + e.Message +#1); end; if (idKZ>0) then begin if (kmen.cOrgDod<>0) then Helios.ExecSQL('UPDATE ' + tblKZ + ' SET Aktualni_Dodavatel=' + kmen.cOrgDod.ToString + ' WHERE ID=' + idKZ.ToString); if (kmen.cOrgVyr<>0) then Helios.ExecSQL('UPDATE ' + tblKZ + ' SET Vyrobce=' + kmen.cOrgVyr.ToString + ' WHERE ID=' + idKZ.ToString); if (kmen.sortiment<>'') then Helios.ExecSQL('UPDATE ' + tblKZ + ' SET IdSortiment=ISNULL( (SELECT ID FROM ' + tblSortim + ' WHERE KatAllTecky=N' + kmen.sortiment.QuotedString + '), NULL) WHERE ID=' + idKZ.ToString); if (kmen.minDod<>0) then Helios.ExecSQL('UPDATE ' + tblKZ + ' SET Minimum_Baleni_Dodavatel=' + kmen.minDod.ToString + ' WHERE ID=' + idKZ.ToString); if (kmen.dodLhuta<>0) then Helios.ExecSQL('UPDATE ' + tblKZ + ' SET DodaciLhuta=' + kmen.dodLhuta.ToString + ' WHERE ID=' + idKZ.ToString); end; end else begin lSQL:= 'UPDATE ' + tblKZ + ' SET Nazev1=N' + QuotedStr(kmen.nazev1) + ', Nazev2=N' + QuotedStr(kmen.nazev2) + ', Nazev3=N' + QuotedStr(kmen.nazev3) + ', SKP=N' + QuotedStr(kmen.skp); lSQL:= lSQL + IfThen(kmen.sortiment<>'', ', IdSortiment=ISNULL( (SELECT ID FROM ' + tblSortim + ' WHERE KatAllTecky=N' + QuotedStr(kmen.sortiment) + '), NULL)', ''); lSQL:= lSQL + ' WHERE ID=' + idKZ.ToString; Helios.ExecSQL(lSQL); Inc(cntAkt); end; if (idKZ>0) then begin lSQL:= 'IF NOT EXISTS(SELECT id FROM ' + tblNC + ' WHERE CenovaUroven=99 AND IDKmenZbozi=' + idKZ.ToString + ') INSERT ' + tblNC + ' (CenovaUroven, IDKmenZbozi) VALUES (99, ' + idKZ.ToString + ')'; Helios.ExecSQL(lSQL); if (kmen.mena='kč') or (kmen.mena='czk') then Helios.ExecSQL('UPDATE ' + tblNC + ' SET CenaKC=' + StringReplace(kmen.jcena.ToString, ',', '.',[rfReplaceAll]) + ' WHERE CenovaUroven=99 AND IDKmenZbozi=' + idKZ.ToString); if (kmen.mena='€') or (kmen.mena='eur') then begin lSQL:= 'IF NOT EXISTS(SELECT id FROM ' + tblNC + ' WHERE Mena1=N''EUR'' AND CenovaUroven=99 AND IDKmenZbozi=' + idKZ.ToString + ') '; Helios.ExecSQL(lSQL + 'UPDATE ' + tblNC + ' SET Mena1=N''EUR'' WHERE CenovaUroven=99 AND IDKmenZbozi=' + idKZ.ToString); Helios.ExecSQL('UPDATE ' + tblNC + ' SET CenaVal1=' + StringReplace(kmen.jcena.ToString, ',', '.',[rfReplaceAll]) + ' WHERE ' + 'CenaVal1<>' + StringReplace(kmen.jcena.ToString, ',', '.',[rfReplaceAll]) + ' AND Mena1=N''EUR'' AND CenovaUroven=99 AND IDKmenZbozi=' + idKZ.ToString); end; if (kmen.barcode<>'') then begin lSQL:= 'IF NOT EXISTS(SELECT ID FROM ' + tblBCode + ' WHERE BarCode=N' + QuotedStr(kmen.barcode) + ') INSERT ' + tblBCode + ' (IDKmenZbo, Prednastaveno, BarCode)'; lSQL:= lSQL + ' SELECT ' + idKZ.ToString + ', 1, N' + QuotedStr(kmen.barcode); Helios.ExecSQL(lSQL); end; lSQL:= 'IF NOT EXISTS(SELECT ID FROM ' + tblKZe + ' WHERE ID=' + idKZ.ToString + ') INSERT ' + tblKZe + ' (ID) VALUES (' + idKZ.ToString + ')'; Helios.ExecSQL(lSQL); lSQL:= 'UPDATE ' + tblKZe + ' SET _Naradi_TypPolozky=' + IfThen(kmen.typ=0, 'NULL', kmen.typ.ToString) + ', _Naradi_Kategorie=' + kmen.kateg.ToString + ', _Naradi_Objednavat=' + kmen.obj.ToString(); lSQL:= lSQL + ' WHERE ID=' + idKZ.ToString; Helios.ExecSQL(lSQL); if (kmen.typ>0) then Helios.ExecSQL('UPDATE ' + tblKZ + ' SET Nazev3=N'''' WHERE id=' + idKZ.ToString); end; except on E:Exception do errMsg:= E.Message + CRLF + lSQL; end; end; waitEnd; Helios.Refresh(true); Helios.Info(#1'Nových karet: ' + cntIns.ToString + CRLF + 'Upravených karet: ' + cntAkt.ToString + #1); end; end; end; procedure TplgKdynium.ImportKmenMeridlaVC (const Helios: IHelios); var lSQL, radky, zaznam, msg, errMsg, sTemp: String; cnt, sl, posunSl, idxR, idxS, idKZ, idKVC, idVC, cntIns, cntAkt, overRadek: integer; arrRadky: TArray>; kmen: TKmen; cont, prvniPrazdne: Boolean; begin overRadek:= 0; if (Clipboard.HasFormat(CF_TEXT)) then begin errMsg:= ''; cont:= true; prvniPrazdne:= false; radky:= ''; try radky:= Clipboard.AsText; // if (LeftStr(radky,1)=#9) then // prvniPrazdne:= true; sl:= PosCount(#9, LeftStr(radky, Pos(#13, radky)-1)) + 1; // pocet sloupcu except on E:Exception do cont:= false; end; if (radky<>'') and (cont) then begin cont:= true; cntIns:= 0; cntAkt:= 0; cnt:= Length(radky)-Length(StringReplace(radky, Chr(13),'',[rfReplaceAll]))+1; SetLength(arrRadky, cnt, sl); // index od 0 if (Pos(#13, radky)>0) then zaznam:= LeftStr(radky, Pos(#13, radky)-1) // nazvy sloupcu else zaznam:= radky; if Helios.YesNo('Má importovaná oblast hlavičku ?', false) then begin Delete(radky,1,Pos(#13,radky)+1); // smaze hlavicku SetLength(arrRadky,Length(arrRadky)-1); end; for idxR:=0 to High(arrRadky)-1 do begin if (Pos(#13,radky)>0) then zaznam:= LeftStr(radky,Pos(#13,radky)-1) else zaznam:= radky; zaznam:= Trim(StringReplace(zaznam, #9#9, #9' '#9,[rfReplaceAll])); if (zaznam<>'') then for idxS:=0 to sl-1 do begin if (zaznam<>'') then begin if (Pos(#9, zaznam)>0) then begin arrRadky[idxR,idxS]:= LeftStr(zaznam, Pos(#9, zaznam)-1); Delete(zaznam,1,Pos(#9, zaznam)); end else begin arrRadky[idxR,idxS]:= zaznam; zaznam:= ''; end; end; end; Delete(radky,1,Pos(#13, radky)+1); end; posunSl:= 0; if (prvniPrazdne) then posunSl:= -1; waitStart(nil, 'Import dat karet výrobních čísel měřidel', Length(arrRadky), clRed); for idxR:=0 to High(arrRadky)-1 do begin if (idxR mod 10=0) then waitSetProgBar(idxR); try idKZ:= 0; idVC:= 0; clearKmen (kmen); { if Trim(arrRadky[idxR, 0])<>Trim(arrRadky[idxR, 1]) then kmen.sz:= Trim(arrRadky[idxR, 0]); if (Length(kmen.sz)=2) then kmen.sz:= '0' + kmen.sz; } kmen.sz:= 'MNA'; if (posunSl=-1) then posunSl:= 0; if (Trim(arrRadky[idxR, posunSl])<>'') then if (HeliosExistsTest(Helios, tblSZ, 'SkupZbo=N' + Trim(arrRadky[idxR, posunSl]).QuotedString)) then kmen.sz:= Trim(arrRadky[idxR, posunSl]) else if (posunSl<>-1) then posunSl:= -1; kmen.regCis:= Trim(arrRadky[idxR, posunSl + 1]); kmen.vyrCis:= Trim(arrRadky[idxR, posunSl + 2]); if (kmen.regCis='') or (kmen.vyrCis='') then continue; kmen.popisVC:= Trim(arrRadky[idxR, posunSl + 3]); kmen.skutPopis:= Trim(arrRadky[idxR, posunSl + 4]); TryStrToDate(Trim(arrRadky[idxR, posunSl + 5]), kmen.datKalibrace); kmen.umisteniVC:= Trim(arrRadky[idxR, posunSl + 7]); idKZ:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT ID FROM ' + tblKZ + ' WHERE SkupZbo=N' + kmen.sz.QuotedString + ' AND RegCis=N' + kmen.regCis.QuotedString); if (idKZ>0) then begin lSQL:= 'SELECT ID FROM ' + tblVyrCK + ' WHERE IDKmenZbozi=' + idKZ.ToString + ' AND Nazev1=N' + kmen.vyrCis.QuotedString; idKVC:= helUtils.getHeliosIntVal(Helios, 0, lSQL); if (idKVC=0) then begin lSQL:= 'DECLARE @i INT; EXEC dbo.hp_OZInsertVyrCK @IDVyrCK=@i OUT, @IDKmenZbozi=' + idKZ.ToString + ', @Selectem=0, @Nazev1=N' + kmen.vyrCis.QuotedString; lSQL:= lSQL + ', @Nazev2=N' + kmen.popisVC.QuotedString + '; SELECT @i'; try with Helios.OpenSQL(lSQL) do idKVC:= VarToStr(FieldValues(0)).ToInteger; if (kmen.umisteniVC<>'') then begin lSQL:= 'IF NOT EXISTS (SELECT 1 FROM ' + tblVyrCKE + ' WHERE ID=' + idKVC.ToString + ') INSERT ' + tblVyrCKE + ' (ID) SELECT ' + idKVC.ToString + CRLF; lSQL:= lSQL + 'UPDATE ' + tblVyrCKE + ' SET _Umisteni=LEFT(N' + kmen.umisteniVC.QuotedString + ', 50) WHERE ID=' + idKVC.ToString; Helios.ExecSQL(lSQL); end; Inc(cntIns); except on E:Exception do begin Helios.Error(#1'Chyba INS: ' + E.Message +#1); idKZ:= 0; idKVC:= 0; end; end; end else Inc(cntAkt); if (idKVC>0) then begin lSQL:= 'IF NOT EXISTS(SELECT ID FROM ' + tblVyrCKE + ' WHERE ID=' + idKVC.ToString + ') INSERT ' + tblVyrCKE + ' (ID) SELECT ' + idKVC.ToString + CRLF; if (kmen.skutPopis<>'') then lSQL:= lSQL + 'UPDATE ' + tblVyrCKE + ' SET _SkutecnyPopis=N' + QuotedStr(kmen.skutPopis) + ' WHERE ISNULL(_SkutecnyPopis,N'''')<>N' + QuotedStr(kmen.skutPopis) + ' AND ID=' + idKVC.ToString + CRLF; try if (DateToStr(kmen.datKalibrace)<>'') then lSQL:= lSQL + 'UPDATE ' + tblVyrCKE + ' SET _DatumKalibrace=CONVERT(datetime,N' + QuotedStr(DateToStr(kmen.datKalibrace)) + ',104) WHERE (_DatumKalibrace IS NULL OR _DatumKalibrace<>' + 'CONVERT(datetime,N' + QuotedStr(DateToStr(kmen.datKalibrace)) + ',104)) AND ID=' + idKVC.ToString; finally end; Helios.ExecSQL(lSQL); end; end else errMsg:= errMsg + 'Kmenová karta měřidla nenalezena: ' + kmen.regCis + CRLF; except on E:Exception do errMsg:= E.Message + CRLF + lSQL; end; end; waitEnd; Helios.Refresh(true); Helios.Info(#1'Nových karet VČ: ' + cntIns.ToString + CRLF + 'Upravených karet VČ: ' + cntAkt.ToString + #1); end; end; end; procedure TplgKdynium.ZakazkaZobrazAtestyTaveb(const Helios: IHelios; ids: TArray); var lSQL, idsX: string; bidRozbory, i: integer; begin if (Length(ids)>0) then begin bidRozbory:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT 100000+Cislo FROM ' + tblObecPrehled + ' WHERE NazevSys=N''hvw_Rozbory'''); if (bidRozbory>0) then begin // ID taveb idsX:= ''; lSQL:= 'IF OBJECT_ID(N''tempdb..#TabVybraneZaznamy'') IS NULL CREATE TABLE #TabVybraneZaznamy (ID INT NOT NULL, Hloubka TINYINT NOT NULL)'; Helios.ExecSQL(lSQL); for i:=Low(ids) to High(ids) do begin idsX:= idsX + ids[i].ToString + ', '; Helios.ExecSQL('INSERT #TabVybraneZaznamy (ID, Hloubka) SELECT ' + ids[i].ToString + ', 1'); end; if (RightStr(idsX,2)=', ') then idsX:= LeftStr(idsX, Length(idsX)-2); lSQL:= 'hvw_Rozbory.CisloTavby IN (SELECT CisloTavby FROM ' + tblTavby + ' WHERE ID IN (' + ArrayToString(ids, ',') + '))'; Helios.OpenBrowse(bidRozbory, lSQL); Helios.ExecSQL('DELETE FROM #TabVybraneZaznamy WHERE Hloubka=1'); end; end; end; procedure TplgKdynium.ZakazkaZobrazTavby(const Helios: IHelios; ids: TArray); var lSQL, idsX: string; bidTavby, i: integer; begin if (Length(ids)>0) then begin bidTavby:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT 100000+Cislo FROM ' + tblObecPrehled + ' WHERE NazevSys=N''hvw_TavbyPosledniData'''); if (bidTavby>0) then begin lSQL:= 'DROP TABLE IF EXISTS #TabVybraneZaznamy' + CRLF + 'CREATE TABLE #TabVybraneZaznamy (ID INT NOT NULL, Hloubka TINYINT NOT NULL)'; Helios.ExecSQL(lSQL); // ID zakazek idsX:= ''; for i:=Low(ids) to High(ids) do begin idsX:= idsX + ids[i].ToString + ', '; Helios.ExecSQL('INSERT #TabVybraneZaznamy (ID, Hloubka) SELECT ' + ids[i].ToString + ', 0'); end; if (RightStr(idsX,2)=', ') then idsX:= LeftStr(idsX, Length(idsX)-2); lSQL:= 'hvw_TavbyPosledniData.CisloTavby IN (SELECT DISTINCT(x.Tavba) FROM ('; lSQL:= lSQL + 'SELECT ee._Tavba AS Tavba FROM ' + tblMzdZmE + ' ee INNER JOIN ' + tblMzdZm + ' e ON (ee.ID=e.ID)'; lSQL:= lSQL + ' INNER JOIN ' + tblPrikaz + ' vp ON (vp.ID=e.IDPrikaz) INNER JOIN ' + tblKZ + ' k ON (k.ID=vp.IDTabKmen)'; lSQL:= lSQL + ' WHERE k.SkupZbo=N''P30'' AND vp.IDZakazka IN (' + helUtils.ArrayToString(ids, ',') + ')' + CRLF; lSQL:= lSQL + 'UNION ALL' + CRLF; lSQL:= lSQL + 'SELECT eex._Tavba AS Tavba FROM ' + tblMzdZmE + ' eex INNER JOIN ' + tblMzdZm + ' ex ON (eex.ID=ex.ID)'; lSQL:= lSQL + ' INNER JOIN ' + tblPrikaz + ' vpx ON (vpx.ID=ex.IDPrikaz) INNER JOIN ' + tblKZ + ' kx ON (kx.ID=vpx.IDTabKmen)'; lSQL:= lSQL + ' WHERE kx.SkupZbo=N''P30'' AND vpx.IDZakazka IN (SELECT IDZakazkaNizsi FROM ' + tblVazbyZak + ' WHERE IDZakazkaVyssi IN (' + helUtils.ArrayToString(ids, ',') + '))' + CRLF; lSQL:= lSQL + 'UNION ALL' + CRLF; lSQL:= lSQL + 'SELECT vcp.VyrCislo FROM ' + tblPrikazVC + ' vcp INNER JOIN ' + tblPrikaz; lSQL:= lSQL + ' vp ON (vp.ID=vcp.IDPrikaz) INNER JOIN ' + tblKZ + ' k ON (k.ID=vp.IDTabKmen)'; lSQL:= lSQL + ' WHERE k.SkupZbo=N''P30'' AND vp.IDZakazka IN (SELECT IDZakazkaNizsi FROM ' + tblVazbyZak + ' WHERE IDZakazkaVyssi IN (' + helUtils.ArrayToString(ids, ',') + '))' + CRLF; lSQL:= lSQL + 'UNION ALL' + CRLF; lSQL:= lSQL + 'SELECT vcp2.VyrCislo FROM ' + tblPrikazVC + ' vcp2 INNER JOIN ' + tblPrikaz; lSQL:= lSQL + ' vp2 ON (vp2.ID=vcp2.IDPrikaz) INNER JOIN ' + tblKZ + ' k2 ON (k2.ID=vp2.IDTabKmen)'; lSQL:= lSQL + ' WHERE k2.SkupZbo=N''P30'' AND vp2.IDZakazka IN (' + helUtils.ArrayToString(ids, ',') + ')) x)'; { lSQL:= 'hvw_TavbyPosledniData.CisloTavby IN (SELECT ee._Tavba FROM ' + tblMzdZmE + ' ee INNER JOIN ' + tblMzdZm + ' e ON (ee.ID=e.ID)'; lSQL:= lSQL + ' INNER JOIN ' + tblPrikaz + ' vp ON (vp.ID=e.IDPrikaz) INNER JOIN ' + tblKZ + ' k ON (k.ID=vp.IDTabKmen)'; lSQL:= lSQL + ' WHERE k.SkupZbo=N''P30'' AND vp.IDZakazka IN (' + helUtils.ArrayToString(ids, ',') + '))'; lSQL:= lSQL + ' OR hvw_TavbyPosledniData.CisloTavby IN (SELECT vcp.VyrCislo FROM ' + tblPrikazVC + ' vcp INNER JOIN ' + tblPrikaz; lSQL:= lSQL + ' vp ON (vp.ID=vcp.IDPrikaz) INNER JOIN ' + tblKZ + ' k ON (k.ID=vp.IDTabKmen)'; lSQL:= lSQL + ' WHERE k.SkupZbo=N''P30'' AND vp.IDZakazka IN (' + helUtils.ArrayToString(ids, ',') + '))'; } Helios.OpenBrowse(bidTavby, lSQL); Helios.ExecSQL('DELETE FROM #TabVybraneZaznamy WHERE Hloubka=0'); end; end; end; procedure TplgKdynium.ImportOperaciDoPripravyPrikazu(const Helios: IHelios); var lSQL, IDcka: string; mnoz: Extended; idKZ, bidP: integer; begin lSQL:= 'IF OBJECT_ID(N''tempdb..#ImportOperaciDoPrikazu'') IS NOT NULL DROP TABLE #ImportOperaciDoPrikazu' + CRLF; lSQL:= lSQL + 'IF OBJECT_ID(N''dbo._temp_ImportOperaciDoPrikazu'') IS NOT NULL DELETE FROM dbo._temp_ImportOperaciDoPrikazu WHERE Autor=SUSER_SNAME()' + CRLF; lSQL:= lSQL + 'IF OBJECT_ID(N''dbo._temp_ImportOperaciDoPrikazu'') IS NOT NULL IF NOT EXISTS(SELECT * FROM dbo._temp_ImportOperaciDoPrikazu)'; lSQL:= lSQL + ' DROP TABLE dbo._temp_ImportOperaciDoPrikazu'; Helios.ExecSQL(lSQL); // oVar1:= helUtils.getHeliosStrVal(Helios, 'SELECT TOP(1) p.ID FROM ' + tblPostup + ' p INNER JOIN #TabPrPostup pp ON (pp.Operace=p.Operace AND pp.Typ=p.Typ AND pp.dilec=p.Dilec)'); bidP:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT 100000+Cislo FROM ' + tblObecPrehled + ' WHERE NazevSys=N''hvw_TPVPostupy'''); if (bidP>0) then begin mnoz:= helUtils.getHeliosFloatVal(Helios, 1, 'SELECT TOP(1) Mnozstvi FROM #TabZmetkyNaOpravu'); idKZ:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT TOP(1) IDTabKmen FROM #TabZmetkyNaOpravu'); if (idKZ>0) then begin lSQL:= 'hvw_TPVPostupy.ID IN (SELECT p.ID FROM ' + tblPostup + ' p INNER JOIN ' + tblKZ + ' k ON (k.ID=p.dilec) WHERE LEFT(k.SkupZbo,2)=N''OO'' AND k.RegCis='; lSQL:= lSQL + '(SELECT TOP(1) k2.RegCis FROM #TabZmetkyNaOpravu z INNER JOIN ' + tblKZ + ' k2 ON (k2.ID=z.IDTabKmen)))'; if Helios.Prenos2(bidP, 'hvw_TPVPostupy.ID', 'hvw_TPVPostupy.ID', oVar1, oVar2, lSQL, 'Vyberte operace', false, true, false, 1) then begin IDcka:= VarToStr(oVar1); if (IDcka='') then IDcka:= '0'; lSQL:= 'CREATE TABLE #ImportOperaciDoPrikazu (Dilec INT NOT NULL, IDOperace INT NOT NULL, Mnozstvi NUMERIC(19,6) NOT NULL, Autor NVARCHAR(80) DEFAULT SUSER_SNAME())' + CRLF; lSQL:= lSQL + 'INSERT #ImportOperaciDoPrikazu (Dilec, IDOperace, Mnozstvi) SELECT ' + idKZ.ToString + ', ID, ' + mnoz.ToString + ' FROM ' + tblPostup + ' WHERE ID IN (' + IDcka + ')'; if (jeTest) then begin lSQL:= StringReplace(lSQL, '#ImportOp', 'dbo._temp_ImportOp', [rfReplaceAll]); lSQL:= StringReplace(lSQL, 'tempdb..', '', [rfReplaceAll]); end; Helios.ExecSQL(lSQL); if not(jeTest) then Helios.ExecSQL('IF OBJECT_ID(N''dbo.ep_VPr_Priprava_ImportOperaci'') IS NOT NULL EXEC dbo.ep_VPr_Priprava_ImportOperaci'); Helios.Refresh(true); end; end; end; end; procedure TplgKdynium.ImportObedy (const Helios: IHelios); var lSQL, defDir, fName, radek, cast1, cast2, cast3: string; radky: TStringList; i, i2, cisZam, pocetObedu: integer; datOd, datDo: string; obedy: TArray; begin defDir:= GetEnvironmentVariable('USERPROFILE') + PathDelim + 'Desktop'; if (helUtils.OtevriSoubor ('Vyberte exportovaný soubor', 'Textové soubory', '*.txt', defDir, fName)) then if (FileExists(fName)) then begin radky:= TStringList.Create; radky.LoadFromFile(fName); try try i:= 1; i2:= 0; SetLength(obedy, radky.Count*3); for radek in radky do begin if (i=3) then begin datOd:= MidStr(radek, 35, 8); datOd:= LeftStr(datOd, 6) + '20' + RightStr(datOd, 2); datDo:= MidStr(radek, 46, 8); datDo:= LeftStr(datDo, 6) + '20' + RightStr(datDo, 2); cast1:= MidStr(radek, 67, 5).Trim; cast2:= MidStr(radek, 76, 22); cast3:= MidStr(radek, 109, 2); if (TryStrToInt(cast1, cisZam)) and (TryStrToInt(cast3, pocetObedu)) then if (pocetObedu>0) then begin obedy[i2].cisZam:= cisZam; obedy[i2].pocetObedu:= StrToInt(cast3); Inc (i2); end; cast1:= MidStr(radek, 115, 5).Trim; cast2:= MidStr(radek, 124, 22); cast3:= MidStr(radek, 155, 3); if (TryStrToInt(cast1, cisZam)) and (TryStrToInt(cast3, pocetObedu)) then if (pocetObedu>0) then begin obedy[i2].cisZam:= cisZam; obedy[i2].pocetObedu:= StrToInt(cast3); Inc (i2); end; end else if (i>3) then begin cast1:= MidStr(radek, 4, 5).Trim; cast2:= MidStr(radek, 14, 22); cast3:= MidStr(radek, 61, 2); if (TryStrToInt(cast1, cisZam)) and (TryStrToInt(cast3, pocetObedu)) then if (pocetObedu>0) then begin obedy[i2].cisZam:= cisZam; obedy[i2].pocetObedu:= StrToInt(cast3); Inc (i2); end; cast1:= MidStr(radek, 67, 5).Trim; cast2:= MidStr(radek, 76, 22); cast3:= MidStr(radek, 109, 2); if (TryStrToInt(cast1, cisZam)) and (TryStrToInt(cast3, pocetObedu)) then if (pocetObedu>0) then begin obedy[i2].cisZam:= cisZam; obedy[i2].pocetObedu:= StrToInt(cast3); Inc (i2); end; cast1:= MidStr(radek, 115, 5).Trim; cast2:= MidStr(radek, 124, 22); cast3:= MidStr(radek, 155, 3); if (TryStrToInt(cast1, cisZam)) and (TryStrToInt(cast3, pocetObedu)) then if (pocetObedu>0) then begin obedy[i2].cisZam:= cisZam; obedy[i2].pocetObedu:= StrToInt(cast3); Inc (i2); end; end; Inc(i); end; except end; finally radky.Free; end; lSQL:= 'DROP TABLE IF EXISTS #TabImpObedy' + CRLF + 'CREATE TABLE #TabImpObedy (CisloZam INT NOT NULL, PocetObedu INT NOT NULL)'; Helios.ExecSQL (lSQL); for i:=0 to Length(obedy)-1 do if (obedy[i].cisZam>0) and (obedy[i].pocetObedu>0) then Helios.ExecSQL ('INSERT #TabImpObedy (CisloZam, PocetObedu) SELECT ' + obedy[i].cisZam.ToString + ', ' + obedy[i].pocetObedu.ToString); lSQL:= 'IF OBJECT_ID(N''dbo.hpx_HDC_Mzdy_PredzpracImportObedyJobAbacus'', N''P'') IS NOT NULL EXEC dbo.hpx_HDC_Mzdy_PredzpracImportObedyJobAbacus @datumOd=N' + datOd.QuotedString; Helios.ExecSQL (lSQL); end; end; procedure TplgKdynium.RozpRezii_PseudoAutomat(const Helios: IHelios; const arrID: TArray); var lSQL, nazRozp, vstStr, denikIDs, utvarVst, cisStredOper: string; idPreuct, idx, rozpNr, idRozpRez, idObd, mObd, rObd: integer; ctiZak, ctiUtvar: Boolean; celkemHod, zakazkaHod: extended; arrVstStr: TArray; begin if (Length(arrID)>0) then begin idObd:= 0; with Helios.OpenSQL('IF OBJECT_ID(N''tempdb..#TabObdobiRozpRez'') IS NOT NULL SELECT IdObd FROM #TabObdobiRozpRez ELSE SELECT 0') do idObd:= VarToStr(FieldValues(0)).ToInteger; if (idObd=0) then begin idx:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT DPBID FROM ' + tblObecPrehled + ' WHERE NazevSys=N''hvw_ObdobiStavu'''); if (idx>0) then if Helios.Prenos(idx, 'hvw_ObdobiStavu.ID', oVar, 'DATEDIFF(year, hvw_ObdobiStavu.DatumOd, GETDATE()) BETWEEN 0 AND 1', 'Vyberte období M/R', true) then idObd:= StrToInt(VarToStr(oVar)); end; cisStredOper:= ''; if (Helios.Prenos(bidStrom, 'Cislo', oVar, 'TabStrom.TypStrediska=1', 'Vyberte Výrobní středisko', true)) then cisStredOper:= oVar; for rozpNr:=0 to Length(arrID)-1 do begin idRozpRez:= arrID[rozpNr]; if ((idRozpRez>0) and (idObd>0)) then begin with Helios.OpenSQL('SELECT DATEPART(month, DatumOd), DATEPART(year, DatumOd) FROM hvw_ObdobiStavu WHERE Id=' + idObd.ToString) do begin mObd:= VarToStr(FieldValues(0)).ToInteger; rObd:= VarToStr(FieldValues(1)).ToInteger; end; // with Helios.OpenSQL('SELECT Id FROM ' + tblPreuctRez + ' WHERE IdFIARozpousteniRezii=' + IntToStr(idRozpRez)) do // idPreuct:= StrToInt(VarToStr(FieldValues(0))); ctiZak:= getHeliosBoolVal(Helios, false, 'SELECT VystupZakazky FROM ' + tblRozpRez + ' WHERE Id=' + idRozpRez.ToString); ctiUtvar:= getHeliosBoolVal(Helios, false, 'SELECT VystupUtvary FROM ' + tblRozpRez + ' WHERE Id=' + idRozpRez.ToString); vstStr:= ''; { if (ctiUtvar or ctiZak) then begin lSQL:= 'SELECT STRING_AGG( '; lSQL:= lSQL + IfThen(ctiUtvar, 'ISNULL(Utvar, N'''')', ''); lSQL:= lSQL + IfThen(ctiZak, IfThen(ctiUtvar, ' + IIF(Utvar IS NOT NULL, N''/'', N'''') + ', '') + 'ISNULL(CisloZakazky,N'''')',''); lSQL:= lSQL + ', N'','') FROM ' + tblRozpRezDet + ' WHERE IdFIARozpusteniRezii=' + idRozpRez.ToString; with Helios.OpenSQL(lSQL) do if (RecordCount=1) then vstStr:= VarToStr(FieldValues(0)); end; SetLength(arrVstStr, PosCount(',',vstStr)+1); arrVstStr:= vstStr.Split([',']); // StringToStringArray(vstStr,','); } if (idObd>0) then begin waitStart(nil, 'Zjišťuji podíly evidovaných hodin práce' + IfThen(cisStredOper='', '', ' pracovišť střediska ' + cisStredOper), 1, clRed); lSQL:= 'SELECT ISNULL(SUM(pmz.Nor_cas_H), 0) FROM ' + tblPMZ + ' pmz INNER JOIN ' + tblPrikaz + 'p ON (p.ID=pmz.IDPrikaz) INNER JOIN ' + tblZak; lSQL:= lSQL + ' z ON (p.IDZakazka=z.ID) INNER JOIN ' + tblPrPost + ' pp ON (pp.IDPrikaz=pmz.IDPrikaz AND pp.Doklad=pmz.DokladPrPostup AND pp.Alt=pmz.AltPrPostup'; lSQL:= lSQL + ' AND pp.IDOdchylkyDo IS NULL) INNER JOIN ' + tblCPrac + ' pr ON (pr.ID=pmz.IDPracoviste) WHERE DATEPART(month, pmz.Datum)=' + mObd.ToString; lSQL:= lSQL + ' AND DATEPART(year, pmz.Datum)=' + rObd.ToString + ' AND pp.typ=1' + IfThen(cisStredOper='', '', ' AND pr.IDTabStrom=N' + cisStredOper.QuotedString); celkemHod:= helUtils.getHeliosFloatVal(Helios, 0, lSQL); lSQL:= 'SELECT DISTINCT(z.CisloZakazky), ' + idRozpRez.ToString + ', 100*ISNULL(SUM(pmz.Nor_cas_H),0)/' + celkemHod.ToString.Replace(',','.') + ' FROM ' + tblPMZ + ' pmz INNER JOIN '; lSQL:= lSQL + tblPrikaz + 'p ON (p.ID=pmz.IDPrikaz) INNER JOIN ' + tblZak; lSQL:= lSQL + ' z ON (p.IDZakazka=z.ID) INNER JOIN ' + tblPrPost + ' pp ON (pp.IDPrikaz=pmz.IDPrikaz AND pp.Doklad=pmz.DokladPrPostup AND pp.Alt=pmz.AltPrPostup'; lSQL:= lSQL + ' AND pp.IDOdchylkyDo IS NULL) INNER JOIN ' + tblCPrac + ' pr ON (pr.ID=pmz.IDPracoviste) WHERE DATEPART(month, pmz.Datum)=' + mObd.ToString; lSQL:= lSQL + ' AND DATEPART(year, pmz.Datum)=' + rObd.ToString + ' AND pp.typ=1' + IfThen(cisStredOper='', '', ' AND pr.IDTabStrom=N' + cisStredOper.QuotedString) + ' GROUP BY z.CisloZakazky'; Helios.ExecSQL('DELETE FROM ' + tblRozpRezDet + ' WHERE IdFIARozpusteniRezii=' + idRozpRez.ToString); helUtils.ReseedTable(Helios, tblRozpRezDet); Helios.ExecSQL('INSERT ' + tblRozpRezDet + ' (CisloZakazky, IdFIARozpusteniRezii, Podil) ' + lSQL); waitEnd; { with Helios.OpenSQL(lSQL) do if (RecordCount>0) then begin Helios.ExecSQL('DELETE FROM ' + tblRozpRezDetVst + ' WHERE IdFIARozpusteniRezii=' + idRozpRez.ToString); end else Helios.Error(#1'Ve vybraném období nejsou evidovány žádné operace.'#1); } end; // idObd>0 end; // idRozpRez>0 end; // for rozpNr end; Helios.Refresh(true); end; procedure TplgKdynium.VytvorTempExtKomPar (const Helios: IHelios; id: integer); var lSQL: string; begin lSQL:= 'IF OBJECT_ID(N''tempdb..#TabExtKomPar'', ''U'') IS NULL CREATE TABLE #TabExtKomPar (Popis NVARCHAR(10) NULL, Cislo NUMERIC(19,6) NULL)' + CRLF; lSQL:= lSQL + 'DELETE FROM #TabExtKomPar' + CRLF + 'INSERT #TabExtKomPar (Popis, Cislo) SELECT N''STVlastID'', ' + id.ToString; Helios.ExecSQL(lSQL); end; procedure TplgKdynium.Run (const Helios: IHelios); const MinVerzeHelios = $030020250106; var typAkce: byte; browID, cRec, cntID, l_loop, idDZ, dpz, cOrg, newBid: integer; lSQL, autor, radDokl, IDcka, params, paramsBak, vlastPar, vlastPar2, contInfo, sz, podm, sTemp: string; iTemp, iTemp2, iTemp3: integer; nTemp, nTemp2, nTemp3, nTemp4: Extended; arrId: TArray; fGenPolos: TformGenPolos; fOdlitek: TformOdlitek; fGenTPV: TformGenTPV; fOdvTavba: TformOdvTavby; fOdvLis: TformOdvLis; fDavObec: TformDavkyObecne; fEvidOp: TformEvidOper; fHrOdvOp: TformHrOdvOper; fSchvalZak: TformSchvaleniZak; fOOPPohyb: TformOOPPohyb; term, canCont, plusJeden: boolean; begin {$IFDEF MadExcept} MESettings.BugReportFile:= GetEnvironmentVariable('USERPROFILE') + PathDelim + 'Desktop' + PathDelim + 'MadExReport.txt'; MESettings.AutoSave:= true; MESettings.BugReportFileSize:= 1024 * 200; MESettings.GeneralNoBugReport:= false; {$ENDIF} term:= false; jeTest:= false; if (Helios.HeVersion0 then begin typAkce:= StrToInt(LeftStr(params,Pos(';',params)-1)); params:= MidStr(params,Pos(';',params)+1,255); if Pos(';',params)>0 then browID:= StrToInt(LeftStr(params,Pos(';',params)-1)) else browID:= StrToInt(params); if Pos(';',params)>0 then // zadany 3 parametry (akce, browID, vlastnikID) begin params:= MidStr(params,Pos(';',params)+1,255); if Pos(';', params)>0 then begin vlastPar:= LeftStr(params,Pos(';',params)-1); vlastPar2:= MidStr(params,Pos(';',params)+1,255); end else vlastPar:= params; end; end else raise Exception.Create('Nemám potřebný počet parametrů !'); end; verText:= GetFileVersion2(GetModuleName(HInstance)); if Length(verText)=12 then verText:= LeftStr(verText,9) + '0' + RightStr(verText,3); if (RightStr(LeftStr(vlastPar,2),1)=':') or (LeftStr(vlastPar,2)='\\') then cestaExport:= vlastPar; vlastPar:= Trim(vlastPar); vlastPar2:= Trim(vlastPar2); { jeTest:= UpperCase(vlastPar)='TEST'; if (vlastPar2<>'') then jeTest:= UpperCase(vlastPar2)='TEST'; } if AnsiContainsText(UpperCase(paramsBak), ';TEST') then jeTest:= true; if (Helios.BrowseID<>browID) then begin typAkce:= 0; Helios.Error('Tento plugin lze volat pouze z přehledu: '#1 + IntToStr(browID) + #1'.'); end; IDcka:= ''; cRec:= 0; if (Helios.SelectedRecordIDs<>'') then IDcka:= Helios.SelectedRecordIDs else if not VarIsNull(Helios.CurrentRecordID) then begin cRec:= StrToInt(VarToStr(Helios.CurrentRecordID)); IDcka:= IntToStr(cRec); end; if (IDcka<>'') then begin cntID:= 1 + Length(IDcka)-Length(StringReplace(IDcka,',','',[rfReplaceAll])); SetLength(arrID,cntID); for l_loop:=0 to cntID-1 do begin if Pos(',',IDcka)>0 then begin arrID[l_loop]:= StrToInt(LeftStr(IDcka,Pos(',',IDcka)-1)); IDcka:= MidStr(IDcka,Pos(',',IDcka)+1,262140) // 65535 * 4 (max. delka pole) end else arrID[l_loop]:= StrToInt(IDcka); end; cRec:= arrID[0]; end; case typAkce of 0: Helios.Info(#1'PLUGIN OK'#1); 1: begin if (cRec>0) then begin if (browID=2) or (browID=11001) then begin with Helios.OpenSQL('SELECT id FROM ' + tblKZ + ' WHERE SkupZbo IN (N''VOD'',N''VOB'') AND Id=' + IntToStr(cRec)) do if (RecordCount=1) then begin fGenPolos:= TformGenPolos.Create(nil); try fGenPolos.Helios:= Helios; fGenPolos.idKZ:= cRec; fGenPolos.genKusovnik:= (LowerCase(vlastPar)='genkus'); fGenPolos.ShowModal; finally fGenPolos.Free; end; end else Helios.Error(#1'!! Akci lze spustit jen nad skupinami VOB/VOD !!'#1); end else Helios.Error(#1'!! Akci lze spustit jen v přehledu Kmenové karty nebo Vyráběné dílce !!'#1); end; end; 2: begin // kopie polozek na doklad try idDZ:= StrToInt(VarToStr(Helios.QueryEdit.FieldByNameValues('ID'))); dpz:= StrToInt(VarToStr(Helios.QueryEdit.FieldByNameValues('DruhPohybuZbo'))); newBid:= 0; if (dpz=2) or (dpz=4) then newBid:= 16; // prijemka if (dpz=0) then newBid:= 18; // vydejka if Helios.Prenos(newBid, 'ID', oVar1, '', 'Vyberte zdrojový doklad', true) then Helios.ExecSQL('IF OBJECT(N' + QuotedStr('dbo.ep_PZ_KopieVsechPolozkyNaDoklad') + ') IS NOT NULL EXEC dbo.ep_PZ_KopieVsechPolozkyNaDoklad @idSrc=' + VarToStr(oVar1) + ', @idDst=' + IntToStr(idDZ)); except end; end; 3: begin Helios.ExecSQL('IF OBJECT_ID(N' + QuotedStr('dbo._TabInsertPol') + ') IS NOT NULL DROP TABLE dbo._TabInsertPol'); try idDZ:= StrToInt(VarToStr(Helios.QueryEdit.FieldByNameValues('ID'))); dpz:= StrToInt(VarToStr(Helios.QueryEdit.FieldByNameValues('DruhPohybuZbo'))); try cOrg:= StrToInt(VarToStr(Helios.QueryEdit.FieldByNameValues('CisloOrg'))); except on E:Exception do cOrg:= 0; end; radDokl:= VarToStr(Helios.QueryEdit.FieldByNameValues('RadaDokladu')); if not((dpz=0) and ((radDokl='165') or (radDokl='166'))) then Helios.Error(#1'Tuto akci lze spustit jen v příjemce řady 165 / 166'#1) else begin podm:= 'TabPohybyZbozi.IdDOklad IN (SELECT ID FROM ' + tblDZ + ' WHERE CisloOrg=' + IntToStr(cOrg)+ ' AND DruhPohybuZbo IN (2,4))'; podm:= podm + ' AND TabPohybyZbozi.SkupZbo IN (N''F00'',N''F01'') AND TabPohybyZbozi.IDZboSklad IN (SELECT ID FROM ' + tblSS + ' WHERE IDKmenZbozi IN '; podm:= podm + '(SELECT ID FROM ' + tblKZe + ' WHERE ISNULL(_CislovySkladFormyPripravky,N'''')=N' + QuotedStr(Helios.Sklad) + '))'; if Helios.Prenos2(45, 'TabPohybyZbozi.ID', 'TabPohybyZbozi.RegCis', oVar1, oVar2, podm, 'Vyberte přijímané formy', True, True, False, 1) then begin IDcka:= VarToStr(oVar1); if (IDcka<>'') then begin lSQL:= 'IF OBJECT_ID(N''tempdb..#TabInsertPol'') IS NOT NULL DROP TABLE #TabInsertPol; CREATE TABLE #TabInsertPol (IdDoklad INT, IdStav INT, IdPohybOld INT)'; if (jeTest) then lSQL:= 'IF OBJECT_ID(N''dbo._TabInsertPol'') IS NOT NULL DROP TABLE dbo._TabInsertPol; CREATE TABLE dbo._TabInsertPol (IdDoklad INT, IdStav INT, IdPohybOld INT)'; Helios.ExecSQL(lSQL); arrID:= helUtils.StrToArrayInt(IDcka); for l_loop:=0 to Length(arrId)-1 do Helios.ExecSQL('INSERT ' + IfThen(jeTest, 'dbo._TabInsertPol', '#TabInsertPol') + ' (IdDoklad, IdPohybOld) VALUES (' + IntToStr(idDZ) + ', ' + IntToStr(arrID[l_loop]) + ')'); lSQL:= 'MERGE ' + IfThen(jeTest, 'dbo._TabInsertPol', '#TabInsertPol') + ' AS T USING dbo.TabPohybyZbozi AS S ON (S.Id=T.IdPohybOld) WHEN MATCHED THEN UPDATE SET T.IDStav=S.IdZboSklad;'; Helios.ExecSQL(lSQL); lSQL:= 'IF OBJECT_ID(N''dbo.ep_OZ_VlozPolozkyZTempTab'') IS NOT NULL EXEC dbo.ep_OZ_VlozPolozkyZTempTab @idDoklad=' + IntToStr(idDZ); lSQL:= lSQL + ', @typInsertu=1'; if not(jeTest) then Helios.ExecSQL(lSQL); end; end; end; except end; end; 4: begin exportProMatrix(Helios); term:= true; end; 5: begin Helios.ExecSQL('IF OBJECT_ID(N''dbo.ep_Matrix_GenVydObj'') IS NOT NULL EXEC dbo.ep_Matrix_GenVydObj'); term:= true; end; 6: ImportKmenProMatrix(Helios, vlastPar); 7: begin if (cRec>0) then if ('P30'=helUtils.getHeliosStrVal(Helios, '', 'SELECT SkupZbo FROM ' + tblKZ + ' WHERE ID=' + cRec.ToString)) then Helios.OpenBrowse2(100054, 'hvw_MaterialyPrvky.IDKmenZbozi=' + cRec.ToString, 898); end; 8: begin fOdlitek:= TformOdlitek.Create(nil); try sTemp:= helUtils.getHeliosStrVal(Helios, '', 'SELECT SkupZbo FROM ' + tblKZ + ' WHERE ID=' + cRec.ToString); fOdlitek.odlitek:= helUtils.getHeliosStrVal(Helios, '', 'SELECT RegCis FROM ' + tblKZ + ' WHERE ID=' + cRec.ToString); // with Helios.OpenSQL('SELECT ID FROM ' + tblOdlitky + ' WHERE ISNULL(MatecniOdlitek,N'''')=N' + QuotedStr(fOdlitek.odlitek)) do // if (RecordCount>1) then // Helios.Error(#1'Registrační číslo je číslo matečního odlitku, vyberte odpovídající kartu VOD nebo VOB'#1) // else if not (helUtils.HeliosExistsTest(Helios, tblOdlitky, 'Odlitek=N' + fOdlitek.odlitek.QuotedString)) then begin if (sTemp='VOB') or (sTemp='VOD') or (sTemp='P10') or (sTemp='P11') then begin Helios.ExecSQL('INSERT ' + tblOdlitky + ' (Odlitek) SELECT N' + fOdlitek.odlitek.QuotedString); fOdlitek.odlitek:= helUtils.getHeliosStrVal(Helios, '', 'SELECT RegCis FROM ' + tblKZ + ' WHERE ID=' + cRec.ToString); end else Helios.Error(#1'Tato karta nemá zavedenou kartu s paramerty odlitku/obrobku.'#1); end; // fOdlitek.skinName:= 'Carbon'; fOdlitek.Helios:= Helios; fOdlitek.jenView:= false; fOdlitek.debug:= false; fOdlitek.idK:= cRec; if (vlastPar='debug') then fOdlitek.debug:= true; { lSQL:= 'IF NOT EXISTS(SELECT ID FROM ' + tblOdlitky + ' WHERE Odlitek=N' + QuotedStr(fOdlitek.odlitek) + ') INSERT '; lSQL:= lSQL + tblOdlitky + ' (Odlitek, Nazev) SELECT RegCis, LTRIM(RTRIM(Nazev1)) FROM ' + tblKZ + ' WHERE ID=' + cRec.ToString; Helios.ExecSQL(lSQL); } with Helios.OpenSQL('SELECT ID FROM ' + tblOdlitky + ' WHERE Odlitek=N' + QuotedStr(fOdlitek.odlitek) + ' AND BlokovaniEditoru IS NOT NULL') do if (RecordCount>0) then fOdlitek.jenView:= true; fOdlitek.ShowModal; finally fOdlitek.Free; end; end; 9: begin fGenTPV:= TformGenTPV.Create(nil); try fGenTPV.Helios:= Helios; fGenTPV.idKZrodic:= cRec; fGenTPV.jeTest:= jeTest; fGenTPV.ShowModal; finally fGenTPV.Free; end; end; 10: begin fOdvTavba:= TformOdvTavby.Create(nil); try fOdvTavba.rada:= ''; fOdvTavba.jeTest:= jeTest; fOdvTavba.Helios:= Helios; fOdvTavba.typ:= 1; // tavba fOdvTavba.druhDavky:= 0; fOdvTavba.ShowModal; finally fOdvTavba.Free; end; end; 11: begin fOdvTavba:= TformOdvTavby.Create(nil); try fOdvTavba.rada:= ''; fOdvTavba.jeTest:= jeTest; fOdvTavba.Helios:= Helios; fOdvTavba.typ:= 2; // vibrovac fOdvTavba.druhDavky:= 0; fOdvTavba.ShowModal; finally fOdvTavba.Free; end; end; 12: begin ImportOperaciDoPripravyPrikazu(Helios); end; 13: begin cRec:= StrToInt(VarToStr(Helios.HeliosVlastnik.QueryBrowse.FieldByNameValues('ID'))); term:= helUtils.HeliosExistsTest(Helios, tblPlanLis, 'ID=' + cRec.ToString + ' AND Ukonceno=1'); oVar1:= ''; oVar2:= ''; Helios.ExecSQL('IF OBJECT_ID(N''dbo._temp_TabPrPostupy'') IS NOT NULL DROP TABLE dbo._temp_TabPrPostupy'); podm:= 'TabPrPostup.Kusy_zive>0 AND TabPrPostup.IDPrikaz IN (SELECT ID FROM ' + tblPrikaz + ' WHERE StavPrikazu IN (30,40)'; podm:= podm + ' AND Rada LIKE N' + QuotedStr('221-1%') + ') AND TabPrPostup.pracoviste IN (SELECT ID FROM ' + tblCPrac; podm:= podm + ' WHERE pracoviste LIKE N''V%'' AND LOWER(nazev) LIKE N''lis%'''; // podm:= podm + helUtils.getHeliosStrVal(Helios, 'SELECT Lis FROM ' + tblPlanLis + ' WHERE ID=' + cRec.ToString) + '%'''; podm:= podm + ')'; if Helios.Prenos2(bidFrontaPrace, 'TabPrPostup.ID', 'TabPrPostup.IDPrikaz', oVar1, oVar2, podm, 'Vyberte příkazy' + IfThen(term,' (!! PLÁN JE UZAVŘEN !!)',''), true, true, false, 1) then begin if not(term) then // je plan uzavren ? begin lSQL:= 'IF OBJECT_ID(N''tempdb..#TabPrPostupy'') IS NOT NULL DROP TABLE #TabPrPostupy' + CRLF + 'CREATE TABLE #TabPrPostupy (ID INT NOT NULL, '; lSQL:= lSQL + 'Autor NVARCHAR(80) DEFAULT SUSER_SNAME())' + CRLF; lSQL:= lSQL + 'INSERT #TabPrPostupy (ID) SELECT ID FROM ' + tblPrPost + ' WHERE ID IN (' + oVar1 + ')' + CRLF; if (jeTest) then begin lSQL:= StringReplace(lSQL, 'tempdb..', '', [rfReplaceAll]); lSQL:= StringReplace(lSQL, '#TabPrPostupy', 'dbo._temp_TabPrPostupy', [rfReplaceAll]); end; Helios.ExecSQL(lSQL); if not(jeTest) then Helios.ExecSQL('IF OBJECT_ID(N''dbo.ep_Vosk_PlanLisu_AddPrikazy'') IS NOT NULL EXEC dbo.ep_Vosk_PlanLisu_AddPrikazy @idPlan=' + cRec.ToString); end else Helios.Error(#1'Plán je již uzavřen, nelze přidávat příkazy'#1); end; end; 14: begin fOdvTavba:= TformOdvTavby.Create(nil); try fOdvTavba.rada:= ''; if (vlastPar<>'') then fOdvTavba.rada:= vlastPar; fOdvTavba.jeTest:= jeTest; fOdvTavba.Helios:= Helios; fOdvTavba.typ:= 3; // operace obecne odvod na stromky fOdvTavba.druhDavky:= 0; fOdvTavba.ShowModal; finally fOdvTavba.Free; end; end; 15: ImportKmenFormy(Helios); 16: begin fOdvLis:= TformOdvLis.Create(nil); try fOdvLis.Helios:= Helios; fOdvLis.jeTest:= jeTest; fOdvLis.mVerze:= verText; fOdvLis.ShowModal; finally fOdvLis.Free; end; end; 17: begin fOdvTavba:= TformOdvTavby.Create(nil); try fOdvTavba.rada:= '221-12'; fOdvTavba.jeTest:= jeTest; fOdvTavba.Helios:= Helios; fOdvTavba.typ:= 4; // operace udelej prevodku P12 z vosku na obalovnu fOdvTavba.druhDavky:= 0; fOdvTavba.ShowModal; finally fOdvTavba.Free; end; end; 18: begin fOdlitek:= TformOdlitek.Create(nil); try fOdlitek.odlitek:= helUtils.getHeliosStrVal(Helios, '', 'SELECT Odlitek FROM ' + tblOdlitky + ' WHERE ID=' + cRec.ToString); fOdlitek.Helios:= Helios; fOdlitek.jenView:= false; fOdlitek.debug:= false; if (vlastPar='debug') then fOdlitek.debug:= true; with Helios.OpenSQL('SELECT ID FROM ' + tblOdlitky + ' WHERE Odlitek=N' + QuotedStr(fOdlitek.odlitek) + ' AND BlokovaniEditoru IS NOT NULL') do if (RecordCount>0) then fOdlitek.jenView:= true; fOdlitek.ShowModal; finally fOdlitek.Free; end; end; 19: begin fDavObec:= TformDavkyObecne.Create(nil); try fDavObec.id:= 0; if (vlastPar='edit') then fDavObec.id:= cRec; fDavObec.idRodic:= 0; fDavObec.druhDavky:= 4; fDavObec.Helios:= Helios; fDavObec.ShowModal; finally fDavObec.Free; end; end; { 19: begin fOdvTavba:= TformOdvTavby.Create(nil); try fOdvTavba.rada:= '223-31'; fOdvTavba.jeTest:= jeTest; fOdvTavba.Helios:= Helios; fOdvTavba.typ:= 5; // obecna davka fOdvTavba.druhDavky:= 0; if (IsNumeric(Trim(vlastPar))) then fOdvTavba.druhDavky:= StrToInt(Trim(vlastPar)); // 1 = tavirna -> konecna fOdvTavba.ShowModal; finally fOdvTavba.Free; end; end; } 20: begin fOdvTavba:= TformOdvTavby.Create(nil); try fOdvTavba.jeTest:= jeTest; fOdvTavba.Helios:= Helios; fOdvTavba.typ:= 6; // P42 fOdvTavba.druhDavky:= 6; fOdvTavba.ShowModal; finally fOdvTavba.Free; end; end; 21: begin fEvidOp:= TformEvidOper.Create(nil); try fEvidOp.Helios:= Helios; fEvidOp.jeTest:= jeTest; fEvidOp.filtrRada:= IfThen(vlastPar<>'', vlastPar, ''); fEvidOp.ShowModal; finally fEvidOp.Free; end; end; 22: begin fHrOdvOp:= TformHrOdvOper.Create(nil); try fHrOdvOp.Helios:= Helios; fHrOdvOp.idHromEvidOp:= 0; fHrOdvOp.jeEditace:= false; if (vlastPar='edit') then begin fHrOdvOp.idHromEvidOp:= cRec; fHrOdvOp.jeEditace:= true; end; fHrOdvOp.jeTest:= jeTest; fHrOdvOp.filtrRada:= IfThen(vlastPar<>'', vlastPar, ''); fHrOdvOp.ShowModal; finally fHrOdvOp.Free; end; end; 23: ImportKmenFormyVC(Helios); 24: begin try if (Length(arrId)>0) then VyrPrikazyDoVyssiho(Helios, arrID); except on E:Exception do Helios.Error(#1'Chyba převedení příkazu(-ů) do vyššího' + CRLF + E.Message + #1); end; end; 25: begin if (Helios.BrowseID=11083) then begin try cRec:= StrToInt(VarToStr(Helios.HeliosVlastnik.QueryBrowse.FieldByName('ID').Value)); // ID prikazu ImportVCKmen2VyrPrikaz(Helios, cRec); except on E:Exception do Helios.Error(#1'Chyba při zjišťování ID příkazu'#1 + CRLF + E.Message); end; end; end; 26: ImportKmenMeridla(Helios); 27: ImportKmenMeridlaVC(Helios); 28: ZakazkaZobrazTavby(Helios, arrID); 29: ZakazkaZobrazAtestyTaveb(Helios, arrID); 30: begin fSchvalZak:= TformSchvaleniZak.Create(nil); try fSchvalZak.Helios:= Helios; fSchvalZak.id:= 0; if (Length(arrId)>1) then fSchvalZak.arrID:= arrId else fSchvalZak.id:= cRec; fSchvalZak.ShowModal; finally fSchvalZak.Free; end; end; 31: ImportProcentaZtrat(Helios); 32: begin iTemp:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT DPBID FROM ' + tblObecPrehled + ' WHERE NazevSys=N''hvw_ObdobiStavu'''); if (iTemp>0) then begin podm:= 'DATEDIFF(year, hvw_ObdobiStavu.DatumOd, GETDATE()) BETWEEN 0 AND 1'; if (Length(arrID)=1) then begin iTemp2:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT o.DatumOd_Y FROM ' + tblObd + ' o INNER JOIN ' + tblRozpRez + ' r ON (r.IDObdobi=o.ID) WHERE r.ID=' + cRec.ToString); if (iTemp2>0) then podm:= podm + ' AND hvw_ObdobiStavu.DatumOd_Y=' + iTemp2.ToString; end; if Helios.Prenos(iTemp, 'hvw_ObdobiStavu.ID', oVar, podm, 'Vyberte období ' + IfThen(iTemp2>0, ' roku ' + iTemp2.ToString, ' (M/R)'), true) then begin Helios.ExecSQL('DROP TABLE IF EXISTS #TabObdobiRozpRez' + CRLF + 'CREATE TABLE #TabObdobiRozpRez (IdObd INT)' + CRLF + 'INSERT #TabObdobiRozpRez (IdObd) SELECT ' + VarToStr(oVar)); RozpRezii_PseudoAutomat(Helios, arrID); end; end; Helios.ExecSQL('DROP TABLE IF EXISTS #TabObdobiRozpRez'); end; 33: begin // OOP zadani pohybu fOOPPohyb:= TformOOPPohyb.Create(nil); try fOOPPohyb.Helios:= Helios; fOOPPohyb.jeTest:= jeTest; fOOPPohyb.ShowModal; finally fOOPPohyb.Free; end; end; 34: begin iTemp:= StrToInt (VarToStr(Helios.HeliosVlastnik.QueryBrowse.FieldByNameValues('ID'))); // radek vydejky iTemp2:= StrToInt (VarToStr(Helios.HeliosVlastnik.QueryBrowse.FieldByNameValues('IDZboSklad'))); // idZboSklad nTemp:= StrToFloat (VarToStr(Helios.HeliosVlastnik.QueryBrowse.FieldByNameValues('Mnozstvi'))); // mnozstvi radku nTemp4:= helUtils.getHeliosFloatVal (Helios, 0, 'SELECT Mnozstvi FROM ' + tblSS + ' WHERE ID=' + iTemp2.ToString); nTemp3:= 0; VytvorTempExtKomPar (Helios, iTemp); lSQL:= 'DROP TABLE IF EXISTS #TabRadkyProVyber' + CRLF + 'CREATE TABLE #TabRadkyProVyber (IDX INT IDENTITY(1,1) NOT NULL, ID INT NOT NULL)'; Helios.ExecSQL(lSQL); lSQL:= 'SELECT p.ID, p.Mnozstvi FROM ' + tblPZ + ' p INNER JOIN ' + tblDZ + ' d ON (d.ID=p.IDDoklad) WHERE p.DruhPohybuZbo=0 AND p.IDZboSklad=' + iTemp2.ToString; lSQL:= lSQL + ' AND p.ID NOT IN (SELECT ID FROM ' + tblPZe + ' WHERE ISNULL(_Expedice_VazbaPrijemVydejNenabizet, 0)=1)'; lSQL:= lSQL + ' AND EXISTS(SELECT 1 FROM ' + tblDDZ + ' dd INNER JOIN ' + tblDDZe + ' dde ON (dde.ID=dd.ID) WHERE dd.RadaDokladu=d.RadaDokladu AND dd.DruhPohybuZbo=d.DruhPohybuZbo'; lSQL:= lSQL + ' AND ISNULL(dde._Expedice_VazbaPrijemVydejNabizet,0)=1)'; lSQL:= lSQL + ' AND p.ID>=ISNULL( (SELECT ID FROM ' + tblPZe + ' WHERE ISNULL(_Expedice_VazbaPrijemVydejHranice,0)=1), 0)'; lSQL:= lSQL + ' ORDER BY p.DatPorizeni DESC'; with Helios.OpenSQL(lSQL) do if (RecordCount>0) then begin canCont:= true; plusJeden:= false; First; while not(EOF) and (canCont) do begin iTemp3:= StrToInt (VarToStr(FieldByNameValues('ID'))); nTemp2:= StrToFloat (VarToStr(FieldByNameValues('Mnozstvi'))); nTemp3:= nTemp3 + nTemp2; if (nTemp30) then Helios.OpenBrowse(bidTabExtKom, ''); end; if (term) then Application.Terminate; end; initialization // System.ReportMemoryLeaksOnShutdown:= true; TComObjectFactory.Create(ComServer, TplgKdynium, Class_Kdynium, 'runMe', '', ciMultiInstance, tmSingle); END.