def get_specification_privacy_filter(user): # Circular imports. from lp.registry.model.accesspolicy import AccessPolicyGrant public_spec_filter = ( Specification.information_type.is_in(PUBLIC_INFORMATION_TYPES)) if user is None: return [public_spec_filter] elif IPersonRoles.providedBy(user): user = user.person artifact_grant_query = Coalesce( ArrayIntersects( SQL('Specification.access_grants'), Select(ArrayAgg(TeamParticipation.teamID), tables=TeamParticipation, where=(TeamParticipation.person == user))), False) policy_grant_query = Coalesce( ArrayIntersects( Array(SQL('Specification.access_policy')), Select(ArrayAgg(AccessPolicyGrant.policy_id), tables=(AccessPolicyGrant, Join( TeamParticipation, TeamParticipation.teamID == AccessPolicyGrant.grantee_id)), where=(TeamParticipation.person == user))), False) return [Or(public_spec_filter, artifact_grant_query, policy_grant_query)]
def find_active(cls, store, branch: Branch, user: LoginUser): profile = user.profile now = localnow() query = And( # All fields are optional, so default to the current user (or now) if they are missing Coalesce(cls.expire_at, now) >= now, Coalesce(cls.branch_id, branch.id) == branch.id, Coalesce(cls.user_id, user.id) == user.id, Coalesce(cls.profile_id, profile.id) == profile.id, ) return store.find(cls, query)
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 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_contributions(pofile, potmsgset_ids): """Map all users' most recent contributions to a `POFile`. Returns a dict mapping `Person` id to the creation time of their most recent `TranslationMessage` in `POFile`. This leaves some small room for error: a contribution that is masked by a diverged entry in this POFile will nevertheless produce a POFileTranslator record. Fixing that would complicate the work more than it is probably worth. :param pofile: The `POFile` to find contributions for. :param potmsgset_ids: The ids of the `POTMsgSet`s to look for, as returned by `get_potmsgset_ids`. """ store = IStore(pofile) language_id = pofile.language.id template_id = pofile.potemplate.id contribs = store.find( (TranslationMessage.submitterID, TranslationMessage.date_created), TranslationMessage.potmsgsetID.is_in(potmsgset_ids), TranslationMessage.languageID == language_id, TranslationMessage.msgstr0 != None, Coalesce(TranslationMessage.potemplateID, template_id) == template_id) contribs = contribs.config(distinct=(TranslationMessage.submitterID, )) contribs = contribs.order_by(TranslationMessage.submitterID, Desc(TranslationMessage.date_created)) return dict(contribs)
def _queryTranslatableFiles(self, no_older_than=None, languages=None): """Get `POFile`s this person could help translate. :param no_older_than: Oldest involvement to consider. If the person last worked on a `POFile` before this date, that counts as not having worked on it. :param languages: Optional set of languages to restrict search to. :return: An unsorted query yielding `POFile`s. """ if self.person.is_team: return [] tables = self._composePOFileReviewerJoins(expect_reviewer_status=False) join_condition = And( POFileTranslator.personID == self.person.id, POFileTranslator.pofileID == POFile.id, POFile.language != getUtility(ILaunchpadCelebrities).english) if no_older_than is not None: join_condition = And( join_condition, POFileTranslator.date_last_touched >= no_older_than) translator_join = Join(POFileTranslator, join_condition) tables.append(translator_join) translated_count = ( POFile.currentcount + POFile.updatescount + POFile.rosettacount) conditions = translated_count < POTemplate.messagecount # The person must not be a reviewer for this translation (unless # it's in the sense that any user gets review permissions # for it). permission = Coalesce( Distribution.translationpermission, Product.translationpermission, ProjectGroup.translationpermission) Reviewership = ClassAlias(TeamParticipation, 'Reviewership') # XXX JeroenVermeulen 2009-08-28 bug=420364: Storm's Coalesce() # can't currently infer its return type from its inputs, leading # to a "can't adapt" error. Using the enum's .value works # around the problem. not_reviewer = Or( permission == TranslationPermission.OPEN.value, And( permission == TranslationPermission.STRUCTURED.value, Translator.id == None), And( permission == TranslationPermission.RESTRICTED.value, Translator.id != None, Reviewership.id == None)) conditions = And(conditions, not_reviewer) if languages is not None: conditions = And(conditions, POFile.languageID.is_in(languages)) return Store.of(self.person).using(*tables).find(POFile, conditions)
def getBuildQueueSizes(self): """See `IBuilderSet`.""" results = ISlaveStore(BuildQueue).find( (Count(), Sum(BuildQueue.estimated_duration), Processor, Coalesce(BuildQueue.virtualized, True)), Processor.id == BuildQueue.processorID, BuildQueue.status == BuildQueueStatus.WAITING).group_by( Processor, Coalesce(BuildQueue.virtualized, True)) result_dict = {'virt': {}, 'nonvirt': {}} for size, duration, processor, virtualized in results: if virtualized is False: virt_str = 'nonvirt' else: virt_str = 'virt' result_dict[virt_str][processor.name] = (size, duration) return result_dict
class BaseTransferView(Viewable): BranchDest = ClassAlias(Branch, 'branch_dest') PersonDest = ClassAlias(Person, 'person_dest') CompanyDest = ClassAlias(Company, 'company_dest') transfer_order = TransferOrder identifier = TransferOrder.identifier identifier_str = Cast(TransferOrder.identifier, 'text') status = TransferOrder.status open_date = TransferOrder.open_date finish_date = Coalesce(TransferOrder.receival_date, TransferOrder.cancel_date) source_branch_id = TransferOrder.source_branch_id destination_branch_id = TransferOrder.destination_branch_id source_branch_name = Coalesce(NullIf(Company.fancy_name, u''), Person.name) destination_branch_name = Coalesce(NullIf(CompanyDest.fancy_name, u''), PersonDest.name) group_by = [TransferOrder, source_branch_name, destination_branch_name] tables = [ TransferOrder, Join(TransferOrderItem, TransferOrder.id == TransferOrderItem.transfer_order_id), # Source LeftJoin(Branch, TransferOrder.source_branch_id == Branch.id), LeftJoin(Person, Branch.person_id == Person.id), LeftJoin(Company, Company.person_id == Person.id), # Destination LeftJoin(BranchDest, TransferOrder.destination_branch_id == BranchDest.id), LeftJoin(PersonDest, BranchDest.person_id == PersonDest.id), LeftJoin(CompanyDest, CompanyDest.person_id == PersonDest.id), ] @property def branch(self): # We need this property for the acronym to appear in the identifier return self.store.get(Branch, self.source_branch_id)
def recalculateCounts(self): """See `ITranslatedLanguage`.""" templates = self.parent.getCurrentTemplatesCollection() pofiles = templates.joinOuterPOFile(self.language) total_count_results = list( pofiles.select(Coalesce(Sum(POTemplate.messagecount), 0), Coalesce(Sum(POFile.currentcount), 0), Coalesce(Sum(POFile.updatescount), 0), Coalesce(Sum(POFile.rosettacount), 0), Coalesce(Sum(POFile.unreviewed_count), 0), Max(POFile.date_changed))) total, imported, changed, rosetta, unreviewed, date_changed = ( total_count_results[0]) translated = imported + rosetta new = rosetta - changed self.setCounts(total, translated, new, changed, unreviewed) # We have to add a timezone to the otherwise naive-datetime object # (because we've gotten it using Max() aggregate function). if date_changed is not None: date_changed = date_changed.replace(tzinfo=pytz.UTC) self.last_changed_date = date_changed
class MedicSoldItemsView(Viewable): branch = Branch id = Sellable.id identifier = Sale.identifier code = Sellable.code description = Sellable.description category = SellableCategory.description manufacturer = ProductManufacturer.name batch_number = Coalesce(StorableBatch.batch_number, u'') batch_date = StorableBatch.create_date sale_id = Sale.id open_date = Sale.open_date confirm_date = Sale.confirm_date branch_name = Company.fancy_name medic_name = Person.name crm_number = OpticalMedic.crm_number partner = OpticalMedic.partner quantity = Sum(SaleItem.quantity) total = Sum(SaleItem.quantity * SaleItem.price) tables = [ Sellable, LeftJoin(Product, Product.id == Sellable.id), LeftJoin(SellableCategory, Sellable.category_id == SellableCategory.id), LeftJoin(ProductManufacturer, Product.manufacturer_id == ProductManufacturer.id), Join(SaleItem, SaleItem.sellable_id == Sellable.id), Join(Sale, SaleItem.sale_id == Sale.id), LeftJoin(StorableBatch, StorableBatch.id == SaleItem.batch_id), Join(Branch, Sale.branch_id == Branch.id), Join(Company, Branch.person_id == Company.person_id), Join(WorkOrderItem, WorkOrderItem.sale_item_id == SaleItem.id), Join(WorkOrder, WorkOrder.id == WorkOrderItem.order_id), Join(OpticalWorkOrder, OpticalWorkOrder.work_order_id == WorkOrder.id), Join(OpticalMedic, OpticalMedic.id == OpticalWorkOrder.medic_id), Join(Person, Person.id == OpticalMedic.person_id), ] clause = Sale.status == Sale.STATUS_CONFIRMED group_by = [ id, branch_name, code, description, category, manufacturer, StorableBatch.id, OpticalMedic.id, Person.id, Sale.id, Branch.id ]
def summarize_contributors(potemplate_id, language_id, potmsgset_ids): """Return the set of ids of persons who contributed to a `POFile`. This is a limited version of `get_contributions` that is easier to compute. """ store = IStore(POFile) contribs = store.find( TranslationMessage.submitterID, TranslationMessage.potmsgsetID.is_in(potmsgset_ids), TranslationMessage.languageID == language_id, TranslationMessage.msgstr0 != None, Coalesce(TranslationMessage.potemplateID, potemplate_id) == potemplate_id) contribs.config(distinct=True) return set(contribs)
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))
class ProductBrandStockView(Viewable): # Alias of Branch to Person table id = Product.brand brand = Coalesce(Product.brand, u'') quantity = Sum(ProductStockItem.quantity) tables = [ Product, LeftJoin(Storable, Storable.product_id == Product.id), LeftJoin(ProductStockItem, ProductStockItem.storable_id == Storable.id), LeftJoin(Branch, Branch.id == ProductStockItem.branch_id) ] group_by = [id, brand] @classmethod def find_by_branch(cls, store, branch): if branch: return store.find(cls, ProductStockItem.branch_id == branch.id) return store.find(cls)
class TransferItemView(BaseTransferView): id = TransferOrderItem.id item_quantity = TransferOrderItem.quantity item_description = Sellable.description sellable_id = Sellable.id batch_number = Coalesce(StorableBatch.batch_number, u'') batch_date = StorableBatch.create_date group_by = BaseTransferView.group_by[:] group_by.extend([TransferOrderItem, Sellable, batch_number, batch_date]) tables = BaseTransferView.tables[:] tables.extend([ Join(Sellable, Sellable.id == TransferOrderItem.sellable_id), LeftJoin(StorableBatch, StorableBatch.id == TransferOrderItem.batch_id) ]) @classmethod def find_by_branch(cls, store, sellable, branch): query = (cls.sellable_id == sellable.id, Or(cls.source_branch_id == branch.id, cls.destination_branch_id == branch.id)) return store.find(cls, query)
class SellableFullStockView(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 or None :cvar product_id: the id of the product table or None :cvar branch_id: the id of branch table or None :cvar stock: the stock of the product or None """ sellable = Sellable id = Sellable.id code = Sellable.code barcode = Sellable.barcode status = Sellable.status cost = Sellable.cost description = Sellable.description 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 base_price = Sellable.base_price max_discount = Sellable.max_discount product_id = Product.id model = Product.model unit = SellableUnit.description manufacturer = ProductManufacturer.name category_description = SellableCategory.description # Aggregates stock = Coalesce(Sum(ProductStockItem.quantity), 0) tables = [ Sellable, LeftJoin(SellableUnit, SellableUnit.id == Sellable.unit_id), LeftJoin(SellableCategory, SellableCategory.id == Sellable.category_id), LeftJoin(Product, Product.sellable_id == Sellable.id), LeftJoin(Storable, Storable.product_id == Product.id), LeftJoin(ProductStockItem, ProductStockItem.storable_id == Storable.id), LeftJoin(ProductManufacturer, Product.manufacturer_id == ProductManufacturer.id), ] group_by = [ Sellable, SellableUnit, product_id, model, unit, manufacturer, category_description ] @classmethod def find_by_branch(cls, store, branch): if branch: # We need the OR part to be able to list services query = Or(ProductStockItem.branch == branch, Eq(ProductStockItem.branch_id, None)) return store.find(cls, query) return store.find(cls) @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
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 WorkOrderWithPackageView(WorkOrderView): """A view for |workorders| in a |workorderpackage| This is the same as :class:`.WorkOrderView`, but package information is joined together """ _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") # WorkOrderPackage package_id = WorkOrderPackage.id package_identifier = WorkOrderPackage.identifier package_send_date = WorkOrderPackage.send_date package_receive_date = WorkOrderPackage.receive_date # WorkOrderPackageItem package_item_id = WorkOrderPackageItem.id package_notes = WorkOrderPackageItem.notes # Branch source_branch_name = Coalesce(_CompanySource.fancy_name, _PersonSource.name) destination_branch_name = Coalesce(_CompanyDestination.fancy_name, _PersonDestination.name) tables = WorkOrderView.tables[:] tables.extend([ LeftJoin(WorkOrderPackageItem, WorkOrderPackageItem.order_id == WorkOrder.id), LeftJoin(WorkOrderPackage, WorkOrderPackageItem.package_id == WorkOrderPackage.id), 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), ]) @classmethod def find_by_package(cls, store, package): """Find results for this view that are in the *package* :param store: the store that will be used to find the results :param package: the |workorderpackage| used to filter the results :returns: the matching views :rtype: a sequence of :class:`WorkOrderWithPackageView` """ return store.find(cls, package_id=package.id)
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 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 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)
class InventoryItemsView(Viewable): """Holds information about |inventoryitems| This is used to get the most information of an inventory item without doing lots of database queries. It's best used with :meth:`.find_by_product` """ #: the |inventoryitem| inventory_item = InventoryItem #: the |inventory| inventory = Inventory #: the |sellable| sellable = Sellable #: The |StorableBatch| batch = StorableBatch branch = Branch # InventoryItem id = InventoryItem.id product_id = InventoryItem.product_id recorded_quantity = InventoryItem.recorded_quantity counted_quantity = InventoryItem.counted_quantity actual_quantity = InventoryItem.actual_quantity product_cost = InventoryItem.product_cost is_adjusted = InventoryItem.is_adjusted reason = InventoryItem.reason # Inventory inventory_identifier = Inventory.identifier open_date = Inventory.open_date close_date = Inventory.close_date #: The name of the person that performed the inventory responsible_name = Person.name #: The code of the sellable code = Sellable.code # XXX: Maybe we should have this in the database, since the price may change price = Sellable.base_price #: The description of the product description = Sellable.description #: The number of the batch that was adjusted batch_number = Coalesce(StorableBatch.batch_number, u'') batch_date = StorableBatch.create_date tables = [ InventoryItem, Join(Inventory, InventoryItem.inventory_id == Inventory.id), Join(Product, Product.id == InventoryItem.product_id), Join(Sellable, Sellable.id == Product.id), LeftJoin(StorableBatch, InventoryItem.batch_id == StorableBatch.id), Join(LoginUser, Inventory.responsible_id == LoginUser.id), Join(Person, LoginUser.person_id == Person.id), Join(Branch, Inventory.branch_id == Branch.id), ] @classmethod def find_by_inventory(cls, store, inventory): """find results for this view that are related to the given inventory :param store: the store that will be used to find the results :param inventory: the |inventory| that should be filtered :returns: the matching views :rtype: a sequence of :class:`InventoryItemView` """ return store.find(cls, Inventory.id == inventory.id) @classmethod def find_by_product(cls, store, product): """find results for this view that references *product* :param store: the store that will be used to find the results :param product: the |product| used to filter the results :returns: the matching views :rtype: a sequence of :class:`InventoryItemView` """ return store.find(cls, product_id=product.id)