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


import json
import datetime


@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}"
        
        cur.execute("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 "+\
                     "from banquet_apply a "+sWhere)
        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],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)

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']

            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
                    )
                    ac.save()

        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}" 
        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 ),'其他') "+\
              "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]
                }

                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()
        else:
            res['e']='记录不存在'
    else:
        res['e'] = '数据丢失'

    return JsonResponse(res)
    
def ajaxBanquetDetail(request):
    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 "+\
              "from banquet_apply "+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]
            }
            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
    
    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)
