<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.com.ctop.report.mapper.KuaishouAccountReportDailyDwMapper">

    <select id="getReportByParams" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        stat_date as statDate,
        account_id as accountId,
        account_name as authName,
        ${filedAll}
        FROM
        kuaishou_account_report_daily_dw
        WHERE
        stat_date &lt;= #{endDate}
        AND stat_date &gt;= #{startDate}
        AND account_id in
        <foreach item="item" index="index" collection="accountIds"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
        <if test="hasGroupBy == 1">
            group by ${groupBy}
            order by ${target} ${order}
        </if>
    </select>


    <select id="getTotalCharge" resultType="com.alibaba.fastjson.JSONObject">
        SELECT t1.todayCharge, t2.yesterdayCharge, t3.thisWeekCharge, t4.lastWeek1 + t5.lastWeek2 as 'lastWeekCharge'
        FROM (
                 SELECT IFNULL(sum(charge), 0.00) as 'todayCharge'
                 FROM kuaishou_account_report_hourly_dw
                 WHERE stat_date = #{today}
             ) t1
                 LEFT JOIN
             (
                 SELECT IFNULL(sum(charge), 0.00) as 'yesterdayCharge'
                 FROM kuaishou_account_report_hourly_dw
                 WHERE stat_date = #{yesterday}
                   AND `hour` &lt;= #{hour}
             ) t2
             ON 1 = 1
                 LEFT JOIN
             (
                 SELECT IFNULL(sum(charge), 0.00) as 'thisWeekCharge'
                 FROM kuaishou_account_report_hourly_dw
                 WHERE stat_date >= #{monday}
                   AND stat_date &lt;= #{today}
             ) t3
             ON 1 = 1
                 LEFT JOIN
             (
                 SELECT IF(#{lastStartDate} - #{lastEndDate} = 0, 0.00, sum(charge)) as 'lastWeek1'
                 FROM kuaishou_account_report_daily_dw
                 WHERE stat_date >= #{lastStartDate}
                   AND stat_date &lt;= DATE_FORMAT(DATE_SUB(#{lastEndDate}
                                                       , INTERVAL 1 DAY )
                     , '%Y%m%d')
             ) t4
             ON 1 = 1
                 LEFT JOIN
             (
                 SELECT IFNULL(sum(charge), 0.00) as 'lastWeek2'
                 FROM kuaishou_account_report_hourly_dw
                 WHERE stat_date = #{lastEndDate}
                   AND `hour` &lt;= #{hour}
             ) t5 ON 1 = 1
    </select>

    <select id="getYesterdayUploadNewMaterialCount" resultType="Long">
        SELECT IFNULL(sum(1), 0)
        FROM `app_kuaishou_material_info`
        WHERE media_time = DATE_FORMAT(DATE_SUB(#{lastEndDate}, INTERVAL 1 DAY ), '%Y%m%d')
    </select>

    <select id="getHourcharge" resultType="com.alibaba.fastjson.JSONObject">
        SELECT hour as 'time', IFNULL(SUM(charge), 0.00) as 'charge'
        FROM kuaishou_account_report_hourly_dw
        WHERE stat_date = #{startTime}
        <if test="projectId != null and projectId != ''">
            AND project_id = #{projectId}
        </if>
        GROUP BY hour
        ORDER BY hour
    </select>

    <select id="getDatacharge" resultType="com.alibaba.fastjson.JSONObject">
        SELECT DATE_FORMAT(stat_date, '%Y-%m-%d') as 'time', IFNULL(sum(charge), 0.00) as 'charge'
        FROM kuaishou_account_report_hourly_dw
        WHERE stat_date &lt;= #{endTime}
        AND stat_date >= #{startTime}
        <if test="projectId != null and projectId != ''">
            AND project_id = #{projectId}
        </if>
        GROUP BY stat_date
        ORDER BY stat_date
    </select>


    <select id="getProjectToDayReportTotal" resultType="Long">
        SELECT IFNULL(sum(1), 0)
        FROM (
        SELECT 1
        FROM kuaishou_account_report_hourly_dw
        WHERE stat_date = #{startTime}
        <if test="projectId != null and projectId != ''">
            AND project_id = #{projectId}
        </if>
        GROUP BY project_id
        ) t
    </select>

    <select id="getProjectToDayReport" resultType="com.alibaba.fastjson.JSONObject">
        SELECT project_name as 'projectName', t2.product_name as 'productName', company_name as 'companyName',
        t3.realname as 'saler', t4.realname as 'operater', sum(charge) as 'charge', SUM(aclick) as 'aclick', SUM(bclick)
        as 'bclick', IFNULL(t5.num, 0) as 'num', IFNULL(
        concat(round((sum(charge) - t6.charges) / t6.charges * 100, 2), '%'), '-') as 'rio'
        FROM kuaishou_account_report_hourly_dw t1
        LEFT JOIN `jeecg-boot`.ctop_product t2 ON t2.id = t1.product_id
        LEFT JOIN `jeecg-boot`.sys_user t3 ON t1.sale_id = t3.id
        LEFT JOIN `jeecg-boot`.sys_user t4 ON t1.user_id = t4.id
        LEFT JOIN (
        SELECT tt2.project_id, sum(1) as 'num'
        FROM app_kuaishou_material_info tt1
        LEFT JOIN kuaishou_project_material_relation tt2 ON tt1.material_id = tt2.signature
        WHERE media_time = DATE_FORMAT(DATE_SUB(#{startTime}, INTERVAL 1 DAY ), '%Y%m%d')
        <if test="projectId != null and projectId != ''">
            AND project_id = #{projectId}
        </if>
        <if test="projectId == null">
            GROUP BY project_id
        </if>
        ) t5 ON t1.project_id = t5.project_id
        LEFT JOIN (
        SELECT project_id, SUM(charge) as 'charges'
        FROM kuaishou_account_report_hourly_dw
        WHERE stat_date = DATE_FORMAT(DATE_SUB(#{startTime}, INTERVAL 1 DAY ), '%Y%m%d')
        AND hour &lt;= #{hour}
        <if test="projectId != null and projectId != ''">
            AND project_id = #{projectId}
        </if>
        <if test="projectId == null">
            GROUP BY project_id
        </if>
        ) t6 ON t1.project_id = t6.project_id
        WHERE stat_date = #{startTime}
        <if test="projectId != null and projectId != ''">
            AND t1.project_id = #{projectId}
        </if>
        <if test="projectId == null">
            GROUP BY t1.project_id
            ORDER BY sum(charge) desc
        </if>
    </select>


    <select id="getProjectMoreDaysReportTotal" resultType="Long">
        SELECT IFNULL(sum(1), 0)
        FROM (
        SELECT 1
        FROM kuaishou_account_report_daily_dw
        WHERE stat_date >= #{startTime}
        AND stat_date &lt;= #{endTime}
        <if test="projectId != null and projectId != ''">
            AND project_id = #{projectId}
        </if>
        GROUP BY project_id
        ) t
    </select>


    <select id="getProjectMoreDaysReport" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        t1.project_id as 'projectId',
        project_name as 'projectName',
        t2.product_name as 'productName',
        company_name as 'companyName',
        t3.realname as 'saler',
        t4.realname as 'operater',
        sum(charge) as 'charge',
        SUM(aclick) as 'aclick',
        SUM(bclick) as 'bclick',
        IFNULL(t5.num,0) as 'num',
        '-' as 'rio'
        FROM kuaishou_account_report_daily_dw t1
        LEFT JOIN `jeecg-boot`.ctop_product t2 ON t2.id = t1.product_id
        LEFT JOIN `jeecg-boot`.sys_user t3 ON t1.sale_id = t3.id
        LEFT JOIN `jeecg-boot`.sys_user t4 ON t1.user_id = t4.id
        LEFT JOIN (
        SELECT tt2.project_id,sum(1) as 'num' FROM app_kuaishou_material_info tt1
        LEFT JOIN kuaishou_project_material_relation tt2 ON tt1.material_id = tt2.signature
        WHERE media_time >= #{startTime}
        AND media_time &lt;= #{endTime}
        <if test="projectId != null and projectId != ''">
            AND project_id = #{projectId}
        </if>
        <if test="projectId == null">
            GROUP BY project_id
        </if>
        ) t5 ON t1.project_id = t5.project_id
        WHERE stat_date >= #{startTime}
        AND stat_date &lt;= #{endTime}
        <if test="projectId != null and projectId != ''">
            AND t1.project_id = #{projectId}
        </if>
        <if test="projectId == null">
            GROUP BY t1.project_id
            ORDER BY sum(charge) desc
        </if>
    </select>

</mapper>