<?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="com.ruoyi.screen.mapper.ZongzhiScreenOpinionMapper">


    <select id="getSentimentType" resultType="com.ruoyi.screen.domain.response.ZongzhiSentimentTypeResponse">
        select count(*) as count , type,date_time as publishTime
        from tc_yq_zs
        where is_status = '1'
        GROUP BY type, date_time
    </select>
    <select id="getSentimenCount" resultType="com.ruoyi.screen.domain.response.ZongzhiSentimentCountResponse">
        select count(*) as count ,sentiment_type as type,ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS pt
        from tc_network_sentiment
        where sentiment_type !='2' and is_status = '1'
        GROUP BY sentiment_type
    </select>
    <select id="getSentimenClassificationBymediaType"
            resultType="com.ruoyi.screen.domain.response.ZongzhiSentimenClassificationResponse">
        select media_type as type, sum(media_count) as pt
        from tc_yq_fl
        where media_type is not null
          and is_status = '1'
        GROUP BY media_type
    </select>
    <select id="getSentimenClassificationByeventType"
            resultType="com.ruoyi.screen.domain.response.ZongzhiSentimenClassificationResponse">
        select event_type as type, sum(event_count) as pt
        from tc_yq_fl
        where event_type is not null
          and is_status = '1'
        GROUP BY event_type
    </select>
    <select id="getWordCloud" resultType="com.ruoyi.screen.domain.response.ZongzhiSentimenWordCloudResponse">
        select yuci_type AS type
        from tc_network_sentiment
        where is_status = '1'
        GROUP BY yuci_type
    </select>
    <select id="getSentimentReport"
            resultType="com.ruoyi.screen.domain.response.ZongzhiSentimentReportResponse">
        select a.reminder, b.isturn
        from (select count(reminder) as reminder from tc_network_sentiment where reminder = '1' and is_status = '1') a,
             (select count(*) as isturn from tc_network_sentiment where isturn = '1' and is_status = '1') b

    </select>
    <select id="getSentimentReportbyReportType"
            resultType="com.ruoyi.screen.domain.response.ZongzhiReportResponse">
        select count(sentiment_report_type) as count, sentiment_report_type as sentimentReportType
        from tc_network_sentiment
        where sentiment_report_type is not null and is_status = '1'
        GROUP BY sentiment_report_type
    </select>
    <select id="getSentimentDept" resultType="com.ruoyi.screen.domain.response.ZongzhiSentimentDeptResponse">
        select dep_name, count(*) as count
        from tc_network_sentiment
        where isturn ='1' and is_status = '1'
        GROUP BY dep_name
    </select>
    <select id="getSentimentFiltering"
            resultType="com.ruoyi.screen.domain.response.ZongzhiSentimentfilterResponse">
        select a.count1, b.count2, c.count3, d.pt
        from (select count(*) as count1 from tc_network_sentiment where sentiment_type = 6 and is_status = '1') a,
             (select count(*) as count2 from tc_network_sentiment where sentiment_type = 7 and is_status = '1') b,
             (select count(*) as count3 from tc_network_sentiment where sentiment_type = 8 and is_status = '1') c,
             (select ROUND(COUNT(case when sentiment_type = 8 then sentiment_type else null end) * 100.0 /
                           SUM(COUNT(*)) OVER (), 1) AS pt
              from tc_network_sentiment
              where  is_status = '1') d

    </select>
</mapper>