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)