def getInvoiceManageDetail(self,
condition=None,
page_no=1,
page_size=5,
edge_size=0,
session=None):
'''发票管理列表详情'''
if not session:
session = sa.get_session()
query = session.query(Invoice)
if condition:
for (attr, attrValue) in [(key, value)
for (key, value) in condition.items()]:
if attr == 'account_id':
query = query.filter(Invoice.account_id == attrValue)
if attr == 'type':
query = query.filter(Invoice.type == attrValue)
if attr == 'status':
query = query.filter(Invoice.status == attrValue)
if attr == 'titlelike':
query = query.filter(
Invoice.title.like('%' + attrValue.replace('_', '\_') +
'%'))
pagination = Pagination(query)
result = pagination.paginate(page_no, page_size, edge_size)
if result:
for invoice in result:
invoice.address
return result
def getInvoiceByPage(self,
condition=None,
page_no=1,
page_size=15,
edge_size=0,
session=None):
''' 分页查询'''
if not session:
session = sa.get_session()
query = session.query(Invoice)
if condition:
for (attr, attrValue) in [(key, value)
for (key, value) in condition.items()]:
if attr == 'account_id':
query = query.filter(Invoice.account_id == attrValue)
if attr == 'type':
query = query.filter(Invoice.type == attrValue)
if attr == 'status':
query = query.filter(Invoice.status == attrValue)
pagination = Pagination(query)
result = pagination.paginate(page_no, page_size, edge_size)
if result:
for invoice in result:
invoice.address
return result
def list(self,
account_id,
started_at=None,
ended_at=None,
page_no=1,
page_size=15,
edge_size=0,
isBillItems=False,
session=None,
bill_type=None):
"""账单列表"""
if not session:
session = sa.get_session()
query = session.query(Bill)
if account_id:
query = query.filter(Bill.account_id == account_id)
if started_at:
if isinstance(started_at, str):
started_at = datetime.datetime.strptime(
started_at, '%Y-%m-%d %H:%M:%S')
query = query.filter(Bill.started_at >= started_at)
if ended_at:
if isinstance(ended_at, str):
ended_at = datetime.datetime.strptime(ended_at,
'%Y-%m-%d %H:%M:%S')
query = query.filter(Bill.ended_at <= ended_at)
if bill_type:
query = query.filter(Bill.type == bill_type)
pagination = Pagination(query)
rows = pagination.paginate(page_no, page_size, edge_size)
if isBillItems and rows:
for row in rows:
row.bill_items
return rows
def getAccountByPage(self,
query=None,
page_no=1,
page_size=15,
edge_size=0,
session=None):
''' 分页查询'''
if not session:
session = sa.get_session()
if query is None:
query = self.getQuery(session)
pagination = Pagination(query)
return pagination.paginate(page_no, page_size, edge_size)
def list(self, info):
'''
info
列表
:return:
'''
try:
query = self.session.query(self.model_class)
query = self._list_query(query, info)
if "page" in info:
records = Pagination(query).paginate(**info["page"])
total = records.total
else:
records = query.all()
total = len(records)
return [self.getDictFromObj_nr(item) for item in records], total
except Exception as e:
LOG.error(str(e))
raise e
def list_by_me(self, info):
'''
所有被我处理的工单
'''
try:
current_user = info["user"]
query = self.session.query(self.model_class).filter(
self.model_class.records.any(
WorkOrderRecord.record_by == current_user))
query = self._list_query(query, info)
if "page" in info:
records = Pagination(query).paginate(**info["page"])
total = records.total
else:
records = query.all()
total = len(records)
return [
self.getDictFromObj_rp(item, {"type": "name"})
for item in records
], total
except Exception as e:
LOG.error(str(e))
raise e
def getAmountTotal(self,
account_id,
started_at,
ended_at,
region_id=None,
resource_type=None,
page_no=1,
page_size=15,
edge_size=0,
session=None):
'''取得消费明细汇总'''
if not session:
session = sa.get_session()
query = session.query(Consumption.account_id.label("account_id"), Consumption.resource_id.label("resource_id"),Consumption.sum.label('sum'), Consumption.resource_name.label("resource_name"), Consumption.region_id.label("region_id")\
, Consumption.resource_type.label("resource_type"), Consumption.parent_id.label("parent_id"), func.sum(Consumption.amount).label("amount_total"), func.max(Consumption.ended_at).label("ended_at"), func.min(Consumption.started_at).label("started_at"))
if account_id:
query = query.filter(Consumption.account_id == account_id)
if region_id:
query = query.filter(Consumption.region_id == region_id)
if resource_type:
if resource_type not in ('instance', 'cdn'):
query = query.filter(
Consumption.resource_type == resource_type)
else:
if resource_type == 'instance':
query = query.filter(
or_(Consumption.resource_type == resource_type,
Consumption.resource_type == 'cpu',
Consumption.resource_type == 'memory'))
if resource_type == 'cdn':
query = query.filter(
or_(Consumption.resource_type == 'cdnflow',
Consumption.resource_type == 'cdnbandwidth'))
if started_at is not None:
query = query.filter(Consumption.started_at >= started_at)
if ended_at is not None:
query = query.filter(Consumption.ended_at <= ended_at)
query_instance = query
query = query.filter(not_(Consumption.resource_type == 'cpu')).filter(
not_(Consumption.resource_type == 'memory'))
# rows = query.group_by(Consumption.account_id, Consumption.resource_id).order_by(Consumption.region_id).all()
query = query.group_by(Consumption.account_id,
Consumption.resource_id).order_by(
Consumption.region_id)
pagination = Pagination(query)
result = pagination.paginate(page_no, page_size, edge_size)
pageResult = PageResult(result.total, page_no, page_size, edge_size)
for row in result:
pageResult.append({'account_id':row.account_id, 'resource_id':row.resource_id, 'resource_name':row.resource_name if row.resource_name else row.sum\
, 'region_id':row.region_id, 'resource_type':row.resource_type, 'parent_id':row.parent_id\
, 'amount_total':row.amount_total, 'started_at':row.started_at, 'ended_at':row.ended_at})
if row.resource_type == 'instance':
query_cpu_memory = query_instance
query_cpu_memory = query_cpu_memory.filter(
Consumption.parent_id == row.resource_id)
query_cpu_memory = query_cpu_memory.group_by(
Consumption.account_id,
Consumption.resource_id).order_by(Consumption.region_id)
cpu_memory_rows = query_cpu_memory.all()
for cpu_memory_row in cpu_memory_rows:
pageResult.append({'account_id':cpu_memory_row.account_id, 'resource_id':cpu_memory_row.resource_id, 'resource_name':cpu_memory_row.resource_name\
, 'region_id':cpu_memory_row.region_id, 'resource_type':cpu_memory_row.resource_type, 'parent_id':cpu_memory_row.parent_id\
, 'amount_total':cpu_memory_row.amount_total, 'started_at':cpu_memory_row.started_at, 'ended_at':cpu_memory_row.ended_at})
session.close()
return pageResult