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` = 0 149 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 null 151 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` = 0 154 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 null 156 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` = 0 159 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 null 161 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` = 0 164 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` = 0 167 set a.WHARF_ID = b.id; 168 169 -- 将权限不足的id值的记录，对应的ERROR_message置为“无此港口权限”,status置为0 170 -- 只有起始港、航线、船公司要考虑权限 171 case when D_USER_ID is not null 172 then 173 -- 起始港 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 null 177 then 178 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 else 180 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 null 187 then 188 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 else 190 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 null 197 then 198 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 else 200 set @i=@i; 201 end case; 202 else 203 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 set 311 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,SIGN 320 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_companyID 323 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
