<?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.nbclass.szxgl.mapper.GraphicStatisticsMapper">

	<select id="getProjectNumDataByPie" resultType="java.util.Map">
		SELECT COUNT(*) AS value, _area AS name FROM project 
		LEFT JOIN project_area ON project._area = project_area.area 
		where 1=1 
		<if test="starttime != null and starttime != ''">
			<![CDATA[ AND project._create_time >= #{starttime} ]]>
		</if>
		<if test="endtime != null and endtime != ''">
			<![CDATA[ AND project._create_time <= #{endtime} ]]>
		</if>
		GROUP BY _area ORDER BY project_area.id
	</select>
	
	<select id="getProjectNumDataByLine" resultType="java.util.Map">
		<!-- 
		按周: DATE_FORMAT(_create_time,'%Y%u')
		按月：DATE_FORMAT(_create_time,'%Y%m')
		-->
		SELECT COUNT(*) AS num, _area as area, DATE_FORMAT(_create_time,'%Y%m') AS weeks FROM project 
		LEFT JOIN project_area ON project._area = project_area.area 
		where 1=1 
		<if test="starttime != null and starttime != ''">
			<![CDATA[ AND project._create_time >= #{starttime} ]]>
		</if>
		<if test="endtime != null and endtime != ''">
			<![CDATA[ AND project._create_time <= #{endtime} ]]>
		</if>
		GROUP BY weeks, _area ORDER BY weeks, project_area.id 
	</select>
  	
  		<select id="queryPersonProject" resultType="java.util.Map">
		SELECT COUNT(1)AS num,sy.true_name FROM(
		SELECT p.id,  pu._table_id AS user_id,p._create_time FROM project p LEFT JOIN project_user pu ON p.id = pu._project_id WHERE pu._type='1'
		  UNION
		SELECT p.id,ptu._user_id AS user_id,p._create_time FROM project p LEFT JOIN project_task_user ptu ON p.id = ptu._project_id
		)t LEFT JOIN sy_users sy ON t.user_id = sy.user_name
		WHERE sy.true_name is not null 
		<if test="starttime != null and starttime != ''">
			<![CDATA[ AND t._create_time >= #{starttime} ]]>
		</if>
		<if test="endtime != null and endtime != ''">
			<![CDATA[ AND t._create_time <= #{endtime} ]]>
		</if>
		<if test="deptId != null">
		 AND sy.dept_id=#{deptId}
		 </if>
		 <if test="userId != null">
		 AND sy.user_name=#{userId}
		 </if>
		  
		GROUP BY t.user_id ORDER BY num DESC
	</select>
	
	<select id="queryMonthProjectLine" resultType="map">	
	      SELECT COUNT(1) AS num, 
	            DATE_FORMAT(t._create_time, "%m") AS `month`
	      FROM(
				SELECT
				   distinct p.id,p._create_time
				FROM
				   project p 
						LEFT JOIN 
							project_user pu ON p.id = pu._project_id
						LEFT JOIN sy_users su ON pu._table_id = su.user_name
						WHERE
							pu._type = '1'
						AND DATE_FORMAT(p._create_time, "%Y") =#{year}
						<if test="deptId!=null">
						AND FIND_IN_SET(#{deptId},su.dept_id)
							<!-- AND su.dept_id like concat('%', #{deptId}, '%') -->
							</if>
					 )t	
						GROUP BY
							`month`
						ORDER BY
							`month`

	</select>
	
	<select id="queryDepartmentLable" resultType="map">	
	      SELECT 
			 CASE 
				WHEN qp.Id IS NULL THEN q.`Name`
				ELSE CONCAT(q.`Name`,'-',qp.`Name`)
				END
				AS  lable,
			CASE 
				WHEN qp.Id IS NULL THEN q.Id
				ELSE qp.Id
				END
			    AS value
			 FROM qywx_party q LEFT JOIN qywx_party qp ON q.Id = qp.ParentId WHERE q.ParentId =1
			ORDER BY lable
	</select>
</mapper>