ugradecount.~pas

来自「一.录入学生信息（预计有81个学生」· ~PAS 代码 · 共 548 行

unit uGradeCount;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ImgList, DB, ADODB, ComCtrls, StdCtrls, Grids, Aligrid, ExtCtrls,
  Mask;

type
  TfrmGradeCount = class(TForm)
    GroupBox1: TGroupBox;
    Tree: TTreeView;
    dsQuery: TADODataSet;
    ImageList1: TImageList;
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    Grid: TStringAlignGrid;
    Panel1: TPanel;
    dsStudent: TADODataSet;
    dsSubject: TADODataSet;
    dsTemp: TADODataSet;
    dsStd: TADODataSet;
    GroupBox2: TGroupBox;
    GridB: TStringAlignGrid;
    Label1: TLabel;
    cmbSubj: TComboBox;
    Label2: TLabel;
    MaskEdit1: TMaskEdit;
    MaskEdit2: TMaskEdit;
    Label3: TLabel;
    cmdFilter: TButton;
    ListSubjCode: TListBox;
    cmdDetail: TButton;
    cmdClose: TButton;
    Panel2: TPanel;
    Label4: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    Label7: TLabel;
    Function ReadyData():Boolean;
    Function ReadyStudent():Boolean;
    Function ReadySubj(NeedReLoadList:Boolean=False):Boolean;
    Function ReadyGrade():Boolean;

    Function GetStudentRow(SelID:String):Integer;
    Function GetSubjectCol(SelSubjName:String):Integer;
    Procedure CalcTotal();
    procedure CheckHonours();
    Function GetHScore(SelSubj:String):Extended;
    Procedure HideArea();
    Procedure SetJB();

    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure TreeClick(Sender: TObject);
    procedure cmdFilterClick(Sender: TObject);
    procedure cmdDetailClick(Sender: TObject);
    procedure cmdCloseClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

Function ShowGradeCount():Boolean;

implementation

uses uDM,uPub,uStudentEdit;

{$R *.dfm}

Function ShowGradeCount():Boolean;
var
  frmGradeCount: TfrmGradeCount;
begin
  Result:=False;
  frmGradeCount:=TfrmGradeCount.Create(nil);
  if Not frmGradeCount.ReadyData then
  begin
    frmGradeCount.Free;
    Exit;
  end;
  frmGradeCount.Show;
  Result:=True;
end;

Function TfrmGradeCount.ReadyData():Boolean;
var TmpSQL:String;
    OldTerm:String;
    TmpData:PNodeData;
    TmpItem,LastPar:TTreeNode;
begin
  Result:=False;
  TmpSQL:='select TTermToClass.*,TTerm.TermName,TClass.ClassName from TTermToClass,TTerm,TClass'+
    ' where TTermToClass.TermCode=TTerm.TermCode And TTermToClass.ClassCode=TClass.ClassCode'+
    ' Order by TTermToClass.TermCode,TTermToClass.ClassCode';
  dsQuery.Close;
  dsQuery.CommandText:=TmpSQL;
  Try
    dsQuery.Open;
  Except
    ShowMessage('读取本地数据文件发生错误！');
    Exit;
  End;
  dsQuery.First;
  LastPar:=nil;
  While Not dsQuery.Eof Do
  begin
    if OldTerm<>dsQuery.FieldByName('TermCode').AsString then
    begin
      new(TmpData);
      TmpItem:=Tree.Items.Add(nil,dsQuery.FieldByName('TermName').AsString);
      TmpItem.ImageIndex:=0;
      TmpItem.SelectedIndex:=0;
      TmpData.Code:=dsQuery.FieldByName('TermCode').AsString;
      TmpData.Name:=dsQuery.FieldByName('TermName').AsString;
      TmpItem.Data:=TmpData;
      OldTerm:=dsQuery.FieldByName('TermCode').AsString;
      LastPar:=TmpItem;
    end;
    new(TmpData);
    TmpItem:=Tree.Items.AddChild(LastPar,dsQuery.FieldByName('ClassName').AsString);
    TmpItem.ImageIndex:=1;
    TmpItem.SelectedIndex:=1;
    TmpData.Code:=dsQuery.FieldByName('ClassCode').AsString;
    TmpData.Name:=dsQuery.FieldByName('ClassName').AsString;
    TmpItem.Data:=TmpData;
    dsQuery.Next;
  end;
  Result:=True;
end;

Function TfrmGradeCount.ReadyStudent():Boolean;
var TmpSQL:String;
    TmpRow:Integer;
begin
  Result:=False;
  dsStudent.Close;  
  if Tree.Selected=nil then Exit;
  if Tree.Selected.Level<>1 then Exit;
  EmptyGrid(Grid);
  TmpSQL:='Select * from TStudent Where ClassCode='+''''+PNodeData(Tree.Selected.Data)^.Code+''''+
    ' Order by SName';
  dsStudent.Close;
  dsStudent.CommandText:=TmpSQL;
  Try
    dsStudent.Open;
  Except
    ShowMessage('读取本地数据文件发生错误！');
    Exit;
  End;
  Grid.ColCount:=3;
  if dsStudent.RecordCount=0 then
    Grid.RowCount:=2  
  else
    Grid.RowCount:=dsStudent.RecordCount+1;
  dsStudent.First;
  Grid.ColWidths[0]:=-1;
  TmpRow:=1;
  While Not dsStudent.Eof Do
  begin
    Grid.Cells[0,TmpRow]:=dsStudent.fieldByName('ID').AsString;
    Grid.Cells[1,TmpRow]:=dsStudent.fieldByName('SName').AsString;
    Grid.Cells[2,TmpRow]:=dsStudent.fieldByName('SNumber').AsString;
//    Grid.AlignCell[2,TmpRow]:=alRight;
    dsStudent.Next;
    TmpRow:=TmpRow+1;
  end;
  Grid.Cells[1,0]:='姓名';
  Grid.Cells[2,0]:='学号';
  Grid.AlignCol[2]:=alRight;
  Grid.AlignCell[1,0]:=alCenter;
  Grid.AlignCell[2,0]:=alCenter;
  Result:=True;
end;

Function TfrmGradeCount.ReadySubj(NeedReLoadList:Boolean=False):Boolean;
var TmpSQL:String;
    TmpRow:Integer;
begin
  Result:=False;
  if Tree.Selected=nil then
  begin
    dsSubject.Close;
    Exit;
  end;
  if Tree.Selected.Level=0 then
  begin
    dsSubject.Close;
    Exit;
  end;
  TmpSQL:='SELECT TClassToSubject.*,TSubject.SubjName from TClassToSubject,TSubject Where'+
    ' TClassToSubject.SubjCode= TSubject.SubjCode'+
    ' And TClassToSubject.TermCode='+PNodeData(Tree.Selected.Parent.Data)^.Code+
    ' And TClassToSubject.ClassCode='+''''+PNodeData(Tree.Selected.Data)^.Code+'''';
  dsSubject.Close;
  dsSubject.CommandText:=TmpSQL;
  Try
    dsSubject.Open;
  Except
    ShowMessage('打开数据文件发生错误！');
    Exit;
  End;
  dsSubject.First;
  Grid.ColCount:=Grid.ColCount+dsSubject.RecordCount+1;
  TmpRow:=3;
  if NeedReLoadList then
  begin
    cmbSubj.Items.Clear;
    ListSubjCode.Items.Clear;
    cmbSubj.Items.Add('全部');
    ListSubjCode.Items.Add('');
  end;
  While Not dsSubject.Eof Do
  begin
    Grid.Cells[TmpRow,0]:=dsSubject.fieldByName('SubjName').AsString;
    if NeedReLoadList then
    begin
      cmbSubj.Items.Add(dsSubject.fieldByName('SubjName').AsString);
      ListSubjCode.Items.Add(dsSubject.fieldByName('SubjCode').AsString);
    end;
    Grid.AlignCell[TmpRow,0]:=alCenter;
    Grid.AlignCol[TmpRow]:=alRight;
    dsSubject.Next;
    TmpRow:=TmpRow+1;
  end;
//  cmbSubj.ItemIndex:=0;
  Grid.Cells[Grid.ColCount-1,0]:='总分';
  Grid.AlignCell[Grid.ColCount-1,0]:=alCenter;
  
  Result:=True;
end;

Function TfrmGradeCount.ReadyGrade():Boolean;
var TmpSQL:String;
    TmpCol,TmpRow:Integer;
begin
  Result:=False;
  if Tree.Selected=nil then
  begin
    dsSubject.Close;
    Exit;
  end;
  if Tree.Selected.Level=0 then
  begin
    dsSubject.Close;
    Exit;
  end;

  TmpSQL:='Select TGrade.*,TSubject.SubjName from TGrade,TSubject Where '+
    ' TGrade.SubjectCode=TSubject.SubjCode  '+
    ' And TGrade.TermCode='+PNodeData(Tree.Selected.Parent.Data)^.Code+
    ' And TGrade.ClassCode='+''''+PNodeData(Tree.Selected.Data)^.Code+'''';
  dsTemp.Close;
  dsTemp.CommandText:=TmpSQL;
  Try
    dsTemp.Open;
  Except
    ShowMessage('读取本地数据文件发生错误！');
    Exit;
  End;
  dsTemp.First;
  While Not dsTemp.Eof Do
  begin
    TmpCol:=GetSubjectCol(dsTemp.fieldByName('SubjName').AsString);
    TmpRow:=GetStudentRow(dsTemp.fieldByName('StudentID').AsString);
    if (TmpRow>=0) And (TmpCol>=0) then
    begin
      Grid.Cells[TmpCol,TmpRow]:=FormatFloat('0.0',dsTemp.fieldByName('Score').AsFloat);
      Grid.AlignCell[TmpCol,TmpRow]:=alRight;
    end;
    dsTemp.Next;
  end;

  dsStd.Close;
  dsStd.CommandText:='Select THonoursStd.*,TSubject.SubjName from THonoursStd,TSubject Where THonoursStd.TermCode='+
    PNodeData(Tree.Selected.Parent.Data)^.Code+' And THonoursStd.ClassCode='+''''+PNodeData(Tree.Selected.Data)^.Code+'''';
  Try
    dsStd.Open;
  Except
    ShowMessage('读取本地数据文件发生错误！');
    Exit;  
  End;
  For TmpRow:=1 to Grid.RowCount-1 Do
  begin
    For TmpCol:=3 to Grid.ColCount-1 Do
    begin
      if Grid.Cells[TmpCol,TmpRow]='' then Grid.Cells[TmpCol,TmpRow]:='0.0';
    end;
  end;
  Result:=True;
end;

Function TfrmGradeCount.GetStudentRow(SelID:String):Integer;
var Tmploop:Integer;
begin
  For Tmploop:=1 to Grid.RowCount-1 Do
  begin
    if Grid.Cells[0,Tmploop]=SelID then
    begin
      Result:=Tmploop;
      Exit;
    end;
  end;
  Result:=-1;
end;

Function TfrmGradeCount.GetSubjectCol(SelSubjName:String):Integer;
var Tmploop:Integer;
begin
  For Tmploop:=3 to Grid.ColCount-1 Do
  begin
    if Grid.Cells[Tmploop,0]=SelSubjName then
    begin
      Result:=Tmploop;
      Exit;
    end;
  end;
  Result:=-1;
end;

Procedure TfrmGradeCount.CalcTotal();
var Tmploop,TmpRow:Integer;
    TmpField,TmpCount:Extended;
    MinRow,MaxRow:Integer;
    CountMax,CountMin:Extended;
begin
  MinRow:=-1;
  MaxRow:=-1;
  CountMax:=0;
  CountMin:=10000;
  if Grid.ColCount>4 then
  begin
    For TmpRow:=1 to Grid.RowCount-1 Do
    begin
      TmpCount:=0;
      Grid.RowFont[TmpRow].Color:=clBlack;
      For Tmploop:=3 to Grid.ColCount-2 Do
      begin
        if Grid.Cells[Tmploop,TmpRow]='' then
          TmpField:=0
        else
          TmpField:=StrToFloat(Grid.Cells[Tmploop,TmpRow]);
        if TmpField<60 then
        begin
          Grid.RowFont[TmpRow].Color:=clBlue;
        end;
        TmpCount:=TmpCount+TmpField;
      end;
      if TmpCOunt<=CountMin then
      begin
        CountMin:=tmpCOunt;
        MinRow:=TmpRow;
      end;
      if TmpCOunt>=CountMax then
      begin
        CountMax:=tmpCOunt;
        MaxRow:=TmpRow;
      end;      
      Grid.Cells[Grid.ColCount-1,TmpRow]:=FormatFloat('0.0',TmpCount);
      Grid.AlignCell[Grid.ColCount-1,TmpRow]:=alRight;
    end;
  end;
  GridB.ColCount:=Grid.ColCount;
  For Tmploop:=0 to GridB.ColCount-1 Do
  begin
    GridB.Cells[Tmploop,0]:=Grid.Cells[Tmploop,0];
    GridB.AlignCell[Tmploop,0]:=alCenter;
    if MinRow>=1 then
    begin
      GridB.Cells[Tmploop,1]:=Grid.Cells[Tmploop,MinRow];
    end;
    if MaxRow>=1 then
    begin
      GridB.Cells[Tmploop,2]:=Grid.Cells[Tmploop,MaxRow];
    end;
  end;
  GridB.Cells[0,0]:='名次';
  GridB.Cells[0,1]:='最差';
  GridB.Cells[0,2]:='最优';
end;

procedure TfrmGradeCount.CheckHonours();
var TmpRow,TmpCol:Integer;
    TmpFloat:Extended;
    IsH:Boolean;
begin
  For TmpRow:=1 to Grid.RowCount-1 Do
  begin
    IsH:=True;
    if Grid.Cells[0,TmpRow]='' then Exit;
    For TmpCol:=3 to Grid.ColCount-2 Do
    begin
      TmpFloat:=GetHScore(ListSubjCode.Items[TmpCol-3+1]);
      if StrToFloat(Grid.Cells[TmpCol,TmpRow])<TmpFloat then
      begin
        IsH:=False;
      end;
    end;
    if IsH then
    begin
      Grid.RowFont[TmpRow].Color:=clRed;
    end;
  end;
end;

Function TfrmGradeCount.GetHScore(SelSubj:String):Extended;
begin
  Result:=0;
  if dsStd.IsEmpty then Exit;
  dsStd.First;
  dsStd.Filter:='SubjectCode='+''''+SelSubj+'''';
  if dsStd.FindFirst then
  begin
    Result:=dsStd.fieldByName('Score').AsFloat;
  end;
end;

Procedure TfrmGradeCount.HideArea();
var TmpMin,TmpMax,TmpCheck:Extended;
    DealCol:Integer;
    TmpRow:Integer;
begin
  if cmbSubj.ItemIndex<=0 then
  begin
    Exit;
    //DealCol:=Grid.ColCount-1;
  end
  else
  begin
    DealCol:=cmbSubj.ItemIndex+2;
  end;
  TmpMin:=StrToFloat(Trim(MaskEdit1.Text));
  TmpMax:=StrToFloat(Trim(MaskEdit2.Text));
  For TmpRow:=1 to Grid.RowCount-1 Do
  begin
    if Grid.Cells[0,TmpRow]='' then Exit;
    TmpCheck:=StrToFloat(Grid.Cells[DealCol,TmpRow]);
    if TmpCheck<TmpMin then
    begin
      Grid.RowHeights[TmpRow]:=-1;
    end;
    if TmpCheck>TmpMax then
    begin
      Grid.RowHeights[TmpRow]:=-1;
    end;
  end;
end;

Procedure TfrmGradeCount.SetJB();
var TmpSQL:String;
    LastTermCode,StudentID:String;
    Tmploop:Integer;
begin
  TmpSQL:='Select Max(TermCode) from TTerm Where TermCode<'+PNodeData(Tree.Selected.Parent.Data)^.Code;
  dsTemp.Close;
  dsTemp.CommandText:=TmpSQL;
  Try
    dsTemp.Open;
  Except
    SHowMessage('读取数据过程中发生错误！');
    Exit;
  End;
  Grid.ColCount:=Grid.ColCount+1;
  Grid.Cells[Grid.ColCount-1,0]:='上学期总分';
  Grid.AlignCell[Grid.ColCount-1,0]:=alcenter;
  LastTermCode:=IntToStr(dsTemp.Fields[0].AsInteger);
  For Tmploop:=1 to Grid.RowCount-1 Do
  begin
    StudentID:=Grid.Cells[0,Tmploop];
    if StudentID='' then Exit;
    TmpSQL:='Select Sum(Score) from Tgrade Where TermCode='+LastTermCode+
      ' And ClassCode='+''''+PNodeData(Tree.Selected.Data)^.Code+''''+
      ' And StudentID='+StudentID;
    dstemp.Close;
    dstemp.CommandText:=TmpSQL;
    Try
      dstemp.Open;
    Except
      SHowMessage('读取数据过程中发生错误！');
      Exit;
    End;
    if Grid.Cells[Grid.ColCount-2,Tmploop]='' then Exit;
    Grid.Cells[Grid.ColCount-1,Tmploop]:=FormatFloat('0.0',dsTemp.Fields[0].AsFloat);
    Grid.AlignCell[Grid.ColCount-1,Tmploop]:=alRight;
    if StrToFloat(Grid.Cells[Grid.ColCount-2,Tmploop])=0 then Continue;
    if dsTemp.Fields[0].AsFloat<>0 then
    begin
      if StrToFloat(Grid.Cells[Grid.ColCount-1,Tmploop])<StrToFloat(Grid.Cells[Grid.ColCount-2,Tmploop]) then
      begin
        if StrToFloat(Grid.Cells[Grid.ColCount-2,Tmploop])/StrToFloat(Grid.Cells[Grid.ColCount-1,Tmploop])>1.1 then
        begin
          Grid.ColorRow[Tmploop]:=$00C2E9B7; 
        end;
      end;
    end;
  end;
end;

procedure TfrmGradeCount.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  Action:=caFree;
end;

procedure TfrmGradeCount.TreeClick(Sender: TObject);
begin
  EmptyGrid(Grid);
  if Not ReadyStudent then Exit;
  if Not ReadySubj(True) then Exit;
  if Not ReadyGrade then Exit;
  CalcTotal;
  CheckHonours;
  SetJB;

end;

procedure TfrmGradeCount.cmdFilterClick(Sender: TObject);
begin
  EmptyGrid(Grid);
  if Not ReadyStudent then Exit;
  if Not ReadySubj then Exit;
  if Not ReadyGrade then Exit;
  CalcTotal;
  CheckHonours;
  SetJB;
  if cmbSubj.ItemIndex>0 then
  begin
    Sort(cmbSubj.ItemIndex-1+3,False,True,Grid);
    HideArea;
  end;
end;

procedure TfrmGradeCount.cmdDetailClick(Sender: TObject);
begin
  if Grid.Cells[0,grid.Row]='' then Exit;
  ShowStudentEdit(StrToInt(Grid.Cells[0,grid.Row]),PNodeData(Tree.Selected.Data)^.Code,
    PNodeData(Tree.Selected.Data)^.Name);
end;

procedure TfrmGradeCount.cmdCloseClick(Sender: TObject);
begin
  Close;
end;

end.