from django.shortcuts import render
from django.conf import settings
import json
import requests
from redis import Redis
from django.http import HttpResponse,HttpResponseRedirect,JsonResponse
from django.contrib.auth import authenticate, login, logout
from company.models import *
from entertainment.models import *
from django.db import connection

from django.views.decorators.csrf import csrf_exempt
from django.utils.translation import gettext_lazy as _
from .wx_pay import WxPay, WxPayError

import base64
import binascii

from utils.views import time_key_check,send_sms,encrypt_p,decrypt_p
import uuid
import redis
#from io import StringIO
from PIL import Image, ImageDraw, ImageFont
import os
import io
import operator
import datetime,time
import hashlib
import random
from .wx_enterprise_pay import *
from .constant.QZ import *

#2020-05-22
sK='fgfdvgu$#&3t@j'

media_path=settings.MEDIA_ROOT
static_path=settings.STATIC_ROOT
WX=settings.WX
WX_TJ=settings.WX_TJFW
WX_JG=settings.WX_JG
WX_CK_UID=settings.TJ_CK_UID

from inventory.views import label_sys_info
from sales.models import *
from product.models import Product
from inventory.models import StorehouseBD09

from promotion.models import *
from .models import *
from company.models import WxOpenid,WxWallet,WxWalletApplication,ApplicationReviewLog,Company,User,DeliveryAddress,BankInfo,BBSdelete

#2020-11-16
from inventory.views import label_all_info
import psycopg2

from dateutil.relativedelta import relativedelta

from django.apps import apps as djapps
from listobj.views import autocomplete
import re


def decrypt_11(code):  
    c=''
    if len(code)==11:  
        s=code.lower()[:10]    
        code=s[9]+s[2]+s[1]+s[3:9]+s[0]        
        c=str(int('0x'+code,16))
    return c


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




def app_autocomplete(request,what):
    data={'e':'','msg':''}
    e=time_key_check(request,sK)
    if e:
        data['e']=e
        return JsonResponse(data)   
    uid=request.GET.get('user_id','')
    e=[]
    if not uid:
        e.append('user id missing')
    us=User.objects.filter(id=int(uid))
    if not us:
        e.append('user id error')
    else:
        u=us[0]
        company=u.company
    term=request.GET.get('term','')
    if not term:
        e.append('term mission')
    if e:
        data['e']=','.join(e)
        return JsonResponse(data)

    query_all=request.GET.get('query_all','0')     
    lmt=5
    d=autocomplete(company,what,term,query_all,lmt)
    a=[]
    for i in d:
        a.append({'id':i['value'],'name':i['label']})
    data={'e':'','data':a}
    return JsonResponse(data,safe=False)

#2020-07-10
rd=redis.StrictRedis(host='localhost', port=settings.RD['port'], password=settings.RD['password'], decode_responses=True)

#2020-12-21 微信redis库
rd_wx=redis.StrictRedis(host='localhost', port=settings.RD['port'], db=5, password=settings.RD['password'], decode_responses=True) 

WX_ERRS={
    1:'标签号码不存在', 
    10:'标签已被扫码',
    100:'奖项已领取',
}



#2020-08-20
from Crypto.Cipher import DES
sW=b'gBGaeqhY'



#2020-10-13
import math
x_pi = 3.14159265358979324 * 3000.0 / 180.0
pi = 3.1415926535897932384626  # π
a = 6378245.0  # 长半轴
ee = 0.00669342162296594323  # 偏心率平方

#2021-02-28
from listobj.views import list_bulletinboard

def gcj02_to_bd09(lng, lat):
    """
    火星坐标系(GCJ-02)转百度坐标系(BD-09)
    谷歌、高德——>百度
    :param lng:火星坐标经度
    :param lat:火星坐标纬度
    :return:
    """
    z = math.sqrt(lng * lng + lat * lat) + 0.00002 * math.sin(lat * x_pi)
    theta = math.atan2(lat, lng) + 0.000003 * math.cos(lng * x_pi)
    bd_lng = z * math.cos(theta) + 0.0065
    bd_lat = z * math.sin(theta) + 0.006
    return [bd_lng, bd_lat]
def bd09_to_gcj02(bd_lon, bd_lat):
    """
    百度坐标系(BD-09)转火星坐标系(GCJ-02)
    百度——>谷歌、高德
    :param bd_lat:百度坐标纬度
    :param bd_lon:百度坐标经度
    :return:转换后的坐标列表形式
    """
    x = bd_lon - 0.0065
    y = bd_lat - 0.006
    z = math.sqrt(x * x + y * y) - 0.00002 * math.sin(y * x_pi)
    theta = math.atan2(y, x) - 0.000003 * math.cos(x * x_pi)
    gg_lng = z * math.cos(theta)
    gg_lat = z * math.sin(theta)
    return [gg_lng, gg_lat]
def wgs84_to_gcj02(lng, lat):
    """
    WGS84转GCJ02(火星坐标系)
    :param lng:WGS84坐标系的经度
    :param lat:WGS84坐标系的纬度
    :return:
    """
    if out_of_china(lng, lat):  # 判断是否在国内
        return [lng, lat]
    dlat = _transformlat(lng - 105.0, lat - 35.0)
    dlng = _transformlng(lng - 105.0, lat - 35.0)
    radlat = lat / 180.0 * pi
    magic = math.sin(radlat)
    magic = 1 - ee * magic * magic
    sqrtmagic = math.sqrt(magic)
    dlat = (dlat * 180.0) / ((a * (1 - ee)) / (magic * sqrtmagic) * pi)
    dlng = (dlng * 180.0) / (a / sqrtmagic * math.cos(radlat) * pi)
    mglat = lat + dlat
    mglng = lng + dlng
    return [mglng, mglat]
def gcj02_to_wgs84(lng, lat):
    """
    GCJ02(火星坐标系)转GPS84
    :param lng:火星坐标系的经度
    :param lat:火星坐标系纬度
    :return:
    """
    if out_of_china(lng, lat):
        return [lng, lat]
    dlat = _transformlat(lng - 105.0, lat - 35.0)
    dlng = _transformlng(lng - 105.0, lat - 35.0)
    radlat = lat / 180.0 * pi
    magic = math.sin(radlat)
    magic = 1 - ee * magic * magic
    sqrtmagic = math.sqrt(magic)
    dlat = (dlat * 180.0) / ((a * (1 - ee)) / (magic * sqrtmagic) * pi)
    dlng = (dlng * 180.0) / (a / sqrtmagic * math.cos(radlat) * pi)
    mglat = lat + dlat
    mglng = lng + dlng
    return [lng * 2 - mglng, lat * 2 - mglat]
def bd09_to_wgs84(bd_lon, bd_lat):
    lon, lat = bd09_to_gcj02(bd_lon, bd_lat)
    return gcj02_to_wgs84(lon, lat)
def wgs84_to_bd09(lon, lat):
    lon, lat = wgs84_to_gcj02(lon, lat)
    return gcj02_to_bd09(lon, lat)


def out_of_china(lng, lat):
    """
    判断是否在国内，不在国内不做偏移
    :param lng:
    :param lat:
    :return:
    """
    return not (lng > 73.66 and lng < 135.05 and lat > 3.86 and lat < 53.55)




def pad8(text):
    while len(text) % 8 != 0:
        text += ' '
    return text


def ajax_label_img(request,company_id=None):
    data={'img':'','cnt':0,'e':''}
    params={}
    fwcode=request.GET.get('fwcode','')
    record=request.GET.get('record','0')
    uid=request.GET.get('uid','')
    if uid:
        consumer_id=int(uid)
    else:
        consumer_id=None
    history=request.GET.get('history','1')    
    if not fwcode:
        data['e']='label code missing'
        return JsonResponse(data)
    if uid:
        uid='pg_'+uid
    else:
        if request.META.get('HTTP_X_FORWARDED_FOR'):
            ip = request.META.get("HTTP_X_FORWARDED_FOR")
        else:
            ip = request.META.get("REMOTE_ADDR")
        uid=ip
    params['fwcode']=fwcode
    params['uid']=uid
    params['record']=record
    params['history']=history
    r=requests.get('http://www.china315net.com:35207/tjfw/get/',params)
    if r.status_code==200:
        d=r.json()
        data['img']="data:image/png;base64,"+d['img']
        data['cnt']=d['counter']['net']
        if d['history']:
            data['tm_1st']=d['history'][0]['tm'] 
        else:
            data['tm_1st']=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        #写入IMAGE QUERY   
        if company_id:
            company_id=int(company_id)
        i=LabelImageQuery(consumer_id=consumer_id,company_id=company_id,label_code=fwcode)
        i.save()
    else:
        data['e']='retrieve image failed'
    return JsonResponse(data)







def test_get_activity(request,company_id):
    label_code=request.GET.get('label_code','')
    data=get_activity(company_id,label_code)
    return JsonResponse(data)




#针对经销商的活动，在物流销售出库时判断.这里只判断针对消费者的营销活动；
def get_activity(company_id,label_code,lng=0,lat=0):  
    data={
        'label_type':'',
        'activity':None,
        'award':None,
        'seeds_id':None,
        'package_id':0,
        'product_name':'',
        'product_id':0,
        'agent_id':'',
        'e':''
    }
   
    conpany_id=str(company_id)
    cur=connection.cursor()  


    
    
    #抽奖活动
    cur.execute("select inv_mgt from company_companycfg where company_id="+company_id)
    r=cur.fetchone()
    #消费者抽奖活动，
    product_id=0
    package_id=0
    pk_id='0'
    if r:        
        #该公司有物流管理
        if r[0]:  
            objs=Company.objects.filter(id=int(company_id))
            if objs:
                company=objs[0]
                d=label_sys_info(label_code,company,has_price=False)
                product_id=d['product']['id']
                package_id=d['package']['id'] 
                data['product_id']=d['product']['id']
                data['package_id']=package_id 
                data['product_name']=d['product']['name']   
                data['e']=d['e']
                pk_id=str(d['label_pk_id'])

                
                stock_dev = d['stock_dev'] 
                stock_dev_len = len(stock_dev)
                if stock_dev_len>0:
                    agent = stock_dev[stock_dev_len-1]['company_id']
                    if agent:
                        data['agent_id'] = agent

            
               
    #2020-09-30
    #先判断标签是否在指定奖池中
    s="select coalesce(activity_id,0),coalesce(award_id,0),id from sales_lotteryseeds "+\
        "where company_id="+company_id+" and label_code='"+label_code+"'"
    cur.execute(s)
    r=cur.fetchone()
    if r:        
        if r[0]:
            data['activity']=Activities.objects.get(id=r[0])
        if r[1]:
            data['award']=ActivityAwards.objects.get(id=r[1])
        if r[2]:
            data['seeds_id']=r[2]
        return data
    else:     
        #2020-08-20 判断条件：                  微信授权前，只判断产品。
        #其它条件： 指定经销商 指定营销层级：   目前只适用于针对经销商的促销活动
        #           指定销售区域：              只有微信授权后才可以取到地理位置 
        #           指定包装规格(箱/盒/瓶)：    先进入相应活动页面，如果扫码标签类别不正确，提示重新扫码
        tm=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        sWhere=" where company_id="+company_id+" and status=1 and ('"+tm+"' between tm_start and tm_end) "+\
            " and lblpkg_id="+str(package_id)+" "      

        s="select id,priority from sales_activities "+sWhere+\
            "and case when array_length(products,1) is null then true else ARRAY["+str(product_id)+"] <@ products end "+\
            "and case when array_length(agents,1) is null then true else "+\
                "array(select company_to_id from inventory_stkdetails where ancestor_id="+company_id+" and label_pk_id="+pk_id+") && agents end "+\
            "order by priority desc limit 1"
        
        #print(s)
        

        #s="select id,priority from sales_activities "+sWhere+\
        #    "and case when array_length(products,1) is null then true else ARRAY["+str(product_id)+"] <@ products end "+\
        #    "and case when array_length(agents,1) is null then true else "+\
        #        "array(select company_to_id from inventory_stkdetails where ancestor_id="+company_id+" and label_pk_id="+pk_id+") && agents end "+\
        #    "and case when array_length(agent_lvs,1) is null then true else "+\
        #       "array(select level_id from company_level where id in (select company_to_id from inventory_stkdetails "+\
        #           "where ancestor_id="+company_id+" and label_pk_id="+pk_id+")) && agent_lvs end "+\
        #    "and case when array_length(batch_ids,1) is null then true else "+\
        #        "array(select batch_id from inventory_stkdetails where ancestor_id="+company_id+" and label_pk_id="+pk_id+") && batch_ids end "+\
        #    "and case when array_length(adm_ids,1) is null then true else "+\
        #        "ST_Contains("+\
        #                "ST_Union(array(SELECT geom  FROM gis.adm where id in "+\
        #                    "((select (unnest(adm_ids)))))),"+\
        #                "ST_GeomFromText('POINT("+lng+" "+lat+")',4326)::geometry"+\
        #            ") end "+\
        #    "order by priority desc limit 1"
           
        
        
        cur.execute(s)
        r=cur.fetchone()
        if r:        
            data['activity']=Activities.objects.get(id=r[0])
    cur.close()

    #不确定的条件：是否指定产品，是否指定地理位置（区域）
    #if lng and lat:
    #    lng=str(lng)
    #    lat=str(lat)
    #    s="select a.id from ("+\
    #            prfx+" and array_length(products,1) is null and array_length(adm_ids,1) is null"+\
    #            " union "+\
    #            prfx+" and ARRAY["+str(product_id)+"] <@ products and array_length(adm_ids,1) is null"+\
    #            " union "+\
    #            prfx+" and array_length(products,1) is null and ST_Contains("+\
    #                    "ST_Union(array(SELECT geom  FROM gis.adm where id in "+\
    #                        "((select (unnest(adm_ids)))))),"+\
    #                    "ST_GeomFromText('POINT("+lng+" "+lat+")',4326)::geometry"+\
    #                ")"+\
    #            " union "+\
    #            prfx+" and ARRAY["+str(product_id)+"] <@ products and array_length(adm_ids,1) is null"+\
    #                " and ST_Contains("+\
    #                    "ST_Union(array(SELECT geom  FROM gis.adm where id in "+\
    #                        "((select (unnest(adm_ids)))))),"+\
    #                    "ST_GeomFromText('POINT("+lng+" "+lat+")', 4326)::geometry"+\
    #                ")"+\
    #        ") a order by a.priority desc limit 1"
    #else:


    
    
    return data



'''
select 
ST_Contains(ST_Union(array(SELECT geom  FROM gis.adm where id in (28317,39419,31629,26148))),ST_GeomFromText('POINT(111.238653 28.205708)', 4326)::geometry)

SELECT *,ST_Contains(ST_Union(array(SELECT geom  FROM gis.adm where id in (select (unnest(adm_ids))))),
	ST_GeomFromText('POINT(115.672401 27.584558)', 4326)::geometry)
FROM logistics.sales_activities 

select * from gis.adm where id in (select (unnest((SELECT  adm_ids  FROM logistics.sales_activities where id=14))))
'''


#小程序帐号密码登录
@csrf_exempt
def wx_login(request):
    data={
        'e':'',  
        'is_staff':False,
        'openid':'',
        'user_id':0,
        'user_name':0,       
        'last_name':'',
        'company_id':0,
        'company_name':'',
        'level':-1,
        'pwd_changed':False,
        #'logo_company':'',
        #'logo_ancestor':'',
        'logo_url':'',
        'img_bg_url':'',
        'ancestor_id':0,
        'show_inbound':0,
        'bind_wxopenid':'',
        'roles':[],
        'isTerminal':False
    }    
    usr=request.POST.get('user_account','')
    pwd=request.POST.get('user_password','')
    openid=request.POST.get('openid','') 
    bind_wxopenid=request.POST.get('bind_wxopenid',0)

    if usr and pwd and openid:
        user = authenticate(username=usr, password=pwd)
        if user is not None and user.is_active:           
            staffs=Staff.objects.filter(user_id=user.id)
            if staffs:
                data['is_staff']=True
            c=user.company
            data['pwd_changed']=user.pwd_changed
            data['user_id'] = user.id            
            data['user_name']=user.username
            data['last_name']=user.last_name
            data['company_id'] = c.id
            data['ancestor_id']=c.ancestor_id
            data['company_name'] = c.name    
            data['level']=c.level.sn   
            if c.is_terminal:
                data['isTerminal'] = True         
            for g in user.groups.all():
                data['roles'].append(g.name)

            user.wx_openid=openid
            if bind_wxopenid:
                user.bind_wxopenid=openid
            user.save()  
            if user.bind_wxopenid:
                data['bind_wxopenid']=user.bind_wxopenid

            #if c.img_logo:                
            #    #data['logo_company']=str(base64.b64encode(c.img_logo.read()), encoding='utf-8')  
            #    data['logo_url']=c.img_logo.url
            #if c.img_bg:
            #    data['img_bg_url']=c.img_bg.url
            cs=Company.objects.filter(id=c.ancestor_id)
            if cs:
                c=cs[0]                
                if c.img_logo:                    
                    #data['logo_ancestor']=str(base64.b64encode(c.img_logo.read()), encoding='utf-8')  
                    data['logo_url']=c.img_logo.url
                if c.img_bg:
                    data['img_bg_url']=c.img_bg.url
                #2020-07-15 是否允许一级经销商入库
                cur=connection.cursor()
                s="select perm_inbound_lv1 from company_companycfg where company_id="+str(c.id)
                cur.execute(s)
                r=cur.fetchone()
                if r:
                    data['show_inbound']=r[0]
                cur.close()
        else:
            data['e'] = _('帐号或密码错误')                    
    else:    
        data['e']='user_account,user_password,openid required'
    return JsonResponse(data)



#小程序使用手机号码登录 Jonathan 
@csrf_exempt
def wx_login_phone(request):
    data={
        'e':'',  
        'is_staff':False,
        'openid':'',
        'user_id':0,
        'user_name':0,       
        'last_name':'',
        'company_id':0,
        'company_name':'',
        'level':-1,
        'pwd_changed':False,
        #'logo_company':'',
        #'logo_ancestor':'',
        'logo_url':'',
        'img_bg_url':'',
        'ancestor_id':0,
        'show_inbound':0,
        'bind_wxopenid':'',
        'roles':[],
        'isTerminal':False
    }    
    phone=request.POST.get('phone','')
    ancestor_id=request.POST.get('ancestor_id','')
    code=request.POST.get('code','')
    openid=request.POST.get('openid','')
    bind_wxopenid=request.POST.get('bind_wxopenid',0)

    if phone and code and openid and ancestor_id:
        user = User.objects.filter(phone=phone,ancestor_id=ancestor_id)
        if user : 
            user = user[0]   
            if user.is_active:       
                staffs=Staff.objects.filter(user_id=user.id)
                if staffs:
                    data['is_staff']=True
                c=user.company
                data['pwd_changed']=True
                data['user_id'] = user.id            
                data['user_name']=user.username
                data['last_name']=user.last_name
                data['company_id'] = c.id
                data['ancestor_id']=c.ancestor_id
                data['company_name'] = c.name    
                data['level']=c.level.sn   
                if c.is_terminal:
                    data['isTerminal'] = True         
                for g in user.groups.all():
                    data['roles'].append(g.name)

                user.wx_openid=openid
                if bind_wxopenid:
                    user.bind_wxopenid=openid
                user.save()  
                if user.bind_wxopenid:
                    data['bind_wxopenid']=user.bind_wxopenid 
                cs=Company.objects.filter(id=c.ancestor_id)
                if cs:
                    c=cs[0]                
                    if c.img_logo:                    
                        #data['logo_ancestor']=str(base64.b64encode(c.img_logo.read()), encoding='utf-8')  
                        data['logo_url']=c.img_logo.url
                    if c.img_bg:
                        data['img_bg_url']=c.img_bg.url
                    #2020-07-15 是否允许一级经销商入库
                    cur=connection.cursor()
                    s="select perm_inbound_lv1 from company_companycfg where company_id="+str(c.id)
                    cur.execute(s)
                    r=cur.fetchone()
                    if r:
                        data['show_inbound']=r[0]
                    cur.close()
            else:
                data['e'] =  _('账号未激活') 
        else:
            data['e'] = _('账号不存在')                    
    else:    
        data['e']='user_account,user_password,openid required'
    return JsonResponse(data)


@csrf_exempt
def wx_change_pwd(request):   
    data={'e':'','msg':''}
    openid=request.POST.get('openid','')
    acc=request.POST.get('user_account','')
    pwd0 = request.POST.get('password_old', '')    
    pwd1 = request.POST.get('password_new', '')
    user=None

    #帐号，原密码，新密码, openid 必传
    if acc and  pwd0 and  pwd1 and openid:  
        user = authenticate(username=acc, password=pwd0)
        if user is not None and user.is_active:
            user.wx_openid=openid               
            user.set_password(pwd1) 
            user.pwd_changed=True
            user.save()
            data['msg']=_('密码修改成功')
        else:
            data['e'] = _('帐号或原密码错误')            
            
    else:
        phone = request.POST.get('phone', '')
        code = request.POST.get('code', '')

        if phone and code and acc and pwd1 and openid: 
            user = User.objects.filter(id=int(acc))
            if user: 
                cur = connection.cursor()
                cur.execute("select tm,code from sales_sms where phone = '"+phone+"' order by id desc limit 1")
                r = cur.fetchone()
                if r: 
                    if code == r[1]: 
                        
                        for u in user:
                            u.wx_openid = openid
                            u.set_password(pwd1)
                            u.pwd_changed = True
                            u.save()
                        data['msg'] = '密码修改成功'
                    else:
                        data['e'] = '验证码不正确'
                else:
                    data['e'] = '数据异常'
            else:
                data['e'] = '帐号或原密码错误'  
                
        else: 
            data['e'] = 'user account, old & new password, openid required'    
    return JsonResponse(data)


#2020-11-23
@csrf_exempt
def wx_reset_password(request):  
    data={'e':'','msg':''}
    e=time_key_check(request,sK)
    if e:
        data['e']=e
        return JsonResponse(data)   
    openid=request.POST.get('openid','')
    acc=request.POST.get('user_account','')
    pwd = request.POST.get('password', '')   

    #帐号，密码，openid 必传
    if acc and  pwd and  openid:          
        us=User.objects.filter(username=acc)
        if us:
            u=us[0]
            u.set_password(pwd)
            u.save()
            data['msg']=_('密码重置成功')        
        else:
            data['e'] = _('帐号不存在')               
    else:      
        data['e']='user account,password, openid required'
    return JsonResponse(data)

#2021-03-19
def get_user_lonin_info(u):  
    data={
        'e':'',  
        'is_staff':False,
        'openid':'',
        'bind_wxopenid':'',
        'pwd_changed':False,
        'user_id':0,
        'user_name':0,      
        'last_name':'',
        'company_id':0,
        'company_name':'',       
        'logo_url':'',
        'img_bg_url':'',
        'ancestor_id':0,
        'show_inbound':0,
        'level':-1,
        'roles':[]
    }   
    if not u:
        return data
    staffs=Staff.objects.filter(user_id=u.id)
    if staffs:
        data['is_staff']=True
    data['user_id']=u.id
    data['user_name']=u.username
    data['last_name']=u.last_name
    if u.bind_wxopenid:
        data['bind_wxopenid']=u.bind_wxopenid
    if u.company:
        c=u.company
        data['company_id']=c.id
        data['ancestor_id']=c.ancestor_id
        data['company_name']=c.name   
        data['level']=c.level.sn
        data['pwd_changed']=u.pwd_changed
        for g in u.groups.all():
            data['roles'].append(g.name)            
        cs=Company.objects.filter(id=c.ancestor_id)
        if cs:
            c=cs[0]
            if c.img_logo:  
                data['logo_url']=c.img_logo.url
            if c.img_bg:
                data['img_bg_url']=c.img_bg.url                
            #2020-07-15 是否允许一级经销商入库
            cur=connection.cursor()
            s="select perm_inbound_lv1 from company_companycfg where company_id="+str(c.id)
            cur.execute(s)
            r=cur.fetchone()
            if r:
                data['show_inbound']=r[0]
            cur.close()
    return data  


#2021-03-19
def auxiliary_oauth_login(request,what):
    e=time_key_check(request,sK)
    if e:        
        return JsonResponse({'e':e})

    u=None
    if what=='cellphone':
        phone=request.GET.get('phone','')
        us=User.objects.filter(username=phone)
        if us:
            u=us[0]
            data=get_user_lonin_info(u)
    elif what=='wechat':            
        a=[]
        wxopenid=request.GET.get('wxopenid','')    
        s="select id from company_user where bind_wxopenid='"+wxopenid+"'"             
        cur=connection.cursor()
        cur.execute(s)
        for r in cur.fetchall():        
            us=User.objects.filter(id=r[0])
            if us:
                u=us[0]        
                a.append(get_user_lonin_info(u))
        cur.close()
        data={'infor':a}
    elif what=='wxwallet':      
        a=[]
        wxopenid=request.GET.get('wxopenid','')
        #"select id from"

        s="select id from company_user where bind_wxopenid='"+wxopenid+"' and wx_openid in (select wx_openid from company_wxopenid "+\
            "where id in (select wxopenid_id from company_wxwallet))"
        #已审核过的微信钱包
        #sAnd="and (select id from company_wxwallet where wxopenid_id="+\
        #    "(select id from company_wxopenid where wx_openid='"+wxopenid+"' limit 1))>0"
        #s="select id from company_user where wx_openid='"+wxopenid+"' "+sAnd
        
        cur=connection.cursor()
        cur.execute(s)
        for r in cur.fetchall():        
            us=User.objects.filter(id=r[0])
            if us:
                u=us[0]        
                a.append(get_user_lonin_info(u))
        cur.close()
        data={'infor':a}
    return JsonResponse(data)


#小程序自动登录
def wx_jscode_oauth(request):
    data={
        'e':'',  
        'is_staff':False,
        'isTerminal':False,
        'openid':'',
        'pwd_changed':False,
        'user_id':0,
        'user_name':0,      
        'last_name':'',
        'company_id':0,
        'company_name':'',
        'bind_wxopenid':'',
        #'logo_company':'',
        #'logo_ancestor':'',
        'logo_url':'',
        'img_bg_url':'',
        'ancestor_id':0,
        'show_inbound':0,
        'level':-1,
        'roles':[]
    }   
   
    jscode=request.GET.get('jscode','')
    if not jscode:
        data['e']='jscode missing'
        return JsonResponse(data)

    appid=WX["appid"]
    appsecret=WX["appsecret"]
    
    #2020-11-02 此处的officialaccount_id  实际为小程序APPID,为避免重新发布，保留原参数名
    acc=request.GET.get('officialaccount_id','')
    
    #2020-11-02
    app_id=request.GET.get('appid','')
    if app_id:    
        acc=app_id      

    if acc:
        #os=WxOfficialAccounts.objects.filter(id=int(acc))
        os=WxApp.objects.filter(id=int(acc))
        
        if os:
            o=os[0]
            appid=o.appid
            appsecret=o.appsecret

    #print(appid,appsecret)
    wx_url=WX["url_jscode2session"].format(appid=appid,appsecret=appsecret,jscode=jscode)

    #print(wx_url)
    res = requests.get(wx_url)
    r= res.json()
    if 'errcode' in r:
        if r['errcode']:
            data['e']=str(r['errcode'])+":"+r['errmsg']
            return JsonResponse(data)
    openid=r['openid']
    data['openid']=openid      
    us=User.objects.filter(wx_openid=openid).order_by('-last_login')
    if us:
        u=us[0]
        staffs=Staff.objects.filter(user_id=u.id)
        if staffs:
            data['is_staff']=True
        data['user_id']=u.id
        data['user_name']=u.username
        data['last_name']=u.last_name
        if u.bind_wxopenid:
            data['bind_wxopenid']=u.bind_wxopenid
        if u.company:
            c=u.company
            data['company_id']=c.id
            data['ancestor_id']=c.ancestor_id
            data['company_name']=c.name   
            data['level']=c.level.sn
            data['isTerminal'] = c.is_terminal
            data['pwd_changed']=u.pwd_changed
            for g in u.groups.all():
                data['roles'].append(g.name)
            #if c.img_logo:                
            #    #data['logo_company']=str(base64.b64encode(c.img_logo.read()), encoding='utf-8') 
            #    data['logo_url']=c.img_logo.url
            cs=Company.objects.filter(id=c.ancestor_id)
            if cs:
                c=cs[0]
                if c.img_logo:                   
                    #data['logo_ancestor']=str(base64.b64encode(c.img_logo.read()), encoding='utf-8')  
                    data['logo_url']=c.img_logo.url
                if c.img_bg:
                    data['img_bg_url']=c.img_bg.url                
                #2020-07-15 是否允许一级经销商入库
                cur=connection.cursor()
                s="select perm_inbound_lv1 from company_companycfg where company_id="+str(c.id)
                cur.execute(s)
                r=cur.fetchone()
                if r:
                    data['show_inbound']=r[0]
                cur.close()
    return JsonResponse(data)    
   




@csrf_exempt
def wx_logout(request):
    data={'e':'','openid':'','user_name':'','user_id':'','openid_post':''}

    r = json.loads(request.body)
    openid=r['openid']
    if not openid:
        data['e']="openid can't be null"
        return JsonResponse(data)
    data['openid_post']=openid
    us=User.objects.filter(wx_openid=openid)
    if not us:
        data['e']='invalid openid'
    else:
        for u in us:        
            u.wx_openid=None
            u.save()
            data['openid']=u.wx_openid
            data['user_id']=u.id
            data['user_name']=u.username
    return JsonResponse(data)



#2020-07-10 是否统一使用 get_wx_access_token?????
def get_access_token():
    data={'e':'','token':''}
    rd=redis.StrictRedis(host='localhost', port=16379, password='tjredis139', decode_responses=True)
    tk=rd.get('davis_wx_accesstoken')
    if not tk:
        res = requests.get(WX['url_access_token'])
        r=json.loads(res.text)       
        if 'errcode' in r:
            data['e']=r['errmsg']
        else:
            rd.set('davis_wx_accesstoken',r['access_token'],7000)
            data['token'] = r['access_token']            
    else:
        data['token']=tk            
    return  data


def get_wx_access_token(wx_officail_account_id):
    data={'e':'','token':''}
    cur=connection.cursor()
    cur.execute("select wx_appid,wx_appsecret FROM sales_wxofficialaccounts where id="+str(wx_officail_account_id))        
    r=cur.fetchone()
    if r:
        if int(wx_officail_account_id)==6: 
            #使用了天鉴公众号授权，为防止TOKEN过期或影响其它APP调用，这里调用颂恩接口
        
            res = requests.get(WX['url_access_token_fmt'].format(r[0],r[1]))
            r=json.loads(res.text)    

            if 'errcode' in r:
                data['e']=r['errmsg']
            else:            
                data['token'] = r['access_token'] 
                rd.set('wx_accesstoken_'+str(wx_officail_account_id),r['access_token'],7000)
        else:
            tk=rd.get('wx_accesstoken_'+str(wx_officail_account_id))
            if not tk:   
                if r:        
                    res = requests.get(WX['url_access_token_fmt'].format(r[0],r[1]))
                    r=json.loads(res.text)            
                    if 'errcode' in r:
                        data['e']=r['errmsg']
                    else:
                        rd.set('wx_accesstoken_'+str(wx_officail_account_id),r['access_token'],7000)
                        data['token'] = r['access_token']
                
            else:
                data['token']=tk   

    else:
        data['e']='WeChat official id error'




    cur.close()
    return  data


#2020-10-09 
def get_wx_js_sign(request,wx_officail_account_id):
    url=request.GET.get('url')       
    if int(wx_officail_account_id)==6:
        r=requests.post(
            'http://tjfnew.china315net.com/ajax/wxjsinfo_interface.ashx',
            {'url':url}
        )
        r=json.loads(r.text)
        data={
            'noncestr': r['nonceStr'],  
            'signature':r['signature'],
            'timestamp':r['timestamp'],
            'appid':r['appId'],
        } 
    else:
        cs = '1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'
        nonce = "".join(random.sample(cs, 16))
        data={'noncestr': nonce, 'url':url, 'jsapi_ticket':'', 'timestamp':''} 
        cur=connection.cursor()
        cur.execute("select wx_appid,wx_appsecret FROM sales_wxofficialaccounts where id="+str(wx_officail_account_id))       
        r=cur.fetchone()
        if r:        
            appid=r[0]
        else:
            data['e']='WeChat official id error'
            return JsonResponse(data)        
        tk=rd.get('wx_jsapi_ticket_'+str(wx_officail_account_id))  
        if not tk:
            d=get_wx_access_token(wx_officail_account_id)
            if d['e']:
                data['e']=d['e']
                return JsonResponse(data)
            tk=d['token']        
            res = requests.get(WX['url_ticket_fmt'].format(tk))
            r=json.loads(res.text)  
            if 'ticket' in r:
                tk=r['ticket']
                rd.set('wx_jsapi_ticket_'+str(wx_officail_account_id),tk,7000)                          
            else:
                data['e']=r['errmsg']
                return JsonResponse(data)
        t=str(int(time.time()))
        data['timestamp']=t
        data['jsapi_ticket']=tk 
        sort=sorted(data.items(), key=lambda d:d[0], reverse = False )    
        s="&".join(str(x)+"="+str(y) for x,y in sort)
        m2=hashlib.sha1(s.encode('utf8'))
        data['signature']=m2.hexdigest()
        data['appid']=appid 
        data.pop('url')
        data.pop('jsapi_ticket') 
    return JsonResponse(data)


   

#def send_sms(request,company_id):
#    data={'e':'company id error'}
#    if company_id=='17738':        
#        phone=request.POST.get('phone','')
#        cur=connection.cursor()
#        cur.execute("select id from sales_consumeraccount where phone='"+phone+"'")
#        http://tjfnew.china315net.com/api/requestCode.ashx?phone=18108807612&compname=婷曼逸
#    return JsonResponse(data)




#返回客户有特殊要求的自定义页面
def wx_customize_page(request,company_id,page_id,app_id='0'):
    #uid=request.COOKIES.get(WX_CK_UID, '')
    uid=request.COOKIES.get(WX_CK_UID+'_'+company_id+"_"+app_id, '')


    cur=connection.cursor()
    if company_id=='17738' and page_id=='2': #云南婷曼逸商贸有限公司 防伪页面
        '''
        url='/wx/tjfw/base/oauth/'+company_id+'/'   
        if not uid:                  
            cur.close()
            return HttpResponseRedirect(url)  
        label_code=request.GET.get('label_code','')
        data={'e':'', 'registered':False, 'label_code':label_code,'openid':'','consumer_id':''}
        registered=False

        cur.execute("select wx_openid,name,phone,birthday from sales_consumeraccount where id="+uid)
        r=cur.fetchone()
        if not r:
            cur.close()
            return HttpResponseRedirect(url)  

        if not r[1] or not r[2] or not r[3]:
            cur.close()
            return HttpResponseRedirect(url)
        registered=True          
        data['consumer_id']=uid
        data['openid']=r[0]
        data['registered']=registered
        cur.close()
        '''        
        if not uid:                  
            consumer_id=request.GET.get('consumer_id','')
            if not consumer_id:
                cur.close()
                url='/wx/tjfw/base/oauth/'+company_id+'/?prefer=2'
                return HttpResponseRedirect(url)
            else:
                cur.execute("select id from sales_consumeraccount where id="+consumer_id)
                r=cur.fetchone()
                if not r:
                    url = '/wx/tjfw/base/oauth/' + company_id + '/?prefer=2'
                    cur.close()
                    return HttpResponseRedirect(url)
                s="select wx_openid,name,phone,birthday,id from sales_consumeraccount where id="+consumer_id
        else:
            uid=decrypt_p(uid)
            s="select wx_openid,name,phone,birthday,id from sales_consumeraccount where id="+uid  
        cur.execute(s)
        r=cur.fetchone()
        if not r[1] or not r[2] or not r[3]:
            registered=False
        else:
            registered=True   
        return render(request,company_id+"/"+page_id+".html",{'registered':registered,'consumer_id':r[4],'openid':r[0]})

    elif company_id=='20445' and page_id=='10': #求真,分享
        if not uid:                  
            consumer_id=request.GET.get('consumer_id','')
            if not consumer_id:
                cur.close()
                url='/wx/tjfw/base/oauth/'+company_id+'/?prefer='+page_id
                return HttpResponseRedirect(url)
            else:
                cur.execute("select id from sales_consumeraccount where id="+consumer_id)
                r=cur.fetchone()
                if not r:
                    url = '/wx/tjfw/base/oauth/' + company_id + '/?prefer='+page_id
                    cur.close()
                    return HttpResponseRedirect(url)   
                #s="select wx_openid,name,phone,birthday,id from sales_consumeraccount where id="+consumer_id
        else:
            uid=decrypt_p(uid)
        #s="select wx_openid,name,phone,birthday,id from sales_consumeraccount where id="+uid  
        #cur.execute(s)
        #r=cur.fetchone()      
        parnt_id=request.COOKIES.get('parent_id', '')
        return  render(request,company_id+"/"+page_id+".html",
            {'consumer_id':uid,'parent_id':parent_id}
        )    
    return render(request, company_id+"/"+page_id+".html")   
        



#消费者扫码，经过微信授权后，在这里做实际业务逻辑处理
def wx_process_label_scan(request,company_id,app_id=None):
    if not app_id:
        app_id='0'
    label_code=request.GET.get('label_code','')   
    openid=request.GET.get('openid','')
    consumer_id=request.COOKIES.get(WX_CK_UID+'_'+company_id+"_"+app_id, '')
    #2020-12-24
    if consumer_id:
        consumer_id=decrypt_p(consumer_id)

    fr_official=request.GET.get('from_official_account','') 
    province=request.GET.get('province','null')
    city=request.GET.get('city','null')
    address=request.GET.get('address','null')    
    lng=request.GET.get('lng','')
    lat=request.GET.get('lat','')
   
   
    #if request.session['wx_openid']:
        #openid=request.session['wx_openid']    
    


    #婷曼逸 前端需要 is_new 判断
    is_new=request.GET.get('is_new','') #0:旧帐号,1:新帐号  


    lng_lat='null'
    if lng and lat:
        lng_lag='{'+lng+','+lat+'}'    
        pos_perm='true'
    else:
        pos_perm='false'
    amd_id1='null'
    amd_id2='null'
    amd_id3='null'
    act_id='null'
    wx_labelscan_id=None
    cur=connection.cursor()    
    
    
    if company_id=='17738': #云南婷曼逸商贸有限公司
        if request.method=='GET':
            registered=False  
            #是否已有注册信息           
            s="select id from sales_consumeraccount where id='"+consumer_id+"' and name is not null and phone is not null"
            cur.execute(s)
            r=cur.fetchone()    
            if r:
                registered=True
            #cur.close()    
            response=render(request, company_id+'/index.html', {
                'registered':registered,
                'from_official_account':fr_official,
                'label_code':label_code,
                'openid':openid,
                'consumer_id':consumer_id
            })
        else:
            v=request.POST.get('verifycode','')
            if v=='1': #短信发送验证码
                data={'msg':'','reason':'','yzm':'','e':''}
                phone=request.POST.get('phone')
                cur.execute("select id from sales_consumeraccount where  phone='"+phone+"'")
                r=cur.fetchone()
                if r:
                    data['e']="您填写的手机号码已被注册"
                else:
                    res=requests.get('http://tjfnew.china315net.com/api/requestCode.ashx',{'phone':phone,'compname':'婷曼逸'})
                    t=res.content.decode("utf-8")
                    if t=='no':
                        data['msg']='no'
                    else:
                        d=json.loads(t)
                        data['msg']=d['msg']
                        data['reason']=d['reason']
                        data['yzm']=d['yzm']
                        #{"msg":"success","reason":"done！","yzm":"6360"}
                return JsonResponse(data)
            else:
                name=request.POST.get('name')
                phone=request.POST.get('phone')
                birth='null'
                age='null'
                b=request.POST.get('birth','')
                a=request.POST.get('age','')
                if b:
                    birth="'"+b+"'"
                if a:
                    age=a
                s="update sales_consumeraccount set name='"+name+"',phone='"+phone+"',birthday="+birth+",age="+age+" where id="+consumer_id           
                cur.execute(s)            
                response=JsonResponse({'data':{'e':'ok'}})
    elif company_id == "20445" :
        response = render(request, 'label_scan/' + str(company_id) + '/index.html')
    # elif company_id == "18472" :

    #     response = render(request, 'label_scan/' + str(company_id) + '/index.html')


    elif company_id =="19257" or company_id == "18801" or company_id == "19318" or company_id =="17095" or company_id =="30371":
        data = {
            'e': '',
            'award': {
                'code':400,
                'reason':'',
                'data': '',
                'winner_id':0
            },
            'label_code':'',
            'company_id':'',
            'consumer_id':'',
            'is_need_yzm':False
        }
        label_code = request.session.get('wx_label_code', label_code)
        #consumer_id = request.GET.get('consumer_id', consumer_id)
        data['label_code'] = label_code
        data['company_id'] = company_id
        data['consumer_id'] = consumer_id
        pageUrl = 'label_scan/' + str(company_id) + '/index.html'

        #print(consumer_id)
        member = Member.objects.filter(consumer_id=consumer_id,company_id=int(company_id))
        if not member:
            consumer = ConsumerAccount.objects.get(id=int(consumer_id))
            name = consumer.wx_nickname
            gender = consumer.gender
            province = ''
            city = ''
            birthday = None
            if consumer.wx_province:
                province = consumer.wx_province
            if consumer.wx_city:
                city = consumer.wx_city
            if consumer.birthday:
                birthday = consumer.birthday

            member = Member(
                consumer=consumer,
                company_id=int(company_id),
                name=name,
                gender=gender,
                remark='消费者扫码注册',
                province=province,
                city=city,
                birthday=birthday,
                grade_id=1,
                headImg=consumer.wx_headimgurl,
                exps=0
            )
            member.save()

        need_draw_on_load = True
        
        
        if company_id == "19257":
            need_draw_on_load = False
            cur.execute("select count(*) from label.lbl_vcode where code = '"+label_code+"'")
            rc = cur.fetchone()
            if rc[0] >0:
                data['is_need_yzm'] = True
        elif company_id == "18801":
            if label_code.startswith('1035'): 
                need_draw_on_load=False
            else: 
                cur.execute("select coalesce((select code_a from  label.lbl_abcode where code_b='" + label_code + "'),'" + label_code + "')")
                rrrr = cur.fetchone()
                label_code = rrrr[0] 
        # 诗仙太白活动
        elif company_id == "19318":
            #if consumer_id !="5304" and consumer_id !="5688":
                #return HttpResponseRedirect('http://tjfnew.china315net.com/update.html')

            
            #判断是否有刮损瓶盖的现象出现
            if label_code !="104505468674" and label_code.startswith('1045'): 
                cid = int(label_code)
                #if cid >=104500000011 and cid <=104507982526:
                if cid < 104504321812 or cid > 104504321934: 
                        
                    cur.execute("select coalesce((select code_b from label.lbl_abcode where code_a='" + label_code + "'),'')")
                    rrrr = cur.fetchone()
                    if rrrr: 
                        appeal = Consumer_Appeal.objects.filter(label_code=rrrr[0] ,status=1)
                        #如果瓶盖有申诉且通过 则去查询瓶盖号码
                        if appeal:  
                            label_code = rrrr[0] 
            
            need_draw_on_load = False
            # 如果是A标
            if label_code.startswith('1045') or label_code.startswith('1047'): 
                data['e']='a'
            # 如果是B标
            else:   
                cur.execute("select count(*) from sales_consumerwinner where consumer_id = "+consumer_id +
                                 " and label_code is not null and company_id = 19318 ")
                cnt = cur.fetchone() 
                if cnt[0] < 100:      
                    data['e']=''
                else:
                    data['e'] = 'full' 
                
        #壹支好友
        elif company_id == "30371":
            is_access = request.GET.get('access','')
            if is_access != "1":
                need_draw_on_load = False 
        
        elif company_id == "17095": 
            need_draw_on_load = False 
            cur.execute("select count(*) from sales_activities_code_range where code_start<='"+label_code+"' and code_end>='"+label_code+"' and company_id = 17095 ")
            cnt = cur.fetchone() 
            if cnt[0] > 0: 
                need_draw_on_load = True   
        
        #如果需要扫码后抽奖
        if need_draw_on_load:
            d = get_activity(company_id, label_code,lng=0, lat=0) 
            if d['activity']:
                activity = d['activity'] 
                act_id = activity.id
                activity_award = None
                winner =ConsumerWinner.objects.filter(label_code=label_code,company_id=int(company_id))
                # 如果已经中奖
                if winner:
                    winner = winner[0]
                    if winner.award:
                        activity_award = winner.award
                        # 如果奖品还没领取
                        if not winner.tm_redeemed:
                            #如果领奖的是同一个人
                            if winner.consumer_id == int(consumer_id):
                                data['award']['code'] = 200
                                data['award']['reason'] = '成功'
                            else:
                                data['award']['code'] = 402
                                data['award']['reason'] = '奖品已被领取'
                        else:
                            data['award']['code']= 401
                            data['award']['reason'] = '奖品已领取'
                        #奖品赋值
                        #奖品赋值
                        points = 0
                        amount = 0

                        award = activity_award.award

                        if award.points:
                            points = award.points
                        if award.amount:
                            amount = str(award.amount)
                        award_ = {
                            "id": activity_award.id,
                            "name": activity_award.name,
                            "points": points,
                            "awardcat_id": award.awardcat_id,
                            "photo": award.photo.url,
                            "amount": amount 
                        }
                        data['award']['data'] = json.dumps(award_) 
                    else:
                        data['award']['code'] = 400
                        data['award']['reason'] = '未中奖'
                    data['award']['winner_id'] = encrypt_p(str(winner.id))
                else:
                    if d['award']:
                        activity_award = d['award']
                    else:
                        activity_award = d['activity'].draw()
                    activity_award_id = None
                    award_name = ''
                    award_points = '0'
                    award_amount = '0'
                    award_img = ''
                    award_cat = ''
                    is_need_hexiao = False
                    # 如果中奖了
                    if activity_award:
                        activity_award_id = activity_award.id
                        data['award']['code'] = 200
                        # 奖品赋值
                        points = 0
                        amount = 0
                        award = activity_award.award
                        award_name = award.name
                        award_points = award.points
                        award_amount = award.amount
                        award_img = award.photo
                        award_cat = award.awardcat.name
                        is_need_hexiao = award.is_need_hexiao


                        if award.points:
                            points = award.points
                        if award.amount:
                            amount = str(award.amount)
                        award_ = {
                            "id": activity_award.id,
                            "name": activity_award.name,
                            "points": points,
                            "awardcat_id": award.awardcat_id,
                            "photo": award.photo.url,
                            "amount": amount
                        }
                        data['award']['data'] = json.dumps(award_)
                    else:
                        data['award']['code'] = 400
                        data['award']['reason'] = '未中奖'


                    w = ConsumerWinner(
                        company_id=int(company_id),
                        consumer_id=consumer_id,
                        labelscan_id=wx_labelscan_id,
                        label_code=label_code,
                        activity_id=activity.id,
                        award_id=activity_award_id,
                        activity_name=activity.name,
                        award_name=award_name,
                        award_points=award_points,
                        award_amount=award_amount,
                        award_img=award_img,
                        award_cat=award_cat,
                        is_need_hexiao=is_need_hexiao
                    )
                    w.save()

                    data['award']['winner_id'] = encrypt_p(str(w.id))

        response = render(request, pageUrl, data)
        if company_id =="19318":
            response.delete_cookie('winner_id')
    
    else:  
        e={'code':0,'msg':''}  
        label0=label_code

        cur.execute("select coalesce((select code_a from  label.lbl_abcode where code_b='"+label_code+"'),'"+label_code+"')")
        rrrr=cur.fetchone()
        label_code=rrrr[0]
        

       

        #营销活动
        activity=None
        award=None 
        seeds_id=None
        product=None
        #标签是否在固定奖池中
        cur.execute("select activity_id,award_id,id from sales_lotteryseeds where label_code='"+label_code+"'")
        rrrr=cur.fetchone()

       
        if rrrr:
            seeds_id=r[2]
            acts=Activities.objects.filter(id=r[0])
            if r[1]:
                award=Award.objects.filter(id=r[1])
            if acts:
                activity=acts[0]
                if not award:
                    award=activity.draw() 
        else:
            if label_code:
                d=get_activity(company_id,label_code,lng=0,lat=0)   
                #data={'activity':None,'award':None,'seeds_id':None,'package_id':0,'product_name':'','product_id':0,'e':''}
                wx_package_id=d['package_id']
                product=d['product_name']
                if d['activity']:
                    activity=d['activity']  
                    act_id=activity.id
                    #标签号码是否已被领取
                    s="select consumer_id,tm from sales_consumerwinner where label_code='"+label_code+"' and tm_redeemed is not null"
                    cur.execute(s)
                    if cur.fetchone():
                        e={'code':10,'msg':WX_ERRS[10]}   
                    else:
                        award=d['activity'].draw()  

        if not label_code:
            label_code=request.session.get('wx_label_code',label_code)        
        if not consumer_id:
            consumer_id=request.session.get('wx_consumer_id',consumer_id)  
            if not consumer_id:
                cur.execute("select id from sales_consumeraccount where wx_openid='"+openid+"'")
                r8=cur.fetchone()
                if r8:
                    cunsumer_id=r8[0]            
        
        #写入扫码记录
        s="insert into sales_labelscan ("+\
                "company_id,consumer_id,activity_id,"+\
                "province,city,address,pos_permission,lng_lat,amd_id1,amd_id2,amd_id3,"+\
                "label_code,label_pkg,label_id,label_pk_id,product_id"+\
            ") select "+\
            company_id+","+str(consumer_id)+","+str(act_id)+","+\
            province+","+city+","+address+","+pos_perm+","+lng_lat+","+amd_id1+","+amd_id2+","+amd_id3+","+\
            "c.code,a.label_pkg,a.label_id,a.label_pk_id,a.product_id from "+\
            "(select '"+label_code+"'::text code) c "+\
            "left outer join "+\
            "(select label_code,label_pkg,label_id,label_pk_id,product_id from inventory_stkdetails "+\
                    "where company_id="+str(company_id)+" order by vtype_id,tm limit 1"+\
            ") a "+\
            "on c.code=a.label_code returning id"      
        cur.execute(s)    
        r=cur.fetchone()
        wx_labelscan_id=r[0]         
        
        winner_id=None        
        f=settings.BASE_DIR+'\\templates\\label_scan\\'+str(company_id)+'\\index.html'
        if not os.path.exists(f):
            return JsonResponse({'e':str(company_id)+'\index.html 不存在'})


            
        #2020-12-11
        #写入中奖记录
        if award:
            w=ConsumerWinner(
                    company_id  =int(company_id), 
                    consumer_id =consumer_id,
                    labelscan_id=wx_labelscan_id,
                    label_code  =label_code,
                    activity_id=activity.id,
                    award_id=award.id                    
            )
            w.save()
            winner_id=w.id
          



        #2020-10-06 取微信授权公众号 
        s="select case when exists(select id from company_companycfg where company_id="+str(company_id)+" and tjfw_oauth=1)"+\
                " then 6 else (select id from  sales_wxofficialaccounts where company_id="+str(company_id)+") end"
        cur.execute(s)
        r=cur.fetchone()
        wx_id=0
        if r:
            if r[0]:
                wx_id=r[0]


        wx_package_id=0
        if 'wx_package_id' in  request.session:
            wx_package_id=request.session['wx_package_id']
            
        data={
                'e':e,   
                'activity':activity,
                'award':award,                       
                'product':product,
                'company_id':company_id,
                'label_code':label_code,
                'label0':label0,
                'winner_id':winner_id,
                'consumer_id':consumer_id,
                'package_id':wx_package_id
        }           
        
        response=render(request,'label_scan/'+str(company_id)+'/index.html', data)
                    

    if company_id=='17738': #云南婷曼逸商贸有限公司
        s="insert into sales_labelscan ("+\
                "company_id,consumer_id,activity_id,"+\
                "province,city,address,pos_permission,lng_lat,amd_id1,amd_id2,amd_id3,"+\
                "label_code,label_pkg,label_id,label_pk_id,product_id"+\
            ") select "+\
            company_id+","+consumer_id+","+str(act_id)+","+\
            province+","+city+","+address+","+pos_perm+","+lng_lat+","+amd_id1+","+amd_id2+","+amd_id3+","+\
            "c.code,a.label_pkg,a.label_id,a.label_pk_id,a.product_id from "+\
            "(select '"+label_code+"'::text code) c "+\
            "left outer join "+\
            "(select label_code,label_pkg,label_id,label_pk_id,product_id from inventory_stkdetails "+\
                    "where company_id="+company_id+" order by vtype_id,tm limit 1"+\
            ") a "+\
            "on c.code=a.label_code returning id"      
        cur.execute(s)    
        r=cur.fetchone()
        request.session['wx_labelscan_id']=r[0]
        
    
    #设置COOKIE
    #2020-08-20
    #des = DES.new(sW, DES.MODE_ECB)
    #onsumer_id=des.encrypt(pad8(str(consumer_id))) 
    #2020-12-12 使用签名COOKIE
    #response.set_signed_cookie('TJ_'+company_id+'_consumer_id',consumer_id,salt=settings.WX_SALT,expires=datetime.datetime.now()+datetime.timedelta(days=7))
    
    
    #response.set_cookie(WX_CK_UID, consumer_id, expires=datetime.datetime.now()+datetime.timedelta(days=7))      
    #2020-12-23    


    #2020-12-30 窜货预警
    if lng and lat: 
        try:
            bugsell_check(
                consumer_id=consumer_id,
                company_id=company_id,
                label_code=label_code,                
                gis_data={
                    'lng_lat':[lng,lat],
                    'province':province,
                    'city':city,
                    'address':address
                }
            )
        except Exception:
            pass

    response.set_cookie(WX_CK_UID+'_'+company_id+"_"+app_id,encrypt_p(str(consumer_id)), expires=datetime.datetime.now()+datetime.timedelta(days=7))  
    response.set_cookie('label_code', label_code, max_age=3600)
    response.set_cookie('TJWX_consumer_id', consumer_id, max_age=3600)
    return response   



@csrf_exempt
def ajax_get_activity(request):
    data = {
        'e': '',
        'award': {
            'code':400,
            'reason':'',
            'data': '',
            'winner_id':0
        }, 
    }
    company_id = request.GET.get('company_id','')
    label_code = request.COOKIES.get('label_code','') 
    consumer_id = request.COOKIES.get('TJWXUSR_'+company_id+'_0','')
    if not company_id or not label_code or not consumer_id:
        return JsonResponse({'e':'数据丢失'})

    consumer_id = decrypt_p(consumer_id)
    
    d = get_activity(company_id, label_code,lng=0, lat=0) 
    if d['activity']:
            activity = d['activity']
            
            product_id = d['product_id']
            agent = d['agent_id']

            data['product_id']=product_id
            data['agent_id']=agent
            
            act_id = activity.id
            activity_award = None
            winner =ConsumerWinner.objects.filter(label_code=label_code,company_id=int(company_id))
            # 如果已经中奖
            if winner:
                winner = winner[0]
                if winner.award:
                    activity_award = winner.award
                    # 如果奖品还没领取
                    if not winner.tm_redeemed:
                        #如果领奖的是同一个人
                        if winner.consumer_id == int(consumer_id):
                            data['award']['code'] = 200
                            data['award']['reason'] = '成功'
                        else:
                            data['award']['code'] = 402
                            data['award']['reason'] = '奖品已被领取'
                    else:
                        data['award']['code']= 401
                        data['award']['reason'] = '奖品已领取'
                    #奖品赋值
                    points = 0
                    amount = 0

                    award = activity_award.award

                    if award.points:
                        points = award.points
                    if award.amount:
                        amount = str(award.amount)
                    award_ = {
                        "id": activity_award.id,
                        "name": activity_award.name,
                        "points": points,
                        "awardcat_id": award.awardcat_id,
                        "photo": award.photo.url,
                        "amount": amount ,
                        "award_id":award.id
                    }
                    data['award']['data'] = award_
                else:
                    data['award']['code'] = 400
                    data['award']['reason'] = '未中奖'
                data['award']['winner_id'] = encrypt_p(str(winner.id))
            else:
                if d['award']:
                    activity_award = d['award']
                else:
                    activity_award = d['activity'].draw()
                activity_award_id = None
                award_name = ''
                award_points = '0'
                award_amount = '0'
                award_img = ''
                award_cat = ''
                is_need_hexiao = False
                # 如果中奖了
                if activity_award:
                    activity_award_id = activity_award.id
                    data['award']['code'] = 200
                    # 奖品赋值
                    points = 0
                    amount = 0
                    award = activity_award.award
                    award_name = award.name
                    award_points = award.points
                    award_amount = award.amount
                    award_img = award.photo
                    award_cat = award.awardcat.name
                    is_need_hexiao = award.is_need_hexiao


                    if award.points:
                        points = award.points
                    if award.amount:
                        amount = str(award.amount)
                    award_ = {
                        "id": activity_award.id,
                        "name": activity_award.name,
                        "points": points,
                        "awardcat_id": award.awardcat_id,
                        "photo": award.photo.url,
                        "amount": amount,
                        "award_id":award.id
                    }
                    data['award']['data'] = award_
                else:
                    data['award']['code'] = 400
                    data['award']['reason'] = '未中奖'


                w = ConsumerWinner(
                    company_id=int(company_id),
                    consumer_id=consumer_id,
                    labelscan_id=None,
                    label_code=label_code,
                    activity_id=activity.id,
                    award_id=activity_award_id,
                    activity_name=activity.name,
                    award_name=award_name,
                    award_points=award_points,
                    award_amount=award_amount,
                    award_img=award_img,
                    award_cat=award_cat,
                    is_need_hexiao=is_need_hexiao
                )
                if agent:
                    w.agent_id = agent 
                if product_id:
                    w.product_id =product_id
                w.save()

                data['award']['winner_id'] = encrypt_p(str(w.id))

    return JsonResponse(data)



#2020-09-30 窜货检查
def bugsell_check(consumer_id,company_id,label_code,gis_data):
    data={'e':'','id':0}
    cur=connection.cursor()  
    tb='label.t'+label_code[:4]
    label_pk_id=0
    label_pkg=0    
    try:    
        s="select package_id," + \
            "coalesce((select parent_id from " +tb+ " where id=a.parent_id),parent_id,id) " + \
            "from " +tb+ " a where code='" + label_code +"' "+\
            "order by id desc limit 1"     
        cur.execute(s)
        r = cur.fetchone()
        if not r:
            data['e'] = 'label_code error'
        else:
            label_pkg=r[0]
            label_pk_id=r[1]    
    except Exception as e:
        data['e'] = 'label_code error'
    s="select level_id,"+\
        "(select auth_area from company_companycfg where company_id="+str(company_id)+") "+\
        "from company_company a where id="+str(company_id)    
    cur.execute(s)
    r=cur.fetchone()
    cfg_auth=None
    if not r:
        data['e']='company_id error'
    else:
        if r[0]!=1:
            data['e']='company level error'
        else:
            cfg_auth=r[1]
    if data['e']:
        return data  

    lng=str(gis_data['lng_lat'][0])
    lat=str(gis_data['lng_lat'][1])    
    #简单授权(授权长期有效)
    if cfg_auth==1:      
        s="select id,company_id,company_to_id,product_id,dt,"+\
            "array_to_string((select adm_ids from company_company where id=company_to_id),',') "+\
            "from inventory_stkdetails where label_pk_id="+str(label_pk_id)+\
            " and ancestor_id="+str(company_id)+" and vtype_id=2 order by dt"  
        cur.execute(s)
        for r in cur.fetchall():
            ids=r[5].split(',')
            a=[]
            for id in ids:                
                s="(select public.st_distance(public.st_geogfromtext('SRID=4326;POINT("+str(lng)+" "+str(lat)+")'),"+\
                        "(select geom from public.adm where id="+id+")))::int"   
                a.append(s)
            if a:   
                s="select LEAST("+",".join(a)+")"    
                cur.execute(s)
                r1=cur.fetchone()
                d=r1[0]
                if d>0:
                    b=BugSell(
                        consumer_id=consumer_id,        
                        label_code=label_code,        
                        ancestor_id=int(company_id),
                        label_pkg=int(label_pkg),   
                        tm_delivery=datetime.datetime.now(),
                        province=gis_data['province'],  
                        city=gis_data['city'],
                        address=gis_data['address'],        
                        lng_lat=gis_data['lng_lat'],
                        stkdetails_id=r[0],
                        company_id=r[1],
                        agent_id=r[2],       
                        product_id=r[3],       
                        distance=d,   
                    )
                    b.save()
                    data['id']=b.id                    
    #严格授权(定期增加授权记录)    
    elif cfg_auth==2:
        pass                             
                    
    cur.close()
    return data

   




#2020-09-30 进入扫码页面后，返回经纬度，写入查询记录
def record_label_scaned(request,company_id):
    data={'e':''}
    r=json.loads(request.body.decode('utf-8'))
    label_code=r['label_code']    
    lng_lat=r['lng_lat']    
    consumer_id=r['consumer_id']
    pos_permission=False
    province=None
    city=None
    address=None
    if lng_lat:
        pos_permission=True       
  
    if not label_code:
        data['e']='labe code missing'
    else:
        if lng_lat:
            lng_lat=gcj02_to_bd09(float(lng_lat[0]),float(lng_lat[1]))
            headers={'User-Agent':'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/63.0.3239.132 Safari/537.36 QIHU 360SE'}
            res=requests.get(settings.BD_URL+"&location="+str(lng_lat[1])+","+str(lng_lat[0])+"&output=json",headers=headers)
            p=json.loads(res.text)   
            if p["status"]==0:
                province=p['result']['addressComponent']['province']
                city=p['result']['addressComponent']['city']
                address=p['result']['formatted_address']

        cur=connection.cursor()
        s="select label_id,label_pk_id,label_pkg,product_id,company_to_id,"+\
            "(select adm_ids from company_company where id=a.company_to_id),id "+\
            "from inventory_stkdetails a where "+\
            "label_pk_id=(select coalesce(parent_id,id) from label.labelinfo where code='"+label_code+"')"+\
            " and ancestor_id="+company_id+" and vtype_id=2 order by id desc limit 1"


        #f=open('d:\\test_bugsell.txt','w')
        #f.write(s+'\r\n')

        cur.execute(s)
        r=cur.fetchone()
        d=None
        label_id=None
        label_pk_id=None        
        label_pkg_id=None
        product_id=None
        agent_id=None
        stkdetails_id=None        
        if r:   
            label_id=r[0]
            label_pk_id=r[1]
            label_pkg_id=r[2]
            product_id=r[3]
            agent_id=r[4]
            stkdetails_id=r[6]
            a=[]
            if lng_lat:
                for i in r[5]:        
                    s="(select public.st_distance(public.st_geogfromtext('SRID=4326;POINT("+str(lng_lat[0])+" "+str(lng_lat[1])+")'),"+\
                        "(select geom from public.adm where id="+str(i)+")))::int"    
                    a.append(s)
                if a:      
                    s="select LEAST("+",".join(a)+")"
                    
                    #f.write(s)
                    #f.close()

                    cur.execute(s)
                    r=cur.fetchone()
                    d=r[0]        
        l=LabelScan(
            consumer_id=int(consumer_id), 
            company_id = int(company_id),            
            label_code =label_code, 
            label_pkg = label_pkg_id,
            label_id = label_id,
            label_pk_id = label_pk_id,
            product_id = product_id,
            province = province,
            city = city,
            address = address,
            pos_permission = pos_permission,
            lng_lat = lng_lat
        )
        l.save()        
        if d:
            b=BugSell(
                consumer_id=consumer_id,        
                label_code=label_code, 
                label_pkg=label_pkg_id,
                agent_id=agent_id,  
                company_id=company_id,
                ancestor_id=company_id,     
                product_id=product_id,     
                stkdetails_id=stkdetails_id,        
                province=province,  
                city=city,
                address=address,        
                lng_lat=lng_lat,
                distance=d
            )
            b.save()
     
    return JsonResponse(data)     
    
    
  

#2020-07-04
#   微信授权，state 参数 代码:
#   i:tjfw_id                   (天鉴防伪OPENID         sales_tjfwaccount_id)
#   c:label_code                (扫码获得的标签号码）
#   o:wx_auth_acct_id           (微信授权公众号         sales_wxofficialaccounts_id)
#   m:customize_page_number     (授权后跳转自定义页面)
#2020-07-04



#消费者微信扫码后跳转到这里做微信授权
def wx_label_scan(request,company_id,app_id=None):
    #2020-12-24
    if not app_id:
        app_id='0'

    label_code=request.GET.get('cpid')  
    cur=connection.cursor()



    if company_id == "1" or company_id == "20445":
        ck = ''
    else:
        ck=request.COOKIES.get(WX_CK_UID+'_'+company_id+"_"+app_id, '')
        #response.set_cookie(WX_CK_UID+'_'+company_id+"_"+app_id,encrypt_p(consumer_id), expires=datetime.datetime.now()+datetime.timedelta(days=7))  
         

    #2020-08-18 当前标签是否有符合条件的营销活动
    #微信授权之前，不能取地理位置，先取出不包括地理位置条件的记录
    data=get_activity(company_id,label_code)

   
    #data = {'activity': None, 'award': None, 'seeds_id': None}
    activity_id=0
    award_id=0
    seeds_id=0
    wx_official_account_id=0

    #if data['activity']:
    #    activity_id=data['activity'].id
    #    #wx_official_account_id=data['activity'].wx_account.id
    #else:
    #    cur.execute("select wx_account_id from company_companycfg where company_id="+company_id)
    #    r=cur.fetchone()
    #    if r:
    #        if r[0]:
    #            wx_official_account_id=r[0]
    #2020-10-06   
    s="select case when exists(select id from company_companycfg where company_id="+str(company_id)+" and tjfw_oauth=1)"+\
        " then 6 else (select id from  sales_wxofficialaccounts where company_id="+str(company_id)+") end"



    #2020-11-02
    #try:
    #        f=open('d:\\hzy\\wx_label_scan.txt','w')
    #        f.write(s)
    #        f.close()
    #except Exception:
    #        pass




    cur.execute(s)
    r=cur.fetchone()        
    if r:
        if r[0]:
            wx_official_account_id=r[0]
    
    
    if data['award']:
        award_id=data['award'].id
    if data['seeds_id']:
        seeds_id=int(data['seeds_id'])
    request.session['wx_company_id']=int(company_id)
    request.session['wx_activity_id']=activity_id
    request.session['wx_award_id']=award_id
    request.session['wx_seeds_id']=seeds_id
    request.session['wx_label_code'] = label_code
    request.session['wx_auth_account_id']=wx_official_account_id
    #2020-08-20
    request.session['wx_package_id'] = data['package_id']
    request.session['wx_product_id'] = data['product_id']
    request.session['wx_product_name'] = data['product_name']
    #海南天鉴防伪 wx_official_account_id=6
    if ck:
        #uid=request.COOKIES[WX_CK_UID]        
        #2020-12-23
        uid=decrypt_p(ck)

        #f=open('d:\\datacenter\\cookies.txt','a')
        #f.write('company_id:'+company_id+',uid:'+uid+'\r\n')
        #f.close()

        #2020-08-20
        #if not str(uid).isdigit():
        #    des = DES.new(sW, DES.MODE_ECB)
        #    uid=des.decrypt(uid).strip()
        s="select wx_openid from sales_consumeraccount where id="+uid
        cur.execute(s)
        r=cur.fetchone()
        if r:              
            #跳转到扫码业务逻辑处理界面
            url='/wx/process/label/scan/'+company_id+'/?label_code='+label_code+"&is_new=0&openid="+r[0]+"&consumer_id="+uid
            cur.close()
            request.session['wx_consumer_id']=int(uid)
            request.session['wx_openid']=r[0]

            response = HttpResponseRedirect(url)  
            parent_id = request.GET.get('parent_id','')
            if str(company_id) == "20445":
                if not parent_id:
                    parent_id = ''
                response.set_cookie('parent_id', parent_id, expires=datetime.datetime.now()+datetime.timedelta(seconds=30))  
                response.set_cookie('label_code', label_code, expires=datetime.datetime.now()+datetime.timedelta(seconds=30))
            
            if company_id =="19318":
                winner_id = request.GET.get('winner_id','')
                if winner_id:
                    response.set_cookie('winner_id', winner_id, expires=datetime.datetime.now()+datetime.timedelta(seconds=30))   
            
            return response
    cur.execute("select tjfwbase_oauth from company_companycfg where company_id="+company_id)
    r=cur.fetchone()
    if r[0]:
        url='/wx/tjfw/base/oauth/'+company_id+'/'
    else:
        url='/wx/oauth/'+company_id+'/'
    url=url+'?label_code='+label_code    
    cur.close()

    response = HttpResponseRedirect(url)  
    parent_id = request.GET.get('parent_id','')
    if str(company_id) == "20445":
        if not parent_id:
            parent_id = ''
        response.set_cookie('parent_id', parent_id, expires=datetime.datetime.now()+datetime.timedelta(seconds=30))  
        response.set_cookie('label_code', label_code, expires=datetime.datetime.now()+datetime.timedelta(seconds=30))

    if company_id =="19318":
        winner_id = request.GET.get('winner_id','')
        if winner_id:
            response.set_cookie('winner_id', winner_id, expires=datetime.datetime.now()+datetime.timedelta(seconds=30))   

    return response



#微信公众号
#连接微信服务器，准备换取CODE
def wx_tjfw_base_oauth(request,company_id):
    label_code=request.GET.get('label_code','')
    prefer=request.GET.get('prefer','')
    state={}
    if label_code:
        state['c']=label_code    
    if prefer:
        state['m']=prefer    
    state=binascii.hexlify(json.dumps(state).encode('utf8')).decode('utf-8')


    url=WX_TJ['oauth_direct_url_prefix']+"https://"+request.get_host()+'/wx/tjfw/base/oauth/info/'+company_id+'/?wx_scope=snsapi_base'    
    scope='snsapi_base'
    
    #2020-08-18 营销活动设置了天鉴微信公众号授权
    request.session.setdefault('wx_auth_account_id',0)
    if request.session['wx_auth_account_id']==6:
        scope='snsapi_userinfo'


    url=WX['url_oauth'].format(
        state=state,
        response_type='code',
        scope=scope,
        appid=WX_TJ['app_id'],
        redirect_url=url
    )    
    return HttpResponseRedirect(url)



#与微信服务器连接中，用CODE交换OPENID等相关信息
def wx_tjfw_base_oauth_info(request,company_id):
    data = {
        'consumer_id': '0',
        'tjfw_acct_id': '0',
        'wx_official_id': '0',
        'label_code': '',
        'nickname': '',
        'openid': '',
        'headimgurl': '',
        'province': '',
        'city': '',
        'sex': '',  # 性别 0：未知、1：男、2：女
        'unionid': '',
        'language': '',
        'is_new': '0',
        'e': ''
    }   
    code=request.GET.get("code",'')
    scope=request.GET.get('wx_scope','')    
    state=binascii.unhexlify(request.GET.get('state')).decode('utf8')      
    if not code:
        data['e']='code not found'
        return JsonResponse(data,safe=False)     


    r=requests.get(WX['url_oauth_access_token'].format(WX_TJ['app_id'],WX_TJ['app_secret'],code))   
    r=json.loads(r.content.decode("utf-8"))  

    if not 'access_token' in r:
        return  HttpResponse("")
    
    access_token=r['access_token']    
    openid=r['openid']    
    if not access_token or not openid:
        data['e']='access_token or openid not found'
        return JsonResponse(data,safe=False) 
    cur=connection.cursor()
    cur.execute("select id from sales_tjfwaccount where wx_openid='"+openid+"'")
    r=cur.fetchone()
    if r:
        tjfw_acct_id=str(r[0])
    else:
        cur.execute("insert into sales_tjfwaccount(wx_openid) values ('"+openid+"') returning id")
        r=cur.fetchone()
        tjfw_acct_id=str(r[0])

    

    #2020-08-18 营销活动设置了使用天鉴微信公众号授权,
    if request.session['wx_auth_account_id']==6:
        user_url = "https://api.weixin.qq.com/sns/userinfo?access_token={0}&openid={1}&lang=zh-CN"
        d = requests.get(user_url.format(access_token, openid))
        d = d.content.decode("utf-8")
        if not d:
            data['e'] = 'get wechat user info failed'
            return JsonResponse(data, safe=False)
        d = json.loads(d)
        if 'errcode' in d:
            data['e'] = 'get user info err,code:' + d['errcode']
            return JsonResponse(data, safe=False) 

        data['nickname'] = d['nickname']
        data['sex'] = str(d['sex'])
        data['headimgurl'] = d['headimgurl']
        data['province'] = d['province']
        data['openid']=d['openid']
        data['city'] = d['city']
        data['language'] = d['language']
        s = "select id from sales_consumeraccount where wx_openid='" + data['openid'] +\
            "' and  wx_account_id=" + str(request.session['wx_auth_account_id'])
        cur.execute(s)
        r = cur.fetchone()
        if r:
            data['consumer_id'] = str(r[0])
        else:
            s = "insert into sales_consumeraccount(" + \
                "wx_openid,wx_nickname,wx_headimgurl,wx_province,gender," + \
                "wx_city,wx_language,wx_account_id,tjfwacct_id" + \
                ") values ('" + \
                data['openid'] + "','" + data['nickname'] + "','" + data['headimgurl'] + "','" + data[
                    'province'] + "'," + data['sex'] + ",'" + \
                data['city'] + "','" + data['language'] + "',6," + tjfw_acct_id + \
                ") returning id"
            cur.execute(s)
            r = cur.fetchone()
            data['consumer_id'] = str(r[0])        

        request.session['wx_consumer_id']=int(data['consumer_id'])
        request.session['wx_openid']=openid
        #直接跳转到通用扫码业务逻辑处理界面
        url='/wx/process/label/scan/'+company_id+'/?label_code='+request.session['wx_label_code']           
    else:          
        url='/wx/oauth/'+company_id+'/?tjfw_acct_id='+tjfw_acct_id    
        state=json.loads(state)
        if 'c' in state:
            url=url+"&label_code="+state['c']
        if 'm' in state:
            url=url+"&prefer="+str(state['m'])  
    cur.close()      
    response = HttpResponseRedirect(url) 
    if company_id == "17095":
        response.set_cookie(WX_CK_UID+'_'+company_id+"_0",encrypt_p(str(data['consumer_id'])), expires=datetime.datetime.now()+datetime.timedelta(days=7))
    return response


#2020-06-23
#微信公众号
#连接微信服务器，准备换取CODE
def wx_oauth(request,company_id):        
    scope=request.GET.get('wx_scope','snsapi_userinfo') #snsapi_base snsapi_userinfo
    label_code=request.GET.get('label_code','')
    tjfw_acct_id=request.GET.get('tjfw_acct_id','')
    prefer=request.GET.get('prefer','')

   
    a={}
    if label_code:
        a['c']=label_code    
    if tjfw_acct_id:
        a['i']=tjfw_acct_id        
    cur=connection.cursor()    
    

    #2020-11-02
    if label_code:
        s="select wx_appid,wx_appsecret,id from sales_wxofficialaccounts where id="+str(request.session['wx_auth_account_id'])  
        cur.execute(s)
        r=cur.fetchone()
        if r:
            appid=r[0]
        else:
            #2020-09-02 婷曼逸 如果消费者从婷曼逸公众号进入设置不了session,按company_id取wx_appid       
            s="select wx_appid,wx_appsecret,id from sales_wxofficialaccounts where id="+\
                "(select case when exists(select id from company_companycfg where company_id="+str(company_id)+" and tjfw_oauth=1)"+\
                    " then 6 else (select id from  sales_wxofficialaccounts where company_id="+str(company_id)+") end)"                  
            cur.execute(s)
            r=cur.fetchone()
            appid=r[0]
    else:
        s="select wx_appid,wx_appsecret,id from sales_wxofficialaccounts where company_id="+company_id  
        cur.execute(s)
        r=cur.fetchone()
        if r:
            appid=r[0]
        

    #2020-11-11
    #try:
    #        f=open('d:\\hzy\\wx_oauth.txt','a')
    #        f.write('\r\n'+datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')+'->'+ s)
    #        f.close()
    #except Exception:
    #        pass


    a['o']=str(r[2])
    cur.close()  
    if prefer:
        a['m']=prefer
    state=json.dumps(a)
    state=binascii.hexlify(state.encode('utf8')).decode('utf-8')
    url="https://"+request.get_host()+'/wx/oauth/info/'+company_id+'/?wx_scope='+scope    
    if appid == WX_JG['app_id']:
        url = WX_JG['oauth_direct_url_prefix'] + url
    url=WX['url_oauth'].format(
        state=state,
        response_type='code',
        scope=scope,
        appid=appid,
        redirect_url=url
    )

    return HttpResponseRedirect(url)

    
#微信公众号
#与微信服务器连接中，用CODE交换OPENID等相关信息
def wx_oauth_info(request,company_id,app_id='0'):
    data={
        'consumer_id':'0',   
        'tjfw_acct_id':'0',
        'wx_official_id':'0',
        'label_code':'',
        'nickname':'',
        'openid':'',
        'headimgurl':'',
        'province':'',
        'city':'',
        'sex':'',  #性别 0：未知、1：男、2：女
        'unionid':'',
        'language':'',            
        'is_new':'0',
        'e':''
    }        
    
    #跳转到扫码业务逻辑处理界面
    #url='/wx/process/label/scan/'+company_id+'/'    

    code=request.GET.get("code",'')
    scope=request.GET.get('wx_scope','')
    state=binascii.unhexlify(request.GET.get('state')).decode('utf8') 
    if not code:
        data['e']='code not found'
        return JsonResponse(data,safe=False)       

    a=json.loads(state)
    data['wx_official_id']=a['o']
    if 'c' in a:
        data['label_code']=a['c']
    if 'i' in a:
        data['tjfw_acct_id']=a['i']  
    cur=connection.cursor()
    cur.execute("select wx_appid,wx_appsecret,id from sales_wxofficialaccounts where company_id="+company_id)
    #今后可能会有天鉴公众号 代 客户授权
    r=cur.fetchone()
    appid=r[0]     
    secret=r[1]   
    wx_official_account_id=str(r[2])
    r=requests.get(WX['url_oauth_access_token'].format(appid,secret,code))   


    #2020-11-02
    #try:
    #    f=open('d:\\hzy\\wx_oauth_info.txt','a')
    #    f.write('\r\n'+datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')+'->company_id:'+company_id+',app_id:'+appid+","+r.content.decode("utf-8"))
    #    f.close()
    #except Exception:
    #    pass

    r=json.loads(r.content.decode("utf-8")) 
    
    

    access_token=r['access_token']    
    data['openid']=r['openid']
    refresh_token=r['refresh_token']
    if not access_token or not data['openid']:
        data['e']='access_token or openid not found'
        return JsonResponse(data,safe=False)         
    user_url="https://api.weixin.qq.com/sns/userinfo?access_token={0}&openid={1}&lang=zh-CN"
    d=requests.get(user_url.format(access_token,data['openid']))
    d=d.content.decode("utf-8")
    if not d:
        data['e']='get wechat user info failed'
        return JsonResponse(data,safe=False)    
    d=json.loads(d)
    if 'errcode' in d: 
        data['e']='get user info err,code:'+d['errcode']
        return JsonResponse(data,safe=False)    
    data['nickname']=d['nickname'].replace("'","")
    data['sex']=str(d['sex'])
    data['headimgurl']=d['headimgurl']
    data['province']=d['province'].replace("'","")
    data['city']=d['city'].replace("'","")
    data['language']=d['language']   
    s="select id from sales_consumeraccount where wx_openid='"+data['openid']+"' and  wx_account_id="+wx_official_account_id
    cur.execute(s)
    r=cur.fetchone()
    if r:
        data['consumer_id']=str(r[0])
    else:       
        if data['tjfw_acct_id']=='0':
            tj_id='null'
        else:
            tj_id=data['tjfw_acct_id']
        s="insert into sales_consumeraccount("+\
                "wx_openid,wx_nickname,wx_headimgurl,wx_province,gender,"+\
                "wx_city,wx_language,wx_account_id,tjfwacct_id"+\
            ") values ('"+\
                data['openid']+"','"+data['nickname']+"','"+data['headimgurl']+"','"+data['province']+"',"+data['sex']+",'"+\
                data['city']+"','"+data['language']+"',"+data['wx_official_id']+","+data['tjfw_acct_id']+\
            ") returning id"
        cur.execute(s)
        r=cur.fetchone()
        data['consumer_id']=str(r[0])      
        data['is_new']='1'
    cur.close()

    request.session['wx_consumer_id']=int(data['consumer_id'])
    request.session['wx_openid']=data['openid']    
    if 'm' in a:
        #参数有传入自定义页面
        url='/wx/customize/'+company_id+'/'+str(a['m'])+'/?consumer_id='+data['consumer_id']  
    else:
        #跳转到通用扫码业务逻辑处理界面
        url='/wx/process/label/scan/'+company_id+'/?label_code='+data['label_code']+\
            "&is_new="+data['is_new']+"&openid="+data['openid']+"&consumer_id="+data['consumer_id']  
    response=HttpResponseRedirect(url)
    #2020-12-24
    response.set_cookie(WX_CK_UID+'_'+company_id+"_"+app_id,encrypt_p(str(data['consumer_id'])), expires=datetime.datetime.now()+datetime.timedelta(days=7))
    return response


    

#微信小程序邀请码,默认小程序ID为天鉴物流终端    
def get_wxcode(request,app_id="1"):
    data={'e':'','img':'','data':{}}
    #邀请码宽度
    w=300   
    #logo 宽度
    l_w=int(300/5)
    
    e=time_key_check(request,sK)
    if e:
        data['e']=e
        return JsonResponse(data)
    scene=request.GET.get('scene','')
    page=request.GET.get('page','')
    cid=request.GET.get('company_id','')
    uid=request.GET.get('user_id','')
    if not scene or not page or not cid or not uid:
        data['e']='scene,page,company_id,user_id missing'
        return JsonResponse(data)
    
    us=User.objects.filter(id=int(uid))    
    cs=Company.objects.filter(id=int(cid))
    if not cs or not us:
        data['e']='company_id or user_id not exists'
        return JsonResponse(data)
    c=cs[0]
    u=us[0]
    aid=str(c.ancestor_id)
    cur=connection.cursor()      
    cur.execute("SELECT appid, appsecret FROM wx_wxapp where id="+app_id)
    r=cur.fetchone()
    tk=rd.get('wx_accesstoken_app_'+str(app_id))
    if not tk:                        
        res = requests.get(WX['url_access_token_fmt'].format(r[0],r[1]))
        r=json.loads(res.text)            
        if 'errcode' in r:
            data['e']=r['errmsg']
            return JsonResponse(data) 
        else:
            rd.set('wx_accesstoken_app_'+str(app_id),r['access_token'],7000)
            tk = r['access_token']               
    url='https://api.weixin.qq.com/wxa/getwxacodeunlimit?access_token='+tk
    header={'Content-Type': 'application/json'}    
    res=requests.post(url,headers=header,data=json.dumps({"scene":scene,"page":page,'width':w})) 

    if res.status_code==502 or res.status_code==500:
        data['e']='internal server err'
        return JsonResponse(data)
    if 'json' in res.headers.get('content-type'):
        data['e']=res.json().get('errmsg')
        return JsonResponse(data)       
    ancestor=Company.objects.get(id=int(aid))
    p=media_path+'/'+ancestor.name+'/register/wxcode'
    if not os.path.exists(p):
        os.makedirs(p)
    r_name=ancestor.name+'/register/wxcode/'+str(c.id)+"_"+str(uuid.uuid1())+'.jpg'
    img=Image.open(io.BytesIO(res.content))
    #底部添加LOGO和文字    
    target = Image.new('RGB', (w, w + l_w), '#ffffff')
    target.paste(img, (0, 0))
    f_logo=static_path+'static\\images\\logo.png'
    if ancestor.img_logo:
        f_logo=media_path+'\\'+str(ancestor.img_logo)    
    img_logo=Image.open(f_logo)    
    img_logo=img_logo.resize((l_w,l_w),Image.ANTIALIAS)
    #有透明通道的，合并
    if len(img_logo.split()) == 4:        
        r, g, b, a = img_logo.split()
        img_logo = Image.merge("RGB", (r, g, b))
        target.paste(img_logo,(0,w,l_w,w+l_w),mask=a)        
    else:    
        target.paste(img_logo,(0,w,l_w,w+l_w))
    s=c.name
    font_size=int((w-l_w)/len(s))
    #字体最大26，否则与LOGO不协调
    font_size=min(font_size,26)
    font = ImageFont.truetype('C:\\Windows\\Fonts\\微软雅黑\\msyh.ttc', font_size) 
    draw = ImageDraw.Draw(target)
    draw.text((l_w, w+int((l_w-font_size)/2.5)), s, fill=(0, 0, 0), font=font)
    ImageDraw.Draw(target)
    target.save(media_path+'/'+r_name)
    data['img']='/media/'+r_name
    data['data']={
        'company_id':c.id,
        'company_name':c.name,
        'user_id':u.id,
        'user_name':u.username
    }
    return JsonResponse(data)


@csrf_exempt    
def salesman_writeoff(request):
    data={'e':'','pkg_id':0,'extra':'','code':''}   
    e=time_key_check(request,sK)
    if e:
        data['e']=e
        return JsonResponse(data)
    #POST,json
    r = json.loads(request.body)
    if 'user_id' not in r or 'label_code' not in r:
        data['e']='user_id,label_code missing'
        return JsonResponse(data)
    u_id=str(r['user_id'])
    code=r['label_code']
    cur=connection.cursor()
    try:
        cur.execute("SELECT id,package_id,coalesce(parent_id,id) FROM label.t"+code[:4]+" where code='"+code+"'")
        r=cur.fetchone()
    except:
        data['e']=_('标签号码错误')
        return JsonResponse(data)

    if not r:
        data['e']=_('标签号码错误')
        return JsonResponse(data)
    label_id=str(r[0])
    label_pk_id=str(r[2])
    pkg_id=str(r[1])
    data['pkg_id']=r[1]
    #是否已经核销
    #1:瓶 2:盒 3:箱
    e=''
    if label_pk_id=='3':
        cur.execute("select to_char(tm,'yyyy-mm-dd HH24:MI:SS') from inventory_recipientwriteoff where label_pk_id="+label_pk_id)
        r=cur.fetchone()
        if r:
            e='此箱已于'+r[0]+'核销'
    else:
        cur.execute("select to_char(tm,'yyyy-mm-dd HH24:MI:SS') from inventory_recipientwriteoff where label_id="+label_id)
        r=cur.fetchone()
        if r:
            e='此瓶已于'+r[0]+'核销'
    if e:
        data['e']=e
        return JsonResponse(data)

    #是否曾经领用
    #箱标 一箱可能贴2枚箱标
    if label_pk_id=='3':
        s="select a.batch_id,a.id,b.company_id,b.ancestor_id,to_char(b.tm_writeoff,'yyyy-mm-dd HH24:MI:SS'),b.code from "+\
        "(select batch_id,id from inventory_recipientdetails where receiver_id="+u_id+" and label_pk_id="+label_pk_id+") a "+\
        " left outer join "+\
        "(select id,company_id,ancestor_id,tm_writeoff,code from inventory_recipientsbatch) b "+\
        "on a.batch_id=b.id"   
    #瓶标
    else:
        s="select a.batch_id,a.id,b.company_id,b.ancestor_id,to_char(b.tm_writeoff,'yyyy-mm-dd HH24:MI:SS'),b.code from "+\
        "(select batch_id,id from inventory_recipientdetails where receiver_id="+u_id+" and label_id="+label_id+") a "+\
        " left outer join "+\
        "(select id,company_id,ancestor_id,tm_writeoff,code from inventory_recipientsbatch) b "+\
        "on a.batch_id=b.id"    
    cur.execute(s)
    r=cur.fetchone()
    if not r:
        data['e']='只能核销自己领用过的记录'
    else:
        cd=r[5]
        if r[4]:
            data['e']='领用单'+cd+'已于 '+r[4]+' 完成核销'
            return JsonResponse(data)
        b_id=str(r[0])
        s="INSERT INTO inventory_recipientwriteoff(label_code,label_pkg,label_id,label_pk_id,"+\
            "batch_id,detail_id,company_id,ancestor_id,user_id) values ("+\
            "'"+code+"',"+pkg_id+","+label_id+","+label_pk_id+","+\
            str(r[0])+","+str(r[1])+","+str(r[2])+","+str(r[3])+","+u_id+")"
        cur.execute(s)   
        #检查领用记录，核销完成的，加上核销时间    
        a=[]
        b=[]       
        #领用       
        #箱标            
        cur.execute("select label_pk_id from inventory_recipientdetails where batch_id="+b_id+" and label_pkg=3")            
        for r1 in cur.fetchall():
            if r1[0] not in a:
                a.append(r1[0])
        #瓶标           
        cur.execute("select label_id    from inventory_recipientdetails where batch_id="+b_id+" and label_pkg=1")            
        for r1 in cur.fetchall():
            if r1[0] not in a:
                a.append(r1[0])
        #核销        
        #箱标            
        cur.execute("select label_pk_id from inventory_recipientwriteoff where batch_id="+b_id+" and label_pkg=3")            
        for r1 in cur.fetchall():
            if r1[0] not in b:
                b.append(r1[0])
        #瓶标            
        cur.execute("select label_id    from inventory_recipientwriteoff where batch_id="+b_id+" and label_pkg=1")            
        for r1 in cur.fetchall():
            if r1[0] not in b:
                b.append(r1[0])
        a.sort()
        b.sort()
        if operator.eq(a, b):
            cur.execute("update inventory_recipientsbatch set tm_writeoff=now() where id="+b_id)
            data['extra']="领用单"+cd+"完成核销"    
    return JsonResponse(data) 



@csrf_exempt   
def salesman_recipient(request):
    data={'e':'','cnt':0}   
    e=time_key_check(request,sK)
    if e:
        data['e']=e
        return JsonResponse(data)    
    #POST,json
    r = json.loads(request.body)
    if 'code' not in r or 'user_id' not in r or 'labels' not in r or 'receiver_id' not in r or 'reason' not in r:
        data['e']='user_id,receiver_id,code,data missing'
        return JsonResponse(data)
    reason="'"+r['reason']+"'"
    u_id=str(r['user_id'])
    r_id=str(r['receiver_id'])
    code=r['code']    
    codes=r['labels']
    cur=connection.cursor()    
    cur.execute("select company_id,ancestor_id from company_user where id="+u_id)    
    r=cur.fetchone()
    if not r:
        data['e']='wrong user id'
        return JsonResponse(data)
    if len(codes)==0:
        data['e']='标签号码不能为空'
        return JsonResponse(data)
    a_id=str(r[1])   
    c_id=str(r[0])       
    #同一公司领用单号不能重复
    cur.execute("select to_char(tm,'yyyy-mm-dd HH24:MI:SS') from inventory_recipientsbatch where company_id="+c_id+" and code='"+code+"'")
    r=cur.fetchone()
    if r:
        data['e']="领用号与 "+r[0]+" 提交的单号重复"
        return JsonResponse(data)
    s="INSERT INTO inventory_recipientsbatch(code,reason,company_id,receiver_id,user_id,ancestor_id)"+\
        " values ('"+code+"',"+reason+","+c_id+","+r_id+","+u_id+","+a_id+") returning id"
    cur.execute(s)
    r=cur.fetchone()
    id=str(r[0])
    a=[]
    cur.execute("create temp table codes(label_code character varying(20),tb character varying(20),"+\
        "code character varying(20),label_id bigint,label_pk_id bigint,label_pkg smallint,err character varying(200))")
    for code in codes: 
        a.append("('" + code + "','" + code[:4] + "')")
    sSql="insert into codes (label_code,tb) values " + ",".join(a)        
    cur.execute(sSql)
    #处理标签替换
    s="update codes set code="+\
        "coalesce((select code from label.lbl_replace_details where code0=label_code),label_code)"
    cur.execute(s)   
    cur.execute("select tb from codes group by tb")
    rs = cur.fetchall()
    for r in rs:
        cur.execute("select to_regclass('label.t" + r[0] + "')")
        r0 = cur.fetchone()
        if r0[0]:
            s="update codes set "+\
                "label_id=b.id,"+\
                "label_pkg=b.package_id,"+\
                "label_pk_id=coalesce(b.parent_id,b.id),"+\
                "err=case when b.id is null then '标签号码错误' else null end "+\
                "from "+\
                "(SELECT label_code,code from codes where tb='"+r[0]+"') a "+\
                "left outer join "+\
                "(select id,code,parent_id,package_id,is_pk from label.t"+r[0]+") b "+\
                "on a.label_code=b.code "+\
                "where tb='"+r[0]+"' and codes.code=a.code"
            cur.execute(s)  
    s="INSERT INTO inventory_recipientdetails(label_code,label_pkg,label_id,label_pk_id,batch_id,company_id,ancestor_id,receiver_id,product_id,err) "+\
        "select label_code,label_pkg,label_id,label_pk_id,"+id+","+c_id+","+a_id+","+r_id+","+\
        "(select product_id from inventory_stkdetails where ancestor_id="+a_id+" and label_pk_id=a.label_pk_id limit 1),err from codes  a "+\
        " where (select id from inventory_recipientdetails where label_code=a.label_code and company_id="+c_id+") is null"
    cur.execute(s)    
    cur.execute("select count(*) from inventory_recipientdetails where batch_id="+id+" and err is null")
    r=cur.fetchone()
    data['cnt']=r[0]
    return JsonResponse(data)


@csrf_exempt   
def agent_order(request):
    data={'e':'','cnt':0}   
    e=time_key_check(request,sK)
    if e:
        data['e']=e
        return JsonResponse(data)    
    #POST,json
    r = json.loads(request.body)
    if 'code' not in r or 'user_id' not in r or 'data' not in r:
        data['e']='user_id,code,data missing'
        return JsonResponse(data)
    details=r['data']
    code=r['code']
    user_id=str(r['user_id'])
    dt='null'
    remark='null'
    if 'date' in r:
        if r['date']!='': 
            dt="'"+r['date']+"'"
    if 'remark' in r:
        remark="'"+r['remark']+"'"
    cur=connection.cursor()    
    cur.execute("select company_id,ancestor_id,(select parent_id from company_company where id=company_id)"+\
        " from company_user where id="+user_id)    
    r=cur.fetchone()
    if not r:
        data['e']='wrong user id'
        return JsonResponse(data)
    a_id=str(r[1])
    v_id = str(r[2])
    review_company_id = str(r[2])
    c_id=str(r[0])
    #茗酒汇直接向总司下单
    if a_id == "19257":
        v_id = a_id
    #同一经销商订单号不能重复
    cur.execute("select to_char(tm,'yyyy-mm-dd HH24:MI:SS') from company_agentorderbatch where company_id="+c_id+" and code='"+str(code)+"'")
    r=cur.fetchone()
    if r:
        data['e']="订单号与 "+r[0]+" 提交的订单重复"
        return JsonResponse(data)

    s="INSERT INTO company_agentorderbatch("+\
        "company_id,ancestor_id,vendor_id,user_id,code,dt_expect,remark,review_company_id,voucher_id) values ("+\
        c_id+","+a_id+","+v_id+","+user_id+",'"+str(code)+"',"+dt+","+remark+","+review_company_id+",0) returning id"
    cur.execute(s)
    r=cur.fetchone()
    id=str(r[0])
    a=[]
    for d in details:
        a.append("("+str(d[0])+","+str(d[1])+","+id+","+a_id+","+v_id+","+c_id+","+str(d[2])+",0)")    
    s="insert into company_agentorderdetails(product_id,qty_box,batch_id,ancestor_id,vendor_id,company_id,qty_bottle,voucher_id)"+\
        " values "+",".join(a)    
    cur.execute(s)
    data['cnt']=cur.rowcount
    return JsonResponse(data)


#业务员帮助下单
@csrf_exempt
def agent_order_helper(request):
    data = {'e': '', 'cnt': 0}
    e = time_key_check(request, sK)
    if e:
        data['e'] = e
        return JsonResponse(data)
        # POST,json
    r = json.loads(request.body)
    if 'code' not in r or 'user_id' not in r or 'data' not in r or 'agent_id' not in r or 'voucher_id' not in r:
        data['e'] = 'user_id,code,data,agent_id,voucher_id missing'
        return JsonResponse(data)
    details = r['data']
    code = r['code']
    user_id = str(r['user_id'])
    agent_id = str(r['agent_id'])
    voucher_id = str(r['voucher_id'])
    dt = 'null'
    remark = 'null'
    data_change= []
    if 'date' in r:
        if r['date'] != '':
            dt = "'" + r['date'] + "'"
    if 'remark' in r:
        remark = "'" + r['remark'] + "'"
    if 'data_change' in r:
        data_change = r['data_change']
    cur = connection.cursor()
    cur.execute("select ancestor_id from company_user where id=" + user_id)
    r = cur.fetchone()
    if not r:
        data['e'] = 'wrong user id'
        return JsonResponse(data)
    a_id = str(r[0])
    v_id = str(r[0])
    review_company_id = str(r[0])   

    # 同一经销商订单号不能重复
    cur.execute(
        "select to_char(tm,'yyyy-mm-dd HH24:MI:SS') from company_agentorderbatch where company_id=" + agent_id + " and code='" + str(
            code) + "'")
    r = cur.fetchone()
    if r:
        data['e'] = "订单号与 " + r[0] + " 提交的订单重复"
        return JsonResponse(data)

    s="INSERT INTO company_agentorderbatch("+\
        "company_id,ancestor_id,vendor_id,user_id,code,dt_expect,remark,review_company_id,voucher_id,helper_id) values ("+\
        agent_id+","+a_id+","+v_id+","+user_id+",'"+str(code)+"',"+dt+","+remark+","+review_company_id+","+voucher_id+","+user_id+") returning id"
    
    cur.execute(s)
    r = cur.fetchone()
    id = str(r[0])
    a = []
    for d in details:
        a.append(
            "(" + str(d[0]) + "," + str(d[1]) + "," + id + "," + a_id + "," + v_id + "," + agent_id + "," + str(d[2]) + ",0)")
    
    for d in data_change:
        a.append("(" + str(d[0]) + "," + str(d[1]) + "," + id + "," + a_id + "," + v_id + "," + agent_id + "," + str(d[2]) + ",9)")

    s = "insert into company_agentorderdetails(product_id,qty_box,batch_id,ancestor_id,vendor_id,company_id,qty_bottle,voucher_id)" + \
        " values " + ",".join(a)
    cur.execute(s)
    data['cnt'] = cur.rowcount
    return JsonResponse(data)



def get_salesman(request,company_id):
    data={'e':'','data':[]}   
    e=time_key_check(request,sK)
    if e:
        data['e']=e
        return JsonResponse(data)
    cur=connection.cursor() 
    #7;"内勤管理组"    #8;"业务员"
    s="select b.id,b.username from "+\
        "(SELECT id,user_id FROM company_user_groups where group_id=8) a "+\
        "left outer join "+\
        "(select id,username,company_id from company_user) b "+\
        "on a.user_id=b.id "+\
        "where b.company_id="+company_id
    cur.execute(s)
    for r in cur.fetchall():
        data['data'].append({'id': r[0], 'name':r[1]})
    return JsonResponse(data)


def get_my_info(request,what):
    data={'e':'','data':{}}   
    e=time_key_check(request,sK)
    if e:
        data['e']=e
        return JsonResponse(data)
    u_id=request.GET.get('user_id','')
    role=request.GET.get('role','')
    if not u_id:
        data['e']='user_id missing'
        return JsonResponse(data)    
    cur=connection.cursor() 
    if what=='order':       
        if not role:
            data['e']='role missing:taker/maker'
            return JsonResponse(data)
        cur.execute("select ancestor_id from company_user where id = "+u_id)
        rrr = cur.fetchone()
        if not rrr:
            data['e'] = 'missing user'
            return JsonResponse(data)
        ancestor_id = str(rrr[0])
        #下单人
        if role=='maker':
            sAnd=" and user_id="+u_id
        #接单人
        elif role=='taker':
            sAnd=" and vendor_id=(select company_id from company_user where id="+u_id+")"
        elif role == 'company':
            sAnd = " and company_id=(select company_id from company_user where id=" + u_id + ")"
        uncomplete=[]        
        #2020-11-05 内参要求订单可箱可盒         
        s="select id,code,to_char(tm,'yyyy-mm-dd HH24:MI'),"+\
            "coalesce(to_char(dt_expect,'yyyy-mm-dd'),''),"+\
            "(select name from company_company where id=company_id),"+\
            "(select username from company_user where id=user_id), "+\
            "(select username from company_user where id=helper_id), " + \
            "(select name from company_company where id=review_company_id), " + \
            "(SELECT tm from inventory_batch where code = batch_code and vtype_id = 2 LIMIT 1), " + \
            "(SELECT tm from company_agentorderreview WHERE batch_id = a.id and company_id = "+ancestor_id+" LIMIT 1), " + \
            "voucher_id "+\
            "from company_agentorderbatch a where tm_complete is null"+sAnd    
        cur.execute(s)
        rs=cur.fetchall()
        for r in rs:
            bid=r[0]
            ps=[]
            cur.execute("select product_id,coalesce(qty_box,0),coalesce(qty_bottle,0),"+\
                "(select name from product_product where id=product_id),voucher_id "+\
                "from company_agentorderdetails where batch_id="+str(bid))
            for rr in cur.fetchall():
                ps.append({'id':rr[0],'name':rr[3],'qty_box':rr[1],'qty_bottle':rr[2],'voucher_id':rr[4]})
            fh_tm = ''
            if r[8]:
                fh_tm =r[8].strftime("%Y-%m-%d %H:%M:%S")
            review_tm = ''
            if r[9]:
                review_tm =r[9].strftime("%Y-%m-%d %H:%M:%S")
            voucher_id = r[10]
            voucher_type = ''
            if voucher_id ==0:
                voucher_type = '销售订单'
            elif voucher_id == 4:
                voucher_type = '退货订单'
            else:
                voucher_type = '换货订单'
            uncomplete.append({
                'id':bid,
                'order_code':r[1],
                'order_time':r[2],
                'date_expect':r[3],                
                'company_name':r[4],
                'user_name':r[5],
                'helper_name':r[6],
                'review_company':r[7],
                'products':ps,
                'fh_tm':fh_tm,
                'review_tm':review_tm,
                'voucher_id':voucher_id,
                'voucher_type':voucher_type
            })
        complete=[]        
        s="select id,code,to_char(tm,'yyyy-mm-dd HH24:MI'),"+\
            "coalesce(to_char(dt_expect,'yyyy-mm-dd HH24:MI'),''),"+\
            "(select name from company_company where id=company_id),"+\
            "(select username from company_user where id=user_id), "+\
            "(select username from company_user where id=helper_id), " + \
            "(select name from company_company where id=review_company_id), " + \
            "(SELECT tm from inventory_batch where code = batch_code and vtype_id = 2 LIMIT 1), " + \
            "(SELECT tm from company_agentorderreview WHERE batch_id = a.id and company_id = "+ancestor_id+" LIMIT 1), " + \
            "voucher_id "+\
            "from company_agentorderbatch a where tm_complete is not null"+sAnd 
        cur.execute(s)
        rs=cur.fetchall()
        for r in rs:
            bid=r[0]
            ps=[]
            cur.execute("select product_id,coalesce(qty_box,0),coalesce(qty_bottle,0),"+\
                "(select name from product_product where id=product_id),voucher_id "+\
                "from company_agentorderdetails where batch_id="+str(bid))
            for rr in cur.fetchall():
                ps.append({'id':rr[0],'name':rr[3],'qty_box':rr[1],'qty_bottle':rr[2],'voucher_id':rr[4]})
            fh_tm = ''
            if r[8]:
                fh_tm =r[8].strftime("%Y-%m-%d %H:%M:%S")
            review_tm = ''
            if r[9]:
                review_tm =r[9].strftime("%Y-%m-%d %H:%M:%S")
            voucher_id = r[10]
            voucher_type = ''
            if voucher_id ==0:
                voucher_type = '销售订单'
            elif voucher_id == 4:
                voucher_type = '退货订单'
            else:
                voucher_type = '换货订单'
            complete.append({
                'id':bid,
                'order_code':r[1],
                'order_time':r[2],
                'date_expect':r[3],                
                'company_name':r[4],
                'user_name':r[5],
                'helper_name':r[6],
                'review_company':r[7],
                'products':ps,
                'fh_tm':fh_tm,
                'review_tm':review_tm,
                'voucher_id':voucher_id,
                'voucher_type':voucher_type
            })
        return JsonResponse({'e':data['e'],'closed':complete,'unclosed':uncomplete})
    elif what=='writeoff':
        closed=[]
        unclosed=[]
        #已核销
        s="select code,to_char(tm,'yyyy-mm-dd HH24:MI'),to_char(tm_writeoff,'yyyy-mm-dd HH24:MI'),"+\
            "coalesce((select count(*) from inventory_recipientdetails where batch_id=a.id and label_pkg=3),0),"+\
            "coalesce((select count(*) from inventory_recipientdetails where batch_id=a.id and label_pkg=1),0),"+\
            "id "+\
            "from inventory_recipientsbatch a where receiver_id="+u_id+" and tm_writeoff is not null"
        cur.execute(s)
        for r in cur.fetchall():
            closed.append({
                'id':r[5],
                'code':r[0],
                'time':r[1],
                'time_writeoff':r[2],
                'boxes':r[3],
                'bottles':r[4]
            })
        #待核销  
        s="select a.code,a.tm,a.box,a.bottle,coalesce(b.box,0),coalesce(c.bottle,0) from "+\
            "(select id,code,to_char(tm,'yyyy-mm-dd HH24:MI') tm,"+\
                "(select count(*) box from inventory_recipientdetails where batch_id=a.id and label_pkg=3),"+\
                "(select count(*) bottle from inventory_recipientdetails where batch_id=a.id and label_pkg=1) "+\
                "from inventory_recipientsbatch a where receiver_id="+u_id+" and tm_writeoff is null) a "+\
            "left outer join "+\
            "(select batch_id,count(*) box from inventory_recipientwriteoff where label_pkg=3 group by batch_id) b "+\
            "on a.id=b.batch_id "+\
            "left outer join "+\
            "(select batch_id,count(*) bottle from inventory_recipientwriteoff where label_pkg=1 group by batch_id) c "+\
            "on a.id=c.batch_id order by a.tm" 
        cur.execute(s)               
        for r in cur.fetchall() :
            unclosed.append({
                'recipient':{'code':r[0],'time':r[1],'boxes':r[2],'bottles':r[3]},
                'writeoff':{'boxes':r[4],'bottles':r[5]}
            })
        data['closed']=closed
        data['unclosed']=unclosed
    elif what=='company':        
        s="select a.company_id,a.ancestor_id,b.name,b.lv,a.username,"+\
            "(select perm_inbound_lv1 from company_companycfg where company_id=a.ancestor_id) "+\
            "from "+\
            "(select company_id, ancestor_id,username from company_user where  id="+u_id+") a "\
            "left outer join "+\
            "(select id,name,(select sn from company_level  where id=level_id) lv from company_company) b "+\
            "on a.company_id=b.id"
        cur.execute(s)
        r=cur.fetchone()
        d={'e':''}
        if r:
            d['company_id']=r[0]
            d['ancestor_id']=r[1]
            d['company_name']=r[2]
            d['company_level']=r[3]
            d['user_name']=r[4] 
            d['show_inbound']=r[5]            
            s="select group_id,"+\
                "(select name from auth_group where id=group_id) "+\
                "from company_user_groups where user_id="+u_id
            cur.execute(s)
            g=[]
            for r in cur.fetchall():
                g.append({'group_id':r[0],'group_name':r[1]})
            d['groups']=g
            d['staff_division']={'saldivisions':[],'subdivisions':[],'agents':[]}            
            cur.execute("select saldiv_ids,subsaldiv_ids,agent_ids from company_staffsaldiv where user_id="+u_id)
            r=cur.fetchone()
            if r:
                if r[0]:
                    d['staff_division']['saldivisions']=r[0]
                if r[1]:
                    d['staff_division']['subdivisions']=r[1]
                if r[2]:
                    d['staff_division']['agents']=r[2]   
        else:
            data['e']='user_id error'
        cur.close()
        return JsonResponse(d)
    elif what=='storegeo':
        s="select id,lng_lat,tm_checked from inventory_storehousebd09 "+\
            "where company_id=(select company_id from company_user where id="+u_id+")"
        d=[]
        cur.execute(s)
        for r in cur.fetchall():          
            tm=''
            if r[2]:
                tm=r[2].strftime('%Y-%m-%d %H:%M:%S')
            d.append({'id':r[0],'lng':r[1][0],'lat':r[1][1],'time_checked':tm})        
        #2020-10-15
        data['data']['storegeos']=d
        s="SELECT coalesce(min(distance),99999999.99) FROM sales_channelpromtionsrebate where level_id="+\
            "(select level_id from company_company where id=(select company_id from company_user where id="+u_id+")) "+\
            "and promotion_id in (select id from sales_channelpromotions where status=1 and (now() between tm_start and tm_end) "+\
                "and company_id=(select ancestor_id from company_company where id=(select company_id from company_user where id="+u_id+"))"+\
            ")"        
        cur.execute(s)
        r=cur.fetchone()
        data['data']['distance_lmt']=r[0]
    #2020-09-21
    elif what=='rebate':        
        t=request.GET.get('type','summary')
        d=[]
        if t=='summary':
            s="select sum(val) from sales_channelrebatebatch where confirmed=true "+\
                "and company_id=(select company_id from company_user where id="+u_id+")"           
            cur.execute(s)
            r=cur.fetchone()
            d={'amount':r[0]}
        else:           
            s="select a.val,a.tm,"+\
                "(select name from sales_channelpromotions where id=a.promotion_id),"+\
                "case b.vtype_id when 1 then (select name from company_company where id=b.company_id)||' 扫码入库' "+\
                    "when 2 then (select name from company_company where id=b.company_to_id)||' 扫码出库' end,"+\
                "(select count(*) from inventory_stkdetails where batch_id=a.batch_id and label_pkg=3),"+\
                "(select count(*) from inventory_stkdetails where batch_id=a.batch_id and label_pkg=1),"+\
                "case when a.company_id=a.company_src_id then (select name from company_company where "+\
                    "id=a.company_id) else (select name from company_company where id=b.company_to_id) end "+\
                "from "+\
                "(select batch_id,company_id,promotion_id,company_src_id,val,tm,id "+\
                    "from sales_channelrebatebatch where confirmed=true "+\
                    "and company_id=(select company_id from company_user where id="+u_id+")"+\
                ") a "+\
                "left outer join "+\
                "(select id,vtype_id,company_to_id,company_id from inventory_batch) b "+\
                "on a.batch_id=b.id"  
            cur.execute(s)
            for r in cur.fetchall():
                q=''
                if r[4]:
                    q=str(r[4])+'箱'
                if r[5]:
                    q=q+str(r[5])+'瓶'
                d.append({                    
                    'amount':r[0],
                    'time':r[1].strftime('%Y-%m-%d %H:%M:%S'),      
                    'promotion':r[2],
                    'src':r[3],
                    'qty':q                    
                })
        data['data']=d
    # 2020-01-12 查询订单审核记录
    elif what == 'review':
        if not role:
            data['e'] = 'role missing:taker/maker'
            return JsonResponse(data)
        # 下单人
        if role == 'maker':
            sAnd = " user_id=" + u_id
        # 接单人
        elif role == 'taker':
            sAnd = " review_company_id=(select company_id from company_user where id=" + u_id + ")"
        uncomplete = []
        # 2020-11-05 内参要求订单可箱可盒
        s = "select id,code,to_char(tm,'yyyy-mm-dd HH24:MI')," + \
            "coalesce(to_char(dt_expect,'yyyy-mm-dd'),'')," + \
            "(select name from company_company where id=company_id)," + \
            "(select username from company_user where id=user_id) " + \
            "from company_agentorderbatch a where " + sAnd
        cur.execute(s)
        rs = cur.fetchall()
        for r in rs:
            bid = r[0]
            ps = []
            cur.execute("select product_id,coalesce(qty_box,0),coalesce(qty_bottle,0)," +
                        "(select name from product_product where id=product_id) " +
                        "from company_agentorderdetails where batch_id=" + str(bid))
            for rr in cur.fetchall():
                ps.append({'id': rr[0], 'name': rr[3],
                           'qty_box': rr[1], 'qty_bottle': rr[2]})
            uncomplete.append({
                'id': bid,
                'order_code': r[1],
                'order_time': r[2],
                'date_expect': r[3],
                'company_name': r[4],
                'user_name': r[5],
                'products': ps
            })
        complete = []
        s = "select id,code,to_char(tm,'yyyy-mm-dd HH24:MI')," + \
            "coalesce(to_char(dt_expect,'yyyy-mm-dd HH24:MI'),'')," + \
            "(select name from company_company where id=company_id)," + \
            "(select username from company_user where id=user_id) " + \
            "from company_agentorderbatch a where id in (SELECT batch_id from company_agentorderreview WHERE user_id = "+u_id+")"
        cur.execute(s)
        rs = cur.fetchall()
        for r in rs:
            bid = r[0]
            ps = []
            cur.execute("select product_id,coalesce(qty_box,0),coalesce(qty_bottle,0)," +
                        "(select name from product_product where id=product_id) " +
                        "from company_agentorderdetails where batch_id=" + str(bid))
            for rr in cur.fetchall():
                ps.append({'id': rr[0], 'name': rr[3],
                           'qty_box': rr[1], 'qty_bottle': rr[2]})
            complete.append({
                'id': bid,
                'order_code': r[1],
                'order_time': r[2],
                'date_expect': r[3],
                'company_name': r[4],
                'user_name': r[5],
                'products': ps
            })
        return JsonResponse({'e': data['e'], 'closed': complete, 'unclosed': uncomplete})

    elif what == 'brandrecommender':
        o_user = User.objects.get(id=u_id)        
        s = "select id,name from company_brandrecommender where company_id="+\
            "(select ancestor_id from company_user where id="+u_id+") and is_active=true and agent_id="+str(o_user.company_id)
        cur.execute(s)
        rs = cur.fetchall()
        a=[]
        for r in rs:
            a.append({'id':r[0],'name':r[1]})
        return JsonResponse({'e':'','recommenters':a})
    #2021-02-08
    elif what=='mycenter':     
        data={
            'wxwallet':{
                "wallet":[],
                'application':[]
            },    
            'bankinfo':{
                'bank':[],
                'application':[]
            },
            'deliveryinfo':[],
            'payment_priority':{'sn':1,'name':'微信支付平台'},            
        }
        u=User.objects.get(id=int(u_id))
        c=u.company
        cid=str(u.company_id)
        #微信钱包        
        s="select a.id,wx_nickname,w.wx_headimgurl,a.realname,a.cellphone from "+\
            "(SELECT id,cellphone,realname,wxopenid_id,application_id FROM company_wxwallet where company_id="+cid+") a "+\
            "left outer join "+\
            "(SELECT id,wx_openid,wx_nickname,wx_headimgurl FROM company_wxopenid) w "+\
            "on a.wxopenid_id=w.id "            
        cur.execute(s)
        r=cur.fetchone()
        if r:
            data['wxwallet']['wallet'].append({'id':r[0],'nickname':r[1],'headimgurl':r[2],'realname':r[3],'cellphone':r[4]})               
        s="select a.id,to_char(tm,'yyyy-mm-dd HH24:MI:SS'),w.wx_nickname,w.wx_headimgurl,a.realname,"+\
            "status_id,(select name from company_applicationstatus where id=a.status_id),coalesce(a.cmmnt,'') "+\
            "from "+\
            "(select id,tm,status_id,wxopenid_id,realname,payee_wxopenid_id,"+\
                "(select comment from company_applicationreviewlog where content_type='wxwalletapplication' "+\
                    "and obj_id=app.id and status_id=app.status_id order by id desc limit 1) cmmnt "+\
                "from company_wxwalletapplication app where payee_company_id="+cid+") a "+\
            "left outer join "+\
            "(SELECT id,wx_openid,wx_nickname,wx_headimgurl FROM company_wxopenid) w "+\
            "on a.payee_wxopenid_id=w.id "        
        cur.execute(s)
        r=cur.fetchone()
        if r:            
            data['wxwallet']['application']=[{'id':r[0],'time':r[1],'nickname':r[2],'headimgurl':r[3],'realname':r[4],'status':{'id':r[5],'name':r[6],'comment':r[7]}}]
        #银行帐号绑定
        s="SELECT id,bank_name,bank_account,payee,is_default FROM company_bankinfo where company_id="+cid          
        cur.execute(s)
        for r in cur.fetchall():
            data['bankinfo']['bank'].append({
                'id':r[0],
                'bank_name':r[1],
                'bank_account':r[2],
                'payee':r[3],                
                'is_default':r[4],                
            })   
        s="select id,to_char(tm,'yyyy-mm-dd HH24:MI:SS'),bank_name,bank_account,payee,"+\
            "status_id,(select name from company_applicationstatus where id=status_id),"+\
            "coalesce((select comment from company_applicationreviewlog where content_type='bankinfoapplication' "+\
                "and obj_id=app.id and status_id=app.status_id order by id desc limit 1),'') "+\
            "from company_bankinfoapplication app where payee_company_id="+cid
        cur.execute(s)
        for r in cur.fetchall():
            data['bankinfo']['application'].append({'id':r[0],'time':r[1],'bank_name':r[2],'bank_account':r[3],'payee':r[4],'status':{'id':r[5],'name':r[6],'comment':r[7]}})
        #收货地址
        s="SELECT address,coalesce(cellphone,''),coalesce(receiver,''),is_default,id FROM company_deliveryaddress where company_id="+cid
        cur.execute(s)
        #r=cur.fetchone()
        #if r:
        for r in cur.fetchall():
            data['deliveryinfo'].append({
                'address':r[0],
                'cellphone':r[1],
                'receiver':r[2],
                'is_default':r[3],
                'id':r[4],
            })      
        #首选付款方式
        if c.rebate_payment==1:
            data['payment_priority']['sn']=1
            data['payment_priority']['name']='微信支付平台'
        elif c.rebate_payment==2:
            data['payment_priority']['sn']=2
            data['payment_priority']['name']='银行卡'
    #2021-02-28
    elif what=='bulletinboard':
        us=User.objects.filter(id=int(u_id))
        if not us:
            data['e']='user id error'
        else:
            dt= datetime.datetime.now().strftime('%Y-%m-%d')       
            dt_fr = request.GET.get('date_from', dt)
            dt_to = request.GET.get('date_to', dt)
            u=us[0]
            data['data']=list_bulletinboard(u,u.company,dt_fr,dt_to,is_app=True)       
    
    elif what=='entapplication':        
        d=[]
        staffs=Staff.objects.filter(user_id=int(u_id))
        if not staffs:
            data['e']='user is not a staff'
        else:
            staff=staffs[0]
            #内参,不录入产品,按实际扫码产品为准
            if staff.user.ancestor_id in [1644]:
                s="select id,to_char(tm,'yyyy-mm-dd HH24:MI:SS'),subject,headcount,reason,location,"+\
                    "(select name from entertainment_category where id=category_id),is_writeoff,category_id,remark,is_closed,"+\
                    "coalesce((select username from company_user where id=user_confirmed_id),''),"+\
                    "coalesce(to_char(tm_confirmed,'yyyy-mm-dd HH24:MI:SS'),'') "+\
                    "from entertainment_entapplication where staff_id="+str(staff.id)               
                
                cur.execute(s)
                for r in cur.fetchall():
                    ps=[]   
                    s="select a.product_id,p.name,a.cnt_p from "+\
                        "(select product_id,count(*) cnt_p from entertainment_entwriteoff where entapplication_id="+str(r[0])+" group by product_id) a "+\
                        "left outer join "+\
                        "(select id,name,qty_p from product_product) p "+\
                        "on a.product_id=p.id "
                    cur.execute(s)
                    for rr in cur.fetchall():
                        ps.append({
                            'id':rr[0],
                            'name':rr[1],
                            'qty_x':0,
                            'qty_p':0,
                            'cnt_bottles':rr[2],
                            'cnt_writeoff':rr[2],                            
                        })
                    d.append({
                        'app_id':r[0],
                        'category':r[6],
                        'category_id':r[8],
                        'remark':r[9],
                        'is_writeoff':r[7],
                        'time':r[1],
                        'subject':r[2],
                        'headcount':r[3],
                        'reason':r[4],
                        'location':r[5],
                        'products':ps,
                        'is_closed':r[10],
                        'confirmed_by':r[11],
                        'confirmed_tm':r[12],
                    })   
            else:
                s="select id,to_char(tm,'yyyy-mm-dd HH24:MI:SS'),subject,headcount,reason,location,"+\
                    "(select name from entertainment_category where id=category_id),is_writeoff,category_id,remark,is_closed,"+\
                    "coalesce((select username from company_user where id=user_confirmed_id),''),"+\
                    "coalesce(to_char(tm_confirmed,'yyyy-mm-dd HH24:MI:SS'),'') "+\
                    "from entertainment_entapplication where staff_id="+str(staff.id)
                cur.execute(s)
                for r in cur.fetchall():
                    ps=[]
                    s="select a.product_id,p.name,a.qty_x,a.qty_p,a.qty_x*p.qty_p+a.qty_p,"+\
                        "(select count(*) from entertainment_entwriteoff where entapplication_id=a.entapplication_id and product_id=a.product_id) "+\
                        "from "+\
                        "(select entapplication_id,product_id,qty_x,qty_p from entertainment_entappproduct where entapplication_id="+str(r[0])+") a "+\
                        "left outer join "+\
                        "(select id,name,qty_p from product_product) p "+\
                        "on a.product_id=p.id "                    
                    cur.execute(s)
                    for rr in cur.fetchall():
                        ps.append({
                            'id':rr[0],
                            'name':rr[1],
                            'qty_x':rr[2],
                            'qty_p':rr[3],
                            'cnt_bottles':rr[4],
                            'cnt_writeoff':rr[5],                            
                        })
                    d.append({
                        'app_id':r[0],
                        'category':r[6],
                        'category_id':r[8],
                        'remark':r[9],
                        'is_writeoff':r[7],
                        'time':r[1],
                        'subject':r[2],
                        'headcount':r[3],
                        'reason':r[4],
                        'location':r[5],
                        'products':ps,
                        'is_closed':r[10],
                        'confirmed_by':r[11],
                        'confirmed_tm':r[12],
                    })   
        data['data']=d
    elif what=='actapplication':        
        d=[]
        us=User.objects.filter(id=int(u_id))
        if not us:
            data['e']='user id error'
        else:
            u=us[0]
            #内参,会议活动,不录入产品,按实际扫码产品为准
            if u.ancestor_id in [1644]:
                s="select id,to_char(tm,'yyyy-mm-dd HH24:MI:SS'),subject,hotel,dinner_headcount,dinner_address,to_char(dinner_dt,'yyyy-mm-dd'),"+\
                    "category_id,(select name from entertainment_category where id=category_id),is_writeoff,"+\
                    "coalesce(schema_no,''),coalesce(invitee,''),coalesce(contact,''),coalesce(cellphone,''),coalesce(remark,''),is_closed,"+\
                    "case when is_gift then 1 else 0 end,"+\
                    "coalesce((select username from company_user where id=user_confirmed_id),''),"+\
                    "coalesce(to_char(tm_confirmed,'yyyy-mm-dd HH24:MI:SS'),'') "+\
                    "from entertainment_actapplication where user_id="+str(u.id)
                cur.execute(s)
                for r in cur.fetchall():
                    ps=[] 
                    s="select a.product_id,p.name,a.cnt_p from "+\
                        "(select product_id,count(*) cnt_p from entertainment_entwriteoff where actapplication_id="+str(r[0])+" group by product_id) a "+\
                        "left outer join "+\
                        "(select id,name,qty_p from product_product) p "+\
                        "on a.product_id=p.id "
                    cur.execute(s)
                    for rr in cur.fetchall():
                        ps.append({
                            'id':rr[0],
                            'name':rr[1],
                            'qty_x':0,
                            'qty_p':0,
                            'cnt_bottles':rr[2],
                            'cnt_writeoff':rr[2],                            
                        })
                    d.append({
                        'app_id':r[0],
                        'time':r[1],
                        'subject':r[2],
                        'hotel':r[3],
                        'dinner_headcount':r[4],
                        'dinner_address':r[5],
                        'dinner_dt':r[6],
                        'catetory_id':r[7],
                        'category':r[8],
                        'is_writeoff':r[9],
                        'schema_no':r[10],
                        'invitee':r[11],
                        'contact':r[12],
                        'cellphone':r[13],  
                        'remark':r[14],
                        'products':ps,
                        'is_closed':r[15],
                        'is_gift':r[16],
                        'confirmed_by':r[17],
                        'confirmed_tm':r[18],
                    })   
            else:
                s="select id,to_char(tm,'yyyy-mm-dd HH24:MI:SS'),subject,hotel,dinner_headcount,dinner_address,to_char(dinner_dt,'yyyy-mm-dd'),"+\
                    "category_id,(select name from entertainment_category where id=category_id),is_writeoff,"+\
                    "coalesce(schema_no,''),coalesce(invitee,''),coalesce(contact,''),coalesce(cellphone,''),coalesce(remark,''),is_closed,"+\
                    "coalesce((select username from company_user where id=user_confirmed_id),''),"+\
                    "coalesce(to_char(tm_confirmed,'yyyy-mm-dd HH24:MI:SS'),'') "+\
                    "from entertainment_actapplication where user_id="+str(u.id)
                cur.execute(s)
                for r in cur.fetchall():
                    ps=[]
                    s="select a.product_id,p.name,a.qty_x,a.qty_p,a.qty_x*p.qty_p+a.qty_p,"+\
                        "(select count(*) from entertainment_entwriteoff where actapplication_id=a.actapplication_id and product_id=a.product_id) "+\
                        "from "+\
                        "(select actapplication_id,product_id,qty_x,qty_p from entertainment_actappproduct where actapplication_id="+str(r[0])+") a "+\
                        "left outer join "+\
                        "(select id,name,qty_p from product_product) p "+\
                        "on a.product_id=p.id "                    
                    cur.execute(s)
                    for rr in cur.fetchall():
                        ps.append({
                            'id':rr[0],
                            'name':rr[1],
                            'qty_x':rr[2],
                            'qty_p':rr[3],
                            'cnt_bottles':rr[4],
                            'cnt_writeoff':rr[5],                            
                        })
                    d.append({
                        'app_id':r[0],
                        'time':r[1],
                        'subject':r[2],
                        'hotel':r[3],
                        'dinner_headcount':r[4],
                        'dinner_address':r[5],
                        'dinner_dt':r[6],
                        'catetory_id':r[7],
                        'category':r[8],
                        'is_writeoff':r[9],
                        'schema_no':r[10],
                        'invitee':r[11],
                        'contact':r[12],
                        'cellphone':r[13],  
                        'remark':r[14],
                        'products':ps,
                        'is_closed':r[15],
                        'confirmed_by':r[16],
                        'confirmed_tm':r[17],
                    })   
        data['data']=d

    #2021-05-25
    elif what=='giftapplication':        
        d=[]
        us=User.objects.filter(id=int(u_id))
        if not us:
            data['e']='user id error'
        else:
            u=us[0]           
            s="select id,to_char(tm,'yyyy-mm-dd HH24:MI:SS'),"+\
                "entity,receiver,cellphone,coalesce(remark,''),"+\
                "coalesce((select username from company_user where id=user_confirmed_id),''),"+\
                "coalesce(to_char(tm_confirmed,'yyyy-mm-dd HH24:MI:SS'),'') "+\
                "from entertainment_giftapplication a where user_id="+str(u.id)+\
                " and (select count(*) from entertainment_entwriteoff where giftapplication_id=a.id)>0"           
            cur.execute(s)
            for r in cur.fetchall():
                ps=[]                     
                s="select b.product_id,b.x,b.p,"+\
                    "(select name from product_product where id=b.product_id) from "+\
                    "(select a.product_id,sum(x) x,sum(p) p from "+\
                        "(select product_id,case when package_id=3 then 1 else 0 end x,"+\
                            "case when package_id=1 then 1 else 0 end p from entertainment_entwriteoff where giftapplication_id="+str(r[0])+") a "+\
                        "group by product_id) b"                         
                cur.execute(s)
                for rr in cur.fetchall():
                    ps.append({
                        'id':rr[0],
                        'name':rr[3],
                        'qty_x':rr[1],
                        'qty_p':rr[2],                                                  
                    })
                d.append({
                    'products':ps,
                    'app_id':r[0],
                    'time':r[1],
                    'entity':r[2],
                    'receiver':r[3],
                    'cellphone':r[4],
                    'remark':r[5],                    
                    'confirmed_by':r[6],
                    'confirmed_tm':r[7],
                })           
        data['data']=d

    #2021-03-31
    elif what=='periodclosings':
        us=User.objects.filter(id=int(u_id))
        if not us:
            data['e']='user id error'
        else:            
            u=us[0]
            d=[]
            s="select coalesce(bottles,0),coalesce(boxes,0),amount,coalesce(amount_balance,0),amount_topay,"+\
                "c.name,c.terms from "+\
                "(select rebateclosing_id,bottles,boxes,amount,amount_balance,amount_topay from sales_rebatepayable "+\
                    "where agent_id="+str(u.company_id)+" order by id) p "+\
                "left outer join "+\
                "(select id,name,dt_fr,dt_to,lv1_dt_fr,lv1_dt_to,terms::json from sales_rebateclosing where "+\
                    "company_id="+str(u.company.ancestor_id)+" and closed_user is null and calculated_tm is not null) c "+\
                "on p.rebateclosing_id=c.id where c.name is not null"
            cur.execute(s)
            for r in cur.fetchall():
                terms=[]
                for t in r[6]:
                    if t['checked']:
                        terms.append({ 'val':t['val'], 'name':t['name'] })
                d.append({   
                    'amt_current':r[2],
                    'amt_balance':r[3],
                    'amt_topay':r[4],
                    'name':r[5],
                    'terms':terms,
                })
            data['data']=d
    #2021-04-02
    elif what=='rebate_lv1':
        us=User.objects.filter(id=int(u_id))
        if not us:
            data['e']='user id error'
        else:            
            u=us[0]
            cid=str(u.company_id)
            #
            s="select coalesce(r.amt,0),coalesce(p.amt,0),coalesce(r.amt,0)-coalesce(p.amt,0) from "+\
                "(select company_id,sum(val) amt from sales_channelrebatebatch where "+\
                    "company_id="+cid+" group by company_id) r "+\
                "left outer join "+\
                "(select agent_id,sum(amount_total) amt from sales_rebatepaid where "+\
                    "agent_id="+cid+" group by agent_id) p "+\
                "on r.company_id=p.agent_id "
            cur.execute(s)
            r=cur.fetchone()
            d={'acc_amt':0,'acc_paid':0,'acc_unpaid':0}
            if r:
                d['acc_amt']=r[0]
                d['acc_paid']=r[1]
                d['acc_unpaid']=r[2]
            closings=[]            
            s="select coalesce(bottles,0),coalesce(boxes,0),amount,coalesce(amount_balance,0),amount_topay,"+\
                "c.name from "+\
                "(select rebateclosing_id,bottles,boxes,amount,amount_balance,amount_topay from sales_rebatepayable "+\
                    "where agent_id="+cid+" order by id) p "+\
                "left outer join "+\
                "(select id,name,dt_fr,dt_to,lv1_dt_fr,lv1_dt_to from sales_rebateclosing) c "+\
                "on p.rebateclosing_id=c.id"
            cur.execute(s)
            for r in cur.fetchall():
                closings.append({   
                    'amt_current':r[2],
                    'amt_balance':r[3],
                    'amt_topay':r[4],
                    'name':r[5],
                })
            #data['periods']=closings
            #data['amount']=d
            data={'e':'','periods':closings,'amount':d}
    cur.close()
    return JsonResponse(data)   


@csrf_exempt 
def close_order(request):
    data={'e':'','cnt':0}   
    e=time_key_check(request,sK)
    if e:
        data['e']=e
        return JsonResponse(data)    
    #POST,json
    r = json.loads(request.body)
    if 'id' not in r or 'company_id' not in r:
        data['e']='order id or company id missing'
        return JsonResponse(data)
    cur=connection.cursor()
    cur.execute('select vendor_id from company_agentorderbatch where id='+str(r['id']))
    r1=cur.fetchone()
    if not r1:
        data['e']='wrong order id'
        return JsonResponse(data)
    elif r1[0]!=int(r['company_id']):
        data['e']=_('只能关闭属于自己的订单')
        return JsonResponse(data)
    

    
    cur.execute("update company_agentorderbatch set tm_complete=now() where id="+str(r['id']))
    cnt=cur.rowcount
    data['cnt']=cnt
    cur.close()
    return JsonResponse(data)


@csrf_exempt
def permit_order(request):
    data = {'e': ''}
    e = time_key_check(request, sK)
    if e:
        data['e'] = e
        return JsonResponse(data)
        # POST,json
    r = json.loads(request.body)
    if 'id' not in r or 'company_id' not in r  or 'user_id' not in r:
        data['e'] = 'order id or company id missing'
        return JsonResponse(data)

    c = Company.objects.filter(id=r["company_id"])
    if c:
        c = c[0]
        u = User.objects.filter(id=r["user_id"])
        if u:
            u = u[0] 
            ao = AgentOrderBatch.objects.filter(id=r['id'])
            if ao:
                ao = ao[0]   
                ao.review_company = c.parent
                a = AgentOrderReview(   
                    batch=ao, 
                    company=c,
                    user=u
                )
                a.save() 
                ao.save()
            else:
                data['e'] = '订单错误'
        else:
            data['e'] = '用户不存在'   
    else:
        data['公司不存在']   
    return JsonResponse(data)




def fwcode_to_wlcode(request):
    data = {'e': '','code':''}
    code = request.GET.get('code','')
    if not code:
        data['e'] = 'Lack of Parameters.'    
        return JsonResponse(data)
    res = requests.get('http://www.china315net.com:35088/jg/fw_wl/' + code + '/')    
    j=res.json()
    if j['wl']:
        data['code']=j['wl']
    else:
        data['code']=code
    return JsonResponse(data)



#2020-09-15
@csrf_exempt 
def storegeo_add(request):
    data={'e':'','id':0}   
    e=time_key_check(request,sK)
    if e:
        data['e']=e
        return JsonResponse(data)
    #POST,json
    r = json.loads(request.body)
    if 'company_id' not in r or 'lng' not in r or 'lat' not in r:
        data['e']="company_id or lng or lat missing"
        return JsonResponse(data)
    cs=Company.objects.filter(id=int(r['company_id']))
    if not cs:
        data['e']='company_id error'
        return JsonResponse(data)
    c=cs[0]     
    o=StorehouseBD09(company_id=c.id,ancestor_id=c.ancestor_id,lng_lat=[float(r['lng']),float(r['lat'])])
    
    #2020-09-17 第1个仓库位置系统自动审核通过
    if len(c.storehousebd09_company.all())==0:
        o.checked_by_id=1
        o.tm_checked=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')   
        
    o.save()
    data['id']=o.id
    return JsonResponse(data)


#2020-09-15 计算指定座标到最近仓库距离
def store_distance(request,company_id):
    data={'distance':{},'e':''}
    lng=str(request.GET.get('lng',''))
    lat=str(request.GET.get('lat','')) 
    if not lng or not lat:
        data['e']='lng or lat missing'
        return JsonResponse(data)
    cs=Company.objects.filter(id=int(company_id))
    if not cs:
        data['e']='company id error'
        return JsonResponse(data)
    cur=connection.cursor()
    distances=[]
    cur.execute("select id,lng_lat from inventory_storehousebd09 where company_id="+company_id)
    rs=cur.fetchall()
    for r in rs:       
        s="select public.st_distance(public.st_geogfromtext('SRID=4326;POINT("+lng+" "+lat+")'),"+\
            "public.st_geogfromtext('SRID=4326;POINT("+str(r[1][0])+\
                " "+str(r[1][1])+")'))::int"         
        cur.execute(s)
        rr=cur.fetchone()    
        distances.append({'store_id':r[0],'distance':rr[0]})
    if distances:
        distances.sort(key=lambda k: (k.get('distance', 0)))
        data['distance']=distances[0]
    return JsonResponse(data)





#2020-10-04
@csrf_exempt
def list_requisition_satus(request,company_id,status_id=None):
    data={'e':'','data':[]}   
    e=time_key_check(request,sK)
    if e:
        data['e']=e
        return JsonResponse(data)    
    cur=connection.cursor()
    if status_id:
        sWhere=" where company_id="+company_id+" and status_id="+status_id
        s="select a.id,b.name,b.code,b.id,"+\
            "(select name from company_company where id=a.agent_id),a.tm from "+\
            "(select id,agent_id,scheme_id,tm from promotion_giftsrequisition"+sWhere+") a "+\
            "left outer join "+\
            "(select id,name,code from promotion_giftsscheme) b "+\
            "on a.scheme_id=b.id order by a.tm"
        cur.execute(s)
        rs=cur.fetchall()
        for r in rs:
            products=[]
            materials=[]
            s="select product_id,x,p,"+\
            "(select name from product_product where id=product_id) from "+\
            "(select product_id,sum(x) x,sum(p) p from "\
                "(select product_id,case package_id when 3 then 1 else 0 end x,"+\
                    "case package_id when 1 then 1 else 0 end p  "+\
                    "from promotion_requisitionproducts "+\
                    "where requisition_id="+str(r[0])+\
                ") a "+\
                "group by product_id"+\
            ") b"    
            cur.execute(s)
            for rr in cur.fetchall():
                products.append({"id":rr[0],"name":rr[3],"x":rr[1],"p":rr[2]})
            s="select material_id,qty,"+\
                "(select name from promotion_material where id=material_id) from "+\
                "(select material_id,sum(qty) qty from  promotion_requisitionmaterials "+\
                    "where requisition_id="+str(r[0])+" group by material_id"+\
                ") a"
            cur.execute(s)
            for rr in cur.fetchall():
                materials.append({"id":rr[0],"name":rr[2],"qty":rr[1]})
            data['data'].append({                
                "scheme_name":r[1],
                "scheme_code":r[2],
                "scheme_id":r[3],
                "requisition_id":r[0],
                "requisition_time":r[5].strftime("%Y-%m-%d %H:%M:%S"),
                "agent_name":r[4],
                "products":products,
                "materials":materials
            })           
    else:        
        s="select a.id,a.name,b.cnt,a.action from "+\
            "(select id,name,action from promotion_giftsstatus where company_id="+company_id+" or company_id is null) a "+\
            "left outer join "+\
            "(select status_id,count(*) cnt from promotion_giftsrequisition "+\
                "where company_id="+company_id+" group by status_id"+\
            ") b " +\
            "on a.id=b.status_id"
        cur.execute(s)
        for r in cur.fetchall():
            data['data'].append({"status_id":r[0],"status_name":r[1],"count":r[2],"action":r[3]})   

    cur.close()
    return JsonResponse(data)





#2020-10-04
@csrf_exempt
def giftsrequisition_update(request,requisition_id,status_id):
    data={'e':''}   
    e=time_key_check(request,sK)
    if e:
        data['e']=e        
        return JsonResponse(data)   
    cur=connection.cursor()
    r=json.loads(request.body)    
    product=r['product']
    material=r['material']         
    uid=str(r['user_id'])
    cnt_product=0
    cnt_material=0
    
    #目前5个ID，符合下一状态为当前状态加一
    if status_id in ['1','2','3','4','5']:
        status_id=str(int(status_id)+1)    
    l=RequisitionStatusLog(requisition_id=int(requisition_id),status_id=int(status_id),user_id=int(uid))
    l.save()
    lid=str(l.id)
    if product:   
        cur.execute("create temp table codes(label_code character varying(20),tb character varying(20),product_id integer,"+\
            "code character varying(20),label_id bigint,label_pk_id bigint,label_pkg smallint,err character varying(100))")
        for p in product:
            pid=str(p['product_id'])
            a=[] 
            for code in p['codes']:        
                #codes: [{code: 123456789012,  err_code: 1,  e: "错误信息1"}]            
                e='null'
                if code['e']:
                    e="'"+code['e'][:100]+"'"
                a.append("("+pid+",'" + code['code'] + "','" + code['code'][:4] + "',"+e+")")
            sSql="insert into codes (product_id,label_code,tb,err) values " + ",".join(a)   
            cur.execute(sSql)
        #处理标签替换
        s="update codes set code="+\
            "coalesce((select code from label.lbl_replace_details where code0=label_code),label_code)"
        cur.execute(s)
        #2020-07-27 处理跨段动态绑定
        s="update codes set tb=b.tbname from "+\
            "(SELECT tb,label_code from codes) a "+\
            "left outer join "+\
            "(SELECT segment, tbname FROM label.lbl_tbcross) b "+\
            "on a.tb=b.segment "+\
            "where  codes.label_code=a.label_code and b.tbname is not null"
        cur.execute(s)
        cur.execute("update codes set tb='t'||left(code,4) where length(tb)=4")    
        
        cur.execute("select tb from codes group by tb")
        rs = cur.fetchall()
        for r in rs:  
            cur.execute("select to_regclass('label." + r[0] + "')")
            r0 = cur.fetchone()
            if r0[0]:
                s="update codes set "+\
                    "label_id=b.id,"+\
                    "label_pkg=b.package_id,"+\
                    "label_pk_id=coalesce(b.parent_id,b.id) "+\
                    "from "+\
                    "(SELECT label_code,code from codes where tb='"+r[0]+"') a "+\
                    "left outer join "+\
                    "(select id,code,parent_id,package_id,is_pk from label."+r[0]+") b "+\
                    "on a.label_code=b.code "+\
                    "where tb='"+r[0]+"' and codes.code=a.code"
                cur.execute(s) 
        cur.execute("select tb from codes group by tb")
        rs = cur.fetchall()
        for r in rs:    
            s="insert into promotion_requisitionlogproduct"+\
                "(requisitionlog_id,label_code,package_id,label_id,label_pk_id,dt,tm,product_id,status_id) select "+\
                lid+",label_code,label_pkg,label_id,label_pk_id,now(),now(),product_id,"+str(status_id)+\
                " from codes a where tb='"+r[0]+"'"               
            cur.execute(s)   
            cnt_product=cnt_product+cur.rowcount
    if material:
        a=[]
        for d in material:
            e='null'
            if d['e']:
                err="'"+d['e']+"'"
            a.append("("+lid+","+str(d['material_id'])+","+str(d['qty'])+",now(),now(),"+str(status_id)+")")
        s="insert into promotion_requisitionlogmaterial(requisitionlog_id,material_id,qty,dt,tm,status_id) values "+",".join(a)
        cur.execute(s)
        cnt_material=cnt_material+cur.rowcount  
    #检查修改STATUS
    #1;"待领取"     业务员（扫码）领用
    #2;"待出库"     销售助理/内勤
    #3;"待派发"     业务员
    #4;"待确认"     经销商（扫码）确认
    #5;"已确认"                

    #更新领用状态     
    cur.execute("update promotion_giftsrequisition set status_id="+status_id+" where id="+requisition_id)   
    data['cnt_product']=cnt_product
    data['cnt_material']=cnt_material
    return JsonResponse(data)


#2020-10-05
@csrf_exempt
def requisition_agent_list(request,company_id,status_id=None):    
    data={'e':'','data':[]}   
    e=time_key_check(request,sK)
    if e:
        data['e']=e
        return JsonResponse(data)    
    cur=connection.cursor()
    if status_id:
        sWhere=" where agent_id="+company_id+" and status_id="+status_id
        s="select a.id,b.name,b.code,b.id,"+\
            "(select name from company_company where id=a.agent_id),a.tm from "+\
            "(select id,agent_id,scheme_id,tm from promotion_giftsrequisition"+sWhere+") a "+\
            "left outer join "+\
            "(select id,name,code from promotion_giftsscheme) b "+\
            "on a.scheme_id=b.id order by a.tm"
        cur.execute(s)
        rs=cur.fetchall()
        for r in rs:
            products=[]
            materials=[]
            s="select product_id,x,p,"+\
            "(select name from product_product where id=product_id) from "+\
            "(select product_id,sum(x) x,sum(p) p from "\
                "(select product_id,case package_id when 3 then 1 else 0 end x,"+\
                    "case package_id when 1 then 1 else 0 end p  "+\
                    "from promotion_requisitionproducts "+\
                    "where requisition_id="+str(r[0])+\
                ") a "+\
                "group by product_id"+\
            ") b"    
            cur.execute(s)
            for rr in cur.fetchall():
                products.append({"id":rr[0],"name":rr[3],"x":rr[1],"p":rr[2]})
            s="select material_id,qty,"+\
                "(select name from promotion_material where id=material_id) from "+\
                "(select material_id,sum(qty) qty from  promotion_requisitionmaterials "+\
                    "where requisition_id="+str(r[0])+" group by material_id"+\
                ") a"
            cur.execute(s)
            for rr in cur.fetchall():
                materials.append({"id":rr[0],"name":rr[2],"qty":rr[1]})
            data['data'].append({                
                "scheme_name":r[1],
                "scheme_code":r[2],
                "scheme_id":r[3],
                "requisition_id":r[0],
                "requisition_time":r[5].strftime("%Y-%m-%d %H:%M:%S"),
                "agent_name":r[4],
                "products":products,
                "materials":materials
            })           
    else:        
        s="select a.id,a.name,b.cnt,a.action from "+\
            "(select id,name,action from promotion_giftsstatus where company_id is null or "+\
                "company_id=(select ancestor_id from company_company where id="+company_id+")"+\
            ") a "+\
            "left outer join "+\
            "(select status_id,count(*) cnt from promotion_giftsrequisition "+\
                "where agent_id="+company_id+" group by status_id"+\
            ") b " +\
            "on a.id=b.status_id"
        cur.execute(s)
        for r in cur.fetchall():
            data['data'].append({"status_id":r[0],"status_name":r[1],"count":r[2],"action":r[3]})   

    cur.close()
    return JsonResponse(data)



#2020-11-20 消费者扫防伪码
def nc_labelScan(request):
    data = {
        "e": "",
        "img": "",
        "cnt": "",
        "tm_1st": "",
        "province": "",
        "city": "",
        "address": "",
        "product":"",
        "photo":"",        
        "batch_no":"",
        "qc":"",        
        "package":"",
        "outbound_date":"",
        "price":0,
        "alcohol":0,
        "volume":0,
        "series":'',
        "last_record":{},    
    }
    lng = request.GET.get("lng", 0)
    lat = request.GET.get("lat", 0)
    fwcode = request.GET.get("fwcode", "")
    uid = request.GET.get("uid", "")
    
    
       
    if not fwcode:
        data['e']='label code missing'
        return JsonResponse(data)
    cur=connection.cursor()
    
    #解析地理位置
    province = ""
    city = ""
    address = ""
    headers = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/63.0.3239.132 Safari/537.36 QIHU 360SE'
    }
    if lng and lat:
        lng_lat = gcj02_to_bd09(float(lng), float(lat))
        url_base = "https://api.map.baidu.com/geocoder/v2/?ak=ASl2xmUaYpjwdWy3GPBQbpUK"
        url = url_base + "&location=" + str(lng_lat[1]) + "," + str(lng_lat[0]) + "&output=json"
        location_result = requests.get(url, headers=headers)
        p = json.loads(location_result.text)
        if p["status"] == 0:
            province = p['result']['addressComponent']['province']
            city = p['result']['addressComponent']['city']
            address = p['result']['formatted_address']
            data["province"] = province
            data["city"] = city
            data["address"] = address
    if uid:
        suid='pg_'+uid
    else:
        if request.META.get('HTTP_X_FORWARDED_FOR'):
            ip = request.META.get("HTTP_X_FORWARDED_FOR")
        else:
            ip = request.META.get("REMOTE_ADDR")
        suid = ip
    #不写入
    record = '0'
    #返回1条
    history = '1'
    params = {}
    params['fwcode'] = fwcode
    params['uid'] = suid
    params['record'] = record
    params['history'] = history
    r = requests.get('http://www.china315net.com:35207/tjfw/get/', params)
    if r.status_code == 200:        
        d = r.json()
        data['img'] = "data:image/png;base64," + d['img']
        data['cnt'] = d['counter']['net']
        if d['history']:
            data['tm_1st'] = d['history'][0]['tm']
        else:
            data['tm_1st'] = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    else:
        data['e'] = 'retrieve image failed'

    c=''    #物流标签号码
    #防伪转物流码-酒鬼防伪码以40开头    
    if fwcode[:2]=='40':        
        cur.execute("SELECT wl_code FROM label.lbl_fw_wl where fw_code='"+c+"'")
        r=cur.fetchone()
        if r:
            c=r[0]
        else:
            r=requests.get('http://china315net.com:35222/wuliu/47/fw/wuliu/?fw_code='+fwcode)
            d=r.json()
            if d['wl']:
                c=d['wl']
    #取产品名称，产品图片等信息 
    if not c:
        data['e']='转物流码失败'
    else:
        rg=c[:4]
        label_pk_id=None
        try:
            #箱-瓶二级套标, 暂不考虑箱-盒-瓶三级套标
            cur.execute("select coalesce(parent_id,id) from label.t"+rg+" where code='"+c+"'")
            r=cur.fetchone()
            if r:
                label_pk_id=str(r[0])
        except Exception:
            pass
            #"(SELECT value FROM product_productprop where categoryprop_id=3 and product_id=a.product_id) vol,"+\
            #"(SELECT value FROM product_productprop where categoryprop_id=4 and product_id=a.product_id) pkg "+\

        label_id=None
        label_pkg_id=None
        if not label_pk_id:
            data['e']='label code error'
        else:  
            s="select coalesce(p.name,pnc.name,''),"+\
                "coalesce(p.photo,pnc.photo,''),"+\
                "coalesce(p.price,pnc.price,0),"+\
                "coalesce(b.code,b.batch_no,'') batch_no,"+\
                "coalesce(b.qc_name,'') qc,"+\
                "(SELECT value FROM product_productprop where categoryprop_id=2 and id in "+\
                    "(SELECT prd_property_id FROM product_prdprops where product_id=a.product_id)) alc,"+\
                "(SELECT value FROM product_productprop where categoryprop_id=3 and id in "+\
                    "(SELECT prd_property_id FROM product_prdprops where product_id=a.product_id)) vol,"+\
                "(SELECT value FROM product_productprop where categoryprop_id=4 and id in "+\
                    "(SELECT prd_property_id FROM product_prdprops where product_id=a.product_id)) pkg,"+\
                "coalesce(to_char((select dt from inventory_stkdetails where label_pk_id="+label_pk_id+\
                    " and company_id=1 and vtype_id=2 limit 1),'yyyy-mm-dd'),''),"+\
                "coalesce((select name from product_series where id=pnc.series_id),"+\
                    "(select name from product_series where id=p.series_id)),"+\
                "a.label_id,a.label_pkg,coalesce(pnc.id,p.id) from "+\
                "(select product_id,label_pk_id,batch_id,"+\
                    "(select nc_id from z_jg_nc_product where jg_id=product_id) nc_product_id,label_id,label_pkg "+\
                    "from inventory_stkdetails where label_pk_id="+label_pk_id+" and is_delete=false "+\
                    " and company_id in (1,1644) and vtype_id in (1,2) and is_delete=false limit 1"+\
                ") a "+\
                "left outer join "+\
                "(select id,name,photo,price,series_id from product_product) p "+\
                "on a.product_id=p.id "+\
                "left outer join "+\
                "(select id,name,photo,price,series_id from product_product) pnc "+\
                "on a.nc_product_id=pnc.id "+\
                "left outer join "+\
                "(select id,batch_no,code,qc_name from inventory_batch) b "+\
                "on a.batch_id=b.id"   
            cur.execute(s)
            r=cur.fetchone()
            if r:  
                product_id=r[12]
                label_id=r[10]
                label_pkg_id=r[11]
                data['product']=r[0]
                data['package']=r[7] 
                if r[1]:
                    data['photo']='/media/'+r[1]
                data['price']=r[2]
                data['batch_no']=r[3]
                data['qc']=r[4]
                data['alcohol']=r[5]
                data['volume']=r[6]                
                data['outbound_date']=r[8]
                data['series']=r[9]

                #2020-11-19 取上次查询记录
                cur.execute("select province,city,address,to_char(tm,'yyyy-mm-dd HH24:MI:SS') from sales_labelscan where "+\
                    "label_pk_id="+label_pk_id+" and scan_type=1 order by id desc limit 1")
                rr=cur.fetchone()
                if rr:
                    data['last_record']={'province':rr[0],'city':rr[1],'address':rr[2],'time':rr[3]}
            cur.close()     
            
            #scan_types={
            #    'image':1,          #防伪查询
            #    'consumer':2,       #消费者扫码
            #    'inspector':3,      #市场稽查
            #    'tj':4,             #内部查询
            #}            
            pos_permission=False
            if lng and lat:
                pos_permission=True
            l=LabelScan(
                consumer_id=int(uid), 
                company_id = 1644,  
                scan_type=1,                #防伪查询
                label_code =c, 
                label_pkg = label_pkg_id,
                label_id = label_id,
                label_pk_id = label_pk_id,
                product_id = product_id,
                province = province,
                city = city,
                address = address,
                pos_permission = pos_permission,
                lng_lat = [lng,lat]
            )
            try:
                l.save()           
            except Exception:
                pass
    return JsonResponse(data)



#2020-11-04
def labelscan_save(data):
    l=LabelScan( 
        consumer_id=data['consumer_id'],
        company_id = data['company_id'],
        label_code = data['label_code'],
        label_pkg = data['label_pkg'],
        label_id = data['label_id'],
        label_pk_id =data['label_pk_id'],        
        activity_id = data['activity_id'],        
        product_id =data['product_id'],
        province =data['province'],
        city = data['city'],
        address = data['address'],
        pos_permission = data['pos_permission'],
        lng_lat = data['lng_lat'],     
        remark = data['remark']
    )
    if data['agent_name']:
        l.agent_name = data['agent_name']
    if data['is_first']:
        l.is_first = data['is_first']
    l.save()
    return l.id
def imagequery_save(data):
    l=LabelImageQuery(    
        consumer_id = data['consumer_id'],
        company_id = data['company_id'],
        label_code = data['label_code'],     
        remark = data['remark'] 
    )
    l.save()
    return l.id

@csrf_exempt
def getPhoneVerifyCode(request):
    company_name = request.POST.get('compname', '')
    if company_name == "海南天鉴":
        company_name = "天鉴防伪"
    cur = connection.cursor()
    data = {'msg': '', 'reason': '', 'yzm': '', 'e': ''}
    phone = request.POST.get('phone')
    
    if not company_name or not phone:
        return JsonResponse({'e': 'lack of parameters'})

    is_login=request.POST.get('is_login',0)    
    cur.execute("select id from company_terminalregister where cell_phone='" + phone + "'")
    r = cur.fetchone()
    if r and not is_login:
            data['e'] = "您填写的手机号码已被注册"
    else:
        us=User.objects.filter(username=phone)
        if not is_login or us:
            res = requests.get('http://tjfnew.china315net.com/api/requestCode.ashx',
                            {'phone': phone, 'compname': company_name})
            t = res.content.decode("utf-8")
            if t == 'no':
                data['msg'] = 'no'
            else:
                d = json.loads(t)
                data['msg'] = d['msg']
                data['reason'] = d['reason']
                data['yzm'] = d['yzm']
                # {"msg":"success","reason":"done！","yzm":"6360"}
        else:
            data['e'] = "您的手机号码没有可登录的关联帐户"
    return JsonResponse(data)


@csrf_exempt
def wx_salesmanPunchIn(request):
    uid = request.POST.get('uid', '')
    compid = request.POST.get('compid', '')
    lng_lat = request.POST.get('lng_lat', '')
    terminal_id = request.POST.get('terminal_id', '')
    distance = request.POST.get('distance', '')
    lng_lat = lng_lat.split(',')

    if not uid or not compid or not lng_lat or not terminal_id:
        return JsonResponse({'e': 'lack of parameters'})

    cur = connection.cursor()
    c = Company.objects.filter(id=terminal_id)
    if c.exists():   
        tm = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        sql3 = "insert into z_salesmancheckin (uid, lng_lat, dst_compid, distance, tm) " \
               "values ("+uid+",'{"+str(lng_lat[0])+","+str(lng_lat[1])+"}' , "+\
               terminal_id+", '"+distance+"','"+tm+"')"

        cur.execute(sql3)
        return JsonResponse({'msg': 'ok'})
    else:
        return JsonResponse({'msg': '所属公司不存在'})

@csrf_exempt
def wx_salesmanPunchInHistory(request):
    data = {'e': ''}
    uid = request.GET.get('uid', '')
    cur = connection.cursor()
    sql = f"select distance, tm, " \
          f"(select name from logistics.company_company where id=dst_compid)," \
          f"(select lng_lat from logistics.company_company where id=dst_compid)," \
          f"lng_lat from logistics.z_salesmancheckin where uid={uid}"
    
    cur.execute(sql)
    r = cur.fetchall()
    history = []
    for i in r:
        history.append({
            'name': i[2],
            'time': i[1].strftime('%Y-%m-%d %H:%M:%S'),
            'distance': i[0],
            'salesman_lnglat': i[4],
            'company_lnglat': i[3]
        })
    data['history'] = history
    return JsonResponse(data)



@csrf_exempt 
def wx_salesmanTerminalsPostion(request):
    uid = request.GET.get('uid', '')
    compid = request.GET.get('compid', '')
    lng_lat = request.GET.get('lng_lat', '')
    
    if not uid or not compid or not lng_lat:
        return JsonResponse({'e': 'lack of parameters'})

    lng_lat = lng_lat.split(',')

    c = Company.objects.filter(id=compid)
    if c.exists(): 

        cur = connection.cursor()
        ssql = "select t.id,t.name,t.lng_lat, public.ST_DistanceSphere("+\
                "public.st_setsrid(public.st_makepoint(lng_lat[1],lng_lat[2]),4326),"+\
                "public.st_setsrid(public.st_makepoint("+lng_lat[0]+","+lng_lat[1]+"),4326)"+\
            ") AS distance "+\
            "from logistics.company_company t where array_length(lng_lat,1)>0 "+\
            "and ancestor_id = "+compid +" and lng_lat is not null "+\
            "and public.ST_DistanceSphere(public.st_setsrid(public.st_makepoint(lng_lat[1],lng_lat[2]),4326),"+\
                "public.st_setsrid(public.st_makepoint("+lng_lat[0]+","+lng_lat[1]+"),4326)"+\
            ")<50000 order by distance"         
        cur.execute(ssql)
        rows = cur.fetchall()
        lists = []
        for r in rows:
        
            teriminal_lng_lat = r[2]

            lt = bd09_to_gcj02( float(teriminal_lng_lat[0]), float(teriminal_lng_lat[1]) )

            lists.append({
                'id':r[0],
                'name':r[1],
                'lng_lat':lt,
                'distance':r[3]
            })  

        return JsonResponse({'msg': 'ok','lists':lists})
    else:
        return JsonResponse({'msg': '所属公司不存在'})


# Jonathan 2020-12-14
# 前端扫码记录及图片数据请求接口
@csrf_exempt
def wx_label_scan_record(request):
    data = {
        "e": "",
        "img": "",
        "cnt": "",
        "tm_1st": "",
        "province": "",
        "city": "",
        "address": "",
        "product_name":"",
        "company_name":"",
        "code":"",
        "agent_name":"",
        "fh_time":""
    }
    lng = request.GET.get("lng", 0)
    lat = request.GET.get("lat", 0)
    fwcode = request.GET.get("fwcode", "")
    uid = request.GET.get("uid", "")
    compid = request.GET.get("compid", "")

    if not fwcode:
        data['e'] = 'label code missing'
        return JsonResponse(data)
    if not uid or not compid:
        data['e'] = 'uid and compid missing'
        return JsonResponse(data)

    data['code'] = fwcode
    
    if len(uid) >10:
        uid = decrypt_p(uid)

    # 解析地理位置
    province = ""
    city = ""
    address = ""
    district = ""
    headers = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/63.0.3239.132 Safari/537.36 QIHU 360SE'
    }
    lng_lat = [lng, lat]
    if lng and lat:
        lng_lat = gcj02_to_bd09(float(lng), float(lat))
        url_base = "https://api.map.baidu.com/geocoder/v2/?ak=ASl2xmUaYpjwdWy3GPBQbpUK"
        url = url_base + "&location=" + str(lng_lat[1]) + "," + str(lng_lat[0]) + "&output=json"
        location_result = requests.get(url, headers=headers)
        p = json.loads(location_result.text)
        if p["status"] == 0:
            province = p['result']['addressComponent']['province']
            city = p['result']['addressComponent']['city']
            district = p['result']['addressComponent']['district']
            address = p['result']['formatted_address']
            data["province"] = province
            data["city"] = city
            data["address"] = address
    record = '1'
    # 返回1条
    history = '1'
    params = {}
    params['fwcode'] = fwcode
    params['uid'] = uid
    params['record'] = record
    params['history'] = history
    r = requests.get('http://www.china315net.com:35207/tjfw/get/', params)
    if r.status_code == 200:
        d = r.json()
        data['img'] = "data:image/png;base64," + d['img']
        data['cnt'] = d['counter']['net']
        if d['history']:
            data['tm_1st'] = d['history'][0]['tm']
        else:
            data['tm_1st'] = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    else:
        data['e'] = 'retrieve image failed'
    is_first = False
    cur = connection.cursor()
    cur.execute("select count(*) from sales_labelscan where label_code = '" + fwcode + "' and consumer_id = " + uid)
    r = cur.fetchone()
    u_scan_num = r[0]
    if u_scan_num == 0:
        is_first = True
    product_id = None
    agent_name = ''
    comp = Company.objects.filter(id=int(compid))
    if comp:
        d = label_sys_info(fwcode, comp[0], has_price=False)
        if d['product']['id'] !=0:
            product_id = d['product']['id'] 
            data['product_name'] = d['product']['name']
        if d['stock_dev']:
            s_len = range(len(d['stock_dev']))
            for s in s_len:
                if d['stock_dev'][s]['voucher_type'] == '销售出库':
                    agent_name = d['stock_dev'][s]['to_company']
                    data['agent_name'] = agent_name
                    data['fh_time'] = d['stock_dev'][s]['time']
                    break



    data_save = {
        'consumer_id': int(uid),
        'company_id': compid,
        'label_code': fwcode,
        'label_pkg': None,
        'label_id': None,
        'label_pk_id': None,
        'activity_id': None,
        'product_id': product_id,
        'province': province,
        'city': city,
        'district': district,
        'address': address,
        'pos_permission': True,
        'lng_lat': lng_lat,
        'remark': '',
        'is_first': is_first,
        'agent_name':agent_name
    }
    labelscan_save(data_save)
    return JsonResponse(data)



#2020-12-21 同步微信OPENID，昵称，头像; user: openid'id
def refresh_wxopenid(company_id,user_id,wx_openid,wx_nickname,wx_headimgurl,category=1):    
    wx_key=str(company_id)+'_'+wx_openid
    user_key='user_openids'+str(user_id)
    o=None   
    id=None
    #有帐号登录的用户微信（公司职员，经销商）
    if category==1:
        r=rd_wx.hgetall(wx_key)
        if r: 
            id=rd_wx.hget(wx_key,'id')
            if r['wx_nickname']!=wx_nickname or r['wx_headimgurl']!=wx_headimgurl:               
                objs=WxOpenid.objects.filter(company_id=company_id,wx_openid=wx_openid)
                if objs:
                    o=objs[0]                         
        else:
            objs=WxOpenid.objects.filter(company_id=company_id,wx_openid=wx_openid)
            if objs:
                o=objs[0]
            else:
                o=WxOpenid(company_id=company_id,wx_openid=wx_openid)            
        if o:
            o.wx_nickname=wx_nickname
            o.wx_headimgurl=wx_headimgurl
            o.save()
            id=o.id           
            rd_wx.hmset(wx_key,{'wx_nickname':wx_nickname,'wx_headimgurl':wx_headimgurl,'id':id}) 
        #user_id 是否有此openid
        if not id:
            objs=WxOpenid.objects.filter(company_id=company_id,wx_openid=wx_openid)
            if objs:
                obj=objs[0]
                id=obj.id
        if id:
            if not rd_wx.sismember(user_key,id):           
                rd_wx.sadd(user_key,id)
                objs=User.objects.filter(id=int(user_id))
                if objs:
                    obj=objs[0]
                    
                    #2021-03-20
                    obj.wx_openid=wx_openid
                    
                    if obj.wxopenids:                    
                            obj.wxopenids.append(id)
                    else:
                        obj.wxopenids=[(id)]
                    obj.save()
    return 0


#2020-12-21 前端：有帐号用户，微信息同步接口 
def sync_user_account(request,company_id):
    data={'e':''}   
    e=time_key_check(request,sK)
    if e:
        data['e']=e
        return JsonResponse(data)   
    wx_openid=request.GET.get('wx_openid','')
    wx_nickname=request.GET.get('wx_nickname','')
    wx_headimgurl=request.GET.get('wx_headimgurl','')
    user_id=request.GET.get('user_id','')
    
    #2021-01-05
    us=User.objects.filter(id=int(user_id))
    if not us:
        data['e']='company id err'
        return JsonResponse(data)
    u=us[0]
    if u.company_id!=int(company_id):
        data['e']='user account err'
        return JsonResponse(data)


    if not wx_openid or not wx_nickname or not wx_headimgurl or not user_id:
        data['e']='wx_openid,wx_nickname,wx_headimgurl missing'
        return JsonResponse(data)   
    try:
        #refresh_wxopenid(int(company_id),int(user_id),wx_openid,wx_nickname,wx_headimgurl,category=1)
        refresh_wxopenid(u.ancestor_id,int(user_id),wx_openid,wx_nickname,wx_headimgurl,category=1)
    except Exception:
        pass
    return JsonResponse(data)




#窜货查询
def chcx(request,company_id=None):
    data = {
        'company_id':company_id,
        'label_code':'',
        'key':'',
        'tm':''
    }
    sK = 'yglkfdvgu$#&3t@j'
    tm = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    m2 = hashlib.md5()
    m2.update((sK + tm).encode(encoding='UTF-8'))
    k = m2.hexdigest()

    label_code = request.COOKIES.get('label_code', '')
    data['label_code']= label_code
    data['key'] = k
    data['tm'] = tm
    return render(request, "dist/chcx/index.html",data)
    
#窜货查询获取动态密码
@csrf_exempt
def chcx_getcode(request):
    data = {
        'e':'',
        'code':''
    }
    company_id = request.POST.get('company_id','')
    phone = request.POST.get('phone','')
    if company_id and phone:
        cur = connection.cursor()
        cur.execute("select id from company_user where phone = '" + phone+"' and ancestor_id = "+company_id)
        r = cur.fetchone()
        if r:
            user_id = r[0]
            cur.execute("select count(*) from company_user_groups where user_id = "+user_id +" and group_id = 1")
            rr = cur.fetchone()
            if rr:
                if rr[0] > 0:
                    codes = []
                    for i in range(6):
                        codes.append(str(random.randint(0, 9)))
                    code = ''.join(codes)
                    text = '您的验证码是：' + code + '。请不要把验证码泄露给其他人!'
                    send_sms(phone, text)
                    data['code'] = code
                else:
                    data['e'] = '无权限'
            else:
                data['e'] = '无权限'
        else:
            data['e'] = '用户不存在'
    else:
        data['e'] = '数据丢失'
    return  JsonResponse(data)


# 消费者中奖记录
@csrf_exempt  
def wx_consumer_activity_win_record(request):
    data = {
        "e": "",
        "records": []
    }
    consumer_id = request.GET.get("consumer_id", "")
    if len(consumer_id) > 10:
        consumer_id = decrypt_p(consumer_id)

    company_id = request.GET.get("company_id", "")
    label_code = request.GET.get("label_code", "")
    award_cat = request.GET.get("award_cat", "")
    exclude_award_cat = request.GET.get("exclude_award_cat", "")
    if label_code:
        if company_id == "19318": 
            winner = ConsumerWinner.objects.filter( 
                company_id=int(company_id),
                label_code=label_code 
            )
        else:
            winner = ConsumerWinner.objects.filter(
                consumer_id=int(consumer_id),
                company_id=int(company_id),
                label_code=label_code 
            ).exclude(award=None)
    elif award_cat:
        winner = ConsumerWinner.objects.filter(
            consumer_id=int(consumer_id),
            company_id=int(company_id),
            award_cat=award_cat,
        )
    else:
        if exclude_award_cat: 
            winner = ConsumerWinner.objects.filter(
                consumer_id=int(consumer_id),
                company_id=int(company_id)).exclude(award_cat=exclude_award_cat)
        else:
            if company_id == "19318":
                winner = ConsumerWinner.objects.filter(
                    consumer_id=int(consumer_id),
                    company_id=int(company_id))
            else: 
                winner = ConsumerWinner.objects.filter(
                    consumer_id=int(consumer_id),
                    company_id=int(company_id)).exclude(award=None)
    if winner:
        for w in winner:

            tm_redeemed = ''
            if w.tm_redeemed:
                tm_redeemed = w.tm_redeemed.strftime("%Y-%m-%d %H:%M:%S")
            is_need_address = False
            if w.award_cat == '实物':
                is_need_address = True
            award_img = ''
            if w.award_img:
                award_img = 'https://davis.china315net.com/media/'+w.award_img 
            awid = ''
            if w.award:
                awid = w.award.award.id
            can_active_award_name = ''
            if w.can_active_award:
                can_active_award_name = w.can_active_award.name
            tm_expire = ''
            if w.tm_expire:
                tm_expire = w.tm_expire.strftime("%Y-%m-%d %H:%M:%S")
            is_get_user = False
            if int(consumer_id) == w.consumer_id:
                is_get_user = True

            product_id = 0
            if w.product:
                product_id = w.product_id

            data['records'].append({
                'id': encrypt_p(str(w.id)),
                'awid':awid,
                'activity_name': w.activity_name,
                'award_cat': w.award_cat,
                'award_name': w.award_name,
                'award_img': award_img,
                'is_need_hexiao': w.is_need_hexiao,
                'is_hexiao': w.is_hexiao,
                'is_need_address': is_need_address,
                'tm_redeemed': tm_redeemed,
                'tm': w.tm.strftime("%Y-%m-%d %H:%M:%S"),
                'can_active_award_name':can_active_award_name,
                'is_sharing':w.is_sharing,
                'tm_expire':tm_expire,
                'is_get_user':is_get_user,
                'product_id':product_id
            })
    return JsonResponse(data)


# 消费者中奖记录汇总
@csrf_exempt
def wx_consumer_activity_win_summary(request): 
    data = {
        "e": "",
        "records": {}
    }
    consumer_id = request.GET.get("consumer_id", "") 
    company_id = request.GET.get("company_id", "")
    award_cat = request.GET.get("award_cat", "")
    if consumer_id and company_id:
        consumer_id = decrypt_p(consumer_id) 
        cur = connection.cursor()
        filter_sql = "consumer_id = "+consumer_id
        if award_cat:
            filter_sql = filter_sql +" and award_cat ='"+award_cat+"' and tm_used is null "



        cur.execute("SELECT (SELECT award_id from sales_activityawards WHERE id = a.award_id),"+
            "award_name,count(*) FROM sales_consumerwinner a WHERE "+filter_sql+" GROUP BY"+
            "(SELECT award_id from sales_activityawards WHERE id = a.award_id),award_name") 
        rs = cur.fetchall()
        for r in rs:
            awid = r[0]
            data['records'][awid] = {
                'award_name':r[1],
                'num':r[2]
            } 


    else:
        data['e'] = '数据丢失' 

    return JsonResponse(data)


    
# 核销状态
@csrf_exempt
def wx_consumer_winner_hexiao_record(request):
    data = {
        'e':'',
        'is_hexiao':False
    }
    winner_id = request.GET.get('winner_id', '')
    company_id = request.GET.get('company_id', '')
    if winner_id and company_id:
        winner_id = decrypt_p(winner_id)
        #print(winner_id)
        c = ConsumerWinner.objects.filter(id=int(winner_id), company_id=int(company_id))
        if c:
            c = c[0]
            data['is_hexiao'] = c.is_hexiao
        else:
            data['e'] = '无数据'
    else:
        data['e'] = '数据丢失'
    return  JsonResponse(data)



'''
#2020-11-18 通用标签查询接口
def ajax_label_scan(request,company_id,scan_type):
    scan_types={
        'image':1,          #防伪查询
        'consumer':2,       #消费者扫码
        'inspector':3,      #市场稽查
        'tj':4,             #内部查询
    }
    data={'e':'','label_info':{},'image':{},'lastone':{}}
    if scan_type not in scan_types:
        data['e']='type of scan not existed'
        return JsonResponse(data)
    r=json.loads(request.body.decode('utf-8'))
    label_code=r['label_code']    
    lng_lat=r['lng_lat']    
    
    #2020-11-18 考虑今后消费者ID加密
    consumer_id=r['consumer_id']
    
    pos_permission=False
    province=None
    city=None
    address=None
    if lng_lat:
        pos_permission=True     
        lng_lat=gcj02_to_bd09(float(lng_lat[0]),float(lng_lat[1]))
        headers={'User-Agent':'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/63.0.3239.132 Safari/537.36 QIHU 360SE'}
        res=requests.get(settings.BD_URL+"&location="+str(lng_lat[1])+","+str(lng_lat[0])+"&output=json",headers=headers)
        p=json.loads(res.text)   
        if p["status"]==0:
            province=p['result']['addressComponent']['province']
            city=p['result']['addressComponent']['city']
            address=p['result']['formatted_address']
  
    if not label_code:
        data['e']='labe code missing'
    else:
        #是否返回标签详细信息        
        if 'label_info' in r:
            if r['label_info']:
                data['label_info']=label_all_info(int(company_id),label_code)            

        if scan_type=='image':             
            record=request.GET.get('record','1')             
            history=request.GET.get('history','1')            
            
            uid='pg_'+str(consumer_id)
           
            if request.META.get('HTTP_X_FORWARDED_FOR'):
                ip = request.META.get("HTTP_X_FORWARDED_FOR")
            else:
                ip = request.META.get("REMOTE_ADDR")
            
            params['fwcode']=fwcode
            params['uid']=uid
            params['record']=1
            params['history']=1
            r=requests.get('http://www.china315net.com:35207/tjfw/get/',params)
            if r.status_code==200:
                d=r.json()
                data['img']="data:image/png;base64,"+d['img']
                data['cnt']=d['counter']['net']
                if d['history']:
                    data['tm_1st']=d['history'][0]['tm'] 
                else:
                    data['tm_1st']=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')

                #写入IMAGE QUERY   
                #if company_id:
                #    company_id=int(company_id)
                #i=LabelImageQuery(consumer_id=consumer_id,company_id=company_id,label_code=fwcode)
                #i.save()
            else:
                data['e']='retrieve image failed'          

        cur=connection.cursor()
        s="select label_id,label_pk_id,label_pkg,product_id,company_to_id,"+\
            "(select adm_ids from company_company where id=a.company_to_id),id "+\
            "from inventory_stkdetails a where "+\
            "label_pk_id=(select coalesce(parent_id,id) from label.labelinfo where code='"+label_code+"')"+\
            " and ancestor_id="+company_id+" and vtype_id=2 order by id desc limit 1"
        #f=open('d:\\test_bugsell.txt','w')
        #f.write(s+'\r\n')
        cur.execute(s)
        r=cur.fetchone()
        d=None
        label_id=None
        label_pk_id=None        
        label_pkg_id=None
        product_id=None
        agent_id=None
        stkdetails_id=None        
        if r:   
            label_id=r[0]
            label_pk_id=r[1]
            label_pkg_id=r[2]
            product_id=r[3]
            agent_id=r[4]
            stkdetails_id=r[6]
            a=[]
            if lng_lat:
                for i in r[5]:        
                    s="(select public.st_distance(public.st_geogfromtext('SRID=4326;POINT("+str(lng_lat[0])+" "+str(lng_lat[1])+")'),"+\
                        "(select geom from public.adm where id="+str(i)+")))::int"    
                    a.append(s)
                if a:      
                    s="select LEAST("+",".join(a)+")"                    
                    #f.write(s)
                    #f.close()
                    cur.execute(s)
                    r=cur.fetchone()
                    d=r[0]        
        l=LabelScan(
            consumer_id=int(consumer_id), 
            company_id = int(company_id),            
            label_code =label_code, 
            label_pkg = label_pkg_id,
            label_id = label_id,
            label_pk_id = label_pk_id,
            product_id = product_id,
            province = province,
            city = city,
            address = address,
            pos_permission = pos_permission,
            lng_lat = lng_lat
        )
        l.save()        
        if d:
            b=BugSell(
                consumer_id=consumer_id,        
                label_code=label_code, 
                label_pkg=label_pkg_id,
                agent_id=agent_id,  
                company_id=company_id,
                ancestor_id=company_id,     
                product_id=product_id,     
                stkdetails_id=stkdetails_id,        
                province=province,  
                city=city,
                address=address,        
                lng_lat=lng_lat,
                distance=d
            )
            b.save()     
    return JsonResponse(data)     
'''  


def get_rebateapplication_review(ancestor_id,user_id):
    cur=connection.cursor()
    aid=str(ancestor_id)
    uid=str(user_id)

    group_ids=[]
    cur.execute("select group_id FROM company_user_groups where user_id="+str(user_id))
    for r in cur.fetchall():
        group_ids.append(r[0])
    #1;  0;  1644;"待内勤审票"
    #2;  1;  1644;"待财务审票"
    #10; 2;  1644;"待转帐"
    #3;  3;  1644;"已转帐"
    #4;  10; 1644;"被驳回"
    #5;  20; 1644;"已作废"

    #goup_id
    #4;"系统管理员"
    #12;"销售助理"
    #18;"财务"
    #20;"出纳"
    #7;"内勤管理组"

    #业务员 #内勤
    status=''
    if 7 in group_ids or 12 in group_ids:
        status=" and status_id=1 "
    #财务
    elif 18 in group_ids:
        status=" and status_id=2 "
    else:
        status=" and false "
    
    a=[]  
    s="SELECT id,agent_name,case when level_id=2 then '' else parent_name end,amount,img_invoices,"+\
        "to_char(tm,'yyyy-mm-dd HH24:MI:SS'),"+\
        "(select name from sales_rebateclosing where id=rebateclosing_id),"+\
        "case when payment_method=1 then '微信钱包' else '银行卡' end,invoice_needed "+\
        "FROM sales_rebateapplication WHERE company_id="+aid+status +\
            "and array(select division_id from company_company where id=agent_id) <@ "+\
                "(select saldiv_ids from company_staffsaldiv where user_id="+uid+")"       
    cur.execute(s)
    for r in cur.fetchall():
        inv=[]
        objs=json.loads(r[4])
        for i in objs:
            inv.append({
                'upload_user_account':i['user_account'],
                'upload_user_name':i['user_name'],
                'upload_time':i['time'],
                'image':i['image'],      
            })
        a.append({
            "app_id":r[0],
            "agent":r[1],
            "parent":r[2],
            "amount":float(r[3]),            
            "app_time":r[5],
            "invoince":inv,
            "period":r[6],
            "method":r[7],
            "invoice_needed":r[8],
        })
    cur.close()
    return a

#2021-03-29
def set_rebateapplication_invoicecheck(ancestor_id,uid,app_id,action='confirm',comment=''):
    data={'e':''}
    cur=connection.cursor()
    aid=str(ancestor_id)
    uid=str(uid)


    app=RebateApplication.objects.get(id=int(app_id))
    cur.execute("select group_id from company_user_groups where user_id="+uid)
    gs=[]
    for r in cur.fetchall():
        gs.append(r[0])
    status_id=0
    #18;"财务" 12;"销售助理"7;"内勤管理组"
    #2;内勤审票  10;财审票

    #print(action,comment)
    
    if action=='confirm':
        if app.status_id==1 and (7 in gs or 12 in gs):
            status_id=2
        elif app.status_id==2 and 18 in gs:
            status_id=10
    else:
        if action=='reject':
            status_id=4
        elif action=='scrap':
            status_id=5   
    
    if status_id:        
        app.status_id=status_id
        l=ApplicationReviewLog(company_id=int(aid),content_type='rebateapplication', obj_id=app.id, status_id=status_id, user_id=int(uid))
        if comment:
            l.comment=comment
        l.save()        
        app.save()
    else:
        data['e']='rebateapplication status error'   

    return data



#2021-01-04
@csrf_exempt
def channel_rebate_action(request,what):
    data={'e':''}
    e=time_key_check(request,sK)
    if e:
        data['e']=e
        return JsonResponse(data)    
    cur=connection.cursor()
    #提现申请
    if what=='application':
        if request.method=='GET':
            data={'e':'','applications':[],'period':{'id':0, 'name':'','amount':0, 'dt_from':'', 'dt_to':''},'wx_wallet':[],'bankinfo':[],'wx_openids':[]}
            u_id=request.GET.get('user_id','')
            c_id=request.GET.get('company_id','')
            if not u_id or not c_id :
                data['e']='user_id/company_id missing'
                return JsonResponse(data)
            s="select ancestor_id,company_id,wxopenids from company_user where id="+u_id
            cur.execute(s)
            r=cur.fetchone()
            if not r:
                data['e']='user_id error'
                return JsonResponse(data)            
            aid=str(r[0])          
            s="select id,wx_openid,wx_nickname,wx_headimgurl from company_wxopenid where id in "+\
                "(select unnest(wxopenids) from company_user where company_id="+c_id+")"
            cur.execute(s)
            for r in cur.fetchall():                 
                data['wx_openids'].append({'id':r[0],'wx_openid':r[1],'wx_nickname':r[2],'wx_headimgurl':r[3]})           
            s="select id,name,dt_fr,dt_to,"+\
                "(select amount_topay from sales_rebatepayable where rebateclosing_id=a.id and agent_id="+c_id+"),terms::json "+\
                "from sales_rebateclosing a where company_id="+aid+" and closed_tm is null order by dt desc"            
            cur.execute(s)
            r=cur.fetchone()            
            if r:
                terms=[]
                for t in r[5]:
                    if t['checked']:
                        terms.append({'val':t['val'],'name':t['name']})
                data['period']={
                    'id':r[0],
                    'name':r[1],
                    'amount':r[4],
                    'dt_from':r[2].strftime('%Y-%m-%d'),
                    'dt_to':r[3].strftime('%Y-%m-%d'),
                    'terms':terms,
                }   
            #绑定的微信钱包           
            ws=WxWallet.objects.filter(company_id=int(c_id))
            #app_id=0
            if ws:
                w=ws[0]               
                data['wx_wallet'].append({
                    'id':w.id,                    
                    'wxopenid_id':w.wxopenid.id,
                    'wx_nickname':w.wxopenid.wx_nickname,                    
                    'wx_headimgurl':w.wxopenid.wx_headimgurl,
                    'realname':w.realname,
                    'cellphone':w.cellphone,                    
                })
            #绑定的银行卡
            for b in BankInfo.objects.filter(company_id=int(c_id)):
                data['bankinfo'].append({'id':b.id,'bank_name':b.bank_name,'bank_account':b.bank_account,'payee':b.payee})
            
            #apps=RebateApplication.objects.filter(rebateclosing=r[0],agent_id=int(c_id)).order_by('dt')
            #2021-05-211
            apps=RebateApplication.objects.filter(agent_id=int(c_id)).order_by('dt')
            for a in apps:                
                inv=[]
                for i in a.img_invoices:                   
                    inv.append({
                        'time':i['time'],
                        'image':i['image'],
                        'user_name':i['user_name'],
                        'user_account':i['user_account'],                        
                    })
                wx_wallet={}
                bankinfo={}
                method=a.payment_method
                if method==1:
                    if a.wxwallet:
                        wx_wallet={'id':a.wxwallet.id,'wx_nickname':a.wxwallet.wxopenid.wx_nickname,'wx_headimgurl':a.wxwallet.wxopenid.wx_headimgurl,'realname':a.wxwallet.realname}
                    else:
                        wx_wallet={'id':0,'wx_nickname':'','wx_headimgurl':'','realname':''}
                else:
                    if a.bankinfo:
                        bankinfo={'id':a.bankinfo.id,'bank_name':a.bankinfo.bank_name,'bank_account':a.bankinfo.bank_account,'payee':a.bankinfo.payee}
                    else:
                        bankinfo={'id':0,'bank_name':'','bank_account':'','payee':''}
                
                #2021-05-19
                reason=''
                s="select coalesce(comment,'') from company_applicationreviewlog where content_type='rebateapplication' "+\
                    "and obj_id="+str(a.id)+" and status_id="+str(a.status.id)+"  order by id desc limit 1"   
                cur.execute(s)
                r=cur.fetchone()
                if r:
                    reason=r[0]
                data['applications'].append({
                    'period_id':a.rebateclosing_id,
                    'payment_method':a.payment_method,
                    'id':a.id,                    
                    'time':a.tm.strftime('%Y-%m-%d %H:%M:%S'),
                    'code':a.code,
                    'wx_nickname':a.wx_nickname,
                    'wx_headimgurl':a.wx_headimgurl,
                    'wx_wallet':wx_wallet,
                    'bankinfo':bankinfo,                    
                    'amount':a.amount,
                    'amount_vat':a.amount_vat,
                    'amount_total':a.amount_total,
                    'invoices':inv,
                    'invoice_needed':a.invoice_needed,
                    'status':{
                        'id':a.status.id,
                        'sn':a.status.sn,
                        'name':a.status.name,
                        'reason':reason,
                    }
                })   
        elif request.method=='POST':
            data={'e':''}
            r = json.loads(request.body)
            
            uid=r['user_id']
            us=User.objects.filter(id=int(uid))
            if not us:
                data['e']='user_id error'
                return JsonResponse(data)
            u=us[0]
            if u.company_id!=int(r['company_id']):
                data['e']='user_id/company error'
                return JsonResponse(data) 
            
            #2021-02-21
            wxwallet_id=None
            bankinfo_id=None

            invoice_needed=1
            if 'invoice_needed' in r:
                invoice_needed=int(r['invoice_needed'])
            if 'payment_method' not in r:
                data['e']='payment method missing'
                return JsonResponse(data)
            e=[]
            if int(r['payment_method'])==1:
                if 'wx_wallet_id' not in r:
                    e.append('wxwallet id missing')
                else:
                    wxwallet_id=int(r['wx_wallet_id'])
            elif int(r['payment_method'])==2:
                if 'bankinfo_id' not in r:
                    e.append('bankinfo id missing')
                else:
                    bankinfo_id=int(r['bankinfo_id'])
            else:
                e.append('payment method err')
            if e:
                data['e']=','.join(e)
                return JsonResponse(data)

            code=None
            if 'code' in r:
                code=r['code']  

            wx_openid=None
            wx_nickname =None
            wx_headimgurl =None
            if 'wx_openid' in r:
                wx_openid=r['wx_openid']
            if 'wx_nickname' in r:
                wx_nickname=r['wx_nickname']
            if 'wx_headimgurl' in r:
                wx_headimgurl=r['wx_headimgurl']
            #提现限额检查
            ps=RebatePayable.objects.filter(agent_id=u.company_id,rebateclosing_id=int(r['period_id']))
            if not ps:
                data['e']='本期没有可提现额度'
                return JsonResponse(data)
            p=ps[0]
            amt=float(r['amount'])


            amt_vat=None
            rate_vat=None
            amt_total=None
            if 'amount_vat' in r:
                amt_vat=float(r['amount_vat'])
            if 'amount_total' in r:
                amt_total=float(r['amount_total'])
            if 'rate_vat' in r:
                rate_vat=r['rate_vat']
            if amt<0.0:
                data['e']='申请提现不能为负数'
                return JsonResponse(data)

            id=None
            if 'id' in r:
                id=int(r['id'])


            m=0.0
            for a in RebateApplication.objects.filter(agent_id=u.company_id,rebateclosing_id=int(r['period_id'])):
                if a.status.id not in [4,5]:
                    m=float(m)+float(a.amount)
            if amt>float(p.amount_topay)-float(m):
                data['e']='提现申请金额已超过本期应付款金额'
                return JsonResponse(data) 
            #rate_vat=models.SmallIntegerField(_('税率'),blank=True,null=True)
            #amount_vat=models.DecimalField(_('税费'),max_digits=11,decimal_places=2,blank=True,null=True)
            #amount_total=models.DecimalField(_('金额合计'),max_digits=12,decimal_places=2,blank=True,null=True)

            



            #2021-04-22
            if id:
                objs=RebateApplication.objects.filter(id=id)
                if objs:
                    a=objs[0]
                    a.status_id=1
                else:
                    data['e']='id not exists'
                    return JsonResponse(data)
            else:
                a=RebateApplication()

            a.code=code
            a.rebateclosing_id=int(r['period_id'])
            a.amount=amt    
            a.user_id=u.id
            a.company_id=u.company.ancestor_id
            a.agent_id=u.company_id
            a.agent_name=u.company.name
            a.parent_name=u.company.parent.name
            a.level_id=u.company.level_id                 
            a.wx_openid=wx_openid
            a.wx_nickname =wx_nickname
            a.wx_headimgurl =wx_headimgurl                         
            a.payment_method=int(r['payment_method'])
            a.wxwallet_id=wxwallet_id
            a.bankinfo_id=bankinfo_id
            a.invoice_needed=invoice_needed                
            a.rate_vat=rate_vat
            a.amount_vat=amt_vat
            a.amount_total=amt_total

            #a=RebateApplication(        
            #    code=code,
            #    rebateclosing_id=int(r['period_id']),
            #    amount=amt,      
            #    user_id=u.id,
            #    company_id=u.company.ancestor_id,
            #    agent_id=u.company_id,
            #    agent_name=u.company.name,
            #    parent_name=u.company.parent.name,
            #    level_id=u.company.level_id,   

            #    #2021-02-21
            #    wx_openid=wx_openid,
            #    wx_nickname =wx_nickname, 
            #    wx_headimgurl =wx_headimgurl,   
            #    #wxopenid_id=w.wxopenid.id,  
            #    #payee_name=w.realname,                
            #    payment_method=int(r['payment_method']),
            #    wxwallet_id=wxwallet_id,
            #    bankinfo_id=bankinfo_id,
            #    invoice_needed=invoice_needed,
            #    #2021-04-02
            #    rate_vat=rate_vat,
            #    amount_vat=amt_vat,
            #    amount_total=amt_total
            #)

            #2021-04-09
            if a.invoice_needed==0 and a.amount_total==0:
                a.amount_total=a.amount
            
            a.save()
            data['id']=a.id
    elif what=='uploadinvoice':   
        img=request.FILES.get('file','')
        if not img:   
            data['e']='未上传图片file'
            return JsonResponse(data)
        id=request.POST.get('id','')
        user_id=request.POST.get('user_id','')
        if not id or not user_id:        
            data['e']='user id or application id missing'
            return JsonResponse(data)
        us=User.objects.filter(id=int(user_id))
        if not us:
            data['e']='user id error'
            return JsonResponse(data)
        u=us[0]        
        apps=RebateApplication.objects.filter(id=int(id))
        if not apps:
            data['e']='application id err'
            return JsonResponse(data)
        a=apps[0]
        rt='F:\\datacenter\\media\\'
        p=str(a.company_id)+'\\invoice\\'+a.rebateclosing.name
        dr=rt+p
        if not os.path.exists(dr):
            os.makedirs(dr) 
        m2=hashlib.md5(str(datetime.datetime.now()).encode('utf8'))
        fn=str(a.agent_id)+'_'+m2.hexdigest()+'.'+img.name.split('.')[-1]         
        with open(dr +'\\'+ fn, 'wb+') as destination:
            for chunk in img.chunks():
                destination.write(chunk)        
        a.img_invoices.append({
            'user_account':u.username,
            'user_name':u.last_name,
            'time':datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
            'image':'/media/'+p.replace('\\','/')+'/'+fn
        }) 

        #a.status_id=2

        a.save()
    elif what=='deliveryaddress':
        r = json.loads(request.body)
        u_id=r['user_id']
        c_id=r['company_id']
        add=r['address']     
        id=None
        if 'id' in r:
            id=int(r['id'])
        if id:            
            ds=DeliveryAddress.objects.filter(id=id)
            if not ds:
                data['e']='id error'
                return JsonResponse(data)
            else:
                d=ds[0]
        else:
            u=User.objects.get(id=int(u_id))  
            d=DeliveryAddress(ancestor_id=u.company.ancestor_id,company_id=int(c_id))
            ds=DeliveryAddress.objects.filter(company_id=int(c_id))
            if not ds:
                d.is_default=True              
        d.address=add
        if 'cellphone' in r:
            d.cellphone=r['cellphone']
        if 'receiver' in r:
            d.receiver=r['receiver']
        d.save()
    elif what=='setdefault':
        r = json.loads(request.body)        
        e=[]
        if 'content_type' not in r:
            e.append('content_type missing')
        if 'id' not in r:
            e.append('id missing')
        if e:
            data['e']=','.join(e)
            return JsonResponse(data)
        c=r['content_type'] #deliveryaddress,bankinfo
        a='company'
        if 'app_name' in r:
            a=r['app_name']        
        m=djapps.get_model(a, r['content_type'])
        objs=m.objects.filter(id=int(r['id']))
        if objs:
            o=objs[0]
            for obj in m.objects.filter(company_id=o.company_id):
                obj.is_default=False       
                obj.save()
            o.is_default=True
            o.save()
        else:
            data['e']="content_type:"+r['content_type']+" id="+str(r['id'])+" not exists"
    elif what=='reviewinvoice':
        data={'e':''}
        uid=request.GET.get('user_id','')
        if not uid:
            data['e']='user id missing'
            return JsonResponse(data)
        us=User.objects.filter(id=int(uid))
        if not us:
            data['e']='user id error'
            return JsonResponse(data)
        u=us[0]
        d=get_rebateapplication_review(u.ancestor_id,u.id)        
        data['data']=d
    elif what=='invoice_check':
        data={'e':''}
        uid=request.POST.get('user_id','')
        if not uid:
            data['e']='user id missing'
            return JsonResponse(data)
        us=User.objects.filter(id=int(uid))
        if not us:
            data['e']='user id error'
            return JsonResponse(data)
        app_id=request.POST.get('app_id','')
        if not app_id:
            data['e']='rebateapplication id missing'
            return JsonResponse(data)
        u=us[0]
        apps=RebateApplication.objects.filter(id=int(app_id))
        if not apps:
            data['e']='rebateapplication id err'
            return JsonResponse(data)
        action=request.POST.get('action','confirm')
        comment=request.POST.get('comment','')
        d=set_rebateapplication_invoicecheck(u.ancestor_id,uid,app_id,action,comment)        
        data=d   


    return JsonResponse(data)



@csrf_exempt
def test_send_hb(request):
    openid = u'oMWLQjl_lPPSdhL4yrwrH0TEwm1M'
    money = 100
    company_id = 19257
    data = {
        'e':''
    }

    r = requests.get(url='http://tjfnew.china315net.com/api/hb/sendHb.ashx',
            params={
                'openid': openid,
                'cpid': '888866662222',
                'num': money,
                     })

    if r.status_code == requests.codes.ok:
        msg = r.json()['reason']
        data['e']=msg
    return JsonResponse(data)


def send_hb(openid,money,company_id):
    """
    向个人用户发红包
    """

    if str(company_id) == "19318":
       return send_hb_sxtb(openid,money)
    if str(company_id) == "19257":
       return send_hb_mjh(openid,money) 

    wp = Pay.objects.filter(company_id=int(company_id))
    if wp:
        wp = wp[0]
        wx_app_id = wp.appid
        wx_mch_id = wp.pay_id
        wx_mch_key = wp.key
        path = wp.cert_path
        send_name = wp.remark 

        wx_pay = WxPay(
            wx_app_id=wx_app_id,
            wx_mch_id=wx_mch_id,
            wx_mch_key=wx_mch_key,
            wx_notify_url=u''
        )
        if str(company_id) == "30371": 
            err = wx_pay.enterprise_payment(
                api_cert_path=path+'apiclient_cert.pem',
                api_key_path=path+'apiclient_key.pem',
                openid=openid,
                check_name=False,
                amount=int(money),
                desc=u"壹支好酉扫码回馈",
                spbill_create_ip=u'116.62.122.204'
            ) 
            return err
        else: 
            err = wx_pay.send_red_pack(
                # 证书获取方法请阅读：https://pay.weixin.qq.com/wiki/doc/api/tools/cash_coupon.php?chapter=4_3
                # api_cert_path: 微信支付商户证书（apiclient_cert.pem）的本地保存路径
                api_cert_path=path+'apiclient_cert.pem',
                # api_cert_path: 微信支付商户证书（apiclient_key.pem）的本地保存路径
                api_key_path=path+'apiclient_key.pem',
                send_name=send_name,  # 红包名称
                re_openid=openid,  # 要接收红包的用户openid
                total_amount=money,  # total_fee 单位是 分， 100 = 1元, 最大499元
                wishing=u'感谢参与',  # 祝福语
                client_ip=u'116.62.122.204',  # 调用微信发红包接口服务器公网IP地址
                act_name=u'感恩回馈',  # 活动名称
                scene_id='PRODUCT_1',
                remark=u'感谢参与'  # 备注
            )
            return err
    err = {
        'return_code':'fail'
    }
    return err
    
    
    
def send_hb_qz(openid,money,wishing=u'感谢参与'):
    """
    向个人用户发红包
    """
    wx_pay = WxPay(
        wx_app_id=u'wxf7fe8ffd6613fc71',
        wx_mch_id=u'1430842802',
        wx_mch_key=u'3V82SfwnjuJQh72YJb4fWzaNqeDQiz1i',
        wx_notify_url=u''
    )
    path = r"F:\datacenter\wx\cert\qz\\" 

    err = wx_pay.send_red_pack(
        # 证书获取方法请阅读：https://pay.weixin.qq.com/wiki/doc/api/tools/cash_coupon.php?chapter=4_3
        # api_cert_path: 微信支付商户证书（apiclient_cert.pem）的本地保存路径
        api_cert_path=path+'apiclient_cert.pem',
        # api_cert_path: 微信支付商户证书（apiclient_key.pem）的本地保存路径
        api_key_path=path+'apiclient_key.pem',
        send_name=u'求真酒业',  # 红包名称
        re_openid=openid,  # 要接收红包的用户openid
        total_amount=int(money),  # total_fee 单位是 分， 100 = 1元, 最大499元
        wishing=wishing,  # 祝福语
        client_ip=u'116.62.122.204',  # 调用微信发红包接口服务器公网IP地址
        act_name=u'感恩回馈',  # 活动名称,
        scene_id='PRODUCT_2',
        remark=u'感谢参与'  # 备注
    )
    return err
def send_hb_qz_enterprise(openid,money):
    """
    向个人用户发红包
    """
    wx_pay = WxPay(
        wx_app_id=u'wxf7fe8ffd6613fc71',
        wx_mch_id=u'1430842802',
        wx_mch_key=u'3V82SfwnjuJQh72YJb4fWzaNqeDQiz1i',
        wx_notify_url=u''
    )
    path = r"F:\datacenter\wx\cert\qz\\" 

    err = wx_pay.enterprise_payment(
        api_cert_path=path+'apiclient_cert.pem',
        api_key_path=path+'apiclient_key.pem', 
        openid=openid, 
        amount=int(money), 
        desc=u'感谢参与',
        spbill_create_ip=u'116.62.122.204',
        check_name=False,
    )
    return err
def test_send_hb_qz(request):
    openid = "oE1IM5_O32l4UPdvufOcGseAW0QQ"
    money = 1
    err = send_hb_qz_enterprise(openid, money)
    return JsonResponse(err)

def send_hb_mjh(openid,money):
    """
    向个人用户发红包
    """
    wx_pay = WxPay(
        wx_app_id=u'wx91b0412e32bf998b',
        wx_mch_id=u'1494684242',
        wx_mch_key=u'E0LB6lR4eO6du4Wj0cMttyF9y0eH7gE7',
        wx_notify_url=u''
    )
    path = r"F:\datacenter\wx\cert\mjh\\" 

    err = wx_pay.send_red_pack(
        # 证书获取方法请阅读：https://pay.weixin.qq.com/wiki/doc/api/tools/cash_coupon.php?chapter=4_3
        # api_cert_path: 微信支付商户证书（apiclient_cert.pem）的本地保存路径
        api_cert_path=path+'apiclient_cert.pem',
        # api_cert_path: 微信支付商户证书（apiclient_key.pem）的本地保存路径
        api_key_path=path+'apiclient_key.pem',
        send_name=u'茗酒汇',  # 红包名称
        re_openid=openid,  # 要接收红包的用户openid
        total_amount=int(money),  # total_fee 单位是 分， 100 = 1元, 最大499元
        wishing=u'感谢参与',  # 祝福语
        client_ip=u'116.62.122.204',  # 调用微信发红包接口服务器公网IP地址
        act_name=u'感恩回馈',  # 活动名称,
        scene_id='PRODUCT_1',
        remark=u'感谢参与'  # 备注
    )
    return err


@csrf_exempt
def test_send_qz(request):
    #res=send_hb_sxtb('oh_9g0sFdwfInjKWZrT8ODpFCPKI',102)
    res=send_hb('oMWLQjl_lPPSdhL4yrwrH0TEwm1M',102,18801)
    return JsonResponse(res)


    
def send_hb_sxtb(openid,money):
    """
    向个人用户发红包
    """ 
    wx_pay = WxPay(
        wx_app_id=u'wx129b3a5296d66505',
        wx_mch_id=u'1494798302',
        wx_mch_key=u'OEXLrGalrIvyAuUvcm8DXMo87yFZuHQC',
        wx_notify_url=u''
    )
    path = r"F:\datacenter\wx\cert\sxtb\\" 

    # err = wx_pay.enterprise_payment(
    #     api_cert_path=path+'apiclient_cert.pem',
    #     api_key_path=path+'apiclient_key.pem',
    #     openid=openid,
    #     check_name=False,
    #     amount=int(money),
    #     desc=u"诗仙太白扫码活动",
    #     spbill_create_ip=u'116.62.122.204'
    # )

    err = wx_pay.send_red_pack(
        # 证书获取方法请阅读：https://pay.weixin.qq.com/wiki/doc/api/tools/cash_coupon.php?chapter=4_3
        # api_cert_path: 微信支付商户证书（apiclient_cert.pem）的本地保存路径
        api_cert_path=path+'apiclient_cert.pem',
        # api_cert_path: 微信支付商户证书（apiclient_key.pem）的本地保存路径
        api_key_path=path+'apiclient_key.pem',
        send_name=u'诗仙太白',  # 红包名称
        re_openid=openid,  # 要接收红包的用户openid
        total_amount=int(money),  # total_fee 单位是 分， 100 = 1元, 最大499元
        wishing=u'感谢参与',  # 祝福语
        client_ip=u'116.62.122.204',  # 调用微信发红包接口服务器公网IP地址
        act_name=u'感恩回馈',  # 活动名称,
        scene_id='PRODUCT_1',
        remark=u'感谢参与'  # 备注
    )
    return err


def test_celery_task(request):
     from wx.task import add
     add.delay(2, 2)
     return JsonResponse({'e':''})


@csrf_exempt
def wxwallet_application(request):
    data={'e':''}   
    e=time_key_check(request,sK)
    if e:
        data['e']=e
        return JsonResponse(data)   
    r=json.loads(request.body.decode('utf-8'))     
    
    app_company_id=r['app_company_id']
    app_user_id=r['app_user_id']
    app_nickname=r['app_nickname']
    app_headimgurl=r['app_headimgurl']   
    
    bank_name=None
    bank_account=None
    priority=1

    app_id=None
    if 'bank_name' in r:
        bank_name=r['bank_name']
    if 'bank_account' in r:
        bank_account=r['bank_account']
    if 'priority' in r:
        priority=int(r['priority'])
    
    if 'app_id' in r:
        app_id=int(r['app_id'])
    agt_id=r['agent_id']    
    cell=r['cellphone']
    realname=r['realname']
    agents=Company.objects.filter(id=int(agt_id))
    if not agents:
        data['e']='agent id error'
        return JsonResponse(data)
    openid=r['openid']
    app_openid=r['app_openid']  

    objs=WxOpenid.objects.filter(wx_openid=openid)
    if not objs:
        o=WxOpenid(wx_openid=openid,company=u.company.ancestor_id,wx_nickname=r['app_nickname'],wx_headimgurl=['app_headimgurl'])
        o.save()
    else:
        o=objs[0]  
    wxopen_id=o.id
    

    objs=WxOpenid.objects.filter(wx_openid=app_openid)
    if not objs:
        o=WxOpenid(wx_openid=app_openid,company=u.company.ancestor_id,wx_nickname=r['app_nickname'],wx_headimgurl=['app_headimgurl'])
        o.save()
    else:
        o=objs[0]  
    app_openid_id=o.id

    cur=connection.cursor()

    s="select (select name from company_company where id=company_id),to_char(tm,'yyyy-mm-dd HH24:MI:SS')"+\
        " from company_wxwalletapplication where company_id="+str(agt_id)+" and status_id not in (6,5)"
    cur.execute(s)
    r=cur.fetchone()
    if r:
        data['e']=r[0]+" 微信钱包申请("+r[1]+")还在处理当中，请不要重复申请"
        return JsonResponse(data)   

    #2021-01-25
    agent=agents[0]
    wallets=WxWallet.objects.filter(company_id=int(agt_id))
    if wallets:
        wallet=wallets[0]
        #wallet.status_id=4
    else:
        wallet=WxWallet(ancestor_id=agent.ancestor_id, company_id = agent.id)    
    wallet.wxopenid_id=int(wxopen_id)
    wallet.cellphone=cell
    wallet.realname=realname 
    wallet.bank_name=bank_name
    wallet.bank_account=bank_account
    wallet.save()
    if app_id:
        app=WxWalletApplication.objects.get(id=int(app_id))
        app.status_id=4
        app.tm=datetime.datetime.now()
        app.dt=datetime.datetime.now()
        #删除已有日志?   
        for l in ApplicationReviewLog.objects.filter(content_type='wxwalletapplication',obj_id=int(app_id)):
            l.delete()
    else:
        app=WxWalletApplication()
        app.status_id=1
    app.ancestor_id=agent.ancestor_id
    app.wxwallet_id=wallet.id
    app.app_company_id=app_company_id
    app.app_user_id=app_user_id
    app.app_wxopenid_id=app_openid_id
    app.app_nickname = app_nickname
    app.app_headimgurl = app_headimgurl
    app.wxopenid_id=wxopen_id
    app.company_id=agent.id
    app.cellphone=cell
    app.realname=realname                           
    app.priority=priority      
    app.bank_name=bank_name
    app.bank_account=bank_account    
    app.save()
    return JsonResponse(data)



# 2021-01-23
def wx_consumer_activity_card_compose(request): 
    data = {
        "e": "" 
    }
    consumer_id = request.GET.get("consumer_id", "") 
    company_id = request.GET.get("company_id", "")
    award_cat = '卡券'
    if consumer_id and company_id:
        if company_id == "19318":  
            consumer_id = decrypt_p(consumer_id) 
            cur = connection.cursor() 
            cur.execute("SELECT original_award_id,count(*) from sales_consumerwinner where company_id = "+company_id+
            " and award_cat = '卡券' and tm_used is null and consumer_id = "+consumer_id+" GROUP BY original_award_id")
            rows_affected=cur.rowcount 
            if rows_affected == 8: 
                cur.execute("UPDATE sales_consumerwinner set tm_used=now() where id in ( "+
	                "SELECT min(id) from sales_consumerwinner "+
                    "where company_id = "+company_id+" and consumer_id = "+consumer_id+" and award_cat = '卡券' and tm_used is null group by  original_award_id)")
                
                activity_award = ActivityAwards.objects.get(id=62)
                activity = activity_award.activity

                award = activity_award.award
                w = ConsumerWinner(
                        company_id=int(company_id),
                        consumer_id=consumer_id,  
                        activity_id=activity.id,
                        award_id=activity_award.id,
                        activity_name=activity.name,
                        award_name=award.name,
                        award_points=award.points,
                        award_amount=award.amount,
                        award_img=award.photo,
                        award_cat=award.awardcat.name,
                        is_need_hexiao=award.is_need_hexiao
                    )
                w.save()
            else:
                data['e'] = '卡片不足够'  
    else:
        data['e'] = '数据丢失' 

    return JsonResponse(data)

#分享奖品
def wx_consumer_award_share(request): 
    data = {
        "e": "" 
    }
    consumer_id = request.GET.get("consumer_id", "") 
    company_id = request.GET.get("company_id", "")
    id = request.GET.get("id", "")
    if consumer_id and company_id and id:
        if company_id == "19318":  
            consumer_id = decrypt_p(consumer_id) 
            id = decrypt_p(id)
            w = ConsumerWinner.objects.filter(id=int(id),consumer_id=int(consumer_id))
            if w:
                w = w[0]
                w.is_sharing = True 
                w.save()
            else:
                data['e'] = '数据异常'
              
    else:
        data['e'] = '数据丢失' 

    return JsonResponse(data)





#2021-01-26
def get_notifications(request):
    data={'e':'','notifications':[]}
    e=time_key_check(request,sK)
    if e:
        data['e']=e
        return JsonResponse(data)  
    uid=request.GET.get('user_id','0')
    if not uid:
        data['e']='user id missing'
        return JsonResponse(data)
    aid=0
    cur=connection.cursor() 
    s="select u.ancestor_id,w.wxopenid_id from "+\
        "(select username,company_id,ancestor_id from company_user where id="+uid+") u "+\
        "left outer join "+\
        "(select ancestor_id,company_id,wxopenid_id from company_wxwallet ) w "+\
        "on u.company_id=w.company_id"
    cur.execute(s)
    r=cur.fetchone()
    if not r:
        data['e']='user id error'
    else:
        aid=r[0]
        #内参公司检查当前经销是否已绑定微信钱包
        if aid==1644:
            if not r[1]:
                data['notifications'].append({
                    'obj':'WxWallet',
                    'notification':'系统检测到您的账号未绑定微信钱包或银行账号，请前往绑定。'
                })  
    s="select id,subject,content,tm, category "+\
        "FROM company_bulletinboard b where category=2 and company_id=(select ancestor_id from company_user where id="+uid+") "+\
        " and (select id from company_bbsreader where bulletinboard_id=b.id and user_id="+uid+") is null" 
    
    s="select b.id,b.subject,coalesce(b.content,''),to_char(b.tm,'yyyy-mm-dd HH24:MI:SS'),coalesce(to_char(r.tm,'yyyy-mm-dd HH24:MI:SS'),'') from "+\
        "(select id,subject,content,tm FROM company_bulletinboard where category=2 "+\
            "and company_id=(select ancestor_id from company_user where id="+uid+")) b "+\
        "left outer join "+\
        "(select id,bulletinboard_id,tm from company_bbsreader where user_id="+uid+") r "+\
        "on b.id=r.bulletinboard_id"


    cur.execute(s)
    b=[]
    for r in cur.fetchall():        
        b.append({
            'id':r[0],
            'subject':r[1],
            'content':r[2],
            'time_create':r[3],
            'time_read':r[4],
        })
    data['bubulletinboard_sys']=b

    #2021-03-26待审单据 数量     
    s="SELECT count(*) FROM sales_rebateapplication WHERE company_id="+str(aid)+" and status_id=1 "+\
        "and invoice_needed=1 and array(select division_id from company_company where id=agent_id) <@ "+\
            "(select saldiv_ids from company_staffsaldiv where user_id="+uid+")"    
    cur.execute(s)
    r=cur.fetchone()    
    data['rebate_applications']=r[0]  
    
    cur.close()
    return JsonResponse(data)



# @csrf_exempt
# def temp_update(request):
#     brs = ActivityRebate_BonusRecord.objects.all()
#     bottles_list = []
#     counter = 0
#     for i in brs:
#         company = Company.objects.get(id = 20445)
#         labelInfo = label_sys_info(i.labelcode,company,has_price=False)
#         product_id = labelInfo['product']['id']
#         bottles_list.append(labelInfo['bottles'])
#         i.product = Product.objects.get(id=product_id)
#         #i.save()
#         counter += 1
#     return JsonResponse({'counter':counter, 'all':len(brs),'bottle':bottles_list})

def qz_anniversary_rebate(product_id, number, consumer_id, lbcode, openid):
    aw = ''
    if i == QZ_PRODUCT_3_YEARS and summaryByProduct[i] == 100:
        aw = ActivityAwards.objects.get(id=QZ_ACTIVITY_AWARD_ANNI_100_3_YEARS)
    if i == QZ_PRODUCT_3_YEARS and summaryByProduct[i] == 1000:
        aw = ActivityAwards.objects.get(id=QZ_ACTIVITY_AWARD_ANNI_1000_3_YEARS)
    if i == QZ_PRODUCT_3_YEARS and summaryByProduct[i] == 10000:
        aw = ActivityAwards.objects.get(id=QZ_ACTIVITY_AWARD_ANNI_10000_3_YEARS)
    if i == QZ_PRODUCT_6_YEARS and summaryByProduct[i] == 100:
        aw = ActivityAwards.objects.get(id=QZ_ACTIVITY_AWARD_ANNI_100_6_YEARS)
    if i == QZ_PRODUCT_6_YEARS and summaryByProduct[i] == 1000:
        aw = ActivityAwards.objects.get(id=QZ_ACTIVITY_AWARD_ANNI_1000_6_YEARS)
    if i == QZ_PRODUCT_6_YEARS and summaryByProduct[i] == 10000:
        aw = ActivityAwards.objects.get(id=QZ_ACTIVITY_AWARD_ANNI_10000_6_YEARS)
    if i == QZ_PRODUCT_ZUNPIN and summaryByProduct[i] == 100:
        aw = ActivityAwards.objects.get(id=QZ_ACTIVITY_AWARD_ANNI_100_ZUNPIN)
    if i == QZ_PRODUCT_ZUNPIN and summaryByProduct[i] == 1000:
        aw = ActivityAwards.objects.get(id=QZ_ACTIVITY_AWARD_ANNI_1000_ZUNPIN)
    if i == QZ_PRODUCT_JIAPIN_RED and summaryByProduct[i] == 100:
        aw = ActivityAwards.objects.get(id=QZ_ACTIVITY_AWARD_ANNI_100_JIAPIN_RED)
    if i == QZ_PRODUCT_JIAPIN_RED and summaryByProduct[i] == 1000:
        aw = ActivityAwards.objects.get(id=QZ_ACTIVITY_AWARD_ANNI_1000_JIAPIN_RED)
    if i == QZ_PRODUCT_JINGPIN_BLUE and summaryByProduct[i] == 100:
        aw = ActivityAwards.objects.get(id=QZ_ACTIVITY_AWARD_ANNI_100_JINGPIN_BLUE)
    if i == QZ_PRODUCT_JINGPIN_BLUE and summaryByProduct[i] == 1000:
        aw = ActivityAwards.objects.get(id=QZ_ACTIVITY_AWARD_ANNI_1000_JINGPIN_BLUE)
    if i == QZ_PRODUCT_ZHENPIN_BLACK and summaryByProduct[i] == 100:
        aw = ActivityAwards.objects.get(id=QZ_ACTIVITY_AWARD_ANNI_100_ZHENPIN_BLACK) 

    tm = datetime.datetime.now()
    product = Product.objects.get(id=product_id)

    c_win_record = ConsumerWinner(
                            company_id=20445,
                            consumer_id=consumer_id,
                            activity_id=QZ_ACTIVITY_ID,
                            award=aw,
                            tm=tm
                        )
    c_send_record = ActivityRebate_BonusRecord(
                            activity_id=29,
                            company_id_id=20445,
                            consumer_id=consumer_id,
                            labelcode=lbcode,
                            product=product,
                            operation=QZ_OPERATION_REDPACK_SHARE,
                            amount= aw.award.amount
                        )
    c_win_record.save()
    c_send_record.save()

    openid = ConsumerAccount.objects.get(id=consumer_id).wx_openid
    amt = o_shareredpack[0].amount
    res = send_hb_qz(openid,amt*100)
    rec = ActivityRebate_RedPackSendRecord(
                activity_id=QZ_ACTIVITY_ID,
                company_id_id=20445,
                consumer_id=consumer_id,
                bonus_record=c_send_record,
                openid= openid,
                remark=res['err_code'],
                return_code=res['return_code'],
                result_code=res['result_code']
            )
    rec.save()


@csrf_exempt
def activitybonus_checkUserData(request):
    #进入页面，查询是否有可以领取的红包
    lbcode = request.GET.get('lbcode', '')
    consumer_id = request.GET.get('uid', '')
    is_share = request.GET.get('is_share', 0)

    tm = datetime.datetime.now()
    responseData = {
        'e': '',
        'redpack': '',
        'nickname': '',
        'userlogo': '',
        'parent': '',
        'children': [],
        'bonus_history': [],
        'mode': '1',
        'redpack_amount': 0,
        'series_id': QZ_PRODUCT_SERIES_HIGH,
        'package_id': ''
    }
    cur = connection.cursor()
    series_id = QZ_PRODUCT_SERIES_HIGH


    ### 通过labelcode 查询 product_id
    company = Company.objects.get(id = 20445)
    labelInfo = label_sys_info(lbcode,company,has_price=False)
    product_id = labelInfo['product']['id']
    package_id = labelInfo['package']['id']

    if not product_id:
        responseData['e'] = '标签没有指定产品'
        return JsonResponse(responseData)

    sql_product = f"select series_id from logistics.product_product where id={product_id}"
    cur.execute(sql_product)
    r = cur.fetchone()
    if r:
        series_id = r[0]
        responseData['series_id']=series_id
    if series_id == QZ_PRODUCT_SERIES_HIGH:
        responseData['mode'] = '1'
    else:
        responseData['mode'] = '2'
    
    #是否有用户?
    user = ConsumerAccount.objects.filter(id=consumer_id)
    if len(user) > 0:
        user = user.first()
        responseData['nickname'] = user.wx_nickname
        responseData['userlogo'] = user.wx_headimgurl
        userRel = ActivityRebate_Relations.objects.filter(parent_id=consumer_id)
        parent=ActivityRebate_Relations.objects.filter(consumer_id=consumer_id)
        if parent:
            responseData['parent'] = {'nickname':str(parent[0].parent),'id':str(parent[0].parent_id)}
        children = []
        children_info = []
        if len(userRel)>0:
            for i in userRel:
                children.append(i.consumer_id)
            for j in children:
                ca = ConsumerAccount.objects.get(id=j)
                children_info.append({'nickname':ca.wx_nickname,'id':j})
            responseData['children']=children_info

    else:
        responseData['e'] = '无此用户'

    sum_record = ActivityRebate_BonusRecord.objects.filter(consumer_id=consumer_id)

    # 统计信息
    sum = []
    summaryByProduct = {}
    for i in sum_record:
        sum.append({
                'tm': i.tm,
                'amount': i.amount
            })
        if i.operation == 1:
            if i.product_id not in summaryByProduct.keys():
                summaryByProduct[i.product_id] = 1
            else:
                summaryByProduct[i.product_id] += 1
    responseData['bonus_history']=sum   

    act_f = Activities.objects.filter(id=QZ_ACTIVITY_ID)
    # 用户是否满足逢整返利条件?
    '''
    if len(act_f)>0:
        act = act_f.first()
        if act.status == 1:
            sum_aw = ''
            for i in summaryByProduct.keys():
                qz_anniversary_rebate(product_id=i, number=summaryByProduct[i],consumer_id=consumer_id,
                lbcode=lbcode,openid=openid)
    '''

    # 该号码的分享红包是否已部署?
    o_shareredpack = ActivityRebate_ShareRedPack.objects.filter(labelcode=lbcode)
    if len(o_shareredpack) == 0 and int(is_share) == 0:
        if len(act_f)>0:
            act = act_f.first()
            if act.status == 1:
                if product_id==QZ_PRODUCT_3_YEARS or product_id==QZ_PRODUCT_6_YEARS:
                    if len(labelInfo['bottles']) > 0:
                        aw = ActivityAwards.objects.get(id=qz_activitybonus_product_award_relation(product_id))
                        balance = 0

                        if product_id==QZ_PRODUCT_3_YEARS:
                            balance = 6
                        if product_id==QZ_PRODUCT_6_YEARS:
                            balance = 9

                        counter = 0
                        amt_list = []
                        # 留足每个0.3的量
                        for i in labelInfo['bottles']:
                            amt = random.uniform(0.3, balance-0.3*(6-counter))
                            amt_list.append(round(amt,1))
                            counter += 1
                            balance -= amt
                        for j_idx, j_val in enumerate(amt_list):
                            srp = ActivityRebate_ShareRedPack(
                                activity_id=29,
                                labelcode=labelInfo['bottles'][j_idx],
                                company_id=company,
                                product_id=product_id,
                                amount=j_val,
                                is_redeemed=False,
                                remark=''
                            )
                            srp.save()

    # 如果是分享红包链接进入,检查是否已领取
    if len(o_shareredpack) > 0 and int(is_share) == 1:
        if len(act_f)>0:
            act = act_f.first()
            if act.status == 1:
                # 已否已兑换?
                if not o_shareredpack[0].is_redeemed:
                    responseData['redpack'] = '2'
                    responseData['redpack_amount'] = o_shareredpack[0].amount
                    return JsonResponse(responseData)
                



    #标签号码对应的红包是否已领取?
    record = ActivityRebate_BonusRecord.objects.filter(labelcode=lbcode)
    alreadyGet = False
    if len(record)>0:
        for i in record:
            if int(i.operation) == 1:
                alreadyGet = True
        if alreadyGet:
            responseData['redpack'] = '0'
    if len(record)==0 or not alreadyGet:
        #未存在已领取记录，可能有红包
        #活动是否激活? 求真红包返利活动 id=29 status: 0,1 ~ 未激活，已激活
        act_f = Activities.objects.filter(id=QZ_ACTIVITY_ID)
        if len(act_f)>0:
            act = act_f.first()
            if act.status == 1:
                #活动已激活
                aw = ActivityAwards.objects.get(id=qz_activitybonus_product_award_relation(product_id))
                responseData['redpack'] = '1'
                responseData['redpack_amount']=aw.award.amount
            else:
                #未激活
                responseData['e'] = '活动未激活'
                responseData['redpack'] = '0'
        else:
            responseData['e'] = '活动不存在'
            responseData['redpack'] = '0'
    return JsonResponse(responseData)


def qz_activitybonus_product_award_relation(product_id):
    if product_id == QZ_PRODUCT_3_YEARS:
        return QZ_ACTIVITY_AWARD_3_YEARS_100_PERCENT
    elif product_id == QZ_PRODUCT_6_YEARS:
        return QZ_ACTIVITY_AWARD_6_YEARS_100_PERCENT
    elif product_id == QZ_PRODUCT_ZUNPIN:
        return QZ_ACTIVITY_AWARD_ZUNPIN
    elif product_id == QZ_PRODUCT_JIAPIN_RED:
        return QZ_ACTIVITY_AWARD_JIAPIN_RED
    elif product_id == QZ_PRODUCT_JINGPIN_BLUE:
        return QZ_ACTIVITY_AWARD_JINGPIN_BLUE
    elif product_id == QZ_PRODUCT_ZHENPIN_BLACK:
        return QZ_ACTIVITY_AWARD_ZHENPIN_BLACK

    else:
        return QZ_ACTIVITY_AWARD_REDPACK_100_PERCENT

@csrf_exempt
def activitybonus_getBonus(request):
    responseData = {
        'e': '',
        'redpack': '',
        'success': ''
    }
    lbcode = request.GET.get('lbcode', '')
    consumer_id = request.GET.get('uid', '')
    is_share = request.GET.get('is_share', -1)
    tm = datetime.datetime.now()

    cur = connection.cursor()
    veriCode = request.GET.get('vc', '')

    ### 通过labelcode 查询 product_id
    company = Company.objects.get(id = 20445)
    labelInfo = label_sys_info(lbcode,company,has_price=False)
    product_id = labelInfo['product']['id']
    product = Product.objects.get(id=product_id)

    series_id = QZ_PRODUCT_SERIES_HIGH
    sql_product = f"select series_id from logistics.product_product where id='{product_id}'"
    cur.execute(sql_product)
    r = cur.fetchone()
    if r:
        series_id = r[0]

    if int(is_share) == 1:
        o_shareredpack = ActivityRebate_ShareRedPack.objects.filter(labelcode=lbcode)
        act_f = Activities.objects.filter(id=QZ_ACTIVITY_ID)
        if len(o_shareredpack) > 0 and int(is_share) == 1:
            if len(act_f)>0:
                act = act_f.first()
                if act.status == 1:
                    # 已否已兑换?
                    if o_shareredpack[0].is_redeemed:
                        responseData['e'] = '已兑换'
                        return JsonResponse(responseData)
                    else:
                        # 将号码标记为已兑换
                        o_shareredpack[0].is_redeemed = True
                        o_shareredpack[0].save()

                        # 写中奖记录和兑奖记录
                        c_win_record = ConsumerWinner(
                            company_id=20445,
                            consumer_id=consumer_id,
                            activity_id=QZ_ACTIVITY_ID,
                            award_id=QZ_ACTIVITY_AWARD_SHARE,
                            tm=tm
                        )
                        aw = ActivityAwards.objects.get(id=QZ_ACTIVITY_AWARD_SHARE)
                        c_send_record = ActivityRebate_BonusRecord(
                            activity_id=29,
                            company_id_id=20445,
                            consumer_id=consumer_id,
                            labelcode=lbcode,
                            product=product,
                            operation=QZ_OPERATION_REDPACK_SHARE,
                            amount= o_shareredpack[0].amount / 100
                        )
                        c_win_record.save()
                        c_send_record.save()
                        # 发送红包并记录
                        openid = ConsumerAccount.objects.get(id=consumer_id).wx_openid
                        amt = o_shareredpack[0].amount
                        res = send_hb_qz(openid,amt*100)
                        rec = ActivityRebate_RedPackSendRecord(
                                    activity_id=QZ_ACTIVITY_ID,
                                    company_id_id=20445,
                                    consumer_id=consumer_id,
                                    bonus_record=c_send_record,
                                    openid= openid,
                                    remark=res['err_code'],
                                    return_code=res['return_code'],
                                    result_code=res['result_code']
                                )
                        rec.save()
                        responseData['success'] = "1"
                        return JsonResponse(responseData)

    else:
        sql_vc = f"select vcode from label.lbl_vcode where code='{lbcode}'"
        cur.execute(sql_vc)
        r = cur.fetchone()
        if r:
            if str(r[0]) != veriCode:
                responseData['e'] = '验证码不正确'
                return JsonResponse(responseData)
        else:
            responseData['e'] = '标签号码异常'
            return JsonResponse(responseData)

        #是否有用户?
        user = ConsumerAccount.objects.filter(id=consumer_id)
        if len(user)==0:
            responseData['e'] = '无此用户'
            return JsonResponse(responseData)
        #标签号码对应的红包是否已领取?
        record = ActivityRebate_BonusRecord.objects.filter(labelcode=lbcode)
        if len(record) > 0:
            alreadyGet = False
            for i in record:
                if int(i.operation) == 1:
                    alreadyGet = True
            if alreadyGet:
                #已存在已领取记录, 没有红包
                responseData['redpack'] = '0'
                responseData['e'] = '已领取'
                return JsonResponse(responseData)
        else:
            # 未存在已领取记录，可能有红包
            # 活动是否激活? 求真红包返利活动 id=29 status: 0,1 ~ 未激活，已激活
            act_f = Activities.objects.filter(id=QZ_ACTIVITY_ID)
            if len(act_f)>0:
                act = act_f.first()
                if act.status == 1:
                    if series_id == QZ_PRODUCT_SERIES_LOW:
                        #先记录个人返利
                        c_self1 = ConsumerWinner(
                            company_id=20445,
                            consumer_id=consumer_id,
                            activity_id=QZ_ACTIVITY_ID,
                            award_id=qz_activitybonus_product_award_relation(product_id),
                            tm=tm
                        )
                        aw = ActivityAwards.objects.get(id=qz_activitybonus_product_award_relation(product_id))
                        c_self2 = ActivityRebate_BonusRecord(
                            activity_id=29,
                            company_id_id=20445,
                            consumer_id=consumer_id,
                            labelcode=lbcode,
                            product=product,
                            operation=QZ_OPERATION_REDPACK_DIRECT,
                            amount= aw.award.amount
                        )
                        c_self1.save()
                        c_self2.save()

                        openid = ConsumerAccount.objects.get(id=consumer_id).wx_openid
                        amt = aw.award.amount
                        
                        res = send_hb_qz(openid,amt*100)
                        rec = ActivityRebate_RedPackSendRecord(
                                    activity_id=QZ_ACTIVITY_ID,
                                    company_id_id=20445,
                                    consumer_id=consumer_id,
                                    bonus_record=c_self2,
                                    openid= openid,
                                    remark=res['err_code'],
                                    return_code=res['return_code'],
                                    result_code=res['result_code']
                                )
                        rec.save()
                        
                        parents = []
                        cur = connection.cursor()
                        find_id = consumer_id
                        for i in range(2):
                            sql = "select parent_id from sales_activityrebate_relations where consumer_id="+str(find_id)
                            cur.execute(sql)
                            r=cur.fetchone()
                            if not r:
                                break
                            else: 
                                parents.append(r[0])
                                find_id = r[0]

                        # 注意! 已乘100
                        direct_redpack_amount = amt * 100

                        ratio_list = []
                        if product_id == QZ_PRODUCT_3_YEARS:
                            ratio_list = QZ_PRODUCT_3_YEARS_RATIO
                        if product_id == QZ_PRODUCT_6_YEARS:
                            ratio_list = QZ_PRODUCT_6_YEARS_RATIO

                        aw_list = [QZ_ACTIVITY_AWARD_LEVEL_2, QZ_ACTIVITY_AWARD_LEVEL_3, QZ_ACTIVITY_AWARD_LEVEL_4]
                        for idx,val in enumerate(parents):
                            c_parent1 = ConsumerWinner(
                                company_id=20445,
                                consumer_id=val,
                                activity_id=QZ_ACTIVITY_ID,
                                award_id=aw_list[idx],
                                tm=tm
                            )
                            aw = ActivityAwards.objects.get(id=aw_list[idx])
                            c_parent2 = ActivityRebate_BonusRecord(
                                activity_id=29,
                                company_id_id=20445,
                                consumer_id=val,
                                labelcode=lbcode,
                                product=product,
                                operation=idx+2,
                                amount= float(direct_redpack_amount) * ratio_list[idx]
                            )
                            c_parent1.save()
                            c_parent2.save()

                            openid = ConsumerAccount.objects.get(id=val).wx_openid
                            
                            send_amount_with_100 = float(direct_redpack_amount) * ratio_list[idx]
                            res = send_hb_qz(openid, send_amount_with_100)

                            rec = ActivityRebate_RedPackSendRecord(
                                    activity_id=29,
                                    company_id_id=20445,
                                    consumer_id=val,
                                    bonus_record=c_parent2,
                                    openid= openid,
                                    remark=res['err_code'],
                                    return_code=res['return_code'],
                                    result_code=res['result_code']
                                )
                            rec.save()
                        
                        return JsonResponse(responseData)
                    else:
                        c_self1 = ConsumerWinner(
                            company_id=20445,
                            consumer_id=consumer_id,
                            activity_id=QZ_ACTIVITY_ID,
                            award_id=qz_activitybonus_product_award_relation(product_id),
                            tm=tm
                        )
                        aw = ActivityAwards.objects.get(id=qz_activitybonus_product_award_relation(product_id))
                        c_self2 = ActivityRebate_BonusRecord(
                            activity_id=QZ_ACTIVITY_ID,
                            company_id_id=20445,
                            consumer_id=consumer_id,
                            labelcode=lbcode,
                            product=product,
                            operation=QZ_OPERATION_REDPACK_DIRECT,
                            amount= aw.award.amount
                        )
                        c_self1.save()
                        c_self2.save()

                        openid = ConsumerAccount.objects.get(id=consumer_id).wx_openid
                        amt = aw.award.amount
                        res = send_hb_qz(openid,amt*100)
                        rec = ActivityRebate_RedPackSendRecord(
                                    activity_id=QZ_ACTIVITY_ID,
                                    company_id_id=20445,
                                    consumer_id=consumer_id,
                                    bonus_record=c_self2,
                                    openid= openid,
                                    remark=res['err_code'],
                                    return_code=res['return_code'],
                                    result_code=res['result_code']
                                )
                        rec.save()
                        return JsonResponse(responseData)
                else:
                    responseData['e'] = '活动未激活'
                    return JsonResponse(responseData)
            else:
                responseData['e'] = '活动不存在'
                return JsonResponse(responseData)


@csrf_exempt
def activitybonus_relationBinding(request):
    responseData = {
        'e': '',
        'consumer_id': '',
        'parent_id': ''
    }
    consumer_id = request.GET.get('consumer_id', '')
    parent_id = request.GET.get('parent', None)
    tm = datetime.datetime.now()

    responseData['consumer_id'] = consumer_id
    responseData['parent_id'] = parent_id

    if int(parent_id) == int(consumer_id):
        responseData['e'] = '不能绑定自己为上级'
        return JsonResponse(responseData)

    # 上下级唯一
    user = ActivityRebate_Relations.objects.filter(consumer_id=consumer_id)
    if len(user) > 0:
        user = user[0]

        if user is not None:
            if user.parent is not None:
                responseData['e'] = '当前已绑定上级'
            else:
                pr = ActivityRebate_Relations.objects.filter(consumer_id=parent_id)
                if len(pr) > 0:
                    pr = pr[0]
                    if int(pr.parent_id) == int(consumer_id):
                        responseData['e'] = '不能相互绑定为上下级'
                        return JsonResponse(responseData)
                    else:
                        user.parent_id = parent_id
                        user.save()
                else:
                    responseData['e'] = '上级帐户不存在'
    else:
        pr = ActivityRebate_Relations.objects.filter(consumer_id=parent_id)
        if len(pr) > 0:
            pr = pr[0]
            if int(pr.parent_id) == int(consumer_id):
                responseData['e'] = '不能相互绑定为上下级'
                return JsonResponse(responseData)
        ar = ActivityRebate_Relations(
                activity_id=29,
                company_id=20445,
                consumer_id=consumer_id,
                parent_id=parent_id,
                tm=tm
            )
        ar.save()

    return JsonResponse(responseData)


@csrf_exempt
def brandRecommenderRegister(request):
    # 专为求真酒业设计
    responseData = {
        'e': ''
    }
    phone = request.POST.get('phone', '')
    name = request.POST.get('name', '')
    address = request.POST.get('address', '')
    bind_company = request.POST.get('bind_compid', '')

    if not phone or not name or not address or not bind_company:
        responseData['e'] = '缺少参数'
    else:
        o_user = User.objects.filter(phone=phone)
        if len(o_user) > 0:
            responseData['e'] = '手机号已注册'
        else:
            account = phone
            uu=User(
                username=account,
                last_name=name,
                company_id=20445,
                ancestor_id=20445,
                phone=phone,
            )    
            uu.set_password('123456')
            uu.save()
            uu.groups.add(21)

            br = BrandRecommender(
                company_id=20445,
                agent_id=bind_company,
                # 操作人
                user=uu,
                name=name,
                cellphone=phone,
                address=address
            )
            br.save()
    return JsonResponse(responseData)

@csrf_exempt
def kangnian_checkLabelActivated(request):
    responseData = {
        'e': '',
        'act': 0,
        'lbcode': ''
    }
    a_code = request.GET.get('labelcode', '')
    labelcode = ""
    cur = connection.cursor()
    sql = f"select code_b from label.lbl_abcode where code_a='{a_code}'"
    cur.execute(sql)
    r = cur.fetchone()
    if r:
        labelcode = r[0]
        responseData['lbcode'] = labelcode

    if not a_code:
        responseData['e'] = '缺少参数'
    else:
        al = KangNian_ActiveLabel.objects.filter(labelcode=labelcode)
        if len(al) == 0:
            responseData['e'] = '标签未激活'
            responseData['act'] = 0
        else:

            responseData['act'] = 1
    return JsonResponse(responseData)


@csrf_exempt
def kangnian_getTerminal(request):
    responseData = {
        'e': '',
        'list': []
    }
    consumer = request.GET.get('consumer_id', '')
    labelcode = request.GET.get('labelcode', '')
    if not consumer or not labelcode:
        responseData['e'] = '缺少参数'
        return JsonResponse(responseData)
    else:
        company_list = Company.objects.filter(ancestor_id=31917, level_id=4)
        for i in company_list:
            responseData['list'].append({
                "id": i.id,
                "name": i.name,
                "address": i.address
            })
        return JsonResponse(responseData)

@csrf_exempt
def kangnian_getProductList(request):
    responseData = {
        'e': '',
        'list': []
    }
    p = Product.objects.filter(company_id=31917)
    product_list = []
    for i in p:
        product_list.append({
            "id": i.id,
            "name": i.name
        })
    responseData['list'] = product_list
    return JsonResponse(responseData)

@csrf_exempt
def kangnian_activate(request):
    responseData = {
        'e': ''
    }
    req = json.loads(request.body.decode('utf-8')) 
     
    user_id = req['user_id']
    labelcode = req['labelcode']
    start_labelcode = req['start_labelcode']
    end_labelcode = req['end_labelcode']
    product_id = req['product_id']
    payment_method = req['payment_method']
    is_check = req['is_check']

    cur = connection.cursor()
    if not user_id or not product_id:
        responseData['e'] = '缺少参数'
        return JsonResponse(responseData)
    else:
        u = User.objects.filter(id=user_id)
        if len(u) > 0:
            if labelcode:
                al = KangNian_ActiveLabel.objects.filter(labelcode=labelcode)
                if len(al) == 0:
                    active_label = KangNian_ActiveLabel(
                        labelcode = labelcode,
                        is_active = True,
                        enabled = True,
                        user_id = user_id,
                        product_id = product_id,
                        payment_method = payment_method
                    )
                    active_label.save()
                else:
                    responseData['e'] = '标签已激活'
            elif start_labelcode or end_labelcode:
                if str(is_check) == '1':
                    sql = f"select count(*) from label.lbl_abcode where code_b between '{start_labelcode}' and '{end_labelcode}' and (select count(1) from logistics.sales_kangnian_activelabel where labelcode=code_b) = 0"
                    cur.execute(sql)
                    r = cur.fetchone()
                    responseData['number'] = r[0]
                else:
                    sql = f"select code_b from label.lbl_abcode where code_b between '{start_labelcode}' and '{end_labelcode}'"
                    cur.execute(sql)
                    r = cur.fetchall()
                    r_list = []
                    for i in r:
                        r_list.append(i)
                    counter = 0
                    for j in r_list:
                        o_knal = KangNian_ActiveLabel.objects.filter(labelcode=j[0])
                        if len(o_knal) == 0:
                            knal = KangNian_ActiveLabel(
                                    labelcode = j[0],
                                    is_active = True,
                                    enabled = True,
                                    user_id = user_id,
                                    product_id = product_id,
                                    payment_method = payment_method
                            )
                            knal.save()
                            counter += 1
                        responseData['cnt'] = counter
            else:
                responseData['e'] = '缺少参数'
                return JsonResponse(responseData)
        else:
            responseData['e'] = '找不到用户'
    return JsonResponse(responseData)

@csrf_exempt
def kangnian_reserve(request):
    responseData = {
        'e': ''
    }
    consumer_id = request.POST.get('consumer_id', '')
    labelcode = request.POST.get('labelcode', '')
    terminal_id = request.POST.get('terminal_id', '')
    name = request.POST.get('name', '')
    phone = request.POST.get('phone', '')

    if not consumer_id or not labelcode or not terminal_id or not name or not phone:
        responseData['e'] = '缺少参数'
        return JsonResponse(responseData)
    else:
        al = KangNian_ActiveLabel.objects.filter(labelcode=labelcode)
        if len(al) == 0:
            responseData['e'] = '标签未激活'
            return JsonResponse(responseData)
        if len(al) == 1 and not al[0].is_active:
            responseData['e'] = '标签未激活'
            return JsonResponse(responseData)
        c = Company.objects.filter(id=terminal_id, level_id=4)
        if len(c) > 0:
            o_rl = KangNian_ReserveLabel.objects.filter(labelcode=labelcode)
            if len(o_rl) == 0:
                rl = KangNian_ReserveLabel(
                    labelcode = labelcode,
                    consumer_id = consumer_id,
                    agent_id = terminal_id,
                    name = name, 
                    phone = phone
                )
                rl.save()
            else:
                responseData['e'] = '该号码已预约'
        else:
            responseData['e'] = '无此终端店'
        return JsonResponse(responseData)

@csrf_exempt
def kangnian_writeoff(request):
    responseData = {
        'e': ''
    }

    user_id = request.POST.get('user_id', '')
    reserve_id = request.POST.get('reserve_id', '')
    terminal_id = request.POST.get('terminal_id', '')
    scan_mode = request.POST.get('scan_mode', 0)
    labelcode = request.POST.get('labelcode', '')
    b_labelcode = ''

    if str(scan_mode) == '1' and labelcode:
        sql = f"select code_b from label.lbl_abcode where code_a='{labelcode}'"
        cur = connection.cursor()
        cur.execute(sql)
        r = cur.fetchone()
        if r:
            b_labelcode = r[0]
            rs = KangNian_ReserveLabel.objects.filter(labelcode=b_labelcode)
            if len(rs) > 0:
                reserve_id = rs[0].id
        else:
            responseData['e'] = '此号码无预约记录'
            return JsonResponse(responseData)

    if not user_id or not reserve_id or not terminal_id:
        responseData['e'] = '缺少参数'
    else:
        c = Company.objects.filter(id=terminal_id, level_id=4)
        if len(c) > 0:
            rs = KangNian_ReserveLabel.objects.filter(id=reserve_id)
            if len(rs) > 0:
                wolb = KangNian_WriteOffLabel.objects.filter(labelcode=rs[0].labelcode)
                if len(wolb) == 0:
                    new_wo = KangNian_WriteOffLabel(
                        labelcode = rs[0].labelcode,
                        user_id = user_id,
                        agent_id = terminal_id
                    )
                    new_wo.save()
                    rs[0].is_writeoffed = True
                    rs[0].save()
                else:
                    responseData['e'] = '此号码已被核销'
            else:
                responseData['e'] = '此号码无预约记录'
        else:
            responseData['e'] = '无此终端店'
    return JsonResponse(responseData)

@csrf_exempt
def kangnian_writeoff_list(request):
    responseData = {
        'e': ''
    }

    # 解七进制
    terminal_id = request.GET.get('terminal_id', '')
    decode_terminal_id = str(int(terminal_id, 7))
    terminal_id = decode_terminal_id
    is_history = request.GET.get('is_history', '')

    if not terminal_id:
        responseData['e'] = '缺少参数'
    else:
        c = Company.objects.filter(id=terminal_id, level_id=4)
        if len(c) > 0:
            rs = KangNian_ReserveLabel.objects.filter(agent_id=terminal_id)
            res_list_writeoffed = []
            res_list_not_writeoffed = []
            for i in rs:
                if i.is_writeoffed:
                    res_list_writeoffed.append({
                        "labelcode": i.labelcode,
                        "tm": i.tm,
                        "name": i.name,
                        "phone": i.phone,
                        "id": i.id,
                        "is_writeoffed": i.is_writeoffed
                    })
                else:
                    res_list_not_writeoffed.append({
                        "labelcode": i.labelcode,
                        "tm": i.tm,
                        "name": i.name,
                        "phone": i.phone,
                        "id": i.id,
                        "is_writeoffed": i.is_writeoffed
                    })
                responseData['list_w'] = res_list_writeoffed
                responseData['list_not_w'] = res_list_not_writeoffed
        else:
            responseData['e'] = '无此终端店'
    return JsonResponse(responseData)


# 通过手机修改密码获取验证码
def change_password_get_code(request): 
    data = {
        "e": "",
        'uid':''
    }
    phone = request.GET.get("phone", "") 
    company_id = request.GET.get("company_id", "")
    if phone: 
        cur = connection.cursor()
        if company_id:
            cur.execute("select id from company_user where phone = '" +
                    phone+"' and ancestor_id = "+company_id)
        else:
            cur.execute("select id from company_user where phone = '" +phone+"' limit 1")
        r = cur.fetchone()
        if r:
            user_id = r[0] 
            codes = []
            for i in range(6):
                codes.append(str(random.randint(0, 9)))
            code = ''.join(codes)
            text = '您的验证码是：' + code + '。请不要把验证码泄露给其他人!'
            sign = None
            if company_id == "19257":
                sign = '茗酒荟'
            send_sms(phone, text, sign)
            data['code'] = code  
            data['uid'] = user_id
            
            s = Sms(
                company_id=int(company_id),
                phone=phone,
                code=code
            )
            s.save() 
        else:
            data['e'] = '用户不存在'
    else:
        data['e'] = '数据丢失'
    return JsonResponse(data)




#AI数据汇总
def tjfw_swm(request):

    dt = datetime.datetime.now()
    dt_fr = datetime.datetime.now().strftime('%Y-%m')
    
    dt_fr = dt_fr+'-01'
    dt_to = datetime.datetime.now().strftime('%Y-%m-%d')
    # dt_fr = str(yr) + '-' + mn + '-01'
    # dt_to = str(yr) + '-' + mn + '-' + d

    # dt_fr = str(yr) + '-' + mn
    # dt_to = str(yr) + '-' + mn

    dt_fr = request.GET.get('date_from', dt_fr)
    dt_to= request.GET.get('date_to', dt_to) 

    dt_to =(datetime.datetime.strptime(dt_to,'%Y-%m-%d') + relativedelta(days=1)).strftime('%Y-%m-%d')

    data={
        'quantitys': {'category': [], 'zhunque': [],'ycxtongguo': [],'ycxzhengque':[]},
          'date_from': dt_fr,
          'date_to': dt_to,
          }
    postgres_host = "139.199.178.210"  # 数据库地址
    postgres_port = "15432"  # 数据库端口
    postgres_user = "postgres"  # 数据库用户名
    postgres_password = "frank"  # 数据库密码
    postgres_datebase = "swm"  # 数据库名字
    postgres_table1 = ""  # 数据库中的表的名字

    # connection string
    conn_string = "host=" + postgres_host + " port=" + postgres_port + " dbname=" + postgres_datebase + \
                  " user=" + postgres_user + " password=" + postgres_password
    conn = psycopg2.connect(conn_string)
    cur =conn.cursor()

    sql = "SELECT a.ut ,a.counts,b.cnt,c.toalrecordsforcouter,d.totalswmcouter,COALESCE(e.miscont,0)miscont,f.passRate,"+\
            "concat(round((1.0*b.cnt/c.toalrecordsforcouter*100),4))ycxzql,"+\
            "concat(round((1.0*f.passRate/d.totalswmcouter*100),4))ycxtgl,"+\
            "concat(round((1.0*(c.toalrecordsforcouter-COALESCE(e.miscont,0))/c.toalrecordsforcouter*100),4))zql from "+\
            "(select  to_char(uptime,'yyyy-mm-dd')ut ,count(*)counts from swm_recognition where uptime>'"+str(dt_fr)+"' and uptime<'"+str(dt_to)+"' GROUP BY ut)a"+\
            " LEFT outer JOIN"+\
            " (select count(*)cnt, to_char(uptime,'yyyy-mm-dd')ut from public.swm_recognition where uptime>'"+str(dt_fr)+"' "+\
            "and uptime<'"+str(dt_to)+"'and fdvalue<>0 and fdvalue=realvalue  GROUP BY ut)b"+\
            " on a.ut=b.ut left outer join"+\
            " (select count(*)toalrecordsforcouter, to_char(uptime,'yyyy-mm-dd')ut from public.swm_recognition "+\
          "where uptime>'"+str(dt_fr)+"' and uptime<'"+str(dt_to)+"'and fdvalue<>0  GROUP BY ut)c"+\
            " on a.ut=c.ut  left outer join"+\
            " (select count(*)totalswmcouter, to_char(uptime,'yyyy-mm-dd')ut from public.swm_recognition "+\
          "where uptime>'"+str(dt_fr)+"' and uptime<'"+str(dt_to)+"'and  realvalue=1  GROUP BY ut)d"+\
            " on a.ut=d.ut LEFT outer JOIN"+\
            " (select count(*)miscont, to_char(uptime,'yyyy-mm-dd')ut from public.swm_recognition where  "+\
          "uptime>'"+str(dt_fr)+"' and uptime<'"+str(dt_to)+"'and fdvalue=1 and realvalue<> 1 and iscorrect=false GROUP BY ut)e"+\
            " on a.ut=e.ut  LEFT outer JOIN"+\
            "(select count(*)passRate, to_char(uptime,'yyyy-mm-dd')ut from public.swm_recognition "+\
          "where  uptime>'"+str(dt_fr)+"' and uptime<'"+str(dt_to)+"'and fdvalue=1 and realvalue=1 GROUP BY ut)f"+\
            " on a.ut=f.ut order by a.ut"
    # print(sql)
    cur.execute(sql)
    r = cur.fetchall()
    for i in r :
        data['quantitys']['category'].append(i[0])
        data['quantitys']['zhunque'].append((i[-1]))
        data['quantitys']['ycxtongguo'].append((i[-2]))
        data['quantitys']['ycxzhengque'].append((i[-3])) 
    return  JsonResponse(data)
    # return render(request, 'sales/sales_table/swm.html', data)


#2021-02-28
@csrf_exempt
def get_app_obj(request,app,obj,obj_id,user_id):
    data={'e':'','data':{}}
    e=time_key_check(request,sK)
    if e:
        data['e']=e
        return JsonResponse(data)    
    m=djapps.get_model(app,obj)
    objs=m.objects.filter(id=int(obj_id))
    if not objs:
        data['e']='obj id error'
        return JsonResponse(data)
    o=objs[0]

    if app=='company' and obj=='bulletinboard':
        if request.method=='GET':
            attach=[]
            d={'id':o.id,'subject':o.subject,'content':o.content,'time':o.tm.strftime('%Y-%m-%d %H:%M:%S'),'remark':o.remark}
            sn=1
            for a in o.bulletinboardattach_set.all():
                attach.append({'sn':sn,'url':a.attachment.url,'name':a.get_name()})
            d['attachment']=attach
            data['data']=d
        else:            
            rs=BBSreader.objects.filter(bulletinboard_id=int(obj_id),user_id=int(user_id))
            if not rs:
                r=BBSreader(bulletinboard_id=int(obj_id),user_id=int(user_id))
                r.save()
    return JsonResponse(data)


#2021-03-12 酒鬼系列，扫码核销标签检查
def jg_writeoff_label_check(request,company_id):
    data={'e':'','label':'','package_id':0,'product_id':0,'product_name':'','label_pk_id':0,'label_code':'','box_code':'','is_bottlecap':False}
    e=time_key_check(request,sK)
    if e:
        data['e']=e
        return JsonResponse(data)     
    label=request.GET.get('label','')
    if not label:
        data['e']='label mission'
        return JsonResponse(data)
    is_gift=request.GET.get('is_gift','0')
    
    
    cs=Company.objects.filter(id=int(company_id))
    if not cs:
        data['e']='company id err'
        return JsonResponse(data)
    company=cs[0]
    aid=str(company.ancestor_id)
    
    data['label']=label
    cur=connection.cursor()      

    #2021-04-23 瓶盖码
    cur.execute("select bottle from z_bottlecap_binding where label='"+label+"' order by id desc limit 1")
    r=cur.fetchone()
    if r:        
        data['is_bottlecap']=True
        label=r[0]
    d=label_sys_info(label,company,has_price=False)     
    data['product_id']=d['product']['id']
    data['product_name']=d['product']['name']
    data['label_pk_id']=d['label_pk_id']
    data['package_id']=d['package']['id']
    data['box_code']=d['boxes']['pk']   
    data['label_code']=d['code']

    if data['label_pk_id']==0:
        data['e']='标签号码异常'
        return JsonResponse(data)
    #20211-04-23 是否为瓶标
    if data['package_id']!=1 and is_gift=='0':
        data['e']='请扫描瓶盖码(瓶标)'
        return JsonResponse(data)
    #2021-04-19是否已核销
    cur.execute("select user_id,to_char(tm,'yyyy-mm-dd HH24:MI:SS'),(select coalesce(last_name,username) "+\
        "from company_user where id=user_id) from entertainment_entwriteoff where label_code='"+data['label_code']+"'")
    r0=cur.fetchone()
    if r0:
        data['e']='此瓶已于'+r0[1]+'核销('+r0[2]+')'
        cur.close()
        return JsonResponse(data)    
    #2021-04-25 内参没有此产品信息,从酒鬼拿相关信息
    if company_id=='1644' and  not data['product_id']:
        company=Company.objects.get(id=1)
        d=label_sys_info(label,company,has_price=False) 
        data['label_pk_id']=d['label_pk_id']
        data['package_id']=d['package']['id']
        data['box_code']=d['boxes']['pk']   
        data['label_code']=d['code']        
        if d['product']['id']:
            s="select id,name from product_product where id="+\
                "(select nc_id from z_jg_nc_product where jg_id="+str(d['product']['id'])+")"
            cur.execute(s)
            r=cur.fetchone()
            if r:
                data['product_id']=r[0]
                data['product_name']=r[1]   
    cur.close()            
    return JsonResponse(data)


@csrf_exempt
def app_obj_action(request,app,obj):    
    data={'e':'','data':{}}
    e=time_key_check(request,sK)
    if e:
        data['e']=e
        return JsonResponse(data)
    try:
        m=djapps.get_model(app,obj)
    except Exception:
        data['e']='application name / object name error'
        return JsonResponse(data)
    

    #手机端删除公告信息
    if app=='company' and obj=='bulletinboard':
        r=json.loads(request.body.decode('utf-8'))   
        if 'action' not in r or 'user_id' not in r or 'obj_id' not in r:
            data['e']='action,user id,obj id missing'
            return JsonResponse(data)
        else:       
            action=r['action']
            obj_id=r['obj_id']
            user_id=r['user_id']
            if action=='delete':
                rs=BBSdelete.objects.filter(bulletinboard_id=int(obj_id),user_id=int(user_id))
                if not rs:
                    r=BBSdelete(bulletinboard_id=int(obj_id),user_id=int(user_id))
                    r.save()
    #手机端  用酒单
    elif app=='entertainment' and obj=='entapplication':
        r=json.loads(request.body.decode('utf-8'))   
        if 'action' not in r or 'user_id' not in r:
            data['e']='action,user id missing'
            return JsonResponse(data)
        action=r['action']
        staffs=Staff.objects.filter(user_id=r['user_id'])
        if not staffs:
            data['e']='user is not a staff'
            return JsonResponse(data)
        else:
            staff=staffs[0]
        if action=='submit':
            cur=connection.cursor()        
            e=[]
            for f in ['subject','reason','headcount','location','remark','category_id','products']:
                if f not in r:
                    e.append(f)
            if e:
                data['e']=','.join(e)+' missing'
                return JsonResponse(data)
            a=EntApplication(                
                company_id=staff.user.ancestor_id, 
                staff_id=staff.id, 
                user_id=staff.user.id,
                department_id=staff.department.id,
                category_id=r['category_id'],
                subject=r['subject'],
                headcount=r['headcount'],
                reason=r['reason'],
                location=r['location'],
                remark=r['remark']
            )
            a.save()
            ps=[]
            qty_x=0
            qty_p=0
            for p in r['products']:
                qty_x=qty_x+p['qty_x']
                qty_p=qty_p+p['qty_p']
                ps.append("("+str(a.id)+","+str(p['id'])+","+str(p['qty_x'])+","+str(p['qty_p'])+")")
            if ps:
                s="insert into entertainment_entappproduct(entapplication_id,product_id,qty_x,qty_p) values "+",".join(ps)
                cur.execute(s)    
            if qty_x+qty_p>0:
                a.qty_box=qty_x
                a.qty_bottle=qty_p
                a.save()
                a.set_cnt_bottles()
        elif action=='close':
            if 'id' not in r:
                data['e']='id missing'
                return JsonResponse(data)
            es=EntApplication.objects.filter(id=int(r['id']))
            if not es:
                data['e']='id not exists'
                return JsonResponse(data)
            e=es[0]
            e.is_closed=True
            e.save()

    #手机端  会议活动申报
    elif app=='entertainment' and obj=='actapplication':
        r=json.loads(request.body.decode('utf-8'))   
        if 'action' not in r or 'user_id' not in r:
            data['e']='action,user id missing'
            return JsonResponse(data)
        action=r['action']
        us=User.objects.filter(id=r['user_id'])
        if not us:
            data['e']='user id error'
            return JsonResponse(data)
        u=us[0]
        c=u.company
        staffs=Staff.objects.filter(user_id=r['user_id'])
        department_id=None
        #1;"公司";0;"";1
        #2;"一级";1;"";1
        #4;"二级";2;"";1       
        agent_id=None
        parent_id=None
        cnt_bottles=0
        #if c.level_id>1 and not c.is_expense_account:
        if c.level_id>1:
            agent_id=c.id
            parent_id=c.parent_id
        if staffs:
            department_id=staffs[0].department_id    
        if action=='submit':
            cur=connection.cursor()        
            e=[]
            for f in  ['schema_no','subject','hotel','cellphone','contact','invitee','dinner_headcount','dinner_address','dinner_dt','remark','category_id','products']:
                if f not in r:
                    e.append(f)
            if e:
                data['e']=','.join(e)+' missing'
                return JsonResponse(data)
            cnt_botles=None
            if 'cnt_bottles' in r:
                cnt_bottles=int(r['cnt_bottles'])
            a=ActApplication(          
                schema_no=r['schema_no'],
                company_id=u.ancestor_id,            
                division_id=c.division_id,
                subdivision_id=c.sub_division_id,
                user_id=r['user_id'],
                department_id=department_id,
                category_id=r['category_id'],
                subject=r['subject'],
                dinner_headcount=r['dinner_headcount'],
                dinner_address=r['dinner_address'],
                dinner_dt=r['dinner_dt'],
                cellphone=r['cellphone'],
                contact=r['contact'],
                invitee=r['invitee'],
                hotel=r['hotel'],
                remark=r['remark'],
                cnt_bottles=cnt_bottles,
                agent_id=agent_id,
                parent_id=parent_id,
            )

            #2021-05-10
            if 'is_gift' in r:
                if int(r['is_gift'])==1:
                    a.is_gift=True
            
            a.save()
            ps=[]
            qty_x=0
            qty_p=0
            for p in r['products']:
                qty_x=qty_x+p['qty_x']
                qty_p=qty_p+p['qty_p']
                ps.append("("+str(a.id)+","+str(p['id'])+","+str(p['qty_x'])+","+str(p['qty_p'])+")")
            if ps:
                s="insert into entertainment_actappproduct(actapplication_id,product_id,qty_x,qty_p) values "+",".join(ps)
                cur.execute(s)    
            if qty_x+qty_p>0:
                a.qty_box=qty_x
                a.qty_bottle=qty_p
                a.save()
                a.set_cnt_bottles()
        elif action=='close':
            if 'id' not in r:
                data['e']='id missing'
                return JsonResponse(data)
            es=ActApplication.objects.filter(id=int(r['id']))
            if not es:
                data['e']='id not exists'
                return JsonResponse(data)
            e=es[0]
            e.is_closed=True
            e.save()

    #手机端  赠酒申请
    elif app=='entertainment' and obj=='giftapplication':
        r=json.loads(request.body.decode('utf-8'))   
        if 'action' not in r or 'user_id' not in r:
            data['e']='action,user id missing'
            return JsonResponse(data)
        us=User.objects.filter(id=int(r['user_id']))
        if not us:
            data['e']='user id error'
            return JsonResponse(data)
        u=us[0]
        action=r['action']
        #staffs=Staff.objects.filter(user_id=r['user_id'])
        #if not staffs:
        #    data['e']='user is not a staff'
        #    return JsonResponse(data)
        #else:
        #    staff=staffs[0]  
        if action=='submit':
            cur=connection.cursor()        
            e=[]
            for f in ['code','receiver','cellphone','entity']:
                if f not in r:
                    e.append(f)
            if e:
                data['e']=','.join(e)+' missing'
                return JsonResponse(data)
            a=GiftApplication(                
                company_id=u.ancestor_id, 
                agent_id=u.company_id,
                user_id=u.id,  
                code=r['code'],
                receiver=r['receiver'],
                cellphone=r['cellphone'],                
                entity=r['entity'],
            )
            a.save()  
            data['data']['id']=a.id     
            data['data']['code']=a.code 
            data['data']['receiver']=a.receiver 
            data['data']['cellphone']=a.cellphone  
            data['data']['entity']=a.entity              
        elif action=='close':
            if 'id' not in r:
                data['e']='id missing'
                return JsonResponse(data)
            gs=GiftApplication.objects.filter(id=int(r['id']))
            if not es:
                data['e']='id not exists'
                return JsonResponse(data)
            g=es[0]
            g.is_closed=True
            g.save()

    elif app=='entertainment' and obj=='entwriteoff':      
        r=json.loads(request.body.decode('utf-8'))   
        if 'action' not in r or 'user_id' not in r or 'company_id' not in r:
            data['e']='action,user id,company_id missing'
            return JsonResponse(data)        
        if r['action']=='post':            
            if 'category_id' not in r or 'data' not in r:
                data['e']='category id,data missing'
                return JsonResponse(data)            
            us=User.objects.filter(id=int(r['user_id']))
            if not us:
                data['e']='user id error'
                return JsonResponse(data)
            u=us[0]
            if u.company_id!=int(r['company_id']):
                data['e']='user id not match company id'
                return JsonResponse(data)
            div_id='null'
            if u.company.division_id:
                div_id=str(u.company.division_id)
            sub_id='null'
            if u.company.sub_division_id:
                sum_id=str(u.company.sub_division_id)
            staffs=Staff.objects.filter(user_id=r['user_id'])
            agt_id='null'
            #if u.company.level_id!=1 and not u.company.is_expense_account:
            agt_id=str(u.company_id)
            parent_id='null'
            if u.company.parent_id:
                parent_id=str(u.company.parent_id)
            department_id='null'
            if staffs:               
                staff=staffs[0]
                department_id=str(staff.department.id)
            #4;"招待用酒";0;"";1644            #5;"会议(活动)用酒";0;"";1644            #6;"其它";0;"";1644
            entapplication_id='null'
            actapplication_id='null'
            #2021-05-10
            giftapplication_id='null'
            
            e=''
            address='null'
            if 'address' in r:
                address="'"+r['address']+"'"
            if r['category_id']==4:
                if 'entapplication_id' not in r:
                    e='entapplication id missing'
                else:
                    entapplication_id=str(r['entapplication_id'])
            elif r['category_id']==5:
                if 'actapplication_id' not in r:
                    e='actapplication id missing'
                else:
                    actapplication_id=str(r['actapplication_id'])
            #2021-05-10
            elif r['category_id']==10:
                if 'giftapplication_id' not in r:
                    e='giftapplication id missing'
                else:
                    giftapplication_id=str(r['giftapplication_id'])

            else:
                if 'entapplication_id' in r:
                    entapplication_id=str(r['entapplication_id'])
                elif 'actapplication_id' in r:
                    actapplication_id=str(r['actapplication_id'])
                #2021-05-10
                elif 'giftapplication_id' in r:
                    giftapplication_id=str(r['giftapplication_id'])
            if e:
                data['e']=e
                return JsonResponse(data)
            a=[]
            for d in r['data']:
                #2021-05-19
                pkg='1'
                if 'package_id' in d:
                    pkg=str(d['package_id'])

                is_gift='false'
                if 'is_gift' in d:
                    b=d['is_gift']
                    try:
                        if int(b)==1:
                            is_gift='true'
                    except Exception:
                        pass                    
                s="("+str(u.ancestor_id)+","+str(r['category_id'])+","+entapplication_id+","+actapplication_id+","+giftapplication_id+","+str(u.id)+","+div_id+","+sub_id+","+agt_id+","+parent_id+","+\
                    department_id+","+str(d['is_bottlecap']).lower()+","+str(d['product_id'])+",'"+d['label']+"','"+d['label_code']+"','"+d['box_code']+"',"+str(d['label_pk_id'])+","+address+","+is_gift+","+pkg+")"
                a.append(s)
            s="INSERT INTO entertainment_entwriteoff("+\
                "company_id,category_id,entapplication_id,actapplication_id,giftapplication_id,user_id,division_id,subdivision_id,agent_id,parent_id,department_id,"+\
                "is_bottlecap,product_id,label,label_code,box_code,label_pk_id,address,is_gift,package_id) values "+",".join(a)+" on conflict (label_code) do nothing"
            cur=connection.cursor()
            cur.execute(s)
            data['cnt']=cur.rowcount
            cur.close()    
            
            if entapplication_id!='null':
                o=EntApplication.objects.get(id=int(entapplication_id))
                o.check_writeoff()
            elif actapplication_id!='null':
                o=ActApplication.objects.get(id=int(actapplication_id))
                o.check_writeoff()
        #2021-04-25 核销明细
        elif r['action']=='details':           
            if 'obj' not in r or 'id' not in r:
                data['e']='obj name or obj id missing'
                return JsonResponse(data)
            obj=r['obj']
            id=str(r['id'])
            if obj not in ['entapplication','actapplication','giftapplication']:
                data['e']='obj name should be entapplication or actapplication or giftapplication'
                return JsonResponse(data)
            cur=connection.cursor()
            s="select label,label_code,box_code,(select name from product_product where id=product_id), "+\
                "package_id,case package_id when 1 then '瓶标' when 3 then '箱标' end,"+\
                "case when is_gift then 1 else 0 end,id "+\
                "from entertainment_entwriteoff where "+obj+"_id="+id
            cur.execute(s)
            d=[]
            for r in cur.fetchall():
                d.append({'label':r[0],'bottle':r[1],'box':r[2],'product':r[3],'package_id':r[4],'package_name':r[5],'is_gift':r[6],'id':r[7]})            
            data['data']={'details':d}

    return JsonResponse(data)


def geo_location_to_text(request):
    data = {
        'e': ''
    }
    lng = request.GET.get("lng", 0)
    lat = request.GET.get("lat", 0)

    if not lng:
        data['e'] = 'lag missing'
        return JsonResponse(data)
    if not lat:
        data['e'] = 'lat missing'
        return JsonResponse(data)

    # 解析地理位置
    province = ""
    city = ""
    address = ""
    district = ""
    headers = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/63.0.3239.132 Safari/537.36 QIHU 360SE'
    }
    lng_lat = [lng, lat]
    if lng and lat:
        lng_lat = gcj02_to_bd09(float(lng), float(lat))
        url_base = "https://api.map.baidu.com/geocoder/v2/?ak=ASl2xmUaYpjwdWy3GPBQbpUK"
        url = url_base + "&location=" + str(lng_lat[1]) + "," + str(lng_lat[0]) + "&output=json"
        location_result = requests.get(url, headers=headers)
        p = json.loads(location_result.text)
        if p["status"] == 0:
            province = p['result']['addressComponent']['province']
            city = p['result']['addressComponent']['city']
            district = p['result']['addressComponent']['district']
            address = p['result']['formatted_address']
            data["province"] = province
            data["city"] = city
            data["address"] = address
    return JsonResponse(data)


#2021-04-16
@csrf_exempt
def app_create(request,what):
    data={'e':'','id':0}
    e=time_key_check(request,sK)
    if e:
        data['e']=e
        return JsonResponse(data)
    
    r=json.loads(request.body.decode('utf-8'))
    if what=='agent':        
        if 'user_id' not in r or 'name' not in r or 'address' not in r or 'cellphone' not in r or not 'city' in r or not 'contacts' in r:
            data['e']='/user_id/name/city/address/cellphone/contacts missing'
            return JsonResponse(data)
        us=User.objects.filter(id=int(r['user_id']))
        if not us:
            data['e']='user id error'
            return JsonResponse(data)
        u=us[0]
        c=u.company
        cs=Company.objects.filter(name=r['name'],ancestor_id=c.ancestor_id)
        if cs:
            data['e']='经销商 '+r['name']+' 已存在!'
            return JsonResponse(data)
        o=Company(
            name=r['name'],
            city=r['city'],
            cell_phone=r['cellphone'],
            contacts=r['contacts'],
            address=r['address'],
            parent_id=c.id,
            ancestor_id=c.ancestor_id,
            hierarchy_id=c.hierarchy_id,
            version=c.version,
            category_id = c.category_id,
        )
        o.save()    
        data['id']=o.id
    return JsonResponse(data)




#2021-05-06
def list_products(request,company_id):    
    data={'e':'','data':[]}
    e=time_key_check(request,sK)
    if e:
        data['e']=e
        return JsonResponse(data)     
    ids=request.GET.get('ids','')
    if not ids:
        data['e']='ids mission'
        return JsonResponse(data)

    cs=Company.objects.filter(id=company_id)
    if not cs:
        data['e']='company id err'
        return JsonResponse(data)
    
    c=cs[0]
    sub_qry=[]   
    cur=connection.cursor()
    cur.execute("select id,name,desc_cn,unit from company_categoryprop where category_id="+str(c.category_id))
    for p in cur.fetchall():        
        sub_qry.append("coalesce((select value from product_productprop where id="+\
            "(SELECT prd_property_id  FROM product_prdprops where product_id=p.id "+\
            " and (SELECT categoryprop_id FROM product_productprop where id=prd_property_id)="+str(p[0])+")),'') "+p[1])  
    s="select id,name,coalesce(trim(to_char(price,'999999.99')),'') price,"+\
        "case when specs is null then '' else name||'['||left(specs,10)||']' end specs,"+\
        "coalesce(external_code,'') external_code,coalesce((select name from product_series where id=series_id),'') series,"+\
        "case when length(photo)>5 then '/media/'||photo else '' end photo,"+\
        "coalesce(barcode,'') barcode,"+",".join(sub_qry)+\
        " from product_product p where company_id="+company_id+" and id in ("+ids+")"               
    cur.execute(s)
    columns = [col[0] for col in cur.description]
    d=[ dict(zip(columns, row))
        for row in cur.fetchall() ]
    data['data']=d
    return JsonResponse(data)       


@csrf_exempt
def match_label_vcode(request):
  data = {
    'e': '',
    'status': '0'
  }
  labelcode = request.GET.get('labelcode', '')
  vcode = request.GET.get('vcode', '')

  if not labelcode or not vcode:
    data['e'] = 'lack of params'
    return JsonResponse(data)

  cur = connection.cursor()
  sql = r"select code,vcode from label.lbl_vcode where code='"+labelcode+"'"
  cur.execute(sql)
  r = cur.fetchall()
  if len(r) > 0:
    if r[0][1] == vcode:
      data['status'] = '1'
    else:
      data['status'] = '0'
  return JsonResponse(data)


@csrf_exempt
def check_yzm(request):
    data = {
        'e':''
    }
    label_code = request.GET.get('label_code','')
    yzm = request.GET.get('yzm','')
    if label_code and yzm:
        cur = connection.cursor()
        cur.execute("select vcode from label.lbl_vcode where code = '"+label_code+"' limit 1")
        rs = cur.fetchall()
        if rs:
            rs = rs[0]
            if rs[0].strip() == yzm:
                pass
            else:
                data['e'] = '验证码错误'
        else:
            data['e'] = '数据异常'
    else:
        data['e'] = '数据丢失'


    return JsonResponse(data)


#2021-06-23 消费者扫码层级返利
@csrf_exempt
def customer_scan_rebate(request):
    data={'e':'','channelpromotion_id':0,'info':''}
    e=time_key_check(request,sK)
    if e:
        data['e']=e
        return JsonResponse(data)    
    #POST JSON
    r=json.loads(request.body.decode('utf-8'))
    if 'label_code' not in r or 'company_id' not in r or 'label_scan_id' not in r:
        data['e']='label_code missing'
        return JsonResponse(data)
    #标签是否已有返利记录
    lbl=r['label_code']
    c_id=str(r['company_id'])
    s_id=str(r['label_scan_id'])
    cur=connection.cursor()
    cur.execute("select id from wx_channelpromotionrebate where label_code='"+lbl+"'")
    r=cur.fetchone()
    if r:
        data['info']='此标签已返利'
        return JsonResponse(data)
    #标签是否已有替换记录
    lbl1=lbl
    cur.execute("select code0 from label.lbl_replace_details where code='"+lbl+"' order by id desc limit 1")
    r=cur.fetchone()
    if r:
        lbl1=r[0]
    s="select parent_id,package_id from label.t"+lbl1[:4]+" where code='"+lbl1+"'"
    try:
        cur.execute(s)
    except Exception:
        data['e']="物流标签号码"+lbl+"错误"
        return JsonResponse(data)
    r=cur.fetchone()    
    if not r:
        data['e']="物流标签号码"+lbl+"错误"
        return JsonResponse(data)

    cur.execute("select parent_id,package_id,code,id from label.t"+lbl1[:4]+"  where id="+str(r[0]))
    r=cur.fetchone()
    if r[1]==3:
        pid=str(r[3])
        pcode=r[2]
    else:
        cur.execute("select parent_id,"+\
            "(select code from label.t"+lbl1[:4]+" where id=a.parent_id) "+\
            "from label.t"+lbl1[:4]+" a where id="+str(r[0]))
        rr=cur.fetchone()
        pid=str(rr[0])    
        pcode=rr[1]
    t=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')  
    ss="("+\
        "select id from inventory_stkdetails where ancestor_id="+c_id+" and label_pk_id="+pid+" "+\
            "and (select level_id from company_company where id=company_id)=end_level_id "+\
            "and case when terminal_qualified=true then "+\
                "(select is_terminal from company_company where id=company_id)=true else true end "+\
            "and case when array_length(products,1) is null then true else product_id=any(products) end "+\
            "and ("+\
                "case when array_length(div_ids,1)  is null then true else "+\
                    "(select division_id from company_company where id=company_id)=any(div_ids) end "+\
                "or "+\
                "case when array_length(subdiv_ids,1) is null then true else "+\
                    "(select sub_division_id from company_company where id=company_id)=any(subdiv_ids) end"+\
            ")  and case when array_length(agents,1) is null then true else company_id=any(agents) end "+\
            "order by id desc limit 1"+\
        ") "
    s="select id,rebates,"+ss+",package_unit "+\
        "from sales_channelpromotions where ('"+t+"' between tm_start and tm_end) "+\
        "and company_id="+c_id+" and status=1 and scan_type=2 and "+ss+" is not null "+\
        "order by priority desc limit 1"
    cur.execute(s)
    r=cur.fetchone()
    if r:  
        if r[3]==3:
            data['e']='费者扫码层级返利核算单位只能为瓶'
            return JsonResponse(data)
        p_id=str(r[0])
        data['prochannelpromotion_id']=r[0]
        sa=[]
        cur.execute("select batch_id,vtype_id,product_id,company_id from inventory_stkdetails where id="+str(r[2]))
        rr=cur.fetchone()
        bth_id=str(rr[0])
        vch_id=str(rr[1])
        prd_id=str(rr[2])
        tc=Company.objects.get(id=rr[3])        
        rbs=json.loads(r[1])
        for rb in rbs:            
            pc=tc
            div_id='null'
            if pc.division:
                div_id=str(pc.division.id)
            while pc.level_id>1:
                if pc.level_id==rb['id']:
                    sa.append("("+c_id+","+str(pc.id)+","+s_id+","+p_id+","+\
                        "(select consumer_id from sales_labelscan where id="+s_id+"),"+\
                        vch_id+","+bth_id+","+prd_id+","+str(pc.level_id)+","+div_id+","+\
                        str(rb['value'])+",'"+lbl+"','"+pcode+"')")                        
                pc=pc.parent
        if sa:    
            s="INSERT INTO wx_channelpromotionrebate(ancestor_id,company_id,labelscan_id,promotion_id,consumer_id,"+\
                    "vtype_id,batch_id,product_id,level_id,saldivision_id,val,label_code,box_code"+\
                ") VALUES "+",".join(sa)
            cur.execute(s)
    return JsonResponse(data)
