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 BillingCycle(db.Model):
"""Model class to represent billing cycle dates
NOTE: this is probably temporary....
"""
__tablename__ = "billing_cycles"
id = db.Column(db.Integer, primary_key=True)
start_date = db.Column(db.TIMESTAMP(timezone=True))
end_date = db.Column(db.TIMESTAMP(timezone=True))
def __repr__(self): # pragma: no cover
return (f"<{self.__class__.__name__}: {self.id}, "
f"start_date: {self.start_date}, end_date: {self.end_date}>")
@classmethod
def get_current_cycle(cls, date=None):
"""Helper method to get current billing cycle of given date
Args:
date (date): date to get billing cycle for
Returns:
object: billing cycle object, if any
"""
if not date:
date = datetime.now()
return cls.query.filter(cls.start_date <= date,
cls.end_date > date).first()
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 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 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 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 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 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
}