<?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.oa.mapper.FlowInstanceMapper">
    <!-- 通用查询映射结果 -->
    <resultMap id="flowInstanceResultMap" type="cn.com.oa.model.vo.FlowInstanceVo">
        <result column="l_ins_id" property="insId"/>
        <result column="v_ins_name" property="insName"/>
        <result column="v_ins_remark" property="insRemark"/>
        <result column="l_model_id" property="modelId"/>
        <result column="n_state" property="state"/>
        <result column="v_creator" property="creator"/>
        <result column="d_createdate" property="createdate"/>
        <result column="d_canceldate" property="canceldate"/>
        <result column="d_finishdate" property="finishdate"/>
        <result column="l_form_ins_id" property="formInsId"/>
        <result column="l_form_id" property="formId"/>
        <result column="l_groupid" property="groupid"/>
        <result column="n_control" property="control"/>
        <result column="v_model_name" property="modelName"/>
    </resultMap>

    <select id="getSevenDayFlowInsCreateCount" resultType="java.util.Map">
        SELECT
            DATE_FORMAT(a.total_date,'%Y-%m-%d') AS totalDate,
            IFNULL(COUNT(filtered_t.l_ins_id), 0) AS totalCount
        FROM
            (SELECT CURDATE() - INTERVAL 0 DAY AS total_date
             UNION ALL SELECT CURDATE() - INTERVAL 1 DAY
             UNION ALL SELECT CURDATE() - INTERVAL 2 DAY
             UNION ALL SELECT CURDATE() - INTERVAL 3 DAY
             UNION ALL SELECT CURDATE() - INTERVAL 4 DAY
             UNION ALL SELECT CURDATE() - INTERVAL 5 DAY
             UNION ALL SELECT CURDATE() - INTERVAL 6 DAY) a
                LEFT JOIN
                (SELECT t.l_ins_id, DATE(t.d_createdate) AS createdate
                    FROM bpm_flow_instance t
                     JOIN bpm_flow_model c ON t.l_model_id = c.l_model_id
                        WHERE t.n_state IN (1, 2)
                        <if test="flowInstance.dept!=null">
                            and c.v_dept = #{flowInstance.dept}
                        </if>
                          AND (c.n_control = -1
                           OR c.n_control = 1
                           OR (c.n_control = 0 AND c.n_control_url != '' AND c.n_control_url IS NOT NULL))
                            ) AS filtered_t ON filtered_t.createdate = a.total_date
        GROUP BY
            a.total_date
        ORDER BY
            a.total_date;
    </select>

    <select id="getSevenDayFlowInsFinishCount" resultType="java.util.Map">
        SELECT
            DATE_FORMAT(a.total_date,'%Y-%m-%d')  AS totalDate,
            IFNULL(COUNT(filtered_t.l_ins_id), 0) AS totalCount
        FROM
            (SELECT CURDATE() - INTERVAL 0 DAY AS total_date
             UNION ALL SELECT CURDATE() - INTERVAL 1 DAY
             UNION ALL SELECT CURDATE() - INTERVAL 2 DAY
             UNION ALL SELECT CURDATE() - INTERVAL 3 DAY
             UNION ALL SELECT CURDATE() - INTERVAL 4 DAY
             UNION ALL SELECT CURDATE() - INTERVAL 5 DAY
             UNION ALL SELECT CURDATE() - INTERVAL 6 DAY) a
                LEFT JOIN
                (SELECT t.l_ins_id, DATE(t.d_finishdate) AS finishdate
                    FROM bpm_flow_instance t
                             JOIN bpm_flow_model c ON t.l_model_id = c.l_model_id
                WHERE t.n_state = 2
                <if test="flowInstance.dept!=null">
                    and c.v_dept = #{flowInstance.dept}
                </if>
                  AND (c.n_control = -1
                   OR c.n_control = 1
                   OR (c.n_control = 0 AND c.n_control_url != '' AND c.n_control_url IS NOT NULL))
                    ) AS filtered_t ON filtered_t.finishdate = a.total_date
        GROUP BY
            a.total_date
        ORDER BY
            a.total_date;
    </select>
    <select id="selectPageAllDealing" resultMap="flowInstanceResultMap">
        SELECT
            *
        FROM
            (
                SELECT
                    t.l_ins_id,
                    t.v_ins_name,
                    t.v_ins_remark,
                    t.be_careful,
                    t.l_model_id,
                    t.n_state,
                    t.v_creator,
                    t.d_createdate,
                    t.d_canceldate,
                    t.d_finishdate,
                    c.v_allow_cancels,
                    c.v_model_name,
                    c.n_is_cancel,
                    c.n_end_cancel,
                    c.n_cancel_can_read,
                    ( CASE WHEN t.n_control IS NOT NULL THEN t.n_control ELSE c.n_control END ) AS n_control,
                    c.n_control_url,
                    c.n_custom_cancel,
                    t.l_form_ins_id,
                    t.l_form_id,
                    t.l_file_id,
                    t.l_tache_ins_start,
                    t.l_groupid,
                    t.l_office_id,
                    t.l_groupxuserid,
                    t.l_pdf_id,
                    c.n_is_custom_print
                FROM
                    bpm_flow_instance t
                        LEFT JOIN bpm_flow_model c ON t.l_model_id = c.l_model_id
                WHERE
                    1 = 1
                  AND t.n_state = 1
                  <if test="flowInstance.dept!=null">
                      AND c.v_dept = #{flowInstance.dept}
                  </if>
                  AND (
                            c.n_control =- 1
                        OR c.n_control = 1
                        OR ( c.n_control = 0 AND c.n_control_url != '' AND c.n_control_url IS NOT NULL )
                    )
                GROUP BY
                    t.l_ins_id
            ) s
        WHERE
            1 = 1
        ORDER BY
            s.d_createdate DESC
    </select>
    <select id="getMaxUseFlows" resultType="java.util.Map">
        SELECT
            c.v_model_name AS modelName,
            count( t.l_ins_id ) AS flowCount
        FROM
            bpm_flow_instance t
                LEFT JOIN bpm_flow_model c ON t.l_model_id = c.l_model_id
        WHERE
            1 = 1
          AND t.n_state IN ( 1, 2 )
          <if test="flowInstance.dept!=null">
             AND c.v_dept = #{flowInstance.dept}
          </if>
          AND (
                    c.n_control =- 1
                OR c.n_control = 1
                OR ( c.n_control = 0 AND c.n_control_url != '' AND c.n_control_url IS NOT NULL )
            )
        GROUP BY
            modelName
        ORDER BY
            flowCount DESC
        LIMIT 5
    </select>
    <select id="getTotalFlowInsCount" resultType="java.lang.Integer">
        SELECT
            count( t.l_ins_id ) AS flowCount
        FROM
            bpm_flow_instance t
                LEFT JOIN bpm_flow_model c ON t.l_model_id = c.l_model_id
        WHERE
            1 = 1
          AND t.n_state IN ( 1, 2 )
            <if test="flowInstance.dept!=null">
                AND c.v_dept = #{flowInstance.dept}
            </if>
          AND (
                    c.n_control =- 1
                OR c.n_control = 1
                OR ( c.n_control = 0 AND c.n_control_url != '' AND c.n_control_url IS NOT NULL )
            )
    </select>
    <select id="listAllDealing" resultMap="flowInstanceResultMap">
        SELECT
        *
        FROM
        (
        SELECT
        t.l_ins_id,
        t.v_ins_name,
        t.v_ins_remark,
        t.be_careful,
        t.l_model_id,
        t.n_state,
        t.v_creator,
        t.d_createdate,
        t.d_canceldate,
        t.d_finishdate,
        c.v_allow_cancels,
        c.v_model_name,
        c.n_is_cancel,
        c.n_end_cancel,
        c.n_cancel_can_read,
        ( CASE WHEN t.n_control IS NOT NULL THEN t.n_control ELSE c.n_control END ) AS n_control,
        c.n_control_url,
        c.n_custom_cancel,
        t.l_form_ins_id,
        t.l_form_id,
        t.l_file_id,
        t.l_tache_ins_start,
        t.l_groupid,
        t.l_office_id,
        t.l_groupxuserid,
        t.l_pdf_id,
        c.n_is_custom_print
        FROM
        bpm_flow_instance t
        LEFT JOIN bpm_flow_model c ON t.l_model_id = c.l_model_id
        WHERE
        1 = 1
        AND t.n_state = 1
        <if test="flowInstance.dept!=null">
            AND c.v_dept = #{flowInstance.dept}
        </if>
        AND (
        c.n_control =- 1
        OR c.n_control = 1
        OR ( c.n_control = 0 AND c.n_control_url != '' AND c.n_control_url IS NOT NULL )
        )
        GROUP BY
        t.l_ins_id
        ) s
        WHERE
        1 = 1
        ORDER BY
        s.d_createdate DESC
    </select>
    <select id="getSevenDayFlowInsInfo" resultType="cn.com.oa.model.vo.FlowInstanceVo">
        SELECT
            DATE ( a.d_createdate ) AS totalDate,
            b.v_model_name AS modelName,
            COUNT(a.l_model_id) AS count
        FROM bpm_flow_instance a
            JOIN bpm_flow_model b ON a.l_model_id = b.l_model_id
        WHERE a.d_createdate BETWEEN CURDATE() - INTERVAL 6 DAY AND CURDATE() AND a.n_state != 0
        GROUP BY totalDate, b.v_model_name;
    </select>
</mapper>
