java/zhihuishenqu.git

			@@ -244,6 +244,7 @@
			",(select count(id) from com_mng_population where act_id = #{communityId} and out_or_local = 1) as localTotal " +
			",(select count(id) from com_mng_population where act_id = #{communityId} and out_or_local = 2) as outTotal " +
			",(select count(id) from com_mng_population where act_id = #{communityId} and label is not null) as specialTotal " +
			",(select count(village_id) from com_mng_village where community_id = #{communityId}) as villageTotal " +
			" from com_mng_population as cmp where act_id = #{communityId}")
			ComMngPopulationTotalVO getPopulationTotalByAdmin(@Param("communityId") Long communityId);

			@@ -284,7 +285,7 @@
			" egd.grid_community_id = #{communityId} " +
			" AND e.event_deal_status IN ( 1, 4 ) " +
			"ORDER BY " +
			" e.create_at DESC " +
			" e.happen_time DESC " +
			" LIMIT 8")
			List<IndexEventListStatisticsVO> getScreenIndexByEventList(@Param("communityId") Long communityId);

			@@ -305,18 +306,82 @@

			@Select("SELECT " +
			" count( e.id ) AS eventTFTotal, " +
			" IFNULL(( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 1 AND event_status = 2 AND event_deal_status in (1,2,3) AND grid_id = egd.id ),0) AS eventZATotal, " +
			" IFNULL(( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 3 AND event_status = 2 AND event_deal_status in (1,2,3) AND grid_id = egd.id ),0) AS eventMDTotal, " +
			" IFNULL(( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 6 AND event_status = 2 AND event_deal_status in (1,2,3) AND grid_id = egd.id ),0) AS eventTSTotal, " +
			" IFNULL(( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 4 AND event_status = 2 AND event_deal_status in (1,2,3) AND grid_id = egd.id ),0) AS eventBWDTotal, " +
			" IFNULL(( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 2 AND event_status = 2 AND event_deal_status in (1,2,3) AND grid_id = egd.id ),0) AS eventGGTotal " +
			" IFNULL(( " +
			" SELECT " +
			" count( e1.id ) " +
			" FROM " +
			" `event` AS e1 " +
			" LEFT JOIN event_grid_data AS egd1 ON egd1.id = e1.grid_id " +
			" WHERE " +
			" event_category = 1 " +
			" AND event_type = 1 " +
			" AND event_status = 2 " +
			" AND egd1.grid_community_id = #{communityId} " +
			" ), " +
			" 0 " +
			" ) AS eventZATotal, " +
			" IFNULL(( " +
			" SELECT " +
			" count( e2.id ) " +
			" FROM " +
			" `event` AS e2 " +
			" LEFT JOIN event_grid_data AS egd2 ON egd2.id = e2.grid_id " +
			" WHERE " +
			" event_category = 1 " +
			" AND event_type = 3 " +
			" AND event_status = 2 " +
			" AND egd2.grid_community_id = #{communityId} " +
			" ), " +
			" 0 " +
			" ) AS eventMDTotal, " +
			" IFNULL(( " +
			" SELECT " +
			" count( e3.id ) " +
			" FROM " +
			" `event` AS e3 " +
			" LEFT JOIN event_grid_data AS egd3 ON egd3.id = e3.grid_id " +
			" WHERE " +
			" event_category = 1 " +
			" AND event_type = 6 " +
			" AND event_status = 2 " +
			" AND egd3.grid_community_id = #{communityId} " +
			" ), " +
			" 0 " +
			" ) AS eventTSTotal, " +
			" IFNULL(( " +
			" SELECT " +
			" count( e4.id ) " +
			" FROM " +
			" `event` AS e4 " +
			" LEFT JOIN event_grid_data AS egd4 ON egd4.id = e4.grid_id " +
			" WHERE " +
			" event_category = 1 " +
			" AND event_type = 4 " +
			" AND event_status = 2 " +
			" AND egd4.grid_community_id = #{communityId} " +
			" ), " +
			" 0 " +
			" ) AS eventBWDTotal, " +
			" IFNULL(( " +
			" SELECT " +
			" count( e5.id ) " +
			" FROM " +
			" `event` AS e5 " +
			" LEFT JOIN event_grid_data AS egd5 ON egd5.id = e5.grid_id " +
			" WHERE " +
			" event_category = 1 " +
			" AND event_type = 2 " +
			" AND event_status = 2 " +
			" AND egd5.grid_community_id = #{communityId} " +
			" ), " +
			" 0 " +
			" ) AS eventGGTotal " +
			"FROM " +
			" `event` AS e " +
			" LEFT JOIN event_grid_data AS egd ON egd.id = e.grid_id " +
			"WHERE " +
			" e.event_category = 1 " +
			" AND e.event_type = 5 " +
			" AND e.event_deal_status in (1,2,3) " +
			" AND e.event_status = 2 " +
			" AND egd.grid_community_id = #{communityId}")
			IndexGridEventStatisticsVO getGridEventStatisticsList(@Param("communityId") Long communityId);
			@@ -326,17 +391,16 @@

			@Select("SELECT " +
			" count( e.id ) AS eventTFTotal, " +
			" IFNULL(( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 1 AND event_status = 2 AND event_deal_status in (1,2,3) AND grid_id = e.grid_id ),0) AS eventZATotal, " +
			" IFNULL(( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 3 AND event_status = 2 AND event_deal_status in (1,2,3) AND grid_id = e.grid_id) ,0) AS eventMDTotal, " +
			" IFNULL(( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 6 AND event_status = 2 AND event_deal_status in (1,2,3) AND grid_id = e.grid_id ),0) AS eventTSTotal, " +
			" IFNULL(( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 4 AND event_status = 2 AND event_deal_status in (1,2,3) AND grid_id = e.grid_id ),0) AS eventBWDTotal, " +
			" IFNULL(( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 2 AND event_status = 2 AND event_deal_status in (1,2,3) AND grid_id = e.grid_id ),0) AS eventGGTotal " +
			" IFNULL(( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 1 AND event_status = 2 AND grid_id = e.grid_id ),0) AS eventZATotal, " +
			" IFNULL(( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 3 AND event_status = 2 AND grid_id = e.grid_id) ,0) AS eventMDTotal, " +
			" IFNULL(( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 6 AND event_status = 2 AND grid_id = e.grid_id ),0) AS eventTSTotal, " +
			" IFNULL(( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 4 AND event_status = 2 AND grid_id = e.grid_id ),0) AS eventBWDTotal, " +
			" IFNULL(( SELECT count( id ) FROM `event` WHERE event_category = 1 AND event_type = 2 AND event_status = 2 AND grid_id = e.grid_id ),0) AS eventGGTotal " +
			"FROM " +
			" `event` AS e " +
			"WHERE " +
			" e.event_category = 1 " +
			" AND e.event_type = 5 " +
			" AND e.event_deal_status in (1,2,3) " +
			" AND e.event_status = 2 " +
			" AND e.grid_id = #{gridId}")
			IndexGridEventStatisticsVO getGridEventByGirdId(@Param("gridId") Long gridId);
			@@ -586,7 +650,7 @@
			@Select("SELECT " +
			" su.nick_name AS userName, " +
			" su.image_url AS imageUrl, " +
			" e.create_at, " +
			" e.happen_time as createAt, " +
			" e.event_des, " +
			" e.danger_level, " +
			" e.urgent, " +
			@@ -606,7 +670,7 @@
			" AND e.event_deal_status = 1 " +
			" AND e.event_category = 1 " +
			"ORDER BY " +
			" e.create_at DESC " +
			" e.happen_time DESC " +
			" LIMIT 1")
			EventNewStatisticsVO getEventScreenRightTop(@Param("communityId") Long communityId);

			@@ -625,6 +689,7 @@

			@Select("SELECT " +
			" event_type AS type, " +
			" e.id AS eventId, " +
			" happent_lat_lng AS latLng " +
			"FROM " +
			" `event` AS e " +
			@@ -633,18 +698,17 @@
			" e.event_category = 1 " +
			" AND e.event_type IN ( 1, 2, 3, 4, 5, 6 ) " +
			" AND e.event_status = 2 " +
			" AND e.event_deal_status = 1 " +
			" AND egd.grid_community_id = #{communityId} " +
			" AND e.happen_time BETWEEN date_add( curdate(), INTERVAL - DAY ( curdate())+ 1 DAY ) " +
			" AND date_add( curdate()- DAY ( curdate())+ 1, INTERVAL 1 MONTH ) UNION ALL " +
			"SELECT " +
			" IFNULL( NULL, 7 ) AS type, " +
			" id AS eventId, " +
			" lng_lat AS latLng " +
			"FROM " +
			" com_act_easy_photo " +
			"WHERE " +
			" community_id = #{communityId} " +
			" AND handle_status = 1 " +
			" AND del_tag = 0 " +
			" AND lng_lat IS NOT NULL " +
			" AND create_at BETWEEN date_add( curdate(), INTERVAL - DAY ( curdate())+ 1 DAY ) " +
			@@ -672,6 +736,17 @@
			"WHERE " +
			" community_id = #{communityId}")
			List<CivilVillageStatisticsVO> getCivilScreenVillageList(@Param("communityId") Long communityId);

			@Select("SELECT " +
			" count( id ) AS peopleNum, " +
			" ( SELECT count( id ) FROM com_mng_population_house AS cmph WHERE village_id = #{villageId} ) AS houseNum, " +
			" ( SELECT count( id ) FROM com_mng_population WHERE village_id = #{villageId} AND out_or_local = 1 ) AS registerNum, " +
			" ( SELECT count( id ) FROM com_mng_population WHERE village_id = #{villageId} AND out_or_local = 2 ) AS flowNum " +
			"FROM " +
			" com_mng_population AS cmp " +
			"WHERE " +
			" village_id = #{villageId}")
			CivilVillageStatisticsVO getCivilScreenVillageStatistics(@Param("villageId") Long villageId);


			@Select("SELECT COUNT(id) AS man,(SELECT COUNT(id) FROM com_mng_population WHERE sex = 2 AND act_id = #{communityId}) AS woman FROM com_mng_population WHERE sex = 1 AND act_id = #{communityId}")
			@@ -708,4 +783,69 @@
			@Select("select count(user_id) from sys_user where community_id = #{communityId} and type = 1")
			Long countUsedCommunityPopulation(@Param("communityId")Long communityId);

			@Select(
			"SELECT " +
			"(SELECT COUNT(aa.age) FROM " +
			"(SELECT (SELECT TIMESTAMPDIFF(YEAR, birthday, CURDATE()) ) AS age FROM com_mng_population WHERE act_id = #{communityId}) as aa where aa.age<= 16) as age16," +
			"(SELECT COUNT(aa.age) FROM " +
			"(SELECT (SELECT TIMESTAMPDIFF(YEAR, birthday, CURDATE()) ) AS age FROM com_mng_population WHERE act_id = #{communityId}) AS aa WHERE aa.age > 16 and aa.age<= 27) AS age27," +
			"(SELECT COUNT(aa.age) FROM " +
			"(SELECT (SELECT TIMESTAMPDIFF(YEAR, birthday, CURDATE()) ) AS age FROM com_mng_population WHERE act_id = #{communityId}) AS aa WHERE aa.age > 27 and aa.age<= 35) AS age35," +
			"(SELECT COUNT(aa.age) FROM " +
			"(SELECT (SELECT TIMESTAMPDIFF(YEAR, birthday, CURDATE()) ) AS age FROM com_mng_population WHERE act_id = #{communityId}) AS aa WHERE aa.age > 35 and aa.age<= 45) AS age45," +
			"(SELECT COUNT(aa.age) FROM " +
			"(SELECT (SELECT TIMESTAMPDIFF(YEAR, birthday, CURDATE()) ) AS age FROM com_mng_population WHERE act_id = #{communityId}) AS aa WHERE aa.age > 45 and aa.age<= 55) AS age55," +
			"(SELECT COUNT(aa.age) FROM " +
			"(SELECT (SELECT TIMESTAMPDIFF(YEAR, birthday, CURDATE()) ) AS age FROM com_mng_population WHERE act_id = #{communityId}) AS aa WHERE aa.age > 55) AS age55over")
			Map<String, Long> indexCountByAge(@Param("communityId")Long communityId);


			@Select("SELECT " +
			" id, " +
			" su.nick_name AS userName, " +
			" su.image_url, " +
			" caep.create_at, " +
			" caep.detail AS eventDes, " +
			" caep.happen_addr AS happenAddress, " +
			" caep.lng_lat AS happentLatLng, " +
			" caep.photo_path_list, " +
			" IFNULL( NULL, 7 ) AS eventType, " +
			" caep.handle_status AS eventDealStatus " +
			"FROM " +
			" com_act_easy_photo AS caep " +
			" LEFT JOIN sys_user AS su ON su.user_id = caep.sponsor_id " +
			"WHERE " +
			" id = #{eventId}")
			EventNewStatisticsVO getEventScreenSSPDateil(@Param("eventId")Long eventId);

			@Select("SELECT " +
			" su.nick_name AS userName, " +
			" su.image_url AS imageUrl, " +
			" e.create_at as createAt, " +
			" e.event_des, " +
			" e.event_clazz, " +
			" e.danger_level, " +
			" e.urgent, " +
			" e.major, " +
			" e.happen_address, " +
			" e.happent_lat_lng, " +
			" e.event_type, " +
			" e.event_category, " +
			" e.id, " +
			" egd.grid_name, " +
			" e.event_deal_status " +
			"FROM " +
			" `event` AS e " +
			" LEFT JOIN event_grid_data AS egd ON egd.id = e.grid_id " +
			" LEFT JOIN sys_user AS su ON su.user_id = e.grid_member_id " +
			"WHERE " +
			" e.id = #{eventId}")
			EventNewStatisticsVO getEventScreenEventDetail(@Param("eventId") Long eventId);

			@Select("select process_date,process_result from event_transfer_record where event_id = #{eventId}")
			List<EventTransferRecordVO> getEventScreenEventTransList(@Param("eventId") Long eventId);

			@Select("select count(id) from com_elders_auth_elderly WHERE community_id = #{communityId}")
			Integer getStatisticsCount(@Param("communityId") Long communityId);

			}