class Versions(db.Model):
"""Model to represent versions table for subscriptions"""
__tablename__ = "versions"
id = db.Column(db.Integer, primary_key=True)
subscription_id = db.Column(db.Integer,
db.ForeignKey("subscriptions.id"),
nullable=False)
plan_id = db.Column(db.Integer, db.ForeignKey("plans.id"), nullable=False)
effective_date_start = db.Column(db.TIMESTAMP(timezone=True))
effective_date_end = db.Column(db.TIMESTAMP(timezone=True))
creation_date = db.Column(db.TIMESTAMP(timezone=True))
subscription = db.relationship("Subscription",
foreign_keys=[subscription_id],
lazy="select",
back_populates="versions")
plan = db.relationship("Plan", foreign_keys=[plan_id], lazy="select")
def __repr__(self):
return (
f"<{self.__class__.__name__}: {self.id}, "
f"effective_date_start: {self.effective_date_start}, effective_date_end: {self.effective_date_end}>, "
f"<subscription: {self.subscription_id}>, "
f"<plan: {self.plan_id}>")
class ATTPlanVersion(db.Model):
"""Model class to represent ATT plan version
Custom versioning class to keep track of plans enabled ATT side
"""
__tablename__ = "att_plan_versions"
id = db.Column(db.Integer, primary_key=True)
subscription_id = db.Column(
db.Integer, db.ForeignKey("subscriptions.id"), nullable=False
)
subscription = db.relationship(
"Subscription", back_populates="att_plan_versions", lazy="select"
)
plan_id = db.Column(
db.String(30), db.ForeignKey("plans.id"), nullable=False
)
plan = db.relationship("Plan", foreign_keys=[plan_id], lazy="select")
start_effective_date = db.Column(db.TIMESTAMP(timezone=True), nullable=False)
end_effective_date = db.Column(db.TIMESTAMP(timezone=True), nullable=False)
mb_available = db.Column(db.BigInteger)
def __repr__(self): # pragma: no cover
return (
f"<{self.__class__.__name__}: {self.subscription_id}, "
f"{str(self.plan_id)} ({self.start_effective_date} - "
f"{self.end_effective_date}) "
)
class SubscriptionServiceChange(db.Model):
"""Model class to keep track of exact datetime when Subscription
service codes were changed (added/removed).
"""
__tablename__ = "subscription_service_changes"
change_id = db.Column("id", db.Integer, primary_key=True)
service_code_id = db.Column(db.Integer,
db.ForeignKey("service_codes.id"),
nullable=False)
subscription_id = db.Column(db.Integer,
db.ForeignKey("subscriptions.id"),
nullable=False)
change_date = db.Column(db.TIMESTAMP(timezone=True),
server_default=db.func.now())
event_type = db.Column(db.Enum("added", "removed"), nullable=False)
subscription = db.relationship(Subscription,
foreign_keys=[subscription_id],
lazy="select")
service_code = db.relationship(ServiceCode,
foreign_keys=[service_code_id],
lazy="select")
def __repr__(self): # pragma: no cover
return (
f"<{self.__class__.__name__}: {self.change_id} "
f"service: {self.service_code_id} was {self.event_type.value} at "
f"{self.change_date}, subscription: {self.subscription_id}>")
class Subscription(db.Model):
"""Model class to represent ATT subscriptions"""
__tablename__ = "subscriptions"
id = db.Column(db.Integer, primary_key=True)
phone_number = db.Column(db.String(10))
status = db.Column(ENUM(SubscriptionStatus),
default=SubscriptionStatus.new)
activation_date = db.Column(db.TIMESTAMP(timezone=True), nullable=True)
expiry_date = db.Column(db.TIMESTAMP(timezone=True), nullable=True)
plan_id = db.Column(db.String(30),
db.ForeignKey("plans.id"),
nullable=False)
plan = db.relationship("Plan", foreign_keys=[plan_id], lazy="select")
service_codes = db.relationship(
"ServiceCode",
secondary=subscriptions_service_codes,
primaryjoin=
"Subscription.id==subscriptions_service_codes.c.subscription_id",
secondaryjoin=
"ServiceCode.id==subscriptions_service_codes.c.service_code_id",
back_populates="subscriptions",
cascade="all,delete",
lazy="subquery")
data_usages = db.relationship(DataUsage, back_populates="subscription")
def __repr__(self): # pragma: no cover
return (f"<{self.__class__.__name__}: {self.id} ({self.status}), "
f"phone_number: {self.phone_number or '[no phone number]'}, ",
f"plan: {self.plan_id}>")
@classmethod
def get_subscriptions(cls, **kwargs):
"""Gets a list of Subscription objects using given kwargs
Generates query filters from kwargs param using base class method
Args:
kwargs: key value pairs to apply as filters
Returns:
list: objects returned from query result
"""
return cls.query.filter(**kwargs).all()
@property
def service_code_names(self):
"""Helper property to return names of active service codes"""
return [code.name for code in self.service_codes]
class DataUsage(db.Model):
"""Model class to represent data usage record
Note:
A daily usage record is created for a subscription each day
it is active, beginning at midnight UTC timezone.
"""
__tablename__ = "data_usages"
id = db.Column(db.Integer, primary_key=True)
mb_used = db.Column(db.Float, default=0.0)
from_date = db.Column(db.TIMESTAMP(timezone=True))
to_date = db.Column(db.TIMESTAMP(timezone=True))
subscription_id = db.Column(
db.Integer, db.ForeignKey("subscriptions.id"), nullable=False
)
subscription = db.relationship("Subscription", back_populates="data_usages")
def __repr__(self): # pragma: no cover
return (
f"<{self.__class__.__name__}: {self.id} ({self.subscription_id}) "
f"{self.mb_used} MB {self.from_date} - {self.to_date}>"
)
class Subscription(db.Model):
"""Model class to represent ATT subscriptions"""
__tablename__ = "subscriptions"
id = db.Column(db.Integer, primary_key=True)
phone_number = db.Column(db.String(10))
status = db.Column(ENUM(SubscriptionStatus), default=SubscriptionStatus.new)
activation_date = db.Column(db.TIMESTAMP(timezone=True), nullable=True)
expiry_date = db.Column(db.TIMESTAMP(timezone=True), nullable=True)
plan_id = db.Column(db.String(30), db.ForeignKey("plans.id"), nullable=False)
plan = db.relationship("Plan", foreign_keys=[plan_id], lazy="select")
def __repr__(self): # pragma: no cover
return (
f"<{self.__class__.__name__}: {self.id} ({self.status}), "
f"phone_number: {self.phone_number or '[no phone number]'}, ",
f"plan: {self.plan_id}>"
)
@classmethod
def get_subscriptions(cls, **kwargs):
"""Gets a list of Subscription objects using given kwargs
Generates query filters from kwargs param using base class method
Args:
kwargs: key value pairs to apply as filters
Returns:
list: objects returned from query result
"""
return cls.query.filter(**kwargs).all()
class ServiceCode(db.Model):
"""Model class to represent service codes"""
__tablename__ = "service_codes"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(50), unique=True, nullable=False)
description = db.Column(db.String(200))
subscriptions = db.relationship(
"Subscription",
secondary=subscriptions_service_codes,
primaryjoin=
"ServiceCode.id==subscriptions_service_codes.c.service_code_id",
secondaryjoin=
"Subscription.id==subscriptions_service_codes.c.subscription_id",
back_populates="service_codes",
cascade="all,delete")
def __repr__(self): # pragma: no cover
return (f"<{self.__class__.__name__}: {self.id}, "
f"{self.name}: ({self.description})>")
@classmethod
def get_data_blocking_code(cls):
"""Gets the data blocking service code"""
return cls.get_one(name=cls.get_data_blocking_code_name())
@classmethod
def get_data_blocking_code_name(cls):
"""Gets the name of the data blocking service code"""
return current_app.config.get("DATA_BLOCKING_CODE")
class SubscriptionVersion(db.Model):
"""Model to represent versioning table for subscriptions"""
__tablename__ = "subscriptions_versions"
id = db.Column(db.Integer, primary_key=True)
subscription_id = db.Column(db.Integer,
db.ForeignKey("subscriptions.id"),
nullable=False)
subscription = db.relationship("Subscription",
foreign_keys=[subscription_id],
lazy="select",
back_populates="versions")
plan_id = db.Column(db.Integer, db.ForeignKey("plans.id"), nullable=False)
plan = db.relationship("Plan", foreign_keys=[plan_id], lazy="select")
date_start = db.Column(db.TIMESTAMP(timezone=True))
date_end = db.Column(db.TIMESTAMP(timezone=True))
date_created = db.Column(db.TIMESTAMP(timezone=True))
class Plan(db.Model):
"""Model class to represent mobile service plans"""
__tablename__ = "plans"
id = db.Column(db.String(30), primary_key=True)
description = db.Column(db.String(200))
# amount of data available for a given billing cycle
mb_available = db.Column(db.BigInteger)
is_unlimited = db.Column(db.Boolean)
versions = db.relationship('SubscriptionVersion', back_populates='plan')
def __repr__(self): # pragma: no cover
return (f"<{self.__class__.__name__}: {self.id} ({self.description})>")
class ServiceCode(db.Model):
"""Model class to represent service codes"""
__tablename__ = "service_codes"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(50), unique=True, nullable=False)
description = db.Column(db.String(200))
subscriptions = db.relationship(
"Subscription",
secondary=subscriptions_service_codes,
primaryjoin=
"ServiceCode.id==subscriptions_service_codes.c.service_code_id",
secondaryjoin=
"Subscription.id==subscriptions_service_codes.c.subscription_id",
back_populates="service_codes",
cascade="all,delete")
def __repr__(self): # pragma: no cover
return (f"<{self.__class__.__name__}: {self.id}, "
f"{self.name}: ({self.description})>")
class Subscription(db.Model):
"""Model class to represent ATT subscriptions"""
__tablename__ = "subscriptions"
id = db.Column(db.Integer, primary_key=True)
phone_number = db.Column(db.String(10))
status = db.Column(ENUM(SubscriptionStatus),
default=SubscriptionStatus.new)
plan_id = db.Column(db.String(30),
db.ForeignKey("plans.id"),
nullable=False)
plan = db.relationship("Plan", foreign_keys=[plan_id], lazy="select")
service_codes = db.relationship(
"ServiceCode",
secondary="subscriptions_service_codes",
primaryjoin=
"Subscription.id==subscriptions_service_codes.c.subscription_id",
secondaryjoin=
"ServiceCode.id==subscriptions_service_codes.c.service_code_id",
back_populates="subscriptions",
cascade="all,delete",
lazy="subquery")
data_usages = db.relationship("DataUsage", back_populates="subscription")
def __repr__(self): # pragma: no cover
return (f"<{self.__class__.__name__}: {self.id} ({self.status}), "
f"phone_number: {self.phone_number or '[no phone number]'}, ",
f"plan: {self.plan_id}>")
@classmethod
def get_subscriptions(cls, **kwargs):
"""Gets a list of Subscription objects using given kwargs
Generates query filters from kwargs param using base class method
Args:
kwargs: key value pairs to apply as filters
Returns:
list: objects returned from query result
"""
return cls.query.filter(**kwargs).all()
def block_subscription(self):
""" Add Data Block service code to this subscription"""
self.service_codes.append(get_object_or_404(ServiceCode, 1))
db.session.commit()
def unblock_subscription(self):
""" Remove Data Block service code from this subscription"""
self.service_codes.remove(get_object_or_404(ServiceCode, 1))
db.session.commit()
def sum_usage(self):
"""Return data usage in gigabytes for this subscription"""
if self.status == "SubscriptionStatus.new":
return 0
from src.models.usages import DataUsage
return DataUsage.query \
.with_entities(func.sum(DataUsage.mb_used) / 1024) \
.filter(DataUsage.subscription_id == self.id) \
.scalar()
def check_overflow(self):
"""Return if the data usage of the subscription is overflow"""
gb_used = self.sum_usage()
if self.plan_id != "3" and self.plan.mb_available / 1024 < float(
0 if gb_used is None else gb_used):
return True
return False
def is_blocked(self):
"""Return if subscription include data block service code"""
for service_code in self.service_codes:
if service_code.id == 1:
return True
return False
class Subscription(db.Model):
"""Model class to represent ATT subscriptions"""
__tablename__ = "subscriptions"
id = db.Column(db.Integer, primary_key=True)
phone_number = db.Column(db.String(10))
status = db.Column(ENUM(SubscriptionStatus),
default=SubscriptionStatus.new)
activation_date = db.Column(db.TIMESTAMP(timezone=True), nullable=True)
expiry_date = db.Column(db.TIMESTAMP(timezone=True), nullable=True)
plan_id = db.Column(db.String(30),
db.ForeignKey("plans.id"),
nullable=False)
plan = db.relationship("Plan", foreign_keys=[plan_id], lazy="select")
service_codes = db.relationship(
"ServiceCode",
secondary=subscriptions_service_codes,
primaryjoin=
"Subscription.id==subscriptions_service_codes.c.subscription_id",
secondaryjoin=
"ServiceCode.id==subscriptions_service_codes.c.service_code_id",
back_populates="subscriptions",
cascade="all,delete",
lazy="subquery")
data_usages = db.relationship(DataUsage, back_populates="subscription")
versions = db.relationship("SubscriptionVersion",
back_populates="subscription",
order_by="SubscriptionVersion.date_created")
def __repr__(self): # pragma: no cover
return (f"<{self.__class__.__name__}: {self.id} ({self.status}), "
f"phone_number: {self.phone_number or '[no phone number]'}, ",
f"plan: {self.plan_id}>")
@classmethod
def get_subscriptions(cls, **kwargs):
"""Gets a list of Subscription objects using given kwargs
Generates query filters from kwargs param using base class method
Args:
kwargs: key value pairs to apply as filters
Returns:
list: objects returned from query result
"""
return cls.query.filter_by(**kwargs).all()
@property
def service_code_names(self):
"""Helper property to return names of active service codes"""
return [code.name for code in self.service_codes]
@classmethod
def get_subscriptions_in_cycle(cls, billing_cycle, subscription_id=None):
"""The function filters subscriptions that have versions within given billing cycle. If subscription_id
argument has been passed the function returns queryset that is filtered with this id.
Args:
billing_cycle (BillingCycle): object of BillingCycle
subscription_id (int, optional): id of Subscription object, default is None
Returns:
sqlalchemy.orm.query.Query: returns queryset for subscription that have versions withing given
billing cycle with joined versions array to every subscription (ordered by date_created).
"""
query = cls.query
if subscription_id is not None:
query = query.filter_by(id=subscription_id)
query = query \
.outerjoin(
SubscriptionVersion,
and_(
Subscription.id == SubscriptionVersion.subscription_id,
SubscriptionVersion.date_start >= billing_cycle.start_date,
SubscriptionVersion.date_end <= billing_cycle.end_date,
),
) \
.options(contains_eager(cls.versions))
return query
class DataUsage(db.Model):
"""Model class to represent data usage record
Note:
A daily usage record is created for a subscription each day
it is active, beginning at midnight UTC timezone.
"""
__tablename__ = "data_usages"
id = db.Column(db.Integer, primary_key=True)
mb_used = db.Column(db.Float, default=0.0)
from_date = db.Column(db.TIMESTAMP(timezone=True))
to_date = db.Column(db.TIMESTAMP(timezone=True))
subscription_id = db.Column(db.Integer,
db.ForeignKey("subscriptions.id"),
nullable=False)
subscription = db.relationship("Subscription",
back_populates="data_usages")
def __repr__(self): # pragma: no cover
return (
f"<{self.__class__.__name__}: {self.id} ({self.subscription_id}) "
f"{self.mb_used} MB {self.from_date} - {self.to_date}>")
@classmethod
def get_statistics_for_a_subscription(cls, sid, date=None):
"""Helper method to get data usage on billing cycle of given date
Args:
sid (int): subscription id to look up
date (date): date to get billing cycle for
Returns:
dict: {
over_limit, true if data usage is over plan limit
amount_used, the amount of data used in megabytes
amount_left amount used over plan limit in megabytes
}
"""
cycle = BillingCycle.get_current_cycle(date)
default_usage = cls.query \
.filter(cls.subscription_id == sid) \
.first()
subscription = default_usage is not None and default_usage.subscription
plan = default_usage is not None and default_usage.subscription.plan
# get total amount used in current cycle
query = []
query.append(cls.subscription_id == sid)
if cycle is not None:
query.append(cls.from_date >= cycle.start_date)
query.append(cls.to_date <= cycle.end_date)
amount = cls.query \
.with_entities(func.sum(cls.mb_used)) \
.filter(*query) \
.scalar()
if plan and subscription and subscription.status != SubscriptionStatus.new:
mb_available = plan.mb_available - amount
else:
mb_available = 0
return {
"over_limit": mb_available <= 0,
"amount_used": amount,
"amount_left": mb_available if mb_available > 0 else 0
}
