u_allsubjecttable.pas

一个简单的学籍管理软件

unit U_AllSubjectTable;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  HGrids, HDBGrids, TB97Tlbr, TB97Ctls, TB97, DBTables, Db, Menus,
  ComCtrls, ExtCtrls, ImgList, StdCtrls, Spin;

type
  TFm_AllSubjectTable = class(TForm)
    MDS: TDataSource;
    Dock: TDock97;
    Tb97_Func: TToolbar97;
    TbBtn_Help: TToolbarButton97;
    TbBtn_Exit: TToolbarButton97;
    TbSep02: TToolbarSep97;
    MQuery: TQuery;
    MUpSQL: TUpdateSQL;
    PPM: TPopupMenu;
    PM_Name: TMenuItem;
    PM_Code: TMenuItem;
    PM_Z01: TMenuItem;
    PM_Table: TMenuItem;
    PM_Help: TMenuItem;
    PM_Exit: TMenuItem;
    PM_Z02: TMenuItem;
    Spl_Main: TSplitter;
    TbBtn_Collect: TToolbarButton97;
    ImageList: TImageList;
    TbSep01: TToolbarSep97;
    TbBtn_ExamNo: TToolbarButton97;
    MGrid: THDBGrid;
    Panel1: TPanel;
    MTrView: TTreeView;
    Panel2: TPanel;
    CmbBox_Subject: TComboBox;
    Lbl_Sub: TLabel;
    TbBtn_Condition: TToolbarButton97;
    SQuery: TQuery;
    SUpSQL: TUpdateSQL;
    ChkBox_Class: TCheckBox;
    TbBtn_Print: TToolbarButton97;
    TbSp01: TToolbarSep97;
    PM_Print: TMenuItem;
    PM_Z03: TMenuItem;
    procedure TbBtn_ExitClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure Spl_MainCanResize(Sender: TObject; var NewSize: Integer;
      var Accept: Boolean);
    procedure Spl_FuCanResize(Sender: TObject; var NewSize: Integer;
      var Accept: Boolean);
    procedure MTrViewGetImageIndex(Sender: TObject; Node: TTreeNode);
    procedure MTrViewGetSelectedIndex(Sender: TObject; Node: TTreeNode);
    procedure MTrViewChange(Sender: TObject; Node: TTreeNode);
    procedure QueryGetText(Sender: TField; var Text: String; DisplayText: Boolean);
    procedure TbBtn_CollectClick(Sender: TObject);
    procedure TbBtn_HelpClick(Sender: TObject);
    procedure TbBtn_ExamNoClick(Sender: TObject);
    procedure Spl_MainMoved(Sender: TObject);
    procedure CmbBox_SubjectChange(Sender: TObject);
    procedure TbBtn_ConditionClick(Sender: TObject);
    procedure ChkBox_ClassClick(Sender: TObject);
    procedure TbBtn_PrintClick(Sender: TObject);
  private
  public
    CurGrade, OldGrade: integer;
    CurClass: string;
    CurSubCode, CurSelSub: integer;
    ExamType: integer;

    GradeChanged, ExamChanged, ShowClassChanged: Boolean;

    BaseCondCode: integer; //  0 -- 没有条件  1 -- 前 X 名  2 -- XX 分以上
    BaseConds: integer;    //  条件
    UsedCondCode: integer;
    UsedConds: integer;   

    ShowClass, OldShowClass: Boolean;
    procedure ReloadQuery(GradeNo: integer; SubCode: integer);
    procedure ReCreateColumn(GradeNo: integer; SelSub, SubCode: integer);
    procedure DistillQuery;
    procedure SetGridTitle;
    function CheckCurExam: Boolean;
  end;

var
  Fm_AllSubjectTable: TFm_AllSubjectTable;

procedure Show_AllSubjectTable;

implementation

uses IniFiles, U_Main, U_GlobalProc, U_GlobalVar, U_DM, U_SetCurExam,
     U_SubjectCondition, U_SubjectCollect, U_Print;

{$R *.DFM}

procedure Show_AllSubjectTable;
var
  i: integer;
begin
  for i := Fm_Main.MDIChildCount - 1 downto 0 do
  begin
    if (Fm_Main.MDIChildren[i].Name <> 'Fm_Wizard') and
      (Fm_Main.MDIChildren[i].Name <> 'Fm_AllSubjectTable') then
      Fm_Main.MDIChildren[i].Close;
  end;
  if not Assigned(Fm_AllSubjectTable) then
  begin
    Fm_AllSubjectTable := TFm_AllSubjectTable.Create(Application);
    with Fm_AllSubjectTable do
    begin
      BaseCondCode := 0;
      BaseConds := 0;
      UsedCondCode := 0;
      UsedConds := 0;
      
      GetCurExamCode;
      RefreshGridStyle(MGrid);
      ExamChanged := True;
      OldGrade := -1;
      ChkBox_Class.Checked := True;
      ShowClass := True;
      MakeGradeTree(MTrView, 0, False);
      MTrView.Items[0].Selected := True;
    end;
  end;
  with Fm_AllSubjectTable do
  begin
    if WindowState <> wsMaximized then
      WindowState := wsMaximized;
    Show;
    SetFocus;
  end;
end;

procedure TFm_AllSubjectTable.ReloadQuery(GradeNo: integer; SubCode: integer);
var
  aQuery: TQuery;
  tmSQL: string;
  i: integer;
  tmCount: Double;
begin
  SQuery.Close;
  tmSQL := 'SELECT CAST('''' AS SMALLINT) AS i_mci, s_xhao, s_xming, s_bjhao';
  //  课程
  aQuery := TQuery.Create(Application);
  with aQuery do
  try
    DataBaseName := DataDBase;
    if ExamType = 2 then
    begin
      SQL.Add('SELECT d1.s_kcdhao, d1.s_kcmcheng ');
      SQL.Add('FROM kc d1 ');
      SQL.Add('WHERE s_kcdhao=''' + IntFormatStr(SubCode, 4) + '''');
    end
    else
    begin
      SQL.Add('SELECT d1.s_kcdhao, d1.s_kcmcheng');
      SQL.Add('FROM kc d1, xqkskc d2 ');
      SQL.Add('WHERE d2.s_xqhao=''' + S_CurTermCode +
        ''' AND d2.si_nji=' + IntToStr(GradeNo) +
        ' AND d1.s_kcdhao=d2.s_kcdhao');
    end;
    Open;
    //  课程选项
    CmbBox_Subject.Items.Clear;
    if IsEmpty then
      Exit; // SQuery 未变
    CmbBox_Subject.Items.AddObject('全部课程', Pointer(0));
    while not Eof do
    begin
      tmSQL := tmSQL + ', CAST(''0'' AS NUMERIC) AS n_' + FieldByName('s_kcdhao').AsString;
      CmbBox_Subject.Items.AddObject(FieldByName('s_kcmcheng').AsString,
        Pointer(FieldByName('s_kcdhao').AsInteger));
      Next;
    end;
    tmSQL := tmSQL + ', CAST(''0'' AS NUMERIC) AS n_zfen, CAST(''0'' AS NUMERIC) AS n_pjun ' +
      'FROM xsjbxx ' +
      'WHERE 0=1';
    if CmbBox_Subject.Items.Count = 2 then
      CmbBox_Subject.Items.Delete(0);
    CmbBox_Subject.ItemIndex := 0;
    //  选择
    CurSelSub := Integer(CmbBox_Subject.Items.Objects[CmbBox_Subject.ItemIndex]);

    //  数据源
    SQuery.SQL.Text := tmSQL;
    SQuery.Open;

    //  学生
    Close;
    SQL.Clear;
    if ExamType = 2 then //  抽考
    begin
      SQL.Add('SELECT d1.s_xhao, d2.s_xming, d2.s_bjhao');
      SQL.Add('FROM ckry d1, xsjbxx d2');
      SQL.Add('WHERE d1.si_nji=' + IntToStr(CurGrade));
      {if CurClass <> '' then
        SQL.Add('AND d2.s_bjhao=''' + CurClass + '''');}
      SQL.Add('AND d1.s_xhao=d2.s_xhao');
    end
    else
    begin //  全考
      SQL.Add('SELECT s_xhao, s_xming, s_bjhao');
      SQL.Add('FROM xsjbxx');
      //if CurClass = '' then
      SQL.Add('WHERE s_bjhao LIKE ''' + S_SchoolCode +
        IntFormatStr(W_CurYear - CurGrade + 1, 4) + '%''');
      //else
      //  SQL.Add('WHERE s_bjhao=''' + CurClass + '''');
    end;
    Open;
    if IsEmpty then
    begin
      Close;
      Exit; //  SQuery 为空
    end;
    while not Eof do
    begin //依次添加学生:  名次  学号                           姓名
      SQuery.AppendRecord(['', FieldByName('s_xhao').AsString,
        FieldByName('s_xming').Asstring, FieldByName('s_bjhao').Asstring]);
      Next;
    end;
    //  成绩
    Close;
    SQL.Text := 'SELECT s_xhao, s_kcdhao, n_cji FROM kscj WHERE ' +
      'i_kshao=' + IntToStr(I_CurExamCode[CurGrade]) + ' AND s_kcdhao=:ps_kcdhao';
    for i := 4 to SQuery.FieldCount - 3 do // 去除名次、学号、姓名、班级号、总分、平均
    begin
      tmSQL := Copy(SQuery.Fields[i].FieldName, 3, 4);
      SQuery.First;
      Close;
      ParamByName('ps_kcdhao').AsString := tmSQL;
      Prepare;
      Open;
      while not SQuery.Eof do
      begin
        if Locate('s_xhao', SQuery.FieldByName('s_xhao').AsString, []) then
        begin
          SQuery.Edit;
          SQuery.Fields[i].AsFloat := FieldByName('n_cji').AsFloat;
          SQuery.Post;
        end;
        SQuery.Next;
      end;
      Close;
    end;
  finally
    Close;
    Free;
  end;
  with SQuery do
  try
    //  总分与平均数
    First;
    while not Eof do
    begin
      tmCount := 0;
      for i := 4 to FieldCount - 3 do
        tmCount := tmCount + Fields[i].AsFloat;
      Edit;
      FieldByName('n_zfen').AsFloat := tmCount;
      FieldByName('n_pjun').AsFloat := tmCount / (FieldCount - 6);
      Post;
      Next;
    end;
  finally
    OldGrade := GradeNo;
  end;
end;

procedure TFm_AllSubjectTable.ReCreateColumn(GradeNo: integer; SelSub, SubCode: integer);
var
  aQuery: TQuery;
  Meters: integer;
  TM: TTextMetric;
begin
  GetTextMetrics(MGrid.Canvas.Handle, TM);
  Meters := MGrid.Canvas.TextWidth('0') - TM.tmOverhang;
  //  名次
  MGrid.Columns.Clear;
  with MGrid.Columns.Add do
  begin
    Fieldname := 'i_mci';
    Title.Caption := '名次';
    Width := 4 * Meters + TM.tmOverhang + 4;
    CanEdit := False;
    Alignment := taCenter;
  end;
  //  姓名
  with MGrid.Columns.Add do
  begin
    Fieldname := 's_xming';
    Title.Caption := '姓名';
    Width := 8 * Meters + TM.tmOverhang + 4;
    CanEdit := False;
  end;
  aQuery := TQuery.Create(Application);
  with aQuery do
  try
    DataBaseName := DataDBase;
    if ShowClass then
    begin
      //  班级
      with MGrid.Columns.Add do
      begin
        Fieldname := 's_bjhao';
        Title.Caption := '班级';
        Width := 6 * Meters + TM.tmOverhang + 4;
        CanEdit := False;
        SQL.Text := 'SELECT * FROM bj WHERE si_dqnji=' + IntToStr(CurGrade);
        Open;
        while not Eof do
        begin
          Picklist.AddObject(FieldByName('s_bjmcheng').AsString,
            Pointer(FieldByName('s_bjhao').AsInteger));
          Next;
        end;
        Close;
      end;
    end;
    SQL.Text := 'SELECT d1.s_kcdhao, d1.s_kcmcheng FROM ';
    if SelSub > 0 then
      SQL.Text := SQL.Text + 'kc d1 WHERE s_kcdhao=''' + IntFormatStr(SelSub, 4) + ''''
    else if SubCode > 0 then //  此选择支好像不需要???
      SQL.Text := SQL.Text + 'kc d1 WHERE s_kcdhao=''' + IntFormatStr(SubCode, 4) + ''''
    else
      SQL.Text := SQL.Text + 'kc d1, xqkskc d2  WHERE d2.s_xqhao=''' + S_CurTermCode +
        ''' AND d2.si_nji=' + IntToStr(GradeNo) + ' AND d1.s_kcdhao=d2.s_kcdhao';
    Open;
    if IsEmpty then
      Exit;
    while not Eof do
    begin
      with MGrid.Columns.Add do
      begin
        FieldName := 'n_' + FieldByName('s_kcdhao').AsString;
        Title.Caption := FieldByName('s_kcmcheng').AsString;
        Width := 6 * Meters + TM.tmOverhang + 4;
        CanEdit := False;
        Alignment := taRightJustify;
      end;
      Next;
    end;
    Close;

    if (MGrid.Columns.Count > 4) or
       ((MGrid.Columns.Count > 3) and (not ShowClass)) then
    begin
      //  总分
      with MGrid.Columns.Add do
      begin
        FieldName := 'n_zfen';
        Title.Caption := '总分';
        Width := 8 * Meters + TM.tmOverhang + 4;
        CanEdit := False;
        Alignment := taRightJustify;
      end;
      //  平均
      with MGrid.Columns.Add do
      begin
        FieldName := 'n_pjun';
        Title.Caption := '平均';
        Width := 7 * Meters + TM.tmOverhang + 4;
        CanEdit := False;
        Alignment := taRightJustify;
      end;
    end;
  finally
    Close;
    Free;
  end;
end;

type
  TOrderRecord = record
    xhao: string;
    cji: double;
  end;

procedure TFm_AllSubjectTable.DistillQuery;
var
  IdxName: string;
  i, j, AryCount: integer;
  OrderBuf: array of TOrderRecord;
  M: TOrderRecord;
  ChangedFlag: Boolean;
begin
  if CurSelSub > 0 then
    IdxName := 'n_' + IntFormatStr(CurSelSub, 4)
  else
    IdxName := 'n_zfen';

  //  取排序值
  AryCount := 0;
  with SQuery do
  try