def get_registry_counts(self):
sql = """
select count(*) as count from accounts
"""
results = db.session.execute(sql).fetchall()
items = sql_result_to_dict(results)
return items[0]
def sql_search_new_key_data_total(self, key):
sql = """
SELECT count(*) AS total FROM curriculums AS c WHERE c.delete_at IS NULL AND c.cname LIKE '%{}%'
""".format(key)
results = db.session.execute(sql).fetchall()
_total = sql_result_to_dict(results)
return _total[0].get("total")
def get_teacher_royalty_money(self, day):
sql = """
select date_format(create_at,'%Y-%m-%d') as dateDay ,sum(actual_money) as money_total from extracts group by dateDay order by dateDay desc limit 0,{}
""".format(day)
results = db.session.execute(sql).fetchall()
items = sql_result_to_dict(results)
return items
def get_day_sum_money(self, day):
sql = """
select date_format(create_at,'%Y-%m-%d')as dateDay,sum(t_money) as moneyDay from extracts group by dateDay order by dateDay limit 0,{}
""".format(day)
results = db.session.execute(sql).fetchall()
items = sql_result_to_dict(results)
return items
def get_day_up_curriculums_count(self, day=10):
sql = "select " \
"DATE_FORMAT(c.create_at,'%Y-%m-%d') as dateDay,count(*) as countDay " \
"from " \
"curriculums as c " \
"group by dateDay order by dateDay desc limit 0,{}".format(day)
results = db.session.execute(sql).fetchall()
items = sql_result_to_dict(results)
return items
def get_day_registry_count(self, day=10):
sql = "select " \
"DAte_format(u.create_at,'%Y-%m-%d') as dateDay,count(*) as countDay " \
"from accounts as u where u.status != 0 and u.status !=10 " \
"group by dateDay order by dateDay desc " \
"limit 0,{}".format(day)
results = db.session.execute(sql).fetchall()
items = sql_result_to_dict(results)
return items
def sql_order_new_results(self, key, page, number):
sql = """
SELECT
*
FROM
curriculums as c
WHERE
delete_at IS NULL AND cname LIKE '%{}%'
ORDER BY create_at
DESC limit {},{}
""".format(key, (page - 1) * number, number)
results = db.session.execute(sql).fetchall()
items = sql_result_to_dict(results, "cimage")
return items
def sql_search_hot_key_data_total(self, key):
sql = """
SELECT
count(distinct c.cid) AS total
FROM
shopping_carts AS sp
JOIN
curriculums AS c
ON
sp.cid = c.cid
WHERE
c.delete_at is null and c.cname LIKE '%{}%'
""".format(key)
results = db.session.execute(sql).fetchall()
_total = sql_result_to_dict(results)
return _total[0].get("total")
def sql_order_hot_results(self, key, page, number):
sql = """
SELECT
count(*) AS count ,sp.cid AS s_cid ,c.*
FROM
shopping_carts AS sp
join
curriculums AS c
ON
sp.cid = c.cid
WHERE
c.delete_at IS NULL AND c.cname like '%{}%'
GROUP BY s_cid
ORDER BY s_cid DESC
limit {},{}
""".format(key, (page - 1) * number, number)
results = db.session.execute(sql).fetchall()
items = sql_result_to_dict(results, "cimage")
return items
def get_purchase_records(self, page=1, number=10):
if page == None:
page = 1
if number == None:
number = 10
c = self.query.filter_by(aid=self.aid).first()
sql = "select u.aid as uid,u.nickname as nk,u.status as st,c.aid as c_aid,c.cname as name,c.price as price,c.cimage as img,c.create_at as at " \
"from " \
"shopping_carts as shop " \
"join " \
"accounts as u " \
"on " \
"u.aid = shop.aid " \
"join curriculums as c " \
"on " \
"shop.cid = c.cid " \
"where shop.cid in (select cid from curriculums where aid = {}) limit {},{}}".format(c.aid,page-1,page*number)
results = db.session.execute(sql).fetchall()
items = sql_result_to_dict(results)
return items
