# -*- coding: utf-8 -*-
from __future__ import unicode_literals
import json
from django.shortcuts import render

from utils.views import *
from django.views.decorators.csrf import csrf_exempt
from django.http import HttpResponse, HttpResponseRedirect, JsonResponse
from django.db import connection
from django.utils import timezone
from django.apps import apps
# Create your views here.
from django.contrib.auth.decorators import login_required
import datetime
from django.utils import timezone
from django.utils.timezone import now
from django.utils.safestring import SafeString
from django.utils.translation import gettext_lazy as _
from calendar import monthrange
from django.conf import settings
import os
from pure_pagination import Paginator
from .models import *
from product.models import Product
from company.models import Company,ChangeLog
from django.apps import apps
from django.contrib.auth.decorators import login_required
from django.shortcuts import redirect
import requests
from sales.models import *
from .forms import *
from django.forms import modelformset_factory

#2020-09-14
from decimal import *

import redis
#import pickle
import uuid

sK = 'fgfdvgu$#&3t@j'

def test_label_query(request,code,what):    
    u=User.objects.get(id=2)
    if what=='stkdev':
        data=label_stkdev(code,u)
    elif what=='relations':
        data=label_relations(code,u)
    #label_stkdev(code,user,has_price=False)
    #label_relations(code,user)
    return JsonResponse(data)



#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]        
        c=str(int('0x'+code,16))
    return c


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





#2020-04-06 酒鬼本地数据(sql server 2012)放在116服务器(sql server 2014)
def jiugui_local116(box_code):
    data = {
        'e':'',
        'product':'',
        'boxes': {'pk': '', 'related': []},
        'bottles': [],
        'stock_dev':[]
    }
    url="http://davis.china315net.com:8118/wuliu/jiugui/local/?code="+box_code
    r = requests.get(url)
    if r.status_code == requests.codes.ok:
        data=r.json()
    else:
        data['e']='local data error'
    return data




#2020-06-28
#对外标签查询接口
def external_label_info(request,company_id):
    sK='yglkfdvgu$#&3t@j'
    code=request.GET.get('code','')
    data = {
        'e': '',
        'code': code,
        'product': '',
        'package': '',    
        'photo':'',
        'price':'',
        'replace': '',        
        'boxes': {'pk': '', 'related': []},
        'packets': [],
        'bottles': [],
        'stock_dev': [],
        
        #2020-11-02
        'qc':'',
        'batch_no':'',
        'barcode':''
    }
    if not code:
        data['e']='code missing'
        return JsonResponse(data)    
      
    e = time_key_check(request, sK)
    if e:
        data['e'] = e
        return JsonResponse(data)   
    c=Company.objects.get(id=int(company_id))
    d=label_sys_info(code,c,has_price=True)   
    if d['e']:
        data['e']=d['e']
        return JsonResponse(data)  
    data['replace']=d['replace']
    data['package']=d['package']['name']
    data['product']=d['product']['name']
    data['price']=d['product']['price']
    data['packets']=d['packets']
    data['bottles']=d['bottles']
    data['boxes']=d['boxes']
    data['stock_dev']=d['stock_dev']
    if d['product']['photo']:
        data['photo']='/media/'+d['product']['photo']
    
    if 'batch_no' in  d:
        data['batch_no']=d['batch_no']
    if 'qc' in d:
        data['qc']=d['qc']
    if 'barcode' in d:
        data['barcode']=d['barcode']

    return JsonResponse(data)




def user_label_info(user,code):   
    data = {
        'e': '',
        'code': code,
        'product': '',
        'package': '',        
        'replace': '',
        'boxes': {'pk': '', 'related': []},
        'packets': [],
        'bottles': [],
        'stock_dev': []        
    }     
    u=user
    c=u.company
    d=label_sys_info(code,c)   
    if d['e']:
        data['e']=d['e']
        return data    
    data['packets']=d['packets']
    data['bottles']=d['bottles']
    data['boxes']=d['boxes']
    #天鉴品控部 只能查询套标关系
    if u.id==4:   
        return data    
    cur=connection.cursor()
    if d['stock_dev']: 
        data['replace']=d['replace']      
        data['package']=d['package']['name'] 
        data['product']=d['product']['name'] 
        has_perm=False
        #市场稽查产品授权        
        cur.execute("select id from company_user_groups where user_id="+str(u.id)+" and group_id=1")
        r=cur.fetchone()        
        if r:            
            cur.execute("select what,content from product_authquery where user_id="+str(u.id)+" and what is not null")
            r=cur.fetchone()            
            if not r:
                has_perm=True
            else:
                if r[0]=='product':
                    a=r[1].split(',')
                    #酒鬼可以查看内参产品物流
                    if u.company_id==1:
                        s="select id from product_product where company_id=1644 and name in "+\
                            "(select name from product_product where id in  ("+r[1]+"))"
                        cur.execute(s)
                        for r1 in cur.fetchall():
                            a.append(r1[0])
                    if d['product']['id'] in a:
                        has_perm=True
                elif r[0]=='series':
                    if d['series']['id'] in r[1].split(','):
                        has_perm=True
        else:
            has_perm=True
        if has_perm:
            data['stock_dev']=d['stock_dev']
            if not d['has_outbound']:
                dd=jiugui_local116(d['boxes']['pk'])
                for rr in dd['stock_dev']:
                    if rr['voucher_type']=='销售出库':
                        data['stock_dev'].append(rr)                    
    else:
        if c.id==1:
            data['package']=d['package']['name']
            dd=jiugui_local116(d['boxes']['pk'])
            #罗秋吉
            if u.id==2:
                data['bottles']=d['bottles']
                data['boxes']=d['boxes']                
                data['stock_dev']=dd['stock_dev']
            else:
                data['bottles']=dd['bottles']
                data['boxes']=dd['boxes']                
                data['stock_dev']=dd['stock_dev']                
        else:
            data['e']=_('标签未激活')
    return data     




#2020-06-22 重写标签查询接口
def label_sys_info(code,company=None,has_price=False):
    data = {
        'e': '',
        'replace':'',
        'batch_no':'',
        'qc':'',
        'barcode':'',
        'code': code,
        'has_outbound':False,
        'stock_dev':[], 
        'packets': [],
        'bottles': [],
        'series':{'id':0,'name':''},
        'product':{'id':0,'name':'','photo':'','price':''},  
        'package':{'id':0, 'name':''},        
        'boxes': {'pk':'', 'related':[]}                         
    }        
    cur=connection.cursor()
    label_pk_id=0
    # 标签是否有被替换的情况
    s="select code0,code,dt from label.lbl_replace_details where code='" + code + "' or code0='"+code+"' order by id desc limit 1"    
    cur.execute(s)   
    rr=cur.fetchone()    
    if rr:
        code=rr[0]
        data['replace']="此标签有替换记录 "+rr[2].strftime('%Y-%m-%d')+": "+rr[0]+" -> "+rr[1]   
    if company.id in (1,1644) and code[:1]=='4':
        r = requests.request('GET', 'http://www.china315net.com:35088/jg/fw_wl/'+code+'/')
        d = json.loads(r.text)
        if d['wl']:
            code=d['wl']
            data['code']=d['wl']
        else:
            data['e']='获取物流码失败'
            return data
    #2020-04-30 有标签要查BA表 "湖北裕贡酒业销售有限公司"
    if company.id in [16716]:
        cur.execute("select code_a FROM label.lbl_abcode where code_b='"+code+"'")
        r=cur.fetchone()
        if r:
            code=r[0]    
    t="label.t" +code[:4]

    #2020-07-27 是否跨段动态绑定
    cur.execute("select tbname from label.lbl_tbcross where segment='"+code[:4]+"'")
    rrr=cur.fetchone()
    if rrr:
        t='label.'+rrr[0] 


    try:
        cur.execute("select id,package_id,(SELECT name FROM label.lbl_package where id=package_id)," + \
            "coalesce((select parent_id from " +t+ " where id=a.parent_id),parent_id) from "+t+" a where code='"+code+"'")   
        r = cur.fetchone()
        if not r:
            data['e'] = _('标签号码异常')
            return data
    except Exception as e:
        data['e'] = _('标签号码异常')
        return data
    data['package']['id']=r[1]
    data['package']['name']=r[2]
    if r[3]:
        label_pk_id=r[3]
    else:
        label_pk_id=r[0]   
    #箱标
    s="select code,"+\
        "array_to_string(array(select code from "+t+" where parent_id="+str(label_pk_id)+" and package_id=3),',') "+\
        "from "+t+" where id="+str(label_pk_id)
    cur.execute(s)
    r=cur.fetchone()
    data['boxes']['pk']=r[0]
    if r[1]:
        data['boxes']['related']=r[1].split(',')
    #是否有盒标    
    cur.execute("select id,code from "+t+" where package_id=2 and parent_id="+str(label_pk_id))
    rs=cur.fetchall()
    #有盒标，三级对应
    if rs:
        for r in rs:            
            a = []
            cur.execute("select coalesce((select code from label.lbl_replace_details where code0=a.code),a.code) " +\
                "from " + t + " a where parent_id=" + str(r[0]) + " order by code")
            for r1 in cur.fetchall():
                a.append(r1[0])
            data['packets'].append({'code': r[1], 'bottles': a})
    else:
        #箱瓶二级对应
        cur.execute("select array_to_string(array(select "+\
            "coalesce((select code from label.lbl_replace_details where code0=a.code),a.code) "+\
            "from "+t+" a where package_id=1 and parent_id="+str(label_pk_id)+"),',')")
        r=cur.fetchone()
        data['bottles']=r[0].split(',')

    #天鉴品控部 不属于任何公司，且只能查询套标关系 
    if not company:
        return data        
    #产品，公司，物流信息
    sWhere=" where label_pk_id="+str(label_pk_id)
    if company.level_id==1:
        #酒鬼可查内参物流
        if company.id==1:
            sAnd=" and ancestor_id in (1,1644)"
        else:
            sAnd=" and ancestor_id="+str(company.id)
    else:        
        sAnd=" and company_id="+str(company.id)  
    #price=" "
    price=",'' "
    if has_price:
        price=",coalesce(to_char((select price from product_product where id=a.product_id),'FM999999.90'),'') "
    s="select a.label_code, b.code, a.product_id,a.vtype_id,"+\
        "(select series_id from product_product where id=a.product_id),"+\
        "(select name from product_series where id=(select series_id from product_product where id=a.product_id)),"+\
        "(select name FROM label.lbl_package where id=a.label_pkg),"+\
        "(select name from inventory_voucher where id=a.vtype_id),"+\
        "to_char(a.tm,'yyyy-mm-dd HH24:MI:SS'),"+\
        "(select username from company_user where id=b.user_id),"+\
        "(select name from product_product where id=a.product_id),"+\
        "coalesce((select name from inventory_storehouse where id=a.store_id),''),"+\
        "case when a.vtype_id=3 then coalesce((select name from  inventory_storehouse where id=a.store_fr_id),'') else '' end,"+\
        "coalesce((select name from company_company where id=a.company_to_id),''),"+\
        "coalesce((select photo from product_product where id=a.product_id),''),"+\
        "coalesce((select barcode from product_product where id=a.product_id),''),"+\
        "b.code,"+\
        "b.qc "+\
        price +\
        "from "+\
        "(SELECT label_code,vtype_id,batch_id,tm,product_id,store_id,store_fr_id,company_to_id,label_pkg"+\
           " from inventory_stkdetails"+sWhere+sAnd+" order by company_id,vtype_id,dt) a "+\
        "left outer join "+\
        "(select id,code,user_id,coalesce(qc_name,'') qc from inventory_batch) b "+\
        "on a.batch_id=b.id "+\
        "order by a.tm"    


    
    cur.execute(s)    
    for r in cur.fetchall():   
        if r[3]==1:
            data['product']['id']=r[2]
            data['product']['name']=r[10]
            data['product']['photo']=r[14]            

            #2020-11-12
            data['barcode']=r[15]  
            data['qc']=r[16]
            data['batch_no']=r[17]

            data['product']['price']=r[18]

            data['series']['id']=r[4]
            data['series']['name']=r[5]
            
        elif not data['product']['id']:
            data['product']['id']=r[2]
            data['product']['name']=r[10]
            data['product']['photo']=r[14]
            data['product']['price']=r[15]
            data['series']['id']=r[4]
            data['series']['name']=r[5]   
        if r[3]==2:
            data['has_outbound']=True
        i={
            'label_code': r[0],
            'voucher_no': r[1],
            'package': r[6],
            'voucher_type': r[7],            
            'time': r[8],
            'user': r[9],
            'product': r[10],
            'store': r[11],
            'store_fr': r[12] ,            
            'to_company': r[13]            
        }
        if has_price:
            if r[14]:
                i['price']=str(r[15])
            else:
                i['price']=''
        data['stock_dev'].append(i)    
    return data
   


#2020-06-28
@tj_login_required
def get_label_sys_info(request, code):   
    data = {
        'e': '',
        'code': code,
        'product': '',
        'package': '',        
        'replace': '',
        'boxes': {'pk': '', 'related': []},
        'packets': [],
        'bottles': [],
        'stock_dev': []        
    }     
    user=request.user
    data=user_label_info(user,code)   
    return JsonResponse(data) 


#2020-06-28
def wx_user_label_info(request, user_id, code):   
    data = {
        'e': '',
        'code': code,
        'product': '',
        'package': '',        
        'replace': '',
        'boxes': {'pk': '', 'related': []},
        'packets': [],
        'bottles': [],
        'stock_dev': []        
    }     
    user=User.objects.get(id=int(user_id))
    data=user_label_info(user,code)   
    return JsonResponse(data)





#2020-05-21 酒鬼酒，若新平台没有发货记录，查询116本地发货记录
def jiugui_delivery(box_code):
    delivery = {}
    url="http://davis.china315net.com:8118/wuliu/jiugui/delivery/?code="+box_code
    r = requests.get(url)
    if r.status_code == requests.codes.ok:
        delivery=r.json()   
    return delivery


@csrf_exempt
def fg_update(request,is_app=False):
    """
    新物流系统，产成品入库，出库处理
    :param request: json
    :return: json
    """
    data = {'e': '', 'cnt': 0, 'rebate':{}}
    k = sK
    r = json.loads(request.body)
    e = time_key_check(request, k)
    if e:
        data['e'] = e
        return JsonResponse(data)
    keys = ['bill_number', 'user_id', 'company_id', 'voucher_id', 'voucher_time']
    for k in keys:
        if k not in keys:
            data['e'] = _('缺少参数') + k
            return JsonResponse(data)
    """单据类别：voucher id
    1;"成品入库单";"GRN";1
    2;"销售出库单";"GDN";-1
    3;"成品转库单";"GTN";0
    4;"销售退库单";"GWN";1
    5;"入库单退库";"GCN";-1
    """
    # 必传：单号，用户ID，公司ID, 单据类别ID, 单据生成时间
    code = r['bill_number']
    # hdh+"|" + yyyyMMddHHmmss  比如：8785454567|20190528220956  |号前是交货单号，后面是一个年月日时分秒的虚拟单号
    code = code.split('|')[0]

    usr_id = int(r['user_id'])
    cmp_id = int(r['company_id'])
    vch_id = int(r['voucher_id'])
    
    #2020-10-30
    qc=None
    if 'qc' in r:
        qc=r['qc']
    #生产批次号
    batch_no=None
    if 'batch_no' in r:        
        batch_no=r['batch_no']
    else:
        if vch_id==1:
            batch_no=code


    vch_tm = r['voucher_time']
    #2020-08-29 是否强制发货
    force=False
    if 'force' in r:
        force=r['force']

    # 扫码枪传过来的voucher_time，不可靠
    vch_tm = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")

    vch_dt = vch_tm[:10]
    c = Company.objects.get(id=int(cmp_id))
    voucher = Voucher.objects.get(id=int(vch_id))    
    #m = apps.get_model('inventory', voucher.code)
    batch = Batch()    
    batch.code = code    
    batch.company_id=c.id
    batch.company_fr_id = c.id
    batch.level_id=c.level_id
    batch.hierarchy_id=c.hierarchy_id
    batch.version=c.version   
    batch.user_id = int(usr_id)   
    batch.ancestor_id=c.ancestor_id
    
    #2020-10-30
    batch.qc_name=qc
    batch.batch_no=batch_no

    #2020-09-17       
    if 'lnglat_start' in r:
        batch.lnglat_start=r['lnglat_start']
    if 'lnglat_end' in r:
        batch.lnglat_end=r['lnglat_end']
    if 'distance' in r:
        batch.distance=r['distance']
    if 'storegeo_id' in r:
        batch.storegeo_id=r['storegeo_id']


    # 写入到delivery
    prd_id = 'null'
    sto_id = 'null'
    agt_id = 'null'

    # 成品入库，号码唯一
    label_unique=''
    batch.voucher_id = voucher.id
    batch.vtype_id=int(vch_id)   
   
    
    #2020-05-30
    STRICT=True         #直营店要扫码入库
    if vch_dt[:7]=='2020-06' and c.ancestor_id==1644:
        STRICT=False    #扫码出库时，若直营店未扫码入库，系统补一笔入库


    # 有没有传产品ID；转库有没有from_store_id
    store_fr_id_o = 0
    if 'from_store_id' in r:
        store_fr_id_o = int(r['from_store_id'])
        if vch_id == 3 and store_fr_id_o:            
            batch.store_fr_id = store_fr_id_o
    product_id_o = None
    if 'product_id' in r:
        if int(r['product_id'])>0:
            product_id_o=int(r['product_id'])
          
    batch.product_id = product_id_o
    if vch_id == 1:         # 产成品入库
        # 成品入库，号码唯一
        #label_unique=" and (select id from logistics.inventory_stkdetails " + \
        #             "where voucher_type='grn' and label_code=a.label_code and company_id=" + str(c.id) + " ) is null"        

        batch.store_fr_id = int(r['store_id'])
        if c.has_plant:      
            #2020-04-21 超鹏把plant_id写死了，使用的是gongchang_id
            if 'gongchang_id' in r:
                batch.prd_plant_id = int(r['gongchang_id'])
            else:
                batch.prd_plant_id = int(r['plant_id'])     

        if c.has_workshop:           
            batch.prd_workshop_id = int(r['workshop_id'])
        if 'qc_id' in r:           
            batch.qc_id = int(r['qc_id'])
        if 'batch_no' in r:           
            batch.batch_no = r['batch_no']
        if 'workline_id' in r:           
            batch.prd_line_id = r['workline_id']
    elif vch_id == 2:       # 销售出库
        sto_id = r['store_id'] 
        if sto_id:
            batch.store_fr_id = int(sto_id)
        agt_id = r['to_company_id']       
        batch.company_to_id = int(agt_id)
        #2020-05-28 微信小程序，销售出库给团购，①单位名称，②姓名，③电话。        
        if is_app and int(agt_id)==11014:  
            batch.customer_company=r['company']                 
            batch.customer_name=r['name']                 
            batch.customer_phone=r['phone']
    elif vch_id == 3:       # 转库        
        batch.store_to_id = int(r['store_id'])
    elif vch_id == 4:       # 销售退库       
        batch.store_fr_id = int(r['store_id'])
    elif vch_id == 5:       # 标签退库
        if 'store_id' in r:            
            batch.store_fr_id = int(r['store_id'])
    
    if not batch.store_fr_id:
        batch.store_fr_id=None
    batch.save()
    b_id = str(batch.id)    
    codes = r['codes']
    rec_cnt = len(codes)
    a = []
    cur = connection.cursor()
    #2020-10-19
    cur.execute("create temp table codes(label_code character varying(20),tb character varying(20),product_id integer,"+\
        "code character varying(20),label_id bigint,label_pk_id bigint,label_pkg smallint,err character varying(100))")
    for code in codes:
        #物流终端发货CODES
        #codes: [{code: 123456789012,  err_code: 1,  e: "错误信息1"}]
        if is_app:  
            e='null'
            pid='null'
            #if code['product_id']:
            if 'product_id' in code:
                if int(code['product_id'])>0:
                    pid=str(code['product_id'])   
            elif batch.product_id:
                pid=str(batch.product_id)
            if code['e']:
                e="'"+code['e'][:100]+"'"
            a.append("('"+code['code']+"','"+code['code'][:4]+"',"+e+","+pid+")")
            sSql="insert into codes (label_code,tb,err,product_id) values " + ",".join(a)
        else:            
            a.append("('" + code + "','" + code[:4] + "',"+str(batch.product_id)+")")
            sSql="insert into codes (label_code,tb,product_id) values " + ",".join(a)
    
    
    cur.execute(sSql)
    #处理标签替换
    s="update codes set code="+\
        "coalesce((select code from label.lbl_replace_details where code0=label_code),label_code)"
    cur.execute(s)


    #2020-07-27 处理跨段动态绑定
    s="update codes set tb=b.tbname from "+\
        "(SELECT tb,label_code from codes) a "+\
        "left outer join "+\
        "(SELECT segment, tbname FROM label.lbl_tbcross) b "+\
        "on a.tb=b.segment "+\
        "where  codes.label_code=a.label_code and b.tbname is not null"
    cur.execute(s)
    cur.execute("update codes set tb='t'||left(code,4) where length(tb)=4")    

    cur.execute("select tb from codes group by tb")
    rs = cur.fetchall()
    for r in rs:  
        cur.execute("select to_regclass('label." + r[0] + "')")
        r0 = cur.fetchone()
        if r0[0]:
            s="update codes set "+\
                "label_id=b.id,"+\
                "label_pkg=b.package_id,"+\
                "label_pk_id=coalesce(b.parent_id,b.id) "+\
                "from "+\
                "(SELECT label_code,code from codes where tb='"+r[0]+"') a "+\
                "left outer join "+\
                "(select id,code,parent_id,package_id,is_pk from label."+r[0]+") b "+\
                "on a.label_code=b.code "+\
                "where tb='"+r[0]+"' and codes.code=a.code"
            cur.execute(s)   

    #2020-10-19
    s="insert into logistics.inventory_batchdetails(batch_id,label_code,label_id,label_pkg,tm,label_pk_id,company_id,ancestor_id,err,product_id) " +\
        "select "+str(batch.id)+",code,label_id,label_pkg,now(),label_pk_id,"+str(c.id)+","+str(c.ancestor_id)+",err,product_id from codes"
    cur.execute(s)

    cnt = cur.rowcount   


    store_fr = "null"
    d = 'false'
    if product_id_o:
        product = str(product_id_o)
    else:        
        product = "(select product_id from logistics.inventory_stkdetails where vtype_id in (1,2) " + \
            " and label_pk_id=a.label_pk_id and product_id is not null order by id desc limit 1)"  
    if vch_id == 5:     # 标签退库
        d = "true"
        store = "(select store_id from logistics.inventory_stkdetails where voucher_type='grn' " + \
            "and  label_code=a.label_code and store_id is not null order by id desc limit 1)"
    elif vch_id == 3:    # 成品转库
        store = str(batch.store_to_id)        
        store_fr = "coalesce(" + str(store_fr_id_o) + "," + \
                "(select store_id from logistics.inventory_stkdetails where voucher_type='gtn' " + \
                "and  label_code=a.label_code and store_id is not null order by id desc limit 1)," + \
                "(select store_id from logistics.inventory_stkdetails where voucher_type='grn' " + \
                "and  label_code=a.label_code and store_id is not null order by id desc limit 1))" 
    else:
        if batch.store_fr_id:
            store = str(batch.store_fr_id)
        else:
            store='null'    

    cur.execute("update logistics.inventory_batchdetails set err='标签错误'  where batch_id="+b_id+" and label_id is null")
    #2020-05-01 无论是微信小程序还是扫码枪，入库和出库，同一箱号只能有一条记录    
    #if vch_id in [1,2]:
    
    #2020-11-09,内参小程序新模式：扫了瓶标，可以扫箱标，置换出瓶标(数据检查已在小程序端完成)    
    if batch.ancestor_id==1644 and is_app:
        pass
    elif vch_id in [1,2]:
        s=''
        rmk=voucher.remark  
        if vch_id==2:
            if not force:
                s="update logistics.inventory_batchdetails b set err='此标签已于'||("+\
                    "select to_char(tm,'yyyy-mm-dd HH24:MI:SS') from logistics.inventory_stkdetails where "+\
                        "vtype_id="+str(vch_id)+" and company_id="+str(c.id)+\
                        " and label_code=b.label_code  limit 1)||'"+rmk+"' "+\
                    " where batch_id="+b_id+" and err is null "\
                    " and (select tm from logistics.inventory_stkdetails where "+\
                        "vtype_id="+str(vch_id)+" and company_id="+str(c.id)+\
                        " and label_code=b.label_code limit 1) is not null"
        elif vch_id==1:
            #2020-11-25    酒鬼扫码非实时上传，再次入库可能早于标签退库，先不做重复入库检查            
            s=''
            
            #s="update logistics.inventory_batchdetails b set err='此标签已于'||("+\
            #    "select to_char(tm,'yyyy-mm-dd HH24:MI:SS') from logistics.inventory_stkdetails where "+\
            #        "vtype_id="+str(vch_id)+" and company_id="+str(c.id)+\
            #        " and label_code=b.label_code order by id desc limit 1)||'"+rmk+"' "+\
            #    " where batch_id="+b_id+" and err is null "\
            #    " and (select tm from logistics.inventory_stkdetails where "+\
            #        "vtype_id="+str(vch_id)+" and company_id="+str(c.id)+\
            #        " and label_code=b.label_code limit 1) is not null "+\
            #    " and (select id from logistics.inventory_stkdetails where "+\
            #        "label_code=b.label_code and is_delete=true limit 1) is null"
        if s:
            cur.execute(s)
            

    # 写入进出存明细
    if vch_id == 2:     # 销售出库单
        sql = "INSERT INTO logistics.inventory_stkdetails (company_to_id,is_delete,dt,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 " + \
            str(batch.company_to_id) + "," + d + ",'" + vch_dt + "',label_code,"+b_id+"," + \
            str(c.id) + ", product_id," + store + "," + store + ",label_id," + \
            "label_pkg,"+str(vch_id)+",label_pk_id,"+str(c.ancestor_id)+" from logistics.inventory_batchdetails a where batch_id=" + b_id + \
            " and product_id is not null and err is null"
    elif vch_id==3:     # 成品转库
        sql = "INSERT INTO logistics.inventory_stkdetails (is_delete,dt,label_code,batch_id," + \
            "company_id,product_id,store_fr_id,store_id,store_to_id,label_id,label_pkg,vtype_id,label_pk_id,ancestor_id) select " + \
            d + ",'" + vch_dt + "',label_code,"+b_id+"," +\
            str(c.id) + "," + product + "," + store_fr + "," +store + "," +  store + ",label_id," + \
            "label_pkg,"+str(vch_id)+",label_pk_id,"+str(c.ancestor_id)+" from logistics.inventory_batchdetails a where batch_id=" + b_id + \
            " and " + product + " is not null and " +  store + " is not null" + label_unique + " and err is null"
    
    elif vch_id==5:
        sql = "INSERT INTO logistics.inventory_stkdetails (is_delete,dt,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 " + \
            "true,'" + vch_dt + "',label_code,"+b_id+"," +\
            str(c.id) + "," + product + "," + store + "," +  store + ",label_id," + \
            "label_pkg,"+str(vch_id)+",label_pk_id,"+str(c.ancestor_id)+" from logistics.inventory_batchdetails a where batch_id=" + b_id + \
            " and " + product + " is not null " +  label_unique   + " and err is null"
    else:
        sql = "INSERT INTO logistics.inventory_stkdetails (is_delete,dt,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 " + \
            d + ",'" + vch_dt + "',label_code,"+b_id+"," +\
            str(c.id) + ",product_id," + store + "," +  store + ",label_id," + \
            "label_pkg,"+str(vch_id)+",label_pk_id,"+str(c.ancestor_id)+" from logistics.inventory_batchdetails a where batch_id=" + b_id + \
            "  and product_id>0 " + label_unique + " and err is null"

    #f=open('d:\\test_update.txt','w')
    #f.write(sql)
    #f.close()


    cur.execute(sql)
    if rec_cnt == cur.rowcount:
        batch.dt_handled = timezone.now()
        batch.tm_handled = timezone.now()
        batch.save()
    data['cnt'] = cnt

    if vch_id==2 and batch.product_id in settings.TY_PRODUCTS_ID:
        #is_test=True or False
        delivery_ty(batch.id,False)  

    

    #入库退标，对原入库标签做删除标记(一箱可能贴2个标签，即入库扫的是一枚标，退库时可能是同一箱的另一枚)
    if vch_id==5:                 
        s="update inventory_stkdetails set is_delete=true where vtype_id=1 and "+\
            "label_pk_id in (select label_pk_id from logistics.inventory_batchdetails where batch_id="+b_id+")"
        cur.execute(s)    
        
        #2020-05-01 酒鬼酒公司:红坛酒退标，删除19.9跳转和奖项记录
        if cmp_id==1 and batch.id in settings.JG_HT_GR:          
            bts=[]
            s="select left(label_code,4) from inventory_stkdetails where batch_id="+b_id+"group by left(label_code,4)"
            cur.execute(s)
            rs00=cur.fetchall()
            for r00 in rs00:
                cur.execute("select code from label.t"+r00[0]+" where package_id=1 and parent_id in  ("+\
                    "select label_pk_id from inventory_stkdetails where batch_id="+b_id+")")
                for r000 in cur.fetchall():
                    bts.append("'"+r000[0]+"'")
            if bts:
                cn_al=pyodbc.connect(settings.CN_AL)  
                cn_xa=pyodbc.connect(settings.CN_XA)
                cur_al=cn_al.cursor()
                cur_xa=cn_xa.cursor()
                #跳转                
                cur_al.execute("delete from TJ_JG_Activity where labelcode in ("+",".join(bts)+")")       
                cn_al.commit()
                #奖项
                cur_xa.execute("delete from  TB_BaseLabelInfo where LabelCode in ("+",".join(bts)+")") 
                cn_xa.commit()

                cur_al.close()
                cn_al.close()
                cur_xa.close()            
                cn_xa.close()   
    elif vch_id==2:     
        #内参发货一级经销商，发货直接作为一级经销商入库
        #if c.id==1644:
        #2020-04-25修改为
        #公司发货一级经销商，发货直接作为一级经销商入库(酒鬼除外）

        #2020-07-17
        cfgs=CompanyCfg.objects.filter(company_id=c.id)
        auto_inbound=False
        if cfgs:        
            cfg=cfgs[0]
            if cfg.inbound_auto_lv1:
                auto_inbound=True
        #if c.level_id==1 and c.id!=1:
        if auto_inbound: 
            #batch
            b=Batch(vtype_id=1,level_id=2,hierarchy_id=1,version=1,company_id=int(agt_id),company_fr_id=c.id,code=batch.code,
                reference_id=batch.id, product_id=batch.product_id,user_id=int(usr_id),ancestor_id=c.ancestor_id)
            b.save()
            #batchdetails
            sql="insert into logistics.inventory_batchdetails(batch_id,label_code,label_id,label_pkg,label_pk_id,company_id,ancestor_id) select "+\
                str(b.id)+",label_code,label_id,label_pkg,label_pk_id,"+str(agt_id)+",company_id from logistics.inventory_batchdetails where batch_id="+b_id
            cur.execute(sql)
            #stkdetails
            sql = "INSERT INTO logistics.inventory_stkdetails (dt,label_code,batch_id," + \
                "company_id,product_id,label_id,label_pkg,vtype_id,label_pk_id,ancestor_id) select '" + \
                vch_dt + "',label_code,"+str(b.id)+"," + str(agt_id) + "," + product + ", label_id," + \
                "label_pkg,1,label_pk_id,"+str(c.ancestor_id)+" from inventory_batchdetails a where batch_id=" + str(b.id) + \
                " and label_id is not null"         
            cur.execute(sql)
            b.dt_handled = timezone.now()
            b.tm_handled = timezone.now()
            b.save()
        #2020-06-10，取消直营店自动补入库，改为先入库再出库
        #扫码出库时，若直营店未扫码入库，系统补一笔入库 level_id:1,公司；2,平台商，4：终端店（直营店）
        #elif c.level_id>2 and STRICT==False:            
        #    s="select id from inventory_stkdetails a where batch_id="+str(batch.id)+" and (select id from inventory_stkdetails"+\
        #        " where company_id="+str(c.id)+" and vtype_id=1 and label_code=a.label_code) is null limit 1"  
        #    cur.execute(s)
        #    rs_add=cur.fetchone()
        #    if rs_add:  
        #        b=Batch(vtype_id=1,level_id=c.level_id,hierarchy_id=c.hierarchy_id,version=0,company_id=c.id,company_fr_id=c.parent_id,
        #            code=batch.code,product_id=batch.product_id,user_id=int(usr_id),ancestor_id=c.ancestor_id)
        #        b.save()                
        #        a_id=str(c.ancestor_id)
        #        p_id=str(c.parent_id)
        #        c_id=str(c.id)
        #        #batchdetails
        #        sql="insert into inventory_batchdetails(batch_id,label_code,label_id,label_pkg,label_pk_id,company_id,ancestor_id) select "+\
        #            str(b.id)+",label_code,label_id,label_pkg,label_pk_id,"+c_id+","+a_id+" from inventory_batchdetails a "+\
        #            "where batch_id="+b_id+" and (select id from inventory_stkdetails where "+\
        #                " company_id="+c_id+" and vtype_id=1 and label_code=a.label_code) is null"
        #        cur.execute(sql)
        #        #stkdetails
        #        sql = "INSERT INTO logistics.inventory_stkdetails (dt,label_code,batch_id," + \
        #            "company_id,product_id,label_id,label_pkg,vtype_id,label_pk_id,ancestor_id) select '" + \
        #            vch_dt+"',label_code,"+str(b.id)+","+c_id+","+product+",label_id,label_pkg,1,label_pk_id,"+a_id+\
        #            " from inventory_batchdetails a where batch_id="+str(b.id)+" and label_id is not null"                             
        #        cur.execute(sql)
        #        b.dt_handled = timezone.now()
        #        b.tm_handled = timezone.now()
        #        b.save()   
        #酒鬼公司发给内参，发货直接作为内参入库
        elif c.id==1 and agt_id==7212:           
            sto=None
            if int(sto_id)==37:
                sto=45
            elif int(sto_id)==47:
                sto=46
            else:
                #默认吉首仓库
                sto=45
            #batch
            b=Batch(vtype_id=1,level_id=1,hierarchy_id=1,version=1,company_id=1644,company_fr_id=1,store_fr_id=sto,
                code=batch.code,product_id=batch.product_id,user_id=int(usr_id),ancestor_id=1644)
            b.save()           
            #batchdetails
            sql="insert into logistics.inventory_batchdetails(batch_id,label_code,label_id,label_pkg,label_pk_id,company_id) "+\
                "select "+str(b.id)+",label_code,label_id,label_pkg,label_pk_id,company_id from logistics.inventory_batchdetails where batch_id="+b_id
            cur.execute(sql)
            
            #stkdetails
            if not sto:
                sto='null'
            else:
                sto=str(sto)
            sql = "INSERT INTO logistics.inventory_stkdetails (dt,label_code,batch_id," + \
                "company_id,product_id,label_id,label_pkg,vtype_id,label_pk_id,ancestor_id,store_id,store_fr_id) select '" + \
                vch_dt + "',label_code,"+str(b.id)+","+ str(agt_id) + ",product_id, label_id," + \
                "label_pkg,1,label_pk_id,"+str(agt_id)+","+sto+","+sto+" from logistics.inventory_batchdetails a where batch_id=" + b_id + \
                " and label_id is not null"         
            cur.execute(sql)
            b.dt_handled = timezone.now()
            b.tm_handled = timezone.now()
            b.save()   

    
    #2020-09-16 层级返利
    if c.level.sn>0 and is_app:      
        #try:    
            data['rebate']=channel_rebate(batch)            
        #except:
        #    pass
    return JsonResponse(data)


@csrf_exempt
def legacy_delivery(request):
    data = {'e': '', 'cnt': 0}
    # 2019-05-25,发货机器人有时发空请求过来
    if not request.body:
        return JsonResponse(data)
    try:
        r = json.loads(request.body)
    except Exception:
        return JsonResponse(data)
    k = sK
    fh_tm = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    pro_id = r['product_id']
    if 'fh_time' in r:
        fh_tm = r['fh_time']
    type_id = r['type']
    usr_id = r['user_id']
    sto_id = r['store_id']
    fr_cid = r['fr_company_id']
    to_cid = r['to_company_id']
    pici = r['fhpici']
    fhkey = r['fhkey']
    cnt = str(r['xiang'])
    codes = r['codes']
    if not codes:
        return JsonResponse(data)
    beizhu = 'null'
    khdd = 'null'
    gpsinfo = 'null'
    sclocal = 'null'
    if r['beizhu']:
        beizhu = "'" + r['beizhu'] + "'"
    if r['sclocal']:
        sclocal = "'" + r['sclocal'] + "'"
    if r['gpsinfo']:
        gpsinfo = "'" + r['gpsinfo'] + "'"
    if r['khdd']:
        khdd = "'" + r['khdd'] + "'"
    rec_cnt = len(codes)
    e = time_key_check(request, k)
    if e:
        data['e'] = e
        return JsonResponse(data)
    if int(type_id) not in (2, 4):
        data['e'] = '单据类别只能是销售出库单或销售退库单'
        return JsonResponse(data)    
    cur = connection.cursor()
    cur.execute("insert into logistics.delivery_summary (fhpici,fhkey,gpsinfo,khddh,proid,stid,fhuserid,compid," +
                "agentid,xiangnumber,voucher_id,beizhu,sclocal,fhdate" +
        ") values (" +
            "'" + pici + "','" + fhkey + "'," + gpsinfo + "," + khdd + "," + str(pro_id)+"," + str(sto_id) + "," +
            str(usr_id) + "," + str(fr_cid) + "," + str(to_cid) + "," + str(cnt) + "," + str(type_id) + "," +
            beizhu + "," + sclocal + ",'" + fh_tm + "') returning id")
    r = cur.fetchone()
    sum_id = str(r[0])
    cur1 = connection.cursor()
    cur1.execute("create temp table codes (label_code character varying(20),tb character varying(20),label_id bigint)")
    a = []
    for code in codes:
        a.append("('" + code + "','" + code[:4] + "')")
    cur1.execute("insert into codes (label_code,tb) values " + ",".join(a))
    cur1.execute("select tb from codes group by tb")
    rs = cur1.fetchall()
    for r in rs:
        cur.execute("select to_regclass('label.t" + r[0] + "')")
        r0 = cur.fetchone()
        if r0[0]:
            cur1.execute("update codes set label_id=" +
                         "(select id from label.t" + r[0] + " where code=label_code) where tb='" + r[0] + "'")    
    s = "insert into delivery_details(summary_id,label_code,label_id) select " + \
        sum_id + ",label_code,coalesce(label_id,0) from codes"
    cur.execute(s)
    data['cnt'] = cur.rowcount   
    return JsonResponse(data)




#2019-11-16 出人库列表    
#@login_required
@tj_login_required
def stock_list(request,company_id=None):
    if not company_id:
        company_id=str(request.user.company.id)
    dt = datetime.datetime.now().strftime('%Y-%m-%d')
    dt_fr = request.GET.get('date_fr', dt)
    dt_to = request.GET.get('date_to', dt)
    dt_max = max(dt_fr, dt_to)
    dt_min = min(dt_fr, dt_to)
    dt_fr = dt_min
    dt_to = dt_max
    store_id = request.GET.get("store_id", "")
    product_id = request.GET.get("product_id", "")
    store_label = request.GET.get("store_label", "")
    product_label = request.GET.get("product_label", "")
    params = {
        'dt_fr': dt_fr,
        'dt_to': dt_to,
        'store_id': store_id,
        'product_id': product_id,
        'store_label': store_label,
        'product_label': product_label
    }
    sStore = ""
    sProduct = ""
    sFsto=""
    if store_id:
        sFsto = " and store_to_id=" + str(store_id)
        sStore = " and store_fr_id=" + str(store_id)
    if product_id:
        sProduct = " and product_id=" + str(product_id)
    sWhere = " where company_id=" + company_id + " and (dt between '" + dt_fr + "' and '" + dt_to + "')"
    data = {'e': '', 'data': [], 'stores': [], 'products': []}
    cur = connection.cursor()
    for o in Storehouse.objects.filter(company_id=int(company_id)).order_by('name'):
        data['stores'].append({'label': o.name, 'value': o.id})
    for o in Product.objects.filter(company_id=int(company_id)).order_by('name'):
        data['products'].append({'label': o.name, 'value': o.id})
    """
    单据类别：voucher id
    1;"成品入库单";"GRN";1
    2;"销售出库单";"GDN";-1
    3;"成品转库单";"GTN";0
    4;"销售退库单";"GWN";1
    5;"入库单退库";"GCN";-1
    """
    s = "select (select name from logistics.inventory_storehouse where id=l.store_id) store," + \
        "(select name from logistics.product_product where id=l.product_id) product," + \
        "coalesce(grn.qty,0),coalesce(gcn.qty,0),coalesce(gtn_to.qty,0)," + \
        "coalesce(gtn_fr.qty,0),coalesce(gdn.qty,0),coalesce(gwn.qty,0) from " + \
        "(select a.product_id product_id,a.store_id store_id from (" + \
            "select product_id,store_fr_id store_id from logistics.inventory_batch " + sWhere + sStore + sProduct + \
                " and store_fr_id is not null group by product_id,store_fr_id"+\
            " union " + \
            "select product_id,store_to_id store_id from logistics.inventory_batch" + sWhere + sFsto + sProduct + \
                " and store_to_id is not null group by product_id,store_to_id) a) l " + \
        "left outer join " +\
        "(select product_id,store_id,count(*) qty from logistics.inventory_stkdetails" + sWhere + sStore + sProduct + \
            " and vtype_id=1  and is_delete=false group by product_id,store_id) as grn " + \
        "on l.product_id=grn.product_id and l.store_id=grn.store_id " + \
        "left outer join " + \
        "(select product_id,store_id,count(*) qty from logistics.inventory_stkdetails" + sWhere + sStore + sProduct + \
            " and vtype_id=2 group by product_id,store_id) as gdn " + \
        "on l.product_id=gdn.product_id and l.store_id=gdn.store_id " + \
        "left outer join " + \
        "(select product_id,store_id,count(*) qty from logistics.inventory_stkdetails" + sWhere + sStore + sProduct + \
            " and vtype_id=5 group by product_id,store_id) as gcn " + \
        "on l.product_id=gcn.product_id and l.store_id=gcn.store_id " + \
        "left outer join " + \
        "(select product_id,store_id,count(*) qty from logistics.inventory_stkdetails" + sWhere + sStore + sProduct + \
            " and vtype_id=4 group by product_id,store_id) as gwn " + \
        "on l.product_id=gwn.product_id and l.store_id=gwn.store_id " + \
        "left outer join " + \
        "(select product_id,store_to_id store_id,count(*) qty from logistics.inventory_stkdetails" + sWhere + sFsto + sProduct + \
            " and vtype_id=3 and store_to_id is not null group by product_id,store_to_id) as gtn_to " + \
        "on l.product_id=gtn_to.product_id and l.store_id=gtn_to.store_id " + \
        "left outer join " + \
        "(select product_id,store_fr_id store_id,count(*) qty from logistics.inventory_stkdetails" + sWhere + sStore + sProduct +\
            " and vtype_id=3 and store_fr_id is not null group by product_id,store_fr_id) as gtn_fr " + \
        "on l.product_id=gtn_fr.product_id and l.store_id=gtn_fr.store_id"   


    
    cur.execute(s)
    for r in cur.fetchall():
        data['data'].append([r[0], r[1], r[2], r[3], r[4], r[5], r[6], r[7],])    
    
    return render(request, 'inventory/stock/stock_list.html', {        
        'stores': SafeString(data['stores']),
        'products': SafeString(data['products']),
        'data':SafeString(data['data']),
        'e': data['e'],
        'params': params
    })


#2019-11-16 入库，销售，转库，退库，退标    
#@login_required
@tj_login_required
def batch_list(request,obj,company_id=None):
    if not company_id:
        company_id=str(request.user.company.id)
    dt = datetime.datetime.now().strftime('%Y-%m-%d')
    dt_fr = request.GET.get('date_fr', dt)
    dt_to = request.GET.get('date_to', dt)
    dt_max = max(dt_fr, dt_to)
    dt_min = min(dt_fr, dt_to)
    dt_fr = dt_min
    dt_to = dt_max
    store_id = request.GET.get("store_id", "")
    product_id = request.GET.get("product_id", "")
    store_label = request.GET.get("store_label", "")
    product_label = request.GET.get("product_label", "")
    
    agent_id=request.GET.get("agent_id","")
    agent_label=request.GET.get("agent_label","")
    params = {
        'dt_fr': dt_fr,
        'dt_to': dt_to,
        'store_id': store_id,
        'product_id': product_id,
        'store_label': store_label,
        'product_label': product_label
    }     
    v=None
    sStore=""
    e=""
    sProduct=""
    if store_id:
        sStore=" and store_fr_id="+store_id
    if product_id:
        sProduct=" and product_id="+product_id
    vs=Voucher.objects.filter(code=obj.upper())
    data=[]
    stores=[]
    products=[]
    if not vs:
        e=_('单据类别错误')
    else:
        #2020-12-01
        saldiv_rpt=False
        try:
            if request.user.company.cfg_company:
                saldiv_rpt=request.user.company.cfg_company.saldiv_report
        except Exception:
            pass
        v=vs[0]
        vid=str(v.id)
        sWhere=" where company_id="+company_id+" and vtype_id="+vid+\
            " and (dt between '"+dt_fr+"' and '"+dt_to+"')" 
        sDel=''
        if v.id==1:            
            sDel=" and is_delete=false"
        dataset=[]       
        for o in Storehouse.objects.filter(company_id=int(company_id)).order_by('name'):
            stores.append({'label': o.name, 'value': o.id})
        for o in Product.objects.filter(company_id=int(company_id)).order_by('name'):
            products.append({'label': o.name, 'value': o.id})     
        s="select b.tm,b.code,"+\
            "coalesce((select name from product_product where id=b.product_id),''),"+\
            "coalesce((select name from inventory_storehouse where id=b.store_fr_id),''),"+\
            "coalesce((select username from company_user where id=b.user_id),''),"+\
            "coalesce(d.x,0),coalesce(d.h,0),coalesce(d.p,0),"+\
            "case when b.customer_company is null then coalesce((select name from company_company where id=b.company_to_id),'')"+\
                " else '团购('||customer_name||' '||b.customer_phone||' '||b.customer_company||')' end,"+\
            "coalesce((select name from inventory_storehouse where id=b.store_to_id),''),"+\
            "b.id,"+\
            "coalesce((select name from company_prdplant where id=b.prd_plant_id),''), "+\
            "b.qc_name "+\
            " from "+\
            "(select id,tm,coalesce(code,'') code,user_id,product_id,store_fr_id,store_to_id,company_to_id,prd_plant_id,"\
                "customer_company,customer_phone,customer_name,qc_name from inventory_batch "+sWhere+sProduct+sStore+") b "+\
            "left outer join "+\
            "(select a.batch_id,sum(a.x) x,sum(a.h) h,sum(a.p) p from "+\
                "(select batch_id,"+\
                    "case when label_pkg in (3,4) then 1 else 0 end x,"+\
                    "case when label_pkg=2        then 1 else 0 end h,"+\
                    "case when label_pkg=1        then 1 else 0 end p "+\
                    "from inventory_stkdetails"+sWhere+sProduct+sStore+sDel+\
                ") a group by a.batch_id"+\
            ") d "+\
            "on b.id=d.batch_id"     
        #如内参公司，销售出库要显示战区分区
        if vid=='2' and saldiv_rpt:
            s="select b.tm,b.code,"+\
                "coalesce((select name from product_product where id=b.product_id),''),"+\
                "coalesce((select name from inventory_storehouse where id=b.store_fr_id),''),"+\
                "coalesce((select username from company_user where id=b.user_id),''),"+\
                "coalesce(d.x,0),coalesce(d.h,0),coalesce(d.p,0),"+\
                "case when b.customer_company is null then coalesce((select name from company_company where id=b.company_to_id),'')"+\
                    " else '团购('||customer_name||' '||b.customer_phone||' '||b.customer_company||')' end,"+\
                "coalesce((select name from inventory_storehouse where id=b.store_to_id),''),"+\
                "b.id,"+\
                "coalesce((select name from company_prdplant where id=b.prd_plant_id),''), "+\
                "b.qc_name, "+\
                "coalesce((select name from company_salesdivision where id=c.division_id),''),"+\
                "coalesce((select name from company_subsaldivision where id=c.sub_division_id),'') "+\
                " from "+\
                "(select id,tm,coalesce(code,'') code,user_id,product_id,store_fr_id,store_to_id,company_to_id,prd_plant_id,"\
                    "customer_company,customer_phone,customer_name,qc_name from inventory_batch "+sWhere+sProduct+sStore+") b "+\
                "left outer join "+\
                "(select a.batch_id,sum(a.x) x,sum(a.h) h,sum(a.p) p from "+\
                    "(select batch_id,"+\
                        "case when label_pkg in (3,4) then 1 else 0 end x,"+\
                        "case when label_pkg=2        then 1 else 0 end h,"+\
                        "case when label_pkg=1        then 1 else 0 end p "+\
                        "from inventory_stkdetails"+sWhere+sProduct+sStore+sDel+\
                    ") a group by a.batch_id"+\
                ") d "+\
                "on b.id=d.batch_id "+\
                "left outer join "+\
                "(select id,name,division_id,sub_division_id from company_company) c "\
                "on b.company_to_id=c.id"
            
        cur=connection.cursor()
        cur.execute(s)
        """
        单据类别：voucher id
        1;"成品入库单";"GRN";1
        2;"销售出库单";"GDN";-1
        3;"成品转库单";"GTN";0
        4;"销售退库单";"GWN";1
        5;"入库单退库";"GCN";-1
        """           
        for r in cur.fetchall():
            tm="<a href='/inventory/batch/details/"+str(r[10])+"/' target='_blank'>"+\
                r[0].strftime('%Y-%m-%d %H:%M:%S')+"</a>"
            if vid=='1':
                if request.user.company.is_manufacturing:
                    #16716;"湖北裕贡酒业销售有限公司"
                    if company_id=='16716': 
                        data.append([tm, r[1],r[2],r[11],r[3],r[4],r[5],r[6],r[7] ])
                    else:
                        data.append([tm, r[1],r[2],r[12],r[3],r[4],r[5],r[6],r[7] ])
                else:
                    data.append([tm, r[1],r[2],r[3],r[4],r[5],r[6],r[7] ])
            elif vid=='2':                
                if saldiv_rpt:
                    data.append([tm, r[1],r[8],r[13],r[14],r[2],r[3],r[4],r[5],r[6],r[7] ])
                else:
                    data.append([tm, r[1],r[8],r[2],r[3],r[4],r[5],r[6],r[7] ])
            elif vid=='3':
                data.append([tm, r[1],r[2],r[9],r[3],r[4],r[5],r[6],r[7] ])
            elif vid=='4':
                data.append([tm, r[1],r[8],r[2],r[3],r[4],r[5],r[6],r[7] ])
            elif vid=='5':
                data.append([tm, r[1],r[2],r[3],r[4],r[5],r[6],r[7] ])      
    return render(request, 'inventory/stock/batch_list.html', {        
        'stores': SafeString(stores),
        'products': SafeString(products),
        'data':SafeString(data),
        'e': e,
        'voucher':v,
        'params': params
    })


@tj_login_required
def delete_obj(request,obj_name='',id='0'):
    data={'e':''}
    if request.user.id in [1,2,3214]: #admin,罗秋吉
        if obj_name=='batch':
            bs=Batch.objects.filter(id=int(id))    
            if bs:
                b=bs[0]
                b.delete()
            else:
                data['e']=_('batch id err')
        else:
            data['e']='unexpected obj name'
    else:
        data['e']='permission denyed'
    return JsonResponse(data)



#2019-11-17 标签明细
@tj_login_required
def batch_details(request,id):
    data={'e':'','batch':{},'dataset':[],'del_perm':False}
    if request.user.id in [1,2,3214]: #admin,罗秋吉，百得信
        data['del_perm']=True
    cur=connection.cursor()
    s="select tm,coalesce(code,''),"+\
        "coalesce((select name from inventory_voucher where id=vtype_id),''),"+\
        "coalesce((select name from product_product where id=product_id),''),"+\
        "coalesce((select name from inventory_storehouse where id=store_fr_id),''),"+\
        "coalesce((select name from inventory_storehouse where id=store_to_id),''),"+\
        "coalesce((select username from company_user where id=user_id),''),"+\
        "coalesce((select name from company_company where id=company_to_id),''),"+\
        "vtype_id,"+\
        "coalesce((select name from company_prdplant where id=prd_plant_id),''),"+\
        "coalesce((select code from inventory_voucher where id=vtype_id),'') "+\
        "from inventory_batch where id="+id    
    cur.execute(s)
    r=cur.fetchone()
    if r:
        data['batch']={'time':r[0].strftime('%Y-%m-%d %H:%M:%S'),'code':r[1],'voucher':r[2],'id':id,'product':r[3],
            'store_fr':r[4],'store_to':r[5],'user':r[6],'customer':r[7],'vid':r[8],'plant':r[9],'vcode':r[10]}         
        #标签退库
        if r[8]==5:
            s="select a.label_code,"+\
                "coalesce((SELECT name FROM label.lbl_package where id=a.label_pkg),a.err,'错误'),"+\
                "(select name from product_product where id=b.product_id) "+\
                " from "+\
                "(select label_code,label_pkg,err from inventory_batchdetails where batch_id="+id+") a "                

        else:
            s="select a.label_code,"+\
                "coalesce((SELECT name FROM label.lbl_package where  id=b.label_pkg),a.err,'错误'),"+\
                "(select name from product_product where id=b.product_id) "+\
                " from "+\
                "(select label_code,err from inventory_batchdetails where batch_id="+id+") a "+\
                "left outer join "+\
                "(select label_code,label_pkg,product_id from inventory_stkdetails where batch_id="+id+") b "+\
                "on a.label_code=b.label_code"      
        cur.execute(s)
        for r in cur.fetchall():
            data['dataset'].append([ r[0],r[1],r[2] ])
        cur.close()
    data['dataset']=SafeString(data['dataset'])
    return render(request, 'inventory/stock/batch_details.html', data)    


#2019-11-24
@tj_login_required
@csrf_exempt
def batch_edit(request,id):
    if request.method=='GET':
        data={'e':'','batch':{},'store_fr':[],'store_to':[],'product':[],'company_to':[]}     
        company_id=request.user.company.id
        for o in Storehouse.objects.filter(company_id=company_id).order_by('name'):       
            data['store_fr'].append({'label': o.name, 'value': o.id})
            data['store_to'].append({'label': o.name, 'value': o.id})

        #for o in Product.objects.filter(company_id=company_id).order_by('name'):
        #    data['product'].append({'label': o.name, 'value': o.id}) 

        cur=connection.cursor()    
        cur.execute("select (select username from company_user where id=user_id),"+\
            "(select name from inventory_voucher where id=vtype_id),"+\
            "tm,vtype_id,coalesce(code,''),product_id,"+\
            "coalesce(company_to_id,0),coalesce(store_fr_id,0),coalesce(store_to_id,0),"+\
            "coalesce((select name from product_product where id=product_id),''),"+\
            "coalesce((select name from company_company where id=company_to_id),''),"+\
            "coalesce((select name from inventory_storehouse where id=store_fr_id),''),"+\
            "coalesce((select name from inventory_storehouse where id=store_to_id),''),"+\
            "coalesce(qc_name,'') "+\
            "from inventory_batch where id="+id)
        r=cur.fetchone()
        if r:
            data['batch']={'user':r[0],'voucher':r[1],'tm':r[2].strftime('%Y-%m-%d %H:%M:%S'),'vtype_id':r[3],
                'code':r[4],'product_id':r[5],'company_to_id':r[6],'store_fr_id':r[7],'store_to_id':r[8],
                'product':r[9],'company_to':r[10],'store_fr':r[11],'store_to':r[12],'qc_name':r[13]
            }      
            #if r[3] in [2,4]:
            #    for o in Company.objects.filter(parent_id=company_id).order_by('name'):
            #        data['company_to'].append({'label': o.name, 'value': o.id})  
            
        return render(request, 'inventory/stock/batch_edit.html', data) 
    else:
        a=[]
        cur=connection.cursor() 
        b=Batch.objects.get(id=int(id)) 
        #2020-06-11 为修改平台商入库信息，保存原信息
        check_inbound=False
        if b.vtype_id==2:            
            check_inbound=True     
        product_id=request.POST.get('product_id','')
        company_to_id=request.POST.get('company_to_id','')
        store_fr_id=request.POST.get('store_fr_id','')
        store_to_id=request.POST.get('store_to_id','')
        code=request.POST.get('code','')
        
        #2020-111-12
        if b.vtype_id==1:            
            qc_name=request.POST.get('qc_name','')
            if qc_name:
                b.qc_name=qc_name

        b.code=code       
        b.product_id=int(product_id)
        a.append('product_id='+str(product_id))
       
        if b.vtype_id==2:
            b.company_to_id=int(company_to_id)        
            a.append('company_to_id='+str(company_to_id))
        
        b.store_fr_id=int(store_fr_id)
        a.append('store_id='+str(store_fr_id))
        a.append('store_fr_id='+str(store_fr_id))
        if store_to_id:
            b.store_to_id=int(store_to_id)
            a.append('store_to_id='+str(store_to_id))
        s="update inventory_stkdetails set "+",".join(a)+" where batch_id="+id
        cur.execute(s) 
        b.save()
        #2020-06-06若为发货，且有经销商对应入库记录，则要修改相应入库信息
        if check_inbound:   
            #2020-07-11
            bs=Batch.objects.filter(reference_id=b.id)
            if len(bs)==1:
                b1=bs[0]
                b1.code=code
                b1.product_id=product_id
                b1.company_id=company_to_id                          
                s="update inventory_stkdetails set "+\
                    "product_id="+product_id+",company_id="+company_to_id+\
                    " where batch_id="+str(b1.id)
                cur.execute(s)
                b1.save()   
        #2020-08-15        
        log=ChangeLog(company_id=b.company_id,ancestor_id=b.ancestor_id,obj_name='Batch',obj_id=int(id),user_id=request.user.id)
        log.save()


        #2020-08-25 酒鬼酒修改需要发送透云数据时（？原客户ID为临时客户代码17741）需要重发
        if b.company_id==1 and b.vtype_id==2 and b.product_id in settings.TY_PRODUCTS_ID:
            # is_test=True or False
            delivery_ty(b.id, False)

        #return  redirect('/inventory/batch/gdn/')
        cur.execute("select code from inventory_voucher where id="+str(b.vtype_id))
        nt='gdn'
        r=cur.fetchone()
        nt=r[0]
        return  redirect('/inventory/batch/'+nt+'/')


#@login_required
@tj_login_required
def inventory_html(request, what):
    if what == 'label_query':
        html = 'inventory/label_query.html'
    elif what == 'label_replace':
        html = 'inventory/label_replace.html'
    return render(request, html)


#@login_required
@tj_login_required
def get_label_info(request, code):   
    u = request.user    
    if u.is_staff:       
        d = label_info(code, True, None,u)     

        #2020-04-24
        if u.username=='品控部':            
            dd=json.loads(d.content)
            dd['stock_dev']=[]
            dd['product']=''
            return JsonResponse(dd)
    else:
        #2020-04-28 酒鬼罗工，未入库套标查询
        if u.username=='罗秋吉':
            d = label_info(code) 
        else:
            d = label_info(code, False, u.company_id,u)      
    return d


# 手机APP窜货查询接口
def app_label_info(request, company_id, code):
    return label_info(code, False, company_id)





def label_info(code, is_staff=False, company_id=None,user=None):
    data = {
        'e': '',
        'code': code,
        'product': '',
        'package': '',
        'company': '',
        'replace': '',
        'boxes': {'pk': '', 'related': []},
        'packets': [],
        'bottles': [],
        'bottlecaps':[],
        'stock_dev': [],
        'replace':''
    }
    if code[:1]=='4':
        r = requests.request('GET', 'http://www.china315net.com:35088/jg/fw_wl/'+code+'/')
        d = json.loads(r.text)
        if d['wl']:
            code=d['wl']
            data['code']=d['wl']
        else:
            data['e']='获取物流码失败'
            return JsonResponse(data)
    cur = connection.cursor()
    s_company = ''
    a_company_id=0      
    

    if company_id:
        cur.execute("select ancestor_id from logistics.company_company where id="+str(company_id))        
        r=cur.fetchone()
        if r:
            a_company_id=r[0]
        s_company = " and company_id=" + str(company_id)
        #2020-04-09 酒鬼市场稽查人员，先不分酒鬼和内参
        if int(company_id)==1: 
            s_company = " and ancestor_id in (1,1644)"
        else:
            s_company = " and ancestor_id="+ str(a_company_id)
            #else:
            #s_company = " and company_id=" + str(company_id)
    

    #2020-04-30 有标签要查BA表 "湖北裕贡酒业销售有限公司"
    #if not company_id or a_company_id in [16716]:
    cur.execute("select code_a FROM label.lbl_abcode where code_b='"+code+"'")
    r=cur.fetchone()
    if r:
        code=r[0]
            

   

    #2020-07-27 是否跨段动态绑定
    tb = 'label.t' + code[:4]
    cur.execute("select tbname from label.lbl_tbcross where segment='"+code[:4]+"'")
    rrr=cur.fetchone()
    if rrr:
        tb='label.'+rrr[0]    
   

    # 有标签被替换的情况，
    try:
        cur.execute("select id,package_id,parent_id," + \
                    "(select parent_id from " +tb+ " where id=a.parent_id)," + \
                    "(select code from " +tb+ " where id=a.parent_id) "+\
                    "from " +tb+ " a where code=coalesce((select code0 from label.lbl_replace_details where code='" + code + "'"+\
                        " order by id desc limit 1),'" + code +\
                    "')"
        )
        r = cur.fetchone()

        if not r:
            data['e'] = _('标签号码异常')
            return JsonResponse(data)
    except Exception as e:
        data['e'] = _('标签号码异常')
        return JsonResponse(data)

    s="select code0,code,dt from label.lbl_replace_details where code='" + code + "'"+\
        " or code0='"+code+"' order by id desc limit 1"
    

    #print(s)

    cur.execute(s)
   
    rr=cur.fetchone()
    if rr:
        data['replace']=u"此标签有替换记录 "+rr[2].strftime('%Y-%m-%d')+": "+rr[0]+"->"+rr[1]





    # 非天鉴职员查询
    if company_id:
        parent_id='null'
        if r[2]:
            parent_id=str(r[2])
            if r[3]:
                parent_id=str(r[3])
        s="select (select name from logistics.product_product where id=product_id) "\
            "from logistics.inventory_stkdetails where " + \
            "label_pk_id in (" + str(r[0])+"," + parent_id + ")" + s_company +" limit 1"
        
        cur.execute(s)
        r99=cur.fetchone()
        if r99:
            data['product']=r99[0]
        else:
            if int(company_id)==1:
                if r[1] == 3:
                    data['package']=_('箱标')
                else:
                    data['package']=_('瓶标')
                c = code
                if r[4]:
                    c = r[4]

                #r = jiugui_local_info(c)
                #2020-04-06
                r = jiugui_local116(c)
                
                if not r['e']:
                    data['product'] = r['product']
                    data['stock_dev'] = r['stock_dev']
                    if r['boxes']['pk']:
                        data['boxes']= r['boxes']
                    if r['bottles']:
                        data['bottles'] = r['bottles']
                # data['e'] = r['e']
                return JsonResponse(data)
            else:
                data['e'] = _('标签号码未激活')
            return JsonResponse(data)
        
    #company_id=None,酒鬼酒
    # 主箱标ID
    id = 0
    #tb = 'label.t' + code[:4]
    
    


    # 箱标
    if r[1] == 3:
        data['package'] = _('箱标')
        if r[2]:
            id = r[2]
        else:
            id = r[0]
    # 盒标
    elif r[1] == 2:
        data['package'] = _('盒标')
        s = "select coalesce((select parent_id from " + tb + " where id=a.parent_id),parent_id) " + \
            "from " + tb + " a where id=" + str(r[0])
        cur.execute(s)
        id = cur.fetchone()[0]
    # 瓶标
    elif r[1] == 1:
        data['package'] = _('瓶标')
        s = "select package_id," + \
            "coalesce((select parent_id from " + tb + " where id=a.parent_id),parent_id) " + \
            "from " + tb + " a where id=" + str(r[0])
        cur.execute(s)
        r1 = cur.fetchone()
        # 箱瓶二级对应
        if r1[0] != 3:
            id = r1[1]
        # 箱盒瓶三级对应
        else:
            if r1[1]:
                s="select coalesce((select parent_id from "+tb+" where id=a.parent_id),parent_id) from "+tb+" a where id="+str(r1[1])
                cur.execute(s)
                r2 = cur.fetchone()
                id = r2[0]
    # 单枚标
    elif r[1] == 4:
        data['package'] = _('单枚标')
        data['boxes']['pk'] = code
    # 套标
    if id:
        id = str(id)
        # 箱标数据
        # s = "select code,array_to_string(array(select code from " + tb + " where parent_id=" + id + \
        #    " and package_id=3),',') from " + tb + " where id=" + id + " order by code"

        s = "select code,array_to_string(array(select coalesce(" +\
            "(select code from label.lbl_replace_details where code0=a.code),a.code) from " + \
            tb + " a where parent_id=" + id + \
            " and package_id=3),',') from " + tb + " where id=" + id + " order by code"
        cur.execute(s)
        r = cur.fetchone()
        data['boxes']['pk'] = r[0]
        if r[1]:
            data['boxes']['related'] = r[1].split(',')
        cur.execute("select coalesce((select code from label.lbl_replace_details where code0=a.code),a.code) " +\
                    "from " + tb + " a where parent_id=" + id + " and package_id=1 order by code")
        rs = cur.fetchall()
        
        #2020-10-17
        bottlecaps={}

        # 箱瓶二级对应
        if rs:
            for r in rs:
                data['bottles'].append(r[0])
            
            #2020-10-17
            if data['bottles']:
                cur.execute("select label,code,bottle from label.bottlecap where bottle in ('"+"','".join(data['bottles'])+"')")
                for rrr in cur.fetchall():
                    #bottlecaps.append({rrr[2]:rrr[1]})
                    bottlecaps[rrr[2]]=rrr[1]
        # 箱盒瓶三级对应
        else:
            cur.execute("select id,coalesce((select code from label.lbl_replace_details where code0=a.code),a.code) " +\
                "from " + tb + " a where parent_id=" + id + " and package_id=2 order by code")
            rs = cur.fetchall()
            for r in rs:
                a = []
                cur.execute("select coalesce((select code from label.lbl_replace_details where code0=a.code),a.code) " +\
                    "from " + tb + " a where parent_id=" + str(r[0]) + " order by code")
                for r1 in cur.fetchall():
                    a.append(r1[0])
                data['packets'].append({'code': r[1], 'bottles': a})
                
                #2020-10-17
                if a:
                    cur.execute("select label,code,bottle from label.bottlecap where bottle in ('"+"','".join(a)+"')")
                    for rrr in cur.fetchall():
                        #bottlecaps.append({rrr[2]:rrr[1]})
                        bottlecaps[rrr[2]]=rrr[1]
        data['bottlecaps']=bottlecaps
    else:
        # pass
        id = r[0]
        data['boxes']['pk'] = code    
    s="select label_code,(select name from inventory_voucher where id=vtype_id),"+\
        "(select code from inventory_batch where id=batch_id),tm,"+\
        "(select username from company_user where id="+\
            "(select user_id from inventory_batch where id=batch_id)),"+\
        "(select name from product_product where id=product_id),"+\
        "(select name from inventory_storehouse where id=store_id),"+\
        "coalesce((select name from inventory_storehouse where id=store_fr_id),''),"+\
        "(select name from label.lbl_package where id=label_pkg),"+\
        "coalesce((select name from company_company where id=company_to_id),'') "+\
        "from inventory_stkdetails where label_id="+str(id) +" or "+\
            "label_code='"+code+"' order by tm"
    #2020-03-24
    s="select label_code,(select name from inventory_voucher where id=vtype_id),"+\
        "(select code from inventory_batch where id=batch_id),tm,"+\
        "(select username from company_user where id="+\
            "(select user_id from inventory_batch where id=batch_id)),"+\
        "(select name from product_product where id=product_id),"+\
        "(select name from inventory_storehouse where id=store_id),"+\
        "coalesce((select name from inventory_storehouse where id=store_fr_id),''),"+\
        "(select name from label.lbl_package where id=label_pkg),"+\
        "coalesce((select name from company_company where id=company_to_id),''),vtype_id "+\
        "from inventory_stkdetails where label_pk_id="+str(id)+" order by dt,vtype_id"
    
    has_outbound=False
    cur.execute(s)
    for r in cur.fetchall():
        store_fr=r[6]
        store_to=r[7]
        if r[10]==2:
            has_outbound=True
        if r[1]!='成品转库单':
            store_to=''
        data['stock_dev'].append({
            'label_code': r[0],
            'voucher_type': r[1],
            'voucher_no': r[2],
            'time': r[3].strftime('%Y-%m-%d %H:%M'),
            'user': r[4],
            'product': r[5],
            'store': store_fr,
            'store_fr': store_to,
            'package': r[8],
            'to_company': r[9]
        })
    if not data['stock_dev']:
        #r = jiugui_local_info(data['boxes']['pk'])
        #2020-04-06
        r = jiugui_local116(data['boxes']['pk'])



        if not r['e']:
            data['product'] = r['product']
            data['stock_dev'] = r['stock_dev']
            if r['boxes']['related']:
                data['boxes'] = r['boxes']
            if r['bottles']:
                data['bottles'] = r['bottles']
        # data['e'] = r['e']
    else:
        if has_outbound==False:
            d=jiugui_delivery(data['boxes']['pk'])
            if d['delivery']:
                data['stock_dev'].append(d['delivery'])    


    '''
    if not data['stock_dev'] and is_staff:
        #r = jiugui_local_info(data['boxes']['pk'])
        #2020-04-06
        r = jiugui_local116(data['boxes']['pk'])



        if not r['e']:
            data['product'] = r['product']
            data['stock_dev'] = r['stock_dev']
            if r['boxes']['related']:
                data['boxes'] = r['boxes']
            if r['bottles']:
                data['bottles'] = r['bottles']
        # data['e'] = r['e']
    '''
    cur.close()
    return JsonResponse(data)


def period_close(yr, mn, is_initial=False):
    yr = str(yr)
    mn = str(mn)
    cur = connection.cursor()
    if not is_initial:
        for c in Company.objects.filter(inv_mgt=True):
            cur.execute("delete from logistics.inventory_stk0 where " +
                        "company_id=" + str(c.id) + " and yr=" + yr + " and mn=" + mn)
    s = "insert into logistics.inventory_stk0(company_id,product_id,store_id,yr,mn) " + \
        ""



#2020-05-13
@csrf_exempt
def app_label_replace(request):
    data = {'e': '', 'cnt': 0}
    k = sK    
    e = time_key_check(request, k)
    if e:
        data['e'] = e
        return JsonResponse(data)
    codes=[]
    rs=json.loads(request.body)
    u_id=str(rs['user_id'])
    c_id=str(rs['company_id'])
    for r in rs['codes']:
        codes.append("('"+r[0]+"','"+r[1]+"')")
    cur=connection.cursor()
    cur.execute("create temp table tmp(code0 character varying(20),code character varying(20))")
    cur.execute("insert into tmp(code0,code) values "+",".join(codes))
    # 写入BATCH
    s = "insert into label.lbl_replace_batch(user_id,company_id,txt_file) values " + \
            "(" + u_id + "," + c_id + ",'扫码枪上传') returning id"
    cur.execute(s)
    id = str(cur.fetchone()[0])

    #写入LOG
    s = "INSERT INTO label.lbl_replace_log(code0, code, batch_id, company_id, label_id) " + \
        "select code0,code," + id + "," + c_id + ",null from tmp" 
    cur.execute(s)

    cur.execute("select left(code0,4) from tmp group by left(code0,4)")
    rs = cur.fetchall()
    for r in rs:
        cur.execute("select to_regclass('label.t" + r[0] + "')")
        r0 = cur.fetchone()
        if r0[0]:            
            s = "INSERT INTO label.lbl_replace_details(code0, code, batch_id, company_id, label_id,parent_id) " + \
                "select code0,code," + id + "," + c_id + "," + \
                "(select id from label.t" + r[0] + " where left(code0,4)='" + r[0] + "' and code=code0)," + \
                "(select parent_id from label.t" + r[0] + " where left(code0,4)='" + r[0] + "' and code=code0) " + \
                "from tmp on conflict (code0) do update set "+\
                "code=EXCLUDED.code,batch_id=EXCLUDED.batch_id,company_id=EXCLUDED.company_id,label_id=EXCLUDED.label_id,parent_id=EXCLUDED.parent_id"    
            cur.execute(s)
            data['cnt']=data['cnt']+cur.rowcount    
    return JsonResponse(data)    



#@login_required
@tj_login_required
@csrf_exempt
def upload_label_replace(request):
    data = {'files': [], 'e': ''}
    dt = datetime.datetime.now()
    u_id = str(request.user.id)
    c = request.user.company
    c_name = c.name
    p = settings.MEDIA_ROOT + '/' + c_name + '/txt/label_replace/' +  \
        str(dt.year) + str(dt.month).zfill(2) + str(dt.day).zfill(2)
    if not os.path.exists(p):
        os.makedirs(p)
    c_id = str(c.id)
    files = request.FILES.getlist('files')
    cur = connection.cursor()
    cur.execute("create temp table tmp(code0 character varying(20),code character varying(20))")
    #2019-12-23
    #替换要改成一行行读取
    for f in files:
        fn = f.name
        with open(p + '/' + fn, 'wb+') as destination:
            for chunk in f.chunks():
                destination.write(chunk)
        f.seek(0)
        
        cur.execute("truncate table tmp")
        cur.copy_from(f, 'tmp', sep=',', null='')
        # 写入BATCH
        s = "insert into label.lbl_replace_batch(user_id,company_id,txt_file) values " + \
            "(" + u_id + "," + c_id + ",'" + fn + "') returning id"
        cur.execute(s)
        id = str(cur.fetchone()[0])
        cur.execute("select left(code0,4) from tmp group by left(code0,4)")
        rs = cur.fetchall()
        for r in rs:
            cur.execute("select to_regclass('label.t" + r[0] + "')")
            r0 = cur.fetchone()
            if r0[0]:
                s = "INSERT INTO label.lbl_replace_log(code0, code, batch_id, company_id, label_id) " + \
                    "select code0,code," + id + "," + c_id + "," + \
                    "(select id from label.t" + r[0] + "  where left(code0,4)='" + r[0]+"' and code=code0) " + \
                    "from tmp"
                cur.execute(s)
                s = "INSERT INTO label.lbl_replace_details(code0, code, batch_id, company_id, label_id,parent_id) " + \
                    "select code0,code," + id + "," + c_id + "," + \
                    "(select id from label.t" + r[0] + " where left(code0,4)='" + r[0] + "' and code=code0)," + \
                    "(select parent_id from label.t" + r[0] + " where left(code0,4)='" + r[0] + "' and code=code0) " + \
                    "from tmp on conflict (code0) do nothing" 

                    #"from tmp on conflict (code0) do update set code=excluded.code,batch_id=" + id
                cur.execute(s)

        s = "select count(*)," + \
            "(select count(*) from label.lbl_replace_log where label_id is not null and batch_id=" + id + ") from tmp"
        cur.execute(s)
        r = cur.fetchone()
        tt = r[0]
        ok = r[1]
        s = ''
        if tt == ok:
            s = ",tm_handled=now()"
        cur.execute("update label.lbl_replace_batch set cnt_tt=" + str(tt) + ",cnt_ok=" + str(ok) + s + " where id="+id)
        data['files'].append({'txt_name': fn, 'total': tt, 'ok': ok})
    return JsonResponse(data)



def list_unhandled_batch(request, company_id):
    data = []
    cur = connection.cursor()
    s = "select a.code,a.batch_no,a.tm,"+\
        "(select username from logistics.company_user where id=a.user_id)," + \
        "(select name from logistics.product_product where id=a.product_id)," + \
        "(select name from logistics.inventory_storehouse where id=a.store_fr_id)," + \
        "(select name from logistics.inventory_storehouse where id=a.store_to_id)," + \
        "(select name from logistics.company_company where id=a.company_to_id)," + \
        "(select name from logistics.inventory_voucher where id=a.voucher_id)," + \
        "(select count(*) from logistics.inventory_batchdetails where batch_id=a.id)," + \
        "a.id from " + \
        "(select code,batch_no,product_id,store_fr_id,store_to_id,voucher_id,company_to_id,tm,id,user_id " + \
            "from logistics.inventory_batch where company_fr_id=" + company_id + " and tm_handled is null) a"
    cur.execute(s)
    for r in cur.fetchall():
        data.append({
            'bill_number': r[0],
            'time': r[2].strftime('%Y-%m-%d %H:%M:%S'),
            'user': r[3],
            'product': r[4],
            'store': r[5],
            'agent': r[7],
            'bill_type': r[8],
            'records_count': r[9],
            'id':r[10]
        })
    return render(request, 'inventory/inventory_unhandled.html',{'data':data})


@tj_login_required
def handle_batch(request,batch_id):
    data={'e':'','cnt':0}
    bths=Batch.objects.filter(id=str(batch_id))
    if not bths:
        data['e']='BATCH ID:'+batch_id+'  不存在'
        return JsonResponse(data) 
    b=bths[0]
    #标签是否已有基础数据
    cur=connection.cursor()
    tt=0
    e_tt=0
    #cur.execute("select coaleace((select tbname from label.lbl_tbcross where segment=left(label_code,4)),left(label_code,4)),"+\
    #    "count(*) from logistics.inventory_batchdetails where batch_id="+batch_id+" group by left(label_code,4)")
    s="select coalesce((select tbname from label.lbl_tbcross where segment=a.rg),'t'||a.rg),a.cnt,a.rg from "+\
        "(select left(label_code,4) rg,count(*) cnt from  logistics.inventory_batchdetails "+\
            "where batch_id="+batch_id+" group by left(label_code,4)) a"
    cur.execute(s)
    rs = cur.fetchall()
    for r in rs:
        cur.execute("select to_regclass('label." + r[0] + "')")
        r0 = cur.fetchone()
        if r0[0]:
            tt=tt+r[1]
            s="update logistics.inventory_batchdetails c set "+\
                "label_id=b.id,"+\
                "label_pk_id=coalesce(b.parent_id,b.id),"+\
                "label_pkg=b.package_id "+\
                "from "+\
                "(select id,label_code from logistics.inventory_batchdetails where "+\
                    "batch_id="+batch_id+" and left(label_code,4)='"+r[2]+"') a "+\
                "left outer join "+\
                "label."+r[0]+" as b "+\
                "on b.code=a.label_code "+\
                "where c.id=a.id"            
            cur.execute(s)
    s="select count(*) from logistics.inventory_batchdetails "+\
        "where batch_id="+batch_id+" and label_id is null" 
    cur.execute(s)  
    r=cur.fetchone()
    e_tt=r[0]   
    if e_tt:
        data['e']=str(e_tt)+'条记录缺少标签基础数据（记录总数'+str(tt)+'）'   
    
    #删除已有记录
    cur.execute("delete from logistics.inventory_stkdetails where batch_id="+batch_id)


    if b.vtype_id==5:
        sql = "INSERT INTO inventory_stkdetails (vtype_id,batch_id,company_to_id,company_id,"+\
            "label_code,label_id,label_pkg,product_id,store_fr_id,store_to_id,store_id,dt,tm,is_delete,label_pk_id) "+\
            "select a.vtype_id,a.id,a.company_to_id,company_id,b.label_code,b.label_id,"+\
            "b.label_pkg,"+\
            "(select product_id from inventory_stkdetails where vtype_id=1 and label_pk_id=b.label_pk_id limit 1),"+\
            "a.store_fr_id,a.store_to_id,a.store_fr_id,a.dt,a.tm,true,b.label_pk_id from "+\
            "(select batch_id,label_id,label_code,label_pkg,label_pk_id from inventory_batchdetails "+\
                "where batch_id="+batch_id+" and label_id is not null "+\
            ") b "+\
            "left outer join "+\
            "(select * from inventory_batch where id="+batch_id+") a "+\
            "on b.batch_id=a.id "+\
            "where (select product_id from inventory_stkdetails where vtype_id=1 and label_pk_id=b.label_pk_id limit 1) is not null"
    else:
        sql = "INSERT INTO inventory_stkdetails (vtype_id,batch_id,company_to_id,company_id,"+\
            "label_code,label_id,label_pkg,product_id,store_fr_id,store_to_id,store_id,dt,tm,label_pk_id) "+\
            "select a.vtype_id,a.id,a.company_to_id,company_id,b.label_code,b.label_id,b.label_pkg,"+\
            "coalesce(b.product_id,(select product_id from inventory_batch where id=a.id)),"+\
            "a.store_fr_id,a.store_to_id,a.store_fr_id,a.dt,a.tm,b.label_pk_id from "+\
            "(select batch_id,label_id,label_code,label_pkg,label_pk_id,product_id from inventory_batchdetails "+\
                "where batch_id="+batch_id+" and label_id is not null "+\
            ") b "+\
            "left outer join "+\
            "(select * from inventory_batch where id="+batch_id+") a "+\
            "on b.batch_id=a.id" 
    
    cur.execute(sql+" returning id")
    data['cnt']=cur.rowcount
    if not e_tt:
        b.tm_handled=datetime.datetime.now()
        b.dt_handled=datetime.datetime.now()
        b.save()


    #入库退标，对原入库标签做删除标记(一箱可能贴2个标签，即入库扫的是一枚标，退库时可能是同一箱的另一枚)
    if b.vtype_id==5:      
        s="update inventory_stkdetails set is_delete=true where vtype_id=1 and "+\
            "label_pk_id in (select label_pk_id from logistics.inventory_batchdetails where batch_id="+batch_id+")"
        cur.execute(s)
    return JsonResponse(data)    


#保存到旧物流，透云取数
def delivery_ty(batch_id,is_test=False):  
    data={'summary':0,'details':0}
    cur=connection.cursor()
    cn_ty=psycopg2.connect(settings.TY_WULIU_STR)
    cur_ty=cn_ty.cursor()
    bs=Batch.objects.filter(id=batch_id)
    if bs:
        b=bs[0]
        yr=str(b.tm.year)
        agt=Company.objects.get(id=b.company_to_id)        
        cur_ty.execute("select id from customer_company where name='" + agt.name + "' and parent_id=7 and parent_compid=99")
        r=cur_ty.fetchone()
        if not r:
            ty_agent('add', agt)
        cur_ty.execute("select id from customer_company where name='" + agt.name + "' and parent_id=7 and parent_compid=99")
        r=cur_ty.fetchone()
        if r:
            cid=str(r[0])
            pid=str(settings.TY_PRODUCTS_ID[b.product_id]['wl_id'])
            s="select count(*) from inventory_batchdetails where batch_id="+str(b.id)
            cur.execute(s)
            r=cur.fetchone()
            tt='0'
            if r:
                tt=str(r[0])   
                data['summary']=r[0]
            if is_test:
                tm="'2019-01-01 00:00:00'"
            else:
                tm='now()'
            bno="null"
            if b.code:
                bno="'"+b.code+"'"
            s="INSERT INTO public.y"+yr+"("+\
                "userid,fhpici,fhkey,xiangnumber,lqty,fhtypeid,tm,frcompid,frstoid,pid,tocompid,bill_no) VALUES ("+\
                "178,'000','000',"+tt+","+tt+",3,"+tm+",99,238,"+pid+","+cid+","+bno+\
            ") returning id"
            cur_ty.execute(s)
            r=cur_ty.fetchone()
            sid=str(r[0])
            cn_ty.commit()
                        
            s="select left(label_code,4) from inventory_stkdetails where batch_id="+str(b.id)+\
                " group by left(label_code,4)"
            cur.execute(s)
            a=[]
            rs=cur.fetchall()
            for r in rs:
                cur.execute("select label_code from inventory_stkdetails where "+\
                    "batch_id="+str(b.id)+" and left(label_code,4)='"+r[0]+"'")
                rs1=cur.fetchall()
                for r1 in rs1:
                    a.append("("+sid+",(select id from z"+r[0]+" where code='"+r1[0]+"'),'"+r1[0]+"')")
            if a:
                s="insert into delivery_details(summary_id,label_id,label_code)"+\
                    " values "+",".join(a) 
                cur_ty.execute(s)
                cn_ty.commit()
                cur_ty.execute("select count(*) from delivery_details where summary_id="+sid)
                r=cur_ty.fetchone()
                data['details']=r[0]
    return data



@login_required
def send_to_wuliu(request,batch_id):
    #def delivery_ty(batch_id,is_test=False):  

    return JsonResponse(delivery_ty(batch_id))


#2020-01-07
@csrf_exempt
def app_label_check(request):
    data={'e':'','code':'','package_id':0,'label_pk_id':0,'err_code':0,'product_id':0,'product_name':''}
    if request.method=='GET':
        data['e']='invalid method'
        data['err_code']=10003
        return JsonResponse(data)
    
    STRICT=True #只有上级发过货才可以入库，只有入库后才可以扫码出库    
    #2020-04-23
    c=request.POST.get('code','')
    company_id=request.POST.get('company_id','')
    vtype_id=request.POST.get('vtype_id','')  
    user_id=request.POST.get('user_id','')    
    
    #2020-04-23
    data['code']=c
    if not c or not company_id or not vtype_id:
        data['e']='parameter missing'
        data['err_code']=10000
        return JsonResponse(data)    
    

    companys=Company.objects.filter(id=int(company_id))
    if not companys:
        data['e']='invalid company id'
        data['err_code']=10000
        return JsonResponse(data)
    company=companys[0]   

    cfgs=CompanyCfg.objects.filter(company_id=company.ancestor_id)
    if not cfgs:
        data['e']='company configurations missing'
        data['err_code']=10000
        return JsonResponse(data)
    cfg=cfgs[0]    
    cur=connection.cursor()

    #2020-07-15
    s="select parent_id,ancestor_id,"+\
        "coalesce((select name from company_company where id=a.parent_id),''),"+\
        "coalesce((select name from company_company where id=a.ancestor_id),'') "+\
        "from company_company a where id="+company_id
    cur.execute(s)
    r=cur.fetchone()
    #a_id=str(company.ancestor_id) 
    #p_id=str(company.parent_id)
    p_id=str(r[0])
    a_id=str(r[1]) 
    p_name=r[2]
    a_name=r[3]
    c_id=str(company_id)   

    #2020-05-30
    #内参6月份活动，只要内参给父级发过货，终端店就可以扫码入库
    #dt=datetime.datetime.now().strftime('%Y-%m-%d')
    #if dt[:7]=='2020-06' and a_id=='1644':
    #    STRICT=False

    #主箱标ID
    lbl_pk_id=0
    #酒鬼防伪码以40开头
    if c[:2]=='40':        
        cur.execute("SELECT wl_code FROM label.lbl_fw_wl where fw_code='"+c+"'")
        r=cur.fetchone()
        if r:
            c=r[0]
        else:
            r=requests.get('http://china315net.com:35222/wuliu/47/fw/wuliu/?fw_code='+c)
            d=r.json()
            if d['wl']:
                c=d['wl']
    data['code']=c
    if company.level.sn==0:
        data['e']=_('此工具只适用于经销商和门店')
        data['err_code']=10000
        return JsonResponse(data)

    tb='label.t'+c[:4]
    #2020-07-27 是否跨段动态绑定
    cur.execute("select tbname from label.lbl_tbcross where segment='"+c[:4]+"'")
    rrr=cur.fetchone()
    if rrr:
        tb='label.'+rrr[0]  

    try:
        cur.execute("select package_id,coalesce(parent_id,id) from "+tb+" where code='"+c+"'")
        r=cur.fetchone()
        if not r[0]:
            data['e']=_('标签号码未激活')
            data['err_code']=20001
            return JsonResponse(data)
        else:
            data['package_id']=r[0]
            data['label_pk_id']=r[1]
            lbl_pk_id=str(r[1])
            
    except Exception:
        data['e']=_('标签号码错误')
        data['err_code']=20000
        return JsonResponse(data)   
    

    
    #箱标,瓶标同一标签都只能入/出一次
    s="select label_pkg,tm from inventory_stkdetails where vtype_id="+vtype_id+\
        " and label_code='"+c+"' and company_id="+c_id
    cur.execute(s)      
    r=cur.fetchone()
    if r:
        t1=''
        if vtype_id=='1':            
            t1=_('此标签已入库')
        elif vtype_id=='2':
            t1=_('此标签已出库')     
        data['err_code']=10001
        data['e']=t1+'('+r[1].strftime('%Y-%m-%d %H:%M:%S')+')'
        return JsonResponse(data)   

    #瓶标
    if data['package_id']==1:        
        if vtype_id=='1':
            if cfgs.lv1_pkg_split==0:            
                data['e']=_('请扫描箱标入库')
                data['err_code']=10002
                return JsonResponse(data)            
        elif vtype_id=='2':     
            if company.level_id==2: #1级经销商
                if cfgs.lv1_pkg_split==0: 
                    data['e']=_('请扫描箱标出库')
                    data['err_code']=10002
                    return JsonResponse(data)              
            s="select id from inventory_stkdetails where vtype_id=1 and label_pk_id="+lbl_pk_id+" and company_id="+c_id                      
            cur.execute(s)
            r=cur.fetchone()
            if not r:
                if cfgs.lv1_pkg_split==0:
                    data['e']=_('请先将此瓶所在箱扫码入库')
                    data['err_code']=10002
                    return JsonResponse(data)
            #所在箱是否出过库
            cur.execute("select label_code,tm from inventory_stkdetails where vtype_id=2 and label_pkg=3 and label_pk_id="+lbl_pk_id+" and company_id="+c_id)
            r=cur.fetchone()
            if r:
                data['e']=_('包含此瓶的箱标已出库')+'('+r[0]+":"+r[1].strftime('%Y-%m-%d %H:%M:%S')+')'
                data['err_code']=10001
                return JsonResponse(data)            
    #箱标,单枚标
    elif data['package_id'] in (3,4):   
        #关联箱标出/入库检查
        s="select label_pkg,tm from inventory_stkdetails where vtype_id="+vtype_id+" and label_pk_id="+lbl_pk_id+" and company_id="+c_id
        cur.execute(s)
        r=cur.fetchone()
        if r:
            t1=''
            if vtype_id=='1':            
                t1=_('此箱已入库')
            elif vtype_id=='2':
                t1=_('此箱已出库')     
            data['err_code']=10001
            data['e']=t1+'('+r[1].strftime('%Y-%m-%d %H:%M:%S')+')'
            return JsonResponse(data)   
        if vtype_id=='1':  
            #inbound_cretiria_lv1 一级经销入库规则:
            #(0, _('严格(工厂有发货)')),
            #(1, _('中等(工厂有入库)')), 
            #(2, _('不限制')),),default=0)

            #inbound_cretiria_lv2 二级经销入库规则:
            #(0, _('严格(父级有发货)')),
            #(1, _('中等(父级有入库)')), 
            #(2, _('宽松(工厂有发货)')),
            #(3, _('超宽松(工厂有入库)')),
            #(4, _('不限制')),),default=0)   

            if company.level.id==2:     #一级经销商入库
                if cfg.inbound_cretiria_lv1==0:
                    sErr='未查询到 "'+a_name+'" 发货记录'
                    s="select id from inventory_stkdetails where vtype_id=2 and company_id="+a_id+" and company_to_id="+c_id+" and label_pk_id="+lbl_pk_id+" limit 1"
                elif cfg.inbound_cretiria_lv1==1:
                    sErr='未查询到 "'+a_name+'" 入库记录'
                    s="select id from inventory_stkdetails where vtype_id=1 and company_id="+a_id+" and label_pk_id="+lbl_pk_id+" limit 1"
                else:
                    sErr=''
                    s="select 1"
            else:                       #二级以上经销商入库
                if cfg.inbound_cretiria_lv2==0:
                    sErr='未查询到 "'+p_name+'" 发货记录'
                    s="select id from inventory_stkdetails where vtype_id=2 and company_id="+p_id+" and company_to_id="+c_id+" and label_pk_id="+lbl_pk_id+" limit 1"
                elif cfg.inbound_cretiria_lv2==1:
                    sErr='未查询到 "'+p_name+'" 入库记录'
                    s="select id from inventory_stkdetails where vtype_id=1 and company_id="+p_id+" and label_pk_id="+lbl_pk_id+" limit 1"
                elif cfg.inbound_cretiria_lv2==2:
                    sErr='未查询到 "'+a_name+'" 发货记录'
                    s="select id from inventory_stkdetails where vtype_id=2 and company_id="+a_id+" and label_pk_id="+lbl_pk_id+" limit 1"
                elif cfg.inbound_cretiria_lv2==3:
                    sErr='未查询到 "'+a_name+'" 入库记录'
                    s="select id from inventory_stkdetails where vtype_id=1 and company_id="+a_id+" and label_pk_id="+lbl_pk_id+" limit 1"
                else:
                    sErr=''
                    s="select 1"    
            #一级经销商系统已自动做入库            
            #if STRICT:     
            #    if company.level==2:
            #        sErr='未查询到 "'+a_name+'" 发货记录'
            #    else:
            #        sErr='未查询到 "'+p_name+'" 发货记录'
            #    s="select id from inventory_stkdetails where vtype_id=2 and company_id="+p_id+" and company_to_id="+c_id+" and label_pk_id="+lbl_pk_id+" limit 1"  
            #else:
            #    #工厂给一级的发货，系统已自动做了一级入库,一级没有入库操作      #工厂是否给一级发过货    
            #    sErr='未查询到 "'+a_name+'" 到 "'+p_name+'" 的发货记录'
            #    s="select id from inventory_stkdetails where vtype_id=2 and company_id="+a_id+" and company_to_id="+p_id+" and label_pk_id="+lbl_pk_id+" limit 1"            
            
            cur.execute(s)
            r=cur.fetchone()
            if not r:               
                data['e']=sErr
                data['err_code']=10001
                return JsonResponse(data)
        elif vtype_id=='2':
            #是否已入库  
            s="select id from inventory_stkdetails where vtype_id=1 and label_pk_id="+lbl_pk_id+" and company_id="+c_id  
            cur.execute(s)
            r=cur.fetchone()
            if not r:     
                #2020-11-03
                if company.level_id==2:
                    data['e']=_('未查询到工厂发货记录') 
                else:
                    data['e']=_('请先将此箱标入库后再扫码出库') 
                data['err_code']=10004
                return JsonResponse(data)
            #是否出过库
            cur.execute("select label_code,tm,label_pkg from inventory_stkdetails where vtype_id=2 and label_pk_id="+lbl_pk_id+" and company_id="+c_id)
            r=cur.fetchone()
            if r:
                if r[2] in (3,4):
                    t1=_('此箱已出库')
                else:
                    t1=_('此箱已被拆箱出库')
                data['e']=t1+'('+r[0]+":"+r[1].strftime('%Y-%m-%d %H:%M:%S')+')'
                data['err_code']=10000
                return JsonResponse(data)   


    #2020-10-19 取标签产品ID
    s="select product_id,"+\
        "(select name from product_product where id=product_id) from inventory_stkdetails where label_pk_id="+lbl_pk_id+\
        " and company_id="+a_id+" and vtype_id in (1,2) order by vtype_id limit 1"
    cur.execute(s)
    r=cur.fetchone()
    if r:
        data['product_id']=r[0]
        data['product_name']=r[1]
    return JsonResponse(data)       
    
    


def app_stock(request,company_id):
    data={'e':'','stock':[]}
    k = sK   
    e = time_key_check(request, k)
    if e:
        data['e'] = e
        return JsonResponse(data) 
    sWhere=" where company_id="+company_id    
    s="select (select name from product_product where id=b.product_id),"+\
        "b.x,b.h,b.p,b.product_id from "+\
        "(select a.product_id,sum(a.x) x,sum(a.h) h,sum(a.p) p from "+\
            "(select product_id,"+\
                "case when label_pkg in (3,4) then "+\
                    "case when vtype_id in (2,5) then -1 else 1 end "+\
                "else 0 end x,"+\
                "case when label_pkg=2 then "+\
                    "case when vtype_id in (2,5) then -1 else 1 end "+\
                "else 0 end h,"+\
                "case when label_pkg=1 then "+\
                    "case when vtype_id in (2,5) then -1 else 1 end "+\
                "else 0 end p "+\
                "from inventory_stkdetails"+sWhere+\
            ") a "+\
        "group by a.product_id) b "+\
        "order by name"   
    cur=connection.cursor()
    cur.execute(s)
    
    for r in cur.fetchall():
        qty=''
        a=[]
        if r[1]:
            a.append(str(r[1])+'箱')
        if r[2]:
            a.append(str(r[2])+'盒')
        if r[3]:
            a.append(str(r[3])+'瓶')
        if a:
            qty=",".join(a)        
        data['stock'].append({
            "product":r[0],
            "qty_x":r[1],
            "qty_h":r[2],
            "qty_p":r[3],
            "qty":qty,
            "product_id":r[4]
        })

    return JsonResponse(data)


@login_required
def children_stock(request):
    data={'e':'','stock':[]}
    cs=Company.objects.filter(id=str(request.user.company.id))
    if not cs:
        data['e']=_('无效帐号')
    else:
        c=cs[0]
        if c.level.sn==0:
            sWhere=" where ancestor_id="+str(c.ancestor_id)+" and  company_id<>"+str(c.id)
        else:
            sWhere=" where company_id in (select id from company_company where id="+str(c.id)+" or parent_id="+str(c.id)+")"
    s="select (select name from company_company where id=b.company_id) company,"+\
        "(select name from product_product where id=b.product_id) product,"+\
        "b.x,b.h,b.p,b.company_id,b.product_id,"+\
        "(select name from company_level where id=(select level_id from company_company where id=b.company_id)) "+\
        " from (select a.product_id,a.company_id,sum(a.x) x,sum(a.h) h,sum(a.p) p from "+\
            "(select product_id,company_id,"+\
                "case when label_pkg in (3,4) then "+\
                    "case when vtype_id in (2,5) then -1 else 1 end "+\
                "else 0 end x,"+\
                "case when label_pkg=2 then "+\
                    "case when vtype_id in (2,5) then -1 else 1 end "+\
                "else 0 end h,"+\
                "case when label_pkg=1 then "+\
                    "case when vtype_id in (2,5) then -1 else 1 end "+\
                "else 0 end p "+\
                "from inventory_stkdetails"+sWhere+\
            ") a "+\
        "group by a.product_id,a.company_id) b "+\
        "order by company,product"
    cur=connection.cursor()
    cur.execute(s)    
    for r in cur.fetchall():
        qty=''
        a=[]
        if r[2]:
            a.append(str(r[2])+'箱')
        if r[3]:
            a.append(str(r[3])+'盒')
        if r[4]:
            a.append(str(r[4])+'瓶')
        if a:
            qty=",".join(a)   
      
        data['stock'].append([            
            "<a href='/inventory/stock/agent/"+str(r[5])+"/'>"+r[0]+"</a>", #agent
            r[7],                                                           #level
            r[1],                                                           #product           
            qty                                                             #qty            
        ])
       
    return render(request, 'inventory/stock/agents_stock.html',data)
    

@login_required
def agent_stock(request,agt_id):
    data={'e':'','data':[],'company':''}
    cs=Company.objects.filter(id=str(request.user.company.id))
    if not cs:
        data['e']=_('无效帐号')
    else:
        c=cs[0]
        if c.level.id>int(agt_id):
            data['e']=_('帐号层级错误')
            return JsonResponse(data)
        agts=Company.objects.filter(id=int(agt_id))
        if agts:
            data['company']=agts[0].name
    sWhere=" where company_id="+agt_id
    s="select b.tm,(select name from inventory_voucher where id=b.vtype_id),b.code,"+\
            "coalesce((select name from product_product where id=b.product_id),''),"+\
            "coalesce((select name from company_company where id=b.company_to_id),''),"+\
            "coalesce((select username from company_user where id=b.user_id),''),"+\
            "coalesce(d.x,0),coalesce(d.h,0),coalesce(d.p,0),b.id from "+\
            "(select id,tm,coalesce(code,'') code,user_id,product_id,company_to_id,vtype_id "\
                "from inventory_batch "+sWhere+") b "+\
            "left outer join "+\
            "(select a.batch_id,sum(a.x) x,sum(a.h) h,sum(a.p) p from "+\
                "(select batch_id,"+\
                    "case when label_pkg in (3,4) then 1 else 0 end x,"+\
                    "case when label_pkg=2        then 1 else 0 end h,"+\
                    "case when label_pkg=1        then 1 else 0 end p "+\
                    "from inventory_stkdetails"+sWhere+\
                ") a group by a.batch_id"+\
            ") d "+\
            "on b.id=d.batch_id"
    cur=connection.cursor()
    cur.execute(s)
    #"<a href='/inventory/batch/details/"+str(r[9])+"/'>"+r[0].strftime('%Y-%m-%d %H:%M:%S')+"</a>", #时间 
    for r in cur.fetchall():
        data['data'].append([
            r[0].strftime('%Y-%m-%d %H:%M:%S'), #时间 
            r[1],                               #单据类别
            r[2],                               #单据编号
            r[3],                               #产品名称
            r[4],                               #客户名称
            r[5],                               #操作人
            r[6],                               #箱
            r[7],                               #盒
            r[8]                                #瓶

        ])
    return render(request, 'inventory/stock/agent_stock.html',data)


def jg_label_segment(request):
    data={}
    r = requests.get('http://www.china315net.com:35088/jg/label/segment/') 
    data=r.json()
    return JsonResponse(data)


#红坛 高度柔和
def ht_gdrh_upload(request,batch_id):
    batch=Batch.objects.get(id=int(batch_id))
    pids=settings.JG_HT_GR
    data={'total':0,'cnt_direct':0,'cnt_bonus':0}
    if not batch.product_id in pids:
        return JsonResponse(data)
    d=pids[batch.product_id]    
    cn_al=pyodbc.connect(settings.CN_AL)  
    cn_xa=pyodbc.connect(settings.CN_XA)

    #测试
    postfix='_test'  
    postfix=''
    cur_xa= cn_xa.cursor()
    cur_al= cn_al.cursor()
    cur_al.execute("create table #temp (code varchar (20)) ")
    cn_al.commit()
    cur_xa.execute("create table #temp (code varchar (20)) ")
    cn_xa.commit()    
    cur=connection.cursor()
    s="select left(label_code,4),count(*) from inventory_stkdetails where batch_id="+str(batch.id)+"group by left(label_code,4)"
    cur.execute(s)
    rs=cur.fetchall()       
    for r in rs:        
        data['total']=data['total']+r[1]        
        tb="t"+r[0]
        #2020-08-21 后绑定红坛跨段
        cur.execute("select tbname FROM label.lbl_tbcross where segment='"+r[0]+"'")
        rr=cur.fetchone()
        if rr:
            tb=rr[0]
        s="select code from label."+tb+" where package_id=1 and parent_id in  ("+\
            "select label_pk_id from inventory_stkdetails where batch_id="+str(batch.id)+")"
        cur.execute(s)
        while True:
            bts=[]
            cs=cur.fetchmany(1000)
            if  cs:
                for c in cs:
                    bts.append("('"+c[0]+"')")
            else:
                break
            if bts:
                s="insert into #temp (code) values "+",".join(bts)
                cur_xa.execute(s)                   
                cn_xa.commit()
                cur_al.execute(s)
                cn_al.commit()  
    #跳转
    s="insert into TJ_JG_Activity"+postfix+" (labelcode,flag,tm,upload_by_jiugui) "+\
        "select a.code,'"+d['code']+"',getdate(),1 from "+\
        "(select code from #temp "+\
            "where (select labelcode from TJ_JG_Activity"+postfix+" where labelcode=code) is null "+\
            "group by code"+\
        ") a"
    
    cur_al.execute(s)
    cnt_direct=cur_al.rowcount
    data['cnt_direct']=cnt_direct
    cn_al.commit()
    #奖项
    s="insert into TB_BaseLabelInfo"+postfix+\
        "(LabelCode,YanZhengMa,JXID,JXName,Compid,DJFlag,Remarks,time,PID,upload_by_jiugui,jgflag) SELECT "+\
        "a.code,'000000',"+d['jxid']+",'"+d['jxname']+"',99,0,'"+str(batch.id)+"',convert(varchar(100),getdate(),120),'"+d['pid']+"',1,"+d['jgflag']+\
        " from (select code from #temp "+\
            "where (select labelcode from TB_BaseLabelInfo"+postfix+" where labelcode=code) is null "+\
            "group by code"+\
        ") a"
    
    cur_xa.execute(s)  
    cnt_bonus=cur_xa.rowcount
    data['cnt_bonus']=cnt_bonus
    cn_xa.commit()   
    batch.tm_bonus=datetime.datetime.now()
    batch.save()
    return JsonResponse(data)    



def app_childcompany(request,company_id):
    data={'e':'','children':[]}
    k = sK   
    e = time_key_check(request, k)
    if e:
        data['e'] = e
        return JsonResponse(data) 
    cs=Company.objects.filter(parent_id=int(company_id)).order_by('name')
    for c in cs:
        data['children'].append({
            'id':c.id,
            'name':c.name.split(' ')[0]
        })
    cs=Company.objects.filter(parent_id=None,ancestor_id=None)
    for c in cs:
        data['children'].append({
            'id':c.id,
            'name':c.name.split(' ')[0]
        })
    return JsonResponse(data)






#2020-03-27
@tj_login_required
def stk_err_list(request,company_id=None):    
    if not company_id:
        company_id=str(request.user.company.id)
    dt = datetime.datetime.now().strftime('%Y-%m-%d')
    dt_fr = request.GET.get('date_fr', dt)
    dt_to = request.GET.get('date_to', dt)
    dt_max = max(dt_fr, dt_to)
    dt_min = min(dt_fr, dt_to)
    dt_fr = dt_min
    dt_to = dt_max
    data={'e':'','data':[],'params': {'dt_fr': dt_fr, 'dt_to': dt_to }}
   

    sWhere=" where company_id="+company_id+" and (dt between '"+dt_fr+"' and '"+dt_to+"')" 
    s="select b.tm,b.code,b.cnt_batch,b.cnt_stk,"+\
            "(select name from inventory_voucher where id=b.vtype_id),"+\
            "coalesce((select name from product_product where id=b.product_id),''),"+\
            "coalesce((select name from company_company where id=b.company_to_id),''),"+\
            "coalesce((select name from inventory_storehouse where id=b.store_fr_id),''),"+\
            "coalesce((select username from company_user where id=b.user_id),''),"+\
            "b.id from "+\
            "(select id,tm,coalesce(code,'') code,user_id,product_id,company_to_id,vtype_id,store_fr_id,"\
                "(select count(*) from inventory_batchdetails where batch_id=a.id) cnt_batch,"+\
                "(select count(*) from inventory_stkdetails where   batch_id=a.id) cnt_stk "+\
                "from inventory_batch a "+sWhere+" and "+\
                    "(select count(*) from inventory_batchdetails where   batch_id=a.id)>"+\
                    "(select count(*) from inventory_stkdetails where   batch_id=a.id)"+\
            ") b order by b.tm"            
    cur=connection.cursor()
    cur.execute(s)    
    for r in cur.fetchall():
        data['data'].append([
            "<a href='/inventory/batch/details/"+str(r[9])+"/'>"+r[0].strftime('%Y-%m-%d %H:%M:%S')+"<a/>", #时间 
            r[4],                               #单据类别
            r[1],                               #单据编号
            r[5],                               #产品名称
            r[6],                               #客户名称
            #r[8],                               #操作人
            r[7],                               #仓库
            r[2],                               #batch_cnt
            #r[3],                               #stk_cnt
            r[2]-r[3]                           #差异
        ])    
    return render(request, 'inventory/stock/error_list.html',data)


#2020-03-27
@tj_login_required
def stk_err_json(request,company_id=None):    
    if not company_id:
        company_id=str(request.user.company.id)
    dt = datetime.datetime.now().strftime('%Y-%m-%d')
    dt_fr = request.GET.get('date_fr', dt)
    dt_to = request.GET.get('date_to', dt)
    dt_max = max(dt_fr, dt_to)
    dt_min = min(dt_fr, dt_to)
    dt_fr = dt_min
    dt_to = dt_max
    data={'e':'','data':[]}

    sWhere=" where company_id="+company_id+" and (dt between '"+dt_fr+"' and '"+dt_to+"')" 
    s="select b.tm,b.code,b.cnt_batch,b.cnt_stk,"+\
            "(select name from inventory_voucher where id=b.vtype_id),"+\
            "coalesce((select name from product_product where id=b.product_id),''),"+\
            "coalesce((select name from company_company where id=b.company_to_id),''),"+\
            "coalesce((select name from inventory_storehouse where id=b.store_fr_id),''),"+\
            "coalesce((select username from company_user where id=b.user_id),''),"+\
            "b.id from "+\
            "(select id,tm,coalesce(code,'') code,user_id,product_id,company_to_id,vtype_id,store_fr_id,"\
                "(select count(*) from inventory_batchdetails where batch_id=a.id) cnt_batch,"+\
                "(select count(*) from inventory_stkdetails where   batch_id=a.id) cnt_stk "+\
                "from inventory_batch a "+sWhere+" and "+\
                    "(select count(*) from inventory_batchdetails where   batch_id=a.id)>"+\
                    "(select count(*) from inventory_stkdetails where   batch_id=a.id)"+\
            ") b order by b.tm"            
    cur=connection.cursor()
    cur.execute(s)    
    for r in cur.fetchall():
        data['data'].append([
            "<a href='/inventory/batch/details/"+str(r[9])+"/'>"+r[0].strftime('%Y-%m-%d %H:%M:%S')+"<a/>", #时间 
            r[4],                               #单据类别
            r[1],                               #单据编号
            r[5],                               #产品名称
            r[6],                               #客户名称
            #r[8],                               #操作人
            r[7],                               #仓库
            r[2],                               #batch_cnt
            #r[3],                               #stk_cnt
            r[2]-r[3]                           #差异
        ])    
    return JsonResponse(data)



#2020-06-19
@tj_login_required
def stk_product_err(request):    
    company_id=str(request.user.company.id)
    dt = datetime.datetime.now().strftime('%Y-%m-%d')
    dt_fr = request.GET.get('date_fr', dt)
    dt_to = request.GET.get('date_to', dt)
    dt_max = max(dt_fr, dt_to)
    dt_min = min(dt_fr, dt_to)
    dt_fr = dt_min
    dt_to = dt_max    
    return render(request, 'inventory/stock/product_error_list.html',{'dt_fr': dt_fr, 'dt_to': dt_to})


#2020-06-19
@tj_login_required
def stk_product_err_ajax(request):
    data=[]
    dt = datetime.datetime.now().strftime('%Y-%m-%d')
    dt_fr = request.GET.get('date_fr', dt)
    dt_to = request.GET.get('date_to', dt)
    dt_max = max(dt_fr, dt_to)
    dt_min = min(dt_fr, dt_to)
    dt_fr = dt_min
    dt_to = dt_max    
    cid=str(request.user.company_id)
    cur=connection.cursor()
    s="select f.id,to_char(f.tm,'yyyy-mm-dd HH24:MI:SS'),f.code,"+\
        "(select username from company_user where id=f.user_id),"+\
        "(select name from company_company where id=f.company_to_id),"+\
        "(select count(*) from inventory_stkdetails where  batch_id=e.batch_id) cnt,"+\
        "(select count(*) from inventory_stkdetails z where  batch_id=e.batch_id and product_id<>"+\
            "(select product_id from inventory_stkdetails where label_pk_id=z.label_pk_id and company_id="+cid+" and vtype_id=1 and is_delete=false)),"+\
        "(select name from product_product where id=e.product_id_a) ck_product,"+\
        "(select name from product_product where id=e.product_id_b) rk_product "+\
        "from "+\
        "(select d.batch_id,d.product_id_a,d.product_id_b from "+\
	    "(select (select batch_id from inventory_stkdetails where company_id="+cid+" and vtype_id=2 and label_id=c.label_id),"+\
                "c.product_id_a,c.product_id_b from "+\
		"(select a.label_id,a.product_id product_id_a,b.product_id product_id_b from "+\
		    "(SELECT label_id,product_id FROM inventory_stkdetails where company_id="+cid+" and vtype_id=2 "+\
                        "and (dt between '"+dt_fr+"' and '"+dt_to+"') group by label_id,product_id) a "+\
		    "left outer join "+\
		    "(SELECT label_id,product_id  FROM inventory_stkdetails where company_id="+cid+\
                        " and vtype_id=1 and is_delete=false group by label_id,product_id) b "+\
		    "on a.label_id=b.label_id "+\
		    "where a.product_id<>b.product_id "+\
		") c "+\
	    ") d "+\
	    "group by d.batch_id,d.product_id_a,d.product_id_b "+\
        ") e "+\
        "left outer join "+\
        "(select id,tm,user_id,company_to_id,code from inventory_batch) f "+\
        "on e.batch_id=f.id"
    cur.execute(s)
    for r in cur.fetchall():
        data.append([ "<a href='/inventory/stock/err/product/batch/"+str(r[0])+"/'>"+r[1]+"</a>",r[2],r[3],r[4],r[5],r[6],r[7],r[8] ])       
    return JsonResponse(data,safe=False)


@tj_login_required
def stk_product_err_batch(request,id):
    cur=connection.cursor()
    s="select tm,coalesce(code,''),"+\
        "coalesce((select name from inventory_voucher where id=vtype_id),''),"+\
        "coalesce((select name from product_product where id=product_id),''),"+\
        "coalesce((select name from inventory_storehouse where id=store_fr_id),''),"+\
        "coalesce((select name from inventory_storehouse where id=store_to_id),''),"+\
        "coalesce((select username from company_user where id=user_id),''),"+\
        "coalesce((select name from company_company where id=company_to_id),''),"+\
        "vtype_id,"+\
        "coalesce((select name from company_prdplant where id=prd_plant_id),'') "+\
        "from inventory_batch where id="+id    
    cur.execute(s)
    r=cur.fetchone()
    if r:
        batch={'time':r[0].strftime('%Y-%m-%d %H:%M:%S'),'code':r[1],'voucher':r[2],'id':id,
                'product':r[3],'store_fr':r[4],'store_to':r[5],'user':r[6],'customer':r[7],'vid':r[8],'plant':r[9]}    
        s="SELECT b.label_code,"+\
            "(select name from product_product where id=b.pid_ck) product_ck,"+\
            "(select name from product_product where id=b.pid_rk) product_rk from "+\
            "(select label_code,label_pkg,product_id pid_ck,"+\
                "(select product_id from inventory_stkdetails where label_pk_id=a.label_pk_id and vtype_id=1 and company_id=a.company_id and is_delete=false)"+\
                " pid_rk FROM inventory_stkdetails a where batch_id="+id+" and "+\
                "product_id<>(select product_id from inventory_stkdetails where label_pk_id=a.label_pk_id and vtype_id=1 and company_id=a.company_id and is_delete=false)"+\
            ") b"
        cur.execute(s)
        dataset=[]
        for r in cur.fetchall():
            dataset.append([ r[0],r[1],r[2] ])
        
    return render(request,'inventory/stock/product_error_batch.html',{'batch':batch,'dataset':dataset})




#2020-04-27
#酒鬼瓶盖
def bottlecap(request):    
    code=request.GET.get('label','')    
    data = {
        'e': '',
        'code': code,
        'product': '',
        'package': '',
        'company': '',
        'replace': '',
        'boxes': {'pk': '', 'related': []},
        'packets': [],
        'bottles': [],
        'stock_dev': [],
        'replace':''
    }
    if not code:
        data['e']='标签不能为空'
        return JsonResponse(data) 
    k = sK   
    e = time_key_check(request, k)
    if e:
        data['e'] = e
        return JsonResponse(data) 
    
    cur=connection.cursor()
    s="SELECT (SELECT  box  FROM jg_bottlecap.replace where code=lbl) "+\
        "FROM jg_bottlecap.binding where label='"+code+"'"
    cur.execute(s)
    r=cur.fetchone()
    if r:
        c=r[0]
    else:
        c=code
    
    if c:
        return label_info(c)
    return JsonResponse(data)

    


#2020-04-22
#@login_required
@tj_login_required
@csrf_exempt
def upload_inv(request):
    
    data={'e':'','batch':{},'store_fr':[],'store_to':[],'product':[],'company_to':[]}     
    company_id=request.user.company.id
    for o in Storehouse.objects.filter(company_id=company_id).order_by('name'):       
        data['store_fr'].append({'label': o.name, 'value': o.id})
        #data['store_to'].append({'label': o.name, 'value': o.id})
    for o in Product.objects.filter(company_id=company_id).order_by('name'):
        data['product'].append({'label': o.name, 'value': o.id})
    for o in Company.objects.filter(parent_id=company_id).order_by('name'):
        data['company_to'].append({'label': o.name, 'value': o.id})


    return render(request, 'inventory/upload_inv_txt.html', data)    



#2020-04-22
#@login_required
@tj_login_required
def upload_inv_txt(request,is_app=False):    
    data = {'files': [], 'e': ''}    
    customer_id=request.POST.get('customer_id','0')
    product_id=request.POST.get('product_id','0')
    store_fr_id=request.POST.get('store_fr_id','0')  
    store_to_id=request.POST.get('store_to_id','0')
    v_id=request.POST.get('voucher_id','2')   
    p_id=request.POST.get('plant_id','0')   
    clmn=request.POST.get('column','5')    
    u=request.user
    c = u.company
    u_id=u.id
    c_id=u.company_id    
    code=request.POST.get('code','')     
    dt = datetime.datetime.now() 
    c_name = c.name
    p = settings.MEDIA_ROOT + '/' + c_name + '/txt/inv_txt/' +  \
        str(dt.year) + str(dt.month).zfill(2) + str(dt.day).zfill(2)
    if not os.path.exists(p):
        os.makedirs(p)
    files = request.FILES.getlist('files') 
    a=[]
    for f in files:
        cnt_tt=0
        cnt_ok=0
        fn = f.name
        with open(p + '/' + fn, 'wb+') as destination:
            for chunk in f.chunks():
                destination.write(chunk)   
        a=[]        
        for l in open(p + '/' + fn,'r'):
            l=l.strip()
            if l:
                aa=l.split(',')
                l=aa[int(clmn)-1]
                a.append(l)
    
    t=dt.strftime('%Y-%m-%d %H:%M:%S')
    m2=hashlib.md5((sK+t).encode('utf8'))

    data={
            "bill_number":code,
            "company_id":c_id,
            "to_company_id":int(customer_id),
            "user_id":u_id,
            "store_id":int(store_fr_id),
            "product_id":int(product_id), 
            "voucher_id":int(v_id),     
            "voucher_time":t,            
            "codes":a,
            "time":t,
            "key":m2.hexdigest(),
            "force": True,  # 2020-08-31 强制发货（不检查标签是否已有发货记录）
    } 
    
    request._body=json.dumps(data)
    request.META['CONTENT_TYPE']='application/json'
    return fg_update(request)
    




@tj_login_required
@csrf_exempt
def stk_transfer(request):
    customer=[]
    u=request.user
    if not u.company:
        return JsonResponse({'e':_('')})
    cur=connection.cursor()
    if request.method=='GET':
        sWhere=' where parent_id='+str(u.company.id)
        if u.company.level_id==1:
            sWhere=' where ancestor_id='+str(u.company.id)
        s="select id,name from logistics.company_company "+sWhere       
        cur.execute(s)
        for r in cur.fetchall():
            customer.append({'label': r[1], 'value': r[0]})
    else:       
        id_o=str(request.POST.get('company_id_old'))
        id_n=str(request.POST.get('company_id_new'))  
        a_s=[   
            "update logistics.inventory_batchdetails set company_id="+id_n+" where company_id="+id_o,           
            "update logistics.inventory_stkdetails set company_to_id="+id_n+" where company_to_id="+id_o,    
            "update logistics.inventory_stkdetails set company_id="+id_n+" where company_id="+id_o,
            "update logistics.inventory_batch set company_to_id="+id_n+" where company_to_id="+id_o,            
            "update logistics.inventory_batch set company_fr_id="+id_n+" where  company_fr_id="+id_o,
            "update logistics.inventory_batch set company_id="+id_n+" where company_id="+id_o
        ]
        cnt=0
        for s in a_s:
            cur.execute(s)
            cnt=max(cnt,cur.rowcount)        
        return JsonResponse({'e':'','cnt':cnt})
    return render(request, 'inventory/stk_transfer.html', {'company':customer}) 



'''
def app_stock_details(request,company_id):
    data={'e':'','stock':[]}
    k = sK   
    e = time_key_check(request, k)
    if e:
        data['e'] = e
        return JsonResponse(data) 
    sWhere=" where company_id="+company_id    
    s="select"+\
        "(a.batch_id,sum(a.x) x,sum(a.h) h,sum(p) p from "+\        
            "(select batch_id,"+\
                "case when label_pkg in (3,4) then "+\
                    "case when vtype_id in (2,5) then -1 else 1 end "+\
                    "else 0 end x,"+\
                "case when label_pkg=2 then "+\
                    "case when vtype_id in (2,5) then -1 else 1 end "+\
                    "else 0 end h,"+\
                "case when label_pkg=1 then "+\
                    "case when vtype_id in (2,5) then -1 else 1 end "+\
                    "else 0 end p "+\
                "from inventory_stkdetails"+sWhere+\
            ") a  "+\
        "group by a.batch_id) b "+\
        "left outer join "+\
        "(select product_id,)"

        "order by name"   
    cur=connection.cursor()
    cur.execute(s)
    for r in cur.fetchall():
        data['stock'].append({
            "product":r[0],
            "qty_x":r[1],
            "qty_h":r[2],
            "qty_p":r[3]
        })
    return JsonResponse(data)






def binding_upload(request):
    data = {'files': [], 'e': ''}
    p = settings.MEDIA_ROOT
    u_id = str(request.user.id)
    c = request.user.company
    c_name = c.name
    if not os.path.exists(p + '/txt/labelbase/' + c_name):
        os.makedirs(p + '/txt/labelbase/' + c_name)
    c_id = str(c.id)
    files = request.FILES.getlist('files')
    cur = connection.cursor()
    for f in files:
        fn = f.name
        # if fn[:11].upper() == 'lbl_replace':
        with open(p + '/txt/labelbase/' + c_name + '/' + fn, 'wb+') as destination:
            for chunk in f.chunks():
                destination.write(chunk)
        f.seek(0)
        cur.execute("create temp table tmp(code0 character varying(20),code character varying(20))")
        cur.copy_from(f, 'tmp', sep=',', null='')
        # 写入BATCH
        s = "insert into label.lbl_replace_batch(user_id,company_id,txt_file) values " + \
            "(" + u_id + "," + c_id + ",'" + fn + "') returning id"
        cur.execute(s)
        id = str(cur.fetchone()[0])
        cur.execute("select left(code0,4) from tmp group by left(code0,4)")
        rs = cur.fetchall()
        for r in rs:


'''



#2020-08-12
# @login_required
@tj_login_required
@csrf_exempt
def query_label(request):
    dt_data = {'data':[],'clmns':[_('标签号码'),_('标签类型'),_('产品名称'),_('套标关系'),_('物流信息')],'e':[]}
    if request.method == 'GET':
        return render(request, 'inventory/label_txt_query_copy.html', dt_data)
    else:
        d = {'labels': [], 'e': ''}
        f = request.FILES.get('file')
        l = f.read().decode().splitlines()
        for n in l:
            d['labels'].append(n)
        for i in d['labels']:
            url = 'https://davis.china315net.com/label/app/1/' + i + '/'
            r = requests.get(url)
            dd=r.json()
            boxes=''
            #是否有关联箱标
            if dd['boxes']['related']:
                boxes="箱标:"+dd['boxes']['pk']+",关联箱标:"+",".join(dd['boxes']['related'])
            else:
                boxes="箱标:"+dd['boxes']['pk']
            cs=''
            #箱-盒-瓶 三级对应
            if dd['packets']:
                chlds=[]
                for c in dd['packets']:
                    chlds.append(c['code']+"(盒)->"+",".join(c['bottles'])+"(瓶)")
                cs=";"+",".join(chlds)
            #箱-瓶f二级对应
            elif dd['bottles']:
                cs=";瓶标："+",".join(dd['bottles'])
            wl=[]
            sal=[]
            for w in dd['stock_dev']:
                if w['voucher_type']=='销售出库':
                    sal.append(w['time']+"->"+w['to_company']+"(单号"+w['voucher_no']+" 操作员:"+w['user']+
                               " 标签号码"+w['label_code']+"["+w['package']+"])")
                else:
                    wl.append(w['time']+":"+w['voucher_type']+"(单号"+w['voucher_no']+";操作员:"+\
                        w['user']+";"+w['store']+";标签号码"+w['label_code']+"["+w['package']+"]"+")"
                    )
            dt_data['data'].append([  i,dd['package'], dd['product'], boxes+cs, ";".join(sal)+";".join(wl) ])
        return JsonResponse(dt_data)



# 2020/7/24  经销商出入库查询
@tj_login_required
def agent_stk_query(request):
    dt = datetime.datetime.now().strftime('%Y-%m-%d')
    dt_fr = request.GET.get('date_fr', dt)
    dt_to = request.GET.get('date_to', dt)
    data=[]
    agent_id = request.GET.get("agent_id", "")
    if agent_id=="":
        return render(request, 'inventory/agent_stk_query.html',
                      {'data': json.dumps(data),
                       'date_fr': dt_fr,
                       'date_to': dt_to,
                       })
    else:
        sWhere="where company_id = "+agent_id+" and (dt between '"+dt_fr+"' and '"+dt_to+"')"

        s = "select b.tm,(select name from logistics.inventory_voucher where id=b.vtype_id),b.code," + \
            "coalesce((select name from logistics.product_product where id=b.product_id),'')," + \
            "coalesce((select name from logistics.company_company where id=b.company_to_id),'')," + \
            "coalesce((select username from logistics.company_user where id=b.user_id),'')," + \
            "coalesce(d.x,0),coalesce(d.h,0),coalesce(d.p,0),b.id from " + \
            "(select id,tm,coalesce(code,'') code,user_id,product_id,company_to_id,vtype_id " \
                "from inventory_batch " + sWhere + ") b " + \
            "left outer join " + \
            "(select a.batch_id,sum(a.x) x,sum(a.h) h,sum(a.p) p from " + \
                "(select batch_id," + \
                    "case when label_pkg in (3,4) then 1 else 0 end x," + \
                    "case when label_pkg=2        then 1 else 0 end h," + \
                    "case when label_pkg=1        then 1 else 0 end p " + \
                    "from logistics.inventory_stkdetails " + sWhere + \
                ") a group by a.batch_id" + \
            ") d " + \
            "on b.id=d.batch_id"
        cur = connection.cursor()
        cur.execute(s)
        for r in cur.fetchall():
            data.append([
                     "<a href='inventory/agent/details/" + str(r[9]) + "/'>" + str(r[0].strftime('%Y-%m-%d %H:%M:%S')) + "</a>",  # agent
                r[1],  # 单据类别
                r[2],  # 单据编号
                r[3],  # 产品名称
                r[4],  # 客户名称
                r[5],  # 操作人
                r[6],  # 箱
                r[7],  # 盒
                r[8]   # 瓶
             ])
        cur.execute("select (select name from company_level where id=level_id),"+\
            "(select name from company_company where id=a.parent_id),name from company_company a where id = "+agent_id+"")
        r=cur.fetchone()
        parent=r[1]
        level=r[0]
        cur.close()
        return render(request, 'inventory/agent_stk_query.html',
                      {'data':json.dumps(data),
                       'date_fr': dt_fr,
                       'date_to': dt_to,
                       "level":level,
                       "parent":parent,
                       "agent_id":agent_id,
                       'agent_name':r[2]
                       })



@tj_login_required
def agent_stk_query_details(request,id):
    data = {'e': '', 'batch': {}, 'dataset': []}
    cur = connection.cursor()
    s = "select tm,coalesce(code,'')," + \
        "coalesce((select name from logistics.inventory_voucher where id=vtype_id),'')," + \
        "coalesce((select name from logistics.product_product where id=product_id),'')," + \
        "coalesce((select name from logistics.inventory_storehouse where id=store_fr_id),'')," + \
        "coalesce((select name from logistics.inventory_storehouse where id=store_to_id),'')," + \
        "coalesce((select username from logistics.company_user where id=user_id),'')," + \
        "coalesce((select name from logistics.company_company where id=company_to_id),'')," + \
        "vtype_id," + \
        "coalesce((select name from logistics.company_prdplant where id=prd_plant_id),'') " + \
        "from inventory_batch where id=" + id
    cur.execute(s)
    r = cur.fetchone()
    if r:
        data['batch'] = {'time': r[0].strftime('%Y-%m-%d %H:%M:%S'), 'code': r[1], 'voucher': r[2], 'id': id,
                         'product': r[3], 'store_fr': r[4], 'store_to': r[5], 'user': r[6], 'customer': r[7],
                         'vid': r[8], 'plant': r[9]}

        # 标签退库
        if r[8] == 5:
            s = "select a.label_code," + \
                "coalesce((SELECT name FROM logistics.label.lbl_package where id=a.label_pkg),a.err,'错误')" + \
                " from " + \
                "(select label_code,label_pkg,err from logistics.inventory_batchdetails where batch_id=" + id + ") a "

        else:
            s = "select a.label_code," + \
                "coalesce((SELECT name FROM label.lbl_package where id=b.label_pkg),a.err,'错误')" + \
                " from " + \
                "(select label_code,err from logistics.inventory_batchdetails where batch_id=" + id + ") a " + \
                "left outer join " + \
                "(select label_code,label_pkg from logistics.inventory_stkdetails where batch_id=" + id + ") b " + \
                "on a.label_code=b.label_code"
        cur.execute(s)

        for r in cur.fetchall():
            data['dataset'].append([r[0], r[1]])
        cur.close()
    data['dataset'] = SafeString(data['dataset'])
    return render(request, "inventory/label_query_details.html", data)



#2020-09-01
@login_required
def ajax_geo(request,obj,company_id=None):
    cur=connection.cursor()
    if request.method=='GET':        
        center={'lng':116.404,'lat':39.915}        
        zoom=11        
        province_gis_id=request.GET.get('adm1_id','')
        city_gis_id=request.GET.get('adm2_id','')
        county_gis_id=request.GET.get('adm3_id','')
       
        store_geos=[]

        geo_id=''
        if county_gis_id and county_gis_id!='null':   #区/县
            zoom=14
            geo_id=county_gis_id
        elif city_gis_id and city_gis_id!='null':
            zoom=12
            geo_id=city_gis_id
        elif province_gis_id and province_gis_id!='null':
            zoom=10
            geo_id=province_gis_id
        if geo_id:
            s="select public.ST_X(public.ST_Centroid(geom)),public.ST_Y(public.ST_Centroid(geom)) FROM gis.adm where id="+geo_id            
            cur.execute(s)
            r=cur.fetchone()
            if r:
                center={'lng':r[0],'lat':r[1]}
        lng_lat=[]
        if company_id:
            cur.execute("select lng_lat from company_company where id="+company_id)
            r=cur.fetchone()
            if r:
                if r[0]:
                    lng_lat=[r[0][0],r[0][1]]
        if obj=='store':
            html='storehouse_geo.html'
            if company_id:
                c=Company.objects.get(id=int(company_id))
                cur.execute("select id,lng_lat,tm,tm_checked,"+\
                    "coalesce((select username from company_user where id=checked_by_id),'') "+\
                    "from inventory_storehousebd09 where company_id="+company_id
                )   
                for r in cur.fetchall():
                    tm_ck=''
                    if r[3]:
                        tm_ck=r[3].strftime('%Y-%m-%d %H:%M:%S')
                    store_geos.append({
                        'id':r[0],
                        'sn':str(r[0]).zfill(8),
                        'lnglat':[r[1][0],r[1][1]],
                        'tm':r[2].strftime('%Y-%m-%d %H:%M:%S'),
                        'tm_checked':tm_ck,
                        'checked_by':r[4]                        
                    })
            if store_geos:
                g=store_geos[0]
                center={'lng':g['lnglat'][0],'lat':g['lnglat'][1]}
                zoom=13
        elif obj=='agent':
            html='agent_geo.html'
            if company_id:
                #print(company_id)
                cur.execute("select id,lng_lat,tm,tm_checked,"+\
                    "coalesce((select username from company_user where id=checked_by_id),'') "+\
                    "from inventory_storehousebd09 where company_id="+company_id
                )
                for r in cur.fetchall():
                    tm_ck=''
                    if r[3]:
                        tm_ck=r[3].strftime('%Y-%m-%d %H:%M:%S')
                    store_geos.append({
                        'id':r[0],
                        'sn':str(r[0]).zfill(8),
                        'lnglat':[r[1][0],r[1][1]],
                        'tm':r[2].strftime('%Y-%m-%d %H:%M:%S'),
                        'tm_checked':tm_ck,
                        'checked_by':r[4]
                    })
        data={'obj':obj,'center':center,'zoom':zoom,'stores':store_geos,'lng_lat':lng_lat}       
        return render(request, "inventory/"+html,data)





#2020-11-19 最终版
def channel_rebate(batch):
    distance='99999999.99'
    data={
        'promotion_id':0,
        'amount':0,
        'distance_lmt':distance
    }
    #公司级扫码不计算渠道返利
    amt=0.00    
    #2020-11-08 已经扫过瓶标这次又扫了箱标 对应的返利金额（箱标会计入返利，原瓶标返利记录删除）    
    amt_neg=0.00

    c=Company.objects.get(id=batch.company_id)   
    if c.level.id==1:
        return data
    cur=connection.cursor()  
    t=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    cid=str(c.id)
    aid=str(c.ancestor_id)
    bid=str(batch.id)
    vid=str(batch.vtype_id)
    if batch.distance or batch.distance==0:
        distance=str(batch.distance)
    agt_id='0'
    saldiv_id='0'
    if c.division:
        saldiv_id=str(c.division_id)        
    subdiv_id='0'
    if c.sub_division:
        subdiv_id=str(c.sub_division_id)
    if batch.company_to_id:
        agt_id=str(batch.company_to_id) 
    
    if batch.vtype_id==1:
        sAgt=""
    elif batch.vtype_id==2:
        sAgt=" and case when array_length(agents,1) is null then true else ARRAY["+agt_id+"] <@ agents end"    
    
    #2020-11-19
    sProductdA="ARRAY(SELECT product_id from inventory_stkdetails where batch_id="+bid+" group by product_id)"

    s="select b.id,b.priority,a.val,a.pvals,b.package_unit,a.id,a.distance,b.products,b.dt_delivery from "+\
            "("+\
                "SELECT val,level_id,promotion_id,pvals,id,distance FROM sales_channelpromtionsrebate "+\
                    " where level_id="+str(c.level_id)+" and vtype_id="+vid+\
            ") a "+\
            "left outer join ("+\
                "select id,name,awardcategory_id,package_unit,priority,products,dt_delivery from sales_channelpromotions "+ \
                "where company_id="+aid+" and ('"+t+"' between tm_start and tm_end) and status=1 "+\
                "and case when array_length(products,1)   is null then true else ("+sProductdA+" && products) end "+\
                "and ("+\
                    "case when array_length(div_ids,1)    is null then true else ARRAY["+saldiv_id+"] <@ div_ids end "+\
                    "or "+\
                    "case when array_length(subdiv_ids,1) is null then true else ARRAY["+subdiv_id+"] <@ subdiv_ids end"+\
                ") "+\
                sAgt+\
            ") b "+\
            "on a.promotion_id=b.id "+\
            "where b.id is not null and a.id is not null order by b.priority desc limit 1"   

    


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

    #2020-11-06 主箱标所在箱最后一次从公司发货的日期
    cDt="(select max(dt) from inventory_stkdetails where label_pk_id=a.label_pk_id and company_id="+aid+" and vtype_id=2) "
    bBoxbottle=False
    v=0.00
    if r:
        v=float(r[2])   #单位返利金额
        u=r[4]          #计算返利的包装单位：1瓶，3箱
        rid=r[5]        #返利明细ID
        pid=r[0]        #返利活动ID
        d=r[6]          #返利扫码距离限定 
        es_dt=r[8]      #最早发货时间（公司）
        data['promotion_id']=pid          
        if v>0.00:        
            #删除已有返利记录
            for bth in ChannelRebateBatch.objects.filter(batch_id=batch.id):
                bth.delete()
            #2020-11-19
            #删除已有箱/瓶置换记录
            for sbt in BoxBottleSubstitute.objects.filter(stk_batch_id=batch.id):
                sbt.delete()

            sDt=''
            if es_dt:
                sDt=" and "+cDt+">='"+es_dt.strftime('%Y-%m-%d')+"'"
            storegeo_id='null'
            if batch.storegeo_id:
                storegeo_id=str(batch.storegeo_id)
            if not d:                           #未限定扫码距离
                checked_by='1'
                tm_checked="'"+datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')+"'"
                confirmed='true'
            else:                               #返利活动限定了扫码距离
                data['distance_lmt']=d
                if int(float(distance))<=d:     #扫码距离在限定范围内
                    checked_by='1'
                    tm_checked="'"+datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')+"'"
                    confirmed='true'
                else:                           #不在限定范围内，或未上传扫码距离
                    checked_by='null'
                    tm_checked='null'
                    confirmed='false'
            tb="tmp_details"
            #创建临时表，计箱返利明细
            s="create temp table "+tb+"(label_code character varying(20),product_id integer,"+\
                "label_id bigint,label_pk_id bigint,label_pkg smallint,val numeric(12,2),dt_delivery date)"
            cur.execute(s)

            #2020-11-08 箱瓶置换 临时表
            tb_s="rebatesubstitute"
            s="CREATE temp TABLE "+tb_s+"(ancestor_id integer,company_id integer,vtype_id smallint,stk_batch_id integer,"+\
                "label_id bigint,label_pk_id bigint,label_bottle character varying(20),label_box character varying(20),"+\
                "product_id integer,rebate_batch_id integer,stk_batch_old_id integer,rebate_batch_old_id integer,val numeric(12,2))"
            cur.execute(s)

            ps=[]
            if r[7]:
                for p in r[7]:
                    ps.append(str(p))
                sProduct=" and product_id in ("+",".join(ps)+")"
            else:
                sProduct=''
            if u==1:
                pval=str(0.0+v)
                xval="coalesce((select qty_p from product_product where id=product_id),1)*"+str(0.0+v)
            elif u==3:
                pval=str(0.0+v)+"/coalesce((select qty_p from product_product where id=product_id),1)"
                xval=str(0.0+v)
            s="insert into "+tb+" (label_code,product_id,label_id,label_pk_id,label_pkg,val,dt_delivery) select "+\
                "label_code,product_id,label_id,label_pk_id,label_pkg,"+\
                "case when label_pkg=1 then "+pval+" else "+xval+" end,"+cDt+\
                "from inventory_stkdetails a where batch_id="+bid+sProduct+sDt                

            cur.execute(s)
            cnt=cur.rowcount
            if cnt:    
                cur.execute("select sum(val) from "+tb)
                r=cur.fetchone()
                #有距离限定的，在限定距离内
                if confirmed=='true':
                    amt=r[0]
                vals=str(r[0])
                s="insert into sales_channelrebatebatch "+\
                    "(company_id,company_src_id,promotion_id,batch_id,dt,tm,val,checked_by_id,tm_checked,confirmed,distance,storegeo_id,ancestor_id,vtype_id) values ("+\
                        cid+","+cid+","+str(pid)+","+bid+",now(),now(),"+vals+","+checked_by+","+tm_checked+","+confirmed+","+distance+","+storegeo_id+","+aid+","+vid+\
                    ")  returning id"
                cur.execute(s)
                rt=cur.fetchone()
                id=str(rt[0])

                #2020-11-07
                #返利明细中已存在的，包含在此次扫码上传的箱标里的瓶标返利明细复制到箱-瓶置换表
                s="insert into "+tb_s+"(ancestor_id,company_id,vtype_id,stk_batch_id,rebate_batch_id,product_id,"+\
                    "stk_batch_old_id,rebate_batch_old_id,label_id,label_pk_id,label_bottle,val,label_box) select "+\
                    aid+","+cid+","+vid+","+bid+","+id+",product_id,stk_batch_id,rebate_batch_id,label_id,label_pk_id,label_code,val,"+\
                    "(select label_code from "+tb+" where label_pk_id=a.label_pk_id limit 1) from sales_channelrebatedetails a where "+\
                    "company_id="+cid+" and vtype_id="+vid+" and label_pkg=1 "+\
                    "and label_pk_id in (select label_pk_id from "+tb+")"   
                cur.execute(s)
                cur.execute("select sum(val) from "+tb_s)
                r=cur.fetchone()
                if r[0]:
                    amt_neg=r[0] 
                    #2020-11-09 修改BATCH返利金额
                    cur.execute("update sales_channelrebatebatch set val=val-"+str(float(r[0]))+" where id="+id)   
                    
                    s="INSERT INTO inventory_boxbottlesubstitute(ancestor_id,company_id,vtype_id,product_id,stk_batch_id,rebate_batch_id,"+\
                        "stk_batch_old_id,rebate_batch_old_id,val,label_id,label_pk_id,label_bottle,label_box) select "+\
                        "ancestor_id,company_id,vtype_id,product_id,stk_batch_id,rebate_batch_id,stk_batch_old_id,"+\
                        "rebate_batch_old_id,val,label_id,label_pk_id,label_bottle,label_box from "+tb_s
                    cur.execute(s)
                    #2020-11-16
                    bBoxbottle=True
                
                s="INSERT INTO sales_channelrebatedetails("+\
                    "label_id,label_pk_id,label_code,label_pkg,val,product_id,rebate_batch_id,stk_batch_id,dt_delivery,company_id,vtype_id) select "+\
                    "label_id,label_pk_id,label_code,label_pkg,val,product_id,"+id+","+bid+",dt_delivery,"+cid+","+vid+" from "+tb
                cur.execute(s)       

                #父级返利
                s="select recursion, val, level_id from sales_parentrebate where rebate_id="+str(rid)
                cur.execute(s)
                for r in cur.fetchall():
                    v=float(r[1])
                    if v>0.00:
                        p=c
                        for i in range(r[0]):
                            if p.parent:
                                p=p.parent
                        if p.level_id==r[2]:
                            #清空临时表
                            cur.execute("truncate table "+tb)
                            cur.execute("truncate table "+tb_s)
                            if u==1:
                                pval=str(0.0+v)
                                xval="coalesce((select qty_p from product_product where id=product_id),1)*"+str(0.0+v)
                            elif u==3:
                                pval=str(0.0+v)+"/coalesce((select qty_p from product_product where id=product_id),1)"
                                xval=str(0.0+v)
                            s="insert into "+tb+" (label_code,product_id,label_id,label_pk_id,label_pkg,val,dt_delivery) select "+\
                                "label_code,product_id,label_id,label_pk_id,label_pkg,"+\
                                "case when label_pkg=1 then "+pval+" else "+xval+" end,"+cDt+\
                                "from inventory_stkdetails a where batch_id="+bid+sProduct+sDt
                            cur.execute(s)
                            cnt=cur.rowcount
                            if cnt:
                                cur.execute("select sum(val) from "+tb)
                                r=cur.fetchone()
                                vals=str(r[0])
                                s = "insert into sales_channelrebatebatch "+\
                                    "(company_id,company_src_id,promotion_id,batch_id,dt,tm,val,checked_by_id,tm_checked,confirmed,distance,storegeo_id,ancestor_id,vtype_id) values (" + \
                                    str(p.id)+","+cid+","+str(pid)+","+bid+",now(),now(),"+vals+","+checked_by+","+tm_checked+","+confirmed+","+distance+","+storegeo_id+","+aid+","+vid+\
                                    ") returning id"
                                cur.execute(s)
                                rt=cur.fetchone()
                                id=str(rt[0])
                                
                                #2020-11-07
                                #返利明细中已存在的，包含在此次扫码上传的箱标里的瓶标返利明细复制到箱-瓶置换表
                                s="insert into "+tb_s+"(ancestor_id,company_id,vtype_id,stk_batch_id,rebate_batch_id,product_id,stk_batch_old_id,"+\
                                    "rebate_batch_old_id,label_id,label_pk_id,label_bottle,val,label_box) select "+aid+","+str(p.id)+","+\
                                    vid+","+bid+","+id+",product_id,stk_batch_id,rebate_batch_id,label_id,label_pk_id,label_code,val,"+\
                                    "(select label_code from "+tb+" where label_pk_id=a.label_pk_id limit 1) from sales_channelrebatedetails a where "+\
                                    "company_id="+str(p.id)+" and vtype_id="+vid+" and label_pkg=1 "+\
                                    "and label_pk_id in (select label_pk_id from "+tb+")"
                                cur.execute(s)
                                cur.execute("select sum(val) from "+tb_s)
                                r=cur.fetchone()
                                if r[0]:  
                                    #2020-11-09 修改BATCH返利金额
                                    cur.execute("update sales_channelrebatebatch set val=val-"+str(float(r[0]))+" where id="+id) 
                                    
                                    s="INSERT INTO inventory_boxbottlesubstitute(ancestor_id,company_id,vtype_id,product_id,stk_batch_id,rebate_batch_id,"+\
                                        "stk_batch_old_id,rebate_batch_old_id,val,label_id,label_pk_id,label_bottle,label_box) select "+\
                                        "ancestor_id,company_id,vtype_id,product_id,stk_batch_id,rebate_batch_id,stk_batch_old_id,"+\
                                        "rebate_batch_old_id,val,label_id,label_pk_id,label_bottle,label_box from "+tb_s
                                    cur.execute(s)

                                s="INSERT INTO sales_channelrebatedetails("+\
                                    "label_id,label_pk_id,label_code,label_pkg,val,product_id,rebate_batch_id,stk_batch_id,dt_delivery,company_id,vtype_id) select "+\
                                    "label_id,label_pk_id,label_code,label_pkg,val,product_id,"+id+","+bid+",dt_delivery,"+str(p.id)+","+vid+" from "+tb
                                cur.execute(s)     
        data['amount']=amt
        data['amount_neg']=amt_neg
    

    #2020-11-16 返利金额为0，箱盒置换也要写入置换表
    if not bBoxbottle:
        s="INSERT INTO inventory_boxbottlesubstitute("+\
            "ancestor_id,company_id,vtype_id,product_id,stk_batch_id,rebate_batch_id,"+\
            "stk_batch_old_id,rebate_batch_old_id,val,label_id,label_pk_id,label_bottle,label_box) select "+\
            aid+","+cid+","+vid+",a.product_id,"+bid+",null,"+\
            "batch_id,null,null,a.label_id,a.label_pk_id,a.label_code,b.label_code from  "+\
            "(select batch_id,label_id,label_pk_id,label_code,product_id from inventory_stkdetails where "+\
                "company_id="+cid+" and vtype_id="+vid+" and batch_id<>"+bid+" and label_pkg=1 and label_pk_id in "+\
                "(select label_pk_id from inventory_stkdetails where batch_id="+bid+" and label_pkg=3)"+\
            ") a "+\
            "left outer join "+\
            "(select label_pk_id,label_code from inventory_stkdetails where batch_id="+bid+" and label_pkg=3) b "+\
            "on a.label_pk_id=b.label_pk_id"
        cur.execute(s)
    cur.close() 
    return data










@login_required
def check_stroegeo(request):
    c=request.user.company
    cur=connection.cursor()
    if request.method=='GET':
        data=[]
        clmns=['仓库编号','现有仓库','经销商名称','营销层级','父级','上报时间','操作']
        if c.level_id==1:        
            s="select id,tm,"+\
                "(select name from company_company where id=a.company_id),"+\
                "(select name from company_level   where id=(select level_id  from company_company where id=a.company_id)),"+\
                "(select name from company_company  where id=(select parent_id from company_company where id=a.company_id)),"+\
                "(select count(*) from inventory_storehousebd09 where company_id=a.company_id),"+\
                "company_id from inventory_storehousebd09 a "+\
                "where ancestor_id="+str(c.id)+" and checked_by_id is null"
            cur.execute(s)
            for r in cur.fetchall():
                id=str(r[0])
                data.append([ id.zfill(8),r[5],"<div class='agt'>"+r[2]+"</div>",
                    r[3],r[4],r[1].strftime('%Y-%m-%d %H:%M:%S'),
                    "<button class='btn btn-xs btn-primary btn-ck' sid='"+id+"' sn='"+id.zfill(8)+"'>核准</button>"+\
                    "<button class='btn btn-xs btn-primary btn-vw' cid='"+str(r[6])+"'>查看</button>"
                ])     
        return render(request,'inventory/check_storegeo.html',{'data':data,'clmns':clmns,'subtt':[_('经销商管理'),_('待核准仓库')]})
    else:  
        id=str(request.POST.get('id'))
        uid=str(request.user.id)
        t=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')        
        cur.execute("update inventory_storehousebd09 set tm_checked='"+t+"',checked_by_id="+uid+" where id="+id)
        return JsonResponse({'e':''})





#2020-10-27 酒鬼扫码枪瓶盖绑定接口
def bottlecap_binding(request,company_id,process_id): 
    rmk='扫码枪在线绑定'
    #rmk='测试'    
    data={'e':'','warning':'','bottles':0,'bound':0}
    k = sK   
    e = time_key_check(request, k)
    if e:
        data['e'] = e
        return JsonResponse(data)
    
    #瓶盖实时绑定数据存放的表名
    tb_cap='logistics.z_bottlecap_binding'
    #连接REDIS数据库,指定db10
    rd=redis.StrictRedis(host='localhost', port=settings.RD['port'], db=10, password=settings.RD['password'], decode_responses=True) 

    #是否为盖码，是否为临时标，扫码枪已做判断，接口不再做判断
    #process_id  1:瓶盖临时标 2:盒与临时标 3:扫箱码检查       
    
    tmp=request.GET.get('temp','')
    lbl=request.GET.get('label','')
    product=request.GET.get('product','')
    
    #2020-11-11
    pid=request.GET.get('product_id','')
    
    line=request.GET.get('line','')
    user=request.GET.get('user','0')
    batch_no=request.GET.get('batch_no','')


    if process_id in ['1','2']:        
        if len(tmp)!=12 or len(lbl)!=12:
            data['e']='标签号码和临时标长度不正确'
            return JsonResponse(data)

    tm=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')    

    cur=connection.cursor()

    #只有瓶盖-临时标在REDIS设置键值对
    prfx='bottlecap_'+company_id+'_'
    tmp=prfx+tmp
    #为提高效率，不考虑套标跨段，且只有箱/瓶二级套标关系
    tb='label.t'+lbl[:4]  
    if process_id=='1':         
        o=rd.hgetall(tmp)        
        if o:
            if o['label']!=lbl:
                data['warning']='此临时标已于'+o['time']+'与盖码绑定过,现绑定为新盖码'       
        rd.hmset(tmp,{'label':lbl,'product':product,'line':line,'user':user,'time':tm,'batch_no':batch_no})
    elif process_id=='2':  
        #防止把盒标扫成临时标
        if not lbl.isdigit() or lbl[:3]=='444':
            data['e']='请扫盒标'
        else:
            if not pid:
                pid="null"
            d=rd.hgetall(tmp)   
            if d:           
                g=d['label']
                c=decrypt_11h(g)   
                s="INSERT INTO "+tb_cap+"("+\
                    "label,code,bottle,type,remark,company_id,product,prd_line,username,label_pk_id,box_code,bottles_cnt,batch_no,product_id) select "+\
                    "'"+g+"','"+c+"','"+lbl+"',999,'"+rmk+"',"+company_id+",'"+product+"','"+line+"','"+user+"',l.parent_id,"+\
                    "(select code from "+tb+" where id=l.parent_id),"+\
                    "(select count(*) from "+tb+" where package_id=1 and parent_id=l.parent_id),"+\
                    "'"+batch_no+"',"+pid+" from "+\
                    "(select cast('"+lbl+"' as varchar(20)) code) a "+\
                    "left outer join "+\
                    "(select code,id,parent_id from "+tb+") l "+\
                    "on a.code=l.code returning label_pk_id"  
                try:
                    cur.execute(s)                    
                except Exception:
                    s="INSERT INTO "+tb_cap+"("+\
                        "label,code,bottle,type,remark,company_id,product,prd_line,username,label_pk_id,box_code,bottles_cnt,batch_no,product_id) values "+\
                        "('"+g+"','"+c+"','"+lbl+"',999,'"+rmk+"',"+company_id+",'"+product+"','"+line+"','"+user+"',null,"+\
                        "null,null,'"+batch_no+"',"+pid+") returning label_pk_id"      
                    cur.execute(s)
                r=cur.fetchone()
                if r[0]:
                    rd.delete(tmp)
                else:
                    data['e']='盒标 '+lbl+' 不存在'  
            else:
                data['e']='盒标 '+lbl+' 对应的临时标未与瓶盖绑定'            
    elif process_id=='3':   
        r=None
        cnt=0
        tt=0
        err='null'
        try:
            cur.execute("select package_id,coalesce(parent_id,id),"+\
                "(select count(*) from "+tb+" where parent_id=coalesce(a.parent_id,a.id) and package_id=1) "+\
                "from "+tb+" a where code='"+lbl+"'")
            r=cur.fetchone()
        except Exception:
            pass        
        if not r:
            data['e']='箱标 '+lbl+' 不存在'
        else:
            if r[0]!=3:
                data['e']=lbl+' 请扫描箱标'
            else:
                tt=r[2]
                data['bottles']=tt
                s="select count(*) from ( select bottle from "+tb_cap+" where "+\
                    "label_pk_id="+str(r[1])+" group by bottle) a"
                cur.execute(s)
                r=cur.fetchone()
                cnt=r[0] 
                data['bound']=cnt                  
                if r[0]<tt:
                    data['e']='箱标 '+lbl+" 绑定不完整 "+str(cnt)+"/"+str(tt)
        if data['e']:
            err="'"+data['e']+"'"
        s="INSERT INTO logistics.z_bottlecap_boxcheck("+\
            "code,bottles_perbox,cnt_checked,username,product,prd_line,err) values ("+\
            "'"+lbl+"',"+str(tt)+","+str(cnt)+",'"+user+"','"+product+"','"+line+"',"+err+")"
        cur.execute(s)                
    cur.close()
    return JsonResponse(data)


def bottlecap_products(request,company_id):
    data={'e':'','data':[]}
    k = sK   
    e = time_key_check(request, k)
    if e:
        data['e'] = e
        return JsonResponse(data)
    if company_id=='1':
        data['data']=[
            {'name':'52度500mL红坛酒鬼酒（18）',        'id':1091,  'bottles':6}, 
            {'name':'52度500mL紫坛酒鬼酒（2020版）',    'id':1071,  'bottles':6},  
            {'name':'42度500mL红坛酒鬼酒（20）',        'id':1075,  'bottles':6},
            {'name':'52度500mL红坛酒鬼酒（20）',        'id':867,   'bottles':6},
            {'name':'52度500mL红坛酒鬼酒（20）（2*2）', 'id':1118,  'bottles':4},
            {'name':'52度500mL酒鬼酒（馥郁经典）',      'id':1126,  'bottles':6},                 
        ]
    return JsonResponse(data)



@login_required
def bottlecap_output(request):    
    c=request.user.company
    cur=connection.cursor()
    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={'data':[],'clmns':['日期','产线','产品','瓶数','备注'],'dt_fr':dt_fr,'dt_to':dt_to}

    s="select to_char(dt,'yyyy-mm-dd'),prd_line,product,count(*) from z_bottlecap_binding "+\
        "where label_pk_id is not null and is_scraped=false and company_id="+str(c.id)+" and dt between '"+dt_fr+"' and '"+dt_to+"' "+\
        "group by dt,prd_line,product order by dt,prd_line,product"
    cur.execute(s)
    rs=cur.fetchall()
    for r in rs:   
        rmk=''
        s="select a.label_pk_id,box_code,a.bottles_cnt,"+\
            "array_to_string(array("+\
                "select bottle from z_bottlecap_binding where dt='"+r[0]+"' and  label_pk_id=a.label_pk_id),',') bottles,"+\
            "to_char((select min(tm) from z_bottlecap_binding where dt='"+r[0]+"' and label_pk_id=a.label_pk_id),'HH24:MI:SS') min_tm,"+\
            "to_char((select max(tm) from z_bottlecap_binding where dt='"+r[0]+"' and label_pk_id=a.label_pk_id),'HH24:MI:SS') min_tm "+\
            "from "+\
            "(select label_pk_id,box_code,bottles_cnt from z_bottlecap_binding b "+\
                "where dt='"+r[0]+"' and product='"+r[2]+"' and prd_line='"+r[1]+"' and bottles_cnt<>"+\
                "(select count(*) from "+\
                    "(select bottle from z_bottlecap_binding where dt='"+r[0]+"' and label_pk_id=b.label_pk_id) c"+\
                ") "+\
                "group by label_pk_id,box_code,bottles_cnt"+\
            ") a "            
        cur.execute(s)
        cnt=cur.rowcount
        if cnt:
            rmk="共 "+str(cnt)+"箱 绑定数据不完整"   
        html=[]
        for rr in cur.fetchall():          
            bts=rr[3].split(',')
            ul='<div>箱号：'+rr[1]+' '+\
                    '<span style="color:red;margin-left:7px;">'+str(len(bts))+'</span>'+'/'+\
                    '<span style="color:red;">'+str(rr[2])+'</span>'+\
                '<div>时间：'+rr[4]+'至'+rr[5]+'</div>' 
            for bt in bts:
                ul=ul+'<li style="margin-left:44px;" >'+bt+'</li>'                
            ul=ul+"</div>"
            html.append(ul)    
        if rmk:
            rmk="<span style='cursor:pointer' title='不完整箱号列表：'  data-container='#data-content' "+\
                "data-toggle='popover' data-content='"+"".join(html)+"' >"+rmk+"</span>"            
        data['data'].append([  r[0],r[1],r[2],r[3],rmk ])    
    return render(request, "inventory/bottlecap_output.html",data)




@csrf_exempt
def bottlecap_scrapped(request,company_id):
    data={'e':'','cnt':0}
    k = sK   
    e = time_key_check(request, k)
    if e:
        data['e'] = e
        return JsonResponse(data)
    guid=str(uuid.uuid1())
    a=[]
    r=json.loads(request.body)
    cur=connection.cursor()
    user=r['user']
    codes=r['codes']
    for c in codes:
        tb="label.t"+c[:4]
        a.append("('"+guid+"',"+company_id+",'"+c+"','"+user+"')")
    if a:
        cur.execute("INSERT INTO z_bottlecap_scrapped(guid,company_id,code,username) values "+  ",".join(a))        
        s="update z_bottlecap_binding set is_scraped=true where "+\
            "bottle in (select code from z_bottlecap_scrapped where guid='"+guid+"')"
        cur.execute(s)
        data['cnt']=cur.rowcount
    cur.close()   
    return JsonResponse(data)




#2020-11-04
#内参层级扫码新思路：
#一级：只做团购和零售出库扫码
#二级入库：
#1.检查公司是否给上级发过货
#2.入库后才可出库
#3.二级入库数核减一级库存
@csrf_exempt
def app_labelcheck_new(request):
    data={'e':'','code':'','package_id':0,'label_pk_id':0,'err_code':0,'product_id':0,'product_name':''}
    c=request.POST.get('code','')
    company_id=request.POST.get('company_id','')
    vtype_id=request.POST.get('vtype_id','')  
    user_id=request.POST.get('user_id','')    
    
    #2020-04-23
    data['code']=c
    if not c or not company_id or not vtype_id:
        data['e']='parameter missing'
        data['err_code']=10000
        return JsonResponse(data)  
    if request.method=='GET':
        data['e']='invalid method'
        data['err_code']=10003
        return JsonResponse(data)

    companys=Company.objects.filter(id=int(company_id))
    if not companys:
        data['e']='invalid company id'
        data['err_code']=10000
        return JsonResponse(data)
    company=companys[0]    
    lv_id=company.level_id  
    cid=company.id
    pid=company.parent_id
    aid=company.ancestor_id
    cur=connection.cursor()


    #主箱标ID
    lbl_pk_id=0
    #酒鬼防伪码以40开头
    if c[:2]=='40':        
        cur.execute("SELECT wl_code FROM label.lbl_fw_wl where fw_code='"+c+"'")
        r=cur.fetchone()
        if r:
            c=r[0]
        else:
            r=requests.get('http://china315net.com:35222/wuliu/47/fw/wuliu/?fw_code='+c)
            d=r.json()
            if d['wl']:
                c=d['wl']
    data['code']=c
    if company.level.sn==0:
        data['e']=_('此工具只适用于经销商和门店')
        data['err_code']=10000
        return JsonResponse(data)

    tb='label.t'+c[:4]
    #2020-07-27 是否跨段动态绑定
    cur.execute("select tbname from label.lbl_tbcross where segment='"+c[:4]+"'")
    rrr=cur.fetchone()
    if rrr:
        tb='label.'+rrr[0]  
    try:
        cur.execute("select package_id,coalesce(parent_id,id) from "+tb+" where code='"+c+"'")
        r=cur.fetchone()
        if not r[0]:
            data['e']=_('标签号码未激活')
            data['err_code']=20001
            return JsonResponse(data)
        else:
            data['package_id']=r[0]
            data['label_pk_id']=r[1]
            lbl_pk_id=str(r[1])
            pkg=r[0]
    except Exception:
        data['e']=_('标签号码错误')
        data['err_code']=20000
        return JsonResponse(data)   
    

    #箱标,瓶标在同一营销层级都只能入/出一次
    sLbl=" and label_code='"+c+"'"
    if int(vtype_id)==3:
        sLbl=" and label_pk_id="+str(lbl_pk_id)
    s="select product_id,tm from inventory_stkdetails a where vtype_id="+vtype_id+\
        " and ancestor_id="+str(aid)+sLbl+\
        " and (select level_id from company_company where id=a.company_id)="+str(lv_id)
    cur.execute(s)      
    r=cur.fetchone()
    if r:
        t1=''
        if vtype_id=='1':            
            t1=_('此标签已入库')
        elif vtype_id=='2':
            t1=_('此标签已出库')     
        data['err_code']=10001
        data['e']=t1+'('+r[1].strftime('%Y-%m-%d %H:%M:%S')+')'
        return JsonResponse(data)      
    
    
    if int(vtype_id)==1:        #一级经销商不做入库操作        
        s="select id from inventory_stkdetails where vtype_id=2 and company_id="+str(aid)+\
            " and label_pk_id="+str(lbl_pk_id)+"  and company_to_id="+str(pid)
        cur.execute(s)
        r=cur.fetchone()
        if not r:               #二级扫码入库错误信息
            data['err_code']=10001            
            data['e']='未查询到公司到一级经销商的发货记录'
            return JsonResponse(data)  
    else:        
        if lv_id==2:            #一级扫码出库
            e='未查询到公司到一级经销商的发货记录'
            s="select id from inventory_stkdetails where vtype_id=2 and company_id="+str(aid)+\
                " and label_pk_id="+str(lbl_pk_id)+"  and company_to_id="+str(cid)
        else:                   #二级以上扫码出库
            e='请先进行入库操作'
            s="select id from inventory_stkdetails where vtype_id=1 and company_id="+str(cid)+\
                " and label_pk_id="+str(lbl_pk_id)
        cur.execute(s)
        r=cur.fetchone()
        if not r:
            data['err_code']=10001            
            data['e']=e
            return JsonResponse(data)  
    
    #同一层级，扫过箱标再扫瓶标
    if pkg==1:
        s="select a.tm,b.name from "+\
            "(select company_id,tm from inventory_stkdetails where label_pk_id="+str(lbl_pk_id)+\
                " and vtype_id="+vtype_id+" and label_pkg=3 and ancestor_id="+str(aid)+\
            ") a "+\
            "left outer join "+\
            "(select id,name,level_id from company_company) b "+\
            "on a.company_id=b.id "+\
            "where b.level_id="+str(lv_id)   
        cur.execute(s)
        r=cur.fetchone()
        if r:
            data['err_code']=10001            
            data['e']='对应箱标已被扫码('+r[1]+' '+r[0].strftime('%Y-%m-%d %H:%M:%S')+')'
    #同一层级，扫过瓶标再扫箱标
    elif pkg==3:    
        s="select a.tm,b.name from "+\
            "(select company_id,tm from inventory_stkdetails where label_pk_id="+str(lbl_pk_id)+\
                " and vtype_id="+vtype_id+" and label_pkg=1 and ancestor_id="+str(aid)+\
            ") a "+\
            "left outer join "+\
            "(select id,name,level_id from company_company) b "+\
            "on a.company_id=b.id "+\
            "where b.level_id="+str(lv_id)   
        cur.execute(s)
        r=cur.fetchone()
        if r:
            data['err_code']=10001            
            data['e']='此箱已有瓶标被扫码('+r[1]+' '+r[0].strftime('%Y-%m-%d %H:%M:%S')+')'   


    #2020-11-10 取标签产品ID
    s="select product_id,"+\
        "(select name from product_product where id=product_id) from inventory_stkdetails where label_pk_id="+str(lbl_pk_id)+\
        " and company_id="+str(aid)+" and vtype_id in (1,2) order by vtype_id limit 1"
    cur.execute(s)
    r=cur.fetchone()
    if r:
        data['product_id']=r[0]
        data['product_name']=r[1]
    return JsonResponse(data)  



#2020-11-10 检查有瓶盖产品，瓶盖绑定数据是否完整
def bottlecap_count(request,company_id,product_id):
    data={'e':''}
    e=''
    code=request.GET.get('label_code','')
    if not code:
        e='标签号码不能为空'
    elif len(code)<12:
        e='标签号码长度为12位'
    if e:
        data['e']=e
        return JsonResponse(data)

    cur=connection.cursor()
    tb='label.t'+code[:4]
    #有瓶盖数据的标签，目前只考虑箱瓶二级套标关系
    s="select a.package_id,a.label_pk_id,"+\
        "(select bottle_cnt from z_bottle_cap_product where product_id="+product_id+") bottles,"+\
        "coalesce((select count(*) from "+\
            "(select code from label.bottlecap where label_pk_id=a.label_pk_id "+\
                "and length(code)=12  group by code) b"+\
        "),0) cnt,"+\
        "to_char((select dt from inventory_stkdetails where label_pk_id=a.label_pk_id and company_id="+\
            company_id+" and vtype_id=1 and is_delete=false order by id desc limit 1),'yyyy-mm-dd') dt_inbound "+\
        "from (select package_id,coalesce(parent_id,id) label_pk_id from "+tb+"  where code='"+code+"') a" 
    try:
        cur.execute(s)
        r=cur.fetchone()
        if r:
            if not r[4] or r[4]>='2020-06-01':
                if r[2]>r[3]:
                    data['e']='瓶盖绑定数据不完整 '+str(r[3])+"/"+str(r[2])
    except Exception:
        data['e']='标签号码错误'
    cur.close()
    return JsonResponse(data)






#2020-11-12 获取标签信息---最终版
def label_all_info(company_id,code):  
    data = {
        'e': '',                #错误信息        
        'code': code,           #标签号码        
        'package':'',           #标签类型 箱/盒/瓶 
        'product':{
            'series_id':None,   #产品系列ID            
            'series_name':'',   #产品系列名称
            'id':None,          #产品ID    
            'name':'',          #产品名称
            'photo':'',         #产品图片链接
            'price':'',         #产品价格
            'batch_no':'',      #生产批次      
            'barcode':'',       #产品条形码
            'qc':'',            #质检员
            'alcohol':'',       #酒精度
            'volume':'',        #净含量
            'specification':''  #包装规格
        },              
        'boxes': {
            'pk':{
                'code':'',      #主箱标号码
                'replace':{},   #主箱标替换信息
            },       
            'related':[],       #关联箱标信息                
        },        
        'packets': [],          #盒标信息   页面以3级关联显示
        'bottles': [],          #瓶标信息   页面以2级关联显示
        'stock_dev':[],         #入库/出库信息
    } 
    
    has_outbound=False  #酒鬼酒如系统内没有物流信息，需查询SQL SVR数据

    cur=connection.cursor()
    label_pk_id=0
    # 标签是否有被替换的情况
    s="select code0,code,dt from label.lbl_replace_details where code='" + code + "' or code0='"+code+"' order by id desc limit 1"    
    cur.execute(s)   
    rr=cur.fetchone()    
    if rr:
        code=rr[0]              
    if company_id in (1,1644) and code[:1]=='4':
        r = requests.request('GET', 'http://www.china315net.com:35088/jg/fw_wl/'+code+'/')
        d = json.loads(r.text)
        if d['wl']:
            code=d['wl']
            data['code']=d['wl']
        else:
            data['e']='获取物流码失败'
            return data

    #2020-04-30 有标签要查BA表 "湖北裕贡酒业销售有限公司"
    if company_id in [16716]:
        cur.execute("select code_a FROM label.lbl_abcode where code_b='"+code+"'")
        r=cur.fetchone()
        if r:
            code=r[0]    

    t="label.t" +code[:4]
    #2020-07-27 是否跨段动态绑定
    cur.execute("select tbname from label.lbl_tbcross where segment='"+code[:4]+"'")
    rrr=cur.fetchone()
    if rrr:
        t='label.'+rrr[0]
    try:
        cur.execute("select id,package_id,(SELECT name FROM label.lbl_package where id=package_id)," + \
            "coalesce((select parent_id from " +t+ " where id=a.parent_id),parent_id) from "+t+" a where code='"+code+"'")   
        r = cur.fetchone()
        if not r:
            data['e'] = _('标签号码异常')
            return data
    except Exception as e:
        data['e'] = _('标签号码异常')
        return data    
    data['package']=r[2]
    if r[3]:
        label_pk_id=r[3]
    else:
        label_pk_id=r[0]  
    #主箱标
    s="select id,code,"+\
        "(select code0||','||code||','||to_char(dt,'yyyy-mm-dd') from "+\
            "label.lbl_replace_details where code=a.code or code0=a.code order by id desc limit 1) "+\
        "from "+t+" a where id="+str(label_pk_id)
    cur.execute(s)
    r=cur.fetchone()
    data['boxes']['pk']['code']=r[1]
    if r[2]:
        aa=r[2].split(',')
        data['boxes']['pk']['replace']={'old':aa[0],'new':aa[1],'date':aa[2]}
    #关联箱标
    s="select id,code,"+\
        "(select code0||','||code||','||to_char(dt,'yyyy-mm-dd') from "+\
            "label.lbl_replace_details where code=a.code or code0=a.code order by id desc limit 1) "+\
        "from "+t+" a where parent_id="+str(label_pk_id)+" and package_id=3"
    cur.execute(s)
    for r in cur.fetchall():
        d={}
        if r[2]:
            aa=r[2].split(',')
            d={'old':aa[0],'new':aa[1],'date':aa[2]}
        data['boxes']['related'].append({
            'code':r[1],
            'replace':d
        })
    #是否有盒标   
    s="select id,code,"+\
        "(select code0||','||code||','||to_char(dt,'yyyy-mm-dd') from "+\
            "label.lbl_replace_details where code=a.code or code0=a.code order by id desc limit 1) "+\
        "from "+t+" a where package_id=2 and parent_id="+str(label_pk_id)
    cur.execute(s)
    rs=cur.fetchall()
    #有盒标，三级对应
    if rs:
        for r in rs:   
            d={}
            if r[2]:
                aa=r[2].split(',')
                d={'old':aa[0],'new':aa[1],'date':aa[2]}                
            s="select id,code,"+\
                "(select code0||','||code||','||to_char(dt,'yyyy-mm-dd') from "+\
                    "label.lbl_replace_details where code=a.code or code0=a.code order by id desc limit 1) "+\
                "from "+t+" a where parent_id=" + str(r[0]) + " order by code"
            cur.execute(s)
            a = []
            for r1 in cur.fetchall():
                d={}
                if r1[2]:
                    aa=r1[2].split(',')
                    d={'old':aa[0],'new':aa[1],'date':aa[2]}
                a.append({'code':r1[1],'replace':d})
            data['packets'].append({
                'code': r[1],
                'replace':d,
                'bottles': a
            })
    else:
        #箱瓶二级对应
        s="select id,code,"+\
            "(select code0||','||code||','||to_char(dt,'yyyy-mm-dd') from "+\
                "label.lbl_replace_details where code=a.code or code0=a.code order by id desc limit 1) "+\
            "from "+t+" a where parent_id=" + str(label_pk_id) + " and package_id=1 order by code"           
        cur.execute(s)
        for r1 in cur.fetchall():
            d={}
            if r1[2]:
                aa=r1[2].split(',')
                d={'old':aa[0],'new':aa[1],'date':aa[2]}
            data['bottles'].append({ 'code':r1[1], 'replace':d  })
    cur.execute("select level_id,ancestor_id from company_company where id="+str(company_id))
    r=cur.fetchone()
    lv_id=r[0]   
    a_id=r[1]
    
    #产品信息
    if a_id==1644:
        s="select a.label_code,b.code,b.qc,p.name,coalesce(pnc.photo,p.photo),"+\
            "coalesce(pnc.barcode,p.barcode),coalesce(pnc.series_id,p.series_id),p.id,"+\
            "coalesce(to_char(coalesce(pnc.price,p.price),'FM999999.90'),''),"+\
            "coalesce((select name from product_series where id=coalesce(pnc.series_id,p.series_id)),''), "+\
            "(SELECT value FROM product_productprop where categoryprop_id=2 and id in "+\
                "(SELECT prd_property_id FROM product_prdprops where product_id=a.product_id)) alc,"+\
            "(SELECT value FROM product_productprop where categoryprop_id=3 and id in "+\
                "(SELECT prd_property_id FROM product_prdprops where product_id=a.product_id)) vol,"+\
            "(SELECT value FROM product_productprop where categoryprop_id=4 and id in "+\
                "(SELECT prd_property_id FROM product_prdprops where product_id=a.product_id)) spc "+\
            "from "+\
            "(select label_code,batch_id,product_id,"+\
                "(select nc_id from z_jg_nc_product where jg_id=product_id) nc_product_id "+\
                "from inventory_stkdetails where "+\
                "label_pk_id="+str(label_pk_id)+" and vtype_id in (1,2) and is_delete=false "+\
            "and ancestor_id=(select ancestor_id from company_company where id="+str(company_id)+")"+\
            " order by vtype_id,tm limit 1"+\
            ") a "+\
            "left outer join "+\
            "(select id,code,user_id,coalesce(qc_name,'') qc,batch_no from inventory_batch) b "+\
            "on a.batch_id=b.id "+\
            "left outer join "+\
            "(select id,name,series_id,photo,barcode,price from product_product) p "+\
            "on a.product_id=p.id "+\
            "left outer join "+\
            "(select id,name,series_id,photo,barcode,price from product_product) pnc "+\
            "on a.nc_product_id=pnc.id" 
    else:  
        s="select a.label_code,b.code,b.qc,p.name,p.photo,p.barcode,p.series_id,p.id,"+\
            "coalesce(to_char(p.price,'FM999999.90'),''),"+\
            "coalesce((select name from product_series where id=p.series_id),''), "+\
            "(SELECT value FROM product_productprop where categoryprop_id=2 and id in "+\
                "(SELECT prd_property_id FROM product_prdprops where product_id=a.product_id)) alc,"+\
            "(SELECT value FROM product_productprop where categoryprop_id=3 and id in "+\
                "(SELECT prd_property_id FROM product_prdprops where product_id=a.product_id)) vol,"+\
            "(SELECT value FROM product_productprop where categoryprop_id=4 and id in "+\
                "(SELECT prd_property_id FROM product_prdprops where product_id=a.product_id)) spc "+\
            "from "+\
            "(select label_code,batch_id,product_id,"+\
                "(select nc_id from z_jg_nc_product where jg_id=product_id) nc_product_id "+\
                "from inventory_stkdetails where "+\
                "label_pk_id="+str(label_pk_id)+" and vtype_id in (1,2) and is_delete=false "+\
                "and ancestor_id=(select ancestor_id from company_company where id="+str(company_id)+")"+\
                " order by vtype_id,tm limit 1"+\
            ") a "+\
            "left outer join "+\
            "(select id,code,user_id,coalesce(qc_name,'') qc,batch_no from inventory_batch) b "+\
            "on a.batch_id=b.id "+\
            "left outer join "+\
            "(select id,name,series_id,photo,barcode,price from product_product) p "+\
            "on a.product_id=p.id"    
    cur.execute(s)
    r=cur.fetchone()
    if r:
        data['product']['batch_no']=r[1]  
        data['product']['qc']=r[2]  
        data['product']['name']=r[3]
        if r[4]:            
            data['product']['photo']='/media/'+r[4] 
        if r[5]:
            data['product']['barcode']=r[5] 
        if r[6]:
            data['product']['series_id']=r[6]    
        data['product']['id']=r[7]         
        data['product']['price']=r[8]    
        if r[9]:
            data['product']['series_name']=r[9]  
        if r[10]:
            data['product']['alcohol']=r[10]
        if r[11]:
            data['product']['volume']=r[11]
        if r[12]:
            data['product']['specification']=r[12]

        #物流信息       
        sWhere=" where label_pk_id="+str(label_pk_id)
        if lv_id==1:
            #酒鬼可查内参物流
            if company_id==1:
                sAnd=" and ancestor_id in (1,1644)"
            else:
                sAnd=" and ancestor_id="+str(company_id)
        else:       
            #2020-11-14 经销商可查下级物流
            sAnd=" and company_id="+str(company_id)+" or (select parent_id from company_company where id=company_id)="+str(company_id) 
        s="select a.label_code,b.code,to_char(a.tm,'yyyy-mm-dd HH24:MI:SS'),"+\
            "(select username from company_user where id=b.user_id),"+\
            "(select name from company_company where id=a.company_id),"+\
            "coalesce((select name from company_company where id=a.company_to_id),''),"+\
            "(select name FROM label.lbl_package where id=a.label_pkg),"+\
            "(select name from inventory_voucher where id=a.vtype_id),"+\
            "coalesce((select name from inventory_storehouse where id=a.store_id),''),"+\
            "coalesce((select name from inventory_storehouse where id=a.store_fr_id),''),"+\
            "(select name from product_product where id=a.product_id),"+\
            "a.vtype_id from "+\
            "(SELECT label_code,vtype_id,batch_id,tm,product_id,store_id,store_fr_id,label_pkg,"+\
                "company_id,company_to_id from inventory_stkdetails"+sWhere+sAnd+") a "+\
            "left outer join "+\
            "(select id,code,user_id from inventory_batch) b "+\
            "on a.batch_id=b.id "+\
            "order by a.tm"
        cur.execute(s)
        for r in cur.fetchall():
            if r[11]==2:
                has_outbound=True
            data['stock_dev'].append({
                'label':r[0],
                'batch_no':[1],
                'time':r[2],
                'user':r[3],
                'company':r[4],
                'company_to':r[5],
                'package':r[6],
                'voucher':r[7],
                'store':r[8],
                'store_from':r[9],
                'product':r[10]
            })       

    if not has_outbound and company_id==1:
        #无系统物流信息，酒鬼，内参查询 SQL数据库    
        dd=jiugui_local116(data['boxes']['pk']['code'])        
        for d in dd['stock_dev']:
            data['stock_dev'].append({
                'user':'',
                'store_from':'',
                'company':'酒鬼酒股份有限公司',
                'label':d['label_code'],
                'batch_no':d['voucher_no'],
                'time':d['time'],                
                'company_to':d['to_company'],
                'package':d['package'],
                'voucher':d['voucher_type'],                
                'store':d['store'],
                'product':d['product']
            })            
    return data



#2020-11-16 获取瓶盖数据不完整的箱号
def get_bottlecap_boxerr(request,company_id):
    data={'boxes':[],'e':''}
    k = sK   
    e = time_key_check(request, k)
    if e:
        data['e'] = e
        return JsonResponse(data)    
    cur=connection.cursor()
    cur.execute("select box_code,remark from z_bottlecap_boxerr where company_id="+company_id)
    for r in cur.fetchall():
        data['boxes'].append({'code':r[0], 'remark':r[1]})
    return JsonResponse(data)
