def add_get_form(self, cuID, csID=None, cmID=None, full_width=True):
"""
Returns add form for an invoice
"""
from os_customer import Customer
from os_invoice import Invoice
self._add_get_form_permissions_check()
db = current.db
T = current.T
customer = Customer(cuID)
self._add_get_form_set_default_values_customer(customer)
self._add_get_form_enable_minimal_fields()
if csID:
self._add_get_form_enable_subscription_fields(csID)
form = SQLFORM(db.invoices, formstyle='bootstrap3_stacked')
elements = form.elements('input, select, textarea')
for element in elements:
element['_form'] = "invoice_add"
form_element = form.element('form')
form['_id'] = 'invoice_add'
if form.process().accepted:
iID = form.vars.id
invoice = Invoice(
iID
) # This sets due date and Invoice (calls invoice.on_create() #
invoice.link_to_customer(cuID)
self._add_reset_list_status_filter()
if csID:
invoice.link_to_customer_subscription(csID)
invoice.item_add_subscription(form.vars.SubscriptionYear,
form.vars.SubscriptionMonth)
redirect(URL('invoices', 'edit', vars={'iID': iID}))
# So the grids display the fields normally
for field in db.invoices:
field.readable = True
return form
def customers_subscriptions_create_invoices_for_month(self, year, month, description):
"""
Actually create invoices for subscriptions for a given month
"""
from general_helpers import get_last_day_month
from os_invoice import Invoice
T = current.T
db = current.db
DATE_FORMAT = current.DATE_FORMAT
year = int(year)
month = int(month)
firstdaythismonth = datetime.date(year, month, 1)
lastdaythismonth = get_last_day_month(firstdaythismonth)
csap = db.customers_subscriptions_alt_prices
fields = [
db.customers_subscriptions.id,
db.customers_subscriptions.auth_customer_id,
db.customers_subscriptions.school_subscriptions_id,
db.customers_subscriptions.Startdate,
db.customers_subscriptions.Enddate,
db.customers_subscriptions.payment_methods_id,
db.school_subscriptions.Name,
db.school_subscriptions_price.Price,
db.school_subscriptions_price.tax_rates_id,
db.tax_rates.Percentage,
db.customers_subscriptions_paused.id,
db.invoices.id,
csap.id,
csap.Amount,
csap.Description
]
rows = db.executesql(
"""
SELECT cs.id,
cs.auth_customer_id,
cs.school_subscriptions_id,
cs.Startdate,
cs.Enddate,
cs.payment_methods_id,
ssu.Name,
ssp.Price,
ssp.tax_rates_id,
tr.Percentage,
csp.id,
i.invoices_id,
csap.id,
csap.Amount,
csap.Description
FROM customers_subscriptions cs
LEFT JOIN auth_user au
ON au.id = cs.auth_customer_id
LEFT JOIN school_subscriptions ssu
ON cs.school_subscriptions_id = ssu.id
LEFT JOIN
(SELECT id,
school_subscriptions_id,
Startdate,
Enddate,
Price,
tax_rates_id
FROM school_subscriptions_price
WHERE Startdate <= '{firstdaythismonth}' AND
(Enddate >= '{firstdaythismonth}' OR Enddate IS NULL)) ssp
ON ssp.school_subscriptions_id = ssu.id
LEFT JOIN tax_rates tr
ON ssp.tax_rates_id = tr.id
LEFT JOIN
(SELECT id,
customers_subscriptions_id
FROM customers_subscriptions_paused
WHERE Startdate <= '{firstdaythismonth}' AND
(Enddate >= '{firstdaythismonth}' OR Enddate IS NULL)) csp
ON cs.id = csp.customers_subscriptions_id
LEFT JOIN
(SELECT ics.id,
ics.invoices_id,
ics.customers_subscriptions_id
FROM invoices_customers_subscriptions ics
LEFT JOIN invoices on ics.invoices_id = invoices.id
WHERE invoices.SubscriptionYear = {year} AND invoices.SubscriptionMonth = {month}) i
ON i.customers_subscriptions_id = cs.id
LEFT JOIN
(SELECT id,
customers_subscriptions_id,
Amount,
Description
FROM customers_subscriptions_alt_prices
WHERE SubscriptionYear = {year} AND SubscriptionMonth = {month}) csap
ON csap.customers_subscriptions_id = cs.id
WHERE cs.Startdate <= '{lastdaythismonth}' AND
(cs.Enddate >= '{firstdaythismonth}' OR cs.Enddate IS NULL) AND
ssp.Price <> 0 AND
ssp.Price IS NOT NULL AND
au.trashed = 'F'
""".format(firstdaythismonth=firstdaythismonth,
lastdaythismonth =lastdaythismonth,
year=year,
month=month),
fields=fields)
igpt = db.invoices_groups_product_types(ProductType = 'subscription')
igID = igpt.invoices_groups_id
invoices_created = 0
# Alright, time to create some invoices
for row in rows:
if row.invoices.id:
# an invoice already exists, do nothing
continue
if row.customers_subscriptions_paused.id:
# the subscription is paused, don't create an invoice
continue
if row.customers_subscriptions_alt_prices.Amount == 0:
# Don't create an invoice if there's an alt price for the subscription with amount 0.
continue
csID = row.customers_subscriptions.id
cuID = row.customers_subscriptions.auth_customer_id
pmID = row.customers_subscriptions.payment_methods_id
subscr_name = row.school_subscriptions.Name
if row.customers_subscriptions_alt_prices.Description:
inv_description = row.customers_subscriptions_alt_prices.Description
else:
inv_description = description
if row.customers_subscriptions.Startdate > firstdaythismonth:
period_begin = row.customers_subscriptions.Startdate
else:
period_begin = firstdaythismonth
period_end = lastdaythismonth
if row.customers_subscriptions.Enddate:
if row.customers_subscriptions.Enddate >= firstdaythismonth and \
row.customers_subscriptions.Enddate < lastdaythismonth:
period_end = row.customers_subscriptions.Enddate
item_description = period_begin.strftime(DATE_FORMAT) + ' - ' + \
period_end.strftime(DATE_FORMAT)
iID = db.invoices.insert(
invoices_groups_id = igID,
payment_methods_id = pmID,
SubscriptionYear = year,
SubscriptionMonth = month,
Description = inv_description,
Status = 'sent'
)
# create object to set Invoice# and due date
invoice = Invoice(iID)
invoice.link_to_customer(cuID)
invoice.link_to_customer_subscription(csID)
invoice.item_add_subscription(year, month)
invoice.set_amounts()
invoices_created += 1
##
# For scheduled tasks db connection has to be committed manually
##
db.commit()
return T("Invoices created") + ': ' + unicode(invoices_created)
def deliver(self,
class_online_booking=True,
class_booking_status='booked'):
"""
Create invoice for order and deliver goods
"""
from os_attendance_helper import AttendanceHelper
from os_cache_manager import OsCacheManager
from os_invoice import Invoice
from os_school_classcard import SchoolClasscard
from os_school_subscription import SchoolSubscription
from os_customer_subscription import CustomerSubscription
from os_school_membership import SchoolMembership
from os_customer_membership import CustomerMembership
from os_workshop import Workshop
from os_workshop_product import WorkshopProduct
cache_clear_classschedule_api = current.globalenv[
'cache_clear_classschedule_api']
get_sys_property = current.globalenv['get_sys_property']
TODAY_LOCAL = current.TODAY_LOCAL
ocm = OsCacheManager()
db = current.db
T = current.T
if self.order.Status == 'delivered':
return
create_invoice = False
iID = None
invoice = None
# Only create an invoice if order amount > 0
amounts = self.get_amounts()
sys_property_create_invoice = 'shop_donations_create_invoice'
create_invoice_for_donations = get_sys_property(
sys_property_create_invoice)
if create_invoice_for_donations == 'on':
create_invoice_for_donations = True
else:
create_invoice_for_donations = False
if amounts:
if amounts.TotalPriceVAT > 0:
if not self.order.Donation or (self.order.Donation and
create_invoice_for_donations):
create_invoice = True
# Create blank invoice
igpt = db.invoices_groups_product_types(ProductType='shop')
iID = db.invoices.insert(
invoices_groups_id=igpt.invoices_groups_id,
Description=T('Order #') + unicode(self.coID),
Status='sent')
# Link invoice to order
db.invoices_customers_orders.insert(
customers_orders_id=self.coID, invoices_id=iID)
# Call init function for invoices to set Invoice # , etc.
invoice = Invoice(iID)
invoice.link_to_customer(self.order.auth_customer_id)
# Add items to the invoice
rows = self.get_order_items_rows()
for row in rows:
##
# Only rows where school_classcards_id, workshops_products_id , classes_id or Donation are set
# are put on the invoice
##
# Check for product:
if row.ProductVariant:
if create_invoice:
invoice.item_add_product_variant(
product_name=row.ProductName,
description=row.Description,
quantity=row.Quantity,
price=row.Price,
tax_rates_id=row.tax_rates_id,
accounting_glaccounts_id=row.accounting_glaccounts_id,
accounting_costcenters_id=row.accounting_costcenters_id
)
# Check for classcard
if row.school_classcards_id:
# Deliver card
card_start = TODAY_LOCAL
scd = SchoolClasscard(row.school_classcards_id)
ccdID = scd.sell_to_customer(self.order.auth_customer_id,
card_start,
invoice=False)
# clear cache
ocm.clear_customers_classcards(self.order.auth_customer_id)
# Add card to invoice
if create_invoice:
invoice.item_add_classcard(ccdID)
# Check for subscription
if row.school_subscriptions_id:
## Deliver subscription
# Determine payment method
cs_payment_method = get_sys_property(
'shop_subscriptions_payment_method')
if cs_payment_method == 'mollie':
payment_method_id = 100
else:
payment_method_id = 3
subscription_start = TODAY_LOCAL
ssu = SchoolSubscription(row.school_subscriptions_id)
csID = ssu.sell_to_customer(
self.order.auth_customer_id,
subscription_start,
payment_methods_id=payment_method_id)
# Add credits for the first month
cs = CustomerSubscription(csID)
cs.add_credits_month(subscription_start.year,
subscription_start.month)
# clear cache
ocm.clear_customers_subscriptions(self.order.auth_customer_id)
if create_invoice:
# This will also add the registration fee if required.
iiID = invoice.item_add_subscription(
csID, TODAY_LOCAL.year, TODAY_LOCAL.month)
# Check for membership
if row.school_memberships_id:
# Deliver membership
membership_start = TODAY_LOCAL
sme = SchoolMembership(row.school_memberships_id)
cmID = sme.sell_to_customer(
self.order.auth_customer_id,
membership_start,
invoice=False, # Don't create a separate invoice
)
# clear cache
ocm.clear_customers_memberships(self.order.auth_customer_id)
if create_invoice:
cm = CustomerMembership(cmID)
# Check if price exists and > 0:
if sme.row.Price:
iiID = invoice.item_add_membership(cmID)
# Check for workshop
if row.workshops_products_id:
# Deliver workshop product
wsp = WorkshopProduct(row.workshops_products_id)
wspcID = wsp.sell_to_customer(self.order.auth_customer_id,
invoice=False)
# Add workshop product to invoice
if create_invoice:
invoice.item_add_workshop_product(wspcID)
# Check if sold out
if wsp.is_sold_out():
# Cancel all unpaid orders with a sold out product for this workshop
ws = Workshop(wsp.wsID)
ws.cancel_orders_with_sold_out_products()
# Check for classes
if row.classes_id:
# Deliver class
ah = AttendanceHelper()
if row.AttendanceType == 1:
result = ah.attendance_sign_in_trialclass(
self.order.auth_customer_id,
row.classes_id,
row.ClassDate,
online_booking=class_online_booking,
invoice=False,
booking_status=class_booking_status)
elif row.AttendanceType == 2:
result = ah.attendance_sign_in_dropin(
self.order.auth_customer_id,
row.classes_id,
row.ClassDate,
online_booking=class_online_booking,
invoice=False,
booking_status=class_booking_status,
)
if create_invoice:
invoice.item_add_class_from_order(row, result['caID'])
# Clear api cache to update available spaces
cache_clear_classschedule_api()
# Check for donation
if row.Donation:
# Add donation line to invoice
if create_invoice and create_invoice_for_donations:
invoice.item_add_donation(row.TotalPriceVAT,
row.Description)
# Check for custom item
if row.Custom:
# Add custom line to invoice
if create_invoice:
invoice.item_add_custom_from_order(row)
# Notify customer of new invoice
#if create_invoice:
#invoice.mail_customer_invoice_created()
# Update status
self.set_status_delivered()
# Notify customer of order delivery
self._deliver_mail_customer()
return dict(iID=iID, invoice=invoice)
def create_invoice_for_month(self, SubscriptionYear, SubscriptionMonth):
"""
:param SubscriptionYear: Year of subscription
:param SubscriptionMonth: Month of subscription
"""
from os_invoice import Invoice
db = current.db
TODAY_LOCAL = current.TODAY_LOCAL
DATE_FORMAT = current.DATE_FORMAT
# create invoice linked to subscription for first subscription term to know the right amount.
SubscriptionYear = TODAY_LOCAL.year
SubscriptionMonth = TODAY_LOCAL.month
firstdaythismonth = datetime.date(SubscriptionYear, SubscriptionMonth,
1)
lastdaythismonth = get_last_day_month(firstdaythismonth)
left = [
db.invoices_customers_subscriptions.on(
db.invoices_customers_subscriptions.invoices_id ==
db.invoices.id)
]
# Check if an invoice already exists, if so, return invoice id
query = (db.invoices_customers_subscriptions.customers_subscriptions_id == self.csID) & \
(db.invoices.SubscriptionYear == SubscriptionYear) & \
(db.invoices.SubscriptionMonth == SubscriptionMonth)
rows = db(query).select(db.invoices.ALL, left=left)
if len(rows):
return rows.first().id
# Check if the subscription is paused
query = (db.customers_subscriptions_paused.customers_subscriptions_id == self.csID) & \
(db.customers_subscriptions_paused.Startdate <= lastdaythismonth) & \
((db.customers_subscriptions_paused.Enddate >= firstdaythismonth) |
(db.customers_subscriptions_paused.Enddate == None))
if db(query).count():
return
# Check if an alt. price with amount 0 has been defined
csap = db.customers_subscriptions_alt_prices
query = (csap.customers_subscriptions_id == self.csID) & \
(csap.SubscriptionYear == SubscriptionYear) & \
(csap.SubscriptionMonth == SubscriptionMonth)
csap_rows = db(query).select(csap.ALL)
if csap_rows:
csap_row = csap_rows.first()
if csap_row.Amount == 0:
return
# Ok we've survived all checks, continue with invoice creation
igpt = db.invoices_groups_product_types(ProductType='subscription')
igID = igpt.invoices_groups_id
if TODAY_LOCAL > firstdaythismonth:
period_begin = TODAY_LOCAL
else:
period_begin = firstdaythismonth
period_end = lastdaythismonth
if self.enddate:
if self.startdate >= firstdaythismonth and \
self.enddate < lastdaythismonth:
period_end = self.enddate
item_description = period_begin.strftime(DATE_FORMAT) + ' - ' + \
period_end.strftime(DATE_FORMAT)
iID = db.invoices.insert(invoices_groups_id=igID,
payment_methods_id=self.payment_methods_id,
customers_subscriptions_id=self.csID,
SubscriptionYear=SubscriptionYear,
SubscriptionMonth=SubscriptionMonth,
Description='',
Status='sent')
# create object to set Invoice# and due date
invoice = Invoice(iID)
invoice.link_to_customer(self.auth_customer_id)
invoice.link_to_customer_subscription(self.csID)
invoice.item_add_subscription(SubscriptionYear, SubscriptionMonth)
return iID
def create_invoice_for_month(self, SubscriptionYear, SubscriptionMonth, description=None, invoice_date='today'):
"""
:param SubscriptionYear: Year of subscription
:param SubscriptionMonth: Month of subscription
"""
from os_school_subscription import SchoolSubscription
from os_invoice import Invoice
T = current.T
db = current.db
TODAY_LOCAL = current.TODAY_LOCAL
DATE_FORMAT = current.DATE_FORMAT
firstdaythismonth = datetime.date(SubscriptionYear, SubscriptionMonth, 1)
lastdaythismonth = get_last_day_month(firstdaythismonth)
left = [
db.invoices_items.on(
db.invoices_items.invoices_id ==
db.invoices.id
),
db.invoices_items_customers_subscriptions.on(
db.invoices_items_customers_subscriptions.invoices_items_id ==
db.invoices_items.id
),
]
# Check if an invoice already exists, if so, return invoice id
query = (db.invoices_items_customers_subscriptions.customers_subscriptions_id == self.csID) & \
(db.invoices.SubscriptionYear == SubscriptionYear) & \
(db.invoices.SubscriptionMonth == SubscriptionMonth)
rows = db(query).select(db.invoices.ALL,
left=left)
if len(rows):
return rows.first().id
# Check if the subscription is paused the full month
query = (db.customers_subscriptions_paused.customers_subscriptions_id == self.csID) & \
(db.customers_subscriptions_paused.Startdate <= firstdaythismonth) & \
((db.customers_subscriptions_paused.Enddate >= lastdaythismonth) |
(db.customers_subscriptions_paused.Enddate == None))
if db(query).count():
# This subscription is paused the full month, nothing to do
return
# Check if the customer has been moved to deleted
customer = db.auth_user(self.cs.auth_customer_id)
if customer.trashed:
# Customer has been trashed, don't do anything
return
# Check if an alt. price with amount 0 has been defined
csap = db.customers_subscriptions_alt_prices
query = (csap.customers_subscriptions_id == self.csID) & \
(csap.SubscriptionYear == SubscriptionYear) & \
(csap.SubscriptionMonth == SubscriptionMonth)
csap_rows = db(query).select(csap.ALL)
if csap_rows:
csap_row = csap_rows.first()
if csap_row.Amount == 0:
return
# Check if the regular price is 0
school_subscription = SchoolSubscription(self.ssuID)
price = school_subscription.get_price_on_date(
firstdaythismonth,
formatted=False
)
if price == 0:
# No need to create an invoice
return
# Ok we've survived all checks, continue with invoice creation
igpt = db.invoices_groups_product_types(ProductType='subscription')
igID = igpt.invoices_groups_id
if not description:
description = T("Subscription")
if invoice_date == 'first_of_month':
date_created = datetime.date(
int(SubscriptionYear),
int(SubscriptionMonth),
1
)
else:
date_created = TODAY_LOCAL
iID = db.invoices.insert(
invoices_groups_id=igID,
payment_methods_id=self.payment_methods_id,
customers_subscriptions_id=self.csID,
SubscriptionYear=SubscriptionYear,
SubscriptionMonth=SubscriptionMonth,
Description=description,
Status='sent',
DateCreated=date_created
)
# create object to set Invoice# and due date
invoice = Invoice(iID)
invoice.link_to_customer(self.auth_customer_id)
iiID = invoice.item_add_subscription(self.csID, SubscriptionYear, SubscriptionMonth)
return iID