<?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.CanteenDiningRecordDAO">
	<resultMap id="CanteenDiningRecordMap" type="com.jpsoft.shinestar.modules.base.entity.CanteenDiningRecord">
		<id property="id" column="id_" />
			<result property="personId" column="person_id" />
			<result property="deviceNo" column="device_no" />
			<result property="isOutsourcer" column="is_outsourcer" />
			<result property="category" column="category_" />
			<result property="categoryAmount" column="category_amount" />
			<result property="address" column="address_" />
			<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="attendanceDate" column="attendance_date" />
			<result property="recordTime" column="record_time" />
			<result property="remark" column="remark_" />
			</resultMap>
	<insert id="insert" parameterType="com.jpsoft.shinestar.modules.base.entity.CanteenDiningRecord">
	<!--
	<selectKey resultType="java.lang.String" order="BEFORE" keyProperty="id">
		select sys_guid() from dual
	</selectKey>
	-->
	<![CDATA[
		insert into base_canteen_dining_record
	    (id_,person_id,device_no,is_outsourcer,category_,category_amount,address_,del_flag,create_by,create_time,update_by,update_time,attendance_date,record_time,remark_)
		values
		(
#{id,jdbcType=VARCHAR}
,#{personId,jdbcType= NUMERIC }
,#{deviceNo,jdbcType=VARCHAR}
,#{isOutsourcer,jdbcType= NUMERIC }
,#{category,jdbcType=VARCHAR}
,#{categoryAmount,jdbcType= NUMERIC }
,#{address,jdbcType=VARCHAR}
,#{delFlag,jdbcType= NUMERIC }
,#{createBy,jdbcType=VARCHAR}
,#{createTime,jdbcType= TIMESTAMP }
,#{updateBy,jdbcType=VARCHAR}
,#{updateTime,jdbcType= TIMESTAMP }
,#{attendanceDate,jdbcType=VARCHAR}
,#{recordTime,jdbcType= TIMESTAMP }
,#{remark,jdbcType=VARCHAR}
		)
	]]>
	</insert>
	<delete id="delete" parameterType="string">
		delete from base_canteen_dining_record where id_=#{id,jdbcType=VARCHAR}
	</delete>
	<update id="update" parameterType="com.jpsoft.shinestar.modules.base.entity.CanteenDiningRecord">
		update base_canteen_dining_record
		<set>
				<if test="personId!=null">
		person_id=#{personId,jdbcType= NUMERIC },
		</if>
				<if test="deviceNo!=null">
		device_no=#{deviceNo,jdbcType=VARCHAR},
		</if>
				<if test="isOutsourcer!=null">
		is_outsourcer=#{isOutsourcer,jdbcType= NUMERIC },
		</if>
				<if test="category!=null">
		category_=#{category,jdbcType=VARCHAR},
		</if>
				<if test="categoryAmount!=null">
		category_amount=#{categoryAmount,jdbcType= NUMERIC },
		</if>
				<if test="address!=null">
		address_=#{address,jdbcType=VARCHAR},
		</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="attendanceDate!=null">
				attendance_date=#{attendanceDate,jdbcType=VARCHAR},
			</if>
			<if test="recordTime!=null">
				record_time=#{recordTime,jdbcType= TIMESTAMP },
			</if>
			<if test="remark!=null">
				remark_=#{remark,jdbcType=VARCHAR},
			</if>
		</set>
	where id_=#{id}
	</update>
	<select id="get" parameterType="string" resultMap="CanteenDiningRecordMap">
		select * from base_canteen_dining_record where id_=#{0}
	</select>
	<select id="exist" parameterType="string" resultType="int">
		select count(*) from base_canteen_dining_record where id_=#{0}
	</select>
	<select id="list" resultMap="CanteenDiningRecordMap">
		select * from base_canteen_dining_record
	</select>
	<select id="findCountByPersonId" resultType="int">
		select count(*) from base_canteen_dining_record where del_flag=false and person_id = #{0}
	</select>
	<select id="findByPersonId" resultMap="CanteenDiningRecordMap">
		select * from base_canteen_dining_record where del_flag=false and person_id = #{0}
	</select>

	<select id="findCountByPersonIdAndAddress" resultType="int">
		select count(*) from base_canteen_dining_record
		<where>
			del_flag=false
			<if test="searchParams.personId != null">
				and person_id = #{searchParams.personId}
			</if>
			<if test="searchParams.category != null">
				and category_ = #{searchParams.category}
			</if>
			<if test="searchParams.address != null">
				and address_ like #{searchParams.address}
			</if>
			<if test="searchParams.beginTime != null">
				<![CDATA[
                  and record_time >= #{searchParams.beginTime}
                ]]>
			</if>
			<if test="searchParams.endTime != null">
				<![CDATA[
                  and record_time <= #{searchParams.endTime}
                ]]>
			</if>
		</where>
	</select>
	<select id="lastEatTime" resultMap="CanteenDiningRecordMap">
		select * from base_canteen_dining_record where del_flag=false and person_id = #{0} order by record_time desc limit 1
	</select>

	<select id="findByPersonIdAndRecordTime" resultMap="CanteenDiningRecordMap">
		<![CDATA[
		select * from base_canteen_dining_record
		where del_flag=false and
		person_id = #{personId} and
		record_time>=#{startTime} and record_time<=#{endTime}
		]]>
	</select>
	<select id="search" parameterType="hashmap" resultMap="CanteenDiningRecordMap">
		<![CDATA[
			select * from base_canteen_dining_record a
			inner join base_person_info b on a.person_id = b.id_
			inner join base_company_info c on c.id_ = b.company_id
		]]>
		<where>
			a.del_flag = false and b.del_flag = false
			<if test="searchParams.personId != null">
				and a.person_id = #{searchParams.personId}
			</if>
			<if test="searchParams.category != null">
				and a.category_ = #{searchParams.category}
			</if>
			<if test="searchParams.isOutsourcer != null">
				and a.is_outsourcer = #{searchParams.isOutsourcer}
			</if>
			<if test="searchParams.name != null">
				and b.name_ like #{searchParams.name}
			</if>
			<if test="searchParams.deviceNoList != null">
                and a.device_no in
				<foreach item="item" collection="searchParams.deviceNoList"  open="(" separator="," close=")">
                    #{item}
				</foreach>
			</if>
			<if test="searchParams.jobNumber != null">
				and b.job_number like #{searchParams.jobNumber}
			</if>
			<if test="searchParams.companyId != null">
				and c.code_ like #{searchParams.companyId}
			</if>
			<if test="searchParams.beginTime != null">
				<![CDATA[
                  and a.record_time >= #{searchParams.beginTime}
                ]]>
			</if>
			<if test="searchParams.endTime != null">
				<![CDATA[
                  and a.record_time <= #{searchParams.endTime}
                ]]>
			</if>
		</where>
		<foreach item="sort" collection="sortList"  open="order by" separator=",">
	        ${sort.name} ${sort.order}
	 	</foreach>
	</select>

	<select id="canteenDiningRecordDetailList" parameterType="hashmap" resultType="hashmap">

			select a.name_ as canteenName,a.id_ as id,a.address_ as address,
			sum((SELECT count(z.person_id) from base_canteen_dining_record z WHERE z.device_no = c.device_no and  z.category_='2' and z.del_flag=FALSE
			<if test="searchParams.isOutsourcer != null">
				and z.is_outsourcer = #{searchParams.isOutsourcer}
			</if>
			<if test="searchParams.beginTime != null">
				<![CDATA[
                  and z.record_time >= #{searchParams.beginTime}
                 ]]>
			</if>
			<if test="searchParams.endTime != null">
				<![CDATA[
					and z.record_time <= #{searchParams.endTime}
				]]>
			</if>
			)) as 'lunch',
			sum((SELECT count(z.person_id) from base_canteen_dining_record z WHERE z.device_no = c.device_no and  z.category_='3' and z.del_flag=FALSE
			<if test="searchParams.isOutsourcer != null">
				and z.is_outsourcer = #{searchParams.isOutsourcer}
			</if>
			<if test="searchParams.beginTime != null">
				<![CDATA[
                  and z.record_time >= #{searchParams.beginTime}
                  ]]>
			</if>
			<if test="searchParams.endTime != null">
				<![CDATA[
				and z.record_time <= #{searchParams.endTime}
				]]>
			</if>
			)) as 'supper',
			sum((SELECT count(z.person_id) from base_canteen_dining_record z WHERE z.device_no = c.device_no and  z.category_='4' and z.del_flag=FALSE
			<if test="searchParams.isOutsourcer != null">
				and z.is_outsourcer = #{searchParams.isOutsourcer}
			</if>
			<if test="searchParams.beginTime != null">
				<![CDATA[
                  and z.record_time >= #{searchParams.beginTime}
                  ]]>
			</if>
			<if test="searchParams.endTime != null">
				<![CDATA[
				and z.record_time <= #{searchParams.endTime}
				]]>
			</if>
			)) as 'nightSnack',
			sum((SELECT count(z.person_id) from base_canteen_dining_record z WHERE z.device_no = c.device_no and z.del_flag=FALSE
			<if test="searchParams.isOutsourcer != null">
				and z.is_outsourcer = #{searchParams.isOutsourcer}
			</if>
			<if test="searchParams.beginTime != null">
				<![CDATA[
					  and z.record_time >= #{searchParams.beginTime}
					  ]]>
			</if>
			<if test="searchParams.endTime != null">
				<![CDATA[
					and z.record_time <= #{searchParams.endTime}
					]]>
			</if>
			)) as 'personNum'
			FROM base_canteen_info a
			left JOIN base_canteen_device b on a.id_= b.canteen_id
			left join base_device_info c on c.id_=b.device_id
		<where>
			a.del_flag = false and b.del_flag = false and c.del_flag=false
			GROUP BY a.id_
		</where>
		<foreach item="sort" collection="sortList"  open="order by" separator=",">
			${sort.name} ${sort.order}
		</foreach>
	</select>


	<select id="canteenDiningRecordCompanyList" parameterType="hashmap" resultType="hashmap">
			select a.name_ as companyName,a.id_ as companyId,
			sum((SELECT count(z.person_id) from base_canteen_dining_record z WHERE z.person_id = b.id_ and  z.category_='2' and z.del_flag=FALSE
			<if test="searchParams.beginTime != null">
				<![CDATA[
					  and z.record_time >= #{searchParams.beginTime}
					]]>
			</if>
			<if test="searchParams.endTime != null">
				<![CDATA[
					  and z.record_time <= #{searchParams.endTime}
					]]>
			</if>
			)) as 'lunch',
			sum((SELECT count(z.person_id) from base_canteen_dining_record z WHERE z.person_id = b.id_ and  z.category_='3' and z.del_flag=FALSE
			<if test="searchParams.beginTime != null">
				<![CDATA[
						  and z.record_time >= #{searchParams.beginTime}
						]]>
			</if>
			<if test="searchParams.endTime != null">
				<![CDATA[
						  and z.record_time <= #{searchParams.endTime}
						]]>
			</if>
			)) as 'supper',
			sum((SELECT count(z.person_id) from base_canteen_dining_record z WHERE z.person_id = b.id_ and  z.category_='4' and z.del_flag=FALSE
			<if test="searchParams.beginTime != null">
				<![CDATA[
					and z.record_time >= #{searchParams.beginTime}
				]]>
			</if>
			<if test="searchParams.endTime != null">
				<![CDATA[
					and z.record_time <= #{searchParams.endTime}
				]]>
			</if>
			)) as 'nightSnack',
			sum((SELECT count(z.person_id) from base_canteen_dining_record z WHERE z.person_id = b.id_ and z.del_flag=FALSE
			<if test="searchParams.beginTime != null">
				<![CDATA[
						and z.record_time >= #{searchParams.beginTime}
					]]>
			</if>
			<if test="searchParams.endTime != null">
				<![CDATA[
						and z.record_time <= #{searchParams.endTime}
					]]>
			</if>
			)) as 'personNum'
			FROM base_company_info a
			left join base_person_info b on a.id_=b.company_id
		<where>
			a.del_flag = false and b.del_flag = false
			<if test="searchParams.companyId != null">
				<![CDATA[
                  and a.code_ like #{searchParams.companyId}
                ]]>
			</if>
			<if test="searchParams.isOutsourcer != null">
				and a.is_outsourcer = #{searchParams.isOutsourcer}
			</if>
			GROUP BY a.id_
		</where>
		<foreach item="sort" collection="sortList"  open="order by" separator=",">
			${sort.name} ${sort.order}
		</foreach>
	</select>

	<select id="canteenDiningRecordCompanyDetail" parameterType="hashmap" resultType="hashmap">
			SELECT a.id_,c.name_ as companyName,a.name_,a.job_number,count(b.person_id) as personNum,
			(
				SELECT count(*) FROM base_canteen_dining_record z
				WHERE z.person_id = a.id_ and z.del_flag=FALSE and z.category_='2'
				<if test="searchParams.beginTime != null">
				<![CDATA[
					and z.record_time >= #{searchParams.beginTime}
				]]>
				</if>
				<if test="searchParams.endTime != null">
					<![CDATA[
					  and z.record_time <= #{searchParams.endTime}
					]]>
				</if>
			) as 'lunch',
			(
				SELECT count(*) FROM base_canteen_dining_record z
				WHERE z.person_id = a.id_ and z.del_flag=FALSE and z.category_='3'
				<if test="searchParams.beginTime != null">
					<![CDATA[
							and z.record_time >= #{searchParams.beginTime}
						]]>
				</if>
				<if test="searchParams.endTime != null">
					<![CDATA[
							  and z.record_time <= #{searchParams.endTime}
							]]>
				</if>
			) as 'supper',
			(
				SELECT count(*) FROM base_canteen_dining_record z
				WHERE z.person_id = a.id_ and z.del_flag=FALSE and z.category_='4'
				<if test="searchParams.beginTime != null">
					<![CDATA[
							and z.record_time >= #{searchParams.beginTime}
						]]>
				</if>
				<if test="searchParams.endTime != null">
					<![CDATA[
							  and z.record_time <= #{searchParams.endTime}
							]]>
				</if>
			) as 'nightSnack'
			FROM base_person_info a
			left join base_canteen_dining_record b on a.id_=b.person_id
			left join base_company_info c on a.company_id = c.id_
		<where>
			a.del_flag = false and b.del_flag = false
			<if test="searchParams.companyId != null">
				<![CDATA[
                  and a.company_id = #{searchParams.companyId}
                ]]>
			</if>
			<if test="searchParams.beginTime != null">
				<![CDATA[
                  and b.record_time >= #{searchParams.beginTime}
                ]]>
			</if>
			<if test="searchParams.endTime != null">
				<![CDATA[
                  and b.record_time <= #{searchParams.endTime}
                ]]>
			</if>
			GROUP BY a.id_
		</where>
		<foreach item="sort" collection="sortList"  open="order by" separator=",">
			${sort.name} ${sort.order}
		</foreach>
	</select>

    <select id="canteenDiningRecordDetail" parameterType="hashmap" resultType="hashmap">
        <![CDATA[
            SELECT * FROM base_canteen_dining_record x WHERE x.del_flag = false and x.device_no in
            (SELECT DISTINCT c.device_no FROM base_canteen_info a left join base_canteen_device b on a.id_=b.canteen_id
            inner join base_device_info c on b.device_id=c.id_
            WHERE a.id_= #{searchParams.id} and a.del_flag = false and b.del_flag = false and c.del_flag = false)
		]]>
		<if test="searchParams.isOutsourcer != null">
			and x.is_outsourcer = #{searchParams.isOutsourcer}
		</if>
		<if test="searchParams.beginTime != null">
			<![CDATA[
				and x.record_time >= #{searchParams.beginTime}
			 ]]>
		</if>
		<if test="searchParams.endTime != null">
			<![CDATA[
				and x.record_time <= #{searchParams.endTime}
			]]>
		</if>
        <foreach item="sort" collection="sortList"  open="order by" separator=",">
            ${sort.name} ${sort.order}
        </foreach>
    </select>

	<select id="canteenHqPersonList" parameterType="hashmap" resultType="hashmap">
		SELECT a.id_ as personId,a.job_number as jobNumber,a.name_ as personName,IFNULL(b.full_name, b.name_) as companyName,
		(SELECT count(id_) FROM base_canteen_dining_record z WHERE z.person_id=a.id_ and z.del_flag=FALSE and z.category_='2'
		<if test="searchParams.beginTime != null">
			<![CDATA[
                  and z.record_time >= #{searchParams.beginTime}
                 ]]>
		</if>
		<if test="searchParams.endTime != null">
			<![CDATA[
					and z.record_time <= #{searchParams.endTime}
				]]>
		</if>
		) as 'lunch',
		(SELECT count(id_) FROM base_canteen_dining_record z WHERE z.person_id=a.id_ and z.del_flag=FALSE and z.category_='3'
		<if test="searchParams.beginTime != null">
			<![CDATA[
                  and z.record_time >= #{searchParams.beginTime}
             ]]>
		</if>
		<if test="searchParams.endTime != null">
			<![CDATA[
				and z.record_time <= #{searchParams.endTime}
				]]>
		</if>
		) as 'supper',
		(SELECT count(id_) FROM base_canteen_dining_record z WHERE z.person_id=a.id_ and z.del_flag=FALSE and z.category_='4'
		<if test="searchParams.beginTime != null">
			<![CDATA[
                  and z.record_time >= #{searchParams.beginTime}
             ]]>
		</if>
		<if test="searchParams.endTime != null">
			<![CDATA[
				and z.record_time <= #{searchParams.endTime}
			]]>
		</if>
		) as 'nightSnack',
		(SELECT count(id_) FROM base_canteen_dining_record z WHERE z.person_id=a.id_ and z.del_flag=FALSE
		<if test="searchParams.beginTime != null">
			<![CDATA[
                  and z.record_time >= #{searchParams.beginTime}
             ]]>
		</if>
		<if test="searchParams.endTime != null">
			<![CDATA[
				and z.record_time <= #{searchParams.endTime}
			]]>
		</if>
		) as 'personNum',
		(SELECT IFNULL(sum(z.category_amount), 0.0) FROM base_canteen_dining_record z WHERE z.person_id=a.id_ and z.del_flag=FALSE
		<if test="searchParams.beginTime != null">
			<![CDATA[
                  and z.record_time >= #{searchParams.beginTime}
             ]]>
		</if>
		<if test="searchParams.endTime != null">
			<![CDATA[
				and z.record_time <= #{searchParams.endTime}
			]]>
		</if>
		) as 'totalFee'
		FROM base_person_info a
		left join base_company_info b on a.company_id=b.id_
		<where>
			a.del_flag = FALSE and b.del_flag = FALSE
			<if test='searchParams.companyCode!=null'>
				and b.code_ like #{searchParams.companyCode}
			</if>
			<if test="searchParams.hqStatus != null">
				and a.hq_status = #{searchParams.hqStatus}
			</if>
			<if test="searchParams.querHq != null">
				<![CDATA[
					and a.hq_status <> '0' and a.hq_status is not null
				]]>
			</if>
		</where>
		<foreach item="sort" collection="sortList"  open="order by" separator=",">
			${sort.name} ${sort.order}
		</foreach>
	</select>

</mapper>