# -*- 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




#2020-10-27 11位解密
def decrypt_11(code):  
    c=''
    if len(code)==11:  
        s=code.lower()[:10]    
        code=s[9]+s[2]+s[1]+s[3:9]+s[0] 
        try:
            c=str(int('0x'+code,16))
        except Exception:
            c=code
    return c


def decrypt_11h(code):    
    if code[0].lower() in ('h','g'):
        return decrypt_11(code[1:])
    return ''


class HandReplace():   
    # 初始化
    def __init__(self, is_test=True, cn=None, log=True, step=100):
        self.log = log
        # 结构：[ ['旧标','新标','标签类型','主箱标] ]
        self.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_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_replace = 'https://winesmp.cofco.com/prod/trml/link/prodCode/changeCodeByTJ'     
        
        # 汉得测试环境
        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'

    # 取汉得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=''
        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_replace(self, codes, batch):
        cur = self.cn.cursor()   
        cnt = str(len(codes))
        data = []           
        for c in codes:               
            data.append({
                "mainCodeNo": c[1],
                'oldCode':    c[0]
            })
        # 写入替换日志        
        s = "INSERT INTO log.sync_replace_batch(company_id,vendor,url,batch_id,batch_code,cnt_total,src) values " + \
            "(1,'hand','"+self.url_replace+"',"+str(batch['id'])+",'"+batch['code']+"',"+cnt+",'"+json.dumps(data)+"') " + \
            "returning id"
        cur.execute(s)
        r = cur.fetchone()
        log_id = r[0]
        #如果没有替换数据需要传送
        if cnt=='0':
            d={'replace': {'ok': 0, 'total': 0,'remark':'没有替换数据需要发送汉得'}}           
            s="update log.sync_replace_batch set results='"+json.dumps(d)+"'"
            cur.execute(s)
            cur.close()
            return d   
        
        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.replace['total'] = len(codes)        
        cnt_ok = 0
        tk = self.get_token()
        handled=[]
        if tk:            
            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=ln                    
                    for c in codes:
                        handled.append("("+str(batch['id'])+",'"+c[0]+"','"+c[1]+"',now(),now(),true)")
                    #已传送成功的数据，写入handled表
                    sIns="INSERT INTO log.sync_replace_handled(batch_id,code0,code,tm,tm_handled,is_handled) values "
                    s=sIns+",".join(delivered)+" on conflict(code0) do update set "+\
                        "batch_id=EXCLUDED.batch_id,code=EXCLUDED.code,tm=now(),tm_handled=now(),is_handled=true"
                    cur.execute(s)

                    result={'e':'','msg':str(ln)+'条替换发送成功'}
                else:                    
                    result={'e':'失败,'+d['result']}
            else:                
                result={'e':'失败，code:'+str(status)}
        else:
            status=-1            
            result={'e':'获取汉得token失败'}

        if not handled: #汉得 要么全部成功，要么全都不成功
            a=[]
            for c in codes:
                a.append("("+str(batch['id'])+",'"+c[0]+"','"+c[1]+"',now(),false,'发送失败')")
            sIns="INSERT INTO log.sync_replace_handled(batch_id,code0,code,tm,is_handled,remark) values "
            s=sIns+",".join(a)+" on conflict(code0) do update set "+\
                "batch_id=EXCLUDED.batch_id,code=EXCLUDED.code,tm=now(),is_handled=false,remark='发送失败'"
            cur.execute(s)
        # 更新日志结果          
        s = "update log.sync_replace_batch set " + \
            "status=" + str(status) + "," + \
            "results='"+json.dumps(result)+"' "+\
            "where id=" + str(log_id)
        cur.execute(s)
        cur.close()
        return {'replace': {'ok': cnt_ok, 'total': ln}}
        
    def process_batch(self, batch_id):
        t="tmp_replace"
        cur = self.cn.cursor()        
        s = "CREATE TEMP TABLE "+t+"(" + \
                "code0 character varying(20)," + \
                "code character varying(20)," + \
                "label0 character varying(20)," + \
                "label character varying(20)," + \
                "label_pk_id0 bigint,"+\
                "label_pk_id  bigint,"+\
                "product_id integer,"+\
                "customer_id integer, "+\
                "rg0 character varying(10),"+\
                "rg character varying(10),"+\
                "tb0 character varying(20),"+\
                "tb character varying(20), "+\
                "label_pkg smallint "+\
            ")"
        cur.execute(s) 
        s="select code0,code from "+\
            "(select code0, "+\
                "(select code from label.lbl_replace_details where id="+\
                    "(select max(id) from label.lbl_replace_details where batch_id="+str(batch_id)+" and code0=d.code0)"+\
                ") code "+\
                "FROM label.lbl_replace_details d where batch_id="+str(batch_id)+" and "+\
                "(select id from log.sync_replace_handled where code0=d.code0 and is_handled=true) is null "+\
            ") a group by code0,code"
        cur.execute(s)
        rs=cur.fetchall()
        a=[]
        codes=[]
        for r in rs:
            if r[0][0]=='h':    #只会有小圆标替换，瓶盖不会做替换
                lb0=decrypt_11h(r[0])
                lb =decrypt_11h(r[1])
                if lb and lb0:
                    a.append("('"+r[0]+"','"+r[1]+"','"+lb0+"','"+lb+"','"+lb0[:4]+"','"+lb[:4]+"')")
            else:
                a.append("('"+r[0]+"','"+r[1]+"','"+r[0]+"','"+r[1]+"','"+r[0][:4]+"','"+r[1][:4]+"')")
        if a:
            s="insert into "+t+"(code0,code,label0,label,rg0,rg) values "+",".join(a)
            cur.execute(s)
            #是否使用了跨段绑定的标签
            s="update "+t+" set "+\
                "tb0=(select tbname FROM label.lbl_tbcross where segment=rg0),"+\
                "tb=(select  tbname FROM label.lbl_tbcross where segment=rg)"
            cur.execute(s)
            #没有跨段的取号段表名
            s="update "+t+" set tb0='label.t'||rg0 "+\
                "where tb0 is null"                
            cur.execute(s)         
            
            cur.execute("select tb0 from "+t+" group by tb0")
            rs=cur.fetchall()

            #取主箱标ID 目前酒鬼套标大多为 箱-瓶 两级关系（发汉得产品只有箱瓶两级）
            for r in rs:                
                tb=r[0]
                s="update "+t+" t set label_pk_id0="+\
                    "(select coalesce(parent_id,id) from "+tb+" where code=label0) "+\
                    "where tb0='"+tb+"'"
                cur.execute(s)
            #取产品ID
            for r in rs:                
                tb=r[0] 
                s="update "+t+" t set "+\
                    "product_id=(select product_id from logistics.inventory_stkdetails "+\
                        "where company_id=1 and label_pk_id=t.label_pk_id0 and is_delete=false "+\
                        "order by vtype_id,dt limit 1),"+\
                    "customer_id=(select company_to_id from logistics.inventory_stkdetails "+\
                        "where company_id=1 and vtype_id=2 and label_pk_id=t.label_pk_id0 and is_delete=false "+\
                        "order by dt desc limit 1) "+\
                    "where tb0='"+r[0]+"'"
                cur.execute(s)  
            pids=[]
            s="select products from logistics.thirdparty_vendor where company_id=1 and code='HD'"
            cur.execute(s)
            ps=cur.fetchone()
            for p in ps[0]:                
                pids.append("'"+str(p['id'])+"'")
            s="select code0,code,customer_id from "+t+" where product_id in ("+",".join(pids)+")"
            cur.execute(s)
            undelivered=[]
            for r in cur.fetchall():
                if r[2]:
                    codes.append([r[0],r[1]])                     
                else:
                    undelivered.append("("+str(batch_id)+",'"+r[0]+"','"+r[1]+"',now(),'未发货')")
            if undelivered:
                sIns="INSERT INTO log.sync_replace_handled(batch_id,code0,code,tm,remark) values "
                s=sIns+",".join(undelivered)+" on conflict(code0) do update set "+\
                    "batch_id=EXCLUDED.batch_id,code=EXCLUDED.code,tm=now(),is_handled=false,remark='未发货'"                
                cur.execute(s)
        cur.close()
        batch={'id':batch_id,'code':'RP_'+str(batch_id).zfill(8)}    
        rt=self.post_replace(codes, batch) 
        return rt
    


is_test=True
batch_id=439

cn=psycopg2.connect(database="datacenter", user="acc",password="123",host="47.107.230.103", port="5432")
hand=HandReplace(is_test=is_test,cn=cn)
hand.process_batch(batch_id=batch_id)

