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 *
import psycopg2
import json
from product.models import *
from django.contrib.auth.models import Group
from inventory.forms import *
from django.http import HttpResponse, HttpResponseRedirect, JsonResponse
import os
from sales.models import Activities,ChannelPromotions
#@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'],
    }
    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-06-09
    '''
    if obj_name=='user':
        if not id:        
            obj.company=request.user.company
    else:
        obj.company=request.user.company
    '''
    if obj_name=='user':
        if not id:        
            obj.company=request.user.company
    elif obj_name=='companycfg':
        pass
    else:
        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 = []
        products = []
        adms = []
        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))
        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+")"
                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


            #2020-04-24 所有酒鬼客户，全部同步到透云
            if obj.hierarchy_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, 'children': children,
            'parent': parent, 'prs': prs, 'is_saved': is_saved,'adms':adms,
            'level': obj.level, 'jg_ty': jg_ty, 'sync_ty': sync_ty} )
    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':
        c = request.user.company
        company_src = []
        groups=[] 
        mygroups=[]
        if request.method == 'GET':
            obj.password=''
            form = UserForm(instance=obj)    
        elif request.method == "POST":
            group_ids = request.POST['group_ids']
            form = UserForm(request.POST,instance=obj) 
            if form.is_valid():                
                company_id=int(request.POST.get('company',c.id))             
                u_name = form.cleaned_data.get('username')
                u_pwd = form.cleaned_data.get('password')       
                phone = form.cleaned_data.get('phone') 
                if id:
                    u=User.objects.get(id=int(id))
                else:
                    u=User()
                u.username=u_name
                u.phone=phone
                u.company_id=company_id
                u.ancestor_id=c.ancestor_id               
                u.set_password(u_pwd)
                u.save()                    
                #myuser.groups.remove(group, group, ...)
                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
        for g in Group.objects.all():
            groups.append({'id':g.id,'name':g.name})         
        #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()
        if id:
            for g in obj.groups.all():
                mygroups.append(str(g.id))         

        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
    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', [_('营销活动'), _('中奖记录')]],
        'channelpromtions': ['活动设置', 'sales', 'ChannelPromotions',  [_('渠道促销'),  _('活动设置')] ],
        
        #'prd_workshop': ['生产班组', 'company', 'PrdWorkshop'],
    }
    u=request.user
    c=u.company
    if obj not in d:
        return HttpResponse(_('无效对像名'))
    data['subtt']=d[obj][3]
    m = apps.get_model(d[obj][1], d[obj][2])
    cur=connection.cursor()    
    if obj=='product':        
        sub_qry=[]
        data['clmns']=[_('产品名称'),_('品牌系列'),_('图片'),]
        has_pro=False
        cnt=2
        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 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):
                data0.append(r[n])  
            if u.has_perm('company.sys_admin'):
                data0[0]='<a href="/obj/edit/product/'+str(r[cnt+3])+'/">'+r[0]+'</a>'
            else:
                data0[0]=r[0]
            data['data'].append(data0)
            
    elif obj=='series':
        cur.execute("select id,name,coalesce(remark,'') from product_series 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/series/'+str(r[0])+'/">'+r[1]+'</a>'
            else:
                a=r[1]
            data['data'].append([a,r[2]])
    elif obj=='store':
        data['clmns']=[_('仓库名称'),_('地址')]
        cur.execute("select id,name,coalesce(address,'') from inventory_storehouse where company_id="+str(c.id))
        for r in cur.fetchall():
            if u.has_perm('company.sys_admin'):
                a='<a href="/obj/edit/store/'+str(r[0])+'/">'+r[1]+'</a>'
            else:
                a=r[1]            
            data['data'].append([a,r[2]])
    elif obj=='user':
        sWhere=" where company_id="+str(c.id)
        if c.level_id==1:
            sWhere=" where ancestor_id="+str(c.id)  
        data['clmns']=[_('帐号'),_('公司名称'),_('父级公司'),_('注册时间'),_('营销层级'),_('功能组别'),_('联系电话')]
        cur.execute("select id,username,to_char(date_joined,'yyyy-mm-dd HH24:MI:SS'),"+\
                "coalesce((select name from company_company where id=company_id),''),"+\
                "coalesce((select name from company_level where id=(select level_id from company_company where id=company_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(phone,(select cell_phone from company_company where id="+str(c.id)+"),''), "+\
                "coalesce((select name from company_company where id="+\
                    "(select parent_id from company_company where  id=a.company_id)),'') "+\
                "from company_user a "+sWhere)
        for r in cur.fetchall():
            if u.has_perm('company.sys_admin'):
                a='<a href="/obj/edit/user/'+str(r[0])+'/">'+r[1]+'</a>'
            else:
                a=r[1]
            data['data'].append([a,r[3],r[7],r[2],r[4],r[5],r[6]])
    elif obj=='agent':
        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"
        cur.execute("select id,name,"+\
            "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),''),"+\
            "coalesce(address,''),coalesce(remark,'') "+\
            "from company_company a "+sWhere)
        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]
            data['data'].append([a,r[2],r[3],r[4],r[5],r[6],r[7]])
    elif obj=='productprop':
        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))
        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':
        data['clmns']=[_('战区名称'),_('战区编码'),_('战区经理'),_('备注说明')]       
        cur.execute("SELECT id, name,coalesce(code,''), coalesce(remark,''),"+\
            "coalesce((SELECT username from company_user where id=manager_id),'') "+\
            "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]
            data['data'].append([a,r[2],r[4],r[3]])
    elif obj=='sal_subdivision':
        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':
        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':   
        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,'FM99999999.99'),''),"+\
                "(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([ r[7], nm, 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,'FM99999999.99'),'') "+\
                "from sales_award"+sWhere   
            cur.execute(s)
            for r in cur.fetchall():
                img=''
                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/'
        data['obj']='Award'        
    elif obj=='activities':   
        data['murl']='/sales/obj/Activities/'
        data['obj']='Activities'
        data['clmns'] = [_('活动名称'), _('创建时间'), _('公司名称'), _('活动对象'), _('中奖方案'), _('优先级'), _('开始时间'), _('结束时间'), _('活动状态')]  
        if u.id==1:
            objs=Activities.objects.all()
        else:
            objs=Activities.objects.filter(company_id=c.id)
        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=='channelpromtions':   
        data['murl']='/sales/channelpromotion/'
        #data['obj']='ChannelPromotions'
        data['clmns'] = [_('活动名称'), _('创建时间'), _('公司名称'), _('优先级'), _('开始时间'), _('结束时间'), _('活动状态')]  
        if u.id==1:
            objs=ChannelPromotions.objects.all()
        else:
            objs=ChannelPromotions.objects.filter(company_id=c.id)
        for act in objs:
            data['data'].append([ 
                "<a href='/sales/channelpromotion/"+str(act.id)+"/'>"+act.name+"</a>", 
                act.tm.strftime('%Y-%m-%d %H:%M:%S'),act.company.name, 
                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()
            ])  
    elif obj=='companycfg':   
        data['murl']='/obj/edit/companycfg/'
        data['obj']='CompanyCfg'
        data['clmns'] = [_('公司名称'), _('静默授权'), _('微信公众号'), _('天鉴代授权'), _('产品图片'), _('产品价格'),
            _('库存管理'), _('跨段后绑定'),_('产品授权'),_('区域授权'),_('一级自动入库'), _('一级可录入库'), _('一级入库规则'), _('二级入库规则')]  
        for o in CompanyCfg.objects.all():
            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>", 
                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 = datetime.datetime.now().strftime('%Y-%m-%d')
        dt_fr = request.GET.get('date_from', dt)
        dt_to = request.GET.get('date_to', dt)
        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'] = [_('公司名称'), _('活动名称'),_('奖项'), _('奖品'), _('微信昵称'), _('微信头像'), _('姓名'), _('电话'), _('中奖时间'), _('领奖时间')]             
            s="select (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(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+") 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'] = [_('活动名称'),_('奖项'), _('奖品'), _('微信昵称'), _('微信头像'), _('姓名'), _('电话'), _('中奖时间'), _('领奖时间')]             
            s="select " +\
                "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(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[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]
                ])
        
    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





@login_required
def ajax_autocomplete(request,what):
    data=[]
    cid=request.GET.get('company_id',str(request.user.company.id))
    q=request.GET.get('term')   
    cur=connection.cursor()
    s=''
    lmt=' limit 5'
    if what=='agent':
        qry_all=request.GET.get('query_all','0')
        if qry_all=='0':
            s="select id,name from company_company where parent_id="+cid+\
                " and name like '%"+q+"%' order by name"
        else:
            s = "select id,name from company_company where ancestor_id=" + cid + \
                " and name like '%" + q + "%' order by name"    
    elif what=='product':
        s="select id,name from product_product where company_id="+cid+\
            " and name like '%"+q+"%' order by name"   
    elif what=='series':
        s="select id,name from product_series where company_id="+cid+\
            " 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"
    if s:        
        s=s+lmt
        cur.execute(s)
        rs=cur.fetchall()
        for r in rs:        
            data.append({"value":r[0],"label":r[1]})
    return JsonResponse(data, safe=False) 
