(SELECT u.id AS collector_id,
u.name AS collector_name,
t.name AS area_name
FROM ods_collection.fdm_col_admin_user AS u
LEFTJOIN
(SELECT admin_user_id,
min(area_id) AS area_id
FROM ods_collection.fdm_col_user_area_relation
WHERE dt = "${p_date}"GROUPBY admin_user_id) AS r1 ON u.id = r1.admin_user_id
LEFTJOIN ods_collection.fdm_col_area AS t ON t.id= r1.area_id
AND t.dt = "${p_date}"WHERE u.dt = "${p_date}"AND u.business_id = "1"AND u.id != "320")
SELECT u.id AS collector_id,
u.name AS collector_name,
t.name AS area_name
FROM
(SELECT id,
name
FROM ods_collection.fdm_col_admin_user
WHERE dt = "${p_date}"AND business_id = "1"AND id != "320" ) AS u
JOIN
(SELECT admin_user_id,
min(area_id) AS area_id
FROM ods_collection.fdm_col_user_area_relation
WHERE dt = "${p_date}"GROUPBY admin_user_id) AS r1 ON u.id = r1.admin_user_id
JOIN ods_collection.fdm_col_area AS t ON t.id= r1.area_id
AND t.dt = "${p_date}"
INSERT OVERWRITE TABLE ods_collection.dm_col_repay_detail_report partition (dt='${p_date}')
SELECT from_unixtime(cast(r.TIME_REPAID/1000AS bigint),'yyyy-MM-dd') AS p_date,
c.order_number AS order_number,
l.collector_id,
l.collector_name AS collector_name,
l.area_name AS collector_area,
r.PRINCIPAL AS principal,
r.OVERDUE_INTEREST AS OVERDUE_INTEREST,
r.PENALTY AS PENALTY,
c.PRODUCT_NAME AS PRODUCT_NAME,
r.REDUCE_AMOUNT AS REDUCE_AMOUNT,
r.OVERDUE_DAYS AS OVERDUE_DAYS,
CASEWHEN r.COLLECTOR_ID = c.COLLECTOR_ID
AND c.STATUS = 'F'THEN"是"ELSE"否"ENDAS status,
c.TERMS AS TERMS,
c.START_TERM AS START_TERM,
c.END_TERM AS END_TERM,
from_unixtime(cast(c.TIME_PAYOUT/1000AS BIGINT),'yyyy-MM-dd') AS TIME_PAYOUT,
nvl(get_json_object(d.data,'$.brokerFee'),0)AS brokerFee,
nvl(get_json_object(d.data,'$.serviceFee'),0) AS serviceFee,
nvl(get_json_object(d.data,'$.insuranceFee'),0) AS insuranceFee,
nvl(get_json_object(d.data,'$.guaranteeFee'),0) AS guaranteeFee,
nvl(get_json_object(d.data,'$.additionalServiceFee'),0) AS additionalServiceFee,
nvl(get_json_object(d.data,'$.overdueBrokerFee'),0) AS overdueBrokerFee,
nvl(get_json_object(d.data,'$.extensionFee'),0) AS extensionFee,
nvl(get_json_object(d.data,'$.suspendedAmount'),0) AS suspendedAmount
FROM ods_collection.sdm_col_repayment AS r
INNERJOIN ods_collection.fdm_col_case AS c ON c.id = r.case_id
AND c.dt = "${p_date}"AND c.business_id = "1"LEFTJOIN ods_collection.sdm_col_general_data AS d ON d.parent_id = r.id
AND d.business_id = "1"INNERJOIN
( SELECT u.id AS collector_id,
u.name AS collector_name,
t.name AS area_name
FROM
(SELECT id,
name
FROM ods_collection.fdm_col_admin_user
WHERE dt = "${p_date}"AND business_id = "1"AND id != "320" ) AS u
JOIN
(SELECT admin_user_id,
min(area_id) AS area_id
FROM ods_collection.fdm_col_user_area_relation
WHERE dt = "${p_date}"GROUPBY admin_user_id) AS r1 ON u.id = r1.admin_user_id
JOIN ods_collection.fdm_col_area AS t ON t.id= r1.area_id
AND t.dt = "${p_date}") AS l ON r.collector_id = l.collector_id
WHERE r.REPAYMENT_TYPE IN ('N',
'O');
(SELECT u.id AS collector_id, u.name AS collector_name, t.name AS area_name FROM ods_collection.fdm_col_admin_user AS u LEFT JOIN (SELECT admin_user_id, mi...