#电商财务对账
##一、元数据导出
###1、pay_serivce.order_goods_info
```
select 
    id
    ,order_no   
    ,sku_no
    ,sku_name
    ,cast(prime_costs AS DECIMAL(10,3))/ 100 AS prime_costs
    ,cast(market_price AS DECIMAL(10,3)) / 100 AS market_price
    ,cast(sale_price AS DECIMAL(10,3)) / 100 AS sale_price
    ,cast(amt_freight AS DECIMAL(10,3)) / 100 AS amt_freight
    ,cast(amt_freight_deduct AS DECIMAL(10,3))  / 100 AS amt_freight_deduct
    ,cast(amt_cash_coupon_deduct AS DECIMAL(10,3))  / 100 AS amt_cash_coupon_deduct
    ,cast(amt_coupon_deduct AS DECIMAL(10,3))  / 100 AS amt_coupon_deduct
    ,cast(amt_deduct_pay_coupon AS DECIMAL(10,3)) / 100 AS amt_deduct_pay_coupon
    ,cast(amt_activity_deduct AS DECIMAL(10,3)) / 100 AS amt_activity_deduct
    ,cast(amt_need_pay AS DECIMAL(10,3)) / 100 AS amt_need_pay
    ,created_at 
    ,updated_at
from 
    order_goods_info 
where created_at >= '2021-09-01 00:00:00' AND created_at<'2021-10-01 00:00:00'
```
###2、kdsp.t_order_sku
```
select 
    *
from
    t_order_sku 
where
	date_format(created_at,'%Y-%m') = '2021-09' 
```

###3、kdsp.t_order_info
```
#用户pay_time作为支付指标
select 
	*
from 
	t_order_info 
where 
	date_format(created_at,'%Y-%m') = '2021-09' 
```
	
###4、kdsp.t_sku_info
```
select 
    *
from 
    t_sku_info 
where 
    date_format(created_at,'%Y-%m') = '2021-09' 
```

###5、kdsp.t_goods_rear_category
```
select 
    *
from 
    t_goods_rear_category 
where 
    date_format(created_at,'%Y-%m') = '2021-09' 
```
###6、kdsp.t_jd_order_info
```
select 
    *
from
    t_jd_order_info
where
	date_format(created_at,'%Y-%m') = '2021-08' 
```

###7、kdsp.t_after_service_apply
```
select 
    *
from 
    t_after_service_apply 
where 
    date_format(updated_at,'%Y-%m') = '2021-09' 
    
SELECT
aft.*,
sku.sku_no
FROM
	t_after_service_apply aft
LEFT JOIN t_order_sku sku ON sku.order_no = aft.order_no
AND aft.order_sku_id = sku.id
WHERE
	date_format(aft.updated_at, '%Y-%m') = '2021-09'
```

###8、ps_txn_summary
```
select 
    a.order_no 
    ,a.user_uuid
    ,a.flow_no
    ,a.order_goods_id as order_goods_id
    ,case
        when a.trade_type = 2 then 'R002'
        when a.trade_type = 3 then 'R003' 
        when a.trade_type = 1 then 'R004' 
    end as "txn_code"
    ,a.sku_no 
    ,a.flow_order_no
    ,a.consume_flow_no
    ,(CAST(a.trade_amount AS DECIMAL(10, 3)) / 100) as post_amt
    ,a.created_at
from
    goods_pay_detail a
left join 
    order_info b 
on 
    a.order_no = b.order_no 
where 
    1=1
and 
    b.status = 1  and date_format(b.paid_time,'%Y-%m') = '2021-09'
```
	
###9、ps_refund_summary
```
SELECT
	'202109' AS 'TERM_NO', 
	order_no,
	user_uuid,
	refund_flow_no,
	refund_flow_order_no,
	CASE
WHEN trade_type = 2 THEN
	'R094'
WHEN TRADE_TYPe = 3 THEN
	'R093'
WHEN trade_type = 1 THEN
	'R092'
ELSE
	'R091'
END trade_type,
 sku_no,
 cast(
	refund_amount AS DECIMAL (10, 3)
) / 100 AS refund_amount,
 created_at
FROM
	log_refund_request
WHERE
	`status` = 3
AND created_at >= '2021-09-01 00:00:00'
AND created_at < '2021-10-01';
```

###10、dw_pay_summary 抽中间表
```
SELECT
	psts.`TERM_NO`,
	psts.`ORDER_NO`,
	psts.`USER_UUID`,
	psts.`FLOW_NO`,
	psts.`ORDER_GOODS_ID`,
	psts.`SKU_NO`,
	psts.`FLOW_ORDER_NO`,
	psts.`CONSUME_FLOW_NO`,
	SUM(psts.VCC_AMT) AS 'VCC_AMT',
	SUM(psts.WX_AMT) AS 'WX_AMT',
	SUM(psts.ZFB_AMT) AS 'ZFB_AMT'
FROM
	(
		SELECT
			`TERM_NO`,
			`ORDER_NO`,
			`USER_UUID`,
			`FLOW_NO`,
			`ORDER_GOODS_ID`,
			`TXN_CODE`,
			`SKU_NO`,
			`FLOW_ORDER_NO`,
			`CONSUME_FLOW_NO`,
			CASE TXN_CODE
		WHEN 'R004' THEN
			POST_AMT
		ELSE
			0.0
		END AS 'VCC_AMT',
		CASE TXN_CODE
	WHEN 'R002' THEN
		POST_AMT
	ELSE
		0.0
	END AS 'WX_AMT',
	CASE TXN_CODE
WHEN 'R003' THEN
	POST_AMT
ELSE
	0.0
END AS 'ZFB_AMT',
 `POST_AMT`,
 `CREATED_AT`
FROM
	`ps_txn_summary`
WHERE
	TERM_NO = 202108
	) psts
GROUP BY
	psts.ORDER_NO,
	psts.SKU_NO,
	psts.ORDER_GOODS_ID
```

###11、dw_refund_summary 退款中间表
```
SELECT
	psrs.`TERM_NO`,
	psrs.`ORDER_NO`,
	psrs.`USER_UUID`,
	psrs.`SKU_NO`,
	psrs.REFUND_FLOW_NO AS 'REFUND_FLOW_NO',
	psrs.REFUND_FLOW_ORDER_NO AS 'REFUND_FLOW_ORDER_NO',
	SUM(psrs.VCC_AMT) AS 'VCC_AMT',
	SUM(psrs.WX_AMT) AS 'WX_AMT',
	SUM(psrs.ZFB_AMT) AS 'ZFB_AMT',
	SUM(psrs.WX_AMT)+SUM(psrs.ZFB_AMT) AS 'CASH_AMT',
	psrs.CREATED_AT
FROM
	(
		SELECT
			`TERM_NO`,
			`ORDER_NO`,
			`USER_UUID`,
			`TXN_CODE`,
			REFUND_FLOW_NO,
			REFUND_FLOW_ORDER_NO,
			`SKU_NO`,
			CASE TXN_CODE
		WHEN 'R092' THEN
			REFUND_AMT
		ELSE
			0.0
		END AS 'VCC_AMT',
		CASE TXN_CODE
	WHEN 'R093' THEN
		REFUND_AMT
	ELSE
		0.0
	END AS 'ZFB_AMT',
	CASE TXN_CODE
WHEN 'R094' THEN
	REFUND_AMT
ELSE
	0.0
END AS 'WX_AMT',
 `REFUND_AMT`,
 `CREATED_AT`
FROM
	`ps_refund_summary`
WHERE
	TERM_NO = 202108
	) psrs
GROUP BY
	psrs.ORDER_NO,
	psrs.SKU_NO
```

##二、支付对账
准备根订单号补全的存储过程
```
CREATE DEFINER=`root`@`localhost` FUNCTION `findParentOrderNo`(`orderNo` varchar(64)) RETURNS varchar(64) CHARSET utf8
BEGIN

	DECLARE COUNT INT DEFAULT 0;
	DECLARE  PARENT VARCHAR(64) DEFAULT NULL;
	DECLARE  ROOT VARCHAR(64) DEFAULT NULL;
	SET PARENT=orderNo;

	WHILE LENGTH(PARENT)>0 DO
	SET ROOT=PARENT;
	SET PARENT=(SELECT PARENT_ORDER_NO FROM t_order_info WHERE ORDER_NO=PARENT LIMIT 1);
	END WHILE;

	RETURN ROOT;
END
```
执行根订单号补全
```
UPDATE t_order_info OINF
INNER JOIN (
	SELECT
		id,
		findParentOrderNo (ORDER_NO) AS ROOT_ORDER_NO,
		ORDER_NO
	FROM
		t_order_info
	WHERE
		TERM_NO=202108 AND ROOT_ORDER_NO='' LIMIT 10000
) ROOT_INFO ON OINF.ID = ROOT_INFO.id
SET OINF.ROOT_ORDER_NO = ROOT_INFO.ROOT_ORDER_NO
WHERE
	OINF.ID = ROOT_INFO.id; 
```

###1、order_inf_sku 订单关联SKU信息表
```
SELECT
	oinf.TERM_NO,
	oinf.ID,
	oinf.USER_ID,
	oinf.ROOT_ORDER_NO,
	oinf.ORDER_NO,
	osku.sku_source AS ORDER_TYPE,
	oinf.ORDER_STATUS,
	oinf.ORDER_TIME,
	oinf.PAY_TIME,
	oinf.DELIVERY_AT,
	oinf.PAY_TYPE,
	oinf.ORDER_COMPLETE_TIME,
	osku.SKU_NO,
	osku.SKU_ID,
	osku.ID AS 'ORDER_SKU_ID',
	osku.SHOP_NAME,
	osku.SKU_NAME,
	osku.PRICE,
	osku.COST_PRICE,
	osku.AMOUNT,
	osku.PAY_AMOUNT,
	osku.COUNT,
	osku.FREIGHT_DEDUCTION_FEE,
	osku.COUPON_DEDUCTION_FEE,
	osku.CASH_COUPON_DEDUCTION_FEE,
	osku.FREIGHT_FEE,
	osku.FR_ACTIVITY_DEDUCTION_FEE,
	osku.AFTER_SERVICE_FLAG,
	osku.SHOP_ID
FROM
	t_order_info oinf
	LEFT JOIN t_order_sku osku ON oinf.ID = osku.ORDER_INFO_ID
WHERE
	oinf.TERM_NO = 202109
	AND oinf.ORDER_STATUS <> 51
	ORDER BY
	oinf.ROOT_ORDER_NO DESC
```

###2 、ps_pay_inf_sku 拉取商品信息表
```
SELECT
	skui.ID AS 'ID',
	skui.BRAND_NAME AS 'BRAND_NAME',
	gcat1.CATEGROY_NAME AS 'CATEGROY_NAME1',
	gcat2.CATEGROY_NAME AS 'CATEGROY_NAME2',
	gcat3.CATEGROY_NAME AS 'CATEGROY_NAME3',
	psts.ORDER_GOODS_ID AS 'ORDER_GOODS_ID',
	isku.USER_ID AS 'USER_ID',
	isku.SKU_ID AS 'SKU_ID',
	psts.FLOW_NO AS 'FLOW_NO',
	psts.SKU_NO AS 'SKU_NO',
	psts.TERM_NO AS 'TERM_NO',
	psts.ORDER_NO AS 'ROOT_ORDER_NO',
	psts.VCC_AMT+psts.WX_AMT+psts.ZFB_AMT AS 'POST_AMT',
	psts.VCC_AMT AS 'VCC_AMT',
	psts.WX_AMT AS 'WX_AMT',
	psts.ZFB_AMT AS 'ZFB_AMT',
	isku.TERM_NO AS 'SKU_TERM_NO',
	isku.ID AS 'ORDER_INFO_ID',
	isku.ORDER_NO AS 'SUB_ORDER_NO',
	isku.SHOP_NAME AS 'SHOP_NAME',
	isku.SHOP_ID AS 'SHOP_ID',
	isku.SKU_NAME AS 'SKU_NAME',
	isku.ORDER_TYPE AS 'ORDER_TYPE',
	isku.order_status AS 'ORDER_STATUS',
	isku.PAY_TYPE AS 'PAY_TYPE',
	isku.AMOUNT AS 'AMOUNT',
	isku.COUNT AS 'COUNT',
	isku.PAY_AMOUNT AS 'PAY_AMOUNT',
	ogd_inf.amt_freight AS 'AMT_FREIGHT',
	ogd_inf.amt_freight_deduct AS 'AMT_FREIGHT_DEDUCT',
	ogd_inf.amt_cash_coupon_deduct AS 'AMT_CASH_COUPON_DEDUCT',
	ogd_inf.amt_coupon_deduct AS 'AMT_COUPON_DEDUCT',
	ogd_inf.amt_deduct_pay_coupon AS 'AMT_DEDUCT_PAY_COUPON',
	ogd_inf.amt_activity_deduct AS 'AMT_ACTIVITY_DEDUCT',
	ogd_inf.AMT_NEED_PAY AS 'AMT_NEED_PAY',
	isku.PRICE AS 'PRICE',
	isku.COST_PRICE AS 'COST_PRICE',
	isku.ORDER_TIME AS 'ORDER_TIME',
	isku.PAY_TIME AS 'PAY_TIME',
	isku.DELIVERY_AT AS 'DELIVERY_AT',
	isku.ORDER_COMPLETE_TIME AS 'ORDER_COMPLETE_TIME',
	isku.after_service_flag AS 'AFTER_SERVICE_FLAG'
	FROM
		dw_pay_summary psts
	LEFT JOIN order_goods_info ogd_inf ON ogd_inf.ID = psts.ORDER_GOODS_ID
	LEFT JOIN order_inf_sku isku ON psts.ORDER_NO = isku.ROOT_ORDER_NO
	AND psts.SKU_NO = isku.SKU_NO
	LEFT JOIN t_sku_info skui ON psts.SKU_NO = skui.SKU_NO
	LEFT JOIN t_goods_rear_category gcat1 ON skui.C_ID1 = gcat1.CATEGROY_ID
	LEFT JOIN t_goods_rear_category gcat2 ON skui.C_ID2 = gcat2.CATEGROY_ID
	LEFT JOIN t_goods_rear_category gcat3 ON skui.C_ID3 = gcat3.CATEGROY_ID
	WHERE
	psts.TERM_NO >= 202109
-- AND psts.ORDER_NO IN (
-- '1421501573946892288'
-- -- '1432671460492763136'
-- )
```

###3、ps_pay_sku_bill 合并SKU粒度表
```
SELECT
	pspis.TERM_NO AS 'TERM_NO',
	pspis.SKU_TERM_NO AS 'SKU_TERM_NO',
	pspis.ORDER_GOODS_ID AS 'ORDER_GOODS_ID',
	pspis.ORDER_INFO_ID AS 'ORDER_INFO_ID',
	pspis.USER_ID AS 'USER_ID',
	pspis.FLOW_NO AS 'FLOW_NO',
	pspis.ROOT_ORDER_NO AS 'ROOT_ORDER_NO',
	pspis.SUB_ORDER_NO AS 'SUB_ORDER_NO',
	pspis.SKU_NO AS 'SKU_NO',
	pspis.SKU_ID AS 'SKU_ID',
	pspis.CATEGROY_NAME1 AS 'CATEGROY_NAME1',
	pspis.CATEGROY_NAME2 AS 'CATEGROY_NAME2',
	pspis.CATEGROY_NAME3 AS 'CATEGROY_NAME3',
	pspis.BRAND_NAME AS 'BRAND_NAME',
	pspis.SHOP_NAME AS 'SHOP_NAME',
	pspis.SKU_NAME AS 'SKU_NAME',
	pspis.SHOP_ID AS 'SHOP_ID',
	pspis.ORDER_TYPE AS 'ORDER_TYPE',
	pspis.order_status AS 'order_status',
	pspis.PAY_TYPE AS 'PAY_TYPE',
-- MAX(pspis.amount) + SUM(pspis.amt_freight) - SUM(pspis.amt_freight_deduct) - SUM(pspis.POST_AMT) - SUM(pspis.amt_deduct_pay_coupon) - SUM(
-- 	pspis.amt_cash_coupon_deduct
-- ) - SUM(pspis.amt_coupon_deduct) - SUM(pspis.amt_activity_deduct) AS AA,
-- CASE
-- WHEN MAX(pspis.amount) + SUM(pspis.amt_freight) - SUM(pspis.amt_freight_deduct) - SUM(pspis.POST_AMT) - SUM(pspis.amt_deduct_pay_coupon) - SUM(
-- 	pspis.amt_cash_coupon_deduct
-- ) - SUM(pspis.amt_coupon_deduct) - SUM(pspis.amt_activity_deduct) = 0.000 THEN
-- 	'Y'
-- ELSE
-- 	'N'
-- END AS 'CHECKED_PAY',

	SUM(pspis.VCC_AMT) AS 'VCC_AMT',
	SUM(pspis.WX_AMT) AS 'WX_AMT',
	SUM(pspis.ZFB_AMT) AS 'ZFB_AMT',
	SUM(pspis.amt_deduct_pay_coupon) AS 'AMT_DEDUCT_PAY_COUPON',
	CAST((SUM(pspis.amt_need_pay)/COUNT(1)*MAX(pspis.COUNT)) AS DECIMAL(10, 3)) AS 'AMT_NEED_PAY',
	SUM(pspis.POST_AMT) AS 'POST_AMT',

	MAX(pspis.amount) AS 'AMOUNT',
	MAX(pspis.COUNT) AS 'COUNT',
	SUM(pspis.amt_freight)-SUM(pspis.AMT_FREIGHT_DEDUCT) AS 'POST_AMT_FREIGHT',
	MAX(pspis.pay_amount) AS 'PAY_AMOUNT',

	SUM(pspis.amt_freight) AS 'AMT_FREIGHT',
	SUM(pspis.AMT_FREIGHT_DEDUCT) AS 'AMT_FREIGHT_DEDUCT',
	SUM(pspis.amt_cash_coupon_deduct) AS 'AMT_CASH_COUPON_DEDUCT',
	SUM(pspis.amt_coupon_deduct) AS 'AMT_COUPON_DEDUCT',
	SUM(pspis.amt_activity_deduct) AS 'AMT_ACTIVITY_DEDUCT',

	pspis.PRICE AS 'PRICE',
	pspis.COST_PRICE AS 'COST_PRICE',
	pspis.ORDER_TIME AS 'ORDER_TIME',
	pspis.PAY_TIME AS 'PAY_TIME',
	pspis.DELIVERY_AT AS 'DELIVERY_AT',
	pspis.ORDER_COMPLETE_TIME AS 'ORDER_COMPLETE_TIME'
FROM
	`ps_pay_inf_sku` pspis
WHERE
	pspis.TERM_NO = 202108
-- AND pspis.ROOT_ORDER_NO IN(
-- '1421502775434653696',
-- '1421501573946892288',
-- '1421501612735807488'
-- )
GROUP BY
	pspis.ROOT_ORDER_NO,
	pspis.SUB_ORDER_NO,
	pspis.SKU_NO
```


###4、pay_sku_bill 出SKU粒度的核对明细
```
-- 合计应付金额 = 合计商品金额 + 合计运费金额
-- 合计支付金额 = 合计商品金额 + 合计运费金额 - 支付减免券金额 - 现金减免券金额 - 优惠券券减免金额 - 活动减免金额
-- 核应付实付 = (合计商品金额 + 运费金额 - 运费抵扣金额 - 实付金额 - 支付减免券金额 - 现金减免券金额 - 优惠券减免券金额 - 活动减免金额 = 0.0)
-- 核商品金额 = (合计商品金额 - 商品售价 * 合计商品数量 == 0.0)
-- 核实付金额 = (VCC支付金额 + 微信支付金额+ 支付宝支付金额 - 实付金额 == 0.0)
SELECT
	pspsb.TERM_NO AS '期次',
	pspsb.SKU_TERM_NO AS 'SKU期次',
	CONCAT('#', pspsb.`ROOT_ORDER_NO`) AS '根订单号',
	CONCAT('#', pspsb.`SUB_ORDER_NO`) AS '子订单号',
	CONCAT('#', pspsb.`SKU_NO`) AS 'SKU号',
	pspsb.`CATEGROY_NAME1` AS '一级品类',
	pspsb.`CATEGROY_NAME2` AS '二级品类',
	pspsb.`CATEGROY_NAME3` AS '三级品类',
	pspsb.`BRAND_NAME` AS '品牌',
	pspsb.`SHOP_NAME` AS '店铺',
	pspsb.`SKU_NAME` AS '商品名称',
	CASE pspsb.ORDER_TYPE
	WHEN 1 THEN
		'自营'
	WHEN 2 THEN
		'京东开普勒'
	WHEN 3 THEN
		'京东联盟'
	WHEN 4 THEN
		'众联'
	WHEN 5 THEN
		'众联企业购'
	WHEN 6 THEN
		'京东企业购'
	END AS '商品来源',

	CASE pspsb.`ORDER_STATUS`
	WHEN '11' THEN
		'待付款'
	WHEN '12' THEN
		'待发货'
	WHEN '13' THEN
		'待收货'
	WHEN '14' THEN
		'发货中'
	WHEN '21' THEN
		'订单完成'
	WHEN '31' THEN
		'订单已付款主动取消'
	WHEN '32' THEN
		'订单已付款自动取消'
	WHEN '33' THEN
		'订单拒收取消'
	WHEN '41' THEN
		'已付款主动取消退款完成订单关闭'
	WHEN '42' THEN
		'已付款自动取消退款完成订单关闭'
	WHEN '43' THEN
		'未支付用户主动取消订单关闭'
	WHEN '44' THEN
		'未支付超时取消订单关闭'
	WHEN '45' THEN
		'订单拒收退款订单取消'
	WHEN '46' THEN
		'订单已付款充值失败已退款订单关闭'
	WHEN '51' THEN
		'订单拆单关闭'
	ELSE
		'未知'
	END AS '订单状态',
	CASE pspsb.`PAY_TYPE`
	WHEN '1' THEN
		'微信支付'
	WHEN '2' THEN
		'额度支付'
	WHEN '3' THEN
		'支付宝支付'
	WHEN '4' THEN
		'微信+信用支付'
	WHEN '5' THEN
		'支付宝加信用支付'
	END AS '支付方式',
	pspsb.`VCC_AMT` AS 'VCC支付金额/元',
	pspsb.`WX_AMT` AS '微信支付金额/元',
	pspsb.`ZFB_AMT` AS '支付宝支付金额/元',
	pspsb.`AMOUNT` AS '合计商品金额/元',
	pspsb.`COUNT` AS '合计商品数量/件',
	pspsb.`POST_AMT_FREIGHT` AS '合计运费金额/元',
	pspsb.`PAY_AMOUNT` AS '合计应付总额/元',
	pspsb.`AMT_DEDUCT_PAY_COUPON` AS '支付减免券金额/元',
	pspsb.`AMT_CASH_COUPON_DEDUCT` AS '现金减免券金额/元',
	pspsb.`AMT_COUPON_DEDUCT` AS '优惠券减免券金额/元',
	pspsb.`AMT_ACTIVITY_DEDUCT` AS '活动减免金额/元',
	pspsb.`AMT_NEED_PAY` AS '合计支付金额/元',
	pspsb.`POST_AMT` AS '实付金额/元',
 CASE WHEN 
(pspsb.amount
+ pspsb.amt_freight
- pspsb.POST_AMT
- pspsb.AMT_FREIGHT_DEDUCT
- pspsb.amt_deduct_pay_coupon
- pspsb.amt_cash_coupon_deduct
- pspsb.amt_coupon_deduct
- pspsb.amt_activity_deduct)= 0.000 THEN
	'Y'
ELSE
	'N'
END AS '核应付实付',
 CASE
WHEN (pspsb.amount - pspsb.PRICE * pspsb.count) = 0.000 THEN
	'Y'
ELSE
	'N'
END AS '核商品金额',
 CASE
WHEN (pspsb.VCC_AMT + pspsb.WX_AMT + pspsb.ZFB_AMT - pspsb.POST_AMT) = 0.000 THEN
	'Y'
ELSE
	'N'
END AS '核实付金额',
	pspsb.`PRICE` AS '商品零售价/元',
	pspsb.`COST_PRICE` AS '商品成本/元',
	pspsb.`AMT_FREIGHT` AS '运费金额/元',
	pspsb.`AMT_FREIGHT_DEDUCT` AS '运费抵扣金额/元',
	pspsb.`ORDER_TIME` AS '下单时间',
	pspsb.`PAY_TIME` AS '支付时间',
	pspsb.`DELIVERY_AT` AS '发货时间',
	pspsb.`ORDER_COMPLETE_TIME` AS '订单完成时间',
	pspsb.`FLOW_NO` AS '流水号'
FROM
	ps_pay_sku_bill pspsb WHERE TERM_NO=202108 ORDER BY ROOT_ORDER_NO;
```

###5、pay_order_bill 出订单粒度的核对明细
```
SELECT
	pspsb.TERM_NO AS 'TERM_NO',
	pspsb.SKU_TERM_NO AS 'SKU_TERM_NO',
	pspsb.ROOT_ORDER_NO AS 'ROOT_ORDER_NO',
	GROUP_CONCAT(pspsb.SUB_ORDER_NO) AS 'SUB_ORDER_NO',
	GROUP_CONCAT(pspsb.SKU_NO) AS 'SKU_NO',
	SUM(pspsb.`VCC_AMT`) AS 'VCC_AMT',
	SUM(pspsb.`WX_AMT`) AS 'WX_AMT',
	SUM(pspsb.`ZFB_AMT`) AS 'ZFB_AMT'
FROM
	ps_pay_sku_bill pspsb
WHERE
	pspsb.TERM_NO = 202108
GROUP BY
	pspsb.ROOT_ORDER_NO;
```

###6、出银有企无订单明细
```
SELECT
	its.BASE_CODE AS '支付渠道',
	CONCAT('#',its.SERIAL_NO) AS '三方流水号',
	CONCAT('#', its.BASE_ORDER_NO) AS 'PS丢失根单号',
	its.POST_AMT AS '支付订单金额',
	CASE
WHEN its.POST_AMT <= 0.03 THEN
	'Y'
ELSE
	'N'
END AS '测试单'
FROM
	institution_transaction_summary its
LEFT JOIN pay_order_bill obill ON obill.ROOT_ORDER_NO = its.BASE_ORDER_NO
WHERE
	its.TERM_NO = 202109
AND its.TXN_FLAG = 'C'
AND obill.ROOT_ORDER_NO IS NULL;
```

##三、退款对账
###1、ps_rfd_inf_sku 拉取商品信息表
```
SELECT
	psrs.TERM_NO AS 'TERM_NO',
	isku.TERM_NO AS 'SKU_TERM_NO',
	CASE
WHEN isku.ID IS NULL THEN
	'N'
ELSE
	'Y'
END AS Nil,
	isku.USER_ID,
 psrs.ORDER_NO AS ROOT_ORDER_NO,
 isku.ORDER_NO AS 'SUB_ORDER_NO',
 isku.SKU_NO,
 isku.ID,
 isku.ORDER_NO,
 gcat1.CATEGROY_NAME AS CATEGROY_NAME1,
 gcat2.CATEGROY_NAME AS CATEGROY_NAME2,
 gcat3.CATEGROY_NAME AS CATEGROY_NAME3,
 CASE isku.ORDER_TYPE
WHEN 1 THEN
	'自营'
WHEN 2 THEN
	'京东开普勒'
WHEN 3 THEN
	'京东联盟'
WHEN 4 THEN
	'众联'
WHEN 5 THEN
	'众联企业购'
WHEN 6 THEN
	'京东企业购'
END AS 'ORDER_TYPE',
 CASE isku.order_status
WHEN '11' THEN
	'待付款'
WHEN '12' THEN
	'待发货'
WHEN '13' THEN
	'待收货'
WHEN '14' THEN
	'发货中'
WHEN '21' THEN
	'订单完成'
WHEN '31' THEN
	'订单已付款主动取消'
WHEN '32' THEN
	'订单已付款自动取消'
WHEN '33' THEN
	'订单拒收取消'
WHEN '41' THEN
	'已付款主动取消退款完成订单关闭'
WHEN '42' THEN
	'已付款自动取消退款完成订单关闭'
WHEN '43' THEN
	'未支付用户主动取消订单关闭'
WHEN '44' THEN
	'未支付超时取消订单关闭'
WHEN '45' THEN
	'订单拒收退款订单取消'
WHEN '46' THEN
	'订单已付款充值失败已退款订单关闭'
WHEN '51' THEN
	'订单拆单关闭'
ELSE
	'未知'
END AS 'ORDER_STATUS',
 isku.shop_name,
 isku.sku_name,
 isku.ORDER_SKU_ID,
 isku.price,
 isku.cost_price,
 isku.amount,
 isku.pay_amount,
 CASE isku.PAY_TYPE
WHEN '1' THEN
	'微信支付'
WHEN '2' THEN
	'额度支付'
WHEN '3' THEN
	'支付宝支付'
WHEN '4' THEN
	'微信+信用支付'
WHEN '5' THEN
	'支付宝+信用支付'
END AS 'PAY_TYPE',
psrs.VCC_AMT,
psrs.WX_AMT,
psrs.ZFB_AMT,
psrs.CASH_AMT,
psrs.VCC_AMT+psrs.WX_AMT+psrs.ZFB_AMT AS 'REFUND_AMT',
 IFNULL(isku.count, 0) AS PAY_COUNT,
 IFNULL(ogd_inf.AMT_NEED_PAY, 0.0) AS 'AMT_NEED_PAY',
'' AS 'SALES_SERVICE',
 isku.order_time,
 isku.pay_time
FROM
	dw_refund_summary psrs
LEFT JOIN order_inf_sku isku ON psrs.ORDER_NO = isku.ROOT_ORDER_NO
AND psrs.SKU_NO = isku.SKU_NO
LEFT JOIN order_goods_info ogd_inf ON ogd_inf.order_no = isku.ROOT_ORDER_NO
AND ogd_inf.sku_no = isku.SKU_NO
LEFT JOIN t_sku_info skui ON psrs.SKU_NO = skui.SKU_NO
LEFT JOIN t_goods_rear_category gcat1 ON skui.C_ID1 = gcat1.CATEGROY_ID
LEFT JOIN t_goods_rear_category gcat2 ON skui.C_ID1 = gcat2.CATEGROY_ID
LEFT JOIN t_goods_rear_category gcat3 ON skui.C_ID2 = gcat3.CATEGROY_ID
WHERE
	psrs.term_no = 202109
```


###2、ps_rfd_sku_bill 出SKU粒度的中间表
```
SELECT
	pris.TERM_NO AS 'TERM_NO',
	pris.SKU_TERM_NO AS 'SKU_TERM_NO',
	pris.`Nil` AS 'Nil',
	-- 	pris.`ORDER_GOODS_ID`,
pris.USER_ID,
	pris.`ROOT_ORDER_NO` AS 'ROOT_ORDER_NO',
	pris.`SUB_ORDER_NO` AS 'SUB_ORDER_NO',
	pris.`SKU_NO` AS 'SKU_NO',
	-- 	pris.`ORDER_SKU_ID`,
	-- 	pris.`ID`,
	pris.`CATEGROY_NAME1` AS 'CATEGROY_NAME1',
	pris.`CATEGROY_NAME2` AS 'CATEGROY_NAME2',
	pris.`CATEGROY_NAME3` AS 'CATEGROY_NAME3',
	pris.`ORDER_TYPE` AS 'ORDER_TYPE',
	pris.`ORDER_STATUS` AS 'ORDER_STATUS',
	pris.`shop_name` AS 'shop_name',
	pris.`sku_name` AS 'sku_name',
	pris.`price` AS 'price',
	pris.`cost_price` AS 'cost_price',
	pris.`amount` AS 'amount',
	pris.`pay_amount` AS 'pay_amount',
	pris.`PAY_TYPE` AS 'PAY_TYPE',
	pris.`VCC_AMT` AS 'VCC_AMT',
	pris.`WX_AMT` AS 'WX_AMT',
	pris.`ZFB_AMT` AS 'ZFB_AMT',
	pris.`CASH_AMT` AS 'CASH_AMT',
	pris.`PAY_COUNT` AS 'PAY_COUNT',
	SUM(pris.`AMT_NEED_PAY`) AS 'AMT_NEED_PAY',
	pris.`REFUND_AMT` AS 'REFUND_AMT',
	CASE
WHEN SUM(pris.`AMT_NEED_PAY`) - pris.`REFUND_AMT` = 0.000 THEN
	'Y'
ELSE
	'N'
END 'CHECKED_AMOUNT',
 pris.`SALES_SERVICE` AS 'SALES_SERVICE',
 pris.`order_time` AS 'order_time',
 pris.`pay_time` AS 'pay_time'
FROM
	`ps_rfd_inf_sku` pris
WHERE
pris.TERM_NO=202108
GROUP BY
	pris.ROOT_ORDER_NO,
	pris.SUB_ORDER_NO,
	pris.SKU_NO
```


###3、出退款SKU粒度的核对明细
```
SELECT
	rbill.TERM_NO AS '期次',
	rbill.SKU_TERM_NO AS '支付期次',
	rbill.Nil,
	CONCAT('#',rbill.`ROOT_ORDER_NO`) AS '根订单号',
	CONCAT('#',rbill.`SUB_ORDER_NO`) AS '子订单号',
	CONCAT('#',rbill.`SKU_NO`) AS 'SKU号',
	rbill.USER_ID AS '用户ID',
	rbill.`CATEGROY_NAME1` AS '一级品类',
	rbill.`CATEGROY_NAME2` AS '二级品类',
	rbill.`CATEGROY_NAME3` AS '三级品类',
	rbill.`ORDER_TYPE` AS '商品来源',
	rbill.`ORDER_STATUS` AS '订单状态',
	rbill.`shop_name` AS '店铺',
	rbill.`sku_name` AS '商品名称',
	rbill.`price` AS '商品零售价/元',
	rbill.`cost_price` AS '商品成本/元',
	rbill.`amount` AS '合计商品金额/元',
	rbill.`pay_amount` AS '合计应付总额/元',
	rbill.`PAY_TYPE` AS '支付方式',
	rbill.`VCC_AMT` AS 'VCC支付金额/元',
	rbill.`WX_AMT` AS '微信支付金额/元',
	rbill.`ZFB_AMT` AS '支付宝支付金额/元',
	rbill.`PAY_COUNT` AS '购买数量',
	rbill.`AMT_NEED_PAY` AS '合计支付金额/元',
	rbill.`REFUND_AMT` AS '退款金额/元',
	rbill.CHECKED_AMOUNT AS '核退款金额',
	rbill.`SALES_SERVICE` AS '售后状态',
	rbill.`order_time` AS '下单时间',
	rbill.`pay_time` AS '支付时间'
-- 	,CONCAT('#',jd.jd_order_id) AS '京东订单'
FROM
	`ps_rfd_sku_bill` rbill
-- LEFT JOIN jd_order_info jd ON jd.order_no = rbill.SUB_ORDER_NO
WHERE
	rbill.TERM_NO = 202109
```

###3、出售后SKU粒度的核对明细
```
SELECT
	CONCAT('#',aft.order_no) AS '售后子单号',
	CONCAT('#',sku.`ROOT_ORDER_NO`) AS '根订单号',
	CONCAT('#',sku.`ORDER_NO`) AS '子订单号',
	CONCAT('#',sku.`SKU_NO`) AS 'SKU号',
	bill.`CATEGROY_NAME1` AS '一级品类',
	bill.`CATEGROY_NAME2` AS '二级品类',
	bill.`CATEGROY_NAME3` AS '三级品类',
	bill.`ORDER_TYPE` AS '商品来源',
	bill.`ORDER_STATUS` AS '订单状态',
	bill.`shop_name` AS '店铺',
	bill.`sku_name` AS '商品名称',
	bill.`price` AS '商品零售价/元',
	bill.`cost_price` AS '商品成本/元',
	bill.`amount` AS '合计商品金额/元',
	bill.`pay_amount` AS '合计应付总额/元',
	bill.`PAY_TYPE` AS '支付方式',
	bill.`VCC_AMT` AS 'VCC支付金额/元',
	bill.`WX_AMT` AS '微信支付金额/元',
	bill.`ZFB_AMT` AS '支付宝支付金额/元',
	bill.`PAY_COUNT` AS '购买数量',
	bill.`AMT_NEED_PAY` AS '合计支付金额/元',
	bill.`REFUND_AMT` AS '退款金额/元',
	aft.`refund_amount` AS '售后金额/元',
	bill.CHECKED_AMOUNT AS '核退款金额',
	bill.`SALES_SERVICE` AS '售后状态',
	bill.`order_time` AS '下单时间',
	bill.`pay_time` AS '支付时间'
FROM
	t_after_service_apply aft
LEFT JOIN order_inf_sku sku ON aft.order_no = sku.ORDER_NO
LEFT JOIN ps_rfd_sku_bill bill ON bill.ROOT_ORDER_NO = sku.ROOT_ORDER_NO
AND bill.SUB_ORDER_NO = sku.ORDER_NO
AND bill.SKU_NO = sku.SKU_NO
WHERE
	aft.term_no = 202109
	AND aft.deal_status = 70
```

###4、退款银有企无SKU粒度的核对明细
```
SELECT
	its.TERM_NO AS '期次',
	CONCAT('#',its.TRANSACTION_ID) AS '交易ID',
	CONCAT('#',its.BASE_ORDER_NO) AS '根订单号',
	CONCAT('#',its.SERIAL_NO) AS '流水号',
	its.POST_AMT AS '退款金额',
	its.BASE_CODE AS '支付方式',
	its.TXN_CODE AS '交易码',
	rfd.*
FROM
	institution_transaction_summary its
LEFT JOIN dw_refund_summary rfd ON rfd.ORDER_NO = its.BASE_ORDER_NO
WHERE
	its.TERM_NO = 202109
AND its.TXN_FLAG = 'D'
AND rfd.TERM_NO IS NULL;
```


##四、修复脚本
###1、关联不到商品信息订单补全
```
SELECT
	sku.TERM_NO AS '期次',
	CONCAT('#',sku.SKU_NO) AS 'SKU号',
	gcat1.CATEGROY_NAME AS '一级品类',
	gcat2.CATEGROY_NAME AS '二级品类',
	gcat3.CATEGROY_NAME AS '三级品类',
	sku.SHOP_NAME AS '店铺',
	sku.SKU_NAME AS '商品名称'
FROM
	t_sku_info sku
LEFT JOIN t_goods_rear_category gcat1 ON sku.C_ID1 = gcat1.CATEGROY_ID
LEFT JOIN t_goods_rear_category gcat2 ON sku.C_ID1 = gcat2.CATEGROY_ID
LEFT JOIN t_goods_rear_category gcat3 ON sku.C_ID2 = gcat3.CATEGROY_ID
WHERE
	sku.sku_no IN (
		184935512285184,
		10982709793793,
		10978230276097,
		219832692247040
	)
```

###2、状态51、无子单号订单信息查询
```
SELECT
	oinf.TERM_NO,
	CONCAT('#',oinf.ID),
	CONCAT('#',oinf.ROOT_ORDER_NO),
	CONCAT('#',oinf.ORDER_NO),
	CONCAT('#',osku.SKU_NO),
	CASE oinf.ORDER_TYPE
	WHEN 1 THEN
		'自营'
	WHEN 2 THEN
		'京东开普勒'
	WHEN 3 THEN
		'京东联盟'
	WHEN 4 THEN
		'众联'
	WHEN 5 THEN
		'众联企业购'
	WHEN 6 THEN
		'京东企业购'
	ELSE
	    oinf.TERM_NO
	END AS 'ORDER_TYPE',
	oinf.order_status,
	oinf.order_time,
	oinf.pay_time,
	oinf.delivery_at,
	oinf.PAY_TYPE,
	oinf.order_complete_time,
	osku.shop_name,
	osku.sku_name,
	osku.price,
	osku.cost_price,
	osku.amount,
	osku.pay_amount,
	osku.count
FROM
	t_order_info oinf
	LEFT JOIN order_sku osku ON oinf.id = osku.order_info_id
WHERE 
(
(oinf.ROOT_ORDER_NO='1429979400845373440' AND osku.SKU_NO='10976015683073')
)
```

##VCC 财务对账
###1、放款对齐流水
SELECT
	PS_AMT AS '支付前置金额',
	CONSUME_FLOW_NO AS 'VCC流水号',
    TXN_ID AS '交易ID',
	VCC_AMT AS '放款金额',
	LF_RT_FLAG AS '检测金额',
	PS_AMT-LF_RT_FLAG AS '检测余额',
	SHOP_TYPE AS '渠道类型',
	STATUS AS '交易状态',
	TXN_TIME AS '放款时间'
FROM
	(
		SELECT
			VTS.TXN_ID AS TXN_ID,
			sum(psts.post_amt) AS PS_AMT,
			psts.consume_flow_no AS CONSUME_FLOW_NO,
			VTS.POST_AMT AS VCC_AMT,
			IFNULL(VTS.POST_AMT, 0) AS LF_RT_FLAG,
			VTS.SHOP_TYPE AS SHOP_TYPE,
			VTS.STATUS AS STATUS,
			VTS.TXN_TIME AS TXN_TIME
		FROM
			ps_txn_summary psts
		LEFT JOIN vcc_txn_summary VTS ON VTS.FLOW_NO = psts.CONSUME_FLOW_NO
		WHERE
			psts.txn_code = 'R004'
		AND 
			psts.term_no = 202109
		GROUP BY
			psts.consume_flow_no
	) TEMP
WHERE 
	PS_AMT-LF_RT_FLAG  = 0
	
	
	
###2、退货对齐流水
SELECT
	PS_AMT,
	CONCAT('#',REFUND_FLOW_NO),
	VCC_AMT,
	LF_RT_FLAG,
	PS_AMT - LF_RT_FLAG AS BAL
FROM
	(
		SELECT
			SUM(PRS.REFUND_AMT) AS PS_AMT,
			PRS.REFUND_FLOW_NO AS REFUND_FLOW_NO,
			VRS.REFUND_AMT AS VCC_AMT,
			IFNULL(VRS.REFUND_AMT, 0) AS LF_RT_FLAG
		FROM
			PS_REFUND_SUMMARY PRS
		LEFT JOIN VCC_REFUND_SUMMARY VRS ON VRS.REFUND_FLOW_NO = PRS.REFUND_FLOW_NO
		WHERE
			PRS.TXN_CODE = 'R092'
		AND 
			PRS.term_no = 202109
		GROUP BY
			PRS.REFUND_FLOW_NO
	) TEMP
WHERE 
	PS_AMT-LF_RT_FLAG  = 0
	
	
###3、放款PS有VCC无
SELECT * FROM
(SELECT DISTINCT(consume_flow_no) as cl from ps_txn_summary where txn_code = 'R004' AND TERM_NO = '202108')PSTS LEFT JOIN
vcc_txn_summary VSTS ON PSTS.cl=VSTS.flow_no
WHERE VSTS.FLOW_NO IS NULL order by PSTS.cl 

###4、 放款VCC有PS无
SELECT VSTS.FLOW_NO FROM vcc_txn_summary VSTS LEFT JOIN (
SELECT DISTINCT
	PSTS.consume_flow_no AS ps_consume_flow_no
FROM
	ps_txn_summary PSTS
WHERE
	PSTS.txn_code = 'R004'
) PSTSDC
ON PSTSDC.ps_consume_flow_no=VSTS.FLOW_NO WHERE VSTS.TERM_NO = '202108' AND PSTSDC.ps_consume_flow_no IS NULL;


###5、退货PS有VCC无
SELECT
	CONCAT('#',psrs.refund_flow_no) AS refund_flow_no,
	vrs.*
FROM
	(
		SELECT DISTINCT
			(refund_flow_no) AS refund_flow_no
		FROM
			`ps_refund_summary`
		WHERE
			TXN_CODE = 'R092'
		AND term_no = 202109
	) psrs
LEFT JOIN vcc_refund_summary AS vrs ON psrs.refund_flow_no = vrs.refund_flow_no
WHERE
	vrs.refund_flow_no IS NULL


###6、退货VCC有PS无

SELECT
	CONCAT('#', psrs.refund_flow_no) AS refund_flow_no,
	vrs.*
FROM
	(
		SELECT DISTINCT
			(refund_flow_no) AS refund_flow_no
		FROM
			`ps_refund_summary`
		WHERE
			TXN_CODE = 'R092'
		AND TERM_NO = 202109
	) psrs
RIGHT JOIN vcc_refund_summary AS vrs ON psrs.refund_flow_no = vrs.refund_flow_no
WHERE
	psrs.refund_flow_no IS NULL
AND VRS.TERM_NO = 202109
	
#会员权益
```
SELECT
	CONCAT('#', pnt.user_id) AS '用户ID',
	CONCAT('#', pnt.`level`) AS '用户等级',
	CONCAT('#', bil.`ROOT_ORDER_NO`) AS '根订单号',
	CONCAT('#', bil.`SUB_ORDER_NO`) AS '子订单号',
	CONCAT('#', bil.`SKU_NO`) AS 'SKU号',
	CONCAT('#', bil.SKU_ID) AS 'SKU ID',
	bil.`SKU_NAME` AS '商品名称',
	CASE bil.ORDER_TYPE
WHEN 1 THEN
	'自营'
WHEN 2 THEN
	'京东开普勒'
WHEN 3 THEN
	'京东联盟'
WHEN 4 THEN
	'众联'
WHEN 5 THEN
	'众联企业购'
WHEN 6 THEN
	'京东企业购'
END AS '商品来源',
 CASE bil.`PAY_TYPE`
WHEN '1' THEN
	'微信支付'
WHEN '2' THEN
	'额度支付'
WHEN '3' THEN
	'支付宝支付'
WHEN '4' THEN
	'微信+信用支付'
WHEN '5' THEN
	'支付宝加信用支付'
END AS '支付方式',
 bil.`VCC_AMT` AS 'VCC支付金额/元',
 bil.`WX_AMT` AS '微信支付金额/元',
 bil.`ZFB_AMT` AS '支付宝支付金额/元',
 bil.`AMOUNT` AS '合计商品金额/元',
 bil.`COUNT` AS '合计商品数量/件',
 bil.`POST_AMT_FREIGHT` AS '合计运费金额/元',
 bil.`PAY_AMOUNT` AS '合计应付总额/元',
 bil.`AMT_DEDUCT_PAY_COUPON` AS '支付减免券金额/元',
 bil.`AMT_CASH_COUPON_DEDUCT` AS '现金减免券金额/元',
 bil.`AMT_COUPON_DEDUCT` AS '优惠券减免券金额/元',
 bil.`AMT_ACTIVITY_DEDUCT` AS '活动减免金额/元',
 bil.`AMT_NEED_PAY` AS '合计支付金额/元',
 bil.`POST_AMT` AS '实付金额/元',
 bil.`PRICE` AS '商品零售价/元',
 bil.`COST_PRICE` AS '商品成本/元',
 bil.`AMT_FREIGHT` AS '运费金额/元',
 bil.`AMT_FREIGHT_DEDUCT` AS '运费抵扣金额/元',
 bil.`ORDER_TIME` AS '下单时间',
 bil.`PAY_TIME` AS '支付时间',
 bil.`DELIVERY_AT` AS '发货时间',
 bil.`ORDER_COMPLETE_TIME` AS '订单完成时间',
 GROUP_CONCAT(aft.deal_status) AS '售后状态'
FROM
	t_user_benefits_point pnt
LEFT JOIN ps_pay_sku_bill bil ON pnt.user_id = bil.USER_ID
LEFT JOIN t_after_service_apply aft ON aft.order_no = bil.SUB_ORDER_NO
WHERE
	bil.ORDER_COMPLETE_TIME >= '2021-09-01 00:00:00'
AND bil.ORDER_COMPLETE_TIME < '2021-10-01 00:00:00'
GROUP BY 
	bil.`ROOT_ORDER_NO`,
	bil.`SUB_ORDER_NO`,
	bil.`SKU_NO`
```

## 202109-京东售后-电商V供应链
```
SELECT
CONCAT('#',aft.order_no) AS '子订单号',
aft.refund_amount AS '退款金额',
sku.count as '购买数量',
sku.cost_price AS '供货单价/元',
	CASE sku.sku_source
	WHEN 1 THEN
		'自营'
	WHEN 2 THEN
		'京东开普勒'
	WHEN 3 THEN
		'京东联盟'
	WHEN 4 THEN
		'众联'
	WHEN 5 THEN
		'众联企业购'
	WHEN 6 THEN
		'京东企业购'
	END AS '商品来源',
CONCAT('#',inf.jd_order_id) AS '京东订单ID'
FROM
	t_after_service_apply aft
LEFT JOIN t_jd_order_info inf ON aft.order_no = inf.order_no
LEFT JOIN t_order_sku sku ON aft.order_sku_id = sku.id
WHERE aft.deal_status=70 and
aft.order_no<>'' order by aft.created_at DESC;
```