private void delivery(Connection conn) throws Exception {
String sql =
"select a.sheetid,b.tid, upper(ltrim(rtrim(b.companycode))) companycode,"
+ "upper(ltrim(rtrim(b.outsid))) outsid from it_upnote a with(nolock), ns_delivery b with(nolock)"
+ "where a.sheettype=3 and a.sheetid=b.sheetid and a.receiver='"
+ Params.tradecontactid
+ "' and b.iswait=0";
Vector vdeliveryorder = SQLHelper.multiRowSelect(conn, sql);
Log.info(username, " 本次要处理的淘宝发货条数为: " + vdeliveryorder.size(), null);
for (int i = 0; i < vdeliveryorder.size(); i++) {
Hashtable hto = (Hashtable) vdeliveryorder.get(i);
String sheetid = hto.get("sheetid") != null ? hto.get("sheetid").toString() : "";
String orderid = hto.get("tid") != null ? hto.get("tid").toString() : "";
String post_company = hto.get("companycode") != null ? hto.get("companycode").toString() : "";
String post_no = hto.get("outsid") != null ? hto.get("outsid").toString() : "";
// 如果物流公司为空则忽略处理
if (post_company.trim().equals("")) {
Log.warn(username, jobName + " 快递公司为空！订单号:" + orderid, null);
continue;
}
// 如果物流公司为空则忽略处理
if (post_no.trim().equals("")) {
Log.warn(username, "快递单号为空！订单号:" + orderid, null);
continue;
}
if (!StringUtil.isNumeric(orderid)) {
sql =
"insert into IT_UpNoteBak(Owner,SheetID,SheetType,Sender,Receiver,Notetime,HandleTime,Flag) "
+ " select Owner , SheetID , SheetType , Sender , Receiver , Notetime , getdate() , 1 from IT_UpNote "
+ " where SheetID = '"
+ sheetid
+ "' and SheetType = 3";
SQLHelper.executeSQL(conn, sql);
sql = "delete from IT_UpNote where SheetID='" + sheetid + "' and sheettype=3";
SQLHelper.executeSQL(conn, sql);
Log.warn(username, jobName + ",订单号【" + orderid + "】不全是数字!", null);
continue;
}
if ("1".equals(Params.isJZ)) {
sql = "select top 1 lbpdc from ns_customerorder where tid='" + orderid + "'";
String isJz = SQLHelper.strSelect(conn, sql);
if ("zj".equals(isJz)) { // 家装订单
// jzSend(post_no,orderid,post_company,conn,sheetid);
Log.info(username, "家装订单暂时不发货: 订单号: " + orderid, null);
} else normalSend(post_no, orderid, post_company, conn, sheetid);
} else normalSend(post_no, orderid, post_company, conn, sheetid);
}
}
/** 修改物流公司和运单号 已经发过货的订单才能进行此操作 api : taobao.logistics.consign.resend 免费 */
private void resend(Connection conn) throws Exception {
String sql =
"select a.sheetid,b.tid, upper(ltrim(rtrim(b.companycode))) companycode,"
+ " upper(ltrim(rtrim(b.outsid))) outsid from it_upnote a with(nolock), ns_delivery b with(nolock)"
+ "where a.sheettype=4 and a.sheetid=b.sheetid and a.receiver='"
+ Params.tradecontactid
+ "' and b.iswait=0";
Vector vdeliveryorder = SQLHelper.multiRowSelect(conn, sql);
for (int i = 0; i < vdeliveryorder.size(); i++) {
String orderid = "";
try {
Hashtable hto = (Hashtable) vdeliveryorder.get(i);
String sheetid = hto.get("sheetid") != null ? hto.get("sheetid").toString() : "";
orderid = hto.get("tid") != null ? hto.get("tid").toString() : "";
String post_company =
hto.get("companycode") != null ? hto.get("companycode").toString().trim() : "";
String post_no =
hto.get("outsid").toString() != null ? hto.get("outsid").toString().trim() : "";
// 如果物流公司为空则忽略处理
if (post_company.trim().equals("")) {
Log.warn(username, jobName + " 快递公司为空！订单号:" + orderid + "");
continue;
}
// 如果物流公司为空则忽略处理
if (post_no.trim().equals("")) {
Log.warn(username, jobName + "快递单号为空！订单号:" + orderid + "");
continue;
}
if (!StringUtil.isNumeric(orderid)) {
sql =
"insert into IT_UpNoteBak(Owner,SheetID,SheetType,Sender,Receiver,Notetime,HandleTime,Flag) "
+ " select Owner , SheetID , SheetType , Sender , Receiver , Notetime , getdate() , 1 from IT_UpNote "
+ " where SheetID = '"
+ sheetid
+ "' and SheetType = 4";
SQLHelper.executeSQL(conn, sql);
sql = "delete from IT_UpNote where SheetID='" + sheetid + "' and sheettype=4";
SQLHelper.executeSQL(conn, sql);
Log.warn(username, jobName + " 订单号【" + orderid + "】不全是数字!");
continue;
}
TaobaoClient client = new DefaultTaobaoClient(Params.url, Params.appkey, Params.appsecret);
LogisticsConsignResendRequest req = new LogisticsConsignResendRequest();
req.setOutSid(post_no);
req.setTid(TranTid(orderid));
req.setCompanyCode(post_company);
LogisticsConsignResendResponse rsp = client.execute(req, Params.authcode);
if (rsp.isSuccess()) {
try {
conn.setAutoCommit(false);
sql =
"insert into IT_UpNoteBak(Owner,SheetID,SheetType,Sender,Receiver,Notetime,HandleTime,Flag) "
+ " select Owner , SheetID , SheetType , Sender , Receiver , Notetime , getdate() , 1 from IT_UpNote "
+ " where SheetID = '"
+ sheetid
+ "' and SheetType = 4";
SQLHelper.executeSQL(conn, sql);
sql = "delete from IT_UpNote where SheetID='" + sheetid + "' and sheettype=4";
SQLHelper.executeSQL(conn, sql);
conn.commit();
conn.setAutoCommit(true);
} catch (Exception sqle) {
if (!conn.getAutoCommit())
try {
conn.rollback();
} catch (Exception e1) {
}
try {
conn.setAutoCommit(true);
} catch (Exception e1) {
}
// throw new JSQLException(sql, sqle);
}
Log.info(
username,
"处理订单【" + orderid + "】发货成功,快递公司【" + post_company + "】,快递单号【" + post_no + "】",
null);
} else {
if (rsp.getSubMsg().indexOf("不能重复发货") >= 0 || rsp.getSubMsg().indexOf("发货类型不匹配") >= 0) {
sql =
"insert into IT_UpNoteBak(Owner,SheetID,SheetType,Sender,Receiver,Notetime,HandleTime,Flag) "
+ " select Owner , SheetID , SheetType , Sender , Receiver , Notetime , getdate() , 1 from IT_UpNote "
+ " where SheetID = '"
+ sheetid
+ "' and SheetType = 4";
SQLHelper.executeSQL(conn, sql);
sql = "delete from IT_UpNote where SheetID='" + sheetid + "' and sheettype=4";
SQLHelper.executeSQL(conn, sql);
Log.info(
username,
"订单【" + orderid + "】不能重复发货,快递公司【" + post_company + "】,快递单号【" + post_no + "】",
null);
} else if (rsp.getSubMsg().indexOf("订单未发货") >= 0) {
sql =
"insert into IT_UpNoteBak(Owner,SheetID,SheetType,Sender,Receiver,Notetime,HandleTime,Flag) "
+ " select Owner , SheetID , SheetType , Sender , Receiver , Notetime , getdate() , 1 from IT_UpNote "
+ " where SheetID = '"
+ sheetid
+ "' and SheetType = 4";
SQLHelper.executeSQL(conn, sql);
sql = "delete from IT_UpNote where SheetID='" + sheetid + "' and sheettype=4";
SQLHelper.executeSQL(conn, sql);
Log.info(
username,
"订单【" + orderid + "】可能未发货，或已经退货,快递公司【" + post_company + "】,快递单号【" + post_no + "】",
null);
} else if ((rsp.getSubMsg().indexOf("没有权限进行发货") >= 0)
|| rsp.getSubMsg().indexOf("没有权限发货") >= 0
|| rsp.getSubMsg().indexOf("当前订单状态不支持修改") >= 0) {
sql =
"insert into IT_UpNoteBak(Owner,SheetID,SheetType,Sender,Receiver,Notetime,HandleTime,Flag) "
+ " select Owner , SheetID , SheetType , Sender , Receiver , Notetime , getdate() , 1 from IT_UpNote "
+ " where SheetID = '"
+ sheetid
+ "' and SheetType = 4";
SQLHelper.executeSQL(conn, sql);
sql = "delete from IT_UpNote where SheetID='" + sheetid + "' and sheettype=4";
SQLHelper.executeSQL(conn, sql);
Log.info(
username,
"没有权限发货,订单【" + orderid + "】,快递公司【" + post_company + "】,快递单号【" + post_no + "】",
null);
} else if (rsp.getSubMsg().indexOf("物流订单不存在") >= 0
|| rsp.getSubMsg().indexOf("订单已经被拆单") >= 0
|| rsp.getSubMsg().indexOf("当前操作的订单是拆单订单") >= 0) {
TaobaoClient subclient =
new DefaultTaobaoClient(Params.url, Params.appkey, Params.appsecret);
LogisticsOfflineSendRequest subreq = new LogisticsOfflineSendRequest();
subreq.setOutSid(post_no);
subreq.setTid(TranTid(orderid));
subreq.setCompanyCode(post_company);
subreq.setIsSplit(1L);
String subtids = "";
sql =
"select oid from customerorderitem a with(nolock),customerorder b with(nolock),"
+ "customerdelive c with(nolock) where a.sheetid=b.sheetid and a.sheetid=c.refsheetid "
+ "and c.customersheetid='"
+ orderid
+ "' and c.delivery='"
+ post_company
+ "' "
+ "and c.deliverysheetid='"
+ post_no
+ "'";
List sublist = SQLHelper.multiRowListSelect(conn, sql);
if (sublist.size() > 0) {
for (Iterator it = sublist.iterator(); it.hasNext(); ) {
String oid = (String) it.next();
subtids = oid + "," + subtids;
}
subtids = subtids.substring(0, subtids.length() - 1);
subreq.setSubTid(subtids);
LogisticsOfflineSendResponse subrsp = client.execute(subreq, Params.authcode);
if (subrsp.isSuccess()) {
Log.info(
username,
"处理订单【"
+ orderid
+ "】,子订单【"
+ subtids
+ "】发货成功,快递公司【"
+ post_company
+ "】,快递单号【"
+ post_no
+ "】",
null);
}
}
sql =
"insert into IT_UpNoteBak(Owner,SheetID,SheetType,Sender,Receiver,Notetime,HandleTime,Flag) "
+ " select Owner , SheetID , SheetType , Sender , Receiver , Notetime , getdate() , 1 from IT_UpNote "
+ " where SheetID = '"
+ sheetid
+ "' and SheetType = 4";
SQLHelper.executeSQL(conn, sql);
sql = "delete from IT_UpNote where SheetID='" + sheetid + "' and sheettype=4";
SQLHelper.executeSQL(conn, sql);
Log.info(
jobName,
"物流订单不存在或订单已经被拆,订单【"
+ orderid
+ "】,快递公司【"
+ post_company
+ "】,快递单号【"
+ post_no
+ "】");
} else if (rsp.getSubMsg().indexOf("运单号不符合规则或已经被使用") >= 0) {
// 公司内部自提发货处理
if ((post_no.toUpperCase().indexOf("1111111") >= 0)
|| (post_no.toUpperCase().indexOf("YJ") >= 0)) {
sql =
"insert into IT_UpNoteBak(Owner,SheetID,SheetType,Sender,Receiver,Notetime,HandleTime,Flag) "
+ " select Owner , SheetID , SheetType , Sender , Receiver , Notetime , getdate() , 1 from IT_UpNote "
+ " where SheetID = '"
+ sheetid
+ "' and SheetType = 4";
SQLHelper.executeSQL(conn, sql);
sql = "delete from IT_UpNote where SheetID='" + sheetid + "' and sheettype=4";
SQLHelper.executeSQL(conn, sql);
} else {
String cc = "";
if (post_no.toUpperCase().indexOf("EH") == 0) {
cc = "EMS";
} else if (post_no.toUpperCase().indexOf("101") == 0) {
cc = "SF";
} else if (post_no.toUpperCase().indexOf("368") == 0) {
cc = "STO";
} else if (post_no.toUpperCase().indexOf("W") == 0) {
cc = "YTO";
} else cc = post_company;
sql =
"update ns_delivery set companycode='"
+ cc
+ "' where SheetID = '"
+ sheetid
+ "'";
SQLHelper.executeSQL(conn, sql);
}
Log.info(
username,
"运单号不符合规则或已经被使用,订单【"
+ orderid
+ "】,快递公司【"
+ post_company
+ "】,快递单号【"
+ post_no
+ "】",
null);
} else if (rsp.getSubMsg().indexOf("该订单不支持修改") >= 0) {
sql =
"insert into IT_UpNoteBak(Owner,SheetID,SheetType,Sender,Receiver,Notetime,HandleTime,Flag) "
+ " select Owner , SheetID , SheetType , Sender , Receiver , Notetime , getdate() , 1 from IT_UpNote "
+ " where SheetID = '"
+ sheetid
+ "' and SheetType = 4";
SQLHelper.executeSQL(conn, sql);
sql = "delete from IT_UpNote where SheetID='" + sheetid + "' and sheettype=4";
SQLHelper.executeSQL(conn, sql);
Log.info(
jobName,
"处理订单【"
+ orderid
+ "】发货失败,快递公司【"
+ post_company
+ "】,快递单号【"
+ post_no
+ "】"
+ "错误信息:"
+ rsp.getSubMsg()
+ rsp.getMsg());
} else
Log.info(
username,
"处理订单【"
+ orderid
+ "】发货失败,快递公司【"
+ post_company
+ "】,快递单号【"
+ post_no
+ "】"
+ "错误信息:"
+ rsp.getSubMsg()
+ rsp.getMsg(),
null);
}
} catch (Exception ex) {
if (conn != null && !conn.getAutoCommit()) conn.rollback();
Log.info(username, "修改发货信息作业出错，订单号: " + orderid + ",错误信息: " + ex.getMessage(), null);
}
}
}