unit Unit7;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, mysql55conn, sqldb, db, FileUtil, Forms, Controls,
  Graphics, Dialogs, Menus, ComCtrls, StdCtrls, DBGrids, ExtCtrls, ComObj;

type

  { TForm7 }

  TForm7 = class(TForm)
    Button1: TButton;
    Button2: TButton;
    Button3: TButton;
    Button4: TButton;
    Button5: TButton;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    DBGrid2: TDBGrid;
    Label1: TLabel;
    Label2: TLabel;
    MainMenu1: TMainMenu;
    Memo1: TMemo;
    MenuItem1: TMenuItem;
    MenuItem2: TMenuItem;
    MenuItem3: TMenuItem;
    MenuItem4: TMenuItem;
    MenuItem5: TMenuItem;
    MenuItem6: TMenuItem;
    MySQL55Connection1: TMySQL55Connection;
    OpenDialog1: TOpenDialog;
    PageControl1: TPageControl;
    RadioGroup1: TRadioGroup;
    SQLQuery1: TSQLQuery;
    SQLTransaction1: TSQLTransaction;
    TabSheet1: TTabSheet;
    TabSheet2: TTabSheet;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure Button4Click(Sender: TObject);
    procedure Button5Click(Sender: TObject);
    procedure FormClose(Sender: TObject; var CloseAction: TCloseAction);
    procedure FormHide(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure MenuItem1Click(Sender: TObject);
    procedure MenuItem2Click(Sender: TObject);
    procedure MenuItem3Click(Sender: TObject);
    procedure MenuItem4Click(Sender: TObject);
    procedure MenuItem6Click(Sender: TObject);
    procedure RadioGroup1Click(Sender: TObject);
  private

  public

  end;

var
  Form7: TForm7;
  excel_path:string;

implementation
uses Unit1,Unit2,Unit3,Unit4,Unit5,Unit6;
{$R *.lfm}

{ TForm7 }

procedure TForm7.MenuItem1Click(Sender: TObject);
begin
  Form7.Hide;
  Form1.Show;
end;

procedure TForm7.FormShow(Sender: TObject);
begin
  case Unit5.role of
  1:begin
     MenuItem6.Visible:=True;
     MenuItem3.Visible:=True;
   end;
   2:begin
     MenuItem3.Visible:=True;
     MenuItem6.Visible:=False;
   end;
   3:begin
     MenuItem6.Visible:=False;
     MenuItem3.Visible:=False;
   end;
  end;
end;

procedure TForm7.Button3Click(Sender: TObject);
var
  query:string;
  i:integer;
begin
  for i:=0 to Memo1.Lines.Count-1 do
     query:=query+Memo1.Lines[i]+' ';
  try
    SQLQuery1.Close;
    SQLQuery1.SQL.Clear;
    SQLQuery1.SQL.Add(query);
    SQLQuery1.Open;
    Button2.Enabled:=True;
  except
    on ESQLDatabaseError do ShowMessage('Некорректный запрос');
    on EDatabaseError do ShowMessage('Задан пустой запрос');
  end;
end;

procedure TForm7.Button4Click(Sender: TObject);
begin

  if OpenDialog1.Execute then
    excel_path:= OpenDialog1.FileName;
  Button5.Enabled:=True;
end;

procedure TForm7.Button5Click(Sender: TObject);
var
  S,S1,S2: string;
  i,j,count: integer;
  Excelapp:Variant;
begin
  Excelapp:=CreateOleObject('Excel.Application');
  Excelapp.WorkBooks.Open(WideString(excel_path));
  ExcelApp.Visible := false;
  case RadioGroup1.ItemIndex of
  0:begin
    count:=4;
    S:='insert into patients(first_name,second_name,birth_date,category_id) values';
  end;
  1:begin
    count:=4;
    S:='insert into doctors(first_name,second_name,speciality_id,category_id) values';
  end;
  2:begin
    count:=2;
    S:='insert into appeals(date,patient_id) values';
  end;
  3:begin
    count:=4;
    S:='insert into treatments(appeal_id,doctor_ID,diagnode_id,treatment_cost) values';
  end;
  end;

  for i:=1 to 100 do
  begin
   S1:='(';
   for j:=1 to count do
   begin
      s2:=Excelapp.Cells[i,j].value;
      if(s2<>'')then
           s1:=s1+s2+','
      else
           break;
    end;
   if(S1<>'(') then begin
      Delete(S1,Length(S1),1);
      S:=S+S1+'),';
   end
   else
       break;
   end;
  Delete(S,Length(S),1);
  try
    SQLQuery1.Close;
    SQLQuery1.SQL.Clear;
    SQLQuery1.SQL.Add(S);
    SQLQuery1.ExecSQL;
    SQLTransaction1.Commit;
    ShowMessage('Данные выгружены в БД');

    case RadioGroup1.ItemIndex of
    0:S:='select * from patients';
    1:S:='select * from doctors';
    2:S:='select * from appeals';
    3:S:='select * from treatments';
    end;
    SQLQuery1.Close;
    SQLQuery1.SQL.Clear;
    SQLQuery1.SQL.Add(S);
    SQLQuery1.Open;
  finally
   ExcelApp.Quit;
  end;
end;

procedure TForm7.FormClose(Sender: TObject; var CloseAction: TCloseAction);
begin
  Application.Terminate;
end;

procedure TForm7.FormHide(Sender: TObject);
begin
  SQLQuery1.Close;
end;

procedure TForm7.Button1Click(Sender: TObject);
var
  path:string;
begin
  if OpenDialog1.Execute then
   begin
    path:= OpenDialog1.FileName;
    Memo1.Text := ReadFileToString(path);
   end;
end;

function IsNumber(a:string):boolean;
var
  n:real;
  c:integer;
begin
  val(a,n,c);
  IsNumber:=c=0;
end;

procedure TForm7.Button2Click(Sender: TObject);
var
i,j: Integer;
ExcelApp,Sheet, Workbook: Variant;
s:string;
begin
  if(DBGrid1.Columns.Count=0) then
    ShowMessage('Нет данных для экспорта в Excel')
  else
      begin
         ExcelApp:= CreateOleObject('Excel.Application');
         ExcelApp.Visible:=False;
         Workbook := ExcelApp.WorkBooks.Add;
         Sheet := ExcelApp.Workbooks[1].WorkSheets[1];
         DBGrid1.DataSource.DataSet.First;
         for i:=1 to DBGrid1.DataSource.DataSet.RecordCount do
         begin
              for j:=1 to DBGrid1.DataSource.DataSet.FieldCount do
              begin
               s:=DBGrid1.DataSource.DataSet.Fields[j-1].AsWideString;
               if(IsNumber(s)) then
                   Sheet.cells[i,j]:=DBGrid1.DataSource.DataSet.Fields[j-1].AsWideString
               else
                   Sheet.cells[i,j]:='"'+DBGrid1.DataSource.DataSet.Fields[j-1].AsWideString+'"';
              end;
            DBGrid1.DataSource.DataSet.Next;
         end;
         ExcelApp.Visible := True;
      end;
end;

procedure TForm7.MenuItem2Click(Sender: TObject);
begin
  Form7.Hide;
  Form2.Show;
end;

procedure TForm7.MenuItem3Click(Sender: TObject);
begin
  Form7.Hide;
  Form3.Show;
end;

procedure TForm7.MenuItem4Click(Sender: TObject);
begin
  Form7.Hide;
  Form4.Show;
end;

procedure TForm7.MenuItem6Click(Sender: TObject);
begin
  Form7.Hide;
  Form6.Show;
end;

procedure TForm7.RadioGroup1Click(Sender: TObject);
begin
  Button4.Enabled:=True;
end;

end.