from django.shortcuts import render
from django.http import HttpResponse, HttpResponseRedirect, JsonResponse
from .models import *
from .forms import *
from company.models import Company, User
from sales.models import Award
from django.contrib.auth.decorators import login_required
from django.views.decorators.csrf import csrf_exempt
from django.db import connection
from django.apps import apps
from decimal import Decimal
from inventory.views import points_changed

import os
import json
import pdfkit
import datetime
from datacenter import settings


@login_required
def banquetList(request,obj):
    data = {'data': [], 'clmns': [], 'e': [],'subtt': ['', ''], 'murl': '', 'dt': False, 'add': True}
    cur=connection.cursor()    

    if obj =='category':
        data['subtt'] = ['宴会管理','宴会类别']
        data['clmns'] = ['分类名称', '备注']
        data['murl'] = 'banquet/edit/category/'
        data['show_add'] = True
        rs = Category.objects.filter(company_id=request.user.ancestor_id)
        for r in rs:
            a='<a href="/banquet/edit/category/'+str(r.id)+'/">'+r.name+'</a>'
            remark = ''
            if r.remark:
                remark = r.remark
            data['data'].append([ 
                a,
                remark
            ])
    elif obj == "apply":

        data['subtt'] = ['宴会管理', '宴会申请']
        data['clmns'] = ['类别', '产品', '政策', '用酒人', '联系电话', '宴会时间', '地址', '桌数', '大区', '分区',
                         '申请终端', '申请人', '部门', '申请时间', '扫码产品', '审核时间','审核人', '状态']

        data['dt'] = True

        dt_default = datetime.datetime.now().strftime('%Y-%m-%d')
        dt_fr_default = (datetime.datetime.now() - datetime.timedelta(days=7)).strftime('%Y-%m-%d')
        dt_fr = request.GET.get('date_from', dt_fr_default)
        dt_to = request.GET.get('date_to', dt_default)
        data["date_from"] = dt_fr
        data["date_to"] = dt_to
        ancestor_id = request.user.ancestor_id
        sWhere = f" where tm::date >= '{dt_fr}' and tm::date <= '{dt_to}' and ancestor_id = {ancestor_id}"
        s="select id,(select name from banquet_category where id = category_id)," + \
                    "COALESCE((select name from product_product where id = product_id),'其他')," + \
                    "coalesce(policy,'')," + \
                    "user_name,user_phone,to_char(use_tm,'yyyy-mm-dd HH24:MI'),address,table_num," + \
                    "COALESCE((select name from company_salesdivision where id = (select division_id from company_company where id =a.company_id)),'')," + \
                    "COALESCE((select name from company_subsaldivision where id = (select sub_division_id from company_company where id =a.company_id)),'')," + \
                    "(select name from company_company where id = company_id)," + \
                    "(select last_name from company_user where id = applyer_id)," + \
                    "coalesce((SELECT name FROM company_department where id = (SELECT department_id FROM company_staff WHERE user_id = applyer_id)),'')," + \
                    "to_char(tm,'yyyy-mm-dd HH24:MI:SS')," + \
                    "coalesce(to_char(permit_tm,'yyyy-mm-dd HH24:MI:SS'),'')," + \
                    "status,COALESCE ( ( select last_name from company_user WHERE  id = permiter_id ), '' ) " + \
                    "from banquet_apply a " + sWhere
        
        cur.execute(s)
        rs = cur.fetchall()
        for r in rs:
            pbtn = "<a href='/banquet/list/apply_codes/?id=" + str(
                r[0]) + "' target='_blank' class='btn btn-default btn-sm btn-dlg'>详情</a>"
            permit_btn = ""
            status = r[16]
            if status != 0:
                if status == 1:
                    permit_btn = "<span>通过</span>"
                else:
                    permit_btn = "<span>未通过</span>"
            else:
                permit_btn = "<a href='/banquet/list/apply_permit/?id=" + str(
                    r[0]) + "' target='_blank' class='btn btn-default btn-sm btn-dlg'>审核</a>"

            data['data'].append([
                r[1], r[2], r[3], r[4], r[5], r[6], r[7], r[8], r[9], r[10], r[11], r[12], r[13], r[14], pbtn, r[15],r[17],
                permit_btn
            ])
    elif obj =="apply_codes":
        data['subtt'] = ['宴会申请','申请产品']
        data['clmns'] = ['标签序号','类型','产品']
        id = request.GET.get('id','')
        if id:
            cur.execute("select code,package_id,(select name from product_product where id = product_id) from banquet_apply_codes where apply_id = "+id)
            rs = cur.fetchall()
            for r in rs: 
                pkg = "瓶"
                if r[1] == 2:
                    pkg = "盒"
                elif r[1] == 3:
                    pkg = "箱"
                data['data'].append([  
                    r[0],pkg,r[2]
                ])
    
    elif obj == "apply_permit":
        id = request.GET.get('id','')
        if id:
            data["id"] = id
            data["uid"] = request.user.id
        return render(request, 'banquet/apply_permit.html', data)
    return render(request, 'obj/list_obj.html', data)
    

@login_required
def edit_banquet(request,obj_name,id=None):
    d = {
        'category': ['分类', 'banquet', 'Category'],
    }
    if obj_name not in d:
        return HttpResponse(_('无效对像名'))
    m=apps.get_model(d[obj_name][1], d[obj_name][2])    
    if id:
        obj = m.objects.get(id=int(id))
    else:
        obj = m()
    is_saved = False
       
    obj.company=request.user.company
        
        
    if obj_name=='category':              
        if request.method == 'GET':
            form = CategoryForm(instance=obj)       
        elif request.method == "POST":   
            form = CategoryForm(request.POST, instance=obj)
            if form.is_valid():
                form.save()
                is_saved = True
                
                
    return render(request, 'banquet/'+obj_name+'.html', {'form': form, 'is_saved': is_saved})

@csrf_exempt
def ajax_banquet(request,obj):

    if obj == 'category':
        return ajaxBanquetCategroies(request)
    elif obj =="award":
        return ajaxSalesAwards(request)
    elif obj =="apply":
        return ajaxBanquetApply(request)
    elif obj == "list":
        return ajaxBanquetList(request)
    elif obj == "permit":
        return ajaxBanquetPermit(request)
    elif obj == "detail":
        return ajaxBanquetDetail(request)
    elif obj == "cancel":
        return ajaxBanquetCancel(request)
    elif obj == "codecheck":
        return ajaxBanquetCodeCheck(request)
    elif obj == "pdf":
        return ajaxBanquetDetail(request,"pdf")

def ajaxBanquetApply(request):
    res = {
        'e': ''
    }
    d = json.loads(request.body)
    if (
        'company_id' in d and
        'applyer_id' in d and
        'category_id' in d and
        'policy' in d and
        'address' in d and
        'table_num' in d and
        'user_name' in d and
        'user_phone' in d and
        'use_tm' in d and
        'codes' in d
    ):
        company_id = d['company_id']
        applyer_id = d['applyer_id']
        category_id = d['category_id'] 
        policy = d['policy']
        address = d['address']
        table_num = d['table_num']
        user_name = d['user_name']
        user_phone = d['user_phone']
        use_tm = d['use_tm']
        codes = d['codes']
        if (
            company_id and
            applyer_id and
            category_id and 
            policy and
            address and
            table_num and
            user_name and
            user_phone and
            use_tm
        ):
            c = Company.objects.get(id=company_id)
            if 'id' in d and d['id']:
                apply = Apply.objects.get(id=d['id'])
            else:
                apply = Apply()
                apply.ancestor_id = c.ancestor_id
                apply.applyer_id = applyer_id 
                apply.company = c
            apply.category_id = category_id
            
            if 'product_id' in d and d['product_id']:
                product_id = d['product_id']
                apply.product_id = product_id
                
            apply.policy = policy
            apply.address = address
            apply.table_num = table_num
            apply.user_name = user_name
            apply.user_phone = user_phone
            apply.use_tm = use_tm
            
            #if c.ancestor_id == 19318:
                #apply.status = 1
             
            
            if 'lng_lat' in d and d['lng_lat']:
                apply.lng_lat = d['lng_lat']
                
                
            if 'status' in d and d['status']:
                apply.status = d['status']
            if 'reason' in d and d['reason']:
                apply.reason = d['reason']
            if 'annotation' in d and d['annotation']:
                apply.annotation = d['annotation']
            if 'permiter_id' in d and d['permiter_id']:
                apply.permit_tm = datetime.datetime.now()
                apply.permiter_id = d['permiter_id']

            if 'businessman_id' in d and d['businessman_id']:
                apply.businessman_id = d['businessman_id']
            
            if c.ancestor_id == 19318:
                sPrd = ''
                sSaldiv = ''
                sSubdiv = ''
                # 是否传参product_id
                # 经销商是否含销售战区信息
                if c.division:
                    sSaldiv = " and case when array_length(div_ids,1) is null then true else ARRAY[" + str(
                        c.division_id) + "] <@ div_ids end "
                # 经销商是否含销售分区信息
                if c.sub_division:
                    sSubdiv = " and case when array_length(div_ids,1) is null then true else ARRAY[" + str(
                        c.sub_division_id) + "] <@ div_ids end "
                # 当前经销商是否符合渠道营销方案

                t = datetime.datetime.now().strftime('%Y-%m-%d %H:%M')
                aid = str(c.ancestor_id)
                s = "select id,name,awardcategory_id,triggers,point_ratio from sales_channelpromotions " + \
                    "where company_id=" + aid + " and ('" + t + "' between tm_start and tm_end) and status=1 " + \
                    sPrd + sSaldiv + sSubdiv
                cur = connection.cursor()
                sTrigger = " and triggers @> '[{\"id\":\"2\"}]'::jsonb "
                s = s + sTrigger + " order by priority"
                cur.execute(s)
                r = cur.fetchone()
                if r:
                    # 渠道促销活动id
                    channelpromotions_id = r[0]
                    apply.channelpromotions_id =channelpromotions_id
            
            apply.save()
            

            existCodes = []
            acs = Apply_Codes.objects.filter(apply_id=apply.id)
            for a in acs:
                existCodes.append(a.code)

            for i in range(len(codes)):
                citem = codes[i]
                code = codes[i]['code']
                product_id = codes[i]['product_id']
                package_id = codes[i]['package_id']
                
                needAdd = True        
                
                if 'row_id' in citem and citem['row_id']:
                    if 'delete' in citem and citem['delete']:
                        ac = Apply_Codes.objects.get(id=citem['row_id'])
                        ac.delete()
                        needAdd = False
                        
                
                for ii in range(len(existCodes)):
                    if code == existCodes[ii]:
                        needAdd = False
                        break
                        
                if needAdd:
                    ac = Apply_Codes(
                        apply=apply,
                        code=code,
                        product_id=product_id,
                        package_id=package_id
                    )
                    if 'label_pk_id' in citem:
                        ac.label_id =citem['label_id']
                        ac.label_pk_id = citem['label_pk_id']
                    ac.save()
            
            
            if 'photos' in d:
                photos = d['photos']
                for i in range(len(photos)):
                    photo = photos[i]
                    if 'row_id' in photo and photo['row_id']:
                        ac = Apply_Pictures(id=photo['row_id'])
                        if 'delete' in photo and photo['delete']:
                            ac.delete()
                            continue
                    else:
                        ac = Apply_Pictures()

                    ac.apply_id = apply.id
                    ac.photo = photo['url']
                    ac.save()
            if 'status' in d and d['status'] == 1 and c.ancestor_id == 19318:
                points_changed.send(apply)
        else:
            res['e'] = '数据丢失'
    else:
        res['e'] = '数据丢失'
    return JsonResponse(res)

def ajaxBanquetList(request):
    res = {
        'e':'',
        'list':[],
        'un_permit':[],
        'permited':[]
    }
    company_id = request.GET.get('company_id','')
    uid = request.GET.get('uid','')
    ancestor_id = request.GET.get('ancestor_id','')
    tm_start = request.GET.get('tm_start','')
    tm_end = request.GET.get('tm_end','')
    use_tm_start = request.GET.get('use_tm_start','')
    use_tm_end = request.GET.get('use_tm_end','')

    if ancestor_id:

        sWhere = f" where ancestor_id = {ancestor_id}"
        if company_id:
            sWhere = sWhere+ f" and company_id = {company_id}"
        if uid:
            sWhere = sWhere+ f" and (applyer_id = {uid} or businessman_id = {uid})" 
        if tm_start and tm_end:
            sWhere = sWhere+ f" and tm >= '{tm_start}' and tm<= '{tm_end}'"
            
        if use_tm_start and use_tm_end:
            sWhere = sWhere+ f" and use_tm >= '{use_tm_start}' and use_tm<= '{use_tm_end}'"
        
        
        cur=connection.cursor()
        sql = "select id, user_name, user_phone, address, table_num, to_char(use_tm,'yyyy-mm-dd HH24:MI'), to_char(tm,'yyyy-mm-dd HH24:MI:SS'), award_id, "+\
              "(select name from sales_award where id = award_id),"+\
              "(select name from banquet_category where id = category_id),"+\
              "to_char(permit_tm,'yyyy-mm-dd HH24:MI:SS'),to_char(finish_tm,'yyyy-mm-dd HH24:MI:SS'),status,applyer_id,"+\
              "(select last_name from company_user where id = applyer_id ), "+\
              "(select name from company_company where id = company_id ),policy, "+\
              "COALESCE((select name from product_product where id = product_id ),'其他'), "+\
              "(SELECT last_name || ' '|| COALESCE(phone,'') from company_user where id = businessman_id) "+\
              "from banquet_apply "+sWhere+" order by id desc"
        cur.execute(sql)
        rs = cur.fetchall()
        if rs: 
            for r in rs: 
                item = {
                    'id': r[0],
                    'user_name':r[1],
                    'user_phone':r[2],
                    'address':r[3],
                    'table_num':r[4],
                    'use_tm':r[5],
                    'tm':r[6],
                    'award_id':r[7],
                    'award_name':r[8],
                    'category_name':r[9],
                    'permit_tm':r[10],
                    'finish_tm':r[11],
                    'status':r[12],
                    'applyer_id':r[13],
                    'applyer_name':r[14],
                    'company_name':r[15],
                    'policy':r[16],
                    'product_name':r[17],
                    'businessman_name':r[18]
                }

                if r[12] == 0:
                    res['un_permit'].append(item)
                else: 
                    res['permited'].append(item)
    else:
        res['e'] = '数据丢失'
    return JsonResponse(res)

def ajaxBanquetCategroies(request):
    res = {
        'e':'',
        'categories':[]
    }
    company_id = request.GET.get('company_id','')
    if company_id:
        cs = Category.objects.filter(company_id=company_id)
        if cs:
            for c in cs:
                res['categories'].append({
                    'id':c.id,
                    'name': c.name
                })
    else:
        res['e'] = '数据丢失'

    return JsonResponse(res)
    
def ajaxSalesAwards(request):
    res = {
        'e':'',
        'awards':[]
    }
    company_id = request.GET.get('company_id','')
    if company_id:
        cs = Award.objects.filter(company_id=company_id,is_usein_banquet=True)
        if cs:
            for c in cs:
                res['awards'].append({
                    'id':c.id,
                    'name': c.name
                })
    else:
        res['e'] = '数据丢失'

    return JsonResponse(res)
    
def ajaxBanquetPermit(request):
    res = {
        'e':''
    }
    id = request.GET.get('id','')
    status = request.GET.get('status','')
    uid = request.GET.get('uid','')
    remark = request.GET.get('remark','')
    if id and uid and status: 
        rs = Apply.objects.filter(id=id)
        if rs:
            a = rs[0]
            a.permit_tm = datetime.datetime.now()
            a.permiter_id = uid
            a.status = status
            a.reason = remark
            a.save()
            if status == '1': 
                points_changed.send(a)
        else:
            res['e']='记录不存在'
    else:
        res['e'] = '数据丢失'

    return JsonResponse(res)
    
def ajaxBanquetDetail(request,responseType="json"):
    res = {
        'e':'',
        'detail':{
            'info':None,
            'photos':[],
            'codes':[]
        }
    }
    id = request.GET.get('id','')
    if id: 
        cur=connection.cursor()  
        sWhere = f" where id = {id}" 
        sql = "select id, user_name, user_phone, address, table_num, to_char(use_tm,'yyyy-mm-dd HH24:MI'), to_char(tm,'yyyy-mm-dd HH24:MI:SS'), award_id, " + \
              "(select name from sales_award where id = award_id)," + \
              "(select name from banquet_category where id = category_id)," + \
              "to_char(permit_tm,'yyyy-mm-dd HH24:MI:SS'),to_char(finish_tm,'yyyy-mm-dd HH24:MI:SS'),status,applyer_id," + \
              "(select username from company_user where id = applyer_id ), " + \
              "(select name from company_company where id = company_id ), " + \
              "category_id, company_id, lng_lat, " + \
              "coalesce((select name from product_product where id = product_id),'其他'),product_id,policy, " + \
              "reason,annotation,businessman_id,(SELECT last_name || ' '|| COALESCE(phone,'') from company_user where id = businessman_id), " + \
              "COALESCE((select name from company_salesdivision where id = (select division_id from company_company where id =a.company_id)),'')," + \
              "COALESCE((select name from company_subsaldivision where id = (select sub_division_id from company_company where id =a.company_id)),'')" + \
              "from banquet_apply a " + sWhere
        cur.execute(sql)
        r = cur.fetchall()
        if r:
            r = r[0]
            item = {
                'id': r[0],
                'user_name':r[1],
                'user_phone':r[2],
                'address':r[3],
                'table_num':r[4],
                'use_tm':r[5],
                'tm':r[6],
                'award_id':r[7],
                'award_name':r[8],
                'category_name':r[9],
                'permit_tm':r[10],
                'finish_tm':r[11],
                'status':r[12],
                'applyer_id':r[13],
                'applyer_name':r[14],
                'company_name':r[15],
                'category_id':r[16],
                'company_id':r[17],
                'lng_lat':r[18],
                'product_name':r[19],
                'product_id':r[20],
                'policy':r[21],
                'reason':r[22],
                'annotation':r[23],
                'businessman_id': r[24],
                'businessman_name': r[25],
                'salesdivision': r[26],
                'subsaldivision': r[27]
            }
            res['detail']['info'] = item
            
            
            sWhere=f" where apply_id = {id}" 
            
            cur.execute("select id,(select name from product_product where id = product_id),product_id,code,package_id from banquet_apply_codes "+sWhere)
            rs = cur.fetchall()
            codes = []
            for r in rs:
                pkg = "瓶"
                if r[4] == 2:
                    pkg = "盒"
                elif r[4]== 3:
                    pkg = "箱"
                codes.append({
                    'row_id':r[0],
                    'product_name':r[1], 
                    'product_id':r[2],
                    'code':r[3],
                    'package':pkg,
                    'package_id':r[4]
                }) 
            res['detail']['codes'] = codes
            
            cur.execute("select id, photo from banquet_apply_pictures " + sWhere)
            rs = cur.fetchall()
            photos = []
            for r in rs:
                photos.append({
                    'row_id': r[0],
                    'url': r[1]
                })
            res['detail']['photos'] = photos
    
    if responseType == "pdf":
        if res['detail']['info']:
            return render(request, 'banquet/pdf.html', {
                'info': res['detail']['info'], 
                'photos': res['detail']['photos'],
                'codes': res['detail']['codes']
            })
            
    return JsonResponse(res)

def ajaxBanquetCancel(request):
    res = {
        'e':''
    }
    id = request.GET.get('id','')
    if id: 
        obj = Apply.objects.get(id=id)
        if obj.status == 0:
            obj.delete()
        else:
            data['e'] = '无法删除已审核申请'
    else:
        data['e'] = '数据丢失'
    
    return JsonResponse(res)

def ajaxBanquetCodeCheck(request):
    data = {
        'e':'',
        'apply_id': 0
    }
    code = request.GET.get('code','')
    if code:
        bc = Apply_Codes.objects.filter(code=code)
        if bc:
            data['apply_id'] = bc[0].apply_id
    else:
        data['e'] = '数据丢失'
    return JsonResponse(data)

