淘宝转链接口更新

admin

2025-02-25 30d8e227e8d823b6c38c3b9c90ac2df03b63befe

 fanli/src/main/java/com/yeshi/fanli/util/taobao/TaoBaoOrderUtil.java

@@ -1,747 +1,787 @@
package com.yeshi.fanli.util.taobao;



import java.io.IOException;

import java.io.InputStream;

import java.math.BigDecimal;

import java.net.URLEncoder;

import java.util.ArrayList;

import java.util.Date;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import java.util.concurrent.ConcurrentHashMap;



import org.apache.commons.httpclient.HttpClient;

import org.apache.commons.httpclient.HttpException;

import org.apache.commons.httpclient.methods.GetMethod;

import org.springframework.stereotype.Component;

import org.yeshi.utils.HttpUtil;



import com.yeshi.fanli.dto.order.OrderItem;

import com.yeshi.fanli.entity.bus.user.Order;

import com.yeshi.fanli.entity.bus.user.UserInfo;

import com.yeshi.fanli.entity.order.CommonOrder;

import com.yeshi.fanli.entity.taobao.TaoBaoOrder;

import com.yeshi.fanli.entity.taobao.TaoBaoUnionConfig;

import com.yeshi.fanli.entity.taobao.TaoBaoWeiQuanOrder;

import com.yeshi.fanli.exception.TaoBaoOrderException;

import com.yeshi.fanli.log.LogHelper;

import com.yeshi.fanli.util.Constant;

import com.yeshi.fanli.util.MoneyBigDecimalUtil;

import com.yeshi.fanli.util.StringUtil;

import com.yeshi.fanli.util.TimeUtil;



import jxl.Sheet;

import jxl.Workbook;

import net.sf.json.JSONObject;



@Component

public class TaoBaoOrderUtil {



   private static final String ORDERFAILURE = "订单失效";

   private static final String ORDERPAYMENT = "订单付款";

   private static final String ORDERSETTLEMENT = "订单结算";

   private static final String ORDERSUCCESS = "订单成功";



   /**

    * 获取淘宝联盟的Cookie

    * 
    * @param account

    * @return

    */

   public static String getTaoBaoCookie(String account) {

      HttpClient client = new HttpClient();

      try {

         GetMethod gm = new GetMethod(String.format("http://193.112.35.168:8091/tb/cookie/getcookie?account=%s",

               URLEncoder.encode(account, "UTF-8")));

         client.executeMethod(gm);

         String as = gm.getResponseBodyAsString();

         JSONObject root = JSONObject.fromObject(as);

         if (root.optInt("code") == 0) {

            // EhcacheUtil.removeCache(account);

            String cookie = root.optString("cookie");

            return cookie;

         }



      } catch (Exception e) {

         e.printStackTrace();

      }

      // Cache cache = EhcacheUtil.getCache(account);

      // if(cache==null){

      // EhcacheUtil.addCahae(EhcacheUtil.getMyPubCacheConfig(account));

      // String title="cookie失效提醒";

      // String content="淘宝联盟账号["+account+"],已经失效了！";

      // String emails = taoBaoOrderUtil.configService.get("send_email");

      // if(emails != null && !"".equals(emails.trim())){

      // String[] addr = emails.split(",");

      // for (String email : addr) {

      // MailSenderUtil.sendEmail(email, title, content);

      // }

      // }

      // }

      return "";

   }



   /**

    * 上报联盟的Cookie无效

    * 
    * @param account

    */

   public static void reportCookieInvalid(String account) {

      if (1 > 0)

         return;

      HttpClient client = new HttpClient();

      try {

         GetMethod gm = new GetMethod(String.format("http://193.112.35.168:8091/tb/cookie/reportinvalid?account=%s",

               URLEncoder.encode(account, "UTF-8")));

         client.executeMethod(gm);

         String as = gm.getResponseBodyAsString();

         JSONObject root = JSONObject.fromObject(as);

         if (root.optInt("code") == 0) {// 上报成功

            LogHelper.cookieLog("上报成功！");

            return;

         }

         LogHelper.cookieLog("上报失败！");



      } catch (Exception e) {

         e.printStackTrace();

         LogHelper.cookieLog("上报失败！");

      }

   }



   /**

    * 按天数爬取淘宝联盟的返利订单

    * 
    * @param day

    * @return

    */

   public static List<TaoBaoOrder> getOrderList(int day, TaoBaoUnionConfig config) {

      String cookie1 = getTaoBaoCookie(config.getAccount());



      LogHelper.cookieLog("账号为：" + config.getAccount() + ",cookie为：" + cookie1);



      String[] cookies = new String[] { cookie1 };

      List<TaoBaoOrder> list = new ArrayList<TaoBaoOrder>();

      int i = 0;

      for (String cookie : cookies) {

         if (com.yeshi.fanli.util.StringUtil.isNullOrEmpty(cookie)) {

            i++;

            continue;

         }

         // 原始为30

         String startTime = TimeUtil.getGernalTime(System.currentTimeMillis() - 1000 * 60 * 60 * 24L * day,

               "yyyy-MM-dd");

         String endTime = TimeUtil.getGernalTime(System.currentTimeMillis(), "yyyy-MM-dd");



         String url = String.format(

               "http://pub.alimama.com/report/getTbkPaymentDetails.json?spm=a219t.7664554.1998457203.85.9pFBtK&queryType=1&payStatus=&DownloadID=DOWNLOAD_REPORT_INCOME_NEW&startTime=%s&endTime=%s",

               startTime, endTime);

         HttpClient client = new HttpClient();

         // client.getHostConfiguration().setProxy("192.168.1.200", 8888);

         GetMethod gm = new GetMethod(url);

         gm.setRequestHeader("Cookie", cookie);

         gm.setRequestHeader("User-Agent",

               "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/51.0.2704.106 Safari/537.36");

         InputStream inputStream = null;

         try {

            client.executeMethod(gm);

            System.out.println(gm.getStatusCode());

            if (gm.getStatusCode() == 200) {

               System.out.println(gm.getResponseHeader("Content-Type").getValue());

               if (!gm.getResponseHeader("Content-Type").getValue().contains("excel")) {

                  if (i == 0) {

                     reportCookieInvalid(config.getAccount());

                  }

                  continue;

               }

               inputStream = gm.getResponseBodyAsStream();

               list.addAll(parseOrder(inputStream));



               // String path =

               // TaoBaoOrderUtil.class.getClassLoader().getResource("/").getPath();

               // String yyyyMMddHHmmss =

               // TimeUtil.yyyyMMddHHmmss(System.currentTimeMillis());

               // FileUtils.copyInputStreamToFile(inputStream, new

               // File(path+"/excel/"+yyyyMMddHHmmss+"."+"xls"));

            }

         } catch (HttpException e) {

            e.printStackTrace();

         } catch (IOException e) {

            e.printStackTrace();

         } finally {

            i++;

            if (inputStream != null) {

               try {

                  inputStream.close();

               } catch (IOException e) {

                  e.printStackTrace();

               }

            }

         }

      }



      return list;

   }



   /**

    * 根据链接下载淘宝订单

    * 
    * @param url

    * @return

    */

   private static List<TaoBaoOrder> downLoadOrderFromTaoBao(String url, TaoBaoUnionConfig config)

         throws TaoBaoOrderException {

      String cookie1 = getTaoBaoCookie(config.getAccount());

      LogHelper.cookieLog("账号为：" + config.getAccount() + ",cookie为：" + cookie1);

      String[] cookies = new String[] { cookie1 };

      List<TaoBaoOrder> list = new ArrayList<TaoBaoOrder>();

      int i = 0;

      for (String cookie : cookies) {

         if (com.yeshi.fanli.util.StringUtil.isNullOrEmpty(cookie)) {

            i++;

            continue;

         }

         // 原始为30



         HttpClient client = new HttpClient();

         // client.getHostConfiguration().setProxy("192.168.1.122", 8888);

         GetMethod gm = new GetMethod(url);

         gm.setRequestHeader("Cookie", cookie);

         gm.setRequestHeader("User-Agent",

               "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/51.0.2704.106 Safari/537.36");

         gm.setRequestHeader("Referer", "http://pub.alimama.com/myunion.htm");

         gm.setRequestHeader("Accept",

               "text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8");

         gm.setRequestHeader("Upgrade-Insecure-Requests", "1");

         gm.setRequestHeader("Accept-Encoding", "gzip, deflate");

         gm.setRequestHeader("Accept-Language", "zh-CN,zh;q=0.9");

         InputStream inputStream = null;

         try {

            client.executeMethod(gm);

            System.out.println(gm.getStatusCode());

            if (gm.getStatusCode() == 200) {

               System.out.println(gm.getResponseHeader("Content-Type").getValue());

               if (!gm.getResponseHeader("Content-Type").getValue().contains("excel")) {

                  if (i == 0) {

                     throw new TaoBaoOrderException(gm.getStatusCode(), "返回的内容异常");

                  }

                  continue;

               }

               inputStream = gm.getResponseBodyAsStream();

               list.addAll(parseOrder(inputStream));



               // String path =

               // TaoBaoOrderUtil.class.getClassLoader().getResource("/").getPath();

               // String yyyyMMddHHmmss =

               // TimeUtil.yyyyMMddHHmmss(System.currentTimeMillis());

               // FileUtils.copyInputStreamToFile(inputStream, new

               // File(path+"/excel/"+yyyyMMddHHmmss+"."+"xls"));

            } else {

               throw new TaoBaoOrderException(gm.getStatusCode(), gm.getResponseHeader("Location").getValue());

            }

         } catch (HttpException e) {

            e.printStackTrace();

         } catch (IOException e) {

            e.printStackTrace();

         } finally {

            i++;

            if (inputStream != null) {

               try {

                  inputStream.close();

               } catch (IOException e) {

                  e.printStackTrace();

               }

            }

         }

      }

      return list;

   }



   /**

    * 按时间戳爬取淘宝联盟的返利订单

    * 
    * @param startTime

    * @param endTime

    * @return

    */

   @SuppressWarnings("deprecation")

   public static List<TaoBaoOrder> getOrderList(long startTime, long endTime, TaoBaoUnionConfig config)

         throws TaoBaoOrderException {

      String startTimeStr = TimeUtil.getGernalTime(startTime, "yyyy-MM-dd HH:mm:ss");

      String endTimeStr = TimeUtil.getGernalTime(endTime, "yyyy-MM-dd HH:mm:ss");



      String url = String.format(

            "http://pub.alimama.com/report/getTbkPaymentDetails.json?spm=a219t.7664554.1998457203.10.7a7835d9UJKaiP&queryType=1&payStatus=&DownloadID=DOWNLOAD_REPORT_INCOME_NEW&startTime=%s&endTime=%s",

            URLEncoder.encode(startTimeStr), URLEncoder.encode(endTimeStr));



      return downLoadOrderFromTaoBao(url, config);

   }



   /**

    * 爬取第三方服务商推广订单

    * 
    * @param startTime

    * @param endTime

    * @return

    */

   @SuppressWarnings("deprecation")

   public static List<TaoBaoOrder> getThirdServiceOrderList(long startTime, long endTime, TaoBaoUnionConfig config)

         throws TaoBaoOrderException {

      String startTimeStr = TimeUtil.getGernalTime(startTime, "yyyy-MM-dd HH:mm:ss");

      String endTimeStr = TimeUtil.getGernalTime(endTime, "yyyy-MM-dd HH:mm:ss");

      String url = String.format(

            "http://pub.alimama.com/report/getTbkThirdPaymentDetails.json?spm=a219t.7664554.1998457203.10.7a7835d9UJKaiP&queryType=2&payStatus=&DownloadID=DOWNLOAD_REPORT_TK3_PUB&startTime=%s&endTime=%s",

            URLEncoder.encode(startTimeStr), URLEncoder.encode(endTimeStr));

      return downLoadOrderFromTaoBao(url, config);

   }



   /**

    * 爬取最近30天的订单

    * 
    * @return

    */

   public static List<TaoBaoOrder> getOrderList(TaoBaoUnionConfig config) {

      return getOrderList(30, config);

   }



   /**

    * 获取维权成功订单

    * 
    * @param startTime

    * @param endTime

    * @return

    */

   public static List<TaoBaoWeiQuanOrder> getWeiQuanOrders(long startTime, long endTime, TaoBaoUnionConfig config) {

      String cookie = getTaoBaoCookie(config.getAccount());

      return getWeiQuanOrders(cookie, startTime, endTime);

   }



   private static List<TaoBaoWeiQuanOrder> getWeiQuanOrders(String cookies, long startTime, long endTime) {

      String startTimeStr = TimeUtil.getGernalTime(startTime, "yyyy-MM-dd");

      String endTimeStr = TimeUtil.getGernalTime(endTime, "yyyy-MM-dd");

      String url = String.format(

            "http://pub.alimama.com/report/getNewTbkRefundPaymentDetails.json?spm=a219t.7664554.1998457203.105.49cc35d9eDSunp&refundType=1&searchType=1&DownloadID=DOWNLOAD_EXPORT_CPSPAYMENT_REFUND_OVERVIEW&startTime=%s&endTime=%s",

            startTimeStr, endTimeStr);

      Map<String, String> headers = new HashMap<>();

      headers.put("User-Agent",

            "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/63.0.3239.84 Safari/537.36");

      headers.put("Cookie", cookies);

      headers.put("Referer", "http://pub.alimama.com/myunion.htm");

      headers.put("Upgrade-Insecure-Requests", "1");

      InputStream in = HttpUtil.getAsInputStream(url, headers);



      List<TaoBaoWeiQuanOrder> list = new ArrayList<>();

      list = parseWeiQuanOrder(in);



      list.addAll(getThirdWeiQuanOrders(cookies, startTime, endTime));



      // if (list != null)

      // for (int i = 0; i < list.size(); i++) {

      // if (!list.get(i).getState().equalsIgnoreCase("维权成功")) {

      // list.remove(i);

      // i--;

      // }

      // }

      return list;

   }



   // 第三方订单维权

   private static List<TaoBaoWeiQuanOrder> getThirdWeiQuanOrders(String cookies, long startTime, long endTime) {

      String startTimeStr = TimeUtil.getGernalTime(startTime, "yyyy-MM-dd");

      String endTimeStr = TimeUtil.getGernalTime(endTime, "yyyy-MM-dd");

      String url = String.format(

            "http://pub.alimama.com/report/getNewTbkRefundPaymentDetails.json?spm=a219t.7664554.1998457203.163.158a35d9r0bV0O&refundType=2&searchType=3&DownloadID=DOWNLOAD_EXPORT_CPSPAYMENT_REFUND_OVERVIEW&startTime=%s&endTime=%s",

            startTimeStr, endTimeStr);

      Map<String, String> headers = new HashMap<>();

      headers.put("User-Agent",

            "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/63.0.3239.84 Safari/537.36");

      headers.put("Cookie", cookies);

      headers.put("Referer", "http://pub.alimama.com/myunion.htm");

      headers.put("Upgrade-Insecure-Requests", "1");

      InputStream in = HttpUtil.getAsInputStream(url, headers);

      return parseWeiQuanOrder(in);

   }



   /**

    * 从excel文件解析订单

    * 
    * @param in

    * @return

    */

   public static List<TaoBaoOrder> parseOrder(InputStream in) {

      try {

         jxl.Workbook rwb = Workbook.getWorkbook(in);

         Sheet sheet = rwb.getSheet(0);

         List<TaoBaoOrder> orderRecordList = new ArrayList<TaoBaoOrder>();

         int createtimeColumn = 0;

         int clickTimeColumn = 0;// 点击时间

         int titleColumn = 0;// 商品名称

         int auctionIdColumn = 0;// 商品ID

         int managerWangWangColumn = 0;// 掌柜旺旺

         int shopColumn = 0;// 所属店铺

         int countColumn = 0;// 商品数

         int priceColumn = 0;// 商品单价

         int orderStateColumn = 0; // 订单状态

         int orderTypeColumn = 0; // 订单类型

         int iRatioColumn = 0;// 收入比率

         int sRatioColumn = 0;// 分成比率



         int paymentColumn = 0; // 付款金额

         int estimateColumn = 0;// 效果预估

         int settlementColumn = 0; // 结算金额

         int incomeColumn = 0;// 预估收入

         int settlementTimeColumn = 0;// 结算时间



         int tkRateColumn = 0;// 佣金比率

         int tkMoneyColumn = 0;// 佣金金额



         int technologySupportPercentColumn = 0;// 技术服务费

         int subsidyRatioColumn = 0;// 补贴比率

         int subsidyColumn = 0;// 补贴金额

         int subsidyTypeColumn = 0;// 补贴类型

         int transactionPlatformColumn = 0;// 成交平台

         int thirdServiceColumn = 0;// 第三方服务来源



         int orderIdColumn = 0;// 订单编号

         int classNameColumn = 0; // 分类名称

         int sourceMediaIdColumn = 0; // 媒体ID

         int sourceMediaNameColumn = 0;// 来源媒体名称

         int adPositionIdColumn = 0; // 广告位ID

         int adPositionNameColumn = 0;// 广告位名称



         for (int c = 0; c < sheet.getColumns(); c++) {

            String content = sheet.getCell(c, 0).getContents().trim();

            if ("创建时间".equalsIgnoreCase(content))

               createtimeColumn = c;

            else if ("点击时间".equalsIgnoreCase(content))

               clickTimeColumn = c;

            else if ("商品信息".equalsIgnoreCase(content))

               titleColumn = c;

            else if ("商品ID".equalsIgnoreCase(content))

               auctionIdColumn = c;

            else if ("掌柜旺旺".equalsIgnoreCase(content))

               managerWangWangColumn = c;

            else if ("所属店铺".equalsIgnoreCase(content))

               shopColumn = c;

            else if ("商品数".equalsIgnoreCase(content))

               countColumn = c;

            else if ("商品单价".equalsIgnoreCase(content))

               priceColumn = c;

            else if ("订单状态".equalsIgnoreCase(content))

               orderStateColumn = c;

            else if ("订单类型".equalsIgnoreCase(content))

               orderTypeColumn = c;

            else if ("收入比率".equalsIgnoreCase(content))

               iRatioColumn = c;

            else if ("分成比率".equalsIgnoreCase(content))

               sRatioColumn = c;

            else if ("付款金额".equalsIgnoreCase(content))

               paymentColumn = c;

            else if ("效果预估".equalsIgnoreCase(content))

               estimateColumn = c;

            else if ("结算金额".equalsIgnoreCase(content))

               settlementColumn = c;

            else if ("预估收入".equalsIgnoreCase(content))

               incomeColumn = c;

            else if ("结算时间".equalsIgnoreCase(content))

               settlementTimeColumn = c;

            else if ("佣金比率".equalsIgnoreCase(content))

               tkRateColumn = c;

            else if ("佣金金额".equalsIgnoreCase(content))

               tkMoneyColumn = c;

            else if ("技术服务费比率".equalsIgnoreCase(content))

               technologySupportPercentColumn = c;

            else if ("补贴比率".equalsIgnoreCase(content))

               subsidyRatioColumn = c;

            else if ("补贴金额".equalsIgnoreCase(content))

               subsidyColumn = c;

            else if ("补贴类型".equalsIgnoreCase(content))

               subsidyTypeColumn = c;

            else if ("成交平台".equalsIgnoreCase(content))

               transactionPlatformColumn = c;

            else if ("第三方服务来源".equalsIgnoreCase(content))

               thirdServiceColumn = c;

            else if ("订单编号".equalsIgnoreCase(content))

               orderIdColumn = c;

            else if ("类目名称".equalsIgnoreCase(content))

               classNameColumn = c;

            else if ("来源媒体ID".equalsIgnoreCase(content))

               sourceMediaIdColumn = c;

            else if ("来源媒体名称".equalsIgnoreCase(content))

               sourceMediaNameColumn = c;

            else if ("广告位ID".equalsIgnoreCase(content))

               adPositionIdColumn = c;

            else if ("广告位名称".equalsIgnoreCase(content))

               adPositionNameColumn = c;

         }



         for (int r = 1; r < sheet.getRows(); r++) {

            TaoBaoOrder orderRecord = new TaoBaoOrder();

            for (int c = 0; c < sheet.getColumns(); c++) {

               String content = sheet.getCell(c, r).getContents().trim();

               if (c == createtimeColumn)

                  orderRecord.setCreateTime(content);

               else if (c == clickTimeColumn)

                  orderRecord.setClickTime(content);

               else if (c == titleColumn)

                  orderRecord.setTitle(content);

               else if (c == auctionIdColumn)

                  orderRecord.setAuctionId(Long.parseLong(content));

               else if (c == managerWangWangColumn)

                  orderRecord.setManagerWangWang(content);

               else if (c == shopColumn)

                  orderRecord.setShop(content);

               else if (c == countColumn)

                  orderRecord.setCount(Integer.parseInt(content));

               else if (c == priceColumn)

                  orderRecord.setPrice(new BigDecimal(content));

               else if (c == orderStateColumn)

                  orderRecord.setOrderState(content);

               else if (c == orderTypeColumn)

                  orderRecord.setOrderType(content);

               else if (c == iRatioColumn)

                  orderRecord.setiRatio(new BigDecimal(content.replace("%", "").trim()));

               else if (c == sRatioColumn)

                  orderRecord.setsRatio(new BigDecimal(content.replace("%", "").trim()));

               else if (c == paymentColumn)

                  orderRecord.setPayment(new BigDecimal(content));

               else if (c == estimateColumn)

                  orderRecord.setEstimate(new BigDecimal(content));

               else if (c == settlementColumn)

                  orderRecord.setSettlement(new BigDecimal(content));

               else if (c == incomeColumn)

                  orderRecord.seteIncome(new BigDecimal(content));

               else if (c == settlementTimeColumn)

                  orderRecord.setSettlementTime(content);

               else if (c == tkRateColumn)

                  orderRecord.setTkRate(new BigDecimal(content.replace("%", "").trim()));

               else if (c == tkMoneyColumn)

                  orderRecord.setTkMoney(new BigDecimal(content));

               else if (c == technologySupportPercentColumn)

                  orderRecord.setTechnologySupportPercent(StringUtil.isNullOrEmpty(content) ? null

                        : new BigDecimal(content.replace("%", "").trim()));

               else if (c == subsidyRatioColumn)

                  orderRecord.setSubsidyRatio(new BigDecimal(content.replace("%", "").trim()));

               else if (c == subsidyColumn)

                  orderRecord.setSubsidy(new BigDecimal(content));

               else if (c == subsidyTypeColumn)

                  orderRecord.setSubsidyType(content);

               else if (c == transactionPlatformColumn)

                  orderRecord.setTransactionPlatform(content);

               else if (c == thirdServiceColumn)

                  orderRecord.setThirdService(content);

               else if (c == orderIdColumn)

                  orderRecord.setOrderId(content);

               else if (c == classNameColumn)

                  orderRecord.setClassName(content);

               else if (c == sourceMediaIdColumn)

                  orderRecord.setSourceMediaId(content);

               else if (c == sourceMediaNameColumn)

                  orderRecord.setSourceMediaName(content);

               else if (c == adPositionIdColumn)

                  orderRecord.setAdPositionId(content);

               else if (c == adPositionNameColumn)

                  orderRecord.setAdPositionName(content);

            }



            if (!StringUtil.isNullOrEmpty(orderRecord.getAuctionId() + ""))

               orderRecordList.add(orderRecord);

         }

         return orderRecordList;

      } catch (Exception e) {

         e.printStackTrace();

      }



      return new ArrayList<TaoBaoOrder>();



   }



   /**

    * 解析维权订单

    * 
    * @param in

    * @return

    */

   public static List<TaoBaoWeiQuanOrder> parseWeiQuanOrder(InputStream in) {

      try {

         jxl.Workbook rwb = Workbook.getWorkbook(in);

         Sheet sheet = rwb.getSheet(0);

         List<TaoBaoWeiQuanOrder> orderRecordList = new ArrayList<TaoBaoWeiQuanOrder>();

         for (int r = 1; r < sheet.getRows(); r++) {

            TaoBaoWeiQuanOrder orderRecord = new TaoBaoWeiQuanOrder();

            for (int c = 0; c < sheet.getColumns(); c++) {

               String content = sheet.getCell(c, r).getContents().trim();

               switch (c) {

               case 0://

                  orderRecord.setOrderId(content);

                  break;

               case 1://

                  orderRecord.setOrderItemId(content);

                  break;

               case 2://

                  orderRecord.setGoodsName(content);

                  break;

               case 3:

                  orderRecord.setMoney(StringUtil.isNullOrEmpty(content) ? null : new BigDecimal(content));

                  break;

               case 4:

                  orderRecord.setFanMoney(StringUtil.isNullOrEmpty(content) ? null : new BigDecimal(content));

                  break;

               case 5://

                  orderRecord.setState(content);

                  break;

               case 6://

                  if (!StringUtil.isNullOrEmpty(content))

                     orderRecord.setJieSuanTime(

                           new Date(TimeUtil.convertToTimeTemp(content, "yyyy-MM-dd HH:mm:ss")));

                  break;

               case 7://

                  if (!StringUtil.isNullOrEmpty(content))

                     orderRecord.setWeiQuanTime(

                           new Date(TimeUtil.convertToTimeTemp(content, "yyyy-MM-dd HH:mm:ss")));

                  break;

               case 8://

                  if (!StringUtil.isNullOrEmpty(content))

                     orderRecord.setWeiQuanFinishTime(

                           new Date(TimeUtil.convertToTimeTemp(content, "yyyy-MM-dd HH:mm:ss")));

                  break;

               }

            }

            if (!StringUtil.isNullOrEmpty(orderRecord.getOrderId()))

               orderRecordList.add(orderRecord);

         }

         return orderRecordList;

      } catch (Exception e) {

         e.printStackTrace();

      }

      return new ArrayList<TaoBaoWeiQuanOrder>();

   }



   public static OrderItem convert(TaoBaoOrder tbo, Order order, BigDecimal rate, List<String> tbImgList) {

      OrderItem orderItem = new OrderItem();

      orderItem.setFanTime(0L);

      orderItem.setIsGift(false);

      orderItem.setRefundTime(0L);

      orderItem.setSettlementTime(0L);

      String createTime = tbo.getCreateTime();

      orderItem.setCreateTime(TimeUtil.convertDateToTemp2(createTime));

      if ("订单结算".equalsIgnoreCase(tbo.getOrderState()))

         orderItem.setFanMoney(MoneyBigDecimalUtil.mul(rate.divide(BigDecimal.valueOf(100)), tbo.geteIncome()));

      else

         orderItem.setFanMoney(MoneyBigDecimalUtil.mul(rate.divide(BigDecimal.valueOf(100)), tbo.getEstimate()));

      if ("订单结算".equalsIgnoreCase(tbo.getOrderState()))

         orderItem.setPayMoney(tbo.getSettlement());

      else

         orderItem.setPayMoney(tbo.getPayment());



      orderItem.setOrder(order);

      orderItem.setOrderId(tbo.getOrderId());



      if (tbImgList != null && tbImgList.size() > 0) {

         orderItem.setPicture(tbImgList.get(0));

      } else {

         orderItem.setPicture("");

      }

      if (ORDERFAILURE.equals(tbo.getOrderState())) {

         orderItem.setState(-1);

      } else if (ORDERPAYMENT.equals(tbo.getOrderState())) {

         orderItem.setState(1);

      } else if (ORDERSETTLEMENT.equals(tbo.getOrderState())) {

         orderItem.setState(2);

         orderItem.setSettlementTime(TimeUtil.convertDateToTemp2(tbo.getSettlementTime()));

      } else if (ORDERSUCCESS.equals(tbo.getOrderState())) // 其他值

      {

         orderItem.setState(2);

         orderItem.setSettlementTime(TimeUtil.convertDateToTemp2(tbo.getSettlementTime()));

      }

      orderItem.setTitle(tbo.getTitle());

      orderItem.setType(1);

      UserInfo userInfo = order.getUserInfo();

      orderItem.setUserInfo(userInfo);

      orderItem.setAuctionId(tbo.getAuctionId());

      orderItem.setVersion(order.getVersion());

      orderItem.setPrePayMoney(new BigDecimal(tbo.getCount()).multiply(tbo.getPrice()));

      // orderItem.setGoodsPrice(tbo.getPrice());

      // orderItem.setGoodsNum(tbo.getCount());

      return orderItem;

   }



   /**

    * 将淘宝订单按照订单号归类

    * 
    * @param orderList

    * @return

    */

   public static Map<String, List<TaoBaoOrder>> classifyTaoBaoOrderByOrderId(List<TaoBaoOrder> orderList) {

      Map<String, List<TaoBaoOrder>> map = new ConcurrentHashMap<>();

      for (TaoBaoOrder order : orderList) {

         if (map.get(order.getOrderId()) == null)

            map.put(order.getOrderId(), new ArrayList<>());

         // 设置排序值，从1开始

         order.setOrderBy(map.get(order.getOrderId()).size() + 1);

         map.get(order.getOrderId()).add(order);

      }

      return map;

   }



   /**

    * 计算邀请赚的返利时间

    * 
    * @param balanceTime

    * @return

    */

   public static long computeInviteFanLiTime(long balanceTime) {

      int month = Integer.parseInt(TimeUtil.getGernalTime(balanceTime, "M"));

      int year = Integer.parseInt(TimeUtil.getGernalTime(balanceTime, "yyyy"));

      String time = "";

      if (month == 12) {

         time = (year + 1) + "-1";

      } else {

         time = year + "-" + (month + 1);

      }

      time += "-25";

      // 设为25日凌晨到账

      time += " 00:00:00";

      // TimeUtil.getGernalTime(balanceTime, "HH:mm:ss");

      return TimeUtil.convertToTimeTemp(time, "yyyy-M-dd HH:mm:ss");

   }



   public static CommonOrder convert(TaoBaoOrder taoBaoOrder) {

      CommonOrder commonOrder = new CommonOrder();

      commonOrder.setCount(taoBaoOrder.getCount());

      commonOrder.setCreateTime(new Date());

      if (taoBaoOrder.geteIncome() != null && taoBaoOrder.getSubsidy() != null)

         commonOrder.seteIncome(taoBaoOrder.geteIncome().add(taoBaoOrder.getSubsidy()));

      else

         commonOrder.seteIncome(taoBaoOrder.geteIncome());

      commonOrder.setEstimate(taoBaoOrder.getEstimate());

      commonOrder.setOrderNo(taoBaoOrder.getOrderId());

      commonOrder.setPayment(taoBaoOrder.getPayment());

      commonOrder.setSettlement(taoBaoOrder.getSettlement());

      if (!StringUtil.isNullOrEmpty(taoBaoOrder.getSettlementTime()))

         commonOrder.setSettleTime(

               new Date(TimeUtil.convertToTimeTemp(taoBaoOrder.getSettlementTime(), "yyyy-MM-dd HH:mm:ss")));

      commonOrder.setSourcePosition(String.format("mm_%s_%s_%s", "124933865", taoBaoOrder.getSourceMediaId(),

            taoBaoOrder.getAdPositionId()));

      commonOrder.setSourceType(Constant.SOURCE_TYPE_TAOBAO);

      int state = 0;

      if ("订单付款".equalsIgnoreCase(taoBaoOrder.getOrderState())) {

         state = CommonOrder.STATE_FK;

      } else if ("订单成功".equalsIgnoreCase(taoBaoOrder.getOrderState())) {

         state = CommonOrder.STATE_FK;

      } else if ("订单失效".equalsIgnoreCase(taoBaoOrder.getOrderState())) {

         state = CommonOrder.STATE_SX;

      } else if ("订单结算".equalsIgnoreCase(taoBaoOrder.getOrderState())) {

         state = CommonOrder.STATE_JS;

      }

      commonOrder.setOrderBy(taoBaoOrder.getOrderBy());

      commonOrder.setState(state);

      commonOrder.setThirdCreateTime(

            new Date(TimeUtil.convertToTimeTemp(taoBaoOrder.getCreateTime(), "yyyy-MM-dd HH:mm:ss")));

      commonOrder.setUpdateTime(new Date());

      commonOrder.setTradeId(taoBaoOrder.getTradeId());



      return commonOrder;



   }

}

package com.yeshi.fanli.util.taobao;

import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

import org.apache.commons.httpclient.HttpClient;
import org.apache.commons.httpclient.HttpException;
import org.apache.commons.httpclient.methods.GetMethod;
import org.springframework.stereotype.Component;
import org.yeshi.utils.HttpUtil;

import com.yeshi.fanli.dto.order.OrderItem;
import com.yeshi.fanli.entity.bus.user.Order;
import com.yeshi.fanli.entity.bus.user.UserInfo;
import com.yeshi.fanli.entity.taobao.TaoBaoOrder;
import com.yeshi.fanli.entity.taobao.TaoBaoUnionConfig;
import com.yeshi.fanli.entity.taobao.TaoBaoWeiQuanOrder;
import com.yeshi.fanli.exception.order.TaoBaoOrderException;
import com.yeshi.fanli.log.LogHelper;
import org.yeshi.utils.MoneyBigDecimalUtil;
import com.yeshi.fanli.util.StringUtil;
import org.yeshi.utils.TimeUtil;

import jxl.Sheet;
import jxl.Workbook;
import net.sf.json.JSONObject;

@Component
public class TaoBaoOrderUtil {

   private static final String ORDERFAILURE = "订单失效";
   private static final String ORDERPAYMENT = "订单付款";
   private static final String ORDERSETTLEMENT = "订单结算";
   private static final String ORDERSUCCESS = "订单成功";

   /**
    * 获取淘宝联盟的Cookie
    * 
    * @param account
    * @return
    */
   public static String getTaoBaoCookie(String account) {
      HttpClient client = new HttpClient();
      try {
         GetMethod gm = new GetMethod(String.format("http://193.112.35.168:8091/tb/cookie/getcookie?account=%s",
               URLEncoder.encode(account, "UTF-8")));
         client.executeMethod(gm);
         String as = gm.getResponseBodyAsString();
         JSONObject root = JSONObject.fromObject(as);
         if (root.optInt("code") == 0) {
            // EhcacheUtil.removeCache(account);
            String cookie = root.optString("cookie");
            return cookie;
         }

      } catch (Exception e) {
         e.printStackTrace();
      }
      // Cache cache = EhcacheUtil.getCache(account);
      // if(cache==null){
      // EhcacheUtil.addCahae(EhcacheUtil.getMyPubCacheConfig(account));
      // String title="cookie失效提醒";
      // String content="淘宝联盟账号["+account+"],已经失效了！";
      // String emails = taoBaoOrderUtil.configService.get("send_email");
      // if(emails != null && !"".equals(emails.trim())){
      // String[] addr = emails.split(",");
      // for (String email : addr) {
      // MailSenderUtil.sendEmail(email, title, content);
      // }
      // }
      // }
      return "";
   }

   /**
    * 上报联盟的Cookie无效
    * 
    * @param account
    */
   public static void reportCookieInvalid(String account) {
      if (1 > 0)
         return;
      HttpClient client = new HttpClient();
      try {
         GetMethod gm = new GetMethod(String.format("http://193.112.35.168:8091/tb/cookie/reportinvalid?account=%s",
               URLEncoder.encode(account, "UTF-8")));
         client.executeMethod(gm);
         String as = gm.getResponseBodyAsString();
         JSONObject root = JSONObject.fromObject(as);
         if (root.optInt("code") == 0) {// 上报成功
            LogHelper.error("上报成功！");
            return;
         }
         LogHelper.error("上报失败！");

      } catch (Exception e) {
         e.printStackTrace();
         LogHelper.error("上报失败！");
      }
   }

   /**
    * 按天数爬取淘宝联盟的返利订单
    * 
    * @param day
    * @return
    */
   public static List<TaoBaoOrder> getOrderList(int day, TaoBaoUnionConfig config) {
      String cookie1 = getTaoBaoCookie(config.getAccount());

      LogHelper.error("账号为：" + config.getAccount() + ",cookie为：" + cookie1);

      String[] cookies = new String[] { cookie1 };
      List<TaoBaoOrder> list = new ArrayList<TaoBaoOrder>();
      int i = 0;
      for (String cookie : cookies) {
         if (com.yeshi.fanli.util.StringUtil.isNullOrEmpty(cookie)) {
            i++;
            continue;
         }
         // 原始为30
         String startTime = TimeUtil.getGernalTime(System.currentTimeMillis() - 1000 * 60 * 60 * 24L * day,
               "yyyy-MM-dd");
         String endTime = TimeUtil.getGernalTime(System.currentTimeMillis(), "yyyy-MM-dd");

         String url = String.format(
               "http://pub.alimama.com/report/getTbkPaymentDetails.json?spm=a219t.7664554.1998457203.85.9pFBtK&queryType=1&payStatus=&DownloadID=DOWNLOAD_REPORT_INCOME_NEW&startTime=%s&endTime=%s",
               startTime, endTime);
         HttpClient client = new HttpClient();
         // client.getHostConfiguration().setProxy("192.168.1.200", 8888);
         GetMethod gm = new GetMethod(url);
         gm.setRequestHeader("Cookie", cookie);
         gm.setRequestHeader("User-Agent",
               "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/51.0.2704.106 Safari/537.36");
         InputStream inputStream = null;
         try {
            client.executeMethod(gm);
            System.out.println(gm.getStatusCode());
            if (gm.getStatusCode() == 200) {
               System.out.println(gm.getResponseHeader("Content-Type").getValue());
               if (!gm.getResponseHeader("Content-Type").getValue().contains("excel")) {
                  if (i == 0) {
                     reportCookieInvalid(config.getAccount());
                  }
                  continue;
               }
               inputStream = gm.getResponseBodyAsStream();
               list.addAll(parseOrder(inputStream));

               // String path =
               // TaoBaoOrderUtil.class.getClassLoader().getResource("/").getPath();
               // String yyyyMMddHHmmss =
               // TimeUtil.yyyyMMddHHmmss(System.currentTimeMillis());
               // FileUtils.copyInputStreamToFile(inputStream, new
               // File(path+"/excel/"+yyyyMMddHHmmss+"."+"xls"));
            }
         } catch (HttpException e) {
            e.printStackTrace();
         } catch (IOException e) {
            e.printStackTrace();
         } finally {
            i++;
            if (inputStream != null) {
               try {
                  inputStream.close();
               } catch (IOException e) {
                  e.printStackTrace();
               }
            }
         }
      }

      return list;
   }

   /**
    * 根据链接下载淘宝订单
    * 
    * @param url
    * @return
    */
   private static List<TaoBaoOrder> downLoadOrderFromTaoBao(String url, TaoBaoUnionConfig config)
         throws TaoBaoOrderException {
      String cookie1 = getTaoBaoCookie(config.getAccount());
      LogHelper.error("账号为：" + config.getAccount() + ",cookie为：" + cookie1);
      String[] cookies = new String[] { cookie1 };
      List<TaoBaoOrder> list = new ArrayList<TaoBaoOrder>();
      int i = 0;
      for (String cookie : cookies) {
         if (com.yeshi.fanli.util.StringUtil.isNullOrEmpty(cookie)) {
            i++;
            continue;
         }
         // 原始为30

         HttpClient client = new HttpClient();
         // client.getHostConfiguration().setProxy("192.168.1.122", 8888);
         GetMethod gm = new GetMethod(url);
         gm.setRequestHeader("Cookie", cookie);
         gm.setRequestHeader("User-Agent",
               "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/51.0.2704.106 Safari/537.36");
         gm.setRequestHeader("Referer", "http://pub.alimama.com/myunion.htm");
         gm.setRequestHeader("Accept",
               "text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8");
         gm.setRequestHeader("Upgrade-Insecure-Requests", "1");
         gm.setRequestHeader("Accept-Encoding", "gzip, deflate");
         gm.setRequestHeader("Accept-Language", "zh-CN,zh;q=0.9");
         InputStream inputStream = null;
         try {
            client.executeMethod(gm);
            System.out.println(gm.getStatusCode());
            if (gm.getStatusCode() == 200) {
               System.out.println(gm.getResponseHeader("Content-Type").getValue());
               if (!gm.getResponseHeader("Content-Type").getValue().contains("excel")) {
                  if (i == 0) {
                     throw new TaoBaoOrderException(gm.getStatusCode(), "返回的内容异常");
                  }
                  continue;
               }
               inputStream = gm.getResponseBodyAsStream();
               list.addAll(parseOrder(inputStream));

               // String path =
               // TaoBaoOrderUtil.class.getClassLoader().getResource("/").getPath();
               // String yyyyMMddHHmmss =
               // TimeUtil.yyyyMMddHHmmss(System.currentTimeMillis());
               // FileUtils.copyInputStreamToFile(inputStream, new
               // File(path+"/excel/"+yyyyMMddHHmmss+"."+"xls"));
            } else {
               throw new TaoBaoOrderException(gm.getStatusCode(), gm.getResponseHeader("Location").getValue());
            }
         } catch (HttpException e) {
            e.printStackTrace();
         } catch (IOException e) {
            e.printStackTrace();
         } finally {
            i++;
            if (inputStream != null) {
               try {
                  inputStream.close();
               } catch (IOException e) {
                  e.printStackTrace();
               }
            }
         }
      }
      return list;
   }

   /**
    * 按时间戳爬取淘宝联盟的返利订单
    * 
    * @param startTime
    * @param endTime
    * @return
    */
   @SuppressWarnings("deprecation")
   public static List<TaoBaoOrder> getOrderList(long startTime, long endTime, TaoBaoUnionConfig config)
         throws TaoBaoOrderException {
      String startTimeStr = TimeUtil.getGernalTime(startTime, "yyyy-MM-dd HH:mm:ss");
      String endTimeStr = TimeUtil.getGernalTime(endTime, "yyyy-MM-dd HH:mm:ss");

      String url = String.format(
            "http://pub.alimama.com/report/getTbkPaymentDetails.json?spm=a219t.7664554.1998457203.10.7a7835d9UJKaiP&queryType=1&payStatus=&DownloadID=DOWNLOAD_REPORT_INCOME_NEW&startTime=%s&endTime=%s",
            URLEncoder.encode(startTimeStr), URLEncoder.encode(endTimeStr));

      return downLoadOrderFromTaoBao(url, config);
   }

   /**
    * 爬取第三方服务商推广订单
    * 
    * @param startTime
    * @param endTime
    * @return
    */
   @SuppressWarnings("deprecation")
   public static List<TaoBaoOrder> getThirdServiceOrderList(long startTime, long endTime, TaoBaoUnionConfig config)
         throws TaoBaoOrderException {
      String startTimeStr = TimeUtil.getGernalTime(startTime, "yyyy-MM-dd HH:mm:ss");
      String endTimeStr = TimeUtil.getGernalTime(endTime, "yyyy-MM-dd HH:mm:ss");
      String url = String.format(
            "http://pub.alimama.com/report/getTbkThirdPaymentDetails.json?spm=a219t.7664554.1998457203.10.7a7835d9UJKaiP&queryType=2&payStatus=&DownloadID=DOWNLOAD_REPORT_TK3_PUB&startTime=%s&endTime=%s",
            URLEncoder.encode(startTimeStr), URLEncoder.encode(endTimeStr));
      return downLoadOrderFromTaoBao(url, config);
   }

   /**
    * 爬取最近30天的订单
    * 
    * @return
    */
   public static List<TaoBaoOrder> getOrderList(TaoBaoUnionConfig config) {
      return getOrderList(30, config);
   }

   public static List<TaoBaoWeiQuanOrder> getWeiQuanOrders(InputStream input) {
      return parseWeiQuanOrder(input);
   }

   private static List<TaoBaoWeiQuanOrder> getWeiQuanOrders(String cookies, long startTime, long endTime) {
      String startTimeStr = TimeUtil.getGernalTime(startTime, "yyyy-MM-dd");
      String endTimeStr = TimeUtil.getGernalTime(endTime, "yyyy-MM-dd");
      String url = String.format(
            "http://pub.alimama.com/report/getNewTbkRefundPaymentDetails.json?spm=a219t.7664554.1998457203.105.49cc35d9eDSunp&refundType=1&searchType=1&DownloadID=DOWNLOAD_EXPORT_CPSPAYMENT_REFUND_OVERVIEW&startTime=%s&endTime=%s",
            startTimeStr, endTimeStr);
      Map<String, String> headers = new HashMap<>();
      headers.put("User-Agent",
            "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/63.0.3239.84 Safari/537.36");
      headers.put("Cookie", cookies);
      headers.put("Referer", "http://pub.alimama.com/myunion.htm");
      headers.put("Upgrade-Insecure-Requests", "1");
      InputStream in = HttpUtil.getAsInputStream(url, headers);

      List<TaoBaoWeiQuanOrder> list = new ArrayList<>();
      list = parseWeiQuanOrder(in);

      list.addAll(getThirdWeiQuanOrders(cookies, startTime, endTime));

      // if (list != null)
      // for (int i = 0; i < list.size(); i++) {
      // if (!list.get(i).getState().equalsIgnoreCase("维权成功")) {
      // list.remove(i);
      // i--;
      // }
      // }
      return list;
   }

   // 第三方订单维权
   private static List<TaoBaoWeiQuanOrder> getThirdWeiQuanOrders(String cookies, long startTime, long endTime) {
      String startTimeStr = TimeUtil.getGernalTime(startTime, "yyyy-MM-dd");
      String endTimeStr = TimeUtil.getGernalTime(endTime, "yyyy-MM-dd");
      String url = String.format(
            "http://pub.alimama.com/report/getNewTbkRefundPaymentDetails.json?spm=a219t.7664554.1998457203.163.158a35d9r0bV0O&refundType=2&searchType=3&DownloadID=DOWNLOAD_EXPORT_CPSPAYMENT_REFUND_OVERVIEW&startTime=%s&endTime=%s",
            startTimeStr, endTimeStr);
      Map<String, String> headers = new HashMap<>();
      headers.put("User-Agent",
            "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/63.0.3239.84 Safari/537.36");
      headers.put("Cookie", cookies);
      headers.put("Referer", "http://pub.alimama.com/myunion.htm");
      headers.put("Upgrade-Insecure-Requests", "1");
      InputStream in = HttpUtil.getAsInputStream(url, headers);
      return parseWeiQuanOrder(in);
   }

   /**
    * 从excel文件解析订单
    * 
    * @param in
    * @return
    */
   public static List<TaoBaoOrder> parseOrder(InputStream in) {
      try {
         jxl.Workbook rwb = Workbook.getWorkbook(in);
         Sheet sheet = rwb.getSheet(0);
         List<TaoBaoOrder> orderRecordList = new ArrayList<TaoBaoOrder>();
         int createtimeColumn = 0;
         int clickTimeColumn = 0;// 点击时间
         int titleColumn = 0;// 商品名称
         int auctionIdColumn = 0;// 商品ID
         int managerWangWangColumn = 0;// 掌柜旺旺
         int shopColumn = 0;// 所属店铺
         int countColumn = 0;// 商品数
         int priceColumn = 0;// 商品单价
         int orderStateColumn = 0; // 订单状态
         int orderTypeColumn = 0; // 订单类型
         int iRatioColumn = 0;// 收入比率
         int sRatioColumn = 0;// 分成比率

         int paymentColumn = 0; // 付款金额
         int estimateColumn = 0;// 效果预估
         int settlementColumn = 0; // 结算金额
         int incomeColumn = 0;// 预估收入
         int settlementTimeColumn = 0;// 结算时间

         int tkRateColumn = 0;// 佣金比率
         int tkMoneyColumn = 0;// 佣金金额

         int technologySupportPercentColumn = 0;// 技术服务费
         int subsidyRatioColumn = 0;// 补贴比率
         int subsidyColumn = 0;// 补贴金额
         int subsidyTypeColumn = 0;// 补贴类型
         int transactionPlatformColumn = 0;// 成交平台
         int thirdServiceColumn = 0;// 第三方服务来源

         int orderIdColumn = 0;// 订单编号
         int classNameColumn = 0; // 分类名称
         int sourceMediaIdColumn = 0; // 媒体ID
         int sourceMediaNameColumn = 0;// 来源媒体名称
         int adPositionIdColumn = 0; // 广告位ID
         int adPositionNameColumn = 0;// 广告位名称

         for (int c = 0; c < sheet.getColumns(); c++) {
            String content = sheet.getCell(c, 0).getContents().trim();
            if ("创建时间".equalsIgnoreCase(content))
               createtimeColumn = c;
            else if ("点击时间".equalsIgnoreCase(content))
               clickTimeColumn = c;
            else if ("商品信息".equalsIgnoreCase(content))
               titleColumn = c;
            else if ("商品ID".equalsIgnoreCase(content))
               auctionIdColumn = c;
            else if ("掌柜旺旺".equalsIgnoreCase(content))
               managerWangWangColumn = c;
            else if ("所属店铺".equalsIgnoreCase(content))
               shopColumn = c;
            else if ("商品数".equalsIgnoreCase(content))
               countColumn = c;
            else if ("商品单价".equalsIgnoreCase(content))
               priceColumn = c;
            else if ("订单状态".equalsIgnoreCase(content))
               orderStateColumn = c;
            else if ("订单类型".equalsIgnoreCase(content))
               orderTypeColumn = c;
            else if ("收入比率".equalsIgnoreCase(content))
               iRatioColumn = c;
            else if ("分成比率".equalsIgnoreCase(content))
               sRatioColumn = c;
            else if ("付款金额".equalsIgnoreCase(content))
               paymentColumn = c;
            else if ("效果预估".equalsIgnoreCase(content))
               estimateColumn = c;
            else if ("结算金额".equalsIgnoreCase(content))
               settlementColumn = c;
            else if ("预估收入".equalsIgnoreCase(content))
               incomeColumn = c;
            else if ("结算时间".equalsIgnoreCase(content))
               settlementTimeColumn = c;
            else if ("佣金比率".equalsIgnoreCase(content))
               tkRateColumn = c;
            else if ("佣金金额".equalsIgnoreCase(content))
               tkMoneyColumn = c;
            else if ("技术服务费比率".equalsIgnoreCase(content))
               technologySupportPercentColumn = c;
            else if ("补贴比率".equalsIgnoreCase(content))
               subsidyRatioColumn = c;
            else if ("补贴金额".equalsIgnoreCase(content))
               subsidyColumn = c;
            else if ("补贴类型".equalsIgnoreCase(content))
               subsidyTypeColumn = c;
            else if ("成交平台".equalsIgnoreCase(content))
               transactionPlatformColumn = c;
            else if ("第三方服务来源".equalsIgnoreCase(content))
               thirdServiceColumn = c;
            else if ("订单编号".equalsIgnoreCase(content))
               orderIdColumn = c;
            else if ("类目名称".equalsIgnoreCase(content))
               classNameColumn = c;
            else if ("来源媒体ID".equalsIgnoreCase(content))
               sourceMediaIdColumn = c;
            else if ("来源媒体名称".equalsIgnoreCase(content))
               sourceMediaNameColumn = c;
            else if ("广告位ID".equalsIgnoreCase(content))
               adPositionIdColumn = c;
            else if ("广告位名称".equalsIgnoreCase(content))
               adPositionNameColumn = c;
         }

         for (int r = 1; r < sheet.getRows(); r++) {
            TaoBaoOrder orderRecord = new TaoBaoOrder();
            for (int c = 0; c < sheet.getColumns(); c++) {
               String content = sheet.getCell(c, r).getContents().trim();
               if (c == createtimeColumn)
                  orderRecord.setCreateTime(content);
               else if (c == clickTimeColumn)
                  orderRecord.setClickTime(content);
               else if (c == titleColumn)
                  orderRecord.setTitle(content);
               else if (c == auctionIdColumn)
                  orderRecord.setAuctionId(content);
               else if (c == managerWangWangColumn)
                  orderRecord.setManagerWangWang(content);
               else if (c == shopColumn)
                  orderRecord.setShop(content);
               else if (c == countColumn)
                  orderRecord.setCount(Integer.parseInt(content));
               else if (c == priceColumn)
                  orderRecord.setPrice(new BigDecimal(content));
               else if (c == orderStateColumn)
                  orderRecord.setOrderState(content);
               else if (c == orderTypeColumn)
                  orderRecord.setOrderType(content);
               else if (c == iRatioColumn)
                  orderRecord.setiRatio(new BigDecimal(content.replace("%", "").trim()));
               else if (c == sRatioColumn)
                  orderRecord.setsRatio(new BigDecimal(content.replace("%", "").trim()));
               else if (c == paymentColumn)
                  orderRecord.setPayment(new BigDecimal(content));
               else if (c == estimateColumn)
                  orderRecord.setEstimate(new BigDecimal(content));
               else if (c == settlementColumn)
                  orderRecord.setSettlement(new BigDecimal(content));
               else if (c == incomeColumn)
                  orderRecord.seteIncome(new BigDecimal(content));
               else if (c == settlementTimeColumn)
                  orderRecord.setSettlementTime(content);
               else if (c == tkRateColumn)
                  orderRecord.setTkRate(new BigDecimal(content.replace("%", "").trim()));
               else if (c == tkMoneyColumn)
                  orderRecord.setTkMoney(new BigDecimal(content));
               else if (c == technologySupportPercentColumn)
                  orderRecord.setTechnologySupportPercent(StringUtil.isNullOrEmpty(content) ? null
                        : new BigDecimal(content.replace("%", "").trim()));
               else if (c == subsidyRatioColumn)
                  orderRecord.setSubsidyRatio(new BigDecimal(content.replace("%", "").trim()));
               else if (c == subsidyColumn)
                  orderRecord.setSubsidy(new BigDecimal(content));
               else if (c == subsidyTypeColumn)
                  orderRecord.setSubsidyType(content);
               else if (c == transactionPlatformColumn)
                  orderRecord.setTransactionPlatform(content);
               else if (c == thirdServiceColumn)
                  orderRecord.setThirdService(content);
               else if (c == orderIdColumn)
                  orderRecord.setOrderId(content);
               else if (c == classNameColumn)
                  orderRecord.setClassName(content);
               else if (c == sourceMediaIdColumn)
                  orderRecord.setSourceMediaId(content);
               else if (c == sourceMediaNameColumn)
                  orderRecord.setSourceMediaName(content);
               else if (c == adPositionIdColumn)
                  orderRecord.setAdPositionId(content);
               else if (c == adPositionNameColumn)
                  orderRecord.setAdPositionName(content);
            }

            if (!StringUtil.isNullOrEmpty(orderRecord.getAuctionId() + ""))
               orderRecordList.add(orderRecord);
         }
         return orderRecordList;
      } catch (Exception e) {
         e.printStackTrace();
      }

      return new ArrayList<TaoBaoOrder>();

   }

   /**
    * 解析维权订单
    * 
    * @param in
    * @return
    */
   public static List<TaoBaoWeiQuanOrder> parseWeiQuanOrder(InputStream in) {
      try {
         jxl.Workbook rwb = Workbook.getWorkbook(in);
         Sheet sheet = rwb.getSheet(0);
         List<TaoBaoWeiQuanOrder> orderRecordList = new ArrayList<TaoBaoWeiQuanOrder>();
         for (int r = 1; r < sheet.getRows(); r++) {
            TaoBaoWeiQuanOrder orderRecord = new TaoBaoWeiQuanOrder();
            for (int c = 0; c < sheet.getColumns(); c++) {
               String content = sheet.getCell(c, r).getContents().trim();
               switch (c) {
               case 0:// 维权时间
                  if (!StringUtil.isNullOrEmpty(content))
                     orderRecord.setWeiQuanTime(
                           new Date(TimeUtil.convertToTimeTemp(content, "yyyy-MM-dd HH:mm:ss")));
                  break;
               case 1:// 维权完成时间
                  if (!StringUtil.isNullOrEmpty(content))
                     orderRecord.setWeiQuanFinishTime(
                           new Date(TimeUtil.convertToTimeTemp(content, "yyyy-MM-dd HH:mm:ss")));
                  break;
               case 2:// 结算时间
                  if (!StringUtil.isNullOrEmpty(content))
                     orderRecord.setJieSuanTime(
                           new Date(TimeUtil.convertToTimeTemp(content, "yyyy-MM-dd HH:mm:ss")));
                  break;
               case 3:// 商品名称
                  orderRecord.setGoodsName(content);
                  break;
               case 5:// 淘宝订单号
                  orderRecord.setOrderId(content);
                  break;
               case 6:// 淘宝子订单号
                  orderRecord.setOrderItemId(content);
                  break;
               case 7:// 维权状态
                  orderRecord.setState(content);
                  break;
               case 8:// 结算金额

                  break;
               case 9:// 退款金额
                  orderRecord.setMoney(StringUtil.isNullOrEmpty(content) ? null : new BigDecimal(content));
                  break;

               case 10:// 返还商家金额
                  orderRecord.setFanMoney(StringUtil.isNullOrEmpty(content) ? null : new BigDecimal(content));
                  break;
               }
            }
            if (!StringUtil.isNullOrEmpty(orderRecord.getOrderId()))
               orderRecordList.add(orderRecord);
         }
         return orderRecordList;
      } catch (Exception e) {
         e.printStackTrace();
      }
      return new ArrayList<TaoBaoWeiQuanOrder>();
   }

   public static OrderItem convert(TaoBaoOrder tbo, Order order, BigDecimal rate, List<String> tbImgList) {
      OrderItem orderItem = new OrderItem();
      orderItem.setFanTime(0L);
      orderItem.setIsGift(false);
      orderItem.setRefundTime(0L);
      orderItem.setSettlementTime(0L);
      String createTime = tbo.getCreateTime();
      orderItem.setCreateTime(TimeUtil.convertDateToTemp2(createTime));
      if ("订单结算".equalsIgnoreCase(tbo.getOrderState()))
         orderItem.setFanMoney(MoneyBigDecimalUtil.mul(rate.divide(BigDecimal.valueOf(100)), tbo.geteIncome()));
      else
         orderItem.setFanMoney(MoneyBigDecimalUtil.mul(rate.divide(BigDecimal.valueOf(100)), tbo.getEstimate()));
      if ("订单结算".equalsIgnoreCase(tbo.getOrderState()))
         orderItem.setPayMoney(tbo.getSettlement());
      else
         orderItem.setPayMoney(tbo.getPayment());

      orderItem.setOrder(order);
      orderItem.setOrderId(tbo.getOrderId());

      if (tbImgList != null && tbImgList.size() > 0) {
         orderItem.setPicture(tbImgList.get(0));
      } else {
         orderItem.setPicture("");
      }
      if (ORDERFAILURE.equals(tbo.getOrderState())) {
         orderItem.setState(-1);
      } else if (ORDERPAYMENT.equals(tbo.getOrderState())) {
         orderItem.setState(1);
      } else if (ORDERSETTLEMENT.equals(tbo.getOrderState())) {
         orderItem.setState(2);
         orderItem.setSettlementTime(TimeUtil.convertDateToTemp2(tbo.getSettlementTime()));
      } else if (ORDERSUCCESS.equals(tbo.getOrderState())) // 其他值
      {
         orderItem.setState(2);
         orderItem.setSettlementTime(TimeUtil.convertDateToTemp2(tbo.getSettlementTime()));
      }
      orderItem.setTitle(tbo.getTitle());
      orderItem.setType(1);
      UserInfo userInfo = order.getUserInfo();
      orderItem.setUserInfo(userInfo);
      orderItem.setAuctionId(tbo.getAuctionId());
      orderItem.setVersion(order.getVersion());
      orderItem.setPrePayMoney(new BigDecimal(tbo.getCount()).multiply(tbo.getPrice()));
      // orderItem.setGoodsPrice(tbo.getPrice());
      // orderItem.setGoodsNum(tbo.getCount());
      return orderItem;
   }

   /**
    * 将淘宝订单按照订单号归类
    * 
    * @param orderList
    * @return
    */
   public static Map<String, List<TaoBaoOrder>> classifyTaoBaoOrderByOrderId(List<TaoBaoOrder> orderList) {
      Map<String, List<TaoBaoOrder>> map = new ConcurrentHashMap<>();
      for (TaoBaoOrder order : orderList) {
         if (map.get(order.getOrderId()) == null)
            map.put(order.getOrderId(), new ArrayList<>());
         // 设置排序值，从1开始
         order.setOrderBy(map.get(order.getOrderId()).size() + 1);
         map.get(order.getOrderId()).add(order);
      }

      // 订单排序值灰度测试
      try {
         for (Iterator<String> its = map.keySet().iterator(); its.hasNext();) {
            String orderId = its.next();
            try {
               setOrderBy(map.get(orderId));
            } catch (Exception e) {
//               LogHelper.errorDetailInfo(e, "订单号:" + orderId, "订单排序值灰度测试");
            }
         }
      } catch (Exception e) {
      }

      return map;
   }

   public static void setOrderBy(List<TaoBaoOrder> orderList) {// 提取一样的
      if (orderList == null || orderList.size() == 1)
         return;
      int len = orderList.get(0).getTradeId().length();
      int startIndex = 0;
      for (int i = 0; i < len; i++) {
         boolean isSame = true;
         for (int j = 0; j < orderList.size() - 1; j++) {
            if (orderList.get(j).getTradeId().charAt(i) != orderList.get(j + 1).getTradeId().charAt(i)) {
               isSame = false;
               break;
            }
         }
         if (!isSame) {
            startIndex = i;
            break;
         }
      }
      int endIndex = 0;

      for (int i = len - 1; i >= 0; i--) {
         boolean isSame = true;
         for (int j = 0; j < orderList.size() - 1; j++) {
            if (orderList.get(j).getTradeId().charAt(i) != orderList.get(j + 1).getTradeId().charAt(i)) {
               isSame = false;
               break;
            }
         }
         if (!isSame) {
            endIndex = i;
            break;
         }
      }
      for (TaoBaoOrder taoBaoOrder : orderList) {
         String tradeId = taoBaoOrder.getTradeId();
         String orderByStr = tradeId.substring(startIndex, endIndex + 1);
         if (orderByStr.length() > 8) {
            LogHelper.error(taoBaoOrder.getOrderId() + "-订单的orderBy超过8位:" + orderByStr);
         }
         int orderBy = Integer.parseInt(orderByStr);
         // taoBaoOrder.setOrderBy(orderBy);
      }
   }

   /**
    * 计算邀请赚的返利时间
    * 
    * @param balanceTime
    * @return
    */
   public static long computeInviteFanLiTime(long balanceTime) {
      int month = Integer.parseInt(TimeUtil.getGernalTime(balanceTime, "M"));
      int year = Integer.parseInt(TimeUtil.getGernalTime(balanceTime, "yyyy"));
      String time = "";
      if (month == 12) {
         time = (year + 1) + "-1";
      } else {
         time = year + "-" + (month + 1);
      }
      time += "-25";
      // 设为25日凌晨到账
      time += " 00:00:00";
      // TimeUtil.getGernalTime(balanceTime, "HH:mm:ss");
      return TimeUtil.convertToTimeTemp(time, "yyyy-M-dd HH:mm:ss");
   }

   /**
    * 获取一组订单的hash值
    * 
    * @param list
    * @return
    */
   public static String getOrderDataHashCode(List<TaoBaoOrder> list) {
      List<TaoBaoOrder> orderList = new ArrayList<>();
      orderList.addAll(list);
      Comparator<TaoBaoOrder> orderCM = new Comparator<TaoBaoOrder>() {

         @Override
         public int compare(TaoBaoOrder o1, TaoBaoOrder o2) {
            return Long.parseLong(o1.getTradeId()) - Long.parseLong(o2.getTradeId()) > 0 ? 1 : -1;
         }
      };
      Collections.sort(orderList, orderCM);
      String sts = "";
      for (TaoBaoOrder order : orderList) {
         sts += (order.getTradeId() + "-" + order.getSpecialId() + "-" + order.getRelationId());
      }
      return StringUtil.Md5(sts);
   }
}