class ProductDao():
def __init__(self):
self.session = Session()
# #获取所有的商品数量
def get_all_product(self):
return self.session.query(TProduct).distinct(TProduct.product_id).count()
# #任务ID查询商品总数
def get_all_products_by_taskid(self, taskId):
return self.session.query(TProduct.product_id).filter(TProduct.task_id == taskId). \
distinct().count()
##总的销量
def get_sales_count_by_taskid(self, taskId):
sql_text = text('select sum(product_sales_count) from t_product where ' \
'task_id = :taskId and status = 0')
args = {"taskId": taskId}
sales = self.session.execute(sql_text, args).first()[0]
return int(sales)
##均价
def get_mid_price_by_taskid(self, taskId):
sql_text = text('select AVG(current_price) from t_product where task_id = :taskId and status = 0'
'')
args = {"taskId": taskId}
price = self.session.execute(sql_text, args).first()
print price
return "%.2f" % price[0]
##商店商品总数
def get_products_count_by_shop_id(self, shopId):
return self.session.query(TProduct.product_id).filter(TProduct.shop_id == shopId).distinct().count()
##总销量
def get_all_sales_by_shop_id(self, shopId):
sql_text = ('select sum(product_sales_count) from t_product where shop_id = :shopId and status = 0'
'group by `product_id` order by create_time desc')
args = {"shopId": shopId}
return self.session.execute(sql_text, args).first()
##评论数
def get_comments_count_by_shop_id(self, shopId):
sql_text = 'select sum(good_comments) as gc, sum(mid_comments + bad_comments) as mdc from t_product ' \
'where shop_id = :shopId group by `product_id` order by create_time desc '
args = {"shopId": shopId}
return self.session.execute(sql_text, args).first()
##查询该shop下所有的商品信息
def get_products_by_shop_id(self, shopId, page, pageSize):
return self.session.query(TProduct).group_by(TProduct.product_id). \
filter(TProduct.shop_id == shopId).order_by(TProduct.create_time.desc()) \
.offset((page - 1) * pageSize).limit(pageSize)
## 查询该shop下的所有商品
def get_products_count_by_shop_id(self, shopId):
return self.session.query(TProduct).group_by(TProduct.product_id).filter(TProduct.shop_id == shopId).count()
## 搜索商品信息
def get_product_list_and_shop(self, taskId, productName, minPrice, maxPrice,
minComments, maxComments, minSales, maxSales,
shopName, shopArea, shopType, page, pageSize):
sql_text = "select tp.product_id, tp.product_name, tp.product_sales_count," \
"tp.current_price, tp.product_comments, tp.good_comments, tp.mid_comments, tp.bad_comments," \
"tp.product_url, ts.shop_id, ts.shop_name, ts.shop_level, ts.shop_area, ts.shop_url " \
"from t_product as tp left join t_shop as ts on tp.shop_id = ts.shop_id where 1 = 1 and tp.shop_id <> '' and tp.status = 0"
if taskId is not None and taskId != "":
sql_text += " and tp.task_id=" + taskId
if productName is not None and productName != "":
sql_text += " and tp.product_name like '%" + productName + "%'"
if minPrice is not None and minPrice != "":
sql_text += " and tp.current_price >=" + str(minPrice)
if maxPrice is not None and maxPrice != "":
sql_text += " and tp.current_price <=" + str(maxPrice)
if minComments is not None and minComments != "":
sql_text += " and tp.product_comments >=" + str(minComments)
if maxComments is not None and maxComments != "":
sql_text += " and tp.product_comments <=" + str(maxComments)
if minSales is not None and minSales != "":
sql_text += " and tp.product_sales_count >=" + str(minSales)
if maxSales is not None and maxSales != "":
sql_text += " and tp.product_sales_count <=" + str(maxSales)
if shopName is not None and shopName != "":
sql_text += " and ts.shop_name like '%" + shopName + "%'"
if shopArea is not None and shopArea != "":
sql_text += " and ts.shop_area like '%" + shopArea + "%'"
if shopType is not None and shopType != "":
sql_text += " and ts.shop_type =" + shopType
sql_text += " group by tp.product_id order by tp.create_time desc "
sql_text += " limit " + str((page - 1) * pageSize) + ", " + str(pageSize)
return self.session.execute(sql_text).fetchall()
def get_product_list_and_shop_all(self, taskId, productName, minPrice, maxPrice,
minComments, maxComments, minSales, maxSales,
shopName, shopArea, shopType):
sql_text = "select tp.product_id, tp.product_name, tp.product_sales_count," \
"tp.current_price, tp.product_stock," \
"tp.product_url, ts.shop_boss_tb, ts.shop_id, ts.shop_name, ts.shop_level, ts.shop_area, ts.shop_url " \
"from t_product as tp left join t_shop as ts on tp.shop_id = ts.shop_id where 1 = 1 and tp.shop_id <> '' and tp.status = 0"
if taskId is not None and taskId != "":
sql_text += " and tp.task_id=" + taskId
if productName is not None and productName != "":
sql_text += " and tp.product_name like '%" + productName + "%'"
if minPrice is not None and minPrice != "":
sql_text += " and tp.current_price >=" + str(minPrice)
if maxPrice is not None and maxPrice != "":
sql_text += " and tp.current_price <=" + str(maxPrice)
if minComments is not None and minComments != "":
sql_text += " and tp.product_comments >=" + str(minComments)
if maxComments is not None and maxComments != "":
sql_text += " and tp.product_comments <=" + str(maxComments)
if minSales is not None and minSales != "":
sql_text += " and tp.product_sales_count >=" + str(minSales)
if maxSales is not None and maxSales != "":
sql_text += " and tp.product_sales_count <=" + str(maxSales)
if shopName is not None and shopName != "":
sql_text += " and ts.shop_name like '%" + shopName + "%'"
if shopArea is not None and shopArea != "":
sql_text += " and ts.shop_area like '%" + shopArea + "%'"
if shopType is not None and shopType != "":
sql_text += " and ts.shop_type =" + shopType
sql_text += " group by tp.product_id order by tp.create_time desc "
return self.session.execute(sql_text).fetchall()
##总的数量
def get_product_count_and_shop(self, taskId, productName, minPrice, maxPrice,
minComments, maxComments, minSales, maxSales,
shopName, shopArea, shopType):
sql_text = "select count(*) from t_product as tp left join t_shop as ts on tp.shop_id = ts.shop_id where 1 = 1"
if taskId is not None and taskId != "":
sql_text += " and tp.task_id=" + taskId
if productName is not None and productName != "":
sql_text += " and tp.product_name like '%" + productName + "%'"
if minPrice is not None and minPrice != "":
sql_text += " and tp.current_price >=" + str(minPrice)
if maxPrice is not None and maxPrice != "":
sql_text += " and tp.current_price <=" + str(maxPrice)
if minComments is not None and minComments != "":
sql_text += " and tp.product_comments >=" + str(minComments)
if maxComments is not None and maxComments != "":
sql_text += " and tp.product_comments <=" + str(maxComments)
if minSales is not None and minSales != "":
sql_text += " and tp.product_sales_count >=" + str(minSales)
if maxSales is not None and maxSales != "":
sql_text += " and tp.product_sales_count <=" + str(maxSales)
if shopName is not None and shopName != "":
sql_text += " and ts.shop_name like '%" + shopName + "%'"
if shopArea is not None and shopArea != "":
sql_text += " and ts.shop_area like '%" + shopArea + "%'"
if shopType is not None and shopType != "":
sql_text += " and ts.shop_type =" + shopType
sql_text += " order by tp.create_time desc "
return self.session.execute(sql_text).first()
##通过ID获取最新的产品信息
def get_product_by_id(self, productId):
sql_text = "select tp.product_id, tp.product_url, tp.product_name, tp.current_price, tp.original_price, tp.product_sales_count, " \
"tp.good_comments, tp.mid_comments, tp.bad_comments,tp.product_collections, ts.shop_name,ts.shop_url, ts.shop_boss_tb, " \
"ts.shop_area, ts.shop_level, ts.shop_time, ts.shop_desc_count, ts.shop_service_count, " \
"ts.shop_logis_count, ts.month_tuikuan_value, ts.month_dispute_value, ts.month_punish_value " \
"from t_product as tp left join t_shop as ts on tp.shop_id = ts.shop_id" \
" where tp.product_id=:productId and tp.status = 0 group by tp.product_id order by tp.create_time desc"
args = {"productId": productId}
return self.session.execute(sql_text, args).first()
##价格分析
def get_price_by_product_id(self, productId):
sql_text = 'select tp.current_price, tp.original_price, DATE_FORMAT(tp.create_time,"%Y%m%d") ' \
'from t_product as tp where tp.product_id = ' + productId + ' and tp.status = 0 group by tp.create_time order by tp.create_time desc'
return self.session.execute(sql_text).fetchall()
##销量变化曲线
def get_sales_by_product_id(self, productId):
sql_text = 'select tp.product_sales_count, DATE_FORMAT(tp.create_time,"%Y%m%d") from t_product as tp where ' \
'tp.product_id=:productId and tp.status = 0 group by tp.create_time order by tp.create_time desc'
args = {"productId": productId}
return self.session.execute(sql_text, args).fetchall()
##评论变化
def get_comments_by_product_id(self, productId):
sql_text = 'select tp.product_comments, CAST(sum(tp.mid_comments + tp.bad_comments) as SIGNED) as md, ' \
'DATE_FORMAT(tp.create_time,"%Y%m%d") from t_product as tp where tp.product_id=:productId and tp.status = 0 ' \
'group by tp.create_time order by tp.create_time desc'
args = {"productId": productId}
return self.session.execute(sql_text, args).fetchall()
##人气指数
def get_popular_by_product_id(self, productId):
sql_text = 'select CAST(sum(tp.product_collections + tp.product_fukuan) as SIGNED) as popular, ' \
'DATE_FORMAT(tp.create_time,"%Y%m%d") from t_product as tp where ' \
'tp.product_id=:productId and tp.status = 0 group by tp.create_time order by tp.create_time desc'
args = {"productId": productId}
return self.session.execute(sql_text, args).fetchall()
def get_data_date_by_task_id(self, taskId):
sql_text = 'select DATE_FORMAT(create_time, "%Y%m%d") from t_product as tp where tp.task_id = :taskId and tp.status = 0 ' \
'group by DATE_FORMAT(create_time, "%Y%m%d")'
args = {"taskId": taskId}
return self.session.execute(sql_text, args).fetchall()
##根据日期分页显示数据
def get_data_by_date_with_taskid(self, createDate, taskId, page, pageSize):
sql_text = text('select tp.product_id, tp.product_url, tp.product_name from t_product as tp where DATE_FORMAT(tp.create_time, "%Y%m%d") =:createDate '
'and tp.task_id=:taskId and tp.status = 0 order by tp.create_time limit :start_num, :end_num')
start_num = (page - 1) * pageSize
args = {"createDate": createDate, "taskId": taskId, "start_num": start_num, "end_num": pageSize}
return self.session.execute(sql_text, args).fetchall()
##根据日期任务ID显示所有的数据
def get_all_count_by_date_with_taskid(self, createDate, taskId):
sql_text = text('select count(*) from t_product as tp where DATE_FORMAT(tp.create_time, "%Y%m%d") =:createDate'
' and tp.task_id=:taskId and tp.status = 0')
args = {"createDate": createDate, "taskId": taskId}
return self.session.execute(sql_text, args).first()[0]
##根据任务ID和日期显示所有的数据
def get_all_data_by_date_with_taskid(self, createDate, taskId):
sql_text = text('select tp.product_id from t_product as tp where DATE_FORMAT(tp.create_time, "%Y%m%d") =:createDate '
'and tp.task_id=:taskId and tp.status = 0 order by tp.create_time')
args = {"createDate": createDate, "taskId": taskId}
return self.session.execute(sql_text, args).fetchall()
class TaskDao():
def __init__(self):
self.session = Session()
def add_task(self, name, keyword, type, num, start_time, user_id, url,
parentId, is_comment):
task = TTask()
task.create_time = datetime.datetime.now()
task.task_key = keyword
task.task_num = num
task.task_status = 0
task.task_name = name
task.task_type = type
task.created_id = user_id
task.updated_id = user_id
task.update_time = datetime.datetime.now()
task.start_time = start_time
task.task_url = url
task.is_open = 0
task.parent_id = parentId
task.is_comments = is_comment
self.session.add(task)
self.session.commit()
self.session.refresh(task)
return task.id
def get_all_task_count(self):
return self.session.query(TTask).distinct(TTask.task_id).count()
## 更新任务状态
def update_task_status(self, taskid, status):
self.session.query(TTask).filter(TTask.task_id == taskid).update(
{"task_status": status}, synchronize_session=False)
self.session.commit()
##更新任务ID
def update_task_id(self, id, taskid):
self.session.query(TTask).filter(TTask.id == id).update(
{"task_id": taskid}, synchronize_session=False)
self.session.commit()
##关闭或者开启任务
def close_or_open_task(self, id, open):
self.session.query(TTask).filter(TTask.id == id).update(
{"is_open": open}, synchronize_session=False)
self.session.commit()
##任务列表
def get_task_list(self, keyword, page, page_size, status):
if status is None or status == '':
return self.session.query(TTask).order_by(TTask.create_time.desc()).filter(TTask.task_key.like('%{0}%'.format(keyword)))\
.filter(TTask.parent_id == 0).offset((page - 1) * page_size).limit(page_size).all()
else:
return self.session.query(TTask).order_by(TTask.create_time.desc()).filter(TTask.task_status == status).filter(TTask.task_key.like('%{0}%'.format(keyword)))\
.filter(TTask.parent_id == 0).offset((page - 1) * page_size).limit(page_size).all()
##获取子任务
def get_child_task_list(self, parentId, status):
return self.session.query(TTask).filter(
TTask.parent_id == parentId).all()
## 获取任务总的数量
def get_task_count(self, keyword, status):
if status is None or status == '':
return self.session.query(TTask).filter(
TTask.task_key.like('%{0}%'.format(keyword))).filter(
TTask.parent_id == 0).count()
else:
return self.session.query(TTask).filter(TTask.task_status == status).\
filter(TTask.task_key.like('%{0}%'.format(keyword))).filter(TTask.parent_id == 0).count()
##查询任务详情
def get_task_details(self, taskId):
return self.session.query(TTask).filter(
TTask.task_id == taskId).first()
##价格空间分布
def get_price_range_by_task_id(self, taskId):
sql_text = text(
'select tp.current_price as price, tp.product_sales_count from t_product as tp where '
'tp.task_id=:taskId and tp.current_price <> "" and tp.shop_id <> ""'
)
args = {"taskId": taskId}
return self.session.execute(sql_text, args).fetchall()
class ShopDao():
def __init__(self):
self.session = Session()
##搜索所有的商家
def select_shop_list(self, page, pageSize, key, area, type, mark, minLevel,
maxLevel, taskId):
sql = "select ts.*, tp.* from t_shop as ts left join (select shop_id, sum(product_sales_count), " \
"sum(good_comments), sum(mid_comments), sum(bad_comments) from t_product group by product_id order by create_time desc) " \
"as tp on ts.shop_id = tp.shop_id "
if mark is not None and mark != '':
sql += " left join t_mark_shop as tms on tms.shop_id = ts.shop_id and tms.status=" + mark
sql += " where 1=1"
if key is not None and key != '':
sql += " and ts.shop_name like '%" + key + "%'"
if area is not None and area != '':
sql += " and ts.shop_area like '%" + area + "%'"
if minLevel is not None and minLevel != '':
sql += " and ts.shop_level >=" + str(minLevel)
if maxLevel is not None and maxLevel != '':
sql += " and ts.shop_level <=" + str(maxLevel)
if type is not None and type != '':
sql += " and ts.shop_type=" + type
if taskId is not None and taskId != '':
sql += " and ts.task_id=" + taskId
sql += " group by ts.shop_id order by ts.create_time desc "
sql += " limit " + str((page - 1) * pageSize) + ", " + str(pageSize)
sql_text = text(sql)
return self.session.execute(sql_text).fetchall()
def select_shop_list_all(self, key, area, type, mark, minLevel, maxLevel,
taskId):
sql = "select ts.*, tp.* from t_shop as ts left join (select shop_id, sum(product_sales_count), " \
"sum(good_comments), sum(mid_comments), sum(bad_comments) from t_product group by product_id order by create_time desc) " \
"as tp on ts.shop_id = tp.shop_id "
if mark is not None and mark != '':
sql += " left join t_mark_shop as tms on tms.shop_id = ts.shop_id and tms.status=" + mark
sql += " where 1=1"
if key is not None and key != '':
sql += " and ts.shop_name like '%" + key + "%'"
if area is not None and area != '':
sql += " and ts.shop_area like '%" + area + "%'"
if minLevel is not None and minLevel != '':
sql += " and ts.shop_level >=" + str(minLevel)
if maxLevel is not None and maxLevel != '':
sql += " and ts.shop_level <=" + str(maxLevel)
if type is not None and type != '':
sql += " and ts.shop_type=" + type
if taskId is not None and taskId != '':
sql += " and ts.task_id=" + taskId
sql += " group by ts.shop_id order by ts.create_time desc "
sql_text = text(sql)
return self.session.execute(sql_text).fetchall()
##搜索商家的总数
def select_shop_list_count(self, key, area, type, mark, minLevel, maxLevel,
taskId):
sql = "select count(DISTINCT ts.shop_id) from t_shop as ts where 1=1"
if key is not None and key != '':
sql += " and ts.shop_name like '%" + key + "%'"
if area is not None and area != '':
sql += " and ts.shop_area like '%" + area + "%'"
if minLevel is not None and minLevel != '':
sql += " and ts.shop_level >=" + minLevel
if maxLevel is not None and maxLevel != '':
sql += " and ts.shop_level <=" + maxLevel
if type is not None and type != '':
sql += " and ts.shop_type=" + type
if taskId is not None and taskId != '':
sql += " and ts.task_id=" + taskId
sql_text = text(sql)
return self.session.execute(sql_text).fetchall()[0]
##搜索商家最后一条信息
def select_shop_last_data_by_id(self, shopId):
return self.session.query(TShop).filter(
TShop.shop_id == shopId).order_by(
TShop.create_time.desc()).first()
##平台总共多少商家
def select_all_shop_count(self):
return self.session.query(TShop).distinct(TShop.shop_id).count()
##根据任务ID查询所有商家
def select_all_shop_by_task_id(self, taskId):
return self.session.query(TShop.shop_id).\
filter(TShop.task_id == taskId).distinct().count()
##销售排名
def get_sales_ranking_by_task_id(self, taskId):
sql_text = text(
'select ts.shop_id, ts.shop_name, ts.shop_url, tp.product_sales_count from t_shop as ts left join t_product '
'as tp on ts.shop_id = tp.shop_id where ts.task_id = :taskId group by ts.shop_id '
'order by tp.product_sales_count desc limit 10')
args = {"taskId": taskId}
return self.session.execute(sql_text, args).fetchall()
##信用排名
def get_shop_level_by_task_id(self, taskId):
sql_text = text(
'select ts.shop_id, ts.shop_name, ts.shop_url, ts.shop_level from t_shop as ts where ts.task_id = :taskId '
'group by ts.shop_id order by ts.shop_level desc limit 10')
args = {"taskId": taskId}
return self.session.execute(sql_text, args).fetchall()
##中差评排名
def get_comments_by_task_id(self, taskId):
sql_text = text(
'select ts.shop_id, ts.shop_name, ts.shop_url, (tp.mid_comments + tp.bad_comments) as md from t_shop as '
'ts left join t_product as tp on tp.shop_id = ts.shop_id where ts.task_id = :taskId '
'group by ts.shop_id order by (tp.mid_comments + tp.bad_comments) desc limit 10'
)
args = {"taskId": taskId}
return self.session.execute(sql_text, args).fetchall()
##服务情况变化
def get_service_vary_by_shop_id(self, shopId):
sql_text = text(
'select DATE_FORMAT(ts.create_time,"%Y%m%d") as shop_date, ts.month_tuikuan_value, ts.month_auto_end, '
'ts.month_dispute_value, ts.month_punish_value'
' from t_shop as ts where ts.shop_id =:shopId group by ts.shop_id order by ts.create_time desc'
)
args = {"shopId": shopId}
return self.session.execute(sql_text, args).fetchall()
#动态评分
def get_comments_vary_by_shop_id(self, shopId):
sql_text = text(
'select DATE_FORMAT(ts.create_time,"%Y%m%d") as shop_date, ts.shop_desc_count, '
'ts.shop_service_count, ts.shop_logis_count from t_shop as ts where ts.shop_id =:shopId '
'group by ts.shop_id order by ts.create_time desc')
args = {"shopId": shopId}
return self.session.execute(sql_text, args).fetchall()
# shopDao = ShopDao()
# print shopDao.select_shop_list(1, 20, None, None, None, None, None)