增加用户版本
diff --git a/.idea/misc.xml b/.idea/misc.xml
index 7038ae8..867022d 100644
--- a/.idea/misc.xml
+++ b/.idea/misc.xml
@@ -1,5 +1,5 @@
<?xml version="1.0" encoding="UTF-8"?>
<project version="4">
- <component name="ProjectRootManager" version="2" project-jdk-name="Python 2.7.3 virtualenv at ~/Dev/py27-dev64" project-jdk-type="Python SDK" />
+ <component name="ProjectRootManager" version="2" project-jdk-name="Python 2.7.3 virtualenv at ~/.venv/py27 (1)" project-jdk-type="Python SDK" />
</project>
diff --git a/.idea/v2convert.iml b/.idea/v2convert.iml
index 1ba372b..96c0d23 100644
--- a/.idea/v2convert.iml
+++ b/.idea/v2convert.iml
@@ -2,7 +2,7 @@
<module type="PYTHON_MODULE" version="4">
<component name="NewModuleRootManager">
<content url="file://$MODULE_DIR$" />
- <orderEntry type="jdk" jdkName="Python 2.7.3 virtualenv at ~/Dev/py27-dev64" jdkType="Python SDK" />
+ <orderEntry type="jdk" jdkName="Python 2.7.3 virtualenv at ~/.venv/py27 (1)" jdkType="Python SDK" />
<orderEntry type="sourceFolder" forTests="false" />
</component>
</module>
diff --git a/convert-example.json b/convert-example.json
index def9d61..6c313d0 100755
--- a/convert-example.json
+++ b/convert-example.json
@@ -5,6 +5,7 @@
// desc: 转换规则的描述
// pre_exec: 执行转换前需要执行一次的 sql 语句，格式如 [ "insert into ....", "delete from ...."]，
// 可以有多条 sql 语句, 这个操作是在原始数据库执行
+ // pre_dst_exec: 执行转换前执行一次的 sql, 这个操作在目标库执行
// post_exec: 转换完成后需要执行一次的 sql 语句， 格式同 pre_exec， 这个操作是在目标数据库执行
// src_sql: 转换原始表查询 sql
// dest_column: 转换目标表 column , src_sql 查询出来的字段与 dest_column 的字段
diff --git a/convert-shisu-test.json b/convert-shisu-test.json
new file mode 100644
index 0000000..b02b7d1
--- /dev/null
+++ b/convert-shisu-test.json
@@ -0,0 +1,228 @@
+[
+ {
+ "desc": "账户信息表",
+ "name": "T_ACCOUNT",
+ "action": "truncate",
+ "src_sql": "select (case when a.account_id is null then ''
+ else '1'||substr(a.account_id,8) end) accno,
+ a.cut_name,'2001',a.password,a.customer_id,a.card_id,a.purse_id,
+ '' singlemaxamt,'' daycostmaxamt,'' daycostamt,'' cardmaxbal,
+ a.current_state,(case when a.current_state=1 then c.deposit_fee
+ else 0 end) foregift,
+ 0 ydaybal,a.last_bala,a.last_freebala,a.last_frozebala,0 subsidybal,
+ 0 subsidyno,a.card_balance,a.consume_count,0 dpscnt,a.consume_count paycnt,
+ 0 termid,'' transtime,0 lastsubsidyamt,0 lastsubsidyno,0 lastbala,
+ 0 lastcardcnt,0 lastdpscnt,0 lastpaycnt,'' lasttransdate,'' lastaccdate,
+ a.open_date,a.close_date from ykt_cur.t_aif_account a left join
+ ykt_cur.t_pif_card c on a.card_id=c.card_id where a.act_type=1",
+ "dest_column": "ACCNO, ACCNAME, SUBJNO, ACCPWD, CUSTID, CARDNO, PURSENO, SINGLEMAXAMT,
+ DAYCOSTMAXAMT,DAYCOSTAMT, CARDMAXBAL, STATUS, FOREGIFT, YDAYBAL,
+ BALANCE, AVAILBAL, FROZEBAL, SUBSIDYBAL, SUBSIDYNO, CARDBAL, CARDCNT,
+ DPSCNT, PAYCNT, TERMID, TRANSTIME, LASTSUBSIDYAMT, LASTSUBSIDYNO,
+ LASTCARDBAL, LASTCARDCNT, LASTDPSCNT, LASTPAYCNT, LASTTRANSDATE,
+ LASTACCDATE, OPENDATE, CLOSEDATE",
+ "post_exec":["update t_syskey set keyval=(select max(to_number(substr(accno,2)))
+ from t_account) where keycode='CARDACCNO'",
+ "update t_account set balance=balance+60, availbal=AVAILBAL+60, foregift=120
+ where accno='1' || substr('1000000000192778', 8) "]
+ },
+ {
+ "name": "T_INNERACC",
+ "action": "truncate",
+ "desc": "内部账户表",
+ "src_sql": "select (case when a.account_id='1011' then '1001'
+ when a.account_id='1021' then '1002'
+ when a.account_id='1031' then '112101'
+ when a.account_id='1041' then '112102'
+ when a.account_id='1051' then '112103'
+ when a.account_id='2051' then '2002'
+ when a.account_id='2061' then '220299'
+ when a.account_id='2071' then '2203'
+ when a.account_id='5011' then '6002'
+ when a.account_id='5021' then '6005'
+ when a.account_id='5051' then '6003'
+ when a.account_id='5041' then '6006'
+ when a.account_id='5071' then '6001'
+ else a.account_id end) account_id,
+ a.cut_name, (case when a.account_id='1011' then '1001'
+ when a.account_id='1021' then '1002'
+ when a.account_id='1031' then '112101'
+ when a.account_id='1041' then '112102'
+ when a.account_id='1051' then '112103'
+ when a.account_id='2051' then '2002'
+ when a.account_id='2061' then '220299'
+ when a.account_id='2071' then '2203'
+ when a.account_id='5011' then '6002'
+ when a.account_id='5021' then '6005'
+ when a.account_id='5051' then '6003'
+ when a.account_id='5041' then '6006'
+ when a.account_id='5071' then '6001'
+ else a.account_id end) subno,
+ 0 ,last_BALA,(CASE WHEN SUBSTR(A.ACCOUNT_ID,1,1)='1' THEN 1
+ ELSE 2 END) BALFLAG,
+ '','' from (select rtrim(a.account_id) account_id,a.last_bala,
+ a.cut_name from ykt_cur.t_aif_account a where a.act_type=7 and
+ a.account_id not in ('1122','2031','2041','1071')) a",
+ "dest_column": "ACCNO, ACCNAME, SUBJNO, YDAYBAL, BALANCE, BALFLAG, LASTTRANSDATE,
+ LASTACCDATE",
+ "remark": "导入内部账户，并更新错误的借贷标志",
+ "post_exec": ["update t_inneracc set balance=abs(balance),ydaybal=abs(ydaybal),
+ balflag=1 where accno in ('6201','6202')",
+ "update t_syspara set paraval=:bala_date where paraid=2",
+ "insert into T_INNERACC (ACCNO, ACCNAME, SUBJNO, YDAYBAL, BALANCE,
+ BALFLAG, LASTTRANSDATE, LASTACCDATE)
+ values ('112201', '易支付充值款', '112201', 0, 0, 1, null, null)",
+ "insert into T_INNERACC (ACCNO, ACCNAME, SUBJNO, YDAYBAL, BALANCE,
+ BALFLAG, LASTTRANSDATE, LASTACCDATE)
+ values ('112299', '其他应收款', '112299', 0, 0, 1, null, null)",
+ "insert into T_INNERACC (ACCNO, ACCNAME, SUBJNO, YDAYBAL, BALANCE,
+ BALFLAG, LASTTRANSDATE, LASTACCDATE)
+ values ('6004', '搭伙费收入', '6004', 0, 0, 2, null, null)",
+ "insert into T_INNERACC (ACCNO, ACCNAME, SUBJNO, YDAYBAL, BALANCE,
+ BALFLAG, LASTTRANSDATE, LASTACCDATE)
+ values ('2006', '水控存款', '2006', 0, 0, 2, null, null)",
+ "insert into T_INNERACC (ACCNO, ACCNAME, SUBJNO, YDAYBAL, BALANCE,
+ BALFLAG, LASTTRANSDATE, LASTACCDATE)
+ values ('220201', '批量销户应付款', '220201', 0, 0, 2, null, null)",
+ "insert into T_INNERACC (ACCNO, ACCNAME, SUBJNO, YDAYBAL, BALANCE,
+ BALFLAG, LASTTRANSDATE, LASTACCDATE)
+ values ('6201', '营业外支出', '6201', 0, 0, 1, null, null)",
+ "insert into T_INNERACC (ACCNO, ACCNAME, SUBJNO, YDAYBAL, BALANCE,
+ BALFLAG, LASTTRANSDATE, LASTACCDATE)
+ values ('6202', '商户提成支出', '6202', 0, 0, 1, null, null)"
+ ]
+ },
+ {
+ "name": "T_SHOPACC",
+ "action": "truncate",
+ "desc": "商户账户表",
+ "src_sql": "select (case when a.account_id is null then ''
+ else '2'||substr(a.account_id,8) end) accno,
+ s.shop_id shopid,a.cut_name,'2004',a.password,a.current_state,
+ 0 ydaybal,a.last_bala,a.last_freebala,a.last_frozebala,'' lasttransdate,
+ '' lastaccdate, a.open_date,a.close_date from
+ ykt_cur.t_aif_account a left join ykt_cur.t_cif_shop s on
+ a.customer_id=s.cut_id where a.act_type=2 and s.shop_id is not null
+ order by s.shop_id",
+ "dest_column": "ACCNO, SHOPID, ACCNAME, SUBJNO, ACCPWD, STATUS, YDAYBAL, BALANCE,
+ AVAILBAL, FROZEBAL, LASTTRANSDATE, LASTACCDATE, OPENDATE, CLOSEDATE",
+ "post_exec": ["update t_syskey set keyval=(select max(to_number(substr(accno,2)))
+ from t_shopacc) where keycode='SHOPACCNO'"]
+ },
+ {
+ "name": "T_RPTACCBAL",
+ "action": "truncate",
+ "desc": "账户结算表",
+ "src_sql": "select :tx_date,'2001',(case when a.account_id is null then ''
+ else '1'||substr(a.account_id,8) end) accno,
+ 2 ,last_bala from ykt_cur.t_aif_account a where act_type=1
+ union all
+ select :tx_date,'2004',(case when a.account_id is null then ''
+ else '2'||substr(a.account_id,8) end) accno,
+ 2,last_bala from ykt_cur.t_aif_account a where act_type=2
+ union all
+ select :tx_date, (case when a.account_id='1011' then '1001'
+ when a.account_id='1021' then '1002'
+ when a.account_id='1031' then '112101'
+ when a.account_id='1041' then '112102'
+ when a.account_id='1051' then '112103'
+ when a.account_id='2051' then '2002'
+ when a.account_id='2061' then '220299'
+ when a.account_id='2071' then '2203'
+ when a.account_id='5011' then '6002'
+ when a.account_id='5021' then '6005'
+ when a.account_id='5051' then '6003'
+ when a.account_id='5041' then '6006'
+ when a.account_id='5071' then '6001'
+ else rtrim(a.account_id) end) subno,
+ (case when a.account_id='1011' then '1001'
+ when a.account_id='1021' then '1002'
+ when a.account_id='1031' then '112101'
+ when a.account_id='1041' then '112102'
+ when a.account_id='1051' then '112103'
+ when a.account_id='2051' then '2002'
+ when a.account_id='2061' then '220299'
+ when a.account_id='2071' then '2203'
+ when a.account_id='5011' then '6002'
+ when a.account_id='5021' then '6005'
+ when a.account_id='5051' then '6003'
+ when a.account_id='5041' then '6006'
+ when a.account_id='5071' then '6001'
+ else rtrim(a.account_id) end) account_id,
+ (case when substr(a.account_id,1,1)='1' then 1 else 2 end) flag,
+ last_bala from ykt_cur.t_aif_account a where act_type=7 and
+ a.account_id not in ('1122','2031','2041','1071')",
+ "dest_column": "ACCDATE, SUBJNO, ACCNO, BALFLAG, BALANCE",
+ "remark": "导入内部账户报表，并更新错误的借贷标志",
+ "post_exec": ["update t_rptaccbal set balance=abs(balance),balflag=1
+ where accno in ('6201','6202')",
+ // "insert into t_rptaccbal(accdate,subjno,accno,balflag,balance)
+ // select :tx_date,s.subjno,s.subjno,
+ // (case when substr(s.subjno,1,1)='1' then 1
+ // else 2 end) flag1,0
+ // from t_subject s left join t_rptaccbal b on
+ // (s.subjno=b.subjno and b.accdate=:tx_date)
+ // where b.accdate is null",
+ "insert into t_rptaccbal(accdate,subjno,accno,balflag,balance)
+ select :tx_date,s.subjno,s.subjno,2,0 from t_rptsubjbal s
+ where s.subjno in (2001,2004,2202,2006,220201,6004)
+ and s.accdate=:tx_date",
+ "insert into t_rptaccbal(accdate,subjno,accno,balflag,balance)
+ select :tx_date,s.subjno,s.subjno,1,0 from t_rptsubjbal s
+ where s.subjno in (6201,6202)
+ and s.accdate=:tx_date",
+ "update t_rptaccbal set balance=balance+60 where
+ accno='1' || substr('1000000000192778', 8) and accdate=:tx_date"]
+ },
+ {
+ "name": "T_RPTSUBJBAL",
+ "action": "truncate",
+ "desc": "科目结算表",
+ "src_sql": "select :tx_date,'2001',2 ,sum(last_bala),0,0,2,sum(last_bala)
+ from ykt_cur.t_aif_account a where act_type=1
+ union all
+ select :tx_date,'2004',2,sum(last_bala),0,0,2,sum(last_bala)
+ from ykt_cur.t_aif_account a where act_type=2
+ union all select :tx_date,
+ (case when a.account_id='1011' then '1001'
+ when a.account_id='1021' then '1002'
+ when a.account_id='1031' then '112101'
+ when a.account_id='1041' then '112102'
+ when a.account_id='1051' then '112103'
+ when a.account_id='2051' then '2002'
+ when a.account_id='2061' then '220299'
+ when a.account_id='2071' then '2203'
+ when a.account_id='5011' then '6002'
+ when a.account_id='5021' then '6005'
+ when a.account_id='5051' then '6003'
+ when a.account_id='5041' then '6006'
+ when a.account_id='5071' then '6001'
+ when a.account_id='2081' then '2202'
+ else rtrim(a.account_id) end) subno,
+ (case when substr(a.account_id,1,1)='1' then 1
+ else 2 end) flag1,
+ sum(last_bala),0,0,
+ (case when substr(a.account_id,1,1)='1' then 1
+ else 2 end) flag2,sum(last_bala) from
+ ykt_cur.t_aif_account a where act_type=7 and
+ a.account_id not in ('1122','2031','2041','1071')
+ group by a.account_id",
+ "dest_column": "ACCDATE, SUBJNO, BEGINBALFLAG, BEGINBAL, DRAMT, CRAMT,
+ ENDBALFLAG, ENDBAL",
+ "post_exec" : ["insert into t_rptsubjbal(ACCDATE, SUBJNO, BEGINBALFLAG, BEGINBAL,
+ DRAMT, CRAMT, ENDBALFLAG, ENDBAL)
+ select :tx_date, subjno, 1,
+ (select sum(beginbal) from t_rptsubjbal t where accdate=:tx_date
+ and t.subjno like s.subjno || '__'),
+ 0,0,1,
+ (select sum(endbal) from t_rptsubjbal t where accdate=:tx_date
+ and t.subjno like s.subjno || '__') from t_subject s
+ where subjno in ('1121','2202')",
+ "update t_rptsubjbal set beginbalflag=1,endbalflag=1 where
+ subjno in ('6201','6202')",
+ "update t_rptsubjbal set beginbal=beginbal+60, endbal=endbal+60
+ where accdate=:tx_date and subjno='2001'",
+ "delete from t_servicelog"],
+ "remark": "生成结算科目数据"
+ }
+]
diff --git a/convert-shisu.json b/convert-shisu.json
new file mode 100755
index 0000000..2c119e6
--- /dev/null
+++ b/convert-shisu.json
@@ -0,0 +1,40 @@
+/*
+ // 配置格式说明：
+ // name: 转换目标表名
+ // action: 装换前的操作，truncate 表示先执行truncate 操作，none 表示无任何动作
+ // desc: 转换规则的描述
+ // pre_exec: 执行转换前需要执行一次的 sql 语句，格式如 [ "insert into ....", "delete from ...."]，
+ // 可以有多条 sql 语句, 这个操作是在原始数据库执行
+ // post_exec: 转换完成后需要执行一次的 sql 语句， 格式同 pre_exec， 这个操作是在目标数据库执行
+ // src_sql: 转换原始表查询 sql
+ // dest_column: 转换目标表 column , src_sql 查询出来的字段与 dest_column 的字段
+*/
+[
+ {
+ "name": "T_DEVICE_TEMP",
+ "action": "truncate",
+ "desc": "设备注册表",
+ "src_sql": "SELECT T1.DEVICE_ID,T1.DEVICE_NAME,T1.DEVPHY999_ID,T1.DEV999_NO,
+ T1.PHYTYPE DEVPHYTYPE,T1.DEVTYPE,'' DEVVERNO,T1.CARDSTR,T2.DEVICE_ID,
+ 1 RUNSTATUS,(case when T1.STATE_ID between 1 and 4 then 1
+ else 2 end) STATUS,
+ T1.PORTCOUNT,T1.PORTNO,T1.JOINMODE,T1.COMADD,nvl(trim(T1.PORT),0),T1.BAUDRATE,
+ '' CARDSET,T1.BSHEETVER,T1.LASTTRASERNO,T1.OPERATE_TIME LASTTRANSTIME,
+ trim(T1.PUSERNO),0 MAXAMT,T1.SUBSYSTEM_ID,T1.AREA,'' DOORLSTVER,T1.DEV_USAGE,
+ '' OPENDATE,'' CLOSEDATE,'' BATCHNO,'' LASTSAVED,'' REMARK FROM
+ YKT_CUR.T_PIF_DEVICE T1 LEFT JOIN YKT_CUR.T_PIF_DEVICE T2 ON
+ T1.FDEV_ID=T2.DEVICE_ID AND T1.DEVPHY999_ID NOT IN ('FFFFFFFF','00000000')
+ AND T2.STATE_ID<5 ORDER BY T1.DEVICE_ID",
+ "dest_column": "DEVICEID, DEVICENAME, DEVPHYID,DEVICENO, DEVPHYTYPE, DEVTYPECODE,
+ DEVVERNO, CARDPHYTYPE, FDEVICEID,RUNSTATUS, STATUS,SVRPORTCNT,
+ SVRPORTNO,COMMTYPE, IP,PORTNO, BAUDRATE, CARDSET,CARDVERNO,
+ LASTSEQNO,LASTTRANSTIME,PURSENO, MAXAMT, SYSID,AREACODE,
+ DOORLSTVER, DEVUSAGE,OPENDATE, CLOSEDATE, BATCHNO,
+ LASTSAVED, REMARK",
+ "post_exec": ["update t_device_temp x set devphyid=substr(devphyid,1,4)
+ || lpad(to_number(substr(devphyid,5,1), 'XX'),2,'0')
+ || lpad(to_number(substr(devphyid,6,1), 'XX'),2,'0')
+ || substr(devphyid,7,2)
+ where x.devphytype=1001 and length(x.devphyid) = 8"]
+ }
+]
diff --git a/convert-shu-backup.json b/convert-shu-backup.json
new file mode 100644
index 0000000..f6ca3d9
--- /dev/null
+++ b/convert-shu-backup.json
@@ -0,0 +1,106 @@
+
+ {
+ "name": "T_AREA",
+ "action": "truncate",
+ "desc": "区域信息表",
+ "src_sql": "select area_no,area_name,father_area_no,'' oldareacode,
+ arealevel,address,comments from ykt_cur.t_pif_area",
+ "dest_column": "AREACODE, AREANAME, FAREACODE, OLDAREACODE, AREALEVEL, ADDR, REMARK"
+ },
+ {
+ "name": "T_CUSTTYPE",
+ "action": "truncate",
+ "desc": "客户类型表",
+ "src_sql": "SELECT CUT_TYPE,TYPE_NAME,CUT_TYPE,0 AREACODE,0 OLDCUSTTYPE,0 USEFLAG
+ FROM YKT_CUR.T_CIF_CUTTYPEFEE",
+ "dest_column": "CUSTTYPE, CUSTTYPENAME, FEETYPE,AREACODE, OLDCUSTTYPE, USEFLAG"
+ },
+ {
+ "name": "T_DEPT",
+ "action": "truncate",
+ "desc": "部门表",
+ "src_sql": "SELECT DEPT_CODE,0 AREACODE,DEPT_NAME,E_NAME,DEPT_NAME,PARENTDEPT_CODE,
+ DEPT_LEVEL,IS_ACADEMY,DEPT_CODE,0 USEFLAG,'' LASTSAVED
+ FROM YKT_CUR.T_CIF_DEPT",
+ "dest_column": "DEPTCODE, AREACODE, DEPTNAME, DEPTENAME, DEPTFULLNAME, FDEPTCODE,
+ DEPTLEVEL, DEPTTYPE, OLDDEPTCODE, USEFLAG, LASTSAVED"
+ },
+
+ {
+ "name": "T_FEETYPE",
+ "action": "truncate",
+ "desc": "收费类别表",
+ "src_sql": "SELECT FEE_TYPE,SUBSTR(FEE_NAME,1,60),IS_OUTFEE,0,0
+ FROM YKT_CUR.T_PIF_FEETYPE",
+ "dest_column": "FEETYPE, FEENAME, BOARDFLAG,BOARDRATE, DPSRATE"
+ },
+ {
+ "name": "T_SUBSYSTEM",
+ "action": "truncate",
+ "desc": "子系统表",
+ "src_sql": "SELECT SYSTEM_ID,SYSTEM_NAME,SYSTEM_TYPE,AREA,SERVER_ID,MAC_ADDR,IP,
+ PORT, '' CARDVERNO,INIT_KEY,DYNA_KEY,SYNCH_TIME,
+ (case when STATE=1 then 1
+ when state=2 then 1
+ when state=3 then 2
+ when state=4 then 3
+ else 4 end) runstatus ,
+ (case when STATE=6 then 2 else 1 end) status ,
+ '' OPENDATE,'' CLOSEDATE,'' REMARK FROM
+ YKT_CUR.T_PIF_SUBSYSTEM",
+ "dest_column": "SYSID, SYSNAME, SYSTYPE, AREACODE, SERVERID, MACADDR, IP, PORT,
+ CARDVERNO, INITKEY, DYNAKEY, SYNCTIME, RUNSTATUS, STATUS,
+ OPENDATE, CLOSEDATE, REMARK"
+ },
+ {
+ "name": "T_SPECIALTY",
+ "action": "truncate",
+ "desc": "专业信息表",
+ "src_sql": "SELECT S_CODE,S_NAME,1 USEFLAG,'' LASTSAVED FROM
+ YKT_CUR.T_CIF_SPECIALITY WHERE S_CODE <>'' and s_code<> '0'",
+ "dest_column": "SPECIALTYCODE, SPECIALTYNAME, USEFLAG, LASTSAVED"
+ },
+ {
+ "name": "T_OPERATOR",
+ "action": "truncate",
+ "desc": "操作员表",
+ "src_sql": "select rownum,oper_code,2,100,oper_name, phone, mobile,
+ email, addr, pwd, 1 from ykt_cur.t_pif_operator",
+ "dest_column": "OPERID, OPERCODE, OPERTYPE, BRANCHNO, OPERNAME, TEL, MOBILE,
+ EMAIL, ADDR, OPERPWD, STATUS",
+ "post_exec": ["update t_syskey set keyval=(select max(operid) from t_operator)
+ where keycode='OPERID'",
+ "delete from t_operrole",
+ "insert into t_operrole(operroleid, operid, roleid) values(1, 26, 1)",
+ "update t_operator set branchno=101 where opername like '%宝山%'",
+ "update t_operator set branchno=102 where opername like '%延长%'",
+ "update t_operator set branchno=103 where opername like '%嘉定%'"
+ ]
+ },
+ ,
+ {
+ "name": "T_OPERATOR",
+ "action": "truncate",
+ "desc": "操作员表",
+ "src_sql": "select rownum,oper_code,2,
+ case when g.group_id = 1000 then 101
+ when g.group_id = 1001 then 102
+ when g.group_id = 1002 then 103
+ when g.group_id = 1003 then 105
+ when g.group_id = 1004 then 100
+ when g.group_id = 1005 then 106
+ when g.group_id = 1006 then 104
+ else 100 end,
+ oper_name, phone, mobile,
+ email, addr, pwd, 1 from ykt_cur.t_pif_operator t left join
+ ykt_cur.t_oper_group g on t.oper_code=g.oper_id order by t.oper_code",
+ "dest_column": "OPERID, OPERCODE, OPERTYPE, BRANCHNO, OPERNAME, TEL, MOBILE,
+ EMAIL, ADDR, OPERPWD, STATUS",
+ "post_exec": ["update t_syskey set keyval=(select max(operid) from t_operator)
+ where keycode='OPERID'",
+ "delete from t_operrole",
+ "insert into t_operrole(operroleid, operid, roleid) values(1, 51, 1)"
+ ]
+ }
+
+
\ No newline at end of file
diff --git a/convert-shu.json b/convert-shu.json
new file mode 100755
index 0000000..4f24c9b
--- /dev/null
+++ b/convert-shu.json
@@ -0,0 +1,379 @@
+/*
+ // 配置格式说明：
+ // name: 转换目标表名
+ // action: 装换前的操作，truncate 表示先执行truncate 操作，none 表示无任何动作
+ // desc: 转换规则的描述
+ // pre_exec: 执行转换前需要执行一次的 sql 语句，格式如 [ "insert into ....", "delete from ...."]，
+ // 可以有多条 sql 语句, 这个操作是在原始数据库执行
+ // post_exec: 转换完成后需要执行一次的 sql 语句， 格式同 pre_exec， 这个操作是在目标数据库执行
+ // src_sql: 转换原始表查询 sql
+ // dest_column: 转换目标表 column , src_sql 查询出来的字段与 dest_column 的字段
+*/
+[
+ {
+ "name": "T_DEVICE",
+ "action": "truncate",
+ "desc": "设备注册表",
+ "src_sql": "SELECT T1.DEVICE_ID,T1.DEVICE_NAME,T1.DEVPHY999_ID,T1.DEV999_NO,
+ T1.PHYTYPE DEVPHYTYPE,T1.DEVTYPE,'' DEVVERNO,T1.CARDSTR,T2.DEVICE_ID,
+ 1 RUNSTATUS,(case when T1.STATE_ID between 1 and 4 then 1
+ else 2 end) STATUS,
+ T1.PORTCOUNT,T1.PORTNO,T1.JOINMODE,T1.COMADD,nvl(trim(T1.PORT),0),T1.BAUDRATE,
+ '' CARDSET,T1.BSHEETVER,T1.LASTTRASERNO,T1.OPERATE_TIME LASTTRANSTIME,
+ trim(T1.PUSERNO),0 MAXAMT,T1.SUBSYSTEM_ID,T1.AREA,'' DOORLSTVER,T1.DEV_USAGE,
+ '' OPENDATE,'' CLOSEDATE,'' BATCHNO,'' LASTSAVED,'' REMARK FROM
+ YKT_CUR.T_PIF_DEVICE T1 LEFT JOIN YKT_CUR.T_PIF_DEVICE T2 ON
+ T1.FDEV_ID=T2.DEVICE_ID AND T1.DEVPHY999_ID NOT IN ('FFFFFFFF','00000000')
+ AND T2.STATE_ID<5 ORDER BY T1.DEVICE_ID",
+ "dest_column": "DEVICEID, DEVICENAME, DEVPHYID,DEVICENO, DEVPHYTYPE, DEVTYPECODE,
+ DEVVERNO, CARDPHYTYPE, FDEVICEID,RUNSTATUS, STATUS,SVRPORTCNT,
+ SVRPORTNO,COMMTYPE, IP,PORTNO, BAUDRATE, CARDSET,CARDVERNO,
+ LASTSEQNO,LASTTRANSTIME,PURSENO, MAXAMT, SYSID,AREACODE,
+ DOORLSTVER, DEVUSAGE,OPENDATE, CLOSEDATE, BATCHNO,
+ LASTSAVED, REMARK",
+ "post_exec": ["update t_syskey set keyval=(select max(deviceid) from t_device)
+ where keycode='TERMID'",
+ "update t_device x set devphyid=substr(devphyid,1,4)
+ || lpad(to_number(substr(devphyid,5,1), 'XX'),2,'0')
+ || lpad(to_number(substr(devphyid,6,1), 'XX'),2,'0')
+ || substr(devphyid,7,2)
+ where x.devphytype=1001 and length(x.devphyid) = 8
+ and regexp_like(devphyid, '[A-F0-9]{8}')"]
+ },
+ {
+ "name": "T_CUSTOMER",
+ "action": "truncate",
+ "desc": "客户信息表",
+ "src_sql": "select cut_id,stuemp_no,cut_type,fee_type,cut_state,cut_name,area,
+ classdept_no,classdept_name,s_code,class_no,class_no,1 idtype,man_id,
+ sex,'' country,nation,'' email,tel,'' mobile, address,'' zipcode,
+ '' custattr,substr(in_date,1,8),substr(can_time,1,8),
+ substr(reg_time,1,8) opendate,'' closedate,batch_no,1 openflag,
+ 0 holdcardcnt,1 useflag,0 eaccflag,'' lastsaved
+ from ykt_cur.t_cif_customer",
+ "dest_column": "CUSTID, STUEMPNO, CUSTTYPE, FEETYPE, STATUS, CUSTNAME, AREACODE,
+ DEPTCODE, DEPTFULLNAME, SPECIALTYCODE, CLASSCODE, CLASSNAME,
+ IDTYPE, IDNO, SEX, COUNTRY, NATION, EMAIL, TEL, MOBILE, ADDR,
+ ZIPCODE, CUSTATTR, INDATE,OUTDATE, OPENDATE, CLOSEDATE, BATCHNO,
+ OPENFLAG, HOLDCARDCNT, USEFLAG, EACCFLAG, LASTSAVED",
+ "post_exec": ["update t_customer a set holdcardcnt=(select count(*) from t_card t
+ where t.custid=a.custid and t.status=1)",
+ "update t_syskey set keyval=(select max(custid) from t_customer)
+ where keycode='CUSTID'"]
+ },
+ {
+ "name": "T_CARD",
+ "action": "truncate",
+ "desc": "卡信息表",
+ "src_sql": "select c.card_id,(case when a.account_id is null then '3000000001'
+ else '1'||substr(a.account_id,8) end),
+ type_id,phytype,(case when c1.fee_type>0 then c1.fee_type
+ else c1.cut_type end) feetype,
+ c.cosumer_id,c.showid,c.physical_no,c.password,
+ (case when c.end_time ='' then '20000101'
+ else c.end_time end) expiredate,
+ substr(c.state_id,1,1) status,
+ (case when substr(c.state_id,2,1)='1' then '1'
+ else '0' end) lossflag,
+ '' lossdate,'' lossefttime,
+ (case when substr(c.state_id,3,1)='1' then '1'
+ else '0' end) frozeflag,
+ '' frozedate,'0' badflag,'' badtype,'' baddate,'0' lockflag,
+ '' lockdate,c.begin_time opendate,'' closedate,'' cardverno,'' lastsaved
+ from YKT_CUR.T_PIF_CARD c left join ykt_cur.t_aif_account a on
+ c.card_id=a.card_id left join ykt_cur.t_cif_customer c1 on
+ c.cosumer_id=c1.cut_id order by c.card_id",
+ "dest_column": "CARDNO, ACCNO, CARDTYPE,CARDPHYTYPE, FEETYPE, CUSTID, SHOWCARDNO,
+ CARDPHYID, CARDPWD,EXPIREDATE, STATUS, LOSSFLAG, LOSSDATE, LOSSEFTTIME,
+ FROZEFLAG, FROZEDATE, BADFLAG, BADTYPE, BADDATE, LOCKFLAG, LOCKDATE,
+ OPENDATE, CLOSEDATE, CARDVERNO, LASTSAVED",
+ "remark": "导入卡表前更新 begin_time 不能为空",
+ "post_exec": ["update t_syskey set keyval=(select max(cardno) from t_card)
+ where keycode='CARDNO'",
+ "update t_card set cardphytype=20"]
+ },
+ {
+ "name": "T_SHOPACC",
+ "action": "truncate",
+ "desc": "商户账户表",
+ "src_sql": "select (case when a.account_id is null then ''
+ else '2'||substr(a.account_id,8) end) accno,
+ s.shop_id shopid,a.cut_name,'2004',a.password,a.current_state,
+ a.last_bala ydaybal,a.last_bala balance,0,0,'' lasttransdate,
+ '' lastaccdate, a.open_date,a.close_date from
+ ykt_cur.t_aif_account a left join ykt_cur.t_cif_shop s on
+ a.customer_id=s.cut_id where a.act_type=2 and s.shop_id is not null
+ order by s.shop_id",
+ "dest_column": "ACCNO, SHOPID, ACCNAME, SUBJNO, ACCPWD, STATUS, YDAYBAL, BALANCE,
+ AVAILBAL, FROZEBAL, LASTTRANSDATE, LASTACCDATE, OPENDATE, CLOSEDATE",
+ "post_exec": ["update t_syskey set keyval=(select max(to_number(substr(accno,2)))
+ from t_shopacc) where keycode='SHOPACCNO'"]
+ },
+ {
+ "desc": "商户信息表",
+ "name": "T_SHOP",
+ "action": "truncate",
+ "src_sql":"select t.shop_id,t.dept_id,'' areacode,'' deptcode,t.shop_name shopname,
+ (case when t.is_leaf ='1' then 1 else 0 end) shop_type,
+ (case when t.is_leaf='1' then 1 else 0 end) accflag ,shop_state,
+ (case when a.account_id is null then '' else '2'||substr(a.account_id,8) end) accno,
+ t.man,1 idtype,t.man_id,t.tel,'' mobile,'' email,t.address,'' zipcode,
+ (case when t.is_getfee='1' then 1 else 0 end) boardfeeflag,'' RAKEOFFTYPE,
+ 0, '' DEPOSITAGIO,'' DEPOSITCNT,'' DEPOSITAMT,'' DISCOUNTAMT, '' BANKCODE,
+ '' BANKACCNO,'' BANKACCNAME,'' OPENDATE,'' CLOSEDATE,'' USEFLAG
+ from ykt_cur.t_cif_shop t left join ykt_cur.t_aif_account a on
+ t.cut_id=a.customer_id and t.is_leaf='1'",
+ "dest_column": "SHOPID, FSHOPID, AREACODE,DEPTCODE, SHOPNAME, SHOPTYPE,ACCFLAG,
+ STATUS, ACCNO, CONTACTMAN, IDTYPE, IDNO, TEL, MOBILE, EMAIL, ADDR,
+ ZIPCODE, BOARDFEEFLAG, RAKEOFFTYPE, RAKEOFFRATE, DEPOSITAGIO,DEPOSITCNT,
+ DEPOSITAMT, DISCOUNTAMT, BANKCODE, BANKACCNO, BANKACCNAME, OPENDATE,
+ CLOSEDATE, USEFLAG",
+ "post_exec": ["update t_syskey set keyval=(select max(shopid) from t_shop)
+ where keycode='SHOPID'",
+ "update t_shop set areacode=11 where rowid in (
+ select rowid shop_rowid from t_shop c
+ start with c.shopid=11
+ connect by prior c.shopid=c.fshopid)",
+ "update t_shop set areacode=12 where rowid in (
+ select rowid shop_rowid from t_shop c
+ start with c.shopid=43
+ connect by prior c.shopid=c.fshopid)",
+ "update t_shop set areacode=13 where rowid in (
+ select rowid shop_rowid from t_shop c
+ start with c.shopid=44
+ connect by prior c.shopid=c.fshopid)"]
+ },
+ {
+ "name": "T_INNERACC",
+ "action": "truncate",
+ "desc": "内部账户表",
+ "src_sql": "select (case when a.account_id='1011' then '1001'
+ when a.account_id='1021' then '1002'
+ when a.account_id='1031' then '112101'
+ when a.account_id='1041' then '112102'
+ when a.account_id='2051' then '2002'
+ when a.account_id='2041' then '220299'
+ when a.account_id='2071' then '2203'
+ when a.account_id='5011' then '6002'
+ when a.account_id='5021' then '6005'
+ when a.account_id='5041' then '6001'
+ when a.account_id='5071' then '6201'
+ else a.account_id end) account_id,
+ a.cut_name, (case when a.account_id='1011' then '1001'
+ when a.account_id='1021' then '1002'
+ when a.account_id='1031' then '112101'
+ when a.account_id='1041' then '112102'
+ when a.account_id='2051' then '2002'
+ when a.account_id='2041' then '220299'
+ when a.account_id='2071' then '2203'
+ when a.account_id='5011' then '6002'
+ when a.account_id='5021' then '6005'
+ when a.account_id='5041' then '6001'
+ when a.account_id='5071' then '6201'
+ else a.account_id end) subno,
+ 0 ,last_BALA,(CASE WHEN SUBSTR(A.ACCOUNT_ID,1,1)='1' THEN 1
+ ELSE 2 END) BALFLAG,
+ '','' from (select rtrim(a.account_id) account_id,a.last_bala,
+ a.cut_name from ykt_cur.t_aif_account a where a.act_type=7 and
+ a.account_id not in ('1122','2031','1071')) a",
+ "dest_column": "ACCNO, ACCNAME, SUBJNO, YDAYBAL, BALANCE, BALFLAG, LASTTRANSDATE,
+ LASTACCDATE",
+ "remark": "导入内部账户，并更新错误的借贷标志",
+ "post_exec": ["update t_inneracc set balance=abs(balance),ydaybal=abs(ydaybal),
+ balflag=1 where accno in ('6201','6202')",
+ "update t_syspara set paraval=:bala_date where paraid=2",
+ "insert into T_INNERACC (ACCNO, ACCNAME, SUBJNO, YDAYBAL, BALANCE, BALFLAG, LASTTRANSDATE, LASTACCDATE)
+ values ('112201', '易支付充值款', '112201', 0, 0, 1, null, null)",
+ "insert into T_INNERACC (ACCNO, ACCNAME, SUBJNO, YDAYBAL, BALANCE, BALFLAG, LASTTRANSDATE, LASTACCDATE)
+ values ('112299', '其他应收款', '112299', 0, 0, 1, null, null)",
+ "insert into T_INNERACC (ACCNO, ACCNAME, SUBJNO, YDAYBAL, BALANCE, BALFLAG, LASTTRANSDATE, LASTACCDATE)
+ values ('2006', '水控存款', '2006', 0, 0, 2, null, null)",
+ "insert into T_INNERACC (ACCNO, ACCNAME, SUBJNO, YDAYBAL, BALANCE, BALFLAG, LASTTRANSDATE, LASTACCDATE)
+ values ('220201', '批量销户应付款', '220201', 0, 0, 2, null, null)",
+ "insert into T_INNERACC (ACCNO, ACCNAME, SUBJNO, YDAYBAL, BALANCE, BALFLAG, LASTTRANSDATE, LASTACCDATE)
+ values ('6003', '商户佣金收入', '6003', 0, 0, 2, null, null)",
+ "insert into T_INNERACC (ACCNO, ACCNAME, SUBJNO, YDAYBAL, BALANCE, BALFLAG, LASTTRANSDATE, LASTACCDATE)
+ values ('6004', '搭伙费收入', '6004', 0, 0, 2, null, null)",
+ "insert into T_INNERACC (ACCNO, ACCNAME, SUBJNO, YDAYBAL, BALANCE, BALFLAG, LASTTRANSDATE, LASTACCDATE)
+ values ('6006', '退卡盈余收入', '6006', 0, 0, 2, null, null)",
+ "insert into T_INNERACC (ACCNO, ACCNAME, SUBJNO, YDAYBAL, BALANCE, BALFLAG, LASTTRANSDATE, LASTACCDATE)
+ values ('6202', '商户提成支出', '6202', 0, 0, 1, null, null)"
+ ]
+ },
+ {
+ "desc": "账户信息表",
+ "name": "T_ACCOUNT",
+ "action": "truncate",
+ "src_sql": "select (case when a.account_id is null then ''
+ else '1'||substr(a.account_id,8) end) accno,
+ a.cut_name,'2001',a.password,a.customer_id,a.card_id,a.purse_id,
+ '' singlemaxamt,'' daycostmaxamt,'' daycostamt,'' cardmaxbal,
+ a.current_state,(case when a.current_state=1 then c.deposit_fee
+ else 0 end) foregift,
+ 0 ydaybal,a.last_bala,a.last_freebala,a.last_frozebala,0 subsidybal,
+ 0 subsidyno,a.card_balance,a.consume_count,0 dpscnt,a.consume_count paycnt,
+ 0 termid,'' transtime,0 lastsubsidyamt,0 lastsubsidyno,0 lastbala,
+ 0 lastcardcnt,0 lastdpscnt,0 lastpaycnt,'' lasttransdate,'' lastaccdate,
+ a.open_date,a.close_date from ykt_cur.t_aif_account a left join
+ ykt_cur.t_pif_card c on a.card_id=c.card_id where a.act_type=1 and a.current_state<>2",
+ "dest_column": "ACCNO, ACCNAME, SUBJNO, ACCPWD, CUSTID, CARDNO, PURSENO, SINGLEMAXAMT,
+ DAYCOSTMAXAMT,DAYCOSTAMT, CARDMAXBAL, STATUS, FOREGIFT, YDAYBAL,
+ BALANCE, AVAILBAL, FROZEBAL, SUBSIDYBAL, SUBSIDYNO, CARDBAL, CARDCNT,
+ DPSCNT, PAYCNT, TERMID, TRANSTIME, LASTSUBSIDYAMT, LASTSUBSIDYNO,
+ LASTCARDBAL, LASTCARDCNT, LASTDPSCNT, LASTPAYCNT, LASTTRANSDATE,
+ LASTACCDATE, OPENDATE, CLOSEDATE",
+ "post_exec":["update t_syskey set keyval=(select max(to_number(substr(accno,2)))
+ from t_account) where keycode='CARDACCNO'",
+ "update t_account set balance=balance+:diff_bala,
+ availbal=availbal+:diff_bala where accno='1002246656'"]
+ },
+ {
+ "name": "T_SHOPPOS",
+ "action": "truncate",
+ "desc": "商户设备对应表",
+ "src_sql": "select t1.id,t1.device_id,t2.devphy999_id,t1.shop_id,t1.end_time,
+ t1.comments,t1.begin_date,t1.end_date,1,'','' from
+ ykt_cur.t_cif_shop_pos t1,ykt_cur.t_pif_device t2
+ where t1.device_id=t2.device_id",
+ "dest_column": "ID, DEVICEID, DEVPHYID, SHOPID, ENDTIME, REMARK, STARTDATE,
+ ENDDATE, STATUS, OPERCODE, LASTSAVED",
+ "post_exec" : ["update t_shoppos t set t.devphyid=(select x.devphyid from t_device x
+ where x.deviceid=t.deviceid)"]
+ },
+ {
+ "name": "T_RPTACCBAL",
+ "action": "truncate",
+ "desc": "账户结算表",
+ "src_sql": "select :tx_date,'2001',(case when a.account_id is null then ''
+ else '1'||substr(a.account_id,8) end) accno,
+ 2 ,last_bala from ykt_cur.t_aif_account a where act_type=1 and current_state<>2
+ union all
+ select :tx_date,'2004',(case when a.account_id is null then ''
+ else '2'||substr(a.account_id,8) end) accno,
+ 2,last_bala from ykt_cur.t_aif_account a where act_type=2 and current_state<>2
+ union all
+ select :tx_date, (case when a.account_id='1011' then '1001'
+ when a.account_id='1021' then '1002'
+ when a.account_id='1031' then '112101'
+ when a.account_id='1041' then '112102'
+ when a.account_id='2041' then '220299'
+ when a.account_id='2051' then '2002'
+ when a.account_id='2071' then '2203'
+ when a.account_id='5011' then '6002'
+ when a.account_id='5021' then '6005'
+ when a.account_id='5041' then '6001'
+ when a.account_id='5071' then '6201'
+ else rtrim(a.account_id) end) subno,
+ (case when a.account_id='1011' then '1001'
+ when a.account_id='1021' then '1002'
+ when a.account_id='1031' then '112101'
+ when a.account_id='1041' then '112102'
+ when a.account_id='2051' then '2002'
+ when a.account_id='2041' then '220299'
+ when a.account_id='2071' then '2203'
+ when a.account_id='5011' then '6002'
+ when a.account_id='5021' then '6005'
+ when a.account_id='5041' then '6001'
+ when a.account_id='5071' then '6201'
+ else rtrim(a.account_id) end) account_id,
+ (case when substr(a.account_id,1,1)='1' then 1 else 2 end) flag,
+ last_bala from ykt_cur.t_aif_account a where act_type=7 and
+ a.account_id not in ('1122','2031','1071')",
+ "dest_column": "ACCDATE, SUBJNO, ACCNO, BALFLAG, BALANCE",
+ "remark": "导入内部账户报表，并更新错误的借贷标志",
+ "post_exec": ["update t_rptaccbal set balance=abs(balance),balflag=1
+ where accno in ('6201','6202')",
+ // "insert into t_rptaccbal(accdate,subjno,accno,balflag,balance)
+ // select :tx_date,s.subjno,s.subjno,
+ // (case when substr(s.subjno,1,1)='1' then 1
+ // else 2 end) flag1,0
+ // from t_subject s left join t_rptaccbal b on
+ // (s.subjno=b.subjno and b.accdate=:tx_date)
+ // where b.accdate is null",
+ "insert into t_rptaccbal(accdate,subjno,accno,balflag,balance)
+ select :tx_date,s.subjno,s.subjno,2,0 from t_rptsubjbal s
+ where s.subjno in (2001,2004,2006,220201,6004)
+ and s.accdate=:tx_date",
+ "insert into t_rptaccbal(accdate,subjno,accno,balflag,balance)
+ select :tx_date,s.subjno,s.subjno,1,0 from t_rptsubjbal s
+ where s.subjno in (6202)
+ and s.accdate=:tx_date",
+ "update t_rptaccbal set balance=balance+1.93 where accdate=:tx_date
+ and accno='1002246656'"
+ ]
+ },
+ {
+ "name": "T_RPTSUBJBAL",
+ "action": "truncate",
+ "desc": "科目结算表",
+ "src_sql": "select :tx_date,'2001',2 ,sum(last_bala),0,0,2,sum(last_bala)
+ from ykt_cur.t_aif_account a where act_type=1 and a.current_state<>2
+ union all
+ select :tx_date,'2004',2,sum(last_bala),0,0,2,sum(last_bala)
+ from ykt_cur.t_aif_account a where act_type=2 and a.current_state<>2
+ union all select :tx_date,
+ (case when a.account_id='1011' then '1001'
+ when a.account_id='1021' then '1002'
+ when a.account_id='1031' then '112101'
+ when a.account_id='1041' then '112102'
+ when a.account_id='2051' then '2002'
+ when a.account_id='2041' then '220299'
+ when a.account_id='2071' then '2203'
+ when a.account_id='5011' then '6002'
+ when a.account_id='5021' then '6005'
+ when a.account_id='5041' then '6001'
+ when a.account_id='5071' then '6201'
+ else rtrim(a.account_id) end) subno,
+ (case when substr(a.account_id,1,1)='1' then 1
+ else 2 end) flag1,
+ sum(last_bala),0,0,
+ (case when substr(a.account_id,1,1)='1' then 1
+ else 2 end) flag2,sum(last_bala) from
+ ykt_cur.t_aif_account a where act_type=7 and
+ a.account_id not in ('1122','2031','1071')
+ group by a.account_id",
+ "dest_column": "ACCDATE, SUBJNO, BEGINBALFLAG, BEGINBAL, DRAMT, CRAMT,
+ ENDBALFLAG, ENDBAL",
+ "post_exec" : ["insert into t_rptsubjbal(ACCDATE, SUBJNO, BEGINBALFLAG, BEGINBAL,
+ DRAMT, CRAMT, ENDBALFLAG, ENDBAL)
+ select :tx_date, subjno, 1,
+ (select nvl(sum(beginbal),0) from t_rptsubjbal t where accdate=:tx_date
+ and t.subjno like s.subjno || '__'),
+ 0,0,1,
+ (select nvl(sum(endbal),0) from t_rptsubjbal t where accdate=:tx_date
+ and t.subjno like s.subjno || '__') from t_subject s
+ where subjno in ('1121','6202')",
+ "insert into t_rptsubjbal(ACCDATE, SUBJNO, BEGINBALFLAG, BEGINBAL,
+ DRAMT, CRAMT, ENDBALFLAG, ENDBAL)
+ select :tx_date, subjno, 2,
+ (select nvl(sum(beginbal),0) from t_rptsubjbal t where accdate=:tx_date
+ and t.subjno like s.subjno || '__'),
+ 0,0,2,
+ (select nvl(sum(endbal),0) from t_rptsubjbal t where accdate=:tx_date
+ and t.subjno like s.subjno || '__') from t_subject s
+ where subjno in ('2202')",
+ "update t_rptsubjbal set beginbalflag=1,endbalflag=1,
+ beginbal=abs(beginbal),endbal=abs(endbal) where
+ subjno in ('6201','6202')",
+ "update t_rptsubjbal set beginbal=beginbal+:diff_bala, endbal=endbal+:diff_bala
+ where accdate=:tx_date and subjno='2001'",
+ "delete from t_servicelog"],
+ "remark": "生成结算科目数据"
+ },
+ {
+ "name": "T_CARDVER",
+ "action": "truncate",
+ "desc": "卡版本号表",
+ "src_sql": " select t.operate_date, 0, 0, t.volume, t.card_id, x.physical_no, c.stuemp_no,
+ case when is_ad = 0 then 3
+ when is_ad = 1 then 6
+ else 3 end ,
+ case when t.is_ad = 0 then 1
+ when t.is_ad = 1 then 2
+ else 0 end , t.state_id
+ from t_tif_black_sheet t, t_pif_card x, t_cif_customer c
+ where t.card_id = x.card_id and x.cosumer_id = c.cut_id",
+ "dest_column": "ACCDATE, TERMID, TERMSEQNO, CARDVERNO, CARDNO, CARDPHYID, STUEMPNO,
+ CARDVERTYPE, ADDDELFLAG, STATUS"
+ }
+]
diff --git a/cvtcfg-shisu.ini b/cvtcfg-shisu.ini
new file mode 100644
index 0000000..b612748
--- /dev/null
+++ b/cvtcfg-shisu.ini
@@ -0,0 +1,22 @@
+[database]
+srctype=oracle
+# srcdsn=yktdb.shisu
+srchost=10.1.6.30
+srcport=1521
+srcname=yktdb
+srcuser=ykt_cur
+srcpswd=kingstar1
+
+dsttype=oracle
+# dstname=ecarddb.shisu
+dstport=1521
+#dsthost=10.1.6.110
+#dstname=ecardt
+#dstuser=ecard
+#dstpswd=kingstar
+dsthost=10.1.6.30
+dstname=ecarddb
+dstuser=ecard
+dstpswd=ecard#shisu2013
+
+commitcount=5000
diff --git a/cvtcfg-shu.ini b/cvtcfg-shu.ini
new file mode 100644
index 0000000..62f324c
--- /dev/null
+++ b/cvtcfg-shu.ini
@@ -0,0 +1,17 @@
+[database]
+srctype=oracle
+#srcdsn=
+srchost=10.10.1.253
+srcport=1521
+srcname=yktdb
+srcuser=ykt_cur
+srcpswd=kingstar
+
+dsttype=oracle
+dstname=ecarddb
+dsthost=10.10.1.253
+dstport=1521
+dstuser=ecard
+dstpswd=shu#ecard#4
+
+commitcount=2000
diff --git a/shisu-bakcup.json b/shisu-bakcup.json
new file mode 100644
index 0000000..b029754
--- /dev/null
+++ b/shisu-bakcup.json
@@ -0,0 +1,545 @@
+{
+ "desc": "商户信息表",
+ "name": "T_SHOP",
+ "action": "truncate",
+ "src_sql":"select t.shop_id,t.dept_id,'' areacode,'' deptcode,t.shop_name shopname,
+ (case when t.is_leaf ='1' then 1 else 0 end) shop_type,
+ (case when t.is_leaf='1' then 1 else 0 end) accflag ,shop_state,
+ (case when a.account_id is null then '' else '2'||substr(a.account_id,8) end) accno,
+ t.man,1 idtype,t.man_id,t.tel,'' mobile,'' email,t.address,'' zipcode,
+ (case when t.is_getfee='1' then 1 else 0 end) boardfeeflag,'' RAKEOFFTYPE,
+ t.ratio, '' DEPOSITAGIO,'' DEPOSITCNT,'' DEPOSITAMT,'' DISCOUNTAMT, '' BANKCODE,
+ '' BANKACCNO,'' BANKACCNAME,'' OPENDATE,'' CLOSEDATE,'' USEFLAG
+ from ykt_cur.t_cif_shop t left join ykt_cur.t_aif_account a on
+ t.cut_id=a.customer_id and t.is_leaf='1'",
+ "dest_column": "SHOPID, FSHOPID, AREACODE,DEPTCODE, SHOPNAME, SHOPTYPE,ACCFLAG,
+ STATUS, ACCNO, CONTACTMAN, IDTYPE, IDNO, TEL, MOBILE, EMAIL, ADDR,
+ ZIPCODE, BOARDFEEFLAG, RAKEOFFTYPE, RAKEOFFRATE, DEPOSITAGIO,DEPOSITCNT,
+ DEPOSITAMT, DISCOUNTAMT, BANKCODE, BANKACCNO, BANKACCNAME, OPENDATE,
+ CLOSEDATE, USEFLAG",
+ "post_exec": ["update t_syskey set keyval=(select max(shopid) from t_shop)
+ where keycode='SHOPID'"]
+ },
+ {
+ "desc": "账户信息表",
+ "name": "T_ACCOUNT",
+ "action": "truncate",
+ "src_sql": "select (case when a.account_id is null then ''
+ else '1'||substr(a.account_id,8) end) accno,
+ a.cut_name,'2001',a.password,a.customer_id,a.card_id,a.purse_id,
+ '' singlemaxamt,'' daycostmaxamt,'' daycostamt,'' cardmaxbal,
+ a.current_state,(case when a.current_state=1 then c.deposit_fee
+ else 0 end) foregift,
+ 0 ydaybal,a.cur_bala,a.cur_freebala,a.cur_frozebala,0 subsidybal,
+ 0 subsidyno,a.card_balance,a.consume_count,0 dpscnt,a.consume_count paycnt,
+ 0 termid,'' transtime,0 lastsubsidyamt,0 lastsubsidyno,0 lastbala,
+ 0 lastcardcnt,0 lastdpscnt,0 lastpaycnt,'' lasttransdate,'' lastaccdate,
+ a.open_date,a.close_date from ykt_cur.t_aif_account a left join
+ ykt_cur.t_pif_card c on a.card_id=c.card_id where a.act_type=1",
+ "dest_column": "ACCNO, ACCNAME, SUBJNO, ACCPWD, CUSTID, CARDNO, PURSENO, SINGLEMAXAMT,
+ DAYCOSTMAXAMT,DAYCOSTAMT, CARDMAXBAL, STATUS, FOREGIFT, YDAYBAL,
+ BALANCE, AVAILBAL, FROZEBAL, SUBSIDYBAL, SUBSIDYNO, CARDBAL, CARDCNT,
+ DPSCNT, PAYCNT, TERMID, TRANSTIME, LASTSUBSIDYAMT, LASTSUBSIDYNO,
+ LASTCARDBAL, LASTCARDCNT, LASTDPSCNT, LASTPAYCNT, LASTTRANSDATE,
+ LASTACCDATE, OPENDATE, CLOSEDATE",
+ "post_exec":["update t_syskey set keyval=(select max(to_number(substr(accno,2)))
+ from t_account) where keycode='CARDACCNO'",
+ "update t_account set balance=balance+60, availbal=AVAILBAL+60, foregift=120
+ where accno='1' || substr('1000000000111416', 8) "]
+ },
+ {
+ "name": "T_AREA",
+ "action": "truncate",
+ "desc": "区域信息表",
+ "src_sql": "select area_no,area_name,father_area_no,'' oldareacode,
+ arealevel,address,comments from ykt_cur.t_pif_area",
+ "dest_column": "AREACODE, AREANAME, FAREACODE, OLDAREACODE, AREALEVEL, ADDR, REMARK"
+ },
+ {
+ "name": "T_AUTHCARD",
+ "action": "truncate",
+ "desc": "授权卡信息表",
+ "src_sql": "select rownum,'' opercode,physical_no,'' showcardno,
+ 0 privilege,end_time,state_id,0 lossflag,'' lossdate,
+ substr(begin_time,1,4)||'0101' opendate,'' closedate
+ from YKT_CUR.T_PIF_AUTHCARD",
+ "dest_column": "CARDNO, OPERCODE, CARDPHYID, SHOWCARDNO, PRIVILEGE, EXPIREDATE,
+ STATUS, LOSSFLAG, LOSSDATE, OPENDATE, CLOSEDATE"
+ },
+ {
+ "name": "T_CARD",
+ "action": "truncate",
+ "desc": "卡信息表",
+ "src_sql": "select c.card_id,(case when a.account_id is null then '3000000001'
+ else '1'||substr(a.account_id,8) end),
+ type_id,phytype,(case when c1.fee_type>0 then c1.fee_type
+ else c1.cut_type end) feetype,
+ c.cosumer_id,c.showid,c.physical_no,c.password,
+ (case when c.end_time ='' then '20000101'
+ else c.end_time end) expiredate,
+ substr(c.state_id,1,1) status,
+ (case when substr(c.state_id,2,1)='1' then '1'
+ else '0' end) lossflag,
+ '' lossdate,'' lossefttime,
+ (case when substr(c.state_id,3,1)='1' then '1'
+ else '0' end) frozeflag,
+ '' frozedate,'0' badflag,'' badtype,'' baddate,'0' lockflag,
+ '' lockdate,c.begin_time opendate,'' closedate,'' cardverno,'' lastsaved
+ from YKT_CUR.T_PIF_CARD c left join ykt_cur.t_aif_account a on
+ c.card_id=a.card_id left join ykt_cur.t_cif_customer c1 on
+ c.cosumer_id=c1.cut_id order by c.card_id",
+ "dest_column": "CARDNO, ACCNO, CARDTYPE,CARDPHYTYPE, FEETYPE, CUSTID, SHOWCARDNO,
+ CARDPHYID, CARDPWD,EXPIREDATE, STATUS, LOSSFLAG, LOSSDATE, LOSSEFTTIME,
+ FROZEFLAG, FROZEDATE, BADFLAG, BADTYPE, BADDATE, LOCKFLAG, LOCKDATE,
+ OPENDATE, CLOSEDATE, CARDVERNO, LASTSAVED",
+ "remark": "导入卡表前更新 begin_time 不能为空",
+ "post_exec": ["update t_syskey set keyval=(select max(cardno) from t_card)
+ where keycode='CARDNO'",
+ "update t_card set cardphytype=20"]
+ },
+ {
+ "name": "T_CARDVER",
+ "action": "truncate",
+ "desc": "卡版本号表",
+ "src_sql": " select t.operate_date, 0, 0, t.volume, t.card_id, x.physical_no, c.stuemp_no,
+ case when is_ad = 0 then 3
+ when is_ad = 1 then 6
+ else 3 end ,
+ case when t.is_ad = 0 then 1
+ when t.is_ad = 1 then 2
+ else 0 end , t.state_id
+ from t_tif_black_sheet t, t_pif_card x, t_cif_customer c
+ where t.card_id = x.card_id and x.cosumer_id = c.cut_id",
+ "dest_column": "ACCDATE, TERMID, TERMSEQNO, CARDVERNO, CARDNO, CARDPHYID, STUEMPNO,
+ CARDVERTYPE, ADDDELFLAG, STATUS"
+ },
+ {
+ "name": "T_CUSTOMER",
+ "action": "truncate",
+ "desc": "客户信息表",
+ "src_sql": "select cut_id,stuemp_no,cut_type,fee_type,cut_state,cut_name,area,
+ classdept_no,classdept_name,s_code,class_no,class_no,1 idtype,man_id,
+ sex,'' country,nation,'' email,tel,'' mobile, address,'' zipcode,
+ '' custattr,substr(in_date,1,8),substr(can_time,1,8),
+ substr(reg_time,1,8) opendate,'' closedate,batch_no,1 openflag,
+ 0 holdcardcnt,1 useflag,0 eaccflag,'' lastsaved
+ from ykt_cur.t_cif_customer",
+ "dest_column": "CUSTID, STUEMPNO, CUSTTYPE, FEETYPE, STATUS, CUSTNAME, AREACODE,
+ DEPTCODE, DEPTFULLNAME, SPECIALTYCODE, CLASSCODE, CLASSNAME,
+ IDTYPE, IDNO, SEX, COUNTRY, NATION, EMAIL, TEL, MOBILE, ADDR,
+ ZIPCODE, CUSTATTR, INDATE,OUTDATE, OPENDATE, CLOSEDATE, BATCHNO,
+ OPENFLAG, HOLDCARDCNT, USEFLAG, EACCFLAG, LASTSAVED",
+ "post_exec": ["update t_customer a set holdcardcnt=(select count(*) from t_card t
+ where t.custid=a.custid and t.status=1)",
+ "update t_syskey set keyval=(select max(custid) from t_customer)
+ where keycode='CUSTID'"]
+ },
+ {
+ "name": "T_CUSTTYPE",
+ "action": "truncate",
+ "desc": "客户类型表",
+ "src_sql": "SELECT CUT_TYPE,TYPE_NAME,CUT_TYPE,0 AREACODE,0 OLDCUSTTYPE,0 USEFLAG
+ FROM YKT_CUR.T_CIF_CUTTYPEFEE",
+ "dest_column": "CUSTTYPE, CUSTTYPENAME, FEETYPE,AREACODE, OLDCUSTTYPE, USEFLAG"
+ },
+ {
+ "name": "T_DEPT",
+ "action": "truncate",
+ "desc": "部门表",
+ "src_sql": "SELECT DEPT_CODE,0 AREACODE,DEPT_NAME,E_NAME,DEPT_NAME,PARENTDEPT_CODE,
+ DEPT_LEVEL,IS_ACADEMY,DEPT_CODE,0 USEFLAG,'' LASTSAVED
+ FROM YKT_CUR.T_CIF_DEPT",
+ "dest_column": "DEPTCODE, AREACODE, DEPTNAME, DEPTENAME, DEPTFULLNAME, FDEPTCODE, DEPTLEVEL, DEPTTYPE, OLDDEPTCODE, USEFLAG, LASTSAVED"
+ },
+ {
+ "name": "T_DEVICE",
+ "action": "truncate",
+ "desc": "设备注册表",
+ "src_sql": "SELECT T1.DEVICE_ID,T1.DEVICE_NAME,T1.DEVPHY999_ID,T1.DEV999_NO,
+ T1.PHYTYPE DEVPHYTYPE,T1.DEVTYPE,'' DEVVERNO,T1.CARDSTR,T2.DEVICE_ID,
+ 1 RUNSTATUS,(case when T1.STATE_ID between 1 and 4 then 1
+ else 2 end) STATUS,
+ T1.PORTCOUNT,T1.PORTNO,T1.JOINMODE,T1.COMADD,nvl(trim(T1.PORT),0),T1.BAUDRATE,
+ '' CARDSET,T1.BSHEETVER,T1.LASTTRASERNO,T1.OPERATE_TIME LASTTRANSTIME,
+ trim(T1.PUSERNO),0 MAXAMT,T1.SUBSYSTEM_ID,T1.AREA,'' DOORLSTVER,T1.DEV_USAGE,
+ '' OPENDATE,'' CLOSEDATE,'' BATCHNO,'' LASTSAVED,'' REMARK FROM
+ YKT_CUR.T_PIF_DEVICE T1 LEFT JOIN YKT_CUR.T_PIF_DEVICE T2 ON
+ T1.FDEV_ID=T2.DEVICE_ID AND T1.DEVPHY999_ID NOT IN ('FFFFFFFF','00000000')
+ AND T2.STATE_ID<5 ORDER BY T1.DEVICE_ID",
+ "dest_column": "DEVICEID, DEVICENAME, DEVPHYID,DEVICENO, DEVPHYTYPE, DEVTYPECODE,
+ DEVVERNO, CARDPHYTYPE, FDEVICEID,RUNSTATUS, STATUS,SVRPORTCNT,
+ SVRPORTNO,COMMTYPE, IP,PORTNO, BAUDRATE, CARDSET,CARDVERNO,
+ LASTSEQNO,LASTTRANSTIME,PURSENO, MAXAMT, SYSID,AREACODE,
+ DOORLSTVER, DEVUSAGE,OPENDATE, CLOSEDATE, BATCHNO,
+ LASTSAVED, REMARK",
+ "post_exec": ["update t_syskey set keyval=(select max(deviceid) from t_device)
+ where keycode='TERMID'",
+ "update t_device x set devphyid=substr(devphyid,1,4)
+ || lpad(to_number(substr(devphyid,5,1), 'XX'),2,'0')
+ || lpad(to_number(substr(devphyid,6,1), 'XX'),2,'0')
+ || substr(devphyid,7,2)
+ where x.devphytype=1001 and length(x.devphyid) = 8"]
+ },
+ {
+ "name": "T_FEETYPE",
+ "action": "truncate",
+ "desc": "收费类别表",
+ "src_sql": "SELECT FEE_TYPE,SUBSTR(FEE_NAME,1,60),IS_OUTFEE,0,0
+ FROM YKT_CUR.T_PIF_FEETYPE",
+ "dest_column": "FEETYPE, FEENAME, BOARDFLAG,BOARDRATE, DPSRATE"
+ },
+ {
+ "name": "T_INNERACC",
+ "action": "truncate",
+ "desc": "内部账户表",
+ "src_sql": "select (case when a.account_id='1011' then '1001'
+ when a.account_id='1021' then '1002'
+ when a.account_id='1031' then '112101'
+ when a.account_id='1041' then '112102'
+ when a.account_id='1051' then '112103'
+ when a.account_id='2051' then '2002'
+ when a.account_id='2061' then '220299'
+ when a.account_id='2071' then '2203'
+ when a.account_id='5011' then '6002'
+ when a.account_id='5021' then '6005'
+ when a.account_id='5051' then '6003'
+ when a.account_id='5041' then '6006'
+ when a.account_id='5071' then '6001'
+ else a.account_id end) account_id,
+ a.cut_name, (case when a.account_id='1011' then '1001'
+ when a.account_id='1021' then '1002'
+ when a.account_id='1031' then '112101'
+ when a.account_id='1041' then '112102'
+ when a.account_id='1051' then '112103'
+ when a.account_id='2051' then '2002'
+ when a.account_id='2061' then '220299'
+ when a.account_id='2071' then '2203'
+ when a.account_id='5011' then '6002'
+ when a.account_id='5021' then '6005'
+ when a.account_id='5051' then '6003'
+ when a.account_id='5041' then '6006'
+ when a.account_id='5071' then '6001'
+ else a.account_id end) subno,
+ 0 ,cur_BALA,(CASE WHEN SUBSTR(A.ACCOUNT_ID,1,1)='1' THEN 1
+ ELSE 2 END) BALFLAG,
+ '','' from (select rtrim(a.account_id) account_id,a.cur_bala,
+ a.cut_name from ykt_cur.t_aif_account a where a.act_type=7 and
+ a.account_id not in ('1122','2031','2041','1071')) a",
+ "dest_column": "ACCNO, ACCNAME, SUBJNO, YDAYBAL, BALANCE, BALFLAG, LASTTRANSDATE,
+ LASTACCDATE",
+ "remark": "导入内部账户，并更新错误的借贷标志",
+ "post_exec": ["update t_inneracc set balance=abs(balance),ydaybal=abs(ydaybal),
+ balflag=1 where accno in ('6201','6202')",
+ "update t_syspara set paraval=:bala_date where paraid=2",
+ "insert into T_INNERACC (ACCNO, ACCNAME, SUBJNO, YDAYBAL, BALANCE,
+ BALFLAG, LASTTRANSDATE, LASTACCDATE)
+ values ('112201', '易支付充值款', '112201', 0, 0, 1, null, null)",
+ "insert into T_INNERACC (ACCNO, ACCNAME, SUBJNO, YDAYBAL, BALANCE,
+ BALFLAG, LASTTRANSDATE, LASTACCDATE)
+ values ('112299', '其他应收款', '112299', 0, 0, 1, null, null)",
+ "insert into T_INNERACC (ACCNO, ACCNAME, SUBJNO, YDAYBAL, BALANCE,
+ BALFLAG, LASTTRANSDATE, LASTACCDATE)
+ values ('6004', '搭伙费收入', '6004', 0, 0, 2, null, null)",
+ "insert into T_INNERACC (ACCNO, ACCNAME, SUBJNO, YDAYBAL, BALANCE,
+ BALFLAG, LASTTRANSDATE, LASTACCDATE)
+ values ('2006', '水控存款', '2006', 0, 0, 2, null, null)",
+ "insert into T_INNERACC (ACCNO, ACCNAME, SUBJNO, YDAYBAL, BALANCE,
+ BALFLAG, LASTTRANSDATE, LASTACCDATE)
+ values ('220201', '批量销户应付款', '220201', 0, 0, 2, null, null)",
+ "insert into T_INNERACC (ACCNO, ACCNAME, SUBJNO, YDAYBAL, BALANCE,
+ BALFLAG, LASTTRANSDATE, LASTACCDATE)
+ values ('6201', '营业外支出', '6201', 0, 0, 1, null, null)",
+ "insert into T_INNERACC (ACCNO, ACCNAME, SUBJNO, YDAYBAL, BALANCE,
+ BALFLAG, LASTTRANSDATE, LASTACCDATE)
+ values ('6202', '商户提成支出', '6202', 0, 0, 1, null, null)"
+ ]
+ },
+ {
+ "name": "T_SUBSYSTEM",
+ "action": "truncate",
+ "desc": "子系统表",
+ "src_sql": "SELECT SYSTEM_ID,SYSTEM_NAME,SYSTEM_TYPE,AREA,SERVER_ID,MAC_ADDR,IP,
+ PORT, '' CARDVERNO,INIT_KEY,DYNA_KEY,SYNCH_TIME,
+ (case when STATE=1 then 1
+ when state=2 then 1
+ when state=3 then 2
+ when state=4 then 3
+ else 4 end) runstatus ,
+ (case when STATE=6 then 2 else 1 end) status ,
+ '' OPENDATE,'' CLOSEDATE,'' REMARK FROM
+ YKT_CUR.T_PIF_SUBSYSTEM",
+ "dest_column": "SYSID, SYSNAME, SYSTYPE, AREACODE, SERVERID, MACADDR, IP, PORT,
+ CARDVERNO, INITKEY, DYNAKEY, SYNCTIME, RUNSTATUS, STATUS,
+ OPENDATE, CLOSEDATE, REMARK"
+ },
+ {
+ "name": "T_SHOPACC",
+ "action": "truncate",
+ "desc": "商户账户表",
+ "src_sql": "select (case when a.account_id is null then ''
+ else '2'||substr(a.account_id,8) end) accno,
+ s.shop_id shopid,a.cut_name,'2004',a.password,a.current_state,
+ 0 ydaybal,a.cur_bala,a.cur_freebala,a.cur_frozebala,'' lasttransdate,
+ '' lastaccdate, a.open_date,a.close_date from
+ ykt_cur.t_aif_account a left join ykt_cur.t_cif_shop s on
+ a.customer_id=s.cut_id where a.act_type=2 and s.shop_id is not null
+ order by s.shop_id",
+ "dest_column": "ACCNO, SHOPID, ACCNAME, SUBJNO, ACCPWD, STATUS, YDAYBAL, BALANCE,
+ AVAILBAL, FROZEBAL, LASTTRANSDATE, LASTACCDATE, OPENDATE, CLOSEDATE",
+ "post_exec": ["update t_syskey set keyval=(select max(to_number(substr(accno,2)))
+ from t_shopacc) where keycode='SHOPACCNO'"]
+ },
+ {
+ "name": "T_SPECIALTY",
+ "action": "truncate",
+ "desc": "专业信息表",
+ "src_sql": "SELECT S_CODE,S_NAME,1 USEFLAG,'' LASTSAVED FROM
+ YKT_CUR.T_CIF_SPECIALITY WHERE S_CODE <>'' and s_code<> '0'",
+ "dest_column": "SPECIALTYCODE, SPECIALTYNAME, USEFLAG, LASTSAVED"
+ },
+ {
+ "name": "T_SHOPPOS",
+ "action": "truncate",
+ "desc": "商户设备对应表",
+ "src_sql": "select t1.id,t1.device_id,t2.devphy999_id,t1.shop_id,t1.end_time,
+ t1.comments,t1.begin_date,t1.end_date,1,'','' from
+ ykt_cur.t_cif_shop_pos t1,ykt_cur.t_pif_device t2
+ where t1.device_id=t2.device_id",
+ "dest_column": "ID, DEVICEID, DEVPHYID, SHOPID, ENDTIME, REMARK, STARTDATE,
+ ENDDATE, STATUS, OPERCODE, LASTSAVED"
+ },
+ {
+ "name": "T_RPTACCBAL",
+ "action": "truncate",
+ "desc": "账户结算表",
+ "src_sql": "select :tx_date,'2001',(case when a.account_id is null then ''
+ else '1'||substr(a.account_id,8) end) accno,
+ 2 ,cur_bala from ykt_cur.t_aif_account a where act_type=1
+ union all
+ select :tx_date,'2004',(case when a.account_id is null then ''
+ else '2'||substr(a.account_id,8) end) accno,
+ 2,cur_bala from ykt_cur.t_aif_account a where act_type=2
+ union all
+ select :tx_date, (case when a.account_id='1011' then '1001'
+ when a.account_id='1021' then '1002'
+ when a.account_id='1031' then '112101'
+ when a.account_id='1041' then '112102'
+ when a.account_id='1051' then '112103'
+ when a.account_id='2051' then '2002'
+ when a.account_id='2061' then '220299'
+ when a.account_id='2071' then '2203'
+ when a.account_id='5011' then '6002'
+ when a.account_id='5021' then '6005'
+ when a.account_id='5051' then '6003'
+ when a.account_id='5041' then '6006'
+ when a.account_id='5071' then '6001'
+ else rtrim(a.account_id) end) subno,
+ (case when a.account_id='1011' then '1001'
+ when a.account_id='1021' then '1002'
+ when a.account_id='1031' then '112101'
+ when a.account_id='1041' then '112102'
+ when a.account_id='1051' then '112103'
+ when a.account_id='2051' then '2002'
+ when a.account_id='2061' then '220299'
+ when a.account_id='2071' then '2203'
+ when a.account_id='5011' then '6002'
+ when a.account_id='5021' then '6005'
+ when a.account_id='5051' then '6003'
+ when a.account_id='5041' then '6006'
+ when a.account_id='5071' then '6001'
+ else rtrim(a.account_id) end) account_id,
+ (case when substr(a.account_id,1,1)='1' then 1 else 2 end) flag,
+ cur_bala from ykt_cur.t_aif_account a where act_type=7 and
+ a.account_id not in ('1122','2031','2041','1071')",
+ "dest_column": "ACCDATE, SUBJNO, ACCNO, BALFLAG, BALANCE",
+ "remark": "导入内部账户报表，并更新错误的借贷标志",
+ "post_exec": ["update t_rptaccbal set balance=abs(balance),balflag=1
+ where accno in ('6201','6202')",
+ // "insert into t_rptaccbal(accdate,subjno,accno,balflag,balance)
+ // select :tx_date,s.subjno,s.subjno,
+ // (case when substr(s.subjno,1,1)='1' then 1
+ // else 2 end) flag1,0
+ // from t_subject s left join t_rptaccbal b on
+ // (s.subjno=b.subjno and b.accdate=:tx_date)
+ // where b.accdate is null",
+ "insert into t_rptaccbal(accdate,subjno,accno,balflag,balance)
+ select :tx_date,s.subjno,s.subjno,2,0 from t_rptsubjbal s
+ where s.subjno in (2001,2004,2202,2006,220201,6004)
+ and s.accdate=:tx_date",
+ "insert into t_rptaccbal(accdate,subjno,accno,balflag,balance)
+ select :tx_date,s.subjno,s.subjno,1,0 from t_rptsubjbal s
+ where s.subjno in (6201,6202)
+ and s.accdate=:tx_date",
+ "update t_rptaccbal set balance=balance+60 where
+ accno='1' || substr('1000000000111416', 8) and accdate=:tx_date"]
+ },
+ {
+ "name": "T_RPTSUBJBAL",
+ "action": "truncate",
+ "desc": "科目结算表",
+ "src_sql": "select :tx_date,'2001',2 ,sum(cur_bala),0,0,2,sum(cur_bala)
+ from ykt_cur.t_aif_account a where act_type=1
+ union all
+ select :tx_date,'2004',2,sum(cur_bala),0,0,2,sum(cur_bala)
+ from ykt_cur.t_aif_account a where act_type=2
+ union all select :tx_date,
+ (case when a.account_id='1011' then '1001'
+ when a.account_id='1021' then '1002'
+ when a.account_id='1031' then '112101'
+ when a.account_id='1041' then '112102'
+ when a.account_id='1051' then '112103'
+ when a.account_id='2051' then '2002'
+ when a.account_id='2061' then '220299'
+ when a.account_id='2071' then '2203'
+ when a.account_id='5011' then '6002'
+ when a.account_id='5021' then '6005'
+ when a.account_id='5051' then '6003'
+ when a.account_id='5041' then '6006'
+ when a.account_id='5071' then '6001'
+ when a.account_id='2081' then '2202'
+ else rtrim(a.account_id) end) subno,
+ (case when substr(a.account_id,1,1)='1' then 1
+ else 2 end) flag1,
+ sum(cur_bala),0,0,
+ (case when substr(a.account_id,1,1)='1' then 1
+ else 2 end) flag2,sum(cur_bala) from
+ ykt_cur.t_aif_account a where act_type=7 and
+ a.account_id not in ('1122','2031','2041','1071')
+ group by a.account_id",
+ "dest_column": "ACCDATE, SUBJNO, BEGINBALFLAG, BEGINBAL, DRAMT, CRAMT,
+ ENDBALFLAG, ENDBAL",
+ "post_exec" : ["insert into t_rptsubjbal(ACCDATE, SUBJNO, BEGINBALFLAG, BEGINBAL,
+ DRAMT, CRAMT, ENDBALFLAG, ENDBAL)
+ select :tx_date, subjno, 1,
+ (select sum(beginbal) from t_rptsubjbal t where accdate=:tx_date
+ and t.subjno like s.subjno || '__'),
+ 0,0,1,
+ (select sum(endbal) from t_rptsubjbal t where accdate=:tx_date
+ and t.subjno like s.subjno || '__') from t_subject s
+ where subjno in ('1121','2202')",
+ "update t_rptsubjbal set beginbalflag=1,endbalflag=1 where
+ subjno in ('6201','6202')",
+ "update t_rptsubjbal set beginbal=beginbal+60, endbal=endbal+60
+ where accdate=:tx_date and subjno='2001'",
+ "delete from t_servicelog"],
+ "remark": "生成结算科目数据"
+ },
+ {
+ "name": "T_OPERATOR",
+ "action": "truncate",
+ "desc": "操作员表",
+ "src_sql": "select rownum,oper_code,3,100,oper_name, phone, mobile,
+ email, addr, pwd, 1 from ykt_cur.t_pif_operator where
+ oper_code not in ('system')",
+ "dest_column": "OPERID, OPERCODE, OPERTYPE, BRANCHNO, OPERNAME, TEL, MOBILE,
+ EMAIL, ADDR, OPERPWD, STATUS",
+ "post_exec": ["update t_syskey set keyval=(select max(operid) from t_operator)
+ where keycode='OPERID'"]
+ },
+ {
+ "name": "T_SUBSIDY",
+ "action": "truncate",
+ "desc": "补助数据表",
+ "src_sql": "select t.BATCH_NO,SEQNO,c.STUEMP_NO,CUST_NO,SUBSIDY_NO,CARD_NO,SUMMARY,
+ BROKER_NAME,BROKER_ID,BILL_TYPE,BILL_NO,AMOUNT,SUBSIDYTYPE,1,'','',
+ TX_DATE,TX_TIME,GET_DATE,GET_TIME,STATUS,0,'',OPER_CODE FROM
+ YKT_CUR.t_tif_subsidy t,ykt_cur.t_cif_customer c where t.cust_no=c.cut_id",
+ "dest_column": "BATCHNO,SEQNO,STUEMPNO,CUSTID,SUBSIDYNO,CARDNO,SUMMARY,BROKERNAME,
+ BROKERIDNO,PAYTYPE,VOUCHERNO,AMOUNT,SUBSIDYTYPE,SUBSIDYMODE,IMPDATE,
+ IMPTIME,PUTDATE,PUTTIME,GETDATE,GETTIME,STATUS,CHECKFLAG,
+ ERRMSG,OPERCODE"
+ },
+ {
+ "name": "T_PHOTOLIMIT",
+ "action": "truncate",
+ "desc": "拍照系统用户表",
+ "src_sql": "select oper_code,oper_name, password, oper_limit, enabled,
+ substr(begin_date,1, 8), substr(end_date,1,6), memo from t_cif_photolimit",
+ "dest_column": "OPERCODE, OPERNAME, OPERPWD , OPERLIMIT , ENABLED , BEGINDATE ,
+ ENDDATE , REMARK "
+ },
+ {
+ "name": "T_TIF_REPORT_ACTIVE",
+ "action": "truncate",
+ "desc": "历史结算总表",
+ "src_sql": "select BALANCE_DATE, SERI_TYPE, TRADE_NUMBER, TRADE_AMT, ACC_ADDNUM,
+ ACC_AMT, SHOP_ADDNUM, SHOP_AMT, DEP_AMT, EARN_AMT, MNG_AMT,
+ COST_AMT, ENSURE_AMT, BANK_AMT, CASH_AMT, OPERATOR_CODE, DATA_SIGN,
+ RESERVE1, RESERVE2, CHECK_ADD_AMT, OUTLAY_ADD_AMT, DOWN_SUBSIDY_AMT,
+ UP_SUBSIDY_AMT from ykt_cur.T_TIF_REPORT_ACTIVE",
+ "dest_column": "BALANCE_DATE, SERI_TYPE, TRADE_NUMBER, TRADE_AMT, ACC_ADDNUM,
+ ACC_AMT, SHOP_ADDNUM, SHOP_AMT, DEP_AMT, EARN_AMT, MNG_AMT,
+ COST_AMT, ENSURE_AMT, BANK_AMT, CASH_AMT, OPERATOR_CODE, DATA_SIGN,
+ RESERVE1, RESERVE2, CHECK_ADD_AMT, OUTLAY_ADD_AMT, DOWN_SUBSIDY_AMT,
+ UP_SUBSIDY_AMT"
+ },
+ {
+ "name": "T_TIF_REPORT_DEPTTRADE",
+ "action": "truncate",
+ "desc": "T_TIF_REPORT_DEPTTRADE",
+ "src_sql": "select BALANCE_DATE, DEPT_CODE, DEPT_NAME, SERI_TYPE, FEE_TYPE,
+ OUTORIN, OPCOUNT, OPFEE, RESERVE_1, RESERVE_2 from
+ ykt_cur.T_TIF_REPORT_DEPTTRADE",
+ "dest_column": "BALANCE_DATE, DEPT_CODE, DEPT_NAME, SERI_TYPE, FEE_TYPE, OUTORIN,
+ OPCOUNT, OPFEE, RESERVE_1, RESERVE_2"
+ },
+ {
+ "name": "T_TIF_REPORT_ERRACC",
+ "action": "truncate",
+ "desc": "T_TIF_REPORT_ERRACC",
+ "src_sql": "select BALANCE_DATE, ACCOUNT_ID, CUT_NAME, STUEMP_NO, TYPE_ID,
+ DEPT_NAME, LAST_BALA, CUR_BALA, TRADE_AMT, DIFF_BALA, DATA_SIGN,
+ RESERVE1, RESERVE2 from ykt_cur.T_TIF_REPORT_ERRACC",
+ "dest_column": "BALANCE_DATE, ACCOUNT_ID, CUT_NAME, STUEMP_NO, TYPE_ID,
+ DEPT_NAME, LAST_BALA, CUR_BALA, TRADE_AMT, DIFF_BALA, DATA_SIGN,
+ RESERVE1, RESERVE2"
+ },
+ {
+ "name": "T_TIF_REPORT_OPER",
+ "action": "truncate",
+ "desc": "T_TIF_REPORT_OPER",
+ "src_sql": "select BALANCE_DATE,(case when OPERATOR_CODE='' then '999999'
+ else OPERATOR_CODE end),
+ SERI_TYPE, MAINDEVICE_ID, DEVICE_ID, HAPPEN_NUM, HAPPEN_AMT,
+ MNG_AMT, ENSURE_AMT, COST_AMT, DEP_AMT, EARN_AMT, BANK_AMT,
+ CASH_AMT, DATA_SIGN, RESERVE1, RESERVE2, ACC_ADD_AMT, SHP_ADD_AMT,
+ CHECK_AMT, OUTLAY_AMT, DOWN_SUBSIDY_AMT, UP_SUBSIDY_AMT from
+ ykt_cur.T_TIF_REPORT_OPER",
+ "dest_column": "BALANCE_DATE, OPERATOR_CODE, SERI_TYPE, MAINDEVICE_ID,
+ DEVICE_ID, HAPPEN_NUM, HAPPEN_AMT, MNG_AMT, ENSURE_AMT, COST_AMT,
+ DEP_AMT, EARN_AMT, BANK_AMT, CASH_AMT, DATA_SIGN, RESERVE1,
+ RESERVE2, ACC_ADD_AMT, SHP_ADD_AMT, CHECK_AMT, OUTLAY_AMT,
+ DOWN_SUBSIDY_AMT, UP_SUBSIDY_AMT"
+ },
+ {
+ "name": "T_TIF_REPORT_SHOP_BALANCE",
+ "action": "truncate",
+ "desc": "T_TIF_REPORT_SHOP_BALANCE",
+ "src_sql": "select BALANCE_DATE, SHOP_ID, SHOP_NAME, DEPT_ID, TRADE_NUM,
+ TRADE_AMT, MNG_AMT, TOTAL_AMT, DATA_SIGN, RESERVE1, RESERVE2 from
+ ykt_cur.T_TIF_REPORT_SHOP_BALANCE",
+ "dest_column": "BALANCE_DATE, SHOP_ID, SHOP_NAME, DEPT_ID, TRADE_NUM, TRADE_AMT,
+ MNG_AMT, TOTAL_AMT, DATA_SIGN, RESERVE1, RESERVE2"
+ },
+ {
+ "name": "T_TIF_REPORT_SHOP_BALANCE_01",
+ "action": "truncate",
+ "desc": "T_TIF_REPORT_SHOP_BALANCE_01",
+ "src_sql": "select BALANCE_DATE, SHOP_ID, SHOP_NAME, DEPT_ID, OPERATE_DATE, TRADE_NUM,
+ TRADE_AMT, MNG_AMT, TOTAL_AMT, DATA_SIGN, RESERVE1, RESERVE2,OUTORIN,
+ SERI_TYPE from
+ ykt_cur.T_TIF_REPORT_SHOP_BALANCE_01",
+ "dest_column": "BALANCE_DATE, SHOP_ID, SHOP_NAME, DEPT_ID, OPERATE_DATE, TRADE_NUM,
+ TRADE_AMT, MNG_AMT, TOTAL_AMT, DATA_SIGN, RESERVE1, RESERVE2,OUTORIN,
+ SERI_TYPE"
+ },
+ {
+ "name": "T_TIF_REPORT_OPER_STATISTICS",
+ "action": "truncate",
+ "desc": "T_TIF_REPORT_OPER_STATISTICS",
+ "src_sql": "select BALANCE_DATE, OPER_CODE, SERIAL_TYPE, SUBNO, OTHER_SUBNO,
+ DEBIT_AMT,DEBIT_COUNT,CREDIT_AMT,CREDIT_COUNT,SURPLUS from
+ ykt_cur.T_TIF_REPORT_OPER_STATISTICS",
+ "dest_column": "BALANCE_DATE, OPER_CODE, SERIAL_TYPE, SUBNO, OTHER_SUBNO,
+ DEBIT_AMT,DEBIT_COUNT,CREDIT_AMT,CREDIT_COUNT,SURPLUS"
+ }
\ No newline at end of file