from django.shortcuts import render
from .models import *
from django.db import connection
from django.shortcuts import redirect
from utils.views import *
from django.utils.translation import gettext_lazy as _
from django.http import HttpResponse, HttpResponseRedirect, JsonResponse
# Create your views here.



def decrypt_11(code):
    c=''
    if len(code)==11:
        s=code.lower()[:10]
        code=s[9]+s[2]+s[1]+s[3:9]+s[0]
        c=str(int('0x'+code,16))
    return c


def decrypt_11h(code):
    if code[0].lower()=='h':
        return decrypt_11(code[1:])
    return ''




@tj_login_required
def list_query_auth(request):
    data={'clmns':[],'data':[]}
    cid=str(request.user.company_id)
    cur=connection.cursor()
    s="SELECT id, (select username from company_user where  id=user_id),"+\
        "auth_name FROM product_authquery where company_id="+cid    
    data['clmns']=[_('稽查员'),_('授权产品')]
    cur.execute(s)
    for r in cur.fetchall():
        if request.user.has_perm('company.sys_admin'):
            u="<a href='/product/query/auth/"+str(r[0])+"/' >"+r[1]+"</a>"            
        else:
            u=r[1]            
        data['data'].append([u,r[2]])   
    return render(request,'product/list_auth.html',data)




@tj_login_required
def query_auth(request,id=None):        
    '''
    1;"市场稽查组"
    2;"成品入库组"
    3;"销售出库组"
    4;"系统管理员"
    5;"经销商"
    6;"套标查询组"
    7;"内勤管理组"
    8;"业务员"
    9;"战区经理"
    10;"业务经理"
    11;"业务主管"
    12;"销售助理"
    13;"营销管培生"
    14;"战区副经理"
    15;"区域经理"
    16;"战区总监"
    '''   
    cid=str(request.user.company.id)
    cur=connection.cursor()    
    data={'users':[],'series':[],'auths':[],'is_saved':False}     
    if id:
        obj=AuthQuery.objects.get(id=int(id))         
    else:
        obj=AuthQuery()    
    data['obj']=obj
    cur.execute("select id,name from product_series where company_id="+cid)
    for r in cur.fetchall():
        data['series'].append([r[0],r[1]])
    if request.method=='GET':
        s="select user_id,(select username from company_user where id=user_id) from company_user_groups "+\
            "where (select company_id from company_user where id=user_id)="+cid+" and group_id in (1)"
        cur.execute(s)    
        for r in cur.fetchall():
            data['users'].append([r[0],r[1]])    
    else:
        obj.company_id=int(cid)
        obj.content=request.POST.get('content')
        obj.what=request.POST.get('what')
        obj.user_id=request.POST.get('user_id')
        if obj.what=='product':
            s="select array_to_string(array(select name from product_product where id in ("+obj.content+")),',')"
        elif obj.what=='series':
            s="select array_to_string(array(select name from product_series where id in ("+obj.content+")),',')"        
        cur.execute(s)
        r=cur.fetchone()
        obj.auth_name=r[0]
        obj.save()   
        data['is_saved']=True
        request.path='/product/query/auth/'+str(obj.id)+'/'
    s=''
    if obj.what=='product':
        s="select id,name from product_product where id in ("+obj.content+")"
    elif obj.what=='series':
        s="select id,name from product_series where id in ("+obj.content+")"
    if s:
        cur.execute(s)
        for r in cur.fetchall():
            data['auths'].append([r[0],r[1]]) 
    return render(request,'product/query_auth.html',data)





#2020-08-29
@csrf_exempt
def label_binding(request,company_id):
    data={'e':'','cnt':0,'type':''}
    sKs={
        "1":'jg!@bh9%$3Z4Ag35xV', #1酒鬼酒
    }
    k = sKs[company_id]
   
    
    #2020-09-05
    f=open('d:\\lbl_binding.txt','w')
    f.write(request.body.decode('utf-8'))
    f.write('\r\n')
    f.write('CONTENT_TYPE:'+request.META['CONTENT_TYPE'])
    f.close()

    e = time_key_check(request, k)
    if e:
        data['e'] = e
        return JsonResponse(data)
    r = json.loads(request.body.decode('utf-8'))

    keys = ['type', 'data']
    #pids={
    #    465:{'product_id':385,'product_name':'52度500ml黄坛酒鬼酒（2018版）210(238101)'},
    #    210:{'product_id':373,'product_name':'52度500ml红坛酒鬼酒(高度柔和)106(238025)'},
    #    106:{'product_id':164,'product_name':'50度500ml酒鬼酒'}
    #}
    pids = {
        '（243530）52度500ml黄坛酒鬼酒（2018版）': '385',
        '(238101)52度500ml红坛酒鬼酒(高度柔和)': '373',
        '(238025)50度500ml酒鬼酒': '164',
    }

    '''
    productid	productname
    465	（243530）52度500ml黄坛酒鬼酒（2018版）
    210	(238101)52度500ml红坛酒鬼酒(高度柔和)
    106	(238025)50度500ml酒鬼酒
    '''

    #if r['product_name'] not in pids:
    #    data['e']=_('产品D错误：465,210,106')
    #    return JsonResponse(data)
    for k in keys:
        if k not in r:
            data['e'] = _('缺少参数') + k
            return JsonResponse(data)

    type=r['type']
    #product_id=pids[r['product_name']]
    ts={'xx':'箱箱','xh':'箱盒','hp':'盒瓶'}

    if type not in ts:
        data['e']=_('对应关系错误：xx,xh,hp')
        return JsonResponse(data)
    cur=connection.cursor()
    #s="insert into labelbinding.batch(company_id,binding_type,product_id,product_name) values ("+company_id+",'"+\
    #    type+"',"+product_id+",'"+r['product_name']+"')  returning id"

    s="insert into labelbinding.batch(company_id,binding_type) values "+\
        "("+company_id+",'"+type+"')  returning id"
    cur.execute(s)
    rr=cur.fetchone()
    id=str(rr[0])
    aa=[]
    cnt=0
    for a in r['data']:
        #product_id=pids[a['product_name']]
        h='null'
        pid='null'
        c=a['label_code']
        if a['product_name'] in pids:
            pid=str(pids[a['product_name']])
        if type=='hp':
            h="'"+c+"'"
            c = decrypt_11h(h)            
        aa.append("("+str(id)+",'"+a['txt']+"','"+a['parent_code']+"','"+c+"','"+a['pici']+\
                "','"+str(a['qc'])+"','"+a['time']+"','"+type+"',"+h+","+pid+",'"+a['product_name']+"')" )
    if aa:
        s="insert into labelbinding.batch_details(batch_id,src,parent_code,label_code,batch_no,qc,tm_binding,type,hcode,product_id,product_name)"+\
            " values "+ ",".join(aa)
        cur.execute(s)
        cnt=cnt+cur.rowcount
    data['cnt']=cnt
    data['type']=ts[type]+"对应"
    return JsonResponse(data)



#2020-08-29
@login_required
def label_binding_process(request):
    company_id=str(request.user.company_id)
    cur=connection.cursor()
    if request.method=='GET':
        data=[]
        s="select id,"+\
            "array_to_string(array(select batch_no from labelbinding.batch_details where batch_id=a.id),','),"+\
            "to_char(a.tm,'yyyy-mm-dd HH24:MI:SS'),product_name,"+\
            "case binding_type when 'xx' then '箱箱' when 'xh' then '箱盒' when 'hp' then '盒瓶' else '未知' end,"+\
            "(select count(*) from labelbinding.batch_details where batch_id=a.id),"+\
            "case binding_type when 'xx' then 1 when 'xh' then 2  when 'hp' then 3 end sn "+\
            "from labelbinding.batch a where company_id="+company_id+" and handled=false order by dt,sn"
        cur.execute(s)
        for r in cur.fetchall():
            data.append([ r[1], r[2], r[3], r[4], r[5], r[0] ])
        stores=[]
        return render(request, 'product/label_binding.html', {'data': data,'stores':stores})
    else:
        tb='labelbinding.batch_details'
        tb_l = 'label.lbl_binding_jg'
        d=json.loads(request.body)
        sId=" and batch_id in ("+",".join(d['batch_id'])+") "

        # 主箱标
        s = "insert into " + tb_l + "(code,package_id,category_id,type_id,is_pk) " + \
            "select code_parent,3,1,2,true from " + tb + " where type='xx'"+sId+\
            "group by code_parent on CONFLICT(code) do nothing"
        cur.execute(s)
        cnt_xx_p=cur.rowcount

        # xx
        s = "insert into " + tb_l + "(code,package_id,category_id,type_id,is_pk,is_related,parent_id) " + \
            "select b.code,3,1,2,false,true," + \
            "(select id from " + tb_l + " where code=b.code_parent) from " + \
            "(SELECT ctid,code,code_parent FROM " + tb + " a where type='xx'" +sId+ \
            "and a.ctid in (select max(ctid) from " + tb + " a where type='xx' "+sId+" group by code) " + \
            ") b where (select id from " + tb_l + " where code=b.code_parent)>0 " + \
            "on CONFLICT(code) do nothing"
        cur.execute(s)
        cnt_xx_c=cur.rowcount

        # xh
        s = "insert into " + tb_l + "(code,package_id,category_id,type_id,is_pk,parent_id) " + \
            "select b.code,2,1,2,false," + \
            "(select coalesce(parent_id,id) from " + tb_l + " where code=b.code_parent) from " + \
            "(SELECT ctid,code,code_parent FROM " + tb + " a where type='xh' " + sId+ \
            "and a.ctid in (select max(ctid) from " + tb + " a where type='xh' "+sId+" group by code) " + \
            ") b where (select id from " + tb_l + " where code=b.code_parent)>0 " + \
            "on CONFLICT(code) do nothing"
        cur.execute(s)
        cnt_xh=cur.rowcount

        # hp
        s = "insert into " + tb_l + "(code,package_id,category_id,type_id,is_pk,parent_id) " + \
            "select b.code,1,1,2,false," + \
            "(select id from " + tb_l + " where code=b.code_parent) from " + \
            "(SELECT ctid,code,code_parent FROM " + tb + " a where type='hp' " +sId+ \
            "and a.ctid in (select max(ctid) from " + tb + " a where type='hp' "+sId+" group by code) " + \
            ") b where (select id from " + tb_l + " where code=b.code_parent)>0 " + \
            "on CONFLICT(code) do nothing"
        cur.execute(s)
        cnt_hp=cur.rowcount

        # 物流-活动码写入
        s = "INSERT INTO label.lbl_hcode(code, hcode, package_id) " + \
            "select code,hcode,1 from " + tb + " where type='hp' " + sId+\
            " on  CONFLICT(hcode) do nothing"
        cur.execute(s)
        #标签入库

        data={'cnt_xx':{'p':cnt_xx_p,'c':cnt_xx_c},'xp':cnt_xh,'hp':cnt_hp}
        return JsonResponse(data)





#2021-08-04 Jonathan
@csrf_exempt
def ajax_upload_product_image(request):
    data = {
        'e':'',
        'id':'' 
    }
    if request.method == 'POST' and request.user: 
    
        photo = request.POST.get('image','')
        product_id = request.POST.get('product_id','')
        
        if photo and product_id:
        
            show_order = request.POST.get('show_order','')
            remark = request.POST.get('remark','')
            
            img = Images(
                company=request.user.company,
                product_id=product_id,
                photo=photo,
                show_order=show_order,
                remark=remark
            )
            img.save()
            data['id'] = img.id
        else:
            data['e'] = '数据丢失'
        
    else:
        data['e'] = '数据丢失'
    
    
    return JsonResponse(data)

@csrf_exempt
def ajax_product_detail(request):
    data = {
        'e':'',
        'name':'',
        'price':'',
        'series':'',
        'pl':'',
        'standard':'',
        'processflow':'',
        'props':[],
        'desc':''
    }
    product_id = request.GET.get("id","")
    if product_id:
        cur=connection.cursor()
        cur.execute("""select name, 
                    price,
                    (select name from product_series where id = series_id),
                    pl,
                    standard,
                    processflow,
                    "desc"
                    from product_product where id = """+product_id)
        r = cur.fetchone()
        if r:
            data['name'] = r[0]
            data['price'] = r[1]
            data['series'] = r[2]
            data['pl'] = r[3]
            data['standard'] = r[4]
            data['processflow'] = r[5]
            data['desc']=r[6]
            
        cur.execute(f"""
            SELECT
                ( SELECT desc_cn FROM company_categoryprop WHERE ID = ( SELECT categoryprop_id FROM product_productprop WHERE ID = prd_property_id ) ),
                ( SELECT VALUE FROM product_productprop WHERE ID = prd_property_id ) ,
                ( SELECT unit FROM company_categoryprop WHERE ID = ( SELECT categoryprop_id FROM product_productprop WHERE ID = prd_property_id ) )
            FROM
                product_prdprops
            WHERE
                product_id = {product_id}
        """)
        rs = cur.fetchall()
        if rs:
            for rr in rs:
                data['props'].append({
                    'desc': rr[0],
                    'value': rr[1],
                    'unit': rr[2]
                })
    else:
        data['e'] = '数据丢失'

    return JsonResponse(data)




#2022-06-12 产品保质期管理
@login_required
def shelf_life(request,what='list',batch_id=None):
    data={'e':'','clmns':[],'data':[]}
    c=request.user.company
    cid=str(c.id)
    aid=str(c.ancestor_id)
    if c.level_id>1:
        data['e']='目前此功能只适用于企业级用户'
    else:
        cur=connection.cursor()
        if what=='list':             
            #默认列示经销商库存产品
            for_what=request.GET.get('for','agent')
            #到期提醒提前天数，默认到期1天前提醒
            lead=request.GET.get('lead','1')            
            sDtInbound="(select dt from stkdetails.c"+aid+" where company_id="+cid+" and vtype_id=1 and label_pk_id=d.label_pk_id order by dt desc limit 1)"
            
            #sDays="extract(day FROM (select dt from stkdetails.c"+aid+" where company_id="+cid+" "+\
            #    "and vtype_id=1 and label_pk_id=d.label_pk_id order by dt desc limit 1)-now() + "+\
            #        "(case (select shelf_life from logistics.product_product where id=product_id) "+\
            #            "when '{}' then ' 10000 day' "+\
            #        "else  (((select shelf_life from logistics.product_product where id=product_id)->>'value')||"+\
            #                "' '||((select shelf_life from logistics.product_product where id=product_id)->>'period')) "+\
            #        " end)::interval "+\
            #    ")"
            
            #2022-06-14
            sDays="round(extract('epoch' FROM (select dt from stkdetails.c"+aid+" where company_id="+cid+" "+\
                "and vtype_id=1 and label_pk_id=d.label_pk_id order by dt desc limit 1)-now() + "+\
                    "(case (select shelf_life from logistics.product_product where id=product_id) "+\
                        "when '{}' then ' 10000 day' "+\
                    "else  (((select shelf_life from logistics.product_product where id=product_id)->>'value')||"+\
                            "' '||((select shelf_life from logistics.product_product where id=product_id)->>'period')) "+\
                    " end)::interval "+\
                ")/3600/24.0)"
            s="select batch_id,to_char(dt_inbound,'yyyy-mm-dd'),cnt,p.name,"+\
                "case when days<0 then '已过期'||abs(days)||'天' when days=0 then '今天到期' else ''||days end,"\
                "to_char(b.tm,'yyyy-mm-dd hh24:MI:ss'),"+\
                "(select name from company_company where id=company_to_id),"+\
                "p.shelf_life->>'value'||(case p.shelf_life->>'period' "+\
                    "when 'day' then '天' "+\
                    "when 'month' then '个月' "+\
                    "when 'year' then '年' end),"+\
                "p.id,b.code from "+\
                "(select batch_id,product_id,company_to_id,count(*) cnt,"+sDtInbound+" dt_inbound,"+sDays+" days "+\
                    "from stkdetails.c"+aid+" d where company_id="+cid+" and vtype_id=2 and "+sDays+"<="+lead+" "+\
                    "group by batch_id,product_id,company_to_id,"+sDtInbound+\
                ") g "+\
                "left outer join "+\
                "(select id,name,shelf_life from product_product where company_id="+aid+") p "+\
                "on g.product_id=p.id "+\
                "left outer join "+\
                "(select id,code,tm from inventory_batch where company_id="+aid+" and vtype_id=2) b "+\
                "on g.batch_id=b.id"
            data['clmns']=['产品名称','经销商名称','入库日期','保质期','剩余天数','发货时间','单号','数量']
            cur.execute(s)
            for r in cur.fetchall():
                data['data'].append([ r[3],r[6],r[1],r[7],r[4],r[5],r[9],
                    "<a href='/product/shelflife/details/"+str(r[0])+"/?product_id="+str(r[8])+"'>"+str(r[2])+"</a>"
                ])            
            html='list_shelf_life.html'
        elif what=='details':   
            product_id=request.GET.get('product_id')
            html='details_shelf_life.html'
            cur.execute("select name,shelf_life from product_product where id="+product_id)
            r=cur.fetchone()
            if not r:
                data['e']='product id error'
            else:
                cur.execute("select to_char(tm,'yyyy-mm-dd hh24:MI:ss'),company_to_id,"+\
                    "(select name from company_company where id=company_to_id),code "+\
                    "from inventory_batch where id="+batch_id)
                rr=cur.fetchone()
                agent={}
                tm=''
                if rr:
                    tm=rr[0]
                    agent={'id':rr[1],'name':rr[2]}
                data['clmns']=['标签号码','标签类型','备注说明',]
                shelf_life={}
                if r[1]:
                    o=json.loads(r[1])   
                    shelf_life=str(o['value'])
                    if o['period']=='day':
                        shelf_life=shelf_life+'天'
                    elif o['period']=='month':
                        shelf_life=shelf_life+'个月'
                    elif o['period']=='year':
                        shelf_life=shelf_life+'年'
                cur.execute("select label_code,(select name from label.lbl_package where id=label_pkg) from stkdetails.c"+aid+" where batch_id="+batch_id)
                d=[]
                for rrr in cur.fetchall():
                    d.append([rrr[0],rrr[1],''])
                data['delivery']={
                    'product':{
                        'id':int(product_id),
                        'name':r[0],
                        'shelf_life':shelf_life,
                    },                    
                    'code':rr[3],
                    'time':tm,
                    'agent':agent,   
                    'details':d
                }
    return render(request,'product/'+html,data)
