<?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.xinyilu.system.mapper.parentInfo.ParentInfoMapper">

    <resultMap type="ParentInfo" id="ParentInfoResult">
        <result property="id" column="id"/>
        <result property="openid" column="openid"/>
        <result property="unionid" column="unionid"/>
        <result property="parentName" column="parent_name"/>
        <result property="parentSex" column="parent_sex"/>
        <result property="phone" column="phone"/>
        <result property="avatar" column="avatar"/>
        <result property="password" column="password"/>
        <result property="loginIp" column="login_ip"/>
        <result property="loginDate" column="login_date"/>
        <result property="status" column="status"/>
        <result property="delFlag" column="del_flag"/>
        <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="userId" column="user_id"/>
    </resultMap>

    <sql id="selectParentInfoVo">
        select id, openid, unionid, parent_name, parent_sex, phone, avatar, password, login_ip, login_date, status, del_flag, create_by, create_time, update_by, update_time, user_id from parent_info
    </sql>

    <select id="selectParentInfoList" parameterType="parentInfoVO" resultMap="ParentInfoResult">
        <include refid="selectParentInfoVo"/>
        <where>
            <if test="openid != null  and openid != ''">
                and openid = #{openid}
            </if>
            <if test="unionid != null  and unionid != ''">
                and unionid = #{unionid}
            </if>
            <if test="parentName != null  and parentName != ''">
                and parent_name like concat('%', #{parentName}, '%')
            </if>
            <if test="parentSex != null  and parentSex != ''">
                and parent_sex = #{parentSex}
            </if>
            <if test="phone != null  and phone != ''">
                and phone = #{phone}
            </if>
            <if test="avatar != null  and avatar != ''">
                and avatar = #{avatar}
            </if>
            <if test="password != null  and password != ''">
                and password = #{password}
            </if>
            <if test="loginIp != null  and loginIp != ''">
                and login_ip = #{loginIp}
            </if>
            <if test="loginDate != null ">
                and login_date = #{loginDate}
            </if>
            <if test="status != null  and status != ''">
                and status = #{status}
            </if>
            <if test="userId != null ">
                and user_id = #{userId}
            </if>
            and del_flag = 0
        </where>
        order by create_time desc
    </select>

    <select id="selectParentInfoById" parameterType="Long"
            resultMap="ParentInfoResult">
        <include refid="selectParentInfoVo"/>
        where id = #{id} and del_flag = 0
        order by create_time desc
    </select>

    <insert id="insertParentInfo" parameterType="ParentInfo" useGeneratedKeys="true"
            keyProperty="id">
        insert into parent_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="openid != null">openid,
            </if>
            <if test="unionid != null">unionid,
            </if>
            <if test="parentName != null">parent_name,
            </if>
            <if test="parentSex != null">parent_sex,
            </if>
            <if test="phone != null">phone,
            </if>
            <if test="avatar != null">avatar,
            </if>
            <if test="password != null">password,
            </if>
            <if test="loginIp != null">login_ip,
            </if>
            <if test="loginDate != null">login_date,
            </if>
            <if test="status != null">status,
            </if>
            <if test="delFlag != null">del_flag,
            </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="userId != null">user_id,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="openid != null">#{openid},
            </if>
            <if test="unionid != null">#{unionid},
            </if>
            <if test="parentName != null">#{parentName},
            </if>
            <if test="parentSex != null">#{parentSex},
            </if>
            <if test="phone != null">#{phone},
            </if>
            <if test="avatar != null">#{avatar},
            </if>
            <if test="password != null">#{password},
            </if>
            <if test="loginIp != null">#{loginIp},
            </if>
            <if test="loginDate != null">#{loginDate},
            </if>
            <if test="status != null">#{status},
            </if>
            <if test="delFlag != null">#{delFlag},
            </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="userId != null">#{userId},
            </if>
        </trim>
    </insert>

    <update id="updateParentInfo" parameterType="ParentInfo">
        update parent_info
        <trim prefix="SET" suffixOverrides=",">
            <if test="openid != null">openid =
                #{openid},
            </if>
            <if test="unionid != null">unionid =
                #{unionid},
            </if>
            <if test="parentName != null">parent_name =
                #{parentName},
            </if>
            <if test="parentSex != null">parent_sex =
                #{parentSex},
            </if>
            <if test="phone != null">phone =
                #{phone},
            </if>
            <if test="avatar != null">avatar =
                #{avatar},
            </if>
            <if test="password != null">password =
                #{password},
            </if>
            <if test="loginIp != null">login_ip =
                #{loginIp},
            </if>
            <if test="loginDate != null">login_date =
                #{loginDate},
            </if>
            <if test="status != null">status =
                #{status},
            </if>
            <if test="delFlag != null">del_flag =
                #{delFlag},
            </if>
            <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="userId != null">user_id =
                #{userId},
            </if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteParentInfoById" parameterType="Long">
        delete from parent_info where id = #{id}
    </delete>

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

    <update id="updateParentInfoByIds" parameterType="java.lang.Long">
        update parent_info set del_flag = 1 where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>

    <select id="selectRoleParentInfoList" parameterType="parentInfoVO" resultMap="ParentInfoResult">
        SELECT
        pi.id,
        pi.openid,
        pi.unionid,
        pi.parent_name,
        pi.parent_sex,
        pi.phone,
        pi.avatar,
        pi.`password`,
        pi.login_ip,
        pi.login_date,
        pi.`status`,
        pi.del_flag,
        pi.create_by,
        pi.create_time,
        pi.update_by,
        pi.update_time,
        pi.user_id
        FROM
        parent_info pi
        LEFT JOIN parent_student_info psi ON pi.id = psi.parent_id
        LEFT JOIN student_info si ON psi.student_id = si.id
        LEFT JOIN class_info ci ON si.class_id = ci.id
        <where>
            <if test="openid != null  and openid != ''">
                and pi.openid = #{openid}
            </if>
            <if test="unionid != null  and unionid != ''">
                and pi.unionid = #{unionid}
            </if>
            <if test="parentName != null  and parentName != ''">
                and pi.parent_name like concat('%', #{parentName}, '%')
            </if>
            <if test="parentSex != null  and parentSex != ''">
                and pi.parent_sex = #{parentSex}
            </if>
            <if test="phone != null  and phone != ''">
                and pi.phone = #{phone}
            </if>
            <if test="avatar != null  and avatar != ''">
                and pi.avatar = #{avatar}
            </if>
            <if test="password != null  and password != ''">
                and pi.password = #{password}
            </if>
            <if test="loginIp != null  and loginIp != ''">
                and pi.login_ip = #{loginIp}
            </if>
            <if test="loginDate != null ">
                and pi.login_date = #{loginDate}
            </if>
            <if test="status != null  and status != ''">
                and pi.status = #{status}
            </if>
            <if test="kindergartenIdList != null and kindergartenIdList.size() > 0">
                and ci.kindergarten_id in
                <foreach collection="kindergartenIdList" item="kindergartenId" open="(" separator="," close=")">
                    #{kindergartenId}
                </foreach>
            </if>
            <if test="kindergartenId != null">
                and ci.kindergarten_id = #{kindergartenId}
            </if>
            <if test="classIdList != null and classIdList.size() > 0">
                and ci.id in
                <foreach collection="classIdList" item="classId" open="(" separator="," close=")">
                    #{classId}
                </foreach>
            </if>
            <if test="userId != null ">
                or pi.user_id = #{userId}
            </if>
            <if test="phoneNumber != null  and phoneNumber != ''">
                or pi.phone = #{phoneNumber}
            </if>
            and pi.del_flag = 0
        </where>
        group by pi.id
        order by pi.create_time desc
    </select>

    <select id="selectParentPhoneInfo" parameterType="parentInfo" resultType="int">
        select count(1) as phone from parent_info
        <where>
            <if test="phone != null  and phone != ''">
                and phone = #{phone}
            </if>
           AND del_flag = 0
        </where>
    </select>
</mapper>
