class Sql:
# 获取项目的城市信息
sql_1 = """
select IFNULL(brand_id, -1), house_id, house_name, city from d_house a where brand_id = '13' and house_id != '13' and status = 1 order by house_id
"""
# 根据任务id获取推送客户信息
sql_2 = """
select a.task_key, b.customer_type, b.name, b.mail, b.house_or_region, a.customer_id, GROUP_CONCAT(c.house_or_brand_id) as ids
from report_task_info a left join report_push_customer_info b on b.id = a.customer_id
left join report_customer_authority_info c on b.id = c.customer_id
LEFT JOIN d_house d on d.house_id = c.house_or_brand_id or d.brand_id = c.house_or_brand_id
where a.task_key = %s and a.status = b.status = c.status = 1 and d.`status` = 1
group by a.task_key, b.customer_type, b.name, b.mail, b.house_or_region, a.customer_id
"""
# 1:总浏览量
#
# 集团部分+项目部分!!!
# ----项目PV--权限项目范围内，求和
# 参数：时间区间 和 项目列表
sql_3 = """
SELECT SUM(pv) AS pv1 FROM a_idfa_behavior_sum
WHERE report_d >= %s and report_d <= %s AND house_id IN %s
"""
# ----集团PV--权限项目范围内，求和
# 参数：数据区间和项目列表
sql_4 = """
SELECT SUM(pv) AS pav2 FROM a_behavior_brand_mini_day
WHERE report_d >= %s and report_d <= %s AND house_id IN %s
"""
# 2:总浏览人数
#
# 参数，数据区间， 项目列表
sql_5 = """
SELECT
count(
DISTINCT IFNULL(mobile, user_id)
) as people
FROM
(
SELECT
A.user_id,
B.mobile
FROM
a_idfa_behavior_sum A
LEFT JOIN d_user B ON A.user_id = B.user_id
WHERE
A.report_d >= %s and A.report_d <= %s
AND A.house_id IN %s
UNION
SELECT
A.brand_user_id AS user_id,
B.mobile
FROM
a_behavior_brand_mini_day A
LEFT JOIN a_brand_app_customer B ON A.brand_user_id = B.brand_customer_id
WHERE
A.report_d >= %s and A.report_d <= %s
AND A.house_id IN %s
) t1
"""
# 3：新增获客
sql_6 = """
SELECT
count(
DISTINCT IFNULL(mobile, user_id)
)
FROM
(
SELECT
user_id,
mobile,
created
FROM
d_user
WHERE
created >= %s
AND created <= %s
AND house_id IN %s
AND source ='3'
UNION
SELECT
brand_customer_id AS user_id,
mobile,
rlat_created
FROM
a_brand_app_customer_house_rlat
WHERE
rlat_created >= %s
AND rlat_created <= %s
AND rlat_house_id IN %s
AND cust_house_flag ='1'
) t1
"""
# 4:新增获电
sql_7 = """
SELECT
COUNT(DISTINCT mobile)
FROM
(
SELECT
user_id,
mobile,
wx_phone_time AS created
FROM
d_user
WHERE
wx_phone_time >= %s
AND wx_phone_time <= %s
AND house_id IN %s
and source = '3'
UNION
SELECT
brand_customer_id,
mobile,
houdian_time AS created
FROM
(
SELECT
*,
CASE
WHEN rlat_created > shouquan_time THEN
rlat_created
ELSE
shouquan_time
END AS houdian_time
FROM
a_brand_app_customer_house_rlat
WHERE
mobile IS NOT NULL
AND rlat_house_id IN %s
AND cust_house_flag = '1'
) t1
WHERE
houdian_time >= %s
AND houdian_time <= %s
) t1
"""
# hosue_id, type, count
sql_8 = """
select house_id, type, COUNT(DISTINCT customer_mobile) from f_dm_jianye_allagent_day where report_d >= %s and report_d <= %s group by house_id, type
"""
sql_9 = """
SELECT house_id, visit_new_uv_dtd FROM a_brand_customer_share_dtd where brand_id ='13' and report_d >= %s and report_d <= %s order by report_d
"""
sql_10 = """
select type, COUNT(DISTINCT customer_mobile) from f_dm_jianye_allagent_day where report_d >= %s and report_d <= %s group by type
"""
sql_11 = """"
select a.house_or_region, min(b.house_or_brand_id), sum(a.name) from report_push_customer_info a left join report_customer_authority_info b on a.id = b.customer_id group by house_or_region
"""
sql_12 = """
insert into report_push_log(name, mail, report_name, push_time, send_status, status, error_message) values(%s, %s, %s, now(), %s, 1, %s)
"""
sql_13 = """
SELECT
x.brand_id,
x.house_id,
x.house_name,
ifnull(x.pv, 0),
x.uv,
x.new_cust,
x.shouquan_cust
FROM
(
SELECT
c.pv,
c.uv,
a.brand_id,
a.house_id,
a.house_name,
a.brand_name,
ifnull(b.house_layout_num, 0) house_layout_num,
ifnull(d.launch_time, '--') launch_time,
c.new_cust,
c.shouquan_cust,
c.revisit_cust
FROM
(
SELECT
brand_id,
ifnull(house_id, '0') house_id,
sum(pv) pv,
count(DISTINCT brand_user_id) uv,
count(
DISTINCT CASE
WHEN is_new_user = 1 THEN
brand_user_id
END
) new_cust,
count(
DISTINCT CASE
WHEN is_shouquan_user = 1 THEN
brand_user_id
END
) shouquan_cust,
count(
DISTINCT CASE
WHEN is_new_user = 0 THEN
brand_user_id
END
) revisit_cust
FROM
a_behavior_brand_mini_day
WHERE
report_d >= %s
AND report_d <= %s
GROUP BY
brand_id,
ifnull(house_id, '0')
) c
LEFT JOIN (
SELECT
house_id,
count(1) house_layout_num
FROM
d_content_layout
WHERE
`status` = '1'
AND house_id <> 1
GROUP BY
house_id
UNION ALL
SELECT
bb.brand_id house_id,
count(1) house_layout_num
FROM
d_content_layout aa
JOIN d_house bb ON aa.house_id = bb.house_id
WHERE
aa.`status` = '1'
AND bb. STATUS = '1'
AND aa.house_id <> 1
GROUP BY
bb.brand_id
) b ON c.house_id = b.house_id
JOIN d_house a ON a.house_id = c.house_id
AND a.brand_id = c.brand_id
LEFT JOIN d_house_attr d ON c.house_id = d.house_id
AND c.brand_id = d.brand_id
) x
"""
sql_14 = """
select a.id , a.`name`, a.mail, a.customer_type, a.house_or_region, GROUP_CONCAT(b.house_or_brand_id)
from report_push_customer_info a
left join report_customer_authority_info b on a.id = b.customer_id
where a.id = %s and a.status = 1 and b.status = 1
group by a.id , a.`name`, a.mail, a.customer_type, a.house_or_region
"""
sql_15 = """
select * from d_jianye_house_city_region_rlat
"""
sql_16 = """
SELECT b.region, b.city, a.house_name, a.advi_name, a.mobile, a.org_name, IFNULL(a.fx_mobile,0), IFNULL(a.zc_mobile, 0), IFNULL(a.bb_mobile, 0) FROM f_dm_jianye_agent_house_day a left join d_jianye_house_city_region_rlat b on a.house_id = b.house_id where a.report_d = %s and a.house_id in %s order by a.org_name, CAST(a.fx_mobile AS SIGNED) desc
"""
sql_17 = """
SELECT b.region, b.city, a.house_name, a.advi_name, a.mobile, a.org_name, IFNULL(a.fx_mobile,0), IFNULL(a.zc_mobile, 0), IFNULL(a.bb_mobile, 0) FROM f_dm_jianye_agent_house_month a left join d_jianye_house_city_region_rlat b on a.house_id = b.house_id where a.report_d = %s and a.house_id in %s order by a.org_name, CAST(a.fx_mobile AS SIGNED) desc
"""
sql_18 = """
SELECT b.region, b.city, a.house_name, a.advi_name, a.mobile, a.org_name, IFNULL(a.fx_mobile,0), IFNULL(a.zc_mobile, 0), IFNULL(a.bb_mobile, 0) FROM f_dm_jianye_agent_house_all a left join d_jianye_house_city_region_rlat b on a.house_id = b.house_id where a.report_d = %s and a.house_id in %s order by a.org_name, CAST(a.fx_mobile AS SIGNED) desc
"""
# 项目
sql_19 = """
SELECT house_id
,COUNT(DISTINCT mobile) AS new_moblie_qutty
,sum(CASE WHEN adviser_id IS NULL THEN 1 END ) AS Dispatchable_mobile_qutty
,COUNT(
DISTINCT CASE WHEN adviser_id IS NOT NULL THEN mobile
END
) AS Dispatched_mobile_qutty
FROM gdm_m01_cus_mobile_total
WHERE register_time >= %s
AND register_time <= %s
and house_id in %s and brand_id = 13
group by house_id
"""
#集团
sql_20 = """
SELECT brand_id
,COUNT(DISTINCT mobile) AS new_moblie_qutty
,sum(CASE WHEN adviser_id IS NULL THEN 1 END ) AS Dispatchable_mobile_qutty
,COUNT(
DISTINCT CASE WHEN adviser_id IS NOT NULL THEN mobile
END
) AS Dispatched_mobile_qutty
FROM gdm_m01_cus_mobile_total
WHERE register_time >= %s
AND register_time <= %s
and brand_id = 13
group by brand_id
"""
if __name__ == '__main__':
print(len('222'))