unit Unit2;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Grids, DBGrids, DB, ADODB, ExtCtrls, DBCtrls, IniFiles,
  CheckLst, ToolWin, ComCtrls, ImgList, Buttons, ComObj;

type
  TForm2 = class(TForm)

    ADOConnection1: TADOConnection;
    ADOQuery1: TADOQuery;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    CheckBox1: TCheckBox;
    DBNavigator1: TDBNavigator;
    Button1: TButton;
    ImageList1: TImageList;
    SpeedButton1: TSpeedButton;
    ADOTable1: TADOTable;
    ADOTable2: TADOTable;
    procedure FormCreate(Sender: TObject);
    procedure CheckBox1Click(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormShow(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure SpeedButton1Click(Sender: TObject);
    procedure DBGrid1ColEnter(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    procedure AppMessage(var Msg: TMsg; var Handled: Boolean);
  end;

var
  Form2: TForm2;
  Ini : Tinifile;
  main, note : String;

implementation

uses Unit1, Unit3;

{$R *.dfm}

//------------------------------------------------------------------------------

procedure ColumnReadOnlyFalse;
begin
  Form2.DBGrid1.DataSource.DataSet.Fields[1].ReadOnly := false;
end;

//------------------------------------------------------------------------------

procedure AllUsers;
begin
  Form2.ADOQuery1.Active := false;
  Form2.ADOQuery1.SQL.Clear;
  Form2.ADOQuery1.SQL.Add('select');
  Form2.ADOQuery1.SQL.Add('m.*,');
  Form2.ADOQuery1.SQL.Add('n.a_date_pmd_ruch,');
  Form2.ADOQuery1.SQL.Add('n.DecryptMailMO_InNumb,');
  Form2.ADOQuery1.SQL.Add('n.DecryptMailMO_Date,');
  Form2.ADOQuery1.SQL.Add('n.DecryptMailMO_Reason,');
  Form2.ADOQuery1.SQL.Add('n.date_expert,');
  Form2.ADOQuery1.SQL.Add('n.fio_exp_kmp,');
  Form2.ADOQuery1.SQL.Add('n.date_expert_vozv,');
  Form2.ADOQuery1.SQL.Add('n.PRIM_GOR,');
  Form2.ADOQuery1.SQL.Add('n.PRIM,');
  Form2.ADOQuery1.SQL.Add('n.PRIM_OPERATOR,');
  Form2.ADOQuery1.SQL.Add('n.PRIM_TIMESTAMP');
  Form2.ADOQuery1.SQL.Add('from base_x.dbo.' + main + ' m');
  Form2.ADOQuery1.SQL.Add('left join base_x.dbo.' + note + ' n on n.id_schet_sluch = m.id_schet_sluch');
  Form2.ADOQuery1.Active := true;
  Form2.DBNavigator1.Visible := false;
end;

//------------------------------------------------------------------------------

procedure CurrentUser;
begin
  Form2.ADOQuery1.Active := false;
  Form2.ADOQuery1.SQL.Clear;
  Form2.ADOQuery1.SQL.Add('select');
  Form2.ADOQuery1.SQL.Add('m.*,');
  Form2.ADOQuery1.SQL.Add('n.a_date_pmd_ruch,');
  Form2.ADOQuery1.SQL.Add('n.DecryptMailMO_InNumb,');
  Form2.ADOQuery1.SQL.Add('n.DecryptMailMO_Date,');
  Form2.ADOQuery1.SQL.Add('n.DecryptMailMO_Reason,');
  Form2.ADOQuery1.SQL.Add('n.date_expert,');
  Form2.ADOQuery1.SQL.Add('n.fio_exp_kmp,');
  Form2.ADOQuery1.SQL.Add('n.date_expert_vozv,');
  Form2.ADOQuery1.SQL.Add('n.PRIM_GOR,');
  Form2.ADOQuery1.SQL.Add('n.PRIM,');
  Form2.ADOQuery1.SQL.Add('n.PRIM_OPERATOR,');
  Form2.ADOQuery1.SQL.Add('n.PRIM_TIMESTAMP');
  Form2.ADOQuery1.SQL.Add('from base_x.dbo.' + main + ' m');
  Form2.ADOQuery1.SQL.Add('left join base_x.dbo.' + note + ' n on n.id_schet_sluch = m.id_schet_sluch');
  Form2.ADOQuery1.SQL.Add('where 1=1');
  Form2.ADOQuery1.SQL.Add('and m.fio_exp = ' + quotedStr(UsernameShort));
  Form2.ADOQuery1.Active := true;
  ColumnReadOnlyFalse();
  Form2.DBNavigator1.Visible := true;
end;

//------------------------------------------------------------------------------

procedure AdminUser;
begin
  Form2.DBGrid1.ReadOnly := true;
  Form2.ADOQuery1.Active := false;
  Form2.ADOQuery1.SQL.Clear;
  Form2.ADOQuery1.SQL.Add('select');
  Form2.ADOQuery1.SQL.Add('m.*,');
  Form2.ADOQuery1.SQL.Add('n.a_date_pmd_ruch,');
  Form2.ADOQuery1.SQL.Add('n.DecryptMailMO_InNumb,');
  Form2.ADOQuery1.SQL.Add('n.DecryptMailMO_Date,');
  Form2.ADOQuery1.SQL.Add('n.DecryptMailMO_Reason,');
  Form2.ADOQuery1.SQL.Add('n.date_expert,');
  Form2.ADOQuery1.SQL.Add('n.fio_exp_kmp,');
  Form2.ADOQuery1.SQL.Add('n.date_expert_vozv,');
  Form2.ADOQuery1.SQL.Add('n.PRIM_GOR,');
  Form2.ADOQuery1.SQL.Add('n.PRIM,');
  Form2.ADOQuery1.SQL.Add('n.PRIM_OPERATOR,');
  Form2.ADOQuery1.SQL.Add('n.PRIM_TIMESTAMP');
  Form2.ADOQuery1.SQL.Add('from base_x.dbo.' + main + ' m');
  Form2.ADOQuery1.SQL.Add('left join base_x.dbo.' + note + ' n on n.id_schet_sluch = m.id_schet_sluch');
  Form2.ADOQuery1.Active := true;
  ColumnReadOnlyFalse();
  Form2.DBNavigator1.Visible := true;
end;

//------------------------------------------------------------------------------

procedure TForm2.FormCreate(Sender: TObject);
begin
  Ini := TiniFile.Create(extractfilepath(paramstr(0))+'settings.ini');
  main := Ini.ReadString('main','main','');
  note := Ini.ReadString('note','note','');

  AllUsers();
end;

//------------------------------------------------------------------------------

procedure TForm2.CheckBox1Click(Sender: TObject);
begin
  if CheckBox1.Checked = true then
  begin
    if role = 1 then
      CurrentUser()
    else if role = 2 then
      AdminUser();
  end
  else if CheckBox1.Checked = false then
  begin
    AllUsers();
  end;
end;

//------------------------------------------------------------------------------

procedure TForm2.AppMessage(var Msg: TMsg; var Handled: Boolean);
var
i: SmallInt;   
begin
  if Msg.message = WM_MOUSEWHEEL then
  begin
    Msg.message := WM_KEYDOWN;
    Msg.lParam := 0;
    i := HiWord(Msg.wParam);
    if i > 0 then
      Msg.wParam := VK_UP
    else
      Msg.wParam := VK_DOWN;
    Handled := False;
  end;
end;

//------------------------------------------------------------------------------

procedure TForm2.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  Application.Terminate;
  Exit;
end;

//------------------------------------------------------------------------------

procedure TForm2.FormShow(Sender: TObject);
begin
  Form2.WindowState := wsMaximized;
  Application.OnMessage := Form2.AppMessage;
end;

//------------------------------------------------------------------------------

procedure TForm2.Button1Click(Sender: TObject);
begin
  //Form3.Visible := true;
  Form3.Show;
end;

//------------------------------------------------------------------------------

procedure TForm2.SpeedButton1Click(Sender: TObject);
var
  ExcelApp, Workbook, Range, Cell1, Cell2, ArrayData : Variant;
  TemplateFile : String;
  BeginCol, BeginRow, i, j, t : Integer;
  RowCount, ColCount : Integer;
begin
  BeginCol := 1;
  BeginRow := 2;

  RowCount := DBGrid1.DataSource.DataSet.RecordCount;
  ColCount := DBGrid1.FieldCount;

  ExcelApp := CreateOleObject('Excel.Application');

  ExcelApp.Application.EnableEvents := false;

  Workbook := ExcelApp.WorkBooks.Open(ExtractFilePath(Application.ExeName) + 'Template\Template.xlsx');

  with DbGrid1.DataSource.DataSet do
  begin
    Last;
    First;
    ArrayData := VarArrayCreate([1, RecordCount, 1, Fields.Count], varVariant);

    for i:=1 to RecordCount do
    begin
      for j:=1 to Fields.Count do
        ArrayData[i,j] := Fields[j-1].AsVariant;
      Next;
    end;
  end;

  Cell1 := WorkBook.WorkSheets[1].Cells[BeginRow, BeginCol];

  Cell2 := WorkBook.WorkSheets[1].Cells[BeginRow  + RowCount - 1, BeginCol + ColCount - 1];

  Range := WorkBook.WorkSheets[1].Range[Cell1, Cell2];

  Range.Value := ArrayData;

  ExcelApp.Visible := false;

  ExcelApp.WorkBooks[1].SaveAs(ExtractFilePath(Application.ExeName) + 'Âûãðóçêè\Ëåòàëüíûå ñëó÷àè.xlsx');
  ExcelApp.Quit;
end;

//------------------------------------------------------------------------------

procedure TForm2.DBGrid1ColEnter(Sender: TObject);
begin
with TDBGrid(Sender) do
   if Columns[SelectedIndex].ReadOnly
     then Options := Options - [dgEditing]
     else Options := Options + [dgEditing];
end;

//------------------------------------------------------------------------------

end.