searchbyhistoryunit.pas

短小而实用的房地产销售管理软件

Unit SearchByHistoryUnit;

Interface

Uses
   Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
   Dialogs, AAFont, AACtrls, DB, ADODB, Grids, DBGridEh, ExtCtrls,
   LbSpeedButton, ToolWin, ComCtrls, RealOneProgressBar;

Type
   TSearchByHistoryForm = Class(TForm)
      AALabel1: TAALabel;
      Panel1: TPanel;
      Splitter3: TSplitter;
      Splitter2: TSplitter;
      Splitter4: TSplitter;
      DBGridEH1: TDBGridEh;
      DBGridEh2: TDBGridEh;
      DBGridEh3: TDBGridEh;
      DataSource1: TDataSource;
      ADOQuery1: TADOQuery;
      DataSource2: TDataSource;
      ADOQuery2: TADOQuery;
      DataSource3: TDataSource;
      ADOQuery3: TADOQuery;
      Splitter1: TSplitter;
      DataSource4: TDataSource;
      ADOQuery4: TADOQuery;
      Panel2: TPanel;
      DBGridEh4: TDBGridEh;
      Splitter5: TSplitter;
      Panel3: TPanel;
      LbSpeedButton1: TLbSpeedButton;
      DBGridEh5: TDBGridEh;
      LbSpeedButton5: TLbSpeedButton;
      LbSpeedButton4: TLbSpeedButton;
      RealOneProgressBar1: TRealOneProgressBar;
      ADOQuery5: TADOQuery;
      DataSource5: TDataSource;
      AALabel2: TAALabel;
      Procedure EditTypeFlags(sComID, SItemID, sHourceID, sRoomID, stype:
         String; bOverdue: Boolean);
      Procedure InitRealBar(RBar: TRealOneProgressBar; Max1, Min1, Pos1:
         integer);
      Procedure GetOverdueRecords(ADOQ5: TADOQuery; sTablename: String);
      Procedure FormClose(Sender: TObject; Var Action: TCloseAction);
      Procedure DBGridEh1CellClick(Column: TColumnEh);
      Procedure DBGridEh2CellClick(Column: TColumnEh);
      Procedure DBGridEh3CellClick(Column: TColumnEh);
      Procedure LbSpeedButton5Click(Sender: TObject);
      Procedure FormCreate(Sender: TObject);
      Procedure DBGridEh4CellClick(Column: TColumnEh);
      Procedure LbSpeedButton4Click(Sender: TObject);
      Procedure LbSpeedButton1Click(Sender: TObject);
   Private
    { Private declarations }
   Public
    { Public declarations }
   End;

Var
   SearchByHistoryForm: TSearchByHistoryForm;

Implementation
Uses DM;
{$R *.dfm}

Procedure TSearchByHistoryForm.FormClose(Sender: TObject;
   Var Action: TCloseAction);
Begin
   Action := cafree;
   SearchByHistoryForm := Nil;
End;

Procedure TSearchByHistoryForm.DBGridEh1CellClick(Column: TColumnEh);
Begin
   With ADOQuery2 Do Begin
      sql.Text :=
         'select * FROM 项目信息表 WHERE 公司编号=''' +
         ADOQuery1.fieldbyname('公司编号').Text + '''';
      open;

   End;
   DBGridEh2CellClick(DBGridEh2.Columns.Items[0]);
End;

Procedure TSearchByHistoryForm.DBGridEh2CellClick(Column: TColumnEh);
Begin

   With ADOQuery3 Do Begin
      sql.Text :=
         'SELECT * from 单体信息表 WHERE 公司编号=''' +
         ADOQuery1.fieldbyname('公司编号').Text + ''' and 项目编号=''' +
         ADOQuery2.fieldbyname('项目编号').Text + '''';
      open;
      DBGridEh3CellClick(DBGridEh3.Columns.Items[0]);
   End;
End;

Procedure TSearchByHistoryForm.DBGridEh3CellClick(Column: TColumnEh);
Begin

   With ADOQuery4 Do Begin
      sql.Text := 'select * FROM 单元信息表 WHERE 公司编号=''' +
         ADOQuery1.fieldbyname('公司编号').Text + ''' and 项目编号=''' +
         ADOQuery2.fieldbyname('项目编号').Text + ''' and 幢号=''' +
         ADOQuery3.fieldbyname('幢号').Text + '''';
      open;
   End;
   DBGridEh4CellClick(DBGridEh4.Columns.Items[0]);

End;

Procedure TSearchByHistoryForm.LbSpeedButton5Click(Sender: TObject);
Begin
   Close;
End;

Procedure TSearchByHistoryForm.FormCreate(Sender: TObject);
Begin
   ADOQuery1.Active := true;
   If ADOQuery1.recordcount > 0 Then
      DBGridEh1CellClick(DBGridEH1.Columns.Items[0]);
End;

Procedure TSearchByHistoryForm.DBGridEh4CellClick(Column: TColumnEh);
Begin
   LbSpeedButton4.click;

End;

Procedure TSearchByHistoryForm.LbSpeedButton4Click(Sender: TObject);
Begin
   InitRealBar(RealOneProgressBar1, 8, 0, 0); //总进度
   ADOQuery5.Close;
   ADOQuery5.sql.Text := 'DELETE * FROM 销售历史临时表';
   ADOQuery5.ExecSQL;                   //清空
   ADOQuery5.sql.Text := 'select * from  销售历史临时表 order by 过期日期 desc';
   ADOQuery5.open;
   With RealOneProgressBar1 Do Begin
      Position := Position + 1;
      GetOverdueRecords(ADOQuery5, '单元未售表');
      Position := Position + 1;
      GetOverdueRecords(ADOQuery5, '单元预售表');
      Position := Position + 1;
      GetOverdueRecords(ADOQuery5, '单元拆迁还建表');
      Position := Position + 1;
      GetOverdueRecords(ADOQuery5, '单元租赁表');
      Position := Position + 1;
      GetOverdueRecords(ADOQuery5, '单元抵押表');
      Position := Position + 1;
      GetOverdueRecords(ADOQuery5, '单元预定表');
      Position := Position + 1;
      GetOverdueRecords(ADOQuery5, '单元预留表');
      Position := Position + 1;

   End;
End;

Procedure TSearchByHistoryForm.InitRealBar(RBar: TRealOneProgressBar; Max1,
   Min1, Pos1: integer);
Begin
   With RBar Do Begin
      Max := Max1;
      Min := Min1;
      Position := Pos1;
   End;
End;

Procedure TSearchByHistoryForm.GetOverdueRecords(ADOQ5: TADOQuery;
   sTablename: String);
Var
   i                : integer;
   adot             : TADOQuery;
   swhere, sname    : String;

Begin
   swhere := '';
   If sTablename = '单元预售表' Then sname := '预(销)售';

   If sTablename = '单元拆迁还建表' Then sname := '拆迁还建';
   If sTablename = '单元租赁表' Then Begin
      sname := '租赁';
      swhere := ' and 最后租房=True ';
   End;
   If sTablename = '单元抵押表' Then sname := '抵押';
   If sTablename = '单元预定表' Then sname := '预定';
   If sTablename = '单元预留表' Then sname := '预留';
   If sTablename = '单元未售表' Then Begin
      sname := '未售';
      swhere := ' and 最后定价=true';
   End;
   adot := TADOQuery.create(self);
   With adot Do Begin
      connection := DM.DataModule2.ADOConnection1;
      sql.Text := 'SELECT 公司编号,项目编号,幢号,房号, 过期日期 FROM ' +
         sTablename + ' WHERE 是否过期=true AND 公司编号=''' +
         ADOQuery4.fieldbyname('公司编号').Text + ''' AND 项目编号=''' +
         ADOQuery4.fieldbyname('项目编号').Text + ''' and 幢号=''' +
         ADOQuery4.fieldbyname('幢号').Text + ''' and 房号=''' +
         ADOQuery4.fieldbyname('房号').Text + '''' + swhere;
      open;
      If recordcount > 0 Then Begin
         For i := 0 To recordcount - 1 Do Begin
            ADOQ5.Append;
            ADOQ5.fieldbyname('公司编号').Text := fieldbyname('公司编号').Text;
            ADOQ5.fieldbyname('项目编号').Text := fieldbyname('项目编号').Text;
            ADOQ5.fieldbyname('幢号').Text := fieldbyname('幢号').Text;
            ADOQ5.fieldbyname('房号').Text := fieldbyname('房号').Text;
            ADOQ5.fieldbyname('过期日期').Text := fieldbyname('过期日期').Text;
            ADOQ5.fieldbyname('销售类型').Text := sname;
            ADOQ5.post;
         End;
      End;
      Free;
   End;

End;

Procedure TSearchByHistoryForm.LbSpeedButton1Click(Sender: TObject);
Var
   ID1, ID2, ID3, id4, sOldType, stype: String;
   adoBase          : TADOQuery;
Begin
   If Not ADOQuery5.Active Then Exit;
   If ADOQuery5.recordcount = 0 Then Exit;

   stype := ADOQuery5.fieldbyname('销售类型').Text;
   sOldType := ADOQuery4.fieldbyname('销售类型').Text;
   ID1 := ADOQuery5.fieldbyname('公司编号').Text;
   ID2 := ADOQuery5.fieldbyname('项目编号').Text;
   ID3 := ADOQuery5.fieldbyname('幢号').Text;
   id4 := ADOQuery5.fieldbyname('房号').Text;
   EditTypeFlags(ID1, ID2, ID3, id4, sOldType, true);
   EditTypeFlags(ID1, ID2, ID3, id4, stype, false);
   adoBase := TADOQuery.create(self);
   With adoBase Do Begin
      connection := DM.DataModule2.ADOConnection1;
      sql.Text := 'update 单元信息表 set 销售类型=''' + stype + ''',可销售房屋状态=''' +
         stype + ''' WHERE 公司编号=''' +
         ID1 + ''' AND 项目编号=''' + ID2 + ''' and 幢号=''' + ID3 + ''' and 房号=''' + id4 + '''';
      ExecSQL;
      Free;
   End;
   ADOQuery4.tag := ADOQuery4.RecNo;
   DBGridEh3CellClick(DBGridEh3.Columns.Items[0]);
   Application.ProcessMessages;
   ADOQuery4.RecNo := ADOQuery4.tag;
   LbSpeedButton4.click;
End;

Procedure TSearchByHistoryForm.EditTypeFlags(sComID, SItemID, sHourceID,
   sRoomID, stype: String; bOverdue: Boolean);
Var
   adotemp          : TADOQuery;
   sTname, SBOOLEAN : String;
Begin

   If stype = '未售' Then sTname := '单元未售表';
   If stype = '预(销)售' Then sTname := '单元预售表';
   If stype = '拆迁还建' Then sTname := '单元拆迁还建表';
   If stype = '抵押' Then sTname := '单元抵押表';
   If stype = '预定' Then sTname := '单元预定表';
   If stype = '预留' Then sTname := '单元预留表';
   If stype = '租赁' Then sTname := '单元租赁表';
   If bOverdue Then SBOOLEAN := 'TRUE' Else SBOOLEAN := 'False';

   adotemp := TADOQuery.create(self);
   With adotemp Do Begin
      connection := DM.DataModule2.ADOConnection1;

      sql.Text := 'update ' + sTname + ' set 是否过期=' + SBOOLEAN + ',过期日期=#' +
         DateToStr(Now) + '# where 公司编号=''' + sComID +
         ''' AND 项目编号=''' + SItemID + ''' and 幢号=''' + sHourceID +
         ''' AND 房号=''' + sRoomID + '''';

      ExecSQL;
      Free;
   End;

End;

End.