createcjdatafm.pas

一个电力部门的催缴费用系统

unit CreateCJDataFM;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, Buttons,db,dbTables,ChildFM, ComCtrls, ExtCtrls;

type
  TFM_CreateCJData = class(Tfm_Child)
    pnl_Client: TPanel;
    probar_CreateCj: TProgressBar;
    btn_CreateCjData: TBitBtn;
    btn_Cancel: TBitBtn;
    ch_BakCJNum: TCheckBox;
    Button1: TButton;
    GroupBox1: TGroupBox;
    rb_Day: TRadioButton;
    rb_Month: TRadioButton;
    ch_AutoImport: TCheckBox;
    GroupBox2: TGroupBox;
    ch_AutoUpdTodayNum: TCheckBox;
    edt_Day: TEdit;
    Label1: TLabel;
    Label2: TLabel;
    UpDown1: TUpDown;
    Label3: TLabel;
    Dt_ImportData: TDateTimePicker;
    Dt_UpdTodayNum: TDateTimePicker;
    Label4: TLabel;
    btn_SaveCfg: TBitBtn;
    lbl_Caption: TLabel;
    Label5: TLabel;
    edt_Qfee: TEdit;
    Label6: TLabel;
    procedure btn_CreateCjDataClick(Sender: TObject);
    procedure FormResize(Sender: TObject);
    procedure btn_CancelClick(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure btn_SaveCfgClick(Sender: TObject);
    procedure ch_AutoImportClick(Sender: TObject);
    procedure ch_AutoUpdTodayNumClick(Sender: TObject);
    procedure edt_QfeeKeyPress(Sender: TObject; var Key: Char);
    procedure rb_DayClick(Sender: TObject);
    procedure rb_MonthClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
  private
    { Private declarations }
    bHalt,bstartImport:Boolean;
    function QryUserQFee(ACZH,CArea:string):single;
    procedure BackupCJNum;
    procedure ImportCjUser;
    procedure RestoreCjNum;
    function GetTimesPerMonth(UserType:string): Integer;
  public
    { Public declarations }
    constructor CreateForm(AOwner: TComponent; AParent: TWinControl;AbstartImport:Boolean); overload;
  end;

var
  FM_CreateCJData: TFM_CreateCJData;

implementation
uses SystemDM,SourceDM,SystemPH;
{$R *.DFM}
constructor TFM_CreateCJData.CreateForm(AOwner: TComponent; AParent: TWinControl;AbstartImport:Boolean);
begin
  bstartImport:=AbstartImport;
  inherited Create(AOwner,AParent);
end;
function TFM_CreateCJData.QryUserQFee(ACZH,CArea:string):single;
var
//  AnyQuery:TQuery;
//  sCJBYBBZ:string;
  flQfee:Single;
begin

  flQfee:=0;
  with TQuery.Create(nil)do
  try
   DataBaseName := SysDM.DBMain.DatabaseName;
   Sql.Text := Format('Select BALANCE_FEE From ELECFEE_USERINFO Where USER_ACCOUNT = ''%s'' and USER_AREA = ''%s''',[ACZH,CArea]);
   Open;
   if FieldByName('BALANCE_FEE').AsFloat < 0 then
    Result := Abs(FieldByName('BALANCE_FEE').AsFloat);
  finally
   Free;
  end;
  {AnyQuery:=nil;
  if copy(ACZH,2,1)='1' then
    AnyQuery:=DM_Source.Qr_TdTemp ;
  if copy(ACZH,2,1)='2' then
    AnyQuery:=DM_Source.Qr_TxTemp ;
  if copy(ACZH,2,1)='3' then
    AnyQuery:=DM_Source.Qr_lsTemp ;
  if copy(ACZH,1,2)='39' then
    AnyQuery:=DM_Source.Qr_DhTemp ;
  if Assigned(AnyQuery) then
  begin
    AnyQuery.Close;
    AnyQuery.Sql.Text:='select CJBYBBZ from d_cbfx'
                      +' where CZH = '''+ ACZH +'''';
    AnyQuery.Prepare ;
    try
      AnyQuery.open;
      if not AnyQuery.IsEmpty then
      begin
        sCJBYBBZ:=AnyQuery.FieldbyName('CJBYBBZ').AsString;
        if (Copy(sCJBYBBZ,2,1)='1') or (Copy(sCJBYBBZ,2,1)='4') then
        begin
          if SysDM.SQL_Querys(AnyQuery,'select nxyye from d_cxyeb where CZH ='''+ACZH+'''') then
          begin
            if AnyQuery.FieldByName('nxyye').AsFloat<0 then
              flQfee:=ABS(AnyQuery.FieldByName('nxyye').AsFloat);
          end;
        end
        else if (Copy(sCJBYBBZ,2,1)='2') or (Copy(sCJBYBBZ,2,1)='3') then
        begin
          if SysDM.SQL_Querys(AnyQuery,'select NSSHJ-NYSHJ nxyye from d_dfsf where CZH ='''+ACZH+'''') then
          begin
            if AnyQuery.FieldByName('nxyye').AsFloat<0 then
              flQfee:=ABS(AnyQuery.FieldByName('nxyye').AsFloat);
          end;
        end
      end;
    except
    end;
  end;
  Result:=flQfee;}
end;
procedure TFM_CreateCJData.BackupCJNum;
var
  sSql:String;
begin
  lbl_Caption.Caption :='正在备份历史的催缴次数...';
  SysDM.SQL_Exec(SysDM.Qr_SysTemp,'truncate table CJ_Table_Num',true);
  if SysDM.SQL_Querys(SysDM.Qr_SysTemp,
             'Select CZH,CJ_Num,Today_Num from CJ_Table') then
  begin
    proBar_CreateCJ.Position :=0;
    proBar_CreateCj.Max := SysDM.Qr_sysTemp.RecordCount;
    while (not SysDM.Qr_sysTemp.Eof) and (not bHalt) do
    begin
      sSql:='insert into Cj_table_Num(CZH,Cj_Num,Today_Num) Values('''
           +SysDM.Qr_sysTemp.FieldbyName('CZH').AsString+''','
           +SysDM.Qr_sysTemp.FieldbyName('Cj_Num').AsString+','
           +SysDM.Qr_sysTemp.FieldbyName('Today_Num').AsString+')';
      SysDM.SQL_Exec(SysDM.Qr_Temp,sSql,true);
      proBar_CreateCJ.Position :=proBar_CreateCJ.Position+1;
      SysDM.Qr_sysTemp.Next ;
      Application.ProcessMessages ;
    end;
  end;
end;
procedure TFM_CreateCJData.ImportCjUser;
var
  flQfee:Single;
  sSql:string;
  iTimesPerMonth:integer;
begin
  lbl_Caption.Caption :='正在导入催缴用户...';
  iTimesPerMonth:=3;  //default 3
 { if SysDM.SQL_Querys(SysDM.Qr_SysTemp,
     'Select TimesPerMonth,MaxTimesPerDay from CallOutTimeTable where ID=1') then
  begin
    iTimesPerMonth:=SysDM.Qr_sysTemp.Fieldbyname('TimesPerMonth').AsInteger;
  end; }

  SysDM.SQL_Exec(SysDM.Qr_SysTemp,'truncate table CJ_Table',true);
  if SysDM.SQL_Querys(SysDM.Qr_SysTemp,
     'select CZH,PhoneCode,UserName,User_Type,carea from CjUserList') then
  begin
    proBar_CreateCJ.Position :=0;
    proBar_CreateCj.Max := SysDM.Qr_sysTemp.RecordCount;
    while (not SysDM.Qr_SysTemp.Eof) and (not bHalt) do
    begin
      flQfee:=QryUserQfee(SysDM.Qr_SysTemp.FieldbyName('CZH').AsString,SysDM.Qr_SysTemp.FieldbyName('Carea').AsString);
      if flQfee > StrToIntDef(trim(edt_Qfee.text),0) then
      begin
        iTimesPerMonth := GetTimesPerMonth(SysDM.Qr_SysTemp.FieldbyName('User_Type').AsString);
        sSql:='insert into CJ_Table(Telephone,CZH,UserName,TimeTable,Qfee,YCJ_Num,CJ_Num) values('''
             +SysDM.Qr_SysTemp.FieldbyName('PhoneCode').AsString+''','''
             +SysDM.Qr_sysTemp.FieldbyName('CZH').AsString+''','''
             +SysDM.Qr_sysTemp.FieldbyName('UserName').AsString+''','
             +SysDM.Qr_sysTemp.FieldbyName('User_Type').AsString + ',' //'1,'
             +floatToStr(flQfee)+','
             +IntToStr(iTimesPerMonth)+','
             +IntToStr(iTimesPerMonth)+')';
        SysDM.SQL_Exec(SysDM.Qr_Temp,sSql,true);
      end;
      SysDM.Qr_SysTemp.Next ;
      proBar_CreateCJ.Position :=proBar_CreateCJ.Position+1;
      Application.ProcessMessages ;

    end;
  end;
end;
procedure TFM_CreateCJData.RestoreCjNum;
var
  sSql:String;
begin
  lbl_Caption.Caption :='正在还原历史催缴次数...';
  sSql:='Update Cj_Table set Cj_Num=CJ_Table_Num.Cj_Num,Today_Num=Cj_Table_Num.Today_Num'
       +' from Cj_Table_Num where Cj_Table.CZH=Cj_Table_Num.CZH';
  SysDM.SQL_Exec(SysDM.Qr_SysTemp,sSql,true);

end;

procedure TFM_CreateCJData.btn_CreateCjDataClick(Sender: TObject);
begin
  bHalt:=false;
  if ch_BakCJNum.Checked then BackUpCjNum;
  ImportCjUser;
  if ch_BakCJNum.Checked then RestoreCjNum;
  lbl_Caption.Caption :='导入完毕';
end;

procedure TFM_CreateCJData.FormResize(Sender: TObject);
begin
  inherited;
  pnl_Client.Top := (Height - pnl_Client.Height) div 2;
  pnl_Client.Left := (Width - pnl_Client.Width) div 2;
end;

procedure TFM_CreateCJData.btn_CancelClick(Sender: TObject);
begin
  inherited;
  bHalt:=true;
end;

procedure TFM_CreateCJData.Button1Click(Sender: TObject);
var
  sUserName,sCZH,sSql,sSqlIns:string;
  iCZHNum:integer;
begin
  inherited;
  if SysDM.SQL_Querys(SysDM.Qr_Temp,
     'Select UserName from CjUserList where CZH is NULL') then
  begin
    while not SysDM.Qr_Temp.Eof do
    begin
      iCZHNum:=0;
      sUserName:=SysDM.Qr_Temp.FieldbyName('UserName').AsString;
      sSql:='Select CZH from d_jbyb where CHM='''+sUserName+'''';
      with DM_Source do
      begin
        if SysDM.SQL_Querys(Qr_TdTemp,sSql) then
        begin
          while not Qr_TdTemp.Eof do
          begin
            sCZH:=Qr_TdTemp.FieldbyName('CZH').AsString;
            sSqlIns:='insert into tmpcjuserlist(CZH,UserName) Values('''
                    + Qr_TdTemp.FieldbyName('CZH').AsString +''','''
                    + sUserName +''')';
            SysDM.SQL_Exec(SysDM.Qr_SysTemp,sSqlIns);
            Qr_TdTemp.Next ;
            inc(iCZHNum);
          end;
        end;
        if SysDM.SQL_Querys(Qr_TxTemp,sSql) then
        begin
          while not Qr_TxTemp.Eof do
          begin
            sCZH:=Qr_TxTemp.FieldbyName('CZH').AsString;
            sSqlIns:='insert into tmpcjuserlist(CZH,UserName) Values('''
                    + Qr_TxTemp.FieldbyName('CZH').AsString +''','''
                    + sUserName +''')';
            SysDM.SQL_Exec(SysDM.Qr_SysTemp,sSqlIns);
            Qr_TxTemp.Next ;
            inc(iCZHNum);
          end;
        end;
        if SysDM.SQL_Querys(Qr_LsTemp,sSql) then
        begin
          while not Qr_LsTemp.Eof do
          begin
            sCZH:=Qr_LsTemp.FieldbyName('CZH').AsString;
            sSqlIns:='insert into tmpcjuserlist(CZH,UserName) Values('''
                    + Qr_LsTemp.FieldbyName('CZH').AsString +''','''
                    + sUserName +''')';
            SysDM.SQL_Exec(SysDM.Qr_SysTemp,sSqlIns);
            Qr_LsTemp.Next ;
            inc(iCZHNum);
          end;
        end;
        if SysDM.SQL_Querys(Qr_DhTemp,sSql) then
        begin
          while not Qr_DhTemp.Eof do
          begin
            sCZH:=Qr_DhTemp.FieldbyName('CZH').AsString;
            sSqlIns:='insert into tmpcjuserlist(CZH,UserName) Values('''
                    + Qr_DhTemp.FieldbyName('CZH').AsString +''','''
                    + sUserName +''')';
            SysDM.SQL_Exec(SysDM.Qr_SysTemp,sSqlIns);
            Qr_DhTemp.Next ;
            inc(iCZHNum);
          end;
        end;
      end;
      if iCZHNum = 1 then
      begin
        sSql:='update cjUserlist set CZH='''+sCZH+''''
             +' where UserName='''+sUserName+'''';
        SysDM.SQL_Exec(SysDM.Qr_SysTemp,sSql);
      end;
      SysDM.Qr_Temp.Next ;
    end;
  end;

end;

procedure TFM_CreateCJData.FormCreate(Sender: TObject);
begin
  inherited;
  lbl_Caption.Caption :='';
  ch_AutoImport.Checked :=BAutoImport;
  if iImportFreq = 1 then
    rb_Day.Checked :=true
  else
    rb_Month.Checked :=true;
  edt_Day.Text :=IntToStr(iImportDay);
  Dt_ImportData.Time :=EnCodeTime(ImportHour,ImportMin,ImportSec,0);

  ch_AutoUpdTodayNum.Checked :=BAutoUpdateTodayNum;
  Dt_UpdTodayNum.Time :=EnCodeTime(UpdTodayNumHour,UpdTodayNumMin,UpdTodayNumSec,0);
  edt_Qfee.Text :=SysDM.ReadIni('system','MinQfee','0');
  if Lowercase(SysDM.ReadIni('system','BakCjNum','true'))='true' then
    ch_BakCJNum.Checked :=true
  else
    ch_BakCJNum.Checked :=false;

    ch_AutoImportClick(self);
    ch_AutoUpdTodayNumClick(self);
end;

procedure TFM_CreateCJData.btn_SaveCfgClick(Sender: TObject);
var
  wTemp:WORD;
begin
  inherited;
  if ch_AutoImport.Checked then
  begin
    BAutoImport:=True;
    SysDM.WriteIni('system','AutoImport','true');
  end else
  begin
    BAutoImport:=False;
    SysDM.WriteIni('system','AutoImport','false');
  end;
  if rb_Day.Checked then
  begin
    iImportFreq:=1;
    SysDM.WriteIni('system','ImportFreq','1');
  end else
  begin
    iImportFreq:=2;
    SysDM.WriteIni('system','ImportFreq','2');
  end;
  iImportDay:=StrToIntDef(trim(Edt_Day.Text),1);
  SysDM.WriteIni('system','ImportDay',IntToStr(iImportDay));
  DeCodeTime(Dt_ImportData.Time,ImportHour,ImportMin,ImportSec,wTemp);
  sImportTime:=IntToStr(ImportHour)+':'+IntToStr(ImportMin)+':'+IntToStr(ImportSec);
  SysDM.WriteIni('system','ImportTime',sImportTime);
  if ch_AutoUpdTodayNum.Checked then
  begin
    BAutoUpdateTodayNum:=true;
    SysDM.WriteIni('system','AutoUpdateTodayNum','true');
  end else
  begin
    BAutoUpdateTodayNum:=false;
    SysDM.WriteIni('system','AutoUpdateTodayNum','false');
  end;

  DeCodeTime(Dt_UpdTodayNum.Time,UpdTodayNumHour,UpdTodayNumMin,UpdTodayNumSec,wTemp);
  sUpdateTodayNumTime:=IntToStr(UpdTodayNumHour)+':'+IntToStr(UpdTodayNumMin)+':'+IntToStr(UpdTodayNumSec);
  SysDM.WriteIni('system','UpdateTodayNumTime',sUpdateTodayNumTime);

  SysDM.WriteIni('system','MinQfee',trim(edt_Qfee.Text));
  if ch_BakCJNum.Checked =true then
    SysDM.WriteIni('system','BakCjNum','true')
  else
    SysDM.WriteIni('system','BakCjNum','false');

end;

procedure TFM_CreateCJData.ch_AutoImportClick(Sender: TObject);
begin
  inherited;
  if ch_AutoImport.Checked then
  begin
    rb_Month.Enabled :=true; rb_day.Enabled :=true;
    if rb_Day.Checked then edt_Day.Enabled :=false else edt_day.Enabled :=true;
    dt_ImportData.Enabled :=true;
  end else
  begin
    rb_Month.Enabled :=false; rb_day.Enabled :=false;
    edt_Day.Enabled :=false;  dt_ImportData.Enabled :=false;
  end;
end;

procedure TFM_CreateCJData.ch_AutoUpdTodayNumClick(Sender: TObject);
begin
  inherited;
  Dt_UpdTodayNum.Enabled :=ch_AutoUpdTodayNum.Checked;
end;

procedure TFM_CreateCJData.edt_QfeeKeyPress(Sender: TObject;
  var Key: Char);
begin
  inherited;
  if not (Key in ['0'..'9',#8]) then Key:=#0;
end;

procedure TFM_CreateCJData.rb_DayClick(Sender: TObject);
begin
  inherited;
  edt_Day.Enabled :=false;
end;

procedure TFM_CreateCJData.rb_MonthClick(Sender: TObject);
begin
  inherited;
  edt_day.Enabled :=true;
end;

procedure TFM_CreateCJData.FormShow(Sender: TObject);
begin
  inherited;
  if bStartImport then btn_CreateCjData.Click ;
end;

procedure TFM_CreateCJData.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  inherited;
  FM_CreateCJData:=nil;
end;

function TFM_CreateCJData.GetTimesPerMonth(UserType: string): Integer;
begin
  with TQuery.Create(nil)do
  try
   DataBaseName := SysDM.DBMain.DatabaseName;
   Sql.Text := Format('Select TimesPerMonth from CallOutTimeTable where ID= %s',[UserType]);
   Open;
   Result := FieldByName('TimesPerMonth').AsInteger;
  finally
   Free;
  end;
end;

end.