checkbox.pas

企业端数据申报系统：单位管理模块 单位查询. 业务申报模块 在线数据下载 在线数据上传 在线业务申核 申报业务查询 磁盘数据导出 磁盘数据导入 在线业务模块 在线业务

unit CheckBox;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, basedialog, TFlatButtonUnit, TFlatProgressBarUnit, StdCtrls,
  jpeg, ExtCtrls, IdBaseComponent, IdComponent, IdTCPConnection,
  IdTCPClient, IdHTTP, ztvregister, ztvBase, ztvUnZip,DB,DBTables;

type
  TCheckBox_frm = class(Tbasedialog_form)
    Image2: TImage;
    Memo1: TMemo;
    Label1: TLabel;
    FlatProgressBar1: TFlatProgressBar;
    FB_cancel: TFlatButton;
    IdHTTP1: TIdHTTP;
    UnZip: TUnZip;
    IdHTTP2: TIdHTTP;
    procedure FormActivate(Sender: TObject);
    procedure IdHTTP1Work(Sender: TObject; AWorkMode: TWorkMode;
      const AWorkCount: Integer);
    procedure IdHTTP1WorkBegin(Sender: TObject; AWorkMode: TWorkMode;
      const AWorkCountMax: Integer);
    procedure IdHTTP1WorkEnd(Sender: TObject; AWorkMode: TWorkMode);
    procedure FB_cancelClick(Sender: TObject);
  private
    { Private declarations }
   procedure  UpdateTable(Query:TQuery;filename,tablename:string;updatefieldname:widestring;start,ends:integer);
  public
    { Public declarations }
  end;

var
  CheckBox_frm: TCheckBox_frm;

implementation

uses Main, datashare, imp;

{$R *.dfm}

procedure TCheckBox_frm.UpdateTable(Query:TQuery;filename,tablename:string;updatefieldname:widestring;start,ends:integer);
var
  fieldname:array[0..99] of string;
  bl:array[0..99] of boolean;
  param:array[0..99] of string;
  str,s,line ,where,value:widestring;
  i,j,k,pos1,pos2:integer;
  txt:Tstrings;
begin
  if not  FileExists(filename) then  exit;

  for i:=0 to 99 do bl[i]:=false;

  str:=updatefieldname+',';
  i:=pos(',',str); pos1:=0;
  while (i>1) do
    begin
      s:=copy(str,1,i-1);
      param[pos1]:=trim(s);
      str:=copy(str,i+1,length(str)-i);
      inc(pos1);
      i:=pos(',',str);
    end;


  txt:=Tstringlist.Create ;
  txt.LoadFromFile(filename);
  for i:=0 to txt.Count-1 do
    begin
      for pos2:=0 to 99 do
         begin
           fieldname[pos2]:=param[pos2];
           bl[pos2]:=false;
         end;
      line:=txt[i];pos2:=0;
      j:=pos('|',line);
      while (j>1) and (pos2<pos1) do
        begin
          s:=copy(line,1,j-1);
          if trim(s)<>'' then
          begin
            bl[pos2]:=true;
            Fieldname[pos2]:=Fieldname[pos2]+'='+Quotedstr(s);
          end;
          inc(pos2);
          line:=copy(line,j+1,length(line)-j);
          j:=pos('|',line);
        end;

      value:='' ;
      for j:=0 to pos2-1 do
        if bl[j] then
          begin
            if value='' then value:=fieldname[j]   else value:=value+','+fieldname[j];
          end;
      where:='';
      for j:=start to ends do
        if bl[j] then
          begin
             if where='' then where:=fieldname[j]   else where:=where+'  and   ' +fieldname[j];
          end;

       with Query do
         begin
           close;
           sql.Clear ;         //if fieldname[j]='opdate' then where:='dateformat('+fieldname[j]+','+quotestr('yyyy-mm-dd hh:mi:ss')+')';
           //----------------20040812-----------------  sysdabse的时间精确到毫秒,真是烦呀!
           (*k:=pos('opdate',where);
           line:=copy(where,1,k-1);
           where:=copy(where,k,length(where)-k);
           k:=pos(',',where);
           if k>0 then
             begin
              s:=copy(where,1,k-1);
              where:=copy(where,k,length(where)-k);
             end
           else
             begin
               s:=where;
               where:='';
             end;
            k:=pos('=',s);
            if k>0 then
              begin
                str:='opdate-date('+copy(s,k+1,length(s)-k-1)+''''+')=0';
                where:=line+str+where;
              end;    *)
            k:=pos('opdate',where);
           where:= StringReplace(where,'opdate','dateformat(opdate,'+quotedstr('yyyy-mm-dd hh:mm:ss')+')',[rfReplaceAll, rfIgnoreCase]);
           //----------------20040812-----------------
           sql.Add('update '+tablename +'  set '+value+' where ' +where);
           prepare;
           execsql;
         end;
    end;
  txt.Free ;
  deletefile(filename);
end;
procedure TCheckBox_frm.FormActivate(Sender: TObject);
var
  sbdz_pswgcase,sbdz_htarch,sbdz_pspause,sbdz_rtacct,sbdz_psagacct,fieldname,sbdz_rtwage,sbdz_inesiis,account_moving,line:widestring;
  i:integer;
  DownLoadFile:TFileStream;
  str,d:string;
begin
    screen.cursor:=crhourglass;
    delay(500);//界面模糊
    with DataShare_frm.Query1 do
    begin
      close;
      sql.Clear ;
      sql.Add('select psseno,yearno,opdate from sbdz_pswgcase where procflg=2') ;
      prepare;
      open;
      sbdz_pswgcase:=''; line:='' ;
      while not eof do
        begin
          for i:=0 to FieldCount-1 do
            if line='' then line:=Fields[i].AsString else line:=line+'|'+Fields[i].AsString ;
         sbdz_pswgcase:=sbdz_pswgcase+line+'♂' ;
         line:='';
         next;
        end;

     close;
     sql.Clear ;
     sql.Add('select psseno,opdate from sbdz_htarch where procflg=2');
     prepare;
     open;
     sbdz_htarch:='';line:='';
     while not eof do
        begin
          for i:=0 to FieldCount-1 do
            if line='' then line:=Fields[i].AsString else line:=line+'|'+Fields[i].AsString ;
         sbdz_htarch:=sbdz_htarch+line+'♂';
         line:='';
         next;
        end;

     close;
     sql.Clear ;
     sql.Add('select psseno,opdate from sbdz_pspause where procflg=2');
     prepare;
     open;
     sbdz_pspause:='';line:='' ;
     while not eof do
        begin
          for i:=0 to FieldCount-1 do
            if line='' then line:=Fields[i].AsString else line:=line+'|'+Fields[i].AsString ;
         sbdz_pspause:=sbdz_pspause+line+'♂' ;
         line:='';
         next;
        end;

     close;
     sql.Clear ;
     sql.Add('select psseno,yearno,opdate from sbdz_rtacct where procflg=2');
     prepare;
     open;
     sbdz_rtacct:=''; line:='';
     while not eof do
        begin
          for i:=0 to FieldCount-1 do
            if line='' then line:=Fields[i].AsString else line:=line+'|'+Fields[i].AsString ;
         sbdz_rtacct:=sbdz_rtacct+line+'♂';
         line:='';
         next;
        end;

     close;
     sql.Clear ;
     sql.Add('select psseno,yearno,opdate from sbdz_psagacct where procflg=2');
     prepare;
     open;
     sbdz_psagacct:=''; line:='';
     while not eof do
        begin
          for i:=0 to FieldCount-1 do
            if line='' then line:=Fields[i].AsString else line:=line+'|'+Fields[i].AsString ;
         sbdz_psagacct:=sbdz_psagacct+line+'♂';
         line:='';
         next;
        end;

     close;
     sql.Clear ;
     sql.Add('select psseno,opdate from sbdz_rtwage where procflg=2');
     prepare;
     open;
     sbdz_rtwage:=''; line:='';
     while not eof do
       begin
         for i:=0 to Fieldcount-1 do
           if line='' then line:=Fields[i].AsString  else line:=line+'|'+Fields[i].AsString ;
         sbdz_rtwage:=sbdz_rtwage+line+'♂';
         line:='';
         next;
       end;

     close;
     sql.Clear ;
     sql.Add('select psseno,opdate from sbdz_inesiis where procflg=2');
     prepare;
     open;
     sbdz_inesiis:='';  line:='';
     while not eof do
       begin
         for i:=0 to Fieldcount-1 do
           if line='' then line:=Fields[i].AsString else line:=line+'|'+Fields[i].AsString ;
         sbdz_inesiis:=sbdz_inesiis+line+'♂';
         line:='';
         next;
       end;

     close;
     sql.Clear ;
     sql.Add('select psseno,opdate from account_moving where procflg=2');
     prepare;
     open;
     account_moving:='';line:='';
     while not eof do
       begin
         for i:=0 to Fieldcount-1 do
           if line='' then line:=Fields[i].AsString else line:=line+'|'+Fields[i].AsString ;
         account_moving:=account_moving+line+'♂';
         line:='';
         next;
       end;
     if  (sbdz_htarch='') and (sbdz_psagacct='') and (sbdz_pspause='') and (sbdz_pswgcase='') and  (sbdz_rtacct='')
     and (sbdz_rtwage='') and (sbdz_inesiis='') and (account_moving='') then
     begin
       screen.cursor:=crdefault;
       application.MessageBox('最近没有更新汇总业务','恩普软件',mb_iconinformation+mb_ok);
       exit;
     end;
     FB_cancel.Enabled :=false;
     msg:=fecho.getCKReturnDataFileName(username,sbdz_htarch,sbdz_psagacct,sbdz_pspause,sbdz_pswgcase,sbdz_rtacct,sbdz_inesiis,sbdz_rtwage,account_moving);
     if msg.msgCode<>1 then
     begin
       FB_cancel.Enabled :=true;
       FlatProgressBar1.Visible :=false;
       cursor:=crdefault;
       application.MessageBox(pchar(string(msg.msgText)),'恩普软件',mb_iconerror+mb_ok);
       exit;
     end ;
    FlatProgressBar1.Visible :=true;
    memo1.Lines.Clear ;
    memo1.Lines.Add('正在检索数据...');
    memo1.Lines.Add('根据单位大小,职工多少,所需的时间长短不一!');

    if directoryexists(extractfilepath(application.ExeName)+'importfile') then
       createdir(extractfilepath(application.ExeName)+'importfile');

    if fileexists(extractfilepath(application.ExeName)+'importfile\bb.zip') then
      deletefile(extractfilepath(application.ExeName)+'importfile\bb.zip');
    DownLoadFile:=TFileStream.Create(extractfilepath(application.ExeName)+'importfile\bb.zip',fmCreate);
    FlatProgressBar1.Visible:=true;
    str:=download+msg.msgtext;
    memo1.Lines.Add('正在下载数据,下载进度如进度条所示!');
    IdHTTP1.Get(download+msg.msgText,DownLoadFile);
    DownLoadFile.Free;
    Fecho.deleteDataFile(msg.msgText); 

    delay(500);
    d:=extractfilepath(application.ExeName);
    DeleteAll(extractfilepath(application.ExeName)+'importfile\');
    unzip.ArchiveFile :=extractfilepath(application.ExeName)+'importfile\bb.zip';
    unzip.ExtractDir :=d+'importfile\';
    unzip.FileSpec.Add('*.*');
    unzip.Extract ;
    deletefile(extractfilepath(application.ExeName)+'importfile\bb.zip');
    d:=extractfilepath(application.ExeName)+'importfile\';
    try
    memo1.Lines.Add('正在更新合同制职工更新汇总表...!');
    DataShare_frm.Dbs.StartTransaction ;
    fieldname:='opseno,psseno,opdate,procflg,ckcode,ckdate,notpassnotes';
    updatetable(DataShare_frm.Query1,d+'sbdz_htarch.txt','sbdz_htarch',fieldname,1,2);

    memo1.Lines.Add('正在更新个人帐户更新汇总表...!');
    fieldname:='opseno,psseno,yearno,opdate,procflg,ckcode,ckdate,notpassnotes';
    updatetable(DataShare_frm.Query1,d+'sbdz_psagacct.txt','sbdz_psagacct',fieldname,1,3);

    memo1.Lines.Add('正在更新中断人员更新汇总表...！');
    fieldname:='opseno,psseno,opdate,procflg,ckcode,ckdate,notpassnotes';
    updatetable(DataShare_frm.Query1,d+'sbdz_pspause.txt','sbdz_pspause',fieldname,1,2);

    memo1.Lines.Add('正在更新缴费工资更新汇总表...！');
    fieldname:='opseno,psseno,yearno,opdate,procflg,ckcode,ckdate,notpassnotes,stwg';
    updatetable(DataShare_frm.Query1,d+'sbdz_pswgcase.txt','sbdz_pswgcase',fieldname,1,3);

    memo1.Lines.Add('正在更新退休人员帐户更新汇总表...！');
    fieldname:='opseno,psseno,yearno,opdate,procflg,ckcode,ckdate,notpassnotes';
    updatetable(DataShare_frm.Query1,d+'sbdz_rtacct.txt','sbdz_rtacct',fieldname,1,3);

    memo1.Lines.Add('正在更新异地转入人员更新汇总表...！');
    fieldname:='opseno,psseno,opdate,procflg,ckcode,ckdate,notpassnotes';
    updatetable(DataShare_frm.Query1,d+'sbdz_InEsiis.txt','sbdz_InEsiis',fieldname,1,2);

    memo1.Lines.Add('正在更新并规计发养老金更新汇总表...');
    fieldname:='opseno,psseno,opdate,procflg,ckcode,ckdate,notpassnotes';
    updatetable(DataShare_frm.Query1,d+'sbdz_rtwage.txt','sbdz_rtwage',fieldname,1,2);

    memo1.Lines.Add('正在更退休人员退休金更新汇总表...');
    fieldname:='opseno,psseno,opdate,procflg,ckcode,ckdate,notpassnotes';
    updatetable(DataShare_frm.Query1,d+'sbdz_psout.txt','Account_Moving',fieldname,1,2);

    memo1.Lines.Add('更新完成！');
    DeleteAll(d);
    DataShare_frm.Dbs.Commit ;
    screen.cursor:=crdefault;
    application.MessageBox('更新完成！','恩普软件',mb_iconinformation+mb_ok);
    except
      DeleteAll(d);
      DataShare_frm.Dbs.Rollback ;
      screen.cursor:=crdefault;
      application.MessageBox('更新异常！','恩普软件',mb_iconinformation+mb_ok);
      FB_cancel.Enabled :=true;
    end;
    end;
    FB_cancel.Enabled :=true;
end;

procedure TCheckBox_frm.IdHTTP1Work(Sender: TObject; AWorkMode: TWorkMode;
  const AWorkCount: Integer);
begin
  inherited;
   FlatProgressBar1.Position :=AWorkCount;
end;

procedure TCheckBox_frm.IdHTTP1WorkBegin(Sender: TObject;
  AWorkMode: TWorkMode; const AWorkCountMax: Integer);
begin
  inherited;
   FlatProgressBar1.Min :=0;
  FlatProgressBar1.Max :=AWorkCountMax;
end;

procedure TCheckBox_frm.IdHTTP1WorkEnd(Sender: TObject;
  AWorkMode: TWorkMode);
begin
  inherited;
   FlatProgressBar1.visible:=false;
end;

procedure TCheckBox_frm.FB_cancelClick(Sender: TObject);
begin
  inherited;
  close;
end;

end.