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


#2020-10-27 11位解密
#def decrypt_11(code):  
#    c=''
#    if len(code)==11:  
#        s=code.lower()[:10]    
#        code=s[9]+s[2]+s[1]+s[3:9]+s[0] 
#        try:
#            c=str(int('0x'+code,16))
#        except Exception:
#            c=code
#    return c


#def decrypt_11h(code):    
#    if code[0].lower() in ('h','g'):
#        return decrypt_11(code[1:])
#    return ''



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})

#lisin 2024-08-24 包材管理相关
#采购记录查询
@login_required
def packaging_purchase_query(request):
    packaging_purchase_id = request.GET.get('purchase_id')
    instore_id = int(request.GET.get('instore_id')) if request.GET.get('instore_id') != 'None' else request.GET.get('instore_id') 
    
    try:
        packaging_purchase = PackagingPurchasRecord.objects.get(id=packaging_purchase_id)
        packaging = Packaging.objects.get(id=packaging_purchase.packaging.id)
        vendor = Vendor.objects.get(id=packaging_purchase.vendor.id)

        # 已入库数量查询
        packaging_instore = PackagingInstore.objects.filter(company=request.user.company, packaging_purchas=packaging_purchase)
        print('packaging_inventory',packaging_instore)
        yet_instore_num = 0
        if len(packaging_instore):
            for i in packaging_instore:
                if int(i.id) != instore_id:#排除当前入库记录数量
                    yet_instore_num += i.instore_num 

        #查询库存
        instore_num=0
        receive_num=0
        packaging_instore = PackagingInstore.objects.filter( company=request.user.company, packaging=packaging)
        packaging_receive = PackagingReceive.objects.filter( company=request.user.company, packaging=packaging)
        if len(packaging_instore):
            for item in packaging_instore:
                instore_num += int(item.instore_num)
        if len(packaging_receive):
            for i in packaging_receive:
                receive_num += int(i.receive_num)  
        packaging_inventory = instore_num - receive_num

        data = {
            'dt': packaging_purchase.dt,
            'qty': packaging_purchase.qty, #采购数量    
            'yet_instore_num': yet_instore_num, #采购单已入库数量
            'packaging_inventory': packaging_inventory,
            'packaging_id': packaging.id,
            'packaging_code': packaging.code,
            'packaging_name': packaging.name,
            'packaging_specification': packaging.specification,
            'vendor_name': vendor.name,
        }
        return JsonResponse(data)
    except PackagingInstore.DoesNotExist:
        return JsonResponse({'error': 'id not found'}, status=404)
# 包材采购记录list
@login_required
def packaging_purchase_list(request):
    company_id=request.user.company_id
    data={'data':[],'clmns':[],'e':[],'subtt':['库存管理', '包装材料采购'],'order': [],
        'murl':'/mes/packaging/purchase/edit/','dt':False,'add':True,'allown_delete':True,'show_add':True}
    data['clmns']=[_("采购编码"),_("采购时间"),_("包材编码"),_('包材名称'),('规格型号'),_("单位"),_("采购供应商"),_("采购单价"),_("采购数量"),_("采购总价") ]
    s="select a.id, coalesce(a.code,''), to_char(a.dt,'yyyy-mm-dd'), p.code, p.name, coalesce(p.specification,''),"+\
      "case when p.unit_id IS NOT NULL THEN (select name from mes_unit where id=p.unit_id) else '/' end,"+\
      "v.name, a.unit_price, a.qty, a.total_price "+\
      "from mes_packagingpurchasrecord a "+\
      "inner join mes_packaging p on a.packaging_id=p.id "+\
      "inner join mes_vendor v on a.vendor_id=v.id "+\
      "where a.company_id="+ str(company_id)
    cur=connection.cursor() 
    cur.execute(s)
    for r in cur.fetchall():  
        code_link = f"<a href='/mes/packaging/purchase/edit/{r[0]}'>{r[1]}<a/>"
        data['data'].append([ code_link,r[2],r[3],r[4],r[5],r[6],r[7],float(r[8]),int(r[9]),float(r[10]) ])
    
    return render(request,'obj/list_obj.html',data)

#包材采购记录添加/编辑
@login_required
def packaging_purchase_edit(request,id=None):
    if id is not None:
        obj = PackagingPurchasRecord.objects.get(id=int(id))
    else:
        obj = PackagingPurchasRecord()
    is_saved=False
    company_id=request.user.company.id
    obj.company = request.user.company
    obj.company_id = company_id
    md_packaging = Packaging.objects.filter(company=request.user.company).all()
    md_vendor = Vendor.objects.filter(company=request.user.company).all()
    if request.method == 'GET':
        if id is None: #新增 创建编码
            obj.code = create_code('CG')
        form = PackagingPurchaseRecordForm(instance = obj,user=request.user)

    elif request.method == "POST":   
        form = PackagingPurchaseRecordForm(request.POST,instance = obj,user=request.user)
        if form.is_valid():
            form.save()
            is_saved = True
    return render(request,'mes/packaging_purchase_edit.html',{'form':form,'is_saved':is_saved,
                   'md_packaging':md_packaging, 'md_vendor':md_vendor})


#包材入库记录list
@login_required
def packaging_instore_list(request):
    company_id=request.user.company_id
    data={'data':[],'clmns':[],'e':[],'subtt':['库存管理', '包装材料入库'],'order': [],
        'murl':'/mes/packaging/instore/edit/','dt':False,'add':True,'allown_delete':True,'show_add':True}
    data['clmns']=[_("入库编码"),_("入库时间"),_("采购编码"),_("采购时间"),_("包材编码"),_("包材名称"),_("规格型号"),_("单位"),_("采购供应商"),_("采购数量"),_("入库数量") ]
    s="select a.id, coalesce(a.code,''), to_char(a.dt,'yyyy-mm-dd'), ppr.code, to_char(ppr.dt,'yyyy-mm-dd'), p.code," +\
      "p.name, coalesce(p.specification,'/'),"+\
      "case when p.unit_id IS NOT NULL THEN (select name from mes_unit where id=p.unit_id)  else '/' end,"+\
      "v.name, ppr.qty, a.instore_num "+\
      "from mes_packaginginstore a "+\
      "inner join mes_packagingpurchasrecord ppr on a.packaging_purchas_id=ppr.id "+\
      "inner join mes_packaging p on ppr.packaging_id=p.id "+\
      "inner join mes_vendor v on ppr.vendor_id=v.id "+\
      "where a.company_id="+ str(company_id)
    cur=connection.cursor() 
    cur.execute(s)
    for r in cur.fetchall():
        code_link = f"<a href='/mes/packaging/instore/edit/{r[0]}'>{r[1]}<a/>"
        data['data'].append([ code_link,r[2],r[3],r[4],r[5],r[6],r[7],r[8],r[9],r[10],r[11] ])
    
    return render(request,'obj/list_obj.html',data)

#包材入库记录添加/编辑
@login_required
def packaging_instore_edit(request,id=None):
    if id:
        obj = PackagingInstore.objects.get(id=int(id))
    else:
        obj = PackagingInstore()
    is_saved=False
    company_id=request.user.company.id
    obj.company = request.user.company
    obj.company_id = company_id
    if request.method == 'GET':
        if id is None:#新增 创建code
            obj.code = create_code('RK')
        form = PackagingInstoreForm(instance = obj,user=request.user)       
    elif request.method == "POST":  
        packaging_id=request.POST.get('packaging')
        packaging = Packaging.objects.get(id=packaging_id)
        form = PackagingInstoreForm(request.POST,instance = obj,user=request.user)
        if form.is_valid():
            #采购记录添加
            form.save()
            is_saved = True
    return render(request,'mes/packaging_instore_edit.html',{'form':form,'is_saved':is_saved, 'instore_id':id})

#包材库存
def packaging_inventory(request,what=None):
    #库存查询
    if(what=='query'):
        packaging_id = request.GET.get('id')
        try:
            packaging = Packaging.objects.get(id=packaging_id)
            #查询库存
            instore_num=0
            receive_num=0
            packaging_instore = PackagingInstore.objects.filter( company=request.user.company, packaging=packaging)
            packaging_receive = PackagingReceive.objects.filter( company=request.user.company, packaging=packaging)
            if len(packaging_instore):
                for item in packaging_instore:
                    instore_num += int(item.instore_num)

            if len(packaging_receive):
                for i in packaging_receive:
                    receive_num += int(i.receive_num)  
            print('packaging_inventory',instore_num,receive_num)
            packaging_inventory = instore_num - receive_num
            data = {
                'inventory_num': packaging_inventory,
                'packaging_code': packaging.code,
                'packaging_name': packaging.name,
                'packaging_specification': packaging.specification,
            }
            return JsonResponse(data)
        except PackagingInstore.DoesNotExist:
            return JsonResponse({'error': 'id not found'}, status=404)
    #库存报表
    if(what == 'list'):
        company_id=request.user.company_id
        data={'data':[],'clmns':[],'e':[],'subtt':['库存管理', '包材库存报表'],'order': [],
            'murl':'','dt':False,'add':False,'allown_delete':False,'show_add':False}
        data['clmns']=[_("包材编码"),_("包材名称"),_("规格型号"),_("单位"),_("采购数量"),_("入库数量"),_("领用数量"),_("库存数量")]
        s="select p.id, p.code, p.name, coalesce(p.specification,'/'),"+\
          "case when p.unit_id IS NOT NULL THEN (select name from mes_unit where id=p.unit_id)  else '/' end,"+\
          "(select SUM(purchase.qty) from mes_packagingpurchasrecord purchase where purchase.packaging_id=p.id),"+\
          "(select SUM(instore.instore_num) from mes_packaginginstore instore where instore.packaging_id=p.id),"+\
          "(select SUM(receive.receive_num) from mes_packagingreceive receive where receive.packaging_id=p.id),"+\
          "(select SUM(instore.instore_num) from mes_packaginginstore instore where instore.packaging_id=p.id) - (select SUM(receive.receive_num) from mes_packagingreceive receive where receive.packaging_id=p.id) as inventory_num "+\
          "from mes_packaging p "+\
          "where p.company_id="+ str(company_id)
        cur=connection.cursor() 
        cur.execute(s)
        for r in cur.fetchall():  
            # 将 None 转换为 /
            r = ["/" if x is None else x for x in r]
            inventory_num = max(0, r[8])
            data['data'].append([ r[1],r[2],r[3],r[4],r[5],r[6],r[7],inventory_num ])
        return render(request,'obj/list_obj.html',data)
    
# 包材领用记录list
@login_required
def packaging_receive_list(request):
    company_id=request.user.company_id
    data={'data':[],'clmns':[],'e':[],'subtt':['库存管理', '包装材料领用'],'order': [],
        'murl':'/mes/packaging/receive/','dt':False,'add':True,'allown_delete':True,'show_add':True} 
    data['clmns']=[_("领用编码"),_("领用时间"),_("包材编码"),_('包材名称'),('规格型号'),_("单位"),("领取数量") ]
    s="select a.id, coalesce(a.code,''), to_char(a.dt,'yyyy-mm-dd'), p.code, p.name, coalesce(p.specification,'/'),"+\
      "case when p.unit_id IS NOT NULL THEN (select name from mes_unit where id=p.unit_id)  else '/' end,"+\
      "a.receive_num "+\
      "from mes_packagingreceive a "+\
      "inner join mes_packaging p on a.packaging_id=p.id "+\
      "where a.company_id="+ str(company_id)
    cur=connection.cursor() 
    cur.execute(s)
    for r in cur.fetchall():
        code_link = f"<a href='/mes/packaging/receive/{r[0]}'>{r[1]}<a/>"
        data['data'].append([ code_link,r[2],r[3],r[4],r[5],r[6],r[7] ])
    
    return render(request,'obj/list_obj.html',data)

#包材领用记录 添加/编辑
@login_required
def packaging_receive(request,id=None):
    if id:
        obj = PackagingReceive.objects.get(id=int(id))
    else:
        obj = PackagingReceive()
    is_saved=False
    company_id=request.user.company.id
    obj.company = request.user.company
    obj.company_id = company_id
    if request.method == 'GET':
        if id is None:#新增 创建code
            obj.code = create_code('LY')
        form = PackagingReceiveForm(instance = obj,user=request.user)       
    elif request.method == "POST":   
        form = PackagingReceiveForm(request.POST,instance = obj,user=request.user)
        packaging_id = request.POST.get('packaging')
        receive_num = request.POST.get('receive_num')
        packaging = Packaging.objects.get(id=packaging_id)
        if form.is_valid():
            form.save()
            is_saved = True

    return render(request,'mes/packaging_receive.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)       
        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(external_code,'/'),coalesce(address,''),coalesce(contents,'/'),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],r[5],r[6],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,thirdparty=None):
    """
    标签在线绑定接口
    """
    data={'e':'','cnt':0} 
    
    # 2023-09-01 第三方APP登录 
    if thirdparty:
        if thirdparty in settings.THIRDPARTY_KEYS:
            k=settings.THIRDPARTY_KEYS[thirdparty]
        else:            
            return JsonResponse({'e': '无效的第三方'})
    else:
        k = sK
    e = time_key_check(request, k)
    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]

    # 2023-10-18
    if action=='retrieve':
        if what=='property':
            # 取产品，仓库信息
            data={'e':'','stores':[],'products':[],'origin':[]}
            s="select p.id,p.name,g.prop from "+\
                "(SELECT id, name FROM product_product where company_id="+company_id+") p "+\
                "left outer join "+\
                "(select j.product_id,json_object_agg(j.name,j.value) prop from "+\
                    "(SELECT product_id,"+\
                        "(select name from company_categoryprop where id="+\
	                    "(select categoryprop_id from product_productprop where id=prd_property_id)"+\
                        "),"+\
                        "(select value from logistics.product_productprop where id=prd_property_id)"+\
                        "FROM product_prdprops where product_id in "+\
                            "(select id FROM product_product where company_id="+company_id+")"+\
                    ") j "+\
                    "group by j.product_id "+\
                ") g "+\
                "on p.id=g.product_id"
            cur.execute(s)
            for r in cur.fetchall():
                data['products'].append({
                    "id":r[0],
                    "name":r[1],
                    "property":r[2]
                })
            cur.execute("select id,name from inventory_storehouse where company_id="+company_id)        
            for r in cur.fetchall() :
                data['stores'].append({'id':r[0],'name':r[1]})
            if company_id in ['180087',]:            
                # 吉林康庄大道农业发展有限公司
                # 种子来源地 追加返回一级行政区划
                adms=[]
                # 不包含港澳台
                s="SELECT id,name FROM gis.adm_map_cn where level=1 "+\
                    "and adm_id>0 "+\
                    "order by name"
                cur.execute(s)
                for r in cur.fetchall():
                    adms.append({'id':r[0],'name':r[1]})
                data['origin']=adms
            return JsonResponse(data)
        else:
            # 目前只取peoperty
            data['e']='invalid url'
        return JsonResponse(data)
    elif 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)




            # 2024-02-28 9537;"54度500mL酒鬼酒生肖酒纪念套装（2023）"三种生肖酒重新绑定一箱
            pid=request.GET.get('product_id','')
            if pid=='9537':
                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"
        
        # 2023-10-18
        origin_id='null'
        if 'origin_id' in r:
            origin_id=str(r['origin_id'])
        
        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'])
        if store_id == '0':
            s=f"""
            select id from inventory_storehouse where company_id={c.id}  limit 1 
            """
            cur.execute(s)
            store=cur.fetchone()
            if store:
                store_id=str(store[0])
        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"+\
                ",origin_adm_id"+\
            ")"  
            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+\
                    ","+origin_id+\
                ")")    
            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]
                
                
                # 2023-08-24 酒海不干，瓶标链接内容为加密码(解密后与箱标跨段)
                bcd=b['code']                
                if bcd[0] in ('h','g'):
                    dbcd=decrypt_11h(bcd)
                    if dbcd:
                        bcd=dbcd  
                        tb=bcd[: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+","+\
                        "'"+bcd+"',1,"+x_id+",false,false,true,"+remark+")")
                else:
                    d_tbs[tb]=["("+company_id+","+type_id+","+cat_id+","+p_tb+","+\
                        "'"+bcd+"',1,"+x_id+",false,false,true,"+remark+")"]
                    # 盒标号段数组
                    bottle_tb.append(bcd[: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)   

        # 2023-10-25
        elif what=='CAP':
            # 瓶(盒)标绑瓶盖 数据一条条传
            # 现只考虑上传西凤模式盖码数据，即内(加密码)外码(10位16进制)由瓶盖厂绑定;酒厂扫盖外码与瓶(盒)标绑定
            tb='mes_bottlcap'
            #b=r['bottle']   # 瓶标            
            c=r['label_code']
            p=str(r['label_pkg'])
            
            g=r['cap']      # 盖内码
            o=r['outer']    # 盖外码 
            cap=g
            if len(o)==10 and len(c)==12 and c.isdigit():  
                g="(select cap from bottlecap.fcty_binding_details where outter='"+o.upper()+"')"
                o="'"+o+"'"            
                cur.execute("update "+tb+" set is_delete=true where label_code='"+c+"'")             
                s="INSERT INTO "+tb+"(company_id,product_id,store_id,user_id,batch_no,"+\
                        "prd_shop,prd_line,prd_team,qc,remark,"+\
                        "label_code,label_pkg, cap, outter"+\
                    ") values ("+\
                        company_id+","+product_id+","+store_id+","+user_id+","+batch_no+","+\
                        prd_shop+","+prd_line+","+prd_team+","+qc+","+remark+","+\
                        "'"+c+"',"+p+","+g+","+o+\
                    ")" 
                cur.execute(s) 
                data['cnt']=1
            
    # 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.upper() not in ('HX','PX', 'CAP','PH'):
            data['e']='当前只处理 HX(盒箱),PX(瓶箱),CAP(瓶盖),PH(瓶盒) 绑定数据'
            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-10-26 西凤老绿瓶模式瓶盖绑定数据
        # 现只考虑上传西凤模式盖码数据，即内(加密码)外码(10位16进制)由瓶盖厂绑定;酒厂扫盖外码与瓶(盒)标绑定
        if what.upper()=='CAP': 
            lmt=10 # 数组长度达到此长度，写入一次临时表 
            tb='mes_bottlcap'    
            tmp="codes"            
            s="create temp table "+tmp+"("+\
                "id serial,"+\
                "label_code character varying(20) not null,"+\
                "label_pkg smallint NOT NULL,"+\
                "cap character varying(20), "+\
                "outter character varying(20),"+\
                "duplications jsonb)"
            cur.execute(s)
            a=[]                
            n=0
            tt=0
            ok=0
            sIns="INSERT INTO "+tmp+"(label_code,cap,outter,label_pkg,duplications)"
            for d in r['details']:
                b=d['label_code']   # 瓶标            
                g=d['cap']          # 盖内码
                o=d['outer']        # 盖外码  
                if len(o)==10 and len(b)==12 and b.isdigit():  
                    g = "(select cap from bottlecap.fcty_binding_details where outter='" + o.upper() + "' ORDER BY tm LIMIT 1)"
                    o="'"+o+"'"   
                    a.append("('"+b+"',"+g+","+o+","+str(d['label_pkg'])+","+\
                        "(select jsonb_agg(jsonb_build_object("+\
                            "'id',id, 'label_code',label_code, 'label_pkg',label_pkg, 'cap',cap, 'outter',outter"+\
                            ")) from "+tb+" where (label_code='"+b+"' or outter='"+d['outer']+"') and is_delete=false"+\
                        "))")
                    n=n+1
                    if n==lmt:
                        cur.execute(sIns+" values "+",".join(a))
                        tt=tt+cur.rowcount
                        a=[]
                        n=0
            if a:    
                cur.execute(sIns+" values "+",".join(a))   
                tt=tt+cur.rowcount
            if tt:
                # 先写入瓶盖绑定明细
                s="INSERT INTO "+tb+"(company_id,product_id,store_id,user_id,batch_no,"+\
                    "prd_shop,prd_line,prd_team,qc,remark,"+\
                    "label_code,cap,outter,label_pkg) select "+\
                    company_id+","+product_id+","+store_id+","+user_id+","+batch_no+","+\
                    prd_shop+","+prd_line+","+prd_team+","+qc+","+remark+","+\
                    "label_code,cap,outter,label_pkg from "+\
                    "(select label_code,cap,outter,label_pkg from "+tmp+" group by label_code,cap,outter,label_pkg) g"
                cur.execute(s)
                ok=ok+cur.rowcount                
                # 写入重复绑定表 如果有
                s="INSERT INTO mes_labelbindduplicate(company_id,user_id,portal,label_parent,label_child,duplications) select "+\
                    company_id+","+user_id+",'CAP',label_code,outter,duplications from "+tmp+" where jsonb_array_length(duplications)>0"   
                cur.execute(s)
                # 重复数据打删除标记 如果有
                s="update "+tb+" set is_delete=true where id in ("+\
                    "select id from (SELECT (jsonb_array_elements(duplications)->>'id')::int id	FROM "+tmp+" "+\
                        "where jsonb_array_length(duplications)>0) t "+\
	                "group by t.id "+\
                    ")"
                cur.execute(s)      
            data={'e':'','cnt':tt,'cnt_ok':ok}   
            return JsonResponse(data) 


        # 2023-10-30  离线瓶盒绑定 数据质量不确定;对应的盒是否绑定了箱或什么时候跟箱绑定也不确定
        # 这里只存数据，remark标注 "离线,套标关系未写入"
        elif what=="PH":                
            a=[]   
            tb='mes_labelbindph'
            remark="'离线,套标关系未写入'"            
            
            for d in r['details']:
                a.append("("+company_id+","+product_id+","+store_id+","+user_id+","+batch_no+","+\
                    prd_shop+","+prd_line+","+prd_team+","+qc+","+remark+","+\
                    "'"+d['box']+"','"+d['bottle']+"'"+\
                ")")            
            # 插入mes绑定明细
            cur.execute("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) values "+",".join(a)
            )
            data['cnt']=cur.rowcount
            cur.close()
            return JsonResponse(data)



        # 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']:
            if isinstance(d['main_code'],list):
                m=d['main_code'][0]
            else:
                m=d['main_code']
            mt="'"+m[:4]+"'"
            mc="'"+m+"'"
            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+")")
                
                #if len(d['main_code'])>20 or len(d['related_code'])>20 or len(c)>20:
                #    print(mc+":"+d['main_code']+","+d['related_code']+","+c)

        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("'",''), level_id=1, vtype_id=1, company_id=cid, hierarchy_id=cid, ancestor_id=cid, product_id=product_id,
            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,what=None):
    """
    标签在线绑定控制
    """
    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)
    single_inbound = str(request.GET.get('singleInbound')).lower() == "true"
    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,coalesce((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"

        if cid.__eq__(19267):
            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,coalesce((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,product_id,store_id " + \
                "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"

        # 2023-10-18
        if cid in ['180087',]:
            data['clmns']=['日期','生产时间','产品','包装规格','生产批次号','制种产地','仓库','产量','待入库','备注']
            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,coalesce((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,"+\
                    "(select name from gis.adm_map_cn where id=origin_adm_id) "+\
                "from "+\
                "(select min(tm1) tm1,max(tm2) tm2,prd_line,batch_no,store_id,product_id,count(*) qty,'三级绑定' lv,sum(q0) q0,dt,null origin_adm_id 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,origin_adm_id from "+\
                    "(select min(tm) tm1,max(tm) tm2,prd_line,batch_no,store_id,product_id,carton_code,count(*) q0,dt,origin_adm_id FROM mes_labelbindpx"+sWhere+\
                        "group by prd_line,batch_no,store_id,product_id,carton_code,dt,origin_adm_id"+\
                    ") h "+\
                    "group by prd_line,batch_no,store_id,product_id,dt,origin_adm_id"+\
                ") g"
        cur.execute(s)
        cnt=0
        for r in cur.fetchall():
            #print(r)
            
            #print(r[8],r[9],r[10])
            if r[10]:
                a=r[10].split('*')            
            else:
                a=['1','1']
                
            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]))+"盒"

            # 2023-10-18
            if cid=='180087':
                q1=q1.replace('箱','大袋').replace('盒','小袋')
                q2=q2.replace('箱','大袋').replace('盒','小袋')
                data['data'].append([ r[0],r[1],r[3],r[10],r[4],r[12],r[5],q1,q2,r[7] ])
            elif cid.__eq__(19267):
                btn_html = "<a type=\"button\" id='btn-single-inbound' class='btn btn-primary'>入库本行</a>" \
                    if int(qty_in/int(a[1])) else "已入库"
                data['data'].append([r[0], r[1], r[3], r[10], r[4], r[5], q1, q2, r[7], btn_html, r[-2], r[-1]])
            else:
                data['data'].append([ r[0],r[1],r[3],r[10],r[4],r[5],q1,q2,r[7] ])    

              
            
            #print(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=='bottlcap_output':
        data={'data':[],'clmns':['日期','生产时间','产品','生产批次号','数量','备注'],'dt_fr':dt_fr,'dt_to':dt_to,'qty_uninbound':0}
        s=f"""
            select to_char(dt,'yyyy-mm-dd'),to_char(tm,'yyyy-mm-dd hh24:mi:ss') ,(select name from product_product where product_id=id) as product,batch_no,count(1)
            from  logistics.mes_bottlcap {sWhere} group by dt,tm,product,batch_no
        """
        cur.execute(s)
        for r in cur.fetchall():
            data['data'].append([r[0],r[1],r[2],r[3],r[4],''])
        return render(request, 'mes/product_bind.html', data)
    #2023-12-12
    elif action=='bottlecap_hp':
        data = {'data': [], 'clmns': ['日期', '生产时间', '产品', '生产批次号', '数量', '备注'], 'dt_fr': dt_fr, 'dt_to': dt_to,
                'qty_uninbound': 0}
        s=f"""
            select to_char(dt,'YYYY-MM-DD'),to_char(min(tm),'HH24:MI:SS') tm1,to_char(max(tm),'HH24:MI:SS') tm2,
            (select name from product_product where product_id=id) as product,batch_no,count(1)
            FROM mes_labelbindph {sWhere} group by dt,product,batch_no
        """
        cur.execute(s)
        for r in cur.fetchall():
            data['data'].append([r[0], str(r[1])+"--"+str(r[2]), r[3], r[4], r[5], ''])
        return render(request, 'mes/product_bind.html', data)
    elif action=='inbound':
        if single_inbound:
            # prd_line, batch_no, store_id, product_id, dt
            batch_no = request.GET.get('batchNo', '')
            store_id = request.GET.get('storeId', '0')
            product_id = request.GET.get('productId', '0')
            dt = request.GET.get('dt', dt)
            sWhere = (f" WHERE company_id = {cid} AND is_delete = FALSE AND dt = '{dt}' AND batch_no = '{batch_no}' AND "
                      f"store_id = {store_id} AND product_id = {product_id}")
        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),origin_adm_id "+\
                "from "+\
                "(select product_id,store_id ,batch_no,dt,max(tm) tm,origin_adm_id from "+tb+sWhere+" and inbound=false "+\
                    "group by product_id,batch_no,store_id,dt,origin_adm_id"+\
                ") 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] 
                
                # 2023-10-18
                origin_adm_id=''                
                if r[7]:
                    origin_adm_id=r[7]
                sOrigin=''                
                if origin_adm_id:
                    sOrigin=" and origin_adm_id="+str(origin_adm_id)  

                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+"'" +sOrigin
                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():
                    if rr[4]:
                        a=rr[4].split('*')   
                        if rr[2]==int(a[1]):
                            codes.append(rr[0])
                        else:
                            for c in rr[3]:
                                codes.append(c)    
                    else:
                        codes.append(rr[0])

                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],  
                        
                        # 2023-10-18
                        "origin_adm_id":origin_adm_id,
                    }                          
                    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)

    # 2023-11-25 带瓶盖产品维护
    elif action=='productcap':
        if what=='html':
            # 返回维护主页面
            cur.close()
            return render(request,'mes/bottlecap_product.html')
        elif what=='list':
            # 返回带瓶盖产品列表
            '''
            var clmns=[                 
                {'title':'id', 'data':'id'},             
                {'title':'企业名称', 'data':'company'}, 
                {'title':'产品名称', 'data':'product'},  
                {'title':'每箱瓶(盖)数', 'data':'cap_cnt'},  
                {'title':'添加时间', 'data':'tm'}, 
                {'title':'备注说明', 'data':'remark'},
            ];
            '''
            data=[]
            s="select id,coalesce(remark,''),cap_cnt,to_char(tm,'yyyy-mm-dd hh24:mi:ss'),"+\
                "(select name from company_company where id=company_id),"+\
                "(select name from product_product where id=product_id) "+\
                "from mes_bottlecapproduct"
            cur.execute(s)
            for r in cur.fetchall():
                data.append({
                    "id":r[0],
                    "remark":r[1],
                    "cap_cnt":r[2],
                    "tm":r[3],
                    "company":r[4],
                    "product":r[5],                   
                })
            return JsonResponse({'e':'','data':data},safe=False)
        elif what=='edit':
            # 添加/修改 id 为空 新建 ；不为空 修改
            data={'e':''}
            if request.method=='GET':
                id=request.GET.get('id','')
                data={}
                if id:
                    s="select id,company_id,product_id,coalesce(remark,''),cap_cnt,to_char(tm,'yyyy-mm-dd hh24:mi:ss'),"+\
                        "(select name from product_product where id=product_id),"+\
                        "(select name from company_company where id=company_id) "+\
                        "from mes_bottlecapproduct where id="+id
                    cur.execute(s)
                    r=cur.fetchone()
                    if r:
                        data={'id':r[0],'company_id':r[1],'product_id':r[2],'remark':r[3],'cap_cnt':r[4],
                            'tm':r[5],'company_name':r[6],'product_name':r[7]                                    
                        }                
            elif request.method=='POST':
                data={'e':''}
                r=json.loads(request.body)
                if r['id']:
                    o=BottlecapProduct.objects.get(id=r['id'])
                else:
                    o=BottlecapProduct()
                o.company_id=r['company_id']
                o.product_id=r['product_id']
                o.cap_cnt=r['cap_cnt']
                o.remark=r['remark']
                o.save()
            return JsonResponse(data)




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




# 2023-12-29
@csrf_exempt
def bottlecap_makeup(request,company_id,action):
    data={'e':''}
    if company_id=='1':
        tb='logistics.z_bottlecap_binding'        
        # 酒鬼酒盒标、瓶盖绑定补录
        if action=='view':
            return render(request,'mes/bottlecap_makeup.html')
        elif action=='retrieve':
            # 传瓶标，外盖码，返回外盖码版本2加密内盖码和箱绑定信息   
            tb_lbl='' # 标签是在酒鬼跨段表还是现一号段套标表            
            bottle=request.GET.get('bottle','')
            label=request.GET.get('label','')
            carton=request.GET.get('carton','')
            cap_type=''
            cur = connection.cursor()
            s=f"""
                select * from label.lbl_binding_jg where '{bottle}'=code 
            """
            cur.execute(s)
            z=cur.fetchone()                   
            if z:     
                if label and bottle:
                    # 先判断传过来的是盖内还是盖外码
                    if label.isdigit():
                        # 盖外码
                        cap_type='盖外码'
                        cap=CodeEncryptV2(label)
                        code=label
                    else:
                        # 盖内码
                        d=decrypt_11h(label)
                        if d=='error':
                            data['e']='盖内码错误'
                            return JsonResponse(data)                    
                        cap=label
                        code=d  
                        cap_type='盖内码'                
                    
                    data['cap_label']=cap
                    data['cap_code']=code
                    data['cap_type']=cap_type               
                    
                    cur=connection.cursor()
                    cur.execute("select tm,box_code,bottle,label,product from "+tb+" "+\
                        "where code='"+code+"' or label='"+cap+"' or bottle='"+bottle+"'")
                    r0=cur.fetchone()
                    if False:
                    #if r0:
                        data['e']='已有瓶盖绑定记录：箱码:'+r0[1]+',瓶码:'+r0[2]+',盖:'+r0[3]+' '+r0[0].strftime('%Y-%m-%d %H:%M:%S')+' '+r0[4]
                        cur.close()
                    else:
                        a=[]
                        pk_id=0
                        # 优先查询酒鬼跨段表
                        s="select parent_id from label.lbl_binding_jg where code='"+bottle+"'"                           
                        cur.execute(s)
                        r=cur.fetchone()
                        if r:
                            # 在酒鬼跨段表
                            pk_id=str(r[0])
                            tb_lbl='label.lbl_binding_jg'
                        else:                        
                            s="select parent_id from label.t"+bottle[:4]+" where code='"+bottle+"'"   
                            try:
                                cur.execute(s)
                                r=cur.fetchone()
                                if r:
                                    # 在同号段套标表
                                    pk_id=str(r[0])
                                    tb_lbl='label.t'+bottle[:4]
                            except Exception:
                                # 同号段套标表不存在
                                pass                    
                        data['tb_lbl']=tb_lbl                    
                        if pk_id:
                            s="select code,array_to_string(array(select ("+\
                                "select code from "+tb_lbl+" where parent_id="+str(pk_id)+" and package_id=3)),','),"+\
                                "(select count(*) from "+tb_lbl+" where parent_id="+str(pk_id)+" and package_id=1) "+\
                                "from "+tb_lbl+" where id="+str(r[0])
                            cur.execute(s)
                            rr=cur.fetchone()
                            if rr:
                                data['label_pk']={
                                    'id':int(pk_id),
                                    'code':rr[0]
                                }
                                data['cartons']=rr[1]
                                s="SELECT tm,code,bottle,box_code,product,prd_line,batch_no,product_id,store_id,qc,bottles_cnt,"+\
                                    "(select name from inventory_storehouse where id=store_id),"+\
                                    "(select bottle_cnt from z_bottle_cap_product where id=b.product_id),label "+\
                                    "FROM "+tb+" b  where label_pk_id="+pk_id
                                cur.execute(s)
                                for rrr in cur.fetchall():                                
                                    pnm=rrr[4]
                                    ln= rrr[5]
                                    bth=rrr[6]
                                    pid=rrr[7]     
                                    sto_id=rrr[8]
                                    qc= rrr[9]
                                    sto_name=rrr[11]                                
                                    cnt=rrr[12]
                                    tm=rrr[0].strftime('%Y-%m-%d %H:%M:%S')
                                    a.append({
                                        'bottle':rrr[2],
                                        'code':rrr[1],
                                        'label':rrr[13],
                                        'tm':tm
                                    })
                                data['bottles']=a
                                data['prd']={
                                    'product_name':pnm,
                                    'product_id':pid,
                                    'line':ln,
                                    'store':{'id':sto_id,'name':sto_name},
                                    'batch_no':bth,
                                    'qc':qc,
                                    'bottle_cnt':cnt,
                                    'tm':tm
                                }  
                        else:
                            data['e']=bottle+'瓶码错误或没有套标绑定记录'
                            cur.close()
                        cur.close()   
                else:
                    data['e']='必传盖码(内/外)和瓶标'
            else:
                s=f"""
                    insert into label.lbl_binding_jg(code,package_id,category_id,parent_id,be_replaced, 
                    to_replace, is_pk, is_related, is_active, have_img)    
                    select bottle,1,1,label_pk_id,false,false,false,false,true,false  from logistics.z_bottlecap_binding 
                    where  box_code='{carton}' and bottle not in (select code from label.lbl_binding_jg where label_pk_id=parent_id);
                    insert into label.lbl_binding_jg(code,package_id,category_id,parent_id,be_replaced,
                    to_replace, is_pk, is_related, is_active, have_img)  
                     select {bottle},1,1,id,false,false,false,false,true,false from label.lbl_binding_jg where code='{carton}' and package_id=3  
                """
                cur.execute(s)
                connection.commit()
                data['e'] = '请再次点击检查绑定信息按钮！'
        elif action=='binding':
            rmk='补录'
            user=str(request.user.id)
            r=json.loads(request.body)
            g=r['cap_label']
            c=r['cap_code']
            lbl=r['bottle']
            product=r['product_name']
            pid=str(r['product_id'])
            ln=r['line']
            pk_id=r['pk_id']
            pk_code=r['pk_code']
            cnt=r['bottle_cnt']
            batch_no=r['batch_no']
            qc=r['qc']
            store_id=str(r['store_id']) 
            tm=r['tm']
            tb_lbl=r['tb_lbl']            
            s="INSERT INTO "+tb+"("+\
                    "label,code,bottle,type,remark,company_id,product,prd_line,username,label_pk_id,"+\
                    "box_code,bottles_cnt,batch_no,product_id,qc,store_id,dt,tm"+\
                ") values ("+\
                    "'"+g+"','"+c+"','"+lbl+"',999,'"+rmk+"',"+company_id+",'"+product+"','"+ln+"','"+user+"',"+str(pk_id)+","+\
                    "'"+pk_code+"',"+str(cnt)+",'"+batch_no+"',"+pid+",'"+qc+"',"+store_id+",'"+tm+"','"+tm+"'"+\
                ") "+\
                "on conflict /*no targets specified*/  do nothing"  
            
            #print(s)
            
            cur=connection.cursor()
            cur.execute(s)
            cnt=cur.rowcount
            cur.close()            
            if not cnt:
                data['e']='写入失败，请确定瓶标 '+lbl+' 盖码 '+g+' 没有绑定记录'
            data['cnt']=cnt
        elif action=='redis_html':
            # 瓶盖(内外)/瓶标查询redis绑定信息 页面
            return render(request,'mes/redis_query.html')
        elif action=='redis_query':
            data={'e':'','data':{}}
            rd=redis.StrictRedis(host='localhost', port=settings.RD['port'], db=10, password=settings.RD['password'], decode_responses=True) 
            # redis键名前缀
            prfx='online_'+company_id+'_'

            code=request.GET.get('code','')
            what=request.GET.get('what','')
            #cap=request.GET.get('cap','')
            if what=='bottle':
                # 通过瓶标查盖码
                k=prfx+code
            elif what=='cap':
                # 通过盖码查瓶标
                if code.isdigit():
                    # 盖外码
                    k=prfx+CodeEncryptV2(code)                    
                else:
                    # 盖内码
                    k=prfx+code
            o=rd.hgetall(k)
            if o:
                data['data']={
                    'bottle':o['bottle'],
                    'label':o['label'],
                    'code':o['code'],
                    'product':o['product'],
                    'tm':o['time'],
                    'line':o['line'],
                    'batch_no':o['batch_no']
                } 
            else:
                data['e']='没找到相关信息！'
        elif action=='redis_delete':
            data={'e':''}
            rd = redis.StrictRedis(host='localhost', port=settings.RD['port'], db=10,password=settings.RD['password'], decode_responses=True)
            # redis键名前缀
            prfx = 'online_' + company_id + '_'
            bottle=request.GET.get('code','')
            if bottle:
                k=prfx+bottle
                o=rd.hgetall(k)
                if o:
                    cap=o['label']
                    rd.delete(prfx+cap)
                    rd.delete(prfx+bottle)
                else:
                    data['e']='瓶标'+bottle+'键名不存在'
            else:
                data['e']='无法获知要删除的瓶标'

    return JsonResponse(data)




# 2024-04-17 标签替换-返工
@csrf_exempt
def label_rework(request,company_id,action,what=None):
    data={'e':'','warning':''}         
    e = time_key_check(request, sK)
    if e:
        data['e'] = e
        return JsonResponse(data)    

    
    # 标签替换明细表名(返工数据实际写入标签替换表，vtype_id设置成2)
    TB_R=settings.TB_R

    if action=='check':
        e=''
        c=request.GET.get('code','')
        uid=request.GET.get('user_id','')
        if uid:
            if uid.isdigit():
                us=User.objects.filter(id=uid)
                if us:
                    u=us[0]
                    if str(u.ancestor_id)!=company_id:
                        e='用户帐号与企业匹配错误'
                else:
                    e='用户帐号不存在'
            else:
                e='用户账号错误'
        else:
            e='用户账号不能为空'
        if e:
            data['e']=e
            return JsonResponse(data)

        if not c:
            data['e']='标签号码 不能为空'
            return JsonResponse(data)     
           
        rd=redis.StrictRedis(host='localhost', port=settings.RD['port'], db=4, password=settings.RD['password'], decode_responses=True) 
        ex=120*3600  # 设置redis键值对五天过期

        cur=connection.cursor()        
        if what=='old':
            # 旧标签检查          
            # 用标签号码在redis做键值
            tmp='rework_'+company_id+'_'+what+'_'+c
            o=rd.hgetall(tmp)  
            if o:          
                # redis内已有返工操作
                data['warning']='要返工的旧标签 %s %s 已被 %s 扫描' % (c, o['time'], o['user'])      
                data['primary']={'id':o['pk_id'], 'code':o['pk_code']}
                data['package']={'id':o['pkg_id'], 'name':o['pkg_name']}
            else:   
                # 数据库内是否有返工记录
                s="select code0,code,to_char(tm,'yyyy-mm-dd hh24:mi:ss') from "+TB_R+" "+\
                    "where company_id="+company_id+" and code0='"+c+"' and rework=true and can_rework=false"
                cur.execute(s)
                r=cur.fetchone()
                if r:
                    data['e']='旧标签 %s %s 已做返工，新号码 %s' % (c,r[2],r[1])                   
                else:
                    # 传过来的标签（将被替换的标签）如果已出库，必须先做销售退库后才可以返工
                    q=LabelQuery(company_id,c)                    
                    pk_id=q.infor['label_pk_id']
                    pk_code=q.infor['label_pk_code']  
                    if not pk_id:
                        data['e']='旧标签号码错误'                        
                    else:                        
                        data['primary']={'id':pk_id, 'code':pk_code}
                        data['package']={'id':q.package['id'], 'name':q.package['name']}
                        # 传过来的标签（将被替换的标签）如果已出库，必须先做销售退库后才可以返工
                        tb_stk='stkdetails.c'+company_id                        
                        s="select vtype_id,label_code,label_pkg,to_char(tm,'yyyy-mm-dd hh24:mi:ss'),"+\
                            "(select name from label.lbl_package where id=label_pkg), "+\
                            "(select name from company_company where id=company_to_id) "+\
                            "from "+tb_stk+" where label_pk_id="+str(pk_id)+" and is_delete=false order by tm desc"
                        cur.execute(s)
                        r=cur.fetchone()                        
                        
                        e=''
                        if r:
                            if r[0]==2:   
                                #最后一条记录是销售出库
                                e="%s %s 发货给 %s,到目前没有做销售退库" % (r[4],r[1],r[5])
                        else:                            
                            e="旧标签 %s 没有物流记录" % c
                        if e:
                            data['e']=e
                            cur.close()
                            return JsonResponse(data)                        
                        
                        # 此标签所有数据redis内如果不存在，写入键值对
                        rl=q.get_relations()    
                        tm=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
                        # 箱标
                        for ct in rl['cartons']:
                            l=ct['label']
                            # 返工前如有替换，返回最新标签
                            s="select code from "+TB_R+" where company_id="+company_id+" and code_og='"+l+"' order by tm desc limit 1"
                            cur.execute(s)
                            rr=cur.fetchone()
                            if rr:
                                l=rr[0]
                            tmp='rework_'+company_id+'_'+what+'_'+l
                            o=rd.hgetall(tmp)  
                            if not o:     
                                rd.hmset(tmp, {'pkg_id':3,'pkg_name':'箱','pk_id':pk_id,'pk_code':pk_code,'user':u.username,'time':tm})
                                rd.expire(tmp, ex)
                        # 瓶/盒标（只做二级）
                        if rl['level']==2:
                            pkg_id=1
                            pkg_name='瓶'
                        else:
                            pkg_id=2
                            pkg_name='盒'
                        for ct in rl['children']:
                            l=ct['label']
                            tmp='rework_'+company_id+'_'+what+'_'+l
                            o=rd.hgetall(tmp)  
                            if not o:     
                                rd.hmset(tmp, {'pkg_id':pkg_id,'pkg_name':pkg_name,'pk_id':pk_id,'pk_code':pk_code,'user':u.username,'time':tm})
                                rd.expire(tmp, ex)
        elif what=='new':
            # 新标签近期是否已存入redis
            tmp='rework_'+company_id+'_'+what+'_'+c
            o=rd.hgetall(tmp)  
            if o:
                data['warning']='返工用的新标签 %s %s 已被 %s 扫描' % (c,o['time'],o['user'])                
            else:
                # 用来做替换的新标签，不应该有物流记录
                q=LabelQuery(company_id,c)
                pk=q.infor['label_pk_id']
                if pk:
                    tb_stk='stkdetails.c'+company_id                        
                    s="select vtype_id,label_code,label_pkg,to_char(tm,'yyyy-mm-dd hh24:mi:ss'),"+\
                        "(select name from inventory_voucher where id=vtype_id) "+\
                        "from "+tb_stk+" where label_pk_id="+str(pk)+" and is_delete=false"
                    cur.execute(s)
                    rr=cur.fetchone()
                    if rr:
                        data['e']='用来做替换的新标签套标关系里，标签 %s %s 已有 %s 记录' % (rr[1],rr[3],rr[4])
                        cur.close()
                        return JsonResponse(data)
                rd.hmset(tmp, {'user':u.username, 'time':datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')})
                rd.expire(tmp, ex)             
        else:
            data['e']="返工标签检查，标签类别只能是旧标签或新标签"            
        cur.close()
        return JsonResponse(data)
    elif action=='upload':
        data['cnt']=0
        # 上传返工数据，实际写入标签替换表
        d = json.loads(request.body)
        e=[]
        if 'batch_no' not in d or 'team' not in d or 'user_id' not in d:
            data['e']='用户帐号，批次,班组不能为空'
            return JsonResponse(data)
        batch_no=d['batch_no']
        team=d['team']
        remark='null'
        uid=str(d['user_id'])
        if 'remark' in d:
            if d['remark']:
                remark="'"+d['remark']+"'"
        cur=connection.cursor()
        # 当前返工批次，班组是否存在，不存在就创建
        cur.execute("select id from label.lbl_replace_batch where company_id="+company_id+" "+\
            "and  batch_no='"+batch_no+"' and team='"+team+"' and rework=true")
        r=cur.fetchone()
        if r:
            id=str(r[0])
        else:
            s="INSERT INTO label.lbl_replace_batch(user_id,company_id,rework,batch_no,team,remark,txt_file) values ("+\
                uid+","+company_id+",true,'"+batch_no+"','"+team+"',"+remark+",'返工扫码') returning id"
            cur.execute(s)
            r=cur.fetchone()
            id=str(r[0])        
        sIns="insert into "+TB_R+"(company_id,batch_id,rework,code0,code,code0_pkg,code_pkg,code_og)"
        a=[]
        for dd in d['data']:
            s="("+company_id+","+id+",true,'"+\
                dd['old']+"','"+dd['new']+"',"+str(dd['pkg_id'])+","+str(dd['pkg_id'])+","+\
                "coalesce((select code_og from "+TB_R+" where  code0='"+dd['old']+"' limit 1),'"+dd['old']+"')"+\
            ")"            
            a.append(s)        
        cur.execute(sIns+" values "+",".join(a)+" on conflict(code0, code) do nothing")        
        data['cnt']=cur.rowcount
        cur.close()
        return JsonResponse(data)


def list_caps_by_bottles(request):
    """
    根据瓶码查盖子码
    """
    secret_key = {"HD": "hand$tYal1i%)20@)c1W"}
    auth = time_key_check(request, secret_key["HD"])
    res = {}
    if auth:
        res["e"] = auth
        return JsonResponse(res)
    codes = request.GET.get("codes", None)
    if not codes:
        res["e"] = "Missing parameter codes"
        return JsonResponse(res)
    codes = tuple(codes.replace(' ', '').split(','))
    s = """SELECT bottle, label encode, code decode FROM logistics.z_bottlecap_binding WHERE bottle IN %s
UNION
SELECT bottle, label encode, code decode FROM label.bottlecap WHERE bottle IN %s"""
    cur = connection.cursor()
    cur.execute(s, (codes, codes))
    data = cur.fetchall()
    if not data or len(data) == 0:
        return JsonResponse({"data": []})
    else:
        res["data"] = []
        for row in data:
            res["data"].append({
                row[0]: {
                    "encode": row[1],
                    "decode": row[2]
                }
            })
    return JsonResponse(res)
