class Subscription(db.Model):
__tablename__ = 'subscription'
id = db.Column(db.Integer(11), primary_key=True, nullable=False)
user_id = db.Column(db.Integer(11),
db.ForeignKey('user.id'),
nullable=False)
plan_id = db.Column(db.Integer(11),
db.ForeignKey('plan.id'),
nullable=False)
active = db.Column(db.Boolean, nullable=False, default=False)
start_date = db.Column(db.DateTime, nullable=True)
end_date = db.Column(db.DateTime, nullable=True)
stripe_data = db.Column(db.UnicodeText(4294967295),
nullable=False,
default=u'{}')
updated_at = db.Column(db.DateTime, nullable=False, default=datetime.now)
created_at = db.Column(db.DateTime,
nullable=False,
default=datetime.now,
onupdate=datetime.now)
# relations
transactions = db.relation('Transaction',
backref=db.backref('subscription',
lazy='joined',
uselist=False))
class InvoiceLink(db.Model):
__tablename__ = 'invoice_link'
#column definitions
id = db.Column(db.Integer(11), primary_key=True, nullable=False)
user_id = db.Column(db.Integer(11),
db.ForeignKey('user.id'),
nullable=False)
invoice_id = db.Column(db.Integer(11),
db.ForeignKey('invoice.id'),
nullable=False)
link = db.Column(db.Unicode(25), default=u'', nullable=False)
link_hash = db.Column(db.Unicode(50), nullable=False)
created_at = db.Column(db.DateTime, default=datetime.now)
def generate_link_code(self):
code = random_id(8)
while True:
obj = InvoiceLink.query.filter_by(link=code).first()
if not obj:
break
else:
code = random_id(8)
self.link = code
return self.link
def get_url(self):
url = 'http://%s.%s/portal/invoice/%s' % (
self.user.username, current_app.config['SERVER_NAME'], self.link)
return url
class Company(db.Model):
__tablename__ = 'company'
id = db.Column(u'id', db.BigInteger, primary_key=True, nullable=False)
user_id = db.Column(u'user_id',
db.BigInteger,
db.ForeignKey('user.id'),
nullable=False)
logo_id = db.Column(u'logo_id',
db.BigInteger,
db.ForeignKey('logo.id'),
nullable=False)
company_type_id = db.Column(u'company_type_id',
db.BigInteger,
db.ForeignKey('company_type.id'),
nullable=False)
name = db.Column(u'name', db.Unicode(length=255))
address1 = db.Column(u'address1', db.Unicode(length=255))
address2 = db.Column(u'address2', db.Unicode(length=255))
town = db.Column(u'town', db.Unicode(length=100))
city = db.Column(u'city', db.Unicode(length=100))
county = db.Column(u'county', db.Unicode(length=255))
country = db.Column(u'country', db.Unicode(length=2))
post_code = db.Column(u'post_code', db.Unicode(length=20))
registration_number = db.Column(u'registration_number',
db.Unicode(length=20))
created_at = db.Column(u'created_at',
db.DateTime,
nullable=False,
default=get_current_time())
updated_at = db.Column(u'updated_at',
db.DateTime,
nullable=False,
default=get_current_time())
# user
user = db.relation('User',
primaryjoin='Company.user_id==User.id',
backref=db.backref('company', uselist=False))
# logo
logo = db.relation('Logo', primaryjoin='Company.logo_id==Logo.id')
# account type
company_type = db.relation('CompanyType',
backref=db.backref('companies', lazy='dynamic'))
class Contact(db.Model):
__tablename__ = 'contact'
#column definitions
id = db.Column(u'id', db.BigInteger, primary_key=True, nullable=False)
user_id = db.Column(u'user_id', db.BigInteger, db.ForeignKey('user.id'), nullable=False)
first_name = db.Column(u'first_name', db.String(length=255))
last_name = db.Column(u'last_name', db.String(length=255))
organisation = db.Column(u'organisation', db.String(length=255))
email_address = db.Column(u'email_address', db.String(length=255))
billing_email_address = db.Column(u'billing_email_address', db.String(length=255))
address_line1 = db.Column(u'address_line1', db.String(length=255))
address_line2 = db.Column(u'address_line2', db.String(length=255))
town = db.Column(u'town', db.String(length=255))
city = db.Column(u'city', db.String(length=255))
county = db.Column(u'county', db.String(length=255))
country = db.Column(u'country', db.String(length=2))
post_code = db.Column(u'post_code', db.String(length=15))
created_at = db.Column(u'created_at', db.DateTime, nullable=False,
default=get_current_time())
updated_at = db.Column(u'updated_at', db.DateTime, nullable=False,
default=get_current_time())
#relation definitions
user = db.relation('User', primaryjoin='Contact.user_id==User.id', backref='contacts')
def full_name(self):
return self.first_name + ' ' + self.last_name
class StripePayment(db.Model):
__tablename__ = 'stripe_payment'
id = db.Column(db.Integer(11), primary_key=True, nullable=False)
user_id = db.Column(db.Integer(11),
db.ForeignKey('user.id'),
nullable=False)
payment_id = db.Column(db.Integer(11),
db.ForeignKey('payment.id'),
nullable=True)
invoice_id = db.Column(db.Integer(11),
db.ForeignKey('invoice.id'),
nullable=False)
amount = db.Column(db.Numeric(8, 2), default=0)
token = db.Column(db.Unicode(100), nullable=False)
state = db.Column(db.Unicode(20), default=u'initialized')
error_message = db.Column(db.Unicode(255), nullable=True)
charge_id = db.Column(db.Unicode(100), nullable=True)
charge = db.Column(db.UnicodeText(4294967295),
nullable=False,
default=u'{}')
created_at = db.Column(db.DateTime(), nullable=False, default=datetime.now)
@classmethod
def get_or_create_for_user(cls, user_id):
account = cls.query.filter_by(user_id=user_id).first()
if not account:
model = cls()
model.user_id = user_id
db.session.add(model)
db.session.commit()
return account
def create_payment_object(self):
"""Create related payment object"""
payment = Payment()
payment.invoice_id = self.invoice_id
payment.date = datetime.now()
payment.currency_code = self.invoice.currency_code
payment.amount = self.amount
payment.method = 'stripe'
payment.description = 'Credit card payment'
db.session.add(payment)
db.session.commit()
return payment
class Logo(db.Model):
__tablename__ = 'logo'
id = db.Column(u'id', db.BigInteger, primary_key=True, nullable=False)
original_image_id = db.Column(u'original_image_id',
db.BigInteger,
db.ForeignKey('file.id'),
nullable=False)
thumbnail_image_id = db.Column(u'thumbnail_image_id',
db.BigInteger,
db.ForeignKey('file.id'),
nullable=False)
original = db.relation('File',
primaryjoin='Logo.original_image_id==File.id',
backref=db.backref('original', uselist=False))
thumbnail = db.relation('File',
primaryjoin='Logo.thumbnail_image_id==File.id',
backref=db.backref('thumbnail', uselist=False))
class Transaction(db.Model):
__tablename__ = 'transaction'
id = db.Column(db.Integer(11), primary_key=True, nullable=False)
user_id = db.Column(db.Integer(11),
db.ForeignKey('user.id'),
nullable=False)
subscription_id = db.Column(db.Integer(11),
db.ForeignKey('subscription.id'),
nullable=False)
success = db.Column(db.Boolean, default=False)
amount = db.Column(db.Numeric(8, 2), default=0)
charge_id = db.Column(db.Unicode(100), nullable=True)
charge = db.Column(db.UnicodeText(4294967295),
nullable=True,
default=u'{}')
updated_at = db.Column(db.DateTime, nullable=False, default=datetime.now)
created_at = db.Column(db.DateTime,
nullable=False,
default=datetime.now,
onupdate=datetime.now)
class CustomField(db.Model):
__tablename__ = 'custom_field'
id = db.Column(u'id', db.BigInteger, primary_key=True, nullable=False)
user_id = db.Column(u'user_id',
db.Integer,
db.ForeignKey('user.id'),
nullable=False)
name = db.Column(u'name', db.Unicode(length=255))
value = db.Column(u'value', db.Unicode(length=255))
# user
user = db.relation('User', backref=db.backref('custom_fields'))
class GoCardlessPayment(db.Model):
__tablename__ = 'gocardless_payment'
id = db.Column(db.Integer(11), primary_key=True, nullable=False)
invoice_id = db.Column(db.Integer(11),
db.ForeignKey('invoice.id'),
nullable=False)
user_id = db.Column(db.Integer(11),
db.ForeignKey('user.id'),
nullable=False)
payment_id = db.Column(db.Integer(11),
db.ForeignKey('payment.id'),
nullable=True)
amount = db.Column(db.Numeric(8, 2), default=0)
reference = db.Column(db.Unicode(100), nullable=False)
state = db.Column(db.Unicode(20), default=u'initialized')
resource_id = db.Column(db.Unicode(100), nullable=True)
resource_uri = db.Column(db.Unicode(255), nullable=True)
error_message = db.Column(db.Unicode(255), nullable=True)
created_at = db.Column(db.DateTime(), nullable=False, default=datetime.now)
def create_payment_object(self):
"""Create related payment object"""
payment = Payment()
payment.invoice_id = self.invoice_id
payment.date = datetime.now()
payment.currency_code = self.invoice.currency_code
payment.amount = self.amount
payment.method = 'gocardless'
payment.description = 'Direct debit payment'
db.session.add(payment)
db.session.commit()
return payment
class Payment(db.Model):
__tablename__ = 'payment'
id = db.Column(db.BigInteger, primary_key=True, nullable=False)
invoice_id = db.Column(db.BigInteger, db.ForeignKey('invoice.id'))
date = db.Column(db.DateTime, default=datetime.now, nullable=False)
currency_code = db.Column(db.Unicode(3), nullable=False)
method = db.Column(db.Unicode(50), nullable=False)
description = db.Column(db.Unicode(255), nullable=True)
amount = db.Column(db.Numeric(8, 2), default=0, nullable=False)
created_at = db.Column(db.DateTime, default=datetime.now, nullable=False)
#relation definitions
invoice = db.relation('Invoice',
primaryjoin='Payment.invoice_id==Invoice.id',
backref=db.backref('payments', lazy='dyanmic'))
class Setting(db.Model):
__tablename__ = 'setting'
id = db.Column(db.BigInteger, primary_key=True, nullable=False)
user_id = db.Column(db.BigInteger, db.ForeignKey('user.id'))
key = db.Column(db.Unicode(100))
value = db.Column(db.BLOB())
created_at = db.Column(db.DateTime, default=datetime.now)
updated_at = db.Column(db.DateTime, default=datetime.now)
def set_value(self, value):
self.value = json.dumps(value)
def get_value(self):
return json.loads(self.value)
class StripeAccount(db.Model):
__tablename__ = 'stripe_account'
id = db.Column(db.Integer(11), primary_key=True, nullable=False)
user_id = db.Column(db.Integer(11), db.ForeignKey('user.id'))
secret_key = db.Column(db.Unicode(255))
public_key = db.Column(db.Unicode(255))
enabled = db.Column(db.Boolean, default=False)
updated_at = db.Column(db.DateTime(), nullable=False, default=datetime.now)
created_at = db.Column(db.DateTime(), nullable=False, default=datetime.now)
@classmethod
def get_or_create_for_user(cls, user_id):
account = cls.query.filter_by(user_id=user_id).first()
if not account:
model = cls()
model.user_id = user_id
db.session.add(model)
db.session.commit()
return account
class GoCardlessAccount(db.Model):
__tablename__ = 'gocardless_account'
id = db.Column(db.Integer(11), primary_key=True, nullable=False)
user_id = db.Column(db.Integer(11), db.ForeignKey('user.id'))
app_identifier = db.Column(db.Unicode(255))
app_secret = db.Column(db.Unicode(255))
merchant_access_token = db.Column(db.Unicode(255))
merchant_id = db.Column(db.Unicode(255))
enabled = db.Column(db.Boolean, default=False)
updated_at = db.Column(db.DateTime(), nullable=False, default=datetime.now)
created_at = db.Column(db.DateTime(), nullable=False, default=datetime.now)
@classmethod
def get_or_create_for_user(cls, user_id):
account = cls.query.filter_by(user_id=user_id).first()
if not account:
model = cls()
model.user_id = user_id
db.session.add(model)
db.session.commit()
return account
class TaxRate(db.Model):
__tablename__ = 'tax_rate'
id = db.Column(u'id', db.BigInteger, primary_key=True, nullable=False)
user_id = db.Column(u'user_id', db.BigInteger, db.ForeignKey('user.id'))
type = db.Column(u'type', db.String(255))
name = db.Column(u'name', db.String(255))
rate = db.Column(u'rate', db.Numeric(8, 2), nullable=True)
updated_at = db.Column(u'updated_at',
db.DateTime(),
nullable=False,
default=get_current_time())
created_at = db.Column(u'created_at',
db.DateTime(),
nullable=False,
default=get_current_time())
user = db.relation('User', lazy='select', backref='tax_rates')
def serialize(self):
return model_to_dict(self)
class InvoiceItem(db.Model):
__tablename__ = 'invoice_item'
id = db.Column(u'id', db.BigInteger, primary_key=True, nullable=False)
invoice_id = db.Column(u'invoice_id', db.BigInteger,
db.ForeignKey('invoice.id'))
type_id = db.Column(u'type_id', db.BigInteger,
db.ForeignKey('invoice_item_type.id'))
tax_rate_id = db.Column(u'tax_rate_id', db.BigInteger,
db.ForeignKey('tax_rate.id'))
description = db.Column(u'description', db.String)
quantity = db.Column(u'quantity', db.Numeric(8, 2))
price = db.Column(u'price', db.Numeric(8, 2))
tax = db.Column(u'tax', db.Numeric(8, 2))
total = db.Column(u'total', db.Numeric(8, 2))
sort_order = db.Column(u'sort_order', db.Integer, default=0)
#relation definitions
invoice_item_type = db.relation(
'InvoiceItemType',
primaryjoin='InvoiceItem.type_id==InvoiceItemType.id')
invoice = db.relation('Invoice',
primaryjoin='InvoiceItem.invoice_id==Invoice.id',
backref=db.backref('invoice_items', lazy='dyanmic'))
tax_rate = db.relation('TaxRate',
primaryjoin='InvoiceItem.tax_rate_id==TaxRate.id')
def should_render_field(self, name):
"""Returns True if the field should be rendered in the item list on the
invoice"""
no_render = {
'Comment': ['quantity', 'price', 'tax', 'total'],
'VAT': ['quantity']
}
type_name = self.invoice_item_type.name
if not type_name in no_render:
return True
if name in no_render[type_name]:
return False
return True
def quantity_str(self):
"""Returns a more sanely formatted quantity string, taking into account
the type of the item that we're rendering"""
if self.invoice_item_type.name == 'Hour':
mins = self.quantity * 60
hours = 0
while mins >= 60:
mins -= 60
hours += 1
if mins == 0:
return hours
else:
mins = int(round(mins))
mins = str(mins).zfill(2)
return '%s:%s' % (hours, mins)
return int(self.quantity) if math.fmod(self.quantity,
1) == 0 else self.quantity
def update_totals(self):
"""Recalculate the tax and the totals for this invoice"""
if self.tax_rate_id > 0:
rate = 0
if self.id:
rate = self.tax_rate.rate
else:
from nano.models import Invoice, TaxRate
rate = TaxRate.query.get(self.tax_rate_id).rate
self.tax = self.quantity * float(self.price) * float(rate) / 100
else:
self.tax = 0
self.total = self.quantity * float(self.price)
return self.tax, self.total
def serialize(self):
"""Serialize the invoice structure so that it can be used for JSON"""
d = model_to_dict(self)
d['InvoiceItemType'] = self.invoice_item_type.serialize()
if self.tax_rate:
d['TaxRate'] = self.tax_rate.serialize()
return d
class Invoice(db.Model):
__tablename__ = 'invoice'
DATE_FORMAT = '%a %b %d %Y'
id = db.Column(u'id', db.Integer, primary_key=True, nullable=False)
user_id = db.Column(u'user_id', db.Integer, db.ForeignKey('user.id'))
contact_id = db.Column(u'contact_id', db.Integer, db.ForeignKey('contact.id'))
payment_term_id = db.Column(u'payment_term_id', db.Integer, db.ForeignKey('payment_term.id'))
status = db.Column(u'status', db.String(255))
reference = db.Column(u'reference', db.String(255))
po_reference = db.Column(u'po_reference', db.String(255))
currency_code = db.Column(u'currency_code', db.String(5))
date_issued = db.Column(u'date_issued', db.DateTime)
due_date = db.Column(u'due_date', db.DateTime)
written_off_date = db.Column(u'written_off_date', db.DateTime)
sub_total = db.Column(u'sub_total', db.Numeric(8, 2))
tax = db.Column(u'tax', db.Numeric(8, 2))
total = db.Column(u'total', db.Numeric(8, 2))
payment_status = db.Column(u'payment_status', db.Unicode(10), default=u'unpaid')
updated_at = db.Column(u'updated_at', db.DateTime, nullable=False, default=get_current_time())
created_at = db.Column(u'created_at', db.DateTime, nullable=False, default=get_current_time())
# relations
user = db.relation('User', primaryjoin='Invoice.user_id==User.id', backref='invoices')
contact = db.relation('Contact', primaryjoin='Invoice.contact_id==Contact.id', backref='invoices')
payment_term = db.relation('PaymentTerm', primaryjoin='Invoice.payment_term_id==PaymentTerm.id')
invoice_link = db.relation('InvoiceLink', backref=db.backref('invoice', uselist=False, lazy='joined'))
gocardless_payments = db.relation('GoCardlessPayment', backref=db.backref('invoice', uselist=False, lazy='joined'))
stripe_payments = db.relation('StripePayment', backref=db.backref('invoice', uselist=False, lazy='joined'))
@classmethod
def next_invoice_number(cls, user):
"""Next the next invoice number for the user"""
cur_max = cls.query.filter_by(user_id=user.id).count()
cur_max += 1
return str(cur_max)
@property
def due_date_nice(self):
return self.due_date.strftime(self.DATE_FORMAT)
@property
def date_issued_nice(self):
return self.date_issued.strftime(self.DATE_FORMAT)
def next_item_sort_order(self):
"""Generate the next number for the invoice item's sort order"""
from nano.models import InvoiceItem
total = InvoiceItem.query.filter_by(invoice_id=self.id).count()
return total+1
def update_totals(self, commit=False):
"""Update total and tax"""
sub_total = 0.0
tax = 0.0
for item in self.invoice_items:
sub_total += float(item.total if item.total else 0)
tax += float(item.tax if item.tax else 0)
self.tax = tax
self.sub_total = sub_total
self.total = float(self.tax) + float(self.sub_total)
if commit:
db.session.add(self)
db.session.commit()
return True
def serialize(self):
d = model_to_dict(self)
d['InvoiceItems'] = [item.serialize() for item in self.invoice_items]
return d
def update_payment_status(self):
"""Returns true if the amount has been paid"""
payments = Payment.query.filter_by(invoice_id=self.id).all()
total = 0.0
for payment in payments:
total += float(payment.amount)
if total >= self.total:
self.payment_status = u'paid'
else:
self.payment_status = u'unpaid'
db.session.add(self)
db.session.commit()
return False
def get_status(self):
if self.status == 'draft':
return 'draft'
if self.status == 'saved':
paid = True if self.payment_status == 'paid' else False
if paid:
return 'paid'
if self.due_date <= datetime.now():
return 'overdue'
else:
return 'saved'
def __json__(self):
return json_dumps(self.serialize())