<?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.enterprise.mapper.EnterpriseMapper">

    <resultMap type="com.ruoyi.frequency.enterprise.entity.Enterprise" id="EnterpriseResult">
        <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="avatar"    column="avatar"    />
        <result property="sketch"    column="sketch"    />
        <result property="address"    column="address"    />
        <result property="longitude"    column="longitude"    />
        <result property="latitude"    column="latitude"    />
        <result property="wecom"    column="wecom"    />
        <result property="goodAt"    column="good_at"    />
        <result property="serviceAmount"    column="service_amount"    />
        <result property="serviceOrderNum"    column="service_order_num"    />
        <result property="star"    column="star"    />
        <result property="sysUserId"    column="sys_user_id"    />
        <result property="userType"    column="user_type"    />
        <result property="userId"    column="user_id"    />
        <result property="account"    column="account"    />
        <result property="loginPwd"    column="login_pwd"    />
        <result property="quotationImage"    column="quotation_image"    />
        <result property="productionCycle"    column="production_cycle"    />
        <result property="numberPeople"    column="number_people"    />
        <result property="pricePosition"    column="price_position"    />
        <result property="type"    column="type"    />
        <result property="bondAmount"    column="bond_amount"    />
        <result property="monthServiceAmount"    column="month_service_amount"    />
        <result property="enterpriseCreateTime"    column="enterprise_create_time"    />
        <result property="worksNum"    column="worksNum"    />
    </resultMap>

    <sql id="selectEnterpriseVo">
        SELECT
            t.id,
            t.create_by,
            t.create_time,
            t.update_by,
            t.update_time,
            t.`name`,
            t.del_flag,
            t.avatar,
            t.sketch,
            t.address,
            t.longitude,
            t.latitude,
            t.wecom,
            t.good_at,
            t.service_amount,
            t.service_order_num,
            t.star,
            t.sys_user_id,
            t.month_service_order_num,
            2 user_type,
            s.id user_id,
            su.user_name account,
            su. PASSWORD login_pwd,
            t.quotation_image,
            t.production_cycle,
            t.number_people,
            t.price_position,
            t.type,
            t.bond_amount,
            t.month_service_amount,
            t.enterprise_create_time,
            (
                SELECT
                    count(*)
                FROM
                    t_works w
                WHERE
                    w.store_id = t.id
                    AND w.del_flag != 2
            ) worksNum
        FROM
            t_enterprise AS t
                LEFT JOIN t_store AS s ON t.id = s.bind_enterprise_id
                LEFT JOIN sys_user AS su ON su.user_id = t.sys_user_id
    </sql>

    <select id="selectEnterpriseList" parameterType="com.ruoyi.frequency.enterprise.entity.Enterprise" resultMap="EnterpriseResult">
        <include refid="selectEnterpriseVo"/>
        <where> and t.del_flag != 2
            <if test="type != null  and type != ''"> and t.type = #{type}</if>
            <if test="delFlag != null "> and t.del_flag = #{delFlag}</if>
            <if test="createTime != null "> and t.create_time = #{createTime}</if>
            <if test="name != null  and name != ''"> and t.name like concat('%', #{name}, '%')</if>
            <if test="avatar != null  and avatar != ''"> and t.avatar = #{avatar}</if>
            <if test="sketch != null  and sketch != ''"> and t.sketch = #{sketch}</if>
            <if test="address != null  and address != ''"> and t.address = #{address}</if>
            <if test="longitude != null  and longitude != ''"> and t.longitude = #{longitude}</if>
            <if test="latitude != null  and latitude != ''"> and t.latitude = #{latitude}</if>
            <if test="wecom != null  and wecom != ''"> and t.wecom = #{wecom}</if>
            <if test="goodAt != null  and goodAt != ''"> and t.good_at = #{goodAt}</if>
            <if test="serviceAmount != null "> and t.service_amount = #{serviceAmount}</if>
            <if test="serviceOrderNum != null "> and t.service_order_num = #{serviceOrderNum}</if>
            <if test="star != null "> and t.star = #{star}</if>
            <if test="sysUserId != null "> and t.sys_user_id = #{sysUserId}</if>
            <if test="userInfo != null and userInfo.nickname != null  and userInfo.nickname != '' ">
                and s.nickname like concat('%', #{userInfo.nickname}, '%')
            </if>
            <if test="userInfo != null and  userInfo.mobile != null and  userInfo.mobile != '' ">
                and s.mobile like concat('%', #{userInfo.mobile}, '%')
            </if>
            <if test="account != null and account != ''"> and su.user_name  like concat('%', #{account}, '%')</if>

        </where>
        order by t.create_time desc
    </select>

    <select id="statistics" parameterType="com.ruoyi.frequency.enterprise.entity.Enterprise" resultType="com.ruoyi.frequency.enterprise.model.result.EnterpriseCount">
        SELECT
        count(1) AS number,
        IFNULL(sum(t.service_amount), 0) AS serviceAmount,
        IFNULL(sum(t.service_order_num), 0) AS serviceOrderNum,
        IFNULL(
        SUM(
        (
        SELECT
        count(*)
        FROM
        t_works w
        WHERE
        w.store_id = t.id
        AND w.del_flag != 2
        )
        ),
        0
        ) worksNum
        FROM
        t_enterprise AS t
        where
        t.id in
        <foreach item="id" collection="list" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>

    <select id="selectEnterpriseById" parameterType="Long" resultMap="EnterpriseResult">
        <include refid="selectEnterpriseVo"/>
        where t.id = #{id}
    </select>

    <insert id="insertEnterprise" parameterType="com.ruoyi.frequency.enterprise.entity.Enterprise">
        insert into t_enterprise
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createdTime != null">created_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="avatar != null">avatar,</if>
            <if test="sketch != null">sketch,</if>
            <if test="address != null">address,</if>
            <if test="longitude != null">longitude,</if>
            <if test="latitude != null">latitude,</if>
            <if test="wecom != null">wecom,</if>
            <if test="goodAt != null">good_at,</if>
            <if test="serviceAmount != null">service_amount,</if>
            <if test="serviceOrderNum != null">service_order_num,</if>
            <if test="star != null">star,</if>
            <if test="sysUserId != null">sys_user_id,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createdTime != null">#{createdTime},</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="avatar != null">#{avatar},</if>
            <if test="sketch != null">#{sketch},</if>
            <if test="address != null">#{address},</if>
            <if test="longitude != null">#{longitude},</if>
            <if test="latitude != null">#{latitude},</if>
            <if test="wecom != null">#{wecom},</if>
            <if test="goodAt != null">#{goodAt},</if>
            <if test="serviceAmount != null">#{serviceAmount},</if>
            <if test="serviceOrderNum != null">#{serviceOrderNum},</if>
            <if test="star != null">#{star},</if>
            <if test="sysUserId != null">#{sysUserId},</if>
        </trim>
    </insert>

    <update id="updateEnterprise" parameterType="com.ruoyi.frequency.enterprise.entity.Enterprise">
        update t_enterprise
        <trim prefix="SET" suffixOverrides=",">
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createdTime != null">created_time = #{createdTime},</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="avatar != null">avatar = #{avatar},</if>
            <if test="sketch != null">sketch = #{sketch},</if>
            <if test="address != null">address = #{address},</if>
            <if test="longitude != null">longitude = #{longitude},</if>
            <if test="latitude != null">latitude = #{latitude},</if>
            <if test="wecom != null">wecom = #{wecom},</if>
            <if test="goodAt != null">good_at = #{goodAt},</if>
            <if test="serviceAmount != null">service_amount = #{serviceAmount},</if>
            <if test="serviceOrderNum != null">service_order_num = #{serviceOrderNum},</if>
            <if test="star != null">star = #{star},</if>
            <if test="sysUserId != null">sys_user_id = #{sysUserId},</if>
            <if test="bondAmount != null">bond_amount = #{bondAmount},</if>
            <if test="monthServiceAmount != null">month_service_amount = #{monthServiceAmount},</if>
            <if test="enterpriseCreateTime != null">enterprise_create_time = #{enterpriseCreateTime},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteEnterpriseById" parameterType="Long">
        delete from t_enterprise where id = #{id}
    </delete>

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

    <select id="enterprisePage" resultType="com.ruoyi.controller.resp.StorePageResp">
        SELECT * FROM ( SELECT
        e.id,
        e.wecom,
        4 userType,
        e.create_time,
        e.del_flag,
        e.name nickname,
        e.avatar avatar,
        e.online_status online_status,
        e.online_duration online_duration,
        e.star star,
        case when f.id is null then 1 else 2 end as isFollow,

        e.service_order_num orderNum,

        e.month_service_order_num orderMonthNum,

        e.service_amount orderAmount,

        e.good_at speciality_area,
        e.good_at branch_work,

        e.id bind_enterprise_id,
        e.area area,
        e.quotation_image,
        e.production_cycle,
        e.number_people,
        e.price_position
        FROM
        t_enterprise AS e
        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 = e.id
                AND f.follower_user_type = 4
            </when>
        </choose>
        AND f.del_flag = 1
        WHERE
         e.del_flag = 1
        <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 == 2 ">
                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 e.id not in
                    <foreach collection="req.blockUserList" item="userVo" open="(" close=")" separator=",">
                        IF(#{userVo.userType} = 4, #{userVo.userId}, '')
                    </foreach>
                </when>
            </choose>
        </if>

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

        <if test="req.keyword!=null and req.keyword!=''">
            AND  e.name 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>
