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


    <select id="getArticles" resultType="com.ruoyi.screen.domain.response.ZongzhiArticlesResponse">
        select type, count(*) as count
        from tc_network_article   where is_status ='1' group by type
    </select>
    <select id="getAppraiser" resultType="com.ruoyi.screen.domain.response.ZongzhiAppraiserResponse">
        select name as type, sum(percent) AS pt
        from tc_bjsj
        where is_status = '1'
        GROUP BY type
    </select>
    <select id="getAppraiserComplete"
            resultType="com.ruoyi.screen.domain.response.ZongzhiAppraiserCompleteResponse">
        SELECT
            SUM( s.finish ),
            b.NAME
        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 area_id,
                    finish
                FROM
                    tc_wpyrwwcl
                WHERE
                    is_status = '1' ) s
                LEFT JOIN tc_town b ON s.area_id = b.id

        GROUP BY
            s.area_id
    </select>
</mapper>