<!--软件地图首页列表信息-->
<select id="queryAllSoftwareFigure2" resultType="com.huawei.kunpeng.center.business.softwarefigure.vo.SoftwareFigureRespVO">
select
tsf.software_figure_id,
software_name,
is_opensource,
software_type productType,
tsf.partner_name,
tsf.partner_id,
tsf.admin_region,
tsf.industry,
tsf.national_power nationalPower,
sum(pt.trade_power) tradePower,
sum(pt.native_trade_power) nativeTradePower,
tsf.national_ranking nationalRanking,
admin_region_ranking adminRegionRanking,
<choose>
<when test="dto.singleTrade != null and dto.singleTrade != '' and dto.industry == 'KP'">
sum(ifnull(pt.trade_product_num,0)) versionNum,
sum(ifnull(pt.trade_adapted_product_num,0)) certVersionNum,
sum(ifnull(pt.trade_compatible_num,0)) cCertifiedNum,
sum(ifnull(pt.trade_validated_num,0)) vCertifiedNum,
sum(ifnull(pt.trade_native_num,0)) nCertifiedNum,
sum(ifnull(pt.trade_cust_num,0)) customerNum,
sum(ifnull(pt.trade_project_num,0)) projectNum,
</when>
<otherwise>
product_num versionNum,
adapted_product_num certVersionNum,
Compatible_num cCertifiedNum,
Validated_num vCertifiedNum,
native_num nCertifiedNum,
tsf.cust_num customerNum,
tsf.project_num projectNum,
</otherwise>
</choose>
tsf.created_time,
tsf.last_updated_time
from t_software_figure tsf
left join t_software_trade_figure pt on pt.software_figure_id = tsf.software_figure_id
LEFT JOIN t_partner_figure tpf on tsf.partner_id = tpf.partner_id
where 1=1
<if test="dto.industry != null and dto.industry != ''">
and tsf.industry = #{dto.industry} AND tpf.industry = #{dto.industry}
</if>
<if test="dto.addrAreaId!=null and dto.addrAreaId!=''">
and tpf.admin_region in
<foreach collection="dto.addrAreaId.split(',')" separator="," item="item" open="(" close=")">
#{item}
</foreach>
</if>
<if test="dto.trade!=null and dto.trade!=''">
AND pt.trade in
<foreach collection="dto.trade.split(',')" separator="," item="item" open="(" close=")">
#{item}
</foreach>
</if>
<if test="dto.partnerLevel!=null and dto.partnerLevel!=''">
and tpf.partner_level in
<foreach collection="dto.partnerLevel.split(',')" separator="," item="item" open="(" close=")">
#{item}
</foreach>
</if>
<if test="dto.softwareType!=null and dto.softwareType!=''">
and tsf.software_type in
<foreach collection="dto.softwareType.split(',')" separator="," item="item" open="(" close=")">
#{item}
</foreach>
</if>
<if test="dto.isOpenSourceReal!=null and dto.isOpenSourceReal!=''">
and tsf.is_openSource in
<foreach collection="dto.isOpenSourceReal" separator="," item="item" open="(" close=")">
#{item}
</foreach>
</if>
<if test="dto.softwareName!=null and dto.softwareName!=''">
and lower(tsf.software_name) like CONCAT('%',lower(#{dto.softwareName}),'%')
</if>
<if test="dto.partnerName!=null and dto.partnerName!=''">
and tsf.partner_name like CONCAT('%',#{dto.partnerName},'%')
</if>
<if test="dto.isNativeSoftware != null and dto.isNativeSoftware != '' ">
and tsf.native_num > 0
</if>
<if test="dto.productType!=null and dto.productType!=''">
and software_type in
<foreach collection="dto.productType.split(',')" item="item" open="(" close=")" separator="," >
#{item}
</foreach>
</if>
group by tsf.software_figure_id
<choose>
<when test="sortJson != null">
order by ${sortJson.filed} ${sortJson.mode}
</when>
<otherwise>
order by tsf.national_power desc
</otherwise>
</choose>
</select>
<!--开发语言-->
<select id="getSoftwareDevelopLanguage" resultType="java.lang.String">
SELECT
DISTINCT tp.develop_language
FROM
t_product tp
LEFT JOIN t_isv ti on tp.isv_credit_code = ti.isv_credit_code
where tp.product_name = #{vo.softwareName} AND ti.partner_id = #{vo.partnerId} AND tp.develop_language is not null AND tp.develop_language != ''
</select>
<!--算力-->
<select id="getAdaptedPowerInfo" resultType="java.lang.Long">
select SUM(t.power) FROM (
select ifnull(SUM(tppl.opr_customer_power), 0) power
from t_software_figure tsf
LEFT JOIN t_partner_figure tpf on tsf.partner_id = tpf.partner_id
LEFT JOIN t_isv ti on ti.partner_id = tsf.partner_id
LEFT JOIN t_product tp on ti.isv_credit_code = tp.isv_credit_code and tp.product_name = tsf.software_name
LEFT JOIN t_product_power_log tppl on tp.product_id = tppl.product_id
where
tppl.opr_type in ('A', 'U-A', 'U-D')
AND tp.product_id is not null
AND tppl.adapt_status = '2'
AND tsf.software_figure_id = #{softwareFigureId}
AND tsf.industry = 'KP' AND tpf.industry = 'KP'
UNION ALL
select ifnull(SUM(tpts.customer_power), 0) power
from t_software_figure tsf
LEFT JOIN t_partner_figure tpf on tsf.partner_id = tpf.partner_id
LEFT JOIN t_isv ti on ti.partner_id = tsf.partner_id
LEFT JOIN t_product tp on ti.isv_credit_code = tp.isv_credit_code and tp.product_name = tsf.software_name
LEFT JOIN t_product_trade_scene tpts on tpts.product_id = tp.product_id
where
tpts.approve_status = '1'
AND tpts.demand_source = '0'
AND tp.adapt_status = '2'
AND tp.product_id is not null
AND tsf.software_figure_id = #{softwareFigureId}
AND tsf.industry = 'KP' AND tpf.industry = 'KP') t
</select>
<!--区域伙伴数量-->
<select id="getPartnerNumByAdministrative" resultType="com.huawei.kunpeng.center.business.partnerfigure.dto.PartnerFigureByAdmResDTO">
select
t.admin_region addrAreaId,
IFNULL(t3.name_,t.admin_region) addrAreaName,
t3.name_en addrAreaNameEn,
count(DISTINCT t.partner_id) partnerNum,
count(DISTINCT if(t.partner_level='super_top',t.partner_id,null)) superHeadNum,
count(DISTINCT if(t.partner_level='trade_top',t.partner_id,null)) industryHeadNum,
count(DISTINCT if(t.partner_level='trade_center',t.partner_id,null)) industryWaistNum,
count(DISTINCT if(t.partner_level='area_center',t.partner_id,null)) regionalWaistNum,
count(DISTINCT if(t.partner_level='long_tail',t.partner_id,null)) tailPartnersNum,
<choose>
<when test=" dto.industry == 'KP'">
sum(pt.trade_power) partnerPowerNum ,
sum(if(t.partner_level='super_top',pt.trade_power,null)) superHeadPowerNum,
sum(if(t.partner_level='trade_top',pt.trade_power,null)) industryHeadPowerNum,
sum(if(t.partner_level='trade_center',pt.trade_power,null)) industryWaistPowerNum,
sum(if(t.partner_level='area_center',pt.trade_power,null)) regionalWaistPowerNum,
sum(if(t.partner_level='long_tail',pt.trade_power,null)) tailPartnersPowerNum
</when>
<otherwise>
sum(pt.trade_power) partnerPowerNum ,
sum(if(t.partner_level='super_top',t.national_power,null)) superHeadPowerNum,
sum(if(t.partner_level='trade_top',t.national_power,null)) industryHeadPowerNum,
sum(if(t.partner_level='trade_center',t.national_power,null)) industryWaistPowerNum,
sum(if(t.partner_level='area_center',t.national_power,null)) regionalWaistPowerNum,
sum(if(t.partner_level='long_tail',t.national_power,null)) tailPartnersPowerNum
</otherwise>
</choose>
from t_partner_figure t
<if test="dto.industry == 'KP'">
left join t_partner_trade_figure pt on pt.partner_figure_id = t.partner_figure_id
</if>
left join (select value_,name_,name_en from t_dictionary_detail t3 where t3.dict_id = (select id_ from t_dictionary t where t.value_ = 'partner_map_area')
union all
select if(t4.value_ = 450000, 460000,640000) value_,name_,name_en from t_dictionary_detail t4
where t4.dict_id = (select id_ from t_dictionary t where t.value_ = 'partner_map_area') and t4.value_ in ('450000','610000')
) t3 on t.admin_region=t3.value_
where t.industry = #{dto.industry} and t3.name_ is not null
and EXISTS (select 1 from t_isv isv where isv.partner_id = t.partner_id)
<include refid="where_sql"/>
GROUP BY t3.name_
order by partnerNum desc,t3.name_
</select>
<!--伙伴区域-->
SELECT
value_,
name_,
name_en
FROM
t_dictionary_detail t3
WHERE
t3.dict_id = (SELECT id_ FROM t_dictionary t WHERE t.value_ = 'partner_map_area') UNION ALL
SELECT
IF(t4.value_ = 450000, 460000, 640000) value_,
name_,
name_en
FROM
t_dictionary_detail t4
WHERE
t4.dict_id = (SELECT id_ FROM t_dictionary t WHERE t.value_ = 'partner_map_area')
AND t4.value_ IN ('450000', '610000')
伙伴数量信息
<select id="getPartnerLevelNumInfo" resultType="com.huawei.kunpeng.center.business.partnerfigure.dao.PartnerLevelInfoDAO">
SELECT
tpf.partner_level partnerLevel,
COUNT(DISTINCT tpf.partner_id) resultValue,
sum(if(t.level is null, 1, 0)) unregisteredNum,
sum(if(t.level = 1, 1, 0)) certificationLevelNum,
sum(if(t.level = 2, 1, 0)) priorityNum,
sum(if(t.level = 3, 1, 0)) leadingLevelNum,
sum(if(t.level = 4, 1, 0)) strategicLevelNum
FROM t_partner_figure tpf
LEFT JOIN (
SELECT
tpl.partner_id,
max( tpl.level ) level
FROM
t_partner_level tpl
LEFT JOIN t_dictionary_detail tdd1 ON tpl.partner_program = tdd1.value_
AND tdd1.dict_id =(
SELECT
id_
FROM
t_dictionary
WHERE
value_ = 'partner_program'
)
LEFT JOIN t_dictionary_detail tdd2 ON tpl.level = tdd2.value_
AND tdd2.dict_id =(
SELECT
id_
FROM
t_dictionary
WHERE
value_ = 'partner_auth_level'
)
WHERE
tdd1.value_ IS NOT NULL
AND tdd1.org_type = '0'
AND tdd2.value_ IS NOT NULL
GROUP BY
tpl.partner_id
) t ON t.partner_id = tpf.partner_id
where 1=1
and EXISTS (select 1 from t_isv isv where isv.partner_id = tpf.partner_id)
GROUP BY partner_level
</select>
<select id="getNativePartnerLevelNumInfo" resultType="com.huawei.kunpeng.center.business.partnerfigure.dao.PartnerLevelInfoDAO">
SELECT temp.partner_level partnerLevel,COUNT(DISTINCT temp.partner_id) resultValue
FROM (
select tpf.partner_id,tpf.partner_level
from t_partner_figure tpf
left join t_partner_trade_figure pt on pt.partner_figure_id = tpf.partner_figure_id
AND tpf.native_software_num > 0
AND tpf.partner_id in (
SELECT DISTINCT t3.partner_id
FROM t_product t1
JOIN t_adaptation_project t2 ON t1.product_id = t2.product_id
AND t1.region_id = t2.region_id
JOIN t_adaptation_authentication ta ON t2.id = ta.adaptation_project_id
AND t2.region_id = ta.region_id
JOIN t_isv t3 ON t1.isv_credit_code = t3.isv_credit_code
AND ta.region_id = t3.region_id
WHERE t2.promotion_phase = '1391'
AND ta.auth_level = 'Native'
)
)temp
GROUP BY temp.partner_level
</select>
超级头部、行业头部、行业腰部、区域腰部、中长尾
// 排序 按照顺序输出 "super_top", "trade_top", "trade_center", "area_center", "long_tail"
List<PartnerLevelNumInfoRespVO> sort = new ArrayList<>();
List<String> sortRule = ListUtil.toList("super_top", "trade_top", "trade_center", "area_center", "long_tail");
sortRule.stream().forEach(item -> {
Optional<PartnerLevelNumInfoRespVO> optional = result.stream().filter(i -> item.equals(i.getPartnerLevel())).findFirst();
optional.ifPresent(sort::add);
});
-- 行业
SELECT * FROM t_dictionary_detail WHERE dict_id = '2' AND status_ = 0 AND parent_id = '0';
-- 行业 伙伴数量
select count(DISTINCT tpf.partner_id) resultValue, '419' trade
from t_partner_figure tpf
left join t_partner_trade_figure pt on pt.partner_figure_id = tpf.partner_figure_id
WHERE pt.trade = '419'
and EXISTS (select 1 from t_isv isv where isv.partner_id = tpf.partner_id);
-- 按行业 同辕伙伴数量
select count(DISTINCT tpf.partner_id) resultValue,'419' trade
from t_partner_figure tpf
left join t_partner_trade_figure pt on pt.partner_figure_id = tpf.partner_figure_id
WHERE pt.trade = '419'
AND tpf.native_software_num > 0
-- 伙伴列表
SELECT
t1.partner_id,
t1.partner_name,
tab.certificateLevel,
t.partner_level,
t1.kp_region_id realRegionId,
t2.short_org_name regionName,
t.admin_region partnerAddr,
sum(pt.trade_cust_num) customerNum,
sum(pt.trade_project_num) projectNum,
t.national_power partnerPower,
sum(pt.trade_power) tradePower,
sum(pt.native_trade_power) nativePower,
pt.trade_software_num productNum,
pt.trade_adapted_software_num adaptProductNum,
pt.trade_certificate_software_num certProductNum,
pt.trade_native_software_num nativeProductNum,
t.national_ranking nationalRanking,
t.region_ranking regionRanking
FROM
t_partner_figure t
LEFT JOIN t_partner_trade_figure pt ON pt.partner_figure_id = t.partner_figure_id
LEFT JOIN t_partners t1 ON t1.partner_id = t.partner_id
LEFT JOIN sys_org t2 ON t2.org_id = t1.kp_region_id
LEFT JOIN (
SELECT
tpl.partner_id,
GROUP_CONCAT(tpl.LEVEL) LEVEL,
GROUP_CONCAT(CONCAT(tpl.identity, '-', tdd2.name_)) certificateLevel
FROM
t_partner_level tpl
LEFT JOIN t_dictionary_detail tdd1 ON tpl.partner_program = tdd1.value_
AND tdd1.dict_id = (SELECT id_ FROM t_dictionary WHERE value_ = 'partner_program' -- 伙伴计划
)
LEFT JOIN t_dictionary_detail tdd2 ON tpl.LEVEL = tdd2.value_
AND tdd2.dict_id = (SELECT id_ FROM t_dictionary WHERE value_ = 'partner_auth_level')
WHERE
tdd1.value_ IS NOT NULL
AND tdd1.org_type = '0'
AND tdd2.value_ IS NOT NULL
GROUP BY
tpl.partner_id
) tab ON tab.partner_id = t.partner_id
WHERE
1 = 1
AND EXISTS (SELECT 1 FROM t_isv isv WHERE isv.partner_id = t.partner_id)
GROUP BY
t1.partner_id
根据上边关系帮我生成表关系图