peopleunit.pas

学费管理系统,学校使用

      sql.add(' and student.regdate>='''+datetostr(datetimepicker1.Date)+' 00:00:00'+'''');
      sql.add(' and student.regdate<='''+datetostr(datetimepicker2.Date)+' 23:59:59'+'''');
    end;      
    if trim(ClassCode_Edit.Text )<>'' then
      sql.Add('group by studentname')
    else if trim(Departmentid_Edit.Text )<>'' then
      sql.Add('group by classcode')
    else
      sql.Add('group by department');
    sql.add(')as c left join (');

    if trim(ClassCode_Edit.Text )<>'' then
      sql.Add('select studentname')
    else if trim(Departmentid_Edit.Text )<>'' then
      sql.Add('select classcode')
    else
      sql.Add('select department');
    sql.Add(',count(*) as ''欠费人数'' from (');
    sql.Add('select studentid,chargerangeid,sum(quantum)-sum(derate)-(sum(fact)-sum(returnnum))  as cash');
    sql.Add('from income');
    sql.Add('group by studentid,chargerangeid) as a,student,department where a.studentid=student.id and student.departmentid=department.id');
    sql.Add('and cash>0');
    if trim( ChargeYearID_edit.Text )<>'' then
      sql.add(' and student.ChargeYearID='+trim( ChargeYearID_edit.Text ));
    if trim( ChargeRangeId_edit.Text )<>'' then
      sql.add(' and a.ChargeRangeId='+trim( ChargeRangeId_edit.Text ));
    if trim(Departmentid_Edit.Text )<>'' then
      sql.add(' and student.departmentid='+trim( Departmentid_Edit.Text ));
    if trim(Scholarid_edit.Text )<>'' then
      sql.add(' and student.Scholarid='+trim( Scholarid_edit.Text ));
    if trim(name_Edit.Text )<>'' then
      sql.add(' and student.studentname='''+trim( name_Edit.Text )+'''');
    if trim(Length_ComboBox.Text )<>'' then
      sql.add(' and student.length='+trim( Length_ComboBox.Text ));
    if trim(ClassCode_Edit.Text )<>'' then
      sql.add(' and student.classcode='''+trim( ClassCode_Edit.Text )+'''');
    if checkbox1.Checked then
    begin
      sql.add(' and student.regdate>='''+datetostr(datetimepicker1.Date)+' 00:00:00'+'''');
      sql.add(' and student.regdate<='''+datetostr(datetimepicker2.Date)+' 23:59:59'+'''');
    end;
    if trim(ClassCode_Edit.Text )<>'' then
      sql.Add('group by studentname')
    else if trim(Departmentid_Edit.Text )<>'' then
      sql.Add('group by classcode')
    else
      sql.Add('group by department');
    sql.Add(') as a ');
    if trim(ClassCode_Edit.Text )<>'' then
      sql.Add('on a.studentname=c.studentname')
    else if trim(Departmentid_Edit.Text )<>'' then
      sql.Add('on a.classcode=c.classcode')
    else
      sql.Add('on a.department=c.department');
    sql.add(' left join ( ');
    if trim(ClassCode_Edit.Text )<>'' then
      sql.Add('select studentname')
    else if trim(Departmentid_Edit.Text )<>'' then
      sql.Add('select classcode')
    else
      sql.Add('select department');
    sql.Add(',count(*)as ''交清人数'' from (');
    sql.Add('select studentid,chargerangeid,sum(quantum)-sum(derate)-(sum(fact)-sum(returnnum))  as cash');
    sql.Add('from income');
    sql.Add('group by studentid,chargerangeid) as a,student,department where a.studentid=student.id and student.departmentid=department.id');
    sql.Add('and cash<=0');
    if trim( ChargeYearID_edit.Text )<>'' then
      sql.add(' and student.ChargeYearID='+trim( ChargeYearID_edit.Text ));
    if trim( ChargeRangeId_edit.Text )<>'' then
      sql.add(' and a.ChargeRangeId='+trim( ChargeRangeId_edit.Text ));
    if trim(Departmentid_Edit.Text )<>'' then
      sql.add(' and student.departmentid='+trim( Departmentid_Edit.Text ));
    if trim(Scholarid_edit.Text )<>'' then
      sql.add(' and student.Scholarid='+trim( Scholarid_edit.Text ));
    if trim(name_Edit.Text )<>'' then
      sql.add(' and student.studentname='''+trim( name_Edit.Text )+'''');
    if trim(Length_ComboBox.Text )<>'' then
      sql.add(' and student.length='+trim( Length_ComboBox.Text ));
    if trim(ClassCode_Edit.Text )<>'' then
      sql.add(' and student.classcode='''+trim( ClassCode_Edit.Text )+'''');
    if checkbox1.Checked then
    begin
      sql.add(' and student.regdate>='''+datetostr(datetimepicker1.Date)+' 00:00:00'+'''');
      sql.add(' and student.regdate<='''+datetostr(datetimepicker2.Date)+' 23:59:59'+'''');
    end;
    if trim(ClassCode_Edit.Text )<>'' then
      sql.Add('group by studentname')
    else if trim(Departmentid_Edit.Text )<>'' then
      sql.Add('group by classcode')
    else
      sql.Add('group by department');
    sql.Add(' )as b');
    if trim(ClassCode_Edit.Text )<>'' then
      sql.Add('on b.studentname=c.studentname')
    else if trim(Departmentid_Edit.Text )<>'' then
      sql.Add('on b.classcode=c.classcode')
    else
      sql.Add('on b.department=c.department');

    sql.Add('union all');

    if trim(ClassCode_Edit.Text )<>'' then
      sql.Add('select ''合计'' as ''姓名''')
    else if trim(Departmentid_Edit.Text )<>'' then
      sql.Add('select ''合计'' as ''班级''')
    else
      sql.Add('select ''合计'' as ''院系''');
    sql.Add(',sum(欠费人数),sum(交清人数),sum(c.合计) from (');
    if trim(ClassCode_Edit.Text )<>'' then
      sql.Add('select studentname')
    else if trim(Departmentid_Edit.Text )<>'' then
      sql.Add('select classcode')
    else
      sql.Add('select department');
    sql.Add(',count(*) as ''合计'' from (');
    sql.Add('select studentid,chargerangeid,sum(quantum)-sum(derate)-(sum(fact)-sum(returnnum))  as cash');
    sql.Add('from income');
    sql.Add('group by studentid,chargerangeid) as a,student,department where a.studentid=student.id and student.departmentid=department.id');
    if trim( ChargeYearID_edit.Text )<>'' then
      sql.add(' and student.ChargeYearID='+trim( ChargeYearID_edit.Text ));
    if trim( ChargeRangeId_edit.Text )<>'' then
      sql.add(' and a.ChargeRangeId='+trim( ChargeRangeId_edit.Text ));
    if trim(Departmentid_Edit.Text )<>'' then
      sql.add(' and student.departmentid='+trim( Departmentid_Edit.Text ));
    if trim(Scholarid_edit.Text )<>'' then
      sql.add(' and student.Scholarid='+trim( Scholarid_edit.Text ));
    if trim(name_Edit.Text )<>'' then
      sql.add(' and student.studentname='''+trim( name_Edit.Text )+'''');
    if trim(Length_ComboBox.Text )<>'' then
      sql.add(' and student.length='+trim( Length_ComboBox.Text ));
    if trim(ClassCode_Edit.Text )<>'' then
      sql.add(' and student.classcode='''+trim( ClassCode_Edit.Text )+'''');
    if checkbox1.Checked then
    begin
      sql.add(' and student.regdate>='''+datetostr(datetimepicker1.Date)+' 00:00:00'+'''');
      sql.add(' and student.regdate<='''+datetostr(datetimepicker2.Date)+' 23:59:59'+'''');
    end;      
    if trim(ClassCode_Edit.Text )<>'' then
      sql.Add('group by studentname')
    else if trim(Departmentid_Edit.Text )<>'' then
      sql.Add('group by classcode')
    else
      sql.Add('group by department');
    sql.add(')as c left join (');

    if trim(ClassCode_Edit.Text )<>'' then
      sql.Add('select studentname')
    else if trim(Departmentid_Edit.Text )<>'' then
      sql.Add('select classcode')
    else
      sql.Add('select department');
    sql.Add(',count(*) as ''欠费人数'' from (');
    sql.Add('select studentid,chargerangeid,sum(quantum)-sum(derate)-(sum(fact)-sum(returnnum))  as cash');
    sql.Add('from income');
    sql.Add('group by studentid,chargerangeid) as a,student,department where a.studentid=student.id and student.departmentid=department.id');
    sql.Add('and cash>0');
    if trim( ChargeYearID_edit.Text )<>'' then
      sql.add(' and student.ChargeYearID='+trim( ChargeYearID_edit.Text ));
    if trim( ChargeRangeId_edit.Text )<>'' then
      sql.add(' and a.ChargeRangeId='+trim( ChargeRangeId_edit.Text ));
    if trim(Departmentid_Edit.Text )<>'' then
      sql.add(' and student.departmentid='+trim( Departmentid_Edit.Text ));
    if trim(Scholarid_edit.Text )<>'' then
      sql.add(' and student.Scholarid='+trim( Scholarid_edit.Text ));
    if trim(name_Edit.Text )<>'' then
      sql.add(' and student.studentname='''+trim( name_Edit.Text )+'''');
    if trim(Length_ComboBox.Text )<>'' then
      sql.add(' and student.length='+trim( Length_ComboBox.Text ));
    if trim(ClassCode_Edit.Text )<>'' then
      sql.add(' and student.classcode='''+trim( ClassCode_Edit.Text )+'''');
    if checkbox1.Checked then
    begin
      sql.add(' and student.regdate>='''+datetostr(datetimepicker1.Date)+' 00:00:00'+'''');
      sql.add(' and student.regdate<='''+datetostr(datetimepicker2.Date)+' 23:59:59'+'''');
    end;
    if trim(ClassCode_Edit.Text )<>'' then
      sql.Add('group by studentname')
    else if trim(Departmentid_Edit.Text )<>'' then
      sql.Add('group by classcode')
    else
      sql.Add('group by department');
    sql.Add(') as a ');
    if trim(ClassCode_Edit.Text )<>'' then
      sql.Add('on a.studentname=c.studentname')
    else if trim(Departmentid_Edit.Text )<>'' then
      sql.Add('on a.classcode=c.classcode')
    else
      sql.Add('on a.department=c.department');
    sql.add(' left join ( ');
    if trim(ClassCode_Edit.Text )<>'' then
      sql.Add('select studentname')
    else if trim(Departmentid_Edit.Text )<>'' then
      sql.Add('select classcode')
    else
      sql.Add('select department');
    sql.Add(',count(*)as ''交清人数'' from (');
    sql.Add('select studentid,chargerangeid,sum(quantum)-sum(derate)-(sum(fact)-sum(returnnum))  as cash');
    sql.Add('from income');
    sql.Add('group by studentid,chargerangeid) as a,student,department where a.studentid=student.id and student.departmentid=department.id');
    sql.Add('and cash<=0');
    if trim( ChargeYearID_edit.Text )<>'' then
      sql.add(' and student.ChargeYearID='+trim( ChargeYearID_edit.Text ));
    if trim( ChargeRangeId_edit.Text )<>'' then
      sql.add(' and a.ChargeRangeId='+trim( ChargeRangeId_edit.Text ));
    if trim(Departmentid_Edit.Text )<>'' then
      sql.add(' and student.departmentid='+trim( Departmentid_Edit.Text ));
    if trim(Scholarid_edit.Text )<>'' then
      sql.add(' and student.Scholarid='+trim( Scholarid_edit.Text ));
    if trim(name_Edit.Text )<>'' then
      sql.add(' and student.studentname='''+trim( name_Edit.Text )+'''');
    if trim(Length_ComboBox.Text )<>'' then
      sql.add(' and student.length='+trim( Length_ComboBox.Text ));
    if trim(ClassCode_Edit.Text )<>'' then
      sql.add(' and student.classcode='''+trim( ClassCode_Edit.Text )+'''');
    if checkbox1.Checked then
    begin
      sql.add(' and student.regdate>='''+datetostr(datetimepicker1.Date)+' 00:00:00'+'''');
      sql.add(' and student.regdate<='''+datetostr(datetimepicker2.Date)+' 23:59:59'+'''');
    end;      
    if trim(ClassCode_Edit.Text )<>'' then
      sql.Add('group by studentname')
    else if trim(Departmentid_Edit.Text )<>'' then
      sql.Add('group by classcode')
    else
      sql.Add('group by department');
    sql.Add(' )as b');
    if trim(ClassCode_Edit.Text )<>'' then
      sql.Add('on b.studentname=c.studentname')
    else if trim(Departmentid_Edit.Text )<>'' then
      sql.Add('on b.classcode=c.classcode')
    else
      sql.Add('on b.department=c.department');
    open;
    //memo1.Lines.Text :=sql.Text ;
  end;
end;

procedure Tpeopleform.ChargeRangeId_editKeyPress(Sender: TObject;
  var Key: Char);
begin
  if not (key in['0'..'9','.',#8,#13]) then key:=#0;  
end;

procedure Tpeopleform.ChargeYearID_editKeyPress(Sender: TObject;
  var Key: Char);
begin
  if not (key in['0'..'9','.',#8,#13]) then key:=#0;
end;

procedure Tpeopleform.output_bitbtnClick(Sender: TObject);
begin
  ExportToExc(list_adoquery);
end;

procedure Tpeopleform.ClassCode_EditChange(Sender: TObject);
var i:Integer;
begin
  name_Edit.Items.Clear;
  if trim(classcode_edit.Text )='' then exit;
  with oper_adoquery do
  begin
    close;
    sql.Clear;
    sql.Add('select distinct(studentname) as studentn from student where  classcode=:classcode');
    Parameters.ParamByName('classcode').Value :=trim(classcode_edit.Text );
    open;
    name_Edit.Items.Add(' ');
    if not isempty then
    begin

      for i := 0 to RecordCount-1 do
      begin
        name_Edit.Items.Add(fieldbyname('studentn').AsString) ;
        Next;
      end;
    end;
  end;
end;

end.