from sqlalchemy import select
from sqlalchemy import types
from sqlalchemy.sql import expression
class DashboardPMOrder(Base):
"""Dashboard PM: Total of Order by workflow state."""
__table__ = total_order_project
__mapper_args__ = {'primary_key': [total_order_project.c.absolute_url]}
__session__ = Session
delivered_orders_pm = select([
func.CONCAT('/projects/',
expression.cast(Project.id,
types.Unicode)).label('absolute_url'),
Project.title,
func.count(distinct(Order.id)).label('total'),
func.sum(
case([(and_(Order.state == 'delivered',
Order.accept_date.is_(None)), 1)],
else_=0)).label('newly_delivered'),
func.sum(
case([(and_(Order.state.in_(
('refused', 'in_qa')), Order.accept_date.isnot(None)), 1)],
else_=0)).label('further_revision'),
func.sum(
case([(and_(Order.state == 'delivered',
Order.accept_date.isnot(None)), 1)],
else_=0)).label('re_delivered'),
GEO_LAYOUT = go.layout.Geo(fitbounds="locations",
visible=False,
projection={"type": "mercator"})
HEIGHT = 800
DataType = Optional[Union[dt.datetime, str]]
log = logging.getLogger(__name__)
map_data_region = data.get_italy_map(selection="regions")
map_data_province = data.get_italy_map(selection="provinces")
concat = func.CONCAT(
db.ItalyProvince.denominazione_provincia,
" (",
db.ItalyProvince.sigla_provincia,
"): ",
db.ItalyProvinceCase.totale_casi,
)
def generate_map_region(value: str, data: DataType = None) -> go.Figure:
"""Generate the map region for the given data showing the given value
Arguments:
value {str} -- Value to visualize
Keyword Arguments:
data {Optional[dt.date]} -- Date to visualize (default: {None})
Returns:
def card_query():
args = utility.get_args(request)
draw = args.get('draw', "") # 这个值作者会直接返回给前台
start = args.get('start', "") # 从多少开始
length = args.get('length', "") # 数据长度
search = args.get('search[value]', "") # 获取前台传过来的过滤条件
order_column = args.get('order[0][column]', "") # 哪一列排序，从0开始
order_dir = args.get('order[0][dir]', "") # asc desc 升序或者降序
order_name = args.get('columns[' + order_column + '][name]',
"") # 需要html里定义name
'''
SELECT * FROM
(SELECT cards.*, admins.user_name AS admin_name, my_card_types.card_type_name FROM `cards` LEFT JOIN `admins` ON cards.admin_id = admins.id
LEFT JOIN (SELECT card_types.*,CONCAT(softwares.name, '(', CAST(card_types.day AS CHAR), '天卡)') AS card_type_name FROM card_types
LEFT JOIN softwares ON card_types.software_id = softwares.id) AS my_card_types ON cards.type_id = my_card_types.id) AS t1
WHERE CONCAT_WS(',',`card_no`,`remark`,`admin_name`,`card_type_name`)
LIKE '%"..search.."%' "..order.." LIMIT "..start..", "..length
'''
# 点卡需要用户间彼此隔离
cards = []
recordsTotal = 0
if admin_permission.can():
cards = db.session.query(Card).join(Card.admin).join(
Card.card_type).join(CardType.software).filter(
func.CONCAT_WS(
',', Card.card_no, Card.remark, Admin.user_name,
func.CONCAT(
Software.name, "(", CardType.day,
'天卡)')).like('%' + search + '%')).order_by(
text(order_name + " " +
order_dir)).offset(start).limit(length).all()
recordsTotal = db.session.query(func.count(Card.id)).scalar()
else:
cards = db.session.query(Card).join(Card.admin).join(
Card.card_type).join(CardType.software).filter(
or_(Admin.id == current_user.id,
Admin.superior_id == current_user.id)).filter(
func.CONCAT_WS(
',', Card.card_no, Card.remark, Admin.user_name,
func.CONCAT(
Software.name, "(", CardType.day,
'天卡)')).like('%' + search + '%')).order_by(
text(order_name + " " + order_dir)).offset(
start).limit(length).all()
recordsTotal = db.session.query(func.count(Card.id)).join(
Card.admin).join(Card.card_type).join(CardType.software).filter(
or_(Admin.id == current_user.id,
Admin.superior_id == current_user.id)).scalar()
recordsFiltered = recordsTotal if search == "" else len(cards)
data = []
for card in cards:
item = [
card.id, card.admin.user_name,
card.card_type.get_name(), card.card_no, card.password,
card.remark, card.is_used, card.is_enable,
card.create_time + timedelta(days=card.card_type.expired_day),
card.update_time, card.create_time
]
data.append(item)
result = {
"draw": draw,
"recordsTotal": recordsTotal,
"recordsFiltered": recordsFiltered,
"data": data
}
return utility.get_json(result)
def re_volume(x):
return {
Volume.IDS: func.INSTR(','+",".join(sid)+',',func.CONCAT(',',Goods.id,',')),
Volume.PRICE: Goods.price,
}.get(x, 0)