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 User,Company

from django.db import connection

from django.views.decorators.csrf import csrf_exempt
from django.utils.translation import gettext_lazy as _

import base64
import binascii

from utils.views import time_key_check,send_sms
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


#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_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 *
#2020-07-10
rd=redis.StrictRedis(host='localhost', port=settings.RD['port'], password=settings.RD['password'], decode_responses=True)



WX_ERRS={
    1:'标签号码不存在', 
    10:'标签已被扫码',    
}



#2020-08-20
#from Crypto.Cipher import DES
sW='a@t#)eqh'



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

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(label_code,company_id,lng=0,lat=0):  
    data={'activity':None,'award':None,'seeds_id':None,'package_id':0,'product_name':'','product_id':0,'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
    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']=product_id
                data['package_id']=package_id
                data['product_name']=d['product']['name']   
                data['e']=d['e']

    #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


                
    #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)"
    prfx="select id,priority from sales_activities "+sWhere    
    s="select a.id from ("+\
        prfx+" and array_length(products,1) is null"+\
        " union "+\
        prfx+" and ARRAY["+str(product_id)+"] <@ products"+\
    ") a order by a.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':'',  
        'openid':'',
        'user_id':0,
        'user_name':0,        
        '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,
        'roles':[]
    }    
    usr=request.POST.get('user_account','')
    pwd=request.POST.get('user_password','')
    openid=request.POST.get('openid','') 

    if usr and pwd and openid:
        user = authenticate(username=usr, password=pwd)
        if user is not None and user.is_active:           

            c=user.company
            data['pwd_changed']=user.pwd_changed
            data['user_id'] = user.id
            data['user_name']=user.username
            data['company_id'] = c.id
            data['ancestor_id']=c.ancestor_id
            data['company_name'] = c.name    
            data['level']=c.level.sn
            for g in user.groups.all():
                data['roles'].append(g.name)

            user.wx_openid=openid
            user.save()  
            #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)



@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:      
        data['e']='user account, old & new password, openid required'
    return JsonResponse(data)


#小程序自动登录
def wx_jscode_oauth(request):
    data={
        'e':'',  
        'openid':'',
        'pwd_changed':False,
        'user_id':0,
        'user_name':0,        
        'company_id':0,
        'company_name':'',
        #'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]
        data['user_id']=u.id
        data['user_name']=u.username
        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)
            #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):
    uid=request.COOKIES.get(WX_CK_UID, '')
    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:
             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]})
    
    return render(request, company_id+"/"+page_id+".html")   
        



#消费者扫码，经过微信授权后，在这里做实际业务逻辑处理
def wx_process_label_scan(request,company_id):  
    label_code=request.GET.get('label_code','')   
    openid=request.GET.get('openid','')
    consumer_id=request.GET.get('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_label_code']:
    #    label_code=request.session['wx_label_code']
    #if request.session['wx_openid']:
    #    openid=request.session['wx_openid']
    
    label_code = request.session.get('wx_label_code', label_code)
    openid = request.session.get('wx_openid', 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'
    cur=connection.cursor()    

    #营销活动ID
    #activity=None
    #award=None 
    #seeds_id=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:
    #    data=get_activity(label_code,company_id,lng=0,lat=0)          
    #    if data['activity']:
    #        activity=data['activity']
    #        award=data['activity'].draw()      

    
    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'}})
    
    else:  
        e={'code':0,'msg':''}        
        label_code=request.session.get('wx_label_code',label_code)        
        consumer_id=request.session.get('wx_consumer_id',consumer_id)         
        activity=None
        act_id='null'


        #'fav_color' in request.session



        if 'wx_activity_id' in request.session:  
            if request.session['wx_activity_id']:
                act_id=request.session['wx_activity_id']           
                acts=Activities.objects.filter(id=act_id)
                if acts:
                    activity=acts[0]
        #天鉴防伪微信域名已满，暂时不取地理位置


        


        #标签号码是否已被扫码
        s="select consumer_id,tm from sales_consumerwinner where label_code='"+label_code+"'"
        cur.execute(s)
        if cur.fetchone():
            e={'code':10,'msg':WX_ERRS[10]}                
        
        
        product=None
        if 'wx_product_id' in request.session:
            ps=Product.objects.filter(id=int(request.session['wx_product_id']))
            if ps:
                product=ps[0]
        

        #2020-09-30 找到符合的活动    
        #这里先做通用页面测试，以后考虑奖池中奖（已有奖项ID）和 自定义活动页面
        if act_id!='null':
            response=render(request, 'sales/activity/default.html', {
                'e':e,
                'activity':activity,
                'product':product,
                'company_id':company_id,
                'label_code':label_code,                
                'consumer_id':consumer_id,
                'package_id':request.session['wx_package_id'],                
            })
        else:
            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-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,   
                'wxofficialaccounts_id':wx_id,
                'product':product,
                'company_id':company_id,
                'label_code':label_code,                
                '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)
    #consumer_id=des.encrypt(pad8(str(consumer_id)))

    response.set_cookie(WX_CK_UID, consumer_id, expires=datetime.datetime.now()+datetime.timedelta(days=7))

    return response    




#2020-09-30
#def bugsell_check(consumer_id,stk_data,gis_data):
#    data={'e':'','id':0}
#    agt=Compang.objects.get(stk_data['agent_id'])
#    lng=str(gis_data['lng_lat'][0])
#    lat=str(gis_data['lng_lat'][1])
#    a=[]
#    for i in agt.adm_ids:        
#        s="(select st_distance(public.st_geogfromtext('SRID=4326;POINT("+lng+" "+lat+")'),"+\
#            "(select geom from public.adm where id="+str(i)+")))::int"    
#        a.append(s)
#    if not a:
#        data['e']=compid_auth_area+u'未指定授权区域'
#    else:            
#        cur=connection.cursor()
#        s="select LEAST("+",".join(a)+")"
#        cur.execute(s)
#        r=cur.fetchone()
#        d=r[0]
#        b=BugSales(
#            consumer_id=consumer_id,        
#            label_code=stk_data['label_code'],        
#            ancestor_id=stk_data['ancestor_id'],
#            company_id=stk_data['company_id'],
#            agent_id=stk_data['agent_id'],       
#            product_id=stk_data['product_id'],       
#            package_id=stk_data['ancesotr'],
#            stkdetials_id=stk_data['stkdetials_id'],        
#            province=gis_data['province'],  
#            city=gis_data['city'],
#            address=gis_data['address'],        
#            lng_lat=gis_data['lng_lat'],
#            distance=d
#        )
#        b.save()
#        data['id']=b.id
#    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):
    label_code=request.GET.get('cpid')  
    cur=connection.cursor()
    ck=request.COOKIES.get(WX_CK_UID, '')

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

   
    #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]        

        #f=open('d:\\datacenter\\cookies.txt','a')
        #f.write(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]  
            return HttpResponseRedirect(url)  
    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()
    return HttpResponseRedirect(url)


#微信公众号
#连接微信服务器，准备换取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()      
    return HttpResponseRedirect(url) 


#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-02
    #try:
    #        f=open('d:\\hzy\\wx_oauth.txt','w')
    #        f.write(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    
    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):
    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','w')
    #    f.write(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']
    data['sex']=str(d['sex'])
    data['headimgurl']=d['headimgurl']
    data['province']=d['province']
    data['city']=d['city']
    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']      

    return HttpResponseRedirect(url)
    #return HttpResponseRedirect(url+"?"+"&".join(["%s=%s" % (k, v) for k, v in data.items()]))

    

#微信小程序邀请码,默认小程序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])
    c_id=str(r[0])
    #同一经销商订单号不能重复
    cur.execute("select to_char(tm,'yyyy-mm-dd HH24:MI:SS') from company_agentorderbatch where company_id="+c_id+" and code='"+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) values ("+\
        c_id+","+a_id+","+v_id+","+user_id+",'"+code+"',"+dt+","+remark+") 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+")")    
    s="insert into company_agentorderdetails(product_id,qty,batch_id,ancestor_id,vendor_id,company_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)
        #下单人
        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+")"
        uncomplete=[]
        s="select to_char(tm,'yyyy-mm-dd HH24:MI'),"+\
            "code,"+\
            "array_to_string(array(select "+\
                "(select name from product_product where id=product_id)||qty||'箱' from company_agentorderdetails where batch_id=a.id),','),"+\
            "coalesce(to_char(dt_expect,'yyyy-mm-dd'),''),"+\
            "id,"+\
            "(select name from company_company where id=company_id),"+\
            "(select username from company_user where id=user_id) "+\
            "from company_agentorderbatch a where tm_complete is null"+sAnd        
        cur.execute(s)
        for r in cur.fetchall():
            uncomplete.append({
                'order_time':r[0],
                'order_code':r[1],
                'product_qty':r[2],
                'date_expect':r[3],
                'id':r[4],
                'company_name':r[5],
                'user_name':r[6]                
            })
        complete=[]
        s="select to_char(tm,'yyyy-mm-dd HH24:MI'),"+\
            "code,"+\
            "array_to_string(array(select "+\
                "(select name from product_product where id=product_id)||qty||'箱' from company_agentorderdetails where batch_id=a.id),','),"+\
            "coalesce(to_char(dt_expect,'yyyy-mm-dd'),''),"+\
            "id,"+\
            "(select name from company_company where id=company_id),"+\
            "(select username from company_user where id=user_id) "+\
            "from company_agentorderbatch a where tm_complete is not null"+sAnd 
        cur.execute(s)
        for r in cur.fetchall():
            complete.append({
                'order_time':r[0],
                'order_code':r[1],
                'product_qty':r[2],
                'date_expect':r[3],
                'id':r[4],
                'company_name':r[5],
                'user_name':r[6]       
            })
        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'),"+\
            "(select count(*) from inventory_recipientdetails where batch_id=a.id and label_pkg=3),"+\
            "(select count(*) 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 not null"
        cur.execute(s)
        for r in cur.fetchall():
            closed.append({
                '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]
        else:
            data['e']='user_id error'
        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
    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)




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()
    data['code']=j['wl']
    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',''))

    #f=open('d:\\test_nc_geo.txt','w')
    #f.write('lng_lat:'+lng+","+lat+"\r\n")


    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" 

        #f.write(s+"\r\n")
        
        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]

    #f.write(str(distances[0]))
    #f.close()

    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)




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":'',
    }
    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:
        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
    #不写入
    record = '0'
    #返回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'

    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 "+\


        if not label_pk_id:
            data['e']='label code error'
        else:
            s="select coalesce(p.name,(select name from product_product where id=r.product_id),''),"+\
                "coalesce(p.photo,(select photo from product_product where id=r.product_id),''),"+\
                "coalesce(p.price,(select price from product_product where id=r.product_id),0),"+\
                "(select coalesce(batch_no,code) from inventory_batch where id=r.batch_id) batch_no,"+\
                "(select coalesce(qc_name,'') from inventory_batch where id=r.batch_id) 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=r.label_pk_id"+\
                    " and vtype_id=2 and company_id=1 limit 1),'yyyy-mm-dd'),''),"+\
                "(select name from product_series where id=p.series_id) "+\
                "from "+\
                "(select product_id,label_pk_id from inventory_stkdetails where company_id=1644 and label_pk_id="+label_pk_id+" 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 batch_id,label_pk_id,(select nc_id from z_jg_nc_product where jg_id=a.product_id) product_id "+\
                    "from inventory_stkdetails a where company_id=1 and label_pk_id="+label_pk_id+\
                    " and vtype_id in (1,2) and is_delete=false limit 1"+\
                ") r "+\
                "on a.label_pk_id=r.label_pk_id" 
            cur.execute(s)
            r=cur.fetchone()
            if r:                
                data['product']=r[0]
                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['package']=r[7] 
                data['outbound_date']=r[8]
                data['series']=r[9]



        cur.close()

    #data save to label_scan
    '''
    if uid and c:

        data_save={
            'consumer_id':int(uid),
            'compan_id':1644,
            'label_code':c,
        }
        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']
    '''
    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']
    )
    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




'''
#def test_wx_location(request,company_id):
#            #2020-10-06 取微信授权公众号 
#            cur=connection.cursor()
#            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={
#                  
#                'wxofficialaccounts_id':wx_id,
#               
#            }
#            return render(request,'label_scan/'+str(company_id)+'/index.html', data)



from app.models import WXUser, RedirectUrl
from utils import get_wx_authorize_url, get_random_string
from django.shortcuts import redirect



# Create your views here.
def get_wx_authorize_url(appid : str, state: str = None):
  if state is None:
    state = "".join([random.choice(string.ascii_letters + string.digits) for _ in range(20)])
  redirect_url = 'your callback url' # 回调链接，在这里面进行用户信息入库的操作
  response_type = 'code'
  scope = 'snsapi_userinfo'
  wx_url = f"https://open.weixin.qq.com/connect/oauth2/authorize?appid={appid}&redirect_uri={redirect_url}&response_type={response_type}&scope={scope}&state={state}#wechat_redirect"
  return wx_url


def request_access_token(appid : str, secret : str, code: str):
  secret = settings.WX_SECRET
  api = f"https://api.weixin.qq.com/sns/oauth2/access_token?appid={appid}&secret={secret}&code=[code]&grant_type=authorization_code"
  r = requests.get(api)
  return r.json()


def request_userinfo(access_token: str, openid: str):
  api = f"https://api.weixin.qq.com/sns/userinfo?access_token={access_token}&openid={openid}&lang=zh_CN"
  r = requests.get(api)
  return r.json()

 
def login_required(func):
  def wrapper(request, *args, **kwargs):
    openid = request.openid
    try:
      user = WXUser.objects.get(openid=openid)
      request.wxuser = user
    except WXUser.DoesNotExist:
      state = get_random_string()
      redirect_url = get_wx_authorize_url(state=state)
 
      # 存储跳转链接
      try:
        r = RedirectUrl.objects.get(state=state)
      except RedirectUrl.DoesNotExist:
        r = RedirectUrl()
        r.state = state
      origin_url = request.get_raw_uri()
      r.url = origin_url
      r.save()
 
      return redirect(redirect_url)
    return func(request, *args, **kwargs)
 
  return wrapper


class RedirectUrl(BaseModel):
  state = models.TextField(unique=True)
  url = models.TextField()
'''
