from django.shortcuts import render

# Create your views here.
from mes.models import *
from mes.forms import *
from django.contrib.auth.decorators import login_required
import datetime
from django.db import connection
from django.utils.translation import gettext_lazy as _
from django.views.decorators.csrf import csrf_exempt

from django.http import HttpResponse,HttpResponseRedirect,JsonResponse

from utils.views import *
import json

# 2022-09-08
from django.conf import settings
import redis



# 2022-08-31
import hashlib,requests
sK = 'fgfdvgu$#&3t@j'

# 2023-07-24
from inventory.views import decrypt_11,decrypt_11h





vouchers={
    25:{'title':"原材料入库", 'cat':"mat","show_store_to":False, 'invtype_id':2,'invtype_name':'原材料','fname':'materials',
        'vendor_needed':True,'customer_needed':False},
    26:{'title':"原材料出库", 'cat':"mat","show_store_to":False, 'invtype_id':2,'invtype_name':'原材料','fname':'materials',
        'vendor_needed':False,'customer_needed':False},
    27:{'title':"原材料转库", 'cat':"mat", "show_store_to":True, 'invtype_id':2,'invtype_name':'原材料','fname':'materials',
        'vendor_needed':False,'customer_needed':False},
    28:{'title':"原材料报废", 'cat':"mat","show_store_to":False, 'invtype_id':2,'invtype_name':'原材料','fname':'materials',
        'vendor_needed':False,'customer_needed':False},

    29:{'title':"包装材料入库",'cat':"pkg","show_store_to":False,'invtype_id':3,'invtype_name':'包装材料','fname':'packaging',
        'vendor_needed':True,'customer_needed':False},
    30:{'title':"包装材料出库",'cat':"pkg","show_store_to":False,'invtype_id':3,'invtype_name':'包装材料','fname':'packaging',
        'vendor_needed':False,'customer_needed':False},
    31:{'title':"包装材料转库",'cat':"pkg","show_store_to":True, 'invtype_id':3,'invtype_name':'包装材料','fname':'packaging',
        'vendor_needed':False,'customer_needed':False},
    32:{'title':"包装材料报废",'cat':"pkg","show_store_to":False,'invtype_id':3,'invtype_name':'包装材料','fname':'packaging',
        'vendor_needed':False,'customer_needed':False},

    33:{'title':"半成品入库", 'cat':"sem", "show_store_to":False,'invtype_id':4,'invtype_name':'半成品','fname':'semifinishedgoods',
        'vendor_needed':False,'customer_needed':False},
    34:{'title':"半成品出库", 'cat':"sem", "show_store_to":False,'invtype_id':4,'invtype_name':'半成品','fname':'semifinishedgoods',
        'vendor_needed':False,'customer_needed':False},
    35:{'title':"半成品转库", 'cat':"sem", "show_store_to":True, 'invtype_id':4,'invtype_name':'半成品','fname':'semifinishedgoods',
        'vendor_needed':False,'customer_needed':False},
    36:{'title':"半成库报废", 'cat':"sem", "show_store_to":False,'invtype_id':4,'invtype_name':'半成品','fname':'semifinishedgoods'},      

    37:{'title':"产成品入库", 'cat':"fg", "show_store_to":False, 'invtype_id':1,'invtype_name':'产成品','fname':'product',
        'vendor_needed':False,'customer_needed':False},
    38:{'title':"产成库出库", 'cat':"fg", "show_store_to":False, 'invtype_id':1,'invtype_name':'产成品','fname':'product',
        'vendor_needed':False,'customer_needed':True},   
}


@login_required
def edit_Storehouse(request,id=None):
    is_saved=False
    if id:
        obj=Storehouse.objects.get(id=int(id))
    else:
        obj=Storehouse(company_id=request.user.company_id)
    if request.method=="GET":
        form=StorehouseForm(instance=obj)
    else:
        form=StorehouseForm(request.POST,instance=obj)
        if form.is_valid():
            form.save()
            is_saved = True
    return render(request,'mes/edit_storehouse.html',{'form':form,'is_saved':is_saved})


@login_required
def list_batch(request,vtype_id):    
    cur=connection.cursor()  
    v=vouchers[int(vtype_id)]
    data={'data':[],'clmns':[],'e':[],'murl':'/mes/batch/'+vtype_id+'/','show_add':False,'voucher':v}
    show_add=False
    can_edit=False    
    u=request.user
    c=u.company
    if  u.has_perm('company.sys_admin'):
        data['add']=True    
    my_id=request.GET.get(v['fname']+'_id','')
    vendor_id=request.GET.get('vendor_id','')
    customer_id=request.GET.get('customer_id','')
    myAnd=''
    vendorAnd=''
    customerAnd=''
    sMy="null"
    sVendor="null"
    sCustomer="null"
    obj={'my':{},'vendor':{},'customer':{}}
    if my_id:        
        myAnd=" and "+my_id+" in (select "+v['fname']+"_id from mes_stkdetails where batch_id=m.id group by "+v['fname']+"_id)"     
        if v['fname']=='product':
            tb="product_product"
        else:
            tb="mes_"+v['fname']
        sMy="(select id||'____'||name from "+tb+" where id="+my_id+")"
    if vendor_id:  
        vendorAnd=" and vendors @> '[{\"id\":"+vendor_id+"}]'::jsonb"
        sVendor="(select id||'____'||name from mes_vendor where id="+vendor_id+")"
    if customer_id:
        customerAnd=" and customer_id="+customer_id
        sCustomer="(select id||'____'||name from mes_customer where id="+customer_id+")"
    cur.execute("select "+sMy+","+sVendor+","+sCustomer)
    r=cur.fetchone()
    if r:
        if r[0]:
            a=r[0].split('____')
            obj['my']={"id":a[0],"name":a[1]}
        if r[1]:
            a=r[1].split('____')
            obj['vendor']={"id":a[0],"name":a[1]}
        if r[2]:
            a=r[2].split('____')
            obj['customer']={"id":a[0],"name":a[1]}
    data['obj']=obj
          
    dt = datetime.datetime.now().strftime('%Y-%m-%d')
    dt_fr = request.GET.get('dt_fr', dt)
    dt_to = request.GET.get('dt_to', dt)   
    data['date_fr']=dt_fr
    data['date_to']=dt_to
    sWhere=" where company_id="+str(c.id)+" and vtype_id="+vtype_id+" and (dt between '"+dt_fr+"' and '"+dt_to+"') "+\
        "and is_delete=false"
    if v['fname']=='product':
        ss="(select name from product_product where id=product_id)"
    else:
        ss="(select name from mes_"+v['fname']+" where id="+v['fname']+"_id)" 

    #SELECT id, code, dt, tm, remark, company_id, store_fr_id, store_to_id, 
    #   user_id, vtype_id, is_delete, order_id, vendors, 
    #	array_to_string(array(select jsonb_array_elements(vendors)::json->'name'),',')
    #   FROM logistics.mes_batch
    if v['invtype_id']==2: #原材料
        data['clmns'] = [_('单据号码'),_('制单日期'),_('操作人'),_('仓库'),_('供应商'),_('生产订单编号'),_('物料详情')]  
        s = "select b.id,b.code,to_char(b.dt_voucher,'yyyy-mm-dd'),"+\
            "(select username from company_user where id=b.user_id),"+\
            "coalesce((select name from mes_storehouse where id=b.store_fr_id),''),"+\
            "array_to_string(array(select jsonb_array_elements(b.vendors)::json->'name'),','),"+\
            "coalesce((select code from mes_order where id=b.order_id),''),"+\
            "array_to_string(array(select "+ss+"||' '||qty||"+\
                "(select name from mes_unit where id=unit_id)||"+\
                "case when qc_name is null then ' 末质检' else ' 已质检' end "+\
                "from mes_stkdetails where batch_id=b.id),'<br/>') "+\
            "from "+\
            "(SELECT id,code,tm,store_fr_id,store_to_id,user_id,vendors,dt_voucher,order_id "+\
                "FROM mes_batch m "+sWhere+myAnd+vendorAnd+customerAnd+\
            ") b"    
        cur.execute(s)
        for r in cur.fetchall():
            if r[1]:
                if data['add']:
                    a='<a href="/mes/batch/'+vtype_id+'/'+str(r[0])+'/">'+r[1]+'</a>'
                else:
                    a=r[1]
                data['data'].append([a ,r[2], r[3], r[4], r[5], r[6], r[7] ])
    elif v['invtype_id']==1: #产成品
        data['clmns'] = [_('单据号码'),_('制单日期'),_('操作人'),_('仓库'),_('代工客户'),_('生产订单编号'),_('物料详情')]  
        s = "select b.id,b.code,to_char(b.dt_voucher,'yyyy-mm-dd'),"+\
            "(select username from company_user where id=b.user_id),"+\
            "coalesce((select name from mes_storehouse where id=b.store_fr_id),''),"+\
            "coalesce((select name from company_company where id=b.customer_id),''),"+\
            "coalesce((select code from mes_order where id=b.order_id),''),"+\
            "array_to_string(array(select "+ss+"||' '||qty||"+\
                "(select name from mes_unit where id=unit_id)||"+\
                "case when qc_name is null then ' 末质检' else ' 已质检' end "+\
                "from mes_stkdetails where batch_id=b.id),'<br/>') "+\
            "from "+\
            "(SELECT id,code,tm,store_fr_id,store_to_id,user_id,customer_id,dt_voucher,order_id "+\
                "FROM mes_batch m "+sWhere+myAnd+vendorAnd+customerAnd+\
            ") b"    
        cur.execute(s)
        for r in cur.fetchall():
            if r[1]:
                if data['add']:
                    a='<a href="/mes/batch/'+vtype_id+'/'+str(r[0])+'/">'+r[1]+'</a>'
                else:
                    a=r[1]
                data['data'].append([a ,r[2], r[3], r[4], r[5], r[6], r[7] ])
    else: #包装材料、半成品
        data['clmns'] = [_('单据号码'),_('制单日期'), _('操作人'),_('仓库'),_('物料详情')]  
        s = "select b.id,b.code,to_char(b.dt_voucher,'yyyy-mm-dd'),"+\
            "(select username from company_user where id=b.user_id),"+\
            "coalesce((select name from mes_storehouse where id=b.store_fr_id),''),"+\
            "array_to_string(array(select "+ss+"||' '||qty||"+\
                "(select name from mes_unit where id=unit_id)||"+\
                "case when qc_name is null then ' 末质检' else ' 已质检' end "+\
                "from mes_stkdetails where batch_id=b.id),'<br/>') "+\
            "from "+\
            "(SELECT id,code,tm,store_fr_id,store_to_id,user_id,dt_voucher "+\
                "FROM mes_batch m "+sWhere+myAnd+vendorAnd+customerAnd+\
            ") b"    
        cur.execute(s)
        for r in cur.fetchall():
            if r[1]:
                if data['add']:
                    a='<a href="/mes/batch/'+vtype_id+'/'+str(r[0])+'/">'+r[1]+'</a>'
                else:
                    a=r[1]
                data['data'].append([a ,r[2], r[3], r[4], r[5] ])
    data['dt_fr']=dt_fr
    data['dt_to']=dt_to  
    return render(request,'mes/list_batch.html',data)


@login_required
def edit_batch(request,vtype_id,id=None):    
    url=request.GET.get('previous_url','None').replace('____','&') 
    subtt=[_('出入库列表'),vouchers[int(vtype_id)]['title']]
    is_saved=False
    voucher=vouchers[int(vtype_id)]
    u=request.user
    c=u.company     
    normal=True
    if voucher['vendor_needed'] or voucher['customer_needed']:
        normal=False   
    if id:
        obj=Batch.objects.get(id=int(id))
    else:
        obj=Batch(company_id=c.id,user_id=u.id,dt_voucher=datetime.datetime.now())       
        obj.vtype_id=int(vtype_id)  
    unit_ids=[]
    units=[]

    for u in CompanyUnit.objects.filter(company_id=c.id,invtype_id=vouchers[int(vtype_id)]['invtype_id']):
        unit_ids.append(u.unit.id)
        units.append([u.unit.id,u.unit.name])      
    if request.method=="GET":
        form=BatchForm(instance=obj)
        form.fields['store_fr'].queryset =Storehouse.objects.filter(company_id=c.id)
        form.fields['store_to'].queryset =Storehouse.objects.filter(company_id=c.id)
        formset=StkDetailsFormset(instance=obj)    
        for f in formset:
            f.fields['unit'].queryset=Unit.objects.filter(id__in=unit_ids)  
    else:
        url=request.POST.get('previous_url','None')
        form=BatchForm(request.POST,instance=obj)   
        formset=StkDetailsFormset(request.POST,instance=obj)        
        if form.is_valid():  
            form.save()    
            is_saved=True
            if formset.is_valid():                    
                formset.save()       
                if url!='None':
                    return HttpResponseRedirect(url)
    return render(request,'mes/edit_batch.html',{'form':form,'formset':formset,'is_saved':is_saved,'normal':normal,'previous_url':url,
        'voucher':voucher,'subtt':subtt,'units':units})


@login_required
def list_obj(request,what):       
    dt = datetime.datetime.now().strftime('%Y-%m-%d')
    dt_fr = request.GET.get('dt_fr', dt)
    dt_to = request.GET.get('dt_to', dt)   
    u=request.user
    c=u.company
    data={'e':'','clmns':[],'data':[],'dt_fr':dt_fr,'dt_to':dt_to,'subtt':[],'murl':'/mes/edit/'+what+'/','show_add':False}   
    if  u.has_perm('company.sys_admin'):
        data['show_add']=True
    cur=connection.cursor()
    if what=='order':        
        data['subtt']=[_('订单管理'),_('生产订单')]
        data['clmns']=[_('生成时间'),_('订单编号'),_('客户名称'),_('交货日期'),_('订单详情'),_('当前状态')]
        d=[]        
        s="select id,to_char(tm,'yyyy-mm-dd HH24:MI:SS'),coalesce(code,''),to_char(dt_delivery,'yyyy-mm-dd'),"+\
            "coalesce((select name from company_company where id=agent_id),customer,''),"+\
            "array_to_string(array(select (select name from product_product where id=product_id)||' '||qty||"+\
                "case when unit_id is null then '' else (select name from mes_unit where id=unit_id) end "+\
                "from mes_orderdetails where order_id=b.id),'<br/>'),"+\
            "(select name from mes_orderstatus where id=status_id) "+\
            "FROM mes_order b where company_id="+str(c.id)+" and is_delete is false"
        cur.execute(s)
        for r in cur.fetchall():
            a=r[1]
            if data['show_add']:
                a='<a class="edt" href="/mes/edit/order/'+str(r[0])+'/">'+r[1]+'</a>'
                aa='<a class="evl" order_no="'+r[2]+'" href="/mes/order/evalute/'+str(r[0])+'/">'+r[6]+'</a>'
            d.append([ a,r[2],r[4],r[3],r[5],aa ])
        data['data']=d
        return render(request,'mes/list_order.html',data)
    elif what=='vendor':        
        data['subtt']=[_('供应商与客户'),_('供应商')]
        data['clmns']=[_('供应商名称'),_('详细地址'),_('联系电话'),_('备注说明'),_('操作')]
        d=[]        
        s="SELECT id,name,coalesce(address,''),coalesce(cellphone,''),coalesce(remark,'') "+\
            "FROM mes_vendor where company_id="+str(c.id)+" and is_delete is false"
        cur.execute(s)
        for r in cur.fetchall():
            a=r[1]
            if data['show_add']:
                a='<a class="edt" href="/mes/edit/vendor/'+str(r[0])+'/">'+r[1]+'</a>'    
            btn = '<a class="btn btn-sm btn-default" href="/mes/edit/vendorPhotos/'+str(r[0])+'">资质图片管理</a>'
            d.append([ a,r[2],r[3],r[4],btn ])
        data['data']=d 
    elif what=='customer':        
        data['subtt']=[_('供应商与客户'),_('代工客户')]
        data['clmns']=[_('代工客户名称'),_('详细地址'),_('联系电话'),_('备注说明')]
        d=[]        
        s="SELECT id,name,coalesce(address,''),coalesce(cellphone,''),coalesce(remark,'') "+\
            "FROM mes_customer where company_id="+str(c.id)+" and is_delete is false"
        cur.execute(s)
        for r in cur.fetchall():
            a=r[1]
            if data['show_add']:
                a='<a class="edt" href="/mes/edit/customer/'+str(r[0])+'/">'+r[1]+'</a>'                
            d.append([ a,r[2],r[3],r[4] ])
        data['data']=d
    #2021-09-22 待质检原材料列表
    elif what=='mat_qc':
        data['subtt']=[_('库存管理'),_('待质检原材料列表')]
        data['clmns']=[_('原材料名称'),_('批次'),_('数量'),_('入库日期'),_('入库单号'),_('供应商'),_('操作'),_('备注说明')]
        d=[]    
        s="select b.id,b.code,to_char(b.dt,'yyyy-mm-dd'),coalesce(batch_no,''),coalesce(remark,''),"+\
            "(select name from mes_materials where id=m.materials_id),"+\
            "array_to_string(array(SELECT jsonb_array_elements(vendors)->>'name'),','),"+\
            "m.qty||(select name from mes_unit where id=m.unit_id), "+\
            "m.id from "+\
            "(select batch_id,materials_id,unit_id,qty,batch_no,id FROM mes_stkdetails where company_id="+str(c.id)+\
                " and vtype_id=25 and materials_id>0  and qc_name is null) m "+\
            "left outer join "+\
            "(select id,dt,code,vendors,remark from mes_batch) b "+\
            "on m.batch_id=b.id"
        cur.execute(s)
        for r in cur.fetchall():
            a=r[1]
            if data['show_add']:
                a='<button class="edt btn btn-xs btn-primary" obj_id="'+str(r[8])+'">质检</button>'                
            d.append([ r[5],r[3],r[7],r[2],r[1],r[6],a,r[4] ])
        data['data']=d
        return render(request,'mes/list_mat_qc.html',data)
    elif what=='stock':
        cats=[
            {'id':1, 'name':'原材料',   'fname':'materials_id',         'tb':'mes_materials'},
            {'id':2, 'name':'半成品',   'fname':'semifinishedgoods_id', 'tb':'mes_semifinishedgoods'},
            {'id':3, 'name':'包装材料', 'fname':'packaging_id',         'tb':'mes_packaging'},
            {'id':4, 'name':'产成品',   'fname':'product_id',           'tb':'product_product'},
        ]
        cat_id=int(request.GET.get('cat_id','1'))
        matches = [d for d in cats if d['id'] == cat_id]
        cat=matches[0]
        data['subtt']=[_('库存管理'),_('物料库存列表')]        
        data['clmns']=[ cat['name']+'名称', '数量', '单位', '质检']
        d=[]    
        cid=str(c.id)        
        #目前方法未使用期结
        sUnit="coalesce((select name from mes_unit where measure_id=s.measure_id and is_basis=true),'')"
        if cat_id==4:
            sUnit="'箱'"
        s="select coalesce((select name from "+cat['tb']+" where id="+cat['fname']+"),''),"+\
            "to_char(s.qty,'9999990.999'),"+\
            sUnit+","+\
            "case qc_passed when true then '合格' when false then '不合格' else  '未质检' end "+\
            "from (select "+cat['fname']+",qc_passed,"+\
                "sum(qty*(select direction from inventory_voucher where id=vtype_id)*"+\
                    "(select ratio from mes_unit where id=unit_id) "+\
                ") qty, "+\
                "(select measure_id from mes_unit where id=unit_id) measure_id "+\
                "from mes_stkdetails where company_id="+cid+" and "+cat['fname']+">0 and is_delete=false "+\
                "group by "+cat['fname']+",qc_passed,measure_id"+\
            ") s"            
        cur.execute(s)
        for r in cur.fetchall():                          
            d.append([ r[0],r[1],r[2],r[3] ])
        data['data']=d
        data['cats']=cats
        data['cat_id']=cat_id
        cur.close()
        return render(request,'mes/list_stock.html',data)

    return render(request,'obj/list_obj.html',data)
     


@login_required
def edit_obj(request,what,id=None):
    u=request.user
    c=u.company    
    is_saved=False
    if what=='order':        
        if id:
            subtt=[_('生产订单'), _('修改') ]
            obj=Order.objects.get(id=int(id))
        else:
            subtt=[_('生产订单'), _('新建') ]
            obj=Order(company_id=c.id,user=u)

        if request.method=='GET':
            form=OrderForm(instance=obj)
            formset=OrderDetailsFormset(instance=obj)
        else:
            form=OrderForm(request.POST,instance=obj)
            formset=OrderDetailsFormset(request.POST,instance=obj)
            if form.is_valid():  
                form.save()    
                is_saved=True
                if formset.is_valid():                    
                    formset.save()
        return render(request,'mes/edit_order.html',
                {'form':form,'formset':formset,'is_saved':is_saved,'subtt':subtt})
    elif what=='order_status':    
        o=Order.objects.get(id=int(id))
        status_id=int(request.POST.get('status_id'))     
        o.status_id=status_id
        o.save()       
        return JsonResponse({'e':''})    
    elif what=='vendor':    
        if id:
            subtt=[_('生产订单'), _('修改') ]
            obj=Vendor.objects.get(id=int(id))
        else:
            subtt=[_('生产订单'), _('新建') ]
            obj=Vendor(company_id=c.id)

        if request.method=='GET':
            form=VendorForm(instance=obj)            
        else:
            form=VendorForm(request.POST,instance=obj)            
            if form.is_valid():  
                form.save()    
                return HttpResponseRedirect('/mes/list/vendor/') 
                
        return render(request,'mes/edit_vendor.html',
                {'form':form,'is_saved':is_saved,'subtt':subtt})   
    elif what=='vendorPhotos':
        subtt=[_('供应商管理'), _('资质图片管理') ]
        obj=VendorPhotos(vendor_id=id)
        form=VendorPhotosForm(instance=obj) 
        
        if request.GET.get('del','') == '1':
            pid = request.GET.get('id','')
            if pid:
                obj=VendorPhotos.objects.get(id=pid)
                obj.delete()
        elif request.method == 'POST':
                form=VendorPhotosForm(request.POST,request.FILES,instance=obj) 
                if form.is_valid():  
                    form.save()  
                    is_saved=True
            
        clmns = ['图片','备注','操作']
        data = []
        
        rs = VendorPhotos.objects.filter(vendor_id=id)
        for r in rs:
            url=''
            remark=''
            if r.photo:
                url = f'<img src="{r.photo.url}" width="100" />'
            if r.remark:
                remark = r.remark
            data.append([
                url,
                remark,
                "<a class='btn btn-sm btn-danger' href='mes/edit/vendorPhotos/"+id+"/?del=1&id="+str(r.id)+"'>删除</a>"
            ])
                
        return render(request,'mes/edit_vendorPhotos.html',
                {'form':form,'is_saved':is_saved,'subtt':subtt,'clmns':clmns,'data':data})   
                
    elif what=='customer':    
        if id:
            subtt=[_('生产订单'), _('修改') ]
            obj=Customer.objects.get(id=int(id))
        else:
            subtt=[_('生产订单'), _('新建') ]
            obj=Customer(company_id=c.id)

        if request.method=='GET':
            form=CustomerForm(instance=obj)            
        else:
            form=CustomerForm(request.POST,instance=obj)            
            if form.is_valid():  
                form.save()    
                return HttpResponseRedirect('/mes/list/customer/')
        return render(request,'mes/edit_customer.html',
                {'form':form,'is_saved':is_saved,'subtt':subtt})   


    #2021-09-24
    elif what=='mat_qc':
        if request.method=='GET':            
            obj=StkDetails.objects.get(id=int(id))
            return render(request,'mes/edit_mat_qc.html',{'obj':obj})  
        else:
            id=request.POST.get('id')
            qc_name=request.POST.get('qc_name')
            passed=request.POST.get('passed')
            remark=request.POST.get('remark')
            o=StkDetails.objects.get(id=int(id))
            t=datetime.datetime.now()
            if passed=='1':
                p=True
            else:
                p=False
            o.qc_name=qc_name
            o.qc_passed=p
            o.qc_tm=t
            o.qc_results={'id':int(id),'remark':remark}
            o.save()

            return JsonResponse({'e':''})
            
  

@login_required
def evaluate_order(request,id):
    objs=Order.objects.filter(id=int(id))
    o=None    
    data={
        'e':'', 'data':[],'status':[],
        'clmns':[_('类别'),_('物料名称'),_('计量单位'),_('当前订单用量'),_('现库存量'),_('未完成订单用量'),_('未完成订单已领用'),_('实际可用量')]        
    }
    if objs:     
        cur=connection.cursor()
        o=objs[0]
        oid=str(o.id)
        c=o.company
        cid=str(c.id)
        s="select (select name from product_product where id=o.product_id),b.product_id,b.id from "+\
            "(select product_id from mes_orderdetails where order_id="+oid+" group by product_id) o "+\
            "left outer join "+\
            "(select product_id,name,id from mes_bom where is_delete=false) b "+\
            "on o.product_id=b.product_id"
        e=[]
        bids=[]
        cur.execute(s)
        for r in cur.fetchall():
            if r[2]:
                bids.append(str(r[2]))
            else:
                e.append(r[0])
        if e:
            data['e']=",".join(e)+"没有录入物料清单"
        else:      
            bids="("+",".join(bids)+")"                
            for s in OrderStatus.objects.all():
                data['status'].append([s.id,s.name])  
            #入库材料是否要经质检                 
            if c.cfg_company.mes_inbound_qc_check_needed: 
                data['clmns']=[_('类别'),_('物料名称'),_('计量单位'),_('当前订单用量'),_('现库存量'),_('待质检'),_('未完成订单用量'),_('未完成订单已领用'),_('实际可用量')]                                
                s="select  '原材料',coalesce(m.name,''),to_char(coalesce(b.qty,0),'9999999990.999'),"+\
                    "(select name from mes_unit where measure_id=m.measure_id and is_basis=true limit 1),"+\
                    "to_char(coalesce(s1.qty,0),'999999990.999'),to_char(coalesce(s2.qty,0),'999999990.999'),to_char(coalesce(o1.qty,0),'999999990.999'),"+\
                    "to_char(coalesce(o2.qty,0),'9999990.999'),to_char((coalesce(s1.qty,0)-coalesce(o1.qty,0)+coalesce(o2.qty,0)-coalesce(s2.qty,0)),'99999999.999') from "+\
                    "(select materials_id,sum((select ratio from mes_unit where id=unit_id)*qty*"+\
                        "(select qty from mes_orderdetails where product_id=(select product_id from mes_bom where id=bom_id))) qty "+\
                        "FROM mes_bommaterials where bom_id in "+bids+" group by materials_id) b "+\
                    "left outer join "+\
                    "(select id,name,measure_id from mes_materials) m "+\
                    "on b.materials_id=m.id "+\
                    "left outer join "+\
                    "(select materials_id,"+\
                        "sum(qty*(select direction from inventory_voucher where id=vtype_id)*"+\
                            "(select ratio from mes_unit where id=unit_id)) qty "+\
                        "from mes_stkdetails where company_id="+cid+" and materials_id is not null group by materials_id) s1 "+\
                    "on b.materials_id=s1.materials_id "+\
                    "left outer join "+\
                    "(select materials_id,"+\
                        "sum(qty*(select ratio from mes_unit where id=unit_id)) qty "+\
                        "from mes_stkdetails where company_id="+cid+" and vtype_id=25 "+\
                        "and qc_id is null and materials_id is not null group by materials_id) s2 "+\
                    "on b.materials_id=s2.materials_id "+\
                    "left outer join "+\
                    "(select materials_id,sum((select ratio from mes_unit where id=unit_id)*qty*"+\
                        "(select sum(qty) from mes_orderdetails where order_id<>"+oid+" and product_id=(select product_id from mes_bom where id=bom_id))) qty "+\
                            "FROM mes_bommaterials where bom_id in "+\
                            "(select id from mes_bom where product_id in "+\
                                "(select product_id from mes_orderdetails where order_id="+oid+")) "+\
                        "group by materials_id) o1 "+\
                    "on b.materials_id=o1.materials_id "+\
                    "left outer join "+\
                    "(select materials_id,"+\
                        "sum(qty*(select ratio from mes_unit where id=unit_id)) qty "+\
                        "from mes_stkdetails where company_id="+cid+" and vtype_id=26 and materials_id is not null "+\
                            "and (select order_id from mes_batch where id=batch_id) is not null "+\
                            "and (select status_id from mes_order where id=(select order_id from mes_batch where id=batch_id)) in (1,2,3) "+\
                            "group by materials_id) o2 "+\
                    "on b.materials_id=o2.materials_id "+\
                "union all "+\
                "select  '半成品',coalesce(m.name,''),to_char(coalesce(b.qty,0),'9999999990.999'),"+\
                    "(select name from mes_unit where measure_id=m.measure_id and is_basis=true limit 1),"+\
                    "to_char(coalesce(s1.qty,0),'999999990.999'),to_char(coalesce(s2.qty,0),'999999990.999'),to_char(coalesce(o1.qty,0),'999999990.999'),"+\
                    "to_char(coalesce(o2.qty,0),'9999990.999'),to_char((coalesce(s1.qty,0)-coalesce(o1.qty,0)+coalesce(o2.qty,0)-coalesce(s2.qty,0)),'99999999.999') from "+\
                    "(select semifinishedgoods_id,sum((select ratio from mes_unit where id=unit_id)*qty*"+\
                        "(select qty from mes_orderdetails where product_id=(select product_id from mes_bom where id=bom_id))) qty "+\
                        "FROM mes_bomsemifinishedgoods where bom_id in "+bids+" group by semifinishedgoods_id) b "+\
                    "left outer join "+\
                    "(select id,name,measure_id from mes_semifinishedgoods) m "+\
                    "on b.semifinishedgoods_id=m.id "+\
                    "left outer join "+\
                    "(select semifinishedgoods_id,"+\
                        "sum(qty*(select direction from inventory_voucher where id=vtype_id)*"+\
                            "(select ratio from mes_unit where id=unit_id)) qty "+\
                        "from mes_stkdetails where company_id="+cid+" and semifinishedgoods_id is not null group by semifinishedgoods_id) s1 "+\
                    "on b.semifinishedgoods_id=s1.semifinishedgoods_id "+\
                    "left outer join "+\
                    "(select semifinishedgoods_id,"+\
                        "sum(qty*(select ratio from mes_unit where id=unit_id)) qty "+\
                        "from mes_stkdetails where company_id="+cid+" and vtype_id=33 "+\
                        "and qc_id is null and semifinishedgoods_id is not null group by semifinishedgoods_id) s2 "+\
                    "on b.semifinishedgoods_id=s2.semifinishedgoods_id "+\
                    "left outer join "+\
                    "(select semifinishedgoods_id,sum((select ratio from mes_unit where id=unit_id)*qty*"+\
                        "(select sum(qty) from mes_orderdetails where order_id<>"+oid+" and product_id=(select product_id from mes_bom where id=bom_id))) qty "+\
                            "FROM mes_bomsemifinishedgoods where bom_id in "+\
                            "(select id from mes_bom where product_id in "+\
                                "(select product_id from mes_orderdetails where order_id="+oid+")) "+\
                        "group by semifinishedgoods_id) o1 "+\
                    "on b.semifinishedgoods_id=o1.semifinishedgoods_id "+\
                    "left outer join "+\
                    "(select semifinishedgoods_id,"+\
                        "sum(qty*(select ratio from mes_unit where id=unit_id)) qty "+\
                        "from mes_stkdetails where company_id="+cid+" and vtype_id=34 and semifinishedgoods_id is not null "+\
                            "and (select order_id from mes_batch where id=batch_id) is not null "+\
                            "and (select status_id from mes_order where id=(select order_id from mes_batch where id=batch_id)) in (1,2,3) "+\
                            "group by semifinishedgoods_id) o2 "+\
                    "on b.semifinishedgoods_id=o2.semifinishedgoods_id "+\
                "union all "+\
                "select  '包装材料',coalesce(m.name,''),to_char(coalesce(b.qty,0),'9999999990.999'),"+\
                    "(select name from mes_unit where measure_id=m.measure_id and is_basis=true limit 1),"+\
                    "to_char(coalesce(s1.qty,0),'999999990.999'),to_char(coalesce(s2.qty,0),'999999990.999'),to_char(coalesce(o1.qty,0),'999999990.999'),"+\
                    "to_char(coalesce(o2.qty,0),'9999990.999'),to_char((coalesce(s1.qty,0)-coalesce(o1.qty,0)+coalesce(o2.qty,0)-coalesce(s2.qty,0)),'99999999.999') from "+\
                    "(select packaging_id,sum((select ratio from mes_unit where id=unit_id)*qty*"+\
                        "(select qty from mes_orderdetails where product_id=(select product_id from mes_bom where id=bom_id))) qty "+\
                        "FROM mes_bompackaging where bom_id in "+bids+" group by packaging_id) b "+\
                    "left outer join "+\
                    "(select id,name,measure_id from mes_packaging) m "+\
                    "on b.packaging_id=m.id "+\
                    "left outer join "+\
                    "(select packaging_id,"+\
                        "sum(qty*(select direction from inventory_voucher where id=vtype_id)*"+\
                            "(select ratio from mes_unit where id=unit_id)) qty "+\
                        "from mes_stkdetails where company_id="+cid+" and packaging_id is not null group by packaging_id) s1 "+\
                    "on b.packaging_id=s1.packaging_id "+\
                    "left outer join "+\
                    "(select packaging_id,"+\
                        "sum(qty*(select ratio from mes_unit where id=unit_id)) qty "+\
                        "from mes_stkdetails where company_id="+cid+" and vtype_id=29 "+\
                        "and qc_id is null and packaging_id is not null group by packaging_id) s2 "+\
                    "on b.packaging_id=s2.packaging_id "+\
                    "left outer join "+\
                    "(select packaging_id,sum((select ratio from mes_unit where id=unit_id)*qty*"+\
                        "(select sum(qty) from mes_orderdetails where order_id<>"+oid+" and product_id=(select product_id from mes_bom where id=bom_id))) qty "+\
                            "FROM mes_bompackaging where bom_id in "+\
                            "(select id from mes_bom where product_id in "+\
                                "(select product_id from mes_orderdetails where order_id="+oid+")) "+\
                        "group by packaging_id) o1 "+\
                    "on b.packaging_id=o1.packaging_id "+\
                    "left outer join "+\
                    "(select packaging_id,"+\
                        "sum(qty*(select ratio from mes_unit where id=unit_id)) qty "+\
                        "from mes_stkdetails where company_id="+cid+" and vtype_id=30 and packaging_id is not null "+\
                            "and (select order_id from mes_batch where id=batch_id) is not null "+\
                            "and (select status_id from mes_order where id=(select order_id from mes_batch where id=batch_id)) in (1,2,3) "+\
                            "group by packaging_id) o2 "+\
                    "on b.packaging_id=o2.packaging_id "
                cur.execute(s)
                for r in cur.fetchall():
                    data['data'].append([ r[0],r[1],r[3],r[2],r[4],r[5],r[6],r[7],r[8]  ])                 
            else:
                data['clmns']=[_('类别'),_('物料名称'),_('计量单位'),_('当前订单用量'),_('现库存量'),_('未完成订单用量'),_('未完成订单已领用'),_('实际可用量')] 
                s="select  '原材料',coalesce(m.name,''),to_char(coalesce(b.qty,0),'9999999990.999'),"+\
                    "(select name from mes_unit where measure_id=m.measure_id and is_basis=true limit 1),"+\
                    "to_char(coalesce(s.qty,0),'999999990.999'),to_char(coalesce(o1.qty,0),'999999990.999'),to_char(coalesce(o2.qty,0),'9999990.999'),"+\
                    "to_char((coalesce(s.qty,0)-coalesce(o1.qty,0)+coalesce(o2.qty,0)),'99999999.999') from "+\
                    "(select materials_id,sum((select ratio from mes_unit where id=unit_id)*qty*"+\
                        "(select qty from mes_orderdetails where product_id=(select product_id from mes_bom where id=bom_id))) qty "+\
                        "FROM mes_bommaterials where bom_id in "+bids+" group by materials_id) b "+\
                    "left outer join "+\
                    "(select id,name,measure_id from mes_materials) m "+\
                    "on b.materials_id=m.id "+\
                    "left outer join "+\
                    "(select materials_id,"+\
                        "sum(qty*(select direction from inventory_voucher where id=vtype_id)*"+\
                            "(select ratio from mes_unit where id=unit_id)) qty "+\
                        "from mes_stkdetails where company_id="+cid+" and materials_id is not null group by materials_id) s "+\
                    "on b.materials_id=s.materials_id "+\
                    "left outer join "+\
                    "(select materials_id,sum((select ratio from mes_unit where id=unit_id)*qty*"+\
                        "(select sum(qty) from mes_orderdetails where order_id<>"+oid+" and product_id=(select product_id from mes_bom where id=bom_id))) qty "+\
                            "FROM mes_bommaterials where bom_id in "+\
                            "(select id from mes_bom where product_id in "+\
                                "(select product_id from mes_orderdetails where order_id="+oid+")) "+\
                        "group by materials_id) o1 "+\
                    "on b.materials_id=o1.materials_id "+\
                    "left outer join "+\
                    "(select materials_id,"+\
                        "sum(qty*(select ratio from mes_unit where id=unit_id)) qty "+\
                        "from mes_stkdetails where company_id="+cid+" and vtype_id=26 and materials_id is not null "+\
                            "and (select order_id from mes_batch where id=batch_id) is not null "+\
                            "and (select status_id from mes_order where id=(select order_id from mes_batch where id=batch_id)) in (1,2,3) "+\
                            "group by materials_id) o2 "+\
                    "on b.materials_id=o2.materials_id "+\
                "union all "+\
                "select  '半成品',coalesce(m.name,''),to_char(coalesce(b.qty,0),'9999999990.999'),"+\
                    "(select name from mes_unit where measure_id=m.measure_id and is_basis=true limit 1),"+\
                    "to_char(coalesce(s.qty,0),'999999990.999'),to_char(coalesce(o1.qty,0),'999999990.999'),to_char(coalesce(o2.qty,0),'9999990.999'),"+\
                    "to_char((coalesce(s.qty,0)-coalesce(o1.qty,0)+coalesce(o2.qty,0)),'99999999.999') from "+\
                    "(select semifinishedgoods_id,sum((select ratio from mes_unit where id=unit_id)*qty*"+\
                        "(select qty from mes_orderdetails where product_id=(select product_id from mes_bom where id=bom_id))) qty "+\
                        "FROM mes_bomsemifinishedgoods where bom_id in "+bids+" group by semifinishedgoods_id) b "+\
                    "left outer join "+\
                    "(select id,name,measure_id from mes_materials) m "+\
                    "on b.semifinishedgoods_id=m.id "+\
                    "left outer join "+\
                    "(select semifinishedgoods_id,"+\
                        "sum(qty*(select direction from inventory_voucher where id=vtype_id)*"+\
                            "(select ratio from mes_unit where id=unit_id)) qty "+\
                        "from mes_stkdetails where company_id="+cid+" and semifinishedgoods_id is not null group by semifinishedgoods_id) s "+\
                    "on b.semifinishedgoods_id=s.semifinishedgoods_id "+\
                    "left outer join "+\
                    "(select semifinishedgoods_id,sum((select ratio from mes_unit where id=unit_id)*qty*"+\
                        "(select sum(qty) from mes_orderdetails where order_id<>"+oid+" and product_id=(select product_id from mes_bom where id=bom_id))) qty "+\
                            "FROM mes_bomsemifinishedgoods where bom_id in "+\
                            "(select id from mes_bom where product_id in "+\
                                "(select product_id from mes_orderdetails where order_id="+oid+")) "+\
                        "group by semifinishedgoods_id) o1 "+\
                    "on b.semifinishedgoods_id=o1.semifinishedgoods_id "+\
                    "left outer join "+\
                    "(select semifinishedgoods_id,"+\
                        "sum(qty*(select ratio from mes_unit where id=unit_id)) qty "+\
                        "from mes_stkdetails where company_id="+cid+" and vtype_id=34 and semifinishedgoods_id is not null "+\
                            "and (select order_id from mes_batch where id=batch_id) is not null "+\
                            "and (select status_id from mes_order where id=(select order_id from mes_batch where id=batch_id)) in (1,2,3) "+\
                            "group by semifinishedgoods_id) o2 "+\
                    "on b.semifinishedgoods_id=o2.semifinishedgoods_id "+\
                "union all "+\
                "select  '包装材料',coalesce(m.name,''),to_char(coalesce(b.qty,0),'9999999990.999'),"+\
                    "(select name from mes_unit where measure_id=m.measure_id and is_basis=true limit 1),"+\
                    "to_char(coalesce(s.qty,0),'999999990.999'),to_char(coalesce(o1.qty,0),'999999990.999'),to_char(coalesce(o2.qty,0),'9999990.999'),"+\
                    "to_char((coalesce(s.qty,0)-coalesce(o1.qty,0)+coalesce(o2.qty,0)),'99999999.999') from "+\
                    "(select packaging_id,sum((select ratio from mes_unit where id=unit_id)*qty*"+\
                        "(select qty from mes_orderdetails where product_id=(select product_id from mes_bom where id=bom_id))) qty "+\
                        "FROM mes_bompackaging where bom_id in "+bids+" group by packaging_id) b "+\
                    "left outer join "+\
                    "(select id,name,measure_id from mes_packaging) m "+\
                    "on b.packaging_id=m.id "+\
                    "left outer join "+\
                    "(select packaging_id,"+\
                        "sum(qty*(select direction from inventory_voucher where id=vtype_id)*"+\
                            "(select ratio from mes_unit where id=unit_id)) qty "+\
                        "from mes_stkdetails where company_id="+cid+" and packaging_id is not null group by packaging_id) s "+\
                    "on b.packaging_id=s.packaging_id "+\
                    "left outer join "+\
                    "(select packaging_id,sum((select ratio from mes_unit where id=unit_id)*qty*"+\
                        "(select sum(qty) from mes_orderdetails where order_id<>"+oid+" and product_id=(select product_id from mes_bom where id=bom_id))) qty "+\
                            "FROM mes_bompackaging where bom_id in "+\
                            "(select id from mes_bom where product_id in "+\
                                "(select product_id from mes_orderdetails where order_id="+oid+")) "+\
                        "group by packaging_id) o1 "+\
                    "on b.packaging_id=o1.packaging_id "+\
                    "left outer join "+\
                    "(select packaging_id,"+\
                        "sum(qty*(select ratio from mes_unit where id=unit_id)) qty "+\
                        "from mes_stkdetails where company_id="+cid+" and vtype_id=30 and packaging_id is not null "+\
                            "and (select order_id from mes_batch where id=batch_id) is not null "+\
                            "and (select status_id from mes_order where id=(select order_id from mes_batch where id=batch_id)) in (1,2,3) "+\
                            "group by packaging_id) o2 "+\
                    "on b.packaging_id=o2.packaging_id "
                cur.execute(s)
                for r in cur.fetchall():
                    data['data'].append([ r[0],r[1],r[3],r[2],r[4],r[5],r[6],r[7]  ])     
    return render(request,'mes/evaluate_order.html',{'data':data,'order':o})      


@csrf_exempt
def ajax_vendor_detail(request):
    data = {
        'e':'',
        'name':'',
        'address':'',
        'cellphone':'',
        'remark':'',
        'external_code':'',
        'contents':'',
        'images':[]
    }
    vendor_id = request.GET.get("id","")
    if vendor_id:
        cur=connection.cursor()
        cur.execute("""select name,
                    address,
                    cellphone,
                    remark,
                    external_code,
                    contents
                    from mes_vendor where id = """+vendor_id)
        r = cur.fetchone()
        if r:
            data['name'] = r[0]
            data['address'] = r[1]
            data['cellphone'] = r[2]
            data['remark'] = r[3]
            data['external_code'] = r[4]
            data['contents'] = r[5]
            
        cur.execute(f"""
            SELECT  photo, remark, (select name from mes_vendorphotocategory where id = category_id) 
            FROM
                mes_vendorphotos
            WHERE
                vendor_id = {vendor_id}
        """)
        rs = cur.fetchall()
        if rs:
            for rr in rs:
                data['images'].append({
                    'photo': rr[0],
                    'remark': rr[1],
                    'category_name': rr[2]
                })
    else:
        data['e'] = '数据丢失'

    return JsonResponse(data)







def label_tb_check(tb):
    """
    检查号段表是否存在，不存在就创建
    """    
    s="select count(*) from information_schema.tables where table_schema='label' "+\
        "and table_type='BASE TABLE' and table_name='"+tb+"'"
    cur=connection.cursor()
    cur.execute(s)
    r=cur.fetchone()
    if not r[0]:
        s="CREATE TABLE label."+tb+"("+\
                "CONSTRAINT "+tb+"_id_pk PRIMARY KEY(id),"+\
                "CONSTRAINT "+tb+"_code_unique UNIQUE(code),"\
                "CONSTRAINT "+tb+"_code_check CHECK(\"left\"(code::text,4)='"+tb[-4:]+"'::text)"+\
            ") INHERITS (label.labelinfo);"+\
            "ALTER TABLE label."+tb+" OWNER TO acc;"+\
            "CREATE INDEX "+tb+"_category_idx ON label."+tb+" USING btree(category_id);"+\
            "CREATE INDEX "+tb+"_code_idx ON label."+tb+" USING btree(code COLLATE pg_catalog.\"default\");"+\
            "CREATE INDEX "+tb+"_inform_idx ON label."+tb+" USING btree(inform_id);"+\
            "CREATE INDEX "+tb+"_package_idx ON label."+tb+" USING btree(package_id);"+\
            "CREATE INDEX "+tb+"_parent_idx ON label."+tb+" USING btree(parent_id);"
        cur.execute(s)
    cur.close()    
    return 

def carton_tb_check(tb):
    """
    检查号段表是否存在，不存在就创建
    """    
    s="select count(*) from information_schema.tables where table_schema='carton' "+\
        "and table_type='BASE TABLE' and table_name='"+tb+"'"
    cur=connection.cursor()
    cur.execute(s)
    r=cur.fetchone()
    if not r[0]:
        s="CREATE TABLE carton."+tb+"("+\
                "CONSTRAINT "+tb+"_id_pk PRIMARY KEY(id),"+\
                "CONSTRAINT "+tb+"_code_unique UNIQUE(code),"\
                "CONSTRAINT "+tb+"_code_check CHECK(\"left\"(code::text,4)='"+tb[-4:]+"'::text)"+\
            ") INHERITS (carton.cartoninfo);"+\
            "ALTER TABLE carton."+tb+" OWNER TO acc;"+\
            "CREATE INDEX "+tb+"_code_idx    ON carton."+tb+" USING btree(code COLLATE pg_catalog.\"default\");"+\
            "CREATE INDEX "+tb+"_company_idx ON carton."+tb+" USING btree(company_id);"+\
            "CREATE INDEX "+tb+"_dt_idx      ON carton."+tb+" USING btree(dt);"+\
            "CREATE INDEX "+tb+"_inbound_idx ON carton."+tb+" USING btree(inbound);"  
        cur.execute(s)
    cur.close()    
    return 

@csrf_exempt
def label_bind(request,company_id,action,what):
    """
    标签在线绑定接口
    """
    data={'e':'','cnt':0} 
    
    e = time_key_check(request, sK)
    if e:
        data['e'] = e
        return JsonResponse(data) 
    cs=Company.objects.filter(id=int(company_id))    
    if not cs:
        data['e']='company id error'
        return JsonResponse(data)
    c=cs[0]
    cat_id=str(c.category_id)   #企业类型
    type_id='2'                 #后绑定
    cur=connection.cursor()
    c=cs[0]
    if action=='check':
        # 标签检查 PH:瓶盒绑定，返回盒标绑定的瓶数; HX:盒箱绑定，检查箱标绑定盒数; PX:瓶箱绑定，返回箱标绑定的瓶数
        data={'e':'','binded':{},'product':{}}
        e=[]
        c=request.GET.get('label','')
        if not c:
            e.append('label missing')
        if what not in ['PH','HX','PX']:
            e.append('process not defined')
        if e:
            data['e']=','.join(e)
            return JsonResponse(data)
        tb='mes_labelbind'+what.lower()
        if what=='PH':
            # 三级绑定，检查每盒绑定了多少瓶
            idx=2
            sWhere=" where box_code='"+c+"' and is_delete=false"

            # 2023-03-27 扫码枪目前只做了这个瓶盒PH检查,没做盒箱HX检查，扫码枪多传产品ID
            product_id=request.GET.get('product_id','')
            if product_id:                
                if int(product_id) in [2642,1465,1463,1509,1503,1501,1489,1487,1483,1470,1466,1502,1504,1488,1486,1482,1490,1471,1464,3229,3228,3230,3226,3231,3224,2677,1468,1467,1469,1506,1505,1508,1507,3227,2801,3225,1485,1484]:
                    #SELECT id FROM logistics.product_product WHERE company_id = 19267 AND is_delete IS FALSE AND (name LIKE '%泥膜%' OR name LIKE '%灸贴%' OR name LIKE '%艾饼%')
                    # 易臻 泥膜，灸贴，艾饼 虽然是箱盒瓶三级包装，工厂用箱标直接绑盒标，盒内小支上面不贴标签
                    s="select id,name,company_id,coalesce((select value from product_productprop where id=("+\
                            "SELECT prd_property_id FROM product_prdprops where product_id=p.id and categoryprop_id=5 and company_id=p.company_id)"+\
                        "),'') from product_product p where id="+product_id
                    cur.execute(s)
                    r=cur.fetchone()
                    data['product']={'id':r[0],'name':r[1],'specification':''}
                    data['binded']={'start':'','end':'','cnt':0}
                    if r[3]:
                        data['product']['specification']=r[3]
                        data['binded']['cnt']=int(r[3].split('*')[2])
                    cur.close()
                    return JsonResponse(data)

        elif what=='HX':
            # 三级绑定，检查每箱绑定了多少盒
            idx=1
            sWhere=" where carton_code='"+c+"' and is_delete=false"
        elif what=='PX':
            # 二级绑定，检查每箱绑定了多少瓶
            # idx=1
            # sWhere=" where carton_code='"+c+"' and is_delete=false"
            # 2022-08-27 # 瓶箱二级绑定，箱标(可能有两枚，如酒鬼)和瓶标扫码枪一次上传，理论上扫码枪已经做每箱瓶数的检查
            # 需要检查的，应该当前扫的这枚瓶标，有没有绑定过
            #z_rebound_bottles


            # 2022-09-28 没外箱了，只生产1737箱            
            #if int(company_id)==1:
            #    # 酒鬼酒，内参年份酒重新绑定检查瓶标是否在指定表范围内
            #    sn=request.GET.get('sn',"1")
            #    cur.execute("select id FROM z_rebound_bottles where company_id="+str(company_id)+" and code='"+c+"' "+\
            #        " and batch_no="+sn)
            #    r=cur.fetchone()
            #    if not r:
            #        data['e']="瓶标 "+c+" 不在指定批次瓶标范围内"
            #        return JsonResponse(data)




            # 检查瓶标是否被绑定过
            cur.execute("select carton_code,to_char(tm,'YYYY-mm-dd HH24:MI:SS') from mes_labelbindpx where company_id="+str(company_id)+" "+\
                "and bottle_code='"+c+"' and is_delete=false")
            r=cur.fetchone()
            if r:
                data['e']="瓶标 "+c+" 已于"+r[1]+" 绑定到箱 "+r[0]
                return JsonResponse(data)
            return JsonResponse(data)

        s="select b.product_id,p.name,b.cnt,"+\
            "to_char(tm_start,'yyyy-mm-dd hh24:mi:ss'),to_char(tm_end,'yyyy-mm-dd hh24:mi:ss'),"+\
            "coalesce((select value from product_productprop where id=("+\
                "SELECT prd_property_id FROM product_prdprops where product_id=p.id and categoryprop_id=5 and company_id=p.company_id)"+\
            "),'') "+\
            "from "+\
            "(select min(product_id) product_id,min(tm) tm_start,max(tm) tm_end,count(*) cnt from "+tb+sWhere+") b "+\
            "left outer join "+\
            "(select id,name,external_code,company_id from product_product where company_id="+company_id+") p "+\
            "on b.product_id=p.id"   
        cur.execute(s)   
        r=cur.fetchone()
        if r[2]:       
            data['product']={'id':r[0],'name':r[1],'specification':''}
            data['binded']={'start':r[3],'end':r[4],'cnt':r[2]}
            if r[5]:
                data['product']['specification']=r[5]
                a=r[5].split('*')
                if what in ['PH','HX']:
                    if len(a)==3:
                        if int(a[idx])!=r[2]:
                            lbl='盒标'
                            if what=='HX':
                                lbl='箱标'                            
                            data['e']=lbl+c+"数据绑定不正确:"+str(r[2])+"/"+str(a[idx])
                    else:
                        data['e']='"'+r[1]+'"的包装规格不是三级套标关系'
                else:
                    if len(a)==2:
                        if a[idx]!=r[2]:
                            data['e']='箱标'+c+"数据绑定不正确:"+str(r[2])+"/"+str(a[idx])
                    else:
                        data['e']='"'+r[1]+'"的包装规格不是二级套标关系'
            else:
                if r[1]:
                    data['e']='"'+r[1]+'"包装规格未定义' 
                else:
                    data['e']='企业ID与产品ID不匹配'       
        else:
            data['e']='没有标签'+c+'的绑定数据'
    elif action=='upload':
        # 上传绑定关系   
        # 为保险起见，所有请求数据写入绑定日志
        try:
            cid=str(c.id)+"_"+c.name
            s = request.body.decode('utf8')   
            pth="F:\\hzy\\log\\labelbinding\\"
            dt=datetime.datetime.now().strftime('%Y-%m-%d')
            if not os.path.exists(pth+dt):
                os.mkdir(pth+dt)        
            fn=cid+".txt"
            f=open(pth+dt+'\\'+fn,'a',encoding='utf-8')
            f.write(s+'\n-------------------------------------------------------\n')
            f.close()
        except Exception:
            pass
        
        r = json.loads(request.body)
        e=[]    
        if 'user_id' not in r or 'product_id' not in r or 'batch_no' not in r or 'store_id' not in r:
            data['e']='user id/product id/store_id/batch no required'
            return JsonResponse(data)    
        flds_lbl="company_id,type_id,category_id,parent_tb,code,package_id,parent_id,is_pk,is_related,is_active,remark"
        qc='null'       
        prd_line='null'
        prd_shop='null'
        prd_team='null'
        # remark='null'
        if 'shop' in r:
            if r['shop']:
                prd_shop="'"+r['shop']+"'"
        if 'line' in r:
            if r['line']:
                prd_line="'"+r['line']+"'"   
        if 'team' in r:
            if r['team']:
                prd_team="'"+r['team']+"'"
        if 'qc' in r:
            if r['qc']:
                qc="'"+r['qc']+"'"    
        user_id=str(r['user_id'])
        store_id=str(r['store_id'])
        product_id=str(r['product_id'])
        batch_no="'"+r['batch_no']+"'"      
        
        #if 'remark' in r:
        #    remark="'"+r['remark']+"'"
        #else:
        #    remark=batch_no
        
        # 2022-09-17
        remark=''
        if 'remark' in r:
            remark=r['remark']
        else:
            remark=batch_no
        if remark:
            remark=remark.replace("'","")    
            remark="'"+remark[:30]+"'" #今晚label库 remark长度改为128; code长度改为32 
        else:
            remark="null"


        a_bnd=[]
        a_lbl=[]   
        if what=='PH':
            # 瓶盒绑定 （箱盒瓶三级绑定关系)
            if 'box' not in r or 'bottles' not in r:
                e.append("box/bottles required")            
            if e:
                data['e']=','.join(e)
                return JsonResponse(data)         
            tb='mes_labelbindph'
            # 绑定表
            # 为防止重复，把当前盒标相关数据打删除标记
            s="update "+tb+" set is_delete=true where company_id="+company_id+" and box_code='"+r['box']+"'"
            cur.execute(s)    

            # 2022-12-13 如果产品是组合包装，对之前的所有绑定记录打删除标记(拆箱之前的绑定，一般为瓶箱2级绑定）
            cur.execute("select components from product_product where id="+product_id)
            r0=cur.fetchone()
            if r0[0]:
                #cts=[]
                bts=[]
                for b in r['bottles']:
                    bts.append("('"+b['code']+"')")
                #s="create temp table codes(id serial,label_code character varying(32))"
                #cur.execute(s)
                #cur.execute("insert into codes(label_code) values "+",".join(bts))                
                # 拆包装的原数据来源 对拆包前的绑定信息打删除标记

                # 2022-12-17 不能对来源单品整箱打删除，瓶标明细就可以
                s="update mes_labelbindpx set is_delete=true where company_id="+company_id+" and bottle_code in ("+",".join(bts)+")"
                cur.execute(s)

                '''
                s="select carton_code from mes_labelbindpx where company_id="+company_id+" and is_delete=false "+\
                    "and (select id from codes where label_code=bottle_code limit 1) is not null group by carton_code"
                cur.execute(s)
                rs=cur.fetchall()                
                if rs:
                    # 拆包装的标签来源于 瓶箱 绑定
                    for rr in rs:
                        cts.append("'"+rr[0]+"'") 
                    s="update mes_labelbindpx set is_delete=true where company_id="+company_id+" and carton_code in ("+",".join(cts)+")"
                    cur.execute(s)
                else:
                    cts=[]
                    s="select box_code from mes_labelbindph where company_id="+company_id+" and is_delete=false "+\
                        "and (select id from codes where label_code=bottle_code limit 1)>0 group by box_code"
                    cur.execute(s)
                    rs=cur.fetchall()
                    if rs:
                        # 拆包装的标签来源于 瓶盒 绑定
                        for rr in rs:
                            cts.append("'"+rr[0]+"'") 
                        s="update mes_labelbindph set is_delete=true where company_id="+company_id+" and box_code in ("+",".join(cts)+")"
                        cur.execute(s)
                '''

            #flds_lbl="company_id,type_id,category_id,parent_tb,code,package_id,parent_id,is_pk,is_related,is_active,remark"
            sIns="INSERT INTO "+tb+"(company_id,product_id,store_id,user_id,batch_no,"+\
                "prd_shop,prd_line,prd_team,qc,remark,"+\
                "box_code,bottle_code,bottle_cap_label,bottle_cap_code"+\
            ")"            
            for b in r['bottles']:     
                cod='null'
                lbl='null'
                if 'cap' in b:
                    if b['cap']:
                        lbl="'"+b['cap']+"'"
                        #cod 瓶盖要不要解码?

                # 2022-12-12 如果当前产品为组合包装，扫码枪要传每个瓶标的产品ID
                pid=product_id                
                # 瓶标数组已经是由字典组成
                if 'product_id' in b:
                    pid=str(b['product_id']) 
                
                a_bnd.append("("+company_id+","+pid+","+store_id+","+user_id+","+batch_no+","+\
                    prd_shop+","+prd_line+","+prd_team+","+qc+","+remark+","+\
                    "'"+r['box']+"','"+b['code']+"',"+lbl+","+cod+\
                ")")                 
            cur.execute(sIns+" values "+",".join(a_bnd))
            # 标签表    
            # 盒标号段表是否存在，不存在自动创建
            label_tb_check("t"+r['box'][:4])
            # 插入盒标
            #flds_lbl="company_id,type_id,category_id,parent_tb,code,package_id,parent_id,is_pk,is_related,is_active,remark"
            cur.execute("insert into label.t"+r['box'][:4]+"("+flds_lbl+") values ("+\
                company_id+","+type_id+","+cat_id+",null,'"+r['box']+"',2,null,false,false,true,"+remark+") "+\
                "on conflict(code) do update set parent_id=excluded.parent_id,tm=now(),remark=excluded.remark  "+\
                "returning id")
            rr=cur.fetchone()
            h_id=str(rr[0]) 
            h_tb=r['box'][:4]
            # 插入瓶标
            #d_tbs={'t'+r['box'][:4]:["("+company_id+","+type_id+","+cat_id+",null,'"+r['box']+"',2,null,false,false,true,"+remark+")"]} 
            d_tbs={}
            for b in r['bottles']:                
                tb=b['code'][:4]
                # 瓶标号段表是否存在，不存在自动创建
                label_tb_check("t"+tb)
                if tb==h_tb:
                    p_tb='null'
                else:
                    p_tb="'"+h_tb+"'"
                if tb in d_tbs:
                    d_tbs[tb].append("("+company_id+","+type_id+","+cat_id+","+p_tb+","+\
                        "'"+b['code']+"',1,"+h_id+",false,false,true,"+remark+")")
                else:
                    d_tbs[tb]=["("+company_id+","+type_id+","+cat_id+","+p_tb+","+\
                        "'"+b['code']+"',1,"+h_id+",false,false,true,"+remark+")"]
            for t in d_tbs:                
                s="insert into label.t"+t+"("+flds_lbl+") values"+",".join(d_tbs[t])+\
                    "on conflict(code) do update set parent_id=excluded.parent_id,parent_tb=excluded.parent_tb,tm=now(),remark=excluded.remark "
                cur.execute(s)              
        elif what=='HX':
            # 盒箱绑定 （箱盒瓶三级绑定关系）
            tb='mes_labelbindhx'
            if 'cartons' not in r or 'boxes' not in r:
                e.append('cartons/boxes required')
            if e:
                data['e']=','.join(e)
                return JsonResponse(data)     
            # 绑定表
            # 为防止重复，把当前主箱标相关数据打删除标记            
            s="update "+tb+" set is_delete=true where company_id="+company_id+" and carton_code='"+r['cartons'][0]+"'"
            cur.execute(s) 

            # 2022-12-13 如果产品是组合包装，对之前的所有绑定记录打删除标记
            cur.execute("select components from product_product where id="+product_id)
            r0=cur.fetchone()
            if r0[0]:
                #cts=[]
                bts=[]
                for b in r['boxes']:
                    if isinstance(b,dict):
                        bts.append("('"+b['code']+"')")
                    else:
                        bts.append("('"+b+"')")
                #s="create temp table codes(id serial,label_code character varying(32))"
                #cur.execute(s)
                #cur.execute("insert into codes(label_code) values "+",".join(bts))                
                # 拆包装的原数据来源 对拆包前的绑定信息打删除标记
                
                # 2022-12-17 不能对来源单品整箱打删除，盒标明细就可以
                s="update mes_labelbindhx set is_delete=true where company_id="+company_id+" and box_code in ("+",".join(bts)+")"
                cur.execute(s)
                
                '''
                s="select carton_code from mes_labelbindpx where company_id="+company_id+" and is_delete=false "+\
                    "and (select id from codes where label_code=bottle_code limit 1) is not null group by carton_code"
                cur.execute(s)
                rs=cur.fetchall()                
                if rs:
                    # 拆包装的标签来源于 瓶箱 绑定
                    for rr in rs:
                        cts.append("'"+rr[0]+"'") 
                    s="update mes_labelbindpx set is_delete=true where company_id="+company_id+" and carton_code in ("+",".join(cts)+")"
                    cur.execute(s)
                else:
                    cts=[]
                    s="select carton_code from mes_labelbindhx where company_id="+company_id+" and is_delete=false "+\
                        "and (select id from codes where label_code=box_code limit 1)>0 group by carton_code"
                    cur.execute(s)
                    rs=cur.fetchall()
                    if rs:
                        # 拆包装的标签来源于 盒箱 绑定
                        for rr in rs:
                            cts.append("'"+rr[0]+"'") 
                        s="update mes_labelbindhx set is_delete=true where company_id="+company_id+" and carton_code in ("+",".join(cts)+")"
                        cur.execute(s)
                '''

            #flds_lbl="company_id,type_id,category_id,parent_tb,code,package_id,parent_id,is_pk,is_related,is_active,remark"
            sIns="INSERT INTO "+tb+"(company_id,product_id,store_id,user_id,batch_no,"+\
                "prd_shop,prd_line,prd_team,qc,remark,"+\
                "carton_code,carton_related,box_code"+\
            ")"  
            #box_code, carton_code, carton_related, 
            for b in r['boxes']:     
                rel='null'
                if len(r['cartons'])>1:
                    rel="'"+r['cartons'][1]+"'"                
                
                #a_bnd.append("("+company_id+","+product_id+","+store_id+","+user_id+","+batch_no+","+\
                #    prd_shop+","+prd_line+","+prd_team+","+qc+","+remark+","+\
                #    "'"+r['cartons'][0]+"',"+rel+",'"+b+"'"+\
                #")")    

                # 2022-09-29 首先从瓶盒绑定取生产批次，如果没有，取传过来的生产批次
                bth="coalesce((SELECT batch_no FROM mes_labelbindph where company_id="+company_id+" and box_code='"+b+"' and is_delete=false limit 1),"+batch_no+")"        
                
                # 2022-12-12 如果当前产品为组合包装，扫码枪要传每个瓶标的产品ID
                pid=product_id
                if isinstance(b,dict):
                    # 如果传过来的盒标数组由字典组成，标签号码键名为code
                    cd=b['code']
                    if 'product_id' in b:
                        pid=str(b['product_id']) 
                else:
                    # 传过来的盒标为字符串数组
                    cd=b
                
                a_bnd.append("("+company_id+","+pid+","+store_id+","+user_id+","+bth+","+\
                    prd_shop+","+prd_line+","+prd_team+","+qc+","+remark+","+\
                    "'"+r['cartons'][0]+"',"+rel+",'"+cd+"'"+\
                ")") 

            cur.execute(sIns+" values "+",".join(a_bnd))
            # 标签表    
            # 主箱标号段表是否存在，不存在自动创建            
            label_tb_check("t"+r['cartons'][0][:4])
            # 主箱标
            pk_code=r['cartons'][0]
            # 盒标号段
            box_tb=[]
            # 瓶标号段
            bottle_tb=[]

            # 插入主箱标
            #flds_lbl="company_id,type_id,category_id,parent_tb,code,package_id,parent_id,is_pk,is_related,is_active,remark"
            cur.execute("insert into label.t"+r['cartons'][0][:4]+"("+flds_lbl+") values ("+\
                company_id+","+type_id+","+cat_id+",null,'"+pk_code+"',3,null,true,false,true,"+remark+") "+\
                "on conflict(code) do update set parent_id=excluded.parent_id,tm=now(),remark=excluded.remark "+\
                "returning id")
            rr=cur.fetchone()
            x_id=str(rr[0])
            x_tb=r['cartons'][0][:4]
            # 是否有副箱标
            if len(r['cartons'])==2:    #一箱最多有2个箱标
                parent_tb='null'
                if x_tb!=r['cartons'][1][:4]:
                    parent_tb="'"+x_tb+"'"
                label_tb_check("t"+r['cartons'][1][:4])
                # 插入副箱标                
                cur.execute("insert into label.t"+r['cartons'][1][:4]+"("+flds_lbl+") values ("+\
                    company_id+","+type_id+","+cat_id+","+parent_tb+",'"+r['cartons'][1]+"',3,"+x_id+",false,true,true,"+remark+")"+\
                    "on conflict(code) do update set parent_id=excluded.parent_id,parent_tb=excluded.parent_tb,tm=now(),remark=excluded.remark")    
            
            # 插入盒标
            #d_tbs={'t'+r['box'][:4]:["("+company_id+","+type_id+","+cat_id+",null,'"+r['box']+"',2,null,false,false,true,"+remark+")"]} 
            d_tbs={}
            for b in r['boxes']:                
                tb=b[:4]
                # 盒标号段表是否存在，不存在自动创建
                label_tb_check("t"+tb)
                if tb==x_tb:
                    p_tb='null'
                else:
                    p_tb="'"+x_tb+"'"
                if tb in d_tbs:
                    d_tbs[tb].append("("+company_id+","+type_id+","+cat_id+","+p_tb+","+\
                        "'"+b+"',2,"+x_id+",false,false,true,"+remark+")")
                else:
                    d_tbs[tb]=["("+company_id+","+type_id+","+cat_id+","+p_tb+","+\
                        "'"+b+"',2,"+x_id+",false,false,true,"+remark+")"]
                    # 盒标号段数组
                    box_tb.append(b[:4])

            for t in d_tbs:                
                s="insert into label.t"+t+"("+flds_lbl+") values"+",".join(d_tbs[t])+\
                    " on conflict(code) do update set parent_id=excluded.parent_id,parent_tb=excluded.parent_tb,tm=now(),remark=excluded.remark"
                cur.execute(s)

            # 瓶标号段数组
            s="select array_to_string(array(select left(bottle_code,4) from logistics.mes_labelbindph where box_code in "+\
                "('"+"','".join(r['boxes'])+"') and is_delete=false group by left(bottle_code,4)),',')"
            cur.execute(s)
            rr=cur.fetchone()
            # 瓶标号段字符串
            s_box_tb='null'
            s_bottle_tb='null'
            if rr:
                if rr[0]:
                    s_bottle_tb="'"+rr[0]+"'"            
            # 盒标号段字符串
            if box_tb:
                s_box_tb="'"+",".join(box_tb)+"'"            
            # 写入主箱标关联号段表
            carton_tb_check("t"+pk_code[:4])
            s="INSERT INTO carton.t"+pk_code[:4]+"(code,level,box_tb,bottle_tb,company_id,tm,dt,remark) values ('"+\
                pk_code+"',3,"+s_box_tb+","+s_bottle_tb+","+company_id+",now(),now(),"+remark+") "+\
                " on conflict(code) do update set level=excluded.level,box_tb=excluded.box_tb,bottle_tb=excluded.bottle_tb,"+\
                    "company_id=excluded.company_id,remark=excluded.remark,dt=now(),tm=now()"
            cur.execute(s)   
        elif what=='PX':
            # 瓶箱绑定 （箱瓶二级绑定关系）
            tb='mes_labelbindpx'
            if 'cartons' not in r or 'bottles' not in r:
                e.append('cartons/bottles required')
            if e:
                data['e']=','.join(e)
                return JsonResponse(data)
            # 绑定表
            # 为防止重复，把当前主箱标相关数据打删除标记            
            s="update "+tb+" set is_delete=true where company_id="+company_id+" and carton_code='"+r['cartons'][0]+"'"    
            cur.execute(s)        

            # 2022-12-13 如果产品是组合包装，对之前的所有绑定记录打删除标记
            cur.execute("select components from product_product where id="+product_id)
            r0=cur.fetchone()
            if r0[0]:
                #cts=[]
                bts=[]
                for b in r['bottles']:
                    bts.append("('"+b['code']+"')")
                #s="create temp table codes(id serial,label_code character varying(32))"
                #cur.execute(s)
                #cur.execute("insert into codes(label_code) values "+",".join(bts))                
                # 拆包装的原数据来源 对拆包前的绑定信息打删除标记
                
                # 2022-12-17 不能对来源单品整箱打删除，瓶标明细就可以
                s="update mes_labelbindpx set is_delete=true where company_id="+company_id+" and bottle_code in ("+",".join(bts)+")"
                cur.execute(s)
                
                '''
                s="select carton_code from mes_labelbindpx where company_id="+company_id+" and is_delete=false "+\
                    "and (select id from codes where label_code=bottle_code limit 1) is not null group by carton_code"
                cur.execute(s)
                rs=cur.fetchall()                
                if rs:
                    # 拆包装的标签来源于 瓶箱 绑定
                    for rr in rs:
                        cts.append("'"+rr[0]+"'") 
                    s="update mes_labelbindpx set is_delete=true where company_id="+company_id+" and carton_code in ("+",".join(cts)+")"
                    cur.execute(s)
                else:
                    cts=[]
                    s="select carton_code from mes_labelbindhx where company_id="+company_id+" and is_delete=false "+\
                        "and (select id from codes where label_code=box_code limit 1)>0 group by carton_code"
                    cur.execute(s)
                    rs=cur.fetchall()
                    if rs:
                        # 拆包装的标签来源于 盒箱 绑定
                        for rr in rs:
                            cts.append("'"+rr[0]+"'") 
                        s="update mes_labelbindhx set is_delete=true where company_id="+company_id+" and carton_code in ("+",".join(cts)+")"
                        cur.execute(s)
                '''

            #flds_lbl="company_id,type_id,category_id,parent_tb,code,package_id,parent_id,is_pk,is_related,is_active,remark"
            sIns="INSERT INTO "+tb+"(company_id,product_id,store_id,user_id,batch_no,"+\
                "prd_shop,prd_line,prd_team,qc,remark,"+\
                "carton_code,carton_related,bottle_code,bottle_cap_label,bottle_cap_code"+\
            ")"  
            cart_rel='null'
            if len(r['cartons'])==2:
                cart_rel="'"+r['cartons'][1]+"'"
            for b in r['bottles']:     
                cod='null'
                lbl='null'
                if 'cap' in b:
                    if b['cap']:
                        lbl="'"+b['cap']+"'"
                        #cod 瓶盖要不要解码?

                # 2023-07-24 酒海不干，瓶标链接内容为加密码(解密后与箱标跨段)
                bcd=b['code']                
                if bcd[0] in ('h','g'):
                    dbcd=decrypt_11h(bcd)
                    if dbcd:
                        bcd=dbcd  

                # 2022-12-12 如果当前产品为组合包装，扫码枪要传每个瓶标的产品ID                
                pid=product_id                                
                # 瓶标数组已经是由字典组成
                if 'product_id' in b:
                    pid=str(b['product_id'])         
                
                a_bnd.append("("+company_id+","+pid+","+store_id+","+user_id+","+batch_no+","+\
                    prd_shop+","+prd_line+","+prd_team+","+qc+","+remark+","+\
                    "'"+r['cartons'][0]+"',"+cart_rel+",'"+bcd+"',"+lbl+","+cod+\
                ")")    
            cur.execute(sIns+" values "+",".join(a_bnd))

            # 标签表    
            # 主箱标号段表是否存在，不存在自动创建            
            label_tb_check("t"+r['cartons'][0][:4])

            # 主箱标
            pk_code=r['cartons'][0]
            # 盒标号段
            box_tb=[]
            # 瓶标号段
            bottle_tb=[]
            # 插入主箱标
            #flds_lbl="company_id,type_id,category_id,parent_tb,code,package_id,parent_id,is_pk,is_related,is_active,remark"
            cur.execute("insert into label.t"+r['cartons'][0][:4]+"("+flds_lbl+") values ("+\
                company_id+","+type_id+","+cat_id+",null,'"+pk_code+"',3,null,true,false,true,"+remark+") "+\
                "on conflict(code) do update set parent_id=excluded.parent_id,tm=now(),remark=excluded.remark  "+\
                "returning id")
            rr=cur.fetchone()
            x_id=str(rr[0])
            x_tb=r['cartons'][0][:4]

            # 2022-09-29  # 可能会将套标重新绑定，将原有parent_id为主箱标ID的标签设为未绑定(即parent_id=null)
            s="update label.t"+x_tb+" set parent_id=null where parent_id="+x_id
            cur.execute(s)

            # 是否有副箱标
            if len(r['cartons'])==2:    #一箱最多有2个箱标
                parent_tb='null'
                if x_tb!=r['cartons'][1][:4]:
                    parent_tb="'"+x_tb+"'"
                label_tb_check("t"+r['cartons'][1][:4])
                # 插入副箱标                
                cur.execute("insert into label.t"+r['cartons'][1][:4]+"("+flds_lbl+") values ("+\
                    company_id+","+type_id+","+cat_id+","+parent_tb+",'"+r['cartons'][1]+"',3,"+x_id+",false,true,true,"+remark+") "+\
                    "on conflict(code) do update set parent_id=excluded.parent_id,parent_tb=excluded.parent_tb,tm=now(),remark=excluded.remark")  
            # 插入瓶标
            #d_tbs={'t'+r['box'][:4]:["("+company_id+","+type_id+","+cat_id+",null,'"+r['box']+"',2,null,false,false,true,"+remark+")"]} 
            d_tbs={}

            for b in r['bottles']:                
                tb=b['code'][:4]
                # 瓶标号段表是否存在，不存在自动创建
                label_tb_check("t"+tb)
                if tb==x_tb:
                    p_tb='null'
                else:
                    p_tb="'"+x_tb+"'"
                if tb in d_tbs:
                    d_tbs[tb].append("("+company_id+","+type_id+","+cat_id+","+p_tb+","+\
                        "'"+b['code']+"',1,"+x_id+",false,false,true,"+remark+")")
                else:
                    d_tbs[tb]=["("+company_id+","+type_id+","+cat_id+","+p_tb+","+\
                        "'"+b['code']+"',1,"+x_id+",false,false,true,"+remark+")"]
                    # 盒标号段数组
                    bottle_tb.append(b['code'][:4])

            for t in d_tbs:                
                s="insert into label.t"+t+"("+flds_lbl+") values"+",".join(d_tbs[t])+\
                    "on conflict(code) do update set parent_id=excluded.parent_id,parent_tb=excluded.parent_tb,tm=now(),remark=excluded.remark"
                cur.execute(s)              
            # 瓶标号段字符串
            s_bottle_tb='null'
            if bottle_tb:                
                s_bottle_tb="'"+",".join(bottle_tb)+"'"                            
            # 写入主箱标关联号段表
            carton_tb_check("t"+pk_code[:4])
            s="INSERT INTO carton.t"+pk_code[:4]+"(code,level,box_tb,bottle_tb,company_id,tm,dt,remark) values ('"+\
                pk_code+"',2,null,"+s_bottle_tb+","+company_id+",now(),now(),"+remark+") "+\
                "on conflict(code) do update set level=excluded.level,box_tb=excluded.box_tb,bottle_tb=excluded.bottle_tb,"+\
                    "company_id=excluded.company_id,remark=excluded.remark,dt=now(),tm=now()"
            cur.execute(s)   
            
    # 2022-12-12 组合包装产品
    elif action=='component':        
        # 组合包装内实物已按正常生产流程做过在线绑定，现在拆箱重新组装成新的套盒        
        if what=='product':
            # 传标签过来,返回当前标签原产品ID给扫码枪(扫码枪根据返回的原产品ID判断套盒绑定是否正确、完整) 
            data={'e':'','product_id':0} 
            c=request.GET.get('label','')
            pkg=request.GET.get('package_id','1')
            if not c:
                data['e']='label missing'
                return JsonResponse(data)
            if pkg=='1':
                nm='瓶'
                s="select id,product_id FROM mes_labelbindpx   where company_id="+company_id+" and bottle_code='"+c+"' and is_delete=false"+\
                    " union all "+\
                    "select id,product_id FROM mes_labelbindph where company_id="+company_id+" and bottle_code='"+c+"' and is_delete=false"
            elif pkg=='2':
                nm='盒'
                s="select id,product_id FROM mes_labelbindhx   where company_id="+company_id+" and box_code='"+c+"' and is_delete=false"+\
                    " union all "+\
                    "select id,product_id FROM mes_labelbindph where company_id="+company_id+" and box_code='"+c+"' and is_delete=false"
            elif pkg=='3':
                nm='箱'
                s="select id,product_id FROM mes_labelbindhx   where company_id="+company_id+" and carton_code='"+c+"' and is_delete=false"+\
                    " union all "+\
                    "select id,product_id FROM mes_labelbindpx where company_id="+company_id+" and carton_code='"+c+"' and is_delete=false"   
            s="select product_id,id,(select name from product_product where id=product_id) from ("+s+") a order by id desc limit 1" 
            cur.execute(s)
            r=cur.fetchone()
            if r:
                data['product_id']=r[0]
                data['product_name']=r[2]
            else:
                data['e']="当前"+nm+"标没有拆箱前的产品信息" 
        elif what=='check':
            # 组合包装 检查当前标签(盒标)有没有绑定数据
            data={'e':'','binded':[]} 
            c=request.GET.get('label','')
            pkg=request.GET.get('package_id','2')
            if pkg=='2':
                s="select product_id,count(*) from mes_labelbindph where company_id="+company_id+" "+\
                    " and box_code='"+c+"' and is_delete=false group by product_id,box_code"
                cur.execute(s)
                for r in cur.fetchall():
                    data['binded'].append({"product_id":r[0],"cnt":r[1]})
                if not data['binded']:
                    data['e']="标签没有绑定信息"



    # 2023-05-24 西安服务器在线绑定数据批量上传 
    elif action=='massupload':
        # 上传绑定关系   
        # 为保险起见，所有请求数据写入绑定日志
        try:
            cid=str(c.id)+"_"+c.name
            s = request.body.decode('utf8')   
            pth="F:\\hzy\\log\\labelbinding\\"
            dt=datetime.datetime.now().strftime('%Y-%m-%d')
            if not os.path.exists(pth+dt):
                os.mkdir(pth+dt)        
            fn=cid+".txt"
            f=open(pth+dt+'\\'+fn,'a',encoding='utf-8')
            f.write(s+'\n-------------------------------------------------------\n')
            f.close()
        except Exception:
            pass
        
        r = json.loads(request.body)
        e=[]    
        if 'user_id' not in r or 'product_id' not in r or 'batch_no' not in r or 'store_id' not in r or 'details' not in r:
            data['e']='user id/product id/store_id/batch/data no required'
            return JsonResponse(data)    
        # 当前只处理 HX(盒箱),PX(瓶箱) 绑定
        if what not in ('HX','PX'):
            data['e']='当前只处理 HX(盒箱),PX(瓶箱) 绑定数据'
            return JsonResponse(data)
        # data['children']
        if not r['details']:
            data['e']='缺少参数details明细数据'
            return JsonResponse(data)   

        flds_lbl="company_id,type_id,category_id,parent_tb,code,package_id,parent_id,is_pk,is_related,is_active,remark"        
        qc='null'       
        prd_line='null'
        prd_shop='null'
        prd_team='null'
        # remark='null'
        if 'shop' in r:
            if r['shop']:
                prd_shop="'"+r['shop']+"'"
        if 'line' in r:
            if r['line']:
                prd_line="'"+r['line']+"'"   
        if 'team' in r:
            if r['team']:
                prd_team="'"+r['team']+"'"
        if 'qc' in r:
            if r['qc']:
                qc="'"+r['qc']+"'"    
        user_id=str(r['user_id'])
        store_id=str(r['store_id'])
        product_id=str(r['product_id'])
        batch_no="'"+r['batch_no']+"'"              
       
        remark=''
        if 'remark' in r:
            remark=r['remark']
        else:
            remark=batch_no
        if remark:
            remark=remark.replace("'","")    
            remark="'"+remark[:30]+"'" #今晚label库 remark长度改为128; code长度改为32 
        else:
            remark="null"

        # 2023-05-24 创建临时表 如果是跟箱标绑定，1箱最多帖2个箱标
        cur.execute("create temp table codes("+\
            "id serial,"+\
            "label_pk_id bigint,"+\
            "main_tb      character varying(4),"+\
            "related_tb   character varying(4),"+\
            "child_tb     character varying(4),"+\
            "main_code    character varying(20),"+\
            "related_code character varying(20),"+\
            "child_code character varying(20))")        
        a=[]
        for d in r['details']:
            mt="'"+d['main_code'][:4]+"'"
            mc="'"+d['main_code']+"'"
            rc='null'
            rt='null'
            if d['related_code']:
                rt="'"+d['related_code'][:4]+"'"
                rc="'"+d['related_code']+"'"
            for c in d['children']:
                ct="'"+c[:4]+"'"
                cc="'"+c+"'"
                a.append("("+mt+","+rt+","+ct+","+mc+","+rc+","+cc+")")
        cur.execute("insert into codes("+\
            "main_tb,related_tb,child_tb,"+\
            "main_code,related_code,child_code) values "+",".join(a))   
        tb_mes='mes_labelbind'+what.lower()        
        if what=='PX':
            fld_c='bottle_code'     
            lv="2"
            c_pkg="1"           # 子标签类型
            c_tb="bottle_tb"    # carton表 子标签号码字段表
        elif what=='HX':
            fld_c='box_code'
            lv="3"
            c_pkg="2"           # 子标签类型
            c_tb="box_tb"       # carton表 子标签号码字段表
        s="update "+tb_mes+" set is_delete=true where company_id="+company_id+" and "+fld_c+" in (select child_code from codes)"                
        cur.execute(s)
        
        # 插入mes绑定明细
        cur.execute("INSERT INTO "+tb_mes+"("+\
                "company_id,product_id,store_id,user_id,batch_no,"+\
                "prd_shop,prd_line,prd_team,qc,remark,"+\
                "carton_code,carton_related,"+fld_c+\
            ") select "+\
                company_id+","+product_id+","+store_id+","+user_id+","+batch_no+","+\
                prd_shop+","+prd_line+","+prd_team+","+qc+","+remark+","+\
                "main_code,related_code,child_code from codes"
        )
     
        flds_lbl="company_id,type_id,category_id,parent_tb,code,package_id,parent_id,is_pk,is_related,is_active,remark"
        # 主箱标写入基础数据
        cur.execute("select main_tb from codes group by main_tb")
        rs=cur.fetchall()
        for rr in rs:   
            cur.execute("insert into label.t"+rr[0]+"("+flds_lbl+") select "+\
                company_id+","+type_id+","+cat_id+",null,main_code,3,null,true,false,true,"+remark+" "+\
                "from (select main_code from codes where main_tb='"+rr[0]+"' group by main_code) a "+\
                "on conflict(code) do update set parent_id=excluded.parent_id,tm=now(),remark=excluded.remark"
            )
            s="update codes set "+\
                "label_pk_id=(select id from label.t"+rr[0]+" where code=main_code) where main_tb='"+rr[0]+"'"
            cur.execute(s)
        # 是否有副箱标        
        cur.execute("select related_tb from codes group by related_tb having related_tb is not null")
        rs=cur.fetchall()
        for rr in rs:  
            cur.execute("select main_tb from codes where related_tb='"+rr[0]+"' group by main_tb")
            rs0=cur.fetchall()            
            for r0 in rs0:    
                p_tb='null'
                if rr[0]!=r0[0]:
                    p_tb="'"+rr[0]+"'"
                cur.execute("insert into label.t"+rr[0]+"("+flds_lbl+") select "+\
                    company_id+","+type_id+","+cat_id+","+p_tb+",related_code,3,"+\
                    "label_pk_id,false,true,true,"+remark+" "+\
                    "from (select related_code from codes where related_tb='"+rr[0]+"' "+\
                        "and main_tb='"+r0[0]+"' group by related_code) a "+\
                    "on conflict(code) do update set parent_id=excluded.parent_id,tm=now(),remark=excluded.remark "                    
                )
        # 处理子标签
        cur.execute("select child_tb from codes group by child_tb having child_tb is not null")
        rs=cur.fetchall()
        for rr in rs:  
            cur.execute("select main_tb from codes where child_tb='"+rr[0]+"' group by main_tb")
            rs0=cur.fetchall()            
            for r0 in rs0:    
                p_tb='null'
                if rr[0]!=r0[0]:
                    p_tb="'"+rr[0]+"'"
                cur.execute("insert into label.t"+rr[0]+"("+flds_lbl+") select "+\
                    company_id+","+type_id+","+cat_id+","+p_tb+",child_code,"+c_pkg+","+\
                    "label_pk_id,false,false,true,"+remark+" "+\
                    "from (select label_pk_id,child_code from codes where child_tb='"+rr[0]+"' "+\
                        "and main_tb='"+r0[0]+"' group by label_pk_id,child_code"+\
                    ") a "+\
                    "on conflict(code) do update set parent_id=excluded.parent_id,tm=now(),remark=excluded.remark "                    
                )    

        # 写入主箱标关联号段表
        cur.execute("select main_tb from codes group by main_tb")
        rs=cur.fetchall()
        for rr in rs:
            carton_tb_check("t"+rr[0])
            s="INSERT INTO carton.t"+rr[0]+"(code,level,"+c_tb+",company_id,tm,dt,remark) select "+\
                "main_code,"+lv+",tbs,"+\
                company_id+",now(),now(),"+remark+" from "+\
                "(select main_code,array_to_string(array"+\
                    "(select child_tb from codes where main_code=g.main_code group by child_tb),',') tbs "+\
                    "from "+\
                    "(select main_code from codes where main_tb='"+rr[0]+"' group by main_code) g "+\
                ") a "+\
                "on conflict(code) do update set level=excluded.level,box_tb=excluded.box_tb,bottle_tb=excluded.bottle_tb,"+\
                    "company_id=excluded.company_id,remark=excluded.remark,dt=now(),tm=now()"  
            cur.execute(s) 

        tb_stk="stkdetails.c"+company_id        
        # 自动入库     
        uid=14499 # 以system身份做入库
        cid=int(company_id)
        b=Batch(code=batch_no.replace("'",''),product_id=int(product_id),level_id=1, vtype_id=1, company_id=cid, hierarchy_id=cid, ancestor_id=cid, 
            company_fr_id=cid, user_id=uid, voucher_id=1, version=0,store_fr_id=int(store_id), remark=remark          
        )
        b.save()
        # 写入batch_details
        s="insert into inventory_batchdetails(batch_id,label_code,label_id,label_pkg,tm,label_pk_id,company_id,ancestor_id,product_id,label_code0) select " +\
            str(b.id)+",main_code,label_pk_id,3,now(),label_pk_id,"+company_id+","+company_id+","+product_id+",main_code from "+\
            "(select main_code,label_pk_id from codes group by main_code,label_pk_id) g" 
        cur.execute(s)
        # 是否已有入库记录
        s=s="update inventory_batchdetails b set err='此标签已于'||("+\
            "select to_char(tm,'yyyy-mm-dd HH24:MI:SS') from "+tb_stk+" where "+\
            "vtype_id=1 and company_id="+company_id+\
            " and is_delete=false and label_pk_id=b.label_pk_id "+\
            " order by id desc limit 1)||'入库' "+\
            " where batch_id="+str(b.id)+" and err is null "\
            " and (select tm from "+tb_stk+" where "+\
                "vtype_id=1 and company_id="+company_id+\
                " and label_pk_id=b.label_pk_id and is_delete=false limit 1) is not null "       
        cur.execute(s)  
        # 写入stkdetails
        s="INSERT INTO "+tb_stk+"(label_code,batch_id," + \
            "company_id,product_id,store_fr_id,store_id,label_id,label_pkg,vtype_id,label_pk_id,ancestor_id) select " + \
            "label_code,"+str(b.id)+"," +\
            "company_id,product_id,"+store_id+","+store_id+",label_id," + \
            "label_pkg,1,label_pk_id,ancestor_id from inventory_batchdetails a "+\
            "where batch_id="+str(b.id)+" and err is null returning id"
        cur.execute(s)
        data['cnt']=cur.rowcount
        # 更新mes数据绑定入库标识
        s="update "+tb_mes+" set inbound=true "+\
            "where carton_code in (select main_code from codes)"
        cur.execute(s)

    cur.close()
    return JsonResponse(data)


@login_required
def product_bind(request,action):
    """
    标签在线绑定控制
    """
    u=request.user
    cid=str(u.company_id)
    dt = datetime.datetime.now().strftime('%Y-%m-%d')
    dt_fr = request.GET.get('dt_fr', dt)
    dt_to = request.GET.get('dt_to', dt)  
    cur=connection.cursor()
    sWhere=" where company_id="+cid+" and is_delete=false and (dt between '"+dt_fr+"' and '"+dt_to+"') "
    #and inbound=false
    if action=='output':        
        # 查看产量
        data={'data':[],'clmns':['日期','生产时间','产品','包装规格','生产批次号','仓库','产量','待入库','备注'],'dt_fr':dt_fr,'dt_to':dt_to,'qty_uninbound':0}  
        s="select to_char(g.dt,'yyyy-mm-dd'),to_char(tm1,'HH24:MI:SS')||'--'||to_char(tm2,'HH24:MI:ss'),"+\
            "coalesce(prd_line,''),coalesce((select name from product_product where id=product_id),''),"+\
            "coalesce(batch_no,''),coalesce((select name from inventory_storehouse where id=store_id),''),qty,lv,"+\
            "(select count(*) from mes_labelbindhx"+sWhere+" and inbound=false "+\
                    "and product_id=g.product_id and batch_no=g.batch_no and store_id=g.store_id and dt=g.dt)+"+\
                "(select count(*) from  mes_labelbindpx"+sWhere+" and inbound=false "+\
                    "and product_id=g.product_id and batch_no=g.batch_no and store_id=g.store_id and dt=g.dt) qty_inbound, "+\
            "q0,(SELECT value FROM product_productprop where company_id="+cid+" and categoryprop_id=(SELECT id FROM company_categoryprop where name='package' "+\
                "and  category_id=(select category_id from company_company where id="+cid+")) "+\
                "and id in (select prd_property_id from product_prdprops where product_id=g.product_id) limit 1),g.dt "+\
            "from "+\
            "(select min(tm1) tm1,max(tm2) tm2,prd_line,batch_no,store_id,product_id,count(*) qty,'三级绑定' lv,sum(q0) q0,dt from "+\
                "(select min(tm) tm1,max(tm) tm2,prd_line,batch_no,store_id,product_id,carton_code,count(*) q0,dt FROM mes_labelbindhx"+sWhere+\
                    "group by prd_line,batch_no,store_id,product_id,carton_code,dt"+\
                ") h "+\
                "group by prd_line,batch_no,store_id,product_id,dt "+\
            "union all "+\
            "select min(tm1) tm1,max(tm2) tm2,prd_line,batch_no,store_id,product_id,count(*) qty,'二级绑定' lv,sum(q0),dt from "+\
                "(select min(tm) tm1,max(tm) tm2,prd_line,batch_no,store_id,product_id,carton_code,count(*) q0,dt FROM mes_labelbindpx"+sWhere+\
                    "group by prd_line,batch_no,store_id,product_id,carton_code,dt"+\
                ") h "+\
                "group by prd_line,batch_no,store_id,product_id,dt"+\
            ") g"  
        cur.execute(s)
        cnt=0
        for r in cur.fetchall():
            a=r[10].split('*')            
            qty=r[9]
            qty_in=r[8]
            q1=''
            q2=''
            if qty:   
                if (int(qty/int(a[1]))):
                    q1=str(int(qty/int(a[1])))+"箱"
                if (qty % int(a[1])):
                    if q1:
                        q1=q1+" 零 "+str(qty % int(a[1]))+"盒"
                    else:
                        q1=str(qty % int(a[1]))+"盒"
            if qty_in:
                if int(qty_in/int(a[1])):
                    q2=str(int(qty_in/int(a[1])))+"箱"
                if (qty_in % int(a[1])):
                    if q2:
                        q2=q2+" 零 "+str(qty_in % int(a[1]))+"盒"
                    else:
                        q2=str(qty_in % int(a[1]))+"盒"
            data['data'].append([ r[0],r[1],r[3],r[10],r[4],r[5],q1,q2,r[7] ])  
            cnt=cnt+qty_in        
        data['qty_uninbound']=cnt
        
        

        return render(request,'mes/product_bind.html',data)
    elif action=='inbound':
        d=[]
        # 将生产的产品按批次入库
        tbs=['mes_labelbindhx','mes_labelbindpx']
        for tb in tbs:
            s="select product_id,store_id,batch_no,dt,tm,"+\
                "(select name from product_product where id=product_id),"+\
                "(select name from inventory_storehouse where id=store_id) "+\
                "from "+\
                "(select product_id,store_id ,batch_no,dt,max(tm) tm from "+tb+sWhere+" and inbound=false "+\
                    "group by product_id,batch_no,store_id,dt"+\
                ") g"
            cur.execute(s)
            rs=cur.fetchall()            
            for r in rs:                
                codes=[]
                dt=r[3].strftime('%Y-%m-%d')
                tm=r[4].strftime('%Y-%m-%d %H:%M:%S')
                no=r[2] 
                sW=" where company_id="+cid+" and is_delete=false and dt='"+dt+"' and inbound=false "+\
                    "and product_id="+str(r[0])+" and store_id="+str(r[1])+" and batch_no='"+no+"'" 
                cur.execute("select carton_code from "+tb+sW+" group by carton_code")                
                if tb=='mes_labelbindhx':
                    fld='box_code'
                else:
                    fld='bottle_code'                
                s="select carton_code,product_id,cnt,"+\
                    "array(select "+fld+" from "+tb+sW+" and carton_code=g.carton_code),"+\
                    "(SELECT value FROM product_productprop where company_id="+cid+" and categoryprop_id=(SELECT id FROM company_categoryprop where name='package' "+\
                        "and  category_id=(select category_id from company_company where id="+cid+")) "+\
                        "and id in (select prd_property_id from product_prdprops where product_id=g.product_id) limit 1) "+\
                    "from "+\
                    "(select carton_code,product_id,count(*) cnt from "+tb+sW+" group by carton_code,product_id) g"
                cur.execute(s)
                for rr in cur.fetchall():
                    a=rr[4].split('*')                   
                    if rr[2]==int(a[1]):
                        codes.append(rr[0])
                    else:
                        for c in rr[3]:
                            codes.append(c)    
                
                if codes:            
                    t=datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")            
                    m2=hashlib.md5((sK+t).encode('utf8'))    
                    key=m2.hexdigest()  
                    data={        
                        "key": key,                 # key
                        "time": t,                  # 时间戳
                        "time_is_crucial":True,     # 强制以最后绑定时间做入库时间           
                        "user_id":14499,            # 14499;"system"
                        "voucher_id": 1,            # 成品入库                                           
                        "company_id":cid,                          
                        "voucher_time":tm,
                        #"bill_number":r[3].strftime('%Y%m%d')+"_test",
                        "bill_number":no,
                        "batch_no": no,  
                        "codes":codes,
                        "product_id":r[0],                 
                        "store_id":r[1],      
                    }                          
                    address='https://davis.china315net.com'            
                    url=address+'/inventory/fg/update/'                    
                    
                    
                    #发送到销售出库接口             
                    rt=requests.post(url,json=data)                 
                    if rt.status_code==200:
                        s="update "+tb+" set inbound=true "+sW                        
                        cur.execute(s)
                        d.append({
                            'product':r[5],
                            'store':r[6],
                            'batch_no':no,
                            'cnt':len(codes)
                        })  
                    
        return JsonResponse(d,safe=False)



def stocktake_err_log(company_id,code,e):
    cur=connection.cursor()
    s="INSERT INTO logistics.z_stocktake_label_err(company_id, label, msg)"+\
        " values ("+str(company_id)+",'"+code+"','"+e+"')"
    cur.execute(s)
    cur.close()
    return 0
    

# 2022-09-01
def label_check(request,company_id):
    data={'e':'','msg':''}
    e = time_key_check(request, sK)
    if e:
        data['e'] = e
        return JsonResponse(data) 
    c=request.GET.get('code','')
    if not c:
        data['e']='code missing'
        return JsonResponse(data)
    c=c.strip()[-12:]
    cur=connection.cursor()
    if company_id=='19267':
        # 先解决易臻盘点箱盒检查
        # 表是否存在
        s="select count(*) from information_schema.tables where table_schema='label' "+\
            "and table_type='BASE TABLE' and table_name='t"+c[:4]+"'"
        cur.execute(s)
        r=cur.fetchone()
        if not r[0]:
            data['e']='标签号码不存在'
            stocktake_err_log(company_id,c,data['e'])
            return JsonResponse(data)        
        t='label.t'+c[:4]
        s="select count(*) from "+t+" where parent_id="+\
            "(select id from "+t+" where code='"+c+"')"
        cur.execute(s)
        r=cur.fetchone()
        if r[0]<1:
            data['e']='数据绑定不完全 '+str(r[0])
            stocktake_err_log(company_id,c,data['e'])
            return JsonResponse(data)    
    return JsonResponse(data)


















# 2022-09-08
def fg_update_setpro(company_id,batch_id,vtype_id):
    '''
    产成品出入库后处理程序
    '''
    cs={
        19267:'yz',
    }
    ancestor_id = get_ancestorId(company_id)

    rd=redis.StrictRedis(host='localhost', port=settings.RD['port'], db=4, password=settings.RD['password'], decode_responses=True) 
    cur=connection.cursor()
    if int(company_id) in [19267]:        
        s="select product_id,(select name from product_product where id=product_id),"+\
            "label_code,label_pkg,label_id,to_char(tm,'yyyy-mm-dd hh24:mi:ss') from stkdetails.c"+ancestor_id+" where company_id="+str(company_id)+" and  batch_id="+str(batch_id)
        if int(vtype_id)==1:
            # 易臻入库把标签，产品写入redis
            cur.execute(s)
            rs=cur.fetchall()
            for r in rs:
                print(r[2])
                rd.hmset(cs[int(company_id)]+':label_product:'+r[2], {
                    "product_id":r[0],
                    "product_name":r[1],        
                    "label_pkg":r[3],          
                    "batch_id":batch_id,
                    "time":r[5]
                    }
                )
                if r[3]==3 and r[0] not in [2677]:  # 2677  太极温阳灸贴,目前只扫箱标入库，整箱发给客户
                    s="select code,package_id from label.t"+r[2][:4]+" where parent_id="+str(r[4])
                    cur.execute(s)
                    for rr in cur.fetchall():
                        rd.hmset(cs[int(company_id)]+':label_product:'+rr[0], {
                            "product_id":r[0],
                            "product_name":r[1],        
                            "label_pkg":rr[1],     
                            "batch_id":batch_id,
                            "time":r[5]
                            }
                        )
    cur.close()
    return 0
