statunit.pas

学费管理系统,学校使用

          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');
    end
    else
    begin
      sql.add('select case quantum.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('应收金额 as 应收金额,已收金额 as 已收金额,退费金额,减免金额,应收金额-减免金额-(已收金额-退费金额) as 欠费金额 from(');
      sql.add('select chargeitemid,sum(quantum) as ''应收金额'' from ( ');
      sql.add('select * from editreg where 1=1');
      if trim(chargeitem_combobox.Text )<>'' then
        sql.Add(' and chargeitemid='+inttostr(chargeitem_combobox.itemindex));
      sql.add('and regdate in (select max(regdate) 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( 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 studentid,chargerangeid))as a group by chargeitemid) as quantum  ');
      sql.add('left join(select sum(receive) as ''已收金额'',chargeitemid 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( 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 receive on receive.chargeitemid=quantum.chargeitemid ');
      sql.add('left join(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;
      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 returnnum on returnnum.chargeitemid=quantum.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;
      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 decrease on decrease.chargeitemid=quantum.chargeitemid');
      sql.add('union all select ''合计'' as ''项目'', ');
      sql.add('sum(应收金额) as 应收金额,sum(已收金额) as 已收金额,sum(退费金额),sum(减免金额),sum(应收金额-'+sumdecrease+'-(已收金额-'+sumreturn+')) as 欠费金额 from( ');
      sql.add('select chargeitemid,sum(quantum) as ''应收金额'' from ( ');
      sql.add('select * from editreg where 1=1');
      if trim(chargeitem_combobox.Text )<>'' then
        sql.Add(' and chargeitemid='+inttostr(chargeitem_combobox.itemindex));
      sql.add('and regdate in (select max(regdate) 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( 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 studentid,chargerangeid))as a group by chargeitemid) as quantum');
      sql.add('left join(select sum(receive) as ''已收金额'',chargeitemid 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( 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 receive on receive.chargeitemid=quantum.chargeitemid');
      sql.add('left join(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;
      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 returnnum on returnnum.chargeitemid=quantum.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;
      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 decrease on decrease.chargeitemid=quantum.chargeitemid ');
    end;
    open;
    //memo1.Lines.Text :=sql.Text ;
  end;
end;

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

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

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

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