#coding:utf-8
import psycopg2
import hashlib
import requests
import datetime
import json 

class BzData():
    #初始化
    def __init__(self,is_test=True,cn=None,log=True):
        self.log=log
        #结构：[ ['旧标','新标','标签类型','主箱标] ]
        self.codes_replace=[]
        self.boxlabels=[]
        self.boxes={
            'relations':{'totals':[],'errors':[],'oks':[]},
            'delivery': {'totals':[],'errors':[],'oks':[]},
            'replace':  {'totals':[],'errors':[],'oks':[]},
        }
        #内网取数地址，随机字符
        self.url_internal='http://www.china315net.com:35088/get/codes/from/wuliu/1644/'
        self.sK_internal='$internal&GLker5w3jtQLx#'
        #数据库连接，如果为空，使用django默认连接
        if cn:
            cn.autocommit = True
        else:
            cn=connection
        self.cn=cn        
        if is_test:
            # 博智测试环境
            self.url_bz = 'http://122.9.157.64:17001/cps/v1/dealerOrder/dealerOrder/getCodeFromTianJan'
            # 博智正式环境
        else:
            self.url_bz = 'https://cps.jgjnc.com/cps/v1/dealerOrder/dealerOrder/getCodeFromTianJan'
        tmp = 'tmp'
        bt_c = 'bottles_c'
        bt_p = 'bottles_p'
        bx = 'boxes'
        fw = 'fw'
        self.tmp = tmp
        self.bt_c = bt_c
        self.bt_p = bt_p
        self.bx = bx
        self.fw = fw
        cur=self.cn.cursor()
        #发货标签号码临时表
        s="CREATE TEMP TABLE "+tmp+"("+\
            "stk_id integer,"+\
            "label_code character varying(20),"+\
            "tb character varying(20),"+\
            "label_pk_id bigint,"+\
            "product_id integer,"+\
            "company_to_id integer,"+\
            "cnt integer,"+\
            "cartons jsonb,"+\
            "boxes jsonb,"+\
            "bottles jsonb "+\
        ")"
        cur.execute(s)
        # 箱标表（取关联箱示和防伪码）
        s = "CREATE TEMP TABLE " + bx + "(" + \
            "stk_id integer," + \
            "id serial," + \
            "label character varying(20)," + \
            "code character varying(20)" + \
            ")"
        cur.execute(s)
        # 瓶标表 取瓶盖码
        s = "CREATE TEMP TABLE " + bt_c + "(" + \
            "stk_id integer," + \
            "id serial," + \
            "label character varying(20)," + \
            "code 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)
        # 防伪码
        s = "CREATE TEMP TABLE " + fw + "(" + \
            "stk_id integer," + \
            "id integer," + \
            "label character varying(20)," + \
            "code character varying(20)" + \
            ")"
        cur.execute(s)
        cur.close()
        
 
    def process_batch(self,batch_id,what=['delivery']):
        s = "select b.id,to_char(tm,'yyyy-mm-dd hh24:mi:ss')," + \
            "c.name,c.external_code,p.name,p.external_code," + \
            "coalesce(b.dn_number,'')," + \
            "coalesce(b.salesorder_code,'')," + \
            "coalesce(b.salesorder_type_code,'')," + \
            "coalesce(b.code,'')," + \
            "p.id,c.id," + \
            "(select products from logistics.thirdparty_ordersync where " + \
            "company_id=1644 and left(order_code,9)=left(b.code,9) order by id desc limit 1)" + \
            "from " + \
            "(select id,company_to_id,product_id,tm,dn_number,salesorder_code,salesorder_type_code,code from logistics.inventory_batch "+\
                "where id=" + str(batch_id) + ") b " + \
            "left outer join " + \
            "(select id,name,external_code from logistics.company_company) c " + \
            "on b.company_to_id=c.id " + \
            "left outer join " + \
            "(select id,name,external_code from logistics.product_product) p " + \
            "on b.product_id=p.id"
        cur=self.cn.cursor()
        cur.execute(s)
        r = cur.fetchone()
        batch=   {'id': r[0],  'code':r[9], 'time':r[1]}
        product= {'id': r[10], 'code':r[5], 'name': r[4]}
        customer={'id': r[11], 'code':r[3], 'name': r[2]}
        o_t = r[8]
        o_n = r[7]
        if not r[8]:
            if r[12]:
                o_n = r[9]
                for p in r[12]:
                    if p['code'] == r[5]:
                        o_t = p['order_type']['code']
                        break
        order={
            'dn_number':r[6],
            'number':o_n,
            'type':o_t            
        }
        cur.close()
        return self.post_delivery(batch,order,customer,product) 
      
    
    def post_delivery(self,batch,order,customer,product,mode='new'):
        b_fw=True
        b_promotion=True
        
        id=batch['id']
        barcodes=[]
        tmp=self.tmp
        bt_c=self.bt_c
        bt_p=self.bt_p
        bx=self.bx
        fw=self.fw
        barcodes = []
        cur=self.cn.cursor()
        cur.execute('truncate table ' + tmp)
        cur.execute('truncate table ' + bx)
        cur.execute('truncate table ' + fw)
        cur.execute('truncate table ' + bt_c)
        cur.execute('truncate table ' + bt_p)      
        #标签号码取替换前号码
        s = "insert into "+tmp+"(stk_id,label_code,label_pk_id,product_id,company_to_id) select id,"+\
            "coalesce((select code0 from label.lbl_replace_details where code=label_code),label_code),"+\
            "label_pk_id,product_id,company_to_id " + \
            "from logistics.inventory_stkdetails where batch_id=" + str(id)
        cur.execute(s)
        cnt_total=cur.rowcount
        cnt_ok=0        
        s="INSERT INTO log.sync_inventory_batch(company_id,vendor,url,batch_id,vtype_id,batch_code,cnt_total,product,customer) values "+\
            "(1644,'bz','"+self.url_bz+"',"+str(batch['id'])+",2,'"+batch['code']+"',"+str(cnt_total)+",'"+json.dumps(product)+"','"+json.dumps(customer)+"') "+\
            "returning id"
        cur.execute(s)    
        r=cur.fetchone()   
        log_id=r[0]

        #取号段或跨段表名
        s="update "+tmp+" set tb=coalesce((select 'label.'||tbname from label.lbl_tbcross where segment=left(label_code,4)),'label.t'||left(label_code,4))"
        cur.execute(s)
        
        cur.execute("select tb from " + tmp + " group by tb")
        rs = cur.fetchall()
        for r in rs:
            # 插入箱标表
            s = "insert into " + bx + "(stk_id,label) select s.stk_id,t.code from " + \
                "(select stk_id,label_pk_id from " + tmp + " where tb='" + r[0] + "') s," + r[0] + " t " + \
                "where t.id=s.label_pk_id or (t.parent_id=s.label_pk_id and t.package_id=3)"
            cur.execute(s)
            # 插入瓶标
            s = "insert into " + bt_c + "(stk_id,label) select s.stk_id,t.code 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)

        t = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        m2 = hashlib.md5((self.sK_internal + t).encode('utf8'))
        key = m2.hexdigest()
        # 取箱标防伪码
        if b_fw:
            d = []
            cur.execute("select id,label from " + bx)
            for r in cur.fetchall():
                d.append({'id': r[0], 'label': r[1]})
            r = requests.post(self.url_internal + '/fw/', 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 " + bx + " where id=" + str(c['id']) + ") )")
            cur.execute("insert into " + fw + "(id,label,code,stk_id) values " + ",".join(a))
            # 取瓶标小圆标活动码
        if b_promotion:
            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]})
            data = {'key': key, 'time': t, 'labels': d}

            r = requests.post(self.url_internal + '/promotion/', json={'key': key, 'time': t, 'labels': d})
            if r.status_code == 200:
                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']) + ") )")
                if a:
                    cur.execute("insert into " + bt_p + "(id,label,code,stk_id) values " + ",".join(a))

        s = "select fw.cartons,p.bottles from " + \
            "(select stk_id,json_agg(json_build_object(" + \
            "'logistics_code',label," + \
            "'security_code',coalesce(code,'')" + \
            ")) cartons from " + fw + " group by stk_id" + \
            ") fw " + \
            "left outer join " + \
            "(select stk_id,json_agg(json_build_object(" + \
            "'logistics_code',label," + \
            "'bottlecap',coalesce(code,(select code from " + bt_p + " where id=btc.id),'')" + \
            ")) bottles from " + bt_c + " btc group by stk_id" + \
            ") p " + \
            "on fw.stk_id=p.stk_id"
        cur.execute(s)
        for r in cur.fetchall():
            barcodes.append({
                "bottles": r[1],
                "boxes": [],
                "cartons": r[0]
            })
        data=[{
            "barcodes":barcodes,
            "batch_id": batch['id'],
            "batch_no": batch['code'],
            "time":     batch['time'],
            "customer":{'code':customer['code'],'name':customer['name']},
            "product": {'code':product['code'],'name':product['name']},
            "dn_number":order['dn_number'],
	    "orderNo": order['number'],
	    "orderType": order['type']      
	    
        }]    
        
        r=requests.post(self.url_bz,json=data)
        status=r.status_code 
        is_ok='false'
        if status==200:
            d=json.loads(r.text)
            if d['success']:
                cnt_ok=cnt_total
                is_ok='true'
            rt="'"+json.dumps(r.text)+"'"
        else:
            rt='null'
        s="update log.sync_inventory_batch set "+\
            "status="+str(status)+",is_ok="+is_ok+",cnt_total="+str(cnt_total)+",cnt_ok="+str(cnt_ok)+","+\
            "src='"+json.dumps(data)+"',results="+rt+" where id="+str(log_id) 
        cur.execute(s)  
        cur.close()
        return {'delivery':{'ok':cnt_ok,'total':cnt_total}}

#cn=psycopg2.connect(database="datacenter", user="acc",password="123",host="47.107.230.103", port="5432")    
#bz=BzData(cn=cn)
#bz.process_batch(batch_id=266587)
