CREATE DEFINER=`root`@`%` PROCEDURE `p_bigscreen_init`(`v_year` int)
BEGIN

  -- ===============================================================================================================================
  -- 1、项目单位情况

	-- 清空项目单位情况表
truncate table t_gtzz_tj_dwqk;

-- 初始化项目单位情况表
insert into t_gtzz_tj_dwqk(bxlx_name,cxlx_name)	select t1.bxlx_name,t2.cxlx_name from t_gtzz_dm_bxlx as t1 cross join t_gtzz_dm_cxlx as t2  where t1.b_use=1;

-- 填充数据
update t_gtzz_tj_dwqk as t1,(select t1.bxlx_name,count(1) as c,t1.cxlx_name  from t_gtzz_subject as t1 where t1.sbnd=v_year and t1.cxlx_name='城区'  group by t1.bxlx_name) as t2 set t1.count=t2.c where t1.bxlx_name=t2.bxlx_name and t1.cxlx_name=t2.cxlx_name;

update t_gtzz_tj_dwqk as t1,(select t1.bxlx_name,count(1) as c,t1.cxlx_name  from t_gtzz_subject as t1 where t1.sbnd=v_year and t1.cxlx_name='乡镇'  group by t1.bxlx_name) as t2 set t1.count=t2.c where t1.bxlx_name=t2.bxlx_name and t1.cxlx_name=t2.cxlx_name;

-- 项目单位项目情况统计表
-- select * from t_gtzz_tj_dwqk;
-- ===============================================================================================================================
-- 2、项目类型分布统计表
truncate table t_gtzz_tj_lxfb;
insert into  t_gtzz_tj_lxfb(xmlb,count) 	select xmlb,count(1) as c from t_gtzz_subject where sbnd=v_year group by xmlb;

-- 项目类型分布统计表
-- select * from t_gtzz_tj_lxfb;
-- ===============================================================================================================================
-- 3、项目金额分布
truncate table t_gtzz_tj_jefb;
insert into  t_gtzz_tj_jefb(subject_id,tz,ys)	select t1.subject_id,(case when t2.itemvalue47='' then 0 else t2.itemvalue47 end) ,
                                                        (select sum(this_year_cost+after_year_cost) a from t_pro_detail where task_sn=t1.task_sn)
from t_gtzz_subject as t1 inner join t_pro_form_value as t2 	on t1.task_sn=t2.task_sn where t1.sbnd=v_year order by t1.subject_id;

-- 打一下补丁
update t_gtzz_tj_jefb set tz=ys where tz is null;

-- 实例金额范围
update t_gtzz_tj_jefb set ys_level=(case when ys<20 then 1 when ys<50 then 2 when ys<100 then 3 when ys<300 then 4 else 5  end);
update t_gtzz_tj_jefb set tz_level=(case when tz<20 then 1 when tz<50 then 2 when tz<100 then 3 when tz<300 then 4 else 5  end);

-- 项目金额分布统计表
-- select count(1) as count ,ys_level from t_gtzz_tj_jefb group by ys_level;
-- select count(1) as count ,tz_level from t_gtzz_tj_jefb group by tz_level;
-- ===============================================================================================================================
-- 4、完成进度
-- 清空完成进度
truncate table t_gtzz_tj_wcjd;
-- 插入项目总量
insert into t_gtzz_tj_wcjd(subject_count) select count(1) as c from t_gtzz_subject as t1 where t1.sbnd=v_year;
-- 更新项目立项数量
update t_gtzz_tj_wcjd set lixiang_count=subject_count;
-- 招标采购
update t_gtzz_tj_wcjd set zbcg_count=(select count(1) as c from t_gtzz_progress where year=v_year and step_code = '0204' and is_current=0 and is_back_log=0);
-- 实施验收
update t_gtzz_tj_wcjd set ssys_count=(select count(1) as c from t_gtzz_progress where year=v_year and step_code = '0305' and is_current=0 and is_back_log=0);
-- 项目拨付
update t_gtzz_tj_wcjd set xmbf_count=(select count(1) as c from t_pro_message as t1 inner join t_pro_task as t2 on t1.task_sn=t2.sn where t1.handle_node_name ='财政执行拨付' and t2.task_year=v_year);
-- 项目关闭
update t_gtzz_tj_wcjd set cancel_count=(select count(1) as c from t_gtzz_progress where year=v_year and is_current=1 and is_finish_flag=2);
-- 完成进度
-- select * from t_gtzz_tj_wcjd;
-- ===============================================================================================================================
-- 5、趋势分布
-- 清空统计表
truncate table t_gtzz_tj_qsfb;
-- 插入骨架数据
insert into t_gtzz_tj_qsfb(month_id,month_name,stage_id,stage_name)	select t1.month_id,t1.month_name,t2.stage_id,t2.stage_name from t_gtzz_dm_month as t1 cross join t_gtzz_dm_stage as t2 ;

-- 更新项目立项个数
update t_gtzz_tj_qsfb as t1,(select date_format(lixiang_time,'%m') as month_id,count(1) count from t_gtzz_subject where sbnd=v_year group by month_id) as t2 set t1.count=t2.count where t1.month_id=t2.month_id and t1.stage_id=1;

-- 更新招标采购数量
update t_gtzz_tj_qsfb as t1,(select date_format(lixiang_time,'%m') as month_id,count(1) count from t_gtzz_progress where year=v_year
    and is_current=0 and is_back_log=0 and step_code='0204' group by month_id) as t2 set t1.count=t2.count where t1.month_id=t2.month_id and t1.stage_id=2;

-- 更新实施验收数量
update t_gtzz_tj_qsfb as t1,(select date_format(lixiang_time,'%m') as month_id,count(1) count from t_gtzz_progress where year=v_year
    and is_current=0 and is_back_log=0 and step_code='0305' group by month_id) as t2 set t1.count=t2.count where t1.month_id=t2.month_id and t1.stage_id=3;

-- 更新项目拨付数量
update t_gtzz_tj_qsfb as t1,(select date_format(lixiang_time,'%m') as month_id,count(1) count from t_gtzz_progress where year=v_year
    and is_current=0 and is_back_log=0 and step_code='0305' and is_bofu=1 group by month_id) as t2 set t1.count=t2.count where t1.month_id=t2.month_id and t1.stage_id=4;

-- 更新项目关闭数量
update t_gtzz_tj_qsfb as t1,(select date_format(lixiang_time,'%m') as month_id,count(1) count from t_gtzz_progress where year=v_year
    and is_current=1 and is_back_log=0 and is_finish_flag=2 group by month_id) as t2 set t1.count=t2.count where t1.month_id=t2.month_id and t1.stage_id=5;

-- 趋势分布
-- select * from t_gtzz_tj_qsfb
-- ===============================================================================================================================
-- 6、单位分布
truncate table t_gtzz_tj_dwfb;
insert into t_gtzz_tj_dwfb(bxlx_id,bxlx_name,stage_id,stage_name)
select t1.bxlx_id,t1.bxlx_name,t2.stage_id,t2.stage_name from t_gtzz_dm_bxlx as t1 cross join t_gtzz_dm_stage  as t2 where t1.b_use=1;

-- 更新项目立项个数
update t_gtzz_tj_dwfb as t1,(select count(1) count,bxlx_name from t_gtzz_subject where sbnd=v_year group by bxlx_name ) as t2 set t1.count=t2.count where t1.bxlx_name=t2.bxlx_name and t1.stage_id=1;
-- 更新招标采购数量
update t_gtzz_tj_dwfb as t1,(select count(1) count,bxlx_name from t_gtzz_progress where year=v_year
    and is_current=0 and is_back_log=0 and step_code='0204' group by bxlx_name) as t2 set t1.count=t2.count where t1.bxlx_name=t2.bxlx_name and t1.stage_id=2;

-- 更新实施验收数量
update t_gtzz_tj_dwfb as t1,(select bxlx_name,count(1) count from t_gtzz_progress where year=v_year
    and is_current=0 and is_back_log=0 and step_code='0305' group by bxlx_name) as t2 set t1.count=t2.count where t1.bxlx_name=t2.bxlx_name and t1.stage_id=3;

-- 更新项目拨付数量
update t_gtzz_tj_dwfb as t1,(select bxlx_name,count(1) count from t_gtzz_progress where year=v_year
    and is_current=0 and is_back_log=0 and step_code='0305' and is_bofu=1 group by bxlx_name) as t2 set t1.count=t2.count where t1.bxlx_name=t2.bxlx_name and t1.stage_id=4;

-- 更新项目关闭数量
update t_gtzz_tj_dwfb as t1,(select bxlx_name,count(1) count from t_gtzz_progress where year=v_year
    and is_current=1 and is_back_log=0 and is_finish_flag=2 group by bxlx_name) as t2 set t1.count=t2.count where t1.bxlx_name=t2.bxlx_name and t1.stage_id=5;

-- select * from t_gtzz_tj_dwfb;
-- ===============================================================================================================================
-- 7、项目单位排行统计表
-- 清空
truncate table t_gtzz_tj_dwph;
-- 初始化所有学校
insert into t_gtzz_tj_dwph(org_code,org_name,cxlx_name,bxlx_name,year) select org_code,org_name,cxlx_name,bxlx_name,v_year from t_gtzz_school;
-- 项目立项
update t_gtzz_tj_dwph as t1,(select t1.org_code,count(1) as c  from t_gtzz_progress as t1 where t1.year=v_year and t1.step_code='0201' and t1.is_back_log=0 group by t1.org_code) as t2 set t1.lixiang_count=t2.c where t1.org_code=t2.org_code;
-- 项目采购
update t_gtzz_tj_dwph as t1,(select t1.org_code,count(1) as c  from t_gtzz_progress as t1 where t1.year=v_year and t1.step_code='0204' and t1.is_current=0 and t1.is_back_log=0 group by t1.org_code) as t2 set t1.zhaobiao_count=t2.c where t1.org_code=t2.org_code;
-- 实施验收
update t_gtzz_tj_dwph as t1,(select t1.org_code,count(1) as c  from t_gtzz_progress as t1 where t1.year=v_year and t1.step_code='0305' and t1.is_current=0 and t1.is_back_log=0 group by t1.org_code) as t2 set t1.shiyanyanshou_count=t2.c where t1.org_code=t2.org_code;
-- 项目拨付
update t_gtzz_tj_dwph as t1,(select t1.org_code,count(1) as c  from t_gtzz_progress as t1 where t1.year=v_year and t1.step_code='0305' and t1.is_current=1 and t1.is_back_log=0 and t1.is_bofu=1 group by t1.org_code) as t2 set t1.bofu_count=t2.c where t1.org_code=t2.org_code;
-- 完成率
update t_gtzz_tj_dwph set finish_rate= (case when lixiang_count=0 then 0 else ROUND(bofu_count / lixiang_count * 100) end);

-- 项目单位排行统计表
-- select * from t_gtzz_tj_dwph;
-- ===============================================================================================================================
-- 8、项目异常
truncate table t_gtzz_tj_xmyc;
-- 超期
insert into t_gtzz_tj_xmyc(type_id,org_name,count)
select 1,org_name,count(1) as count from t_gtzz_progress where step_code='0303' and is_current=1 and timestampdiff(day,action_time,date_format(now(), '%y-%m-%d %h:%i:%s'))>30 group by org_name order by count desc ;

-- 关闭
insert into t_gtzz_tj_xmyc(type_id,org_name,count)
select 2,org_name,count(1) as count from t_gtzz_progress as t1 where is_current=1 and is_finish_flag=2 group by org_name order by count desc ;

-- select * from t_gtzz_tj_xmyc;
-- ===============================================================================================================================
END