unit frmUnit12;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, ddPlugin_TLB, FireDAC.Comp.Client, FireDAC.Stan.Intf,
  FireDAC.Stan.Option, FireDAC.Stan.Param, FireDAC.Stan.Error, FireDAC.DatS,
  FireDAC.Phys.Intf, FireDAC.DApt.Intf, FireDAC.Comp.DataSet,
  JvExDBGrids, JvDBGrid, Vcl.Grids, Vcl.DBGrids, Data.DB, Vcl.StdCtrls;

const
   tblRozpPrijStav = '[dbo].[_TabRozpadPrijStav]';

type
  TfrmRozdelMat = class(TForm)
    btnQuit: TButton;
    dsRozpad: TDataSource;
    tabRozpad: TFDMemTable;
    btnPrevod: TButton;
    dsRozpad2: TDataSource;
    tabRozpad2: TFDMemTable;
    gRozpad2: TJvDBGrid;
    procedure FormShow (Sender: TObject);
    procedure btnQuitClick (Sender: TObject);
    procedure FormResize (Sender: TObject);
    procedure gRozpad2KeyUp (Sender: TObject; var Key: Word; Shift: TShiftState);
    procedure gRozpad2DrawColumnCell (Sender: TObject; const Rect: TRect; DataCol: Integer; Column: TColumn; State: TGridDrawState);
    procedure gRozpad2EditChange (Sender: TObject);
    procedure gRozpad2MouseMove (Sender: TObject; Shift: TShiftState; X, Y: Integer);
  private
   function GetColumnIndexByFieldName (const grid: TJvDBGrid; const fieldName: string): integer;
   function GetColumnByFieldName (const grid: TJvDBGrid; const fieldName: string): TColumn;
   function GetVisibleColumnByIndex (const grid: TJvDBGrid; const idx: integer): TColumn;
   procedure WMSyscommand (Var msg: TWmSysCommand); message WM_SYSCOMMAND;
   procedure NactiUdaje;
//   procedure DataDoTabulky;
   procedure DataDoTabulky2;
//   procedure OdectiDodani (celaTab: Boolean);
   procedure OdectiDodani2 (celaTab: Boolean);
//   procedure SirkySloupcu;
   procedure SirkySloupcu2;
   procedure VytvorRozpad2;
  public
   Helios: IHelios;
   idDZ: integer; // id prijemky
   idSS: Integer; // id stavu skladu
   aIdDZ: WideString; // id vice prijemek
   aIdSS: WideString; // id vice stavu skladu
  end;

var
  frmRozdelMat: TfrmRozdelMat;
  vId, dId, vCol, vRow, iHintRec: Integer;

implementation
uses System.StrUtils, helUtils, myUtils;

{$R *.dfm}

 procedure TfrmRozdelMat.WMSyscommand(var msg: TWmSysCommand);
  begin
   Case (msg.cmdtype and $FFF0) of
     SC_MINIMIZE:
      begin
       msg.result := 0;
      end;
     SC_MAXIMIZE:
        begin
         Self.WindowState := wsMaximized;
         msg.result := 0;
        end;
     SC_RESTORE:
      begin
       msg.result := 0;
      end;
    Else
     inherited;
   end;
  end;


{
 procedure TfrmRozdelMat.SirkySloupcu;
  var idx: integer;
      fName: string;
  begin
   idx:= 0;
   while (idx<=vRozpad.VisibleColumnCount-1) do
    begin
     fName:= LeftStr(StringReplace(vRozpad.VisibleColumns[idx].Name,'vRozpad','',[rfReplaceAll]),4);
     if (fName='Stav') or (fName='Preb') or (fName='Poza') or (fName='Doda') then
      vRozpad.VisibleColumns[idx].Width:= 62;
     Inc(idx);
    end;
   vRozpad.GetColumnByFieldName('CisloZbozi').Width:= 110;
   vRozpad.GetColumnByFieldName('Mnozstvi').Width:= 60;
  end;
}


 procedure TfrmRozdelMat.SirkySloupcu2;
  var idx: integer;
      fName: string;
  begin
   idx:= 0;
   while (idx<=gRozpad2.VisibleColCount-1) do
    begin
     fName:= LeftStr(StringReplace(GetVisibleColumnByIndex (gRozpad2, idx).FieldName,'vRozpad','',[rfReplaceAll]),4);
     if (fName='Stav') or (fName='Preb') or (fName='Poza') or (fName='Doda') then
      GetVisibleColumnByIndex (gRozpad2, idx).Width:= 62;
     Inc(idx);
    end;
   GetColumnByFieldName (gRozpad2, 'CisloZbozi').Width:= 110;
   GetColumnByFieldName (gRozpad2, 'Mnozstvi').Width:= 60;
  end;



{
 procedure TfrmRozdelMat.OdectiDodani (celaTab: Boolean);
  var fName: string;
      colNms: TArray<string>;
      idx1, idx2: integer;
      num: extended;
  begin
   SetLength(colNms,tabRozpad.FieldCount);
   idx1:= 0;
   idx2:= 0;
   while (idx1<=tabRozpad.FieldCount-1) do
    begin
     fName:= tabRozpad.FieldDefs.Items[idx1].Name;
     if Pos('Dodat',fName)>0 then
      begin
       colNms[idx2]:= fName;
       Inc(idx2);
      end;
     Inc(idx1);
    end;
   idx1:= Length(colNms)-1;
   while (idx1>=0) do
    begin
     if colNms[idx1]='' then
      SetLength(colNms,Length(colNms)-1);
     Dec(idx1);
    end;

// Pocitej nad celou tabulkou, jinak jen nad vRozpad.DataController.FocusedRecordIndex
   if (celaTab) then
     begin
      tabRozpad.First;
      while not(tabRozpad.Eof) do
       begin
        num:= 0;
        idx1:= 0;
        while (idx1<=Length(colNms)-1) do
         begin
          num:= num + tabRozpad.FieldByName(colNms[idx1]).AsFloat;
          Inc(idx1);
         end;
        tabRozpad.Edit;
         tabRozpad.FieldByName('Zbytek').AsExtended:= tabRozpad.FieldByName('Mnozstvi').AsExtended - num;
        tabRozpad.Post;
        tabRozpad.Next;
       end;
     end
    else
     begin
      num:= 0;
      idx1:= 0;
      while (idx1<=Length(colNms)-1) do
       begin
        num:= num + tabRozpad.FieldByName(colNms[idx1]).AsFloat;
        Inc(idx1);
       end;
      tabRozpad.Edit;
       tabRozpad.FieldByName('Zbytek').AsExtended:= tabRozpad.FieldByName('Mnozstvi').AsExtended - num;
      tabRozpad.Post;
     end;
   vRozpad.DataController.Refresh;
  end;
}

 procedure TfrmRozdelMat.OdectiDodani2 (celaTab: Boolean);
  var fName: string;
      colNms: TArray<string>;
      idx1, idx2: integer;
      num: extended;
  begin
   SetLength(colNms, tabRozpad2.FieldCount);
   idx1:= 0;
   idx2:= 0;
   while (idx1<=tabRozpad2.FieldCount-1) do
    begin
     fName:= tabRozpad.FieldDefs.Items[idx1].Name;
     if Pos('Dodat',fName)>0 then
      begin
       colNms[idx2]:= fName;
       Inc(idx2);
      end;
     Inc(idx1);
    end;
   idx1:= Length(colNms)-1;
   while (idx1>=0) do
    begin
     if colNms[idx1]='' then
      SetLength(colNms,Length(colNms)-1);
     Dec(idx1);
    end;

// Pocitej nad celou tabulkou, jinak jen nad vRozpad.DataController.FocusedRecordIndex
   if (celaTab) then
     begin
      tabRozpad2.First;
      while not(tabRozpad2.Eof) do
       begin
        num:= 0;
        idx1:= 0;
        while (idx1<=Length(colNms)-1) do
         begin
          num:= num + tabRozpad2.FieldByName(colNms[idx1]).AsFloat;
          Inc(idx1);
         end;
        tabRozpad2.Edit;
         tabRozpad2.FieldByName('Zbytek').AsExtended:= tabRozpad2.FieldByName('Mnozstvi').AsExtended - num;
        tabRozpad2.Post;
        tabRozpad2.Next;
       end;
     end
    else
     begin
      num:= 0;
      idx1:= 0;
      while (idx1<=Length(colNms)-1) do
       begin
        num:= num + tabRozpad2.FieldByName(colNms[idx1]).AsFloat;
        Inc(idx1);
       end;
      tabRozpad2.Edit;
       tabRozpad2.FieldByName('Zbytek').AsExtended:= tabRozpad2.FieldByName('Mnozstvi').AsExtended - num;
      tabRozpad2.Post;
     end;
   gRozpad2.DataSource.DataSet.Refresh;
  end;





 procedure TfrmRozdelMat.gRozpad2DrawColumnCell (Sender: TObject; const Rect: TRect; DataCol: Integer;  Column: TColumn; State: TGridDrawState);
  var i: integer;
      v: Extended;
      fName: string;
  begin
   gRozpad2.Canvas.Font.Color:= clBlack;
   gRozpad2.Canvas.Font.Style:= [];

   if Odd(gRozpad2.DataSource.DataSet.RecNo) then
     gRozpad2.Canvas.Brush.Color:= $00FFD2D2
    else
     gRozpad2.Brush.Color:= clWhite;

   if (gdSelected in State) then
     begin
//      gRozpad2.Canvas.Brush.Color:= clHighlight;
//      gRozpad2.Canvas.Font.Color:= clHighlightText;
      gRozpad2.Canvas.Brush.Color:= $00FF9933;
      gRozpad2.Canvas.Font.Color:= clWhite;
     end;
{
    else
     begin
      ACanvas
     end;
}
   fName:= Column.FieldName;
   fName:= StringReplace(fName,'vRozpad','',[rfReplaceAll]);
   i:= DataCol; // TcxGridDBTableView(Sender).GetColumnByFieldName(fName).Index;
   SetLength(fName,Length(fName)-3);
   if (fName='Dodat') then
    begin
     v:= Column.Field.AsExtended; // StrToFloat(VarToStr(AViewInfo.GridRecord.Values[i]));
     if (v>0) then
      begin
       gRozpad2.Canvas.Font.Color:= clRed;
       gRozpad2.Canvas.Font.Style:= [fsBold];
      end;
    end;

   gRozpad2.DefaultDrawColumnCell (Rect, DataCol, Column, State);
  end;




 procedure TfrmRozdelMat.gRozpad2EditChange (Sender: TObject);
  var i: Integer;
  begin
   i:= 0;
  end;


{
 procedure TfrmRozdelMat.vRozpadEditing (Sender: TcxCustomGridTableView; AItem: TcxCustomGridTableItem; var AAllow: Boolean);
  var fName: string;
  begin
   fName:= TcxGridDBTableView(Sender).Columns[AItem.Index].Name;
   fName:= StringReplace(fName,'vRozpad','',[rfReplaceAll]);
   SetLength(fName,Length(fName)-3);
   if (fName='Dodat') then
     AAllow:= true
    else
     AAllow:= false;
  end;
}




 procedure TfrmRozdelMat.gRozpad2KeyUp (Sender: TObject; var Key: Word; Shift: TShiftState);
  var i: integer;
      x, cName: string;
  begin
   if Key=VK_RETURN then
    begin
     vCol:= gRozpad2.Col;
     i:= gRozpad2.Row;
     cName:= StringReplace(gRozpad2.Columns.Items[vCol].FieldName ,'vRozpad','', [rfReplaceAll]);
     tabRozpad2.RecNo:= gRozpad2.DataSource.DataSet.RecNo+1;
     x:= tabRozpad2.FieldByName(cName).AsString;
     tabRozpad2.Edit;
      tabRozpad2.FieldByName(cName).AsFloat:= StrToFloat(x);
     tabRozpad2.Post;
     OdectiDodani2 (false);
     gRozpad2.Row:= i;
     gRozpad2.Col:= vCol;
    end;
  end;




 procedure TfrmRozdelMat.gRozpad2MouseMove (Sender: TObject; Shift: TShiftState; X, Y: Integer);
  var i: integer;
{
      Site: TcxGridSite;
      HitTest: TcxCustomGridHitTest;
      Item: TcxCustomGridTableItem;
      Rec: TcxCustomGridRecord;
}
      sNewHint: string;
  begin
{
   i:= vRozpad.DataController.FocusedRecordIndex;
   Site:= Sender as TcxGridSite;
   HitTest:= Site.GridView.ViewInfo.GetHitTest(X, Y);
   if HitTest is TcxGridRecordCellHitTest then
    begin
     Item:= TcxGridRecordCellHitTest(HitTest).Item;
     Rec:= TcxGridRecordCellHitTest(HitTest).GridRecord;

     if Item.Index= vRozpad.GetColumnByFieldName('Mnozstvi').Index then begin
       sNewHint := 'New hint goes here';
       if (gRozpad.Hint<>sNewHint) or (Rec.RecordIndex<>iHintRec) then
         begin
          iHintRec:= Rec.RecordIndex;
          gRozpad.ShowHint:= false;
          gRozpad.Hint:= sNewHint;
         end
        else
         gRozpad.ShowHint:= true;
     end
     else begin
       gRozpad.ShowHint:= false;
     end;
    end;
   vRozpad.DataController.FocusedRecordIndex:= i;
}
  end;



 procedure TfrmRozdelMat.DataDoTabulky2;
  var sql, fName: string;
      flds: TArray<string>;
      idx: integer;
  begin
   sql:= '';
   idx:= 0;
   SetLength(flds, gRozpad2.VisibleColCount);
   while (idx<=gRozpad2.VisibleColCount-1) do
    begin
     fName:= StringReplace(GetVisibleColumnByIndex(gRozpad2, idx).FieldName, 'vRozpad','',[rfReplaceAll]);
     flds[idx]:= StringReplace(GetVisibleColumnByIndex(gRozpad2, idx).FieldName, 'vRozpad','',[rfReplaceAll]);
     sql:= sql + flds[idx] + ',';
     Inc(idx);
    end;

   if (sql<>'') then
    SetLength(sql,Length(sql)-1);

   sql:= 'SELECT ' + sql + ' FROM ' + tblRozpPrijStav + ' WHERE Autor=SUSER_SNAME() ORDER BY CisloZbozi';
   with Helios.OpenSQL(sql) do
    if RecordCount>0 then
     begin
      First;
      sql:= '';
      while not(EOF) do
       begin
        idx:= 0;
        tabRozpad2.Append;
         while (idx<Length(flds)) do
          begin
           fName:= flds[idx];
           tabRozpad2.FieldByName(fName).AsString:= VarToStr(FieldValues(idx));
//           sql:= fName + ' = ' + VarToStr(FieldValues(idx)) + CRLF;
           Inc(idx);
          end;
         tabRozpad2.FieldByName('Zbytek').AsExtended:= tabRozpad2.FieldByName('Mnozstvi').AsExtended;
        tabRozpad2.Post;
        Next;
       end;
     end;
   tabRozpad2.First;

   GetColumnByFieldName (gRozpad2, 'IdKmenZbozi').Visible:= false;
   GetColumnByFieldName (gRozpad2, 'Mnozstvi').Visible:= false;

   GetColumnByFieldName (gRozpad2, 'CisloZbozi').Title.Caption:= 'Č.zboží';
   GetColumnByFieldName (gRozpad2, 'Zbytek').Title.Caption:= 'Zbývá';
  end;



{
 procedure TfrmRozdelMat.DataDoTabulky;
  var sql, fName: string;
      flds: TArray<string>;
      idx: integer;
  begin
   sql:= '';
   idx:= 0;
   SetLength(flds,vRozpad.VisibleItemCount);
   while (idx<=vRozpad.VisibleItemCount-1) do
    begin
     fName:= StringReplace(vRozpad.VisibleColumns[idx].Name,'vRozpad','',[rfReplaceAll]);
     flds[idx]:= StringReplace(vRozpad.VisibleColumns[idx].Name,'vRozpad','',[rfReplaceAll]);
     sql:= sql + flds[idx] + ',';
     Inc(idx);
    end;
   if (sql<>'') then
    SetLength(sql,Length(sql)-1);
   sql:= 'SELECT ' + sql + ' FROM ' + tblRozpPrijStav + ' WHERE Autor=SUSER_SNAME() ORDER BY CisloZbozi';
   with Helios.OpenSQL(sql) do
    if RecordCount>0 then
     begin
      First;
      sql:= '';
      while not(EOF) do
       begin
        idx:= 0;
        tabRozpad.Append;
         while (idx<Length(flds)) do
          begin
           fName:= flds[idx];
           tabRozpad.FieldByName(fName).AsString:= VarToStr(FieldValues(idx));
//           sql:= fName + ' = ' + VarToStr(FieldValues(idx)) + CRLF;
           Inc(idx);
          end;
         tabRozpad.FieldByName('Zbytek').AsExtended:= tabRozpad.FieldByName('Mnozstvi').AsExtended;
        tabRozpad.Post;
        Next;
       end;
     end;
   tabRozpad.First;
   vRozpad.GetColumnByFieldName('IdKmenZbozi').Visible:= false;
   vRozpad.GetColumnByFieldName('Mnozstvi').Visible:= false;

   vRozpad.GetColumnByFieldName('CisloZbozi').Caption:= 'Č.zboží';
   vRozpad.GetColumnByFieldName('Zbytek').Caption:= 'Zbývá';
  end;
}


 procedure TfrmRozdelMat.NactiUdaje;
  var sql, celkP, celkD, dodat, stred: string;
      l_loop: integer;
      chyba: boolean;
      mn: Extended;
  begin
   chyba:= false;
   sql:= '';
   if (idDZ>0) then
    aIdDZ:= IntToStr(idDZ);
   if (idSS>0) then
    aIdSS:= IntToStr(idSS);

   sql:= 'IF OBJECT_ID(N' + QuotedStr(tblRozpPrijStav) + ') IS NOT NULL DROP TABLE ' + tblRozpPrijStav;
   sql:= sql + CRLF + 'CREATE TABLE ' + tblRozpPrijStav + '(IdKmenZbozi INT NOT NULL, Mnozstvi NUMERIC(19,6)';
   sql:= sql + ' NOT NULL DEFAULT 0.0, CisloZbozi NVARCHAR(33), Autor NVARCHAR(80) NOT NULL DEFAULT SUSER_SNAME(),';
   sql:= sql + 'DatPorizeni DATETIME DEFAULT GETDATE(), Zbytek NUMERIC(19,6) DEFAULT 0.0 )';
   Helios.ExecSQL(sql);

   if (aIdDZ<>'') then
    begin
     sql:= 'INSERT ' + tblRozpPrijStav + ' (IdKmenZbozi,Mnozstvi,CisloZbozi)';
     sql:= sql + ' SELECT DISTINCT(kz.id),SUM(pz.Mnozstvi),kz.CisloZbozi FROM ' + tblPZ + ' pz INNER JOIN ' + tblSS;
     sql:= sql + ' ss ON (pz.IdZboSklad=ss.id) INNER JOIN ' + tblKZ + ' kz ON (ss.IdKmenZbozi=kz.id) WHERE';
     sql:= sql + ' pz.IDDoklad IN (' + aIdDZ + ') GROUP BY kz.id,kz.CisloZbozi ORDER BY kz.id';
     try
       Helios.ExecSQL(sql);
      except
       chyba:= true;
     end;
    end;
   if (aIdSS<>'') then
    begin
     sql:= 'INSERT ' + tblRozpPrijStav + ' (IdKmenZbozi,Mnozstvi,CisloZbozi)';
     sql:= sql + ' SELECT DISTINCT(kz.id),SUM(ss.Mnozstvi),kz.CisloZbozi FROM ' + tblSS + ' ss INNER JOIN ' + tblKZ;
     sql:= sql + ' kz ON (ss.idKmenZbozi=kz.id) WHERE ss.id IN (' + aIdSS + ')';
     sql:= sql + ' GROUP BY kz.id,kz.CisloZbozi ORDER BY kz.id';
     try
       Helios.ExecSQL(sql);
      except
       chyba:= true;
     end;
    end;

   if not(chyba) then
    begin
     sql:= 'SELECT DISTINCT(RIGHT(vp.KmenoveStredisko,3)) FROM TabPrKVazby kv INNER JOIN TabPrikaz vp';
     sql:= sql + ' ON (kv.IdPrikaz=vp.Id AND vp.StavPrikazu<=40) WHERE kv.mnoz_Nevydane>0 AND kv.nizsi IN';
     sql:= sql + ' (SELECT IdKmenZbozi FROM ' + tblRozpPrijStav + ') ORDER BY RIGHT(vp.KmenoveStredisko,3)';
     with Helios.OpenSQL(sql) do
      if RecordCount>0 then
       begin
        First;
        while (not(EOF) and not(chyba)) do
         begin
          stred:= VarToStr(FieldValues(0));
          sql:= 'IF (COL_LENGTH(N' + QuotedStr(tblRozpPrijStav) + ',N' + QuotedStr('Stav' + stred);
          sql:= sql + ') IS NULL) ALTER TABLE ' + tblRozpPrijStav + ' ADD Stav' + stred;
          sql:= sql + ' NUMERIC(19,6) NOT NULL DEFAULT 0.0' + CRLF;
          sql:= sql + 'IF (COL_LENGTH(N' + QuotedStr(tblRozpPrijStav) + ',N' + QuotedStr('Pozad' + stred);
          sql:= sql + ') IS NULL) ALTER TABLE ' + tblRozpPrijStav + ' ADD Pozad' + stred;
          sql:= sql + ' NUMERIC(19,6) NOT NULL DEFAULT 0.0' + CRLF;
          try
            Helios.ExecSQL(sql);
           except
            chyba:= true;
          end;
          if not(chyba) then
           begin
            sql:= sql + 'IF (COL_LENGTH(N' + QuotedStr(tblRozpPrijStav) + ',N' + QuotedStr('Prebyt' + stred);
            sql:= sql + ') IS NULL) ALTER TABLE ' + tblRozpPrijStav + ' ADD Prebyt' + stred;
            sql:= sql + ' AS ([Stav' + stred + ']-[Pozad' + stred + '])' + CRLF;
            Helios.ExecSQL(sql);

            sql:= sql + 'IF (COL_LENGTH(N' + QuotedStr(tblRozpPrijStav) + ',N' + QuotedStr('Dodat' + stred);
            sql:= sql + ') IS NULL) ALTER TABLE ' + tblRozpPrijStav + ' ADD Dodat' + stred;
            sql:= sql + ' AS (CASE WHEN [Pozad' + stred + ']-[Stav' + stred + ']<0 THEN 0 ELSE [Pozad' + stred;
            sql:= sql + ']-[Stav' + stred + '] END)' + CRLF;
            Helios.ExecSQL(sql);

            sql:= 'SET NOCOUNT ON' + CRLF;
            sql:= sql + 'DECLARE @idKZ INT,@MnS NUMERIC(19,6),@MnP NUMERIC(19,6),@Skl NVARCHAR(30)' + CRLF;
            sql:= sql + 'DECLARE c CURSOR LOCAL FAST_FORWARD FOR SELECT IdKmenZbozi FROM ';
            sql:= sql + tblRozpPrijStav + CRLF + 'OPEN c' + CRLF + 'WHILE (1=1) BEGIN FETCH NEXT FROM c INTO @idKZ' + CRLF;
            sql:= sql + ' IF (@@FETCH_STATUS<>0) BREAK' + CRLF + 'SET @MnS=ISNULL((SELECT ISNULL(Mnozstvi,0) FROM ' + tblSS;
            sql:= sql + ' WHERE IdKmenZbozi=@idKZ AND IDSklad';
            if stred='200' then
              sql:= sql + '=N' + QuotedStr('200')
             else
              sql:= sql + ' LIKE N' + QuotedStr('200%' + stred);
            sql:= sql + '),0)' + CRLF;
            sql:= sql + 'SET @MnP=ISNULL((SELECT ISNULL(SUM(kv.Mnoz_Nevydane),0) FROM ' + tblPrVaz + ' kv INNER JOIN' + tblPrikaz;
            sql:= sql + ' vp ON (kv.IdPrikaz=vp.Id AND vp.StavPrikazu<=40) WHERE kv.Nizsi=@idKZ AND vp.KmenoveStredisko';
            if stred='200' then
              sql:= sql + '=N' + QuotedStr('200')
             else
              sql:= sql + ' LIKE N' + QuotedStr('200%' + stred);
            sql:= sql + '),0)' + CRLF;
            sql:= sql + 'UPDATE ' + tblRozpPrijStav + ' SET Stav' + stred + '=@MnS,Pozad' + stred;
            sql:= sql + '=@MnP WHERE idKmenZbozi=@idKZ' + CRLF + 'END' + CRLF + 'CLOSE c' + CRLF + 'DEALLOCATE c';
            try
              Helios.ExecSQL(sql);
             except
              chyba:= true;
            end;
           end;
          Next;
         end;

//        celkD:= 'IF (COL_LENGTH(N' + QuotedStr(tblRozpPrijStav) + ',N' + QuotedStr('CelkemDodat');
//        celkD:= celkD + ') IS NULL) ALTER TABLE ' + tblRozpPrijStav + ' ADD CelkemDodat AS (';
//        dodat:= 'IF (COL_LENGTH(N' + QuotedStr(tblRozpPrijStav) + ',N' + QuotedStr('Dodat' + stred);
//        dodat:= dodat + ') IS NULL) ALTER TABLE ' + tblRozpPrijStav + ' ADD Dodat' + stred + ' AS (';

// celkem prebytek
//        celkP:= 'IF (COL_LENGTH(N' + QuotedStr(tblRozpPrijStav) + ',N' + QuotedStr('CelkemPrebyt');
//        celkP:= celkP + ') IS NULL) ALTER TABLE ' + tblRozpPrijStav + ' ADD CelkemPrebyt AS (';
//        celkP:= celkP + 'CASE WHEN [Stav' + stred + ']-[Pozad' + stred + ']>0 THEN ([Stav' + stred + ']-';
//        celkP:= celkP + '[Pozad' + stred + ']) ELSE 0 END + ';
// celkem dodat
//        celkD:= celkD + 'CASE WHEN [Stav' + stred + ']-[Pozad' + stred + ']<0 THEN ABS([Stav' + stred + ']-';
//        celkD:= celkD + '[Pozad' + stred + ']) ELSE 0 END + ';

// celkem Prebytek
        mn:= 0;
        sql:= 'SELECT [name] FROM sys.[columns] WHERE OBJECT_ID=OBJECT_ID(N' + QuotedStr(tblRozpPrijStav) + ')';
        sql:= sql + ' AND [name] LIKE N' + QuotedStr('Prebyt%');
        with Helios.OpenSQL(sql) do
         if RecordCount>0 then
          begin
           First;
           sql:= '[';
           while not(EOF) do
            begin
             sql:= sql + VarToStr(FieldValues(0)) + ']+[';
             Next;
            end;
           SetLength(sql,Length(sql)-2);
          end;
        celkP:= 'IF (COL_LENGTH(N' + QuotedStr(tblRozpPrijStav) + ',N' + QuotedStr('CelkemPrebyt');
        celkP:= celkP + ') IS NULL) ALTER TABLE ' + tblRozpPrijStav + ' ADD CelkemPrebyt NUMERIC(19,6) DEFAULT 0.0';
        Helios.ExecSQL(celkP);
        sql:= 'UPDATE ' + tblRozpPrijStav + ' SET CelkemPrebyt=' + sql;
        Helios.ExecSQL(sql);

// celkem Dodat
        sql:= 'SELECT [name] FROM sys.[columns] WHERE OBJECT_ID=OBJECT_ID(N' + QuotedStr(tblRozpPrijStav) + ')';
        sql:= sql + ' AND [name] LIKE N' + QuotedStr('Dodat%');
        with Helios.OpenSQL(sql) do
         if RecordCount>0 then
          begin
           First;
           sql:= '[';
           while not(EOF) do
            begin
             sql:= sql + VarToStr(FieldValues(0)) + ']+[';
             Next;
            end;
           SetLength(sql,Length(sql)-2);
          end;
        celkD:= 'IF (COL_LENGTH(N' + QuotedStr(tblRozpPrijStav) + ',N' + QuotedStr('CelkemDodat');
        celkD:= celkD + ') IS NULL) ALTER TABLE ' + tblRozpPrijStav + ' ADD CelkemDodat NUMERIC(19,6) DEFAULT 0.0';
        Helios.ExecSQL(celkD);
        sql:= 'UPDATE ' + tblRozpPrijStav + ' SET CelkemDodat=' + sql;
        Helios.ExecSQL(sql);
       end;
    end;
  end;




 procedure TfrmRozdelMat.btnQuitClick (Sender: TObject);
  begin
   Close;
  end;




 procedure TfrmRozdelMat.FormResize (Sender: TObject);
  var idx: integer;
      w: integer;
  begin
   gRozpad2.BeginUpdate;
   gRozpad2.Width:= Self.Width - 14;
   gRozpad2.Height:= Self.Height - 90;
   w:= gRozpad2.Width div gRozpad2.VisibleColCount;
   idx:= 0;
   while (idx<=gRozpad2.VisibleColCount-1) do
    begin
     GetVisibleColumnByIndex (gRozpad2, idx).Width:= w-1;
     Inc (idx);
    end;
   btnQuit.Left:= Self.Width - btnQuit.Width - 40;
   btnQuit.Top:= Self.Height - btnQuit.Height - 50;
   btnPrevod.Left:= btnQuit.Left - 160;
   btnPrevod.Top:= btnQuit.Top;
   SirkySloupcu2;
   gRozpad2.EndUpdate;
  end;




 function TfrmRozdelMat.GetVisibleColumnByIndex (const grid: TJvDBGrid; const idx: integer): TColumn;
  var i, visIdx: integer;
  begin
   visIdx:= -1;
   result:= nil;
   for i:=0 to grid.Columns.Count-1 do
    if (grid.Columns.Items[i].Visible) then
     begin
      Inc (visIdx);
      if (visIdx=idx) then
       begin
        result:= grid.Columns.Items[i];
        Break;
       end;
     end;
  end;




 function TfrmRozdelMat.GetColumnByFieldName (const grid: TJvDBGrid; const fieldName: string): TColumn;
  var i: integer;
  begin
   result:= nil;
   for i:=0 to grid.Columns.Count-1 do
    begin
     if (System.SysUtils.SameText(grid.Columns.Items[i].FieldName, fieldName)) then
      begin
       result:= grid.Columns.Items[i];
       Break;
      end;
    end;
  end;




 function TfrmRozdelMat.GetColumnIndexByFieldName (const grid: TJvDBGrid; const fieldName: string): Integer;
  var i: integer;
  begin
   result:= -1;
   for i:=0 to grid.Columns.Count-1 do
    if (System.SysUtils.SameText(grid.Columns.Items[i].FieldName, fieldName)) then
     begin
      result:= i;
      Break;
     end;
  end;




 procedure TfrmRozdelMat.VytvorRozpad2;
  var sql, fName: string;
      idx, cStr: integer;
  begin
   if (tabRozpad2.Active) then
    if (tabRozpad2.RecordCount>0) then
     tabRozpad2.EmptyDataSet;

   if (tabRozpad2.Active) then
    tabRozpad2.Close;
   sql:= 'SELECT [name] FROM sys.[columns] WHERE OBJECT_ID=OBJECT_ID(N' + QuotedStr(tblRozpPrijStav) + ')';
   sql:= sql + ' AND [name] LIKE N' + QuotedStr('Stav%');
   with Helios.OpenSQL(sql) do
    if RecordCount>0 then
     begin
      tabRozpad2.FieldDefs.Clear;
      First;
      with tabRozpad.FieldDefs do
       while not(EOF) do
        begin
         Add(VarToStr(FieldValues(0)),ftFloat,0,true);
         Next;
        end;
     end;
   sql:= 'SELECT [name] FROM sys.[columns] WHERE OBJECT_ID=OBJECT_ID(N' + QuotedStr(tblRozpPrijStav) + ')';
   sql:= sql + ' AND [name] LIKE N' + QuotedStr('Pozad%');
   with Helios.OpenSQL(sql) do
    if RecordCount>0 then
     begin
      First;
      with tabRozpad2.FieldDefs do
       while not(EOF) do
        begin
         Add(VarToStr(FieldValues(0)),ftFloat,0,true);
         Next;
        end;
     end;
   sql:= 'SELECT [name] FROM sys.[columns] WHERE OBJECT_ID=OBJECT_ID(N' + QuotedStr(tblRozpPrijStav) + ')';
   sql:= sql + ' AND [name] LIKE N' + QuotedStr('Prebyt%');
   with Helios.OpenSQL(sql) do
    if RecordCount>0 then
     begin
      First;
      with tabRozpad2.FieldDefs do
       while not(EOF) do
        begin
         Add(VarToStr(FieldValues(0)),ftFloat,0,true);
         Next;
        end;
     end;
   sql:= 'SELECT [name] FROM sys.[columns] WHERE OBJECT_ID=OBJECT_ID(N' + QuotedStr(tblRozpPrijStav) + ')';
   sql:= sql + ' AND [name] LIKE N' + QuotedStr('Dodat%');
   with Helios.OpenSQL(sql) do
    if RecordCount>0 then
     begin
      First;
      with tabRozpad2.FieldDefs do
       while not(EOF) do
        begin
         Add(VarToStr(FieldValues(0)),ftFloat,0,true);
         Next;
        end;
     end;

   with tabRozpad2.FieldDefs do
    begin
     Add('IdKmenZbozi',ftInteger,0,true);
     Add('CisloZbozi',ftString,33,true);
     Add('Mnozstvi',ftFloat,0,true); // puvodni mnozstvi na vydejce/stavu skladu
     Add('Zbytek',ftFloat,0,true); // zbytek po odecteni mnozstvi k dodani
     Add('CelkemPrebyt',ftFloat,0,false);
    end;
   tabRozpad2.Open;

   idx:= 0;
   while (idx<=gRozpad2.Columns.Count-1) do
    begin
     gRozpad2.Columns.Items[idx].Width:= 60;
     Inc(idx);
    end;

   idx:= 0;
   GetColumnByFieldName(gRozpad2, 'CisloZbozi').Index:= idx;
   Inc (idx);
   GetColumnByFieldName(gRozpad2, 'Zbytek').Index:= idx;
   Inc (idx);

   sql:= 'SELECT [name] FROM sys.columns WHERE OBJECT_ID=OBJECT_ID(N' + QuotedStr('_TabRozpadPrijStav') + ') AND';
   sql:= sql + ' [name] LIKE N' + QuotedStr('%200') + ' ORDER BY CASE LEFT([name],4) WHEN N''Stav'' THEN 1';
   sql:= sql + ' WHEN N''Poza'' THEN 2 WHEN N''Preb'' THEN 3 WHEN N''Doda'' THEN 4 END';
   with Helios.OpenSQL(sql) do
    if RecordCount>0 then
     begin
      First;
      while not(EOF) do
       begin
        GetColumnByFieldName(gRozpad2, VarToStr(FieldValues(0))).Index:= idx;
        Inc (idx);
        Next;
       end;
     end;

   for cStr:=101 to 115 do
    begin
     sql:= 'SELECT [name] FROM sys.columns WHERE OBJECT_ID=OBJECT_ID(N' + QuotedStr('_TabRozpadPrijStav') + ') AND';
     sql:= sql + ' [name] LIKE N' + QuotedStr('%' + IntToStr(cStr)) + ' ORDER BY CASE LEFT([name],4) WHEN N''Stav'' THEN 1';
     sql:= sql + ' WHEN N''Poza'' THEN 2 WHEN N''Preb'' THEN 3 WHEN N''Doda'' THEN 4 END';
     with Helios.OpenSQL(sql) do
      if RecordCount>0 then
       begin
        First;
        while not(EOF) do
         begin
          fName:= LeftStr(StringReplace(GetVisibleColumnByIndex (gRozpad2,idx).FieldName, 'vRozpad', '', [rfReplaceAll]), 5);
//          fName:= LeftStr(StringReplace(vRozpad.VisibleColumns[idx].Name,'vRozpad','',[rfReplaceAll]),5);
          GetVisibleColumnByIndex (gRozpad2, idx).ReadOnly:= (fName<>'Dodat');
          GetColumnByFieldName(gRozpad2, VarToStr(FieldValues(0))).Index:= idx;
          Inc (idx);
          Next;
         end;
       end;
    end;

  end;




 procedure TfrmRozdelMat.FormShow (Sender: TObject);
  var sql, fName: string;
      idx, cStr: integer;
  begin
//   cxSetResourceString(@scxGridGroupByBoxCaption,'Sem přetáhněte záhlaví sloupců, podle kterých chcete přehled seskupit.');
//   cxSetResourceString(@scxGridGroupByBoxCaption,'');

   if (idDZ>0) then
    aIdDZ:= IntToStr (idDZ);
   if (idSS>0) then
    aIdSS:= IntToStr (idSS);

   NactiUdaje;


 {
   if (tabRozpad.Active) then
    if (tabRozpad.RecordCount>0) then
     tabRozpad.EmptyDataSet;

   if (tabRozpad.Active) then
    tabRozpad.Close;
   sql:= 'SELECT [name] FROM sys.[columns] WHERE OBJECT_ID=OBJECT_ID(N' + QuotedStr(tblRozpPrijStav) + ')';
   sql:= sql + ' AND [name] LIKE N' + QuotedStr('Stav%');
   with Helios.OpenSQL(sql) do
    if RecordCount>0 then
     begin
      tabRozpad.FieldDefs.Clear;
      First;
      with tabRozpad.FieldDefs do
       while not(EOF) do
        begin
         Add(VarToStr(FieldValues(0)),ftFloat,0,true);
         Next;
        end;
     end;
   sql:= 'SELECT [name] FROM sys.[columns] WHERE OBJECT_ID=OBJECT_ID(N' + QuotedStr(tblRozpPrijStav) + ')';
   sql:= sql + ' AND [name] LIKE N' + QuotedStr('Pozad%');
   with Helios.OpenSQL(sql) do
    if RecordCount>0 then
     begin
      First;
      with tabRozpad.FieldDefs do
       while not(EOF) do
        begin
         Add(VarToStr(FieldValues(0)),ftFloat,0,true);
         Next;
        end;
     end;
   sql:= 'SELECT [name] FROM sys.[columns] WHERE OBJECT_ID=OBJECT_ID(N' + QuotedStr(tblRozpPrijStav) + ')';
   sql:= sql + ' AND [name] LIKE N' + QuotedStr('Prebyt%');
   with Helios.OpenSQL(sql) do
    if RecordCount>0 then
     begin
      First;
      with tabRozpad.FieldDefs do
       while not(EOF) do
        begin
         Add(VarToStr(FieldValues(0)),ftFloat,0,true);
         Next;
        end;
     end;
   sql:= 'SELECT [name] FROM sys.[columns] WHERE OBJECT_ID=OBJECT_ID(N' + QuotedStr(tblRozpPrijStav) + ')';
   sql:= sql + ' AND [name] LIKE N' + QuotedStr('Dodat%');
   with Helios.OpenSQL(sql) do
    if RecordCount>0 then
     begin
      First;
      with tabRozpad.FieldDefs do
       while not(EOF) do
        begin
         Add(VarToStr(FieldValues(0)),ftFloat,0,true);
         Next;
        end;
     end;

   with tabRozpad.FieldDefs do
    begin
     Add('IdKmenZbozi',ftInteger,0,true);
     Add('CisloZbozi',ftString,33,true);
     Add('Mnozstvi',ftFloat,0,true); // puvodni mnozstvi na vydejce/stavu skladu
     Add('Zbytek',ftFloat,0,true); // zbytek po odecteni mnozstvi k dodani
     Add('CelkemPrebyt',ftFloat,0,false);
    end;
   tabRozpad.Open;
   vRozpad.ClearItems;
   vRozpad.DataController.CreateAllItems;
   vRozpad.OptionsCustomize.ColumnFiltering:= false;
   idx:= 0;
   while (idx<=vRozpad.ColumnCount-1) do
    begin
     vRozpad.Columns[idx].Width:= 60;
     Inc(idx);
    end;

   idx:= 0;
   vRozpad.GetColumnByFieldName('CisloZbozi').Index:= idx;
   Inc(idx);
   vRozpad.GetColumnByFieldName('Zbytek').Index:= idx;
   Inc(idx);

   sql:= 'SELECT [name] FROM sys.columns WHERE OBJECT_ID=OBJECT_ID(N' + QuotedStr('_TabRozpadPrijStav') + ') AND';
   sql:= sql + ' [name] LIKE N' + QuotedStr('%200') + ' ORDER BY CASE LEFT([name],4) WHEN N''Stav'' THEN 1';
   sql:= sql + ' WHEN N''Poza'' THEN 2 WHEN N''Preb'' THEN 3 WHEN N''Doda'' THEN 4 END';
   with Helios.OpenSQL(sql) do
    if RecordCount>0 then
     begin
      First;
      while not(EOF) do
       begin
        vRozpad.GetColumnByFieldName(VarToStr(FieldValues(0))).Index:= idx;
        Inc(idx);
        Next;
       end;
     end;

   for cStr:=101 to 115 do
    begin
     sql:= 'SELECT [name] FROM sys.columns WHERE OBJECT_ID=OBJECT_ID(N' + QuotedStr('_TabRozpadPrijStav') + ') AND';
     sql:= sql + ' [name] LIKE N' + QuotedStr('%' + IntToStr(cStr)) + ' ORDER BY CASE LEFT([name],4) WHEN N''Stav'' THEN 1';
     sql:= sql + ' WHEN N''Poza'' THEN 2 WHEN N''Preb'' THEN 3 WHEN N''Doda'' THEN 4 END';
     with Helios.OpenSQL(sql) do
      if RecordCount>0 then
       begin
        First;
        while not(EOF) do
         begin
          fName:= LeftStr(StringReplace(vRozpad.VisibleColumns[idx].Name,'vRozpad','',[rfReplaceAll]),5);
          if (fName='Dodat') then
           vRozpad.VisibleColumns[idx].Editing:= true;
          vRozpad.GetColumnByFieldName(VarToStr(FieldValues(0))).Index:= idx;
          Inc(idx);
          Next;
         end;
       end;
    end;
}

   VytvorRozpad2;


   DataDoTabulky2;
   SirkySloupcu2;
   Self.WindowState:= wsMaximized;
   Resize;
   OdectiDodani2 (True);

  end;

end.