<?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="cn.com.ty.lift.enterprise.oa.mapper.AttendanceMapper" >
	<resultMap id="BaseResultMap" type="cn.com.ty.lift.enterprise.oa.dto.AttendanceResponse" >
		<id column="id" property="id" jdbcType="BIGINT" />
		<result column="mt_company_id" property="mtCompanyId" jdbcType="BIGINT" />
		<result column="user_id" property="userId" jdbcType="BIGINT" />
		<result column="coordinate" property="coordinate" jdbcType="VARCHAR" />
		<result column="remarks" property="remarks" jdbcType="VARCHAR" />
		<result column="address" property="address" jdbcType="VARCHAR" />
		<result column="creator_id" property="creatorId" jdbcType="BIGINT" />
		<result column="create_date" property="createDate" jdbcType="TIMESTAMP" />
		<result column="update_id" property="updateId" jdbcType="BIGINT" />
		<result column="update_date" property="updateDate" jdbcType="TIMESTAMP" />
		<result column="status" property="status" jdbcType="INTEGER" />
		<result column="type" property="type" jdbcType="INTEGER" />

        <result column="username" property="username" jdbcType="VARCHAR" />
		<result column="top_time" property="topTime" jdbcType="TIMESTAMP" />
		<result column="down_time" property="updateDate" jdbcType="TIMESTAMP" />
		<result column="top_time_pm" property="updateDate" jdbcType="TIMESTAMP" />
		<result column="down_time_pm" property="updateDate" jdbcType="TIMESTAMP" />
		<result column="top_time_ot" property="updateDate" jdbcType="TIMESTAMP" />
		<result column="down_time_ot" property="updateDate" jdbcType="TIMESTAMP" />
	</resultMap>

	<!--根据条件分页查询-->
	<select id="pageByCondition" resultMap="BaseResultMap" parameterType="cn.com.ty.lift.enterprise.oa.dto.AttendanceRequest">
		SELECT
			att.*,
			mt.top_time,
			mt.down_time,
			mt.top_time_pm,
			mt.down_time_pm,
			mt.top_time_ot,
			mt.down_time_ot,
			ui.name AS username
		FROM attendance att
			LEFT JOIN user_info ui ON att.user_id = ui.user_id
			LEFT JOIN maintenance_company mt ON att.mt_company_id = mt.id
		<where>
			<if test="cond.username != null and cond.username != ''">
				AND ui.name LIKE concat('%',#{cond.username},'%')
			</if>
			<if test="cond.mtCompanyId != null and cond.mtCompanyId > 0">
				AND att.mt_company_id = #{cond.mtCompanyId}
			</if>
			<if test="cond.clockTime != null">
				AND date(att.create_date) = #{cond.clockTime}
			</if>
			<if test="cond.begin != null">
				AND date(att.create_date) &gt;= #{cond.begin}
			</if>
			<if test="cond.end != null">
				AND date(att.create_date) &lt;= #{cond.end}
			</if>
		</where>
	</select>

    <select id="listByIdList" resultMap="BaseResultMap" parameterType="cn.com.ty.lift.enterprise.oa.dto.AttendanceRequest">
        SELECT
            att.*,
            mt.top_time,
            mt.down_time,
            mt.top_time_pm,
            mt.down_time_pm,
            mt.top_time_ot,
            mt.down_time_ot,
            ui.name AS username,
            ui.mobile AS userMobile
        FROM attendance att
        LEFT JOIN user_info ui ON att.user_id = ui.user_id
        LEFT JOIN maintenance_company mt ON att.mt_company_id = mt.id
        <where>
			<if test="cond.userIds != null and cond.userIds.size > 0">
				AND att.user_id IN
				<foreach collection="cond.userIds" item="uId" open="(" separator="," close=")">
					#{uId}
				</foreach>
			</if>
            <if test="cond.userId != null and cond.userId != ''">
                AND att.user_id = #{cond.userId}
            </if>
            <if test="cond.mtCompanyId != null and cond.mtCompanyId != ''">
                AND att.mt_company_id = #{cond.mtCompanyId}
            </if>
            <if test="cond.attendanceYearMonth != null and cond.attendanceYearMonth != ''">
                AND DATE_FORMAT(att.create_date,'%Y-%m') = #{cond.attendanceYearMonth}
            </if>
            ORDER BY att.create_date DESC
        </where>
    </select>

	<select id="pageByUser" resultMap="BaseResultMap" parameterType="cn.com.ty.lift.enterprise.oa.dto.AttendanceRequest">
		SELECT
			att.*,
			ui.name AS username
		FROM attendance att
		    LEFT JOIN user_info ui ON att.user_id = ui.user_id
		<where>
			<if test="cond.mtCompanyId != null and cond.mtCompanyId > 0">
				AND att.mt_company_id = #{cond.mtCompanyId}
			</if>
			<if test="cond.userId != null">
				AND ui.user_id LIKE #{cond.userId}
			</if>
			<if test="cond.clockTime != null">
				AND date(att.create_date) = #{cond.clockTime}
			</if>
			<if test="cond.begin != null">
				AND date(att.create_date) &gt; #{cond.begin}
			</if>
			<if test="cond.end != null">
				AND date(att.create_date) &lt; #{cond.end}
			</if>
		</where>
	</select>

	<select id="infoById" resultMap="BaseResultMap" parameterType="cn.com.ty.lift.enterprise.oa.dto.AttendanceRequest">
		SELECT
			att.*,
			ui.name AS username
		FROM attendance att
		LEFT JOIN user_info ui ON att.user_id = ui.user_id
		<where>
			<if test="cond.id != null and cond.id > 0">
				AND att.id LIKE #{cond.id}
			</if>
		</where>
	</select>
</mapper>