decreasedetailunit.pas

学费管理系统,学校使用

procedure Tdecreasedetailform.oper_comboboxChange(Sender: TObject);
begin
  Oper_Edit.Text :='';
  if trim(oper_combobox.text)='' then exit;
  Oper_Edit.Text :=NewOper[oper_combobox.itemindex-1].code;
end;

procedure Tdecreasedetailform.clear_bitbtnClick(Sender: TObject);
begin

  list_adoquery.Close;
  clearcontent;
  clear_bitbtn.Enabled :=false ;
  output_bitbtn.Enabled:=false ;
end;

procedure Tdecreasedetailform.DataSource1DataChange(Sender: TObject; Field: TField);
begin
  clear_bitbtn.Enabled :=not list_adoquery.IsEmpty ;
  output_bitbtn.Enabled:=not list_adoquery.IsEmpty ;
end;

procedure Tdecreasedetailform.CloseWindow(var Msg: TMessage);
begin
  FreeAndNil(decreasedetailform);
end;

procedure Tdecreasedetailform.close_bitbtnClick(Sender: TObject);
begin
  self.Close;
end;

procedure Tdecreasedetailform.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  action:=cafree;
  postmessage(handle,wm_user,0,0);
end;

procedure Tdecreasedetailform.clearcontent;

begin
  ChargeYearID_edit.Text :='';
  ChargeRangeId_edit.Text :='';
  Departmentid_Edit.Text :='';
  Department_ComboBox.ItemIndex:=-1;
  Scholarid_edit.Text :='';
  Scholar_ComboBox.Itemindex:=-1;
  name_Edit.Text :='';
  Length_ComboBox.ItemIndex:=-1;
  ClassCode_Edit.Text :='';
  oper_combobox.ItemIndex:=-1;
  oper_edit.Text :='';
  checkbox1.Checked :=false;
  datetimepicker1.Date:=date;
  datetimepicker2.Date:=date;
end;

procedure Tdecreasedetailform.FormShow(Sender: TObject);
begin
  clearcontent;
end;

procedure Tdecreasedetailform.search_bitbtnClick(Sender: TObject);
begin
  if (trim( ChargeYearID_edit.Text )='') and (trim(Departmentid_Edit.Text )='')
    and (trim(Scholarid_edit.Text )='') and (trim(name_Edit.Text )='')
      and (trim(Length_ComboBox.Text )='') and (trim(ClassCode_Edit.Text )='')
        and (trim(ChargeRangeId_edit.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 list_adoquery do
  begin
    close;
    sql.clear;

    sql.Add('select ');
    if trim(oper_edit.Text )<>'' then
    begin
      sql.Add('a.regdate as ''时间'',student.studentname as ''姓名'',');
      sql.add('a.decreasecode as ''减免单号'',a.decreasename as ''减免名称'',a.decreasenum as ''减免金额'' from ( ');
    end
    else if trim(name_Edit.Text)<>'' then
    begin
      sql.Add('a.decreasename as ''减免名称'',a.decreasecode as ''减免单号'',a.decreasenum as ''减免金额'',');
      sql.add('a.regdate as ''时间'',operator.name as ''操作员'' from ( ');
    end
    else if trim(ClassCode_Edit.Text )<>'' then
    begin
      sql.Add('student.studentname as ''姓名'',sum(a.decreasenum) as ''减免金额'' from ( ');
    end
    else if trim(Departmentid_Edit.Text )<>'' then
    begin
      sql.Add('student.classcode as ''班级'',sum(a.decreasenum) as ''减免金额'' from ( ');
    end
    else
    begin
      sql.Add(' department.department as ''院系'',sum(a.decreasenum) as ''减免金额'' from ( ');
    end;

    sql.Add('select studentid,chargerangeid,studyyear,decreasenum,decreasename,decreasecode,operator,regdate');
    sql.Add('from decrease where decreasenum>0) as a,student,operator,department where a.studentid=student.id ');
    sql.Add('and a.operator=operator.code 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');
    sql.Add('select ');
    if trim(oper_edit.Text )<>'' then
    begin
      sql.Add(''''+datetimetostr(now)+''' as ''时间'',''合计'' as ''姓名'',');
      sql.add(''''' as ''减免单号'','''' as ''减免名称'',sum(a.decreasenum )as ''减免金额'' from ( ');
    end
    else if trim(name_Edit.Text)<>'' then
    begin
      sql.Add('''合计'' as ''减免名称'','''' as ''减免单号'',sum(a.decreasenum) as ''减免金额'',');
      sql.add(''''+datetimetostr(now)+''' as ''时间'','''' as ''操作员'' from ( ');
    end
    else if trim(ClassCode_Edit.Text )<>'' then
    begin
      sql.Add('''合计'' as ''姓名'',sum(a.decreasenum) as ''减免金额'' from ( ');
    end
    else if trim(Departmentid_Edit.Text )<>'' then
    begin
      sql.Add('''合计'' as ''班级'',sum(a.decreasenum) as ''减免金额'' from ( ');
    end
    else
    begin
      sql.Add(' ''合计'' as ''院系'',sum(a.decreasenum) as ''减免金额'' from ( ');
    end;

    sql.Add('select studentid,chargerangeid,studyyear,decreasenum,decreasename,decreasecode,operator,regdate');
    sql.Add('from decrease ) as a,student,operator,department where a.studentid=student.id ');
    sql.Add('and a.operator=operator.code 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 )+'''');
    //memo1.Lines.Text :=sql.Text ;
    open;
  end;
end;

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

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

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

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