tongjifrm.pas

信息管理系统

unit tongjifrm;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, Dialogs,
  ExtCtrls, StdCtrls, Buttons, Grids, DBGrids, ComCtrls, Db, DBTables,
  Excel2000, OleServer, ExcelXP,persondatamod_unit,ADODB,paichein_unit,
  DBClient;

type
  Ttjlcfrm = class(TForm)
    Panel1: TPanel;
    GroupBox1: TGroupBox;
    RadioButton1: TRadioButton;
    RadioButton2: TRadioButton;
    RadioButton3: TRadioButton;
    BitBtn1: TBitBtn;
    BitBtn2: TBitBtn;
    Label1: TLabel;
    Edit3: TEdit;
    Label2: TLabel;
    Edit1: TEdit;
    Splitter1: TSplitter;
    Panel2: TPanel;
    Splitter2: TSplitter;
    RadioButton4: TRadioButton;
    Label3: TLabel;
    Label4: TLabel;
    Edit2: TEdit;
    Edit4: TEdit;
    BitBtn3: TBitBtn;
    ExcelApplication1: TExcelApplication;
    ExcelWorkbook1: TExcelWorkbook;
    ExcelWorksheet1: TExcelWorksheet;
    paiche_ds1: TDataSource;
    DBGrid1: TDBGrid;
    DBGrid2: TDBGrid;
    paiche_cds1: TClientDataSet;
    paiche_cds2: TClientDataSet;
    paiche_ds2: TDataSource;
    procedure FormShow(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure DataSource1DataChange(Sender: TObject; Field: TField);
    procedure BitBtn3Click(Sender: TObject);
    procedure FormActivate(Sender: TObject);
    procedure DBGrid1CellClick(Column: TColumn);
  private
  tjfs:string;      // 统计方式
  datamod:Tperson_datamod;
  datestr:string;
  paiche:Tpaichein;
  sql:string;
    { Private declarations }
  public
    { Public declarations }
  end;

var
  tjlcfrm: Ttjlcfrm;

implementation

{$R *.DFM}

procedure Ttjlcfrm.FormShow(Sender: TObject);
begin
  datestr:=datetostr(date);         //当前日期
  RadioButton1.Checked :=True;
  RadioButton2.Checked :=false;
  RadioButton3.Checked :=false;
  RadioButton4.Checked :=false;
  edit1.Text:='';                   //存放出车次数
  edit3.Text:='';                   //存放行车里程
  edit2.Text:=datestr;              //起始日期
  edit4.Text:=datestr;              //截止日期
end;

procedure Ttjlcfrm.BitBtn2Click(Sender: TObject); //退出
begin
  Try                          //断开与Excel的连接
    ExcelApplication1.Quit;
    ExcelWorksheet1.Disconnect;
    ExcelWorkbook1.Disconnect;
    ExcelApplication1.Disconnect;
  Except
  End;
  paiche.Free;
  close;
end;

procedure Ttjlcfrm.BitBtn1Click(Sender: TObject);
var
  sql1:string;
  sql2:string;
  sum:real;//里程总数
  n:integer;//出车总次数
begin
  sql1:='delete from work_info1  '+'insert work_info1  ';
  if (edit2.Text<>'') and (edit4.Text<>'') then
     sql1:=sql1+'select * from work_info where (NUM is not NULL)and(DATETIME>='+''''+edit2.Text+''')and(DATETIME<='+''''+edit4.Text+''')';
  if (edit2.Text<>'') and (edit4.Text='') then
     sql1:=sql1+'select * from work_info where (NUM is not NULL)and(DATETIME>='+''''+edit2.Text+''')';
  if (edit2.Text='') and (edit4.Text<>'') then
     sql1:=sql1+'select * from work_info where (NUM is not NULL)and(DATETIME<='+''''+edit4.Text+''')';
  if (edit2.Text='') and (edit4.Text='') then
     sql1:=sql1+'select * from work_info where (NUM is not NULL)';
  paiche.Execu(sql1);
  sql1:='select * from WORK_INFO1 ';
  n:=paiche.Find(sql1);    //统计总出车次数
  //sum:=paiche.Sum();
  //edit3.Text :=floattostr(sum);
  edit1.Text :=inttostr(n);

  if RadioButton1.Checked =true then   //按车辆编号统计
     begin
       sql2:='select VEHICLE_NO, NUM, TIMES from VEHICLE_NUM order by VEHICLE_NO';
       paiche_cds1.Data:= paiche.Sel(sql2)  ;
       //设置表格栏目显示宽度
       dbgrid1.Columns[0].Width :=85;   //车辆编号
       dbgrid1.Columns[1].Width :=62;   //行驶里程
       dbgrid1.Columns[2].Width :=62;   //出车次数
     end;

  if RadioButton2.Checked =true then     //按司机编号统计
     begin
       sql2:='select DRIVER_ID, NUM, TIMES from DRIVER_NUM order by DRIVER_ID';
       paiche_cds1.Data:= paiche.Sel(sql2)  ;
       //设置表格栏目显示宽度
       dbgrid1.Columns[0].Width :=85;   //司机编号
       dbgrid1.Columns[1].Width :=62;   //行驶里程
       dbgrid1.Columns[2].Width :=62;   //出车次数
     end;

  if RadioButton3.Checked =true then    //按单位统计
     begin
       sql2:='select USER_COMPANY, NUM, TIMES from COMPANY_NUM  order by USER_COMPANY';
       paiche_cds1.Data:= paiche.Sel(sql2)  ;
       //设置表格栏目显示宽度
       dbgrid1.Columns[0].Width :=85;    //用车单位
       dbgrid1.Columns[1].Width :=62;    //行驶里程
       dbgrid1.Columns[2].Width :=62;    //出车次数
     end;

  if RadioButton4.Checked =true then     //按日期统计
     begin
       sql2:='select DATETIME,NUM, TIMES from DATETIME_NUM order by DATETIME';
       paiche_cds1.Data:= paiche.Sel(sql2)  ;
       //设置表格栏目显示宽度
       dbgrid1.Columns[0].Width :=85;    //日期
       dbgrid1.Columns[1].Width :=62;    //行驶里程
       dbgrid1.Columns[2].Width :=62;    //出车次数
     end;
end;

procedure Ttjlcfrm.DataSource1DataChange(Sender: TObject; Field: TField);
var
  sql3:string;
begin               //按不同的统计方式显示细目
  if RadioButton1.Checked =true then     //按车号
     begin
       tjfs:=DBGrid1.Fields[0].AsString ;
       sql3:='select * from WORK_INFO1 where ((NUM is not NULL) and (VEHICLE_NO='+''''+tjfs+''''+')) order by DATETIME';
       paiche_cds2.Data:= paiche.Sel(sql3)  ;
     end;

  if RadioButton2.Checked =true then     //按司机
     begin
       tjfs:=paiche_cds1.fieldbyname('DRIVER_ID').asstring;
       sql3:='select * from WORK_INFO1 where ((NUM is not NULL) and (DRIVER_ID='+''''+tjfs+''''+')) order by DATETIME';
       paiche_cds2.Data:= paiche.Sel(sql3)  ;
     end;

  if RadioButton3.Checked =true then     //按单位
     begin
       tjfs:=paiche_cds1.fieldbyname('USER_COMPANY').asstring;
       sql3:='select * from WORK_INFO1 where ((NUM is not NULL) and (USER_COMPANY='+''''+tjfs+''''+')) order by DATETIME';
       paiche_cds2.Data:= paiche.Sel(sql3)  ;
     end;

  if RadioButton4.Checked =true then      //按日期
     begin
       tjfs:=paiche_cds1.fieldbyname('DATETIME').asstring;
       sql3:='select * from WORK_INFO1 where ((NUM is not NULL) and (DATETIME='+''''+tjfs+''''+')) order by VEHICLE_NO';
       paiche_cds2.Data:= paiche.Sel(sql3)  ;
     end;
end;

procedure Ttjlcfrm.BitBtn3Click(Sender: TObject);
var
  filename1:string;
  i:integer;
  j:variant;
  range1:variant;
begin                        //生成Excel表供打印用
  BitBtn3.Enabled:=false;    //防止重复调用，按扭为不可操作
  if RadioButton1.Checked =true then
     begin
       j:=1;
       datamod.Person_adot1.TableName:='VEHICLE_NUM';
     end;
  if RadioButton2.Checked =true then
     begin
       j:=2;
       datamod.Person_adot1.TableName:='DRIVER_NUM';
     end;

  if RadioButton3.Checked =true then
     begin
       j:=3;
       datamod.Person_adot1.TableName:='COMPANY_NUM';
     end;

  if RadioButton4.Checked =true then
     begin
       j:=4;
       datamod.Person_adot1.TableName:='DATETIME_NUM';
     end;
  //生成Excel表的文件路径和文件名
  filename1:=getcurrentdir+'\'+'出车统计表.xls';

//生成Excel表
  Try
    ExcelApplication1.Connect;    //打开、连接到Excel
  Except
  End;
  ExcelApplication1.Visible[0]:=True;  //Excel表是否可见
  ExcelWorkBook1.ConnectTo(ExcelApplication1.Workbooks.Open(filename1,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,0));
  ExcelWorkBook1.Activate;
  ExcelWorkSheet1.ConnectTo(ExcelWorkbook1.Worksheets.Item[j] as _WorkSheet);
  ExcelWorkSheet1.Activate;
  ExcelWorkSheet1.Name:=datamod.Person_adot1.TableName;   //工作表命名
  ExcelWorkSheet1.UsedRange[0].ClearContents; //清除旧内容
  ExcelWorkSheet1.UsedRange[0].borders.linestyle:=xlLineStyleNone; //清除表格框线
  ExcelWorksheet1.Cells.Item[1,1]:='出车统计表';                   //表格标题
  ExcelWorksheet1.Cells.Item[2,1]:=edit2.Text+'--'+edit4.Text;     //起止日期
  datamod.Person_adot1.Open;
  //将数据库表的记录字段名称作为表头
  ExcelWorksheet1.Cells.Item[3,1]:=datamod.Person_adot1.Fields[0].FieldName;
  ExcelWorksheet1.Cells.Item[3,2]:=datamod.Person_adot1.Fields[1].FieldName+'（公里）';
  ExcelWorksheet1.Cells.Item[3,3]:=datamod.Person_adot1.Fields[2].FieldName;
  ExcelWorksheet1.Cells.Item[3,4]:='备注';
  datamod.Person_adot1.First ;
  i:=4;    //起始行
  //将数据库表的记录字段内容逐一填入Excel的单元格
  while not datamod.Person_adot1.Eof do
  begin
    ExcelWorksheet1.Cells.Item[i,1]:=datamod.Person_adot1.Fields[0].AsString;
    ExcelWorksheet1.Cells.Item[i,2]:=datamod.Person_adot1.Fields[1].AsString;
    ExcelWorksheet1.Cells.Item[i,3]:=datamod.Person_adot1.Fields[2].AsString;
    datamod.Person_adot1.Next ;
    inc(i);
  end;
  datamod.Person_adot1.Close;
  //在Excel的最后一行填合计项
  ExcelWorksheet1.Cells.Item[i,1]:='合计';
  ExcelWorksheet1.Cells.Item[i,2]:='=sum(b4:b'+inttostr(i-1)+')';
  ExcelWorksheet1.Cells.Item[i,3]:='=sum(c4:c'+inttostr(i-1)+')';
  range1:=ExcelWorkSheet1.Range['a3','d'+inttostr(i)];  //确定加表格线区域
  //为Excel表加表格线
  range1.borders.linestyle:=xlcontinuous;   //细实线
  Range1.BorderAround(xlDouble,xlThick);    //外框双线
  Try
    ExcelWorkbook1.Save;         //保存工作簿文件
  Except
  End;
  BitBtn3.Enabled:=true;         //恢复按扭为可操作
end;

procedure Ttjlcfrm.FormActivate(Sender: TObject);
begin
   datamod:=Tperson_datamod.Create(nil); 
   paiche:=Tpaichein.create;
   ShortdateFormat:='YYYY-MM-DD';    //确定日期格式
   LongDateFormat:='YYYY-MM-DD';
   DateSeparator:='-';
end;

procedure Ttjlcfrm.DBGrid1CellClick(Column: TColumn);
var
  sql3:string;
begin               //按不同的统计方式显示细目
  if RadioButton1.Checked =true then     //按车号
     begin
       tjfs:=DBGrid1.Fields[0].AsString ;
       sql3:='select * from WORK_INFO1 where ((NUM is not NULL) and (VEHICLE_NO='+''''+tjfs+''''+')) order by DATETIME';
       paiche_cds2.Data:= paiche.Sel(sql3)  ;
     end;

  if RadioButton2.Checked =true then     //按司机
     begin
       tjfs:=paiche_cds1.fieldbyname('DRIVER_ID').asstring;
       sql3:='select * from WORK_INFO1 where ((NUM is not NULL) and (DRIVER_ID='+''''+tjfs+''''+')) order by DATETIME';
       paiche_cds2.Data:= paiche.Sel(sql3)  ;
     end;

  if RadioButton3.Checked =true then     //按单位
     begin
       tjfs:=paiche_cds1.fieldbyname('USER_COMPANY').asstring;
       sql3:='select * from WORK_INFO1 where ((NUM is not NULL) and (USER_COMPANY='+''''+tjfs+''''+')) order by DATETIME';
       paiche_cds2.Data:= paiche.Sel(sql3)  ;
     end;

  if RadioButton4.Checked =true then      //按日期
     begin
       tjfs:=paiche_cds1.fieldbyname('DATETIME').asstring;
       sql3:='select * from WORK_INFO1 where ((NUM is not NULL) and (DATETIME='+''''+tjfs+''''+')) order by VEHICLE_NO';
       paiche_cds2.Data:= paiche.Sel(sql3)  ;
     end;
end;

end.