unit frmSchvaleniZak; interface uses Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics, Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.StdCtrls, Vcl.ExtCtrls, Vcl.ComCtrls, Vcl.Mask, NxGridView6, NxColumns6, NxControls6, NxCustomGrid6, NxTypes6, NxVirtualGrid6, NxDBGrid6, NxDBColumns6, NxGrid6, Data.DB, MemDS, VirtualTable, Vcl.Buttons, ddPlugin_TLB, FireDAC.Stan.Intf, FireDAC.Stan.Option, FireDAC.Stan.Param, FireDAC.Stan.Error, FireDAC.DatS, FireDAC.Phys.Intf, FireDAC.DApt.Intf, FireDAC.Comp.DataSet, FireDAC.Comp.Client; const tblSchvalZak = '[dbo].[_hdc_TabSchvaleniZakazky]'; tblSchvalZakHist = '[dbo].[_hdc_TabSchvaleniZakazkyHist]'; tblSchvalZakRole = '[dbo].[_hdc_TabSchvaleniZakazkyRole]'; c_UnassignedDate = -693594; type TformSchvaleniZak = class(TForm) edtZakazka: TLabeledEdit; edtZakazkaNazev: TLabeledEdit; cbStav: TComboBox; Label1: TLabel; edtRole: TLabeledEdit; Label2: TLabel; edtPoznamka: TMemo; btnOK: TButton; btnStorno: TButton; Label3: TLabel; lblStav: TLabel; btnAdd: TBitBtn; grd: TNextDBGrid6; grdView: TNxReportGridView6; colStav: TNxDBTextColumn6; colHistID: TNxDBNumberColumn6; tbl: TFDMemTable; ds: TDataSource; tblcolDatum: TDateTimeField; tblcolStav: TStringField; tblcolPozn: TStringField; tblcolHistID: TIntegerField; colRole: TNxDBTextColumn6; colPozn: TNxDBMemoColumn6; tblcolRole: TStringField; Label4: TLabel; lblDatReq: TLabel; Label5: TLabel; lblMnoz: TLabel; tblcolAutor: TStringField; colAutor: TNxDBTextColumn6; Label6: TLabel; cbJenVDO: TCheckBox; tblcolJenVDO: TSmallintField; colJenVDO: TNxDBNumberColumn6; edtRegCis: TLabeledEdit; colDatum: TNxDBDateColumn6; lblDatObr: TLabel; edtDatObr: TDateTimePicker; tblcolDatumOBR: TDateField; colDatumObr: TNxDBTimeColumn6; tblcolVDOproOBR: TSmallintField; mInfo: TMemo; cbZmenaDatumKonZakazka: TCheckBox; tblcolOBRproVDO: TSmallintField; tblcolDatumVDO: TDateField; colVDOproOBR: TNxDBCheckBoxColumn6; tblcolPoznamkaR: TSmallintField; tblcolPoznamka2: TStringField; procedure FormShow (Sender: TObject); procedure FormClose (Sender: TObject; var Action: TCloseAction); procedure btnStornoClick (Sender: TObject); procedure btnAddClick (Sender: TObject); procedure tblAfterPost (DataSet: TDataSet); procedure colDatumDrawBackground (Sender: TObject; ACol, ARow: Integer; CellRect: TRect; State: TNxCellPaintingState); procedure grdViewHeaderMouseDown (Sender: TObject; Button: TMouseButton; Shift: TShiftState; Col, X, Y: Integer); procedure grdCellDblClick(Sender: TObject; ACol, ARow: Integer); procedure btnOKClick(Sender: TObject); procedure colRoleGetHint (Sender: TObject; ACol, ARow: Integer; var Text: WideString); procedure colPoznGetHint (Sender: TObject; ACol, ARow: Integer; var Text: WideString); procedure colDatumGetText(Sender: TObject; ACol, ARow: Integer; var Text: WideString); private function LastRow: integer; procedure NactiHistorii; procedure ZapisDoHist (const idZak, radek: integer); safecall; public Helios: IHelios; id: Integer; arrID: TArray; end; var formSchvaleniZak: TformSchvaleniZak; oVar1: OleVariant; cisZakazky, szKmene, szKmeneSub, currRole, lastRole: string; idZakazky: integer; arrIDZak: TArray; idPrikazFin, idPrikazSub: integer; histLoad, zamek, currZamek, zapisOBR, jeTestDB, jeObrobek, jeVzorek, VdoProVzo: boolean; uzivId, idZakazkyPom: integer; prevRole: string; implementation uses System.StrUtils, System.DateUtils, helUtils; {$R *.dfm} function TformSchvaleniZak.LastRow: integer; var i: integer; begin result:= -1; if (grd.RowCount>0) then for i:=0 to grd.RowCount-1 do if (grd.Cells[0,i]='') then result:= i; end; procedure TformSchvaleniZak.NactiHistorii; var lSQL, stavTxt, role, sTemp, pozn: string; akce: Byte; stavIdx: integer; jenVDO, proOBR, lastRec: boolean; datumObrStr: string; rNum: integer; begin prevRole:= ''; // promenna pro posledni roli v historii (kdo se vyjadril jako posledni) lastRec:= false; if (idZakazky<=0) then Exit; lSQL:= 'SELECT z.CisloZakazky, z.Nazev, z.DruhyNazev, ISNULL(ze._SchvaleniZakazky_Lock,0) FROM ' + tblZak + ' z LEFT JOIN ' + tblZakE; lSQL:= lSQL + ' ze ON (ze.ID=z.ID) WHERE z.ID=' + idZakazky.ToString; with Helios.OpenSQL(lSQL) do if (RecordCount=1) then begin edtZakazka.Text:= VarToStr(FieldValues(0)); edtZakazkaNazev.Text:= VarToStr(FieldValues(1)) + ' ** ' + VarToStr(FieldValues(2)); lSQL:= 'SELECT TOP(1) p.RegCis FROM ' + tblPZ + ' p INNER JOIN ' + tblDZ + ' d ON (d.ID=p.IDDoklad) WHERE d.DruhPohybuZbo IN (9,6) AND '; lSQL:= lSQL + 'ISNULL(p.CisloZakazky,d.CisloZakazky)=N' + QuotedStr(VarToStr(FieldValues(0))); with Helios.OpenSQL(lSQL) do if (RecordCount>0) then edtRegCis.Text:= VarToStr(FieldValues(0)); stavTxt:= VarToStr(FieldValues(3)); zamek:= true; btnOK.Enabled:= false; btnOK.Enabled:= true; if (stavTxt='0') then begin zamek:= false; btnOK.Enabled:= true; end; datumObrStr:= ''; stavTxt:= ''; role:= ''; lblStav.Caption:= stavTxt; // grd.DataSource.DataSet.ClearFields; tbl.EmptyDataSet; histLoad:= true; proOBR:= false; zapisObr:= false; lSQL:= 'SELECT DatPorizeni, Role, Akce, Poznamka, ID, Autor, ISNULL(JenVDO,0) AS JenVDO, ISNULL(VDOproOBR,0) AS VDOproOBR, DatumOBR, DatumKON FROM ' + tblSchvalZakHist; lSQL:= lSQL + ' WHERE IDZakazky=' + idZakazky.ToString + ' ORDER BY DatPorizeni'; with Helios.OpenSQL(lSQL) do begin lastRole:= ''; First; rNum:= 1; while not(EOF) do begin tbl.Append; // tbl.FieldByName('colDatum').AsString:= FormatDateTime('d.m.yyyy hh:nn:ss', StrToDateTime(VarToStr(FieldValues(0)))); if (VarIsType(FieldByNameValues('DatPorizeni'), varDate)) then tbl.FieldByName('colDatum').AsString:= FieldByNameValues('DatPorizeni'); role:= VarToStr(FieldByNameValues('Role')); tbl.FieldByName('colRole').AsString:= role; if (RecordCount=rNum) then lastRole:= role; if (VarIsType(FieldByNameValues('DatumOBR'), varDate)) then if (VarToStr(FieldByNameValues('DatumOBR'))<>'') then tbl.FieldByName('colDatumObr').AsString:= FormatDateTime('d.m.yyyy', StrToDate(VarToStr(FieldByNameValues('DatumOBR')))); if (role='OBR') and (tbl.FieldByName('colDatumObr').AsString<>'') then datumObrStr:= tbl.FieldByName('colDatumObr').AsString; prevRole:= role; zapisObr:= false; if (role='OBR') then zapisObr:= true; proOBR:= false; sTemp:= VarToStr(FieldByNameValues('VDOproOBR')); if (role='VDO') and (StrToBool(sTemp)) then proOBR:= true; tbl.FieldByName('colVDOproOBR').AsInteger:= IfThenInt(proOBR, 1, 0); akce:= StrToInt(VarToStr(FieldByNameValues('Akce'))); case akce of 1: stavIdx:= cbStav.Items.IndexOf('Souhlas'); 2: stavIdx:= cbStav.Items.IndexOf('Vráceno'); 3: stavIdx:= cbStav.Items.IndexOf('Dotaz'); end; case akce of 1: stavTxt:= 'Souhlas'; 2: stavTxt:= 'Vráceno'; 3: stavTxt:= 'Dotaz'; end; if (VarIsType(FieldByNameValues('DatumKON'), varDate)) then if (VarToStr(FieldByNameValues('DatumKON'))<>'') then tbl.FieldByName('colDatumObr').AsString:= FormatDateTime('d.m.yyyy', StrToDate(VarToStr(FieldByNameValues('DatumKON')))); pozn:= VarToStr(FieldByNameValues('Poznamka')); { if (tbl.FieldByName('colDatumObr').AsString<>'') then pozn:= pozn + IfThen(pozn<>'', ' (', '') + 'datum: ' + tbl.FieldByName('colDatumObr').AsString + IfThen(pozn<>'', ')', ''); } tbl.FieldByName('colPoznamka').AsString:= pozn; tbl.FieldByName('colPoznamka2').AsString:= pozn; tbl.FieldByName('colPoznamkaR').AsInteger:= 0; if (pozn.Contains(Chr(13))) then begin tbl.FieldByName('colPoznamkaR').AsInteger:= 1; tbl.FieldByName('colPoznamka').AsString:= '(ukaž mě)'; end; tbl.FieldByName('colHistID').AsString:= VarToStr(FieldByNameValues('ID')); tbl.FieldByName('colAutor').AsString:= VarToStr(FieldByNameValues('Autor')); tbl.FieldByName('colJenVDO').AsInteger:= 0; jenVDO:= StrToBool(VarToStr(FieldValues(6))); if (jenVDO) then begin tbl.FieldByName('colJenVDO').AsInteger:= 1; if (akce=1) then stavTxt:= stavTxt + ' (VDO)'; end; if (proOBR) then stavTxt:= stavTxt + ' (OBR)'; tbl.FieldByName('colStav').AsString:= stavTxt; tbl.Post; Inc (rNum); Next; end; grd.Sort(colDatum.Index, skDescending); grd.Invalidate; if ((lastRole='VDO') or ((currRole='VDO') and (prevRole='OBR'))) and (datumObrStr<>'') then edtDatObr.DateTime:= StrToDateTime (datumObrStr); end; lastRole:= helUtils.getHeliosStrVal (Helios, '', 'SELECT TOP(1) Role FROM ' + tblSchvalZakHist + ' WHERE IDZakazky=' + idZakazky.ToString + ' ORDER BY DatPorizeni DESC'); if (stavTxt='Vráceno') then lblStav.Caption:= stavTxt + ' REP'; if (stavTxt='Souhlas') then lblStav.Caption:= stavTxt; if (stavTxt='Souhlas') and (role='REP') then lblStav.Caption:= 'Požadováno schválení DZR'; if (stavTxt='Souhlas') and (role='DZR') then lblStav.Caption:= 'Požadováno schválení CEN'; if ((stavTxt='Souhlas') and (role='CEN')) or ((stavTxt='Souhlas (VDO)') and (role='REP')) or ((stavTxt='Souhlas') and (role='OBR')) then lblStav.Caption:= 'Požadováno schválení VDO'; if (stavTxt='Souhlas') and (role='VDO') and (proOBR) then lblStav.Caption:= 'Požadováno schválení OBR'; if (stavTxt='Souhlas') and (role='VDO') then lblStav.Caption:= 'Schváleno'; lblDatReq.Caption:= ''; lblMnoz.Caption:= '0'; lSQL:= 'SELECT TOP(1) PozadDatDod, Mnozstvi FROM ' + tblPZ + ' WHERE CisloZakazky=N' + edtZakazka.Text.QuotedString; lSQL:= lSQL + ' AND DruhPohybuZbo=' + IfThen(edtZakazka.Text.LeftStr(1)='R', '6', '9'); with Helios.OpenSQL(lSQL) do if (RecordCount=1) then begin lblDatReq.Caption:= ''; stavTxt:= VarToStr(FieldValues(0)); if (stavTxt<>'') and (VarIsType(FieldValues(0), varDate)) then lblDatReq.Caption:= FormatDateTime('d.m.yyyy', StrToDate(stavTxt)); lblMnoz.Caption:= VarToStr(FieldValues(1)); end; end; lSQL:= 'SELECT 1 FROM ' + tblSchvalZakHist + ' WHERE Akce=2 AND Role=N''VDO'' AND IDZakazky=' + idZakazky.ToString; lSQL:= lSQL + ' AND ID=(SELECT MAX(ID) FROM ' + tblSchvalZakHist + ' WHERE Role=N''VDO'' AND IDZakazky=' + idZakazky.ToString + ')'; if (helUtils.sqlExistsTestGeneral(Helios, lSQL)) then begin btnAdd.Enabled:= false; btnOK.Enabled:= false; edtPoznamka.Enabled:= false; cbStav.Enabled:= false; cbJenVDO.Enabled:= false; end; lSQL:= 'SELECT 1 FROM ' + tblSchvalZakHist + ' WHERE Akce=2 AND Role=N''OBR'' AND IDZakazky=' + idZakazky.ToString; // lSQL:= lSQL + ' AND ID=(SELECT MAX(ID) FROM ' + tblSchvalZakHist + ' WHERE Role=N''VDO'' AND IDZakazky=' + idZakazky.ToString + ')'; if (helUtils.sqlExistsTestGeneral(Helios, lSQL)) then begin btnAdd.Enabled:= false; btnOK.Enabled:= false; edtPoznamka.Enabled:= false; cbStav.Enabled:= false; cbJenVDO.Enabled:= false; end; if (edtRole.Text='OBR') and (zapisOBR) then begin btnAdd.Enabled:= false; btnOK.Enabled:= false; cbStav.Enabled:= false; edtPoznamka.Enabled:= false; end; if not(zamek) then begin btnAdd.Enabled:= true; btnOK.Enabled:= true; edtPoznamka.Enabled:= true; cbStav.Enabled:= true; cbJenVDO.Enabled:= (role='OBR'); end; end; procedure TformSchvaleniZak.tblAfterPost (DataSet: TDataSet); var lR: integer; begin Exit; grd.RowCount:= grd.RowCount + 1; lR:= LastRow; if (histLoad) then begin grd.Cell[colRole.Index, lR].AsString:= DataSet.FieldByName('colRole').AsString; grd.Cell[colDatum.Index, lR].AsString:= DataSet.FieldByName('colDatum').AsString; grd.Cell[colStav.Index, lR].AsString:= DataSet.FieldByName('colStav').AsString; grd.Cell[colPozn.Index, lR].AsString:= Trim(DataSet.FieldByName('colPoznamka').AsString); grd.Cell[colHistID.Index, lR].AsInteger:= DataSet.FieldByName('colHistID').AsInteger; grd.Cell[colAutor.Index, lR].AsString:= DataSet.FieldByName('colAutor').AsString; grd.Cell[colJenVDO.Index, lR].AsInteger:= dataset.FieldByName('colJenVDO').AsInteger; grd.Cell[colDatumObr.Index, lR].AsString:= FormatDateTime('d.m.yyyy', dataset.FieldByName('colDatumObr').AsDateTime); end else begin grd.Cell[colRole.Index, lR].AsString:= edtRole.Text; grd.Cell[colDatum.Index, lR].AsString:= FormatDateTime('d.m.yyyy hh:nn:ss', Now); grd.Cell[colStav.Index, lR].AsString:= cbStav.Text; grd.Cell[colPozn.Index, lR].AsString:= Trim(edtPoznamka.Text); grd.Cell[colJenVDO.Index, lR].AsInteger:= StrToInt(IfThen(cbJenVDO.Checked, '1', '0')); grd.Cell[colDatumObr.Index, lR].AsString:= FormatDateTime('d.m.yyyy', edtDatObr.Date); end; end; procedure TformSchvaleniZak.btnAddClick (Sender: TObject); var lSQL, pozn: string; jenVDO: boolean; begin pozn:= edtPoznamka.Text.Trim; { if (edtRole.Text='OBR') then pozn:= pozn + IfThen(pozn<>'', ' (datum: ', '') + FormatDateTime('d.m.yyyy', edtDatObr.DateTime) + IfThen(pozn<>'', ')', ''); } jenVDO:= cbJenVDO.Checked; if (currRole='VDO') and (cbStav.ItemIndex=0) and (jeVzorek) and not(jenVDO) then if not(Helios.YesNo('Odsouhlasením schvalování uzavřete a pošlete informaci VZO a garantovi.' + CRLF + 'Pokračovat ?', false)) then Exit; histLoad:= false; tbl.Append; tbl.FieldByName('colRole').AsString:= edtRole.Text; tbl.FieldByName('colDatum').AsDateTime:= Now; tbl.FieldByName('colStav').AsString:= cbStav.Text; tbl.FieldByName('colPoznamka').AsString:= pozn; tbl.FieldByName('colHistID').AsInteger:= 0; tbl.FieldByName('colVDOproOBR').AsInteger:= 0; if (cbJenVDO.Caption.Contains('pro OBR')) and (cbJenVDO.Checked) then begin tbl.FieldByName('colVDOproOBR').AsInteger:= 1; tbl.FieldByName('colStav').AsString:= tbl.FieldByName('colStav').AsString + ' (pro OBR)'; end; tbl.FieldByName('colOBRproVDO').AsInteger:= 0; tbl.FieldByName('colJenVDO').AsInteger:= 0; if (cbJenVDO.Caption.Contains('pro VDO')) and (cbJenVDO.Checked) then begin tbl.FieldByName('colOBRproVDO').AsInteger:= 1; tbl.FieldByName('colJenVDO').AsInteger:= 1; tbl.FieldByName('colStav').AsString:= tbl.FieldByName('colStav').AsString + ' (pro VDO)';// IfThen(edtRole.Text='OBR', ' OBR', ' VDO'); end; if ((edtRole.Text='OBR') or (edtRole.Text='VDO')) and (cbZmenaDatumKonZakazka.Checked) then tbl.FieldByName('colDatumObr').AsDateTime:= edtDatObr.DateTime; if (edtRole.Text='VDO') and (cbZmenaDatumKonZakazka.Checked) then tbl.FieldByName('colDatumVDO').AsDateTime:= edtDatObr.DateTime; tbl.Post; grd.Sort (colDatum.Index, skDescending); grd.Invalidate; btnAdd.Enabled:= false; end; procedure TformSchvaleniZak.ZapisDoHist (const idZak: Integer; const radek: Integer); var akce, idKZ, rozdilMin: integer; lSQL, s, roleCil, datKonec: string; datOBR, datKon: TDatetime; idVPrV, idPlan: integer; // VDO/VOB begin if (idZak>0) and (radek>=0) then begin helUtils.ReseedTable (Helios, tblSchvalZakHist); if (edtDatObr.Visible) then datOBR:= edtDatObr.Date; lSQL:= 'INSERT ' + tblSchvalZakHist + ' (IDZakazky, Role, RoleCil, Akce, Poznamka, DatPorizeni, '; if(edtRole.Text='VDO') then begin if not(VdoProVzo) then begin lSQL:= lSQL + 'VDOproOBR'; if (cbZmenaDatumKonZakazka.Checked) then lSQL:= lSQL + ', DatumKON'; end; end else lSQL:= lSQL + 'JenVDO'; if (edtRole.Text='OBR') then lSQL:= lSQL + ', DatumOBR'; if (jeVzorek) and (VdoProVzo) then lSQL:= lSQL + ', VDOProVZO'; lSQL:= lSQL + ') SELECT ' + idZak.ToString + ', N' + edtRole.Text.QuotedString + ', N'; roleCil:= ''; if (cbStav.ItemIndex=0) then // souhlas begin if (edtRole.Text='REP') then begin if (cbJenVDO.Checked) then roleCil:= 'VDO' else roleCil:= 'DZR'; end else if (edtRole.Text='DZR') then roleCil:= 'CEN' else if (edtRole.Text='CEN') then roleCil:= 'VDO' else if (edtRole.Text='VDO') then begin if (VdoProVzo) then roleCil:= 'VZO' else if (cbJenVDO.Checked) then roleCil:= 'OBR' else if (jeVzorek) then roleCil:= 'VZO/G' else roleCil:= 'REP'; end else if (edtRole.Text='OBR') then begin if (cbJenVDO.Checked) then roleCil:= 'VDO' else roleCil:= 'REP'; end else lSQL:= lSQL + ''; end; if (cbStav.ItemIndex=1) then begin if (edtRole.Text='DZR') or (edtRole.Text='CEN') or (edtRole.Text='VDO') then roleCil:= 'REP'; if (edtRole.Text='OBR') then roleCil:= 'VDO'; end; if (currRole='VZO') and (cbStav.ItemIndex=2) then roleCil:= 'VDO'; lSQL:= lSQL + roleCil.QuotedString; s:= grd.Cells[colStav.Index, radek]; akce:= 1; if (s='Vrátit') then akce:= 2; if (s='Souhlas') and (edtRole.Text='VDO') and (cbJenVDO.Checked) then akce:= 1; if (currRole='VZO') then akce:= 3; lSQL:= lSQL + ', ' + akce.ToString + ', N' + QuotedStr(grd.Cells[colPozn.Index, radek]) + ', CONVERT(datetime, N' + QuotedStr(grd.Cells[colDatum.Index, radek]) + ',104),'; if (edtRole.Text='VDO') and not(VdoProVzo) then lSQL:= lSQL + grd.Cell[colVDOproOBR.Index, radek].AsInteger.ToString else lSQL:= lSQL + grd.Cell[colJenVDO.Index, radek].AsInteger.ToString; if (edtRole.Text='OBR') then lSQL:= lSQL + ', CONVERT(date, N' + FormatDateTime('dd.mm.yyyy', edtDatObr.DateTime).QuotedString + ', 104)'; if (edtRole.Text='VDO') and (datOBR<>Today) and (cbZmenaDatumKonZakazka.Checked) then lSQL:= lSQL + ', CONVERT(date, N' + FormatDateTime('dd.mm.yyyy', edtDatObr.DateTime).QuotedString + ', 104)'; // if (edtRole.Text='VDO') then // Helios.ExecSQL('INSERT dbo._hdc_TabLog (LogText) VALUES (N' + lSQL.Replace('''','''''').QuotedString + ')'); try Helios.ExecSQL(lSQL); // pokud je VDO (Roubal), zapis datum do DatumKON na zakazce if (edtRole.Text='VDO') and (s='Souhlas') and (datOBR<>Today) and (cbZmenaDatumKonZakazka.Checked) then Helios.ExecSQL('UPDATE ' + tblZakE + ' SET _DatPotvrzeniVyroby=CONVERT(date, N' + FormatDateTime('dd.mm.yyyy', edtDatObr.DateTime).QuotedString + ', 104) WHERE ID=' + idZak.ToString); // pokud je OBR (Simacek) if (edtRole.Text='OBR') and (s='Souhlas') and (datOBR<>Today) then // and ((jeTestDb) or (Helios.CurrentDB='Kdynium')) then begin lSQL:= 'SELECT p.ID FROM ' + tblVPr + ' p INNER JOIN ' + tblKZ + ' k ON (k.ID=p.IDTabKmen) WHERE k.SkupZbo IN (N''P51'') AND p.IDZakazka=' + idZak.ToString; idVPrV:= helUtils.getHeliosIntVal(Helios, 0, lSQL); if (idVPrV>0) then begin lSQL:= 'DECLARE @planKal INT, @d DATETIME; SET @d=DATEADD(day, 1, CONVERT(datetime, N' + datOBR.Format('yyyymmdd').QuotedString + ', 112))' + CRLF; lSQL:= lSQL + 'EXEC dbo.hp_PrepocetPlanovanychTerminuVyroby @IDPlan=NULL, @IDPrikaz=' + idVPrV.ToString + ', @VcetnePodrizenychPrikazu=0, @PlanUkonceni=@d'; lSQL:= lSQL + ', @AktualizovatDatumVyrPlanu=0, @ErrIDPlanKalend=@planKal OUT, @AktualizovatPotvrzTerminObj=0'; Helios.ExecSQL(lSQL); lSQL:= 'SELECT p.ID FROM ' + tblVPr + ' p INNER JOIN ' + tblKZ + ' k ON (k.ID=p.IDTabKmen) WHERE k.SkupZbo IN (N''VOB'', N''VOD'') AND p.ID=(SELECT IDPrikazRidici FROM ' + tblVPr; lSQL:= lSQL + ' WHERE ID=' + idVPrV.ToString + ') AND p.IDPrikazVyssi IS NULL AND p.IDZakazka=' + idZak.ToString; idVPrV:= helUtils.getHeliosIntVal(Helios, 0, lSQL); if (idVPrV>0) then begin lSQL:= 'SELECT DATEDIFF(mi, Plan_zadani, Plan_ukonceni) AS RozdilMin FROM ' + tblVPr + ' WHERE ID=' + idVPrV.ToString; rozdilMin:= helUtils.getHeliosIntVal(Helios, 0 , lSQL); if (rozdilMin>=0) then begin lSQL:= 'DECLARE @d DATETIME; SET @d=CONVERT(datetime, N' + datOBR.Format('dd.mm.yyyy hh:nn:ss').QuotedString + ', 104); EXEC @d = dbo.ef_GetPrevNextWorkDay @d, N''N''; SELECT @d AS NextDen'; datOBR:= helUtils.getHeliosDateTimeVal(Helios, Now, lSQL); datKonec:= ''; // lSQL:= 'EXEC dbo.hp_VyrPrikaz_GetPlanTermin @IDDilce=' + idKZ.ToString; lSQL:= 'SELECT IDTabKmen, ISNULL(IDZakazModif,0) AS IDZakazModif, kusy_ciste FROM ' + tblVPr + ' WHERE ID=' + idVPrV.ToString; with Helios.OpenSQL(lSQL) do begin lSQL:= 'DECLARE @dStart DATETIME, @dEnd DATETIME, @dStartVyssi DATETIME, @idKZ INT, @errPlanKal INT' + CRLF; lSQL:= lSQL + 'SET @dStart=CONVERT(datetime, N' + datOBR.Format('yyyymmdd').QuotedString + ', 112)' + CRLF; lSQL:= lSQL + 'EXEC dbo.hp_VyrPrikaz_GetPlanTermin @IDDilce=' + VarToStr(FieldByNameValues('IDTabKmen')) + ', @KusyZadane=' + VarToStr(FieldByNameValues('kusy_ciste')); lSQL:= lSQL + ', @PlanZadani=@dStart OUT, @PlanUkonceni=@dEnd OUT, @AplikujDobuMeziPrikazy=0, @Selectem=0, @PlanZadaniVyssiho=@dStartVyssi OUT, @IDZakazModif='; if (VarToStr(FieldByNameValues('IDZakazModif'))='0') then lSQL:= lSQL + 'NULL' else lSQL:= lSQL + VarToStr(FieldByNameValues('IDZakazModif')); lSQL:= lSQL + ', @DatumTPV=NULL, @IDPrikaz=' + idVPrV.ToString + ', @ErrIDPlanKalend=@errPlanKal OUT' + CRLF; lSQL:= lSQL + 'SELECT @dEnd AS DatKonec'; datKon:= helUtils.getHeliosDateTimeVal(Helios, Now, lSQL); datKonec:= helUtils.getHeliosStrVal(Helios, '', lSQL); end; { lSQL:= 'DECLARE @dStart DATETIME, @dEnd DATETIME, @idKZ INT; SET @dStart=CONVERT(datetime, N' + datOBR.Format('yyyymmdd').QuotedString + ', 112)' + CRLF; lSQL:= lSQL + 'EXEC dbo.hp_GetPlanovanyTerminDokonceniVyroby @IDFinal=' + VarToStr(FieldByNameValues('IDTabKmen')) + ', @IDZakazModif='; if (VarToStr(FieldByNameValues('IDZakazModif'))='0') then lSQL:= lSQL + 'NULL' else lSQL:= lSQL + VarToStr(FieldByNameValues('IDZakazModif')); lSQL:= lSQL + ', @Mnozstvi=' + VarToStr(FieldByNameValues('kusy_ciste')) + ', @DatumZahajeni=@dStart, @DatumUkonceni=@dEnd OUT' + CRLF + 'SELECT @dEnd AS DatKonec'; datKonec:= helUtils.getHeliosStrVal(Helios, '', lSQL); end; lSQL:= 'DECLARE @dStart DATETIME, @dEnd DATETIME; SET @dStart=CONVERT(datetime, N' + datOBR.Format('yyyymmdd').QuotedString + ', 112)' + CRLF; lSQL:= lSQL + 'SET @dEnd = DATEADD(mi, ' + rozdilMin.ToString + ', @dStart)' + CRLF + 'SELECT @dEnd AS DatKonec'; with Helios.OpenSQL(lSQL) do datKonec:= VarToStr(FieldByNameValues('DatKonec')); } if (datKonec<>'') then begin lSQL:= 'UPDATE ' + tblVPr + ' SET plan_zadani=dbo.hf_TruncDate(CONVERT(datetime, N' + datOBR.Format('dd.mm.yyyy hh:nn:ss').QuotedString + ', 104))'; lSQL:= lSQL + ', plan_ukonceni=dbo.hf_TruncDate(CONVERT(datetime, N' + datKon.Format('dd.mm.yyyy hh:nn:ss').QuotedString + ', 104)) WHERE ID=' + idVPrV.ToString; Helios.ExecSQL(lSQL); idPlan:= helUtils.getHeliosIntVal (Helios, 0, 'SELECT IDPlan FROM ' + tblVPr + ' WHERE ID=' + idVPrV.ToString); if (idPlan>0) then begin lSQL:= 'UPDATE ' + tblPlan + ' SET datum=dbo.hf_TruncDate(CONVERT(datetime, N' + datKon.Format('dd.mm.yyyy hh:nn:ss').QuotedString + ', 104)) WHERE ID=' + idPlan.ToString; Helios.ExecSQL(lSQL); end; end; end; end; end; end; except end; end; end; procedure TformSchvaleniZak.btnOKClick (Sender: TObject); var i, x: integer; begin if (grd.RowCount>0) and (edtRole.Text<>'') then begin for i:=0 to grd.RowCount-1 do if (grd.Cell[colHistID.Index, i].AsInteger=0) and (grd.Cells[colRole.Index, i]=edtRole.Text) then if (Length(arrID)>1) and (idZakazky=0) then for x:=Low(arrID) to High(arrID) do ZapisDoHist (arrID[x], i) else ZapisDoHist (idZakazky, i); end; Close; end; procedure TformSchvaleniZak.btnStornoClick (Sender: TObject); begin Close; end; procedure TformSchvaleniZak.colDatumDrawBackground (Sender: TObject; ACol, ARow: Integer; CellRect: TRect; State: TNxCellPaintingState); var i: Integer; begin i:= ACol; end; procedure TformSchvaleniZak.colDatumGetText (Sender: TObject; ACol, ARow: Integer; var Text: WideString); begin if (Text='30.12.1899 00:00:00') then Text:= ''; end; procedure TformSchvaleniZak.colPoznGetHint (Sender: TObject; ACol, ARow: Integer; var Text: WideString); begin if (grd.Cells[colDatumObr.index, ARow]<>'') then if (grd.Cells[colDatumObr.index, ARow]<>'30.12.1899') then Text:= grd.Cells[colDatumObr.index, ARow]; end; procedure TformSchvaleniZak.colRoleGetHint (Sender: TObject; ACol, ARow: Integer; var Text: WideString); begin if (grd.Cells[colAutor.Index, ARow]<>'') then // and (ACol=colRole.Index) Text:= grd.Cells[colAutor.Index, ARow]; end; procedure TformSchvaleniZak.FormClose (Sender: TObject; var Action: TCloseAction); var lSQL: string; begin if (tbl.Active) then tbl.Close; lSQL:= 'IF OBJECT_ID(''dbo.TabZakazka_EXT'', N''U'') IS NOT NULL' + CRLF + ' IF EXISTS(SELECT ID FROM ' + tblZakE + ' WHERE ID=' + idZakazky.ToString + ')'; lSQL:= lSQL + CRLF + ' UPDATE ' + tblZakE + ' SET _SchvaleniZakazky_Lock=NULL WHERE ID=' + idZakazky.ToString + ' AND _SchvaleniZakazky_Lock=' + uzivId.ToString; Helios.ExecSQL(lSQL); Action:= caFree; end; procedure TformSchvaleniZak.FormShow (Sender: TObject); var lSQL, infoTxt: string; dVypPrSub, dVypPrFin, sTemp: string; dPlanPrSub, dPlanPrFin: string; begin uzivId:= Helios.UserId; idZakazky:= 0; VdoProVzo:= false; cbZmenaDatumKonZakazka.Visible:= false; jeTestDb:= false; if (helUtils.SQLObjectExists(Helios, Helios.SystemDB + '.dbo.TabDBHelios')) then jeTestDb:= helUtils.sqlExistsTestGeneral (Helios, 'SELECT 1 FROM ' + Helios.SystemDB + '.dbo.TabDBHelios WHERE SysJmeno=N' + Helios.CurrentDB.QuotedString + ' AND TypDB=4'); self.Caption:= ' Schválení zakázky' + IfThen(jeTestDB, ' (TEST DB)', ''); szKmeneSub:= ''; // zdroj ID jsou vyrobni prikazy if (id>0) and (Helios.BrowseID=bidVyrPrik) then idZakazky:= helUtils.getHeliosIntVal (Helios, 0, 'SELECT IDZakazka FROM ' + tblVPr + ' WHERE ID=' + id.ToString); if (Length(arrID)>0) and (Helios.BrowseID=bidVyrPrik) then begin sTemp:= helUtils.getHeliosStrVal (Helios, '', 'SELECT IDZakazka FROM ' + tblVPr + ' WHERE ID IN (' + helUtils.ArrayToString (arrID, ',', true) + ')'); SetLength(arrID, 0); if (sTemp<>'') then arrID:= helUtils.StrToArrayInt (sTemp); end; // zdroj ID je vyrobni plan if (id>0) and (Helios.BrowseID=bidVyrPlan) then idZakazky:= helUtils.getHeliosIntVal (Helios, 0, 'SELECT IDZakazka FROM ' + tblPlan + ' WHERE ID=' + id.ToString); if (Length(arrID)>0) and (Helios.BrowseID=bidVyrPlan) then begin sTemp:= helUtils.getHeliosStrVal (Helios, '', 'SELECT IDZakazka FROM ' + tblPlan + ' WHERE ID IN (' + helUtils.ArrayToString (arrID, ',', true) + ')'); SetLength(arrID, 0); if (sTemp<>'') then arrID:= helUtils.StrToArrayInt (sTemp); end; // zdroj ID jsou zakazky if (id>0) and (Helios.BrowseID=bidZak) then idZakazky:= id; cisZakazky:= ''; if (idZakazky>0) then cisZakazky:= helUtils.getHeliosStrVal(Helios, '', 'SELECT CisloZakazky FROM ' + tblZak + ' WHERE ID=' + idZakazky.ToString); jeVzorek:= helUtils.IfThenBool (LeftStr(cisZakazky,2).ToUpper='VZ', true, false); lSQL:= 'IF NOT EXISTS (SELECT 1 FROM ' + tblSchvalZak + ' WHERE IDZakazka=' + idZakazky.ToString + ') INSERT ' + tblSchvalZak; lSQL:= lSQL + ' (IDZakazka' + IfThen(jeVzorek, ', TypZakazky', '') + ') SELECT ' + idZakazky.ToString + IfThen (jeVzorek, ', 13', ''); Helios.ExecSQL (lSQL); lSQL:= 'SELECT TOP(1) p.SkupZbo FROM ' + tblPZ + ' p INNER JOIN ' + tblDZ + ' d ON (d.ID=p.IDDoklad) INNER JOIN ' + tblZak + ' z ON (z.CisloZakazky='; lSQL:= lSQL + 'ISNULL(p.CisloZakazky,d.CisloZakazky)) WHERE d.DruhPohybuZbo IN (9,6) AND z.ID=' + idZakazky.ToString; szKmene:= helUtils.getHeliosStrVal(Helios, '', lSQL); edtRegCis.EditLabel.Caption:= 'Reg.číslo' + IfThen(szKmene<>'', ' (SK ' + szKmene + ')', '') + ':'; jeObrobek:= (szKmene='VOB'); idPrikazFin:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT TOP(1) ID FROM ' + tblPrikaz + ' WHERE IDPrikazVyssi IS NULL AND IDZakazka=' + idZakazky.ToString); lSQL:= 'SELECT TOP(1) p.ID FROM ' + tblPrikaz + ' p INNER JOIN ' + tblKZ + ' k ON (k.ID=p.IDTabKmen) WHERE p.IDPrikazVyssi=' + idPrikazFin.ToString + ' AND p.IDZakazka=' + idZakazky.ToString; lSQL:= lSQL + ' AND k.SkupZbo NOT IN (N''VDO'')'; idPrikazSub:= helUtils.getHeliosIntVal(Helios, 0, lSQL); szKmeneSub:= helUtils.getHeliosStrVal(Helios, '', 'SELECT k.SkupZbo FROM ' + tblPrikaz + ' p INNER JOIN ' + tblKZ + ' k ON (k.ID=p.IDTabKmen) WHERE p.ID=' + idPrikazSub.ToString); currRole:= ''; if (helUtils.HeliosObjectExists(Helios,helUtils.VratTableName(tblSchvalZakRole))) then begin with Helios.OpenSQL('SELECT TOP(1) Role FROM ' + tblSchvalZakRole + ' WHERE Login=SUSER_SNAME() ORDER BY Priorita') do if (RecordCount=1) then edtRole.Text:= VarToStr(FieldValues(0)); currRole:= UpperCase(edtRole.Text); if (szKmene<>'VOB') and (currRole='OBR') then edtRole.Text:= ''; edtPoznamka.SetFocus; end; cbJenVDO.Enabled:= false; if (currRole='REP') or (currRole='VZO') then cbJenVDO.Enabled:= true; if (currRole='VDO') then begin cbZmenaDatumKonZakazka.Visible:= true; cbJenVDO.Enabled:= true; cbJenVDO.Caption:= ' pro OBR'; lblDatObr.Caption:= 'Datum ' + currRole; lblDatObr.Visible:= true; lSQL:= 'SELECT 1 FROM ' + tblVPr + ' WHERE IDPrikazVyssi IS NULL AND IDZakazka=' + idZakazky.ToString; if (helUtils.sqlExistsTestGeneral(Helios, lSQL)) then edtDatObr.Date:= helUtils.getHeliosDateTimeVal(Helios, Now, lSQL.Replace(' 1 ', ' TOP(1) Plan_ukonceni_X')) else edtDatObr.Date:= Now; edtDatObr.Visible:= true; end; if (Length(arrID)>1) and (idZakazky=0) then edtZakazka.Text:= '(víc zakázek)'; if (currRole='OBR') then begin edtDatObr.DateTime:= Now; if (helUtils.HeliosObjectExists(Helios, 'dbo.ef_HDC_VyrPrikaz_VypocteneUkonceni', '')) and (szKmeneSub='P51') then begin lSQL:= 'SELECT TOP(1) ISNULL(dbo.ef_HDC_VyrPrikaz_VypocteneUkonceni(p.ID, 1),0) AS Datum FROM ' + tblPrikaz + ' p INNER JOIN ' + tblKZ + ' k ON (k.ID=p.IDTabKmen) WHERE p.IDZakazka=' + idZakazky.ToString + ' AND k.SkupZbo=N''P51'''; sTemp:= DateTimeToStr(helUtils.getHeliosDateTimeVal(Helios, c_UnassignedDate, lSQL)); if (sTemp='01.01.1900') then edtDatObr.DateTime:= Now else edtDatObr.DateTime:= helUtils.getHeliosDateTimeVal(Helios, c_UnassignedDate, lSQL); lblDatObr.Visible:= true; edtDatObr.Visible:= true; end; { lSQL:= 'SELECT MAX(X.Cas) FROM (' + 'SELECT KPP.CasDo AS Cas FROM dbo.TabKapacPlan KP INNER JOIN dbo.TabKapacPlanPol KPP ON (KPP.IDKapacPlan=KP.ID' + ' AND KPP.IDPrikaz IN (SELECT ID FROM dbo.TabPrikaz WHERE IDZakazka=' + idZakazky.ToString + ' AND Rada=N''231-51''))' + ' INNER JOIN dbo.TabPrikaz VP ON (VP.ID=KPP.IDPrikaz) WHERE KP.Aktivni=1' + ' UNION ALL ' + 'SELECT DKP.CCasDo FROM TabAdvKapacPlan AKP INNER JOIN TabAdvKPDavky DKP ON (DKP.IDAdvKapacPlan=AKP.ID' + ' AND DKP.IDPrikaz IN (SELECT ID FROM dbo.TabPrikaz WHERE IDZakazka=' + idZakazky.ToString + ' AND Rada=N''231-51''))' + ' INNER JOIN dbo.TabPrikaz VP ON (VP.ID=DKP.IDPrikaz) WHERE AKP.Aktivni=1' + ' ) X'; edtDatObr.DateTime:= helUtils.getHeliosDateTimeVal(Helios, Now(), lSQL); } lblDatObr.Visible:= true; edtDatObr.Visible:= true; end; if not(tbl.Active) then tbl.Active:= true; btnAdd.Enabled:= (edtRole.Text<>''); if (idZakazky>0) and (helUtils.HeliosObjectExists(Helios,helUtils.VratTableName(tblSchvalZakHist))) then NactiHistorii; lSQL:= 'IF OBJECT_ID(''dbo.TabZakazka_EXT'', N''U'') IS NOT NULL' + CRLF + 'IF NOT EXISTS(SELECT ID FROM ' + tblZakE + ' WHERE ID=' + idZakazky.ToString + ')'; lSQL:= lSQL + CRLF + 'INSERT ' + tblZakE + ' (ID) VALUES (' + idZakazky.ToString + ')' + CRLF + 'UPDATE ' + tblZakE + ' SET _SchvaleniZakazky_Lock='; lSQL:= lSQL + uzivId.ToString + ' WHERE ID=' + idZakazky.ToString; Helios.ExecSQL(lSQL); lSQL:= 'SELECT 1 FROM ' + tblZak + ' z INNER JOIN ' + tblZakE + ' ze ON (ze.ID=z.ID) WHERE z.ID=' + idZakazky.ToString + ' AND ISNULL(ze._SchvaleniZakazky_Lock,0)<>' + uzivId.ToString; zamek:= helUtils.sqlExistsTestGeneral(Helios, lSQL); currZamek:= zamek; if not(zamek) then begin if (currRole='OBR') or (currRole='VDO') or (currRole='VZO') then cbJenVDO.Enabled:= true; end else cbJenVDO.Enabled:= false; if (currRole='VZO') then begin cbJenVDO.Checked:= true; cbJenVDO.Enabled:= false; cbStav.Items.Add('Dotaz'); cbStav.ItemIndex:= cbStav.Items.Count-1; if not(jeVzorek) then btnOK.Enabled:= false; end; if (currRole='VDO') and not(jeObrobek) and (prevRole='VZO') and (jeVzorek) then begin cbJenVDO.Caption:= ' pro VZO'; cbJenVDO.Enabled:= false; cbJenVDO.Checked; VdoProVzo:= true; end; if (currRole='VDO') and (jeVzorek) and (jeObrobek) and (prevRole='VZO') then begin cbJenVDO.Checked:= true; end; infoTxt:= ''; dVypPrSub:= ''; dPlanPrSub:= ''; dVypPrFin:= ''; dPlanPrFin:= ''; lSQL:= 'SELECT TOP(1) p.Plan_ukonceni_X FROM ' + tblPrikaz + ' p INNER JOIN ' + tblKZ + ' k ON (k.ID=p.IDTabKmen) WHERE p.IDZakazka=' + idZakazky.ToString + ' AND p.ID=' + idPrikazSub.ToString; dPlanPrSub:= DateTimeToStr(helUtils.getHeliosDateTimeVal(Helios, c_UnassignedDate, lSQL)); if (dPlanPrSub.Contains('.20')) then begin dPlanPrSub:= dPlanPrSub.Replace('.0', '.'); if (dPlanPrSub.StartsWith('0')) then dPlanPrSub:= MidStr(dPlanPrSub, 2, 20); end; lSQL:= 'SELECT TOP(1) p.Plan_ukonceni_X FROM ' + tblPrikaz + ' p INNER JOIN ' + tblKZ + ' k ON (k.ID=p.IDTabKmen) WHERE p.IDZakazka=' + idZakazky.ToString + ' AND p.ID=' + idPrikazFin.ToString; dPlanPrFin:= DateTimeToStr(helUtils.getHeliosDateTimeVal(Helios, c_UnassignedDate, lSQL)); if (dPlanPrFin.Contains('.20')) then begin dPlanPrFin:= dPlanPrFin.Replace('.0', '.'); if (dPlanPrFin.StartsWith('0')) then dPlanPrFin:= MidStr(dPlanPrFin, 2, 20); end; if (helUtils.HeliosObjectExists(Helios, 'dbo.ef_HDC_VyrPrikaz_VypocteneUkonceni', '')) then begin lSQL:= 'SELECT TOP(1) dbo.ef_HDC_VyrPrikaz_VypocteneUkonceni(p.ID, 1) AS Datum FROM ' + tblPrikaz + ' p INNER JOIN ' + tblKZ + ' k ON (k.ID=p.IDTabKmen) WHERE p.IDZakazka=' + idZakazky.ToString + ' AND k.SkupZbo=N''P51'''; dVypPrSub:= helUtils.getHeliosStrVal(Helios, '', lSQL); if (dVypPrSub.Contains('.20')) then begin dVypPrSub:= dVypPrSub.Replace('.0', '.'); if (dVypPrSub.StartsWith('0')) then dVypPrSub:= MidStr(dVypPrSub, 2, 20); end; lSQL:= 'SELECT TOP(1) dbo.ef_HDC_VyrPrikaz_VypocteneUkonceni(p.ID, 1) AS Datum FROM ' + tblPrikaz + ' p INNER JOIN ' + tblKZ + ' k ON (k.ID=p.IDTabKmen) WHERE p.IDZakazka=' + idZakazky.ToString + ' AND k.SkupZbo=N' + szKmene.QuotedString; dVypPrFin:= helUtils.getHeliosStrVal(Helios, '', lSQL); if (dVypPrFin.Contains('.20')) then begin dVypPrFin:= dVypPrFin.Replace('.0', '.'); if (dVypPrFin.StartsWith('0')) then dVypPrFin:= MidStr(dVypPrFin, 2, 20); end; end; infoTxt:= ''; if (dPlanPrSub<>'') and (dPlanPrSub<>'00.00.0000') then infoTxt:= 'plán ' + dPlanPrSub; if (dVypPrSub<>'') and (dVypPrSub<>'00.00.0000') then infoTxt:= infoTxt + ' / výpočet ' + dVypPrSub; if (infoTxt<>'') then mInfo.Lines.Add(szKmeneSub + ': ' + infoTxt); infoTxt:= ''; if (dPlanPrFin<>'') and (dPlanPrFin<>'00.00.0000') then infoTxt:= 'plán ' + dPlanPrSub; if (dVypPrFin<>'') and (dVypPrFin<>'00.00.0000') then infoTxt:= infoTxt + ' / výpočet ' + dVypPrFin; if (infoTxt<>'') then mInfo.Lines.Add(szKmene + ': ' + infoTxt); mInfo.Visible:= (mInfo.Text<>''); btnAdd.Enabled:= not(lastRole=currRole); end; procedure TformSchvaleniZak.grdCellDblClick (Sender: TObject; ACol, ARow: Integer); var s: string; begin if (ACol=colPozn.Index) and (grd.RowCount>0) then if (grd.DataSource.DataSet.FieldByName('colPoznamkaR').AsInteger=1) then begin s:= grd.DataSource.DataSet.FieldByName('colPoznamka2').AsString; helUtils.FormMemo2 ('Poznámka', s, true, 14); end; end; procedure TformSchvaleniZak.grdViewHeaderMouseDown (Sender: TObject; Button: TMouseButton; Shift: TShiftState; Col, X, Y: Integer); begin // if (Button=mbLeft) then // ShowMessage('W: ' + grd.Columns.Item[Col].Width.ToString); end; end.