WITH
base_data AS (
SELECT
mdsb.belong_year,
mdsb.plan_month,
mdsb.product_group_name,
mdsb.product_manag_team_name,
mdsb.product_manag_team_name_cn,
mdsb.product_line_name,
mdsb.product_line_cn_name,
mdsb.product_type_i_name,
MAX(mdsb.tag_id) AS tag_id,
mdsb.tag_name,
MAX(mdsb.unit_name) AS unit_name
FROM
edw_mktsales_cont_sop_model_delivery_support_base AS mdsb
GROUP BY
mdsb.belong_year,
mdsb.plan_month,
mdsb.product_group_name,
mdsb.product_manag_team_name,
mdsb.product_manag_team_name_cn,
mdsb.product_line_name,
mdsb.product_line_cn_name,
mdsb.product_type_i_name,
mdsb.tag_name
),
base_qty_data AS (
SELECT
mdsb.belong_year,
mdsb.plan_month,
mdsb.product_group_name,
mdsb.product_manag_team_name,
mdsb.product_manag_team_name_cn,
mdsb.product_line_name,
mdsb.product_line_cn_name,
mdsb.product_type_i_name,
mdsb.tag_name,
mdsb.data_type,
ROUND(SUM(mdsb.planning_qty), 0) AS planning_qty_sum
FROM
edw_mktsales_cont_sop_model_delivery_support_base AS mdsb
GROUP BY
mdsb.belong_year,
mdsb.plan_month,
mdsb.product_group_name,
mdsb.product_manag_team_name,
mdsb.product_manag_team_name_cn,
mdsb.product_line_name,
mdsb.product_line_cn_name,
mdsb.product_type_i_name,
mdsb.tag_name,
mdsb.data_type
),
overall_unit_head AS (
SELECT
bouh.product_tag_id,
MAX(bouh.device_code) AS device_code
FROM
ods_scp_ba_overall_unit_head bouh
WHERE
bouh.enabled = 1
GROUP BY
bouh.product_tag_id
),
dcs AS (
SELECT
ucs.tag_name,
ucs.planned_month,
ucs.product_class,
MAX(ucs.system_default_qty) AS system_default_qty,
MAX(ucs.manual_default_qty) AS manual_default_qty
FROM
dm_get_unit_cost_seq ucs
GROUP BY
ucs.tag_name,
ucs.planned_month,
ucs.product_class
),
dpad AS (
SELECT
bd.belong_year,
bd.plan_month,
bd.product_group_name,
bd.product_manag_team_name,
bd.product_manag_team_name_cn,
bd.product_line_name,
bd.product_line_cn_name,
bd.product_type_i_name,
opm.product_model_class,
opm.product_model_name AS device_type,
bd.tag_name,
bd.tag_id,
bd.unit_name,
dcs1.system_default_qty,
dcs2.manual_default_qty,
dcs3.manual_default_qty AS month_default_qty,
'规划' AS data_type_1,
bqd1.planning_qty_sum AS data_type_qty_1,
CASE
WHEN dcs3.manual_default_qty IS NOT NULL THEN bqd1.planning_qty_sum * dcs3.manual_default_qty
WHEN dcs2.manual_default_qty IS NOT NULL THEN bqd1.planning_qty_sum * dcs2.manual_default_qty
WHEN dcs1.system_default_qty IS NOT NULL THEN bqd1.planning_qty_sum * dcs1.system_default_qty
ELSE NULL
END AS qty_cost_1,
'发货' AS data_type_2,
bqd2.planning_qty_sum AS data_type_qty_2,
CASE
WHEN dcs3.manual_default_qty IS NOT NULL THEN bqd2.planning_qty_sum * dcs3.manual_default_qty
WHEN dcs2.manual_default_qty IS NOT NULL THEN bqd2.planning_qty_sum * dcs2.manual_default_qty
WHEN dcs1.system_default_qty IS NOT NULL THEN bqd2.planning_qty_sum * dcs1.system_default_qty
ELSE NULL
END AS qty_cost_2
FROM
base_data AS bd
LEFT OUTER JOIN base_qty_data AS bqd1 ON bqd1.tag_name = bd.tag_name
AND bqd1.product_type_i_name = bd.product_type_i_name
AND bqd1.plan_month = bd.plan_month
AND bqd1.data_type = 1
LEFT OUTER JOIN base_qty_data AS bqd2 ON bqd2.tag_name = bd.tag_name
AND bqd2.product_type_i_name = bd.product_type_i_name
AND bqd2.plan_month = bd.plan_month
AND bqd2.data_type = 3
LEFT OUTER JOIN overall_unit_head AS ouh ON ouh.product_tag_id = bd.tag_id
LEFT OUTER JOIN ods_scp_aps_product_model AS opm ON opm.product_model_no = ouh.device_code
LEFT OUTER JOIN dcs AS dcs1 ON dcs1.tag_name = bd.tag_name
AND dcs1.planned_month = '1900-01'
AND regexp_extract (dcs1.product_class, '\\((.*?)\\)', 1) = bd.product_type_i_name
LEFT OUTER JOIN dcs AS dcs2 ON dcs2.tag_name = bd.tag_name
AND dcs2.planned_month = '1900-02'
AND regexp_extract (dcs2.product_class, '\\((.*?)\\)', 1) = bd.product_type_i_name
LEFT OUTER JOIN dcs AS dcs3 ON dcs3.tag_name = bd.tag_name
AND dcs3.planned_month = date_format (to_date (bd.plan_month, 'yyyyMM'), 'yyyy-MM')
AND regexp_extract (dcs3.product_class, '\\((.*?)\\)', 1) = bd.product_type_i_name
),
ecs AS (
SELECT
efe.issue_date,
efe.prod_class_name,
SUM(efe.price) AS sum_cost
FROM
(
SELECT
eiif.entity_name,
DATE_FORMAT (
to_date (eiif.issue_date, 'yyyy-MM-dd'),
'yyyyMM'
) AS issue_date,
eiif.master_code AS prod_class_name,
eiif.price
FROM
edw_sup_inv_app_inventory_inout_fingoodsout_2 AS eiif
) AS efe
GROUP BY
efe.issue_date,
efe.prod_class_name
),
this_month_data AS (
SELECT
dpad1.belong_year,
to_timestamp (concat (dpad1.plan_month, '01'), 'yyyyMMdd') AS plan_month_first_day,
dpad1.product_group_name,
dpad1.product_manag_team_name,
dpad1.product_manag_team_name_cn,
dpad1.product_line_name,
dpad1.product_line_cn_name,
pcn.prod_class_check_name,
dpad1.product_type_i_name,
m5l.l1_name,
m5l.l1_unit,
m5l.l2_name,
m5l.l2_unit,
dpad1.product_model_class AS l3_name,
m5l.l3_unit,
dpad1.device_type AS l4_name,
m5l.l4_unit,
dpad1.tag_name AS l5_name,
m5l.l5_unit,
dpad1.system_default_qty,
dpad1.manual_default_qty,
dpad1.month_default_qty,
dpad1.data_type_1,
dpad1.data_type_qty_1,
dpad1.qty_cost_1 / dpad1.data_type_qty_1 AS qty_unit_cost_1,
dpad1.qty_cost_1 AS qty_total_cost_1,
SUM(dpad1.data_type_qty_1 * dpad1.qty_cost_1) OVER (
PARTITION BY dpad1.plan_month,
dpad1.product_type_i_name
) AS sum_cost_1,
dpad1.data_type_2,
mtep.sum_num AS data_type_qty_2,
mtep.estimate_unit_price AS qty_unit_cost_2,
mtep.estimate_sum_price AS qty_total_cost_2,
ecs1.sum_cost AS sum_cost_2,
atep.estimate_unit_price AS qty_unit_cost_3,
dpad1.data_type_qty_1 * atep.estimate_unit_price AS qty_total_cost_3,
SUM(dpad1.data_type_qty_1 * atep.estimate_unit_price) OVER (
PARTITION BY dpad1.plan_month,
dpad1.product_type_i_name
) AS sum_cost_3,
CASE
WHEN to_timestamp (concat (dpad1.plan_month, '01'), 'yyyyMMdd') < date_trunc ('month', date_sub (current_date(), 1)) THEN '发货'
ELSE '规划'
END AS showed_type,
CASE
WHEN to_timestamp (concat (dpad1.plan_month, '01'), 'yyyyMMdd') < date_trunc ('month', date_sub (current_date(), 1)) THEN mtep.sum_num
ELSE dpad1.data_type_qty_1
END AS showed_qty,
CASE
WHEN to_timestamp (concat (dpad1.plan_month, '01'), 'yyyyMMdd') < date_trunc ('month', date_sub (current_date(), 1)) THEN mtep.estimate_unit_price
WHEN dpad1.month_default_qty IS NOT NULL THEN dpad1.month_default_qty
WHEN dpad1.manual_default_qty IS NOT NULL THEN dpad1.manual_default_qty
ELSE atep.estimate_unit_price
END AS showed_unit_cost,
CASE
WHEN to_timestamp (concat (dpad1.plan_month, '01'), 'yyyyMMdd') < date_trunc ('month', date_sub (current_date(), 1)) THEN mtep.estimate_sum_price
WHEN dpad1.month_default_qty IS NOT NULL THEN dpad1.qty_cost_1
WHEN dpad1.manual_default_qty IS NOT NULL THEN dpad1.qty_cost_1
ELSE dpad1.data_type_qty_1 * atep.estimate_unit_price
END AS showed_total_cost
FROM
dpad AS dpad1
LEFT OUTER JOIN ecs AS ecs1 ON ecs1.issue_date = dpad1.plan_month
AND ecs1.prod_class_name = dpad1.product_type_i_name
LEFT OUTER JOIN dm_sop_month_tag_estimate_price AS mtep ON mtep.product_class_name = dpad1.product_type_i_name
AND mtep.dates = CONCAT (
SUBSTRING(dpad1.plan_month, 1, 4),
'-',
SUBSTRING(dpad1.plan_month, 5, 2)
)
AND mtep.tag_name = dpad1.tag_name
LEFT OUTER JOIN dm_sop_average_tag_estimate_price AS atep ON atep.product_class_name = dpad1.product_type_i_name
AND atep.tag_name = dpad1.tag_name
LEFT OUTER JOIN ods_srm_op_prodclass_dp AS pcn ON pcn.prod_class_name = dpad1.product_type_i_name
LEFT OUTER JOIN dm_product_model_5level AS m5l ON m5l.prod_class_check_name = pcn.prod_class_check_name
AND m5l.l3_name = dpad1.product_model_class
WHERE
dpad1.data_type_qty_1 IS NOT NULL
AND dpad1.data_type_qty_1 <> 0
OR dpad1.data_type_qty_2 IS NOT NULL
AND dpad1.data_type_qty_2 <> 0
)
INSERT OVERWRITE TABLE dm_sop_tag_moth_plan_and_del_daily
SELECT
now () AS edw_create_date,
now () AS edw_last_update,
'' AS edw_data_source,
'Y' AS valid_flag,
tmd.belong_year,
tmd.plan_month_first_day,
tmd.product_group_name,
tmd.product_manag_team_name,
tmd.product_manag_team_name_cn,
tmd.product_line_name,
tmd.product_line_cn_name,
tmd.prod_class_check_name,
tmd.product_type_i_name,
tmd.l1_name,
tmd.l1_unit,
tmd.l2_name,
tmd.l2_unit,
tmd.l3_name,
tmd.l3_unit,
tmd.l4_name,
tmd.l4_unit,
tmd.l5_name,
tmd.l5_unit,
tmd.system_default_qty,
tmd.manual_default_qty,
tmd.month_default_qty,
tmd.data_type_1,
tmd.data_type_qty_1,
tmd.qty_unit_cost_1,
tmd.qty_total_cost_1,
tmd.sum_cost_1,
tmd.data_type_2,
tmd.data_type_qty_2,
tmd.qty_unit_cost_2,
tmd.qty_total_cost_2,
tmd.sum_cost_2,
tmd.qty_unit_cost_3,
tmd.qty_total_cost_3,
tmd.sum_cost_3,
tmd.showed_type,
tmd.showed_qty,
tmd.showed_unit_cost,
tmd.showed_total_cost
FROM
this_month_data AS tmd
最新发布
解决Android模拟器DPAD问题
本文介绍了解决Android模拟器中DPAD未启用的问题。通过编辑模拟器配置文件config.ini中的hw.dPad属性值为yes，重启模拟器后即可启用DPAD功能。
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
