/*统计正在备案工程信息,
并统计建筑钢材、混凝土、防水卷材、塑料管材、建筑外窗、预拌砂浆六类产品类别对应的备案批次与总量,工程面积。
*/
--select count(id) beiancishu,sum(r.purchase_num),project_id from bemms.t_purchase_info r group by product_type ,project_id
select build_licence 施工许可证号,
pro_area 工程面积,
to_char(licence_date, 'yyyy-MM-dd') 发证日期,
project_name 工程名称,
contractor_name 施工单位,
bemms.fun_get_quality_supervision(quality_supervision) 质量监督机构,
to_char(htkgrq, 'yyyy-MM-dd') 合同开工日期,
to_char(htjgrq, 'yyyy-MM-dd') 合同竣工日期,
to_char(operate_date, 'yyyy-MM-dd') 最后备案时间,
wm_concat(case
when product_type = 1 then
ba_count
end) 建筑钢材备案次数,
wm_concat(case
when product_type = 1 then
ba_sum || '吨'
end) 建筑钢材备案总量,
wm_concat(case
when product_type = 2 then
ba_count
end) 预拌混凝土备案次数,
wm_concat(case
when product_type = 2 then
ba_sum || '立方米'
end) 预拌混凝土备案总量,
wm_concat(case
when product_type = 3 then
ba_count
end) 防水卷材备案次数,
wm_concat(case
when product_type = 3 then
ba_sum || '平方米'
end) 防水卷材备案总量,
wm_concat(case
when product_type = 4 then
ba_count
end) 防水涂料备案次数,
wm_concat(case
when product_type = 4 then
ba_sum || '千克'
end) 防水涂料备案总量,
wm_concat(case
when product_type = 5 then
ba_count
end) 建筑外窗备案次数,
wm_concat(case
when product_type = 5 then
ba_sum || '平方米'
end) 建筑外窗备案总,
wm_concat(case
when product_type = 7 then
ba_count
end) 塑料管材管件备案次数,
wm_concat(case
when product_type = 7 then
ba_sum || '米'
end) 塑料管材管件备案总量,
wm_concat(case
when product_type = 9 then
ba_count
end) 预拌砂浆备案次数,
wm_concat(case
when product_type = 9 then
ba_sum || '吨'
end) 预拌砂浆备案总量
from (select t1.build_licence,
t2.ba_count,
t2.ba_sum,
t2.product_type,
t1.pro_area,
t1.licence_date,
t1.project_name,
t1.contractor_name,
t1.quality_supervision,
t1.htkgrq,
t1.htjgrq,
t1.id,
t1.operate_date
from (select s.build_licence,
s.pro_area,
s.licence_date,
s.project_name,
s.contractor_name,
s.quality_supervision, --质量监督机构
s.htkgrq,
s.htjgrq,
s.id,
CASE
WHEN p.last_repdata IS NULL THEN
s.operate_date
ELSE
last_repdata
END operate_date --最后备案时间
from T_SGPROJECT_INFO s
left join (SELECT project_id, MAX(operate_date) last_repdata
FROM t_purchase_info
GROUP BY project_id) p
ON s.id = p.project_id
where 1 = 1
and sgproject_type = 1
and status in (20, 30)
and symbol = 1
AND (s.gclb = '房屋建筑工程' OR s.gclb IS NULL)) t1,
(select count(id) ba_count,
sum(r.purchase_num) ba_sum,
project_id,
product_type
from bemms.t_purchase_info r
group by product_type, project_id) t2
where t1.id = t2.project_id
order by t1.build_licence, product_type)
group by build_licence,
pro_area,
licence_date,
project_name,
contractor_name,
quality_supervision,
htkgrq,
htjgrq,
operate_date
order by build_licence;