unitachieve.pas.~192~

初中成绩录入系统网络版(firebird) firebird下的网络编程

unit unitAchieve;

interface

uses
  SysUtils, Windows, Messages, Classes, Graphics, Controls,
  Forms, Dialogs,unitDatamodule,IBQuery,IBDatabase,IBUpdateSQL,comObj,Variants,Math;

type
  TAchieve = class(TObject)
  private
    IBTrans1: TIBTransaction;
    Query1: TIBQuery;
  public
    constructor Create;
    destructor Destroy;override;
    procedure generateZKZH;
    procedure clearAchieve;
    function exportAch(Filename:String):Boolean;
    function ImportZkzh(FileName:String):Boolean;//导入准考证号
    function ImportJncj(FileName:String):Boolean;//导入技能成绩
    function ImportZyxx(FileName:String):Boolean;//导入志愿信息
    function ImportZGF(FileName:String):Boolean;//导入照顾分
    procedure CalculateDLSW(a,b,c,d:Integer;
      dldjA,dldjB,dldjC,dldjD,swdjA,swdjB,swdjC,swdjD:Real;
      dldjA1,dldjB1,dldjC1,dldjD1,swdjA1,swdjB1,swdjC1,swdjD1:Integer);//计算地理与生物成绩,a,b,c,d为各等级所占比例
    procedure CalculateTotal;//统计总分与投档分(+县级照顾分)
    procedure CalculateTotal2;//统计总分与投档分(+通用照顾分)
    procedure CalculateMingci;//计算名次
  end;
  

implementation

uses UnitProgress, UnitGlobal;

{
*********************************** TAchieve ***********************************
}
procedure CalculateDLSW(a,b,c,d:Integer;
      dldjA,dldjB,dldjC,dldjD,swdjA,swdjB,swdjC,swdjD:Real;
      dldjA1,dldjB1,dldjC1,dldjD1,swdjA1,swdjB1,swdjC1,swdjD1:Integer);
var
  i,Total:Integer;
  high,low:Real;//分数区间
  dili,shengwu,jncj:String;//提取地理与生物成绩
begin
  dm.Trans1.Active:=false;
  dm.Query1.Close;
  dm.Query1.SQL.Text:='select count(*) as cnt from student where dili>0';//地理人数
  dm.Query1.Open;
  Total:=dm.Query1.FieldByName('cnt').AsInteger;
  //计算地理A等级
  i:=Ceil(Total*a/10);
  dm.Query1.Close;
  dm.Query1.SQL.Text:='select first '+Inttostr(i)+' skip 0 dili_a from student order by dili_a desc';
  dm.Query1.Open;
  dm.Query1.Last;
  low:=dm.Query1.FieldByName('dili_a').AsFloat;//获取本等级的最低分
  dldjA:=low;
  dm.Query1.Close;
  dm.Query1.SQL.Text:='update student set dili=''A'' where dili_a>='+FloatTostr(low);
  dm.Query1.ExecSQL;
  //计算地理B等级
  i:=Ceil(Total*(a+b)/10);
  dm.Query1.Close;
  dm.Query1.SQL.Text:='select first '+Inttostr(i)+' skip 0 dili_a from student order by dili_a desc';
  dm.Query1.Open;
  dm.Query1.Last;
  high:=low;//将上一等级的最低分做为此等级的最高分
  low:=dm.Query1.FieldByName('dili_a').AsFloat;//获取本等级的最低分
  dm.Query1.Close;
  dm.Query1.SQL.Text:='update student set dili=''B'' where dili_a>='+FloatTostr(low)+
    ' and dili_a<'+FloatTostr(high);
  dm.Query1.ExecSQL;
  //计算地理C等级
  i:=Ceil(Total*(a+b+c)/10);
  dm.Query1.Close;
  dm.Query1.SQL.Text:='select first '+Inttostr(i)+' skip 0 dili_a from student order by dili_a desc';
  dm.Query1.Open;
  dm.Query1.Last;
  high:=low;//将上一等级的最低分做为此等级的最高分
  low:=dm.Query1.FieldByName('dili_a').AsFloat;//获取本等级的最低分
  dm.Query1.Close;
  dm.Query1.SQL.Text:='update student set dili=''C'' where dili_a>='+FloatTostr(low)+
    ' and dili_a<'+FloatTostr(high);
  dm.Query1.ExecSQL;
  //计算地理D等级
  high:=low;//将上一等级的最低分做为此等级的最高分
  dm.Query1.Close;
  dm.Query1.SQL.Text:='update student set dili=''D'' where dili_a<'+FloatTostr(high);
  dm.Query1.ExecSQL;

  dm.Trans1.Active:=false;
  dm.Query1.Close;
  dm.Query1.SQL.Text:='select count(*) as cnt from student where shengwu>0';//生物人数
  dm.Query1.Open;
  Total:=dm.Query1.FieldByName('cnt').AsInteger;
  //计算生物A等级
  i:=Ceil(Total*a/10);
  dm.Query1.Close;
  dm.Query1.SQL.Text:='select first '+Inttostr(i)+' skip 0 shengwu_a from student order by shengwu_a desc';
  dm.Query1.Open;
  dm.Query1.Last;
  low:=dm.Query1.FieldByName('shengwu_a').AsFloat;//获取本等级的最低分
  dm.Query1.Close;
  dm.Query1.SQL.Text:='update student set shengwu=''A'' where shengwu_a>='+FloatTostr(low);
  dm.Query1.ExecSQL;
  //计算生物B等级
  i:=Ceil(Total*(a+b)/10);
  dm.Query1.Close;
  dm.Query1.SQL.Text:='select first '+Inttostr(i)+' skip 0 shengwu_a from student order by shengwu_a desc';
  dm.Query1.Open;
  dm.Query1.Last;
  high:=low;//将上一等级的最低分做为此等级的最高分
  low:=dm.Query1.FieldByName('shengwu_a').AsFloat;//获取本等级的最低分
  dm.Query1.Close;
  dm.Query1.SQL.Text:='update student set shengwu=''B'' where shengwu_a>='+FloatTostr(low)+
    ' and shengwu_a<'+FloatTostr(high);
  dm.Query1.ExecSQL;
  //计算生物C等级
  i:=Ceil(Total*(a+b+c)/10);
  dm.Query1.Close;
  dm.Query1.SQL.Text:='select first '+Inttostr(i)+' skip 0 shengwu_a from student order by shengwu_a desc';
  dm.Query1.Open;
  dm.Query1.Last;
  high:=low;//将上一等级的最低分做为此等级的最高分
  low:=dm.Query1.FieldByName('shengwu_a').AsFloat;//获取本等级的最低分
  dm.Query1.Close;
  dm.Query1.SQL.Text:='update student set shengwu=''C'' where shengwu_a>='+FloatTostr(low)+
    ' and shengwu_a<'+FloatTostr(high);
  dm.Query1.ExecSQL;
  //计算生物D等级
  high:=low;//将上一等级的最低分做为此等级的最高分
  dm.Query1.Close;
  dm.Query1.SQL.Text:='update student set shengwu=''D'' where shengwu_a<'+FloatTostr(high);
  dm.Query1.ExecSQL;
  dm.Trans1.Commit;
  //以下统计各生的技能成绩并记录
  dm.Trans2.Active:=false;
  dm.Trans1.Active:=false;
  dm.Query2.Close;
  dm.Query2.SQL.Text:='select bmh,dili,shengwu from student';
  dm.Query2.Open;
  dm.Query2.First;
  while not dm.Query2.Eof do
  begin
    dili:=dm.Query2.FieldByName('dili').AsString;
    shengwu:=dm.Query2.FieldByName('shengwu').AsString;
    if dili=shengwu then
      jncj:='2'+dili
    else if dili<shengwu then
      jncj:='1'+dili+'1'+shengwu
    else
      jncj:='1'+shengwu+'1'+dili;
    dm.Query1.Close;
    dm.Query1.SQL.Text:='update student set jncj='''+jncj+
      ''' where bmh='''+dm.Query2.FieldByName('bmh').AsString+'''';
    dm.Query1.ExecSQL;
    dm.Query2.Next;
  end;
  dm.Trans1.Commit;
end;

procedure TAchieve.CalculateMingci;
var
  i,j:Integer;
  Total:Real;
begin
  dm.Query1.Transaction.Active:=false;
  dm.Query1.Close;
  dm.Query1.SQL.Text:='select zkzh,total,mingci from student order by total desc';
  dm.Query1.Open;
  dm.Query1.First;
  query1.Transaction.Active:=false;
  query1.Transaction.StartTransaction;
  i:=1;
  j:=1;
  Total:=0.0;
  while not dm.Query1.Eof do
  begin
    if Total<>dm.Query1.FieldByName('total').AsFloat then
      j:=i;
    Query1.Close;
    Query1.SQL.Text:='update student set mingci='+Inttostr(j)+
      ' where zkzh='''+dm.Query1.FieldByName('zkzh').AsString+'''';
    query1.ExecSQL;
    Total:=dm.Query1.FieldByName('total').AsFloat;
    dm.Query1.Next;
    i:=i+1;
  end;
  query1.Transaction.Commit;
end;

procedure TAchieve.CalculateTotal;
begin
  //计算总分与投档分
  IBTrans1.Active:=false;
  Query1.Close;
  Query1.SQL.Text:='update student set total=coalesce(zhengzhi,0)+coalesce(yuwen,0)'+
    '+coalesce(waiyu,0)+coalesce(shuxue,0)+coalesce(wuli,0)+coalesce(huaxue,0)'+
    '+coalesce(lishi,0),tdf=coalesce(total,0)+coalesce(bxzgf,0)-coalesce(kjf,0)';
  Query1.ExecSQL;
  IBTrans1.Commit;
end;

procedure TAchieve.CalculateTotal2;
begin
  //计算总分与投档分
  IBTrans1.Active:=false;
  Query1.Close;
  Query1.SQL.Text:='update student set total=coalesce(zhengzhi,0)+coalesce(yuwen,0)'+
    '+coalesce(waiyu,0)+coalesce(shuxue,0)+coalesce(wuli,0)+coalesce(huaxue,0)'+
    '+coalesce(lishi,0),tdf=coalesce(total,0)+coalesce(tyzgf,0)-coalesce(kjf,0)'+
    ' where lqpc=0';//没被录取的才计算市级照顾分
  Query1.ExecSQL;
  IBTrans1.Commit;
end;

procedure TAchieve.clearAchieve;
begin
  IBTrans1.Active:=false;
  Query1.Close;
  Query1.SQL.Text:='update ach1 set zhengzhi=0';
  Query1.ExecSQL;
  Query1.Close;
  Query1.SQL.Text:='update ach1 set yuwen=0';
  Query1.ExecSQL;
  Query1.Close;
  Query1.SQL.Text:='update ach1 set waiyu=0';
  Query1.ExecSQL;
  Query1.Close;
  Query1.SQL.Text:='update ach1 set shuxue=0';
  Query1.ExecSQL;
  Query1.Close;
  Query1.SQL.Text:='update ach1 set wuli=0';
  Query1.ExecSQL;
  Query1.Close;
  Query1.SQL.Text:='update ach1 set huaxue=0';
  Query1.ExecSQL;
  Query1.Close;
  Query1.SQL.Text:='update ach1 set lishi=0';
  Query1.ExecSQL;
  Query1.Close;
  Query1.SQL.Text:='update ach1 set dili_a=0';
  Query1.ExecSQL;
  Query1.Close;
  Query1.SQL.Text:='update ach1 set shengwu_a=0';
  Query1.ExecSQL;

  Query1.Close;
  Query1.SQL.Text:='update ach2 set zhengzhi=0';
  Query1.ExecSQL;
  Query1.Close;
  Query1.SQL.Text:='update ach2 set yuwen=0';
  Query1.ExecSQL;
  Query1.Close;
  Query1.SQL.Text:='update ach2 set waiyu=0';
  Query1.ExecSQL;
  Query1.Close;
  Query1.SQL.Text:='update ach2 set shuxue=0';
  Query1.ExecSQL;
  Query1.Close;
  Query1.SQL.Text:='update ach2 set wuli=0';
  Query1.ExecSQL;
  Query1.Close;
  Query1.SQL.Text:='update ach2 set huaxue=0';
  Query1.ExecSQL;
  Query1.Close;
  Query1.SQL.Text:='update ach2 set lishi=0';
  Query1.ExecSQL;
  Query1.Close;
  Query1.SQL.Text:='update ach2 set dili_a=0';
  Query1.ExecSQL;
  Query1.Close;
  Query1.SQL.Text:='update ach2 set shengwu_a=0';
  Query1.ExecSQL;

  Query1.Close;
  Query1.SQL.Text:='update student set zhengzhi=0';
  Query1.ExecSQL;
  Query1.Close;
  Query1.SQL.Text:='update student set yuwen=0';
  Query1.ExecSQL;
  Query1.Close;
  Query1.SQL.Text:='update student set waiyu=0';
  Query1.ExecSQL;
  Query1.Close;
  Query1.SQL.Text:='update student set shuxue=0';
  Query1.ExecSQL;
  Query1.Close;
  Query1.SQL.Text:='update student set wuli=0';
  Query1.ExecSQL;
  Query1.Close;
  Query1.SQL.Text:='update student set huaxue=0';
  Query1.ExecSQL;
  Query1.Close;
  Query1.SQL.Text:='update student set lishi=0';
  Query1.ExecSQL;
  Query1.Close;
  Query1.SQL.Text:='update student set dili_a=0';
  Query1.ExecSQL;
  Query1.Close;
  Query1.SQL.Text:='update student set shengwu_a=0';
  Query1.ExecSQL;

  Query1.Close;
  Query1.SQL.Text:='delete from incepted';
  Query1.ExecSQL;
  IBTrans1.Commit;
end;

constructor TAchieve.Create;
begin
  Query1:=TIBQuery.Create(nil);
  IBTrans1:=TIBTransaction.Create(nil);
  Query1.Transaction:=IBTrans1;
  IBTrans1.DefaultDatabase:=dm.IBDB;
  Query1.Database:=dm.IBDB;
end;

destructor TAchieve.Destroy;
begin
  Query1.Free;
  IBTrans1.Free;
end;

function TAchieve.exportAch(Filename: String):Boolean;
var
  ExcelApp,WorkBook1:Variant;
  i,j,cnt:Integer;
  fmProgress:TfmProgress;
begin
  try
    ExcelApp:=CreateOLEObject('Excel.Application');
    workBook1:=CreateOLEObject('Excel.Sheet');
  except
    Application.MessageBox('初始化Excel失败,请确认系统安装了Excel','错误',MB_OK or MB_ICONERROR);
    result:=false;
    exit;
  end;
  workBook1:=ExcelApp.WorkBooks.Add;
  CalculateTotal;//计算总分与投档分
  IBTrans1.Active:=false;
  Query1.Close;
  //计算总人数以便显示状态条
  Query1.SQL.Text:='select count(*) as cnt from student';
  Query1.Open;
  cnt:=Query1.FieldByName('cnt').AsInteger;
  fmProgress:=TfmProgress.Create(nil);
  fmProgress.ProgressBar1.Max:=cnt;
  fmProgress.Show;
  fmProgress.Update;
  //开始输出
  Query1.Close;
  Query1.SQL.Text:='select school,stdsn,stdname,zkzh,zhengzhi,yuwen,waiyu,shuxue,'+
    'wuli,huaxue,lishi,total,zgf,kjf,tdf,caoxing,dili,shengwu,shiyan,tiyu '+
    'from student order by zkzh';
  Query1.Open;

  workBook1.workSheets[1].Cells[1,1]:='学校';
  workBook1.workSheets[1].Cells[1,2]:='学号';
  workBook1.workSheets[1].Cells[1,3]:='姓名';
  workBook1.workSheets[1].Cells[1,4]:='准考证号';
  workBook1.workSheets[1].Cells[1,5]:='政治';
  workBook1.workSheets[1].Cells[1,6]:='语文';
  workBook1.workSheets[1].Cells[1,7]:='外语';
  workBook1.workSheets[1].Cells[1,8]:='数学';
  workBook1.workSheets[1].Cells[1,9]:='物理';
  workBook1.workSheets[1].Cells[1,10]:='化学';