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,LabelQueryOld
import math

from erp.models import SalesOrder

#2022-03-22
import hashlib
from django.utils.translation import gettext_lazy as _


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



#2022-04-21 第三方产品维护,通过code 外部编码，判断是新增还是修改
def thirdparty_product(company,data):
    #必传外部编码和产品名称
    code=data['code']
    name=data['name']
    #默认传过来的是正式产品，而非辅料、促销品
    is_product=True
    if 'is_product' in data:
        is_product=data['is_product']

    if is_product:
        objs=Product.objects.filter(external_code=code,company_id=company.id)
        if objs:
            obj=objs[0]
            #修改产品名称
            obj.name=name
        else:
            obj=Product(company_id=company.id,external_code=code,name=name)
        obj.save()
        cur=connection.cursor()
        #可传产品系列的名称
        if 'series' in data:
            if data['series']:
                ss=Series.objects.filter(company_id=company.id,name=data['series'])
                if ss:
                    obj.series=ss[0]
        #酒企
        if company.category_id==1:
            pid=str(obj.id)
            #酒企业产品属性：香弄，酒精度，净含量，包装规格
            cats={1:"flavor", 2:"alcohol", 3:"volume", 4:"package"}
            for k,v in cats.items():
                cid=str(k)
                val=None
                if v in data:
                    val=data[v]
                else:
                    if k==1 and company.id in [1,1644]:  #酒鬼，内参
                        val='馥郁香型'
                if val:
                    ps=ProductProp.objects.filter(company_id=company.id,categoryprop_id=int(cid),value=val)
                    if ps:
                        #企业有对应产品属性和值
                        pp=ps[0]
                        s="select id from product_prdprops where product_id="+pid+" and "+\
                            "(SELECT categoryprop_id FROM product_productprop where id=prd_property_id)="+cid
                        cur.execute(s)
                        r=cur.fetchone()
                        if r:
                            #该产品已经有对应属性记录
                            prop=PrdProps.objects.get(id=r[0])
                            prop.prd_property=pp
                        else:
                            prop=PrdProps(product_id=int(pid),prd_property=pp)
                        #保存箱盒瓶换算                        
                        if v=='package':
                            a = val.split('*')
                            if len(a) == 1:
                                obj.qty_x = 1
                            elif len(a) == 2:
                                obj.qty_x = int(a[0])
                                obj.qty_p = int(a[1])
                            elif len(a) == 3:
                                obj.qty_x = int(a[0])
                                obj.qty_h = int(a[1])
                                obj.qty_p = int(a[2])*int(a[1])                               
                            obj.save()
                        prop.save()
        cur.close()
    else:
        #促销物料
        from mes.models import Accessories
        ps=Accessories.objects.filter(company_id=company.id,code=code)
        if ps:
            p=ps[0]
            p.name=name
        else:
            p=Accessories(company_id=company.id,code=code,name=name)
        p.save()        
    return






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

        #2022-04-21 内参金蝶：产品接着接口
        'kingdee': 'nc$tYal1C%)T0@)d1Wn' 
    }
    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':                     
            if 'code' not in d or 'name' not in d:
                e.append('code/name required')                
            else:
                c=Company.objects.get(id=int(company_id))
                thirdparty_product(c,d)    
        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-04-21
    elif vendor=='kingdee':                #内参-金蝶ERP
        if obj=='product':            
            d=r['data']

            # 2022-08-04 写入日志
            try:
                s="INSERT INTO z_3rd_hand(obj, src) values "+\
                    "('kingdee_"+obj+"','"+json.dumps(d)+"')"            
                cur.execute(s)
            except Exception:
                pass


            #data = {'e': '', 'result':{} }
            if 'code' not in d or 'name' not in d:
                data['e']='code/name required'
                return JssonResponse(data)
            c=Company.objects.get(id=int(company_id))
            thirdparty_product(c,d)
            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'    
    #2022-02-21 喷码信息
    marking='null'
    if 'marking' in data['customer']:
        marking="'"+data['customer']['marking']+"'"
    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+"',"+\
        "marking="+marking+","+\
        "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,product_aging) 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']
        
        #2022-04-14 内参业务模式修改，不再区分现金和费用订单
        if 'order_type' in p:
            ordertype_code=p['order_type']['code']
            ordertype_name=p['order_type']['name']
        else: 
            ordertype_code='000'
            ordertype_name='不区分'   
        #内参会传生产年份        
        product_aging='null'
        if 'product_aging' in p:
            if p['product_aging']:
                product_aging="'"+str(p['product_aging'])+"'"

        unit='null'
        
        
        #if 'unit' in p:
        #    if 'name' in p['unit']:
        #        unit="'"+p['unit']['name']+"'"
        
        q=str(int(p['qty']))        
        #默认传过来的数量是最小单位
        q0=q        
        if 'unit' in p:
            if 'name' in p['unit']:
                unit="'"+p['unit']['name']+"'"
                s="select id,qty_x,qty_h,qty_p from product_product where "+\
                    "company_id="+str(company_id)+" and external_code='"+product_code+"'"
                cur.execute(s)
                r0=cur.fetchone()
                if r0:
                    if p['unit']['name'] in ['箱']:
                        if r0[3]:
                            q0=str(int(q)*r0[3])                    
                    elif p['unit']['name'] in ['盒']:
                        if r0[3] and r0[2]:
                            q0=str(int(q)*(r0[3]/r0[2])) 



        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+","+q0+","+product_aging+\
        ")")
    if a:
        cur.execute(sIns+",".join(a))
    cur.close()
    return     






#2022-08-04 
def deliverynote_edit(company_id,data):
    #要求第三方按指定格式参数名传发货单    
    order_code=''
    if 'order_code' in data:
        order_code=data['order_code']
    code=data['dn_number']
    customer_code=data['customer']['code']
    customer_name=data['customer']['name']         
    store=[]
    jsn=json.dumps(data)
    if 'storehouse' in data:
        # 有些企业一张发货单不管多少产品，都在一个仓库发货
        store=data['storehouse']    
    #2022-02-21 喷码信息
    marking='null'
    if 'marking' in data['customer']:
        marking="'"+data['customer']['marking']+"'"     
    cur=connection.cursor()  
    # t=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')

    cid=str(company_id)
    cur.execute("select id from inventory_deliverynote "+\
        "where company_id="+str(company_id)+" and code='"+code+"'")
    r=cur.fetchone()    
    if r:
        id=str(r[0])        
        s="update inventory_deliverynote set "+\
            "marking="+marking+",edit_logs='"+jsn+"'||edit_logs,"+\
            "agent_code='"+customer_code+"',agent_name='"+customer_name+"',"+\
            "agent_id=(select id from company_company where ancestor_id="+cid+" and external_code='"+customer_code+"' limit 1)"+\
            " where id="+id        
        cur.execute(s)
    else:
        s="insert into inventory_deliverynote(company_id,code,user_id,edit_logs,"+\
                "salesorder_code,salesorder_id,marking,agent_code,agent_name,agent_id"+\
            ") values ("+cid+",'"+code+"',14499,'["+jsn+"]','"+\
                order_code+"',(select id from erp_salesorder where company_id="+cid+" and code='"+order_code+"' limit 1),"+\
                marking+",'"+customer_code+"','"+customer_name+"',"+\
                "(select id from company_company where ancestor_id="+cid+" and external_code='"+customer_code+"' limit 1)"+\
            ")  returning id"
        cur.execute(s)
        r=cur.fetchone()
        id=str(r[0]) 
    #如内参公司，订单修改后会再次同步过来，删除订单明细记录
    cur.execute("delete from inventory_dndetails where deliverynote_id="+id)
    sIns="insert into inventory_dndetails(deliverynote_id,company_id,"+\
        "product_code,product_name,product_id,"+\
        "ordertype_code,ordertype_name,ordertype_id,"+\
        "store_code,store_name,store_id,"+\
        "qty,unit_name,unit_code,product_aging) values "
    a=[]
    for p in data['products']:
        if 'storehouse' in p:
            store_code="'"+p['storehouse']['code']+"'"        
            store_name="'"+p['storehouse']['name']+"'"
        else:
            if store:
                store_code="'"+store['code']+"'"        
                store_name="'"+store['name']+"'"
            else:
                store_code='null'        
                store_name='null'
        product_code=p['code']
        product_name=p['name']        
        #2022-04-14 内参业务模式修改，不再区分现金和费用订单
        if 'order_type' in p:
            ordertype_code="'"+p['order_type']['code']+"'"
            ordertype_name="'"+p['order_type']['name']+"'"
        else: 
            ordertype_code='null'
            ordertype_name='null' 
        #内参会传生产年份        
        product_aging='null'
        if 'product_aging' in p:
            if p['product_aging']:
                product_aging="'"+str(p['product_aging'])+"'"
        #u_id='null'
        u_name='null'
        u_code='null'
        q=str(p['qty'])         
        if 'unit' in p:          
            if isinstance(p['unit'],str):
                u_name="'"+p['unit']+"'"
            else:
                u_name="'"+p['unit']['name']+"'"
                u_code="'"+p['unit']['code']+"'"
        a.append("("+id+","+cid+","+\
            "'"+product_code+"','"+product_name+"',"+\
                "(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_code+","+store_name+","+\
                "(select id from inventory_storehouse where company_id="+str(company_id)+" and external_code="+store_code+" limit 1),"+\
            q+","+u_name+","+u_code+","+product_aging+\
        ")")
    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']


    # 2022-08-12 是否需要修改销售订单
    bEditOrder=True

    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)

        #2022-06-01 同步金蝶发货单数据时，如果dn_number带 "-1" "-2" 之类时，不修改订单数据
        #if "-" in d['dn_number'] or "——" in d['dn_number'] or "—" in d['dn_number']:
        #    data['e']='发货单号带 - 或 — 不修改订单信息'
        #    return JsonResponse(data)
        
        # 2022-08-11 内参分单发货，发货单号以a,b,c,d 结尾，不修改销售订单 
        if not d['dn_number'].isdigit():
            bEditOrder=False

    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
    #2022-02-21 喷码信息 marking
    if 'marking' in d['customer']:
        o.marking=d['customer']['marking']
    o.save()

    #2022-01-14
    cur=connection.cursor()
    cur.execute("SELECT  erp_salesorder FROM company_companycfg WHERE company_id="+str(company_id))
    r=cur.fetchone()
    #if int(company_id)==1644:
    if r:
        if r[0] and bEditOrder:
            try:
                salesorder_edit(company_id,d)
            except Exception:
                pass



    # 2022-08-06 写入deliverynote
    try:
        deliverynote_edit(company_id,d)
    except Exception:
        pass

    return JsonResponse(data)



#2022-01-17 检查订单产品剩余发货数量
#注：大多数企业，如酒鬼酒，产品规格为 箱盒瓶 三级对应，实际生产时贴 箱瓶 二级套标，即瓶标对应产品规格的盒
def salesorder_remaining(company_id,order_id,pkg_lv2=True):  
    products=[]  
    cur=connection.cursor() 
    #销售订单单位转换成物流系统单位
    units={
        '箱':'x',
        '件':'x',
        '盒':'h',
        '瓶':'p',        
    }
    
    #将销售出库明细数量换算为瓶；若订单明细没有单位，默认为瓶
    s_qty="coalesce(d.x,0)*p.p+coalesce(d.p,0)+"+\
        "case when d.h>0 then d.h*(p.p/p.h) else 0 end,"            
    if pkg_lv2: #箱盒瓶三级包装规格，使用了箱瓶二级套标
        #s_qty="coalesce(d.x,0)*p.p+"+\
        #    "case when d.h>0 then d.p*(p.p/p.h) else 0 end,"         
        #2022-02-11
        s_qty="coalesce(d.x,0)*p.p+"+\
            "case when d.p>0 and p.h>0 then d.p*(p.p/p.h) else coalesce(d.p,0) end,"    
    """
    s="select t.id, t.code, t.name,"+\
        "p.id, coalesce(p.external_code,''), p.name,"+\
        "s.id, s.external_code, s.name,"+\
        "o.qty, coalesce(o.unit_name,'瓶'),"+\
        s_qty+\
        "p.h, p.p,coalesce(o.product_aging,'') from "+\
        "(select product_id,ordertype_id,store_id,unit_name,product_aging,sum(qty) qty from erp_salesorderdetails "+\
            "where company_id="+str(company_id)+" and salesorder_id="+str(order_id)+" and "+\
            "(select id from product_product where company_id="+str(company_id)+" and external_code=product_code limit 1)>0 "+\
            "group by product_id,ordertype_id,store_id,unit_name,product_aging "+\
        ") o "+\
        "left outer join "+\
        "(select product_id,ordertype_id,sum(x) x,sum(h) h,sum(p) p from "+\
            "(select product_id,"+\
                "coalesce((select salesorder_type_id from inventory_batch where id=batch_id),0) ordertype_id,"+\
                "case when label_pkg=3 then 1 else 0 end x,"+\
                "case when label_pkg=2 then 1 else 0 end h,"+\
                "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,ordertype_id"+\
        ") d "+\
        "on o.product_id=d.product_id and o.ordertype_id=d.ordertype_id "+\
        "left outer join "+\
        "(select id,name,coalesce(qty_h,1) h,coalesce(qty_p,1) p,external_code 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" 
    """
    s="select t.id, t.code, t.name,"+\
        "p.id, coalesce(p.external_code,''), p.name,"+\
        "s.id, s.external_code, s.name,"+\
        "o.qty, coalesce(o.unit_name,'瓶'),"+\
        s_qty+\
        "p.h, p.p,coalesce(o.product_aging,'') from "+\
        "(select product_id,ordertype_id,store_id,unit_name,product_aging,sum(qty) qty from erp_salesorderdetails "+\
            "where company_id="+str(company_id)+" and salesorder_id="+str(order_id)+" and "+\
            "product_id>0 "+\
            "group by product_id,ordertype_id,store_id,unit_name,product_aging "+\
        ") o "+\
        "left outer join "+\
        "(select product_id,ordertype_id,sum(x) x,sum(h) h,sum(p) p from "+\
            "(select product_id,"+\
                "coalesce((select salesorder_type_id from inventory_batch where id=batch_id),0) ordertype_id,"+\
                "case when label_pkg=3 then 1 else 0 end x,"+\
                "case when label_pkg=2 then 1 else 0 end h,"+\
                "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,ordertype_id"+\
        ") d "+\
        "on o.product_id=d.product_id and o.ordertype_id=d.ordertype_id "+\
        "left outer join "+\
        "(select id,name,coalesce(qty_h,1) h,coalesce(qty_p,1) p,external_code 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():  
        q_o=r[9]
        unit=r[10]
        q_d=r[11]           
        
        #当前产品1箱有多少瓶，1箱有多少盒        
        r_h=r[12]
        r_p=r[13]

        #将订单明细单位转换为x,h,p
        u=units[r[10]]         
        X=0
        H=0
        P=0
        ratio=1            
        if u=='x':
            X=q_o
            ratio=r_p
        elif u=='h':
            H=q_o
            ratio=r_p/r_h
            if pkg_lv2:
                #瓶标做盒标使用
                H=0
                X=int(q_o/r_h)
                P=q_o % r_h
            else:
                X=int(q_o/r_h)                
                H=q_o % r_h
        elif u=='p':            
            ratio=1 
            X=int(q_o/r_p)
            if pkg_lv2:
                #瓶标做盒标使用,只有2级对应
                P=q_o % r_p
            else:
                H=int( (q_o-X*r_p) / (r_p/r_h) )
                P=(q_o-X*r_p-H*(r_p/r_h)) % (r_p/r_h)   
        
        #统一成最小单位瓶   剩余发货瓶数,如果已发数量大于订单数，取0    
        q0=max(q_o*ratio-q_d,0)   

        X0=0
        P0=0
        H0=0
        if q0>0:
            #剩余数量转换为整箱
            X0=int(q0/r_p)
            #剩余瓶数
            rm=q0 % r_p   
            #一盒有多少瓶，默认为1盒1瓶
            hp=1
            if r_p and r_h:
                hp=r_p/r_h
            if pkg_lv2:
                H0=0
                if u=='h':
                    #瓶标做盒标使用
                    P0=int(rm/hp)
                else:
                    P0=rm 
            else:
                H0=int(rm/hp)
                P0=int(rm % r_h)             
        products.append({
            'id':r[3],
            'name':r[5],
            'qty':r[9],
            'unit':r[10],
            'storehouse':{'id':r[6],'name':r[8],'code':r[7]},
            'order_type':{'id':r[0],'code':r[1],'name':r[2]},
            'x':X,
            'h':H,
            'p':P,
            'product_aging':r[14],  #2022-04-14 内参订单产品有产品生产年份信息
            'remaining':{'x':X0,'p':P0,'h':H0}   
        })     
    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,'marking':''}
    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,marking 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])
            #2022-02-21
            if r[1]:
                data['data']['customer']['marking']=r[1]
        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'],is_delete=False)
            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 id,name,coalesce(code,''),coalesce(remark,'') "+\
           "from thirdparty_vendor"
        cur.execute(s)
        for r in cur.fetchall():
            d.append([ '<a href="/thirdparty/vendor/edit/'+str(r[0])+'/">'+r[1]+'</a>', r[2],r[3] ])
        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})

#2022-03-22
@login_required
def redirect_action(request,what,id=None):
    if what=='list':
        cur=connection.cursor()
        clmns=['前端跳转代码','所属企业','第三方单位名称','跳转链接','指定产品','指定经销商']
        d=[]
        s="select coalesce(code,''),(select name from company_company where id=company_id),"+\
            "(select name from thirdparty_vendor where id=vendor_id),"+\
            "case when synclogistics then '是' else '否' end,"+\
            "case when is_active then '是' else '否' end,"+\
            "coalesce(redirect,''),"+\
            "array_to_string(array((select jsonb_array_elements(products)->>'name')),', '),"+\
            "array_to_string(array(select jsonb_array_elements(agents)->>'name'),', '),"+\
            "id from thirdparty_redirect"
        cur.execute(s)
        for r in cur.fetchall():
            d.append([ '<a href="/thirdparty/redirect/edit/'+str(r[8])+'/">'+r[0]+'</a>',r[1],
                r[2],r[5],r[6],r[7] ])
        return render(request,'thirdparty/list_redirect.html',{'clmns':clmns,'data':d,'murl':'/thirdparty/redirect/edit/'})
    elif what=='edit':
        if id:
            obj=Redirect.objects.get(id=int(id))
        else:
            obj=Redirect()
        if request.method=='GET':
            form=RedirectForm(instance=obj)    
            form.fields['company'].queryset=Company.objects.filter(level_id=1).order_by('name')
        else:
            form=RedirectForm(request.POST,instance=obj)
            form.fields['company'].queryset=Company.objects.filter(level_id=1).order_by('name')
            if form.is_valid():
                form.save()
        return render(request,'thirdparty/edit_redirect.html',{'form':form})






#2022-03-22 修改
#消费者扫码跳转第三方链接
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)     
    
    # 2022-07-27 保存原码值
    c0=c

    cur=connection.cursor()
    #传过来的标签一定是加密码(瓶盖码或活动小圆标)    
    if c.isdigit():

        #data['e']='非加密码'
        #return JsonResponse(data)
        
        # 2022-06-30 传过来的也可能不是加密码，如万里走单骑，新世纪(恒和商务）独产箱标
        pass
    else:           
        #先检查传过来的标签是在线绑定的瓶盖码,把盖码转为对应瓶标  
        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
                    return JsonResponse(data)
                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'])
            
            # 2022-07-16 内参酒（纳祥瑞）跳到指定页面
            #3386;"52度500mL内参酒（纳祥瑞）"
            #3422;"52度100mL内参酒（纳祥瑞）品鉴酒（促销）"
            #3423;"52度468mL内参酒（纳祥瑞）品鉴酒（促销）"
            
            # 2022-07-20 不做任何处理，颂恩判断
            #if int(product_id) in (3386,3422,3423):
            #    data['flag']='NXR'
            #    data['url']='http://tjfnew.china315net.com/jgsm/poster_just.html'
            #    return JsonResponse(data)           

            


            #先判断是否跳往天鉴相关页面   vendor_id:4 "海南天鉴防伪科技有限公司"
            #s='select code from thirdparty_redirect where company_id='+company_id+' and vendor_id=4 '+\
            #    'and products @> '+"'"+'[{"id":"'+product_id+'"}]'+"'"   
            #cur.execute(s)
            #r=cur.fetchone()      
            #if r:
            #    data['flag']=r[0]
            #    return JsonResponse(data) 



            # 2022-06-30 先判断是否是除 透云,汉得,米多之外的第三方（目前的跳转代码包含HH 恒和；PJ 品鉴 WL 万里走单骑）
            s="select code,COALESCE(redirect,'') from thirdparty_redirect where "+\
                "company_id="+company_id+" and is_active=true "+\
                "and code not in ('HD','TY','MD') "+\
                "and (products @> "+"'"+'[{"id":"'+product_id+'"}]'+"')"

            
            

            cur.execute(s)
            r=cur.fetchone()
            if r:
                
                

                data['flag']=r[0]
                data['url']=r[1]
                if r[0]=='HH':
                    data['url']=data['url']+c0
                return JsonResponse(data) 
            
            s='select products @> '+"'"+'[{"id":"'+product_id+'"}]'+"' from thirdparty_redirect 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:                        
                        #云平台上线之前，发给透云的数据已导入云平台，单号以 ty_ 开头
                        if batch_code[:3]=='ty_':
                            flag='TY'
                        else:                                 
                            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 flag!='HD':                    
                #可能是云平台上线之前，历史数据，查看跳转表
                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':    {                                   #酒鬼
            'hand':     'hand$tYal1i%)20@)c1W'      #汉得
        },                 
    }       
    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)     
    #2022-06-17
    q=LabelQueryOld(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,vendor,inboud_lot,inbound_aging,inbound_date             
            product_lot=''
            product_date=''
            p.pop('id')
            p.pop('contents')     
            p.pop('inbound_aging')
            p.pop('outbound_aging')
            p.pop('inbound_vendor')
            if p['inbound_lot']:
                product_lot=p['inbound_lot']
            p['production_batch']=product_lot
            p.pop('inbound_lot')
            if p['inbound_date']:
                product_date=p['inbound_date']
            p.pop('inbound_date')            
            p['production_date']=product_date            
            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']
                })  
            #2022-04-18 汉得，要返回防伪信息
            if vendor=='hand':
                d=q.get_security_info()
                data['result']['security_queries']=d
    cur.close()
    return JsonResponse(data)    







#2022-02-21 喷码机取销售订单
def marking_order(request,pkg_lv2=True):    #默认 箱/盒/瓶 三层包装 使用箱/瓶二级绑定关系
    
    #是否为测试
    is_test=True

    data={'error_code':0, 'e':'', 'data':{
        'dn_number':'',
        'order_number':'',  
        'order_id':0,
        'customer':{}, 
        'marking':'',
        'storehouse':{},  
        'products':[]        
        } 
    }    
    products=[]           
    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)   
    cur=connection.cursor() 
    u=us[0]
    company_id=u.company_id
    s="select id,code,customer_id,customer_name,marking "+\
        "from erp_salesorder where code="+\
        "(select order_code from thirdparty_ordersync where dn_number='"+dn+"')"
    cur.execute(s)
    rr=cur.fetchone()
    if not rr:
        data['e']='未找到此发货单对应的销售订单'
        cur.close()
        return JsonResponse(data)
    
    if not is_test:
        if not rr[3]:
            data['e']="订单 "+rr[1]+" 喷码信息为空"
            cur.close()
            return JsonResponse(data) 

    # 如果金蝶推的订单customer_id为空，报警停止喷码
    if not rr[2]:
        data['e']='订单'+dn+"经销商编码不存在"
        cur.close()
        return JsonResponse(data) 


    order_id=rr[0]
    data['data']['marking']=rr[3]
    data['data']['dn_number']=dn
    data['data']['marking']=rr[4]
    data['data']['order_number']=rr[1]
    data['data']['order_id']=rr[0]
    data['data']['customer']={'id':rr[2],'name':rr[3]}
    
    s="select t.id, t.code, t.name,"+\
        "p.id, coalesce(p.external_code,''), p.name,"+\
        "s.id, s.external_code, s.name,"+\
        "o.qty, coalesce(o.unit_name,'瓶'),"+\
        "o.qty0,"+\
        "p.h, p.p,coalesce(d.qty0,0),coalesce(o.product_aging,'') from "+\
        "(select product_id,ordertype_id,store_id,unit_name,product_aging,sum(qty) qty,sum(qty0) qty0 from erp_salesorderdetails "+\
            "where company_id="+str(company_id)+" and salesorder_id="+str(order_id)+" and "+\
            "(select id from product_product where company_id="+str(company_id)+" and external_code=product_code limit 1)>0 "+\
            "group by product_id,ordertype_id,store_id,unit_name,product_aging "+\
        ") o "+\
        "left outer join "+\
        "(select product_id,ordertype_id,sum(qty0) qty0 from thirdparty_inkjetdetails where "+\
            "company_id="+str(company_id)+" and salesorder_id="+str(order_id)+" and is_ok=true "+\
            "group by product_id,ordertype_id"+\
        ") d "+\
        "on o.product_id=d.product_id and o.ordertype_id=d.ordertype_id "+\
        "left outer join "+\
        "(select id,name,coalesce(qty_h,1) h,coalesce(qty_p,1) p,external_code 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_o=r[11]
        #已喷码瓶数
        qty0_d=r[14]
        
        #1箱有多少盒        
        r_h=r[12]
        #1箱有多少瓶，
        r_p=r[13]

        #剩余瓶数
        rem=qty0_o-qty0_d        
        X=0
        H=0
        P=0
        if rem:
            X=int(rem/r_p)
            #剩余盒数
            if pkg_lv2:     #瓶标做盒标用
                P=rem-X*r_p 
            else:
                H=int((rem % r_p)/(r_p/r_h))
                P=(rem % r_p) % (r_p/r_h)       
        
        yr=''
        products.append({
            'id':r[3],
            'name':r[5],
            #2022-04-15 内参订单明细指定产品生产年份
            'year':yr,
            'qty':r[9],
            'unit':r[10],
            'storehouse':{'id':r[6],'name':r[8],'code':r[7]},
            'order_type':{'id':r[0],'code':r[1],'name':r[2]},            
            'remaining':{'x':X,'h':H,'p':P}   
        })     
    cur.close()
    data['data']['products']=products
    return JsonResponse(data)



#2022-02-21 喷码机更换产品接口
@csrf_exempt
def marking_product(request):   
    sK=inv_sK
    data={'e':'','batch_id':0}
    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 'batch_id' not in r or 'order_id' not in r or 'product_id' not in r or 'store_id' not in r or 'user_id' not in r or 'marking' not in r or 'dn_number' not in r:        
        data['e']='user id,order id,product id,batch id,store id,marking,delivery note number required'
        return JsonResonse(data)
    
    cur=connection.cursor()  
    us=User.objects.filter(id=int(r['user_id']))
    if not us:
        data['e']='user id error'
        return JsonResonse(data)
    u=us[0]
    c=u.company    

    #2022-04-22 参数存入后台
    url=request.path
    s="INSERT INTO log.thirdparty_post(company_id,vendor,url,data) values "+\
        "("+str(c.id)+",'marking', '"+url+"','"+json.dumps(r)+"')"
    cur.execute(s)

    
    if r['batch_id']:        
        bid=str(r['batch_id'])  
        #改为通过fgupdate接口，可以自动发送给博智
        s="SELECT company_id,product_id,user_id,store_id,coalesce(dn_number,''),"+\
            "(select code FROM logistics.erp_ordertype where id=ordertype_id),"+\
            "(select code FROM logistics.erp_salesorder where id=salesorder_id),"+\
            "(select customer_id FROM logistics.erp_salesorder where id=salesorder_id) "+\
            "FROM logistics.thirdparty_inkjetbatch where id="+bid
        cur.execute(s)
        rr=cur.fetchone()       
        if not rr:
            data['e']='batch id '+bid+' not exists'
            return JsonResponse(data)
        codes=[]
        s="select label_code FROM logistics.thirdparty_inkjetdetails "+\
            "where batch_id="+bid+" and is_ok=true"
        cur.execute(s)
        for r0 in cur.fetchall():
            codes.append(r0[0])
        

        if codes:
            num=rr[4]
            if rr[6]:
                num=rr[6]
            t=datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")            
            m2=hashlib.md5((inv_sK+t).encode('utf8'))    
            key=m2.hexdigest()  
            data={                
                "key": key,
                "time": t,  
                "voucher_id": 2, 
                "bill_number":num,
                "batch_no": num,                   
                "codes":codes,
                "company_id":rr[0],                                
                "product_id":rr[1],
                "user_id":rr[2], 
                "store_id":rr[3],   
                "dn_number":rr[4],
                "ordertype_code":rr[5],                
                "order_number":rr[6],
                "to_company_id":rr[7],  
                "ref_id":int(bid),
            }                          
            address='https://davis.china315net.com'            
            url=address+'/inventory/fg/update/'
            #发送到销售出库接口 
            try:              
                rt=requests.post(url,json=data)   
                #r.status_code == requests.codes.ok
                if rt.status_code==200:
                    cur.execute("update thirdparty_inkjetbatch set outbound=true where id="+bid)
            except Exception:
                pass   
    #2022-05-27
    if int(r['order_id'])==0:
        #结束当前订单 通常用于下班前最后一单,这时操作人员不会去扫新订单
        pass
    else:
        s="select qty,unit_name,qty0 from erp_salesorderdetails where salesorder_id="+str(r['order_id'])+" "+\
            "and product_id="+str(r['product_id'])+" and ordertype_id="+str(r['ordertype_id']) 
        cur.execute(s)    
        rr=cur.fetchone()
        if rr:        
            b=InkJetBatch(            
                company_id=c.id,
                ordertype_id=r['ordertype_id'],
                store_id=r['store_id'],
                salesorder_id=r['order_id'],
                user_id=r['user_id'], 
                product_id=r['product_id'],
                marking=r['marking'],
                dn_number=r['dn_number'],
                qty=rr[0],
                unit=rr[1],
                qty0=rr[2]
            )
            b.save()
            data['batch_id']=b.id
        else:
            data['e']='当前BATCH没有指定产品、订单类型的销售订单明细' 
    return JsonResponse(data)



#2022-02-21 喷码机标签上传、检查接口
def marking_check(request,pkg_lv2=True):    #默认 箱/盒/瓶 三层包装 使用箱/瓶二级绑定关系
    sK=inv_sK
    data={
        'e':'',
        'label':{'code':'','package':''},
        'remaining':{'x':0,'p':0}
    }
    e = time_key_check(request, sK)
    if e:
        data['e']=e
        return JsonResponse(data)  
    user_id=request.GET.get('user_id','')
    batch_id=request.GET.get('batch_id','')
    label=request.GET.get('label','')

    

    label=label.strip()

    marking=request.GET.get('marking','')
    if not user_id or not batch_id or not label or not marking:
        data['e']='user id/batch id/label/marking required'
        return JsonResponse(data)

    a_e=[]
    #2022-03-21 喷码机传二维码标签：'\nhttp://tj1.me/?t=3_209427678865'    另，内参还有条形码标签
    if not label.isdigit():         #非纯数字，二维码标签，        
        if 'http://tj1.me/?t=' not in label:
            a_e.append('非海南天鉴产品标签') 
            
    label=label[-12:]
    if len(label)!=12:
        a_e.append('标签号码长度应为12')

    us=User.objects.filter(id=int(user_id))
    if not us:
        data['e']='用户ID不存在'
        return JsonResonse(data)
    bs=InkJetBatch.objects.filter(id=int(batch_id))
    if not bs:
        data['e']='批处理ID不存在 '
        return JsonResponse(data)
    u=us[0]
    b=bs[0]
    cur=connection.cursor()
    company_id=u.company_id    
    q=q=LabelQuery(int(company_id),label)
    pid=str(b.product.id)   
    
    e=''  
    r_h=1
    r_p=1
    if not q.infor['label_pk_id']:
        a_e.append('标签号码错误')
        e=",".join(a_e)         
    else:     
        pkg=q.package['id']
        pk_id=str(q.infor['label_pk_id'])
        s="select qty_x,qty_h,qty_p,coalesce((select sum(qty0) from thirdparty_inkjetdetails "+\
            "where batch_id="+str(b.id)+" and is_ok=true),0) "+\
            "from product_product where id="+pid
        cur.execute(s)
        r=cur.fetchone()
        qty_mark=r[3]
        r_h=r[1]
        r_p=r[2]
        if pkg==3:
            qq=r[2]
        elif pkg==2:
            if not r[1]:
                e="产品 "+b.product.name+' 盒瓶转换率未定义'                
            else:
                qq=r[2]/r[1]
        elif pkg==1:
            if pkg_lv2 and b.unit=='盒':     #箱/盒/瓶 三层包装 使用箱/瓶二级绑定关系                
                if not r[1]:
                    e="产品 "+b.product.name+' 盒瓶转换率未定义'                        
                else:
                    qq=r[2]/r[1]
            else:
                qq=1
        if not e:
            #是否重复喷码
            if pkg==3:
                s="select label_code,tm from thirdparty_inkjetdetails where label_pk_id="+str(q.infor['label_pk_id'])
            else:
                s="select label_code,tm from thirdparty_inkjetdetails where label_code='"+label+"'"
            s=s+" and is_ok=true"
            cur.execute(s)
            r=cur.fetchone()
            if r:
                e='已有喷码记录：'+r[0]+" "+r[1].strftime('%Y-%m-%d %H:%M:%S')
                if r[0]!=label:
                    e='另一箱码'+e
    #当前是否已达喷码数量
    if not e:
        qty_order=b.qty0
        remaining=qty_order-qty_mark-qq
        if remaining<0:
            e='喷码已达订单数量'
    if e:
        data['e']=e
        s="INSERT INTO thirdparty_inkjetdetails("+\
            "company_id,label_code,is_ok,batch_id,ordertype_id,product_id,salesorder_id,store_id,err) values ("+\
            str(company_id)+",'"+label+"',false,"+str(b.id)+","+str(b.ordertype.id)+","+pid+","+str(b.salesorder.id)+","+str(b.store.id)+",'"+e+"'"+\
        ")"

        cur.execute(s)
        cur.close()
        return JsonResponse(data) 
    else:
        s="INSERT INTO thirdparty_inkjetdetails(company_id,label_code,is_ok,batch_id,ordertype_id,product_id,salesorder_id,"+\
            "store_id,label_pk_id,qty0,label_id,label_pkg) values ("+\
                str(company_id)+",'"+label+"',true,"+str(b.id)+","+str(b.ordertype.id)+","+pid+","+str(b.salesorder.id)+","+\
                str(b.store.id)+","+pk_id+","+str(qq)+","+str(q.infor['label_id'])+","+str(pkg)+\
        ")"
        cur.execute(s)
        cur.close()

    pkgs={1:'瓶',2:'盒',3:'箱'}
    data['label']={'code':label,'package':pkgs[pkg]}
    data['remaining']={'x':int(remaining/r_p),'p': remaining % r_p}       
    return JsonResponse(data)




#2022-03-23
@login_required
def inkjet(request,id=None):   
    cur=connection.cursor()
    cid=str(request.user.company.id)
    if id:  #查看当前批次喷码明细        
        html='inkjet_view.html'              
        clmns=[
            _('标签号码'),
            _('类型'),
            _('状态'),            
            _('上传时间'),
            _('备注'),  
        ]
        s="SELECT label_code,coalesce((select name from label.lbl_package where id=label_pkg),''),"+\
            "case when is_ok then '正常' else '错误' end,"+\
            "to_char(tm,'yyyy-mm-dd HH24:MI:SS'),coalesce(err,'') "+\
            "FROM logistics.thirdparty_inkjetdetails where batch_id="+id  
        cur.execute(s)        
        data=[]
        batch={}
        for r in cur.fetchall():
            data.append([ r[0],r[1],r[2],r[3],r[4] ])        
        s="select dn_number,marking,"+\
            "(select customer_name from erp_salesorder where id=salesorder_id),"+\
            "(select name from product_product where id=product_id),"+\
            "coalesce((select name from erp_ordertype where id=ordertype_id),'') "+\
            "FROM logistics.thirdparty_inkjetbatch where id="+id        
        cur.execute(s)
        r=cur.fetchone()        
        if r:       
            batch['dn_number']=r[0]
            batch['marking']=r[1]
            batch['customer']=r[2]
            batch['product']=r[3]
            batch['ordertype']=r[4]             
        return render(request,'thirdparty/'+html,{'clmns':clmns,'data':data,'batch':batch})          
    else:   #喷码批次列表
        dt = datetime.datetime.now().strftime('%Y-%m-%d')
        dt_fr = request.GET.get('dt_fr', dt)
        dt_to = request.GET.get('dt_to', dt)
        dt_max = max(dt_fr, dt_to)
        dt_min = min(dt_fr, dt_to)
        dt_fr = dt_min
        dt_to = dt_max   
       
        html='inkjet_list.html'
        clmns=[
            _('开始时间'),     
            _('发货单号'),                       
            _('喷码信息'),
            _('产品名称'),
            _('计数'),
            _('经销商'),            
            _('操作人'),            
            _('订单类型'),       
            _('已做出库'),
        ]
        s="SELECT id,to_char(tm,'yyyy-mm-dd HH24:MI:SS'),dn_number,marking,"+\
                "(select name from product_product where id=product_id),"+\
                "(select count(*) from thirdparty_inkjetdetails where batch_id=b.id and is_ok=true),"+\
                "(select customer_name from erp_salesorder where id=salesorder_id),"+\
                "(select username from company_user where id=user_id),"+\
                "coalesce((select name from erp_ordertype where id=ordertype_id),''),"+\
                "case when outbound=true then '是' else '否' end "+\
                "FROM logistics.thirdparty_inkjetbatch b "+\
                    "where company_id="+cid+" "+\
                    "and (dt between '"+dt_fr+"' and '"+dt_to+"')"
        cur.execute(s)     
        data=[]
        for r in cur.fetchall():
            data.append([
                "<a href='/thirdparty/inkjet/"+str(r[0])+"/'>"+r[1]+"</a>",
                r[2],r[3],r[4],r[5],r[6],r[7],r[8],r[9]
            ])
        return render(request,'thirdparty/'+html,{'clmns':clmns,'data':data,'dt_fr':dt_fr,'dt_to':dt_to})

def nfc_label_verify(request):
    data={"code":0,"message":"","vrcode":"","is_active":False}

    e = time_key_check(request, sK)
    if e:
        data['e'] = e
        return JsonResponse(data)
    cur = connection.cursor()
    nfccode = request.GET.get('uid','')
    if nfccode:
        s = "SELECT  label_code, label_pk_id, label_id, label_pkg,is_active FROM label.lbl_nfc where nfc = '"+nfccode +"' "
        cur.execute(s)
        r = cur.fetchone()
        if r :
            data['code'] = 0
            data['is_active'] = r[4]
            data["vrcode"] = r[0]
        else:
            data['code'] = 404
            data['message'] = '未查询到标签信息'
    else:
        data['code'] = 404
        data['message'] = '参数错误'
    return JsonResponse(data)
















# 2022-08-09 
def get_deliverynote_remaining(company_id,dn_number): 
    """
    检查发货单剩余发货数量
    """
    data={'e':'','deliverynote':{},'customer':{},'products':[]}  
    # 企业标准单位
    std_pkg={
            1:{'label_pkg':1, 'name':'瓶','pkg_lv2':True},        # 酒鬼：瓶      产品三级包装使用二级套标
         1644:{'label_pkg':1, 'name':'瓶','pkg_lv2':True},        # 内参：瓶      产品三级包装使用二级套标
        19267:{'label_pkg':2, 'name':'盒','pkg_lv2':False},       # 易臻：套（盒）
    }
    if int(company_id) in std_pkg:
        std=std_pkg[int(company_id)]
    else:
        std={'label_pkg':1,'name':'瓶','pkg_lv2':False}
    pkg_lv2=std['pkg_lv2']
    cur=connection.cursor() 
    s="select agent_id,agent_name,agent_code,coalesce(marking,''),id,coalesce(salesorder_id,0),coalesce(salesorder_code,'') "+\
        "from inventory_deliverynote where company_id="+str(company_id)+" and code='"+str(dn_number)+"'"
    cur.execute(s)
    r=cur.fetchone()
    if not r:
        data['e']='发货单号不存在'
        return data
    else:
        data['deliverynote']={                   
            'marking':r[3],
            'id':r[4],
            'code':dn_number,
            'salesorder':{'id':r[5],'code':r[5]}
        }
        data['customer']={
            'id':r[0],
            'name':r[1],
            'code':r[2],            
        }
    products=[]

    #销售订单单位转换成物流系统单位
    units={
        '箱':'x',
        '件':'x',
        '盒':'h',
        '套':'h',   # 易臻
        '瓶':'p',       
    }
    
    #将发货单明细数量换算为最小单位瓶
    s_qty="coalesce(d.x,0)*p.p+coalesce(d.p,0)+"+\
        "case when d.h>0 then d.h*(p.p/p.h) else 0 end,"            
    if pkg_lv2: #箱盒瓶三级包装规格，使用了箱瓶二级套标
        #s_qty="coalesce(d.x,0)*p.p+"+\
        #    "case when d.h>0 then d.p*(p.p/p.h) else 0 end,"         
        #2022-02-11
        s_qty="coalesce(d.x,0)*p.p+"+\
            "case when d.p>0 and p.h>0 then d.p*(p.p/p.h) else coalesce(d.p,0) end,"   
    s="select coalesce(t.id,0), coalesce(t.code,''), coalesce(t.name,''),"+\
        "p.id, coalesce(p.external_code,''), p.name,"+\
        "s.id, coalesce(s.external_code,''), s.name,"+\
        "o.qty, coalesce(o.unit_name,'瓶'),"+\
        s_qty+\
        "p.h, p.p,coalesce(o.product_aging,''),"+\
        "coalesce((SELECT value FROM product_productprop where categoryprop_id=(select id from company_categoryprop "+\
                "where category_id=(SELECT category_id FROM company_company where id="+str(company_id)+") and name='package') "+\
            "and id in (SELECT prd_property_id FROM product_prdprops where product_id=o.product_id) ),'') "+\
        "from "+\
        "(select deliverynote_id,product_id,ordertype_id,store_id,unit_name,product_aging,sum(qty) qty from inventory_dndetails "+\
            "where company_id="+str(company_id)+" and product_id>0 "+\
            "and deliverynote_id=(select id from inventory_deliverynote where code='"+str(dn_number)+"') "+\
            "group by product_id,ordertype_id,store_id,unit_name,product_aging,deliverynote_id "+\
        ") o "+\
        "left outer join "+\
        "(select product_id,ordertype_id,sum(x) x,sum(h) h,sum(p) p from "+\
            "(select product_id,"+\
                "coalesce((select salesorder_type_id from inventory_batch where id=batch_id),0) ordertype_id,"+\
                "case when label_pkg=3 then 1 else 0 end x,"+\
                "case when label_pkg=2 then 1 else 0 end h,"+\
                "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 is_delete=false and batch_id in "+\
                    "(select id from inventory_batch where dn_number='"+str(dn_number)+"')"+\
            ") s "+\
            "group by product_id,ordertype_id"+\
        ") d "+\
        "on o.product_id=d.product_id and o.ordertype_id=d.ordertype_id "+\
        "left outer join "+\
        "(select id,name,coalesce(qty_h,1) h,coalesce(qty_p,1) p,external_code 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)
    err_products=[]
    for r in cur.fetchall():
        # 产品规格是否有定义
        if not r[15]:
            err_products.append(r[5])
        q_o=float(r[9])
        unit=r[10]
        q_d=r[11]           
        
        #当前产品1箱有多少瓶，1箱有多少盒        
        r_h=r[12]
        r_p=r[13]

        #将发货单明细单位转换为x,h,p             
        if r[10] in units:
            u=units[r[10]] 
        else:
            # 以上查询语句，发货单的单位已默认为瓶，如果单位在 units 中末定义，默认为箱（如易臻 传数量单位 “壮”）
            u='x'           
        X=0
        H=0
        P=0
        ratio=1            
        if u=='x':
            X=q_o
            ratio=r_p
        elif u=='h':
            H=q_o
            ratio=r_p/r_h
            if pkg_lv2:
                #瓶标做盒标使用
                H=0
                X=int(q_o/r_h)
                P=q_o % r_h
            else:
                X=int(q_o/r_h)                
                H=q_o % r_h
        elif u=='p':            
            ratio=1 
            X=int(q_o/r_p)
            if pkg_lv2:
                #瓶标做盒标使用,只有2级对应
                P=q_o % r_p
            else:
                H=int( (q_o-X*r_p) / (r_p/r_h) )
                P=(q_o-X*r_p-H*(r_p/r_h)) % (r_p/r_h)           
        
        #统一成最小单位瓶   剩余发货瓶数,如果已发数量大于订单数，取0    
        q0=max(q_o*ratio-q_d,0)          
        
        # 企业标签计量单位及数量
        std_qty={
            'label_pkg':std['label_pkg'],
            'unit_name':std['name'],
            'qty':0
        }
        if std['label_pkg']==1:
            std_qty['qty']=q0
        elif std['label_pkg']==2:
            std_qty['qty']=q0/(r_p/r_h)
        elif std['label_pkg']==3:
            std_qty['qty']=q0/r_p
        std_qty['qty']=int(std_qty['qty'])

        X0=0
        P0=0
        H0=0
        if q0>0:
            #剩余数量转换为整箱
            X0=int(q0/r_p)
            #剩余瓶数
            rm=q0 % r_p   
            #一盒有多少瓶，默认为1盒1瓶
            hp=1
            if r_p and r_h:
                hp=r_p/r_h
            if pkg_lv2:
                H0=0
                if u=='h':
                    #瓶标做盒标使用
                    P0=int(rm/hp)
                else:
                    P0=rm 
            else:
                H0=int(rm/hp)
                P0=int(rm % hp)             
        products.append({
            'id':r[3],
            'name':r[5],
            'specification':r[15],
            'qty':float(r[9]),
            'unit':r[10],
            'storehouse':{'id':r[6],'name':r[8],'code':r[7]},
            'order_type':{'id':r[0],'code':r[1],'name':r[2]},
            'x':X,
            'h':H,
            'p':P,
            'product_aging':r[14],  #2022-04-14 内参订单产品有产品生产年份信息
            'remaining':{'x':X0,'p':P0,'h':H0},
            'std_remaining':std_qty,
        })     
    data['products']=products
    if err_products:
        data['e']=",".join(err_products)+" 产品规格未定义"
    cur.close()
    return data


# 2022-08-08 
def deliverynote_remaining(request,company_id):
    """
    扫码枪请求发货单信息
    """
    data={'e':'','agent':{},'products':[]}    
    e = time_key_check(request, sK)
    if e:
        data['e']=e
        return JsonResponse(data)
    dn_number=request.GET.get('dn_number','')
    if not dn_number:
        data['e']='dn_number missing'
        return JsonResponse
    data=get_deliverynote_remaining(company_id,dn_number)    
    return JsonResponse(data)
    










