<?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.works.mapper.WorksMapper">

    <resultMap type="com.ruoyi.frequency.works.entity.Works" id="WorksResult">
        <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="worksTypeId"    column="works_type_id"    />
        <result property="title"    column="title"    />
        <result property="coverImage"    column="cover_image"    />
        <result property="images"    column="images"    />
        <result property="price"    column="price"    />
        <result property="userType"    column="user_type"    />
        <result property="storeId"    column="store_id"    />
        <result property="source"    column="source"    />
        <result property="orderId"    column="order_id"    />
        <result property="likeNum"    column="like_num"    />
        <result property="stepNum"    column="step_num"    />
        <result property="commentNum"    column="comment_num"    />
        <result property="collectNum"    column="collect_num"    />
        <result property="browseNum"    column="browse_num"    />
        <result property="orderNo"    column="order_no"    />
        <result property="worksTypeName"    column="worksTypeName"    />
        <result property="ifHighend" column="if_highend"/>
    </resultMap>

    <sql id="selectWorksVo">
        SELECT
            t.id,
            t.create_by,
            t.create_time,
            t.update_by,
            t.update_time,
            t.del_flag,
            t.works_type_id,
            t.title,
            t.cover_image,
            t.images,
            t.price,
            t.user_type,
            t.store_id,
            t.source,
            t.order_id,
            t.like_num,
            t.step_num,
            t.comment_num,
            t.collect_num,
            t.browse_num,
            t.remark,
            o.order_no,
            if(t.user_type = 4, d.dict_label, CONCAT(t3.`name`, '-', t2.`name` )) worksTypeName,
            t.if_highend
        FROM
            t_works AS t
                LEFT JOIN t_works_type AS t2 ON t.works_type_id = t2.id
                LEFT JOIN t_works_type AS t3 ON t2.pid = t3.id
                LEFT JOIN t_order AS o ON t.order_id = o.id
                LEFT JOIN t_store AS s ON t.store_id = s.id and t.user_type = 2
                LEFT JOIN t_enterprise AS e ON t.store_id = e.id and t.user_type = 4
                LEFT JOIN t_store AS s2 ON s2.bind_enterprise_id = e.id and t.user_type = 4
                LEFT JOIN sys_dict_data AS d ON d.dict_code = t.works_type_id and t.user_type = 4
    </sql>

    <select id="selectWorksList" parameterType="com.ruoyi.frequency.works.entity.Works" resultMap="WorksResult">
        <include refid="selectWorksVo"/>
        <where>
            and t.del_flag != 2
            <if test="title != null  and title != ''"> and t.title like concat('%',#{title}, '%')</if>
            <if test="storeId != null "> and store_id = #{storeId}</if>
            <if test="source != null "> and source = #{source}</if>
            <if test="beginTime != null ">
                and  t.create_time >= #{beginTime}

            </if>
            <if test="endTime != null ">
                and  t.create_time &lt; #{endTime}
            </if>
            <if test="worksTypeId != null and worksTypeId != ''">
                and(
                t2.id = #{worksTypeId}
                or t3.id = #{worksTypeId}
                or t.works_type_id = #{worksTypeId}
                )
            </if>
            <if test="storeInfo != null and storeInfo.nickname != null  and storeInfo.nickname != ''">
                and (
                s.nickname like concat('%',#{storeInfo.nickname}, '%')
                or
                e.name like concat('%',#{storeInfo.nickname}, '%')
                )
            </if>
            <if test="storeInfo != null and storeInfo.mobile != null  and storeInfo.mobile != ''">
                and (
                s.mobile like concat('%',#{storeInfo.mobile}, '%')
                or
                s2.mobile like concat('%',#{storeInfo.mobile}, '%')
                )
            </if>
            <if test="enterpriseId != null  and enterpriseId != ''">and e.id = #{enterpriseId}</if>
            <if test="ifHighend != null and ifHighend != '' ">
                and t.if_highend = #{ifHighend}
            </if>
        </where>
        order by t.create_time desc
    </select>


    <select id="statistics" parameterType="com.ruoyi.frequency.works.entity.Works" resultType="com.ruoyi.frequency.works.model.result.WorksCount">
        SELECT
        count(distinct t.store_id) number,
        count(t.id) worksNum,
        ifnull(sum(t.like_num),0) likeNum,
        ifnull(sum(t.step_num),0) stepNum,
        ifnull(sum(t.collect_num),0) collectNum,
        ifnull(sum(t.browse_num),0) browseNum,
        ifnull(sum(t.comment_num),0) commentNum
        FROM
        t_works AS t
        where
        t.id in
        <foreach item="id" collection="list" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>

    <select id="selectWorksById" parameterType="Long" resultMap="WorksResult">
        <include refid="selectWorksVo"/>
        where t.id = #{id}
    </select>

    <insert id="insertWorks" parameterType="com.ruoyi.frequency.works.entity.Works">
        insert into t_works
        <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="worksTypeId != null">works_type_id,</if>
            <if test="title != null">title,</if>
            <if test="coverImage != null">cover_image,</if>
            <if test="images != null">images,</if>
            <if test="price != null">price,</if>
            <if test="storeId != null">store_id,</if>
            <if test="source != null">source,</if>
            <if test="orderId != null">order_id,</if>
            <if test="likeNum != null">like_num,</if>
            <if test="stepNum != null">step_num,</if>
            <if test="commentNum != null">comment_num,</if>
            <if test="collectNum != null">collect_num,</if>
            <if test="browseNum != null">browse_num,</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="worksTypeId != null">#{worksTypeId},</if>
            <if test="title != null">#{title},</if>
            <if test="coverImage != null">#{coverImage},</if>
            <if test="images != null">#{images},</if>
            <if test="price != null">#{price},</if>
            <if test="storeId != null">#{storeId},</if>
            <if test="source != null">#{source},</if>
            <if test="orderId != null">#{orderId},</if>
            <if test="likeNum != null">#{likeNum},</if>
            <if test="stepNum != null">#{stepNum},</if>
            <if test="commentNum != null">#{commentNum},</if>
            <if test="collectNum != null">#{collectNum},</if>
            <if test="browseNum != null">#{browseNum},</if>
        </trim>
    </insert>

    <update id="updateWorks" parameterType="com.ruoyi.frequency.works.entity.Works">
        update t_works
        <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="worksTypeId != null">works_type_id = #{worksTypeId},</if>
            <if test="title != null">title = #{title},</if>
            <if test="coverImage != null">cover_image = #{coverImage},</if>
            <if test="images != null">images = #{images},</if>
            <if test="price != null">price = #{price},</if>
            <if test="storeId != null">store_id = #{storeId},</if>
            <if test="source != null">source = #{source},</if>
            <if test="orderId != null">order_id = #{orderId},</if>
            <if test="likeNum != null">like_num = like_num + #{likeNum},</if>
            <if test="stepNum != null">step_num = step_num + #{stepNum},</if>
            <if test="commentNum != null">comment_num = comment_num + #{commentNum},</if>
            <if test="collectNum != null">collect_num = collect_num + #{collectNum},</if>
            <if test="browseNum != null">browse_num =  browse_num + #{browseNum},</if>
            <if test="trendsTime != null">trends_time = #{trendsTime},</if>
        </trim>
        where id = #{id}
        <if test="likeNum != null">and like_num + #{likeNum} >= 0</if>
        <if test="stepNum != null">and step_num + #{stepNum} >= 0</if>
        <if test="commentNum != null">and comment_num + #{commentNum} >= 0</if>
        <if test="collectNum != null">and collect_num + #{collectNum} >= 0</if>
        <if test="browseNum != null">and  browse_num + #{browseNum} >= 0</if>
    </update>

    <delete id="deleteWorksById" parameterType="Long">
        delete from t_works where id = #{id}
    </delete>

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

    <select id="worksPage" resultType="com.ruoyi.controller.resp.WorksPageResp">
        SELECT * from ( SELECT
        t.id,
        t.create_time,
        t.trends_time,
        t.del_flag,
        t.works_type_id,
        t.title,
        t.cover_image,
        t.images,
        t.price,
        t.user_type,
        t.store_id,
        t.source,
        t.order_id,
        t.like_num,
        t.step_num,
        t.comment_num,
        t.collect_num,
        t.browse_num,
        t.remark,
        case when r.id is null then 1 else 2 end isRecommend,
        r.sort,
        t.if_highend,
        CASE
        WHEN (
        SELECT
        COUNT( 1 )
        FROM
        t_work_like fl
        WHERE
        fl.del_flag = 1
        AND fl.type = 1
        AND fl.user_id = #{userVo.userId}
        AND fl.user_type = #{userVo.userType}
        AND fl.work_id = t.id
        ) = 0 THEN
        1 ELSE 2
        END isLike
        FROM
        t_works AS t
        LEFT JOIN t_store AS s ON t.store_id = s.id
        LEFT JOIN t_recommend r on r.object_id = t.id and r.type = 1 and r.del_flag = 1
        <where>
            and t.del_flag = 1 and t.is_hide = 1
            <if test="req.showEnterprise!=null and req.showEnterprise==2">
                and s.id is not null
            </if>
            <if test="req.typeIds!=null and req.typeIds.size()>0">
                AND t.works_type_id IN
                <foreach collection="req.typeIds" index="index" item="i" open="("
                         separator="," close=")">
                    #{i}
                </foreach>
            </if>
            <if test="req.keyword!=null and req.keyword!=''">
                AND (
                t.title LIKE CONCAT('%',#{req.keyword},'%')
                or
                s.nickname LIKE CONCAT('%',#{req.keyword},'%')
                or
                s.name LIKE CONCAT('%',#{req.keyword},'%')
                )
            </if>
            <if test="req.blockUserList!=null and req.blockUserList.size() > 0">
                AND (
                (t.user_type = 2 and t.store_id not in
                <foreach collection="req.blockUserList" item="userVo" open="(" close=")" separator=",">
                    IF(#{userVo.userType} = 2,#{userVo.userId},'')
                </foreach>)
                or
                (t.user_type = 4 and t.store_id not in
                <foreach collection="req.blockUserList" item="userVo" open="(" close=")" separator=",">
                    IF(#{userVo.userType} = 4,#{userVo.userId},'')
                </foreach>)
                )
            </if>

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

            <if test="req.shieldWorksIdList!=null and req.shieldWorksIdList.size() > 0">
                AND t.id not in
                <foreach collection="req.shieldWorksIdList" item="i" open="(" close=")" separator=",">
                    #{i}
                </foreach>
            </if>

            <if test="req.userType!=null and req.userType!=''">
                <if test="req.userType==1">
                    AND t.customer_id = #{req.userId}
                </if>
                <if test="req.userType==2">
                    AND t.store_id = #{req.userId}
                    AND t.user_type = #{req.userType}
                </if>
                <if test="req.userType==4">
                    AND t.store_id = #{req.userId}
                    AND t.user_type = #{req.userType}
                    <if test="req.typeId!=null and req.typeId!=''">
                        AND t.works_type_id = #{req.typeId}
                    </if>
                </if>
            </if>

            <if test="req.ifHighend!=null and req.ifHighend!=''">
                AND t.if_highend = #{req.ifHighend}
            </if>
        </where>
        )tt
        <if test="req.orderSort==null or req.orderSort==''">
            order by tt.isRecommend desc, tt.sort,tt.trends_time desc,tt.id
        </if>
        <if test="req.orderSort!=null and req.orderSort!=''">
            order by
            <if test="req.orderSort==1">
                tt.create_time desc
            </if>
            <if test="req.orderSort==2">
                tt.like_num desc
            </if>
            <if test="req.orderSort==3">
                tt.comment_num desc
            </if>
        </if>

    </select>

    <select id="worksDetail" resultType="com.ruoyi.controller.resp.WorksDetailResp">
        SELECT
            t.id,
            t.create_time,
            t.del_flag,
            t.works_type_id,
            t.title,
            t.cover_image,
            t.images,
            t.price,
            t.user_type,
            t.store_id,
            t.source,
            t.order_id,
            t.like_num,
            t.step_num,
            t.comment_num,
            t.collect_num,
            t.browse_num,
            t.remark,
            IF(wl.id is null,1,2) isLike,
            IF(wt.id is null,1,2) isStep,
            IF(c.id is null,1,2) isCollect,
            IF(#{userVo.userType} = 2 AND #{userVo.userId} = t.store_id ,2,1) isSelf,
            o.order_no,
            o.create_time orderCreateTime,
            t.if_highend
        FROM
            t_works AS t
                LEFT JOIN t_order o on  o.id = t.order_id
                LEFT JOIN t_work_like wl on wl.del_flag = 1 AND wl.type = 1
                AND wl.user_id = #{userVo.userId}
                AND wl.user_type = #{userVo.userType}
                AND wl.work_id = t.id
                LEFT JOIN t_work_step wt on wt.del_flag = 1
                AND wt.user_id = #{userVo.userId}
                AND wt.user_type = #{userVo.userType}
                AND wt.work_id = t.id
                LEFT JOIN t_collect c on c.del_flag = 1 AND c.type = 1
                AND c.user_id = #{userVo.userId}
                AND c.user_type = #{userVo.userType}
                AND c.object_id = t.id
        where t.id = #{id}
    </select>


    <select id="orderWorksPriceList" resultType="com.ruoyi.controller.resp.OrderWorksPricePage">
        SELECT
            t.id,
            t.create_by,
            t.create_time,
            t.update_by,
            t.update_time,
            t.del_flag,
            t.order_id,
            t.type,
            t.price,
            t.`name`
        FROM
            t_order_price_record AS t
        where t.order_id = #{orderId}
    </select>
</mapper>
