def get_cluster_details(subd): with session_context() as s: q = s.query(ClusterToSubdomain)\ .filter(ClusterToSubdomain.subdomain == subd) datas = q.one() s.expunge_all() return datas
def query_product_state(states, products, start, end): with session_context() as s: q = state_query(s, states, start, end) q = q.join( Tier, aliased=True )\ .filter( or_(( Tier.name.__eq__( product ) for product in products )) ) subs = q.all() s.expunge_all() return subs
def get_subd_names(subd): subdexp = '%' + subd + '%' querytorun = 'select distinct subdomain from accounts a where subdomain like :subexp limit 12' with session_context() as q: try: q = engine.execute(text(querytorun), subexp=subdexp) return q except NoResultFound as e: return None
def query_subd_from_cluster(cluster): with session_context() as s: q = s.query(ClusterToSubdomain)\ .filter(ClusterToSubdomain.cluster_type == cluster) datas = q.all() s.expunge_all() return datas
def find_downgrade_weights(): with session_context() as s: dg = s.query(DownGrades).filter( DownGrades.down_dt + 30 >= date.today()).all() s.expunge_all riskarray = [{ 'subdomain': g.subdomain, 'weight': g.down_wt } for g in dg] return riskarray
def fixtures(): teardown_module() adb.Base.metadata.create_all(bind=adb.engine, tables=[ adb.Users.__table__, adb.UserTracking.__table__, adb.Status.__table__, ]) with adb.session_context() as s: populate(s, model_names=[ 'Users', 'UserTracking', 'Status', ])
def fixtures(): teardown_module() adb.Base.metadata.create_all( bind=adb.engine, tables = [ adb.AccountProfile.__table__, ] ) with adb.session_context() as s: populate( s, model_names=[ 'AccountProfile', ] )
def account_history(subd): with session_context() as s: subs = s.query( AccountState )\ .filter( AccountState.subdomain == subd )\ .order_by( AccountState.updated.desc() )\ .all() fsubs = [{ 'Date': r.updated, 'Rate': r.tRate, 'Tier': getattr(r.tPlan, 'name', 'custom'), 'Volume': r.tGB, 'Retention': r.tDays } for r in subs] return fsubs
def get_subscription_rate(subd): acct_id = acct_id_for_subdomain(subd) with session_context() as s: subsdata = s.query(label('Last Change Date', func.max(AccountActivity.created)), label('current_subs', AccountActivity.to_vol_bytes))\ .filter(AccountActivity.acct_id == acct_id).group_by(AccountActivity.acct_id).one() subsrate = subsdata.current_subs s.expunge_all averagedata = get_total_recvd(acct_id) if subsrate == 0: return 0 percsentdata = float(averagedata) / subsrate if percsentdata >= .8: return 0 elif percsentdata >= .6: return 5 else: return 8
def qtime(model, subd, start, end): with session_context() as s: subq = s.query( Account )\ .filter( Account.subdomain == subd )\ .subquery() q = s.query( model )\ .filter( model.acct_id == subq.c.acct_id )\ .order_by( model.date ) if start: q = q.filter(model.date >= start) if end: q = q.filter(model.date <= end) res = q.all() return {item.date: item.bytes for item in res}
def _query_owners(self, states, start, end, owners=None): with session_context() as s: subq = state_query(s, states, start, end).subquery() # aliased_subq = aliased( AccountState, subq ) # q = s.query( Owners, aliased_subq )\ q = s.query( Owners, subq )\ .join( subq, and_( Owners.acct_id == subq.c.acct_id, Owners.subdomain == subq.c.subdomain, func.DATE( subq.c.updated ) >= Owners.start_date, func.DATE( subq.c.updated ) <= Owners.end_date )) # If we're only querying for specific users then apply the or'd constraints if owners != None: q.filter( reduce( lambda query, func: query | func, [ Owners.owner == owner for owner in owners] ) ) \ subs = q.all() s.expunge_all() return subs
def fixtures(): # In case the test DB was in a bad state teardown_module() adb.Base.metadata.create_all(bind=tbz.engine, tables=[ adb.Owners.__table__, adb.AAWSC.__table__, ]) with adb.session_context() as s: populate(s, model_names=['Owners', 'AAWSC']) tbz.Base.metadata.create_all(bind=tbz.engine, tables=[ tbz.SalesReps.__table__, tbz.Touchbiz.__table__, ]) with tbz.session_context() as s: populate(s, model_names=['SalesReps'])
def get_user_count(subd): acct_id = acct_id_for_subdomain(subd) try: with session_context() as s: userdata = s.query(label('activecount',func.count(distinct(UserTracking.user_id))))\ .filter(Users.user_id == UserTracking.user_id)\ .filter(UserTracking.login + 30 >= date.today())\ .filter(Users.acct_id == acct_id).one() usercnt = int(userdata.activecount) u = s.query(AccountProfile).filter( AccountProfile.subdomain == subd).one() ma = int(u.usercount) userpercent = float(usercnt) / ma s.expunge_all if userpercent >= .8: return 0 elif userpercent >= .6: return 5 else: return 8 except NoResultFound: return 8
def fixtures(): teardown_module() tbz.create_touchbiz_tables() adb.Base.metadata.create_all(bind=tbz.engine, tables=[ adb.AccountStateUncompressed.__table__, adb.AAWSC.__table__, adb.Tier.__table__, adb.Account.__table__, adb.AccountActivity.__table__, adb.AAOwner.__table__, ]) with tbz.session_context() as s: populate(s, model_names=['SalesReps', 'Touchbiz']) with adb.session_context() as s: populate(s, model_names=[ 'AccountStateUncompressed', 'AAWSC', 'Tier', 'Account', 'AccountActivity' ])
def query_client_state(subd): with session_context() as s: clients = query_client_data(s, subd).one() s.expunge_all() return clients
def get_total_recvd(acctid): with session_context() as s: smdata = s.query(label('total_bytes', func.sum(VolumeAccepted.bytes)))\ .filter(VolumeAccepted.date + 30 >= date.today())\ .filter(VolumeAccepted.acct_id == acctid).one() return smdata.total_bytes / 30
def query_state(states, start, end, g2only=True): with session_context() as s: subs = state_query(s, states, start, end, g2only=g2only).all() s.expunge_all() return subs