# -*- coding:utf-8 -*-  
# import win32com.client
import os, sys, string
import sys
import time

import psycopg2
import zipfile
import json
import hashlib
import datetime
import requests


class HandData():
    # 初始化
    def __init__(self, is_test=True, cn=None, log=True, step=100):
        self.log = log
        # 结构：[ ['旧标','新标','标签类型','主箱标] ]
        self.codes_replace = []
        self.boxlabels = []
        self.err_codes = []
        self.boxes = {
            'relations': {'totals': [], 'errors': [], 'oks': []},
            'delivery': {'totals': [], 'errors': [], 'oks': [], 'boxes_out': []},
            'replace': {'totals': [], 'errors': [], 'oks': []},
        }
        # 发送对应关系数据时，汉得接口一次可接收最大箱数阀值；发货数据，箱数汉得没限制
        self.step = step
        # 汉得帐号密
        self.clientid = 'api-tj'
        self.pwd = '#v7gc^sj7ben6*_'
        # 内网取数地址，随机字符
        self.url_internal = 'http://www.china315net.com:35088/get/codes/from/wuliu/1/'
        self.sK_internal = '$internal&GLker5w3jtQLx#'
        # 数据库连接，如果为空，使用django默认连接
        if cn:
            cn.autocommit = True
        else:
            cn = connection
        self.cn = cn    

        # 汉得测试环境
        if is_test:
            self.url_auth = 'https://winesmptest.cofco.com/linkcrm1/action/base/user/apilogin'                             
            self.url_relations = 'https://winesmptest.cofco.com/linkcrm1/trml/link/prodCode/upsertByTJ'
            self.url_delivery = 'https://winesmptest.cofco.com/linkcrm1/trml/link/specOrderTmp/upsertByTJ'
            self.url_replace = 'https://winesmptest.cofco.com/linkcrm1/trml/link/prodCode/changeCodeByTJ'
        # 汉得生产环境
        else:
            self.url_auth = 'https://winesmp.cofco.com/prod/action/base/user/apilogin'
            self.url_relations = 'https://winesmp.cofco.com/prod/trml/link/prodCode/upsertByTJ'
            self.url_delivery = 'https://winesmp.cofco.com/prod/trml/link/specOrderTmp/upsertByTJ'
            self.url_replace = 'https://winesmp.cofco.com/prod/trml/link/prodCode/changeCodeByTJ'

        self.tmp = 'tmp'
        self.bt_c = 'bottles_c'
        self.bt_p = 'bottles_p'
        self.bx = 'boxes'
        self.fw = 'fw'
        tmp = self.tmp
        bt_c = self.bt_c
        bt_p = self.bt_p
        bx = self.bx
        fw = self.fw
        cur = self.cn.cursor()
        s = "CREATE TEMP TABLE " + tmp + "(" + \
            "id serial," + \
            "stk_id integer," + \
            "label character varying(20)," + \
            "label2 character varying(20)," + \
            "label_now character varying(20)," + \
            "label_cnt character varying(20)," + \
            "label_new character varying(20)," + \
            "label2_new character varying(20)," + \
            "tb character varying(20)," + \
            "rg character varying(10)," + \
            "label_pk_id bigint," + \
            "product_id integer," + \
            "cnt integer," + \
            "cartons jsonb," + \
            "bottles jsonb " + \
            ")"
        cur.execute(s)
        # 瓶标表 取瓶盖码
        s = "CREATE TEMP TABLE " + bt_c + "(" + \
            "stk_id integer," + \
            "id serial," + \
            "label character varying(20)," + \
            "code  character varying(20)," + \
            "label_new character varying(20)," \
            "count integer" + \
            ")"
        cur.execute(s)
        # 瓶标表 小圆标
        s = "CREATE TEMP TABLE " + bt_p + "(" + \
            "stk_id integer," + \
            "id integer," + \
            "label  character varying(20)," + \
            "code  character varying(20)" + \
            ")"
        cur.execute(s)

    # 取汉得TOKEN
    def get_token(self):
        url = self.url_auth
        data = {
            "clientId": self.clientid,
            "password": self.pwd
        }
        r = requests.post(url, data)
        if r.status_code == 200:
            d = r.json()
            if d['success']:
                tk = d['result']
        else:
            tk = ''
        return tk

    # 向汉得发送数据
    def post_data(self, url, tk, data):
        headers = {
            "Authorization": "bearer " + tk,
            "Content-type": 'application/json;charset=UTF-8'
        }
        return requests.post(url, headers=headers, json=data)

    def post_relations(self, codes, batch, product, customer={}, dataType='0', repead=False):
        self.boxes = {
            'relations': {'totals': [], 'errors': [], 'oks': []},
            'delivery': {'totals': [], 'errors': [], 'oks': []},
            'replace': {'totals': [], 'errors': [], 'oks': []},
        }
        tmp = self.tmp
        bt_c = self.bt_c
        bt_p = self.bt_p
        bx = self.bx
        fw = self.fw
        setp = self.step
        cur = self.cn.cursor()
        cnt = str(len(codes))
        cur.execute("truncate table " + tmp)
        s = "INSERT INTO log.sync_inventory_batch(company_id,vendor,url,batch_id,vtype_id,batch_code,cnt_total,product,customer) values " + \
            "(1,'hand','" + self.url_relations + "'," + str(batch['id']) + ",1,'" + batch[
                'code'] + "'," + cnt + ",'" + json.dumps(product) + "','" + json.dumps(customer) + "') " + \
            "returning id"
        cur.execute(s)
        r = cur.fetchone()
        log_id = r[0]
        # 写入箱码临时表
        a_codes = []
        for c in codes:
            # 2021-12-12 传过来的标签号码，有替换记录的已经取了替换前的号码
            a_codes.append("(" + str(c[0]) + ",'" + c[1] + "'," + str(c[2]) + ")")
        s = "insert into " + tmp + "(stk_id,label,label_pk_id) values " + ",".join(a_codes)
        cur.execute(s)
        # 更新表名，号段
        s = "update " + tmp + " set tb='label.'||coalesce(" + \
            "(select tbname from label.lbl_tbcross where segment=left(label,4)),'t'||left(label,4))," + \
            "rg=left(label,4)"
        cur.execute(s)
        cur.execute("select tb from " + tmp + " group by tb")
        rs = cur.fetchall()
        for r in rs:
            # stkdetails表内label_pk_id可能是替换前也可能是替换后的，要重新取主箱标ID
            s = "update " + tmp + " t set " + \
                "label_pk_id=(select coalesce(parent_id,id) from " + r[0] + " where code=t.label) " + \
                "where tb='" + r[0] + "'"
            cur.execute(s)
            # 处理箱码
            s = "update " + tmp + " t set " + \
                "label = (select code from " + r[0] + " where id=t.label_pk_id order by id limit 1)," + \
                "label2 = (select code from " + r[
                    0] + " where parent_id=t.label_pk_id and package_id=3 order by id limit 1)," + \
                "label_cnt = (select count(*) from " + r[0] + " where parent_id=t.label_pk_id and package_id=3 )," + \
                "label_new = (select code from label.lbl_replace_details where code0=t.label order by id desc limit 1)," + \
                "label2_new = (select code from label.lbl_replace_details where code0=t.label2 order by id desc limit 1), " + \
                "product_id = (select product_id from logistics.inventory_stkdetails  where label_pk_id  = t.label_pk_id and batch_id = " + str(
                batch["id"]) + " limit 1)  " + \
                "where tb='" + r[0] + "'; "

            cur.execute(s)
            # 处理瓶码
            s = "insert into " + bt_c + "(stk_id,label,label_new) select s.stk_id,t.code," + \
                "(select code from label.lbl_replace_details where code0=t.code order by id desc limit 1) " + \
                "from " + \
                "(select stk_id,label_pk_id from " + tmp + " where tb='" + r[0] + "') s," + r[0] + " t " + \
                "where t.parent_id=s.label_pk_id and t.package_id=1"
            cur.execute(s)

            # 瓶标表 取瓶盖码
        s = "update " + bt_c + " b set code=" + \
            "coalesce((select label from label.bottlecap where bottle=b.label order by id desc limit 1)," + \
            "(select label from logistics.z_bottlecap_binding where bottle=b.label order by id desc limit 1))"
        cur.execute(s)
        # 2021-12-13 没取到盖码，看是不是有替换记录
        s = "update " + bt_c + " b set code=" + \
            "coalesce((select label from label.bottlecap where bottle=b.label order by id desc limit 1)," + \
            "(select label from logistics.z_bottlecap_binding where bottle=b.label order by id desc limit 1)) " + \
            "where code is null and b.label is not null"
        cur.execute(s)
        # 取瓶标小圆标活动码
        d = []
        cur.execute("select id,label from " + bt_c + " where code is null")
        for r in cur.fetchall():
            d.append({'id': r[0], 'label': r[1]})
        if d:
            t = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            m2 = hashlib.md5((self.sK_internal + t).encode('utf8'))
            key = m2.hexdigest()
            data = {'key': key, 'time': t, 'labels': d}
            r = requests.post(self.url_internal + '/promotion/', json={'key': key, 'time': t, 'labels': d})
            d = json.loads(r.text)
            a = []
            for c in d['data']:
                a.append("(" + str(c['id']) + ",'" + c['label'] + "','" + c['code'] + "'," + \
                         "(select stk_id from " + bt_c + " where id=" + str(c['id']) + ") )")
            cur.execute("insert into " + bt_p + "(id,label,code,stk_id) values " + ",".join(a))

        # 将瓶盖码为空的数据替换为活动码
        s = "update " + bt_c + " c set code=coalesce(code,(select code from " + bt_p + " where id=c.id))"
        cur.execute(s)

        # 空盖码生成虚拟码补全
        s = "select label,code,stk_id,(select label_pk_id from " + tmp + " where stk_id = a.stk_id) from " + bt_c + " a where code is null or code = ''";
        cur.execute(s)
        for i in cur.fetchall():
            # 查看是否已经有补全记录
            s = "select label from logistics.z_bottle_virtual where bottle = '" + str(
                i[0]) + "' order by id desc limit 1"
            cur.execute(s)
            nc = cur.fetchone()
            if nc:
                new_cap = nc[0]
                us = "update " + bt_c + " set code = '" + str(new_cap) + "' where label = '" + str(i[0]) + "'"
                cur.execute(us)
            else:
                s = 'SELECT label  from logistics.z_bottle_virtual ORDER BY id desc limit 1'
                cur.execute(s)
                sd = cur.fetchone()
                new_cap = "x" + str(int(sd[0].split('x')[1]) + 1).zfill(11)
                us = "update " + bt_c + " set code = '" + str(new_cap) + "' where label = '" + str(i[0]) + "'"
                cur.execute(us)
                sin = "insert into logistics.z_bottle_virtual (label,bottle,box,label_pk_id) values('" + str(
                    new_cap) + "','" + str(i[0]) + "',(select label from " + tmp + " where stk_id = " + str(
                    i[2]) + "),(select label_pk_id from " + tmp + " where stk_id = " + str(i[2]) + "))"
                cur.execute(sin)

        # 如果有重复盖码
        if repead:
            s = "SELECT * FROM (select label,code,stk_id from " + bt_c + " )a left outer join (SELECT count(*)ct,label " + \
                "from logistics.z_bottlecap_binding group by label )b on a.code = b.label where b.ct is not null"
            cur.execute(s)
            for i in cur.fetchall():
                if i[3] > 1:
                    # 查看是否已经有补全记录
                    s = "select label from logistics.z_bottle_virtual where bottle = '" + str(
                        i[0]) + "' order by id desc limit 1"
                    cur.execute(s)
                    nc = cur.fetchone()
                    if nc:
                        new_cap = nc[0]
                        us = "update " + bt_c + " set code = '" + str(new_cap) + "' where label = '" + str(i[0]) + "'"
                        cur.execute(us)
                    else:
                        s = 'SELECT label  from logistics.z_bottle_virtual ORDER BY id desc limit 1'
                        cur.execute(s)
                        sd = cur.fetchone()
                        new_cap = "x" + str(int(sd[0].split('x')[1]) + 1).zfill(11)
                        us = "update " + bt_c + " set code = '" + str(new_cap) + "'   where label = '" + str(i[0]) + "'"
                        cur.execute(us)
                        sin = "insert into logistics.z_bottle_virtual (label,bottle,box,label_pk_id) values('" + str(
                            new_cap) + "','" + str(i[0]) + "',(select label from " + tmp + " where stk_id = " + str(
                            i[2]) + "),(select label_pk_id from " + tmp + " where stk_id = " + str(i[2]) + "))"
                        cur.execute(sin)


        # 根据主箱标删除重复扫码
        ds = "DELETE FROM " + tmp + "  where id in  ( select a.id from (select id ,label from " + tmp + ")a left outer  join (  select max(id)id,label from " + tmp + " group by label )b on a.id = b.id  where b.label is null )"
        cur.execute(ds)

        cur0 = self.cn.cursor()
        s = "select a.stk_id,a.new_label, a.new_label2,a.label_new,a.label2_new,p.external_code,p.NAME,p.spec from (select stk_id,coalesce(label_new,label)new_label,coalesce(label2_new,label2,'')new_label2,label_new,label2_new,product_id from " + tmp + " order by stk_id )a " + \
            " left outer join  ( SELECT ID , NAME, external_code, ( SELECT VALUE FROM logistics.product_productprop  WHERE ID = ( SELECT " + \
            " prd_property_id  FROM logistics.product_prdprops  WHERE ( SELECT categoryprop_id FROM logistics.product_productprop WHERE ID = prd_property_id AND product_id = prd.ID ) = 4 " + \
            " )  ) spec  FROM logistics.product_product prd  WHERE company_id = 1 ) P ON a.product_id = P.ID"
        cur0.execute(s)
        # cur0.execute("select stk_id,label,coalesce(label2,''),label_new,label2_new from " + tmp + " order by stk_id")

        is_ok = 'false'
        cnt_ok = 0


        # 取汉得授权TOKEN
        tk = self.get_token()
        if tk:
            while True:
                # 默认一次50条
                rs0 = cur0.fetchmany(self.step)
                if not rs0:
                    break
                boxes = []
                aa = []
                for r in rs0:
                    stk_id = str(r[0])
                    # 替换后主箱标
                    c0 = r[1]
                    # 替换后次箱标
                    c1 = r[2]
                    product_code = r[5]
                    product_name = r[6]
                    self.boxlabels.extend([c0, c1])
                    # 箱号全部用主箱码
                    self.boxes['relations']['totals'].append(c0)
                    boxes.append(c0)
                    aa.append({
                        'dataType': dataType,
                        'mainCodeNo': c0,
                        'otherCodeNo': c1,
                        'codeStatus': 'Active',
                        'codeType': 'C001',
                        'parCodeNo': '',
                        'prodNo': product_code,
                        'prodName': product_name,
                        'caseCode': c0
                    })
                    # self.codes_replac 结构：[ ['旧标','新标','标签类型','主箱标] ]
                    '''               
                    if r[3]:  # 主箱标有替换
                        self.codes_replace.append([c0, r[3], 3, c0])
                    if r[4]:  # 次箱标有替换
                        self.codes_replace.append([c1, r[4], 3, c0])
                    '''
                    s = "select c.label,coalesce(c.code,p.code),c.label_new from " + \
                        "(select id,label,code,label_new from " + bt_c + " where stk_id=" + stk_id + ") c " + \
                        "left outer join " + \
                        "(select id,label,code from " + bt_p + " where stk_id=" + stk_id + ") p " + \
                        "on c.id=p.id"
                    cur.execute(s)
                    rss = cur.fetchall()
                    for rr in rss:
                        if rr[0]:
                            # 瓶标
                            aa.append({
                                'dataType': dataType,
                                'mainCodeNo': rr[0],
                                'otherCodeNo': '',
                                'codeStatus': 'Active',
                                'codeType': 'C002',  # 目前为止，酒鬼只有箱-瓶两级，汉得商定瓶标传盒标编代码C002过去
                                'parCodeNo': c0,
                                'prodNo': product_code,
                                'prodName': product_name,
                                'caseCode': c0
                            })
                            if rr[2]:
                                # self.codes_replac 结构：[ ['旧标','新标','标签类型','主箱标] ]
                                self.codes_replace.append([rr[0], rr[2], 1, c0])
                            if rr[1]:
                                # 盖码、活动码
                                aa.append({
                                    'dataType': dataType,
                                    'mainCodeNo': rr[1],
                                    'otherCodeNo': '',
                                    'codeStatus': 'Active',
                                    'codeType': 'C004',
                                    'parCodeNo': rr[0],
                                    'prodNo': product_code,
                                    'prodName': product_name,
                                    'caseCode': c0
                                })
                rt = self.post_data(self.url_relations, tk, aa)
                status = rt.status_code
                err_boxes = 'null'
                n = len(aa)
                if status == 200:
                    d = json.loads(rt.text)
                    # 全部成功
                    if d['success']:
                        cnt_ok = cnt_ok + n
                        is_ok = 'true'
                        self.boxes['relations']['oks'].extend(boxes)
                    # 有报错箱码
                    else:
                        es = d['result'].split('、')
                        es.remove('处理异常')
                        cnt_ok = cnt_ok + n - len(es)
                        self.boxes['relations']['errors'].extend(es)
                        self.boxes['relations']['oks'].extend(list(set(boxes).difference(set(es))))
                        err_boxes = "'" + json.dumps(es) + "'"
                else:
                    is_ok = 'false'
                s = "update log.sync_inventory_batch set cnt_ok=" + str(
                    len(self.boxes['relations']['oks'])) + " where id=" + str(log_id)
                cur.execute(s)
                s = "INSERT INTO log.sync_inventory_batch_details(sync_id,is_ok,src,result,error_boxes,status) VALUES " + \
                    "(" + str(log_id) + "," + is_ok + ",'" + json.dumps(
                    aa) + "','" + rt.text + "'," + err_boxes + "," + str(status) + ")"
                cur.execute(s)
            # 更新日志结果
            s = "update log.sync_inventory_batch set " + \
                "cnt_ok=" + str(len(self.boxes['relations']['oks'])) + "," + \
                "is_ok=" + is_ok + "," + \
                "boxes_totals='" + json.dumps(self.boxes['relations']['totals']) + "'," + \
                "boxes_oks='" + json.dumps(self.boxes['relations']['oks']) + "'," + \
                "boxes_errors='" + json.dumps(self.boxes['relations']['errors']) + "' " + \
                "where id=" + str(log_id)
            cur.execute(s)
        else:
            err = '取汉得授权TOKEN失败'
            s = "update log.sync_inventory_batch set " + \
                "cnt_ok=0,err_msg='" + err + "' " + \
                "where id=" + str(log_id)
            cur.execute(s)
        cur.close()
        return {'relations': {'ok': len(self.boxes['relations']['oks']), 'total': len(codes)}}

    # 箱码
    def post_delivery(self, codes, batch, product, customer, orderType='01', repead=False):

        self.err_codes = []
        self.boxes = {
            'relations': {'totals': [], 'errors': [], 'oks': []},
            'delivery': {'totals': [], 'errors': [], 'oks': [], 'boxes_out': []},
            'replace': {'totals': [], 'errors': [], 'oks': []},
        }
        tmp = self.tmp
        cur = self.cn.cursor()

        cur.execute("truncate table " + tmp)
        cnt = str(len(codes))
        # 取主箱标号码
        a_codes = []
        for c in codes:
            # 2021-12-12 传过来的标签号码，有替换记录的取的是替换前的号码
            a_codes.append("(" + str(c[0]) + ",'" + c[1] + "','" + c[1] + "'," + str(c[2]) + ")")
        s = "insert into " + tmp + "(stk_id,label,label_now,label_pk_id) values " + ",".join(a_codes)
        cur.execute(s)
        # 更新表名，号段
        s = "update " + tmp + " set tb='label.'||coalesce(" + \
            "(select tbname from label.lbl_tbcross where segment=left(label,4)),'t'||left(label,4))," + \
            "rg=left(label,4)"
        cur.execute(s)

        cur.execute("select tb from " + tmp + " group by tb")
        rs = cur.fetchall()
        for r in rs:
            # stkdetails表内label_pk_id可能是替换前也可能是替换后的，要重新取主箱标ID
            s = "update " + tmp + " t set " + \
                "label_pk_id=(select coalesce(parent_id,id) from " + r[0] + " where code=t.label) " + \
                "where tb='" + r[0] + "'"
            cur.execute(s)
            # 处理箱码
            s = "update " + tmp + " t set " + \
                "label=(select code from " + r[0] + " where id=t.label_pk_id  order by id limit 1)," + \
                "label2=(select code from " + r[
                    0] + " where parent_id=t.label_pk_id and package_id=3 order by id limit 1)," + \
                "label_cnt=(select count(*) from " + r[0] + " where parent_id=t.label_pk_id and package_id=3 )," + \
                "label_new= (select code from label.lbl_replace_details where code0=t.label  order by id desc limit 1)," + \
                "label2_new=(select code from label.lbl_replace_details where code0=t.label2  order by id desc limit 1), " + \
                "product_id = (select product_id from logistics.inventory_stkdetails  where label_pk_id  = t.label_pk_id and batch_id = " + str(
                batch["id"]) + " limit 1)  " + \
                "where tb='" + r[0] + "';"
            cur.execute(s)
        # 20211-12-30 关于替换，汉得要求 旧码做退库，再做替换，再用新码补发
        # for r in rs:
        #    #有替换的，label_pk_id取替换前的pk_id,stkdetails内的label_pk_id可能是替换前也可能是替换后的
        #    s="update "+tmp+" t set "+\
        #        "label=(select code from "+r[0]+" where id=t.label_pk_id) "+\
        #        "where tb='"+r[0]+"'"
        #    cur.execute(s)
        # 写入日志
        # 根据主箱标删除重复扫码
        ds = "DELETE FROM " + tmp + "  where id in  ( select a.id from (select id ,label from " + tmp + ")a left outer  join (  select max(id)id,label from " + tmp + " group by label )b on a.id = b.id  where b.label is null )"
        cur.execute(ds)
        s = "INSERT INTO log.sync_inventory_batch(company_id,vendor,url,batch_id,vtype_id,batch_code,cnt_total,product,customer) values " + \
            "(1,'hand','" + self.url_delivery + "'," + str(batch['id']) + ",2,'" + batch[
                'code'] + "'," + cnt + ",'" + json.dumps(product) + "','" + json.dumps(customer) + "') " + \
            "returning id"
        cur.execute(s)
        r = cur.fetchone()
        log_id = r[0]
        d = {
            "tjCreated": batch['time'],
            "tjId": str(batch['id']).zfill(10) + str(int(time.time())),
            "orderNo": 'TJ' + str(batch['id']).zfill(10),
            # "batch_no":batch['code'], 多带参数汉得会报错
            "dealerCode": customer['code'], 
            "itemList": [],
        }
        if repead:
            d['tjId'] = str(batch['id']).zfill(10) + "-2"

        if not orderType:
            # 出库
            if batch['vtype_id'] == 2:
                orderType = "01"
            # 退库
            elif batch['vtype_id'] == 4:
                orderType = "02"
            # 补发
            else:
                orderType = "03"
        d["orderType"] = orderType

        spec = product['spec']
        a = spec.split('*')
        if len(a) == 2:
            spec = spec + '*1'
        # 取汉得授权TOKEN
        tk = self.get_token()
        err = 'null'
        aa_aa = []
        boxes = []
        err_boxes = []  # 错误箱
        error_codes = []  # 需要二次处理箱
        cnt_ok = 0
        if tk:
            sWhere = ''
            if self.boxes['relations']['errors']:
                sWhere = " where label not in ('" + "','".join(self.boxes['relations']['errors']) + "')"
            if self.err_codes:
                sWhere = ''
            s = "select a.stk_id,a.new_label, a.new_label2,a.label_new,a.label2_new,p.external_code,p.NAME,p.spec from (select stk_id,coalesce(label_new,label)new_label,coalesce(label2_new,label2,'')new_label2,label_new,label2_new,product_id from " + tmp + " order by stk_id )a " + \
                " left outer join  ( SELECT ID , NAME, external_code, ( SELECT VALUE FROM logistics.product_productprop  WHERE ID = ( SELECT " + \
                " prd_property_id  FROM logistics.product_prdprops  WHERE ( SELECT categoryprop_id FROM logistics.product_productprop WHERE ID = prd_property_id AND product_id = prd.ID ) = 4 " + \
                " )  ) spec  FROM logistics.product_product prd  WHERE company_id = 1 ) P ON a.product_id = P.ID"

            s = "select  a.new_label,a.stk_id,a.label_cnt,a.label_now,a.product_id ,p.external_code,p.NAME,p.spec from " + \
                " (select coalesce(label_new, label)new_label,stk_id,label_cnt,label_now,product_id " + \
                " from " + tmp + sWhere + " group by new_label,stk_id,label_cnt,label_now,product_id)a " + \
                " left outer join  ( SELECT ID , NAME, external_code, ( SELECT VALUE FROM logistics.product_productprop  WHERE ID = ( SELECT " + \
                " prd_property_id  FROM logistics.product_prdprops  WHERE ( SELECT categoryprop_id FROM logistics.product_productprop WHERE ID = prd_property_id AND product_id = prd.ID ) = 4 " + \
                " )  ) spec  FROM logistics.product_product prd  WHERE company_id = 1 ) P ON a.product_id = P.ID"
            cur.execute(s)
            for r00 in cur.fetchall():
                product_code = r00[5]
                product_name = r00[6]
                product_spec = r00[7]

                # 两枚箱标以替换后主码发货
                if r00[2] != 2:
                    boxes.append(r00[0])
                    d['itemList'].append({
                        "codeNo": r00[0],  # 码编码
                        "codeType": 'C001',  # 产品类型
                        "prodCode": product_code,  # 产品编码
                        "prodStand": spec,  # 产品规格
                        "scanTime": batch['time']  # 扫码时间
                    })
                # 三枚箱标以现在扫描的标签发货
                else:
                    boxes.append(r00[3])
                    d['itemList'].append({
                        "codeNo": r00[3],  # 码编码
                        "codeType": 'C001',  # 产品类型
                        "prodCode": product_code,  # 产品编码
                        "prodStand": spec,  # 产品规格
                        "scanTime": batch['time']  # 扫码时间
                    })

            self.boxes['delivery']['totals'].extend(boxes)
            # 发送
            rt = self.post_data(self.url_delivery, tk, d)
            status = rt.status_code
            n = len(boxes)
            had_outbound = 0
            duplicate = 0

            if status == 200:
                d = json.loads(rt.text)
                # 2022-1-5
                # 有一瓶正确则success为True
                is_ok = 'true'
                if d['success']:
                    cnt_ok = n
                    msgs = d['message'].split("、")[:-1]
                    for msg in msgs:
                        err_boxes.append(msg.split(":")[0])
                        # 已生成资产
                        if "箱码已扫码出库，请再次确认操作" in msg:
                            self.boxes['delivery']['boxes_out'].extend(msg.split(":")[0])
                            had_outbound = had_outbound + 1
                            pass
                        # 码不存在
                        elif "在产品码数据里不存在！" in msg:
                            s = "select stk_id,label,label_pk_id from " + tmp + " where label ='" + msg.split(":")[
                                0] + "'  or label2 = '" + msg.split(":")[0] + "'"
                            cur.execute(s)
                            for r in cur.fetchall():
                                # id,label_code,label_pk_id,tm
                                self.err_codes.append([r[0], r[1], r[2]])
                        # 在同一个出入库单里重复
                        elif "已出库" in msg:
                            duplicate = duplicate + 1
                        # 无法退货
                        elif "该商品已售出，无法退货" in msg or "该商品已拆箱，无法退货" in msg:
                            pass
                        # 其他错误 包括头错误等
                        else:
                            pass
                    cnt_ok = n - had_outbound - duplicate - len(self.err_codes)

                    # 所有错误标签减去已发货的
                    err_boxes = list(set(err_boxes) - set(self.boxes['delivery']['boxes_out']))
                    self.boxes['delivery']['errors'].extend(err_boxes)

                    # 在基础数据中不存在的存入ok
                    for ec in self.err_codes:
                        error_codes.append(ec[1])
                    self.boxes['delivery']['oks'].extend(error_codes)
                # 有报错，全部行出错或者头出错 则success为false
                else:
                    cnt_ok = 0
                    if "经销商不存在" in d['result']:
                        duplicate = "经销商不存在"
                    else:
                        duplicate = "没有可供处理出入库单行"
                    self.boxes['delivery']['errors'].extend(boxes)
            else:
                is_ok = 'false'
                self.boxes['delivery']['errors'].extend(boxes)

            # 更新日志结果
            s = "update log.sync_inventory_batch set " + \
                "status=" + str(status) + "," + \
                "results='" + json.dumps(rt.text) + "'," + \
                "cnt_total=" + str(n) + "," + \
                "cnt_ok=" + str(cnt_ok) + "," + \
                "is_ok=" + is_ok + "," + \
                "boxes_totals='" + json.dumps(self.boxes['delivery']['totals']) + "'," + \
                "boxes_oks='" + json.dumps(self.boxes['delivery']['oks']) + "'," + \
                "boxes_errors='" + json.dumps(self.boxes['delivery']['errors']) + "', " + \
                "boxes_out='" + json.dumps(self.boxes['delivery']['boxes_out']) + "' " + \
                "where id=" + str(log_id)
            cur.execute(s)
        else:
            err = '取汉得授权TOKEN失败'
            s = "update log.sync_inventory_batch set " + \
                "cnt_ok=0,err_msg='" + err + "' " + \
                "where id=" + str(log_id)
            cur.execute(s)
        cur.execute("truncate table " + self.tmp)
        cur.execute("truncate table " + self.bt_c)
        cur.execute("truncate table " + self.bt_p)
        cur.close()
        return {'delivery': {'ok': cnt_ok, "boxes_out": had_outbound, "duplicate": duplicate, 'total': cnt}}

    # 发送箱标替换记录
    def post_replace(self, codes, batch):
        cur = self.cn.cursor()
        s = "CREATE TEMP TABLE tmp_replace(" + \
            "code0 character varying(20)," + \
            "code  character varying(20)" + \
            ")"
        cur.execute(s)
        t = "tmp_replace"
        a_codes = []
        # codes结构：[ [旧,新,（标签类型）], [旧,新,（标签类型）] ]
        ln = len(codes[0])  # 有标签类型，数组长度为3；无标签类型，数组长度为2
        for c in codes:
            if ln == 2:
                a_codes.append("(" + str(batch['id']) + ",'" + c[0] + "','" + c[1] + "',null)")
            elif ln == 3:
                a_codes.append("(" + str(batch['id']) + ",'" + c[0] + "','" + c[1] + "'," + str(c[2]) + ")")
        self.boxes['replace']['total'] = len(codes)
        # 写入箱码临时表
        cnt = str(len(codes))
        s = "INSERT INTO log.sync_inventory_batch(company_id,vendor,url,batch_id,vtype_id,batch_code,cnt_total) values " + \
            "(1,'hand','" + self.url_replace + "'," + str(batch['id']) + ",2,'" + batch['code'] + "'," + cnt + ") " + \
            "returning id"
        cur.execute(s)
        r = cur.fetchone()
        log_id = r[0]
        cnt_ok = 0
        tk = self.get_token()
        if tk:
            data = []
            s = "select code0,code from " + t + " t where " + \
                "(select id from log.sync_replace_details where code0=t.code0 and code=t.code limit 1) is null"
            cur.execute(s)
            for r in cur.fetchall():
                # data={
                #    "mainCodeNo":r[1],
                #    'oldCode':r[0]
                # }
                data.append({
                    "mainCodeNo": r[1],
                    'oldCode': r[0]
                })
            rt = self.post_data(self.url_replace, tk, data)
            status = rt.status_code
            if status == 200:
                d = json.loads(rt.text)
                if d['success']:
                    cnt_ok = len(codes)
                    self.boxes['replace']['oks'].extend(codes)
                    # 2021-12-20
                    s = "insert into log.sync_replace_details(batch_id,code0,code,package_id) values " + \
                        ",".join(a_codes)
                    cur.execute(s)
                else:
                    self.boxes['replace']['errors'].extend(codes)
            else:
                self.boxes['replace']['errors'].extend(codes)
        else:
            self.boxes['replace']['errors'].extend(codes)
        # 更新日志结果
        n = len(codes)
        s = "update log.sync_replace_batch set " + \
            "status=" + str(status) + "," + \
            "cnt_total=" + str(n) + "," + \
            "cnt_ok=" + str(cnt_ok) + "," + \
            "boxes_totals='" + json.dumps(self.boxes['replace']['totals']) + "'," + \
            "boxes_oks='" + json.dumps(self.boxes['replace']['oks']) + "'," + \
            "boxes_errors='" + json.dumps(self.boxes['replace']['errors']) + "' " + \
            "where id=" + str(log_id)
        cur.execute(s)
        return {'replace': {'ok': cnt_ok, 'total': n}}

        # 套标数据 dataType:      "0":新增    "1":修改

    # 发货数据 orderType:     "01":出库   "02":退库   "03":补发
    def process_batch(self, what, batch_id, dataType='0', orderType='01'):
        cur = self.cn.cursor()
        # 每一个BATCH要初始化箱码数组
        self.boxes = {
            'relations': {'totals': [], 'errors': [], 'oks': []},
            'delivery': {'totals': [], 'errors': [], 'oks': []},
            'replace': {'totals': [], 'errors': [], 'oks': []}
        }
        self.boxlabels = []
        self.codes_replace = []
        s = "select b.id,b.code,b.cnt," + \
            "b.product_id,p.name,p.external_code," + \
            "b.company_to_id,c.name,c.external_code,p.spec,to_char(b.tm,'yyyy-mm-dd hh24:mi:ss'),b.vtype_id " + \
            "from " + \
            "(select id,code,company_to_id,product_id,tm,vtype_id," + \
            "(select count(*) from logistics.inventory_stkdetails where batch_id=bb.id) cnt " + \
            "from logistics.inventory_batch bb where id=" + str(batch_id) + \
            ") b " + \
            "left outer join " + \
            "(select id,name,external_code from logistics.company_company where ancestor_id=1) c " + \
            "on b.company_to_id=c.id " + \
            "left outer join " + \
            "(select id,name,external_code," + \
            "(select value from  logistics.product_productprop where id=(select prd_property_id from logistics.product_prdprops " + \
            "where (select categoryprop_id from logistics.product_productprop where id=prd_property_id and product_id=prd.id)=4)) spec " + \
            "from logistics.product_product prd where company_id=1" + \
            ") p " + \
            "on b.product_id=p.id "
        cur.execute(s)
        r = cur.fetchone()
        batch = {'id': r[0], 'code': r[1], 'time': r[10], ' vtype_id': r[11]}
        product = {'id': r[3], 'name': r[4], 'code': r[5], 'spec': r[9]}
        customer = {'id': r[6], 'name': r[7], 'code': r[8]}
        if what in ['relations', 'delivery', 'return']:
            # 取箱码，有替换的取替换前标签
            s = "select id,coalesce((select code0 from label.lbl_replace_details where code=label_code order by id desc limit 1),label_code)," + \
                "label_pk_id,to_char(tm,'yyyy-mm-dd hh24:mi:ss') " + \
                "from logistics.inventory_stkdetails where batch_id=" + str(batch_id) + " and label_pkg=3"
            # 2021-12-30
            # s = "select id,label_code," + \
            #     "label_pk_id,to_char(tm,'yyyy-mm-dd hh24:mi:ss') " + \
            #     "from logistics.inventory_stkdetails where batch_id=" + str(batch_id) + " and label_pkg=3"
            cur.execute(s)
        codes = []
        for r in cur.fetchall():
            codes.append([r[0], r[1], r[2], r[3]])
        cur.close()
        # 发送套标数据
        if what == 'relations':
            rt = self.post_relations(codes, batch, product, customer, dataType)
            # 基础数据发送成功后，发送基础数据的替换记录
            # if self.boxes['relations']['oks']:
            if True:
                # 结构：[ ['旧标','新标','标签类型','主箱标] ]
                if self.codes_replace:
                    aa = []
                    for r in self.codes_replace:
                        if r[3] in self.boxes['relations']['oks']:
                            aa.append([r[0], r[1]])
                    if aa:
                        self.post_replace(aa, batch)
        # 销售出库
        elif what == 'delivery':
            orderType = '01'
            rt1 = self.post_delivery(codes, batch, product, customer, orderType)

            if len(self.err_codes) != 0:
                secondary = self.post_relations(self.err_codes, batch, product, customer, dataType, repead=True)
                rt2 = self.post_delivery(self.err_codes, batch, product, customer, orderType, repead=True)
                rt = {"First": rt1, "Second": rt2, "Secondary": secondary}
            else:
                rt = rt1

        # 2021-12-20 销售退库
        elif what == 'return':
            orderType = '02'
            rt = self.post_delivery(codes, batch, product, customer, orderType)
        else:
            # def post_replace(self,codes,batch):
            rt = {'e': '参数错误:处理数据只分套标数据和发货数据'}
        return rt


