frm_tjcx.pas

用于家庭养殖的财务进出

unit frm_tjcx;

interface

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

type
  Tfrm_cx = class(TForm)
    B_Seach: TButton;
    B_Cancel: TButton;
    Button3: TButton;
    GroupBox1: TGroupBox;
    ListBox1: TListBox;
    Button4: TButton;
    Button5: TButton;
    Button6: TButton;
    Button7: TButton;
    ListBox2: TListBox;
    GroupBox2: TGroupBox;
    CheckBox1: TCheckBox;
    CheckBox2: TCheckBox;
    CheckBox3: TCheckBox;
    CheckBox4: TCheckBox;
    GroupBox3: TGroupBox;
    RadioButton1: TRadioButton;
    RadioButton2: TRadioButton;
    RadioButton3: TRadioButton;
    GroupBox4: TGroupBox;
    ComboBox1: TComboBox;
    ComboBox4: TComboBox;
    ComboBox7: TComboBox;
    ComboBox10: TComboBox;
    ComboBox13: TComboBox;
    ComboBox2: TComboBox;
    ComboBox5: TComboBox;
    ComboBox8: TComboBox;
    ComboBox11: TComboBox;
    ComboBox14: TComboBox;
    Edit1: TEdit;
    Edit2: TEdit;
    Edit3: TEdit;
    Edit4: TEdit;
    ComboBox12: TComboBox;
    ComboBox9: TComboBox;
    ComboBox6: TComboBox;
    ComboBox3: TComboBox;
    Edit5: TEdit;
    ComboBox15: TComboBox;
    ComboBox16: TComboBox;
    ListBox3: TListBox;
    ListBox4: TListBox;
    GroupBox5: TGroupBox;
    RB1: TRadioButton;
    rb2: TRadioButton;
    GroupBox6: TGroupBox;
    rb3: TRadioButton;
    rb4: TRadioButton;
    GroupBox7: TGroupBox;
    rb5: TRadioButton;
    rb6: TRadioButton;
    GroupBox8: TGroupBox;
    rb7: TRadioButton;
    rb8: TRadioButton;
    GroupBox9: TGroupBox;
    rb9: TRadioButton;
    rb10: TRadioButton;
    GroupBox10: TGroupBox;
    DBGrid1: TDBGrid;
    GroupBox11: TGroupBox;
    DBGrid2: TDBGrid;
    B_TJ: TButton;
    dtp1: TDateTimePicker;
    dtp2: TDateTimePicker;
    dtp3: TDateTimePicker;
    dtp4: TDateTimePicker;
    dtp5: TDateTimePicker;
    Memo1: TMemo;
    B_export: TButton;
    ProgressBartoExcel: TProgressBar;
    SaveDialog1: TSaveDialog;
    procedure Button3Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure B_SeachClick(Sender: TObject);
    procedure RB1Click(Sender: TObject);
    procedure rb2Click(Sender: TObject);
    procedure rb3Click(Sender: TObject);
    procedure rb4Click(Sender: TObject);
    procedure rb5Click(Sender: TObject);
    procedure rb6Click(Sender: TObject);
    procedure rb7Click(Sender: TObject);
    procedure rb8Click(Sender: TObject);
    procedure ComboBox1Change(Sender: TObject);
    procedure ComboBox4Change(Sender: TObject);
    procedure ComboBox7Change(Sender: TObject);
    procedure ComboBox10Change(Sender: TObject);
    procedure ComboBox13Change(Sender: TObject);
    procedure ComboBox2Change(Sender: TObject);
    procedure ComboBox5Change(Sender: TObject);
    procedure ComboBox8Change(Sender: TObject);
    procedure ComboBox11Change(Sender: TObject);
    procedure ComboBox14Change(Sender: TObject);
    procedure Button5Click(Sender: TObject);
    procedure Button4Click(Sender: TObject);
    procedure Button6Click(Sender: TObject);
    procedure Button7Click(Sender: TObject);
    procedure CheckBox1Click(Sender: TObject);
    procedure CheckBox2Click(Sender: TObject);
    procedure CheckBox3Click(Sender: TObject);
    procedure CheckBox4Click(Sender: TObject);
    procedure DBGrid1CellClick(Column: TColumn);
    procedure RadioButton1Click(Sender: TObject);
    procedure RadioButton2Click(Sender: TObject);
    procedure RadioButton3Click(Sender: TObject);
    procedure B_CancelClick(Sender: TObject);
    procedure B_TJClick(Sender: TObject);
    procedure B_exportClick(Sender: TObject);
  private
    function DataToExcel(strFileName:string):boolean;
    { Private declarations }
  public
  procedure showcomp(compnum:integer);
  procedure loadfield(comp:tcomponent);
  //procedure loadsubfield(comp:tcomponent;index:integer);
  procedure radiobuttonclick(comp:tcomponent;index:integer);
  //procedure addcolume(field:tfield;caption:string);
    { Public declarations }
  end;

var
  frm_cx: Tfrm_cx;

implementation
  uses data_module,unit3,unit_global,frm_sjtj; //dmserver,unit3,globalvarunit,frm_sjtj;//
  var
    code:string;
    starttime:string;
    endtime:string;
{$R *.dfm}
function Tfrm_cx.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 := DBGrid1.Name;
   Sheet := XLApp.Workbooks[1].WorkSheets[DBGrid1.Name];

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

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

   jCount := 1;
   ProgressBarToExcel.Max := DBGrid1.DataSource.DataSet.RecordCount;
   ProgressBarToExcel.Position := 0;
   while not DBGrid1.DataSource.DataSet.Eof do
   begin
     for iCount := 0 to DBGrid1.Columns.Count - 3 do
       Sheet.Cells[jCount + 1, iCount + 1] := DBGrid1.Columns.Items[iCount].Field.AsString;
     Inc(jCount);
     DBGrid1.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_cx.Button3Click(Sender: TObject);
begin
  close;
end;

procedure Tfrm_cx.FormCreate(Sender: TObject);
var
  i:integer;
begin
  for i:=0 to Self.ComponentCount-1 do
  begin
   if Self.Components[i] is Tcombobox then
     Tcombobox(Self.Components[i]).Visible:=false
   else if  Self.Components[i] is  Tedit then
     Tedit(Self.Components[i]).Visible:=false
   else if self.Components[i] is tgroupbox then
   begin
     if tgroupbox(self.Components[i]).Caption='条件关系' then
       tgroupbox(self.Components[i]).Visible:=false;
   end
   else if self.Components[i] is tradiobutton then
     begin
       tradiobutton(self.Components[i]).Visible:=false;
       tradiobutton(self.Components[i]).Checked:=false;
     end
   else if self.Components[i] is tdatetimepicker then
     begin
       tdatetimepicker(self.Components[i]).Visible:=false;
       tdatetimepicker(self.Components[i]).DateTime:=now;
     end;
  end;
  radiobutton1.Visible:=true;
  radiobutton2.Visible:=true;
  radiobutton3.Visible:=true;
  radiobutton1.Enabled:=false; //显示查询结果单选框按钮
  radiobutton2.Enabled:=false;
  radiobutton3.Enabled:=false;
  dbgrid2.Visible:=false;
  groupbox11.Visible:=false;
  //dbgrid1.Visible:=false;
  dbgrid1.DataSource:=dm.DataSource1;
  dbgrid2.DataSource:=dm.DataSource2;
  //selstr:='select * from ';
  combobox1.Visible:=true;
  combobox2.Visible:=true;
  combobox3.Visible:=true;
  groupbox5.Visible:=true;
  rb1.Visible:=true;
  rb2.Visible:=true;
  //edit1.Visible:=true;
  combobox1.Items.Clear;
  with dm.ADOQuery1 do
  begin
    close;
    sql.Clear;
    sql.Add('select distinct lab_name,field,table_name from field_info where CX_field order by field');
    open;
    first;
    while not eof do
    begin
      combobox1.AddItem(fieldbyname('lab_name').AsString,combobox1);
      {listbox1.AddItem(fieldbyname('lab_name').AsString,listbox1);
      listbox3.AddItem(fieldbyname('field').AsString+','+fieldbyname('table_name').AsString,listbox3); }
      next;
    end;
  end;
  with dm.ADOQuery1 do
  begin
    close;
    sql.Clear;
    sql.Add('select lab_name,field,table_name from field_info where table_name=''a01'' and cx_field');
    open;
    first;
    while not eof do
    begin
      listbox1.AddItem(fieldbyname('lab_name').AsString,listbox1);
      listbox3.AddItem(fieldbyname('field').AsString+','+fieldbyname('table_name').AsString,listbox3);
      next;
    end;
  end;
  //combobox3.BringToFront;
end;

procedure tfrm_cx.radiobuttonclick(comp:tcomponent;index:integer);
var
  i:integer;
begin
  {for i:=7 to 13 do
  begin
   if Self.Components[i] is Tcombobox then
     Tcombobox(Self.Components[i]).Visible:=true
   else if  Self.Components[i] is  Tedit then
     Tedit(Self.Components[i]).Visible:=true
   else if self.Components[i] is tradiogroup then
     tradiogroup(self.Components[i]).Visible:=true
   else if self.Components[i] is tradiobutton then
     tradiobutton(self.Components[i]).Visible:=true;
  end;  }
end;

procedure Tfrm_cx.B_SeachClick(Sender: TObject);
var
  i,j,k:integer;
  count:integer;
  index:integer;
  sqlstr:string;
  postion:integer;
  fieldstr:string;
  def_field,caption:array[0..9] of string;
begin
   radiobutton1.Checked:=false;
   radiobutton1.Enabled:=false;
   radiobutton2.Checked:=false;
   radiobutton2.Enabled:=false;
   radiobutton3.Checked:=false;
   radiobutton3.Enabled:=false;
   groupbox11.Visible:=false;
   def_field[0]:= 'a0101';
   caption[0]:='姓名';
   def_field[1]:= 'a0103';
   caption[1]:='出生年月';
   def_field[2]:= 'a0105';
   caption[2]:='籍贯';
   def_field[3]:= 'a0303';
   caption[3]:='文化程度';
   def_field[4]:= 'a0304';
   caption[4]:='毕业院校';
   def_field[5]:= 'a0107';
   caption[5]:='入党时间';
   def_field[6]:= 'a0108';
   caption[6]:='工作时间';
   def_field[7]:= 'a0501';
   caption[7]:='备注';
  for i:=0 to dbgrid1.Columns.Count-1 do
    dbgrid1.Columns.Delete(0);
  whestr:='';
  selstr:='select prescode as 序号';
  for count:=0 to listbox2.Count-1 do
  begin
    postion:=pos(',',listbox4.Items[count]);
    fieldstr:=copy(listbox4.Items[count],0,postion-1);
    selstr:=selstr+','+fieldstr+' as '+listbox2.Items[count];
  end;
  //200515修改：相似于无法显示
  {if edit1.Visible then
    value[1]:=quotedstr(edit1.Text)
  else if combobox3.Visible then
    value[1]:=quotedstr(combobox3.Text)
  else
  begin
    if dtp1.Visible then
      value[1]:=quotedstr(datetostr(dtp1.DateTime));
    //showmessage(value[1]);
  end;
  if edit2.Visible then
    value[2]:=quotedstr(edit2.Text)
  else if combobox6.Visible then
    value[2]:=quotedstr(combobox6.Text)
  else  if dtp2.Visible then
    value[2]:=quotedstr(datetostr(dtp2.DateTime));
  if edit3.Visible then
    value[3]:=quotedstr(edit3.Text)
  else if combobox9.Visible then
    value[3]:=quotedstr(combobox9.Text)
  else  if dtp3.Visible then
    value[3]:=datetostr(dtp3.DateTime);
  if edit4.Visible then
    value[4]:=quotedstr(edit4.Text)
  else if combobox12.Visible then
    value[4]:=quotedstr(combobox12.Text)
  else  if dtp4.Visible then
    value[4]:=datetostr(dtp4.DateTime);
  if edit5.Visible then
    value[5]:=quotedstr(edit5.Text)
  else if combobox15.visible then
    value[5]:=quotedstr(combobox15.Text)
  else  if dtp5.Visible then
    value[5]:=datetostr(dtp5.DateTime); }
if edit1.Visible then
    value[1]:=edit1.Text
  else if combobox3.Visible then
    value[1]:=combobox3.Text
  else
  begin
    if dtp1.Visible then
      value[1]:=datetostr(dtp1.DateTime);
    //showmessage(value[1]);
  end;
  if edit2.Visible then
    value[2]:=edit2.Text