returndetailunit.pas

学费管理系统,学校使用

    MessageBox(application.handle,pchar('没有选择查询条件!'),'错误',MB_ICONWARNING+MB_OK);
    list_adoquery.close;
    clearcontent;
    exit;
  end;
  with list_adoquery do
  begin
    close;
    sql.clear;
    if (trim(name_Edit.Text )<>'') or (trim(oper_edit.text)<>'') then
    begin

      sql.Add('select');
      sql.Add('case chargeitemid when 1 then ''学费'' when 2 then ''住宿'' when 3 then ''书费'' when 4 then ''保险''');
      sql.Add('when 5 then ''生活用品'' when 6 then ''保证金''end as ''项目'',');
      sql.Add('returncode as ''退费单号'',chargerangeid as ''收费年份'',regdate as ''退费日期'',returnnum as ''退费金额''');
      if trim(oper_edit.text)<>'' then
        sql.Add(',studentname as ''学生姓名''')
      else if trim(name_edit.Text )<>'' then
        sql.Add(',name as ''操作员''');
      sql.Add('from (select student.chargeyearid,student.studentname,student.scholarid,student.departmentid,student.length,student.classcode,');
      sql.Add('returndetail.studentid,returndetail.chargerangeid,returndetail.chargeitemid,returndetail.returnnum,returndetail.returnname,');
      sql.Add('returndetail.regdate,returndetail.operator,returndetail.returncode,operator.name');
      sql.Add('from returndetail,operator,student');
      sql.Add('where returndetail.studentid=student.id');
      sql.Add('and returndetail.operator=operator.code');
      sql.Add('and returnnum>0) as a  where 1=1');
      if trim(ChargeYearID_edit.text)<>'' then
        sql.add(' and ChargeYearID='+trim( ChargeYearID_edit.Text ));
      if trim( ChargeRangeId_edit.Text )<>'' then
        sql.add(' and ChargeRangeId='+trim( ChargeRangeId_edit.Text ));
      if checkbox1.Checked then
      begin
        sql.add(' and regdate>='''+datetostr(datetimepicker1.Date)+' 00:00:00'+'''');
        sql.add(' and regdate<='''+datetostr(datetimepicker2.Date)+' 23:59:59'+'''');
      end;
      if trim(oper_edit.Text )<>'' then
        sql.Add(' and operator='''+trim(oper_edit.Text)+'''');
      if trim(Departmentid_Edit.Text )<>'' then
        sql.add(' and departmentid='+trim( Departmentid_Edit.Text ));
      if trim(Scholarid_edit.Text )<>'' then
        sql.add(' and Scholarid='+trim( Scholarid_edit.Text ));
      if trim(name_Edit.Text )<>'' then
        sql.add(' and studentname='''+trim( name_Edit.Text )+'''');
      if trim(Length_ComboBox.Text )<>'' then
        sql.add(' and length='+trim( Length_ComboBox.Text ));
      if trim(ClassCode_Edit.Text )<>'' then
        sql.add(' and classcode='''+trim( ClassCode_Edit.Text )+'''');
      sql.Add('union all select  ''合计'' as ''项目'',');
      sql.Add(''''' as ''退费单号'','''' as ''收费年份'','''+datetimetostr(now)+''' as ''退费日期'',sum(returnnum) as ''退费金额''');
      if trim(oper_edit.text)<>'' then
        sql.Add(','''' as ''学生姓名''')
      else if trim(name_edit.Text )<>'' then
        sql.Add(','''' as ''操作员''');
      sql.Add('from (select student.chargeyearid,student.studentname,student.scholarid,student.departmentid,student.length,student.classcode,');
      sql.Add('returndetail.studentid,returndetail.chargerangeid,returndetail.chargeitemid,returndetail.returnnum,returndetail.returnname,');
      sql.Add('returndetail.regdate,returndetail.operator,returndetail.returncode,operator.name');
      sql.Add('from returndetail,operator,student');
      sql.Add('where returndetail.studentid=student.id');
      sql.Add('and returndetail.operator=operator.code');
      sql.Add(' and returnnum>0) as a  where 1=1');
      if trim(ChargeYearID_edit.text)<>'' then
        sql.add(' and ChargeYearID='+trim( ChargeYearID_edit.Text ));
      if trim( ChargeRangeId_edit.Text )<>'' then
        sql.add(' and ChargeRangeId='+trim( ChargeRangeId_edit.Text ));
      if checkbox1.Checked then
      begin
        sql.add(' and regdate>='''+datetostr(datetimepicker1.Date)+' 00:00:00'+'''');
        sql.add(' and regdate<='''+datetostr(datetimepicker2.Date)+' 23:59:59'+'''');
      end;
      if trim(oper_edit.Text )<>'' then
        sql.Add(' and operator='''+trim(oper_edit.Text)+'''');
      if trim(Departmentid_Edit.Text )<>'' then
        sql.add(' and departmentid='+trim( Departmentid_Edit.Text ));
      if trim(Scholarid_edit.Text )<>'' then
        sql.add(' and Scholarid='+trim( Scholarid_edit.Text ));
      if trim(name_Edit.Text )<>'' then
        sql.add(' and studentname='''+trim( name_Edit.Text )+'''');
      if trim(Length_ComboBox.Text )<>'' then
        sql.add(' and length='+trim( Length_ComboBox.Text ));
      if trim(ClassCode_Edit.Text )<>'' then
        sql.add(' and classcode='''+trim( ClassCode_Edit.Text )+'''');
    end
    else
    begin
      sql.Add('select ');
      if trim(ClassCode_Edit.Text )<>'' then
      begin
        sql.Add('student.studentname as ''姓名'',') ;
        sql.add('sum(学费) as 学费,sum(住宿费) as 住宿费,sum(书费) as 书费,sum(保险费) as 保险费,sum(生活用品费) as 生活用品费,sum(保证金) as 保证金,sum(合计) as 合计 from ( ');
      end
      else if trim(Departmentid_Edit.Text )<>'' then
      begin
        sql.Add('student.classcode as ''班级'',');
        sql.add('sum(学费) as 学费,sum(住宿费) as 住宿费,sum(书费) as 书费,sum(保险费) as 保险费,sum(生活用品费) as 生活用品费,sum(保证金) as 保证金,sum(合计) as 合计 from ( ');
      end
      else
      begin
        sql.Add(' department.department as ''院系'',');
        sql.add('sum(学费) as 学费,sum(住宿费) as 住宿费,sum(书费) as 书费,sum(保险费) as 保险费,sum(生活用品费) as 生活用品费,sum(保证金) as 保证金,sum(合计) as 合计 from ( ');
      end;

      sql.Add('select a.studentid,a.regdate,a.operator,a.returnname,a.chargerangeid,a.studyyear,a.returnnum as ''学费'',b.returnnum as ''住宿费'',');
      sql.Add('c.returnnum as ''书费'',d.returnnum as ''保险费'',e.returnnum as ''生活用品费'',f.returnnum as ''保证金'',');
      sql.Add('(a.returnnum+b.returnnum+c.returnnum+e.returnnum+f.returnnum+d.returnnum) as ''合计'' from(');
      sql.Add('select studentid,chargerangeid,studyyear,returnnum,regdate,operator,returnname');
      sql.Add('from returndetail');
      sql.Add('where chargeitemid=1) as a');
      sql.Add('left join');
      sql.Add('(select studentid,chargerangeid,studyyear,returnnum,regdate,operator,returnname');
      sql.Add('from returndetail');
      sql.Add('where chargeitemid=2) as b on a.studentid=b.studentid and a.chargerangeid=b.chargerangeid and a.studyyear=b.studyyear  and a.regdate=b.regdate');
      sql.Add('left join');
      sql.Add('(select studentid,chargerangeid,studyyear,returnnum,regdate,operator,returnname');
      sql.Add('from returndetail');
      sql.Add('where chargeitemid=3) as c on a.studentid=c.studentid and a.chargerangeid=c.chargerangeid and a.studyyear=c.studyyear  and a.regdate=c.regdate');
      sql.Add('left join');
      sql.Add('(select studentid,chargerangeid,studyyear,returnnum,regdate,operator,returnname');
      sql.Add('from returndetail');
      sql.Add('where chargeitemid=4) as d on a.studentid=d.studentid and a.chargerangeid=d.chargerangeid and a.studyyear=d.studyyear  and a.regdate=d.regdate');
      sql.Add('left join');
      sql.Add('(select studentid,chargerangeid,studyyear,returnnum,regdate,operator,returnname');
      sql.Add('from returndetail');
      sql.Add('where chargeitemid=5) as e on a.studentid=e.studentid and a.chargerangeid=e.chargerangeid and a.studyyear=e.studyyear  and a.regdate=e.regdate');
      sql.Add('left join');
      sql.Add('(select studentid,chargerangeid,studyyear,returnnum,regdate,operator,returnname');
      sql.Add('from returndetail');
      sql.Add('where chargeitemid=6) as f on a.studentid=f.studentid and a.chargerangeid=f.chargerangeid and a.studyyear=f.studyyear  and a.regdate=f.regdate');
      sql.Add(') 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 checkbox1.Checked then
      begin
        sql.add(' and a.regdate>='''+datetostr(datetimepicker1.Date)+' 00:00:00'+'''');
        sql.add(' and a.regdate<='''+datetostr(datetimepicker2.Date)+' 23:59:59'+'''');
      end;
      if trim(oper_edit.Text )<>'' then
        sql.Add(' and a.operator='''+trim(oper_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 trim(oper_edit.Text )<>'' then   sql.Add('')
      else if trim(name_Edit.Text)<>'' then  sql.Add('')
      else if trim(ClassCode_Edit.Text )<>'' then
        sql.Add(' group by student.studentname ')
      else if trim(Departmentid_Edit.Text )<>'' then
        sql.Add(' group by student.classcode ')
      else  
        sql.Add(' group by department.department ');

      sql.Add('union all select ');
      if trim(ClassCode_Edit.Text )<>'' then
      begin
        sql.Add('''合计'' as ''姓名'',') ;
        sql.add('sum(学费) as 学费,sum(住宿费) as 住宿费,sum(书费) as 书费,sum(保险费) as 保险费,sum(生活用品费) as 生活用品费,sum(保证金) as 保证金,sum(合计) as 合计 from ( ');
      end
      else if trim(Departmentid_Edit.Text )<>'' then
      begin
        sql.Add('''合计'' as ''班级'',');
        sql.add('sum(学费) as 学费,sum(住宿费) as 住宿费,sum(书费) as 书费,sum(保险费) as 保险费,sum(生活用品费) as 生活用品费,sum(保证金) as 保证金,sum(合计) as 合计 from ( ');
      end
      else
      begin
        sql.Add('''合计'' as ''院系'',');
        sql.add('sum(学费) as 学费,sum(住宿费) as 住宿费,sum(书费) as 书费,sum(保险费) as 保险费,sum(生活用品费) as 生活用品费,sum(保证金) as 保证金,sum(合计) as 合计 from ( ');
      end;
      sql.Add('select a.studentid,a.regdate,a.operator,a.returnname,a.chargerangeid,a.studyyear,a.returnnum as ''学费'',b.returnnum as ''住宿费'',');
      sql.Add('c.returnnum as ''书费'',d.returnnum as ''保险费'',e.returnnum as ''生活用品费'',f.returnnum as ''保证金'',');
      sql.Add('(a.returnnum+b.returnnum+c.returnnum+e.returnnum+f.returnnum+d.returnnum) as ''合计'' from(');
      sql.Add('select studentid,chargerangeid,studyyear,returnnum,regdate,operator,returnname');
      sql.Add('from returndetail');
      sql.Add('where chargeitemid=1) as a');
      sql.Add('left join');
      sql.Add('(select studentid,chargerangeid,studyyear,returnnum,regdate,operator,returnname');
      sql.Add('from returndetail');
      sql.Add('where chargeitemid=2) as b on a.studentid=b.studentid and a.chargerangeid=b.chargerangeid and a.studyyear=b.studyyear  and a.regdate=b.regdate');
      sql.Add('left join');
      sql.Add('(select studentid,chargerangeid,studyyear,returnnum,regdate,operator,returnname');
      sql.Add('from returndetail');
      sql.Add('where chargeitemid=3) as c on a.studentid=c.studentid and a.chargerangeid=c.chargerangeid and a.studyyear=c.studyyear  and a.regdate=c.regdate');
      sql.Add('left join');
      sql.Add('(select studentid,chargerangeid,studyyear,returnnum,regdate,operator,returnname');
      sql.Add('from returndetail');
      sql.Add('where chargeitemid=4) as d on a.studentid=d.studentid and a.chargerangeid=d.chargerangeid and a.studyyear=d.studyyear  and a.regdate=d.regdate');
      sql.Add('left join');
      sql.Add('(select studentid,chargerangeid,studyyear,returnnum,regdate,operator,returnname');
      sql.Add('from returndetail');
      sql.Add('where chargeitemid=5) as e on a.studentid=e.studentid and a.chargerangeid=e.chargerangeid and a.studyyear=e.studyyear  and a.regdate=e.regdate');
      sql.Add('left join');
      sql.Add('(select studentid,chargerangeid,studyyear,returnnum,regdate,operator,returnname');
      sql.Add('from returndetail');
      sql.Add('where chargeitemid=6) as f on a.studentid=f.studentid and a.chargerangeid=f.chargerangeid and a.studyyear=f.studyyear  and a.regdate=f.regdate');
      sql.Add(') 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 checkbox1.Checked then
      begin
        sql.add(' and a.regdate>='''+datetostr(datetimepicker1.Date)+' 00:00:00'+'''');
        sql.add(' and a.regdate<='''+datetostr(datetimepicker2.Date)+' 23:59:59'+'''');
      end;
      if trim(oper_edit.Text )<>'' then
        sql.Add(' and a.operator='''+trim(oper_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 )+'''');
    end;
    open;
   // memo1.Lines.Text :=sql.Text ;
  end;
end;

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

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

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

procedure Treturndetailform.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.