pas_income.pas

比较详细的功能

unit Pas_income;

interface

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

type
  TForm_Income = class(TForm)
    Label1: TLabel;
    Label6: TLabel;
    ToolBar1: TToolBar;
    depart: TSpeedButton;
    DBGrid1: TDBGrid;
    Label2: TLabel;
    Label3: TLabel;
    Edit_sy: TEdit;
    dtp_starttime: TDateTimePicker;
    dtp_endtime: TDateTimePicker;
    DBGrid2: TDBGrid;
    Label4: TLabel;
    Edit_sr: TEdit;
    Label5: TLabel;
    Edit_zc: TEdit;
    seller: TSpeedButton;
    selecthouse: TSpeedButton;
    cb_houseno: TComboBox;
    cb_sellerno: TComboBox;
    cb_departno: TComboBox;
    Label7: TLabel;
    Label8: TLabel;
    procedure departClick(Sender: TObject);
    procedure sellerClick(Sender: TObject);
    procedure selecthouseClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure cb_housenoDropDown(Sender: TObject);
    procedure cb_sellernoDropDown(Sender: TObject);
    procedure cb_departnoDropDown(Sender: TObject);
    procedure FormKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form_Income: TForm_Income;

implementation
       uses pas_dm2, Pas_DM;
{$R *.dfm}
//部门
procedure TForm_Income.departClick(Sender: TObject);
var
  S1:string;
  S2:string;
  i1:integer;
  i2:integer;
begin
     i1:=0;
     i2:=0;


      s1:= ' like ''%'+trim(cb_departno.Text)+'%'' '+
         ' and datediff(day,P_time,'''+datetostr(dtp_starttime.datetime)+''')<=0'+
         ' and datediff(day,P_time,'''+datetostr(dtp_endtime.DateTime)+''')>=0 ';
      s2:= '  like ''%'+trim(cb_departno.Text)+'%'' '+
         ' and datediff(day,hp_occurtime,'''+datetostr(dtp_starttime.datetime)+''')<=0'+
         ' and datediff(day,hp_occurtime,'''+datetostr(dtp_endtime.DateTime)+''')>=0 ';
   
//////////////////////  查询收入
 try
     with  dm2.Query_income do
     begin
        close;
        sql.Clear;
        sql.Add(' select   ho_no 房子编号,'+
          '''租金''  项目, '+
          '  p_paymoney 金额, '+
          '  ho_contractno 合同号,'+
          '  p_now 操作时间, '+
          '  p_remarks 备注 '+
          '  from house_histry,pay where ho_contractNO=p_cno and ho_landlord is null and  ho_depart'+s1+

          ' union all select hp_no 房子编号,'+
          ' hp_thing  项目,'+
          ' hp_cost  金额,'+
          '  ho_contractno 合同号,'+
          ' hp_occurtime  操作时间,'+
          ' hp_remarks 备注'+
          ' from housepay,house_histry where hp_cord=1 and ho_contractNO=hp_cno and  ho_depart '+s2);
        open;
     end;
     except
       showmessage('查询收入出错！');
     end;
//////////////////////  查询支出
     try
     with  dm2.Query_income2 do
     begin
        close;
        sql.Clear;
        sql.Add(' select hp_no 房子编号,'+
          ' hp_thing  项目,'+
          ' hp_cost  金额,'+
          '  ho_contractno 合同号,'+
          ' hp_occurtime  操作时间,'+
          ' hp_remarks 备注'+
          ' from housepay,house_histry where hp_cord=0 and ho_contractNO=hp_cno and ho_client is null and  ho_depart '+s2+
          
          ' union all select   ho_no 房子编号,'+
          '''租金''  项目, '+
          '  p_paymoney 金额, '+
          '  ho_contractno 合同号,'+
          '  p_time 发生时间, '+
          '  p_remarks 备注 '+
          '  from house_histry,pay where ho_validorno=1 and ho_contractNO=p_cno and ho_client is null and  ho_depart'+s1);
        open;
     end;
     except
       showmessage('查询支出出错！');
     end;
//////////////////   计算
     dm2.query_income.First;
     while not dm2.Query_income.Eof do
     begin
      i1:=i1+dm2.Query_income.Fields.Fields[2].asinteger;
      dm2.Query_income.Next;
      end;


    dm2.query_income2.First;
     while not dm2.Query_income2.Eof do
     begin
      i2:=i2+dm2.Query_income2.Fields.Fields[2].asinteger;
      dm2.Query_income2.Next;
      end;
     edit_sr.Text:=inttostr(i1);
     edit_zc.Text:=inttostr(i2);
     edit_sy.Text:=inttostr(i1-i2)
end;


/////////业务员查询
procedure TForm_Income.sellerClick(Sender: TObject);
var
  S1:string;
  S2:string;
  i1:integer;
  i2:integer;
begin
     i1:=0;
     i2:=0;


     s1:= ' like ''%'+trim(cb_sellerno.Text)+'%'' '+
         ' and datediff(day,P_time,'''+datetostr(dtp_starttime.datetime)+''')<=0'+
         ' and datediff(day,P_time,'''+datetostr(dtp_endtime.DateTime)+''')>=0 ';
      s2:= '  like ''%'+trim(cb_sellerno.Text)+'%'' '+
         ' and datediff(day,hp_occurtime,'''+datetostr(dtp_starttime.datetime)+''')<=0'+
         ' and datediff(day,hp_occurtime,'''+datetostr(dtp_endtime.DateTime)+''')>=0';
   
//////////////////////  查询收入
 try
     with  dm2.Query_income do
     begin
        close;
        sql.Clear;
        sql.Add(' select   ho_no 房子编号,'+
          '''租金''  项目, '+
          '  p_paymoney 金额, '+
          '  ho_contractno 合同号,'+
          '  p_now 操作时间, '+
          '  ho_seller 业务员,'+
          '  p_remarks 备注 '+
          '  from house_histry,pay where ho_contractNO=p_cno '+
          ' and ho_landlord is null and  ho_seller'+s1+

          ' union all select hp_no 房子编号,'+
          ' hp_thing  项目,'+
          ' hp_cost  金额,'+
          ' ho_contractno 合同号,'+
          ' hp_occurtime  操作时间,'+
          ' ho_seller 业务员,'+
          ' hp_remarks 备注'+
          ' from house_histry,housepay where ho_contractno=hp_cno and '+
          ' hp_cord=1 and ho_seller '+s2);
      
        open;
     end;
     except
       showmessage('查询收入出错！');
     end;
//////////////////////  查询支出
     try
     with  dm2.Query_income2 do
     begin
        close;
        sql.Clear;
        sql.Add(' select hp_no 房子编号,'+
          ' hp_thing  项目,'+
          ' hp_cost  金额,'+
          ' hp_cno 合同号,'+
          ' ho_seller 业务员,'+
          ' hp_occurtime  操作时间,'+
          ' hp_remarks 备注'+
          ' from house_histry,housepay where hp_cord=0 '+
          '  and ho_contractno=hp_cno and ho_client is null and ho_seller '+s2);
        open;
     end;
     except
       showmessage('查询支出出错！');
     end;
//////////////////   计算
     dm2.query_income.First;
     while not dm2.Query_income.Eof do
     begin
      i1:=i1+dm2.Query_income.Fields.Fields[2].asinteger;
      dm2.Query_income.Next;
      end;


    dm2.query_income2.First;
     while not dm2.Query_income2.Eof do
     begin
      i2:=i2+dm2.Query_income2.Fields.Fields[2].asinteger;
      dm2.Query_income2.Next;
      end;
     edit_sr.Text:=inttostr(i1);
     edit_zc.Text:=inttostr(i2);
     edit_sy.Text:=inttostr(i1-i2)
end;

procedure TForm_Income.selecthouseClick(Sender: TObject);
var
  S1:string;
  S2:string;
  i1:integer;
  i2:integer;
begin
     i1:=0;
     i2:=0;
   
     s1:= ' like ''%'+trim(cb_houseno.Text)+'%'' '+
         ' and datediff(day,P_time,'''+datetostr(dtp_starttime.datetime)+''')<=0'+
         ' and datediff(day,P_time,'''+datetostr(dtp_endtime.DateTime)+''')>=0' ;
     s2:= '  like ''%'+trim(cb_houseno.Text)+'%'' '+
         ' and datediff(day,hp_occurtime,'''+datetostr(dtp_starttime.Datetime)+''')<=0'+
         ' and datediff(day,hp_occurtime,'''+datetostr(dtp_endtime.DateTime)+''')>=0 order by p_time';
 
//////////////////////  查询收入
 try
     with  dm2.Query_income do
     begin
        close;
        sql.Clear;
        sql.Add(' select   ho_no 房子编号,'+
          '''租金''  项目, '+
          '  p_paymoney 金额, '+
          '  ho_contractno 合同号,'+
          '  p_time 发生时间, '+
          '  p_remarks 备注 '+
          '  from house_histry,pay where ho_validorno=1 and ho_contractNO=p_cno and ho_landlord is null and  ho_no'+s1+

          ' union all select hp_no 房子编号,'+
          ' hp_thing  项目,'+
          ' hp_cost  金额,'+
          ' hp_cno 合同号,'+
          ' hp_occurtime  发生时间,'+
          ' hp_remarks 备注'+
          ' from housepay,house_histry where hp_cno=ho_contractno and'+
                  ' ho_validorno=1 and hp_cord=1 and hp_no '+s2);
         
           open;
     end;
     except
       showmessage('查询收入出错！');
     end;
//////////////////////  查询支出
     try
     with  dm2.Query_income2 do
     begin
        close;
        sql.Clear;
        sql.Add(' select   ho_no 房子编号,'+
          '''租金''  项目, '+
          '  p_paymoney 金额, '+
          '  ho_contractno 合同号,'+
          '  p_time 发生时间, '+
          '  p_remarks 备注 '+
          '  from house_histry,pay where ho_validorno=1 and ho_contractNO=p_cno and ho_client is null and  ho_no'+s1+

          ' union all select hp_no 房子编号,'+
          ' hp_thing  项目,'+
          ' hp_cost  金额,'+
          ' hp_cno 合同号,'+
          ' hp_occurtime  发生时间,'+
          ' hp_remarks 备注'+
          ' from housepay,house_histry where hp_cno=ho_contractno and ho_validorno=1 and  hp_cord=0 and hp_no '+s2);
           open;
     end;
     except
       showmessage('查询支出出错！');
     end;
//////////////////   计算
     dm2.query_income.First;
     while not dm2.Query_income.Eof do
     begin
      i1:=i1+dm2.Query_income.Fields.Fields[2].asinteger;
      dm2.Query_income.Next;
      end;


     dm2.query_income2.First;
     while not dm2.Query_income2.Eof do
     begin
      i2:=i2+dm2.Query_income2.Fields.Fields[2].asinteger;
      dm2.Query_income2.Next;
      end;
     edit_sr.Text:=inttostr(i1);
     edit_zc.Text:=inttostr(i2);
     edit_sy.Text:=inttostr(i1-i2)
end;


procedure TForm_Income.FormShow(Sender: TObject);
begin
   dtp_starttime.DateTime:=now();
   dtp_endtime.DateTime:=now();
end;

procedure TForm_Income.cb_housenoDropDown(Sender: TObject);
var
   s:string;
begin
  try
   with dm.aq1 do
   begin
     close;
     sql.Clear;
     s:='select h_no from house ';
     sql.Add(s);
     open;
     cb_houseno.Items.Clear;
     while not dm.aq1.Eof do
     begin
      cb_houseno.Items.Add(fields.Fields[0].asstring);
      next;
    end;
   end;
  except
  end;
end;

procedure TForm_Income.cb_sellernoDropDown(Sender: TObject);
var
   s:string;
begin
  try
   with dm.aq1 do
   begin
     close;
     sql.Clear;
     s:='select s_no from seller ';
     sql.Add(s);
     open;
     cb_sellerno.Items.Clear;
     while not dm.aq1.Eof do
     begin
      cb_sellerno.Items.Add(fields.Fields[0].asstring);
      next;
    end;
   end;
  except
  end;  
end;

procedure TForm_Income.cb_departnoDropDown(Sender: TObject);
var
   s:string;
begin
  try
   with dm.aq1 do
   begin
     close;
     sql.Clear;
     s:='select dp_no from depart ';
     sql.Add(s);
     open;
     cb_departno.Items.Clear;
     while not dm.aq1.Eof do
     begin
      cb_departno.Items.Add(fields.Fields[0].asstring);
      next;
    end;
   end;
  except
  end;
end;

procedure TForm_Income.FormKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
  if   Key   =   VK_F1   then
      begin
          selecthouse.Click;
          Key   :=   0;
          exit;
      end;
end;

end.