?? 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('在此段時間內(nèi)該駕駛員無出車記錄！');
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.[公里數(shù)] is null,0,B.[公里數(shù)]),iif(B.[毛收入] is null,0,B.[毛收入]),iif(B.[公里補(bǔ)貼] is null,0,B.[公里補(bǔ)貼]),'+'iif(B.[手機(jī)費(fèi)] is null,0,B.[手機(jī)費(fèi)]),iif(B.[節(jié)假補(bǔ)貼] is null,0,B.[節(jié)假補(bǔ)貼]),'+'iif(B.[夜行補(bǔ)貼] is null,0,B.[夜行補(bǔ)貼]),iif(B.[其他獎罰] is null,0,B.[其他獎罰]),iif(B.[過路停車費(fèi)] is null,0,B.[過路停車費(fèi)]) '+'from (select jsy,bzyh,cgls from butie1) A LEFT JOIN (select driver_name ,sum(km) as 公里數(shù),round(sum(spend),1) as 毛收入,round(sum(km_bounty),1) as 公里補(bǔ)貼,'+'round(sum(sjb),1) as 手機(jī)費(fèi),round(sum(jjb),1) as 節(jié)假補(bǔ)貼 ,round(sum(duty_bounty),1) as 夜行補(bǔ)貼,round(sum(other),1) as 其他獎罰 ,round(sum(toll),1) as 過路停車費(fèi) '+' 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.[總油費(fèi)] is null,0,B.[總油費(fèi)])'+'from (select * from butie )A LEFT JOIN (select driver_name ,sum(amount) as 耗油量 ,sum(total) as 總油費(fèi) 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.[維修費(fèi)] is null,0,B.[維修費(fèi)])'+'from (select * from butie1 )A LEFT JOIN (select driver_name ,sum(total) as 維修費(fèi) 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+'駕駛員補(bǔ)貼報表';
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.
?? 快捷鍵說明
復(fù)制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -