<?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.frequency.workstype.mapper.WorksTypeMapper">

    <resultMap type="com.ruoyi.frequency.workstype.entity.WorksType" id="WorksTypeResult">
        <result property="id"    column="id"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="delFlag"    column="del_flag"    />
        <result property="name"    column="name"    />
        <result property="code"    column="code"    />
        <result property="pid"    column="pid"    />
        <result property="level"    column="level"    />
        <result property="sort"    column="sort"    />
    </resultMap>

    <sql id="selectWorksTypeVo">
        select id, create_by, create_time, update_by, update_time, del_flag, name, code, pid, level,sort from t_works_type
    </sql>

    <select id="selectWorksTypeList" parameterType="com.ruoyi.frequency.workstype.entity.WorksType" resultMap="WorksTypeResult">
        <include refid="selectWorksTypeVo"/>
        <where>
            and del_flag != 2
            <if test="id != null "> and id != #{id}</if>
            <if test="delFlag != null "> and del_flag = #{delFlag}</if>
            <if test="createTime != null "> and create_time = #{createTime}</if>
            <if test="name != null  and name != ''"> and name like concat('%', #{name}, '%')</if>
            <if test="code != null  and code != ''"> and code = #{code}</if>
            <if test="pid != null "> and pid = #{pid}</if>
            <if test="level != null "> and level = #{level}</if>
        </where>
        order by sort
    </select>

    <select id="selectWorksTypeById" parameterType="Long" resultMap="WorksTypeResult">
        <include refid="selectWorksTypeVo"/>
        where id = #{id}
    </select>

    <insert id="insertWorksType" parameterType="com.ruoyi.frequency.workstype.entity.WorksType">
        insert into t_works_type
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="delFlag != null">del_flag,</if>
            <if test="name != null">name,</if>
            <if test="code != null">code,</if>
            <if test="pid != null">pid,</if>
            <if test="level != null">level,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="delFlag != null">#{delFlag},</if>
            <if test="name != null">#{name},</if>
            <if test="code != null">#{code},</if>
            <if test="pid != null">#{pid},</if>
            <if test="level != null">#{level},</if>
        </trim>
    </insert>

    <update id="updateWorksType" parameterType="com.ruoyi.frequency.workstype.entity.WorksType">
        update t_works_type
        <trim prefix="SET" suffixOverrides=",">
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="delFlag != null">del_flag = #{delFlag},</if>
            <if test="name != null">name = #{name},</if>
            <if test="code != null">code = #{code},</if>
            <if test="pid != null">pid = #{pid},</if>
            <if test="level != null">level = #{level},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteWorksTypeById" parameterType="Long">
        delete from t_works_type where id = #{id}
    </delete>

    <delete id="deleteWorksTypeByIds" parameterType="String">
        delete from t_works_type where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <select id="storePage" resultType="com.ruoyi.controller.resp.StorePageResp">
        SELECT * FROM ( SELECT
        t.id,
        t.create_time,
        t.del_flag,
        IF(#{req.isWorkingDrawing} = 1, t.nickname, e.name) nickname,
        IF(#{req.isWorkingDrawing} = 1, t.avatar, e.avatar) avatar,

        IF(#{req.isWorkingDrawing} = 1, t.online_status, e.online_status) online_status,
        IF(#{req.isWorkingDrawing} = 1, t.online_duration, e.online_duration) online_duration,
        IF(#{req.isWorkingDrawing} = 1, t.star, e.star) star,
        l.`name` levelName,
        l.icon,
        l.edge_piping_type,
        case when f.id is null then 1 else 2 end as isFollow,
        case when h.id is not null then 2 else 1 end as ifHighend,


        IF(#{req.isWorkingDrawing} = 1, (select count(1) from t_order o where t.id = o.store_id and o.status >= 4), e.service_order_num) orderNum,

        IF(#{req.isWorkingDrawing} = 1, (select count(1) from t_order o where t.id = o.store_id and o.status >= 4 and month(o.create_time) = month(now())), e.month_service_order_num) orderMonthNum,


        IF(#{req.isWorkingDrawing} = 1, ifnull((select ifnull(sum(o.amount),0) from t_order o where t.id = o.store_id and o.status >= 4),0), e.service_amount) orderAmount,

        IF(#{req.isWorkingDrawing} = 1, IF(h.id is not null,h.speciality_area,a.speciality_area), e.good_at) speciality_area,
        IF(#{req.isWorkingDrawing} = 1, IF(h.id is not null,h.branch_work,a.branch_work), e.good_at) branch_work,

        t.enter_date,
        t.bind_enterprise_id,
        IF(#{req.isWorkingDrawing} = 1, t.area, e.area) area,
        e.quotation_image,
        e.production_cycle,
        e.number_people,
        e.price_position
        FROM
        t_store AS t
        LEFT JOIN t_level l ON l.type = 2 and l.id = (
        SELECT
        l2.id
        FROM
        t_level l2
        WHERE
        l2.del_flag = 1
        and l2.type =2
        and l2.compliance &lt;= t.income
        ORDER BY
        l2.compliance DESC
        LIMIT 1
        )
        LEFT JOIN t_auth a ON t.id = a.store_id and a.del_flag = 1 and a.pay_status = 2 and a.result = 2
        LEFT JOIN t_follow f ON f.user_id = #{userVo.userId}
        AND f.user_type = #{userVo.userType}
        <choose>
            <when test="req.isWorkingDrawing == 2 ">
                AND f.follower_user_id = t.bind_enterprise_id
                AND f.follower_user_type = 4
            </when>
            <otherwise>
                AND f.follower_user_id = t.id
                AND f.follower_user_type = 2
            </otherwise>
        </choose>
        AND f.del_flag = 1
        LEFT JOIN t_enterprise e ON t.bind_enterprise_id = e.id
        LEFT JOIN t_highend_auth h ON t.id = h.store_id and h.status >= 3 and h.del_flag = 1
        WHERE
        t.del_flag = 1
        <if test="req.sex != null  and req.sex != ''">
            AND a.sex = #{req.sex}
        </if>
        <if test="req.ifHighend != null  and req.ifHighend != ''">
            <if test="req.ifHighend == 1">
                AND a.result = 2
                AND a.type = 1
            </if>
            <if test="req.ifHighend == 2">
                AND h.id is not null
            </if>
            <if test="req.ifHighend == 3">
                AND a.result = 2
                AND a.type = 3
            </if>
        </if>
        <if test="req.onlineStatus != null  and req.onlineStatus != ''">
            <if test="req.onlineStatus == 1 ">
                AND t.id in
                <foreach item="id" collection="req.userIds" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
            <if test="req.onlineStatus == 2 ">
                AND t.id not in
                <foreach item="id" collection="req.userIds" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
        </if>
        <if test="req.isWorkingDrawing != null  and req.isWorkingDrawing != ''">
            <if test="req.isWorkingDrawing == 1 ">
                AND (a.result = 2  or h.status >= 3)
                AND t.bind_enterprise_id is null
            </if>
            <if test="req.isWorkingDrawing == 2 ">
                AND t.bind_enterprise_id is not null
                AND e.name is not null  AND e.name != ''
                AND e.avatar is not null AND e.avatar != ''

                AND e.good_at is not null AND e.good_at != ''
                AND e.sketch is not null AND e.sketch != ''
                <if test="req.enterpriseType != null and req.enterpriseType != ''">
                    AND e.type = #{req.enterpriseType}
                </if>

            </if>
        </if>
        <if test="req.blockUserList!=null and req.blockUserList.size() > 0">
            <choose>
                <when test="req.isWorkingDrawing == 2 ">
                    AND t.bind_enterprise_id not in
                    <foreach collection="req.blockUserList" item="userVo" open="(" close=")" separator=",">
                        IF(#{userVo.userType} = 4, #{userVo.userId}, '')
                    </foreach>
                </when>
                <otherwise>
                    AND t.id not in
                    <foreach collection="req.blockUserList" item="userVo" open="(" close=")" separator=",">
                        IF(#{userVo.userType} = 2, #{userVo.userId}, '')
                    </foreach>

                </otherwise>
            </choose>
        </if>

        <if test="req.shieldUserList!=null and req.shieldUserList.size() > 0">
            <choose>
                <when test="req.isWorkingDrawing == 2 ">
                    AND t.bind_enterprise_id not in
                    <foreach collection="req.shieldUserList" item="userVo" open="(" close=")" separator=",">
                        IF(#{userVo.userType} = 4, #{userVo.userId}, '')
                    </foreach>
                </when>
                <otherwise>
                    AND t.id not in
                    <foreach collection="req.shieldUserList" item="userVo" open="(" close=")" separator=",">
                        IF(#{userVo.userType} = 2, #{userVo.userId}, '')
                    </foreach>

                </otherwise>
            </choose>
        </if>

        <if test="req.keyword!=null and req.keyword!=''">
            AND  (
            t.nickname LIKE CONCAT('%',#{req.keyword},'%')
            or
            e.name LIKE CONCAT('%',#{req.keyword},'%')
            or
            t.name LIKE CONCAT('%',#{req.keyword},'%')
            or
            t.speciality_area LIKE CONCAT('%',#{req.keyword},'%')
            )
        </if>

        <choose>
            <when test="req.minYear == null and req.maxYear != null">
                AND ifnull(YEAR(NOW())-YEAR(t.enter_date),0) >= #{req.maxYear}
            </when>
            <otherwise>
                <if test="req.minYear != null  and req.minYear != ''">
                    AND ifnull(YEAR(NOW())-YEAR(t.enter_date),0) >= #{req.minYear}
                </if>

                <if test="req.maxYear != null  and req.maxYear != ''">
                    AND ifnull(YEAR(NOW())-YEAR(t.enter_date),0) &lt; #{req.maxYear}
                </if>
            </otherwise>
        </choose>
        )tt
        <where>
            <if test="req.area != null  and req.area != ''">
                AND tt.area = #{req.area}
            </if>
            <if test="req.specialityArea != null  and req.specialityArea != ''">
                AND find_in_set(#{req.specialityArea},tt.speciality_area)
            </if>
            <if test="req.branchWork != null  and req.branchWork != ''">
                AND find_in_set(#{req.branchWork},tt.branch_work)
            </if>
        </where>
        ORDER BY
        <if test="req.type == 1">
            <if test="req.sortSql != ''">
                ${req.sortSql}
            </if>
            <if test="req.sortSql == ''">
                tt.create_time desc
            </if>
        </if>
        <if test="req.type == 2">
            tt.orderNum desc
        </if>
        <if test="req.type == 3">
            tt.orderAmount desc
        </if>
        <if test="req.type == 4">
            tt.star desc
        </if>
    </select>
</mapper>
