<?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.ZongzhiScreenManageMapper">


    <select id="getWorkDynamics" resultType="com.ruoyi.screen.domain.response.ZongzhiWorkDynamicsResponse">
        SELECT type, materials_name as name,id
        FROM `tc_work_dongtai` where materials_name is not null
        GROUP BY type, materials_name
    </select>
    <select id="getNetworkReport" resultType="com.ruoyi.screen.domain.response.ZongzhiNetworkReportResponse">
        select count(*) as count, ROUND(count(case when state=2 THEN 1 ELSE NULL END ) * 100.0/ sum(count(*)) OVER (), 1
        )as handle
        FROM tc_network_report

    </select>
    <select id="getaction" resultType="com.ruoyi.screen.domain.response.ZongzhiActionResponse">
        select sum(count) as count, month_year as zhuanxiangTime ,type
        from tc_qlzxxdsj  WHERE NOW() >= DATE_SUB(CURDATE(), INTERVAL 11 MONTH) AND month_year &lt;=NOW()
        GROUP BY type, month_year ;
    </select>
    <select id="getReportByYear" resultType="com.ruoyi.screen.domain.response.ZongzhiReportByYearResponse">
        SELECT count(*)as count,source,DATE_FORMAT(NOW(), '%Y') as year
        FROM `tc_network_report`
        GROUP BY source
    </select>
    <select id="getActionDynamicst"
            resultType="com.ruoyi.screen.domain.response.ZongzhiActionDynamicstResponse">
        select zhuanxiang_img as zhuanxiangImg,zhuanxiang_content as content,zhuanxiang_title as title,id
        from tc_qinglang_zhuanxiang
    </select>
    <select id="getWorkDynamicsCount" resultType="java.lang.Integer">
        SELECT count(*) as count
        FROM `tc_work_dongtai`
        where materials_name is not null
    </select>
    <select id="getMap" resultType="com.ruoyi.screen.domain.response.ZongzhiMapResponse">
        select id,longitude,latitude,jbdx from tc_network_report
    </select>


</mapper>