from django.shortcuts import render
from .models import *
from django.views.decorators.csrf import csrf_exempt
from django.http import HttpResponse, HttpResponseRedirect, JsonResponse
import json
from django.db import connection
import datetime
import os
import pyodbc
from django.conf import settings
from utils.views import *
from django.utils.translation import gettext_lazy as _
from .forms import *

from django.contrib.auth.decorators import login_required

# Create your views here.

def get_xfbqt(request, dept_id=None):
    data = {'e':'','details':[]}
    if dept_id:
        objs = XfBanquet.objects.filter(department=int(dept_id))
    else:
        objs = XfBanquet.objects.all()
    for x in objs:
        t = ''
        if x.tm:
            t = x.tm.strftime('%Y-%m-%d %H:%M:%S')
        a = []
        cnt = 0
        t = ''
        for b in x.xfbqtdetails_set.all():
            if b.tm:
                t=b.tm.strftime('%Y-m-d %H:%M:%S')
            a.append({
                'box_code':b.box_code,
                'bottle_code':b.bottle_code,
                'time': t
            })
            cnt = cnt+1
        data['details'].append({
            'bqt_id':x.id,
            'code': x.code,
            'dept_id': x.department,
            'subject': x.subject,
            'salesman': x.salesman,
            'location': x.location,
            'host': x.host,
            'host_tel': x.host_tel,
            'remark': x.remark,
            'create_time': t,
            'records_cnt':cnt,
            'records':a
        })
    return JsonResponse(data)


@csrf_exempt
def xfbqt_batch(request):
    data = {'e': '','id': 0}
    r = json.loads(request.body)
    b = XfBanquet(subject=r['subject'])
    code = str(r['code'])
    objs = XfBanquet.objects.filter(code=code)
    if objs:
        data['e'] = '活动编码 ' + code + " 已经存在！"
        return JsonResponse(data)
    b.code = code
    if r['department']:
        b.department = int(r['department'])
    if r['salesman']:
        b.salesman=r['salesman']
    if r['location']:
        b.location = r['location']
    if r['host']:
        b.host = r['host']
    if r['host_tel']:
        b.host_tel = r['host_tel']
    if r['remark']:
        b.remark = r['remark']
    b.save()
    data['id'] = b.id
    return JsonResponse(data)


@csrf_exempt
def xfbqt_details(request):
    data = {'e': '', 'cnt': 0}
    r = json.loads(request.body)
    id = str(r['bqt_id'])
    label = r['label']
    objs = XfBqtDetails.objects.filter(bottle_code=label)
    if objs:
        data['e'] = '此标签已扫码 （' + objs[0].bqt.subject + ")"
        return JsonResponse(data)
    # 只能扫瓶标
    cur = connection.cursor()
    s = "select parent_id,package_id from label.labelinfo where code='" + label + "'"
   
    cur.execute(s)
    r = cur.fetchone()
    if not r:
        data['e'] = "标签号码异常"
        return JsonResponse(data)
    if r[1] != 1:
        data['e'] = "请扫描瓶标"
        return JsonResponse(data)
    b_id = str(r[0])
    # 取发货记录
    cur.execute("select label_code,summary_id,id," + \
        "(select fhkey from logistics.delivery_summary where id=summary_id) " + \
        "from logistics.delivery_details where label_code in " + \
            "(select code from label.labelinfo where id=" + b_id + " or parent_id=" + b_id + ")"
    )
    r = cur.fetchone()
    box_code = 'null'
    fhkey = 'null'
    d_id = 'null'
    if r:
        box_code = "'" + r[0] + "'"
        fhkey = "'" + r[3]+ "'"
        d_id = str(r[2])
    s= "insert into logistics.promotion_xfbqtdetails (bqt_id,bottle_code,box_code,fhkey,delivery_details_id,tm)" + \
        " values (" + id + ",'" + label+ "'," + box_code + "," + fhkey + "," + d_id + ",now())"
    cur.execute(s)
    data['cnt'] = cur.rowcount
    return JsonResponse(data)


@csrf_exempt
def upload_receipt_img(request):
    data = {'e':'','path':'', 'fnames':[]}
    t = datetime.datetime.now().strftime('%Y-%m-%d')
    p = 'D:\\web\\tianjianzonghexinxihua\\images\\shqr\\'
    data['path']=t
    p = p + t
    if not os.path.exists(p):
        os.makedirs(p)
    for f in request.FILES.getlist('images'):
        file_path = os.path.join(p, f.name)
        f0 = open(file_path, mode="wb")
        for i in f.chunks():
            f0.write(i)
        f0.close()
        data['fnames'].append(f.name)

    return JsonResponse(data)

#2020-05-18
@tj_login_required
def agent_writeoff(request):
    data={'e':'','data':[],'clmns':[],'dt_fr':'','dt_to':''}
    u = request.user
    c = u.company
    dt = datetime.datetime.now().strftime('%Y-%m-%d')
    dt_fr = request.GET.get('dt_fr', dt)
    dt_to = request.GET.get('dt_to', dt)
    data['dt_fr'] = dt_fr
    data['dt_to'] = dt_to
    data['clmns'] = [
        {'cls': 'lbl', 'tt': _('标签号码')},
        {'cls': 'nck', 'tt': _('昵称')},
        {'cls': 'img', 'tt': _('头像')},
        {'cls': 'add', 'tt': _('扫码地址')},
        {'cls': 'sm_tm', 'tt': _('扫码时间')},
        {'cls': 'agt', 'tt': _('经销商')},
        {'cls': 'prd', 'tt': _('产品名称')},
        {'cls': 'fh_tm', 'tt': _('发货时间')}
    ]
    #2020-05-18 目前暂定判断条件：2020-07-01前，查询117服务器sql server
    if c.id==1 and dt<'2020-07-01':
        cn = pyodbc.connect(settings.WULIU_STR)
        cur = cn.cursor()
        sWhere=" where CompName='酒鬼酒品鉴酒' and (sm_date between '"+dt_fr+"' and '"+dt_to+"')"
        #product=request.GET.get('product_name','')
        #if product:
        #    sWhere=" and product_name='"+product+"'"
        s = "select a.labelcode,isnull(b.nickname,''),isnull(b.HeaderImageUrl,''),a.SMAddress,"+\
            "CONVERT(varchar,a.SMTime,120),isnull(a.agent_name,''),isnull(product_name,''),a.fh_date from "+\
            "(SELECT [LabelCode],[UserID],[SMAddress],[SMTime],[agent_name],[product_name],[fh_date] "+\
                "from [TJMarketingSystemYin].[dbo].[TJ_SMinfo_2018]"+sWhere+") a "+\
            "left outer join "+\
            "(select [UserID],[NickName],[HeaderImageUrl] from [TJMarketingSystemYin].[dbo].[TJ_User]) b "+\
            "on a.userid=b.userid"
        cur.execute(s)
        for r in cur.fetchall():
            tm=''
            i=''
            if r[6]:
                tm=r[7]
            if r[2]:
                i='<img src="'+r[2]+'" class="img_header"></img'
            data['data'].append([ r[0],r[1],i,r[3],r[4],r[5],r[6],tm ])
    return render(request, 'sales/agents_writeoff.html',data)




#2020-10-02
@login_required
def edit_gifts(request,what,id=None):   
    is_saved=False
    u=request.user
    c=u.company
    div_ids=[]        
    subdiv_ids=[] 
    if id:
        if what=='scheme':
            o=GiftsScheme.objects.get(id=int(id))
        elif what=='application':
            o=GiftsApplication.objects.get(id=int(id))
        elif what=='requisition':
            o=GiftsRequisition.objects.get(id=int(id))
    else:
        if what=='scheme':
            o=GiftsScheme(company_id=c.id,user_id=u.id)
        elif what=='application':
            o=GiftsApplication(company_id=c.id,user_id=u.id,ancestor_id=c.ancestor_id)
        elif what=='requisition':
            o=GiftsRequisition(company_id=c.id,user_id=u.id,ancestor_id=c.ancestor_id,status_id=1)
    if request.method=='GET':
        if what=='scheme':
            cur=connection.cursor()            
            form=SchemeForm(instance=o)
            p_set=SchemeProductFormset(instance=o)
            m_set=SchemeMaterialFormset(instance=o)

            #2020-11-19
            cur.execute("select id,name from company_salesdivision where id in "+\
                "(select unnest((select div_ids from promotion_giftsscheme where id="+id+")))"
            )
            for r in cur.fetchall():
                div_ids.append([ r[0],r[1] ])
            cur.execute("select id,name from company_subsaldivision where id in "+\
                "(select unnest((select subdiv_ids from promotion_giftsscheme where id="+id+")))"
            )
            for r in cur.fetchall():
                subdiv_ids.append([ r[0],r[1] ])     
        elif what=='application':
            form=GiftApplicationForm(instance=o)
            p_set=GApplicationProductFormset(instance=o)
            m_set=GApplicationMaterialFormset(instance=o)
        elif what=='requisition':
            form=GiftRequisitionForm(instance=o)
            p_set=GRequisitionProductFormset(instance=o)
            m_set=GRequisitionMaterialFormset(instance=o)
    else:
        if what=='scheme':
            form=SchemeForm(request.POST, request.FILES,instance=o)
            p_set=SchemeProductFormset(request.POST, request.FILES,instance=o)
            m_set=SchemeMaterialFormset(request.POST, request.FILES,instance=o)
        elif what=='application':
            form=GiftApplicationForm(request.POST, request.FILES,instance=o)
            p_set=GApplicationProductFormset(request.POST, request.FILES,instance=o)
            m_set=GApplicationMaterialFormset(request.POST, request.FILES,instance=o)
        elif what=='requisition':
            form=GiftRequisitionForm(request.POST, request.FILES,instance=o)
            p_set=GRequisitionProductFormset(request.POST, request.FILES,instance=o)
            m_set=GRequisitionMaterialFormset(request.POST, request.FILES,instance=o)
        if form.is_valid():            
            o=form.save()      
            if p_set.is_valid():
                p_set.save()
            if m_set.is_valid():
                m_set.save()
            #2020-10-04
            if what=='requisition' and not id:
                l=RequisitionStatusLog(requisition_id=o.id,status_id=1,user_id=u.id)
                l.save()
                #写入产品日志明细  
                #s="INSERT INTO promotion_requisitionlogproduct(label_code,package_id,label_id,label_pk_id,dt,tm,requisitionlog_id) "+\
                #    "select label_code,package_id,label_id,label_pk_id,now(),now(),"+str(l.id)+" "+\
                #    "from promotion_requisitionproductdetails where requisition_id="+str(o.id)
                #cur.execute(s)
                #写入物料日志明细
                #s="INSERT INTO promotion_requisitionlogmaterial(material_id,qty,dt,tm,requisitionlog_id) "+\
                #    "select material_id,qty,now(),now(),"+str(l.id)+" "+\
                #    "from promotion_requisitionmaterials where requisition_id="+str(o.id)
                #cur.execute(s)
            return HttpResponseRedirect('/obj/list/gifts'+what+'/')    
    d=[]
    for i in SalesDivision.objects.filter(company_id=c.id):
        d.append([ i.id, i.name ])
    data={
        'form':form,
        'saldivs':d,
        'is_saved':is_saved,
        'pset':p_set,
        'mset':m_set
    }
    #2020-11-19
    if what=='scheme':
        data['div_ids']=div_ids
        data['subdiv_ids']=subdiv_ids    
    return render(request, 'promotion/'+what+'.html',data)


#2020-10-04
def gifts_requisition_log(request,id):
    c=request.user.company
    obj=GiftsRequisition.objects.get(id=int(id))
    cur=connection.cursor()
    dd=[]
    
    #2020-05-22
    k='fgfdvgu$#&3t@j'
    tt=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    m2=hashlib.md5((k+tt).encode('utf8'))
    key=m2.hexdigest()
    s="select a.name,b.tm,"+\
            "(select username from company_user where id=b.user_id),a.id,b.id,"+\
            "(select action from promotion_giftsstatus where id=a.id) "+\
            " from "+\
            "(select id,sn,name from promotion_giftsstatus where company_id is null or company_id="+str(c.ancestor_id)+" order by sn) a "+\
            "left outer join "+\
            "(select user_id,tm,status_id,id,requisition_id from promotion_requisitionstatuslog where requisition_id="+id+" and status_id<="+str(obj.status_id)+\
                " and id in (select max(id) from promotion_requisitionstatuslog group by requisition_id,status_id)) b "+\
            "on a.id=b.status_id "+\
            "order by sn"
    cur.execute(s)
    for r in cur.fetchall():
        t=''
        u=''           
        act=''
        products=[]
        materials=[]
        if r[1]:
            t=r[1]
            u=r[2]
            lid=str(r[4])
            #product
            s="select a.product_id,sum(a.x) x,sum(a.h) h,"+\
                "coalesce((select name from product_product where id=a.product_id),''),count(*) from "+\
                "(select product_id,case package_id when 3 then 1 else 0 end x,case package_id when 1 then 1 else 0 end h "+\
                    "from promotion_requisitionlogproduct where requisitionlog_id="+lid+" and status_id="+str(r[3])+\
                ") a group by a.product_id" 
            cur.execute(s)
            for rr in cur.fetchall():
                q=''
                if rr[1]:
                    q=q+str(rr[1])+"箱"
                if rr[2]:
                    q=q+str(rr[2])+"瓶"  
                if q:
                    products.append({'name':rr[3],'qty':q})
                else:
                    products.append({'name':rr[3],'qty':str(rr[4])+"(未知包装)"})
            #material
            s="select coalesce((select name from promotion_material where id=material_id),''),qty "+\
                "from promotion_requisitionlogmaterial where requisitionlog_id="+lid+" and status_id="+str(r[3])
            cur.execute(s)
            for rr in cur.fetchall():
                materials.append({'name':rr[0],'qty':rr[1]})
        #2020-10-08
        if r[3] in (2,3):
            act=r[5]
        dd.append({
            'status_id':r[3],
            'status_action':act,
            'status_name':r[0],
            'time':t,
            'user':u,
            'products':products,
            'materials':materials,            
        })    
    data={'req':obj,'logs':dd,'time':tt,'key':key }
    return render(request, 'promotion/requistion_log.html',data)



#2020-11-06
#def promotion_approve(request,what,id):
#    if what=='giftapplication':
#        obj=
