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 time_key_check
import json

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);"
        cur.execute(s)
    cur.close()    
    return 

@csrf_exempt
def label_bind(request,company_id,action,what):
    """
    标签在线绑定接口
    """
    data={'e':'','cnt':0}
    sK = 'fgfdvgu$#&3t@j'
    
    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"
        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"
        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
        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)            
            #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 瓶盖要不要解码?
                a_bnd.append("("+company_id+","+product_id+","+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)            
            #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+"'"+\
                ")")      
            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)            
            #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 瓶盖要不要解码?
                a_bnd.append("("+company_id+","+product_id+","+store_id+","+user_id+","+batch_no+","+\
                    prd_shop+","+prd_line+","+prd_team+","+qc+","+remark+","+\
                    "'"+r['cartons'][0]+"',"+cart_rel+",'"+b['code']+"',"+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]
            # 是否有副箱标
            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)   
    cur.close()
    return JsonResponse(data)

