unit frmDavkyObecne;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics, ddPlugin_TLB,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Data.DB, MemDS, VirtualTable, NxTypes6, NxGridView6, NxColumns6,
  NxControls6, NxCustomGrid6, NxVirtualGrid6, NxDBGrid6, NxDBColumns6,
  Vcl.StdCtrls, TBPackageU, Vcl.Buttons, Vcl.Mask, RzEdit, System.ImageList,
  Vcl.ImgList, Vcl.ExtCtrls;

const
  tblDavky = '[dbo].[_hdc_TabDavky]';

type
  TformDavkyObecne = class(TForm)
    grd: TNextDBGrid6;
    dSource: TDataSource;
    tbl: TVirtualTable;
    tblIDDavka: TIntegerField;
    grdView: TNxReportGridView6;
    tblIDPrikaz: TIntegerField;
    tblVyrPrikaz: TStringField;
    tblMnozstvi: TFloatField;
    tblMJ: TStringField;
    colVyrPrikaz: TNxDBTextColumn6;
    tblNazev: TStringField;
    tblRegCis: TStringField;
    colRegCis: TNxDBTextColumn6;
    colPopis: TNxDBTextColumn6;
    colMnozstvi: TNxDBNumberColumn6;
    colMJ: TNxDBTextColumn6;
    btnOK: TButton;
    btnGenPrevod: TButton;
    edtPrikaz: TEdit;
    selPrikaz: TButtonedEdit;
    lblDavka: TLabel;
    tblTavba: TStringField;
    colTavba: TNxDBTextColumn6;
    edtMnoz: TRzNumericEdit;
    cbMJ: TComboBox;
    Label1: TLabel;
    edtTavba: TEdit;
    selTavba: TButtonedEdit;
    Label2: TLabel;
    btnAdd: TButton;
    imgs: TImageList;
    tblIDRodic: TIntegerField;
    lblPrikaz: TLabel;
    tblSkladem: TFloatField;
    colSkladem: TNxDBNumberColumn6;
    lblZapisDat: TLabel;
    procedure grdViewGetCellColor (Sender: TObject; ACol, ARow: Integer; var CellColor: TColor; State: TNxCellPaintingState);
    procedure FormShow (Sender: TObject);
    procedure edtPrikazExit (Sender: TObject);
    procedure selPrikazClick (Sender: TObject);
    procedure btnAddClick (Sender: TObject);
    procedure FormClose (Sender: TObject; var Action: TCloseAction);
    procedure selTavbaClick (Sender: TObject);
    procedure btnOKClick (Sender: TObject);
   private
    procedure NactiDoklad;
    procedure NactiPrikaz;
   public
    Helios: IHelios;
    druhDavky: Integer;
    jeTest: Boolean;
    idRodic, id: integer;
  end;

var
  formDavkyObecne: TformDavkyObecne;
  rc, popis, sklad, verText: string;
  oVar: OleVariant;
  idVPr, idTavba, idKZ, bidTavby, cisloDavky, rokDavky: integer;
  mn: Extended;

implementation

uses System.StrUtils, System.DateUtils, helUtils;

{$R *.dfm}

 procedure TformDavkyObecne.NactiPrikaz;
  var s,r,n: string;
  begin
   if (idVPr>0) then
    begin
     lblPrikaz.Caption:= '';
     s:= '';
     r:= '';
     n:= '';
     sklad:= '';
     with Helios.OpenSQL('SELECT k.SkupZbo, k.RegCis, k.Nazev1, p.Sklad, p.IDTabKmen FROM ' + tblPrikaz + ' p INNER JOIN ' + tblKZ + ' k ON (k.ID=p.IDTabKmen) WHERE p.ID=' + idVPr.ToString) do
      begin
       s:= VarToStr(FieldValues(0));
       r:= VarToStr(FieldValues(1));
       n:= VarToStr(FieldValues(2));
       sklad:= VarToStr(FieldValues(3));
       idKZ:= StrToInt(VarToStr(FieldValues(4)));
       lblPrikaz.Caption:= r + ' / ' + n;
      end;
     rc:= r;
     popis:= n;
    end;
 end;



 procedure TformDavkyObecne.btnAddClick(Sender: TObject);
  var lSQL: string;
  begin
   if (idVPr>0) then
    begin
     if not(tbl.Active) then
      tbl.Open;
     tbl.Append;
      tbl.FieldByName('IDDavka').AsInteger:= 0;
      tbl.FieldByName('IDRodic').AsInteger:= idRodic;
      tbl.FieldByName('IDPrikaz').AsInteger:= idVPr;
      tbl.FieldByName('RegCis').AsString:= rc;
      tbl.FieldByName('Nazev').AsString:= popis;
      tbl.FieldByName('VyrPrikaz').AsString:= edtPrikaz.Text;
      tbl.FieldByName('Mnozstvi').AsExtended:= edtMnoz.Value;
      lSQL:= 'SELECT Mnozstvi FROM ' + tblSS + ' WHERE IDSklad=N' + QuotedStr(sklad) + ' AND IDKmenZbozi=' + idKZ.ToString;
      tbl.FieldByName('Skladem').AsExtended:= helUtils.getHeliosFloatVal(Helios, 0, lSQL);
      tbl.FieldByName('MJ').AsString:= cbMJ.Text;
      tbl.FieldByName('Tavba').AsString:= edtTavba.Text;
     tbl.Post;
     idVPr:= 0;
     edtPrikaz.Text:= '';
     lblPrikaz.Caption:= '';
     edtTavba.Text:= '';
     edtMnoz.Value:= 0;
     cbMJ.ItemIndex:= cbMJ.Items.IndexOf('ks');
     edtPrikaz.SetFocus;
     grd.DataSource.DataSet.Refresh;
    end;
  end;




 procedure TformDavkyObecne.btnOKClick(Sender: TObject);
  var lSQL, t: string;
      idD, poz: integer;
  begin
   lblZapisDat.Visible:= true;
   Self.Repaint;
   btnOK.Enabled:= false;
   btnGenPrevod.Enabled:= false;
   lblZapisDat.Caption:= 'Zápis dat do tabulky dávek...';

   if (tbl.RecordCount>0) then
    begin
     tbl.First;
     while not(tbl.Eof) do
      begin
       idD:= tbl.FieldByName('IDDavka').AsInteger;
       t:= tbl.FieldByName('Tavba').AsString;
       poz:= 0;
       if (idD=0) then
        begin
         lSQL:= 'SELECT MAX(Pozice) FROM ' + tblDavky + ' WHERE DruhDavky=' + druhDavky.ToString + ' AND Rok=' + rokDavky.ToString + ' AND Cislo=' + cisloDavky.ToString;
         poz:= 1 + helUtils.getHeliosIntVal(Helios, 0, lSQL);
         lSQL:= 'INSERT ' + tblDavky + ' (DruhDavky, Rok, Cislo, Pozice, Tavba) VALUES (' + druhDavky.ToString + ', ' + rokDavky.ToString + ', ';
         lSQL:= lSQL + cisloDavky.ToString + ', ' +  poz.ToString + ', N' + QuotedStr(t) + '); SELECT SCOPE_IDENTITY()';
         with Helios.OpenSQL(lSQL) do
          idD:= StrToInt(VarToStr(FieldValues(0)));
        end;
       if (poz=0) then
        poz:= helUtils.getHeliosIntVal(Helios, 1, 'SELECT Pozice FROM ' + tblDavky + ' WHERE ID=' + idD.ToString);
       lSQL:= 'INSERT #TabDavkaObecna (IDDavka, IDRodic, Pozice, IDPrikaz, Mnozstvi, Tavba) SELECT ' + idD.ToString + ', ' + tbl.FieldByName('IDRodic').AsString;
       lSQL:= lSQL + ', ' + poz.ToString;
       lSQL:= lSQL + ', ' + tbl.FieldByName('IDPrikaz').AsString + ', ' + StringReplace(tbl.FieldByName('Mnozstvi').AsString, '', '', [rfReplaceAll]) + ', N';
       lSQL:= lSQL + tbl.FieldByName('Tavba').AsString;
       if (jeTest) then
        lSQL:= StringReplace(lSQL, '#TabDa', 'dbo._temp_TabDa', [rfReplaceAll]);
       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;
   btnGenPrevod.Enabled:= true;
   Close;

  end;



 procedure TformDavkyObecne.edtPrikazExit(Sender: TObject);
  begin
   edtPrikaz.Text:= Trim(helUtils.StripChars(edtPrikaz.Text, ['''', '"', ';']));
   if (edtPrikaz.Text<>'') then
    begin
     idVPr:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT ID FROM ' + tblPrikaz + ' WHERE RadaPrikaz=N' + QuotedStr(edtPrikaz.Text));
     NactiPrikaz;
     edtTavba.SetFocus;
    end;
  end;



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



 procedure TformDavkyObecne.FormShow(Sender: TObject);
  var lSQL: string;
      i: integer;
  begin
   DefFontData.CharSet := Helios.Charset;
   DefFontData.Name    := Helios.Font;
   DefFontData.Height  := Helios.FontHeight;

   Screen.MenuFont.CharSet := DefFontData.CharSet;
   Screen.MenuFont.Name    := DefFontData.Name;
   Screen.MenuFont.Height  := DefFontData.Height;

   Self.Icon.Handle:= Helios.MainApplicationIconHandle;
   Self.Font.Name:= Helios.Font;
   Self.Font.Height:= Helios.FontHeight;

   verText:= GetFileVersion2(GetModuleName(HInstance));
   if (Length(verText)=12) then
    verText:= LeftStr(verText,9) + '0' + RightStr(verText,3);

   bidTavby:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT 100000+Cislo FROM ' + tblObecPrehled + ' WHERE NazevSys=N''hvw_TavbyPosledniData''');

{
   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;
    end;
}

   lSQL:= 'IF OBJECT_ID(N''dbo._temp_TabDavkaObecna'') IS NOT NULL DELETE FROM dbo._temp_TabDavkaObecna WHERE Autor=SUSER_SNAME()' + CRLF;
   lSQL:= lSQL + 'IF OBJECT_ID(N''dbo._temp_TabDavkaObecna'') IS NOT NULL IF NOT EXISTS(SELECT * FROM dbo._temp_TabDavkaObecna) DROP TABLE dbo._temp_TabDavkaObecna' + CRLF;
   lSQL:= lSQL + 'IF OBJECT_ID(N''tempdb..#TabDavkaObecna'') IS NOT NULL DROP TABLE #TabDavkaObecna';
   Helios.ExecSQL(lSQL);

   lSQL:= 'CREATE TABLE #TabDavkaObecna (IDDavka INT, IDRodic INT, Pozice INT, IDPrikaz INT, Mnozstvi NUMERIC(19,6), Tavba NVARCHAR(15), Autor NVARCHAR(80) DEFAULT SUSER_SNAME())';
   if (jeTest) then
    lSQL:= StringReplace(lSQL, '#TabD', 'dbo._temp_TabD', [rfReplaceAll]);
   Helios.ExecSQL(lSQL);

   rokDavky:= YearOf(Now);
   cisloDavky:= 1;
   idRodic:= helUtils.getHeliosIntVal(Helios, 0, 'SELECT IDRodic FROM ' + tblDavky + ' WHERE ID=' + id.ToString);
   if (idRodic=0) and (id=0) then
    begin
     cisloDavky:= helUtils.getHeliosIntVal(Helios, 1, 'SELECT MAX(Cislo)+1 FROM ' + tblDavky + ' WHERE Rok=' + rokDavky.ToString + ' AND DruhDavky=' + druhDavky.ToString);
     lSQL:= 'INSERT ' + tblDavky + ' (DruhDavky, Rok, Cislo, Pozice) VALUES (' + druhDavky.ToString + ', ' + rokDavky.ToString + ', ' + cisloDavky.ToString + ', 1); SELECT SCOPE_IDENTITY()';
     with Helios.OpenSQL(lSQL) do
      idRodic:= StrToInt(VarToStr(FieldValues(0)));
    end;
   if (idRodic=0) and (id>0) then
    idRodic:= id;
   if (idRodic>0) then
    NactiDoklad;

  end;



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



 procedure TformDavkyObecne.NactiDoklad;
  var lSQL: string;
      cis: string;
      fldIDDavka, fldIDPrikaz, fldVyrPrikaz, fldRegCis, fldNazev, fldMnozstvi, fldMJ, fldTavba: TField;
  begin
   if (idRodic>0) then
    begin
     if not(tbl.Active) then
      tbl.Open;
     tbl.Edit;
     tbl.Clear; //Fields;

     fldIDDavka:= tbl.FieldByName('IDDavka');
     fldIDPrikaz:= tbl.FieldByName('IDPrikaz');
     fldVyrPrikaz:= tbl.FieldByName('VyrPrikaz');
     fldRegCis:= tbl.FieldByName('RegCis');
     fldNazev:= tbl.FieldByName('Nazev');
     fldMnozstvi:= tbl.FieldByName('Mnozstvi');
     fldMJ:= tbl.FieldByName('MJ');

     lblDavka.Caption:= '';
     if (helUtils.HeliosExistsTest(Helios, tblDavky, 'ID=' + id.ToString + ' AND DruhDavky=4')) then
      begin
       cis:= helUtils.getHeliosStrVal(Helios, '', 'SELECT Cislo FROM ' + tblDavky + ' WHERE ID=' + id.ToString);
       lblDavka.Caption:= 'Dávka KONEČNÁ : ' + cis;
      end;

     lSQL:= 'SELECT ID, IDPrikaz, Pozice, Mnozstvi, MJ, Tavba FROM ' + tblDavky + ' WHERE IDRodic=' + idRodic.ToString + ' ORDER BY Pozice';
     with Helios.OpenSQL(lSQL) do
      if (RecordCount>0) then
       begin
        First;
        while not(Eof) do
         begin
          tbl.Append;
           fldIDDavka.AsString:= VarToStr(FieldValues(0));
           fldIDPrikaz.AsString:= VarToStr(FieldValues(1));
           fldMnozstvi.AsString:= VarToStr(FieldValues(3));
           fldMJ.AsString:= VarToStr(FieldValues(4));
           fldTavba.AsString:= VarToStr(FieldValues(5));
          tbl.Post;
          Next;
         end;
        tbl.RecNo:= 1;
        grd.Repaint;
       end;
    end;
  end;

 procedure TformDavkyObecne.selPrikazClick(Sender: TObject);
  var lSQL, podm: string;
  begin
   podm:= 'TabPrikaz.StavPrikazu IN (30,40,50,60)';
   if (druhDavky=4) then
    podm:= podm + ' AND TabPrikaz.Rada=N''223-31''';
   if Helios.Prenos(bidVyrPrik, 'TabPrikaz.ID', oVar, podm, 'Vyberte příkaz do dávky', true) then
    begin
     idVPr:= VarToStr(oVar).ToInteger;
     NactiPrikaz;
     edtPrikaz.Text:= helUtils.getHeliosStrVal(Helios, '', 'SELECT RadaPrikaz FROM ' + tblPrikaz + ' WHERE ID=' + idVPr.ToString);
     edtTavba.SetFocus;
    end;

  end;

 procedure TformDavkyObecne.selTavbaClick(Sender: TObject);
  begin
//   oVar:= helUtils.getHeliosIntVal(Helios, 'SELECT ID FROM ' + tblTavby + ' WHERE CisloTavby=N' + QuotedStr(edtTavba.Text));
   oVar:= edtTavba.Text;
   if Helios.Prenos(bidTavby, 'hvw_TavbyPosledniData.CisloTavby', oVar, '', 'Vyberte tavbu', true) then
    edtTavba.Text:= VarToStr(oVar);
   edtMnoz.SetFocus;
  end;

end.