from backend.models import ZzNews
from common import util
from django.db import connection
from django.db.models import Sum, Count
import math


def get_news(page, page_size):
    """
    根据页数获取新闻列表
    :param page: 当前页数,默认1
    :param page_size: 每页条数,默认8
    :return:
    """
    # start_point = (int(page) - 1) * offset
    # news_list = ZzNews.objects.filter(delete_flag=0).order_by("-create_time")[start_point:offset]
    # news_list = util.sql_array(news_list,
    #                            ['id', 'class_id', 'news_title', 'navi_content', 'create_user', 'create_time'])
    # return news_list
    start_point = (int(page) - 1) * page_size
    date_format = "%Y-%m-%d"
    cursor = connection.cursor()
    cursor.execute(
        'select id,class_id,if(class_id=1,"公司新闻",if(class_id=2,"媒体报道",if(class_id=3,"行业动态",""))) as class_name, news_title,navi_content,create_user,date_format(create_time,%s) as create_time from zz_news where delete_flag=0 ORDER BY create_time DESC limit %s,%s ',
        [date_format, start_point, page_size])
    items = cursor.fetchall()  # 读取所有
    desc = cursor.description
    li = []
    if items:
        for item in items:
            d = {}
            i = 0
            for de in desc:
                d[de[0]] = item[i]
                i = i + 1
            li.append(d)
        return li
    else:
        return li


def get_news_by_class(class_id, page, page_size):
    """
    根据分类获取新闻列表以及数量
    :param class_id: 当前分类, 默认1
    :param page: 当前页数,默认1
    :param page_size: 每页数量,默认8
    :return:
    """
    page_size = int(page_size)
    start_point = (int(page) - 1) * page_size
    # news_list = ZzNews.objects.filter(delete_flag=0, class_id=class_id).order_by("-create_time")[start_point:offset]
    # news_list = util.sql_array(news_list,
    #                            ['id', 'class_id', 'news_title', 'navi_content', 'create_user', 'create_time'])
    date_format = "%Y-%m-%d"
    cursor = connection.cursor()
    cursor.execute(
        'select id,class_id,news_title,navi_content,content,create_user,date_format(create_time,%s) as create_time from zz_news WHERE delete_flag=0 and class_id = %s ORDER  BY  create_time DESC limit %s,%s ',
        [date_format, class_id, start_point, page_size])
    items = cursor.fetchall()  # 读取所有
    desc = cursor.description
    news_list = []
    if items:
        for item in items:
            d = {}
            i = 0
            for de in desc:
                d[de[0]] = item[i]
                i = i + 1
            news_list.append(d)
    num = ZzNews.objects.filter(delete_flag=0, class_id=class_id).order_by("-create_time").aggregate(Count("id"))
    num = dict(num)
    paper = {}
    num['news_list'] = news_list
    paper['current'] = int(page)
    paper['page_size'] = int(page_size)
    paper['total'] = int(num['id__count'])
    paper['total_pages'] = math.ceil(int(num['id__count']) / int(page_size))
    num['page'] = paper
    return num


def get_news_content(news_id):
    """
    根据id获取新闻的内容
    :param news_id:
    :return: list
    """
    news = {}
    news_content = ZzNews.objects.filter(pk=news_id)
    news_content = util.sql_array(news_content,
                                  ['id', 'class_id', 'news_title', 'navi_content', 'content', 'create_user',
                                   'create_time'])

    cursor = connection.cursor()
    sql='select * from zz_news WHERE delete_flag=0 and class_id = %s and  create_time < "%s" order by create_time desc limit 1' %(news_content[0]['class_id'],news_content[0]['create_time'])
    cursor.execute(sql)
    items = cursor.fetchall()  # 读取所有
    desc = cursor.description
    prev_content = []
    if items:
        for item in items:
            d = {}
            i = 0
            for de in desc:
                d[de[0]] = item[i]
                i = i + 1
            prev_content.append(d)

    cursor = connection.cursor()
    sql='select * from zz_news WHERE delete_flag=0 and class_id = %s and create_time > "%s" order by create_time limit 1' %(news_content[0]['class_id'],news_content[0]['create_time'])
    cursor.execute(sql)
    items = cursor.fetchall()  # 读取所有
    desc = cursor.description
    next_content = []
    if items:
        for item in items:
            d = {}
            i = 0
            for de in desc:
                d[de[0]] = item[i]
                i = i + 1
            next_content.append(d)
    news['prev_news'] = prev_content
    news['current_news'] = news_content
    news['next_news'] = next_content
    return news