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,CompanyCfg
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





# 2022-08-15 内参扫码发货相关数据，是否从销售订单改为发货单
ON_DELIVERYNOTE=True
#ON_DELIVERYNOTE=False


#2022-11-02
from taskman import tasks
from inventory.models import Batch as StkBatch

# 2022-11-21 可变列表、字典要深拷贝
import copy 

# 2023-09-14
from inventory.views import taskman_post,batch_post

#**********
# 2023-07-03 喷信息是否区分订单类型
MARKING_GROUPBY_ORDERTYPE=True      #区分
#MARKING_GROUPBY_ORDERTYPE=False     #不区分
#*********








#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()
    ancestor_id = get_ancestorId(company_id)
    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 stkdetails.c"+ancestor_id+" 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 stkdetails.c"+ancestor_id+" 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
    #print(s)
    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 stkdetails.c"+ancestor_id+" 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']

    # 2022-09-09 内参促销物料 金蝶编码以 900 开头
    if company.id==1644 and code[:3]=='900':
        is_product=False

    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:
                        if d['sap_code'][0:1]=='F':
                            o = Company(parent_id=int(company_id), category_id=1, name=str(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
                        else:
                            o = objs[0]
                            o.is_delete = is_delete
                            o.save()
                            o.name = d['name']
                            try:
                                o.save()
                                is_ok = 'true'
                            except Exception:
                                pass
                    else:
                        if d['sap_code'][0:1]=='F':
                            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
                        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']+"'"

        #2022-11-17 易臻国际程慧君:杏仙堂的仓库是001	易臻国际程慧君:易臻的是05   易臻国际程慧君:按照这2个来
        if int(company_id)==19267 and store_code=="'001'":
            store_code="'05'"

        # 2022-11-14 易臻 大礼包 产品要转换为 易臻和杏仙堂真实产品名称数量   
        if int(company_id)==19267 and (product_code in ['04003','04004','04009']) :
            #2677 太极温阳灸贴	99999              3464 太极温养眼贴	8888	            2642 转念九选贡艾艾饼	00176
            #"code": "04003", "name": "【元气满满】直播大礼包" 	    【元气满满】直播大礼包  （ 包含：灸贴*8    眼贴*2    贡艾*1 ）
            #"code": "04004", "name": "【灸粉】直播大礼包"	        【灸粉】直播大礼包  （ 包含：灸贴*5    眼贴*1    贡艾*1 ）             
            gifts={
                "04003":{
                    2677:{'code':'99999',   'name':'太极温阳灸贴',      'qty':8}, 
                    3464:{'code':'8888',    'name':'太极温养眼贴',      'qty':2},
                    2642:{'code':'00176',   'name':'转念九选贡艾艾饼',  'qty':1}
                },
                "04004":{ 
                    2677:{'code':'99999',   'name':'太极温阳灸贴',      'qty':5}, 
                    3464:{'code':'8888',    'name':'太极温养眼贴',      'qty':1},
                    2642:{'code':'00176',   'name':'转念九选贡艾艾饼',  'qty':1}
                },
                #2022-12-12 易臻国际??程慧君??? 04009     【无包装】元气满满-艾礼
                #艾灸贴8盒，眼贴2盒，贡艾1盒
                "04009":{ 
                    2677:{'code':'99999',   'name':'太极温阳灸贴',      'qty':8}, 
                    3464:{'code':'8888',    'name':'太极温养眼贴',      'qty':2},
                    2642:{'code':'00176',   'name':'转念九选贡艾艾饼',  'qty':1}
                },
            }     
            u_name="'盒'"

            # 2022-11-15 182 新包装仓库	05   如果用友传过来无效仓库编码，默认为新包装仓库
            for k,v in gifts[product_code].items():    
                a.append("("+id+","+cid+",'"+v['code']+"','"+v['name']+"',"+str(k)+","+\
                    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+","+\
                        "coalesce((select id from inventory_storehouse where company_id="+str(company_id)+" and external_code="+store_code+" limit 1),182),"+\
                    str(int(float(q))*v['qty'])+","+u_name+","+u_code+","+product_aging+\
                ")")
        else:
            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 
"""


#2023-06-29
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')



    # 2023-06-29 取经销商ID
    cust_id='null'    
    cur.execute("select id,division_id from company_company where ancestor_id="+str(company_id)+" and external_code='"+customer_code+"' and is_delete=false limit 1")
    r=cur.fetchone()

    # 内参喷码信息需要知道客户是否为特渠客户
    SPECIAL_CHANNEL=False
    # 2023-07-19 传过来的外部编码可能不存在，即没有任何记录
    if r:
        if r[0]:
            cust_id=str(r[0])    
        if int(company_id)==1644:       
            if r[1] in [55]:            #55;"013";"特渠管理部"
                SPECIAL_CHANNEL=True

    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="+cust_id+" "\
            "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+"',"+cust_id+\
            ")  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,marking) values " #2023-06-29 添加喷码信息    
    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']+"'"

        #2022-11-17 易臻国际程慧君:杏仙堂的仓库是001	易臻国际程慧君:易臻的是05   易臻国际程慧君:按照这2个来
        if int(company_id)==19267 and store_code=="'001'":
            store_code="'05'"

        # 2022-11-14 易臻 大礼包 产品要转换为 易臻和杏仙堂真实产品名称数量   
        if int(company_id)==19267 and (product_code in ['04003','04004','04009']) :
            #2677 太极温阳灸贴	99999              3464 太极温养眼贴	8888	            2642 转念九选贡艾艾饼	00176
            #"code": "04003", "name": "【元气满满】直播大礼包" 	    【元气满满】直播大礼包  （ 包含：灸贴*8    眼贴*2    贡艾*1 ）
            #"code": "04004", "name": "【灸粉】直播大礼包"	        【灸粉】直播大礼包  （ 包含：灸贴*5    眼贴*1    贡艾*1 ）             
            gifts={
                "04003":{
                    2677:{'code':'99999',   'name':'太极温阳灸贴',      'qty':8}, 
                    3464:{'code':'8888',    'name':'太极温养眼贴',      'qty':2},
                    2642:{'code':'00176',   'name':'转念九选贡艾艾饼',  'qty':1}
                },
                "04004":{ 
                    2677:{'code':'99999',   'name':'太极温阳灸贴',      'qty':5}, 
                    3464:{'code':'8888',    'name':'太极温养眼贴',      'qty':1},
                    2642:{'code':'00176',   'name':'转念九选贡艾艾饼',  'qty':1}
                },
                #2022-12-12 易臻国际??程慧君??? 04009     【无包装】元气满满-艾礼
                #艾灸贴8盒，眼贴2盒，贡艾1盒
                "04009":{ 
                    2677:{'code':'99999',   'name':'太极温阳灸贴',      'qty':8}, 
                    3464:{'code':'8888',    'name':'太极温养眼贴',      'qty':2},
                    2642:{'code':'00176',   'name':'转念九选贡艾艾饼',  'qty':1}
                },
            }     
            u_name="'盒'"

            # 2022-11-15 182 新包装仓库	05   如果用友传过来无效仓库编码，默认为新包装仓库
            for k,v in gifts[product_code].items():    
                a.append("("+id+","+cid+",'"+v['code']+"','"+v['name']+"',"+str(k)+","+\
                    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+","+\
                        "coalesce((select id from inventory_storehouse where company_id="+str(company_id)+" and external_code="+store_code+" limit 1),182),"+\
                    str(int(float(q))*v['qty'])+","+u_name+","+u_code+","+product_aging+","+marking+\
                ")")
        else:      
            pid='null'
            mk=marking
            cur.execute("select id from product_product where company_id="+str(company_id)+" and external_code='"+product_code+"'")
            rp=cur.fetchone()
            if rp:
                pid=str(rp[0]) 
            # 2023-06-29 内参明细写入喷码信息
            if int(company_id)==1644 and marking!='null':   
                #第一类 52度500ml2021版 非费用非特渠部门客户订单喷码信息加 -赋码  后缀 （同时这类订单要推送到海商） 
                #id:2605 52度500mL内参酒(2021版）
                #第二类 所有费用订单喷码信息加 -FY 后缀
                #第三类 非一、二类订单正常喷码 不加后缀

                # 2023-10-19  
                # {"id": "8811", "name": "52度500mL内参酒（甲辰）"},
                # {"id": "2605", "name": "52度500mL内参酒(2021版）"}, 
                # {"id": "3470", "name": "52度500mL内参酒（纳祥瑞）"},                  
                # {"id": "4506", "name": "52度475mL内参酒（湘西版）"}
                # 1、甲辰全部喷 “赋码”
                # 2、2021版，纳祥瑞，湘西版 非费用非特渠 喷“赋码”
                if pid=='8811' or (pid in ['2605','3470','4506'] and not SPECIAL_CHANNEL and ordertype_code.replace("'","")=='001'):
                    mk="'"+marking.replace("'","")+"赋码'"
                else:
                    #"001";"现金订单" "002";"费用订单"                
                    if ordertype_code.replace("'","")=='002':
                        mk="'"+marking.replace("'","")+"-FY'"                            
            a.append("("+id+","+cid+","+\
                "'"+product_code+"','"+product_name+"',"+pid+","+\
                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+","+mk+\
            ")")
    if a:
        cur.execute(sIns+",".join(a))
    cur.close()
    return 


#2021-08-04 接收第三方发货单数据接口
@csrf_exempt
def sync_order(request,company_id,vendor,action=None):   
    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']


    # 2024-02-05 酒鬼TMS删除单据
    if action:
        if company_id=='1' and action=='delete':
            if 'dn_number' in d:
                dn=d['dn_number']
                ss=OrderSync.objects.filter(company_id=int(company_id),dn_number=dn)
                if ss:
                    if request.META.get('HTTP_X_FORWARDED_FOR'):
                        ip = request.META.get("HTTP_X_FORWARDED_FOR")
                    else:
                        ip = request.META.get("REMOTE_ADDR")
                    o=ss[0]
                    o.is_delete=True
                    o.deletion={
                        'ip':ip,
                        'time':r['time'],
                        'key':r['key']
                    }
                    o.save()
                    data['result']['msg']='成功删除发货单 '+dn
                else:
                    data['e']='dn_number '+dn+' 不存在'
            else:
                data['e']="参数名不正确"
            return JsonResponse(data)

        else:
            data['e']='unauthorized action'
            return JsonResponse(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():
        # 2022-08-23 内参订单号长度为10,分单发货的话,后面加3位数字
        if len(d['dn_number'])>12:
            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']

    # 2024-02-05 酒鬼TMS可能会传分销商信息
    if 'distributor' in d:
        o.distributor=d['distributor']

    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',        
    }
    ancestor_id = get_ancestorId(company_id)
    #将销售出库明细数量换算为瓶；若订单明细没有单位，默认为瓶
    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 stkdetails.c"+ancestor_id+" 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 stkdetails.c"+ancestor_id+" 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


# 2022-08-09
# 2023-06-28 添加参数marking，区分请求是来自喷码机(最终要区分现金和费用)还是扫码枪
def get_deliverynote_remaining(company_id,dn_number): 
    """
    检查发货单剩余发货数量
    """
    if company_id in (1,1644):
        is_standard=False
    else:
        is_standard=True

    if not is_standard:
        # 内参喷码机接口数据格式
        data={
            "e":"",
            "error_code":"",
            "dn_number":"",
	        "order_number":"",
	        "order_id":0,
	        "customer":{"id":0, "name":""},
	        "marking":'',
            "products":[],
            "products_total":{}
        }
    else:
        data={'e':'',"error_code":"",'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()
    ancestor_id = get_ancestorId(company_id)
    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:
        if not is_standard:
            # 内参喷码机接口数据格式
            data['dn_number']=dn_number
            data['order_number']=r[6]
            data['order_id']=r[4]
            data['marking']=r[3]   
            data['on_delivery']=True
        else:
            data['deliverynote']={    
                'id':r[4],
                'code':dn_number,
                'salesorder':{'id':r[5],'code':r[6]}
            }
        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,"   
    
    
    # 2023-06-28 内参金蝶同一产品会有不同订单类型的两条记录，喷码机接口修改
    # 是否区分订单类型    
    group_by_ordertype=False    #扫码枪产品不区分订单类型




    if group_by_ordertype:         
        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,coalesce(ordertype_id,0) 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 stkdetails.c" + ancestor_id + " 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 "
    else:
        s="select coalesce((select id from erp_ordertype where company_id="+str(company_id)+" and code='000'),0), '000', '不区分',"+\
            "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,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,store_id,unit_name,product_aging,deliverynote_id "+\
            ") o "+\
            "left outer join "+\
            "(select product_id,sum(x) x,sum(h) h,sum(p) p from "+\
                "(select product_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 stkdetails.c"+ancestor_id+" 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 "+\
            ") d "+\
            "on o.product_id=d.product_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,external_code,name from inventory_storehouse where company_id="+str(company_id)+") s "+\
            "on o.store_id=s.id "  
    cur.execute(s)
    err_products=[]

    i=0 #产品种类计数
    q=0 #数量合计
    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'  

        # 2022-05 如果产品规格为二级包装，单位u 转为 'p'
        a_spec=r[15].split('*')        
        if len(a_spec)==2:
            u='p'

        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:
                if len(a_spec)==3:
                    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)
                elif len(a_spec)==2:
                    P=q_o
        
        #统一成最小单位瓶   剩余发货瓶数,如果已发数量大于订单数，取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:
            if len(a_spec)==3:
                std_qty['qty']=q0/(r_p/r_h)
            elif len(a_spec)==2:
                std_qty['qty']=q_o

        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:
            q=int(float(q))+int(q_o)
            i=i+1

            #剩余数量转换为整箱
            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:
                if len(a_spec)==3:
                    H0=int(rm/hp)
                    P0=int(rm % hp)
                elif len(a_spec)==2:
                    H0=0
                    P0=rm 
        if not is_standard:
            # 内参喷码机接口数据格式
            products.append({
                'id':r[3],
                'name':r[5],                
		"qty":int(r[9]),
	        "unit":r[10],
                'product_aging':r[14],  #2022-04-14 内参订单产品有产品生产年份信息
	        'storehouse':{'id':r[6],'name':r[8],'code':r[7]},
                'order_type':{'id':r[0],'code':r[1],'name':r[2]},
                'x':int(X),
                'h':int(H),
                'p':int(P), 
	        "remaining":{'x':X0,'p':P0,'h':H0}
            })            
        else:
            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]},                
                'x':int(X),
                'h':int(H),
                'p':int(P),               
                'remaining':{'x':X0,'p':P0,'h':H0},
                'std_remaining':std_qty,
            })     
    data['products']=products
    data['products_total']={'qty':q,'cnt':i}
    if err_products:
        data['e']=",".join(err_products)+" 产品规格未定义"
    cur.close()
    return data



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

    

    # 2024-02-05 酒鬼TMS有可能删除了发货单
    if d.is_delete:  
        tm=d.deletion['time']
        data['e']='发货单 '+dn_number+' '+tm+' 已删除'
        return data

    
    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'])) 
    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]    

    #if u.company_id==19267:
    #    ON_DELIVERYNOTE=True


    # 2022-08-17
    #if u.company_id==1644:
    # 2022-11-14
    if u.company_id in [1644,19267]:
        d=get_deliverynote_remaining(u.company_id,dn)
        data['e']=d['e']
        data['error_code']=''
        d.pop('e')
        d.pop('error_code')
        data['data']=d
    else:
        data=get_order_infor(u.company,dn)   
    
    #data=get_order_infor(u.company,dn)


    return JsonResponse(data)



#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,vendor=None):
    data={'e':'','flag':'','remedy':'','url':''}
    flag=''

    #补救码
    remedy='' 


    # 2024-01-24 是否可跳转馥郁荟抽奖
    e=''
    if vendor:
        data={'e':'','redirect':False}        
        sKs={               
            'hand':'hand$tYal1i%)20@)c1W',
        }
        if vendor in sKs:
            sK=sKs[vendor]
            e=time_key_check(request, sK)
        else:
            e='vendor is not defined'
    if e:
        data['e']=e
        return JsonResponse(data)

    
    c=request.GET.get('code','')
    if not c:
        data['e']='parameter code missing'
        return JsonResponse(data)     
    
    # 2022-07-27 保存原码值
    c0=c
    
    # 2023-09-25
    is_bottlecap=False
    
    cur=connection.cursor()

    # 2023-01-10 标签可能带 11,22,33,1_,2_,3_
    if len(c)==14 and c[:2] in ('11','22','33','1_','2_','3_'):
        c=c[2:] 

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

        #data['e']='非加密码'
        #return JsonResponse(data)
        
        # 2022-06-30 传过来的也可能不是加密码，如万里走单骑，新世纪(恒和商务）独产箱标
        pass
    else:          

        # 2023-06-13
        # 2023-07-28 盖码有gd开头，长度13
        if len(c)==13 and c[:2]=='gd':
            c='h'+c[2:]
            data['remedy']=c
        label_code=decrypt_11h(c)  

        



        #先检查传过来的标签是在线绑定的瓶盖码,把盖码转为对应瓶标  
        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)),"+\
            "(select id from z_jg_bottlecap_remedy where cap_old='"+c+"' order by id desc limit 1)"
        cur.execute(s)
        r=cur.fetchone()    
        if r[0]:

            # 2023-09-25 
            is_bottlecap=True

            c=r[0]     
            # 2022-10-13 特殊情况处理：瓶盖有绑定记录，但瓶盖被误扫抽过奖，返回补救码给第三方
            if r[1]:
                s="select bottle,cap_new,url,coalesce(flag,'') from logistics.z_jg_bottlecap_remedy where id="+str(r[1])
                cur.execute(s)
                rr=cur.fetchone()
                remedy=rr[1]
                data['flag']=rr[3]
                data['remedy']=remedy
                return JsonResponse(data)
        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'])


            # 2023-01-12 内参酒跳转到内参指定页面 内参密友荟
            product=q.get_product()
            product_id=str(product['id'])            
            product_name=product['name']            
            
            if int(product_id) in [3386,3885,2408, 8800] and c0[0] in ['g','h']: 
                # 3386 52度500mL内参酒（纳祥瑞）只跳瓶盖码
                # 3885 52度475mL内参酒（湘西版）只跳瓶盖码 2023-03-10
                # 2408 52度500ml内参酒（2021版）只跳瓶盖码 2023-05-05
                # 8800 52度500mL内参酒（甲辰）  只跳瓶盖码 2023-10-19


                
                # 正式上线前，先只跳转测试码                
                nc_online=True    

                #if c0 in ["g0607fa96c34","g1609560c03r","g360982e1239","g8607fbc283m","ge60982c663s","g66096f79d3s"]:
                #    nc_online=True

                if nc_online==True:
                    data['flag']='NC'
                    data['url']='https://jgjnc.com/m/#/sub/scan/search?code='+c0
                    #            https://jgjnc.com/m/#/sub/scan/search?code=g060c0e3d43e
                    return JsonResponse(data)              
            
            # 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':
                    if c0[0]=='h':
                        # data['url']=data['url']+c0
                        # 2023-11-10 恒河商务 原 https://hhe.shuzhima.cn/v2/?s= 改为 henghesw.com
                        data['url']=data['url']+'/?t='+c0  
                    else:
                        data['flat']=''
                        data['url']=''
                return JsonResponse(data) 
            
            # 2022-09-09 唐山妙品
            # https://tjfnew.china315net.com/jgsm/end_tsmp.html
            # 如果产品名称 含唐山妙品 或 经销商是  唐山妙品商贸有限公司  # id:17617
            customer=''
            customer_id='0'
            for i in q.get_stk_dev():
                #if i['vtype_id'] == 2:
                # 2023-10-17
                if i['vtype_id'] == 2 and i['agent']['id']:
                    customer = i['customer']
                    customer_id=str(i['agent']['id'])
            #if '唐山妙品' in customer and product_id in  ['1075','1243','942','1246','33','1245'] :
            # 2023-05-12
            # 33;"39度500mL酒鬼酒（传承）（2*2）"1075;"42度500mL红坛酒鬼酒（20）" 跳汉得            
            if '唐山妙品' in customer and product_id in   [        '1243','942','1246',     '1245'] :
                data['flag'] = 'TSMP'
                data['url'] = 'https://tjfnew.china315net.com/jgsm/end_tsmp.html'
                return JsonResponse(data)

            
            s='select products @> '+"'"+'[{"id":"'+product_id+'"}]'+"',"+\
                "(select "+customer_id+" in (SELECT customer_id FROM thirdparty_exemption where company_id=1 and vendor_id=3)) "+\
                "from thirdparty_redirect where company_id="+company_id+" and code='HD'"       
            cur.execute(s)
            r=cur.fetchone()   
            #是一物一码产品, 且不是酒鬼国际客户   
            if r[0] and not r[1] :                               
                    #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']    

                    # 2022-11-30 透云停服，所有跳转都到汉得
                    flag="HD"

                    # 2023-09-25 酒鬼酒传承系列小圆标不做跳转，正常显示我司页面即可
                    if '传承' in product_name and not is_bottlecap:
                        return JsonResponse(data) 
                        
                    
                    # 2023-06-13 补推120多万箱内品酒，没盖码的推小圆标；小圆标不跳汉得
                    if '内品' in product_name:
                        s="select id from "+\
                            "(select id from logistics.z_bottlecap_binding where code='"+label_code+"' "+\
                                " union "+\
                                "select id from label.bottlecap where code='"+label_code+"'"+\
                            ") a" 
                                   
                        #print(s)
                        
                        cur.execute(s)
                        rrr=cur.fetchone()
                        
                        if not rrr:  
                            #print('不是瓶盖码！')                          
                            return JsonResponse(data) 

                    # 2023-04-12                    
                    #66;3438;"52度500ml内品酒（紫盒）"
                    pids=['3438',] 
                    # 两批次10万盖码使用了错误的加密版本(加密码最后一位不确定)  
                    
                    # 2023-07-17 42 500ml 
                    # 3463;"42度500ml内品酒（紫盒）";;;"281337"
                    pids=['3438','3463']
                    
                    
                    
                    #if product_id in pids and c[:3]=='444':
                    if str(product_id) in pids and label_code[:3]=='444':

                        #dt_to='2023-10-01' # 10万盖码最后使用日期
                        #dt_to='2023-05-24' # 2023-05-24:程雨枫 老版的紫盒瓶盖用完了
                        dt_to='2023-07-14'

                        #sWhere=" where code='"+c+"' and (dt between '2023-04-11' and '"+dt_to+"')"  
                        # 2023-05-16 2022年12月18-19日，有12000瓶"52度500ml内品酒（紫盒）"
                        #sWhere=" where code='"+c+"' and (dt between '2022-12-17' and '"+dt_to+"')"  

                        # 2023-12-22 加密版本1最后一位数字不固定，只能查询解密后的明码是否有瓶盖绑定记录
                        sWhere=" where code='"+label_code+"' and (dt between '2022-12-17' and '"+dt_to+"')"
                        s="select label,code,bottle FROM logistics.z_bottlecap_binding"+sWhere+" order by id desc"  
                        # 2023-12-22 查询两个表
                        s="select label,code,bottle,id from ("+\
                            "select id,label,code,bottle FROM logistics.z_bottlecap_binding"+sWhere+\
                            " union "+\
                            "select id,label,code,bottle FROM label.bottlecap"+sWhere+\
                        ") u order by id desc"                        
                        
                        
                        cur.execute(s)
                        rt=cur.fetchone()                        
                        if rt:                            

                            cap=rt[0]  
                            # 无论加密码是否一致，都做记录                             
                            s="INSERT INTO logistics.z_bottlecap_version(v1,v2,bottle,box,company_id,label) values "+\
                                "('"+c0+"','"+cap+"','"+q.infor['wl']+"','"+q.infor['wl']+"',"+company_id+",'"+c+"')"
                            cur.execute(s)   

                            #print('cap:',cap,'c0:',c0)
                            if cap!=c0:
                                # 确认扫码盖码与版本2加密后盖码不一致，工厂扫码绑定时通过瓶盖上明码生成的盖码(返回加密版本2)的加密码给第三方
                                data['remedy']=cap
                            data['flag']=flag
                            return JsonResponse(data)    



                    '''
                    #云平台有发货记录            
                    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=='':                    
                #可能是云平台上线之前，历史数据，查看跳转表
                cur.execute("select flag from z_bottle_redirect where label='"+c+"'")
                r=cur.fetchone()        
                if r:            
                    # flag=r[0]

                    # 2022-11-30 透云停服，所有跳转都到汉得
                    flag="HD"


        # 2023-11-29 酒鬼酒：非营销平台活动产品的小红帽瓶盖，跳二维码落地页新页面
        if flag=='' and is_bottlecap:
            # flag='RedCap'
            data['url']='http://tjfnew.china315net.com/jgsm/wxsq/gzh.html'       






        # 2024-01-24
        if vendor:
            if flag=='HD' and is_bottlecap:
                data['redirect']=True
            if 'url' in data:
                data.pop('url')
        else:
            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',     #汉得
            'sfa':      'hand$tYal1i%)20@)c1W',     #SAF项目组 2022-11-21 跟hand使用统一的key
            'zmd':      'zmd$Dg2lU$jhl0)AqiCj',     #2023-08-16酒鬼酒专卖店项目
        },        
        # 2023-01-03
        '19267':{
            'app':      'yz$3Ylz%T270)0b9kapp',     #易臻生物科技 
        },
    }       
    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'

    # 2022-12-02 汉得传参有时带http转接
    code=code.replace("http://tj1.me/?t=","")  

    # 2023-01-10 标签可能带 11,22,33,1_,2_,3_
    if len(code)==14 and code[:2] in ('11','22','33','1_','2_','3_'):
        code=code[2:]


    #写入第三方查询日志
    s="INSERT INTO log.label_query_3rd(company_id,vendor,ip,code) "+\
        "VALUES ("+company_id+",'"+vendor+"','"+ip+"','"+code+"')"
    cur=connection.cursor()

    cur.execute(s)
    ancestor_id = get_ancestorId(company_id)
    
   

    # 2023-01-03 易臻APP接口，只检查码是否存在
    if company_id=='19267' and vendor=='app':
        data = {'e': '', 'result':{'is_ok':False} }
        # AB标内是否存在
        cur.execute("SELECT code_a FROM label.lbl_abcode where code_a='"+code+"' or code_b='"+code+"'")
        r=cur.fetchone()
        if r:
            # 有AB标对应
            data['result']['is_ok']=True
        else:
            try:
                cur.execute("select id from label.t"+code[:4]+" where code='"+code+"'")
                r=cur.fetchone()
                if r:
                    # 号段表存在，且表内有这个标号码
                    data['result']['is_ok']=True
            except Exception:
                # 可能传过来的号码前4位的标签表都不存在
                pass
        cur.close()
        return JsonResponse(data)


    
    # 2022-11-17
    q=LabelQuery(company_id,code)       
    #2022-06-17
    #q=LabelQueryOld(company_id,code) 

    if not q.infor['label_pk_id']:
        data['e']='标签号码错误'
    else:
        pk=str(q.infor['label_pk_id'])
        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 stkdetails.c"+ancestor_id+" where company_id="+aid+" "+\
                                "and label_pk_id="+pk+" and vtype_id=1 and is_delete=false "+\
                            "union all "+\
                            "select product_id,tm from stkdetails.c"+ancestor_id+" 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:
                data['e']='标签号码未激活'
                
        else:            
            if company_id=='1' and vendor=='sfa':
                # 2022-11-17 酒鬼酒中粮SFA项目组               
                # 取经销商信息
                s="select external_code,name from "+\
                    "(select company_to_id from stkdetails.c1 where label_pk_id="+pk+" and vtype_id=2 and is_delete=false order by id desc limit 1) s "+\
                    "left outer join "+\
                    "(select id,name,external_code from company_company where ancestor_id=1) c "+\
                    "on s.company_to_id=c.id"
                cur.execute(s)
                rr=cur.fetchone()
                if rr:
                    code=rr[0]
                    name=rr[1]
                else:
                    code=''
                    name=''
                rls=q.get_relations() 
                # 主箱标
                ck=q.infor['label_pk_code']
                # 副箱标
                atts=[]      

                # 2024-02-19 汉得需要返回转角码C005
                C005=''
                att=''

                for c in rls['cartons']:                  
                    if c['label']!=ck:
                        atts.append(c['label'])  
                if atts:
                    att=atts[0]
                    # 2024-02-19 另一关联箱标(汉得称转角码 C005)
                    if len(atts)>1:
                        C005=atts[1]               
                    
                d={
                    "prodCode": p['external_code'],
                    "prodName": p['name'],                   
                    "acctCode": code,
                    "acctName": name,                    
                    "cartonCode":ck ,
                    "cartonOtherCode": att,
                    
                    # 2024-02-19
                    "C005":C005
                }                 
                if q.package['id']==1:
                    # 瓶标
                    d["logisticsCode"]=q.infor['wl']
                    data["result"]=d
                else:
                    # 箱标
                    ds=[]                                      
                    #for c in rls['children']:                      
                    #    dd=copy.deepcopy(d)
                    #    dd["logisticsCode"]=c['label']                       
                    #    ds.append(dd)  


                    # 2023-08-08 SFA 大坛酒，如果只有箱标，把箱标写到瓶标数组里
                    if rls['children']:
                        for c in rls['children']:                      
                            dd=copy.deepcopy(d)
                            dd["logisticsCode"]=c['label']                       
                            ds.append(dd)
                    else:
                        d['logisticsCode']=ck
                        ds.append(d)

                    data["result"]=ds                                            
            else:
                if vendor=='zmd':
                    # 酒鬼酒专卖店项目                   
                    p_d={1:3, 2:2, 3:1, 100:4}       #1:箱码 2 盒码 3 瓶码 4 盖内码                    
                    ctype=p_d[q.package['id']]
                    if q.infor['is_bottlecap']:
                        ctype=4
                    r={'code':code,'type':ctype,'prodCode':p['external_code'],'prodName':p['name'],'batchNo':p['inbound_lot'],'produceTime':p['inbound_date'],'dealerCode':'','dealerName':''}                   
                    agt=q.get_endclient()
                    if agt:
                        r['dealerCode']=agt['external_code']
                        r['dealerName']=agt['name']

                    return JsonResponse({'e':'','result':r})                    
                else:
                    # 2023-08-29 增加cartons数组                    
                    rls=q.get_relations() 
                    # 主箱标
                    ck=q.infor['label_pk_code']
                    cartons=[ck]
                    # 副箱标
                    atts=[]                   
                    for c in rls['cartons']:                  
                        if c['label']!=ck:
                            cartons.append(c['label']) 
                    data['result']['cartons']=cartons


                    #给第三方数据，删除产品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':
                    # 2023-03-06 是否要返回防伪信息
                    security_infor=request.GET.get('security_infor','0')
                    if security_infor=='1':
                        d=q.get_security_info()
                    else:
                        d={"times":0,"image":""}
                    data['result']['security_queries']=d



    cur.close()
    return JsonResponse(data)    












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


    data={'error_code':0, 'e':'', 'data':{
        'dn_number':'',
        'order_number':'',  
        'order_id':0,
        'customer':{}, 
        'marking':'',
        'deliverynote_id':0,        # 2022-08-31
        '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
    # 2022-08-15
    if ON_DELIVERYNOTE:
        s="select id,code,agent_id,agent_name,coalesce(marking,''),coalesce(salesorder_code,'') "+\
            "from inventory_deliverynote where code='"+dn+"'"
    else:
        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[4]:
            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]                              # 2022-08-31 喷码机调整完成后，这里要取销售订单ID
    data['data']['marking']=rr[3]
    data['data']['dn_number']=dn
    data['data']['marking']=rr[4]
    data['data']['deliverynote_id']=rr[0]       # 2022-08-31
    if ON_DELIVERYNOTE:
        data['data']['order_number']=rr[5]
    else:
        data['data']['order_number']=rr[1]
    data['data']['order_id']=rr[0]
    data['data']['customer']={'id':rr[2],'name':rr[3]}

    if ON_DELIVERYNOTE:
        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,'瓶'),"+\
            "case o.unit_name when '瓶' then o.qty "+\
                             "when '盒' then o.qty*(p.p/p.h) "+\
                             "when '箱' then o.qty*p.p "+\
                             "else o.qty end 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 from inventory_dndetails "+\
                "where company_id="+str(company_id)+" and deliverynote_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 deliverynote_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" 
        # 2023-04-05 内参喷码机纸质发货单不同年份产品数量已合并 内参李正凯：“他这个订单在喷码机里面没有合并数量”
        # 这里改为相同产品不再区分年份
        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,'瓶'),"+\
            "case o.unit_name when '瓶' then o.qty "+\
                             "when '盒' then o.qty*(p.p/p.h) "+\
                             "when '箱' then o.qty*p.p "+\
                             "else o.qty end qty0,"+\
            "p.h, p.p,coalesce(d.qty0,0),'' product_aging from "+\
            "(select product_id,ordertype_id,store_id,unit_name,sum(qty) qty from inventory_dndetails "+\
                "where company_id="+str(company_id)+" and deliverynote_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"+\
            ") o "+\
            "left outer join "+\
            "(select product_id,ordertype_id,sum(qty0) qty0 from thirdparty_inkjetdetails where "+\
                "company_id="+str(company_id)+" and deliverynote_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" 
    else:    
        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" 
        # 2023-04-05 内参喷码机纸质发货单不同年份产品数量已合并 内参李正凯：“他这个订单在喷码机里面没有合并数量”
        # 这里改为相同产品不再区分年份
        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),'' product_aging from "+\
            "(select product_id,ordertype_id,store_id,unit_name,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"+\
            ") 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=int(r[11])
        #已喷码瓶数
        qty0_d=r[14]
        
        #1箱有多少盒        
        r_h=r[12]
        #1箱有多少瓶，
        r_p=r[13]

        #剩余瓶数
        rem=qty0_o-qty0_d        
        #print(r_h,r_p)
        #print(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=''
        if r[15]:
            yr=r[15]
        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)


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

    # 2023-06-30 是否相同产品是否区分订单类型
    # group_by_ordertype = MARKING_GROUPBY_ORDERTYPE
    


    data={'error_code':0, 'e':'', 'data':{
        'dn_number':'',
        'order_number':'',  
        'order_id':0,
        'customer':{}, 
        'marking':'',
        'deliverynote_id':0,        # 2022-08-31
        '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
    # 2022-08-15
    if ON_DELIVERYNOTE:
        s="select id,code,agent_id,agent_name,coalesce(marking,''),coalesce(salesorder_code,'') "+\
            "from inventory_deliverynote where code='"+dn+"'"
    else:
        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[4]:
            data['e']="单号 "+rr[1]+" 喷码信息为空"
            cur.close()
            return JsonResponse(data) 

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

    # 2023-07-03 截止目前，这里指的是 发货单DeliveryNote id
    order_id=rr[0]     
    

    # 2023-06-26 喷码信息
    mk=rr[4]      
    data['data']['marking']=mk  #保留数据结构外部喷码信息marking不变
    data['data']['dn_number']=dn   
    data['data']['deliverynote_id']=rr[0]       # 2022-08-31
    if ON_DELIVERYNOTE:
        data['data']['order_number']=rr[5]
    else:
        data['data']['order_number']=rr[1]
    data['data']['order_id']=rr[0]
    data['data']['customer']={'id':rr[2],'name':rr[3]}

    if ON_DELIVERYNOTE:     
        # 2023-06-30 内参金蝶同一产品会有不同订单类型的两条记录，喷码机接口修改  
        #if group_by_ordertype:
            # 需要对订单类型分类
        
        # 2023-07-04 不判断MARKING_GROUPBY_ORDERTYPE,保存原来的数据结构,只在产品明细多个参数marking
        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,'瓶'),"+\
            "case o.unit_name when '瓶' then o.qty "+\
                             "when '盒' then o.qty*(p.p/p.h) "+\
                             "when '箱' then o.qty*p.p "+\
                             "else o.qty end qty0,"+\
            "p.h, p.p,coalesce(d.qty0,0),'' product_aging,coalesce(marking,'"+mk+"') from "+\
            "(select product_id,ordertype_id,store_id,unit_name,marking,sum(qty) qty from inventory_dndetails "+\
                "where company_id="+str(company_id)+" and deliverynote_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,marking"+\
            ") o "+\
            "left outer join "+\
            "(select product_id,ordertype_id,sum(qty0) qty0 from thirdparty_inkjetdetails where "+\
                "company_id="+str(company_id)+" and deliverynote_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" 
        
        '''
        else:
            # 不对订单类型分类
            s="select coalesce((select id from erp_ordertype where company_id="+str(company_id)+" and code='000'),0), '000', '不区分',"+\
            "p.id, coalesce(p.external_code,''), p.name,"+\
            "s.id, s.external_code, s.name,"+\
            "o.qty, coalesce(o.unit_name,'瓶'),"+\
            "case o.unit_name when '瓶' then o.qty "+\
                             "when '盒' then o.qty*(p.p/p.h) "+\
                             "when '箱' then o.qty*p.p "+\
                             "else o.qty end qty0,"+\
            "p.h, p.p,coalesce(d.qty0,0),'' product_aging,'"+mk+"' marking from "+\
            "(select product_id,store_id,unit_name,sum(qty) qty from inventory_dndetails "+\
                "where company_id="+str(company_id)+" and deliverynote_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,store_id,unit_name"+\
            ") o "+\
            "left outer join "+\
            "(select product_id,sum(qty0) qty0 from thirdparty_inkjetdetails where "+\
                "company_id="+str(company_id)+" and deliverynote_id="+str(order_id)+" and is_ok=true "+\
                "group by product_id"+\
            ") d "+\
            "on o.product_id=d.product_id "+\
            "left outer join "+\
            "(select id,name,coalesce(qty_h,1) h,coalesce(qty_p,1) p,external_code from product_product where company_id="+str(company_id)+") p "+\
            "on o.product_id=p.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"    
        '''

    else:    
        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,''),'"+mk+"' marking 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" 
        # 2023-04-05 内参喷码机纸质发货单不同年份产品数量已合并 内参李正凯：“他这个订单在喷码机里面没有合并数量”
        # 这里改为相同产品不再区分年份
        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),'' product_aging,'"+mk+"' marking from "+\
            "(select product_id,ordertype_id,store_id,unit_name,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 "+\
            ") 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=int(r[11])
        #已喷码瓶数
        qty0_d=r[14]
        
        #1箱有多少盒        
        r_h=r[12]
        #1箱有多少瓶，
        r_p=r[13]

        #剩余瓶数
        rem=qty0_o-qty0_d        
        #print(r_h,r_p)
        #print(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=''
        if r[15]:
            yr=r[15]
        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},

            # 2023-06-30 喷码信息写入产品明细
            'marking':r[16]
        })     
    cur.close()
    data['data']['products']=products
    return JsonResponse(data)














#2022-02-21 喷码机更换产品接口
@csrf_exempt
def marking_product00000(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-11-01 为尽快给喷码机返回结果，改为直接写入发货明细
        #print(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
        
        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),"+\
            "outbound,marking FROM thirdparty_inkjetbatch where id="+bid
        cur.execute(s)
        rr=cur.fetchone()       
        if not rr:
            data['e']='batch id '+bid+' not exists'
            cur.close()
            return JsonResponse(data)        
        
        # 没有写入过销售出库
        #if not rr[8]:
        if True:

            aid=c.ancestor_id
            cid=c.id
            tocid=rr[7]
            pid=rr[1]
            uid=rr[2]
            num=rr[4]
            if rr[6]:
                num=rr[6]              


            # 2023-06-30 喷码信息            
            #第一类52度500ml2021版 非费用非特渠部门客户订单喷码信息加-赋码后缀(同时这类订单要推送到海商
            #第二类所有费用订单喷码信息加-FY 后缀
            #第三类非一二类订单正常喷码不加后缀
            post_to_haishang=False
            mk=None
            if rr[9]:
                mk=rr[9]
                if mk[-2:]=='赋码':
                    post_to_haishang=True


            # batch
            b=StkBatch(
                company_id=cid,
                company_fr_id = cid,
                level_id=c.level_id,
                hierarchy_id=c.hierarchy_id,
                version=c.version,
                ancestor_id=aid,
                batch_no=num,
                code=num,    
                salesorder_code=num,

                user_id = uid,
                reference_id=int(bid),
                vtype_id=2,
                product_id=rr[1],                
                store_fr_id=rr[3],
                dn_number=rr[4],
                salesorder_type_code=rr[5],                
                company_to_id=tocid,
                
                # 2023-06-30 为节省字段资源，内参喷码信息写入 rmk_name
                # 2022-05-16 成义烧坊，修改单据时，可添加客户名和地址 rmk_name=models.CharField(_('客户/收货人姓名'),max_length=100,blank=True,null=True)
                rmk_name=mk
            )
            b.save()            
            rid=str(bid) # inkjetbatch id
            aid=str(aid)
            cid=str(cid)
            bid=str(b.id)
            pid=str(pid)
            tocid=str(tocid)
            # batchdetails
            s="insert into inventory_batchdetails(batch_id,company_id,ancestor_id,product_id,label_code,label_id,label_pkg,label_pk_id) " +\
                "select "+bid+","+cid+","+aid+",product_id,label_code,label_id,label_pkg,label_pk_id "+\
                "from thirdparty_inkjetdetails where batch_id="+rid+" and is_ok=true"
            cur.execute(s)
            # stkdetails
            tb_stk="stkdetails.c"+aid
            s="INSERT INTO "+tb_stk+"(batch_id,company_id,ancestor_id,product_id,company_to_id,store_fr_id,store_id," + \
                "vtype_id,label_code,label_id,label_pkg,label_pk_id) select " + \
                bid+","+cid+","+aid+","+pid+","+tocid+",store_id,store_id,"+\
                "2, label_code,label_id,label_pkg,label_pk_id "+\
                "from thirdparty_inkjetdetails where batch_id="+rid+" and is_ok=true"
            cur.execute(s)

            
            
           
            # 发送给博智
            #if cid=='1644':
            #    # nc_sm_test 扫码测试
            #    if uid!=12962:
            #        #内参，异步任务服务器发送 博智
            #        result=tasks.post_bz.delay({
            #            'batch':{'id':b.id,'code':b.code},
            #            'actions':['delivery']
            #        })         


            # 2023-07-13 根据喷码信息判断，是否发送给海商
            #if post_to_haishang:
            #    # 推送给海商
            #    try:
            #        result_hs=tasks.post_hs.delay({
            #            'batch':{'id':b.id,'code':b.code},
            #            'actions':['delivery']
            #        })         
            #    except Exception:                    
            #        pass


            # 2023-09-14 是否推送第三方，统一调用 inventory_views.taskman_post(batch)            
            if cid=='1644':
                try:
                    taskman_post(b)
                except Exception:
                    pass


            # 修改状态
            s="update inventory_batch set dt_handled=now(),tm_handled=now() where id="+bid
            cur.execute(s)
            s="update thirdparty_inkjetbatch set outbound=true where id="+bid
            cur.execute(s)

            
            # 2022-11-03 经销商是否自动入库
            auto_inbound=False
            cfgs=CompanyCfg.objects.filter(company_id=int(aid)) 
            if cfgs:
                cfg=cfgs[0]
                if c.level_id==1 and cfg.inbound_auto_lv1:
                    auto_inbound=True  
            if auto_inbound:
                # 经销商入库batch
                bth=StkBatch(
                        #14499; "system"; "系统管理员"
                        user_id = 14499, 
                        vtype_id=1,
                        reference_id=int(bid),

                        company_id=int(tocid),
                        company_fr_id = int(cid),
                        hierarchy_id=c.hierarchy_id,
                        version=c.version,
                        ancestor_id=int(aid),
                        level_id=c.level_id,                    
                        batch_no=num,
                        code=num,    
                        product_id=int(pid),   
                )
                bth.save()       
                bid=str(bth.id) #经销商入库batch id
                # batchdetails
                s="insert into inventory_batchdetails(batch_id,company_id,ancestor_id,product_id,label_code,label_id,label_pkg,label_pk_id) " +\
                        "select "+bid+","+tocid+","+aid+","+pid+",label_code,label_id,label_pkg,label_pk_id "+\
                        "from thirdparty_inkjetdetails where batch_id="+rid+" and is_ok=true"
                cur.execute(s)
                # stkdetails
                s="INSERT INTO "+tb_stk+"(batch_id,company_id,ancestor_id,product_id,vtype_id,label_code,label_id,label_pkg,label_pk_id) " + \
                        "select "+bid+","+tocid+","+aid+","+pid+",1,label_code,label_id,label_pkg,label_pk_id "+\
                        "from thirdparty_inkjetdetails where batch_id="+rid+" and is_ok=true"
                cur.execute(s)
            
                #print('错误类型是',e.__class__.__name__)#except:#jia
                #print('错误明细是',e.__class__.__name__,e)   #continue#jia
                #ff=open('F:\\hzy\\log\\err.txt','a+')
                #ff.write(str(e)+'\n')
                #ff.close()
                



    #2022-05-27
    if int(r['order_id'])==0:
        #结束当前订单 通常用于下班前最后一单,这时操作人员不会去扫新订单
        pass
    else:        
        if ON_DELIVERYNOTE:
            deliverynote_id=int(r['order_id'])
            #s="select qty,unit_name,"+\
            #    "case o.unit_name when '瓶' then o.qty "+\
            #        "when '盒' then o.qty*(p.p/p.h) "+\
            #        "when '箱' then o.qty*p.p "+\
            #        "else o.qty end qty0,"+\
            #    "(select salesorder_id from inventory_deliverynote where id="+str(r['order_id'])+") "+\
            #    "from "+\
            #    "(select unit_name,qty,product_id from inventory_dndetails where deliverynote_id="+str(r['order_id'])+" "+\
            #        "and product_id="+str(r['product_id'])+" and ordertype_id="+str(r['ordertype_id'])+") o "+\
            #    "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(c.id)+") p "+\
            #    "on o.product_id=p.id"

            # 2022-12-21 内参分单操作，金蝶可能先分单，后推主订单，会导致分单对应的主订单id为空 salesorder_id
            s="select qty,unit_name,"+\
                "case o.unit_name when '瓶' then o.qty "+\
                    "when '盒' then o.qty*(p.p/p.h) "+\
                    "when '箱' then o.qty*p.p "+\
                    "else o.qty end qty0,"+\
                "coalesce((select salesorder_id from inventory_deliverynote where id="+str(r['order_id'])+"),"+\
                    "(SELECT salesorder_id FROM inventory_deliverynote where company_id="+str(c.id)+" "+\
                        "and salesorder_code=(select salesorder_code from inventory_deliverynote where code='"+r['dn_number']+"' limit 1) "+\
                        "and salesorder_id is not null limit 1) "+\
                ") from "+\
                "(select unit_name,qty,product_id from inventory_dndetails where deliverynote_id="+str(r['order_id'])+" "+\
                    "and product_id="+str(r['product_id'])+" and ordertype_id="+str(r['ordertype_id'])+") o "+\
                "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(c.id)+") p "+\
                "on o.product_id=p.id"
        else:
            deliverynote_id=None
            s="select qty,unit_name,qty0,salesorder_id 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:        
            salesorder_id=None
            if rr[3]:
                salesorder_id=rr[3]
            # 2022-12-21 内参分单操作，金蝶只推了分单，没推主订单
            else:
                if ON_DELIVERYNOTE:
                    data['e']='请检查'+r['dn_number']+'主订单ID是否为空'

            b=InkJetBatch(    
                company_id=c.id,
                salesorder_id=salesorder_id,
                deliverynote_id=deliverynote_id,
                ordertype_id=r['ordertype_id'],
                store_id=r['store_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 喷码机更换产品接口
@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)    

    """
    "key": "6858c94dc7900b0b57943a1de71cd4d4", 
    "time": "2023-07-01 10:34:10", 
    "marking": "0512JSMZH", 
    "user_id": "12670", 
    "batch_id": 8260, 
    "order_id": "56606", 
    "store_id": "45", 
    "dn_number": "20230615566", 
    "product_id": "2605", 
    "ordertype_id": "1", 
    "deliverynote_id": "56606"    
    """

    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)


    
    # 2023-06-30 同一产品是否区分订单类型(现金/费用)  
    group_by_ordertype=MARKING_GROUPBY_ORDERTYPE        
    

    
    if r['batch_id']:        
        bid=str(r['batch_id'])  
        
        # 2022-11-01 为尽快给喷码机返回结果，改为直接写入发货明细
        #print(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
        
        #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),"+\
        #    "outbound,marking FROM thirdparty_inkjetbatch where id="+bid
        
        # 2023-08-04
        #dn_id=str(r['deliverynote_id']) 
        dn_id="(select deliverynote_id from thirdparty_inkjetbatch where id="+bid+")"
        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 agent_id from inventory_deliverynote where id="+dn_id+"),"+\
            "outbound,marking FROM thirdparty_inkjetbatch where id="+bid


       # agent_id, 
       #company_id, user_id, librarian, salesman, salesmanager, address, 
       #receiver, telephone, consignee, marking, edit_logs, agent_code


        cur.execute(s)
        rr=cur.fetchone()       
        if not rr:
            data['e']='batch id '+bid+' not exists'
            cur.close()
            return JsonResponse(data)        
        
        # 没有写入过销售出库
        #if not rr[8]:
        if True:

            aid=c.ancestor_id
            cid=c.id
            tocid=rr[7]
            pid=rr[1]
            uid=rr[2]
            num=rr[4]
            if rr[6]:
                num=rr[6]   

            # 2023-06-30 喷码信息            
            #第一类52度500ml2021版 非费用非特渠部门客户订单喷码信息加-赋码后缀(同时这类订单要推送到海商
            #第二类所有费用订单喷码信息加-FY 后缀
            #第三类非一二类订单正常喷码不加后缀
            post_to_haishang=False
            mk=None
            if rr[9]:
                mk=rr[9]
                if mk[-2:]=='赋码':
                    post_to_haishang=True

            # batch
            b=StkBatch(
                company_id=cid,
                company_fr_id = cid,
                level_id=c.level_id,
                hierarchy_id=c.hierarchy_id,
                version=c.version,
                ancestor_id=aid,
                batch_no=num,
                code=num,    
                salesorder_code=num,

                user_id = uid,
                reference_id=int(bid),
                vtype_id=2,
                product_id=rr[1],                
                store_fr_id=rr[3],
                dn_number=rr[4],
                salesorder_type_code=rr[5],                
                company_to_id=tocid,
                
                # 2023-06-30 为节省字段资源，内参喷码信息写入 rmk_name
                # 2022-05-16 成义烧坊，修改单据时，可添加客户名和地址 rmk_name=models.CharField(_('客户/收货人姓名'),max_length=100,blank=True,null=True)
                rmk_name=mk
            )
            b.save()            
            rid=str(bid) # inkjetbatch id
            aid=str(aid)
            cid=str(cid)
            bid=str(b.id)
            pid=str(pid)
            tocid=str(tocid)
            # batchdetails
            s="insert into inventory_batchdetails(batch_id,company_id,ancestor_id,product_id,label_code,label_id,label_pkg,label_pk_id) " +\
                "select "+bid+","+cid+","+aid+",product_id,label_code,label_id,label_pkg,label_pk_id "+\
                "from thirdparty_inkjetdetails where batch_id="+rid+" and is_ok=true"
            cur.execute(s)
            # stkdetails
            tb_stk="stkdetails.c"+aid
            s="INSERT INTO "+tb_stk+"(batch_id,company_id,ancestor_id,product_id,company_to_id,store_fr_id,store_id," + \
                "vtype_id,label_code,label_id,label_pkg,label_pk_id) select " + \
                bid+","+cid+","+aid+","+pid+","+tocid+",store_id,store_id,"+\
                "2, label_code,label_id,label_pkg,label_pk_id "+\
                "from thirdparty_inkjetdetails where batch_id="+rid+" and is_ok=true"
            cur.execute(s)  
            
           
            # 推送给博智
            if cid=='1644':
                # nc_sm_test 扫码测试
                if uid!=12962:
                    #内参，异步任务服务器发送 博智
                    result=tasks.post_bz.delay({
                        'batch':{'id':b.id,'code':b.code},
                        'actions':['delivery']
                    })            
            
            # 2023-07-04 根据喷码信息判断，是否发送给海商
            if post_to_haishang:
                # 推送给海商
                try:
                    result_hs=tasks.post_hs.delay({
                        'batch':{'id':b.id,'code':b.code},
                        'actions':['delivery']
                    })         
                except Exception:                    
                    pass
            

            # 修改状态
            s="update inventory_batch set dt_handled=now(),tm_handled=now() where id="+bid
            cur.execute(s)
            s="update thirdparty_inkjetbatch set outbound=true where id="+bid
            cur.execute(s)

            
            # 2022-11-03 经销商是否自动入库
            auto_inbound=False
            cfgs=CompanyCfg.objects.filter(company_id=int(aid)) 
            if cfgs:
                cfg=cfgs[0]
                if c.level_id==1 and cfg.inbound_auto_lv1:
                    auto_inbound=True  
            if auto_inbound:
                # 经销商入库batch
                bth=StkBatch(
                        #14499; "system"; "系统管理员"
                        user_id = 14499, 
                        vtype_id=1,
                        reference_id=int(bid),

                        company_id=int(tocid),
                        company_fr_id = int(cid),
                        hierarchy_id=c.hierarchy_id,
                        version=c.version,
                        ancestor_id=int(aid),
                        level_id=c.level_id,                    
                        batch_no=num,
                        code=num,    
                        product_id=int(pid),   
                )
                bth.save()       
                bid=str(bth.id) #经销商入库batch id
                # batchdetails
                s="insert into inventory_batchdetails(batch_id,company_id,ancestor_id,product_id,label_code,label_id,label_pkg,label_pk_id) " +\
                        "select "+bid+","+tocid+","+aid+","+pid+",label_code,label_id,label_pkg,label_pk_id "+\
                        "from thirdparty_inkjetdetails where batch_id="+rid+" and is_ok=true"
                cur.execute(s)
                # stkdetails
                s="INSERT INTO "+tb_stk+"(batch_id,company_id,ancestor_id,product_id,vtype_id,label_code,label_id,label_pkg,label_pk_id) " + \
                        "select "+bid+","+tocid+","+aid+","+pid+",1,label_code,label_id,label_pkg,label_pk_id "+\
                        "from thirdparty_inkjetdetails where batch_id="+rid+" and is_ok=true"
                cur.execute(s)
            
                #print('错误类型是',e.__class__.__name__)#except:#jia
                #print('错误明细是',e.__class__.__name__,e)   #continue#jia
                #ff=open('F:\\hzy\\log\\err.txt','a+')
                #ff.write(str(e)+'\n')
                #ff.close()
                



    #2022-05-27
    if int(r['order_id'])==0:
        #2023-07-03 这里传过来的order_id,发货单DeliveryNote id
        #结束当前订单 通常用于下班前最后一单,这时操作人员不会去扫新的发货单
        pass
    else:        
        if ON_DELIVERYNOTE:
            deliverynote_id=int(r['order_id'])
            #s="select qty,unit_name,"+\
            #    "case o.unit_name when '瓶' then o.qty "+\
            #        "when '盒' then o.qty*(p.p/p.h) "+\
            #        "when '箱' then o.qty*p.p "+\
            #        "else o.qty end qty0,"+\
            #    "(select salesorder_id from inventory_deliverynote where id="+str(r['order_id'])+") "+\
            #    "from "+\
            #    "(select unit_name,qty,product_id from inventory_dndetails where deliverynote_id="+str(r['order_id'])+" "+\
            #        "and product_id="+str(r['product_id'])+" and ordertype_id="+str(r['ordertype_id'])+") o "+\
            #    "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(c.id)+") p "+\
            #    "on o.product_id=p.id"

            # 2022-12-21 内参分单操作，金蝶可能先分单，后推主订单，会导致分单对应的主订单id为空 salesorder_id
            #s="select qty,unit_name,"+\
            #    "case o.unit_name when '瓶' then o.qty "+\
            #        "when '盒' then o.qty*(p.p/p.h) "+\
            #        "when '箱' then o.qty*p.p "+\
            #        "else o.qty end qty0,"+\
            #    "coalesce((select salesorder_id from inventory_deliverynote where id="+str(r['order_id'])+"),"+\
            #        "(SELECT salesorder_id FROM inventory_deliverynote where company_id="+str(c.id)+" "+\
            #            "and salesorder_code=(select salesorder_code from inventory_deliverynote where code='"+r['dn_number']+"' limit 1) "+\
            #            "and salesorder_id is not null limit 1) "+\
            #    "),marking from "+\
            #    "(select unit_name,qty,product_id,marking from inventory_dndetails where deliverynote_id="+str(r['order_id'])+" "+\
            #        "and product_id="+str(r['product_id'])+" and ordertype_id="+str(r['ordertype_id'])+") o "+\
            #    "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(c.id)+") p "+\
            #    "on o.product_id=p.id"
            
            # 2023-07-17 同一产品可能有不同批次(年份)            
            s="select qty,unit_name,"+\
                "case o.unit_name when '瓶' then o.qty "+\
                    "when '盒' then o.qty*(p.p/p.h) "+\
                    "when '箱' then o.qty*p.p "+\
                    "else o.qty end qty0,"+\
                "coalesce((select salesorder_id from inventory_deliverynote where id="+str(r['order_id'])+"),"+\
                    "(SELECT salesorder_id FROM inventory_deliverynote where company_id="+str(c.id)+" "+\
                        "and salesorder_code=(select salesorder_code from inventory_deliverynote where code='"+r['dn_number']+"' limit 1) "+\
                        "and salesorder_id is not null limit 1) "+\
                "),marking from "+\
                "(select unit_name,sum(qty) qty,product_id,marking from inventory_dndetails where deliverynote_id="+str(r['order_id'])+" "+\
                    "and product_id="+str(r['product_id'])+" and ordertype_id="+str(r['ordertype_id'])+" "+\
                    "group by unit_name,product_id,marking"+\
                ") o "+\
                "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(c.id)+") p "+\
                "on o.product_id=p.id"
        else:
            deliverynote_id=None
            s="select qty,unit_name,qty0,salesorder_id 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:   
            # 2023-07-03
            e=''
            salesorder_id=None
            if rr[3]:
                salesorder_id=rr[3]

                # 2023-07-03 如果区分现金费用，要比对喷码机传过来的喷码信息
                # 2023-08-24 内参喷码机端会手工修改喷码内容，不再比较喷码信息是否一致
                #if group_by_ordertype:
                #    if rr[4]!=r['marking']:                       
                #        e='喷码信息不一致，当前喷码信息应为'+rr[4]
            else:
                # 2022-12-21 内参分单操作，金蝶只推了分单，没推主订单
                if ON_DELIVERYNOTE:  
                    #e='请检查"'+r['dn_number']+'"主订单ID是否为空('+\
                    #    '发货单ID"'+str(r['ordier_id'])+'",喷码信息"'+r['marking']+'",产品id"'+str(r['product_id'])+'")'
                    
                    # 2023-08-04 内参存在无销售订单的发货
                    pass
            

            # 2023-07-03 如有错误，提示喷码机切换产品失败 
            if e:
                data['e']=e
                return JsonResponse(data)           

            
            b=InkJetBatch(    
                company_id=c.id,
                salesorder_id=salesorder_id,
                deliverynote_id=deliverynote_id,
                ordertype_id=r['ordertype_id'],
                store_id=r['store_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']='产品、发货单错误,请重新切换产品或结束订单('+\
                    '发货单ID:'+str(r['order_id'])+',喷码信息:'+r['marking']+',产品id:'+str(r['product_id'])+')'
    return JsonResponse(data)






#2022-02-21 喷码机标签上传、检查接口
def marking_check00000(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]


    '''
    # 2022-09-22 如果按版本2传参，检查 deliverynote_id product_id store_id
    deliverynote_id=request.GET.get('deliverynote_id','')
    product_id=request.GET.get('product_id','')
    store_id=request.GET.get('store_id','')
    if deliverynote_id:
        if b.deliverynote_id!=int(deliverynote_id):
            a_e.append('发货单id')
    if product_id: 
        if b.product_id!=int(product_id):
            a_e.append('产品')
    if store_id:
        if b.store_id!=int(store_id):
            a_e.append('仓库')    
    if a_e:
        data['e']='信息不一致('+','.join(a_e)+'),请确认已经正确切换了产品(或点击结束订单)'
        return JsonResponse(data)
    '''


    cur=connection.cursor()
    
    company_id=u.company_id    
    ancestor_id = get_ancestorId(company_id)

    q=q=LabelQuery(int(company_id),label)
    pid=str(b.product.id)  

    # 2023-06-30 产品是否区分订单类型
    group_by_ordertype=False
    #group_by_ordertype=True
    
    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'])

        # 2022-10-28 增加内参酒鬼产品ID转换
        #if company_id==1644:
        if company_id==-1644:
            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),"+\
                "coalesce("+\
                    "(select product_id from stkdetails.c"+ancestor_id+" where company_id=1 and label_pk_id="+pk_id+" and vtype_id=1 and is_delete=false order by id limit 1),"+\
                    "(select product_id from stkdetails.c"+ancestor_id+" where company_id=1 and label_pk_id="+pk_id+" and vtype_id=2 order by id limit 1),"+\
                    "0"+\
                "),"+pid+" "+\
                "from product_product where id="+pid         
            cur.execute(s)
            r=cur.fetchone()
            # 2022-10-28 如果有酒鬼酒产品名称，检查当前产品与发货单是否一致
            if r[4]:
                s="select "+\
                    "(select nc_product_id from z_nc_jg_product where jg_product_id="+str(r[4])+"),"+\
                    "(select name from product_product where id="+str(r[4])+"),"+\
                    "(select name from product_product where id="+pid+")"
                cur.execute(s)
                rr=cur.fetchone()
                if str(rr[0]):
                    if not str(rr[0])==pid:  
                        # pkgs={1:'瓶',2:'盒',3:'箱'}
                        # data['label']={'code':label,'package':pkgs[pkg]}
                        data['e']="当前订单产品 "+rr[2]+" 与标签产品不一致 "+rr[1]
                        cur.close()
                        return JsonResponse(data) 
        else:
            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:
        # 2023-06-30
        if group_by_ordertype:
            # 区分现金费用
            qty_order=b.qty0
        else:
            # 不区分现金费用
            s="select sum(qty0) from erp_salesorderdetails where salesorder_id="+str(b.salesorder_id)+" and product_id="+str(b.product_id)
            cur.execute(s)
            rrrr=cur.fetchone()    
            if rrrr[0]:
                qty_order=rrrr[0]
            else:
                qty_order=0
        
        remaining=qty_order-qty_mark-qq
        if remaining<0:
            e='喷码已达订单数量'
    sid='null'
    if b.salesorder_id:
        sid=str(b.salesorder_id)
    did='null'
    if b.deliverynote_id:
        did=str(b.deliverynote_id)
    if e:
        data['e']=e        
        s="INSERT INTO thirdparty_inkjetdetails("+\
            "company_id,label_code,is_ok,batch_id,ordertype_id,product_id,salesorder_id,deliverynote_id,store_id,err) values ("+\
            str(company_id)+",'"+label+"',false,"+str(b.id)+","+str(b.ordertype.id)+","+pid+","+sid+","+did+","+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,deliverynote_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+","+sid+","+did+","+\
        #        str(b.store.id)+","+pk_id+","+str(qq)+","+str(q.infor['label_id'])+","+str(pkg)+\
        #")"

        # 2022-11-05 喷码机传上来的可能是防伪码，需转为物流码
        wl=label
        if int(company_id) in [1,1644] and label[:1]=='4' and q.infor['wl']:
            wl=q.infor['wl']  
        s="INSERT INTO  thirdparty_inkjetdetails(company_id,label_code,is_ok,batch_id,ordertype_id,product_id,salesorder_id,deliverynote_id,"+\
            "store_id,label_pk_id,qty0,label_id,label_pkg,label_original) values ("+\
                str(company_id)+",'"+wl+"',true,"+str(b.id)+","+str(b.ordertype.id)+","+pid+","+sid+","+did+","+\
                str(b.store.id)+","+pk_id+","+str(qq)+","+str(q.infor['label_id'])+","+str(pkg)+",'"+label+"'"+\
        ")"
        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-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)  
    
    
    '''
    key=9a99e97b86886a8733fe9445aeab26c5&
    user_id=12670&
    batch_id=8241&
    label=%0A210606445938;http://tj1.me/?t=3_210606445938&
    marking=0394YCSM&
    order_id=1&
    deliverynote_id=56438&
    product_id=3738&
    store_id=46    
    '''

    user_id=request.GET.get('user_id','')
    batch_id=request.GET.get('batch_id','')
    label=request.GET.get('label','').strip()  
    marking=request.GET.get('marking','')
    product_id=request.GET.get('product_id','')
    deliverynote_id=request.GET.get('deliverynote_id','')   
    if not user_id or not batch_id or not label or not marking or not product_id or not deliverynote_id:
        data['e']='user id/batch id/label/marking/product_id required/deliverynote_id'
        return JsonResponse(data)
    
    
    #2022-03-21 喷码机传二维码标签：'\nhttp://tj1.me/?t=3_209427678865'    另，内参还有条形码标签
    #if not label.isdigit():         #非纯数字，二维码标签，        
    #    if 'http://tj1.me/?t=' not in label:             
    #        data['e']='非海南天鉴产品标签'
    #        return JsonResponse(data)
    if not label[-12:].isdigit():
        data['e']='标签号码应为长度12的数字'
        return JsonResponse(data)






    label=label[-12:]
    if len(label)!=12:        
        data['e']='标签号码长度应为12'
        return JsonResonse(data)


    


    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]


    # 2023-06-30 同一产品是否区分订单类型(现金/费用)  
    group_by_ordertype=MARKING_GROUPBY_ORDERTYPE
    
    
    
    # 2023-07-01 检查参数是否一致  
    a_e=[]
    if b.product_id!=int(product_id):
        a_e.append('产品')
    if b.deliverynote_id!=int(deliverynote_id):
        a_e.append('发货单')
    
    # 2023-08-28 不检查喷码信息
    #if b.marking!=marking:
    #        if group_by_ordertype:
    #        # 区分订单类型
    #        a_e.append('喷码信息')
    if a_e:
        data['e']=','.join(a_e)+'不一致,请确认已切换了产品或点击结束订单. 当前准备接收:'+\
            '发货单号 '+b.dn_number+',产品 '+b.product.name+',喷码信息 '+b.marking   
        return JsonResponse(data)     


    cur=connection.cursor()    
    company_id=u.company_id    
    
    #ancestor_id = get_ancestorId(company_id)
    # 2023-07-01 目前用户帐号所属公司id就是企业id
    ancestor_id=str(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'])

        # 2022-10-28 增加内参酒鬼产品ID转换
        #if company_id==1644:
        if company_id== -1644:
            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),"+\
                "coalesce("+\
                    "(select product_id from stkdetails.c"+ancestor_id+" where company_id=1 and label_pk_id="+pk_id+" and vtype_id=1 and is_delete=false order by id limit 1),"+\
                    "(select product_id from stkdetails.c"+ancestor_id+" where company_id=1 and label_pk_id="+pk_id+" and vtype_id=2 order by id limit 1),"+\
                    "0"+\
                "),"+pid+" "+\
                "from product_product where id="+pid         
            cur.execute(s)
            r=cur.fetchone()
            # 2022-10-28 如果有酒鬼酒产品名称，检查当前产品与发货单是否一致
            if r[4]:
                s="select "+\
                    "(select nc_product_id from z_nc_jg_product where jg_product_id="+str(r[4])+"),"+\
                    "(select name from product_product where id="+str(r[4])+"),"+\
                    "(select name from product_product where id="+pid+")"
                cur.execute(s)
                rr=cur.fetchone()
                if str(rr[0]):
                    if not str(rr[0])==pid:  
                        # pkgs={1:'瓶',2:'盒',3:'箱'}
                        # data['label']={'code':label,'package':pkgs[pkg]}
                        data['e']="当前订单产品 "+rr[2]+" 与标签产品不一致 "+rr[1]
                        cur.close()
                        return JsonResponse(data) 
        else:
            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:
        # 2023-06-30      
        if group_by_ordertype:
            # 区分现金费用
            qty_order=b.qty0
        else:
            # 不区分现金费用
            s="select sum(qty0) from erp_salesorderdetails where salesorder_id="+str(b.salesorder_id)+" and product_id="+str(b.product_id)
            cur.execute(s)            
            rrrr=cur.fetchone()
            if rrrr[0]:
                qty_order=rrrr[0]
            else:
                # 没有当前订单没有要喷码的产品(大概率原因是上传了错误的喷码batch_id)                
                qty_order=0
                

        if not e:
            remaining=qty_order-qty_mark-qq
            if remaining<0:
                e='喷码已达订单数量'
    sid='null'
    if b.salesorder_id:
        sid=str(b.salesorder_id)
    did='null'
    if b.deliverynote_id:
        did=str(b.deliverynote_id)
    if e:
        data['e']=e        
        s="INSERT INTO thirdparty_inkjetdetails("+\
            "company_id,label_code,is_ok,batch_id,ordertype_id,product_id,salesorder_id,deliverynote_id,store_id,err) values ("+\
            str(company_id)+",'"+label+"',false,"+str(b.id)+","+str(b.ordertype.id)+","+pid+","+sid+","+did+","+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,deliverynote_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+","+sid+","+did+","+\
        #        str(b.store.id)+","+pk_id+","+str(qq)+","+str(q.infor['label_id'])+","+str(pkg)+\
        #")"

        # 2022-11-05 喷码机传上来的可能是防伪码，需转为物流码
        wl=label
        if int(company_id) in [1,1644] and label[:1]=='4' and q.infor['wl']:
            wl=q.infor['wl']  
        s="INSERT INTO  thirdparty_inkjetdetails(company_id,label_code,is_ok,batch_id,ordertype_id,product_id,salesorder_id,deliverynote_id,"+\
            "store_id,label_pk_id,qty0,label_id,label_pkg,label_original) values ("+\
                str(company_id)+",'"+wl+"',true,"+str(b.id)+","+str(b.ordertype.id)+","+pid+","+sid+","+did+","+\
                str(b.store.id)+","+pk_id+","+str(qq)+","+str(q.infor['label_id'])+","+str(pkg)+",'"+label+"'"+\
        ")"
        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'),coalesce(dn_number,''),coalesce(marking,''),"+\
                "coalesce((select name from product_product where id=product_id),''),"+\
                "(select count(*) from thirdparty_inkjetdetails where batch_id=b.id and is_ok=true),"+\
                "coalesce((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,"binding_time":""}

    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,to_char(tm,'yyyy-mm-dd HH24:mi:ss') "+\
            "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]
            data["binding_time"]=r[5]
        else:
            data['code'] = 404
            data['message'] = '未查询到标签信息'
    else:
        data['code'] = 404
        data['message'] = '参数错误'
    return JsonResponse(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)
    

# 2022-12-20
def post_batch_hs(batch,order,customer,product):
    '''
    内参海商接口，单个batch发送
    '''
    tmp = 'tmp'
    bt_c = 'bottles_c'  #瓶标瓶盖码
    bt_p = 'bottles_p'  #瓶标活动码       
    bx = 'boxes'
    fw = 'fw'
    bts='bottles'
    
    
    cur=connection.cursor()
    b_fw=True
    b_promotion=True        
    id=batch['id']
    barcodes=[]    
    barcodes = []   
    cur.execute('truncate table ' + tmp)
    cur.execute('truncate table ' + bx)
    cur.execute('truncate table ' + fw)
    cur.execute('truncate table ' + bt_c)
    cur.execute('truncate table ' + bt_p)      
    cur.execute('truncate table ' + bts)
    
    tb_stk='stkdetails.c1644'
    
    #标签号码取替换前号码
    s = "insert into "+tmp+"(stk_id,label_code,label_pk_id,product_id,company_to_id,label_pkg) select id,"+\
        "coalesce((select code0 from label.lbl_replace_details where code=label_code order by id desc limit 1),label_code),"+\
        "label_pk_id,product_id,company_to_id,label_pkg " + \
        "from "+tb_stk+" where batch_id=" + str(id)


    #2022-03-21 内参发货有一箱扫两个箱标的情况
    #标签号码取替换前号码
    s = "insert into "+tmp+"(stk_id,label_code,label_pk_id,product_id,company_to_id,label_pkg) "+\
        "select id,label_code,label_pk_id,product_id,company_to_id,label_pkg from "+\
        "(select max(id) id,"+\
            "max(coalesce((select code0 from label.lbl_replace_details where code=label_code order by id desc limit 1),label_code)) label_code,"+\
            "label_pk_id,product_id,company_to_id,3 label_pkg " + \
            "from logistics.inventory_stkdetails where batch_id="+str(id)+" and label_pkg=3 "+\
            "group by label_pk_id,product_id,company_to_id "+\
            "union all "+\
            "select id,"+\
            "coalesce((select code0 from label.lbl_replace_details where code=label_code order by id desc limit 1),label_code),"+\
            "label_pk_id,product_id,company_to_id,label_pkg " + \
            "from "+tb_stk+" where batch_id="+ str(id)+" and label_pkg<>3 "+\
        ") d "

    cur.execute(s)   

    #取号段或跨段表名
    s="update "+tmp+" set tb=coalesce((select 'label.'||tbname from label.lbl_tbcross where segment=left(label_code,4)),'label.t'||left(label_code,4))"
    cur.execute(s)

    #2022-02-15 将瓶标发货写入临时表
    s="insert into "+bts+"(stk_id,label) select stk_id,label_code from "+tmp+" where label_pkg=1"
    cur.execute(s)
        
    cur.execute("select tb from " + tmp + " where label_pkg=3 group by tb")
    rs = cur.fetchall()
    for r in rs:
        # 插入箱标表
        s = "insert into " + bx + "(stk_id,label) select s.stk_id,t.code from " + \
            "(select stk_id,label_pk_id from " + tmp + " where tb='" + r[0] + "' and label_pkg=3) s," + r[0] + " t " + \
            "where t.id=s.label_pk_id or (t.parent_id=s.label_pk_id and t.package_id=3)"
        cur.execute(s)
        # 插入整箱发货的瓶标
        s = "insert into " + bt_c + "(stk_id,label) select s.stk_id,t.code from " + \
            "(select stk_id,label_pk_id from " + tmp + " where tb='" + r[0] + "' and label_pkg=3) s," + r[0] + " t " + \
            "where t.parent_id=s.label_pk_id and t.package_id=1"
        cur.execute(s)
    # 取瓶标瓶盖码        
    s = "update " + bt_c + " b set code=" + \
        "coalesce((select label from label.bottlecap where bottle=b.label order by id desc limit 1)," + \
            "(select label from logistics.z_bottlecap_binding where bottle=b.label order by id desc limit 1))"
    cur.execute(s)

    t = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    m2 = hashlib.md5((settings.INTERNAL_SK + t).encode('utf8'))
    key = m2.hexdigest()
    # 取箱标防伪码      2022-01-21 内参瓶标也要取防伪码
    if b_fw:
        d = []
        cur.execute("select id,label from " + bx+\
            " union select id,label from "+bt_c
        )
        for r in cur.fetchall():
            d.append({'id': r[0], 'label': r[1]})
        if d:
            r = requests.post(settings.INTERNAL_URL + '/fw/', json={'key': key, 'time': t, 'labels': d})           
            d = json.loads(r.text)
            a = []
            for c in d['data']:
                a.append("(" + str(c['id']) + ",'" + c['label'] + "','" + c['code'] + "'," + \
                    "(select stk_id from " + bx + " where id=" + str(c['id']) + ") )")
            cur.execute("insert into " + fw + "(id,label,code,stk_id) values " + ",".join(a))
    # 取瓶标小圆标活动码
    if b_promotion:
        d = []
        cur.execute("select id,label from " + bt_c + " where code is null")
        for r in cur.fetchall():
            d.append({'id': r[0], 'label': r[1]})
        if d:
            data = {'key': key, 'time': t, 'labels': d}
            r = requests.post(settings.INTERNAL_URL + '/promotion/', json={'key': key, 'time': t, 'labels': d})
            if r.status_code == 200:
                d = json.loads(r.text)
                a = []
                for c in d['data']:
                    a.append("(" + str(c['id']) + ",'" + c['label'] + "','" + c['code'] + "'," + \
                            "(select stk_id from " + bt_c + " where id=" + str(c['id']) + ") )")
                if a:
                    cur.execute("insert into " + bt_p + "(id,label,code,stk_id) values " + ",".join(a))
    s = "select x.cartons,p.bottles from " + \
        "(select stk_id,json_agg(json_build_object(" + \
                "'logistics_code',label," + \
                "'security_code',coalesce((select code from " + fw + " where label=bx.label),'')" + \
            ")) cartons from " + bx + " bx group by stk_id" + \
        ") x " + \
        "left outer join " + \
        "(select stk_id,json_agg(json_build_object(" + \
                "'logistics_code',label," + \
                "'bottlecap',coalesce(code,(select code from " + bt_p + " where id=btc.id),'')," + \
                "'security_code',coalesce((select code from " + fw + " where label=btc.label),'')" + \
            ")) bottles from " + bt_c + " btc group by stk_id" + \
        ") p " + \
        "on x.stk_id=p.stk_id"            
    cur.execute(s)
    for r in cur.fetchall():
        barcodes.append({
            "bottles": r[1],
            "boxes": [],
            "cartons": r[0]
        })  
    #2022-02-15 处理瓶标发货
    cur.execute("select count(*) from "+bts)
    r0=cur.fetchone()
    if r0[0]>0:
        #瓶盖码
        s = "update " + bts + " b set cap=" + \
            "coalesce((select label from label.bottlecap where bottle=b.label order by id desc limit 1)," + \
                "(select label from logistics.z_bottlecap_binding where bottle=b.label order by id desc limit 1))"
        cur.execute(s)    
        # 取防伪码      2022-01-21 内参瓶标也要取防伪码
        if b_fw:
            d = []
            cur.execute("select stk_id,label from " + bts)
            for r in cur.fetchall():
                d.append({'id': r[0], 'label': r[1]})
            if d:
                r = requests.post(settings.INTERNAL_URL + '/fw/', json={'key': key, 'time': t, 'labels': d})           
                d = json.loads(r.text)                
                for c in d['data']:
                    cur.execute("update "+bts+" set fw='"+c['code']+"' where stk_id="+str(c['id']))                
        # 取小圆标活动码
        if b_promotion:
            d = []
            cur.execute("select stk_id,label from " + bts + " where cap is null")
            for r in cur.fetchall():
                d.append({'id': r[0], 'label': r[1]})
            if d:
                data = {'key': key, 'time': t, 'labels': d}    
                r = requests.post(settings.INTERNAL_URL + '/promotion/', json={'key': key, 'time': t, 'labels': d})
                if r.status_code == 200:
                    d = json.loads(r.text)                             
                    for c in d['data']:
                        cur.execute("update "+bts+" set cap='"+c['code']+"' where stk_id="+str(c['id']))      
        cur.execute("select label,cap,fw from "+bts)
        abts=[]
        for r in cur.fetchall():
            abts.append({"logistics_code": r[0], "bottlecap": r[1],"security_code": r[2]})
        barcodes.append({
            "bottles":abts,
            "boxes":[],
            "cartons":[]                        
        })

    data={      
        "batch_id": batch['id'],
        "batch_no": batch['code'],
        "time":     batch['time'],
        "customer":{'code':customer['code'],'name':customer['name']},
        "product": {'code':product['code'],'name':product['name']},
        "dn_number":order['dn_number'],
	"orderNo": order['number'],  
        "barcodes":barcodes,
    }      
    cur.close()    
    return data 



# 2022-12-05 内参海商会员系统接口
@csrf_exempt
def nc_haishang(request,what):
    company_id=1644
    sK='nchs@Flt57Lb%kNd0!5C1644'
    data={'e':'','result':{}}
    e = time_key_check(request, sK)
    if e:
        data['e']=e
        return JsonResponse(data)

    tb='stkdetails.c'+str(company_id)
    if request.META.get('HTTP_X_FORWARDED_FOR',''):
        ip =  request.META['HTTP_X_FORWARDED_FOR']
    else:
        ip = request.META['REMOTE_ADDR']
    vendor='haishang'
    
    cur=connection.cursor()
    if what=='product':
        code=request.GET.get('code','')
        data['result']={
            'prodcode':'',	#产品编码	string	否	对应的产品Code
            'prodname':'',      #产品名称	string	否	对应的产品名称
            'batchno':'',	#生产批号	string	否	生产批号
            'producetime':'',	#生产时间date	否	生产时间
            'dealercode':'',	#经销商code	string	否	
            'dealername':'',	#经销商名称	string	否                    
        }
        if not code:
            data['e']='code missing'
        else:
            #写入第三方查询日志
            s="INSERT INTO log.label_query_3rd(company_id,vendor,ip,code) "+\
                "VALUES ("+str(company_id)+",'"+vendor+"','"+ip+"','"+code+"')"
            cur.execute(s)
            code=code.replace('http://tj1.me/?t=','')  

            # 2023-01-10 标签可能带 11,22,33,1_,2_,3_
            if len(code)==14 and code[:2] in ('11','22','33','1_','2_','3_'):
                code=code[2:]


            q=LabelQuery(company_id,code)     
            if not q.infor['label_pk_id']:
                data['e']='标签号码错误'
            else:
                pk=str(q.infor['label_pk_id'])
                # 取酒鬼工厂生产批次
                s="select b.batch_no,to_char(tm,'yyyy-mm-dd'),d.id from "+\
                    "(select id,batch_id from stkdetails.c1 where label_pk_id="+pk+" and vtype_id=1 and is_delete=false) d "+\
                    "left outer join "+\
                    "(select id,batch_no,dt,tm from inventory_batch where vtype_id=1 and company_id=1) b "+\
                    "on d.batch_id=b.id "+\
                    "order by d.id desc"
                cur.execute(s)
                r=cur.fetchone()
                if r:
                    data['result']['batchno']=r[0]
                    data['result']['producetime']=r[1]
                p=q.get_product()
                if p['id']:
                    d=p
                    src='nc'
                    # 标签类型 #type	码类型	 string	否 1箱码 2 盒码 3 瓶码 4 盖内码
                    if code.isdigit():
                        if q.package['id']==1:
                            pkg='3'
                        elif q.package['id']==3:
                            pkg='1'
                        elif q.package['id']==2:
                            pkg='2'
                    else:
                        # 2023-08-21 海商只盖内码才返回pkg 4
                        if q.infor['is_bottlecap']:
                            pkg='4'
                        else:
                            pkg='3'
                    data['result']['type']=pkg
                    # 产品信息 prodcode':'',	#产品编码	string	否	对应的产品Code       'prodname
                    # volume	容量    brand	品牌 默认内参   spec	规格名称
                    for l in p['property']:
                        if l['desc_cn']=='净含量':
                            data['result']['volume']=l['value']
                        elif l['desc_cn']=='包装(每箱瓶数)':
                            data['result']['spec']=l['value']
                    
                    data['result']['prodcode']=p['external_code']
                    data['result']['prodname']=p['name']
                else:
                    '''
                    src='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 stkdetails.c"+ancestor_id+" where company_id="+aid+" "+\
                                    "and label_pk_id="+pk+" and vtype_id=1 and is_delete=false "+\
                                "union all "+\
                                "select product_id,tm from stkdetails.c"+ancestor_id+" 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
                    '''      
                    pass
                if src=='nc':
                    # 取经商信息
                    # 2023-07-28调用LabelQuery新增方法,取最后一次销售出库经销商信息
                    r=q.get_endclient()
                    if r:
                        data['result']['dealercode']=r['external_code']
                        data['result']['dealername']=r['name'] 
    elif what=='label':
        code=request.GET.get('code','')
        if not code :
            data['e']='parameter code missing'
        else:
            #写入第三方查询日志
            s="INSERT INTO log.label_query_3rd(company_id,vendor,ip,code) "+\
                "VALUES ("+str(company_id)+",'"+vendor+"','"+ip+"','"+code+"')"
            cur.execute(s)
            code=code.replace('http://tj1.me/?t=','')

            # 2023-01-10 标签可能带 11,22,33,1_,2_,3_
            if len(code)==14 and code[:2] in ('11','22','33','1_','2_','3_'):
                code=code[2:]

            q=LabelQuery(company_id,code)     
            if not q.infor['label_pk_id']:
                data['e']='标签号码错误'
            else:
                pk=str(q.infor['label_pk_id'])
                p=q.get_product()
                if p['id']:
                    ck=q.infor['label_pk_code']
                    data['result']['prodcode']=p['external_code']
                    data['result']['prodname']=p['name']                   
                    rls=q.get_relations() 
                    # 主箱标
                    cs=[]
                    for c in rls['cartons']:                  
                        cs.append(c['label'])
                    data['result']['CaseCodes']=cs
                    bs=[]
                    for c in rls['children']:   
                        
                        # 2023-01-09 内参要求，箱标返回把有瓶数据  瓶标、盖码只返当前瓶数据
                        if q.package['id']==3:
                            if c['code0']:
                                bs.append("'"+c['code0']+"'")
                            else:
                                bs.append("'"+c['label']+"'")              
                        else:    
                            if c['label']==q.infor['wl'] or c['code0']==q.infor['wl']:
                                if c['code0']:
                                    bs.append("'"+c['code0']+"'")
                                else:
                                    bs.append("'"+c['label']+"'")  

                    s="select a.code,coalesce(g.label,'') from "+\
                        "(select unnest(array["+",".join(bs)+"]) code) a "+\
                        "left outer join "+\
                        "(select bottle,max(label) as label from "+\
                            "(select bottle,label from label.bottlecap where bottle in ("+",".join(bs)+") "+\
                                "union  "+\
                                "select bottle,label from logistics.z_bottlecap_binding where bottle in ("+",".join(bs)+")"+\
                            ") b group by bottle"+\
                        ") g "+\
                        "on a.code=g.bottle"
                    cur.execute(s)
                    bs=[]
                    for r in cur.fetchall():
                        bs.append({'bottle':r[0],'bottlecap':r[1]})
                    data['result']['BottleCodes']=bs    
    elif what=='order':
        data={'e':'','result':[]}  
        # 接收post过来的订单号
        r0=json.loads(request.body)
        if 'numbers' not in r0:
            data['e']='parameter numbers missing'
            return JsonResponse(data)
        codes=r0['numbers']
        if len(codes)>10:
            data['e']='一次最多请求10个订单'
            return JsonResponse(data)

        cid=str(company_id)
        cur=connection.cursor()
        # 写入日志
        url=request.path
        s="INSERT INTO log.thirdparty_post(company_id,vendor,url,data) values "+\
            "("+cid+",'haishang', '"+url+"','"+json.dumps(r0)+"')"
        cur.execute(s)

        # 创建临时表
        tmp = 'tmp'
        bt_c = 'bottles_c'  #瓶标瓶盖码
        bt_p = 'bottles_p'  #瓶标活动码       
        bx = 'boxes'
        fw = 'fw'
        bts='bottles'          
        #发货标签号码临时表
        s="CREATE TEMP TABLE "+tmp+"("+\
            "stk_id integer,"+\
            "label_code character varying(20),"+\
            "tb character varying(20),"+\
            "label_pk_id bigint,"+\
            "label_pkg integer,"+\
            "product_id integer,"+\
            "company_to_id integer,"+\
            "cnt integer,"+\
            "cartons jsonb,"+\
            "boxes jsonb,"+\
            "bottles jsonb "+\
        ")"
        cur.execute(s)
        # 箱标表（取关联箱示和防伪码）
        s = "CREATE TEMP TABLE " + bx + "(" + \
            "stk_id integer," + \
            "id serial," + \
            "label character varying(20)," + \
            "code character varying(20)" + \
            ")"
        cur.execute(s)
        # 瓶标表 取瓶盖码
        s = "CREATE TEMP TABLE " + bt_c + "(" + \
            "stk_id integer," + \
            "id serial," + \
            "label character varying(20)," + \
            "code character varying(20)" + \
            ")"
        cur.execute(s)
        # 瓶标表 小圆标
        s = "CREATE TEMP TABLE " + bt_p + "(" + \
            "stk_id integer," + \
            "id integer," + \
            "label character varying(20)," + \
            "code character varying(20)" + \
            ")"
        cur.execute(s)
        # 防伪码
        s = "CREATE TEMP TABLE " + fw + "(" + \
            "stk_id integer," + \
            "id integer," + \
            "label character varying(20)," + \
            "code character varying(20)" + \
            ")"
        cur.execute(s)        
        #瓶标发货
        s = "CREATE TEMP TABLE " + bts + "(" + \
            "stk_id integer," + \
            "id integer," + \
            "label character varying(20)," + \
            "cap character varying(20)," + \
            "fw character varying(20)" + \
            ")"
        cur.execute(s)        
        
        
        for c in codes:
            s="select d.id,code,to_char(tm,'yyyy-mm-dd hh24:mi:ss'),dn_number,"+\
                "p.external_code,p.name,"+\
                "c.external_code,c.name "+\
                "from "+\
                "(select id,code,dn_number,tm,product_id,company_to_id from inventory_batch "+\
                    "where company_id=1644 and salesorder_code='"+c+"' "+\
                    "and (select count(*) from stkdetails.c"+cid+")>0"+\
                ") d "+\
                "left outer join "+\
                "(select id,name,external_code from company_company where ancestor_id="+cid+") c "+\
                "on d.company_to_id=c.id "+\
                "left outer join "+\
                "(select id,name,external_code from product_product where company_id="+cid+") p "+\
                "on d.product_id=p.id"
            cur.execute(s)
            rs=cur.fetchall()
            for r in rs:
                batch={'id':r[0],'code':r[1],'time':r[2]}
                order={'number':c,'dn_number':r[3]}
                product={'code':r[4],'name':r[5]}
                customer={'code':r[6],'name':r[7]}                
                d=post_batch_hs(batch,order,customer,product)                
                data['result'].append(d)        
        
        # 测试，打印data
        #ff=open('f:\\hzy\\nc_hs_data.json','w')
        #ff.write(json.dumps(data))
        #ff.close()
        
        cur.close()
        return JsonResponse(data)
            
    cur.close()
    return JsonResponse(data)




# 2023-08-05 发货数据推送第三方日志查询列表
@login_required
def delivery_post(request,what='data'):    
    clmns=[
        {"title":"batch_id",    "data":"batch_id"},
        {"title":"单据编号",    "data":"batch_code", "width":"5%"},
        {"title":"类型",    "data":"voucher_type", "width":"2%"},            
        {"title":"产品信息",    "data":"product", "width":"13%"}, 
        {"title":"经销商信息",      "data":"customer", "width":"13%"},
        {"title":"箱",        "data":"cnt_box", "width":"2%"},
        {"title":"瓶",        "data":"cnt_bottle", "width":"2%"},
        {"title":"代码",  "data":"code_3rd", "width":"2%"},
        {"title":"单据时间",    "data":"tm_voucher", "width":"5%"},
        {"title":"首推时间",    "data":"tm_push", "width":"5%"},
        {"title":"成功",    "data":"is_ok", "width":"2%"},
        {"title":"日志",    "data":"log", "width":"2%"},
    ]

    dt = datetime.datetime.now().strftime('%Y-%m-%d')
    dt_fr = request.GET.get('date_fr', dt)
    dt_to = request.GET.get('date_to', dt)
    dt_max = max(dt_fr, dt_to)
    dt_min = min(dt_fr, dt_to)        
        
    dt_fr = dt_min
    dt_to = dt_max    
        
    #dt_fr='2023-08-09'


    if what=='data':
        #ajax取推送数据记录
        cur=connection.cursor()
        

        cid=str(request.user.ancestor_id)
        data={}
        sWhere=" where company_id="+cid+" and (dt_voucher between '"+dt_fr+"' and '"+dt_to+"')"  
        s="select batch_id,code,carton_cnt,bottle_cnt,customer::json,product::json,"+\
            "to_char(tm_voucher,'yyyy-mm-dd HH24:mi'),"+\
            "to_char((select min(tm) from log.sync_inventory_batch where batch_id=p.batch_id),'yyyy-mm-dd HH24:mi'),"+\
            "case vtype_id when 2 then '出库' when 4 then '退库' end,"+\
            "(select code from thirdparty_vendor where id=vendor_id) "+\
            "FROM thirdparty_batchpost p "+sWhere       
        
        cur.execute(s)        
        d=[]
        for r in cur.fetchall():
            c=''
            p=''            
            if r[4]:
                c=r[4]['name']
                if r[4]['external_code']:
                    c=c+"["+r[4]['external_code']+"]"
            if r[5]:
                p=r[5]['name']
                if r[5]['external_code']:
                    p=p+"["+r[5]['external_code']+"]"
            
            d.append({
                'batch_id':r[0],
                'batch_code':r[1],
                'cnt_box':r[2],
                'cnt_bottle':r[3],                
                'customer':c,
                'product':p,   
                'tm_voucher':r[6],
                'tm_push':r[7],                
                'voucher_type':r[8],
                'code_3rd':r[9],                  
                'is_ok':'',
                'log':''                
            })  
        cur.close()
        return JsonResponse({'data':d,'clmns':clmns}, safe=False)
    elif what=='log':

        data=[]
        result=''
        bid=request.GET.get('batch_id','')
        code=request.GET.get('batch_code','')
        s="select to_char(tm,'yyyy-mm-dd HH24:mi:ss'),results FROM log.sync_inventory_batch "+\
            "where batch_id="+bid
        cur=connection.cursor()
        cur.execute(s)
        for r in cur.fetchall():
            d=[]
            if r[1]:
                r1=(r[1][1:-1]).replace("\\",'')                
                result=r1
            else:
                result="{'error':'未知错误'}"
            result=result.replace('{','').replace('}','')
            data.append({'time':r[0], 'result':result})
        cur.close()
        return JsonResponse(data,safe=False)

    elif what=='html':
        return render(request,'thirdparty/batch_posted.html',
            {'clmns':clmns,'dt_fr':dt_fr,'dt_to':dt_to}
        )



from sales.models import LabelScan
        
#2023-09-28
#天鉴NFC标签查询 国庆节后要新生成一个app，以下查询转到这个app内
def tj_nfc(request,action='query'):
    sK="TjNfc$#aiv-ns4Iw^Sb*d&V"  
    data={'e':''}    
    e = time_key_check(request, sK)
    if e:
        data['e'] = e
        return JsonResponse(data)

    if action=='query':    
        code=request.GET.get('code','')
        if not code:
            data['e']='code missing'
            return JsonResponse(data)
        data={
	    "e": "",
            "production_time":"",
            "customer":"",
	    "product_name": "",
            "batch_no":"",
            "query_cnt":"",
            "query_1st":"",
        }        

        company_id=None   
        company_id=16687        #16687;"酒企演示帐号"
        company_name='酒企演示帐号'

        
        # 天鉴NFC标签，可以根据号码段区分出company_id


        if company_id: 
            q=LabelQuery(company_id,code)
            if not q.infor['label_pk_id']:
                data['e']='标签号码错误'
                return JsonResponse(data)           
             
            
            product_id=None
            consumer_id=None
            label_code=code

            product=q.get_product()
            if product:
                product_id=product['id']
                data['product_name']=product['name']        


            stkdev=q.get_stk_dev()
            aa=[]
            
            for d in stkdev:
                if d['vtype_id']==1 and d['company']==company_name:                    
                    #data['product_name']=d['product']
                    data['production_time']=d['tm']   
                    data['batch_no']=d['bill_no']
                elif d['vtype_id']==2:
                    aa.append(d['customer']+' '+d['tm'][:10])
                   
            if aa:
                data['customer']=",".join(aa)
            
            # 查询结果写入
            l=LabelScan(
                product_id = product_id,
                consumer_id=consumer_id, 
                company_id = int(company_id),  
                remark='天鉴NFC',
                
                label_code =label_code, 
                label_pkg = q.package['id'],
                label_id = q.infor['label_id'],
                label_pk_id = q.infor['label_pk_id'],
                pos_permission = False,              

                #province = province,
                #city = city,
                #address = address,                
                #lng_lat = lng_lat
            )
            l.save()     

            # 2023-10-07 首查信息
            cur=connection.cursor()
            s="select count(*),min(tm) from sales_labelscan where label_code='"+code+"'"
            cur.execute(s)
            r=cur.fetchone()
            if r[0]:
                data['query_cnt']="已查询 "+str(r[0])+' 次'
                data['query_1st']=r[1].strftime('%Y-%m-%d %H:%M:%S')
            else:
                data['query_cnt']="首次查询"
            cur.close()     
                    

    return JsonResponse(data)
