unitprtsubject.pas.~214~

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

unit UnitPrtSubject;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, frxClass, frxDBSet, frxIBXComponents, StdCtrls, Buttons, CheckLst,
  frxExportRTF, ComCtrls,math, ExtCtrls;

type
  TfmPrtSubject = class(TForm)
    frxDBDataset1: TfrxDBDataset;
    frxIBXComponents1: TfrxIBXComponents;
    BitBtn1: TBitBtn;
    SubjectList: TCheckListBox;
    BitBtn2: TBitBtn;
    frxRTFExport1: TfrxRTFExport;
    ProgressBar1: TProgressBar;
    RadioGroup1: TRadioGroup;
    BitBtn3: TBitBtn;
    frxReport1: TfrxReport;
    GroupBox1: TGroupBox;
    RadioButton1: TRadioButton;
    RadioButton2: TRadioButton;
    procedure BitBtn3Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
  private
    { Private declarations }
    procedure createData(myprj:String);
  public
    { Public declarations }
  end;

implementation

uses unitDatamodule, unitAchieve;

{$R *.dfm}

procedure TfmPrtSubject.BitBtn1Click(Sender: TObject);
var
  i:Integer;
begin
  dm.Trans1.Active:=false;
  dm.Query1.SQL.Text:='delete from achtotal';
  dm.Query1.ExecSQL;
  dm.Trans1.Commit;
  progressBar1.Visible:=true;
  ProgressBar1.Max:=100;
  for i:=0 to SubjectList.Items.Count-1 do
  begin
    if SubjectList.Checked[i] then
      CreateData(SubjectList.Items[i]);
  end;
  ProgressBar1.Visible:=false;
  dm.Trans1.Active:=false;
  dm.Query1.Close;
  dm.Query1.SQL.Text:='select * from achtotal order by id';
  dm.Query1.Open;
  frxReport1.ShowReport();
end;

procedure TfmPrtSubject.createData(myprj: String);
var
  mycnt:integer;//存储各校人数
  school,schorder:string;//存储校名
  A1,A2,A3,A4,A5,A6:real;//存储各分数段的人数百分比
  tz:real;//将分数按百分制统计分数段时的一个调整值,比如,科目是"英语",那么tz:=100/50,因为英语的满分为50分
  bz100,bz90,bz80,bz60,bz50:string;//各个标准的标准值
  prjfield:String;//科目对应的字段
begin
  if myprj='政治' then
    prjField:='ZHENGZHI'
  else if myprj='语文' then
    prjField:='YUWEN'
  else if myprj='英语' then
    prjField:='WAIYU'
  else if myprj='数学' then
    prjField:='SHUXUE'
  else if myprj='物理' then
    prjField:='WULI'
  else if myprj='化学' then
    prjField:='HUAXUE'
  else if myprj='历史' then
    prjField:='LISHI'
  else if myprj='总分' then
    prjField:='TOTAL';

  //本过程将统计各科成绩,并写入achTotal表
  //将总分算成百分制,方便统计分数段人数
  if (myprj='语文')or(myprj='数学')or(myprj='英语') then
    tz:=150/100
  else if myprj='政治' then
    tz:=70/100
  else if myprj='物理' then
    tz:=90/100
  else if myprj='化学' then
    tz:=60/100
  else if myprj='历史' then
    tz:=50/100
  else if myprj='总分' then
    tz:=720/100
  else
    tz:=1;
  bz100:=floattostr(100*tz);bz90:=floattostr(roundto(90*tz,-1));bz80:=floattostr(roundto(80*tz,-1));
  bz60:=floattostr(roundto(60*tz,-1));bz50:=floattostr(roundto(50*tz,-1));//定下各项标准分
  dm.TransTable1.Active:=false;
  dm.Table1.TableName:='ACHTOTAL';
  dm.Table1.Open;

  ProgressBar1.Position:=0;//进度条清零
  dm.Trans2.Active:=false;
  dm.Query2.SQL.Text:='select schname,schorder from school order by schorder';
  dm.Query2.Open;
  dm.Query2.First;
  while not dm.Query2.Eof do
  begin
    school:=dm.Query2.FieldByName('schname').AsString;
    schorder:=dm.Query2.FieldByName('schorder').AsString;
    dm.Table1.Append;
    dm.Table1.FieldByName('subject').AsString:=myprj;
    dm.Table1.FieldByName('school').AsString:=school;
    dm.Trans1.Active:=false;
    dm.Query1.SQL.Text:='select count(*) as mycnt from student where substring(bmh from 1 for 4)='''+schorder+''' and quekao=''F''';
    dm.Query1.Open;
    mycnt:=dm.Query1.fieldbyname('mycnt').AsInteger;
    dm.Table1.FieldByName('rs').AsInteger:=mycnt;
    a1:=0;a2:=0;a3:=0;a4:=0;a5:=0;a6:=0;
    if mycnt>0 then
    begin
      dm.Query1.Close;
      dm.Query1.SQL.Text:='select sum('+prjField+') as myavg from student '+
        'where substring(bmh from 1 for 4)='''+schorder+''' and quekao=''F''';
      dm.Query1.Open;
      dm.Table1.FieldByName('pjf').AsFloat:=round(dm.Query1.fieldbyname('myavg').asfloat/mycnt*100)/100;
      dm.Query1.Close;
      dm.Query1.SQL.Text:='select max('+prjField+') as mymax from student '+
        'where substring(bmh from 1 for 4)='''+schorder+''' and quekao=''F''';
      dm.Query1.Open;
      dm.Table1.FieldByName('zg').AsFloat:=dm.Query1.fieldbyname('mymax').AsFloat;
      dm.Query1.Close;
      dm.Query1.SQL.Text:='select min('+prjField+') as mymin from student '+
        'where substring(bmh from 1 for 4)='''+schorder+''' and quekao=''F''';
      dm.Query1.Open;
      dm.Table1.FieldByName('zd').AsFloat:=dm.Query1.fieldbyname('mymin').AsFloat;
      dm.Query1.Close;
      dm.Query1.SQL.Text:='select count(*) as myyouxiu from student '+
        'where '+prjField+'>='+bz80+' and substring(bmh from 1 for 4)='''+schorder+''' and quekao=''F''';
      dm.Query1.Open;
      dm.Table1.FieldByName('yx').AsInteger:=dm.Query1.fieldbyname('myyouxiu').AsInteger;
      dm.Table1.FieldByName('yxl').AsFloat:=round(dm.Query1.fieldbyname('myyouxiu').AsInteger/mycnt*10000)/10000;
      dm.Query1.Close;
      dm.Query1.SQL.Text:='select count(*) as myjige from student '+
        'where '+prjfield+'>='+bz60+' and substring(bmh from 1 for 4)='''+schorder+''' and quekao=''F''';
      dm.Query1.Open;
      dm.Table1.FieldByName('jg').AsInteger:=dm.Query1.fieldbyname('myjige').AsInteger;
      dm.Table1.FieldByName('jgl').AsFloat:=round(dm.Query1.fieldbyname('myjige').AsFloat/mycnt*10000)/10000;
      dm.Query1.Close;
      dm.Query1.SQL.Text:='select count(*) as mymanfen from student '+
        'where '+prjfield+'='+bz100+' and substring(bmh from 1 for 4)='''+schorder+''' and quekao=''F''';
      dm.Query1.Open;
      dm.Table1.FieldByName('mf').AsInteger:=dm.Query1.fieldbyname('mymanfen').AsInteger;
      A1:=round(dm.Query1.fieldbyname('mymanfen').AsInteger/mycnt*1000)/10;//满分人数百分比率
      dm.Query1.Close;
      dm.Query1.SQL.Text:='select count(*) as my90 from student '+
        'where '+prjField+'>='+bz90+' and '+prjfield+'<'+bz100+' and substring(bmh from 1 for 4)='''+schorder+''' and quekao=''F''';
      dm.Query1.Open;
      dm.Table1.FieldByName('L90').AsInteger:=dm.Query1.fieldbyname('my90').AsInteger;
      A2:=round(dm.Query1.fieldbyname('my90').AsInteger/mycnt*1000)/10;//90分数段的百分比
      dm.Query1.Close;
      dm.Query1.SQL.Text:='select count(*) as my80 from student '+
        'where '+prjField+'>='+bz80+' and '+prjField+'<'+bz90+' and substring(bmh from 1 for 4)='''+schorder+''' and quekao=''F''';
      dm.Query1.Open;
      dm.Table1.FieldByName('L80').AsInteger:=dm.Query1.fieldbyname('my80').AsInteger;
      A3:=round(dm.Query1.fieldbyname('my80').AsInteger/mycnt*1000)/10;//80分数段的百分比
      dm.Query1.Close;
      dm.Query1.SQL.Text:='select count(*) as my60_79 from student '+
        'where '+prjField+'>='+bz60+' and '+prjField+'<'+bz80+' and substring(bmh from 1 for 4)='''+schorder+''' and quekao=''F''';
      dm.Query1.Open;
      dm.Table1.FieldByName('L60_79').AsInteger:=dm.Query1.fieldbyname('my60_79').AsInteger;
      A4:=round(dm.Query1.fieldbyname('my60_79').AsInteger/mycnt*1000)/10;//60--79分数段的百分比
      dm.Query1.Close;
      dm.Query1.SQL.Text:='select count(*) as my50 from student '+
        'where '+prjField+'>='+bz50+' and '+prjField+'<'+bz60+' and substring(bmh from 1 for 4)='''+schorder+''' and quekao=''F''';
      dm.Query1.Open;
      dm.Table1.FieldByName('L50').AsInteger:=dm.Query1.fieldbyname('my50').AsInteger;
      A5:=round(dm.Query1.fieldbyname('my50').AsInteger/mycnt*1000)/10;//50分数段的百分比
      dm.Query1.Close;
      dm.Query1.SQL.Text:='select count(*) as my50k from student '+
        'where '+prjfield+'<'+bz50+' and substring(bmh from 1 for 4)='''+schorder+''' and quekao=''F''';
      dm.Query1.Open;
      dm.Table1.FieldByName('L50LESS').AsInteger:=dm.Query1.fieldbyname('my50k').AsInteger;
      A6:=round(dm.Query1.fieldbyname('my50k').AsInteger/mycnt*1000)/10;//50分以下的百分比
    end;
    dm.Table1.Post;
    //以下存储各百分比
    dm.Table1.Append;
    dm.Table1.FieldByName('subject').AsString:=myprj;
//    dm.Table1.FieldByName('学校').AsString:='百分率';
    dm.Table1.FieldByName('mf').AsFloat:=a1;
    dm.Table1.FieldByName('L90').AsFloat:=a2;
    dm.Table1.FieldByName('L80').AsFloat:=a3;
    dm.Table1.FieldByName('L60_79').AsFloat:=a4;
    dm.Table1.FieldByName('L50').AsFloat:=a5;
    dm.Table1.FieldByName('L50LESS').AsFloat:=a6;
    dm.Table1.Post;
    ProgressBar1.Position:=ProGressBar1.Position+6;
    dm.Query2.Next;
  end;
  //以下添加全县资料
  dm.Table1.Append;
  dm.Table1.FieldByName('subject').AsString:=myprj;
  dm.Table1.FieldByName('school').AsString:='全县';
  dm.Query1.Close;
  dm.Query1.SQL.Text:='select count(*) as mycnt from student where quekao=''F''';
  dm.Query1.Open;
  mycnt:=dm.Query1.fieldbyname('mycnt').AsInteger;
  dm.Table1.FieldByName('rs').AsInteger:=mycnt;
  dm.Query1.Close;
  dm.Query1.SQL.Text:='select sum('+prjField+') as myavg from student where quekao=''F''';
  dm.Query1.Open;
  dm.Table1.FieldByName('pjf').AsFloat:=round(dm.Query1.fieldbyname('myavg').asfloat/mycnt*100)/100;
  dm.Query1.Close;
  dm.Query1.SQL.Text:='select max('+prjField+') as mymax from student where quekao=''F''';
  dm.Query1.Open;
  dm.Table1.FieldByName('zg').AsFloat:=dm.Query1.fieldbyname('mymax').AsFloat;
  dm.Query1.Close;
  dm.Query1.SQL.Text:='select min('+prjField+') as mymin from student where quekao=''F''';
  dm.Query1.Open;
  dm.Table1.FieldByName('zd').AsFloat:=dm.Query1.fieldbyname('mymin').AsFloat;
  dm.Query1.Close;
  dm.Query1.SQL.Text:='select count(*) as myyouxiu from student where '+prjField+'>='+bz80+' and quekao=''F''';
  dm.Query1.Open;
  dm.Table1.FieldByName('yx').AsInteger:=dm.Query1.fieldbyname('myyouxiu').AsInteger;
  dm.Table1.FieldByName('yxl').AsFloat:=round(dm.Query1.fieldbyname('myyouxiu').AsInteger/mycnt*10000)/10000;
  dm.Query1.Close;
  dm.Query1.SQL.Text:='select count(*) as myjige from student where '+prjField+'>='+bz60+' and quekao=''F''';
  dm.Query1.Open;
  dm.Table1.FieldByName('jg').AsInteger:=dm.Query1.fieldbyname('myjige').AsInteger;
  dm.Table1.FieldByName('jgl').AsFloat:=round(dm.Query1.fieldbyname('myjige').AsFloat/mycnt*10000)/10000;
  dm.Query1.Close;
  dm.Query1.SQL.Text:='select count(*) as mymanfen from student where '+prjField+'='+bz100+' and quekao=''F''';
  dm.Query1.Open;
  dm.Table1.FieldByName('mf').AsInteger:=dm.Query1.fieldbyname('mymanfen').AsInteger;
  dm.Query1.Close;
  dm.Query1.SQL.Text:='select count(*) as my90 from student where '+
    prjField+'>='+bz90+' and '+prjField+'<'+bz100+' and quekao=''F''';
  dm.Query1.Open;
  dm.Table1.FieldByName('L90').AsInteger:=dm.Query1.fieldbyname('my90').AsInteger;
  dm.Query1.Close;
  dm.Query1.SQL.Text:='select count(*) as my80 from student where '+
    prjField+'>='+bz80+' and '+prjField+'<'+bz90+' and quekao=''F''';
  dm.Query1.Open;
  dm.Table1.FieldByName('L80').AsInteger:=dm.Query1.fieldbyname('my80').AsInteger;
  dm.Query1.Close;
  dm.Query1.SQL.Text:='select count(*) as my60_79 from student where '+
    prjField+'>='+bz60+' and '+prjField+'<'+bz80+' and quekao=''F''';
  dm.Query1.Open;
  dm.Table1.FieldByName('L60_79').AsInteger:=dm.Query1.fieldbyname('my60_79').AsInteger;
  dm.Query1.Close;
  dm.Query1.SQL.Text:='select count(*) as my50 from student where '+
    prjField+'>='+bz50+' and '+prjfield+'<'+bz60+' and quekao=''F''';
  dm.Query1.Open;
  dm.Table1.FieldByName('L50').AsInteger:=dm.Query1.fieldbyname('my50').AsInteger;
  dm.Query1.Close;
  dm.Query1.SQL.Text:='select count(*) as my50k from student where '+
    prjField+'<'+bz50+' and quekao=''F''';
  dm.Query1.Open;
  dm.Table1.FieldByName('L50LESS').AsInteger:=dm.Query1.fieldbyname('my50k').AsInteger;
  dm.Table1.Post;
  progressbar1.Position:=progressbar1.Position+5;
  try
    dm.TransTable1.Commit;
  except
    dm.TransTable1.Rollback;
  end;
end;

procedure TfmPrtSubject.FormCreate(Sender: TObject);
var
  Achieve:TAchieve;
begin
  Achieve:=TAchieve.Create;
  Achieve.CalculateTotal;
  Achieve.Free;
end;

procedure TfmPrtSubject.BitBtn3Click(Sender: TObject);
var
  i,zeros:Integer;
begin
  dm.Query1.Transaction.Active:=false;
  if RadioGroup1.ItemIndex=0 then
  begin
    dm.Query1.Transaction.StartTransaction;
    dm.Query1.Close;
    dm.Query1.SQL.Text:='update student set quekao=''F''';
    dm.Query1.ExecSQL;
    dm.Query1.Transaction.Commit;
    Application.MessageBox('标记完毕','提示',MB_OK or MB_ICONINFORMATION);
    exit;
  end;
  dm.Query1.Close;
  dm.Query1.SQL.Text:='select zkzh,zhengzhi,yuwen,waiyu,shuxue,wuli,huaxue,lishi from student';
  dm.Query1.Open;
  dm.Query1.Last;
  dm.Query1.First;
  dm.Query2.Transaction.Active:=false;
  dm.Query2.Transaction.StartTransaction;
  ProgressBar1.Max:=dm.Query1.RecordCount;
  progressBar1.Position:=0;
  ProgressBar1.Visible:=true;
  while not dm.Query1.Eof do
  begin
    zeros:=0;
    for i:=1 to dm.Query1.FieldCount-1 do
    begin
      if dm.Query1.Fields[i].AsFloat=0 then
        zeros:=zeros+1;
    end;
    if zeros>=2 then //0分超过4科,则为缺考
    begin
      dm.Query2.Close;
      dm.Query2.SQL.Text:='update student set quekao=''T'' where zkzh='''+
        dm.Query1.FieldByName('zkzh').AsString+'''';
      dm.Query2.ExecSQL;
    end
    else
    begin
      dm.Query2.Close;
      dm.Query2.SQL.Text:='update student set quekao=''F'' where zkzh='''+
        dm.Query1.FieldByName('zkzh').AsString+'''';
      dm.Query2.ExecSQL;
    end;
    dm.Query1.Next;
    ProgressBar1.StepIt;
  end;
  dm.Query2.Transaction.Commit;
  ProgressBar1.Visible:=false;
  Application.MessageBox('标记完毕','提示',MB_OK or MB_ICONINFORMATION);
end;

end.