# -*- coding:utf-8 -*-  
#import win32com.client  
import os, sys, string  
import sys
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.boxes={
            'relations':{'totals':[],'errors':[],'oks':[]},
            'delivery': {'totals':[],'errors':[],'oks':[]},
            '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+"("+\
            "stk_id integer,"+\
            "label character varying(20),"+\
            "label2 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,"+\
            "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)"+\
        ")"
        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'):   
        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),"+\
                "label2="+\
                    "(select code from "+r[0]+" where parent_id=t.label_pk_id and package_id=3 order by id limit 1),"+\
                "label_new= (select code from label.lbl_replace_details where code0=t.label),"+\
                "label2_new=(select code from label.lbl_replace_details where code0=t.label2) "+\
                "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) 
        cur0=self.cn.cursor()
        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]
                    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']))+","+\
               "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'):
        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]+"',"+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:    
            #有替换的，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)     
        # 写入日志        
        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),
            "orderNo": 'TJ' + str(batch['id']).zfill(10),
            #"batch_no":batch['code'], 多带参数汉得会报错
            "dealerCode": customer['code'],
            "itemList": [],
        }
        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=[]
        cnt_ok = 0
        if tk:            
            sWhere=''
            if self.boxes['relations']['errors']:
                sWhere=" where label not in ('"+"','".join(self.boxes['relations']['errors'])+"')"
            s="select label,stk_id from "+tmp+sWhere
            cur.execute(s)
            for r00 in cur.fetchall():
                boxes.append(r00[0])                
                d['itemList'].append({
                    "codeNo": r00[0],               # 码编码
                    "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)
            if status==200:
                d=json.loads(rt.text)
                #全部成功
                if d['success']:
                    cnt_ok=n
                    is_ok='true'
                    self.boxes['delivery']['oks'].extend(boxes)
                #有报错，汉得一箱数据都不会接收
                else:  
                    cnt_ok=0
                    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)+","+\
                "boxes_totals='"+json.dumps(self.boxes['delivery']['totals'])+"',"+\
                "boxes_oks='"+json.dumps(self.boxes['delivery']['oks'])+"',"+\
                "boxes_errors='"+json.dumps(self.boxes['delivery']['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 {'delivery':{'ok':cnt_ok,'total':len(codes)}}
    
    
    #发送箱标替换记录
    def post_replace(self,codes,batch):    
        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("('"+c[0]+"','"+c[1]+"',null)")
            elif ln==3:
                a_codes.append("('"+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]
                }
            rt = self.post_data(self.url_replace, tk, d)
            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)                        
                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="+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']:        
            #取箱码，有替换的取替换前标签
            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"
            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 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':
            rt= self.post_delivery(codes,batch,product,customer,orderType)
        else:
            #def post_replace(self,codes,batch):  
            rt= {'e':'参数错误:处理数据只分套标数据和发货数据'}
        #print(rt)
        return rt   




'''
data={
    'step':100,
    'batch':{
        'id':245102,
        'code':'8220056639'
    },
    #'actions':['relations']
    #'actions':['delivery']
    'actions':['relations','delivery']
}

batch_id=data['batch']['id']
actions=data['actions']
step=data['step']
    
    
cn=psycopg2.connect(database="datacenter", user="acc",password="123",host="47.107.230.103", port="5432")
is_test=True
hand=HandData(is_test=is_test,cn=cn,step=step)
#发送整笔BATCH全部所有数据
for act in actions:
    r=hand.process_batch(act,batch_id)
'''
