frm_sjcx.pas

用于家庭养殖的财务进出

unit frm_SJCX;

interface

uses
   Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, CheckLst, Grids, DBGrids, frame_CompanyList, ComCtrls,ComObj,
  StrUtils,Windows;

type
  Tfrm_XJCX = class(TForm)
    GroupBox1: TGroupBox;
    CKB_Table: TCheckListBox;
    GroupBox2: TGroupBox;
    ListBox1: TListBox;
    Button6: TButton;
    Button7: TButton;
    Button4: TButton;
    Button5: TButton;
    ListBox2: TListBox;
    GroupBox3: TGroupBox;
    CB_Field: TComboBox;
    CB_Relation: TComboBox;
    CB_Value: TComboBox;
    GroupBox5: TGroupBox;
    RB1: TRadioButton;
    rb2: TRadioButton;
    CB_Table: TComboBox;
    GroupBox10: TGroupBox;
    DBG_Result: TDBGrid;
    B_Seach: TButton;
    Button3: TButton;
    B_TJ: TButton;
    B_export: TButton;
    GB_SunInfo: TGroupBox;
    RadioButton1: TRadioButton;
    RadioButton2: TRadioButton;
    RadioButton3: TRadioButton;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    GB_SubInfo: TGroupBox;
    DBGrid2: TDBGrid;
    B_SubInfo: TButton;
    GroupBox4: TGroupBox;
    Frame61: TFrame6;
    E_Value: TEdit;
    DPT_Value: TDateTimePicker;
    Memo2: TMemo;
    SaveDialog1: TSaveDialog;
    SaveDialog2: TSaveDialog;
    StatusBar1: TStatusBar;
    ProgressBartoExcel: TProgressBar;
    LB_SqlCN: TListBox;
    B_CXAdd: TButton;
    B_CXDel: TButton;
    B_CXClear: TButton;
    procedure B_SubInfoClick(Sender: TObject); //是否显示从属信息表
    procedure FormCreate(Sender: TObject);//初始化所有变量，并初始化查询表 数据信息
    procedure Button5Click(Sender: TObject);//将listbox1中的值单个移向listbox2
    procedure Button4Click(Sender: TObject); //将listbox2中的值单个移向listbox1
    procedure Button7Click(Sender: TObject);//将listbox1中的值全部移向listbox2
    procedure Button6Click(Sender: TObject);//将listbox2中的值全部移向listbox1
    procedure CKB_TableClickCheck(Sender: TObject);//显示复选框中数据表里面的全部字段
    procedure CB_TableChange(Sender: TObject); //选择一个查询表
    procedure CB_FieldChange(Sender: TObject); //选中某个字段
    procedure CB_RelationChange(Sender: TObject); //选择字段查询关系"=,>等"
    //procedure B_CreateSQLClick(Sender: TObject);
    procedure B_CancelClick(Sender: TObject); //将sql语句中的所有值清空
    procedure B_SeachClick(Sender: TObject);  //根据sql语句查询
    procedure B_TJClick(Sender: TObject);   //进入数据统计界面
    procedure B_exportClick(Sender: TObject); //查询结果倒出成execl文档
    procedure Button3Click(Sender: TObject); //退出系统
    procedure DBG_ResultCellClick(Column: TColumn);
    procedure RadioButton1Click(Sender: TObject); //从属信息表的选择
    procedure ConnClick(Sender: TObject);  //没用
    procedure B_CXAddClick(Sender: TObject);  //创建sql语句，并关联前面sql语句
    procedure B_CXClearClick(Sender: TObject);//清空所有查询条件
    procedure B_CXDelClick(Sender: TObject); //删除选中的查询条件
    procedure FormDestroy(Sender: TObject); //关闭窗体释放初始化变量
    //procedure rb2Click(Sender: TObject);
  private
    function DataToExcel(strFileName:string):boolean;//数据转化成execl文档
    procedure CreateSQL;
    { Private declarations }
  public
    //UnChoseFieldlist:Tstrings;  //代替listbox3
    //ChosedFieldlist:Tstrings;   //代替listbox4
    { Public declarations }
  end;

var
   frm_XJCX: Tfrm_XJCX;
   grid_recordindex:integer;
   m_fieldlist:string;
   m_tablelist:string;
   m_wherelist:string;
   companylist:string;
   tconn_str:string;

   i_count:integer; //判断是否为第一个查询条件
   s_info:string; //每次查询条件字符串
implementation
   uses data_module,unit_global,unit3, frm_sjtj;
{$R *.dfm}

procedure Tfrm_XJCX.B_SubInfoClick(Sender: TObject); //是否显示从属信息表
begin
  if b_subinfo.Caption='从属信息〉〉' then
  begin
    b_subinfo.Caption:='隐藏从属信息';
    frm_XJCX.Height:=frm_XJCX.Height+GB_SubInfo.Height;
    //frm_XJCX.Constraints.MaxHeight:=frm_XJCX.Height;
    //frm_XJCX.Constraints.MinHeight:=frm_XJCX.Height;
    //frm_XJCX.Constraints.MaxWidth:=frm_XJCX.Width;
    //frm_XJCX.Constraints.MinWidth:=frm_XJCX.Width;
    GB_SubInfo.Visible:=true;
    GB_SunInfo.Visible:=true;
  end
  else
  begin
    b_subinfo.Caption:='从属信息〉〉';
    frm_XJCX.Height:=frm_XJCX.Height-GB_SubInfo.Height;
    //frm_XJCX.Constraints.MaxHeight:=frm_XJCX.Height;
    //frm_XJCX.Constraints.MinHeight:=frm_XJCX.Height;
    //frm_XJCX.Constraints.MaxWidth:=frm_XJCX.Width;
    //frm_XJCX.Constraints.MinWidth:=frm_XJCX.Width;
    GB_SubInfo.Visible:=false;
    GB_SunInfo.Visible:=false;
  end;
end;

procedure Tfrm_XJCX.FormCreate(Sender: TObject);
begin
  //dbg_result.DataSource:=dm.DataSource1;
  //=========初始化所有变量
  sql_conden:=tstringlist.create;
  //UnChoseFieldlist:=tstringlist.Create;
  //ChosedFieldlist:=tstringlist.Create;
  tablelist:=tstringlist.create;
  SJ_PreFieldName:=tstringlist.Create;
  SJ_SelectFieldName:=tstringlist.Create;
  //SelectField_Rellist:=tstringlist.Create;
  SearchTablelist:=tstringlist.create;
  SearchFieldList:=tstringlist.Create;
  SqlTablelist:=tstringlist.Create;
  SqlFieldlist:=Tstringlist.Create;
  SqlRelation:=Tstringlist.Create;
  SqlValue:=Tstringlist.Create;
  SqlConn:=Tstringlist.Create;
  resultlist:=tstringlist.create;
  //end初始化==========

  frm_XJCX.Height:=frm_XJCX.Height-GB_SubInfo.Height;
  GB_SubInfo.Visible:=false;
  GB_SunInfo.Visible:=false;
  CB_value.Visible:=false;
  dpt_value.Visible:=false;
  //第一次生成sql语句时不可用
  rb1.Enabled:=false;
  rb2.Enabled:=false;
  i_count:=0;
  //=================
  with dm.ADOQuery1 do
  begin
    close;
    sql.Clear;
    sql.Add('select tablename,dwname from dw_info where yn');
    open;
    if not eof then
    begin
      while not eof do
      begin
        ckb_table.Items.Add(fieldbyname('dwname').AsString);
        tablelist.add(fieldbyname('tablename').AsString);
        cb_table.AddItem(fieldbyname('dwname').AsString,cb_table);
        SearchTablelist.Add(fieldbyname('tablename').AsString);
        next;
      end;
    end;
  end;
end;

procedure Tfrm_XJCX.Button5Click(Sender: TObject);
var
  i,count,postion:integer;
begin
  count:=0;
  for i:=0 to listbox1.count-1 do
  begin
    if listbox1.Selected[count] then
    begin
      listbox2.AddItem(listbox1.Items[count],listbox2);
      SJ_SelectFieldName.Add(SJ_PreFieldName[count]);
      //@@@@20053.12 listbox4.AddItem(listbox3.Items[count],listbox4);
      postion:=pos('.',listbox1.Items[count]);
      //!!!SelectField_Rellist.Add(copy(listbox1.Items[count],1,postion-1));
      listbox1.DeleteSelected;
      SJ_PreFieldName.Delete(count);
    end
    else
      count:=count+1;
  end;
end;

procedure Tfrm_XJCX.Button4Click(Sender: TObject);
var
  i,count,postion:integer;
begin
  count:=0;
  for i:=0 to listbox2.count-1 do
  begin
    if listbox2.Selected[count] then
    begin
      listbox1.AddItem(listbox2.Items[count],listbox1);
      SJ_PreFieldName.Add(SJ_SelectFieldName[count]);
      //@@@@@ 20053.12 listbox3.AddItem(listbox4.Items[count],listbox4);
      listbox2.DeleteSelected;
      //!!!SelectField_Rellist.Delete(count);
    end
    else
      count:=count+1;
  end;
end;

procedure Tfrm_XJCX.Button7Click(Sender: TObject);
var
  i,postion:integer;
begin
  for i:=0 to listbox1.Count-1 do
  begin
    listbox2.AddItem(listbox1.Items[i],listbox2);
    SJ_SelectFieldName.Add(SJ_PreFieldName[i]);
    postion:=pos('.',listbox2.Items[i]);
    //SelectField_Rellist.Add(copy(listbox2.Items[i],1,postion-1));
    //@@@@listbox4.AddItem(listbox3.Items[i],listbox4);
  end;
  listbox1.Items.Clear;
  SJ_PreFieldName.Clear;
  //@@@@@@@listbox3.Items.Clear;
end;

procedure Tfrm_XJCX.Button6Click(Sender: TObject);
var
  i:integer;
begin
  for i:=0 to listbox2.Count-1 do
  begin
    listbox1.AddItem(listbox2.Items[i],listbox1);
    SJ_PreFieldName.Add(SJ_SelectFieldName[i]);
    //SelectField_Rellist.Delete(list);
    //@@@@listbox3.AddItem(listbox4.Items[i],listbox3);
  end;
  //SelectField_Rellist.Clear;
  listbox2.Items.Clear;
  SJ_SelectFieldName.Clear;
  //@@@@listbox4.Items.Clear;
end;
procedure Tfrm_XJCX.CKB_TableClickCheck(Sender: TObject);
var
  i,j,count,count1:integer;
  postion:integer;
  table_name:string;
begin
  if ckb_table.Checked[ckb_table.ItemIndex] then   //如果被选中
  begin
    with dm.ADOQuery1 do
    begin
      close;
      sql.Clear;
      sql.Add('select lab_name,field from field_info where table_name='+quotedstr(Tablelist[ckb_table.ItemIndex]));
      open;
      if not eof then
      begin
        first;
        while not eof do
        begin
          listbox1.AddItem(Tablelist[ckb_table.ItemIndex]+'.'+fieldbyname('lab_name').AsString,listbox1);
          SJ_PreFieldName.Add(Tablelist[ckb_table.ItemIndex]+'.'+(fieldbyname('field').AsString));
          next;
        end;
      end;
    end;
  end
  else
  begin
////////////////////2006-03 zjx/////////////////////////
   count1:=0;
   for j:=0 to listbox2.Count-1 do
    begin
      postion:=pos('.',listbox2.Items[count1]);
      table_name:=copy(listbox2.Items[count1],1,postion-1);
      if table_name=tablelist[ckb_table.ItemIndex] then
      begin
        listbox2.ItemIndex:=count1;
        listbox2.DeleteSelected;
        //SJ_PreFieldName.Delete(count1);
      end else
        count1:=count1+1;
    end;
////////////////////////////////////////////////////////
    count:=0;
    for i:=0 to listbox1.Count-1 do
    begin
      postion:=pos('.',listbox1.Items[count]);
      table_name:=copy(listbox1.Items[count],1,postion-1);
      //showmessage(tablelist[ckb_table]);
      if table_name=tablelist[ckb_table.ItemIndex] then
      begin
        listbox1.ItemIndex:=count;
        listbox1.DeleteSelected;
        SJ_PreFieldName.Delete(count);
      end
      else
        count:=count+1;
    end;
  end;
end;


procedure Tfrm_XJCX.CB_TableChange(Sender: TObject);
begin
  if trim(cb_table.Text)<>'' then    //选中了一个表了
  begin
    SearchFieldList.Clear;
    cb_field.Clear;
    //SqlTablelist.Add(cb_table.Text);
    with dm.ADOQuery1 do
    begin
      close;
      sql.Clear;
      sql.Add('select lab_name,field from field_info where table_name='+quotedstr(SearchTablelist[CB_Table.itemindex]));
      open;
      if not eof then
      begin
        first;
        while not eof do
        begin
          CB_Field.AddItem(fieldbyname('lab_name').AsString,cb_field);
          SearchFieldList.Add(fieldbyname('field').AsString);
          next;
        end;
      end;
    end;
  end;
end;

procedure Tfrm_XJCX.CB_FieldChange(Sender: TObject);   //字段改变，然后符号选择框改变同时检索词的类型框也改变
begin
  if trim(cb_field.Text)<>'' then  //选中某个字段
  begin
    //SqlFieldlist.Add(cb_field.Text);
    with dm.ADOQuery1 do
    begin
      close;
      sql.Clear;
      sql.Add('select type from field_info where field='+quotedstr(SearchFieldList[cb_field.Itemindex]));
      open;
      if not eof then
      begin
        if (fieldbyname('type').AsString='E') OR (fieldbyname('type').AsString='UE')  then
        begin
          E_value.Visible:=true;
          CB_value.Visible:=false;
          dpt_value.visible:=false;
        end;
        if (fieldbyname('type').AsString='D') then
        begin
          E_value.Visible:=false;
          CB_value.Visible:=false;
          dpt_value.visible:=true;
        end;
        if (fieldbyname('type').AsString='C') then
        begin
          E_value.Visible:=false;
          CB_value.Visible:=true;
          dpt_value.visible:=false;
          cb_value.Clear;
          loadsubfield(CB_value,SearchFieldList[cb_field.Itemindex]);
        end;
      end;
      cb_relation.Clear;
      loadsig(CB_Relation,fieldbyname('type').AsString);
    end;

  end;
end;

procedure Tfrm_XJCX.CB_RelationChange(Sender: TObject);
begin
  //SqlRelation.Add(unit3.sig(cb_relation));
end;

procedure Tfrm_XJCX.CreateSQL; //生成sql语句
var
  SqlFieldlistcount:integer;
  SqlRelationcount:integer;
  SqlValuecount:integer;
  SqlConncount:integer;                    //在判断当前的筛选条件不是重复的前提下进行填值
  Chi_SQL:string;
  i:integer;
  s_temp:string;
  s_ENsql:string;
begin
  if trim(cb_table.Text)<>'' then
  begin
    SqlTablelist.Add(SearchTablelist[cb_table.itemindex]);
    s_temp:=s_temp+' '+cb_table.Text+'.';
    //memo1.Text:=memo1.Text+' '+cb_table.Text+'.';
  end;
  if trim(cb_field.Text)<>'' then
  begin
    SqlFieldlist.Add(SearchFieldList[cb_field.itemindex]);
    s_temp:= s_temp+cb_field.Text;
    s_ensql:=s_ensql+SearchTablelist[cb_table.itemindex]+'.'+SearchFieldList[cb_field.itemindex];
    //memo1.Text:=memo1.Text
  end;
  if trim(cb_relation.Text)<>'' then
  begin
    SqlRelation.Add(unit3.sig(cb_relation));
    s_temp:= s_temp+cb_relation.Text;
    s_ensql:=s_ensql+' '+unit3.sig(cb_relation);
  end;
  if e_value.Visible then   //如果为相似关系,那么格式应该为'%dfs%'
    if trim(e_value.Text)<>'' then
    if (unit3.sig(cb_relation)='like') or (unit3.sig(cb_relation)='not like') then  //如果为相似关系
      sqlvalue.Add(quotedstr('%'+e_value.Text+'%'))
    else
      SqlValue.Add(quotedstr(e_value.Text));
  if cb_value.Visible then
    if trim(cb_value.Text)<>'' then
    if (unit3.sig(cb_relation)='like') or (unit3.sig(cb_relation)='not like') then
      sqlvalue.Add(quotedstr('%'+cb_value.Text+'%'))
    else
      SqlValue.Add(quotedstr(cb_value.Text));
  if dpt_value.Visible then
    SqlValue.Add(quotedstr(datetostr(dpt_value.DateTime)));
  SqlValuecount:=SqlValue.count;
  if sqlvaluecount>0 then
  begin
    s_temp:= s_temp+sqlvalue[SqlValuecount-1];
    s_ensql:=s_ensql+' '+sqlvalue[SqlValuecount-1];
  end;
  for i:=0 to sql_conden.Count-1 do
     if pos(s_ensql,sql_conden.Strings[i])<>0 then
       break;
  if i=sql_conden.Count then
  begin
    if (rb1.Enabled) or (rb2.Enabled) then
    begin
      if rb1.Checked then
      begin