BEGIN
DECLARE done INT;
DECLARE v_companyName VARCHAR(100);
DECLARE v_phone VARCHAR(30);
DECLARE v_contactName VARCHAR(30);
DECLARE v_companyAddress VARCHAR(256);
DECLARE v_mail VARCHAR(50);
DECLARE v_fixphone VARCHAR(30);
DECLARE v_id INT;
DECLARE isInsert INT DEFAULT 0;
DECLARE isRepeat INT DEFAULT 0;
DECLARE isSuccessFlag INT DEFAULT 0;
DECLARE curCount INT DEFAULT 0;
DECLARE v_masterUserid int DEFAULT 10005;
-- 定义游标
DECLARE rs_cursor CURSOR FOR
SELECT id, trim(companyName), trim(phone), trim(contactName), trim(companyAddress), SUBSTRING_INDEX(trim(companyMailbox),'---','1') as mail,trim(fixedPhone) from crawl_data where status = 0 order by companyName;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN rs_cursor;
cursor_loop:LOOP
FETCH rs_cursor INTO v_id,v_companyName,v_phone, v_contactName,v_companyAddress,v_mail,v_fixphone; -- 取数据
IF done=1 THEN
leave cursor_loop;
END IF;
-- 逻辑处理
-- 更新表
set isInsert = 0;
set isRepeat = 0;
set v_masterUserid = 10005;
if (v_companyName is not null and v_phone is not null) THEN -- 公司名和手机存在时
-- 判断数据库里是否重复
if (select count(1) from intendeduser_market m where m.companyName = v_companyName and (m.mobile = v_phone or m.mobileTwo = v_phone)) <= 0 THEN
-- 新增操作
set isInsert = 1;
else
set isRepeat = 1;
end if;
ELSEif (v_companyName is not null and v_fixphone is not null) THEN -- 公司和联系电话存在时
if(select count(1) from intendeduser_market m where m.companyName = v_companyName and m.tel = v_fixphone) <= 0 THEN
set isInsert = 1;
else
set isRepeat = 1;
end if;
end IF;
if isInsert=1 then
if (LOCATE('北京', v_companyName) > 0 or LOCATE('天津', v_companyName) > 0 or LOCATE('河北', v_companyName) > 0
or LOCATE('石家庄', v_companyName) > 0 or LOCATE('黑龙江', v_companyName) > 0 or LOCATE('吉林', v_companyName) > 0 or LOCATE('辽宁', v_companyName) > 0 or LOCATE('山西', v_companyName) > 0) THEN -- 公司名匹配城市 为北京 天津 河北 石家庄 黑龙家 吉林 辽宁 山西 等
set v_masterUserid = 10245;
end if;
insert into intendeduser_market (companyName,mobile,username,companyAddress,email,createsysUserId,tel,servicelog,servicelogTime,createtime,modifytime, marketUserId,remark)
values (v_companyName, v_phone, v_contactName,v_companyAddress, v_mail, 10071, v_fixphone, '系统导入', NOW(), NOW(),NOW(), v_masterUserid,'猫头鹰爬取');
end if;
set isSuccessFlag = 1;
if isRepeat = 1 THEN
set isSuccessFlag = 2;
end if;
update crawl_data c set c.status = isSuccessFlag where c.id = v_id;
END LOOP;
CLOSE rs_cursor;
END