def post_search_callback(cls, sresults):
select = sresults.get_select_expr(Count(1), Sum(cls.total))
return ('count', 'sum'), select
def post_search_callback(cls, sresults):
select = sresults.get_select_expr(
Count(Distinct(Sellable.id)), Sum(Field('_stock_summary',
'stock')))
return ('count', 'sum'), select
def get_receivertip_list(store, receiver_id, language):
rtip_summary_list = []
rtips = store.find(models.ReceiverTip, receiver_id=receiver_id)
itips_ids = [rtip.internaltip_id for rtip in rtips]
itips_by_id = {}
aqs_by_itip = {}
comments_by_itip = {}
internalfiles_by_itip = {}
messages_by_rtip = {}
for itip, archivedschema in store.find(
(models.InternalTip, models.ArchivedSchema),
In(models.InternalTip.id, itips_ids), models.ArchivedSchema.hash ==
models.InternalTip.questionnaire_hash,
models.ArchivedSchema.type == u'preview'):
itips_by_id[itip.id] = itip
aqs_by_itip[itip.id] = archivedschema
result = store.find(
(models.ReceiverTip.id, Count()),
models.ReceiverTip.receiver_id == receiver_id,
models.ReceiverTip.id == models.Message.receivertip_id).group_by(
models.ReceiverTip)
for rtip_id, count in result:
messages_by_rtip[rtip_id] = count
result = store.find(
(models.InternalTip.id, Count()), In(models.InternalTip.id, itips_ids),
models.InternalTip.id == models.Comment.internaltip_id).group_by(
models.InternalTip)
for itip_id, count in result:
comments_by_itip[itip_id] = count
result = store.find(
(models.InternalTip.id, Count()), In(models.InternalTip.id, itips_ids),
models.InternalTip.id == models.InternalFile.internaltip_id).group_by(
models.InternalTip)
for itip_id, count in result:
internalfiles_by_itip[itip_id] = count
for rtip in rtips:
internaltip = itips_by_id[rtip.internaltip_id]
archivedschema = aqs_by_itip[rtip.internaltip_id]
rtip_summary_list.append({
'id':
rtip.id,
'creation_date':
datetime_to_ISO8601(internaltip.creation_date),
'last_access':
datetime_to_ISO8601(rtip.last_access),
'update_date':
datetime_to_ISO8601(internaltip.update_date),
'expiration_date':
datetime_to_ISO8601(internaltip.expiration_date),
'progressive':
internaltip.progressive,
'new':
rtip.access_counter == 0
or rtip.last_access < internaltip.update_date,
'context_id':
internaltip.context_id,
'access_counter':
rtip.access_counter,
'file_counter':
internalfiles_by_itip.get(internaltip.id, 0),
'comment_counter':
comments_by_itip.get(internaltip.id, 0),
'message_counter':
messages_by_rtip.get(rtip.id, 0),
'tor2web':
internaltip.tor2web,
'preview_schema':
db_serialize_archived_preview_schema(store, archivedschema,
language),
'preview':
internaltip.preview,
'total_score':
internaltip.total_score,
'label':
rtip.label
})
return rtip_summary_list
from stoqlib.domain.payment.operation import get_payment_operation
from stoqlib.domain.payment.payment import Payment, PaymentChangeHistory
from stoqlib.domain.payment.renegotiation import PaymentRenegotiation
from stoqlib.domain.person import Person
from stoqlib.domain.purchase import PurchaseOrder
from stoqlib.domain.sale import Sale
from stoqlib.lib.dateutils import localtoday
from stoqlib.lib.parameters import sysparam
from stoqlib.lib.translation import stoqlib_gettext
_ = stoqlib_gettext
_CommentsSummary = Select(columns=[PaymentComment.payment_id,
Alias(Count(PaymentComment.id), 'comments_number')],
tables=[PaymentComment],
group_by=[PaymentComment.payment_id]),
CommentsSummary = Alias(_CommentsSummary, '_comments')
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
class WorkOrderFinishedView(WorkOrderView):
"""A view for finished |workorders|
This is the same as :class:`.WorkOrderView`, but only finished
orders are showed here.
"""
clause = WorkOrder.status == WorkOrder.STATUS_WORK_FINISHED
_WorkOrderPackageItemsSummary = Alias(
Select(columns=[
WorkOrderPackageItem.package_id,
Alias(Count(WorkOrderPackageItem.id), 'quantity')
],
tables=[WorkOrderPackageItem],
group_by=[WorkOrderPackageItem.package_id]), '_package_items')
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")
def getMostActiveBugTrackers(self, limit=None):
"""See `IBugTrackerSet`."""
return IStore(BugTracker).find(
BugTracker, BugTracker.id == BugWatch.bugtrackerID).group_by(
BugTracker).order_by(Desc(Count(BugWatch))).config(limit=limit)
def getTemplatesAndLanguageCounts(self):
"""See `IHasTranslationTemplates`."""
join = self.getTemplatesCollection().joinOuterPOFile()
result = join.select(POTemplate, Count(POFile.id))
return result.group_by(POTemplate)
def calculate_bugsummary_rows(target):
"""Calculate BugSummary row fragments for the given `IBugTarget`.
The data is re-aggregated from BugTaskFlat, BugTag and BugSubscription.
"""
# Use a CTE to prepare a subset of BugTaskFlat, filtered to the
# relevant target and to exclude duplicates, and with has_patch
# calculated.
relevant_tasks = With(
'relevant_task',
Select((BugTaskFlat.bug_id, BugTaskFlat.information_type,
BugTaskFlat.status, BugTaskFlat.milestone_id,
BugTaskFlat.importance,
Alias(BugTaskFlat.latest_patch_uploaded != None, 'has_patch'),
BugTaskFlat.access_grants, BugTaskFlat.access_policies),
tables=[BugTaskFlat],
where=And(BugTaskFlat.duplicateof_id == None,
*get_bugtaskflat_constraint(target))))
# Storm class to reference the CTE.
class RelevantTask(BugTaskFlat):
__storm_table__ = 'relevant_task'
has_patch = Bool()
# Storm class to reference the union.
class BugSummaryPrototype(RawBugSummary):
__storm_table__ = 'bugsummary_prototype'
# Prepare a union for all combination of privacy and taggedness.
# It'll return a full set of
# (status, milestone, importance, has_patch, tag, viewed_by, access_policy)
# rows.
common_cols = (RelevantTask.status, RelevantTask.milestone_id,
RelevantTask.importance, RelevantTask.has_patch)
null_tag = Alias(Cast(None, 'text'), 'tag')
null_viewed_by = Alias(Cast(None, 'integer'), 'viewed_by')
null_policy = Alias(Cast(None, 'integer'), 'access_policy')
tag_join = Join(BugTag, BugTag.bugID == RelevantTask.bug_id)
public_constraint = RelevantTask.information_type.is_in(
PUBLIC_INFORMATION_TYPES)
private_constraint = RelevantTask.information_type.is_in(
PRIVATE_INFORMATION_TYPES)
unions = Union(
# Public, tagless
Select(common_cols + (null_tag, null_viewed_by, null_policy),
tables=[RelevantTask],
where=public_constraint),
# Public, tagged
Select(common_cols + (BugTag.tag, null_viewed_by, null_policy),
tables=[RelevantTask, tag_join],
where=public_constraint),
# Private, access grant, tagless
Select(common_cols +
(null_tag, Unnest(RelevantTask.access_grants), null_policy),
tables=[RelevantTask],
where=private_constraint),
# Private, access grant, tagged
Select(common_cols +
(BugTag.tag, Unnest(RelevantTask.access_grants), null_policy),
tables=[RelevantTask, tag_join],
where=private_constraint),
# Private, access policy, tagless
Select(
common_cols +
(null_tag, null_viewed_by, Unnest(RelevantTask.access_policies)),
tables=[RelevantTask],
where=private_constraint),
# Private, access policy, tagged
Select(
common_cols +
(BugTag.tag, null_viewed_by, Unnest(RelevantTask.access_policies)),
tables=[RelevantTask, tag_join],
where=private_constraint),
all=True)
# Select the relevant bits of the prototype rows and aggregate them.
proto_key_cols = (BugSummaryPrototype.status,
BugSummaryPrototype.milestone_id,
BugSummaryPrototype.importance,
BugSummaryPrototype.has_patch, BugSummaryPrototype.tag,
BugSummaryPrototype.viewed_by_id,
BugSummaryPrototype.access_policy_id)
origin = IStore(BugTaskFlat).with_(relevant_tasks).using(
Alias(unions, 'bugsummary_prototype'))
results = origin.find(proto_key_cols + (Count(), ))
results = results.group_by(*proto_key_cols).order_by(*proto_key_cols)
return results
def get_receivertip_list(store, receiver_id, language):
rtip_summary_list = []
rtips = store.find(ReceiverTip, ReceiverTip.receiver_id == receiver_id)
itips_ids = [rtip.internaltip_id for rtip in rtips]
itips_by_id = {}
contexts_by_id = {}
aqs_by_itip = {}
comments_by_itip = {}
internalfiles_by_itip = {}
messages_by_rtip = {}
for itip, context, archivedschema in store.find(
(InternalTip, Context, ArchivedSchema), In(InternalTip.id, itips_ids),
Context.id == InternalTip.context_id,
ArchivedSchema.hash == InternalTip.questionnaire_hash,
ArchivedSchema.type == u'preview'):
itips_by_id[itip.id] = itip
contexts_by_id[context.id] = context
aqs_by_itip[itip.id] = archivedschema
result = store.find(
(ReceiverTip.id, Count()), ReceiverTip.receiver_id == receiver_id,
ReceiverTip.id == Message.receivertip_id).group_by(ReceiverTip)
for rtip_id, count in result:
messages_by_rtip[rtip_id] = count
result = store.find(
(InternalTip.id, Count()), In(InternalTip.id, itips_ids),
InternalTip.id == Comment.internaltip_id).group_by(InternalTip)
for itip_id, count in result:
comments_by_itip[itip_id] = count
result = store.find(
(InternalTip.id, Count()), In(InternalTip.id, itips_ids),
InternalTip.id == InternalFile.internaltip_id).group_by(InternalTip)
for itip_id, count in result:
internalfiles_by_itip[itip_id] = count
for rtip in rtips:
internaltip = itips_by_id[rtip.internaltip_id]
context = contexts_by_id[internaltip.context_id]
archivedschema = aqs_by_itip[rtip.internaltip_id]
mo = Rosetta(context.localized_keys)
mo.acquire_storm_object(context)
rtip_summary_list.append({
'id':
rtip.id,
'creation_date':
datetime_to_ISO8601(internaltip.creation_date),
'last_access':
datetime_to_ISO8601(rtip.last_access),
'update_date':
datetime_to_ISO8601(internaltip.update_date),
'expiration_date':
datetime_to_ISO8601(internaltip.expiration_date),
'progressive':
internaltip.progressive,
'new':
rtip.access_counter == 0
or rtip.last_access < internaltip.update_date,
'context_name':
mo.dump_localized_key('name', language),
'access_counter':
rtip.access_counter,
'file_counter':
internalfiles_by_itip.get(internaltip.id, 0),
'comment_counter':
comments_by_itip.get(internaltip.id, 0),
'message_counter':
messages_by_rtip.get(rtip.id, 0),
'tor2web':
internaltip.tor2web,
'preview_schema':
db_serialize_archived_preview_schema(store, archivedschema,
language),
'preview':
internaltip.preview,
'total_score':
internaltip.total_score,
'label':
rtip.label
})
return rtip_summary_list
def _calculate_tag_query(conditions, tags):
"""Determine tag-related conditions and assemble a query.
:param conditions: the other conditions that constrain the query.
:param tags: the list of tags that the bug has.
"""
# These are tables and joins we will want. We leave out the tag join
# because that needs to be added conditionally.
tables = [
StructuralSubscription,
Join(BugSubscriptionFilter,
BugSubscriptionFilter.structural_subscription_id ==
StructuralSubscription.id),
LeftJoin(BugSubscriptionFilterStatus,
BugSubscriptionFilterStatus.filter_id ==
BugSubscriptionFilter.id),
LeftJoin(BugSubscriptionFilterImportance,
BugSubscriptionFilterImportance.filter_id ==
BugSubscriptionFilter.id),
LeftJoin(BugSubscriptionFilterInformationType,
BugSubscriptionFilterInformationType.filter_id ==
BugSubscriptionFilter.id)]
tag_join = LeftJoin(
BugSubscriptionFilterTag,
BugSubscriptionFilterTag.filter_id == BugSubscriptionFilter.id)
# If the bug has no tags, this is relatively easy. Otherwise, not so
# much.
if len(tags) == 0:
# The bug has no tags. We should leave out filters that
# require any generic non-empty set of tags
# (BugSubscriptionFilter.include_any_tags), which we do with
# the conditions.
conditions.append(Not(BugSubscriptionFilter.include_any_tags))
tables.append(tag_join)
return Select(
BugSubscriptionFilter.id,
tables=tables,
where=And(*conditions),
# We have to make sure that the filter does not require
# any *specific* tags. We do that with a GROUP BY on the
# filters, and then a HAVING clause that aggregates the
# BugSubscriptionFilterTags that are set to "include" the
# tag. (If it is not an include, that is an exclude, and a
# bug without tags will not have a particular tag, so we can
# ignore those in this case.) This requires a CASE
# statement within the COUNT.
group_by=(BugSubscriptionFilter.id,),
having=Count(
SQL('CASE WHEN BugSubscriptionFilterTag.include '
'THEN BugSubscriptionFilterTag.tag END')) == 0)
else:
# The bug has some tags. This will require a bit of fancy
# footwork. First, though, we will simply want to leave out
# filters that should only match bugs without tags.
conditions.append(Not(BugSubscriptionFilter.exclude_any_tags))
# We're going to have to do a union with another query. One
# query will handle filters that are marked to include *any*
# of the filter's selected tags, and the other query will
# handle filters that include *all* of the filter's selected
# tags (as determined by BugSubscriptionFilter.find_all_tags).
# Every aspect of the unioned queries' WHERE clauses *other
# than tags* will need to be the same, and so we perform that
# separately, first. When Storm supports the WITH statement
# (bug 729134), we can consider folding this back into a single
# query.
candidates = list(
IStore(BugSubscriptionFilter).using(*tables).find(
BugSubscriptionFilter.id, *conditions))
if not candidates:
return None
# As mentioned, in this first SELECT we handle filters that
# match any of the filter's tags. This can be a relatively
# straightforward query--we just need a bit more added to
# our WHERE clause, and we don't need a GROUP BY/HAVING.
first_select = Select(
BugSubscriptionFilter.id,
tables=[BugSubscriptionFilter, tag_join],
where=And(
Or( # We want filters that proclaim they simply want any tags.
BugSubscriptionFilter.include_any_tags,
# Also include filters that match any tag...
And(Not(BugSubscriptionFilter.find_all_tags),
Or( # ...with a positive match...
And(BugSubscriptionFilterTag.include,
In(BugSubscriptionFilterTag.tag, tags)),
# ...or with a negative match...
And(Not(BugSubscriptionFilterTag.include),
Not(In(BugSubscriptionFilterTag.tag, tags))),
# ...or if the filter does not specify any tags.
BugSubscriptionFilterTag.tag == None))),
In(BugSubscriptionFilter.id, candidates)))
# We have our first clause. Now we start on the second one:
# handling filters that match *all* tags.
# This second query will have a HAVING clause, which is where some
# tricky bits happen. We first make a SQL snippet that
# represents the tags on this bug. It is straightforward
# except for one subtle hack: the addition of the empty
# space in the array. This is because we are going to be
# aggregating the tags on the filters using ARRAY_AGG, which
# includes NULLs (unlike most other aggregators). That
# is an issue here because we use CASE statements to divide
# up the set of tags that are supposed to be included and
# supposed to be excluded. This means that if we aggregate
# "CASE WHEN BugSubscriptionFilterTag.include THEN
# BugSubscriptionFilterTag.tag END" then that array will
# include NULL. SQL treats NULLs as unknowns that can never
# be matched, so the array of ['foo', 'bar', NULL] does not
# contain the array of ['foo', NULL] ("SELECT
# ARRAY['foo','bar',NULL]::TEXT[] @>
# ARRAY['foo',NULL]::TEXT[];" is false). Therefore, so we
# can make the HAVING statement we want to make without
# defining a custom Postgres aggregator, we use a single
# space as, effectively, NULL. This is safe because a
# single space is not an acceptable tag. Again, the
# clearest alternative is defining a custom Postgres aggregator.
tags_array = "ARRAY[%s,' ']::TEXT[]" % ",".join(
quote(tag) for tag in tags)
# Now let's build the select itself.
second_select = Select(
BugSubscriptionFilter.id,
tables=[BugSubscriptionFilter, tag_join],
# Our WHERE clause is straightforward. We are simply
# focusing on BugSubscriptionFilter.find_all_tags, when the
# first SELECT did not consider it.
where=And(BugSubscriptionFilter.find_all_tags,
In(BugSubscriptionFilter.id, candidates)),
# The GROUP BY collects the filters together.
group_by=(BugSubscriptionFilter.id,),
having=And(
# The list of tags should be a superset of the filter tags to
# be included.
ArrayContains(
SQL(tags_array),
# This next line gives us an array of the tags that the
# filter wants to include. Notice that it includes the
# empty string when the condition does not match, per the
# discussion above.
ArrayAgg(
SQL("CASE WHEN BugSubscriptionFilterTag.include "
"THEN BugSubscriptionFilterTag.tag "
"ELSE ' '::TEXT END"))),
# The list of tags should also not intersect with the
# tags that the filter wants to exclude.
Not(
ArrayIntersects(
SQL(tags_array),
# This next line gives us an array of the tags
# that the filter wants to exclude. We do not bother
# with the empty string, and therefore allow NULLs
# into the array, because in this case we are
# determining whether the sets intersect, not if the
# first set subsumes the second.
ArrayAgg(
SQL('CASE WHEN '
'NOT BugSubscriptionFilterTag.include '
'THEN BugSubscriptionFilterTag.tag END'))))))
# Everything is ready. Return the union.
return Union(first_select, second_select)