search.pas

绝对精品 预算管理系统 本人花钱购买来的源代 人民大学出版社会出版的教程

unit search;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, Menus, StdCtrls, ComCtrls, DB, DBTables, Grids, DBGrids,ComObj,
  Excel2000, OleServer, ADODB;

type
  Tfrmsearch = class(TForm)
    GroupBox1: TGroupBox;
    DBGrid1: TDBGrid;
    DataSource1: TDataSource;
    Database1: TDatabase;
    Tablepzlb: TTable;
    Tablegkglbm: TTable;
    Tablefygsbm: TTable;
    Tableyskmlb: TTable;
    Tableyskm: TTable;
    Labname: TLabel;
    Labpass: TLabel;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label5: TLabel;
    Label4: TLabel;
    Label7: TLabel;
    Label8: TLabel;
    Label11: TLabel;
    Label12: TLabel;
    dtfsrqqs: TDateTimePicker;
    comboxglbmmc: TComboBox;
    comboxyslbmc: TComboBox;
    comboxgsbmmc: TComboBox;
    comboxyskmmc: TComboBox;
    Label13: TLabel;
    Label14: TLabel;
    dtfsrqjz: TDateTimePicker;
    comboxglbmdm: TComboBox;
    comboxyslbdm: TComboBox;
    comboxgsbmdm: TComboBox;
    comboxyskmdm: TComboBox;
    comboxpzhm: TComboBox;
    comboxpzlbmc: TComboBox;
    cmdsearch: TButton;
    cmdclear: TButton;
    Tablekjyw2: TTable;
    Querykjyw: TQuery;
    cmdexcel: TButton;
    cmdquit: TButton;
    procedure FormCreate(Sender: TObject);
    procedure comboxglbmdmChange(Sender: TObject);
    procedure comboxglbmmcChange(Sender: TObject);
    procedure comboxgsbmdmChange(Sender: TObject);
    procedure comboxgsbmmcChange(Sender: TObject);
    procedure comboxyslbdmChange(Sender: TObject);
    procedure comboxyslbmcChange(Sender: TObject);
    procedure comboxyskmdmChange(Sender: TObject);
    procedure comboxyskmmcChange(Sender: TObject);
    procedure cmdsearchClick(Sender: TObject);
    procedure cmdclearClick(Sender: TObject);
    procedure cmdexcelClick(Sender: TObject);
    procedure DBGrid1DblClick(Sender: TObject);
    procedure cmdquitClick(Sender: TObject);
  private
    { Private declarations }

  public
    { Public declarations }
     m_pzhm:string;
  end;

var
  frmsearch: Tfrmsearch;
  username:string;
  userpwd:string;
  userqx:string;

implementation

uses Unit2, yskmlb, yskm, pzlb, fygsbm, gkglbm, kjyw, login, splash,
  display, dbfxz;
{$R *.dfm}

procedure Tfrmsearch.FormCreate(Sender: TObject);
begin
//设置凭证号码对应的组合框的初始值
while not tablekjyw2.Eof  do
   begin
     comboxpzhm.AddItem(tablekjyw2.FieldValues['pzhm'],sender);
     tablekjyw2.MoveBy(1);
   end;
//设置凭证类别对应的组合框的初始值
while not tablepzlb.Eof  do
   begin
     comboxpzlbmc.AddItem(tablepzlb.FieldValues['pzlbmc'],sender);
     tablepzlb.MoveBy(1);
   end;
while not tablegkglbm.Eof  do
   begin
     comboxglbmdm.AddItem(tablegkglbm.FieldValues['dm'],sender);
     comboxglbmmc.AddItem(tablegkglbm.FieldValues['glbmmc'],sender);
     tablegkglbm.MoveBy(1);
   end;
while not tablefygsbm.Eof  do
   begin
     comboxgsbmdm.AddItem(tablefygsbm.FieldValues['dm'],sender);
     comboxgsbmmc.AddItem(tablefygsbm.FieldValues['gsbmmc'],sender);
     tablefygsbm.MoveBy(1);
   end;
while not tableyskmlb.Eof  do
   begin
     comboxyslbdm.AddItem(tableyskmlb.FieldValues['dm'],sender);
     comboxyslbmc.AddItem(tableyskmlb.FieldValues['yslbmc'],sender);
     tableyskmlb.MoveBy(1);
   end;
//设置预算科目对应的组合框的初始值
while not tableyskm.Eof  do
   begin
     comboxyskmdm.AddItem(tableyskm.FieldValues['dm'],sender);
     comboxyskmmc.AddItem(tableyskm.FieldValues['yskmmc'],sender);
     tableyskm.MoveBy(1);
   end;
//设置凭证发生截至日期的初始值
dtfsrqjz.Date:=date();
end;

procedure Tfrmsearch.comboxglbmdmChange(Sender: TObject);
begin
//判断用户输入或选择的部门名称是否为空
if comboxglbmdm.Text<>'' then
   begin
     //设置tablegkglbm的filter属性，已找到与该部门代码对应的部门名称
     tablegkglbm.Filter:='dm='''+comboxglbmdm.Text+'''';
     if not tablegkglbm.eof then
       //与comboxglbmmc组合框中显示与该部门代码对应的部门名称
       comboxglbmmc.Text:=tablegkglbm.FieldValues['glbmmc'];
   end;
//重新设置tablegkglbm的filter属性，使它返回所有记录
tablegkglbm.Filter:='dm<>'''''
end;

procedure Tfrmsearch.comboxglbmmcChange(Sender: TObject);
begin
if comboxglbmmc.Text<>'' then
   begin
     tablegkglbm.Filter:='glbmmc='''+comboxglbmmc.Text+'''';
     if not tablegkglbm.eof then
       comboxglbmdm.Text:=tablegkglbm.FieldValues['dm'];
   end;
tablegkglbm.Filter:='glbmmc<>'''''
end;

procedure Tfrmsearch.comboxgsbmdmChange(Sender: TObject);
begin
if comboxgsbmdm.Text<>'' then
   begin
     tablefygsbm.Filter:='dm='''+comboxgsbmdm.Text+'''';
     if not tablefygsbm.eof then
       comboxgsbmmc.Text:=tablefygsbm.FieldValues['gsbmmc'];
   end;
tablefygsbm.Filter:='dm<>'''''
end;

procedure Tfrmsearch.comboxgsbmmcChange(Sender: TObject);
begin
if comboxgsbmmc.Text<>'' then
   begin
     tablefygsbm.Filter:='gsbmmc='''+comboxgsbmmc.Text+'''';
     if not tablefygsbm.eof then
       comboxgsbmdm.Text:=tablefygsbm.FieldValues['dm'];
   end;
tablefygsbm.Filter:='gsbmmc<>'''''
end;

procedure Tfrmsearch.comboxyslbdmChange(Sender: TObject);
begin
if comboxyslbdm.Text<>'' then
   begin
     tableyskmlb.Filter:='dm='''+comboxyslbdm.Text+'''';
     if not tableyskmlb.eof then
       comboxyslbmc.Text:=tableyskmlb.FieldValues['yslbmc'];
   end;
tableyskmlb.Filter:='dm<>'''''
end;

procedure Tfrmsearch.comboxyslbmcChange(Sender: TObject);
begin
if comboxyslbmc.Text<>'' then
   begin
     tableyskmlb.Filter:='yslbmc='''+comboxyslbmc.Text+'''';
     if not tableyskmlb.eof then
       comboxyslbdm.Text:=tableyskmlb.FieldValues['dm'];
   end;
tableyskmlb.Filter:='dm<>'''''
end;
procedure Tfrmsearch.comboxyskmdmChange(Sender: TObject);
begin
if comboxyskmdm.Text<>'' then
   begin
     tableyskm.Filter:='dm='''+comboxyskmdm.Text+'''';
     if not tableyskm.eof then
       comboxyslbmc.Text:=tableyskm.FieldValues['yskmmc'];
   end;
tableyskm.Filter:='dm<>'''''
end;

procedure Tfrmsearch.comboxyskmmcChange(Sender: TObject);
begin
if comboxyslbmc.Text<>'' then
   begin
     tableyskm.Filter:='yslbmc='''+comboxyslbmc.Text+'''';
     if not tableyskmlb.eof then
       comboxyslbdm.Text:=tableyskm.FieldValues['dm'];
   end;
tableyskm.Filter:='yskmmc<>'''''
end;

procedure Tfrmsearch.cmdsearchClick(Sender: TObject);
var
  sqlstr:string;
begin
  //开始组合sql查询字符串
  if comboxpzhm.Text<>'' then
     sqlstr:='pzhm='''+comboxpzhm.Text+'''';
  if comboxpzlbmc.Text<>'' then
     if sqlstr<>'' then
       sqlstr:=sqlstr+' and pzlbmc='''+comboxpzlbmc.Text+''''
     else
       sqlstr:='pzlbmc='''+comboxpzlbmc.Text+'''';
  if comboxglbmdm.Text<>'' then
     if sqlstr<>'' then
       sqlstr:=sqlstr+' and glbmdm='''+comboxglbmdm.Text+''''
     else
       sqlstr:='glbmdm='''+comboxglbmdm.Text+'''';
  if comboxgsbmdm.Text<>'' then
     if sqlstr<>'' then
       sqlstr:=sqlstr+' and gsbmdm='''+comboxgsbmdm.Text+''''
     else
       sqlstr:='gsbmdm='''+comboxgsbmdm.Text+'''';
  if comboxyslbdm.Text<>'' then
     if sqlstr<>'' then
       sqlstr:=sqlstr+' and yslbdm='''+comboxyslbdm.Text+''''
     else
       sqlstr:='yslbdm='''+comboxyslbdm.Text+'''';
  if comboxyskmdm.Text<>'' then
     if sqlstr<>'' then
       sqlstr:=sqlstr+' and yskmdm='''+comboxyskmdm.Text+''''
     else
       sqlstr:='yskmdm='''+comboxyskmdm.Text+'''';
  if sqlstr<>'' then
      sqlstr:=sqlstr+' and fsrq>='+datetostr(dtfsrqqs.date)+' and fsrq<='+datetostr(dtfsrqjz.date)
  else
      sqlstr:=' fsrq>='+datetostr(dtfsrqqs.date)+' and fsrq<='+datetostr(dtfsrqjz.date);
  //设置querykjyw的filter属性，已显示符合查询条件的记录
  querykjyw.filter:=sqlstr;
end;

procedure Tfrmsearch.cmdclearClick(Sender: TObject);
begin
  //设置凭证发生起始日期的默认值
  dtfsrqqs.Date:=strtodate('1980-1-1');
  //设置凭证发生截至日期的默认值
  dtfsrqjz.Date:=date();
  comboxpzhm.Text:='';
  comboxpzlbmc.Text:='';
  comboxglbmdm.Text:='';
  comboxglbmmc.Text:='';
  comboxgsbmdm.Text:='';
  comboxgsbmmc.Text:='';
  comboxyslbdm.Text:='';
  comboxyslbmc.Text:='';
  comboxyskmdm.Text:='';
  comboxyskmmc.Text:='';
  //设置querykjyw的Filter属性，在DataGrid1控件中显示kjyw表中的所有记录
  querykjyw.Filter:='pzhm<>''''';
end;

procedure Tfrmsearch.cmdexcelClick(Sender: TObject);
var
  iCount, jCount: Integer;
  XLApp: Variant;
  Sheet: Variant;
  I: Integer;
begin
  Screen.Cursor := crHourGlass;
  if not VarIsEmpty(XLApp) then
  begin
    XLApp.DisplayAlerts := False;
    XLApp.Quit;
    VarClear(XLApp);
  end;

  try
    //建立excel对象
    XLApp := CreateOleObject('Excel.Application');
  except
    Screen.Cursor := crDefault;
    Exit;
  end;
  // '建立excel对象的工作薄对象
  XLApp.WorkBooks.Add;
  XLApp.SheetsInNewWorkbook := 1;
  XLApp.WorkBooks[1].WorkSheets[1].Name := dbgrid1.Name;
  Sheet := XLApp.Workbooks[1].WorkSheets[dbgrid1.Name];
  Sheet.Columns.AutoFit;
  if not dbgrid1.DataSource.DataSet.Active then
    begin
      Screen.Cursor := crDefault;
      Exit;
    end;
    //将当前记录位置移到第一条记录
    dbgrid1.DataSource.DataSet.first;
    //在EXCEL表中显示表格的列标题
    for iCount := 0 to dbgrid1.Columns.Count - 2 do
      Sheet.Cells[1, iCount + 1] := dbgrid1.Columns.Items[iCount+1].Title.Caption;
    jCount := 1;
    while not dbgrid1.DataSource.DataSet.Eof do
    begin
     //在excel表中的第m+3行，第i列对应的单元格中显示DBGrid1网格中第n列的值
      for iCount := 0 to dbgrid1.Columns.Count - 2 do
       //在excel表中的第jCount + 1行，第iCount + 1列对应的单元格中显示DBGrid1
       //网格中当前记录第icount列的值
       Sheet.Cells[jCount + 1, iCount + 1] := querykjyw.Fields[icount].AsString;
      inc(jCount);
      dbgrid1.DataSource.DataSet.Next;
    end;
    //显示EXCEL表格
    XlApp.Visible := True;
  Screen.Cursor := crDefault;
end;

procedure Tfrmsearch.DBGrid1DblClick(Sender: TObject);
begin
m_pzhm:=querykjyw.FieldValues['pzhm'];
//判断选定记录的凭证号码是否为空
if m_pzhm<>'' then
  //选定记录的凭证号码是否为空
  begin
   //创建"预算会计业务资料显示"窗口
   frmdisplay:=tfrmdisplay.create(application);
   //显示选定记录的凭证号码
   frmdisplay.comboxpzhm.Text:=querykjyw.FieldValues['pzhm'];
   //显示选定记录的凭证类别名称
   frmdisplay.comboxpzlbmc.Text:=querykjyw.FieldValues['pzhm'];
   frmdisplay.comboxywje.Text:=querykjyw.FieldValues['ywje'];
   frmdisplay.comboxglbmdm.Text:=querykjyw.FieldValues['glbmdm'];
   frmdisplay.comboxglbmmc.Text:=querykjyw.FieldValues['pzhm'];
   frmdisplay.comboxgsbmdm.Text:=querykjyw.FieldValues['glbmmc'];
   frmdisplay.comboxgsbmmc.Text:=querykjyw.FieldValues['gsbmmc'];
   frmdisplay.comboxyslbdm.Text:=querykjyw.FieldValues['yslbdm'];
   frmdisplay.comboxyslbmc.Text:=querykjyw.FieldValues['yslbmc'];
   frmdisplay.comboxyskmdm.Text:=querykjyw.FieldValues['yskmdm'];
   //显示选定记录的预算科目名称
   frmdisplay.comboxyskmmc.Text:=querykjyw.FieldValues['yskmmc'];
   //判断选定记录的备注是否为空
   if querykjyw.FieldValues['bz']<>'' then
     //选定记录的备注不为空，显示选定记录的备注
     frmdisplay.editbz.Text:=querykjyw.FieldValues['bz'];
   //显示"预算会计业务资料显示"窗口
   frmdisplay.ShowModal;
 end;
end;

procedure Tfrmsearch.cmdquitClick(Sender: TObject);
begin
frmsearch.Close;
end;

end.