数据处理
## 新增民警账号
```
INSERT INTO `ds_base`.`user_police`
(`USER_ID`, `ACCOUNT`, `XM`, `XB`, `EMAIL`, `PHONE`, `CSRQ`, `DW_ID`, `AREA_CODE`, `MZ`, `ZJLX`, `ZJHM`, `CREATE_USER_ID`, `UPDATE_USER_ID`, `PXH`, `MOBILE_PHONE`, `FAX`, `OFFICE_PHONE`, `DUTY`, `DUTY_LEVEL`, `STATE`, `CREATE_TIME`, `UPDATE_TIME`, `INVALID`)
VALUES (CONCAT("10",MD5('wujianan')), 'wujianan', '吴嘉楠', '0', '1447308902@qq.COM', '0571-12345678', '1992-10-10', '3306', '3306000000', NULL, NULL, NULL, '00admin', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1', NOW(), NOW(), NULL);
INSERT INTO `ds_datasyn`.`user_police`
(`USER_ID`, `ACCOUNT`, `XM`, `XB`, `EMAIL`, `PHONE`, `CSRQ`, `DW_ID`, `AREA_CODE`, `MZ`, `ZJLX`, `ZJHM`, `CREATE_USER_ID`, `UPDATE_USER_ID`, `PXH`, `MOBILE_PHONE`, `FAX`, `OFFICE_PHONE`, `DUTY`, `DUTY_LEVEL`, `STATE`, `CREATE_TIME`, `UPDATE_TIME`, `INVALID`)
VALUES (CONCAT("10",MD5('wujianan')), 'wujianan', '吴嘉楠', '0', '1447308902@qq.COM', '0571-12345678', '1992-10-10', '3306', '3306000000', NULL, NULL, NULL, '00admin', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1', NOW(), NOW(), NULL);
# 登录认证表
INSERT INTO `ds_base`.`user_oauth`
(`XH`,`USER_ID`, `LOGIN_TYPE`, `ACCOUNT`, `PWD`, `THIRD_APP_ID`, `STATUS`, `SECURITY`, `UPDATE_TIME`, `CREATE_TIME`)
SELECT MD5(ACCOUNT),user_id,'account', ACCOUNT, md5('yht123456'),NULL, '1', '0', NOW(),NOW()
FROM user_police WHERE dw_id='3302' AND LENGTH(ACCOUNT) =11
SELECT MD5(ACCOUNT),user_id,'account', ACCOUNT, 'E10ADC3949BA59ABBE56E057F20F883E', NULL, '1', '0', NOW(),NOW()
FROM user_police WHERE ACCOUNT ='wujianan'
```
## 插入罪犯信息
```
INSERT INTO ds_base.user_prisoner
(user_id,zf_bh,jydm,xm,xb,zjhm,gydw,gyjq,qr,zr,sjf,zybz)
VALUES
(CONCAT('11',MD5(2021072701)),'2021072701',3306,'吴江楠',1,'51343620000727909X',3306,3306110110,'2020-01-01','2025-01-01',1,1)
INSERT INTO ds_datasyn.user_prisoner
(user_id,zf_bh,jydm,xm,xb,zjhm,gydw,gyjq,qr,zr,sjf,zybz)
VALUES
(CONCAT('11',MD5(2021072701)),'2021072701',3306,'吴江楠',1,'51343620000727909X',3306,3306110110,'2020-01-01','2025-01-01',1,1)
INSERT INTO `ds_base`.`user_oauth`
(`XH`,`USER_ID`, `LOGIN_TYPE`, `ACCOUNT`, `PWD`, `THIRD_APP_ID`, `STATUS`, `SECURITY`, `UPDATE_TIME`, `CREATE_TIME`)
SELECT MD5(zf_bh),user_id,'no', zf_bh , 'E10ADC3949BA59ABBE56E057F20F883E', NULL, '1', '0', NOW(),NOW()
FROM user_prisoner WHERE zf_bh='2021072701'
```
## 批量造罪犯数据
```
INSERT INTO `ds_base`.`user_prisoner` (`USER_ID`, `ZF_BH`, `JYDM`, `XM`, `ZSXM`, `XB`, `PHOTO`, `CSRQ`, `MZ`, `ZJLX`, `ZJHM`, `ZJYXQKS`, `ZJYXQZR`, `CARD_NO`, `BQZZMM`, `BQWHCD`, `BQHYZK`, `BQZY`, `BQZYFL`, `BQZJ`, `BQZC`, `BQSXZY`, `TC`, `ZJXY`, `CJGHDPTT`, `BQZW`, `GYDW`, `GYJQ`, `GJDQ`, `JGQH`, `CSQH`, `JTQH`, `JTMX`, `HJQH`, `HJMX`, `HKLX`, `SG`, `TX`, `TZ`, `LX`, `XX`, `ZC`, `KY`, `PFBJ`, `ZYZT`, `FIRST_ZM`, `SYZM`, `XQ`, `QR`, `ZR`, `BZNX`, `DAH`, `FDH`, `SDDJ`, `SSDJ`, `UPDATE_TIME`, `SJf`, `FGDJ`, `CYDJ`, `ZYBZ`, `CREATE_TIME`, `IS_FULFIL`, `IS_FULFIL_IMG`, `FULFIL`, `LBC`, `CYDJF`, `CYXFF`, `IS_MONEY_OPERATE`, `CY_UPDATE_TIME`)
SELECT CONCAT("11",MD5(`ZF_BH`))as user_id, REPLACE(`ZF_BH`,"33110","33090")AS `ZF_BH` , REPLACE(`JYDM`,"3302","3309") as `JYDM`, `XM`, `ZSXM`, `XB`, `PHOTO`, `CSRQ`, `MZ`, `ZJLX`, NULL, `ZJYXQKS`, `ZJYXQZR`, `CARD_NO`, `BQZZMM`, `BQWHCD`, `BQHYZK`, `BQZY`, `BQZYFL`, `BQZJ`, `BQZC`, `BQSXZY`, `TC`, `ZJXY`, `CJGHDPTT`, `BQZW`, REPLACE(GYDW,"3302","3309") AS GYDW, REPLACE(`GYJQ`,"3302038004","3309170130") AS GYJQ, `GJDQ`, `JGQH`, `CSQH`, `JTQH`, `JTMX`, `HJQH`, `HJMX`, `HKLX`, `SG`, `TX`, `TZ`, `LX`, `XX`, `ZC`, `KY`, `PFBJ`, `ZYZT`, `FIRST_ZM`, `SYZM`, `XQ`, `QR`, `ZR`, `BZNX`, `DAH`, `FDH`, `SDDJ`, `SSDJ`, NOW(), `SJf`, `FGDJ`, `CYDJ`, `ZYBZ`, `CREATE_TIME`, `IS_FULFIL`, `IS_FULFIL_IMG`, `FULFIL`, `LBC`, `CYDJF`, `CYXFF`, `IS_MONEY_OPERATE`, `CY_UPDATE_TIME`
FROM `ds_base`.`user_prisoner` WHERE GYJQ=3302038004
INSERT INTO `ds_datasyn`.`user_prisoner` (`USER_ID`, `ZF_BH`, `JYDM`, `XM`, `XMSZM`, `ZSXM`, `XB`, `CSRQ`, `MZ`, `ZJLX`, `ZJHM`, `ZJYXQKS`, `ZJYXQZR`, `GYDW`, `GYJQ`, `GJDQ`, `FIRST_ZM`, `SYZM`, `SJf`, `FGDJ`, `CYDJ`, `ZYBZ`, `UPDATE_TIME`, `CREATE_TIME`, `IS_FULFIL`, `IS_FULFIL_IMG`, `FULFIL`, `LBC`, `XQ`, `QR`, `ZR`, `IS_MONEY_OPERATE`)
SELECT user_id, `ZF_BH` , `JYDM`, `XM`, '',`ZSXM`, `XB`, `CSRQ`, `MZ`, `ZJLX`, `ZJHM`, `ZJYXQKS`, `ZJYXQZR`, GYDW, GYJQ, `GJDQ`, `FIRST_ZM`, `SYZM`, `SJf`, `FGDJ`, `CYDJ`, `ZYBZ`, NOW(), `CREATE_TIME`, `IS_FULFIL`, `IS_FULFIL_IMG`, `FULFIL`, `LBC`, `XQ`, `QR`, `ZR`, `IS_MONEY_OPERATE`
FROM `ds_base`.`user_prisoner` WHERE GYJQ=3309170130 -- AND ZF_BH LIKE '33090%'
AND ZF_BH NOT in(
SELECT ZF_BH FROM ds_datasyn.user_prisoner)
INSERT INTO `ds_base`.`user_oauth`
(`XH`,`USER_ID`, `LOGIN_TYPE`, `ACCOUNT`, `PWD`, `THIRD_APP_ID`, `STATUS`, `SECURITY`, `UPDATE_TIME`, `CREATE_TIME`)
SELECT MD5(zf_bh),user_id,'no', zf_bh , 'E10ADC3949BA59ABBE56E057F20F883E', NULL, '1', '0', NOW(),NOW()
FROM user_prisoner WHERE GYJQ=3309170130
AND ZF_BH NOT in(
SELECT account FROM ds_base.user_oauth)
```
## ds_datasyn库数据同步
```
# 亲属信息同步
UPDATE ds_datasyn.user_visitor t1,ds_base.user_visitor t2
SET t1.LB=t2.LB,t1.GX=t2.GX,t1.XM=t2.XM,t1.XB=t2.XB,t1.MZ=t2.MZ,
t1.ZJLX=t2.ZJLX,t1.ZJHM=t2.ZJHM
WHERE t1.USER_ID=t2.USER_ID
# 罪犯信息同步
UPDATE ds_datasyn.user_prisoner t1,ds_base.user_prisoner t2
SET t1.JYDM=t2.JYDM,t1.XM=t2.XM,t1.ZSXM=t2.ZSXM,
t1.XB=t2.XB,t1.CSRQ=t2.CSRQ,t1.MZ=t2.MZ,t1.ZJLX=t2.ZJLX,
t1.ZJHM=t2.ZJHM,t1.ZJYXQKS=t2.ZJYXQKS,t1.ZJYXQZR=t2.ZJYXQZR,
t1.GYDW=t2.GYDW,t1.GYJQ=t2.GYJQ,t1.GJDQ=t2.GJDQ,
t1.FIRST_ZM=t2.FIRST_ZM,t1.SYZM=t2.SYZM,
t1.SJf=t2.SJf,
t1.FGDJ=t2.FGDJ,
t1.CYDJ=t2.CYDJ,
t1.ZYBZ=t2.ZYBZ,
t1.IS_FULFIL=t2.IS_FULFIL,
t1.IS_FULFIL_IMG=t2.IS_FULFIL_IMG,
t1.FULFIL=t2.FULFIL,
t1.LBC=t2.LBC,
t2.IS_MONEY_OPERATE=t1.IS_MONEY_OPERATE
WHERE t1.USER_ID=t2.USER_ID
```
```
# 监狱授权应用--开发环境
INSERT INTO `ds_app`.`app_dw_config` (`CONFIG_ID`, `APP_ID`, `AUTH_CODE`, `AUTH_NAME`, `AUTH_TYPE`, `IS_SHOW`, `URL`, `CREATE_USER_ID`, `UPDATE_TIME`, `CREATE_TIME`)
SELECT MD5(CONCAT(APP_ID,t2.JYDM)) AS configID,APP_ID,t2.JYDM,t2.DWMC,0,1,url,'00admin', NOW(), NOW()
FROM app_info t1 join ds_base.sys_dw_info t2 WHERE APP_ID in(
'416d5abb578a479ba3add7995bba5a35', # 省局商超APP-罪犯
'0caa810fb7d34aefb95aadc982726acd', # 亲情购APP[罪犯]
'3b6c4cc65f464e348c8b282d58b113f9', # 省局商超WEB-罪犯
'd8aab7ba4bd64b8f9c0d71977c8fc434', # 亲情购WEB[罪犯]
'ed08137c0c2846b3b18b47c6823f7d95', # 罪犯账务【APP】
'4c1096d452dd4bceb536abb299542c48', # 罪犯账务【WEB】
'4aa3d16265f54f7f8183b68565a1c69f' # 亲属购物【APP】
# '8ab0ad71056f4cb1890fc79255fa5157' # 罪犯点购App
)
AND APP_ID NOT in(SELECT DISTINCT APP_ID FROM app_dw_config);
-- 设置子应用的跳转地址
UPDATE app_dw_config t1,app_info t2
SET t1.url=t2.url
WHERE t1.app_id=t2.app_id AND t1.url is NULL
AND t2.APP_TYPE=3
# 配置默认映射地址
INSERT INTO `ds_app`.`app_dw_config_url` (`XH`, `CONFIG_ID`, `DICT_ID`, `DICT_VALUE`, `UPDATE_TIME`, `CREATE_TIME`)
SELECT MD5(config_id),config_id,dict_id,dict_json,NOW(),NOW()
-- SELECT config_id,t1.app_id,auth_code,auth_name,dict_id,dict_name,dict_json
FROM app_dw_config t1 LEFT JOIN app_info_dict t2 ON t1.app_id=t2.app_id
WHERE dict_id is not null AND config_id not in(
SELECT config_id FROM app_dw_config_url
)
```