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
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'],        
        'productprop': ['产品属性', 'product','ProductProp'],
        'prd_plant': ['工厂/厂区', 'company', 'PrdPlant'],
        'sal_division': ['营销战区', 'company','SalesDivision'],
        'sal_subdivision': ['营销分区', 'company','SubSalDivision'],
        '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']
        
    }
    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']:        
        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=='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   
    elif obj_name=='agent':
        sync_ty = '0'
        parent = request.user.company
        #children = []
        provinces=[]
        products = []
        adms = []
        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
        is_saved = False
        aid = '0'
        if id:
            aid = id
        if request.method == 'GET':
            # 默认同步到透云
            sync_ty = '1'
            form = AgentForm(instance=obj, user=request.user)
            if id:
                cur=connection.cursor()
                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]])
                cur.close()            
        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
        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} )
    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','') 
                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 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]})
        cur.close()
        return render(request, 'obj/user.html', {
            'form': form,
            'groups':groups,
            'mygroups':mygroups,
            'is_saved':is_saved,
            'company_src': company_src
        })
    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():
                form.save()
                is_saved = True   
        return render(request,'kangnian/inventorymanipulation.html',{'form':form,'is_saved':is_saved})

    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':[],'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',[_('产品管理'),_('产品属性')]],
        'prd_plant': ['工厂/厂区', 'company', 'PrdPlant',[_('公司管理'),_('工厂名称')]],
        'sal_division': ['营销战区', 'company','SalesDivision',[_('公司管理'),_('营销战区')]],
        'sal_subdivision': ['营销分区', 'company','SubSalDivision',[_('公司管理'),_('营销分区')]],
        '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',[_('物流查询'),_('窜货预警')]],
        #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', [_('康年皇冠'), _('激活历史')]],

        "qz_brandrecommender":['品牌推荐官返利明细', 'sales', 'ActivityRebate_BrandRecommenderBonus', [_('求真酒业'), _('品牌推荐官返利明细')]],
        "qz_brandrecommender_redpack_record":['品牌推荐官红包发放情况', 'sales', 'ActivityRebate_BrandRecommenderRedpackRecord', [_('求真酒业'), _('品牌推荐官红包发放情况')]],        
        "qz_anniversary": ['逢整返利明细', 'sales', 'ActivityRebate_Anniversary', [_('求真酒业'), _('逢整返利明细')]]
    }
    
    #2020-10-10
    show_add=False
    can_edit=False

    u=request.user
    c=u.company
    if obj not in d:
        return HttpResponse(_('无效对像名'))
    
    #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])
            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 u.has_perm('company.sys_admin'):
                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,'') from product_series where  company_id="+str(c.id)+" and is_delete=false")
        data['clmns']=[_('系列名称'),_('说明')]
        for r in cur.fetchall():
            if u.has_perm('company.sys_admin'):
                a='<a href="/obj/edit/series/'+str(r[0])+'/">'+r[1]+'</a>'
            else:
                a=r[1]
            data['data'].append([a,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,'') 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]])
    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)         
        
        #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 u.has_perm('company.sys_admin'):
                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=='agent':
        #2020-10-16
        if (c.level_id==1 and u.has_perm('company.sys_admin') ) or u.id==3311:
            show_add=True
        is_finance=False
        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']=[_('经销商/分销商名称'),_('外部编码'),_('父级'),_('营销层级'),_('所属战区'),_('所属分区'),_('经销区域'),_('银行卡'),_('微信钱包'),
                        _('经销商地址'),_('联系电话'),_('派出机构'),_('帐号'),_('终端店')] 
                

        #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"
        
        #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"       


        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 "+\
            "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 u.has_perm('company.sys_admin'):
                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:
                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] ])
    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 u.has_perm('company.sys_admin'):
                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 u.has_perm('company.sys_admin'):
                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 u.has_perm('company.sys_admin'):
                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 u.has_perm('company.sys_admin'):
                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]])
    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/'              
        if u.id==1:            
            sWhere=''
            data['clmns'] = [_('活动名称'), _('创建时间'),_('公司名称'),_('扫码场景'),_('核算单位'),
                _('活动开始时间'),_('活动结束时间'),_('最早发货日期'),_('优先级'),_('当前状态')]  
        else:           
            data['clmns'] = [_('活动名称'), _('创建时间'),              _('扫码场景'),_('核算单位'),
                _('活动开始时间'),_('活动结束时间'),_('最早发货日期'),_('优先级'),_('当前状态')]  
            sWhere=" where company_id="+str(c.id)
        #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 HH24:MI'),''),"+\
            "coalesce(to_char(tm_end,'yyyy-mm-dd HH24:MI'),''),"+\
            "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,"+\
            "case package_unit when 1 then '瓶' when 3 then '箱' end "+\
            "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)
            #print(html)
            if u.id==1:                
                data['data'].append([ 
                    "<a href='/sales/channelpromotion/"+str(r[0])+"/'>"+r[1]+"</a>", 
                    r[4],r[8],r[9],r[10],r[5],r[6],r[7],r[2],r[3]  
                ])                
            else:
                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[9]+"</span>",
                    r[10],r[5], r[6],r[7],r[2],r[3]   
                ])    
    elif obj=='companycfg':   
        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['clmns'] = [_('公司名称'),_('管理员'), _('静默授权'), _('微信公众号'), _('天鉴代授权'), _('产品图片'), _('产品价格'),
            _('库存管理'), _('跨段后绑定'),_('产品授权'),_('区域授权'),_('一级自动入库'), _('一级可录入库'), _('一级入库规则'), _('二级入库规则')]  
        for o in CompanyCfg.objects.all():
            a=[]
            for u in User.objects.filter(company_id=o.company.id,groups__id=4):
                a.append(u.username)                    
            wx_appid=''
            tb_binding=''
            if o.label_binding:
                tb_binding=o.label_binding
            if o.wx_account:
                wx_appid=o.wx_account.wx_appid
            data['data'].append([ 
                "<a href='/obj/edit/companycfg/"+str(o.id)+"/'>"+o.company.name+"</a>", 
                ",".join(a),
                o.get_tjfwbase_oauth_display(),wx_appid,o.get_tjfw_oauth_display(),o.get_product_photo_display(),o.get_product_price_display(),
                o.get_inv_mgt_display(),tb_binding,o.get_auth_product_display(),o.get_auth_area_display(),o.get_inbound_auto_lv1_display(),
                o.get_perm_inbound_lv1_display(),o.get_inbound_cretiria_lv1_display(), o.get_inbound_cretiria_lv2_display()
            ])        
    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))),'' )"+\
                "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]
                ])
    #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) 
    
    #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 "
            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))        
        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();"
            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>'
            ])
    #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;"销售助理"
        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:
            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:
            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 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[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:
            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:
            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:
            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:
            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 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[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=f" where tm::date >= '{dt_fr}' and tm::date <= '{dt_to}' "

        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      

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

        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>"
                print(del_btn)
            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['murl'] = '/obj/edit/kangnian_inventorymanipulation/'
        data['clmns'] = ['经销商名称','商品名称','库存数量','操作笔数']
        sql = "select (select name from logistics.company_company where id=agent_id),(select name from logistics.product_product where id=product_id), coalesce(sum(amount),0), count(1) from logistics.sales_kangnian_inventorymanipulation 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]])
        show_add=True
        can_edit=True
    elif obj=='kangnian_inventorymanipulation_detail': 
        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['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') 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]])
        show_add=False
        can_edit=True
    elif obj=='kangnian_activehistory':
        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['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"
        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=='qz_brandrecommender':
        data['murl'] = '/sales/qz_brandrecommender/'
        data['clmns'] = ['明细ID','品牌推荐官','关联号码','返利时间','金额','说明','兑现状态','是否关联了Openid']
        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 from logistics.sales_activityrebate_brandrecommenderbonus"
        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[5], r[1], str_amount, r[3], is_redeemed, is_set_wxopenid])
        show_add=False
        can_edit=True
    elif obj=='qz_anniversary':
        data['murl'] = '/sales/qz_anniversary/'
        data['clmns'] = ['返利说明','返利时间','奖品','收货人','收货地址','收货人电话','发货状态']
        sql = "select reason,tm,(select name from sales_award where id=award_id),name,address,phone,is_redeemed from sales_activityrebate_anniversary"
        cur.execute(sql)
        
        for r in cur.fetchall():
            is_redeemed = r[6]
            if r[6]:
                is_redeemed = "已发货"
            else:
                is_redeemed = "尚未发货"
            data['data'].append([r[0], r[1], r[2], r[3], r[4], r[5], is_redeemed])
        show_add=False
        can_edit=False
    elif obj=='qz_brandrecommender_redpack_record':
        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,'') 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 = ''
            if r[4] == 'SUCCESS':
                is_success = '<span style="display:inline-block;background:green;width:15px;height:15px;border-radius:50%;"></span> ' + r[4]
            else:
                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],''])
        show_add=False
        can_edit=False        
    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'):   
    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"   
    

        
        
     



    elif what=='product':
        #2020-10-10        
        sWhere=" where company_id="+cid+" 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=='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"   

    #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"  

    #2021-01-20
    elif what=='batch':        
        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"

    #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]})
            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')
    lmt=20
    data=autocomplete(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
    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':
        cur.execute('delete from sales_kangnian_writeofflabel 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+\
                ") 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




