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

inv_sK=sK


#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", #酒鬼酒
    ]    
    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))
            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 or not 'category_code' in d:
                    e.append('province/city/address/category_code 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]
                    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=d['category_code'])
                    o.save()
                    #data['id']=o.id
                    is_ok='true'
            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 or not 'category_code' in d:
                    e.append('province/city/address/category_code missing')
                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=d['category_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=='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

        #处理结果
        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=='hand':                #酒鬼酒-博智
        if obj=='customer': 

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


#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 '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)
    o.through_3rd=vendor
    o.order_code=order_code
    o.products=d['products']
    o.customer=d['customer']
    o.storehouse=d['storehouse']
    o.data=d  
    o.is_ok=True
    o.save()
    #数据检查
    #for p in o.products:
    #    ps=Product.objects.filter(external_code=p.code)
        
    return JsonResponse(data)


#2021-08-05 从第三方发货单号取发货信息
def get_order_infor(company,dn_number):
    error_codes={
        1:      '发货单号不存在',
        100:    '客户编码不存在',
        201:    '所有产品编码不存在',
        210:    '部分产品编码不存在',
        300:    '仓库编码不存在'
    }
    
    data={'error_code':0, 'e':'', 'data':{'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]  
    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}
    else:
        ec.append('100')
        data['e']='customer code not exists'
    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
    for p in d.products:
        q=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'])) 
    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}

    #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']
        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']))
        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 (select name from company_company where id=company_id),"+\
            "name,coalesce(code,''),coalesce(redirect,''),"+\
            "case when synclogistics then '是' else '否' end,"+\
            "case when is_active then '是' else '否' end,"+\
            "array_to_string(array((select jsonb_array_elements(products)->>'name')),', '),"+\
            "array_to_string(array(select jsonb_array_elements(agents)->>'name'),', '),"+\
            "id from thirdparty_vendor"
        cur.execute(s)
        for r in cur.fetchall():
            d.append([ '<a href="/thirdparty/vendor/edit/'+str(r[8])+'/">'+r[1]+'</a>', r[0], r[2],r[4],r[5],r[3],r[6],r[7] ])
        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})


#消费者扫码跳转第三方链接
def label_redirect(request,company_id):
    #data={'e':'','flag':''}
    flag=''
    c=request.GET.get('code','')
    if not c:
        #data['e']='parameter code missing'
        return HttpResponse(flag)
    cur=connection.cursor()


    #先检查传过来的标签是在线绑定的瓶盖码,要把盖码转为对应瓶标    
    cur.execute("select bottle from label.bottlecap where code='"+c+"' order by id desc")
    r=cur.fetchone()
    if r:
        c=r[0]
        
    #酒鬼酒标签，含内参公司
    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]:
                        #data['flag']='HD'
                        #return JsonResponse(data)
                        
                        
                        flag='HD'
                        return HttpResponse(flag)
                #再判断是否要跳转到透云
                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'
                    return HttpResponse(flag)

                #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'
                    return HttpResponse(flag)
            #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'
                        return HttpResponse(flag)

                    
        #可能是系统之外的历史数据，查看跳转表
        cur.execute("select flag from z_bottle_redirect where label='"+c+"'")
        r=cur.fetchone()
        if r:
            #data['flag']=r[0]
            flag=r[0]
    #else:
    #    data['e']='company id not defined'
    #return JsonResponse(data)
    
    return HttpResponse(flag)
