<?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.bonus.system.index.dao.HomePageDao">

	<select id="getTodayAttData" resultType="com.bonus.system.index.entity.IndexTodayAttBean">
		select a.shouldAttNum, a.actualAttNum,(a.shouldAttNum - a.actualAttNum) as noActualAttNum, TRUNCATE(a.actualAttNum /
		       a.shouldAttNum * 100, 0) as attRate from (
		  select count(agpr.user_id ) as shouldAttNum, count(a.user_id) as actualAttNum
		  from sys_user su left join (
		  	  select * from v_att_update_data where att_current_day = #{date}
		  	  and (toWorkAttStatus = 1 or offWorkAttStatus = 1)
		  ) a on a.user_id = su.user_id
	   		LEFT JOIN att_group_person_relation agpr on agpr.user_id = su.user_id and agpr.is_active = 1
		  where su.is_active = 1
	  	) a
	</select>

	<select id="getTodayAbnormalData" resultType="com.bonus.system.index.entity.IndexAbnormalBean">
		select IFNULL(sum(toWorkAttStatus = 2 or offWorkAttStatus = 2), 0) as lateNum,
			   IFNULL(sum(toWorkAttStatus = 4 or offWorkAttStatus = 4), 0) as earlyNum,
			   IFNULL(sum(toWorkAttStatus = 3 or offWorkAttStatus = 3), 0) as skippingNum,
			   IFNULL(sum(toWorkAttStatus in(SELECT dict_value
		FROM sys_dict_data
		where dict_type = 'att_status' and is_leave = '1' ) or
						  offWorkAttStatus in(SELECT dict_value
		FROM sys_dict_data
		where dict_type = 'att_status' and is_leave = '1')), 0) as leaveNum,
			   IFNULL(sum(toWorkAttStatus = 9 or offWorkAttStatus = 9), 0) as addressErrorNum,
			   IFNULL(sum(toWorkAttStatus = 8 or offWorkAttStatus = 8), 0) as einErrorNum
		from v_att_update_data
		where att_current_day = #{date}
	</select>

	<select id="getMonthAbnormalData" resultType="com.bonus.system.index.entity.IndexMonthAbnormalBean">
		select sum(examine_status = 0 ) as unProcessedData,
			   sum(examine_status = 1 or examine_status = 2 or examine_status = 3) as processedData
		from leave_apply
		where #{month} BETWEEN DATE_FORMAT(leave_start_date, '%Y-%m')
		and DATE_FORMAT(leave_end_date, '%Y-%m')
		and (leave_type  like '%外出%' or leave_type  like '%轮休%')
		and is_active = 1
	</select>

	<select id="getOrgNumData" resultType="com.bonus.system.index.entity.IndexOrgNumBean">
		select id as orgId, org_name, parent_id, count(DISTINCT suo.user_id) as personNum
		from sys_organization org left join sys_user_org suo on suo.org_id = org.id
		and suo.is_active = 1 where org.is_active = 1
		GROUP BY id
	</select>

	<select id="getMonthAbnormalTypeData" resultType="com.bonus.system.index.entity.IndexAbnormalBean">
		select IFNULL(sum(toWorkAttStatus = 2 or offWorkAttStatus = 2), 0) as lateNum,
			   IFNULL(sum(toWorkAttStatus = 4 or offWorkAttStatus = 4), 0) as earlyNum,
			   IFNULL(sum(toWorkAttStatus = 3 or offWorkAttStatus = 3), 0) as skippingNum,
			   IFNULL(sum(toWorkAttStatus in(SELECT dict_value
		FROM sys_dict_data
		where dict_type = 'att_status' and is_leave = '1') or
						  offWorkAttStatus in(SELECT dict_value
		FROM sys_dict_data
		where dict_type = 'att_status' and is_leave = '1')), 0) as leaveNum,
			   IFNULL(sum(toWorkAttStatus = 9 or offWorkAttStatus = 9), 0) as addressErrorNum,
			   IFNULL(sum(toWorkAttStatus = 8 or offWorkAttStatus = 8), 0) as einErrorNum
		from v_att_update_data
		where DATE_FORMAT(att_current_day, '%Y-%m') = #{month}
	</select>

	<select id="getMapData" resultType="com.bonus.system.index.entity.MapBean">
		select org.id as orgId, org_name, province, lon, lat, count(DISTINCT suo.user_id) as personNum
		from sys_organization org
		left join sys_user_org suo on suo.org_id = org.id and suo.is_active = 1
		where is_department = 1 and reviewer_status = 1 and org.is_active = 1
		GROUP BY org.id
	</select>
</mapper>
