class DailyInPaymentView(InPaymentView):
SalesPersonPerson = ClassAlias(Person, 'salesperson_person')
ClientPerson = ClassAlias(Person, 'client_person')
PersonBranch = ClassAlias(Person, 'person_branch')
BranchCompany = ClassAlias(Company, 'branch_company')
salesperson_name = SalesPersonPerson.name
client_name = ClientPerson.name
branch_name = Coalesce(NullIf(BranchCompany.fancy_name, u''),
PersonBranch.name)
sale_subtotal = Field('_sale_items', 'subtotal')
tables = InPaymentView.tables[:]
tables.extend([
Join(PersonBranch, Branch.person_id == PersonBranch.id),
Join(BranchCompany, Branch.person_id == BranchCompany.person_id),
LeftJoin(SalesPerson, Sale.salesperson_id == SalesPerson.id),
LeftJoin(SalesPersonPerson,
SalesPerson.person_id == SalesPersonPerson.id),
LeftJoin(Client, Sale.client_id == Client.id),
LeftJoin(ClientPerson, Client.person_id == ClientPerson.id),
LeftJoin(SaleItemSummary,
Field('_sale_items', 'sale_id') == Sale.id),
])
def get_sellable_view_query(self):
branch = api.get_current_branch(self.store)
branch_query = Or(Field('_stock_summary', 'branch_id') == branch.id,
Eq(Field('_stock_summary', 'branch_id'), None))
query = And(branch_query,
Sellable.get_available_sellables_query(self.store))
return self.sellable_view, query
def find_by_branch(cls, store, branch):
if branch is None:
return store.find(cls)
# When we need to filter on the branch, we also need to add the branch
# column on the ProductStockItem subselect, so the filter works. We cant
# keep the branch_id on the main subselect, since that will cause the
# results to be duplicate when not filtering by branch (probably the
# most common case). So, we need all of this workaround
# Make sure that the join we are replacing is the correct one.
assert cls.tables[3].right == _StockSummary
# Highjack the class being queried, since we need to add the branch
# on the ProductStockItem subselect to filter it later
class HighjackedViewable(cls):
tables = cls.tables[:]
tables[3] = LeftJoin(
_StockBranchSummary,
Field('_stock_summary', 'storable_id') == Storable.id)
# Also show products that were never purchased.
query = Or(
Field('_stock_summary', 'branch_id') == branch.id,
Eq(Field('_stock_summary', 'branch_id'), None))
return store.find(HighjackedViewable, query)
def get_sellable_view_query(self):
return (
self.sellable_view,
# FIXME: How to do this using sellable_view.find_by_branch ?
And(
Or(
Field('_stock_summary',
'branch_id') == self.model.branch.id,
Eq(Field('_stock_summary', 'branch_id'), None)),
Sellable.get_available_sellables_query(self.store)))
class WorkOrderPackageView(Viewable):
"""A view for |workorderpackages|
This is used to get the most information of a |workorderpackage|
without doing lots of database queries.
"""
_BranchSource = ClassAlias(Branch, "branch_source")
_BranchDestination = ClassAlias(Branch, "branch_destination")
_PersonSource = ClassAlias(Person, "person_source")
_PersonDestination = ClassAlias(Person, "person_destination")
_CompanySource = ClassAlias(Company, "company_source")
_CompanyDestination = ClassAlias(Company, "company_destination")
#: the |workorderpackage| object
package = WorkOrderPackage
# WorkOrderPackage
id = WorkOrderPackage.id
identifier = WorkOrderPackage.identifier
send_date = WorkOrderPackage.send_date
receive_date = WorkOrderPackage.receive_date
# Branch
source_branch_name = Coalesce(NullIf(_CompanySource.fancy_name, u''),
_PersonSource.name)
destination_branch_name = Coalesce(
NullIf(_CompanyDestination.fancy_name, u''), _PersonDestination.name)
# WorkOrder
quantity = Coalesce(Field('_package_items', 'quantity'), 0)
tables = [
WorkOrderPackage,
LeftJoin(_BranchSource,
WorkOrderPackage.source_branch_id == _BranchSource.id),
LeftJoin(_PersonSource, _BranchSource.person_id == _PersonSource.id),
LeftJoin(_CompanySource, _CompanySource.person_id == _PersonSource.id),
LeftJoin(
_BranchDestination,
WorkOrderPackage.destination_branch_id == _BranchDestination.id),
LeftJoin(_PersonDestination,
_BranchDestination.person_id == _PersonDestination.id),
LeftJoin(_CompanyDestination,
_CompanyDestination.person_id == _PersonDestination.id),
LeftJoin(_WorkOrderPackageItemsSummary,
Field('_package_items', 'package_id') == WorkOrderPackage.id),
]
@classmethod
def find_by_destination_branch(cls, store, branch):
return store.find(cls,
WorkOrderPackage.destination_branch_id == branch.id)
class AccountView(Viewable):
account = Account
id = Account.id
parent_id = Account.parent_id
account_type = Account.account_type
dest_account_id = Account.parent_id
description = Account.description
code = Account.code
source_value = Field('source_sum', 'value')
dest_value = Field('dest_sum', 'value')
tables = [
Account,
LeftJoin(Alias(_SourceSum, 'source_sum'),
Field('source_sum', 'source_account_id') == Account.id),
LeftJoin(Alias(_DestSum, 'dest_sum'),
Field('dest_sum', 'account_id') == Account.id),
]
@property
def parent_account(self):
"""Get the parent account for this view"""
return self.store.get(Account, self.parent_id)
def matches(self, account_id):
"""Returns true if the account_id matches this account or its parent"""
if self.id == account_id:
return True
if self.parent_id and self.parent_id == account_id:
return True
return False
def get_combined_value(self):
"""Returns the combined value of incoming and outgoing
transactions"""
if not self.dest_value and not self.source_value:
return 0
elif not self.dest_value:
return -self.source_value
elif not self.source_value:
return self.dest_value
else:
return self.dest_value - self.source_value
def __repr__(self):
return '<AccountView %s>' % (self.description, )
class ProductWithStockBranchView(ProductWithStockView):
"""The same as ProductWithStockView but has a branch_id property that must
be used to filte.
Note that when using this viewable, all queries must include the branch
filter, otherwise, the results may be duplicated (once for each branch in
the database)
"""
minimum_quantity = Storable.minimum_quantity
maximum_quantity = Storable.maximum_quantity
branch_id = Field('_stock_summary', 'branch_id')
storable_id = Field('_stock_summary', 'storable_id')
tables = ProductWithStockView.tables[:]
tables[3] = LeftJoin(_StockBranchSummary, storable_id == Storable.id)
def can_remove(self, skip=None):
"""Check if this object can be removed from the database
This will check if there's any object referencing self
:param skip: an itarable containing the (table, column) to skip
the check. Use this to avoid false positives when you will
delete those skipped by hand before self.
"""
skip = skip or set()
selects = []
refs = self.store.list_references(self.__class__.id)
for t_name, c_name, ot_name, oc_name, u, d in refs:
if (t_name, c_name) in skip:
continue
column = Field(t_name, c_name)
selects.append(
Select(columns=[1], tables=[t_name],
where=column == self.id, limit=1))
# If everything was skipped, there's no query to execute
if not len(selects):
return True
# We can only pass limit=1 to UnionAll if there's more than one select.
# If not, storm will put the limit anyway and it will join with the
# select's limit producing an error: multiple LIMIT clauses not allowed
if len(selects) > 1:
extra = {'limit': 1}
else:
extra = {}
return not any(self.store.execute(UnionAll(*selects, **extra)))
def merge_with(self, other, skip=None, copy_empty_values=True):
"""Does automatic references updating when merging two objects.
This will update all tables that reference the `other` object and make
them reference `self` instead.
After this it should be safe to remove the `other` object. Since there
is no one referencing it anymore.
:param skip: A set of (table, column) that should be skiped by the
automatic update. This are normally tables that require a special
treatment, like when there are constraints.
:param copy_empty_values: If True, attributes that are either null or an
empty string in self will be updated with the value from the other
object (given that the other attribute is not empty as well)
"""
skip = skip or set()
event_skip = DomainMergeEvent.emit(self, other)
if event_skip:
skip = skip.union(event_skip)
if copy_empty_values:
self.copy_empty_values(other)
refs = self.store.list_references(type(self).id)
for (table, column, other_table, other_column, u, d) in refs:
if (table, column) in skip:
continue
clause = Field(table, column) == other.id
self.store.execute(Update({column: self.id}, clause, table))
def get_max_value(cls, store, attr):
"""Get the maximum value for a given attr
On text columns, trying to find the max value for them using MAX()
on postgres would result in some problems, like '9' being considered
greater than '10' (because the comparison is done from left to right).
This will 0-"pad" the values for the comparison, making it compare
the way we want. Note that because of that, in the example above,
it would return '09' instead of '9'
:para store: a store
:param attr: the attribute to find the max value for
:returns: the maximum value for the attr
"""
cls.validate_attr(attr, expected_type=UnicodeCol)
max_length = Alias(
Select(columns=[Alias(Max(CharLength(attr)), 'max_length')],
tables=[cls]), '_max_length')
# Using LPad with max_length will workaround most of the cases where
# the string comparison fails. For example, the common case would
# consider '9' to be greater than '10'. We could test just strings
# with length equal to max_length, but than '010' would be greater
# than '001' would be greater than '10' (that would be excluded from
# the comparison). By doing lpad, '09' is lesser than '10' and '001'
# is lesser than '010', working around those cases
max_batch = store.using(cls, max_length).find(cls).max(
LPad(attr, Field('_max_length', 'max_length'), u'0'))
# Make the api consistent and return an ampty string instead of None
# if there's no batch registered on the database
return max_batch or u''
def test_generate_series_integer(self):
series = GenerateSeries(5, 10),
data = list(
self.store.using(series).find(
Field('generate_series', 'generate_series')))
self.assertEquals(len(data), 6)
self.assertEquals(data, [5, 6, 7, 8, 9, 10])
class ProductFullStockItemView(ProductFullStockView):
# ProductFullStockView already joins with a 1 to Many table (Sellable
# with ProductStockItem).
#
# This is why we must join PurchaseItem (another 1 to many table) in a
# subquery
minimum_quantity = Storable.minimum_quantity
maximum_quantity = Storable.maximum_quantity
to_receive_quantity = Coalesce(Field('_purchase_total', 'to_receive'), 0)
difference = ProductFullStockView.stock - Storable.minimum_quantity
tables = ProductFullStockView.tables[:]
tables.append(
LeftJoin(Alias(_PurchaseItemTotal, '_purchase_total'),
Field('_purchase_total', 'sellable_id') == Sellable.id))
def collect_link_statistics(store):
one_week = datetime.date.today() - datetime.timedelta(days=7)
query = Date(Sale.confirm_date) >= one_week
# Profit Margin
item_cost = Alias(
Select(columns=[
SaleItem.sale_id,
Alias(Sum(SaleItem.quantity * SaleItem.average_cost), 'cost')
],
tables=SaleItem,
group_by=[SaleItem.sale_id]), 'item_cost')
column = ((Sum(Sale.total_amount) / Sum(Field('item_cost', 'cost')) - 1) *
100)
tables = [Sale, Join(item_cost, Field('item_cost', 'sale_id') == Sale.id)]
profit_margin = store.using(*tables).find(column, query).one()
# Sale chart
columns = (DateTrunc(u'day', Sale.confirm_date), Sum(Sale.total_amount))
sale_data = store.find(columns, query)
sale_data.group_by(columns[0])
# Best selling
tables = [
Sellable,
Join(SaleItem, SaleItem.sellable_id == Sellable.id),
Join(Sale, SaleItem.sale_id == Sale.id)
]
columns = (Sellable.description, Sum(SaleItem.quantity),
Sum(SaleItem.quantity * SaleItem.price))
product_data = store.using(*tables).find(columns,
query).order_by(-columns[2])
product_data.group_by(Sellable.description)
data = dict(sales_total=store.find(Sale, query).sum(Sale.total_amount),
sales_count=store.find(Sale, query).count(),
clients_served=store.find(Sale, query).count(Sale.client_id,
distinct=True),
profit_margin=format(float(profit_margin), '.2f'),
best_selling=list(product_data),
sales_chart=list(sale_data),
timeline=_collect_timeline(store),
timestamp=datetime.datetime.now())
return json.dumps(data, default=default)
class OutPaymentView(BasePaymentView):
supplier_name = Person.name
invoice_numbers = Field('_invoice_number_summary', 'invoice_numbers')
_count_tables = BasePaymentView._count_tables[:]
_count_tables.append(
LeftJoin(Person,
BasePaymentView.PaymentGroup_Sale.recipient_id == Person.id))
tables = BasePaymentView.tables[:]
tables.extend([
LeftJoin(Person,
Person.id == BasePaymentView.PaymentGroup_Sale.recipient_id),
LeftJoin(
_InvoiceNumberSummary,
Field('_invoice_number_summary', 'group_id') == PaymentGroup.id),
])
clause = (Payment.payment_type == Payment.TYPE_OUT)
def test_generate_series_date(self):
a = datetime.datetime(2012, 1, 1)
b = datetime.datetime(2012, 4, 1)
series = GenerateSeries(Cast(a, 'timestamp'), Cast(b, 'timestamp'),
Cast(u'1 month', 'interval')),
data = list(
self.store.using(series).find(
Field('generate_series', 'generate_series')))
self.assertEquals(len(data), 4)
self.assertEquals(data[0], a)
self.assertEquals(data[1], datetime.datetime(2012, 2, 1))
self.assertEquals(data[2], datetime.datetime(2012, 3, 1))
self.assertEquals(data[3], b)
def find_available_by_storable(cls, store, storable, branch=None):
"""Find results for this view that for *storable* that have stock
The same as :meth:`.find_by_storable`, but only results with
:obj:`.stock` > 0 will be fetched
:param store: the store that will be used to find the results
:param storable: the |storable| used to filter the results
:param branch: a |branch| that, if not ``None``, will be used to
filter the results to only get batches on that branch.
:returns: the matching views
:rtype: a sequence of :class:`StorableBatchView`
"""
results = cls.find_by_storable(store, storable, branch=branch)
return results.find(Field('_stock_summary', 'stock') > 0)
def test_over(self):
series = GenerateSeries(0, 10, 1)
normal_values = Field('generate_series', 'generate_series')
over_nothing = Over(Sum(normal_values))
over_order_values = Over(Sum(normal_values), [], [normal_values])
over_partition_values = Over(Sum(normal_values), [normal_values])
data = list(
self.store.using(series).find(
(normal_values, over_nothing, over_order_values,
over_partition_values)))
self.assertEquals(len(data), 11)
self.assertEquals(data,
[(i, 55, sum(range(i + 1)), i) for i in range(11)])
def find_by_storable(cls, store, storable, branch=None):
"""Find results for this view that for *storable*
Normally it's best to use this instead of *store.find* since
it'll only |batches| for the given |storable|.
:param store: the store that will be used to find the results
:param storable: the |storable| used to filter the results
:param branch: a |branch| that, if not ``None``, will be used to
filter the results to only get batches on that branch.
:returns: the matching views
:rtype: a sequence of :class:`StorableBatchView`
"""
query = StorableBatch.storable_id == storable.id
if branch is not None:
query = And(query,
Field('_stock_summary', 'branch_id') == branch.id)
return store.find(cls, query)
def test_case(self):
# Ordinary case
series = GenerateSeries(0, 5)
case = Case(condition=Field('generate_series', 'generate_series') <= 3,
result=0,
else_=1)
data = list(self.store.using(series).find(case))
self.assertEquals(data, [0, 0, 0, 0, 1, 1])
# else_ is None
case = Case(condition=Field('generate_series', 'generate_series') <= 3,
result=Field('generate_series', 'generate_series'),
else_=None)
data = list(self.store.using(series).find(case))
self.assertEquals(data, [0, 1, 2, 3, None, None])
# else_ is a False equivalent value
case = Case(condition=Field('generate_series', 'generate_series') <= 3,
result=Field('generate_series', 'generate_series'),
else_=0)
data = list(self.store.using(series).find(case))
self.assertEquals(data, [0, 1, 2, 3, 0, 0])
# else_ is False
case = Case(condition=Field('generate_series', 'generate_series') != 1,
result=True,
else_=False)
data = list(self.store.using(series).find(case))
self.assertEquals(data, [True, False, True, True, True, True])
# result is None
case = Case(condition=Field('generate_series', 'generate_series') != 1,
result=None,
else_=False)
data = list(self.store.using(series).find(case))
self.assertEquals(data, [None, False, None, None, None, None])
class StorableBatchView(Viewable):
"""A view for |batches|
This is used to get the most information of a |batch|
without doing lots of database queries.
It's bestly used with :meth:`.find_by_storable`
"""
#: the |batch| object
batch = StorableBatch
#: the |branch| this batch is in
branch = Branch
# StorableBatch
id = StorableBatch.id
create_date = StorableBatch.create_date
batch_number = StorableBatch.batch_number
stock = Coalesce(Field('_stock_summary', 'stock'), 0)
tables = [
StorableBatch,
LeftJoin(_StockSummary,
Field('_stock_summary', 'batch_id') == StorableBatch.id),
LeftJoin(Branch, Field('_stock_summary', 'branch_id') == Branch.id),
]
@classmethod
def find_by_storable(cls, store, storable, branch=None):
"""Find results for this view that for *storable*
Normally it's best to use this instead of *store.find* since
it'll only |batches| for the given |storable|.
:param store: the store that will be used to find the results
:param storable: the |storable| used to filter the results
:param branch: a |branch| that, if not ``None``, will be used to
filter the results to only get batches on that branch.
:returns: the matching views
:rtype: a sequence of :class:`StorableBatchView`
"""
query = StorableBatch.storable_id == storable.id
if branch is not None:
query = And(query,
Field('_stock_summary', 'branch_id') == branch.id)
return store.find(cls, query)
@classmethod
def find_available_by_storable(cls, store, storable, branch=None):
"""Find results for this view that for *storable* that have stock
The same as :meth:`.find_by_storable`, but only results with
:obj:`.stock` > 0 will be fetched
:param store: the store that will be used to find the results
:param storable: the |storable| used to filter the results
:param branch: a |branch| that, if not ``None``, will be used to
filter the results to only get batches on that branch.
:returns: the matching views
:rtype: a sequence of :class:`StorableBatchView`
"""
results = cls.find_by_storable(store, storable, branch=branch)
return results.find(Field('_stock_summary', 'stock') > 0)
class BasePaymentView(Viewable):
PaymentGroup_Sale = ClassAlias(PaymentGroup, 'payment_group_sale')
PaymentGroup_Purchase = ClassAlias(PaymentGroup, 'payment_group_purchase')
payment = Payment
group = PaymentGroup
purchase = PurchaseOrder
sale = Sale
method = PaymentMethod
branch = Branch
card_data = CreditCardData
card_type = CreditCardData.card_type
check_data = CheckData
# Payment
id = Payment.id
identifier = Payment.identifier
identifier_str = Cast(Payment.identifier, 'text')
description = Payment.description
due_date = Payment.due_date
status = Payment.status
paid_date = Payment.paid_date
open_date = Payment.open_date
value = Payment.value
paid_value = Payment.paid_value
payment_number = Payment.payment_number
group_id = Payment.group_id
branch_id = Payment.branch_id
# PaymentGroup
renegotiated_id = PaymentGroup.renegotiation_id
# PaymentMethod
method_name = PaymentMethod.method_name
method_id = PaymentMethod.id
# PaymentCategory
color = PaymentCategory.color
category = PaymentCategory.name
# PaymentComment
comments_number = Field('_comments', 'comments_number')
# Sale
sale_id = Sale.id
sale_open_date = Sale.open_date
# Purchase
purchase_id = PurchaseOrder.id
_count_tables = [
Payment,
Join(Branch, Payment.branch_id == Branch.id),
LeftJoin(PaymentGroup, PaymentGroup.id == Payment.group_id),
LeftJoin(PaymentCategory, PaymentCategory.id == Payment.category_id),
Join(PaymentMethod, Payment.method_id == PaymentMethod.id),
LeftJoin(CreditCardData, Payment.id == CreditCardData.payment_id),
LeftJoin(CheckData, Payment.id == CheckData.payment_id),
# Purchase
LeftJoin(PaymentGroup_Purchase,
PaymentGroup_Purchase.id == Payment.group_id),
LeftJoin(PurchaseOrder,
PurchaseOrder.group_id == PaymentGroup_Purchase.id),
# Sale
LeftJoin(PaymentGroup_Sale, PaymentGroup_Sale.id == Payment.group_id),
LeftJoin(Sale, Sale.group_id == PaymentGroup_Sale.id),
]
tables = _count_tables + [
LeftJoin(CommentsSummary,
Field('_comments', 'payment_id') == Payment.id),
]
#
# Private
#
def _get_due_date_delta(self):
if self.status in [Payment.STATUS_PAID, Payment.STATUS_CANCELLED]:
return datetime.timedelta(0)
return localtoday().date() - self.due_date.date()
#
# Public API
#
@classmethod
def post_search_callback(cls, sresults):
select = sresults.get_select_expr(Count(1), Sum(cls.value))
return ('count', 'sum'), select
def can_change_due_date(self):
return self.status not in [
Payment.STATUS_PAID, Payment.STATUS_CANCELLED
]
def can_cancel_payment(self):
"""Only lonely payments and pending can be cancelled
"""
if self.sale_id or self.purchase_id:
return False
return self.status == Payment.STATUS_PENDING
@property
def method_description(self):
return self.method.description
@property
def status_str(self):
return Payment.statuses[self.status]
def is_late(self):
return self._get_due_date_delta().days > 0
def get_days_late(self):
return max(self._get_due_date_delta().days, 0)
def is_paid(self):
return self.status == Payment.STATUS_PAID
@property
def operation(self):
return self.method.operation
@classmethod
def find_pending(cls, store, due_date=None):
query = cls.status == Payment.STATUS_PENDING
if due_date:
if isinstance(due_date, tuple):
date_query = And(
Date(cls.due_date) >= due_date[0],
Date(cls.due_date) <= due_date[1])
else:
date_query = Date(cls.due_date) == due_date
query = And(query, date_query)
return store.find(cls, query)
class PurchaseOrderView(Viewable):
"""General information about purchase orders
:cvar id: the id of purchase_order table
:cvar status: the purchase order status
:cvar open_date: the date when the order was started
:cvar quote_deadline: the date when the quotation expires
:cvar expected_receival_date: expected date to receive products
:cvar expected_pay_date: expected date to pay the products
:cvar receival_date: the date when the products were received
:cvar confirm_date: the date when the order was confirmed
:cvar salesperson_name: the name of supplier's salesperson
:cvar expected_freight: the expected freight value
:cvar surcharge_value: the surcharge value for the order total
:cvar discount_value: the discount_value for the order total
:cvar supplier_name: the supplier name
:cvar transporter_name: the transporter name
:cvar branch_name: the branch company name
:cvar ordered_quantity: the total quantity ordered
:cvar received_quantity: the total quantity received
:cvar subtotal: the order subtotal (sum of product values)
:cvar total: subtotal - discount_value + surcharge_value
"""
Person_Supplier = ClassAlias(Person, 'person_supplier')
Person_Transporter = ClassAlias(Person, 'person_transporter')
Person_Branch = ClassAlias(Person, 'person_branch')
Person_Responsible = ClassAlias(Person, 'person_responsible')
purchase = PurchaseOrder
branch = Branch
id = PurchaseOrder.id
identifier = PurchaseOrder.identifier
identifier_str = Cast(PurchaseOrder.identifier, 'text')
status = PurchaseOrder.status
open_date = PurchaseOrder.open_date
quote_deadline = PurchaseOrder.quote_deadline
expected_receival_date = PurchaseOrder.expected_receival_date
expected_pay_date = PurchaseOrder.expected_pay_date
confirm_date = PurchaseOrder.confirm_date
salesperson_name = NullIf(PurchaseOrder.salesperson_name, u'')
expected_freight = PurchaseOrder.expected_freight
surcharge_value = PurchaseOrder.surcharge_value
discount_value = PurchaseOrder.discount_value
branch_id = Branch.id
supplier_id = Supplier.id
supplier_name = Person_Supplier.name
transporter_name = Coalesce(Person_Transporter.name, u'')
branch_name = Coalesce(NullIf(Company.fancy_name, u''), Person_Branch.name)
responsible_name = Person_Responsible.name
receival_date = Field('_receiving_order', 'receival_date')
ordered_quantity = Field('_purchase_item', 'ordered_quantity')
received_quantity = Field('_purchase_item', 'received_quantity')
subtotal = Field('_purchase_item', 'subtotal')
total = Field('_purchase_item', 'subtotal') - \
PurchaseOrder.discount_value + PurchaseOrder.surcharge_value
tables = [
PurchaseOrder,
Join(PurchaseItemSummary,
Field('_purchase_item', 'order_id') == PurchaseOrder.id),
LeftJoin(PurchaseReceivingSummary,
Field('_receiving_order', 'purchase_id') == PurchaseOrder.id),
LeftJoin(Supplier, PurchaseOrder.supplier_id == Supplier.id),
LeftJoin(Transporter, PurchaseOrder.transporter_id == Transporter.id),
LeftJoin(Branch, PurchaseOrder.branch_id == Branch.id),
LeftJoin(LoginUser, PurchaseOrder.responsible_id == LoginUser.id),
LeftJoin(Person_Supplier, Supplier.person_id == Person_Supplier.id),
LeftJoin(Person_Transporter, Transporter.person_id == Person_Transporter.id),
LeftJoin(Person_Branch, Branch.person_id == Person_Branch.id),
LeftJoin(Company, Company.person_id == Person_Branch.id),
LeftJoin(Person_Responsible, LoginUser.person_id == Person_Responsible.id),
]
@classmethod
def post_search_callback(cls, sresults):
select = sresults.get_select_expr(Count(1), Sum(cls.total))
return ('count', 'sum'), select
#
# Public API
#
def get_open_date_as_string(self):
return self.open_date.strftime("%x")
@property
def status_str(self):
return PurchaseOrder.translate_status(self.status)
@classmethod
def find_confirmed(cls, store, due_date=None):
query = cls.status == PurchaseOrder.ORDER_CONFIRMED
if due_date:
if isinstance(due_date, tuple):
date_query = And(Date(cls.expected_receival_date) >= due_date[0],
Date(cls.expected_receival_date) <= due_date[1])
else:
date_query = Date(cls.expected_receival_date) == due_date
query = And(query, date_query)
return store.find(cls, query)
def post_search_callback(cls, sresults):
select = sresults.get_select_expr(
Count(Distinct(Sellable.id)), Sum(Field('_stock_summary',
'stock')))
return ('count', 'sum'), select
class ProductFullStockView(Viewable):
"""Stores information about products.
This view is used to query stock information on a certain branch.
:cvar id: the id of the asellable table
:cvar barcode: the sellable barcode
:cvar status: the sellable status
:cvar cost: the sellable cost
:cvar price: the sellable price
:cvar description: the sellable description
:cvar unit: the unit of the product
:cvar product_id: the id of the product table
:cvar location: the location of the product
:cvar branch_id: the id of branch table
:cvar stock: the stock of the product
"""
sellable = Sellable
product = Product
# Sellable
id = Sellable.id
code = Sellable.code
barcode = Sellable.barcode
status = Sellable.status
cost = Sellable.cost
description = Sellable.description
image_id = Sellable.image_id
base_price = Sellable.base_price
on_sale_price = Sellable.on_sale_price
on_sale_start_date = Sellable.on_sale_start_date
on_sale_end_date = Sellable.on_sale_end_date
# Product
product_id = Product.id
location = Product.location
model = Product.model
manufacturer = ProductManufacturer.name
tax_description = SellableTaxConstant.description
category_description = SellableCategory.description
unit = SellableUnit.description
# Aggregates
total_stock_cost = Coalesce(Field('_stock_summary', 'total_stock_cost'), 0)
stock = Coalesce(Field('_stock_summary', 'stock'), 0)
tables = [
# Keep this first 4 joins in this order, so find_by_branch may change it.
Sellable,
Join(Product, Product.sellable_id == Sellable.id),
LeftJoin(Storable, Storable.product_id == Product.id),
LeftJoin(_StockSummary,
Field('_stock_summary', 'storable_id') == Storable.id),
LeftJoin(SellableTaxConstant,
SellableTaxConstant.id == Sellable.tax_constant_id),
LeftJoin(SellableCategory,
SellableCategory.id == Sellable.category_id),
LeftJoin(SellableUnit, Sellable.unit_id == SellableUnit.id),
LeftJoin(ProductManufacturer,
Product.manufacturer_id == ProductManufacturer.id),
]
clause = Sellable.status != Sellable.STATUS_CLOSED
@classmethod
def post_search_callback(cls, sresults):
select = sresults.get_select_expr(
Count(Distinct(Sellable.id)), Sum(Field('_stock_summary',
'stock')))
return ('count', 'sum'), select
@classmethod
def find_by_branch(cls, store, branch):
if branch is None:
return store.find(cls)
# When we need to filter on the branch, we also need to add the branch
# column on the ProductStockItem subselect, so the filter works. We cant
# keep the branch_id on the main subselect, since that will cause the
# results to be duplicate when not filtering by branch (probably the
# most common case). So, we need all of this workaround
# Make sure that the join we are replacing is the correct one.
assert cls.tables[3].right == _StockSummary
# Highjack the class being queried, since we need to add the branch
# on the ProductStockItem subselect to filter it later
class HighjackedViewable(cls):
tables = cls.tables[:]
tables[3] = LeftJoin(
_StockBranchSummary,
Field('_stock_summary', 'storable_id') == Storable.id)
# Also show products that were never purchased.
query = Or(
Field('_stock_summary', 'branch_id') == branch.id,
Eq(Field('_stock_summary', 'branch_id'), None))
return store.find(HighjackedViewable, query)
def get_unit_description(self):
unit = self.product.sellable.get_unit_description()
if unit == u"":
return u"un"
return unit
def get_product_and_category_description(self):
"""Returns the product and the category description in one string.
The category description will be formatted inside square
brackets, if any. Otherwise, only the product description will
be returned.
"""
category_description = ''
if self.category_description:
category_description += '[' + self.category_description + '] '
return category_description + self.description
@property
def stock_cost(self):
if self.stock:
return self.total_stock_cost / self.stock
return 0
@property
def price(self):
# See Sellable.price property
if self.on_sale_price:
today = localnow()
start_date = self.on_sale_start_date
end_date = self.on_sale_end_date
if is_date_in_interval(today, start_date, end_date):
return self.on_sale_price
return self.base_price
@property
def has_image(self):
return self.image_id is not None
class WorkOrderView(Viewable):
"""A view for |workorders|
This is used to get the most information of a |workorder|
without doing lots of database queries.
"""
# TODO: Maybe we should have a cache for branches, to avoid all this
# joins just to get the company name.
_BranchOriginalBranch = ClassAlias(Branch, "branch_original_branch")
_BranchCurrentBranch = ClassAlias(Branch, "branch_current_branch")
_PersonOriginalBranch = ClassAlias(Person, "person_original_branch")
_PersonCurrentBranch = ClassAlias(Person, "person_current_branch")
_CompanyOriginalBranch = ClassAlias(Company, "company_original_branch")
_CompanyCurrentBranch = ClassAlias(Company, "company_current_branch")
_PersonClient = ClassAlias(Person, "person_client")
_PersonSalesPerson = ClassAlias(Person, "person_salesperson")
#: the |workorder| object
work_order = WorkOrder
#: the |workordercategory| object
category = WorkOrderCategory
#: the |client| object
client = Client
# WorkOrder
id = WorkOrder.id
identifier = WorkOrder.identifier
status = WorkOrder.status
equipment = WorkOrder.equipment
open_date = WorkOrder.open_date
approve_date = WorkOrder.approve_date
finish_date = WorkOrder.finish_date
# WorkOrderCategory
category_name = WorkOrderCategory.name
category_color = WorkOrderCategory.color
# Client
client_name = _PersonClient.name
# SalesPerson
salesperson_name = _PersonSalesPerson.name
# Branch
branch_name = Coalesce(NullIf(_CompanyOriginalBranch.fancy_name, u''),
_PersonOriginalBranch.name)
current_branch_name = Coalesce(
NullIf(_CompanyCurrentBranch.fancy_name, u''),
_PersonCurrentBranch.name)
# WorkOrderItem
quantity = Coalesce(Field('_work_order_items', 'quantity'), 0)
total = Coalesce(Field('_work_order_items', 'total'), 0)
tables = [
WorkOrder,
LeftJoin(Client, WorkOrder.client_id == Client.id),
LeftJoin(_PersonClient, Client.person_id == _PersonClient.id),
LeftJoin(Sale, WorkOrder.sale_id == Sale.id),
LeftJoin(SalesPerson, Sale.salesperson_id == SalesPerson.id),
LeftJoin(_PersonSalesPerson,
SalesPerson.person_id == _PersonSalesPerson.id),
LeftJoin(_BranchOriginalBranch,
WorkOrder.branch_id == _BranchOriginalBranch.id),
LeftJoin(_PersonOriginalBranch,
_BranchOriginalBranch.person_id == _PersonOriginalBranch.id),
LeftJoin(_CompanyOriginalBranch,
_CompanyOriginalBranch.person_id == _PersonOriginalBranch.id),
LeftJoin(_BranchCurrentBranch,
WorkOrder.current_branch_id == _BranchCurrentBranch.id),
LeftJoin(_PersonCurrentBranch,
_BranchCurrentBranch.person_id == _PersonCurrentBranch.id),
LeftJoin(_CompanyCurrentBranch,
_CompanyCurrentBranch.person_id == _PersonCurrentBranch.id),
LeftJoin(WorkOrderCategory,
WorkOrder.category_id == WorkOrderCategory.id),
LeftJoin(_WorkOrderItemsSummary,
Field('_work_order_items', 'order_id') == WorkOrder.id),
]
@classmethod
def post_search_callback(cls, sresults):
select = sresults.get_select_expr(Count(1), Sum(cls.total))
return ('count', 'sum'), select
@classmethod
def find_by_current_branch(cls, store, branch):
return store.find(cls, WorkOrder.current_branch_id == branch.id)
class BasePaymentView(Viewable):
PaymentGroup_Sale = ClassAlias(PaymentGroup, 'payment_group_sale')
PaymentGroup_Purchase = ClassAlias(PaymentGroup, 'payment_group_purchase')
payment = Payment
group = PaymentGroup
purchase = PurchaseOrder
sale = Sale
method = PaymentMethod
# Payment
id = Payment.id
identifier = Payment.identifier
description = Payment.description
due_date = Payment.due_date
status = Payment.status
paid_date = Payment.paid_date
value = Payment.value
paid_value = Payment.paid_value
payment_number = Payment.payment_number
group_id = Payment.group_id
# PaymentGroup
renegotiated_id = PaymentGroup.renegotiation_id
# PaymentMethod
method_name = PaymentMethod.method_name
method_id = PaymentMethod.id
# PaymentCategory
color = PaymentCategory.color
category = PaymentCategory.name
# PaymentComment
comments_number = Field('_comments', 'comments_number')
# Sale
sale_id = Sale.id
# Purchase
purchase_id = PurchaseOrder.id
purchase_status = PurchaseOrder.status
_count_tables = [
Payment,
LeftJoin(PaymentGroup, PaymentGroup.id == Payment.group_id),
LeftJoin(PaymentCategory, PaymentCategory.id == Payment.category_id),
Join(PaymentMethod, Payment.method_id == PaymentMethod.id),
# Purchase
LeftJoin(PaymentGroup_Purchase, PaymentGroup_Purchase.id == Payment.group_id),
LeftJoin(PurchaseOrder, PurchaseOrder.group_id == PaymentGroup_Purchase.id),
# Sale
LeftJoin(PaymentGroup_Sale, PaymentGroup_Sale.id == Payment.group_id),
LeftJoin(Sale, Sale.group_id == PaymentGroup_Sale.id),
]
tables = _count_tables + [
LeftJoin(CommentsSummary, Field('_comments', 'payment_id') == Payment.id),
]
@classmethod
def post_search_callback(cls, sresults):
select = sresults.get_select_expr(Count(1), Sum(cls.value))
return ('count', 'sum'), select
def can_change_due_date(self):
return self.status not in [Payment.STATUS_PAID,
Payment.STATUS_CANCELLED]
def can_cancel_payment(self):
"""Only lonely payments and pending can be cancelled
"""
if self.sale_id or self.purchase_id:
return False
return self.status == Payment.STATUS_PENDING
def get_status_str(self):
return Payment.statuses[self.status]
def is_late(self):
if self.status in [Payment.STATUS_PAID, Payment.STATUS_CANCELLED]:
return False
return (localtoday().date() - self.due_date.date()).days > 0
def get_days_late(self):
if not self.is_late():
return 0
days_late = localtoday().date() - self.due_date.date()
if days_late.days < 0:
return 0
return days_late.days
def is_paid(self):
return self.status == Payment.STATUS_PAID
@property
def operation(self):
return self.method.operation
@classmethod
def find_pending(cls, store, due_date=None):
query = cls.status == Payment.STATUS_PENDING
if due_date:
if isinstance(due_date, tuple):
date_query = And(Date(cls.due_date) >= due_date[0],
Date(cls.due_date) <= due_date[1])
else:
date_query = Date(cls.due_date) == due_date
query = And(query, date_query)
return store.find(cls, query)
class HighjackedViewable(cls):
tables = cls.tables[:]
tables[3] = LeftJoin(
_StockBranchSummary,
Field('_stock_summary', 'storable_id') == Storable.id)
