unit frmOdvLisX;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics, System.StrUtils,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.StdCtrls, Vcl.Mask, RzEdit, TBPackageU, ddPlugin_TLB, Vcl.ExtCtrls,
  Vcl.ComCtrls, Data.DB, Vcl.Grids, Vcl.DBGrids, VirtualDataSet, MemDS, VirtualTable, NxGridView6, NxColumns6,
  NxControls6, NxCustomGrid6, NxTypes6, NxVirtualGrid6, NxDBCells6, NxDBGrid6, NxDBColumns6, NxIntf6, NxEdit6,
  Vcl.Menus, XDBGrids;

const
  tblPlan = '[dbo].[_hdc_TabPlanNasazeniLisu]';
  tblPlanR = '[dbo].[_hdc_TabPlanNasazeniLisuR]';
  tblOdlitky = '[dbo].[_hdc_TabOdlitky]';

type
  TZavady = record
   poz: integer;
   kod: string;
   popis: string;
  end;

  TformOdvLisX = class(TForm)
    btnOK: TButton;
    btnStorno: TButton;
    lblZapisDat: TLabel;
    edtBarcode: TLabeledEdit;
    selBarcode: TButtonedEdit;
    edtCisZam: TLabeledEdit;
    selCisZam: TButtonedEdit;
    edtVstriku: TRzNumericEdit;
    Label1: TLabel;
    tbl: TVirtualTable;
    ds: TDataSource;
    grd: TNextDBGrid6;
    view: TNxReportGridView6;
    colRegCis: TNxDBTextColumn6;
    colNazev: TNxDBTextColumn6;
    colKsDobre: TNxDBNumberColumn6;
    tblIDPrPostup: TIntegerField;
    tblRegCis: TStringField;
    tblNazev: TStringField;
    tblKsDobre: TSmallintField;
    lblPrijmeni: TLabel;
    lblPlan: TLabel;
    tblIDVyrCS: TIntegerField;
    tblFormaSN: TStringField;
    tblIDradek: TIntegerField;
    tblIDnaradi: TIntegerField;
    tblKsZmetky1: TIntegerField;
    tblKsZmetky2: TIntegerField;
    tblKsZmetky3: TIntegerField;
    tblKsNaPlate: TIntegerField;
    colFormaSN: TNxDBTextColumn6;
    colZmetky1: TNxDBNumberColumn6;
    colZmetky2: TNxDBNumberColumn6;
    colZmetky3: TNxDBNumberColumn6;
    colKsVeForme: TNxDBNumberColumn6;
    Label2: TLabel;
    tblForma: TStringField;
    tblcolZakazka: TStringField;
    colZakazka: TNxDBTextColumn6;
    dsForma: TDataSource;
    tblFormaX: TVirtualTable;
    tblFormaXRegCis: TStringField;
    tblFormaXForma: TStringField;
    colForma: TNxDBComboBoxColumn6;
    grdX: TXDBGrid;
    procedure FormShow(Sender: TObject);
    procedure btnStornoClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure btnOKClick(Sender: TObject);
    procedure edtBarcodeExit(Sender: TObject);
    procedure selBarcodeClick(Sender: TObject);
    procedure selCisZamClick(Sender: TObject);
    procedure selCisZamExit(Sender: TObject);
    procedure edtCisZamExit(Sender: TObject);
    procedure viewGetCellColor(Sender: TObject; ACol, ARow: Integer;
      var CellColor: TColor; State: TNxCellPaintingState);
    procedure edtVstrikuExit(Sender: TObject);
    procedure colFormaSetCell(Sender: TObject; ACol, ARow: Integer;
      Cell: INxBaseCell);
    procedure grdCellClick(Sender: TObject; ACol, ARow: Integer;
      Button: TMouseButton);
   private
   public
    Helios: IHelios;
    jeTest: Boolean;
  end;

var
  formOdvLis: TformOdvLisX;
  idPlan, bidPlan: integer;
  oVar: OleVariant;
  zavady: TArray<TZavady>;

implementation
uses System.DateUtils, helUtils;
{$R *.dfm}


 procedure TformOdvLisX.btnOKClick(Sender: TObject);
  var lSQL, idcko: string;
  begin
   lblZapisDat.Visible:= true;
   Self.Repaint;
   btnOK.Enabled:= false;
   btnStorno.Enabled:= false;

   if (tbl.RecordCount>0) then
    begin
     lSQL:= 'INSERT #TabOdvodLis (IDPlan, CisZam, PocetVstriku) VALUES (' + idPlan.ToString + ', ' + edtCisZam.Text + ', ';
     lSQL:= lSQL + StringReplace(FloatToStr(edtVstriku.Value), ',', '.', [rfReplaceAll]) + ')' + CRLF + 'SELECT SCOPE_IDENTITY()';
     if (jeTest) then
      begin
       lSQL:= StringReplace(lSQL, '#TabOdv', 'dbo._temp_TabOdv', [rfReplaceAll]);
//       lSQL:= StringReplace(lSQL, 'tempdb..', '', [rfReplaceAll]);
      end;

     with Helios.OpenSQL(lSQL) do
      idcko:= VarToStr(FieldValues(0));

     tbl.First;
     while not(tbl.Eof) do
      begin
       lSQL:= 'INSERT #TabOdvodLisR (IDHlava, IDRadek, IDVyrCis, KsDobre, KsZmetky1, KodZav1, KsZmetky2, KodZav2, KsZmetky3, KodZav3, KsVeForme, IDPrPostup) VALUES (';
       lSQL:= lSQL + idcko + ', ' + tbl.FieldByName('IDradek').AsString;
       lSQL:= lSQL + ', NULL'; // IDVyrCis
       lSQL:= lSQL + ', ' + StringReplace(tbl.FieldByName('KsDobre').AsString, '', '', [rfReplaceAll]);
       lSQL:= lSQL + ', ' + StringReplace(tbl.FieldByName('KsZmetky1').AsString, '', '', [rfReplaceAll]) + ', N' + QuotedStr(IfThen(Length(zavady)>0, zavady[0].kod, ''));
       lSQL:= lSQL + ', ' + StringReplace(tbl.FieldByName('KsZmetky2').AsString, '', '', [rfReplaceAll]) + ', N' + QuotedStr(IfThen(Length(zavady)>1, zavady[1].kod, ''));
       lSQL:= lSQL + ', ' + StringReplace(tbl.FieldByName('KsZmetky3').AsString, '', '', [rfReplaceAll]) +  ', N' + QuotedStr(IfThen(Length(zavady)>2, zavady[2].kod, ''));
       lSQL:= lSQL + ', ' + StringReplace(tbl.FieldByName('KsVeForme').AsString, '', '', [rfReplaceAll]) + ', ' + tbl.FieldByName('IDPostup').AsString + ')';
       if (jeTest) then
        begin
         lSQL:= StringReplace(lSQL, '#TabOdv', 'dbo._temp_TabOdv', [rfReplaceAll]);
//         lSQL:= StringReplace(lSQL, 'tempdb..', '', [rfReplaceAll]);
        end;
       Helios.ExecSQL(lSQL);
       tbl.Next;
      end;
    end;

   if not(jeTest) then
    Helios.ExecSQL('IF OBJECT_ID(''dbo.ep_Vosk_PlanLisu_Odvod'') IS NOT NULL EXEC dbo.ep_Vosk_PlanLisu_Odvod');

   lblZapisDat.Visible:= false;
   Self.Repaint;
   btnOK.Enabled:= true;
   btnStorno.Enabled:= true;
   Close;
  end;




 procedure TformOdvLisX.btnStornoClick(Sender: TObject);
  begin
   Close;
  end;




 procedure TformOdvLisX.colFormaSetCell(Sender: TObject; ACol, ARow: Integer; Cell: INxBaseCell);
 var lSQL: string;
 begin
 end;




 procedure TformOdvLisX.edtBarcodeExit(Sender: TObject);
  var lSQL, formy, sncka, nDokl, sVPr: string;
      idPrikaz: integer;
  begin
   edtBarcode.Text:= Trim(edtBarcode.Text);
   edtBarcode.Text:= helUtils.StripChars(edtBarcode.Text, ['''', '"', '-', ';', ' ']);
   if (edtBarcode.Text<>'') then
    begin
     lblPlan.Caption:= '';
     idPlan:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT ID FROM ' + tblPlan + ' WHERE BarCode=N' + QuotedStr(edtBarcode.Text));
     if (idPlan>0) then
      begin
       if not(tblFormaX.Active) then
        tblFormaX.Open;
       tblFormaX.Clear;

       lSQL:= 'SELECT Lis + N'' / '' + CONVERT(nvarchar, DATEPART(day,Datum)) + N''.'' + CONVERT(nvarchar, DATEPART(month,Datum)) + N''.''';
       lSQL:= lSQL + ' + CONVERT(nvarchar, DATEPART(year,Datum)) + N'' / '' + Smena FROM ' + tblPlan + ' WHERE ID=' + idPlan.ToString;
       lblPlan.Caption:= helUtils.getHeliosStrVal(Helios, '', lSQL);
       lSQL:= 'SELECT r.IDPrPostup, k.RegCis, k.Nazev1, r.IDvyrcis, vc.Nazev1, r.ID, nv.Naradi, ISNULL(o.KsVeForme,0), nvk.RegCis, nv.Doklad, vp.ID, z.CisloZakazky FROM ' + tblPlanR;
       lSQL:= lSQL + ' r INNER JOIN ' + tblPrPost + ' p ON (p.ID=r.IDPrPostup) INNER JOIN ' + tblPrikaz + ' vp ON (p.IDPrikaz=vp.ID) INNER JOIN ' + tblKZ;
       lSQL:= lSQL + ' k ON (k.ID=vp.IdTabKmen) LEFT JOIN ' + tblOdlitky + ' o ON (o.Odlitek=k.RegCis) LEFT JOIN ' + tblPrNVazby;
       lSQL:= lSQL + ' nv ON (p.IDPrikaz=nv.IDPrikaz AND p.Doklad=nv.Doklad) LEFT JOIN dbo.TabVyrCS vc ON (vc.ID=r.IDvyrcis) LEFT JOIN ' + tblKZ;
       lSQL:= lSQL + ' nvk ON (nv.Naradi=nvk.Id) LEFT JOIN ' + tblZak + ' z ON (z.ID=vp.IDZakazka) WHERE r.IDHlava=' + idPlan.ToString + ' ORDER BY r.ID';
       with Helios.OpenSQL(lSQL) do
        if (RecordCount>0) then
         begin
          if not(tbl.Active) then
           tbl.Open;
          tbl.Edit;
          tbl.Clear; //Fields;
          First;
          while not(Eof) do
           begin
            tbl.Append;
            tbl.FieldByName('IDPostup').AsString:= VarToStr(FieldValues(0));
            tbl.FieldByName('RegCis').AsString:= VarToStr(FieldValues(1));
            tbl.FieldByName('Nazev').AsString:= VarToStr(FieldValues(2));
            tbl.FieldByName('KsDobre').AsExtended:= 0;
            tbl.FieldByName('IDVyrCS').AsInteger:= StrToInt(IfThen(VarToStr(FieldValues(3))='', '-1', VarToStr(FieldValues(3))));
            tbl.FieldByName('IDradek').AsInteger:= StrToInt(VarToStr(FieldValues(5)));
            tbl.FieldByName('IDnaradi').AsInteger:= StrToInt(IfThen(VarToStr(FieldValues(6))='', '-1', VarToStr(FieldValues(6))));


            tbl.FieldByName('Forma').AsString:= tbl.FieldByName('RegCis').AsString; //VarToStr(FieldValues(8));
            tblFormaX.Append;
             tblFormaX.FieldByName('RegCis').AsString:= tbl.FieldByName('RegCis').AsString;
             tblFormaX.FieldByName('Forma').AsString:= 'Forma1';
            tblFormaX.Post;

            tblFormaX.Append;
             tblFormaX.FieldByName('RegCis').AsString:= tbl.FieldByName('RegCis').AsString;
             tblFormaX.FieldByName('Forma').AsString:= 'Forma2';
            tblFormaX.Post;

            sncka:= '';
            nDokl:= VarToStr(FieldValues(9));
            sVPr:= VarToStr(FieldValues(10));
            if (nDokl<>'') then
             begin
              lSQL:= 'SELECT STRING_AGG(ne._VyrobniCislo, N'','') FROM ' + tblPrNVazby + ' n INNER JOIN ' + tblPrNVazbyE + ' ne ON (ne.ID=n.ID) WHERE n.Doklad=';
              lSQL:= lSQL + nDokl +  ' AND n.IDPrikaz=' + sVPr;
              sncka:= helUtils.getHeliosStrVal(Helios, '', lSQL);
             end;

            tbl.FieldByName('FormaSN').AsString:= VarToStr(FieldValues(4));
            if (tbl.FieldByName('FormaSN').AsString='') and (tbl.FieldByName('IDnaradi').AsInteger>0) then
             tbl.FieldByName('FormaSN').AsString:= helUtils.getHeliosStrVal(Helios, '', 'SELECT Nazev1 FROM ' + tblKZ + ' WHERE ID=' + tbl.FieldByName('IDnaradi').AsInteger.ToString);
            tbl.FieldByName('KsZmetky1').AsExtended:= 0;
            tbl.FieldByName('KsZmetky2').AsExtended:= 0;
            tbl.FieldByName('KsZmetky3').AsExtended:= 0;
            tbl.FieldByName('KsVeForme').AsInteger:= StrToInt(VarToStr(FieldValues(7)));

            tbl.FieldByName('Zakazka').AsString:= VarToStr(FieldValues(11));
            tbl.Post;
            Next;
           end;
          tbl.RecNo:= 1;
          grd.Repaint;
         end;
      end;
     edtCisZam.SetFocus;
    end;
  end;




 procedure TformOdvLisX.edtCisZamExit(Sender: TObject);
  var c: integer;
  begin
   lblPrijmeni.Caption:= '';
   edtCisZam.Text:= Trim(edtCisZam.Text);
   edtCisZam.Text:= helUtils.StripChars(edtCisZam.Text, ['''', '"', '-', ';', ' ']);
   if (edtCisZam.Text<>'') then
    if TryStrToInt(edtCisZam.Text, c) then
      begin
       if (helUtils.HeliosExistsTest(Helios, tblCZam, 'Cislo=' + c.ToString)) then
         begin
          lblPrijmeni.Caption:= helUtils.getHeliosStrVal(Helios, '', 'SELECT Prijmeni FROM ' + tblCZam + ' WHERE Cislo=' + c.ToString);
          edtCisZam.Text:= StringOfChar('0', 6-Length(edtCisZam.Text)) + edtCisZam.Text;
          edtVstriku.SetFocus;
         end
        else
         begin
          Helios.Error(#1'Zadané osobní číslo nebylo nalezeno !'#1);
          edtCisZam.SetFocus;
          edtCisZam.SelectAll;
         end;
      end
     else
      begin
       Helios.Error(#1'Zadané znaky nejsou číslo'#1);
       edtCisZam.SetFocus;
       edtCisZam.SelectAll;
      end;
  end;




 procedure TformOdvLisX.edtVstrikuExit(Sender: TObject);
  var cell: TGridCoord;
  begin
   if (grd.RowCount>0) then
    begin
    end;
  end;




 procedure TformOdvLisX.FormClose(Sender: TObject; var Action: TCloseAction);
  begin
   if (tbl.Active) then
    tbl.Close;
   Action:= caFree;
  end;




 procedure TformOdvLisX.FormShow(Sender: TObject);
  var lSQL: string;
      i, i2: integer;
  begin
   self.ParentWindow:= Helios.MainApplicationHandle;
   self.Icon.Handle:= Helios.MainApplicationIconHandle;
   for i:=0 to Self.ComponentCount-1 do
     begin
      if (Self.Components[i] is TButton) then
       begin
        (Self.Components[i] as TButton).Font.Name:= Helios.Font;
        (Self.Components[i] as TButton).Font.Height:= Helios.FontHeight;
       end;
      if (Self.Components[i] is TButtonedEdit) then
       begin
//        (Self.Components[i] as TTB_3Button).Width:= 30;
//        (Self.Components[i] as TTB_3Button).Height:= 30;
       end;
     end;

   Helios.ExecSQL('IF OBJECT_ID(N''dbo._temp_TabOdvodLis'') IS NOT NULL DELETE FROM dbo._temp_TabOdvodLis WHERE Autor=SUSER_SNAME()');
   Helios.ExecSQL('IF OBJECT_ID(N''dbo._temp_TabOdvodLisR'') IS NOT NULL DELETE FROM dbo._temp_TabOdvodLisR WHERE IDHlava NOT IN (SELECT ID FROM dbo._temp_TabOdvodLis)');
   Helios.ExecSQL('IF OBJECT_ID(N''dbo._temp_TabOdvodLis'') IS NOT NULL IF NOT EXISTS(SELECT * FROM dbo._temp_TabOdvodLis) DROP TABLE dbo._temp_TabOdvodLis');
   Helios.ExecSQL('IF OBJECT_ID(N''dbo._temp_TabOdvodLisR'') IS NOT NULL IF NOT EXISTS(SELECT * FROM dbo._temp_TabOdvodLisR) DROP TABLE dbo._temp_TabOdvodLisR');

   Helios.ExecSQL('IF OBJECT_ID(N''tempdb..#TabOdvodLis'') IS NOT NULL DROP TABLE #TabOdvodLis');
   Helios.ExecSQL('IF OBJECT_ID(N''tempdb..#TabOdvodLisR'') IS NOT NULL DROP TABLE #TabOdvodLisR');

   lSQL:= 'IF OBJECT_ID(N''tempdb..#TabOdvodLis'') IS NULL CREATE TABLE #TabOdvodLis (ID INT IDENTITY(1,1) NOT NULL, IDPlan INT NOT NULL, CisZam INT NOT NULL,';
   lSQL:= lSQL +' PocetVstriku SMALLINT NOT NULL DEFAULT 0, Autor NVARCHAR(80) NOT NULL DEFAULT SUSER_SNAME())' + CRLF;
   lSQL:= lSQL + 'IF OBJECT_ID(N''tempdb..#TabOdvodLisR'') IS NULL CREATE TABLE #TabOdvodLisR (IDHlava INT NOT NULL, IDRadek INT, KsDobre SMALLINT NOT NULL DEFAULT 0,';
   lSQL:= lSQL + ' IDVyrCis INT, IDNaradi INT, IDPrPostup INT, KsZmetky1 INT NOT NULL DEFAULT 0, KodZav1 NVARCHAR(15) DEFAULT N'''', KsZmetky2 INT NOT NULL DEFAULT 0,';
   lSQL:= lSQL + ' KodZav2 NVARCHAR(15) DEFAULT N'''', KsZmetky3 INT NOT NULL DEFAULT 0, KodZav3 NVARCHAR(15) DEFAULT N'''', KsVeForme SMALLINT DEFAULT 1,';
   lSQL:= lSQL + ' FormaSN NVARCHAR(100) DEFAULT N'''')';
   if (jeTest) then
    begin
     lSQL:= StringReplace(lSQL, '#TabOdv', 'dbo._temp_TabOdv', [rfReplaceAll]);
     lSQL:= StringReplace(lSQL, 'tempdb..', '', [rfReplaceAll]);
    end;

   Helios.ExecSQL(lSQL);

   SetLength(zavady, 0);
   lSQL:= 'SELECT TOP(3) z.kod, e._PopisEvidence, e._PoziceEvidence FROM ' + tblCZavadE + ' e INNER JOIN ' + tblCZavad + ' z ON (z.ID=e.ID)';
   lSQL:= lSQL + ' WHERE CHARINDEX(N''- VOSK'', CONVERT(nvarchar,Poznamka))>0 AND e._PoziceEvidence IS NOT NULL ORDER BY e._PoziceEvidence';
   with Helios.OpenSQL(lSQL) do
    if (RecordCount>0) then
     begin
      SetLength(zavady, RecordCount);
      i:= 0;
      First;
      while not(EOF) do
       begin
        zavady[i].kod:= VarToStr(FieldValues(0));
        zavady[i].popis:= VarToStr(FieldValues(1));
        zavady[i].poz:= StrToInt(VarToStr(FieldValues(2)));
        Inc(i);
        Next;
       end;
     end;

   if (Length(zavady)>0) then
    begin
     for i:=0 to Length(zavady)-1 do
      begin
       i2:= grd.Columns.ItemBy['colZmetky' + (i+1).ToString].Index;
       grd.Columns.Item[i2].Header.Caption:= 'Z ' + zavady[i].popis;
       grd.Columns.Item[i2].Enabled:= true;
      end;
    end;
   grd.Repaint;

  end;



 procedure TformOdvLisX.grdCellClick(Sender: TObject; ACol, ARow: Integer; Button: TMouseButton);
  var lSQL: string;
  begin
   if (ACol=colForma.Index) then
    begin

    end;
  end;




 procedure TformOdvLisX.selBarcodeClick(Sender: TObject);
  begin
   edtBarcode.Text:= Trim(edtBarcode.Text);
   oVar:= edtBarcode.Text;
   bidPlan:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT 100000+Cislo FROM ' + tblObecPrehled + ' WHERE NazevSys=N''hvw_PlanNasazeniLisu''');
   if (bidPlan>0) then
    if Helios.Prenos(bidPlan, 'hvw_PlanNasazeniLisu.Barcode', oVar, 'hvw_PlanNasazeniLisu.Ukonceno=0', 'Vyberte plán', true) then
     begin
      edtBarcode.Text:= VarToStr(oVar);
      edtBarcodeExit(Sender);
     end;
   edtCisZam.SetFocus;
  end;




 procedure TformOdvLisX.selCisZamClick(Sender: TObject);
  begin
   if Helios.Prenos(bidCisZam, 'TabCisZam.Cislo', oVar, '', 'Vyberte zaměstnance', true) then
    begin
     edtCisZam.Text:= VarToStr(oVar);
     selCisZamExit(Sender);
    end;
  end;




 procedure TformOdvLisX.selCisZamExit(Sender: TObject);
  begin
   edtCisZam.Text:= helUtils.StripChars(edtCisZam.Text, ['''', '"', '-', ';', ' ']);
   edtCisZamExit(Sender);
   edtVstriku.SetFocus;
  end;



 procedure TformOdvLisX.viewGetCellColor(Sender: TObject; ACol, ARow: Integer; var CellColor: TColor; State: TNxCellPaintingState);
  begin
   if (grd.RowCount>=ARow+1) then
    if (ACol=colKsVeForme.Index) then
     if (grd.Cells[ACol, ARow]='0') then
       begin
        CellColor:= clRed;
        grd.Cell[ACol, ARow].Enabled:= true;
       end
      else
       begin
//        CellColor:= clWindow;
        grd.Cell[ACol, ARow].Enabled:= false;
       end;
  end;

end.