studentinfosearchunit.pas

学费管理系统,学校使用

unit studentinfosearchUnit;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, cxStyles, cxCustomData, cxGraphics, cxFilter, cxData,
  cxDataStorage, cxEdit, DB, cxDBData, ADODB, StdCtrls, Buttons,
  cxGridLevel, cxClasses, cxControls, cxGridCustomView,
  cxGridCustomTableView, cxGridTableView, cxGridDBTableView, cxGrid,ComObj,
  ExtCtrls;

type
  Tstudentinfosearchform = class(TForm)
    Panel1: TPanel;
    cxGrid1DBTableView1: TcxGridDBTableView;
    cxGrid1Level1: TcxGridLevel;
    cxGrid1: TcxGrid;
    search_bitbtn: TBitBtn;
    clear_bitbtn: TBitBtn;
    BitBtn3: TBitBtn;
    list_adoquery: TADOQuery;
    DataSource1: TDataSource;
    cxGrid1DBTableView1Id: TcxGridDBColumn;
    cxGrid1DBTableView1ChargeYearID: TcxGridDBColumn;
    cxGrid1DBTableView1StudentName: TcxGridDBColumn;
    cxGrid1DBTableView1Code: TcxGridDBColumn;
    cxGrid1DBTableView1length: TcxGridDBColumn;
    cxGrid1DBTableView1ClassCode: TcxGridDBColumn;
    cxGrid1DBTableView1Address: TcxGridDBColumn;
    cxGrid1DBTableView1Source: TcxGridDBColumn;
    cxGrid1DBTableView1Tutor: TcxGridDBColumn;
    cxGrid1DBTableView1Circs: TcxGridDBColumn;
    cxGrid1DBTableView1Memo: TcxGridDBColumn;
    cxGrid1DBTableView1department: TcxGridDBColumn;
    cxGrid1DBTableView1sexname: TcxGridDBColumn;
    cxGrid1DBTableView1tuition: TcxGridDBColumn;
    cxGrid1DBTableView1house: TcxGridDBColumn;
    cxGrid1DBTableView1book: TcxGridDBColumn;
    cxGrid1DBTableView1insurance: TcxGridDBColumn;
    cxGrid1DBTableView1live: TcxGridDBColumn;
    cxGrid1DBTableView1pledge: TcxGridDBColumn;
    cxGrid1DBTableView1sumfact: TcxGridDBColumn;
    Panel2: TPanel;
    cxGrid2: TcxGrid;
    cxGridDBTableView1: TcxGridDBTableView;
    cxGridLevel1: TcxGridLevel;
    cxGrid3: TcxGrid;
    cxGridDBTableView2: TcxGridDBTableView;
    cxGridLevel2: TcxGridLevel;
    cxGrid4: TcxGrid;
    cxGridDBTableView3: TcxGridDBTableView;
    cxGridLevel3: TcxGridLevel;
    ADOQuery1: TADOQuery;
    ADOQuery2: TADOQuery;
    ADOQuery3: TADOQuery;
    DataSource2: TDataSource;
    DataSource3: TDataSource;
    DataSource4: TDataSource;
    cxGrid1DBTableView1DBColumn1: TcxGridDBColumn;
    cxGridDBTableView3DecreaseName: TcxGridDBColumn;
    cxGridDBTableView3DecreaseNum: TcxGridDBColumn;
    cxGridDBTableView3DecreaseCode: TcxGridDBColumn;
    cxGridDBTableView2DBColumn: TcxGridDBColumn;
    cxGridDBTableView2returnnum: TcxGridDBColumn;
    cxGridDBTableView2returnnum_1: TcxGridDBColumn;
    cxGridDBTableView2returncode: TcxGridDBColumn;
    cxGridDBTableView1DBColumn: TcxGridDBColumn;
    cxGridDBTableView1cash: TcxGridDBColumn;
    cxGrid1DBTableView1DBColumn2: TcxGridDBColumn;
    cxGrid1DBTableView1DBColumn3: TcxGridDBColumn;
    BitBtn1: TBitBtn;
    procedure FormShow(Sender: TObject);
    procedure DataSource1DataChange(Sender: TObject; Field: TField);
    procedure search_bitbtnClick(Sender: TObject);
    procedure clear_bitbtnClick(Sender: TObject);
    procedure BitBtn3Click(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure BitBtn1Click(Sender: TObject);
  private
    { Private declarations }
    SqlText:String;
    procedure CloseWindow(var Msg:TMessage);message wm_user;
  public
    { Public declarations }
  end;

var
  studentinfosearchform: Tstudentinfosearchform;

implementation

uses MainformUnit, DMUnit;

{$R *.dfm}

procedure Tstudentinfosearchform.FormShow(Sender: TObject);
begin
  SqlText:=list_adoquery.SQL.Text ;
  search_bitbtn.Click;
end;

procedure Tstudentinfosearchform.DataSource1DataChange(Sender: TObject;
  Field: TField);
begin
  clear_bitbtn.Enabled :=not list_adoquery.IsEmpty ;
  bitbtn1.Enabled :=not list_adoquery.IsEmpty ;
  adoquery1.Close;
  adoquery2.Close;
  adoquery3.Close;
  if list_adoquery.IsEmpty then exit;
  with adoquery1 do
  begin
    close;
    sql.Clear;
    sql.add('select * from decrease');
    sql.add('where studentid=:studentid ');
    sql.add('and chargerangeid=:chargerangeid and decreasenum>0');
    Parameters.ParamByName('studentid').Value :=list_adoquery.FieldValues['id'];
    Parameters.ParamByName('chargerangeid').Value :=list_adoquery.FieldValues['chargerangeid'];
    open;
  end;
  with adoquery2 do
  begin
    close;
    sql.Clear;
    sql.add('select case chargeitemid when 1 then ''学费'' when 2 then ''住宿'' when 3 then ''书费''');
    sql.add('when 4 then ''保险'' when 5 then ''生活用品'' when 6 then ''保证金''end as ''项目'',returnnum,returnname,returncode');
    sql.add('from returndetail');
    sql.add('where returnnum>0');
    sql.add('and studentid=:studentid ');
    sql.add('and chargerangeid=:chargerangeid');
    sql.add('order by chargeitemid');
    Parameters.ParamByName('studentid').Value :=list_adoquery.FieldValues['id'];
    Parameters.ParamByName('chargerangeid').Value :=list_adoquery.FieldValues['chargerangeid'];
    open;
  end;
  with adoquery3 do
  begin
    close;
    sql.Clear;

    sql.add('select case chargeitemid when 1 then ''学费'' when 2 then ''住宿'' when 3 then ''书费''');
    sql.add('when 4 then ''保险'' when 5 then ''生活用品'' when 6 then ''保证金''end as ''项目'',(quantum-derate-(fact-returnnum)) as cash ');
    sql.add('from income ');
    sql.add('where (quantum-derate-(fact-returnnum))>0');
    sql.add('and studentid=:studentid ');
    sql.add('and chargerangeid=:chargerangeid');
    sql.add('order by chargeitemid ');
    Parameters.ParamByName('studentid').Value :=list_adoquery.FieldValues['id'];
    Parameters.ParamByName('chargerangeid').Value :=list_adoquery.FieldValues['chargerangeid'];
    open;
  end;
end;

procedure Tstudentinfosearchform.search_bitbtnClick(Sender: TObject);
begin
  list_adoquery.close;
  list_adoquery.Open;
end;

procedure Tstudentinfosearchform.clear_bitbtnClick(Sender: TObject);
begin
  list_adoquery.Close;
  adoquery1.Close;
  adoquery2.Close;
  adoquery3.Close;
  bitbtn1.Enabled :=false;
  clear_bitbtn.Enabled :=false;
end;

procedure Tstudentinfosearchform.CloseWindow(var Msg: TMessage);
begin
  FreeAndNil(studentinfosearchform);
end;

procedure Tstudentinfosearchform.BitBtn3Click(Sender: TObject);
begin
  self.Close;
end;

procedure Tstudentinfosearchform.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  action:=cafree;
  postmessage(handle,wm_user,0,0);
end;

procedure Tstudentinfosearchform.BitBtn1Click(Sender: TObject);
var
  row,i:integer;
  sheet,ExcelApplication,range:variant;
  tmpstr:String;
  tmpmoney:single;
begin
  tmpstr:=ExtractFilePath(application.ExeName);
  ExcelApplication:=CreateOLEObject('Excel.Application');
  ExcelApplication.visible:=false;
  ExcelApplication.workbooks.add(null);
  ExcelApplication.workbooks[1].sheets[1].name:='学生信息';
  sheet:=ExcelApplication.workbooks[1].sheets['学生信息'];
  row:=1;
  for i :=0  to cxGrid1DBTableView1.ColumnCount-1 do
  begin
    sheet.Cells.Item[row,I+1]:=cxGrid1DBTableView1.Columns[i].Caption ;
  end;
  inc(row);
  with list_adoquery do
  begin
    First ;
    while not Eof  do
    begin

      sheet.Cells.Item[row,1]:=fieldbyname('Id').AsString ;
      sheet.Cells.Item[row,2]:=fieldbyname('ChargeYearID').AsString ;
      sheet.Cells.Item[row,3]:=fieldbyname('chargerangeid').AsString ;
      sheet.Cells.Item[row,4]:=fieldbyname('department').AsString ;
      sheet.Cells.Item[row,5]:=fieldbyname('length').AsString ;
      sheet.Cells.Item[row,6]:=fieldbyname('StudentName').AsString ;
      sheet.Cells.Item[row,7]:=fieldbyname('Code').AsString ;
      sheet.Cells.Item[row,8]:=fieldbyname('sexname').AsString ;
      sheet.Cells.Item[row,9]:=fieldbyname('ClassCode').AsString ;
      sheet.Cells.Item[row,10]:=fieldbyname('Address').AsString ;
      sheet.Cells.Item[row,11]:=fieldbyname('Source').AsString ;
      sheet.Cells.Item[row,12]:=fieldbyname('Tutor').AsString ;
      sheet.Cells.Item[row,13]:=fieldbyname('Circs').AsString ;
      sheet.Cells.Item[row,14]:=fieldbyname('Memo').AsString ;
      sheet.Cells.Item[row,15]:=fieldbyname('tuition').AsString ;
      sheet.Cells.Item[row,16]:=fieldbyname('house').AsString ;
      sheet.Cells.Item[row,17]:=fieldbyname('book').AsString ;
      sheet.Cells.Item[row,18]:=fieldbyname('insurance').AsString ;
      sheet.Cells.Item[row,19]:=fieldbyname('live').AsString ;
      sheet.Cells.Item[row,20]:=fieldbyname('pledge').AsString ;
      sheet.Cells.Item[row,21]:=fieldbyname('sumfact').AsString ;
      sheet.Cells.Item[row,22]:=fieldbyname('sumderate').AsString ;
      sheet.Cells.Item[row,23]:=fieldbyname('sumreturnnum').AsString ;
      next;
      inc(row);
    end;
  end;
  ExcelApplication.visible:=true;
end;

end.