unit KCfind;

interface

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

type
  TKCFindForm = 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;
    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 MenuItem2Click(Sender: TObject);
    procedure MenuItem3Click(Sender: TObject);
    procedure N23Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  KCFindForm: TKCFindForm;

implementation

uses datamodule, ModiMX, KCGL;

{$R *.dfm}

procedure TKCFindForm.FormShow(Sender: TObject);
var
  query: Tadoquery;
  sql: string;
  i: integer;
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[0, 0] := '項目編號';
  stringgrid1.cells[1, 0] := '項目名稱';
  stringgrid1.cells[2, 0] := '開始時間';
  stringgrid1.cells[3, 0] := '結束時間';
  stringgrid1.cells[4, 0] := '教師';
  stringgrid1.cells[5, 0] := '類型';
  stringgrid1.cells[6, 0] := '班級';
  stringgrid1.cells[7, 0] := '機器';
  stringgrid1.cells[8, 0] := '分錄ID';
  stringgrid1.ColWidths[8] := -1;

  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 xwpctb ';
  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('id').asstring));
    combobox1.Items.Add(trim(query.fieldbyname('pcname').asstring));
    query.Next;
  end;
end;

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

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

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

procedure TKCFindForm.Button1Click(Sender: TObject);
var
  i, row: integer;
  sql: string;
  query: Tadoquery;
begin
  for i := 1 to stringgrid1.RowCount - 1 do
    stringgrid1.Rows[i].Clear;

  query := Tadoquery.Create(self);
  query.Connection := dmfm.ADOConnection;

  sql := format(' select fkcnum,fkcname,fbegintime,fendtime,c.Fname,fsflx,d.fname,fjqname,fid from ' +
    '   (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 fjqname+'','' like ' + '''''''%' + combobox1.text + '%'''',''';

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

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

  row := 1;
  self.StatusBar1.Panels[0].Text := '共查詢到 ' + inttostr(query.RecordCount) + ' 條記錄';
  while not query.Eof do
  begin
    for i := 0 to 8 do
    begin
      if uppercase(query.Fields[i].FieldName) = 'FSFLX' then
        stringgrid1.cells[i, row] := kcglform.getSFLSName(query.Fields[i].AsString)
      else
        stringgrid1.cells[i, row] := trim(query.Fields[i].AsString);
    end;
    Stringgrid1.cells[9, row] := KCGLForm.getweektostr((dayofweek(query.fieldbyname('fbegintime').Value) - 1));
    query.Next;
    row := row + 1;
  end;

  query.Destroy;
end;

procedure TKCFindForm.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 TKCFindForm.Button2Click(Sender: TObject);
begin
  close;
end;

procedure TKCFindForm.MenuItem2Click(Sender: TObject);
begin
  if Stringgrid1.Cells[0, Stringgrid1.row] = '' then exit;
  modimxform.ID := stringgrid1.cells[8, Stringgrid1.row];
  if modiMXForm.showmodal = mrok then
    button1.Click;
end;

procedure TKCFindForm.MenuItem3Click(Sender: TObject);
var
  query: Tadoquery;
  sql: string;
begin
  if Stringgrid1.Cells[0, Stringgrid1.row] = '' then exit;
  if messagebox(handle, '是否刪除選中的課程安排明細項目？', '提示', mb_iconwarning + mb_yesno) = idno then exit;

  query := Tadoquery.Create(self);
  query.Connection := dmfm.ADOConnection;
  sql := 'delete Tkcapmx where fid=' + Stringgrid1.Cells[8, Stringgrid1.row];
  query.close;
  query.sql.text := sql;
  query.ExecSQL;

  button1.Click;
  query.Destroy;
end;

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

end.