from django.shortcuts import render
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 django.contrib.auth.decorators import login_required
sK = 'fgfdGvgu$#&3tE@j'

@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)
    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():
            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():
            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,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]))
            data['agents']['data'].append({'value':int(r[1]),'name':r[0], 'index': i, 'company_to_id': r[2]})
    #---- 查询:按系列分类的总数
    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['products']['data'].reverse()

    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)
    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   
    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])
    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)
            formset=ActivityAwardsFormset(instance=obj) 
            a=[]
            for r in cur.fetchall():
                a.append( [r[0], r[1] ])
            data['prs']=a
            agents=[]
            products=[]
            levels=[]
            adms=[]
            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] ])
            data['agents']=agents
            data['products']=products
            data['levels']=levels
            data['adms']=adms
            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)     
            formset=ActivityAwardsFormset(request.POST, request.FILES, instance=obj)
            if form.is_valid():                   
                form.save()
                formset.save()
                is_saved=True   
                return HttpResponseRedirect('/obj/list/'+obj_name.lower()+'/')    

    data['form']=form
    data['is_saved']=is_saved
    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_curtomer_redeem(request,winner_id):
    data={"e":''}
    ws=ConsumerWinner.objects.filter(id=int(winner_id))
    if ws:
        w=ws[0]
        if not w.tm_redeemed:
            w.tm_redeemed=now()
            w.save()
    else:
        data['e']='winner id err'
    return JsonResponse(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()
        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)
        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-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
    clmns=['扫码时间','经销商/分销商名称','战区','分区','层级','活动名称','产品名称','扫码场景','扫码类别','箱数','瓶数','奖励金额(元)','距离仓库(米)','扫码距离确认']
    if c.level_id==1: 
        s="select to_char(a.tm,'yyyy-mm-dd HH24:MI:SS'),"+\
            "c.name,"+\
            "(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 '扫码出库' 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,"+\
            "(select count(*) from inventory_stkdetails where  batch_id=a.batch_id and label_pkg=3),"+\
            "(select count(*) from inventory_stkdetails where  batch_id=a.batch_id and label_pkg=1),"+\
            "case when a.company_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),'') "+\
            "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+"'"+\
            ") 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]:
                x=str(r[10])
            if r[11]:
                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[14])+"/confirm/'>确认</a>"
            d.append([ a,r[1],r[15],r[16],lv,r[2],r[3],r[4],t,x,p,r[5],r[6],cfm ])

    else:
        clmns=['时间','活动名称','奖励来源','扫码类别','产品名称','数量','奖励金额(元)','距离仓库','扫码距离确认']
        #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),"+\
        #    "coalesce((select name from product_product where id=(b.product_id)),''),"+\
        #    "case b.vtype_id when 1 then '扫码入库' when 2 then '扫码出库' end,"+\
        #    "case when a.distance=99999999.99 then '未知' else to_char(a.distance,'FM99999') end,"+\
        #    "to_char(a.val,'FM99999999990.00'),"+\
        #    "case when checked_by_id=1 then '系统确认' else (select username from company_user where id=checked_by_id) end,"+\
        #    "a.id,a.tm_checked,"+\
        #    "(select count(*) from inventory_stkdetails where  batch_id=a.batch_id and label_pkg=3),"+\
        #    "(select count(*) from inventory_stkdetails where batch_id=a.batch_id and label_pkg=1), "+\
        #    "(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 "+\
        #        "where company_id="+str(c.id)+" 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"  
        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'),"+\
            "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,"+\
            "(select count(*) from inventory_stkdetails where  batch_id=a.batch_id and label_pkg=3),"+\
            "(select count(*) from inventory_stkdetails where batch_id=a.batch_id and label_pkg=1), "+\
            "(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 "+\
                "where company_id="+str(c.id)+" 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 "+\
            "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=b.id"
        cur.execute(s)
        for r in cur.fetchall():    
            qty=''
            a="<a class='btn-details' href='/sales/channelretate/details/"+str(r[14])+"/'>"+r[0]+"</a>"
            if r[10]:
                qty=qty+str(r[10])+'箱'
            if r[11]:
                qty=qty+str(r[11])+'瓶'            
            cfm=r[7]
            if cfm:
                cfm=cfm+" "+r[9].strftime('%Y-%m-%d %H:%M')            
            d.append([ a,r[1],r[2],r[4],r[5],r[3],qty,r[6],cfm ])        
    data={'clmns':clmns,'data':d,'date_from':dt_fr,'date_to':dt_to}
   

    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+"' "
    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.x,'FM99999999990'),"+\
            "to_char(a.p,'FM99999999990'),"+\
            "to_char(a.mval,'FM99999999990.00'),"+\
            "to_char(a.cval,'FM99999999990.00'),"+\
            "to_char(a.cfm,'FM99999999990.00') "+\
            "from "+\
            "(select o.company_id,sum(o.mval) mval,sum(o.cval) cval,sum(o.cfm) cfm,sum(o.x) x,sum(o.p) p from "+\
                "(SELECT company_id,"+\
                    "case when company_id=company_src_id then val else 0.00 end mval,"+\
                    "case when company_id<>company_src_id then val else 0.00 end cval,"+\
                    "case when checked_by_id is null then 0 else val end cfm,"+\
                    "case when checked_by_id is null then val else 0 end uncfm,"+\
                    "(select count(id) from sales_channelrebatedetails where rebate_batch_id=a.id and label_pkg=3) x,"+\
                    "(select count(id) from sales_channelrebatedetails where rebate_batch_id=a.id and label_pkg=1) p "+\
                    "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=[]   
    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]
    
    #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),'999.90'),'') from sales_rebatesubstitute "+\
    #        "where rebate_batch_id="+batch_id+" and label_pk_id=a.label_pk_id) "+\
    #    "from sales_channelrebatedetails a where rebate_batch_id="+batch_id)
    
    #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],
    }

    cid=str(request.user.company.id)
    data={'e':''}
    #dt=datet
    data_closing=[]
    clmns_closing=['会计期','期结时间','账单笔数','应付款金额','审核人','审核时间','付款笔数','已付款金额','审核人','审核时间']
    cur=connection.cursor()
    #closing
    s="SELECT id,name,coalesce(to_char(tm,'yyyy-mm-dd HH24:MI:SS'),''),"+\
        "case period when 1 then '月结' when 2 then '季结' when 3 then '年结' else '' end,"+\
        "coalesce((select name from company_user where id=user_id),''),"+\
        "coalesce(to_char(payable_counting,'99999'),''),"+\
        "coalesce(to_char(payable_amount,'99999999.90'),''),"+\
        "coalesce((select name from company_user where id=payable_checker_id),''),"+\
        "coalesce(to_char(tm_payable_checked,'yyyy-mm-dd HH24:MI:SS'),''),"+\
        "coalesce(to_char(paid_counting,'99999'),''),"+\
        "coalesce(to_char(paid_amount,'99999999.90'),''),"+\
        "coalesce((select name from company_user where id=paid_checker_id),''),"+\
        "coalesce(to_char(tm_paid_checked,'yyyy-mm-dd HH24:MI:SS'),'') "+\
        "FROM sales_rebateclosing where company_id="+cid
    cur.execute(s)
    for r in cur.fetchall():
        data_closing.append([    r[1],r[2],r[5],r[6],r[7],r[8],r[9],r[10],r[11],r[12]   ])
    periods=[]
    cur.execute("select id,name from sales_rebateclosing where company_id="+cid+" order by dt desc limit 12")
    for r in cur.fetchall():
        periods.append([r[0],r[1]])

    clmns_payable=['经销商/分销商名称','层级','父级经销商名称','应付款金额(元)']
    data_payable=[]

    clmns_paid=['经销商/分销商名称','层级','父级经销商名称','已付款金额(元)']
    data_paid=[]

    data['last_period']={1:last_month,2:last_quarter} 
    data['data_closing']=data_closing
    data['clmns_closing']=clmns_closing    
    data['data_payable']=data_payable
    data['clmns_payable']=clmns_payable    
    data['data_paid']=data_paid
    data['clmns_paid']=clmns_paid
    data['periods']=periods   
    return render(request,'sales/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 what=='close':
        if c.level_id!=1:
            data['e']='公司层级错误'
        else:
            pname =request.GET.get('period_name','')
            ptype=request.GET.get('period_type','')
            pdt_fr=request.GET.get('period_fr','')
            pdt_to=request.GET.get('period_to','')     
            if not pname or not pdt_fr or not pdt_to or not ptype:
                data['e']='会计期名称/开始日期/结束日期不能为空'
            else:
                cls=RebateClosing.objects.filter(company_id=c.id,name=pname)
                if cls:
                    data['e']='会计期 '+pname+'已做过期结'
                else:
                    cls=RebateClosing(company_id=c.id,name=pname,period=int(ptype),user_id=u.id,dt_fr=pdt_fr,dt_to=pdt_to)
                    cls.save()
                    s="insert into sales_rebatepayable(company_id,parent_id,level_id,amount,dt,tm,agent_id,rebateclosing_id,agent_name,parent_name) 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) from "+\
                        "(select company_id,sum(val) val from sales_channelrebatebatch where ancestor_id="+str(c.id)+\
                            " and (dt between '"+pdt_fr+"' and '"+pdt_to+"') 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"    
                    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.save()  
    elif what=='list_payable':
        clmns=['经销商/分销商名称','层级','父级经销商名称','应付款金额(元)']
        d=[]
        cls_id=request.GET.get('closing_id','')
        if cls_id:
            s="select agent_name,parent_name,coalesce(to_char(amount,'99999999.90'),''),"+\
                "(select name from company_level where id=a.level_id) from "+\
                "sales_rebatepayable a where rebateclosing_id="+str(cls_id) 
            cur.execute(s)
            for r in cur.fetchall():
                d.append([r[0],r[3],r[1],r[2]])
        else:
            data['e']='id missing'
        data['clmn']=clmns
        data['data']=d
    elif what=='list_paid':
        clmns=['经销商/分销商名称','层级','父级经销商名称','已付款金额(元)']
        d=[]
        cls_id=request.GET.get('closing_id','')
        if cls_id:
            s="select agent_name,parent_name,coalesce(to_char(amount,'99999999.90'),''),"+\
                "(select name from company_level where id=a.level_id) from "+\
                "sales_rebatepaid a where rebateclosing_id="+str(cls_id) 
            cur.execute(s)
            for r in cur.fetchall():
                d.append([r[0],r[3],r[1],r[2]])
        else:
            data['e']='id missing'
        data['clmn']=clmns
        data['data']=d
    return JsonResponse(data)



#2020-12-01
@login_required
def saldiv_rebates_ajax(request):
    company_id=str(request.user.company_id)
    dt0='2020-11-01'    #最早发货时间
    product_ids='(900,1109)'
    dt=datetime.datetime.now().strftime('%Y-%m-%d')
    dt=request.GET.get('date',dt)
    cur_provincial=[]
    cur_extra=[]
    acc_provincial=[]
    acc_extra=[]
    cur=connection.cursor()

    #本期
    s="select a.name,a.provincial,coalesce(g.boxes1,0),coalesce(g.boxes2,0),id from "+\
        "(select id,name,provincial from company_salesdivision where company_id="+company_id+" and is_delete=false "+\
            "union all "+\
            "select 0,'其它',0 "+\
        ") 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=a.id) boxes1,0 boxes2 "+\
            "from sales_channelrebatebatch a where dt='"+dt+"' and ancestor_id="+company_id+" and vtype_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=a.id) boxes2 "+\
            "from sales_channelrebatebatch a where dt='"+dt+"' and ancestor_id="+company_id+" and vtype_id=1 "+\
        ") b "+\
        "group by division_id) g "+\
        "on a.id=g.division_id "+\
        "order by a.id"
    cur.execute(s)
    for r in cur.fetchall():
        if r[1]:
            cur_provincial.append([r[0],r[2],r[3]])
        else:
            cur_extra.append([r[0],r[2],r[3]])
        
    #累计
    s="select a.name,a.provincial,coalesce(g.boxes1,0),coalesce(g.boxes2,0),coalesce(d.boxes,0),a.id from "+\
        "(select id,name,provincial from company_salesdivision where company_id="+company_id+" and is_delete=false "+\
            "union all "+\
            "select 0,'其它',0 "+\
        ") 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=a.id) boxes1,0 boxes2 "+\
            "from sales_channelrebatebatch a where (dt between '"+dt0+"' and '"+dt+"') and ancestor_id="+company_id+" and vtype_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=a.id) boxes2 "+\
            "from sales_channelrebatebatch a where (dt between '"+dt0+"' and '"+dt+"') and ancestor_id="+company_id+" and vtype_id=1 "+\
        ") b "+\
        "group by division_id) g "+\
        "on a.id=g.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 "+\
                "where ancestor_id="+company_id+" and vtype_id=2 and (dt between '"+dt0+"' and '"+dt+"') "+\
                "and product_id in "+product_ids+" "+\
                "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 "+\
        "order by a.id"
    cur.execute(s)
    for r in cur.fetchall():
        if r[1]:
            acc_provincial.append([ r[0],r[2],r[3],r[4] ])
        else:
            acc_extra.append([ r[0],r[2],r[3],r[4] ])
    return JsonResponse({'cur_provincial':cur_provincial,'cur_extra':cur_extra,'acc_provincial':acc_provincial,'acc_extra':acc_extra})
    #data={'cur_provincial':cur_provincial,'cur_extra':cur_extra,'acc_provincial':acc_provincial,'acc_extra':acc_extra,'dt':dt,'dt0':dt0}
    cur.close()
    #return render(request,'sales/saldivision_rebates.html',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-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):
    """
    test:
    item=6: k3907875923p (2)
    item=5: k290783a7e3u
    item=5: k290783a7f32
    item=3: kc9077ff683k
    item=2: k79077e1de3i
    item=1: kc9077d47039
    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=1 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.'})
    try:
        ecpid = PromotionCode.objects.get(remark=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': '已被消耗'})
    except Exception as e:
        return HttpResponse(e)


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

    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
            if cat >= 3:
                limit = 3
                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=user,
                    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
            if cat >= 5:
                limit = 5
                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,
                    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,
                        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','')
    consumer_id = request.GET.get('uid', '')

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

    c = ConsumerRedeem.objects.get(winner=ConsumerWinner.objects.get(id=consumerwinner_id))
    return JsonResponse({'id': c.id, 'check': c.checked_tm, 'delivery': c.waybill_number})


@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)
    data = {
        'activatedNumber': activatedNumber,
        'winnerNumber': winnerNumber,
        'redeemedNumber': redeemedNumber,
        "recentOperation": recentOperation
    }
    return render(request, 'sales/status_monitor.html', data)



