<?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="org.jeecg.ctop.material.mapper.KuaishouPromoterMapper">

    <select id="getPromoterInfo" resultType="com.alibaba.fastjson.JSONObject">
        SELECT t1.hotSaleChannelInfo,
               t1.hotSaleBrandInfo,
               t2.videoSales as 'totalSale',
               t2.promotedProductsNum,
               t3.promoteLiveCount,
               t2.liveStreamGMV,
               t3.liveStreamVisitorCount,
               t3.avgVisitorOnlineCount,
               t2.liveStreamGPM,
               t3.interactionRate,
               ROUND(avgViewTime / 60000, 0) as 'avgViewTime',
               t3.maxSameTimeVisitorCount,
               t2.videoGPM,
               t2.avgVideoSales,
               t2.avgVideoViewers,
               t4.videoCount,
               t4.videoWatchCount,
               t4.totalSale as 'videoTotalSale'
        FROM kwai_promoter.kwai_promoter_info t1
                 LEFT JOIN kwai_promoter.kwai_promoter_base_info t2 ON t1.promoterId = t2.promoterId
                 LEFT JOIN kwai_promoter.kwai_promoter_live_base_info t3 ON t1.promoterId = t3.promoterId
            AND t3.timeRangeType = 2
                 LEFT JOIN kwai_promoter.kwai_promoter_video_info t4 ON t1.promoterId = t4.promoterId
            AND t4.timeRangeType = 2
        WHERE t1.promoterId = #{promoterId}
    </select>

    <select id="getFansCharacteristic" resultType="com.alibaba.fastjson.JSONObject">
        SELECT type,
               CONCAT(type_date, '居多') as 'name',
               CONCAT(ROUND(MAX(FeatureRate) / 100, 2), '%') as 'roi'
        FROM kwai_promoter.kwai_promoter_fans_base_info
        WHERE promoterId = #{promoterId}
          AND timeRangeType = 2
        GROUP BY type

    </select>

    <select id="getPromoterLiveCommerceInfo" resultType="com.alibaba.fastjson.JSONObject">
        SELECT `date`,
               commentsCount,
               likesCount,
               liveMinuteDuration,
               liveVisitorCount,
               maxVisitorCount,
               sharesCount
        from kwai_promoter.kwai_promoter_live_analyse_info
        WHERE promoterId = #{promoterId}
          AND `date` BETWEEN #{startInt}
            and #{endInt}
        GROUP BY `date`
        ORDER BY `date`
    </select>

    <select id="getPromoterShortVideosCommerceInfo" resultType="com.alibaba.fastjson.JSONObject">
        SELECT `date`,
               shareCount,
               videoWatchCount,
               likeCount,
               commentCount
        from kwai_promoter.kwai_promoter_video_report_analyse_info
        WHERE promoterId = #{promoterId}
          AND `date` BETWEEN #{startInt} and #{endInt}
        GROUP BY `date`
        ORDER BY `date`
    </select>

    <select id="getPromoterVideoInfo" resultType="com.alibaba.fastjson.JSONObject">
        SELECT IFNULL(avgVideoSales, 0.0) as 'avgVideoSales', IFNULL(videoSales, 0.0) as 'videoSales'
        from kwai_promoter.kwai_promoter_base_info
        WHERE promoterId = #{promoterId}
    </select>

</mapper>
