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, AgentWinner
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 django.utils.timezone import now
from decimal import Decimal
from inventory.views import points_changed

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

from django.utils.translation import gettext_lazy as _

# 2024-06-28
sK = 'fgfdvgu$#&3t@j'
from utils.views import time_key_check
from inventory.views import LabelQuery, decrypt_11h
from wx.views import transfer_batch
import redis
import math
from mall.views import update_member_points_agent


#2020-12-21 微信redis库
rd_wx=redis.StrictRedis(host='localhost', port=settings.RD['port'], db=5, password=settings.RD['password'], decode_responses=True) 


@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 ), '' ), " "(SELECT COUNT(1) FROM banquet_apply_codes WHERE apply_id = a.id AND package_id = 3)," + \
            "coalesce(to_char(end_tm,'yyyy-mm-dd HH24:MI:SS'),''), "+\
            "actual_table_num,"+\
            "(SELECT COUNT(0) FROM banquet_writeoff WHERE apply_id = a.id), "+\
            "(select name from company_company where id = (select parent_id from company_company where id = company_id)), "+\
            "scan_num,"+\
            "bottle_num "+\
            "from banquet_apply a " + sWhere
        cur.execute(s)
        rs = cur.fetchall()
        for r in rs:
            dict = {
                0: ['未审核','blue'],
                1: ['已通过','#07c160'],
                2: ['未通过','red'],
                3: ['结算审核中','blue'],
                4: ['结算中','blue'],
                5: ['已结算','#07c160'],
            }
            status = dict[r[16]]
            color = 'blue'
            btn1 = "<a href='/banquet/list/apply_permit/?id=" + str(
                    r[0]) + "' target='_blank' class='btn-dlg btn-only-dlg' style='text-decoration: underline; color: "+status[1]+"'>"+status[0]+"</a>"
                
            btns = "<a href='/banquet/list/apply_codes/?id=" + str(
                    r[0]) + "' target='_blank' class='btn btn-default btn-sm btn-only-dlg'>申请扫码详情</a>"
            btns = btns + "<a href='/banquet/list/writeoff/?id=" + str(
                    r[0]) + "' target='_blank' class='btn btn-default btn-sm btn-only-dlg'>核销详情</a>"
            btns = btns + "<a href='/banquet/list/information_edit/?id=" + str(
                    r[0]) + "' target='_blank' class='btn btn-default btn-sm btn-only-dlg'>补充宴会信息</a>"
            btns = btns + "<a href='/banquet/list/apply_photos/?id=" + str(
                    r[0]) + "' target='_blank' class='btn btn-default btn-sm btn-only-dlg'>查看照片</a>"
            btns = btns + "<a href='/banquet/list/manual_settle/?id=" + str(
                    r[0]) + "' target='_blank' class='btn btn-default btn-sm btn-only-dlg'>手动结算</a>"
            scan_rate = 0
            if r[23] and r[24]:
                #scan_rate = str(round(r[23] / r[24], 4) * 100) + '%'
                scan_rate = format((r[23] / r[24])*100, '.2f') + '%'
                    
            data['data'].append([
                btn1, r[1], r[2], r[3], r[4], r[5], r[6], r[19], r[7], r[8], r[20], r[18], r[21], r[23], r[24], scan_rate, r[9], r[10], r[22], r[11], r[12],
                r[13], r[14], r[15], r[17], btns
            ])
        return render(request, 'banquet/list_obj.html', data)
    elif obj =="apply_codes":
        data['subtt'] = ['宴会申请','申请产品']
        data['clmns'] = ['标签序号','类型','产品']
        id = int(request.GET.get('id','0'))
        if id:
            cur.execute("select code,package_id,(select name from product_product where id = product_id) from banquet_apply_codes where apply_id = "+str(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]
                ]) 
        return render(request, 'banquet/apply_codes.html', data)
    elif obj =="writeoff":
        data['subtt'] = ['宴会申请','核销产品']
        data['clmns'] = ['标签序号','类型','产品']
        id = int(request.GET.get('id','0'))
        if id:
            cur.execute("select label_code,label_pkg,(select name from product_product where id = product_id) from banquet_writeoff where apply_id = "+str(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]
                ]) 
        return render(request, 'banquet/apply_codes.html', data)
    
    elif obj == "apply_permit":
        id = int(request.GET.get('id','0'))
        if id:
            apply = Apply.objects.get(id=id)
            data["id"] = id
            data["uid"] = request.user.id
            data["status"] = apply.status
        return render(request, 'banquet/apply_permit.html', data)
    elif obj == "bonus":
        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 a.tm::date >= '{dt_fr}' and a.tm::date <= '{dt_to}' and a.ancestor_id = {ancestor_id}"
        s=f"""select 
                a.id,
                b.name,
                a.table_num,
                a.qty_x,
                a.qty_p,
                a.amt,
                c.name,
                e.name,
                to_char(a.tm,'yyyy-mm-dd HH24:MI:SS')
            from banquet_bonus a 
            left join company_company b on a.agent_id = b.id
            left join product_product c on a.product_id = c.id
            left join banquet_apply d on a.apply_id = d.id
            left join banquet_policy e on a.policy_id = e.id
            {sWhere} """
        
        cur.execute(s)
        rs = cur.fetchall()
        for r in rs:
            data['data'].append([
                r[1],
                r[2],
                r[3],
                r[4],
                float(r[5]),
                r[6],
                r[7],
                r[8],
            ])
    elif obj == "bonus_redeemed":
        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 a.tm::date >= '{dt_fr}' and a.tm::date <= '{dt_to}' and a.ancestor_id = {ancestor_id}"
        s=f"""select 
                a.id,
                b.name,
                c.username || c.last_name,
                a.val,
                a.online_receipt,
                to_char(a.tm,'yyyy-mm-dd HH24:MI:SS')
            from banquet_bonusredeemed a 
            left join company_company b on a.company_id = b.id
            left join company_user c on a.user_id = c.id
            {sWhere} """
        
        cur.execute(s)
        rs = cur.fetchall()
        for r in rs:
            data['data'].append([
                r[1],
                r[2],
                float(r[3]),
                r[4],
                r[5],
            ])
    elif obj == "information_edit":
        id = request.GET.get('id','')
        if id:
            r = Apply.objects.get(id=id)
            data["id"] = id
            data["uid"] = request.user.id
            data["actual_table_num"] = r.actual_table_num
            data["actual_qty_x"] = r.actual_qty_x
        return render(request, 'banquet/information_edit.html', data)
    elif obj == "apply_photos":
        id = request.GET.get('id','')
        if id:
            rs = Apply_Pictures.objects.filter(apply_id=id)
            data["id"] = id
            data["uid"] = request.user.id
            data["photos"] = []
            for r in rs:
                data["photos"].append(r.photo)
        return render(request, 'banquet/apply_photos.html', data)
    elif obj == "manual_settle":
        id = int(request.GET.get('id','0'))
        if id:
            apply = Apply.objects.get(id=id)
            policy = Policy.objects.get(id=apply.strategy_id)
            # cur.execute("select count(0) from banquet_writeoff where apply_id="+str(id)+" and label_pkg = 3")
            # x = cur.fetchone()[0]
            # z = apply.table_num
            bonus_grades = policy.bonus_grades
            for r in bonus_grades:
                vals = []
                for val in r['vals']:
                    if val['product_id'] == str(apply.product_id):
                        vals.append(val)
                r['vals'] = vals
            data['bonus_grades'] = json.dumps(bonus_grades)
            data['status'] = apply.status
            data['id'] = id
            return render(request, 'banquet/manual_settle.html', data)
    elif obj == "manual_settle_list":
        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 a.tm::date >= '{dt_fr}' and a.tm::date <= '{dt_to}' and a.ancestor_id = {ancestor_id}"
        s = f"""
            select 
                a.id,
                (select name from company_company where id = b.company_id),
                coalesce(b.policy,''),
                a.amt,
                a.remark,
                (select username from company_user where id = a.user_id),
                to_char(a.tm,'yyyy-mm-dd HH24:MI:SS')
            from
                banquet_settlerecord a left join
                banquet_apply b on b.id = a.apply_id
            {sWhere}
        """
        cur.execute(s)
        rs = cur.fetchall()
        for r in rs:
            btns = ''
            # btns = btns + "<a href='/banquet/ajax/manual_settle_delete/?id=" + str(
            #         r[0]) + "' class='btn btn-danger btn-sm'>删除</a>"
            data['data'].append([
                r[1],r[2],float(r[3]),r[4],r[5],r[6],btns
            ])
    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")
    elif obj == "strategy":
        return ajaxGetBanquetStrategy(request)
    elif obj == "bonus":
        return ajaxGetBanquetBonus(request)
    elif obj == "bonus_redeem":
        return ajaxBanquetBonusRedeem(request)
    elif obj == "information_edit":
        return ajaxBanquetInformationEdit(request)
    elif obj == "settle":
        return ajaxBanquetSettle(request)
    elif obj == "consumer_scan_analysis":
        id = int(request.GET.get('id','0'))
        return JsonResponse(ajaxBanquetConsumerScanAnalysis(id))
    elif obj == "manual_settle":
        return ajaxBanquetManualSettle(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:
                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 'end_tm' in d and d['end_tm']:
                apply.end_tm = d['end_tm']
            if 'actual_table_num' in d and d['actual_table_num']:
                apply.actual_table_num = d['actual_table_num']
            if 'strategy_id' in d and d['strategy_id']:
                apply.strategy_id = d['strategy_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), "+\
              "to_char(end_tm,'yyyy-mm-dd HH24:MI:SS'), "+\
              "is_closed, "+\
              "to_char(tm_closed,'yyyy-mm-dd HH24:MI:SS'), "+\
              "actual_table_num, "+\
              "strategy_id, "+\
              "(select name from banquet_policy where id = strategy_id), "+\
              "reason, "+\
              "actual_qty_x, "+\
              "actual_qty_p "+\
              "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],
                    'end_tm': r[19],
                    'is_closed': r[20],
                    'tm_closed': r[21],
                    'actual_table_num': r[22],
                    'strategy_id': r[23],
                    'strategy_name': r[24],
                    'reason': r[25],
                    'actual_qty_x': r[26],
                    'actual_qty_p': r[27],
                }

                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':[],
            'writeOffCodes': []
        }
    }
    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)),'')," + \
              "to_char(end_tm,'yyyy-mm-dd HH24:MI:SS'), "+\
              "is_closed, "+\
              "to_char(tm_closed,'yyyy-mm-dd HH24:MI:SS'), "+\
              "actual_table_num, "+\
              "strategy_id, "+\
              "(select name from banquet_policy where id = strategy_id), "+\
              "actual_qty_x,"+\
              "actual_qty_p "+\
              "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],
                'end_tm': r[28],
                'is_closed': r[29],
                'tm_closed': r[30],
                'actual_table_num': r[31],
                'strategy_id': r[32],
                'strategy_name': r[33],
                'actual_qty_x': r[34],
                'actual_qty_p': r[35]
            }
            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
            
            
            cur.execute("select id,(select name from product_product where id = product_id),product_id,label_code,label_pkg from banquet_writeoff "+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],
                    'label_code':r[3],
                    'package':pkg,
                    'package_id':r[4]
                }) 
            res['detail']['writeOffCodes'] = codes
    
    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:
            res['e'] = '无法删除已审核申请'
    else:
        res['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)
    
def ajaxGetBanquetStrategy(request):
    data = {
        'e':'',
        'data': []
    }
    ancestor_id = int(request.GET.get('ancestor_id','0'))
    if ancestor_id:
        cur=connection.cursor()
        s = f"""
            select id, name, to_char(dt_start,'yyyy-mm-dd'), to_char(dt_end,'yyyy-mm-dd'), products 
            from banquet_policy where company_id = {str(ancestor_id)} and now() between dt_start and dt_end
        """
        cur.execute(s)
        rs = cur.fetchall()
        for r in rs:
            data['data'].append({
                'id': r[0],
                'name': r[1],
                'dt_start': r[2],
                'dt_end': r[3],
                'products': r[4],
            })
    else:
        data['e'] = '数据丢失'
    return JsonResponse(data)

def ajaxGetBanquetBonus(request):
    data = {
        'e':'',
        'rebate': [],
        'redeemed': [],
        'total':{
            'rebate': 0,
            'redeemed': 0,
            'residue': 0,
        }
    }
    ancestor_id = int(request.GET.get('ancestor_id','0'))
    agent_id = int(request.GET.get('agent_id','0'))
    if ancestor_id and agent_id:
        cur=connection.cursor()
        # 查询返利记录
        sWhere = f" where a.ancestor_id = {str(ancestor_id)} and a.agent_id = {str(agent_id)}"
        s=f"""select 
                a.id,
                b.name,
                a.table_num,
                a.qty_x,
                a.qty_p,
                a.amt,
                c.name,
                e.name,
                to_char(a.tm,'yyyy-mm-dd HH24:MI:SS')
            from banquet_bonus a 
            left join company_company b on a.agent_id = b.id
            left join product_product c on a.product_id = c.id
            left join banquet_apply d on a.apply_id = d.id
            left join banquet_policy e on a.policy_id = e.id
            {sWhere} """
        cur.execute(s)
        rs = cur.fetchall()
        for r in rs:
            data['rebate'].append({
                'id': r[0],
                'company_name': r[1],
                'table_num': r[2],
                'qty_x': r[3],
                'qty_p': r[4],
                'amt': float(r[5]),
                'product_name': r[6],
                'policy_name': r[7],
                'tm': r[8],
            })
        
        # 查询提现记录
        s="select id,val,to_char(tm,'yyyy-mm-dd HH24:mi:ss') from banquet_bonusredeemed where company_id=" + str(agent_id)
        cur.execute(s)
        for r in cur.fetchall():
            data['redeemed'].append({               
                'tm':r[2],
                'amount':r[1]
            })

        # 取返利总金额，已提现总金额，可提现金额
        s="select coalesce(rval,0.00),coalesce(dval,0.00) from "+\
            "(select "+\
                "(select sum(amt) from banquet_bonus where agent_id="+str(agent_id)+") rval,"+\
                "(select sum(val) from banquet_bonusredeemed where company_id="+str(agent_id)+") dval "+\
            ") t"
        cur.execute(s)
        r=cur.fetchone()
        data['total']={
            'rebate':r[0],
            'redeemed':r[1],
            'residue':r[0]-r[1]
        }

    else:
        data['e'] = '缺少必要参数'
    return JsonResponse(data)

def ajaxBanquetBonusRedeem(request):
    data = {'e': ''}
    
    #data['e']='系统优化中请于7.22日10点后进行提现'   
    #return JsonResponse(data)
    wx_openid=request.POST.get('wxopenid','')
    user_id=request.POST.get('user_id','')
    amount=request.POST.get('amount','') # 单位元
    method=request.POST.get('method','wx')
    us=User.objects.filter(id=int(user_id))
    if not us:
        data['e']='user not exits'
        return JsonResponse(data)
    u=us[0]
    cid=str(u.company_id)
    if not rd_wx.set(cid+':'+user_id, user_id, px=5000, nx=True):
        return JsonResponse({'e':'网络繁忙，请稍后再试'})
    cur=connection.cursor()
    if method=='wx':
        if not wx_openid or not user_id or not amount:
            data['e']='wxopenid,user id,amount required'
            return JsonResponse(data)  
        
        s="select coalesce(rval,0.00),coalesce(dval,0.00) from "+\
            "(select "+\
                "(select sum(amt) from banquet_bonus where agent_id="+cid+") rval,"+\
                "(select sum(val) from banquet_bonusredeemed where company_id="+cid+") dval "+\
            ") t"
        cur.execute(s)
        r=cur.fetchone()
        if float(amount)+float(r[1])>float(r[0]):
            data['e']='可提现金额不足'
            return JsonResponse(data)
        amt=float(amount)
        if amt<1 or amt>200:
            data['e']='当前微信限制每次可提现金额在1-200元范围内,请重新输入提现金额'
            return JsonResponse(data)
        money = amt*100
        
        app_id = 'wxe9a919e0d3c419ee'
        code, message = transfer_batch(wx_openid, money, u.ancestor_id, app_id)
        if code == 200:
            rd=BonusRedeemed(
                company_id=u.company_id,
                ancestor_id=u.company.ancestor_id,
                user_id=u.id,
                val=amt,
                request=json.dumps({'method':'wx','data':{'wx_openid':wx_openid,'money':money,'company_id':u.company_id}}),
                online_receipt=json.dumps(message)
            )
            rd.save()
        else:
            data['e']='err'
            data['msg']=message
    else:
        pass
        # 微信之外的支付暂不做任何处理
    cur.close()
    return JsonResponse(data)
   
def ajaxBanquetInformationEdit(request):
    res = {
        'e':''
    }
    id = int(request.GET.get('id','0'))
    actual_table_num = int(request.GET.get('actual_table_num','0'))
    actual_qty_x = int(request.GET.get('actual_qty_x','0'))
    if id and actual_table_num and actual_qty_x: 
        rs = Apply.objects.filter(id=id)
        if rs:
            a = rs[0]
            a.permit_tm = datetime.datetime.now()
            a.actual_table_num = actual_table_num
            a.actual_qty_x = actual_qty_x
            a.save()
        else:
            res['e']='记录不存在'
    else:
        res['e'] = '数据丢失'

    return JsonResponse(res)
    
def ajaxBanquetSettle(request):
    data = {
        'e':''
    }
    id = int(request.GET.get('id','0'))
    actual_table_num = int(request.GET.get('actual_table_num','0'))
    if id: 
        apply = Apply.objects.get(id=id)
        if apply.status == 1:
            if actual_table_num:
                apply.actual_table_num = actual_table_num
                cur = connection.cursor()
                cur.execute("select count(0) from banquet_apply_codes where apply_id = " + str(apply.id))
                apply_codes_cnt = cur.fetchone()[0]
                cur.execute("select count(0) from banquet_writeoff where apply_id = " + str(apply.id))
                writeoff_cnt = cur.fetchone()[0]
                
                apply.status = 3 #结算审核中
                # 如果提交箱数和核销相同 且 实际桌数与申请桌数相同 则自动返利 并修改状态为结算中
                if apply_codes_cnt == writeoff_cnt and apply.actual_table_num == apply.table_num:
                    if apply.ancestor_id == 208869:
                        d = ajaxBanquetConsumerScanAnalysis(id)
                        #print(d)
                        if d['scan_rate'] >= 0.5:
                            apply.status = 4 #结算中
                    else:
                        apply.status = 4 #结算中
                        
                apply.save()
            else:
                data['e'] = '无实际桌数，不能结算'
        else:
            data['e'] = '当前状态不可结算'
    else:
        data['e'] = '数据丢失'

    return JsonResponse(data)
 
def ajaxBanquetConsumerScanAnalysis(id):
    data = {
        'e':'',
        'bottle_num': 0,
        'scan_num': 0,
        'scan_rate': 0
    }
    if id:
        code4s = {}
        cur=connection.cursor()
        cur.execute("select code from banquet_apply_codes where apply_id = "+str(id))
        rs = cur.fetchall()
        for r in rs:
            label_code = r[0]
            c4 = label_code[:4]
            if c4 in code4s:
                code4s[c4].append(label_code)
            else:
                code4s[c4] = [label_code]
        bottle_num = 0
        bottles = []
        #print(code4s)
        for c in code4s:
            cstr  = ','.join("'{0}'".format(i) for i in code4s[c])
            cur.execute(f"""
                SELECT 
                    code,
                    (SELECT cap FROM mes_bottlcap where label_code = a.code and cap is not null and cap !='' limit 1)
                from label.t{c} a where a.parent_id in (
                    SELECT id from label.t{c} where code in (
                        {cstr}
                    )
                )
                
            """)
            rs = cur.fetchall()
            bottle_num = bottle_num + len(rs)
            for r in rs:
                code = r[0]
                if r[1]:
                    code = decrypt_11h(r[1])
                bottles.append(code)
        data['bottle_num'] = bottle_num
        if bottles:
            s = "SELECT count(DISTINCT(label_code)) FROM sales_labelscan where label_code in (" +(','.join("'{0}'".format(i) for i in bottles)) +")"
            #print(s)
            cur.execute(s)
            r = cur.fetchone()
            data['scan_num'] = r[0]
            data['scan_rate'] = round(r[0] / bottle_num, 2)
            cur.execute(f"UPDATE banquet_apply set scan_num = {str(r[0])}, bottle_num = {str(bottle_num)} where id = {str(id)}")
    else:
        data['e'] = '数据丢失'

    return data
 
def ajaxBanquetManualSettle(request):
    data = {'e': ''}
    id = int(request.POST.get('id','0'))
    amt = float(request.POST.get('amt','0'))
    remark = request.POST.get('remark','')
    u = request.user
    apply = Apply.objects.filter(id=id, ancestor_id=u.ancestor_id)
    if apply:
        apply = apply[0]
        # 判断是不是公司级的
        if u.company_id == u.ancestor_id:
            if apply.status == 1 or apply.status == 3:
                apply.status = 4 #结算中
                apply.save()
                s = SettleRecord(
                    ancestor_id=apply.ancestor_id,
                    user_id=u.id,
                    apply_id=apply.id,
                    amt=amt,
                    remark=remark
                )
                s.save()
            else:
                data['e'] = '当前状态无法结算'
        else:
            data['e'] = '无权限'
    else:
        data['e'] = '无数据'
    return JsonResponse(data)

# 2024-06-24
def policy(request,action='list',id=None):
    u=request.user
    c=u.company   
    html='policy_'+action+".html"    

    show_add=False
    can_edit=False 
    
    if c.level_id==1 and u.has_perm('company.sys_admin'):
        show_add=True
        can_edit=True

    data={'action':action,'show_add':show_add,'can_edit':can_edit}
    if action=='edit':     
        if id:
            obj=Policy.objects.get(id=id)
        else:
            obj=Policy(company_id=c.id)
        if request.method=='GET':            
            form=PolicyForm(instance=obj)
        else:
            form=PolicyForm(request.POST,instance=obj)
            
            print(form)

            if form.is_valid():
                form.save()
            #return JsonResponse({'e':''})

        data['form']=form
    else:
        clmns=[_('政策名称'),_('开始日期'),_('结束日期'),_('产品限定')]
        d=[]
        for p in Policy.objects.filter(company_id=c.id):
            a=[]
            for pp in p.products:
                a.append(pp['name'])
            d.append([
                '<a href="/banquet/policy/edit/'+str(p.id)+'/">'+p.name+'</a>',
                p.dt_start.strftime('%Y-%m-%d'),
                p.dt_end.strftime('%Y-%m-%d'),
                ",".join(a)
            ])
        data['clmns']=clmns
        data['data']=d

   

    return render(request,'banquet/'+html,data)


# 2024-06-28
# 扫码核销接口
def writeoff_labelscan(request,agent_id):
    data={'e':'','package':{},'label':{},'product_name':'','product_id':0}
    e = time_key_check(request, sK)
    if e:
        data['e'] = e
        return JsonResponse(data)    

    user_id=request.GET.get('user_id','')
    label_code=request.GET.get('label_code','')
    apply_id=request.GET.get('apply_id','')

    # 经纬度
    lng=request.GET.get('lng','')
    lat=request.GET.get('lat','')
    city=request.GET.get('city',None)
    province=request.GET.get('province',None)
    policy_id=None

    if user_id and label_code and apply_id:
        us=User.objects.filter(id=user_id)
        cur=connection.cursor()
        if not us:
            data['e']=_('用户id不存在')
        else:
            u=us[0]
            c=u.company
            if c.id!=int(agent_id):
                data['e']=_('帐号与公司不匹配')
            else:    

                # 需要核销的宴会申请必有对应的政策方案
                s="select location_constrain,id from banquet_policy "+\
                    "where id=(select strategy_id from banquet_apply where id="+apply_id+")"                
                # 测试
                #s="select location_constrain,id from banquet_policy "+\
                #    "where id=1"

                cur.execute(s)
                r0=cur.fetchone()
                
                if not r0:
                    data['e']=_('宴会申请没有对应的正策方案，无法核销')
                else:
                    policy_id=r0[1]
                    # 测试固定id
                    #policy_id=1
                    
                    cid=c.id
                    aid=c.ancestor_id
                    q=LabelQuery(aid,label_code)                 
                    pk=q.infor['label_pk_id']
                    product_id=None
                    if not pk:
                        data['e']=_('标签号码错误')
                    else:
                        # 2024-07-01
                        data['label']={
                            'pk_id':pk,
                            'pk_code':q.infor['label_pk_code']
                        }                  

                        pk=str(pk)
                        data['package']=q.package    
                        # 标签是否在宴会申请附带的codes内,标签是否已扫码核销
                        s="select (select tm from banquet_writeoff where label_code='"+label_code+"' limit 1),"+\
                            "product_id "+\
                            "from banquet_apply_codes where apply_id="+apply_id+" and label_pk_id="+pk                 
                        cur.execute(s)
                        r=cur.fetchone()
                        if r:
                            if r[0]:
                                data['e']=_('当前标签已于 %s 扫码核销' % r[0].strftime('%Y-%m-%d %H:%M:%S'))
                            else:
                                # 码类型检查 
                                #if q.package['id']==3:
                                    # 核销应该扫瓶标
                                #    data['e']=_('请扫瓶码核销')
                                #else:
                                product_id=r[1]
                                cur.execute("select name from product_product where id = "+str(product_id))
                                data['product_id'] = product_id
                                data['product_name'] = cur.fetchone()[0]
                                # 宴会政策是否限制扫码区域
                                if r0[0]:
                                    if not lng or not lat:
                                        data['e']=_('依据宴会政策，需要扫码地理位置')
                                    else:                                    
                                        # 扫码地点是否在销售区域内
                                        a=[]
                                        if c.adm_ids:
                                            for i in c.adm_ids:
                                                a.append(str(i))
                                        if a:                                        
                                            # 已设置经销商授权销售区域
                                            s="select  public.ST_Contains("+\
                                                "public.ST_Union(array(SELECT geom_gcj02 FROM gis.adm_map_cn where id in("+",".join(a)+"))),"+\
                                                "public.ST_GeomFromText('POINT("+str(lng)+" "+str(lat)+")',4326)::public.geometry"+\
                                            ")"
                                            cur.execute(s)
                                            r=cur.fetchone()
                                            if not r[0]:
                                                data['e']=_('扫码地点不在授权销售区域内') 
                        else:
                            data['e']=_('当前标签不在宴会申请的箱号内')  

                        if not data['e']:
                            # 保存宴会扫码核销记录
                            point=[]
                            if lng and lat:
                                point=[float(lng),float(lat)]
                            w=Writeoff(policy_id=policy_id,apply_id=apply_id,ancestor_id=aid,agent_id=cid,user_id=user_id,               
                                product_id=product_id,lng_lat=point,province=province,city=city,   
                                label_code =label_code, label_pk_id =pk, label_id=q.infor['label_id'],label_pkg=q.package['id']
                            )
                            w.save()
        cur.close()
    else:
        data['e']='user id or labe code or apply id missing'

    return JsonResponse(data)



# 2024-06-29 经销商获取末关闭的宴会申请
def get_applys(request,agent_id):
    data={'apps':[],'e':''}    
    e = time_key_check(request, sK)
    if e:
        data['e'] = e
        return JsonResponse(data)    

    cur=connection.cursor()
    s="select id,user_name,table_num,to_char(tm,'yyyy-mm-dd hh24:mi:ss'),"+\
        "(select count(*) from banquet_apply_codes where apply_id=a.id),"+\
        "coalesce(w.qty,0) "+\
        "from "+\
        "(SELECT id,user_name,table_num,tm FROM banquet_apply "+\
            "where company_id="+agent_id+" and is_closed=false) a "+\
        "left outer join "+\
        "(select apply_id,count(*) qty from "+\
            "(select apply_id,label_pk_id from banquet_writeoff "+\
                "where agent_id="+agent_id+" group by apply_id,label_pk_id"+\
            ") w "+\
            "group by apply_id"+\
        ") w "+\
        "on a.id=w.apply_id"
    cur.execute(s)
    for r in cur.fetchall():
        data['apps'].append({
            'id':r[0],
            'user_name':r[1],
            'table_num':r[2],
            'tm':r[3],
            'qty_apply':r[4],
            'qty_writeoff':r[5]
        })
    return JsonResponse(data)



# 2024-07-01 宴会结算 1 奖励 2 实物结余
@csrf_exempt
def close_applys(request,agent_id,apply_id=None):
    data={'e':'','apply_ids':[]}

    # pos方法
    if request.method=='GET':
        data['e']=_('请求方式错误')
        return JsonResponse(data)   

    d=json.loads(request.body)

    if 'user_id' in d:
        uid=str(d['user_id'])
    else:
        data['e']='user id missing'   
        return JsonResponse(data)

    if apply_id:
        sAnd=" and id="+apply_id
    else:
        sAnd=""

    sW="where company_id="+agent_id+" and is_closed=false and strategy_id is not null"
    # 测试
    #sW="where company_id="+agent_id+" and is_closed=false"

    cur=connection.cursor()
    cur.execute("select ancestor_id from company_company where id="+agent_id)
    r=cur.fetchone()
    aid=str(r[0])

    # 要结算的宴会申请
    s="select strategy_id,id,coalesce(actual_table_num,0),product_id,ancestor_id FROM banquet_apply "+sW+sAnd 
    cur.execute(s)
    rs=cur.fetchall()
    amt='0'
    for r in rs:        
        id=str(r[0])      
        app=str(r[1])

        # 宴席桌数
        z=str(r[2])
        product_id = r[3]
        ancestor_id = r[4]

        ps=Policy.objects.filter(id=id)
        if not ps:
            return JsonResponse(data)
        p=ps[0]
        if p.sql:
            amt=p.sql

        if p.consumer_scan_strategy:
            cur.execute(f"""
                select count(0) from sales_consumerwinner where company_id = {str(ancestor_id)} and xcode in (
                    select label_code from banquet_writeoff where apply_id={str(app)}
                )
            """)
            scan_num = cur.fetchone()[0]
            for s in p.consumer_scan_strategy:
                if s['product_id'] == 0 or s['product_id'] == product_id:
                    award = Award.objects.get(id=s['award_id'])
                    award_num = 0
                    remark = ''
                    # 如果单个消费者中奖达到1次
                    if s['condition'] == "consumer_win":
                        award_num = scan_num
                        remark = '单个消费者中奖达到'+str(scan_num)+'次'
                    # 如果消费者中奖累计达到N次
                    elif s['condition'] == "consumer_win_total":
                        award_num = math.floor(scan_num/s['num'])
                        remark = '当消费者中奖累计达到'+str(s['num'])+'次'
                    points = 0
                    if award.awardcat_id == 2:
                        points = award.points * award_num
                    if award_num:
                        aw = AgentWinner(
                            ancestor_id=ancestor_id,
                            company_id=agent_id,
                            award_id=award.id,
                            tm_redeemed=now(),
                            award_name=award.name,
                            award_points=award.points,
                            award_amount=award.amount,
                            award_img=award.photo.url,
                            award_cat=award.awardcat.name,
                            award_num=award_num,
                            banquet_apply_id=app,
                            remark=remark
                        )
                        aw.save()
                        if points:
                            update_member_points_agent(agent_id,points,'消费者宴会扫码返利')
        
        data['apply_ids'].append(r[1])

        # 箱数
        x_num = 'count(*)'
        
        # 查询手动结算记录，如果有手动结算，则以手动结算为准
        cur.execute("select amt from banquet_settlerecord where apply_id = "+app+" order by id desc limit 1")
        r = cur.fetchone()
        if r:
            amt = str(r[0])
        else:
            # # 查询扫码率 过滤不需要扫码的产品
            if ancestor_id == 208869:
                include_product_ids = [10124, 10125, 10126, 10127, 10128]
                if product_id in include_product_ids:
                    analysis = ajaxBanquetConsumerScanAnalysis(int(app))
                    if analysis['bottle_num']:
                        x_num = str(math.floor(analysis['bottle_num'] / 6))

        print(amt)
        if amt !='0':
            # 写入奖励记录产品名称
            s="insert into banquet_bonus(ancestor_id,agent_id,policy_id,apply_id,product_id,qty_x,table_num,amt,qty_p) "+\
                "select "+aid+","+agent_id+","+id+","+app+",product_id,X,Z,"+amt+",P from "+\
                "(select product_id,"+x_num+" X, "+z+" Z,sum(P) P from "+\
                    "(select label_pk_id,product_id,"+\
                        "(select count(*) from banquet_writeoff where apply_id="+app+" and label_pk_id=w.label_pk_id and label_pkg!=3) P "+\
                        "from banquet_writeoff w where apply_id="+app+\
                            " group by label_pk_id,product_id"+\
                    ") g group by product_id"+\
                ") gg"   
            print(s)
            cur.execute(s)
    
        # 写入剩余实物
        s="INSERT INTO banquet_surplus(ancestor_id,agent_id,policy_id,apply_id,product_id,qty_x,qty_p,dt,tm) "+\
             "select "+aid+","+agent_id+","+id+","+app+",product_id,X,0,now(),now() from "+\
            "(select product_id,sum(X) X from "+\
                "(select product_id,1 X from banquet_apply_codes a where apply_id="+app+\
                    " and not exists(select id from banquet_writeoff where label_pk_id=a.label_pk_id)"+\
                ") g group by product_id "+\
            ") gg"   
        cur.execute(s)


        # 修改宴会申请is_cose状态
        s="update banquet_apply set "+\
            "is_closed=true,tm_closed=now(),closed_by_id="+uid+",status=5 "+\
            "where id="+app
        cur.execute(s)

    cur.close()
    return JsonResponse(data)
