|
|
-- 命名空间
|
|
|
#namespace("Gtzz")
|
|
|
-- 获取指定步骤的项目列表(待办) max()...group by 只显示同一个项目+步骤CODE的最后一条
|
|
|
#sql("getToDoStepList")
|
|
|
select 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
|
|
|
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 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 1=1
|
|
|
#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 last_step_code>='0206'
|
|
|
#if(setting_jl)
|
|
|
and ta.jl_count>0
|
|
|
#end
|
|
|
#if(not_setting_jl)
|
|
|
and ta.jl_count=0
|
|
|
#end
|
|
|
#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.*,f_getYqCount(t1.subject_id) as yq_count,f_getStepFirstName(t1.subject_id) as FirstStepName,
|
|
|
f_getStepSecondName(t1.subject_id) as SecondStepName,
|
|
|
(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 *,f_getYqCount(t1.subject_id) as yq_count,f_getStepFirstName(t1.subject_id) as FirstStepName,
|
|
|
f_getStepSecondName(t1.subject_id) as SecondStepName,
|
|
|
(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
|
|
|
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('/baseService/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)%'
|
|
|
-- and t3.is_current=0
|
|
|
#end
|
|
|
#end
|
|
|
-- 四个阶段的统计信息(市、校两级兼容)
|
|
|
#sql("tjTopFourStep")
|
|
|
select
|
|
|
(select count(1) from t_gtzz_sy_laststep where
|
|
|
-- year=year(now())
|
|
|
year=2021
|
|
|
#if(org_code)
|
|
|
and org_code='#(org_code)'
|
|
|
#end
|
|
|
) as xmlx,
|
|
|
(select count(1) from t_gtzz_sy_laststep where
|
|
|
-- year=year(now())
|
|
|
year=2021
|
|
|
#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())
|
|
|
year=2021
|
|
|
#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())
|
|
|
year=2021
|
|
|
#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=2021
|
|
|
-- YEAR(NOW())
|
|
|
#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
|
|
|
-- 今年项目分布(市专用)
|
|
|
#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())
|
|
|
year=2021
|
|
|
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)
|
|
|
-- and step_code>'0201'
|
|
|
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
|
|
|
#end |
