56,675
社区成员
发帖
与我相关
我的任务
分享
{"message":"\n### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '2-SOETERBROEK/ALEXANDER JO-NY83HJ934' for key 'supplierId'\n### The error may involve com.ziztour.common.db.mapper.p.SupplierInfoMapper.procSupplierEmployee-Inline\n### The error occurred while setting parameters\n### SQL: {call pro_supplier_employee( ?, ?, ? )}\n### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '2-SOETERBROEK/ALEXANDER JO-NY83HJ934' for key 'supplierId'\n; SQL []; Duplicate entry '2-SOETERBROEK/ALEXANDER JO-NY83HJ934' for key 'supplierId'; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '2-SOETERBROEK/ALEXANDER JO-NY83HJ934' for key 'supplierId'","statusCode":700,"success":false}
DROP PROCEDURE IF EXISTS pro_supplier_employee;
-- 供应商关联企业 员工以及常用旅客白名单 处理
CREATE PROCEDURE pro_supplier_employee(
IN companyId VARCHAR(20000), -- 企业ID
IN supplierId BIGINT,-- 供应商ID
OUT result INT -- 返回结果
)
BEGIN
DECLARE spid bigint DEFAULT supplierId;
DECLARE cid bigint;
DECLARE eid bigint;
DECLARE ph varchar(50);
DECLARE idt int;
DECLARE bday date;
DECLARE sx int;
DECLARE iname varchar(50);
DECLARE icard varchar(50);
DECLARE Cur_1 CURSOR FOR SELECT a.cid,a.eid,a.ph,a.idt,a.bday,a.sx,a.iname,a.icard from view_supplier_personlInfo a group by a.iname,a.icard;
DECLARE Cur_2 CURSOR FOR SELECT a.cid,a.eid,a.ph,a.idt,a.bday,a.sx,a.iname,a.icard from view_supplier_employ a group by a.iname,a.icard;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET result = 1;#错误定义,标记循环结束
SET @companyId=companyId;
SET @supplierId=supplierId;
DROP VIEW IF EXISTS view_supplier_personlInfo;
DROP VIEW IF EXISTS view_supplier_employ;
-- 查找员工关联的常用旅客的 idCard (非同事关系)
SET @sel3=" create view view_supplier_personlInfo as SELECT companyId as cid,empId as eid,phone as ph,idType as idt,birthday as bday,sex as sx,TRIM(case when LENGTH(name)>0 then name when (name is null or LENGTH(name)<1 ) then CONCAT(case when lastName is null then '' else lastName end ,' ',case when firstName is null then '' else firstName end ) end) as iname ,idcard as icard from b_company_employee_personlInfo where empId in (";
SET @sel3=CONCAT(@sel3,"select id from b_company_employee where FIND_IN_SET(companyId,'",@companyId,"') and state =1 ) and isColleague=0 and state=1 ");
SET @sel3=CONCAT(@sel3," and (( length(name)>0 and idcard is not null and idtype is not null) or (length(lastName)>0 and idcard is not null and idtype is not null) or ( length(firstName)>0 and idcard is not null and idtype is not null))");
-- 查询关联企业的员工ID (插入白名单使用)
SET @esql2=" create view view_supplier_employ as select id as eid,companyId as cid,phone as ph,idType as idt,birthday as bday,sex as sx,TRIM(case when LENGTH(name)>0 then name when (name is null or LENGTH(name)<1 ) then CONCAT(case when lastName is null then '' else lastName end ,' ',case when firstName is null then '' else firstName end ) end) as iname ,idcard as icard from b_company_employee where FIND_IN_SET(companyId,'";
set @esql2=CONCAT(@esql2,@companyId,"') and state =1 and (( length(name)>0 and idcard is not null and idtype is not null) or (length(lastName)>0 and idcard is not null and idtype is not null) or ( length(firstName)>0 and idcard is not null and idtype is not null))");
PREPARE stmt_sel3 FROM @sel3;
EXECUTE stmt_sel3;
DEALLOCATE PREPARE stmt_sel3;
PREPARE stmt_esql2 FROM @esql2;
EXECUTE stmt_esql2;
DEALLOCATE PREPARE stmt_esql2;
-- 错误定义,标记循环结束
SET result = 0;#只有定义为0,新的循环才能继续。
/* 打开光标 */
OPEN Cur_1;
/* 循环执行 */
REPEAT
FETCH Cur_1 INTO cid,eid,ph,idt,bday,sx,iname,icard;
set @ct=0;
select count(1) into @ct from supplier_company_employ_relation sc where sc.name=iname and sc.idCard=icard and sc.supplierId=spid;
IF NOT result THEN
IF @ct<1 THEN
if cid is null then
insert INTO supplier_company_employ_relation (supplierId,companyId,employeeId,isRecord,phone,name,idType,idCard,birthday,sex,createTime,state) values (spid,0,eid,0,ph,iname,idt,icard,bday,sx,now(),1);
ELSE
insert INTO supplier_company_employ_relation (supplierId,companyId,employeeId,isRecord,phone,name,idType,idCard,birthday,sex,createTime,state) values (spid,cid,eid,0,ph,iname,idt,icard,bday,sx,now(),1);
end if;
end if;
END IF;
UNTIL result END REPEAT; #result=1时退出被循
/*关闭光标*/
CLOSE Cur_1;
SET result = 0;#只有定义为0,新的循环才能继续。
OPEN Cur_2;
REPEAT
FETCH Cur_2 INTO cid,eid,ph,idt,bday,sx,iname,icard;
IF NOT result THEN
set @ct1=0;
select count(1) into @ct1 from supplier_company_employ_relation sc where sc.name=iname and sc.idCard=icard and sc.supplierId=spid;
if @ct1 <1 then
insert INTO supplier_company_employ_relation(supplierId,companyId,employeeId,isRecord,phone,name,idType,idCard,birthday,sex,createTime,state) values (spid,cid,eid,0,ph,iname,idt,icard,bday,sx,now(),1);
end if;
END IF;
UNTIL result END REPEAT;
CLOSE Cur_2;
DROP VIEW IF EXISTS view_supplier_personlInfo;
DROP VIEW IF EXISTS view_supplier_employ;
SET result = 1;
-- select @companyId;
END;