def userTypeEdit(account_id,type,credit_line,session=None):
typeEditSql='update account set type=\'%s\',credit_line=%f where account_id=\'%s\''%(type,credit_line,account_id)
if session is None:
session=sa.get_session()
session.begin()
session.execute(typeEditSql)
session.commit()
def getksuserinfo(account_id, session=None):
if session is None:
session=sa.get_session()
try:
rows=session.execute('select project_id, username, type, salesman_id, company_property, customer_level from account where account_id=\'%s\''%account_id).first()
project_id=rows.project_id
username = rows.username
type = rows.type
salesman_id = rows.salesman_id
company_property = rows.company_property
customer_level = rows.customer_level
ksuserinfo = session.execute('select id, company, telephone,email from keystone.user where default_project_id=:project_id',
dict({'project_id': project_id})).first()
ks_user_id = ksuserinfo.id
ks_company = ksuserinfo.company
ks_telephone = ksuserinfo.telephone
ks_email = ksuserinfo.email
return json.dumps({'success': 'success', 'keystone_user_info':{
'account_id': account_id,
'username': username,
'user_id': ks_user_id,
'type': type,
'company': ks_company,
'company_property': company_property,
'salesman_id': salesman_id,
'customer_level': customer_level,
'email': ks_email,
'telephone': ks_telephone
}})
except Exception as e:
session.close()
LOG.error(str(e))
LOG.error(traceback.format_exc())
return outError("获取keystone客户信息失败！")
def getaccountInfo(account_id,**args):
'''帐号信息获取'''
try:
session=sa.get_session()
result={}
discount_sql=SQL.DiscountListPerRegion
project_id=session.execute('select project_id from account where account_id=\'%s\''%account_id).first().project_id
userIdQuery=session.execute('select id from keystone.user where default_project_id=\'%s\''%project_id)
if userIdQuery:
user_id=userIdQuery.first().id
else:
user_id=0
region_query=session.execute('select region_id from keystone.user_region where user_id=\'%s\''%user_id).fetchall()
region_temp=[]
for each in region_query:
region_temp.append(each.region_id)
region_temp.append("RegionCdn")
for item in region_temp:
if item!='RegionCdn':
result[item]={}
temp=session.execute(discount_sql,dict({'account_id':account_id,'region_id':item})).fetchall()
for each in temp:
if each.discount_ratio and each.billing_item:
result[item][each.billing_item]=float(each.discount_ratio)
temp2=session.execute(discount_sql,dict({'account_id':account_id,'region_id':'RegionCdn'})).fetchall()
for each in temp2:
if each.discount_ratio and each.billing_item:
result[item][each.billing_item]=float(each.discount_ratio)
return json.dumps(result)
except Exception as e:
session.close()
LOG.error(str(e))
LOG.error(traceback.format_exc())
return outError("获取帐号信息失败!")
def getcustomercount(parent_id=None):
sql=SQL.CustomerCount
if parent_id:
sql+= " and parent_id ='%s'"%parent_id
try:
session=sa.get_session()
user=session.execute(sql).fetchall()
total=len(user)
count_frozen=0
count_normal=0
count_deleted=0
for i in user:
if i.status=='frozen':
count_frozen+=1
if i.status=='normal':
count_normal+=1
if i.status=='deleted':
count_deleted+=1
return json.dumps({'success': 'success', 'total': total, 'frozen':count_frozen, 'normal': count_normal, 'deleted': count_deleted})
except Exception as e:
session.close()
LOG.error(str(e))
LOG.error(traceback.format_exc())
return outError("获取客户数量汇总失败!")
def getcontactlist(account_id, session=None):
if session is None:
session=sa.get_session()
sql=SQL.GetContactList
try:
contactlist=session.execute(sql, dict({'account_id':account_id})).fetchall()
result=[]
for eachcontact in contactlist:
temp={}
temp['contact_id']=eachcontact.contact_id
temp['contact_name']=eachcontact.contactname
temp['contact_position']=eachcontact.position
temp['contact_telephone']=eachcontact.telephone
temp['contact_email']=eachcontact.email
temp['created_by']=eachcontact.creator
temp['created_at']=eachcontact.created_at
temp['remark']=eachcontact.remark
result.append(temp)
session.close()
return outSuccess('subAccountList', result)
except Exception as e:
session.close()
LOG.error(str(e))
LOG.error(traceback.format_exc())
return outError("获取联系人列表失败！")
def getrebatesubbillList(condition, likecondition, page_no, page_size):
try:
session = sa.get_session()
temp_list = []
rebate_bill_id = condition['rebate_bill_id']
sql = 'select user.company,temp.* from keystone.user join (select rebate_subbill.*,account.username,account.user_id,account.type from rebate_subbill join account on rebate_bill_id =:rebate_bill_id and rebate_subbill.account_id = account.account_id)as temp on user.id = temp.user_id'
if likecondition:
pass
rows = session.execute(sql, dict({'rebate_bill_id':
rebate_bill_id})).fetchall()
if rows:
getDictFromObj(temp_list, rows, [
'rebate_subbill_id', 'username', 'company', 'rebate_bill_id',
'account_id', 'rebate_amount', 'type', 'amount', 'gift_amount',
'started_at', 'ended_at', 'created_at'
])
count = len(temp_list)
temp_list = temp_list[(page_no - 1) * page_size:(page_no * page_size)]
return json.dumps({
'count': count,
'rebate_subbill_list': temp_list,
'success': 'success'
})
except Exception as e:
session.close()
LOG.error(str(e))
LOG.error(traceback.format_exc())
return outError("获取返现子账户列表失败!")
def getrebatebillitemList(condition, likecondition, page_no, page_size):
try:
session = sa.get_session()
temp_list = []
rebatebillitemDao = rebateBillItemDao.RebateBillItemDao()
rows = rebatebillitemDao.list(condition, likecondition, session)
if rows:
getDictFromObj(temp_list, rows, [
'rebate_bill_item', 'rebate_subbill_id', 'resource_id',
'resource_name', 'resource_type', 'region_id', 'amount',
'gift_amount', 'rebate_amount', 'started_at', 'ended_at',
'created_at'
])
count = len(temp_list)
temp_list = temp_list[(page_no - 1) * page_size:(page_no * page_size)]
return json.dumps({
'count': count,
'rebate_billitem_list': temp_list,
'success': 'success'
})
except Exception as e:
session.close()
LOG.error(str(e))
LOG.error(traceback.format_exc())
return outError("获取返现详情列表失败!")
def getOrderRechargeList(self,
account_id=None,
payment_type=None,
started_at=None,
ended_at=None,
page_no=1,
page_size=15,
session=None):
try:
if not session:
session = sa.get_session()
count = session.execute(
self._getOrderRechargeListCount(account_id, payment_type,
started_at,
ended_at)).first().total_sum
rows = session.execute(
self._getOrderRechargeListSql(account_id, payment_type,
started_at, ended_at,
(page_no - 1) * page_size,
page_size)).fetchall()
result = []
if rows:
for row in rows:
result.append(dict(zip(row.keys(), row.values())))
session.close()
return result, count
except Exception as e:
session.close()
LOG.error(str(e))
raise e
def getDiscountDetail(self, condition=None, session=None):
if not session:
session = sa.get_session()
if condition is None:
return session.query(Discount)
query = session.query(Discount)
if condition:
billingItem = None
for (attr, attrValue) in [(key, value)
for (key, value) in condition.items()]:
if attr == 'account_id':
query = query.filter(Discount.account_id == attrValue)
if attr == 'billing_item_id':
query = query.filter(Discount.billing_item_id == attrValue)
if attr == 'region_id':
if not billingItem:
billingItem = BillingItem()
billingItem.region_id = attrValue
if attr == 'billing_item':
if not billingItem:
billingItem = BillingItem()
billingItem.billing_item = attrValue
if billingItem:
billingItemDao = BillingItemDao(billingItem)
billingItem = billingItemDao.getBillingItem(session)
query = query.filter(Discount.billing_item == billingItem)
row = query.first()
if row:
row.billing_item
return row
def getQueryByCondition(self, condition=None, session=None):
'''条件查询'''
if not session:
session = sa.get_session()
if condition is None:
return session.query(Discount)
query = session.query(Discount)
if condition:
billingItem = None
for (attr, attrValue) in [(key, value)
for (key, value) in condition.items()]:
if attr == 'account_id':
query = query.filter(Discount.account_id == attrValue)
if attr == 'billing_item_id':
query = query.filter(Discount.billing_item_id == attrValue)
if attr == 'region_id':
if not billingItem:
billingItem = BillingItem()
billingItem.region_id = attrValue
if attr == 'billing_item':
if not billingItem:
billingItem = BillingItem()
billingItem.billing_item = attrValue
if billingItem:
billingItemDao = BillingItemDao(billingItem)
billingItem = billingItemDao.getBillingItem(session)
query = query.filter(Discount.billing_item == billingItem)
# if likeCondition:
# for (attr,attrValue) in [(key,value)for (key,value) in likeCondition.items()]:
# if attr=='username':
# query=query.filter(Discount.username.like('%'+attrValue+'%'))
return query
def list(self, condition=None, likeCondition=None, session=None):
'''按条件查询所有的返现账单'''
if not session:
session = sa.get_session()
query = self.getQueryByCondition(condition, likeCondition)
rows = query.all()
return rows
def ProjectAdminsubAccountList(self,
parent_id,
condition=None,
session=None):
if session is None:
session = sa.get_session()
if condition is None:
condition = {}
sql = SQL.subAccountList_business
sql += ' order by created_at desc'
query = session.execute(sql, dict({'parent_id': parent_id},
**condition))
rows = query.fetchall()
count = len(rows)
if condition:
if condition.has_key('limit') and condition.has_key('offset'):
limit = int(condition['limit'])
offset = int(condition['offset'])
rows = rows[(limit * offset):((offset + 1) * limit)]
result = []
for row in rows:
extra = json.loads(row.extra) if row.extra else {}
company = row.company
if company is None and extra.has_key('company'):
company = extra['company']
result.append({'account_id':row.account_id, 'user_id':row.user_id, 'username':row.name, 'email':row.email, 'telephone':row.telephone, \
'cash_balance':row.cash_balance, 'gift_balance':row.gift_balance, 'type':row.type, 'credit_line':row.credit_line, 'company':company,
'role': 'project_subaccount', 'created_at': row.created_at, 'sales': row.sales_name, 'status': row.status})
session.close()
return result, count
def _list_query(self,info):
session = sa.get_session()
sql_sentence="select * from billing.action_log"
sql_condition=[]
condition=info.get("condition")
page=info.get("page")
params={}
if condition.get("dateFrom") :
params["dateFrom"]=condition.get("dateFrom")
sql_condition.append("action_at >=:dateFrom")
if condition.get("dateTo"):
params["dateTo"]=condition.get("dateTo")
sql_condition.append("action_at<=:dateTo")
if condition.get("keyword"):
params["keyword"]=condition.get("keyword")
sql_condition.append("CONCAT_WS(' ',`user_name`,`resource_name`,`resource_type`,`action_name`,`detail`,`status`) like :keyword")
condition=" and ".join(sql_condition)
if condition:
sql_sentence+=" where "+condition
if page.get("offset") and page.get("page_size"):
page_size=int(page.get("page_size"))
offset=int(page.get("offset"))
params["offset"]=offset
params["page_size"]=page_size
sql_sentence+=" LIMIT :page_size OFFSET :offset"
query=session.execute(sql_sentence,params)
return query
def getRebateSubBill(self, account_id, started_at, ended_at, session=None):
if not session:
session = sa.get_session()
query = session.execute(
SQL.get_rebate_subbill_item, {
'started_at': started_at,
'ended_at': ended_at,
'account_id': account_id
})
rows = query.fetchall()
result = []
if rows:
for row in rows:
gift_amount = 0
gift_amount_query = session.execute(
SQL.get_rebate_subbill_giftamount, {
'started_at': started_at,
'ended_at': ended_at,
'account_id': row.account_id
}).first()
if gift_amount_query and gift_amount_query.gift_amount_total:
gift_amount = gift_amount_query.gift_amount_total
result.append({
'account_id': row.account_id,
'parent_account': row.parent_account,
'rebate_amount_total': row.rebate_amount_total,
'amount_total': row.amount_total,
'gift_amount_total': gift_amount
})
session.close()
return result
def get_price_for_nass(self, network_type, bandwidth, distance):
sql = """
SELECT bb.price
FROM (
SELECT
network_type,
cast(SUBSTRING_INDEX(distance, '-', 1) as UNSIGNED) AS dist_lower,
cast(SUBSTRING_INDEX(distance, '-', -1) as UNSIGNED) AS dist_upper,
cast(SUBSTRING_INDEX(bandwidth, '-', 1) as UNSIGNED) AS bw_lower,
cast(SUBSTRING_INDEX(bandwidth, '-', -1) as UNSIGNED) AS bw_upper,
price
FROM
(
SELECT
SUBSTRING_INDEX(billing_item, '_', 1) AS network_type,
substr(SUBSTRING_INDEX(billing_item, '_', 2), length('%s') + 2) AS distance,
SUBSTRING_INDEX(billing_item, '_', -1) AS bandwidth,
price
FROM billing_item
WHERE region_id = 'naas' AND billing_item LIKE '%s%%')
AS aa) AS bb
WHERE %f >= dist_lower and %f <= dist_upper
and
%f >= bw_lower and %f <= bw_upper;
""" % (network_type, network_type, distance, distance, bandwidth,
bandwidth)
return sa.get_session().execute(sql).first()
def getAllProjectAdminAccount(self, session=None):
if session is None:
session = sa.get_session()
sql = '''select temp.* from (select account.*,user.parent_id from account join keystone.user on account.user_id = user.id\
and user.parent_id is not null)as temp join keystone.user on temp.parent_id = user.id and user.name in ('register','admin') '''
rows = session.execute(sql).fetchall()
return rows
def getrechargeDetail(order_no, headers=None, **kwargs):
try:
session = sa.get_session()
account_id = session.execute(
'select account_id from `order` where order_no=\'%s\'' %
order_no).first().account_id
alipay_info = session.execute(
'select pay_account,trade_no,pay_at from alipay_info where order_no=\'%s\''
% order_no).first()
recharge_info = session.execute(
'select created_at,amount,remark from recharge where order_no=\'%s\''
% order_no).first()
account_info = session.execute(SQL.getCompanyInfoByAccount,
dict({'account_id':
account_id})).first()
detaildict = {}
detaildict['trade_no'] = alipay_info.trade_no if alipay_info else None
detaildict['pay_at'] = alipay_info.pay_at if alipay_info else None
detaildict[
'pay_account'] = alipay_info.pay_account if alipay_info else None
detaildict[
'created_at'] = recharge_info.created_at if recharge_info else None
detaildict['amount'] = recharge_info.amount if recharge_info else None
detaildict['remark'] = recharge_info.remark if recharge_info else None
detaildict['company'] = account_info.company if account_info else None
detaildict['phone'] = account_info.telephone if account_info else None
detaildict[
'username'] = account_info.username if account_info else None
return outSuccess('rechargedetail', detaildict)
except Exception as e:
session.close()
LOG.error(str(e))
LOG.error(traceback.format_exc())
return outError("获取充值列表失败!")
def get_account_info(self, account_id, session=None):
'''
获取帐号信息
:param account_id:
:return:
'''
if session is None:
session = sa.get_session()
account_info = {}
account = session.query(Account).filter(
Account.account_id == account_id).first()
if account: #如果存在账户信息
# user_info=self.session.execute(SQL.get_user_info1, {'user_id':account.user_id}).first()
# account_info={key :getattr(user_info,key) for key in set(user_info._parent.keys)&{"username","telephone","email"}}
account.cash_balance = account.cash_balance if account.cash_balance else 0
account.gift_balance = account.gift_balance if account.gift_balance else 0
account.credit_line = account.credit_line if account.credit_line else 0
account_info["account_id"] = account.account_id
account_info["cash_balance"] = account.cash_balance
account_info["gift_balance"] = account.gift_balance
account_info["credit_line"] = account.credit_line
account_info["type"] = account.type
account_info[
"available_balance"] = account.cash_balance + account.gift_balance
account_info[
"total_balance"] = account.cash_balance + account.gift_balance + account.credit_line
account_info[
"available_credit"] = account.credit_line if account.cash_balance >= 0 else account.credit_line + account.cash_balance
return account_info
def getQueryByCondition(self,
condition=None,
likeCondition=None,
session=None):
'''条件查询'''
if not session:
session = sa.get_session()
if condition is None and likeCondition is None:
return session.query(Account)
query = session.query(Account)
if condition:
for (attr, attrValue) in [(key, value)
for (key, value) in condition.items()]:
if attr == 'type':
query = query.filter(Account.type == attrValue)
if attr == 'status':
query = query.filter(Account.status == attrValue)
if likeCondition:
for (attr, attrValue) in [
(key, value) for (key, value) in likeCondition.items()
]:
if attr == 'username':
query = query.filter(
Account.username.like('%' + attrValue + '%'))
return query
def getvalidAdertList():
try:
session = sa.get_session()
temp = AdvertDao()
advert_list_temp = temp.getvalidadvert()
advert_list = []
if advert_list_temp:
for item in advert_list_temp:
tempdict = {}
tempdict['advert_id'] = item.advert_id
tempdict['title'] = item.title
tempdict['url'] = item.url
tempdict['started_at'] = item.started_at
tempdict['ended_at'] = item.ended_at
tempdict['created_at'] = item.created_at
tempdict['updated_at'] = item.updated_at
advert_list.append(tempdict)
count = len(advert_list)
return json.dumps(
{
'count': count,
'valid_advert_list': advert_list,
'success': 'success'
},
cls=CJsonEncoder,
ensure_ascii=False)
except Exception as e:
session.close()
LOG.error(str(e))
LOG.error(traceback.format_exc())
return outError("获取有效公告失败!")
def getNAASForcast(self, account_id, session=None):
if not session:
session = sa.get_session()
query = session.query(
Consumption.account_id.label('account_id'),
Consumption.resource_type.label('resource_type'),
Consumption.parent_id.label('parent_id'),
func.sum(Consumption.amount).label('amount_total'),
func.max(Consumption.discounted_at).label('ended_at'),
func.min(Consumption.discounted_at).label('started_at'))
if account_id:
query = query.filter(Consumption.account_id == account_id)
d1 = datetime.datetime.utcnow()
started_at = (d1 - datetime.timedelta(days=1)).strftime('%Y-%m-%d')
ended_at = d1.strftime('%Y-%m-%d')
query = query.filter(Consumption.resource_type == 'tunnel').filter(
Consumption.started_at == started_at).filter(
Consumption.ended_at == ended_at)
row = query.group_by(Consumption.resource_type == 'tunnel').first()
result = []
if row:
result.append({
'account_id': row.account_id,
'resource_type': row.resource_type,
'parent_id': row.parent_id,
'amount_total': row.amount_total,
'started_at': row.started_at,
'ended_at': row.started_at
})
return result
def getAmountTotal_new_naas(self,
account_id,
started_at,
ended_at,
session=None):
if not session:
session = sa.get_session()
# if isinstance(started_at, datetime.date):
# started_at=datetime.date.strftime(started_at,'%Y-%m-%d')
# if isinstance(ended_at, datetime.date):
# ended_at=datetime.date.strftime(ended_at,'%Y-%m-%d')
query = session.execute(
SQL.bill_naas, {
'account_id': account_id,
'started_at': started_at,
'ended_at': ended_at
})
rows = query.fetchall()
result = []
for row in rows:
result.append({'account_id':row.account_id, 'resource_id':row.resource_id, 'resource_name':row.resource_name\
, '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\
, 'standard_total':row.standard_total, 'gift_total':row.gift_total})
session.close()
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 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 detail(self, session=None):
if not session:
session = sa.get_session()
self.invoice = session.query(Invoice).filter(
Invoice.invoice_id == self.invoice.invoice_id).first()
self.invoice.address
return self.invoice
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 getadvertinfo(self, session=None):
if not session:
session = sa.get_session()
query = session.query(Advert).filter(
Advert.advert_id == self.advert.advert_id)
row = query.first()
return row
def list(self, session=None):
if not session:
session = sa.get_session()
query = session.query(BillingItem)
if self.billingItem and self.billingItem.region_id:
query = query.filter(
BillingItem.region_id == self.billingItem.region_id)
return query.all()
def getvalidadvert(self, session=None):
if not session:
session = sa.get_session()
today = datetime.datetime.now().strftime('%Y-%m-%d') + ' 00:00:00'
query = session.query(Advert).filter(Advert.ended_at >= today)
query = query.filter(Advert.started_at <= today)
rows = query.all()
return rows
def update(self, values, session=None):
if not session:
session = sa.get_session()
session.begin()
self.address = session.query(Address).filter(
Address.address_id == self.address.address_id).first()
self.address.update(values)
session.commit()
