from django.shortcuts import render

# Create your views here.

from django.apps import apps
from utils.views import *
from django.contrib.auth.decorators import login_required
from django.utils.translation import gettext_lazy as _
from django.db import connection

from product.forms import *
from company.forms import *
from entertainment.forms import *
from entertainment.models import Category as EntCategory

import psycopg2
import json
from product.models import *
from django.contrib.auth.models import Group
from inventory.forms import *
from sales.forms import *
from django.http import HttpResponse, HttpResponseRedirect, JsonResponse
import os
from sales.models import Activities,ChannelPromotions,KangNian_WriteOffLabel
from promotion.models import *
from mes.models import *
from mes.forms import *



#2021-04-02
def get_obj_info(app_name,obj_name,id):
    data={'e':'','data':{}}    
    cur=connection.cursor()
    if app_name=='inventory' and obj_name=='stack':
        s="select code,coalesce(location,'空白'),coalesce(remark,'空白'),"+\
            "product_id,(select name from product_product where id=product_id),"+\
            "store_id,(select name from inventory_storehouse where id=store_id) "+\
            "from inventory_stack where id="+str(id)
        cur.execute(s)
        r=cur.fetchone()        
        if r:                    
            d={
                'e':'',
                'id':int(id),
                'code':r[0],
                'location':r[1],
                'remark':r[2],
                'product':{'id':r[3],'name':r[4]},
                'store':{'id':r[5],'name':r[6]},                
                'labels':[]
            }
            s="select coalesce((select code_b FROM label.lbl_abcode where code_a=label_code),label_code),"+\
                "case label_pkg when 1 then '瓶' when 2 then '盒' when 3 then '箱' else '未知' end,"+\
                "coalesce((select name from product_product where id=product_id),''),"+\
                "to_char(tm,'yyyy-mm-dd HH24:MI:ss') "+\
                "from inventory_stackdetails where stack_id="+str(id)
            cur.execute(s)
            for rr in cur.fetchall():
                d['labels'].append( [rr[0],rr[1],rr[2],rr[3] ])
            data=d
        else:
            data['e']='stack id error'
    return data



#@tj_login_required
def agent_lbl_info(request,user_id=None):
    data={'e':'',
          'label_pk_id':0,
          'label_id':0,
          'product_id':0,
          'product_name':'',
          "label_pkg_id":0,
          'label_pkg_name':''
    }
    if user_id:
        us=User.objects.filter(id=int(user_id))
        if not us:
            return JsonResponse({'e':'Wrong user'})
        u=us[0]
    else:
        u=request.user
    c=u.company
    code = request.GET.get('code','')
    sWhere = " where label_code='" + code + "' and vtype_id in (1,2)  and is_delete=false"
    if c.level_id==1:
        sWhere=sWhere +" and  ancestor_id="+str(c.id)
    else:
        sWhere=sWhere+" and company_id="+str(c.id)
    cur=connection.cursor()
    cur.execute("select label_id,label_pk_id,label_pkg,product_id,"+\
        "coalesce((select name from product_product where id=product_id),'') "+\
        "from logistics.inventory_stkdetails "+sWhere)
    r=cur.fetchone ()
    if r:
        data['label_id']=r[0]
        data['label_pk_id']=r[1]
        data["label_pkg_id"]=r[2]
        data['product_id']=r[3]
        data['product_name']=r[4]
        if r[2]==1:
            data['label_pkg_name']=_('瓶标')
        if r[2]==3:
            data['label_pkg_name']=_('箱标')
        if r[2]==2:
            data['label_pkg_name']=_('盒标')
    else:
        cur.execute("SELECT id,package_id,parent_id FROM label.labelinfo where code='"+code+"'")
        r=cur.fetchone()
        if r:
            data['label_id'] = r[0]
            data["label_pkg_id"] = r[1]
            if r[2]:
                data['label_pk_id'] = r[2]
            else:
                data['label_pk_id'] = r[0]
            if r[1]==1:
                data['label_pkg_name']=_('瓶标')
            if r[1]==3:
                data['label_pkg_name']=_('箱标')
            if r[1]==2:
                data['label_pkg_name']=_('盒标')
    return JsonResponse(data)


@tj_login_required
def edit_obj(request,obj_name,id=None):
    d = {
        'product': ['产品', 'product', 'Product'],
        'series':['产品系列','product','Series'],
        'store': ['仓库', 'inventory','Storehouse'],
        'user': ['帐号', 'company', 'User'], 
        'agent': ['经销商', 'company', 'Company'],     
        #2021-08-09
        'outlet': ['加盟商门店', 'company', 'Outlet'],
        'productprop': ['产品属性', 'product','ProductProp'],        
        'sal_division': ['营销战区', 'company','SalesDivision'],
        'sal_subdivision': ['营销分区', 'company','SubSalDivision'],
        'prd_plant': ['工厂/厂区', 'company', 'PrdPlant'],
        'prd_shop': ['生产车间', 'company', 'PrdWorkshop'],
        'prd_line': ['生产线', 'company', 'PrdLine'],        
        'prd_team': ['生产班组', 'company','PrdTeam'],
        'prd_qc': ['质检员', 'company', 'PrdQc'],
        'recipient': ['业务员领用', 'inventory', 'RecipientsBatch'],
        'companycfg': ['公司设置', 'company', 'CompanyCfg'],
        #'prd_workshop': ['生产班组', 'company', 'PrdWorkshop'],
        'promat': ['促销物料', 'promotion', 'Material'],

        'brandrecommender': ['公司管理', 'company', 'BrandRecommender'],
        #2021-03-01
        'rebateinvoice': ['渠道促销', 'sales', 'RebateInvoice'],
        'entapplication':['领用核销', 'entertainment','EntApplication'],
        'actapplication':['领用核销', 'entertainment','ActApplication'],

        'rebateperiod':['返利会计期', 'sales','RebateClosing'],

        #2021-05-29
        'materials':['原材料', 'mes','Materials'],
        'semifinishedgoogs':['半成品', 'mes','SemiFinishedGoods'],
        'packaging':['包装物料', 'mes','Packaging'],
        'bom':['物料清单', 'mes','BOM'],

        'kangnian_inventorymanipulation': ['库存管理', 'sales', 'Kangnian_inventorymanipulation'],
        'Kangnian_inventorymanipulation_detail': ['库存明细', 'sales', 'Kangnian_inventorymanipulation_detail'],
        'kangnian_storehouse_management_detail': ['仓库库存明细', 'sales', 'KangNian_StorehouseInventoryManipulation'],
        'channel': ['渠道列表', 'company', 'Channel'],
        'channel_level': ['渠道等级', 'company', 'Channel_Level'],
        'uploadfiles': ['公司资料', 'company', 'UploadFiles'],
        
    }
    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

    #2020-11-06    
    if obj_name not in ['user','companycfg','outlet']:        
        obj.company=request.user.company

    if obj_name=='store':              
        if request.method == 'GET':
            form = StoreForm(instance=obj,user=request.user)       
        elif request.method == "POST":   
            form = StoreForm(request.POST, instance=obj,user=request.user)
            if form.is_valid():
                form.save()
                is_saved = True
    elif obj_name=='series':
        if request.method == 'GET':
            form = SeriesForm(instance=obj,user=request.user)       
        elif request.method == "POST":
            form = SeriesForm(request.POST, instance=obj,user=request.user)
            if form.is_valid():
                form.save()
                is_saved = True
    elif obj_name=='prd_plant':        
        if request.method == 'GET':
            form = PlantForm(instance=obj,user=request.user)       
        elif request.method == "POST":
            form = PlantForm(request.POST, instance=obj,user=request.user)
            if form.is_valid():
                form.save()
                is_saved = True
    elif obj_name=='prd_shop':        
        if request.method == 'GET':
            form = PrdShopForm(instance=obj,user=request.user)       
        elif request.method == "POST":
            form = PrdShopForm(request.POST, instance=obj,user=request.user)
            if form.is_valid():
                form.save()
                is_saved = True
    elif obj_name=='prd_line':        
        if request.method == 'GET':
            form = PrdLineForm(instance=obj,user=request.user)       
        elif request.method == "POST":
            form = PrdLineForm(request.POST, instance=obj,user=request.user)
            if form.is_valid():
                form.save()
                is_saved = True
    elif obj_name=='prd_team':        
        if request.method == 'GET':
            form = PrdTeamForm(instance=obj,user=request.user)       
        elif request.method == "POST":
            form = PrdTeamForm(request.POST, instance=obj,user=request.user)
            if form.is_valid():
                form.save()
                is_saved = True
    elif obj_name=='productprop':        
        if request.method == 'GET':
            form = PropForm(instance=obj,user=request.user)       
        elif request.method == "POST":
            form = PropForm(request.POST, instance=obj,user=request.user)
            if form.is_valid():
                form.save()
                is_saved = True
    elif obj_name=='sal_division':        
        if request.method == 'GET':
            form = SalesDivisionForm(instance=obj,user=request.user)       
        elif request.method == "POST":
            form = SalesDivisionForm(request.POST, instance=obj,user=request.user)
            if form.is_valid():
                form.save()
                is_saved = True
    elif obj_name=='sal_subdivision':        
        if request.method == 'GET':
            form = SalSubDivisionForm(instance=obj,user=request.user)       
        elif request.method == "POST":
            form = SalSubDivisionForm(request.POST, instance=obj,user=request.user)
            if form.is_valid():
                form.save()
                is_saved = True
    #2021-08-09
    elif obj_name=='outlet':
        if request.method == 'GET':
            c=request.user.company
            form = OutletForm(instance=obj)     
            form.fields['series'].queryset=Series.objects.filter(company_id=c.ancestor_id) 
            form.fields['status'].queryset=OutletStatus.objects.filter(company_id=c.ancestor_id)             
        elif request.method == "POST":
            u=request.user
            form = OutletForm(request.POST,instance=obj)
            if form.is_valid():
                o=form.save(commit=False)
                tm=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
                if id:                
                    o.edit_by_id=u.id
                    o.edit_tm=tm
                else:
                    o.create_by_id=u.id
                    o.create_tm=tm
                o.save()   
                is_saved = True       
                return HttpResponseRedirect('/obj/list/outlet/')
        return render(request,'company/franchise/edit_outlet.html',{'form':form,'is_saved':is_saved}) 

    elif obj_name=='agent':
        c=request.user.company
        hc = c.hierarchy
        cfg_franchise = hc.cfg_company.franchise_mgt
        is_saved = False
        parent = request.user.company
        if cfg_franchise == 0:  # 无加盟商体系管理
            sync_ty = '0'            
            #children = []
            provinces=[]
            products = []
            adms = []
            series = []
            channel_levels = []
            myadms={}
            cur=connection.cursor()
            cur.execute("select id,name from gis.adm where level=1 order by name")
            for r in cur.fetchall():
                provinces.append([r[0],r[1]])
            #for c in m.objects.filter(parent_id=parent.id):
            #    children.append({'label': c.name, 'value': c.id})

            #当前用户公司层级为 公司级
            #if parent.level.sn==0:
            #    children.append({'label': parent.name, 'value': parent.id})
            if id:
                obj = m.objects.get(id=int(id))
                if request.method=='GET':
                    s="select coalesce(province_gis_id,0),coalesce((select name from gis.adm where id=province_gis_id),''),"+\
                        "coalesce(city_gis_id,0),coalesce((select name from gis.adm where id=city_gis_id),''),"+\
                        "coalesce(county_gis_id,0),coalesce((select name from gis.adm where id=county_gis_id),'') "+\
                        "from company_company where id="+id
                    cur.execute(s)
                    rr=cur.fetchone()
                    if rr:
                        myadms={'province':[rr[0],rr[1]],'city':[rr[2],rr[3]],'county':[rr[4],rr[5]]}
            else:
                obj = m()
                obj.hierarchy_id=parent.hierarchy_id
                obj.version=parent.version
                obj.category_id = parent.category_id      
            
            cur=connection.cursor()      
            aid = '0'
            if id:
                aid = id
            if request.method == 'GET':
                # 默认同步到透云
                sync_ty = '1'
                form = AgentForm(instance=obj, user=request.user)
                if id:
                    s="select id,name from product_product where array[id] <@ "+\
                      "(select products from company_company where id="+id+") order by name"
                    cur.execute(s)
                    for r in cur.fetchall():
                        products.append([r[0],r[1]])
                    s = "SELECT id,case when level=1 then name else "+\
                        "(select name from gis.adm where id=a.id_1)||name end FROM gis.adm a "+\
                        "where array[id] <@ (select adm_ids from company_company where id="+id+")"

                    s = "SELECT id,case level when 1 then name "+\
                        "when 2 then (select name from gis.adm where id=a.id_1)||name "+\
                        "when 3 then (select name from gis.adm where id=a.id_1)||(select name from gis.adm where id=a.id_2)||name "+\
                        "end  "+\
                        "FROM gis.adm a where array[id] <@ (select adm_ids from company_company where id="+id+")"
                    cur.execute(s)
                    for r in cur.fetchall():
                        adms.append([r[0], r[1]])
            else:
                form = AgentForm(request.POST, instance=obj, user=request.user)
                sync_ty = request.POST.get('sync_ty','0')
                if form.is_valid():
                    # 若用户为非公司级，其添加的经销商所属战区/分区/归属公司体系在models.save已处理；是否为终端店，前端已指定
                    agt = form.save()
                    is_saved = True
                    #print(agt.lng_lat);
                #2020-04-24 所有酒鬼客户，全部同步到透云
                #if obj.hierarchy_id == 1 and obj.level_id>1:
                #2021-02-24
                if obj.ancestor_id   == 1 and obj.level_id>1:
                    if id:
                        m = 'edit'
                    else:
                        m = 'add'
                    ty_agent(m, obj)
                #return HttpResponseRedirect('/obj/list/' + obj_name.lower() + '/')
            r = requests.request('GET', request.scheme+"://"+request.get_host()+'/gis/adm/1/')
            prs = json.loads(r.text)
            #2020-04-21 先禁用 同步到透云
            jg_ty=False
            
            if id:
                s = "SELECT id,name,(select count(id) from company_company where series_more @> Array[s.id] and id = "+id+") "+\
                        "FROM product_series s where company_id = "+str(hc.id)
                cur.execute(s)
                for r in cur.fetchall():
                    series.append([r[0], r[1],r[2]])
                    
                s = "SELECT id,name,(select count(id) from company_company where channel_level_ids @> Array[s.id] and id = "+id+") "+\
                        "FROM company_channel_level s where ancestor_id = "+str(hc.id)
                cur.execute(s)
                for r in cur.fetchall():
                    channel_levels.append([r[0], r[1],r[2]])
                
            else:
                s = "SELECT id,name,0 "+\
                        "FROM product_series s where company_id = "+str(hc.id)
                cur.execute(s)
                for r in cur.fetchall():
                    series.append([r[0], r[1],r[2]])
                
                s = "SELECT id,name,0 "+\
                        "FROM company_channel_level s where ancestor_id = "+str(hc.id)
                cur.execute(s)
                for r in cur.fetchall():
                    channel_levels.append([r[0], r[1],r[2]])
                
            cur.close()
            return render(request, 'obj/agent.html', {'form': form, 'products': products,
                'parent': parent, 'prs': prs, 'is_saved': is_saved,'adms':adms,'level': obj.level,
                'jg_ty': jg_ty, 'sync_ty': sync_ty,'provinces':provinces,'myadms':myadms,'series':series,'channel_levels':channel_levels} )
        else:
            c=request.user.company
            if id:
                obj=Company.objects.get(id=int(id))
            else:
                obj = Company(parent_id=parent.id)
                obj.hierarchy_id = parent.hierarchy_id
                obj.version = parent.version
                obj.category_id = parent.category_id
            if request.method=="GET":
                form=AgentForm(instance=obj,user=request.user)
                # 2020-011-18 状态单选
                form.fields['franchisestatus'].queryset = FranchiseStatus.objects.filter(company_id=c.id, is_delete=False)
                form.fields['series'].queryset = Series.objects.filter(company_id=c.id)
                #易臻国际 不区分战区，只选择分区（归属部门
                if c.id==19267:
                    form.fields['sub_division'].queryset=SubSalDivision.objects.filter(company_id=c.id).order_by('code')
                formset=OutletFormset(instance=obj)
                empty_form=formset.empty_form
                empty_form.fields['series'].queryset=Series.objects.filter(company_id=c.id)                
                empty_form.fields['status'].queryset=OutletStatus.objects.filter(company_id=c.id)                  
                for f in formset.forms:
                    f.fields['series'].queryset=Series.objects.filter(company_id=c.id).order_by('sn') 
                    f.fields['status'].queryset=OutletStatus.objects.filter(company_id=c.id)
            else:
                form=AgentForm(request.POST,instance=obj, user=request.user)
                # 2020-011-18 状态单选
                form.fields['franchisestatus'].queryset = FranchiseStatus.objects.filter(company_id=c.id, is_delete=False)
                form.fields['series'].queryset = Series.objects.filter(company_id=c.id)
                if c.id==19267:
                    form.fields['sub_division'].queryset=SubSalDivision.objects.filter(company_id=c.id).order_by('code')
                formset=OutletFormset(request.POST,instance=obj)
                empty_form=formset.empty_form  
                empty_form.fields['series'].queryset=Series.objects.filter(company_id=c.id)                
                empty_form.fields['status'].queryset=OutletStatus.objects.filter(company_id=c.id)                  
                for f in formset.forms:
                    f.fields['series'].queryset=Series.objects.filter(company_id=c.id) 
                    f.fields['status'].queryset=OutletStatus.objects.filter(company_id=c.id)


                if form.is_valid():
                    agt=form.save()
                    if formset.is_valid(): 
                        pass
                    formset.save()
                    is_saved=True    
                    #return HttpResponseRedirect('/obj/list/agent/')    
            html='company/franchise/franchise.html'    
            #if request.user.id==11783:
            #    html='company/franchise/franchise_admin.html'
            #return render(request,html,{'form':form,'formset':formset,'is_saved':is_saved,'company':parent,'empty_form':empty_form})
            return render(request,html,{'form':form,'formset':formset,'is_saved':is_saved,'company':parent,'empty_form':empty_form,'id':id})
    elif obj_name=='product':
        c_id=request.user.company.id
        #if id:
        #    obj.company_id=c_id
        #if id:
        #    obj = m.objects.get(id=int(id))
        #else:
        #    obj = m()
        #    if request.user.company:
        #        obj.company_id = c_id
        pid = 0
        obj.company_id=c_id
        if id:
            pid = id           
        
        if request.method == 'GET':
            props = get_product_props(c_id, pid)
            form = ProductForm(instance=obj,user=request.user)        
        elif request.method == "POST":
            form = ProductForm(request.POST,request.FILES, instance=obj,user=request.user)
            if form.is_valid():
                p = form.save()
                cnt = request.POST.get('rec_cnt')
                for n in range(int(cnt)):
                    rec_id = request.POST.get('rec_id_'+str(n+1), '')
                    pro_id = request.POST.get('pro_id_'+str(n+1), '')
                    remark = request.POST.get('remark_'+str(n+1), '')
                    if rec_id:
                        o = PrdProps.objects.get(id=int(rec_id))
                    else:
                        o = PrdProps()
                    o.product = p
                    if pro_id:
                        o.prd_property_id = int(pro_id)
                        o.remark = remark
                        o.save()
                        # 如果表单有设置箱盒瓶比例
                        if o.prd_property.categoryprop.name == 'package':
                            a = o.prd_property.value.split('*')
                            if len(a) == 1:
                                p.qty_x = 1
                            elif len(a) == 2:
                                p.qty_x = int(a[0])
                                p.qty_p = int(a[1])
                            elif len(a) == 3:
                                p.qty_x = int(a[0])
                                p.qty_h = int(a[1])
                                p.qty_p = int(a[2])*int(a[1])
                            p.save()
                is_saved = True
            props = get_product_props(obj.company_id, pid)
        has_series=False      
        if Series.objects.filter(company_id=c_id):
            has_series=True


        # 2020-07-20
        cfg = None
        cfgs = CompanyCfg.objects.filter(company_id=request.user.company_id)
        if cfgs:
            cfg = cfgs[0]
        return render(request, 'obj/product.html', {'form': form, 'props': props, 'is_saved': is_saved,'has_series':has_series,'cfg':cfg})
        #return render(request, 'obj/product.html', {'form': form, 'props': props, 'is_saved': is_saved,'has_series':has_series})
    elif obj_name=='user':
        #2021-05-04        
        rbac=False        

        c = request.user.company
        if c.ancestor_id==1644:
            rbac=True
        company_src = []
        groups=[] 
        mygroups=[]
        if request.method == 'GET':
            obj.password=''   
            #form = UserForm(instance=obj)  
            if id:
                form=UserChangeForm(instance=obj)
            else:
                form=UserCreateForm(instance=obj)
        elif request.method == "POST":
            group_ids = request.POST['group_ids']
            #form = UserForm(request.POST,instance=obj)            
            if id:
                form=UserChangeForm(request.POST,instance=obj)
            else:
                form=UserCreateForm(request.POST,instance=obj)
            if form.is_valid():                
                #company_id=int(request.POST.get('company',c.id)) 
                cmpny=form.cleaned_data.get('company')                
                u_name = form.cleaned_data.get('username')
                last_name = form.cleaned_data.get('last_name')
                u_pwd = form.cleaned_data.get('password')       
                phone = form.cleaned_data.get('phone','')      
                product_series = form.cleaned_data.get('product_series','') 
                if id:
                    u=User.objects.get(id=int(id))
                    if not u_pwd:
                        u_pwd=request.POST.get('password','')
                else:
                    u=User()
                u.username=u_name
                u.phone=phone
                u.company_id=cmpny.id
                u.last_name=last_name

                u.ancestor_id=c.ancestor_id 
                if product_series:
                    u.product_series = product_series
                else:
                    u.product_series = None
                if u_pwd:
                    u.set_password(u_pwd)
                u.save()     
                u.groups.clear()
                if group_ids:
                    for g_id in group_ids.split(','):                       
                        g=Group.objects.get(id=int(g_id))                        
                        u.groups.add(g)                 
                is_saved = True
        #2021-05-04
        if rbac:
            for g in CGroup.objects.filter(company_id=c.ancestor_id):
                groups.append({'id':g.group_id,'name':g.name})              
            if id:
                for g in obj.groups.all():
                    mygroups.append(str(g.id)) 
        else:
            for g in Group.objects.all():
                groups.append({'id':g.id,'name':g.name})              
            if id:
                for g in obj.groups.all():
                    mygroups.append(str(g.id))     
        #2020-01-05
        s = "select id,name from logistics.company_company where ancestor_id=" +  str(c.ancestor_id) 
        cur = connection.cursor()
        cur.execute(s)
        for r in cur.fetchall():
            company_src.append({'label': r[1], 'value': r[0]})
        series = []
        if id:
            s = "SELECT id,name,(select count(id) from company_user where product_series @> Array[s.id] and id = "+id+") "+\
                "FROM product_series s where company_id = "+str(c.ancestor_id)
            cur.execute(s)
            for r in cur.fetchall():
                series.append([r[0], r[1],r[2]])
        
            
        cur.close()
        return render(request, 'obj/user.html', {
            'form': form,
            'groups':groups,
            'mygroups':mygroups,
            'is_saved':is_saved,
            'company_src': company_src,
            'series':series
        })
    elif obj_name == 'recipient':
        salesman_src=[]
        if request.method == 'GET':
            if not id:
                obj.user = request.user
                obj.company = request.user.company
                obj.ancestor_id = request.user.company.ancestor_id

            form = RecipientsBatchForm(instance=obj)
            formset = RecipientsDetailsFormset(instance=obj)
            cur=connection.cursor()
            #2020-05-18
            #SELECT  id, name FROM logistics.auth_group; 7;"内勤管理组"   8;"业务员"
            c = request.user.company
            sWhere = " where company_id="+str(c.id)
            if c.level_id==1:
                sWhere=" where ancestor_id="+str(c.ancestor_id)
            cur.execute("select id,username from logistics.company_user "+sWhere+\
                " and id in (select user_id from logistics.company_user_groups where group_id in (8))")
            for r in cur.fetchall():
                salesman_src.append({'label': r[1], 'value': r[0]})
        else:
            form = RecipientsBatchForm(request.POST, instance=obj)
            formset = RecipientsDetailsFormset(request.POST, instance=obj)
            if form.is_valid() and formset.is_valid():
                form.save()
                formset.save()
                is_saved=True
        return render(request, 'inventory/recipient.html',
            {'form': form, 'formset': formset, 'salesman_src':salesman_src,'is_saved': is_saved}
        )
    #2020-07-15
    elif obj_name == 'companycfg':        
        if request.method=='GET':
            form=CompanyCfgForm(instance=obj)
        else:
            form=CompanyCfgForm(request.POST,instance=obj)
            if form.is_valid():
                form.save()                
                is_saved=True
    #2020-09-23
    elif obj_name == 'promat':        
        if request.method=='GET':
            form=PromatForm(instance=obj)
        else:
            form=PromatForm(request.POST,instance=obj)
            if form.is_valid():
                form.save()                
                is_saved=True
    #2021-01-27
    elif obj_name == 'brandrecommender':        
        if request.method=='GET':
            form=BrandRecommenderForm(instance=obj)
            
        else:
            form=BrandRecommenderForm(request.POST,instance=obj)
            if form.is_valid():
                form.save()                
                is_saved=True
    #内参
    elif obj_name == 'rebateinvoice':        
        if request.method=='GET':
            if not id:
                obj.user_id=request.user.id  
            form=RebateInvoiceForm(instance=obj)
            formset=RbInvAttachFormset(instance=obj)
        else:
            form=RebateInvoiceForm(request.POST,instance=obj)
            formset=RbInvAttachFormset(request.POST,request.FILES,instance=obj)
            if form.is_valid():
                form.save()         
                if formset.is_valid():
                    formset.save()
                is_saved=True
        return render(request,'obj/rebateinvoice.html',{'form':form,'formset':formset,'is_saved':is_saved})
    #内参
    elif obj_name == 'entapplication':      
        can_edit=False
        u=request.user
        if u.company.level_id==1 and u.has_perm('company.sys_admin'):
            can_edit=True
        if request.method=='GET':
            if not id:
                obj.user_id=u.id  
            form=EntAppForm(instance=obj,user=u)
            formset=EntAppPrdFormset(instance=obj)
        else:
            form=EntAppForm(request.POST,instance=obj,user=u)
            formset=EntAppPrdFormset(request.POST,instance=obj)  
            if form.is_valid():
                o=form.save()    
                x=0
                p=0
                if formset.is_valid():
                    for f in formset:
                        if f.cleaned_data and not f.cleaned_data['DELETE']:
                            if f.cleaned_data['qty_x']:
                                x=x+f.cleaned_data['qty_x']
                            if f.cleaned_data['qty_p']:
                                p=p+f.cleaned_data['qty_p']                           
                    formset.save()
                    o.qty_box=x
                    o.qty_bottle=p
                    o.save()
                o.set_cnt_bottles()
                is_saved=True
        return render(request,'entertainment/edit_entapplication.html',{'form':form,'formset':formset,'is_saved':is_saved,'can_edit':can_edit})
    elif obj_name == 'actapplication':      
        can_edit=False
        u=request.user
        if u.company.level_id==1 and u.has_perm('company.sys_admin'):
            can_edit=True
        if request.method=='GET':
            if not id:
                obj.user_id=u.id  
            form=ActAppForm(instance=obj,user=u)
            formset=ActAppPrdFormset(instance=obj)
        else:
            form=ActAppForm(request.POST,instance=obj,user=u)
            formset=ActAppPrdFormset(request.POST,instance=obj)  
            if form.is_valid():
                o=form.save()    
                x=0
                p=0           
                
                if formset.is_valid():
                    for f in formset:
                        if f.cleaned_data and not f.cleaned_data['DELETE']:
                            if f.cleaned_data['qty_x']:
                                x=x+f.cleaned_data['qty_x']
                            if f.cleaned_data['qty_p']:
                                p=p+f.cleaned_data['qty_p']                           
                    formset.save()
                    o.qty_box=x
                    o.qty_bottle=p
                    o.save()
                o.set_cnt_bottles()
                is_saved=True
        return render(request,'entertainment/edit_actapplication.html',{'form':form,'formset':formset,'is_saved':is_saved,'can_edit':can_edit})
    #内参
    elif obj_name == 'rebateperiod':        
        if request.method=='GET':
            if not id:
                obj.user_id=request.user.id  
                obj.company_id=request.user.company.id
            form=RbClosingForm(instance=obj)            
        else:
            form=RbClosingForm(request.POST,instance=obj)   
            if form.is_valid():
                o=form.save()
                if not o.lv1_dt_fr:
                    o.lv1_dt_fr=o.dt_fr
                if not o.lv1_dt_to:
                    o.lv1_dt_to=o.dt_to
                o.save()
                is_saved=True
        return render(request,'obj/edit_rebateperiod.html',{'form':form,'is_saved':is_saved})
    
    #2021-05-29
    elif obj_name=='materials':   
        units=[]
        if request.method == 'GET':  
            cid=request.user.company.id
            if not id:
                obj.company_id=cid            
            #for u in CompanyUnit.objects.filter(company_id=cid,category=1):
            #    units.append([{'id':u.unit.id,'name':u.unit.name}])
            form = MaterialsForm(instance=obj)       
        elif request.method == "POST":
            form = MaterialsForm(request.POST, instance=obj)
            if form.is_valid():
                form.save()
                is_saved = True
        return render(request,'obj/edit_materials.html',{'form':form,'is_saved':is_saved,'units':units})
    elif obj_name=='semifinishedgoogs':  
        units=[]
        if request.method == 'GET':  
            cid=request.user.company.id
            if not id:
                obj.company_id=cid            
            #for u in CompanyUnit.objects.filter(company_id=cid,category=2):
            #    units.append([{'id':u.unit.id,'name':u.unit.name}])
            form = MaterialsForm(instance=obj)       
        elif request.method == "POST":
            form = MaterialsForm(request.POST, instance=obj)
            if form.is_valid():
                form.save()
                is_saved = True
        return render(request,'obj/edit_semifinishedgoods.html',{'form':form,'is_saved':is_saved,'units':units})
    elif obj_name=='packaging':    
        units=[]
        if request.method == 'GET':  
            cid=request.user.company.id
            if not id:
                obj.company_id=cid            
            #for u in CompanyUnit.objects.filter(company_id=cid,category=2):
            #    units.append([{'id':u.unit.id,'name':u.unit.name}])
            form = MaterialsForm(instance=obj)       
        elif request.method == "POST":
            form = MaterialsForm(request.POST, instance=obj)
            if form.is_valid():
                form.save()
                is_saved = True
        return render(request,'obj/edit_packaging.html',{'form':form,'is_saved':is_saved,'units':units})
    elif obj_name=='bom':    
        cid=request.user.company.id
        units_mat=[]
        units_sem=[]
        units_pkg=[]
        #1;"FG";"产成品"
        #2;"MAT";"原材料"
        #3;"PKG";"包装材料"
        #4;"SEMI";"半成品"
        
        for uu in CompanyUnit.objects.filter(company_id=cid,invtype=2):
            units_mat.append(uu.unit)
        for uu in CompanyUnit.objects.filter(company_id=cid,invtype=4):
            units_sem.append(uu.unit)
        for uu in CompanyUnit.objects.filter(company_id=cid,invtype=3):
            units_pkg.append(uu.unit)
        if request.method == 'GET':             
            if not id:
                obj.company_id=cid    
            form = BOMForm(instance=obj) 
            formset_m=BOMmaterialsFormset(instance=obj)
            formset_s=BOMsemifinishedgoodsFormset(instance=obj)
            formset_p=BOMpackagingFormset(instance=obj) 
        elif request.method == "POST":            
            form = BOMForm(request.POST, instance=obj)
            formset_m=BOMmaterialsFormset(request.POST,instance=obj)
            formset_s=BOMsemifinishedgoodsFormset(request.POST,instance=obj)
            formset_p=BOMpackagingFormset(request.POST,instance=obj)
            if form.is_valid():
                form.save()
                is_saved = True
                if formset_m.is_valid():
                    formset_m.save()
                if formset_s.is_valid():
                    formset_s.save()
                if formset_p.is_valid():
                    formset_p.save()
                
        return render(request,'mes/edit_bom.html',
            {'form':form,'is_saved':is_saved,'formset_m':formset_m,'formset_s':formset_s,'formset_p':formset_p,
                'units_mat':units_mat,'units_sem':units_sem,'units_pkg':units_pkg}
        )
    elif obj_name == 'kangnian_inventorymanipulation':
        if request.method == 'GET':
            if id:
                obj.id = id
                form = KangNian_InventoryManipulationForm(instance=obj)
            else:
                form = KangNian_InventoryManipulationForm(instance=obj)       
                
                
        elif request.method == "POST":
            form = KangNian_InventoryManipulationForm(request.POST, instance=obj)  
            
            if form.is_valid():
                sobj = form.save(commit=False)
                method = request.POST.get('method','1')   
                
                if method == '4': 
                    
                    if sobj.agent.id != 32220:
                        ss = KangNian_InventoryManipulation(
                            agent_id=32220,
                            reason=obj.reason,
                            product = obj.product,
                            amount= sobj.amount,
                            user=sobj.user,
                            tuiku_agent_id= sobj.agent.id
                        )
                        ss.save()
                        
                    sobj.amount = -sobj.amount  
                    
                    
                sobj.user = request.user
                sobj.save()
                
                if method == '2':
                    ss = KangNian_InventoryManipulation(
                        agent=request.user.company,
                        reason='展台出库'+obj.reason,
                        product = obj.product,
                        amount= -sobj.amount,
                        user=sobj.user
                    )
                    
                    ss.save()
                elif method == '1':
                    amount = -obj.amount
                    storehouse = KangNian_StorehouseInventoryManipulation(
                        storehouse = obj.storehouse,
                        reason = obj.reason,
                        product = obj.product,
                        amount = amount,
                        user=sobj.user
                    )
                    storehouse.save()
                    
                is_saved = True   
        return render(request,'kangnian/inventorymanipulation.html',{'form':form,'is_saved':is_saved})
    elif obj_name == 'kangnian_storehouse_management_detail':
        if request.method == 'GET':
            if id:
                obj.id = id
                form = KangNian_StorehouseManagementDeatilForm(instance=obj)
            else: 
                form = KangNian_StorehouseManagementDeatilForm(instance=obj)       
        elif request.method == "POST":
            form = KangNian_StorehouseManagementDeatilForm(request.POST, instance=obj)
            if form.is_valid():
            
                sobj = form.save(commit=False)
                sobj.user = request.user
                method = request.POST.get('method','1')   
                if method == '0': 
                    sobj.amount = -sobj.amount
                sobj.save() 
                is_saved = True   
        return render(request,'kangnian/storehousemanagement_detail.html',{'form':form,'is_saved':is_saved})
    elif obj_name == 'channel':        
        if request.method=='GET':
            form=Company_Channel_Form(instance=obj)
            
        else:
            form=Company_Channel_Form(request.POST,instance=obj)
            if form.is_valid():
                m = form.save(commit=False)
                m.ancestor_id = request.user.ancestor_id
                m.save()                
                is_saved=True
    elif obj_name == 'channel_level':        
        if request.method=='GET':
            form=Company_Channel_Level_Form(instance=obj,user=request.user)
            
        else:
            form=Company_Channel_Level_Form(request.POST,instance=obj,user=request.user)
            if form.is_valid():
                m = form.save(commit=False)
                m.ancestor_id = request.user.ancestor_id
                m.save()                
                is_saved=True
    elif obj_name == 'uploadfiles':    
        if request.method=='GET':
            form=Company_UploadFiles_Form(instance=obj,user=request.user)
        else:
            form=Company_UploadFiles_Form(request.POST,instance=obj,user=request.user)
            if form.is_valid():
                m = form.save(commit=False)
                m.company_id = request.user.company_id
                m.save()                
                is_saved=True
    
    
    return render(request, 'obj/'+obj_name+'.html', {'form': form, 'is_saved': is_saved})



@tj_login_required
def list_obj(request, obj):
    data={'data':[],'clmns':[],'e':[],'subtt':[],'order': [], 'murl':'/obj/edit/'+obj+'/','dt':False,'add':True}
    d = {
        'product': ['产品', 'product', 'Product',[_('产品管理'),_('产品名称')]],
        'series':['产品系列','product','Series',[_('产品管理'),_('产品系列')]],
        'store': ['仓库', 'inventory','Storehouse',[_('公司管理'),_('仓库列表')]],
        'user': ['帐号', 'company', 'User',[_('公司管理'),_('帐号名称')]], 
        'agent': ['经销商', 'company', 'Company',[_('公司管理'),_('经销商名称')]],        
        'productprop': ['产品属性', 'product','ProductProp',[_('产品管理'),_('产品属性')]],        
        'sal_division': ['营销战区', 'company','SalesDivision',[_('公司管理'),_('营销战区')]],
        'sal_subdivision': ['营销分区', 'company','SubSalDivision',[_('公司管理'),_('营销分区')]],
        'prd_plant': ['工厂/厂区', 'company', 'PrdPlant',[_('公司管理'),_('工厂名称')]],
        'prd_shop': ['生产车间', 'company', 'PrdWorkshop',[_('公司管理'),_('生产车间')]],
        'prd_line': ['生产线', 'company', 'PrdLine',[_('公司管理'),_('生产线')]],        
        'prd_team': ['生产班组', 'company','PrdTeam',[_('公司管理'),_('生产班组')]],
        'prd_qc': ['质检员', 'company', 'PrdQc',[_('公司管理'),_('质检员')]],


        'recipient': ['业务员领用', 'inventory', 'RecipientsBatch',  [_('领用核销'), _('业务员领用')] ],
        'award': ['奖品设置', 'sales', 'Award',  [_('营销活动'), _('奖品设置')] ],
        'activities': ['活动设置', 'sales', 'Activities',  [_('营销活动'),  _('活动设置')] ],
        'companycfg': ['公司设置', 'company', 'CompanyCfg',  [_('公司管理'), _('公司设置')] ],
        'consumerwinner': ['中奖记录', 'sales', 'ConsumerWinner', [_('营销活动'), _('中奖记录')]],
        'consumerredeem': ['兑奖情况', 'sales', 'ConsumerRedeem', [_('营销活动'), _('兑奖情况')]],
        'channelpromotions': ['活动设置', 'sales', 'ChannelPromotions', [_('渠道促销'),  _('扫码奖励方案')] ],
        #2020-09-17
        'storegeo':['仓库核准','inventory','StorehouseBD09',[_('经销商管理'),_('待核准仓库')] ],        
        #'prd_workshop': ['生产班组', 'company', 'PrdWorkshop'],

        #2020-09-23
        'promat':['促销物料','promotion','Material',[_('产品管理'),_('促销物料')] ],  

        #2020-10-03
        'giftsscheme':['促销礼品活动方案','promotion','GiftsScheme',[_('领用核销'),_('活动方案')] ], 
        'giftsapplication':['促销礼品申请单','promotion','GiftsApplication',[_('领用核销'),_('礼品申请')] ], 
        'giftsrequisition':['促销礼品领用单','promotion','GiftsRequisition',[_('领用核销'),_('礼品领用')] ], 
        
        #2020-10-14
        'bugsell':['窜货预警','sales','BugSell',[_('物流查询'),_('窜货预警')]],
        'fakelabel':['假货预警','sales','FakeLabel',[_('物流查询'),_('假货预警')]],
        'bugsell_cluster_map':['窜货预警地图','sales','BugSell',[_('物流查询'),_('窜货预警地图')]],
        'fakelabel_cluster_map':['假货预警地图','sales','FakeLabel',[_('物流查询'),_('假货预警地图')]],
        #2021-01-24
        'wxwalletapplication': ['经销商绑定微信钱包申请', 'company','WxWalletApplication',[_('经销商管理'),_('微信钱包绑定申请')]],
        'bankinfoapplication': ['经销商绑定银行卡申请',  'company','BankInfoApplication',[_('经销商管理'),_('银行卡绑定申请')]],
         #2021-01-27
        'brandrecommender': ['品牌推荐官','company','BrandRecommender',[_('公司管理管理'),_('品牌推荐官')]],

        #2021-02-22
        'inventoryticket': ['销售启单','inventory','Tickets',[_('物流工具'),_('销售启单')]],
        'bulletinboard':  ['通知信息','company','BulletinBoard',[_('公司管理'),_('通知信息')]],
        #2021-03-08
        'rebateinvoice':  ['发票录入','sales','RebateInvoice',[_('渠道促销'),_('发票录入')]],

        #2021-03-10
        'entapplication':['用酒单','entertainment','EntApplication',[_('领用核销'),_('招待用酒核销')]],
        'actapplication':['会议活动申报','entertainment','ActApplication',[_('领用核销'),_('会议(活动)用酒核销')]],
        #2021-05-24
        'giftapplication':['赠品核销','entertainment','GiftApplication',[_('领用核销'),_('赠酒核销')]],

        'entwriteoff':['扫码核销记录','entertainment','EntWriteoff',[_('领用核销'),_('扫码明细记录')]],
        #2021-04-01
        'stack':['垛位列表','inventory','Stack',[_('进销存报表'),_('垛位列表')]],


        #2021-05-29
        'companyunit':['计量单位','mes','CompanyUnit',[_('物料信息'),_('计量单位')]],
        'materials':['原材料','mes','Materials',[_('物料信息'),_('原材料')]],
        'semifinishedgoogs':['半成品','mes','SemiFinishedGoods',[_('物料信息'),_('半成品')]],
        'packaging':['包装物料','mes','Packaging',[_('物料信息'),_('包装物料')]],


        'bom':['物料清单','mes','BOM',[_('物料信息'),_('物料清单')]],
        'storehouse':['仓库信息','mes','Storehouse',[_('物料信息'),_('仓库信息')]],
        
        "kangnian_reserve": ['预约列表', 'sales', 'Kangnian_reservelabel', [_('康年皇冠'), _('预约列表')]],
        "kangnian_writeoff":['核销列表', 'sales', 'Kangnian_writeofflabel', [_('康年皇冠'), _('核销列表')]],
        "kangnian_inventorymanipulation":['库存管理', 'sales', 'Kangnian_inventorymanipulation', [_('康年皇冠'), _('库存管理')]],
        "kangnian_inventorymanipulation_detail":['库存明细', 'sales', 'Kangnian_inventorymanipulation', [_('康年皇冠'), _('库存明细')]],
        "kangnian_activehistory":['激活历史', 'sales', 'Kangnian_activelabel', [_('康年皇冠'), _('激活历史')]],
        "kangnian_storehouse_management":['仓库库存管理','sales','Kangnian_activelabel', [_('康年皇冠'), _('仓库库存管理')]],
        "kangnian_storehouse_management_detail":['仓库库存明细','sales','Kangnian_activelabel', [_('康年皇冠'), _('仓库库存管理')]],


        "qz_brandrecommender": ['品牌推荐官返利明细', 'sales', 'ActivityRebate_BrandRecommenderBonus', [_('求真酒业'), _('品牌推荐官返利明细')]],
        "qz_brandrecommender_redpack_record":['品牌推荐官红包发放情况', 'sales', 'ActivityRebate_BrandRecommenderRedpackRecord', [_('求真酒业'), _('品牌推荐官红包发放情况')]],        
        "qz_brandrecommender_rebate_summary":['品牌推荐官返利汇总', 'sales', 'ActivityRebate_BrandRecommenderBonus', [_('求真酒业'), _('品牌推荐官返利汇总')]],
        "qz_anniversary": ['逢整返利明细', 'sales', 'ActivityRebate_Anniversary', [_('求真酒业'), _('逢整返利明细')]],
        "qz_rebatenumber":['红包领取状况','sales','ActivityRebate_BonusRecord', [_('求真酒业'), _('红包领取状况')]],
        "qz_consumerwinner":['红包发放明细','sales','ConsumerWinner', [_('求真酒业'), _('红包发放明细')]],
        "qz_redpacksendfail":['普通红包发放失败记录','sales','ActivityRebate_BonusRecord', [_('求真酒业'), _('普通红包发放失败记录')]],
        "qz_redpackstatus": ['红包活动开展情况','sales','ConsumerWinner', [_('求真酒业'), _('红包活动开展情况')]],
        
        #2021-08-09 加盟商门店列表
        "outlet":['加盟商门店列表','company','Outlet',[_('门店名称'),_('所属加盟商'),_('审核'),_('省份'),_('城市'),_('区/县'),_('详细地址'),_('授权品牌'),_('门店状态'),_('特殊情况说明')]],

        "channel": ['渠道列表','company','Channel', [_('公司管理'), _('渠道列表')]],
        "channel_level": ['渠道等级','company','Channel_Level', [_('公司管理'), _('渠道等级')]],
        'uploadfiles': ['公司资料','company','UploadFiles', [_('公司管理'), _('公司资料')]],
        # 2022-07-08 终端活动码、瓶盖、物料扫码记录
        'sales_couponscan':['终端活动码扫码记录','sales','CouponScan',[_('营销活动'),_('活动扫码')]],
    }
    
    #2020-10-10
    show_add=False
    can_edit=False    




    u=request.user
    c=u.company
    if obj not in d:
        return HttpResponse(_('无效对像名'))


    #2022-04-20 
    if c.level_id==1 and u.has_perm('company.sys_admin'):
        show_add=True
        can_edit=True
    elif u.id in [14040]:   #长宇酒业，14040;"助理"
        show_add=True
        can_edit=True
    #2022-05-05  宝臣(104922)，山西成兆兴酒业有限公司(100425)  一级需要维护其下级经销商信息  
    elif c.ancestor_id in [104922,100425]:   
        # 2022-08-03
        if c.level_id in [2,4]:
            show_add=True
            can_edit=True          

    
    #2020-11-06
    data['obj']=d[obj][2]

    data['subtt']=d[obj][3]
    m = apps.get_model(d[obj][1], d[obj][2])
    cur=connection.cursor()    


    #2021-04-19
    only_myself=False
    group_ids=[]
    cur.execute("select group_id FROM company_user_groups where user_id="+str(request.user.id))
    for r in cur.fetchall():
        group_ids.append(r[0])
    #8;"业务员" 12;"销售助理"  4;"系统管理员"
    if 4 not in group_ids:
        if 8 in group_ids:   
            only_myself=True

    if obj=='product':   
        #2020-10-16
        #if c.level_id==1 and u.has_perm('company.sys_admin'):
        #    show_add=True
        
        sub_qry=[]
        #data['clmns']=[_('产品名称'),_('品牌系列'),_('图片'),]


        #20202-12-24
        if c.has_external_code:
            data['clmns']=[_('产品名称'),_('外部编码'),_('品牌系列'),_('图片'),_('条型码')]
            cnt=3
        else:
            #2020-11-12
            data['clmns']=[_('产品名称'),_('品牌系列'),_('图片'),_('条型码')]
            #2020-12-24
            cnt=2
            
        
        has_pro=False
        
        cur.execute("select id,name,desc_cn,unit from company_categoryprop where category_id="+str(c.category_id))
        for p in cur.fetchall():
            has_pro=True
            cnt=cnt+1
            data['clmns'].append(p[2])    
            if p[0]==4:  #产品包装(每箱瓶数)
                sub_qry.append("coalesce((select value from product_productprop where id="+\
                    "(SELECT prd_property_id  FROM product_prdprops where product_id=p.id "+\
                        " and (SELECT categoryprop_id FROM product_productprop where id=prd_property_id)="+str(p[0])+")),'')"+\
                    "||'    ['||case when qty_p>0 then qty_p||'瓶' else '未知' end||']'"
                )  
            else:
                sub_qry.append("coalesce((select value from product_productprop where id="+\
                    "(SELECT prd_property_id  FROM product_prdprops where product_id=p.id "+\
                        " and (SELECT categoryprop_id FROM product_productprop where id=prd_property_id)="+str(p[0])+")),'')")  
            
            



        #s="select name,coalesce((select name from product_series where id=series_id),''),"+\
        #    "case when length(photo)>5 then '<img src=\"/media/'||photo||'\" style=\"height:90px;cursor:pointer;\"></img>' else '' end,"
        
        if c.has_external_code:
            #2020-11-24
            s="select case when specs is null then name else name||'  ['||left(specs,10)||']' end,"+\
                "coalesce(external_code,''),coalesce((select name from product_series where id=series_id),''),"+\
                "case when length(photo)>5 then '<img src=\"/media/'||photo||'\" style=\"height:90px;cursor:pointer;\"></img>' else '' end,"+\
                "coalesce(barcode,''),"
        else:
            #2020-11-12
            s="select name,coalesce((select name from product_series where id=series_id),''),"+\
                "case when length(photo)>5 then '<img src=\"/media/'||photo||'\" style=\"height:90px;cursor:pointer;\"></img>' else '' end,"+\
                "coalesce(barcode,''),"
        if has_pro:            
            s=s+(",".join(sub_qry))+","            
        data['clmns'].append(_('产品说明'))
        data['clmns'].append(_('产品价格'))
        s=s+"coalesce(remark,''),coalesce(to_char(price,'9999999.99'),''),id from product_product p "+\
            "where company_id="+str(c.id)+" and is_delete=false"        
        cur.execute(s)     

        

        for r in cur.fetchall():
            data0=[]           
            #for n in range(cnt+3):
            
            #2020-11-12
            for n in range(cnt+4):
                data0.append(r[n])  
            if can_edit:
                data0[0]='<a href="/obj/edit/product/'+str(r[cnt+4])+'/">'+r[0]+'</a>'
            else:
                data0[0]=r[0]
            data['data'].append(data0)
            
    elif obj=='series':
        #2020-10-16
        #if c.level_id==1 and u.has_perm('company.sys_admin'):
        #    show_add=True

        cur.execute("select id,name,coalesce(remark,''),sn from product_series where  company_id="+str(c.id)+" and is_delete=false")
        data['clmns']=[_('系列名称'),_('排序'),_('说明')]
        for r in cur.fetchall():
            if can_edit:
                a='<a href="/obj/edit/series/'+str(r[0])+'/">'+r[1]+'</a>'
            else:
                a=r[1]
            data['data'].append([a,r[3],r[2]])
    elif obj=='store':
        #2020-10-16
        #if (c.level_id==1 and u.has_perm('company.sys_admin') ) or u.id==3311:
        #    show_add=True
        
        #2021-04-27
        group_ids=[]
        cur.execute("select group_id FROM company_user_groups where user_id="+str(u.id))
        for r in cur.fetchall():
            group_ids.append(r[0])
        #经销商可以添加自己的仓库
        if 5 in group_ids:  #5;"经销商"
            show_add=True

        data['clmns']=[_('仓库名称'),_('外部编码'),_('仓库管理员'),_('地址'),_('联系人'),_('电话')]
        cur.execute("select id,name,coalesce(external_code,''),coalesce((select username from company_user where id=keeper_id),''),"+\
                "coalesce(address,''),coalesce(contact,''),coalesce(cellphone,'') from inventory_storehouse where company_id="+str(c.id))
        for r in cur.fetchall():
            #if u.has_perm('company.sys_admin'):
            if show_add:
                a='<a href="/obj/edit/store/'+str(r[0])+'/">'+r[1]+'</a>'
            else:
                a=r[1]            
            data['data'].append([ a,r[2],r[3],r[4],r[5],r[6] ])
    elif obj=='user':
        #2020-10-16
        if (c.level_id==1 and u.has_perm('company.sys_admin') ) or u.id==3311:
            show_add=True

        #sWhere=" where company_id="+str(c.id)
        #if c.level_id==1:
        #    sWhere=" where ancestor_id="+str(c.id)   
        
        # 2022-07-28       
        sWhere=" where ancestor_id="+str(c.id)       
        if c.level_id>1:
            sWhere=" where company_id="+str(c.id)+" or (select parent_id from company_company where id=company_id)="+str(c.id)   
        
        #2021-05-08
        if c.is_expense_account:
            if c.level_id==2:
                sWhere=" where (select division_id from company_company where id=company_id)="+str(c.division_id)+" and is_delete=false" 
            elif c.clevel_id==4:
                sWhere=" where (select sub_division_id from company_company where id=company_id)="+str(c.sub_division_id)+" and is_delete=false"               
            
        data['clmns']=[_('帐号'),_('姓名'),_('公司名称'),_('父级公司'),_('注册时间'),_('营销层级'),_('功能组别'),_('联系电话'),_('战区'),_('分区'),_('操作')]        
        s="select a.id,username,to_char(date_joined,'yyyy-mm-dd HH24:MI'),"+\
                "coalesce(b.name,''),"+\
                "coalesce((select name from company_level where id=b.level_id),''),"+\
                "(select array_to_string(array(select name from auth_group where id in "+\
                    "(select group_id from logistics.company_user_groups where user_id=a.id)),',')"+\
                "),"+\
                "coalesce(a.phone,b.cell_phone,''), "+\
                "coalesce((select name from company_company where id=b.parent_id),''), "+\
                "coalesce((select name from company_salesdivision where id=b.division_id),''),"+\
                "coalesce((select name from company_subsaldivision where id=b.sub_division_id),''),"+\
                "coalesce(a.last_name,'') "+\
                "from "+\
                "(select id,username,last_name,phone,date_joined,company_id from company_user"+sWhere+") a "+\
                "left outer join "+\
                "(select id,name,parent_id,level_id,division_id,sub_division_id,cell_phone from company_company) b "+\
                "on a.company_id=b.id"
        cur.execute(s)
        for r in cur.fetchall():
            if can_edit:
                a='<a href="/obj/edit/user/'+str(r[0])+'/">'+r[1]+'</a>'
                if c.id == 19257:
                    b='<a href="/company/user/brands/'+str(r[0])+'/" class="btn btn-xs btn-default">品牌绑定</a>'
                else:
                    b=''
            else:
                a=r[1]
                b=''
            data['data'].append([ a,r[10],r[3],r[7],r[2],r[4],r[5],r[6],r[8],r[9],b ])
    #加盟商门店列表
    elif obj=='outlet':
        if c.level_id==1 and u.has_perm('company.sys_admin'):
            data['show_add']=True
        sWhere=" where ancestor_id="+str(c.ancestor_id)+" and is_delete=false"
        data['clmns']=['',_('门店名称'),_('所属加盟商'),_('省份'),_('城市'),_('详细地址'),_('查询老师'),_('首查时间'),_('联系人'),_('职位'),_('授权品牌'),_('门店状态'),_('审核'),_('特殊情况说明')]
        s = "select id,name,(select name from company_company where id=company_id)," + \
            "coalesce((select username from company_user where id=approve_by_id),''),"+\
            "coalesce(to_char(approve_tm,'yyyy-mm-dd HH24:MI'),''),"+\
            "coalesce(province_gis->>'name','')," + \
            "coalesce(city_gis->>'name','')," + \
            "coalesce(address,''),"+\
            "coalesce(qry_teacher,''),"+\
            "coalesce(to_char(qry_time,'YYYY-mm-dd HH24:MI'),''),"+\
            "coalesce(contacts,''),"+\
            "coalesce(position,''),"+\
            "coalesce((select name from product_series where id=series_id),''),"+\
            "coalesce((select name from company_outletstatus where id=status_id),''),"+\
            "coalesce(remark,'') "+\
            "from company_outlet a " + sWhere 
        cur.execute(s)
        for r in cur.fetchall():
            if u.has_perm('company.sys_admin'):
                a = '<a class="edit" href="/obj/edit/outlet/' + str(r[0]) + '/">' + r[1] + '</a>'
            else:
                a = r[1]      
            if r[3]:
                ae=r[3]+' '+r[4]                    
            else:
                ae='<button class="approve btn btn-sm btn-primary">审核通过</button>'
            data['data'].append([ r[0], a, r[2], r[5], r[6], r[7], r[8],r[9],r[10],r[11],r[12],r[13],ae,r[14] ])
        return render(request, 'company/franchise/list_outlet.html', data)
        
    elif obj=='agent':
        #2020-10-10
        if c.is_expense_account:
            #战区
            if c.level_id==2:
                sWhere=" where division_id="+str(c.division_id)+" and is_delete=false"
            #片区
            elif c.level_id==4:
                si="0"
                if c.sub_division_id:
                    si=str(c.sub_division_id)
                sWhere=" where sub_division_id="+si+" and is_delete=false"
        else:
            sWhere=" where parent_id="+str(c.id)+" and is_delete=false"
            if c.level_id==1:
                sWhere=" where ancestor_id="+str(c.id)+" and is_delete=false and level_id<>1"

        cfg_franchise=c.hierarchy.cfg_company.franchise_mgt

        #2020-10-16
        #if (c.level_id==1 and u.has_perm('company.sys_admin') ) or u.id==3311 or u.id==12110 :
        #2022-03-21 山西樊王酒业有限公司  fwjysmy:13372   可以新建经销商
        if (c.level_id==1 and u.has_perm('company.sys_admin') ) or u.id in [3311,12110,13372]:
            show_add=True
        is_finance=False
        if cfg_franchise==0: #无加盟商体系管理            
            if 18 in group_ids or 20 in group_ids or 4 in group_ids:  #4;"系统管理员" 18;"财务" 20;"出纳"
                is_finance=True

            data['clmns']=[_('经销商/分销商名称'),_('外部编码'),_('父级'),_('营销层级'),_('所属战区'),_('所属分区'),_('经销区域'),_('经销商地址'),_('联系电话'),_('派出机构'),_('帐号'),_('终端店'),_('经纬度')]
            if u.ancestor_id==1644:
                if is_finance:
                    data['clmns']=[_('经销商/分销商名称'),_('外部编码'),_('父级'),_('营销层级'),_('所属战区'),_('所属分区'),_('经销区域'),_('银行卡'),_('微信钱包'),
                            _('经销商地址'),_('联系电话'),_('派出机构'),_('帐号'),_('终端店')]
            sFilter = ""
            isChannel = False
            
            if u.ancestor_id==19318:
                isChannel = True
            
            if isChannel:
                data['clmns']=[_('经销商/分销商名称'),_('外部编码'),_('父级'),_('营销层级'),_('所属战区'),_('所属分区'),_('经销区域'),_('经销商地址'),_('联系电话'),_('派出机构'),_('帐号'),_('终端店'),_('经纬度'),_('渠道'),_('级别'),_('产品系列')]
                sFilter = ",COALESCE((select name from company_channel where id = channel_id),''),"+\
                          "array_to_string(array(select name from company_channel_level where id in (select unnest(channel_level_ids))),','),"+\
                          "array_to_string(array(select name from product_series where id in (select unnest(series_more))),',') "

            #sWhere=" where parent_id="+str(c.id)+" and is_delete=false"
            #if c.level_id==1:
            #    sWhere=" where ancestor_id="+str(c.id)+" and is_delete=false and level_id<>1"

            


            s="select id,name,coalesce(external_code,''),"+\
                "coalesce((select name from company_company where id=a.parent_id),''),"+\
                "coalesce((select name from company_level where id=a.level_id),''),"+\
                "coalesce((select name from company_salesdivision where id=division_id),''),"+\
                "coalesce((select name from company_subsaldivision where id=sub_division_id),''),"+\
                "case when array_length(adm_ids,1) is null then '' else "+\
                    "array_to_string(array(select name from gis.adm where array[id] <@ adm_ids),',') end,"+\
                "coalesce(address,''),coalesce(cell_phone,''),"+\
                "case when is_expense_account then '是' else '否' end,"+\
                "coalesce(array_to_string(array(select username from company_user where company_id=a.id),','),''), "+\
                "case when is_terminal then '是' else '否' end, "+\
                "COALESCE( array_to_string(lng_lat, ',') ,'') "+sFilter+\
                "from company_company a "+sWhere
            if u.ancestor_id==1644 and is_finance:
                s="select id,name,coalesce(external_code,''),"+\
                    "coalesce((select name from company_company where id=a.parent_id),''),"+\
                    "coalesce((select name from company_level where id=a.level_id),''),"+\
                    "coalesce((select name from company_salesdivision where id=division_id),''),"+\
                    "coalesce((select name from company_subsaldivision where id=sub_division_id),''),"+\
                    "case when array_length(adm_ids,1) is null then '' else "+\
                        "array_to_string(array(select name from gis.adm where array[id] <@ adm_ids),',') end,"+\
                    "coalesce(address,''),coalesce(cell_phone,''),"+\
                    "case when is_expense_account then '是' else '否' end,"+\
                    "coalesce(array_to_string(array(select username from company_user where company_id=a.id),','),''), "+\
                    "case when is_terminal then '是' else '否' end,"+\
                    "coalesce((select name from company_applicationstatus where id=(select status_id from company_bankinfoapplication "+\
                        "where id=(select application_id from company_bankinfo where company_id=a.id and is_default=true order by id desc limit 1))),''), "+\
                    "coalesce((select name from company_wxwalletstatus where id=(select status_id from company_wxwalletapplication "+\
                        "where id=(select application_id from company_wxwallet where company_id=a.id limit 1))),'') "+\
                    "from company_company a "+sWhere

            cur.execute(s)
            for r in cur.fetchall():
                if can_edit:
                    a='<a href="/obj/edit/agent/'+str(r[0])+'/">'+r[1]+'</a>'
                else:
                    a=r[1]
                if u.ancestor_id==1644 and is_finance:
                    data['data'].append([a,r[2],r[3],r[4],r[5],r[6],r[7],r[13],r[14],r[8],r[9],r[10],r[11],r[12]])
                else:
                    if isChannel:
                        data['data'].append([a,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],r[15],r[16] ])
                    else:
                        data['data'].append([a,r[2],r[3],r[4],r[5],r[6],r[7],r[8],r[9],r[10],r[11],r[12],r[13] ])

        elif cfg_franchise==1:  #加盟商 品牌/系列授权管理                    
            #易臻生物科技（上海）有限公司
            #if c.level_id==1 and u.has_perm('company.sys_admin'):
            #    data['show_add']=True
            if c.level_id==1:
                s="select cgroup_ids @> '[{\"name\":\"内勤管理组\"}]'::jsonb FROM company_user where id="+str(u.id)    
                cur.execute(s)
                rr=cur.fetchone()     
                if rr:
                    if rr[0]:                 
                        show_add=True
            #data['clmns']=[_('加盟商名称'),_('编码'),_('所属事业部'),_('门店数量'),_('省份'),_('城市'),_('授权品牌'),_('当前状态'),_('审核'),_('删除'), _('备注说明')]
            data['clmns']=[_('编码'),_('加盟商名称'),_('所属事业部'),_('查询老师'),_('省份'),_('城市'),_('门店数量'),_('授权品牌'),_('当前状态'),_('首查时间'),_('审核'),_('删除'), _('录入时间'), _('备注说明')]

            s = "select id,name,coalesce(external_code,'')," + \
                    "coalesce((select name from company_subsaldivision where id=sub_division_id),'')," + \
                    "(select count(*) from company_outlet where company_id=a.id),"+\
                    "coalesce((select name from gis.adm where id=city_gis_id),"+\
                        "array_to_string(array(select city_gis->>'name' from company_outlet where company_id=a.id group by city_gis->>'name'),',')),"+\
                    "array_to_string(array((select jsonb_array_elements(franchise_series)::json->>'name')),','),"+\
                    "array_to_string(array((select jsonb_array_elements(franchise_status)::json->>'name')),','),"+\
                    "coalesce(remark,''),coalesce(cell_phone,''),"+\
                    "coalesce((select username from company_user where id=approve_by_id),''),coalesce(to_char(approve_tm,'yyyy-mm-dd HH24:MI'),''), "+\
                    "coalesce((select name from gis.adm where id=province_gis_id),"+\
                        "array_to_string(array(select province_gis->>'name' from company_outlet where company_id=a.id group by province_gis->>'name'),',')) "+\
                    "from company_company a " + sWhere 
            # 2021-11-18 加盟状态单选
            s = "select id,name,coalesce(external_code,'')," + \
                    "coalesce((select name from company_subsaldivision where id=sub_division_id),'')," + \
                    "(select count(*) from company_outlet where company_id=a.id),"+\
                    "coalesce((select name from gis.adm where id=city_gis_id),"+\
                        "array_to_string(array(select city_gis->>'name' from company_outlet where company_id=a.id group by city_gis->>'name'),',')),"+\
                    " COALESCE((SELECT name from product_series where id = series_id),''),"+\
                	" COALESCE((SELECT name from company_franchisestatus where id = franchisestatus_id),''), "+\
                    " coalesce(remark,''),coalesce(cell_phone,''),"+\
                    " coalesce((select username from company_user where id=approve_by_id),''),coalesce(to_char(approve_tm,'yyyy-mm-dd HH24:MI'),''), "+\
                    " coalesce((select name from gis.adm where id=province_gis_id),"+\
                        "array_to_string(array(select province_gis->>'name' from company_outlet where company_id=a.id group by province_gis->>'name'),',')), "+\
                    "  COALESCE(qry_teacher,''), COALESCE(to_char(qry_time,'yyyy--mm-dd HH24:MI:SS'),''), "+\
                " COALESCE(to_char(tm,'yyyy--mm-dd HH24:MI:SS'),'')registertm from company_company a " + sWhere
            cur.execute(s)
            for r in cur.fetchall():
                #if u.has_perm('company.sys_admin'):
                #    a = '<a class="edit" href="/obj/edit/agent/' + str(r[0]) + '/">' + r[1] + '</a>'
                #else:
                #    a = r[1]
                if show_add:
                    data['show_add']=show_add
                    a = '<a class="edit" href="/obj/edit/agent/' + str(r[0]) + '/">' + r[1] + '</a>'
                else:
                    a = r[1]      

                if r[10]:
                    ae=r[10]+' '+r[11]                    
                else:
                    ae='<button class="approve btn btn-sm btn-primary">审核通过</button>'
                btn_del='<button class="delete btn btn-sm btn-danger">删除</button>'
                #data['data'].append([  a, r[2], r[3], r[4], r[12], r[5], r[6], r[7], ae,btn_del, r[8], r[0] ])
                data['data'].append([  r[2],a,  r[3],r[13], r[12], r[5], r[4], r[6], r[7], r[14],ae,btn_del,r[15], r[8], r[0] ])
            return render(request, 'company/franchise/list_agents.html', data)

    elif obj=='productprop':
        #2020-10-16
        if c.level_id==1 and u.has_perm('company.sys_admin'):
            show_add=True

        data['clmns']=[_('属性名'),_('属性值'),_('备注说明')]       
        cur.execute("SELECT id, value, coalesce(remark,''),"+\
            "(SELECT desc_cn company_categoryprop from company_categoryprop where id=categoryprop_id) "+\
            "FROM product_productprop where company_id="+str(c.id)+" and is_delete=false")
        for r in cur.fetchall():
            if can_edit:
                a='<a href="/obj/edit/productprop/'+str(r[0])+'/">'+r[3]+'</a>'
            else:
                a=r[3]
            data['data'].append([a,r[1],r[2]])
    elif obj=='sal_division':
        #2020-10-16
        if c.level_id==1 and u.has_perm('company.sys_admin'):
            show_add=True
        if c.cfg_company.saldiv_provincial:
            data['clmns']=[_('战区名称'),_('战区编码'),_('战区经理'),_('省内省外'),_('备注说明')] 
        else:
            data['clmns']=[_('战区名称'),_('战区编码'),_('战区经理'),_('备注说明')]       
        cur.execute("SELECT id, name,coalesce(code,''), coalesce(remark,''),"+\
            "coalesce((SELECT username from company_user where id=manager_id),''),"+\
            "case when provincial=1 then '省内' else '省外' end "+\
            "FROM company_salesdivision where company_id="+str(c.id))
        for r in cur.fetchall():
            if can_edit:
                a='<a href="/obj/edit/sal_division/'+str(r[0])+'/">'+r[1]+'</a>'
            else:
                a=r[1]
            if c.cfg_company.saldiv_provincial:
                data['data'].append([a,r[2],r[4],r[5],r[3]])
            else:
                data['data'].append([a,r[2],r[4],r[3]])
    elif obj=='sal_subdivision':
        #2020-10-16
        #if c.level_id==1 and u.has_perm('company.sys_admin'):
        #    show_add=True

        data['clmns']=[_('分区名称'),_('分区编码'),_('分区经理'),_('所属战区'),_('备注说明')]       
        cur.execute("SELECT id, name,coalesce(code,''), coalesce(remark,''),"+\
            "coalesce((SELECT username from company_user where id=manager_id),''),"+\
            "coalesce((select name from company_salesdivision where id=division_id),'') "+\
            "FROM company_subsaldivision where company_id="+str(c.id))
        for r in cur.fetchall():
            if can_edit:
                a='<a href="/obj/edit/sal_subdivision/'+str(r[0])+'/">'+r[1]+'</a>'
            else:
                a=r[1]
            data['data'].append([a,r[2],r[4],r[5],r[3]])  
    elif obj=='prd_plant':
        #2020-10-16
        #if c.level_id==1 and u.has_perm('company.sys_admin'):
        #    show_add=True
        cur.execute("select id,name,coalesce(address,''),coalesce(remark,'') from company_prdplant where  company_id="+str(c.id))
        data['clmns']=[_('工厂名称'),_('地址'),_('说明')]
        for r in cur.fetchall():
            if can_edit:
                a='<a href="/obj/edit/prd_plant/'+str(r[0])+'/">'+r[1]+'</a>'
            else:
                a=r[1]
            data['data'].append([a,r[2],r[3]])
    #2022-01-04
    elif obj=='prd_shop':        
        #if c.level_id==1 and u.has_perm('company.sys_admin'):
        #    show_add=True
        cur.execute("select id,coalesce((select name from company_prdplant where id=prd_plant_id),''),name,coalesce(remark,'') "+\
            "from company_prdworkshop where  company_id="+str(c.id))
        data['clmns']=[_('车间名称'),_('工厂名称'),_('说明')]
        for r in cur.fetchall():
            if can_edit:
                a='<a href="/obj/edit/prd_shop/'+str(r[0])+'/">'+r[2]+'</a>'
            else:
                a=r[2]
            data['data'].append([a,r[1],r[3]])
    elif obj=='prd_line':        
        #if c.level_id==1 and u.has_perm('company.sys_admin'):
        #    show_add=True
        cur.execute("select id,name,coalesce(remark,''),"+\
            "coalesce((select name from company_prdplant where id=prd_plant_id),''),"+\
            "coalesce((select name from company_prdworkshop where id=prd_workshop_id),'') "+\
            "from company_prdline where  company_id="+str(c.id))
        data['clmns']=[_('工厂名称'),_('车间名称'),_('生产线'),_('说明')]
        for r in cur.fetchall():
            if can_edit:
                a='<a href="/obj/edit/prd_line/'+str(r[0])+'/">'+r[1]+'</a>'
            else:
                a=r[1]
            data['data'].append([ a,r[3],r[4],r[2] ])
    elif obj=='prd_team':        
        #if c.level_id==1 and u.has_perm('company.sys_admin'):
        #    show_add=True
        cur.execute("select id,name,coalesce(remark,''),"+\
            "coalesce((select name from company_prdplant where id=prd_plant_id),''),"+\
            "coalesce((select name from company_prdworkshop where id=prd_workshop_id),''),"+\
            "coalesce((select name from company_prdline where id=prd_line_id),'') "+\
            "from company_prdteam where  company_id="+str(c.id))
        data['clmns']=[_('工厂名称'),_('车间名称'),_('生产线'),_('班组名称'),_('说明')]
        for r in cur.fetchall():
            if can_edit:
                a='<a href="/obj/edit/prd_team/'+str(r[0])+'/">'+r[1]+'</a>'
            else:
                a=r[1]
            data['data'].append([a,r[3],r[4],r[5],r[2]])
    elif obj=='recipient':
        data['clmns'] = [_('公司/片区'),_('领用单号'), _('领用原因'), _('领用数量'), _('领用人'), _('领用时间'), _('操作人'), _('核销完成时间')]        
        dt = datetime.datetime.now().strftime('%Y-%m-%d')
        dt_fr = request.GET.get('dt_fr', dt)
        dt_to = request.GET.get('dt_to', dt)
        sWhere = " where company_id = "+str(c.id)
        if c.level_id==1:
            sWhere = " where ancestor_id="+str(c.id)
        sWhere=sWhere+" and dt between '"+dt_fr+"' and '"+dt_to+"'"
        s = "select a.code,a.reason,coalesce(b.x||'箱','')||coalesce(c.p||'瓶', '')," + \
            "(select username from logistics.company_user where id=a.receiver_id),"+\
            "to_char(tm,'yyyy-mm-dd HH24:MI:SS')," + \
            "(select username from logistics.company_user where id=a.user_id)," + \
            "case when tm_writeoff is null then '' else to_char(tm_writeoff,'yyyy-mm-dd HH24:MI:SS') end,"+\
            "(select name from logistics.company_company where id=a.company_id) from "+\
            "(select id,code,reason,tm,tm_writeoff,receiver_id,user_id,company_id from logistics.inventory_recipientsbatch"+sWhere + ") a "+\
            "left outer join "+\
            "(select batch_id,count(*) x from logistics.inventory_recipientdetails where label_pkg=3 group by batch_id) b "+\
            " on a.id=b.batch_id "+ \
            "left outer join " + \
            "(select batch_id,count(*) p from logistics.inventory_recipientdetails where label_pkg=1 group by batch_id) c " + \
            " on a.id=c.batch_id"   
        cur.execute(s)
        for r in cur.fetchall():
            data['data'].append([r[7] ,r[0], r[1], r[2], r[3], r[4], r[5], r[6] ])
        data['dt_fr']=dt_fr
        data['dt_to']=dt_to    
        return render(request,'inventory/list_recipient.html',data)
    elif obj=='award':         
        #2020-10-16
        if c.level_id==1 and u.has_perm('company.sys_admin'):
            show_add=True    
        if u.id==1:            
            sWhere=''
            data['clmns'] = [_('奖品名称'), _('公司名称'), _('奖品类型'), _('奖品图片'), _('奖品介绍'), _('折合积分'), _('折合现金')]  
            s = "select id,(select name from sales_awardcategory where id=awardcat_id),name,photo,"+\
                "coalesce(introduction,''),coalesce(to_char(points,'FM9999999MI'),''),coalesce(to_char(amount,'FM99999990.00'),''),"+\
                "(select name from company_company where id=company_id) "+\
                " from sales_award"+sWhere   
            cur.execute(s)
            for r in cur.fetchall():
                img=''   
                nm="<a href='/sales/obj/Award/"+str(r[0])+"/'>"+r[2]+"</a>"
                if r[3]:                
                    img="<img src='/media/"+r[3]+"'></img>"
                data['data'].append([  nm, r[7], r[1], img, r[4], r[5], r[6] ])
        else:
            sWhere=" where company_id="+str(c.id)
            data['clmns'] = [_('奖品名称'), _('奖品类型'), _('奖品图片'), _('奖品介绍'), _('折合积分'), _('折合现金')]  
            s = "select id,(select name from sales_awardcategory where id=awardcat_id),name,photo,"+\
                "coalesce(introduction,''),coalesce(to_char(points,'FM9999999MI'),''),coalesce(to_char(amount,'FM99999990.00'),'') "+\
                "from sales_award"+sWhere   
            cur.execute(s)
            for r in cur.fetchall():
                img=''
                if u.company.level_id==1 and u.has_perm('company.sys_admin'):
                    nm="<a href='/sales/obj/Award/"+str(r[0])+"/'>"+r[2]+"</a>"
                else:
                    nm=r[2]                
                if r[3]:                
                    img="<img src='/media/"+r[3]+"'></img>"
                data['data'].append([ nm, r[1], img, r[4], r[5], r[6] ])
        data['murl']='/sales/obj/Award/'               
    elif obj=='activities':  
        #2020-10-16
        #if u.id==1:
        #    show_add=True  user.hash_perm( 'school.add_studygroup' ) 
        if u.company.level_id==1 and u.has_perm('company.sys_admin'):
            show_add=True
        data['murl']='/sales/obj/Activities/'
        data['obj']='Activities'
        data['clmns'] = [_('活动名称'), _('创建时间'), _('公司名称'), _('活动对象'), _('中奖方案'), _('优先级'), _('开始时间'), _('结束时间'), _('活动状态')]  
        #2020-12-14
        if u.id in [1,3506]:
            can_edit=True
            objs=Activities.objects.all()
        else:
            objs=Activities.objects.filter(company_id=c.id,is_delete=False)
        for act in objs:
            data['data'].append([ 
                "<a href='/sales/obj/Activities/"+str(act.id)+"/'>"+act.name+"</a>", 
                act.tm.strftime('%Y-%m-%d %H:%M:%S'),act.company.name, act.get_target_display(),
                act.get_scheme_display(), act.priority,act.tm_start.strftime('%Y-%m-%d %H:%M:%S'),
                act.tm_end.strftime('%Y-%m-%d %H:%M:%S'),act.get_status_display()
            ])          
    #2020-09-09
    elif obj=='channelpromotions':  
        data['murl']='/sales/channelpromotion/'                    
        data['clmns'] = [_('活动名称'), _('创建时间'),_('扫码场景'),
            _('活动时间'),_('最早发货期'),_('优先级'),_('限定产品'),_('限定战区'),_('当前状态')]  
        sWhere=" where company_id="+str(c.id)+" and is_delete=false"
        #if u.id in [1,6]:
        if u.company.level_id==1 and u.has_perm('company.sys_admin'):
            show_add=True
        s="select id,name,priority,"+\
            "case status when 0 then '未激活' when 1 then '进行中' when 2 then '暂停' when 3 then '已结束' end,"+\
            "coalesce(to_char(tm,'yyyy-mm-dd HH24:MI'),''),"+\
            "coalesce(to_char(tm_start,'yyyy-mm-dd'),'')||' 到 '||coalesce(to_char(tm_end,'yyyy-mm-dd'),''),"+\
            "coalesce(to_char(dt_delivery,'yyyy-mm-dd'),''),"+\
            "(select name from company_company where id=company_id),"+\
            "case when scan_type=2 then '消费者扫码' else array_to_string(array(select "+\
                "(select name from company_level where id=level_id)||'扫码'||(select remark from inventory_voucher where id=vtype_id) "+\
                    " FROM sales_channelpromtionsrebate where promotion_id=a.id),',') end, "+\
            "array_to_string(array(select name from product_product where array[id] <@ products),','),"+\
            "array_to_string(array(select name from company_salesdivision where array[id] <@ div_ids),',') "+\
            "from sales_channelpromotions a "+sWhere
        cur.execute(s)      
        rs=cur.fetchall()
        for r in  rs:   
            s="select id,distance,to_char(val,'999.90'),"+\
                "(select name from company_level where id=level_id),"+\
                "(select remark from inventory_voucher where id=vtype_id) "+\
                "from sales_channelpromtionsrebate where promotion_id="+str(r[0])
            cur.execute(s)
            html=[]
            rs1=cur.fetchall()
            for r1 in rs1:
                d='距离:'
                if r1[1]:
                    d=d+str(r1[1])+'米'
                else:
                    d=d+'无'
                ul="<div>"+r1[3]+"扫码"+r1[4]+" "+d+'<li style="margin-left:10px">扫码奖励:'+r1[2]+"</li>"
                s="select (select name from company_level where id=level_id),to_char(val,'999.90')"+\
                    " from sales_parentrebate where rebate_id="+str(r1[0])
                cur.execute(s)                    
                for r2 in cur.fetchall():
                    ul=ul+'<div><li style="margin-left:10px">'+r2[0]+"经销商扫码奖励"+r2[1].strip()+"</li></div>"
                ul=ul+"</div>"
                html.append(ul)            
                data['data'].append([ 
                    "<a href='/sales/channelpromotion/"+str(r[0])+"/'>"+r[1]+"</a>",
                    r[4], "<span style='cursor:pointer' title='扫码场景设置:' "+\
                        "data-container='#tb' data-toggle='popover' "+\
                        "data-content='"+"".join(html)+"'>"+r[8]+"</span>",
                    r[5], r[6],r[2],r[9],r[10],r[3]  
                ])    
    elif obj=='companycfg':   
        #帐号 system
        usys=User.objects.get(id=14499)  
        if request.method=='GET':
            if u.id==1:
                show_add=True
                murl='/obj/edit/companycfg/'
            elif u.id==3506:
                show_add=True
                murl='/company/create/enterprise/'
            data['murl']=murl
            data['obj']='CompanyCfg' 
            data['enterprise']={'id':usys.company.id,'name':usys.company.name}

            ents=[]
            for c in Company.objects.filter(level_id=1):
                ents.append({'label':c.name,'value':c.id})
            data['ents']=ents

            data['clmns']=[_('公司名称'),_('公司ID'),_('行业类别'),_('创建时间'),_('企业菜单'),_('菜单加载'),_('管理员帐号'),_('账号总数'),('经销商总数')]  
            cur=connection.cursor()
            s="select id,company_id,"+\
                "(select name from company_company where id=company_id),"+\
                "(select count(*) from rbac_companymenu where company_id=c.company_id) menu,"+\
                "array_to_string(array(select username from company_user u where company_id=c.company_id and "+\
                    "4=any(array(SELECT group_id FROM company_user_groups where user_id=u.id))),',') users,"+\
                "case menu_type when 0 then 'left.html' when 1 then '动态加载' end,"+\
                "to_char((select tm from company_company where id=c.company_id),'yyyy-mm-dd HH24:MI:SS'),"+\
                "(select count(*) from company_user where ancestor_id=c.company_id),"+\
                "(select count(*) from company_company where ancestor_id=c.company_id and id<>c.company_id),"+\
                "(select name from company_category where id="+\
                    "(select category_id from company_company where id=c.company_id)) "+\
                "from company_companycfg c"       
        
            cur.execute(s)
            for r in cur.fetchall():
                if r[3]>0:
                    a="<a href='/rbac/action/list_companymenu/?company_id="+str(r[1])+"'>修改</a>"
                else:
                    a="<a href='/rbac/action/create_companymenu/?company_id="+str(r[1])+"'>生成</a>"
                data['data'].append([
                    "<a href='/obj/edit/companycfg/"+str(r[0])+"/'>"+r[2]+"</a>",
                    str(r[1]),r[9],r[6],a,r[5],r[4],r[7],r[8]
                ])
            cur.close()
        else:
            data={'e':'','company_name':''}
            cid=request.POST.get('company_id','')
            if cid:
                cs=Company.objects.filter(id=int(cid))
                if cs:
                    usys.company_id=cs[0].id
                    usys.save()
                else:
                    data['e']='company id error'
            else:
                data['e']='company id missing'
            return JsonResponse(data)
    elif obj == 'consumerwinner':
        data['dt']=True
        data['add']=False
        data['murl'] = '/sales/obj/ConsumerWinner/'
        data['obj'] = 'ConsumerWinner'

        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      

        sWhere=" where dt between '"+dt_fr+"' and '"+dt_to+"' "
        if u.id == 1:    
            data['clmns'] = [_('公司名称'), _('活动名称'),_('奖项'), _('奖品'), _('微信昵称'), _('微信头像'), _('姓名'), _('电话'), _('中奖时间'), _('领奖时间')] 
            # "coalesce((select name from sales_activities where id=a.activity_id),'')," +\            
            s="select (select name from company_company where id=a.company_id)," +\
                "coalesce(a.award_name,''),"+\
                "coalesce(b.name,''),"+\
                "coalesce((select name from sales_award where id=b.award_id),''),"+\
                "coalesce(c.wx_nickname,'')," +\
                "coalesce(c.wx_headimgurl,''),"+\
                "coalesce(a.name,'')," +\
                "coalesce(a.phone,''),"+\
                "to_char(a.tm,'yyyy-mm-dd HH24:MI:SS')," +\
                "case when a.tm_redeemed is null then '' else to_char(tm_redeemed,'yyyy-mm-dd HH24:MI:SS') end " +\
                "from "+\
                "(select * from sales_consumerwinner "+sWhere+" and company_id="+str(c.id)+") a "+\
                "left outer join "+\
                "(select id,activity_id,name,award_id from sales_activityawards) b "+\
                "on a.award_id=b.id "+\
                "left outer join "+\
                "(select id,wx_nickname,wx_headimgurl from sales_consumeraccount) c "+\
                "on a.consumer_id=c.id"  
            cur.execute(s)
            for r in cur.fetchall():
                img = ''           
                if r[5]:
                    img = "<img src='"+r[5]+"' style='height:30px;cursor:pointer;' tt='"+r[4]+"'></img>"           
                data['data'].append([
                    r[0], r[1], r[2], r[3],r[4],img, r[6], r[7],r[8],r[9]
                ])
        else:           
            data['clmns'] = [_('活动名称'),_('奖项'), _('奖品'), _('微信昵称'), _('微信头像'), _('姓名'), _('电话'), _('地址'), _('中奖时间'), _('领奖时间'), _('经销商'), _('区域'),_('生产批次'),_('标签序号'), _('备注')]  

            ssql = ""
            if request.user.company_id == 19318:
                ssql = " and award_cat !='福袋' "
                
            s="select " +\
                "coalesce((select name from sales_activities where id=a.activity_id),'')," +\
                "coalesce(b.name,''),"+\
                "coalesce((select name from sales_award where id=b.award_id),''),"+\
                "coalesce(c.wx_nickname,'')," +\
                "coalesce(c.wx_headimgurl,''),"+\
                "coalesce(a.name,'')," +\
                "coalesce(a.phone,''),"+\
                "coalesce(a.address,''),"+\
                "to_char(a.tm,'yyyy-mm-dd HH24:MI:SS')," +\
                "case when a.tm_redeemed is null then '' else to_char(tm_redeemed,'yyyy-mm-dd HH24:MI:SS') end, " +\
                "coalesce((select name from company_company where id=a.agent_id),''),"+\
                "coalesce( (SELECT name from company_subsaldivision where id = ( (select sub_division_id from company_company where id=a.agent_id))),'' ),"+\
                "coalesce(batch_code,''), "+\
                "coalesce(label_code,''), "+\
                "coalesce(remark,'') "+\
                "from "+\
                "(select * from sales_consumerwinner "+sWhere+" and company_id="+str(c.id) +ssql+") a "+\
                "left outer join "+\
                "(select id,activity_id,name,award_id from sales_activityawards) b "+\
                "on a.award_id=b.id "+\
                "left outer join "+\
                "(select id,wx_nickname,wx_headimgurl from sales_consumeraccount) c "+\
                "on a.consumer_id=c.id"   
            cur.execute(s)
            for r in cur.fetchall():
                img = ''           
                if r[4]:
                    img = "<img src='"+r[4]+"' style='height:30px;cursor:pointer;' tt='"+r[3]+"'></img>"           
                data['data'].append([
                    r[0], r[1], r[2], r[3], img, r[5],r[6], r[7],r[8],r[9],r[10],r[11],r[12],r[13],r[14]
                ])
    #2020-11-30
    elif obj == 'consumerredeem':
        data['dt']=True
        data['add']=False
        data['murl'] = '/sales/obj/ConsumerRedeem/'
        data['obj'] = 'ConsumerRedeem'

        dt_default = datetime.datetime.now().strftime('%Y-%m-%d')
        dt_fr_default = (datetime.datetime.now() - datetime.timedelta(days=30)).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

        sWhere=" where dt between '"+dt_fr+"' and '"+dt_to+"' "
        if u.id == 1 or u.id == 2 or u.id == 4752:
            data['clmns'] = [_('编号'),_('公司名称'), _('活动名称'),_('奖项'), _('奖品'), _('微信昵称'), _('微信头像'), _('姓名'), _('电话'),_('省'),_('市')
                ,_('地址'), _('合成时间'), _('兑奖时间'), _('发货时间'), _('物流单号'), _('审核时间')]
            s="select d.id, (select name from company_company where id=a.company_id)," +\
                "coalesce((select name from sales_activities where id=a.activity_id),'')," +\
                "b.name,"+\
                "coalesce((select name from sales_award where id=b.award_id),''),"+\
                "coalesce(c.wx_nickname,'')," +\
                "coalesce(c.wx_headimgurl,''),"+\
                "coalesce(d.remark,'')," +\
                "coalesce(d.phone,''),"+\
                "coalesce(d.province,''),"+ \
                "coalesce(d.city,'')," + \
                "coalesce(d.address,'')," + \
                "to_char(a.tm,'yyyy-mm-dd HH24:MI:SS')," +\
                "case when a.tm_redeemed is null then '' else to_char(tm_redeemed,'yyyy-mm-dd HH24:MI:SS') end, " \
                "coalesce(to_char(d.tm_send,'yyyy-mm-dd'), ''), "+\
                "coalesce(d.waybill_number,''), "+ \
                "case when d.checked_tm is null then '' else to_char(checked_tm,'yyyy-mm-dd HH24:MI:SS') end, " \
                "d.winner_id " +\
                "from "+\
                "(select * from sales_consumerwinner "+sWhere+") a "+\
                "left outer join "+\
                "(select id,activity_id,name,award_id from sales_activityawards) b "+\
                "on a.award_id=b.id "+\
                "left outer join "+\
                "(select id,wx_nickname,wx_headimgurl from sales_consumeraccount) c "+\
                "on a.consumer_id=c.id " \
                "left outer join " \
                "(select * from sales_consumerredeem) d " \
                "on a.id=d.winner_id " \
                "where d.phone <> ''"
            cur.execute(s)
            for r in cur.fetchall():
                img = ''
                if r[6]:
                    img = "<img src='"+r[6]+"' style='height:30px;cursor:pointer;' tt='"+r[4]+"'></img>"
                if r[0]:
                    r0 = f"<span class='showEditBox' data-id='{r[0]}' data-wid='{r[17]}'>"+str(r[0])+"</span>"
                data['data'].append([
                    r0, r[1], r[2], r[3],r[4],r[5], img,
                    r[7],r[8],r[9],r[10],r[11],r[12],r[13],r[14],r[15],r[16],r[17]
                ])



    #2020-09-17
    elif obj == 'storegeo':                
        data['clmns']=['仓库编号','现有仓库','经销商名称','营销层级','父级','上报时间','操作']
        if c.level_id==1:        
            s="select id,tm,"+\
                "(select name from company_company where id=a.company_id),"+\
                "(select name from company_level   where id=(select level_id  from company_company where id=a.company_id)),"+\
                "(select name from company_company  where id=(select parent_id from company_company where id=a.company_id)),"+\
                "(select count(*) from inventory_storehousebd09 where company_id=a.company_id) "+\
                "from inventory_storehousebd09 a "+\
                "where ancestor_id="+str(c.id)+" and checked_by_id is null"
            cur.execute(s)
            for r in cur.fetchall():
                data['data'].append([ str(r[0]).zfill(8),r[5],r[2],r[3],r[4],r[1].strftime('%Y-%m-%d %H:%M:%S'),
                    "<a href='/inventory/ajax/geo/store/'>核准</a> <a>查看</a>"])     

    #2020-09-23
    elif obj=='promat':
        #2020-10-16
        if c.level_id==1 and u.has_perm('company.sys_admin'):
            show_add=True
        data['murl']='/obj/edit/promat/'       
        data['clmns'] = [_('物料名称'), _('物料编码'), _('规格'), _('单位'), _('备注')] 
        cur.execute("select id,name,coalesce(code,''),coalesce(specification,''),coalesce(unit,''),coalesce(remark,'')"+\
            " from promotion_material where company_id="+str(c.id))
        for r in cur.fetchall():            
            data['data'].append([ 
                "<a href='/obj/edit/promat/"+str(r[0])+"/'>"+r[1]+"</a>", r[2],r[3],r[4],r[5]               
            ])  

    #2020-10-03    
    elif obj=='giftsscheme':   
        #2020-10-16
        if c.level_id==1 and u.has_perm('company.sys_admin'):
            show_add=True

        data['murl']='/promotion/gifts/edit/scheme/'       
        data['clmns'] = [_('时间'),_('编号'), _('活动名称'), _('指定战区'), _('指定分区'),_('客户类型'),_('发放条件'),_('发放方式'),_('最晚申请时间'),_('备注说明')] 
        cur.execute("select id,to_char(tm,'yyyy-mm-dd HH24:MI:SS'),coalesce(code,''),name,"+\
            "array_to_string(array(select name from company_salesdivision  where array[id] <@ div_ids),','),"+\
            "array_to_string(array(select name from company_subsaldivision where array[id] <@ subdiv_ids),','),"+\
            "agent_type,condition,"+\
            "case when means=1 then '总部配送' else '自行申请' end,coalesce(to_char(tm_app_latest,'yyyy-mm-dd HH24:MI:SS'),''),"+\
            "coalesce(remark,'') from promotion_giftsscheme where company_id="+str(c.ancestor_id))
        for r in cur.fetchall():           
            data['data'].append([ 
                "<a href='/promotion/gifts/edit/scheme/"+str(r[0])+"/'>"+r[1]+"</a>",
                r[2],r[3],r[4],r[5],r[6],r[7],r[8],r[9],r[10]
            ])
    elif obj=='giftsapplication':
        #8;"业务员" 12;"销售助理"  4;"系统管理员"  7;"内勤管理组"  12;"销售助理"
        #2020-10-10
        if c.is_expense_account or u.id==1:
            show_add=True

        data['murl']='/promotion/gifts/edit/application/'       
        data['clmns'] = [_('申请时间'),_('申请单位'),_('所属活动'), _('申请理由'),_('审核')] 
        if c.level_id==1:
            sWhere=" where ancestor_id="+str(c.id)
        else:
            sWhere=" where company_id="+str(c.id)+" or (select parent_id from company_company where id=company_id)="+str(c.id) 
        cur.execute("select id,to_char(tm,'yyyy-mm-dd  HH24:MI:SS'),"+\
            "(select name from company_company where id=company_id),"+\
            "(select name from promotion_giftsscheme where id=scheme_id),reason,"+\
            "case when approved_by_id is null then '' else (select username from company_user "+\
                "where id=approved_by_id)||' '||to_char(tm_approved,'yyyy-mm-dd  HH24:MI:SS') end "+\
            "from promotion_giftsapplication"+sWhere)
        for r in cur.fetchall(): 
            #2020-11-06
            apprv=r[5]
            if not apprv:
                if c.level_id==1 and u.has_perm('company.sys_admin'):
                    apprv='<a href="/obj/action/giftsapplication/'+str(r[0])+'/check/" class="chk">审核</a>'
            data['data'].append([ "<a href='/promotion/gifts/edit/application/"+str(r[0])+"/'  class='edt'>"+r[1]+"</a>",
                r[2],r[3],r[4],apprv  ])
        return render(request,'promotion/list_application.html',data)
            
    elif obj=='giftsrequisition':
        #2020-10-10
        if c.is_expense_account or u.id==1:
            show_add=True
            
        data['murl']='/promotion/gifts/edit/requisition/'       
        data['clmns'] =  [_('发起时间'),_('领用单编号'),_('发起人'),_('派发单位'),_('经销商名称'), _('所属活动方案'), _('当前状态')] 
        if c.level_id==1:
            sWhere=" where ancestor_id="+str(c.id)
        else:
            sWhere=" where company_id="+str(c.id)+" or (select parent_id from company_company where id=company_id)="+str(c.id) 
        cur.execute("select id,to_char(tm,'yyyy-mm-dd  HH24:MI:SS'),"+\
            "(select username from company_user where id=user_id),"+\
            "(select name from company_company where id=company_id),"+\
            "coalesce((select name from company_company where id=agent_id),''),"+\
            "(select name from promotion_giftsscheme where id=scheme_id),"+\
            "(select name from promotion_giftsstatus where id=status_id) "+\
            "from promotion_giftsrequisition "+sWhere)
        for r in cur.fetchall():           
            data['data'].append([ 
                "<a href='/promotion/gifts/edit/requisition/"+str(r[0])+"/'>"+r[1]+"</a>",
                str(r[0]).zfill(5),r[2],r[3],r[4],r[5],
                "<a href='/promotion/gifts/requistion/log/"+str(r[0])+"/'>"+r[6]+"</a>" 
            ]) 


    #2020-10-14
    elif obj=='bugsell':        
        dt = datetime.datetime.now().strftime('%Y-%m-%d')
        dt_fr = request.GET.get('date_from', dt)
        dt_to = request.GET.get('date_to', dt)   
        dt_max = max(dt_fr, dt_to)
        dt_min = min(dt_fr, dt_to)
        dt_fr = dt_min
        dt_to = dt_max
        data={'e':'','data':[],'date_from':dt_fr,'date_to':dt_to}   
        data['clmns']=[_('疑似窜货经销商'),_('营销层级'),_('上级公司'),_('授权销售区域'),_('窜货记录数'),_('操作')] 
        #非公司级帐户不能查询窜货
        if c.level_id==1:            
            s="select b.name,a.cnt,array_to_string("+\
                    "array(select name from gis.adm where id in (select unnest((select b.adm_ids)))),','),"+\
                "(select name from company_level where id=b.level_id), "+\
                "(select name from company_company where id=b.parent_id),"+\
                "b.id from "+\
                "(select agent_id,count(*) cnt FROM sales_bugsell where ancestor_id="+str(c.ancestor_id)+" and dt between '"+dt_fr+"' and '"+dt_to+"' group by agent_id) a "+\
                "left outer join "+\
                "(select id,name,adm_ids,level_id,parent_id from company_company) b "+\
                "on a.agent_id=b.id"
            cur.execute(s)
            for r in cur.fetchall():
                data['data'].append([ 
                    "<a href='/sales/bugsell/agent/"+str(r[5])+"/?date_from="+dt_fr+"&date_to="+dt_to+"' agt='"+r[0]+"'>"+r[0]+"</a>",
                    r[3],r[4],r[2],r[1],
                    "<a href='/sales/bugsell/agent/map/"+str(r[5])+"/?date_from="+dt_fr+"&date_to="+dt_to+"' agt='"+r[0]+"'>地图显示</a>"
                ])
        return render(request,'sales/list_bugsell.html',data) 
    #2022-06-10
    elif obj=='fakelabel':        
        dt = datetime.datetime.now().strftime('%Y-%m-%d')
        dt_fr = request.GET.get('date_from', dt)
        dt_to = request.GET.get('date_to', dt)   
        dt_max = max(dt_fr, dt_to)
        dt_min = min(dt_fr, dt_to)
        dt_fr = dt_min
        dt_to = dt_max
        data={'e':'','data':[],'date_from':dt_fr,'date_to':dt_to}   
        data['clmns']=[_('涉及经销商'),_('营销层级'),_('上级公司'),_('授权销售区域'),_('假货记录数'),_('操作')] 
        #非公司级帐户不能查询
        if c.level_id==1:            
            s="select b.name,a.cnt,array_to_string("+\
                    "array(select name from gis.adm where id in (select unnest((select b.adm_ids)))),','),"+\
                "(select name from company_level where id=b.level_id), "+\
                "(select name from company_company where id=b.parent_id),"+\
                "b.id from "+\
                "(select agent_id,count(*) cnt FROM sales_fakelabel where ancestor_id="+str(c.ancestor_id)+" and dt between '"+dt_fr+"' and '"+dt_to+"' group by agent_id) a "+\
                "left outer join "+\
                "(select id,name,adm_ids,level_id,parent_id from company_company) b "+\
                "on a.agent_id=b.id"
            cur.execute(s)
            for r in cur.fetchall():
                data['data'].append([ 
                    "<a href='/sales/fakelabel/agent/"+str(r[5])+"/?date_from="+dt_fr+"&date_to="+dt_to+"' agt='"+r[0]+"'>"+r[0]+"</a>",
                    r[3],r[4],r[2],r[1],
                    "<a href='/sales/fakelabel/agent/map/"+str(r[5])+"/?date_from="+dt_fr+"&date_to="+dt_to+"' agt='"+r[0]+"'>地图显示</a>"
                ])
        return render(request,'sales/list_fakelabel.html',data)     
    elif obj=='bugsell_cluster_map':        
        dt = datetime.datetime.now().strftime('%Y-%m-%d')
        dt_fr = request.GET.get('date_from', dt)
        dt_to = request.GET.get('date_to', dt)   
        dt_max = max(dt_fr, dt_to)
        dt_min = min(dt_fr, dt_to)
        dt_fr = dt_min
        dt_to = dt_max
        data={'e':'','data':[],'date_from':dt_fr,'date_to':dt_to,'cities':[],'agents':[],'products':[],'allCnt':0}   
        #非公司级帐户不能查询窜货
        if c.level_id==1:     
            
            sfilter = f"ancestor_id = {str(c.ancestor_id)} and dt between '{dt_fr}' and '{dt_to}' and array_length(lng_lat,1)>0 "
            s=f"""SELECT label_code,address,distance,lng_lat,tm,(select name from product_product where id = product_id),
                (select name from company_company where id = agent_id) 
                FROM sales_bugsell 
                where {sfilter} """
                
            cur.execute(s)
            
            for r in cur.fetchall():
                data['data'].append({
                    'label_code':r[0],
                    'address':r[1],
                    'distance':float(r[2]),
                    'lnglat':[float(r[3][0]),float(r[3][1])],
                    'tm':r[4].strftime('%Y-%m-%d %H:%M:%S'),
                    'product':r[5],
                    'agent': r[6]
                })
            data['data'] = json.dumps(data['data'])
            
            s=f"SELECT count(id) as cnt,city FROM sales_bugsell where {sfilter} GROUP BY city ORDER BY cnt desc limit 10"
            cur.execute(s)
            for r in cur.fetchall():
                data['cities'].append({
                    'name': r[1],
                    'cnt': r[0]
                })
            s=f"SELECT count(id) as cnt,agent_id,(select name from company_company where id = agent_id) FROM sales_bugsell where {sfilter} GROUP BY agent_id ORDER BY cnt desc limit 10"
            cur.execute(s)
            for r in cur.fetchall():
                data['agents'].append({
                    'name': r[2],
                    'cnt': r[0]
                })
            s=f"SELECT count(id) as cnt,product_id,(select name from product_product where id = product_id) FROM sales_bugsell where {sfilter} GROUP BY product_id ORDER BY cnt desc limit 10"
            cur.execute(s)
            for r in cur.fetchall():
                data['products'].append({
                    'name': r[2],
                    'cnt': r[0]
                })
            s=f"SELECT count(id) FROM sales_bugsell where {sfilter}"
            cur.execute(s)
            data['allCnt'] = cur.fetchone()[0]
            #print(data)
        return render(request,'sales/bugsell_cluster_map.html',data) 
    elif obj=='fakelabel_cluster_map':        
        dt = datetime.datetime.now().strftime('%Y-%m-%d')
        dt_fr = request.GET.get('date_from', dt)
        dt_to = request.GET.get('date_to', dt)   
        dt_max = max(dt_fr, dt_to)
        dt_min = min(dt_fr, dt_to)
        dt_fr = dt_min
        dt_to = dt_max
        data={'e':'','data':[],'date_from':dt_fr,'date_to':dt_to,'cities':[],'agents':[],'products':[],'allCnt':0}   
        #非公司级帐户不能查询窜货
        if c.level_id==1:     
            
            sfilter = f"ancestor_id = {str(c.ancestor_id)} and dt between '{dt_fr}' and '{dt_to}' and array_length(lng_lat,1)>0"
            s=f"""SELECT label_code,address,city,lng_lat,tm,(select name from product_product where id = product_id),
                (select name from company_company where id = agent_id) 
                FROM sales_fakelabel 
                where {sfilter} """
                
            cur.execute(s)
            
            for r in cur.fetchall():
                data['data'].append({
                    'label_code':r[0],
                    'address':r[1],
                    'city':r[2],
                    'lnglat':[float(r[3][0]),float(r[3][1])],
                    'tm':r[4].strftime('%Y-%m-%d %H:%M:%S'),
                    'product':r[5],
                    'agent': r[6]
                })
            data['data'] = json.dumps(data['data'])
            
            s=f"SELECT count(id) as cnt,city FROM sales_fakelabel where {sfilter} GROUP BY city ORDER BY cnt desc limit 10"
            cur.execute(s)
            for r in cur.fetchall():
                data['cities'].append({
                    'name': r[1],
                    'cnt': r[0]
                })
            s=f"SELECT count(id) as cnt,agent_id,(select name from company_company where id = agent_id) FROM sales_fakelabel where {sfilter} GROUP BY agent_id ORDER BY cnt desc limit 10"
            cur.execute(s)
            for r in cur.fetchall():
                data['agents'].append({
                    'name': r[2],
                    'cnt': r[0]
                })
            s=f"SELECT count(id) as cnt,product_id,(select name from product_product where id = product_id) FROM sales_fakelabel where {sfilter} GROUP BY product_id ORDER BY cnt desc limit 10"
            cur.execute(s)
            for r in cur.fetchall():
                data['products'].append({
                    'name': r[2],
                    'cnt': r[0]
                })
            s=f"SELECT count(id) FROM sales_fakelabel where {sfilter}"
            cur.execute(s)
            data['allCnt'] = cur.fetchone()[0]
            #print(data)
        return render(request,'sales/fakelabel_cluster_map.html',data) 
    #2021-02-05
    elif obj in ['bankinfoapplication','wxwalletapplication']:    
        dt = datetime.datetime.now().strftime('%Y-%m-%d')
        dt_fr = request.GET.get('date_from', dt)
        dt_to = request.GET.get('date_to', dt)   
        astatus_id=request.GET.get('status_id','0')
        dt_max = max(dt_fr, dt_to)
        dt_min = min(dt_fr, dt_to)
        dt_fr = dt_min
        dt_to = dt_max
        if astatus_id=='0':
            sStatus=''
        else:
            sStatus=" and status_id="+astatus_id
        astatus=[]
        cur=connection.cursor()
        cur.execute("select id,name from company_applicationstatus where company_id="+str(c.ancestor_id)+" order by sn")
        for r in cur.fetchall():
            astatus.append([r[0],r[1]])        
        
        if obj=='wxwalletapplication':
            clmns=["id","申请时间","申请人","当前状态","上级经销商","所属大区","经销商","手机号码","绑定微信","收款人实名","内勤审核","财务审核"]
            #flds=",w.wx_nickname,w.wx_headimgurl,a.cellphone,a.realname "
            flds=",coalesce(w.wx_nickname,'')||"+\
                "case when (select wxopenid_id from company_wxwallet where company_id=a.company_id limit 1) is null or a.status_id=5 then '' "+\
                "else '<br/><span style=\"color:red;\">'||(select wx_nickname from company_wxopenid where id="+\
                        "(select wxopenid_id from company_wxwallet where company_id=a.company_id limit 1))||"+\
                        "' '||(select realname from company_wxwallet where company_id=a.company_id limit 1)||'</span>' end,"+\
                "coalesce(w.wx_headimgurl,''),a.cellphone,a.realname "


            aflds="id,ancestor_id,app_nickname,cellphone,realname,tm,remark,payee_wxopenid_id,company_id,dt,priority,"+\
                "nickname,user_id,wxopenid_id,payee_company_id,status_id,wxwallet_id"
            sL="left outer join "+\
                "(select id,wx_nickname,wx_headimgurl from company_wxopenid) w "+\
                "on a.payee_wxopenid_id=w.id "
            subtt='微信钱包审核'
        else:
            clmns=["id","申请时间","申请人","当前状态","上级经销商","所属大区","经销商","开户行","帐号","收款人","内勤审核","财务审核"]
            flds=",a.bank_name,a.bank_account,a.payee "
            aflds="id,ancestor_id,company_id,bank_name,bank_account,tm,dt,remark,nickname,payee,user_id,wxopenid_id,payee_company_id,status_id"
            sL='' 
            subtt='银行卡审核'
        d=[]
        if c.level_id==1:
            sWhere=" where ancestor_id="+str(c.id)+" and (dt between '"+dt_fr+"' and '"+dt_to+"')"+sStatus 
        elif c.is_expense_account:        
            sWhere=" where (select division_id from company_company where id=company_id)="+str(c.division_id)
        else:
            sWhere=" where company_id="+str(c.id)+" and (dt between '"+dt_fr+"' and '"+dt_to+"')"+sStatus      
        
        #用户组别
        group_ids=[]
        cur.execute("select group_id FROM company_user_groups where user_id="+str(u.id))
        for r in cur.fetchall():
            group_ids.append(r[0])
        s="select a.id,to_char(a.tm,'yyyy-mm-dd HH24:MI'),coalesce(a.nickname,''),coalesce(a.headimgurl,''),coalesce(c.name,''),"+\
            "coalesce((select name from company_applicationstatus where id=status_id),''),"+\
            "case when parent_id="+str(c.ancestor_id)+" then '' else (select name from company_company where id=c.parent_id) end,"+\
            "coalesce((select name from company_salesdivision where id=c.division_id),''),"+\
            "coalesce((select username from company_user where id=a.user_id),''),"+\
            "coalesce((select username from company_user where id=i.user_id)||' '||to_char(i.tm,'yyyy-mm-dd HH24:MI'),''),"+\
            "coalesce((select username from company_user where id=f.user_id)||' '||to_char(f.tm,'yyyy-mm-dd HH24:MI'),''),"+\
            "a.status_id,c.level_id,c.division_id,c.sub_division_id"+flds+"from "+\
            "(select * from company_"+obj+" "+sWhere+") a "+\
            "left outer join "+\
            "(select id,name,parent_id,division_id,sub_division_id,level_id from company_company) c "+\
            "on a.company_id=c.id "+sL+\
            "left outer join "+\
            "(select dt,tm,user_id,obj_id,comment from company_applicationreviewlog "+\
                "where company_id="+str(c.ancestor_id)+" and content_type='"+obj+"' and status_id=2) i "\
            "on a.id=i.obj_id "+\
            "left outer join "+\
            "(select dt,tm,user_id,obj_id,comment from company_applicationreviewlog "+\
                "where company_id="+str(c.ancestor_id)+" and content_type='"+obj+"' and status_id=3) f "\
            "on a.id=f.obj_id "



        #/company/html/applicationreviewlog/    

        s="select a.id,to_char(a.tm,'yyyy-mm-dd HH24:MI'),coalesce(a.nickname,''),'',coalesce(c.name,''),"+\
            "coalesce((select name from company_applicationstatus where id=status_id),''),"+\
            "case when c.parent_id="+str(c.ancestor_id)+" then '' else coalesce((select name from company_company where id=c.parent_id),'') end,"+\
            "coalesce((select name from company_salesdivision where id=c.division_id),''),"+\
            "coalesce((select username from company_user where id=a.user_id),''),"+\
            "coalesce((select username from company_user where id=i.user_id)||' '||to_char(i.tm,'yyyy-mm-dd HH24:MI'),''),"+\
            "coalesce((select username from company_user where id=f.user_id)||' '||to_char(f.tm,'yyyy-mm-dd HH24:MI'),''),"+\
            "a.status_id,c.level_id,c.division_id,c.sub_division_id"+flds+"from "+\
            "(select * from company_"+obj+" "+sWhere+") a "+\
            "left outer join "+\
            "(select id,name,parent_id,division_id,sub_division_id,level_id from company_company where ancestor_id="+str(c.ancestor_id)+") c "+\
            "on a.company_id=c.id "+sL+\
            "left outer join "+\
            "(select dt,tm,user_id,obj_id,comment from company_applicationreviewlog "+\
                "where company_id="+str(c.ancestor_id)+" and content_type='"+obj+"' and status_id=2) i "\
            "on a.id=i.obj_id "+\
            "left outer join "+\
            "(select dt,tm,user_id,obj_id,comment from company_applicationreviewlog "+\
                "where company_id="+str(c.ancestor_id)+" and content_type='"+obj+"' and status_id=3) f "\
            "on a.id=f.obj_id "

        s="select a.id,to_char(a.tm,'yyyy-mm-dd HH24:MI'),coalesce(a.nickname,''),'',coalesce(c.name,''),"+\
            "coalesce((select name from company_applicationstatus where id=status_id),''),"+\
            "case when c.parent_id="+str(c.ancestor_id)+" then '' else coalesce((select name from company_company where id=c.parent_id),'') end,"+\
            "coalesce((select name from company_salesdivision where id=c.division_id),''),"+\
            "coalesce((select username from company_user where id=a.user_id),''),"+\
            "coalesce((select username from company_user where id=i.user_id)||' '||to_char(i.tm,'yyyy-mm-dd HH24:MI'),''),"+\
            "coalesce((select username from company_user where id=f.user_id)||' '||to_char(f.tm,'yyyy-mm-dd HH24:MI'),''),"+\
            "a.status_id,c.level_id,c.division_id,c.sub_division_id"+flds+"from "+\
            "(select * from company_"+obj+" "+sWhere+") a "+\
            "left outer join "+\
            "(select id,name,parent_id,division_id,sub_division_id,level_id from company_company where ancestor_id="+str(c.ancestor_id)+") c "+\
            "on a.company_id=c.id "+sL+\
            "left outer join "+\
            "(select dt,tm,user_id,obj_id,comment from company_applicationreviewlog "+\
                "where company_id="+str(c.ancestor_id)+" and content_type='"+obj+"' and status_id=2) i "\
            "on a.id=i.obj_id "+\
            "left outer join "+\
            "(select dt,tm,user_id,obj_id,comment from company_applicationreviewlog "+\
                "where company_id="+str(c.ancestor_id)+" and content_type='"+obj+"' and status_id=3) f "\
            "on a.id=f.obj_id "



        cur.execute(s)
       

        rs=cur.fetchall()
        is_admin=u.has_perm('company.sys_admin')
        for r in rs: 
            act=''
            status_id=r[11]           
            #if (u.groups.filter(id=4) and status_id in [1,2,4]) or (u.groups.filter(id=12) and status_id in [1,4]) or (u.groups.filter(id=18) and status_id in [1,2,4]):
            if (4 in group_ids  and status_id in [1,2,4]) or (12 in group_ids and status_id in [1,4]) or ((18 in group_ids or 20 in group_ids) and status_id in [1,2,4]):
                act='<br/><a class="btn-xs btn-danger act" act="reject" href="/company/application/approve/"  app_id="'+str(r[0])+'">驳回</a>'
            #if status_id in [5,6]:
            #if status_id not in [1]:
            #    li=''
            #    s="select to_char(tm,'yyyy-mm-dd HH24:MI'),coalesce(comment,''),"+\
            #        "(select last_name from company_user where id=user_id),"+\
            #        "(select name from company_applicationstatus where id=status_id) "+\
            #        "from company_applicationreviewlog "+\
            #        "where content_type='"+obj+"' and obj_id="+str(r[0])+" order by id"               
            #    cur.execute(s)
            #    for l in cur.fetchall(): 
            #        lg=l[3]
            #        if l[1]:
            #            lg=lg+":"+l[1]
            #        li=li+'<li>'+l[0]+' '+l[2]+'</li>'+lg                   
            #    if li:                    
            #        li="<span style='cursor:pointer'  title='审核日志：'  data-container='#tb td' "+\
            #            "data-toggle='popover' data-content='<div>"+li+"</div>' >"+r[5]+"</span>"  
            #    else:
            #        li=r[5]
            #    status=li
            #else:
            #    status='<span class="app-status" app_id="'+str(r[0])+'">'+r[5]+'</span>'
            if status_id>1:
                status='<span class="app-status" app_id="'+str(r[0])+'">'+\
                    '<a class="log" href="/company/html/applicationreviewlog/?content_type='+obj+'&obj_id='+str(r[0])+'">'+r[5]+'</a></span>'
            else:
                status='<span class="app-status" app_id="'+str(r[0])+'">'+r[5]+'</span>'
            
            status=status+act
            #7;"内勤管理组" 18;"财务" 12;"销售助理" 4;"系统管理员" 20;"出纳"
            internal=r[9]   
            intl='<input type="checkbox" id="cb-internal-'+str(r[0])+'" app_id="'+str(r[0])+'" user_group_id="12"/><label for="cb-internal-'+str(r[0])+'">通过</label>'
            #if not internal and status_id in [1,4] and u.groups.filter(id=12):
            if not internal and status_id in [1,4] and 12 in group_ids:                
                internal=intl                
            finance=r[10]   
            #if not finance and status_id==2 and u.groups.filter(id=18):
            if not finance and status_id==2 and (18 in group_ids or 20 in group_ids):                
                finance='<input type="checkbox" id="cb-finance-'+str(r[0])+'" app_id="'+str(r[0])+'" user_group_id="18" /><label for="cb-finance-'+str(r[0])+'">通过</label>'             
            
            #if obj=='bankinfoapplication':  
            #    d.append([r[0],r[1],r[8]+"<img data-toggle='popover'  data-trigger='hover' title='"+r[2]+"' src='"+r[3]+"'/>",status,r[6],
            #        r[7],r[4],r[15],r[16],r[17],internal,finance,
            #    ])
            #else:                
            #    d.append([r[0],r[1],r[8]+"<img data-toggle='popover'  data-trigger='hover' title='"+r[2]+"' src='"+r[3]+"'/>",status,r[6],
            #        r[7],r[4],r[17],r[15]+"<img src='"+r[16]+"' />",r[18],internal,finance,
            #    ])   
            tm=r[1]
            if is_admin:
                tm='<a class="edit" href="/company/obj/edit/'+obj+'/'+str(r[0])+'/">'+r[1]+'</a>'

            #2021-03-11,不显示微信头像
            if obj=='bankinfoapplication':  
                d.append([r[0],tm,r[8],status,r[6],r[7],
                    '<span class="agent">'+r[4]+'</span>',r[15],r[16],r[17],internal,finance,
                ])
            else:                
                d.append([r[0],tm,r[8],status,r[6],r[7],
                    '<span class="agent">'+r[4]+'</span>',r[17],r[15],r[18],internal,finance,
                ])             

        

        return render(request,'sales/channelrebate/list_wxwallet_application.html',
                {'clmns':clmns,'data':d,'subtt':subtt,'content_type':obj,'date_from':dt_fr,'date_to':dt_to,'status':astatus,'status_id':astatus_id})   
    elif obj=='brandrecommender':
        data['clmns']=["添加时间","姓名","电话","地址","备注","关联经销商","OpenID","是否过审","操作"]
        if c.level_id==1 and u.has_perm('company.sys_admin'):
            show_add=True
        data['murl']='/obj/edit/brandrecommender/'   
        cur=connection.cursor()
        cur.execute("select id,name,coalesce(cellphone,''),coalesce(address,''),coalesce(remark,''),(select name from company_company where id=agent_id),coalesce(wxopenid,''),coalesce(is_active,False),coalesce(to_char(tm,'yyyy-mm-dd HH24:MI:SS'),'')"+\
            " from company_brandrecommender where company_id="+str(c.id)+" and not is_hidden")        
        for r in cur.fetchall():
            is_active_text = ''
            dot = ""

            if r[7] == True:
                is_active_text = '已过审'
                dot="<span style='display: inline-block;background:green;width:13px;height:13px;margin-right: 0.5rem;border-radius:50%;'></span>"
            else:
                is_active_text = '未审'
                dot="<span style='display: inline-block;background:red;width:13px;height:13px;margin-right: 0.5rem;border-radius:50%;'></span>"
            
            js = r"$.ajax({url:'/company/brandrecommender_active/?id=" + str(r[0]) + "'});$.refreshContent();"

            if u.id == 4574:
                del_btn = '<button class="btn btn-danger btn-xs" onclick='+'"deleteObj(' +"`brandrecommender`"+','+ str(r[0]) + ")\"'>隐藏</button>"
            else:
                del_btn = '-'

            data['data'].append([
                '<a style="text-decoration:underline;" href="/obj/edit/brandrecommender/'+str(r[0])+'/">'+r[8]+'</a>',
                r[1],r[2],r[3],r[4],r[5],r[6],
                dot+r'<a style="text-decoration:underline;" href="/obj/list/brandrecommender/" onclick="'+js+'">'+is_active_text+'</a>',
                del_btn
            ])
    #2021-02-22
    elif obj=='inventoryticket':
        dt= datetime.datetime.now().strftime('%Y-%m-%d')       
        dt_fr = request.GET.get('date_from', dt)
        dt_to = request.GET.get('date_to', dt)
        data['clmns']=["生成时间","操作人","订单号","发货仓库","经销商名称","产品名称","发货数量","打印"]
        if (c.level_id==1 and u.has_perm('company.sys_admin')) or u.id in [4788,4783]: 
            show_add=True
        data['murl']='/inventory/salesticket/'   
        cur=connection.cursor()
        sDt=" and (dt between '"+dt_fr+"' and '"+dt_to+"')"
        s="select a.id,a.tm,u.username,coalesce(a.order_no,''),coalesce(s.name,''),c.name,p.name,a.qty from "+\
            "(select id,to_char(tm,'yyyy-mm-dd HH24:MI:SS') tm,order_no,product_id,agent_id,store_id,qty,user_id "+\
                "from inventory_tickets where company_id="+str(c.id)+sDt+") a "+\
            "left outer join "+\
            "(select id,name from company_company ) c "+\
            "on a.agent_id=c.id "+\
            "left outer join "+\
            "(select id,name from product_product) p "+\
            "on a.product_id=p.id "+\
            "left outer join "+\
            "(select id,name from inventory_storehouse) s "+\
            "on a.store_id=s.id "+\
            "left outer join "+\
            "(select id,username,last_name from company_user) u "+\
            "on a.user_id=u.id"
        cur.execute(s)        
        for r in cur.fetchall():
            data['data'].append([
                '<a href="/inventory/salesticket/'+str(r[0])+'/">'+r[1]+'</a>',
                r[2],r[3],r[4],r[5],r[6],r[7],
                '<input type="checkbox" id="cb-'+str(r[0])+'" tk_id="'+str(r[0])+'" /><label for="cb-'+str(r[0])+'">打印</label>'
            ])
        data['show_add']=show_add
        data['date_from']=dt_fr
        data['date_to']=dt_to     
        return render(request,'inventory/list_inventorytickets.html',data) 
    elif obj=='bulletinboard':
        dt= datetime.datetime.now().strftime('%Y-%m-%d')       
        dt_fr = request.GET.get('date_from', '2021-01-01')
        dt_to = request.GET.get('date_to', dt)
        data=list_bulletinboard(u,c,dt_fr,dt_to,is_app=False)        
        return render(request,'company/list_bullitinboard.html',data)

    #2021-03-01
    elif obj=='rebateinvoice':        
        dt= datetime.datetime.now().strftime('%Y-%m-%d')       
        dt_fr = request.GET.get('date_from', dt)
        dt_to = request.GET.get('date_to', dt)
        sCompany=" where company_id="+str(c.id)
        if c.level_id==1:
            if u.has_perm('company.sys_admin'):
                show_add=True
        else:
            if c.is_expense_account:
                sCompany=" where ()="+str(c.division_id)
            else:
                sCompany=" where agent_id="+str(c.id)        

        s="select id,to_char(tm,'yyyy-mm-dd HH24:MI:SS'),code,numbers,to_char(dt_billing,'yyyy-mm-dd'),"+\
            "to_char(amount,'9999999.99'),coalesce(claimant,''),coalesce(remark,''),"+\
            "'',"+\
            "coalesce((select name from company_salesdivision where id=(select division_id from company_company where id=agent_id)),''),"+\
            "(select username from company_user where id=user_id) "+\
            "from sales_rebateinvoice where  company_id="+str(c.id)   
        cur.execute(s)
        data['clmns']=[_('录入时间'),_('发票代码'),_('发票号码'),_('开票日期'),_('发票金额(元)'),_('报销人'),_('战区'),_('登记人'),_('备注'),_('附件')]
        for r in cur.fetchall():
            if u.has_perm('company.sys_admin'):
                a='<a class="lnk" href="/obj/edit/rebateinvoice/'+str(r[0])+'/">'+r[1]+'</a>'
            else:
                a=r[1]
            v="<a class='view-img' href='/obj/action/rebateinvoice/"+str(r[0])+"/view/'>查看</a>"
            data['data'].append([ a,r[2],r[3],r[4],r[5],r[6],r[9],r[10],r[7], v ])
        data['show_add']=show_add
        data['date_from']=dt_fr
        data['date_to']=dt_to   
        return render(request,'sales/list_rebateinvoice.html',data)

    elif obj=='entapplication':
        #8;"业务员" 12;"销售助理"  4;"系统管理员"  7;"内勤管理组"  12;"销售助理" 17;"营销管理组"
        dt= datetime.datetime.now().strftime('%Y-%m-%d')
        dt_default= (datetime.datetime.now() - datetime.timedelta(days=7)).strftime('%Y-%m-%d')
        dt_fr = request.GET.get('date_fr', dt_default)
        dt_to = request.GET.get('date_to', dt)
        is_writeoff=request.GET.get('is_writeoff','-1')
        bConfirm=False        
        if 8 in group_ids or 7 in group_ids or 17 in group_ids:
            bConfirm=True
        sWriteoff=''
        if is_writeoff=='0':
            sWriteoff=' and is_writeoff=false'
        elif is_writeoff=='1':
            sWriteoff=' and is_writeoff=true'
        sDt=" and (dt between '"+dt_fr+"' and  '"+dt_to+"')"
        
        sCompany=" where user_id="+str(u.id)
        if c.level_id==1:
            if u.has_perm('company.sys_admin'):
                show_add=True
            sCompany=" where company_id="+str(c.id)
        elif 7 in group_ids or 12 in group_ids or 17 in group_ids:
            sCompany=" where (select division_id from company_company where id="+\
                "(select company_id from company_user where id=a.user_id))="+str(c.division_id)     
        s="select id,to_char(tm,'yyyy-mm-dd HH24:MI:SS'),subject,headcount,reason,location,"+\
            "(select count(*) from entertainment_entwriteoff where entapplication_id=a.id)||'/'||cnt_bottles,"+\
            "coalesce((select name from company_department where id=department_id),"+\
                "(select name from company_company where id=(select company_id from company_user where id=user_id)),''),"+\
            "coalesce((select name from company_staff where id=a.staff_id),"+\
                "(select last_name from company_user where id=a.user_id),''),"+\
            "(select username from company_user where id=user_confirmed_id)||to_char(tm_confirmed,'yyyy-mm-dd HH24:MI'),"+\
            "user_id,coalesce(schema_no,'') from entertainment_entapplication a"+sCompany+sDt+sWriteoff           
        cur.execute(s)
        data['clmns']=[_('申请时间'),_('申请部门'),_('总案流水号'),_('经办人'),_('接待对象'),_('接待人数'),_('接待原因'),_('接待地的点'),_('核销(瓶)'),_('确认')]
        for r in cur.fetchall(): 
            sConfrim=''
            cur.execute("select coalesce(p.name,''),a.cnt,p.qty_p from "+\
                "(select product_id,count(*) cnt from entertainment_entwriteoff where entapplication_id="+str(r[0])+\
                    " group by product_id) a "+\
                "left outer join "+\
                "(select id,name,qty_p from product_product) p "+\
                "on a.product_id=p.id")
            tip=''
            for rr in cur.fetchall():                                
                tip=tip+'<li><span style="margin-right:10px;">'+rr[0]+":"+str(rr[1])+"瓶</div></li>"
            if tip:
                tip="<span style='cursor:pointer;text-decoration:underline;'  title='用酒数量：'  data-container='#tb td' "+\
                    "data-toggle='popover' data-content='<div>"+tip+"</div>' >"+r[6]+"</span>" 
            else:
                tip=r[6]

            #prt="<a href='obj/action/entapplication/"+str(r[0])+"/print/'>打印</a>"            
            if (u.has_perm('company.sys_admin') or bConfirm) and r[9]:
                a='<a style="text-decoration:underline;" href="obj/action/entapplication/'+str(r[0])+'/print/">'+r[1]+'</a>'
            else:
                if u.id==r[10]:
                    a='<a style="text-decoration:underline;" href="obj/action/entapplication/'+str(r[0])+'/print/">'+r[1]+'</a>'
                else:
                    a=r[1]
            if r[9]:
                sConfirm=r[9]
            else:
                sConfirm='<input type="checkbox" id="cb-'+str(r[0])+'" /><label for="cb-'+str(r[0])+'">确认</label>'
            data['data'].append([ a,r[7],r[11],r[8],r[2],r[3],r[4],r[5],tip,sConfirm ])
            
        data['show_add']=show_add
        data['date_from']=dt_fr
        data['date_to']=dt_to    
        
        data['is_writeoff']=is_writeoff
        return render(request,'entertainment/list_entapplication.html',data)
    elif obj=='actapplication':
        dt= datetime.datetime.now().strftime('%Y-%m-%d')
        dt_default=(datetime.datetime.now() - datetime.timedelta(days=7)).strftime('%Y-%m-%d')
        dt_fr = request.GET.get('date_fr', dt_default)
        dt_to = request.GET.get('date_to', dt)
        is_writeoff=request.GET.get('is_writeoff','-1')
        bConfirm=False
        if 8 in group_ids or 7 in group_ids or 17 in group_ids:
            bConfirm=True
        sWriteoff=''
        if is_writeoff=='0':
            sWriteoff=' and is_writeoff=false'
        elif is_writeoff=='1':
            sWriteoff=' and is_writeoff=true'
        sDt=" and (dt between '"+dt_fr+"' and  '"+dt_to+"')"
        sCompany=" where user_id="+str(u.id)
        if c.level_id==1:
            if u.has_perm('company.sys_admin'):
                show_add=True
            sCompany=" where company_id="+str(c.id)
        elif 7 in group_ids or 12 in group_ids or 17 in group_ids:
            sCompany=" where (select division_id from company_company where id="+\
                "(select company_id from company_user where id=a.user_id))="+str(c.division_id) 

        #2021-04-19
        if only_myself:
            sCompany=" where user_id="+str(u.id)   
        s="select id,to_char(tm,'yyyy-mm-dd HH24:MI:SS'),coalesce(subject,''),"+\
            "coalesce((select name from company_department where id=department_id),"+\
                "(select name from company_company where id=agent_id),''),"+\
            "(select coalesce(last_name,username) from company_user where id=user_id),"+\
            "coalesce(schema_no,''),coalesce((select name from entertainment_category where id=category_id),''),"+\
            "coalesce((select name from company_salesdivision where id=division_id),''),"+\
            "case when a.parent_id="+str(c.ancestor_id)+" then '' else coalesce((select name from company_company where id=a.parent_id),'') end,"+\
            "coalesce(hotel,''),coalesce(dinner_address,''),coalesce(cellphone,''),to_char(dinner_dt,'yyyy-mm-dd'),"+\
            "coalesce(invitee,''),dinner_headcount,"+\
            "(select count(*) from entertainment_entwriteoff where actapplication_id=a.id)||"+\
                "case when cnt_bottles=0 then '' else "+\
                    "'/'||"+\
                    "case when qty_box=0     then '' else qty_box||'箱' end ||"+\
                    "case when qty_bottle=0  then '' else qty_bottle||'瓶' end "+\
                " end,"+\
            "coalesce(contact,''),"+\
            "(select username from company_user where id=user_confirmed_id)||to_char(tm_confirmed,'yyyy-mm-dd HH24:MI'),"+\
            "user_id from entertainment_actapplication a  "+sCompany+sDt+sWriteoff  

        cur.execute(s)
        data['clmns']=[_('申报时间'),_('主题'),_('申请单位'),_('总案流水号'),_('提交人'),_('所属战区'),_('平台商'),_('酒店餐厅'),
            _('详细地址'),_('联系人'),_('联系电话'),_('用餐日期'),_('招待对象'),_('用餐人数'),_('核销(瓶)'),_('确认')]
        for r in cur.fetchall():   
            s="select coalesce((select name from product_product where id=a.product_id),''),a.cnt from "+\
                "(select product_id,count(*) cnt from  entertainment_entwriteoff "+\
                    "where actapplication_id="+str(r[0])+" group by product_id) a"
            cur.execute(s)
            tip=''
            tips=[]
            for rr in cur.fetchall():                
                tips.append('<li><span style="margin-right:10px;">'+rr[0]+":"+str(rr[1])+"瓶</div></li>")            
            if tips:    
                tip=''.join(tips)
            
                #tip="待核销"
                tip="<span style='cursor:pointer;text-decoration:underline;' title='核销记录：'  data-container='#tb td' "+\
                    "data-toggle='popover' data-content='<div>"+tip+"</div>' >"+str(r[15])+"</span>" 
            else:
                tip=str(r[15])
            if r[17]:
                sConfirm=r[17]
            else:
                sConfirm='<input type="checkbox" id="cb-'+str(r[0])+'" /><label for="cb-'+str(r[0])+'">确认</label>'

            #obj/action/(?P<obj_name>\w+)/(?P<id>\d+)/(?P<act>\w+)/$', list_views.obj_action),  
            if (u.has_perm('company.sys_admin') or bConfirm) and r[17]:
                a='<a style="text-decoration:underline;" href="/obj/action/actapplication/'+str(r[0])+'/print/">'+r[1]+'</a>'
            else:
                if u.id==r[18]:
                    a='<a style="text-decoration:underline;" href="/obj/action/actapplication/'+str(r[0])+'/print/">'+r[1]+'</a>'
                else:
                    a=r[1]

            data['data'].append([ a,r[2],r[3],r[5],r[4],r[7],r[8],r[9],r[10],r[16],r[11],r[12],r[13],r[14],tip,sConfirm ])
            
        data['show_add']=show_add
        data['date_from']=dt_fr
        data['date_to']=dt_to    
        return render(request,'entertainment/list_actapplication.html',data)



    #2021-05-25
    elif obj=='giftapplication':
        dt= datetime.datetime.now().strftime('%Y-%m-%d')
        dt_default=(datetime.datetime.now() - datetime.timedelta(days=7)).strftime('%Y-%m-%d')
        dt_fr = request.GET.get('date_fr', dt_default)
        dt_to = request.GET.get('date_to', dt)
        is_writeoff=request.GET.get('is_writeoff','-1')
        bConfirm=False
        if 8 in group_ids or 7 in group_ids or 17 in group_ids:
            bConfirm=True
        sWriteoff=''
        if is_writeoff=='0':
            sWriteoff=' and is_writeoff=false'
        elif is_writeoff=='1':
            sWriteoff=' and is_writeoff=true'
        sDt=" and (dt between '"+dt_fr+"' and  '"+dt_to+"')"
        sCompany=" where user_id="+str(u.id)
        if c.level_id==1:
            if u.has_perm('company.sys_admin'):
                show_add=True
            sCompany=" where company_id="+str(c.id)
        elif 7 in group_ids or 12 in group_ids or 17 in group_ids:
            sCompany=" where (select division_id from company_company where id="+\
                "(select company_id from company_user where id=a.user_id))="+str(c.division_id) 
        s="select id,to_char(tm,'yyyy-mm-dd HH24:MI:SS'),"+\
            "(select last_name from company_user where id=a.user_id),"+\
            "coalesce((select name from company_department where id=department_id),"+\
                "(select name from company_company where id=agent_id),''),"+\
            "entity,receiver,cellphone,"+\
            "(select username from company_user where id=user_confirmed_id)||to_char(tm_confirmed,'yyyy-mm-dd HH24:MI'),"+\
            "user_id,coalesce(schema_no,'') from  entertainment_giftapplication a "+sCompany+sDt+\
            " and (select count(*) from entertainment_entwriteoff where giftapplication_id=a.id)>0"

        cur.execute(s)
        data['clmns']=[_('申报时间'),_('提交人'),_('总案流水号'),_('申报单位'),_('赠予单位名称'),('赠予人姓名'),_('联系电话'),_('核销数量'),_('确认')]
       
        rs=cur.fetchall()
        for r in rs:
            x=''
            p=''
            s="select "+\
                "(select count(*) from entertainment_entwriteoff where giftapplication_id="+str(r[0])+" and package_id=3),"+\
                "(select count(*) from entertainment_entwriteoff where giftapplication_id="+str(r[0])+" and package_id=1)"
            cur.execute(s)
            rr=cur.fetchone()
            if rr[0]:
                x=str(rr[0])+"箱"
            if rr[1]:
                p=str(rr[1])+"瓶"
            if (u.has_perm('company.sys_admin') or bConfirm) and r[7]:
                a='<a style="text-decoration:underline;" href="/obj/action/giftapplication/'+str(r[0])+'/print/">'+str(r[1])+'</a>'
            else:
                if u.id==r[8]:
                    a='<a style="text-decoration:underline;" href="/obj/action/giftapplication/'+str(r[0])+'/print/">'+str(r[1])+'</a>'
                else:
                    a=r[1]
            if r[7]:
                sConfirm=r[7]
            else:
                sConfirm='<input type="checkbox" id="cb-'+str(r[0])+'" /><label for="cb-'+str(r[0])+'">确认</label>'
            data['data'].append([a,r[2],r[9],r[3],r[4],r[5],r[6],x+p,sConfirm ])            
        data['show_add']=show_add
        data['date_from']=dt_fr
        data['date_to']=dt_to    
        return render(request,'entertainment/list_giftapplication.html',data)

    elif obj=='entwriteoff':
        dt= datetime.datetime.now().strftime('%Y-%m-%d')       
        dt_fr = request.GET.get('date_fr', dt)
        dt_to = request.GET.get('date_to', dt)
        cat_id=request.GET.get('category_id','')
        cid=str(c.id)
        sDt=" and (dt between '"+dt_fr+"' and  '"+dt_to+"')"
        sCompany=" where company_id="+cid
        sCat=""
        if cat_id:
            sCat=" and category_id="+cat_id
        if c.level_id>1:            
            sCompany=" where parent_id="+cid+" or agent_id="+cid     

        #2021-04-19
        if only_myself:
            sCompany=" where user_id="+str(u.id)

        s="select w.id,to_char(w.tm,'yyyy-mm-dd HH24:MI:SS'),"+\
            "coalesce((select name from company_department where id=w.department_id),"+\
                "(select name from company_company where id=w.agent_id),''),"+\
            "(select coalesce(last_name,username) from company_user where id=w.user_id),"+\
            "case when e.id>0 then 'ZDYJ'||to_char(e.id,'00000000') else case when a.id>0 then a.subject else g.entity end end,"+\
            "coalesce((select name from entertainment_category where id=w.category_id),''),"+\
            "coalesce((select name from company_salesdivision where id=w.division_id),''),"+\
            "coalesce((select name from product_product where id=w.product_id),''),"+\
            "label,label_code,coalesce(box_code,''),"+\
            "case when w.is_bottlecap then '是' else '否' end "+\
            "from "+\
            "(select * from entertainment_entwriteoff"+sCompany+sDt+sCat+") w "+\
            "left outer join "+\
            "(select id from entertainment_entapplication) e "+\
            "on w.entapplication_id=e.id "+\
            "left outer join "+\
            "(select id,subject from entertainment_actapplication) a "+\
            "on w.actapplication_id=a.id "+\
            "left outer join "+\
            "(select * from entertainment_giftapplication) g "+\
            "on w.giftapplication_id=g.id"
        cur.execute(s)
        data['clmns']=[_('扫码时间'),_('部门/经销商'),_('核销人'),_('主题/用酒单'),_('用酒类型'),_('所属战区'),_('产品名称'),_('标签号码'),_('对应箱标'),]
        for r in cur.fetchall():           
            data['data'].append([ r[1],r[2],r[3],r[4],r[5],r[6],r[7],r[8],r[10] ])                
        
        data['show_add']=show_add
        data['date_from']=dt_fr
        data['date_to']=dt_to
        data['category_id']=cat_id
        return render(request,'entertainment/list_entwriteoff.html',data)
    #2021-04-01
    elif obj=='stack':   
        cur.execute("select code,coalesce(location,''),coalesce(remark,''),"+\
            "coalesce((select name from inventory_storehouse where id=store_id),''),"+\
            "coalesce((select name from product_product where id=product_id),''),"+\
            "(select count(*)from inventory_stackdetails where stack_id=a.id),id "+\
            "from inventory_stack a where company_id="+str(c.id)+" and is_delete=false")
        data['clmns']=[_('垛标编号'),_('所在仓库'),_('位置'),_('产品名称'),_('当前箱数'),_('备注说明')]
        for r in cur.fetchall():  
            a="<a href='/obj/action/stack/"+str(r[6])+"/view/' >"+r[0]+"</a>"
            data['data'].append([a,r[3],r[1],r[4],r[5],r[2] ])   
        return render(request,'obj/list_stack.html',data)

    #2021-05-29
    elif obj=='companyunit':
        data['clmns']=[_("计量单位"),_('代码'),('基准转换率'),_("默认单位"),_("计量类别"),_("计量标的物"),_("备注说明") ]        

        s="select a.id,coalesce(a.remark,''),case when a.is_default then '是' else '否' end,"+\
            "(select name from mes_measure where id=a.measure_id),"+\
            "(select name from inventory_invtype where id=a.invtype_id),"+\
            "u.name,coalesce(u.abbreviation,''),to_char(u.ratio,'990.999') "+\
            "from "+\
            "(select * from mes_companyunit where company_id="+str(u.ancestor_id)+" and is_delete=false"+") a "+\
            "left outer join "+\
            "(select * from mes_unit) u "+\
            "on a.unit_id=u.id"

        cur.execute(s)        
        for r in cur.fetchall():
            a=''
            data['data'].append([ r[5],r[6],r[7],r[2],r[3],r[4],r[1] ])

    elif obj=='materials':
        data['clmns']=["原材料名称","编码","计量类别","备注说明"]   
        s="select id,name,coalesce(code,''),coalesce(remark,''),"+\
            "coalesce((select name from mes_measure where id=measure_id),'') "+\
            "from mes_materials where company_id="+str(u.ancestor_id)+" and is_delete=false"
        cur.execute(s)   
        show_add=u.has_perm('company.sys_admin')
        for r in cur.fetchall():
            if show_add:
                a='<a href="/obj/edit/materials/'+str(r[0])+'/">'+r[1]+'</a>'
            else:
                a=r[1]
            data['data'].append([ a,r[2],r[4],r[3] ])
    elif obj=='semifinishedgoogs':
        data['clmns']=["半成品名称","编码","计量类别","备注说明"]   
        s="select id,name,coalesce(code,''),coalesce(remark,''),"+\
            "coalesce((select name from mes_measure where id=measure_id),'') "+\
            "from mes_semifinishedgoods where company_id="+str(u.ancestor_id)+" and is_delete=false"
        cur.execute(s)   
        show_add=u.has_perm('company.sys_admin')
        for r in cur.fetchall():
            if show_add:                
                a='<a href="/obj/edit/semifinishedgoogs/'+str(r[0])+'/">'+r[1]+'</a>'
            else:
                a=r[1]
            data['data'].append([ a,r[2],r[4],r[3] ])   
    elif obj=='packaging':
        data['clmns']=["包装物料名称","编码","计量类别","备注说明"]   
        s="select id,name,coalesce(code,''),coalesce(remark,''),"+\
            "coalesce((select name from mes_measure where id=measure_id),'') "+\
            "from mes_packaging where company_id="+str(u.ancestor_id)+" and is_delete=false"
        cur.execute(s)   
        show_add=u.has_perm('company.sys_admin')
        for r in cur.fetchall():
            if show_add:
                a='<a href="/obj/edit/packaging/'+str(r[0])+'/">'+r[1]+'</a>'
            else:
                a=r[1]
            data['data'].append([ a,r[2],r[4],r[3] ])
    elif obj=='bom':
        data['clmns']=["物料单编码","产成品名称","原材料配比","半成品配比","包装物料耗用"]   
        s="select id,name,coalesce(code,''), "+\
            "(select name from product_product where id=product_id),"+\
            "array_to_string(array(select (select name from mes_materials where id=materials_id)||' '||qty||(select name from mes_unit where id=m.unit_id) "+\
                "from mes_bommaterials m where bom_id=b.id),'<br/>'),"+\
            "array_to_string(array(select (select name from mes_semifinishedgoods where id=semifinishedgoods_id)||' '||qty||(select name from mes_unit where id=s.unit_id) "+\
                "from mes_bomsemifinishedgoods s where bom_id=b.id),'<br/>'),"+\
            "array_to_string(array(select (select name from mes_packaging where id=packaging_id)||' '||qty||(select name from mes_unit where id=p.unit_id) "+\
                "from mes_bompackaging p where bom_id=b.id),'<br/>') "+\
            "from mes_bom b where company_id="+str(u.ancestor_id)+" and is_delete=false"      
        cur.execute(s)   
        show_add=u.has_perm('company.sys_admin')
        for r in cur.fetchall():
            if show_add:
                a='<a href="/obj/edit/bom/'+str(r[0])+'/">'+r[2]+'</a>'
            else:
                a=r[1]
            data['data'].append([ a,r[3],r[4],r[5],r[6] ])
    elif obj=='storehouse':
        data['murl']='/mes/storehouse/'
        data['clmns']=["仓库名称","仓库编码","仓库位置","备注说明"]   
        s="select id,name,coalesce(code,''),coalesce(location,''),coalesce(remark,'') "+\
            "from mes_storehouse where company_id="+str(u.ancestor_id)+" and is_delete=false"
        cur.execute(s)   
        show_add=u.has_perm('company.sys_admin')
        for r in cur.fetchall():
            if show_add:
                a='<a href="/mes/storehouse/'+str(r[0])+'/">'+r[1]+'</a>'
            else:
                a=r[1]
            data['data'].append([ a,r[2],r[3],r[4] ])
    elif obj=='kangnian_reserve':
        data['dt']=True
        data['add']=False

        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      
        
        sWhere = ''
        if str(c.id) == '31917':
            sWhere=f" where tm::date >= '{dt_fr}' and tm::date <= '{dt_to}' "
        else:
            sWhere=f" where tm::date >= '{dt_fr}' and tm::date <= '{dt_to}' and agent_id={c.id}"

        data['murl'] = '/kangnian/reserve_list/'
        data['clmns'] = ['标签号码','终端店','消费者','姓名','电话',"产品","数量","上店时间",'核销状态','时间' ]
        sql = "select labelcode,(select name from company_company where id=agent_id),(select wx_nickname from sales_consumeraccount where id=consumer_id),name,phone,(select name from product_product where id = product_id),product_num,to_char(want_tm,'yyyy-mm-dd HH24:MI'),is_writeoffed,to_char(tm,'yyyy-mm-dd HH24:MI:SS') from logistics.sales_kangnian_reservelabel " + sWhere
        cur.execute(sql)
        writeoff_status = ""

        for r in cur.fetchall():
            if r[8]:
                writeoff_status = "已被核销"
            else:
                writeoff_status = "未核销"
            data['data'].append([r[0],r[1],r[2],r[3],r[4],r[5],r[6],r[7],writeoff_status,r[9]])
    elif obj=='kangnian_writeoff':
        data['dt']=True
        data['add']=False

        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      

        if str(c.id) == '31917':
            sWhere=f" where tm::date >= '{dt_fr}' and tm::date <= '{dt_to}' "
        else:
            sWhere=f" where tm::date >= '{dt_fr}' and tm::date <= '{dt_to}' and agent_id={c.id}"

        data['murl'] = '/kangnian/writeoff_list/'
        data['clmns'] = ['标签号码','产品', '时间', '终端店', '核销帐号', '预约人', '操作']
        sql = "select labelcode,(select name from product_product where id=product_id),to_char(tm,'yyyy-mm-dd HH24:MI:SS'),(select name from company_company where id=agent_id),(select last_name from company_user where id=user_id),coalesce((select name || '' || phone from sales_kangnian_reservelabel where id=reserve_label_id),''), id from logistics.sales_kangnian_writeofflabel "+sWhere
        cur.execute(sql)

        for r in cur.fetchall(): 
            person = ''
            if u.id == 11444:
                del_btn = '<button class="btn btn-danger btn-xs" onclick='+'"deleteObj(' +"`kangnian_writeofflabel`"+','+ str(r[6]) + ")\"'>删除</button>"
            else:
                del_btn = ''
            if r[5]:
                person = r[5]
            data['data'].append([r[0],r[1],r[2],r[3],r[4],person,del_btn])
            
    elif obj=='kangnian_inventorymanipulation': 
        data['clmns'] = ['经销商名称','商品名称','库存数量','操作笔数','入库数量','出库扣减数量','核销扣减数量','退库数量']
        sql = """SELECT
                ( SELECT NAME FROM company_company WHERE ID = agent_id ),
                ( SELECT NAME FROM product_product WHERE ID = product_id ),
                COALESCE ( SUM ( amount ), 0 ),
                COUNT ( 1 ),
                (
                SELECT COALESCE
                    ( SUM ( amount ), 0 ) 
                FROM
                    sales_kangnian_inventorymanipulation 
                WHERE
                    amount > 0 
                    AND agent_id = A.agent_id 
                    AND product_id = A.product_id 
                    AND vtype_id = 1   
                ),
                (
                SELECT COALESCE(SUM
                    ( amount ), 0 )
                FROM
                    sales_kangnian_inventorymanipulation 
                WHERE
                    amount < 0 
                    AND agent_id = A.agent_id 
                    AND product_id = A.product_id 
                    AND reason != '核销扣除'   
                    AND vtype_id != 4   
                ),
                ( SELECT COALESCE(SUM ( amount ),0) FROM sales_kangnian_inventorymanipulation WHERE agent_id = A.agent_id AND product_id = A.product_id AND reason = '核销扣除' ),
                
                (
                SELECT COALESCE(SUM
                    ( amount ), 0 )
                FROM
                    sales_kangnian_inventorymanipulation 
                WHERE 
                    agent_id = A.agent_id 
                    AND product_id = A.product_id 
                    AND vtype_id = 4   
                )
            FROM
                sales_kangnian_inventorymanipulation A 
            GROUP BY
                agent_id,
                product_id"""

        cur.execute(sql)

        for r in cur.fetchall():
            #print(r)
            data['data'].append([r[0],r[1],r[2],r[3],r[4],r[5],r[6],r[7]])
        #show_add=True
        can_edit=True
    elif obj=='kangnian_inventorymanipulation_detail': 
        data['dt']=True 
        data['murl'] = '/obj/edit/kangnian_inventorymanipulation/'

        dt_default = datetime.datetime.now().strftime('%Y-%m-%d')
        dt_fr_default = (datetime.datetime.now() - datetime.timedelta(days=3)).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      

        sWhere=f" where tm::date >= '{dt_fr}' and tm::date <= '{dt_to}' "

        #data['murl'] = '/obj/edit/kangnian_inventorymanipulation_detail/'
        data['clmns'] = ['门店名称','原因','产品名称','数量','时间','操作人']
        sql = "select id,(select name from company_company where id=agent_id),reason,(select name from product_product where id = product_id),amount,to_char(tm,'yyyy-mm-dd HH24:MI:SS'),(select COALESCE(last_name,'') from company_user where id=user_id) from logistics.sales_kangnian_inventorymanipulation " +sWhere

        cur.execute(sql)
        
        for r in cur.fetchall():
            edit_link = f'<a href="/obj/edit/kangnian_inventorymanipulation/{r[0]}/">{r[1]}</a>'
            data['data'].append([edit_link,r[2],r[3],r[4],r[5],r[6]])
        show_add=True
        can_edit=True
    elif obj=='kangnian_activehistory':
        data['dt']=False
        data['add']=False

        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      

        sWhere=f" where tm::date >= '{dt_fr}' and tm::date <= '{dt_to}' "

        data['murl'] = '/kangnian/activehistory/'
        data['clmns'] = ['产品名称','激活数量','已核销数量','激活时间']
        #sql = "select (select name from logistics.product_product where id=product_id), to_char(tm, 'YYYY-MM-DD') as d, count(1),sum(case when is_writeoffed then 1 else 0 end ) writeoffed from logistics.sales_kangnian_activelabel "+sWhere+"group by d, product_id"
        sql = "SELECT (select name from logistics.product_product where id=product_id),sum(num),(select count(*) from sales_kangnian_writeofflabel where product_id = sales_kangnian_activelabelsummary.product_id),to_char(dt, 'YYYY-MM-DD') as dt FROM sales_kangnian_activelabelsummary GROUP BY product_id,dt"
        
        
        cur.execute(sql)
        
        for r in cur.fetchall():
            data['data'].append([r[0], r[1], r[2], r[3]])
        show_add=False
        can_edit=True

    elif obj == 'kangnian_storehouse_management':
        data['murl'] = '/kangnian/kangnian_storehouse_management/'
        data['clmns'] =  ['仓库名称','商品名称','库存数量','操作笔数','入仓数量','出仓数量']
        # KangNian_StorehouseInventoryManipulation
        sql="""SELECT
                ( SELECT NAME FROM inventory_storehouse WHERE ID = storehouse_id ),
                ( SELECT NAME FROM product_product WHERE ID = product_id ),
                SUM ( amount ),
                COUNT ( 1 ),
                (
                SELECT COALESCE
                    ( SUM ( amount ), 0 ) 
                FROM
                    sales_kangnian_storehouseinventorymanipulation 
                WHERE
                    amount > 0 
                    AND storehouse_id = A.storehouse_id 
                    AND product_id = A.product_id 
                ),
                (
                SELECT COALESCE
                    ( SUM ( amount ), 0 ) 
                FROM
                    sales_kangnian_storehouseinventorymanipulation 
                WHERE
                    amount < 0 
                    AND storehouse_id = A.storehouse_id 
                    AND product_id = A.product_id 
                ) 
            FROM
                sales_kangnian_storehouseinventorymanipulation A 
            GROUP BY
                storehouse_id,
                product_id"""
        cur.execute(sql)
        
        for r in cur.fetchall():
            data['data'].append([r[0], r[1], str(r[2]), r[3],r[4],r[5]])
        show_add = False
        can_edit = True

    elif obj == 'kangnian_storehouse_management_detail':
        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      

        sWhere=f" where tm::date >= '{dt_fr}' and tm::date <= '{dt_to}' "
        data['murl'] = '/obj/edit/kangnian_storehouse_management_detail/'
        data['clmns'] =  ['仓库名称','产品名称','数量','操作说明','操作时间','操作人', '操作']
        sql = "select (select name from logistics.inventory_storehouse where id=sales_kangnian_storehouseinventorymanipulation.storehouse_id), (select name from logistics.product_product where id=product_id), amount, coalesce(reason, ''), to_char(tm, 'YYYY-MM-DD HH24:mm:ss'),(select last_name from company_user where id=user_id), id from logistics.sales_kangnian_storehouseinventorymanipulation " + sWhere
        cur.execute(sql)
        
        for r in cur.fetchall():
            if u.id == 11444:
                del_btn = '<button class="btn btn-danger btn-xs" onclick='+'"deleteObj(' +"`kangnian_storehouse_management_detail`"+','+ str(r[6]) + ")\"'>删除</button>"
            else:
                del_btn = ''
            data['data'].append([r[0], r[1], r[2], r[3], r[4],r[5], del_btn])
        show_add = True
        can_edit = True
    
    elif obj=='qz_brandrecommender':
        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      

        sWhere=f" where tm::date >= '{dt_fr}' and tm::date <= '{dt_to}' "

        data['order'] = ['0', 'desc']
        data['murl'] = '/sales/qz_brandrecommender/'
        data['clmns'] = ['明细ID','品牌推荐官','关联经销商', '关联号码','返利时间','金额','说明','兑现状态']
        sql = "select (select name from logistics.company_brandrecommender where id=brandrecommender_id),to_char(tm, 'YYYY-MM-DD HH24:mm:ss'),amount,reason,is_redeemed,coalesce(labelcode,''),(select wxopenid from logistics.company_brandrecommender where id=brandrecommender_id),id,(select (select name from logistics.company_company where id=agent_id) from logistics.company_brandrecommender where id=brandrecommender_id) from logistics.sales_activityrebate_brandrecommenderbonus " + sWhere
        cur.execute(sql)
        
        for r in cur.fetchall():
            is_redeemed = r[4]
            str_amount = '￥ ' + str(r[2])
            is_set_wxopenid = False
            if r[4]:
                is_redeemed = '<image src="/static/images/check.png" style="width:18px;height:18px;"/> 已兑现'
            else:
                is_redeemed = "尚未兑现"
            # if r[6]:
            #     is_set_wxopenid = '<span style="display:inline-block;background:green;width:15px;height:15px;border-radius:50%;"></span> 当前已关联'
            # else:
            #     is_set_wxopenid = '<span style="display:inline-block;background:red;width:15px;height:15px;border-radius:50%;"></span> 尚未关联'
            data['data'].append([r[7],r[0],r[8], r[5], r[1], str_amount, r[3], is_redeemed])
        show_add=False
        can_edit=True
    elif obj=='qz_anniversary':
        data['murl'] = '/sales/qz_anniversary/'
        data['clmns'] = ['返利说明','返利时间','奖品','收货人','收货地址','收货人电话','发货状态']
        sql = "select reason, to_char(tm, 'YYYY-MM-DD HH24:mm:ss'), aw_desc, name, address, phone, is_redeemed, id from sales_activityrebate_anniversary"
        cur.execute(sql)
        
        for r in cur.fetchall():
            is_redeemed = r[6]
            if r[6]:
                is_redeemed_text = "已发货"
                dot="<span style='display: inline-block;background:green;width:13px;height:13px;margin-right: 0.5rem;border-radius:50%;'></span>"
            else:
                is_redeemed_text = "尚未发货"
                dot="<span style='display: inline-block;background:red;width:13px;height:13px;margin-right: 0.5rem;border-radius:50%;'></span>"
            js=r"$.ajax({url:'/sales/qz_toggle_anniversary/?id=" + str(r[7]) + "'});$.refreshContent();"
            btn = r'<a style="text-decoration:underline;" href="/obj/list/qz_anniversary/" onclick="'+js+'">'+dot+is_redeemed_text+'</a>'


            data['data'].append([r[0], r[1], r[2], r[3], r[4], r[5], btn])
        show_add=False
        can_edit=False
    elif obj=='qz_brandrecommender_redpack_record':
        data['order'] = ['4', 'desc']
        data['murl'] = '/sales/qz_anniversary/'
        data['clmns'] = ['品牌推荐官','关联明细ID','记录添加时间','金额','最后一次发送时间','是否成功通信','是否成功发送','错误信息','操作']
        sql = "select bonus_id,to_char(tm, 'YYYY-MM-DD HH24:mm:ss'),to_char(last_send_tm, 'YYYY-MM-DD HH24:mm:ss'),coalesce(return_code,''),coalesce(result_code,''),coalesce(err_code_des,''),id from logistics.sales_activityrebate_brandrecommenderredpackrecord"
        cur.execute(sql)
        
        for r in cur.fetchall():
            ar_brb = ActivityRebate_BrandRecommenderBonus.objects.get(id=r[0])
            br = BrandRecommender.objects.get(id=ar_brb.brandrecommender_id)
            is_success = ''
            retry_btn = ''
            if r[4] == 'SUCCESS':
                is_success = '<span style="display:inline-block;background:green;width:15px;height:15px;border-radius:50%;"></span> ' + r[4]
            else:
                js=r"$.ajax({url:'/sales/qz_brandrecommender_redpack_retry/?id=" + str(r[6]) + "'});$.refreshContent();"
                retry_btn = r'<a style="text-decoration:underline;" href="/obj/list/qz_brandrecommender_redpack_record/" onclick="'+js+'">重试</a>'
                is_success = '<span style="display:inline-block;background:red;width:15px;height:15px;border-radius:50%;"></span> ' + r[4]        
            
            data['data'].append([br.name,r[0], r[1], str(ar_brb.amount), r[2],r[3],is_success,r[5],retry_btn])
        show_add=False
        can_edit=False        
    elif obj=='qz_brandrecommender_rebate_summary':
        data['murl'] = '/sales/qz_brandrecommender_rebate_summary/'
        data['clmns'] = ['品牌推荐官', '汇总金额', '原因', '关联经销商']
        sql = "select b.name, sum(a.amount), coalesce(a.reason, ''), (select name from logistics.company_company where id=b.agent_id) as agent_name from logistics.sales_activityrebate_brandrecommenderbonus a left join logistics.company_brandrecommender b on a.brandrecommender_id=b.id where is_redeemed=true group by reason, b.name, agent_id"
        cur.execute(sql)
        
        for r in cur.fetchall():            
            data['data'].append([r[0],str(r[1]), r[2], r[3]])
        show_add=False
        can_edit=False 
    elif obj=='qz_rebatenumber':
        data['dt']=True
        data['add']=False

        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      

        sWhere=f" where tm::date >= '{dt_fr}' and tm::date <= '{dt_to}' "

        data['order'] = ['1', 'desc']
        data['murl'] = '/sales/qz_rebatenumber/'
        data['clmns'] = ['消费者','领取次数','产品名称','共计发放金额','操作方式']
        sql="select (select wx_nickname from logistics.sales_consumeraccount where id=consumer_id),(select wx_headimgurl from logistics.sales_consumeraccount where id=consumer_id), count(1),(select name from logistics.product_product where id=product_id), sum(amount),operation from logistics.sales_activityrebate_bonusrecord "+sWhere+" group by consumer_id, product_id,operation "
        cur.execute(sql)
        
        for r in cur.fetchall():
            str_consumer = f"<img src='{r[1]}' style='width:18px;height:18px;border-radius:7px;' /> {r[0]}"
            str_operation = ''
            if r[5] == 1:
                str_operation = '直接扫码返利'
            elif r[5] == 2:
                str_operation = '二级返利'
            elif r[5] == 3:
                str_operation = '三级返利'
            elif r[5] == 10:
                str_operation = '分享红包'
            elif r[5] == 100:
                str_operation = '逢整返利'
            data['data'].append([str_consumer, r[2], str(r[3]), str(r[4]), str(str_operation)])
        show_add=False
        can_edit=False   
    elif obj=='qz_consumerwinner':
        data['dt']=True
        data['add']=False

        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      

        sWhere=f" where tm::date >= '{dt_fr}' and tm::date <= '{dt_to}' "

        data['order'] = ['3', 'desc']
        data['murl'] = '/sales/qz_rebatenumber/'
        data['clmns'] = ['奖项','奖品','消费者','时间','金额']
        sql="select " +\
            "coalesce(b.name,'') as activity_award_name," +\
            "coalesce((select name from logistics.sales_award where id=b.award_id),'') as award_name," +\
            "coalesce(c.wx_nickname,'') as wx_nickname," +\
            "coalesce(c.wx_headimgurl,'') as headimgurl," +\
            "to_char(a.tm,'yyyy-mm-dd HH24:MI:SS') as tm, " +\
            "(select amount from logistics.sales_activityrebate_bonusrecord where consumerwinner_id=a.id) " +\
            "from" +\
            "(select * from logistics.sales_consumerwinner where company_id=20445) a " +\
            "left outer join" +\
            "(select id,activity_id,name,award_id from logistics.sales_activityawards) b " +\
            "on a.award_id=b.id " +\
            "left outer join " +\
            "(select id,wx_nickname,wx_headimgurl from logistics.sales_consumeraccount) c " +\
            "on a.consumer_id=c.id " + sWhere + " order by tm desc"
        cur.execute(sql)
        
        for r in cur.fetchall():
            str_consumer = f"<img src='{r[3]}' style='width:18px;height:18px;border-radius:7px;' /> {r[2]}"
            
            amount_info = "￥ " + str(r[5])
            if not r[5]:
                amount_info = "---"

            data['data'].append([r[0], r[1], str_consumer, r[4], amount_info])
        show_add=False
        can_edit=False  
    elif obj == 'qz_redpacksendfail':
        data['add']=False


        data['murl'] = '/sales/qz_redpacksendfail/'
        data['clmns'] = ['错误代码','发放金额','错误详情','单号','收款人微信昵称','操作']

        sql = "select remark, (select amount from logistics.sales_activityrebate_bonusrecord where id=sales_activityrebate_redpacksendrecord.bonus_record_id), coalesce(err_code_des,''), coalesce(bill_no,''), (select wx_nickname from logistics.sales_consumeraccount where id=consumer_id), id from logistics.sales_activityrebate_redpacksendrecord where activity_id=29 and result_code='FAIL'"
        cur.execute(sql)
        for r in cur.fetchall():
            retry_btn = '-'
            if r[3]:
                js=r"$.ajax({url:'/sales/qz_regular_redpack_retry/?id=" + str(r[5]) + "'});$.refreshContent();"
                retry_btn = r'<a style="text-decoration:underline;" href="/obj/list/qz_redpacksendfail/" onclick="'+js+'">重试</a>'

            data['data'].append([r[0], str(r[1]), r[2], str(r[3]), r[4], retry_btn])
        
        show_add=False
        can_edit=False 
    elif obj == 'qz_redpackstatus':
        data['add']=False

        data['murl'] = '/sales/qz_redpackstatus/'
        data['clmns'] = ['活动名称','奖项等级','奖品名称','已兑数量','上限','对应金额', '获奖概率', '已派奖比例']

        sql = "select (select name from logistics.sales_activities where id=a.activity_id) activity_name, name, (select name from logistics.sales_award where id = award_id) award_name, (select count(1) from logistics.sales_consumerwinner where award_id=a.id), qty_upper upper_limit, (select amount from logistics.sales_award where id = award_id) amount, round(rate, 2) rate from logistics.sales_activityawards a where activity_id in (57,58,59,60,61,63,64)"
        cur.execute(sql)


        for r in cur.fetchall():
            already_redeemed = r[3]
            top_limit = r[4]
            already_redeemed_str = str(r[3])
            if already_redeemed >= top_limit:
                already_redeemed_str = '<span style="color:red;">' + already_redeemed_str + '</span>'
            redeemed_rate = round(r[3] / r[4]  * 100) / 100
            redeemed_rate_html = '<div style="display:flex;justify-content:flex-start;align-items:center;"><div style="width:52px;height:16px;border:1px solid #444;"><div style="background:#888;height:14px;width:'+str(redeemed_rate*50)+'px"></div></div><div style="margin-left:0.5rem;">'+str(round(redeemed_rate*100))+'%</div></div>'
            data['data'].append([r[0], str(r[1]), r[2], already_redeemed_str, str(r[4]), '￥ '+str(r[5]), str(r[6])+' %', redeemed_rate_html])
        
        show_add=False
        can_edit=False 
    elif obj=='channel':
        data['murl']='/obj/edit/channel/'
        data['clmns']=["名称","备注"]   
        s="select id,name,coalesce(remark,'')from company_channel where ancestor_id="+str(u.ancestor_id)+""
        cur.execute(s)   
        show_add=u.has_perm('company.sys_admin')
        for r in cur.fetchall():
            if show_add:
                a='<a href="/obj/edit/channel/'+str(r[0])+'/">'+r[1]+'</a>'
            else:
                a=r[1]
            data['data'].append([ a,r[2]])
    elif obj=='channel_level':
        data['murl']='/obj/edit/channel_level/'
        data['clmns']=["名称","系列","等级","备注"]   
        s="select id,name,level,coalesce(remark,''),coalesce((select name from product_series where id = series_id),'') from company_channel_level where ancestor_id="+str(u.ancestor_id)+""
        cur.execute(s)   
        show_add=u.has_perm('company.sys_admin')
        for r in cur.fetchall():
            if show_add:
                a='<a href="/obj/edit/channel_level/'+str(r[0])+'/">'+r[1]+'</a>'
            else:
                a=r[1]
            level = ''
            if r[2]:
                level = r[2]
            data['data'].append([ a,r[4],level,r[3]])
    elif obj=='uploadfiles':
        data['murl']='/obj/edit/uploadfiles/'
        data['clmns']=["文件类名","文件类型","文件地址","备注"]   
        s=f"""select id,(select name from company_filecategory where id = category_id),media_type,url,coalesce(remark,'') from company_uploadfiles where company_id = {str(u.ancestor_id)}"""
        cur.execute(s)   
        show_add=u.has_perm('company.sys_admin')
        for r in cur.fetchall():
            if show_add:
                a='<a href="/obj/edit/uploadfiles/'+str(r[0])+'/">'+r[1]+'</a>'
            else:
                a=r[1]
            media_type = '图片'
            if r[2] == 2:
                media_type = '文件'
              
            data['data'].append([ a,media_type,r[3],r[4]])
    elif obj=='sales_couponscan':
        # 2022-07-08 终端活动扫码
        data['dt']=True
        dt = datetime.datetime.now().strftime('%Y-%m-%d')
        dt_fr = request.GET.get('date_from', dt)
        dt_to = request.GET.get('date_to', dt)   
        dt_max = max(dt_fr, dt_to)
        dt_min = min(dt_fr, dt_to)
        dt_fr = dt_min
        dt_to = dt_max   
        data["date_from"] = dt_fr
        data["date_to"] = dt_to 
        sWhere=" where (dt between '"+dt_fr+"' and '"+dt_to+"')"
        c=u.company
        if c.level_id==1:
            sWhere=sWhere+" and ancestor_id="+str(c.id)
        else:
            sWhere=sWhere+" and (company_id="+str(c.id)+" or (select parent_id from company_company where id=company_id)="+str(c.id)+")"           

        data['clmns']=["经销商名称","扫码时间","标签号码","活动名称","产品名称","奖项","数值"]   
        s="SELECT (select name from company_company where id=company_id),"+\
            "to_char(tm,'yyyy-mm-dd HH24:Mi'),label_code,"+\
            "coalesce((select name from sales_activities where id=activity_id),''),"+\
            "coalesce((select name from product_product where id=product_id),''),"+\
            "coalesce((select name from sales_award where id=award_id),''),"+\
            "to_char(val,'99990.00') from sales_couponscan "+sWhere       
        cur.execute(s)   
        can_edit=False
        data['can_edit']=False        
        for r in cur.fetchall():              
            data['data'].append([ r[0],r[1],r[2],r[3],r[4],r[5],r[6] ])
            
    data['show_add']=show_add
    data['can_edit']=can_edit      
    return render(request, 'obj/list_obj.html',data)
      

def get_product_props(company_id, product_id):
    props = []
    cur = connection.cursor()
    s = "select a.categoryprop_id," + \
        "(select desc_cn from company_categoryprop where id=a.categoryprop_id),"+ \
        "(select unit from company_categoryprop where id=a.categoryprop_id) " + \
        "from " \
        "(SELECT categoryprop_id FROM product_productprop where company_id=" + \
            str(company_id) + " group by categoryprop_id" + \
        ") a"
    cur.execute(s)
    for r in cur.fetchall():
        s = "SELECT id, value, categoryprop_id, company_id FROM product_productprop where "+\
            "company_id="+str(company_id)+" and categoryprop_id=" + str(r[0]) + " order by right('0000000000'||value,11)"
        cur.execute(s)
        sel = []
        for r1 in cur.fetchall():
            sel.append([str(r1[0]), r1[1]])
        rec_id = ''
        pro_id = ''
        rmk = ''
        if product_id:
            s = "SELECT id, remark,prd_property_id  FROM product_prdprops where product_id=" + product_id + " and " + \
                "(SELECT categoryprop_id  FROM product_productprop where id=prd_property_id)="+str(r[0])
            cur.execute(s)
            r2 = cur.fetchone()
            if r2:
                rec_id = str(r2[0])
                pro_id = str(r2[2])
                rmk = r2[1]
        unit = ''
        if r[2]:
            unit = r[2]
        props.append({
            'label': r[1],
            'ops': sel,
            'rec_id': rec_id,
            'pro_id': pro_id,
            'remark': rmk,
            'unit': unit
        })
    return props


    
def autocomplete(company,what,term,query_all='0',lmt=20,vtype_id='2',product_id=0):   
    data=[]   
    q=term   
    cur=connection.cursor()
    c=company   
    aid=str(c.ancestor_id)
    cid=str(c.id)


   

    qry_all=query_all

    s=''
    lmt=' limit '+str(lmt)
    if what=='agent':        
        if c.level_id==1:
            qry_all='1'
        if qry_all=='0':
            #2020-10-10
            if c.is_expense_account:
                #战区
                if c.level_id==2:
                    s="select id,name from company_company where division_id="+str(c.division_id)+\
                        " and is_delete=false and is_expense_account=false and name like '%" + q + "%' order by name"
                #分区
                elif c.level_id==4:
                    s="select id,name from company_company where sub_division_id="+str(c.sub_division_id)+\
                        " and is_delete=false and is_expense_account=false and name like '%" + q + "%' order by name"
            else:
                #2021-04-12 长宇酒业 终端店没有父级
                if aid in ['19651',]:
                    s="select id,name from company_company where (ancestor_id="+aid+\
                        " or id in (11013,11014)) and is_delete=false and name like '%"+q+"%' order by name"
                else:
                    s="select id,name from company_company where (parent_id="+cid+\
                        " or id in (11013,11014)) and is_delete=false and name like '%"+q+"%' order by name"
        #2020-11-05 17842;"浙江举一反三品牌管理有限公司"
        elif c.id in [17842]:
            s = "select id,name from company_company where (parent_id=" + cid + \
                " or id in (11013,11014)) and is_delete=false and name like '%" + q + "%' order by name" 
        else:
            #2020-10-10            
            s = "select id,name from company_company where (ancestor_id=" + cid + \
                " or id in (11013,11014)) and is_delete=false and name like '%" + q + "%' order by name"   
    #2021-08-27
    elif what=='company':
        s = "select id,name from company_company where level_id=1 and name like '%" + q + "%' order by name"   
    elif what=='product':
        #2020-10-10        
        sWhere=" where company_id="+cid+" and is_delete=false and name like '%"+q+"%' order by name"   
        #2020-11-05 17842;"浙江举一反三品牌管理有限公司"
        if c.is_expense_account or c.id in [17842]:
            sWhere=" where company_id="+str(c.ancestor_id)+" and is_delete=false and name like '%"+q+"%' order by name"
        s="select id,name from product_product"+sWhere   
    elif what=='store':
        s="select id,name from inventory_storehouse where company_id="+cid+\
            " and is_delete=false and name like '%"+q+"%' order by name"   
    elif what=='series':
        s="select id,name from product_series where company_id="+cid+\
            " and is_delete=false and name like '%"+q+"%' order by name"
    elif what=='award':
        s="select id,name from sales_award where company_id="+cid+\
            " and name like '%"+q+"%' order by name"
    elif what=='level':
        s="select id,name from company_level where "+\
            " name like '%"+q+"%' order by name"
    #2020-09-09
    elif what=='saldiv':
        s="select id,name from company_salesdivision where company_id="+cid+\
            " and name like '%"+q+"%' order by name"
    elif what=='subdiv':
        s="select id,name from company_subsaldivision where company_id="+cid+\
            " and name like '%"+q+"%' order by name"
    #2020-09-25
    elif what=='salesman':
        s="select id,username from company_user a where ancestor_id="+cid+\
            " and 8 in (select group_id from company_user_groups where user_id=a.id) "+\
            "and (username like '%"+q+"%') order by username"  
    elif what=='salesman_lastname':
        s="select id,last_name from company_user a where ancestor_id="+cid+\
            " and 8 in (select group_id from company_user_groups where user_id=a.id) "+\
            "and last_name like '%"+q+"%' order by last_name"    
    elif what=='material':
        #2020-10-10
        sWhere=" where company_id="+cid+" and name like '%"+q+"%' order by name"
        if c.is_expense_account:
            sWhere=" where company_id="+str(c.ancestor_id)+" and name like '%"+q+"%' order by name"        
        s="select id,name from promotion_material a "+sWhere
    elif what=='scheme':
        s="select id,name from promotion_giftsscheme where company_id="+cid+\
            " and means=2 and name like '%"+q+"%' order by name"         
    elif what =='brands':
        s="select id,name from company_brands where name like '%"+q+"%' and company_id = "+cid+" order by name"   

    #2020-09-16
    elif what=='parent':        
        if qry_all=='0':
            s="select id,name from company_company where ancestor_id="+cid+\
                " and name like '%"+q+"%' order by name"   
            sLv=''
            if c.level_id>1:
                sLv=" and level_id="+str(c.level_id)
            s="select c.id,c.name,l.id,l.name,"+\
                "(select name from company_level where id=c.level_id) "+\
                "from "+\
                "(select id,name,level_id,(select sn+1 from company_level where id=level_id) sn "+\
                    "from company_company where ancestor_id="+aid+sLv+" and name like '%"+q+"%' order by name"+\
                ") c "+\
                "left outer join "+\
                "(select id,name,sn from company_level) l "+\
                "on c.sn=l.sn"
    #2020-12-10
    elif what=='user':       
        if qry_all=='0':
            s="select id,username,last_name,"+\
                "array_to_string(array(select name from auth_group where id in (select group_id from company_user_groups where user_id=a.id)),',') "+\
                "from company_user a where ancestor_id="+cid+" "+\
                "and username like '%"+q+"%' order by username"  
   
    elif what=='user_last_name':       
        if qry_all=='0':
            s="select id,last_name,username,"+\
                "array_to_string(array(select name from auth_group where id in (select group_id from company_user_groups where user_id=a.id)),',') "+\
                "from company_user a where ancestor_id="+cid+" "+\
                "and last_name like '%"+q+"%' order by last_name" 

    #2021-01-20
    elif what=='batch':              
        if vtype_id=='2':
            s="select a.id,a.code||'  '||coalesce(p.name,'')||'  '||coalesce(c.name) ||'  '||  "+\
                "to_char((select count(*) from inventory_stkdetails where batch_id=a.id),'9999999') "+\
                "from "+\
                "(select id,code,product_id,company_to_id from inventory_batch where company_id="+cid+\
                    " and vtype_id="+vtype_id+\
                    " and code like '%"+q+"%' order by code) a "+\
                "left outer join "+\
                "(select id,name from company_company) c "+\
                "on a.company_to_id=c.id "+\
                "left outer join "+\
                "(select id,name from product_product) p "+\
                "on a.product_id=p.id"
        #2022-04-13 生产批次查询
        elif vtype_id=='1':
            s="select coalesce(a.batch_no,a.code),coalesce(a.batch_no,a.code)||'  '||coalesce(p.name,'') "+\
                "from "+\
                "(select id,code,batch_no,product_id,company_to_id from inventory_batch where company_id="+cid+\
                    " and vtype_id="+vtype_id+\
                    " and (code like '%"+q+"%' or batch_no like '%"+q+"%') order by code"+\
                ") a "+\
                "left outer join "+\
                "(select id,name from product_product) p "+\
                "on a.product_id=p.id"      
            #入库时单号 code 为生产批次
            sProduct=''
            if product_id!='0':
                sProduct=" and product_id="+product_id
            s="select code,code from inventory_batch where company_id="+cid+\
                " and vtype_id="+vtype_id+sProduct+" "+\
                " and code like '%"+q+"%' order by code"        

    #2021-03-07
    elif what=='department':
        s="select id,name from company_department a where company_id="+cid+\
            " and name like '%"+q+"%' order by name"  
    elif what=='staff':
        s="select a.id,a.name||'   ['|| case when b.name is null then '' else b.name||' ' end "+\
                "||case when c.name is null then '' else c.name end ||']' "+\
            "from (select id,name,department_id,position_id from company_staff where company_id="+cid+\
                " and name like '%"+q+"%') a "+\
            "left outer join "+\
            "(select id,name from company_department where company_id="+cid+") b "+\
            "on a.department_id=b.id "+\
            "left outer join "+\
            "(select id,name from company_position where company_id="+cid+") c "+\
            "on a.position_id=c.id "+\
            "order by a.name"
    #2021-05-31
    elif what=='materials':
        s="select id,name from mes_materials a where company_id="+cid+\
            " and name like '%"+q+"%' order by name"  
    elif what=='semifinishedgoods':
        s="select id,name from mes_semifinishedgoods a where company_id="+cid+\
            " and name like '%"+q+"%' order by name"
    elif what=='packaging':
        s="select id,name from mes_packaging a where company_id="+cid+\
            " and name like '%"+q+"%' order by name"    
    elif what=='mes_order':
        s="select id,code from mes_order a where company_id="+cid+\
            " and code like '%"+q+"%' order by code"
    elif what=='mes_vendor':
        s="select id,name from mes_vendor a where company_id="+cid+\
            " and name like '%"+q+"%' order by name"
    elif what=='mes_customer':
        s="select id,name from mes_customer a where company_id="+cid+\
            " and name like '%"+q+"%' order by name"
    elif what=='inbound_ticket':
        s="select id,code from inventory_inboundtickets where company_id="+cid+\
            " and code like '%"+q+"%' order by code"   

    if s:        
        s=s+lmt
        cur.execute(s)
        rs=cur.fetchall()
        for r in rs:        
            if what=='user':
                data.append({"value":r[0],"label":r[1],"user_name":r[2],'groups':r[3]})
            elif what=='parent':
                data.append({"value":r[0], "label":r[1], "plv":r[4], "level":{"id":r[2], "name":r[3]} })                       
            else:
                data.append({"value":r[0],"label":r[1]})   

    return data


@login_required
def ajax_autocomplete(request,what):
    #data=[]
    cid=request.GET.get('company_id',str(request.user.company.id))
    company=Company.objects.get(id=int(cid))
    q=request.GET.get('term')   
    vtype_id=request.GET.get('vtype_id','2')
    qry_all=request.GET.get('query_all','0')
    product_id=request.GET.get('product_id','0')
    lmt=20
 


    #data=autocomplete(company,what,q,qry_all,lmt,vtype_id)   
    data=autocomplete(company,what,q,qry_all,lmt,vtype_id,product_id)   
    

    return JsonResponse(data, safe=False) 
    
def autoselect(company,what,term,query_all='0',lmt=20,vtype_id='2'):  
    data=[]   
    q=term   
    cur=connection.cursor()
    c=company   
    aid=str(c.ancestor_id)
    cid=str(c.id)  
    qry_all=query_all 
    s=''
    lmt=' limit '+str(lmt)
    
    
    if what=='activityawards':
        s="select id,name from sales_activityawards where activity_id ="+q;

    if s:        
        s=s+lmt
        cur.execute(s)
        rs=cur.fetchall()
        for r in rs:      
            data.append({"value":r[0],"label":r[1]})
    return data
    

@login_required
def ajax_select(request,what):
    #data=[]
    cid=request.GET.get('company_id',str(request.user.company.id))
    company=Company.objects.get(id=int(cid))
    q=request.GET.get('id','0')   
    vtype_id=request.GET.get('vtype_id','2')
    qry_all=request.GET.get('query_all','0')
    lmt=20
    data=autoselect(company,what,q,qry_all,lmt,vtype_id)    
    return JsonResponse(data, safe=False) 


#2020-10-19
def ajax_subdiv(request,division_id):
    data=[]
    cur=connection.cursor()
    cur.execute("select id,name from company_subsaldivision where division_id="+division_id)
    for r in cur.fetchall():
        data.append([r[0],r[1]])
    return JsonResponse(data,safe=False)





#2020-11-24
@login_required
def delete_obj(request,obj_name,id):
    data={'e':'','cnt':0}
    cur=connection.cursor()
    if obj_name=='product':
        cur.execute('update product_product set is_delete=true where id='+id)
        data['cnt']=cur.rowcount

    elif obj_name=='agent':
        cur.execute('update company_company set is_delete=true where id='+id)
        data['cnt']=cur.rowcount

        #2021-11-11 19267:易臻
        if request.user.company.hierarchy.cfg_company.franchise_mgt:
            cur.execute("update company_outlet set is_delete=true where company_id="+id)
    elif obj_name=='series':
        cur.execute('update product_series set is_delete=true where id='+id)
        data['cnt']=cur.rowcount
    elif obj_name=='productprop':
        cur.execute('update product_productprop set is_delete=true where id='+id)
        data['cnt']=cur.rowcount
    elif obj_name=='kangnian_writeofflabel':
        
        sobj = KangNian_WriteOffLabel.objects.get(id=id)
        sobj.delete()
        #cur.execute('delete from sales_kangnian_writeofflabel where id='+id)
        
        data['cnt']=1
    elif obj_name=='kangnian_storehouse_management_detail':
        cur.execute('delete from sales_kangnian_storehouseinventorymanipulation where id='+id)
        data['cnt']=cur.rowcount
    elif obj_name == 'brandrecommender':
        cur.execute("update company_brandrecommender set is_hidden=true,is_active=false,wxopenid='' where id="+id)
        data['cnt']=cur.rowcount
    return JsonResponse(data,safe=False)



#2020-11-18
@login_required
def obj_action(request,obj_name,id,act):
    cur=connection.cursor()
    data={'e':''}
    uid=str(request.user.id)
    if obj_name=='giftsapplication':
        if act=='check':                            #促销礼品申请-审核
            s="update promotion_giftsapplication set "+\
                "approved_by_id="+uid+",tm_approved=now() where id="+id
            cur.execute(s)
    #2021-03-16
    elif obj_name=='entapplication' and act=='print':
        o=EntApplication.objects.get(id=int(id))
        sn='ZDYJ'+str(o.id).zfill(8)
        ps=[]
        s="select p.name,p.qty_p,a.label_code,a.tm,a.address from "+\
            "(select product_id,label_code,address,tm from entertainment_entwriteoff where entapplication_id="+id+") a "+\
            "left outer join "+\
            "(select id,name,qty_p from product_product) p "+\
            "on a.product_id=p.id"
        cur.execute(s)
        for r in cur.fetchall():
            ps.append({
                "name":r[0],
                "spec":"1*"+str(r[1]),
                "label":r[2],
                'time':r[3].strftime("%Y-%m-%d %H:%M:%S"),
                'address':r[4],
            })        
        return render(request,'entertainment/print_entapplication.html',{'o':o,'products':ps,'no':sn})
    elif obj_name=='actapplication' and act=='print':
        o=ActApplication.objects.get(id=int(id))    
        parent=None
        if o.parent_id:
            ps=Company.objects.filter(id=o.parent_id)
            if ps:
                parent=ps[0].name
        cs=EntCategory.objects.filter(company_id=request.user.ancestor_id).order_by('sn')
        sn='PJHX'+str(o.id).zfill(8)
        division=None        
        if o.division_id:
            divs=SalesDivision.objects.filter(id=o.division_id)
            if divs:
                division=divs[0].name
        else:
            if o.agent_id:
                s="select id,name from company_salesdivision where id="+\
                    "(select division_id from company_company where id="+str(o.agent_id)+")"                
                cur.execute(s)
                r=cur.fetchone()
                if r:
                    division=r[1]
        ps=[]
        sm=[]
        s="select a.product_id,coalesce(p.qty_p,1),coalesce(p.name,''),a.cnt from "+\
            "(select product_id,count(*) cnt from  entertainment_entwriteoff where actapplication_id="+id+" group by product_id) a "+\
            "left outer join "+\
            "(select id,name,qty_p from product_product) p "+\
            "on a.product_id=p.id"

        cur.execute(s)
        for r in cur.fetchall():
            x=int(r[3]/r[1])
            p=r[3] % r[1]
            qty=''
            if x:
                qty=str(x)+'箱'
            if p:
                qty=qty+str(p)+'瓶'
            sm.append({
                'product_name':r[2],
                'qty':qty,
                'desc':'1*'+str(r[1]),
                'bottles':r[2],
             })
        s="select p.qty_p,p.name,a.label_code,coalesce(a.address,'') from "+\
            "(select product_id,label_code,address from  entertainment_entwriteoff where actapplication_id="+id+") a "+\
            "left outer join "+\
            "(select id,name,qty_p from product_product) p "+\
            "on a.product_id=p.id"
        cur.execute(s)     
        for r in cur.fetchall():
            ps.append({
                'name':r[1],
                'package':'1*'+str(r[0]),
                'label_code':r[2],
                'address':r[3],                
            })
        tm=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        return render(request,'entertainment/print_actapplication.html',{'o':o,'products':ps,'summary':sm,'sn':sn,'cs':cs,'tm':tm})

    #2021-05-25
    elif obj_name=='giftapplication' and act=='print':
        o=GiftApplication.objects.get(id=int(id))
        sn='ZPSB'+str(o.id).zfill(8)
        qty=0.0
        ps=[]       

        s="select p.name,p.qty_p,to_char((a.x*p.qty_p+a.p)/(1.0*p.qty_p),'999990.99'),"+\
            "case when a.x>0 then a.x||'箱' else '' end||case when a.p>0 then a.p||'瓶' else '' end from "+\
            "(select r.product_id product_id,sum(r.x) x,sum(r.p) p from "+\
                "(select product_id,"+\
                    "case package_id when 1 then 0 when 3 then 1 end x, "+\
                    "case package_id when 1 then 1 when 3 then 0 end p "+\
                    "from entertainment_entwriteoff  where giftapplication_id="+id+\
                    " and product_id>0"+\
                ") r group by r.product_id  "+\
            ") a "+\
            "left outer join "+\
            "(select id,name,qty_p from product_product) p "+\
            "on a.product_id=p.id"
        cur.execute(s)
        for r in cur.fetchall():
            qty=qty+float(r[2])
            ps.append({
                "name":r[0],
                "spec":"1*"+str(r[1]),
                "qty":r[3],
                "qty_x":r[2],
                               
            })    
        return render(request,'entertainment/print_giftapplication.html',{'o':o,'products':ps,'no':sn,'qty':qty})

    elif obj_name=='rebateinvoice' and act=='view':
        o=RebateInvoice.objects.get(id=int(id))
        imgs=[]
        for i in o.rebateinvoiceattach_set.all():
            imgs.append(i)
        return render(request,'obj/view_invoice_imgs.html',{'imgs':imgs})
    elif obj_name=='stack' and act=='view':
        d=get_obj_info('inventory','stack',id)
        return render(request,'obj/view_stack.html',{'data':d})
         
    return JsonResponse(data)


#2020-11-18
@login_required
def obj_view(request,app_name,obj_name,id):
    m=apps.get_model(app_name, obj_name)
    o=m.objects.get(id=int(id))
    return render(request,app_name+'/view_'+obj_name+'.html',{'obj':o})


#2021-02-28 
def list_bulletinboard(user,company,date_from,date_to,is_app=False):
    data={}  
    show_add=False
    sCompany=" where company_id="+str(company.ancestor_id)
    if company.level_id==1:            
        sDiv=''
        sCope=' and scope in (0,1)' #scope=models.SmallIntegerField(_('类别'),choices=((0,'所有人'),(1,'公司内部'),(2,'经销商')),default=0)
        if user.has_perm('company.sys_admin'):
            show_add=True
    else:
        div_id='0'
        if company.is_expense_account:
            sCope=" and scope in (0,1)"            
        else:
            sCope=" and scope in (0,2)"
        if company.division_id:
            div_id=str(company.division_id)
        sDiv=" and case when salesdivisions::text='[]' then true else "+\
            div_id+" in (select jsonb_array_elements_text(salesdivisions)::int) end"      
    cur=connection.cursor()
    sDt=''
    if not is_app:
        sDt=" and (dt between '"+date_from+"' and '"+date_to+"')"   
    sCat=''
    #2021-03-24 内参系统信息 在前端 显示为系统提示 
    if is_app and user.ancestor_id in [1644]:
        sCat=" and category<>2"      

    s="select id,to_char(tm,'yyyy-mm-dd HH24:MI:SS'),subject,"+\
        "case scope when 0 then '全部' when 1 then '公司内部' when 2 then '经销商' end,"+\
        "(select username from company_user where id=user_id),"+\
        "(select count(*) from company_bulletinboardattach where bulletinboard_id =a.id),"+\
        "array_to_string(array(select name from company_salesdivision where id in "+\
            "(SELECT jsonb_array_elements_text(salesdivisions)::int)),','),"+\
        "case category when 0 then '公共信息' when 1 then '政策文件' when 2 then '系统消息' else '其它' end,"+\
        "coalesce((select id from company_bbsreader where bulletinboard_id=a.id and user_id="+str(user.id)+"),0),"+\
        "coalesce(a.remark,'') from "+\
        "(SELECT id,subject,salesdivisions,scope,is_active,tm,tm_edit,remark,user_id,category "+\
            "FROM company_bulletinboard b "+sCompany+sDt+sCope+sDiv+sCat+""+\
            " and (select id from company_bbsdelete where bulletinboard_id=b.id and user_id="+str(user.id)+") is null"+\
        ") a  order by id desc"     
   

    cur.execute(s)
    d=[]
    if is_app:
        for r in cur.fetchall():      
            read=False
            if r[8]>0:
                read=True
            d.append({
                'id':r[0],'time':r[1],'subject':r[2],'scope':r[3],'user':r[4],'attachments':r[5],'salsedivision':r[6],'category':r[7],'read':read,'remark':r[9]                
            })
        data=d   
    else:        
        perm=False       
        if user.has_perm('company.sys_admin'):
            perm=True
        for r in cur.fetchall():
            opt=''
            if perm:
                opt='<a style="text-decoration:underline;" href="/company/bulletinboard/'+str(r[0])+'/">修改</a>'
            d.append([
                '<a style="text-decoration:underline;" href="/obj/view/company/bulletinboard/'+str(r[0])+'/">'+r[1]+'</a>',
                r[4],r[2],r[7],r[3],r[6],r[5],opt
            ])
        data['murl']='/company/bulletinboard/' 
        data['clmns']=["发布时间","发布人","标题摘要","信息类别","发布范围","指定战区","附件","操作"]     
        data['data']=d
        data['show_add']=show_add
        data['date_from']=date_from
        data['date_to']=date_to    
    cur.close()
    return data



@tj_login_required
def externalcode_check(request,cid,id,code):
    data={'flag':False}
    if id:
        sWhere = "and id != "+str(id)
    else:
        sWhere = ""
    cur = connection.cursor()
    if code != "0000":
        s = 'select count(*) from logistics.company_company WHERE external_code = \''+ code +'\' and  parent_id = '+cid + sWhere
        cur.execute(s)
        r = cur.fetchone()
        if (r[0] > 0 ):
            data['flag']=True
    return JsonResponse(data)
