| | |
|---|
| | | <select id="selectIndexDataKanban" resultType="com.panzhihua.common.model.vos.IndexDataKanbanVO"> |
|---|
| | | SELECT |
|---|
| | | COUNT(user_id)allUser, |
|---|
| | | COUNT(case WHEN DATE_FORMAT(create_at,'%m-%d-%Y')=DATE_FORMAT(SYSDATE(),'%m-%d-%Y') then user_id else null end )addUser, |
|---|
| | | COUNT(case WHEN DATE_FORMAT(t.create_at,'%m-%d-%Y')=DATE_FORMAT(SYSDATE(),'%m-%d-%Y') then user_id else null end )addUser, |
|---|
| | | COUNT(case WHEN DATE_FORMAT(last_login_time,'%m-%d-%Y')=DATE_FORMAT(SYSDATE(),'%m-%d-%Y') then user_id else null end )activeDayUser, |
|---|
| | | (SELECT count(user_id) FROM sys_user WHERE type = 1 AND last_login_time > date_sub(SYSDATE(), INTERVAL 7 DAY))activeWeekUser |
|---|
| | | FROM |
|---|
| | | sys_user |
|---|
| | | where type=1 and community_id is not null |
|---|
| | | sys_user t LEFT JOIN com_act t1 on t.community_id = t1.community_id |
|---|
| | | where t.type=1 and t.community_id is not null and t1.area_code ='510423' |
|---|
| | | </select> |
|---|
| | | |
|---|
| | | <select id="selectCommunityUserOrder" resultType="com.panzhihua.service_user.model.dtos.DataKanbanDTO"> |
|---|
| | |
|---|
| | | FROM |
|---|
| | | com_act a |
|---|
| | | LEFT JOIN sys_user u ON a.community_id = u.community_id |
|---|
| | | and u.type=1 and u.create_at <![CDATA[ >= ]]> DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d 00:00:00'),INTERVAL 0 DAY) |
|---|
| | | and u.type=1 and u.create_at >= DATE_ADD(curdate(),interval -day(curdate())+1 day) where a.area_code = '510423' |
|---|
| | | GROUP BY a.community_id)t ORDER BY t.num desc |
|---|
| | | </select> |
|---|
| | | |
|---|
| | |
|---|
| | | LEFT JOIN com_street AS cs ON cs.street_id = ca.street_id |
|---|
| | | WHERE |
|---|
| | | su.type = 1 |
|---|
| | | AND su.community_id IS NOT NULL |
|---|
| | | AND su.community_id IS NOT NULL and ca.area_code = '510423' |
|---|
| | | GROUP BY |
|---|
| | | ca.community_id |
|---|
| | | ORDER BY |
|---|
