unit dept;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, Buttons, Db, DBTables,datamd, Grids, DBGrids, ComCtrls,report;

type
  Tdept_m = class(TForm)
    GroupBox1: TGroupBox;
    Label1: TLabel;
    ComboBox1: TComboBox;
    BitBtn1: TBitBtn;
    BitBtn2: TBitBtn;
    GroupBox2: TGroupBox;
    Label2: TLabel;
    Edit1: TEdit;
    Label3: TLabel;
    ComboBox2: TComboBox;
    BitBtn3: TBitBtn;
    BitBtn4: TBitBtn;
    GroupBox3: TGroupBox;
    DBGrid1: TDBGrid;
    BitBtn5: TBitBtn;
    BitBtn6: TBitBtn;
    Label4: TLabel;
    DateTimePicker1: TDateTimePicker;
    DateTimePicker2: TDateTimePicker;
    DateTimePicker3: TDateTimePicker;
    DateTimePicker4: TDateTimePicker;
    Label5: TLabel;
    procedure FormShow(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure BitBtn3Click(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    procedure BitBtn4Click(Sender: TObject);
    procedure BitBtn5Click(Sender: TObject);
    procedure BitBtn6Click(Sender: TObject);

  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  dept_m: Tdept_m;

implementation

{$R *.DFM}

procedure Tdept_m.FormShow(Sender: TObject);
begin
 datetimepicker1.Date:=date;
 datetimepicker2.Date:=date;
 combobox1.Items.Clear;
 combobox2.Items.Clear;
 dddata.qy5.SQL.clear;
  dddata.qy5.sql.add('select * from dept');
  dddata.qy5.open;
 while not  dddata.qy5.eof do
 begin
   combobox1.Items.Add( dddata.qy5.fieldbyname('dept_nm').asstring);
   combobox2.Items.Add( dddata.qy5.fieldbyname('dept_nm').asstring);
   dddata.qy5.Next;
 end;
end;

procedure Tdept_m.BitBtn1Click(Sender: TObject);
begin
 dddata.qy5.SQL.clear;
 dddata.qy5.SQL.add('select * from dept where dept_nm=:dept ');
 dddata.qy5.parambyname('dept').asstring:=combobox1.text;
 dddata.qy5.open;
 if dddata.qy5.recordcount>0 then
 abort;
 dddata.qy5.SQL.clear;
 dddata.qy5.SQL.add('insert into dept(dept_nm) values(:dept)');
 dddata.qy5.ParamByName('dept').asstring:=combobox1.text;
 dddata.Qy5.ExecSQL;
 application.MessageBox('ok','message',mb_ok);
end;

procedure Tdept_m.BitBtn3Click(Sender: TObject);
begin
 dddata.qy5.SQL.clear;
 dddata.qy5.SQL.add('select * from tel_dept where ext_num=:ext');
 dddata.qy5.parambyname('ext').asstring:=edit1.text;
 dddata.qy5.open;
 if dddata.qy5.recordcount>0 then
 abort;

 dddata.qy5.SQL.clear;
 dddata.qy5.SQL.add('insert into tel_dept(dept_nm,ext_num) values(:dept,:ext)');
 dddata.qy5.ParamByName('dept').asstring:=combobox2.text;
 dddata.qy5.ParamByName('ext').asstring:=edit1.text;
 dddata.Qy5.ExecSQL;
 application.MessageBox('ok','message',mb_ok);
end;

procedure Tdept_m.BitBtn2Click(Sender: TObject);
begin
  dddata.qy5.sql.clear;
  dddata.qy5.sql.add('delete  from dept where dept_nm=:dept');
  dddata.qy5.ParamByName('dept').asstring:=combobox1.text;
  dddata.qy5.ExecSQL;
  application.MessageBox('ok','message',mb_ok);
end;

procedure Tdept_m.BitBtn4Click(Sender: TObject);
begin
  dddata.qy5.sql.clear;
  dddata.qy5.sql.add('delete  from tel_dept where ext_num=:ext');
  dddata.qy5.ParamByName('ext').asstring:=edit1.text;
  dddata.qy5.ExecSQL;
  application.MessageBox('ok','message',mb_ok);
end;

procedure Tdept_m.BitBtn5Click(Sender: TObject);
begin
 dddata.Qy6.sql.clear;
 dddata.Qy6.sql.add('select a.ext_num  分機(jī),tel_dt 日期,tel_tt 時間,tel_num 電話,tel_sc 時長, total 話費,dept_nm as 部門 ');
 dddata.qy6.sql.add('from  teletrn a,tel_dept b where');
 dddata.Qy6.sql.add(' a.ext_num=b.ext_num and tel_dt>=:d1 and tel_dt<=:d2 '); //
 dddata.Qy6.sql.add(' and tel_tt>=:t1 and tel_tt<=:t2 ');
 if edit1.text<>'' then
 begin
 dddata.Qy6.sql.add(' and a.ext_num=:ext ');
 dddata.Qy6.ParamByName('ext').asstring:=edit1.text;
 end;
 if combobox2.Text<>'' then
 begin
 dddata.Qy6.sql.add(' and  b.dept_nm=:dept');
 dddata.Qy6.ParamByName('dept').asstring:=combobox2.text;
 end;
 dddata.Qy6.ParamByName('d1').asstring:=formatdatetime('yy-mm-dd',datetimepicker1.Date);;//datetostr(datetimepicker1.Date);
 dddata.Qy6.ParamByName('d2').asstring:=datetostr(datetimepicker2.Date);
 dddata.Qy6.ParamByName('t1').asstring:=timetostr(datetimepicker3.time);
 dddata.Qy6.ParamByName('t2').asstring:=timetostr(datetimepicker4.time);
 dddata.Qy6.open;


end;

procedure Tdept_m.BitBtn6Click(Sender: TObject);
begin
if dddata.qy6.recordcount>0 then
begin

 report_m.QuickRep1.DataSet:=dddata.qy6;
 report_m.QuickRep1.Preview;
 end;
end;

end.