uas-eis/src/main/java/com/uas/eis/task/TaoBaoDownLoadTask.java

package com.uas.eis.task;

import com.taobao.api.DefaultTaobaoClient;
import com.taobao.api.TaobaoClient;
import com.taobao.api.domain.Order;
import com.taobao.api.domain.Trade;
import com.taobao.api.request.TopOaidDecryptRequest;
import com.taobao.api.request.TradesSoldGetRequest;
import com.taobao.api.response.TopOaidDecryptResponse;
import com.taobao.api.response.TradesSoldGetResponse;
import com.uas.eis.core.config.TaoBaoConfig;
import com.uas.eis.dao.BaseDao;
import com.uas.eis.utils.CollectionUtil;
import com.uas.eis.utils.DateUtil;
import com.uas.eis.utils.StringUtil;
import oracle.sql.DATE;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;


import java.util.*;


@Component
public class TaoBaoDownLoadTask {

    public static Logger logger = Logger.getLogger(TaoBaoDownLoadTask.class);

    @Autowired
    private TaoBaoConfig taoBaoConfig;
    @Autowired
    private BaseDao baseDao;
    /**
     * 下载订单
     * */
    @Scheduled(cron = "0 0/10 * * * ?")
    public void DownLoadOrder(){

        /**
         * 有效店铺
         * */
        List<Map<String,Object>> shopList = baseDao.queryForList("SELECT TB_ID,TB_CODE,TB_LASTSYNCDATE,TB_OPENUID,TB_NICK from TBAUTHORIZATION where validflag=1 ORDER BY TB_LASTSYNCDATE ASC");
        logger.info("Start downLoad SaleOrder---");
        final boolean[] downLoadFlag = {false};
        TaobaoClient client = new DefaultTaobaoClient(taoBaoConfig.getServerUrl(), taoBaoConfig.getAppKey(), taoBaoConfig.getAppSecret());
        TradesSoldGetRequest req = new TradesSoldGetRequest();
        req.setFields("total_results,has_next,trades,seller_nick,pic_path,payment,seller_rate,post_fee,receiver_name,receiver_state,receiver_address,receiver_zip,receiver_mobile"+
                ",receiver_phone,consign_time,received_payment,receiver_country,receiver_town,order_tax_fee,shop_pick,tid,num,num_iid,status,title,type,price,discount_fee"+
                ",total_fee,created,pay_time,modified,end_time,seller_flag,buyer_nick,has_buyer_message,credit_card_fee,step_trade_status,step_paid_fee,mark_desc,shipping_type"+
                ",adjust_fee,trade_from,service_orders,oid,item_oid,service_id,service_detail_url,num,price,payment,title,total_fee,buyer_nick,refund_id,seller_nick,pic_path"+
                ",tmser_spu_code,oid_str,buyer_rate,receiver_city,receiver_district,o2o,o2o_guide_id,o2o_delivery,orders,item_meal_name,pic_path,seller_nick,buyer_nick,refund_status,outer_iid,snapshot_url,snapshot,timeout_action_time,buyer_rate,seller_rate"+
                ",seller_type,cid,sub_order_tax_fee,sub_order_tax_rate,oid,status,title,item_oid,type,sku_properties_name,order_attr,shipping_type"+
                ",md_qualification,customization,inv_type,shipper,f_type,f_status,f_term,assembly_rela,cl_down_payment,cl_month_payment,cl_tail_payment"+
                ",cl_installment_num,cal_penalty,cl_service_fee,cl_car_taker,cl_car_taker_phone,cl_car_taker_i_d_num,cl_car_taker_id_num,down_payment"+
                ",down_payment_ratio,month_payment,tail_payment,installment_num,penalty,service_fee,oid_str,nr_reduce_inv_fail,sort_info"+
                ",rt_omni_outer_sc_id,rt_omni_sc_id,modify_address,ti_modify_address_time,omni_jxs_outerid,propoint,brand_light_shop_source,special_refund_type"+
                ",extend_info,service_type,threepl_timing,is_o2o_passport,delivery_time,sign_time");
        shopList.stream().forEach(shop -> {
            String tbOpenuid = String.valueOf(shop.get("TB_OPENUID"));
            String TB_NICK = String.valueOf(shop.get("TB_NICK"));
            List<String> executeSQLS = new ArrayList<>();
            String sessionKey = String.valueOf(shop.get("TB_CODE"));
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(DateUtil.parse(String.valueOf(shop.get("TB_LASTSYNCDATE")),"yyyy-MM-dd HH:mm:ss"));
            calendar.add(Calendar.DATE, -1);
            //若超过三个月则结束日期= 上次对接时间+3个月
            Calendar calendarEnd = Calendar.getInstance();
            calendarEnd.setTime(DateUtil.parse(String.valueOf(shop.get("TB_LASTSYNCDATE")), "yyyy-MM-dd HH:mm:ss"));
            calendarEnd.add(Calendar.MONTH, 3);
            Date endDate = new Date();
            if (calendarEnd.getTime().getTime() < endDate.getTime()) {
                endDate = calendarEnd.getTime();
            }
            req.setStartCreated(calendar.getTime());
            req.setEndCreated(endDate);
            //本地调试用
            /*
                Calendar calendartest1 = Calendar.getInstance();
                calendartest1.setTime(DateUtil.parse("2025-05-05 00:00:00","yyyy-MM-dd HH:mm:ss"));
                Calendar calendartest2 = Calendar.getInstance();
                calendartest2.setTime(DateUtil.parse("2025-05-06 00:00:00","yyyy-MM-dd HH:mm:ss"));
                req.setStartCreated(calendartest1.getTime());
                req.setEndCreated(calendartest2.getTime());
            */
            req.setUseHasNext(true);
            List<Trade> trades = new ArrayList<>();
            try {
                TradesSoldGetResponse rsp = client.execute(req, sessionKey);
                if(rsp.getMsg()!=null&&"Invalid session".equals(rsp.getMsg())){
                    baseDao.execute("update TBAUTHORIZATION set validflag=0,unvalidtime=sysdate, UNVALIDREMARK='"+ "("+StringUtil.nvl(rsp.getSubCode(),rsp.getErrorCode())+")"+StringUtil.nvl(rsp.getSubMsg(),rsp.getMsg()) +"' where tb_id = "+shop.get("TB_ID"));
                }else {
                    if(!CollectionUtil.isEmpty(rsp.getTrades())){
                        Long pageCount = 1L;
                        getNextTBOrder(pageCount,trades,rsp,req,client,sessionKey);
                        logger.info(TB_NICK+"("+tbOpenuid+")店铺获取单数"+trades.size());
                        for(Trade trade: trades){
                            formatTrade(trade, sessionKey,tbOpenuid, executeSQLS);
                        }
                        if(executeSQLS.size()>0){
                            downLoadFlag[0] = true;
                            System.out.println("执行SQL条数："+executeSQLS.size());
//                            System.out.println("SQL："+executeSQLS.toString());
                            baseDao.execute(executeSQLS);
                        }
                        //更新当前店铺的最近更新时间
                    baseDao.updateByCondition("TBAUTHORIZATION","TB_LASTSYNCDATE=to_date ( '" + DateUtil.format(endDate, DateUtil.FORMAT_YMD_HMS) + "','YYYY-MM-DD hh24:mi:ss') ","TB_CODE ='"+sessionKey+"'");
                    }
                  }
            } catch (Exception e) {
                e.printStackTrace();
                logger.info("淘宝未响应并返回订单数据");
            }
        });
        //部分信息处理
        if(downLoadFlag[0]){
            baseDao.callProcedure("SP_UPDATE_SALEINFO");
        }
    }
    private void getNextTBOrder(Long pageNo,List<Trade> trades,TradesSoldGetResponse rsp,TradesSoldGetRequest req,TaobaoClient client,String sessionKey ){
        if(!CollectionUtil.isEmpty(rsp.getTrades())){
            trades.addAll(rsp.getTrades());
            if(rsp.getHasNext()!=null&&rsp.getHasNext()){
                Long pageSize = 40L;
                pageNo = pageNo + 1;
                req.setPageNo(pageNo);
                req.setPageSize(pageSize);
                try {
                    TradesSoldGetResponse rsp1 = client.execute(req, sessionKey);
                    getNextTBOrder(pageNo,trades,rsp1,req,client,sessionKey);
                } catch (Exception e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }
    private  void formatTrade(Trade trade ,String sessionKey,String tbOpenuid, List<String> executeSqls){
        String oaid=trade.getOaid();
        String tid=trade.getTid().toString();
        boolean isEXISTS = baseDao.checkIf("SALE", "SA_CODE='"+tid+"'");
        if(isEXISTS) {
            return;
        }
        logger.info("订单信息：单号{"+trade.getTid()+"}，状态{"+trade.getStatus()+"}，店铺{"+trade.getSellerNick()+"}");
        TopOaidDecryptRequest req= new TopOaidDecryptRequest();
        List<TopOaidDecryptRequest.ReceiverQuery> queryList = new ArrayList<TopOaidDecryptRequest.ReceiverQuery>();
        TopOaidDecryptRequest.ReceiverQuery receiverQuery = new TopOaidDecryptRequest.ReceiverQuery();
          receiverQuery.setOaid(oaid);
          receiverQuery.setTid(tid);
          receiverQuery.setScene("1001");
          queryList.add(receiverQuery);
        req.setQueryList(queryList);

        TaobaoClient client =  new DefaultTaobaoClient(taoBaoConfig.getServerUrl(), taoBaoConfig.getAppKey(), taoBaoConfig.getAppSecret());
        String name_oaid=trimNull(trade.getReceiverName() );
        String receiver_mobile=trimNull(trade.getReceiverMobile() );
        String address=trimNull(trade.getReceiverState() ) + trimNull(trade.getReceiverCity() ) + trimNull(trade.getReceiverDistrict() ) +  trimNull(trade.getReceiverAddress() );
        if(oaid!=null) {
            try {
                TopOaidDecryptResponse rep = client.execute(req, sessionKey);
                name_oaid = rep.getReceiverList().get(0).getName();
                receiver_mobile=rep.getReceiverList().get(0).getMobile();
                address=trimNull(rep.getReceiverList().get(0).getState() ) + trimNull(rep.getReceiverList().get(0).getCity() ) + trimNull(rep.getReceiverList().get(0).getDistrict() ) +  trimNull( rep.getReceiverList().get(0).getAddressDetail() );
            } catch (Exception e) {
                logger.info("淘宝返回敏感信息失败,"+e.getMessage());
            }
        }
        name_oaid=name_oaid.replaceAll("'","");
        String modified = DateUtil.format((trade.getModified() == null ? trade.getCreated() : trade.getModified()), DateUtil.FORMAT_YMD_HMS);
        String end_time = DateUtil.format((trade.getEndTime() == null ? trade.getCreated() : trade.getEndTime()), DateUtil.FORMAT_YMD_HMS);
        String consigntime = DateUtil.format((trade.getConsignTime() == null ? trade.getCreated() : trade.getConsignTime()), DateUtil.FORMAT_YMD_HMS);
        if(!"TRADE_NO_CREATE_PAY".equals(trade.getStatus())
                &&!"WAIT_BUYER_PAY".equals(trade.getStatus())
                &&!"PAY_PENDING".equals(trade.getStatus())
                &&!"WAIT_BUYER_CONFIRM_GOODS".equals(trade.getStatus())
                &&!"TRADE_CLOSED_BY_TAOBAO".equals(trade.getStatus())
                &&!"TRADE_CLOSED".equals(trade.getStatus())
                &&!"TRADE_FINISHED".equals(trade.getStatus())) {
                Long said = baseDao.queryForObject("select sale_seq.nextval from dual", Long.class);
                executeSqls.add("INSERT INTO SALESECRETINFO(SAID, NAME,MOBILE, PLACE,SACODE) SELECT "+said+",pkg_security.encrypt_data('"+taoBaoConfig.getPublicKey()+"','"+name_oaid+"')," +
                        "pkg_security.encrypt_data('"+taoBaoConfig.getPublicKey()+"','"+receiver_mobile+"'), pkg_security.encrypt_data('"+taoBaoConfig.getPublicKey()+"','"+address+"'),'" + tid + "' FROM DUAL");
                String INSERTSALESql = "insert into sale( SA_NEEDUPDATE,SA_DISCOUNTFEE,SA_TRADESTATUS, sa_sellernick,sa_custcode,sa_paymentscode" +
                        ",SA_TAOBAOID,sa_payments,sa_custname,sa_kind,sa_wwh_user,sa_need15" +
                        " ,sa_id,sa_tradeid,sa_code,sa_date,sa_recorder,sa_currency,sa_rate" +
                        ",sa_apcustname,sa_need9,sa_toplace" +
                        ",sa_transport,sa_seller,sa_updatedate" +
                        ",sa_enddate,sa_statuscode,sa_status,sa_fare,sa_total" +
                        ",sa_recamount,sa_recorddate,sa_printstatuscode,sa_remark,sa_tbopenuid)" +
                        "  SELECT   1,'" + trade.getDiscountFee() + "','" + trimNull(trade.getStatus()) + "',trim(nvl('" + trimNull(trade.getSellerNick()) + "','静夜思solong')),'GN0008358','P001'" +
                        ", " + tid + " ,'现结','HLK淘宝','国内标准销售订单','" + trimNull(name_oaid) + "','" + trimNull(receiver_mobile) + "'" +
                        "   , " + said + "," + tid + ",'" + tid + "', to_date ('" + end_time + "','YYYY-MM-DD hh24:mi:ss') ,'管理员','RMB','1'" +
                        ",'"+ trimNull(name_oaid) + "','" + trimNull(trade.getBuyerNick()) + "','" + address.replaceAll("'", "") + "'" +
                        ",'"+ trimNull(trade.getShippingType()) + "','" + trimNull(trade.getO2oGuideName()) + "', to_date ('" + modified + "','YYYY-MM-DD hh24:mi:ss') " +
                        ", to_date ('" + end_time + "','YYYY-MM-DD hh24:mi:ss') ,'ENTERING','在录入','" + trimNull(trade.getPostFee()) + "','" + trimNull(trade.getPayment()) + "'" +
                        ",'"+ trimNull(trade.getPayment()) + "', to_date ('" + consigntime + "','YYYY-MM-DD hh24:mi:ss') ,'unprint','淘宝自动抓取','"+tbOpenuid+"' FROM DUAL  " +
                        " WHERE NOT EXISTS ( select 1 from sale WHERE SA_TAOBAOID=" + tid + "   )  ";

                executeSqls.add(INSERTSALESql);
                List<Order> orders = trade.getOrders();
                for (int j = 0; j < orders.size(); j++) {
                    String INSERTSALEDETAILSql = "insert into saledetail(SD_PROPERTIES,sd_remark,sd_id,sd_said,sd_code,sd_detno,sd_prodcode,sd_qty,sd_price,sd_total" +
                            ",sd_statuscode,sd_status,sd_discount,SD_DIVIDE_ORDER_FEE,SD_PART_MJZ_DISCOUNT,SD_OID)" +
                            "  SELECT '" + trimNull(orders.get(j).getSkuPropertiesName()) + "','" + orders.get(j).getTitle() + "', saledetail_seq.nextval ," + said + "  ,'" + tid + "'," + (j+1) + ",'" + trimNull(orders.get(j).getOuterIid()) + "'," + orders.get(j).getNum() + "," + orders.get(j).getPrice() + " ," + orders.get(j).getPayment() +
                            ",'ENTERING','在录入', " + orders.get(j).getDiscountFee() + "," + orders.get(j).getDivideOrderFee() + "," + orders.get(j).getPartMjzDiscount() +","+orders.get(j).getOid()
                            + " FROM DUAL WHERE NOT EXISTS ( select sd_id from saledetail WHERE SD_CODE = '" + tid + "'  and sd_detno = " + (j+1) + " )  ";
                    executeSqls.add(INSERTSALEDETAILSql);
                }
        }
    }

    public  String trimNull(String str){
        if("".equals(str)||str==null||"null".equals(str)){
            return "";
        }else{
            return str.trim();
        }
    }
}