from django.shortcuts import render, redirect
import io
#from django.http import FileResponse
from django.http import HttpResponse,HttpResponseRedirect,JsonResponse
from reportlab.pdfgen import canvas

# Create your views here.
from django.utils.translation import gettext_lazy as _
from calendar import monthrange
from utils.views import *
from django.db import connection
from .forms import *
from .models import *
from django.apps import apps
import datetime

from lxml import etree
import xlrd
from xlrd import *
from urllib.parse import urlencode
from http.client import HTTPConnection
from random import shuffle 
from wx.views import send_hb


from django.contrib.auth.decorators import login_required
from mall.views import update_member_points_consumer
sK = 'fgfdGvgu$#&3tE@j'

#2021-03-02
from wx.views import start_wx_enterprise_pay

from product.models import Product
#wx/wx_enterprise_pay.py
#start_wx_enterprise_pay



@tj_login_required
def qry_month(request):
    total = 0
    prevTotal = 0
    cid=str(request.user.company.id)
    dt=datetime.datetime.now()
    y=dt.year
    years=[]
    for i in range(2019,y+1):
        years.append(str(i))

    dt0=dt.replace(day=1) - datetime.timedelta(days=1)
    if cid == "19257":
        dt0 = datetime.datetime.now()
    y=str(dt0.year)
    m=str(dt0.month).zfill(2)
    yr=request.GET.get('year',y)
    mn=request.GET.get('month',m)
    d=str(monthrange(int(yr), int(mn))[1])
    dt1=str(yr)+'-'+mn+'-01'
    dt2=str(yr)+'-'+mn+'-'+d
    data={
        'quantitys':{'category':[],'data':[]},
        'agents'   :{'category':[],'data':[]},
        'products' :{'data':[]},
        'years':years,
        'y':yr,
        'm':mn,
        'total': total,
        'series': {'data': []},
        'prev_total': prevTotal,
        'cid': cid
    }
    datap = {
        'quantitys': {'category': [], 'data': []},
        'agents': {'category': [], 'data': []},
        'products': {'data': []},
        'years': years,
        'y': yr,
        'm': mn,
        'total': total,
        'prev_total': prevTotal,
        'cid': cid
    }

    def queryAction(cid, dt1, dt2, data):
        #查询总数
        sWhere="where company_id="+cid+" and vtype_id=2 and (dt between '"+dt1+"' and '"+dt2+"')"
        cur=connection.cursor()

        s="select dt,"+\
            "sum((select count(*) from inventory_stkdetails where batch_id=a.id)) "+\
            "from inventory_batch a "+sWhere+" group by dt order by dt"
        cur.execute(s)
        for r in cur.fetchall():
            if int(r[1])>0:
                data['quantitys']['category'].append(str(r[0].day).zfill(2))
                data['quantitys']['data'].append(int(r[1]))

        s="select (select name from product_product where id=b.product_id),b.qty qty"+\
            " from (select product_id,"+\
                "sum((select count(*) from inventory_stkdetails where batch_id=a.id)) qty "+\
                "from inventory_batch a "+sWhere+" group by product_id order by qty desc limit 20) b"
        cur.execute(s)
        for r in cur.fetchall():
            if int(r[1])>0:
                data['products']['data'].append({'value':int(r[1]),'name':r[0].replace('\\','')})
            #data['products']['category'].append(r[0])
            #data['products']['data'].append(int(r[1]))

        s="select (select name from company_company where id=b.company_to_id),b.qty,b.company_to_id"+\
            " from (select company_to_id,"+\
                "sum((select count(*) from inventory_stkdetails where batch_id=a.id)) qty "+\
                "from inventory_batch a "+sWhere+" group by company_to_id order by qty desc limit 20) b"
        cur.execute(s)
        for i, r in enumerate(cur.fetchall()):
            #data['agents']['category'].append(r[0])
            #data['agents']['data'].append(int(r[1]))
            if int(r[1])>0:
                data['agents']['data'].append({'value':int(r[1]),'name':r[0], 'index': i, 'company_to_id': r[2]})
    def queryPreviousTotal(cid, dt3, dt4):
        cur=connection.cursor()
        s = "select sum(b.sum) from (select sum((select count(*) from logistics.inventory_stkdetails where batch_id = a.id)) from logistics.inventory_batch a where company_id = "+cid+" and vtype_id = 2 and (dt between '"+dt3+"' and '"+dt4+"') group by dt order by dt)b"
        cur.execute(s)
        r=cur.fetchone()
        return r[0]
        
    #---- 查询:按系列分类的总数
    sql = "select series_id,cnt,f.name from (select (select series_id from logistics.product_product where id=product_id) series_id,count(*) cnt " \
          "from logistics.inventory_stkdetails where vtype_id=2 and company_id='"+cid+"' and (dt between '"+dt1+"' and '"+dt2+"'"+") " \
          "group by series_id)e left join logistics.product_series f on e.series_id=f.id order by cnt desc"
    cur = connection.cursor()
    cur.execute(sql)
    for r in cur.fetchall():
        r0 = r[0]
        r1 = r[1]
        r2 = r[2]
        if(r[0] == None):
            r0 = "无"
            r2 = "其他"
        data['series']['data'].append({'series':r0, 'count':r1, 'name': r2})
    
    #---- 查询:按系列分类的总数
    pyr = yr                                      #(可能的)上一年pyr
    pM = str(int(mn.lstrip('0')) - 1).zfill(2)    #上个月
    if mn=="01":                                #如果跨年, 这个月是1月，那么上个月是12月，年 - 1
        pM = "12"
        pyr = str(int(yr) - 1)
    dt3 = str(pyr) + '-' + pM + '-01'             #日期的节点
    dt4 = str(pyr) + '-' + pM + '-' + str(monthrange(int(pyr), int(pM))[1])
    queryAction(cid, dt1, dt2, data)
    #queryAction(cid, dt3, dt4, datap)

    for num in data['quantitys']['data']:   #设置总数
        data['total'] += num
    # for num in datap['quantitys']['data']:
    #     datap['total'] += num
    #data['prev_total'] = datap['total']     #替换要发送的data中的prev_total

    data['prev_total'] = str(queryPreviousTotal(cid, dt3, dt4))

    data['products']['data'].reverse()
    #print(data)
    return render(request,'sales/monthly.html',data)

def ajax_qry_agent_detail(request):
    response_array = []

    dt=datetime.datetime.now()
    dt0=dt.replace(day=1) - datetime.timedelta(days=1)
    y=str(dt0.year)
    m=str(dt0.month).zfill(2)

    yr=request.GET.get('year',y)
    mn=request.GET.get('month',m)
    d=str(monthrange(int(yr), int(mn))[1])
    dt1=str(yr)+'-'+mn+'-01'
    dt2=str(yr)+'-'+mn+'-'+d

    cid = request.GET.get('cid')
    comp_to_id = request.GET.get('comp_to_id')
    type = request.GET.get('type')
    if cid!=None and comp_to_id!=None and type !=None:
        if type == "agent":
            sql = "select a.product_id, a.count, b.name from (select product_id,count(*) from logistics.inventory_stkdetails " \
                  "where company_id="+cid+" and company_to_id="+comp_to_id+" and vtype_id=2 and (dt between '"+dt1+"' and '"+dt2+"')" \
                  " group by product_id) a " \
                  "left join logistics.product_product b on a.product_id = b.id"
            cur = connection.cursor()
            cur.execute(sql)
            for r in cur.fetchall():
                response_array.append({"name": r[2], "count": r[1]})
            return JsonResponse({"value": response_array})
        elif type == "series":
            series = request.GET.get('series')
            if series != '无':
                sql = "select * from (select a.count, b.name, b.series_id from (select product_id, count(*) " \
                      "from logistics.inventory_stkdetails where company_id='"+cid+"' and vtype_id=2 and (dt between '"+dt1+"' and '"+dt2+"')" \
                      "group by product_id) a " \
                      "left join logistics.product_product b " \
                      "on a.product_id=b.id) c where series_id="+series+" order by c.count desc limit 20"
            else:
                sql = "select * from (select a.count, b.name, b.series_id from (select product_id, count(*) " \
                      "from logistics.inventory_stkdetails where company_id='" + cid + "' and vtype_id=2 and (dt between '" + dt1 + "' and '" + dt2 + "')" \
                      "group by product_id) a " \
                      "left join logistics.product_product b " \
                      "on a.product_id=b.id) c where series_id is null order by c.count desc limit 20"
            cur = connection.cursor()
            cur.execute(sql)
            for r in cur.fetchall():
                #print(r)
                r1 = r[1]
                r0 = r[0]
                if(len(r[1]) == 0):
                    r1 = "未知"
                response_array.append({"name": r1, "count": r0})
            return JsonResponse({"value": response_array})
        else:
            return JsonResponse({"e": 'Invalid type.'})
    else:
        return JsonResponse({'e': 'Lack of parameters.'})
    return JsonResponse([],safe=False)


def ajax_qry_month(request, vtype_id):
    cid = request.GET.get('company_id', '')
    e = time_key_check(request, sK)
    if e:
        return JsonResponse({'e': e})
    if not cid:
        return JsonResponse({'e': 'company_id err'})

    dt = datetime.datetime.now()
    y = dt.year
    years = []
    for i in range(2019, y + 1):
        years.append(str(i))

    dt0 = dt.replace(day=1) - datetime.timedelta(days=1)
    y = str(dt0.year)
    m = str(dt0.month).zfill(2)
    yr = request.GET.get('year', y)
    mn = request.GET.get('month', m)
    d = str(monthrange(int(yr), int(mn))[1])
    dt1 = str(yr) + '-' + mn + '-01'
    dt2 = str(yr) + '-' + mn + '-' + d

    dt1 = str(yr) + '-' + mn + '-01'
    dt2 = str(yr) + '-' + mn + '-' + d
    dt1 = request.GET.get('date_from', dt1)
    dt2 = request.GET.get('date_to', dt2)

    if vtype_id == '2':
        data = {
            'quantitys': {'category': [], 'data': []},
            'agents': {'category': [], 'data': []},
            'products': {'data': []},
            'years': years,
            'y': yr,
            'm': mn,
            'date_from': dt1,
            'date_to': dt2
        }
    elif vtype_id == '1':
        data = {
            'quantitys': {'category': [], 'data': []},
            'products': {'data': []},
            'years': years,
            'y': yr,
            'm': mn,
            'date_from': dt1,
            'date_to': dt2
        }

    sWhere = "where company_id=" + cid + " and vtype_id=" + vtype_id + " and (dt between '" + dt1 + "' and '" + dt2 + "')"
    cur = connection.cursor()

    s = "select dt," + \
        "sum((select count(*) from inventory_stkdetails where batch_id=a.id)) " + \
        "from inventory_batch a " + sWhere + " group by dt order by dt"
    cur.execute(s)
    for r in cur.fetchall():
        # data['quantitys']['category'].append(str(r[0].day).zfill(2))
        data['quantitys']['category'].append(r[0].strftime('%Y-%m-%d'))
        data['quantitys']['data'].append(int(r[1]))

    s = "select (select name from product_product where id=b.product_id),b.qty qty" + \
        " from (select product_id," + \
        "sum((select count(*) from inventory_stkdetails where batch_id=a.id)) qty " + \
        "from inventory_batch a " + sWhere + " group by product_id order by qty desc limit 10) b"
    cur.execute(s)
    for r in cur.fetchall():
        data['products']['data'].append({'value': int(r[1]), 'name': r[0]})
        # data['products']['category'].append(r[0])
        # data['products']['data'].append(int(r[1]))

    if vtype_id == '2':
        s = "select (select name from company_company where id=b.company_to_id),b.qty qty" + \
            " from (select company_to_id," + \
            "sum((select count(*) from inventory_stkdetails where batch_id=a.id)) qty " + \
            "from inventory_batch a " + sWhere + " group by company_to_id order by qty desc limit 10) b"
        cur.execute(s)
        for r in cur.fetchall():
            # data['agents']['category'].append(r[0])
            # data['agents']['data'].append(int(r[1]))
            data['agents']['data'].append({'value': int(r[1]), 'name': r[0]})

    c = request.GET.get('callback', '')
    if c:
        response = json.dumps(data, ensure_ascii=False)
        return HttpResponse(c + '(' + response + ')')
    else:
        return JsonResponse(data)


@tj_login_required
def qry_date(request):
    cid = str(request.user.company.id)
    dt = datetime.datetime.now()
    y = dt.year
    years = []
    for i in range(2019, y + 1):
        years.append(str(i))

    dt0 = dt.replace(day=1) - datetime.timedelta(days=1)
    if cid =="19257":
        dt0= datetime.datetime.now()
    y = str(dt0.year)
    m = str(dt0.month).zfill(2)
    yr = request.GET.get('year', y)
    mn = request.GET.get('month', m)
    d = str(monthrange(int(yr), int(mn))[1])

    dt1 = str(yr) + '-' + mn + '-01'
    dt2 = str(yr) + '-' + mn + '-' + d
    dt1 = request.GET.get('date_from', dt1)
    dt2 = request.GET.get('date_to', dt2)

    data = {
        'quantitys': {'category': [], 'data': []},
        'agents': {'category': [], 'data': []},
        'products': {'data': []},
        'years': years,
        'y': yr,
        'm': mn,
        'date_from': dt1,
        'date_to': dt2
    }
    sWhere = "where company_id=" + cid + " and vtype_id=2 and (dt between '" + dt1 + "' and '" + dt2 + "')"
    cur = connection.cursor()

    s = "select dt," + \
        "sum((select count(*) from inventory_stkdetails where batch_id=a.id)) " + \
        "from inventory_batch a " + sWhere + " group by dt order by dt"
    cur.execute(s)
    for r in cur.fetchall():
        data['quantitys']['category'].append(r[0].strftime('%Y-%m-%d'))
        data['quantitys']['data'].append(int(r[1]))

    s = "select (select name from product_product where id=b.product_id),b.qty qty" + \
        " from (select product_id," + \
        "sum((select count(*) from inventory_stkdetails where batch_id=a.id)) qty " + \
        "from inventory_batch a " + sWhere + " group by product_id order by qty desc limit 10) b"
    cur.execute(s)
    for r in cur.fetchall():
        data['products']['data'].append({'value': int(r[1]), 'name': r[0]})
        # data['products']['category'].append(r[0])
        # data['products']['data'].append(int(r[1]))

    s = "select (select name from company_company where id=b.company_to_id),b.qty qty" + \
        " from (select company_to_id," + \
        "sum((select count(*) from inventory_stkdetails where batch_id=a.id)) qty " + \
        "from inventory_batch a " + sWhere + " group by company_to_id order by qty desc limit 10) b"
    cur.execute(s)
    for r in cur.fetchall():
        # data['agents']['category'].append(r[0])
        # data['agents']['data'].append(int(r[1]))
        data['agents']['data'].append({'value': int(r[1]), 'name': r[0]})

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



@tj_login_required
def agents_scan(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)
    data={'lv1':[],'lv2':[],'e':'',"date_from":dt_fr,"date_to":dt_to}
    cur=connection.cursor()
    c=request.user.company
    cid=str(c.id)
    if c.level.sn==0:
        s="select (select name from company_company where id=r.company_id),"+\
            "coalesce(r.cnt::int,0),coalesce(c.cnt::int,0) from "+\
            "(select company_id,sum((select count(*) cnt from inventory_batchdetails where batch_id=a.id)) cnt"+\
                " from inventory_batch  a "+\
	        " where vtype_id=1 and level_id=2 and ancestor_id="+cid+" and company_id<>"+cid+\
                " and dt between '"+dt_fr+"' and '"+ dt_to+"'"+\
                " group by company_id"+\
            ") r "+\
            " left outer join "+\
            "(select company_id,sum((select count(*) cnt from inventory_batchdetails where batch_id=a.id)) cnt"+\
                " from inventory_batch  a where vtype_id=2"+\
                " and ancestor_id="+cid+" and company_id<>"+cid+\
                " and dt between '"+dt_fr+"' and '"+ dt_to+"'"+\
                " group by company_id"+\
            ") c "+\
	    "on r.company_id=c.company_id "+\
            "where (select cell_phone from company_company where id=r.company_id) is not null"
        cur.execute(s)
        for r in cur.fetchall():
            if r[2]==0 or r[1]==0:
                rt=''
            else:
                rt=str(round(r[2]*100/r[1],2))+'%'
            data['lv1'].append([
                r[0],r[1],r[2],rt
            ])
        
        s="select (select name from  company_company where id=s.company_id),"+\
            "(select name from company_company where id=s.company_to_id),"+\
            "coalesce(s.cnt::int,0),coalesce(r.cnt::int,0),coalesce(c.cnt::int,0) from "+\
            "(select company_id,company_to_id,sum((select count(*) cnt from inventory_batchdetails where batch_id=a.id)) cnt "+\
                "from inventory_batch a where vtype_id=2 and level_id=2 and  ancestor_id="+cid+\
                " and dt between '"+dt_fr+"' and '"+ dt_to+"'"+\
                " group by company_id,company_to_id"+\
            ") s "+\
            "left outer join "+\
            "(select company_id,sum((select count(*) cnt from inventory_batchdetails where batch_id=a.id)) cnt "+\
                "from inventory_batch a	where vtype_id=1 and level_id=4 and ancestor_id="+cid+\
                " and dt between '"+dt_fr+"' and '"+ dt_to+"'"+\
                " group by company_id"+\
            ") r "+\
            "on s.company_to_id=r.company_id "+\
            "left outer join "+\
            "(select company_id,sum((select count(*) cnt from inventory_batchdetails where batch_id=a.id)) cnt "+\
                "from inventory_batch a	where vtype_id=2 and level_id=4 and ancestor_id="+cid+\
                " and dt between '"+dt_fr+"' and '"+ dt_to+"'"+\
                " group by company_id"+\
            ") c "+\
            "on s.company_to_id=c.company_id "+\
            "where r.cnt>0"
        
        cur.execute(s)
        for r in cur.fetchall():
            data['lv2'].append([
                r[0],r[1],r[2],r[3],r[4]    
            ])

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




@login_required
def edit_obj(request,obj_name,id=None):
    data={'form':None,'is_saved':False,'companys':[]}
    cur=connection.cursor()
    u=request.user
    c=u.company 
    is_saved=False   
    can_edit=False
    if u.id in [1,3506]:
        for co in Company.objects.filter(level_id=1).order_by('name'):
            data['companys'].append([co.id,co.name])
    else:
        data['companys'].append([c.id,c.name])
    m=apps.get_model('sales', obj_name)
    if id:
        obj = m.objects.get(id=int(id))        
        #data['company_id']=obj.company_id
    else:
        obj = m()
        obj.user=u
        #obj.company=c
        obj.status=0
    if request.method=='GET':
        if obj_name=='Award':        
            form=AwardForm(instance=obj)            
        elif obj_name=='Activities':
            cur.execute("SELECT id, name  FROM gis.adm where level=1 order by name")
            form=ActivitiesForm(instance=obj,user=u)
            formset=ActivityAwardsFormset(instance=obj) 
            a=[]
            for r in cur.fetchall():
                a.append( [r[0], r[1] ])
            data['prs']=a
            agents=[]
            products=[]
            levels=[]
            adms=[]
            batch_ids=[]
            if id:
                cur.execute("select id,name from company_company where id in "+\
                    "(select unnest((select agents from sales_activities where id="+id+")))"
                )
                for r in cur.fetchall():
                    agents.append([ r[0],r[1] ])
                cur.execute("select id,name from product_product where id in "+\
                    "(select unnest((select products from sales_activities where id="+id+")))"
                )
                for r in cur.fetchall():
                    products.append([ r[0],r[1] ])
                cur.execute("select id,name from company_level where id in "+\
                    "(select unnest((select agent_lvs from sales_activities where id="+id+")))"
                )
                for r in cur.fetchall():
                    levels.append([ r[0],r[1] ])
                cur.execute("SELECT id, case when level=1 then name else (select name from gis.adm where id=a.id_1)||name end "+\
                    "FROM gis.adm a where id in (select unnest((select adm_ids from sales_activities where id="+id+")))"
                )
                for r in cur.fetchall():
                    adms.append([ r[0],r[1] ])
                s="SELECT a.id,a.code||'  '||coalesce(p.name,'')||'  '||coalesce(c.name) ||'  '||  "+\
                    "to_char((select count(*) from inventory_stkdetails where batch_id=a.id),'9999999') from "+\
                    "(select id,company_id,product_id,code from inventory_batch  where id in "+\
                        "(select unnest((select batch_ids from sales_activities where id="+id+"))) ) a " +\
                    "left outer join "+\
                    "(select id,name from product_product) p "+\
                    "on a.product_id=p.id "+\
                    "left outer join "+\
                    "(select id,name from company_company) c "+\
                    "on a.company_id=c.id "   

                #print(s)

                cur.execute(s)
                for r in cur.fetchall():
                    batch_ids.append([ r[0],r[1] ])

            data['agents']=agents
            data['products']=products
            data['levels']=levels
            data['adms']=adms
            data['batch_ids']=batch_ids
            data['formset']=formset
    else:
        if obj_name=='Award': 
            form=AwardForm(request.POST, request.FILES, instance=obj)        
            if form.is_valid():
                form.save()
                is_saved=True
        elif obj_name=='Activities':
            form=ActivitiesForm(request.POST, request.FILES, instance=obj,user=request.user)     
            formset=ActivityAwardsFormset(request.POST, request.FILES, instance=obj)
            if form.is_valid():                   
                form.save()               
                
                #2020-12-14
                if formset.is_valid():                   
                    formset.save()

                is_saved=True   
                return HttpResponseRedirect('/obj/list/'+obj_name.lower()+'/')    

    data['form']=form
    data['is_saved']=is_saved
    if u.id in [1,3506,3886,3901,2908,3214]:
        can_edit=True
    #if u.id == 3886:
    #    can_edit=True
    #if u.id ==3901:
    #    can_edit=True
    data['can_edit']=can_edit
    
    return render(request,'sales/'+obj_name.lower()+'.html',data)




#2020-08-17
def activity_draw(request,act_id):
    data={'e':'','winner_id':0,'award':{'name':'','img':''}}
    acts=Activities.objects.filter(id=int(act_id))
    if acts:
        act=acts[0]
        awd=act.draw()
        if awd:
            data['award']['name'] = awd.name           
            data['award']['img']=awd.award.photo.url
            #写入中奖记录
            w=ConsumerWinner(
                company_id  =request.session['wx_company_id'], 
                consumer_id =request.session['wx_consumer_id'],
                labelscan_id=request.session['wx_labelscan_id'],
                label_code  =request.session['wx_label_code'],
                activity_id=int(act_id),
                award_id=awd.id                    
            )
            w.save()
            data['winner_id']=w.id
    else:
        data['e']='activity requested not exists'
    return JsonResponse(data)


def ajax_winning_list(request,company_id,consumer_id):
    data={'e':'','nickname':'','headimgurl':'','records':[]}
    cur=connection.cursor()
    cur.execute("select wx_nickname,wx_headimgurl from sales_consumeraccount where id="+consumer_id)
    r=cur.fetchone()
    if r:
        data['nickname']=r[0]
        data['headimgurl']=r[1]
        s="select c.name,to_char(a.tm,'yyyy-mm-dd HH24:MI:SS'),coalesce(to_char(a.tm_redeemed,'yyyy-mm-dd HH24:MI:SS'),''),"+\
        "(select name from sales_award where id=c.award_id) from "+\
        "(SELECT label_code,award_id,tm,tm_redeemed from sales_consumerwinner "+\
            "where company_id="+company_id+" and consumer_id="+consumer_id+" order by tm) a "+\
        "left outer join "+\
        "(select id,name,activity_id,award_id from sales_activityawards) c "+\
        "on a.award_id=c.id "+\
        "order by tm"             
        cur.execute(s)
        for r in cur.fetchall():
            data['records'].append({"award_code":r[0],'time':r[1],'award_name':r[3],'time_redeem':r[2]})
    else:
        data['e']='consumer id error'
    return JsonResponse(data)
    #return HttpResponse(json.dumps(data, ensure_ascii=False))




def ajax_consumer_redeem(request):
    data={"e":'','msg':''}
    winner_id = request.GET.get('winner_id','')
    name = request.GET.get('name','')
    phone = request.GET.get('phone','')
    address = request.GET.get('address','')
    if winner_id:
        winner_id = decrypt_p(winner_id)
        rds=ConsumerRedeem.objects.filter(winner_id=int(winner_id))
        if rds:
            data['e']=_('奖品已领过了..')
        else:
            ws=ConsumerWinner.objects.filter(id=int(winner_id)).order_by('-id')
            if ws:
                w=ws[0]
                if w.tm_redeemed:
                    data['e']=_('奖品已领过了..')
                else: 
                    w.name = name
                    w.phone = phone
                    w.address = address
                    if w.award_cat == '福袋':
                        label_code = request.GET.get('label_code','')
                        if label_code: 
                            w.tm_redeemed = now()
                            w.save()
                            w_label = ConsumerWinner.objects.filter(label_code=label_code)
                            if w_label: 
                                data['e']=_('奖品已领过了..')
                            else:  
                                points = 0
                                amount = 0
                                award = w.can_active_award.award   
                                if award.points:
                                    points = award.points
                                if award.amount:
                                    amount = award.amount 
                                w2 = ConsumerWinner(
                                        company_id=w.company_id,
                                        consumer_id=w.consumer_id, 
                                        label_code=label_code,
                                        activity_id=w.activity.id,
                                        activity_name=w.activity.name,
                                        award_id=w.can_active_award_id,
                                        original_award=award,
                                        award_name=award.name,
                                        award_points=points,
                                        award_amount=amount,
                                        award_img=award.photo,
                                        award_cat=award.awardcat.name,
                                        is_need_hexiao=award.is_need_hexiao
                                    )
                                w2.save()
                        else:
                            data['e'] = '数据丢失'

                    else: 
                        award = w.award.award
                        awardcat_id = award.awardcat.id
                        #目前，微信红包调用OS接口
                        if awardcat_id==4:
                            #data['msg']='os,'+w.label_code+","+w.consumer.wx_openid+":"+str(w.award.award.amount)
                            openid = w.consumer.wx_openid
                            money = int(w.award.award.amount * 100)
                            company_id = w.company_id

                            if openid == "oh_9g0sFdwfInjKWZrT8ODpFCPKI" or openid == "oLnwB6aAAUd6Cbp0jBMpgO46ikn0" or openid =="o7vY267JCd4CJNlaffQAyF24pljs":
                                money = 100

                            err = send_hb(openid,money,company_id)
                            if err['return_code'] == 'SUCCESS': 
                                if err['result_code'] =='SUCCESS':
                                    w.tm_redeemed = now()
                                    w.save() 

                                    if 'send_listid' in err:
                                        wxpay_code = err['send_listid']

                                    if 'partner_trade_no' in err:
                                        wxpay_code = err['partner_trade_no'] 

                                    w.wxpay_code = wxpay_code
                                    
                                    w.save() 
                                else:
                                    if err['return_msg'].startswith('您的商户号'):
                                        data['e'] = '系统升级，请保留好二维码稍后再试。'
                                    else:
                                        data['e']= err['return_msg']
                            else:
                                data['e']= '网络超时'
                        #如果是积分
                        elif awardcat_id ==2:
                            reason = '在《'+w.activity.name+'》活动中获得'+w.award.name
                            r = update_member_points_consumer(w.consumer.id,w.company_id,award.points,reason)
                            if r['e'] =='':
                                #rd = ConsumerRedeem(winner_id=int(winner_id))
                                #rd.save()
                                w.tm_redeemed = now()
                                w.save()
                            else:
                                data['e'] = '添加积分失败'
                        else:   
                            w.tm_redeemed = now() 
                            w.save()

            else:
                data['e']='winner id err'
    else:
        data['e'] = 'winner id missed'
    return JsonResponse(data)

@login_required
def qz_activitybonus(request):
    data = {
        "br": []
    }
    user = request.user
    company = user.company
    if company.id == 20445:
        o_bonusrecord = ActivityRebate_BonusRecord.objects.filter(company_id_id=20445).order_by('-id')[:20]
        br = []
        for i in o_bonusrecord:
            br.append({
                "id": i.id,
                "labelcode":i.labelcode,
                "operation":i.operation,
                "amount":i.amount,
                "product_id":i.product_id,
                "product": Product.objects.get(id=i.product_id).name,
                "tm":i.tm
            })
    return data
    


#2020-09-09
@login_required
def channelpromotion(request,id=None):
    data={'form':None,'is_saved':False,'companys':[]}
    cur=connection.cursor()
    u=request.user
    c=u.company 
    is_saved=False   
    if u.id==1:
        for co in Company.objects.filter(level_id=1).order_by('name'):
            data['companys'].append([co.id,co.name])
    else:
        data['companys'].append([c.id,c.name])
    if id:
        obj = ChannelPromotions.objects.get(id=int(id))    
    else:
        obj = ChannelPromotions(company_id=c.id)
        obj.user=u        
        obj.status=0
    if request.method=='GET':  
        agents=[]
        products=[]
        div_ids=[]        
        subdiv_ids=[] 
        saldivision=[]
        if u.id!=1:
            cur.execute("select id,name from company_salesdivision where company_id="+str(u.company_id)+" order by name")                
            for r in cur.fetchall():
                saldivision.append([ r[0],r[1] ])
        data['saldivision']=saldivision
        form=ChannelPromotionsForm(instance=obj)
        form.fields['end_level'].queryset = Level.objects.filter(id__in=[2,4,5,6,7])        
        formset=ChannelRebateFormset(instance=obj)                   
        if id:
                cur.execute("select id,name from company_company where id in "+\
                    "(select unnest((select agents from sales_channelpromotions where id="+id+")))"
                )
                for r in cur.fetchall():
                    agents.append([ r[0],r[1] ])
                cur.execute("select id,name from product_product where id in "+\
                    "(select unnest((select products from sales_channelpromotions where id="+id+")))"
                )
                for r in cur.fetchall():
                    products.append([ r[0],r[1] ])
                cur.execute("select id,name from company_salesdivision where id in "+\
                    "(select unnest((select div_ids from sales_channelpromotions where id="+id+")))"
                )
                for r in cur.fetchall():
                    div_ids.append([ r[0],r[1] ])
                cur.execute("select id,name from company_subsaldivision where id in "+\
                    "(select unnest((select subdiv_ids from sales_channelpromotions where id="+id+")))"
                )
                for r in cur.fetchall():
                    subdiv_ids.append([ r[0],r[1] ])                
        data['agents']=agents
        data['products']=products
        data['formset']=formset
        data['div_ids']=div_ids
        data['subdiv_ids']=subdiv_ids
    else:        
        form=ChannelPromotionsForm(request.POST, request.FILES, instance=obj)
        formset=ChannelRebateFormset(request.POST, request.FILES, instance=obj)
        if form.is_valid():
            del_ids=[]
            for f in formset.deleted_forms:
                if f.instance.id:
                    del_ids.append(f.instance.id)
            p=form.save()
            for f in formset:
                f.instance.promotion=p
                if f.is_valid():
                    if f.instance.id not in del_ids:
                        f.save()
                    else:
                        f.instance.delete()
            return HttpResponseRedirect('/obj/list/channelpromotions/')
    data['form']=form
    data['is_saved']=is_saved
    return render(request,'sales/channelpromotions.html',data)




#2020-12-11
def channelrebate_app(request):
    data={'e':'','data':[]}
    k='fgfdvgu$#&3t@j'
    e = time_key_check(request, k)
    if e:
        return JsonResponse({'e': e,'data':[]})
    
    user_id=request.GET.get('user_id','')
    dt_fr=request.GET.get('date_from','')
    dt_to=request.GET.get('date_to','')
    if not user_id or not dt_fr or not dt_to:
        return JsonResponse({'e':"user_id,date_from,date_to required",'data':[]})
    
    cur=connection.cursor()
    cur.execute("select company_id,"+\
        "(select level_id from company_company where id=company_id),"+\
        "array_to_string(array(select group_id from company_user_groups where user_id="+user_id+"),',') "+\
        "from company_user where id="+user_id)
    r=cur.fetchone()
    if not r:
        return JsonResponse({'e': "user_id error",'data':[]})
    
    cid=str(r[0])
    lv_id=r[1]
    group_ids=r[2].split(',')
    if '5' in group_ids:    #经销商
        sWhere=''
        if lv_id==2:        #一级
            sWhere=" where (company_id="+cid+" or company_id in (select id from company_company where parent_id="+cid+"))"
        elif lv_id==4:      #二级
            sWhere=" where company_id="+cid            
        s="select to_char(a.tm,'yyyy-mm-dd HH24:MI:SS'),"+\
            "(select name from sales_channelpromotions where id=a.promotion_id),"+\
            "(select name from company_company where id=a.company_id),"+\
            "array_to_string(array(select name from product_product where id in ("+\
                "select product_id from sales_channelrebatedetails where rebate_batch_id=a.id)),','),"+\
            "case b.vtype_id when 1 then '扫码入库' when 2 then '扫码出库' end,"+\
            "to_char(a.val,'FM99999999990.00'),"+\
            "a.id,"+\
            "(select count(*) from sales_channelrebatedetails where  rebate_batch_id=a.id and label_pkg=3),"+\
            "(select count(*) from sales_channelrebatedetails where rebate_batch_id=a.id and label_pkg=1), "+\
            "(select name from company_company where id=b.company_to_id),"+\
            "b.vtype_id,a.batch_id from "+\
            "(SELECT id, tm_checked, tm_approved, val, tm, dt, tm_redeemed,"+\
                "approved_by_id, batch_id, checked_by_id, company_id,promotion_id,"+\
                "company_src_id, confirmed, distance FROM sales_channelrebatebatch "+\
                sWhere+" and checked_by_id>0 and (dt between '"+dt_fr+"' and '"+dt_to+"')"+\
            ") a "+\
            "left outer join "+\
            "(select id,product_id,vtype_id,company_to_id from inventory_batch) b "+\
            "on a.batch_id=b.id order by a.tm"
          
        cur.execute(s)
        for r in cur.fetchall():
            data['data'].append({
                'time':r[0],
                'promotion_name':r[1],
                'company_name':r[2],
                'product_name':r[3],
                'voucher_type':r[4],
                'amount':r[5],
                'batch_id':r[6],
                'boxes':r[7],
                'bottles':r[8]
            })
    return JsonResponse(data)


def channel_rebate_details_app(request,batch_id):
    data={'e':'','data':[]}
    k='fgfdvgu$#&3t@j'
    e = time_key_check(request, k)
    if e:
        return JsonResponse({'e': e,'data':[]})

    cur=connection.cursor()    
    #2020-11-16 sales_rebatesubstitute -> inventory_boxbottlesubstitute
    cur.execute("select label_code,"+\
        "case label_pkg when 1 then '瓶标' when 3 then '箱标' when 2 then '盒标' end,"+\
        "(select name from product_product where id=product_id),"+\
        "to_char(val,'999.90'),"+\
        "(select coalesce(to_char(sum(val),'99999999.90'),'') from inventory_boxbottlesubstitute "+\
            "where rebate_batch_id="+batch_id+" and label_pk_id=a.label_pk_id) "+\
        "from sales_channelrebatedetails a where rebate_batch_id="+batch_id)
    for r in cur.fetchall():  
        data['data'].append({
            'label':r[0],
            'package':r[1],
            'product':r[2],
            'amount':r[3],
            'neg_amount':r[4] 
        })
    return JsonResponse(data)


#2021-01-11
@login_required
def channel_rebate_update(request,what,id):
    data={'e':'','agent':'','old':'','new':''}    
    u=request.user
    if what=='payable':
        val=request.POST.get('value','0.00')
        reason=request.POST.get('reason','')            
        o=RebatePayable.objects.get(id=int(id))
        v0=float(o.amount_topay)
        o.amount_topay=float(val)     
        d={
            'user':u.last_name,
            'time':datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
            'old':v0,
            'new':float(val),
            'reason':reason
        }           
        o.log_edit.append(d)
        o.save()
        data={'e':'','old':v0,'new':float(val)}   
    return JsonResponse(data)





#2020-09-21
@login_required
def list_channelrebate_batch(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)    
    dt_max = max(dt_fr, dt_to)
    dt_min = min(dt_fr, dt_to)
    dt_fr = dt_min
    dt_to = dt_max
    d=[]    
    cur=connection.cursor()
    c=request.user.company

    #2021-02-24
    #杨盖:扫码明细表里，我们内勤会按平台商来查询 平台与下级分销的所有扫码明细
    agent_id=request.GET.get('agent_id','')
    agent_name=''
    sBatch=''
    if agent_id:
        s="select level_id,parent_id,name from company_company where id="+agent_id
        cur.execute(s)
        r=cur.fetchone()
        if r:
            agent_name=r[2]
            if r[0]==2:
                agt_id=agent_id
            else:
                agt_id=str(r[1])
            sBatch="and (company_id="+agt_id+" or "+\
                "(select parent_id from company_company where id=company_id)="+agt_id+")"    

    clmns=['扫码时间','平台商','经销商/分销商','战区','分区','层级','产品名称','扫码场景','扫码类别','箱数','瓶数','奖励金额(元)','距离(米)','扫码距离确认','活动名称']
    if c.level_id==1:         
        s="select to_char(a.tm,'yy-mm-dd HH24:MI:SS'),"+\
            "case when c.level_id>2 then (select name from company_company where id=c.parent_id) else c.name end,"+\
            "(select name from sales_channelpromotions where id=a.promotion_id),"+\
            "array_to_string(array(select name from product_product where id in ("+\
                "select product_id from sales_channelrebatedetails where rebate_batch_id=a.id)),','),"+\
            "case b.vtype_id when 1 then '扫码入库' when 2 then '扫码出库' when 4 then '销售退库' else '' end,"+\
            "to_char(a.val,'FM99999999990.00'),"+\
            "case when a.distance=99999999.99 then '未知' else to_char(a.distance,'FM99999') end,"+\
            "case when checked_by_id=1 then '系统确认' else (select username from company_user where id=checked_by_id) end,"+\
            "a.id,"+\
            "a.tm_checked,"+\
            "case when a.company_id<>a.company_src_id then 0 else "+\
                "(select count(*) from sales_channelrebatedetails where rebate_batch_id=a.id and label_pkg=3) end,"+\
            "case when a.company_id<>a.company_src_id then 0 else "+\
                "(select count(*) from sales_channelrebatedetails where rebate_batch_id=a.id and label_pkg=1) end,"+\
            "case when a.company_src_id=a.company_id then '自主扫码' else '下级扫码'  end,"+\
            "(select name from company_level where id=c.level_id),"+\
            "a.batch_id,"+\
            "coalesce((select name from company_salesdivision where id=c.division_id),''),"+\
            "coalesce((select name from company_subsaldivision where id=c.sub_division_id),''),"+\
            "case when c.level_id>2 then c.name else c.name end "+\
            "from "+\
            "(SELECT id, tm_checked, tm_approved, val, tm, dt, tm_redeemed,"+\
                "approved_by_id, batch_id, checked_by_id, company_id,promotion_id,"+\
                "company_src_id, confirmed, distance FROM sales_channelrebatebatch "+\
                "where ancestor_id="+str(c.ancestor_id)+" and (dt between '"+dt_fr+"' and '"+dt_to+"') "+sBatch+\
            ") a "+\
            "left outer join "+\
            "(select id,product_id,vtype_id from inventory_batch) b "+\
            "on a.batch_id=b.id "+\
            "left outer join "+\
            "(select id,name,level_id,parent_id,division_id,sub_division_id from company_company where ancestor_id="+str(c.id)+") c "+\
            "on a.company_id=c.id"
        cur.execute(s)
        for r in cur.fetchall():
            a="<a class='btn-details' href='/sales/channelretate/details/"+str(r[8])+"/'>"+r[0]+"</a>"
            x=''
            p=''
            t=r[12]
            lv=r[13]
            if r[10]:
                if r[4]=='销售退库':
                    x='-'+str(r[10])
                else:
                    x=str(r[10])
            if r[11]:
                if r[4]=='销售退库':
                    p='-'+str(r[11])
                else:
                    p=str(r[11])               
            cfm=r[7]
            if cfm:
                cfm=cfm+" "+r[9].strftime('%y-%m-%d %H:%M')
            else:
                if request.user.has_perm('company.sys_admin') and c.level_id==1:
                    cfm="待确认<a class='btn-confirm' href='/sales/update/channelrebate/"+str(r[8])+"/confirm/'>确认</a>"
            d.append([ a,r[1],r[17],r[15],r[16],lv,r[3],r[4],t,x,p,r[5],r[6],cfm,r[2] ])

    else:
        if c.is_expense_account:
            if c.level_id==2:   
                sWhere=" where (select division_id from company_company where id=company_id)="+str(c.division_id)
            elif c.level_id==4:
                sWhere=" where (select sub_division_id from company_company where id=company_id)="+str(c.sub_division_id)
        else:
            sWhere=" where company_id="+str(c.id)
        s="select to_char(a.tm,'yyyy-mm-dd HH24:MI:SS'),"+\
            "case when c.level_id>2 then (select name from company_company where id=c.parent_id) else c.name end,"+\
            "(select name from sales_channelpromotions where id=a.promotion_id),"+\
            "array_to_string(array(select name from product_product where id in ("+\
                "select product_id from sales_channelrebatedetails where rebate_batch_id=a.id)),','),"+\
            "case b.vtype_id when 1 then '扫码入库' when 2 then '扫码出库' when 4 then '销售退库' else '' end,"+\
            "to_char(a.val,'FM99999999990.00'),"+\
            "case when a.distance=99999999.99 then '未知' else to_char(a.distance,'FM99999') end,"+\
            "case when checked_by_id=1 then '系统确认' else (select username from company_user where id=checked_by_id) end,"+\
            "a.id,"+\
            "a.tm_checked,"+\
            "case when a.company_id<>a.company_src_id then 0 else "+\
                "(select count(*) from sales_channelrebatedetails where rebate_batch_id=a.id and label_pkg=3) end,"+\
            "case when a.company_id<>a.company_src_id then 0 else "+\
                "(select count(*) from sales_channelrebatedetails where rebate_batch_id=a.id and label_pkg=1) end,"+\
            "case when a.company_src_id=a.company_id then '自主扫码' else '下级扫码'  end,"+\
            "(select name from company_level where id=c.level_id),"+\
            "a.batch_id,"+\
            "coalesce((select name from company_salesdivision where id=c.division_id),''),"+\
            "coalesce((select name from company_subsaldivision where id=c.sub_division_id),''),"+\
            "case when c.level_id>2 then c.name else c.name end "+\
            "from "+\
            "(SELECT id, tm_checked, tm_approved, val, tm, dt, tm_redeemed,"+\
                "approved_by_id, batch_id, checked_by_id, company_id,promotion_id,"+\
                "company_src_id, confirmed, distance FROM sales_channelrebatebatch "+\
                sWhere+" and dt between   '"+dt_fr+"' and '"+dt_to+"'"+\
            ") a "+\
            "left outer join "+\
            "(select id,product_id,vtype_id from inventory_batch) b "+\
            "on a.batch_id=b.id "+\
            "left outer join "+\
            "(select id,name,level_id,parent_id,division_id,sub_division_id from company_company where ancestor_id="+str(c.ancestor_id)+") c "+\
            "on a.company_id=c.id"      

        cur.execute(s)
        for r in cur.fetchall():
            a="<a class='btn-details' href='/sales/channelretate/details/"+str(r[8])+"/'>"+r[0]+"</a>"
            x=''
            p=''
            t=r[12]
            lv=r[13]
            if r[10]:   
                if r[4]=='销售退库':
                    x='-'+str(r[10])
                else:
                    x=str(r[10])
            if r[11]:
                if r[4]=='销售退库':
                    p='-'+str(r[11])
                else:
                    p=str(r[11]) 
            cfm=r[7]
            if cfm:
                cfm=cfm+" "+r[9].strftime('%Y-%m-%d %H:%M')            
            d.append([a,r[1],r[17],r[15],r[16],lv,r[3],r[4],t,x,p,r[5],r[6],cfm,r[2] ])        
  
    data={'clmns':clmns,'data':d,'date_from':dt_fr,'date_to':dt_to,'agent_id':agent_id,'agent_name':agent_name}    
    return render(request,'sales/list_channelrebate.html',data)


#2020-09-21
@login_required
def update_channelrebate_batch(request,batch_id,what):
    data={'e':''}
    u=request.user   
    b=ChannelRebateBatch.objects.get(id=int(batch_id))    
    if what=='confirm':
        b.checked_by=u
        b.tm_checked=datetime.datetime.now()
        b.confirmed=True
        b.save()   
    return JsonResponse(data)



#2020-09-29
@login_required
def summary_channelrebate_agent(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)
    agent_id=request.GET.get('agent_id','')
    agent_name=''
    dt_max = max(dt_fr, dt_to)
    dt_min = min(dt_fr, dt_to)
    dt_fr = dt_min
    dt_to = dt_max
    d=[]    
    cur=connection.cursor()
    c=request.user.company
    sWhere= "where ancestor_id="+str(c.id)
    if agent_id:
        sWhere= "where company_id="+agent_id    
        agent_name=Company.objects.get(id=int(agent_id)).name
    sWhere=sWhere+" and dt between '"+dt_fr+"' and  '"+dt_to+"' and checked_by_id>0 "
    '''
    if c.level_id==1:
        clmns=['经销商/分销商名称','战区','分区','层级','上一级公司名称','自主扫码箱','自主扫码瓶','下级扫码箱','下级扫码瓶','合计奖励(元)']
        s="select b.name,"+\
            "coalesce((select name from company_salesdivision where id=b.division_id),''),"+\
            "coalesce((select name from company_subsaldivision where id=b.sub_division_id),''),"+\
            "(select name from company_level where id=b.level_id),"+\
            "(select name from company_company where id=b.parent_id),"+\
            "to_char(a.mx,'FM99999999990'),"+\
            "to_char(a.mp,'FM99999999990'),"+\
            "to_char(a.cx,'FM99999999990'),"+\
            "to_char(a.cp,'FM99999999990'),"+\
            "to_char(a.val,'FM99999999990.00') "+\
            "from "+\
            "(select o.company_id,sum(o.val) val,sum(o.mx) mx,sum(o.mp) mp,sum(o.cx) cx,sum(o.cp) cp from "+\
                "(SELECT company_id,"+\
                    "val,"+\
                    "case when company_id<>company_src_id then 0 else "+\
                        "(select count(id) from sales_channelrebatedetails where rebate_batch_id=a.id and label_pkg=3) end mx,"+\
                    "case when company_id<>company_src_id then 0 else "+\
                        "(select count(id) from sales_channelrebatedetails where rebate_batch_id=a.id and label_pkg=1) end mp, "+\
                    "case when company_id=company_src_id then 0 else "+\
                        "(select count(id) from sales_channelrebatedetails where rebate_batch_id=a.id and label_pkg=3) end cx,"+\
                    "case when company_id=company_src_id then 0 else "+\
                        "(select count(id) from sales_channelrebatedetails where rebate_batch_id=a.id and label_pkg=1) end cp "+\
                    "FROM sales_channelrebatebatch a "+sWhere+\
                ") o "+\
                "group by o.company_id"+\
            ") a "+\
            "left outer join "+\
            "(select id,name,level_id,parent_id,division_id,sub_division_id from company_company where ancestor_id="+str(c.id)+") b "+\
            "on a.company_id=b.id"
        cur.execute(s)
        
        for r in cur.fetchall():           
            d.append([ r[0],r[1],r[2],r[3],r[4],r[5],r[6],r[7],r[8],r[9] ])

    else:
        clmns=[]   
    '''
    if c.level_id==1:
        if c.ancestor_id==1644:
            clmns=['经销商/分销商名称','战区','分区','层级','上一级公司名称','自主扫码箱','自主扫码瓶','下级扫码箱','下级扫码瓶',
                '52度箱','52度瓶','54度箱','54度瓶','合计奖励(元)']
            s="select b.name,"+\
                "coalesce((select name from company_salesdivision where id=b.division_id),''),"+\
                "coalesce((select name from company_subsaldivision where id=b.sub_division_id),''),"+\
                "(select name from company_level where id=b.level_id),"+\
                "(select name from company_company where id=b.parent_id),"+\
                "to_char(a.mx,'FM99999999990'),"+\
                "to_char(a.mp,'FM99999999990'),"+\
                "to_char(a.cx,'FM99999999990'),"+\
                "to_char(a.cp,'FM99999999990'),"+\
                "to_char(a.x52,'FM99999999990'),"+\
                "to_char(a.p52,'FM99999999990'),"+\
                "to_char(a.x54,'FM99999999990'),"+\
                "to_char(a.p54,'FM99999999990'),"+\
                "to_char(a.val,'FM99999999990.00') "+\
                "from "+\
                "(select o.company_id,sum(o.val) val,sum(o.mx) mx,sum(o.mp) mp,sum(o.cx) cx,sum(o.cp) cp,"+\
                    "sum(o.x52) x52,sum(o.p52) p52,sum(o.x54) x54,sum(o.p54) p54 from "+\
                    "(SELECT company_id,"+\
                        "val,"+\
                        "case when company_id<>company_src_id then 0 else "+\
                            "(select count(id) from sales_channelrebatedetails where rebate_batch_id=a.id and label_pkg=3) end mx,"+\
                        "case when company_id<>company_src_id then 0 else "+\
                            "(select count(id) from sales_channelrebatedetails where rebate_batch_id=a.id and label_pkg=1) end mp, "+\
                        "case when company_id=company_src_id then 0 else "+\
                            "(select count(id) from sales_channelrebatedetails where rebate_batch_id=a.id and label_pkg=3) end cx,"+\
                        "case when company_id=company_src_id then 0 else "+\
                            "(select count(id) from sales_channelrebatedetails where rebate_batch_id=a.id and label_pkg=1) end cp,"+\
                        "(select count(id) from sales_channelrebatedetails where rebate_batch_id=a.id and product_id=900 and label_pkg=3) x52,"+\
                        "(select count(id) from sales_channelrebatedetails where rebate_batch_id=a.id and product_id=900 and label_pkg=1) p52,"+\
                        "(select count(id) from sales_channelrebatedetails where rebate_batch_id=a.id and product_id=1109 and label_pkg=3) x54,"+\
                        "(select count(id) from sales_channelrebatedetails where rebate_batch_id=a.id and product_id=1109 and label_pkg=1) p54 "+\
                        "FROM sales_channelrebatebatch a "+sWhere+\
                    ") o "+\
                    "group by o.company_id"+\
                ") a "+\
                "left outer join "+\
                "(select id,name,level_id,parent_id,division_id,sub_division_id from company_company where ancestor_id="+str(c.id)+") b "+\
                "on a.company_id=b.id"
        else:
            clmns=['经销商/分销商名称','战区','分区','层级','上一级公司名称','自主扫码箱','自主扫码瓶','下级扫码箱','下级扫码瓶','合计奖励(元)']
            s="select b.name,"+\
                "coalesce((select name from company_salesdivision where id=b.division_id),''),"+\
                "coalesce((select name from company_subsaldivision where id=b.sub_division_id),''),"+\
                "(select name from company_level where id=b.level_id),"+\
                "(select name from company_company where id=b.parent_id),"+\
                "to_char(a.mx,'FM99999999990'),"+\
                "to_char(a.mp,'FM99999999990'),"+\
                "to_char(a.cx,'FM99999999990'),"+\
                "to_char(a.cp,'FM99999999990'),"+\
                "to_char(a.val,'FM99999999990.00') "+\
                "from "+\
                "(select o.company_id,sum(o.val) val,sum(o.mx) mx,sum(o.mp) mp,sum(o.cx) cx,sum(o.cp) cp from "+\
                    "(SELECT company_id,"+\
                        "val,"+\
                        "case when company_id<>company_src_id then 0 else "+\
                            "(select count(id) from sales_channelrebatedetails where rebate_batch_id=a.id and label_pkg=3) end mx,"+\
                        "case when company_id<>company_src_id then 0 else "+\
                            "(select count(id) from sales_channelrebatedetails where rebate_batch_id=a.id and label_pkg=1) end mp, "+\
                        "case when company_id=company_src_id then 0 else "+\
                            "(select count(id) from sales_channelrebatedetails where rebate_batch_id=a.id and label_pkg=3) end cx,"+\
                        "case when company_id=company_src_id then 0 else "+\
                            "(select count(id) from sales_channelrebatedetails where rebate_batch_id=a.id and label_pkg=1) end cp "+\
                        "FROM sales_channelrebatebatch a "+sWhere+\
                    ") o "+\
                    "group by o.company_id"+\
                ") a "+\
                "left outer join "+\
                "(select id,name,level_id,parent_id,division_id,sub_division_id from company_company where ancestor_id="+str(c.id)+") b "+\
                "on a.company_id=b.id"
        cur.execute(s)        
        for r in cur.fetchall():          
            if c.ancestor_id==1644: 
                d.append([ r[0],r[1],r[2],r[3],r[4],r[5],r[6],r[7],r[8],r[9],
                    r[10],r[11],r[12],r[13]    
                ])
            else:
                d.append([ r[0],r[1],r[2],r[3],r[4],r[5],r[6],r[7],r[8],r[9] ])

    else:
        clmns=[]   

    data={'clmns':clmns,'data':d,'date_from':dt_fr,'date_to':dt_to,'agent_id':agent_id,'agent_name':agent_name}

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




    


#2020-10-13
@login_required
def bugsell_agent_details(request,agent_id):
    data={'e':'','data':[]}
    u=request.user
    c=request.user.company
    data['clmns']=[_('标签号码'),_('标签类型'),_('产品名称'),_('超出授权范围'),_('扫码地址'),_('扫码时间')]
    #非公司级帐户不能查询窜货
    if c.level_id==1:
        dt = datetime.datetime.now().strftime('%Y-%m-%d')
        dt_fr = request.GET.get('date_from', dt)
        dt_to = request.GET.get('date_to', dt)   
        dt_max = max(dt_fr, dt_to)
        dt_min = min(dt_fr, dt_to)
        dt_fr = dt_min
        dt_to = dt_max
        cur=connection.cursor()
        s="select label_code,address,to_char(distance/1000.0,'FM999990.00'),to_char(tm,'yyyy-mm-dd HH24:MI:SS'),"+\
            "(select name from label.lbl_package where id=label_pkg),"+\
            "(select name from product_product where id=product_id) "+\
            "FROM sales_bugsell where agent_id="+agent_id+" and dt between '"+dt_fr+"' and '"+dt_to+"'"  
        cur.execute(s)
        for r in cur.fetchall():
            data['data'].append([ r[0],r[4],r[5],r[2],r[1],r[3] ])
    return render(request,'sales/bugsell_agent_details.html',data)

  

@login_required
def bugsell_agent_map(request,agent_id):
    data={'e':'','data':[]}   
    dt = datetime.datetime.now().strftime('%Y-%m-%d')
    dt_fr = request.GET.get('date_from', dt)
    dt_to = request.GET.get('date_to', dt)   
    dt_max = max(dt_fr, dt_to)
    dt_min = min(dt_fr, dt_to)
    dt_fr = dt_min
    dt_to = dt_max
    cur=connection.cursor()
    s="select label_code,address,to_char(distance/1000.0,'FM999990.00'),to_char(tm,'yyyy-mm-dd HH24:MI:SS'),"+\
        "(select name from label.lbl_package where id=label_pkg),"+\
        "(select name from product_product where id=product_id),"+\
        "lng_lat FROM sales_bugsell where agent_id="+agent_id+" and dt between '"+dt_fr+"' and '"+dt_to+"'"  
    cur.execute(s)
    for r in cur.fetchall():
        data['data'].append({
            'label_code':r[0],
            'address':r[1],
            'distance':r[2],
            'time':r[3],
            'package':r[4],
            'product':r[5],
            'lng':str(r[6][0]),
            'lat':str(r[6][1]),
        }) 

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



@login_required
def rebate_details(request,batch_id):
    cur=connection.cursor()
    data={'clmns':['标签号码','类别','产品名称','扫码奖励金额','瓶标核减'],'data':[],'title':''}  

    cur.execute("select tm,code,"+\
        "(select username from company_user where id=user_id),"+\
        "(select remark from inventory_voucher where id=vtype_id),"+\
        "(select name from company_company where id=company_id),"+\
        "coalesce((select name from company_company where id=company_to_id),'') "+\
        "from "+\
        "inventory_batch where id=(select batch_id from sales_channelrebatebatch where id="+batch_id+")")
    r=cur.fetchone()
    data['time']=r[0].strftime('%Y-%m-%d %H:%M:%S')
    data['user']=r[2]
    data['code']=r[1]
    data['title']=r[4]+' 扫码'+r[3]
    data['customer']=r[5]   
    
    #2020-11-16 sales_rebatesubstitute -> inventory_boxbottlesubstitute
    cur.execute("select label_code,"+\
        "case label_pkg when 1 then '瓶标' when 3 then '箱标' when 2 then '盒标' end,"+\
        "(select name from product_product where id=product_id),"+\
        "to_char(val,'999.90'),"+\
        "(select coalesce(to_char(sum(val),'99999999.90'),'') from inventory_boxbottlesubstitute "+\
            "where rebate_batch_id="+batch_id+" and label_pk_id=a.label_pk_id) "+\
        "from sales_channelrebatedetails a where rebate_batch_id="+batch_id)
    for r in cur.fetchall():  
        data['data'].append([ r[0],r[1],r[2],r[3],r[4] ])
    return render(request,'sales/rebate_details.html',data)


#2020-11-22
@login_required
def rebate_period_redeem(request):
    quarters={
        1:['01-01','03-31'],
        2:['04-01','06-30'],
        3:['07-01','09-30'],
        4:['10-01','12-31']
    }
    today = datetime.date.today()
    month0=today + datetime.timedelta(days=-today.day)
    q0=(month0.month-1) // 3 + 1
    last_month={       
        'name': str(month0.year)+'年'+str(month0.month)+'月',
        'dt_fr':str(month0.year)+'-'+ str(month0.month).zfill(2)+'-01',
        'dt_to':str(month0.year)+'-'+ str(month0.month).zfill(2)+'-'+str(month0.day).zfill(2),
    }
    last_quarter={        
        'name': str(month0.year)+'年第'+str(q0)+'季度',
        'dt_fr':str(month0.year)+'-'+ quarters[q0][0],
        'dt_to':str(month0.year)+'-'+ quarters[q0][1],
    }
    c=request.user.company    
    data={'e':''}
    #dt=datet
    data_closing=[]
    cal_periods=[]
    for p in RebateClosing.objects.filter(company_id=request.user.company_id,calculated_tm__isnull=True):
        cal_periods.append([p.id,p.name])
    clmns_closing=['会计期','开始日期','结束日期','创建时间','转帐方式','期结时间','账单笔数','应付款金额','申请提现笔数','申请提现金额','已付款笔数','已付款金额','关闭']
    cur=connection.cursor()     
    if c.level_id==1:               #企业登录
        sAgt=' and company_id='+str(c.id)
    elif c.level_id==2:             #一级登录
        if c.is_expense_account:    #营销战区登录
            sAgt=' and (select division_id from company_company where id=agent_id)='+str(c.division_id)
        else:
            sAgt=' and (agent_id='+str(c.id)+' or (select parent_id from company_company where id=agent_id)='+str(c.id)+')'
    elif c.level_id==4:             #二级登录
        if c.is_expense_account:    #营销分区登录
            sAgt=' and (select sub_division_id from company_company where id=agent_id)='+str(c.sub_division_id)
        else:
            sAgt=' and agent_id='+str(c.id)
    s="select a.name,coalesce(to_char(lv1_dt_fr,'yyyy-mm-dd'),''),coalesce(to_char(lv1_dt_to,'yyyy-mm-dd'),''),"+\
        "to_char(dt_fr,'yyyy-mm-dd'),to_char(dt_to,'yyyy-mm-dd'),to_char(tm,'yyyy-mm-dd HH24:MI'),"+\
        "coalesce(to_char(a.calculated_tm,'yyyy-mm-dd HH24:MI'),''),"+\
        "coalesce(to_char(b.cnt,'999990'),''),coalesce(to_char(b.amt_prv,'99999999.90'),''),"+\
        "coalesce(to_char(b.amt_cur,'99999999.90'),''),coalesce(to_char(b.amt_tt,'99999999.90'),''),"+\
        "coalesce(to_char(c.cnt,'999999'),''),coalesce(to_char(c.amt,'99999999.90'),''),"+\
        "coalesce(to_char(d.cnt,'999999'),''),coalesce(to_char(d.amt,'99999999.90'),''),"+\
        "a.id,"+\
        "case when a.closed_tm is null then '' else to_char(a.closed_tm,'yyyy-mm-dd')||(select last_name from company_user where id=a.closed_user) end,"+\
        "a.terms from "+\
        "(select id,name,period,tm,payable_checked_user_id,payable_checked_tm,application_checked_user_id,application_checked_tm,"+\
            "calculated_tm,closed_user,closed_tm,dt_fr,dt_to,lv1_dt_fr,lv1_dt_to,terms::json from sales_rebateclosing where company_id="+str(c.ancestor_id)+") a "+\
        "left outer join "+\
        "(SELECT rebateclosing_id,count(*) cnt,sum(amount) amt_cur,"+\
            "sum(amount_balance) amt_prv,sum(amount_topay) amt_tt  from sales_rebatepayable "+\
            "where true "+sAgt+" group by rebateclosing_id) b "+\
        "on a.id=b.rebateclosing_id "+\
        "left outer join "+\
        "(SELECT rebateclosing_id,count(*) cnt,sum(amount) amt from sales_rebateapplication "+\
            "where status_id<>5 "+sAgt+" group by rebateclosing_id) c "+\
        "on a.id=c.rebateclosing_id "+\
        "left outer join "+\
        "(SELECT rebateclosing_id,count(*) cnt,sum(amount) amt from sales_rebatepaid "+\
            "where true "+sAgt+" group by rebateclosing_id) d "+\
        "on a.id=d.rebateclosing_id"
    cur.execute(s)
    can_close=False
    if request.user.has_perm('company.sys_admin') and c.level_id==1:
        can_close=True
    for r in cur.fetchall():
        cls=r[16]
        terms=[]        
        for t in r[17]:          
            if t['checked']:
                terms.append(t['name'])
        if not cls and can_close:
            cls='<input type="checkbox" id="cls-'+str(r[15])+'"/><label for="cls-'+str(r[15])+'">关闭</label>'
        data_closing.append([r[0],r[1],r[2],r[3],r[4], r[5], ','.join(terms), r[6], r[7],r[8],r[9],r[10],r[11],r[12],r[13],r[14], cls])  

    periods=[]
    cur.execute("select id,name from sales_rebateclosing where company_id="+str(c.ancestor_id)+" order by dt desc limit 12")
    for r in cur.fetchall():
        periods.append([r[0],r[1]])       
    data['data_closing']=data_closing
    data['clmns_closing']=clmns_closing   
    data['periods']=periods    
    data['last_period']={1:last_month,2:last_quarter}  


   

    sts=[]
    for obj in RebateApplicationStatus.objects.filter(act_sn__isnull=False).order_by('act_sn'):
        sts.append([obj.id,obj.act_name])
    data['status']=sts
    #data['clmns_payable']=['id','经销商/分销商名称','微信钱包','微信收款人实名','上级经销商名称','扫码奖励(元)','应付款金额(元)','备注']
    #data['clmns_payable']=['id','经销商/分销商名称','层级','微信钱包','银行卡','上级经销商名称','扫码奖励','上期余额','本期应付','本期已付']
    #data['clmns_payable']=['id','经销商/分销商','层级','上级经销商名称','本期奖励奖励(元)','上期余额','本期应提(元)','本期提现(元)','本期税金(元)','本期付款(元)']
    data['clmns_payable']=['id','经销商/分销商','层级','上级经销商名称','战区','分区','本期奖励奖励(元)','上期余额','本期应提(元)','本期提现(元)','本期税金(元)','本期付款(元)','提现结余']
    #data['clmns_application']=['id','经销商/分销商名称','所属战区','片区','申请人','申请时间','应付款金额','申请提现(元)','收款人微信','实名','当前状态','审票','放款']
    data['clmns_application']=['id','经销商/分销商','所属战区','申请人','本期奖励','提现','税率%','税额','合计','微信钱包','银行卡','状态','发票','内勤','财务','申请时间']
    data['clmns_paid']=['id','经销商/分销商名称','层级','上级经销商名称','微信付款单号','银行转帐流水单号','转帐时间','转帐金额(元)']

    #data['clmns_transfer']=['id','经销商/分销商','所属战区','申请人','本期奖励','提现','税率%','税额','合计','微信钱包','银行卡','状态','发票','出纳','申请时间']
    data['clmns_transfer']=['id','经销商/分销商','所属战区','申请人','本期奖励','提现','税率%','税额','合计','微信钱包','银行卡','状态','发票','出纳','申请时间','开户行','账号','开户人']

    #data['clmns_lv1']=['id','平台商名称','所属战区','扫码奖励金额','上期余额','应货返金额','货返'] 
    data['clmns_lv1']=['id','平台商名称','所属战区','扫码奖励金额','上期余额','转预付总金额','备注'] 

    data['cal_periods']=cal_periods
    return render(request,'sales/channelrebate/rebate_period_redeem.html',data)


#2020-11-23
@login_required
def rebate_period_action(request,what):
    data={'e':''}
    u=request.user
    c=request.user.company
    cur=connection.cursor()



    if c.level_id==1:           #企业登录
        sAgt=' and company_id='+str(c.id)
    elif c.level_id==2:         #一级登录
        sAgt=' and (select division_id from company_company where  id=agent_id)='+str(c.division_id)
    elif c.level_id==4:         #二级登录
        sAgt=' and  (select sub_division_id from company_company where  id=agent_id)='+str(c.sub_division_id)
    #进行期结操作
    if what=='close':
        if c.level_id!=1:
            data['e']='公司层级错误'
        else:
            pid=int(request.GET.get('id','0'))           
            clss=RebateClosing.objects.filter(id=pid)
            if not clss:
                data['e']='会计期 id 不存在'
            else:
                cls=clss[0]
                if cls.calculated_tm:                
                    data['e']='会计期 '+cls.name+'已做过期结'
                else:  
                    #上期期结ID
                    cur.execute("select max(id) from sales_rebateclosing where company_id="+str(c.id)+" and id<>"+str(pid))
                    r=cur.fetchone()
                    if r:
                        prv_id=str(r[0])
                    else:
                        prv_id='0'
                    #2021-04-01 内参 一级商和二级商 会计期不一致
                    #二级商
                    s="insert into sales_rebatepayable("+\
                            "company_id,parent_id,level_id,amount,amount_balance,amount_topay,dt,tm,agent_id,rebateclosing_id,agent_name,parent_name,log_edit"+\
                        ") select "+\
                        str(c.id)+",c.parent_id,c.level_id,a.cur_val,a.prv_val,a.cur_val+a.prv_val,now(),now(),a.company_id,"+str(cls.id)+",c.name,"+\
                        "(select name from company_company where id=c.parent_id),'[]' from "+\
                        "(select z.company_id,coalesce(sum(z.cur_val),0) cur_val,coalesce(sum(z.prv_val),0) prv_val from "+\
                            "(select company_id,sum(val) cur_val,0 prv_val from sales_channelrebatebatch where ancestor_id="+str(c.id)+\
                            " and (select level_id from company_company where id=company_id)=4 "+\
                            " and (dt between  '"+cls.dt_fr.strftime('%Y-%m-%d')+"' and '"+cls.dt_to.strftime('%Y-%m-%d')+"') group by company_id "+\
                            "union all "+\
                            "select agent_id company_id,0 cur_val,coalesce(amount_balance,0)+coalesce(amount,0)-"+\
                                "coalesce((select sum(amount) from sales_rebatepaid where agent_id=p.agent_id and rebateclosing_id="+prv_id+"),0) prv_val "+\
                                "FROM sales_rebatepayable p where rebateclosing_id="+prv_id+" and level_id=4"+\
                            ") z group by z.company_id"+\
                        ") a "\
                        "left outer join "+\
                        "(select id,name,parent_id,level_id from company_company) c "+\
                        "on a.company_id=c.id where a.cur_val+a.prv_val<>0"
                    cur.execute(s)                    
                    #一级商
                    #s="insert into sales_rebatepayable(company_id,parent_id,level_id,amount,dt,tm,agent_id,rebateclosing_id,agent_name,parent_name,amount_topay,log_edit) select "+\
                    #    str(c.id)+",c.parent_id,c.level_id,a.val,now(),now(),a.company_id,"+str(cls.id)+",c.name,"+\
                    #    "(select name from company_company where id=c.parent_id),a.val,'[]' from "+\
                    #    "(select company_id,sum(val) val from sales_channelrebatebatch where ancestor_id="+str(c.id)+\
                    #        " and (select level_id from company_company where id=company_id)=2 "+\
                    #        " and (dt between '"+cls.lv1_dt_fr.strftime('%Y-%m-%d')+"' and '"+cls.lv1_dt_to.strftime('%Y-%m-%d')+"') group by company_id) a "\
                    #    "left outer join "+\
                    #    "(select id,name,parent_id,level_id from company_company) c "+\
                    #    "on a.company_id=c.id"
                    s="insert into sales_rebatepayable("+\
                            "company_id,parent_id,level_id,amount,amount_balance,amount_topay,dt,tm,agent_id,rebateclosing_id,agent_name,parent_name,log_edit"+\
                        ") select "+\
                        str(c.id)+",c.parent_id,c.level_id,a.cur_val,a.prv_val,a.cur_val+a.prv_val,now(),now(),a.company_id,"+str(cls.id)+",c.name,"+\
                        "(select name from company_company where id=c.parent_id),'[]' from "+\
                        "(select z.company_id,coalesce(sum(z.cur_val),0) cur_val,coalesce(sum(z.prv_val),0) prv_val from "+\
                            "(select company_id,sum(val) cur_val,0 prv_val from sales_channelrebatebatch where ancestor_id="+str(c.id)+\
                            " and (select level_id from company_company where id=company_id)=2 "+\
                            " and (dt between '"+cls.lv1_dt_fr.strftime('%Y-%m-%d')+"' and '"+cls.lv1_dt_to.strftime('%Y-%m-%d')+"') group by company_id "+\
                            "union all "+\
                            "select agent_id company_id,0 cur_val,coalesce(amount_balance,0)+coalesce(amount,0)-"+\
                                "coalesce((select sum(amount) from sales_rebatepaid where agent_id=p.agent_id and rebateclosing_id="+prv_id+"),0) prv_val "+\
                                "FROM sales_rebatepayable p where rebateclosing_id="+prv_id+" and level_id=2"+\
                            ") z group by z.company_id"+\
                        ") a "\
                        "left outer join "+\
                        "(select id,name,parent_id,level_id from company_company) c "+\
                        "on a.company_id=c.id where a.cur_val+a.prv_val<>0"
                    cur.execute(s)
                    cur.execute("select count(*),sum(amount) from sales_rebatepayable where rebateclosing_id="+str(cls.id))
                    r=cur.fetchone()
                    cls.payable_counting=r[0]
                    cls.payable_amount=r[1]
                    cls.calculated_tm=datetime.datetime.now()
                    cls.save()  
    elif what=='list_payable':
        #clmns=['id','经销商/分销商名称','微信钱包','微信收款人实名','上级经销商名称','扫码奖励(元)','应付款金额(元)','备注']
        clmns=['id','经销商/分销商','层级','上级经销商名称','战区','分区','本期奖励(元)','上期余额','本期应提(元)','本期提现(元)','本期税金(元)','本期付款(元)']
        d=[]
        cls_id=request.GET.get('closing_id','')
        lv_id=request.GET.get('lv_id','')
        sLv=''
        if lv_id:
            sLv=" and level_id="+lv_id
        if cls_id:            
            #2021-02-18
            #1;1644;0;"已提交申请"
            #2;1644;1;"内勤已申核"
            #3;1644;2;"财务已审核"
            #4;1644;10;"申请变更"
            #5;1644;10000;"已确认"            
            s="select a.id,a.agent_name,(select name from company_level where id=a.level_id),a.parent,"+\
                "a.amt1,a.amt0,a.amt2,"+\
                "p.amt,p.amt_vat,p.amt_total "+\
                "from "+\
                "(select id,agent_name,coalesce(to_char(amount,'99999999.90'),'') amt1,"+\
                    "case when amount_balance is null then '' else to_char(amount_balance,'99999990.00') end amt0,"+\
                    "coalesce(to_char(amount_topay,'99999999.90'),'') amt2,"+\
                    "case when level_id=2 then '' else parent_name end parent,log_edit,agent_id,level_id "+\
                    "from sales_rebatepayable a where rebateclosing_id="+str(cls_id)+sAgt+sLv+") a "+\
                "left outer join "+\
                "(select agent_id,sum(amount) amt,sum(amount_vat) amt_vat,sum(amount_total) amt_total from sales_rebatepaid "+\
                    "where rebateclosing_id="+str(cls_id)+" group by agent_id) p "+\
                "on a.agent_id=p.agent_id"


            s="select a.id,a.agent_name,(select name from company_level where id=a.level_id),a.parent,"+\
                "a.amount::float,a.amount_balance::float,a.amount_topay::float,"+\
                "p.amt::float,p.amt_vat::float,p.amt_total::float,"+\
                "(coalesce(a.amount,0.0)+coalesce(amount_balance,0.0)-coalesce(p.amt,0.0))::float,"+\
                "(select name from company_salesdivision where id=c.division_id),"+\
                "(select name from company_subsaldivision where id=c.sub_division_id) "+\
                "from "+\
                "(select id,agent_name,amount,agent_id,amount_balance,amount_topay,"+\
                    "case when level_id=2 then '' else parent_name end parent,log_edit,level_id "+\
                    "from sales_rebatepayable a where rebateclosing_id="+str(cls_id)+sAgt+sLv+") a "+\
                "left outer join "+\
                "(select agent_id,sum(amount) amt,sum(amount_vat) amt_vat,sum(amount_total) amt_total from sales_rebatepaid "+\
                    "where rebateclosing_id="+str(cls_id)+" group by agent_id) p "+\
                "on a.agent_id=p.agent_id "+\
                "left outer join "+\
                "(select id,name,division_id,sub_division_id from company_company where ancestor_id="+str(c.ancestor_id)+") c "+\
                "on a.agent_id=c.id"

           
            cur.execute(s)
            for r in cur.fetchall():
                #li=''
                #lgs=json.loads(r[6])                
                #if lgs:                     
                #    for l in lgs:
                #        reason=''
                #        if l['reason']:
                #            reason="<div>"+l['reason']+"</div>"
                #        li=li+'<li>时间:'+l['time']+'</li>'+\
                #            format(l['old'],'.2f')+' → '+format(l['new'],'.2f')+'  '+l['user']+ reason                           
                #if li:                    
                #    li="<span style='cursor:pointer' title='"+r[0]+"：'  data-container='#tb_payable td' "+\
                #        "data-toggle='popover' data-content='<div>"+li+"</div>' >查看修改记录</span>"                 
                d.append([ r[0],r[1],r[2],r[3],  r[11],r[12],  r[4],r[5],r[6],r[7],r[8],r[9],  r[10] ])
        else:
            data['e']='id missing'
        data['clmns']=clmns
        data['data']=d
        
    elif what=='list_application':          
        #clmns=['id','经销商/分销商名称','所属战区','申请人'       ,'应付款金额','申请提现(元)','微信钱包','银行卡','当前状态','发票','内勤审票','财务审票','出纳付款','申请时间']
        #clmns=['id','经销商/分销商名称','所属战区','申请人','类别','应付款','提现','税率%','税额','合计','微信钱包','银行卡','状态','发票','内勤','财务','出纳','申请时间']
        clmns=['id','经销商/分销商','所属战区','申请人','本期奖励','提现','税率%','税额','合计','微信钱包','银行卡','状态','发票','内勤','财务','申请时间']
        d=[]
        cls_id=request.GET.get('closing_id','')
        #2021-07-01
        cls=RebateClosing.objects.get(id=int(cls_id))
        is_closed=False
        if cls.closed_user:
            is_closed=True        
        status=request.GET.get('status','')
        method=request.GET.get('method','')
        invoice_needed=request.GET.get('invoice_needed','')

        sStatus=''       
        sMethod=''
        sInvoice=''
        if method:
            sMethod=" and payment_method="+method
        if invoice_needed:
            sInvoice=" and invoice_needed="+invoice_needed
        if status:
            sStatus=" and status_id="+status
        cnt_transmit=0
        amt_transmit=0.0

        #用户组别
        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 status:
        #sStatus=" and status_id not in (3,10)"
        
        if cls_id:    
            s="select a.agent_name,coalesce(to_char(a.amount,'99999990.00'),''),"+\
                "coalesce((select name from company_salesdivision where id=d.division_id),''),"+\
                "coalesce((select name from company_subsaldivision where id=d.sub_division_id),''),"+\
                "(select status from sales_rebateapplicationstatus where id=a.status_id),"+\
                "(select last_name from company_user where id=user_id),"+\
                "case when a.payment_method=2  then '' else "+\
                    "c.wx_nickname||'<br />'||(select realname from company_wxwallet where id=a.wxwallet_id) end,"+\
                "case when a.payment_method=1  then '' else bnk.bank_name||'<br />'||bnk.bank_account||bnk.payee end,"+\
                "coalesce(to_char((select amount_topay from sales_rebatepayable where rebateclosing_id="+str(cls_id)+" and agent_id=a.agent_id),'99999999.90'),''),"+\
                "coalesce(to_char(tm,'mm-dd HH24:MI'),''),a.status_id,a.id,a.payment_method,a.wx_transmit,"+\
                "jsonb_array_length(img_invoices),case when a.invoice_needed=1 then '开票' else '不开票' end,"+\
                "coalesce(to_char(a.rate_vat,'99'),''),coalesce(to_char(a.amount_vat,'9999990.00'),''),coalesce(to_char(a.amount_total,'9999990.00'),''),a.invoice_needed,"+\
                "coalesce((select (select last_name from company_user where id=user_id)||'<br/>'||to_char(tm,'mm-dd HH24:MI') from company_applicationreviewlog where "+\
                    "content_type='rebateapplication' and obj_id=a.id and status_id=2 order by tm desc limit 1),''),"+\
                "coalesce((select (select last_name from company_user where id=user_id)||'<br/>'||to_char(tm,'mm-dd HH24:MI') from company_applicationreviewlog where "+\
                    "content_type='rebateapplication' and obj_id=a.id and status_id=10 order by tm desc limit 1),''),"+\
                "coalesce((select (select last_name from company_user where id=user_id)||'<br/>'||to_char(tm,'mm-dd HH24:MI') from company_applicationreviewlog where "+\
                    "content_type='rebateapplication' and obj_id=a.id and status_id=6 order by tm desc limit 1),'') "+\
                "from (SELECT id,company_id,agent_name,parent_name,amount,img_invoices,dt,tm,tm_chceked,agent_id,status_id,code,user_id,invoice_needed,rate_vat,amount_vat,amount_total,"+\
                    "payment_method,bankinfo_id,wxwallet_id,(select wxopenid_id from company_wxwallet where id=wxwallet_id) wxopenid_id,wx_transmit "+\
                    "from sales_rebateapplication where rebateclosing_id="+str(cls_id)+sAgt+sStatus+sMethod+sInvoice+") a "+\
                "left outer join "+\
                "(select id,wx_nickname,wx_headimgurl FROM company_wxopenid) c "+\
                "on a.wxopenid_id=c.id "+\
                "left outer join "+\
                "(SELECT id,bank_name,bank_account,payee FROM company_bankinfo where "+\
                    "ancestor_id="+str(c.ancestor_id)+") bnk "+\
                "on a.bankinfo_id=bnk.id "+\
                "left outer join "+\
                "(select id,division_id,sub_division_id,rebate_payment from company_company) d "+\
                "on a.agent_id=d.id "+\
                "left outer join "+\
                "(SELECT id,act_name,status FROM sales_rebateapplicationstatus where company_id="+str(u.ancestor_id)+") s "+\
                "on a.status_id=s.id "                      
            cur.execute(s)
            for r in cur.fetchall():
                backoffice_check=r[20]
                finance_check=r[21]
                casher_pay=r[22]
                reject=''
                status_id=r[10]   
                inv_cnt=r[14]
                m=r[12]
                
                if r[19]:
                    if inv_cnt>0:
                        invoice='<button class="btn btn-primary btn-xs" act="review_invoice">'+str(inv_cnt)+' 张</button>'
                    else:
                        invoice='待上传'
                else:
                    invoice='不开票'
                #1;  0;  1644;"待内勤审票"
                #2;  1;  1644;"待财务审票"
                #10; 2;  1644;"待转帐"
                #3;  3;  1644;"已转帐"
                #4;  10; 1644;"被驳回"
                #5;  20; 1644;"已作废"

                #goup_id
                #4;"系统管理员"
                #12;"销售助理"
                #18;"财务"
                #20;"出纳"
                #7;"内勤管理组"
                app_id=str(r[11])                
                if status_id==1:
                    if is_closed:
                        if u.id==3891:
                            reject='<button class="btn btn-primary btn-xs" act="reject">驳回</button>'
                    else:
                        if 4 in group_ids or 12 in group_ids:
                            reject='<button class="btn btn-primary btn-xs" act="reject">驳回</button>'
                        if (inv_cnt>0 and 12 in group_ids) or r[19]==0:
                            backoffice_check='<input type="checkbox" class="backoffice_check" id="cb-backoffice-'+app_id+'"/><label for="cb-backoffice-'+app_id+'">通过</label>'
                elif status_id==2:
                    if not is_closed:
                        if 4 in group_ids or 18 in group_ids:
                            reject='<button class="btn btn-primary btn-xs" act="reject">驳回</button>'                    
                        if 18 in group_ids:
                            finance_check='<input type="checkbox" class="finance_check" id="cb-finance-'+app_id+'"/><label for="cb-finance-'+app_id+'">通过</label>'   
                elif status_id in [3,10]:
                    if not is_closed:
                        if 4 in group_ids or 20 in group_ids:
                            reject='<button class="btn btn-primary btn-xs" act="reject">驳回</button>'
                        if 20 in group_ids:                            
                            casher_pay='<input type="checkbox" id="cb-casher-'+app_id+'" class="casher_pay" method="'+str(m)+'" />'+\
                                '<label for="cb-casher-'+app_id+'">转帐</label>'   
                status=r[4]
                if status_id in [4,5]:
                    status='<a class="viewlog" href="/sales/channelretate/period/action/view_rebateapplog/?id='+app_id+'">'+r[4]+'</a>'
                d.append([app_id,'<sapan class="agent">'+r[0]+'</span>',r[2],r[5],r[8],'<span class="amt">'+r[1]+'</span>',
                    r[16],r[17],r[18],r[6],r[7],status+'<br/>'+reject,invoice,backoffice_check,finance_check,r[9] ])           
        else:
            data['e']='id missing'
        data['clmns']=clmns
        data['data']=d   
        data['cnt_transmit']=cnt_transmit
        data['amt_transmit']=amt_transmit

        #print(amt_transmit)

    

    elif what=='list_transfer':          
        #clmns=['id','经销商/分销商名称','所属战区','申请人'       ,'应付款金额','申请提现(元)','微信钱包','银行卡','当前状态','发票','内勤审票','财务审票','出纳付款','申请时间']
        #clmns=['id','经销商/分销商名称','所属战区','申请人','类别','应付款','提现','税率%','税额','合计','微信钱包','银行卡','状态','发票','内勤','财务','出纳','申请时间']
        clmns=['id','经销商/分销商','所属战区','申请人','本期奖励','提现','税率%','税额','合计','微信钱包','银行卡','状态','发票','出纳','申请时间','开户行','账号','开户人']
        d=[]
        cls_id=request.GET.get('closing_id','')
        #2021-07-01
        cls=RebateClosing.objects.get(id=int(cls_id))
        is_closed=False
        if cls.closed_user:
            is_closed=True 
        status=request.GET.get('status','')
        method=request.GET.get('method','')
        invoice_needed=request.GET.get('invoice_needed','')

        sStatus=''       
        sMethod=''
        sInvoice=''
        if method:
            sMethod=" and payment_method="+method
        if invoice_needed:
            sInvoice=" and invoice_needed="+invoice_needed

        cnt_transmit=0
        amt_transmit=0.0
        amt_unpaid=0.0
        amt_paid=0.0
        #用户组别
        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 not status:
            sStatus=" and status_id in (3,6,10)"
        else:
            sStatus=" and status_id in ("+status+")"
        if cls_id:    
            s="select a.agent_name,coalesce(to_char(a.amount,'99999990.00'),''),"+\
                "coalesce((select name from company_salesdivision where id=d.division_id),''),"+\
                "coalesce((select name from company_subsaldivision where id=d.sub_division_id),''),"+\
                "(select status from sales_rebateapplicationstatus where id=a.status_id),"+\
                "(select last_name from company_user where id=user_id),"+\
                "case when a.payment_method=2  then '' else "+\
                    "c.wx_nickname||'<br />'||(select realname from company_wxwallet where id=a.wxwallet_id) end,"+\
                "case when a.payment_method=1  then '' else bnk.bank_name||'<br />'||bnk.bank_account||bnk.payee end,"+\
                "coalesce(to_char((select amount_topay from sales_rebatepayable where rebateclosing_id="+str(cls_id)+" and agent_id=a.agent_id),'99999999.90'),''),"+\
                "coalesce(to_char(tm,'mm-dd HH24:MI'),''),a.status_id,a.id,a.payment_method,a.wx_transmit,"+\
                "jsonb_array_length(img_invoices),case when a.invoice_needed=1 then '开票' else '不开票' end,"+\
                "coalesce(to_char(a.rate_vat,'99'),''),coalesce(to_char(a.amount_vat,'9999990.00'),''),coalesce(to_char(a.amount_total,'9999990.00'),''),a.invoice_needed,"+\
                "coalesce((select (select last_name from company_user where id=user_id)||'<br/>'||to_char(tm,'mm-dd HH24:MI') from company_applicationreviewlog where "+\
                    "content_type='rebateapplication' and obj_id=a.id and status_id=2 order by tm desc limit 1),''),"+\
                "coalesce((select (select last_name from company_user where id=user_id)||'<br/>'||to_char(tm,'mm-dd HH24:MI') from company_applicationreviewlog where "+\
                    "content_type='rebateapplication' and obj_id=a.id and status_id=10 order by tm desc limit 1),''),"+\
                "coalesce((select (select last_name from company_user where id=user_id)||'<br/>'||to_char(tm,'mm-dd HH24:MI') from company_applicationreviewlog where "+\
                    "content_type='rebateapplication' and obj_id=a.id and status_id=6 order by tm desc limit 1),''),"+\
                "(select (select last_name from company_user where id=operator_id)||'<br/>'||to_char(tm,'mm-dd HH24:MI') from sales_rebatepaylog "+\
                    "where application_id=a.id order by id desc limit 1),"+\
                "coalesce(bnk.bank_name,''),coalesce(bnk.bank_account,''),coalesce(bnk.payee,''),"+\
                "wa.status_id,(select remark from company_wxwalletstatus where id=wa.status_id) from "+\
                "(SELECT id,company_id,agent_name,parent_name,amount,img_invoices,dt,tm,tm_chceked,agent_id,status_id,code,user_id,invoice_needed,rate_vat,amount_vat,amount_total,"+\
                    "payment_method,bankinfo_id,wxwallet_id,(select wxopenid_id from company_wxwallet where id=wxwallet_id) wxopenid_id,wx_transmit "+\
                    "from sales_rebateapplication where rebateclosing_id="+str(cls_id)+sAgt+sStatus+sMethod+sInvoice+\
                ") a "+\
                "left outer join "+\
                "(select id,wx_nickname,wx_headimgurl FROM company_wxopenid) c "+\
                "on a.wxopenid_id=c.id "+\
                "left outer join "+\
                "(SELECT id,bank_name,bank_account,payee FROM company_bankinfo where "+\
                    "ancestor_id="+str(c.ancestor_id)+") bnk "+\
                "on a.bankinfo_id=bnk.id "+\
                "left outer join "+\
                "(select id,division_id,sub_division_id,rebate_payment from company_company) d "+\
                "on a.agent_id=d.id "+\
                "left outer join "+\
                "(SELECT id,act_name,status FROM sales_rebateapplicationstatus where company_id="+str(u.ancestor_id)+") s "+\
                "on a.status_id=s.id "+\
                "left outer join "+\
                "(SELECT id,cellphone,realname,company_id,wxopenid_id,application_id FROM company_wxwallet) w "+\
                "on w.id=a.wxwallet_id "+\
                "left outer join "+\
                "(SELECT id, status_id,wxwallet_id FROM company_wxwalletapplication) wa "+\
                "on wa.id=w.application_id"
            cur.execute(s)
            for r in cur.fetchall():
                backoffice_check=r[20]
                finance_check=r[21]
                casher_pay=r[22]
                reject=''
                status_id=r[10]   
                inv_cnt=r[14]
                m=r[12]
                


                if status_id==6:
                    amt_paid=amt_paid+float(r[18])
                else:
                    amt_unpaid=amt_unpaid+float(r[18])
                #print(status_id,amt_paid,amt_unpaid)
                if r[19]:
                    if inv_cnt>0:
                        invoice='<button class="btn btn-primary btn-xs" act="review_invoice">'+str(inv_cnt)+' 张</button>'
                    else:
                        invoice='待上传'
                else:
                    invoice='不开票'
                wa_status=''
                if r[27]:
                    if r[27]!=5:
                        wa_status=r[28]

                #1;  0;  1644;"待内勤审票"
                #2;  1;  1644;"待财务审票"
                #10; 2;  1644;"待转帐"
                #3;  3;  1644;"已转帐"
                #4;  10; 1644;"被驳回"
                #5;  20; 1644;"已作废"

                #goup_id
                #4;"系统管理员"
                #12;"销售助理"
                #18;"财务"
                #20;"出纳"
                #7;"内勤管理组"
                app_id=str(r[11])                
                #if status_id==1:
                #    if 4 in group_ids or 12 in group_ids:
                #        reject='<button class="btn btn-primary btn-xs" act="reject">驳回</button>'
                #    #if (inv_cnt>0 and 12 in group_ids) or r[19]==0:
                #    #    backoffice_check='<input type="checkbox" class="backoffice_check" id="cb-backoffice-'+app_id+'"/><label for="cb-backoffice-'+app_id+'">通过</label>'
                #elif status_id==2:
                #    if 4 in group_ids or 18 in group_ids:
                #        reject='<button class="btn btn-primary btn-xs" act="reject">驳回</button>'
                #    if r[19]:
                #        #if inv_cnt>0 and 18 in group_ids:
                #        #    finance_check='<input type="checkbox" class="finance_check" id="cb-finance-'+app_id+'"/><label for="cb-finance-'+app_id+'">通过</label>'
                #    else:
                #        casher_pay='<input type="checkbox" id="cb-casher-'+app_id+'" class="casher_pay" method="'+str(m)+'" />'+\
                #                '<label for="cb-casher-'+app_id+'">转帐</label>' 

                #elif status_id in [3,10]:


                if (4 in group_ids or 20 in group_ids) and status_id!=6 and not is_closed:
                        reject='<button class="btn btn-primary btn-xs" act="reject">驳回</button>'
                if status_id==6:
                    casher_pay=r[23]
                else:
                    if 20 in group_ids  and not is_closed:                            
                        casher_pay='<input type="checkbox" id="cb-casher-'+app_id+'" class="casher_pay" method="'+str(m)+'" wa_status="'+wa_status+'"/>'+\
                            '<label for="cb-casher-'+app_id+'">转帐</label>'   
                #status=r[4]
                #if status_id in [4,5]:
                #status='<a class="viewlog" href="/sales/channelretate/period/action/view_rebateapplog/?id='+app_id+'">'+r[4]+'</a>'
                status=r[4]
                d.append([app_id,'<sapan class="agent">'+r[0]+'</span>',r[2],r[5],r[8],'<span class="amt">'+r[1]+'</span>',
                    r[16],r[17],r[18],r[6],r[7],status+'<br/>'+reject,invoice,casher_pay,r[9], r[24],r[25],r[26] ])           
        else:
            data['e']='id missing'
        data['clmns']=clmns
        data['data']=d   
        data['amt_paid']=amt_paid
        data['amt_unpaid']=amt_unpaid
        

        
    elif what=='list_paid':
        clmns=['id','经销商/分销商名称','层级','上级经销商名称','微信付款单号','银行转帐流水单号','转帐时间','转帐金额(元)']
        d=[]
        cls_id=request.GET.get('closing_id','')

        sMethod=request.GET.get('paid_method','')
        sLv=request.GET.get('paid_lv','')
        if sMethod:
            sMethod=" and payment_method="+sMethod
        if sLv:
            sLv=" and level_id="+sLv
        if cls_id:
            s="select agent_name,case when a.level_id=2 then '' else parent_name end,coalesce(to_char(amount,'99999999.90'),''),"+\
                "(select name from company_level where id=a.level_id),id,"+\
                "coalesce(payment_no,''),coalesce(bank_statement,''),"+\
                "case when bank_statement is null then to_char(tm,'yyyy-mm-dd HH24:MI:SS') else to_char(dt,'yyyy-mm-dd') end "+\
                "from "+\
                "sales_rebatepaid a where rebateclosing_id="+str(cls_id)+sAgt+sMethod+sLv 
            cur.execute(s)
            for r in cur.fetchall():
                d.append([ r[4], r[0],r[3],r[1],r[5],r[6],r[7],r[2]])
        else:
            data['e']='id missing'
        data['clmns']=clmns
        data['data']=d


    elif what=='list_lv1':
        clmns=['id','平台商名称','所属战区','扫码奖励金额','上期余额','转预付总金额','备注']     
        #用户组别
        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])
        d=[]
        cls_id=request.GET.get('closing_id','')   
        if cls_id:
            s="select id,agent_name,"+\
                "(select name from company_salesdivision where id=(select division_id from company_company where id=agent_id)),"+\
                "coalesce(to_char(amount,'99999999.90'),''),"+\
                "coalesce(to_char(amount_balance,'99999999.90'),''),"+\
                "coalesce(to_char(amount_topay,'99999999.90'),''),"+\
                "coalesce((select (select last_name from company_user where id=user_id)||'<br/>'||to_char(tm,'yyyy-mm-dd HH24:MI:SS') "+\
                    "from sales_rebatepaid where rebatepayable_id=a.id),'') "+\
                "from sales_rebatepayable a where rebateclosing_id="+str(cls_id)+" and level_id=2 "+sAgt 
            cur.execute(s)            
            for r in cur.fetchall():
                act=r[6]
                act=''
                pid=str(r[0])
                #if not act and 18 in group_ids:
                #    act='<input type="checkbox" id="cb-lv1-'+pid+'" class="lv1_pay" />'+\
                #        '<label for="cb-lv1-'+pid+'">货返</label>'


                d.append([ r[0],r[1],r[2],r[3],r[4],r[5],act ])
        else:
            data['e']='id missing'
        data['clmns']=clmns
        data['data']=d

    elif what=='review_invoice':
        u=request.user
        if request.method=='GET':            
            id=request.GET.get('id','')
            data={'id':id,'images':[]}
            app=None
            apps=RebateApplication.objects.filter(id=int(id))
            if apps:
                app=apps[0]
            if app:
                data['payment_method']=app.payment_method
                data['amount'] = app.amount_total
                if app.payment_method==1:
                    data['payee_name'] = app.wxwallet.realname                    
                    data['wx_nickname'] = app.wx_nickname
                    data['wx_headimgurl'] = app.wx_headimgurl
                else:
                    data['bank_name']=app.bankinfo.bank_name
                    data['bank_account']=app.bankinfo.bank_account
                    data['payee']=app.bankinfo.payee
                for i in app.img_invoices:
                    data['images'].append(i)                    
            return render(request,'sales/channelrebate/agent_invoices.html',data)
    elif what=='rebateapplication_reject':
        u=request.user
        if request.method=='GET':            
            id=request.GET.get('id','')
            data={'id':id,'images':[]}
            app=None
            apps=RebateApplication.objects.filter(id=int(id))
            if apps:
                app=apps[0]
            if app:
                #2021-04-19
                data['payee_name']=''
                if app.payment_method==1:
                    if app.wxwallet:
                        data['payee_name'] = app.wxwallet.realname
                else:
                    if app.bankinfo:
                        data['payee_name'] = app.bankinfo.payee
                data['amount'] = app.amount
                data['wx_nickname'] = app.wx_nickname
                data['wx_headimgurl'] = app.wx_headimgurl
                for i in app.img_invoices:
                    data['images'].append(i)                    
            return render(request,'sales/channelrebate/rebateapplication_reject.html',data)
        else:
            r=json.loads(request.body)           
            id=int(r['id'])
            reason=r['reason'] 
            action=r['action']
            status=''
            app=None
            apps=RebateApplication.objects.filter(id=int(id))
            if apps:
                app=apps[0]
            if app:
                u=request.user
                if action=='reject':
                    app.status_id=4
                    status='被驳回'
                elif action=='scrap':
                    app.status_id=5
                    status='已作废'
                app.save()
                l=ApplicationReviewLog(company_id=u.ancestor_id, content_type='rebateapplication', obj_id=app.id, status_id=app.status_id, user_id=u.id, comment=reason)
                l.save()                
            else:
                data['e']='id missing or incorrect'
            data['status']=status 
    elif what=='invoice':  
        checked=request.POST.get('checked')
        id=request.POST.get('id')
        app=None
        apps=RebateApplication.objects.filter(id=int(id))
        if apps:
            app=apps[0]
        if app:
            if checked=='true':
                #app.status_id=3
                #status='待放款'
                #2021-03-02 核票后直接待转帐
                app.status_id=8
                status='待转帐'
            else:
                app.status_id=2
                status='待审票'
            app.save()
        else:
            data['e']='id missing or incorrect'
        data['status']=status         
    elif what=='transmit':
        data['status']='待转帐'
        checked=request.POST.get('checked')
        id=request.POST.get('id')
        app=None
        apps=RebateApplication.objects.filter(id=int(id))
        if apps:
            app=apps[0]
        if app:            
            if checked=='true':               
                app.wx_transmit=True                
            else:
                app.wx_transmit=False 
            app.save()
        else:
            data['e']='id missing or incorrect'    
    #2021-03-28
    elif what=='backoffice_check':  
        checked=request.POST.get('checked')
        id=request.POST.get('id')
        app=None
        apps=RebateApplication.objects.filter(id=int(id))
        if apps:
            app=apps[0]
        if app:
            if checked=='true':      
                #2021-04-15 不开票提现也要财务审核
                app.status_id=2
                status='财务审票'
                
                #if app.invoice_needed:
                #    app.status_id=2
                #    status='财务审票'
                #else:
                #    app.status_id=3
                #    status='出纳付款'
            else:
                app.status_id=1
                status='内勤审票'
            app.save()
            l=ApplicationReviewLog(company_id=u.ancestor_id, content_type='rebateapplication', obj_id=app.id, status_id=app.status_id, user_id=u.id)
            l.save() 
        else:
            data['e']='id missing or incorrect'
        data['status']=status
    elif what=='finance_check':  
        checked=request.POST.get('checked')
        id=request.POST.get('id')
        app=None
        apps=RebateApplication.objects.filter(id=int(id))
        if apps:
            app=apps[0]
        if app:
            if checked=='true':               
                app.status_id=10
                status='出纳付款'
            else:
                app.status_id=2
                status='财务审票'
            app.save()
            l=ApplicationReviewLog(company_id=u.ancestor_id, content_type='rebateapplication', obj_id=app.id, status_id=app.status_id, user_id=u.id)
            l.save() 
        else:
            data['e']='id missing or incorrect'
        data['status']=status 
    elif what=='rebateapplication_bankpaid':
        u=request.user
        if request.method=='GET':            
            id=request.GET.get('id','')
            data={'id':id,'images':[]}
            app=None
            apps=RebateApplication.objects.filter(id=int(id))
            if apps:
                app=apps[0]
            if app:                
                data['bank_name'] = app.bankinfo.bank_name                
                data['bank_account'] = app.bankinfo.bank_account
                data['amount'] = app.amount                
                #for i in app.img_invoices:
                #    data['images'].append(i)                    
            return render(request,'sales/channelrebate/rebateapplication_bankpaid.html',data)
        else:
            r=json.loads(request.body)           
            id=int(r['id'])
            dt_paid=r['dt_paid']
            bank_statement=r['bank_statement']
            status=''
            a=None
            apps=RebateApplication.objects.filter(id=int(id))
            if apps:
                a=apps[0]
            if a:
                u=request.user          
                l=ApplicationReviewLog(company_id=u.ancestor_id,content_type='rebateapplication', obj_id=a.id, status_id=6, user_id=u.id)
                l.save()   
                ps=RebatePaid.objects.filter(application_id=a.id)
                if not ps:
                    p=RebatePaid(company_id=c.ancestor_id,rebateclosing_id=a.rebateclosing_id,application_id=a.id,operator_id=u.id,level_id=a.level_id,
                        parent_name=a.parent_name,agent_id=a.agent_id,agent_name=a.agent_name,user_id=a.user_id,bank_statement=bank_statement,
                        invoice_needed=a.invoice_needed,payment_method=a.payment_method,)                    
                else:
                    p=ps[0]
                    p.dt=dt_paid
                    p.bank_statement=bank_statement     
                p.amount=a.amount
                p.amount_vat=a.amount_vat
                p.amount_total=a.amount_total  
                p.rebatepaylog_id=l.id               
                p.save()
                a.status_id=6
                status='已转帐'
                a.save()                  
                
            else:
                data['e']='id missing or incorrect'
            data['status']=status 
    elif what=='lv1_pay':
        u=request.user
        if request.method=='GET':            
            id=request.GET.get('id','')
            data={}
            app=None
            apps=RebatePayable.objects.filter(id=int(id))
            if apps:
                app=apps[0]
            if app:          
                data['amount'] = app.amount
                data['amount_balance'] = app.amount_balance                
                data['amount_topay'] = app.amount_topay
                data['id']=id
                                
                #for i in app.img_invoices:
                #    data['images'].append(i)                    
            return render(request,'sales/channelrebate/lv1_pay.html',data)
        else:
            r=json.loads(request.body)           
            id=int(r['id'])
            dt_paid=r['dt_paid']
            amt_paid=r['amt_paid']
            #bank_statement=r['bank_statement']
            status=''
            a=None
            apps=RebatePayable.objects.filter(id=int(id))
            if apps:
                a=apps[0]
            if a:
                u=request.user          
                l=ApplicationReviewLog(company_id=u.ancestor_id,content_type='rebatepayable', obj_id=a.id, status_id=3, user_id=u.id)
                l.save()   
                ps=RebatePaid.objects.filter(rebatepayable_id=a.id)
                if not ps:
                    p=RebatePaid(company_id=c.ancestor_id,rebateclosing_id=a.rebateclosing_id,operator_id=u.id,level_id=a.level_id,
                       agent_id=a.agent_id,agent_name=a.agent_name,amount=a.amount,amount_total=float(amt_paid),user_id=u.id,
                        invoice_needed=0,payment_method=3,rebatepayable_id=a.id,dt=dt_paid)
                    p.save()
                else:
                    p=ps[0]
                    p.dt=dt_paid                 
                            
                p.rebatepaylog_id=l.id               
                p.save()
                a.status_id=6
                status='已转帐'
                a.save()                  
                
            else:
                data['e']='id missing or incorrect'
            data['status']=status 
    elif what=='wx_pay':
        companyCertId=0
        #2021-03-02 logistics.company_companycert
        if c.ancestor_id==1644: 
            companyCertId=1 
        result=[]
        clmns=['经销商名称','转帐金额','电话','微信昵称','收款人','处理结果']
        
        data={'cnt_paid':0,'amt_paid':0.00,'cnt_err':0,'amt_err':0.00}
        #cls_id=request.POST.get('closing_id','')
        ids=request.POST.get('ids','')
        a_ids=ids.split(',')
        if len(a_ids)==0:
            return JsonResponse(data)
        x_forwarded_for = request.META.get('HTTP_X_FORWARDED_FOR')
        if x_forwarded_for:
            ip = x_forwarded_for.split(',')[-1].strip()
        else:
            ip = request.META.get('REMOTE_ADDR')  
        for a in  RebateApplication.objects.filter(id__in=ids.split(',')):            
            sn='NC'+str(a.id).zfill(10)  
            r=start_wx_enterprise_pay(companyCertId,a.wxwallet.wxopenid.wx_openid,int(a.amount_total*100),sn,ip,a.wxwallet.realname,a.rebateclosing.name+'扫码奖励')
            l=RebatePaylog(company_id=c.ancestor_id,application_id=a.id,operator_id=u.id,msg=r) 
            if r['return_code']=="SUCCESS" and r["result_code"]=='SUCCESS':                
                l.is_success=True                    
                data['cnt_paid']=data['cnt_paid']+1
                data['amt_paid']=data['amt_paid']+float(a.amount_total)
                ps=RebatePaid.objects.filter(application_id=a.id)
                if not ps:
                    p=RebatePaid(company_id=c.ancestor_id,rebateclosing_id=a.rebateclosing_id,application_id=a.id,rebatepaylog_id=l.id,operator_id=u.id,level_id=a.level_id,
                        parent_name=a.parent_name,agent_id=a.agent_id,agent_name=a.agent_name,payment_no=sn,user_id=a.user_id)                    
                else:
                    p=ps[0]
                    p.dt=datetime.datetime.now()
                    p.tm=datetime.datetime.now()
                    p.rebatepaylog_id=l.id                
                p.amount=a.amount
                p.amount_vat=a.amount_vat
                p.amount_total=a.amount_total                
                p.save()
                #已转帐
                a.status_id=6
                a.save()
                result.append([a.agent_name,str(a.amount_total),a.wxwallet.cellphone,a.wxwallet.wxopenid.wx_nickname,a.wxwallet.realname,'成功'])
                    
                #2021-04-13
                try:
                    lg=ApplicationReviewLog(company_id=u.ancestor_id,content_type='rebateapplication', obj_id=a.id, status_id=6, user_id=u.id)
                    lg.save()
                except Exception:
                    pass
            else:
                l.is_success=False                
                data['cnt_err']=data['cnt_err']+1
                data['amt_err']=data['amt_err']+float(a.amount_total)
                result.append([a.agent_name,str(a.amount_total),a.wxwallet.cellphone,a.wxwallet.wxopenid.wx_nickname,a.wxwallet.realname,'失败:'+l.msg['err_code_des']])

            l.save()  

        #2021-05-03
        data['data']=result
        data['clmns']=clmns        
        return render(request,'sales/channelrebate/transfer_result.html',data)
    elif what=='cancel_paid':  
        #checked=request.POST.get('checked')
        id=request.POST.get('id')
        app=None
        apps=RebateApplication.objects.filter(id=int(id))
        if apps:
            app=apps[0]
        if app:                        
            app.status_id=3
            status='出纳付款'           
            app.save()
            l=ApplicationReviewLog(company_id=u.ancestor_id, content_type='rebateapplication', obj_id=app.id, status_id=app.status_id, user_id=u.id)
            l.save() 
        else:
            data['e']='id missing or incorrect'
        data['status']=status 

    elif what=='view_rebateapplog':
        data={'comment':'','user':'','time':'','status':''}
        id=request.GET.get('id','0')
        cur.execute("select to_char(tm,'yyyy-mm-dd HH24:MI:SS'),coalesce(comment,''),"+\
            "(select last_name from company_user where id=user_id),"+\
            "(select name from sales_rebateapplicationstatus where id=status_id) "+\
            "from company_applicationreviewlog where content_type='rebateapplication' and obj_id="+id+" order by id desc limit 1")
        r=cur.fetchone()        
        if r:     
            data['time']=r[0]
            data['comment']=r[1]
            data['user']=r[2]
            data['status']=r[3]            
        return render(request,'sales/channelrebate/view_appreviewlog.html',data)

    #2021-05-18
    elif what=='close_period':      
        data={'e':'','msg':''}
        tm=datetime.datetime.now().strftime('%Y-%m-%d')
        #r=json.loads(request.body)           
        cls_id=request.POST.get('cls_id')
        cur.execute("update sales_rebateclosing set closed_tm=now(),closed_user="+str(u.id)+" where id="+cls_id)
        data['msg']=tm+u.last_name
    return JsonResponse(data)




@login_required
def saldiv_rebates_ajax(request):
    user_id=request.user.id
    product_id=request.GET.get('product_id','')
    dt=datetime.datetime.now().strftime('%Y-%m-%d')
    dt=request.GET.get('date',dt)
    data=saldiv_rebates_data(user_id,dt,product_id)
    return JsonResponse(data)


def saldiv_rebates_app(request):  
    k='fgfdvgu$#&3t@j'
    e = time_key_check(request, k)
    if e:
        return JsonResponse({'e': e})

    user_id=request.GET.get('user_id','')
    if not user_id:
        return JsonResponse({'e': 'user_id missing'})

    product_id=request.GET.get('product_id','')
    dt=datetime.datetime.now().strftime('%Y-%m-%d')
    dt=request.GET.get('date',dt)
    data=saldiv_rebates_data(user_id,dt,product_id)
    return JsonResponse(data,safe=False)




#2020-12-08
def saldiv_rebates_details(user_id,dt,division_id,vtype_id,is_app=False,product_id=None):
    dt0='2020-11-01'    #最早发货时间
    data=[]
    u=User.objects.get(id=int(user_id))
    aid=str(u.company.ancestor_id)
    cur=connection.cursor()
    if product_id:
        product_ids='('+str(product_id)+')'   
    else:
        product_ids="(900,1109)"
    p="(select name from company_company where id=a.parent_id) parent,"
    if int(vtype_id)==1:
        clmns=['经销商','分销商','类型','扫码时间','数量(箱)','物流码(箱码)','数量(瓶)','物流码(瓶码)']
        t="'分销' agent,"        
        sLv="4 "
    elif int(vtype_id)==2:
        clmns=['经销商',         '类型','扫码时间','数量(箱)','物流码(箱码)','数量(瓶)','物流码(瓶码)']
        t='(select name from company_company where id=b.company_to_id) agent,'        
        sLv="2 "    
    s="select (select name from company_salesdivision where id="+str(division_id)+"),"+\
            p+"(select name from company_company where id=a.company_id),"+t+\
            "to_char(a.tm,'yyyy-mm-dd HH24:MI'), "+\
            "coalesce(dx.cnt,0),coalesce(dx.labels,''),coalesce(dp.cnt,0),coalesce(dp.labels,'') "+\
            "from "+\
            "(select id,batch_id,company_id,tm,"+\
                "(select parent_id from  company_company where id=b1.company_id) parent_id "+\
                "from sales_channelrebatebatch b1 where ancestor_id="+aid+" and confirmed=true "+\
                "and (select level_id from company_company where id=b1.company_id)="+sLv+\
                "and vtype_id="+vtype_id+" and (dt between '"+dt0+"' and '"+dt+"') "\
                "and (select division_id from company_company where id=b1.company_id)="+division_id+\
            ") a "+\
            "left outer join "+\
            "(select id,company_to_id from inventory_batch) b "+\
            "on a.batch_id=b.id "+\
            "left outer join "+\
            "(select rebate_batch_id,count(*) cnt,"+\
                "array_to_string(ARRAY(SELECT unnest(array_agg(label_code))),'|<br/>') labels "+\
                "from sales_channelrebatedetails where label_pkg=3 "+\
                "and product_id in "+product_ids+" group by rebate_batch_id"+\
            ") dx "+\
            "on a.id=dx.rebate_batch_id "+\
            "left outer join "+\
            "(select rebate_batch_id,count(*) cnt,"+\
                "array_to_string(ARRAY(SELECT unnest(array_agg(label_code))),'|<br/>') labels "+\
                "from sales_channelrebatedetails where label_pkg=1 "+\
                "and product_id in "+product_ids+" group by rebate_batch_id"+\
            ") dp "+\
            "on a.id=dp.rebate_batch_id "+\
            "order by (select name from company_company where id=a.parent_id),a.company_id,a.tm"        
    cur.execute(s)
    for r in cur.fetchall():
            if int(vtype_id)==1:
                data.append([ r[1],r[2],r[3],r[4],r[5],r[6],r[7],r[8] ])
            elif int(vtype_id)==2:
                data.append([ r[2],r[3],r[4],r[5],r[6],r[7],r[8] ])           
    return {'clmns':clmns,'data':data}


#2020-12-08
@login_required
def saldiv_rebates_details_ajax(request):
    user_id=request.user.id
    division_id=request.GET.get('division_id','')
    product_id=request.GET.get('product_id','')
    vtype_id=request.GET.get('vtype_id','')
    dt=request.GET.get('date','')    
    data=saldiv_rebates_details(user_id,dt,division_id,vtype_id,product_id)
    #return JsonResponse(data,safe=False)
    return render(request,'sales/saldiv_rebates_details.html',data)


#2020-12-09
def saldiv_rebates_details_app(request):
    k='fgfdvgu$#&3t@j'
    e = time_key_check(request, k)
    if e:
        return JsonResponse({'e': e})
    user_id=request.GET.get('user_id','')        
    division_id=request.GET.get('division_id','')
    product_id=request.GET.get('product_id','')
    vtype_id=request.GET.get('vtype_id','')
    dt=request.GET.get('date','')    
    if not user_id or not division_id or not vtype_id or not dt:
        return JsonResponse({'e': 'user_id,division_id,vtype_id,dt required'})
    
    data=saldiv_rebates_details(user_id,dt,division_id,vtype_id,True,product_id)
    return JsonResponse(data,safe=False)
    
    

#2020-12-01 此接口只用于公司内部营销相关人员使用
def saldiv_rebates_data(user_id,dt,product_id=None):
    dt0='2020-11-01'    #最早发货时间 
    data={'divisions':[],'product_id':product_id,'date':dt}

    u=User.objects.get(id=int(user_id))
    c=u.company
    company_id=str(c.id)
    ancestor_id=str(c.ancestor_id)
    if product_id:
        product_ids='('+str(product_id)+')'   
    else:
        product_ids="(900,1109)"   
    
    cur=connection.cursor()
    provincial_saldiv=[]
    extra_saldiv=[]
    
    sWhere_cur=" where dt='"+dt+"' and ancestor_id="+ancestor_id
    sWhere_acc=" where ancestor_id="+ancestor_id+" and (dt between '"+dt0+"' and '"+dt+"')"    
    sWhere_stk=" where company_id="+ancestor_id+" and vtype_id=2 and (dt between '"+dt0+"' and '"+dt+"') "+\
        "and product_id in "+product_ids+" "
    sWhere_saldiv=''
    sWhere_subsaldiv=''
    sWhere_anent=''
    aa=[]
    if int(user_id) not in [1,6,3891]:      #admin,nc_admin,tj_nc_admin
        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 d in r[0]:
                    aa.append(str(d))
                if aa:
                    sWhere_saldiv=" where a.id in ("+",".join(aa)+") "
        else:
            return data 
    cur.execute("select category from company_salesdivision where company_id="+ancestor_id+" group by category order by category")
    rs=cur.fetchall()
    sUnion=''
    for r0 in rs:
        cat_id=r0[0]
        if cat_id==1:
            cat_name='湖南市场'
        elif cat_id==2:
            cat_name='省外战区'
        elif cat_id==3:
            cat_name=''
            sUnion=" union all select 0,'其它',0,999,'' "
        d=[]
        sWhere_div=" where company_id="+ancestor_id+" and is_delete=false and rpt_shown=true and category="+str(cat_id)          
        s="select a.id,a.name,coalesce(g1.boxes1,0),coalesce(g1.boxes2,0),"+\
            "coalesce(g2.boxes1,0),coalesce(g2.boxes2,0),coalesce(d.boxes,0),a.code from "+\
            "(select id,name,provincial,sn,code from company_salesdivision"+sWhere_div+sUnion+") a "+\
            "left outer join "+\
            "(select division_id,sum(boxes1) boxes1,sum(boxes2) boxes2 "+\
                "from "+\
                "(select coalesce((select division_id from company_company where id=company_id),0) division_id,"+\
                    "(select count(*) from sales_channelrebatedetails where rebate_batch_id=a1.id and label_pkg=3 and product_id in "+product_ids+") boxes1,0 boxes2 "+\
                    "from sales_channelrebatebatch a1 "+sWhere_cur+" and confirmed=true and vtype_id=2 "+\
                    "and (select level_id from company_company where id=a1.company_id)=2 "+\
                "union all "+\
                "select coalesce((select division_id from company_company where id=company_id),0) division_id,"+\
                    "0 boxes1,(select count(*) from sales_channelrebatedetails where rebate_batch_id=a2.id and label_pkg=3 and product_id in "+product_ids+") boxes2 "+\
                    "from sales_channelrebatebatch a2 "+sWhere_cur+" and confirmed=true and vtype_id=1 "+\
                    "and (select level_id from company_company where id=a2.company_id)=4"+\
                ") b "+\
                "group by division_id"+\
            ") g1 "+\
            "on a.id=g1.division_id "+\
            "left outer join "+\
            "(select division_id,sum(boxes1) boxes1,sum(boxes2) boxes2 from "+\
                "(select coalesce((select division_id from company_company where id=company_id),0) division_id,"+\
                    "(select count(*) from sales_channelrebatedetails where rebate_batch_id=a1.id  and label_pkg=3 and product_id in "+product_ids+") boxes1,0 boxes2 "+\
                    "from sales_channelrebatebatch a1 "+sWhere_acc+" and confirmed=true and vtype_id=2 "+\
                    "and (select level_id from company_company where id=a1.company_id)=2 "+\
                    "union all "+\
                    "select coalesce((select division_id from company_company where id=company_id),0) division_id,"+\
                    "0 boxes1,(select count(*) from sales_channelrebatedetails where rebate_batch_id=a2.id and label_pkg=3 and product_id in "+product_ids+") boxes2 "+\
                    "from sales_channelrebatebatch a2  "+sWhere_acc+" and confirmed=true and vtype_id=1"+\
                    "and (select level_id from company_company where id=a2.company_id)=4"+\
                ") c "+\
                "group by division_id"+\
            ") g2 "+\
            "on a.id=g2.division_id "+\
            "left outer join "+\
            "(select t.division_id division_id,sum(t.boxes) boxes from "+\
                "(select coalesce((select division_id from company_company where id=company_to_id),0) division_id,"+\
                    "(select count(*) from inventory_stkdetails where batch_id=a.id) boxes from inventory_batch a "+\
                    sWhere_stk+\
                    " and (select is_expense_account from company_company where id=company_to_id)=false"+\
                ") t "+\
                "group by t.division_id"+\
            ") d "+\
            "on a.id=d.division_id "+\
            sWhere_saldiv+\
            "order by a.code" 
       
        cur.execute(s)
        for r in cur.fetchall():       
            if r[6]==0:
                rt=''
            else:
                rt="{:.2f}".format((float(r[4])+float(r[5]))*100.0/float(r[6]))
            d.append({
                "division_id":r[0],
                "division_name":r[1],
                "cur_lv1_boxes":r[2],
                "cur_lv2_boxes":r[3],
                "acc_lv1_boxes":r[4],
                "acc_lv2_boxes":r[5],
                "acc_delivery_boxes":r[6],
                "rate":rt,
            }) 
        data['divisions'].append({'category':cat_name,'data':d})     
    cur.close()    
    return data
 

@login_required
def saldiv_rebates(request):
    dt0='2020-11-01'    #最早发货时间
    dt=datetime.datetime.now().strftime('%Y-%m-%d')
    dt=request.GET.get('date',dt)
    return render(request,'sales/saldivision_rebates.html',{'dt':dt,'dt0':dt0})


#2020-12-21 修改/存款应付款
#@login_required
#def rebate_

def rebate_payable(closing_id,edit=0):
    cur=connection.cursor()
    s="select id,parent_name,agent_name,amount,amount,to_char(tm,'yyyy-mm-dd HH24:MI:SS') "+\
        "from sales_rebatepayable where rebateclosing_id="+id
    cur.execute(s)
    for r in cur.fetchall():
        pass

    #SELECT id, company_id, agent_name, parent_id, parent_name, level_id, 
    #   amount, dt, tm, tm_chceked, remark, agent_id, rebateclosing_id, 
    #   is_closed, log_edit, image_invoice  FROM logistics.sales_rebatepayable;
    return render(request,'sales/rebate_payable.html',{'edit':edit,'dt0':dt0})

'''
#2020-11-23
@login_required
def edit_period_rebate(request):
    data={'e':''}
    u=request.user
    c=u.company
    cur=connection.cursor()
    if request.method=='GET':
        clms=['ID','层级','经销商/分销商名称','应付款金额','实际付款金额']
        data=[]
        closing_id=request.GET.get('closing_id','')
        level_id=request.GET.get('level_id','')
        sLv=''
        if level_id:
            sLv=" and level_id="+level_id
        s="select id,agent_name,to_char(amount,'99999999.90'),"+\
            "(select name from company_level where id=level_id) where "+\
            "company_id="+str(c.id)+" and rebateclosing_id="+closing_id+" and amount>0"+sLv
        cur.execute(s)
        for r in cur.fetchall():
            data.append([ r[0],r[3],r[1],r[2],'' ])

'''

#2020-11-24 酒鬼酒鼠年纪念酒活动
@csrf_exempt
def promotionCode_labelCheckAndGet(request):
    """
    user=3837

    {status}:
        0: 初始状态
        1: 已领取
        2: 已转赠
        3: 已消耗
    {ecpid.remark}: 加密的码
    {item}:
        1: 担当鼠
        2: 励志鼠
        3: 相知鼠
        4: 结缘鼠
        5: 守望鼠
        6: 回眸鼠
    {operation}:
        1: 领取操作
        2: 转赠操作, code转为已转赠状态
        3: 兑换操作, code转为被消耗状态
        4: 仅扫码
        5: 非正常操作
    """

    ecpid = request.GET.get('ecpid', '')
    consumer_id = request.GET.get('uid', '')
    tm = datetime.datetime.now()

    if not consumer_id:
        return JsonResponse({'e': 'no consumer_id.'})
    try:
        user = ConsumerAccount.objects.get(id=consumer_id)
    except Exception as e:
        return JsonResponse({'e': 'invalid consumer_id.'})
    if not ecpid:
        return JsonResponse({'e': 'no ecpid.'})
    try:
        ecpid = PromotionCode.objects.get(remark=ecpid)
        id = ecpid.id
        cpid = ecpid.code
        item = ecpid.item
        status = ecpid.status
        if status == 0:
            #初始状态，领取
            p = PromotionCodeStatusRecord(code=ecpid,operation=1,uid=user,tm=tm)
            p.save()
            ecpid.status = 1
            ecpid.uid = user
            ecpid.save()
            return JsonResponse({'msg': '领取成功', 'item': item})
        elif status == 1:
            # 已领取，写一条仅查询的记录，返回已被领取
            p = PromotionCodeStatusRecord(code=ecpid, operation=4, uid=user, tm=tm)
            p.save()
            return JsonResponse({'msg': '已被领取'})
        elif status == 2:
            # 已转赠，领取
            p = PromotionCodeStatusRecord(code=ecpid, operation=1, uid=user, tm=tm)
            p.save()
            ecpid.status = 1
            ecpid.uid = user
            ecpid.save()
            p.save()
            return JsonResponse({'msg': '领取成功', 'item': item})
        else:
            # 已消耗，写一条仅查询的记录，返回已被消耗
            p = PromotionCodeStatusRecord(code=ecpid, operation=4, uid=user, tm=tm)
            p.save()
            return JsonResponse({'msg': '已被消耗'})
    except Exception as e:
        return HttpResponse(e)

@csrf_exempt
def promotionCode_getUserData(request):
    consumer_id = request.GET.get('uid', '')
    tm = datetime.datetime.now()
    if not consumer_id:
        return JsonResponse({'e': 'no consumer id'})
    try:
        user = ConsumerAccount.objects.get(id=consumer_id)
    except Exception as e:
        return JsonResponse({'e': 'not correct consumer id.'})

    cur = connection.cursor()

    list = [0,0,0,0,0,0]
    bonuslist = []
    redeemedlist = []

    sql = f"select item, count(*) from sales_promotioncode where uid_id={user.id} and status in (1,2) group by item"
    cur.execute(sql)
    r = cur.fetchall()
    for i in r:
        list[i[0]-1] = i[1]

    sql2 = f"select id, award_id, " \
           f"(select award_id from logistics.sales_activityawards where id=a.award_id) award_item_id, " \
           f"(select name from sales_award where id=(select award_id from sales_activityawards where id=a.award_id)), " \
           f"tm " \
           f"from sales_consumerwinner a " \
           f"where consumer_id={user.id} and tm_redeemed is null"
    cur.execute(sql2)
    t = cur.fetchall()

    #中奖id, 奖项id, 奖品id, 奖品名称, 时间
    for i in t:
        bonuslist.append({
            'id': i[0],
            'award_id': i[1],
            'award_item_id': i[2],
            'award_name': i[3],
            'tm': i[4]
        })
    sql3 = f"select id, award_id, " \
           f"(select award_id from logistics.sales_activityawards where id=a.award_id) award_item_id, " \
           f"(select name from sales_award where id=(select award_id from sales_activityawards where id=a.award_id)), " \
           f"tm, tm_redeemed " \
           f"from sales_consumerwinner a " \
           f"where consumer_id={user.id} and tm_redeemed is not null"
    cur.execute(sql3)
    d = cur.fetchall()

    #中奖id, 奖项id, 奖品id, 奖品名称, 时间
    for i in d:
        #print(i)
        redeemedlist.append({
            'id': i[0],
            'award_id': i[1],
            'award_item_id': i[2],
            'award_name': i[3],
            'tm': i[4],
            'tm_redeemed': i[5]
        })

    return JsonResponse({
        'cards':  list,
        'bonus': bonuslist,
        'redeemed': redeemedlist
    })

@csrf_exempt
def promotionCode_share(request):
    ecpid = request.POST.get('ecpid', '')
    consumer_id = request.POST.get('uid', '')
    tm = datetime.datetime.now()

    try:
        user = ConsumerAccount.objects.get(id=consumer_id)
    except Exception as e:
        return JsonResponse({'e': 'invalid consumer_id.'})
    if not ecpid:
        return JsonResponse({'e': 'no ecpid.'})

    ecpid = PromotionCode.objects.filter(remark=ecpid, uid=user).first()
    if ecpid:
        id = ecpid.id
        item = ecpid.item
        status = ecpid.status
        if status == 0:
            # 初始状态, 状态不正常，写入记录
            p = PromotionCodeStatusRecord(code=ecpid, operation=5, uid=user, tm=tm)
            p.save()
            return JsonResponse({'msg': '初始状态'})
        elif status == 1:
            # 已领取，转为转赠状态
            p = PromotionCodeStatusRecord(code=ecpid, operation=2, uid=user, tm=tm)
            p.save()
            ecpid.status = 2
            ecpid.uid = user
            ecpid.save()
            return JsonResponse({'msg': '转赠成功'})
        elif status == 2:
            # 已转赠，状态不正常，写入记录
            p = PromotionCodeStatusRecord(code=ecpid, operation=5, uid=user, tm=tm)
            p.save()
            return JsonResponse({'msg': '已转赠'})
        else:
            # 已消耗，状态不正常，写入记录
            p = PromotionCodeStatusRecord(code=ecpid, operation=5, uid=user, tm=tm)
            p.save()
            return JsonResponse({'msg': '已被消耗'})
    else:
        return JsonResponse({'msg': '转赠失败'})


@csrf_exempt
def promotionCode_compose20201214(request):
    """
    {plan}
     0: 3个卡片 兑换 三等奖 258mL红坛品鉴1瓶,
     1: 5个卡片 兑换 二等奖 500mL红坛品鉴1瓶,
     2: 6个不同卡片 兑换 一等奖 五彩酒鬼酒1瓶
    """
    consumer_id = request.POST.get('uid', '')
    activity = request.POST.get('activity', 18)
    company_id = request.POST.get('company', 1)
    plan = request.POST.get('plan', '')

    tm = datetime.datetime.now()

    try:
        user = ConsumerAccount.objects.get(id=consumer_id)
    except Exception as e:
        return JsonResponse({'e': 'invalid consumer_id.'})

    if not consumer_id or not plan:
        return JsonResponse({'e': 'lack of params'})

    cur = connection.cursor()

    if company_id == 1:
        plan = int(plan)
        card_consume_list = []
        sql0 = f"select item, count(*) from sales_promotioncode where uid_id={user.id} and status=1 group by item"
        cur.execute(sql0)
        r = cur.fetchall()
        list = []
        for i in r:
            list.append({
                i[0]: i[1]
            })
        # 有几种卡
        cat = len(list)

        if plan == 0:
            activityAward = 17
            qty = 1
            total = 0
            for j in list:
                j_key = sorted(j.keys())[0]
                amt = j[j_key]
                total = total + amt
            if total >= 3:
                limit = 3
                count = 0
                for k in list:
                    k_key = sorted(k.keys())[0]
                    amt = k[k_key]
                    for l in range(amt):
                        if count >= limit:
                            break
                        sql1 = f"select id, item from sales_promotioncode where uid_id={user.id} and status=1 and item={k_key} limit 1"
                        cur.execute(sql1)
                        r = cur.fetchone()
                        # 修改code的状态
                        ecpid = PromotionCode.objects.get(id=r[0])
                        ecpid.status = 3
                        ecpid.save()
                        # # 写消耗的记录
                        p = PromotionCodeStatusRecord(code=ecpid, operation=3, uid=user, tm=tm)
                        p.save()
                        card_consume_list.append({'id': r[0], 'item': r[1]})
                        count += 1
                # 写 ConsumerWinner
                c = ConsumerWinner(
                    company_id=1,
                    consumer_id=user.id,
                    activity_id=18,
                    award_id=activityAward,
                    qty=qty
                )
                c.save()
                        
            else:
                return JsonResponse({'e': 'insufficient cards', 'code': '15-1'})
        if plan == 1:
            activityAward = 16
            qty = 1
            total = 0
            for j in list:
                j_key = sorted(j.keys())[0]
                amt = j[j_key]
                total = total + amt
            if total >= 5:
                limit = 5
                count = 0
                for k in list:
                    k_key = sorted(k.keys())[0]
                    amt = k[k_key]
                    for l in range(amt):
                        if count >= limit:
                            break
                        sql1 = f"select id, item from sales_promotioncode where uid_id={user.id} and status=1 and item={k_key} limit 1"
                        cur.execute(sql1)
                        r = cur.fetchone()
                        # 修改code的状态
                        ecpid = PromotionCode.objects.get(id=r[0])
                        ecpid.status = 3
                        ecpid.save()
                        # # 写消耗的记录
                        p = PromotionCodeStatusRecord(code=ecpid, operation=3, uid=user, tm=tm)
                        p.save()
                        card_consume_list.append({'id': r[0], 'item': r[1]})
                        count += 1
                    # 写 ConsumerWinner
                c = ConsumerWinner(
                    company_id=1,
                    consumer_id=user.id,
                    activity_id=18,
                    award_id=activityAward,
                    qty=qty
                )
                c.save()
                        
            else:
                return JsonResponse({'e': 'insufficient cards', 'code': '15-2'})

        if plan == 2:
            activityAward = 15
            qty = 1
            if cat >= 6:
                limit = 6
                count = 0
                for j in list:
                    j_key = sorted(j.keys())[0]
                    amt = j[j_key]
                    # 修改 code 的状态为被消耗
                    sql1 = f"select id, item from sales_promotioncode where uid_id={user.id} and status=1 and item={j_key} limit 1"
                    cur.execute(sql1)
                    r = cur.fetchall()
                    for k in r:
                        # 修改code的状态
                        ecpid = PromotionCode.objects.get(id=k[0])
                        ecpid.status = 3
                        ecpid.save()
                        # 写消耗的记录
                        p = PromotionCodeStatusRecord(code=ecpid, operation=3, uid=user, tm=tm)
                        p.save()
                        card_consume_list.append({'id': k[0], 'item': k[1]})
                    count += 1
                    if count >= limit:
                        break
                # 写 ConsumerWinner
                c = ConsumerWinner(
                    company_id=1,
                    consumer_id=user.id,
                    activity_id=18,
                    award_id=activityAward,
                    qty=qty
                )
                c.save()
            else:
                return JsonResponse({'e': 'insufficient cards', 'code': '15-3'})
        return JsonResponse({'card_consumed': card_consume_list, 'plan': plan})
    return JsonResponse({'msg': ''})

@csrf_exempt
def promotionCode_compose(request):
    """
    {plan}
     0: 3个卡片 兑换 三等奖 258mL红坛品鉴1瓶,
     1: 5个卡片 兑换 二等奖 500mL红坛品鉴1瓶,
     2: 6个不同卡片 兑换 一等奖 五彩酒鬼酒1瓶
    """
    consumer_id = request.POST.get('uid', '')
    activity = request.POST.get('activity', 18)
    company_id = request.POST.get('company', 1)
    plan = request.POST.get('plan', '')
    cardsUse = request.POST.get('cardsUse', [])
    tm = datetime.datetime.now()

    try:
        user = ConsumerAccount.objects.get(id=consumer_id)
    except Exception as e:
        return JsonResponse({'e': 'invalid consumer_id.'})

    if not consumer_id or not plan or not cardsUse:
        return JsonResponse({'e': 'lack of params'})

    cur = connection.cursor()

    if company_id == 1:
        plan = int(plan)
        card_consume_list = []
        sql0 = f"select item, count(*) from sales_promotioncode where uid_id={user.id} and status in (1,2) group by item"
        cur.execute(sql0)
        r = cur.fetchall()
        list = []
        listArray = [0,0,0,0,0,0]
        cardsUseArray = cardsUse.split(',')
        for i, v in enumerate(cardsUseArray):
            cardsUseArray[i] = int(v)
        for i in r:
            list.append({
                i[0]: i[1]
            })
            listArray[i[0]-1] = i[1]
        # 有几种卡
        cat = len(list)

        if plan == 0:
            activityAward = 17
            qty = 1
            total = 0
            limit = 3
            cardsUseSum = 0
            for i in cardsUseArray:
                cardsUseSum += i
            if cardsUseSum != limit:
                return JsonResponse({'e': 'limit not match', 'code': '16-1'})

            sufficientCards = True
            for i,v in enumerate(listArray):
                if cardsUseArray[i] > listArray[i]:
                    sufficientCards = False
                    break
            if sufficientCards == False:
                return JsonResponse({'e': 'insufficient cards', 'code': '15-1'})
            else:
                for j,jv in enumerate(cardsUseArray):
                    if jv == 0:
                        continue
                    sql = f"select id, item from sales_promotioncode where uid_id={user.id} and status in (1,2) and item={j+1} limit {jv}"
                    cur.execute(sql)
                    rs = cur.fetchall()
                    for r in rs:
                        # 修改code的状态
                        pc = PromotionCode.objects.get(id=r[0])
                        pc.status = 3
                        pc.save()
                        # 写消耗的记录
                        p = PromotionCodeStatusRecord(code=pc, operation=3, uid=user, tm=tm)
                        p.save()
                        card_consume_list.append({'id': r[0], 'item': r[1]})
                    #写 ConsumerWinner
                c = ConsumerWinner(
                    company_id=1,
                    consumer_id=user.id,
                    activity_id=18,
                    award_id=activityAward,
                    qty=qty
                )
                c.save()
        if plan == 1:
            activityAward = 16
            qty = 1
            total = 0
            limit = 5
            cardsUseSum = 0
            for i in cardsUseArray:
                cardsUseSum += i
            if cardsUseSum != limit:
                return JsonResponse({'e': 'limit not match', 'code': '16-1'})
            sufficientCards = True
            for i,v in enumerate(listArray):
                if cardsUseArray[i] > listArray[i]:
                    sufficientCards = False
                    break
            if sufficientCards == False:
                return JsonResponse({'e': 'insufficient cards', 'code': '15-1'})
            else:
                for j,jv in enumerate(cardsUseArray):
                    if jv == 0:
                        continue
                    sql = f"select id, item from sales_promotioncode where uid_id={user.id} and status in (1,2) and item={j+1} limit {jv}"
                    cur.execute(sql)
                    rs = cur.fetchall()

                    for r in rs:
                        # 修改code的状态
                        pc = PromotionCode.objects.get(id=r[0])
                        pc.status = 3
                        pc.save()
                        # 写消耗的记录
                        p = PromotionCodeStatusRecord(code=pc, operation=3, uid=user, tm=tm)
                        p.save()
                        card_consume_list.append({'id': r[0], 'item': r[1]})
                    #写 ConsumerWinner
                c = ConsumerWinner(
                    company_id=1,
                    consumer_id=user.id,
                    activity_id=18,
                    award_id=activityAward,
                    qty=qty
                )
                c.save()
        if plan == 2:
            activityAward = 15
            qty = 1
            if cat >= 6:
                limit = 6
                count = 0
                for j in list:
                    j_key = sorted(j.keys())[0]
                    amt = j[j_key]
                    # 修改 code 的状态为被消耗
                    sql1 = f"select id, item from sales_promotioncode where uid_id={user.id} and status in (1,2) and item={j_key} limit 1"
                    cur.execute(sql1)
                    r = cur.fetchall()
                    for k in r:
                        # 修改code的状态
                        ecpid = PromotionCode.objects.get(id=k[0])
                        ecpid.status = 3
                        ecpid.save()
                        # 写消耗的记录
                        p = PromotionCodeStatusRecord(code=ecpid, operation=3, uid=user, tm=tm)
                        p.save()
                        card_consume_list.append({'id': k[0], 'item': k[1]})
                        count += 1
                    if count >= limit:
                        break
                # 写 ConsumerWinner
                c = ConsumerWinner(
                    company_id=1,
                    consumer_id=user.id,
                    activity_id=18,
                    award_id=activityAward,
                    qty=qty
                )
                c.save()
            else:
                return JsonResponse({'e': 'insufficient cards', 'code': '15-3'})
        return JsonResponse({'card_consumed': card_consume_list, 'plan': plan})
    return JsonResponse({'msg': 'ok'})




@csrf_exempt
def promotionCode_redeem(request):
    consumer_id = request.POST.get('uid', '')
    winner_id = request.POST.get('winner_id', '')

    name = request.POST.get('name', '')
    province = request.POST.get('province', '')
    phone =request.POST.get('phone', '')
    city = request.POST.get('city', '')
    county = request.POST.get('county', '')
    address = request.POST.get('address', '')
    if not consumer_id or not winner_id or not name or not province or not phone or not city or not county or not address:
        return JsonResponse({'e': 'lack of params', 'code': '16'})

    activity = 18
    company_id = 1

    tm = datetime.datetime.now()

    userlist = ConsumerAccount.objects.filter(id=consumer_id)

    if not userlist.exists():
        return JsonResponse({'e': 'invalid consumer_id.'})
    p = ConsumerWinner.objects.filter(id=winner_id).first()
    if p:
        k = ConsumerRedeem.objects.filter(winner_id=winner_id)
        if k.exists():
            return JsonResponse({'e': '已兑奖'})
        else:
            p.tm_redeemed = tm
            p.save()
            redeem = ConsumerRedeem(
                winner=p,
                province=province,
                phone=phone,
                city=city,
                county=county,
                address=address,
                remark=name
            )
            redeem.save()
            return JsonResponse({'msg': 'ok'})
    else:
        return JsonResponse({'e': '不存在该奖品的合成记录', 'code': '17'})

@csrf_exempt
def promotionCode_getRedeemStatus(request):
    consumerwinner_id = request.GET.get('id','')

    if not consumerwinner_id:
        return JsonResponse({'e': 'lack of parameters'})
    try:
        c = ConsumerRedeem.objects.get(winner=ConsumerWinner.objects.get(id=consumerwinner_id))
        return JsonResponse({'id': c.id, 'check': c.checked_tm, 
                             'send':c.tm_send, 'delivery': c.waybill_number, 'e': ''})
    except Exception as e:
        return JsonResponse({'e': '异常'})

@csrf_exempt
def promotionCode_setRedeemStatus(request):
    consumerwinner_id = request.POST.get('id','')
    operation = request.POST.get('operation', '')
    tm = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")

    if not consumerwinner_id or not operation:
        return JsonResponse({'e': 'lack of parameters'})

    if operation == '1': # 通过审核
        try:
            c = ConsumerRedeem.objects.get(winner=ConsumerWinner.objects.get(id=consumerwinner_id))
            c.checked_tm = tm
            c.save()
            return JsonResponse({'msg': 'success'})
        except Exception as e:
            return JsonResponse({'e': '异常'})
    elif operation == '2':  #发货
        n = request.POST.get('number', '')
        if not n:
            return JsonResponse({'e': 'lack of parameters'})
        try:
            c = ConsumerRedeem.objects.get(winner=ConsumerWinner.objects.get(id=consumerwinner_id))
            c.tm_send = tm
            c.waybill_number = n
            c.save()
            return JsonResponse({'msg': 'success'})
        except Exception as e:
            return JsonResponse({'e': '异常'})
    else:
        return JsonResponse({'e': '异常 3'})

@csrf_exempt
def promotionCode_getShareCard(request):
    consumer_id = request.POST.get('uid', '')
    item = request.POST.get('item', '')
    if not item or not consumer_id:
        return JsonResponse({'e': 'lack of params.'})
    sql = f"select remark from sales_promotioncode where status=1 and uid_id={consumer_id} and item={item}"
    cur = connection.cursor()
    cur.execute(sql)
    r = cur.fetchone()
    if r:
        return JsonResponse({'data': r[0]})
    else:
        return JsonResponse({'data': ''})

        
def status_monitor(request):
    cur = connection.cursor()

    # 酒鬼庚子年纪念集卡活动: activity_id=18
    sql = "select count(*) from sales_promotioncode where company_id=1 and status!=0"
    cur.execute(sql)
    r = cur.fetchone()
    activatedNumber = r[0]
    sql2 = "select count(*) from sales_consumerwinner where activity_id=18"
    cur.execute(sql2)
    r = cur.fetchone()
    winnerNumber = r[0]
    sql3 = "select count(*) from sales_consumerwinner where activity_id=18 and tm_redeemed is not null"
    cur.execute(sql3)
    r = cur.fetchone()
    redeemedNumber = r[0]
    sql4 = "select * from sales_promotioncodestatusrecord order by id desc limit 10"
    cur.execute(sql4)
    r = cur.fetchall()
    recentOperation = []
    for i in r:
        i = list(i)
        if i[1] == 0: i[1] = "初始"
        if i[1] == 1: i[1] = "领取"
        if i[1] == 2: i[1] = "转赠"
        if i[1] == 3: i[1] = "兑换"
        if i[1] == 4: i[1] = "仅查询"
        if i[1] == 5: i[1] = "异常"
        i[2] = i[2].strftime('%m-%d %H:%M')
        recentOperation.append(i)
    sql5 = "select item,count(*) from sales_promotioncode where status!=0 group by item"
    cur.execute(sql5)
    r = cur.fetchall()
    categoryList = []
    for i in r:
        categoryList.append({
            "item": i[0],
            "itemName": "",
            "count": i[1]
        })
    for i in categoryList:
        if i['item'] == 1: i['itemName'] = "担当鼠"
        if i['item'] == 2: i['itemName'] = "励志鼠"
        if i['item'] == 3: i['itemName'] = "相知鼠"
        if i['item'] == 4: i['itemName'] = "结缘鼠"
        if i['item'] == 5: i['itemName'] = "守望鼠"
        if i['item'] == 6: i['itemName'] = "回眸鼠"
    activatedRatio = activatedNumber / 600
    
    sql6 = "select count(*) from sales_promotioncode where array_length(items,1)=2 and status != 0"
    cur.execute(sql6)
    problemCards = cur.fetchone()[0]

    data = {
        'activatedNumber': activatedNumber,
        'activatedRatio': round(activatedRatio,4),
        'winnerNumber': winnerNumber,
        'redeemedNumber': redeemedNumber,
        "recentOperation": recentOperation,
        "categoryList": categoryList,
        "problemCards": problemCards
    }
    return render(request, 'sales/status_monitor.html', data)


#Jonathan
#2020-12-15

@login_required
def bujiang_labels(request):
    data = {'data': [], 'clmns': [], 'e': [],
            'subtt': ['营销活动', '手动步奖'], 'murl': '', 'dt': False, 'add': True}
    data['clmns'] = ['标签序号', '奖品名称', '领取状态', '是否可用', '备注', '时间']
    data['show_add'] = True
    data['murl'] = '/sales/bujiang/'
    for label in BuJiang_Labels.objects.filter(company_id=request.user.company.id):
        flag = '否'
        is_get = '未领取'
        if label.is_active:
            flag = '是'
        if label.is_get:
            is_get = '已领取'
        data['data'].append([
            "<a href='/sales/bujiang/" +
            str(label.id) + "/'>" + label.label_code + "</a>",
            str(label.award.name),
            is_get,
            flag,
            label.remark,
            str(label.tm)
        ])
    return render(request, 'sales/bujiang_labels.html', data)



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


class DecimalEncoder(json.JSONEncoder):
    def default(self, obj):
        if isinstance(obj, decimal.Decimal):
            return float(obj)
        elif isinstance(obj, datetime):
            return obj.strftime('%Y-%m-%d %H:%M:%S')
        elif isinstance(obj, date):
            return obj.strftime('%Y-%m-%d')
        return json.JSONEncoder.default(self, obj)
        # super(DecimalEncoder, self).default(obj)


# Jonathan
# 奖池布奖
@login_required
def lotteryseeds(request):
    data = {'data': [], 'clmns': [], 'e': [],
            'subtt': ['营销活动', '奖池布奖'], 'murl': '', 'dt': True, 'add': True}
    data['clmns'] = ['标签序号', '活动', '奖品', '产品','状态', '备注', '时间','操作']
    data['show_add'] = True
    data['murl'] = '/sales/lottery/'
    dt = datetime.datetime.now().strftime('%Y-%m-%d')
    dt_fr = request.GET.get('date_from', dt)
    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 label_code,(SELECT name from sales_activities WHERE id = activity_id),"+
        "(SELECT name from sales_activityawards WHERE id = a.award_id),"+
        "(SELECT name from product_product WHERE id = product_id),"+
        "(SELECT tm_redeemed from sales_consumerwinner WHERE label_code = a.label_code limit 1),"+
        "remark,tm,id "+
        "FROM sales_lotteryseeds a where company_id = "+ str(request.user.company.id)+" and tm>='"+dt_fr+"' and tm <='"+dt_to+"'")
    rs = cur.fetchall()
    for r in rs:
        is_get = '未领取'
        if r[4]:
            is_get = '已领取'
         
        data['data'].append([
            r[0],
            r[1],
            r[2],
            r[3],
            is_get, 
            r[5],
            r[6].strftime("%Y-%m-%d %H:%M:%S"),
            "<button class='btn btn-danger btn-xs' onclick='deleteItem(" + str(r[7]) + ")'>删除</button>"
        ])
    return render(request, 'sales/lotteryseeds.html', data)

# 布奖删除
@login_required
def lotteryseeds_delete(request,id=None):  
    if id :
        obj = LotterySeeds.objects.get(id=int(id), company_id=request.user.company.id)
        obj.delete()

    return redirect('/sales/lotteryseeds/') 

# 布奖页面
@login_required
def lottery(request, id=None): 

    data = {
        'activites':[]
    }

    activites = Activities.objects.filter(company_id=request.user.company.id)
    if activites:
        for a in activites:
            data['activites'].append({
                'name':a.name,
                'id':a.id
            })

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

# 查询活动中的奖品
@tj_login_required
@csrf_exempt
def activity_awards(request):
    data={
        'e':'', 
        'awards':[]
    }  
    activity_id = request.GET.get('activity_id','')
    if activity_id:
        aws = ActivityAwards.objects.filter(activity_id=int(activity_id))
        if aws:
            for aw in aws:
                data['awards'].append({
                    'id':aw.id,
                    'name':aw.name
                }) 
    else:
        data['e'] = '数据丢失'
    return JsonResponse(data)  

# 进行布奖
@tj_login_required
@csrf_exempt
def activity_lottery(request): 
    data={
        'e':'' 
    }  
    activity_id = request.POST.get('activity_id','')
    batch_id = request.POST.get('batch_id','')
    awards_ = request.POST.get('awards','')
    remark = request.POST.get('remark','')
    company_id = request.user.company.id




    if activity_id and batch_id and awards_:
        awards = json.loads(awards_)


        cur = connection.cursor() 
        cur.execute("select product_id from inventory_batch where id = "+batch_id +"")
        r = cur.fetchone() 
        product_id = r[0]
        cur.execute("select code,id,package_id from label.labelinfo where parent_id in (SELECT label_id from inventory_stkdetails WHERE batch_id = "+batch_id+") and package_id=1");
        rs = cur.fetchall()  
        shuffle(rs)      
        i = 0
        for r in awards:
            award_id = r['award_id']
            num = r['num'] 
            for n in range(0,num):  
                s = LotterySeeds(
                    company_id=company_id,
                    activity_id=activity_id,
                    award_id=award_id,
                    label_code=rs[i][0],
                    label_pkg=rs[i][2],
                    label_id=rs[i][1],
                    product_id=product_id,
                    remark=remark
                )
                s.save()
                i = i+1



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


@login_required
def list_set_config(request, what):
    d = {
        'productputon': ['产品上架', 'sales', 'Productputon'],
        'member': ['会员设置', 'sales', 'Member'],
    }
    data = []
    clmns = []
    subtt = [_('会员管理'), _(d[what][0])]
    m = apps.get_model(d[what][1], d[what][2])
    cid = request.user.company.id
    edit_perm = False
    is_saved = False
    dt = False
    cur = connection.cursor()
    murl = '/list/edit/' + what + '/'
    if what == 'productputon':
        clmns = [_('产品名称'), _('产品价格'), _('产品图片'), _('首页显示'),
                 _('推荐'), _('热销'), _('备注'), _('操作')]

        s = "select name, price,photo, case is_active when true then '是' else '' end," + \
            "case is_push when true then'是' else '' end,case is_sale when true then '是' else '' end," + \
            "remark,id from sales_productputon where company_id=" + str(cid)
        cur.execute(s)
        for r in cur.fetchall():
            if edit_perm:
                name = "<a href='/product/edit/upload/" + str(r[7]) + "'>" + r[0] + "</a>"
            else:
                name = r[0]
            img = ''
            if r[2]:
                img = "<img src='/media/" + r[2] + "' style='height:30px;cursor:pointer;' tt='" + r[0] + "'></img>"
            data.append([name, r[1], img, str(r[3]), str(r[4]), str(r[5]), r[6], '下架'])
    elif what == 'member':
        clmns = [_('姓名'), _('性别'), _('生日'),
                 _('地址'), _('联系方式'), _('备注')]
        s = "select name, case  gender when 0 then '未知' when 1 then '男' when 2 then '女' end ,birthday, coalesce (address,''),phone,coalesce(remark,''),id from sales_member where company_id=" + str(cid) +" and grade_id = 1"
        cur.execute(s)
        for r in cur.fetchall():
            if edit_perm:
                name = "<a href='/product/edit/upload/" + str(r[6]) + "'>" + r[0] + "</a>"
            else:
                name = r[0]
            # img = ''
            # if r[2]:
            #     img = "<img src='/media/" + r[2] + "' style='height:30px;cursor:pointer;' tt='" + r[0] + "'></img>"
            rena = '<a href="/list/edit/member/' + str(r[6]) + '/" >' + str(_("修改")) + '</a>'
            data.append([name, r[1], str(r[2]), str(r[3]), str(r[4]), r[5]])
    return render(request, 'obj/list_obj.html',
                  {'clmns': clmns,
                   'data': json.dumps(data, cls=DecimalEncoder),
                   'subtt': subtt,
                   'edit_perm': edit_perm,
                   'murl': murl,
                   'show_add': False})


@login_required
def list_edit(request, what, id=None):
    d = {
        'productputon': ['产品上架', 'sales', 'Productputon'],
        'member': ['会员设置', 'sales', 'Member'],
    }
    m = apps.get_model(d[what][1], d[what][2])
    is_saved = False
    if what == 'productputon':
        if id:
            obj = m.objects.get(id=int(id))
        else:
            obj = m(company=request.user.company, user=request.user)
        if request.method == 'GET':
            form = ProductPutonForm(instance=obj)
        else:
            form = ProductPutonForm(request.POST, request.FILES, instance=obj)
            if form.is_valid():
                form.save()
                return redirect('/list/set/productputon/')
                is_saved = True
        return render(request, 'product/product_upload.html',
                      {'form': form, 'is_saved': is_saved, "id": id})
    elif what == 'member':
        if id:
            obj = m.objects.get(id=int(id))
        else:
            obj = m(company=request.user.company)
        if request.method == 'GET':
            form = MemberForm(instance=obj)
        else:
            form = MemberForm(request.POST, instance=obj)
            if form.is_valid():
                form.save()
                return redirect('/list/set/member/')
                is_saved = True
        return render(request, 'sales/member/member_list.html',
                      {'form': form, 'is_saved': is_saved, "id": id})


@login_required
@csrf_exempt
def sms_send(request):
    clmns = [_('姓名'), _('性别'), _('生日'),
             _('地址'), _('联系方式'), _('备注'), _('操作')]
    data = []
    lev = []
    cid = request.user.company.id
    cur = connection.cursor()
    edit_perm = False
    is_saved = False
    dt = False
    if request.method == 'GET':
        s = "select name, gender, birthday, address, phone, remark, id from sales_member where company_id=" + str(
            cid)
        cur.execute(s)
        for r in cur.fetchall():
            if edit_perm:
                name = "<a href='/product/edit/upload/" + str(r[6]) + "'>" + r[0] + "</a>"
            else:
                name = r[0]
            send = '<button class="btn-send" value="' + str(r[4]) + '" >发送短信'
            data.append([name, r[1], str(r[2]), str(r[3]), str(r[4]), r[5], send])
        cur.execute("select level,name from sales_memberlevel where company_id =" + str(cid))
        for l in cur.fetchall():
            le = {
                'name': l[1],
                'id': l[0]
            }
            lev.append(le)
        return render(request, 'sales/member/SMS_send.html',
                      {'clmns': clmns,
                       'data': json.dumps(data),
                       'edit_perm': edit_perm,
                       'lev': lev,
                       })
    else:
        r = json.loads(request.body)
        e = ''
        if not r['msg']:
            e = '请输入短信内容！'
            return JsonResponse({'data': json.dumps(data),
                                 'e': json.dumps(e, ensure_ascii=False)})
        if r['gs'] == 'one':
            mobile = r['phone']
            text = r['msg'] 
            if cid == 19257:
                send_sms(mobile,text,"茗酒荟")
            else:
                send_sms(mobile, text)
            tm = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            dt = datetime.date.today().strftime("%Y-%m-%d")
            cur.execute("insert into sales_sms (messages,phone,company_id,dt,tm) values " + \
                        "('" + text + "','" + mobile + "','" + str(cid) + "','" + dt + "','" + tm + "')")
            return JsonResponse({'data': json.dumps(data)})
        elif r['gs'] == 'more':
            if r['lev'] == '0':
                e = '请选择会员等级！'
                return JsonResponse({'data': json.dumps(data),
                                     'e': json.dumps(e, ensure_ascii=False)})
            s = 'SELECT phone from sales_member where member_level_id = ' + \
                '(select id from sales_memberlevel WHERE level  = ' + str(r['lev']) + \
                ' and company_id = ' + str(cid) + ') GROUP BY phone'
            cur.execute(s)
            phone = cur.fetchall()
            if not phone:
                e = '暂无该等级会员！'
                return JsonResponse({'data': json.dumps(data),
                                     'e': json.dumps(e, ensure_ascii=False)})
            for p in phone: 
                if cid == 19257:
                    send_sms(p[0],r['msg'],"茗酒荟")
                else:
                    send_sms(p[0], r['msg'])
                tm = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
                dt = datetime.date.today().strftime("%Y-%m-%d")
                cur.execute(
                    "insert into sales_sms (messages,phone,company_id,dt,tm) values ('" + r['msg'] + "','" + p[
                        0] + "','" +
                    str(cid) + "','" + dt + "','" + tm + "')")

            return JsonResponse({'data': json.dumps(data)}) 


@login_required
def member_level_conf(request):
    clmns = [_('等级'), _('名称'), _('图片'), _('积分'), _('备注'), _('编辑')]
    datas = []
    cid = request.user.company.id
    cur = connection.cursor()
    murl = '/member/level/save/'
    if request.method == 'GET':
        s = 'select  level,name,photo,integral,remark,id from sales_memberlevel where company_id=' + str(cid)
        cur.execute(s)
        for r in cur.fetchall():
            img = ''
            if r[5]:
                img = "<img src='/media/" + r[2] + "' style='height:30px;cursor:pointer;' tt='" + str(r[0]) + "'></img>"
            rena = '<a href="/member/level/details/' + str(r[5]) + '/" >' + str(_("修改")) + '</a>'
            datas.append([r[0], r[1], img, r[3], r[4], rena])
        return render(request, 'sales/member/member_level.html',
                      {'datas': json.dumps(datas),
                       'clmns': clmns,
                       "add": True,
                       'murl': murl, })


@csrf_exempt
@login_required
def member_level_details(request, id):
    data = []
    cid = request.user.company.id
    cur = connection.cursor()
    return JsonResponse(
        {'data': json.dumps(data),
         'id': id,
         })


@csrf_exempt
def member_register(request):
    data = {'e': ''}
    cur = connection.cursor()
    vid = request.POST.get('vid')
    phone = request.POST.get('phone')
    cid = request.POST.get('cid')
    cur.execute("SELECT level,id FROM sales_memberlevel where company_id = '" + str(cid) + "' ORDER BY level")
    r = cur.fetchone()
    lev_id = r[1]

    cur.execute("select phone from sales_member where company_id=" + str(cid) + " and phone ='" + str(phone) + "'")
    r = cur.fetchone()
    if r:
        data['e'] = '该账号已注册'
        return JsonResponse(data)
    else:

        s = "insert into sales_member (name,gender,birthday,province,city,phone,exps,address," \
            "company_id,member_level_id,consumer_id)(select" + \
            " a.wx_nickname,a.gender,a.birthday,a.province,a.city," + str(phone) + ",0,a.address," + str(
            cid) + "," + str(lev_id) + "," + str(vid) + " from" + \
            " (SELECT wx_nickname,gender,birthday,province,city,phone,address FROM sales_consumeraccount" + \
            " where id ='" + str(vid) + "') A)"
        cur.execute(s)
        data['e'] = '注册成功'
        return JsonResponse(data)
    cur.close()
    return JsonResponse(data)


@csrf_exempt
def member_points(request):
    data = {}
    cur = connection.cursor()
    vid = request.POST.get('vid')
    cid = request.POST.get('cid')
    points = request.POST.get('points')
    s = "select id,exps from sales_member where consumer_id = " + str(vid) + " and company_id = " + str(cid)
    cur.execute(s)
    po = cur.fetchone()
    new_points = po[1] + int(points)
    up = "update sales_member set exps = " + str(new_points) + " where company_id = " + str(cid) + " and id = " + str(
        po[0])
    cur.execute(up)
    return JsonResponse(data)


@csrf_exempt
@login_required
def member_level_save(request, id=None):
    data = []
    cid = request.user.company.id
    cur = connection.cursor()
    m = apps.get_model('sales', 'MemberLevel')
    if id:
        obj = m.objects.get(id=int(id))
    else:
        obj = m(company=request.user.company, )
    if request.method == 'GET':
        form = MemberLevelForm(instance=obj)

    else:
        form = MemberLevelForm(request.POST, request.FILES, instance=obj)
        if form.is_valid():
            form.save()
            return redirect('/obj/set/memberlevel/')
    return render(request, 'sales/member/member_level_details.html',
                  {
                      "form": form,
                  })



@csrf_exempt
def birthday_messages(request):
    data={'e':''}
    cur = connection.cursor()
    cid = request.POST.get('cid')
    cur.execute("select name from company_company where id = "+str(cid))
    r = cur.fetchone()
    name = r[0]
    dtnow = datetime.datetime.now().strftime("%m-%d")
    s= "select name,birthday,phone from sales_member where to_char(birthday,'MM-DD') = '"+str(dtnow)+\
       "' and company_id = "+str(cid)
    cur.execute(s)
    members = cur.fetchall()
    for mem in members:
        text = '尊敬的'+str(mem[0])+'今天是'+str(datetime.datetime.now().strftime(" %Y-%m-%d "))+'日，'+str(name)+'祝您生日快乐！'
        # send_sms(text,mem[2])

    cur.execute("select name,dt,customize from sales_holiday where to_char(dt,'MM-DD')='"+str(dtnow)+"' and (company_id="+str(cid)+"or company_id is null)")
    holidays = cur.fetchall()

    cur.execute("select name,phone from sales_member where (company_id="+str(cid)+"or company_id is null)")
    phones = cur.fetchall()

    for holiday in holidays:
        for phone in phones:
            if holiday[2]:
                text = '尊敬的' + str(phone[0]) + '今天是' + str(datetime.datetime.now().strftime(" %Y-%m-%d ")) + '日，' + str(
                    name) + '祝您于我司'+str(holiday[0])+'快乐！'
            else:
                text = '尊敬的' + str(phone[0]) + '今天是' + str(datetime.datetime.now().strftime(" %Y-%m-%d ")) + '日，' + str(
                    name) + '祝您'+str(holiday[0])+'快乐！'
            send_sms(text,phone[1])
    return JsonResponse(data)


@csrf_exempt
@login_required
def holiday_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'] = [_('节日名称'),_('日期'),_('备注')]
    cur=connection.cursor()
    s = "select name,to_char(dt,'yyyy-mm-dd'),coalesce(remark,''),id from sales_holiday where company_id = "+str(cid)+"or company_id is null"
    cur.execute(s)
    r= cur.fetchall()
    for i in r :
        data['data'].append([i[0],i[1],i[2]])
    #WanNianRiLi('2021')
    return render(request, 'sales/member/holiday_list.html',data)


@csrf_exempt
@login_required
def holiday_edit(request,id=None):
    date={'e':''}
    m=apps.get_model('sales', 'Holiday')
    if id:
        obj = m.objects.get(id=int(id))
    else:
        obj = m(company=request.user.company,)
    is_saved = False
    if request.method == 'GET':
        form = HolidayForm(instance=obj)
    elif request.method == "POST":
        form = HolidayForm(request.POST, instance=obj)
        if form.is_valid():
            form.save()
            return redirect('/holiday/list/')
            is_saved = True

    return render(request,'sales/member/birthday_messages.html',
                  {
                   "form":form,
                   })


class WanNianRiLi(object):
    def __init__(self,year):
        self.year = year
        data = self.parseHTML()
        self.exportData(data)

    def parseHTML(self):
        url = 'https://wannianrili.51240.com/ajax/'
        s = requests.session()
        headers = {
            'Host':'wannianrili.51240.com',
            'Connection':'keep-alive',
            'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/81.0.4044.138 Safari/537.36',
            'Accept':'*/*',
            'Sec-Fetch-Site':'same-origin',
            'Sec-Fetch-Mode': 'cors',
            'Sec-Fetch-Dest': 'empty',
            'Referer': 'https://wannianrili.51240.com/',
            'Accept-Encoding': 'gzip, deflate, br',
            'Accept-Language': 'zh-CN,zh;q=0.9,en;q=0.8',
        }
        result = []
        # 生成月份列表
        dateList = [self.year + '-' + '%02d' % i for i in range(1, 13)]
        for year_month in dateList:
            s = requests.session()
            url = 'https://wannianrili.51240.com/ajax/'
            payload = {'q': year_month}
            response = s.get(url, headers=headers, params=payload)
            element = etree.HTML(response.text)
            html = element.xpath('//div[@class="wnrl_riqi"]')
            for _element in html:
                # 获取节点属性
                item = _element.xpath("./a/span")[1].attrib
                if _element.xpath("./a[@class='wnrl_riqi_xiu']"):
                    if item['class'] == 'wnrl_td_bzl wnrl_td_bzl_hong' or item['class'] == 'wnrl_td_bzl wnrl_td_bzl_lv':
                        tag = '节日'
                        _span = _element.xpath('.//text()')

                        result.append({'Date': year_month + '-' + _span[0], 'Holiday': _span[1], 'Tag': tag})
        return result



    def exportData(self, data):
        cur = connection.cursor()
        for i in data:
            s = " insert into sales_holiday (name,dt,customize) values('"+str(i['Holiday'])+"','"+ str(i['Date'])+"',false)"
            cur.execute(s)
        cur.close()



# 通过终端店查询关联的业务员
def search_salesman_by_agent(agent):
    user = None
    if agent:
        cur = connection.cursor()

        user_id = ''
        cur.execute("select user_id from company_staffsaldiv where ARRAY["+str(agent.id)+"] <@ agent_ids ")
        r= cur.fetchone()
        if r:
            if r[0]:
                user_id = r[0]
        if not user_id:
            #分区ID
            sub_division_id = str(agent.sub_division_id)
            #战区ID
            division_id = str(agent.division_id)
            if agent.sub_division_id:
                cur.execute(
                    "select user_id from company_staffsaldiv where ARRAY[" + sub_division_id + "] <@ subsaldiv_ids ")
                r = cur.fetchone()
                if r:
                    user_id = r[0]
            if agent.division_id:
                cur.execute("select user_id from company_staffsaldiv where ARRAY["+division_id+"] <@ saldiv_ids " )
                r = cur.fetchone()
                if r:
                    user_id = r[0]

        if user_id:
            u = User.objects.filter(id=int(user_id))
            if u:
                user = u[0]



    return  user

# 核销接口
@csrf_exempt
def do_hexiao(request):
    def decrypt_11(code):  
        c=''
        if len(code)==11:  
            s=code.lower()[:10]    
            code=s[9]+s[2]+s[1]+s[3:9]+s[0]        
            c=str(int('0x'+code,16))
        return c
    data = {
        'e': ''
    }
    winner_id = request.GET.get('winner_id', '')
    company_id = request.GET.get('company_id', '')
    agent_id = request.GET.get('agent_id', '')
    label_code = request.GET.get('label_code', '')
    remark = request.GET.get('remark', '')
    if company_id and agent_id:
        c = None
        if winner_id:
            winner_id = decrypt_p(winner_id)
            c1 = ConsumerWinner.objects.filter(id=int(winner_id), company_id=int(company_id))
            if c1:
                c = c1[0]
        elif label_code:
            if label_code.startswith('1_') or label_code.startswith('2_') or label_code.startswith('3_'):
                label_code = label_code[-12:]
            elif label_code.startswith('h'):
                label_code = decrypt_11(label_code[1:])
            
            need_search_ba = True

            if company_id == "19318":
                need_search_ba = False

            if need_search_ba: 
                #查询BA
                cur = connection.cursor()
                cur.execute("select code_a from label.lbl_abcode where code_b = '"+label_code+"' ")
                r= cur.fetchone()
                if r:
                    if r[0]:
                        label_code = r[0]

            c1 = ConsumerWinner.objects.filter(label_code=label_code, company_id=int(company_id))
            if c1:
                c = c1[0]
        if c:
            if c.tm_redeemed:
                if c.is_need_hexiao:
                    if not c.is_hexiao:

                        award = c.award.award
                        consumer = c.consumer
                        activity = c.activity
                        #获取奖品是否需要审核
                        is_need_permit = c.award.needcheck
                        agent = Company.objects.get(id=int(agent_id))
                        if agent:
                            money = 0
                            if award.amount:
                                money = award.amount
                            record = HeXiao_Record(
                                company_id=int(company_id),
                                award=award,
                                consumer=consumer,
                                activity=activity,
                                activity_name=activity.name,
                                agent=agent,
                                award_name=award.name,
                                consumer_name=consumer.name,
                                consumer_phone=consumer.phone,
                                agent_name=agent.name,
                                remark=remark,
                                is_permit=is_need_permit,
                                money=money
                            )

                            #如果是代金券
                            if award.awardcat_id == 6:
                                # 代金券不需要配送
                                record.is_need_dispatch = False
                                record.is_need_money_return = True
                                #如果不需要审核
                                if not is_need_permit:
                                    record.is_money_returned=True

                            #查询 User
                            user = search_salesman_by_agent(agent)
                            if user:
                                record.user = user
                                record.user_name = user.username
                                record.user_phone = user.phone

                            record.save()
                            c.is_hexiao = True
                            c.save()

                        else:
                            data['e'] = '终端信息不存在'
                    else:
                        data['e'] = '奖品已被核销'
                else:
                    data['e'] = '奖品不需要核销'
            else:
                data['e'] = '奖品未领取'
        else:
            data['e'] = '数据不存在'

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

#核销列表接口 
@csrf_exempt
def hexiao_record_list(request):
    data = {
        'e': '',
        'list':[]
    }
    company_id = request.GET.get('company_id', '')
    agent_id = request.GET.get('agent_id', '')
    user_id = request.GET.get('user_id', '')
    if company_id:
        records = None
        if agent_id:
            records = HeXiao_Record.objects.filter(company_id=int(company_id),agent_id=int(agent_id)).order_by('-id')
        elif user_id:
            records = HeXiao_Record.objects.filter(company_id=int(company_id),user_id=int(user_id),is_need_dispatch=True,is_permit=True).order_by('-id')

        if records:
            for record in records:
                agent_accepted_tm = record.agent_accepted_tm
                if agent_accepted_tm:
                    agent_accepted_tm = agent_accepted_tm.strftime("%Y-%m-%d %H:%M:%S")
                dispatched_tm = record.dispatched_tm
                if dispatched_tm:
                    dispatched_tm = dispatched_tm.strftime("%Y-%m-%d %H:%M:%S")
                data['list'].append({
                    'id':record.id,
                    'agent_id':record.agent.id,
                    'activity_name':record.activity_name,
                    'award_name':record.award.name,
                    'consumer_nickname':record.consumer.wx_nickname,
                    'consumer_headImg':record.consumer.wx_headimgurl,
                    'is_dispatched':record.is_dispatched,
                    'dispatched_tm':dispatched_tm,
                    'is_agent_accepted':record.is_agent_accepted,
                    'agent_accepted_tm':agent_accepted_tm
                })
    else:
        data['e']='数据丢失'


    return  JsonResponse(data)

#核销配送接口
@csrf_exempt
def do_hexiao_dispatch(request):
    data = {
        'e': ''
    }
    hexiao_record_id = request.GET.get('hexiao_record_id', '')
    company_id = request.GET.get('company_id', '')
    user_id = request.GET.get('user_id', '')
    lat = request.GET.get('lat', '')
    lng = request.GET.get('lng','')
    remark = request.GET.get('remark','')
    if hexiao_record_id and company_id and user_id:
        user = User.objects.get(id=int(user_id))
        if user:
            ids = hexiao_record_id.split(',')
            ids = list(map(int, ids))  
            hexiao_record =HeXiao_Record.objects.filter(id__in=ids, company_id=int(company_id))
            if hexiao_record: 
                for h in hexiao_record:
                    h.is_dispatched=True
                    h.dispatched_tm=datetime.datetime.now()
                    h.user = user
                    h.user_name = user.last_name
                    h.user_phone = user.phone
                    h.save()   
            else:
                data['e'] = '数据不存在'

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


#核销收货 
@csrf_exempt
def do_hexiao_accept(request): 
    data = {
        'e': ''
    }
    hexiao_record_id = request.GET.get('hexiao_record_id', '')
    company_id = request.GET.get('company_id', '') 
    if hexiao_record_id and company_id:
        hexiao_record =HeXiao_Record.objects.filter(id=int(hexiao_record_id), company_id=int(company_id))
        if hexiao_record:
            hexiao_record = hexiao_record[0]
            #如果没有配送过
            if not hexiao_record.is_agent_accepted:  
                hexiao_record.is_agent_accepted=True
                hexiao_record.agent_accepted_tm=datetime.datetime.now()
                hexiao_record.save()
            else:
                data['e'] = '该单已收货'
        else:
            data['e'] = '数据不存在'
    else:
        data['e'] = '数据丢失'
    return  JsonResponse(data)
 
# 核销列表
@login_required
def hexiao_record(request):
    data = {'data': [], 'clmns': [], 'e': [],'subtt': ['营销活动', '奖品核销'], 'murl': '', 'dt': False, 'add': True}
    data['clmns'] = ['终端店名称','活动信息','奖品名称', '消费者信息', '状态','备注','时间','审核','操作']
    data['show_add'] = True
    data['murl'] = '/company/news/edit/'

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

    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 

    company_id = str(request.user.company.id)
    cur = connection.cursor()
    cur.execute("""SELECT 
                id,
                agent_name,
                activity_name,
                award_name,
                (select wx_headimgurl from sales_consumeraccount where id = consumer_id),
                (select wx_nickname from sales_consumeraccount where id = consumer_id), 
                is_need_dispatch,
                is_dispatched,
                is_need_money_return,
                is_money_returned,
                remark,
                tm,
                is_permit  
                FROM sales_hexiao_record WHERE 
                company_id = """+company_id+""" and
                (tm BETWEEN '"""+dt_fr+"""' and '"""+dt_to+"""') order by id desc""")
    rows = cur.fetchall()

    for r in rows:
        id = r[0]
        agent_name = r[1]
        activity_name = r[2]
        award_name = r[3]
        wx_headimgurl = r[4]
        wx_nickname = r[5]
        is_need_dispatch = r[6]
        is_dispatched = r[7]
        is_need_money_return = r[8]
        is_money_returned = r[9]
        remark = r[10]
        tm = r[11]
        is_permit = r[12]





        dispatch_status = ''
        if is_need_dispatch:
            if is_dispatched:
                dispatch_status = '已配送'
            else:
                dispatch_status = '<span style="color:#F00;line-height:0">需要配送</span>'
        else:
            dispatch_status = '不需要配送'
        #如果是代金券
        if is_need_money_return:
            if is_money_returned:
                dispatch_status = '已返现'
            else:
                dispatch_status = '<span style="color:#F00;line-height:0">需要返现</span>'



        flag = '<span style="color:#f00;line-height:0">否</span> <button class="btn btn-default btn-xs" onclick="permitIt('+str(id)+')">审核</button>'
        remark = '无'
        #consumer_name = ''
        #consumer_phone = ''
        if is_permit:
            flag = '是'
        if remark:
            remark = remark
        # if consumer_name:
        #     consumer_name = consumer_name
        # if consumer_phone:
        #     consumer_phone = consumer_phone
            
        
        # consumer = t.consumer
        # wx_headimgurl = consumer.wx_headimgurl
        # wx_nickname = consumer.wx_nickname

        data['data'].append([
            agent_name,
            activity_name,
            award_name,
            "<img src='"+wx_headimgurl+"' style='width:30px' /> "+wx_nickname,
            #consumer_name+consumer_phone,
            dispatch_status,
            remark,
            tm.strftime("%Y-%m-%d %H:%M:%S"),
            flag,
            "<a class='btn btn-default btn-xs' href='/sales/hexiao_record/detail/" + str(id) + "/'>详情</a>"
        ])
    return render(request, 'sales/hexiao_record.html', data)


# 核销详情
@login_required
def hexiao_record_detail(request,id=None):
    data = {
        'record':None,
        'agent':None,
        'consumer':None,
        'sales_man':None
    }
    if id:
        h = HeXiao_Record.objects.filter(company_id=request.user.company.id,id=int(id))
        if h:
            h = h[0]
            data['record'] = h
    return render(request, 'sales/hexiao_record_detail.html', data)



# 审核
@login_required
def hexiao_record_permit(request, id=None):
    data = {
        'e':''
    }
    if id :
        obj = HeXiao_Record.objects.get(id=int(id), company_id=request.user.company.id)
        obj.is_permit=True
        if obj.award.awardcat_id ==6 and obj.is_need_money_return:
            obj.is_money_returned = True

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

# 配送列表
def hexiao_dispatch_record(request):
    data = {'data': [], 'clmns': [], 'e': [],'subtt': ['营销活动', '配送记录'], 'murl': '', 'dt': False, 'add': True}
    data['clmns'] = ['业务员','奖品名称','终端店名称','状态','备注','时间','操作']

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

    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 

    if request.method == 'POST':
        id = request.POST.get('id', '')
        user_id = request.POST.get('user_id', '') 
        if id and user_id:
            u = User.objects.filter(id=int(user_id))
            if u:
                u = u[0] 
                obj = HeXiao_Record.objects.get(id=int(id))
                obj.user = u
                obj.user_name = u.username
                obj.user_phone = u.phone
                obj.save()

    company_id = request.user.company.id

    
    cur = connection.cursor()
    cur.execute("""SELECT 
                id,
                agent_name,
                activity_name,
                award_name,
                (select wx_headimgurl from sales_consumeraccount where id = consumer_id),
                (select wx_nickname from sales_consumeraccount where id = consumer_id), 
                is_need_dispatch,
                is_dispatched,
                is_need_money_return,
                is_money_returned,
                remark,
                tm,
                is_permit,
                user_name,
                user_phone,
                user_id  
                FROM sales_hexiao_record WHERE 
                company_id = """+str(company_id)+""" and
                is_need_dispatch = true and
                is_permit = true and

                (tm BETWEEN '"""+dt_fr+"""' and '"""+dt_to+"""')  order by id desc""")
    rows = cur.fetchall()

    for r in rows:
        id = r[0]
        agent_name = r[1]
        #activity_name = r[2]
        award_name = r[3]
        #wx_headimgurl = r[4]
        #wx_nickname = r[5]
        #is_need_dispatch = r[6]
        is_dispatched = r[7]
        #is_need_money_return = r[8]
        #is_money_returned = r[9]
        remark = r[10]
        tm = r[11]
        #is_permit = r[12]
        user_name = r[13]
        user_phone = r[14] 
        user_id = r[15]


        userInfo = ''
        if user_phone:
            userInfo = user_phone 

        if not user_id:
            if company_id!=19318:
                userInfo = "<div class='btn btn-danger btn-xs' data-id='"+str(id)+"' data-toggle='modal' data-target='#modal_order'>分配业务员</div>"



        dispatch_status = ''
        if is_dispatched:
            dispatch_status = '已配送'
        else:
            dispatch_status = '<span style="color:#F00;line-height:0">还未配送</span>'

        data['data'].append([
            userInfo,
            award_name,
            agent_name,
            dispatch_status,
            remark,
            tm.strftime("%Y-%m-%d %H:%M:%S"),
            "<a class='btn btn-default btn-xs' href='/sales/hexiao_record/detail/" + str(id) + "/'>详情</a>"
        ])
    return render(request, 'sales/hexiao_record.html', data)


# 配送数据
@csrf_exempt
def hexiao_dispatch_record_data(request):
    data = {
        'e': '',
        'records':[]
    }
    company_id = request.GET.get('company_id', '')
    user_id = request.GET.get('user_id', '')
    if company_id and user_id: 
        cur = connection.cursor()
        cur.execute("""SELECT 
                    id,
                    activity_name,
                    award_name,
                    agent_name,
                    is_dispatched,
                    (select lng_lat from company_company where id = agent_id),
                    (select cell_phone from company_company where id = agent_id),
                    is_agent_accepted,
                    tm 
                    FROM sales_hexiao_record WHERE agent_id in (select id from logistics.company_company where sub_division_id in 
                    (SELECT unnest((select subsaldiv_ids FROM logistics.company_staffsaldiv WHERE user_id = """+user_id+""")))) 
                    and is_permit = true and is_need_dispatch = true
                    """)
        rows = cur.fetchall()
        for r in rows:
            data['records'].append({
                'id':r[0],
                'activity_name':r[1],
                'award_name':r[2],  
                'agent_name':r[3],
                'is_dispatched':r[4],
                'lng_lat':r[5],
                'phone':r[6],
                'is_agent_accepted':r[7],
                'tm':r[8].strftime("%Y-%m-%d %H:%M:%S"),
            }) 
    else:
        data['e'] = '数据丢失'
    return JsonResponse(data)



# 代金券返现
def hexiao_returned_money(request):

    data = {'data': [], 'clmns': [], 'e': [],'subtt': ['营销活动', '奖品核销'], 'murl': '', 'dt': False, 'add': True}
    data['clmns'] = ['奖品名称', '终端店名称','返现金额','返现状态','备注','时间','操作']
    d = datetime.datetime.now() 
    dt_fr = request.GET.get('date_from', (d + datetime.timedelta(days=-7)).strftime('%Y-%m-%d')) 
    dt_to = request.GET.get('date_to', (d+datetime.timedelta(days=1)).strftime("%Y-%m-%d"))

    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 

    for t in HeXiao_Record.objects.filter(company_id=request.user.company.id,is_need_money_return=True,tm__range=(dt_fr,dt_to)):

        flag = '<span style="color:#f00">否</span>'
        remark = '无'
        if t.is_money_returned:
            flag = '是'
        if t.remark:
            remark = t.remark

        data['data'].append([
            t.award_name,
            t.agent.name,
            str(t.money),
            flag,
            remark,
            t.tm.strftime("%Y-%m-%d %H:%M:%S"),
            "<a class='btn btn-default btn-xs' href='/sales/hexiao_record/detail/" + str(t.id) + "/'>详情</a>"
        ])
    return render(request, 'sales/hexiao_record.html', data)



@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 = ''
        agent_name = ''
        if l.product:
            product_name = l.product.name
        if l.agent_name:
            agent_name= l.agent_name

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

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

@csrf_exempt
@login_required
def company_list(request):
    data={'clmns':[],'data':[],'e':''}
    cid =request.user.company_id
    cur = connection.cursor()
    c = request.user.company
    data['clmns'] = [_('经销商名称'), _('经销商级别'), _('上级经销商'), _('地址'), _('省份'),
                     _('城市'), _('区县'),_('战区'),_('片区'), _('联系方式')]
    if Level.objects.get(sn=0):
        sWhere = "where ancestor_id = "+str(cid)+" and is_delete=false  and  id !=  " +str(cid)
    else:
        sWhere = 'where parent_id = '+str(cid)+" and is_delete=false"
    if request.method == 'GET':
        s = "select name,(select name from company_level where id = level_id),coalesce ((select name from company_company b where id = a.parent_id ),''),"+\
            "coalesce(address,''),coalesce (province,''),coalesce (city,''),"+\
            "coalesce(county,''),coalesce((select name from company_salesdivision where id = division_id),''),"+\
            "coalesce((select name from company_subsaldivision where id = sub_division_id),''),"+\
            "coalesce(cell_phone,''),parent_id from company_company a  "+sWhere
        cur.execute(s)
        r = cur.fetchall()
        for i in r :
            #print(i)
            data['data'].append([i[0],i[1],i[2],i[3],i[4],i[5],i[6],i[7],i[8],i[9]])
        return render(request,'company/company_list.html',data)
    else:
        dt = datetime.datetime.now()
        files = request.FILES.getlist('file')
        f = files[0]
        fn = f.name
        a = []
        p = settings.MEDIA_ROOT+str(cid)+'/upload/'+\
            str(dt.year)+str(dt.month).zfill(2)+str(dt.day).zfill(2)
        if not os.path.exists(p):
            os.makedirs(p)
        with open (p+'/'+fn,'wb+')as destination:
            for chunk in f.chunks():
                destination.write(chunk)
        book = xlrd.open_workbook(p+'/'+fn)
        sheet = book.sheets()[0]
        rw1 = sheet.nrows #获取行数
        cl1 = sheet.ncols #获取列数
        #print(rw1)
        for i in range(2,rw1):
            if sheet.cell_type(i, 9) == 1:
                c = str(sheet.cell_value(i, 9))
            else:
                c = str(sheet.cell_value(i,9))            
            is_have_company= False
            is_have_parent = False
            if sheet.cell_value(i,0): 
                cur.execute("select id from company_company where name = '"+str(sheet.cell_value(i,0))+"' and ancestor_id = "+str(cid) )
                pp = cur.fetchone()
                if pp and pp[0]:
                    is_have_company = True


            if sheet.cell_value(i,2):

                cur.execute("select id from company_company where  name = '"+str(sheet.cell_value(i,2))+"' and ancestor_id = "+str(cid) )
                p = cur.fetchone()
                if p and p[0]:
                    pid =p[0] 
                    is_have_parent = True
            else:
                pid = cid
                is_have_parent = True
            if not is_have_company and is_have_parent:
                a.append("('"+str(sheet.row_values(i)[0])+"','t', 'f', 't', 'f', 'f', 't', 'f', 'f', "+ \
                    "now(),now(),'f','"+str(sheet.row_values(i)[3])+"','"+str(c)+"','', '', '',1, "+str(cid)+", (select id from company_level where name = '"+ \
                     str(sheet.row_values(i)[1])+"'),"+str(pid)+",'"+ \
                     str(sheet.row_values(i)[4])+"','"+ str(sheet.row_values(i)[5])+"','"+ str(sheet.row_values(i)[6])+\
                     "','t', 0,'f', "+str(cid)+", 'f', 'f', 1, 'f', 'f',(select id from company_salesdivision where name = '"+str(sheet.cell_value(i,7))+\
                    "' and company_id = "+str(cid)+" and is_delete = false),(select id from company_subsaldivision where name = '" + str(
                    sheet.cell_value(i, 8)) + "' and company_id = " + str(cid) +\
                    " and division_id = (select id from company_salesdivision where name = '"+str(sheet.cell_value(i,7))+"')))")

        if a:
            s = "INSERT INTO company_company( name,is_active, is_delete, is_legacy, is_microboss, has_logcode,"+\
                " has_external_code, has_plant, has_workshop, tm,tm_register, inv_mgt, address, cell_phone, img_banner, "+\
                "img_bg, img_id,category_id, hierarchy_id, level_id, parent_id,  province, city,county, is_manufacturing, "+\
                "version, has_saldiv_mgt, ancestor_id, is_expense_account, is_franchised, status, multi_parents, is_exclusive,division_id,sub_division_id)VALUES   "+",".join(a)

            #print(s)

            cur.execute(s)
        return redirect('/company/list/')



@csrf_exempt
def consumer_appeal(request):
    data={'e':''}
    consumer_id = request.POST.get('consumer_id','')
    company_id = request.POST.get('company_id','') 
    if consumer_id and company_id:
        company = Company.objects.filter(id=int(company_id))
        if company:
            company = company[0]
            consumer_id = decrypt_p(consumer_id)
            consumer = ConsumerAccount.objects.filter(id=int(consumer_id))
            if consumer: 
                consumer = consumer[0]
                appeal = Consumer_Appeal()

                photo = request.FILES.get('photo')
                if photo: 
                    comp_name = company.name
                    media_path = settings.MEDIA_ROOT
                    dirs = os.path.join(media_path,comp_name, 'appeal')
                    if not os.path.exists(dirs):
                        os.makedirs(dirs)
                    
                    file_name = str(time.strftime('%Y%m%d%H%M%S', time.localtime(time.time()))) + str(time.time()).replace('.', '')[-7:] + "." + photo.name.split('.')[-1]
                    # 保存图片
                    f_url = os.path.join(media_path,comp_name, 'appeal', file_name)
                    img_url =  "\\media\\"+os.path.join(comp_name, 'appeal', file_name)

                    with open(f_url, 'wb+') as destination:
                        for chunk in photo.chunks():
                            destination.write(chunk)

                    appeal.photo = img_url
                
                appeal.company = company
                appeal.consumer = consumer
                #标签序号
                label_code = request.POST.get('label_code','')
                if label_code:
                    if company_id == "19318": 
                        cur = connection.cursor()
                        cur.execute("select coalesce((select code_b from label.lbl_abcode where code_a='" + label_code + "'),'')")
                        r = cur.fetchone()
                        if r:
                            label_code = r[0] 
                    appeal.label_code = label_code
                #留言
                message = request.POST.get('message','') 
                if message:
                    appeal.message = message
                #手机号码
                phone = request.POST.get('phone','') 
                if phone:
                    appeal.phone = phone

                appeal.save()

                    
            else:
                data['e'] = '用户不存在'
        else:
            data['e'] = '公司不存在'

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



@csrf_exempt
def consumer_appeal_status(request): 
    data={'e':'','status':'','reason':''}
    consumer_id = request.GET.get('consumer_id','')
    company_id = request.GET.get('company_id','') 
    label_code = request.GET.get('label_code','') 
    if consumer_id and company_id and label_code: 
        company = Company.objects.filter(id=int(company_id))
        if company:
            company = company[0]
            consumer_id = decrypt_p(consumer_id)
            consumer = ConsumerAccount.objects.filter(id=int(consumer_id))
            if consumer: 
                consumer = consumer[0]

                
                if company_id == "19318":
                    cur = connection.cursor()
                    cur.execute("select coalesce((select code_b from label.lbl_abcode where code_a='" + label_code + "'),'')")
                    r = cur.fetchone()
                    if r:
                        label_code = r[0] 
                appeal = Consumer_Appeal.objects.filter(label_code=label_code).order_by('-id')[:1]
                if appeal:
                    appeal = appeal[0]
                    reason = appeal.reason
                    data['reason'] = reason

                    if appeal.status == 0:
                        data['status'] = '审核中'
                    elif appeal.status == 1:
                        data['status'] = '审核通过'
                    else:
                        data['status'] = '被驳回' 
            else:
                data['e'] = '用户不存在'
        else:
            data['e'] = '公司不存在'

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

@login_required
def consumer_appeal_page(request):
    
    data={'e':'','data':[],'clmns':[],'dt':''}
    cid = request.user.company.id
    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', (d+datetime.timedelta(days=1)).strftime("%Y-%m-%d"))


    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'] = [_('标签号码'),_('消费者'),_('图片'),_('手机'),_('姓名'),_('客户留言'),_('回复信息'),_('时间'),_('状态'),_('操作')]

    if request.method == 'POST':
            id = request.POST.get('id', '')
            if id:
                obj = Consumer_Appeal.objects.get(id=int(id)) 
                reason = request.POST.get('reason','')
                status = int(request.POST.get('status','0'))
                obj.reason = reason
                obj.status = status
                if status !=0:
                    obj.stop_tm = now()
                    phone = obj.phone

                    text = ''
                    if status == 1:
                        text = '您提交的刮损瓶盖申诉已通过，请您重新扫描盒盖领取。'
                    else:
                        text = '您提交的刮损瓶盖申诉未通过，请重新扫描盒盖二维码拍照。'
                        if reason:
                            text = text + '原因:'+reason
                    
                    cfg=CompanyCfg.objects.get(company_id=request.user.company.ancestor_id)
                    sms_sign=cfg.sms_sign
                    send_sms(phone, text, sms_sign)

                obj.save()


    rows = Consumer_Appeal.objects.filter(company_id=request.user.company.id,tm__range=(dt_fr,dt_to))
    for r in rows:
        phone = ''
        if r.phone:
            phone = r.phone
        name = ''
        if r.name:
            name = r.name
        message = ''
        if r.message:
            message = r.message

        reason =''
        if r.reason:
            reason = r.reason

        status = ''
        if r.status == 0:
            status = '审核中'
        elif r.status == 1:
            status = '<span style="color:green">已通过</span>'
        elif r.status == 2:
            status = '<span style="color:red">未通过</span>'

        data['data'].append([
            r.label_code,
            '<img src="'+r.consumer.wx_headimgurl+'" style="width:20px"/> '+r.consumer.wx_nickname,
            '<img src="'+r.photo+'" style="width:50px"/>',
            phone,
            name,
            message, 
            reason,
            r.tm.strftime("%Y-%m-%d %H:%M:%S"),
            status,
            "<a class='btn btn-default btn-xs' onclick='editLevel("+str(r.id)+")'>操作</a>"
        ])


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


@login_required
def list_obj(request,what):
    dt = datetime.datetime.now().strftime('%Y-%m-%d') 
    dt_fr = request.GET.get('dt_fr',dt) 
    dt_to = request.GET.get('dt_to',dt)    
    u=request.user
    c=u.company
    cur=connection.cursor()
    data={'dt_fr':dt_fr,'dt_to':dt_to,'e':''}
    if what=='consumerchannelrebate_details':        
        sAgent=''
        agent_id=request.GET.get('agent_id','')
        if agent_id:
            a=Company.objects.get(id=int(agent_id))
            data['agent_id']=a.id
            data['agent_name']=a.name
            sAgent=" and company_id="+agent_id
        else:
            data['agent_id']=''
            data['agent_name']=''
        data['clmns']=['扫码时间','微信昵称','标签号码','产品名称','经销商','层级','战区','奖励','活动名称']
        if c.level_id==1:
            sWhere=" where ancestor_id="+str(c.id)
        else:
            sWhere=" where company_id="+str(c.id)
        s="select to_char(tm,'yyyy-mm-dd HH24:MI:SS'),label_code,"+\
            "(select wx_nickname||'<img src=\"'||wx_headimgurl||'\"  style=\"width:20px\" />' from sales_consumeraccount where id=consumer_id),"+\
            "(select name from product_product where id=product_id),"+\
            "(select name from company_company where id=company_id),"+\
            "(select name from company_level where id=level_id),"+\
            "coalesce((select name from company_salesdivision where id=saldivision_id),''),"+\
            "to_char(val,'9990.00'),"+\
            "coalesce((select name from sales_channelpromotions where id=promotion_id),'') "+\
            "from wx_channelpromotionrebate"+sWhere+" and (dt between '"+dt_fr+"' and '"+dt_to+"')"+sAgent
        cur.execute(s)
        d=[]
        for r in cur.fetchall():
            d.append([ r[0],r[2],r[1],r[3],r[4],r[5],r[6],r[7],r[8] ])
        data['data']=d
        return render(request,'sales/consumerchannelrebate_list_details.html',data)
    elif what=='consumerchannelrebate_agent_summary':  
        data['clmns']=['经销商','产品名称','层级','战区','奖励','活动名称']
        if c.level_id==1:
            sWhere=" where ancestor_id="+str(c.id)
        else:
            sWhere=" where company_id="+str(c.id) 
        s="(select name from company_company where id=company_id),"+\
            "(select name from product_product where id=product_id),"+\
            "(select name from company_level where id=level_id),"+\
            "coalesce((select name from company_salesdivision where id=saldivision_id),''),"+\
            "to_char(val,'9990.00'),"+\
            "coalesce((select name from sales_channelpromotions where id=promotion_id),'') "+\
            "from "+\
            "(select procut_id,company_id,level_id,saldivision_id,promotion_id,sum(val) val "+\
                "from wx_channelpromotionrebate"+sWhere+" and (dt between '"+dt_fr+"' and '"+dt_to+"')"+\
                "group by procut_id,company_id,level_id,saldivision_id,promotion_id"+\
            ") g"
        cur.execute(s)
        d=[]
        for r in cur.fetchall():
            d.append([ r[0],r[2],r[1],r[3],r[4],r[5] ])
        data['data']=d
        return render(request,'sales/consumerchannelrebate_list_agents.html',data)
