<?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.gysl.mapper.QyStatsMapper">

    <select id="allProject" resultType="com.ruoyi.gysl.entity.stats.AllProjectResponse">
        SELECT COUNT(*) AS allProject,
        ROUND(IFNULL(SUM(b.zjzmj), 0) / 10000, 1) AS allGrossArea,
        IFNULL(SUM(CASE WHEN a.xzfl = 1 THEN 1 ELSE 0 END), 0) AS allBuilding1,
        IFNULL(SUM(CASE WHEN a.xzfl = 2 THEN 1 ELSE 0 END), 0) AS allBuilding2,
        IFNULL(SUM(CASE WHEN a.xzfl = 3 THEN 1 ELSE 0 END), 0) AS allBuilding3,
        <!-- 当前新开工项目数 -->
        IFNULL(SUM(
        CASE WHEN LEFT(a.begain_time, 4) = YEAR(NOW()) THEN 1 ELSE 0 END
        ), 0) AS currentYearProject,
        <!-- 当年_建筑面积 -->
        ROUND(IFNULL(SUM(
        CASE WHEN LEFT(a.begain_time, 4) = YEAR(NOW()) THEN b.zjzmj ELSE 0 END
        ), 0)/10000,1) AS currentYearGrossArea,
        <!-- 当年_已建数量 -->
        IFNULL(SUM(
        CASE WHEN LEFT(a.begain_time, 4) = YEAR(NOW()) and a.xzfl = 1 THEN 1 ELSE 0 END
        ), 0) AS currentBuilding1,
        <!-- 当年_在建数量 -->
        IFNULL(SUM(
        CASE WHEN LEFT(a.begain_time, 4) = YEAR(NOW()) and a.xzfl = 2 THEN 1 ELSE 0 END
        ), 0) AS currentBuilding2,
        <!-- 当年_拟建数量 -->
        IFNULL(SUM(
        CASE WHEN LEFT(a.begain_time, 4) = YEAR(NOW()) and a.xzfl = 3 THEN 1 ELSE 0 END
        ), 0) AS currentBuilding3
        FROM gysl_basic_information a
        LEFT JOIN gysl_plan_information b ON a.id = b.xm_id
        where a.tyshxydm = #{userName}
    </select>
    <select id="relationalProject" resultType="com.ruoyi.gysl.entity.BasicInformation">

    </select>
    <select id="qyNotice" resultType="com.ruoyi.docking.entity.SmartDeclaration">
        select * from gysl_smart_declaration
        where tyshxydm = #{username}
    </select>
    <select id="qyNoticeCount" resultType="java.lang.Integer">
        select count(*) from gysl_smart_declaration
        where tyshxydm = #{username} and is_read = 1
    </select>

</mapper>

