Unit SearchSellOnlyUnit;

Interface

Uses
   Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
   Dialogs, DB, ADODB, Grids, DBGridEh, ExtCtrls, AAFont, AACtrls, StdCtrls,
   LbSpeedButton, LbButton, ACReportClass;

Type
   TSearchSellOnlyForm = 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;
      Panel2: TPanel;
      Panel4: TPanel;
      LbSpeedButton2: TLbSpeedButton;
      LbSpeedButton1: TLbSpeedButton;
      AALabel21: TAALabel;
      combobox1: TComboBox;
      CheckBox1: TCheckBox;
      Panel3: TPanel;
      Panel5: TPanel;
      AALabel2: TAALabel;
      ComboBox2: TComboBox;
      Panel7: TPanel;
      CheckBox2: TCheckBox;
      LbButton1: TLbButton;
      LbButton2: TLbButton;
      LbButton3: TLbButton;
      SaveDialog1: TSaveDialog;
      DBGridEh4: TDBGridEh;
      ADOQuery4: TADOQuery;
      DataSource4: TDataSource;
      AALabel3: TAALabel;
      ComboBox3: TComboBox;
      RadioButton1: TRadioButton;
      RadioButton2: TRadioButton;
      AcReport1: TAcReport;
      Procedure FormClose(Sender: TObject; Var Action: TCloseAction);
      Procedure DBGridEh1CellClick(Column: TColumnEh);
      Procedure DBGridEh2CellClick(Column: TColumnEh);
      Procedure LbSpeedButton2Click(Sender: TObject);
      Procedure LbButton1Click(Sender: TObject);
      Procedure FormCreate(Sender: TObject);
      Procedure ComboBox1Change(Sender: TObject);
      Procedure ADOQuery1AfterScroll(DataSet: TDataSet);
      Procedure ADOQuery2AfterScroll(DataSet: TDataSet);
      Procedure RadioButton1Click(Sender: TObject);
      Procedure LbSpeedButton1Click(Sender: TObject);
      Procedure LbButton2Click(Sender: TObject);
      Procedure AALabel2Click(Sender: TObject);
      Procedure AALabel3Click(Sender: TObject);
      Procedure DBGridEh4DblClick(Sender: TObject);
   Private
    { Private declarations }
   Public
    { Public declarations }
   End;

Var
   SearchSellOnlyForm: TSearchSellOnlyForm;
   sCastNo          : TStringlist;
   sTname           : String;
Implementation
Uses DM, ViewUnitUnit, PUBFunction;
{$R *.dfm}

Procedure TSearchSellOnlyForm.FormClose(Sender: TObject;
   Var Action: TCloseAction);
Begin
   sCastNo.Free;

   Action := cafree;
   SearchSellOnlyForm := Nil;
End;

Procedure TSearchSellOnlyForm.DBGridEh1CellClick(Column: TColumnEh);
Begin
   With ADOQuery2 Do Begin
      sql.Text :=
         'select * FROM 項(xiàng)目信息表 WHERE 公司編號=''' +
         ADOQuery1.fieldbyname('公司編號').Text + '''';
      open;

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

Procedure TSearchSellOnlyForm.DBGridEh2CellClick(Column: TColumnEh);
Begin
   With ADOQuery3 Do Begin
      sql.Text :=
         'SELECT * from 單體信息表 WHERE 公司編號=''' +
         ADOQuery1.fieldbyname('公司編號').Text + ''' and 項(xiàng)目編號=''' +
         ADOQuery2.fieldbyname('項(xiàng)目編號').Text + '''';
      open;

   End;
   CheckBox1.Caption := '查詢整個(gè)[' + ADOQuery2.fieldbyname('項(xiàng)目名稱').Text +
      '項(xiàng)目';
   ComboBox1Change(self);
   RadioButton1Click(self);
End;

Procedure TSearchSellOnlyForm.LbSpeedButton2Click(Sender: TObject);
Begin
   Close;
End;

Procedure TSearchSellOnlyForm.LbButton1Click(Sender: TObject);
Begin
   SaveDBGridEhToFile(ADOQuery4, DBGridEh4, SaveDialog1);
End;

Procedure TSearchSellOnlyForm.FormCreate(Sender: TObject);
Var
   adot             : TADOQuery;
   i                : integer;
Begin
   sCastNo := TStringlist.create;

   adot := TADOQuery.create(self);
   With adot Do Begin
      connection := DM.DataModule2.ADOConnection1;
      sql.Text := 'select * from 客戶信息表 where 公司編號=''' +
         ADOQuery1.fieldbyname('公司編號').Text + '''';
      open;
      ComboBox2.Items.Clear;
      For i := 0 To recordcount - 1 Do Begin
         ComboBox2.Items.Add(fieldbyname('姓名').Text);
         sCastNo.Add(fieldbyname('客戶編號').Text);
         Next;
      End;
      Free;

   End;

End;

Procedure TSearchSellOnlyForm.ComboBox1Change(Sender: TObject);
Var
   adot             : TADOQuery;
   i                : integer;
Begin
   If combobox1.Text = '拆遷還建' Then
      sTname := '單元拆遷還建表'
   Else
      sTname := '單元預(yù)售表';

   adot := TADOQuery.create(self);
   With adot Do Begin
      connection := DM.DataModule2.ADOConnection1;
      sql.Text := 'select * from ' + sTname + ' where 公司編號=''' +
         ADOQuery1.fieldbyname('公司編號').Text + ''' and 項(xiàng)目編號=''' +
         ADOQuery2.fieldbyname('項(xiàng)目編號').Text + '''';

      open;
      ComboBox3.Items.Clear;
      For i := 0 To recordcount - 1 Do Begin
         ComboBox3.Items.Add(fieldbyname('合同號').Text);
         Next;
      End;
      Free;

   End;
End;

Procedure TSearchSellOnlyForm.ADOQuery1AfterScroll(DataSet: TDataSet);
Begin
   DBGridEh1CellClick(DBGridEH1.Columns.Items[0]);
End;

Procedure TSearchSellOnlyForm.ADOQuery2AfterScroll(DataSet: TDataSet);
Begin
   DBGridEh2CellClick(DBGridEh2.Columns.Items[0]);
End;

Procedure TSearchSellOnlyForm.RadioButton1Click(Sender: TObject);
Begin
   ComboBox2.Enabled := RadioButton1.Checked;
   ComboBox3.Enabled := RadioButton2.Checked;
   ComboBox2.Text := ComboBox2.Items[0];
   ComboBox3.Text := ComboBox3.Items[0];

End;

Procedure TSearchSellOnlyForm.LbSpeedButton1Click(Sender: TObject);
Var
   SQLTEXT          : String;
Begin
   If (Trim(ComboBox2.Text) = '') Or (Trim(ComboBox3.Text) = '') Then Begin
      Application.MessageBox('未選擇客戶名或合同號！查詢無法進(jìn)行！', '錯(cuò)誤警告',
         MB_ICONERROR);
      Exit;
   End;

   If combobox1.Text = '預(yù)(銷)售' Then
      SQLTEXT :=
         'SELECT a.*,b.*,c.* FROM 單元信息表 a ' +
         'inner join (單元預(yù)售表 b ' +
         'inner join 客戶信息表 c on b.客戶編號=c.客戶編號)' +
         'on (a.公司編號=b.公司編號 '

   Else
      SQLTEXT :=
         'SELECT a.*,b.*,c.* FROM 單元信息表 a ' +
         'inner join (單元拆遷還建表 b ' +
         'inner join 客戶信息表 c on b.客戶編號=c.客戶編號)' +
         ' on (a.公司編號=b.公司編號 ';

   If Not CheckBox1.Checked Then
      SQLTEXT := SQLTEXT + ' and a.幢號=''' +
         ADOQuery3.fieldbyname('幢號').Text + '''';
   If Not RadioButton1.Checked Then
      SQLTEXT := SQLTEXT + ' AND A.合同號=''' +
         ComboBox3.Text + ''''
   Else
      SQLTEXT := SQLTEXT + ' and b.客戶編號=''' +
         sCastNo[ComboBox2.Items.IndexOf(ComboBox2.Text)] + '''';

   SQLTEXT := SQLTEXT +
      ') where a.房號=b.房號 and a.幢號=b.幢號 and a.項(xiàng)目編號=b.項(xiàng)目編號 AND B.是否過期=false';
   ADOQuery4.sql.Text := SQLTEXT;

   ADOQuery4.open;
   DBGridEh4.FindColumn('是否過期').Visible := false;
   DBGridEh4.FindColumn('過期日期').Visible := false;
End;

Procedure TSearchSellOnlyForm.LbButton2Click(Sender: TObject);
Begin
   If CheckBox2.Checked Then Begin
      If combobox1.Text = '預(yù)(銷)售' Then
         AcReport1.LoadFromFile(DM.CURRDIR + '\NIL_XMXSB_YXS.APT')
      Else
         AcReport1.LoadFromFile(DM.CURRDIR + '\NIL_XMXSB_CQHJ.APT');
   End Else Begin
      If combobox1.Text = '預(yù)(銷)售' Then
         AcReport1.LoadFromFile(DM.CURRDIR + '\XMXSB_YXS.APT')
      Else
         AcReport1.LoadFromFile(DM.CURRDIR + '\XMXSB_CQHJ.APT');
   End;
   Case TLbButton(Sender).tag Of
      100: AcReport1.Preview;
      101: AcReport1.Print;
   End;
End;

Procedure TSearchSellOnlyForm.AALabel2Click(Sender: TObject);
Begin
   RadioButton1.Checked := true;
End;

Procedure TSearchSellOnlyForm.AALabel3Click(Sender: TObject);
Begin
   RadioButton2.Checked := true;
End;

Procedure TSearchSellOnlyForm.DBGridEh4DblClick(Sender: TObject);
Begin
   If ADOQuery4.Active = false Then Exit;
   If ADOQuery4.recordcount = 0 Then Exit;
   ViewUnitForm := TViewUnitForm.create(self);
   ViewUnitForm.Show;
   Application.ProcessMessages;
   ViewUnitForm.ADOQuery1.RecNo := SearchSellOnlyForm.ADOQuery1.RecNo;
   ViewUnitForm.DBGridEh1CellClick(ViewUnitForm.DBGridEH1.Columns.Items[0]);
   ViewUnitForm.ADOQuery2.RecNo := SearchSellOnlyForm.ADOQuery2.RecNo;
   ViewUnitForm.DBGridEh2CellClick(ViewUnitForm.DBGridEh2.Columns.Items[0]);
   ViewUnitForm.ADOQuery3.RecNo := SearchSellOnlyForm.ADOQuery3.RecNo;
   ViewUnitForm.ADOQuery4.sql.Text :=
      'SELECT * FROM 單元信息表 WHERE 公司編號=''' +
      ViewUnitForm.ADOQuery1.fieldbyname('公司編號').Text +
      ''' and 項(xiàng)目編號=''' +
      ViewUnitForm.ADOQuery2.fieldbyname('項(xiàng)目編號').Text + ''' and 幢號='''
      +
      ViewUnitForm.ADOQuery3.fieldbyname('幢號').Text + ''' AND 房號=''' +
      ADOQuery4.fieldbyname('a.房號').Text + '''';
   ViewUnitForm.ADOQuery4.open;

   ViewUnitForm.DBGridEh4CellClick(ViewUnitForm.DBGridEh4.Columns.Items[0]);
   ViewUnitForm.LbSpeedButton2.click;
End;

End.