unit KCHZ;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ComCtrls, CheckLst, ExtCtrls, Grids, DB, ADODB, Menus,
  ImgList;

type
  TKCHZForm = class(TForm)
    StringGrid1: TStringGrid;
    Panel1: TPanel;
    Label3: TLabel;
    DateTimePicker1: TDateTimePicker;
    DateTimePicker2: TDateTimePicker;
    Label4: TLabel;
    DateTimePicker3: TDateTimePicker;
    DateTimePicker4: TDateTimePicker;
    ComboBox2: TComboBox;
    Label5: TLabel;
    ComboBox6: TComboBox;
    Label7: TLabel;
    ComboBox4: TComboBox;
    ComboBox7: TComboBox;
    Label8: TLabel;
    ComboBox1: TComboBox;
    ComboBox3: TComboBox;
    Button1: TButton;
    Button2: TButton;
    Label2: TLabel;
    ComboBox5: TComboBox;
    StatusBar1: TStatusBar;
    PopupMenu2: TPopupMenu;
    MenuItem1: TMenuItem;
    MenuItem2: TMenuItem;
    MenuItem3: TMenuItem;
    N22: TMenuItem;
    N23: TMenuItem;
    ImageList2: TImageList;
    Label1: TLabel;
    ComboBox8: TComboBox;
    Button3: TButton;
    SaveDialog1: TSaveDialog;
    procedure FormShow(Sender: TObject);
    procedure ComboBox4Change(Sender: TObject);
    procedure ComboBox2Change(Sender: TObject);
    procedure ComboBox1Change(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure StringGrid1DrawCell(Sender: TObject; ACol, ARow: Integer;
      Rect: TRect; State: TGridDrawState);
    procedure Button2Click(Sender: TObject);
    procedure N23Click(Sender: TObject);
    procedure ComboBox8Change(Sender: TObject);
    procedure Button3Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  KCHZForm: TKCHZForm;

implementation

uses datamodule, ModiMX, KCGL;

{$R *.dfm}

procedure TKCHZForm.FormShow(Sender: TObject);
var
  query: Tadoquery;
  sql: string;
  i: integer;
  temp: string;
begin

  datetimepicker1.Date := date;
  datetimepicker3.Date := date + 1;

  datetimepicker2.Time := strtotime('00:00:00');
  datetimepicker4.Time := strtotime('00:00:00');

  for i := 0 to stringgrid1.RowCount - 1 do
    stringgrid1.Rows[i].Clear;

  stringgrid1.cells[1, 0] := '實(shí)驗(yàn)課機(jī)時(shí)（小時(shí)）';

  query := Tadoquery.Create(self);
  query.Connection := dmfm.ADOConnection;
  sql := 'select * from Tkc ';
  query.close;
  query.SQL.text := sql;
  query.open;
  combobox5.Clear;
  combobox5.Items.Add('');
  while not query.Eof do
  begin
    combobox5.Items.Add(trim(query.fieldbyname('Fname').asstring));
    query.Next;
  end;

  sql := 'select * from Tteach ';
  query.close;
  query.SQL.text := sql;
  query.open;
  combobox2.Clear;
  combobox2.Items.Add('');
  combobox6.Clear;
  combobox6.Items.Add('');
  while not query.Eof do
  begin
    combobox2.Items.Add(trim(query.fieldbyname('Fname').asstring));
    combobox6.Items.Add(trim(query.fieldbyname('Fnum').asstring));
    query.Next;
  end;

  sql := 'select * from TBJ ';
  query.close;
  query.SQL.text := sql;
  query.open;
  combobox4.Clear;
  combobox4.Items.Add('');
  combobox7.Clear;
  combobox7.Items.Add('');
  while not query.Eof do
  begin
    combobox4.Items.Add(trim(query.fieldbyname('Fname').asstring));
    combobox7.Items.Add(trim(query.fieldbyname('ID').asstring));
    query.Next;
  end;

  sql := 'select * from TJfinfo ';
  query.close;
  query.SQL.text := sql;
  query.open;
  combobox1.Clear;
  combobox1.Items.Add('');
  combobox3.Clear;
  combobox3.Items.Add('');
  while not query.Eof do
  begin
    combobox3.Items.Add(trim(query.fieldbyname('fnum').asstring));
    combobox1.Items.Add(trim(query.fieldbyname('fname').asstring));
    query.Next;
  end;
end;

procedure TKCHZForm.ComboBox4Change(Sender: TObject);
begin
  combobox7.ItemIndex := combobox4.itemindex;

end;

procedure TKCHZForm.ComboBox2Change(Sender: TObject);
begin
  combobox6.ItemIndex := combobox2.itemindex;
end;

procedure TKCHZForm.ComboBox1Change(Sender: TObject);
begin
  combobox3.ItemIndex := combobox1.itemindex;

end;

procedure TKCHZForm.Button1Click(Sender: TObject);
var
  i, row: integer;
  sql: string;
  query: Tadoquery;
  HJ: Currency;
begin
  if combobox8.Text = '' then
  begin
    messagebox(handle, '統(tǒng)計(jì)依據(jù)不能為空', '提示', mb_iconwarning);
    combobox8.SetFocus;
    exit;
  end;

  for i := 1 to stringgrid1.RowCount - 1 do
    stringgrid1.Rows[i].Clear;

  query := Tadoquery.Create(self);
  query.Connection := dmfm.ADOConnection;
  sql := '';
  if combobox8.Text = '教師' then
    sql := 'select c.Fname,sum(cast(datediff(minute,fbegintime,fendtime) as money)/60) from ';
  if combobox8.Text = '班級(jí)' then
    sql := 'select d.Fname,sum(cast(datediff(minute,fbegintime,fendtime) as money)/60) from ';
  if combobox8.Text = '課程' then
    sql := 'select fKCname,sum(cast(datediff(minute,fbegintime,fendtime) as money)/60) from ';

  sql := sql + format('   (select a.*,b.Fname from Tkcapmx as a left join Tteach as b on a.fjsnum=b.fnum )as c ' +
    ' left join ' +
    ' TBJ as d  ' +
    ' on c.fbjid=d.id ' +
    ' where fbegintime>=''%s'' and fbegintime<=''%s'' ',
    [datetostr(datetimepicker1.Date) + ' ' + timetostr(datetimepicker2.Time),
    datetostr(datetimepicker3.Date) + ' ' + timetostr(datetimepicker4.Time)]);

  if combobox5.text <> '' then
    sql := sql + ' and fkcname=''' + combobox5.text + '''';

  if combobox4.text <> '' then
    sql := sql + ' and fbjID=' + combobox7.text;

  if combobox2.Text <> '' then
    sql := sql + ' and fjsnum=' + combobox6.text;

  if combobox1.Text <> '' then
    sql := sql + ' and fjfnum+'','' like ' + '''%''''' + combobox3.text + ''''',%''';

  if combobox8.Text = '教師' then
    sql := sql + 'group by c.Fname';
  if combobox8.Text = '班級(jí)' then
    sql := sql + 'group by d.Fname';
  if combobox8.Text = '課程' then
    sql := sql + 'group by fKCname ';

  query.close;
  query.sql.text := sql;
  query.Open;

  if query.IsEmpty then
    Stringgrid1.RowCount := 2
  else
    Stringgrid1.RowCount := query.RecordCount + 2;

  row := 1;
  HJ := 0;
  self.StatusBar1.Panels[0].Text := '共查詢到 ' + inttostr(query.RecordCount) + ' 條記錄';
  while not query.Eof do
  begin
    stringgrid1.Cells[0, row] := query.Fields[0].AsString;
    stringgrid1.Cells[1, row] := formatfloat('0.00', query.Fields[1].value);
    HJ := HJ + query.Fields[1].Value;
    query.Next;
    row := row + 1;
  end;

  stringgrid1.Cells[0, row] := '總計(jì)';
  stringgrid1.Cells[1, row] := formatfloat('0.00', HJ);

  query.Destroy;
end;

procedure TKCHZForm.StringGrid1DrawCell(Sender: TObject; ACol,
  ARow: Integer; Rect: TRect; State: TGridDrawState);
var
  x, y: integer;
begin
  stringgrid1.Canvas.FillRect(rect);
  x := stringgrid1.Canvas.TextWidth(stringgrid1.cells[acol, arow]);
  y := stringgrid1.Canvas.TextHeight(stringgrid1.cells[acol, arow]);
  stringgrid1.Canvas.TextOut(rect.left + 2, rect.top + stringgrid1.rowheights[0] div 2 - y div 2, stringgrid1.cells[acol, arow]);

  if (arow = 0) then
  begin
    stringgrid1.Canvas.font.Name := '宋體';
    stringgrid1.Canvas.font.Size := 9;
    stringgrid1.Canvas.FillRect(rect);
    x := stringgrid1.Canvas.TextWidth(stringgrid1.cells[acol, arow]);
    y := stringgrid1.Canvas.TextHeight(stringgrid1.cells[acol, arow]);
    stringgrid1.Canvas.TextOut(rect.left + stringgrid1.ColWidths[acol] div 2 - x div 2, rect.top + stringgrid1.rowheights[0] div 2 - y div 2, stringgrid1.cells[acol, arow]);
    exit;
  end;

  if arow = stringgrid1.row then
  begin
    stringgrid1.Canvas.Brush.Color := clblue;
    stringgrid1.Canvas.FillRect(rect);
    stringgrid1.Canvas.font.Name := '宋體';
    stringgrid1.Canvas.font.Color := clwhite;
    stringgrid1.Canvas.font.Size := 9;
    stringgrid1.Canvas.Font.Style := [fsBold];
    stringgrid1.Canvas.FillRect(rect);
    x := stringgrid1.Canvas.TextWidth(stringgrid1.cells[acol, arow]);
    y := stringgrid1.Canvas.TextHeight(stringgrid1.cells[acol, arow]);
    stringgrid1.Canvas.TextOut(rect.left + 2, rect.top + stringgrid1.rowheights[0] div 2 - y div 2, stringgrid1.cells[acol, arow]);
  end;

end;

procedure TKCHZForm.Button2Click(Sender: TObject);
begin
  close;
end;

procedure TKCHZForm.N23Click(Sender: TObject);
begin
  button1.click;
end;

procedure TKCHZForm.ComboBox8Change(Sender: TObject);
begin
  stringgrid1.Cells[0, 0] := combobox8.Text;
end;

procedure TKCHZForm.Button3Click(Sender: TObject);
var
  Myfile: TextFile;
  i: integer;
begin
  if savedialog1.Execute then
  begin
    AssignFile(Myfile, SaveDialog1.FileName);
    Rewrite(Myfile);
    for i := 0 to StringGrid1.RowCount - 1 do
      Writeln(Myfile, StringGrid1.cells[0, i] + #9 + Stringgrid1.cells[1, i] + #9);
    CloseFile(Myfile);
  end;
end;

end.