1 BEGIN   2   3 drop TEMPORARY TABLE if EXISTS `tmp_fcl_import`;  4 -- 创建导入处理临时表  5 CREATE TEMPORARY TABLE if not EXISTS `tmp_fcl_import` (  6 `id` bigint(20) primary key AUTO_INCREMENT COMMENT '标识',  7 `error_ID` int(11) COMMENT '与error表id一一对应',  8 `PORT_START` varchar(100) DEFAULT NULL COMMENT '起始港',  9 `PORT_END` varchar(100) DEFAULT NULL COMMENT '目的港', 10 `SHIPPING` varchar(100) DEFAULT NULL COMMENT '船公司', 11 `WHARF` varchar(100) DEFAULT NULL COMMENT '港区', 12 `SCHEDULE` varchar(100) DEFAULT NULL COMMENT '班期', 13 `TRANSPORT` varchar(100) DEFAULT NULL COMMENT '中转港', 14 `ROUTE_CODE` varchar(200) DEFAULT NULL COMMENT '航线CODE', 15 `PORT_END_WHARF` varchar(100) DEFAULT NULL COMMENT '目的港挂靠', 16 `ROUTE` varchar(100) DEFAULT NULL COMMENT '航线', 17 `CUTOFF_DAY` varchar(100) DEFAULT NULL COMMENT '截关日', 18 `VOYAGE` varchar(100) DEFAULT NULL COMMENT '航程', 19 `SPACE_NUM` varchar(100) DEFAULT NULL COMMENT '舱位数量', 20 `SPACE_STATUS` varchar(100) DEFAULT NULL COMMENT '舱位状态', 21 `S_NO` varchar(100) DEFAULT NULL COMMENT '约号', 22 `CALL_FLAG` varchar(100) DEFAULT NULL COMMENT '是否电询', 23 `ONSALE_FLAG` varchar(100) DEFAULT NULL COMMENT '是否特价 ', 24 `PRICE_20` varchar(100) DEFAULT NULL COMMENT '20（底）', 25 `PRICE_40` varchar(100) DEFAULT NULL COMMENT '40（底）', 26 `PRICE_40HQ` varchar(100) DEFAULT NULL COMMENT '40HQ（底）', 27 `PRICE_45HQ` varchar(100) DEFAULT NULL COMMENT '45HQ（底）', 28 `BEGIN_DATE` varchar(100) DEFAULT NULL COMMENT '起始日期', 29 `END_DATE` varchar(100) DEFAULT NULL COMMENT '截止日期', 30 `REMARK_OUT` varchar(1000) DEFAULT NULL COMMENT '外部备注', 31 `REMARK_IN` varchar(1000) DEFAULT NULL COMMENT '内部备注', 32 `SPACE_MEMBER` varchar(100) DEFAULT NULL COMMENT '共舱方', 33 `DESC_WEIGHT` varchar(600) DEFAULT NULL COMMENT '限重描述', 34 `BILL_TYPE` varchar(100) DEFAULT NULL COMMENT '提单类型', 35 `ERROR_MESSAGE` varchar(1000) DEFAULT '' COMMENT '导入失败原因', 36 `ERROR_CODE` varchar(1000) DEFAULT NULL COMMENT '错误代号', 37 `ERROR_ROW` varchar(100) DEFAULT NULL COMMENT '导入错误的行', 38 `COMPANY_ID` int(11) DEFAULT NULL COMMENT '创建人公司ID', 39 `CREATE_ID` int(11) DEFAULT NULL COMMENT '创建人ID', 40 `CREATE_TIME` datetime DEFAULT NULL COMMENT '创建时间', 41 `UPDATE_ID` int(11) DEFAULT NULL COMMENT '修改人ID', 42 `UPDATE_TIME` datetime DEFAULT NULL COMMENT '修改时间', 43 `REMOVE` int(2) DEFAULT 0 COMMENT '删除状态 1删除 0有效 ', 44 -- 补充字段 45 PORT_START_ID int (11) default null, 46 PORT_END_ID int (11) default null, 47 SHIPPING_ID int (11) default null, 48 WHARF_ID int (11) default null, 49 TRANSPORT_ID int (11) default null, 50 ROUTE_ID int (11) default null, 51 SIGN varchar(36) default null,     52 status int (2) default 1, 53 KEY `INDEX_PORT_START` (`PORT_START`) USING BTREE, 54 KEY `INDEX_PORT_END` (`PORT_END`) USING BTREE, 55 KEY `INDEX_SHIPPING` (`SHIPPING`) USING BTREE, 56 KEY `INDEX_WHARF` (`WHARF`) USING BTREE, 57 KEY `INDEX_TRANSPORT` (`TRANSPORT`) USING BTREE, 58 KEY `INDEX_ROUTE` (`ROUTE`) USING BTREE, 59 KEY `INDEX_PORT_START_ID` (`PORT_START_ID`) USING BTREE, 60 KEY `INDEX_PORT_END_ID` (`PORT_END_ID`) USING BTREE, 61 KEY `INDEX_SHIPPING_ID` (`SHIPPING_ID`) USING BTREE, 62 KEY `INDEX_WHARF_ID` (`WHARF_ID`) USING BTREE, 63 KEY `INDEX_TRANSPORT_ID` (`TRANSPORT_ID`) USING BTREE, 64 KEY `INDEX_ROUTE_ID` (`ROUTE_ID`) USING BTREE, 65 KEY `INDEX_status` (`status`) USING BTREE, 66 KEY `INDEX_SIGN` (`SIGN`) USING BTREE 67 )ENGINE=MyISAM DEFAULT CHARSET=utf8; 68  69 TRUNCATE table tmp_fcl_import; 70  71 insert into tmp_fcl_import( 72 `error_ID`, 73 `PORT_START`, 74 `PORT_END`, 75 `SHIPPING`, 76 `WHARF`, 77 `SCHEDULE`, 78 `TRANSPORT`, 79 `ROUTE_CODE`, 80 `PORT_END_WHARF`, 81 `ROUTE`, 82 `CUTOFF_DAY`, 83 `VOYAGE`, 84 `SPACE_NUM`, 85 `SPACE_STATUS`, 86 `S_NO`, 87 `CALL_FLAG`, 88 `ONSALE_FLAG`, 89 `PRICE_20`, 90 `PRICE_40`, 91 `PRICE_40HQ`, 92 `PRICE_45HQ`, 93 `BEGIN_DATE`, 94 `END_DATE`, 95 `REMARK_OUT`, 96 `REMARK_IN`, 97 `SPACE_MEMBER`, 98 `DESC_WEIGHT`, 99 `BILL_TYPE`,100 `ERROR_CODE`,101 `ERROR_ROW`,102 `COMPANY_ID`,103 `CREATE_ID`,104 `CREATE_TIME`,105 `UPDATE_ID`,106 `UPDATE_TIME`,107 `REMOVE`108 ) select 109 `ID`,110 `PORT_START`,111 `PORT_END`,112 `SHIPPING`,113 `WHARF`,114 `SCHEDULE`,115 `TRANSPORT`,116 `ROUTE_CODE`,117 `PORT_END_WHARF`,118 `ROUTE`,119 `CUTOFF_DAY`,120 `VOYAGE`,121 `SPACE_NUM`,122 case when `SPACE_STATUS`='爆仓' then 2 else 1 end ,123 `S_NO`,124 case when `CALL_FLAG`='电询' then 1 when `CALL_FLAG`='是' then 1 else 0 end,125 case when `ONSALE_FLAG`='特价' then 1 when `ONSALE_FLAG`='是' then 1 else 0 end,126 `PRICE_20`,127 `PRICE_40`,128 `PRICE_40HQ`,129 `PRICE_45HQ`,130 `BEGIN_DATE`,131 `END_DATE`,132 `REMARK_OUT`,133 `REMARK_IN`,134 `SPACE_MEMBER`,135 `DESC_WEIGHT`,136 case when `BILL_TYPE`='代理单' then 2 else 1 end,137 `ERROR_CODE`,138 `ERROR_ROW`,139 `COMPANY_ID`,140 `CREATE_ID`,141 `CREATE_TIME`,142 `UPDATE_ID`,143 `UPDATE_TIME`,144 `REMOVE`145 from t_freight_fcl_error WHERE company_id=D_companyID;146 -- 验证前数据准备:147 -- 将匹配的id值放到对应的位置148 update tmp_fcl_import a JOIN b_port b on (a.PORT_START = b.NAME_CN or a.PORT_START = b.NAME_EN) and b.`REMOVE` = 0149 set a.PORT_START_ID = b.id;150 update tmp_fcl_import a JOIN b_port_alias b on (a.PORT_START = b.ALIAS) and a.PORT_START_ID is null151 set a.PORT_START_ID = b.PORT_ID;152 153 update tmp_fcl_import a JOIN b_port b on (a.PORT_END = b.NAME_CN or a.PORT_END = b.NAME_EN) and b.`REMOVE` = 0154 set a.PORT_END_ID = b.id,a.ROUTE_ID=b.ROUTE_ID;155 update tmp_fcl_import a JOIN b_port_alias b on (a.PORT_END = b.ALIAS) and a.PORT_END_ID is null156 set a.PORT_END_ID = b.PORT_ID,a.ROUTE_ID=(select ROUTE_ID from b_port where id=b.PORT_ID);157 158 update tmp_fcl_import a JOIN b_port b on (a.TRANSPORT = b.NAME_CN or a.TRANSPORT = b.NAME_EN) and b.`REMOVE` = 0159 set a.TRANSPORT_ID = b.id;160 update tmp_fcl_import a JOIN b_port_alias b on (a.TRANSPORT = b.ALIAS) and a.TRANSPORT_ID is null161 set a.TRANSPORT_ID = b.PORT_ID;162 163 update tmp_fcl_import a JOIN b_shipping b on (a.SHIPPING = b.NAME_CN or a.SHIPPING = b.NAME_EN or a.SHIPPING = b.code) and b.`REMOVE` = 0164 set a.SHIPPING_ID = b.id;165 166 update tmp_fcl_import a JOIN b_wharf b on (a.WHARF = b.NAME_CN or a.WHARF = b.NAME_EN) and b.`REMOVE` = 0167 set a.WHARF_ID = b.id;168 169 -- 将权限不足的id值的记录，对应的ERROR_message置为“无此港口权限”,status置为0170 -- 只有起始港、航线、船公司要考虑权限171 case when D_USER_ID is not null172 then173 -- 起始港174 set @temp_portid=null;175 SELECT port_id into @temp_portid FROM sys_user_port WHERE USER_ID = D_USER_ID LIMIT 1;176 case when @temp_portid is not null177 then178 update tmp_fcl_import set status=0,error_message=concat(error_message,',起运港权限不足') where PORT_START_ID not in (select port_id FROM sys_user_port WHERE USER_ID = D_USER_ID); 179 else180 set @i=@i;181 end case;182 183 -- 航线184 set @temp_route_id=null;185 SELECT ROUTE_ID into @temp_route_id FROM sys_user_route WHERE USER_ID = D_USER_ID LIMIT 1;186 case when @temp_route_id is not null187 then188 update tmp_fcl_import set status=0,error_message=concat(error_message,',航线权限不足') where ROUTE_ID not in (SELECT ROUTE_ID FROM sys_user_route WHERE USER_ID = D_USER_ID); 189 else190 set @i=@i;191 end case;192 193 -- 船公司194 set @temp_SHIPPING_id=null;195 SELECT SHIPPING_ID into @temp_SHIPPING_id FROM sys_user_shipping WHERE USER_ID = D_USER_ID LIMIT 1;196 case when @temp_SHIPPING_id is not null197 then198 update tmp_fcl_import set status=0,error_message=concat(error_message,',船公司权限不足') where SHIPPING_ID not in (SELECT SHIPPING_ID FROM sys_user_shipping WHERE USER_ID = D_USER_ID); 199 else200 set @i=@i;201 end case;202 else203 set @i=@i;204 end case;205 206 207 208 209 -- 开始验证210 -- 1.PORT_START验证:(必填，可匹配id)：211 update tmp_fcl_import set status=0,ERROR_MESSAGE=concat(ERROR_MESSAGE,',起运港不能为空')212 where PORT_START is null or PORT_START = '';213 update tmp_fcl_import set status=0,ERROR_MESSAGE=concat(ERROR_MESSAGE,',起运港无法匹配')214 where PORT_START is not null and PORT_START_ID is null;215 216 -- 2.WHARF验证:(必填，可匹配id)：217 update tmp_fcl_import set status=0,ERROR_MESSAGE=concat(ERROR_MESSAGE,',港区不能为空')218 where WHARF is null or WHARF = '';219 update tmp_fcl_import set status=0,ERROR_MESSAGE=concat(ERROR_MESSAGE,',港区无法匹配')220 where WHARF is not null and WHARF_ID is null;221 222 223 -- 3.PORT_END验证:(必填，可匹配id)：224 update tmp_fcl_import set status=0,ERROR_MESSAGE=concat(ERROR_MESSAGE,',目的港不能为空')225 where PORT_END is null or PORT_END = '';226 update tmp_fcl_import set status=0,ERROR_MESSAGE=concat(ERROR_MESSAGE,',目的港无法匹配')227 where PORT_END is not null and PORT_END_ID is null;228 229 -- 4.PORT_END_WHARF验证:(非必填，长度60)：230 update tmp_fcl_import set status=0,ERROR_MESSAGE=concat(ERROR_MESSAGE,',目的港挂靠长度过长')231 where CHAR_LENGTH(PORT_END_WHARF)>60;232 233 -- 5.SHIPPING验证:(必填，可匹配id)：    234 update tmp_fcl_import set status=0,ERROR_MESSAGE=concat(ERROR_MESSAGE,',船公司不能为空')235 where SHIPPING is null or SHIPPING = '';236 update tmp_fcl_import set status=0,ERROR_MESSAGE=concat(ERROR_MESSAGE,',船公司无法匹配')237 where SHIPPING is not null and SHIPPING_ID is null;238 239 -- 6.SCHEDULE验证:(必填，长度30)：240 update tmp_fcl_import set status=0,ERROR_MESSAGE=concat(ERROR_MESSAGE,',班期不能为空')241 where SCHEDULE is null or SCHEDULE = '';242 243 update tmp_fcl_import set status=0,ERROR_MESSAGE=concat(ERROR_MESSAGE,',班期长度过长')244 where CHAR_LENGTH(SCHEDULE)>30;245 246 -- 7.VOYAGE验证:(非必填，int类型)：    247 update tmp_fcl_import set status=0,ERROR_MESSAGE=concat(ERROR_MESSAGE,',航程不是整数')248 where 0=(VOYAGE REGEXP '^[0-9]{0,10}$');249 250 -- 8.PRICE_20验证:(非必填，int类型)：    251 update tmp_fcl_import set status=0,ERROR_MESSAGE=concat(ERROR_MESSAGE,',20GP不是整数')252 where 0=(PRICE_20 REGEXP '^[\-]{0,1}[0-9]{0,10}$');253 254 -- 9.PRICE_40验证:(非必填，int类型)：    255 update tmp_fcl_import set status=0,ERROR_MESSAGE=concat(ERROR_MESSAGE,',40GP不是整数')256 where 0=(PRICE_40 REGEXP '^[\-]{0,1}[0-9]{0,10}$');257 -- 10.PRICE_40HQ验证:(非必填，int类型)：    258 update tmp_fcl_import set status=0,ERROR_MESSAGE=concat(ERROR_MESSAGE,',40HQ不是整数')259 where 0=(PRICE_40HQ REGEXP '^[\-]{0,1}[0-9]{0,10}$');260 261 262 -- 11.TRANSPORT 验证:(非必填，可匹配id)：263 update tmp_fcl_import set status=0,ERROR_MESSAGE=concat(ERROR_MESSAGE,',中转港无法匹配')264 where TRANSPORT is not null and TRANSPORT_ID is null;265 266 -- 12.BEGIN_DATE 验证:(必填，日期格式)：267 update tmp_fcl_import set status=0,ERROR_MESSAGE=concat(ERROR_MESSAGE,',生效日期不能为空')268 where BEGIN_DATE is null;269 update tmp_fcl_import set status=0,ERROR_MESSAGE=concat(ERROR_MESSAGE,',生效日期格式不对')270 where BEGIN_DATE is not null and 0=(BEGIN_DATE REGEXP '^[0-9]{4}[\-/][0-9]{1,2}[\-/][0-9]{1,2}$');271 272 273 -- 13.END_DATE 验证:(必填，日期格式)：274 update tmp_fcl_import set status=0,ERROR_MESSAGE=concat(ERROR_MESSAGE,',失效日期不能为空')275 where END_DATE is null;276 update tmp_fcl_import set status=0,ERROR_MESSAGE=concat(ERROR_MESSAGE,',失效日期格式不对')277 where END_DATE is not null and 0=(END_DATE REGEXP '^[0-9]{4}[\-/][0-9]{1,2}[\-/][0-9]{1,2}$');278 279 -- 14.DESC_WEIGHT 验证:(非必填，长度600)：280 update tmp_fcl_import set status=0,ERROR_MESSAGE=concat(ERROR_MESSAGE,',限重说明长度过长')281 where CHAR_LENGTH(DESC_WEIGHT)>600;282 283 -- 15.REMARK_OUT 验证:(非必填，长度600)：284 update tmp_fcl_import set status=0,ERROR_MESSAGE=concat(ERROR_MESSAGE,',外部备注长度过长')285 where CHAR_LENGTH(REMARK_OUT)>1000;286 -- 16.REMARK_IN 验证:(非必填，长度600)：287 update tmp_fcl_import set status=0,ERROR_MESSAGE=concat(ERROR_MESSAGE,',内部备注长度过长')288 where CHAR_LENGTH(REMARK_IN)>1000;289 290 -- 17.CUTOFF_DAY 验证:(非必填，长度30)：    291 update tmp_fcl_import set status=0,ERROR_MESSAGE=concat(ERROR_MESSAGE,',截关日长度过长')292 where CHAR_LENGTH(CUTOFF_DAY)>30;293 294 -- 18.SPACE_MEMBER 验证:(非必填,60以内)：295 update tmp_fcl_import set status=0,ERROR_MESSAGE=concat(ERROR_MESSAGE,',共舱方长度过长')296 where CHAR_LENGTH(SPACE_MEMBER)>60;297 298 -- 19.ROUTE_CODE 验证:(非必填，200以内)：299 update tmp_fcl_import set status=0,ERROR_MESSAGE=concat(ERROR_MESSAGE,',航线代码长度过长')300 where CHAR_LENGTH(ROUTE_CODE)>200;301 302 select count(*) into @successNum from tmp_fcl_import where status!=0;303 select count(*) into @errorNum from tmp_fcl_import where status=0;    304 -- 批量处理结果：305 update tmp_fcl_import set sign = md5(CONCAT(IFNULL(PORT_START_ID,'null'),IFNULL(ROUTE_CODE,''),IFNULL(PORT_END_ID,'null'),IFNULL(SHIPPING_ID,'null'),IFNULL(WHARF_ID,'null'),IFNULL(TRANSPORT_ID,'null'),IFNULL(SCHEDULE,''),IFNULL(PORT_END_WHARF,''))) where status!=0;306 update tmp_fcl_import a join t_freight_fcl b on a.sign=b.sign and a.sign is not null and b.company_id=D_companyID set a.status=2;307 308 309 -- status=0的，只要更新error表error_message即可310 update t_freight_fcl_error a join tmp_fcl_import b on a.id=b.error_id and a.company_id=D_companyID set311 a.error_message=substring(b.error_message, 2) ;312 313 -- 去重准备314 update t_freight_fcl_error set remove=1 where id in (select max(a.error_id) from tmp_fcl_import a where a.status!=0 GROUP BY a.sign);315 316 -- status=1的,插入t_freight_fcl表317 INSERT INTO `t_freight_fcl`318 (`PORT_START_ID`, `PORT_END_ID`, `SHIPPING_ID`, `WHARF_ID`, `SCHEDULE`, `TRANSPORT_ID`, `ROUTE_CODE`, `PORT_END_WHARF`, `ROUTE_ID`, `CUTOFF_DAY`, `VOYAGE`, `SPACE_NUM`, `SPACE_STATUS`, `S_NO`, `CALL_FLAG`, `ONSALE_FLAG`, `PRICE_20`, `PRICE_40`, `PRICE_40HQ`, `PRICE_45HQ`, `BEGIN_DATE`, `END_DATE`, `REMARK_OUT`, `REMARK_IN`, `SPACE_MEMBER`, `DESC_WEIGHT`, `BILL_TYPE`,`COMPANY_ID`, `CREATE_ID`, `CREATE_TIME`, `UPDATE_ID`, `UPDATE_TIME`, `REMOVE`,DISPLAY_FLAG,SUPER_FLAG,PLATFORM_FLAG,DBA_FLAG,DISABLE_FLAG,SIGN)319 select PORT_START_ID,PORT_END_ID,SHIPPING_ID,WHARF_ID,SCHEDULE,TRANSPORT_ID,ROUTE_CODE,PORT_END_WHARF,ROUTE_ID,CUTOFF_DAY,VOYAGE,SPACE_NUM,SPACE_STATUS,S_NO,CALL_FLAG,ONSALE_FLAG,PRICE_20,PRICE_40,PRICE_40HQ,PRICE_45HQ,BEGIN_DATE,END_DATE,REMARK_OUT,REMARK_IN,SPACE_MEMBER,DESC_WEIGHT,BILL_TYPE,COMPANY_ID,CREATE_ID,NOW(),UPDATE_ID,NOW(),0,0,0,0,0,0,SIGN320 from tmp_fcl_import where status=1 and error_id in (select id from t_freight_fcl_error where remove=1 );321 -- status=2的,更新t_freight_fcl表322 update t_freight_fcl a join tmp_fcl_import b on a.sign = b.sign and b.status=2 and a.company_id=D_companyID323 set a.PORT_START_ID = b.PORT_START_ID,324 a.PORT_END_ID = b.PORT_END_ID,325 a.SHIPPING_ID = b.SHIPPING_ID,326 a.WHARF_ID = b.WHARF_ID,327 a.SCHEDULE = b.SCHEDULE,328 a.TRANSPORT_ID = b.TRANSPORT_ID,329 a.ROUTE_CODE = b.ROUTE_CODE,330 a.PORT_END_WHARF = b.PORT_END_WHARF,331 a.ROUTE_ID = b.ROUTE_ID,332 a.CUTOFF_DAY = b.CUTOFF_DAY,333 a.VOYAGE = b.VOYAGE,334 a.SPACE_NUM = b.SPACE_NUM,335 a.SPACE_STATUS = b.SPACE_STATUS,336 a.S_NO = b.S_NO,337 a.CALL_FLAG = b.CALL_FLAG,338 a.ONSALE_FLAG = b.ONSALE_FLAG,339 a.PRICE_20 = b.PRICE_20,340 a.PRICE_40 = b.PRICE_40,341 a.PRICE_40HQ = b.PRICE_40HQ,342 a.PRICE_45HQ = b.PRICE_45HQ,343 a.BEGIN_DATE = b.BEGIN_DATE,344 a.END_DATE = b.END_DATE,345 a.REMARK_OUT = b.REMARK_OUT,346 a.REMARK_IN = b.REMARK_IN,347 a.SPACE_MEMBER = b.SPACE_MEMBER,348 a.DESC_WEIGHT = b.DESC_WEIGHT,349 a.BILL_TYPE = b.BILL_TYPE,350 a.UPDATE_ID = b.UPDATE_ID,351 a.UPDATE_TIME = NOW() where b.error_id in (select id from t_freight_fcl_error where remove=1);352 353 -- 删除error表验证通过的记录354 delete from t_freight_fcl_error where id in (select error_id from tmp_fcl_import where status!=0);355 356 -- 返回结果357 select @successNum successNum,@errorNum errorNum;358 END