def get_main_menu(user_id, parent_menu_id=None):
"""
主页获取菜单
:param user_id:
:param parent_menu_id:
:return:
"""
where_param = [HUser.pid == user_id]
if parent_menu_id:
where_param.append(HMenu.parent_menu == parent_menu_id)
else:
where_param.append(HMenu.parent_menu.is_null())
and_conditions = reduce(operator.and_, where_param)
sql = HMenu.select(
HMenu.pid, HMenu.menu_name.alias("title"), HMenu.menu_url.alias("href"), HMenu.parent_menu, HMenu.icon
).join(
HOrg, join_type=JOIN_INNER, on=HMenu.org_ids ** (fn.CONCAT("%,", HOrg.pid, ",%"))
).join(
HRole, join_type=JOIN_INNER, on=HMenu.role_ids ** (fn.CONCAT("%,", HRole.pid, ",%"))
).join(
HUser, join_type=JOIN_INNER,
on=((HUser.org_ids ** (fn.CONCAT("%,", HOrg.pid, ",%"))) &
(HUser.role_ids ** (fn.CONCAT("%,", HRole.pid, ",%"))))
).where(and_conditions).group_by(HMenu.pid).order_by(HMenu.pid, HMenu.sort)
result = [f for f in sql.dicts()]
return result
def case_display_name():
"""Plaintiff v. Defendant, Reporter (Year), written in query syntax."""
return fn.CONCAT(
Cluster.case_name,
fn.coalesce(fn.CONCAT(", ", Cluster.reporter)),
" (",
Cluster.year,
")",
)
def _execute(self) -> Tuple[List[TopBalanceOutput]]:
"""
Execute stock level transaction
:return: number of items with lower stock quantity than threshold
"""
# Get customers with warehouse and district information
top_customer_query = (Customer.select(
Case(
None,
((
Customer.middle_name.is_null(),
fn.CONCAT(
Customer.first_name,
" ",
Customer.last_name,
),
), ),
fn.CONCAT(
Customer.first_name,
Customer.middle_name,
Customer.last_name,
),
).alias("customer_name"),
Customer.balance.alias("customer_balance"),
Customer.warehouse_id.alias("warehouse_id"),
Customer.district_id.alias("district_id"),
).order_by(Customer.balance.desc()).limit(
TopBalanceTransaction.LIMIT).cte("top_customer_query"))
top_balance_query = (top_customer_query.select_from(
top_customer_query.c.customer_name,
top_customer_query.c.customer_balance,
Warehouse.name.alias("warehouse_name"),
District.name.alias("district_name"),
).join(
District,
on=((top_customer_query.c.warehouse_id == District.warehouse_id)
& (top_customer_query.c.district_id == District.id)),
).join(
Warehouse,
on=(top_customer_query.c.warehouse_id == Warehouse.id),
).order_by(top_customer_query.c.customer_balance.desc()).with_cte(
top_customer_query))
return ([result for result in top_balance_query.dicts()], )
def alias_column() -> 'ModelSelect':
"""列别名.
SELECT Concat(vend_name, ' (', vend_country, ')') AS vend_title
FROM vendor;
"""
title = fn.CONCAT(Vendor.vend_name, ' (', Vendor.vend_country, ')')
return (Vendor
.select(title.alias('vend_title')))
def concat() -> 'ModelSelect':
"""拼接.
SELECT Concat(vend_name, ' (', vend_country, ')')
FROM vendor;
"""
title = fn.CONCAT(Vendor.vend_name, ' (', Vendor.vend_country, ')')
return (Vendor
.select(title))
def init_menus(user_id):
"""
用户登录时获取当前用户的菜单数据集
:param user_id: 用户id
:return:
"""
sql = HMenu.select(
HMenu.pid, HMenu.menu_name.alias("title"),
HMenu.menu_url.alias("href"), HMenu.parent_menu, HMenu.icon).join(
HOrg,
join_type=JOIN_INNER,
on=HMenu.org_ids**(fn.CONCAT("%,", HOrg.pid, ",%"))).join(
HRole,
join_type=JOIN_INNER,
on=HMenu.role_ids**(fn.CONCAT("%,", HRole.pid, ",%"))).join(
HUser,
join_type=JOIN_INNER,
on=((HUser.org_ids**(fn.CONCAT("%,", HOrg.pid, ",%"))) &
(HUser.role_ids**(fn.CONCAT(
"%,", HRole.pid, ",%"))))).where(
HUser.pid == user_id).order_by(
HMenu.parent_menu, HMenu.sort)
result = [f for f in sql.dicts()]
level_1_menus = [] # 一级菜单集合
level_2_menus = {} # 二级菜单集合
level_1_child_key = "menu_%s"
for res in result:
if res["parent_menu"]:
menus = level_2_menus[level_1_child_key % res["parent_menu"]]
menus.append(res)
level_2_menus[level_1_child_key % res["parent_menu"]] = menus
else:
level_2_menus[level_1_child_key % res["pid"]] = []
level_1_menus.append(res)
return {"level1": level_1_menus, "level2": level_2_menus}
def get_role_by_user_id(user_id):
"""
根据用户id获取相关角色
:param user_id:
:return:
"""
sql = HRole.select(
fn.group_concat(HRole.role_code).alias("role_codes"),
fn.group_concat(HRole.role_name).alias("role_names"),
).join(HUser,
on=HUser.role_ids**(fn.CONCAT("%,", HRole.pid,
",%"))).where(HUser.pid == user_id)
result = [f for f in sql.dicts()]
if result:
return result[0]
return ""
def report_by_date(date):
data = list(trait.select(report.report_slug,
report.report_name,
trait.trait_name,
trait.trait_slug,
report.release,
trait.submission_date,
mapping.log10p,
fn.CONCAT(mapping.chrom, ":", mapping.pos).alias("CHROM_POS")) \
.join(mapping, JOIN.LEFT_OUTER) \
.switch(trait) \
.join(report) \
.filter(
(db.truncate_date("day", trait.submission_date) == parse(date).date()
),
(report.release == 0),
(trait.status == "complete")
) \
.dicts()
.execute())
return jsonify(data)
def uid(cls):
return fn.CONCAT(cls.idol_id, '-', cls.fan_id)
def get_menu(menu_id=None, user_id=None, role_codes=None, is_children=None):
"""
获取菜单，menu_id为空时查询一级菜单，
is_children为None时根据menu_id查询菜单信息，
否则查询menu_id相关的子菜单
:param menu_id: 菜单主键
:param is_children: 是否是查询子菜单
:param user_id: 当前用户ID
:return:
"""
Menu = HMenu.alias()
ParentMenu = HMenu.alias()
where_param = [HUser.pid == user_id]
if is_children and menu_id:
where_param.append(Menu.parent_menu == menu_id)
elif menu_id:
where_param.append(Menu.pid == menu_id)
else:
where_param.append(Menu.parent_menu.is_null())
and_conditions = reduce(operator.and_, where_param)
# 判断当前用户是不是管理员
if conf.ROLE_CODE_ADMIN in role_codes: # 是管理员
sql = Menu.select(
Menu.pid, Menu.menu_name, Menu.sort, HOrg.name.alias("org_name"),
HRole.role_name,
fn.ifnull(ParentMenu.menu_name, "").alias("parent_name"),
fn.ifnull(Menu.menu_url, "").alias("menu_url")).join(
ParentMenu,
join_type=JOIN_LEFT_OUTER,
on=Menu.parent_menu == ParentMenu.pid).join(
HOrg,
join_type=JOIN_INNER,
on=Menu.org_ids**(fn.CONCAT("%,", HOrg.pid, ",%"))).join(
HRole,
join_type=JOIN_INNER,
on=Menu.role_ids**(fn.CONCAT(
"%,", HRole.pid, ",%"))).join(
HUser,
join_type=JOIN_INNER,
on=HUser.org_ids**(fn.CONCAT(
"%,", HOrg.pid,
",%"))).where(and_conditions).order_by(
Menu.pid, Menu.sort)
else: # 不是管理员
sql = Menu.select(
Menu.pid, Menu.menu_name, Menu.sort, HOrg.name.alias("org_name"),
HRole.role_name,
fn.ifnull(ParentMenu.menu_name, "").alias("parent_name"),
fn.ifnull(Menu.menu_url, "").alias("menu_url")).join(
ParentMenu,
join_type=JOIN_LEFT_OUTER,
on=Menu.parent_menu == ParentMenu.pid).join(
HOrg,
join_type=JOIN_INNER,
on=Menu.org_ids**(fn.CONCAT("%,", HOrg.pid, ",%"))).join(
HRole,
join_type=JOIN_INNER,
on=Menu.role_ids**(fn.CONCAT(
"%,", HRole.pid,
",%"))).join(HUser,
join_type=JOIN_INNER,
on=((HUser.org_ids**(fn.CONCAT(
"%,", HOrg.pid, ",%"))) &
(HUser.role_ids**(fn.CONCAT(
"%,", HRole.pid, ",%")))
)).where(and_conditions).order_by(
Menu.pid, Menu.sort)
result = [f for f in sql.dicts()]
return result
def _execute(self) -> Tuple[List[PopularItemsOutput]]:
"""
Execute new payment transaction
:return: relevant output information
"""
# Get order table joined with customer table
order_customer_query = (Order.select(
Order.id.alias("order_id"),
Order.district_id.alias("district_id"),
Order.warehouse_id.alias("warehouse_id"),
Order.entry_date.alias("entry_date"),
Customer.middle_name.alias("middle_name"),
Customer.first_name.alias("first_name"),
Customer.last_name.alias("last_name"),
).join(
Customer,
on=((Order.warehouse_id == Customer.warehouse_id)
& (Order.district_id == Customer.district_id)
& (Order.customer_id == Customer.id)),
).where((Order.warehouse_id == self.warehouse_id)
& (Order.district_id == self.district_id)).order_by(
Order.entry_date.desc()).limit(
self.orders_to_examine).cte("order_customer_query"))
# Get order lines with maximum quantity, joined with item table
OrderLineInner: OrderLine = OrderLine.alias()
order_line_sum_qty_query = (OrderLineInner.select(
OrderLineInner.warehouse_id.alias("warehouse_id"),
OrderLineInner.district_id.alias("district_id"),
OrderLineInner.order_id.alias("order_id"),
fn.SUM(OrderLineInner.quantity).alias("sum_qty"),
).where((OrderLineInner.warehouse_id == self.warehouse_id)
& (OrderLineInner.district_id == self.district_id)).group_by(
OrderLineInner.warehouse_id,
OrderLineInner.district_id,
OrderLineInner.order_id,
OrderLineInner.item_id,
).cte("order_line_sum_qty_query"))
order_line_max_qty_query = (order_line_sum_qty_query.select(
order_line_sum_qty_query.c.order_id,
fn.MAX(order_line_sum_qty_query.c.sum_qty),
).group_by(
order_line_sum_qty_query.c.warehouse_id,
order_line_sum_qty_query.c.district_id,
order_line_sum_qty_query.c.order_id,
).with_cte(order_line_sum_qty_query))
customer_name_field = Case(
None,
((
order_customer_query.c.middle_name.is_null(),
fn.CONCAT(
order_customer_query.c.first_name,
" ",
order_customer_query.c.last_name,
),
), ),
fn.CONCAT(
order_customer_query.c.first_name,
order_customer_query.c.middle_name,
order_customer_query.c.last_name,
),
).alias("customer_name")
popular_items_query = (OrderLine.select(
order_customer_query.c.order_id,
order_customer_query.c.entry_date,
customer_name_field,
fn.SUM(OrderLine.quantity).alias("quantity"),
Item.id.alias("item_id"),
Item.name.alias("item_name"),
).join(
order_customer_query,
on=((OrderLine.warehouse_id == order_customer_query.c.warehouse_id)
& (OrderLine.district_id == order_customer_query.c.district_id)
& (OrderLine.order_id == order_customer_query.c.order_id)),
).join(Item, on=(OrderLine.item_id == Item.id)).group_by(
order_customer_query.c.order_id,
order_customer_query.c.entry_date,
customer_name_field,
Item.id,
Item.name,
).having(
DBTuple(order_customer_query.c.order_id, fn.SUM(
OrderLine.quantity)).in_(order_line_max_qty_query)).order_by(
order_customer_query.c.order_id.desc(),
fn.SUM(OrderLine.quantity).desc(),
).with_cte(order_customer_query))
# Process query output
return ([result for result in popular_items_query.dicts()], )