baseService/target/classes/p_bigscrenn_init.sql

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

  -- ===============================================================================================================================
  -- 1<><31><EFBFBD><EFBFBD>Ŀ<EFBFBD><C4BF>λ<EFBFBD><CEBB><EFBFBD><EFBFBD>

	-- <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ŀ<EFBFBD><C4BF>λ<EFBFBD><CEBB><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
truncate table t_gtzz_tj_dwqk;

-- <20><>ʼ<EFBFBD><CABC><EFBFBD><EFBFBD>Ŀ<EFBFBD><C4BF>λ<EFBFBD><CEBB><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
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;

-- <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
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='<EFBFBD><EFBFBD><EFBFBD><EFBFBD>'  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='<EFBFBD><EFBFBD><EFBFBD><EFBFBD>'  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;

-- <20><>Ŀ<EFBFBD><C4BF>λ<EFBFBD><CEBB>Ŀ<EFBFBD><C4BF><EFBFBD><EFBFBD>ͳ<EFBFBD>Ʊ<EFBFBD>
-- select * from t_gtzz_tj_dwqk;
-- ===============================================================================================================================
-- 2<><32><EFBFBD><EFBFBD>Ŀ<EFBFBD><C4BF><EFBFBD>ͷֲ<CDB7>ͳ<EFBFBD>Ʊ<EFBFBD>
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;

-- <20><>Ŀ<EFBFBD><C4BF><EFBFBD>ͷֲ<CDB7>ͳ<EFBFBD>Ʊ<EFBFBD>
-- select * from t_gtzz_tj_lxfb;
-- ===============================================================================================================================
-- 3<><33><EFBFBD><EFBFBD>Ŀ<EFBFBD><C4BF><EFBFBD><EFBFBD><EFBFBD>ֲ<EFBFBD>
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;

-- <20><>һ<EFBFBD>²<EFBFBD><C2B2><EFBFBD>
update t_gtzz_tj_jefb set tz=ys where tz is null;

-- ʵ<><CAB5><EFBFBD><EFBFBD><EFBFBD>Χ
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);

-- <20><>Ŀ<EFBFBD><C4BF><EFBFBD><EFBFBD><EFBFBD>ֲ<EFBFBD>ͳ<EFBFBD>Ʊ<EFBFBD>
-- 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<><34><EFBFBD><EFBFBD><EFBFBD>ɽ<EFBFBD><C9BD><EFBFBD>
-- <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ɽ<EFBFBD><C9BD><EFBFBD>
truncate table t_gtzz_tj_wcjd;
-- <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ŀ<EFBFBD><C4BF><EFBFBD><EFBFBD>
insert into t_gtzz_tj_wcjd(subject_count) select count(1) as c from t_gtzz_subject as t1 where t1.sbnd=v_year;
-- <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ŀ<EFBFBD><C4BF><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
update t_gtzz_tj_wcjd set lixiang_count=subject_count;
-- <20>б<EFBFBD><D0B1>ɹ<EFBFBD>
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);
-- ʵʩ<CAB5><CAA9><EFBFBD><EFBFBD>
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);
-- <20><>Ŀ<EFBFBD><C4BF><EFBFBD><EFBFBD>
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 ='<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ִ<EFBFBD>в<EFBFBD><EFBFBD><EFBFBD>' and t2.task_year=v_year);
-- <20><>Ŀ<EFBFBD>ر<EFBFBD>
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);
-- <20><><EFBFBD>ɽ<EFBFBD><C9BD><EFBFBD>
-- select * from t_gtzz_tj_wcjd;
-- ===============================================================================================================================
-- 5<><35><EFBFBD><EFBFBD><EFBFBD>Ʒֲ<C6B7>
-- <20><><EFBFBD><EFBFBD>ͳ<EFBFBD>Ʊ<EFBFBD>
truncate table t_gtzz_tj_qsfb;
-- <20><><EFBFBD><EFBFBD><EFBFBD>Ǽ<EFBFBD><C7BC><EFBFBD><EFBFBD><EFBFBD>
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 ;

-- <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ŀ<EFBFBD><C4BF><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
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;

-- <20><><EFBFBD><EFBFBD><EFBFBD>б<EFBFBD><D0B1>ɹ<EFBFBD><C9B9><EFBFBD><EFBFBD><EFBFBD>
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;

-- <20><><EFBFBD><EFBFBD>ʵʩ<CAB5><CAA9><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
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;

-- <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ŀ<EFBFBD><C4BF><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
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;

-- <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ŀ<EFBFBD>ر<EFBFBD><D8B1><EFBFBD><EFBFBD><EFBFBD>
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;

-- <20><><EFBFBD>Ʒֲ<C6B7>
-- select * from t_gtzz_tj_qsfb
-- ===============================================================================================================================
-- 6<><36><EFBFBD><EFBFBD>λ<EFBFBD>ֲ<EFBFBD>
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;

-- <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ŀ<EFBFBD><C4BF><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
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;
-- <20><><EFBFBD><EFBFBD><EFBFBD>б<EFBFBD><D0B1>ɹ<EFBFBD><C9B9><EFBFBD><EFBFBD><EFBFBD>
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;

-- <20><><EFBFBD><EFBFBD>ʵʩ<CAB5><CAA9><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
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;

-- <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ŀ<EFBFBD><C4BF><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
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;

-- <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ŀ<EFBFBD>ر<EFBFBD><D8B1><EFBFBD><EFBFBD><EFBFBD>
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<><37><EFBFBD><EFBFBD>Ŀ<EFBFBD><C4BF>λ<EFBFBD><CEBB><EFBFBD><EFBFBD>ͳ<EFBFBD>Ʊ<EFBFBD>
-- <20><><EFBFBD><EFBFBD>
truncate table t_gtzz_tj_dwph;
-- <20><>ʼ<EFBFBD><CABC><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ѧУ
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;
-- <20><>Ŀ<EFBFBD><C4BF><EFBFBD><EFBFBD>
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;
-- <20><>Ŀ<EFBFBD>ɹ<EFBFBD>
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;
-- ʵʩ<CAB5><CAA9><EFBFBD><EFBFBD>
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;
-- <20><>Ŀ<EFBFBD><C4BF><EFBFBD><EFBFBD>
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;
-- <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
update t_gtzz_tj_dwph set finish_rate= (case when lixiang_count=0 then 0 else ROUND(bofu_count / lixiang_count * 100) end);

-- <20><>Ŀ<EFBFBD><C4BF>λ<EFBFBD><CEBB><EFBFBD><EFBFBD>ͳ<EFBFBD>Ʊ<EFBFBD>
-- select * from t_gtzz_tj_dwph;
-- ===============================================================================================================================
-- 8<><38><EFBFBD><EFBFBD>Ŀ<EFBFBD>쳣
truncate table t_gtzz_tj_xmyc;
-- <20><><EFBFBD><EFBFBD>
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 ;

-- <20>ر<EFBFBD>
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