<?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">
<!-- namespace必须指向DAO接口 -->
<mapper namespace="com.jpsoft.shinestar.modules.base.dao.PersonHealthLedgerDAO">
	<resultMap id="PersonHealthLedgerMap" type="com.jpsoft.shinestar.modules.base.entity.PersonHealthLedger">
		<id property="id" column="id_" />
			<result property="personId" column="person_id" />
			<result property="healthStatus" column="health_status" />
			<result property="healthRemark" column="health_remark" />
            <result property="familyStatus" column="family_status" />
			<result property="familyGreenCode" column="family_green_code" />
			<result property="isGoto" column="is_goto" />
			<result property="isTouch" column="is_touch" />
			<result property="delFlag" column="del_flag" />
			<result property="createBy" column="create_by" />
			<result property="createTime" column="create_time" />
			<result property="updateBy" column="update_by" />
			<result property="updateTime" column="update_time" />
			<result property="remark" column="remark_" />
		<result property="temperature" column="temperature_" />
		<result property="longitude" column="longitude_" />
		<result property="latitude" column="latitude_" />
		<result property="address" column="address_" />
		<result property="isJingzhou" column="is_jingzhou" />
		<result property="isProvince" column="is_province" />
		<result property="countNum" column="count_num" />

	</resultMap>
	<resultMap id="workerPersonMap"
			   type="com.jpsoft.shinestar.modules.base.dto.PersonHealthLedgerDTO"
			   extends="com.jpsoft.shinestar.modules.base.dao.PersonInfoDAO.PersonInfoMap">
		<result property="personHealthLedgerId" column="person_health_ledger_id"/>
		<result property="countNum" column="count_num"/>
		<result property="address" column="address"/>


	</resultMap>

	<insert id="insert" parameterType="com.jpsoft.shinestar.modules.base.entity.PersonHealthLedger">
	<!--
	<selectKey resultType="java.lang.String" order="BEFORE" keyProperty="id">
		select sys_guid() from dual
	</selectKey>
	-->
		<![CDATA[
		insert into base_person_health_ledger
	    (id_,person_id,health_status,health_remark,family_green_code,
	    is_goto,is_touch,del_flag,create_by,create_time,update_by,update_time,
	    remark_,family_status,temperature_,longitude_,latitude_,address_,is_jingzhou,is_province,count_num)
		values
		(
			#{id,jdbcType=VARCHAR}
			,#{personId,jdbcType= NUMERIC }
			,#{healthStatus,jdbcType=VARCHAR}
			,#{healthRemark,jdbcType=VARCHAR}
			,#{familyGreenCode,jdbcType= NUMERIC }
			,#{isGoto,jdbcType= NUMERIC }
			,#{isTouch,jdbcType= NUMERIC }
			,#{delFlag,jdbcType= NUMERIC }
			,#{createBy,jdbcType=VARCHAR}
			,#{createTime,jdbcType= TIMESTAMP }
			,#{updateBy,jdbcType=VARCHAR}
			,#{updateTime,jdbcType= TIMESTAMP }
			,#{remark,jdbcType= VARCHAR }
			,#{familyStatus,jdbcType= VARCHAR }
			,#{temperature,jdbcType= NUMERIC }
			 ,#{longitude,jdbcType=VARCHAR}
            ,#{latitude,jdbcType=VARCHAR}
            ,#{address,jdbcType=VARCHAR}
            ,#{isJingzhou,jdbcType=NUMERIC}
             ,#{isProvince,jdbcType=NUMERIC}
            ,#{countNum,jdbcType=NUMERIC}
		)
	]]>
	</insert>
	<delete id="delete" parameterType="string">
		delete from base_person_health_ledger where id_=#{id,jdbcType=VARCHAR}
	</delete>
	<update id="update" parameterType="com.jpsoft.shinestar.modules.base.entity.PersonHealthLedger">
		update base_person_health_ledger
		<set>
				<if test="personId!=null">
		person_id=#{personId,jdbcType= NUMERIC },
		</if>
				<if test="healthStatus!=null">
		health_status=#{healthStatus,jdbcType=VARCHAR},
		</if>
				<if test="healthRemark!=null">
		health_remark=#{healthRemark,jdbcType=VARCHAR},
		</if>
				<if test="familyGreenCode!=null">
		family_green_code=#{familyGreenCode,jdbcType= NUMERIC },
		</if>
				<if test="isGoto!=null">
		is_goto=#{isGoto,jdbcType= NUMERIC },
		</if>
				<if test="isTouch!=null">
		is_touch=#{isTouch,jdbcType= NUMERIC },
		</if>
				<if test="delFlag!=null">
		del_flag=#{delFlag,jdbcType= NUMERIC },
		</if>
				<if test="createBy!=null">
		create_by=#{createBy,jdbcType=VARCHAR},
		</if>
				<if test="createTime!=null">
		create_time=#{createTime,jdbcType= TIMESTAMP },
		</if>
				<if test="updateBy!=null">
		update_by=#{updateBy,jdbcType=VARCHAR},
		</if>
				<if test="updateTime!=null">
		update_time=#{updateTime,jdbcType= TIMESTAMP },
		</if>
			<if test="remark!=null">
				remark_=#{remark,jdbcType= VARCHAR },
			</if>
            <if test="familyStatus!=null">
                family_status=#{familyStatus,jdbcType= VARCHAR },
            </if>
			<if test="temperature!=null">
				temperature_=#{temperature,jdbcType= NUMERIC },
			</if>
			<if test="longitude!=null">
				longitude_=#{longitude,jdbcType=VARCHAR},
			</if>
			<if test="latitude!=null">
				latitude_=#{latitude,jdbcType=VARCHAR},
			</if>
			<if test="address!=null">
				address_=#{address,jdbcType=VARCHAR},
			</if>
			<if test="isJingzhou!=null">
				is_Jingzhou=#{isJingzhou,jdbcType=NUMERIC},
			</if>
			<if test="isProvince!=null">
				is_Province=#{isProvince,jdbcType=NUMERIC},
			</if>
			<if test="countNum!=null">
				count_Num=#{countNum,jdbcType=NUMERIC},
			</if>

		</set>
	where id_=#{id}
	</update>
    <update id="replace">
		update base_person_health_ledger
		set person_id=#{destPersonId},update_time=NOW()
		where person_id=#{srcPersonId};
	</update>
    <select id="get" parameterType="string" resultMap="PersonHealthLedgerMap">
		select * from base_person_health_ledger where id_=#{0} and del_flag=0
	</select>
	<select id="exist" parameterType="string" resultType="int">
		select count(*) from base_person_health_ledger where id_=#{0} and del_flag=0
	</select>
	<select id="list" resultMap="PersonHealthLedgerMap">
		select * from base_person_health_ledger and del_flag=0
	</select>

	<select id="existByCreateTimeAndPersonId" resultMap="PersonHealthLedgerMap">
		<![CDATA[
			select * from base_person_health_ledger
			where del_flag=0
			and person_id=#{personId}
			and create_time>=#{startTime}
			and create_time<#{endTime}
			order by create_time desc
			limit 1
		]]>
	</select>
	<select id="search" parameterType="hashmap" resultMap="PersonHealthLedgerMap">
		<![CDATA[
			select a.* from base_person_health_ledger a
			left join base_person_info b on a.person_id = b.id_
			left join base_company_info d on b.company_id = d.id_
		]]>
		<where>
            a.del_flag = 0
			<if test="searchParams.id != null">
				and a.ID_ like #{searchParams.id}
			</if>
			<if test="searchParams.personId != null">
				and a.person_id = #{searchParams.personId}
			</if>
			<if test="searchParams.personName != null">
				and b.name_ like #{searchParams.personName}
			</if>
			<if test="searchParams.healthStatus != null">
				and a.health_status like #{searchParams.healthStatus}
			</if>
			<if test="searchParams.healthStatusNot != null">
				and a.health_status not like #{searchParams.healthStatusNot}
			</if>
			<if test="searchParams.companyId != null">
				and b.company_id = #{searchParams.companyId}
			</if>
			<if test="searchParams.companyCode != null">
				and d.code_ like #{searchParams.companyCode}
			</if>

			<if test="searchParams.startTime != null">
				<![CDATA[
					and a.create_time >= #{searchParams.startTime}
				]]>
			</if>
			<if test="searchParams.endTime != null">
				<![CDATA[
					and a.create_time <= #{searchParams.endTime}
				]]>
			</if>
			<if test="searchParams.GPS">
				<![CDATA[
					and a.longitude_ is not null and a.latitude_ is not null
				]]>
			</if>
			<if test="searchParams.addressOut != null">
				and a.address_ NOT like #{searchParams.addressOut}
			</if>
			<if test="searchParams.addressIn != null">
				and a.address_ like #{searchParams.addressIn}
			</if>
			<if test="searchParams.temperature != null">
				<![CDATA[
					and a.temperature_ > #{searchParams.temperature}
				]]>
			</if>
			<if test="searchParams.queryTime != null">
				and DATE_FORMAT(a.create_time,'%Y-%m-%d') =  DATE_FORMAT(#{searchParams.queryTime},'%Y-%m-%d')
			</if>
		</where>
		<foreach item="sort" collection="sortList"  open="order by" separator=",">
	        a.${sort.name} ${sort.order}
	 	</foreach>
	</select>

	<select id="findByTimeAndPersonId" resultMap="PersonHealthLedgerMap">
		select * from base_person_health_ledger a
		<where>
			a.del_flag = 0
			<if test="personId != null">
				and a.person_id = #{personId}
			</if>
			<if test="queryTime != null">
				and DATE_FORMAT(a.create_time,'%Y-%m-%d') =  DATE_FORMAT(#{queryTime},'%Y-%m-%d')
			</if>
			<if test="addressOut != null">
				and a.address_ NOT like #{addressOut}
			</if>
			<if test="addressIn != null">
				and a.address_ like #{addressIn}
			</if>
			<if test="temperature != null">
				<![CDATA[
					and a.temperature_ > #{temperature}
				]]>
			</if>
		</where>
	</select>

	<select id="findByCreateTimeAndPersonId" resultMap="PersonHealthLedgerMap">
		<![CDATA[
			select * from base_person_health_ledger
			where del_flag=0
			and person_id=#{personId}
			and create_time>=#{startTime}
			and create_time<#{endTime}
			order by create_time desc
		]]>
	</select>

	<select id="lastByPersonId" resultMap="PersonHealthLedgerMap">
		select * from base_person_health_ledger a
		where a.del_flag = 0
		and a.person_id = #{personId}
		order by create_time desc limit 1
	</select>

	<select id="workerSearch" resultMap="workerPersonMap">
		select a.*,
		b.id_ as person_health_ledger_id,
		b.count_num as count_num,
		b.address_ as address
		from base_person_info a
		inner join base_company_info c on a.company_id = c.id_

 		left join base_person_health_ledger b on a.id_ = b.person_id
		and b.del_flag=0

		<if test="searchParams.startTime != null">
			<![CDATA[
				and b.create_time > #{searchParams.startTime}
			]]>
		</if>
		<if test="searchParams.endTime != null">
			<![CDATA[
				and b.create_time < #{searchParams.endTime}
			]]>
		</if>

		<where>
			a.del_flag = 0

			<if test="searchParams.name != null">
				and a.name_ like #{searchParams.name}
			</if>
			<if test="searchParams.companyId != null">
				and a.company_id = #{searchParams.companyId}
			</if>
			<if test="searchParams.companyCode != null">
				and c.code_ like #{searchParams.companyCode}
			</if>
			<if test='searchParams.status == "1"'>
				and b.id_ is not null
			</if>
			<if test='searchParams.status == "2"'>
				and b.id_ is null
			</if>
			<if test="searchParams.addressOut != null">
				and b.address_ NOT like #{searchParams.addressOut}
			</if>
			<if test="searchParams.addressIn != null">
				and b.address_ like #{searchParams.addressIn}
			</if>

			<if test='searchParams.InCompanyId != null'>
				<foreach collection="searchParams.InCompanyId" index="index" item="item" open="and a.company_id in("
						 separator="," close=")">
					#{item}
				</foreach>
			</if>
		</where>
	</select>

	<select id="query" resultType="java.util.Map">
		<![CDATA[
        select count(1) as total, count(  LOCATE('湖北省',b.address_)  OR NULL )  as count0

        ,count(  LOCATE('湖北省荆州市',b.address_)   OR NULL)  as count1

        ,count(  LOCATE('湖北省荆州市',b.address_)=0   OR NULL)  as count2

        from base_person_health_ledger b
		, base_person_info a
		]]>
		<where>
			b.del_flag = 0
			and   a.id_ = b.person_id
			<if test="searchParams.startTime != null">
				<![CDATA[
				and b.create_time > #{searchParams.startTime}
			]]>
			</if>
			<if test="searchParams.endTime != null">
				<![CDATA[
				and b.create_time < #{searchParams.endTime}
			]]>
			</if>

			<if test='searchParams.InCompanyId != null'>
				<foreach collection="searchParams.InCompanyId" index="index" item="item" open="and a.company_id in("
						 separator="," close=")">
					#{item}
				</foreach>
			</if>
		</where>
	</select>
</mapper>