<?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="org.linlinjava.jz.db.dao.HmEmployRelationMappers">
   <resultMap id="BaseResultMap" type="org.linlinjava.jz.db.domain.HmEmployRelation">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    <id column="id" jdbcType="BIGINT" property="id" />
    <result column="order_no" jdbcType="VARCHAR" property="orderNo" />
    <result column="user_servant_id" jdbcType="BIGINT" property="userServantId" />
    <result column="user_employer_id" jdbcType="BIGINT" property="userEmployerId" />
    <result column="user_butler_id" jdbcType="BIGINT" property="userButlerId" />
    <result column="servant_name" jdbcType="VARCHAR" property="servantName" />
    <result column="employer_name" jdbcType="VARCHAR" property="employerName" />
    <result column="butler_name" jdbcType="VARCHAR" property="butlerName" />
    <result column="butler_first_confirm" jdbcType="INTEGER" property="butlerFirstConfirm" />
    <result column="first_service_time" jdbcType="TIMESTAMP" property="firstServiceTime" />
    <result column="rest_mode" jdbcType="INTEGER" property="restMode" />
    <result column="rest_day" jdbcType="VARCHAR" property="restDay" />
    <result column="is_need_clock" jdbcType="INTEGER" property="isNeedClock" />
    <result column="work_scope" jdbcType="VARCHAR" property="workScope" />
    <result column="is_invalid" jdbcType="INTEGER" property="isInvalid" />
    <result column="relieve_time" jdbcType="TIMESTAMP" property="relieveTime" />
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
    <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
    <result column="remark" jdbcType="VARCHAR" property="remark" />
  </resultMap>
   
  <sql id="Base_Column_List">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    id, order_no, user_servant_id, user_employer_id, user_butler_id, servant_name, employer_name, 
    butler_name, butler_first_confirm, first_service_time, rest_mode, rest_day, is_need_clock, 
    work_scope, is_invalid, relieve_time, create_time, update_time, remark
  </sql>
   
    <select id="selectByRelationUserId" parameterType="java.lang.Long" resultMap="BaseResultMap">
	    select
	     <include refid="Base_Column_List" />
	      From hm_employ_relation
			where (  user_servant_id=#{userId} or user_employer_id=#{userId} )
			 and is_invalid=1
			 order by id desc
			  limit 1
	</select>
	
	
	<select id="selectEmployLis"   resultType="org.linlinjava.jz.db.dto.hm.EmployListDto"> 
		select her.id, her.employer_name as employerName,her.servant_name as servantName,hau.phone,hau.user_type as userType,
		(
		CASE 
			WHEN datediff(now(),her.first_service_time)>30 THEN
				'0'
			ELSE
				'1'
		END ) moheqi 
		from hm_employ_relation her
		left join hm_app_user hau on her.user_employer_id=hau.id
		LEFT JOIN customer_order_charge coc ON coc.order_no = her.order_no AND coc.state = 0
		LEFT JOIN customer_order co ON co.order_no = her.order_no
		where 
		(
		her.user_butler_id=#{userButlerId} 
		<if test="queryStr !=null and queryStr !='' ">
			and ( 
				employer_name like  CONCAT('%',#{queryStr},'%') 
				or servant_name like  CONCAT('%',#{queryStr},'%')
				 or hau.phone=#{queryStr}
			 )
		 </if>
		 ) 
		 or co.id   in( select  order_id From jz_ticket where 'handler' =#{userId} and ticket_type=2 )
	</select>
	
	<select id="selectButlerCustomerCount"   resultType="java.lang.Long"> 
		select count(*)
		from hm_employ_relation her
		left join hm_app_user hau on her.user_employer_id=hau.id
		LEFT JOIN customer_order_charge coc ON coc.order_no = her.order_no AND coc.state = 0
		LEFT JOIN customer_order co ON co.order_no = her.order_no
		where 
		  her.user_butler_id=#{userButlerId} 
		 or 
		 co.id   in( select  order_id From jz_ticket where 'handler' =#{userId} and ticket_type=2 )
	</select>
	
	
	<select id="selectGtTwentyFour"   resultType="java.lang.Integer"> 
		 select count(*) From hm_butler_task_parent hbt 
		left join hm_employ_relation he on hbt.order_no=he.order_no
		where he.user_butler_id=#{userButlerId} and hbt.expiration_time>#{expirationTime,jdbcType=TIMESTAMP}
	</select>
	
	<select id="selectCurrentDay"   resultType="java.lang.Integer"> 
		 select count(*) From hm_butler_task_parent hbt 
		left join hm_employ_relation he on hbt.order_no=he.order_no
		where he.user_butler_id=#{userButlerId}
		 and hbt.expiration_time 
		 	between #{expirationTimeSt,jdbcType=TIMESTAMP} and #{expirationTimeEd,jdbcType=TIMESTAMP}
	</select>
	
	<select id="selectTaskTimeOut"   resultType="java.lang.Integer"> 
		select count(*) From hm_butler_task_parent hbtp
		left join hm_employ_relation her on her.id=hbtp.employ_relation_id
		left join hm_app_user hu on her.user_butler_id=hu.id
		where hu.sys_userid =#{sysUserId}
		and hbtp.`status`='0'
		 <![CDATA[ and hbtp.expiration_time < #{expirationTime,jdbcType=TIMESTAMP} ]]> 
	</select>
	
	<select id="selectTaskNeedComplate"   resultType="java.lang.Integer"> 
		select count(*) From hm_butler_task_parent hbtp
		left join hm_employ_relation her on her.id=hbtp.employ_relation_id
		left join hm_app_user hu on her.user_butler_id=hu.id
		where hu.sys_userid =#{sysUserId}
		and hbtp.`status`='0'  
	</select>
  
  
</mapper>