imp.~pas

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

//没有规划好,写了大部分重码
//文本文件导入数据库
unit imp;

interface                      // Dialogs,
uses
SysUtils,Windows,DB,DBTables,Classes;

procedure Imp_sbda_psagacct(filename,tablename,split:string;Query,DelQuery:TQuery);overload;//表sbdz_psagacct共用
procedure Imp_sbda_psagacct(filename,tablename,split:string;fieldnames:widestring;Query,DelQuery:TQuery);overload;
procedure Imp_sbda_psrtacct(filename,tablename,split:string;Query,DelQuery:TQuery); overload;
procedure Imp_sbda_psrtacct(filename,tablename,split:string;fieldnames:widestring;Query,DelQuery:TQuery); overload;
procedure Imp_sbdb_cparch(filename,tablename,split:string;Query,DelQuery:TQuery);overload;
procedure Imp_sbdb_cparch(filename,tablename,split:string;fieldnames:widestring;Query,DelQuery:TQuery);overload;
procedure Imp_SBDB_PSARCH(filename,tablename,split:string;Query,DelQuery:TQuery); overload;
procedure Imp_SBDB_PSARCH(filename,tablename,split:string;fieldnames:widestring;Query,DelQuery:TQuery);overload;
procedure Imp_SBDB_RTARCH(filename,tablename,split:string;Query,DelQuery:TQuery);overload;
procedure Imp_SBDB_RTARCH(filename,tablename,split:string;fieldnames:widestring;Query,DelQuery:TQuery);overload;
procedure Imp_sbdb_rtwage(filename,tablename,split:string;Query,DelQuery:TQuery);  overload;
procedure Imp_sbdb_rtwage(filename,tablename,split:string;fieldnames:widestring;Query,DelQuery:TQuery);  overload;
//procedure Imp_sbdz_rtwage(filename,tablename,split:string;fieldnames:widestring;Query,DelQuery:TQuery); //sbdz_rtwage,sbdb_rtwage里还有一个pyitem,不能以psseno删除
procedure Imp_SBDU_PSWGCASE(filename,tablename,split:string;Query,DelQuery:TQuery); overload;
procedure Imp_SBDU_PSWGCASE(filename,tablename,split:string;fieldnames:widestring;Query,DelQuery:TQuery); overload;
procedure Imp_sbdc_pyitem(filename,tablename,split:string;Query,DelQuery:TQuery);overload;
procedure Imp_sbdc_pyitem(filename,tablename,split:string;fieldnames:widestring;Query,DelQuery:TQuery);overload;
procedure Imp_SBDS_INCONS(filename,tablename,split:string;Query,DelQuery:TQuery);
procedure Imp_SBDS_INANCONS(filename,tablename,split:string;Query,DelQuery:TQuery);
procedure Imp_SBDC_TRADE(filename,tablename,split:string;Query,DelQuery:TQuery);
procedure Imp_SBDC(filename,tablename,split:string;Query,DelQuery:TQuery); overload; //参照表 SBDC_DEPT,SBDC_CPATTR,SBDC_ECMODE,sbdc_pyitem,SBDC_SUBJ,SBDC_TRADE,SBDS_INANCONS,SBDS_INCONS,
procedure Imp_SBDC(filename,tablename,split,fieldnames:string;Query,DelQuery:TQuery); overload;
procedure Imp_sbdz_htarch(filename,tablename,split:string;Query,DelQuery:TQuery); overload;
procedure Imp_sbdz_htarch(filename,tablename,split:string;fieldnames:widestring;Query,DelQuery:TQuery); overload; //sbdz_pspause
procedure Imp_sbdz_pspause(filename,tablename,split:string;Query,DelQuery:TQuery);
procedure Imp_sbdz_pswgcase(filename,tablename,split:string;Query,DelQuery:TQuery);overload;
procedure Imp_sbdz_pswgcase(filename,tablename,split:string;fieldnames:widestring;Query,DelQuery:TQuery);overload;
procedure Imp_sbdz_rtacct(filename,tablename,split:string;Query,DelQuery:TQuery);
procedure Imp_users(filename,tablename,split:string;fieldnames:widestring;Query,DelQuery:TQuery);
procedure DeleteAll(Directory:string);
procedure Imp_Data(filename,tablename,split,fieldnames:string;Query,DelQuery:TQuery);
procedure Exp_Datafile(SQLstr:widestring;directory,filename,split:string;Query:TQuery);
implementation


procedure DeleteAll(Directory:string);
begin
  if fileexists(directory+'sbdb_cparch.txt') then
     deletefile(pansichar(directory+'sbdb_cparch.txt'));
  if fileexists(directory+'sbdb_psarch.txt') then
    deletefile(pansichar(directory+'sbdb_psarch.txt'));
  if fileexists(directory+'sbdb_rtarch.txt') then
    deletefile(pansichar(directory+'sbdb_rtarch.txt'));
  if fileexists(directory+'sbda_psagacct.txt') then
     deletefile(pansichar(directory+'sbda_psagacct.txt'));
  if fileexists(directory+'sbdu_pswgcase.txt') then
    deletefile(pansichar(directory+'sbdu_pswgcase.txt'));
  if fileexists(directory+'sbda_psrtacct.txt') then
    deletefile(pansichar(directory+'sbda_psrtacct.txt'));
  if fileexists(directory+'sbdb_rtwage.txt') then
    deletefile(pansichar(directory+'sbdb_rtwage.txt'));
  if fileexists(directory+'sbdz_pswgcase.txt') then
     deletefile(pansichar(directory+'sbdz_pswgcase.txt'));
  if fileexists(directory+'sbdz_psagacct.txt') then
    deletefile(pansichar(directory+'sbdz_psagacct.txt'));
  if fileexists(directory+'sbdz_htarch.txt') then
    deletefile(pansichar(directory+'sbdz_htarch.txt'));
  if fileexists(directory+'sbdz_pspause.txt') then
    deletefile(pansichar(directory+'sbdz_pspause.txt'));
  if fileexists(directory+'sbdz_rtacct.txt') then
    deletefile(pansichar(directory+'sbdz_rtacct.txt'));
  if fileexists(directory+'SBDC_SUBJ.txt') then
    deletefile(pansichar(directory+'SBDC_SUBJ.txt'));
  if fileexists(directory+'SBDC_ECMODE.txt') then
    deletefile(pansichar(directory+'SBDC_ECMODE.txt'));
  if fileexists(directory+'SBDC_CPATTR.txt') then
    deletefile(pansichar(directory+'SBDC_CPATTR.txt'));
  if fileexists(directory+'SBDC_DEPT.txt') then
    deletefile(pansichar(directory+'SBDC_DEPT.txt'));
  if fileexists(directory+'SBDC_TRADE.txt') then
    deletefile(pansichar(directory+'SBDC_TRADE.txt'));
  if fileexists(directory+'SBDS_INANCONS.txt') then
    deletefile(pansichar(directory+'SBDS_INANCONS.txt'));
  if fileexists(directory+'SBDS_INCONS.txt') then
    deletefile(pansichar(directory+'SBDS_INCONS.txt'));
  if fileexists(directory+'sbdc_pyitem.txt') then
    deletefile(pansichar(directory+'sbdc_pyitem.txt'));
  if fileexists(directory+'users.txt') then
    deletefile(pansichar(directory+'users.txt'));
  if fileexists(directory+'Account_Moving.txt') then
    deletefile(pansichar(directory+'Account_Moving.txt'));
  if fileexists(directory+'sbdz_InEsiis.txt') then
    deletefile(pansichar(directory+'sbdz_InEsiis.txt'));
  if fileexists(directory+'sbdz_rtwage.txt') then
    deletefile(pansichar(directory+'sbdz_rtwage.txt'));
  if fileexists(directory+'sbdz_psout.txt') then
    deletefile(pansichar(directory+'sbdz_psout.txt'));
end;

procedure Imp_sbda_psagacct(filename,tablename,split:string;Query,DelQuery:TQuery);overload;
var
  line,paramstr,valuestr:widestring;
  txt:Tstrings;
  i,j,pos1:integer;
  str:string;// prseno,pseno,yearno,
  value:array[0..47] of string;// param,
begin

  if not  FileExists(filename) then  exit;
  with delQuery do
    begin
      close;
      sql.Clear ;
      sql.Add('delete from  '+tablename+'  where psseno=:param1 and yearno=:param2');
    end;
  {for i:=0 to 47 do
    param[i]:='param'+inttostr(i); }
 value[0]:='PRSENO';     value[1]:='PSSENO';     value[2]:='YEARNO';      value[3]:='LYCPPRIN';  value[4]:='LYPSPRIN';
 value[5]:='LYCPINTS';   value[6]:='LYPSINTS';   value[7]:='CYREWG';      value[8]:='MONTHS';    value[9]:='CYMONTHS';
 value[10]:='PAYIDX' ;   value[11]:='LYMONTHS';  value[12]:='LYREMONTHS';value[13]:='CYARCPFD';value[14]:='CYRELYCPFD';
 value[15]:='CYARPSFD';  value[16]:='CYRELYPSFD';value[17]:='CYFTCPFD';  value[18]:='CYRELYCPAC';value[19]:='SPLYCPFD';
 value[20]:='CYCPACMONS';value[21]:='CYPSARAC';value[22]:='CYRELYPSARAC';value[23]:='CYRELYPSAC';value[24]:='CYPSACMONS';
 value[25]:='CYCPINTS';  value[26]:='CYPSINTS';value[27]:='ACCPINUP';value[28]:='ACPSWGINUP';value[29]:='ACCPINUPINTS';
 value[30]:='ACPSWGINUPINTS';value[31]:='WGMONUP';value[32]:='DBCPFD';value[33]:='DBPSFD';value[34]:='CYDATE';
 value[35]:='PFLAG';  value[36]:='DBFD'; value[37]:='WGNOTES'; value[38]:='FTNOTES'; value[39]:='TOTFD';
 value[40]:='TOTPSFD';value[41]:='LYCYCPINTS'; value[42]:='LYCYPSINTS'; value[43]:='NOTES';value[44]:='CYACMONS';
 value[45]:='PSRATE';value[46]:='CPRATE';value[47]:='SIINTER';
  {with Query do
    begin
      close;
      sql.Clear ;
      sql.Add('insert into '+tablename);
      sql.Add(':param1,:param2,:param3,:param4,:param5,:param6,:param7,:param8,:param9,:param10');
      sql.Add(':param11,:param12,:param13,:param14,:param15,:param16,:param17,:param18,:param19,:param20');
      sql.Add(':param21,:param22,:param23,:param24,:param25,:param26,:param27,:param28,:param29,:param30');
      sql.Add(':param31,:param32,:param33,:param34,:param35,:param36,:param37,:param38,:param39,:param40');
      sql.Add(':param41,:param42,:param43,:param44,:param45,:param46,:param47,:param48 )'); 
    end;                                                                              }
    txt:=Tstringlist.Create ;
    txt.LoadFromFile(filename); 
    for i:=0 to txt.Count-1 do
      begin
        line:=txt[i];
        pos1:=0;
        j:=pos(split,line); paramstr:='';valuestr:='';DelQuery.Close ;
        while (pos1<48) and (j>0) do
          begin
            str:='';
            if j>1 then
            str:=copy(line,1,j-1)
            else
              begin
                line:=copy(line,j+1,length(line)-j);
                j:=pos(split,line);
                inc(pos1);
                continue ;
              end;
             str:=trim(str);
            line:=copy(line,j+1,length(line)-j);

            if (pos1>0) and (pos1<3) then
              DelQuery.Params[pos1-1].AsString :=str;

            if trim(str)<>'' then
                if valuestr='' then
                       begin
                          valuestr:='('+value[pos1];
                          paramstr:='('+Quotedstr(str);
                       end
                else
                begin
                valuestr:=valuestr+','+value[pos1];
                paramstr:=paramstr+','+Quotedstr(str);
                end;
            inc(pos1);
            j:=pos(split,line);
          end;
        if  (pos1>2) then
          begin
            DelQuery.Prepare ;
            DelQuery.ExecSQL ;
          end;
        with Query do
          begin
            close;
            sql.Clear ;
            sql.Add('insert into '+tablename);
            sql.Add(valuestr+')');
            sql.Add('values');
            sql.Add(paramstr+')');
            Prepare ;
            ExecSQL ;
          end;
      end;
  DelQuery.Close ;
  Query.Close ;
  txt.Free ;
  deletefile(pchar(filename));
{
PRSENO         NUMBER(13)                     数据库记录行号::所有表的行号取至SB_PRSENO
PSSENO         NUMBER(8)                      人员内码::SB_PSSENO产生的序列号
YEARNO         NUMBER(4)                      结息年份::帐户所属年份
LYCPPRIN       NUMBER(13,2)                   上年末单位划转部分本金累计::上年末单位划转部分本金累计
LYPSPRIN       NUMBER(13,2)                   上年末个人缴纳部分本金累计::上年末个人缴纳部分本金累计
LYCPINTS       NUMBER(13,2)                   上年末单位划转部分利息累计::上年末单位划转部分利息累计
LYPSINTS       NUMBER(13,2)                   上年末个人缴纳部分利息累计::上年末个人缴纳部分利息累计
CYREWG         NUMBER(13,2)                   当年缴费工资总额::当年缴费工资总额
MONTHS         NUMBER(3)                      累计缴费月数::累计缴费月数
CYMONTHS       NUMBER(3)                      其中本年缴费月数::其中本年缴费月数
PAYIDX         NUMBER(7,3)                    累计缴费指数::暂不使用
LYMONTHS       NUMBER(3)                      其中补缴历年缴费年限缴费月数::其中补缴历年缴费月数(补缴未产生应收的缴费年限)
LYREMONTHS     NUMBER(3)                      其中补缴历年欠缴缴费月数::其中补缴历年欠缴缴费月数(补缴已产生应收的历年欠缴)       
CYARCPFD       NUMBER(13,2)                   本年单位应划入::本年单位应划入
CYRELYCPFD     NUMBER(13,2)                   本年补缴历年缴费年限单位欠划入::                                                   
CYARPSFD       NUMBER(13,2)                   本年个人应缴::本年个人应缴
CYRELYPSFD     NUMBER(13,2)                   本年补缴历年缴费年限个人欠缴::                                                     
CYFTCPFD       NUMBER(13,2)                   本年单位实划入到帐额::本年单位实划入到帐额
CYRELYCPAC     NUMBER(13,2)                   本年补缴历年缴费年限单位应划入到帐额::                                             
SPLYCPFD       NUMBER(13,2)                   本年补缴历年单位欠划入到帐额::本年补缴历年单位欠划入到帐额
CYCPACMONS     NUMBER(13,2)                   本年度单位划入部分到帐总额月积数::                                                 
CYPSARAC       NUMBER(13,2)                   本年个人应缴到帐额::本年个人应缴到帐额
CYRELYPSARAC   NUMBER(13,2)                   本年补缴历年缴费年限个人应缴到帐额::                                               
CYRELYPSAC     NUMBER(13,2)                   本年补缴历年个人欠缴到帐额::
CYPSACMONS     NUMBER(13,2)                   本年度个人缴费部分到帐总额月积数::
CYCPINTS       NUMBER(13,2)                   本年单位划入部分到帐总额产生利息::本年单位划转部分利息(包含补缴历年部分的本年利息)
CYPSINTS       NUMBER(13,2)                   本年个人缴费部分到帐总额产生利息::本年个人缴费部分利息(包含补缴历年部分的本年利息)
ACCPINUP       NUMBER(13,2)                   本年帐户单位划入部分本金调整额::由个人帐户调整档案记入
ACPSWGINUP     NUMBER(13,2)                   本年帐户个人缴费部分本金调整额::由个人帐户调整档案记入
ACCPINUPINTS   NUMBER(13,2)                   本年帐户单位划入部分本金调整额利息::
ACPSWGINUPINTS NUMBER(13,2)                   本年帐户个人缴费部分本金调整额利息::
WGMONUP        NUMBER(13,2)                   本年缴费月数调整数::由个人帐户调整档案记入
DBCPFD         NUMBER(13,2)                   累计单位欠划入总额::
DBPSFD         NUMBER(13,2)                   累计个人欠缴总额::
CYDATE         DATE          Y                结息操作时间::
PFLAG          CHAR(1)       Y                操作标志::[0正常计息 1异地转出 2终止 3退休 5年终结息 7工伤退休 9封存帐户]
DBFD           NUMBER(13,2)  Y                累计欠缴总额::
WGNOTES        VARCHAR2(100) Y                缴费基数备注::
FTNOTES        VARCHAR2(40)  Y                实际缴费备注::[★全额实到 ○全额欠缴 △部分到帐 -没有应收]
TOTFD          NUMBER(13,2)  Y                个人帐户总额::
TOTPSFD        NUMBER(13,2)  Y                其中个人部分::个人帐户总额中个人部分
LYCYCPINTS     NUMBER(13,2)  Y                上年末单位划转部分本息本年利息::
LYCYPSINTS     NUMBER(13,2)  Y                上年末个人缴纳部分本息本年利息::
NOTES          VARCHAR2(256) Y                备注
CYACMONS       NUMBER(3)     Y                当年月积数
PSRATE         NUMBER(10,4)  Y                个人划建比例
CPRATE         NUMBER(10,4)  Y                单位划建比例
SIINTER        NUMBER(10,4) }
end;

procedure Imp_sbda_psagacct(filename,tablename,split:string;fieldnames:widestring;Query,DelQuery:TQuery);overload;
var
  line,paramstr,valuestr,str1:widestring;
  txt:Tstrings;
  i,j,pos1,pos2:integer;
  str:string;// prseno,pseno,yearno,
  value:array[0..99] of string;// param,
  b :array[0..1] of boolean;
begin

  if not  FileExists(filename) then  exit;
  with delQuery do
    begin
      close;
      sql.Clear ;
      sql.Add('delete from  '+tablename+'  where psseno=:param1 and yearno=:param2');
    end;
  b[0]:=false; b[1]:=false;
  str1:=fieldnames+',';
  i:=pos(',',str1); pos2:=0;
  while (i>1) do
  begin
    value[pos2]:=trim(copy(str1,1,i-1));
    str1:=copy(str1,i+1,length(str1)-i);
    inc(pos2);
    i:=pos(',',str1);
  end;                                                           
    txt:=Tstringlist.Create ;
    txt.LoadFromFile(filename);
    for i:=0 to txt.Count-1 do
      begin
        line:=txt[i];
        pos1:=0;
        j:=pos(split,line); paramstr:='';valuestr:='';DelQuery.Close ;
        while (pos1<=pos2) and (j>0) do
          begin
            str:='';
            if j>1 then
            str:=copy(line,1,j-1)
            else
              begin
                line:=copy(line,j+1,length(line)-j);
                j:=pos(split,line);
                inc(pos1);
                continue ;
              end;
             str:=trim(str);
            line:=copy(line,j+1,length(line)-j);

            {if (pos1>0) and (pos1<3) then
              DelQuery.Params[pos1-1].AsString :=str; }

               
            if trim(str)<>'' then
                if valuestr='' then
                       begin
                          valuestr:='('+value[pos1];
                          paramstr:='('+Quotedstr(str);