# -*- coding: utf-8 -*-
import datetime
import json

from django.contrib.auth.decorators import login_required
from django.http import HttpResponse, JsonResponse
from django.shortcuts import render
from django.utils.translation import gettext_lazy as _
from django.apps import apps
import psycopg2
from django.db import connection
from django.views.decorators.csrf import csrf_exempt

from collection.models import Collector, Artical, ConsumeLog
from product.models import Product
from sales.models import Member


@login_required
def list_obj(request, obj):
    data = {'data': [], 'clmns': [], 'e': [], 'subtt': [], 'order': [],
            'murl': '/obj/edit/' + obj + '/', 'dt': False, 'add': True, 'allown_delete': True}
    d = {
        'artical': ['收藏', 'collection', 'Artical', [_('藏品管理'), _('大罐酒')]],

    }

    # 2020-10-10
    show_add = False
    can_edit = False

    u = request.user
    c = u.company
    c_id = str(c.id)
    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()

    # 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 == 'artical':
        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 = "  dt between '" + dt_fr + "' and '" + dt_to + "' "

        data['clmns'] = [_('名称'), _('手机号'), _('地址'), _('微信名'), _('产品名称'),  _('剩余数量(ml)'),  _('创建时间'), _('操作')]

        s = "SELECT " +\
            "a.id, " +\
            "COALESCE(a.name,b.name)as name, " +\
            "COALESCE(a.address,b.address,c.address,'')as address, " +\
            "COALESCE(a.phone,b.phone,c.phone,'')as phone, " +\
            "coalesce(c.wx_nickname,'')," +\
            "d.id as artical_id,"+\
            "coalesce(d.qty,0)," +\
            "TO_CHAR(d.tm, 'yyyy-mm-dd HH24:MI:ss') tm," +\
            "e.name," +\
            "coalesce(d.qty,0)+COALESCE(f.qty_consumer,0)+COALESCE(G.qty_adj,0) " +\
            "FROM " +\
            " ( SELECT id,name,address,member_id,phone FROM logistics.collection_collector WHERE company_id = "+c_id+" )A " +\
            " LEFT OUTER JOIN ( SELECT id,name,address,consumer_id,phone FROM logistics.sales_member ) b ON A.member_id = b.ID " +\
            " left outer join (SELECT id,address,phone,wx_nickname FROM logistics.sales_consumeraccount)c on c.id = b.consumer_id " +\
            " left outer join (select id,collector_id,tm,product_id,qty from logistics.collection_artical where  dt between '" + dt_fr + "' and '" + dt_to + "')d on d.collector_id = a.id " +\
            " left outer JOIN (select id,name from logistics.product_product)e on d.product_id = e.id "+\
            " left outer join (SELECT artical_id,sum(qty)qty_consumer from logistics.collection_consumelog GROUP BY artical_id )f on f.artical_id =d.id "+\
	        " left outer join (SELECT artical_id,sum(qty)qty_adj from logistics.collection_adjlog GROUP BY artical_id )g on g.artical_id =d.id 	where e.NAME is not null"
        cur.execute(s)
        r = cur.fetchall()
        for i in r :
            btns = f'<a class="btn btn-sm btn-default btn-dlg" href="/collection/ajax/manage/?obj=purchase&collector_id={i[0]}">补充购酒</a>' + \
                   f'<a class="btn btn-sm btn-default btn-dlg" href="/collection/ajax/manage/?obj=update&artical_id={i[5]}">调整</a>' + \
                   f'<a class="btn btn-sm btn-default btn-dlg" href="/collection/ajax/manage/?obj=detailslog&artical_id={i[5]}&balance={i[9]}">详情</a>'
            btns = f'<a class="btn btn-sm btn-default btn-dlg" href="/collection/ajax/manage/?obj=update&artical_id={i[5]}">调整</a>' + \
                   f'<a class="btn btn-sm btn-default btn-dlg" href="/collection/ajax/manage/?obj=detailslog&artical_id={i[5]}">详情</a>'
            # data['data'].append([i[1],i[2],i[3],i[4],i[8],i[6],i[9],i[7],btns])
            data['data'].append([i[1],i[3],i[2],i[4],i[8],i[9],i[7],btns])


    return render(request, 'obj/list_obj.html', data)

@login_required
def obj_manage(request):
    data={'e':'','data':[],'clmns':[]}
    obj = request.GET.get('obj', '')
    u = request.user
    c_id = str(request.user.company.id)
    cur= connection.cursor()
    if obj == 'detailslog':
        data['subtt'] = ['大罐酒','日志记录' ]
        artical_id = str(request.GET.get('artical_id', ''))
        data['show_balance'] = True
        data['clmns'] = [_('调整方向'), _('数量(ml)'), _('调整时间'), _('调整人'), _('备注')]
        s = "select * from (SELECT a.direction,coalesce(a.qty,0)qty,a.tm,b.last_name,coalesce(a.remark,'')remark from (SELECT " +\
            "case direction when 1 then '增加' when -1 then '减少' else '不变' end direction ,abs(qty)qty," +\
            "TO_CHAR(tm, 'yyyy-mm-dd HH24:MI:SS')tm,user_id,remark from collection_adjlog where artical_id = "+artical_id+")A "+ \
            "left outer join (SELECT id,last_name from  company_user )b on a.user_id = b.id " + \
            "UNION "+ \
            "SELECT a.direction,a.qty,a.tm,b.name,coalesce(a.remark,'')remark  from(SELECT   "+ \
            "case direction when 1 then '新增' when -1 then '使用' else '不变' end direction ,abs(qty)qty, "+ \
            "TO_CHAR(tm, 'yyyy-mm-dd HH24:MI:SS')tm,collector_id,remark from collection_consumelog WHERE artical_id = "+artical_id+")A " +\
            "left outer join (SELECT id,name from  collection_collector )b on a.collector_id = b.id)p order by p.tm desc "
        cur.execute(s)
        r = cur.fetchall()
        for i in r :
            data['data'].append([i[0],i[1],i[2],i[3],i[4]])
        s = "select b.name,coalesce(a.qty,0)qty,c.name,coalesce(b.phone,'')phone,coalesce(a.qty,0)+COALESCE(f.qty_consumer,0)+COALESCE(G.qty_adj,0) from " +\
            "(select id, collector_id,product_id,qty from collection_artical where id = "+artical_id+")A  "+ \
            "left outer join ( SELECT id,name,phone from collection_collector  )B on b.id = a.collector_id  "+ \
            "left outer join (SELECT id,name from product_product )c on a.product_id = c.id "+\
            "left outer join (SELECT artical_id,sum(qty)qty_consumer from logistics.collection_consumelog GROUP BY artical_id )f on f.artical_id =a.id "+\
	        "left outer join (SELECT artical_id,sum(qty)qty_adj from logistics.collection_adjlog GROUP BY artical_id )g on g.artical_id =a.id "
        cur.execute(s)
        r = cur.fetchone()
        if r :
            data['balance'] = r[4]
            data['collector'] = r[0]
            data['product_name'] = r[2]
            data['phone'] = r[3]
            data['qty'] = r[1]
        return render(request, 'collection/collection.html', data)
    elif obj == 'update':
        data['subtt'] = ['大罐酒', '调整']
        data['show_balance'] = False
        artical_id = str(request.GET.get('artical_id', ''))
        if request.method == "POST":
            direction = str(request.POST.get('direction'))
            qty = request.POST.get('qty')
            if direction == "-1":
                value = int(qty) * -1
            else:
                value = qty
            remark = request.POST.get('remark', '')
            s = "INSERT INTO logistics.collection_adjlog " +\
                "(direction, qty, unit, dt, tm, remark, artical_id, company_id, user_id) VALUES " +\
                "("+direction+", "+str(value)+", 0, now(), now(), '"+remark+"', "+artical_id+", "+str(u.company_id)+", "+str(u.id)+") returning  id;"
            cur.execute(s)
            if cur.fetchone():
                data['res'] = "success"
        data['artical_id'] = artical_id
        s = "select b.name,coalesce(a.qty,0),c.name,coalesce(b.phone,'')phone from (select collector_id,product_id,qty from collection_artical where id = " + artical_id + ")A  " + \
            "left outer join ( SELECT id,name,phone from collection_collector  )B on b.id = a.collector_id  " + \
            "left outer join (SELECT id,name from product_product )c on a.product_id = c.id "
        cur.execute(s)
        r = cur.fetchone()
        if r:
            data['collector'] = r[0]
            data['product_name'] = r[2]
            data['phone'] = r[3]
            data['qty'] = r[1]
        return render(request, 'collection/collection.html', data)

@csrf_exempt
def collector_action(request,what):
    data={'e':''}
    cur = connection.cursor()
    if what == 'register':
        consumer_id = request.GET.get("consumer_id",)
        if not consumer_id :
            data['e']='缺少参数'
            return JsonResponse(data)
        member = Member.objects.filter(consumer_id=consumer_id,company_id=19318)
        if member:
            collector = Collector.objects.filter(member=member[0].id,company_id=19318)
            if collector:
                data['e']='当前微信用户已存在'
                collector_id = collector[0].id
                s = " SELECT  id,qty,product_id,collector_id  from  collection_artical where company_id = 19318 and  collector_id = " + str(collector_id)
                cur.execute(s)
                i = cur.fetchone()
                if i:
                    data['artical_id'] = i[0]
                return JsonResponse(data)
            # else:
            #     m_id = member[0].id

        if request.method == 'POST':
            phone = request.POST.get("phone", )
            name = request.POST.get("name", )
            p_id = request.POST.get("product_id", )
            member = Member.objects.filter(consumer_id=consumer_id, company_id=19318)
            if member:
                m_id = member[0].id
            else:
                member = Member()
                member.company_id = 19318
                member.consumer_id = consumer_id
                member.name = name
                member.phone = phone
                member.save()
                m_id = member.id


            collector = Collector()
            collector.member_id = m_id
            collector.company_id = 19318
            collector.name = name
            collector.phone = phone
            collector.save()
            collector_id = collector.id

            artical = Artical()
            artical.name = name
            artical.collector_id = collector_id
            artical.product_id = p_id
            artical.company_id = 19318
            artical.unit = 'ml'
            artical.qty = 0
            artical.save()

            data['artical_id'] = artical.id
        # else:
        #     data['e']='尚未注册'
        return JsonResponse(data)
    elif what == 'pick':
        if request.method == 'POST':
            try:
                # r = json.loads(request.body)
                # qty = r['qty']
                # collector_id = r['collector_id']
                # artical_id = r['artical_id']
                qty = request.POST.get("qty", )
                collector_id = request.POST.get("collector_id", )
                artical_id = request.POST.get("artical_id", )
                clog = ConsumeLog()
                clog.qty = int(qty) * -1
                clog.direction = -1
                clog.artical_id = int(artical_id)
                clog.collector_id = int(collector_id)
                clog.company_id = 19318
                clog.save()
            except:
                data['e'] = '取酒失败'
        data['artical'] = []
        artical_id = request.GET.get("artical_id", )
        if not artical_id:
            data['e'] = '缺少参数'
            return JsonResponse(data)
        s = " SELECT  a.id,d.id,d.NAME,COALESCE ( a.qty, 0 ) + COALESCE ( b.qty_consumer, 0 ) + COALESCE ( c.qty_adj, 0 ),a.collector_id " +\
            " from (SELECT  id,qty,product_id,collector_id  from  collection_artical where company_id = 19318 and  id = "+str(artical_id)+")a  " +\
            " LEFT OUTER JOIN ( SELECT artical_id, SUM ( qty ) qty_consumer FROM logistics.collection_consumelog GROUP BY artical_id ) b ON b.artical_id = a.ID  " +\
            " LEFT OUTER JOIN ( SELECT artical_id, SUM ( qty ) qty_adj FROM logistics.collection_adjlog GROUP BY artical_id ) c ON c.artical_id = a.ID " +\
            " LEFT OUTER JOIN (SELECT ID, NAME FROM logistics.product_product )d on a.product_id = d.ID"
        cur.execute(s)
        i= cur.fetchone()
        if i:
            data['artical']={'artical_id':i[0],'p_id':i[1],'p_name':i[2],'qty':i[3],'collector_id':i[4]}
        else:
            data['e']='尚未查询到用酒信息'
        return JsonResponse(data)
    elif what == 'logs':
        data['data']=[]
        artical_id = request.GET.get('artical_id')
        s = "select * from (SELECT a.direction,coalesce(a.qty,0)qty,a.tm,b.last_name,coalesce(a.remark,'')remark from (SELECT " + \
            "case direction when 1 then '增加' when -1 then '减少' else '不变' end direction ,abs(qty)qty," + \
            "TO_CHAR(tm, 'yyyy-mm-dd HH24:MI:SS')tm,user_id,remark from collection_adjlog where artical_id = " + artical_id + ")A " + \
            "left outer join (SELECT id,last_name from  company_user )b on a.user_id = b.id " + \
            "UNION " + \
            "SELECT a.direction,a.qty,a.tm,b.name,coalesce(a.remark,'')remark  from(SELECT   " + \
            "case direction when 1 then '新增' when -1 then '使用' else '不变' end direction ,abs(qty)qty, " + \
            "TO_CHAR(tm, 'yyyy-mm-dd HH24:MI:SS')tm,collector_id,remark from collection_consumelog WHERE artical_id = " + artical_id + ")A " + \
            "left outer join (SELECT id,name from  collection_collector )b on a.collector_id = b.id)p order by p.tm desc "
        cur.execute(s)
        r = cur.fetchall()
        for i in r:
            data['data'].append([i[0], i[1], i[2]])
        s = " SELECT  a.id,d.id,d.NAME,COALESCE ( a.qty, 0 ) + COALESCE ( b.qty_consumer, 0 ) + COALESCE ( c.qty_adj, 0 ),a.collector_id " + \
            " from (SELECT  id,qty,product_id,collector_id  from  collection_artical where company_id = 19318 and  id = " + str(artical_id) + ")a  " + \
            " LEFT OUTER JOIN ( SELECT artical_id, SUM ( qty ) qty_consumer FROM logistics.collection_consumelog GROUP BY artical_id ) b ON b.artical_id = a.ID  " + \
            " LEFT OUTER JOIN ( SELECT artical_id, SUM ( qty ) qty_adj FROM logistics.collection_adjlog GROUP BY artical_id ) c ON c.artical_id = a.ID " + \
            " LEFT OUTER JOIN (SELECT ID, NAME FROM logistics.product_product )d on a.product_id = d.ID"
        cur.execute(s)
        i = cur.fetchone()
        if i:
            data['artical'] = {'artical_id': i[0], 'p_id': i[1], 'p_name': i[2], 'qty': i[3], 'collector_id': i[4]}
        else:
            data['e'] = '尚未查询到用酒信息'

        return JsonResponse(data)


