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


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   
    for co in Company.objects.filter(level_id=1).order_by('name'):
        data['companys'].append([co.id,co.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   
    for co in Company.objects.filter(level_id=1).order_by('name'):
        data['companys'].append([co.id,co.name])    
    if id:
        obj = ChannelPromotions.objects.get(id=int(id))    
    else:
        obj = ChannelPromotions()
        obj.user=u        
        obj.status=0
    if request.method=='GET':       
            form=ChannelPromotionsForm(instance=obj)
            formset=ChannelRebateFormset(instance=obj)
            agents=[]
            products=[]
            div_ids=[]
            subdiv_ids=[]        
            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
    else:        
            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/channelpromotions.html',data)
