searchbytimefm.pas

一款房产中介软件

      '未售:日期代表：待售起始日期';

   Application.MessageBox(s, '日期查询说明', MB_ICONINFORMATION);

End;

Procedure TfrmSearchByTime.GetReportFile2();
Var
   va1, VA2         : String;
Begin
   If TabSheet1.Showing Then Begin
      va1 := TabSheet1.Caption;
      VA2 := '设计用途为:' + ComboBox4.Text;
   End Else If TabSheet2.Showing Then Begin
      va1 := TabSheet2.Caption;
      VA2 := '开始日期:' + DBDateTimeEditEh1.Text + ' 至 ' + DBDateTimeEditEh2.Text;
   End Else If TabSheet3.Showing Then Begin
      va1 := TabSheet3.Caption;
      If RadioButton1.Checked Then VA2 := '房屋被抵押';
      If RadioButton2.Checked Then VA2 := '房屋是跃错层';
      If RadioButton3.Checked Then VA2 := '房屋属附属房产';
   End Else If TabSheet4.Showing Then Begin
      va1 := TabSheet4.Caption;
      VA2 := '房屋户型为:' + Edit1.Text;
   End;

//   AcReport2.LoadFromFile(DM.CURRDIR + 'Collect.APT');
//   AcReport2.Variants.Clear;
//   AcReport2.Variants.SetVariant('条件', va1);
//   AcReport2.Variants.SetVariant('条件描述', VA2);
//
//   AcReport2.Variants.SetVariant('幢号', ADOQuery3.fieldbyname('幢号').Text);
//   If CheckBox1.Checked Then
//      AcReport2.Variants.SetVariant('幢号', '整个项目')
//   Else
//      AcReport2.Variants.SetVariant('预销售面积', StaticText2.Caption);
//
//   AcReport2.Variants.SetVariant('拆迁还建面积', StaticText7.Caption);
//   AcReport2.Variants.SetVariant('抵押面积', StaticText3.Caption);
//   AcReport2.Variants.SetVariant('租赁面积', StaticText4.Caption);
//   AcReport2.Variants.SetVariant('未售面积', StaticText8.Caption);
//   AcReport2.Variants.SetVariant('预定面积', StaticText5.Caption);
//   AcReport2.Variants.SetVariant('预留面积', StaticText6.Caption);
//   AcReport2.Variants.SetVariant('总面积', StaticText1.Caption);
//   AcReport2.Variants.SetVariant('预销售套数', StaticText9.Caption);
//   AcReport2.Variants.SetVariant('拆迁还建套数', StaticText14.Caption);
//   AcReport2.Variants.SetVariant('抵押套数', StaticText10.Caption);
//   AcReport2.Variants.SetVariant('租赁套数', StaticText11.Caption);
//   AcReport2.Variants.SetVariant('未售套数', StaticText15.Caption);
//   AcReport2.Variants.SetVariant('预定套数', StaticText12.Caption);
//   AcReport2.Variants.SetVariant('预留套数', StaticText13.Caption);
//   AcReport2.Variants.SetVariant('总套数', StaticText37.Caption);
//   AcReport2.Variants.SetVariant('预销售总额', StaticText16.Caption);
//   AcReport2.Variants.SetVariant('拆迁还建总额', StaticText21.Caption);
//   AcReport2.Variants.SetVariant('抵押总额', StaticText17.Caption);
//   AcReport2.Variants.SetVariant('租赁总额', StaticText18.Caption);
//   AcReport2.Variants.SetVariant('未售总额', StaticText22.Caption);
//   AcReport2.Variants.SetVariant('预定总额', StaticText19.Caption);
//   AcReport2.Variants.SetVariant('预留总额', StaticText20.Caption);
//   AcReport2.Variants.SetVariant('总额', StaticText38.Caption);
//
//   AcReport2.Variants.SetVariant('预销售已收款', StaticText23.Caption);
//   AcReport2.Variants.SetVariant('拆迁还建已收款', StaticText28.Caption);
//   AcReport2.Variants.SetVariant('租赁已收款', StaticText25.Caption);
//   AcReport2.Variants.SetVariant('已收款总额', StaticText39.Caption);
//   AcReport2.Variants.SetVariant('预销售未收款', StaticText30.Caption);
//   AcReport2.Variants.SetVariant('拆迁还建未收款', StaticText35.Caption);
//   AcReport2.Variants.SetVariant('租赁未收款', StaticText32.Caption);
//   AcReport2.Variants.SetVariant('未收款总额', StaticText40.Caption);

End;

Procedure TfrmSearchByTime.LbSpeedButton1Click(Sender: TObject);
Begin
   ClearSTEXT;
   If StrToDateDef(DBDateTimeEditEh1.Text, 0) = 0 Then
      DBDateTimeEditEh1.value := Now;
   If StrToDateDef(DBDateTimeEditEh2.Text, 0) = 0 Then
      DBDateTimeEditEh2.value := Now;

   GetCount;
End;

Procedure TfrmSearchByTime.GetCount;
Var
   wstr1, wstr11, wstr2: String;
   ADOC             : TADOQuery;
   total            : double;
   WENDStr          : String;
   ID1, ID2, ID3    : String;
Begin
{
SELECT a.*,b.*,c.* FROM 单元信息表 a
inner join (单元预售表 b
inner join 客户信息表 c on b.客户编号=c.客户编号)
on (a.公司编号=b.公司编号)
where a.房号=b.房号 and a.幢号=b.幢号 and a.项目编号=b.项目编号 and a.幢号='2号楼'
and (b.签订合同日期 between #2001-1-1# and #2006-1-1#)
}
   total := 0;
   ID1 := ADOQuery1.fieldbyname('公司编号').AsString;
   ID2 := ADOQuery2.fieldbyname('项目编号').AsString;
   ID3 := ADOQuery3.fieldbyname('幢号').AsString;

   wstr1 := '  公司编号=''' + ID1 +
      ''' AND 项目编号=''' + ID2 + '''';
   wstr11 := '  A.公司编号=''' + ID1 +
      ''' AND A.项目编号=''' + ID2 + '''';
   wstr2 := ' a.房号=b.房号  and a.项目编号=b.项目编号 and b.是否过期=FALSE';

   If Not (CheckBox1.Checked) Then Begin
      wstr1 := wstr1 + ' and 幢号=''' + ID3 + '''';
      wstr2 := wstr2 + ' and a.幢号=b.幢号 and a.幢号=''' + ID3 + '''';

      wstr11 := wstr11 + ' AND A.幢号=''' + ID3 + '''';
   End;

   If TabSheet1.Showing Then
      WENDStr := ' 用途=''' + ComboBox4.Text + '''' //设计用途
   Else If TabSheet2.Showing Then
      WENDStr := ' BETWEEN #' + DBDateTimeEditEh1.Text + '# and #' +
         DBDateTimeEditEh2.Text + '#) ' //日期
   Else If TabSheet3.Showing Then Begin
      If RadioButton1.Checked Then WENDStr := ' 房屋是否抵押=TRUE '; //单选
      If RadioButton2.Checked Then WENDStr := ' 跃错层=TRUE ';
      If RadioButton3.Checked Then WENDStr := ' 附属房产=TRUE ';
   End
   Else If TabSheet4.Showing Then
      WENDStr := ' 户型=''' + Edit1.Text + ''''; //户形

   If Not TabSheet2.Showing Then wstr2 := wstr2 + ' and ' + WENDStr;

   ADOC := TADOQuery.create(self);
   With ADOC Do Begin
      connection := DMMain.ADOConnection1;

      If TabSheet1.Showing Then Begin
         sql.Text :=
            'select sum(合同约定建筑面积) as sum1 from 单元信息表 where ' +
            wstr1 + ' and 销售类型=''预(销)售''' + ' and ' + WENDStr;
         open;

         StaticText2.Caption := fieldbyname('SUM1').Text;
         total := total + fieldbyname('SUM1').AsFloat;

         sql.Text :=
            'select sum(合同约定建筑面积) as sum1 from 单元信息表 where ' +
            wstr1 + ' and 销售类型=''抵押''' + ' and ' + WENDStr;
         open;
         StaticText3.Caption := fieldbyname('SUM1').Text;
         total := total + fieldbyname('SUM1').AsFloat;
         sql.Text :=
            'select sum(合同约定建筑面积) as sum1 from 单元信息表 where ' +
            wstr1 + ' and 销售类型=''租赁''' + ' and ' + WENDStr;
         open;

         StaticText4.Caption := fieldbyname('SUM1').Text;
         total := total + fieldbyname('SUM1').AsFloat;
         sql.Text :=
            'select sum(合同约定建筑面积) as sum1 from 单元信息表 where ' +
            wstr1 + ' and 销售类型=''预定''' + ' and ' + WENDStr;
         open;
         StaticText5.Caption := fieldbyname('SUM1').Text;
         total := total + fieldbyname('SUM1').AsFloat;
         sql.Text :=
            'select sum(合同约定建筑面积) as sum1 from 单元信息表 where ' +
            wstr1 + ' and 销售类型=''预留''' + ' and ' + WENDStr;
         open;
         StaticText6.Caption := fieldbyname('SUM1').Text;
         total := total + fieldbyname('SUM1').AsFloat;
         sql.Text :=
            'select sum(合同约定建筑面积) as sum1 from 单元信息表 where ' +
            wstr1 + ' and 销售类型=''拆迁还建''' + ' and ' + WENDStr;
         open;
         StaticText7.Caption := fieldbyname('SUM1').Text;
         total := total + fieldbyname('SUM1').AsFloat;
         sql.Text :=
            'select sum(合同约定建筑面积) as sum1 from 单元信息表 where ' +
            wstr1 + ' and 销售类型=''未售''' + ' and ' + WENDStr;
         open;
         StaticText8.Caption := fieldbyname('SUM1').Text;
         total := total + fieldbyname('SUM1').AsFloat;
         StaticText1.Caption := FloatToStr(total);

   //面积完毕

         total := 0;
         sql.Text :=
            'select count(合同约定建筑面积) as sum1 from 单元信息表 where '
            + wstr1 + ' and 销售类型=''预(销)售''' + ' AND ' + WENDStr;
         open;
         StaticText9.Caption := fieldbyname('SUM1').Text;
         total := total + fieldbyname('SUM1').AsFloat;
         LbButton8.Enabled := fieldbyname('sum1').AsFloat > 0;

         sql.Text :=
            'select count(合同约定建筑面积) as sum1 from 单元信息表 where '
            + wstr1 + ' and 销售类型=''抵押''' + ' AND ' + WENDStr;
         open;
         StaticText10.Caption := fieldbyname('SUM1').Text;
         total := total + fieldbyname('SUM1').AsFloat;
         LbButton9.Enabled := fieldbyname('sum1').AsFloat > 0;

         sql.Text :=
            'select count(合同约定建筑面积) as sum1 from 单元信息表 where '
            + wstr1 + ' and 销售类型=''租赁''' + ' AND ' + WENDStr;
         open;
         StaticText11.Caption := fieldbyname('SUM1').Text;
         total := total + fieldbyname('SUM1').AsFloat;
         LbButton10.Enabled := fieldbyname('sum1').AsFloat > 0;

         sql.Text :=
            'select count(合同约定建筑面积) as sum1 from 单元信息表 where '
            +
            wstr1 + ' and 销售类型=''预定''' + ' AND ' + WENDStr;
         open;
         StaticText12.Caption := fieldbyname('SUM1').Text;
         total := total + fieldbyname('SUM1').AsFloat;
         LbButton11.Enabled := fieldbyname('sum1').AsFloat > 0;

         sql.Text :=
            'select count(合同约定建筑面积) as sum1 from 单元信息表 where '
            +
            wstr1 + ' and 销售类型=''预留''' + ' AND ' + WENDStr;
         open;
         StaticText13.Caption := fieldbyname('SUM1').Text;
         total := total + fieldbyname('SUM1').AsFloat;
         LbButton12.Enabled := fieldbyname('sum1').AsFloat > 0;

         sql.Text :=
            'select count(合同约定建筑面积) as sum1 from 单元信息表 where '
            +
            wstr1 + ' and 销售类型=''拆迁还建''' + ' AND ' + WENDStr;
         open;
         StaticText14.Caption := fieldbyname('SUM1').Text;
         total := total + fieldbyname('SUM1').AsFloat;
         LbButton13.Enabled := fieldbyname('sum1').AsFloat > 0;

         sql.Text :=
            'select count(合同约定建筑面积) as sum1 from 单元信息表 where '
            +
            wstr1 + ' and 销售类型=''未售''' + ' AND ' + WENDStr;
         open;
         StaticText15.Caption := fieldbyname('SUM1').Text;
         total := total + fieldbyname('SUM1').AsFloat;
         LbButton14.Enabled := fieldbyname('sum1').AsFloat > 0;

         StaticText37.Caption := FloatToStr(total);
    //套数完成

         sql.Text := 'SELECT sum(b.成交总价) AS SUM1 FROM 单元信息表 a ' +
            'inner join 单元预售表 b  on (a.公司编号=b.公司编号) WHERE ' +
            wstr2;
         open;
 //
         StaticText16.Caption := fieldbyname('SUM1').Text;
         sql.Text := 'SELECT sum(b.抵押贷款额) AS SUM1 FROM 单元信息表 a ' +
            'inner join 单元抵押表 b  on (a.公司编号=b.公司编号) WHERE ' +
            wstr2;
         open;
         StaticText17.Caption := fieldbyname('SUM1').Text;

         sql.Text :=
            'SELECT sum(b.已交租金) AS SUM1,sum(b.未交租金) AS SUM2 FROM 单元信息表 a ' +
            'inner join 单元租赁表 b  on (a.公司编号=b.公司编号) where ' + wstr2;

         open;
         StaticText18.Caption := FloatToStr(fieldbyname('SUM1').AsFloat +
            fieldbyname('SUM2').AsFloat);

         sql.Text := 'SELECT sum(b.预定总价) AS SUM1 FROM 单元信息表 a ' +
            'inner join  单元预定表 b  on (a.公司编号=b.公司编号) WHERE ' +
            wstr2;
         open;
         StaticText19.Caption := fieldbyname('SUM1').Text;
         sql.Text := 'SELECT sum(b.预留总价) AS SUM1 FROM 单元信息表 a ' +
            'inner join  单元预留表 b  on (a.公司编号=b.公司编号) WHERE ' +
            wstr2;
         open;
         StaticText20.Caption := fieldbyname('SUM1').Text;

         sql.Text := 'SELECT sum(b.补差价) AS SUM1 FROM 单元信息表 a ' +
            'inner join  单元拆迁还建表 b  on (a.公司编号=b.公司编号) WHERE ' +
            wstr2;
         open;
         StaticText21.Caption := fieldbyname('SUM1').Text;
         sql.Text := 'SELECT sum(b.待售总价) AS SUM1 FROM 单元信息表 a ' +
            'inner join  单元未售表 b  on (a.公司编号=b.公司编号) WHERE ' +