<?xml version="1.0" encoding="UTF-8"?>
<mapper namespace="com.yonyou.hrcloud.attentrpt.dao.IAttendRptCalDao" xmlns="http://www.yonyoucloud.com/schema/mapper"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.yonyoucloud.com/schema/mapper  http://www.yonyoucloud.com/schema/mapper.xsd">

    <insert id="batchInsert4Day">
      INSERT INTO ts_daystat
        ( ${colnums} )
        values
        <foreach collection="values" index="index" item="item" separator="," >
            <foreach collection="item" index="index" item="object"  open='(' separator=',' close=')' >#{object}</foreach>
        </foreach>

    </insert>


    <insert id="batchInsert4Mon">
        INSERT INTO ts_monthstat
        ( ${colnums} )
        values
        <foreach collection="values" index="index" item="item" separator="," >
            <foreach collection="item" index="index" item="object"  open='(' separator=',' close=')' >#{object}</foreach>
        </foreach>

    </insert>


    <insert id="batchInsertRptBVOs" parameterType="java.util.List">
        insert into ts_attendrptb (id,pk_attendrpt, report_type,itemtype,pk_timeitem,hournum,tenantid)
        values
        <foreach collection="list" item="item" index="index" separator=",">
            (#{item.id},#{item.pkAttendrpt},#{item.reportType},#{item.itemtype},#{item.pkTimeitem},#{item.hournum},#{item.tenantid})
        </foreach>
    </insert>

    <delete id="batchDel4DayCondition" parameterType="java.util.List">
        delete from ts_daystat where id in
        <foreach collection='list' index='index' item='item' open='(' separator=',' close=')'>#{item}</foreach>
    </delete>

    <delete id="batchDel4MonCondition" parameterType="java.util.List">
        delete from ts_monthstat where id in
        <foreach collection='list' index='index' item='item' open='(' separator=',' close=')'>#{item}</foreach>
    </delete>

    <delete id="delNoAttendDayRpts">
        delete from ts_daystat where tenantid = #{@context.scope} and staff_id in
        (select staff_id from ts_attend_staff where tenantid = #{@context.scope} and attend_type = '2')
        <![CDATA[ and CALENDAR >= #{begindate} and CALENDAR <= #{enddate}]]>
    </delete>

    <delete id="delNoAttendMonRpts">
        delete from ts_monthstat where  tenantid = #{@context.scope} and tsyear = #{year} and tsmonth = #{month} and staff_id in
        (select staff_id from ts_attend_staff where attend_type = '2' and tenantid = #{@context.scope})
    </delete>

    <delete id="batchDel4Day" parameterType="java.util.List">
        delete from ts_daystat where id in
        <foreach collection='list' index='index' item='item' open='(' separator=',' close=')'>#{item}</foreach>
    </delete>

    <delete id="batchDel4Mon" parameterType="java.util.List">
        delete from ts_monthstat where id in
        <foreach collection='list' index='index' item='item' open='(' separator=',' close=')'>#{item}</foreach>
    </delete>
    <delete id="batchDel4RptBByPid" parameterType="java.util.List">
        delete from ts_attendrptb where pk_attendrpt in
        <foreach collection='list' index='index' item='item' open='(' separator=',' close=')'>#{item}</foreach>
    </delete>

    <select id="isReferencing" resultType="com.yonyou.hrcloud.attentrpt.model.AttendRptBaseVO">
        select * from ${tableName} where tenantid = #{@context.scope} and ${whereSql}
    </select>

    <select id="queryDayRptByCondition" resultType="java.lang.String">
        select id from ts_daystat where tenantid = #{@context.scope} and STAFF_ID in
        <foreach collection='staffids' index='index' item='item' open='(' separator=',' close=')'>#{item}</foreach>
        and CALENDAR between #{begindate} and #{enddate} and ${$dialect.ifnull("ISAPPROVE","9")} &lt;&gt; 1
    </select>

    <select id="queryDayRptByConditionTempTable" resultType="java.lang.String">
        select id from ts_daystat where  tenantid = #{@context.scope}
        <con prefix="and" var="cond"/>
        and ${$dialect.ifnull("ISAPPROVE","9")} &lt;&gt; 1
    </select>

    <select id="queryMonRptByCondition" resultType="java.lang.String">
        select id from ts_monthstat where  tenantid = #{@context.scope} and DEPT_ID in
        <foreach collection='deptIds' index='index' item='item' open='(' separator=',' close=')'>#{item}</foreach>
        and STAFF_ID in
        <foreach collection='staffids' index='index' item='item' open='(' separator=',' close=')'>#{item}</foreach>
        and tsyear= #{year} and tsmonth = #{month} and ${$dialect.ifnull("ISAPPROVE","9")} &lt;&gt; 1
    </select>

    <select id="queryMonRptTempTable" resultType="java.lang.String">
        select id from ts_monthstat where  tenantid = #{@context.scope}
        <con prefix="and" var="cond"/>
        and ${$dialect.ifnull("ISAPPROVE","9")} &lt;&gt; 1
    </select>

    <select id="queryMonRptByOrgCondition" resultType="java.lang.String">
        select id from ts_monthstat where  tenantid = #{@context.scope} and org_id in
        <foreach collection='orgIds' index='index' item='item' open='(' separator=',' close=')'>#{item}</foreach>
        and STAFF_ID in
        <foreach collection='staffids' index='index' item='item' open='(' separator=',' close=')'>#{item}</foreach>
        and tsyear= #{year} and tsmonth = #{month} and ${$dialect.ifnull("ISAPPROVE","9")} &lt;&gt; 1
    </select>

    <select id="queryMonthRptInfoByCondition" resultType="java.util.Map">
        SELECT * FROM ts_monthstat WHERE tenantid = #{@context.scope}
        <con prefix="and" var="cond" ignore="true"/>
        and isapprove = 1
    </select>

    <select id="queryApproveMonthRptInfo" resultType="java.lang.String">
        SELECT concat(staff_id,concat(TSYEAR, tsmonth)) idkey
        FROM ts_monthstat WHERE tenantid = #{@context.scope}
        <if test="staffids != null and staffids.size() > 0">
            and staff_id in
            <foreach collection='staffids' index='index' item='item' open='(' separator=',' close=')'>#{item}</foreach>
        </if>
        and isapprove = 1 and
        concat(TSYEAR,tsmonth) &gt;= #{begindate} AND
        concat(TSYEAR,tsmonth) &lt;= #{enddate}
    </select>

    <delete id="batchDel4Day4Task">
        delete from ts_daystat where tenantid = #{@context.scope} and
        <![CDATA[ CALENDAR >= #{begindate} and CALENDAR <= #{enddate}]]> and ${$dialect.ifnull("isapprove","0")} = 0
    </delete>
    <delete id="batchDel4RptB4Task">
        delete from ts_attendrptb where tenantid = #{@context.scope} and pk_attendrpt in
        <if test="rptType == 1">
            (select id from ts_daystat where   <![CDATA[ CALENDAR >= #{begindate} and CALENDAR <= #{enddate}]]> and
            ${$dialect.ifnull("isapprove","0")} = 0)
        </if>
        <if test="rptType == 2">
            (select id from ts_monthstat where tsyear= #{year} and tsmonth = #{month} and ${$dialect.ifnull("isapprove","0")} = 0)
        </if>
    </delete>

    <delete id="delDayByCondition">
        delete from ts_daystat where tenantid = #{@context.scope}
        <con prefix="and" var="cond" ignore="true"/>
    </delete>
    <delete id="delRptBByCondition">
        delete from ts_attendrptb where tenantid = #{@context.scope}
        <con prefix="and" var="cond" ignore="true"/>
    </delete>

    <select id="queryGlbdef3" resultType="java.util.Map">
        SELECT * FROM hi_psndoc_glbdef3 WHERE pk_psndoc = #{pk_psndoc}
        and glbdef1= #{year} and glbdef2 = #{month} and dr = 0
    </select>

    <select id="queryYearGlbdef3" resultType="java.util.Map">
        SELECT sum(nvl(glbdef10,0)) glbdef10,sum(nvl(glbdef11,0)) glbdef11,sum(nvl(glbdef12,0)) glbdef12 FROM hi_psndoc_glbdef3 WHERE pk_psndoc = #{pk_psndoc}
        and glbdef1= #{year} and dr = 0
    </select>

    <select id="queryKQItem" resultType="java.util.Map">
        select ts_item.name,ts_item.id from ts_item where code in('f_n_4','f_n_2','f_n_1','f_n_5','f_n_7','f_n_3','f_n_6') and dr = 0
    </select>

</mapper>