new_website/official/model/CmsCategoryProd.py

from official.models import CmsCategoryProd
from common import util


def getProdByCms(cms_cate_id, content, page, page_size):
    """
    根据cms_category_id查找对应的产品列表
    :param cms_cate_id: 品类id
    :param content: 搜索关键字
    :param page: 当前页
    :param page_size: 一页的数量
    :return:list
    """
    condition_list = []
    page_size = int(page_size)
    start_point = (int(page) - 1) * page_size
    sql = """SELECT c.pro_cate_id, c.sign, c.pro_cate_name, c.prod_des, c.show_price,c.show_img, c.category_id,
            (SELECT d.end_area_id from bus_ticket d where d.line_id = c.sign order by d.ticket_id desc limit 1) 'poi_id'
            FROM cms_category_prod AS b JOIN prod_category AS c ON c.pro_cate_id = b.prod_cate_id  AND c.delete_flag = 0 WHERE b.system = 3 AND b.delete_flag = 0 """
    if cms_cate_id != 0 and content == '':
        sql += ' AND b.cms_cate_id = %s '
        condition_list.append(cms_cate_id)

    if content != '':
        sql += ' and c.pro_cate_name LIKE %s'
        content = '%' + content + '%'
        condition_list.append(content)

    sql += 'limit %s,%s'
    condition_list.append(start_point)
    condition_list.append(page_size)
    prod_list = util.select_all(sql, condition_list)
    return prod_list


def getProdNumByCms(cms_cate_id, content):
    """
    获取对应的分类下的产品的数量
    :param cms_cate_id: 品类id
    :return:
    """
    condition_list = []
    sql = """SELECT sum(if(c.pro_cate_id,1,0)) as num FROM cms_category_prod AS b JOIN prod_category AS c ON c.pro_cate_id = b.prod_cate_id AND c.delete_flag = 0 WHERE b.system = 3 AND b.delete_flag = 0"""
    if cms_cate_id != 0 and content == '':
        sql += ' AND b.cms_cate_id = %s'
        condition_list.append(cms_cate_id)

    if content != '':
        sql += ' and c.pro_cate_name LIKE %s'
        content = '%' + content + '%'
        condition_list.append(content)

    num = util.select_row(sql, condition_list)
    return num