<?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.system.mapper.InfoUserMapper">

    <resultMap type="InfoUser" id="InfoUserResult">
        <result property="userId"    column="user_id"    />
        <result property="userName"    column="user_name"    />
        <result property="phone"    column="phone"    />
        <result property="nickName"    column="nick_name"    />
        <result property="userType"    column="user_type"    />
        <result property="email"    column="email"    />
        <result property="sex"    column="sex"    />
        <result property="avatar"    column="avatar"    />
        <result property="password"    column="password"    />
        <result property="status"    column="status"    />
        <result property="delFlag"    column="del_flag"    />
        <result property="cid"    column="cid"    />
        <result property="mycode"    column="mycode"    />
        <result property="thiscode"    column="thiscode"    />
        <result property="commission"    column="commission"    />
        <result property="annex"    column="annex"    />
        <result property="auditStatus"    column="audit_status"    />
        <result property="telPhone"    column="tel_Phone"    />
        <result property="shijStatus"    column="shij_status"    />
        <result property="driversLicense"    column="drivers_license"    />
        <result property="drivingLicense"    column="driving_license"    />
        <result property="vehiclePhotos"    column="vehicle_photos"    />
        <result property="licensePlate"    column="license_plate"    />
        <result property="fullName"    column="full_name"    />
        <result property="idNumber"    column="id_number"    />
        <result property="merchantType"    column="merchant_type"    />
        <result property="bankAccount"    column="bank_account"    />
        <result property="passportPhoto"    column="passport_photo"    />
        <result property="bankPhoto"    column="bank_photo"    />
        <result property="merchantIntroduction"    column="merchant_introduction"    />
        <result property="offline"    column="offline"    />
        <result property="storeId"    column="store_id"    />
        <result property="address"    column="address"    />
        <result property="longitude"    column="longitude"    />
        <result property="latitude"    column="latitude"    />
        <result property="juli"    column="juli"    />
    </resultMap>

    <sql id="selectInfoUserVo">
        select * from info_user
    </sql>

    <select id="selectInfoUserList" parameterType="InfoUser" resultMap="InfoUserResult">
        <include refid="selectInfoUserVo"/>
        <where>
            <if test="userName != null  and userName != ''"> and BINARY user_name like concat('%', #{userName}, '%') </if>
            <if test="phone != null  and phone != ''"> and phone = #{phone}</if>
            <if test="nickName != null  and nickName != ''"> and BINARY nick_name like concat('%', #{nickName}, '%')</if>
            <if test="userType != null  and userType != ''"> and user_type = #{userType}</if>
            <if test="email != null  and email != ''"> and email = #{email}</if>
            <if test="status != null  and status != ''"> and status = #{status}</if>
            <if test="delFlag != null  and delFlag != ''"> and del_flag = #{delFlag}</if>
            <if test="mycode != null  and mycode != ''"> and mycode = #{mycode}</if>
            <if test="thiscode != null  and thiscode != ''"> and thiscode = #{thiscode}</if>
            <if test="commission != null  and commission != ''"> and commission = #{commission}</if>
            <if test="annex != null  and annex != ''"> and annex = #{annex}</if>
            <if test="auditStatus != null  and auditStatus != ''"> and audit_status = #{auditStatus}</if>
            <if test="telPhone != null  and telPhone != ''"> and tel_phone = #{telPhone}</if>
            <if test="shijStatus != null  and shijStatus != ''"> and shij_status = #{shijStatus}</if>
            <if test="storeId != null"> and store_id = #{storeId}</if>
            <if test="address != null  and address != ''"> and address like concat('%', #{address}, '%')</if>
            <if test="longitude != null"> and longitude = #{longitude}</if>
            <if test="latitude != null"> and latitude = #{latitude}</if>
        </where>
        order by user_id desc
    </select>

    <select id="selectInfoUserByUserId" parameterType="Long" resultMap="InfoUserResult">
        <include refid="selectInfoUserVo"/>
        where user_id = #{userId}
    </select>

    <insert id="insertInfoUser" parameterType="InfoUser" useGeneratedKeys="true" keyProperty="userId">
        insert into info_user
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="userName != null and userName != ''">user_name,</if>
            <if test="phone != null and phone != ''">phone,</if>
            <if test="nickName != null and nickName != ''">nick_name,</if>
            <if test="userType != null and userType != ''">user_type,</if>
            <if test="email != null">email,</if>
            <if test="sex != null">sex,</if>
            <if test="avatar != null and avatar != ''">avatar,</if>
            <if test="password != null">password,</if>
            <if test="status != null and status != ''">status,</if>
            <if test="delFlag != null">del_flag,</if>
            <if test="cid != null">cid,</if>
            <if test="mycode != null">mycode,</if>
            <if test="thiscode != null">thiscode,</if>
            <if test="commission != null">commission,</if>
            <if test="annex != null">annex,</if>
            <if test="auditStatus != null">audit_status,</if>
            <if test="telPhone != null">tel_phone,</if>
            <if test="shijStatus != null">shij_status,</if>
            <if test="driversLicense != null">drivers_license,</if>
            <if test="drivingLicense != null">driving_license,</if>
            <if test="vehiclePhotos != null">vehicle_photos,</if>
            <if test="passportPhoto != null">passport_photo,</if>
            <if test="bankPhoto != null">bank_photo,</if>
            <if test="merchantIntroduction != null">merchant_introduction,</if>
            <if test="offline != null">offline,</if>
            <if test="storeId != null">store_id,</if>
            <if test="address != null and address != ''">address,</if>
            <if test="longitude != null">longitude,</if>
            <if test="latitude != null">latitude</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="userName != null and userName != ''">#{userName},</if>
            <if test="phone != null and phone != ''">#{phone},</if>
            <if test="nickName != null and nickName != ''">#{nickName},</if>
            <if test="userType != null and userType != ''">#{userType},</if>
            <if test="email != null">#{email},</if>
            <if test="sex != null">#{sex},</if>
            <if test="avatar != null and avatar != ''">#{avatar},</if>
            <if test="password != null">#{password},</if>
            <if test="status != null and status != ''">#{status},</if>
            <if test="delFlag != null">#{delFlag},</if>
            <if test="cid != null">#{cid},</if>
            <if test="mycode != null">#{mycode},</if>
            <if test="thiscode != null">#{thiscode},</if>
            <if test="commission != null">#{commission},</if>
            <if test="annex != null">#{annex},</if>
            <if test="auditStatus != null">#{auditStatus},</if>
            <if test="telPhone != null">#{telPhone},</if>
            <if test="shijStatus != null">#{shijStatus},</if>
            <if test="driversLicense != null">#{driversLicense},</if>
            <if test="drivingLicense != null">#{drivingLicense},</if>
            <if test="vehiclePhotos != null">#{vehiclePhotos},</if>
            <if test="passportPhoto != null">#{passportPhoto},</if>
            <if test="bankPhoto != null">#{bankPhoto},</if>
            <if test="merchantIntroduction != null">#{merchantIntroduction},</if>
            <if test="offline !=null">#{offline},</if>
            <if test="storeId != null">#{storeId},</if>
            <if test="address != null and address != ''">#{address},</if>
            <if test="longitude != null">#{longitude},</if>
            <if test="latitude != null">#{latitude}</if>
         </trim>
    </insert>

    <update id="updateInfoUser" parameterType="InfoUser">
        update info_user
        <trim prefix="SET" suffixOverrides=",">
            <if test="userName != null and userName != ''">user_name = #{userName},</if>
            <if test="phone != null and phone != ''">phone = #{phone},</if>
            <if test="nickName != null and nickName != ''">nick_name = #{nickName},</if>
            <if test="userType != null and userType != ''">user_type = #{userType},</if>
            <if test="email != null">email = #{email},</if>
            <if test="sex != null">sex = #{sex},</if>
            <if test="avatar != null and avatar != ''">avatar = #{avatar},</if>
            <if test="password != null">password = #{password},</if>
            <if test="status != null and status != ''">status = #{status},</if>
            <if test="delFlag != null">del_flag = #{delFlag},</if>
            <if test="cid != null">cid = #{cid},</if>
            <if test="mycode != null">mycode = #{mycode},</if>
            <if test="thiscode != null">thiscode = #{thiscode},</if>
            <if test="commission != null">commission = #{commission},</if>
            <if test="annex != null">annex = #{annex},</if>
            <if test="auditStatus != null">audit_status = #{auditStatus},</if>
            <if test="telPhone != null">tel_phone = #{telPhone},</if>
            <if test="shijStatus != null">shij_status = #{shijStatus},</if>
            <if test="driversLicense != null">drivers_license = #{driversLicense},</if>
            <if test="drivingLicense != null">driving_license = #{drivingLicense},</if>
            <if test="vehiclePhotos != null">vehicle_photos = #{vehiclePhotos},</if>
            <if test="passportPhoto != null">passport_photo = #{passportPhoto},</if>
            <if test="bankPhoto != null">bank_photo = #{bankPhoto},</if>
            <if test="merchantIntroduction != null">merchant_introduction = #{merchantIntroduction},</if>
            <if test="offline != null">offline =#{offline},</if>
            <if test="storeId != null">store_id = #{storeId},</if>
            <if test="address != null and address != ''">address = #{address},</if>
            <if test="longitude != null">longitude = #{longitude},</if>
            <if test="latitude != null">latitude = #{latitude}</if>
        </trim>
        where user_id = #{userId}
    </update>

    <update id="deleteInfoUserByUserId" parameterType="Long">
        update info_user SET del_flag=1 where user_id = #{userId}
    </update>

    <update id="deleteInfoUserByUserIds" parameterType="String">
        update info_user SET del_flag=1 where user_id in
        <foreach item="userId" collection="array" open="(" separator="," close=")">
            #{userId}
        </foreach>
    </update>

    <select id="countUsersByUserTypeAndCreatedAt" resultType="int">
        SELECT COUNT(*)
        FROM info_user
        <where>
            user_type = #{userType}
            AND status = 0
            AND del_flag = 0
            <if test="createdAt != null">
                AND created_at &gt; #{createdAt}
            </if>
        </where>
    </select>
    <select id="getUserSum" resultType="int" >
        SELECT IFNULL(COUNT(*),0)  FROM info_user
        <where>
            user_type=#{userType}
            <if test="startDate != null and startDate != '' and endDate != null and endDate != ''">
                 and created_at between #{startDate} and #{endDate}
           </if>
            <if test="startDate != null and startDate != '' and (endDate == null or endDate == '')">
                and created_at >= #{startDate}
            </if>
            <if test="(startDate == null or startDate == '') and (endDate != null and endDate != '')">
                and created_at &lt;= #{endDate}
            </if>
        </where>
    </select>

    <select id="selectStallOwnersByStoreId" parameterType="Long" resultMap="InfoUserResult">
        <include refid="selectInfoUserVo"/>
        where store_id = #{storeId} and user_type = '4' and del_flag = '0'
        order by user_id desc
    </select>

    <select id="selectNightMarketListByDistance" resultMap="InfoUserResult">
        SELECT *, (st_distance(point(longitude,latitude),point(#{longitude},#{latitude}))*111195/1000) as juli
        FROM info_user
        WHERE user_type = '3' AND audit_status = '1' AND del_flag = '0'
        ORDER BY juli ASC
    </select>

</mapper>
