statunit.pas

学费管理系统,学校使用

        and (trim(ChargeRangeId_edit.text)='') and (trim(chargeitem_combobox.text)='')
         and (trim(oper_edit.Text )='') and not  checkbox1.Checked then
  begin
    MessageBox(application.handle,pchar('没有选择查询条件!'),'错误',MB_ICONWARNING+MB_OK);
    list_adoquery.close;
    clearcontent;
    exit;
  end;
  with oper_adoquery do
  begin
    close;
    sql.Clear;
    sql.add ('select * from editreg where 1=1 ');
    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( ChargeYearID_edit.Text )<>'') or (trim(Departmentid_Edit.Text )<>'')
      or (trim(Scholarid_edit.Text )<>'') or (trim(name_Edit.Text )<>'')
        or (trim(Length_ComboBox.Text )<>'') or (trim(ClassCode_Edit.Text )<>'') then
    begin
      sql.add('and studentid in (select id from student where 1=1');
      if trim( ChargeYearID_edit.Text )<>'' then
        sql.add(' and ChargeYearID='+trim( ChargeYearID_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(')');
    end;
    open;
    if not isempty then
      editnum:='sum(receive)'
    else
      editnum:='0';

    close;
    sql.Clear;
    sql.add ('select *  from returndetail where 1=1 ');
    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( ChargeYearID_edit.Text )<>'') or (trim(Departmentid_Edit.Text )<>'')
      or (trim(Scholarid_edit.Text )<>'') or (trim(name_Edit.Text )<>'')
        or (trim(Length_ComboBox.Text )<>'') or (trim(ClassCode_Edit.Text )<>'') then
    begin
      sql.add('and studentid in (select id from student where 1=1');
      if trim( ChargeYearID_edit.Text )<>'' then
        sql.add(' and ChargeYearID='+trim( ChargeYearID_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(')');
    end;
    open;
    if not isempty then
    begin
      returnnum:='sum(returnnum)';
      sumreturn:='退费金额';
    end
    else
    begin
      returnnum:='0';
      sumreturn:='0';
    end;


    close;
    sql.Clear;
    sql.add ('select * from decrease where 1=1');
    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( ChargeYearID_edit.Text )<>'') or (trim(Departmentid_Edit.Text )<>'')
      or (trim(Scholarid_edit.Text )<>'') or (trim(name_Edit.Text )<>'')
        or (trim(Length_ComboBox.Text )<>'') or (trim(ClassCode_Edit.Text )<>'') then
    begin
      sql.add('and studentid in (select id from student where 1=1');
      if trim( ChargeYearID_edit.Text )<>'' then
        sql.add(' and ChargeYearID='+trim( ChargeYearID_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(')');
    end;
    open;
    if not isempty then
    begin
      decrease:='sum(decreasenum)';
      sumdecrease:='减免金额';
    end
    else
    begin
      decrease:='0';
      sumdecrease:='0';
    end;

  end;

  with list_adoquery do
  begin
    close;
    sql.Clear;

    if trim(oper_edit.Text )<>'' then
    begin
      close;
      sql.clear;
      sql.add('select case a.chargeitemid when 1 then ''学费'' when 2 then ''住宿'' when 3 then ''书费'' ');
      sql.add('when 4 then ''保险'' when 5 then ''生活用品'' when 6 then ''保证金''end as ''项目'', ');
      sql.add('a.已收额 as ''已收金额'',减免金额,退费金额  from (   ');
      sql.add('select sum(receive) as ''已收额'',chargeitemid ');
      sql.add('from editreg where 1=1');
      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;
      sql.Add(' and operator='''+trim(oper_edit.Text)+'''');
      if trim(chargeitem_combobox.Text )<>'' then
        sql.Add(' and chargeitemid='+inttostr(chargeitem_combobox.itemindex));
      if (trim( ChargeYearID_edit.Text )<>'') or (trim(Departmentid_Edit.Text )<>'')
        or (trim(Scholarid_edit.Text )<>'') or (trim(name_Edit.Text )<>'')
          or (trim(Length_ComboBox.Text )<>'') or (trim(ClassCode_Edit.Text )<>'') then
      begin
        sql.add('and studentid in (select id from student where 1=1');
        if trim( ChargeYearID_edit.Text )<>'' then
          sql.add(' and ChargeYearID='+trim( ChargeYearID_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(')');
      end;
      sql.add(' group by chargeitemid) as a left join ');
      sql.add('(select '+returnnum+' as ''退费金额'',chargeitemid  from returndetail where 1=1');
      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;
      sql.Add(' and operator='''+trim(oper_edit.Text)+'''');
      if (trim( ChargeYearID_edit.Text )<>'') or (trim(Departmentid_Edit.Text )<>'')
        or (trim(Scholarid_edit.Text )<>'') or (trim(name_Edit.Text )<>'')
          or (trim(Length_ComboBox.Text )<>'') or (trim(ClassCode_Edit.Text )<>'') then
      begin
        sql.add('and studentid in (select id from student where 1=1');
        if trim( ChargeYearID_edit.Text )<>'' then
          sql.add(' and ChargeYearID='+trim( ChargeYearID_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(')');
      end;
      sql.add(' group by chargeitemid) as c on a.chargeitemid=c.chargeitemid');
      sql.add(' left join  ( select '+decrease+' as ''减免金额'',chargeitemid from decrease where 1=1');
      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;
      sql.Add(' and operator='''+trim(oper_edit.Text)+'''');
      if (trim( ChargeYearID_edit.Text )<>'') or (trim(Departmentid_Edit.Text )<>'')
        or (trim(Scholarid_edit.Text )<>'') or (trim(name_Edit.Text )<>'')
          or (trim(Length_ComboBox.Text )<>'') or (trim(ClassCode_Edit.Text )<>'') then
      begin
        sql.add('and studentid in (select id from student where 1=1');
        if trim( ChargeYearID_edit.Text )<>'' then
          sql.add(' and ChargeYearID='+trim( ChargeYearID_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(')');
      end;
      sql.add(' group by chargeitemid) as d on a.chargeitemid=d.chargeitemid union all ');
      sql.add('select ''合计'' as ''项目'',  ');
      sql.add('sum(已收额) as ''已收金额'',sum(减免金额),sum(退费金额)  from (  ');
      sql.add('select sum(receive) as ''已收额'',chargeitemid  ');
      sql.add('from editreg where 1=1  ');
      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;
      sql.Add(' and operator='''+trim(oper_edit.Text)+'''');
      if trim(chargeitem_combobox.Text )<>'' then
        sql.Add(' and chargeitemid='+inttostr(chargeitem_combobox.itemindex));
      if (trim( ChargeYearID_edit.Text )<>'') or (trim(Departmentid_Edit.Text )<>'')
        or (trim(Scholarid_edit.Text )<>'') or (trim(name_Edit.Text )<>'')
          or (trim(Length_ComboBox.Text )<>'') or (trim(ClassCode_Edit.Text )<>'') then
      begin
        sql.add('and studentid in (select id from student where 1=1');
        if trim( ChargeYearID_edit.Text )<>'' then
          sql.add(' and ChargeYearID='+trim( ChargeYearID_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(')');
      end;
      sql.add(' group by chargeitemid) as a left join ');
      sql.add('(select '+returnnum+' as ''退费金额'',chargeitemid  from returndetail where 1=1');
      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;
      sql.Add(' and operator='''+trim(oper_edit.Text)+'''');
      if (trim( ChargeYearID_edit.Text )<>'') or (trim(Departmentid_Edit.Text )<>'')
        or (trim(Scholarid_edit.Text )<>'') or (trim(name_Edit.Text )<>'')
          or (trim(Length_ComboBox.Text )<>'') or (trim(ClassCode_Edit.Text )<>'') then
      begin
        sql.add('and studentid in (select id from student where 1=1');
        if trim( ChargeYearID_edit.Text )<>'' then
          sql.add(' and ChargeYearID='+trim( ChargeYearID_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(')');
      end;
      sql.add(' group by chargeitemid) as c on a.chargeitemid=c.chargeitemid');
      sql.add(' left join( select '+decrease+' as ''减免金额'',chargeitemid from decrease where 1=1');
      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;
      sql.Add(' and operator='''+trim(oper_edit.Text)+'''');
      if (trim( ChargeYearID_edit.Text )<>'') or (trim(Departmentid_Edit.Text )<>'')
        or (trim(Scholarid_edit.Text )<>'') or (trim(name_Edit.Text )<>'')