<?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.winsun.mapper.HhrUserMapper">
    <update id="updateByIcCard">
        update hhr_user set account = #{account} where id_card = #{idCard}
    </update>

    <select id="statisticAllOrderNum" resultType="java.util.HashMap">
        SELECT
           *
        FROM
            (
            SELECT
            count( * ) AS today
            FROM
                hhr_order
            <where>
                <if test="userIds != null and userIds.size() > 0 ">
                    hehuoren_id IN
                    <foreach collection="userIds" item="userId" separator="," open="(" close=")">
                        #{userId}
                    </foreach>
                </if>
                AND create_time &gt;= #{today}
                <if test="isComplete == true">
                    AND order_status = "已完成"
                </if>
            </where>
            ) a,
               (
               SELECT
               count( * ) AS toweek
               FROM
               hhr_order
                <where>
                    <if test="userIds != null and userIds.size() > 0 ">
                        hehuoren_id IN
                        <foreach collection="userIds" item="userId" separator="," open="(" close=")">
                            #{userId}
                        </foreach>
                    </if>

                    AND create_time &gt;= #{toWeekStart}
                    AND create_time &lt;= #{toWeekEnd}
                    <if test="isComplete == true">
                        AND order_status = "已完成"
                    </if>
                </where>
            ) b,
            (
            SELECT
                count( * ) AS tomonth
            FROM
                hhr_order
            <where>
                <if test="userIds != null and userIds.size() > 0 ">
                    hehuoren_id IN
                    <foreach collection="userIds" item="userId" separator="," open="(" close=")">
                        #{userId}
                    </foreach>
                </if>
                AND create_time &gt;= #{toMonthStart}
                AND create_time &lt;= #{toMonthEnd}
                <if test="isComplete == true">
                    AND order_status = "已完成"
                </if>
            </where>
            ) c
    </select>

    <select id="getProductByUserId" parameterType="String" resultType="HashMap">
        select
            hs.jilt_kdy, hs.school_name,hs.bandproduct_id
        from
            hhr_user_school hus
            left join
            hhr_school hs on hus.school_id = hs.id
        where user_id = #{hehuorenId}
    </select>

    <select id="selectSupervisor" parameterType="String" resultType="HashMap">
        select uuu.name ddname,sch.lzgh
                from hhr_user u
                left join hhr_user_school us on u.id = us.user_id
                left join hhr_school sch on sch.id = us.school_id
                left join hhr_supervisor_school ss on us.school_id = ss.school
                left join sys_user uuu on uuu.id = ss.user_id
                where u.id= #{hehuorenId}
    </select>

    <select id="selectHehuorenInfo" parameterType="String" resultType="HashMap">
        SELECT
            su.phone,
            su.name,
            hs.sub_name,
            hs.school_name
        FROM
            sys_user su
            LEFT JOIN hhr_user hu ON su.id = hu.id
            LEFT JOIN hhr_user_school hus ON su.id = hus.user_id
            LEFT JOIN hhr_school hs ON hus.school_id = hs.id
        WHERE
            su.id = #{hehuorenId}
    </select>

    <select id="selectHehuorenSchool" parameterType="String" resultType="HashMap">
        select
            hs.*
        from
            hhr_user_school hus
        LEFT JOIN
            hhr_school hs
        ON
            hus.school_id = hs.id
        WHERE
            hus.user_id = #{userId}
    </select>
    <select id="selectByIcCard" resultType="com.winsun.bean.HhrUser">
        select * from hhr_user where id_card = #{idCard}
    </select>
    <select id="selectByUser" resultType="java.util.Map">
        select hu.account as account, hu.id_card idCard, su.name as name from hhr_user hu join sys_user su on hu.id = su.id where su.name = #{name}
    </select>
</mapper>