wanzb_v3_api/src/manage/controls.py

#coding=utf-8
'''
'''
import os,re,random
import json,time,datetime
import shutil
import tempfile
import zipfile
from docxtpl import DocxTemplate,InlineImage
from docx.shared import Mm
from django.db import transaction
from django.conf import settings
import common.models as cm
import common.error_info as ce
import common.common_functions as ccf
import common.common_control as ccc
import account.password_handle as ph
from utils.exceltool import ExcelTool
from utils.jgpush import send_notification_by_registration_ids
import wzhifuSDK as wxpay
from utils.exceltool import ExcelTool
from utils.qrcodetool import gen_general_qrcode
from django.db.models import Q,Sum,Count
from PIL import Image
from PIL import Image,ImageDraw,ImageFont
from threading import Thread
#from django.core.cache import cache
from django.db import connection
from utils.wxSubscribeMessage import send_consult_reply_message
from utils.aliyunpush import aliyunpush
from tools.article_spider import startSpider
from utils.plottool import plot_table

import xlrd
import xlwt
from xlutils.copy import copy
from xltpl.writer import BookWriter
from weixin.wzhifuSDK_V3 import refund_order

@ccc.cache_data()
def get_user_info(uid):
    user = cm.UserInfo.objects.filter(id=uid).values().first()
    if user:
        user["style"] = []
        if user["zq"]:
            user["style"].append(user["zq"])
        if user["cw"]:
            user["style"].append(user["cw"])
        if user["df"]:
            user["style"].append(user["df"])
    return user

@ccc.cache_data()
def get_match_info(match_id):
    match = cm.Match.objects.filter(id=match_id).values().first()
    return match

def async(f):
    def wrapper(*args, **kwargs):
        thr = Thread(target=f, args=args, kwargs=kwargs)
        thr.start()
    return wrapper


def add_model(cls,**kwargs):
    """
    """
    model_name = re.search(r'.*\.(\w+)View',str(cls.__class__)).groups()[0]
    model = getattr(cm,model_name)
    if model_name == "UserInfo":
        username = kwargs.get("username")
        usercode = kwargs.get("usercode")
        phone = kwargs.get("phone")
        if cm.UserInfo.objects.filter(username=username).exists():
            raise ce.TipException(u"用户名重复!")
        if cm.UserInfo.objects.filter(usercode=usercode).exists():
            raise ce.TipException(u"用户代码重复!")
        if cm.UserInfo.objects.filter(phone=phone).exists():
            raise ce.TipException(u"手机号重复!")
    if model_name == "Match":
        name = kwargs.get("name")
        if cm.Match.objects.filter(name=name).exists():
            raise ce.TipException(u"比赛名称重复!")
    if model_name == "MatchGoup":
        name = kwargs.get("name")
        match_id = kwargs.get("match_id")
        if cm.MatchGroup.objects.filter(name=name,match_id=match_id).exists():
            raise ce.TipException(u"分组名称重复!")

    if model_name == "Player":
        user_id = kwargs.get("user_id")
        match_id = kwargs.get("match_id")
        if cm.Player.objects.filter(user_id=user_id,match_id=match_id).exists():
            raise ce.TipException(u"重复添加选手!")
        user =cm.UserInfo.objects.filter(id=user_id).first()
        username = user.username if user else ""
        usercode = user.usercode if user else ""
        kwargs["username"] = username
        kwargs["usercode"] = usercode

    if model_name == "PlayerRecord":
        #return None
        match_id = kwargs.get("match_id")
        stock_date = kwargs.get("stock_date")
        today_stock = json.dumps(kwargs.get("today_stock"))
        today_stock_img = json.dumps(kwargs.get("today_stock_img"))
        player_id = kwargs.get("player_id")
        today_fund = float(kwargs.get("today_fund"))
        is_markt = int(kwargs.get("is_markt",0))

        player = cm.Player.objects.filter(id=player_id).first()
        user_id = player.user_id
        init_fund = player.fund
        user = cm.UserInfo.objects.filter(id=user_id).first()
        username = user.username
        usercode = user.usercode
        match_group = player.match_group
        match = cm.Match.objects.filter(id=match_id).first()
        if player.match_status < 1:
            raise ce.TipException(u"该账号已暂停/退出比赛,如有疑问请联系管理员获取详情信息!")
        now_date = datetime.datetime.now().strftime("%Y-%m-%d")
        if match.end_time < now_date:
            raise ce.TipException(u"该比赛已结束!")

        #yesterday_date = (datetime.datetime.now()-datetime.timedelta(days=1)).strftime("%Y-%m-%d")
        yesterday = cm.PlayerRecord.get_db_model(match_id).objects.filter(
                        match_id=match_id,player_id=player_id).order_by("-stock_date").first()
        if yesterday:
            yesterday_fund = yesterday.today_fund
            yesterday_stock = yesterday.today_stock
            yesterday_stock_img = yesterday.today_stock_img
            yesterday_is_markt = yesterday.is_markt
        else:
            yesterday_fund = init_fund 
            yesterday_stock = ""
            yesterday_stock_img = "" 
            yesterday_is_markt = 0
        obj,flag = cm.PlayerRecord.get_db_model(match_id).objects.get_or_create(
                        player_id=player_id,
                        match_id=match_id,
                        stock_date=stock_date)
        obj.init_fund = init_fund
        obj.yesterday_fund = yesterday_fund
        obj.yesterday_stock = yesterday_stock
        obj.yesterday_stock_img = yesterday_stock_img
        obj.today_fund = today_fund
        obj.today_stock = today_stock
        obj.today_stock_img = today_stock_img
        obj.user_id = user_id
        obj.username = username
        obj.usercode = usercode
        obj.match_group = match_group
        obj.is_markt = is_markt
        obj.yesterday_is_markt = yesterday_is_markt
        #计算今日和昨日盈亏
        today_income = (today_fund - yesterday_fund)/float(yesterday_fund) if yesterday_fund else 0.0
        total_income = (today_fund - init_fund)/float(init_fund) if init_fund else 0.0
        obj.today_income = round(today_income,4)
        obj.total_income = round(total_income,4)
        if not flag:
            obj.ctime = datetime.datetime.now()
        obj.save()

        #更新group_rank

        return obj.id
    if model_name == "PlayerRecord":
        match_id = kwargs.get("match_id")
        obj = model.get_db_model(match_id).objects.create(**kwargs)
    else:
        obj = model.objects.create(**kwargs)
    #
    if model_name == "Match":
        cm.MatchGroup.objects.create(
            match_id = obj.id, 
            name = obj.name+u"游客组", 
            is_active = 0,
            charge = 0
        )

    return obj.id

def update_model(cls,**kwargs):
    """
    """
    model_name = re.search(r'.*\.(\w+)View',str(cls.__class__)).groups()[0]
    model = getattr(cm,model_name)
    id = kwargs.pop("id")

    if model_name == "UserInfo":
        username = kwargs.get("username")
        usercode = kwargs.get("usercode")
        phone = kwargs.get("phone")
        if cm.UserInfo.objects.exclude(id=id).filter(username=username).exists():
            raise ce.TipException(u"用户名重复!")
        if cm.UserInfo.objects.exclude(id=id).filter(usercode=usercode).exists():
            raise ce.TipException(u"用户代码重复!")
        if phone and cm.UserInfo.objects.exclude(id=id).filter(phone=phone).exists():
            raise ce.TipException(u"手机号重复!")
    if model_name == "Player":
        user_id = kwargs.get("user_id")
        match_id = kwargs.get("match_id")
        match_group = kwargs.get("match_group")
        if cm.Player.objects.exclude(id=id).filter(user_id=user_id,match_id=match_id).exists():
            raise ce.TipException(u"重复添加选手!")
        user =cm.UserInfo.objects.filter(id=user_id).first()
        username = user.username if user else ""
        usercode = user.usercode if user else ""
        try:
            match_group_name = cm.MatchGroup.objects.filter(id=match_group).first().name
        except Exception as e:
            match_group_name = ""
        kwargs["match_group_name"] = match_group_name

    if model_name == "PlayerRecord":
        is_markt = int(kwargs.get("is_markt",0))
        kwargs["today_stock"] = json.dumps(kwargs.get("today_stock"))
        kwargs["today_stock_img"] = json.dumps(kwargs.get("today_stock_img"))

    if model_name == "PlayerRecord":
        match_id = kwargs.get("match_id")
        rst = model.get_db_model(match_id).objects.filter(id=id).update(**kwargs)
    else:
        rst = model.objects.filter(id=id).update(**kwargs)

    if model_name == "PlayerRecord":
        match_id = kwargs.get("match_id")
        user_id = kwargs.get("user_id")
        obj = cm.PlayerRecord.get_db_model(match_id).objects.filter(id=id).first()
        today_fund = obj.today_fund
        yesterday_fund = obj.yesterday_fund
        init_fund = obj.init_fund
        today_income = (today_fund - yesterday_fund)/float(yesterday_fund)
        total_income = (today_fund - init_fund)/float(init_fund)
        obj.today_income = round(today_income,4)
        obj.total_income = round(total_income,4)
        #
        player = cm.Player.objects.filter(user_id=user_id,match_id=match_id).first()
        if player:
            obj.match_group = player.match_group
            obj.username = player.username
            obj.usercode = player.usercode

        #记录持股情况
        stock_date = obj.stock_date
        today_stock = obj.today_stock
        new_stock_list = []
        today_stock_list = json.loads(today_stock)
        for ts in today_stock_list:
            if ts["name"]:
                try:
                    stock,flag = cm.Stock.objects.get_or_create(
                        name = ts["name"] 
                    )
                    stock_id = stock.id
                    usobj,flag = cm.UserStock.objects.get_or_create(
                        user_id = user_id, 
                        stock_id = stock_id,
                        stock_name = ts["name"],
                        player_id = player.id,
                        stock_date = stock_date
                    )
                    if ts.get("fund"):
                        usobj.fund = ts["fund"]
                    usobj.save()
                    ts["stock_id"] = stock_id
                    new_stock_list.append(ts)
                except Exception as e:
                    print(e)
        today_stock = json.dumps(new_stock_list)
        obj.today_stock = today_stock

        obj.save()

        #更新player的init_fund
        player_id = player.id
        cm.Player.objects.filter(id=player_id).update(fund=init_fund)

        ##更新group_rank
        update_group_rank(obj.stock_date,obj.match_group,obj.match_id)


    if model_name == "UserInfo":
        ccc.del_cache("cdata_get_user_info_(%s,)"%id)

    return rst


def delete_model(cls,**kwargs):
    """
    """
    model_name = re.search(r'.*\.(\w+)View',str(cls.__class__)).groups()[0]
    model = getattr(cm,model_name)
    ids = str(kwargs.get("id")).split(",")
    if model_name == "WanzhuConsult":
        cm.WanzhuConsult.objects.filter(Q(user_id__in=ids)|Q(reply_user_id__in=ids)).delete()
    else:
        if model_name == "PlayerRecord":
            match_id = kwargs.get("match_id")
            obj = cm.PlayerRecord.get_db_model(match_id).objects.filter(id__in=ids).first()
            ##更新group_rank
            update_group_rank(obj.stock_date,obj.match_group,obj.match_id)
            rst = model.get_db_model(match_id).objects.filter(id__in=ids).delete()
        else:
            rst = model.objects.filter(id__in=ids).delete()
        if model_name == "UserInfo":
            cm.Player.objects.filter(user_id__in=ids).delete()
            cm.PlayerRecord.objects.filter(user_id__in=ids).delete()
        if model_name == "Player":
            cm.PlayerRecord.objects.filter(player_id__in=ids).delete()
        if model_name == "Match":
            cm.PlayerRecord.objects.filter(match_id__in=ids).delete()
            cm.Player.objects.filter(match_id__in=ids).delete()
            cm.MatchGroup.objects.filter(match_id__in=ids).delete()
        if model_name == "MatchGroup":
            cm.PlayerRecord.objects.filter(match_group__in=ids).delete()
            cm.Player.objects.filter(match_group__in=ids).delete()
        if model_name == "BaikeCategory":
            cm.BaikeDetail.objects.filter(category_id__in=ids).delete()

    return ids


def get_search_list(cls,**kwargs):
    """
    """
    model_name = re.search(r'.*\.(\w+)SearchView',str(cls.__class__)).groups()[0]
    model = getattr(cm,model_name)
    qset = model.objects.all().order_by("-id")
    if model_name == "Player":
        if kwargs.get("usercode"):
            qset = qset.filter(Q(usercode__icontains=kwargs.get("usercode"))|Q(username__icontains=kwargs.get("usercode"))|Q(phone__icontains=kwargs.get("usercode")))
            data = list(qset.values("id","user_id","username","usercode"))
            for item in data:
                item["label"] = "%s(%s)" % (item["username"],item["usercode"])
        else:
            data = []
    elif model_name == "Stock":
        if kwargs.get("name"):
            qset = qset.filter(Q(name__icontains=kwargs.get("name"))|Q(code__icontains=kwargs.get("name")))
            data = list(qset.values("id","name","code"))
            for item in data:
                item["label"] = "%s(%s)" % (item["name"],item["code"])
        else:
            data = []
    elif model_name == "UserInfo":
        if kwargs.get("name"):
            qset = qset.filter(username__icontains=kwargs.get("name"))
            data = list(qset.values("id","username","usercode","avatar"))
            for item in data:
                item["label"] = "%s(%s)" % (item["username"],item["usercode"])
        else:
            data = []
    elif model_name == "MatchGroup":
        if kwargs.get("match_id"):
            qset = qset.filter(match_id__icontains=kwargs.get("match_id"))

        if kwargs.get("is_active"):
            qset = qset.filter(is_active__icontains=kwargs.get("is_active"))
            
        data = list(qset.values("id","name"))
        

    else:
        if kwargs.get("name"):
            qset = qset.filter(name__icontains=kwargs.get("name"))
        data = list(qset.values("id","name"))
    return data

def get_detail_info(cls,**kwargs):
    """
    """
    model_name = re.search(r'.*\.(\w+)View',str(cls.__class__)).groups()[0]
    model = getattr(cm,model_name)
    id = kwargs.get("id")
    if model_name == "PlayerRecord":
        match_id = kwargs.get("match_id")
        rst = list(model.get_db_model(match_id).objects.filter(id=id).values())
    else:
        rst = list(model.objects.filter(id=id).values())
    rst = rst[0] if rst else {}
    if model_name == "Player":
        user = get_user_info(rst["user_id"])
        match = get_match_info(rst["match_id"])

        rst["username"] = user["username"]
        rst["usercode"] = user["usercode"]
        rst["match_name"] = match["name"] if match else ""
        rst["match_group"] = int(rst["match_group"]) if rst["match_group"] else 0
    if model_name == "PlayerRecord":
        rst["today_stock"] = json.loads(rst["today_stock"]) if rst["today_stock"] else []
        rst["today_stock_img"] = json.loads(rst["today_stock_img"]) if rst["today_stock_img"] else []
        rst["yesterday_stock"] = json.loads(rst["yesterday_stock"]) if rst["yesterday_stock"] else []
        rst["yesterday_stock_img"] = json.loads(rst["yesterday_stock_img"]) if rst["yesterday_stock_img"] else []
        rst["is_markt"] = bool(rst["is_markt"]) 
    return rst


def get_group_income(match_id,match_group,stock_date=None):
    """
    """
    data = []
    if stock_date:
        today = stock_date
    else:
        today = datetime.datetime.now().strftime("%Y-%m-%d")
    player_ids = list(cm.Player.objects.filter(match_id=match_id,match_group=match_group).values_list("id",flat=True))
    for player_id in player_ids:
        today_record = cm.PlayerRecord.objects.filter(match_id=match_id,match_group=match_group,player_id=player_id,stock_date=today).order_by("-stock_date").first()
        if today_record:
            data.append(today_record.total_income)
    data = list(set(data))
    data = sorted(data,key=lambda x:x,reverse=True)
    return data

def get_list_info(cls,**kwargs):
    """
    """
    model_name = re.search(r'.*\.(\w+)ListView',str(cls.__class__)).groups()[0]
    model = getattr(cm,model_name)
    if model_name == "PlayerRecord":
        match_id = kwargs.get("match_id",0)
        qset = model.get_db_model(match_id).objects.all()
    else:
        qset = model.objects.all()
    if kwargs.get("name"):
        qset = qset.filter(name__icontains=kwargs.get("name"))
    if model_name == "UserInfo":
        if kwargs.get("username"):
            qset = qset.filter(Q(username__icontains=kwargs.get("username"))|Q(usercode__icontains=kwargs.get("username"))|Q(phone__icontains=kwargs.get("username")))
    if model_name == "Player":
        if kwargs.get("username"):
            qset = qset.filter(Q(username__icontains=kwargs.get("username"))|Q(usercode__icontains=kwargs.get("username")))
        if kwargs.get("match_id"):
            qset = qset.filter(match_id=kwargs.get("match_id"))
        if kwargs.get("groupId"):
            qset = qset.filter(match_group=kwargs.get("groupId"))
    if model_name == "MatchGroup":
        if kwargs.get("match_id"):
            qset = qset.filter(match_id=kwargs.get("match_id"))
    if model_name == "PlayerRecord":
        if kwargs.get("match_id"):
            qset = qset.filter(match_id=kwargs.get("match_id"))
        if kwargs.get("groupId"):
            qset = qset.filter(match_group=kwargs.get("groupId"))
        if kwargs.get("username"):
            qset = qset.filter(Q(username__icontains=kwargs.get("username"))|Q(usercode__icontains=kwargs.get("username")))
        if kwargs.get("stock_date"):
            qset = qset.filter(stock_date=kwargs.get("stock_date"))
        if kwargs.get("auto_complete"):
            if int(kwargs.get("auto_complete"))==1:
                qset = qset.filter(auto_complete__gte=kwargs.get("auto_complete"))
            else:
                qset = qset.filter(auto_complete=kwargs.get("auto_complete"))
    if model_name == "SignupOrder":
        if kwargs.get("match_id"):
            qset = qset.filter(match_id=kwargs.get("match_id"))
        if kwargs.get("username"):
            qset = qset.filter(Q(user_name__icontains=kwargs.get("username"))|Q(phone__icontains=kwargs.get("username")))
        if kwargs.get("signup_type"):
            qset = qset.filter(signup_type=kwargs.get("signup_type"))
        if kwargs.get("order_status") or kwargs.get("order_status")==0:
            qset = qset.filter(order_status=kwargs.get("order_status"))
        if kwargs.get("pay_status") or kwargs.get("pay_status")==0:
            qset = qset.filter(pay_status=kwargs.get("pay_status"))

    if model_name == "Article":
        if kwargs.get("type"):
            qset = qset.filter(type=kwargs.get("type"))
        if kwargs.get("category"):
            qset = qset.filter(category=kwargs.get("category"))
        if kwargs.get("isbanner"):
            qset = qset.filter(isbanner=kwargs.get("isbanner"))
    if model_name == "Stock":
        if kwargs.get("username"):
            qset = qset.filter(name__icontains=kwargs.get("username"))

    if model_name == "BaikeDetail":
        if kwargs.get("name"):
            qset = qset.filter(name__icontains=kwargs.get("name"))
        if kwargs.get("status"):
            qset = qset.filter(status=kwargs.get("status"))
        if kwargs.get("category_id"):
            qset = qset.filter(category_id=kwargs.get("category_id"))

    if model_name == "PlayerRecord":
    	if kwargs.get("username"):
        	data = list(qset.order_by("-stock_date").values())
        else:
        	data = list(qset.order_by("-total_income").values())
    elif model_name == "Player":
        data = list(qset.order_by("-fund").values())
        
    else:
        data = list(qset.order_by("-id").values())
    page = int(kwargs.get("page",1))
    page_size = int(kwargs.get("page_size",20))
    if page and page_size:
        total,data = ccf.get_page_list(data,page,page_size)
        if model_name == "Player":
            for item in data:
                user = get_user_info(item["user_id"])
                if user:
                    match = get_match_info(item["match_id"])
                    item["nickname"] = user["nickname"]
                    item["match_name"] = match["name"] if match else ""
                    item["openid"] = user["openid"]
                    item["player_type"] = user["player_type"]
                    item["phone"] = user["phone"]
        if model_name == "PlayerRecord":
            for item in data:
                user = cm.UserInfo.objects.filter(id=item["user_id"]).first()

                item["username"] = user.username if user else ""
                item["usercode"] = user.usercode if user else ""

                today_stock = json.loads(item["today_stock"]) if item["today_stock"] else []
                today_stock = filter(lambda x:x["name"],today_stock if today_stock else [])
                today_stock_img = json.loads(item["today_stock_img"]) if item["today_stock_img"] else []
                item["today_stock"] = today_stock
                
                ilist = []
                for img in today_stock_img:
                    nlist = img.split('?')
                    if len(nlist) > 0:
                        ilist.append(nlist[0])

            
                item["today_stock_img"] = ilist


                yesterday_stock = json.loads(item["yesterday_stock"]) if item["yesterday_stock"] else []
                yesterday_stock = filter(lambda x:x["name"],yesterday_stock if yesterday_stock else [])

                item["yesterday_stock"] = yesterday_stock
                item["yesterday_stock_img"] = json.loads(item["yesterday_stock_img"]) if item["yesterday_stock_img"] else []
                if item["today_income"]:
                    item["today_income"] = "{}%".format(item["today_income"]*100)
                if item["total_income"]:
                    item["total_income"] = "{}%".format(item["total_income"]*100)
                item["is_markt"] = bool(item["is_markt"]) 
                #资金出入
                if cm.FundInOut.objects.filter(user_id=item["user_id"],stock_date=item["stock_date"]).exists():
                    item["fund_inout"] = cm.FundInOut.objects.filter(user_id=item["user_id"],stock_date=item["stock_date"]).values().first()
            if kwargs.get("groupId"):
                data = sorted(data,key=lambda x:x["group_rank"])
        if model_name == "Match":
            for item in data:
                groups = ",".join(list(cm.MatchGroup.objects.filter(match_id=item["id"]).values_list("name",flat=True)))
                item["join_count"] = cm.Player.objects.filter(match_id=item["id"]).count()
                item["out_count"] = cm.Player.objects.filter(match_id=item["id"],match_status=-1).count()
                item["groups"] = groups
        if model_name == "MatchGroup":
            for item in data:
                match = cm.Match.objects.filter(id=item["match_id"]).first()
                match_name = match.name if match else ""
                item["join_count"] = cm.Player.objects.filter(match_group=item["id"]).count()
                item["out_count"] = cm.Player.objects.filter(match_group=item["id"],match_status=-1).count()
                item["match_name"] = match_name
        if model_name == "SignupOrder":
            for item in data:
                user = cm.UserInfo.objects.filter(id=item["user_id"]).first()
                try:
                    item["match_group_name"] = cm.MatchGroup.objects.filter(id=item["match_group"]).first().name
                except Exception as e:
                    print(e)
                    item["match_group_name"] = ""
                if user:
                    item["usercode"] = user.usercode
                    item["nickname"] = user.nickname
                    item["openid"] = user.openid

        if model_name == "FundInOut":
            for item in data:
                user = cm.UserInfo.objects.filter(id=item["user_id"]).first()

                item["username"] = user.username
                item["usercode"] = user.usercode

            if kwargs.get("username"):
                data = filter(lambda x:x["username"] == str(kwargs.get("username")) or kwargs.get("username") ==x["usercode"],data)

        return (total,data)
    else:
        return len(data),data

    return rst


def download_records(request):
    """
    """
    kwargs = request.json
    header = [u"NO.",u"选手",u"初始资产",u"今日资产",u"今日盈亏",u"总盈亏",u"昨日持股\n(万元)",u"今日持股\n(万元)"]

    match_id = kwargs.get("match_id") 
    qset = cm.PlayerRecord.get_db_model(match_id).objects.all()

    if kwargs.get("match_id"):
        qset = qset.filter(match_id=kwargs.get("match_id"))
    if kwargs.get("groupId"):
        qset = qset.filter(match_group=kwargs.get("groupId"))
    if kwargs.get("username"):
        qset = qset.filter(Q(username__icontains=kwargs.get("username"))|Q(usercode__icontains=kwargs.get("username")))
    if kwargs.get("stock_date"):
        qset = qset.filter(stock_date=kwargs.get("stock_date"))

    qdata = list(qset.order_by("-total_income").values())

    if kwargs.get("groupId"):
        qdata = sorted(qdata,key=lambda x:x["group_rank"])

    data = []
    for item in qdata:
        try:
            user = cm.UserInfo.objects.filter(id=item["user_id"]).first()
            item["username"] = user.username
            item["usercode"] = user.usercode
            item["today_income"] = "{}%".format(item["today_income"]*100)
            item["total_income"] = "{}%".format(item["total_income"]*100)
            try:
                match_group_name = cm.MatchGroup.objects.filter(id=item["match_group"]).first().name
            except:
                match_group_name = ""
            item["match_group_name"] = match_group_name
            item["today_stock_imgs"] = json.loads(item["today_stock_img"]) if item["today_stock_img"] else []

            today_stock = json.loads(item["today_stock"]) if item["today_stock"] else []
            today_stock = list(filter(lambda x:x["name"],today_stock if today_stock else []))
            today_stock = ",".join([x["name"]+str(x["fund"]) for x in today_stock]) if today_stock else ""

            yesterday_stock = json.loads(item["yesterday_stock"]) if item["yesterday_stock"] else []
            yesterday_stock = list(filter(lambda x:x["name"],yesterday_stock if yesterday_stock else []))
            yesterday_stock = ",".join([x["name"]+str(x["fund"]) for x in yesterday_stock]) if yesterday_stock else ""

            #空仓、开超市、请假判断
            if item["auto_complete"] > 0:
                today_stock = u"请假"
            else:
                if item["is_markt"] == 1:
                    today_stock = u"开超市"
                else:
                    if today_stock == "":
                        today_stock = u"空仓"
            #昨日
            if item["yesterday_auto_complete"] > 0:
                yesterday_stock = u"请假"
            else:
                if item["yesterday_is_markt"] == 1:
                    yesterday_stock = u"开超市"
                else:
                    if yesterday_stock == "":
                        yesterday_stock = u"空仓"

            data.append(
                    [
                        item["group_rank"],
                        item["username"],
                        item["init_fund"],
                        item["today_fund"],
                        item["today_income"],
                        item["total_income"],
                        yesterday_stock,
                        today_stock,
                        ''
                    ])
        except Exception as e:
            print(e)
    xlsfile = os.path.join(settings.BASE_DIR,"demo.xls")
    exceltool = ExcelTool(xlsfile)
    
    
    groupName = kwargs.get("groupName")
    if not groupName:
        groupName = u"全部数据"

    xlsdata = exceltool.save_data(groupName,header,data)

    return xlsdata


def download_records_image(request):
    """
    return: filename: str; binary data of the image: bytes.
    """
    kwargs = request.json
    match_id = kwargs.get("match_id") 
    if not match_id:
        return None

    qset = cm.PlayerRecord.get_db_model(match_id).objects.all()
    match = cm.Match.objects.filter(id=match_id).values('name').first()
    if match is None:
        return None
    match_name = match['name']

    if kwargs.get("match_id"):
        qset = qset.filter(match_id=kwargs.get("match_id"))
    if kwargs.get("groupId"):
        qset = qset.filter(match_group=kwargs.get("groupId"))
        match_group = cm.MatchGroup.objects.filter(id=kwargs.get('groupId')).values('name').first()
        if match_group is None:
            match_group_name = ''
        else:
            match_group_name = match_group['name']
    else:
        match_group_name = ''
    if kwargs.get("username"):
        qset = qset.filter(Q(username__icontains=kwargs.get("username"))|Q(usercode__icontains=kwargs.get("username")))
    if kwargs.get("stock_date"):
        qset = qset.filter(stock_date=kwargs.get("stock_date"))
        if match_group_name:
            title = u'{}-{}{}'.format(match_name, match_group_name, kwargs.get("stock_date")[5:])
        else:
            title = u'{}-{}'.format(match_name, kwargs.get("stock_date")[5:])
    else:
        if match_group_name:
            title = u'{}-{}'.format(match_name, match_group_name)
        else:
            title = match_name
    filename = u'{}.png'.format(title)

    if match_group_name in (u'百万组', u'十万组', u'菜鸟组') or len(match_group_name) > 3 or match_group_name == '':
        qdata = list(qset.order_by("-total_income").values())
    else:
        qdata = list(qset.order_by("-total_income").values())

    if kwargs.get("groupId"):
        qdata = sorted(qdata,key=lambda x:x["group_rank"])
    
    data = []
    for item in qdata:
        try:
            user = cm.UserInfo.objects.filter(id=item["user_id"]).first()
            item["username"] = user.username
            item["usercode"] = user.usercode
            item["today_income"] = "{}%".format(item["today_income"]*100)
            item["total_income"] = "{}%".format(item["total_income"]*100)
            try:
                match_group_name = cm.MatchGroup.objects.filter(id=item["match_group"]).first().name
            except:
                match_group_name = ""
            item["match_group_name"] = match_group_name
            item["today_stock_imgs"] = json.loads(item["today_stock_img"]) if item["today_stock_img"] else []

            today_stock = json.loads(item["today_stock"]) if item["today_stock"] else []
            today_stock = list(filter(lambda x:x["name"],today_stock if today_stock else []))
            today_stock = ",".join([x["name"]+str(x["fund"]) for x in today_stock]) if today_stock else ""

            yesterday_stock = json.loads(item["yesterday_stock"]) if item["yesterday_stock"] else []
            yesterday_stock = list(filter(lambda x:x["name"],yesterday_stock if yesterday_stock else []))
            yesterday_stock = ",".join([x["name"]+str(x["fund"]) for x in yesterday_stock]) if yesterday_stock else ""

            #空仓、开超市、请假判断
            if item["auto_complete"] > 0:
                today_stock = u"请假"
            else:
                if item["is_markt"] == 1:
                    today_stock = u"开超市"
                else:
                    if today_stock == "":
                        today_stock = u"空仓"
            #昨日
            if item["yesterday_auto_complete"] > 0:
                yesterday_stock = u"请假"
            else:
                if item["yesterday_is_markt"] == 1:
                    yesterday_stock = u"开超市"
                else:
                    if yesterday_stock == "":
                        yesterday_stock = u"空仓"

            data.append([item["group_rank"],
                         item["username"],
                         item["init_fund"],
                         item["today_fund"],
                         item["today_income"],
                         item["total_income"],
                         yesterday_stock,
                         today_stock])
        except Exception as e:
            print(e)

    nrows = len(data)
    col_headers = [u"NO.", u"选手", u"初始资产", u"今日资产",
                   u"今日盈亏", u"总盈亏", u"昨日持股\n(万元)", u"今日持股\n(万元)"]
    ncols = len(col_headers)

    image_url = plot_table(
        (nrows, ncols),
        data,
        title,
        col_headers,
        'player_record')
    return filename, image_url


def fast_save_player(**kwargs):
    """
    """
    username = kwargs.get("username")
    usercode = kwargs.get("usercode")
    if cm.UserInfo.objects.filter(username=username).exists():
        raise ce.TipException(u"用户名重复!")
    if cm.UserInfo.objects.filter(usercode=usercode).exists():
        raise ce.TipException(u"用户代码重复!")
    user = cm.UserInfo.objects.create(username=username,usercode=usercode)
    #新建选手
    user_id = user.id
    kwargs["user_id"] = user_id
    cm.Player.objects.create(**kwargs)


def update_player_latest(record):
    """更新选手最后一次数据
    """
    user_id = record["user_id"]
    match_id = record["match_id"]
    stock_date = record["stock_date"]
    match_group = record["match_group"]
    wincnt = record["wincnt"]
    total = record["totalcnt"]

    key = "PLAYER_LATEST_{}".format(user_id)
    if record:
        ccc.cache.hset(key,"stock_date",stock_date)
        ccc.cache.hset(key,"match_id",match_id)
        ccc.cache.hset(key,"match_group",match_group)

        #更新胜率
        qset = cm.PlayerRecord.get_db_model(match_id).objects.filter(match_id=match_id,user_id=user_id)
        #win_rate = qset.filter(today_income__gte=0).count()/float(qset.count()) if qset else 0.0
        win_rate = wincnt/float(total) if total else 0.0
        win_rate = round(win_rate,3)
        ccc.cache.hset(key,"win_rate",win_rate)

    #badest = cm.PlayerRecord.get_db_model(match_id).objects.filter(user_id=user_id,match_id=match_id).order_by("today_income").first()
    #if badest:
    #    ccc.cache.hset(key,"badest_income",badest.today_income)


def update_group_rank(stock_date=None,match_id=None,group_id=None):
    """
    """
    if stock_date:
        stock_date = stock_date
    else:
        stock_date = datetime.datetime.now().strftime("%Y-%m-%d")

    del_keys = ccc.cache.keys("*_%s_%s_%s" % (match_id,group_id,stock_date))
    if del_keys:
        ccc.cache.delete(*del_keys)

    groups = cm.MatchGroup.objects.filter(id=group_id)
    for gp in groups:
        match_id = gp.match_id
        match_group = gp.id
        prset = cm.PlayerRecord.get_db_model(match_id).objects.filter(match_id=match_id,match_group=match_group,stock_date=stock_date).order_by("-total_income")
        if prset:
            records = prset.values()
            case_id = " case id "
            cases = []
            where = []
            for index,pr in enumerate(records):
                case = "WHEN %s THEN %s" % (pr["id"],index+1)
                cases.append(case)
                where.append(str(pr["id"]))
                #
                key = "%s_%s_%s_%s" % (pr["user_id"],match_id,match_group,stock_date)
                pr.update({"group_rank":index+1})
                ccc.pl.set(key,json.dumps(pr,cls=ccc.CusJSONEncoder))

            ccc.pl.execute()

            winset = cm.PlayerRecord.get_db_model(match_id).objects.filter(
                match_id=match_id,match_group=match_group,today_income__gte=0)\
                .values("user_id").annotate(wincnt=Count("user_id")).values("user_id","match_id","match_group","wincnt")

            totalset = cm.PlayerRecord.get_db_model(match_id).objects.filter(
                match_id=match_id,match_group=match_group)\
                .values("user_id").annotate(totalcnt=Count("user_id")).values("user_id","totalcnt")

            totallist = list(totalset)
            for item in winset:
                item["stock_date"] = stock_date
                user_id = item["user_id"]
                total = filter(lambda x:x["user_id"]==user_id,totallist)
                if total:
                    item.update(total[0])
                else:
                    item.update({"totalcnt":0})

                if total:
                    #更新最后一次数据
                    update_player_latest(item)




            if cases and where:
                case = case_id + " ".join(cases)
                where = ",".join(where)
                sql = "update player_record_%s set group_rank = %s ELSE 0 END where id in (%s)" % (match_id,case,where)
                cursor = connection.cursor()
                cursor.execute(sql)
                cursor.close()

            ##更新最后一次数据
            #update_player_latest(pr)


def update_comment(**kwargs):
    """
    """
    id = kwargs.get("id")
    match_id = kwargs.get("match_id")
    wanzhu_comment = kwargs.get("wanzhu_comment")
    cm.PlayerRecord.get_db_model(match_id).objects.filter(id=id).update(wanzhu_comment=wanzhu_comment)


def reset_initfund(**kwargs):
    """
    """
    match_id = kwargs.get("match_id")
    cm.Player.objects.filter(match_id=match_id).update(fund=1)
    #cm.Player.objects.filter(match_id=match_id,id=3011).update(fund=None)

def get_stock_comments_list(**kwargs):
    """
    """
    qset = cm.Comments.objects.filter(stock_id__isnull=False).order_by(*["-istop","-id"])
    if kwargs.get("name"):
        qset = qset.filter(stock_name__icontains=kwargs.get("name"))
    data = list(qset.values())
    for item in data:
        item["stock_name"] = cm.Stock.objects.filter(id=item["stock_id"]).first().name
    #分页
    page = int(kwargs.get("page",1))
    page_size = int(kwargs.get("page_size",20))
    if page and page_size:
        total,data = ccf.get_page_list(data,page,page_size)
    else:
        total = len(data)
    return total,data


def get_record_comments_list(**kwargs):
    """
    """
    match_id = kwargs.get("match_id")
    qset = cm.Comments.objects.filter(record_id__isnull=False).order_by(*["-istop","-id"])
    if kwargs.get("user_name"):
        qset = qset.filter(user_name__icontains=kwargs.get("name"))
    data = list(qset.values())
    for item in data:
        item["record_info"] = cm.PlayerRecord.get_db_model(match_id).objects.filter(id=item["record_id"]).values().first()
    #分页
    page = int(kwargs.get("page",1))
    page_size = int(kwargs.get("page_size",20))
    if page and page_size:
        total,data = ccf.get_page_list(data,page,page_size)
    else:
        total = len(data)
    return total,data


def get_wanzhu_consult_list(**kwargs):
    """
    """
    qset = cm.Consult.objects.all().order_by("-ctime")
    if kwargs.get("username"):
        qset = qset.filter(user_name__icontains=kwargs.get("username"))
    if kwargs.get("reply_status"):
        qset = qset.filter(reply_status=kwargs.get("reply_status"))
    if kwargs.get("content"):
        qset = qset.filter(Q(content__icontains=kwargs.get("content"))|Q(reply_content__icontains=kwargs.get("content")))
    data = list(qset.values())
    for item in data:
        user_id = item["user_id"]
        userinfo = get_user_info(user_id)
        
        if userinfo:
            item["user_code"] = userinfo.get("usercode")
            item["phone"] 	  = userinfo.get("phone")


    #分页
    page = int(kwargs.get("page",1))
    page_size = int(kwargs.get("page_size",20))
    if page and page_size:
        total,data = ccf.get_page_list(data,page,page_size)
    else:
        total = len(data)
    return total,data

def calc_reply(v):
    """计算未回复数量
    """
    user_ids = [str(x["user_id"])[0] for x in v]
    user_id_str = "".join(user_ids)

    if v[-1]["user_id"]<=0:
        return 0
    else:
        index = user_id_str.rfind("0")
        return len(v) - index-1


def reply_wanzhu_consult(**kwargs):
    """
    """
    pid = kwargs.get("pid")
    content = kwargs.get("reply_content")
    user_id = kwargs.get("user_id")
    action = kwargs.get("action")
    if action == "send":
        user = cm.UserInfo.objects.filter(id=user_id).first()
        user_name = user.username
        user_avatar = user.avatar

        obj,flag = cm.Consult.objects.get_or_create(
            user_id = user_id, 
        )
        obj.user_name = user_name
        obj.user_avatar = user_avatar
        #obj.reply_content = content
        obj.view_status = 1
        obj.reply_status = 0
        obj.ctime = datetime.datetime.now()
        obj.save()

        wanzhu_obj = cm.WanzhuConsult.objects.create(
            user_id = user_id, 
            user_name = user_name, 
            user_avatar = user_avatar
        )

        wanzhu_obj_1 = cm.WanzhuConsult.objects.create(
            reply_content = content,
            user_id = 0,
            player_id = 0,
            reply_user_id = user_id,
            pid = wanzhu_obj.id
        )
        cm.Consult.objects.filter(user_id=user_id).update(reply_status=1,reply_content=content,ctime=datetime.datetime.now())


    else:
        cm.WanzhuConsult.objects.create(
            pid = pid, 
            reply_content = content,
            user_id = 0,
            player_id = 0,
            reply_user_id = user_id
        )
        cm.Consult.objects.filter(user_id=user_id).update(reply_status=1,reply_content=content,ctime=datetime.datetime.now())
    #发送消息提醒
    if cm.UserInfo.objects.filter(id=user_id).exists():
        user = cm.UserInfo.objects.filter(id=user_id).first()
        openid = user.openid
        username = user.username
        try:
            send_consult_reply_message(openid,username)
        except Exception as e:
            print(e)
        #发送app推送消息
        title = u"顽主杯"
        body = u"你收到一条顽主的消息"
        params = {"type":"message","id":str(user_id)}
        aliyunpush.push_notice_by_userid(user_id,title,body,params,"ios")
        aliyunpush.push_notice_by_userid(user_id,title,body,params,"android")

def update_player_type(**kwargs):
    """
    """
    player_id = kwargs.get("player_id")
    user_id = kwargs.get("user_id")
    player_type = kwargs.get("player_type")
    phone = kwargs.get("phone")

    cm.UserInfo.objects.filter(id=user_id).update(player_type=player_type,phone=phone)
    ccc.del_cache("cdata_get_user_info_(%sL,)"%user_id)

def update_player_role(**kwargs):
    """
    """
    player_id = kwargs.get("player_id")
    user_id = kwargs.get("user_id")
    role = kwargs.get("role")

    cm.Player.objects.filter(id=player_id).update(role=role)


def comments_top(**kwargs):
    """
    """
    _id = kwargs.get("id")
    istop = 1-cm.Comments.objects.filter(id=_id).first().istop
    cm.Comments.objects.filter(id=_id).update(istop=istop)

def comments_black(**kwargs):
    """
    """
    _id = kwargs.get("id")
    comment_status = 1-cm.UserInfo.objects.filter(id=_id).first().comment_status
    cm.UserInfo.objects.filter(id=_id).update(comment_status=comment_status)


def relation_old_user(**kwargs):
    """
    """
    old_user_id = kwargs.get("old_user_id")
    user_id = kwargs.get("user_id")
    print(kwargs,1111111111111)
    user = cm.UserInfo.objects.filter(id=user_id).first()
    if user:
        if user.openid:
            cm.UserInfo.objects.filter(id=old_user_id).update(openid=user.openid)
            cm.UserInfo.objects.filter(id=user_id).delete()


def get_openid_miss(**kwargs):
    """
    """
    qset = cm.UserInfo.objects.filter(openid__isnull=True)
    data = list(qset.values())
    return data

def signup_old_user(**kwargs):
    """
    """
    order_id = kwargs.get("id")
    signup_type = kwargs.get("signup_type")
    match_group = kwargs.get("match_group")
    player_type = kwargs.get("player_type")
    role_type = kwargs.get("role_type")

    order = cm.SignupOrder.objects.filter(id=order_id).first()
    if order:
        user_id = order.user_id
        match_id = order.match_id
        signup_name = order.signup_name

        user = cm.UserInfo.objects.filter(id=user_id).first()
        match_group_name = cm.MatchGroup.objects.filter(id=match_group).first().name
        match = cm.Match.objects.filter(id=match_id).first()

        user.player_type = player_type
        user.phone = order.phone
        user.username = signup_name
        user.save()


        order.signup_type = signup_type
        order.match_group = match_group
        order.match_group_name = match_group_name
        order.order_status = 1
        order.save()
        
        #生产选手信息
        player,flag = cm.Player.objects.get_or_create(
            user_id = user_id, 
            match_id = match_id
        )
        player.match_name = match.name
        player.match_group = match_group
        player.match_group_name = match_group_name
        player.username = signup_name 
        player.usercode = user.usercode
        player.role = role_type
        player.match_status = 1
        player.save()


def get_consult_detail(**kwargs):
    """
    """
    rst = {}
    user_id = kwargs.get("user_id")
    qset = cm.WanzhuConsult.objects.filter(Q(user_id=user_id)|Q(reply_user_id=user_id))
    rst["consult_list"] = list(qset.filter(user_id__gt=0).order_by("ctime").values())
    rst["all_consult_list"] = list(qset.order_by("ctime").values())
    rst["user_id"] = user_id
    cm.Consult.objects.filter(user_id=user_id).update(view_status=1)
    return rst


def delete_consult(**kwargs):
    """
    """
    user_id = kwargs.get("id")
    cm.Consult.objects.filter(user_id=user_id).delete()
    cm.WanzhuConsult.objects.filter(Q(user_id=user_id)|Q(reply_user_id=user_id)).delete()


def batch_update_player_latest(record):
    """更新选手最后一次数据
    """
    user_id = record["user_id"]
    match_id = record["match_id"]
    stock_date = record["stock_date"]
    match_group = record["match_group"]
    #wincnt = record["wincnt"]
    #total = record["totalcnt"]

    key = "PLAYER_LATEST_{}".format(user_id)
    if record and match_group:
        ccc.cache.hset(key,"stock_date",stock_date)
        ccc.cache.hset(key,"match_id",match_id)
        ccc.cache.hset(key,"match_group",match_group)

        ##更新胜率
        #qset = cm.PlayerRecord.get_db_model(match_id).objects.filter(match_id=match_id,user_id=user_id)
        ##win_rate = qset.filter(today_income__gte=0).count()/float(qset.count()) if qset else 0.0
        #win_rate = wincnt/float(total) if total else 0.0
        #win_rate = round(win_rate,3)
        #ccc.cache.hset(key,"win_rate",win_rate)

def get_sub_category(pid):
    """
    """
    qset = cm.BaikeCategory.objects.filter(parent_id=pid)
    qdata = list(qset.values())
    if qdata:
        for subitem in qdata:
            subitem["type"] = "ent"
            subitem["label"] = subitem["name"]
            subitem["children"] = get_sub_category(subitem["id"])                                                                    
    return qdata

def get_baike_category_tree(**kwargs):
    parents = list(cm.BaikeCategory.objects.filter(parent_id__isnull=True).values())
    for item in parents:
        item["label"] = item["name"]
        item["children"] = get_sub_category(item["id"])
    return parents


def get_baike_list_by_category(**kwargs):
    category_id = kwargs.get("category_id")
    qset = cm.BaikeDetail.objects.filter(category_id=category_id)
    data = list(qset.values("id","name"))
    return data

def push_article(**kwargs):
    _id = kwargs.get("id")
    article = cm.Article.objects.filter(id=_id).first()
    #发送app推送消息
    title = u"顽主杯"
    body = article.name
    user_id = "ALL"
    params = {"type":"article","id":_id}
    aliyunpush.push_notice_by_userid(user_id,title,body,params,"ios")
    aliyunpush.push_notice_by_userid(user_id,title,body,params,"android")

def spider_article(**kwargs):

    data = startSpider(kwargs.get("url"))

    return data

def import_article(**kwargs):
    """
    """
    obj = cm.Article.objects.create(**kwargs)


def refund_out_trade_order(**kwargs):
    """
    """
    _id = kwargs.get("id")
    signup_order = cm.SignupOrder.objects.filter(id=_id).first()
    if signup_order:
        out_trade_no = signup_order.out_trade_no
        transaction_id = signup_order.transaction_id
        total_fee = signup_order.total_fee
        amount = str(int(total_fee*100))
        print(amount)
        result = refund_order(transaction_id,out_trade_no,out_trade_no,amount)
        if result.get("result_code") == "SUCCESS":
            signup_order.order_status = -1
            signup_order.save()
            return "success"
        else:
            raise ce.TipException(result.get("err_code_des"))
    else:
        raise ce.TipException(u"该订单不存在退款失败!")

# 计算出入资金
def caculate_inout(**kwargs):
    """
    """

    fundin = kwargs.get("in_fund")
    fundout = kwargs.get("out_fund")
    init_fund = kwargs.get("init_fund")
    today_fund = kwargs.get("today_fund")
    yesterday_fund = kwargs.get("yesterday_fund")
    fundin = float(fundin) if fundin else 0.0
    fundout = float(fundout) if fundout else 0.0
    init_fund = float(init_fund) if init_fund else 0.0
    today_fund = float(today_fund) if today_fund else 0.0
    yesterday_fund = float(yesterday_fund) if yesterday_fund else 0.0
    #入资金
    if fundin > fundout:
        fundin = fundin - fundout
        today_income = round((today_fund-(yesterday_fund+fundin))/(float(yesterday_fund) + fundin),4)
        yesterday_fund = yesterday_fund + fundin
        total_income = round((today_fund-init_fund-fundin)/(init_fund+fundin),4)
        init_fund = init_fund + fundin
    #出资金
    else:
        fundout = fundout - fundin
        today_income = round((today_fund-(yesterday_fund-fundout))/float(yesterday_fund),4)
        yesterday_fund = round(today_fund/(1+today_income),4)
        total_income = round((today_fund+fundout-init_fund)/init_fund,4)
        init_fund = round(today_fund/(1+total_income),4)

    today_income = "{}%".format(today_income*100)
    total_income = "{}%".format(total_income*100)
    yesterday_fund = round(yesterday_fund,4)
    init_fund      = round(init_fund,4)

    return {"yesterday_fund":yesterday_fund,
            "today_income":today_income,
            "total_income":total_income,
            "init_fund":init_fund}
        

def baike_detail_top(**kwargs):
    """
    """
    _id = kwargs.get("id")
    action = kwargs.get("action")

    if action == "top":
        cm.BaikeDetail.objects.filter(id=_id).update(istop=1)
    else:
        cm.BaikeDetail.objects.filter(id=_id).update(istop=0)