|
|
-- 命名空间
|
|
|
#namespace("Gtzz")
|
|
|
-- 获取指定步骤的项目列表(待办) max()...group by 只显示同一个项目+步骤CODE的最后一条
|
|
|
#sql("getToDoStepList")
|
|
|
select '#(person_id)' as operator_id,progress_id,subject_id,subject_name,org_code,subject_type_id,subject_type_name,
|
|
|
step_code,step_name,action_bureau,year,check_status,is_current,begin_time,
|
|
|
task_number,data_type,is_back_log,lixiang_time,org_name as xmdw
|
|
|
from t_gtzz_progress as ta where is_finish_flag <3 and progress_id in
|
|
|
(
|
|
|
select max(progress_id) from t_gtzz_progress where step_code in
|
|
|
(
|
|
|
#for(id:step_codes)
|
|
|
#(for.index > 0 ? ", " : "") '#(id)'
|
|
|
#end
|
|
|
)
|
|
|
and is_current = #(is_current)
|
|
|
#if(org_code)
|
|
|
and org_code='#(org_code)'
|
|
|
#end
|
|
|
#if(keyword)
|
|
|
and ((subject_name like '%#(keyword)%') or (task_number like '%#(keyword)%') or (org_name like '%#(keyword)%'))
|
|
|
#end
|
|
|
#if(begin_date)
|
|
|
and lixiang_time >= '#(begin_date)'
|
|
|
#end
|
|
|
#if(end_date)
|
|
|
and lixiang_time <= '#(end_date)'
|
|
|
#end
|
|
|
-- 专家
|
|
|
#if(expert_person_id)
|
|
|
and subject_id in (select subject_id from t_gtzz_subject_expert where progress_id=0 and step_code in
|
|
|
(
|
|
|
#for(id:step_codes)
|
|
|
#(for.index > 0 ? ", " : "") '#(id)'
|
|
|
#end
|
|
|
)
|
|
|
and person_id='#(expert_person_id)')
|
|
|
#end
|
|
|
-- 监理
|
|
|
#if(jianli_person_id)
|
|
|
and subject_id in (select subject_id from t_gtzz_subject_jianli where step_code in
|
|
|
(
|
|
|
#for(id:step_codes)
|
|
|
#(for.index > 0 ? ", " : "") '#(id)'
|
|
|
#end
|
|
|
)
|
|
|
and person_id='#(jianli_person_id)')
|
|
|
#end
|
|
|
-- 已上报，不显示退回
|
|
|
#if(is_current==0)
|
|
|
and check_status<>2
|
|
|
#end
|
|
|
group by subject_id, step_code
|
|
|
) order by progress_id desc
|
|
|
#end
|
|
|
|
|
|
|
|
|
-- 哪些步骤有上传附件的需要
|
|
|
#sql("getStepDocument")
|
|
|
select t1.step_code,t1.type_id,t2.type_name from t_gtzz_step_document as t1
|
|
|
inner join t_gtzz_document_type as t2 on t1.type_id=t2.type_id
|
|
|
where 1=1
|
|
|
#if(step_code)
|
|
|
and t1.step_code='#(step_code)'
|
|
|
#end
|
|
|
#end
|
|
|
|
|
|
-- 获取指定项目的详细信息
|
|
|
#sql("getSubjectExtInfo")
|
|
|
-- pay_flag:第二阶段业务完成标记(项目资金支付)（0：未完成 1：完成）
|
|
|
-- use_flag:项目启用废除标记（0：废除,1：启用）
|
|
|
-- delete_flag 删除标记（0：有效 1：删除）
|
|
|
-- task_type: 0培训 1新建 2维修 3绿化 4校园文化 5软件平台 6 购买服务 7安全 8装备
|
|
|
-- 数据类型（0-新建 1-支付 2-续拨）
|
|
|
-- process_node_id: 流程节点id
|
|
|
-- process_node_name: 流程节点名称
|
|
|
select t1.sn,(select parent_name from t_pro_detail where task_sn=t1.sn limit 1) as subject_type_name,t1.task_number,t1.task_title,t1.task_year,t1.ex_send_org_name,t1.pay_flag,t1.use_flag,t1.delete_flag,t1.task_type,t1.create_time,t1.data_type,t1.process_node_id,t1.process_node_name,
|
|
|
t2.itemvalue11 as xmjj, -- 项目简介
|
|
|
t2.itemValue20 as ztz, -- 总投资
|
|
|
t2.itemValue47 as zbj, -- 中标价(合同金额)
|
|
|
t2.itemValue20211 as pfje2021, -- 批复金额2021
|
|
|
t2.itemValue20221 as pfje2022, -- 批复金额2022
|
|
|
t2.itemValue20231 as pfje2023, -- 批复金额2023
|
|
|
t2.itemValue20241 as pfje2024, -- 批复金额2024
|
|
|
t2.itemValue20251 as pfje2025 -- 批复金额2025
|
|
|
from t_pro_task as t1 left join t_pro_form_value as t2 on t2.task_sn=t1.sn
|
|
|
where t1.sn='#(task_sn)'
|
|
|
#end
|
|
|
|
|
|
-- 填充项目数据
|
|
|
#sql("fillSubject")
|
|
|
select t1.sn,t1.task_number,t1.task_title,t1.task_year,t1.ex_send_org_name,t1.task_type as subject_type_id ,
|
|
|
t3.subject_type_name, t3.subject_type_name,t1.create_time
|
|
|
from t_pro_task as t1
|
|
|
left join t_pro_form_value as t2 on t2.task_sn=t1.sn
|
|
|
left join t_gtzz_subject_type as t3 on t1.task_type=t3.subject_type_id
|
|
|
where
|
|
|
-- 只关心市直属学校的数据
|
|
|
t1.ex_send_org_name in (select org_name from t_gtzz_school)
|
|
|
-- 立项通过，审批完成
|
|
|
and t1.use_flag=1 and t1.draft_flag=0 and t1.close_flag=1 and t1.data_type=0
|
|
|
-- 按申报时间倒序
|
|
|
order by t1.create_time desc
|
|
|
#end
|
|
|
|
|
|
-- 获取项目财务审批流水
|
|
|
#sql("getSubjectPreInfo")
|
|
|
select handle_node_name,receive_user_name as send_user_name,msg_title, handle_opinion,create_time,modify_time
|
|
|
from t_pro_message where task_sn='#(task_sn)' and DELETE_FLAG=0 order by create_time
|
|
|
#end
|
|
|
-- 获取指定年份的项目查询列表
|
|
|
#sql("getXmxcList")
|
|
|
select * from t_gtzz_progress where progress_id in
|
|
|
(select max(progress_id) from t_gtzz_progress group by subject_id)
|
|
|
#if(year)
|
|
|
and year=#(year)
|
|
|
#end
|
|
|
#if(org_code)
|
|
|
and org_code='#(org_code)'
|
|
|
#end
|
|
|
#if(year)
|
|
|
and year='#(year)'
|
|
|
#end
|
|
|
#if(subject_type_id)
|
|
|
and subject_type_id=#(subject_type_id)
|
|
|
#end
|
|
|
#if(keyword)
|
|
|
and subject_name like '%#(keyword)%'
|
|
|
#end
|
|
|
and step_code like '#(step_code)%' order by begin_time desc
|
|
|
#end
|
|
|
-- 获取符合条件的专家列表
|
|
|
#sql("getExpertList")
|
|
|
select t1.*,
|
|
|
(select group_concat(zc_name) from t_gtzz_expert_domain as t2 where t1.person_id=t2.person_id group by t2.zc_id) zc_name
|
|
|
from t_gtzz_expert as t1 where b_use=1
|
|
|
#if(keyword)
|
|
|
and t1.person_name like '%#(keyword)%'
|
|
|
#end
|
|
|
#if(zc_id)
|
|
|
and t1.person_id in (select person_id from t_gtzz_expert_domain where zc_id=#(zc_id))
|
|
|
#end
|
|
|
order by t1.bureau_name,t1.person_name
|
|
|
#end
|
|
|
-- 项目查询列表之专家分配
|
|
|
#sql("getSubjectListForSettingExpert")
|
|
|
select * from (
|
|
|
select t1.*,
|
|
|
(select count(1) from t_gtzz_subject_expert as t2 where t1.subject_id=t2.subject_id and t2.step_code='#(step_code)' and t2.progress_id=0) as zj_count,
|
|
|
(select count(1) from t_gtzz_progress as t2 where t1.subject_id=t2.subject_id and t2.step_code='#(end_step_code)' ) as can_update
|
|
|
from t_gtzz_subject as t1 where
|
|
|
subject_id in (select subject_id from t_gtzz_progress where
|
|
|
step_code>='#(start_step_code)' and step_code<='#(end_step_code)'
|
|
|
#if(not_setting_zj)
|
|
|
and is_current=1
|
|
|
#end
|
|
|
)
|
|
|
#if(data_type)
|
|
|
and t1.data_type=#(data_type)
|
|
|
#end
|
|
|
|
|
|
#if(begin_date)
|
|
|
and t1.lixiang_time between '#(begin_date)' and '#(end_date)'
|
|
|
#end
|
|
|
|
|
|
#if(keyword)
|
|
|
and (t1.xmmc like '%#(keyword)%' or t1.xmdw like '%#(keyword)%' or t1.task_number like '%#(keyword)%')
|
|
|
#end
|
|
|
order by t1.lixiang_time
|
|
|
) as ta where 1=1
|
|
|
#if(setting_zj)
|
|
|
and ta.zj_count>0
|
|
|
#end
|
|
|
#if(not_setting_zj)
|
|
|
and ta.zj_count=0
|
|
|
#end
|
|
|
#end
|
|
|
-- 项目查询列表之监理公司分配
|
|
|
#sql("getFenPeiJianLiList")
|
|
|
select * from (
|
|
|
select t1.*,
|
|
|
(select progress_id from t_gtzz_progress as t2 where t1.subject_id=t2.subject_id order by progress_id desc limit 1 ) as progress_id,
|
|
|
(select max(step_code) from t_gtzz_progress as t2 where t2.subject_id=t1.subject_id) as last_step_code,
|
|
|
(select count(1) from t_gtzz_subject_jianli as t2 where t1.subject_id=t2.subject_id and t2.step_code='#(step_code)' and t2.person_id='-1') as no_need_jl,
|
|
|
(select count(1) from t_gtzz_subject_jianli as t2 where t1.subject_id=t2.subject_id and t2.step_code='#(step_code)') as jl_count,
|
|
|
(select count(1) from t_gtzz_progress as t2 where t1.subject_id=t2.subject_id and t2.step_code='#(end_step_code)') as can_update
|
|
|
from t_gtzz_subject as t1 where t1.bureau_id='#(bureau_id)'
|
|
|
#if(data_type)
|
|
|
and t1.data_type=#(data_type)
|
|
|
#end
|
|
|
#if(begin_date)
|
|
|
and t1.lixiang_time between '#(begin_date)' and '#(end_date)'
|
|
|
#end
|
|
|
#if(keyword)
|
|
|
and (t1.xmmc like '%#(keyword)%' or t1.xmdw like '%#(keyword)%' or t1.task_number like '%#(keyword)%' )
|
|
|
#end
|
|
|
) as ta where last_step_code>='0206'
|
|
|
#if(setting_jl)
|
|
|
and ta.jl_count>0
|
|
|
#end
|
|
|
#if(not_setting_jl)
|
|
|
and ta.jl_count=0
|
|
|
#end
|
|
|
order by ta.progress_id desc
|
|
|
#end
|
|
|
-- 获取指定处理流水中指定流水号的上传文件类型列表
|
|
|
#sql("getProgressIdDocTypeList")
|
|
|
select ta.document_type_id,count(1) as c from
|
|
|
(select t1.progress_id,t2.* from t_gtzz_progress_file as t1 inner join t_gtzz_file as t2 on t1.file_id=t2.file_id) as ta
|
|
|
where ta.progress_id=#(progress_id) group by ta.document_type_id
|
|
|
#end
|
|
|
-- 综合查询（项目）
|
|
|
#sql("querySubject")
|
|
|
select t1.*,
|
|
|
(select progress_id from t_gtzz_progress where subject_id=t1.subject_id order by progress_id desc limit 1) as progress_id,
|
|
|
(select step_code from t_gtzz_progress where subject_id=t1.subject_id order by progress_id desc limit 1) as step_code
|
|
|
from t_gtzz_subject as t1 where 1=1
|
|
|
#if(keyword)
|
|
|
and (t1.xmmc like '%#(keyword)%' or t1.task_number like '%#(keyword)%')
|
|
|
#end
|
|
|
#if(subject_type_ids)
|
|
|
and t1.subject_type_id in
|
|
|
(
|
|
|
#for(id:subject_type_ids)
|
|
|
#(for.index > 0 ? ", " : "") '#(id)'
|
|
|
#end
|
|
|
)
|
|
|
#end
|
|
|
#if(org_code)
|
|
|
and t1.org_code='#(org_code)'
|
|
|
#end
|
|
|
#if(begin_date)
|
|
|
and (t1.lixiang_time between '#(begin_date)' and '#(end_date)')
|
|
|
#end
|
|
|
#if(data_type)
|
|
|
and t1.data_type=#(data_type)
|
|
|
#end
|
|
|
#if(sbnd)
|
|
|
and t1.sbnd=#(sbnd)
|
|
|
#end
|
|
|
#if(step_code)
|
|
|
and t1.subject_id in (select subject_id from t_gtzz_progress where is_current=#(is_current) and step_code like '#(step_code)%')
|
|
|
#end
|
|
|
#end
|
|
|
|
|
|
-- 综合查询（项目关闭）
|
|
|
#sql("querySubjectForClose")
|
|
|
select *,
|
|
|
(select progress_id from t_gtzz_progress where subject_id=t1.subject_id order by progress_id desc limit 1) as progress_id,
|
|
|
(select step_code from t_gtzz_progress where subject_id=t1.subject_id order by progress_id desc limit 1) as step_code,
|
|
|
(select is_finish_flag from t_gtzz_progress where subject_id=t1.subject_id order by progress_id desc limit 1) as is_finish_flag
|
|
|
from t_gtzz_subject as t1
|
|
|
where 1=1
|
|
|
#if(keyword)
|
|
|
and (t1.xmmc like '%#(keyword)%' or t1.task_number like '%#(keyword)%')
|
|
|
#end
|
|
|
#if(subject_type_ids)
|
|
|
and t1.subject_type_id in
|
|
|
(
|
|
|
#for(id:subject_type_ids)
|
|
|
#(for.index > 0 ? ", " : "") '#(id)'
|
|
|
#end
|
|
|
)
|
|
|
#end
|
|
|
#if(org_code)
|
|
|
and t1.org_code='#(org_code)'
|
|
|
#end
|
|
|
#if(begin_date)
|
|
|
and (t1.lixiang_time between '#(begin_date)' and '#(end_date)')
|
|
|
#end
|
|
|
#if(data_type)
|
|
|
and t1.data_type=#(data_type)
|
|
|
#end
|
|
|
#if(sbnd)
|
|
|
and t1.sbnd=#(sbnd)
|
|
|
#end
|
|
|
and (select t2.is_finish_flag from t_gtzz_progress as t2 where t2.subject_id=t1.subject_id order by t2.progress_id desc limit 1)=2
|
|
|
#end
|
|
|
|
|
|
-- 综合查询（文件）
|
|
|
#sql("queryFile")
|
|
|
select t2.*,t3.*,concat('/FengHuang/upload/',t2.file_id,'.', t2.suffix) as url from t_gtzz_progress_file as t1 inner join t_gtzz_file as t2 on t1.file_id=t2.file_id
|
|
|
inner join t_gtzz_progress as t3 on t1.progress_id=t3.progress_id
|
|
|
where 1=1
|
|
|
#if(keyword)
|
|
|
and((t3.subject_name like '%#(keyword)%') or (t3.task_number like '%#(keyword)%'))
|
|
|
#end
|
|
|
#if(document_type_ids)
|
|
|
and t2.document_type_id in
|
|
|
(
|
|
|
#for(id:document_type_ids)
|
|
|
#(for.index > 0 ? ", " : "") '#(id)'
|
|
|
#end
|
|
|
)
|
|
|
#end
|
|
|
#if(subject_type_ids)
|
|
|
and t3.subject_type_id in
|
|
|
(
|
|
|
#for(id:subject_type_ids)
|
|
|
#(for.index > 0 ? ", " : "") '#(id)'
|
|
|
#end
|
|
|
)
|
|
|
#end
|
|
|
#if(org_code)
|
|
|
and t1.org_code='#(org_code)'
|
|
|
#end
|
|
|
#if(begin_date)
|
|
|
and t2.update_ts between '#(begin_date)' and '#(end_date)'
|
|
|
#end
|
|
|
#if(data_type)
|
|
|
and t3.data_type=#(data_type)
|
|
|
#end
|
|
|
#if(sbnd)
|
|
|
and t3.year=#(sbnd)
|
|
|
#end
|
|
|
#if(step_code)
|
|
|
and t3.step_code like '#(step_code)%'
|
|
|
#end
|
|
|
#end
|
|
|
-- 四个阶段的统计信息(市、校两级兼容)
|
|
|
#sql("tjTopFourStep")
|
|
|
select
|
|
|
(select count(1) from t_gtzz_sy_laststep where year=year(now())-1
|
|
|
#if(org_code)
|
|
|
and org_code='#(org_code)'
|
|
|
#end
|
|
|
) as xmlx,
|
|
|
(select count(1) from t_gtzz_sy_laststep where year=year(now())-1
|
|
|
#if(org_code)
|
|
|
and org_code='#(org_code)'
|
|
|
#end
|
|
|
and step_code like '02%') as zbcg,
|
|
|
(select count(1) from t_gtzz_sy_laststep where year=year(now())-1
|
|
|
#if(org_code)
|
|
|
and org_code='#(org_code)'
|
|
|
#end
|
|
|
and is_finish_flag=0 and step_code like '03%') as ssys,
|
|
|
(select count(1) from t_gtzz_sy_laststep where year=year(now())-1
|
|
|
#if(org_code)
|
|
|
and org_code='#(org_code)'
|
|
|
#end
|
|
|
and is_finish_flag=1) as xmbf
|
|
|
#end
|
|
|
-- 今年项目概览(市专用)
|
|
|
#sql("tjSubjectSummaryCurrentYear")
|
|
|
select sum(subject_count) as subject_count,
|
|
|
sum(finish_count) as finish_count,
|
|
|
sum(close_count) as close_count,
|
|
|
sum(jz_count) as jz_count,
|
|
|
sum(yq_count) as yq_count,
|
|
|
ROUND(sum(money),2) as money
|
|
|
from t_gtzz_sy_base_year where year=year(now())-1
|
|
|
#end
|
|
|
-- 逾期提醒(市、校两级兼容)
|
|
|
#sql("tjYqList")
|
|
|
select * from t_gtzz_sy_laststep where 1=1
|
|
|
#if(org_code)
|
|
|
and org_code='#(org_code)'
|
|
|
#end
|
|
|
and yq_count>0;
|
|
|
#end
|
|
|
-- 项目待办(市、校两级兼容)
|
|
|
#sql("tjSubjectWaitStep")
|
|
|
select t1.step_code,t1.step_name,ifnull(t2.count,0) as count from t_gtzz_step as t1 left join (
|
|
|
select step_code,count(1) as count from t_gtzz_sy_laststep
|
|
|
where is_current=1 and is_finish_flag<3
|
|
|
#if(org_code)
|
|
|
and org_code='#(org_code)'
|
|
|
#end
|
|
|
group by step_code) as t2 on t1.step_code=t2.step_code where (t1.step_code like '02%' or t1.step_code like '03%')
|
|
|
#end
|
|
|
-- 今年项目分布(市专用) -- year=year(now())
|
|
|
#sql("tjFenBu")
|
|
|
select * from
|
|
|
(
|
|
|
select org_code,org_name,sum(subject_count) as subject_count,sum(finish_count) as finish_count,
|
|
|
sum(close_count) as close_count,
|
|
|
sum(jz_count) as jz_count,
|
|
|
sum(yq_count) as yq_count,
|
|
|
ROUND(sum(money),2) as money
|
|
|
from t_gtzz_sy_base_year where year=year(now())-1
|
|
|
group by org_code,org_name
|
|
|
) as ta order by ta.subject_count desc,money desc,finish_count desc
|
|
|
#end
|
|
|
-- 项目进展(学校专用)
|
|
|
#sql("tjxmJz")
|
|
|
select id,year,subject_id,subject_name,date_format(lixiang_time, '%Y-%m-%d') as lixiang_time,step_code,step_name,
|
|
|
step_order_id,big_step_name,
|
|
|
all_step_count,is_finish_flag,is_current,org_code,begin_time,end_time,duration,days_limit,
|
|
|
date_diff_count,create_time,yq_count from t_gtzz_sy_laststep where org_code='#(org_code)'
|
|
|
#end
|
|
|
-- 获取结转的项目列表
|
|
|
#sql("getJzList")
|
|
|
select max(progress_id) as progress_id,
|
|
|
subject_id,org_code,org_name,subject_name,subject_type_id,subject_type_name,
|
|
|
year,step_code,step_name,begin_time,end_time,action_bureau,input_value,radio_id,
|
|
|
is_current,is_back_log,is_finish_flag,check_status,lixiang_time,bxlx_name,cxlx_name,
|
|
|
jsfa_type_id,task_number,data_type,is_finish_time from t_gtzz_progress where org_code='#(org_code)'
|
|
|
and is_finish_flag=#(is_finish_flag)
|
|
|
group by subject_id order by progress_id
|
|
|
#end
|
|
|
-- 归档列表
|
|
|
#sql("getGdList")
|
|
|
select org_code,org_name, sum(subject_count) as subject_count, sum(finish_count) as finish_count,
|
|
|
sum(close_count) as close_count, sum(jz_count) as jz_count, sum(yq_count) as yq_count,ROUND(sum(money),2) as money
|
|
|
from t_gtzz_sy_base_year where 1=1
|
|
|
#if(year)
|
|
|
and year=#(year)
|
|
|
#end
|
|
|
#if(org_code)
|
|
|
and org_code='#(org_code)'
|
|
|
#end
|
|
|
group by org_code,org_name order by sum(subject_count) desc
|
|
|
#end
|
|
|
-- 获取监理项目列表
|
|
|
#sql("getJlSubjectList")
|
|
|
select * from (select t1.*,
|
|
|
ifnull((select count(distinct group_question_id) from t_gtzz_jianli_question as t2 where t2.subject_id=t1.subject_id),0) as question_count,
|
|
|
ifnull((select count(distinct group_question_id) from t_gtzz_jianli_question as t2 where t2.subject_id=t1.subject_id and t2.state_id=3),0) as finish_count,
|
|
|
(select t4.begin_time from t_gtzz_progress as t4 where t4.subject_id=t1.subject_id and t4.step_code='0301' and t4.progress_id<t1.progress_id
|
|
|
order by progress_id desc limit 1) as qd_time from t_gtzz_progress as t1 inner join t_gtzz_subject_jianli as t3 on t1.subject_id=t3.subject_id
|
|
|
where t3.person_id='#(person_id)' and t1.step_code='0302' and t1.is_current=#(is_current))
|
|
|
as ta where 1=1
|
|
|
#if(being_date)
|
|
|
and ta.qd_time >='#(being_date)' and ta.qd_time <='#(end_date) 23:59:59'
|
|
|
#end
|
|
|
#if(keyword)
|
|
|
and (ta.subject_name like '%#(keyword)%' or ta.org_name like '%#(keyword)%')
|
|
|
#end
|
|
|
order by ta.begin_time desc
|
|
|
#end
|
|
|
-- 获取指定项目的监理流水
|
|
|
#sql("getSubjectQuestion")
|
|
|
select t1.* from t_gtzz_jianli_question as t1 where t1.group_question_id=t1.progress_id
|
|
|
and t1.subject_id=#(subject_id)
|
|
|
#if(question_level_id)
|
|
|
and t1.question_level_id=#(question_level_id)
|
|
|
#end
|
|
|
#if(question_type_id)
|
|
|
and t1.question_type_id=#(question_type_id)
|
|
|
#end
|
|
|
#if(begin_date)
|
|
|
and t1.create_time between '#(begin_date)' and '#(end_date) 23:59:59'
|
|
|
#end
|
|
|
#if(state_id)
|
|
|
and t1.state_id=#(state_id)
|
|
|
#end
|
|
|
order by t1.progress_id desc
|
|
|
#end
|
|
|
-- ==========统计分析 ============================================================================================================================================
|
|
|
-- p_shouye_init_base_year
|
|
|
-- 插入单位数据
|
|
|
-- 刷新频率：项目变更+项目单位变更
|
|
|
-- 策略：项目变更时更新
|
|
|
#sql("insertBureauData")
|
|
|
insert into t_gtzz_sy_base_year(org_code,org_name,year)
|
|
|
select t1.org_code,t1.org_name,t2.year from t_gtzz_school as t1 cross join
|
|
|
(select distinct sbnd as year from t_gtzz_subject) as t2
|
|
|
#end
|
|
|
|
|
|
-- 按年度更新项目总数
|
|
|
-- 策略：项目变更时更新
|
|
|
#sql("updateProjectCountByYear")
|
|
|
update t_gtzz_sy_base_year as t1, (select org_code,sbnd,count(1) as subject_count from t_gtzz_subject group by org_code,sbnd) as t2
|
|
|
set t1.subject_count=t2.subject_count where t1.org_code=t2.org_code and t1.year=t2.sbnd;
|
|
|
#end
|
|
|
|
|
|
-- 按年度更新预算金额
|
|
|
#sql("updateMoneyByYear")
|
|
|
update t_gtzz_sy_base_year p,(select org_code,sbnd, sum(ifnull(money,0)) as money
|
|
|
from t_gtzz_subject group by org_code,sbnd) as s set p.money = s.money
|
|
|
where p.org_code = s.org_code and p.year=s.sbnd
|
|
|
#end
|
|
|
|
|
|
-- p_shouye_init_laststep
|
|
|
#sql("get_02_step_count")
|
|
|
select count(1) as c from (select step_order_id from t_gtzz_step where step_code like '02%' and LENGTH(step_code)=4 group by step_order_id ) as t1
|
|
|
#end
|
|
|
|
|
|
#sql("get_03_step_count")
|
|
|
select count(1) as c from (select step_order_id from t_gtzz_step where step_code like '03%' and LENGTH(step_code)=4 group by step_order_id ) as t1
|
|
|
#end
|
|
|
|
|
|
-- 按年度+单位查询完成个数、关闭个数、延期个数
|
|
|
#sql("getRefreshCountByYearOrgCode")
|
|
|
select t1.id, t1.year,t1.org_code,
|
|
|
(select count(distinct t2.subject_id) from t_gtzz_progress as t2 where t2.is_finish_flag=1 and t2.year=t1.year and t2.org_code=t1.org_code) as c1,
|
|
|
(select count(distinct t2.subject_id) from t_gtzz_progress as t2 where t2.is_finish_flag=2 and t2.year=t1.year and t2.org_code=t1.org_code) as c2,
|
|
|
(select ifnull(sum(t2.yq_count),0) from t_gtzz_sy_laststep as t2 where t2.year =t1.year and t2.org_code =t1.org_code) as c3
|
|
|
from t_gtzz_sy_base_year as t1
|
|
|
#end
|
|
|
-- 0:未完成项目流程，1：已完成项目流程，2：项目流程已取消,3:项目结转，本年结束，转入明年。
|
|
|
#sql("laststep_1")
|
|
|
insert into t_gtzz_sy_laststep(year,subject_id,step_code,is_finish_flag,is_current,org_code,begin_time,end_time,subject_name,lixiang_time)
|
|
|
select year,subject_id,step_code,is_finish_flag,is_current,org_code,begin_time,end_time,subject_name,lixiang_time from t_gtzz_progress where progress_id in
|
|
|
(select progress_id from (select max(progress_id),subject_id from t_gtzz_progress group by subject_id) as ta)
|
|
|
#end
|
|
|
-- 对于结束时间为空的，写入当前时间
|
|
|
#sql("laststep_2")
|
|
|
update t_gtzz_sy_laststep set end_time=now() where end_time is null
|
|
|
#end
|
|
|
-- 补充步骤名称和内部排序号
|
|
|
#sql("laststep_3")
|
|
|
update t_gtzz_sy_laststep as t1,t_gtzz_step as t2 set t1.step_name=t2.step_name,t1.days_limit=t2.days_limit where t1.step_code=t2.step_code
|
|
|
#end
|
|
|
#sql("laststep_4")
|
|
|
update t_gtzz_sy_laststep as t1,t_gtzz_step as t2 set t1.step_order_id=t2.in_order_id where t1.step_code=t2.step_code and t1.step_code like '02%'
|
|
|
#end
|
|
|
#sql("laststep_5")
|
|
|
update t_gtzz_sy_laststep as t1,t_gtzz_step as t2 set t1.step_order_id=#para(v_02_step_count)+t2.in_order_id where t1.step_code=t2.step_code and t1.step_code like '03%'
|
|
|
#end
|
|
|
-- 补充大类名称
|
|
|
-- 写入运行时长
|
|
|
-- 写入超时时长
|
|
|
#sql("laststep_6")
|
|
|
update t_gtzz_sy_laststep as t1 set t1.duration=datediff(end_time,begin_time),
|
|
|
t1.date_diff_count=days_limit-duration,
|
|
|
t1.big_step_name=(select step_name from t_gtzz_step as t2 where substr(t1.step_code,1,2)=t2.step_code),
|
|
|
t1.all_step_count=#para(v_02_step_count)+#para(v_03_step_count)
|
|
|
#end
|
|
|
#end |
