from django.shortcuts import render,HttpResponse
from django.db import connection
from django.conf import settings
import json
import requests
from inventory.models import *
from utils.views import *
import datetime
# Create your views here.
from django.views.decorators.csrf import csrf_exempt
from inventory.models import OnlineOrder
from product.models import Product,ProductProp,PrdProps
from company.models import Company
from .models import *
from inventory.views import sK
from .forms import *

from django.contrib.auth.decorators import login_required
from inventory.views import LabelQuery
import math

from erp.models import SalesOrder


inv_sK=sK


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





#2020-08-15 第三方取物流数据
def stk_3rd_fetch(request, what, company_id):
    data = {'e': '', 'data': []}
    sKs={
        '18116':'xF$3al1i%)20@)0814',   # 山西西凤酒分公司       
    }
    e=[]
    if company_id not in sKs:
        e.append('company id error')
    dt=datetime.datetime.now().strftime('%Y-%m-%d')
    dt_fr=request.GET.get('date_from',dt)
    dt_to=request.GET.get('date_to',dt)
    if not is_valid_date(dt_fr+' 00:00:00'):
        e.append('invalid format:date_from')
    if not is_valid_date(dt_to+' 00:00:00'):
        e.append('invalid format:date_to')
    if e:
        data['e']=','.join(e)
        return JsonResponse(data)
    sK=sKs[company_id]
    e = time_key_check(request, sK)
    if e:
        data['e'] = e
        return JsonResponse(data)


    #if what=='new':
    #    sWhere=" where ancestor_id="+company_id+" and (dt between '"+dt_fr+"' and '"+dt_to+"')"
    #elif what=='edit':
    #    sWhere=" where ancestor_id="+company_id+" and id in (select obj_id from company_changelog where "+\
    #        "obj_name='Batch' and ancestor_id="+company_id+" and (dt between '"+dt_fr+"' and '"+dt_to+"') group by obj_id)"       
    if what=='new':
        sWhere=" where company_id="+company_id+" and (dt between '"+dt_fr+"' and '"+dt_to+"')"
    elif what=='edit':
        sWhere=" where company_id="+company_id+" and id in (select obj_id from company_changelog where "+\
            "obj_name='Batch' and company_id="+company_id+" and (dt between '"+dt_fr+"' and '"+dt_to+"') group by obj_id)"
    else:
        data['e'] = 'invalid request'
        return JsonResponse(data)

    cur = connection.cursor()

    s="select b.id,b.code,b.tm,b.cnt,b.vtype_id,"+\
        "(select name from inventory_voucher where id=b.vtype_id),"+\
        "b.company_id,   c1.name,coalesce(c1.external_code,''),"+\
        "b.company_to_id,c2.name,coalesce(c2.external_code,''),"+\
        "b.product_id,p.name,coalesce(p.external_code,''),"+\
        "s.id,s.name,coalesce(s.external_code,'') "+\
        "from "+\
        "(SELECT id,code,to_char(a.tm,'yyyy-mm-dd HH24:MI:SS') tm,"+\
            "(select count(*) from inventory_stkdetails where batch_id=a.id) cnt,"+\
            "vtype_id,company_id,company_to_id,product_id,store_fr_id "+\
            "FROM inventory_batch a  "+sWhere+\
        ") b "+\
        "left outer join "+\
        "(select id,name,external_code from company_company) c1 "+\
        "on c1.id=b.company_id "+\
        "left outer join "+\
        "(select id,name,external_code from company_company) c2 "+\
        "on c2.id=b.company_to_id "+\
        "left outer join "+\
        "(select id,name,external_code from product_product) p "+\
        "on b.product_id=p.id "+\
        "left outer join "+\
        "(select id,name,external_code from inventory_storehouse) s "+\
        "on b.store_fr_id=s.id"
    #s="SELECT id,code,to_char(a.tm,'yyyy-mm-dd HH24:MI:SS'),"+\
    #    "(select count(*) from inventory_stkdetails where batch_id=a.id) cnt,"+\
    #    "vtype_id,(select name from inventory_voucher where id=a.vtype_id),"+\
    #    "company_id,(select name from company_company where id=a.company_id),"+\
    #    "company_to_id,(select name from company_company where id=a.company_to_id),"+\
    #    "product_id,(select name from product_product where id=a.product_id),"+\
    #    "store_fr_id,(select name from inventory_storehouse where id=a.store_fr_id), "+\
    #    "coalesce((select external_id from company_company where id=a.company_to_id),0),"+\
    #    "coalesce((select external_code from product_product where id=a.product_id),'') "+\
    #    "FROM inventory_batch a  "+sWhere   
    cur.execute(s)
    for r in cur.fetchall():
        if r[3]>0:
            batch={'id':r[0],'bill-number':r[1],'time':r[2],
                'voucher':{'id':r[4],'name':r[5]},
                'company':{'id':r[6],'name':r[7],'external_code':r[8]},
                'agent':{'id':r[9],'name':r[10],'external_code':r[11]},
                'product':{'id':r[12],'name':r[13],'external_code':r[14]},
                'storehouse':{'id':r[15],'name':r[16],'externale_code':r[17]}
            }
            details = []
            cur.execute("select label_code,label_pkg,label_pk_id from inventory_stkdetails where batch_id=" + str(r[0]))
            for rr in cur.fetchall():
                details.append([ rr[0],rr[1],rr[2] ])
            data['data'].append({'details': details, 'batch': batch})
    cur.close()
    return JsonResponse(data)



#2020-08-26 第3方数据同步
@csrf_exempt
def sync_data(request,company_id,what,option):
    data = {'e': ''}
    subjects=['agent',]
    options=['add',]
    sKs={
        '18116':'xF$3al1i%)20@)0814',   # 山西西凤酒分公司       
    }
    e=[]
    if company_id not in sKs:
        e.append('company id error')
    if what not in subjects:
        e.append('subject err')
    if option not in options:
        e.append('option err')
    dt=datetime.datetime.now().strftime('%Y-%m-%d')
    dt_fr=request.GET.get('date_from',dt)
    dt_to=request.GET.get('date_to',dt)
    if not is_valid_date(dt_fr+' 00:00:00'):
        e.append('invalid format:date_from')
    if not is_valid_date(dt_to+' 00:00:00'):
        e.append('invalid format:date_to')
    if e:
        data['e']=','.join(e)
        return JsonResponse(data)
    sK=sKs[company_id]
    e = time_key_check(request, sK)
    if e:
        data['e'] = e
        return JsonResponse(data)
    cur = connection.cursor()
    if what=='agent':
        if company_id=='18116':
            keys=['user_name','staff_code','agent_id','agent_name','province','city','cell_phone']
            r=json.loads(request.body)
            for ky in keys:
                if ky not in r:
                    e.append(ky+" missing")
            if e:
                data['e']=','.join(e)
                return JsonResponse(data)
            if option=='add':
                #company
                cs=Company.objects.filter(legacy_id=int(r['agent_id']))
                if cs:
                    e.append('agent id already existed:'+str(r['agent_id']))
                cs=Company.objects.filter(name=r['agent_name'],ancestor_id=int(company_id))
                if cs:
                    e.append('agent name already existed:'+r['agent_name'])
                if e:                    
                    s="insert into log_request_err(request_url,method,parameters,err_msg) values "+\
                        "('"+request.path+"','POST','"+json.dumps(r)+"','"+','.join(e)+"')"
                    cur.execute(s)
                    data['e']=','.join(e)                        
                    return JsonResponse(data)
                pid=int(company_id)
                c=Company(external_id=int(r['agent_id']),parent_id=pid,ancestor_id=pid,hierarchy_id=pid,version=0,category_id=1,tm_register=datetime.datetime.now(),
                    user_id=3314,name=r['agent_name'],cell_phone=r['cell_phone'],province=r['province'],city=r['city'],level_id=2
                )
                c.save()
                #user
                s="select username from company_user where username='"+r['user_name']+"'"
                cur.execute(s)
                rr=cur.fetchone()
                if rr:
                    s="insert into log_request_err(request_url,method,parameters,err_msg) values "+\
                        "('"+request.path+"','POST','"+request.body+"','"+r['user_name']+" 已存在')"
                    cur.execute(s)
                else:
                    u=User(company_id=c.id,username=r['user_name'],ancestor_id=pid, external_code=r['staff_code'], phone=r['cell_phone'])
                    u.set_password(r['cell_phone'])
                    u.save()
                    u.groups.add(5)  #经销商功能组

    return JsonResponse(data)




#2021-011-05
@csrf_exempt
def online_order(request,company_id,what):
    data = {'e': ''}
    sKs={
        '18116':'xF$3al1i%)20@)0814',   # 山西西凤酒分公司       
    }
    if company_id not in sKs:         
        data['e'] = 'company id error'
        return JsonResponse(data)

    sK=sKs[company_id]
    e = time_key_check(request, sK)
    if e:
        data['e'] = e
        return JsonResponse(data)
    if what=='create':
        pid=None
        r=json.loads(request.body)    
        if OnlineOrder.objects.filter(order_sn=r['order_sn']):
            data['e']='订单: '+r['order_sn']+' 已存在'
            return JsonResponse(data)
        ps=Product.objects.filter(external_code=r['goods_number'])
        if ps:
            pid=ps[0].id
        o=OnlineOrder(
            company_id=int(company_id),
            order_sn=r['order_sn'], 
            product_id=pid,
            quantity=int(r['num']),
            amount=float(r['total_price']),
            userid=int(r['user_id']),
            storeid=int(r['storage_id']),
            product_name=r['goods_name'],
            product_code=r['goods_number'],
            username=r['username'],
            mobile=r['mobile'],
            address=r['address']           
        )
        try:
            o.save()
        except Exception:
            data['e']='保存订单出错'
            return JsonResponse(data)
    elif what=='get':
        data['order']={'id':0,'number':'','product':{'id':0,'name':'','qty':0}}
        sn=request.GET.get('order_sn','')
        if not sn:
            data['e']='订单号不能为空'
            return JsonResponse(data)
        os=OnlineOrder.objects.filter(company_id=int(company_id),order_sn=sn)
        if not os:
            data['e']='订单号不存在'
            return JsonResponse(data)
        o=os[0]
        cur=connection.cursor()
        cur.execute("select id,name from product_product where company_id="+company_id+" and external_code='"+o.product_code+"'")
        r=cur.fetchone()
        if r:
            data['order']={
                'id':o.id,
                'number':o.order_sn,
                'product':{
                    'id':r[0],
                    'name':r[1],
                    'qty':o.quantity,
                }
            }
        cur.close()
    elif what=='sync_order':
        cur=connection.cursor()
        s="SELECT id,order_sn,product_id,product_name,product_code,quantity,amount,storeid,tm FROM inventory_onlineorder "+\
            "where company_id="+company_id+" and is_closed=false"
        cur.execute(s)
        o=[]
        for r in cur.fetchall():
            o.append({
                'id':r[0],                
                'order_sn':r[1],
                'product_id':r[2],
                'product_name':r[3],
                'product_code':r[4],
                'quantity':r[5],
                'amount':r[6],
                'storeid':r[7],
                'time':r[8].strftime('%Y-%m-%d %H:%M:%S'),
            })
        data['orders']=o
    return JsonResponse(data)
            


def check_json_format(raw_msg):
  """
  用于判断一个字符串是否符合Json格式
  :param self:
  :return:
  """
  if isinstance(raw_msg, str):    # 首先判断变量是否为字符串
    try:
      json.loads(raw_msg, encoding='utf-8')
    except ValueError:
      return False
    return True
  else:
    return False


#2021-06-25 接收第三方数据接口
@csrf_exempt
def data_post(request,company_id,vendor,obj,action=None):
    companys=[
        "1",        #酒鬼酒,
        "1644",     #内参
    ]    
    data = {'e': '', 'result':{} }
    sKs={
        'tms': 'tms$3al1i%)20@)0814',       
        'hand':'hand$tYal1i%)20@)c1W',  
        'bz':  'bz$tl1hFikq%(30@cc1Z', 
    }
    if company_id not in companys:
        data['e']='company id is error'
        return JsonResponse(data)
    which=vendor+"_"+obj 
    if vendor not in sKs:
        data['e']='vendor is not defined'
        return JsonResponse(data)  
    sK=sKs[vendor]
    e = time_key_check(request, sK)
    if e:
        data['e'] = e
        return JsonResponse(data)
    if request.method=='GET':
        data['e']='http post is prefered'
        return JsonResponse(data)
    cur=connection.cursor()
    r=json.loads(request.body)
    if which=="tms_dn":                 #酒鬼酒-TMS-发货单
        if 'data' not in r:
            data['e']='data missing'
            return JsonResponse(data)
        d=r['data']  
        if 'dn_number' not in d or 'products' not in d or 'storehouse' not in d or 'customer' not in d:
            data['e']='dn_number/customer/storehouse/products missing in data'
            return JsonResponse(data)    
        c=d['dn_number']
        cur.execute("select to_char(tm,'yyyy-mm-dd HH24:MI:SS') from z_jiugui_tms_dn where dn_number='"+c+"'")
        r=cur.fetchone()
        if r:
            data['e']="dn_number '"+c+"' already exists ("+r[0]+")"
            return JsonResponse(data)        
        s="INSERT INTO z_jiugui_tms_dn(dn_number, content) VALUES ('"+c+"','"+json.dumps(d)+"')"
        cur.execute(s)
        return JsonResponse(data)   
    elif vendor=='hand':                #酒鬼酒-ERP-上海汉得信息技术股份有限公司
        data={'e':''}
        d=None    
        id=None
        is_delete=False
        if 'data' in r:
            d=r['data']
        sap_code=None
        e=[]
        is_ok='false'
        err="null"               
        if d:
            s="INSERT INTO z_3rd_hand(obj, src) values "+\
                "('"+obj+"','"+json.dumps(d)+"') returning id"
            cur.execute(s)
            r=cur.fetchone()
            id=str(r[0])
            if 'sap_code' in d:
                sap_code=d['sap_code']
            else:
                if obj not in ['stock']:
                    e.append('sap_code missing in data')
            if 'status' in d:
                if int(d['status'])==0:
                    is_delete=True
            else:
                if obj not in ['stock']:
                    e.append('status missing in data')
        else:
            e.append('data missing')
        if e:
            err=",".join(e)
            data['e']=err
            err=err[:100]
            s="update z_3rd_hand set is_ok="+is_ok+",result='"+json.dumps(data)+"',err='"+err+"' where id="+id
            cur.execute(s)
            return JsonResponse(data)
        
        if obj=='storehouse':                   
            objs=Storehouse.objects.filter(external_code=sap_code,company_id=int(company_id))
            if action=='add':
                if objs:
                    e.append(obj+" with sap_code "+sap_code+" already exists")
                if 'name' not in d:
                    e.append('name of '+obj+' missing')
                else:
                    objs=Storehouse.objects.filter(name=d['name'],company_id=int(company_id))
                    if objs:
                        e.append(obj+" with name "+d['name']+" already exists")
                if not e:
                    o=Storehouse(company_id=int(company_id),name=d['name'],external_code=sap_code,is_delete=is_delete)
                    o.save()
                    #data['id']=o.id
                    is_ok='true'
            elif action=='edit':                
                if 'name' not in d:
                    e.append('name of '+obj+' missing')
                if objs:
                    if not e:
                        o=objs[0]
                        o.name=d['name']
                        o.is_delete=is_delete
                        o.save()
                        is_ok='true'    
                else:                    
                    e.append("sap code ["+sap_code+"] of "+obj+" not exits")
            else:
                e.append('action error')                                    
        elif obj=='customer':              
            objs=Company.objects.filter(external_code=sap_code,parent_id=int(company_id))
            #2021-12-24 汉得二级传 category_code,一级反而不传
            cat=None
            if 'category_code' in d:
                cat=d['category_code']
            #2022-01-14 汉得经销商,产品不管新增还是修改,全部推到添加接口
            if action=='add':                
                #if objs:
                #    e.append(obj+" with sap_code "+sap_code+" already exists")                
                if 'province' not in d or 'city' not in d or 'address' not in d :
                    e.append('province/city/address missing')
                #if 'name' not in d:
                #    e.append('name of '+obj+' missing')
                #else:
                #    objs=Company.objects.filter(name=d['name'],parent_id=int(company_id))
                #    if objs:
                #        e.append(obj+" with name "+d['name']+" already exists")    
                if not e:                    
                    div=None
                    if 'division_code' in d:
                        divs=SalesDivision.objects.filter(code=d['division_code'])
                        if divs:
                            div=divs[0]
                    subdiv=None
                    if 'subdivision_code' in d:
                        subdivs=SubSalDivision.objects.filter(code=d['subdivision_code'])
                        if subdivs:
                            subdiv=subdivs[0]
                    #2022-01-14
                    if objs:
                        o=objs[0]                        
                        o.is_delete=is_delete
                        o.save()

                        o.name=d['name']
                        try:
                            o.save()
                            is_ok='true' 
                        except Exception:
                            pass
                    else:
                        o=Company(parent_id=int(company_id),category_id=1,name=d['name'],external_code=sap_code,
                            province=d['province'],city=d['city'],address=d['address'],is_delete=is_delete,
                            division =div,sub_division=subdiv,external_category=cat)
                        try:
                            o.save()
                            is_ok='true' 
                        except Exception:
                            pass
                    #data['id']=o.id
                     
            elif action=='edit':                
                if 'name' not in d:
                    e.append('name of '+obj+' missing')
                if 'province' not in d or 'city' not in d or 'address' not in d:
                    e.append('province/city/address')
                if objs:
                    if  not e:
                        o=objs[0]         
                        if 'division_code' in d:
                            divs=SalesDivision.objects.filter(code=d['division_code'])
                            if divs:
                                o.division=divs[0]
                        if 'subdivision_code' in d:
                            subdivs=SubSalDivision.objects.filter(code=d['subdivision_code'])
                            if subdivs:
                                o.sub_division=subdivs[0]                    
                        o.name=d['name']
                        o.province=d['province']
                        o.city=d['city']
                        o.address=d['address']
                        o.external_category=cat
                        o.is_delete=is_delete
                        o.save()
                        is_ok='true'
                else:                                        
                    e.append("sap code ["+sap_code+"] of "+obj+" not exits")
            else:
                e.append('action error')   
        elif obj=='product':                     
            objs=Product.objects.filter(external_code=sap_code,company_id=int(company_id))
            if action=='add':
                w=[]
                if objs:
                    e.append(obj+" with sap_code "+sap_code+" already exists")                
                if 'series_code' not in d or 'package' not in d or 'alcohol'  not in d or not 'volume' in d or 'legacy_code' not in d:
                    e.append('series_code/package/alcohol/volume/legacy_code missing')
                if 'name' not in d:
                    e.append('name of '+obj+' missing')
                else:
                    objs=Product.objects.filter(name=d['name'],company_id=int(company_id))
                    if objs:
                        e.append(obj+" with name "+d['name']+" already exists")
                if not e:   
                    o=Product(company_id=int(company_id),name=d['name'],external_code=sap_code,remark=d['legacy_code'],external_series=d['series_code'],is_delete=is_delete)
                    o.save()
                    #data['id']=o.id
                    is_ok='true'
                    #categoryprop #1;"flavor";"香型" #2;"alcohol";"酒精度"  #3;"volume";"净含量"  #4;"package";"包装(每箱瓶数)"   
                    if d['alcohol'].__class__.__name__!='int' or d['volume'].__class__.__name__!='int':
                        w.append('alcohol/volume should be integer')
                    alc=d['alcohol']
                    if alc:
                        alc=int(str(alc).replace('度',''))
                        ps=ProductProp.objects.filter(company_id=int(company_id),categoryprop_id=2,value=alc)
                        if ps:                        
                            p=PrdProps(product_id=o.id,prd_property=ps[0])
                            p.save()
                    vol=d['volume']
                    if vol:
                        if str(vol).isdigit():
                            vol=int(vol)
                            ps=ProductProp.objects.filter(company_id=int(company_id),categoryprop_id=3,value=d['volume'])
                            if ps:                        
                                p=PrdProps(product_id=o.id,prd_property=ps[0])
                                p.save()
                    if d['package']:
                        try:
                            eval(d['package'])
                            ps=ProductProp.objects.filter(company_id=int(company_id),categoryprop_id=4,value=d['package'])
                            if ps:                        
                                p=PrdProps(product_id=o.id,prd_property=ps[0])
                                p.save()
                        except Exception:
                            w.append('package should be calculable')
                if w:
                    data['warning']=','.join(w)
                else:
                    data['warning']=''
            elif action=='edit':                
                if 'name' not in d:
                    e.append('name of '+obj+' missing')
                if 'series_code' not in d  or 'legacy_code' not in d:
                    e.append('series_code/package/alcohol/volume/legacy_code missing')
                if objs:
                    if not e:
                        o=objs[0]
                        o.name=d['name']                    
                        o.remark=d['legacy_code']
                        o.external_series=d['series_code']
                        o.is_delete=is_delete
                        o.save()
                        is_ok='true'
                else:                    
                    e.append("sap code ["+sap_code+"] of "+obj+" not exits")
            else:
                e.append('action error')   
        elif obj=='division':                      
            objs=SalesDivision.objects.filter(code=sap_code,company_id=int(company_id))
            if action=='add':
                if objs:
                    e.append(obj+" with sap_code "+sap_code+" already exists")
                if 'name' not in d:
                    e.append('name of '+obj+' missing')   
                else:
                    objs=SalesDivision.objects.filter(name=d['name'],company_id=int(company_id))
                    if objs:
                        e.append(obj+" with name "+d['name']+" already exists")
                if not e:
                    o=SalesDivision(company_id=int(company_id),name=d['name'],code=sap_code,is_delete=is_delete)
                    o.save()
                    #data['id']=o.id
                    is_ok='true'
            elif action=='edit':                
                if 'name' not in d:
                    e.append('name of '+obj+' missing')
                if objs:
                    if  not e:
                        o=objs[0]
                        o.name=d['name']
                        o.is_delete=is_delete
                        o.save()
                        is_ok='true'
                else:                    
                    e.append("sap code ["+sap_code+"] of "+obj+" not exits")
            else:
                e.append('action error')   
        elif obj=='subdivision':                   
            objs=SubSalDivision.objects.filter(code=sap_code,company_id=int(company_id))
            if action=='add':
                div=None
                if objs:
                    e.append(obj+" with sap_code "+sap_code+" already exists")
                if 'name' not in d:
                    e.append('name of '+obj+' missing')   
                else:
                    objs=SubSalDivision.objects.filter(name=d['name'],company_id=int(company_id))
                    if objs:
                        e.append(obj+" with name "+d['name']+" already exists")
                if 'division_code' not in d:
                    e.append('division_code not mission')
                else:
                    ds=SalesDivision.objects.filter(code=d['division_code'],company_id=int(company_id))
                    if ds:
                        div=ds[0]
                    else:
                        e.append('division_code not exists')
                if not e:
                    o=SubSalDivision(company_id=int(company_id),name=d['name'],code=sap_code,division=div,is_delete=is_delete)
                    o.save()
                    #data['id']=o.id
                    is_ok='true'
            elif action=='edit':                
                if 'name' not in d:
                    e.append('name of '+obj+' missing')
                if 'division_code' not in d:
                    e.append('division_code not mission')
                else:
                    ds=SalesDivision.objects.filter(code=d['division_code'])
                    if ds:
                        div=ds[0]
                    else:
                        e.append('division_code not exists')
                if objs:
                    if not e:
                        o=objs[0]
                        o.division=div
                        o.name=d['name']
                        o.is_delete=is_delete
                        o.save()
                        is_ok='true'
                else:                    
                    e.append("sap code ["+sap_code+"] of "+obj+" not exits")
            else:
                e.append('action error')  

        #2021-09-28 汉得推送经销商库存平调单
        elif obj=='stock' and action=='transfer':  
            if 'from_agent' not in d or 'to_agent' not in d or 'products' not in d or 'voucher_no' not in d: 
                e.append('from_agent/to_agent/proucts/voucher_no required')
            else:
                fr=d['from_agent']
                to=d['to_agent']
                ps=d['products']
                vn=d['voucher_no']
                if 'sap_code' not in fr or 'name' not in fr:
                    e.append('sap_code/name required in from_agent')
                if 'sap_code' not in fr or 'name' not in to:
                    e.append('sap_code/name required in to_agent')

                cur.execute("select count(*) from inventory_batch where code='HD_"+vn+"'")
                r=cur.fetchone()
                if r:                    
                    e.append('duplicated voucher_no:'+vn)
                if e:
                    s="update z_3rd_hand set is_ok=false,err='"+','.join(e)+"' where id="+id
                    cur.execute(s)
                else:  
                    s="select "+\
                        "(select id from company_company where ancestor_id=1 and external_code='"+fr['sap_code']+"'),"+\
                        "(select id from company_company where ancestor_id=1 and external_code='"+to['sap_code']+"')"                    
                    cur.execute(s)
                    r=cur.fetchone()
                    if r:
                        if not r[0]:
                            e.append('sap_code not exists for from_agent')
                        if not r[1]:
                            e.append('sap_code not exists for to_agent')
                        if not e:
                            cid_fr=str(r[0])
                            cid_to=str(r[1])
                            url= request.scheme+'://'+request.get_host()+'/inventory/fg/update/'                                   
                            t=datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")                            
                            m2=hashlib.md5((inv_sK+t).encode('utf8'))                            
                            key=m2.hexdigest()    
                            for p in ps:
                                if 'sap_code' not in p or 'labels' not in p:
                                    e.append('sap_code/labels required in products')
                                else:                                    
                                    pc=p['sap_code']                                
                                    cur.execute("select id from product_product where external_code='"+pc+"'")
                                    rr=cur.fetchone()
                                    if rr:
                                        pid=str(rr[0])
                                        #入库
                                        dd={
                                            'bill_number':'HD_'+vn,    
                                            'voucher_id':"1",    
                                            'product_id':pid,
                                            'company_id':cid_to,                                            
                                            "codes":p['labels'],
                                            'key':key, 
                                            'time':t,                                           
                                            'user_id':"1",                                            
                                            'voucher_time':t,                                            
                                        }
                                        rt =requests.post(url,json=dd)                                        
                                        #出库
                                        dd={
                                            'bill_number':'HD_'+vn,   
                                            'voucher_id':"2",
                                            'product_id':pid, 
                                            'company_id':cid_fr,
                                            "codes":p['labels'],
                                            'to_company_id':cid_to,     
                                            'key':key,                                                                                       
                                            'time':t,                                            
                                            'user_id':"1",                                            
                                            'voucher_time':t,                                            
                                        }
                                        rt =requests.post(url,json=dd)                                        
                                    else:
                                        e.append('sap_code "'+pc+'"'+' of product not exists')                                      
                    else:
                        e.append('sap_code not exists for from_agent and to_agent')
        
        #SubSalDivision division
        else:
            e.append('invalid object name')

        #处理结果
        if e:
            err=','.join(e)
            data['e']=err
            err=err[:100]        
        s="update z_3rd_hand set is_ok="+is_ok+",result='"+json.dumps(data)+"',err='"+err+"' where id="+id
        cur.execute(s)                
        return JsonResponse(data)


    

    
    #2021-10-09
    elif vendor=='bz':                #酒鬼内参-博智
        data={'e':'','is_ok':0}
        d=None    
        id=None
        is_delete=False
        if 'data' in r:
            d=r['data']
        sap_code=None
        name=None

        e=[]
        is_ok='false'
        err="null"               
        if d:
            s="INSERT INTO z_3rd_hand(obj, src, vender) values "+\
                "('"+obj+"','"+json.dumps(d)+"','"+vendor+"') returning id"
            cur.execute(s)
            rr=cur.fetchone()
            id=str(rr[0])
            if 'name' in d:
                name=d['name']
            else:                
                e.append('缺少参数name')
            if 'code' in d:
                sap_code=d['code']
            else:
                e.append("缺少参数code")
            #if 'action' in r:
            #    if r['action'] in ['add','edit']:
            #        action=r['action']
            #    else:
            #        e.append('action type error')
            #else:
            #    e.append("action type required")
        
            if 'status' in d:
                if int(d['status'])==0:
                    is_delete=True  
        else:
            e.append('缺少data字段')
        if e:
            err=",".join(e)
            data['e']=err
            err=err[:100]
            s="update z_3rd_hand set is_ok="+is_ok+",result='"+json.dumps(data)+"',err='"+err+"' where id="+id
            cur.execute(s)
            return JsonResponse(data)

        if obj=='agent': 
            license_number=None
            contacts=None
            c=None
            parents=[]
            if 'province' not in d or 'city' not in d or 'address' not in d or 'parents' not in d or 'cell_phone' not in d or 'license_number' not in d or 'contacts' not in d:
                e.append('parents/province/city/address/cell_phone/contacts/license_number可以为空数组/空字符串，但不能缺失')
            if 'license_number' in d:
                if d['license_number']:
                    license_number=d['license_number']
            if 'contacts' in d:
                if d['contacts']:
                    contacts=d['contacts']            
            #2021-10-12 内参-博智：不能确定当前是新建还是编辑，由天鉴从code判断
            if  not d['code']:            
                data['e']="code can not be a empty string"
                s="update z_3rd_hand set is_ok="+is_ok+",result='"+json.dumps(data)+"',err='"+data['e']+"' where id="+id
                cur.execute(s)
                return JsonResponse(data)
            objs=Company.objects.filter(external_code=d['code'],ancestor_id=int(company_id))
            cs=Company.objects.filter(name=d['name'],ancestor_id=int(company_id))
            if objs:                    #修改
                o=objs[0]
                if cs:
                    c=cs[0]
                    if c.id!=o.id:
                        e.append("'"+d['code']+"'要修改成的新的经销商名称 "+d['name']+" 已存在")
            else:                       #新建
                cs=Company.objects.filter(name=d['name'],ancestor_id=int(company_id))
                if cs:                    
                    e.append("新建经销商 "+d['name']+" 已存在")
                o=Company(ancestor_id=int(company_id),category_id=1,hierarchy_id=1,version=1,external_code=d['code'])
            if e:
                err=",".join(e)
                data['e']=err
                err=err[:100]
                s="update z_3rd_hand set is_ok="+is_ok+",result='"+json.dumps(data)+"',err='"+err+"' where id="+id  
                cur.execute(s)
                return JsonResponse(data)            
            div=None
            if 'division' in d:
                divs=SalesDivision.objects.filter(name=d['division'])
                if divs:
                    o.division=divs[0]
            subdiv=None
            if 'subdivision' in d:
                subdivs=SubSalDivision.objects.filter(code=d['subdivision'])
                if subdivs:
                    o.sub_division=subdivs[0]    
            if d['parents']:            
                for prt in d['parents']:
                    ps=Company.objects.filter(external_code=prt['code'],ancestor_id=int(company_id))
                    if ps:
                        p=ps[0]
                        parents.append({'id':p.id,'name':p.name+'['+p.level.name+']'})
                    else:
                        p=Company.objects.get(id=1644)
            else:
                p=Company.objects.get(id=1644)
            o.name=d['name']
            o.parent=p
            o.parents=parents
            o.province=d['province']
            o.city=d['city']
            o.address=d['address']                       
            o.is_delete=is_delete
            o.cell_phone=d["cell_phone"]
            o.license_number=license_number
            o.contacts=contacts
            o.save()
            is_ok='true'
            data['is_ok']=1
            s="update z_3rd_hand set is_ok="+is_ok+",result='"+json.dumps(data)+"',err='"+err+"' where id="+id
            cur.execute(s)      
        return JsonResponse(data)
        
    
#2022-01-13
def salesorder_edit(company_id,data):
    #要求第三方按指定格式参数名传发货单（销售订单）
    order_code=data['order_code']
    customer_code=data['customer']['code']
    customer_name=data['customer']['name']
    ordertype_code='null'
    ordertype_name='null'    
    if 'order_type' in data:
        ordertype_code="'"+data['order_type']['code']+"'"
        ordertype_name="'"+data['order_type']['name']+"'"    
    cur=connection.cursor()  
    t=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    objs=SalesOrder.objects.filter(company_id=int(company_id),code=order_code)
    if objs:
        o=objs[0]
        o.edit_logs.append({'time':t,'data':data})
    else:
        o=SalesOrder(company_id=int(company_id),code=order_code)        
        o.edit_logs=[{'time':t,'data':data}]
    o.save()
    s="update erp_salesorder set "+\
        "ordertype_code="+ordertype_code+","+\
        "ordertype_name="+ordertype_name+","+\
        "customer_code='"+customer_code+"',"+\
        "customer_name='"+customer_name+"',"+\
        "customer_id=(select id from company_company where ancestor_id="+str(company_id)+" and external_code='"+customer_code+"' limit 1),"+\
        "ordertype_id=(select id from erp_ordertype where company_id="+str(company_id)+" and code='"+customer_code+"' limit 1) "+\
        "where id="+str(o.id)
    cur.execute(s)
    #如内参公司，订单修改后会再次同步过来，删除订单明细记录
    cur.execute("delete from erp_salesorderdetails where salesorder_id="+str(o.id))
    sIns="insert into erp_salesorderdetails(salesorder_id,company_id,"+\
        "product_name,product_code,product_id,"+\
        "ordertype_code,ordertype_name,ordertype_id,"+\
        "store_name,store_code,store_id,"+\
        "unit_id,unit_name,qty,qty0) values "
    a=[]
    for p in data['products']:
        store_code=p['storehouse']['code']        
        store_name=p['storehouse']['name'] 
        product_code=p['code']
        product_name=p['name']
        ordertype_code=p['order_type']['code']
        ordertype_name=p['order_type']['name']
        unit='null'
        if 'unit' in p:
            unit="'"+p['unit']+"'"
        q=str(int(p['qty']))
        a.append("("+str(o.id)+","+str(company_id)+","+\
            "'"+product_name+"','"+product_code+"',"+\
                "(select id from product_product where company_id="+str(company_id)+" and external_code='"+product_code+"' limit 1),"+\
            "'"+ordertype_code+"','"+ordertype_name+"',"+\
                "(select id from erp_ordertype where company_id="+str(company_id)+" and code='"+ordertype_code+"' limit 1),"+\
            "'"+store_name+"','"+store_code+"',"+\
                "(select id from inventory_storehouse where company_id="+str(company_id)+" and external_code='"+store_code+"' limit 1),"+\
            "null,"+unit+","+q+","+q+\
        ")")
    if a:
        cur.execute(sIns+",".join(a))
    cur.close()
    return     

#2021-08-04 接收第三方发货单数据接口
@csrf_exempt
def sync_order(request,company_id,vendor):   
    data = {'e': '', 'result':{} }
    sKs={
        '19267':{                               #易臻生物科技
            'ufida':    'yz$3Yl1z%)20)0b1k4o'   #用友
        },                 
        '1644': {                               #内参
            'kingdee':  'nc$tYal1C%)T0@)d1Wn'   #金蝶
        },                 
        '1':    {                               #酒鬼
            'tms':      'tms$3al1i%)20@)0814'   #TMS
        },                  
    }       
    if company_id not in sKs:
        data['e']='company not authorized'
        return JsonResponse(data)
    if vendor not in sKs[company_id]:
        data['e']='invalid vendor name'
        return JsonResponse(data)
    sK=sKs[company_id][vendor]
    e = time_key_check(request, sK)
    if e:
        data['e'] = e
        return JsonResponse(data)
    if request.method=='GET':
        data['e']='http post is prefered'
        return JsonResponse(data)    
    try:
        r=json.loads(request.body)
    except Exception:
        data['e']='invalid json structrue'
        return JsonResponse(data)
    if 'data' not in r:
        data['e']='data missing'
        return JsonResponse(data)
    d=r['data']
    if company_id=='1644':
        if 'dn_number' not in d or 'products' not in d  or 'customer' not in d:
            data['e']='dn_number/customer/products missing in data'
            return JsonResponse(data)

    else:
        if 'dn_number' not in d or 'products' not in d or 'storehouse' not in d or 'customer' not in d:
            data['e']='dn_number/customer/storehouse/products missing in data'
            return JsonResponse(data)  


    order_code=None
    if 'order_code' in d:
        order_code=d['order_code']   
    dn=d['dn_number']
    ss=OrderSync.objects.filter(company_id=int(company_id),dn_number=dn)
    if ss:
        o=ss[0]
        o.tm_modify=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    else:
        o=OrderSync(company_id=int(company_id),dn_number=dn)
    order_type=None
    if 'order_type' in d:
        t=d['order_type']
        order_type={'code':t['code'],'name':t['name']}
    o.through_3rd=vendor
    o.order_code=order_code
    o.products=d['products']
    o.customer=d['customer']
    if 'storehouse' in d:
        o.storehouse=d['storehouse']
    o.data=d  
    if order_type:
        o.order_type=order_type
    o.is_ok=True
    o.save()

    #2022-01-14
    if int(company_id)==1644:
        salesorder_edit(company_id,d)
    return JsonResponse(data)



#2022-01-17 检查订单产品剩余发货数量
def salesorder_remaining(company_id,order_id):
    products=[]  
    cur=connection.cursor()   
    #qty0,销售订单最小单位数量
    s="select o.product_id,p.name,o.ordertype_id,t.name,"+\
        "o.qty0, coalesce(d.x,0)*p.p+coalesce(d.p,0),coalesce(d.x,0),coalesce(d.p,0), p.p,"+\
        "t.code,s.id,s.name,s.external_code,p.h,p.p,coalesce(o.unit_name,'') from "+\
        "(select product_id,ordertype_id,store_id,unit_name,sum(qty0) qty0 from erp_salesorderdetails where "+\
            "company_id="+str(company_id)+" and salesorder_id="+str(order_id)+" group by product_id,ordertype_id,store_id,unit_name"+\
        ") o "+\
        "left outer join "+\
        "(select product_id,sum(x) x,sum(p) p from "+\
            "(select product_id,"+\
                "case when label_pkg=3 then 1 else 0 end x,"+\
                "case when label_pkg=1 then 1 else 0 end p "+\
                "from inventory_stkdetails where company_id="+str(company_id)+" and vtype_id=2 and batch_id in "+\
                    "(select id from inventory_batch where salesorder_id="+str(order_id)+")"+\
            ") s "+\
            "group by product_id"+\
        ") d "+\
        "on o.product_id=d.product_id "+\
        "left outer join "+\
        "(select id,name,coalesce(qty_h,1) h,coalesce(qty_p,1) p from product_product where company_id="+str(company_id)+") p "+\
        "on o.product_id=p.id "+\
        "left outer join "+\
        "(select id,code,name from erp_ordertype where company_id="+str(company_id)+") t "+\
        "on o.ordertype_id=t.id "+\
        "left outer join "+\
        "(select id,external_code,name from inventory_storehouse where company_id="+str(company_id)+") s "+\
        "on o.store_id=s.id"   
    cur.execute(s)
    for r in cur.fetchall(): 
        #订单明细里的数量qty0，为最小单位的数量
        q=r[4]
        X=int(q/r[8])
        P=q % r[8]        
        #剩余发货数量,如果已发数量大于订单数，取0
        q0=max(r[4]-r[5],0)
        X0=int(q0/r[8])
        P0=q0 % r[8]
        products.append({
            'id':r[0],
            'name':r[1],
            'qty':r[4],
            'unit':r[15],
            'storehouse':{'id':r[10],'name':r[11],'code':r[12]},
            'order_type':{'id':r[2],'code':r[9],'name':r[3]},
            'x':X,
            'p':P,
            'remaining':{'x':X0,'p':P0}   
        })        
    cur.close()
    return products


#2021-08-05 从第三方发货单号取发货信息
def get_order_infor(company,dn_number):
    error_codes={
        1:      '发货单号不存在',
        100:    '客户编码不存在',
        201:    '所有产品编码不存在',
        210:    '部分产品编码不存在',
        300:    '仓库编码不存在'
    }
    
    data={'error_code':0, 'e':'', 'data':{
        'dn_number':'',
        'order_number':'',        
        'customer':{}, 
        'storehouse':{},  
        'products':[], 
        'products_total':{'qty':0,'cnt':0} 
        } 
    }
    ds=OrderSync.objects.filter(company_id=company.id,dn_number=dn_number).order_by('-id')
    if not ds:
        data['e']='deliver note number not exists'
        data['error_code']='1'
        return data
    d=ds[0]  
    
    if d.dn_number:
        data['data']['dn_number']=d.dn_number
    if d.order_code:
        data['data']['order_number']=d.order_code   
    
    ec=[]
    cs=Company.objects.filter(external_code=d.customer['code'],ancestor_id=company.id)
    if cs:
        c=cs[0]
        data['data']['customer']={'id':c.id,'name':c.name}
    else:
        ec.append('100')
        data['e']='customer code not exists'
    if d.storehouse:
        ss=Storehouse.objects.filter(company_id=company.id,external_code=d.storehouse['code'])
        if ss:
            s=ss[0]
            data['data']['storehouse']={'id':s.id,'name':s.name}
        else:
            #data['error_code']=210
            ec.append('210')
    i=0
    q=0
    k=0



    #2022-01-18已启用ERP销售订单
    if company.cfg_company.erp_salesorder:
        cur=connection.cursor()
        s="select id from erp_salesorder where company_id="+str(company.id)+" and code='"+d.order_code+"'"
        cur.execute(s)
        r=cur.fetchone()
        if r:
            data['data']['products']=salesorder_remaining(company.id,r[0])
        cur.close()
    else:
        for p in d.products:
            q=int(float(q))+int(float(p['qty']))
            i=i+1
            u=''
            if 'unit' in p:
                u=p['unit']
            ps=Product.objects.filter(company_id=company.id,external_code=p['code'])
            if ps:          
                data['data']['products'].append({
                    'id':ps[0].id,
                    'name':ps[0].name,
                    'qty':p['qty'],
                    'unit':u
                })
                k=k+int(float(p['qty'])) 





    '''
    for p in d.products:
        q=int(float(q))+int(float(p['qty']))
        i=i+1
        u=''
        if 'unit' in p:
            u=p['unit']
        ps=Product.objects.filter(company_id=company.id,external_code=p['code'])
        if ps:
            if 'storehouse' in p:
                s="select coalesce(id,0) from inventory_storehouse where external_code='"+p['storehouse']['code']+"'"
                cur=connection.cursor()
                cur.execute(s)
                rrr=cur.fetchone()     
                st={'id':rrr[0],'code':p['storehouse']['code'],'name':p['storehouse']['name']}    

                #data['data']['products'].append({
                #    'id':ps[0].id,
                #    'name':ps[0].name,
                #    'qty':p['qty'],
                #    'unit':u,
                #    'storehouse':st,
                #    'order_type':p['order_type']
                #})
                X=0
                P=0
                X0=0
                P0=0
                if company.id==1644:
                    #内参金蝶传过来的数量单位是瓶，要传给扫码枪箱数和瓶数
                    s="select id,name,external_code,"+\
                        "(select value from product_productprop where id=(select prd_property_id from product_prdprops "+\
                            "where (select categoryprop_id from product_productprop where id=prd_property_id and product_id=prd.id)=4)) spec "+\
                        "from product_product prd where company_id=1644 and id="+str(ps[0].id)
                    cur.execute(s)
                    rr=cur.fetchone()                    
                    bts=eval(rr[3]) 
                    X=int( int(p['qty'])/bts )
                    P= int(p['qty']) % bts   
                    #2022-01-12 暂时用订单数量和单位,给扫码枪APP做修改,今天晚些时候用订单实际剩余和单位替换这些变量 
                    X0=X
                    P0=P
                data['data']['products'].append({
                    'id':ps[0].id,
                    'name':ps[0].name,
                    'qty':p['qty'],
                    'unit':u,
                    'storehouse':st,
                    'order_type':p['order_type'],
                    'x':X,
                    'p':P,
                    'remaining':{'x':X0,'p':P0}
                })
            else:        
                data['data']['products'].append({
                    'id':ps[0].id,
                    'name':ps[0].name,
                    'qty':p['qty'],
                    'unit':u
                })
            k=k+int(float(p['qty'])) 
            #cur.close()
    '''    





    if not data['error_code']:
        if k==0:
            #data['error_code']=201
            ec.append('201')
        elif k<q:
            #data['error_code']=210
            ec.append('210')
    if ec:
        data['error_code']=','.join(ec)
    data['data']['products_total']={'qty':q,'cnt':i}
    
    #如内参金蝶传过来的发货单,仓库在产品字段内
    if company.id!=1644:        
        #2021-09-01 相同产品合并，数量取整箱数
        ps={}
        for p in data['data']['products']:
            if p['id'] in ps:
                #ps[p['id']]['qty']=ps[p['id']]['qty']+p['qty']
                ps[p['id']]['qty']=int(float(ps[p['id']]['qty']))+int(float(p['qty']))
            else:
                ps[p['id']]={'id':p['id'],'name':p['name'],'qty':p['qty'],'unit':p['unit']}
    
        result=[]
        for key in ps:
            p=ps[key]
            if round(float(p['qty']),6)==math.ceil(float(p['qty'])):
                p['qty']=math.ceil(float(p['qty']))
            else:
                p['qty']=math.floor(float(p['qty']))

            #2022-01-12 暂时用订单数量和单位,给扫码枪APP做修改,今天晚些时候用订单实际剩余和单位替换这些变量
            q=p['qty']
            u=p['unit']
            result.append({
                'id':p['id'],
                'name':p['name'],
                'qty':p['qty'],
                'unit':p['unit'],
                'remaining':{
                    'qty':q,
                    'unit':u
                }
            })
            #result.append({'id':p['id'],'name':p['name'],'qty':p['qty'],'unit':p['unit']})
        data['data']['products']=result


    return data


#2021-08-05 扫码枪从第三方发货单号取发货信息
def app_get_order_info(request):
    data={'e':'','data':{},'error_code':''}
    uid=request.GET.get('user_id','')
    if not uid:
        data['e']='user id missing'
        return JsonResponse(data)
    e = time_key_check(request, sK)
    if e:
        data['e'] = e
        return JsonResponse(data)

    us=User.objects.filter(id=int(uid))
    if not us:
        data['e']='user id not exists'
        return JsonResponse(data)
    dn=request.GET.get('dn_number','')
    if not dn:
        data['e']='delivery note number missing'
        return JsonResponse(data)    
    u=us[0]    
    d=get_order_infor(u.company,dn)    
    return JsonResponse(d)


#2021-08-26
@login_required
def vendor_action(request,what,id=None):
    if what=='list':
        cur=connection.cursor()
        clmns=['第三方单位名称','所属企业','代码','物流同步','上线','跳转链接','指定产品','指定经销商']
        d=[]
        s="select (select name from company_company where id=company_id),"+\
            "name,coalesce(code,''),coalesce(redirect,''),"+\
            "case when synclogistics then '是' else '否' end,"+\
            "case when is_active then '是' else '否' end,"+\
            "array_to_string(array((select jsonb_array_elements(products)->>'name')),', '),"+\
            "array_to_string(array(select jsonb_array_elements(agents)->>'name'),', '),"+\
            "id from thirdparty_vendor"
        cur.execute(s)
        for r in cur.fetchall():
            d.append([ '<a href="/thirdparty/vendor/edit/'+str(r[8])+'/">'+r[1]+'</a>', r[0], r[2],r[4],r[5],r[3],r[6],r[7] ])
        return render(request,'thirdparty/list_vendor.html',{'clmns':clmns,'data':d,'murl':'/thirdparty/vendor/edit/'})
    elif what=='edit':
        if id:
            obj=Vendor.objects.get(id=int(id))
        else:
            obj=Vendor()
        if request.method=='GET':
            form=VendorForm(instance=obj)            
        else:
            form=VendorForm(request.POST,instance=obj)
            if form.is_valid():
                form.save()
        return render(request,'thirdparty/edit_vendor.html',{'form':form})

'''
#消费者扫码跳转第三方链接
def label_redirect(request,company_id):
    data={'e':'','flag':'','remedy':'','url':''}
    flag=''
    remedy=''

    


    #瓶盖码/活动码小圆标
    c=request.GET.get('code','')
    if not c:
        data['e']='parameter code missing'
        return JsonResponse(data) 
    
    #传过来的标签一定是加密码(瓶盖码或活动小圆标)
    if c.isdigit():
        data['e']='非加密码'
        return JsonResponse(data)

    
    cur=connection.cursor()    

    #先检查传过来的标签是在线绑定的瓶盖码,要把盖码转为对应瓶标  
    s="select coalesce((select bottle from label.bottlecap where label='"+c+"' order by id desc limit 1),"+\
        "(select bottle from logistics.z_bottlecap_binding where label='"+c+"' order by id desc limit 1))"
    cur.execute(s)
    r=cur.fetchone()    
    
    if r[0]:
        c=r[0]        
    else:
        cur.execute("select bottle,cap_new,url from logistics.z_jg_bottlecap_remedy where cap_old='"+c+"' limit 1")
        rr=cur.fetchone()
        if rr:
            #2021-11-05 一些测试性跳转,直接返回跳转链接
            if rr[2]:
                data['url']=rr[2]
                cur.close()
                return JsonResponse(data)


            if rr[0]:
                c=rr[0]
                remedy=rr[1]
                data['remedy']=remedy
            else:
                c=decrypt_11h(c) 
        else:     
            c=decrypt_11h(c)    

    if not c:
        data['e']='标签号码解密失败'
        cur.close()
        return JsonResponse(data)

    #酒鬼酒标签，含内参公司
    if company_id=='1':
        q=LabelQuery(company_id,c)        

        if q.infor['label_pk_id']: 
            id=str(q.infor['label_pk_id'])
            s="select company_to_id,product_id,to_char(dt,'yyyy-mm-dd'),"+\
                "coalesce((select code from inventory_batch where id=batch_id),'') "+\
                "from inventory_stkdetails where company_id="+company_id+" "+\
                "and vtype_id=2 and is_delete=false and label_pk_id="+id+" order by id desc"
            cur.execute(s)
            r=cur.fetchone()      
            if r:
                agent_id=str(r[0])
                product_id=str(r[1])
                code=r[3]
                dt='2021-08-25'
                #首先判断是否要跳转到汉得 
                if r[2]>=dt and '一物一码' in code:                 #2021-09-01 酒鬼酒罗工，只有单号含一物一码的发货跳汉得
                    s='select products @> '+"'"+'[{"id":"'+product_id+'"}]'+"' and "+\
                        'agents @> '+"'"+'[{"id":"'+agent_id+'"}]'+"' "+\
                        "from thirdparty_vendor where company_id="+company_id+" and code='HD'"
                    cur.execute(s)
                    r=cur.fetchone()
                    if r[0]:                           
                        flag='HD'
                        data['flag']=flag
                        cur.close()
                        return JsonResponse(data)                        
                #再判断是否要跳转到透云
                s='select products @> '+"'"+'[{"id":"'+product_id+'"}]'+"' "+\
                    "from thirdparty_vendor where company_id="+company_id+" and code='TY'"
                cur.execute(s)
                r=cur.fetchone()
                if r[0]:
                    flag='TY'
                    data['flag']=flag
                    cur.close()
                    return JsonResponse(data)   
                #print(product_id)
                #是否跳天鉴品鉴页面
                s='select products @> '+"'"+'[{"id":"'+product_id+'"}]'+"' "+\
                    "from thirdparty_vendor where company_id="+company_id+" and code='PJ'"
                cur.execute(s)
                r=cur.fetchone()
                if r[0]:
                    flag='PJ'
                    data['flag']=flag
                    cur.close()
                    return JsonResponse(data)                   
            #2021-09-16
            else:   #没有销售出库记录，查询产品信息  
                p=q.get_product()    
                if p['id']:
                    product_id=str(p['id'])                
                    s='select products @> '+"'"+'[{"id":"'+product_id+'"}]'+"' "+\
                        "from thirdparty_vendor where company_id="+company_id+" and code='PJ'"
                    cur.execute(s)
                    r=cur.fetchone()
                    if r[0]:
                        flag='PJ'
                        data['flag']=flag
                        cur.close()
                        return JsonResponse(data)     
        #可能是系统之外的历史数据，查看跳转表
        cur.execute("select flag from z_bottle_redirect where label='"+c+"'")
        r=cur.fetchone()
        if r:            
            flag=r[0]
            data['flag']=flag 
    cur.close()
    return JsonResponse(data)
'''

#2021-11-30修改
#消费者扫码跳转第三方链接
def label_redirect(request,company_id):
    data={'e':'','flag':'','remedy':'','url':''}
    flag=''
    remedy='' 
    #瓶盖码/活动码小圆标
    c=request.GET.get('code','')
    if not c:
        data['e']='parameter code missing'
        return JsonResponse(data)     
    #传过来的标签一定是加密码(瓶盖码或活动小圆标)
    if c.isdigit():
        data['e']='非加密码'
        return JsonResponse(data)    
    cur=connection.cursor()   
    #先检查传过来的标签是在线绑定的瓶盖码,要把盖码转为对应瓶标  
    s="select coalesce((select bottle from label.bottlecap where label='"+c+"' order by id desc limit 1),"+\
        "(select bottle from logistics.z_bottlecap_binding where label='"+c+"' order by id desc limit 1))"
    cur.execute(s)
    r=cur.fetchone()    
    if r[0]:
        c=r[0]        
    else:
        cur.execute("select bottle,cap_new,url,coalesce(flag,'') from logistics.z_jg_bottlecap_remedy where cap_old='"+c+"' limit 1")
        rr=cur.fetchone()
        if rr:
            #2021-11-05 一些测试性跳转,直接返回跳转链接
            if rr[2]:
                data['url']=rr[2]
                data['flag']=rr[3]
                cur.close()
                return JsonResponse(data)
            if rr[0]:
                c=rr[0]
                remedy=rr[1]
                data['flag']=rr[3]
                data['remedy']=remedy
            else:
                c=decrypt_11h(c) 
        else:     
            c=decrypt_11h(c)   
    if not c:
        data['e']='加密码错误'
        cur.close()
        return JsonResponse(data)
    #酒鬼酒标签，含内参公司
    if company_id=='1':
        q=LabelQuery(company_id,c)  
        #物流数据在云平台系统内
        if q.infor['label_pk_id']:        
            product_id=str(q.get_product()['id'])

            #2021-12-11            品鉴酒,数据发汉得,跳转到天鉴品鉴页面，所以先判断是否为品鉴
            s='select products @> '+"'"+'[{"id":"'+product_id+'"}]'+"' from thirdparty_vendor where company_id="+company_id+" and code='PJ'"
            cur.execute(s)
            r=cur.fetchone()
            if r[0]:
                flag='PJ'
                data['flag']=flag
                cur.close()
                return JsonResponse(data)            
            else:
                s='select products @> '+"'"+'[{"id":"'+product_id+'"}]'+"' from thirdparty_vendor where company_id="+company_id+" and code='HD'"       
                cur.execute(s)
                r=cur.fetchone()   
                #是一物一码产品   
                if r[0]:                               
                    #2011年已经开始发数据给透云，酒鬼酒云平台上线2019年9月；只能先判断是否跳到汉得                
                    devs=q.get_stk_dev()
                    dt=''
                    batch_code=''
                    for d in devs:
                        if d['vtype_id']==2 and not d['is_delete']:
                            dt=d['tm'][:10]
                            batch_code=d['bill_no']
                    #云平台有发货记录            
                    if dt>'2021-11-30':
                        #判断发货日期，2021年11月30日以后的发货，跳汉得
                        flag="HD"                    
                    else:     
                        if batch_code[:3]=='ty_':
                            flag='TY'
                        else:
                            #s="select id from z_ty_outbound where label_pk_id="+str(q.infor['label_pk_id'])+" and is_return=false"  
                            s="select is_return from z_ty_outbound where label_pk_id="+str(q.infor['label_pk_id'])
                            cur.execute(s)
                            rr=cur.fetchone()
                            if rr:
                                #在透云经销商出库表内
                                if rr[0]:
                                    flag="HD"
                                else:
                                    flag="TY"
                            else:
                                #不在透云经销商出库表内
                                flag="HD"

                        #产品在透云系统,且没有出库（已经发送给汉得）
                        #if not rr:
                        #    flag='HD'
                        #else:                            
                        #    flag='TY'


                    data['flag']=flag
                    cur.close()
                    return JsonResponse(data)  
            #不是一物一码产品，看是不是酒鬼品鉴
            #else:                                 
            #    s='select products @> '+"'"+'[{"id":"'+product_id+'"}]'+"' from thirdparty_vendor where company_id="+company_id+" and code='PJ'"
            #    cur.execute(s)
            #    r=cur.fetchone()
            #    if r[0]:
            #        flag='PJ'
            #        data['flag']=flag
            #        cur.close()
            #        return JsonResponse(data)     
        #可能是云平台上线之前，历史数据，查看跳转表
        cur.execute("select flag from z_bottle_redirect where label='"+c+"'")
        r=cur.fetchone()
        if r:            
            flag=r[0]
            data['flag']=flag 
    cur.close()
    return JsonResponse(data)



#2021-11-11 接收第三方销售订单数据接口
@csrf_exempt
def sales_order(request,company_id,vendor):
    data={'e':'','result':{}}
    sKs={
        #'19267':{                               #易臻生物科技
        #    'ufida':    'yz$3Yl1z%)20)0b1k4o'   #用友
        #},                 
        '1644': {                               #内参
            'kingdee':  'nc$tYal1C%)T0@)d1Wn'   #金蝶
        },                 
        #'1':    {                               #酒鬼
        #    'tms':      'tms$3al1i%)20@)0814'   #TMS
        #},                  
    } 
    if company_id in sKs:
        sK=sKs[company_id]
    else:
        #data['e']='operation forbidden'        
        data['e']='company not authorized'
        return JsonResponse(data)

    if vendor not in sKs[company_id]:
        data['e']='invalid vendor name'
        return JsonResponse(data)
    sK=sKs[company_id][vendor]
    e = time_key_check(request, sK)
    if e:
        data['e'] = e
        return JsonResponse(data)
    if request.method=='GET':
        data['e']='http post is prefered'
        return JsonResponse(data)    
    try:
        r=json.loads(request.body)
    except Exception:
        data['e']='invalid json structrue'
        return JsonResponse(data)

    if 'order_info' not in r or 'order_details' not in r:
        data['e']='order_info,order_details missing'
        return jsonResponse(data)   

    info=r['order_info']
    details=r['order_details']
    o_flds=[
        'order_number',
        'customer_code',
        'customer_name',
        'order_type_code',
        'order_type_name',
        'invoice_type_code',
        'invoice_type_name',
        'contract_type_code',
        'contract_type_name',        
        'delivery_address',
        'delivery_method',
        'delivery_date',
        'invoice_address',
        'contacts',
        'cellphone',
        'salesmen',
        'remark',
    ]
    ee=[]
    e=[]
    for d in o_flds:
        if d not in info:
            e.append(d)
    if e:
        ee.append(','.join(e)+' required in order_info;')    
    
    #'订单编码'  
    order_number=info['order_number']
    #客户名称
    customer_code=info['customer_code']
    customer_name=info['customer_name']
    #订单类别
    order_type_code=info['order_type_code']
    order_type_name=info['order_type_name']
    #发票类别
    invoice_type_code=info['invoice_type_code']
    invoice_type_name=info['invoice_type_name']
    #'合同类型'
    contract_type_code=info['contract_type_code']
    contract_type_name=info['contract_type_name']    
    #'送货地点'
    delivery_address=info['delivery_address']
    #'提货方式'
    delivery_method=info['delivery_method']
    #'期望交货日期'
    delivery_date=None
    if info['delivery_date']:
        delivery_date=info['delivery_date']
    #'收票地址')
    invoice_address=info['invoice_address']
    #'联系人'
    contacts=info['contacts']
    #联系电话'   
    cellphone=info['cellphone']
    #销售员'  
    salesmen=info['salesmen']
    #'备注说明'
    remark=info['remark']
    if not isinstance(salesmen,list):
        data['e']='salesmen should be a array'
        return JsonResponse(data)
    o_flds=[
        'order_type_code',
        'order_type_name',
        'product_code',
        'product_name',
        'product_aging',
        'qty',
        'unit_name',
        'tax_rate',
        'price',
        'price_discount',
        'price_strategy',
        'price_invoice',
        'amount_total',
        'amount_invoice',
        'discount_item_code',
        'discount_item_name',
        'finance_account_code',
        'finance_account_name',
        'remark',        
    ]
    ds=details[0]   
    
    e=[]
    for d in o_flds:
        if d not in ds:    
            e.append(d)  
    if e:
        ee.append((','.join(e))+' required in order_details;')    
    if ee:
        data['e']=','.join(ee)
        return JsonResponse(data)
    #写入对像同步表
    if request.META.get('HTTP_X_FORWARDED_FOR'):
        ip = request.META.get("HTTP_X_FORWARDED_FOR")
    else:
        ip = request.META.get("REMOTE_ADDR")
    sync=ObjectSync(company_id=int(company_id),through_3rd=vendor,ip_address=ip,app_name='erp',obj_name='SalesOrder',src=r)
    sync.save()
    #if salesmen:
    #    salesmen=json.dumps(salesmen)
    #else:
    #    salesmen='[]'
    os=SalesOrder.objects.filter(company_id=int(company_id),code=order_number)
    if os:
        o=os[0]
    else:
        o=SalesOrder(company_id=int(company_id),code=order_number)
    o.delivery_address=delivery_address
    o.delivery_method=delivery_method
    o.delivery_date=delivery_date
    o.invoice_address=invoice_address
    o.contacts=contacts
    o.cellphone=cellphone
    o.salesmen=salesmen
    o.remark=remark
    o.customer_code=customer_code
    o.customer_name=customer_name
    #o.customer_id=
    o.invoicetype_code=invoice_type_code
    o.invoicetype_name=invoice_type_name
    #o.invoice_id=
    o.ordertype_code=order_type_code
    o.ordertype_name=order_type_name
    #o.ordertype_id=
    o.contracttype_code=contract_type_code
    o.contracttype_name=contract_type_name
    #o.contracttype_id=
    o.save()
    id=str(o.id)    
    cur=connection.cursor()       
    a=[]
    for d in details:
        #'订单类别'),blank=True,null=True)
        order_type_code=d['order_type_code']      
        order_type_name=d['order_type_name']
        #'产品编码'),max_length=10,blank=True,null=True)
        product_code=d['product_code']    
        #'产品名称'),max_length=50)
        product_name=d['product_name']
        #'产品年份'),max_length=20,blank=True,null=True)
        product_aging=d['product_aging']
        #'数量'))
        qty=d['qty']   
        #单位名称'),max_length=20,blank=True,null=True)    
        unit_name=d['unit_name']

        #'税率'),max_digits=10,decimal_places=4,blank=True,null=True)
        tax_rate='null'
        if d['tax_rate']:
            tax_rate=round(float(d['tax_rate']),4)
        #'销售单价'),max_digits=10,decimal_places=4,blank=True,null=True)   
        price='null'
        if d['price']:
            price=round(float(d['price']),4)
        #('折扣单价'),max_digits=10,decimal_places=4,blank=True,null=True)    
        price_discount='null'
        if d['price_discount']:
            price_discount=round(float(d['price_discount']),4)
        #战略价'),max_digits=10,decimal_places=4,blank=True,null=True)  
        price_strategy='null'
        if d['price_strategy']:
            price_strategy=round(float(d['price_strategy']),4)
        #'开票单价'),max_digits=10,decimal_places=4,blank=True,null=True) 
        price_invoice='null'
        if d['price_invoice']:
            price_invoice=round(float(d['price_invoice']),4)
        #'总金额'),max_digits=14,decimal_places=4,blank=True,null=True)
        amount_total='null'
        if d['amount_total']:
            amount_total=round(float(d['amount_total']),4)
        #'开票总金额'),max_digits=14,decimal_places=4,blank=True,null=True)        
        amount_invoice='null'
        if d['amount_invoice']:
            amount_invoice=round(float(d['amount_invoice']),4)
        #'折扣项目'),blank=True,null=True)
        discount_item_code=d['discount_item_code']
        discount_item_name=d['discount_item_name']
        #'对应财务费用科目'),blank=True,null=True)
        finance_account_code=d['finance_account_code']
        finance_account_name=d['finance_account_name']
        remark=d['remark']   
        ss=company_id+","+id+",'"+remark+"','"+\
            order_type_code+"','"+order_type_name+"',"+\
            "(select id from erp_ordertype where company_id="+company_id+" and code='"+order_type_code+"'),"+\
                "'"+discount_item_code+"','"+discount_item_name+"',"+\
            "(select id from erp_discountitem where company_id="+company_id+" and code='"+order_type_code+"'),"+\
                "'"+finance_account_code+"','"+finance_account_name+"',"+\
            "(select id from erp_finaceaccount where company_id="+company_id+" and code='"+order_type_code+"'),"+\
                "'"+product_code+"','"+product_name+"','"+product_aging+"',"+\
            "(select id from product_product where company_id="+company_id+" and external_code='"+product_code+"'),"+\
                "'"+unit_name+"',null,"+\
            str(qty)+","+str(tax_rate)+","+str(price)+","+str(price_discount)+","+\
            str(price_strategy)+","+str(price_invoice)+","+str(amount_total)+","+str(amount_invoice)
        a.append("("+ss+")")
    cur.execute("delete from erp_salesorderdetails where salesorder_id="+id)
    s="INSERT INTO erp_salesorderdetails("+\
        "company_id,salesorder_id,remark,"+\
        "ordertype_code, ordertype_name,ordertype_id,"+\
        "discountitem_code, discountitem_name,discountitem_id,"+\
        "financeaccount_code,financeaccount_name,financeaccount_id,"+\
        "product_code,product_name,product_aging,product_id,"+\
        "unit_name,unit_id,"+\
        "qty,tax_rate,price,price_discount,price_strategy,price_invoice,"+\
        "amt_total,amt_invoice) "+\
        "values "+",".join(a)  
    cur.execute(s)     
    cur.close()
    sync.result=data
    sync.save()
    return JsonResponse(data)





'''
#2021-11-30
def query_label(request,company_id,vendor):   
    data = {'e': '', 'result':{} }
    sKs={
        #'19267':{                                 #易臻生物科技
        #    'ufida':   'yz$3Yl1z%)20)0b1k4o'      #用友
        #},                 
        '1644': {                                   #内参
            'bz':       'bz$tl1hFikq%(30@cc1Z',     #博智
            'kingdee':  'nc$tYal1C%)T0@)d1Wn'       #金蝶
        },                 
        #'1':    {                                  #酒鬼
        #    'tms':     'tms$3al1i%)20@)0814'       #TMS
        #},                  
    }       
    if company_id not in sKs:
        data['e']='company not authorized'
        return JsonResponse(data)
    if vendor not in sKs[company_id]:
        data['e']='invalid vendor name'
        return JsonResponse(data)
    sK=sKs[company_id][vendor]
    e = time_key_check(request, sK)
    if e:
        data['e'] = e
        return JsonResponse(data)
    code=request.GET.get('code','')
    if not code:
        data['e']='标签号码不能为空'
        return JsonResponse(data)
    if request.META.get('HTTP_X_FORWARDED_FOR',''):
        ip =  request.META['HTTP_X_FORWARDED_FOR']
    else:
        ip = request.META['REMOTE_ADDR']
    #写入第三方查询日志
    s="INSERT INTO log.label_query_3rd(company_id,vendor,ip,code) "+\
        "VALUES ("+company_id+",'"+vendor+"','"+ip+"','"+code+"')"
    cur=connection.cursor()
    cur.execute(s)
    cur.close()
    q=LabelQuery(company_id,code)  
    if not q.infor['label_pk_id']:
        data['e']='标签号码错误'
    else:
        p=q.get_product()
        if not p['id']:
            data['e']='标签号码未激活'
        else:
            #给第三方数据，删除产品ID
            p.pop('id')
            p.pop('contents')
            data['result']['product']=p
            data['result']['stk_dev']=[]
            data['result']['package']=q.package['name']
            dev=q.get_stk_dev()
            for d in dev:
                data['result']['stk_dev'].append({
                    'batch_no':d['bill_no'],
                    'label':d['label_code'],
                    'time':d['tm'],
                    'voucher':d['voucher'],
                    'customer':{'name':d['customer'],'external_code':d['customer_external_code']},
                    'package':d['label_pkg']
                })  
    return JsonResponse(data)    
'''


#2021-11-30
def query_label(request,company_id,vendor):   
    data = {'e': '', 'result':{} }
    sKs={
        #'19267':{                                 #易臻生物科技
        #    'ufida':   'yz$3Yl1z%)20)0b1k4o'      #用友
        #},                 
        '1644': {                                   #内参
            'bz':       'bz$tl1hFikq%(30@cc1Z',     #博智
            'kingdee':  'nc$tYal1C%)T0@)d1Wn'       #金蝶
        },                 
        #'1':    {                                  #酒鬼
        #    'tms':     'tms$3al1i%)20@)0814'       #TMS
        #},                  
    }       
    if company_id not in sKs:
        data['e']='company not authorized'
        return JsonResponse(data)
    if vendor not in sKs[company_id]:
        data['e']='invalid vendor name'
        return JsonResponse(data)
    sK=sKs[company_id][vendor]
    e = time_key_check(request, sK)
    if e:
        data['e'] = e
        return JsonResponse(data)
    code=request.GET.get('code','')
    if not code:
        data['e']='标签号码不能为空'
        return JsonResponse(data)
    if request.META.get('HTTP_X_FORWARDED_FOR',''):
        ip =  request.META['HTTP_X_FORWARDED_FOR']
    else:
        ip = request.META['REMOTE_ADDR']

    #2022-01-22 内参博智接口，加一个参数表示数据来源
    #“source”:”数据来源代码”, 		//2022-01-20 	nc:内参系统  jg:酒鬼系统
    if int(company_id)==1644:
        data['result']['source']='nc'

    #写入第三方查询日志
    s="INSERT INTO log.label_query_3rd(company_id,vendor,ip,code) "+\
        "VALUES ("+company_id+",'"+vendor+"','"+ip+"','"+code+"')"
    cur=connection.cursor()
    cur.execute(s)    
    q=LabelQuery(company_id,code)  
    if not q.infor['label_pk_id']:
        data['e']='标签号码错误'
    else:
        p=q.get_product()
        if not p['id']:
            if int(company_id)==1644:
                data['result']['source']='jg'
                pk=str(q.infor['label_pk_id'])
                #内参可能有漏、错扫码的标签，给博智返回酒鬼产品信息，博智自己映射到内参产品        
                aid='1'
                s="select s.product_id,s.tm,p.name,coalesce(to_char(p.price,'99999.09'),'未知'),p.contents,p.external_code from "+\
                    "(select product_id,tm from "+\
                        "(select product_id,tm from inventory_stkdetails where company_id="+aid+" "+\
                                "and label_pk_id="+pk+" and vtype_id=1 and is_delete=false "+\
                            "union all "+\
                            "select product_id,tm from inventory_stkdetails where company_id="+aid+" "+\
                                "and label_pk_id="+pk+" and  vtype_id=2 "+\
                            "order by tm limit 1"+\
                        ") a "+\
                    ") s "+\
                    "left outer join "+\
                    "(select id,name,price,coalesce(contents,'') contents,external_code from product_product) p "+\
                    "on s.product_id=p.id"
                cur.execute(s)
                r=cur.fetchone()
                d={}
                prp=[]
                if r:      
                    d['name']=r[2]
                    d['price']=r[3].strip()
                    d['id']=r[0]
                    d['external_code']=r[5]
                    s="select coalesce(c.value,'') val,g.name,g.desc_cn,coalesce(g.unit,'') from "+\
                        "(SELECT * FROM product_prdprops where product_id="+str(r[0])+") p "+\
                        "left outer join "+\
                        "(SELECT * FROM product_productprop where company_id="+aid+") c "+\
                        "on p.prd_property_id=c.id "+\
                        "left outer join "+\
                        "(select * from company_categoryprop) g "+\
                        "on c.categoryprop_id=g.id"
                    cur.execute(s)
                    for rr in cur.fetchall():
                        prp.append({'desc_cn':rr[2],'unit':rr[3],'value':rr[0]})                
                    d['property']=prp
                    data['result']['product']=d
                    data['result']['stk_dev']=[]
                    data['result']['package']=q.package['name']
                else:    
                    data['e']='标签号码未激活'
                
        else:
            #给第三方数据，删除产品ID
            p.pop('id')
            p.pop('contents')
            data['result']['product']=p
            data['result']['stk_dev']=[]
            data['result']['package']=q.package['name']
            dev=q.get_stk_dev()
            for d in dev:
                data['result']['stk_dev'].append({
                    'batch_no':d['bill_no'],
                    'label':d['label_code'],
                    'time':d['tm'],
                    'voucher':d['voucher'],
                    'customer':{'name':d['customer'],'external_code':d['customer_external_code']},
                    'package':d['label_pkg']
                })  
    cur.close()
    return JsonResponse(data)    
