| | |
|---|
| | | |
|---|
| | | import org.apache.ibatis.annotations.Mapper; |
|---|
| | | import org.apache.ibatis.annotations.Param; |
|---|
| | | import org.apache.ibatis.annotations.Select; |
|---|
| | | |
|---|
| | | import com.baomidou.mybatisplus.core.mapper.BaseMapper; |
|---|
| | | import com.baomidou.mybatisplus.core.metadata.IPage; |
|---|
| | |
|---|
| | | **/ |
|---|
| | | @Mapper |
|---|
| | | public interface SysUserInputDAO extends BaseMapper<SysUserInputDO> { |
|---|
| | | // @Select("<script> " + |
|---|
| | | // " <where>" + |
|---|
| | | // "<if test='nameCn != null and nameCn.trim() != ""'>" + |
|---|
| | | // " </if> " + |
|---|
| | | // "<if test='nameCn != null and nameCn.trim() != ""'>" + |
|---|
| | | // " </if> " + |
|---|
| | | // "<if test='nameCn != null and nameCn.trim() != ""'>" + |
|---|
| | | // " </if> " + |
|---|
| | | // " </where>" + |
|---|
| | | // "</script>") |
|---|
| | | @Select("select a.id,a.community_id from com_mng_struct_area a where a.area_name=#{areaName}") |
|---|
| | | |
|---|
| | | ComMngStructAreaVO selectByAreaName(String areaName); |
|---|
| | | |
|---|
| | | @Select("SELECT " |
|---|
| | | + "REPLACE(REPLACE(REPLACE(REPLACE(h.house_name,a.address_detail,''),'栋','-'),'单元','-'),'层','-')houseName, " |
|---|
| | | + "h.house_code " + "FROM " + " com_mng_struct_area a " |
|---|
| | | + " join com_mng_struct_house h on h.house_name like CONCAT(a.address_detail,'%') and h.type=5 " + "WHERE " |
|---|
| | | + " a.area_name = #{areaName}") |
|---|
| | | |
|---|
| | | List<ComMngStructHouseVO> selectHouserByareaName(String areaName); |
|---|
| | | |
|---|
| | | @Select("SELECT " + "p.name, " + "p.id, " + "if(p.is_rent=1,'合租','家人')relationship, " + "p.id_card, " + "p.phone, " |
|---|
| | | + "year(curdate())-if(length(p.id_card)=18,substring(p.id_card,7,4),if(length(p.id_card)=15,concat('19',substring(p.id_card,7,2)),null)) as age, " |
|---|
| | | + "p.create_at " + "FROM " + " sys_user_input p " + "WHERE " + " p.door_number = #{doorNumber} " |
|---|
| | | + " AND p.id !=#{userId}") |
|---|
| | | |
|---|
| | | List<ComMngFamilyInfoVO> selectListFamily(@Param("doorNumber") String doorNumber, @Param("userId") Long userId); |
|---|
| | | |
|---|
| | | @Select("<script> " + "SELECT " + " t.id, " + " t.`name`, " + "CASE " + " IF " + " ( " |
|---|
| | | + " length( t.id_card ) = 18, " + " cast( substring( t.id_card, 17, 1 ) AS UNSIGNED ) % 2, " + " IF " |
|---|
| | | + " ( " + " length( t.id_card ) = 15, " + " cast( substring( t.id_card, 15, 1 ) AS UNSIGNED ) % 2, " |
|---|
| | | + " 3 " + " ) " + " ) " + " WHEN 1 THEN " + " 1 " + " WHEN 0 THEN " + " 2 ELSE 0 " |
|---|
| | | + " END AS sex, " + " t.phone, " + " YEAR ( now( ) ) - YEAR ( substring( t.id_card, 7, 8 ) ) age, " |
|---|
| | | + " t1.area_name as areaName, " + " t.political_outlook politicalOutlook, " + " CONCAT( " + " IF " |
|---|
| | | + " ( t.soldier = 1, '军人,', '' ), " + " IF " + " ( t.lowIncome_households = 1, '低保户,', '' ), " + " IF " |
|---|
| | | + " ( t.lowIncome_people = 1, '低收入,', '' ), " + " IF " + " ( t.old_people = 1, '特服家庭,', '' ), " + " IF " |
|---|
| | | + " ( t.key_personnel = 1, '重点人员,', '' ) " + " ) tags, " + " t.create_at as createAt " + "FROM " |
|---|
| | | + " sys_user_input t " + " LEFT JOIN com_mng_struct_area t1 ON t.area_id = t1.id " + "WHERE " |
|---|
| | | + " t.community_id = #{pageInputUserDTO.communityId}" |
|---|
| | | + "<if test='pageInputUserDTO.name != null and pageInputUserDTO.name.trim() != ""'>" |
|---|
| | | + " and t.`name` like concat(#{pageInputUserDTO.name},'%') " + " </if> " |
|---|
| | | + "<if test='pageInputUserDTO.phone != null and pageInputUserDTO.phone.trim() != ""'>" |
|---|
| | | + " and t.phone like concat(#{pageInputUserDTO.phone},'%') " + " </if> " |
|---|
| | | + "<if test='pageInputUserDTO.areaName != null and pageInputUserDTO.areaName.trim() != ""'>" |
|---|
| | | + "AND t1.`area_name` like concat(#{pageInputUserDTO.areaName},'%') " + " </if> " + " group by t.id_card " |
|---|
| | | + "</script>") |
|---|
| | | |
|---|
| | | IPage<InputUserInfoVO> pageInputUser(Page page, @Param("pageInputUserDTO") PageInputUserDTO pageInputUserDTO); |
|---|
| | | |
|---|
| | | @Select("select a.id,a.community_id,a.area_name,address_detail from com_mng_struct_area a where a.community_id=#{communityId}") |
|---|
| | | |
|---|
| | | List<ComMngStructAreaVO> selectComMngStructArea(@Param("communityId") Long communityId); |
|---|
| | | |
|---|
| | | @Select("<script> " + "SELECT * FROM ( " + " SELECT " + " t.id, " + " t.`name`, " + "CASE " + " IF " + " ( " |
|---|
| | | + " length( t.id_card ) = 18, " + " cast( substring( t.id_card, 17, 1 ) AS UNSIGNED ) % 2, " + " IF " |
|---|
| | | + " ( " + " length( t.id_card ) = 15, " + " cast( substring( t.id_card, 15, 1 ) AS UNSIGNED ) % 2, " |
|---|
| | | + " 3 " + " ) " + " ) " + " WHEN 1 THEN " + " 1 " + " WHEN 0 THEN " + " 2 ELSE 0 " |
|---|
| | | + " END AS sex, " + " t.phone, " + " YEAR ( now( ) ) - YEAR ( substring( t.id_card, 7, 8 ) ) age, " |
|---|
| | | + " t1.area_name as areaName, " + " t.political_outlook politicalOutlook, " + " CONCAT( " + " IF " |
|---|
| | | + " ( t.soldier = 1, '军人,', '' ), " + " IF " + " ( t.lowIncome_households = 1, '低保户,', '' ), " + " IF " |
|---|
| | | + " ( t.lowIncome_people = 1, '低收入,', '' ), " + " IF " + " ( t.old_people = 1, '特服家庭,', '' ), " + " IF " |
|---|
| | | + " ( t.key_personnel = 1, '重点人员,', '' ), " + " IFNULL(t.tags,'') " + " ) tags, " |
|---|
| | | + " t2.house_name as address, " + " t.create_at as createAt " + "FROM " + " sys_user_input t " |
|---|
| | | + " LEFT JOIN com_mng_struct_area t1 ON t.area_id = t1.id " |
|---|
| | | + " LEFT JOIN com_mng_struct_house t2 ON t.house_code = t2.house_code " + "WHERE " |
|---|
| | | + " t.community_id = #{pageInputUserDTO.communityId}" + " AND " |
|---|
| | | + "(t.soldier=1 OR t.lowIncome_households=1 OR t.lowIncome_people=1 or t.old_people=1 OR t.special_service_family=1 OR t.key_personnel=1 OR (t.tags IS NOT NULL AND TRIM(t.tags)!=''))" |
|---|
| | | + "<if test='pageInputUserDTO.name != null and pageInputUserDTO.name.trim() != ""'>" |
|---|
| | | + " and t.`name` like concat(#{pageInputUserDTO.name},'%') " + " </if> " |
|---|
| | | + "<if test='pageInputUserDTO.phone != null and pageInputUserDTO.phone.trim() != ""'>" |
|---|
| | | + " and t.phone like concat(#{pageInputUserDTO.phone},'%') " + " </if> " |
|---|
| | | + "<if test='pageInputUserDTO.areaName != null and pageInputUserDTO.areaName.trim() != ""'>" |
|---|
| | | + "AND t1.`area_name` like concat(#{pageInputUserDTO.areaName},'%') " + " </if> " + " group by t.id_card " |
|---|
| | | + " ) tt WHERE 1=1 " |
|---|
| | | + "<if test='pageInputUserDTO.tags != null and pageInputUserDTO.tags.trim() != ""'>" |
|---|
| | | + " and tt.tags like concat('%',#{pageInputUserDTO.tags},'%') " + " </if> " + "</script>") |
|---|
| | | |
|---|
| | | IPage<InputUserInfoVO> specialInputUser(Page page, @Param("pageInputUserDTO") PageInputUserDTO pageInputUserDTO); |
|---|
| | | |
|---|
| | | @Select("" + "SELECT " + " t1.house_code houseCode, " + " t1.house_name houseName, " + " t1.square, " + " t1.state " |
|---|
| | | + "FROM " + " sys_user_input t " + " LEFT JOIN com_mng_struct_house t1 ON t.house_code = t1.house_code " |
|---|
| | | + " WHERE" + " t.id_card = #{idCard}") |
|---|
| | | |
|---|
| | | List<ComMngStructHouseVO> selectUserHouseList(@Param("idCard") String idCard); |
|---|
| | | |
|---|
| | | @Select("<script> " + "SELECT " + "u.id 'order', " + "c.`area_name` areaName, " + "u.door_number doorNumber, " |
|---|
| | | + "if(u.is_rent=1,'租住','自主')isRent, " + "u.name, " + "u.nation, " |
|---|
| | | + "case u.political_outlook when 1 then '党员' when 2 then '团员' else '群众' end politicalOutlook, " |
|---|
| | | + "if(u.marital_status=1,'已婚','未婚')maritalStatus, " + "u.phone, " + "u.education, " + "u.id_card, " |
|---|
| | | + "u.company, " + "u.residence, " + "if(u.is_panzhiHua=1,'是','否') isPanZhiHua, " + "u.situation, " |
|---|
| | | + "if(is_contact=1,'是','否') isContact, " + "if(u.major=1,'是','否') major, " + "if(u.soldier=1,'是','否') soldier, " |
|---|
| | | + "if(u.disability=1,'是','否') disability, " + "if(u.lowIncome_households=1,'是','否') lowIncomeHouseholds, " |
|---|
| | | + "if(u.lowIncome_people=1,'是','否') lowIncomePeople, " + "if(u.old_people=1,'是','否') oldPeople, " |
|---|
| | | + "if(u.special_service_family=1,'是','否') specialServiceFamily, " |
|---|
| | | + "if(u.key_personnel=1,'是','否') keyPersonnel, " + "u.tags " + "FROM " + " sys_user_input u " |
|---|
| | | + " where u.community_id=#{exportUserDTO.communityId} " |
|---|
| | | + "<if test='exportUserDTO.areaName != null and exportUserDTO.areaName.trim() != ""'>" |
|---|
| | | + " JOIN com_mng_struct_area c ON u.area_id = c.id " + " </if> " |
|---|
| | | + "<if test='exportUserDTO.areaName == null or exportUserDTO.areaName.trim() == ""'>" |
|---|
| | | + "LEFT JOIN com_mng_struct_area c ON u.area_id = c.id " + " </if> " |
|---|
| | | + "<if test='exportUserDTO.areaName != null and exportUserDTO.areaName.trim() != ""'>" |
|---|
| | | + "AND c.`area_name` like concat(#{exportUserDTO.areaName},'%') " + " </if> " |
|---|
| | | + "<if test='exportUserDTO.phone != null and exportUserDTO.phone.trim() != ""'>" |
|---|
| | | + "AND u.phone like concat(#{exportUserDTO.phone},'%') " + " </if> " |
|---|
| | | + "<if test='exportUserDTO.name != null and exportUserDTO.name.trim() != ""'>" |
|---|
| | | + " and u.`name` like concat(#{exportUserDTO.name},'%') " + " </if> " + "</script>") |
|---|
| | | |
|---|
| | | List<EexcelUserDTO> selectExport(@Param("exportUserDTO") ExportUserDTO exportUserDTO); |
|---|
| | | |
|---|
| | | @Select("<script> " + "SELECT " + "u.id 'order', " + "c.`area_name` areaName, " + "u.door_number doorNumber, " |
|---|
| | | + "if(u.is_rent=1,'租住','自主')isRent, " + "u.name, " + "u.nation, " |
|---|
| | | + "case u.political_outlook when 1 then '党员' when 2 then '团员' else '群众' end politicalOutlook, " |
|---|
| | | + "if(u.marital_status=1,'已婚','未婚')maritalStatus, " + "u.phone, " + "u.education, " + "u.id_card, " |
|---|
| | | + "u.company, " + "u.residence, " + "if(u.is_panzhiHua=1,'是','否') isPanZhiHua, " + "u.situation, " |
|---|
| | | + "if(is_contact=1,'是','否') isContact, " + "if(u.major=1,'是','否') major, " + "if(u.soldier=1,'是','否') soldier, " |
|---|
| | | + "if(u.disability=1,'是','否') disability, " + "if(u.lowIncome_households=1,'是','否') lowIncomeHouseholds, " |
|---|
| | | + "if(u.lowIncome_people=1,'是','否') lowIncomePeople, " + "if(u.old_people=1,'是','否') oldPeople, " |
|---|
| | | + "if(u.special_service_family=1,'是','否') specialServiceFamily, " |
|---|
| | | + "if(u.key_personnel=1,'是','否') keyPersonnel, " + "u.tags " + "FROM " + " sys_user_input u " |
|---|
| | | + " JOIN com_mng_struct_area c ON u.area_id = c.id " |
|---|
| | | + " where u.community_id=#{exportSpecialUserDTO.communityId} " |
|---|
| | | + "<if test='exportSpecialUserDTO.areaName != null and exportSpecialUserDTO.areaName.trim() != ""'>" |
|---|
| | | + "AND c.`area_name` like concat(#{exportUserDTO.areaName},'%') " + " </if> " |
|---|
| | | + "<if test='exportSpecialUserDTO.name != null and exportSpecialUserDTO.name.trim() != ""'>" |
|---|
| | | + " and u.`name` like concat(#{exportSpecialUserDTO.name},'%') " + " </if> " |
|---|
| | | + "<if test='exportSpecialUserDTO.name != null and exportSpecialUserDTO.name.trim() != ""'>" |
|---|
| | | + " and u.`name` like concat(#{exportSpecialUserDTO.name},'%') " + " </if> " |
|---|
| | | + "<if test='exportSpecialUserDTO.tags != null and exportSpecialUserDTO.tags.trim() != ""'>" |
|---|
| | | + " CONCAT( " + " IF " + " ( t.soldier = 1, '军人,', '' ), " + " IF " |
|---|
| | | + " ( t.lowIncome_households = 1, '低保户,', '' ), " + " IF " + " ( t.lowIncome_people = 1, '低收入,', '' ), " |
|---|
| | | + " IF " + " ( t.old_people = 1, '特服家庭,', '' ), " + " IF " + " ( t.key_personnel = 1, '重点人员,', '' ), " |
|---|
| | | + " IFNULL(t.tags,'') " + " ) like concat(#{exportSpecialUserDTO.tags},'%')" + " </if> " + "</script>") |
|---|
| | | |
|---|
| | | List<EexcelUserDTO> specialUserExport(@Param("exportSpecialUserDTO") ExportSpecialUserDTO exportSpecialUserDTO); |
|---|
| | | } |
|---|
