from django.shortcuts import render,HttpResponse
from django.db import connection
from django.conf import settings
import json
import requests
from inventory.models import *
from utils.views import *
import datetime
# Create your views here.
from django.views.decorators.csrf import csrf_exempt
from inventory.models import OnlineOrder
from product.models import Product,ProductProp,PrdProps
from company.models import Company
from .models import *
from inventory.views import sK
from .forms import *

from django.contrib.auth.decorators import login_required
from inventory.views import LabelQuery
import math

from erp.models import SalesOrder

#2022-03-22
import hashlib
from django.utils.translation import gettext_lazy as _


inv_sK=sK


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


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





#2020-08-15 第三方取物流数据
def stk_3rd_fetch(request, what, company_id):
    data = {'e': '', 'data': []}
    sKs={
        '18116':'xF$3al1i%)20@)0814',   # 山西西凤酒分公司       
    }
    e=[]
    if company_id not in sKs:
        e.append('company id error')
    dt=datetime.datetime.now().strftime('%Y-%m-%d')
    dt_fr=request.GET.get('date_from',dt)
    dt_to=request.GET.get('date_to',dt)
    if not is_valid_date(dt_fr+' 00:00:00'):
        e.append('invalid format:date_from')
    if not is_valid_date(dt_to+' 00:00:00'):
        e.append('invalid format:date_to')
    if e:
        data['e']=','.join(e)
        return JsonResponse(data)
    sK=sKs[company_id]
    e = time_key_check(request, sK)
    if e:
        data['e'] = e
        return JsonResponse(data)


    #if what=='new':
    #    sWhere=" where ancestor_id="+company_id+" and (dt between '"+dt_fr+"' and '"+dt_to+"')"
    #elif what=='edit':
    #    sWhere=" where ancestor_id="+company_id+" and id in (select obj_id from company_changelog where "+\
    #        "obj_name='Batch' and ancestor_id="+company_id+" and (dt between '"+dt_fr+"' and '"+dt_to+"') group by obj_id)"       
    if what=='new':
        sWhere=" where company_id="+company_id+" and (dt between '"+dt_fr+"' and '"+dt_to+"')"
    elif what=='edit':
        sWhere=" where company_id="+company_id+" and id in (select obj_id from company_changelog where "+\
            "obj_name='Batch' and company_id="+company_id+" and (dt between '"+dt_fr+"' and '"+dt_to+"') group by obj_id)"
    else:
        data['e'] = 'invalid request'
        return JsonResponse(data)

    cur = connection.cursor()

    s="select b.id,b.code,b.tm,b.cnt,b.vtype_id,"+\
        "(select name from inventory_voucher where id=b.vtype_id),"+\
        "b.company_id,   c1.name,coalesce(c1.external_code,''),"+\
        "b.company_to_id,c2.name,coalesce(c2.external_code,''),"+\
        "b.product_id,p.name,coalesce(p.external_code,''),"+\
        "s.id,s.name,coalesce(s.external_code,'') "+\
        "from "+\
        "(SELECT id,code,to_char(a.tm,'yyyy-mm-dd HH24:MI:SS') tm,"+\
            "(select count(*) from inventory_stkdetails where batch_id=a.id) cnt,"+\
            "vtype_id,company_id,company_to_id,product_id,store_fr_id "+\
            "FROM inventory_batch a  "+sWhere+\
        ") b "+\
        "left outer join "+\
        "(select id,name,external_code from company_company) c1 "+\
        "on c1.id=b.company_id "+\
        "left outer join "+\
        "(select id,name,external_code from company_company) c2 "+\
        "on c2.id=b.company_to_id "+\
        "left outer join "+\
        "(select id,name,external_code from product_product) p "+\
        "on b.product_id=p.id "+\
        "left outer join "+\
        "(select id,name,external_code from inventory_storehouse) s "+\
        "on b.store_fr_id=s.id"
    #s="SELECT id,code,to_char(a.tm,'yyyy-mm-dd HH24:MI:SS'),"+\
    #    "(select count(*) from inventory_stkdetails where batch_id=a.id) cnt,"+\
    #    "vtype_id,(select name from inventory_voucher where id=a.vtype_id),"+\
    #    "company_id,(select name from company_company where id=a.company_id),"+\
    #    "company_to_id,(select name from company_company where id=a.company_to_id),"+\
    #    "product_id,(select name from product_product where id=a.product_id),"+\
    #    "store_fr_id,(select name from inventory_storehouse where id=a.store_fr_id), "+\
    #    "coalesce((select external_id from company_company where id=a.company_to_id),0),"+\
    #    "coalesce((select external_code from product_product where id=a.product_id),'') "+\
    #    "FROM inventory_batch a  "+sWhere   
    cur.execute(s)
    for r in cur.fetchall():
        if r[3]>0:
            batch={'id':r[0],'bill-number':r[1],'time':r[2],
                'voucher':{'id':r[4],'name':r[5]},
                'company':{'id':r[6],'name':r[7],'external_code':r[8]},
                'agent':{'id':r[9],'name':r[10],'external_code':r[11]},
                'product':{'id':r[12],'name':r[13],'external_code':r[14]},
                'storehouse':{'id':r[15],'name':r[16],'externale_code':r[17]}
            }
            details = []
            cur.execute("select label_code,label_pkg,label_pk_id from inventory_stkdetails where batch_id=" + str(r[0]))
            for rr in cur.fetchall():
                details.append([ rr[0],rr[1],rr[2] ])
            data['data'].append({'details': details, 'batch': batch})
    cur.close()
    return JsonResponse(data)



#2020-08-26 第3方数据同步
@csrf_exempt
def sync_data(request,company_id,what,option):
    data = {'e': ''}
    subjects=['agent',]
    options=['add',]
    sKs={
        '18116':'xF$3al1i%)20@)0814',   # 山西西凤酒分公司       
    }
    e=[]
    if company_id not in sKs:
        e.append('company id error')
    if what not in subjects:
        e.append('subject err')
    if option not in options:
        e.append('option err')
    dt=datetime.datetime.now().strftime('%Y-%m-%d')
    dt_fr=request.GET.get('date_from',dt)
    dt_to=request.GET.get('date_to',dt)
    if not is_valid_date(dt_fr+' 00:00:00'):
        e.append('invalid format:date_from')
    if not is_valid_date(dt_to+' 00:00:00'):
        e.append('invalid format:date_to')
    if e:
        data['e']=','.join(e)
        return JsonResponse(data)
    sK=sKs[company_id]
    e = time_key_check(request, sK)
    if e:
        data['e'] = e
        return JsonResponse(data)
    cur = connection.cursor()
    if what=='agent':
        if company_id=='18116':
            keys=['user_name','staff_code','agent_id','agent_name','province','city','cell_phone']
            r=json.loads(request.body)
            for ky in keys:
                if ky not in r:
                    e.append(ky+" missing")
            if e:
                data['e']=','.join(e)
                return JsonResponse(data)
            if option=='add':
                #company
                cs=Company.objects.filter(legacy_id=int(r['agent_id']))
                if cs:
                    e.append('agent id already existed:'+str(r['agent_id']))
                cs=Company.objects.filter(name=r['agent_name'],ancestor_id=int(company_id))
                if cs:
                    e.append('agent name already existed:'+r['agent_name'])
                if e:                    
                    s="insert into log_request_err(request_url,method,parameters,err_msg) values "+\
                        "('"+request.path+"','POST','"+json.dumps(r)+"','"+','.join(e)+"')"
                    cur.execute(s)
                    data['e']=','.join(e)                        
                    return JsonResponse(data)
                pid=int(company_id)
                c=Company(external_id=int(r['agent_id']),parent_id=pid,ancestor_id=pid,hierarchy_id=pid,version=0,category_id=1,tm_register=datetime.datetime.now(),
                    user_id=3314,name=r['agent_name'],cell_phone=r['cell_phone'],province=r['province'],city=r['city'],level_id=2
                )
                c.save()
                #user
                s="select username from company_user where username='"+r['user_name']+"'"
                cur.execute(s)
                rr=cur.fetchone()
                if rr:
                    s="insert into log_request_err(request_url,method,parameters,err_msg) values "+\
                        "('"+request.path+"','POST','"+request.body+"','"+r['user_name']+" 已存在')"
                    cur.execute(s)
                else:
                    u=User(company_id=c.id,username=r['user_name'],ancestor_id=pid, external_code=r['staff_code'], phone=r['cell_phone'])
                    u.set_password(r['cell_phone'])
                    u.save()
                    u.groups.add(5)  #经销商功能组

    return JsonResponse(data)




#2021-011-05
@csrf_exempt
def online_order(request,company_id,what):
    data = {'e': ''}
    sKs={
        '18116':'xF$3al1i%)20@)0814',   # 山西西凤酒分公司       
    }
    if company_id not in sKs:         
        data['e'] = 'company id error'
        return JsonResponse(data)

    sK=sKs[company_id]
    e = time_key_check(request, sK)
    if e:
        data['e'] = e
        return JsonResponse(data)
    if what=='create':
        pid=None
        r=json.loads(request.body)    
        if OnlineOrder.objects.filter(order_sn=r['order_sn']):
            data['e']='订单: '+r['order_sn']+' 已存在'
            return JsonResponse(data)
        ps=Product.objects.filter(external_code=r['goods_number'])
        if ps:
            pid=ps[0].id
        o=OnlineOrder(
            company_id=int(company_id),
            order_sn=r['order_sn'], 
            product_id=pid,
            quantity=int(r['num']),
            amount=float(r['total_price']),
            userid=int(r['user_id']),
            storeid=int(r['storage_id']),
            product_name=r['goods_name'],
            product_code=r['goods_number'],
            username=r['username'],
            mobile=r['mobile'],
            address=r['address']           
        )
        try:
            o.save()
        except Exception:
            data['e']='保存订单出错'
            return JsonResponse(data)
    elif what=='get':
        data['order']={'id':0,'number':'','product':{'id':0,'name':'','qty':0}}
        sn=request.GET.get('order_sn','')
        if not sn:
            data['e']='订单号不能为空'
            return JsonResponse(data)
        os=OnlineOrder.objects.filter(company_id=int(company_id),order_sn=sn)
        if not os:
            data['e']='订单号不存在'
            return JsonResponse(data)
        o=os[0]
        cur=connection.cursor()
        cur.execute("select id,name from product_product where company_id="+company_id+" and external_code='"+o.product_code+"'")
        r=cur.fetchone()
        if r:
            data['order']={
                'id':o.id,
                'number':o.order_sn,
                'product':{
                    'id':r[0],
                    'name':r[1],
                    'qty':o.quantity,
                }
            }
        cur.close()
    elif what=='sync_order':
        cur=connection.cursor()
        s="SELECT id,order_sn,product_id,product_name,product_code,quantity,amount,storeid,tm FROM inventory_onlineorder "+\
            "where company_id="+company_id+" and is_closed=false"
        cur.execute(s)
        o=[]
        for r in cur.fetchall():
            o.append({
                'id':r[0],                
                'order_sn':r[1],
                'product_id':r[2],
                'product_name':r[3],
                'product_code':r[4],
                'quantity':r[5],
                'amount':r[6],
                'storeid':r[7],
                'time':r[8].strftime('%Y-%m-%d %H:%M:%S'),
            })
        data['orders']=o
    return JsonResponse(data)
            


def check_json_format(raw_msg):
  """
  用于判断一个字符串是否符合Json格式
  :param self:
  :return:
  """
  if isinstance(raw_msg, str):    # 首先判断变量是否为字符串
    try:
      json.loads(raw_msg, encoding='utf-8')
    except ValueError:
      return False
    return True
  else:
    return False


#2021-06-25 接收第三方数据接口
@csrf_exempt
def data_post(request,company_id,vendor,obj,action=None):
    companys=[
        "1",        #酒鬼酒,
        "1644",     #内参
    ]    
    data = {'e': '', 'result':{} }
    sKs={
        'tms': 'tms$3al1i%)20@)0814',       
        'hand':'hand$tYal1i%)20@)c1W',  
        'bz':  'bz$tl1hFikq%(30@cc1Z', 
    }
    if company_id not in companys:
        data['e']='company id is error'
        return JsonResponse(data)
    which=vendor+"_"+obj 
    if vendor not in sKs:
        data['e']='vendor is not defined'
        return JsonResponse(data)  
    sK=sKs[vendor]
    e = time_key_check(request, sK)
    if e:
        data['e'] = e
        return JsonResponse(data)
    if request.method=='GET':
        data['e']='http post is prefered'
        return JsonResponse(data)
    cur=connection.cursor()
    r=json.loads(request.body)
    if which=="tms_dn":                 #酒鬼酒-TMS-发货单
        if 'data' not in r:
            data['e']='data missing'
            return JsonResponse(data)
        d=r['data']  
        if 'dn_number' not in d or 'products' not in d or 'storehouse' not in d or 'customer' not in d:
            data['e']='dn_number/customer/storehouse/products missing in data'
            return JsonResponse(data)    
        c=d['dn_number']
        cur.execute("select to_char(tm,'yyyy-mm-dd HH24:MI:SS') from z_jiugui_tms_dn where dn_number='"+c+"'")
        r=cur.fetchone()
        if r:
            data['e']="dn_number '"+c+"' already exists ("+r[0]+")"
            return JsonResponse(data)        
        s="INSERT INTO z_jiugui_tms_dn(dn_number, content) VALUES ('"+c+"','"+json.dumps(d)+"')"
        cur.execute(s)
        return JsonResponse(data)   
    elif vendor=='hand':                #酒鬼酒-ERP-上海汉得信息技术股份有限公司
        data={'e':''}
        d=None    
        id=None
        is_delete=False
        if 'data' in r:
            d=r['data']
        sap_code=None
        e=[]
        is_ok='false'
        err="null"               
        if d:
            s="INSERT INTO z_3rd_hand(obj, src) values "+\
                "('"+obj+"','"+json.dumps(d)+"') returning id"
            cur.execute(s)
            r=cur.fetchone()
            id=str(r[0])
            if 'sap_code' in d:
                sap_code=d['sap_code']
            else:
                if obj not in ['stock']:
                    e.append('sap_code missing in data')
            if 'status' in d:
                if int(d['status'])==0:
                    is_delete=True
            else:
                if obj not in ['stock']:
                    e.append('status missing in data')
        else:
            e.append('data missing')
        if e:
            err=",".join(e)
            data['e']=err
            err=err[:100]
            s="update z_3rd_hand set is_ok="+is_ok+",result='"+json.dumps(data)+"',err='"+err+"' where id="+id
            cur.execute(s)
            return JsonResponse(data)
        
        if obj=='storehouse':                   
            objs=Storehouse.objects.filter(external_code=sap_code,company_id=int(company_id))
            if action=='add':
                if objs:
                    e.append(obj+" with sap_code "+sap_code+" already exists")
                if 'name' not in d:
                    e.append('name of '+obj+' missing')
                else:
                    objs=Storehouse.objects.filter(name=d['name'],company_id=int(company_id))
                    if objs:
                        e.append(obj+" with name "+d['name']+" already exists")
                if not e:
                    o=Storehouse(company_id=int(company_id),name=d['name'],external_code=sap_code,is_delete=is_delete)
                    o.save()
                    #data['id']=o.id
                    is_ok='true'
            elif action=='edit':                
                if 'name' not in d:
                    e.append('name of '+obj+' missing')
                if objs:
                    if not e:
                        o=objs[0]
                        o.name=d['name']
                        o.is_delete=is_delete
                        o.save()
                        is_ok='true'    
                else:                    
                    e.append("sap code ["+sap_code+"] of "+obj+" not exits")
            else:
                e.append('action error')                                    
        elif obj=='customer':              
            objs=Company.objects.filter(external_code=sap_code,parent_id=int(company_id))
            #2021-12-24 汉得二级传 category_code,一级反而不传
            cat=None
            if 'category_code' in d:
                cat=d['category_code']
            #2022-01-14 汉得经销商,产品不管新增还是修改,全部推到添加接口
            if action=='add':                
                #if objs:
                #    e.append(obj+" with sap_code "+sap_code+" already exists")                
                if 'province' not in d or 'city' not in d or 'address' not in d :
                    e.append('province/city/address missing')
                #if 'name' not in d:
                #    e.append('name of '+obj+' missing')
                #else:
                #    objs=Company.objects.filter(name=d['name'],parent_id=int(company_id))
                #    if objs:
                #        e.append(obj+" with name "+d['name']+" already exists")    
                if not e:                    
                    div=None
                    if 'division_code' in d:
                        divs=SalesDivision.objects.filter(code=d['division_code'])
                        if divs:
                            div=divs[0]
                    subdiv=None
                    if 'subdivision_code' in d:
                        subdivs=SubSalDivision.objects.filter(code=d['subdivision_code'])
                        if subdivs:
                            subdiv=subdivs[0]
                    #2022-01-14
                    if objs:
                        o=objs[0]                        
                        o.is_delete=is_delete
                        o.save()

                        o.name=d['name']
                        try:
                            o.save()
                            is_ok='true' 
                        except Exception:
                            pass
                    else:
                        o=Company(parent_id=int(company_id),category_id=1,name=d['name'],external_code=sap_code,
                            province=d['province'],city=d['city'],address=d['address'],is_delete=is_delete,
                            division =div,sub_division=subdiv,external_category=cat)
                        try:
                            o.save()
                            is_ok='true' 
                        except Exception:
                            pass
                    #data['id']=o.id
                     
            elif action=='edit':                
                if 'name' not in d:
                    e.append('name of '+obj+' missing')
                if 'province' not in d or 'city' not in d or 'address' not in d:
                    e.append('province/city/address')
                if objs:
                    if  not e:
                        o=objs[0]         
                        if 'division_code' in d:
                            divs=SalesDivision.objects.filter(code=d['division_code'])
                            if divs:
                                o.division=divs[0]
                        if 'subdivision_code' in d:
                            subdivs=SubSalDivision.objects.filter(code=d['subdivision_code'])
                            if subdivs:
                                o.sub_division=subdivs[0]                    
                        o.name=d['name']
                        o.province=d['province']
                        o.city=d['city']
                        o.address=d['address']
                        o.external_category=cat
                        o.is_delete=is_delete
                        o.save()
                        is_ok='true'
                else:                                        
                    e.append("sap code ["+sap_code+"] of "+obj+" not exits")
            else:
                e.append('action error')   
        elif obj=='product':                     
            objs=Product.objects.filter(external_code=sap_code,company_id=int(company_id))
            if action=='add':
                w=[]
                if objs:
                    e.append(obj+" with sap_code "+sap_code+" already exists")                
                if 'series_code' not in d or 'package' not in d or 'alcohol'  not in d or not 'volume' in d or 'legacy_code' not in d:
                    e.append('series_code/package/alcohol/volume/legacy_code missing')
                if 'name' not in d:
                    e.append('name of '+obj+' missing')
                else:
                    objs=Product.objects.filter(name=d['name'],company_id=int(company_id))
                    if objs:
                        e.append(obj+" with name "+d['name']+" already exists")
                if not e:   
                    o=Product(company_id=int(company_id),name=d['name'],external_code=sap_code,remark=d['legacy_code'],external_series=d['series_code'],is_delete=is_delete)
                    o.save()
                    #data['id']=o.id
                    is_ok='true'
                    #categoryprop #1;"flavor";"香型" #2;"alcohol";"酒精度"  #3;"volume";"净含量"  #4;"package";"包装(每箱瓶数)"   
                    if d['alcohol'].__class__.__name__!='int' or d['volume'].__class__.__name__!='int':
                        w.append('alcohol/volume should be integer')
                    alc=d['alcohol']
                    if alc:
                        alc=int(str(alc).replace('度',''))
                        ps=ProductProp.objects.filter(company_id=int(company_id),categoryprop_id=2,value=alc)
                        if ps:                        
                            p=PrdProps(product_id=o.id,prd_property=ps[0])
                            p.save()
                    vol=d['volume']
                    if vol:
                        if str(vol).isdigit():
                            vol=int(vol)
                            ps=ProductProp.objects.filter(company_id=int(company_id),categoryprop_id=3,value=d['volume'])
                            if ps:                        
                                p=PrdProps(product_id=o.id,prd_property=ps[0])
                                p.save()
                    if d['package']:
                        try:
                            eval(d['package'])
                            ps=ProductProp.objects.filter(company_id=int(company_id),categoryprop_id=4,value=d['package'])
                            if ps:                        
                                p=PrdProps(product_id=o.id,prd_property=ps[0])
                                p.save()
                        except Exception:
                            w.append('package should be calculable')
                if w:
                    data['warning']=','.join(w)
                else:
                    data['warning']=''
            elif action=='edit':                
                if 'name' not in d:
                    e.append('name of '+obj+' missing')
                if 'series_code' not in d  or 'legacy_code' not in d:
                    e.append('series_code/package/alcohol/volume/legacy_code missing')
                if objs:
                    if not e:
                        o=objs[0]
                        o.name=d['name']                    
                        o.remark=d['legacy_code']
                        o.external_series=d['series_code']
                        o.is_delete=is_delete
                        o.save()
                        is_ok='true'
                else:                    
                    e.append("sap code ["+sap_code+"] of "+obj+" not exits")
            else:
                e.append('action error')   
        elif obj=='division':                      
            objs=SalesDivision.objects.filter(code=sap_code,company_id=int(company_id))
            if action=='add':
                if objs:
                    e.append(obj+" with sap_code "+sap_code+" already exists")
                if 'name' not in d:
                    e.append('name of '+obj+' missing')   
                else:
                    objs=SalesDivision.objects.filter(name=d['name'],company_id=int(company_id))
                    if objs:
                        e.append(obj+" with name "+d['name']+" already exists")
                if not e:
                    o=SalesDivision(company_id=int(company_id),name=d['name'],code=sap_code,is_delete=is_delete)
                    o.save()
                    #data['id']=o.id
                    is_ok='true'
            elif action=='edit':                
                if 'name' not in d:
                    e.append('name of '+obj+' missing')
                if objs:
                    if  not e:
                        o=objs[0]
                        o.name=d['name']
                        o.is_delete=is_delete
                        o.save()
                        is_ok='true'
                else:                    
                    e.append("sap code ["+sap_code+"] of "+obj+" not exits")
            else:
                e.append('action error')   
        elif obj=='subdivision':                   
            objs=SubSalDivision.objects.filter(code=sap_code,company_id=int(company_id))
            if action=='add':
                div=None
                if objs:
                    e.append(obj+" with sap_code "+sap_code+" already exists")
                if 'name' not in d:
                    e.append('name of '+obj+' missing')   
                else:
                    objs=SubSalDivision.objects.filter(name=d['name'],company_id=int(company_id))
                    if objs:
                        e.append(obj+" with name "+d['name']+" already exists")
                if 'division_code' not in d:
                    e.append('division_code not mission')
                else:
                    ds=SalesDivision.objects.filter(code=d['division_code'],company_id=int(company_id))
                    if ds:
                        div=ds[0]
                    else:
                        e.append('division_code not exists')
                if not e:
                    o=SubSalDivision(company_id=int(company_id),name=d['name'],code=sap_code,division=div,is_delete=is_delete)
                    o.save()
                    #data['id']=o.id
                    is_ok='true'
            elif action=='edit':                
                if 'name' not in d:
                    e.append('name of '+obj+' missing')
                if 'division_code' not in d:
                    e.append('division_code not mission')
                else:
                    ds=SalesDivision.objects.filter(code=d['division_code'])
                    if ds:
                        div=ds[0]
                    else:
                        e.append('division_code not exists')
                if objs:
                    if not e:
                        o=objs[0]
                        o.division=div
                        o.name=d['name']
                        o.is_delete=is_delete
                        o.save()
                        is_ok='true'
                else:                    
                    e.append("sap code ["+sap_code+"] of "+obj+" not exits")
            else:
                e.append('action error')  

        #2021-09-28 汉得推送经销商库存平调单
        elif obj=='stock' and action=='transfer':  
            if 'from_agent' not in d or 'to_agent' not in d or 'products' not in d or 'voucher_no' not in d: 
                e.append('from_agent/to_agent/proucts/voucher_no required')
            else:
                fr=d['from_agent']
                to=d['to_agent']
                ps=d['products']
                vn=d['voucher_no']
                if 'sap_code' not in fr or 'name' not in fr:
                    e.append('sap_code/name required in from_agent')
                if 'sap_code' not in fr or 'name' not in to:
                    e.append('sap_code/name required in to_agent')

                cur.execute("select count(*) from inventory_batch where code='HD_"+vn+"'")
                r=cur.fetchone()
                if r:                    
                    e.append('duplicated voucher_no:'+vn)
                if e:
                    s="update z_3rd_hand set is_ok=false,err='"+','.join(e)+"' where id="+id
                    cur.execute(s)
                else:  
                    s="select "+\
                        "(select id from company_company where ancestor_id=1 and external_code='"+fr['sap_code']+"'),"+\
                        "(select id from company_company where ancestor_id=1 and external_code='"+to['sap_code']+"')"                    
                    cur.execute(s)
                    r=cur.fetchone()
                    if r:
                        if not r[0]:
                            e.append('sap_code not exists for from_agent')
                        if not r[1]:
                            e.append('sap_code not exists for to_agent')
                        if not e:
                            cid_fr=str(r[0])
                            cid_to=str(r[1])
                            url= request.scheme+'://'+request.get_host()+'/inventory/fg/update/'                                   
                            t=datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")                            
                            m2=hashlib.md5((inv_sK+t).encode('utf8'))                            
                            key=m2.hexdigest()    
                            for p in ps:
                                if 'sap_code' not in p or 'labels' not in p:
                                    e.append('sap_code/labels required in products')
                                else:                                    
                                    pc=p['sap_code']                                
                                    cur.execute("select id from product_product where external_code='"+pc+"'")
                                    rr=cur.fetchone()
                                    if rr:
                                        pid=str(rr[0])
                                        #入库
                                        dd={
                                            'bill_number':'HD_'+vn,    
                                            'voucher_id':"1",    
                                            'product_id':pid,
                                            'company_id':cid_to,                                            
                                            "codes":p['labels'],
                                            'key':key, 
                                            'time':t,                                           
                                            'user_id':"1",                                            
                                            'voucher_time':t,                                            
                                        }
                                        rt =requests.post(url,json=dd)                                        
                                        #出库
                                        dd={
                                            'bill_number':'HD_'+vn,   
                                            'voucher_id':"2",
                                            'product_id':pid, 
                                            'company_id':cid_fr,
                                            "codes":p['labels'],
                                            'to_company_id':cid_to,     
                                            'key':key,                                                                                       
                                            'time':t,                                            
                                            'user_id':"1",                                            
                                            'voucher_time':t,                                            
                                        }
                                        rt =requests.post(url,json=dd)                                        
                                    else:
                                        e.append('sap_code "'+pc+'"'+' of product not exists')                                      
                    else:
                        e.append('sap_code not exists for from_agent and to_agent')
        
        #SubSalDivision division
        else:
            e.append('invalid object name')

        #处理结果
        if e:
            err=','.join(e)
            data['e']=err
            err=err[:100]        
        s="update z_3rd_hand set is_ok="+is_ok+",result='"+json.dumps(data)+"',err='"+err+"' where id="+id
        cur.execute(s)                
        return JsonResponse(data)


    

    
    #2021-10-09
    elif vendor=='bz':                #酒鬼内参-博智
        data={'e':'','is_ok':0}
        d=None    
        id=None
        is_delete=False
        if 'data' in r:
            d=r['data']
        sap_code=None
        name=None

        e=[]
        is_ok='false'
        err="null"               
        if d:
            s="INSERT INTO z_3rd_hand(obj, src, vender) values "+\
                "('"+obj+"','"+json.dumps(d)+"','"+vendor+"') returning id"
            cur.execute(s)
            rr=cur.fetchone()
            id=str(rr[0])
            if 'name' in d:
                name=d['name']
            else:                
                e.append('缺少参数name')
            if 'code' in d:
                sap_code=d['code']
            else:
                e.append("缺少参数code")
            #if 'action' in r:
            #    if r['action'] in ['add','edit']:
            #        action=r['action']
            #    else:
            #        e.append('action type error')
            #else:
            #    e.append("action type required")
        
            if 'status' in d:
                if int(d['status'])==0:
                    is_delete=True  
        else:
            e.append('缺少data字段')
        if e:
            err=",".join(e)
            data['e']=err
            err=err[:100]
            s="update z_3rd_hand set is_ok="+is_ok+",result='"+json.dumps(data)+"',err='"+err+"' where id="+id
            cur.execute(s)
            return JsonResponse(data)

        if obj=='agent': 
            license_number=None
            contacts=None
            c=None
            parents=[]
            if 'province' not in d or 'city' not in d or 'address' not in d or 'parents' not in d or 'cell_phone' not in d or 'license_number' not in d or 'contacts' not in d:
                e.append('parents/province/city/address/cell_phone/contacts/license_number可以为空数组/空字符串，但不能缺失')
            if 'license_number' in d:
                if d['license_number']:
                    license_number=d['license_number']
            if 'contacts' in d:
                if d['contacts']:
                    contacts=d['contacts']            
            #2021-10-12 内参-博智：不能确定当前是新建还是编辑，由天鉴从code判断
            if  not d['code']:            
                data['e']="code can not be a empty string"
                s="update z_3rd_hand set is_ok="+is_ok+",result='"+json.dumps(data)+"',err='"+data['e']+"' where id="+id
                cur.execute(s)
                return JsonResponse(data)
            objs=Company.objects.filter(external_code=d['code'],ancestor_id=int(company_id))
            cs=Company.objects.filter(name=d['name'],ancestor_id=int(company_id))
            if objs:                    #修改
                o=objs[0]
                if cs:
                    c=cs[0]
                    if c.id!=o.id:
                        e.append("'"+d['code']+"'要修改成的新的经销商名称 "+d['name']+" 已存在")
            else:                       #新建
                cs=Company.objects.filter(name=d['name'],ancestor_id=int(company_id))
                if cs:                    
                    e.append("新建经销商 "+d['name']+" 已存在")
                o=Company(ancestor_id=int(company_id),category_id=1,hierarchy_id=1,version=1,external_code=d['code'])
            if e:
                err=",".join(e)
                data['e']=err
                err=err[:100]
                s="update z_3rd_hand set is_ok="+is_ok+",result='"+json.dumps(data)+"',err='"+err+"' where id="+id  
                cur.execute(s)
                return JsonResponse(data)            
            div=None
            if 'division' in d:
                divs=SalesDivision.objects.filter(name=d['division'])
                if divs:
                    o.division=divs[0]
            subdiv=None
            if 'subdivision' in d:
                subdivs=SubSalDivision.objects.filter(code=d['subdivision'])
                if subdivs:
                    o.sub_division=subdivs[0]    
            if d['parents']:            
                for prt in d['parents']:
                    ps=Company.objects.filter(external_code=prt['code'],ancestor_id=int(company_id))
                    if ps:
                        p=ps[0]
                        parents.append({'id':p.id,'name':p.name+'['+p.level.name+']'})
                    else:
                        p=Company.objects.get(id=1644)
            else:
                p=Company.objects.get(id=1644)
            o.name=d['name']
            o.parent=p
            o.parents=parents
            o.province=d['province']
            o.city=d['city']
            o.address=d['address']                       
            o.is_delete=is_delete
            o.cell_phone=d["cell_phone"]
            o.license_number=license_number
            o.contacts=contacts
            o.save()
            is_ok='true'
            data['is_ok']=1
            s="update z_3rd_hand set is_ok="+is_ok+",result='"+json.dumps(data)+"',err='"+err+"' where id="+id
            cur.execute(s)      
        return JsonResponse(data)
        
    
#2022-01-13
def salesorder_edit(company_id,data):
    #要求第三方按指定格式参数名传发货单（销售订单）
    order_code=data['order_code']
    customer_code=data['customer']['code']
    customer_name=data['customer']['name']
    ordertype_code='null'
    ordertype_name='null'    
    #2022-02-21 喷码信息
    marking='null'
    if 'marking' in data['customer']:
        marking="'"+data['customer']['marking']+"'"
    if 'order_type' in data:
        ordertype_code="'"+data['order_type']['code']+"'"
        ordertype_name="'"+data['order_type']['name']+"'"    
    cur=connection.cursor()  
    t=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    objs=SalesOrder.objects.filter(company_id=int(company_id),code=order_code)
    if objs:
        o=objs[0]
        o.edit_logs.append({'time':t,'data':data})
    else:
        o=SalesOrder(company_id=int(company_id),code=order_code)        
        o.edit_logs=[{'time':t,'data':data}]
    o.save()
    s="update erp_salesorder set "+\
        "ordertype_code="+ordertype_code+","+\
        "ordertype_name="+ordertype_name+","+\
        "customer_code='"+customer_code+"',"+\
        "customer_name='"+customer_name+"',"+\
        "marking="+marking+","+\
        "customer_id=(select id from company_company where ancestor_id="+str(company_id)+" and external_code='"+customer_code+"' limit 1),"+\
        "ordertype_id=(select id from erp_ordertype where company_id="+str(company_id)+" and code='"+customer_code+"' limit 1) "+\
        "where id="+str(o.id)
    cur.execute(s)
    #如内参公司，订单修改后会再次同步过来，删除订单明细记录
    cur.execute("delete from erp_salesorderdetails where salesorder_id="+str(o.id))
    sIns="insert into erp_salesorderdetails(salesorder_id,company_id,"+\
        "product_name,product_code,product_id,"+\
        "ordertype_code,ordertype_name,ordertype_id,"+\
        "store_name,store_code,store_id,"+\
        "unit_id,unit_name,qty,qty0,product_aging) values "
    a=[]
    for p in data['products']:
        store_code=p['storehouse']['code']        
        store_name=p['storehouse']['name'] 
        product_code=p['code']
        product_name=p['name']
        
        #2022-04-14 内参业务模式修改，不再区分现金和费用订单
        if 'order_type' in p:
            ordertype_code=p['order_type']['code']
            ordertype_name=p['order_type']['name']
        else: 
            ordertype_code='000'
            ordertype_name='不区分'   
        #内参会传生产年份        
        product_aging='null'
        if 'product_aging' in p:
            if p['product_aging']:
                product_aging="'"+str(p['product_aging'])+"'"

        unit='null'
        
        
        #if 'unit' in p:
        #    if 'name' in p['unit']:
        #        unit="'"+p['unit']['name']+"'"
        
        q=str(int(p['qty']))        
        #默认传过来的数量是最小单位
        q0=q        
        if 'unit' in p:
            if 'name' in p['unit']:
                unit="'"+p['unit']['name']+"'"
                s="select id,qty_x,qty_h,qty_p from product_product where "+\
                    "company_id="+str(company_id)+" and external_code='"+product_code+"'"
                cur.execute(s)
                r0=cur.fetchone()
                if r0:
                    if p['unit']['name'] in ['箱']:
                        if r0[3]:
                            q0=str(int(q)*0[3])                    
                    elif p['unit']['name'] in ['盒']:
                        if r0[3] and r0[2]:
                            q0=str(int(q)*(r0[3]/r0[2])) 



        a.append("("+str(o.id)+","+str(company_id)+","+\
            "'"+product_name+"','"+product_code+"',"+\
                "(select id from product_product where company_id="+str(company_id)+" and external_code='"+product_code+"' limit 1),"+\
            "'"+ordertype_code+"','"+ordertype_name+"',"+\
                "(select id from erp_ordertype where company_id="+str(company_id)+" and code='"+ordertype_code+"' limit 1),"+\
            "'"+store_name+"','"+store_code+"',"+\
                "(select id from inventory_storehouse where company_id="+str(company_id)+" and external_code='"+store_code+"' limit 1),"+\
            "null,"+unit+","+q+","+q0+","+product_aging+\
        ")")
    if a:
        cur.execute(sIns+",".join(a))
    cur.close()
    return     

#2021-08-04 接收第三方发货单数据接口
@csrf_exempt
def sync_order(request,company_id,vendor):   
    data = {'e': '', 'result':{} }
    sKs={
        '19267':{                               #易臻生物科技
            'ufida':    'yz$3Yl1z%)20)0b1k4o'   #用友
        },                 
        '1644': {                               #内参
            'kingdee':  'nc$tYal1C%)T0@)d1Wn'   #金蝶
        },                 
        '1':    {                               #酒鬼
            'tms':      'tms$3al1i%)20@)0814'   #TMS
        },                  
    }       
    if company_id not in sKs:
        data['e']='company not authorized'
        return JsonResponse(data)
    if vendor not in sKs[company_id]:
        data['e']='invalid vendor name'
        return JsonResponse(data)
    sK=sKs[company_id][vendor]
    e = time_key_check(request, sK)
    if e:
        data['e'] = e
        return JsonResponse(data)
    if request.method=='GET':
        data['e']='http post is prefered'
        return JsonResponse(data)    
    try:
        r=json.loads(request.body)
    except Exception:
        data['e']='invalid json structrue'
        return JsonResponse(data)
    if 'data' not in r:
        data['e']='data missing'
        return JsonResponse(data)
    d=r['data']
    if company_id=='1644':
        if 'dn_number' not in d or 'products' not in d  or 'customer' not in d:
            data['e']='dn_number/customer/products missing in data'
            return JsonResponse(data)

    else:
        if 'dn_number' not in d or 'products' not in d or 'storehouse' not in d or 'customer' not in d:
            data['e']='dn_number/customer/storehouse/products missing in data'
            return JsonResponse(data)  


    order_code=None
    if 'order_code' in d:
        order_code=d['order_code']   
    dn=d['dn_number']
    ss=OrderSync.objects.filter(company_id=int(company_id),dn_number=dn)
    if ss:
        o=ss[0]
        o.tm_modify=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    else:
        o=OrderSync(company_id=int(company_id),dn_number=dn)
    order_type=None
    if 'order_type' in d:
        t=d['order_type']
        order_type={'code':t['code'],'name':t['name']}
    o.through_3rd=vendor
    o.order_code=order_code
    o.products=d['products']
    o.customer=d['customer']
    if 'storehouse' in d:
        o.storehouse=d['storehouse']
    o.data=d  
    if order_type:
        o.order_type=order_type
    o.is_ok=True
    #2022-02-21 喷码信息 marking
    if 'marking' in d['customer']:
        o.marking=d['customer']['marking']
    o.save()

    #2022-01-14
    cur=connection.cursor()
    cur.execute("SELECT  erp_salesorder FROM company_companycfg WHERE company_id="+str(company_id))
    r=cur.fetchone()
    #if int(company_id)==1644:
    if r:
        if r[0]:
            try:
                salesorder_edit(company_id,d)
            except Exception:
                pass
    return JsonResponse(data)



#2022-01-17 检查订单产品剩余发货数量
#注：大多数企业，如酒鬼酒，产品规格为 箱盒瓶 三级对应，实际生产时贴 箱瓶 二级套标，即瓶标对应产品规格的盒
def salesorder_remaining(company_id,order_id,pkg_lv2=True):  
    products=[]  
    cur=connection.cursor() 
    #销售订单单位转换成物流系统单位
    units={
        '箱':'x',
        '件':'x',
        '盒':'h',
        '瓶':'p',        
    }
    
    #将销售出库明细数量换算为瓶；若订单明细没有单位，默认为瓶
    s_qty="coalesce(d.x,0)*p.p+coalesce(d.p,0)+"+\
        "case when d.h>0 then d.h*(p.p/p.h) else 0 end,"            
    if pkg_lv2: #箱盒瓶三级包装规格，使用了箱瓶二级套标
        #s_qty="coalesce(d.x,0)*p.p+"+\
        #    "case when d.h>0 then d.p*(p.p/p.h) else 0 end,"         
        #2022-02-11
        s_qty="coalesce(d.x,0)*p.p+"+\
            "case when d.p>0 and p.h>0 then d.p*(p.p/p.h) else coalesce(d.p,0) end,"    
    s="select t.id, t.code, t.name,"+\
        "p.id, coalesce(p.external_code,''), p.name,"+\
        "s.id, s.external_code, s.name,"+\
        "o.qty, coalesce(o.unit_name,'瓶'),"+\
        s_qty+\
        "p.h, p.p,coalesce(o.product_aging,'') from "+\
        "(select product_id,ordertype_id,store_id,unit_name,product_aging,sum(qty) qty from erp_salesorderdetails "+\
            "where company_id="+str(company_id)+" and salesorder_id="+str(order_id)+" and "+\
            "(select id from product_product where company_id="+str(company_id)+" and external_code=product_code limit 1)>0 "+\
            "group by product_id,ordertype_id,store_id,unit_name,product_aging "+\
        ") o "+\
        "left outer join "+\
        "(select product_id,ordertype_id,sum(x) x,sum(h) h,sum(p) p from "+\
            "(select product_id,"+\
                "coalesce((select salesorder_type_id from inventory_batch where id=batch_id),0) ordertype_id,"+\
                "case when label_pkg=3 then 1 else 0 end x,"+\
                "case when label_pkg=2 then 1 else 0 end h,"+\
                "case when label_pkg=1 then 1 else 0 end p "+\
                "from inventory_stkdetails where company_id="+str(company_id)+" and vtype_id=2 and batch_id in "+\
                    "(select id from inventory_batch where salesorder_id="+str(order_id)+")"+\
            ") s "+\
            "group by product_id,ordertype_id"+\
        ") d "+\
        "on o.product_id=d.product_id and o.ordertype_id=d.ordertype_id "+\
        "left outer join "+\
        "(select id,name,coalesce(qty_h,1) h,coalesce(qty_p,1) p,external_code from product_product where company_id="+str(company_id)+") p "+\
        "on o.product_id=p.id "+\
        "left outer join "+\
        "(select id,code,name from erp_ordertype where company_id="+str(company_id)+") t "+\
        "on o.ordertype_id=t.id "+\
        "left outer join "+\
        "(select id,external_code,name from inventory_storehouse where company_id="+str(company_id)+") s "+\
        "on o.store_id=s.id"  
    cur.execute(s)
    for r in cur.fetchall():  
        q_o=r[9]
        unit=r[10]
        q_d=r[11]           
        
        #当前产品1箱有多少瓶，1箱有多少盒        
        r_h=r[12]
        r_p=r[13]

        #将订单明细单位转换为x,h,p
        u=units[r[10]]         
        X=0
        H=0
        P=0
        ratio=1            
        if u=='x':
            X=q_o
            ratio=r_p
        elif u=='h':
            H=q_o
            ratio=r_p/r_h
            if pkg_lv2:
                #瓶标做盒标使用
                H=0
                X=int(q_o/r_h)
                P=q_o % r_h
            else:
                X=int(q_o/r_h)                
                H=q_o % r_h
        elif u=='p':            
            ratio=1 
            X=int(q_o/r_p)
            if pkg_lv2:
                #瓶标做盒标使用,只有2级对应
                P=q_o % r_p
            else:
                H=int( (q_o-X*r_p) / (r_p/r_h) )
                P=(q_o-X*r_p-H*(r_p/r_h)) % (r_p/r_h)   
        
        #统一成最小单位瓶   剩余发货瓶数,如果已发数量大于订单数，取0    
        q0=max(q_o*ratio-q_d,0)   

        X0=0
        P0=0
        H0=0
        if q0>0:
            #剩余数量转换为整箱
            X0=int(q0/r_p)
            #剩余瓶数
            rm=q0 % r_p   
            #一盒有多少瓶，默认为1盒1瓶
            hp=1
            if r_p and r_h:
                hp=r_p/r_h
            if pkg_lv2:
                H0=0
                if u=='h':
                    #瓶标做盒标使用
                    P0=int(rm/hp)
                else:
                    P0=rm 
            else:
                H0=int(rm/hp)
                P0=int(rm % r_h)             
        products.append({
            'id':r[3],
            'name':r[5],
            'qty':r[9],
            'unit':r[10],
            'storehouse':{'id':r[6],'name':r[8],'code':r[7]},
            'order_type':{'id':r[0],'code':r[1],'name':r[2]},
            'x':X,
            'h':H,
            'p':P,
            'product_aging':r[14],  #2022-04-14 内参订单产品有产品生产年份信息
            'remaining':{'x':X0,'p':P0,'h':H0}   
        })     
    cur.close()
    return products



#2021-08-05 从第三方发货单号取发货信息
def get_order_infor(company,dn_number):
    error_codes={
        1:      '发货单号不存在',
        100:    '客户编码不存在',
        201:    '所有产品编码不存在',
        210:    '部分产品编码不存在',
        300:    '仓库编码不存在'
    }
    
    data={'error_code':0, 'e':'', 'data':{
        'dn_number':'',
        'order_number':'',        
        'customer':{}, 
        'storehouse':{},  
        'products':[], 
        'products_total':{'qty':0,'cnt':0} 
        } 
    }
    ds=OrderSync.objects.filter(company_id=company.id,dn_number=dn_number).order_by('-id')
    if not ds:
        data['e']='deliver note number not exists'
        data['error_code']='1'
        return data
    d=ds[0]  
    
    if d.dn_number:
        data['data']['dn_number']=d.dn_number
    if d.order_code:
        data['data']['order_number']=d.order_code   
    
    ec=[]
    cs=Company.objects.filter(external_code=d.customer['code'],ancestor_id=company.id)
    if cs:
        c=cs[0]
        data['data']['customer']={'id':c.id,'name':c.name,'marking':''}
    else:
        ec.append('100')
        data['e']='customer code not exists'
    if d.storehouse:
        ss=Storehouse.objects.filter(company_id=company.id,external_code=d.storehouse['code'])
        if ss:
            s=ss[0]
            data['data']['storehouse']={'id':s.id,'name':s.name}
        else:
            #data['error_code']=210
            ec.append('210')
    i=0
    q=0
    k=0



    #2022-01-18已启用ERP销售订单
    if company.cfg_company.erp_salesorder:
        cur=connection.cursor()
        s="select id,marking from erp_salesorder where company_id="+str(company.id)+" and code='"+d.order_code+"'"
        cur.execute(s)
        r=cur.fetchone()
        if r:
            data['data']['products']=salesorder_remaining(company.id,r[0])
            #2022-02-21
            if r[1]:
                data['data']['customer']['marking']=r[1]
        cur.close()
    else:
        for p in d.products:
            q=int(float(q))+int(float(p['qty']))
            i=i+1
            u=''
            if 'unit' in p:
                u=p['unit']
            ps=Product.objects.filter(company_id=company.id,external_code=p['code'])
            if ps:          
                data['data']['products'].append({
                    'id':ps[0].id,
                    'name':ps[0].name,
                    'qty':p['qty'],
                    'unit':u
                })
                k=k+int(float(p['qty'])) 





    '''
    for p in d.products:
        q=int(float(q))+int(float(p['qty']))
        i=i+1
        u=''
        if 'unit' in p:
            u=p['unit']
        ps=Product.objects.filter(company_id=company.id,external_code=p['code'])
        if ps:
            if 'storehouse' in p:
                s="select coalesce(id,0) from inventory_storehouse where external_code='"+p['storehouse']['code']+"'"
                cur=connection.cursor()
                cur.execute(s)
                rrr=cur.fetchone()     
                st={'id':rrr[0],'code':p['storehouse']['code'],'name':p['storehouse']['name']}    

                #data['data']['products'].append({
                #    'id':ps[0].id,
                #    'name':ps[0].name,
                #    'qty':p['qty'],
                #    'unit':u,
                #    'storehouse':st,
                #    'order_type':p['order_type']
                #})
                X=0
                P=0
                X0=0
                P0=0
                if company.id==1644:
                    #内参金蝶传过来的数量单位是瓶，要传给扫码枪箱数和瓶数
                    s="select id,name,external_code,"+\
                        "(select value from product_productprop where id=(select prd_property_id from product_prdprops "+\
                            "where (select categoryprop_id from product_productprop where id=prd_property_id and product_id=prd.id)=4)) spec "+\
                        "from product_product prd where company_id=1644 and id="+str(ps[0].id)
                    cur.execute(s)
                    rr=cur.fetchone()                    
                    bts=eval(rr[3]) 
                    X=int( int(p['qty'])/bts )
                    P= int(p['qty']) % bts   
                    #2022-01-12 暂时用订单数量和单位,给扫码枪APP做修改,今天晚些时候用订单实际剩余和单位替换这些变量 
                    X0=X
                    P0=P
                data['data']['products'].append({
                    'id':ps[0].id,
                    'name':ps[0].name,
                    'qty':p['qty'],
                    'unit':u,
                    'storehouse':st,
                    'order_type':p['order_type'],
                    'x':X,
                    'p':P,
                    'remaining':{'x':X0,'p':P0}
                })
            else:        
                data['data']['products'].append({
                    'id':ps[0].id,
                    'name':ps[0].name,
                    'qty':p['qty'],
                    'unit':u
                })
            k=k+int(float(p['qty'])) 
            #cur.close()
    '''    





    if not data['error_code']:
        if k==0:
            #data['error_code']=201
            ec.append('201')
        elif k<q:
            #data['error_code']=210
            ec.append('210')
    if ec:
        data['error_code']=','.join(ec)
    data['data']['products_total']={'qty':q,'cnt':i}
    
    #如内参金蝶传过来的发货单,仓库在产品字段内
    if company.id!=1644:        
        #2021-09-01 相同产品合并，数量取整箱数
        ps={}
        for p in data['data']['products']:
            if p['id'] in ps:
                #ps[p['id']]['qty']=ps[p['id']]['qty']+p['qty']
                ps[p['id']]['qty']=int(float(ps[p['id']]['qty']))+int(float(p['qty']))
            else:
                ps[p['id']]={'id':p['id'],'name':p['name'],'qty':p['qty'],'unit':p['unit']}
    
        result=[]
        for key in ps:
            p=ps[key]
            if round(float(p['qty']),6)==math.ceil(float(p['qty'])):
                p['qty']=math.ceil(float(p['qty']))
            else:
                p['qty']=math.floor(float(p['qty']))

            #2022-01-12 暂时用订单数量和单位,给扫码枪APP做修改,今天晚些时候用订单实际剩余和单位替换这些变量
            q=p['qty']
            u=p['unit']
            result.append({
                'id':p['id'],
                'name':p['name'],
                'qty':p['qty'],
                'unit':p['unit'],
                'remaining':{
                    'qty':q,
                    'unit':u
                }
            })
            #result.append({'id':p['id'],'name':p['name'],'qty':p['qty'],'unit':p['unit']})
        data['data']['products']=result


    return data


#2021-08-05 扫码枪从第三方发货单号取发货信息
def app_get_order_info(request):
    data={'e':'','data':{},'error_code':''}
    uid=request.GET.get('user_id','')
    if not uid:
        data['e']='user id missing'
        return JsonResponse(data)
    e = time_key_check(request, sK)
    if e:
        data['e'] = e
        return JsonResponse(data)

    us=User.objects.filter(id=int(uid))
    if not us:
        data['e']='user id not exists'
        return JsonResponse(data)
    dn=request.GET.get('dn_number','')
    if not dn:
        data['e']='delivery note number missing'
        return JsonResponse(data)    
    u=us[0]    
    d=get_order_infor(u.company,dn)    
    return JsonResponse(d)



#2021-08-26
@login_required
def vendor_action(request,what,id=None):
    if what=='list':
        cur=connection.cursor()
        clmns=['单位名称','编码','备注说明']
        d=[]
        s="select id,name,coalesce(code,''),coalesce(remark,'') "+\
           "from thirdparty_vendor"
        cur.execute(s)
        for r in cur.fetchall():
            d.append([ '<a href="/thirdparty/vendor/edit/'+str(r[0])+'/">'+r[1]+'</a>', r[2],r[3] ])
        return render(request,'thirdparty/list_vendor.html',{'clmns':clmns,'data':d,'murl':'/thirdparty/vendor/edit/'})
    elif what=='edit':
        if id:
            obj=Vendor.objects.get(id=int(id))
        else:
            obj=Vendor()
        if request.method=='GET':
            form=VendorForm(instance=obj)            
        else:
            form=VendorForm(request.POST,instance=obj)
            if form.is_valid():
                form.save()
        return render(request,'thirdparty/edit_vendor.html',{'form':form})

#2022-03-22
@login_required
def redirect_action(request,what,id=None):
    if what=='list':
        cur=connection.cursor()
        clmns=['前端跳转代码','所属企业','第三方单位名称','跳转链接','指定产品','指定经销商']
        d=[]
        s="select coalesce(code,''),(select name from company_company where id=company_id),"+\
            "(select name from thirdparty_vendor where id=vendor_id),"+\
            "case when synclogistics then '是' else '否' end,"+\
            "case when is_active then '是' else '否' end,"+\
            "coalesce(redirect,''),"+\
            "array_to_string(array((select jsonb_array_elements(products)->>'name')),', '),"+\
            "array_to_string(array(select jsonb_array_elements(agents)->>'name'),', '),"+\
            "id from thirdparty_redirect"
        cur.execute(s)
        for r in cur.fetchall():
            d.append([ '<a href="/thirdparty/redirect/edit/'+str(r[8])+'/">'+r[0]+'</a>',r[1],
                r[2],r[5],r[6],r[7] ])
        return render(request,'thirdparty/list_redirect.html',{'clmns':clmns,'data':d,'murl':'/thirdparty/redirect/edit/'})
    elif what=='edit':
        if id:
            obj=Redirect.objects.get(id=int(id))
        else:
            obj=Redirect()
        if request.method=='GET':
            form=RedirectForm(instance=obj)    
            form.fields['company'].queryset=Company.objects.filter(level_id=1).order_by('name')
        else:
            form=RedirectForm(request.POST,instance=obj)
            form.fields['company'].queryset=Company.objects.filter(level_id=1).order_by('name')
            if form.is_valid():
                form.save()
        return render(request,'thirdparty/edit_redirect.html',{'form':form})






'''
#消费者扫码跳转第三方链接
def label_redirect(request,company_id):
    data={'e':'','flag':'','remedy':'','url':''}
    flag=''
    remedy=''

    


    #瓶盖码/活动码小圆标
    c=request.GET.get('code','')
    if not c:
        data['e']='parameter code missing'
        return JsonResponse(data) 
    
    #传过来的标签一定是加密码(瓶盖码或活动小圆标)
    if c.isdigit():
        data['e']='非加密码'
        return JsonResponse(data)

    
    cur=connection.cursor()    

    #先检查传过来的标签是在线绑定的瓶盖码,要把盖码转为对应瓶标  
    s="select coalesce((select bottle from label.bottlecap where label='"+c+"' order by id desc limit 1),"+\
        "(select bottle from logistics.z_bottlecap_binding where label='"+c+"' order by id desc limit 1))"
    cur.execute(s)
    r=cur.fetchone()    
    
    #print(c)

    if r[0]:
        c=r[0]        
    else:
        s="select bottle,cap_new,url from logistics.z_jg_bottlecap_remedy where cap_old='"+c+"' limit 1"
        
        #print(s)

        cur.execute(s)
        rr=cur.fetchone()
        if rr:
            #2021-11-05 一些测试性跳转,直接返回跳转链接
            if rr[2]:
                data['url']=rr[2]
                cur.close()
                return JsonResponse(data)


            if rr[0]:
                c=rr[0]
                remedy=rr[1]
                data['remedy']=remedy
            else:
                c=decrypt_11h(c) 
        else:     
            c=decrypt_11h(c)    

    if not c:
        data['e']='标签号码解密失败'
        cur.close()
        return JsonResponse(data)

    #酒鬼酒标签，含内参公司
    if company_id=='1':
        q=LabelQuery(company_id,c)        

        if q.infor['label_pk_id']: 
            id=str(q.infor['label_pk_id'])
            s="select company_to_id,product_id,to_char(dt,'yyyy-mm-dd'),"+\
                "coalesce((select code from inventory_batch where id=batch_id),'') "+\
                "from inventory_stkdetails where company_id="+company_id+" "+\
                "and vtype_id=2 and is_delete=false and label_pk_id="+id+" order by id desc"
            cur.execute(s)
            r=cur.fetchone()      
            if r:
                agent_id=str(r[0])
                product_id=str(r[1])
                code=r[3]
                dt='2021-08-25'
                #首先判断是否要跳转到汉得 
                if r[2]>=dt and '一物一码' in code:                 #2021-09-01 酒鬼酒罗工，只有单号含一物一码的发货跳汉得
                    s='select products @> '+"'"+'[{"id":"'+product_id+'"}]'+"' and "+\
                        'agents @> '+"'"+'[{"id":"'+agent_id+'"}]'+"' "+\
                        "from thirdparty_vendor where company_id="+company_id+" and code='HD'"
                    cur.execute(s)
                    r=cur.fetchone()
                    if r[0]:                           
                        flag='HD'
                        data['flag']=flag
                        cur.close()
                        return JsonResponse(data)                        
                #再判断是否要跳转到透云
                s='select products @> '+"'"+'[{"id":"'+product_id+'"}]'+"' "+\
                    "from thirdparty_vendor where company_id="+company_id+" and code='TY'"
                cur.execute(s)
                r=cur.fetchone()
                if r[0]:
                    flag='TY'
                    data['flag']=flag
                    cur.close()
                    return JsonResponse(data)   
                #print(product_id)
                #是否跳天鉴品鉴页面
                s='select products @> '+"'"+'[{"id":"'+product_id+'"}]'+"' "+\
                    "from thirdparty_vendor where company_id="+company_id+" and code='PJ'"
                cur.execute(s)
                r=cur.fetchone()
                if r[0]:
                    flag='PJ'
                    data['flag']=flag
                    cur.close()
                    return JsonResponse(data)                   
            #2021-09-16
            else:   #没有销售出库记录，查询产品信息  
                p=q.get_product()    
                if p['id']:
                    product_id=str(p['id'])                
                    s='select products @> '+"'"+'[{"id":"'+product_id+'"}]'+"' "+\
                        "from thirdparty_vendor where company_id="+company_id+" and code='PJ'"
                    cur.execute(s)
                    r=cur.fetchone()
                    if r[0]:
                        flag='PJ'
                        data['flag']=flag
                        cur.close()
                        return JsonResponse(data)     
        #可能是系统之外的历史数据，查看跳转表
        cur.execute("select flag from z_bottle_redirect where label='"+c+"'")
        r=cur.fetchone()
        if r:            
            flag=r[0]
            data['flag']=flag 
    cur.close()
    return JsonResponse(data)
'''


"""
#2021-11-30修改
#消费者扫码跳转第三方链接
def label_redirect(request,company_id):
    data={'e':'','flag':'','remedy':'','url':''}
    flag=''
    remedy='' 
    #瓶盖码/活动码小圆标
    c=request.GET.get('code','')
    if not c:
        data['e']='parameter code missing'
        return JsonResponse(data)     
    #传过来的标签一定是加密码(瓶盖码或活动小圆标)
    if c.isdigit():
        data['e']='非加密码'
        return JsonResponse(data)    
    cur=connection.cursor()   
    #先检查传过来的标签是在线绑定的瓶盖码,要把盖码转为对应瓶标  
    s="select coalesce((select bottle from label.bottlecap where label='"+c+"' order by id desc limit 1),"+\
        "(select bottle from logistics.z_bottlecap_binding where label='"+c+"' order by id desc limit 1))"
    cur.execute(s)
    r=cur.fetchone()    
    if r[0]:
        c=r[0]        
    else:
        cur.execute("select bottle,cap_new,url,coalesce(flag,'') from logistics.z_jg_bottlecap_remedy where cap_old='"+c+"' limit 1")
        rr=cur.fetchone()
        if rr:
            #2021-11-05 一些测试性跳转,直接返回跳转链接
            if rr[2]:
                data['url']=rr[2]
                data['flag']=rr[3]
                cur.close()
                return JsonResponse(data)
            if rr[0]:
                c=rr[0]
                remedy=rr[1]
                data['flag']=rr[3]
                data['remedy']=remedy
            else:
                c=decrypt_11h(c) 
        else:     
            c=decrypt_11h(c)   
    if not c:
        data['e']='加密码错误'
        cur.close()
        return JsonResponse(data)
    #酒鬼酒标签，含内参公司
    if company_id=='1':
        q=LabelQuery(company_id,c)  
        #物流数据在云平台系统内
        if q.infor['label_pk_id']:        
            product_id=str(q.get_product()['id'])           

            #2021-12-11            品鉴酒,数据发汉得,跳转到天鉴品鉴页面，所以先判断是否为品鉴
            s='select products @> '+"'"+'[{"id":"'+product_id+'"}]'+"' from thirdparty_redirect where company_id="+company_id+" and code='PJ'"

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

           
            
            if r:
                if r[0]:
                    flag='PJ'
                    data['flag']=flag
                    cur.close()
                    return JsonResponse(data)            
                else:
                    s='select products @> '+"'"+'[{"id":"'+product_id+'"}]'+"' from thirdparty_redirect where company_id="+company_id+" and code='HD'"       
                    cur.execute(s)
                    r=cur.fetchone()   
                    #是一物一码产品   
                    if r[0]:                               
                        #2011年已经开始发数据给透云，酒鬼酒云平台上线2019年9月；只能先判断是否跳到汉得                
                        devs=q.get_stk_dev()
                        dt=''
                        batch_code=''
                        for d in devs:
                            if d['vtype_id']==2 and not d['is_delete']:
                                dt=d['tm'][:10]
                                batch_code=d['bill_no']
                        #云平台有发货记录            
                        if dt>'2021-11-30':
                            #判断发货日期，2021年11月30日以后的发货，跳汉得
                            flag="HD"                    
                        else:     
                            if batch_code[:3]=='ty_':
                                flag='TY'
                            else:
                                #s="select id from z_ty_outbound where label_pk_id="+str(q.infor['label_pk_id'])+" and is_return=false"  
                                s="select is_return from z_ty_outbound where label_pk_id="+str(q.infor['label_pk_id'])
                                cur.execute(s)
                                rr=cur.fetchone()
                                if rr:
                                    #在透云经销商出库表内
                                    if rr[0]:
                                        flag="HD"
                                    else:
                                        flag="TY"
                                else:
                                    #不在透云经销商出库表内
                                    flag="HD"

                        #产品在透云系统,且没有出库（已经发送给汉得）
                        #if not rr:
                        #    flag='HD'
                        #else:                            
                        #    flag='TY'


                        data['flag']=flag
                        cur.close()
                        return JsonResponse(data)  
            #不是一物一码产品，看是不是酒鬼品鉴
            #else:                                 
            #    s='select products @> '+"'"+'[{"id":"'+product_id+'"}]'+"' from thirdparty_vendor where company_id="+company_id+" and code='PJ'"
            #    cur.execute(s)
            #    r=cur.fetchone()
            #    if r[0]:
            #        flag='PJ'
            #        data['flag']=flag
            #        cur.close()
            #        return JsonResponse(data)     
        #可能是云平台上线之前，历史数据，查看跳转表
        cur.execute("select flag from z_bottle_redirect where label='"+c+"'")
        r=cur.fetchone()
        if r:            
            flag=r[0]
            data['flag']=flag 
    cur.close()
    return JsonResponse(data)
"""




#2022-03-22 修改
#消费者扫码跳转第三方链接
def label_redirect(request,company_id):
    data={'e':'','flag':'','remedy':'','url':''}
    flag=''

    #补救码
    remedy='' 
    
    c=request.GET.get('code','')
    if not c:
        data['e']='parameter code missing'
        return JsonResponse(data)     
    #传过来的标签一定是加密码(瓶盖码或活动小圆标)
    if c.isdigit():
        data['e']='非加密码'
        return JsonResponse(data)    
    cur=connection.cursor()   
    #先检查传过来的标签是在线绑定的瓶盖码,把盖码转为对应瓶标  
    s="select coalesce((select bottle from label.bottlecap where label='"+c+"' order by id desc limit 1),"+\
        "(select bottle from logistics.z_bottlecap_binding where label='"+c+"' order by id desc limit 1))"
    cur.execute(s)
    r=cur.fetchone()    
    if r[0]:
        c=r[0]        
    else:
        #这枚瓶盖没有绑定记录（车间漏扫）
        cur.execute("select bottle,cap_new,url,coalesce(flag,'') from logistics.z_jg_bottlecap_remedy where cap_old='"+c+"' limit 1")
        rr=cur.fetchone()
        if rr:
            #2021-11-05 一些测试性跳转,直接返回跳转链接
            if rr[2]:
                data['url']=rr[2]
                data['flag']=rr[3]
                cur.close()
                return JsonResponse(data)
            if rr[0]:
                c=rr[0]
                remedy=rr[1]
                data['flag']=rr[3]
                data['remedy']=remedy
                return JsonResponse(data)
            else:
                c=decrypt_11h(c) 
        else:     
            c=decrypt_11h(c)   
    if not c:
        data['e']='加密码错误'
        cur.close()
        return JsonResponse(data)
    #酒鬼酒标签，目前含内参公司
    if company_id=='1':
        q=LabelQuery(company_id,c)         
        #物流数据在云平台系统内
        if q.infor['label_pk_id']:        
            product_id=str(q.get_product()['id'])   
            #先判断是否跳往天鉴相关页面   vendor_id:4 "海南天鉴防伪科技有限公司"
            s='select code from thirdparty_redirect where company_id='+company_id+' and vendor_id=4 '+\
                'and products @> '+"'"+'[{"id":"'+product_id+'"}]'+"'"   
            cur.execute(s)
            r=cur.fetchone()      
            if r:
                data['flag']=r[0]
                return JsonResponse(data)  
            else: 
                s='select products @> '+"'"+'[{"id":"'+product_id+'"}]'+"' from thirdparty_redirect where company_id="+company_id+" and code='HD'"       
                cur.execute(s)
                r=cur.fetchone()   
                #是一物一码产品   
                if r[0]:                               
                    #2011年已经开始发数据给透云，酒鬼酒云平台上线2019年9月；只能先判断是否跳到汉得                
                    devs=q.get_stk_dev()
                    dt=''
                    batch_code=''
                    for d in devs:
                        if d['vtype_id']==2 and not d['is_delete']:
                            dt=d['tm'][:10]
                            batch_code=d['bill_no']                            
                    #云平台有发货记录            
                    if dt>'2021-11-30':
                        #判断发货日期，2021年11月30日以后的发货，跳汉得
                        flag="HD"                    
                    else:                        
                        #云平台上线之前，发给透云的数据已导入云平台，单号以 ty_ 开头
                        if batch_code[:3]=='ty_':
                            flag='TY'
                        else:                                 
                            s="select is_return from z_ty_outbound where label_pk_id="+str(q.infor['label_pk_id'])
                            cur.execute(s)
                            rr=cur.fetchone()
                            if rr:
                                #在透云系统经销商出库表内
                                if rr[0]:
                                    flag="HD"
                                else:
                                    flag="TY"
                            else:
                                #不在透云系统经销商出库表内
                                flag="HD"    
        #可能是云平台上线之前，历史数据，查看跳转表
        cur.execute("select flag from z_bottle_redirect where label='"+c+"'")
        r=cur.fetchone()        
        if r:            
            flag=r[0]
        data['flag']=flag 
    cur.close()
    return JsonResponse(data)





#2021-11-11 接收第三方销售订单数据接口
@csrf_exempt
def sales_order(request,company_id,vendor):
    data={'e':'','result':{}}
    sKs={
        #'19267':{                               #易臻生物科技
        #    'ufida':    'yz$3Yl1z%)20)0b1k4o'   #用友
        #},                 
        '1644': {                               #内参
            'kingdee':  'nc$tYal1C%)T0@)d1Wn'   #金蝶
        },                 
        #'1':    {                               #酒鬼
        #    'tms':      'tms$3al1i%)20@)0814'   #TMS
        #},                  
    } 
    if company_id in sKs:
        sK=sKs[company_id]
    else:
        #data['e']='operation forbidden'        
        data['e']='company not authorized'
        return JsonResponse(data)

    if vendor not in sKs[company_id]:
        data['e']='invalid vendor name'
        return JsonResponse(data)
    sK=sKs[company_id][vendor]
    e = time_key_check(request, sK)
    if e:
        data['e'] = e
        return JsonResponse(data)
    if request.method=='GET':
        data['e']='http post is prefered'
        return JsonResponse(data)    
    try:
        r=json.loads(request.body)
    except Exception:
        data['e']='invalid json structrue'
        return JsonResponse(data)

    if 'order_info' not in r or 'order_details' not in r:
        data['e']='order_info,order_details missing'
        return JsonResponse(data)   

    info=r['order_info']
    details=r['order_details']
    o_flds=[
        'order_number',
        'customer_code',
        'customer_name',
        'order_type_code',
        'order_type_name',
        'invoice_type_code',
        'invoice_type_name',
        'contract_type_code',
        'contract_type_name',        
        'delivery_address',
        'delivery_method',
        'delivery_date',
        'invoice_address',
        'contacts',
        'cellphone',
        'salesmen',
        'remark',
    ]
    ee=[]
    e=[]
    for d in o_flds:
        if d not in info:
            e.append(d)
    if e:
        ee.append(','.join(e)+' required in order_info;')    
    
    #'订单编码'  
    order_number=info['order_number']
    #客户名称
    customer_code=info['customer_code']
    customer_name=info['customer_name']
    #订单类别
    order_type_code=info['order_type_code']
    order_type_name=info['order_type_name']
    #发票类别
    invoice_type_code=info['invoice_type_code']
    invoice_type_name=info['invoice_type_name']
    #'合同类型'
    contract_type_code=info['contract_type_code']
    contract_type_name=info['contract_type_name']    
    #'送货地点'
    delivery_address=info['delivery_address']
    #'提货方式'
    delivery_method=info['delivery_method']
    #'期望交货日期'
    delivery_date=None
    if info['delivery_date']:
        delivery_date=info['delivery_date']
    #'收票地址')
    invoice_address=info['invoice_address']
    #'联系人'
    contacts=info['contacts']
    #联系电话'   
    cellphone=info['cellphone']
    #销售员'  
    salesmen=info['salesmen']
    #'备注说明'
    remark=info['remark']
    if not isinstance(salesmen,list):
        data['e']='salesmen should be a array'
        return JsonResponse(data)
    o_flds=[
        'order_type_code',
        'order_type_name',
        'product_code',
        'product_name',
        'product_aging',
        'qty',
        'unit_name',
        'tax_rate',
        'price',
        'price_discount',
        'price_strategy',
        'price_invoice',
        'amount_total',
        'amount_invoice',
        'discount_item_code',
        'discount_item_name',
        'finance_account_code',
        'finance_account_name',
        'remark',        
    ]
    ds=details[0]   
    
    e=[]
    for d in o_flds:
        if d not in ds:    
            e.append(d)  
    if e:
        ee.append((','.join(e))+' required in order_details;')    
    if ee:
        data['e']=','.join(ee)
        return JsonResponse(data)
    #写入对像同步表
    if request.META.get('HTTP_X_FORWARDED_FOR'):
        ip = request.META.get("HTTP_X_FORWARDED_FOR")
    else:
        ip = request.META.get("REMOTE_ADDR")
    sync=ObjectSync(company_id=int(company_id),through_3rd=vendor,ip_address=ip,app_name='erp',obj_name='SalesOrder',src=r)
    sync.save()
    #if salesmen:
    #    salesmen=json.dumps(salesmen)
    #else:
    #    salesmen='[]'
    os=SalesOrder.objects.filter(company_id=int(company_id),code=order_number)
    if os:
        o=os[0]
    else:
        o=SalesOrder(company_id=int(company_id),code=order_number)
    o.delivery_address=delivery_address
    o.delivery_method=delivery_method
    o.delivery_date=delivery_date
    o.invoice_address=invoice_address
    o.contacts=contacts
    o.cellphone=cellphone
    o.salesmen=salesmen
    o.remark=remark
    o.customer_code=customer_code
    o.customer_name=customer_name
    #o.customer_id=
    o.invoicetype_code=invoice_type_code
    o.invoicetype_name=invoice_type_name
    #o.invoice_id=
    o.ordertype_code=order_type_code
    o.ordertype_name=order_type_name
    #o.ordertype_id=
    o.contracttype_code=contract_type_code
    o.contracttype_name=contract_type_name
    #o.contracttype_id=
    o.save()
    id=str(o.id)    
    cur=connection.cursor()       
    a=[]
    for d in details:
        #'订单类别'),blank=True,null=True)
        order_type_code=d['order_type_code']      
        order_type_name=d['order_type_name']
        #'产品编码'),max_length=10,blank=True,null=True)
        product_code=d['product_code']    
        #'产品名称'),max_length=50)
        product_name=d['product_name']
        #'产品年份'),max_length=20,blank=True,null=True)
        product_aging=d['product_aging']
        #'数量'))
        qty=d['qty']   
        #单位名称'),max_length=20,blank=True,null=True)    
        unit_name=d['unit_name']

        #'税率'),max_digits=10,decimal_places=4,blank=True,null=True)
        tax_rate='null'
        if d['tax_rate']:
            tax_rate=round(float(d['tax_rate']),4)
        #'销售单价'),max_digits=10,decimal_places=4,blank=True,null=True)   
        price='null'
        if d['price']:
            price=round(float(d['price']),4)
        #('折扣单价'),max_digits=10,decimal_places=4,blank=True,null=True)    
        price_discount='null'
        if d['price_discount']:
            price_discount=round(float(d['price_discount']),4)
        #战略价'),max_digits=10,decimal_places=4,blank=True,null=True)  
        price_strategy='null'
        if d['price_strategy']:
            price_strategy=round(float(d['price_strategy']),4)
        #'开票单价'),max_digits=10,decimal_places=4,blank=True,null=True) 
        price_invoice='null'
        if d['price_invoice']:
            price_invoice=round(float(d['price_invoice']),4)
        #'总金额'),max_digits=14,decimal_places=4,blank=True,null=True)
        amount_total='null'
        if d['amount_total']:
            amount_total=round(float(d['amount_total']),4)
        #'开票总金额'),max_digits=14,decimal_places=4,blank=True,null=True)        
        amount_invoice='null'
        if d['amount_invoice']:
            amount_invoice=round(float(d['amount_invoice']),4)
        #'折扣项目'),blank=True,null=True)
        discount_item_code=d['discount_item_code']
        discount_item_name=d['discount_item_name']
        #'对应财务费用科目'),blank=True,null=True)
        finance_account_code=d['finance_account_code']
        finance_account_name=d['finance_account_name']
        remark=d['remark']   
        ss=company_id+","+id+",'"+remark+"','"+\
            order_type_code+"','"+order_type_name+"',"+\
            "(select id from erp_ordertype where company_id="+company_id+" and code='"+order_type_code+"'),"+\
                "'"+discount_item_code+"','"+discount_item_name+"',"+\
            "(select id from erp_discountitem where company_id="+company_id+" and code='"+order_type_code+"'),"+\
                "'"+finance_account_code+"','"+finance_account_name+"',"+\
            "(select id from erp_finaceaccount where company_id="+company_id+" and code='"+order_type_code+"'),"+\
                "'"+product_code+"','"+product_name+"','"+product_aging+"',"+\
            "(select id from product_product where company_id="+company_id+" and external_code='"+product_code+"'),"+\
                "'"+unit_name+"',null,"+\
            str(qty)+","+str(tax_rate)+","+str(price)+","+str(price_discount)+","+\
            str(price_strategy)+","+str(price_invoice)+","+str(amount_total)+","+str(amount_invoice)
        a.append("("+ss+")")
    cur.execute("delete from erp_salesorderdetails where salesorder_id="+id)
    s="INSERT INTO erp_salesorderdetails("+\
        "company_id,salesorder_id,remark,"+\
        "ordertype_code, ordertype_name,ordertype_id,"+\
        "discountitem_code, discountitem_name,discountitem_id,"+\
        "financeaccount_code,financeaccount_name,financeaccount_id,"+\
        "product_code,product_name,product_aging,product_id,"+\
        "unit_name,unit_id,"+\
        "qty,tax_rate,price,price_discount,price_strategy,price_invoice,"+\
        "amt_total,amt_invoice) "+\
        "values "+",".join(a)  
    cur.execute(s)     
    cur.close()
    sync.result=data
    sync.save()
    return JsonResponse(data)




'''
#2021-11-30
def query_label(request,company_id,vendor):   
    data = {'e': '', 'result':{} }
    sKs={
        #'19267':{                                 #易臻生物科技
        #    'ufida':   'yz$3Yl1z%)20)0b1k4o'      #用友
        #},                 
        '1644': {                                   #内参
            'bz':       'bz$tl1hFikq%(30@cc1Z',     #博智
            'kingdee':  'nc$tYal1C%)T0@)d1Wn'       #金蝶
        },                 
        #'1':    {                                  #酒鬼
        #    'tms':     'tms$3al1i%)20@)0814'       #TMS
        #},                  
    }       
    if company_id not in sKs:
        data['e']='company not authorized'
        return JsonResponse(data)
    if vendor not in sKs[company_id]:
        data['e']='invalid vendor name'
        return JsonResponse(data)
    sK=sKs[company_id][vendor]
    e = time_key_check(request, sK)
    if e:
        data['e'] = e
        return JsonResponse(data)
    code=request.GET.get('code','')
    if not code:
        data['e']='标签号码不能为空'
        return JsonResponse(data)
    if request.META.get('HTTP_X_FORWARDED_FOR',''):
        ip =  request.META['HTTP_X_FORWARDED_FOR']
    else:
        ip = request.META['REMOTE_ADDR']
    #写入第三方查询日志
    s="INSERT INTO log.label_query_3rd(company_id,vendor,ip,code) "+\
        "VALUES ("+company_id+",'"+vendor+"','"+ip+"','"+code+"')"
    cur=connection.cursor()
    cur.execute(s)
    cur.close()
    q=LabelQuery(company_id,code)  
    if not q.infor['label_pk_id']:
        data['e']='标签号码错误'
    else:
        p=q.get_product()
        if not p['id']:
            data['e']='标签号码未激活'
        else:
            #给第三方数据，删除产品ID
            p.pop('id')
            p.pop('contents')
            data['result']['product']=p
            data['result']['stk_dev']=[]
            data['result']['package']=q.package['name']
            dev=q.get_stk_dev()
            for d in dev:
                data['result']['stk_dev'].append({
                    'batch_no':d['bill_no'],
                    'label':d['label_code'],
                    'time':d['tm'],
                    'voucher':d['voucher'],
                    'customer':{'name':d['customer'],'external_code':d['customer_external_code']},
                    'package':d['label_pkg']
                })  





    return JsonResponse(data)    
'''


#2021-11-30
def query_label(request,company_id,vendor):   
    data = {'e': '', 'result':{} }
    sKs={
        #'19267':{                                 #易臻生物科技
        #    'ufida':   'yz$3Yl1z%)20)0b1k4o'      #用友
        #},                 
        '1644': {                                   #内参
            'bz':       'bz$tl1hFikq%(30@cc1Z',     #博智
            'kingdee':  'nc$tYal1C%)T0@)d1Wn'       #金蝶
        },                 
        '1':    {                                   #酒鬼
            'hand':     'hand$tYal1i%)20@)c1W'      #汉得
        },                 
    }       
    if company_id not in sKs:
        data['e']='company not authorized'
        return JsonResponse(data)
    if vendor not in sKs[company_id]:
        data['e']='invalid vendor name'
        return JsonResponse(data)
    sK=sKs[company_id][vendor]
    e = time_key_check(request, sK)
    if e:
        data['e'] = e
        return JsonResponse(data)
    code=request.GET.get('code','')
    if not code:
        data['e']='标签号码不能为空'
        return JsonResponse(data)
    if request.META.get('HTTP_X_FORWARDED_FOR',''):
        ip =  request.META['HTTP_X_FORWARDED_FOR']
    else:
        ip = request.META['REMOTE_ADDR']

    #2022-01-22 内参博智接口，加一个参数表示数据来源
    #“source”:”数据来源代码”, 		//2022-01-20 	nc:内参系统  jg:酒鬼系统
    if int(company_id)==1644:
        data['result']['source']='nc'

    #写入第三方查询日志
    s="INSERT INTO log.label_query_3rd(company_id,vendor,ip,code) "+\
        "VALUES ("+company_id+",'"+vendor+"','"+ip+"','"+code+"')"
    cur=connection.cursor()
    cur.execute(s)    
    q=LabelQuery(company_id,code)  
    if not q.infor['label_pk_id']:
        data['e']='标签号码错误'
    else:
        p=q.get_product()
        if not p['id']:
            if int(company_id)==1644:
                data['result']['source']='jg'
                pk=str(q.infor['label_pk_id'])
                #内参可能有漏、错扫码的标签，给博智返回酒鬼产品信息，博智自己映射到内参产品        
                aid='1'
                s="select s.product_id,s.tm,p.name,coalesce(to_char(p.price,'99999.09'),'未知'),p.contents,p.external_code from "+\
                    "(select product_id,tm from "+\
                        "(select product_id,tm from inventory_stkdetails where company_id="+aid+" "+\
                                "and label_pk_id="+pk+" and vtype_id=1 and is_delete=false "+\
                            "union all "+\
                            "select product_id,tm from inventory_stkdetails where company_id="+aid+" "+\
                                "and label_pk_id="+pk+" and  vtype_id=2 "+\
                            "order by tm limit 1"+\
                        ") a "+\
                    ") s "+\
                    "left outer join "+\
                    "(select id,name,price,coalesce(contents,'') contents,external_code from product_product) p "+\
                    "on s.product_id=p.id"
                cur.execute(s)
                r=cur.fetchone()
                d={}
                prp=[]
                if r:      
                    d['name']=r[2]
                    d['price']=r[3].strip()
                    d['id']=r[0]
                    d['external_code']=r[5]
                    s="select coalesce(c.value,'') val,g.name,g.desc_cn,coalesce(g.unit,'') from "+\
                        "(SELECT * FROM product_prdprops where product_id="+str(r[0])+") p "+\
                        "left outer join "+\
                        "(SELECT * FROM product_productprop where company_id="+aid+") c "+\
                        "on p.prd_property_id=c.id "+\
                        "left outer join "+\
                        "(select * from company_categoryprop) g "+\
                        "on c.categoryprop_id=g.id"
                    cur.execute(s)
                    for rr in cur.fetchall():
                        prp.append({'desc_cn':rr[2],'unit':rr[3],'value':rr[0]})                
                    d['property']=prp
                    data['result']['product']=d
                    data['result']['stk_dev']=[]
                    data['result']['package']=q.package['name']
                else:    
                    data['e']='标签号码未激活'
                
        else:
            #给第三方数据，删除产品ID,vendor,inboud_lot,inbound_aging,inbound_date             
            product_lot=''
            product_date=''
            p.pop('id')
            p.pop('contents')     
            p.pop('inbound_aging')
            p.pop('outbound_aging')
            p.pop('inbound_vendor')
            if p['inbound_lot']:
                product_lot=p['inbound_lot']
            p['production_batch']=product_lot
            p.pop('inbound_lot')
            if p['inbound_date']:
                product_date=p['inbound_date']
            p.pop('inbound_date')            
            p['production_date']=product_date            
            data['result']['product']=p
            data['result']['stk_dev']=[]
            data['result']['package']=q.package['name']
            dev=q.get_stk_dev()
            for d in dev:
                data['result']['stk_dev'].append({
                    'batch_no':d['bill_no'],
                    'label':d['label_code'],
                    'time':d['tm'],
                    'voucher':d['voucher'],
                    'customer':{'name':d['customer'],'external_code':d['customer_external_code']},
                    'package':d['label_pkg']
                })  
            #2022-04-18 汉得，要返回防伪信息
            if vendor=='hand':
                d=q.get_security_info()
                data['result']['security_queries']=d
    cur.close()
    return JsonResponse(data)    







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

    data={'error_code':0, 'e':'', 'data':{
        'dn_number':'',
        'order_number':'',  
        'order_id':0,
        'customer':{}, 
        'marking':'',
        'storehouse':{},  
        'products':[]        
        } 
    }    
    products=[]           
    uid=request.GET.get('user_id','')
    if not uid:
        data['e']='user id missing'
        return JsonResponse(data)
    
    e = time_key_check(request, sK)
    if e:
        data['e'] = e
        return JsonResponse(data)

    us=User.objects.filter(id=int(uid))
    if not us:
        data['e']='user id not exists'
        return JsonResponse(data)
    
    dn=request.GET.get('dn_number','')
    if not dn:
        data['e']='delivery note number missing'
        return JsonResponse(data)   
    cur=connection.cursor() 
    u=us[0]
    company_id=u.company_id
    s="select id,code,customer_id,customer_name,marking "+\
        "from erp_salesorder where code="+\
        "(select order_code from thirdparty_ordersync where dn_number='"+dn+"')"
    cur.execute(s)
    rr=cur.fetchone()
    if not rr:
        data['e']='未找到此发货单对应的销售订单'
        cur.close()
        return JsonResponse(data)
    
    if not is_test:
        if not rr[3]:
            data['e']="订单 "+rr[1]+" 喷码信息为空"
            cur.close()
            return JsonResponse(data)
    
    order_id=rr[0]
    data['data']['marking']=rr[3]
    data['data']['dn_number']=dn
    data['data']['marking']=rr[4]
    data['data']['order_number']=rr[1]
    data['data']['order_id']=rr[0]
    data['data']['customer']={'id':rr[2],'name':rr[3]}
    
    s="select t.id, t.code, t.name,"+\
        "p.id, coalesce(p.external_code,''), p.name,"+\
        "s.id, s.external_code, s.name,"+\
        "o.qty, coalesce(o.unit_name,'瓶'),"+\
        "o.qty0,"+\
        "p.h, p.p,coalesce(d.qty0,0),coalesce(o.product_aging,'') from "+\
        "(select product_id,ordertype_id,store_id,unit_name,product_aging,sum(qty) qty,sum(qty0) qty0 from erp_salesorderdetails "+\
            "where company_id="+str(company_id)+" and salesorder_id="+str(order_id)+" and "+\
            "(select id from product_product where company_id="+str(company_id)+" and external_code=product_code limit 1)>0 "+\
            "group by product_id,ordertype_id,store_id,unit_name,product_aging "+\
        ") o "+\
        "left outer join "+\
        "(select product_id,ordertype_id,sum(qty0) qty0 from thirdparty_inkjetdetails where "+\
            "company_id="+str(company_id)+" and salesorder_id="+str(order_id)+" and is_ok=true "+\
            "group by product_id,ordertype_id"+\
        ") d "+\
        "on o.product_id=d.product_id and o.ordertype_id=d.ordertype_id "+\
        "left outer join "+\
        "(select id,name,coalesce(qty_h,1) h,coalesce(qty_p,1) p,external_code from product_product where company_id="+str(company_id)+") p "+\
        "on o.product_id=p.id "+\
        "left outer join "+\
        "(select id,code,name from erp_ordertype where company_id="+str(company_id)+") t "+\
        "on o.ordertype_id=t.id "+\
        "left outer join "+\
        "(select id,external_code,name from inventory_storehouse where company_id="+str(company_id)+") s "+\
        "on o.store_id=s.id" 
    cur.execute(s)
    for r in cur.fetchall():       
        #订单瓶数
        qty0_o=r[11]
        #已喷码瓶数
        qty0_d=r[14]
        
        #1箱有多少盒        
        r_h=r[12]
        #1箱有多少瓶，
        r_p=r[13]

        #剩余瓶数
        rem=qty0_o-qty0_d        
        X=0
        H=0
        P=0
        if rem:
            X=int(rem/r_p)
            #剩余盒数
            if pkg_lv2:     #瓶标做盒标用
                P=rem-X*r_p 
            else:
                H=int((rem % r_p)/(r_p/r_h))
                P=(rem % r_p) % (r_p/r_h)       
        
        yr==''
        products.append({
            'id':r[3],
            'name':r[5],
            #2022-04-15 内参订单明细指定产品生产年份
            'year':yr,
            'qty':r[9],
            'unit':r[10],
            'storehouse':{'id':r[6],'name':r[8],'code':r[7]},
            'order_type':{'id':r[0],'code':r[1],'name':r[2]},            
            'remaining':{'x':X,'h':H,'p':P}   
        })     
    cur.close()
    data['data']['products']=products
    return JsonResponse(data)



#2022-02-21 喷码机更换产品接口
@csrf_exempt
def marking_product(request): 
    sK=inv_sK
    data={'e':'','batch_id':0}
    e = time_key_check(request, sK)
    if e:
        data['e']=e
        return JsonResponse(data)
    if request.method=='GET':
        data['e']='http post is prefered'
        return JsonResponse(data)    
    try:
        r=json.loads(request.body)
    except Exception:
        data['e']='invalid json structrue'
        return JsonResponse(data)    
    if 'batch_id' not in r or 'order_id' not in r or 'product_id' not in r or 'store_id' not in r or 'user_id' not in r or 'marking' not in r or 'dn_number' not in r:        
        data['e']='user id,order id,product id,batch id,store id,marking,delivery note number required'
        return JsonResonse(data)
    cur=connection.cursor()
    
    us=User.objects.filter(id=int(r['user_id']))
    if not us:
        data['e']='user id error'
        return JsonResonse(data)
    u=us[0]
    c=u.company    
    
    if r['batch_id']:
        bid=str(r['batch_id'])
        #改为通过fgupdate接口，可以自动发送给博智
        s="SELECT company_id,product_id,user_id,store_id,coalesce(dn_number,''),"+\
            "(select code FROM logistics.erp_ordertype where id=ordertype_id),"+\
            "(select code FROM logistics.erp_salesorder where id=salesorder_id),"+\
            "(select customer_id FROM logistics.erp_salesorder where id=salesorder_id) "+\
            "FROM logistics.thirdparty_inkjetbatch where id="+bid
        cur.execute(s)
        rr=cur.fetchone()
        if not rr:
            data['e']='batch id '+bid+' not exists'
            return JsonResponse(data)
        codes=[]
        s="select label_code FROM logistics.thirdparty_inkjetdetails "+\
            "where batch_id="+bid+" and is_ok=true"
        cur.execute(s)
        for r0 in cur.fetchall():
            codes.append(r0[0])
        if codes:
            num=rr[4]
            if rr[6]:
                num=rr[6]
            t=datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")            
            m2=hashlib.md5((inv_sK+t).encode('utf8'))    
            key=m2.hexdigest()  
            data={                
                "key": key,
                "time": t,  
                "voucher_id": 2, 
                "bill_number":num,
                "batch_no": num,                   
                "codes":codes,
                "company_id":rr[0],                                
                "product_id":rr[1],
                "user_id":rr[2], 
                "store_id":rr[3],   
                "dn_number":rr[4],
                "ordertype_code":rr[5],                
                "order_number":rr[6],
                "to_company_id":rr[7],                    
            }                          
            address='https://davis.china315net.com'            
            url=address+'/inventory/fg/update/'
            #发送到销售出库接口 
            try:
                rt=requests.post(url,json=data)
                if rt.status_code==200:
                    cur.execute("upate thirdparty_inkjetbatch set outbound=true where id="+bid)
            except Exception:
                pass
    s="select qty,unit_name,qty0 from erp_salesorderdetails where salesorder_id="+str(r['order_id'])+" "+\
        "and product_id="+str(r['product_id'])+" and ordertype_id="+str(r['ordertype_id']) 
    cur.execute(s)    
    rr=cur.fetchone()
    if rr:        
        b=InkJetBatch(            
            company_id=c.id,
            ordertype_id=r['ordertype_id'],
            store_id=r['store_id'],
            salesorder_id=r['order_id'],
            user_id=r['user_id'], 
            product_id=r['product_id'],
            marking=r['marking'],
            dn_number=r['dn_number'],
            qty=rr[0],
            unit=rr[1],
            qty0=rr[2]
        )
        b.save()
        data['batch_id']=b.id
    else:
        data['e']='当前BATCH没有指定产品、订单类型的销售订单明细'
        return JsonResponse(data)
    return JsonResponse(data)



#2022-02-21 喷码机标签上传、检查接口
def marking_check(request,pkg_lv2=True):    #默认 箱/盒/瓶 三层包装 使用箱/瓶二级绑定关系
    sK=inv_sK
    data={
        'e':'',
        'label':{'code':'','package':''},
        'remaining':{'x':0,'p':0}
    }
    e = time_key_check(request, sK)
    if e:
        data['e']=e
        return JsonResponse(data)  
    user_id=request.GET.get('user_id','')
    batch_id=request.GET.get('batch_id','')
    label=request.GET.get('label','')

    

    label=label.strip()

    marking=request.GET.get('marking','')
    if not user_id or not batch_id or not label or not marking:
        data['e']='user id/batch id/label/marking required'
        return JsonResponse(data)

    a_e=[]
    #2022-03-21 喷码机传二维码标签：'\nhttp://tj1.me/?t=3_209427678865'    另，内参还有条形码标签
    if not label.isdigit():         #非纯数字，二维码标签，        
        if 'http://tj1.me/?t=' not in label:
            a_e.append('非海南天鉴产品标签') 
            
    label=label[-12:]
    if len(label)!=12:
        a_e.append('标签号码长度应为12')

    us=User.objects.filter(id=int(user_id))
    if not us:
        data['e']='用户ID不存在'
        return JsonResonse(data)
    bs=InkJetBatch.objects.filter(id=int(batch_id))
    if not bs:
        data['e']='批处理ID不存在 '
        return JsonResponse(data)
    u=us[0]
    b=bs[0]
    cur=connection.cursor()
    company_id=u.company_id    
    q=q=LabelQuery(int(company_id),label)
    pid=str(b.product.id)   
    
    e=''  
    r_h=1
    r_p=1
    if not q.infor['label_pk_id']:
        a_e.append('标签号码错误')
        e=",".join(a_e)         
    else:     
        pkg=q.package['id']
        pk_id=str(q.infor['label_pk_id'])
        s="select qty_x,qty_h,qty_p,coalesce((select sum(qty0) from thirdparty_inkjetdetails "+\
            "where batch_id="+str(b.id)+" and is_ok=true),0) "+\
            "from product_product where id="+pid
        cur.execute(s)
        r=cur.fetchone()
        qty_mark=r[3]
        r_h=r[1]
        r_p=r[2]
        if pkg==3:
            qq=r[2]
        elif pkg==2:
            if not r[1]:
                e="产品 "+b.product.name+' 盒瓶转换率未定义'                
            else:
                qq=r[2]/r[1]
        elif pkg==1:
            if pkg_lv2 and b.unit=='盒':     #箱/盒/瓶 三层包装 使用箱/瓶二级绑定关系                
                if not r[1]:
                    e="产品 "+b.product.name+' 盒瓶转换率未定义'                        
                else:
                    qq=r[2]/r[1]
            else:
                qq=1
        if not e:
            #是否重复喷码
            if pkg==3:
                s="select label_code,tm from thirdparty_inkjetdetails where label_pk_id="+str(q.infor['label_pk_id'])
            else:
                s="select label_code,tm from thirdparty_inkjetdetails where label_code='"+label+"'"
            s=s+" and is_ok=true"
            cur.execute(s)
            r=cur.fetchone()
            if r:
                e='已有喷码记录：'+r[0]+" "+r[1].strftime('%Y-%m-%d %H:%M:%S')
                if r[0]!=label:
                    e='另一箱码'+e
    #当前是否已达喷码数量
    if not e:
        qty_order=b.qty0
        remaining=qty_order-qty_mark-qq
        if remaining<0:
            e='喷码已达订单数量'
    if e:
        data['e']=e
        s="INSERT INTO thirdparty_inkjetdetails("+\
            "company_id,label_code,is_ok,batch_id,ordertype_id,product_id,salesorder_id,store_id,err) values ("+\
            str(company_id)+",'"+label+"',false,"+str(b.id)+","+str(b.ordertype.id)+","+pid+","+str(b.salesorder.id)+","+str(b.store.id)+",'"+e+"'"+\
        ")"

        cur.execute(s)
        cur.close()
        return JsonResponse(data) 
    else:
        s="INSERT INTO thirdparty_inkjetdetails(company_id,label_code,is_ok,batch_id,ordertype_id,product_id,salesorder_id,"+\
            "store_id,label_pk_id,qty0,label_id,label_pkg) values ("+\
                str(company_id)+",'"+label+"',true,"+str(b.id)+","+str(b.ordertype.id)+","+pid+","+str(b.salesorder.id)+","+\
                str(b.store.id)+","+pk_id+","+str(qq)+","+str(q.infor['label_id'])+","+str(pkg)+\
        ")"
        cur.execute(s)
        cur.close()

    pkgs={1:'瓶',2:'盒',3:'箱'}
    data['label']={'code':label,'package':pkgs[pkg]}
    data['remaining']={'x':int(remaining/r_p),'p': remaining % r_p}       
    return JsonResponse(data)




#2022-03-23
@login_required
def inkjet(request,id=None):   
    cur=connection.cursor()
    cid=str(request.user.company.id)
    if id:  #查看当前批次喷码明细        
        html='inkjet_view.html'              
        clmns=[
            _('标签号码'),
            _('类型'),
            _('状态'),            
            _('上传时间'),
            _('备注'),  
        ]
        s="SELECT label_code,coalesce((select name from label.lbl_package where id=label_pkg),''),"+\
            "case when is_ok then '正常' else '错误' end,"+\
            "to_char(tm,'yyyy-mm-dd HH24:MI:SS'),coalesce(err,'') "+\
            "FROM logistics.thirdparty_inkjetdetails where batch_id="+id  
        cur.execute(s)        
        data=[]
        batch={}
        for r in cur.fetchall():
            data.append([ r[0],r[1],r[2],r[3],r[4] ])        
        s="select dn_number,marking,"+\
            "(select customer_name from erp_salesorder where id=salesorder_id),"+\
            "(select name from product_product where id=product_id),"+\
            "coalesce((select name from erp_ordertype where id=ordertype_id),'') "+\
            "FROM logistics.thirdparty_inkjetbatch where id="+id        
        cur.execute(s)
        r=cur.fetchone()        
        if r:       
            batch['dn_number']=r[0]
            batch['marking']=r[1]
            batch['customer']=r[2]
            batch['product']=r[3]
            batch['ordertype']=r[4]             
        return render(request,'thirdparty/'+html,{'clmns':clmns,'data':data,'batch':batch})          
    else:   #喷码批次列表
        dt = datetime.datetime.now().strftime('%Y-%m-%d')
        dt_fr = request.GET.get('dt_fr', dt)
        dt_to = request.GET.get('dt_to', dt)
        dt_max = max(dt_fr, dt_to)
        dt_min = min(dt_fr, dt_to)
        dt_fr = dt_min
        dt_to = dt_max   
       
        html='inkjet_list.html'
        clmns=[
            _('开始时间'),     
            _('发货单号'),                       
            _('喷码信息'),
            _('产品名称'),
            _('计数'),
            _('经销商'),            
            _('操作人'),            
            _('订单类型'),       
            _('已做出库'),
        ]
        s="SELECT id,to_char(tm,'yyyy-mm-dd HH24:MI:SS'),dn_number,marking,"+\
                "(select name from product_product where id=product_id),"+\
                "(select count(*) from thirdparty_inkjetdetails where batch_id=b.id and is_ok=true),"+\
                "(select customer_name from erp_salesorder where id=salesorder_id),"+\
                "(select username from company_user where id=user_id),"+\
                "coalesce((select name from erp_ordertype where id=ordertype_id),''),"+\
                "case when outbound=true then '是' else '否' end "+\
                "FROM logistics.thirdparty_inkjetbatch b "+\
                    "where company_id="+cid+" "+\
                    "and (dt between '"+dt_fr+"' and '"+dt_to+"')"
        cur.execute(s)     
        data=[]
        for r in cur.fetchall():
            data.append([
                "<a href='/thirdparty/inkjet/"+str(r[0])+"/'>"+r[1]+"</a>",
                r[2],r[3],r[4],r[5],r[6],r[7],r[8],r[9]
            ])
        return render(request,'thirdparty/'+html,{'clmns':clmns,'data':data,'dt_fr':dt_fr,'dt_to':dt_to})
