物业公司信息查询修复

lin

2024-02-27 03f80beb0bec87016d6953624658e50a0b4bab6d

物业公司信息查询修复

 src/main/java/org/springblade/modules/house/mapper/HouseholdMapper.xml

@@ -39,38 +39,37 @@
    </resultMap>

    <sql id="selectHousehold">
        select
            id,
            house_code,
            name,
            phone_number,
            associated_user_id,
            role_type,
            relationship,
            is_primary_contact,
            residential_status,
            gender,
            birthday,
            id_card,
            ethnicity,
            education,
            hukou_registration,
            work_status,
            employer,
            marital_status,
            card_number,
            other_contact,
            current_address,
            disability_cert,
            party_ember,
            create_user,
            create_time,
            update_user,
            update_time,
            remark,
            is_deleted,
            confirm_flag,
            housing_rental_id
        select id,
               house_code,
               name,
               phone_number,
               associated_user_id,
               role_type,
               relationship,
               is_primary_contact,
               residential_status,
               gender,
               birthday,
               id_card,
               ethnicity,
               education,
               hukou_registration,
               work_status,
               employer,
               marital_status,
               card_number,
               other_contact,
               current_address,
               disability_cert,
               party_ember,
               create_user,
               create_time,
               update_user,
               update_time,
               remark,
               is_deleted,
               confirm_flag,
               housing_rental_id
        from jczz_household
    </sql>

@@ -92,19 +91,18 @@
    </resultMap>

    <select id="selectHouseLabelPage" resultType="org.springblade.modules.house.vo.HouseholdLabelVO">
        select
        id,
        house_code,
        label_id,
        label_name,
        color,
        remark cremark,
        user_id,
        lable_type,
        household_id
        from
        jczz_user_house_label
        where household_id = #{id} and lable_type = 1
        select id,
               house_code,
               label_id,
               label_name,
               color,
               remark cremark,
               user_id,
               lable_type,
               household_id
        from jczz_user_house_label
        where household_id = #{id}
          and lable_type = 1
    </select>

    <!--自定义分页数据查询-->
@@ -331,72 +329,104 @@
               jda.aoi_code        aoiCode,
               jh.relationship     relationship
        from jczz_household jh
        left join jczz_doorplate_address jda on jh.house_code = jda.address_code
        where 1 = 1 and jh.is_deleted = 0
        and jh.associated_user_id = #{userId}
                 left join jczz_doorplate_address jda on jh.house_code = jda.address_code
        where 1 = 1
          and jh.is_deleted = 0
          and jh.associated_user_id = #{userId}
    </select>

    <!--查询房屋集合信息-->
    <select id="getHouseholdListByCode" resultMap="householdAndLabelMap">
        select
            jh.*,
            jhl.id as cid,
            jhl.house_code houseCodes,
            jhl.label_id,
            jhl.label_name,
            jhl.color,
            jhl.user_id,
            jhl.lable_type,
            jhl.household_id,
            jhl.remark as cremark
        select jh.*,
               jhl.id     as  cid,
               jhl.house_code houseCodes,
               jhl.label_id,
               jhl.label_name,
               jhl.color,
               jhl.user_id,
               jhl.lable_type,
               jhl.household_id,
               jhl.remark as  cremark
        from jczz_household jh
        left join jczz_user_house_label jhl on jh.id = jhl.household_id
        where 1=1 and jh.is_deleted = 0
        and jh.house_code = #{code}
        order by -jh.relationship desc,jh.id desc
                 left join jczz_user_house_label jhl on jh.id = jhl.household_id
        where 1 = 1
          and jh.is_deleted = 0
          and jh.house_code = #{code}
        order by -jh.relationship desc, jh.id desc
    </select>

    <!--查询房屋集合信息-按id-->
    <select id="getHouseholdListById" resultMap="householdAndLabelMap">
        select
            jh.id,jh.house_code,jh.name,jh.phone_number,jh.associated_user_id,
            jh.role_type,jh.relationship,jh.is_primary_contact,
            jh.residential_status,jh.gender,jh.birthday,jh.id_card,
            jh.card_type,jh.card_no,
            jh.ethnicity,jh.education,jh.resident_type,
            jh.hukou_registration,jh.resident_adcode,jh.native_place_adcode,
            jh.religious_belief,jh.health_status,jh.disease_name,
            jh.work_status,jh.employer,jh.occupation,jh.cmpy_reg_addr,
            jh.go_out_addr,jh.go_out_where,jh.go_out_time,jh.go_out_reason,
            jh.marital_status,jh.card_number,jh.other_contact,
            if(jda.id is not null,jda.address_name,jh.current_address) as current_address,
            jh.disability_cert,jh.party_ember,jh.remark,
            jh.confirm_flag,jh.housing_rental_id,
            if(jda.id is not null,substring(jda.town_street_code,1,9),jh.home_adcode) as home_adcode,
            br1.name as residentAdName,
            br1.province_code as residentProvinceAdCode,br1.province_name as residentProvinceAdName,
            br1.city_code as residentCityAdCode,br1.city_name as residentCityAdName,
            br2.name as nativePlaceAdName,
            br2.province_code as nativePlaceProvinceAdCode,br2.province_name as nativePlaceProvinceAdName,
            br2.city_code as nativePlaceCityAdCode,br2.city_name as nativePlaceCityAdName,
            jhs.source,
            jhl.id as cid,
            jhl.house_code houseCodes,
            jhl.label_id,
            jhl.label_name,
            jhl.color,
            jhl.user_id,
            jhl.lable_type,
            jhl.household_id,
            jhl.remark as cremark
        select jh.id,
               jh.house_code,
               jh.name,
               jh.phone_number,
               jh.associated_user_id,
               jh.role_type,
               jh.relationship,
               jh.is_primary_contact,
               jh.residential_status,
               jh.gender,
               jh.birthday,
               jh.id_card,
               jh.card_type,
               jh.card_no,
               jh.ethnicity,
               jh.education,
               jh.resident_type,
               jh.hukou_registration,
               jh.resident_adcode,
               jh.native_place_adcode,
               jh.religious_belief,
               jh.health_status,
               jh.disease_name,
               jh.work_status,
               jh.employer,
               jh.occupation,
               jh.cmpy_reg_addr,
               jh.go_out_addr,
               jh.go_out_where,
               jh.go_out_time,
               jh.go_out_reason,
               jh.marital_status,
               jh.card_number,
               jh.other_contact,
               if(jda.id is not null, jda.address_name, jh.current_address)                  as current_address,
               jh.disability_cert,
               jh.party_ember,
               jh.remark,
               jh.confirm_flag,
               jh.housing_rental_id,
               if(jda.id is not null, substring(jda.town_street_code, 1, 9), jh.home_adcode) as home_adcode,
               br1.name                                                                      as residentAdName,
               br1.province_code                                                             as residentProvinceAdCode,
               br1.province_name                                                             as residentProvinceAdName,
               br1.city_code                                                                 as residentCityAdCode,
               br1.city_name                                                                 as residentCityAdName,
               br2.name                                                                      as nativePlaceAdName,
               br2.province_code                                                             as nativePlaceProvinceAdCode,
               br2.province_name                                                             as nativePlaceProvinceAdName,
               br2.city_code                                                                 as nativePlaceCityAdCode,
               br2.city_name                                                                 as nativePlaceCityAdName,
               jhs.source,
               jhl.id                                                                        as cid,
               jhl.house_code                                                                   houseCodes,
               jhl.label_id,
               jhl.label_name,
               jhl.color,
               jhl.user_id,
               jhl.lable_type,
               jhl.household_id,
               jhl.remark                                                                    as cremark
        from jczz_household jh
        left join jczz_user_house_label jhl on jh.id = jhl.household_id
        left join jczz_doorplate_address jda on jda.address_code = jh.house_code
        left join jczz_house jhs on jhs.house_code = jh.house_code
        left join blade_region br1 on br1.code = jh.resident_adcode
        left join blade_region br2 on br2.code = jh.native_place_adcode
        where 1=1 and jh.is_deleted = 0
        and jh.id = #{household.id}
                 left join jczz_user_house_label jhl on jh.id = jhl.household_id
                 left join jczz_doorplate_address jda on jda.address_code = jh.house_code
                 left join jczz_house jhs on jhs.house_code = jh.house_code
                 left join blade_region br1 on br1.code = jh.resident_adcode
                 left join blade_region br2 on br2.code = jh.native_place_adcode
        where 1 = 1
          and jh.is_deleted = 0
          and jh.id = #{household.id}
    </select>

    <!--导出数据-->
@@ -458,17 +488,15 @@

    <!--查询物业-->
    <select id="getProperty" resultType="org.springblade.modules.house.vo.HouseholdOtherVO">
        SELECT
            jpcd.property_company_id as code,
            jpcd.principal as name,
            jpcd.principal_phone as phone
        FROM
            jczz_doorplate_address jda
        LEFT JOIN jczz_district jd ON jd.aoi_code = jda.aoi_code
        LEFT JOIN jczz_property_company_district jpcd on jpcd.district_id=jd.id
        WHERE 1=1
        AND jda.address_code = #{household.houseCode}
        limit 1
        SELECT jpcd.property_company_id AS CODE,
               bu.name                  AS NAME,
               jpcd.principal_phone     AS phone
        FROM jczz_doorplate_address jda
         LEFT JOIN jczz_district jd ON jd.aoi_code = jda.aoi_code
         LEFT JOIN jczz_property_company_district jpcd ON jpcd.district_id = jd.id
         LEFT JOIN blade_user bu ON bu.id = jpcd.principal
        WHERE 1 = 1
          AND jda.address_code = #{household.houseCode} limit 1
    </select>

    <!--查询网格-->
@@ -485,12 +513,11 @@

    <!--查询公安信息-->
    <select id="getSecurity" resultType="org.springblade.modules.house.vo.HouseholdOtherVO">
        SELECT
            address_code as code,policeman as name,policeman_phone as phone
        FROM
            jczz_doorplate_address
        WHERE
          address_code = #{household.houseCode}
        SELECT address_code    as code,
               policeman       as name,
               policeman_phone as phone
        FROM jczz_doorplate_address
        WHERE address_code = #{household.houseCode}
    </select>


@@ -593,11 +620,12 @@

    <!--查询所有未入库的业主信息-->
    <select id="getNotInsertUserHousehold" resultType="org.springblade.modules.house.entity.HouseholdEntity">
        select jh.* from jczz_household jh
        select jh.*
        from jczz_household jh
        where jh.is_deleted = 0
        and jh.relationship = 1
        and jh.associated_user_id is null
        and jh.phone_number !=''
          and jh.relationship = 1
          and jh.associated_user_id is null
          and jh.phone_number !=''
        and length(jh.name)&lt;=12
    </select>


 src/main/java/org/springblade/modules/property/mapper/PropertyCompanyDistrictMapper.xml

@@ -31,20 +31,19 @@
        jpcd.*,
        jd.name as districtName,
        jpc.name as propertyCompanyName,
        jc.name communityName,
        br.name communityName,
        br.name streetName,
        jg.grid_name
        FROM
        jczz_property_company_district jpcd
        LEFT JOIN jczz_district jd ON jd.id = jpcd.district_id AND jd.is_deleted = 0
        LEFT JOIN jczz_property_company jpc ON jpc.id = jpcd.property_company_id AND jpc.is_deleted = 0
        LEFT JOIN jczz_community jc on jc.`code`=jd.community_code
        LEFT JOIN blade_region br on br.code= jd.community_code
        LEFT JOIN jczz_grid_range jgr on jgr.district_code=jd.id
        LEFT JOIN jczz_grid jg on jg.id = jgr.grid_id
        where jpcd.is_deleted = 0
        <if test="propertyCompanyDistrict.communityName!=null and propertyCompanyDistrict.communityName!=''">
            and jc.name like concat('%', #{propertyCompanyDistrict.communityName},'%')
            and br.name like concat('%', #{propertyCompanyDistrict.communityName},'%')
        </if>

        <if test="propertyCompanyDistrict.streetName!=null and propertyCompanyDistrict.streetName!=''">