u_newterm.pas

一个简单的学籍管理软件

unit U_NewTerm;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, ComCtrls, ExtCtrls, Db, DBTables;

type
  TFm_NewTerm = class(TForm)
    Lbl_Mess: TLabel;
    ProcBar: TProgressBar;
    Btn_Cancel: TButton;
    Tm: TTimer;
    procedure FormShow(Sender: TObject);
    procedure TmTimer(Sender: TObject);
  private
  public
  end;

procedure Show_NewTerm;
implementation

uses IniFiles, U_GlobalProc, U_GlobalVar, U_Main;
{$R *.DFM}

procedure Show_NewTerm;
var
  Fm_NewTerm: TFm_NewTerm;
begin
  if not U_GlobalProc.Show_ConfirmMess('开始新学期要在当前学期所有工作做完后进行。' + #$0A + #$0D +
    '真的开始新学期吗？') then
    Exit;
  Fm_NewTerm := TFm_NewTerm.Create(Application);
  with Fm_NewTerm do
  try
    ShowModal;
  finally
    Free;
  end;
end;

procedure TFm_NewTerm.FormShow(Sender: TObject);
begin
  Tm.Enabled := True;
end;

procedure TFm_NewTerm.TmTimer(Sender: TObject);
var
  DQuery: TQuery;
  tmNewTermCode: string;
  DelTermCode, OldGradeName: string;
  tmNewYear, tmNewTerm, i, j: integer;
begin
  //  防止重入
  (Sender as TTimer).Enabled := False;

  if W_CurTerm = 1 then //  仅仅生成新学期
  begin
    tmNewYear := W_CurYear;
    tmNewTerm := W_CurTerm + 1;
    tmNewTermCode := IntToStr(tmNewYear) + IntToStr(tmNewTerm);
  end
  else // if W_CurTerm = 2 then  //  生成新学年
  begin
    tmNewYear := W_CurYear + 1;
    tmNewTerm := 1;
    tmNewTermCode := IntToStr(tmNewYear) + IntToStr(tmNewTerm);

    //  各年级班数
    for i := SI_GradeCount - 1 downto 1 do
      SI_Classes[i] := SI_Classes[i - 1];
    //  一年级班数为 0
    SI_Classes[0] := 0;
    DQuery := TQuery.Create(Application);
    DQuery.DatabaseName := DataDBase;
    try
      with DQuery do
      begin
        //  添加学年
        SQL.Text := 'INSERT INTO nj VALUES(''' + IntToStr(tmNewYear) + '-' +
          IntToStr(tmNewYear + 1) + ''', '''', '''', 0)';
        ExecSQL;
        OldGradeName := IntToStr(tmNewYear - SI_GradeCount);
        ProcBar.Position := ProcBar.Position + 2;
        Application.ProcessMessages;
        //  原最大年级的学籍变化
        SQL.Text := 'INSERT INTO xsxjbh(s_xhao, i_sxhao, si_lxing, d_sjian) ' +
           'SELECT s_xhao, CAST(''999'' AS INT), CAST(''7'' AS smallint), '+
           'CAST(''06/30/' + IntToStr(W_CurYear) + ''' AS DATE) ' +
           'FROM xsjbxx WHERE s_bjhao LIKE ''' +
           S_SchoolCode + OldGradeName + '%''';
        ExecSQL;
        ProcBar.Position := ProcBar.Position + 2;
        Application.ProcessMessages;
        //  原最大年级的班级修改
        SQL.Text := 'UPDATE bj SET s_bjmcheng = '''', si_dqnji = -1 ' +
                    ' WHERE s_bjhao LIKE ''' + S_SchoolCode +
                    OldGradeName + '%''';
        ExecSQL;
        ProcBar.Position := ProcBar.Position + 2;
        Application.ProcessMessages;
        //  新班级名称
        for i := 2 to SI_GradeCount do
        begin
          for j := 0 to SI_Classes[i - 1] - 1 do
          begin
            DQuery.SQL.Text := 'UPDATE bj SET s_bjmcheng = ''' + GradeNames[i - 1] +
                         GradeNames[j] + '班'', si_dqnji =' + IntToStr(i) +
                         ' WHERE s_bjhao=''' + S_SchoolCode +
                         IntToStr(tmNewYear - i + 1) + IntFormatStr(j + 1, 2) + '''';
            ExecSQL;
          end;
        end;
        ProcBar.Position := ProcBar.Position + 2;
        Application.ProcessMessages;
      end;
      //  将要被删除的学生所在的学期号
      DelTermCode := IntToStr(tmNewYear - SI_GradeCount - 3);
      with DQuery do
      begin
        //  入学成绩表
        SQL.Text := 'DELETE FROM xsrxcj  WHERE (s_xhao IN (SELECT s_xhao ' +
          'FROM xsjbxx  WHERE s_xhao LIKE ''' + S_SchoolCode +
          DelTermCode + '%''))';
        ExecSQL;
        ProcBar.Position := ProcBar.Position + 1;
        Application.ProcessMessages;
        // 学生奖惩情况表
        SQL.Text := 'DELETE FROM xsjcqk  WHERE (s_xhao IN (SELECT s_xhao ' +
          'FROM xsjbxx  WHERE s_xhao LIKE ''' + S_SchoolCode +
          DelTermCode + '%''))';
        ExecSQL;
        ProcBar.Position := ProcBar.Position + 1;
        Application.ProcessMessages;
        //  学生考勤统计表
        SQL.Text := 'DELETE FROM xskqtj  WHERE (s_xhao IN (SELECT s_xhao ' +
          'FROM xsjbxx  WHERE s_xhao LIKE ''' + S_SchoolCode +
          DelTermCode + '%''))';
        ExecSQL;
        ProcBar.Position := ProcBar.Position + 1;
        Application.ProcessMessages;
        //  学生品行表现表
        SQL.Text := 'DELETE FROM xspxbx  WHERE (s_xhao IN (SELECT s_xhao ' +
          'FROM xsjbxx  WHERE s_xhao LIKE ''' + S_SchoolCode +
          DelTermCode + '%''))';
        ExecSQL;
        ProcBar.Position := ProcBar.Position + 1;
        Application.ProcessMessages;
        //  学生身体状况表
        SQL.Text := 'DELETE FROM xsstzk  WHERE (s_xhao IN (SELECT s_xhao ' +
          'FROM xsjbxx  WHERE s_xhao LIKE ''' + S_SchoolCode +
          DelTermCode + '%''))';
        ExecSQL;
        ProcBar.Position := ProcBar.Position + 1;
        Application.ProcessMessages;
        //  学生个性发展评价表
        SQL.Text := 'DELETE FROM xsgxfz  WHERE (s_xhao IN (SELECT s_xhao ' +
          'FROM xsjbxx  WHERE s_xhao LIKE ''' + S_SchoolCode +
          DelTermCode + '%''))';
        ExecSQL;
        ProcBar.Position := ProcBar.Position + 1;
        Application.ProcessMessages;
        //  学生参加活动情况表
        SQL.Text := 'DELETE FROM xsxjbh  WHERE (s_xhao IN (SELECT s_xhao ' +
          'FROM xsjbxx  WHERE s_xhao LIKE ''' + S_SchoolCode +
          DelTermCode + '%''))';
        ExecSQL;
        ProcBar.Position := ProcBar.Position + 1;
        Application.ProcessMessages;
        //  学生交纳学杂费情况表
        SQL.Text := 'DELETE FROM xsszfqk  WHERE (s_xhao IN (SELECT s_xhao ' +
          'FROM xsjbxx  WHERE s_xhao LIKE ''' + S_SchoolCode +
          DelTermCode + '%''))';
        ProcBar.Position := ProcBar.Position + 1;
        Application.ProcessMessages;
        //  学生期末老师评语表
        SQL.Text := 'DELETE FROM xsqmpy  WHERE (s_xhao IN (SELECT s_xhao ' +
          'FROM xsjbxx  WHERE s_xhao LIKE ''' + S_SchoolCode +
          DelTermCode + '%''))';
        ExecSQL;
        ProcBar.Position := ProcBar.Position + 1;
        Application.ProcessMessages;
        //  学生期末一般性评价表
        SQL.Text := 'DELETE FROM xsybpj  WHERE (s_xhao IN (SELECT s_xhao ' +
          'FROM xsjbxx  WHERE s_xhao LIKE ''' + S_SchoolCode +
          DelTermCode + '%''))';
        ExecSQL;
        ProcBar.Position := ProcBar.Position + 1;
        Application.ProcessMessages;
        //  考试成绩表
        SQL.Text := 'DELETE FROM kscj  WHERE (s_xhao IN (SELECT s_xhao ' +
          'FROM xsjbxx  WHERE s_xhao LIKE ''' + S_SchoolCode +
          DelTermCode + '%''))';
        ExecSQL;
        ProcBar.Position := ProcBar.Position + 1;
        Application.ProcessMessages;
        //  历届班级表
        SQL.Text := 'DELETE FROM bj  WHERE s_bjhao LIKE ''' + S_SchoolCode +
          DelTermCode + '%''';
        ExecSQL;
        ProcBar.Position := ProcBar.Position + 1;
        Application.ProcessMessages;
        //  历届学年表
        SQL.Text := 'DELETE FROM nj  WHERE s_njhao = ''' +
          DelTermCode + '-' +
          IntToStr(tmNewYear - SI_GradeCount - 2) + '''';
        ExecSQL;
        ProcBar.Position := ProcBar.Position + 1;
        Application.ProcessMessages;
        //  考试设置表
        SQL.Text := 'DELETE FROM kssz  WHERE s_xqhao LIKE ''' +
          DelTermCode + '%''';
        ExecSQL;
        ProcBar.Position := ProcBar.Position + 1;
        Application.ProcessMessages;
        //  学期各年级开设课程表
        SQL.Text := 'DELETE FROM xqkskc  WHERE s_xqhao LIKE ''' +
          DelTermCode + '%''';
        ExecSQL;
        ProcBar.Position := ProcBar.Position + 1;
        Application.ProcessMessages;
        //  学生历史信息表
        SQL.Text := 'INSERT INTO xslsxx SELECT d1.s_xhao, d1.s_xming, d1.si_xbie, ' +
          'd1.si_mzu, d1.d_csrqi, d1.s_xxzzhi, d1.d_rxsjian, d2.d_sjian as d_bysjian, ' +
          'd2.s_yysming as s_byqxiang FROM xsjbxx  d1, xsxjbh  d2 WHERE ' +
          'd1.s_xhao=d2.s_xhao AND d2.si_lxing=7';
        ExecSQL;
        ProcBar.Position := ProcBar.Position + 1;
        Application.ProcessMessages;
        //  学生学籍变化表
        SQL.Text := 'DELETE FROM xsxjbh  WHERE (s_xhao IN (SELECT s_xhao ' +
          'FROM xsjbxx  WHERE s_xhao LIKE ''' + S_SchoolCode +
          DelTermCode + '%''))';
        ExecSQL;
        ProcBar.Position := ProcBar.Position + 1;
        Application.ProcessMessages;
        //  学生基本情况表
        SQL.Text := 'DELETE FROM xsjbxx  WHERE s_xhao LIKE ''' + S_SchoolCode +
          DelTermCode + '%''';
        ExecSQL;
      end;
    finally
      DQuery.Free;
    end;
  end;
  ProcBar.Position := 30;
  Application.ProcessMessages;
  with TIniFile.Create(S_SysPath + '\' + StudentIniFile) do
  try
    W_CurYear := tmNewYear;
    W_CurTerm := tmNewTerm;
    S_CurTermCode := tmNewTermCode;

    //  当前年份、学期
    WriteInteger('系统参数', '当前学年', W_CurYear);
    WriteInteger('系统参数', '当前学期', W_CurTerm);
    for i := 0 to SI_GradeCount - 1 do
      WriteInteger('系统参数', GradeNames[i] + '年级', SI_Classes[i]);
    Fm_Main.MainStateBar.Panels[2].Text := '当前学期：' +
      IntToStr(W_CurYear) + '-' + IntToStr(W_CurYear + 1) + '学年 ' +
      '  第' + IntToStr(W_CurTerm) + '学期';
  finally
    Free;
  end;
  DQuery := TQuery.Create(Application);
  with DQuery do
  try
    //  学杂费
    DatabaseName := DataDBase;
    for i := 1 to SI_GradeCount do
    begin
      SQL.Text := 'INSERT INTO xqszf (s_xqhao, si_nji) VALUES(''' +
                   S_CurTermCode + ''', ' + IntToStr(i) + ')';
      ExecSQL;
      SQL.Text := 'INSERT INTO xsszfqk (s_xhao, s_xqhao, si_nji, ' +
        'n_sfei, n_xfei, n_zfei, n_qta, n_hji) ' +
        'SELECT s_xhao, s_xqhao, si_nji, n_sfei, n_xfei, n_zfei, ' +
        'n_qta, n_hji FROM  xsjbxx JOIN xqszf '+
        'ON s_xhao LIKE ''' + S_SchoolCode + IntToStr(W_CurYear - i + 1) +
        '%'' AND si_nji = ' + IntToStr(i);
      ExecSQL;
      ProcBar.Position := ProcBar.Position + 2;
      Application.ProcessMessages;
      //  学生考勤统计表
      SQL.Text := 'INSERT INTO xskqtj (s_xhao, s_xqhao) ' +
        'SELECT s_xhao, ''' + S_CurTermCode + ''' ' +
        'FROM  xsjbxx WHERE s_xhao LIKE ''' +
        S_SchoolCode + IntToStr(W_CurYear - i + 1) + '%''';
      ExecSQL;
      ProcBar.Position := ProcBar.Position + 2;
      Application.ProcessMessages;
      //  学生品行表现表
      SQL.Text := 'INSERT INTO xspxbx (s_xhao, s_xqhao, i_pjdhao) ' +
        'SELECT d1.s_xhao, ''' + S_CurTermCode + ''', d2.i_pjdhao ' +
        'FROM  xsjbxx d1 JOIN pxbxzb d2 ' +
        'ON d1.s_xhao LIKE ''' +
        S_SchoolCode + IntToStr(W_CurYear - i + 1) + '%''';
      ExecSQL;
      ProcBar.Position := ProcBar.Position + 2;
      Application.ProcessMessages;
      //  学生身体状况表
      SQL.Text := 'INSERT INTO xsstzk (s_xhao, s_xqhao) ' +
        'SELECT s_xhao, ''' + S_CurTermCode + ''' ' +
        'FROM  xsjbxx WHERE s_xhao LIKE ''' +
        S_SchoolCode + IntToStr(W_CurYear - i + 1) + '%''';
      ExecSQL;
      ProcBar.Position := ProcBar.Position + 2;
      Application.ProcessMessages;
      //  学生个性发展评价表
      SQL.Text := 'INSERT INTO xsgxfz (s_xhao, s_xqhao, i_pjdhao) ' +
        'SELECT d1.s_xhao, ''' + S_CurTermCode + ''', d2.i_pjdhao ' +
        'FROM  xsjbxx d1 JOIN gxfznr d2 ' +
        'ON d1.s_xhao LIKE ''' +
        S_SchoolCode + IntToStr(W_CurYear - i + 1) + '%''';
      ExecSQL;
      ProcBar.Position := ProcBar.Position + 2;
      Application.ProcessMessages;
      //  学生参加活动评价表
      SQL.Text := 'INSERT INTO xscjhd (s_xhao, s_xqhao, si_hddhao) ' +
        'SELECT d1.s_xhao, ''' + S_CurTermCode + ''', d2.FieldCode ' +
        'FROM  xsjbxx d1 JOIN "' + S_SysPath + '\dict3" d2 ' +
        'ON d1.s_xhao LIKE ''' + S_SchoolCode + IntToStr(W_CurYear - i + 1) + '%'' ' +
        'AND d2.TableName=''XSCJHD'' AND d2.FieldName=''SI_HDDHAO''';
      ExecSQL;
      ProcBar.Position := ProcBar.Position + 2;
      Application.ProcessMessages;
      //  学生期末一般性评价表
      SQL.Text := 'INSERT INTO xsybpj (s_xhao, s_xqhao) ' +
        'SELECT s_xhao, ''' + S_CurTermCode + ''' ' +
        'FROM  xsjbxx WHERE s_xhao LIKE ''' +
        S_SchoolCode + IntToStr(W_CurYear - i + 1) + '%''';
      ExecSQL;
    end;
    ProcBar.Position := 95;
  finally
    Free;
  end;
  Close;
end;

end.