delete from dispatch.rd_employee_summary where source_party=0 and id in
#{item}
INSERT INTO dispatch.rd_employee_summary (
source_party,
id,
supervise_region_code,
security_station_id,
security_station_name,
supervise_depart_id,
supervise_domain_id,
supervise_depart_name,
`name`,
head_photo,
contact,
company_name,
`status`,
checked_status,
authenticated_status,
military_status,
security_certificate_no,
occupation_type,
hired_date,
leave_time,
insure,
organization_id,
tenant_user_id,
tenant_employee_id,
last_sync_time,
last_updated_time,
device_number,
tenant_im_user_id,
idCard_no,
cid,
sex,responsible_of_station_id,institutional,industry,`position`,is_domain_admin,company_level,resource_type,third_party,
security_station_type
)
select
0,
p1.id,p1.supervise_region_code,p1.ITEM_attendanceSiteId,p1.ITEM_attendanceSiteName,
p1.ITEM_officePoliceAddressID,p1.supervise_domain_id,p1.ITEM_officePoliceAddress,p1.name,p1.head_photo,
p1.telephone,p1.orgName,p1.status,p1.checked_status,p1.authenticated_status,p1.military_status,ifnull(p1.securityCredentialNo, '#1' ) as securityCredentialNo,
p1.occupation_type,p1.hired_date,p1.leave_time, p1.insure, p1.organization_id, p1.tenant_user_id,p1.employeeId,
p1.last_sync_time,p1.last_updated_time,p1.device_number,p1.tenant_im_user_id,p1.number,p1.cid,p1.sex,p1.responsible_of_station_id,
p1.institutional_code,
p1.industry_code,p1.`position`,p1.is_domain_admin,p1.company_level,p1.resource_type,p1.third_party,p1.security_station_type
from (
SELECT
ee.id,
ifnull(SUBSTR(o.supervise_region_code,1,10),'#fk-0830') as supervise_region_code,
(case when o.industry_code='04' OR o.industry_code='05' OR o.industry_code='03' then ifnull(O.supervise_depart_id, '#1' ) else ifnull(k1.ID, '#1' ) end) as ITEM_attendanceSiteId,
(case when o.industry_code='04' OR o.industry_code='05' OR o.industry_code='03' then ifnull(O.supervise_depart_name, '#1' ) else ifnull(k1.ITEM_attendanceSiteName, '#1' ) end) as ITEM_attendanceSiteName,
(case when o.industry_code='04' OR o.industry_code='05' OR o.industry_code='03' then ifnull(O.supervise_depart_id, '#1') else ifnull(k1.ITEM_officePoliceAddressID, '#1' ) end) as ITEM_officePoliceAddressID,
'#fk-0830' as supervise_domain_id,
(case when o.industry_code='04' OR o.industry_code='05' OR o.industry_code='03' then ifnull(O.supervise_depart_name, '#1') else ifnull(k1.ITEM_officePoliceAddress, '#1' ) end) as ITEM_officePoliceAddress,
u.`name`,
c.head_photo,
SUBSTR(ifnull(u.telephone,''),1,28) as telephone,
o.`name` AS orgName,
ee.`status`,
u.checked_status,
u.authenticated_status,
u.military_status,
ifnull(
(SELECT SUBSTR(zsbh,1,38) FROM obpm2.tenant_user_credential_details
WHERE tenant_user_id=u.id and areaCode LIKE concat(SUBSTR(o.supervise_region_code,1,2),'%')
and active=1 and state=1 and certificateType='1' LIMIT 1),'#1') AS securityCredentialNo,
ee.occupation_type,
ee.leave_time AS leave_time,
ifnull( ee.insure, 0 ) as insure,
ee.organization_id,
ee.tenant_user_id,
(case
when o.industry_code in ('04') then
when o.industry_code in ('05','03') or
o.institutional_code in ('0106','0107','0109','0406','0407','0409','0406','0407','0409','0607','0608','0609') then '' else
=CURRENT_DATE)
]]>
end) as responsible_of_station_id,
ee.id as employeeId,
CURRENT_TIMESTAMP as last_sync_time,
ee.last_updated_time AS last_updated_time,
'' as device_number,
ifnull( u.im_identity, '#' ) as tenant_im_user_id,
c.number,
u.cid,
u.sex,
SUBSTR(ifnull( o.institutional_code, '' ),1,28) as institutional_code,
SUBSTR(ifnull( o.industry_code, '' ),1,28) as industry_code,
ee.`position`,ee.is_domain_admin,o.level as company_level,
ifnull(k1.resource_type,0) as resource_type,
(case when SUBSTR(ifnull( o.institutional_code, '' ),1,4)='0201'
then (select i1.ITEM_PERSON_SOURCE_ID from baibaodunflow.tlk_service_person i1
where i1.ITEM_PERSON_ID=k1.ITEM_securityId limit 1
) else ''
end) as third_party,
(case when IFNULL(k1.ITEM_attendanceSiteType,'')!='' then k1.ITEM_attendanceSiteType
else (case when INSTR(IFNULL(k1.ITEM_PROJECT_ID,''),'--__zNNa8cuUuYsO64cQ2u2') >0
then (case o.institutional_code
when '0219' then (select id from dispatch.rd_dynamic_station_types h1 where h1.name like '志愿者%' limit 1)
when '0222' then (select id from dispatch.rd_dynamic_station_types h1 where h1.name like '医%' limit 1)
when '0401' then (select id from dispatch.rd_dynamic_station_types h1 where h1.name like '医%' limit 1)
when '0223' then (select id from dispatch.rd_dynamic_station_types h1 where h1.name like '裁判%' limit 1)
when '0221' then (select id from dispatch.rd_dynamic_station_types h1 where h1.name like '无人机%' limit 1)
when '0102' then '999999'
else (select id from dispatch.rd_dynamic_station_types h1 where h1.name like '其他%' limit 1) end) else k1.ITEM_attendanceSiteType end)
end) as security_station_type
FROM obpm2.tenant_users u
LEFT JOIN obpm2.tenant_user_credentials c ON u.selected_credential_id = c.id
JOIN obpm2.tenant_employees ee ON ee.tenant_user_id = u.id
JOIN obpm2.tenant_organizations o ON o.id = ee.organization_id
LEFT JOIN (
=CURRENT_DATE
order by IFNULL(CAST(t1.ITEM_ATT_TYPE AS UNSIGNED), 0) asc,t1.ITEM_attendanceStartDate asc
)
) ]]> as k1 on k1.ITEM_securityId=ee.id
where ee.status!=1 and ee.id in
#{item}
order by resource_type asc,ITEM_attendanceStartDate asc
) as p1
ON DUPLICATE KEY UPDATE
source_party=0,
supervise_region_code=p1.supervise_region_code,security_station_id=p1.ITEM_attendanceSiteId,
security_station_name=p1.ITEM_attendanceSiteName,supervise_depart_id= p1.ITEM_officePoliceAddressID,supervise_domain_id=p1.supervise_domain_id,
supervise_depart_name=p1.ITEM_officePoliceAddress,`name`=p1.name,head_photo=p1.head_photo,
contact=p1.telephone,company_name=p1.orgName,`status`=p1.status,
checked_status=p1.checked_status,authenticated_status=p1.authenticated_status,military_status=p1.military_status,
security_certificate_no=p1.securityCredentialNo,occupation_type=p1.occupation_type,hired_date=p1.hired_date,
leave_time=p1.leave_time,insure=p1.insure,last_updated_time=p1.last_updated_time,
device_number=p1.device_number,tenant_im_user_id=p1.tenant_im_user_id,idCard_no=p1.number,cid=p1.cid,sex=p1.sex,
responsible_of_station_id=p1.responsible_of_station_id,
institutional = p1.institutional_code,
industry = p1.industry_code,
third_party=p1.third_party,
`position` = p1.`position`,is_domain_admin=p1.is_domain_admin,company_level=p1.company_level,resource_type=p1.resource_type,
security_station_type = p1.security_station_type
UPDATE dispatch.rd_employee_summary e
JOIN obpm2.tenant_users u ON e.tenant_user_id = u.id
join obpm2.tenant_user_credentials c on c.tenant_user_id=u.id
join obpm2.tenant_organizations o on o.id=e.organization_id
set e.authenticated_status=u.authenticated_status,
e.`name`=u.`name`,e.head_photo=c.head_photo,
e.contact=SUBSTR(ifnull(u.telephone,''),1,28),
e.checked_status=u.checked_status,
e.military_status=u.military_status,
e.security_certificate_no=ifnull(
(SELECT SUBSTR(zsbh,1,38) FROM obpm2.tenant_user_credential_details k
WHERE k.tenant_user_id=u.id and k.areaCode LIKE concat(SUBSTR(o.supervise_region_code,1,2),'%')
and k.active=1 and k.state=1 and k.certificateType='1' LIMIT 1),'#1'),
e.tenant_im_user_id=ifnull( u.im_identity, '#' ),
e.idCard_no=c.number,
e.cid=u.cid,
e.sex=u.sex
where e.tenant_user_id in
#{item}
DELETE FROM dispatch.rd_security_station_summary WHERE id in
#{item}
INSERT INTO dispatch.rd_security_station_summary (
id,
NAME,
station_type,
company_name,
principal_id,
principal_name,
principal_contact,
service_scope,
address,
lon_lat,
organization_id,
supervise_domain_id,
supervise_depart_id,
last_sync_time,
last_updated_time,
site_state,
begin_date,
end_date,
supervise_region_code,
count_of_security_man,
supervise_depart_name,
institutional,
industry,
perform_range,
resource_type,
project_id,
project_name
)
SELECT p1.ID,p1.ITEM_attendanceSiteName,p1.ITEM_attendanceSiteType,p1.NAME,p1.ITEM_principal,p1.ITEM_principalName,p1.ITEM_principalPhoneNo,
p1.ITEM_serveObjectName,p1.ITEM_attendanceSiteFullAddress,p1.lonlat,p1.organization_id,p1.supervise_domain_id,p1.supervise_depart_id,p1.last_sync_time,
p1.LASTMODIFIED,p1.ITEM_attendanceSiteState,p1.ITEM_attendanceStartDate,p1.ITEM_attendanceEndDate,p1.supervise_region_code,p1.count_of_security_man,
p1.ITEM_officePoliceAddress,
p1.institutional_code,
p1.industry_code,p1.perform_range,p1.resource_type,p1.project_id,p1.project_name
FROM (
SELECT bi.ID,
bi.ITEM_attendanceSiteName,
(case when IFNULL(bi.ITEM_attendanceSiteType,'')!='' then bi.ITEM_attendanceSiteType
else (case when INSTR(IFNULL(bi.ITEM_PROJECT_ID,''),'--__zNNa8cuUuYsO64cQ2u2') >0
then (case o.institutional_code
when '0219' then (select id from dispatch.rd_dynamic_station_types h1 where h1.name like '志愿者%' limit 1)
when '0222' then (select id from dispatch.rd_dynamic_station_types h1 where h1.name like '医%' limit 1)
when '0401' then (select id from dispatch.rd_dynamic_station_types h1 where h1.name like '医%' limit 1)
when '0223' then (select id from dispatch.rd_dynamic_station_types h1 where h1.name like '裁判%' limit 1)
when '0221' then (select id from dispatch.rd_dynamic_station_types h1 where h1.name like '无人机%' limit 1)
when '0102' then '999999'
else (select id from dispatch.rd_dynamic_station_types h1 where h1.name like '其他%' limit 1) end)else bi.ITEM_attendanceSiteType end)
end) as ITEM_attendanceSiteType,
o.NAME,
bi.ITEM_principal,
bi.ITEM_principalName,
bi.ITEM_principalPhoneNo,
bi.ITEM_serveObjectName,
bi.ITEM_attendanceSiteFullAddress,
CONCAT("{""lat"":",ITEM_attendanceSiteLatitude,",""lon"":",ITEM_attendanceSiteLongitude,"}")AS lonlat,
o.id as organization_id,
'#nk-1007' as supervise_domain_id,
ifnull( bi.ITEM_officePoliceAddressID, '#' ) AS supervise_depart_id,
CURRENT_TIMESTAMP as last_sync_time,
bi.LASTMODIFIED,
bi.ITEM_attendanceSiteState,
ifnull(bi.ITEM_attendanceStartDate,DATE_ADD(CURRENT_DATE, INTERVAL -1 DAY)) as ITEM_attendanceStartDate,
ifnull(bi.ITEM_attendanceEndDate,DATE_ADD(CURRENT_DATE, INTERVAL -1 DAY)) as ITEM_attendanceEndDate,
ifnull(SUBSTR(o.supervise_region_code,1,10),'#fk-0830') as supervise_region_code,
(select count(distinct ITEM_securityId) from baibaodunflow.tlk_attendance_site_person_info x1 where x1.ITEM_attendanceSiteId=bi.ID) as count_of_security_man,
ITEM_officePoliceAddress,
SUBSTR(ifnull( o.institutional_code, '' ),1,10) as institutional_code,
SUBSTR(ifnull( o.industry_code, '' ),1,10) as industry_code,
item_performRange as perform_range,
IFNULL((case when bi.ITEM_ATT_TYPE='1' then 1 when bi.ITEM_ATT_TYPE='2' then 2 when bi.ITEM_ATT_TYPE in ('3','4') then 4 else 0 end),0) as resource_type,
bi.ITEM_PROJECT_ID as project_id,bi.ITEM_PROJECT_NAME as project_name
FROM baibaodunflow.tlk_attendance_site_base_info bi JOIN obpm2.tenant_organizations o ON o.id = bi.DOMAINID
WHERE
=CURRENT_DATE
]]>
AND bi.id in
#{item}
) as p1
ON DUPLICATE KEY UPDATE NAME=p1.ITEM_attendanceSiteName,
station_type=p1.ITEM_attendanceSiteType,
company_name=p1.NAME,
principal_id=p1.ITEM_principal,
principal_name=p1.ITEM_principalName,
principal_contact=p1.ITEM_principalPhoneNo,
service_scope= p1.ITEM_serveObjectName,
address=p1.ITEM_attendanceSiteFullAddress,
lon_lat=p1.lonlat,
supervise_domain_id=p1.supervise_domain_id,
supervise_depart_id=p1.supervise_depart_id,
last_updated_time=p1.LASTMODIFIED,
site_state=p1.ITEM_attendanceSiteState,
begin_date=p1.ITEM_attendanceStartDate,
end_date=p1.ITEM_attendanceEndDate,
supervise_region_code=p1.supervise_region_code,
count_of_security_man=p1.count_of_security_man,
supervise_depart_name=p1.ITEM_officePoliceAddress,
institutional=p1.institutional_code,
industry=p1.industry_code,
perform_range=p1.perform_range,
resource_type=p1.resource_type,
project_id=p1.project_id,project_id=p1.project_name
DELETE FROM dispatch.rd_company_summary WHERE id in
#{item}
INSERT INTO dispatch.rd_company_summary (
id,
NAME,
STATUS,
legal,
legal_telephone,
institutional,
register_address,
business_address,
service_scope,
organization_id,
supervise_domain_id,
supervise_depart_id,
last_sync_time,
last_updated_time,
supervise_region_code,
lon_lat,
count_of_activity_station,
count_of_security_man,
supervise_depart_name,
industry,
points,
count_of_desk,
count_of_men
)
SELECT
p1.id,p1.name,p1.ITEM_companyStatus,p1.legal_person_name,p1.legal_person_telephone,p1.institutional_code,
p1.place_of_register_address,p1.place_of_business_address,p1.service_scope_description,p1.organization_id,p1.supervise_domain_id,
p1.supervise_depart_id,p1.last_sync_time,p1.last_updated_time,p1.supervise_region_code,p1.lonlat,p1.count_of_activity_station,
p1.count_of_security_man,supervise_depart_name,industry_code,points,count_of_desk,count_of_men
FROM (SELECT
o.id,
o.`name`,(
CASE
WHEN o.`status` = 2 THEN
2
WHEN o.`status` = 3 THEN
1
WHEN o.`status` = 4 THEN
3 ELSE 0
END
) AS ITEM_companyStatus,
ci.legal_person_name,
ci.legal_person_telephone,
ifnull( o.institutional_code, '' ) as institutional_code,
o.place_of_register_address,
o.place_of_business_address,
ci.service_scope_description,
o.id as organization_id,
'#nk-1007' as supervise_domain_id,
CURRENT_TIMESTAMP as last_sync_time,
ifnull(o.last_updated_time,CURRENT_TIMESTAMP) as last_updated_time,
CONCAT("{""lat"":",o.latitude,",""lon"":",o.longitude,"}")AS lonlat,
(case when o.industry_code='04' OR o.industry_code='05' OR o.industry_code='03' then 0 else
=CURRENT_DATE and bi.DOMAINID=o.id)
]]> end) as count_of_activity_station,
(case when o.industry_code!='04' then 0 else
end) as count_of_men,
(case when o.industry_code='04' OR o.industry_code='05' OR o.industry_code='03' then 0 else
(select count(1) from obpm2.tenant_employees ee where ee.`status`=0 and ee.organization_id=o.id and ee.occupation_type=1 )
end) as count_of_security_man,
o.supervise_depart_id as supervise_depart_id,
ifnull(o.supervise_depart_name,'#') as supervise_depart_name,
ifnull(o.industry_code,'#') as industry_code,
=CURRENT_DATE) as count_of_desk
]]>,
o.supervise_region_code
FROM obpm2.tenant_organizations o left join obpm2.tenant_companies ci on ci.id=o.id
where `status` not in (2,3,4) and approved_information_status=1 and o.id in
#{item}
) AS p1
ON DUPLICATE KEY UPDATE
NAME=p1.name,STATUS=p1.ITEM_companyStatus,
legal=p1.legal_person_name,
legal_telephone=p1.legal_person_telephone,
institutional=p1.institutional_code,
register_address=p1.place_of_register_address,
business_address=p1.place_of_business_address,
service_scope=p1.service_scope_description,
organization_id=p1.organization_id,
supervise_domain_id=p1.supervise_domain_id,
supervise_depart_id=p1.supervise_depart_id,
last_updated_time=p1.last_updated_time,
supervise_region_code=p1.supervise_region_code,
lon_lat=p1.lonlat,count_of_activity_station=p1.count_of_activity_station,count_of_security_man=p1.count_of_security_man,
supervise_depart_name=p1.supervise_depart_name,
industry=p1.industry_code,
points=p1.points,
count_of_desk=p1.count_of_desk,
count_of_men=p1.count_of_men
update dispatch.rd_company_summary o
=CURRENT_DATE
)
]]> where o.id in
#{item}
update dispatch.rd_company_summary o
where o.id in
#{item}
update dispatch.rd_community_user_summary set is_deleted=1 where id in
#{item}
INSERT INTO dispatch.rd_community_user_summary (
id,
name,
employee_id,
tenant_user_id,
organization_id,
organization_name,
idCard_no,
supervise_depart_id,
supervise_depart_name,
last_sync_time,
last_updated_time,
is_deleted
)
SELECT
p1.ID,p1.ITEM_NAME,p1.ITEM_EMPLOYEE_ID,p1.ITEM_TENANT_USER_ID,p1.ITEM_ORGANIZATION_ID,p1.ITEM_ORGANIZATION_NAME,
p1.ITEM_IDCARDNO,item_superviseid,item_supervisename,CURRENT_TIMESTAMP,p1.LASTMODIFIED,0
FROM (SELECT
ID,IFNULL(ITEM_NAME,'#1') AS ITEM_NAME,IFNULL(ITEM_IDCARDNO,'#1') AS ITEM_IDCARDNO,IFNULL(ITEM_EMPLOYEE_ID,'#1') AS ITEM_EMPLOYEE_ID,IFNULL(ITEM_TENANT_USER_ID,'#') AS ITEM_TENANT_USER_ID,
IFNULL(ITEM_ORGANIZATION_ID,'#1') AS ITEM_ORGANIZATION_ID,
IFNULL(ITEM_ORGANIZATION_NAME,'#1') AS ITEM_ORGANIZATION_NAME,IFNULL(LASTMODIFIED,CURRENT_TIMESTAMP) AS LASTMODIFIED,
IFNULL(item_superviseid,'#1') AS item_superviseid,IFNULL(item_supervisename,'#1') AS item_supervisename
FROM baibaodunflow.tlk_pt_securityman k
where k.id in
#{item}
) AS p1
ON DUPLICATE KEY UPDATE
name=p1.ITEM_NAME,employee_id=p1.ITEM_EMPLOYEE_ID,tenant_user_id=p1.ITEM_TENANT_USER_ID,organization_id=p1.ITEM_ORGANIZATION_ID,
organization_name=p1.ITEM_ORGANIZATION_NAME,idCard_no=p1.ITEM_IDCARDNO,supervise_depart_id = item_superviseid,
supervise_depart_name = item_supervisename, last_updated_time=p1.LASTMODIFIED,is_deleted=0
INSERT INTO `dispatch`.`department_level`
(`id`, `name`, `superior`, `is_online`, `indexCode`, `regionId`,last_updated_time)
select
id,
`name`,
superior,online,index_code,region_id,CURRENT_TIMESTAMP
from (
select
x.id,
x.`name`,
x.superior,
(case when x.`status`='启用' then 1 else 0 end) as online,
x.index_code,
x.region_id
from obpm2.t_supervise_department x where x.id in
#{item}
) as p1
ON DUPLICATE KEY UPDATE `name`=p1.`name`, `superior`=p1.superior, `is_online`=p1.online,
`indexCode`=p1.index_code, `regionId`=p1.region_id,last_updated_time=CURRENT_TIMESTAMP;
insert into dispatch.rd_dispatch_data_scopes
(id,scope,scope_type,note,last_updated_time)
select
id,
item_scopes,0, '根据规则,自动授权',CURRENT_TIMESTAMP
from (
select
x.id,
from obpm2.t_supervise_department x where x.id in
#{item}
and x.region_id in ('110000','910000','140000','140500','350200') and x.`name` like '%派出所%'
) as p1
ON DUPLICATE KEY UPDATE scope=item_scopes,note='根据规则,自动授权.2',last_updated_time=CURRENT_TIMESTAMP
update dispatch.rd_dispatch_data_scopes set scope=null,last_updated_time=CURRENT_TIMESTAMP,note='置空' WHERE scope_type in (1) and id in
#{item}
insert into dispatch.rd_dispatch_data_scopes
(id,scope,scope_type,note,last_updated_time)
select
item_attendanceSiteId,item_scopes,1, '驻勤点被哪家驻勤点所关注',CURRENT_TIMESTAMP
from (
select
x.item_attendanceSiteId,GROUP_CONCAT( DISTINCT DOMAINID) AS item_scopes
from baibaodunFlow.tlk_n_security x
where x.item_attendanceSiteId in
#{item}
GROUP BY item_attendanceSiteId
) as p1
ON DUPLICATE KEY UPDATE scope=item_scopes,last_updated_time=CURRENT_TIMESTAMP
DELETE FROM dispatch.rd_exam_site_summary WHERE id in
#{item}
INSERT INTO `dispatch`.`rd_exam_site_summary` (
`id`,
`name`,
`organization_id`,
`company_name`,
`address`,
`count_of_room`,
`capacity_of_person`,
`lon_lat`,
`status`,
`last_sync_time`,
`last_updated_time`,
`supervise_domain_id`,
`supervise_depart_id`,
`supervise_depart_name`,
`supervise_region_code`,
region_name
)
SELECT
id,ITEM_NAME,organization_id,company_name,ITEM_ADDRESS,count_of_room,
capacity_of_person,lonlat,status,last_sync_time,last_updated_time,
supervise_domain_id,supervise_depart_id,supervise_depart_name,supervise_region_code,regionName
FROM (
SELECT
bi.id,bi.ITEM_NAME,o.id as organization_id,o.name as company_name,bi.ITEM_ADDRESS,
(select count(1) from tms.tlk_exam_room t1 where t1.ITEM_EXAM_SITE_ID=bi.ID and ITEM_ROOM_ACTIVATION_MODE=1) as
count_of_room,
bi.ITEM_PERSON_NUM as capacity_of_person,
CONCAT("{""lat"":",ITEM_LATITUDE,",""lon"":",ITEM_LONGITUDE,"}")AS lonlat,
(case when ITEM_STATUS='1' then 1 else 0 end) as status,CURRENT_TIMESTAMP as last_sync_time,LASTMODIFIED as last_updated_time,
'#fk-1007' as supervise_domain_id,
o.supervise_depart_id as supervise_depart_id,
ifnull(o.supervise_depart_name,'#') as supervise_depart_name,supervise_region_code,
ifnull((select cl.ITEM_CITYNAMEINDEX from companyinfocollect.tlk_citylevel cl where bi.ITEM_REGION_DISTRICT=cl.id limit 1),'') as regionName
FROM tms.tlk_exam_site bi JOIN obpm2.tenant_organizations o ON o.id = bi.DOMAINID
WHERE bi.id in
#{item}
) as p1
ON DUPLICATE KEY UPDATE
name=p1.ITEM_NAME,
organization_id=p1.organization_id,
company_name=p1.company_name,
address=p1.ITEM_ADDRESS,
count_of_room=p1.count_of_room,
capacity_of_person=p1.capacity_of_person,
lon_lat=p1.lonlat,
status=p1.status,
last_updated_time=p1.last_updated_time,
supervise_domain_id=p1.supervise_domain_id,
supervise_depart_id=p1.supervise_depart_id,
supervise_depart_name=p1.supervise_depart_name,
supervise_region_code=p1.supervise_region_code,
region_name = regionName
DELETE FROM dispatch.rd_exam_site_room_summary WHERE id in
#{item}
INSERT INTO `dispatch`.`rd_exam_site_room_summary` (
`id`,
`name`,
`lon_lat`,
`exam_site_id`,
`exam_site_name`,
`capacity_of_person`,
`scope_range`,
`status`,
`organization_id`,
`company_name`,
`last_sync_time`,
`last_updated_time`,
`supervise_domain_id`,
`supervise_depart_id`,
`supervise_depart_name`,
`supervise_region_code`
)
SELECT
id,ITEM_ROOM_NAME,lonlat,exam_site_id,exam_site_name,capacity_of_person,
ITEM_ROOM_RANGE, ITEM_STATUS,organization_id,company_name,
last_sync_time,last_updated_time,
supervise_domain_id,supervise_depart_id,supervise_depart_name,supervise_region_code
FROM (
SELECT
ai.ID,ai.ITEM_ROOM_NAME as ITEM_ROOM_NAME,
CONCAT("{""lat"":",ai.ITEM_LATITUDE,",""lon"":",ai.ITEM_LONGITUDE,"}")AS lonlat,
ai.ITEM_EXAM_SITE_ID as exam_site_id,
bi.ITEM_NAME as exam_site_name,
ai.ITEM_ROOM_PERSON_LIMIT as capacity_of_person,
ai.ITEM_ROOM_RANGE as ITEM_ROOM_RANGE,
(case when ai.ITEM_ROOM_ACTIVATION_MODE='1' then 1 else 0 end) as ITEM_STATUS,
o.ID as organization_id,o.`name` as company_name,
CURRENT_TIMESTAMP as last_sync_time,ai.LASTMODIFIED as last_updated_time,
'#fk-1007' as supervise_domain_id,
ifnull(o.supervise_depart_id,'#') as supervise_depart_id,
ifnull(o.supervise_depart_name,'#') as supervise_depart_name,
ifnull(o.supervise_region_code,'#') as supervise_region_code
FROM tms.tlk_exam_room ai join tms.tlk_exam_site bi on ai.ITEM_EXAM_SITE_ID=bi.ID
JOIN obpm2.tenant_organizations o ON o.id = bi.DOMAINID
WHERE ai.id in
#{item}
) as p1
ON DUPLICATE KEY UPDATE
`name`=p1.ITEM_ROOM_NAME,
`lon_lat`=p1.lonlat,
`exam_site_id`=p1.exam_site_id,
`exam_site_name`=p1.exam_site_name,
`capacity_of_person`=p1.capacity_of_person,
`scope_range`=p1.ITEM_ROOM_RANGE,
`status`=p1.ITEM_STATUS,
`organization_id`=p1.organization_id,
`company_name`=p1.company_name,
last_updated_time=p1.last_updated_time,
supervise_domain_id=p1.supervise_domain_id,
supervise_depart_id=p1.supervise_depart_id,
supervise_depart_name=p1.supervise_depart_name,
supervise_region_code=p1.supervise_region_code
DELETE FROM dispatch.rd_system_exam_info_summary WHERE id in
#{item}
INSERT INTO `dispatch`.`rd_system_exam_info_summary`(`id`, `name`, `begin_time`, `end_time`,
`exam_type`, `teacher_ids`, `exam_site_room_id`,
`last_sync_time`, `last_updated_time`)
SELECT
ID,`ITEM_EXAMNAME`,item_computerStartDate,item_computerEndDate,exam_type,ITEM_PERSON, `item_placeId`,last_sync_time,last_updated_time
FROM (
'2038-01-01' then '2038-01-01' else bi.item_computerEndDate end) as item_computerEndDate,
(case bi.ITEM_EXAMTYPE when '3' then 0 when '1' then 1 end) as exam_type,
bi.ITEM_PERSON,
bi.item_placeId,
CURRENT_TIMESTAMP as last_sync_time,
LASTMODIFIED as last_updated_time
from tms.tlk_system_exam_info bi
where item_examine = '1' and bi.item_computerStartDate<=CURRENT_TIMESTAMP
and bi.item_computerEndDate>=CURRENT_TIMESTAMP
]]>
and bi.id in
#{item}
) as p1
ON DUPLICATE KEY UPDATE
`name`=p1.ITEM_EXAMNAME,
`begin_time`=p1.item_computerStartDate,
`end_time`=p1.item_computerEndDate,
`exam_type`=p1.exam_type,
`teacher_ids`=p1.ITEM_PERSON,
`exam_site_room_id`=p1.item_placeId,
`last_updated_time`=p1.last_updated_time
update dispatch.rd_dispatch_data_scopes set scope=null,last_updated_time=CURRENT_TIMESTAMP,note='非监管的集团调度' WHERE scope_type in (2) and id in
#{item}
insert into dispatch.rd_dispatch_data_scopes
(id,scope,scope_type,note,last_updated_time)
select
id,content,2, '集团公司可以访问的对象',CURRENT_TIMESTAMP
from (
select
x.id,x.content
from obpm2.tenant_data_permissions x
where x.institutional_code not in ('0107','0108','0109','0110','0407','0408','0409','0410','0407','0608','0609','0610') and x.id in
#{item}
) as p1
ON DUPLICATE KEY UPDATE scope=content,last_updated_time=CURRENT_TIMESTAMP
DELETE FROM dispatch.rd_project_summary WHERE id in
#{item}
INSERT INTO `dispatch`.`rd_project_summary`
(`id`, `name`, `type`, `state`, `lon_lat`, `organization_id`, `company_name`,`begin_date`, `end_date`, `principal_id`, `address`, `supervise_region_code`,
`supervise_domain_id`, `supervise_depart_id`, `last_sync_time`, `last_updated_time`,`supervise_depart_name`,
count_of_stations,count_of_teams,count_of_members)
SELECT p1.ID,p1.ITEM_PROJECT_NAME,p1.ITEM_PROJECT_TYPE,p1.ITEM_PREJECT_STATE,p1.lonlat,
p1.DOMAINID,p1.company_name,p1.ITEM_PROJECT_START_TIME,p1.ITEM_PROJECT_END_TIME,p1.ITEM_PRINCIPAL,p1.ITEM_ATTENDANCESITEFULLADDRESS,p1.supervise_region_code,
p1.supervise_domain_id,p1.ITEM_OFFICE_POLICE_ID,CURRENT_TIMESTAMP, p1.LASTMODIFIED,p1.supervise_depart_name,
count_of_stations,count_of_teams,count_of_members
FROM (
SELECT bi.Id,ITEM_PROJECT_NAME,ITEM_PROJECT_TYPE,ITEM_PREJECT_STATE,
CONCAT("{""lat"":",ITEM_ATTENDANCESITELATITUDE,",""lon"":",ITEM_ATTENDANCESITELONGITUDE,"}") AS lonlat,
bi.DOMAINID,o.name as company_name, ITEM_PROJECT_START_TIME,ITEM_PROJECT_END_TIME,ITEM_PRINCIPAL,ITEM_ATTENDANCESITEFULLADDRESS,'#1' as supervise_region_code,
IFNULL(ITEM_OFFICE_POLICE_ID,'#1') as supervise_domain_id,IFNULL(ITEM_OFFICE_POLICE_ADDRESS,'#1') AS supervise_depart_name,
IFNULL(ITEM_OFFICE_POLICE_ID,'#1') AS ITEM_OFFICE_POLICE_ID,bi.LASTMODIFIED,
(select count(1) from baibaodunflow.tlk_attendance_site_base_info n1 where n1.ITEM_PROJECT_ID=bi.id ) as count_of_stations,
count(bi2.Id) as count_of_teams,
(select count(1) from baibaodunflow.tlk_post_person n1 where n1.ITEM_GROUP_ID=bi2.id ) as count_of_members
FROM baibaodunflow.tlk_temporary_protection_project bi
JOIN obpm2.tenant_organizations o on o.id=bi.DOMAINID
left join baibaodunflow.tlk_post_group bi2 on bi2.ITEM_PROJECT_ID=bi.id
WHERE
AND bi.id in
#{item}
group by bi.Id
) as p1 where p1.id is not null
ON DUPLICATE KEY UPDATE `name`=p1.ITEM_PROJECT_NAME,`type`=p1.ITEM_PROJECT_TYPE,`state`=p1.ITEM_PREJECT_STATE,
`lon_lat`=p1.lonlat,`organization_id`=p1.DOMAINID,`begin_date`=p1.ITEM_PROJECT_START_TIME,`end_date`=p1.ITEM_PROJECT_END_TIME,
`principal_id`=p1.ITEM_PRINCIPAL, `address`=p1.ITEM_ATTENDANCESITEFULLADDRESS,`supervise_region_code`=p1.supervise_region_code,
`supervise_domain_id`=p1.supervise_region_code,`supervise_depart_id`=p1.supervise_domain_id,`supervise_depart_id`=p1.ITEM_OFFICE_POLICE_ID,
`last_updated_time`=p1.LASTMODIFIED,
`supervise_depart_name`=p1.supervise_depart_name,
count_of_stations=p1.count_of_stations,count_of_teams=p1.count_of_teams,count_of_members=p1.count_of_members,
company_name = p1.company_name
DELETE FROM dispatch.rd_project_team_group_summary WHERE id in
#{item}
INSERT INTO `dispatch`.`rd_project_team_group_summary`
(`id`, `name`, `project_id`, `organization_id`,`parent_id`, `last_sync_time`, `last_updated_time`)
SELECT p1.Id,p1.ITEM_GROUP_NAME,p1.ITEM_PROJECT_ID,p1.DOMAINID,ITEM_SUPER_ID,CURRENT_TIMESTAMP,p1.LASTFLOWOPERATION
FROM (
SELECT Id,ITEM_GROUP_NAME,ITEM_PROJECT_ID,DOMAINID,ITEM_SUPER_ID,LASTFLOWOPERATION
FROM baibaodunflow.tlk_temporary_protection_group bi
WHERE bi.id in
#{item}
) as p1
ON DUPLICATE KEY UPDATE `name`=p1.ITEM_GROUP_NAME, `parent_id`=ITEM_SUPER_ID,`last_updated_time`=p1.LASTFLOWOPERATION
DELETE FROM dispatch.rd_project_team_member_summary WHERE id in
#{item}
INSERT INTO `dispatch`.`rd_project_team_member_summary`
(`id`, `name`, `idCard_no`,`contact`,`person_type`, `employee_id`,`sex`,`project_team_group_id`,`project_id`,`organization_id`,`company_name`,`last_sync_time`, `last_updated_time`)
SELECT
p1.Id,p1.ITEM_NAME,p1.ITEM_ID_NUMBER,p1.ITEM_PHONE,p1.ITEM_PERSON_TYPE,p1.ITEM_PERSON_ID,p1.ITEM_SEX,p1.ITEM_GROUP_ID,p1.ITEM_PROJECT_ID,p1.DOMAINID,p1.company_name,CURRENT_TIMESTAMP,p1.LASTFLOWOPERATION
FROM (
SELECT bi.Id,bi.ITEM_NAME,ifnull(bi.ITEM_ID_NUMBER,'#1') as ITEM_ID_NUMBER,ifnull(bi.ITEM_PHONE,'#1') as ITEM_PHONE,ifnull(bi.ITEM_PERSON_TYPE,'#1') as ITEM_PERSON_TYPE,
(case when bi.ITEM_SEX='未知' then 0 when bi.ITEM_SEX='男' then 1
when bi.ITEM_SEX='女' then 2 when bi.ITEM_SEX='其他' then 3 else 1 end) as ITEM_SEX,
bi.ITEM_GROUP_ID, bi.ITEM_PERSON_ID,bi.DOMAINID,o.name as company_name, bi.LASTFLOWOPERATION,bi2.ITEM_PROJECT_ID
FROM baibaodunflow.tlk_post_person bi
join obpm2.tenant_organizations o on o.id=bi.DOMAINID
join baibaodunflow.tlk_post_group bi2 on bi.ITEM_GROUP_ID=bi2.ID
WHERE bi.id in
#{item}
) as p1
ON DUPLICATE KEY UPDATE last_updated_time=p1.LASTFLOWOPERATION
INSERT INTO `dispatch`.`rd_device_summary` (
`id`,
`device_type`,
`dispatch_no`,
`device_no`,
`device_model`,
`device_status`,
`device_ready_status`,
`organization_id`,
`organization_name`,
`security_station_id`,
`security_station_name`,
`security_depart_id`,
`last_sync_time`,
`last_updated_time`,
`lat`,
`lon`,
device_uid
)
select
id,device_type,ITEM_DEVICE_NO,ITEM_DEVICE_NO,ITEM_DEVICE_MODEL,device_status,device_ready_status,
item_domain_id,organization_name,security_station_id,security_station_name,security_depart_id,
last_sync_time,LASTMODIFIED,null,null,device_uid_value
from (
select
x.id,(case when ITEM_DEVICE_NO='1' then '0' when ITEM_DEVICE_NO='2' then '1' else '0' end) as device_type,
ITEM_DEVICE_NO,ITEM_DEVICE_MODEL,0 as device_status,0 as device_ready_status,x.item_domain_id,o.`name` as organization_name,i.ID as security_station_id,
i.ITEM_attendanceSiteName as security_station_name,i.ITEM_officePoliceAddressID as security_depart_id,
CURRENT_TIMESTAMP as last_sync_time,x.LASTMODIFIED,NULL as device_uid_value
from dispatch.tlk_hardware x join baibaodunflow.tlk_attendance_site_base_info i on x.ITEM_BINDED_USER_ATTID=i.ID
join obpm2.tenant_organizations o on o.id=x.ITEM_DOMAIN_ID where x.id in
#{item}
) as p1
ON DUPLICATE KEY UPDATE
device_type = p1.device_type,
`device_model` = p1.ITEM_DEVICE_MODEL,
`device_status` = p1.device_status,
`device_ready_status` = p1.device_ready_status,
`organization_id` = p1.item_domain_id,
`organization_name` = p1.organization_name,
`security_station_id` = p1.security_station_id,
`security_station_name` = p1.security_station_name,
security_depart_id = p1.security_depart_id,
dispatch_no = p1.ITEM_DEVICE_NO,
device_no = p1.ITEM_DEVICE_NO,
device_uid =( CASE WHEN device_uid IS NULL THEN p1.device_uid_value ELSE device_uid END)
delete from `dispatch`.`rd_dynamic_station_types`
WHERE id in
#{item}
INSERT INTO `dispatch`.`rd_dynamic_station_types` (
`id`,
`name`,
`icon`,
map_icon,
`participator_icon`,
participator_offline_icon,
`organization_id`,
`last_sync_time`
)
select
id,item_type_name,ITEM_PLACE_DETAIL_ICON,ITEM_PLACE_ICON,ITEM_PERSON_ICON,ITEM_PERSON_OFFLINE_ICON,organization_id,last_sync_time
from (
select
x.id,x.item_type_name,x.item_is_public,x.ITEM_PLACE_DETAIL_ICON,x.ITEM_PLACE_ICON,x.ITEM_PERSON_ICON,x.ITEM_PERSON_OFFLINE_ICON,(case when item_is_public=1 then '#1' else x.DOMAINID end) as organization_id,
CURRENT_TIMESTAMP as last_sync_time
from baibaodunflow.tlk_project_person_type x where x.id in
#{item}
and x.item_is_use=1
) as p1
ON DUPLICATE KEY UPDATE
`name` = p1.item_type_name,
`icon` = p1.ITEM_PERSON_ICON,
`participator_icon` = p1.ITEM_PLACE_ICON,
`organization_id` = p1.organization_id
delete from dispatch.rd_dispatch_data_scopes where id in
#{item}
and scope is null
delete from dispatch.rd_dispatch_data_scopes WHERE scope_type in (3) and id in
concat('3_',#{item})
insert into dispatch.rd_dispatch_data_scopes
(id,scope,scope_type,note,last_updated_time)
select
concat('3_',DOMAINID),item_scopes,3, '大型活动承办商的关联的项目Id',CURRENT_TIMESTAMP
from (
select
x.DOMAINID,GROUP_CONCAT( DISTINCT resourceId) AS item_scopes
from (
=CURRENT_DATE
UNION
select
t1.ITEM_TEAM_ID as DOMAINID,t2.ID as resourceId
from baibaodunFlow.tlk_event_team t1 join baibaodunFlow.tlk_event_project t2
on t1.ITEM_PROJECT_ID=t2.ID and t2.ITEM_PROJECT_START_TIME<=CURRENT_DATE and t2.ITEM_PROJECT_END_TIME>=CURRENT_DATE
join obpm2.tenant_organizations o on o.id=t1.ITEM_TEAM_ID and
SUBSTR(ifnull( o.institutional_code, '' ),1,4) in ('0201','0212','0214','0215','0216','0217','0218','0219','0221')
UNION
select
'hbTS3taB' as DOMAINID, k.item_project_id as resourceId
from baibaodunFlow.tlk_add_work_demand k where item_approval_status='审批通过'
]]>
) as x
where x.DOMAINID in
#{item}
GROUP BY DOMAINID
) as p1
ON DUPLICATE KEY UPDATE scope=item_scopes,last_updated_time=CURRENT_TIMESTAMP
delete from dispatch.rd_dispatch_data_scopes WHERE scope_type in (4) and id in
concat('4_',#{item})
insert into dispatch.rd_dispatch_data_scopes
(id,scope,scope_type,note,last_updated_time)
select
concat('4_',ITEM_PERSON_ID),item_scopes,4, '大型活动承办商的管理人员的权限--管理多个岗点-3',CURRENT_TIMESTAMP
from (
select
x2.ITEM_PERSON_ID,GROUP_CONCAT( DISTINCT ITEM_ATTENDANCE_ID) AS item_scopes
from (
SELECT
t2.ITEM_PERSON_ID,t1.ITEM_ATTENDANCE_ID
FROM baibaodunFlow.tlk_manage_post t1 join baibaodunflow.tlk_manager t2 on t1.ITEM_MANAGER_ID=t2.ID
UNION
SELECT
t1.ITEM_PERSON_ID,t1.ITEM_POST_ID as ITEM_ATTENDANCE_ID
FROM baibaodunFlow.tlk_event_post_manager t1
) as x2
where x2.ITEM_PERSON_ID in
#{item}
GROUP BY ITEM_PERSON_ID
) as p1
ON DUPLICATE KEY UPDATE scope=item_scopes,last_updated_time=CURRENT_TIMESTAMP
delete from `dispatch`.`rd_dispatch_data_source_summary`
WHERE id in
#{item}
INSERT INTO `dispatch`.`rd_dispatch_data_source_summary` (
`id`,
`name`,
`data_type`,
note,
`last_sync_time`
)
select
id,ITEM_TYPE_NAME,0,'人员来源',last_sync_time
from (
select
x.ID,x.ITEM_TYPE_NAME,CURRENT_TIMESTAMP as last_sync_time
from baibaodunflow.tlk_project_person_source x where x.id in
#{item}
) as p1
ON DUPLICATE KEY UPDATE
`name` = p1.ITEM_TYPE_NAME
delete from `dispatch`.`rd_work_stations`
WHERE reference_type=2
AND reference_number in
AND reference_line_id in
AND reference_point_id in
#{item}
;
INSERT INTO `dispatch`.`rd_work_stations` (
`lon_lat`,
`display_order`,
display_name,
`last_sync_time`,address,perform_range,reference_type,reference_number,reference_line_id,reference_point_id,supervise_depart_id,supervise_depart_name
)
select
lonlat,display_order,display_name,last_sync_time,address,perform_range,reference_type,reference_number,reference_line_id,reference_point_id,supervise_depart_id,supervise_depart_name
from (
SELECT
CONCAT(t.id,'-',r1.ID,'-',p.ID) as id,
CONCAT(t.ITEM_NAME,'-',p.ITEM_NAME) as display_name,
CONCAT("{""lat"":",ITEM_ATTENDANCESITELATITUDE,",""lon"":",ITEM_ATTENDANCESITELONGITUDE,"}")AS lonlat,
p.ITEM_LOCATIONCHECK as perform_range,0 as display_order,
p.ITEM_ATTENDANCESITEFULLADDRESS as address,
2 as reference_type,
current_timestamp as last_sync_time,
t.id as reference_number,
r1.id as reference_line_id,p.id as reference_point_id,
t.ITEM_SUPERVISEDEPID as supervise_depart_id,
(select name from dispatch.department_level l1 where l1.id=t.ITEM_SUPERVISEDEPID limit 1) as supervise_depart_name
FROM
duty.tlk_patrol_tasks t
JOIN duty.tlk_patrol_task_path_relations r1 ON t.id = r1.ITEM_TASKID
JOIN duty.tlk_patrol_path_point_relations r2 ON r1.ITEM_PATHID = r2.ITEM_PATHID
JOIN duty.tlk_patrol_points p ON r2.ITEM_POINTID = p.id
where
=CURRENT_DATE OR t.ITEM_ENDDATE is null)
]]>
AND t.id in
AND r1.id in
AND p.id in
AND r2.id in
#{item}
) as p1
ON DUPLICATE KEY UPDATE
`display_order` = p1.display_order,`display_name` = p1.display_name,`lon_lat` = p1.lonlat,
`perform_range` = p1.perform_range,
`reference_type` = p1.reference_type,`address` = p1.address,last_sync_time=p1.last_sync_time,
reference_number=p1.reference_number,reference_line_id=p1.reference_line_id,reference_point_id=p1.reference_point_id,
supervise_depart_id=p1.supervise_depart_id,supervise_depart_name=p1.supervise_depart_name
delete from `dispatch`.`rd_work_stations`
WHERE reference_type=1 and reference_number in
#{item}
INSERT INTO `dispatch`.`rd_work_stations` (
`lon_lat`,
`display_order`,
display_name,
`last_sync_time`,address,perform_range,reference_type,reference_number,reference_line_id,reference_point_id,supervise_depart_id,supervise_depart_name
)
select
lonlat,display_order,display_name,last_sync_time,address,perform_range,reference_type,reference_number,reference_line_id,reference_point_id,supervise_depart_id,supervise_depart_name
from (
select
id,CONCAT("{""lat"":",ITEM_attendanceSiteLatitude,",""lon"":",ITEM_attendanceSiteLongitude,"}")AS lonlat,
1 as display_order,ITEM_attendanceSiteName as display_name,current_timestamp as last_sync_time,
ITEM_attendanceSiteFullAddress as address,item_performRange as perform_range,1 as reference_type,id as reference_number,
'#1' as reference_line_id,'#1' as reference_point_id,ITEM_officePoliceAddressID as supervise_depart_id,ITEM_officePoliceAddress as supervise_depart_name
from baibaodunflow.tlk_attendance_site_base_info bi
where
=CURRENT_DATE
]]>
and bi.id in
#{item}
) as p1
ON DUPLICATE KEY UPDATE
`lon_lat` = p1.lonlat,`display_order` = p1.display_order,`display_name` = p1.display_name,`last_sync_time` = p1.last_sync_time,
`reference_type` = p1.reference_type,reference_number=p1.reference_number,
reference_line_id=p1.reference_line_id,reference_point_id=p1.reference_point_id,supervise_depart_id=p1.supervise_depart_id,supervise_depart_name=p1.supervise_depart_name
delete from `dispatch`.`rd_work_station_users`
WHERE reference_type = 2 and employee_id in
#{item}
INSERT INTO `dispatch`.`rd_work_station_users` (
`employee_id`,
`reference_number`,
`last_sync_time`,reference_type
)
select
employee_id,reference_number,last_sync_time,reference_type
from (
SELECT
r.ITEM_EMPLOYEEID as employee_id,
t.ID as reference_number,CURRENT_TIMESTAMP as last_sync_time,2 as reference_type
FROM
duty.tlk_patrol_task_employee_relations r
JOIN duty.tlk_patrol_tasks t ON r.ITEM_TASKID = t.id where r.ITEM_EMPLOYEEID in
#{item}
) as p1
ON DUPLICATE KEY UPDATE
`reference_number` = p1.reference_number,`last_sync_time` = p1.last_sync_time,
`employee_id` = p1.employee_id,reference_type=p1.reference_type
delete from `dispatch`.`rd_work_station_users`
WHERE reference_type=1 and employee_id in
#{item}
INSERT INTO `dispatch`.`rd_work_station_users` (
`employee_id`,
`reference_number`,
`last_sync_time`,reference_type
)
select
employee_id,reference_number,last_sync_time,reference_type
from (
SELECT
1 as reference_type,
t1.ID as reference_number,
current_timestamp as last_sync_time,
t2.ITEM_securityId as employee_id
FROM baibaodunflow.tlk_attendance_site_base_info t1
JOIN baibaodunflow.tlk_attendance_site_person_info t2 ON t1.ID = t2.ITEM_attendanceSiteId
=CURRENT_DATE
]]>
and t2.ITEM_securityId in
#{item}
) as p1
ON DUPLICATE KEY UPDATE
`reference_type` = p1.reference_type,`reference_number` = p1.reference_number,`last_sync_time` = p1.last_sync_time
delete from dispatch.rd_employee_summary where source_party=1 and id in
#{item}
INSERT INTO dispatch.rd_employee_summary (
source_party,
id,
supervise_region_code,
security_station_id,
security_station_name,
supervise_depart_id,
supervise_domain_id,
supervise_depart_name,
`name`,
head_photo,
contact,
company_name,
`status`,
checked_status,
authenticated_status,
military_status,
security_certificate_no,
occupation_type,
hired_date,
leave_time,
insure,
organization_id,
tenant_user_id,
tenant_employee_id,
last_sync_time,
last_updated_time,
device_number,
tenant_im_user_id,
idCard_no,
cid,
sex,responsible_of_station_id,institutional,industry,`position`,is_domain_admin,company_level,resource_type,third_party,
security_station_type
)
select
1,
p1.id,p1.supervise_region_code,p1.ITEM_attendanceSiteId,p1.ITEM_attendanceSiteName,
p1.ITEM_officePoliceAddressID,p1.supervise_domain_id,p1.ITEM_officePoliceAddress,p1.name,p1.head_photo,
p1.telephone,p1.orgName,p1.status,p1.checked_status,p1.authenticated_status,p1.military_status,ifnull(p1.securityCredentialNo, '#1' ) as securityCredentialNo,
p1.occupation_type,p1.hired_date,p1.leave_time, p1.insure, p1.organization_id, p1.tenant_user_id,p1.employeeId,
p1.last_sync_time,p1.last_updated_time,p1.device_number,p1.tenant_im_user_id,p1.number,p1.cid,p1.sex,p1.responsible_of_station_id,
p1.institutional_code,
p1.industry_code,p1.`position`,p1.is_domain_admin,p1.company_level,p1.resource_type,p1.third_party,p1.security_station_type
from (
SELECT
ee.id,
ifnull(SUBSTR(o.supervise_region_code,1,10),'#fk-0830') as supervise_region_code,
(case when o.industry_code='04' OR o.industry_code='05' OR o.industry_code='03' then ifnull(O.supervise_depart_id, '#1' ) else ifnull(k1.ID, '#1' ) end) as ITEM_attendanceSiteId,
(case when o.industry_code='04' OR o.industry_code='05' OR o.industry_code='03' then ifnull(O.supervise_depart_name, '#1' ) else ifnull(k1.ITEM_attendanceSiteName, '#1' ) end) as ITEM_attendanceSiteName,
(case when o.industry_code='04' OR o.industry_code='05' OR o.industry_code='03' then ifnull(O.supervise_depart_id, '#1') else ifnull(k1.ITEM_officePoliceAddressID, '#1' ) end) as ITEM_officePoliceAddressID,
'#fk-0830' as supervise_domain_id,
(case when o.industry_code='04' OR o.industry_code='05' OR o.industry_code='03' then ifnull(O.supervise_depart_name, '#1') else ifnull(k1.ITEM_officePoliceAddress, '#1' ) end) as ITEM_officePoliceAddress,
u.`name`,
c.head_photo,
SUBSTR(ifnull(u.telephone,''),1,28) as telephone,
o.`name` AS orgName,
ee.`status`,
u.checked_status,
u.authenticated_status,
u.military_status,
ifnull(
(SELECT SUBSTR(zsbh,1,38) FROM obpm2.tenant_user_credential_details
WHERE tenant_user_id=u.id and areaCode LIKE concat(SUBSTR(o.supervise_region_code,1,2),'%')
and active=1 and state=1 and certificateType='1' LIMIT 1),'#1') AS securityCredentialNo,
ee.occupation_type,
ee.leave_time AS leave_time,
ifnull( ee.insure, 0 ) as insure,
ee.organization_id,
ee.tenant_user_id,
(case
when o.industry_code in ('04') then
when o.industry_code in ('05','03') or
o.institutional_code in ('0106','0107','0109','0406','0407','0409','0406','0407','0409','0607','0608','0609') then '' else
=CURRENT_DATE)
]]>
end) as responsible_of_station_id,
ee.id as employeeId,
CURRENT_TIMESTAMP as last_sync_time,
ee.last_updated_time AS last_updated_time,
'' as device_number,
ifnull( u.im_identity, '#' ) as tenant_im_user_id,
c.number,
u.cid,
u.sex,
SUBSTR(ifnull( o.institutional_code, '' ),1,28) as institutional_code,
SUBSTR(ifnull( o.industry_code, '' ),1,28) as industry_code,
ee.`position`,ee.is_domain_admin,o.level as company_level,
ifnull(k1.resource_type,0) as resource_type,
(case when SUBSTR(ifnull( o.institutional_code, '' ),1,4)='0201'
then (select i1.ITEM_PERSON_SOURCE_ID from baibaodunflow.tlk_service_person i1
where i1.ITEM_PERSON_ID=k1.ITEM_securityId limit 1
) else ''
end) as third_party,
(case when IFNULL(k1.ITEM_attendanceSiteType,'')!='' then k1.ITEM_attendanceSiteType
else (case when INSTR(IFNULL(k1.ITEM_PROJECT_ID,''),'--__zNNa8cuUuYsO64cQ2u2') >0
then (case o.institutional_code
when '0219' then (select id from dispatch.rd_dynamic_station_types h1 where h1.name like '志愿者%' limit 1)
when '0222' then (select id from dispatch.rd_dynamic_station_types h1 where h1.name like '医%' limit 1)
when '0401' then (select id from dispatch.rd_dynamic_station_types h1 where h1.name like '医%' limit 1)
when '0223' then (select id from dispatch.rd_dynamic_station_types h1 where h1.name like '裁判%' limit 1)
when '0221' then (select id from dispatch.rd_dynamic_station_types h1 where h1.name like '无人机%' limit 1)
when '0102' then '999999'
else (select id from dispatch.rd_dynamic_station_types h1 where h1.name like '其他%' limit 1) end) else k1.ITEM_attendanceSiteType end)
end) as security_station_type
FROM obpm2.tenant_users u
LEFT JOIN obpm2.tenant_user_credentials c ON u.selected_credential_id = c.id
JOIN (select
0 as status,k.reference_number,k.reference_type,'' as position,k.id,(case when k.member_type=0 then 1 else 0 end) as is_domain_admin,
last_updated_time as last_updated_time,
k.reference_number as organization_id,
k.tenant_user_id,null as leave_time,
0 as occupation_type,k.created_time as hired_date,0 as insure
from obpm2.external_members k) ee ON ee.tenant_user_id = u.id
JOIN obpm2.tenant_organizations o ON o.id = ee.organization_id
LEFT JOIN (
=CURRENT_DATE
order by IFNULL(CAST(t1.ITEM_ATT_TYPE AS UNSIGNED), 0) asc
)
) ]]> as k1 on k1.ITEM_securityId=ee.id
where ee.status!=1 and ee.id in
#{item}
order by resource_type asc,ITEM_attendanceStartDate asc
) as p1
ON DUPLICATE KEY UPDATE
source_party=1,
supervise_region_code=p1.supervise_region_code,security_station_id=p1.ITEM_attendanceSiteId,
security_station_name=p1.ITEM_attendanceSiteName,supervise_depart_id= p1.ITEM_officePoliceAddressID,supervise_domain_id=p1.supervise_domain_id,
supervise_depart_name=p1.ITEM_officePoliceAddress,`name`=p1.name,head_photo=p1.head_photo,
contact=p1.telephone,company_name=p1.orgName,`status`=p1.status,
checked_status=p1.checked_status,authenticated_status=p1.authenticated_status,military_status=p1.military_status,
security_certificate_no=p1.securityCredentialNo,occupation_type=p1.occupation_type,hired_date=p1.hired_date,
leave_time=p1.leave_time,insure=p1.insure,last_updated_time=p1.last_updated_time,
device_number=p1.device_number,tenant_im_user_id=p1.tenant_im_user_id,idCard_no=p1.number,cid=p1.cid,sex=p1.sex,
responsible_of_station_id=p1.responsible_of_station_id,
institutional = p1.institutional_code,
industry = p1.industry_code,
third_party=p1.third_party,
`position` = p1.`position`,is_domain_admin=p1.is_domain_admin,company_level=p1.company_level,resource_type=p1.resource_type,
security_station_type = p1.security_station_type
delete from `dispatch`.`rd_station_devices`
WHERE id in
#{item}
INSERT INTO `dispatch`.`rd_station_devices` (
`id`,
`station_type`,
`station_id`,
third_party_id,
third_party_name,
`last_sync_time`,
last_updated_time
)
select
id,station_type,station_id,third_party_id,third_party_name,last_sync_time,last_sync_time
from (
select
x.ID,(case when x.ITEM_BAND_TYPE = 1 then 1 else 0 end) as station_type,
ITEM_NAME as third_party_name,
item_binded_user_attid as station_id,ITEM_DEVICE_NO as third_party_id,CURRENT_TIMESTAMP as last_sync_time
from dispatch.tlk_hardware x where x.ITEM_DEVICE_TYPE='4' and x.id in
#{item}
) as p1
ON DUPLICATE KEY UPDATE
`station_type`=p1.station_type,
`station_id`=p1.station_id,
third_party_id=p1.third_party_id,
third_party_name=p1.third_party_name,
`last_sync_time`=p1.last_sync_time,
last_updated_time=p1.last_sync_time