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)