<?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.liquidnet.service.smile.mapper.SmileUserMapper">

    <resultMap id="smileUserCheckDao" type="com.liquidnet.service.smile.entity.dao.SmileUserCheckDao">
        <result column="id" property="id"/>
        <result column="uid" property="uid"/>
        <result column="img" property="img"/>
        <result column="name" property="name"/>
        <result column="sex" property="sex"/>
        <result column="birthday" property="birthday"/>
        <result column="phone" property="phone"/>
        <result column="identity" property="identity"/>
        <result column="school_name" property="schoolName"/>
        <result column="province_id" property="provinceId"/>
        <result column="province" property="province"/>
        <result column="city_id" property="cityId"/>
        <result column="city" property="city"/>
        <result column="area_id" property="areaId"/>
        <result column="area" property="area"/>
        <result column="address" property="address"/>
        <result column="type" property="type"/>
        <result column="error_reason" property="reason"/>
    </resultMap>

    <select id="strip" resultType="Integer">
        select count(*)
        from smile_user
        where org_id = #{orgId}
          AND del_tag = 0;
    </select>

    <select id="selectUserNameById" resultType="String">
        select name
        from smile_user
        where id = #{id};
    </select>

    <update id="updateUserSchoolBySchoolId">
        update smile_user
        set school_name = #{schoolName}
        where school_id = #{schoolId};
    </update>

    <select id="selectUserList" parameterType="java.util.Map" resultType="com.liquidnet.service.smile.entity.SmileUser">
        select * from smile_user
        <where>
            del_tag = 0 AND type != 0 AND type != 3 AND type != 4
            <if test="phone!='' and phone !=null">
                AND phone Like concat('%',#{phone},'%')
            </if>
            <if test="type!=null and type != ''">
                AND `type` = #{type}
            </if>
            <if test="sex!=null and sex != ''">
                AND sex = #{sex}
            </if>
            <if test="identity!='' and identity!=null">
                AND `identity` = #{identity}
            </if>
            <if test="schoolId!='' and schoolId!=null">
                AND school_id = #{schoolId}
            </if>
        </where>
        ORDER BY id desc
    </select>
    <select id="pageUser" parameterType="int" resultType="com.liquidnet.service.smile.entity.SmileUser">
        select * from smile_user
        <where>
            del_tag = 0 AND type != 0 AND type != 3 AND type != 4
        </where>
        limit #{offest},#{size}
    </select>
    <select id="getCount" resultType="int">
        select count(1) from smile_user
        <where>
            del_tag = 0 AND type != 0 AND type != 3 AND type != 4
        </where>
    </select>

    <select id="selectUserCheck" parameterType="java.util.Map" resultMap="smileUserCheckDao">
        SELECT
        id,
        uid,
        img,
        `NAME`,
        birthday,
        sex,
        phone,
        `identity`,
        school_name,
        province_id,
        province,
        city_id,
        city,
        area_id,
        area,
        address,
        type,
        error_reason
        FROM
        smile_user
        <where>
            del_tag = 0 AND
            `type` != 1 AND `type` != 2
            <if test="phone!=''">
                AND phone like concat('%',#{phone},'%')
            </if>
        </where>
        Order by created_date desc
    </select>


    <select id="getUserOrder" parameterType="String" resultType="java.util.Map">
        select performance_id as 'yanchuId'
        from (select * from kylin_order_tickets where created_at > DATE_SUB(CURDATE(), INTERVAL 6 MONTH)) as o
           , kylin_order_ticket_status as s
           , kylin_order_ticket_relations as r
        where o.order_tickets_id = s.order_id
          and o.order_tickets_id = r.order_id
          and r.agent_id = #{userId}
          and s.pay_status = '1'
        GROUP BY r.performance_id
    </select>

    <update id="updateUserByUid">
        update smile_user
        set org_id   = #{orgId},
            type     = #{type},
            agent    = 1,
            agent_id = #{agentId},
            `state`  = 0
        where uid = #{uid}
    </update>

    <update id="updateUserStateById">
        update smile_user
        set `state` = #{state},
            where `uid` = #{uid}
    </update>

    <update id="updateUserCheckByUid">
        update smile_user
        set `type`       = #{type},
            error_reason = #{reason},
            `state`      = 0
        where uid = #{uid}
    </update>

    <select id="getAllUidByPerId" parameterType="java.util.Map"
            resultType="com.liquidnet.service.smile.entity.SmileUser">
        SELECT
            su.uid,
            su.`name`,
            su.`type`,
            su.province_id,
            su.province,
            su.city_id,
            su.city,
            su.area_id,
            su.area,
            su.address
        FROM
            smile_user AS su
                INNER JOIN ( SELECT mid, agent_id, agent_id_master FROM kylin_order_ticket_relations WHERE performance_id = #{performancesId} AND agent_id != 0 GROUP BY agent_id ) kk ON kk.agent_id = su.uid
                OR kk.agent_id_master = su.uid
        GROUP BY
            su.uid
    </select>

    <select id="selectUidByPerId" resultType="com.liquidnet.service.smile.entity.SmileUser">
        SELECT
            su.uid,
            su.name,
            su.type,
            su.city
        FROM
            smile_user AS su
                INNER JOIN ( SELECT kotr.agent_id, kotr.agent_id_master FROM kylin_order_ticket_relations AS kotr WHERE kotr.performance_id = #{performancesId} AND kotr.agent_id != 0 ) kk ON su.uid = kk.agent_id
                OR su.uid = kk.agent_id_master
        WHERE su.type = 1
        GROUP BY su.uid
    </select>

    <select id="selectUidByPerIdNotType" resultType="com.liquidnet.service.smile.entity.SmileUser">
        SELECT
            su.uid
        FROM
            smile_user AS su
                INNER JOIN ( SELECT kotr.agent_id, kotr.agent_id_master FROM kylin_order_ticket_relations AS kotr WHERE kotr.performance_id = #{performancesId} AND kotr.agent_id != 0 ) kk ON su.uid = kk.agent_id
                OR su.uid = kk.agent_id_master
        GROUP BY su.uid
    </select>


</mapper>
