<?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.yanzhu.jh.project.mapper.SurProjectAttendanceDataMapper">
    
    <resultMap type="SurProjectAttendanceData" id="SurProjectAttendanceDataResult">
        <result property="id"    column="id"    />
        <result property="cfgid"    column="cfgid"    />
        <result property="vendorsCode"    column="vendors_code"    />
        <result property="serverid"    column="serverid"    />
        <result property="workerId"    column="workerId"    />
        <result property="attendanceType"    column="attendance_type"    />
        <result property="attendanceTime"    column="attendance_time"    />
        <result property="identification"    column="identification"    />
        <result property="teamId"    column="teamId"    />
        <result property="workTypeCode"    column="workTypeCode"    />
        <result property="companyId"    column="companyId"    />
        <result property="vendorId"    column="vendorId"    />
        <result property="projectType"    column="projectType"    />
        <result property="deviceCode"    column="device_code"    />
        <result property="workPointId"    column="work_point_id"    />
        <result property="scanPhoto"    column="scanPhoto"    />
        <result property="other"    column="other"    />
        <result property="state"    column="state"    />
        <result property="remark"    column="remark"    />
        <result property="isDel"    column="is_del"    />
        <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="projectId" column="project_id" />
        <result property="subDeptId" column="sub_dept_id"/>
        <result property="companyTypeId" column="companyTypeId"/>
    </resultMap>

    <sql id="selectSurProjectAttendanceDataVo">
        select * FROM
            (
                select a.*,b.project_id,b.sub_dept_id,c.companyTypeId
                from sur_project_attendance_data a,sur_project_attendance_cfg b,vw_sur_project_attendance_group c
                where a.cfgid=b.id and a.companyId=c.companyId
                )
        sur_project_attendance_data
    </sql>

    <select id="selectSurProjectAttendanceDataList" parameterType="SurProjectAttendanceData" resultMap="SurProjectAttendanceDataResult">
        <include refid="selectSurProjectAttendanceDataVo"/>
        <where>  
            <if test="cfgid != null "> and cfgid = #{cfgid}</if>
            <if test="vendorsCode != null  and vendorsCode != ''"> and vendors_code = #{vendorsCode}</if>
            <if test="serverid != null "> and serverid = #{serverid}</if>
            <if test="workerId != null "> and workerId = #{workerId}</if>
            <if test="attendanceType != null  and attendanceType != ''"> and attendance_type = #{attendanceType}</if>
            <if test="attendanceTime != null  and attendanceTime != ''"> and attendance_time = #{attendanceTime}</if>
            <if test="identification != null  and identification != ''"> and identification = #{identification}</if>
            <if test="teamId != null "> and teamId = #{teamId}</if>
            <if test="workTypeCode != null  and workTypeCode != ''"> and workTypeCode = #{workTypeCode}</if>
            <if test="companyId != null "> and companyId = #{companyId}</if>
            <if test="vendorId != null "> and vendorId = #{vendorId}</if>
            <if test="projectType != null "> and projectType = #{projectType}</if>
            <if test="deviceCode != null  and deviceCode != ''"> and device_code = #{deviceCode}</if>
            <if test="workPointId != null  and workPointId != ''"> and work_point_id = #{workPointId}</if>
            <if test="scanPhoto != null  and scanPhoto != ''"> and scanPhoto = #{scanPhoto}</if>
            <if test="other != null  and other != ''"> and other = #{other}</if>
            <if test="state != null "> and state = #{state}</if>
            <if test="isDel != null "> and is_del = #{isDel}</if>
        </where>
    </select>
    
    <select id="selectSurProjectAttendanceDataById" parameterType="Long" resultMap="SurProjectAttendanceDataResult">
        <include refid="selectSurProjectAttendanceDataVo"/>
        where id = #{id}
    </select>
        
    <insert id="insertSurProjectAttendanceData" parameterType="SurProjectAttendanceData" useGeneratedKeys="true" keyProperty="id">
        insert into sur_project_attendance_data
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="cfgid != null">cfgid,</if>
            <if test="vendorsCode != null">vendors_code,</if>
            <if test="serverid != null">serverid,</if>
            <if test="workerId != null">workerId,</if>
            <if test="attendanceType != null">attendance_type,</if>
            <if test="attendanceTime != null">attendance_time,</if>
            <if test="identification != null">identification,</if>
            <if test="teamId != null">teamId,</if>
            <if test="workTypeCode != null">workTypeCode,</if>
            <if test="companyId != null">companyId,</if>
            <if test="vendorId != null">vendorId,</if>
            <if test="projectType != null">projectType,</if>
            <if test="deviceCode != null">device_code,</if>
            <if test="workPointId != null">work_point_id,</if>
            <if test="scanPhoto != null">scanPhoto,</if>
            <if test="other != null">other,</if>
            <if test="state != null">state,</if>
            <if test="remark != null">remark,</if>
            <if test="isDel != null">is_del,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="cfgid != null">#{cfgid},</if>
            <if test="vendorsCode != null">#{vendorsCode},</if>
            <if test="serverid != null">#{serverid},</if>
            <if test="workerId != null">#{workerId},</if>
            <if test="attendanceType != null">#{attendanceType},</if>
            <if test="attendanceTime != null">#{attendanceTime},</if>
            <if test="identification != null">#{identification},</if>
            <if test="teamId != null">#{teamId},</if>
            <if test="workTypeCode != null">#{workTypeCode},</if>
            <if test="companyId != null">#{companyId},</if>
            <if test="vendorId != null">#{vendorId},</if>
            <if test="projectType != null">#{projectType},</if>
            <if test="deviceCode != null">#{deviceCode},</if>
            <if test="workPointId != null">#{workPointId},</if>
            <if test="scanPhoto != null">#{scanPhoto},</if>
            <if test="other != null">#{other},</if>
            <if test="state != null">#{state},</if>
            <if test="remark != null">#{remark},</if>
            <if test="isDel != null">#{isDel},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
         </trim>
    </insert>

    <update id="updateSurProjectAttendanceData" parameterType="SurProjectAttendanceData">
        update sur_project_attendance_data
        <trim prefix="SET" suffixOverrides=",">
            <if test="cfgid != null">cfgid = #{cfgid},</if>
            <if test="vendorsCode != null">vendors_code = #{vendorsCode},</if>
            <if test="serverid != null">serverid = #{serverid},</if>
            <if test="workerId != null">workerId = #{workerId},</if>
            <if test="attendanceType != null">attendance_type = #{attendanceType},</if>
            <if test="attendanceTime != null">attendance_time = #{attendanceTime},</if>
            <if test="identification != null">identification = #{identification},</if>
            <if test="teamId != null">teamId = #{teamId},</if>
            <if test="workTypeCode != null">workTypeCode = #{workTypeCode},</if>
            <if test="companyId != null">companyId = #{companyId},</if>
            <if test="vendorId != null">vendorId = #{vendorId},</if>
            <if test="projectType != null">projectType = #{projectType},</if>
            <if test="deviceCode != null">device_code = #{deviceCode},</if>
            <if test="workPointId != null">work_point_id = #{workPointId},</if>
            <if test="scanPhoto != null">scanPhoto = #{scanPhoto},</if>
            <if test="other != null">other = #{other},</if>
            <if test="state != null">state = #{state},</if>
            <if test="remark != null">remark = #{remark},</if>
            <if test="isDel != null">is_del = #{isDel},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteSurProjectAttendanceDataById" parameterType="Long">
        delete from sur_project_attendance_data where id = #{id}
    </delete>

    <delete id="deleteSurProjectAttendanceDataByIds" parameterType="String">
        delete from sur_project_attendance_data where id in 
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>
    <select id="getLastServerId" parameterType="SurProjectAttendanceData" resultType="Long">
        SELECT IF(MAX(serverid),MAX(serverid),0) serverid FROM sur_project_attendance_data WHERE cfgid=#{cfgid}
    </select>
    <select id="groupByComany"  parameterType="SurProjectAttendanceData" resultMap="SurProjectAttendanceDataResult">
        select companyTypeId,count(1) id from ( <include refid="selectSurProjectAttendanceDataVo"/> ) x2 where id in (
        select min(id) from ( <include refid="selectSurProjectAttendanceDataVo"/> ) x1
                       where  date(attendance_time)=#{attendanceTime}
                        <if test="subDeptId!=null and subDeptId>0"> and project_id in (
                            SELECT id FROM sur_project WHERE isdel=0 AND deptid = #{subDeptId}
                            )
                        </if>
        <if test="prjIds !=null and prjIds.size()>0">
            and project_id in
            <foreach collection="prjIds"  item="item" index="index" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
                        <if test="projectId!=null and projectId>0"> and project_id=#{projectId}</if>
                       group by workerId )
        and  companyTypeId in (1,2,3,8) group by companyTypeId
    </select>
</mapper>