<select id="getGriderList" resultType="com.sf.gis.common.domain.vo.AppGriderListVo">
SELECT
gr.grider_id,
gr.grider_name,
gr.grider_birth,
gr.native_province_name,
gr.native_city_name,
gr.nation,
gr.grider_age,
gr.grider_sex,
gr.political_outlook,
gr.domicile_city_name,
gr.domicile_province_name,
gr.grider_types,
gr.grider_position_type,
(select STRING_AGG(
t.tag_name , ', ') from (
SELECT gwt.tag_type,
gwt.tag_name from grider_work_tag gwt where gwt.grider_id = gr.grider_id and gwt.deleted=0 and gwt.history_del = 0 order by gwt.tag_type asc) t) as collectTags,
gr.grider_position_code,
(
case
when gr.grider_hiredate is not null and gr.status = 1 then ROUND(EXTRACT(YEAR FROM age(now(), gr.grider_hiredate)) + EXTRACT(MONTH FROM age(now(), gr.grider_hiredate)) / 12.0 , 1)::VARCHAR
when gr.status = 2 and gr.grider_hiredate is not null and gr.depart_date is not null then ROUND(EXTRACT(YEAR FROM age(gr.depart_date::date, gr.grider_hiredate::date)) + EXTRACT(MONTH FROM age(gr.depart_date::date, gr.grider_hiredate::date)) / 12.0 , 1)::VARCHAR
else gr.grider_workyear end) as grider_workyear,
gr.grider_tel_enc,
gr.pda_account,
<!-- gr.grider_work_status,-->
gr.grider_hiredate,
gr.grider_certcode_enc,
gr.height,
gr.buy_service_org,
(case when gr.grider_certtype = '00' then '身份证' when gr.grider_certtype = '01' then '港澳台居住证' when gr.grider_certtype = '02' then '港澳通行证' when gr.grider_certtype = '03' then '护照' else '' end) as griderCerttype,
(case when gr.marriage = 0 then '未婚' when gr.marriage = 1 then '已婚' when gr.marriage = 3 then '离异' when gr.marriage = 4 then '丧偶' when gr.marriage = 2 then '其他' else '' end) as marriage,
(case when gr.veterans = 0 then '否' when gr.veterans = 1 then '是' else '' end) as veterans,
(case when gr.militia = 0 then '否' when gr.militia = 1 then '是' else '' end) as militia,
(case when gr.status = 1 then '在职' when gr.status = 2 then '离职'
else '' end) as griderWorkStatus,
gr.grider_work_status as griderWorkStatusInt,
gr.house_code as houseCode,
gr.enter_party_time as enterPartyTime,
gr.domicile_type AS domicileProvinceName,
gr.domicile_type AS domicileType,
gr.detail_address AS griderAddr,
gr.completion_degree completionDegree,
gr.depart_date departDate,
gr.birth_address birthAddress,
gr.detail_address detailAddress,
gr.domicile_address AS domicileAddress,
t4.gride_school_graduation_time grideSchoolGraduationTime,
t4.gride_school_enrol_time grideSchoolEnrolTime
FROM
app_grider gr
LEFT JOIN app_grider_tag t2 ON gr.grider_id = t2.persontag_owner_id
left join (
SELECT * from (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY grider_id ORDER BY gride_school_graduation_time DESC) AS rn
from app_grider_school) t3 where t3.rn = 1
) t4 on gr.grider_id = t4.grider_id
where gr.status in (1,2)
and gr.inc_day = (select inc_day from app_table_inc_day where table_name = 'app_grider')
<if test="query.collectTags !=null and query.collectTags.size>0">
and EXISTS( SELECT wt.grider_id FROM grider_work_tag wt WHERE wt.grider_id=gr.grider_id and wt.deleted = 0 and wt.history_del = 0 and
wt.tag_name in
<foreach collection="query.collectTags" item="tag" open="(" separator="," close=")">
#{tag}
</foreach>
)
</if>
and EXISTS(
SELECT gg.grider_id FROM app_gerider_grid gg WHERE gg.grider_id=gr.grider_id
<include refid="dataPer"/>
)
<include refid="dynamicQueryCondition"/>
<choose>
<when test="query.sortFlag != null and query.sortFlag== 1">
order by gr.grider_birth asc
</when>
<when test="query.sortFlag != null and query.sortFlag== 2">
order by gr.grider_birth desc NULLS LAST
</when>
<when test="query.sortFlag != null and query.sortFlag== 3">
order by gr.height asc
</when>
<when test="query.sortFlag != null and query.sortFlag== 4">
order by gr.height desc NULLS LAST
</when>
<when test="query.sortFlag != null and query.sortFlag== 5">
order by gr.enter_party_time asc
</when>
<when test="query.sortFlag != null and query.sortFlag== 6">
order by gr.enter_party_time desc NULLS LAST
</when>
<when test="query.sortFlag != null and query.sortFlag== 7">
order by gr.grider_age asc
</when>
<when test="query.sortFlag != null and query.sortFlag== 8">
order by gr.grider_age desc NULLS LAST
</when>
<when test="query.sortFlag != null and query.sortFlag== 9">
order by gr.grider_hiredate asc
</when>
<when test="query.sortFlag != null and query.sortFlag== 10">
order by gr.grider_hiredate desc NULLS LAST
</when>
<when test="query.sortFlag != null and query.sortFlag== 11">
order by t4.gride_school_enrol_time asc
</when>
<when test="query.sortFlag != null and query.sortFlag== 12">
order by t4.gride_school_enrol_time desc NULLS LAST
</when>
<when test="query.sortFlag != null and query.sortFlag== 13">
order by t4.gride_school_graduation_time asc
</when>
<when test="query.sortFlag != null and query.sortFlag== 14">
order by t4.gride_school_graduation_time desc NULLS LAST
</when>
<when test="query.sortFlag != null and query.sortFlag== 15">
order by gr.depart_date asc
</when>
<when test="query.sortFlag != null and query.sortFlag== 16">
order by gr.depart_date desc
</when>
<!-- 没有激活排序条件时 -->
<when test="query.sortFlag == null">
ORDER BY gr.status asc, gr.create_datetime DESC NULLS LAST
</when>
<!-- 出生日期 griderBirth、身高、入党时间、年龄、入职通过时间、最高学历入学时间、最高学历毕业时间-->
</choose>
</select>
<select id="taskPacketProcessList" resultType="com.sf.gis.common.domain.vo.TaskPacketProcessVo">
<!-- select * from (-->
select
tp.execount,
tp.finishedCount,
te.task_exe_id taskExeId,
tp.unSignCount,
tp.handlingCount,
<!--
tp.packet_process_id packetProcessId,
-->
te.exe_begin_time exeBeginTime,
te.exe_done_time exeDoneTime,
te.task_exe_name taskExeName,
tc.task_name taskConfigName,
tc.dept_name uploadDept,
te.task_exe_status taskExeStatus,
te.create_time createTime,
te.dis_stu disStu,
te.create_by createBy,
tt.item_name itemName,
tc.priority
from task_exe te
left join task_config tc on te.task_config_id = tc.task_config_id
left join task_template tt on tc.task_template_id = tt.task_template_id
left join task_item ti on tt.item_id = ti.item_id
<!-- left join task_item ti on tc.task_item_id=ti.item_id-->
inner join
<if test="query.isPc !=null and query.isPc!=''"> <!--pc-->
( select task_exe_id,sum((case when exe_count is null then 0 else exe_count end)) execount,sum((case when finished_count is null then 0 else finished_count end)) finishedCount,sum((case when un_sign_count is null then 0 else un_sign_count end)) unSignCount,sum((case when handling_count is null then 0 else handling_count end)) handlingCount from task_packet_process
where task_plan_data_id is null <include refid="dataPer"/> group by task_exe_id ) tp
</if>
<if test="query.isPc ==null"><!-- h5-->
( select ts.task_exe_id,sum((case when ts.exe_count is null then 0 else ts.exe_count end)) execount,
sum((case when ts.finished_count is null then 0 else ts.finished_count end)) finishedCount,
sum((case when un_sign_count is null then 0 else un_sign_count end)) unSignCount,sum((case when handling_count is null then 0 else handling_count end)) handlingCount
from task_packet_process ts left join task_exe_dis ted on ts.task_exe_id = ted.task_exe_id and ts.level2 = ted.area_code and ted.deleted = 0
where ts.task_plan_data_id is null and (ted.task_dis_status = 2 or ted.task_dis_status is null)
<include refid="dataPer"/> group by ts.task_exe_id ) tp
</if>
on te.task_exe_id=tp.task_exe_id
<where>
<!-- and ti.task_type=3-->
and te.task_gn_status=2
and te.deleted=0
and tp.execount > 0
<if test="query.keyword !=null and query.keyword!=''">
and (te.task_exe_id like CONCAT('%',#{query.keyword},'%') or tc.task_name like CONCAT('%',#{query.keyword},'%'))
</if>
<if test="query.exeType !=null">
and tt.exe_type = #{query.exeType}
</if>
<if test="query.taskDoneTimeBegin !=null">
and te.exe_done_time <![CDATA[>=]]> #{query.taskDoneTimeBegin}
</if>
<if test="query.taskDoneTimeEnd !=null">
and te.exe_done_time <![CDATA[<=]]> #{query.taskDoneTimeEnd}
</if>
<if test="query.taskExeStatus !=null">
and te.task_exe_status = #{query.taskExeStatus}
</if>
<if test="query.hasDoneTask !=null and query.hasDoneTask == 1">
and tp.finishedCount > 0
</if>
<if test="query.itemId !=null and query.itemId!=''">
and ti.serial = #{query.itemId}
</if>
<if test="query.taskExeName !=null and query.taskExeName!=''">
and te.task_exe_name like concat('%', #{query.taskExeName},'%')
</if>
<if test="query.taskExeId !=null and query.taskExeId!=''">
and te.task_exe_id like CONCAT('%',#{query.taskExeId},'%')
</if>
<if test="query.taskConfigName !=null and query.taskConfigName!=''">
and tc.task_name like concat('%', #{query.taskConfigName},'%')
</if>
<if test="query.overdueTime !=null and query.flag">
and te.exe_done_time <![CDATA[<]]> #{query.overdueTime}
<!-- and (select sum(exe_count) from task_packet_process where task_exe_id = te.task_exe_id and task_plan_data_id is null
<include refid="dataPer"/>)>
(select sum(finished_count) from task_packet_process where task_exe_id = te.task_exe_id and task_plan_data_id is null
<include refid="dataPer"/> )-->
</if>
<if test="query.adventStartTime !=null and query.flag">
and te.exe_done_time <![CDATA[>]]> #{query.adventStartTime}
</if>
<if test="query.adventEndTime !=null and query.flag">
and te.exe_done_time <![CDATA[<]]> #{query.adventEndTime}
<!-- and (select sum(exe_count) from task_packet_process where task_exe_id = te.task_exe_id and task_plan_data_id is null
<include refid="dataPer"/>)>
(select sum(finished_count) from task_packet_process where task_exe_id = te.task_exe_id and task_plan_data_id is null
<include refid="dataPer"/> )-->
</if>
<if test="!query.flag and query.overdueTime !=null and query.adventStartTime !=null and query.adventEndTime !=null">
and
(te.exe_done_time <![CDATA[<]]> #{query.overdueTime}
or (
te.exe_done_time <![CDATA[>]]> #{query.adventStartTime}
and te.exe_done_time <![CDATA[<]]> #{query.adventEndTime}
))
<!-- and (select sum(exe_count) from task_packet_process where task_exe_id = te.task_exe_id and task_plan_data_id is null
<include refid="dataPer"/>)>
(select sum(finished_count) from task_packet_process where task_exe_id = te.task_exe_id and task_plan_data_id is null
<include refid="dataPer"/> )-->
</if>
<if test="query.deptIdlist !=null and query.deptIdlist.size()>0">
and
tc.dept_id in
<foreach collection="query.deptIdlist" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</if>
<if test="query.doneStartTime !=null ">
and te.exe_begin_time <![CDATA[>=]]> #{query.doneStartTime}
</if>
<if test="query.doneEndTime !=null">
and te.exe_done_time <![CDATA[<=]]> #{query.doneEndTime}
</if>
<if test="query.disStu !=null">
and te.dis_stu = #{query.disStu}
</if>
<if test="query.createStartTime !=null ">
and te.create_time <![CDATA[>=]]> #{query.createStartTime}
</if>
<if test="query.createEndTime !=null ">
and te.create_time <![CDATA[<=]]> #{query.createEndTime}
</if>
<if test="query.priorityList !=null and query.priorityList.size()>0">
and
tc.priority in
<foreach collection="query.priorityList" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</if>
<if test="query.newDoneEndTimeFlag !=null ">
and te.exe_done_time::timestamp + '30 day' <![CDATA[>]]> #{query.newDoneEndTimeFlag}
and te.task_exe_status=1
</if>
<!-- <if test="query.exeCountFlag !=null ">
and
(select sum(exe_count) from task_packet_process where task_exe_id = te.task_exe_id and task_plan_data_id is null
<include refid="dataPer"/>)>0
</if>-->
<if test="query.tagList !=null and query.tagList.size()>0">
and tp.finishedCount <![CDATA[<]]> tp.execount
</if>
<if test="query.isPcFlag !=null and query.isPcFlag!='' and query.isPcFlag=='1'.toString()"> <!--街道待下发逻辑： -->
and (EXISTS
(
select
1
from
task_exe_dis ed
where
ed.task_exe_id = te.task_exe_id
and ed.deleted = 0
and ed.task_dis_status =1
<if test="query.jdCodes !=null and query.jdCodes.size()>0">
and
ed.area_code in
<foreach collection="query.jdCodes" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</if>
) or te.dis_stu = 0
)
</if>
<if test="query.isPcFlag !=null and query.isPcFlag!='' and query.isPcFlag=='2'.toString()"> <!--街道待下发逻辑： -->
and (
(
EXISTS
(
select
1
from
task_exe_dis ed
where
ed.task_exe_id = te.task_exe_id
and ed.deleted = 0
<if test="query.jdCodes !=null and query.jdCodes.size()>0">
and
ed.area_code in
<foreach collection="query.jdCodes" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</if>
) and
NOT EXISTS
(
select
1
from
task_exe_dis ed
where
ed.task_exe_id = te.task_exe_id
and ed.deleted = 0
and ed.task_dis_status =1
<if test="query.jdCodes !=null and query.jdCodes.size()>0">
and
ed.area_code in
<foreach collection="query.jdCodes" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</if>
)
) or
(
NOT EXISTS
(
select
1
from
task_exe_dis ed
where
ed.task_exe_id = te.task_exe_id
and ed.deleted = 0
<if test="query.jdCodes !=null and query.jdCodes.size()>0">
and
ed.area_code in
<foreach collection="query.jdCodes" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</if>
) and
te.dis_stu = 1
)
)
</if>
<if test="query.pageFlag ==null and query.pageFlag1==null">
and te.task_exe_status!=7
</if>
<if test="query.itemName !=null and query.itemName!=''">
and ti.item_name like concat('%', #{query.itemName},'%')
</if>
<!-- <include refid="queryPer"/>-->
</where>
<choose>
<when test="query.sortFlag != null and query.sortFlag== 1">
order by te.exe_begin_time asc
</when>
<when test="query.sortFlag != null and query.sortFlag== 2">
order by te.exe_begin_time desc
</when>
<when test="query.sortFlag != null and query.sortFlag== 3">
order by te.exe_done_time asc
</when>
<when test="query.sortFlag != null and query.sortFlag== 4">
order by te.exe_done_time desc
</when>
<when test="query.sortFlag != null and query.sortFlag== 5">
order by tc.priority desc,te.exe_done_time desc
</when>
<when test="query.sortFlag != null and query.sortFlag== 6">
order by tc.priority asc,te.exe_done_time desc
</when>
<!-- 没有激活排序条件时 -->
</choose>
<if test="query.createFlag !=null ">
order by te.create_time desc
</if>
<if test="query.pageFlag !=null ">
LIMIT #{query.pageSize} OFFSET #{query.startIndex}
</if>
</select>
<select id="selectnewListview" resultType="com.sf.gis.common.domain.vo.TaskSubVo" parameterType="com.sf.gis.common.domain.dto.TaskSubDto">
SELECT handleTimeStart handle_time
ts.task_sub_id taskSubId,
ts.task_config_id taskConfigId,
ts.task_sub_name taskSubName,
ts.task_sub_status taskSubStatus,
ts.address houseAddress,
ts.snapshot_table snapshotTable,
ts.snapshot_id snapshotId,
ts.task_exe_id taskExeId,
ts.update_time updateTime,
ts.create_time createTime,
ts.exe_done_time exeDoneTime,
te.task_exe_name taskExeName,
ti.item_type itemType,
ti.task_type taskType,
ts.snapshot_type snapshotType,
ts.layer_id layerId,
ts.snapshot_table snapshotTable,
ts.transfer transfer,
ts.task_source taskSource,
ts.theme_id themeId,
ts.grid_code gridCode,
ts.grid_name gridName,
ts.sq_code sqCode,
ts.sq_name sqName,
ts.lng lng,
ts.lat lat,
tc.is_clockIn isClockIn,
tc.operation_manual operationManual,
ts.zfpa_business_type zfpaBusinessType,
ts.extent_one extentOne
FROM task_sub_view ts left join task_exe_dis ted on ts.task_exe_id = ted.task_exe_id and ts.jd_code = ted.area_code and ted.deleted = 0
<if test="query.relationTheme ==1">
inner join analysis_snapshot_person_view asp on ts.snapshot_id = asp.snapshot_person_id and ts.snapshot_type = 1 and asp.person_code = #{query.relationId}
</if>
<if test="query.relationTheme ==2">
inner join analysis_snapshot_house_view asp on ts.snapshot_id = asp.snapshot_house_id and ts.snapshot_type = 2 and asp.house_code = #{query.relationId}
</if>
<if test="query.relationTheme ==3">
inner join analysis_snapshot_legal_view asp on ts.snapshot_id = asp.snapshot_legal_id and ts.snapshot_type = 3 and asp.legal_code = #{query.relationId}
</if>
<if test="query.relationTheme ==6">
inner join analysis_snapshot_building_view asp on ts.snapshot_id = asp.snapshot_building_id and ts.snapshot_type = 3 and asp.bd_code = #{query.relationId}
</if>
left join task_exe te on ts.task_exe_id=te.task_exe_id
left join task_config tc on ts.task_config_id=tc.task_config_id
left join task_template tt on tc.task_template_id = tt.task_template_id
left join task_item ti on tt.item_id = ti.item_id
<!--left join workflow_record wr on ts.task_sub_id=wr.bus_serialno-->
<!--INNER JOIN (
SELECT WR.bus_serialno,WR.wf_record_id from workflow_record AS WR
INNER JOIN (
SELECT MAX(create_time) AS create_time,bus_serialno FROM workflow_record GROUP BY bus_serialno
) AS B ON WR.bus_serialno=B.bus_serialno
WHERE WR."create_time"=B.create_time
) AS qq ON qq.bus_serialno = ts.task_sub_id-->
<where>
te.dis_stu=1 and (ted.task_dis_status = 2 or ted.task_dis_status is null)
and
(ts.deleted=0 or ts.deleted is null)
<include refid="buildQuerySelect"/>
<!-- and wr.wf_record_id IS NOT NULL-->
<if test="query.channelType==null or query.channelType==''">
<if test="query.taskSubStatus !=null">
and ts.task_sub_status = #{query.taskSubStatus}
</if>
</if>
<if test="query.channelType!=null and query.channelType!='' and query.channelType=='building'">
and (ts.task_sub_status = 0 or ts.task_sub_status=1)
and ts.theme_id=#{query.bdCode}
</if>
<if test="query.startSexeDoneTime !=null" >
and te.exe_done_time <![CDATA[>=]]> #{query.startSexeDoneTime}
</if>
<if test="query.endSexeDoneTime !=null">
and te.exe_done_time <![CDATA[<=]]> #{query.endSexeDoneTime}
</if>
<!--<if test="query.crStartTime !=null">
and ts.create_time <![CDATA[>=]]> #{query.crStartTime}
</if>
<if test="query.crEndTime !=null">
and ts.create_time <![CDATA[<=]]> #{query.crEndTime}
</if>-->
<if test="query.crStartTimeNew !=null">
and ts.create_time <![CDATA[>=]]> #{query.crStartTimeNew}
</if>
<if test="query.crEndTimeNew !=null">
and ts.create_time <![CDATA[<=]]> #{query.crEndTimeNew}
</if>
<if test="query.handleTimeStart !=null">
and ts.handle_time <![CDATA[>=]]> #{query.handleTimeStart}
</if>
<if test="query.handleTimeEnd !=null">
and ts.handle_time <![CDATA[<=]]> #{query.handleTimeEnd}
</if>
<if test="query.tagEnumValue !=null and query.tagEnumValue == 1">
and now() > te.exe_done_time
</if>
<!--<if test="query.tagEnumValue !=null and query.tagEnumValue == 2">
and DATE_ADD(now(), INTERVAL '5' DAY) > te.exe_done_time and now() < te.exe_done_time
</if>
<if test="query.tagEnumValue !=null and query.tagEnumValue == 3">
and DATE_ADD(now(), INTERVAL '5' DAY) < te.exe_done_time and now() < te.exe_done_time
</if>-->
<if test="query.tagEnumValue !=null and query.tagEnumValue == 2">
and now()::timestamp + '5 day' > te.exe_done_time and now() < te.exe_done_time
</if>
<!-- <if test="query.tagEnumValue !=null and query.tagEnumValue == 3">
and DATE_ADD(now(), INTERVAL '5' DAY) < te.exe_done_time and now() < te.exe_done_time
</if>-->
<if test="query.tagEnumValue !=null and query.tagEnumValue == 3">
and now()::timestamp + '5 day' < te.exe_done_time and now() < te.exe_done_time
</if>
<if test="query.overdueTime !=null and query.flag">
and te.exe_done_time <![CDATA[<]]> #{query.overdueTime}
</if>
<if test="query.adventStartTime !=null and query.flag">
and te.exe_done_time <![CDATA[>]]> #{query.adventStartTime}
</if>
<if test="query.adventEndTime !=null and query.flag">
and te.exe_done_time <![CDATA[<]]> #{query.adventEndTime}
</if>
<if test="!query.flag and query.overdueTime !=null and query.adventStartTime !=null and query.adventEndTime !=null">
and
(te.exe_done_time <![CDATA[<]]> #{query.overdueTime}
or (
te.exe_done_time <![CDATA[>]]> #{query.adventStartTime}
and te.exe_done_time <![CDATA[<]]> #{query.adventEndTime}
))
</if>
<if test=" query.houseAddress != null and query.houseAddress != '' ">
and ts.address like concat('%', #{query.houseAddress},'%')
</if>
<if test=" query.taskExeName != null and query.taskExeName != '' ">
and te.task_exe_name like concat('%', #{query.taskExeName},'%')
</if>
<if test=" query.taskSubName != null and query.taskSubName != '' ">
and ts.task_sub_name like concat('%', #{query.taskSubName},'%')
</if>
<if test="query.itemTypes !=null and query.itemTypes.size()>0">
and
ti.item_type in
<foreach collection="query.itemTypes" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</if>
<if test="query.taskTypes !=null and query.taskTypes.size()>0">
and
ti.task_type in
<foreach collection="query.taskTypes" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</if>
<if test="query.streetCodeList !=null and query.streetCodeList.size()>0">
and ts.exe_jd_code in
<foreach collection="query.streetCodeList" index = "index" item = "item" open="(" separator="," close=")">
#{item}
</foreach>
</if>
<if test="query.communityCodeList !=null and query.communityCodeList.size()>0">
and ts.exe_sq_code in
<foreach collection="query.communityCodeList" index = "index" item = "item" open="(" separator="," close=")">
#{item}
</foreach>
</if>
<if test="query.gridcodeList !=null and query.gridcodeList.size()>0">
and (ts.exe_grid_code in
<foreach collection="query.gridcodeList" index = "index" item = "item" open="(" separator="," close=")">
#{item}
</foreach>
or (ts.exe_sq_code=#{query.sqCodeFlag} and (ts.exe_grid_code is null or ts.exe_grid_code='') ))
</if>
<if test="query.griderId != null and query.griderId != '' ">
and ts.grider_id = #{query.griderId}
</if>
<if test="query.exeType !=null">
and ts.exe_type = #{query.exeType}
</if>
<if test="query.taskExeId !=null and query.taskExeId != ''" >
and ts.task_exe_id = #{query.taskExeId}
</if>
<if test="query.parentItem !=null ">
and ti.parent_item = #{query.parentItem}
</if>
<if test="query.gridCode !=null and query.gridCode != ''" >
and ts.grid_code = #{query.gridCode}
</if>
<if test="query.sysidList !=null and query.sysidList.size()>0">
and ts.task_sub_id in
<foreach collection="query.sysidList" index = "index" item = "sysiditem" open="(" separator="," close=")">
#{sysiditem}
</foreach>
</if>
</where>
<if test="query.sortField=='address'">
<!-- order by ts.address asc,task_sub_id desc-->
order by convert_to(ts.address, 'UTF-8' ) asc,task_sub_id desc
</if>
<if test="query.sortField=='exeDoneTime'">
order by te.exe_done_time asc,task_sub_id desc
</if>
<if test="query.statusFlag != null and query.statusFlag==2">
ORDER BY ts.update_time DESC NULLS LAST
</if>
<if test="query.statusFlag != null and query.statusFlag==1">
ORDER BY ts.update_time asc
</if>
<if test="query.statusFlag != null and query.statusFlag==3">
ORDER BY ts.create_time asc
</if>
<if test="query.statusFlag != null and query.statusFlag==4">
ORDER BY ts.create_time DESC NULLS LAST
</if>
<if test="query.statusFlag != null and query.statusFlag==5">
ORDER BY ts.exe_done_time asc
</if>
<if test="query.statusFlag != null and query.statusFlag==6">
ORDER BY ts.exe_done_time DESC NULLS LAST
</if>
OFFSET #{query.startIndex} limit #{query.pageSize}
</select>
<select id="getWorkReportPopulationGriderList" resultMap="getWorkReportPopulationListMap">
select split_part(t.griderInfo, '@', 2) as griderInfo,SUM(t.adds) as adds ,SUM(t.sign_out) as sign_out,SUM(t.temporarily_leave) as temporarily_leave,SUM(t.back) as back,SUM(t.edit) as edit,SUM(t.interview) as interview,SUM(t.interviewed) as interviewed,SUM(t.total_person) as total_person from
(
select grider_id || '@' || grider_name as griderInfo,count(1) as adds,0 as sign_out,0 as temporarily_leave,0 as back,0 as edit,0 as interview,0 as interviewed,0 as total_person from bz_person_collect_h5_union_view where opt_type = 12 <include refid="buildPopulationAreaCode"/><include refid="queryOptTime"/> and grider_id is not null and grider_name is not null group by grider_id || '@' || grider_name
union all
select grider_id || '@' || grider_name as griderInfo,0 as adds,count(1) as sign_out,0 as temporarily_leave,0 as back,0 as edit,0 as interview,0 as interviewed,0 as total_person from bz_person_collect_h5_union_view where opt_type = 14 <include refid="buildPopulationAreaCode"/><include refid="queryOptTime"/> and grider_id is not null and grider_name is not null group by grider_id || '@' || grider_name
union all
select grider_id || '@' || grider_name as griderInfo,0 as adds,0 as sign_out,count(1) as temporarily_leave,0 as back,0 as edit,0 as interview,0 as interviewed,0 as total_person from bz_person_collect_h5_union_view where opt_type = 15 <include refid="buildPopulationAreaCode"/><include refid="queryOptTime"/> and grider_id is not null and grider_name is not null group by grider_id || '@' || grider_name
union all
select grider_id || '@' || grider_name as griderInfo,0 as adds,0 as sign_out,0 as temporarily_leave,count(1) as back,0 as edit,0 as interview,0 as interviewed,0 as total_person from bz_person_collect_h5_union_view where opt_type = 16 <include refid="buildPopulationAreaCode"/><include refid="queryOptTime"/> and grider_id is not null and grider_name is not null group by grider_id || '@' || grider_name
union all
select grider_id || '@' || grider_name as griderInfo,0 as adds,0 as sign_out,0 as temporarily_leave,0 as back,count(1) as edit,0 as interview,0 as interviewed,0 as total_person from bz_person_collect_h5_union_view where opt_type = 13 <include refid="buildPopulationAreaCode"/><include refid="queryOptTime"/> and grider_id is not null and grider_name is not null group by grider_id || '@' || grider_name
union all
select grider_id || '@' || grider_name as griderInfo,0 as adds,0 as sign_out,0 as temporarily_leave,0 as back,0 as edit,count(1) as interview,0 as interviewed,0 as total_person from bz_person_collect_h5_union_view where opt_type = 11 <include refid="buildPopulationAreaCode"/><include refid="queryOptTime"/> and grider_id is not null and grider_name is not null group by grider_id || '@' || grider_name
) t
group by t.griderInfo
</select>
<select id="getWorkReportPopulationListNew" resultType="com.sf.gis.common.entity.collect.WorkReportPopulationVo">
select code,SUM(t.adds) as adds,SUM(t.adddtc) as adddtc ,SUM(t.signOut) as signOut,SUM(t.temporarilyLeave) as temporarilyLeave,SUM(t.back) as back,SUM(t.edit) as edit,SUM(t.interview) as interview,SUM(t.interviewed) as interviewed,(SUM(t.total_person) +SUM(t.interviewed)) as total_person from
(
select ${dto.areaField} as code,
count(case when opt_type = 12 then 1 end) as adds,
0 as adddtc,
count(case when opt_type = 14 then 1 end) as signOut,
count(case when opt_type = 15 then 1 end) as temporarilyLeave,
count(case when opt_type = 16 then 1 end) as back,
count(case when opt_type = 13 then 1 end) as edit,
count(case when opt_type = 11 then 1 end) as interview,
0 as interviewed,
0 as total_person from bz_person_collect_h5_union_view where 1=1 <include refid="buildPopulationAreaCode"/> <include refid="queryOptTime"/> and ${dto.areaField} is not null group by ${dto.areaField}
union all
select t.code,
0 as adds,
0 as adddtc,
0 as signOut,
0 as temporarilyLeave,
0 as back,
0 as edit,
0 as interview,
count(1) as interviewed,
0 as total_person from (select distinct person_code,batch_number,${dto.areaField} as code from bz_person_collect_h5_union_view where 1=1 <include refid="buildPopulationAreaCode"/> <include refid="queryOptTime1"/> and ${dto.areaField} is not null) t group by t.code
union all
select ${dto.areaField} as code,
0 as adds,
0 as adddtc,
0 as signOut,
0 as temporarilyLeave,
0 as back,
0 as edit,
0 as interview,
0 as interviewed,
count(1) as total_person from bz_person_collect_latest where inc_day = (select inc_day from app_table_inc_day where table_name = 'bz_person_collect_latest') and living_status in ('1','2') <include refid="buildPopulationAreaCode"/> <include refid="queryOptTime2"/> and ${dto.areaField} is not null group by ${dto.areaField}
) t group by t.code
</select>
<select id="getReportEventList" resultMap="getReportEventListMap">
select grid_code ,sum(normal_finish) as normal_finish, sum(normal) as normal,sum(immediately) as immediately from (
select left(grid_code,9) as grid_code,count(1) as normal_finish,0 as normal,0 as immediately from event_bus_info where grid_code is not null and event_class=2 and event_status='finish' <include refid="buildReportEventAreaCode"/> <include refid="queryEventReportTime"/>
<!-- select left(grid_code,9) as grid_code,count(1) as normal_finish,0 as normal,count(1) as immediately from event_bus_info where grid_code is not null and event_class=2 and event_status='finish' <include refid="buildReportEventAreaCode"/> <include refid="queryEventReportTime"/>-->
group by left(grid_code,9)
union all
select left(grid_code,9) as grid_code,0 as normal_finish,count(1) as normal,0 as immediately from event_bus_info where grid_code is not null and event_class=2 and event_status!='draft' and event_status!='delete' and event_status!='close' <include refid="buildReportEventAreaCode"/> <include refid="queryEventReportTime"/>
group by left(grid_code,9)
union all
select left(grid_code,9) as grid_code,0 as normal_finish,0 as normal,count(1) as immediately from event_bus_info where grid_code is not null and event_class=1 and event_status!='draft' and event_status!='delete' and event_status!='close' <include refid="buildReportEventAreaCode"/> <include refid="queryEventReportTime"/>
group by left(grid_code,9)
) t group by t.grid_code
</select>
<select id="getVerifyTaskList" resultMap="getVerifyTaskListMap"> name
select jd_code as code,sum(exe_count) as exe_count,sum(un_sign_count) as un_sign_count,sum(handling_count) as handling_count,sum(finished_count) as finished_count from (
select jd_code,count(1) as exe_count,0 as un_sign_count,0 as handling_count,0 as finished_count from task_sub_view ts left join task_exe te on ts.task_exe_id=te.task_exe_id where task_sub_status in (0,1,2) and te.deleted = 0 and te.task_gn_status = 2 <include refid="buildVerifyTaskAreaCode"/><include refid="queryTime1"/> group by jd_code
union all
select jd_code,0 as exe_count,count(1) as un_sign_count,0 as handling_count,0 as finished_count from task_sub_view ts left join task_exe te on ts.task_exe_id=te.task_exe_id where task_sub_status=0 and te.deleted = 0 and te.task_gn_status = 2 <include refid="buildVerifyTaskAreaCode"/> <include refid="queryTime1"/> group by jd_code
union all
select jd_code,0 as exe_count,0 as un_sign_count,count(1) as handling_count,0 as finished_count from task_sub_view ts left join task_exe te on ts.task_exe_id=te.task_exe_id where task_sub_status=1 and te.deleted = 0 and te.task_gn_status = 2 <include refid="buildVerifyTaskAreaCode"/> <include refid="queryTime1"/> group by jd_code
union all
select jd_code,0 as exe_count,0 as un_sign_count,0 as handling_count,count(1) as finished_count from task_sub_view ts left join task_exe te on ts.task_exe_id=te.task_exe_id where task_sub_status=2 and te.deleted = 0 and te.task_gn_status = 2 <include refid="buildVerifyTaskAreaCode"/> <include refid="queryTime1"/> group by jd_code
) t group by t.jd_code
</select>
select * from ( SELECT ag.grider_profile , ag.grider_id , ag.grider_name , ag.grider_tel_enc , ROUND((ST_Distance(ST_PointFromText(concat('POINT(', t.griderloc_x, ' ', t.griderloc_y, ')'), 0), ST_PointFromText (concat( 'POINT(', #{param.lng} , ' ', #{param.lat}, ')') , 0) ) * 6378.137 * 2 * PI() * 1000 / 360)::numeric, 0) AS distance FROM app_grider_loc t LEFT JOIN app_grider ag ON t.grider_code = ag.grider_id and ag.inc_day = (SELECT inc_day FROM app_table_inc_day WHERE table_name = 'app_grider') where t.inc_day = (SELECT inc_day FROM app_table_inc_day WHERE table_name = 'app_grider_loc') <if test='param.griderId != null and param.griderId != ""'> and t.grider_code != #{param.griderId} </if> ORDER BY distance) zzz where zzz.distance <![CDATA[ <= ]]> 500 ;
<insert id="saveTaskItemAttach" parameterType="java.util.List">
INSERT INTO item_attach
("work_file_id","item_id","item_version","file_large","file_little","file_type","file_name","file_url","create_by","create_time","update_by","update_time")
VALUES
<foreach collection="itemAttachList" item="attach" separator="," index="index">
(#{attach.workFileId},#{attach.itemId},#{attach.itemVersion}, #{attach.fileLarge}, #{attach.fileLittle}, #{attach.fileType},
#{attach.fileName},#{attach.fileUrl}, #{attach.createBy}, now(), #{attach.updateBy}, now())
</foreach>
</insert>
11111111111111111111111111111111111111111111
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.sf.gis.taskmanage.event.mapper.PeopleLiveTaskMapper">
<!-- 通用查询映射结果 -->
<resultMap id="BaseResultMap" type="com.sf.gis.common.entity.PeopleLiveTask">
<id column="people_live_task_id" property="peopleLiveTaskId" />
<result column="pl_task_id" property="plTaskId" />
<result column="pl_event_id" property="plEventId" />
<result column="pl_content" property="plContent" />
<result column="pl_create_time" property="plCreateTime" />
<result column="pl_limit_time" property="plLimitTime" />
<result column="pl_attachmentinfos" property="plAttachmentinfos" />
<result column="pl_user_id" property="plUserId" />
<result column="pl_user_name" property="plUserName" />
<result column="handle_status" property="handleStatus" />
<result column="handle_time" property="handleTime" />
<result column="handle_user_id" property="handleUserId" />
<result column="handle_user_name" property="handleUserName" />
<result column="approve_content" property="approveContent" />
<result column="remark" property="remark" />
<result column="order_num" property="orderNum" />
<result column="title" property="title" />
<result column="type" property="type" />
<result column="report_time" property="reportTime" />
<result column="incident_address" property="incidentAddress" />
<result column="handle_attachmentinfos" property="handleAttachmentinfos" />
<result column="create_time" property="createTime" />
<result column="update_time" property="updateTime" />
</resultMap>
<!-- 通用查询结果列 -->
<sql id="Base_Column_List">
people_live_task_id, pl_task_id, pl_event_id, pl_content, pl_create_time, pl_limit_time, pl_attachmentinfos, pl_user_id, pl_user_name, handle_status, handle_time, handle_user_id, handle_user_name, approve_content, remark, order_num,
title, type, report_time, incident_address, handle_attachmentinfos, create_time, update_time
</sql>
<select id="selectnewCountList" resultType="java.lang.Integer">
select
count(1) from
(
SELECT
distinct
pt.title,
pt.type,
pt.report_time reportTime,
pt.incident_address incidentAddress,
pt.pl_event_id plEventId,
pt.people_live_task_id peopleLiveTaskId,
pt.order_num orderNum,
pt.handle_status handleStatus
FROM
people_live_task pt
where 1=1
<if test="query.eventAddr != null and query.eventAddr != ''">
and
pt.incident_address like concat('%',#{query.eventAddr},'%')
</if>
<if test="query.title != null and query.title != ''">
and
pt.title like concat('%',#{query.title},'%')
</if>
<if test="query.start != null and query.start != ''">
and pt.report_time >= CAST(#{query.start} AS TIMESTAMP)
</if>
<if test="query.end != null and query.end != ''">
and pt.report_time <![CDATA[<=]]> CAST(#{query.end} AS TIMESTAMP)
</if>
<if test="query.overdue !=null and query.overdue != '' and query.overdue==2 ">
and pt.pl_limit_time <![CDATA[>=]]> #{query.createStartTime}
</if>
<if test="query.overdue !=null and query.overdue != '' and query.overdue==1 ">
and pt.pl_limit_time <![CDATA[<=]]> #{query.createStartTime}
</if>
<if test="query.griderId != null and query.griderId != ''">
and pt.pl_user_id = #{query.griderId}
</if>
<if test="query.sysidList !=null and query.sysidList.size()>0">
and pt.people_live_task_id in
<foreach collection="query.sysidList" index = "index" item = "sysiditem" open="(" separator="," close=")">
#{sysiditem}
</foreach>
</if>
and pt.handle_time is null
) tt
</select>
<select id="selectListPage" resultType="com.sf.gis.common.domain.vo.PeopleLiveTaskVo">
select * from (
SELECT
distinct
pt.title,
pt.type,
pt.report_time reportTime,
pt.incident_address incidentAddress,
pt.pl_event_id plEventId,
pt.people_live_task_id peopleLiveTaskId,
pt.order_num orderNum,
pt.handle_status handleStatus,
pt.pl_limit_time limitTime
FROM
people_live_task pt
where 1=1
<if test="query.eventAddr != null and query.eventAddr != ''">
and
pt.incident_address like concat('%',#{query.eventAddr},'%')
</if>
<if test="query.title != null and query.title != ''">
and
pt.title like concat('%',#{query.title},'%')
</if>
<if test="query.start != null and query.start != ''">
and pt.report_time >= CAST(#{query.start} AS TIMESTAMP)
</if>
<if test="query.end != null and query.end != ''">
and pt.report_time <![CDATA[<=]]> CAST(#{query.end} AS TIMESTAMP)
</if>
<if test="query.overdue !=null and query.overdue != '' and query.overdue==2 ">
and pt.pl_limit_time <![CDATA[>=]]> #{query.createStartTime}
</if>
<if test="query.overdue !=null and query.overdue != '' and query.overdue==1 ">
and pt.pl_limit_time <![CDATA[<=]]> #{query.createStartTime}
</if>
<if test="query.griderId != null and query.griderId != ''">
and pt.pl_user_id = #{query.griderId}
</if>
<if test="query.sysidList !=null and query.sysidList.size()>0">
and pt.people_live_task_id in
<foreach collection="query.sysidList" index = "index" item = "sysiditem" open="(" separator="," close=")">
#{sysiditem}
</foreach>
</if>
and pt.handle_time is null
) tt
order by tt.reportTime desc
LIMIT #{query.pageSize} OFFSET #{query.startIndex}
</select>
<select id="selectqueryList" resultType="com.sf.gis.common.domain.vo.SpvExeVo" parameterType="com.sf.gis.common.domain.dto.SpvExequeryDto">
select * from (
SELECT
distinct
se.exe_done_time exeDoneTime,
se.spv_exe_name spvExeName,
se.create_by createBy,
ser.handle_time distributeTime,
ser.spv_start_time spvStartTime,
ser.task_num taskNum,
stp.task_total_count houseTotalCount,
stp.task_finished_count houseFinishedCount,
stp.finish_time finishTime,
se.spv_exe_id spvExeId,
se.create_dept_name createDeptName,
stp.error_count errorCount,
stp.task_total_count taskTotalCount,
ser.over_time overTime
FROM
spv_exe se
left join spv_taskexe_process stp on se.spv_exe_id = stp.spv_exe_id
left join spv_exe_area_rel ser on se.spv_exe_id = ser.spv_exe_id
where 1=1
and EXISTS( select 1 from spv_auth_person t3 where se.spv_exe_id = t3.spv_exe_id AND t3.deleted = 0 AND t3.assign_person_id=#{query.userId})
<if test="query.spvTaskType !=null">
and se.spv_task_type = #{query.spvTaskType}
</if>
<if test="query.createByandSpvExeName != null and query.createByandSpvExeName != ''">
and
(
se.create_by like concat('%',#{query.createByandSpvExeName},'%')
or
se.spv_exe_name like concat('%',#{query.createByandSpvExeName},'%')
)
</if>
<if test="query.exeDoneTime != null and query.distributeTime != null">
and ser.handle_time <![CDATA[>=]]> #{query.distributeTime}
and se.exe_done_time <![CDATA[<=]]> #{query.exeDoneTime}
</if>
<!-- <if test="query.exeDoneTime != null and query.distributeTime != null">
and ser.handle_time <![CDATA[>]]> #{query.distributeTime}
</if>-->
<!-- null的时候展示进行中和已完成数据-->
<if test="query.exeStatusflag == null">
and se.exe_status = 2
</if>
<!-- 1已完成-->
<if test="query.exeStatusflag==1">
and se.exe_status = 2
and stp.finish_time is not null
</if>
<!--2 进行中-->
<if test="query.exeStatusflag==2">
and se.exe_status = 2
and stp.finish_time is null
</if>
<!--已超期-->
<if test="query.overdueTimeflag==1">
and se.exe_done_time <![CDATA[<]]> #{query.overdueTime}
</if>
<!-- 未超期-->
<if test="query.overdueTimeflag==2">
and se.exe_done_time <![CDATA[>=]]> #{query.overdueTime}
</if>
and se.deleted=0
) tt
order by tt.exeDoneTime asc
LIMIT #{query.pageSize} OFFSET #{query.startIndex}
</select>
<select id="selectnewCountLists" resultType="java.lang.Integer">
select
count(1) from
(
SELECT
distinct
pt.title,
pt.type,
pt.report_time reportTime,
pt.incident_address incidentAddress,
pt.pl_event_id plEventId,
pt.people_live_task_id peopleLiveTaskId,
pt.order_num orderNum,
pt.handle_status handleStatus
FROM
people_live_task pt
where 1=1
<if test="query.eventAddr != null and query.eventAddr != ''">
and
pt.incident_address like concat('%',#{query.eventAddr},'%')
</if>
<if test="query.title != null and query.title != ''">
and
pt.title like concat('%',#{query.title},'%')
</if>
<if test="query.start != null and query.start != ''">
and pt.report_time >= CAST(#{query.start} AS TIMESTAMP)
</if>
<if test="query.end != null and query.end != ''">
and pt.report_time <![CDATA[<=]]> CAST(#{query.end} AS TIMESTAMP)
</if>
<if test="query.griderId != null and query.griderId != ''">
and pt.pl_user_id = #{query.griderId}
</if>
<if test="query.finish==1">
and pt.handle_time is not null
</if>
<!--2 进行中-->
<if test="query.finish==2">
and pt.handle_time is null
</if>
) tt
</select>
<select id="selectListPages" resultType="com.sf.gis.common.domain.vo.PeopleLiveTaskVo">
select * from (
SELECT
distinct
pt.title,
pt.type,
pt.report_time reportTime,
pt.incident_address incidentAddress,
pt.pl_event_id plEventId,
pt.people_live_task_id peopleLiveTaskId,
pt.order_num orderNum,
pt.handle_status handleStatus
FROM
people_live_task pt
where 1=1
<if test="query.eventAddr != null and query.eventAddr != ''">
and
pt.incident_address like concat('%',#{query.eventAddr},'%')
</if>
<if test="query.title != null and query.title != ''">
and
pt.title like concat('%',#{query.title},'%')
</if>
<if test="query.start != null and query.start != ''">
and pt.report_time >= CAST(#{query.start} AS TIMESTAMP)
</if>
<if test="query.end != null and query.end != ''">
and pt.report_time <![CDATA[<=]]> CAST(#{query.end} AS TIMESTAMP)
</if>
<if test="query.griderId != null and query.griderId != ''">
and pt.pl_user_id = #{query.griderId}
</if>
<if test="query.finish==1">
and pt.handle_time is not null
</if>
<!--2 进行中-->
<if test="query.finish==2">
and pt.handle_time is null
</if>
) tt
order by tt.reportTime desc
LIMIT #{query.pageSize} OFFSET #{query.startIndex}
</select>
<select id="getEventStatistics" resultType="com.sf.gis.common.domain.vo.EventStaticVo">
SELECT
count(1) as total_count,
SUM(CASE WHEN t.pl_limit_time<![CDATA[<]]> NOW() THEN 1 ELSE 0 END) AS overdue_event_count
FROM
people_live_task t
where t.pl_user_id = #{griderId}
and t.handle_time is null
</select>
<select id="selectList1" resultType="com.sf.gis.common.entity.PeopleLiveTask">
select * from people_live_task where 1=1
<if test="query.gridCode != null and query.gridCode != ''">
and grid_code = #{query.gridCode}
</if>
<if test="query.reportStartTime != null and query.reportStartTime != ''">
and report_time >= CAST(#{query.reportStartTime} AS TIMESTAMP)
</if>
<if test="query.reportEndTime != null and query.reportEndTime != ''">
and report_time <![CDATA[<=]]> CAST(#{query.reportEndTime} AS TIMESTAMP)
</if>
</select>
</mapper>