main.pas

自己做的用delphi开发的学生成绩管理系统。

unit main;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ExtCtrls,jpeg, ComCtrls, Menus, DB, ADODB, Grids, DBGrids,
  DBGridEh, OleServer, Excel2000;


type
  Tfrmmain = class(TForm)
    Image1: TImage;
    StatusBar1: TStatusBar;
    MainMenu1: TMainMenu;
    Timer1: TTimer;
    N1: TMenuItem;
    E1: TMenuItem;
    ADOConnection1: TADOConnection;
    N2: TMenuItem;
    N3: TMenuItem;
    N4: TMenuItem;
    N5: TMenuItem;
    N6: TMenuItem;
    N7: TMenuItem;
    N8: TMenuItem;
    A1: TMenuItem;
    ADOTable1: TADOTable;
    DataSource1: TDataSource;
    ADOQuery1: TADOQuery;
    C1: TMenuItem;
    H1: TMenuItem;
    DataSource2: TDataSource;
    Excel1: TMenuItem;
    SaveDialog1: TSaveDialog;
    N9: TMenuItem;
    procedure FormCreate(Sender: TObject);
    procedure Timer1Timer(Sender: TObject);
    procedure E1Click(Sender: TObject);
    procedure N3Click(Sender: TObject);
    procedure ADOTable1BeforePost(DataSet: TDataSet);
    procedure N4Click(Sender: TObject);
    procedure N5Click(Sender: TObject);
    procedure FormKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure N7Click(Sender: TObject);
    procedure C1Click(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure ADOTable1NewRecord(DataSet: TDataSet);
    procedure ADOTable1AfterPost(DataSet: TDataSet);
    procedure Excel1Click(Sender: TObject);
    procedure H1Click(Sender: TObject);
    procedure A1Click(Sender: TObject);
  private
    { Private declarations }
    dbgrid1:TDbgridEh;
    sorting:boolean;
    Desc:boolean;
    procedure WriteExcel(AdsData: TDataSet; sName, Title: string;start:integer);
    procedure UpdateData(Sender: TObject;var Text: String; var Value: Variant; var UseText, Handled: Boolean);
    function passDataBase(s:string):string;
    procedure TitleBtnClick(Sender: TObject; ACol: Integer;Column: TColumnEh);
  public
    { Public declarations }
    currentdir:string;
  end;

var
  frmmain: Tfrmmain;

implementation

uses SelInputScore, QueryScore;

{$R *.dfm}

function Tfrmmain.passDataBase(s:string):string;
begin
  Result:=s;
end;


procedure Tfrmmain.FormCreate(Sender: TObject);
var strCon:string;
begin
  dbgrid1:=TDbgridEh.Create(self);
  dbgrid1.Parent:=self;
  dbgrid1.Name:='dbgrid1';
  dbgrid1.Visible:=false;
  dbgrid1.DataSource:=self.DataSource1;
  dbgrid1.Flat:=true;
  dbgrid1.DrawMemoText:=true;
  dbgrid1.OnTitleBtnClick:=TitleBtnClick;

  self.WindowState:=wsMaximized;
  currentdir:=extractfilepath(application.exename);
  if copy(currentdir,length(currentdir),1)='\' then
     currentdir:=copy(currentdir,1,length(currentdir)-1);
  image1.Align:=alclient;
  statusbar1.Panels[0].Text:=datetostr(now)+'  '+timetostr(time);
  if not fileexists(currentdir+'\CJGL.mdb') then
  begin
    showmessage('没有数据库文件'+currentdir+'\CJGL.mdb');
    application.Terminate;
  end;
  self.ADOConnection1.Connected:=false;
  strCon:='Provider=Microsoft.Jet.OLEDB.4.0;'+
          'Data Source='+currentdir+'\CJGL.mdb;'+
          'Persist Security Info=False;'+
          'Jet OLEDB:Database Password='+PassDataBase('doctor');
  self.ADOConnection1.ConnectionString:=strCon;
  try
    self.ADOConnection1.Connected:=true;
  except
    showmessage('数据库文件'+currentdir+'\CJGL.mdb');
    application.Terminate;
  end;
  dbgrid1.Align:=alClient;
  AdoTable1.Connection:=self.ADOConnection1;
  adoQuery1.Connection:=self.ADOConnection1;
end;

procedure Tfrmmain.Timer1Timer(Sender: TObject);
begin
  statusbar1.Panels[0].Text:=datetostr(now)+'  '+timetostr(time);
end;

procedure Tfrmmain.E1Click(Sender: TObject);
begin
  close;
end;

procedure Tfrmmain.N3Click(Sender: TObject);
var i:Integer;
begin
  if (datasource1.DataSet.State=dsEdit)or(datasource1.DataSet.State=dsInsert) then
  begin
    showmessage('正在编辑数据，请按Esc键取消或按Ctrl+Enter键确定');
    exit;
  end;
  datasource1.DataSet.Close;
  try
    adotable1.TableName:='课程目录';
    adotable1.Filtered:=false;
    adotable1.Active:=true;
    adotable1.FieldByName('id').Visible:=false;
    datasource1.DataSet:=self.ADOTable1;
    dbgrid1.Visible:=true;
    dbgrid1.ReadOnly:=False;
    dbgrid1.FooterRowCount:=0;
    self.StatusBar1.Panels[1].Text:='课程输入'
  except
    showmessage('error');
  end;
  for i:=0 to dbgrid1.Columns.Count-1 do
    dbgrid1.Columns[i].Title.TitleButton:=true;

end;

procedure Tfrmmain.ADOTable1BeforePost(DataSet: TDataSet);
var Query:TAdoQuery;
    sql:string;
    kcdm,kcmc,bjmc,xh,xm:string;
    sclass:string;
begin
  if AnsiCompareText(adotable1.TableName,'课程目录')=0 then
  begin
    kcdm:=adotable1.FieldByName('课程代码').AsString;
    kcmc:=adotable1.FieldByName('课程名称').AsString;
    if length(kcdm)=0 then
    begin
      showmessage('请输入课程代码');
      abort;
    end;
    Query:=TadoQuery.Create(self);
    Query.Connection:=self.ADOConnection1;
    sql:='select count(*) from 课程目录 where 1=1 ';
    if (length(kcdm)>0) then
      sql:=sql+' and 课程代码='+''''+kcdm+'''';
    if (length(kcmc)>0) then
      sql:=sql+' and 课程名称='+''''+kcmc+'''';
    query.SQL.Text:=sql;
    query.Active:=true;
    query.First;
    if Query.Fields[0].AsInteger>0 then
    begin
      if adotable1.State=dsInsert then
      begin
        showmessage('课程代码和课程名称不可重复');
        Query.Free;
        abort;
      end
      else if adotable1.State=dsEdit then
      begin
        Query.Free;
        adotable1.Cancel;
        abort;
      end;
    end;
    Query.Free;
  end;

  if AnsiCompareText(adotable1.TableName,'学生名单')=0 then
  begin
    bjmc:=adotable1.FieldByName('班级名称').AsString;
    xh:=adotable1.FieldByName('学号').AsString;
    xm:=adotable1.FieldByName('姓名').AsString;
    if length(bjmc)=0 then
    begin
      showmessage('请输入班级名称');
      abort;
    end;
    if length(xh)=0 then
    begin
      showmessage('请输入学号');
      abort;
    end;
    Query:=TadoQuery.Create(self);
    Query.Connection:=self.ADOConnection1;
    sql:='select count(*) from 学生名单 where 1=1 ';
    sql:=sql+' and 班级名称='+''''+bjmc+'''';
    sql:=sql+' and 学号='+''''+xh+'''';
    query.SQL.Text:=sql;
    query.Active:=true;
    query.First;
    if Query.Fields[0].AsInteger>0 then
    begin
      if adotable1.State=dsInsert then
      begin
        showmessage('班级名称和学号不可重复');
        Query.Free;
        abort;
      end
      else if adotable1.State=dsEdit then
      begin
        Query.Free;
        adotable1.Cancel;
        abort;
      end;
    end;
    Query.Free;
  end;

  if AnsiCompareText(adotable1.TableName,'学生成绩')=0 then
  begin
    if length(dataset.FieldByName('成绩').AsString)=0 then
      dataset.FieldByName('成绩').AsFloat:=0;
    if length(dataset.FieldByName('班级名称').AsString)=0 then
    begin
      showmessage('班级不能为空');
      abort;
    end;
    if length(dataset.FieldByName('学号').AsString)=0 then
    begin
      showmessage('学号不能为空');
      abort;
    end;
    if length(dataset.FieldByName('课程代码').AsString)=0 then
    begin
      showmessage('课程代码不能为空');
      abort;
    end;
    Query:=TAdoQuery.Create(self);
    Query.Connection:=self.ADOConnection1;
    sclass:=dataset.fieldbyname('班级名称').AsString;
    xh:=dataset.fieldbyname('学号').AsString;
    xm:=dataset.fieldbyname('姓名').AsString;
    kcdm:=dataset.fieldbyname('课程代码').AsString;
    Query.SQL.Text:='select count(*) from 学生成绩 where 1=1';
    if length(sclass)>0 then
      Query.SQL.Text:=Query.SQL.Text+' and 班级名称='+''''+sClass+'''';
    if length(xh)>0 then
      Query.SQL.Text:=Query.SQL.Text+' and 学号='+''''+xh+'''';
    if length(xm)>0 then
      Query.SQL.Text:=Query.SQL.Text+' and 姓名='+''''+xm+'''';
    if length(kcdm)>0 then
      Query.SQL.Text:=Query.SQL.Text+' and 课程代码='+''''+kcdm+'''';
    query.Active:=true;
    Query.First;
    if dataset.State=dsInsert then
    begin
      if Query.Fields[0].AsInteger>0 then
      begin
        showmessage('重复输入');
        Query.Free;
        abort;
      end;
    end;
  {
    else if dataset.State=dsEdit then
    begin
      if Query.Fields[0].AsInteger>0 then
      begin
        Query.free;
        dataset.Cancel;
        abort;
      end;
    end;
  }
    Query.Free;
  end;

end;

procedure Tfrmmain.N4Click(Sender: TObject);
var i:integer;
begin
  if (datasource1.DataSet.State=dsEdit)or(datasource1.DataSet.State=dsInsert) then
  begin
    showmessage('正在编辑数据，请按Esc键取消或按Ctrl+Enter键确定');
    exit;
  end;
  datasource1.DataSet.Close;
  try
    adoquery1.Close;
    if not sorting then
      adoquery1.sql.Text:='select * from 学生名单';
    adoquery1.Active:=true;
    adoquery1.FieldByName('id').Visible:=false;
    datasource1.DataSet:=self.ADOquery1;
{
    adotable1.TableName:='学生名单';
    adotable1.Filtered:=false;
    adotable1.Active:=true;
    adotable1.FieldByName('id').Visible:=false;
    datasource1.DataSet:=self.ADOTable1;
}
    dbgrid1.Visible:=true;
    dbgrid1.ReadOnly:=False;
    dbgrid1.FooterRowCount:=0;
    self.StatusBar1.Panels[1].Text:='学生名单输入';
  except
    showmessage('error');
  end;
  for i:=0 to dbgrid1.Columns.Count-1 do
    dbgrid1.Columns[i].Title.TitleButton:=true;
end;

procedure Tfrmmain.N5Click(Sender: TObject);
var sClass,KCDM,KCMC,sql:string;
    i:integer;
    QueryXS,QueryKC:TAdoQuery;
begin
  if (datasource1.DataSet.State=dsEdit)or(datasource1.DataSet.State=dsInsert) then
  begin
    showmessage('正在编辑数据，请按Esc键取消或按Ctrl+Enter键确定');
    exit;
  end;
  if frmSelInputScore.showmodal=mrOk then
  begin
    datasource1.DataSet.Close;
    sclass:=frmSelInputScore.cmbClass.Text;
    kcdm:=frmSelInputScore.cmbKCDM.Text;
    kcmc:=frmSelInputScore.cmbKCMC.Text;
    adoquery1.Active:=false;
    adoTable1.Active:=false;
    adoTable1.TableName:='学生成绩';
    adoTable1.Active:=true;
    sql:='班级名称='+''''+sClass+'''';
    if length(KCDM)>0 then
      sql:=sql+' and 课程代码='+''''+KCDM+'''';
    if length(KCMC)>0 then
      sql:=sql+' and 课程名称='+''''+KCMC+'''';
    adoTable1.Filter:=sql;
    adoTable1.Filtered:=true;
    datasource1.DataSet:=adoTable1;
    adoTable1.FieldByName('id').Visible:=false;
    if length(sClass)>0 then
      adoTable1.FieldByName('班级名称').ReadOnly:=true;
    if length(KCDM)>0 then
      adoTable1.FieldByName('课程代码').ReadOnly:=true;
    if length(KCMC)>0 then
      adoTable1.FieldByName('课程名称').ReadOnly:=true;

    QueryXS:=TADoQuery.Create(self);
    QueryXS.Connection:=self.ADOConnection1;
    QueryKC:=TADoQuery.Create(self);
    QueryKC.Connection:=self.ADOConnection1;
    QueryXS.Active:=false;
    QueryKC.Active:=false;
    if length(sClass)=0 then
      QueryXS.SQL.Text:='select * from 学生名单'
    else
      QueryXS.SQL.Text:='select * from 学生名单 where 班级名称='+''''+sClass+'''';
    QueryXS.Active:=true;
    QueryKC.SQL.Text:='select * from 课程目录';
    QueryKC.Active:=true;
    for i:=0 to dbgrid1.Columns.Count-1 do
    begin
      dbgrid1.Columns[i].AutoDropDown:=true;
      dbgrid1.Columns[i].OnUpdateData:=UpdateData;
    end;

    if not QueryKC.IsEmpty then
    begin
      for i:=0 to dbgrid1.Columns.Count-1 do
      if AnsiCompareText(dbgrid1.Columns.Items[i].FieldName,'课程代码')=0 then
      begin
        dbgrid1.Columns.Items[i].PickList.Clear;
        QueryKC.First;
        while not QueryKC.Eof do
        begin
          dbgrid1.Columns.Items[i].PickList.Add(QueryKC.FieldByName('课程代码').AsString);
          QueryKC.Next;
        end;
      end
      else if AnsiCompareText(dbgrid1.Columns.Items[i].FieldName,'课程名称')=0 then
      begin
        dbgrid1.Columns.Items[i].PickList.Clear;
        QueryKC.First;
        while not QueryKC.Eof do
        begin
          dbgrid1.Columns.Items[i].PickList.Add(QueryKC.FieldByName('课程名称').AsString);
          QueryKC.Next;
        end;
      end;
    end;

    if not QueryXS.IsEmpty then
    begin
      for i:=0 to dbgrid1.Columns.Count-1 do
      if AnsiCompareText(dbgrid1.Columns.Items[i].FieldName,'班级名称')=0 then
      begin
        dbgrid1.Columns.Items[i].Width:=150;
        if length(sClass)=0 then
        begin
          dbgrid1.Columns.Items[i].PickList.Clear;