<?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.ylx.massage.mapper.TJsMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.ylx.massage.domain.TJs">
        <id column="id" property="id"/>
        <id column="c_open_id" property="cOpenId"/>
        <result column="c_bh_list" property="cBhList"/>
        <result column="c_img_list" property="cImgList"
                typeHandler="com.baomidou.mybatisplus.extension.handlers.FastjsonTypeHandler"/>
        <result column="c_sfz_img" property="cSfzImg"
                typeHandler="com.baomidou.mybatisplus.extension.handlers.FastjsonTypeHandler"/>
        <result column="n_sex" property="nSex"/>
        <result column="c_nick_name" property="cNickName"/>
        <result column="c_portrait" property="cPortrait"/>
        <result column="c_name" property="cName"/>
        <result column="c_phone" property="cPhone"/>
        <result column="js_grade" property="jsGrade"/>
        <result column="c_address" property="cAddress"/>
        <result column="health_certificate" property="healthCertificate"/>
        <result column="business_license" property="businessLicense"/>
        <result column="certification" property="certification"/>
        <result column="no_crime" property="noCrime"/>
        <result column="daytime_mileage" property="daytimeMileage"/>
        <result column="nigth_mileage" property="nigthMileage"/>
        <result column="c_jianjie" property="cJianjie"/>
        <result column="n_star" property="nStar"/>
        <result column="n_bili" property="nBili"/>
        <result column="n_num" property="nNum"/>
        <result column="n_num2" property="nNum2"/>
        <result column="n_zan" property="nZan"/>
        <result column="n_status" property="nStatus"/>
        <result column="n_status2" property="nStatus2"/>
        <result column="n_tong" property="nTong"/>
        <result column="c_time" property="cTime"/>
        <result column="n_b1" property="nB1"/>
        <result column="n_b2" property="nB2"/>
        <result column="n_b3" property="nB3"/>
        <result column="name" property="name"/>
        <result column="address" property="address"/>
        <result column="city" property="city"/>
        <result column="dept_id" property="deptId"/>
        <result column="reason_refusal" property="reasonRefusal"/>

        <result column="dt_create_time" property="dtCreateTime"/>
        <result column="nNumber" property="number"/>
        <result column="service_area_codes" property="serviceAreaCodes"/>
        <result column="service_area_json" property="serviceAreaJson"/>

    </resultMap>

    <select id="getAll" resultMap="BaseResultMap">
        select
        js.id,
        js.c_open_id,
        js.c_bh_list,
        js.c_img_list,
        js.c_sfz_img,
        js.n_sex,
        js.c_nick_name,
        js.c_portrait,
        js.c_name,
        js.c_phone,
        js.c_address,
        js.nigth_mileage,
        js.daytime_mileage,
        js.health_certificate,
        js.business_license,
        js.c_jianjie,
        js.n_star,
        js.n_bili,
        js.n_num,
        js.n_zan,
        js.city,
        js.reason_refusal,
        js.js_grade,
        js.n_status,
        js.n_status2,
        js.n_tong,
        js.c_time,
        js.n_b1,
        js.n_b2,
        js.n_b3,
        js.dt_create_time,
        IFNULL(temp.nNumber,0) as nNumber,
        js.service_area_codes,
        js.service_area_json
        from t_js as js
        left join
        (
        select
        count(*) as nNumber,
        t_collect.c_js_id
        from
        t_collect where t_collect.is_delete = 0
        group by
        t_collect.c_js_id
        ) as temp
        on
        temp.c_js_id = js.id
        <where>
            js.is_delete = 0
            <if test="param.nameOrPhone != null and param.nameOrPhone != ''">
                and (
                js.c_nick_name like concat('%',#{param.nameOrPhone},'%')
                    or
                js.c_phone like concat('%',#{param.nameOrPhone},'%')
                )
            </if>
            <if test="param.cNickName != null and param.cNickName != ''">
                and js.c_nick_name like concat('%',#{param.cNickName},'%')
            </if>
            <if test="param.nSex != null">
                and js.n_sex = #{param.nSex}
            </if>
            <if test="param.city != null and param.city != ''">
                and js.city = #{param.city}
            </if>
            <if test="param.jsGrade != null">
                and js.js_grade = #{param.jsGrade}
            </if>
            <if test="param.nB2 != null">
                and js.n_b2 = #{param.nB2}
            </if>
            <if test="param.cOpenId != null and param.cOpenId != ''">
                and js.c_open_id = #{param.cOpenId}
            </if>

            <if test="param.nStatus2 != null">
                and js.n_status2 = #{param.nStatus2}
            </if>
            <if test="param.nStatus != null">
                and js.n_status = #{param.nStatus}
            </if>
            <if test="param.nTong != null">
                and js.n_tong = #{param.nTong}
            </if>
            <if test="param.projectId != null and param.projectId != ''">
                AND FIND_IN_SET(#{param.projectId}, js.c_bh_list )
            </if>
            <if test="param.free != null and param.free == 1">
                AND (daytime_mileage > 0 OR nigth_mileage > 0)
            </if>
            <if test="param.ids != null and param.ids.size() > 0">
                and js.c_phone in
                <foreach item="id" collection="param.ids" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
            <if test="param.recommendTechnicianList != null and param.recommendTechnicianList.size() > 0">
                and js.id in
                <foreach item="id" collection="param.recommendTechnicianList" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>

        </where>
        order by js.dt_create_time desc

<!--        <if test="param.suiji == null">-->
<!--            order by-->
<!--            dt_create_time desc-->
<!--        </if>-->
<!--        <if test="param.suiji != null">-->
<!--            order by-->
<!--            RAND() LIMIT #{ param.suiji }-->
<!--        </if>-->
    </select>


    <select id="getBlockGetJs" resultType="com.ylx.massage.domain.vo.HomeBlock">
        SELECT
            DATE_FORMAT( create_time, '%Y-%m' ) AS MONTH,
            count( 1 ) jsNum
        FROM
            t_js
        WHERE
            is_delete = 0
          AND n_tong = 1
        <if test="deptId != 100 and deptId != ''">
            AND dept_id = #{deptId}
        </if>
        GROUP BY
            MONTH
        ORDER BY
            MONTH
    </select>
    <select id="getBlockJs" resultType="java.lang.Integer">
        SELECT
            count( 1 ) jsNum
        FROM
            t_js
        WHERE
            is_delete = 0
          AND n_tong = 1
        <if test="deptId != 100 and deptId != ''">
            AND dept_id = #{deptId}
        </if>
    </select>

</mapper>
