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()