<?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.api.manage.dao.operationManagement.SysAttendanceSchedulingPlanDao">

    <select id="list" parameterType="com.api.model.operationManagement.SearchAttendanceSchedulingPlan" resultType="com.api.vo.operationManagement.VoAttendanceSchedulingPlan">
        select sasp.*,su.actual_name as create_name,so.name as organization_name,sat.name as team_name
        from sys_attendance_scheduling_plan as sasp,sys_user as su,
        sys_attendance_team as sat,sys_organization as so
        <where>
            sasp.create_id = su.id
            and sasp.team_id = sat.id
            and sat.organization_id = so.id
            <if test="name != null and name != ''">
                and sasp.name like concat('%',#{name},'%')
            </if>
            <if test="status != null">
                and sasp.status = #{status}
            </if>
            <if test="organizationId != null">
                and so.id = #{organizationId}
            </if>
            <if test="createId != null">
                and sasp.create_id = #{createId}
            </if>
            <if test="teamId != null">
                and sasp.team_id = #{teamId}
            </if>
        </where>
        order by sasp.create_date desc
    </select>
    <select id="findById" parameterType="java.lang.Integer" resultType="com.api.vo.operationManagement.VoFBIAttendanceSchedulingPlan">
        select sasp.*,su.actual_name as create_name,so.name as organization_name,sat.name as team_name
        from sys_attendance_scheduling_plan as sasp,sys_user as su,
        sys_attendance_team as sat,sys_organization as so
        where
        sasp.create_id = su.id
        and sasp.team_id = sat.id
        and sat.organization_id = so.id
        and sasp.id = #{id}
    </select>

    <insert id="insert" parameterType="com.api.model.operationManagement.SysAttendanceSchedulingPlan">
        insert into sys_attendance_scheduling_plan(
            name,
            team_id,
            status,
            create_id,
            create_date
        )values (
            #{name},
            #{teamId},
            #{status},
            #{createId},
            #{createDate}
        )
        <selectKey keyProperty="id" resultType="java.lang.Integer">
            select LAST_INSERT_ID() AS id
        </selectKey>
    </insert>
    <insert id="insertDetail" parameterType="com.api.model.operationManagement.SysAttendanceSchedulingPlanDetail">
        insert into sys_attendance_scheduling_plan_detail(
            attendance_scheduling_plan_id,
            working_days,
            first_time_start,
            first_time_end,
            second_time_start,
            second_time_end
        )values (
            #{attendanceSchedulingPlanId},
            #{workingDays},
            #{firstTimeStart},
            #{firstTimeEnd},
            #{secondTimeStart},
            #{secondTimeEnd}
        )
    </insert>
    <insert id="insertException" parameterType="com.api.model.operationManagement.SysAttendanceSchedulingPlanException">
        insert into sys_attendance_scheduling_plan_exception(
            attendance_scheduling_plan_id,
            date_range,
            first_time_start,
            first_time_end,
            second_time_start,
            second_time_end,
            <if test="remakes != null and remakes != ''">
                remakes,
            </if>
            type
        )values (
            #{attendanceSchedulingPlanId},
            #{dateRange},
            #{firstTimeStart},
            #{firstTimeEnd},
            #{secondTimeStart},
            #{secondTimeEnd},
            <if test="remakes != null and remakes != ''">
                #{remakes},
            </if>
            #{type}
        )
    </insert>

    <delete id="deleteException" parameterType="java.lang.Integer">
        delete from sys_attendance_scheduling_plan_exception where attendance_scheduling_plan_id = #{id}
    </delete>
    <delete id="deleteDetail" parameterType="java.lang.Integer">
        delete from sys_attendance_scheduling_plan_detail where attendance_scheduling_plan_id = #{id}
    </delete>
    <delete id="delete" parameterType="java.lang.Integer">
        delete from sys_attendance_scheduling_plan where id = #{id}
    </delete>

    <update id="update" parameterType="com.api.model.operationManagement.SysAttendanceSchedulingPlan">
        update sys_attendance_scheduling_plan
        <set>
            <if test="name != null and name != ''">
                name = #{name},
            </if>
            <if test="teamId != null">
                team_id = #{teamId},
            </if>
            modify_id = #{modifyId},
            modify_date = #{modifyDate}
        </set>
        where id = #{id}
    </update>

    <select id="findExceptionByToday" parameterType="java.lang.Integer"
            resultType="com.api.model.operationManagement.SysAttendanceSchedulingPlanException">
        select saspe.*
        from sys_attendance_scheduling_plan_exception as saspe,sys_attendance_scheduling_plan as sasp,sys_attendance_team as sat
        where saspe.attendance_scheduling_plan_id = sasp.id and sasp.team_id = sat.id
		and sasp.status = 1 and TO_DAYS(now()) = TO_DAYS(saspe.date_range) and FIND_IN_SET(#{id},sat.team_members)
    </select>
    <select id="findExceptionByWeek" parameterType="java.lang.Integer"
            resultType="com.api.model.operationManagement.SysAttendanceSchedulingPlanDetail">
        select sacpd.*
        from sys_attendance_scheduling_plan_detail as sacpd,sys_attendance_scheduling_plan as sasp,sys_attendance_team as sat
        where sacpd.attendance_scheduling_plan_id = sasp.id  and sasp.team_id = sat.id
		and sasp.status = 1 and sacpd.working_days = WEEKDAY(now()) and FIND_IN_SET(#{id},sat.team_members)
    </select>
    <select id="findDetailByPlanId" parameterType="java.lang.Integer"
            resultType="com.api.vo.operationManagement.SysAttendanceSchedulingPlanDetailVo">
        select * from sys_attendance_scheduling_plan_detail where attendance_scheduling_plan_id = #{planId}
    </select>
    <select id="findExceptionByPlanId" parameterType="java.lang.Integer"
            resultType="com.api.vo.operationManagement.SysAttendanceSchedulingPlanExceptionVo">
        select * from sys_attendance_scheduling_plan_exception where attendance_scheduling_plan_id = #{planId}
    </select>

    <update id="updateStatusById" parameterType="com.api.model.operationManagement.SysAttendanceSchedulingPlan">
        update sys_attendance_scheduling_plan
        set status = #{status}
        where id = #{id}
    </update>


</mapper>