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


    <select id="selectOnePj" resultType="com.ruoyi.gysl.entity.response.XmpjqdOneResponse">
        SELECT
            c.pjpm,
            '总投资额' AS ysmc,
            c.total_count AS count
        FROM
            (
            SELECT
            ROW_NUMBER() OVER ( ORDER BY a.ztze DESC ) AS pjpm,
            a.id,
            a.NAME,
            a.begain_time,
            a.end_time,
            a.xzfl,
            a.xmfrdwxz,
            a.ztze,
            COUNT(*) OVER () AS total_count
            FROM
            gysl_basic_information a
            GROUP BY
            a.id
            ) c
        WHERE
            c.id = #{id}
    </select>
    <select id="zdzZtze" resultType="com.ruoyi.gysl.entity.response.XmhxDetail.XmhxDetail">
        select IFNULL(MAX(ztze),0) as pjpm,
               '总投资额' as ysmc
        from gysl_basic_information
    </select>
    <select id="zwsZtze" resultType="java.math.BigDecimal">
        SELECT ztze FROM gysl_basic_information where ztze is not null
        order by ztze desc
    </select>
    <select id="meZtze" resultType="com.ruoyi.gysl.entity.response.XmhxDetail.XmhxDetail">
        SELECT
            ztze as pjpm,
            '总投资额' as ysmc
        FROM gysl_basic_information where  id = #{id}
    </select>
    <select id="page" resultType="com.ruoyi.gysl.entity.Xmpjqd">
        SELECT
        a.id,
        a.name,
        a.begain_time,
        a.end_time,
        a.xzfl,
        a.xmfrdwxz,
        a.ztze,
        b.floor,
        b.scgd,
        b.two_and_four_cg as twoAndFourCg,
        b.scdmhz,
        b.two_and_three_lmhz as twoAndThreeLmhz
        FROM gysl_basic_information a
        left join gysl_building_information b on a.id = b.xm_id and b.sfwzyld = 1
        <where>
            <if test="req.xzfl != null">
                AND a.xzfl = #{req.xzfl}
            </if>
            <if test="req.name != null and req.name != '' ">
                AND a.name like concat('%',#{req.name},'%')
            </if>
            <if test="req.begainTime != null and req.begainTime!= ''">
                AND a.begain_time = #{req.begainTime}
            </if>
            <if test="req.endTime != null and req.endTime!=''">
                AND a.end_time = #{req.endTime}
            </if>
        </where>
        group by a.id,
        a.name,
        a.begain_time,
        a.end_time,
        a.xzfl,
        a.xmfrdwxz,
        a.ztze,
        b.floor,
        b.scgd,
        b.two_and_four_cg ,
        b.scdmhz,
        b.two_and_three_lmhz
        <if test="req.pjdj == 2 and req.sort == 1">
            order by a.ztze asc
        </if>
        <if test="req.pjdj == 2 and req.sort == 2">
            order by a.ztze desc
        </if>
        <if test="req.pjdj == 3 and req.sort == 1">
            order by b.floor asc
        </if>
        <if test="req.pjdj == 3 and req.sort == 2">
            order by b.floor desc
        </if>
        <if test="req.pjdj == 4 and req.sort == 1">
            order by b.scgd asc
        </if>
        <if test="req.pjdj == 4 and req.sort == 2">
            order by b.scgd desc
        </if>
        <if test="req.pjdj == 5 and req.sort == 1">
            order by b.two_and_four_cg asc
        </if>
        <if test="req.pjdj == 5 and req.sort == 2">
            order by b.two_and_four_cg desc
        </if>
        <if test="req.pjdj == 6 and req.sort == 1">
            order by b.scdmhz asc
        </if>
        <if test="req.pjdj == 6 and req.sort == 2">
            order by b.scdmhz desc
        </if>
        <if test="req.pjdj == 7 and req.sort == 1">
            order by b.two_and_three_lmhz asc
        </if>
        <if test="req.pjdj == 7 and req.sort == 2">
            order by b.two_and_three_lmhz desc
        </if>
    </select>

</mapper>

