class LoanItemView(Viewable): id = LoanItem.id quantity = LoanItem.quantity sale_quantity = LoanItem.sale_quantity return_quantity = LoanItem.return_quantity price = LoanItem.price total = LoanItem.quantity * LoanItem.price loan_identifier = Loan.identifier loan_status = Loan.status opened = Loan.open_date closed = Loan.close_date sellable_id = Sellable.id code = Sellable.code description = Sellable.description category_description = SellableCategory.description unit_description = SellableUnit.description tables = [ LoanItem, LeftJoin(Loan, LoanItem.loan_id == Loan.id), LeftJoin(Sellable, LoanItem.sellable_id == Sellable.id), LeftJoin(SellableUnit, Sellable.unit_id == SellableUnit.id), LeftJoin(SellableCategory, SellableCategory.id == Sellable.category_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 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 _BillandCheckPaymentView(Viewable): """A base view for check and bill payments.""" payment = Payment id = Payment.id identifier = Payment.identifier due_date = Payment.due_date paid_date = Payment.paid_date status = Payment.status value = Payment.value payment_number = Payment.payment_number method_name = PaymentMethod.method_name bank_number = BankAccount.bank_number branch = BankAccount.bank_branch account = BankAccount.bank_account tables = [ Payment, LeftJoin(CheckData, Payment.id == CheckData.payment_id), Join(PaymentMethod, Payment.method_id == PaymentMethod.id), LeftJoin(BankAccount, BankAccount.id == CheckData.bank_account_id), ] clause = Or(PaymentMethod.method_name == u'bill', PaymentMethod.method_name == u'check') def get_status_str(self): return Payment.statuses[self.status] @property def method_description(self): return get_payment_operation(self.method_name).description
class ServiceView(Viewable): """Stores information about services :attribute id: the id of the asellable table :attribute barcode: the sellable barcode :attribute status: the sellable status :attribute cost: the sellable cost :attribute price: the sellable price :attribute description: the sellable description :attribute unit: the unit in case the sellable is not a product :attribute service_id: the id of the service table """ sellable = Sellable id = Sellable.id code = Sellable.code barcode = Sellable.barcode status = Sellable.status cost = Sellable.cost price = Sellable.base_price description = Sellable.description category_description = SellableCategory.description unit = SellableUnit.description service_id = Service.id tables = [ Sellable, Join(Service, Service.id == Sellable.id), LeftJoin(SellableUnit, Sellable.unit_id == SellableUnit.id), LeftJoin(SellableCategory, SellableCategory.id == Sellable.category_id), ] def get_unit(self): return self.unit or u""
def get_sellables_for_inventory(cls, store, branch, extra_query=None): """Returns a generator with the necessary data about the stock to open an Inventory :param store: The store to fetch data from :param branch: The branch that is being inventoried :param query: A query that should be used to restrict the storables for the inventory. This can filter based on categories or other aspects of the product. :returns: a generator of the following objects: (Sellable, Product, Storable, StorableBatch, ProductStockItem) """ # XXX: If we should want all storables to be inclued in the inventory, even if if # never had a ProductStockItem before, than we should inclue this query in the # LeftJoin with ProductStockItem below query = ProductStockItem.branch_id == branch.id if extra_query: query = And(query, extra_query) tables = [ Sellable, Join(Product, Product.id == Sellable.id), Join(Storable, Storable.id == Product.id), LeftJoin(StorableBatch, StorableBatch.storable_id == Storable.id), LeftJoin( ProductStockItem, And( ProductStockItem.storable_id == Storable.id, Or(ProductStockItem.batch_id == StorableBatch.id, Eq(ProductStockItem.batch_id, None)))), ] return store.using(*tables).find( (Sellable, Product, Storable, StorableBatch, ProductStockItem), query)
def fetchProjectsForDisplay(self): """See `ITranslationGroup`.""" # Avoid circular imports. from lp.registry.model.product import ( Product, ProductWithLicenses, ) using = [ Product, LeftJoin(LibraryFileAlias, LibraryFileAlias.id == Product.iconID), LeftJoin( LibraryFileContent, LibraryFileContent.id == LibraryFileAlias.contentID), ] columns = ( Product, ProductWithLicenses.composeLicensesColumn(), LibraryFileAlias, LibraryFileContent, ) product_data = ISlaveStore(Product).using(*using).find( columns, Product.translationgroupID == self.id, Product.active == True) product_data = product_data.order_by(Product.displayname) return [ ProductWithLicenses(product, tuple(licenses)) for product, licenses, icon_alias, icon_content in product_data]
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), ]
class SaleItemsView(Viewable): """Show information about sold items and about the corresponding sale. This is slightlig difrent than SoldItemView that groups sold items from diferent sales. """ id = SaleItem.id sellable_id = Sellable.id code = Sellable.code description = Sellable.description sale_id = SaleItem.sale_id sale_identifier = Sale.identifier sale_date = Sale.open_date client_name = Person.name quantity = SaleItem.quantity unit_description = SellableUnit.description tables = [ SaleItem, LeftJoin(Sellable, Sellable.id == SaleItem.sellable_id), LeftJoin(Sale, SaleItem.sale_id == Sale.id), LeftJoin(SellableUnit, Sellable.unit_id == SellableUnit.id), LeftJoin(Client, Sale.client_id == Client.id), LeftJoin(Person, Client.person_id == Person.id), ] clause = Or(Sale.status == Sale.STATUS_CONFIRMED, Sale.status == Sale.STATUS_PAID, Sale.status == Sale.STATUS_RENEGOTIATED, Sale.status == Sale.STATUS_ORDERED)
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 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 QuotationView(Viewable): """Stores information about the quote group and its quotes. """ group = QuoteGroup quotation = Quotation purchase = PurchaseOrder id = Quotation.id purchase_id = Quotation.purchase_id group_id = Quotation.group_id identifier = Quotation.identifier identifier_str = Cast(Quotation.identifier, 'text') group_identifier = QuoteGroup.identifier open_date = PurchaseOrder.open_date deadline = PurchaseOrder.quote_deadline supplier_name = Person.name tables = [ Quotation, Join(QuoteGroup, QuoteGroup.id == Quotation.group_id), LeftJoin(PurchaseOrder, PurchaseOrder.id == Quotation.purchase_id), LeftJoin(Supplier, Supplier.id == PurchaseOrder.supplier_id), LeftJoin(Person, Person.id == Supplier.person_id), ]
def fetchTranslatorData(self): """See `ITranslationGroup`.""" # Fetch Translator, Language, and Person; but also prefetch the # icon information. using = [ Translator, Language, Person, LeftJoin(LibraryFileAlias, LibraryFileAlias.id == Person.iconID), LeftJoin( LibraryFileContent, LibraryFileContent.id == LibraryFileAlias.contentID), ] tables = ( Translator, Language, Person, LibraryFileAlias, LibraryFileContent, ) translator_data = Store.of(self).using(*using).find( tables, Translator.translationgroup == self, Language.id == Translator.languageID, Person.id == Translator.translatorID) translator_data = translator_data.order_by(Language.englishname) mapper = lambda row: row[slice(0, 3)] return DecoratedResultSet(translator_data, mapper)
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)
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 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
class CostCenterEntryStockView(Viewable): """A viewable with information about cost center entries related to stock transactions. """ stock_transaction = StockTransactionHistory # StockTransactionHistory has an indirect reference to only one of this # (sale_item or stock_decrease_item), but here we are speculatively quering # both to cache the results, avoiding extra queries when getting the # description of the transaction _sale_item = SaleItem _stock_decrease_item = StockDecreaseItem _sale = Sale _stock_decrease = StockDecrease id = CostCenterEntry.id cost_center_id = CostCenterEntry.cost_center_id date = StockTransactionHistory.date stock_cost = StockTransactionHistory.stock_cost quantity = StockTransactionHistory.quantity responsible_name = Person.name sellable_id = Sellable.id code = Sellable.code product_description = Sellable.description tables = [ CostCenterEntry, Join( StockTransactionHistory, CostCenterEntry.stock_transaction_id == StockTransactionHistory.id), Join(LoginUser, StockTransactionHistory.responsible_id == LoginUser.id), Join(Person, LoginUser.person_id == Person.id), Join( ProductStockItem, StockTransactionHistory.product_stock_item_id == ProductStockItem.id), Join(Storable, ProductStockItem.storable_id == Storable.id), Join(Product, Storable.product_id == Product.id), Join(Sellable, Product.sellable_id == Sellable.id), # possible sale item and stock decrease item LeftJoin(SaleItem, SaleItem.id == StockTransactionHistory.object_id), LeftJoin(Sale, SaleItem.sale_id == Sale.id), LeftJoin(StockDecreaseItem, StockDecreaseItem.id == StockTransactionHistory.object_id), LeftJoin(StockDecrease, StockDecreaseItem.stock_decrease_id == StockDecrease.id), ] @property def total(self): return currency(abs(self.stock_cost * self.quantity))
class PurchasedItemAndStockView(Viewable): """Stores information about the purchase items that will be delivered and also the quantity that is already in stock. This view is used to query which products are going to be delivered and if they are on time or not. :cvar id: the id of the purchased item :cvar product_id: the id of the product :cvar purchased: the quantity purchased :cvar received: the quantity already received :cvar stocked: the quantity in stock :cvar expected_receival_date: the date that the item might be deliverd :cvar purchase_date: the date when the item was purchased :cvar branch: the branch where the purchase was done """ purchase_item = PurchaseItem id = PurchaseItem.id purchased = PurchaseItem.quantity received = PurchaseItem.quantity_received expected_receival_date = PurchaseItem.expected_receival_date order_identifier = PurchaseOrder.identifier order_identifier_str = Cast(PurchaseOrder.identifier, 'text') purchased_date = PurchaseOrder.open_date branch = PurchaseOrder.branch_id code = Sellable.code description = Sellable.description product_id = Product.id # Aggregate stocked = Sum(ProductStockItem.quantity) tables = [ PurchaseItem, LeftJoin(PurchaseOrder, PurchaseItem.order_id == PurchaseOrder.id), LeftJoin(Sellable, Sellable.id == PurchaseItem.sellable_id), LeftJoin(Product, Product.sellable_id == PurchaseItem.sellable_id), LeftJoin(Storable, Storable.product_id == Product.id), LeftJoin(ProductStockItem, ProductStockItem.storable_id == Storable.id), ] clause = And( PurchaseOrder.status == PurchaseOrder.ORDER_CONFIRMED, PurchaseOrder.branch_id == ProductStockItem.branch_id, PurchaseItem.quantity > PurchaseItem.quantity_received, ) group_by = [ PurchaseItem, order_identifier, purchased_date, branch, code, description, product_id ]
class InPaymentView(BasePaymentView): drawee = Person.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(PaymentRenegotiation, PaymentRenegotiation.group_id == PaymentGroup.id), ]) clause = (Payment.payment_type == Payment.TYPE_IN) @property def renegotiation(self): if self.renegotiation_id: return PaymentRenegotiation.get(self.renegotiation_id, store=self.store) @property def renegotiated(self): if self.renegotiated_id: return PaymentRenegotiation.get(self.renegotiated_id, store=self.store) 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(store).TOLERANCE_FOR_LATE_PAYMENTS query = And(cls.person_id == person.id, cls.status == Payment.STATUS_PENDING, cls.due_date < localtoday().date() - relativedelta(days=tolerance)) late_payments = store.find(cls, query) if late_payments.any(): return True return False
def _getTranslator(self, translationgroup, language, store): """Retrieve one (TranslationGroup, Translator, Person) tuple.""" translator_join = LeftJoin( Translator, And(Translator.translationgroupID == TranslationGroup.id, Translator.languageID == language.id)) person_join = LeftJoin(Person, Person.id == Translator.translatorID) source = store.using(TranslationGroup, translator_join, person_join) return source.find((TranslationGroup, Translator, Person), TranslationGroup.id == translationgroup.id).one()
class PurchaseItemView(Viewable): """This is a view which you can use to fetch purchase items within a specific purchase. It's used by the PurchaseDetails dialog to display all the purchase items within a purchase :param id: id of the purchase item :param purchase_id: id of the purchase order the item belongs to :param sellable: sellable of the item :param cost: cost of the item :param quantity: quantity ordered :param quantity_received: quantity received :param total: total value of the items purchased :param total_received: total value of the items received :param description: description of the sellable :param unit: unit as a string or None if the product has no unit """ purchase_item = PurchaseItem id = PurchaseItem.id cost = PurchaseItem.cost quantity = PurchaseItem.quantity quantity_received = PurchaseItem.quantity_received quantity_sold = PurchaseItem.quantity_sold quantity_returned = PurchaseItem.quantity_returned total = PurchaseItem.cost * PurchaseItem.quantity total_received = PurchaseItem.cost * PurchaseItem.quantity_received total_sold = PurchaseItem.cost * PurchaseItem.quantity_sold current_stock = Sum(ProductStockItem.quantity) purchase_id = PurchaseOrder.id sellable_id = Sellable.id code = Sellable.code description = Sellable.description unit = SellableUnit.description tables = [ PurchaseItem, Join(PurchaseOrder, PurchaseOrder.id == PurchaseItem.order_id), Join(Sellable, Sellable.id == PurchaseItem.sellable_id), LeftJoin(SellableUnit, SellableUnit.id == Sellable.unit_id), LeftJoin( ProductStockItem, And(ProductStockItem.storable_id == PurchaseItem.sellable_id, ProductStockItem.branch_id == PurchaseOrder.branch_id)) ] group_by = [ PurchaseItem.id, Sellable.id, PurchaseOrder.id, SellableUnit.id ] @classmethod def find_by_purchase(cls, store, purchase): return store.find(cls, purchase_id=purchase.id)
class SoldItemView(Viewable): """Stores information about all sale items, including the average cost of the sold items. """ id = Sellable.id code = Sellable.code description = Sellable.description category = SellableCategory.description # Aggregate quantity = Sum(SaleItem.quantity) total_cost = Sum(SaleItem.quantity * SaleItem.average_cost) tables = [ Sellable, LeftJoin(SaleItem, Sellable.id == SaleItem.sellable_id), LeftJoin(Sale, SaleItem.sale_id == Sale.id), LeftJoin(SellableCategory, Sellable.category_id == SellableCategory.id), ] clause = Or(Sale.status == Sale.STATUS_CONFIRMED, Sale.status == Sale.STATUS_PAID, Sale.status == Sale.STATUS_ORDERED) group_by = [id, code, description, category, Sale.status] @classmethod def find_by_branch_date(cls, store, branch, date): queries = [] if branch: queries.append(Sale.branch == branch) if date: if isinstance(date, tuple): date_query = And( Date(Sale.confirm_date) >= date[0], Date(Sale.confirm_date) <= date[1]) else: date_query = Date(Sale.confirm_date) == date queries.append(date_query) if queries: return store.find(cls, And(*queries)) return store.find(cls) @property def average_cost(self): if self.quantity: return self.total_cost / self.quantity return 0
class ClientView(Viewable): person = Person client = Client person_name = Person.name total_sales = Sum(Sale.total_amount) tables = [ Client, LeftJoin(Person, Person.id == Client.person_id), LeftJoin(Sale, Sale.client_id == Client.id), ] group_by = [Person, Client, person_name]
class AccountView(Viewable): account = Account id = Account.id parent_id = Account.parent_id account_type = Account.account_type dest_account_id = Account.parent_id description = Account.description code = Account.code source_value = Field('source_sum', 'value') dest_value = Field('dest_sum', 'value') tables = [ Account, LeftJoin(Alias(_SourceSum, 'source_sum'), Field('source_sum', 'source_account_id') == Account.id), LeftJoin(Alias(_DestSum, 'dest_sum'), Field('dest_sum', 'account_id') == Account.id), ] @property def parent_account(self): """Get the parent account for this view""" return self.store.get(Account, self.parent_id) def matches(self, account_id): """Returns true if the account_id matches this account or its parent""" if self.id == account_id: return True if self.parent_id and self.parent_id == account_id: return True return False def get_combined_value(self): """Returns the combined value of incoming and outgoing transactions""" if not self.dest_value and not self.source_value: return 0 elif not self.dest_value: return -self.source_value elif not self.source_value: return self.dest_value else: return self.dest_value - self.source_value def __repr__(self): return '<AccountView %s>' % (self.description, )
class ServicesSearchExtention(SearchExtension): spec_attributes = dict( manufacturer_name=ProductManufacturer.name ) spec_joins = [ LeftJoin(Product, Product.id == Sellable.id), LeftJoin(ProductManufacturer, Product.manufacturer_id == ProductManufacturer.id) ] def get_columns(self): return [ SearchColumn('manufacturer_name', title=_('Manufacturer'), data_type=str, visible=False) ]
class OutPaymentView(BasePaymentView): supplier_name = Person.name _count_tables = BasePaymentView._count_tables[:] _count_tables.append( LeftJoin(Person, BasePaymentView.PaymentGroup_Sale.recipient_id == Person.id)) tables = BasePaymentView.tables[:] tables.extend([ LeftJoin(Person, Person.id == BasePaymentView.PaymentGroup_Sale.recipient_id), ]) clause = (Payment.payment_type == Payment.TYPE_OUT)
class SellableView(Viewable): sellable = Sellable product = Product storable = Storable service = Service id = Sellable.id tables = [ Sellable, LeftJoin(Service, Sellable.id == Service.id), LeftJoin(Product, Sellable.id == Product.id), LeftJoin(Storable, Sellable.id == Storable.id), LeftJoin(SellableCategory, SellableCategory.id == Sellable.category_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 getBuildsForBuilder(self, builder_id, status=None, user=None): """See `IBuildFarmJobSet`.""" # Imported here to avoid circular imports. from lp.soyuz.model.archive import (Archive, get_archive_privacy_filter) clauses = [ BuildFarmJob.builder == builder_id, Or(Archive.id == None, get_archive_privacy_filter(user)) ] if status is not None: clauses.append(BuildFarmJob.status == status) # We need to ensure that we don't include any private builds. # Currently only package builds can be private (via their # related archive), but not all build farm jobs will have a # related package build - hence the left join. origin = [ BuildFarmJob, LeftJoin(Archive, Archive.id == BuildFarmJob.archive_id), ] return IStore(BuildFarmJob).using(*origin).find( BuildFarmJob, *clauses).order_by(Desc(BuildFarmJob.date_finished), BuildFarmJob.id)
def getSpecifications(self, user): """See `IMilestoneData`""" from lp.registry.model.person import Person origin = [Specification] product_origin, clauses = get_specification_active_product_filter(self) origin.extend(product_origin) clauses.extend(get_specification_privacy_filter(user)) origin.append(LeftJoin(Person, Specification._assigneeID == Person.id)) milestones = self._milestone_ids_expr(user) results = Store.of(self.target).using(*origin).find( (Specification, Person), Specification.id.is_in( Union(Select( Specification.id, tables=[Specification], where=(Specification.milestoneID.is_in(milestones))), Select(SpecificationWorkItem.specification_id, tables=[SpecificationWorkItem], where=And( SpecificationWorkItem.milestone_id.is_in( milestones), SpecificationWorkItem.deleted == False)), all=True)), *clauses) ordered_results = results.order_by(Desc(Specification.priority), Specification.definition_status, Specification.implementation_status, Specification.title) ordered_results.config(distinct=True) return DecoratedResultSet(ordered_results, itemgetter(0))