TomBuzin/Kdynium-plgKdynium
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
Kdynium-plgKdynium/ComObjekt.pas
TomBuz b1cd64bb32 Prvni verze
2025-05-21 21:19:51 +02:00

3005 lines
131 KiB
ObjectPascal
Raw Permalink Blame History

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<integer>); safecall;
procedure ImportVCKmen2VyrPrikaz (const Helios: IHelios; idVPr: Integer); safecall;
procedure ZakazkaZobrazTavby (const Helios: IHelios; ids:TArray<integer>); safecall;
procedure ZakazkaZobrazAtestyTaveb (const Helios: IHelios; ids:TArray<integer>); safecall;
procedure ImportProcentaZtrat (const Helios: IHelios); safecall;
procedure VytvorTempExtKomPar (const Helios: IHelios; id: integer); safecall;
procedure RozpRezii_PseudoAutomat (const Helios: IHelios; const arrID: TArray<integer>); 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<System.Integer>);
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<TArray<string>>;
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<string> = ['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<TArray<string>>;
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<string> = ['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<TArray<string>>;
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<string> = ['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<TArray<string>>;
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<TArray<string>>;
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<string>;
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<TArray<string>>;
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<TArray<string>>;
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<System.Integer>);
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<System.Integer>);
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<TObed>;
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<integer>);
var lSQL, nazRozp, vstStr, denikIDs, utvarVst, cisStredOper: string;
idPreuct, idx, rozpNr, idRozpRez, idObd, mObd, rObd: integer;
ctiZak, ctiUtvar: Boolean;
celkemHod, zakazkaHod: extended;
arrVstStr: TArray<string>;
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<integer>;
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.HeVersion<MinVerzeHelios) then
raise Exception.Create('Plugin vyžaduje min verzi Heliosu ' + IntToHex(MinVerzeHelios, 12))
else
begin
with Helios.OpenSQL('SELECT CONVERT(nvarchar(128),CONTEXT_INFO(),2)') do
if VarIsNull(FieldValues(0)) then
contInfo:= 'NULL'
else
contInfo:= VarToStr(FieldValues(0));
Helios.ExecSQL('SET CONTEXT_INFO 0x484443324b64796e69756d'); // nastav context v sys.sysprocesses (hexadecimalne HDC4Koramex)
UseLatestCommonDialogs:= true;
LocalFormatSettings:= TFormatSettings.Create;
{
lSQL:= 'IF OBJECT_ID(N''tempdb..#TabExtKom'') IS NOT NULL DROP TABLE #TabExtKom' + CRLF;
lSQL:= lSQL + 'CREATE TABLE #TabExtKom (Poznamka nvarchar(255))';
Helios.ExecSQL(lSQL);
}
lSQL:= 'IF OBJECT_ID(''tempdb..#TabExtKom'') IS NULL CREATE TABLE #TabExtKom (Poznamka NVARCHAR(255), Typ TINYINT DEFAULT NULL)' + CRLF;
lSQL:= lSQL + 'IF OBJECT_ID(N''tempdb..#TabTempUziv'') IS NULL CREATE TABLE #TabTempUziv (Tabulka';
lSQL:= lSQL + ' NVARCHAR(255) NOT NULL, SCOPE_IDENTITY INT NULL, Datum DATETIME NULL)';
Helios.ExecSQL(lSQL);
params:= '';
vlastPar:= '';
vlastPar2:= '';
cestaExport:= '';
typAkce:= 0;
skinNum:= 0;
if (helUtils.HeliosObjectExists(Helios, '[' + Helios.SystemDB + '].' + tblUserCfg, 'GlobalSkin3')) then
skinNum:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT GlobalSkin3 FROM [' + Helios.SystemDB + '].' + tblUserCfg + ' WHERE LoginName=SUSER_SNAME()');
with Helios.OpenSQL('SELECT Parametry FROM TabExtKom WHERE ID=' + IntToStr(Helios.ExtKomID)) do
begin
params:= VarToStr(FieldValues(0));
paramsBak:= VarToStr(FieldValues(0));
if Pos(';',params)>0 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 (nTemp3<nTemp4) then
Helios.ExecSQL ('INSERT #TabRadkyProVyber (ID) SELECT ' + iTemp3.ToString)
else
begin
if (nTemp3-nTemp4<nTemp2) then
Helios.ExecSQL ('INSERT #TabRadkyProVyber (ID) SELECT ' + iTemp3.ToString);
canCont:= false;
end;
Next;
end;
end;
end;
35: ImportObedy (Helios);
end; // case
Helios.Refresh(true);
if (contInfo='NULL') then
Helios.ExecSQL('SET CONTEXT_INFO 0x')
else
Helios.ExecSQL('SET CONTEXT_INFO 0x' + contInfo);
if not(term) then
if helUtils.HeliosExistsTest(Helios, '#TabExtKom', '') then
with Helios.OpenSQL('SELECT * FROM #TabExtKom WHERE Poznamka NOT LIKE N''%tavenin%''') do
if (RecordCount>0) 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.
Reference in New Issue View Git Blame Copy Permalink