elab_mvp/sql.py

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
    """


if __name__ == '__main__':
    print(len('222'))