<?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 in (1,2,5) and state = 0
            <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>
    </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 and type!=5
        </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 and type!=5
        </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 AND `type` != 5
            <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},
            <if test="reason!=null and reason != ''">
                error_reason = #{reason},
            </if>
            updated_date=NOW()
        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,
               su.phone,
               su.agent_id,
               su.id_card,
               su.state
        FROM smile_user AS su
                 INNER JOIN (SELECT kotr.agent_id, kotr.agent_id_master
                             FROM kylin_order_ticket_relations AS kotr
                                      INNER JOIN kylin_order_ticket_status AS kots ON kots.order_id = kotr.order_id
                             WHERE kotr.performance_id = #{performancesId}
                               AND kotr.agent_id != 0 AND kots.status IN (1)) kk ON su.uid = kk.agent_id_master
        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>


    <select id="selectPriceIdPhone" resultType="com.liquidnet.service.smile.entity.dto.SmilePriceIdPhoneDao">
        SELECT
        a.*,b.id_card,b.phone,c.name
        FROM
            smile_price as a
        inner join smile_user as b  on a.uid = b.uid
        inner join smile_user as c on b.agent_id = c.uid
        WHERE
        performances_id = #{performanceId}
        AND a.uid IS NOT NULL
        AND a.del_tag = 0
    </select>

</mapper>
