frm_sjcx.pas

用于家庭养殖的财务进出

        sqlconn.Add(' and ');
        s_temp:='and'+s_temp;
        s_ensql:=' and '+s_ensql;
      end
      else
        if rb2.Checked then
        begin
          sqlconn.Add(' or ');
          s_temp:='or'+s_temp;
          s_ensql:=' or '+s_ensql;
        end
      else
      begin
        rb1.Checked:=true;
        sqlconn.Add(' and ');
        s_temp:='and'+s_temp;
        s_ensql:=' and '+s_ensql;      
      end;
    end;
    sql_conden.Add(s_ensql);
    s_info:=s_temp;
  end;
  //  memo1.Text:=memo1.Text+sqlvalue[SqlValuecount-1];
end;

procedure Tfrm_XJCX.B_CancelClick(Sender: TObject); //将sql语句中的所有值清空
begin
  SqlTablelist.Clear;
  SqlFieldlist.Clear;
  SqlRelation.Clear;
  SqlValue.Clear;
  SqlConn.Clear;
  cb_table.Text:='';
  cb_field.Text:='';
  cb_relation.Text:='';
  e_value.Text:='';
  cb_value.Text:='';
  //memo1.Clear;
  frame61.TreeView1.ClearSelection(false);
  rb1.Checked:=false;
  rb2.Checked:=false;
end;

procedure Tfrm_XJCX.B_SeachClick(Sender: TObject); //查询
var
  sqlstr:string;
  i:integer;
  {m_fieldlist:string;
  m_tablelist:string;
  m_wherelist:string;}
  MyRecPtr: PMyRec;
  tname_fromfield,tname_first,tname_next,m_fieldname:string;     //,tconn_str
  postion,postionnext:integer;
  //companylist:string;
begin
  //createsql;
  m_fieldlist:='';  //select 字段名列表
  m_tablelist:='';  //from 表名列表
  m_wherelist:='';  //where 条件列表
  companylist:='';
  tconn_str:='';
  if resultlist.Count>0 then   //resultlist存放查询结果prescode
    Resultlist.Clear;

  //从列表中折分出需要查询的字段名,同时过过滤出字段中的表名
  m_tablelist:='a01';

  if listbox2.Count=0 then
    begin
      messagebox(self.Handle ,'没有选择要查询的字段','提示',MB_OK	+MB_ICONINFORMATION);
      exit;
    end;
  for i:=0 to listbox2.Count-1 do   //需要显示的字段
  begin
    m_fieldname:=copy(SJ_SelectFieldName[i],pos('.',SJ_SelectFieldName[i])+1,length(SJ_SelectFieldName[i]));//从列表字段中取出字段名
    if pos(trim(m_fieldname),trim(m_fieldlist))=0 then   //不重复
      begin
      m_fieldlist:=m_fieldlist+','+m_fieldname;
      tname_fromfield:=leftstr(SJ_SelectFieldName[i],pos('.',SJ_SelectFieldName[i])-1);
        if pos(lowercase(tname_fromfield),lowercase(m_tablelist))=0 then
          m_tablelist:=m_tablelist+','+tname_fromfield;  //从字段列表中找出表名
      end; //end if
  end; //end for


  if copy(trim(m_fieldlist),1,1)=',' then        //若第一个字符是逗号
    m_fieldlist:=copy(m_fieldlist,2,length(m_fieldlist));

  //罗列查询表名
  if frame61.TreeView1.SelectionCount<>0 then   //只要选有单位节点都需要查询a02表
    if pos('a02',lowercase(m_tablelist))=0 then  //防止'a02'重复
      m_tablelist:=m_tablelist+',a02';
      
  for i:=0 to SqlTablelist.Count-1 do
    if pos(lowercase(SqlTablelist[i]),lowercase(m_tablelist))=0 then
      m_tablelist:=m_tablelist+','+SqlTablelist[i];
  {for i:=0 to SelectField_Rellist.Count-1 do
    if pos(lowercase(SelectField_Rellist[i]),lowercase(m_tablelist))=0 then
      m_tablelist:=m_tablelist+','+SelectField_Rellist[i];}
  if copy(trim(m_tablelist),1,1)=',' then
    m_tablelist:=copy(m_tablelist,2,length(m_tablelist));

    if SQL_CondEN.Count=0 then
    begin
      messagebox(self.Handle ,'没有输入查询条件','提示',MB_OK	+MB_ICONINFORMATION);
      exit;
    end;

    
  //生成prescode联等字符串
    postion:=posex(',',m_tablelist,1);
    tname_first:=copy(m_tablelist,1,postion-1);
    if trim(tname_first)<>'' then
    begin
      postionnext:=posex(',',m_tablelist,postion+1);
      if postionnext<>0 then
        tname_next:=copy(m_tablelist,postion+1,postionnext-1-postion)
      else
        tname_next:=copy(m_tablelist,postion+1,length(m_tablelist));
      while trim(tname_next)<>'' do
      begin
        if pos(lowercase(trim(tname_first+'.prescode='+tname_next+'.prescode')),lowercase(tconn_str))=0 then
          tconn_str:=tconn_str+tname_first+'.prescode='+tname_next+'.prescode and ';
        postion:=postionnext;
          if postion<>0 then
            begin
            postionnext:=posex(',',m_tablelist,postion+1);
              if postionnext<>0 then
                tname_next:=copy(m_tablelist,postion+1,postionnext-1-postion)
                else
                  tname_next:=copy(m_tablelist,postion+1,length(m_tablelist));
            end
            else
            break; // tname_next:='';
      end;//end while
    end;//end if
  if RightStr(tconn_str,4)='and ' then     //去掉最后的"and"
    tconn_str:=copy(tconn_str,1,length(tconn_str)-4);
 {
  if frame61.TreeView1.SelectionCount<>0 then   //只要选有单位节点都需要查询a02表
  begin
    m_tablelist:=m_tablelist+',b01';
  end;
  }
  m_wherelist:='';
  for i:=0 to sql_conden.Count-1 do
  begin
    m_wherelist:=m_wherelist+sql_conden.Strings[i];
  end;
  if copy(m_wherelist,1,4)=lowercase(' and') then
    m_wherelist:=copy(m_wherelist,5,length(m_wherelist))
  else
    if copy(m_wherelist,1,3)=lowercase(' or') then
      m_wherelist:=copy(m_wherelist,4,length(m_wherelist));

  if frame61.TreeView1.SelectionCount<>0 then
    if PMyRec(frame61.treeview1.Selected.Data)^.dm<>'' then
      companylist:=' a02.a0209='+quotedstr(PMyRec(frame61.treeview1.Selected.Data)^.dm)
    else
      companylist:=' a02.a0209=b01.dm and b01.host like '+quotedstr('%,'+inttostr(PMyRec(frame61.treeview1.Selected.Data)^.id)+',%');

  if trim(tconn_str)<>'' then
  begin
    sqlstr:='select a01.prescode, '+m_fieldlist+' from '+m_tablelist+' where ('+tconn_str+')';
    if m_wherelist<>'' then
      sqlstr:=sqlstr+' and ('+m_wherelist+')';
    if companylist<>'' then
      sqlstr:=sqlstr+' and '+companylist;
  end
  else
  begin
    sqlstr:='select a01.prescode, '+m_fieldlist+' from '+m_tablelist+' where ';
    if m_wherelist<>'' then
    begin
      sqlstr:=sqlstr+'('+m_wherelist+')';
      if companylist<>'' then
        sqlstr:=sqlstr+' and '+companylist;
    end
    else
    begin
      if companylist<>'' then
        sqlstr:=sqlstr+companylist;
    end;
  end;
  ReadCursor;
  memo2.Text:=sqlstr;
  begin
    with dm.ADOQuery4 do   //查询并且显示
    begin
      close;
      sql.Clear;
      sql.Add(sqlstr);
      open;
      //if not eof then
      first;
      while not eof do
      begin
        Resultlist.Add(fieldbyname('prescode').AsString);
        next;
      end;
      begin
        dm.DataSource1.DataSet:=dm.ADOQuery4;
        dbg_result.DataSource:=dm.DataSource1;
        for i:=0 to dbg_result.Columns.Count-1 do
          dbg_result.Columns.Delete(0);
        for i:=0 to listbox2.Count-1 do
        begin
          dbg_result.Columns.Add;
          dbg_result.Columns[i].Title.Caption:=listbox2.Items[i];
          dbg_result.Columns[i].Field:=fieldbyname(copy(SJ_SelectFieldName[i],pos('.',SJ_SelectFieldName[i])+1,length(SJ_SelectFieldName[i])));//折分的字段名//Fields[i];//.AsString;// .FieldList[i];
          dbg_result.Columns[i].Width:=50;
        end;
      end;
    end;
  end;
  ResumeCursor;
end;

procedure Tfrm_XJCX.B_TJClick(Sender: TObject);
var
  stemp:string;
  m_tablestr:string;
begin
  frm_tj.Show;
  frm_tj.PB1.Position:=frm_tj.PB1.Min;  //2006-03 zjx
end;

procedure Tfrm_XJCX.B_exportClick(Sender: TObject);
begin
  if savedialog1.Execute then
  if DataToExcel(savedialog1.FileName ) then
    messagebox(self.Handle,'导出成功','完成',MB_ICONINFORMATION);
end;

function Tfrm_XJCX.DataToExcel(strFileName:string):boolean;
var
 iCount, jCount: Integer;
 XLApp,MyWorkBook: Variant;
 Sheet: Variant;
 I: Integer;
begin
try
 Screen.Cursor := crHourGlass;
 if not VarIsEmpty(XLApp) then
 begin
   XLApp.DisplayAlerts := False;
   XLApp.Quit;
   VarClear(XLApp);
 end;

 try
   XLApp := CreateOleObject('Excel.Application');
   MyWorkBook:=CreateOleobject('Excel.Sheet');
   MyworkBook:=XLApp.workBooks.Add;
 except
   Screen.Cursor := crDefault;
 Exit;
 end;
   //XLApp.SheetsInNewWorkbook := 1;
   MyWorkBook.WorkSheets[1].Name := DBG_result.Name;
   Sheet := XLApp.Workbooks[1].WorkSheets[DBG_result.Name];

   if not DBG_result.DataSource.DataSet.Active then
   begin
     Screen.Cursor := crDefault;
     Exit;
   end;

   DBG_result.DataSource.DataSet.first;
   for iCount := 0 to DBG_result.Columns.Count - 1 do
     Sheet.Cells[1, iCount + 1] := DBG_result.Columns.Items[iCount].Title.Caption;

   jCount := 1;
   ProgressBarToExcel.Max := DBG_result.DataSource.DataSet.RecordCount;
   ProgressBarToExcel.Position := 0;
   while not DBG_result.DataSource.DataSet.Eof do
   begin
     for iCount := 0 to DBG_result.Columns.Count - 1 do
       Sheet.Cells[jCount + 1, iCount + 1] := DBG_result.Columns.Items[iCount].Field.AsString;
     Inc(jCount);
     DBG_result.DataSource.DataSet.Next;
     ProgressBarToExcel.StepBy(1);
   end;
 //XlApp.Visible := True;
          Screen.Cursor := crDefault;
          MyWorkBook.saveas(strFileName);
          MyWorkBook.close;
          XlApp.Quit;
          XlApp:=Unassigned;
          Result := true;
        except //当存为一个已有的文档而又不覆盖时将
              Result := false;
        end;
end;
procedure Tfrm_XJCX.Button3Click(Sender: TObject);
begin
  close;
end;

procedure Tfrm_XJCX.DBG_ResultCellClick(Column: TColumn);
begin
  grid_recordindex:=dbg_result.SelectedIndex;
end;

procedure Tfrm_XJCX.RadioButton1Click(Sender: TObject);
var
  table_name:string;
  sqlstr:string;
  i:integer;
begin
  if radiobutton1.Checked then
  begin
    table_name:='a02';
    //groupbox11.Caption:='职务信息';
  end
  else if radiobutton2.Checked then
  begin
    table_name:='a04';
    //groupbox11.Caption:='家庭成员信息';
  end
  else if radiobutton3.Checked then
  begin
    table_name:='a06';
    //groupbox11.Caption:='重大事项信息';
  end;
  //groupbox11.Visible:=true;
  //dbgrid2.Visible:=true;
  dm.DataSource2.DataSet:=dm.ADOQuery5;
  dbgrid2.DataSource:=dm.DataSource2;
  //showmessage(inttostr(grid_recordindex));
  if grid_recordindex<1 then
    exit;                         //2006-03 zjx
  sqlstr:='select * from '+table_name+' where prescode='+quotedstr(resultlist[grid_recordindex]);
  with dm.ADOQuery5 do
    begin
      close;
      sql.clear;
      sql.Add(sqlstr);
      open;
      {for i:=0 to recordcount-1 do
      begin
        dbgrid2.Columns[i].Title.Caption:=dm.ADOQuery5.FieldDefList
      end;}
    end;
end;

procedure Tfrm_XJCX.ConnClick(Sender: TObject); //当选者“并且”“或”的时候生成本次查询条件
begin
 { if  rb1.Checked  then
  begin
    if SqlFieldlist.Count<>sqlconn.Count+1 then
      CreateSQL;
    //Memo1.Text:=memo1.Text+' and ';
    sqlconn.Add('and');
  end;
  if rb2.Checked then
  begin
    if sqlfieldlist.count<>sqlconn.Count+1 then
      CreateSQL;
    //Memo1.Text:=memo1.Text+' or ';
    sqlconn.Add('or');
  end; }
end;

procedure Tfrm_XJCX.B_CXAddClick(Sender: TObject);
var
  i:integer;
begin
  if (cb_table.Text='') or (cb_field.Text='') or (cb_relation.Text='') then
    exit;
  CreateSQL;      //创建sql语句然后给lb_sqlcn赋值
  i_count:=i_count+1;
  if i_count>0 then
  begin
    rb1.Enabled:=true;
    rb2.Enabled:=true;
  end;
  for i:=0 to lb_sqlcn.Items.Count-1 do
  begin
    if s_info=lb_sqlcn.Items[i] then
      break;
  end;
  if i=lb_sqlcn.Items.count then
    lb_sqlcn.Items.Add(s_info);
  //rb1.Checked:=true;
  //rb2.Checked:=false;
end;

procedure Tfrm_XJCX.B_CXClearClick(Sender: TObject);
begin
  i_count:=0;
  SqlTablelist.Clear;
  SqlFieldlist.Clear;
  SqlRelation.Clear;
  SqlValue.Clear;
  SqlConn.Clear;
  sql_conden.Clear;
  cb_table.Text:='';
  cb_field.Text:='';
  cb_relation.Text:='';
  e_value.Text:='';
  cb_value.Text:='';
  lb_sqlcn.Clear;
  frame61.TreeView1.ClearSelection(false);
  companylist:='';
  tconn_str:='';
  rb1.Checked:=false;
  rb2.Checked:=false;
  rb1.Enabled:=false;
  rb2.Enabled:=false;
end;

procedure Tfrm_XJCX.B_CXDelClick(Sender: TObject);
begin
  if lb_sqlcn.ItemIndex<0 then
    exit;                     //2006-03 zjx
  if lb_sqlcn.Selected[lb_sqlcn.ItemIndex] then
  begin
    sql_conden.Delete(lb_sqlcn.ItemIndex);
    //showmessage(inttostr(sql_conden.count));
    lb_sqlcn.DeleteSelected;
  end else
    exit;               //2006-03 zjx
  if lb_sqlcn.Items.Count=0 then
  begin
    rb1.Checked:=false;
    rb2.checked:=false;
    rb1.Enabled:=false;
    rb2.Enabled:=false;
  end;
end;

procedure Tfrm_XJCX.FormDestroy(Sender: TObject);
begin
  sql_conden.free;
  //UnChoseFieldlist.free; //no use
  //ChosedFieldlist.free;
  tablelist.free;
  SJ_PreFieldName.free;
  SJ_SelectFieldName.free;
  //SelectField_Rellist.free;
  SearchTablelist.free;
  SearchFieldList.free;
  SqlTablelist.free;
  SqlFieldlist.free;
  SqlRelation.free;
  SqlValue.free;
  SqlConn.free;
  resultlist.free;
end;

end.