<?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="cn.pluss.platform.mapper.UserInfoMapper">

    <insert id="saveUserInfo" parameterType="cn.pluss.platform.entity.UserInfo" keyProperty="id"
            useGeneratedKeys="true">
        insert into tb_pluss_user_info(loginName, trueName, provinceCode, cityCode, sex, phone, email, status, password,
                                       creator, createTime, updator, updateTime, version, parentId, areaId, areaCode,
                                       source)
        VALUES (#{loginName}, #{trueName}, #{provinceCode}, #{cityCode}, #{sex}, #{phone}, #{email}, #{status},
                #{password}, #{creator}, #{createTime}, #{updator}, #{updateTime}, #{version}, #{parentId}, #{areaId},
                #{areaCode}, #{source})
    </insert>

    <insert id="saveUserInfoBatch">
        insert into
        tb_pluss_user_info(loginName,trueName,provinceCode,cityCode,sex,phone,email,status,password,creator,createTime,updator,updateTime,version,parentId,areaId,areaCode,source)
        VALUES
        <foreach collection="list" item="item" index="index" separator=",">
            (#{loginName},#{trueName},#{provinceCode},#{cityCode},#{sex},#{phone},#{email},#{status},#{password},#{creator},#{createTime},#{updator},#{updateTime},#{version},#{parentId},#{areaId},#{areaCode},#{source}
            )
        </foreach>
    </insert>

    <select id="queryUserInfo" parameterType="cn.pluss.platform.entity.UserInfo"
            resultType="cn.pluss.platform.entity.UserInfo">
        SELECT * from tb_pluss_user_info
        <where>
            <if test="id!= null">
                and id = #{id}
            </if>
            <if test="loginName!= null">
                and loginName = #{loginName}
            </if>
            <if test="trueName!= null">
                and trueName = #{trueName}
            </if>
            <if test="provinceCode!= null">
                and provinceCode = #{provinceCode}
            </if>
            <if test="cityCode!= null">
                and cityCode = #{cityCode}
            </if>
            <if test="sex!= null">
                and sex = #{sex}
            </if>
            <if test="phone!= null">
                and phone = #{phone}
            </if>
            <if test="email!= null">
                and email = #{email}
            </if>
            <if test="status!= null">
                and status = #{status}
            </if>
            <if test="password!= null">
                and password = #{password}
            </if>
            <if test="creator!= null">
                and creator = #{creator}
            </if>
            <if test="createTime!= null">
                and createTime = #{createTime}
            </if>
            <if test="updator!= null">
                and updator = #{updator}
            </if>
            <if test="updateTime!= null">
                and updateTime = #{updateTime}
            </if>
            <if test="version!= null">
                and version = #{version}
            </if>
            <if test="parentId!= null">
                and parentId = #{parentId}
            </if>
            <if test="areaId!= null">
                and areaId = #{areaId}
            </if>
            <if test="areaCode!= null">
                and areaCode = #{areaCode}
            </if>
            <if test="source!= null">
                and source = #{source}
            </if>
        </where>
    </select>

    <select id="queryUserInfoList" parameterType="cn.pluss.platform.entity.UserInfo"
            resultType="cn.pluss.platform.entity.UserInfo">
        SELECT * from tb_pluss_user_info
        <where>
            <if test="id!= null">
                and id = #{id}
            </if>
            <if test="loginName!= null">
                and loginName = #{loginName}
            </if>
            <if test="trueName!= null">
                and trueName = #{trueName}
            </if>
            <if test="provinceCode!= null">
                and provinceCode = #{provinceCode}
            </if>
            <if test="cityCode!= null">
                and cityCode = #{cityCode}
            </if>
            <if test="sex!= null">
                and sex = #{sex}
            </if>
            <if test="phone!= null">
                and phone = #{phone}
            </if>
            <if test="email!= null">
                and email = #{email}
            </if>
            <if test="status!= null">
                and status = #{status}
            </if>
            <if test="password!= null">
                and password = #{password}
            </if>
            <if test="creator!= null">
                and creator = #{creator}
            </if>
            <if test="createTime!= null">
                and createTime = #{createTime}
            </if>
            <if test="updator!= null">
                and updator = #{updator}
            </if>
            <if test="updateTime!= null">
                and updateTime = #{updateTime}
            </if>
            <if test="version!= null">
                and version = #{version}
            </if>
            <if test="parentId!= null">
                and parentId = #{parentId}
            </if>
            <if test="areaId!= null">
                and areaId = #{areaId}
            </if>
            <if test="areaCode!= null">
                and areaCode = #{areaCode}
            </if>
            <if test="source!= null">
                and source = #{source}
            </if>
        </where>
        order by id desc
    </select>

    <update id="updateUserInfo" parameterType="cn.pluss.platform.entity.UserInfo">
        update tb_pluss_user_info
        <set>
            <if test="loginName!= null">
                loginName = #{loginName},
            </if>
            <if test="trueName!= null">
                trueName = #{trueName},
            </if>
            <if test="provinceCode!= null">
                provinceCode = #{provinceCode},
            </if>
            <if test="cityCode!= null">
                cityCode = #{cityCode},
            </if>
            <if test="sex!= null">
                sex = #{sex},
            </if>
            <if test="phone!= null">
                phone = #{phone},
            </if>
            <if test="email!= null">
                email = #{email},
            </if>
            <if test="status!= null">
                status = #{status},
            </if>
            <if test="password!= null">
                password = #{password},
            </if>
            <if test="creator!= null">
                creator = #{creator},
            </if>
            <if test="createTime!= null">
                createTime = #{createTime},
            </if>
            <if test="updator!= null">
                updator = #{updator},
            </if>
            <if test="updateTime!= null">
                updateTime = #{updateTime},
            </if>
            <if test="version!= null">
                version = #{version},
            </if>
            <if test="parentId!= null">
                parentId = #{parentId},
            </if>
            <if test="areaId!= null">
                areaId = #{areaId},
            </if>
            <if test="areaCode!= null">
                areaCode = #{areaCode},
            </if>
            <if test="source!= null">
                source = #{source}
            </if>
        </set>
        where id=#{id}
    </update>

    <delete id="deleteUserInfo" parameterType="cn.pluss.platform.entity.UserInfo">
        DELETE
        FROM tb_pluss_user_info
        where id = #{id}
    </delete>

    <select id="queryUserInfoPage" parameterType="java.util.Map" resultType="cn.pluss.platform.entity.UserInfo">
        SELECT * from tb_pluss_user_info
        <where>
            <if test="id!= null">
                and id = #{id}
            </if>
            <if test="loginName!= null">
                and loginName like CONCAT("%",#{loginName},"%")
            </if>
            <if test="trueName!= null">
                and trueName = #{trueName}
            </if>
            <if test="provinceCode!= null">
                and provinceCode = #{provinceCode}
            </if>
            <if test="cityCode!= null">
                and cityCode = #{cityCode}
            </if>
            <if test="sex!= null">
                and sex = #{sex}
            </if>
            <if test="phone!= null">
                and phone = #{phone}
            </if>
            <if test="email!= null">
                and email = #{email}
            </if>
            <if test="status!= null">
                and status = #{status}
            </if>
            <if test="password!= null">
                and password = #{password}
            </if>
            <if test="creator!= null">
                and creator = #{creator}
            </if>
            <if test="createTime!= null">
                and createTime = #{createTime}
            </if>
            <if test="updator!= null">
                and updator = #{updator}
            </if>
            <if test="updateTime!= null">
                and updateTime = #{updateTime}
            </if>
            <if test="version!= null">
                and version = #{version}
            </if>
            <if test="parentId!= null">
                and parentId = #{parentId}
            </if>
            <if test="areaId!= null">
                and areaId = #{areaId}
            </if>
            <if test="areaCode!= null">
                and areaCode = #{areaCode}
            </if>
            <if test="source!= null">
                and source = #{source}
            </if>
        </where>
        order by id desc limit #{pageSize} offset #{offset}
    </select>

    <select id="queryUserInfoVOPage" parameterType="java.util.Map" resultType="cn.pluss.platform.vo.UserInfoVO">
        select ui.*,uo.organCode,uo.organName,uo.organTypeCode,uo.organTypeName from tb_pluss_user_info ui LEFT JOIN
        tb_pluss_user_organ uo ON uo.userId=ui.id
        <where>
            <if test="id!= null and id!=''">
                and ui.id = #{id}
            </if>
            <if test="loginName!= null and loginName!=''">
                and loginName = #{loginName}
            </if>
            <if test="trueName!= null and trueName!=''">
                and trueName like CONCAT("%",#{trueName},"%")
            </if>
            <if test="provinceCode!= null and provinceCode!=''">
                and provinceCode = #{provinceCode}
            </if>
            <if test="cityCode!= null and cityCode!=''">
                and cityCode = #{cityCode}
            </if>
            <if test="sex!= null and sex!=''">
                and sex = #{sex}
            </if>
            <if test="phone!= null and phone!=''">
                and phone LIKE CONCAT("%",#{phone},"%")
            </if>
            <if test="email!= null and email!=''">
                and email = #{email}
            </if>
            <if test="status!= null and status!=''">
                and status = #{status}
            </if>
            <if test="password!= null and password!=''">
                and password = #{password}
            </if>
            <if test="creator!= null and creator!=''">
                and creator = #{creator}
            </if>
            <if test="createTime!= null and createTime!=''">
                and createTime = #{createTime}
            </if>
            <if test="updator!= null and updator!=''">
                and updator = #{updator}
            </if>
            <if test="updateTime!= null and updateTime!=''">
                and updateTime = #{updateTime}
            </if>
            <if test="version!= null and version!=''">
                and version = #{version}
            </if>
            <if test="parentId!= null and parentId!=''">
                and parentId = #{parentId}
            </if>
            <if test="areaId!= null and areaId!=''">
                and areaId = #{areaId}
            </if>
            <if test="areaCode!= null and loginName!=''">
                and areaCode = #{areaCode}
            </if>
            <if test="source!= null and source!=''">
                and source = #{source}
            </if>
            <if test="organCode!= null and organCode!=''">
                and organCode = #{organCode}
            </if>
            <if test="organName!= null and organName!=''">
                and organName = #{organName}
            </if>
            <if test="organTypeCode!= null and organTypeCode!=''">
                and organTypeCode = #{organTypeCode}
            </if>
            <if test="organTypeName!= null and organTypeName!=''">
                and organTypeName = #{organTypeName}
            </if>
        </where>
        order by ui.id DESC limit #{pageSize} offset #{offset}
    </select>

    <select id="queryUserInfoVOPageCount" parameterType="java.util.Map" resultType="java.lang.Integer">
        select count(*) FROM (select ui.*,uo.organCode,uo.organName,uo.organTypeCode,uo.organTypeName from
        tb_pluss_user_info ui LEFT JOIN tb_pluss_user_organ uo ON uo.userId=ui.id
        <where>
            <if test="id!= null and id!=''">
                and ui.id = #{id}
            </if>
            <if test="loginName!= null and loginName!=''">
                and loginName = #{loginName}
            </if>
            <if test="trueName!= null and trueName!=''">
                and trueName like CONCAT("%",#{trueName},"%")
            </if>
            <if test="provinceCode!= null and provinceCode!=''">
                and provinceCode = #{provinceCode}
            </if>
            <if test="cityCode!= null and cityCode!=''">
                and cityCode = #{cityCode}
            </if>
            <if test="sex!= null and sex!=''">
                and sex = #{sex}
            </if>
            <if test="phone!= null and phone!=''">
                and phone LIKE CONCAT("%",#{phone},"%")
            </if>
            <if test="email!= null and email!=''">
                and email = #{email}
            </if>
            <if test="status!= null and status!=''">
                and status = #{status}
            </if>
            <if test="password!= null and password!=''">
                and password = #{password}
            </if>
            <if test="creator!= null and creator!=''">
                and creator = #{creator}
            </if>
            <if test="createTime!= null and createTime!=''">
                and createTime = #{createTime}
            </if>
            <if test="updator!= null and updator!=''">
                and updator = #{updator}
            </if>
            <if test="updateTime!= null and updateTime!=''">
                and updateTime = #{updateTime}
            </if>
            <if test="version!= null and version!=''">
                and version = #{version}
            </if>
            <if test="parentId!= null and parentId!=''">
                and parentId = #{parentId}
            </if>
            <if test="areaId!= null and areaId!=''">
                and areaId = #{areaId}
            </if>
            <if test="areaCode!= null and loginName!=''">
                and areaCode = #{areaCode}
            </if>
            <if test="source!= null and source!=''">
                and source = #{source}
            </if>
            <if test="organCode!= null and organCode!=''">
                and organCode = #{organCode}
            </if>
            <if test="organName!= null and organName!=''">
                and organName = #{organName}
            </if>
            <if test="organTypeCode!= null and organTypeCode!=''">
                and organTypeCode = #{organTypeCode}
            </if>
            <if test="organTypeName!= null and organTypeName!=''">
                and organTypeName = #{organTypeName}
            </if>
        </where>
        )u
    </select>


    <select id="queryUserInfoPageCount" parameterType="java.util.Map" resultType="java.lang.Integer">
        SELECT count(*) from tb_pluss_user_info
        <where>
            <if test="id!= null">
                and id = #{id}
            </if>
            <if test="loginName!= null">
                and loginName = #{loginName}
            </if>
            <if test="trueName!= null">
                and trueName = #{trueName}
            </if>
            <if test="provinceCode!= null">
                and provinceCode = #{provinceCode}
            </if>
            <if test="cityCode!= null">
                and cityCode = #{cityCode}
            </if>
            <if test="sex!= null">
                and sex = #{sex}
            </if>
            <if test="phone!= null">
                and phone = #{phone}
            </if>
            <if test="email!= null">
                and email = #{email}
            </if>
            <if test="status!= null">
                and status = #{status}
            </if>
            <if test="password!= null">
                and password = #{password}
            </if>
            <if test="creator!= null">
                and creator = #{creator}
            </if>
            <if test="createTime!= null">
                and createTime = #{createTime}
            </if>
            <if test="updator!= null">
                and updator = #{updator}
            </if>
            <if test="updateTime!= null">
                and updateTime = #{updateTime}
            </if>
            <if test="version!= null">
                and version = #{version}
            </if>
            <if test="parentId!= null">
                and parentId = #{parentId}
            </if>
            <if test="areaId!= null">
                and areaId = #{areaId}
            </if>
            <if test="areaCode!= null">
                and areaCode = #{areaCode}
            </if>
            <if test="source!= null">
                and source = #{source}
            </if>
        </where>
    </select>


    <select id="queryUserInfoListLikeByPhone" parameterType="cn.pluss.platform.entity.UserInfo"
            resultType="cn.pluss.platform.entity.UserInfo">
        SELECT * from tb_pluss_user_info
        <where>
            <if test="id!= null">
                and id = #{id}
            </if>
            <if test="loginName!= null">
                and loginName = #{loginName}
            </if>
            <if test="trueName!= null">
                and trueName = #{trueName}
            </if>
            <if test="provinceCode!= null">
                and provinceCode = #{provinceCode}
            </if>
            <if test="cityCode!= null">
                and cityCode = #{cityCode}
            </if>
            <if test="sex!= null">
                and sex = #{sex}
            </if>
            <if test="phone!= null">
                and phone Like CONCAT(#{phone},'%')
            </if>
            <if test="email!= null">
                and email = #{email}
            </if>
            <if test="status!= null">
                and status = #{status}
            </if>
            <if test="password!= null">
                and password = #{password}
            </if>
            <if test="creator!= null">
                and creator = #{creator}
            </if>
            <if test="createTime!= null">
                and createTime = #{createTime}
            </if>
            <if test="updator!= null">
                and updator = #{updator}
            </if>
            <if test="updateTime!= null">
                and updateTime = #{updateTime}
            </if>
            <if test="version!= null">
                and version = #{version}
            </if>
            <if test="parentId!= null">
                and parentId = #{parentId}
            </if>
            <if test="areaId!= null">
                and areaId = #{areaId}
            </if>
            <if test="areaCode!= null">
                and areaCode = #{areaCode}
            </if>
            <if test="source!= null">
                and source = #{source}
            </if>
        </where>
        order by id desc
    </select>

    <select id="selectMainUserInfo" resultType="cn.pluss.platform.entity.UserInfo">
        SELECT ui.*
        FROM tb_pluss_merchant_base_info mbi1
        LEFT JOIN tb_pluss_merchant_base_info mbi2 ON mbi1.bindingCode = mbi2.bindingCode
        LEFT JOIN tb_pluss_user_info ui ON mbi2.userId = ui.id
        WHERE mbi1.userId = #{id}
        AND LENGTH(ui.phone) &lt;= 11
    </select>

    <select id="selectMainUserInfoList" resultType="cn.pluss.platform.vo.UserInfoVO">
        SELECT ui.id,mbi2.alias 'alias',
               mbi2.isPushWxMessage as 'isPushWxMessage',
               mbi2.merchantCode as 'merchantCode'
        FROM tb_pluss_merchant_base_info mbi1
        LEFT JOIN tb_pluss_merchant_base_info mbi2 ON (mbi1.bindingCode = mbi2.bindingCode OR mbi1.userId = mbi2.userId)
        LEFT JOIN tb_pluss_user_info ui ON ui.id = mbi2.userId
        WHERE mbi1.userId = #{id}
        order by mbi2.createDt asc
    </select>

</mapper>
