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


    <select id="getSource" resultType="com.ruoyi.screen.domain.response.ZongzhiSourceResponse">
        select count(*) as count ,type
        from tc_data_source where type is not null
        GROUP BY type
    </select>
    <select id="getsafety" resultType="com.ruoyi.screen.domain.response.ZongzhiSafetyResponse">
        select count(*) as count ,attack_type AS attackType
        from tc_safety_detection
        GROUP BY attack_type
    </select>
    <select id="getSafetyList" resultType="com.ruoyi.screen.domain.response.ZongzhiSafetyListResponse">
        select source_ip, attack_time AS attackTime , attack_type as attackType
        from tc_safety_detection
    </select>

    <select id="getAttack" resultType="com.ruoyi.screen.domain.response.ZongzhiAttackResponse">
        SELECT name, attack_count as attackCount
        FROM tc_attack

    </select>
    <select id="getUnAttackTop" resultType="com.ruoyi.screen.domain.response.ZongzhiUnAttackResponse">
        select attacked_ip as attackedIp, attacked_ip_region as attackIpRegion, count(*)as count, ROUND(COUNT(*) * 100.0
        / SUM(COUNT(*)) OVER (), 1) AS pt
        from tc_safety_detection
        GROUP BY attacked_ip
        ORDER BY count desc limit 5
    </select>
    <select id="getSafetyHazard" resultType="com.ruoyi.screen.domain.response.ZongzhiSafetyHazardResponse">
        select type,count(*) as count from tc_safety_danger GROUP BY type
    </select>
    <select id="getHazardDisposal" resultType="com.ruoyi.screen.domain.response.ZongzhiHazardDisposalResponse">
        select risk_name as riskName, level, unit_name AS unitName, risk_source AS riskSource
        from tc_safety_danger
    </select>
    <select id="getNotification" resultType="com.ruoyi.screen.domain.response.ZongzhiNotificationResponse">
        select unit_name as unitName , ip_dns as ipDns, state from tc_safety_danger
    </select>
    <select id="getDengbaoSystem" resultType="com.ruoyi.screen.domain.response.ZongzhiDengbaoSystemResponse">
        select count(*) as count,'等保系统' as name from tc_dengbao_system
    </select>
    <select id="getDengbaoUnit" resultType="com.ruoyi.screen.domain.response.ZongzhiDengbaoUnitResponse">
        select count(*) as count, '等保单位' as name
        from tc_dengbao_unit
    </select>
    <select id="getZhengfu" resultType="com.ruoyi.screen.domain.response.ZongzhiZhengfuResponse">
        select count(*) as count, '政府网站' as name
        from tc_government_web
    </select>
    <select id="getIdc" resultType="com.ruoyi.screen.domain.response.ZongzhiIdcResponse">
        select count(*) as count, 'IDC单位' as name
        from tc_idc_unit
    </select>
    <select id="getTbArea" resultType="com.ruoyi.screen.domain.response.ZongzhiTbAreaResponse">
        SELECT
        s.areaid AS areaId,
        s.area_id as Area,
        ROUND( COUNT( * ) * 100.0 / SUM( COUNT( * ) ) OVER ( ), 1 ) AS pt,
        count( * ) AS count
        FROM
        (
        SELECT
        CASE
        WHEN
        LOCATE( ',', area_id ) = 0 THEN
        TRIM( TRAILING ']' FROM SUBSTR( area_id, LOCATE( '[', area_id ) + 1 ) ) ELSE TRIM( TRAILING ']' FROM
        SUBSTRING_INDEX( area_id, ',', - 1 ) )
        END AS areaid,area_id
        FROM
        tc_tb
        WHERE
        tb_type = '1'
        AND area_id IS NOT NULL
        ) s
        GROUP BY
        s.areaid
    </select>
    <select id="getTbDept" resultType="com.ruoyi.screen.domain.response.ZongzhiTbDeptResponse">
        SELECT COUNT(*) AS count, dep_name
        from tc_tb
        where tb_type = '2'
        group by dep_name

    </select>
    <select id="getmonthList" resultType="com.ruoyi.screen.domain.response.ZongzhiTbDeptCompleteMonthResponse">
        SELECT COUNT(*) AS count,
        count(case when state=1 then state else null end) as complete,
        count(case when state=1 then state else null end) / COUNT(*) AS pt
        FROM`tc_tb`WHERE DATE_FORMAT( tb_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
    </select>
    <select id="getthirdList"
            resultType="com.ruoyi.screen.domain.response.ZongzhiTbDeptCompleteThirdMonthResponse">
        SELECT COUNT(*) AS count,
        count(case when state=1 then state else null end) as complete,
        count(case when state=1 then state else null end) / COUNT(*) AS pt
        FROM `tc_tb` WHERE QUARTER(tb_time)=QUARTER(now());

    </select>

    <select id="getsixList"
            resultType="com.ruoyi.screen.domain.response.ZongzhiTbDeptCompleteSixResponse">
        SELECT COUNT(*) AS count,
        count(case when state=1 then state else null end) as complete,
        count(case when state=1 then state else null end) / COUNT(*) AS pt
        FROM `tc_tb` WHERE DATE(tb_time) >= DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH) and tb_time &lt;=
        CURRENT_DATE();;

    </select>


    <select id="getyearList"
            resultType="com.ruoyi.screen.domain.response.ZongzhiTbDeptCompleteSixResponse">
        SELECT COUNT(*) AS count,
        count(case when state=1 then state else null end) as complete,
        count(case when state=1 then state else null end) / COUNT(*) AS pt
        FROM `tc_tb` WHERE DATE(tb_time) >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR) and tb_time &lt;= CURRENT_DATE();;

    </select>
</mapper>