SELECT a.tenant_employee_id tenantEmployeeId ,a.company_name comName ,a.security_station_name securityStationName ,b.address securityStationAddress ,a.name empName ,e.name superviseDepartName ,(d.tolCount-d.outCount) signInCount ,d.outCount signOutCount ,d.alDay createdDate FROM ( SELECT count(1) tolCount,tenant_employee_id,reference_number security_station_id,DATE_FORMAT(created_time,'%Y-%m-%d') alDay,SUM(`STATUS`) outCount FROM tenant_employee_attendance_records WHERE DATE_FORMAT(created_time,'%Y-%m-%d') BETWEEN #{criteria.createdStarDate} AND #{criteria.createdEndDate} GROUP BY alDay,tenant_employee_id ) d,rd_employee_summary a,rd_security_station_summary b,departmentlevel e WHERE a.tenant_employee_id = d.tenant_employee_id AND b.id = d.security_station_id AND b.supervise_depart_id = e.id AND INSTR(#{criteria.departIds},b.supervise_depart_id) AND INSTR(a.company_name,#{criteria.comName}) AND INSTR(b.name,#{criteria.securityStationName}) AND INSTR(a.name,#{criteria.empName}) AND b.address = #{criteria.address} ORDER BY d.alDay DESC SELECT id ,tenant_user_id ,tenant_employee_id ,organization_id ,reference_number ,status ,note ,address ,reference_type ,lon_lat ,created_time FROM tenant_employee_attendance_records WHERE tenant_employee_id = #{tenantEmployeeId} and status in (0,1) AND DATE_FORMAT(created_time,'%Y-%m-%d') = #{createdDate} ORDER BY id DESC select * from tenant_employee_attendance_records where tenant_employee_id = #{tenantEmployeeId} order by id desc limit 1 select k.* from ( select r.tenant_employee_id, r.`name`, r.organization_id, r.company_name, r.idCard_no, r.project_name, r.extend_json_info ->>'$.projectCompanyName' as project_company_name, r.security_certificate_no as security_certificate_no, count(r.id) as countOfRecords, GROUP_CONCAT(r.`STATUS`) as statusContactValue from tenant_employee_attendance_records r and r.created_time >=#{createdStarDate} and r.name like concat(#{name},'%') and r.company_name like concat(#{companyName},'%') and r.security_station_name like concat(#{securityStationName},'%') and r.project_name like concat(#{projectName},'%') and r.extend_json_info ->>'$.projectCompanyName' like concat(#{projectCompanyName},'%') #{selectedDepartId} and r.idCard_no like concat(#{idCardNo},'%') and ( #{selOrgId} #{selectedDepartId} ) and r.idCard_no like concat(#{idCardNo},'%') and ( #{selOrgId} #{pjId} ) #{selectedDepartId} #{selectedStatus} and r.security_certificate_no !='#1' and r.security_certificate_no ='#1' 0 ]]> group by r.tenant_employee_id,r.organization_id ) as k order by countOfRecords desc limit #{skip},#{pageSize} select count(1) from ( select r.tenant_employee_id, r.`name`, r.company_name, r.idCard_no, count(r.id) as countOfRecords, GROUP_CONCAT(r.`STATUS`) as statusContactValue from tenant_employee_attendance_records r and r.created_time >=#{createdStarDate} and r.name like concat(#{name},'%') and r.company_name like concat(#{companyName},'%') and r.security_station_name like concat(#{securityStationName},'%') and r.project_name like concat(#{projectName},'%') and r.extend_json_info ->>'$.projectCompanyName' like concat(#{projectCompanyName},'%') #{selectedDepartId} and r.idCard_no like concat(#{idCardNo},'%') and ( #{selOrgId} #{pjId} ) #{selectedDepartId} and ( #{selOrgId} #{selectedDepartId} ) and r.idCard_no like concat(#{idCardNo},'%') #{selectedStatus} and r.security_certificate_no !='#1' and r.security_certificate_no ='#1' 0 ]]> group by r.tenant_employee_id,r.organization_id ) as k select r.tenant_employee_id, r.`name`, r.company_name, r.idCard_no, r.security_certificate_no, r.`STATUS` as `status`, r.created_time, r.address, r.organization_id, r.supervise_depart_id, r.reference_number, r.security_station_name, r.supervise_depart_name, r.security_station_address, r.project_id, r.project_name, r.extend_json_info, r.extend_json_info ->>'$.projectCompanyName' as project_company_name from tenant_employee_attendance_records r r.tenant_employee_id=#{employeeId} and status in (0,1) and r.created_time >=#{createdStarDate} and r.name like concat(#{name},'%') and r.company_name like concat(#{companyName},'%') and r.project_name like concat(#{projectName},'%') and r.extend_json_info ->>'$.projectCompanyName' like concat(#{projectCompanyName},'%') and r.security_station_name like concat(#{securityStationName},'%') #{selectedDepartId} and r.idCard_no like concat(#{idCardNo},'%') and ( #{selOrgId} #{selectedDepartId} ) and r.idCard_no like concat(#{idCardNo},'%') and ( #{selOrgId} #{pjId} ) #{selectedDepartId} #{selectedStatus} and r.security_certificate_no !='#1' and r.security_certificate_no ='#1' 0 ]]> order by r.tenant_employee_id asc, r.id desc limit #{skip},#{pageSize} select count(1) from ( select r.tenant_employee_id, r.`name`, r.company_name, r.idCard_no, r.`STATUS` as `status`, r.created_time, r.address, r.security_station_name, r.supervise_depart_name, r.security_station_address from tenant_employee_attendance_records r r.tenant_employee_id=#{employeeId} and status in (0,1) and r.created_time >=#{createdStarDate} and r.name like concat(#{name},'%') and r.company_name like concat(#{companyName},'%') and r.project_name like concat(#{projectName},'%') and r.extend_json_info ->>'$.projectCompanyName' like concat(#{projectCompanyName},'%') and r.security_station_name like concat(#{securityStationName},'%') #{selectedDepartId} and r.idCard_no like concat(#{idCardNo},'%') and ( #{selOrgId} #{selectedDepartId} ) and r.idCard_no like concat(#{idCardNo},'%') and ( #{selOrgId} #{pjId} ) #{selectedDepartId} #{selectedStatus} and r.security_certificate_no !='#1' and r.security_certificate_no ='#1' 0 ]]> ) as k INSERT INTO `dispatch`.`tenant_employee_attendance_records`(`id`, `STATUS`, `reference_type`, `reference_number`, `tenant_employee_id`, `tenant_user_id`, `organization_id`, `lon_lat`, `created_time`, `note`, `address`, `name`, `idCard_no`, `company_name`, `security_station_name`, `security_station_address`, `supervise_depart_id`, `supervise_depart_name`, `security_certificate_no`,`calculated_resource_type`,`project_id`, `project_name`,header_agent,attachments,extend_json_info) VALUES (#{item.id}, #{item.recordStatus,typeHandler=org.apache.ibatis.type.EnumOrdinalTypeHandler}, #{item.referenceType,typeHandler=org.apache.ibatis.type.EnumOrdinalTypeHandler},#{item.referenceNumber}, #{item.tenantEmployeeId},#{item.tenantUserId}, #{item.organizationId}, #{item.lonLat,typeHandler=com.bcxin.tenant.open.domains.typeHandlers.GeoPointTypeHandler},#{item.createdTime},#{item.note},#{item.address},#{item.name},#{item.idCardNo}, #{item.companyName},#{item.securityStationName},#{item.securityStationAddress},#{item.superviseDepartId},#{item.superviseDepartName}, #{item.securityCertificateNo},#{item.calculatedResourceType},(case when ifnull(#{item.projectId},'')='' then '#1' else rs.project_id end) as project_id ,#{item.projectName},SUBSTR(#{item.headerAgent},1,500), #{item.attachments},#{item.extendJsonInfo}) ON DUPLICATE KEY UPDATE `duplicated_count`=duplicated_count+1,duplicated_time=CURRENT_TIMESTAMP, header_agent=SUBSTR(#{item.headerAgent},1,500); INSERT INTO `dispatch`.`tenant_employee_recently_attendances`(`id`, `STATUS`, `tenant_employee_id`, `created_time`, `address`, `name`,`calculated_resource_type`,date_value,attachments,extend_json_info) (#{item.id}, #{item.recordStatus,typeHandler=org.apache.ibatis.type.EnumOrdinalTypeHandler}, #{item.tenantEmployeeId},#{item.createdTime},#{item.address},#{item.name},#{item.calculatedResourceType}, DATE_FORMAT(CURDATE(), '%Y%m%d'),#{item.attachments},#{item.extendJsonInfo}) ON DUPLICATE KEY UPDATE `created_time`=#{item.createdTime}; INSERT INTO `dispatch`.`tenant_employee_attendance_records` (`id`, `STATUS`, `reference_type`, `reference_number`, `tenant_employee_id`, `tenant_user_id`, `organization_id`, `lon_lat`, `created_time`, `note`, `address`, `name`, `idCard_no`, `company_name`, `security_station_name`, `security_station_address`, `supervise_depart_id`, `supervise_depart_name`, `security_certificate_no`,`calculated_resource_type`,`project_id`, `project_name`,duplicated_time,header_agent,attachments,extend_json_info) select #{item.id}, #{item.recordStatus,typeHandler=org.apache.ibatis.type.EnumOrdinalTypeHandler}, #{item.referenceType,typeHandler=org.apache.ibatis.type.EnumOrdinalTypeHandler},#{item.referenceNumber}, #{item.tenantEmployeeId},#{item.tenantUserId}, #{item.organizationId}, #{item.lonLat,typeHandler=com.bcxin.tenant.open.domains.typeHandlers.GeoPointTypeHandler},#{item.createdTime},#{item.note},#{item.address},#{item.name},#{item.idCardNo}, #{item.companyName},rs.name,rs.address,rs.supervise_depart_id,rs.supervise_depart_name, #{item.securityCertificateNo},#{item.calculatedResourceType},(case when ifnull(rs.project_id,'')='' then '#1' else rs.project_id end) as project_id,rs.project_name,CURRENT_TIMESTAMP, SUBSTR(#{item.headerAgent},1,499),#{item.attachments},(case when #{item.extendJsonInfo}!='' then #{item.extendJsonInfo} else (case when ifnull(rs.project_id,'')='' then '#1' else (select concat('{"projectCompanyName":','"',p.company_name,'"','}') from dispatch.rd_project_summary p where p.id=rs.project_id limit 1) end) end) from `dispatch`.rd_security_station_summary rs where rs.id=#{item.referenceNumber} ON DUPLICATE KEY UPDATE `duplicated_count`=duplicated_count+1,duplicated_time=CURRENT_TIMESTAMP,header_agent=SUBSTR(#{item.headerAgent},1,500); INSERT INTO `dispatch`.`tenant_employee_recently_attendances` (`id`, `STATUS`, `tenant_employee_id`, `created_time`, `address`, `name`,`calculated_resource_type`,date_value,attachments,extend_json_info) select #{item.id}, #{item.recordStatus,typeHandler=org.apache.ibatis.type.EnumOrdinalTypeHandler}, #{item.tenantEmployeeId}, #{item.createdTime},#{item.address},#{item.name},#{item.calculatedResourceType},DATE_FORMAT(CURDATE(), '%Y%m%d'),#{item.attachments},#{item.extendJsonInfo} ON DUPLICATE KEY UPDATE `created_time`=#{item.createdTime}; INSERT INTO `dispatch`.`tenant_employee_attendance_records` (`id`, `STATUS`, `reference_type`, `reference_number`, `tenant_employee_id`, `tenant_user_id`, `organization_id`, `lon_lat`, `created_time`, `note`, `address`, `name`, `idCard_no`, `company_name`, `security_station_name`, `security_station_address`, `supervise_depart_id`, `supervise_depart_name`, `security_certificate_no`,`calculated_resource_type`,`project_id`, `project_name`,duplicated_time,header_agent,attachments,reference_number_2,reference_number_3,extend_json_info) select #{item.id}, #{item.recordStatus,typeHandler=org.apache.ibatis.type.EnumOrdinalTypeHandler}, #{item.referenceType,typeHandler=org.apache.ibatis.type.EnumOrdinalTypeHandler}, rs.reference_number, #{item.tenantEmployeeId},#{item.tenantUserId}, #{item.organizationId}, #{item.lonLat,typeHandler=com.bcxin.tenant.open.domains.typeHandlers.GeoPointTypeHandler},#{item.createdTime},#{item.note},#{item.address},#{item.name},#{item.idCardNo}, #{item.companyName},rs.display_name,'#1' as address,rs.supervise_depart_id,rs.supervise_depart_name as supervise_depart_name, #{item.securityCertificateNo},#{item.calculatedResourceType},('#1') as project_id,'#1' as project_name,CURRENT_TIMESTAMP, SUBSTR(#{item.headerAgent},1,499),#{item.attachments},reference_line_id,reference_point_id,#{item.extendJsonInfo} from `dispatch`.rd_work_stations rs where rs.id=#{item.referenceNumber} ON DUPLICATE KEY UPDATE `duplicated_count`=duplicated_count+1,duplicated_time=CURRENT_TIMESTAMP,header_agent=SUBSTR(#{item.headerAgent},1,500); INSERT INTO `dispatch`.`tenant_employee_recently_attendances` (`id`, `STATUS`, `tenant_employee_id`, `created_time`, `address`, `name`,`calculated_resource_type`,date_value) select #{item.id}, #{item.recordStatus,typeHandler=org.apache.ibatis.type.EnumOrdinalTypeHandler}, #{item.tenantEmployeeId}, #{item.createdTime},#{item.address},#{item.name},#{item.calculatedResourceType},DATE_FORMAT(CURDATE(), '%Y%m%d') ON DUPLICATE KEY UPDATE `created_time`=#{item.createdTime}; select * from tenant_employee_recently_attendances k where tenant_employee_id=#{employeeId} and date_value=#{dateValue} order by id desc limit #{skip},#{pageSize} select count(1) from tenant_employee_recently_attendances k where tenant_employee_id=#{employeeId} and date_value=#{dateValue} select created_time,`STATUS`,name,k.address from tenant_employee_recently_attendances k where tenant_employee_id=#{employeeId} and date_value=#{dateValue} order by id desc limit #{skip},#{pageSize} delete from tenant_employee_recently_attendances where limit 100