unit_queryreckoning.pas

delphi开发的中国移动大客户管理系统,后台数据库为oracle

unit Unit_QueryReckoning;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Unit_custombrowsebase, ImgList, Menus, Db, DBTables, Grids, DBGrids,
  ComCtrls, TFlatCheckBoxUnit, TFlatSpinEditUnit, ToolWin, StdCtrls,
  TFlatEditUnit, TFlatComboBoxUnit, TFlatButtonUnit, ExtCtrls,
  TFlatRadioButtonUnit;

type
  Tfrm_QueryReckoning = class(Tform_custombrowsebase)
    Label8: TLabel;
    Label9: TLabel;
    cbGR_talk: TFlatCheckBox;
    spiGR_talkYearBegin: TFlatSpinEditInteger;
    spiGR_talkYearEnd: TFlatSpinEditInteger;
    Label12: TLabel;
    Label13: TLabel;
    spiGR_talkMonBegin: TFlatSpinEditInteger;
    spiGR_talkMonEnd: TFlatSpinEditInteger;
    Label14: TLabel;
    Label15: TLabel;
    Label16: TLabel;
    Label17: TLabel;
    cbJT_talk: TFlatCheckBox;
    spiJT_talkYearBegin: TFlatSpinEditInteger;
    spiJT_talkYearEnd: TFlatSpinEditInteger;
    Label19: TLabel;
    Label20: TLabel;
    spiJT_talkMonBegin: TFlatSpinEditInteger;
    spiJT_talkMonEnd: TFlatSpinEditInteger;
    Label21: TLabel;
    Label22: TLabel;
    procedure cbJT_talkClick(Sender: TObject);
    procedure spiGR_talkYearBeginChange(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure cbGR_talkClick(Sender: TObject);
    procedure FlatButton3Click(Sender: TObject);
    procedure FlatButton2Click(Sender: TObject);
  private
    { Private declarations }
  protected
  	lGR_WriteOffType:string;
        lJT_WriteOffType:string;
        procedure pro_initcombobox(sender:tobject);override;
        procedure pro_initother(sender:tobject);override;
        procedure pro_GR_getid(sender:tobject);override;
        procedure pro_GR_OpenDataSet(sender:tobject;strsql:string);override;
        procedure pro_GR_getsqlwhere(sender:tobject);override;
        procedure pro_GR_checkcondition(sender:tobject);override;
        procedure pro_JT_getid(sender:tobject);override;
        procedure pro_JT_OpenDataSet(sender:tobject;strsql:string);override;
        procedure pro_JT_getsqlwhere(sender:tobject);override;
        procedure pro_JT_checkcondition(sender:tobject);override;

  public
    { Public declarations }
  end;

var
  frm_QueryReckoning: Tfrm_QueryReckoning;

implementation
  uses dmmain,PrintFrmForm;
  Function Fun_ExportData(pFileName:String;pReportTitle:String;pObjectSource:TObject):Boolean;stdcall;external'winfun.dll';

{$R *.DFM}

procedure Tfrm_QueryReckoning.pro_GR_checkcondition(sender: tobject);
begin
  inherited;
if cbGR_Talk.Checked then
    begin
        if (spiGR_talkYearBegin.Value=spiGR_talkYearEnd.Value) and (spiGR_talkMonBegin.Value>spiGR_talkMonEnd.Value) then
            begin
//                fun_messagebox('话费时间段开始日期不能大于结束日期');
                GR_condition:=false;
            end;
    end;
end;

procedure Tfrm_QueryReckoning.pro_GR_getid(sender: tobject);
begin
  inherited;

end;

procedure Tfrm_QueryReckoning.pro_GR_getsqlwhere(sender: tobject);
var
    MonthBeginstr:string;
    MonthEndStr:string;
    tmpstr,tmpstr_begin,tmpstr_end:string;
begin
  inherited;
tmpstr_begin:=inttostr(spiGR_talkMonBegin.value);
if length(tmpstr_begin)=1 then
   tmpstr_begin:='0'+tmpstr_begin;
tmpstr_end:=inttostr(spiGR_talkMonEnd.value);
if length(tmpstr_end)=1 then
   tmpstr_end:='0'+tmpstr_end;
MonthBeginstr:=inttostr(spiGR_talkYearBegin.Value)+tmpstr_begin;
MonthEndStr:=inttostr(spiGR_talkYearEnd.Value)+tmpstr_end;

//
if edGR_SID.Text<>'' then
      tmpstr:=tmpstr+' and '+'t.SID='+''''+edGR_SID.Text+'''';
if lGR_cityno<>'' then
        tmpstr:=tmpstr+' and '+'t.cityno='+lGR_cityno;
if lGR_off_no_home<>'' then
        tmpstr:=tmpstr+' and '+'t.off_no_home='+lGR_off_no_home;
if lGR_USER_ZJBS<>'' then
        tmpstr:=tmpstr+' and '+'t.productno='+''''+lGR_USER_ZJBS+'''';
//
if cbGR_talk.Checked then
	//tmpstr:=tmpstr+' and '+'to_date(t.Calcmonth,''yyyy-mm'') between to_date('''+monthBeginStr+''',''yyyy-mm'') and to_date('''+MonthEndStr+''',''yyyy-mm'')';
	tmpstr:=tmpstr+' and '+'t.Calcmonth>='+MonthBeginstr+' and '+'t.Calcmonth<='+MonthEndStr;
GR_sqlwhere:=GR_sqlwhere+tmpstr;
end;

procedure Tfrm_QueryReckoning.pro_GR_OpenDataSet(sender: tobject;
  strsql: string);
begin
with browse_normal do
    begin
        close;
        sql.clear;
        sql.add('select * from T_V_GR_QueryReckoning');
        sql.Add('where 1=1');
        sql.add(strsql);
        open;
    end;

end;

procedure Tfrm_QueryReckoning.pro_initcombobox(sender: tobject);
begin
  inherited;

end;

procedure Tfrm_QueryReckoning.pro_initother(sender: tobject);
var
    iyear,imonth,iday:word;
begin
  inherited;
decodedate(now,iyear,imonth,iday);
spiGR_talkYearBegin.Value:=iyear;
spiGR_talkYearEnd.Value:=iyear;
spiGR_talkMonBegin.Value:=iMonth;
spiGR_talkMonEnd.Value:=iMonth;
spiJT_talkYearBegin.Value:=iyear;
spiJT_talkYearEnd.Value:=iyear;
spiJT_talkMonBegin.Value:=iMonth;
spiJT_talkMonEnd.Value:=iMonth;

end;

procedure Tfrm_QueryReckoning.pro_JT_checkcondition(sender: tobject);
begin
  inherited;
if cbJT_Talk.Checked then
    begin
        if (spiJT_talkYearBegin.Value=spiJT_talkYearEnd.Value) and (spiJT_talkMonBegin.Value>spiJT_talkMonEnd.Value) then
            begin
//                fun_messagebox('话费时间段开始日期不能大于结束日期');
                JT_condition:=false;
            end;
    end;
end;

procedure Tfrm_QueryReckoning.pro_JT_getid(sender: tobject);
begin
  inherited;

end;

procedure Tfrm_QueryReckoning.pro_JT_getsqlwhere(sender: tobject);
var
    tmpstr,tmpstr_begin,tmpstr_end,MonthBeginstr,MonthEndStr:string;
begin
  inherited;
tmpstr_begin:=inttostr(spiJT_talkMonBegin.value);
if length(tmpstr_begin)=1 then
   tmpstr_begin:='0'+tmpstr_begin;
tmpstr_end:=inttostr(spiJT_talkMonEnd.value);
if length(tmpstr_end)=1 then
   tmpstr_end:='0'+tmpstr_end;
MonthBeginstr:=inttostr(spiJT_talkYearBegin.Value)+tmpstr_begin;
MonthEndStr:=inttostr(spiJT_talkYearEnd.Value)+tmpstr_end;
//
if (lJT_JTYHDM<>'') then
        tmpstr:=tmpstr+' and '+'t.JTYHDM='+lJT_JTYHDM;
if lJT_cityno<>'' then      //地市
        tmpstr:=tmpstr+' and '+'t.cityno='+lJT_cityno;
if lJT_off_no_home>'' then  //局向
        tmpstr:=tmpstr+' and '+'t.off_no_home='+lJT_off_no_home;
if lJT_GH<>'' then      //
        tmpstr:=tmpstr+' and '+'t.WK_NO='+''''+lJT_GH+'''';
//
if cbJT_talk.Checked then
//	tmpstr:=tmpstr+' and '+'to_date(t.Calcmonth,''yyyy-mm'') between to_date('''+monthBeginStr+''',''yyyy-mm'') and to_date('''+MonthEndStr+''',''yyyy-mm'')';
	tmpstr:=tmpstr+' and '+'t.Calcmonth>='+MonthBeginstr+' and '+'t.Calcmonth<='+MonthEndStr;
JT_sqlwhere:=JT_sqlwhere+tmpstr;

end;

procedure Tfrm_QueryReckoning.pro_JT_OpenDataSet(sender: tobject;
  strsql: string);
begin
with browse_normal do
    begin
        close;
        sql.clear;
        sql.add('select * from T_V_JT_QueryReckoning');
        sql.Add('where 1=1');
        sql.add(strsql);
        open;
    end;

end;


procedure Tfrm_QueryReckoning.cbJT_talkClick(Sender: TObject);
begin
  inherited;
if cbJT_talk.Checked then
    begin
      spiJT_talkYearBegin.colorflat:=clwindow;
      spiJT_talkYearEnd.colorflat:=clwindow;
      spiJT_talkMonBegin.colorflat:=clwindow;
      spiJT_talkMonEnd.colorflat:=clwindow;
      spiJT_talkYearBegin.Enabled:=true;
      spiJT_talkYearEnd.Enabled:=true;
      spiJT_talkMonBegin.Enabled:=true;
      spiJT_talkMonEnd.Enabled:=true;
    end
else
    begin
      spiJT_talkYearBegin.colorflat:=clActiveBorder;
      spiJT_talkYearEnd.colorflat:=clActiveBorder;
      spiJT_talkMonBegin.colorflat:=clActiveBorder;
      spiJT_talkMonEnd.colorflat:=clActiveBorder;
      spiJT_talkYearBegin.Enabled:=false;
      spiJT_talkYearEnd.Enabled:=false;
      spiJT_talkMonBegin.Enabled:=false;
      spiJT_talkMonEnd.Enabled:=false;
    end;
end;

procedure Tfrm_QueryReckoning.spiGR_talkYearBeginChange(Sender: TObject);
begin
  inherited;
if sender=spiGR_TalkYearBegin then
    begin
        spiGR_TalkYearEnd.MinValue:=spiGR_TalkYearBegin.Value;
        if spiGR_TalkYearEnd.Value<spiGR_TalkYearBegin.Value then
              spiGR_TalkYearEnd.Value:=spiGR_TalkYearBegin.Value;
    end;
if sender=spiJT_TalkYearBegin then
    begin
        spiJT_TalkYearEnd.MinValue:=spiJT_TalkYearBegin.Value;
        if spiJT_TalkYearEnd.Value<spiJT_TalkYearBegin.Value then
              spiJT_TalkYearEnd.Value:=spiJT_TalkYearBegin.Value;
    end;
if sender=spiGR_TalkMonBegin then
        if (spiGR_TalkYearEnd.Value=spiGR_TalkYearBegin.Value) then
           if (spiGR_TalkMonEnd.Value<=spiGR_TalkMonBegin.Value) then
              	spiGR_TalkMonEnd.Value:=spiGR_TalkMonBegin.Value;
if sender=spiJT_TalkMonBegin then
        if (spiJT_TalkYearEnd.Value=spiJT_TalkYearBegin.Value) then
           if (spiJT_TalkMonEnd.Value<=spiJT_TalkMonBegin.Value) then
              	spiJT_TalkMonEnd.Value:=spiJT_TalkMonBegin.Value;

end;

procedure Tfrm_QueryReckoning.FormShow(Sender: TObject);
begin
  inherited;
cbGR_talk.OnClick(self);
cbJT_talk.OnClick(self);
end;

procedure Tfrm_QueryReckoning.cbGR_talkClick(Sender: TObject);
begin
  inherited;
if cbGR_talk.Checked then
    begin
      spiGR_talkYearBegin.colorflat:=clwindow;
      spiGR_talkYearEnd.colorflat:=clwindow;
      spiGR_talkMonBegin.colorflat:=clwindow;
      spiGR_talkMonEnd.colorflat:=clwindow;
      spiGR_talkYearBegin.Enabled:=true;
      spiGR_talkYearEnd.Enabled:=true;
      spiGR_talkMonBegin.Enabled:=true;
      spiGR_talkMonEnd.Enabled:=true;
    end
else
    begin
      spiGR_talkYearBegin.colorflat:=clActiveBorder;
      spiGR_talkYearEnd.colorflat:=clActiveBorder;
      spiGR_talkMonBegin.colorflat:=clActiveBorder;
      spiGR_talkMonEnd.colorflat:=clActiveBorder;
      spiGR_talkYearBegin.Enabled:=false;
      spiGR_talkYearEnd.Enabled:=false;
      spiGR_talkMonBegin.Enabled:=false;
      spiGR_talkMonEnd.Enabled:=false;
    end;
end;

procedure Tfrm_QueryReckoning.FlatButton3Click(Sender: TObject);
begin
  inherited;
  if browse_normal.Active then
  if not (browse_normal.Bof and browse_normal.Eof) then
  begin
    if PageControl1.ActivePageIndex=0 then
    Fun_ExportData('大客户话费个人客户','大客户话费个人客户',dbgGR)
    else
    Fun_ExportData('大客户话费集团客户','大客户话费集团客户',dbgJT)
  end;

end;

procedure Tfrm_QueryReckoning.FlatButton2Click(Sender: TObject);
begin
  inherited;
  if browse_normal.Active then
  if not (browse_normal.Bof and browse_normal.Eof) then
  begin
    if PageControl1.ActivePageIndex=0 then
    begin
      try
        PrintDbGrid(dbgGR.datasource.dataset,dbgGR,'大客户话费个人客户');
      except
        Application.MessageBox('系统在执行过程中发生错误！','提示',MB_ICONINFORMATION);
        exit;
      end;//try
    end
    else
      begin
        try
          PrintDbGrid(dbgJT.datasource.dataset,dbgJT,'大客户话费集团客户');
        except
          Application.MessageBox('系统在执行过程中发生错误！','提示',MB_ICONINFORMATION);
          exit;
        end;//try
      end;
  end;
end;

end.