conbrowse.~pas

书籍类别（种类编号

  Excelid.WorkSheets[1].Cells[2,1].Value :='建设单位：城市发展局----年 '+ProName+'                                        '+Datetostr(now)+'                             单位：元     ' ;



  Excelid.worksheets[1].range['A1:'+lie+'400'].HorizontalAlignment := $FFFFEFF4;
  Excelid.worksheets[1].range['A1:'+lie+'400'].VerticalAlignment := $FFFFEFF4;

  //**************************************************************************************************
  i:=4;
  Excelid.WorkSheets[1].Cells[i,1].Value := '序号';

  j:=2;
  l:=0;
  for k:=2 to 20 do begin
  if check[k]=1 then begin
  Excelid.WorkSheets[1].Cells[i,j].Value :=char(10);//变成自动换行
  Excelid.WorkSheets[1].Cells[i,j].Value := PName[k];
  if (k=5) or (k=6) or (k=7) or (k=8) or (k=9) or (k=10) or (k=11) or (k=12) then
  begin
  sum1[j]:=j; //数字项的列
  l:=l+1;//共计有几个数字项
  end;
  j:=j+1;
  end;
  end;

  Excelid.worksheets[1].Range['A1:'+lie+'6'].Font.Name := '宋体';
  Excelid.worksheets[1].Range['A1:'+lie+'1'].Font.Size := 14;
  Excelid.worksheets[1].range['A1:'+lie+'4'].font.bold:=true;
  Excelid.worksheets[1].Range['A2:'+lie+'4'].Font.Size := 10;

  Excelid.worksheets[1].Range['A5:'+lie+'400'].Font.Name := '仿宋_GB2312';
  Excelid.worksheets[1].Range['A5:'+lie+'400'].Font.Size := 10;
  //*****************************************************************************************
  Excelid.ActiveSheet.Rows[1].RowHeight := 1/0.035;
     Excelid.Cells[2,1].ColumnWidth:=6;
     Excelid.Cells[2,2].ColumnWidth:=20;
     Excelid.Cells[2,3].ColumnWidth:=20;
     Excelid.Cells[2,4].ColumnWidth:=13;
     Excelid.Cells[2,5].ColumnWidth:=13;
     Excelid.Cells[2,6].ColumnWidth:=13;
     Excelid.Cells[2,7].ColumnWidth:=13;
     Excelid.Cells[2,8].ColumnWidth:=13;
     Excelid.Cells[2,9].ColumnWidth:=13;
     Excelid.Cells[2,10].ColumnWidth:=13;
     Excelid.Cells[2,11].ColumnWidth:=13;
     //Excelid.Cells[2,12].ColumnWidth:=40 ;
//*****************************************************************************************
  i:=5;
 
      DM.ADOQuery9.Close;
      DM.ADOQuery9.SQL.Clear;
      ///////////////////////////////////////////////////////
      if remainZero=false then begin//余额为零不导出
      if str888_1=200 then
      str999:=str888+'and biaoji=0 '
      else
      str999:=str888+'  where biaoji=0 ';
      end else
      begin
      if str888_1=200 then
      str999:=str888+'and biaoji=0 and remain<>0 '
      else
      str999:=str888+'  where biaoji=0 and remain<>0 ';
      end;
      ///////////////////////////////////////////////////////////

      DM.ADOQuery9.SQL.Add(str999);
      DM.ADOQuery9.Open;
      DM.ADOQuery9.Sort:= DM.AdoQuery2.Sort;
      DM.Adoquery9.First;

       while not DM.Adoquery9.Eof do
    begin
      j:=2;
      for k:=2 to 20 do begin
      if check[k]=1 then begin


      if k=16 then begin  // k=16时导出项目里面含有合同序号
      DM.ADOQuery8.Close;
      DM.ADOQuery8.SQL.Clear;
      DM.ADOQuery8.SQL.Add('select connumber,connumber_1 from contract where connumber_1='''+DM.Adoquery9.FieldValues['ConNumber']+'''');
      DM.ADOQuery8.Open;
     if DM.ADOQuery8.RecordCount>0 then begin//如果存在联合记帐
       while not DM.Adoquery8.Eof do
          begin
          strnum:=strnum+char(10)+DM.Adoquery8.FieldValues['ConNumber'];
          DM.Adoquery8.Next;
          end;
          strnum:=DM.Adoquery9.FieldValues['ConNumber']+strnum;//合同序号累加
          end;
          end;



      Excelid.WorkSheets[1].Cells[i,j].Value :=char(10);//变成自动换行
      if strnum<>'' then begin
      Excelid.WorkSheets[1].Cells[i,j].Value :=strnum ;
      strnum:='';
      end else
      Excelid.WorkSheets[1].Cells[i,j].Value := (' '+DM.Adoquery9.FieldByName(field[k]).AsString);
      j:=j+1;

      end;
      end;
    


      Excelid.WorkSheets[1].Cells[i,1].Value := i-4;
      i:=i+1;
      DM.Adoquery9.Next;
      s := 'A4:'+lie+ IntToStr(i);

      Excelid.worksheets[1].Range[s].Borders.LineStyle := 1; //添加边框
       end;
      Excelid.WorkSheets[1].Cells[i,2].Value :='小 计' ;
      if l>0 then begin
      for  j:=0 to 16 do begin
      if sum1[j+2]>0 then
      Excelid.WorkSheets[1].Cells[i,sum1[j+2]].Value :='=sum('+Convert(sum1[j+2])+'5:'+Convert(sum1[j+2])+inttostr(i-1)+')' ;
      end;
      end;

      Excelid.ActiveSheet.PageSetup.Orientation:=2;  //页面方向:横向
      Excelid.ActiveSheet.PageSetup.PrintTitleRows:='$1:$4'; //表头为1-4行
   // Excelid.worksheets[1].Range['A2:M3'].HasArray ;
      Excelid.ActiveSheet.PageSetup.PaperSize:=4;    //纸张大小
      Excelid.ActiveSheet.PageSetup.CenterFooter := '第&P頁';
      Excelid.ActiveSheet.PageSetup.TopMargin := 1/0.035;
      Excelid.ActiveSheet.PageSetup.LeftMargin := 1/0.035;
      Excelid.ActiveSheet.PageSetup.rightMargin := 1.5/0.035;
      Excelid.ActiveSheet.PageSetup.CenterHorizontally := 2/0.035;//水平居中
end;
end;
procedure TConForm.ComboBox1DropDown(Sender: TObject);

begin
if ComboxGrid2.Execute then
  begin
     Combobox1.Text:=comboxgrid2.Rvalues1;
     Combobox1.Refresh;
  End;
end;

procedure TConForm.Button1Click(Sender: TObject);
var
j:integer;
SearchTimeF,
SearchTimeT,
BigClass,SmallClass :string;
begin
j:=0;
 if  ComNum('ss')=0 then begin
     showmessage('请选择要查询的项目');
     exit;
     end;
Qstr1:=' where ';
if CheckBox1.Checked and (combobox1.Text='') then begin
     showmessage('请选择施工单位');
     exit;
     end;
if CheckBox3.Checked and (combobox3.Text='') then begin
     showmessage('请选择合同大类');
     exit;
     end;
if CheckBox4.Checked and (combobox4.Text='') then begin
     showmessage('请选择合同小类');
     exit;
     end;
if CheckBox5.Checked and (combobox5.Text='') then begin
     showmessage('请选择合同序号');
     exit;
     end;
if CheckBox1.Checked and (combobox1.Text<>'') then  begin//选择施工单位
   j:=j+1;
   //Qstr1:=Qstr1+'ConstructUnit='''+trim(combobox1.Text)+'''  ';//+combobox6.Text
    Qstr1:=Qstr1+'ConstructUnit like ''%'+trim(combobox1.Text)+'%''  ';//+combobox6.Text
  // Qstr1:=Qstr1+'  ConPerson  like ''%'+Edit1.Text+'%'' '
   if (j<ComNum('ss')) then
    Qstr1:=Qstr1+combobox6.Text;
      end;
if CheckBox2.Checked  then  begin//选择项目名称
   j:=j+1;
    Qstr1:=Qstr1+'  ItemName like ''%'+trim(combobox2.Text)+'%''  ';//+combobox6.Text
   // Qstr1:=Qstr1+' ItemName='''+trim(combobox2.Text)+''' ';
   if (j<ComNum('ss')) then
   Qstr1:=Qstr1+combobox7.Text;
   end;

if CheckBox3.Checked  then begin//选择大类
   j:=j+1;
      DM.ADOQuery3.Close;
      DM.ADOQuery3.SQL.Clear;
      DM.ADOQuery3.SQL.Add('select Maintype from ResourseMaintype where MContent='''+combobox3.Text+'''');
      DM.ADOQuery3.Open;
      BigClass:=DM.ADOQuery3.FieldValues['Maintype'];
      Qstr1:=Qstr1+' main_id='''+BigClass+''' ';
if (j<ComNum('ss')) then
   Qstr1:=Qstr1+combobox8.Text;
   end;
if CheckBox4.Checked then begin    //选择小类
   j:=j+1;
      DM.ADOQuery3.Close;
      DM.ADOQuery3.SQL.Clear;
      DM.ADOQuery3.SQL.Add('select Subtype from ResourceSubtype where SContent='''+combobox4.Text+'''');
      DM.ADOQuery3.Open;
      SmallClass:=DM.ADOQuery3.FieldValues['Subtype'];
   Qstr1:=Qstr1+' Resource_id='''+SmallClass+''' ' ;
   if (j<ComNum('ss')) then
   Qstr1:=Qstr1+combobox9.Text;
   end;
if CheckBox5.Checked then begin    //选择合同序号
   j:=j+1;
   Qstr1:=Qstr1+' ConNumber='''+trim(combobox5.Text)+''' ';
   if (j<ComNum('ss')) then
   Qstr1:=Qstr1+combobox10.Text;
   end;
if CheckBox6.Checked then begin   //选择日期
   j:=j+1;
   SearchTimeF:=FormatDateTime('yyyy-MM-dd',yxedit1.Date);//起始时间
   SearchTimeT:=FormatDateTime('yyyy-MM-dd',yxedit.Date);//终止时间
   Qstr1:=Qstr1+'  Signtime>='''+SearchTimeF+''''+
          ' and Signtime<='''+SearchTimeT+'''';
   if (j<ComNum('ss')) then
   Qstr1:=Qstr1+' '+combobox11.Text;
   end;
if CheckBox7.Checked then begin                                 //选择日期
   Qstr1:=Qstr1+'  ConPerson  like ''%'+Edit1.Text+'%'' '
   end;


  // showmessage('select * from Contract '+Qstr1);

      with DM do
      begin
        ADOQuery2.Close;
        ADOQuery2.SQL.Clear;
        ADOQuery2.SQL.Add('select * from Contract '+Qstr1+'order by ConstructUnit');
        ADOQuery2.Open;
        ADOQuery5.Close;
        ADOQuery5.SQL.Clear;
        ADOQuery5.SQL.Add('select sum(remain) as sumremain from Contract '+Qstr1);
        ADOQuery5.Open;
        if ADOQuery2.RecordCount=0 then begin
        showmessage('没有要查询的记录!!');
        mainForm.statusBar1.Panels.Items[2].Text :='共计 0 条记录   余额共计  0  元';
        Exit ;
        end;
        str888:='select * from Contract '+Qstr1;
        DataSource1.DataSet :=ADOQuery2;
        DBGrid1.DataSource :=DataSource1;
         DBGrid1.Columns.Items[0].FieldName :='Connumber';
        DBGrid1.Columns.Items[0].Width :=DBGrid1.Width div 12;

        DBGrid1.Columns.Items[1].FieldName :='Connumber_1';
        DBGrid1.Columns.Items[1].Width :=DBGrid1.Width div 12;

        DBGrid1.Columns.Items[2].FieldName :='ItemName';
        DBGrid1.Columns.Items[2].Width :=DBGrid1.Width div 12;

        DBGrid1.Columns.Items[3].FieldName :='ConstructUnit';
        DBGrid1.Columns.Items[3].Width :=DBGrid1.Width div 11;

        DBGrid1.Columns.Items[4].FieldName :='BeAudit';
        DBGrid1.Columns.Items[4].Width :=100;

        DBGrid1.Columns.Items[5].FieldName :='AfAudit';
        DBGrid1.Columns.Items[5].Width :=100;

        DBGrid1.Columns.Items[6].FieldName :='AfAuditPay';
        DBGrid1.Columns.Items[6].Width :=100;

        DBGrid1.Columns.Items[7].FieldName :='Payed';
        DBGrid1.Columns.Items[7].Width :=DBGrid1.Width div 11;

        DBGrid1.Columns.Items[8].FieldName :='BePayed';
        DBGrid1.Columns.Items[8].Width :=100;

        DBGrid1.Columns.Items[9].FieldName :='ThisYear';
        DBGrid1.Columns.Items[9].Width :=80;

        DBGrid1.Columns.Items[10].FieldName :='Remain';

        DBGrid1.Columns.Items[11].FieldName :='benyue';
        DBGrid1.Columns.Items[11].Width :=DBGrid1.Width div 12;

        DBGrid1.Columns.Items[12].FieldName :='BSQK';
        DBGrid1.Columns.Items[12].Width :=DBGrid1.Width div 12;
        
        DBGrid1.Columns.Items[13].FieldName :='SignTime';

        DBGrid1.Columns.Items[14].FieldName :='ConPerson';
        DBGrid1.Columns.Items[14].Width :=DBGrid1.Width div 12;

        DBGrid1.Columns.Items[15].FieldName :='locate';
        DBGrid1.Columns.Items[15].Width :=DBGrid1.Width div 12;

        DBGrid1.Columns.Items[16].FieldName :='Fulfill';
        DBGrid1.Columns.Items[16].Width :=DBGrid1.Width div 12;

        DBGrid1.Columns.Items[17].FieldName :='acceptance';
        DBGrid1.Columns.Items[17].Width :=100;

        DBGrid1.Columns.Items[18].FieldName :='tel';
        DBGrid1.Columns.Items[18].Width :=DBGrid1.Width div 12;
        
        str888_1:=200;
      end;
   showMessage('共查找到'+inttostr(DM.ADOQuery2.RecordCount)+'条记录');

   mainForm.statusBar1.Panels.Items[2].Text :='共计 '+inttostr(DM.ADOQuery2.RecordCount)+' 条记录   余额共计  '+string(DM.ADOQuery5.FieldValues['SumRemain'])+'  元';

   if CheckBox3.Checked=true then ProName:=ComBobox3.Text;
   if CheckBox4.Checked=true then ProName:=ComBobox4.Text;
  // SetLogs('查询合同信息');
   Label25.Caption:= '查询结果';
end;

function TConForm.ComNum(str:string):integer;
var
i:integer;
begin
i:=0;
if  CheckBox1.Checked  then  //选择施工单位
 i:=i+1;
if CheckBox2.Checked then  //选择项目名称
 i:=i+1;
if CheckBox3.Checked  then //选择大类
 i:=i+1;
if CheckBox4.Checked  then    //选择小类
 i:=i+1;
if CheckBox5.Checked  then    //选择合同序号
 i:=i+1;
if  CheckBox6.Checked then                                  //选择日期
 i:=i+1;
if  CheckBox7.Checked then                                  //选择日期
 i:=i+1;

 result:= i;
end;
function TConForm.Convert(intt:integer):string;
 var
lie: string;
begin
 case intt of
  2: lie:='B';
  3: lie:='C';
  4: lie:='D';
  5: lie:='E';
  6: lie:='F';
  7: lie:='G';
  8: lie:='H';
  9: lie:='I';
  10: lie:='J';
  11: lie:='K';
  12: lie:='L';
  13: lie:='M';
  14: lie:='N';
  15: lie:='O';
  16: lie:='P';
  17: lie:='Q';
  18: lie:='R';
  19: lie:='S';
  20: lie:='T';
  21: lie:='U';
  22: lie:='V';
  23: lie:='W';