DROP?PROCEDURE??IF?EXISTS?pro_supplier_employee;
--?供應商關聯(lián)企業(yè)?員工以及常用旅客白名單??處理?
CREATE?PROCEDURE?pro_supplier_employee(?
????IN?companyId?VARCHAR(20000),?--?企業(yè)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;#錯誤定義，標記循環(huán)結束??
?SET?@companyId=companyId;
?SET?@supplierId=supplierId;?

???DROP?VIEW?IF?EXISTS?view_supplier_personlInfo;??
???DROP?VIEW?IF?EXISTS?view_supplier_employ;??

???????--?查找員工關聯(lián)的常用旅客的?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)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))");
	????--?查詢關聯(lián)企業(yè)的員工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)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;

??--?錯誤定義，標記循環(huán)結束?

??????SET?result?=?0;#只有定義為0，新的循環(huán)才能繼續(xù)。??
????/*?打開光標?*/??
????OPEN?Cur_1;??
?????????/*?循環(huán)執(zhí)行?*/??
?????????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，新的循環(huán)才能繼續(xù)。??
????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;