<?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.rongwei.sfcommon.sys.dao.AccidentDao">
    <select id="selectReport" resultType="com.rongwe.scentity.domian.AspAccidentReport">
        WITH ORG_USER_COUNT AS (
        SELECT
        c.ID,
        max( c.FULLNAME ) AS FULLNAME,
        max( c.TENANTID ) AS TENANTID,
        max( c.orgids ) AS orgids,
        COUNT( suo.USERID ) AS ORG_TOTAL
        FROM
        (
        SELECT
        a.ID,
        a.FULLNAME,
        SUBSTRING_INDEX( SUBSTRING_INDEX( a.FULLPID, ',', 2 ), ',', - 1 ) AS TENANTID,
        IFNULL( CONCAT( GROUP_CONCAT( DISTINCT b.ID ), ',', a.ID ), a.ID ) AS orgids
        FROM
        sys_organization a
        LEFT JOIN sys_organization b ON FIND_IN_SET( a.ID, b.FULLPID )
        AND b.DELETED = '0'
        WHERE
        a.DELETED = '0'
        AND a.ORGTYPE = 'chejian'
        <if test="tenantId != null">AND FIND_IN_SET(#{tenantId}, a.FULLPID ) </if>
        GROUP BY
        a.ID,
        a.TENANTID
        ) c
        LEFT JOIN sys_user_org suo ON FIND_IN_SET( suo.ORGID, c.orgids )
        AND suo.DELETED = '0'
        GROUP BY
        c.ID
        ) SELECT
        ouc.ID as departid,
        max( ouc.FULLNAME ) AS departname,
        max( ouc.TENANTID ) AS TENANTID,
        max( ouc.orgids ) AS orgids,
        max( ouc.ORG_TOTAL ) AS departpeople,
        COUNT( aa.ID ) AS count,
        IFNULL(round( COUNT( aa.ID )/ max( ouc.ORG_TOTAL )* 100, 2 ),0.00) AS value
        FROM
        ORG_USER_COUNT ouc
        LEFT JOIN asp_accident aa ON FIND_IN_SET( aa.ACCIDENTDEPT, ouc.orgids )
        AND aa.DECLAREJOBINJURED = '1'
        and aa.DELETED = 0
        <if test="year != null">AND YEAR(aa.HAPPENDATE) = #{year}</if>
        <if test = 'month != -1'>AND MONTH(aa.HAPPENDATE) = #{month}</if>
        <if test="tenantId != null">AND aa.TENANTID= #{tenantId}</if>
        GROUP BY
        ouc.ID
    </select>
</mapper>
