driverstat.pas

管理车队用车记录

unit driverstat;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Grids, DBGrids, Buttons, ComCtrls, ExtCtrls, Mask,
  DBCtrls;

type
  TForm_driverstat = class(TForm)
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    TabSheet2: TTabSheet;
    Panel1: TPanel;
    Label5: TLabel;
    Label9: TLabel;
    Label10: TLabel;
    ComboBox1: TComboBox;
    DateTimePicker1: TDateTimePicker;
    DateTimePicker2: TDateTimePicker;
    Panel2: TPanel;
    BitBtn1: TBitBtn;
    BitBtn3: TBitBtn;
    BitBtn2: TBitBtn;
    DBGrid1: TDBGrid;
    Panel12: TPanel;
    Label45: TLabel;
    Label46: TLabel;
    Label48: TLabel;
    Label1: TLabel;
    Label3: TLabel;
    Label8: TLabel;
    DBEdit6: TDBEdit;
    DBEdit7: TDBEdit;
    DBEdit8: TDBEdit;
    DBEdit9: TDBEdit;
    DBEdit10: TDBEdit;
    DBEdit11: TDBEdit;
    Panel10: TPanel;
    Label37: TLabel;
    Label38: TLabel;
    Label39: TLabel;
    ComboBox4: TComboBox;
    DateTimePicker7: TDateTimePicker;
    DateTimePicker8: TDateTimePicker;
    DBGrid4: TDBGrid;
    Panel11: TPanel;
    BitBtn10: TBitBtn;
    BitBtn11: TBitBtn;
    BitBtn12: TBitBtn;
    Label2: TLabel;
    DBEdit1: TDBEdit;
    Label4: TLabel;
    DBEdit2: TDBEdit;
    procedure FormShow(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    procedure BitBtn10Click(Sender: TObject);
    procedure BitBtn12Click(Sender: TObject);
    procedure BitBtn11Click(Sender: TObject);
    procedure BitBtn3Click(Sender: TObject);
    procedure DBGrid1TitleClick(Column: TColumn);
    procedure DBGrid4TitleClick(Column: TColumn);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form_driverstat: TForm_driverstat;

implementation

uses DM, driverstatrep1, driverstatrep2;

{$R *.dfm}

procedure TForm_driverstat.FormShow(Sender: TObject);
begin
with form_dm.ADOtable_driver do
        begin
        open;
        Sort:='name';
        first;
        combobox1.Items.Clear;
        combobox4.Items.Clear;
        combobox1.Items.Add('全部');
        combobox4.Items.Add('全部');
        repeat
            combobox1.Items.Add(fieldbyname('name').AsString);
            combobox4.Items.Add(fieldbyname('name').AsString);
            Next;
        until eof;
        end;
combobox1.Text:='';
datetimepicker1.Date:=date;
datetimepicker2.Date:=date;
combobox4.Text:='';
datetimepicker7.Date:=date;
datetimepicker8.Date:=date;
end;

procedure TForm_driverstat.BitBtn1Click(Sender: TObject);
begin
    if datetimepicker1.Date>=datetimepicker2.Date then
begin
 showmessage('请选择你一个合适的时间段！');
 abort;
end;
if combobox1.Text<>'全部' then
form_dm.ADOQuery_carout.SQL.Text:='select car_out.*,car.type from car_out,car where car_out.car_id=car.id and driver_name='''+combobox1.Text+''' and  car_out.date between '''+ formatdatetime('yyyy-mm-dd',datetimepicker1.date)+''' and '''+formatdatetime('yyyy-mm-dd',datetimepicker2.date)+'''  order by car_out.unit;'
else
form_dm.ADOQuery_carout.SQL.Text:='select car_out.*,car.type from car_out,car where car_out.car_id=car.id and  car_out.date between '''+ formatdatetime('yyyy-mm-dd',datetimepicker1.date)+''' and '''+formatdatetime('yyyy-mm-dd',datetimepicker2.date)+'''  order by car_out.unit;';
form_dm.ADOQuery_carout.Open;
if form_dm.ADOQuery_carout.RecordCount=0 then
begin
   showmessage('在此段时间内该驾驶员无出车记录！');
   abort;
end;
end;


procedure TForm_driverstat.BitBtn2Click(Sender: TObject);
begin
close;
end;

procedure TForm_driverstat.BitBtn10Click(Sender: TObject);
var date1,date2,car_id:string;
begin
  if datetimepicker7.Date>=datetimepicker8.Date then
  begin
    showmessage('请选择你一个合适的时间段！');
    abort;
  end;
  date1:=formatdatetime('yyyy-mm-dd',datetimepicker7.date);
  date2:=formatdatetime('yyyy-mm-dd',datetimepicker8.date);
  car_id:=combobox4.text;

    form_dm.ADOCommand1.CommandText:='delete from butie1';
    form_dm.ADOCommand1.Execute;
    form_dm.ADOCommand1.CommandText:='insert into butie1(jsy,cph) '+'select A.jsy,B.cph from (select driver.name as jsy from driver) A left join (select driver_name,iif(car_id is null,''0'',car_id)as cph from driver_car )B on A.jsy = B.driver_name';
    form_dm.ADOCommand1.Execute;
    ////////////////////////////
    form_dm.ADOCommand1.CommandText:='delete from butie';
    form_dm.ADOCommand1.Execute;

    form_dm.ADOCommand1.CommandText:='insert into butie(jsy,cph,cgls) '+'select A.jsy,A.cph,iif(B.cgls is null,0,B.cgls)  from (select * from butie1) A left join (select car_id, sum(km) as cgls from car_out group by car_id )B on A.cph = B.car_id';
    form_dm.ADOCommand1.Execute;
    //////////////////////////////
    form_dm.ADOCommand1.CommandText:='delete from butie1';
    form_dm.ADOCommand1.Execute;

    form_dm.ADOCommand1.CommandText:='insert into butie1(jsy,cgls,bzyh) '+'select A.jsy, A.cgls,B.bzyh  from (select * from butie) A left join (select id,iif(oilconsume is null,0,oilconsume)as bzyh from car )B on A.cph = B.id';
    form_dm.ADOCommand1.Execute;

    form_dm.ADOCommand1.CommandText:='delete from butie';
    form_dm.ADOCommand1.Execute;

    form_dm.ADOCommand1.CommandText:='insert into butie(jsy,cgls,bzyh,gls,msr,glbt,sjf,jjbt,yxbt,qtjf,glf) select A.jsy,A.cgls,A.bzyh,iif(B.[公里数] is null,0,B.[公里数]),iif(B.[毛收入] is null,0,B.[毛收入]),iif(B.[公里补贴] is null,0,B.[公里补贴]),'+'iif(B.[手机费] is null,0,B.[手机费]),iif(B.[节假补贴] is null,0,B.[节假补贴]),'+'iif(B.[夜行补贴] is null,0,B.[夜行补贴]),iif(B.[其他奖罚] is null,0,B.[其他奖罚]),iif(B.[过路停车费] is null,0,B.[过路停车费]) '+'from (select jsy,bzyh,cgls from butie1) A  LEFT JOIN (select driver_name ,sum(km) as 公里数,round(sum(spend),1) as 毛收入,round(sum(km_bounty),1) as 公里补贴,'+'round(sum(sjb),1) as 手机费,round(sum(jjb),1)  as 节假补贴 ,round(sum(duty_bounty),1) as 夜行补贴,round(sum(other),1)  as 其他奖罚 ,round(sum(toll),1) as 过路停车费 '+' from car_out where date between '''+ date1+''' and '''+date2+''' group by driver_name) B ON A.jsy = B.driver_name';
     form_dm.ADOCommand1.Execute;

    form_dm.ADOCommand1.CommandText:='delete from butie1';
    form_dm.ADOCommand1.Execute;

    form_dm.ADOCommand1.CommandText:='insert into butie1(jsy,cgls,bzyh,gls,msr,glbt,sjf,jjbt,yxbt,qtjf,glf,hyl,zyf) '+'select A.jsy,A.cgls,A.bzyh,A.gls,A.msr,A.glbt,A.sjf,A.jjbt,A.yxbt,A.qtjf,A.glf,iif(B.[耗油量] is null,0,B.[耗油量]),iif(B.[总油费] is null,0,B.[总油费])'+'from (select * from butie )A LEFT JOIN (select driver_name ,sum(amount) as 耗油量 ,sum(total) as 总油费 from oil where date between '''+date1+''' and '''+date2+''' group by driver_name ) B  ON A.jsy = B.driver_name ';
    form_dm.ADOCommand1.Execute;
    form_dm.ADOCommand1.CommandText:='delete from butie';
    form_dm.ADOCommand1.Execute;
    form_dm.ADOCommand1.CommandText:='insert into butie(jsy,cgls,bzyh,gls,msr,glbt,sjf,jjbt,yxbt,qtjf,glf,hyl,zyf,wxf) '+'select A.jsy,A.cgls,A.bzyh,A.gls,A.msr,A.glbt,A.sjf,A.jjbt,A.yxbt,A.qtjf,A.glf,A.hyl,A.zyf,iif(B.[维修费] is null,0,B.[维修费])'+'from (select * from butie1 )A LEFT JOIN (select driver_name ,sum(total) as 维修费 from repair where date between '''+date1+''' and '''+date2+''' group by driver_name ) B  ON A.jsy = B.driver_name ';
    form_dm.ADOCommand1.Execute;
    form_dm.ADOCommand1.CommandText:='delete from butie1';
    form_dm.ADOCommand1.Execute;
  if combobox4.Text='全部' then
  begin
    form_dm.ADOQuery_common2.SQL.Text:='select *,round(iif(cgls=0,0,iif(hyl/gls*100 is null,0,hyl/cgls*100)),1) as bglyh, round(((bzyh-iif(cgls=0,0,iif(hyl/cgls*100 is null,0,hyl/cgls*100)))*cgls*:P),1) as jyj,round(sjf+yxbt+jjbt+glbt+qtjf+jyj) as zbt from butie  ';
    form_dm.ADOTable_params.Open;
    form_dm.ADOQuery_common2.Parameters.ParamByName('P').Value:=form_dm.ADOTable_params.FieldByName('jyj').AsFloat;
    form_dm.ADOQuery_common2.Open;
    form_dm.ADOTable_params.close;
  end
  else
  begin
    form_dm.ADOQuery_common2.SQL.Text:='select *,round(iif(cgls=0,0,iif(hyl/cgls*100 is null,0,hyl/cgls*100)),1) as bglyh,round(((bzyh-iif(cgls=0,0,iif(hyl/cgls*100 is null,0,hyl/cgls*100)))*cgls*:P),1) as jyj,round(sjf+yxbt+jjbt+glbt+qtjf+jyj) as zbt from butie where jsy='''+car_id+'''';
    form_dm.ADOTable_params.Open;
    form_dm.ADOQuery_common2.Parameters.ParamByName('P').Value:=form_dm.ADOTable_params.FieldByName('jyj').AsFloat;
    form_dm.ADOQuery_common2.Open;
    form_dm.ADOTable_params.close;
  end;
end;

procedure TForm_driverstat.BitBtn12Click(Sender: TObject);
begin
close;
end;

procedure TForm_driverstat.BitBtn11Click(Sender: TObject);
begin
form_driverstatrep1.QRLabel6.Caption:=combobox4.Text+'驾驶员补贴报表';
form_driverstatrep1.QRLabel17.Caption:='('+formatdatetime('yyyy-mm-dd',datetimepicker7.date)+' 至 '+formatdatetime('yyyy-mm-dd',datetimepicker8.date)+')';
form_driverstatrep1.QuickRep1.Preview;
end;

procedure TForm_driverstat.BitBtn3Click(Sender: TObject);
begin
form_driverstatrep2.QRLabel6.Caption:=combobox1.Text+' 出车信息';
form_driverstatrep2.QRLabel17.Caption:='('+formatdatetime('yyyy-mm-dd',datetimepicker1.date)+' 至 '+formatdatetime('yyyy-mm-dd',datetimepicker2.date)+')';
form_driverstatrep2.QuickRep1.Preview;
end;

procedure TForm_driverstat.DBGrid1TitleClick(Column: TColumn);
var
     i:integer;
     fieldname:string;
begin//排序
     i:=Column.Index;
     if bSort=0 then
     begin
         fieldname:=DBGrid1.Columns[i].FieldName+' ASC';
         bSort:=1;
     end
     else
     begin
         fieldname:=DBGrid1.Columns[i].FieldName+' DESC';
         bSort:=0;
     end;
     if form_dm.ADOQuery_carout.SQL.Text<>'' then
     begin
     form_dm.ADOQuery_carout.open;
     form_dm.ADOQuery_carout.Sort:=fieldname;
     end;
end;


procedure TForm_driverstat.DBGrid4TitleClick(Column: TColumn);
var
     i:integer;
     fieldname:string;
begin//排序
     i:=Column.Index;
     if bSort=0 then
     begin
         fieldname:=DBGrid4.Columns[i].FieldName+' ASC';
         bSort:=1;
     end
     else
     begin
         fieldname:=DBGrid4.Columns[i].FieldName+' DESC';
         bSort:=0;
     end;
      if form_dm.ADOQuery_common2.SQL.Text<>'' then
      begin
     form_dm.ADOQuery_common2.open;
     form_dm.ADOQuery_common2.Sort:=fieldname;
     end;
end;


end.