# -*- coding: utf-8 -*-
from __future__ import unicode_literals
from rest_framework import viewsets
# from rest_framework import permissions
from .serializers import *
from .models import *
import json
from django.utils.translation import gettext_lazy as _
import psycopg2

from django.http import HttpResponse, HttpResponseRedirect, JsonResponse
from django.views.decorators.csrf import csrf_exempt
from utils import *
from datacenter.settings import JG_K,SPECIAL_COMPANIES,WULIU_STR,SYNC_JG_TY,TY_WULIU_STR,MEDIA_ROOT
from django.apps import apps
from utils.views import *
from sales.models import Activities
from django.contrib.auth.decorators import login_required,user_passes_test
from django.contrib.auth import authenticate, login, logout
from .forms import *
from django.db import connection
from product.models import *
from product.forms import *
from django.utils.safestring import SafeString
from django.shortcuts import redirect

#from django.shortcuts import render_to_response
from pure_pagination import Paginator, EmptyPage, PageNotAnInteger
from django.template import RequestContext
import requests
from django.db.models import Q

from django.shortcuts import redirect
from django.contrib.auth.models import Group
import os

from inventory.models import Batch

#2021-02-25
import uuid

from rbac.views import *

#from django.core.context_processors import csrf
#from django.template.context_processors import csrf


# from django.forms.models import inlineformset_factory, formset_factory

# 扫码枪MD5前缀
sK = 'fgfdvgu$#&3t@j'

class CategoryViewSet(viewsets.ModelViewSet):
    queryset = Category.objects.all()
    serializer_class = CategorySerializer


class CompanyViewSet(TjViewSet):
    queryset = Company.objects.all()
    serializer_class = CompanySerializer


class CategoryPropViewSet(viewsets.ModelViewSet):   
    queryset = CategoryProp.objects.all()
    serializer_class = CategoryPropSerializer

def user_logout(request):
    logout(request)
    s = _('您已退出登录')

    #2020-10-13
    # response = HttpResponse(s)
    response = HttpResponseRedirect('/')

    if request.COOKIES.get('user_id',''):
        response.delete_cookie('user_id')
        #response.set_cookie('user_id', 0, expires=60 * 60 * 24 * 7)
    return response
    #return ('/')


# 扫码枪登录
def app_login(request):
    data = {'user_id':0, 'company_id':0, 'e':'','company_name':'','storehouse':[],
            'is_manufacturing':False,'has_plants':False,'roles':[],'ancestor_id':0 }
    
    k = sK
    e = time_key_check(request, k)
    if e:
        data['e'] = e
        return JsonResponse(data)
    usr = request.GET.get('user', '')
    pwd = request.GET.get('password', '')
    user = authenticate(username=usr, password=pwd)
    if user is not None and user.is_active:
        for o in user.storehouse_set.all():
            data['storehouse'].append({'id':o.id,'name':o.name})            
        data['user_id'] = user.id
        data['level']=user.company.level.sn
        data['company_id'] = user.company.id
        data['company_name'] = user.company.name
        data['is_manufacturing']=user.company.is_manufacturing      
        data['has_plants']=user.company.has_plant
        data['ancestor_id']=user.company.ancestor_id
        for g in user.groups.all():
            data['roles'].append(g.name)

    else:
        data['e'] = '帐号或密码错误'
    return JsonResponse(data)


def tj_os_login(request):
    uid=request.GET.get('uid',0)
    users = User.objects.filter(legacy_id=int(uid))
    #tk='null'
    if users:
        user = users[0]
        uid = encrypt_p(str(user.id))
        #tk=csrf(request)["csrf_token"]  

        #2019-08-21
        login(request,user)
        
    #return render(request, 'tj_os_login.html', {'uid': uid,'csrftoken':tk})
    #print('csrftoken:',request.META['CSRF_COOKIE'])
    #print('sessionid:',request.session.session_key)
    #tk=request.META['CSRF_COOKIE']
    #sk=request.session.session_key


    #return render(request, 'tj_os_login.html', {'uid': uid,'csrftoken':tk,'sessionid':sk})
    return render(request, 'tj_os_login.html', {'uid': uid})


def accounts_login(request):
    e = ''
    if request.method == 'GET':
        nxt = request.GET.get('next', '')
    else:
        nxt = request.POST.get('next', '')
        usr = request.POST.get('usr', '')
        pwd = request.POST.get('pwd', '')
        user = authenticate(username=usr, password=pwd)
        if user is not None and user.is_active:
            login(request, user)
            return HttpResponseRedirect(nxt)
        else:
            e = _('用户帐号或密码错误!')
   
    return render(request, 'login.html', {'next': nxt, 'e': e})


def user_login(request):
    
    uid = request.GET.get('uid',0)
    e = []
    if uid:
        #return render(request, 'login.html', {'next': '/'})
        users =User.objects.filter(legacy_id=int(uid))
        response = HttpResponse('')
        if users:
            user = users[0]
            login(request, user)
            #request.user = user
            ck = encrypt_p(str(user.id))
        else:
            ck = ""
        response["P3P"] = 'CP="IDC DSP COR ADM DEVi TAIi PSA PSD IVAi IVDi CONi HIS OUR IND CNT"'
        response.set_cookie('user_id', ck, expires=60 * 60 * 24 * 7) 

        
        

        return response
    else:
        usr = request.POST.get('usr', '')
        pwd = request.POST.get('pwd', '')
        user = authenticate(username=usr, password=pwd)
        if user:
            if user.check_password(pwd):
                if user.is_active:
                    login(request, user)
                else:
                    e = [_('此帐号已失效!!!!!')]
            else:
                e = [_('用户名或密码不正确!'), ]
        else:
            e = [_('用户名或密码不正确!'), ]

    
    return HttpResponse(json.dumps({'e': e}, ensure_ascii=False))


@login_required
@csrf_exempt
def user_chgpwd(request):
    e = ''
    if request.method == 'GET':
        nxt = request.GET.get('next', '')
    else:
        u = request.user
        oldpassword = request.POST.get('pwd0', '')
        newpassword = request.POST.get('pwd1', '')
        nxt = request.POST.get('next', '')
        user = authenticate(username=u.username, password=oldpassword)
        if user is not None and user.is_active:
            user.set_password(newpassword)
            user.pwd_changed=True
            user.save()

            # 写入SQLSERVER
            cn = pyodbc.connect(WULIU_STR)
            cur = cn.cursor()
            s = "update TJMarketingSystemYin.dbo.TJ_User set PassWords='" + md5_password(newpassword)+ "' " + \
                "where pg_stk_id=" + str(u.id)
            cur.execute(s)
            cn.commit()
            return HttpResponseRedirect(nxt)
        else:
            e = _('用户帐号或原密码错误!')
    return render(request, 'chgpwd.html', {'next': nxt, 'e': e})


#@login_required
@tj_login_required
def home(request):
    is_os = False;    
    
    #tk=csrf(request)["csrf_token"]
    tk = request.COOKIES.get('user_id', '')
    if tk:
        if not tk.isdigit():
            is_os = True
    c=request.user.company
    #return render(request, 'home.html',{'is_os': is_os,'csrftoken':tk})
    company_logo=''
    cur = connection.cursor()
    a_id=0
    if c:
        a_id=c.ancestor_id
    cur.execute("select img_logo from company_company where id="+str(a_id))
    r=cur.fetchone()
    if r:
        if r[0]:
            company_logo=r[0]
        else:
            company_logo="logo/logo_tj.png"
    else:
        company_logo="logo/logo_tj.png"
    
    
    #sys_admin
    #if request.user.id==3506:
    #    html='sys_admin.html'
    #jg_maketing
    #elif request.user.id==4752:
    #    html="marketing_home.html"
    #else:
    #    html='home.html'



    




    #2021-09-16
    menuitems=[]
    cfg=CompanyCfg.objects.get(company_id=a_id)
    #sys_admin
    if request.user.id==3506:
        html='sys_admin.html'
    else:
        if cfg.menu_type==1:
            html='index.html'
            m=MenuCompany(company_id=a_id,cgroup_id=None,user_id=request.user.id)
            only_authorized=True
            if request.user.has_perm('company.sys_admin'):
                only_authorized=False
            menuitems=m.get_user_menu(only_authorized=only_authorized)     
        else:
            if request.user.id==4752:
                html="marketing_home.html"
            else:
                html='home.html'
    
    #2021-04-19
    is_salesman=False
    
    u=request.user
    #用户组别
    group_ids=[]

    cur.execute("select group_id FROM company_user_groups where user_id="+str(request.user.id))
    for r in cur.fetchall():
        group_ids.append(r[0])
    #8;"业务员" 12;"销售助理"  4;"系统管理员"
    if 4 not in group_ids:
        if 8 in group_ids:   
            is_salesman=True
    cur.close()
        
    return render(request, html,{'is_os':is_os,'company_logo':company_logo,'is_salesman':is_salesman,'menuitems':menuitems})


#2021-05-04
@login_required
def index(request):   
    u=request.user
    c=request.user.company
    #return render(request, 'home.html',{'is_os': is_os,'csrftoken':tk})
    company_logo=''
    cur = connection.cursor()
    a_id=0
    if c:
        a_id=c.ancestor_id
    cur.execute("select img_logo from company_company where id="+str(a_id))
    r=cur.fetchone()
    if r:
        if r[0]:
            company_logo=r[0]
        else:
            company_logo="logo/logo_tj.png"
    else:
        company_logo="logo/logo_tj.png"    
    
    #sys_admin
    if request.user.id==3506:
        html='sys_admin.html'    
    else:
        html='index.html'
    
   
    #m=RbacMenu(company=None,cgroup=None,user=request.user)
    #menuitems=m.get_menu()     

    only_authorized=True
    if u.has_perm('company.sys_admin'):
        only_authorized=False
    m=MenuCompany(company_id=a_id,user_id=u.id)

    menuitems=m.get_user_menu(only_authorized=only_authorized) 

   
    cur.close()          
    return render(request, html,{'company_logo':company_logo,'menuitems':menuitems})






def get_obj_special(company_id, obj):
    data = {'e': '', 'data': []}
    # 酒鬼内参ID:2011, 经销商使用酒鬼经销商，产品使用酒鬼酒的产品名称内含“内参”字样; 酒鬼公司ID=1
    if company_id == 2011:
        cur = connection.cursor()
        if obj == 'product':
            cur.execute("select id,name,coalesce(external_code,'') from logistics.product_product " + \
                        "where (company_id=1 and name like '%内参%') or company_id=2011 order by name")
            for r in cur.fetchall():
                data['data'].append({
                    'id':r[0],
                    'name':r[1],
                    'code':r[2]
                })
        elif obj == 'agent':
            cur.execute("select id,name,coalesce(external_code,'') from logistics.company_company " + \
                        "where parent_id in (1,2011) order by name")
            for r in cur.fetchall():
                data['data'].append({
                    'id': r[0],
                    'name': r[1],
                    'code': r[2]
                })
    return JsonResponse(data)


def get_obj_list(request, company_id, obj):
    """
    扫码枪数据同步接口
    :param request:
    :param company_id: 公司ID
    :param obj: 对像名
    :return: {'e':'', 'data':[]}
    """
    data = {'e': '', 'data': []}
    d = {
        'product': ['产品', 'product', 'Product'],
        'store': ['仓库', 'inventory', 'Storehouse'],
        'agent': ['经销商', 'company', 'Company'],
        'terminal': ['终端店', 'company', 'Company'],
        'plant': ['厂区', 'company', 'PrdPlant'],
        'workshop': ['车间', 'company', 'PrdWorkshop'],
        'line': ['生产线', 'company', 'PrdLine'],
        'team': ['班组', 'company', 'PrdTeam'],
        'qc': ['质检员', 'company', 'PrdQc']
    }

    

    sK = 'fgfdvgu$#&3t@j'
    e = time_key_check(request, sK)
    if e:
        data['e'] = e
        return JsonResponse(data)
    if obj not in d:
        data['e'] = _('无效对像名')
        return JsonResponse(data)
    m = apps.get_model(d[obj][1], d[obj][2])
    search_dict = dict()
    if obj == 'agent':
        c=Company.objects.get(id=int(company_id))
        sWhere=''
        sOrder=''
        if c.level.sn == 0:
            #2021-05-24 企业取客户信息，不应包含自己
            sWhere=" where ancestor_id="+str(c.ancestor_id)+" and id<>"+str(c.id)
            if c.id in [20324]: #江口醇：要求把一级经销商放前面
                sOrder=" order by level_id,name"
        else:
            sWhere=" where parent_id="+str(c.id)
        cur=connection.cursor()
        s="select id,name,coalesce(external_code,''),coalesce(log_code,''),"+\
            "(select sn from company_level where id=level_id) "+\
            "from company_company"+sWhere+sOrder
        cur.execute(s)
        for r in cur.fetchall():
            data['data'].append({'id':r[0],'name':r[1],'code':r[2],'level':r[4],'log_code':r[3]})
        #2020-06-02 张轩：麻烦一下，一级商都添加团购和零售   
        if c.id==1644:
            data['data'].append({'id':11013,'name':'零售','code':'','level':10000,'log_code':''})     
            data['data'].append({'id':11014,'name':'团购','code':'','level':10001,'log_code':''})    
        cur.close()
        return JsonResponse(data)
        #2019-11-14
        #company=Company.objects.get(id=int(company_id))
        #if company.level.sn == 0:
        #    search_dict['hierarchy_id'] = company.hierarchy_id
        #    search_dict['version'] = company.version
        #else:
        #    search_dict['parent_id'] = int(company_id)   


            
    elif obj == 'product':
        c=Company.objects.get(id=int(company_id))
        cur = connection.cursor()
        if c.is_exclusive:
            s="select product_id,coalesce(external_code,''),name,coalesce(barcode,'') from product_product where company_id="+str(c.ancestor_id)
            cur.execute(s)       
            rs=cur.fetchall()
        else:                     
            #s="select a.product_id,b.external_code,b.name from "+\
            #    "(SELECT product_id FROM logistics.company_authorizedproducts where company_id="+str(c.id)+") a "+\
            #"left outer join "+\
            #"(select id,name,external_code from logistics.product_product where company_id="+str(c.ancestor_id)+") b "+\
            #"on a.product_id=b.id"
            #cur.execute(s)       
            #rs=cur.fetchall()
            rs=[]
        if rs:
            d=[]
            for r in rs:
                d.append({'id':r[0],'code':r[1],'name':r[2],'barcode':r[3]})
            return JsonResponse(d)
        else:
            search_dict['company_id']=c.ancestor_id
            search_dict['is_delete']=False
    #2021-04-25
    #elif obj=='store':
    #    cur = connection.cursor()
    #    cur.execute("select id,name,external_code from inventory_storehouse where company_id="+company_id)
    #    d=[]
    #    for r in cur.fetchall():
    #        d.append({'id':r[0],'name':r[1],'code':r[2]})
    #    cur.close()
    #    return JsonResponse(d,safe=False)
    elif obj == 'terminal':
        search_dict['parent_id'] = int(company_id)
        #search_dict['level_id'] = 3
    else:
        search_dict['company_id'] = int(company_id)
    
    #2019-11-05
    #屏蔽删除标志的记录
    if obj!='plant':
        search_dict['is_delete'] =False

    # 是否需要特殊处理：
    #if int(company_id) in SPECIAL_COMPANIES and obj in ['product','agent']:
    #    return get_obj_special(int(company_id),obj)

    #print('begin')
    for o in m.objects.filter(**search_dict).order_by('id'):
        dct = dict()
        dct['id'] = o.id
        dct['name'] = o.name
        dct['code'] = o.external_code
        #2019-12-23
        if obj=='agent':
            dct['level']=o.level.sn
            dct['log_code']=''
            if o.log_code:
                dct['log_code']=o.log_code
        if obj=='product':
            c=''
            if o.barcode:
                c=o.barcode
            dct['barcode']=c
        data['data'].append(dct)
    #print('end')
    #2020-05-07 张轩：一级经销商如果没有子级，把团购和零售加上
    #if not data['data'] and obj == 'agent':
    #    data['data']=[
    #        {'id':11013,'name':'零售','code':'','level':10000,'log_code':''},     
    #        {'id':11014,'name':'团购','code':'','level':10001,'log_code':''},   
    #    ]

    
    #2020-06-02 张轩：麻烦一下，一级商都添加团购和零售
    if obj == 'agent':
        data['data'].append({'id':11013,'name':'零售','code':'','level':10000,'log_code':''})     
        data['data'].append({'id':11014,'name':'团购','code':'','level':10001,'log_code':''})        
    
    return JsonResponse(data)



#@login_required
@tj_login_required
def list_obj(request, obj):
    categories = []
    plants = []
    workshops = []
    divisions = []
    page = 1
    d = {
        'product': ['产品', 'product', 'Product'],
        'series':['产品系列','product','Series'],
        'store': ['仓库', 'inventory', 'Storehouse'],
        'agent': ['经销商', 'company', 'Company'],
        'terminal': ['终端店', 'company', 'Company'],
        'productprop': ['产品属性', 'product', 'ProductProp'],
        'prd_plant': ['工厂/厂区', 'company', 'PrdPlant'],
        'prd_line': ['生产线', 'company', 'PrdLine'],
        # 'prd_workshop': ['生产班组', 'company', 'PrdWorkshop'],
        'prd_team': ['生产班组', 'company', 'PrdTeam'],
        'prd_qc': ['质检员', 'company', 'PrdQc'],
        'sal_division': ['营销战区', 'company', 'SalesDivision'],
        'sal_subdivision': ['营销分区', 'company', 'SubSalDivision'],
        #2021-01-24
        'wxwalletapp': ['经销商绑定微信钱包申请', 'company', 'WxWalletApplication'],
    }
    if obj not in d:
        return HttpResponse('无效对像名')
    disable_link = False
    company = request.user.company
    company_id = company.id
   

    search_dict = dict()

    if obj== 'sal_subdivision':
        for dv in SalesDivision.objects.filter(company_id=int(company_id)):
            divisions.append([dv.id, dv.name])
        search_dict['company_id'] = int(company_id)
    elif obj == 'agent':        
        if company.level.sn == 0:
            search_dict['hierarchy_id'] = company.hierarchy_id
            search_dict['version'] = company.version
            search_dict['level_id__lte']=12
            #2020-01-13
            search_dict['is_delete'] = False
        else:
            search_dict['parent_id'] = int(company_id)        
    elif obj=='product':
        search_dict['is_delete'] = False
        search_dict['company_id'] = int(company_id)
    #2021-01-24 移到 listobj.views
    #elif obj=='wxwalletapp': 
    #    u=request.user
    #    #权限检查
    #    clmns=["申请时间","申请人","当前状态","上级经销商","所属大区","经销商","收款人微信","手机号码","收款人实名","操作"]
    #    d=[]
    #    if company.level_id==1:
    #        sWhere=" where ancester_id="+str(company.id)
    #    elif company.level_id==2:
    #        sWhere=" where company_id="+str(company.id)+" or (select parent_id from company_company where id=company_id)="+str(company_id)
    #    else:
    #        sWhere=" where company_id="+str(company.id)
    #    dt=datetime.datetime.now().strftime('%Y-%m-%d')
    #    dt_fr=request.GET.get('date_from',dt)
    #    dt_to=request.GET.get('date_to',dt)        
    #    cur=connection.cursor()
    #    s="select a.id,app_nickname,app_headimgurl,to_char(tm,'yyyy-mm-dd HH24:MI'),c.name,"+\
    #        "w.wx_nickname,w.wx_headimgurl,a.cellpahone,a.realname,"+\
    #        "(select name from company_wxwalletstatus where id=status_id),"+\
    #        "coalesce((select name from company_company where id=c.parent_id),''),"+\
    #        "coalesce((select name from company_salesdivision where id=c.division_id),''),"+\
    #        "(select username from company_user wehre id=app_user_id) "+\
    #        "from "+\
    #        "(select * from company_wxwalletapplication "+sWhere+") a "+\
    #        "left outer join "+\
    #        "(select id,name,parent_id,division_id,sub_division_id from company_company) c "+\
    #        "on a.company_id=c.id "+\
    #        "left outer join "+\
    #        "(select id,wx_nickname,wx_headimgurl from company_wxopenid) w "+\
    #        "on a.app_wxopenid_id=w.id"
    #    cur.execute(s)
    #    for r in cur.fetchall():
    #        d.append([r[0],r[3],r[12]+"<img src='"+r[2]+"'/>"+r[1],
    #            r[9],r[10],r[11],r[4],
    #            "<img src='"+r[6]+"' />"+r[5],
    #            r[7],r[8],''
    #        ])   
    #    return render(request,'/sales/channelrebate/list_wxwallet_application.html',{'clmns':clmns,'data':d})
    else: 
        search_dict['company_id'] = int(company_id)
    for q in request.GET:
        if q == 'page':
            page = request.GET.get(q)
        else:
            search_dict[q] = request.GET.get(q)
    if obj[:4] == 'prd_':
        for plt in PrdPlant.objects.filter(company_id=int(company_id)).order_by('name'):
            plants.append([plt.id, plt.name])
        for w in PrdWorkshop.objects.filter(company_id=int(company_id)).order_by('name'):
            workshops.append([w.id, w.name])
    m = apps.get_model(d[obj][1], d[obj][2])
    orders = ['name']
    if obj == 'productprop':
        categories = CategoryProp.objects.filter(category=company.category)
        objects = m.objects.filter(**search_dict).order_by('value')
    else:
        #2020-03-17
        if obj=='agent':
            objects = m.objects.filter(**search_dict).exclude(id=company_id).order_by(','.join(orders))
        else:
            objects = m.objects.filter(**search_dict).order_by(','.join(orders))
    page_list = Paginator(objects, request=request, per_page=10)
    p_obj = page_list.page(page)

    #传后端的参数，不包含is_delete
    if 'is_delete' in search_dict:
        search_dict.pop('is_delete')

    #return render(request, 'list/obj/' + obj + '_list.html',
    #    {'obj': p_obj, 'page_list': page_list, 'plants': plants, 'workshops': workshops, 'disable_link': disable_link,
    #     'divisions':divisions, 'categories': categories, 'murl': '/company/' + obj + '/', 'params': SafeString(search_dict)}
    #)
    
    #2020-05-01
    return render(request, 'list/obj/' + obj + '_list.html',
        {'obj': p_obj, 'page_list': page_list, 'plants': plants, 'workshops': workshops, 'disable_link': disable_link,
         'divisions':divisions, 'categories': categories, 'murl': '/company/' + obj + '/', 'params': SafeString(search_dict)}
    )


#@login_required
@tj_login_required
@csrf_exempt
def create_user(request):
    c = request.user.company
    company_src = []
    groups=[]    
    is_saved = False

    if request.method == 'GET':
        form = UserForm()          

    elif request.method == "POST":
        group_id = request.POST['group_id']
        form = UserForm(request.POST)        
        if form.is_valid():
            u_name = form.cleaned_data.get('username')
            u_pwd = form.cleaned_data.get('password')            
            u = User(username=u_name, company=Company.objects.get(name=form.cleaned_data.get('company_name'),ancestor_id=c.ancestor_id))
            u.set_password(u_pwd)
            u.save()
            u_id = str(u.id)
            
            gs=Group.objects.filter(id=int(group_id))
            if gs:
                u.groups.add(gs[0])    

            is_saved = True

    for g in Group.objects.all():
        groups.append({'id':g.id,'name':g.name}) 

    s = "select id,name from logistics.company_company where id=" + str(c.id) + " or parent_id=" + str(c.id)
    #2020-01-05
    s = "select id,name from logistics.company_company where ancestor_id=" +  str(c.ancestor_id) 
    cur = connection.cursor()
    cur.execute(s)
    for r in cur.fetchall():
        company_src.append({'label': r[1], 'value': r[0]})
    cur.close()
    return render(request, 'company/create_user.html', {
        'form': form,
        'groups':groups,
        'is_saved':is_saved,
        'company_src': SafeString(company_src)
    })



#@login_required
@tj_login_required
def list_user00(request):
    search_dict = dict()
    c = request.user.company
    page = 1
    cur = connection.cursor()
    a = []
    name_like = ''
    for q in request.GET:
        if q == 'page':
            page = request.GET.get(q)
        elif q == 'username__contains':
            search_dict[q] = request.GET.get(q)
            name_like = " and username like '%" + search_dict[q] + "%'"
    s = "select username,tm," + \
        "(select name from company_company where id=company_id),"+\
        "(select name FROM auth_group where id=(select group_id FROM company_user_groups where user_id=a.id limit 1)),"+\
        "(select cell_phone from company_company where id=company_id) "+\
        " from company_user a " + \
        " where company_id in (select id from company_company where id=" + str(c.id) + \
        " or parent_id=" + str(c.id) + ")" + name_like    
    cur.execute(s)
    for r in cur.fetchall():
        a.append([r[0], r[2], r[1].strftime('%Y-%m-%d %H:%M:%S'),r[3],r[4]])
    cur.close()
    page_list = Paginator(a, request=request, per_page=10)
    p_obj = page_list.page(page)

    return render(request, 'list/obj/user_list.html',
                  {'obj': p_obj, 'page_list': page_list, 'params': SafeString(search_dict)}
    )


@tj_login_required    
def list_user(request):
    data=[]   
    perm_pwd=False
    if request.user.has_perm('company.sys_admin'):
        perm_pwd=True    
    c = request.user.company    
    cur = connection.cursor()   
    sWhere=" where company_id in (select id from company_company where id=" + str(c.id) + " or parent_id=" + str(c.id) + ")"    
    if c.level.sn==0:
        sWhere=" where ancestor_id="+str(c.id)
    s = "select username,tm," + \
        "(select name from company_company where id=company_id),"+\
        "(select name from company_level where id=(select level_id from  company_company where id=company_id)),"+\
        "coalesce((select name FROM auth_group where id=(select group_id FROM company_user_groups where user_id=a.id limit 1)),''),"+\
        "coalesce((select cell_phone from company_company where id=company_id),''),"+\
        "id "+\
        "from company_user a " +sWhere
    cur.execute(s)
    for r in cur.fetchall():
        uname=r[0]
        if perm_pwd:
            uname='<a class="account" uid="'+str(r[6])+'">'+r[0]+'</a>'
        data.append([uname, r[2], r[1].strftime('%Y-%m-%d %H:%M:%S'),r[3],r[4],r[5]])
    cur.close()  
    return render(request, 'list/obj/user_list.html', {'data': SafeString(data)})

@tj_login_required
def json_sub_sal_division(request, division_id):
    data = {'e':'', 'details': []}
    for d in SubSalDivision.objects.filter(division_id=int(division_id)):
        data['details'].append([d.id,d.name])
    return JsonResponse(data)

#2019-12-31 
def json_children(request, company_id):
    data = {'e':'', 'details': []}
    for d in Company.objects.filter(parent_id=int(company_id)):
        data['details'].append([d.id,d.name])
    #团购，零售
    for d in Company.objects.filter(parent_id=None,ancestor_id=None):
        data['details'].append([d.id,d.name])
    return JsonResponse(data)


def child_level(request,company_id):
    data={'level':{},'e':''}
    cs=Company.objects.filter(id=int(company_id))
    if cs:
        data['level']=cs[0].child_level()
    else:
        data['e']=_('公司ID错误')
    return JsonResponse(data)






def wx_longin(request):
    data={
        'e':'',
        'user_id':0,
        'user_name':'',
        'company_id':0,
        'company_name':'',
        'msg':''
    }
    usr=request.GET.get('user','')
    pwd=request.GET.get('password','')
    pwd0=request.GET.get('password_old','')
    pwd1=request.GET.get('password_new','')
    wx_openid=request.GET.get('wx_openid','')
    user=None            
    if usr and pwd:
        user = authenticate(username=usr, password=pwd)
        if user:
            if user.check_password(pwd):
                if user.is_active:
                    user.wx_openid=openid
                    user.save()
                    login(request, user)   
                else:
                    data['e'] = [_('此帐号已失效!!!!!')]
            else:
                data['e'] = [_('用户名或密码不正确!'), ]
        else:
            data['e'] = [_('用户名或密码不正确!'), ]
    else:        
        us=User.objects.filter(wx_openid=wx_openid)
        if us:
            user=us[0]
    if user:
        data['user_id']=user.id
        data['user_name']=user.username
        data['company_id']=user.company.id
        data['company_name']=user.company.name
        if pwd0 and pwd1:
            #修改密码
            user = authenticate(username=usr, password=pwd0)
            if user:
                if user.check_password(pwd0):
                    if user.is_active:
                        user.set_password(pwd1)
                        user.save()
                        data['msg']=_('密码修改成功')  
                    else:
                        data['e'] = [_('此帐号已失效!!!!!')]
                else:
                    data['e'] = [_('用户名或密码不正确!'), ]
            else:
                data['e'] = [_('用户名或密码不正确!'), ]            
    return JsonResponse(data)



@tj_login_required
def InitPassword(request,uid):
    data={'e':''}
    d={
        1:'jg123456',
        1644:'nc123456',
        16716:'yg123456'
    }
    u0=request.user
    if not u0.has_perm('company.sys_admin'):        
        data['e']=_('您当前没有此操作权限')
        return JsonResponse(data)

    

    us=User.objects.filter(id=int(uid))
    if us:
        u=us[0]
        if u.ancestor_id!=u0.ancestor_id:
            data['e']=_('您当前帐号层级错误')
            return JsonResponse(data)

        if u0.ancestor_id in d:
            pwd=d[u0.ancestor_id]
        else:
            pwd='tj123456'
        u.set_password(pwd)
        u.save()
    else:
        data['e']=_('系统无此用户帐号')
    return JsonResponse(data)


#2020-05-15 终端店注册，返回一级经销商信息
def app_get_agent(request,company_id):
    data={'e':'','data':[]}
    k = sK   
    e = time_key_check(request, k)
    if e:
        data['e'] = e
        return JsonResponse(data)   
    cur=connection.cursor()
    s="select id,name from logistics.company_company where parent_id="+company_id+" and is_delete=false"+\
        " and (right(name,2) not in ('片区','战区','费用')) and (left(name,4) not in  ('业务费用','业务用酒'))"+\
        " and (left(name,2) not in ('零售')) and level_id=2 and name<>'公司总部'"
    cur.execute(s)
    for r in cur.fetchall():    
        data['data'].append({'id':r[0],'name':r[1]})
    return JsonResponse(data)


@csrf_exempt
def upload_register_img(request,what):
    data={'e':''}
    e = time_key_check(request, sK)
    if e:
        data['e'] = e
        return JsonResponse(data)
    if what not in ['signboard','license']:
        data['e']='wrong url'
        return JsonResponse(data) 
    id=request.POST.get('id','')
    if not id:
        data['e']='id missing'
        return JsonResponse(data)

    ts=TerminalRegister.objects.filter(id=int(id))
    if not ts:
        data['e'] = 'id not exists'
        return JsonResponse(data)
    t=ts[0]
    name=t.name
    a=Company.objects.get(id=t.ancestor_id)
    p = settings.MEDIA_ROOT + '\\' + a.name + '\\register\\'+t.parent.name    
    if not os.path.exists(p):
        os.makedirs(p)
    fp = a.name + '/register/' + t.parent.name +"/"    
    #微信小程序每次只能传1张图片
    img=request.FILES.get('file','')
    if not img:   
        data['e']='未上传图片file'
        return JsonResponse(data)
    fn=name+'_'+what+'.'+img.name.split('.')[-1]
    with open(p +'\\'+ fn, 'wb+') as destination:
        for chunk in img.chunks():
            destination.write(chunk)
    cur=connection.cursor()
    cur.execute("update logistics.company_terminalregister set img_"+what+"='"+fp+fn+"' where id="+id)

    #2020-12-18
    if what=='license':        
        ancestor=a
        if ancestor.id==1644:
            if ancestor.cfg_company.terminal_register_automatic_approve:
                r=terminal_register_approve_automatic(id) 
                
    return JsonResponse(data) 


@csrf_exempt
def terminal_register(request):
    data={'e':'','id':0}
    errs_missing={
        'parent_id': '未指定上级公司',
        'name':'终端店名称不能为空',
        'contacts':'联系人不能为空',
        'cell_phone':'手机号码不能为空',
        'address':'终端店地址不能为空',
        'openid':'wx_openid不能为空'
    }  
    
    #e = time_key_check(request, sK)
    #if e:
    #    data['e'] = e
    #    return JsonResponse(data)



    err=[]
    r=json.loads(request.body)
    for k,v in errs_missing.items():
        if k not in r:
            err.append(v)
    if err:
        data['e']=",".join(err)
        return JsonResponse(data)      
    parent_id=r['parent_id']
    name=r['name']
    contacts=r['contacts']
    cell_phone=r['cell_phone']
    address=r['address']
    openid=r['openid']
    lng_lat=[]
    if 'lng_lat' in r:
        lng_lat=r['lng_lat']
    license_number=None
    if 'license_number' in r:
        license_number=r['license_number']
    parents=Company.objects.filter(id=int(parent_id))   
    if not parents:
        data['e']='上级经销商ID不存在' 
        return JsonResponse(data)  
    parent=parents[0]    
    #2020-05-15 目前上级为一级经销商
    if parent.level_id!=2:
        if parent.ancestor_id!=19318:
            data['e']='上级经销商层级错误'  
            return JsonResponse(data) 
    ancestors=Company.objects.filter(id=parent.ancestor_id)
    if not  ancestors:
        data['e']='经销商族系错误'  
        return JsonResponse(data)
    if TerminalRegister.objects.filter(name=name,ancestor_id=parent.ancestor_id):
        data['e']='请不要重复注册！'
        return JsonResponse(data)
    #2020-05-15 确保公司族系内公司名称唯一
    if Company.objects.filter(name=name,ancestor_id=parent.ancestor_id):
        data['e']='您要申请注册的终端店名称已存在！'
        return JsonResponse(data)
    t=TerminalRegister.objects.create(name=name,wx_openid=openid,contacts=contacts,address=address,lng_lat=lng_lat,ancestor_id=ancestors[0].id,
        cell_phone=cell_phone.replace(' ',''),parent_id=parent.id,license_number=license_number)

    if parent.ancestor_id==19318 or parent.ancestor_id==20445: 
        terminal_register_approve_automatic(str(t.id)) 

    data['id']=t.id  

    return JsonResponse(data)  


@tj_login_required
def list_agent_order(request):
    dt=datetime.datetime.now().strftime('%Y-%m-%d')
    dt_fr=request.GET.get('date_from',dt)
    dt_to=request.GET.get('date_to',dt)
    sWhere=" where (dt between '"+dt_fr+"' and '"+dt_to+"')"
    u=request.user
    c=u.company    
    if c.level_id==1:
        sWhere=sWhere+" and ancestor_id="+str(c.id)
        clmns=[_('下单时间'),_('订单号'),_('订货人'),_('订货公司名称'),_('供货公司名称'),_('产品名称及数量'),_('期望到货日期'),_('订单完结日期')]
        s="(select name from company_company where id=vendor_id),"
    else:
        sWhere=sWhere+" and vendor_id="+str(c.id)
        clmns=[_('下单时间'),_('订单号'),_('下单人'),_('下单公司名称'),                  _('产品名称及数量'),_('期望到货日期'),_('订单完结日期')]
        s=""    
    sSql="select to_char(tm,'yyyy-mm-dd HH24:MI'),code,(select username from company_user where id=user_id),"+\
        "(select name from company_company where id=company_id),"+s+"array_to_string(array(select "+\
            "(select name from product_product where id=product_id)||"+\
                "case when qty_box>0 then qty_box||'箱' else '' end||case when qty_bottle>0 then qty_bottle||'瓶' else '' end "+\
            "from company_agentorderdetails where batch_id=a.id),','),"+\
        "coalesce(to_char(dt_expect,'yyyy-mm-dd'),''),coalesce(to_char(tm_complete,'yyyy-mm-dd'),''),id "+\
        "from company_agentorderbatch a"+sWhere
    cur=connection.cursor()
    cur.execute(sSql)
    data=[]    
    for r in cur.fetchall():
        if c.level_id==1:
            data.append([ r[0],r[1],r[2],r[3],r[4],r[5],r[6],r[7] ])
        else:
            #2020-05-28
            o=r[6] 
            if not r[6]:
                o="<a class='btn-close-order' order_id="+str(r[7])+" code='"+r[1]+"' href='#'>关闭订单</a>"
            data.append([ r[0],r[1],r[2],r[3],r[4],r[5],o])    
    return render(request,'company/list_agent_order.html',{'data':data,'clmns':clmns,'date_from':dt_fr,'date_to':dt_to})
      


@tj_login_required
def list_agent_order_review(request):
    d = datetime.datetime.now()
    dt = d.strftime('%Y-%m-%d')
    dt_fr = request.GET.get('date_from', (d + datetime.timedelta(days=-7)).strftime('%Y-%m-%d'))
    dt_to = request.GET.get('date_to', dt)
    sWhere = " where (dt between '" + dt_fr + "' and '" + dt_to + "') and voucher_id=0"
    u = request.user
    c = u.company 
    sWhere = sWhere + " and ancestor_id=" + str(c.id)
    clmns = [_('下单时间'), _('订单类型'), _('订单号'), _('下单人'), _('下单方式'), _('下单公司名称'),
                 _('产品名称及数量'), _('期望到货日期'), _('订单完结日期'), _('审核信息'), _('操作')]
    sSql = (
        "select to_char(tm,'yyyy-mm-dd HH24:MI'),code,(select username from company_user where id=user_id),"
        "(select name from company_company where id=company_id),"
        "array_to_string(array(select (select name from product_product where id=product_id)|| case when qty_box>0 then qty_box||'箱' else '' end||case when qty_bottle>0 then qty_bottle||'瓶' else '' end from company_agentorderdetails where batch_id=a.id ),','),"
        "coalesce(to_char(dt_expect,'yyyy-mm-dd'),''),"
        "coalesce(to_char(tm_complete,'yyyy-mm-dd'),''),id,review_company_id,"
        "(select name from company_company where id=review_company_id),voucher_id,(select username from company_user where id=helper_id)"
        " from company_agentorderbatch a "+sWhere)
    cur = connection.cursor()
    cur.execute(sSql)
    data = []
    for r in cur.fetchall():
        o = r[6]
        if not o:
            o = '尚未完成'

        voucher_id = r[10]
        voucher_type = ''
        helper = r[11]
        if voucher_id:
            if voucher_id == 4:
                voucher_type = '退货' 
            elif voucher_id == 9:
                voucher_type = '换货'
            elif voucher_id == 0:
                voucher_type = '线上订单' 

        else:
            voucher_type = '线上订单'
        if helper:
            helper = helper + '代下单'
        else:
            helper = '自助下单'



        review_company_id = r[8]
        order_id = r[7]
        s = r[9] 
        if not s:
            s = '已审核'
        else: 
            if c.id == review_company_id: 
                s = "<a class='btn-close-order btn btn-danger btn-xs' order_id=" +str(order_id) + " code='" + r[1] + "' href='#'>审核订单</a>"
                
        
        products = r[4] 

        data.append([r[0],voucher_type, r[1], r[2], helper, r[3], products, r[5], o, s,"<a class='btn btn-default btn-xs' onclick='viewDetail("+str(order_id)+")'>详情</a>"])
    return render(request, 'company/list_agent_order.html',
                  {'data': data, 'clmns': clmns, 'date_from': dt_fr, 'date_to': dt_to})



@tj_login_required
def list_agent_order_return(request):
    d = datetime.datetime.now()
    dt = d.strftime('%Y-%m-%d')
    dt_fr = request.GET.get('date_from', (d + datetime.timedelta(days=-7)).strftime('%Y-%m-%d'))
    dt_to = request.GET.get('date_to', dt)
    sWhere = " where (dt between '" + dt_fr + "' and '" + dt_to + "') and (voucher_id=4 or voucher_id= 9)"
    u = request.user
    c = u.company 
    sWhere = sWhere + " and ancestor_id=" + str(c.id)
    clmns = [_('下单时间'), _('订单类型'), _('订单号'), _('下单人'), _('下单方式'), _('下单公司名称'),
                 _('退货产品'), _('换货产品'), _('订单完结日期'), _('审核信息'), _('操作')]
    sSql = (
        "select to_char(tm,'yyyy-mm-dd HH24:MI'),code,(select username from company_user where id=user_id),"
        "(select name from company_company where id=company_id),"
        "array_to_string(array(select (select name from product_product where id=product_id)|| case when qty_box>0 then qty_box||'箱' else '' end||case when qty_bottle>0 then qty_bottle||'瓶' else '' end from company_agentorderdetails where batch_id=a.id and voucher_id!=9),','),"
        "coalesce(to_char(dt_expect,'yyyy-mm-dd'),''),"
        "coalesce(to_char(tm_complete,'yyyy-mm-dd'),''),id,review_company_id,"
        "(select name from company_company where id=review_company_id),voucher_id,(select username from company_user where id=helper_id),"
        "array_to_string(array(select (select name from product_product where id=product_id)|| case when qty_box>0 then qty_box||'箱' else '' end||case when qty_bottle>0 then qty_bottle||'瓶' else '' end from company_agentorderdetails where batch_id=a.id and voucher_id=9),',')"
        " from company_agentorderbatch a "+sWhere)
    cur = connection.cursor()
    cur.execute(sSql)
    data = []
    for r in cur.fetchall():
        o = r[6]
        if not o:
            o = '尚未完成'

        voucher_id = r[10]
        voucher_type = ''
        helper = r[11]
        if voucher_id:
            if voucher_id == 4:
                voucher_type = '退货' 
            elif voucher_id == 9:
                voucher_type = '换货'
            elif voucher_id == 0:
                voucher_type = '线上订单' 

        else:
            voucher_type = '线上订单'
        if helper:
            helper = helper + '代下单'
        else:
            helper = '自助下单'



        review_company_id = r[8]
        order_id = r[7]
        s = r[9] 
        if not s:
            s = '已审核'
        else: 
            if c.id == review_company_id: 
                s = "<a class='btn-close-order btn btn-danger btn-xs' order_id=" +str(order_id) + " code='" + r[1] + "' href='#'>审核订单</a>"
                
        
        products = r[4]
        product_change = r[12] 

        data.append([r[0],voucher_type, r[1], r[2], helper, r[3], products,product_change, o, s,"<a class='btn btn-default btn-xs' onclick='viewDetail("+str(order_id)+")'>详情</a>"])
    return render(request, 'company/list_agent_order.html',
                  {'data': data, 'clmns': clmns, 'date_from': dt_fr, 'date_to': dt_to})


#订单审核详情
@tj_login_required
def list_agent_order_review_detail(request,id=None): 
    data = {
        'e':'',
        'reviews':[],
        'review_person':{
            'company':'', 
            'phone':''
        },
        'tm_complete':'' 
    }
    if id:
        ag = AgentOrderBatch.objects.filter(id=int(id))
        if ag:
            ag = ag[0] 
            if ag.tm_complete:
                data['tm_complete'] = ag.tm_complete.strftime("%Y-%m-%d %H:%M:%S")
            if ag.review_company:
                data['review_person']['company'] = ag.review_company.name  
                data['review_person']['phone'] = ag.review_company.cell_phone 
            reviews = AgentOrderReview.objects.filter(batch_id=ag.id)
            if reviews: 
                for r in reviews:
                    data['reviews'].append({
                        'company':r.company.name,
                        'name':r.user.username,
                        'tm':r.tm.strftime("%Y-%m-%d %H:%M:%S")
                    })
            if ag.batch_code:
                cur = connection.cursor()
                cur.execute("select tm from inventory_batch where code = '"+ag.batch_code+"' ")
                r = cur.fetchone()
                if r and r[0]:
                    data['fh_tm'] = r[0].strftime("%Y-%m-%d %H:%M:%S")

    return render(request,"company/list_agent_order_detail.html",data)



@tj_login_required
def close_agent_order(request,id):
    if request.user.company.id == 19257:
        ao = AgentOrderBatch.objects.filter(id=int(id))
        if ao:
            ao = ao[0]   
            ao.review_company_id = None
            a = AgentOrderReview(   
                batch=ao, 
                company_id=request.user.company.id,
                user_id=request.user.id
            )
            if ao.voucher_id == 4 or ao.voucher_id == 9:
                ao.tm_complete = now()
            a.save() 
            ao.save()
        return JsonResponse({'e': '', 'cnt': 1})
    else: 
        cur = connection.cursor()
        cur.execute(
            "update company_agentorderbatch set tm_complete=now() where id=" + id)
        cnt = cur.rowcount
        cur.close()
        return JsonResponse({'e': '', 'cnt': cnt})



# 给扫码枪用的订单接口
@csrf_exempt
def list_agent_order_data(request):
    data = {
        'e':'',
        'records':[

        ]
    }
    company_id = request.GET.get('company_id','')
    if company_id:
        orders = AgentOrderBatch.objects.filter(vendor_id=int(company_id),review_company_id=None,voucher_id=0,batch_code=None ).order_by('-id')
        if orders:
            for order in orders:
                record = {
                    'code':'',
                    'company_name':'',
                    'company_id':'',
                    'user_name':'',
                    'tm':'',
                    'dt_expect':'',
                    'remark':'',
                    'products':[]
                }
                record['code'] = order.code
                record['company_name'] = order.company.name
                record['company_id'] = order.company_id
                record['user_name'] = order.user.username
                record['tm'] = order.tm.strftime("%Y-%m-%d %H:%M:%S")
                record['dt_expect'] = order.dt_expect.strftime("%Y-%m-%d")
                record['remark'] = order.remark

                products = AgentOrderDetails.objects.filter(batch_id=order.id)
                if products:
                    for p in products:
                        record['products'].append({
                            'id':p.product_id,
                            'name':p.product.name,
                            'qty_box':p.qty_box,
                            'qty_bottle':p.qty_bottle
                        })



                data['records'].append(record)
    else:
        data['e'] = '数据丢失'

    return JsonResponse(data)




'''
def agent_order(request,id=None):
    if not id:
        obj=AgentOrderBatch()
        obj.user = request.user
        obj.company = request.user.company
        obj.ancestor_id = request.user.company.ancestor_id
        obj.vendor=request.user.company.parent
    else:
        obj=AgentOrderBatch.objects.get(id=int(id))
    is_saved=False
    if request.method == 'GET':        
        form = AgentOrderBatchForm(instance=obj)
        formset = AgentOrderDetailsFormset(instance=obj)          
    else:
        form = AgentOrderBatchForm(request.POST, instance=obj)
        formset = AgentOrderDetailsFormset(request.POST, instance=obj)
        if form.is_valid() and formset.is_valid():
            form.save()
            formset.save()
            is_saved=True
    return render(request, 'company/agent_order.html', {'form': form, 'formset': formset, 'is_saved': is_saved})


@csrf_exempt
#wx_app
def app_agent_order(request):
    data={'e':''}
    k = sK   
    e = time_key_check(request, k)
    if e:
        data['e'] = e
        return JsonResponse(data)
    err=[]
    #接收JSON数据
    r=json.loads((request.body).decode('utf-8'))
    if 'company_id' not in r or 'user_id' not in r or 'code' not in r:
        data['e']='code,company_id,user_id missing'
        return JsonResponse(data)

    if 'orders' not in r:
        data['e']='缺少订单明细数据:orders'
        return JsonResponse(data)    
    company_id=int(r['company_id'])
    user_id=int(r['user_id'])
    code=r['code']    
    cs=Company.objects.filter(id=int(company_id))
    if cs:
        c=cs[0]
    else:
        data['e']='company_id error'
        return JsonResponse(data)
    vendor_id=c.parent_id
    date_expect=None
    remark=None
    if 'vendor_id' in r['vendor_id']:
        vendor_id=int(r['vendor_id'])
    if 'date_expect' in r:
        dt_expect=r['date_expect']
    if 'remark' in r:
        remark=r['remark']
    order_batch=AgentOrderBatch.objects.create(code=code,compan_id=company_id,user_id=user_id,vendor_id=vendor_id,ancestor_id=c.ancestor_id,dt_expect=dt_expect,remark=remark)
    
    #订单明细
    a=[]
    for o in r['orders']:
        a.append("("+str(c.ancestor_id)+","+str(c.id)+str(vendor_id)+","+str(order_batch)+
            str(o.product_id)+","+str(o.qty)+")"
        )
    s="INSERT INTO logistics.company_agentorderdetails(ancestor_id,company_id,vendor_id,batch_id,product_id,qty) values "+",".join(a)
    cur=connection.cursor()
    cur.execute(s)
    return JsonResponse(data)
'''


@tj_login_required
def list_terminal_register(request):
    data={'clmns':[],'data':[]}
    d = datetime.datetime.now()
    dt = d.strftime('%Y-%m-%d')
    dt_fr = request.GET.get('date_from', (d + datetime.timedelta(days=-7)).strftime('%Y-%m-%d')) 
    dt_to=request.GET.get('date_to',dt)  
    data['date_from']=dt_fr
    data['date_to']=dt_to
    u=request.user
    c=u.company
    perm=False
    if u.has_perm('company.sys_admin'):
        perm=True 
    cur=connection.cursor()
    if c.level_id==1:
        sWhere="where ancestor_id="+str(c.ancestor_id)+" and (dt between '"+dt_fr+"' and '"+dt_to+"')"
    elif c.is_expense_account:        
        sWhere=" where (select division_id from company_company where id=a.parent_id)="+str(c.division_id)
    else:
        sWhere=" where parent_id="+str(c.id)    
    data['clmns']=[_('终端店名称'),_('联系人'),_('手机号码'),_('详细地址'),_('营业执照编号'),_('父级公司'),_('申请注册时间'),_('审核通过时间')]
    s="select id,name,contacts,cell_phone,address,coalesce(license_number,''),"+\
        "(select name from company_company where id=a.parent_id),"+\
        "to_char(tm,'yyyy-mm-dd HH24:MI'),case when tm_aprv is null then '' else "+\
            "to_char(tm_aprv,'yyyy-mm-dd HH24:MI')||' ('||(select username  from company_user where id=user_aprv_id)||')' end "+\
        "FROM company_terminalregister a "+sWhere+" ORDER BY tm_aprv desc"
    cur.execute(s)
    for r in cur.fetchall():
        if r[8]:
            if perm:
                prv="<a href='/company/register/approve/"+str(r[0])+"/?view=1' >"+r[8]+"</a>"
            else:
                prv=r[8]
        else:
            if perm:
                prv="<a href='/company/register/approve/"+str(r[0])+"/' >现在审核</a>"
            else:
                prv=_("'未审核'")
        data['data'].append([ r[1],r[2],r[3],r[4],r[5],r[6],r[7],prv ])
    return render(request, 'company/list_terminal_register.html', data)        


#2020-12-18 终端店注册自动审核
def terminal_register_approve_automatic(termianl_reg_id,apprv_id=1):
    id=termianl_reg_id
    pwd='123456'
    data={'company':'','username':'','password':'','e':''}   
    obj=TerminalRegister.objects.get(id=int(id)) 
    try:    
        data['company']=obj.name
        u=obj.contacts
        phone=obj.cell_phone    
        c=Company(name=obj.name,parent=obj.parent,address=obj.address,license_number=obj.license_number,category_id=obj.parent.category_id,contacts=u,lng_lat=obj.lng_lat)
        c.is_terminal = True
        c.save()        
        cur=connection.cursor()
        cur.execute("SELECT count(*) FROM company_user where username like '%"+phone+"%'")
        r=cur.fetchone()
        fx=''
        if r[0]:
            fx=chr(r[0]-1+ord('A'))    
        acc=phone+fx        
        uu=User(
            username=acc,
            last_name=u,
            company=c,
            ancestor_id=c.ancestor_id,
            phone=phone,
        )    
        uu.set_password(pwd)
        #创建帐号    
        uu.save()
        
            
        data={'company':obj.name,'username':acc,'password':pwd,'e':''}
        #添加经销商功能组别  5;"经销商"
        uu.groups.add(5)
        #修改审核状态，添加审核人
        obj.tm_aprv=datetime.datetime.now()        
        obj.user_aprv_id=int(apprv_id)
        obj.save() 
        
        #2021-01-16
        cfg=CompanyCfg.objects.get(company_id=c.ancestor_id)
        app_name=cfg.app_name
        sms_sign=cfg.sms_sign
        send_sms(phone,"您在"+app_name+"已成功注册终端店，帐号："+acc+", 密码："+pwd+"。" ,sms_sign)  
    except Exception:    
        data['e']='审核失败，请与系统管理员联系'
        send_sms('13976686507','终端店审核失败:'+obj.name,sms_sign)
    return data

       
@tj_login_required
def terminal_register_approve(request,id):   
    data={'company':'','username':'','password':'','e':''}
    obj=TerminalRegister.objects.get(id=int(id))    
    approved=request.GET.get('approved','')   
    if approved:  
        data=terminal_register_approve_automatic(id,request.user.id)   
        return JsonResponse(data)
    return render(request,'company/terminal_register.html', {'obj':obj}) 




'''
@tj_login_required
def auth_product_query(request,id=None):    
    if id:
        obj=AuthQuery.objects.get(id=int(id))
    else:
        obj=AuthQuery()
    data={'e':'','obj':obj}
    if request.method=='POST':        
        u_id=request.POST.get('user_id')
        what=request.POST.get('what')
        content=request.POST.get('content')
        obj.what=what
        obj.user_id=int(u_id)
        obj.save()        
    else:
        if id:
            m = apps.get_model('product',what)
'''


#2020-09-10
@tj_login_required
def ajax_parent_levels(request,id):
    lv=Level.objects.get(id=int(id))
    ps=[]
    k=lv.sn-1
    while True:
        plv=Level.objects.filter(sn=k)
        if plv:
            p=plv[0]
            if p.id>1:
                ps.append({'id':p.id,'name':p.name,'sn':p.sn,'rec':lv.sn-p.sn})
            else:
                break
        else:
            break
        k=k-1
    return JsonResponse({"parents":ps})


#2020-11-24
@login_required
def create_enterprise(request):
    is_saved=False
    obj=Company()
    cats=[]
    for cat in Category.objects.all():
        cats.append([cat.id,cat.name])
    if request.method == 'GET':           
        form = AgentForm(instance=obj, user=request.user)           
    else:         
        form = AgentForm(request.POST, instance=obj, user=request.user)   
        if form.is_valid():
            c=form.save()
            c.hierarchy_id=c.id
            c.parent_id=c.id
            c.ancestor_id=c.id
            c.hierarchy_id=c.id
            c.level_id=1
            c.img_logo="logo/logo_tj.png"
            c.save()
            acc=request.POST.get('user_acc','')
            pwd=request.POST.get('user_pwd','')           
            u=User(company_id=c.id,ancestor_id=c.id,username=acc,last_name=acc)
            u.set_password(pwd)
            u.save()            
            g=Group.objects.get(id=4)                        
            u.groups.add(g)     

            cfg=CompanyCfg(company_id=c.id)
            cfg.save()

            #2021-04-24 写入企业功能组别
            for g in Group.objects.all():
                cg=CGroup(company_id=c.id,name=g.name,group_id=g.id)
                cg.save()

            #2021-09-11 系统管理员帐号添加企业功能组别 4;"系统管理员"
            ga=CGroup.objects.get(company_id=c.id,group_id=4)            
            u.cgroup_ids=[{'id':ga.id,'name':ga.name}]
            u.save()
            return  redirect("/obj/list/companycfg/")   

    return render(request, 'obj/enterprise.html', {'form': form, 'is_saved':is_saved,'cats':cats})        
   
@login_required
def delete_demo_stk_batch(request):
    data={'e':'','data':[]}
    #酒企演示帐号
    id=16687
    
    cur=connection.cursor()
    
    for b in Batch.objects.filter(ancestor_id=id):
        s="select a.id,code,"+\
            "(select name from company_company where id=a.company_fr_id),"+\
            "(select name from company_company where id=a.company_to_id),"+\
            "(select name from inventory_voucher where id=a.vtype_id),"+\
            "(select name from product_product where id=a.product_id),"+\
            "to_char(tm,'yyyy-mm-dd HH24:MI:SS') from "+\
            "(select id,code,company_fr_id,company_to_id,vtype_id,user_id,product_id,tm "+\
                "from inventory_batch where id="+str(b.id)+") a"   
        cur.execute(s)
        r=cur.fetchone()        
        data['data'].append({
            'id':r[0],
            'code':r[1],
            'company':r[2],
            'agent':r[3],
            'voucher':r[4],
            'product':r[5],
            'time':r[6]
        })
        if request.method=='POST':
            b.delete()       
        
    cur.close()   
    return render(request, 'obj/delete_demo_stk_batch.html', {'data': data})  


@login_required
def edit_staffdiv(request,id=None,act=None):
    if act=='delete':
        if id:
            obj=StaffSaldiv.objects.get(id=int(id))
            obj.delete()
            return JsonResponse({'e':''})

    c=request.user.company
    is_saveed=False
    if id:
        obj=StaffSaldiv.objects.get(id=int(id))
    else:
        obj=StaffSaldiv(company_id=c.id)
    agents=[]
    subsaldivs=[]
    saldivs=[]

    sel_saldivs=[]

    if request.method == 'GET':
        for d in SalesDivision.objects.filter(company_id=c.id).order_by('name'):
            sel_saldivs.append([d.id,d.name])

        form = StaffSaldivForm(instance=obj)  
        if id:
            if obj.saldiv_ids:
                for i in obj.saldiv_ids:
                    objs=SalesDivision.objects.filter(id=i)
                    if objs:
                        o=objs[0]
                        saldivs.append([o.id,o.name])
            if obj.subsaldiv_ids:
                for i in obj.subsaldiv_ids:
                    objs=SubSalDivision.objects.filter(id=i)
                    if objs:
                        o=objs[0]
                        subsaldivs.append([o.id,o.name])
            if obj.agent_ids:
                for i in obj.agent_ids:
                    o=Company.objects.get(id=i)
                    agents.append([o.id,o.name])
    elif request.method == "POST":
        form = StaffSaldivForm(request.POST, instance=obj)
        if form.is_valid():
            o=form.save()
            is_saved = True
            return redirect('/company/list/staffdiv/')
    return render(request, 'company/staff_saldiv_edit.html', 
        {'form': form,'saldivs':saldivs,'agents':agents,'subsaldivs':subsaldivs,'sel_saldivs':sel_saldivs}) 


@login_required
def list_staffdiv(request,id=None):
    u=request.user
    cur=connection.cursor()
    data=[]
    clmns=[_('用户帐号'),_('用户姓名'),_('功能组别'),_('战区'),_('分区'),_('经销商')]
    s="select a.id,"+\
        "(select username from company_user where id=a.user_id),"+\
        "(select last_name from company_user where id=a.user_id),"+\
        "array_to_string(array(select name from auth_group where id in (select unnest(group_ids))),','),"+\
        "array_to_string(array(select name from company_salesdivision where id in (select unnest(saldiv_ids))),','),"+\
        "array_to_string(array(select name from company_subsaldivision where id in (select unnest(subsaldiv_ids))),','),"+\
        "array_to_string(array(select name from company_company where id in (select unnest(agent_ids))),',') "+\
        "from "+\
        "(select id,user_id,to_char(tm,'yyyy-mm-dd HH24:MI:SS') tm,"+\
            "group_ids,saldiv_ids,subsaldiv_ids,agent_ids from company_staffsaldiv "+\
            "where company_id="+str(u.company_id)+\
        ") a"

    s="select a.id,"+\
        "(select username from company_user where id=a.user_id),"+\
        "(select last_name from company_user where id=a.user_id),"+\
        "group_names,"+\
        "array_to_string(array(select name from company_salesdivision where id in (select unnest(saldiv_ids))),','),"+\
        "array_to_string(array(select name from company_subsaldivision where id in (select unnest(subsaldiv_ids))),','),"+\
        "array_to_string(array(select name from company_company where id in (select unnest(agent_ids))),',') "+\
        "from "+\
        "(select id,user_id,to_char(tm,'yyyy-mm-dd HH24:MI:SS') tm,"+\
            "array_to_string(array(select name from auth_group where id in "+\
                "(select group_id from company_user_groups where user_id=c.user_id)),',') group_names,"+\
            "saldiv_ids,subsaldiv_ids,agent_ids from company_staffsaldiv c "+\
            "where company_id="+str(u.company_id)+\
        ") a"


    cur.execute(s)
    for r in cur.fetchall():
        data.append([
            '<a href="/company/edit/staffdiv/'+str(r[0])+'/">'+r[1]+'</a>',
            r[2],r[3],r[4],r[5],r[6]
        ])
    #return JsonResponse(data)
    return render(request,'company/list_staff_saldiv.html',  {'data':data,'clmns':clmns})



#2021-02-04 提交申请 处理程序 
def application_submit00(user_id,company_id,content_type,info):
    data={'e':''}   
    c=Company.objects.get(id=company_id)   
    u=User.objects.get(id=user_id)  
    if u.company_id!=company_id:
        data['e']='company, user not match'
        return data
    m=apps.get_model('company', content_type)
    app_id=info['app_id']
    if not app_id:
        if content_type in ['wxwalletapplication','bankinfoapplication']:
            #如果有未申批完成的申请，不能提交新申请
            if m.objects.filter(company_id=company_id,user_id=user_id).exclude(status_id__in=[5,6]).all():
                data['e']='当前有申请还在审核过程中，请不要重复申请。'
                return data
    if app_id:
        app=m.objects.get(id=app_id)
        if content_type in ['wxwalletapplication','bankinfoapplication']:
            app.status_id==4
    else:
        app=m()    
    app.ancestor_id=c.ancestor_id
    app.company_id=info['company_id']
    app.user_id=info['user_id']   
    app.wxopenid_id=info['wxopenid_id']
    app.nickname=info['nickname']
    app.headimgurl=info['headimgurl']

    if content_type=='wxwalletapplication':
        app.payee_company_id=info['payee_company_id']
        app.payee_wxopenid_id=info['payee_wxopenid_id']
        app.realname=info['realname']
        app.cellphone=info['cellphone']
    elif content_type=='bankinfoapplication':
        app.payee_company_id=info['payee_company_id']            
        app.bank_name=info['bank_name']           
        app.bank_account=info['bank_account']            
        app.payee=info['payee']            
    app.save()
    return data


#2021-02-20 提交申请 处理程序 
def application_submit(user_id,company_id,content_type,info):
    data={'e':''}   
    c=Company.objects.get(id=company_id)   
    u=User.objects.get(id=user_id)  
    
    if u.company_id!=company_id:
        data['e']='company, user not match'
        return data


    m=apps.get_model('company', content_type)
    if info['application_id']:
        app=m.objects.get(id=info['application_id'])
        app.status_id=4
        app.dt=datetime.datetime.now()
        app.tm=datetime.datetime.now()
        #删除已有日志?   
        for l in ApplicationReviewLog.objects.filter(content_type='wxwalletapplication',obj_id=app.id):
            l.delete()
    else:
        app=m() 
    app.ancestor_id=c.ancestor_id
    app.company_id=info['company_id']
    app.user_id=info['user_id']   
    app.wxopenid_id=info['wxopenid_id']
    app.nickname=info['nickname']
    app.headimgurl=info['headimgurl']

    if content_type=='wxwalletapplication':
        app.payee_company_id=info['payee_company_id']
        app.payee_wxopenid_id=info['payee_wxopenid_id']
        app.realname=info['realname']
        app.cellphone=info['cellphone']
    elif content_type=='bankinfoapplication':
        app.payee_company_id=info['payee_company_id']            
        app.bank_name=info['bank_name']           
        app.bank_account=info['bank_account']            
        app.payee=info['payee']            
    app.save()

    '''
    app_id=info['app_id']
    if not app_id:
        if content_type in ['wxwalletapplication','bankinfoapplication']:
            #如果有未申批完成的申请，不能提交新申请
            if m.objects.filter(company_id=company_id,user_id=user_id).exclude(status_id__in=[5,6]).all():
                data['e']='当前有申请还在审核过程中，请不要重复申请。'
                return data
    if app_id:
        app=m.objects.get(id=app_id)
        if content_type in ['wxwalletapplication','bankinfoapplication']:
            app.status_id==4
    else:
        app=m()    
    app.ancestor_id=c.ancestor_id
    app.company_id=info['company_id']
    app.user_id=info['user_id']   
    app.wxopenid_id=info['wxopenid_id']
    app.nickname=info['nickname']
    app.headimgurl=info['headimgurl']

    if content_type=='wxwalletapplication':
        app.payee_company_id=info['payee_company_id']
        app.payee_wxopenid_id=info['payee_wxopenid_id']
        app.realname=info['realname']
        app.cellphone=info['cellphone']
    elif content_type=='bankinfoapplication':
        app.payee_company_id=info['payee_company_id']            
        app.bank_name=info['bank_name']           
        app.bank_account=info['bank_account']            
        app.payee=info['payee']            
    app.save()
    '''
    return data



# 企业申请审批流 小程序(前端)接口
@csrf_exempt
def application_submit_app(request):
    data={'e':''}     
    e = time_key_check(request, sK)
    if e:
        data['e'] = e
        return JsonResponse(data)
    r=json.loads(request.body)
    e=[]    
    if 'user_id' not in r:
        e.append('user id missing')
    if 'content_type' not in r:
        e.append('content_type')
    if 'company_id' not in r:
        e.append('company id missing')
    if 'wxopenid' not in r:
        e.append('wxopenid  missing')
    else:
        openids=WxOpenid.objects.filter(wx_openid=r['wxopenid'])
        if not openids:
            e.append('wxopenid not match')
    if 'wxnickname' not in r:
        e.append('wxnickname missing')
    if 'headimgurl' not in r:
        e.append('headimgurl missing')
    
    if e:
        data['e']=','.join(e)
        return JsonResponse(data)
    content_type=r['content_type']
    user_id=int(r['user_id'])
    company_id=int(r['company_id'])
    wxopenid_id=openids[0].id
    wxnickname=r['wxnickname']
    headimgurl=r['headimgurl']
    app_id=0
    if 'application_id' in r:
        app_id=int(r['application_id'])    
    if content_type=='wxwalletapplication':
        #app_name='微信钱包审核'
        payeeopenids=openids=WxOpenid.objects.filter(wx_openid=r['payee_wxopenid'])
        if not payeeopenids:
            data['e']='wxopenid not match'
            return JsonResponse(data)
        else:
            payee_wxopenid_id=payeeopenids[0].id
        info={
            'application_id':app_id,
            'payee_company_id':int(r['payee_company_id']),
            'payee_wxopenid_id':payee_wxopenid_id,
            'realname':r['realname'],
            'cellphone':r['cellphone'],
        }        
    elif content_type=='bankinfoapplication':
        #app_name='银行卡审核'
        info={
            'application_id':app_id,
            'payee_company_id':int(r['payee_company_id']),
            'bank_name':r['bank_name'], 
            'bank_account':r['bank_account'],
            'payee':r['payee'],            
        }  
    #try
    info['company_id']=company_id
    info['user_id']=user_id
    info['wxopenid_id']=wxopenid_id
    info['nickname']=wxnickname
    info['headimgurl']=headimgurl
    data=application_submit(user_id,company_id,content_type,info)
    #data['app_name']=app_name
    return JsonResponse(data)


#2021-01-25
def application_approve(request):
    data={'e':'','status':''}
    c=request.user.company
    content_type=request.POST.get('content_type','')
    comment=request.POST.get('comment','')
    app_id=request.POST.get('app_id',0)
    group_id=request.POST.get('user_group_id',0)
    act=request.POST.get('action','approve')
    #django_content_type:199;"company";"wxwalletapplication"
    m = apps.get_model('company', content_type)
    app=m.objects.get(id=int(app_id))
    status_id=None
    if content_type in ['wxwalletapplication','bankinfoapplication']:        
        #7;"内勤管理组"  12;"销售助理" 18;"财务" 20;"出纳"
        #2;1;"内勤申核"   #3;2;"财务审核"  #4;10;"变更申请" #5;10000;"已确认"     
        if int(group_id)==12:            
            status_id=2
            data['status']="内勤申核"
        elif int(group_id)==18:
            status_id=3            
            data['status']='财务审核'       
        elif act=='reject':
            status_id=6            
            data['status']='已驳回'
        app.status_id=status_id
        if status_id==3:    #财务审核后,申请即被确认
            app.status_id=5
            data['status']='已确认'
            
            
            m = apps.get_model('company', content_type.replace('application',''))    
            #obj=m()
            
            #2021-05-19
            objs=m.objects.filter(application_id=app.id)
            if objs:
                obj=objs[0]
            else:
                obj=m()



            #为兼容2月5号前提交的申请
            if app.payee_company_id:
                payee_company_id=app.payee_company_id
            else:
                payee_company_id=app.company_id   
            
            
            if content_type=='bankinfoapplication': 
                for b in BankInfo.objects.filter(company_id=app.company_id):
                    b.is_default=False
                    b.save()
                obj.is_default=True
            
            obj.application_id=app.id
            obj.ancestor_id=app.ancestor_id
            obj.company_id=app.company_id
            if content_type=='wxwalletapplication':
                obj.wxopenid_id=app.payee_wxopenid_id
                obj.realname=app.realname 
                obj.cellphone=app.cellphone                
            else:
                obj.bank_name=app.bank_name
                obj.bank_account=app.bank_account
                obj.payee=app.payee               
            obj.save()   

        app.save()   
    log=ApplicationReviewLog(
        company_id=c.id,
        content_type=content_type,
        obj_id=int(app_id),
        status_id=status_id,
        user_id=request.user.id,
    )
    if comment:
        log.comment=comment
    log.save()        
    return JsonResponse(data)


# Jonathan
# 2020-12-15
@login_required
def white_domain(request):
    data = {'data': [], 'clmns': [], 'e': [],
            'subtt': [], 'murl': '', 'dt': False, 'add': True}
    data['clmns'] = ['域名', '域名类型', '是否有效', '添加时间','操作']
    data['show_add'] = True
    data['murl'] = '/white_domain/edit/'

    for domain in White_Domain.objects.filter(company_id=request.user.company.id):
        flag = '否'
        if domain.is_active:
            flag = '是'
        data['data'].append([
            domain.domain,
            domain.domain_type.name,
            flag,
            domain.tm.strftime("%Y-%m-%d %H:%M:%S"),
            "<a class='btn btn-default btn-xs' href='/white_domain/edit/" +str(domain.id) + "/'>详情</a>" +
            "<button class='btn btn-danger btn-xs' onclick='deleteItem(" + str(domain.id) + ")'>删除</button>"
        ])
    return render(request, 'obj/list_obj.html', data)


@login_required
def white_domain_edit(request, id=None):
    if id:
        obj = White_Domain.objects.get(id=int(id))
    else:
        obj = White_Domain(company_id=request.user.company.id)
    if request.method == 'GET':
        form = WhiteDomainForm(instance=obj)
    else:
        form = WhiteDomainForm(request.POST, instance=obj)
    if form.is_valid():
        form.save()
    return render(request, 'company/domain.html', {'form': form})

@login_required
def white_domain_delete(request, id=None):
    if id:
        obj = White_Domain.objects.get(id=int(id), company_id=request.user.company.id)
        obj.delete()
    return redirect('/white_domain/')


@login_required
def third_party_company(request):
    data = {'data': [], 'clmns': [], 'e': [],
            'subtt': [], 'murl': '', 'dt': False, 'add': True}
    data['clmns'] = ['第三方公司', '是否有效', '添加时间','操作']
    data['show_add'] = True
    data['murl'] = '/third_party_company/edit/'

    for t in Third_Party_Company.objects.filter(company_id=request.user.company.id):
        flag = '否'
        if t.is_active:
            flag = '是'
        data['data'].append([
            t.name,
            flag,
            t.tm.strftime("%Y-%m-%d %H:%M:%S"),
            "<a class='btn btn-default btn-xs' href='/third_party_company/edit/" + str(t.id) + "/'>详情</a>"
        ])
    return render(request, 'obj/list_obj.html', data)


@login_required
def third_party_edit(request, id=None):
    if id:
        obj = Third_Party_Company.objects.get(id=int(id))
    else:
        obj = Third_Party_Company(company_id=request.user.company.id)
    if request.method == 'GET':
        form = Third_Party_Company_Form(instance=obj)
    else:
        form = Third_Party_Company_Form(request.POST, instance=obj)
    if form.is_valid():
        form.save()
    return render(request, 'company/third_party.html', {'form': form})


# 绑定规则
@login_required
def label_bind_rule(request):
    data = {'data': [], 'clmns': [], 'e': [],
            'subtt': [], 'murl': '', 'dt': False, 'add': True}
    data['clmns'] = ['绑定规则（箱-盒-瓶）', '是否可用', '时间','操作']
    data['show_add'] = True
    data['murl'] = '/label_bind_rule/edit/'

    for t in Label_Bind_Rule.objects.filter(company_id=request.user.company.id):
        flag = '否'
        if t.is_active:
            flag = '是'
        str_ = str(t.level_1)
        str_ += '-' + str(t.level_2)
        str_ += '-' + str(t.level_3)
        data['data'].append([
            str_,
            flag,
            t.tm.strftime("%Y-%m-%d %H:%M:%S"),
            "<a class='btn btn-default btn-xs' href='/label_bind_rule/edit/" + str(t.id) + "/'>详情</a>"+
            "<button class='btn btn-danger btn-xs' onclick='deleteItem(" + str(t.id) + ")'>删除</button>"
        ])
    return render(request, 'obj/list_obj.html', data)

# 标签绑定删除
@login_required
def label_bind_rule_delete(request, id=None):
    if id:
        obj = Label_Bind_Rule.objects.get(id=int(id), company_id=request.user.company.id)
        obj.delete()
    return redirect('/label_bind_rule/')


@login_required
def label_bind_rule_edit(request, id=None):
    if id:
        obj = Label_Bind_Rule.objects.get(id=int(id))
    else:
        obj = Label_Bind_Rule(company_id=request.user.company.id)
    if request.method == 'GET':
        form = Label_Bind_Rule_Form(instance=obj)
    else:
        form = Label_Bind_Rule_Form(request.POST, instance=obj)
    if form.is_valid():
        form.save()
    return render(request, 'company/label_bind_rule.html', {'form': form})


@csrf_exempt
def label_bind_rule_data(request, compid=None):
    data = {
        'e': '',
        'list': []
    }
    if compid:
        w = Label_Bind_Rule.objects.filter(company_id=int(compid), is_active=True)
        if w:
            for c in w:
                str_ = str(c.level_1)
                str_ += '-' + str(c.level_2)
                str_ += '-' + str(c.level_3)

                data['list'].append(str_)
    else:
        data['e'] = ' 数据丢失'
    return JsonResponse(data)


# 域名白名单
@csrf_exempt
def white_domain_data(request, compid=None):
    data = {
        'e': '',
        'list': []
    }
    if compid:
        w = White_Domain.objects.filter(company_id=int(compid), domain_type=1, is_active=True)
        if w:
            for c in w:
                t = {
                    'id': c.id,
                    'domain': c.domain
                }
                data['list'].append(t)
    else:
        data['e'] = ' 数据丢失'
    return JsonResponse(data)


# 第三方公司
@csrf_exempt
def third_party_company_data(request, compid=None):
    data = {
        'e': '',
        'list': []
    }
    if compid:
        for t in Third_Party_Company.objects.filter(company_id=int(compid), is_active=True):
            ts = {
                'id': t.id,
                'name': t.name
            }
            data['list'].append(ts)
    else:
        data['e'] = '数据丢失'
    return JsonResponse(data)

 
# 序号绑定
@csrf_exempt 
def third_party_label_bind(request):
    data = {
        'e': '',
        'batch_id': '',
        'cnt': 0
    }
    path = "F:/datacenter/"+os.path.join("log","binds",time.strftime('%Y-%m-%d')+".txt") 
    f = open(path,'a')
    if request.method == 'POST':
        d = json.loads(request.body)
        
        f.write(time.strftime('%H:%M:%S')+":"+json.dumps(d))
        f.close() 
        
        if d['compid'] and d['uid']:
            compid = d['compid']
            uid = d['uid']
            third_party_compid = d['third_party_compid']
            remark = ''
            if 'remark' in d:
                remark = d['remark']
            if d['labels']:
                labels = d['labels']

                labels_len = range(len(labels))
                batch_id = str(time.strftime('%Y%m%d%H%M%S'))
                i = 0
                for c in labels_len:
                    #标签绑定规则
                    label_bind_rule = labels[c]['label_bind_rule']
                    #箱码 为数组 []
                    x_labels = labels[c]['label_code']
                    for x in range(len(x_labels)):
                        first_parent_id = 0
                        parent_id = 0
                        x_lbs = x_labels[x].split(',') 
                        tp = Third_Party_Label_Bind(
                            company_id=compid,
                            user_id=uid,
                            label_type=labels[c]['label_type'],
                            label_code=x_lbs[0],
                            label_code_out=x_lbs[1],
                            label_bar_code=x_lbs[2],
                            label_code_type=labels[c]['label_code_type'],
                            label_bind_rule=label_bind_rule,
                            third_party_company_id=third_party_compid,
                            batch_id=batch_id,
                            first_parent_id=first_parent_id,
                            parent_id=parent_id,
                            remark=remark
                        )  
                        tp.save()  
                        i = i+1 
 
                data['batch_id'] = batch_id
                data['cnt'] = i
            else:
                data['e'] = '无标签数据'
        else:
            data['e'] = '数据丢失'
    else:
        data['e'] = '请求失败'

    return JsonResponse(data)

# def third_party_label_bind(request):
#     data = {
#         'e': '',
#         'batch_id': '',
#         'cnt': 0
#     }
#     path = "F:/datacenter/"+os.path.join("log","binds",time.strftime('%Y-%m-%d')+".txt") 
#     f = open(path,'a')
#     f.write(time.strftime('%H:%M:%S')+":hh")
#     if request.method == 'POST':
#         d = json.loads(request.body)
        
#         f.write(time.strftime('%H:%M:%S')+":"+json.dumps(d))
#         f.close() 
        
#         if d['compid'] and d['uid']:
#             compid = d['compid']
#             uid = d['uid']
#             third_party_compid = d['third_party_compid']
#             if d['labels']:
#                 labels = d['labels']

#                 labels_len = range(len(labels))
#                 batch_id = str(time.strftime('%Y%m%d%H%M%S'))
#                 i = 0
#                 for c in labels_len:
#                     #标签绑定规则
#                     label_bind_rule = labels[c]['label_bind_rule']
#                     #箱码 为数组 []
#                     x_labels = labels[c]['label_code']
#                     x_labels_out = labels[c]['label_code_out']
#                     #产品码
#                     x_labels_bar = []
#                     if labels[c]['label_bar_code']:
#                         x_labels_bar = labels[c]['label_bar_code']
#                     if isinstance(x_labels, str):
#                         x_labels = [x_labels]
#                         x_labels_out = [x_labels_out]
#                         x_labels_bar = [x_labels_bar]


#                     first_parent_id = 0
#                     parent_id = 0
#                     #循环箱码
#                     ln = min(len(x_labels),len(x_labels_out))

#                     #循环箱码
#                     for x in range(ln): 
#                         tp = Third_Party_Label_Bind(
#                             company_id=compid,
#                             user_id=uid,
#                             label_type=labels[c]['label_type'],
#                             label_code=x_labels[x],
#                             label_code_out=x_labels_out[x],
#                             label_code_type=labels[c]['label_code_type'],
#                             label_bind_rule=label_bind_rule,
#                             third_party_company_id=third_party_compid,
#                             batch_id=batch_id,
#                             first_parent_id=first_parent_id,
#                             parent_id=parent_id
#                         )
#                         if len(x_labels_bar)>x:
#                             tp.label_bar_code=x_labels_bar[x]

#                         tp.save() 
#                         i = i+1

#                     # 如果有二级绑定
#                     if 'children' in labels[c]:
#                         children = labels[c]['children']
#                         for c2 in range(len(children)):
#                             # 盒标|二级绑定
#                             h_labels = children[c2]['label_code']
#                             h_labels_out = children[c2]['label_code_out']
#                             if isinstance(h_labels, str):
#                                 h_labels = [h_labels]
#                                 h_labels_out = [h_labels_out]
#                             #循环盒标
#                             for h in range(len(h_labels)):
#                                 tp2 = Third_Party_Label_Bind(
#                                     company_id=compid,
#                                     user_id=uid,
#                                     label_type=children[c2]['label_type'],
#                                     label_code=h_labels[h],
#                                     label_code_out=h_labels_out[h],
#                                     label_code_type=children[c2]['label_code_type'],
#                                     label_bind_rule=label_bind_rule,
#                                     third_party_company_id=third_party_compid,
#                                     batch_id=batch_id,
#                                     parent_id=parent_id,
#                                     first_parent_id=first_parent_id
#                                 )
#                                 tp2.save()
#                                 parent_id = tp2.id

#                                 i = i + 1
#                             # 三级绑定
#                             if 'children' in children[c2]:
#                                 children_3 = children[c2]['children']
#                                 for c3 in range(len(children_3)):
#                                     tp3 = Third_Party_Label_Bind(
#                                         company_id=compid,
#                                         user_id=uid,
#                                         label_type=children_3[c3]['label_type'],
#                                         label_code=children_3[c3]['label_code'],
#                                         label_code_out=children_3[c3]['label_code_out'],
#                                         label_code_type=children_3[c3]['label_code_type'],
#                                         label_bind_rule=label_bind_rule,
#                                         third_party_company_id=third_party_compid,
#                                         batch_id=batch_id,
#                                         parent_id=parent_id,
#                                         first_parent_id=first_parent_id
#                                     )
#                                     tp3.save()
#                                     i = i + 1
#                             parent_id = first_parent_id

#                 data['batch_id'] = batch_id
#                 data['cnt'] = i
#             else:
#                 data['e'] = '无标签数据'
#         else:
#             data['e'] = '数据丢失'
#     else:
#         data['e'] = '请求失败'

#     return JsonResponse(data)



# 标签序号查询
@login_required
def third_party_label_search(request):
    data = {
        'e': '',
        'labels': [],
        'search_label_type': '',
        'search_label': '',
        'search_label_third_company': '', 
        'tm': '',
        'batch_id': '',
        'u_name': '',
        'bind_rule': ''
    }
    if request.method == 'POST':
        compid = request.user.company.id
        label_code = request.POST.get('label_code', '')
        label = Third_Party_Label_Bind.objects.filter(label_code=label_code,
                company_id=int(compid)).order_by('-tm')[:1]
        if label:
            label = label[0]
            data['search_label'] = label_code
            data['search_label_third_company'] = label.third_party_company.name 
            data['tm'] = label.tm
            data['batch_id'] = label.batch_id
            data['u_name'] = label.user.username
            data['bind_rule'] = label.label_bind_rule
            if label.label_type == 'X':
                id = label.id
                if label.first_parent_id != 0:
                    labels = Third_Party_Label_Bind.objects.filter(first_parent_id=label.first_parent_id)
                    data['labels'] = json.loads(serializers.serialize("json", labels))
                    x_label = Third_Party_Label_Bind.objects.filter(id=label.first_parent_id)[0]
                    # 将查询的插入前头
                    data['labels'].insert(0, json.loads(serializers.serialize("json", [x_label])[1:-1]))
                else:
                    labels = Third_Party_Label_Bind.objects.filter(first_parent_id=id)
                    data['labels'] = json.loads(serializers.serialize("json", labels))
                    # 将查询的插入前头
                    data['labels'].insert(0, json.loads(serializers.serialize("json", [label])[1:-1]))
                data['search_label_type'] = '箱'
            else:
                if label.label_type == 'H':
                    data['search_label_type'] = '盒'
                else:
                    data['search_label_type'] = '瓶'
                first_parent_id = label.first_parent_id
                labels = Third_Party_Label_Bind.objects.filter(
                    Q(first_parent_id=first_parent_id) | Q(id=first_parent_id))
                data['labels'] = json.loads(serializers.serialize("json", labels))
    else:
        data['e'] = '数据丢失'
    return render(request, 'company/label_bind_search.html', data)

@login_required
def third_party_label_list(request):
    data = {'data': [], 'clmns': [], 'e': [],
            'subtt': [], 'murl': '', 'dt': False, 'add': True, 'order':[]}
    data['clmns'] = ['标签序号', '第三方标签序号','产品码', '第三方公司', '标签类型', '码类型', '绑定规则','备注', '时间']
    # data['show_add'] = True
    # data['murl'] = '/mall/boards/edit/'
    data['btn_name'] = '搜索详细绑定记录'
    data['btn_url'] = '/third_party_label/search/'
    data['dt']=True
    
    dt = datetime.datetime.now().strftime('%Y-%m-%d')
    dt_fr = request.GET.get('date_from', dt)
    dt_to = request.GET.get('date_to', "")
    if not dt_to:
        dt_to = (datetime.datetime.now()+datetime.timedelta(days=1)).strftime("%Y-%m-%d")
    data["date_from"]=dt_fr
    data["date_to"]=dt_to       

    

    cur = connection.cursor()
    company_id=request.user.company.id
    # 查询最后一次更新记录
    cur.execute("SELECT label_code,label_code_out,label_bar_code,"+
        "(SELECT name from company_third_party_company WHERE id = third_party_company_id),label_type,label_code_type,"+
        "label_bind_rule,remark,tm FROM company_third_party_label_bind WHERE company_id = "+str(company_id)+" and tm>='"+dt_fr+"' and tm <='"+dt_to+"'")
    for r in cur.fetchall():
        label_code_type = '二维码'
        label_type = '箱'
        if r[5] == 1:
            label_code_type = '条形码'


        data['data'].append([
            r[0],
            r[1],
            r[2],
            r[3],
            label_type,
            label_code_type,
            r[6],
            r[7],
            str(r[8])
        ]) 
    #print(data)
    return render(request, 'obj/list_obj.html', data)



# 权益
@login_required
def benefit(request):
    data = {'data': [], 'clmns': [], 'e': [],
            'subtt': ['品牌权益', '权益设置'], 'murl': '', 'dt': False, 'add': True}
    data['clmns'] = ['权益名称', '面向对象', '是否激活', '备注', '添加时间', '操作']
    data['show_add'] = True
    data['murl'] = '/company/benefit/edit/'

    for t in Benefit.objects.filter(company_id=request.user.company.id):
        flag = '否'
        face_to = ''
        remark = '无'
        if t.is_face_to_agent:
            face_to = '经销商'
        if t.is_face_to_terminal:
            face_to += ',终端店'
        if t.is_face_to_business:
            face_to += ',业务员'
        if t.is_active:
            flag = '是'
        if t.remark:
            remark = t.remark
        data['data'].append([
            "<a href='/company/benefit/edit/" +
            str(t.id) + "/'>" + t.name + "</a>",
            face_to,
            flag,
            remark,
            t.tm.strftime("%Y-%m-%d %H:%M:%S"),
            "<a class='btn btn-default btn-xs' href='/company/benefit/rule/" + str(t.id) + "/'>规则设定</a>" +
            "<button class='btn btn-danger btn-xs' onclick='deleteItem(" + str(t.id) + ")'>删除</button>"
        ])
    return render(request, 'obj/list_obj.html', data)


# 权益编辑
@login_required
def benefit_edit(request, id=None):
    if id:
        obj = Benefit.objects.get(id=int(id))
    else:
        obj = Benefit(company_id=request.user.company.id)
    if request.method == 'GET':
        form = Benefit_Form(instance=obj)
    else:
        form = Benefit_Form(request.POST, instance=obj)
    if form.is_valid():
        form.save()
    return render(request, 'company/benefit.html', {'form': form})


# 权益删除
@login_required
def benefit_delete(request, id=None):
    if id:
        obj = Benefit.objects.get(id=int(id), company_id=request.user.company.id)
        obj.delete()
    return redirect('/company/benefit/')



# 权益规则
@login_required
def benefit_rule(request, id=None):
    data = {'data': [],
            'clmns': ['规则名称', '类型','规则', '备注', '添加时间', '操作'],
            'e': [], 'subtt': ['品牌权益', '权益规则'],
            'level_up_method_id': '',
            'level_up_method': '',
            'list': [],
            'cats':[]
            }
    if id:
        benefit = Benefit.objects.get(id=int(id))

        if request.method == 'POST':
            level_id = request.POST.get('level_id', '')
            if level_id:
                obj = Benefit_Rule.objects.get(id=int(level_id))
            else:
                obj = Benefit_Rule(company_id=request.user.company.id, benefit_id=int(id))
            if obj:
                form = Benefit_Rule_Form(request.POST, instance=obj)
                if form.is_valid():
                    form.save()

        brand_rule = Benefit_Rule.objects.filter(benefit_id=id)
        for t in brand_rule:
            remark = ''
            if t.remark:
                remark =t.remark
            rule = t.rule_category.name
            if t.rule_category.id == 1:
                rule = rule +' 打'+str(t.discount)+'折'

            data['data'].append([
                t.name,
                t.rule_category.name,
                rule,
                remark,
                t.tm.strftime("%Y-%m-%d %H:%M:%S"),
                "<a class='btn btn-danger btn-xs' onclick='deleteData(" + str(t.id) + ")'>删除</a>"
            ])

        rule_category = Benefit_Rule_Category.objects.all()
        for r in rule_category:
            data['cats'].append({
                'id':r.id,
                'name':r.name
            })


        #benefits = Benefit.objects.filter(company_id=request.user.company.id, is_active=True)
        #data['benefits'] = json.loads(serializers.serialize("json", benefits))

        return render(request, 'company/benefit_rule.html', data)
    else:
        return JsonResponse(data)


# 权益删除
@login_required
def benefit_rule_delete(request, id=None, benefit_rule_id=None):
    if id and benefit_rule_id:
        obj = Benefit_Rule.objects.get(id=int(benefit_rule_id), company_id=request.user.company.id)
        obj.delete()
        return redirect('/company/benefit/rule/' + id)
    else:
        return redirect('/company/benefit/')


# 品牌管理
@login_required
def brands(request):
    data = {'data': [], 'clmns': [], 'e': [],
            'subtt': ['品牌管理', '品牌设置'], 'murl': '', 'dt': False, 'add': True}
    data['clmns'] = ['品牌名称', '厂家', '升级条件', '是否激活', '备注', '添加时间', '操作']
    data['show_add'] = True
    data['murl'] = '/company/brands/edit/'

    for t in Brands.objects.filter(company_id=request.user.company.id):
        flag = '否'
        remark = '无'
        if t.level_up_method == 1:
            level_up_method = '按销售数量'
        else:
            level_up_method = '按销售金额'
        if t.is_active:
            flag = '是'
        if t.remark:
            remark = t.remark
        data['data'].append([
            "<a href='/company/brands/edit/" +
            str(t.id) + "/'>" + t.name + "</a>",
            t.third_party_company.name,
            level_up_method,
            flag,
            remark,
            t.tm.strftime("%Y-%m-%d %H:%M:%S"),
            "<a class='btn btn-default btn-xs' href='/company/brands/level/" + str(t.id) + "/'>等级设定</a>" +
            "<a class='btn btn-default btn-xs' href='/company/brands/edit/" + str(t.id) + "/products/'>产品绑定</a>" +
            "<button class='btn btn-danger btn-xs' onclick='deleteItem(" + str(t.id) + ")'>删除</button>"
        ])
    return render(request, 'obj/list_obj.html', data)


# 品牌编辑
@login_required
def brands_edit(request, id=None):
    if id:
        obj = Brands.objects.get(id=int(id))
    else:
        obj = Brands(company_id=request.user.company.id)
    if request.method == 'GET':
        form = Brands_Form(instance=obj, user=request.user)
    else:
        form = Brands_Form(request.POST, instance=obj, user=request.user)
    if form.is_valid():
        form.save()
    return render(request, 'company/brand.html', {'form': form})


# 品牌删除
@login_required
def brands_delete(request, id=None):
    if id:
        obj = Brands.objects.get(id=int(id), company_id=request.user.company.id)
        obj.delete()
    return redirect('/company/brands/')


# 品牌绑定产品
@login_required
def brands_edit_product(request, id=None):
    data = {'data': [],
            'e': [], 'subtt': ['品牌管理', '品牌产品绑定'],
            'products_bind': [],
            'products': [],
            'brand_name': ''
            }
    if id:
        obj = Brands.objects.get(id=int(id))
        data['brand_name'] = obj.name

        if request.method == 'POST':
            productChooses = request.POST.get('productChooses', '')
            if productChooses:
                ps = json.loads(productChooses)                
                p_len = range(len(ps))
                cur = connection.cursor()
                # 转为默认
                cur.execute("update product_product set brand_id = null where brand_id = " + id)
                for c in p_len:
                    cur.execute("update product_product set brand_id = " + id + " where id = " + ps[c])

        products_bind = Product.objects.filter(brand_id=int(id), company_id=request.user.company.id, is_delete=False)
        products = Product.objects.filter(brand_id=None, company_id=request.user.company.id, is_delete=False)
        data['products_bind'] = json.loads(serializers.serialize("json", products_bind))
        data['products'] = json.loads(serializers.serialize("json", products))

        return render(request, 'company/brand_products.html', data)
    else:
        return JsonResponse(data)


# 品牌等级
@login_required
def brands_level(request, id=None, type=None):
    data = {'data': [],
            'clmns': ['等级级别', '等级名称', '等级图片', '升级条件', '权益', '备注', '操作'],
            'e': [], 'subtt': ['品牌管理', '品牌等级'],
            'level_up_method_id': '',
            'level_up_method': '',
            'list': [],
            'benefits': []
            }
    if id:
        brand = Brands.objects.get(id=int(id))
        level_up_method_id = brand.level_up_method
        data['level_up_method_id'] = level_up_method_id
        if level_up_method_id == 1:
            data['level_up_method'] = '按销售数量'
        elif level_up_method_id == 2:
            data['level_up_method'] = '按销售金额'
        if request.method == 'POST':
            level_id = request.POST.get('level_id', '')
            if level_id:
                obj = Brands_Level.objects.get(id=int(level_id))
            else:
                obj = Brands_Level(company_id=request.user.company.id, brand_id=int(id))
            if type == 'delete' and level_id:
                obj.delete()
                brands_agent_update(id,level_up_method_id)
            else:
                form = Brands_Level_Form(request.POST, request.FILES, instance=obj)
                if form.is_valid():
                    form.save()
                    brands_agent_update(id,level_up_method_id)
        brand_obj = Brands_Level.objects.filter(brand_id=id)
        for t in brand_obj:
            if level_up_method_id == 1:
                level_up_method = '销售数量>=' + str(t.need_num)
            elif level_up_method_id == 2:
                level_up_method = '销售金额>=' + str(t.need_money) + ' 元'
            photo_url = ''
            remark = '无'
            benefit_name = ''
            if t.photo:
                photo_url = t.photo.url
            if t.remark:
                remark = t.remark
            if t.benefit:
                benefit_name = t.benefit.name

            data['data'].append([
                t.level,
                t.name,
                "<img src='" + photo_url + "' style='width:30px' />",
                level_up_method,
                benefit_name,
                remark,
                "<a class='btn btn-default btn-xs' onclick='editLevel(" + str(t.id) + ")'>修改</a>" +
                "<a class='btn btn-default btn-xs' onclick='bindBenefit(" + str(t.id) + ")'>权益绑定</a>" +
                "<a class='btn btn-danger btn-xs' onclick='deleteLevel(" + str(t.id) + ")'>删除</a>"
            ])
        data['list'] = json.loads(json.dumps(serializers.serialize("json", brand_obj)))

        benefits = Benefit.objects.filter(company_id=request.user.company.id, is_active=True)
        data['benefits'] = json.loads(serializers.serialize("json", benefits))

        return render(request, 'company/brand_level.html', data)
    else:
        return JsonResponse(data)





@csrf_exempt
def brands_agent_level(request):
    data = {
        'e':'',
        'levels':[],
        'tasks':[]
    }
    company_id = request.GET.get('company_id','')
    if company_id :
        c = Company.objects.get(id=int(company_id))
        brands_agent_level_update(c.ancestor_id,company_id)
        obj = Brands_Agent_Level.objects.filter(company_id=int(company_id))
        if obj:
            for o in obj:
                b = o.brand_level
                name = b.brand.name + b.name
                photo = b.photo.url
                level = b.level

                benefit = None
                #权益内容
                b = o.brand_level.benefit
                if b:
                    if b.is_active:
                        benefit = {
                            'name':b.name,
                            'rules':[]
                        }
                        #查询权益详细规则
                        bs = Benefit_Rule.objects.filter(benefit_id=b.id)
                        if bs:
                            for b2 in bs:
                                benefit['rules'].append({
                                    'id': encrypt_p(str(b2.id)) ,
                                    'name':b2.name,
                                    'type':b2.rule_category.name,
                                    'discount':b2.discount
                                })


                data['levels'].append({
                    'name':name,
                    'photo':photo,
                    'level':level,
                    'benefit':benefit
                })
        tasks = Sale_Task_Allocate.objects.filter(agent_id=int(company_id))
        if tasks:
            cur = connection.cursor()
            for t in tasks:
                allocate_type = t.allocate_type
                if t.sale_task.is_active:
                    products = []
                    if allocate_type == 1: 
                        ssql = ("select "
                                "(SELECT name from product_product where id = product_id),num,sale_num "
                                "from company_sale_task_allocate_products "
                                "where agent_id = "+company_id +" and sale_task_allocate_id = "+str(t.id))
                        cur.execute(ssql)
                        ps = cur.fetchall()
                        if ps:
                            for p in ps:
                                products.append({
                                    'name':p[0],
                                    'num':p[1],
                                    'sale_num':p[2]
                                })
                    data['tasks'].append({
                        'name': t.sale_task.name,
                        'start_tm': t.sale_task.start_tm,
                        'end_tm': t.sale_task.end_tm,
                        'sale_money': t.sale_money,
                        'sold_money': t.sold_money,
                        'finish_percent': t.finish_percent,
                        'allocate_type':allocate_type,
                        'products':products
                    })


    else:
        data['e'] = '数据丢失'
    return JsonResponse(data)



# 下级终端店等级
@csrf_exempt
def brands_agent_children_level(request):

    data = {
        'e':'',
        'levels':[] 
    }
    company_id = request.GET.get('company_id','') 
    if company_id :
        #c = Company.objects.get(id=int(company_id))
        cur = connection.cursor()
        cur.execute("select (select name from company_company where id = company_id) as compname,"+
                    "(select name from company_brands where id = brand_id),"+
                    "(select name from company_brands_level where id = brand_level_id),"+
                    "(select photo from company_brands_level where id = brand_level_id)"+ 
                    "from company_brands_agent_level where company_id in (SELECT id FROM company_company where parent_id = "+company_id+") order by compname")
        rs = cur.fetchall()
        if rs:
            for r in rs:
                data['levels'].append({
                    'agent_name':r[0],
                    'brand_name':r[1],
                    'level_name':r[2],
                    'level_photo':r[3]
                })
 

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

    return JsonResponse(data)



@csrf_exempt
def test_brands(request,ancestor_id,company_id):
    data = {
        'e': ''
    }
    brands_agent_level_update(ancestor_id,company_id)
    #sale_task_agent_update(ancestor_id,company_id)
    return  JsonResponse(data)


# 考核任务更新 
def sale_task_agent_update(ancestor_id, company_id):
    cur = connection.cursor()
    # 经销商任务
    agent_tasks = Sale_Task_Allocate.objects.filter(agent_id=int(company_id))
    if agent_tasks:
        for t in agent_tasks:
            # 任务开始时间
            start_tm = t.sale_task.start_tm.strftime("%Y-%m-%d")
            # 任务结束时间
            end_tm = (t.sale_task.end_tm+datetime.timedelta(days=1)
                      ).strftime("%Y-%m-%d")
            # 如果按照销售数量升级 
            if t.allocate_type == 1: 
                # 查询销售出库 剔除退库
                cur.execute("SELECT product_id,count(*) FROM inventory_stkdetails WHERE company_id = "+str(company_id)+
                            "and vtype_id = 2 and label_pkg = 3 "+
                            "and label_code not in ( "+
                            "    SELECT label_code from inventory_stkdetails where vtype_id = 4 and label_code in ( "+
                            "        SELECT label_code FROM inventory_stkdetails WHERE company_id = "+str(company_id)+
                            "            and vtype_id = 2  "+
                            "    ) "+
                            ")"+
                            "and product_id in ("+
                            "    SELECT product_id from company_sale_task_allocate_products where agent_id = "+str(company_id)+
                            ") "+
                            "and tm BETWEEN '"+start_tm+"' and '"""+end_tm+"'"+
                            "GROUP BY product_id")
                rs = cur.fetchall()
                if rs: 
                    for r in rs:
                        product_id = r[0]
                        num = r[1]
                        cur.execute("update company_sale_task_allocate_products set sale_num = "+str(num) +" where product_id = "+ str(product_id) +" and agent_id = "+str(company_id))
                    cur.execute("UPDATE company_sale_task_allocate a set finish_percent = ("+
                                "SELECT ((sum(sale_num)*1.0)/(sum(num)*1.0))*100 FROM company_sale_task_allocate_products where sale_task_allocate_id = a.id"+
                                ") where agent_id = "+str(company_id))
            else:   
                cur.execute("SELECT product_id,"+
                            "count(*) * COALESCE((select price from product_product WHERE id = product_id),0) "+
                            "FROM inventory_stkdetails WHERE company_id = "+str(company_id)+" "+
                            "and vtype_id = 2 and label_pkg = 3 "+
                            "and label_code not in ( "+
                            "    SELECT label_code from inventory_stkdetails where vtype_id = 4 and label_code in ( "+
                            "        SELECT label_code FROM inventory_stkdetails WHERE company_id = "+str(company_id)+
                            "            and vtype_id = 2  "+
                            "    ) "+
                            ")"+ 
                            "and tm BETWEEN '"+start_tm+"' and '"""+end_tm+"' "+
                            "GROUP BY product_id")
                rs = cur.fetchall()  
                if rs: 
                    for r in rs:
                        product_id = r[0]
                        money = r[1]
                        cur.execute("UPDATE company_sale_task_allocate set finish_percent = ("+str(money)+"/sale_money)*100,sold_money = "+str(money)+" where agent_id = "+str(company_id))
    sale_task_company_update(ancestor_id)




# 更新所有任务


def sale_task_company_update(company_id):
    cur = connection.cursor()
    # 总任务
    company_tasks = Sale_Task.objects.filter(company_id=int(company_id))
    if company_tasks:
        for t in company_tasks:
            cur.execute(
                "select sum(finish_percent) from company_sale_task_allocate where sale_task_id = "+str(t.id))
            d = cur.fetchone()
            if d: 
                t.finish_percent = d[0]
                t.last_update_tm = datetime.datetime.now()
                t.save()
            else: 
                t.finish_percent = 0
                t.last_update_tm = datetime.datetime.now()
                t.save()



# 更新当前任务
def sale_task_update(id):
    tasks = Sale_Task_Allocate.objects.filter(sale_task_id=int(id))
    if tasks:
        for t in tasks:
            sale_task_agent_update(t.company_id,t.agent_id)



# 更新当前品牌下的经销商等级
def brands_agent_update(brand_id,level_up_method):
    levels = Brands_Agent_Level.objects.filter(brand_id=int(brand_id))
    if levels:
        cur = connection.cursor()
        for t in levels:
            all_num = t.all_num
            all_money = t.all_money
            # 如果按照销售数量升级
            if level_up_method == 1:
                cur.execute(
                    "select id from company_brands_level where " + str(all_num) + " >= need_num and brand_id = " + str(
                        brand_id) + " ORDER BY need_num desc LIMIT 1")
                d = cur.fetchone()
                if d:
                    level_id = d[0]
            # 如果按照销售金额升级
            elif level_up_method == 2:
                cur.execute("select id from company_brands_level where " + str(
                    all_money) + " >= need_money and brand_id = " + str(brand_id) + " ORDER BY need_money desc LIMIT 1")
                d = cur.fetchone()
                if d:
                    level_id = d[0]
            if level_id:
                t.brand_level_id = int(level_id)
                t.save()


# 经销商等级升级记录
def brands_agent_do_record(ancestor_id,company_id,product_id,product_num):
    data = {
        'e': ''
    }

    product = Product.objects.filter(id=int(product_id))
    if product:
        product=product[0]
        a = Brands_Agent_Records(
            ancestor_id=int(ancestor_id),
            company_id=int(company_id),
            brand=product.brand,
            product_id=int(product_id),
            product_name=product.name,
            product_num=int(product_num),
            product_money=product.price
        )
        a.save()
    else:
        data['e'] = '产品不存在'

    return JsonResponse(data)

# 经销商品牌等级更新
def brands_agent_level_update(ancestor_id, company_id):

    data = {
        'e': ''
    }

    cur = connection.cursor()
    # 查询销售出库 剔除退库
    cur.execute("SELECT product_id,(SELECT brand_id from product_product where id = product_id),"+\
                "(SELECT level_up_method from company_brands where id = ((SELECT brand_id from product_product where id = product_id))),"
                "(SELECT price from product_product where id = product_id),"
                "count(*) FROM inventory_stkdetails WHERE company_id = "+str(company_id)+
                "and vtype_id = 2  and label_pkg = 3 "+
                "and label_code not in ( "+
                "    SELECT label_code from inventory_stkdetails where vtype_id = 4 and label_code in ( "+
                "        SELECT label_code FROM inventory_stkdetails WHERE company_id = "+str(company_id)+
                "            and vtype_id = 2  "+
                "    ) "+
                ")"+
                "and product_id in ("+
                "   SELECT id FROM product_product WHERE brand_id in ( "+\
	            "       SELECT brand_id FROM company_brands_level where company_id = "+str(ancestor_id)+")"+\
                ") "+
                "GROUP BY product_id")
    rs = cur.fetchall()             
    for r in rs:
        product_id = r[0]
        brand_id = r[1]
        level_up_method = r[2]
        price = r[3]
        count = r[4]
        level_id = None
        all_price = 0
        if price:
            all_price = price * count
        if level_up_method == 1: 
            cur.execute("select id from company_brands_level where "+str(count) +
                            " >= need_num and brand_id = "+str(brand_id)+" ORDER BY need_num desc LIMIT 1")
            d = cur.fetchone()
            if d:
                level_id = d[0]
        else:
            cur.execute("select id from company_brands_level where "+str(all_price) +
                            " >= need_money and brand_id = "+str(brand_id)+" ORDER BY need_money desc LIMIT 1")
            d = cur.fetchone()
            if d:
                level_id = d[0] 
                
        if level_id:
                b = Brands_Agent_Level.objects.filter(
                    brand_id=int(brand_id), company_id=int(company_id))
                if b:
                    b = b[0]
                    b.all_money = all_price
                    b.all_num = count
                    b.brand_level_id = int(level_id)
                    b.save()
                else:
                    b = Brands_Agent_Level(
                        ancestor_id=int(ancestor_id),
                        company_id=int(company_id),
                        brand_id=int(brand_id),
                        brand_level_id=int(level_id),
                        all_money=all_price,
                        all_num=count
                    )
                    b.save()
 

    return JsonResponse(data)




# 经销商等级列表
@login_required
def brands_agent_level_list(request):
    data = {'data': [], 'clmns': [], 'e': [],
            'subtt': ['品牌权益', '经销商等级'], 'murl': '', 'dt': False, 'add': True}
    data['clmns'] = ['经销商名称', '品牌', '等级', '总金额', '产品总数量']

    for t in Brands_Agent_Level.objects.filter(ancestor_id=request.user.company.id):
        data['data'].append([
            t.company.name,
            t.brand.name,
            "<img src='"+t.brand_level.photo.url+"' style='width:25px' /> "+t.brand_level.name,
            str(t.all_money),
            t.all_num
            #"<a class='btn btn-default btn-xs' href='/company/sale_task/edit/" + str(t.id) + "/'>数据更改</a>"
        ])
    return render(request, 'obj/list_obj.html', data)


# 考核系统
@login_required
def sale_task(request):
    data = {'data': [], 'clmns': [], 'e': [],
            'subtt': ['任务考核', '任务设置'], 'murl': '', 'dt': False, 'add': True}
    data['clmns'] = ['任务名称', '开始时间', '结束时间', '状态', '完成进度', '备注', '操作']
    data['show_add'] = True
    data['murl'] = '/company/sale_task/edit/'
    sale_task_company_update(request.user.company.id)

    for t in Sale_Task.objects.filter(company_id=request.user.company.id):
        flag = '关闭'
        remark = '无'
        finish_percent = str(t.finish_percent)
        if t.is_active:
            flag = '进行中'
        if t.remark:
            remark = t.remark
        width = str(t.finish_percent)
        if t.finish_percent and t.finish_percent >100:
            width = '100'
        data['data'].append([
            "<a href='/company/sale_task/edit/" +
            str(t.id) + "/'>" + t.name + "</a>",
            t.start_tm.strftime("%Y-%m-%d"),
            t.end_tm.strftime("%Y-%m-%d"),
            flag,
            '<div class="progress" style="margin:0"><div class="progress-bar progress-bar-success progress-bar-striped active" role="progressbar" aria-valuenow="' + finish_percent + '" aria-valuemin="0" aria-valuemax="100" style="width: ' + width + '%;">' + finish_percent + '%</div></div>',
            remark,
            "<a class='btn btn-default btn-xs' href='/company/sale_task/edit/" + str(t.id) + "/'>详情</a>" +
            "<a class='btn btn-default btn-xs' href='/company/sale_task/allocate/" + str(t.id) + "/'>任务分配</a>" +
            "<button class='btn btn-danger btn-xs' onclick='deleteItem(" + str(t.id) + ")'>删除</button>"
        ]) 
    return render(request, 'obj/list_obj.html', data)


# 销售任务设定
@login_required
def sale_task_edit(request, id=None):
    if id:
        obj = Sale_Task.objects.get(id=int(id))
    else:
        obj = Sale_Task(company_id=request.user.company.id)
    if request.method == 'GET':
        form = Sale_Task_Form(instance=obj)
    else:
        form = Sale_Task_Form(request.POST, instance=obj)
    if form.is_valid():
        form.save()
        if id:
            sale_task_update(id)
    return render(request, 'company/sale_task.html', {'form': form})


# 销售任务分配
@login_required
def sale_task_allocate(request, id=None, type=None):
    data = {'data': [],
            'clmns': ['经销商', '完成方式', '进度', '备注', '操作'],
            'e': [], 'subtt': ['任务考核', '任务设置', '任务分配'],
            'list': [],
            'agents': []
            }
    if id:
        cur = connection.cursor()
        # task = Sale_Task.objects.get(id=int(id))
        if request.method == 'POST':
            allocate_id = request.POST.get('allocate_id', '')
            agent = request.POST.get('agent', '')
            product_ids = request.POST.get('product_ids', '')
            allocate_type = request.POST.get('allocate_type', '0')
            obj = None
            if allocate_id:
                obj = Sale_Task_Allocate.objects.get(id=int(allocate_id))
            else:
                cur.execute(
                    "select count(*) from company_sale_task_allocate where sale_task_id = " + id + " and agent_id = " + agent)
                r = cur.fetchone()
                if int(r[0]) == 0:
                    obj = Sale_Task_Allocate(company_id=request.user.company.id, sale_task_id=int(
                        id), allocate_type=int(allocate_type))

            if obj:
                form = Sale_Task_Allocate_Form(request.POST, instance=obj)
                if form.is_valid():
                    form.save()
                    if product_ids:
                        ps = product_ids.split(',')
                        for p in range(len(ps)):
                            p1 = ps[p].split('|')
                            sp = Sale_Task_Allocate_Products(
                                sale_task_allocate_id=form.instance.id,
                                agent=form.instance.agent,
                                product_id=int(p1[0]),
                                num=int(p1[1]),
                            )
                            sp.save()
                    sale_task_agent_update(request.user.company.id, agent)

        agents = Company.objects.filter(parent_id=int(request.user.company.id))
        data['agents'] = json.loads(serializers.serialize("json", agents))

        cur.execute("SELECT id,allocate_type,sale_money,sold_money,finish_percent,remark," +
                    "(SELECT name from company_company where id = agent_id)," +
                    "(SELECT sum(num) from company_sale_task_allocate_products WHERE sale_task_allocate_id = a.id) from company_sale_task_allocate a where sale_task_id = "+id)
        allocates = cur.fetchall()
        for r in allocates:
            a_id = r[0]
            allocate_type = r[1]
            sale_money = r[2]
            #sold_money = r[3]
            finish_percent = r[4]
            remark = r[5]
            if not remark:
                remark = '无'
            agent_name = r[6]
            sala_num = r[7]
            width = str(finish_percent)
            if finish_percent and finish_percent > 100:
                width = '100'
            finish_percent = str(finish_percent)

            sale_limit = ''
            if allocate_type == 1:
                allocate_type = '销售数量 >= ' + str(sala_num)

            else:
                allocate_type = '销售金额 >= ' + str(sale_money) + '元'

            data['data'].append([
                agent_name,
                allocate_type,
                '<div class="progress" style="margin:0"><div class="progress-bar progress-bar-success progress-bar-striped active" role="progressbar" aria-valuenow="' +
                finish_percent + '" aria-valuemin="0" aria-valuemax="100" style="width: ' +
                width + '%;">' + finish_percent + '%</div></div>',
                remark,
                # "<a class='btn btn-default btn-xs' onclick='editData(" + str(a.id) + ")'>修改</a>" +
                "<a class='btn btn-danger btn-xs' onclick='deleteData(" + str(
                    a_id) + ")'>删除</a>"
            ])
        data['list'] = []
        return render(request, 'company/sale_task_allocate.html', data)
    else:
        return JsonResponse(data)




# 销售任务删除
@login_required
def sale_task_delete(request, id=None):
    if id:
        obj = Sale_Task.objects.get(id=int(id), company_id=request.user.company.id)
        obj.delete()
    return redirect('/company/sale_task/')
@login_required
def sale_task_allocate_delete(request, id=None, allocate_id=None):
    if id and allocate_id:
        obj = Sale_Task_Allocate.objects.get(id=int(allocate_id), company_id=request.user.company.id)
        task_id = obj.sale_task_id
        obj.delete()
        return redirect('/company/sale_task/allocate/' + str(task_id))
    else:
        return redirect('/company/sale_task/')


# 任务记录
@login_required
def sale_task_record(request):

    data = {'data': [], 'clmns': [], 'e': [],
            'subtt': ['任务考核', '任务记录'], 'murl': '', 'dt': False, 'add': True}
    data['clmns'] = ['任务名称', '经销商', '目标金额', '已完成金额', '完成进度','最后更新时间']
    data['show_add'] = False
    tasks = Sale_Task_Allocate.objects.filter(company_id=request.user.company.id).order_by('-last_update_tm')
    for t in tasks:

        finish_percent = str(t.finish_percent)
        width = str(t.finish_percent)
        if t.finish_percent >100:
            width = '100'


        data['data'].append([
            t.sale_task.name,
            t.agent.name,
            str(t.sale_money),
            str(t.sold_money),
            '<div class="progress" style="margin:0"><div class="progress-bar progress-bar-success progress-bar-striped active" role="progressbar" aria-valuenow="' + finish_percent + '" aria-valuemin="0" aria-valuemax="100" style="width: ' + width + '%;">' + finish_percent + '%</div></div>',
            t.last_update_tm.strftime("%Y-%m-%d %H:%M:%S")
        ])
    return render(request, 'obj/list_obj.html', data)



# 轮播图
@login_required
def page_banners(request, id=None):
    data = {'data': [],
            'clmns': ['图片', '是否激活', '排序', '上传时间', '操作'],
            'e': [], 'subtt': ['页面轮播图', '轮播图管理'],
            'list': []
            }

    if request.method == 'POST':
        banner_id = request.POST.get('banner_id', '')
        obj = None
        if banner_id:
            obj = Page_Banners.objects.get(id=int(banner_id))
        else:
            obj = Page_Banners(company_id=request.user.company.id)
        if obj:
            form = Page_Banners_Form(request.POST,request.FILES, instance=obj)
            if form.is_valid():
                form.save()
    banners = Page_Banners.objects.filter(company_id=request.user.company.id).order_by('sort_id')
    for b in banners:
        flag = '否'
        img_url = ''
        if b.is_active:
            flag = '是'
        if b.image:
            img_url = b.image.url
        data['data'].append([
            "<img src='" + img_url + "' style='width:100px' />",
            flag,
            b.sort_id,
            b.tm.strftime("%Y-%m-%d %H:%M:%S"),
            "<a class='btn btn-default btn-xs' onclick='editData(" + str(b.id) + ")'>修改</a>"+
            "<a class='btn btn-danger btn-xs' onclick='deleteData(" + str(b.id) + ")'>删除</a>"
        ])
    data['list'] = json.loads(json.dumps(serializers.serialize("json", banners)))
    return render(request, 'company/page_banner.html', data)

# 轮播图删除
@login_required
def page_banners_delete(request, id=None):
    if id :
        obj = Page_Banners.objects.get(id=int(id), company_id=request.user.company.id)
        obj.delete()
    return redirect('/company/page_banners/')

# 新闻资讯
@login_required
def news(request):
    data = {'data': [], 'clmns': [], 'e': [],'subtt': ['页面管理', '新闻资讯'], 'murl': '', 'dt': False, 'add': True}
    data['clmns'] = ['文章导图', '标题', '备注', '是否激活','添加时间','操作']
    data['show_add'] = True
    data['murl'] = '/company/news/edit/'

    for t in News.objects.filter(company_id=request.user.company.id):
        flag = '否'
        remark = '无'
        img_url = ''
        if t.is_active:
            flag = '是'
        if t.remark:
            remark = t.remark
        if t.poster:
            img_url = t.poster.url

        data['data'].append([
            "<img src='" + img_url + "' style='width:50px' />",
            t.subtitle,
            remark,
            flag,
            t.tm.strftime("%Y-%m-%d %H:%M:%S"),
            "<a class='btn btn-default btn-xs' href='/company/news/edit/" + str(t.id) + "/'>详情</a>"+
            "<button class='btn btn-danger btn-xs' onclick='deleteItem(" + str(t.id) + ")'>删除</button>"
        ])
    return render(request, 'obj/list_obj.html', data)

# 新闻资讯编辑
@login_required
def news_edit(request, id=None):
    if id:
        obj = News.objects.get(id=int(id))
    else:
        obj = News(company_id=request.user.company.id)
    if request.method == 'GET':
        form = News_Form(instance=obj)
    else:
        form = News_Form(request.POST,request.FILES, instance=obj)
    if form.is_valid():
        form.save()
    return render(request, 'company/news.html', {'form': form})


# 新闻删除
@login_required
def news_delete(request, id=None):
    if id :
        obj = News.objects.get(id=int(id), company_id=request.user.company.id)
        obj.delete()
    return redirect('/company/news/')



# 设置允许上传的文件格式
ALLOW_EXTENSIONS = ['png', 'jpg', 'jpeg']
# 判断文件后缀是否在列表中
def allowed_file(filename):
    return '.' in filename and filename.rsplit('.', 1)[-1] in ALLOW_EXTENSIONS

@csrf_exempt
def news_upload(request):
    data = {
        'errno': '',
        'data': []
    }
    if request.method == 'POST' and request.user:
        # 获取post过来的文件名称，从name=file参数中获取
        img = request.FILES.get('file', '')
        comp_name = request.user.company.name
        if img:
            file_name = str(time.strftime('%Y%m%d%H%M%S', time.localtime(time.time()))) + str(time.time()).replace('.', '')[-7:] + "." + img.name.split('.')[-1]
            # 保存图片
            dirs = "media\\"+os.path.join(comp_name, 'news')
            if not os.path.exists(dirs):
                os.makedirs(dirs)

            f_url = "media\\"+os.path.join(comp_name, 'news', file_name)
            with open(f_url, 'wb+') as destination:
                for chunk in img.chunks():
                    destination.write(chunk)
            data['errno'] = '0'
            data['data'].append(f_url)
        else:
            data['errno'] = '500'
    else:
        data['errno'] = '500'
    return JsonResponse(data)

@login_required
def toggle_brandrecommender_active(request):
    id = request.GET.get('id', '')
    if not id:
        return JsonResponse({'e': 'no id'})
    else:
        br = BrandRecommender.objects.get(id=id)
        if br.is_active:
            br.is_active = False
        else:
            br.is_active = True
        br.save()
        return JsonResponse({'e': ''})

@login_required
def label_scan_list(request):
    data={'e':'','data':[],'clmns':[],'dt':''}
    cid = request.user.company.id
    dt = datetime.datetime.now().strftime('%Y-%m-%d')
    dt_fr = request.GET.get('date_from', dt)
    dt_to = request.GET.get('date_to', dt)
    if dt_fr > dt_to:
        dt_fr, dt_to = dt_to,dt_fr
    else:
        dt_fr, dt_to = dt_fr, dt_to
    data["date_from"] = dt_fr
    data["date_to"] = dt_to


    data['clmns'] = [_('标签号码'),_('消费者'),_('产品'),_('省'),_('市'),_('地址'),_('时间')]


    ls = LabelScan.objects.filter(company_id=request.user.company.id,dt__range=(dt_fr,dt_to))
    for l in ls:
        product_name = ''
        if l.product:
            product_name = l.product.name

        data['data'].append([
            l.label_code,
            '<img src="'+l.consumer.wx_headimgurl+'" style="width:20px"/> '+l.consumer.wx_nickname,
            product_name,
            l.province,
            l.city,
            l.address,
            l.tm.strftime("%Y-%m-%d %H:%M:%S")
        ])

    return render(request, 'sales/label_scan.html',data)


'''
def get_staff_saldiv(request,user_id):
    data={'e':'','saldivs':[],'subsaldivs':[],'agents':[],'groups':[]}

    
    cur=connection.cursor()      
    s="select saldiv_ids,subsaldiv_ids,agent_ids,group_ids from company_staffsaldiv where user_id="+str(user_id)
    cur.execute(s)
    r=cur.fetchone()
    if r:
    #if r[0]:
        for div in r[0]:
            data['saldivs'].append(div)
    #if r[1]:
        for sub in r[1]:
            data['subsaldivs'].append(sub)
    #if r[2]:
        for agt in r[2]:
            data['agents'].append(agt)
    #if r[3]:
        for grp in r[3]:
            data['groups'].append(grp)
        
    return JsonResponse(data)
'''
 
#业务员打卡记录
@login_required
def saleman_checkin(request):
    data = {'data': [], 'clmns': [], 'e': [],'subtt': ['页面管理', '新闻资讯'], 'murl': '', 'dt': False, 'add': True}
    data['clmns'] = ['业务员', '打卡终端店', '距离', '时间']
    data['show_add'] = False
    cur = connection.cursor()
    cur.execute("SELECT (select username from company_user where id = uid ),"
                "(SELECT name from company_company where id = dst_compid), id,uid,lng_lat,dst_compid,distance,tm "
                "FROM z_salesmancheckin")
    rs = cur.fetchall() 
    for r in rs:
        data['data'].append([
            str(r[0]),
            str(r[1]),
            str(r[6])+'米',
            r[7].strftime("%Y-%m-%d %H:%M:%S")
        ])
    return render(request, 'obj/list_obj.html', data)

def company_delete(request):
    ancestor_id = "19257" 
    cur = connection.cursor()
    #add_user_from_company(ancestor_id)
    return JsonResponse({'e':''})
     
    #for b in Batch.objects.filter(ancestor_id=int(ancestor_id)):
    #    print(b)
    #    b.delete()
    #return JsonResponse({'e':''})
    #开始删除终端店订单
    cur.execute("delete from company_agentorderdetails where ancestor_id="+ancestor_id)
    cur.execute("delete from company_agentorderbatch where ancestor_id="+ancestor_id)

    
    
    for b in Batch.objects.filter(ancestor_id=int(ancestor_id)):
        print(b)
        b.delete()
    
    com = Company.objects.filter(ancestor_id=ancestor_id,level_id__gt=2)
    for c in com:
        company_id = str(c.id)
        print(company_id)
        c.delete()
    # user = User.objects.filter(ancestor_id=int(ancestor_id),id__gt=3886)
    # for u in user:
    #     print(str(u.id))
    #     u.delete()
    return JsonResponse({'e':''})

    #删除白名单
    cur.execute("delete from company_white_domain where company_id="+ancestor_id)
    #删除标签绑定记录
    cur.execute("delete from company_third_party_label_bind where company_id="+ancestor_id)
    #删除营销人员区域管理
    cur.execute("delete from company_staffsaldiv where company_id="+ancestor_id)

    #开始删除铭牌权益
    #删除经销商等级记录
    cur.execute("delete from company_brands_agent_records where ancestor_id="+ancestor_id)
    #删除经销商等级
    cur.execute("delete from company_brands_agent_level where ancestor_id="+ancestor_id)
    #删除等级
    cur.execute("delete from company_brands_level where company_id="+ancestor_id)
    #删除品牌
    cur.execute("update product_product set brand_id = null where company_id="+ancestor_id)
    cur.execute("delete from company_brands where company_id="+ancestor_id)
    #删除权益规则
    cur.execute("delete from company_benefit_rule where company_id="+ancestor_id)
    #删除权益
    cur.execute("delete from company_benefit where company_id="+ancestor_id)
    #删除经销商任务
    cur.execute("delete from company_sale_task_allocate where company_id="+ancestor_id)
    #删除总任务
    cur.execute("delete from company_sale_task where company_id="+ancestor_id)
    #删除第三方公司
    cur.execute("delete from company_third_party_company where company_id="+ancestor_id)


    #开始删除活动数据
    #删除消费者扫码记录
    cur.execute("delete from sales_labelscan where company_id="+ancestor_id)
    #删除核销配送返现记录
    cur.execute("delete from sales_hexiao_record where company_id="+ancestor_id)
    #删除消费者中奖记录
    cur.execute("delete from sales_consumerwinner where company_id="+ancestor_id)
    #删除步奖记录
    cur.execute("delete from sales_bujiang_labels where company_id="+ancestor_id) 
    #开始删除活动
    for a in Activities.objects.filter(company_id=int(ancestor_id)):
        a.delete()
    #删除奖品
    cur.execute("delete from sales_award where company_id="+ancestor_id)

    #开始删除会员信息
    #删除会员积分记录
    cur.execute("delete from mall_userpoints_record where company_id="+ancestor_id)
    #删除会员积分
    cur.execute("delete from mall_userpoints where company_id="+ancestor_id)
    #删除商城用户地址
    cur.execute("delete from mall_user_address where company_id="+ancestor_id)
    #删除订单评论
    cur.execute("delete from mall_order_comment where company_id="+ancestor_id)
    #删除订单详情
    cur.execute("delete from mall_order_detail where company_id="+ancestor_id)
    #删除订单
    cur.execute("delete from mall_orders where company_id="+ancestor_id)
    #删除商品轮播图 
    cur.execute("delete from mall_points_products_banner where company_id="+ancestor_id)
    #删除商品
    cur.execute("delete from mall_points_products where company_id="+ancestor_id)
    #删除积分商城设置
    cur.execute("delete from mall_settings where company_id="+ancestor_id)
    #删除会员等级
    cur.execute("delete from sales_memberlevel where company_id="+ancestor_id)  
    #删除会员
    cur.execute("delete from sales_member where company_id="+ancestor_id)  

    #开始删除业务员打卡记录
    cur.execute("delete from z_salesmancheckin where ancestor_id="+ancestor_id)

    #



    #删除营销分区
    #cur.execute("delete from company_subsaldivision where company_id="+ancestor_id)
    #删除营销大区



    #开始删除节假日
    cur.execute("delete from sales_holiday where company_id="+ancestor_id)

  

    #开始删除出库入库数据
    
    for b in Batch.objects.filter(ancestor_id=int(ancestor_id)):
        print(b)
        b.delete()

    #删除准确的物流数据
    #cur.execute("delete from inventory_stkdetails where ancestor_id="+ancestor_id)
    #删除已上传的物流数据
    #cur.execute("delete from inventory_batchdetails where ancestor_id="+ancestor_id)
    #删除物流数据Batch
    #cur.execute("delete from inventory_batch where ancestor_id="+ancestor_id)
    #删除物流仓库
    cur.execute("delete from inventory_storehouse where company_id="+ancestor_id)
    #删除经销商订单
    cur.execute("delete from company_agentorderdetails where ancestor_id="+ancestor_id) 
    cur.execute("delete from company_agentorderbatch where ancestor_id="+ancestor_id)


    com = Company.objects.filter(ancestor_id=ancestor_id,level_id__gt=1)
    for c in com:
        company_id = str(c.id)
        c.delete()
    user = User.objects.filter(ancestor_id=int(ancestor_id),id__gt=3886)
    for u in user:
        print(str(u.id))
        u.delete()
        
    ss = Series.objects.filter(company_id=int(ancestor_id))
    for s in ss:
        print(str(s.id))
        s.delete()
    # pros = Product.objects.filter(company_id=int(ancestor_id))
    # for p in pros:
    #     print(str(p.id))
    #     p.delete()
    cur.execute("delete from product_productprop where company_id="+ancestor_id)
    cur.execute("delete from promotion_material where company_id="+ancestor_id)
    return JsonResponse({'e':'success'})


#添加经销商未导入的用户账号
def add_user_from_company(ancestor_id): 
    cur = connection.cursor()
    cur.execute("SELECT id,cell_phone FROM company_company WHERE ancestor_id = "+ancestor_id+
        "AND id not in (SELECT company_id FROM company_user WHERE ancestor_id = "+ancestor_id+")"+
        "and cell_phone not in (SELECT username FROM company_user WHERE ancestor_id = "+ancestor_id+") AND level_id = 4")
    rs = cur.fetchall()
    for r in rs:
        cur.execute("select count(*) from company_user where username = '"+r[1]+"'")
        rr = cur.fetchone() 
        if rr[0] == 0:
            u = User()
            u.username=r[1]
            u.phone=r[1]
            u.company_id=r[0]
            u.ancestor_id=int(ancestor_id)               
            u.set_password("123456")
            u.save()  
            g=Group.objects.get(id=5)                        
            u.groups.add(g) 



# 留言
@csrf_exempt
def leave_message(request):
    data = {
        'e':''
    }
    consumer_id = request.POST.get('consumer_id','')
    company_id = request.POST.get('company_id','')
    subject = request.POST.get('subject','')
    name = request.POST.get('name','')
    tel = request.POST.get('tel','')
    address = request.POST.get('address','') 
    message = request.POST.get('message','') 
    if consumer_id and company_id and name and tel and address:
        consumer_id = decrypt_p(consumer_id)
        cur = connection.cursor()
        dt = datetime.datetime.now().strftime('%Y-%m-%d')
        cur.execute("select count(*) from company_leave_message where consumer_id = "+consumer_id+" and "+
            "company_id = "+company_id +" and leave_date = '"+dt+"' ")
        ct = int(cur.fetchone()[0])
        if ct<=3:
            lm = Leave_Message(
                company_id=int(company_id),
                consumer_id=int(consumer_id),
                subject=subject,
                name=name,
                tel=tel,
                address=address,
                message=message
            )
            lm.save()
        else:
            data['e'] = '当天可提交次数已达上限'
    else:
        data['e'] = '数据丢失'
    return JsonResponse(data)

#消息留言
@login_required
def leave_message_list(request):
    data = {'data': [], 'clmns': [], 'e': [],'subtt': ['会员管理', '消息留言'], 'murl': '', 'dt': True, 'add': False}
    data['clmns'] = ['头像','微信昵称','主题','姓名', '电话','地址','消息','时间']


    d = datetime.datetime.now()
    dt = d.strftime('%Y-%m-%d')
    dt_fr = request.GET.get('date_from', (d + datetime.timedelta(days=-7)).strftime('%Y-%m-%d'))
    dt_to = request.GET.get('date_to', dt)
    data["date_from"]=dt_fr
    data["date_to"]=dt_to    
    dt_to = (datetime.datetime.now()+datetime.timedelta(days=1)).strftime("%Y-%m-%d")

    cur = connection.cursor()
    cur.execute("select (SELECT wx_nickname from sales_consumeraccount WHERE id = consumer_id),"+
	    "(SELECT wx_headimgurl from sales_consumeraccount WHERE id = consumer_id),"+
        "subject,"+
        "name,"+
        "tel,"+
        "address,"+
        "message,tm from company_leave_message where company_id = "+str(request.user.company.id)+" and tm>='"+dt_fr+"' and tm <='"+dt_to+"'")
    rs = cur.fetchall() 
    for r in rs:
        data['data'].append([
            "<img src='"+r[1]+"' style='width:30px' />",
            str(r[0]),
            str(r[2]),
            str(r[3]),
            str(r[4]),
            str(r[5]),
            str(r[6]),
            r[7].strftime("%Y-%m-%d %H:%M:%S")
        ])
    return render(request, 'obj/list_obj.html', data)

# 用户品牌关联
@login_required
def user_brands(request,id=None):
    data = {
        'e':'',
        'products':[]
    }
    if request.method == 'POST': 
        brand_ids = request.POST.get('brand_ids', '')
        bs = []
        if brand_ids:
            bs = brand_ids.split(',')
        
        obj = User.objects.get(id=int(id))
        obj.brand_ids = bs
        obj.save()
    cur = connection.cursor()
    ssql = ("select id,name from company_brands "
            "where array[id] <@ (select brand_ids from company_user where id = "+str(id)+" )")
    cur.execute(ssql)
    rs = cur.fetchall()
    for r in rs:
        data['products'].append({
            'id':r[0],
            'name':r[1]
        })
    return render(request, 'company/user_brands.html', data)


#2021-01-04
def auth_txt(request,file_name):   
    t='error'
    fs=[
        'AVQ7vT2y3X.txt',
        'kmaSPKL2qi.txt',
        'MP_verify_Apm8ueGlttXm3f3v.txt',
        'MP_verify_BMKOImLfGdRBoCVn.txt',
        'MP_verify_dDCad5nDr1PguTaz.txt',
        'MP_verify_KSszkH6otcVzVTgA.txt',
        'MP_verify_oHSdj8rdg7AalbNA.txt',
        'MP_verify_pCyo0qfelFAOiZIp.txt',
        'MP_verify_uebwFIuroWKtMUgv.txt',
        'MP_verify_waYqEcGM4koyXd8N.txt',
        'MP_verify_x0bWH2L8RAgYPkQ5.txt',
        'N3JGAyOSPx.txt',
        'QUU8m0PVzG.txt',
        'MP_verify_CjgWtBdarfFaJJyI.txt',
        'MP_verify_CTdc5Bn88a1G1ymt.txt',
        'MP_verify_eISzNVIZWvn0QrJ1.txt',
        '6867287938.txt',
        'MP_verify_eGgAfUEEbDyEyTUw.txt',
        'lxtNS0FugD.txt',
        'MP_verify_5LWl0fLxc3Bdvvhz.txt',
        'MP_verify_17cwI6HzCKVcUytQ.txt'
    ]
    if file_name in fs:    
        with open('F:\\datacenter\\media\\auth\\'+file_name, 'r') as f:        
            t=f.read()
    return HttpResponse(t)       




#2021-02-24
@login_required
def bulletinboard(request,id=None):
    is_saved=False
    div_ids=[]
    sal_divs=[]
    cur=connection.cursor()

    if id:        
        obj = BulletinBoard.objects.get(id=int(id))        
    else:
        obj = BulletinBoard(company_id=request.user.company.id,user_id=request.user.id)

    if request.method == 'GET':
        form = BulletinboardForm(instance=obj)
        formset=BulletinBoardAttachFormset(instance=obj) 
        
        s="select id,name from company_salesdivision where company_id="+str(request.user.company_id)+\
            " and rpt_shown=true order by code"
        cur.execute(s)
        for r in cur.fetchall():
            sal_divs.append([r[0],r[1]])
        ids=[]
        for d in obj.salesdivisions:
            ids.append(str(d))
        if ids:
            cur.execute("select id,name from company_salesdivision where id in  ("+",".join(ids)+")")
            for r in cur.fetchall():
                div_ids.append([r[0],r[1]])
    else:
        form = BulletinboardForm(request.POST, request.FILES, instance=obj)
        formset=BulletinBoardAttachFormset(request.POST, request.FILES, instance=obj)

        if form.is_valid():
            is_saved=True
            form.save()
            if formset.is_valid():
                formset.save()
            return  redirect("/obj/list/bulletinboard/")  

        #form=ChannelPromotionsForm(request.POST, request.FILES, instance=obj)
        #formset=ChannelRebateFormset(request.POST, request.FILES, instance=obj)


    return render(request, 'company/bulletinboard.html',
            {'form':form,'formset':formset,'is_saved=False':is_saved,'sal_divs':sal_divs,'div_ids':div_ids})


#2021-03-06    
def get_departments(dept,cur):
    d={}
    d['id']=dept['id']
    d['parent_id']=dept['parent_id']
    d['name']=dept['name']
    d['level']=dept['level']
    d['headcount']=dept['headcount']    
    d['children']=[]
    children=[]
    s="select id,name,level,coalesce(division_id,0),"+\
        "(select count(*) from logistics.company_staff where department_id=a.id) "+\
        "from logistics.company_department a where parent_id="+str(d['id'])+" order by sn"
    cur.execute(s)    
    for r in cur.fetchall():
        children.append({'id':r[0],'name':r[1],'level':r[2],'division_id':r[3],'headcount':{'current':r[4],'total':r[4]},'parent_id':d['id'] })           
    if children:
        d['children']=[get_departments(child,cur) for child in children]    
    for c in d['children']:
        d['headcount']['total']=d['headcount']['total']+c['headcount']['total']
    return d 


#2021-03-07
@login_required
def orginazition_edit(request,obj_name,id=None):
    u=request.user
    if obj_name=='department':
        is_saved=False
        subdivisions=[]
        if id:
            obj=Department.objects.get(id=int(id))
            if obj.division:
                for s in SubSalDivision.objects.filter(division_id=obj.division.id):
                    subdivisions.append([s.id,s.name])
        else:
            obj=Department(company_id=u.ancestor_id)
            pid=request.GET.get('parent_id','')
            if pid:
                pid=int(pid)
                ps=Department.objects.filter(id=pid)
                if ps:
                    p=ps[0]
                    obj.parent_id=pid
                    obj.division_id=p.division_id
                    obj.subsidiary=p.subsidiary
        if request.method=='GET':
            form=DepartmentForm(instance=obj,user=request.user)  
        else:
            
            form=DepartmentForm(request.POST,instance=obj,user=request.user)
            if form.is_valid():
                form.save()
                is_saved=True                
        r=render(request,'company/edit_department.html',{'form':form,'subdivisions':subdivisions,'is_saved':is_saved})
        return HttpResponse(r)
    elif obj_name=='position':
        is_saved=False       
        if id:
            obj=Position.objects.get(id=int(id))            
        else:
            obj=Position(company_id=u.ancestor_id)            
        if request.method=='GET':
            form=PositionForm(instance=obj)  
        else:            
            form=PositionForm(request.POST,instance=obj)
            if form.is_valid():
                form.save()
                is_saved=True   
        r=render(request,'company/edit_position.html',{'form':form,'is_saved':is_saved})
        return HttpResponse(r)
    elif obj_name=='staff':
        
        is_saved=False       
        if id:
            obj=Staff.objects.get(id=int(id))            
        else:
            obj=Staff(company_id=u.ancestor_id)            
        if request.method=='GET':
            if not id:
                dept_id=request.GET.get('dept_id','')
                if dept_id:
                    ds=Department.objects.filter(id=int(dept_id))
                    if ds:
                        obj.department=ds[0]
            form=StaffForm(instance=obj,user=request.user)  
        else:            
            form=StaffForm(request.POST,instance=obj,user=request.user)
            if form.is_valid():
                form.save()
                is_saved=True   
        r=render(request,'company/edit_staff.html',{'form':form,'is_saved':is_saved})
        return HttpResponse(r)




#2021-02-24
@login_required
def orginazition(request,what,id=None):
    u=request.user
    c=u.company
    aid=str(u.ancestor_id)
    cur=connection.cursor()
    if what=="vieworginazition": 
        departments=[]               
        cur.execute("select id,name from company_department where company_id="+str(aid)+" and level=0")
        r=cur.fetchone()
        if r:            
            departments=get_departments({'parent_id':0,'id':r[0],'name':r[1],'level':0,'division_id':0,'headcount':{'current':0,'total':0}},cur)
        cur.close()
        return render(request,'company/view_orginazition.html', {'departments':departments})
    elif what=="liststaff":
        if request.method=="GET":            
            id=request.GET.get('dept_id','')
            obj=Department.objects.get(id=int(id))
            sWhere=" where d.id="+id+" or d.parent_id="+id
            if obj.level==0:
                sWhere=''
            label=obj.get_label()            
            clmns=[_('姓名'),_('岗位职务'),_('部门'),_('部门负责人'),_('上级部门'),_('手机电话'),_('登录帐号')]
            data=[]          
            s="select s.id,s.name,coalesce(s.cellphone,u.phone,''),coalesce(u.username,''),d.name,"+\
                "(select name from company_department where id=d.parent_id),"+\
                "coalesce((select name from company_position where id=s.position_id),''),"+\
                "case when is_department_head then '是' else '否' end "+\
                "from "+\
                "(select * from company_staff a where company_id="+aid+") s "+\
                "left outer join "+\
                "(select * from company_department where company_id="+aid+") d "+\
                "on s.department_id=d.id "+\
                "left outer join "+\
                "(select id,username,last_name,phone from company_user where ancestor_id="+aid+") u "+\
                "on s.user_id=u.id "+sWhere            
            cur.execute(s)
            for r in cur.fetchall():
                a=r[1]
                if request.user.has_perm('company.sys_admin'):
                    a="<a href='/company/orginazition/edit/staff/"+str(r[0])+"/' tt='员工信息-修改' >"+r[1]+"</a>" 
                data.append([ a ,r[6],r[4],r[7],r[5],r[2],r[3] ])           
            r=render(request,'company/list_staff.html', {'label':label,'data':data,'clmns':clmns,'url':request.get_full_path()})
            cur.close()
            return HttpResponse(r)
    elif what=='listposition':
        data=[]
        clmns=['职务名称','备注说明']
        cur.execute("select id,name,coalesce(remark,'') from company_position where company_id="+aid+" and is_delete=false")
        for r in cur.fetchall():
            a=r[1]
            if request.user.has_perm('company.sys_admin'):
                a="<a href='/company/orginazition/edit/position/"+str(r[0])+"/' tt='岗位职务-修改' >"+r[1]+"</a>"
            data.append([a,r[2]])
        r=render(request,'company/list_position.html', {'data':data,'clmns':clmns,'url':request.get_full_path()})
        cur.close()
        return HttpResponse(r)

    #2021-04-27
    elif what=='cgroup_menu':
        if request.method=='GET':
            id=request.GET.get('cgroup_id','')
            if not id:
                cgroups=[]
                cur.execute("SELECT id, name FROM company_cgroup where company_id="+str(u.ancestor_id)+" and is_delete=false order by name")
                for r in cur.fetchall():
                    cgroups.append({'id':r[0],'name':r[1]})
                return render(request,'rbac/list_cgroup_menu.html', {'cgroups':cgroups})
            else:
                g=CGroup.objects.get(id=int(id))
                m=RbacMenu(company=None,cgroup=g)
                menuitems=m.get_menu()   
                cur.close()
                return render(request,'rbac/cgroup_menu.html',{'menuitems':menuitems,'cgroup':g})
        else:
            r=json.loads(request.body)   
            cgroup_id=r['cgroup_id']
            auth_ids=r['auth_ids']
            g=CGroupMenu.objects.get(cgroup_id=int(cgroup_id))
            auth_ids = [ int(x) for x in auth_ids ]            
            g.menus=auth_ids
            g.save()
            data={'e':''}
            cur.close()
            return JsonResponse(data)
            #return render(request,'rbac/cgroup_menu.html',{'menuitems':menuitems,'cgroup':g})

    #2021-04-27
    elif what=='cgroup':
        if request.method=='GET':           
            id=request.GET.get('id','')
            if id:
                obj=CGroup.objects.get(id=int(id))
                form=CGroupForm(instance=obj)
                return render(request,'company/edit_cgroup.html',{'form':form})
            else:
                data=[]
                clmns=['功能组别','停用','备注说明']
                cur.execute("SELECT id,name,coalesce(remark,''),case when is_delete then '是' else '否' end,"+\
                    "to_char(tm_edit,'yyyy-mm-dd HH24:HI:SS') FROM company_cgroup "+\
                    "where company_id="+str(request.user.ancestor_id)+" order by name")
                for r in cur.fetchall():
                    a='<a href="/company/orginazition/cgroup/?id='+str(r[0])+'">'+r[1]+'</a>'
                    data.append([ a ,r[3],r[2] ])
                return render(request,'company/list_cgroup.html',{'clmns':clmns,'data':data})
        else:
           
            #id=request.POST['id']

            obj=CGroup.objects.get(id=int(id))
            form=CGroupForm(request.POST,instance=obj)
            is_saved=False
           
            if form.is_valid():
                form.save()
                is_saved=True
            return render(request,'company/edit_cgroup.html',{'form':form,'is_saved':is_saved})
            #return JsonResponse(data)

@login_required
@csrf_exempt
def ajax_upload_file(request):
    u=request.user
    c=u.company.name
    p=os.path.join(MEDIA_ROOT,c,'uploadfile')
    data={'e':'','uri':''}
    if request.method == 'POST':       
        file = request.FILES.get('file',None)           
        if file:
            a=file.name.split('.')
            if len(a)>1:
                ext='.'+a[1]
            else:
                ext=''
            day_dir = datetime.datetime.now().strftime('%Y%m%d')
            pre_dir = os.path.join(p,day_dir).replace('/','\\')
            if not os.path.exists(pre_dir):
                os.makedirs(pre_dir)
            fn=str(uuid.uuid4())+ext
            filename=os.path.join(pre_dir,fn)            
            with open(filename,'wb') as f:
                for line in file.chunks():
                    f.write(line)
            data['uri']="/media/"+c+"/uploadfile/"+day_dir+"/"+fn
        else:
            data['e']='file missing'

    
    
    return JsonResponse(data)


def test_add_user(request):
    ancestor_id = "19651"
    cur = connection.cursor()
    cur.execute("select id,name,cell_phone,level_id FROM company_company a WHERE ancestor_id =19651"+
                "and (SELECT count(*) from company_user where company_id = a.id) = 0 "+
                "and (SELECT count(*) from company_user where username = a.cell_phone) = 0 and cell_phone is not null and cell_phone !='' ")
    rows = cur.fetchall()
    for r in rows:
        id = r[0]
        name = r[1]
        cell_phone = r[2]
        level_id = r[3]

        u = User()

        if cell_phone:
            u.username = cell_phone
        else:
            u.username = name
        u.company_id = id
        u.ancestor_id = int(ancestor_id)

        u.set_password("123456")

        u.save() 
        gs=Group.objects.filter(id=5)
        if gs:
            u.groups.add(gs[0])   



        #print(id,name)

    return JsonResponse({'e':''})


#2021-04-05
def get_company_applicationreviewlog(content_type,obj_id):    
    cur=connection.cursor()
    l=[]
    s="select to_char(tm,'yyyy-mm-dd HH24:MI'),coalesce(comment,''),"+\
        "(select last_name from company_user where id=user_id),"+\
        "(select name from company_applicationstatus where id=status_id),"+\
        "coalesce((select name from company_company where id=company_id),'') "+\
        "from company_applicationreviewlog "+\
        "where content_type='"+content_type+"' and obj_id="+str(obj_id)+" order by id"
    cur.execute(s)
    for r in cur.fetchall():
        l.append({
            'time':r[0],
            'comment':r[1],
            'user':r[2],
            'status':r[3],            
        })
    cur.close()
    return l


@login_required
def html_company_applicationreviewlog(request):
    content_type=request.GET.get('content_type','')
    obj_id=request.GET.get('obj_id','')
    l=get_company_applicationreviewlog(content_type,obj_id)
    return render(request,'company/company_applicationreviewlog.html',{"logs":l})



#2021-05-10
@login_required
def obj_edit(request,obj_name,id):
    data={'e':'','data':[]}
    m = apps.get_model('company',obj_name)
    objs=m.objects.filter(id=int(id))
    if objs:
        o=objs[0]
    else:
        data['e']='object id error'
        return JsonResponse(data)
 
    u=request.user
    cur=connection.cursor()
    group_ids=[]
    cur.execute("select group_id FROM company_user_groups where user_id="+str(u.id))
    for r in cur.fetchall():
        group_ids.append(r[0])
    
    if 4 in group_ids:  #4;"系统管理员" 18;"财务" 20;"出纳"
        if request.method=='GET':
            return render(request,'company/edit_'+obj_name.lower()+'.html',{'obj':o})    
        else:  
            r=json.loads(request.body)
            #class ChangeLog(models.Model):
            #    company = models.ForeignKey('company.Company', verbose_name=_('公司'), related_name='changelog_company', on_delete=models.PROTECT) 
            #    ancestor_id=models.IntegerField(_('族系'), db_index=True)
            #    user = models.ForeignKey('company.User', verbose_name=_('操作人'), on_delete=models.PROTECT, related_name='changelog_user')     
            #    obj_name=models.CharField(_('对象名称'),max_length=20, db_index=True)    
            #    obj_id=models.IntegerField(_('对象id'),db_index=True)
            #    tm = models.DateTimeField(_('时间'), auto_now_add=True)
            #    dt = models.DateField(_('日期'), auto_now_add=True, db_index=True)
            #    remark=models.CharField(_('备注说明'),max_length=100,blank=True,null=True)
            #if obj_name=='bankinfoapplication':
            #    remark='收款行帐号:'+o.bank_account+" -> "+r['bank_account']
            #    o.bank_account=r['bank_account']
            #if obj_name=='WxWallet':                
            #    remark='收款人实名:'+o.realname+" -> "+r['realname']
            #    o.realname=r['realname']
            #o.save()
            if obj_name=='wxwalletapplication':
                remark='收款人实名:'+o.realname+" -> "+r['realname']
                o.realname=r['realname']
                o.save()
                ws=WxWallet.objects.filter(application_id=int(id))
                if ws:
                    w=ws[0]
                    w.realname=r['realname']
                    w.save()
            elif obj_name=='bankinfoapplication':
                remark='帐号:'+o.bank_account+"->"+r['bank_account']+\
                    ',开户行:'+o.bank_name+"->"+r['bank_name']+\
                    ',收款人:'+o.payee+"->"+r['payee']
                o.bank_account=r['bank_account']
                o.bank_name=r['bank_name']
                o.payee=r['payee']
                o.save()
                bs=BankInfo.objects.filter(application_id=int(id))
                if bs:
                    b=bs[0]
                    b.bank_account=r['bank_account']
                    b.save()
            l=ChangeLog(company=u.company,ancestor_id=u.ancestor_id,user_id=u.id,obj_name=obj_name,obj_id=int(id),remark=remark)
            l.save()            
            return JsonResponse(data)
    else:
        data['e']='未授权的操作'
        return JsonResponse(data)
            

#2021-07-23
@login_required
def franchise_outlet(request):
    if request.method=='GET':
        is_view=request.GET.get('is_view','0')
        gis_city_id=request.GET.get('gis_city_id','')
        lng_lat=request.GET.get('lng_lat','')
        el_id=request.GET.get('el_id','')
        id=request.GET.get('obj_id','')
        outlet_name=request.GET.get('outlet_name','未知门店名称')
        province_name=request.GET.get('provinve','')
        city_name=request.GET.get('city','')
        
        center={'lng':116.404,'lat':39.915} 
        centerc=''
        cid=str(request.user.company.id)
        
        cur=connection.cursor()
        if lng_lat:
            a=lng_lat.split(',')
            center['lng']=a[0]
            center['lat']=a[1]
        elif gis_city_id:            
            cur.execute("SELECT public.st_x(public.ST_Centroid(geom)),public.st_y(public.ST_Centroid(geom)) FROM gis.adm where id="+gis_city_id)
            r=cur.fetchone()
            if r:
                center['lng']=r[0]
                center['lat']=r[1]  
        elif province_name and city_name:
            centerc=province_name+city_name
        series_legends=[]    
        status=[]
        outlets=[]
        s="select name,icon from product_series where company_id="+cid
        cur.execute(s)
        for r in cur.fetchall():
            series_legends.append({'name':r[0],'icon':r[1]})
        s="select s.name,c.en_name,c.name from "+\
            "(SELECT name,color_id FROM company_outletstatus where company_id="+cid+") s "+\
            "left outer join "+\
            "(select id,name,code,en_name from company_color) c "+\
            "on s.color_id=c.id"
        cur.execute(s)
        for r in cur.fetchall():
            status.append({'name':r[0],'color_code':r[1],'color_name':r[2]})
        sId=''
        if id:
            if id!='None':
                sId=" and id<>"+id
        s="select o.id,o.lng_lat,s.name,coalesce(s.icon,''),coalesce(c.name,''),coalesce(c.en_name,'') "+\
            "from "+\
            "(SELECT name,lng_lat,company_id,prdduct_id,series_id,status_id,id FROM company_outlet "+\
                "where ancestor_id="+cid+" and array_length(lng_lat,1)>0 "+sId+") o "+\
            "left outer join "+\
            "(select id,name,icon from product_series where company_id="+cid+") s "+\
            "on o.series_id=s.id "+\
            "left outer join "+\
            "(SELECT id, name, remark, color_id FROM company_outletstatus) t "+\
            "on o.status_id=t.id "+\
            "left outer join "+\
            "(SELECT id, name, code, en_name  FROM logistics.company_color) c "+\
            "on t.color_id=c.id "
        cur.execute(s)
        for r in cur.fetchall():
            outlets.append({
                "id":r[0],               
                "series":{"name":r[2],"icon":r[3]},
                "color":{"name":r[4],"code":r[5]},                
                "lnglat":{"lng":float(r[1][0]),"lat":float(r[1][1])},
            })    
        cur.close()
        data={'center':center,'centerc':centerc,'lng_lat':lng_lat,'el_id':el_id,'outlet_name':outlet_name,'outlets':outlets,'series_legends':series_legends,'status':status}
        
        #print(data)
        html='company/franchise/outlet_location.html'
        #if request.user.id==11783:
        #    html='company/franchise/outlet_location_admin.html'
        return render(request,html,data) 



#2021-07-23
@login_required
def view_outlet(request,id=None):
    data={'e':''}
    u=request.user
    c=u.company
    if c.level_id!=1:
        data['e']='company level error'
        return JsonResponse(data)
    cur=connection.cursor() 
    if id:
        cur.close()        
    else:
        cid=str(c.id)           
        series_legends=[]    
        status=[]
        outlets=[]
        s="select name,icon from product_series where company_id="+cid
        cur.execute(s)
        for r in cur.fetchall():
            series_legends.append({'name':r[0],'icon':r[1]})
        s="select s.name,c.en_name,c.name,s.id from "+\
            "(SELECT id,name,color_id FROM company_outletstatus where company_id="+cid+") s "+\
            "left outer join "+\
            "(select id,name,code,en_name from company_color) c "+\
            "on s.color_id=c.id"
        cur.execute(s)
        for r in cur.fetchall():
            status.append({'name':r[0],'color_code':r[1],'color_name':r[2],'id':r[3]})
        
        data={'e':'','series_legends':series_legends,'status':status}
        cur.close()
        html='company/franchise/view_outlet.html'   
        return render(request,html,data) 
        

#2021-08-09
@login_required
def edit_outlet(request,id=None):
    u=request.user
    c=u.company
    is_saved=False
    if id:        
        obj = Outlet.objects.get(id=int(id))        
    else:
        obj = Outlet(ancestor_id,c.ancestor_id)
    if request.method=='GET':
        form=OutletForm(instance=obj)
        form.fields['series'].queryset=Series.objects.filter(company_id=c.id)
        form.fields['product'].queryset=Product.objects.filter(company_id=c.id)
        form.fields['status'].queryset=OutletStatus.objects.filter(company_id=c.id)
    else:
        form=OutletForm(request.POST,instance=obj)
        
    return render(request,'company/franchise/edit_outlet.html',{'form':form,'is_saved':is_saved})



def outlet_info(o):
        city = ''
        address = ''
        remark=''
        series={}
        status={}
        if o.remark:
            remark=o.remark
        if o.address:
            address = o.address
        if o.city_gis:
            city = o.city_gis['name']
        if o.series:
            series={'id':o.series.id,'name':o.series.name}
        if o.status:
            status={'id':o.status.id,'name':o.status.name}
        d = {
            'id': o.id,
            'name': o.name,
            'company': {'id': o.id, 'name':o.company.name, 'code':o.company.external_code},
            'series': series,
            'status': status,
            'remark': remark,
            'city': city,
            'address': address
        }
        return d


#2021-07-31
@login_required
def outlet_get(request,what,id=None):
    data={'e':''}
    if what=='list':
        cur=connection.cursor()
        cid=str(request.user.company_id)
        status_id=request.GET.get('status_id','')
        sStatus=''
        if status_id:
            sStatus=" and status_id="+status_id
        s="select o.name,o.lng_lat,s.name,s.icon,t.name,c.name,c.en_name,"+\
            "(select name from company_company where id=o.company_id),o.id "+\
            "from "+\
            "(SELECT name,lng_lat,company_id,prdduct_id,series_id,status_id,id FROM company_outlet "+\
                "where ancestor_id="+cid+" and array_length(lng_lat,1)>0 "+sStatus+") o "+\
            "left outer join "+\
            "(select id,name,icon from product_series where company_id="+cid+") s "+\
            "on o.series_id=s.id "+\
            "left outer join "+\
            "(SELECT id, name, remark, color_id FROM company_outletstatus) t "+\
            "on o.status_id=t.id "+\
            "left outer join "+\
            "(SELECT id, name, code, en_name  FROM logistics.company_color) c "+\
            "on t.color_id=c.id "
        cur.execute(s)
        a=[]
        for r in cur.fetchall():
            a.append({
                "id":r[8],
                "name":r[0],
                "series":{"name":r[2],"icon":r[3]},
                "color":{"name":r[5],"code":r[6]},
                "status":r[4],
                "lnglat":{"lng":float(r[1][0]),"lat":float(r[1][1])},
            })
        data['outlets']=a
    elif what=='outlet':
        o=Outlet.objects.get(id=int(id))
        data['data']=outlet_info(o)
    return JsonResponse(data)
        

#2021-08-10
@login_required
def franchise_action(request,what,action,id):
    data={'e':''}
    u=request.user
    t=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    if what=='agent':
        os=Company.objects.filter(id=int(id))
        if not os:
            data['e']='agent id error'
        else:
            o=os[0]
            if action=='approve':
                o.approve_by_id=u.id
                o.approve_tm=t
                o.save()
            else:
                data['e']='action name error'
    elif what=='outlet':
        os=Outlet.objects.filter(id=int(id))
        if not os:
            data['e']='outlet id error'
        else:
            o=os[0]
            if action=='approve':
                o.approve_by_id=u.id
                o.approve_tm=t
                o.save()
            else:
                data['e']='action name error'
    return JsonResponse(data)
