ydsf.pas

房屋出租信息管理系统,房屋出租信息的录入、查询、删除以及用户管理等功能

unit YdSf;

interface

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

type
  TYdSfForm = class(TForm)
    Panel1: TPanel;
    Label1: TLabel;
    Label2: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label7: TLabel;
    Label8: TLabel;
    Label10: TLabel;
    Label12: TLabel;
    Label9: TLabel;
    Label3: TLabel;
    Label6: TLabel;
    Label11: TLabel;
    Ed_Fh: TEdit;
    Ed_Bh: TEdit;
    Ed_Xm: TEdit;
    Ed_Sybd: TEdit;
    Ed_Bybd: TEdit;
    Ed_Sys: TEdit;
    Ed_Dj: TEdit;
    Ed_Yfj: TEdit;
    Ed_Sfj: TEdit;
    DT_Jkrq: TDateTimePicker;
    Com_Status: TComboBox;
    Ed_Sycbrq: TEdit;
    Panel2: TPanel;
    bitbtn_insert: TBitBtn;
    bitbtn_modify: TBitBtn;
    bitbtn_post: TBitBtn;
    bitbtn_cancel: TBitBtn;
    Panel3: TPanel;
    DBGrid1: TDBGrid;
    DS_YdSf: TDataSource;
    ADQ_YdSf: TADOQuery;
    ADQ_Ydglshow: TADOQuery;
    ADOQry_update: TADOQuery;
    ADOQry_insert: TADOQuery;
    ADQ_Dj: TADOQuery;
    Ed_rq: TEdit;
    ADQ_Date: TADOQuery;
    ADQ_RecordCount: TADOQuery;
    BBtn_Print: TBitBtn;
    fieldname: TComboBox;
    BitBtn_find: TBitBtn;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormCreate(Sender: TObject);
    procedure Ed_FhChange(Sender: TObject); 
    procedure Ed_FhKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure Ed_FhKeyPress(Sender: TObject; var Key: Char);
    procedure bitbtn_insertClick(Sender: TObject);
    procedure bitbtn_modifyClick(Sender: TObject);
    procedure bitbtn_cancelClick(Sender: TObject);
    procedure bitbtn_postClick(Sender: TObject);
    procedure BitBtn_findClick(Sender: TObject);
    procedure BBtn_PrintClick(Sender: TObject);
  private
    { Private declarations }
  public
      select_flag:integer;
      procedure cleardata;
      procedure enabledAdd;
      procedure enabledfalse;
      procedure AddData;
      procedure Fhquery;
      procedure Fhquery1;
      procedure Fhadddata;
      procedure Fhdate;
    { Public declarations }
  end;

var
  YdSfForm: TYdSfForm;

implementation

uses DataModule, Unit_common_files, YdSfGjcx, Cbreport;

{$R *.dfm}
procedure TYdSfForm.Fhquery ;
var nYear,nMonth,nDay:word;
begin
  DecodeDate(Now(),nYear,nMonth,nDay);
  GetTableName(nYear,nMonth);
  GetsyTableName(nYear,nMonth);
  ADQ_Ydglshow.Close ;
  ADQ_Ydglshow.SQL.Clear ;
  ADQ_Ydglshow.SQL.Add('SELECT C.manid, D.manname, D.Zjm, C.fangno,B.Cbrq, B.lsb, B.rsb, B.db, B.mqb,');
  ADQ_Ydglshow.SQL.Add('A.Cbrq AS bycbrq, A.lsb AS bylsb,A.rsb AS byrsb, A.db AS bydb,A.mqb AS bymqb');
  ADQ_Ydglshow.SQL.Add('FROM dbo.Zf_Man D INNER JOIN dbo.Zf_Rz C ON D.manid = C.manid INNER JOIN');
  ADQ_Ydglshow.SQL.Add(''+ StrTableName+ ' A ON C.fangno = A.fangno INNER JOIN');
  ADQ_Ydglshow.SQL.Add(''+ StrTableName+ ' B ON A.fangno = B.fangno');
  ADQ_Ydglshow.SQL.Add('where (A.fangno like :fangno or D.zjm like :zjm) and b.cbrq<a.cbrq');
  ADQ_Ydglshow.Parameters.ParamByName('fangno').Value :=trim(Ed_Fh.Text)+'%';
  ADQ_Ydglshow.Parameters.ParamByName('zjm').Value    :=trim(Ed_Fh.Text)+'%';
  ADQ_Ydglshow.Open ;
  ADQ_Dj.Open ;
  Fhadddata;
end;
procedure TYdSfForm.Fhquery1 ;
begin
  ADQ_Date.Close ;
  ADQ_Date.SQL.Clear ;
  ADQ_Date.SQL.Add('select * From XmXf ');
  ADQ_Date.SQL.Add('where fangno like :fangno or zjm like :zjm');
  ADQ_Date.Parameters.ParamByName('fangno').Value :=trim(Ed_Fh.Text)+'%';
  ADQ_Date.Parameters.ParamByName('zjm').Value    :=trim(Ed_Fh.Text)+'%';
  ADQ_Date.Open ;
  if ADQ_Date.IsEmpty  then
      begin
       showmessage('本房间月底还未抄表！请先抄表再收费');
       Ed_Fh.SelectAll ;
       exit;
      end
   else
     begin
      ADQ_Date.Close ;
      ADQ_Date.SQL.Clear ;
      ADQ_Date.SQL.Add('select max(cbrq)as cbrq From XmXf ');
      ADQ_Date.SQL.Add('where fangno like :fangno or zjm like :zjm');
      ADQ_Date.Parameters.ParamByName('fangno').Value :=trim(Ed_Fh.Text)+'%';
      ADQ_Date.Parameters.ParamByName('zjm').Value    :=trim(Ed_Fh.Text)+'%';
      ADQ_Date.Open ;
      Ed_rq.Text:=ADQ_Date.FieldByName('cbrq').Text ;
      ADQ_Ydglshow.Close ;
      ADQ_Ydglshow.SQL.Clear ;
      ADQ_Ydglshow.SQL.Add('SELECT *from xmxf');
      ADQ_Ydglshow.SQL.Add('where (fangno like :fangno or zjm like :zjm) and cbrq =:cbrq');
      ADQ_Ydglshow.Parameters.ParamByName('cbrq').Value   :=trim(Ed_rq.Text );
      ADQ_Ydglshow.Parameters.ParamByName('fangno').Value :=trim(Ed_Fh.Text)+'%';
      ADQ_Ydglshow.Parameters.ParamByName('zjm').Value    :=trim(Ed_Fh.Text)+'%';
      ADQ_Ydglshow.Open ;
      ADQ_Dj.Open ;
      Fhadddata;
    end;  
end;
procedure TYdSfForm.Fhadddata ;
var Sys,Yfj:Double;
begin
  Ed_Bh.Text    :=ADQ_Ydglshow.fieldByName('manid').Text ;
  Ed_Xm.Text    :=ADQ_Ydglshow.FieldByName('manname').Text ;
  Ed_Sybd.Text  :=ADQ_Ydglshow.FieldByName('db').Text ;
  Ed_Sycbrq.Text:=ADQ_Ydglshow.FieldByName('cbrq').Text ;
  Ed_Bybd.Text  :=ADQ_Ydglshow.FieldByName('bydb').Text ;
  Ed_Dj.Text    :=ADQ_Dj.FieldByName('price').Text ;
  if (trim(Ed_Bybd.Text)='') and  (trim(Ed_Sybd.Text)='') then
    begin
       Ed_Bybd.Text:='0';
       Ed_Sybd.Text:='0';
    end ;
  Sys:=StrToFloat(trim(Ed_Bybd.Text))-StrToFloat(trim(Ed_Sybd.Text));
  Yfj:=sys*StrTofloat(trim(Ed_Dj.Text));
  Ed_Sys.Text   :=FloatToStr(Sys);
  Ed_Sfj.Text   :=FloatToStr(Yfj);
  Ed_Yfj.Text   :=FloatTostr(Yfj);
end;
procedure TYdSfForm.Fhdate ;
var nYear,nMonth,nDay:word;
begin
  DecodeDate(Now(),nYear,nMonth,nDay);
  GetTableName(nYear,nMonth);
  GetsyTableName(nYear,nMonth);
  ADQ_RecordCount.Close ;
  ADQ_RecordCount.SQL.Clear ;
  ADQ_Recordcount.SQL.Add('SELECT A.fangno, B.Zjm, A.Cbrq, A.lsb, A.rsb, A.db,A.mqb');
  ADQ_Recordcount.SQL.Add('from '+ StrTableName+ ' A INNER JOIN ');
  ADQ_RecordCount.SQL.Add('dbo.Zf_Rz C ON A.fangno = C.fangno INNER JOIN ');
  ADQ_Recordcount.SQL.Add('dbo.Zf_Man B ON C.manid = B.manid ');
  ADQ_Recordcount.SQL.Add('where A.fangno like :fangno or zjm like :zjm');
  ADQ_Recordcount.Parameters.ParamByName('fangno').Value :=trim(Ed_Fh.Text)+'%';
  ADQ_Recordcount.Parameters.ParamByName('zjm').Value    :=trim(Ed_Fh.Text)+'%';
  ADQ_RecordCount.Open ;
end;
procedure TYdSfForm.enabledAdd  ;
begin
  bitbtn_insert.Enabled :=false;
  bitbtn_post.Enabled   :=true;
  bitbtn_cancel.Enabled :=true;
  Ed_yfj.Enabled        :=true;
  Ed_fh.Enabled         :=true;
  Ed_sfj.Enabled        :=true;
  Com_Status.Enabled    :=true;
end;
procedure TYdSfForm.cleardata ;
begin
  Ed_fh.Text     :='' ;
  com_status.Text:='';
end;
procedure TYdSfForm.AddData ;
begin
  Ed_Fh.Text     := ADQ_YdSf.fieldbyname('fangno').Text ;
  Ed_Bh.Text     := ADQ_YdSf.fieldbyname('manid').Text ;
  DT_Jkrq.Date   := ADQ_YdSf.fieldbyname('Jkrq').Value  ;
  Ed_Sybd.Text   := ADQ_YdSf.fieldbyname('Sybd').Text ;
  Ed_Bybd.Text   := ADQ_YdSf.fieldbyname('bybd').Text ;
  Ed_Sys.Text    := ADQ_YdSf.fieldbyname('sys').Text  ;
  Ed_Dj.Text     := ADQ_YdSf.fieldbyname('Yddj').Text ;
  Ed_Yfj.Text    := ADQ_YdSf.fieldbyname('yfj').Text ;
  Ed_Sfj.Text    := ADQ_YdSf.fieldbyname('sfj').Text  ;
  Com_status.Text:= ADQ_YdSf.fieldbyname('status').Text ;
end;
procedure TYdSfForm.enabledfalse ;
begin
  Ed_yfj.Enabled       :=false;
  Ed_fh.Enabled        :=false;
  Ed_sfj.Enabled       :=false;
  Com_status.Enabled   :=false;
  bitbtn_cancel.Enabled:=false;
  bitbtn_insert.Enabled:=true;
end;

procedure TYdSfForm.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  Action  :=cafree;
  YdSfForm:=nil;
end;

procedure TYdSfForm.FormCreate(Sender: TObject);
begin
  DT_Jkrq.Date:=date();
  ADQ_YdSf.Open ;
end;

procedure TYdSfForm.Ed_FhChange(Sender: TObject);
begin
     Fhdate;
  if ADQ_RecordCount.IsEmpty   then
      begin
        showmessage('对不起！没有此用户');
        Ed_Fh.SelectAll ;
        exit;
      end;
  if ADQ_RecordCount.RecordCount >1 then
       Fhquery
    else
      begin
       AlterView;
       Fhquery1;
      end;
end;  

procedure TYdSfForm.Ed_FhKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
    if key=13 then
  begin
    Ed_fh.Text:=ADQ_Ydglshow.FieldByName('fangno').asstring;
    Ed_fh.SetFocus ;
  end;
end;

procedure TYdSfForm.Ed_FhKeyPress(Sender: TObject; var Key: Char);
begin
   if (key>='0')and(key<='9') then
  begin
    ADQ_Ydglshow.Locate('fangno',trim(Ed_fh.text+char(key)),[]);
  end;
end;

procedure TYdSfForm.bitbtn_insertClick(Sender: TObject);
begin
  select_flag:=1;
  enabledAdd;
  cleardata;
  Ed_Fh.SetFocus ;
  DT_Jkrq.Date:=date();
end;

procedure TYdSfForm.bitbtn_modifyClick(Sender: TObject);
begin
   select_flag:=2;
   AddData;
   enabledAdd;
end;

procedure TYdSfForm.bitbtn_cancelClick(Sender: TObject);
begin
  enabledfalse ;
   bitbtn_insert.Enabled:=true;
   bitbtn_post.Enabled  :=false;
   bitbtn_cancel.Enabled:=false;
end;

procedure TYdSfForm.bitbtn_postClick(Sender: TObject);
begin
   try
   if Ed_fh.Text=''  then
        begin
          Application.MessageBox('请填写房号','余科华提示',MB_OK+MB_ICONINFORMATION);
          Ed_Fh.SetFocus ;
          exit;
        end;
    if Ed_Sfj.Text=''  then
        begin
          Application.MessageBox('请填写实收金额','余科华提示',MB_OK+MB_ICONINFORMATION);
          Ed_Sfj.SetFocus ;
          exit;
        end;
   if Com_status.Text=''  then
        begin
          Application.MessageBox('请填写状态','余科华提示',MB_OK+MB_ICONINFORMATION);
          Com_status.SetFocus ;
          exit;
        end;  
  if DB_module.ADOCon.InTransaction then DB_module.ADOCon.RollbackTrans ;
     DB_module.ADOCon.BeginTrans;
  if Select_flag=1 then
  begin
     if ADOQry_insert.Active  then
        ADOQry_insert.Close;
        ADOQry_insert.Parameters.ParamByName('fangNO').Value :=Ed_Fh.Text ;
        ADOQry_insert.Parameters.ParamByName('manid').Value:=Ed_Bh.Text;
        ADOQry_insert.Parameters.ParamByName('jkrq').Value:=DT_Jkrq.Date ;
        ADOQry_insert.Parameters.ParamByName('sybd').Value:=Ed_Sybd.Text ;
        ADOQry_insert.Parameters.ParamByName('bybd').Value:=Ed_Bybd.Text ;
        ADOQry_insert.Parameters.ParamByName('sys').Value:=Ed_Sys.Text ;
        ADOQry_insert.Parameters.ParamByName('Yddj').Value:=Ed_Dj.Text;
        ADOQry_insert.Parameters.ParamByName('Yfj').Value:=Ed_Yfj.Text;
        ADOQry_insert.Parameters.ParamByName('sfj').Value:=Ed_sfj.Text  ;
        if  trim(Com_status.text)='已交款' then
            ADOQry_insert.Parameters.ParamByName('status').Value:='1'
         else
            ADOQry_insert.Parameters.ParamByName('status').Value:='0';
        ADOQry_insert.ExecSQL;

        DB_module.ADOCon.CommitTrans;
        showmessage('保存成功！');
        if ADQ_YdSf.Active then
           ADQ_YdSf.Close;
           ADQ_YdSf.Open ;
  end
 else if select_flag=2 then
  begin
  if ADOQry_update.Active then
     ADOQry_update.Close;
        ADOQry_update.Parameters.ParamByName('fangno').Value :=Ed_Fh.Text ;
        ADOQry_update.Parameters.ParamByName('manid').Value:=Ed_Bh.Text;
        ADOQry_update.Parameters.ParamByName('jkrq').Value:=DT_Jkrq.Date ;
        ADOQry_update.Parameters.ParamByName('sybd').Value:=Ed_Sybd.Text ;
        ADOQry_update.Parameters.ParamByName('bybd').Value:=Ed_Bybd.Text ;
        ADOQry_update.Parameters.ParamByName('sys').Value:=Ed_Sys.Text ;
        ADOQry_update.Parameters.ParamByName('Yddj').Value:=Ed_Dj.Text;
        ADOQry_update.Parameters.ParamByName('Yfj').Value:=Ed_Yfj.Text  ;
        ADOQry_update.Parameters.ParamByName('sfj').Value:=Ed_sfj.Text   ;
        if trim(Com_status.text)='已交款' then
           ADOQry_update.Parameters.ParamByName('status').Value:='1'
        else
           ADOQry_update.Parameters.ParamByName('status').Value:='0';
        ADOQry_update.Parameters.ParamByName('oldID').value:=ADQ_YdSf.FieldByName('ID').Value ;
        ADOQry_update.ExecSQL;

        DB_module.ADOCon.CommitTrans;
        showmessage('修改成功！');
    if  ADQ_YdSf.Active then
        ADQ_YdSf.Close;
        ADQ_YdSf.Open ;
  end
 except
  begin
   MessageBox(0,'操作失败！','',MB_OK+MB_ICONERROR);
   DB_module.ADOCon.RollbackTrans;
   exit;
  end;
end;
  enabledfalse ;
end;

procedure TYdSfForm.BitBtn_findClick(Sender: TObject);
var
  lop,f_count:integer;
begin
   YdSfGjcxForm:= TYdSfGjcxForm.create(nil);
  try
    if not ADQ_YdSf.Active then ADQ_YdSf.Open;
       f_count := DBGrid1.columns.Count;
       for lop := 0 to f_count -1 do
      begin
        fieldname.Items.Add(DBGrid1.columns[lop].FieldName);
        YdSfGjcxForm.Com_jl.Items.Add(DBGrid1.columns[lop].Title.Caption);
      end;
      YdSfGjcxForm.fieldname.items.Assign(fieldname.items);
      if YdSfGjcxForm.ShowModal = mryes then
    begin
      ADQ_YdSf.Close;
      ADQ_YdSf.SQL.Clear;
      ADQ_YdSf.SQL.Add('SELECT A.ID,A.fangno, A.manid , B.manname,A.Jkrq, A.Sybd, A.Bybd, A.Sys,A.yddj,');
      ADQ_YdSf.SQL.Add('A.Yfj,A.sfj,case A.status when ''1'' then ''已交款'' else ''未交款'' end as status ');
      ADQ_YdSf.SQL.Add(' FROM dbo.Zf_YdSf A INNER JOIN  dbo.Zf_Man B ON A.manid = B.manid ');
      ADQ_YdSf.SQL.Add(YdSfGjcxForm.returnstr);
      ADQ_YdSf.Open;
    end;
  finally
    YdSfGjcxForm.Free;
  end;
end;

procedure TYdSfForm.BBtn_PrintClick(Sender: TObject);
begin
   if ADQ_YdSf.IsEmpty then
    begin
       messagebox(0,'请查询后再打印！','余科华提示',MB_OK+MB_ICONWARNING);
       Exit;
    end
 else
    begin
         BbForm_report:=TBbForm_report.Create(nil);
       try
         BbForm_report.QRL_YdRmbdx.Caption:=tochinesecurrency(ADQ_YdSf.fieldbyname('sfj').AsCurrency);
         BbForm_report.Qp_YdSfSj.Preview ;
       finally
         BbForm_report.Free ;
       end;  
    end;
end;

end.