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
