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))
