carstat.pas

管理车队用车记录

unit carstat;

interface

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

type
  TForm_carstat = class(TForm)
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    TabSheet2: TTabSheet;
    TabSheet3: TTabSheet;
    Panel2: TPanel;
    BitBtn1: TBitBtn;
    BitBtn3: TBitBtn;
    BitBtn2: TBitBtn;
    DBGrid1: TDBGrid;
    Panel3: TPanel;
    Label3: TLabel;
    Edit1: TEdit;
    Panel1: TPanel;
    Label2: TLabel;
    Label6: TLabel;
    Label7: TLabel;
    ComboBox2: TComboBox;
    DateTimePicker3: TDateTimePicker;
    DateTimePicker4: TDateTimePicker;
    Label1: TLabel;
    Edit2: TEdit;
    TabSheet4: TTabSheet;
    Panel4: TPanel;
    Label13: TLabel;
    Label14: TLabel;
    Label15: TLabel;
    ComboBox1: TComboBox;
    DateTimePicker1: TDateTimePicker;
    DateTimePicker2: TDateTimePicker;
    DBGrid2: TDBGrid;
    Panel5: TPanel;
    BitBtn4: TBitBtn;
    BitBtn5: TBitBtn;
    BitBtn6: TBitBtn;
    Panel6: TPanel;
    Label17: TLabel;
    Label18: TLabel;
    Edit11: TEdit;
    Edit12: TEdit;
    Panel7: TPanel;
    Label25: TLabel;
    Label26: TLabel;
    Label27: TLabel;
    ComboBox3: TComboBox;
    DateTimePicker5: TDateTimePicker;
    DateTimePicker6: TDateTimePicker;
    DBGrid3: TDBGrid;
    Panel8: TPanel;
    BitBtn7: TBitBtn;
    BitBtn8: TBitBtn;
    BitBtn9: TBitBtn;
    Panel9: TPanel;
    Label28: TLabel;
    Edit19: TEdit;
    Panel10: TPanel;
    Label37: TLabel;
    Label38: TLabel;
    Label39: TLabel;
    ComboBox4: TComboBox;
    DateTimePicker7: TDateTimePicker;
    DateTimePicker8: TDateTimePicker;
    DBGrid4: TDBGrid;
    Panel11: TPanel;
    BitBtn10: TBitBtn;
    BitBtn11: TBitBtn;
    BitBtn12: TBitBtn;
    Panel12: TPanel;
    Label40: TLabel;
    Label41: TLabel;
    Label42: TLabel;
    Label43: TLabel;
    Label44: TLabel;
    Label45: TLabel;
    Label46: TLabel;
    Label48: TLabel;
    DBEdit1: TDBEdit;
    DBEdit2: TDBEdit;
    DBEdit3: TDBEdit;
    DBEdit4: TDBEdit;
    DBEdit5: TDBEdit;
    DBEdit6: TDBEdit;
    DBEdit7: TDBEdit;
    DBEdit8: TDBEdit;
    Label4: TLabel;
    DBEdit9: TDBEdit;
    Label5: TLabel;
    DBEdit10: TDBEdit;
    Label8: TLabel;
    DBEdit11: TDBEdit;
    Label9: TLabel;
    DBEdit12: TDBEdit;
    procedure FormShow(Sender: TObject);
    procedure BitBtn10Click(Sender: TObject);
    procedure BitBtn12Click(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure BitBtn3Click(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    procedure BitBtn6Click(Sender: TObject);
    procedure BitBtn9Click(Sender: TObject);
    procedure BitBtn11Click(Sender: TObject);
    procedure BitBtn4Click(Sender: TObject);
    procedure BitBtn5Click(Sender: TObject);
    procedure BitBtn7Click(Sender: TObject);
    procedure BitBtn8Click(Sender: TObject);
    procedure DBGrid1TitleClick(Column: TColumn);
    procedure DBGrid2TitleClick(Column: TColumn);
    procedure DBGrid3TitleClick(Column: TColumn);
    procedure DBGrid4TitleClick(Column: TColumn);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form_carstat: TForm_carstat;

implementation
 uses dm, carstatrep1, carstatrep4, carstatrep2, carstatrep3;
{$R *.dfm}

procedure TForm_carstat.FormShow(Sender: TObject);
begin
with form_dm.ADOtable_car do
        begin
        open;
        Sort:='id';
        first;
        combobox1.Items.Clear;
        combobox2.Items.Clear;
        combobox3.Items.Clear;
        combobox4.Items.Clear;
        combobox1.Items.Add('全部');
        combobox2.Items.Add('全部');
        combobox3.Items.Add('全部');
        combobox4.Items.Add('全部');
        repeat
            combobox1.Items.Add(fieldbyname('id').AsString);
             combobox2.Items.Add(fieldbyname('id').AsString);
              combobox3.Items.Add(fieldbyname('id').AsString);
               combobox4.Items.Add(fieldbyname('id').AsString);
            Next;
        until eof;
        end;
combobox1.Text:='';
combobox2.Text:='';
combobox3.Text:='';
combobox4.Text:='';
datetimepicker1.Date:=date;
datetimepicker2.Date:=date;
datetimepicker3.Date:=date;
datetimepicker4.Date:=date;
datetimepicker5.Date:=date;
datetimepicker6.Date:=date;
datetimepicker7.Date:=date;
datetimepicker8.Date:=date;
edit1.Text:='0';
edit2.text:='0';
end;

procedure TForm_carstat.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 shouru';
    form_dm.ADOCommand1.Execute;
    form_dm.ADOCommand1.CommandText:='delete from shouru1';
    form_dm.ADOCommand1.Execute;
    form_dm.ADOCommand1.CommandText:='insert into shouru(cph,bzyh,gls,msr,glbt,sjf,jjbt,yxbt,qtjf,glf) '+'select A.id,A.oilconsume,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 id,oilconsume from car) A  LEFT JOIN '+' (select car_id ,round(sum(km),1) 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 car_id) B ON A.id = B.car_id';
    form_dm.ADOCommand1.Execute;

    form_dm.ADOCommand1.CommandText:='insert into shouru1(cph,bzyh,gls,msr,glbt,sjf,jjbt,yxbt,qtjf,glf,hyl,zyf) '+'select A.cph,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 shouru )A LEFT JOIN (select car_id ,sum(amount) as 耗油量 ,sum(total) as 总油费 from oil where date between '''+date1+''' and '''+date2+''' group by car_id ) B  ON A.cph = B.car_id ';
    form_dm.ADOCommand1.Execute;
    form_dm.ADOCommand1.CommandText:='delete from shouru';
    form_dm.ADOCommand1.Execute;
    form_dm.ADOCommand1.CommandText:='insert into shouru(cph,bzyh,gls,msr,glbt,sjf,jjbt,yxbt,qtjf,glf,hyl,zyf,wxf) '+'select A.cph,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 shouru1 )A LEFT JOIN (select car_id ,sum(total) as 维修费 from repair where date between '''+date1+''' and '''+date2+''' group by car_id ) B  ON A.cph = B.car_id ';
    form_dm.ADOCommand1.Execute;
    form_dm.ADOCommand1.CommandText:='delete from shouru1';
    form_dm.ADOCommand1.Execute;
  if combobox4.Text='全部' then
  begin
    form_dm.ADOQuery_common.SQL.Text:='select *,round(iif(gls=0,0,iif(hyl/gls*100>bzyh,-1,1)*(hyl/gls*100-bzyh)*gls*:P),1) as jyj,round(msr-zyf-glf-sjf-yxbt-jjbt-glbt-qtjf-wxf-jyj) as csr from shouru';
    form_dm.ADOTable_params.Open;
    form_dm.ADOQuery_common.Parameters.ParamByName('P').Value:=form_dm.ADOTable_params.FieldByName('jyj').AsFloat;
    form_dm.ADOQuery_common.Open;
    form_dm.ADOTable_params.close;
  end
  else
  begin
    form_dm.ADOQuery_common.SQL.Text:='select *,round(iif(gls=0,0,iif(hyl/gls*100>bzyh,-1,1)*(hyl/gls*100-bzyh)*gls*:P),1) as jyj,round(msr-zyf-glf-sjf-yxbt-jjbt-glbt-qtjf-wxf-jyj) as csr from shouru where cph='''+car_id+'''';
    form_dm.ADOTable_params.Open;
    form_dm.ADOQuery_common.Parameters.ParamByName('P').Value:=form_dm.ADOTable_params.FieldByName('jyj').AsFloat;
    form_dm.ADOQuery_common.Open;
    form_dm.ADOTable_params.close;
  end;
end;

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

procedure TForm_carstat.BitBtn1Click(Sender: TObject);
var i:integer;
    allfee:double;
begin
if datetimepicker3.Date>=datetimepicker4.Date then
begin
 showmessage('请选择你一个合适的时间段！');
 abort;
end;
if combobox2.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 car_out.car_id='''+combobox2.Text+''' and  car_out.date between '''+ formatdatetime('yyyy-mm-dd',datetimepicker3.date)+''' and '''+formatdatetime('yyyy-mm-dd',datetimepicker4.date)+'''  order by car_out.car_id;'
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',datetimepicker3.date)+''' and '''+formatdatetime('yyyy-mm-dd',datetimepicker4.date)+'''  order by car_out.car_id;';
form_dm.ADOQuery_carout.Open;