个人钱款（吴嘉楠&裘程磊）
## 罪犯钱款-裘程磊
--罪犯存款总额
SELECT SUM(BALANCE)FROM user_prisoner_bill WHERE JYDW =3312
--虚实卡数量
SELECT COUNT(BN_USER_ID) FROM user_prisoner_bk_no WHERE BN_TYPE = 1 AND BN_STATE =1 AND BN_USED = 1 AND BN_JYDW =3312
--卡总数
SELECT COUNT(BN_USER_ID) FROM user_prisoner_bk_no WHERE BN_STATE =1 AND BN_USED = 1 AND BN_JYDW =3312
## 罪犯收入支出占比-裘程磊
--分类收支统计
SELECT bb.jydw AS jydm,aa.parent_code AS areaCode,
(SELECT name FROM ds_datasyn.sys_area WHERE area_code = aa.parent_code AND LEFT(area_code,4) = 3312) AS areaName,
SUM(IFNULL(bb.AMOUNT,0)) AS amount,bb.TRADE_TYPE AS tradeType,bb.TRANS_TYPE AS transType,cc.TRADE_TYPE_NAME AS tradeName
FROM ds_datasyn.sys_area aa
LEFT JOIN ds_bill.user_prisoner_bill_flow_3312 bb ON aa.area_code = bb.AREA_CODE
AND bb.TRADE_TYPE NOT LIKE '3%'
AND bb.CDATE >= '2022-04-01' AND bb.CDATE <= '2022-04-31'
LEFT JOIN ds_bill.sys_trade_conf cc ON bb.TRADE_TYPE = cc.TRADE_TYPE
WHERE aa.type IN ("01", "02", "03", "04", "05", "10") AND LEFT(aa.area_code,4) = 3312 AND aa.parent_code != 'region'
GROUP BY aa.parent_code,bb.TRADE_TYPE,bb.TRANS_TYPE
## 罪犯收入支出趋势-裘程磊
-总收支统计
SELECT SUM(AMOUNT) FROM ds_bill.user_prisoner_bill_flow_3312 WHERE TRANS_TYPE = 2 AND CDATE >= '2022-04-01' AND CDATE <= '2022-04-30';
## 罪犯存款汇总-吴嘉楠，100%
源数据库表 ds_bill.user_prisoner_bill
省局
监狱
## 罪犯存款余额-吴嘉楠，100%
源数据库表 ds_bill.user_prisoner_bill
省局
监狱
## 家属汇款统计-吴嘉楠，100%
源数据库表 ds_bill.user_prisoner_bankdetail
省局
监狱
## 家属单笔汇款金额分布-吴嘉楠，100%
源数据库表
ds_bill.user_prisoner_bankdetail
省局
监狱
