def _updateTranslationMessages(self, tm_ids): # Unset imported messages that might be in the way. PreviousImported = ClassAlias(TranslationMessage, 'PreviousImported') CurrentTranslation = ClassAlias(TranslationMessage, 'CurrentTranslation') previous_imported_select = Select( PreviousImported.id, tables=[PreviousImported, CurrentTranslation], where=And( PreviousImported.is_current_upstream == True, (PreviousImported.potmsgsetID == CurrentTranslation.potmsgsetID), Or( And(PreviousImported.potemplateID == None, CurrentTranslation.potemplateID == None), (PreviousImported.potemplateID == CurrentTranslation.potemplateID)), PreviousImported.languageID == CurrentTranslation.languageID, CurrentTranslation.id.is_in(tm_ids))) previous_imported = self.store.find( TranslationMessage, TranslationMessage.id.is_in(previous_imported_select)) previous_imported.set(is_current_upstream=False) translations = self.store.find(TranslationMessage, TranslationMessage.id.is_in(tm_ids)) translations.set(is_current_upstream=True)
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), ])
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
class ReturnedSalesView(Viewable): PersonBranch = ClassAlias(Person, 'person_branch') PersonResponsible = ClassAlias(Person, 'responsible_sale') PersonClient = ClassAlias(Person, 'person_client') returned_sale = ReturnedSale id = ReturnedSale.id identifier = ReturnedSale.identifier identifier_str = Cast(ReturnedSale.identifier, 'text') return_date = ReturnedSale.return_date reason = ReturnedSale.reason invoice_number = ReturnedSale.invoice_number sale_id = Sale.id sale_identifier = Sale.identifier sale_identifier_str = Cast(Sale.identifier, 'text') responsible_name = PersonResponsible.name branch_name = PersonBranch.name client_name = PersonClient.name tables = [ ReturnedSale, Join(Sale, Sale.id == ReturnedSale.sale_id), Join(LoginUser, LoginUser.id == ReturnedSale.responsible_id), Join(PersonResponsible, PersonResponsible.id == LoginUser.person_id), Join(Branch, Branch.id == ReturnedSale.branch_id), Join(PersonBranch, PersonBranch.id == Branch.person_id), LeftJoin(Client, Client.id == Sale.client_id), LeftJoin(PersonClient, PersonClient.id == Client.person_id), ]
class TransferOrderView(Viewable): BranchDest = ClassAlias(Branch, 'branch_dest') PersonDest = ClassAlias(Person, 'person_dest') transfer_order = TransferOrder id = TransferOrder.id identifier = TransferOrder.identifier identifier_str = Cast(TransferOrder.identifier, 'text') status = TransferOrder.status open_date = TransferOrder.open_date receival_date = TransferOrder.receival_date source_branch_id = TransferOrder.source_branch_id destination_branch_id = TransferOrder.destination_branch_id source_branch_name = Person.name destination_branch_name = PersonDest.name # Aggregates total_items = Sum(TransferOrderItem.quantity) 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), # Destination LeftJoin(BranchDest, TransferOrder.destination_branch_id == BranchDest.id), LeftJoin(PersonDest, BranchDest.person_id == PersonDest.id), ]
class AccountTransactionView(Viewable): """AccountTransactionView provides a fast view of the transactions tied to a specific |account|. It's mainly used to show a ledger. """ Account_Dest = ClassAlias(Account, 'account_dest') Account_Source = ClassAlias(Account, 'account_source') transaction = AccountTransaction id = AccountTransaction.id code = AccountTransaction.code description = AccountTransaction.description value = AccountTransaction.value date = AccountTransaction.date dest_account_id = Account_Dest.id dest_account_description = Account_Dest.description source_account_id = Account_Source.id source_account_description = Account_Source.description tables = [ AccountTransaction, LeftJoin(Account_Dest, AccountTransaction.account_id == Account_Dest.id), LeftJoin(Account_Source, AccountTransaction.source_account_id == Account_Source.id), ] @classmethod def get_for_account(cls, account, store): """Get all transactions for this |account|, see Account.transaction""" return store.find( cls, Or(account.id == AccountTransaction.account_id, account.id == AccountTransaction.source_account_id)) def get_account_description(self, account): """Get description of the other |account|, eg. the one which is transfered to/from. """ if self.source_account_id == account.id: return self.dest_account_description elif self.dest_account_id == account.id: return self.source_account_description else: raise AssertionError def get_value(self, account): """Gets the value for this |account|. For a destination |account| this will be negative """ if self.dest_account_id == account.id: return self.value else: return -self.value
def iterReady(job_type=None): from lp.code.model.branch import Branch SourceBranch = ClassAlias(Branch) TargetBranch = ClassAlias(Branch) clauses = [ BranchMergeProposalJob.job == Job.id, Job._status.is_in([JobStatus.WAITING, JobStatus.RUNNING]), BranchMergeProposalJob.branch_merge_proposal == BranchMergeProposal.id, BranchMergeProposal.source_branch == SourceBranch.id, BranchMergeProposal.target_branch == TargetBranch.id, ] if job_type is not None: clauses.append(BranchMergeProposalJob.job_type == job_type) jobs = IMasterStore(Branch).find( (BranchMergeProposalJob, Job, BranchMergeProposal, SourceBranch, TargetBranch), And(*clauses)) # Order by the job status first (to get running before waiting), then # the date_created, then job type. This should give us all creation # jobs before comment jobs. jobs = jobs.order_by(Desc(Job._status), Job.date_created, Desc(BranchMergeProposalJob.job_type)) # Now only return one job for any given merge proposal. ready_jobs = [] seen_merge_proposals = set() for bmp_job, job, bmp, source, target in jobs: # If we've seen this merge proposal already, skip this job. if bmp.id in seen_merge_proposals: continue # We have now seen this merge proposal. seen_merge_proposals.add(bmp.id) # If the job is running, then skip it if job.status == JobStatus.RUNNING: continue derived_job = bmp_job.makeDerived() # If the job is an update preview diff, then check that it is # ready. if IUpdatePreviewDiffJob.providedBy(derived_job): try: derived_job.checkReady() except (UpdatePreviewDiffNotReady, BranchHasPendingWrites): # If the job was created under 15 minutes ago wait a bit. minutes = ( config.codehosting.update_preview_diff_ready_timeout) cut_off_time = (datetime.now(pytz.UTC) - timedelta(minutes=minutes)) if job.date_created > cut_off_time: continue ready_jobs.append(derived_job) return ready_jobs
class DeliveryView(Viewable): PersonTransporter = ClassAlias(Person, 'person_transporter') PersonClient = ClassAlias(Person, 'person_client') delivery = Delivery # Delivery id = Delivery.id status = Delivery.status tracking_code = Delivery.tracking_code open_date = Delivery.open_date deliver_date = Delivery.deliver_date receive_date = Delivery.receive_date identifier_str = Cast(Sale.identifier, 'text') # Transporter transporter_name = PersonTransporter.name # Client client_name = PersonClient.name # Sale sale_identifier = Sale.identifier # Address address_id = Delivery.address_id tables = [ Delivery, LeftJoin(Transporter, Transporter.id == Delivery.transporter_id), LeftJoin(PersonTransporter, PersonTransporter.id == Transporter.person_id), LeftJoin(SaleItem, SaleItem.id == Delivery.service_item_id), LeftJoin(Sale, Sale.id == SaleItem.sale_id), LeftJoin(Client, Client.id == Sale.client_id), LeftJoin(PersonClient, PersonClient.id == Client.person_id), # LeftJoin(Address, # Address.person_id == Client.person_id), ] @property def status_str(self): return Delivery.statuses[self.status] @property def address_str(self): return self.store.get(Address, self.address_id).get_description()
class ServicesSearchExtention(SearchExtension): PersonMedic = ClassAlias(Person, 'person_medic') spec_attributes = dict( manufacturer_name=ProductManufacturer.name, medic_name=PersonMedic.name, ) spec_joins = [ LeftJoin(Product, Product.id == Sellable.id), LeftJoin(ProductManufacturer, Product.manufacturer_id == ProductManufacturer.id), LeftJoin(OpticalWorkOrder, OpticalWorkOrder.work_order_id == WorkOrder.id), LeftJoin(OpticalMedic, OpticalWorkOrder.medic_id == OpticalMedic.id), LeftJoin(PersonMedic, PersonMedic.id == OpticalMedic.person_id), ] def get_columns(self): return [ SearchColumn('manufacturer_name', title=_('Manufacturer'), data_type=str, visible=False), SearchColumn('medic_name', title=_('Medic'), data_type=str, visible=False), ]
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)
class CardPaymentView(Viewable): """A view for credit providers.""" _DraweePerson = ClassAlias(Person, "drawee_person") payment = Payment credit_card_data = CreditCardData # Payment Columns id = Payment.id identifier = Payment.identifier identifier_str = Cast(Payment.identifier, 'text') description = Payment.description due_date = Payment.due_date paid_date = Payment.paid_date status = Payment.status value = Payment.value # CreditCardData fare = CreditCardData.fare fee = CreditCardData.fee fee_calc = CreditCardData.fee_value card_type = CreditCardData.card_type auth = CreditCardData.auth device_id = CardPaymentDevice.id device_name = CardPaymentDevice.description drawee_name = _DraweePerson.name provider_name = CreditProvider.short_name sale_id = Sale.id renegotiation_id = PaymentRenegotiation.id tables = [ Payment, Join(PaymentMethod, PaymentMethod.id == Payment.method_id), Join(CreditCardData, CreditCardData.payment_id == Payment.id), Join(CreditProvider, CreditProvider.id == CreditCardData.provider_id), LeftJoin(CardPaymentDevice, CardPaymentDevice.id == CreditCardData.device_id), LeftJoin(PaymentGroup, PaymentGroup.id == Payment.group_id), LeftJoin(_DraweePerson, _DraweePerson.id == PaymentGroup.payer_id), LeftJoin(Sale, Sale.group_id == PaymentGroup.id), LeftJoin(PaymentRenegotiation, PaymentRenegotiation.group_id == PaymentGroup.id), ] def get_status_str(self): return Payment.statuses[self.status] @property def renegotiation(self): if self.renegotiation_id: return self.store.get(PaymentRenegotiation, self.renegotiation_id) @classmethod def find_by_provider(cls, store, provider): if provider: return store.find(cls, CreditCardData.provider == provider) return store.find(cls)
def find_team_participations(people, teams=None): """Find the teams the given people participate in. :param people: The people for which to query team participation. :param teams: Optionally, limit the participation check to these teams. This method performs its work with at most a single database query. It first does similar checks to those performed by IPerson.in_team() and it may turn out that no database query is required at all. """ teams_to_query = [] people_teams = {} def add_team_to_result(person, team): teams = people_teams.get(person) if teams is None: teams = set() people_teams[person] = teams teams.add(team) # Check for the simple cases - self membership etc. if teams: for team in teams: if team is None: continue for person in people: if team.id == person.id: add_team_to_result(person, team) continue if not team.is_team: continue teams_to_query.append(team) # Avoid circular imports from lp.registry.model.person import Person # We are either checking for membership of any team or didn't eliminate # all the specific team participation checks above. if teams_to_query or not teams: Team = ClassAlias(Person, 'Team') person_ids = [person.id for person in people] conditions = [ TeamParticipation.personID == Person.id, TeamParticipation.teamID == Team.id, Person.id.is_in(person_ids) ] team_ids = [team.id for team in teams_to_query] if team_ids: conditions.append(Team.id.is_in(team_ids)) store = IStore(Person) rs = store.find( (Person, Team), *conditions) for (person, team) in rs: add_team_to_result(person, team) return people_teams
class InPaymentView(BasePaymentView): DraweeCompany = ClassAlias(Company, 'drawee_company') drawee = Person.name drawee_fancy_name = DraweeCompany.fancy_name person_id = Person.id renegotiated_id = PaymentGroup.renegotiation_id renegotiation_id = PaymentRenegotiation.id _count_tables = BasePaymentView._count_tables[:] _count_tables.append(LeftJoin(Person, PaymentGroup.payer_id == Person.id)) tables = BasePaymentView.tables[:] tables.extend([ LeftJoin(Person, PaymentGroup.payer_id == Person.id), LeftJoin(DraweeCompany, DraweeCompany.person_id == Person.id), LeftJoin(PaymentRenegotiation, PaymentRenegotiation.group_id == PaymentGroup.id), ]) clause = (Payment.payment_type == Payment.TYPE_IN) @property def renegotiation(self): if self.renegotiation_id: return self.store.get(PaymentRenegotiation, self.renegotiation_id) @property def renegotiated(self): if self.renegotiated_id: return self.store.get(PaymentRenegotiation, self.renegotiated_id) def get_parent(self): return self.sale or self.renegotiation @classmethod def has_late_payments(cls, store, person): """Checks if the provided person has unpaid payments that are overdue :param person: A :class:`person <stoqlib.domain.person.Person>` to check if has late payments :returns: True if the person has overdue payments. False otherwise """ tolerance = sysparam.get_int('TOLERANCE_FOR_LATE_PAYMENTS') query = And( cls.person_id == person.id, cls.status == Payment.STATUS_PENDING, cls.due_date < localtoday() - relativedelta(days=tolerance)) for late_payments in store.find(cls, query): sale = late_payments.sale # Exclude payments for external sales as they are handled by an # external entity (e.g. a payment gateway) meaning that they be # out of sync with the Stoq database. if not sale or not sale.is_external(): return True return False
class PurchaseReceivingView(Viewable): """Stores information about received orders. :cvar id: the id of the receiving order :cvar receival_date: the date when the receiving order was closed :cvar invoice_number: the number of the order that was received :cvar invoice_total: the total value of the received order :cvar purchase_identifier: the identifier of the received order :cvar branch_id: the id branch where the order was received :cvar purchase_responsible_name: the one who have confirmed the purchase :cvar responsible_name: the one who has received the order :cvar supplier_name: the supplier name """ _Responsible = ClassAlias(Person, "responsible") _Supplier = ClassAlias(Person, "supplier_person") _PurchaseUser = ClassAlias(LoginUser, "purchase_user") _PurchaseResponsible = ClassAlias(Person, "purchase_responsible") order = ReceivingOrder id = ReceivingOrder.id receival_date = ReceivingOrder.receival_date invoice_number = ReceivingOrder.invoice_number invoice_total = ReceivingOrder.invoice_total purchase_identifier = PurchaseOrder.identifier purchase_identifier_str = Cast(PurchaseOrder.identifier, 'text') branch_id = ReceivingOrder.branch_id purchase_responsible_name = _PurchaseResponsible.name responsible_name = _Responsible.name supplier_name = _Supplier.name tables = [ ReceivingOrder, LeftJoin(PurchaseOrder, ReceivingOrder.purchase_id == PurchaseOrder.id), LeftJoin(_PurchaseUser, PurchaseOrder.responsible_id == _PurchaseUser.id), LeftJoin(_PurchaseResponsible, _PurchaseUser.person_id == _PurchaseResponsible.id), LeftJoin(Supplier, ReceivingOrder.supplier_id == Supplier.id), LeftJoin(_Supplier, Supplier.person_id == _Supplier.id), LeftJoin(LoginUser, ReceivingOrder.responsible_id == LoginUser.id), LeftJoin(_Responsible, LoginUser.person_id == _Responsible.id), ]
def findBuildsByArchitecture(self, distroseries, archive): """Find associated builds, by architecture. Looks for `BinaryPackageBuild` records for this source package release, with publication records in the distroseries associated with `distroarchseries`. There should be at most one of these per architecture. :param distroarchseries: `DistroArchSeries` to look for. :return: A dict mapping architecture tags (in string form, e.g. 'i386') to `BinaryPackageBuild`s for that build. """ # Avoid circular imports. from lp.soyuz.model.binarypackagerelease import BinaryPackageRelease from lp.soyuz.model.distroarchseries import DistroArchSeries from lp.soyuz.model.publishing import BinaryPackagePublishingHistory BuildDAS = ClassAlias(DistroArchSeries, 'BuildDAS') PublishDAS = ClassAlias(DistroArchSeries, 'PublishDAS') query = Store.of(self).find( (BuildDAS.architecturetag, BinaryPackageBuild), BinaryPackageBuild.source_package_release == self, BinaryPackageRelease.buildID == BinaryPackageBuild.id, BuildDAS.id == BinaryPackageBuild.distro_arch_series_id, BinaryPackagePublishingHistory.binarypackagereleaseID == BinaryPackageRelease.id, BinaryPackagePublishingHistory.archiveID == archive.id, PublishDAS.id == BinaryPackagePublishingHistory.distroarchseriesID, PublishDAS.distroseriesID == distroseries.id, # Architecture-independent binary package releases are built # in the nominated arch-indep architecture but published in # all architectures. This condition makes sure we consider # only builds that have been published in their own # architecture. PublishDAS.architecturetag == BuildDAS.architecturetag) results = list(query.config(distinct=True)) mapped_results = dict(results) assert len(mapped_results) == len(results), ( "Found multiple build candidates per architecture: %s. " "This may mean that we have a serious problem in our DB model. " "Further investigation is required." % [(tag, build.id) for tag, build in results]) return mapped_results
def _naiveGetMergeProposals(self, statuses=None, target_repository=None, target_path=None, prerequisite_repository=None, prerequisite_path=None, merged_revision_ids=None, merge_proposal_ids=None, eager_load=False): Target = ClassAlias(GitRepository, "target") extra_tables = list( set(self._tables.values() + self._asymmetric_tables.values())) tables = [GitRepository] + extra_tables + [ Join( BranchMergeProposal, And( GitRepository.id == BranchMergeProposal.source_git_repositoryID, *(self._filter_expressions + self._asymmetric_filter_expressions))), Join(Target, Target.id == BranchMergeProposal.target_git_repositoryID), ] expressions = self._getRepositoryVisibilityExpression() expressions.extend(self._getRepositoryVisibilityExpression(Target)) if target_repository is not None: expressions.append( BranchMergeProposal.target_git_repository == target_repository) if target_path is not None: expressions.append( BranchMergeProposal.target_git_path == target_path) if prerequisite_repository is not None: expressions.append(BranchMergeProposal.prerequisite_git_repository == prerequisite_repository) if prerequisite_path is not None: expressions.append( BranchMergeProposal.prerequisite_git_path == prerequisite_path) if merged_revision_ids is not None: expressions.append( BranchMergeProposal.merged_revision_id.is_in( merged_revision_ids)) if merge_proposal_ids is not None: expressions.append( BranchMergeProposal.id.is_in(merge_proposal_ids)) 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 album_list(): ltype, size, offset = map(request.args.get, ['type', 'size', 'offset']) try: size = int(size) if size else 10 offset = int(offset) if offset else 0 except: return request.error_formatter(0, 'Invalid parameter format') query = store.find(Folder, Track.folder_id == Folder.id) if ltype == 'random': albums = [] count = query.count() if not count: return request.formatter({'albumList': {}}) for _ in xrange(size): x = random.choice(xrange(count)) albums.append(query[x]) return request.formatter({ 'albumList': { 'album': [a.as_subsonic_child(request.user) for a in albums] } }) elif ltype == 'newest': query = query.order_by(Desc(Folder.created)).config(distinct=True) elif ltype == 'highest': query = query.find(RatingFolder.rated_id == Folder.id).group_by( Folder.id).order_by(Desc(Avg(RatingFolder.rating))) elif ltype == 'frequent': query = query.group_by(Folder.id).order_by(Desc(Avg(Track.play_count))) elif ltype == 'recent': query = query.group_by(Folder.id).order_by(Desc(Max(Track.last_play))) elif ltype == 'starred': query = query.find(StarredFolder.starred_id == Folder.id, User.id == StarredFolder.user_id, User.name == request.username) elif ltype == 'alphabeticalByName': query = query.order_by(Folder.name).config(distinct=True) elif ltype == 'alphabeticalByArtist': parent = ClassAlias(Folder) query = query.find(Folder.parent_id == parent.id).order_by( parent.name, Folder.name).config(distinct=True) else: return request.error_formatter(0, 'Unknown search type') return request.formatter({ 'albumList': { 'album': [ f.as_subsonic_child(request.user) for f in query[offset:offset + size] ] } })
def _naiveGetMergeProposals(self, statuses=None, for_branches=None, target_branch=None, prerequisite_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 prerequisite_branch is not None: expressions.append( BranchMergeProposal.prerequisite_branch == prerequisite_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)
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)
class LoanView(Viewable): PersonBranch = ClassAlias(Person, 'person_branch') PersonResponsible = ClassAlias(Person, 'person_responsible') PersonClient = ClassAlias(Person, 'person_client') loan = Loan id = Loan.id identifier = Loan.identifier identifier_str = Cast(Loan.identifier, 'text') status = Loan.status open_date = Loan.open_date close_date = Loan.close_date expire_date = Loan.expire_date removed_by = Loan.removed_by client_id = Loan.client_id branch_id = Loan.branch_id branch_name = PersonBranch.name responsible_name = PersonResponsible.name client_name = PersonClient.name # Aggregates loaned = Sum(LoanItem.quantity) total = Sum(LoanItem.quantity * LoanItem.price) tables = [ Loan, Join(LoanItem, Loan.id == LoanItem.loan_id), LeftJoin(Branch, Loan.branch_id == Branch.id), LeftJoin(LoginUser, Loan.responsible_id == LoginUser.id), LeftJoin(Client, Loan.client_id == Client.id), LeftJoin(PersonBranch, Branch.person_id == PersonBranch.id), LeftJoin(PersonResponsible, LoginUser.person_id == PersonResponsible.id), LeftJoin(PersonClient, Client.person_id == PersonClient.id), ] group_by = [Loan, branch_name, responsible_name, client_name]
def new_search(): query, artist_count, artist_offset, album_count, album_offset, song_count, song_offset = map( request.values.get, [ 'query', 'artistCount', 'artistOffset', 'albumCount', 'albumOffset', 'songCount', 'songOffset' ]) try: artist_count = int(artist_count) if artist_count else 20 artist_offset = int(artist_offset) if artist_offset else 0 album_count = int(album_count) if album_count else 20 album_offset = int(album_offset) if album_offset else 0 song_count = int(song_count) if song_count else 20 song_offset = int(song_offset) if song_offset else 0 except: return request.error_formatter(0, 'Invalid parameter') if not query: return request.error_formatter(10, 'Missing query parameter') parent = ClassAlias(Folder) artist_query = store.find( parent, Folder.parent_id == parent.id, Track.folder_id == Folder.id, parent.name.contains_string(query)).config(distinct=True, offset=artist_offset, limit=artist_count) album_query = store.find(Folder, Track.folder_id == Folder.id, Folder.name.contains_string(query)).config( distinct=True, offset=album_offset, limit=album_count) song_query = store.find( Track, Track.title.contains_string(query))[song_offset:song_offset + song_count] return request.formatter({ 'searchResult2': { 'artist': [{ 'id': str(a.id), 'name': a.name } for a in artist_query], 'album': [f.as_subsonic_child(request.user) for f in album_query], 'song': [ t.as_subsonic_child(request.user, request.prefs) for t in song_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 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), ]
def getSubscribedAddresses(self, team_names): """See `IMailingListSet`.""" store = IStore(MailingList) Team = ClassAlias(Person) tables = ( EmailAddress, Join(Person, Person.id == EmailAddress.personID), Join(Account, Account.id == Person.accountID), Join(TeamParticipation, TeamParticipation.personID == Person.id), Join(MailingListSubscription, MailingListSubscription.personID == Person.id), Join(MailingList, MailingList.id == MailingListSubscription.mailing_listID), Join(Team, Team.id == MailingList.teamID), ) team_ids, list_ids = self._getTeamIdsAndMailingListIds(team_names) preferred = store.using(*tables).find( (EmailAddress.email, Person.display_name, Team.name), And( MailingListSubscription.mailing_listID.is_in(list_ids), TeamParticipation.teamID.is_in(team_ids), MailingList.teamID == TeamParticipation.teamID, MailingList.status != MailingListStatus.INACTIVE, Account.status == AccountStatus.ACTIVE, Or( And(MailingListSubscription.email_addressID == None, EmailAddress.status == EmailAddressStatus.PREFERRED), EmailAddress.id == MailingListSubscription.email_addressID), )) # Sort by team name. by_team = collections.defaultdict(set) for email, display_name, team_name in preferred: assert team_name in team_names, ( 'Unexpected team name in results: %s' % team_name) value = (display_name, email.lower()) by_team[team_name].add(value) # Turn the results into a mapping of lists. results = {} for team_name, address_set in by_team.items(): results[team_name] = list(address_set) return results
def iter_data(self): # If this is not a distroseries, then the query is much simpler. if not self.is_distroseries: potemplateset = getUtility(IPOTemplateSet) # The "shape" of the data returned by POTemplateSubset isn't quite # right so we have to run it through zip first. return zip( potemplateset.getSubset(productseries=self.productseries, distroseries=self.distroseries, ordered_by_names=True)) # Otherwise we have to do more work, primarily for the "sharing" # column. OtherTemplate = ClassAlias(POTemplate) join = (self.context.getTemplatesCollection().joinOuter( Packaging, And(Packaging.distroseries == self.context.id, Packaging.sourcepackagename == POTemplate.sourcepackagenameID) ).joinOuter( ProductSeries, ProductSeries.id == Packaging.productseriesID).joinOuter( Product, And( Product.id == ProductSeries.productID, Or(Product.translations_usage == ServiceUsage.LAUNCHPAD, Product.translations_usage == ServiceUsage.EXTERNAL)) ).joinOuter( OtherTemplate, And(OtherTemplate.productseriesID == ProductSeries.id, OtherTemplate.name == POTemplate.name)).joinInner( SourcePackageName, SourcePackageName.id == POTemplate.sourcepackagenameID) ) return join.select(POTemplate, Packaging, ProductSeries, Product, OtherTemplate, SourcePackageName).order_by(SourcePackageName.name, POTemplate.priority, POTemplate.name)
def build_search_query(self, text, extra_columns=()): """Query parameters shared by search() and count_search_matches(). :returns: Storm ResultSet object """ # These classes are imported in this method to prevent an import loop. from lp.registry.model.product import Product from lp.registry.model.projectgroup import ProjectGroup from lp.registry.model.distribution import Distribution OtherPillarName = ClassAlias(PillarName) origin = [ PillarName, LeftJoin(OtherPillarName, PillarName.alias_for == OtherPillarName.id), LeftJoin(Product, PillarName.product == Product.id), LeftJoin(ProjectGroup, PillarName.project == ProjectGroup.id), LeftJoin(Distribution, PillarName.distribution == Distribution.id), ] conditions = SQL(''' PillarName.active = TRUE AND (PillarName.name = lower(%(text)s) OR Product.fti @@ ftq(%(text)s) OR lower(Product.title) = lower(%(text)s) OR Project.fti @@ ftq(%(text)s) OR lower(Project.title) = lower(%(text)s) OR Distribution.fti @@ ftq(%(text)s) OR lower(Distribution.title) = lower(%(text)s) ) ''' % sqlvalues(text=ensure_unicode(text))) columns = [ PillarName, OtherPillarName, Product, ProjectGroup, Distribution ] for column in extra_columns: columns.append(column) return IStore(PillarName).using(*origin).find(tuple(columns), conditions)
class AccountTransactionView(Viewable): """AccountTransactionView provides a fast view of the transactions tied to a specific |account|. It's mainly used to show a ledger. """ Account_Dest = ClassAlias(Account, 'account_dest') Account_Source = ClassAlias(Account, 'account_source') transaction = AccountTransaction id = AccountTransaction.id code = AccountTransaction.code description = AccountTransaction.description value = AccountTransaction.value date = AccountTransaction.date operation_type = AccountTransaction.operation_type dest_account_id = Account_Dest.id dest_account_description = Account_Dest.description source_account_id = Account_Source.id source_account_description = Account_Source.description tables = [ AccountTransaction, LeftJoin(Account_Dest, AccountTransaction.account_id == Account_Dest.id), LeftJoin(Account_Source, AccountTransaction.source_account_id == Account_Source.id), ] @classmethod def get_for_account(cls, account, store): """Get all transactions for this |account|, see Account.transaction""" return store.find( cls, Or(account.id == AccountTransaction.account_id, account.id == AccountTransaction.source_account_id)) def get_account_description(self, account): """Get description of the other |account|, eg. the one which is transfered to/from. """ if self.source_account_id == account.id: return self.dest_account_description elif self.dest_account_id == account.id: return self.source_account_description else: raise AssertionError def get_value(self, account): """ Gets the transaction value according to an |account|. If this |account| is the source, the value returned will be negative. Representing a outgoing transaction. """ # A transaction that was not adjusted, will have the source equals # to destination account. So get the value based on operation type. if self.source_account_id == self.dest_account_id: return self.get_value_by_type() elif self.source_account_id == account.id: return -self.value else: return self.value def get_value_by_type(self): """ Returns the transaction value, based on operation type. """ if self.operation_type == AccountTransaction.TYPE_IN: return self.value else: return -self.value
class PurchaseOrderView(Viewable): """General information about purchase orders :cvar id: the id of purchase_order table :cvar status: the purchase order status :cvar open_date: the date when the order was started :cvar quote_deadline: the date when the quotation expires :cvar expected_receival_date: expected date to receive products :cvar expected_pay_date: expected date to pay the products :cvar receival_date: the date when the products were received :cvar confirm_date: the date when the order was confirmed :cvar salesperson_name: the name of supplier's salesperson :cvar expected_freight: the expected freight value :cvar surcharge_value: the surcharge value for the order total :cvar discount_value: the discount_value for the order total :cvar supplier_name: the supplier name :cvar transporter_name: the transporter name :cvar branch_name: the branch company name :cvar ordered_quantity: the total quantity ordered :cvar received_quantity: the total quantity received :cvar subtotal: the order subtotal (sum of product values) :cvar total: subtotal - discount_value + surcharge_value """ Person_Supplier = ClassAlias(Person, 'person_supplier') Person_Transporter = ClassAlias(Person, 'person_transporter') Person_Branch = ClassAlias(Person, 'person_branch') Person_Responsible = ClassAlias(Person, 'person_responsible') purchase = PurchaseOrder branch = Branch id = PurchaseOrder.id identifier = PurchaseOrder.identifier identifier_str = Cast(PurchaseOrder.identifier, 'text') status = PurchaseOrder.status open_date = PurchaseOrder.open_date quote_deadline = PurchaseOrder.quote_deadline expected_receival_date = PurchaseOrder.expected_receival_date expected_pay_date = PurchaseOrder.expected_pay_date confirm_date = PurchaseOrder.confirm_date salesperson_name = NullIf(PurchaseOrder.salesperson_name, u'') expected_freight = PurchaseOrder.expected_freight surcharge_value = PurchaseOrder.surcharge_value discount_value = PurchaseOrder.discount_value branch_id = Branch.id supplier_id = Supplier.id supplier_name = Person_Supplier.name transporter_name = Coalesce(Person_Transporter.name, u'') branch_name = Coalesce(NullIf(Company.fancy_name, u''), Person_Branch.name) responsible_name = Person_Responsible.name receival_date = Field('_receiving_order', 'receival_date') ordered_quantity = Field('_purchase_item', 'ordered_quantity') received_quantity = Field('_purchase_item', 'received_quantity') subtotal = Field('_purchase_item', 'subtotal') total = Field('_purchase_item', 'subtotal') - \ PurchaseOrder.discount_value + PurchaseOrder.surcharge_value tables = [ PurchaseOrder, Join(PurchaseItemSummary, Field('_purchase_item', 'order_id') == PurchaseOrder.id), LeftJoin(PurchaseReceivingSummary, Field('_receiving_order', 'purchase_id') == PurchaseOrder.id), LeftJoin(Supplier, PurchaseOrder.supplier_id == Supplier.id), LeftJoin(Transporter, PurchaseOrder.transporter_id == Transporter.id), LeftJoin(Branch, PurchaseOrder.branch_id == Branch.id), LeftJoin(LoginUser, PurchaseOrder.responsible_id == LoginUser.id), LeftJoin(Person_Supplier, Supplier.person_id == Person_Supplier.id), LeftJoin(Person_Transporter, Transporter.person_id == Person_Transporter.id), LeftJoin(Person_Branch, Branch.person_id == Person_Branch.id), LeftJoin(Company, Company.person_id == Person_Branch.id), LeftJoin(Person_Responsible, LoginUser.person_id == Person_Responsible.id), ] @classmethod def post_search_callback(cls, sresults): select = sresults.get_select_expr(Count(1), Sum(cls.total)) return ('count', 'sum'), select # # Public API # def get_open_date_as_string(self): return self.open_date.strftime("%x") @property def status_str(self): return PurchaseOrder.translate_status(self.status) @classmethod def find_confirmed(cls, store, due_date=None): query = cls.status == PurchaseOrder.ORDER_CONFIRMED if due_date: if isinstance(due_date, tuple): date_query = And(Date(cls.expected_receival_date) >= due_date[0], Date(cls.expected_receival_date) <= due_date[1]) else: date_query = Date(cls.expected_receival_date) == due_date query = And(query, date_query) return store.find(cls, query)
def old_search(): artist, album, title, anyf, count, offset, newer_than = map( request.values.get, ['artist', 'album', 'title', 'any', 'count', 'offset', 'newerThan']) try: count = int(count) if count else 20 offset = int(offset) if offset else 0 newer_than = int(newer_than) / 1000 if newer_than else 0 except: return request.error_formatter(0, 'Invalid parameter') min_date = datetime.fromtimestamp(newer_than) if artist: parent = ClassAlias(Folder) query = store.find(parent, Folder.parent_id == parent.id, Track.folder_id == Folder.id, parent.name.contains_string(artist), parent.created > min_date).config(distinct=True) elif album: query = store.find(Folder, Track.folder_id == Folder.id, Folder.name.contains_string(album), Folder.created > min_date).config(distinct=True) elif title: query = store.find(Track, Track.title.contains_string(title), Track.created > min_date) elif anyf: folders = store.find(Folder, Folder.name.contains_string(anyf), Folder.created > min_date) tracks = store.find(Track, Track.title.contains_string(anyf), Track.created > min_date) res = list(folders[offset:offset + count]) if offset + count > folders.count(): toff = max(0, offset - folders.count()) tend = offset + count - folders.count() res += list(tracks[toff:tend]) return request.formatter({ 'searchResult': { 'totalHits': folders.count() + tracks.count(), 'offset': offset, 'match': [ r.as_subsonic_child(request.user) if isinstance(r, Folder) else r.as_subsonic_child(request.user, request.prefs) for r in res ] } }) else: return request.error_formatter(10, 'Missing search parameter') return request.formatter({ 'searchResult': { 'totalHits': query.count(), 'offset': offset, 'match': [ r.as_subsonic_child(request.user) if isinstance(r, Folder) else r.as_subsonic_child(request.user, request.prefs) for r in query[offset:offset + count] ] } })
def _prepare_result_set(self): store = self._cls._get_store() args = [] if self._clause: args.append(self._clause) for key, value in self._by.items(): args.append(getattr(self._cls, key) == value) tables = [] if self._clauseTables is not None: tables.extend(self._clauseTables) if not (self._prejoins or self._prejoinClauseTables): find_spec = self._cls else: find_spec = [self._cls] if self._prejoins: already_prejoined = {} last_prejoin = 0 join = self._cls for prejoin_path in self._prejoins: local_cls = self._cls path = () for prejoin_attr in prejoin_path.split("."): path += (prejoin_attr, ) # If we've already prejoined this column, we're done. if path in already_prejoined: local_cls = already_prejoined[path] continue # Otherwise, join the table relation = getattr(local_cls, prejoin_attr)._relation last_prejoin += 1 remote_cls = ClassAlias(relation.remote_cls, '_prejoin%d' % last_prejoin) join_expr = join_aliased_relation( local_cls, remote_cls, relation) join = LeftJoin(join, remote_cls, join_expr) find_spec.append(remote_cls) already_prejoined[path] = remote_cls local_cls = remote_cls if join is not self._cls: tables.append(join) if self._prejoinClauseTables: property_registry = self._cls._storm_property_registry for table in self._prejoinClauseTables: cls = property_registry.get("<table %s>" % table).cls find_spec.append(cls) find_spec = tuple(find_spec) if tables: # If we are adding extra tables, make sure the main table # is included. tables.insert(0, self._cls.__storm_table__) # Inject an AutoTables expression with a dummy true value to # be ANDed in the WHERE clause, so that we can introduce our # tables into the dynamic table handling of Storm without # disrupting anything else. args.append(AutoTables(SQL("1=1"), tables)) if self._selectAlso is not None: if type(find_spec) is not tuple: find_spec = (find_spec, SQL(self._selectAlso)) else: find_spec += (SQL(self._selectAlso), ) return store.find(find_spec, *args)