def getMergeProposalsForReviewer(self, reviewer, status=None):
"""See `IBranchCollection`."""
tables = [
BranchMergeProposal,
Join(CodeReviewVoteReference,
CodeReviewVoteReference.branch_merge_proposalID == \
BranchMergeProposal.id),
LeftJoin(CodeReviewComment,
CodeReviewVoteReference.commentID == CodeReviewComment.id)]
expressions = [
CodeReviewVoteReference.reviewer == reviewer,
BranchMergeProposal.source_branchID.is_in(self._getBranchSelect())]
visibility = self._getBranchVisibilityExpression()
if visibility:
expressions.append(BranchMergeProposal.target_branchID.is_in(
Select(Branch.id, visibility)))
if status is not None:
expressions.append(
BranchMergeProposal.queue_status.is_in(status))
proposals = self.store.using(*tables).find(
BranchMergeProposal, *expressions)
# Apply sorting here as we can't do it in the browser code. We need
# to think carefully about the best places to do this, but not here
# nor now.
proposals.order_by(Desc(CodeReviewComment.vote))
return proposals
class TillClosedView(Viewable):
id = Till.id
observations = Till.observations
opening_date = Date(Till.opening_date)
closing_date = Date(Till.closing_date)
initial_cash_amount = Till.initial_cash_amount
final_cash_amount = Till.final_cash_amount
branch_id = BranchStation.branch_id
_ResponsibleOpen = ClassAlias(Person, "responsible_open")
_ResponsibleClose = ClassAlias(Person, "responsible_close")
_LoginUserOpen = ClassAlias(LoginUser, "login_responsible_open")
_LoginUserClose = ClassAlias(LoginUser, "login_responsible_close")
responsible_open_name = _ResponsibleOpen.name
responsible_close_name = _ResponsibleClose.name
tables = [
Till,
Join(BranchStation, BranchStation.id == Till.station_id),
# These two need to be left joins, since historical till dont have a
# responsible
LeftJoin(_LoginUserOpen,
Till.responsible_open_id == _LoginUserOpen.id),
LeftJoin(_LoginUserClose,
Till.responsible_close_id == _LoginUserClose.id),
LeftJoin(_ResponsibleOpen,
_LoginUserOpen.person_id == _ResponsibleOpen.id),
LeftJoin(_ResponsibleClose,
_LoginUserClose.person_id == _ResponsibleClose.id),
]
clause = Till.status == Till.STATUS_CLOSED
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)]
class ProductComponentView(ProductFullStockView):
"""Stores information about production products"""
tables = ProductFullStockView.tables[:]
tables.extend([
Join(ProductComponent, ProductComponent.product_id == Product.id),
])
def _getPersonalBranch(self, person, branch_name):
"""Find a personal branch given its path segments."""
origin = [Branch, Join(Person, Branch.owner == Person.id)]
return IStore(Branch).using(*origin).find(
Branch, Person.name == person, Branch.distroseries == None,
Branch.product == None, Branch.sourcepackagename == None,
Branch.name == branch_name).one()
def getNonActiveSubscribers(self):
"""See `IArchiveSubscriber`."""
store = Store.of(self)
if self.subscriber.is_team:
# We get all the people who already have active tokens for
# this archive (for example, through separate subscriptions).
auth_token = LeftJoin(
ArchiveAuthToken,
And(ArchiveAuthToken.person_id == Person.id,
ArchiveAuthToken.archive_id == self.archive_id,
ArchiveAuthToken.date_deactivated == None))
team_participation = Join(TeamParticipation,
TeamParticipation.personID == Person.id)
# Only return people with preferred email address set.
preferred_email = Join(EmailAddress,
EmailAddress.personID == Person.id)
# We want to get all participants who are themselves
# individuals, not teams:
non_active_subscribers = store.using(
Person, team_participation, preferred_email, auth_token).find(
(Person, EmailAddress),
EmailAddress.status == EmailAddressStatus.PREFERRED,
TeamParticipation.teamID == self.subscriber_id,
Person.teamowner == None,
# There is no existing archive auth token.
ArchiveAuthToken.person_id == None)
non_active_subscribers.order_by(Person.name)
return non_active_subscribers
else:
# Subscriber is not a team.
token_set = getUtility(IArchiveAuthTokenSet)
if token_set.getActiveTokenForArchiveAndPerson(
self.archive, self.subscriber) is not None:
# There are active tokens, so return an empty result
# set.
return EmptyResultSet()
# Otherwise return a result set containing only the
# subscriber and their preferred email address.
return store.find(
(Person, EmailAddress), Person.id == self.subscriber_id,
EmailAddress.personID == Person.id,
EmailAddress.status == EmailAddressStatus.PREFERRED)
def get_person_by_crm(cls, store, document):
query = cls.crm_number == document
tables = [
Person,
Join(OpticalMedic, Person.id == OpticalMedic.person_id)
]
return store.using(*tables).find(Person, query).one()
def getByTranslator(self, translator):
"""See ITranslatorSet."""
store = Store.of(translator)
# TranslationGroup is referenced directly in SQL to avoid
# a cyclic import.
origin = [
Translator,
Join(TeamParticipation,
TeamParticipation.teamID == Translator.translatorID),
Join("TranslationGroup",
on="TranslationGroup.id = Translator.translationgroup")
]
result = store.using(*origin).find(
Translator, TeamParticipation.person == translator)
return result.order_by("TranslationGroup.title")
def _get_by_category(self, store, category_name):
tables = [Client,
Join(ClientCategory, Client.category_id == ClientCategory.id)]
clients = store.using(*tables).find(Client, ClientCategory.name == category_name)
retval = []
for client in clients:
retval.append(self._dump_client(client))
return retval
def sample_binary_packages(self):
"""See IDistributionSourcePackageRelease."""
# Avoid circular imports.
from lp.registry.model.distroseries import DistroSeries
from lp.soyuz.model.distroarchseries import DistroArchSeries
from lp.soyuz.model.distroseriespackagecache import (
DistroSeriesPackageCache)
archive_ids = list(self.distribution.all_distro_archive_ids)
result_row = (SQL('DISTINCT ON(BinaryPackageName.name) 0 AS ignore'),
DistroSeries, BinaryPackageName,
DistroSeriesPackageCache)
tables = (
BinaryPackagePublishingHistory,
Join(
DistroArchSeries, DistroArchSeries.id ==
BinaryPackagePublishingHistory.distroarchseriesID),
Join(DistroSeries,
DistroArchSeries.distroseriesID == DistroSeries.id),
Join(
BinaryPackageRelease, BinaryPackageRelease.id ==
BinaryPackagePublishingHistory.binarypackagereleaseID),
Join(
BinaryPackageName, BinaryPackageName.id ==
BinaryPackageRelease.binarypackagenameID),
Join(BinaryPackageBuild,
BinaryPackageBuild.id == BinaryPackageRelease.buildID),
LeftJoin(
DistroSeriesPackageCache,
And(
DistroSeriesPackageCache.distroseries == DistroSeries.id,
DistroSeriesPackageCache.archiveID.is_in(archive_ids),
DistroSeriesPackageCache.binarypackagename ==
BinaryPackageName.id)))
all_published = Store.of(self.distribution).using(*tables).find(
result_row, DistroSeries.distribution == self.distribution,
BinaryPackagePublishingHistory.archiveID.is_in(archive_ids),
BinaryPackageBuild.source_package_release ==
self.sourcepackagerelease)
all_published = all_published.order_by(BinaryPackageName.name)
def make_dsb_package(row):
_, ds, bpn, package_cache = row
return DistroSeriesBinaryPackage(ds, bpn, package_cache)
return DecoratedResultSet(all_published, make_dsb_package)
def subscribedBy(self, person):
"""See `IGitCollection`."""
return self._filterBy(
[GitSubscription.person == person],
table=GitSubscription,
join=Join(GitSubscription,
GitSubscription.repository == GitRepository.id),
symmetric=False)
def _getTranslatorJoins():
# XXX CarlosPerelloMarin 2007-03-31 bug=102257:
# The KarmaCache table doesn't have a field to store karma per
# language, so we are actually returning the people with the most
# translation karma that have this language selected in their
# preferences.
from lp.registry.model.person import PersonLanguage
return Join(
PersonLanguage,
Join(
KarmaCache, KarmaCategory,
And(KarmaCategory.name == 'translations',
KarmaCache.categoryID == KarmaCategory.id,
KarmaCache.productID == None, KarmaCache.projectID == None,
KarmaCache.sourcepackagenameID == None,
KarmaCache.distributionID == None)),
PersonLanguage.personID == KarmaCache.personID)
def linkedToBugs(self, bugs):
"""See `IBranchCollection`."""
bug_ids = [bug.id for bug in bugs]
return self._filterBy([In(BugBranch.bugID, bug_ids)],
table=BugBranch,
join=Join(BugBranch,
BugBranch.branch == Branch.id),
symmetric=False)
def subscribedBy(self, person):
"""See `IBranchCollection`."""
return self._filterBy(
[BranchSubscription.person == person],
table=BranchSubscription,
join=Join(BranchSubscription,
BranchSubscription.branch == Branch.id),
symmetric=False)
def isSeries(self):
"""See `IBranchCollection`."""
# Circular imports.
from lp.registry.model.productseries import ProductSeries
return self._filterBy([Branch.id == ProductSeries.branchID],
table=ProductSeries,
join=Join(ProductSeries,
Branch.id == ProductSeries.branchID))
def getTranslatables(self):
"""Return an iterator over products that are translatable in LP.
Only products with IProduct.translations_usage set to
ServiceUsage.LAUNCHPAD are considered translatable.
"""
store = Store.of(self)
origin = [
Product,
Join(ProductSeries, Product.id == ProductSeries.productID),
Join(POTemplate, ProductSeries.id == POTemplate.productseriesID),
]
return store.using(*origin).find(
Product,
Product.projectgroup == self.id,
Product.translations_usage == ServiceUsage.LAUNCHPAD,
).config(distinct=True)
def _naiveGetMergeProposals(self, statuses=None, for_branches=None,
target_branch=None, merged_revnos=None,
merged_revision=None, eager_load=False):
Target = ClassAlias(Branch, "target")
extra_tables = list(set(
self._tables.values() + self._asymmetric_tables.values()))
tables = [Branch] + extra_tables + [
Join(BranchMergeProposal, And(
Branch.id == BranchMergeProposal.source_branchID,
*(self._branch_filter_expressions +
self._asymmetric_filter_expressions))),
Join(Target, Target.id == BranchMergeProposal.target_branchID),
]
expressions = self._getBranchVisibilityExpression()
expressions.extend(self._getBranchVisibilityExpression(Target))
if for_branches is not None:
branch_ids = [branch.id for branch in for_branches]
expressions.append(
BranchMergeProposal.source_branchID.is_in(branch_ids))
if target_branch is not None:
expressions.append(
BranchMergeProposal.target_branch == target_branch)
if merged_revnos is not None:
expressions.append(
BranchMergeProposal.merged_revno.is_in(merged_revnos))
if merged_revision is not None:
expressions.extend([
BranchMergeProposal.merged_revno == BranchRevision.sequence,
BranchRevision.revision_id == Revision.id,
BranchRevision.branch_id ==
BranchMergeProposal.target_branchID,
Revision.revision_id == merged_revision
])
tables.extend([BranchRevision, Revision])
if statuses is not None:
expressions.append(
BranchMergeProposal.queue_status.is_in(statuses))
resultset = self.store.using(*tables).find(
BranchMergeProposal, *expressions)
if not eager_load:
return resultset
else:
loader = partial(
BranchMergeProposal.preloadDataForBMPs, user=self._user)
return DecoratedResultSet(resultset, pre_iter_hook=loader)
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 TillFiscalOperationsView(Viewable):
"""Stores informations about till payment tables
:attribute date: the date when the entry was created
:attribute description: the entry description
:attribute value: the entry value
:attribute station_name: the value of name branch_station name column
"""
id = TillEntry.id
identifier = Payment.identifier
identifier_str = Cast(Payment.identifier, 'text')
date = Payment.open_date
description = Payment.description
value = Payment.value
cfop = CfopData.code
station_name = BranchStation.name
branch_id = Branch.id
status = Till.status
tables = [
TillEntry,
Join(Payment, Payment.id == TillEntry.payment_id),
Join(Till, Till.id == TillEntry.till_id),
Join(BranchStation, BranchStation.id == Till.station_id),
Join(Branch, Branch.id == BranchStation.branch_id),
Join(PaymentGroup, PaymentGroup.id == Payment.group_id),
Join(Sale, Sale.group_id == PaymentGroup.id),
Join(CfopData, CfopData.id == Sale.cfop_id),
]
def _fetch_children_or_parents(self, join_cond, cond, user):
from lp.blueprints.model.specificationsearch import (
get_specification_privacy_filter)
return list(Store.of(self).using(
Specification,
Join(SpecificationDependency, join_cond == self.id)).find(
Specification,
cond == Specification.id, *get_specification_privacy_filter(user)
).order_by(Specification.title))
def inDistributionSourcePackage(self, distro_source_package):
"""See `IBranchCollection`."""
distribution = distro_source_package.distribution
sourcepackagename = distro_source_package.sourcepackagename
return self._filterBy(
[DistroSeries.distribution == distribution,
Branch.sourcepackagename == sourcepackagename],
table=Distribution,
join=Join(DistroSeries, Branch.distroseries == DistroSeries.id))
class StockDecreaseView(Viewable):
"""Stores information about all stock decreases
"""
_PersonBranch = ClassAlias(Person, "person_branch")
id = StockDecrease.id
identifier = StockDecrease.identifier
confirm_date = StockDecrease.confirm_date
branch_name = _PersonBranch.name
removed_by_name = Person.name
tables = [
StockDecrease,
Join(Employee, StockDecrease.removed_by_id == Employee.id),
Join(Person, Employee.person_id == Person.id),
Join(Branch, StockDecrease.branch_id == Branch.id),
Join(_PersonBranch, Branch.person_id == _PersonBranch.id),
]
class TransferItemView(BaseTransferView):
id = TransferOrderItem.id
item_quantity = TransferOrderItem.quantity
item_description = Sellable.description
group_by = BaseTransferView.group_by[:]
group_by.extend([TransferOrderItem, Sellable])
tables = BaseTransferView.tables[:]
tables.append(Join(Sellable, Sellable.id == TransferOrderItem.sellable_id))
class OpticalWorkOrderItemsView(Viewable):
optical_work_order = OpticalWorkOrder
work_order = WorkOrder
optical_product = OpticalProduct
work_order_item = WorkOrderItem
# OpticalWorkOrder
id = OpticalWorkOrder.id
# WorkOrder
work_order_id = WorkOrder.id
# WorkOrderItem
work_order_item_id = WorkOrderItem.id
# OpticalProduct
optical_product_id = OpticalProduct.id
quantity = WorkOrderItem.quantity
tables = [
OpticalWorkOrder,
LeftJoin(WorkOrder, WorkOrder.id == OpticalWorkOrder.work_order_id),
LeftJoin(WorkOrderItem, WorkOrderItem.order_id == WorkOrder.id),
Join(Sellable, Sellable.id == WorkOrderItem.sellable_id),
Join(OpticalProduct, OpticalProduct.product_id == Sellable.id),
]
group_by = [id, work_order_id, work_order_item_id, optical_product_id]
@property
def sellable(self):
return self.work_order_item.sellable
@classmethod
def find_by_order(cls, store, work_order):
"""Find all items on of the given work_order
:param work_order: |work_order|
"""
return store.find(cls, work_order_id=work_order.id)
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 officialBranches(self, pocket=None):
"""See `IBranchCollection`"""
if pocket is None:
expressions = []
else:
expressions = [SeriesSourcePackageBranch.pocket == pocket]
return self._filterBy(
expressions,
table=SeriesSourcePackageBranch,
join=Join(SeriesSourcePackageBranch,
SeriesSourcePackageBranch.branch == Branch.id))
def targetedBy(self, person, since=None):
"""See `IBranchCollection`."""
clauses = [BranchMergeProposal.registrant == person]
if since is not None:
clauses.append(BranchMergeProposal.date_created >= since)
return self._filterBy(
clauses,
table=BranchMergeProposal,
join=Join(BranchMergeProposal,
BranchMergeProposal.target_branch == Branch.id),
symmetric=False)
def get_inventory_data(self):
"""Returns a generator with the details of the Inventory
Each item contains:
- The |inventoryitem|
- the |storable|
- the |product|
- the |sellable|
- the |storablebatch|
"""
store = self.store
tables = [InventoryItem,
Join(Product, Product.id == InventoryItem.product_id),
Join(Sellable, Sellable.id == Product.id),
LeftJoin(Storable, Storable.id == Product.id),
LeftJoin(StorableBatch, StorableBatch.id == InventoryItem.batch_id)]
return store.using(*tables).find(
(InventoryItem, Storable, Product, Sellable, StorableBatch),
InventoryItem.inventory_id == self.id)
def _getTeamIdsAndMailingListIds(self, team_names):
"""Return a tuple of team and mailing list Ids for the team names."""
store = IStore(MailingList)
tables = (Person, Join(MailingList, MailingList.team == Person.id))
results = set(
store.using(*tables).find((Person.id, MailingList.id),
And(Person.name.is_in(team_names),
Person.teamowner != None)))
team_ids = [result[0] for result in results]
list_ids = [result[1] for result in results]
return team_ids, list_ids
class ProductBranchStockView(Viewable):
"""Stores information about the stock of a certain |product| among
all branches
"""
branch = Branch
id = Branch.id
branch_name = Person.name
storable_id = ProductStockItem.storable_id
stock = ProductStockItem.quantity
tables = [
Branch,
Join(Person, Person.id == Branch.person_id),
Join(ProductStockItem, ProductStockItem.branch_id == Branch.id),
]
@classmethod
def find_by_storable(cls, store, storable):
return store.find(cls, storable_id=storable.id)