def add_criterion(self, name):
count_name = "{}_count".format(name)
self.event_query.columns.append(
Sum(If(C.event_id.is_in(Param(name)), C.event_count,
0)).as_(count_name), )
self.event_where.append(Param(name))
self.event_having.exprs += (Column(count_name), )
self.info_query.columns.append(
Sum(Column(count_name)).as_(count_name))
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 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
]
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),
]
def test_over(self):
series = GenerateSeries(0, 10, 1)
normal_values = Field('generate_series', 'generate_series')
over_nothing = Over(Sum(normal_values))
over_order_values = Over(Sum(normal_values), [], [normal_values])
over_partition_values = Over(Sum(normal_values), [normal_values])
data = list(
self.store.using(series).find(
(normal_values, over_nothing, over_order_values,
over_partition_values)))
self.assertEquals(len(data), 11)
self.assertEquals(data,
[(i, 55, sum(range(i + 1)), i) for i in range(11)])
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 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 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]
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]
def recalculateCounts(self):
"""See `ITranslatedLanguage`."""
templates = self.parent.getCurrentTemplatesCollection()
pofiles = templates.joinOuterPOFile(self.language)
total_count_results = list(
pofiles.select(Coalesce(Sum(POTemplate.messagecount), 0),
Coalesce(Sum(POFile.currentcount), 0),
Coalesce(Sum(POFile.updatescount), 0),
Coalesce(Sum(POFile.rosettacount), 0),
Coalesce(Sum(POFile.unreviewed_count), 0),
Max(POFile.date_changed)))
total, imported, changed, rosetta, unreviewed, date_changed = (
total_count_results[0])
translated = imported + rosetta
new = rosetta - changed
self.setCounts(total, translated, new, changed, unreviewed)
# We have to add a timezone to the otherwise naive-datetime object
# (because we've gotten it using Max() aggregate function).
if date_changed is not None:
date_changed = date_changed.replace(tzinfo=pytz.UTC)
self.last_changed_date = date_changed
def getBuildQueueSizes(self):
"""See `IBuilderSet`."""
results = ISlaveStore(BuildQueue).find(
(Count(), Sum(BuildQueue.estimated_duration), Processor,
Coalesce(BuildQueue.virtualized, True)),
Processor.id == BuildQueue.processorID,
BuildQueue.status == BuildQueueStatus.WAITING).group_by(
Processor, Coalesce(BuildQueue.virtualized, True))
result_dict = {'virt': {}, 'nonvirt': {}}
for size, duration, processor, virtualized in results:
if virtualized is False:
virt_str = 'nonvirt'
else:
virt_str = 'virt'
result_dict[virt_str][processor.name] = (size, duration)
return result_dict
class ProductQuantityView(Viewable):
"""Stores information about products solded and received.
:cvar id: the id of the sellable_id of products_quantity table
:cvar description: the product description
:cvar branch_id: the id of branch table
:cvar quantity_sold: the quantity solded of product
:cvar quantity_transfered: the quantity transfered of product
:cvar quantity_received: the quantity received of product
:cvar branch: the id of the branch_id of producst_quantity table
:cvar date_sale: the date of product's sale
:cvar date_received: the date of product's received
"""
id = ProductHistory.sellable_id
branch = ProductHistory.branch_id
sold_date = ProductHistory.sold_date
received_date = ProductHistory.received_date
production_date = ProductHistory.production_date
decreased_date = ProductHistory.decreased_date
code = Sellable.code
description = Sellable.description
# Aggregates
quantity_sold = Sum(ProductHistory.quantity_sold)
quantity_received = Sum(ProductHistory.quantity_received)
quantity_transfered = Sum(ProductHistory.quantity_transfered)
quantity_produced = Sum(ProductHistory.quantity_produced)
quantity_consumed = Sum(ProductHistory.quantity_consumed)
quantity_lost = Sum(ProductHistory.quantity_lost)
quantity_decreased = Sum(ProductHistory.quantity_decreased)
tables = [
ProductHistory,
Join(Sellable, ProductHistory.sellable_id == Sellable.id),
]
# This are columns that are not supposed to be queried, but should still be
# able to be filtered
hidden_columns = [
'sold_date', 'received_date', 'production_date', 'decreased_date'
]
group_by = [id, branch, code, description]
class SoldItemsByBranchView(SoldItemView):
"""Store information about the all sold items by branch.
"""
branch_name = Person.name
# Aggregates
total = Sum(SaleItem.quantity * SaleItem.price)
tables = SoldItemView.tables[:]
tables.extend([
LeftJoin(Branch, Branch.id == Sale.branch_id),
LeftJoin(Person, Branch.person_id == Person.id)
])
clause = Or(SoldItemView.clause, Sale.status == Sale.STATUS_RENEGOTIATED)
group_by = SoldItemView.group_by[:]
group_by.append(branch_name)
class ClientView(Viewable):
person = Person
client = Client
id = Client.id
person_name = Person.name
total_sales = Sum(Sale.total_amount)
# This will be retrieved as None (because clients are not suppliers in the
# tests) and even though the column is defined as "allow_none=False"
# it should not fail in the selects bellow.
supplier_status = Supplier.status
tables = [
Client,
LeftJoin(Person, Person.id == Client.person_id),
LeftJoin(Supplier, Person.id == Supplier.person_id),
LeftJoin(Sale, Sale.client_id == Client.id),
]
group_by = [Person, Client, person_name, supplier_status]
def collect_link_statistics(store):
one_week = datetime.date.today() - datetime.timedelta(days=7)
query = Date(Sale.confirm_date) >= one_week
# Profit Margin
item_cost = Alias(
Select(columns=[
SaleItem.sale_id,
Alias(Sum(SaleItem.quantity * SaleItem.average_cost), 'cost')
],
tables=SaleItem,
group_by=[SaleItem.sale_id]), 'item_cost')
column = ((Sum(Sale.total_amount) / Sum(Field('item_cost', 'cost')) - 1) *
100)
tables = [Sale, Join(item_cost, Field('item_cost', 'sale_id') == Sale.id)]
profit_margin = store.using(*tables).find(column, query).one()
# Sale chart
columns = (DateTrunc(u'day', Sale.confirm_date), Sum(Sale.total_amount))
sale_data = store.find(columns, query)
sale_data.group_by(columns[0])
# Best selling
tables = [
Sellable,
Join(SaleItem, SaleItem.sellable_id == Sellable.id),
Join(Sale, SaleItem.sale_id == Sale.id)
]
columns = (Sellable.description, Sum(SaleItem.quantity),
Sum(SaleItem.quantity * SaleItem.price))
product_data = store.using(*tables).find(columns,
query).order_by(-columns[2])
product_data.group_by(Sellable.description)
data = dict(sales_total=store.find(Sale, query).sum(Sale.total_amount),
sales_count=store.find(Sale, query).count(),
clients_served=store.find(Sale, query).count(Sale.client_id,
distinct=True),
profit_margin=format(float(profit_margin), '.2f'),
best_selling=list(product_data),
sales_chart=list(sale_data),
timeline=_collect_timeline(store),
timestamp=datetime.datetime.now())
return json.dumps(data, default=default)
class ProductBrandStockView(Viewable):
# Alias of Branch to Person table
id = Product.brand
brand = Coalesce(Product.brand, u'')
quantity = Sum(ProductStockItem.quantity)
tables = [
Product,
LeftJoin(Storable, Storable.product_id == Product.id),
LeftJoin(ProductStockItem,
ProductStockItem.storable_id == Storable.id),
LeftJoin(Branch, Branch.id == ProductStockItem.branch_id)
]
group_by = [id, brand]
@classmethod
def find_by_branch(cls, store, branch):
if branch:
return store.find(cls, ProductStockItem.branch_id == branch.id)
return store.find(cls)
##
# pylint: enable=E1101
"""Views related to Daily Movement Reports"""
from storm.expr import Join, LeftJoin, Sum, Alias, Select, Coalesce
from storm.info import ClassAlias
from stoqlib.database.expr import Field, NullIf
from stoqlib.domain.payment.views import InPaymentView, OutPaymentView
from stoqlib.domain.person import Branch, Client, Company, Person, SalesPerson
from stoqlib.domain.sale import Sale, SaleItem, InvoiceItemIpi
_SaleItemSummary = Select(columns=[SaleItem.sale_id,
Alias(Sum(SaleItem.quantity * SaleItem.price +
InvoiceItemIpi.v_ipi), 'subtotal')],
tables=[SaleItem,
LeftJoin(InvoiceItemIpi,
SaleItem.ipi_info_id == InvoiceItemIpi.id)],
group_by=[SaleItem.sale_id])
SaleItemSummary = Alias(_SaleItemSummary, '_sale_items')
class DailyInPaymentView(InPaymentView):
SalesPersonPerson = ClassAlias(Person, 'salesperson_person')
ClientPerson = ClassAlias(Person, 'client_person')
PersonBranch = ClassAlias(Person, 'person_branch')
salesperson_name = SalesPersonPerson.name
class SellableFullStockView(Viewable):
"""Stores information about products.
This view is used to query stock information on a certain branch.
:cvar id: the id of the asellable table
:cvar barcode: the sellable barcode
:cvar status: the sellable status
:cvar cost: the sellable cost
:cvar price: the sellable price
:cvar description: the sellable description
:cvar unit: the unit of the product or None
:cvar product_id: the id of the product table or None
:cvar branch_id: the id of branch table or None
:cvar stock: the stock of the product or None
"""
sellable = Sellable
id = Sellable.id
code = Sellable.code
barcode = Sellable.barcode
status = Sellable.status
cost = Sellable.cost
description = Sellable.description
on_sale_price = Sellable.on_sale_price
on_sale_start_date = Sellable.on_sale_start_date
on_sale_end_date = Sellable.on_sale_end_date
base_price = Sellable.base_price
max_discount = Sellable.max_discount
product_id = Product.id
model = Product.model
unit = SellableUnit.description
manufacturer = ProductManufacturer.name
category_description = SellableCategory.description
# Aggregates
stock = Coalesce(Sum(ProductStockItem.quantity), 0)
tables = [
Sellable,
LeftJoin(SellableUnit, SellableUnit.id == Sellable.unit_id),
LeftJoin(SellableCategory,
SellableCategory.id == Sellable.category_id),
LeftJoin(Product, Product.sellable_id == Sellable.id),
LeftJoin(Storable, Storable.product_id == Product.id),
LeftJoin(ProductStockItem,
ProductStockItem.storable_id == Storable.id),
LeftJoin(ProductManufacturer,
Product.manufacturer_id == ProductManufacturer.id),
]
group_by = [
Sellable, SellableUnit, product_id, model, unit, manufacturer,
category_description
]
@classmethod
def find_by_branch(cls, store, branch):
if branch:
# We need the OR part to be able to list services
query = Or(ProductStockItem.branch == branch,
Eq(ProductStockItem.branch_id, None))
return store.find(cls, query)
return store.find(cls)
@property
def price(self):
# See Sellable.price property
if self.on_sale_price:
today = localnow()
start_date = self.on_sale_start_date
end_date = self.on_sale_end_date
if is_date_in_interval(today, start_date, end_date):
return self.on_sale_price
return self.base_price
def post_search_callback(cls, sresults):
select = sresults.get_select_expr(
Count(Distinct(Sellable.id)), Sum(Field('_stock_summary',
'stock')))
return ('count', 'sum'), select
from stoqlib.domain.sellable import (Sellable, SellableUnit, SellableCategory,
SellableTaxConstant)
from stoqlib.domain.stockdecrease import (StockDecrease, StockDecreaseItem)
from stoqlib.lib.decorators import cached_property
from stoqlib.lib.dateutils import localnow
from stoqlib.lib.validators import is_date_in_interval
# Use a subselect to count the number of items, because that takes a lot less
# time (since it doesn't have a huge GROUP BY clause).
# Note that there are two subselects possible. The first should be used when the
# viewable is queried without the branch and the second when it is queried with
# the branch.
_StockSummary = Alias(
Select(columns=[
ProductStockItem.storable_id,
Alias(Sum(ProductStockItem.quantity), 'stock'),
Alias(Sum(ProductStockItem.quantity * ProductStockItem.stock_cost),
'total_stock_cost')
],
tables=[ProductStockItem],
group_by=[ProductStockItem.storable_id]), '_stock_summary')
_StockBranchSummary = Alias(
Select(columns=_StockSummary.expr.columns + [ProductStockItem.branch_id],
tables=_StockSummary.expr.tables[:],
group_by=_StockSummary.expr.group_by +
[ProductStockItem.branch_id]), '_stock_summary')
class ProductFullStockView(Viewable):
"""Stores information about products.
def productserieslanguages(self):
"""See `IProductSeries`."""
store = Store.of(self)
english = getUtility(ILaunchpadCelebrities).english
results = []
if self.potemplate_count == 1:
# If there is only one POTemplate in a ProductSeries, fetch
# Languages and corresponding POFiles with one query, along
# with their stats, and put them into ProductSeriesLanguage
# objects.
origin = [Language, POFile, POTemplate]
query = store.using(*origin).find(
(Language, POFile),
POFile.language == Language.id,
Language.visible == True,
POFile.potemplate == POTemplate.id,
POTemplate.productseries == self,
POTemplate.iscurrent == True,
Language.id != english.id)
ordered_results = query.order_by(['Language.englishname'])
for language, pofile in ordered_results:
psl = ProductSeriesLanguage(self, language, pofile=pofile)
total = pofile.potemplate.messageCount()
imported = pofile.currentCount()
changed = pofile.updatesCount()
rosetta = pofile.rosettaCount()
unreviewed = pofile.unreviewedCount()
translated = imported + rosetta
new = rosetta - changed
psl.setCounts(total, translated, new, changed, unreviewed)
psl.last_changed_date = pofile.date_changed
results.append(psl)
else:
# If there is more than one template, do a single
# query to count total messages in all templates.
query = store.find(
Sum(POTemplate.messagecount),
POTemplate.productseries == self,
POTemplate.iscurrent == True)
total, = query
# And another query to fetch all Languages with translations
# in this ProductSeries, along with their cumulative stats
# for imported, changed, rosetta-provided and unreviewed
# translations.
query = store.find(
(Language,
Sum(POFile.currentcount),
Sum(POFile.updatescount),
Sum(POFile.rosettacount),
Sum(POFile.unreviewed_count),
Max(POFile.date_changed)),
POFile.language == Language.id,
Language.visible == True,
POFile.potemplate == POTemplate.id,
POTemplate.productseries == self,
POTemplate.iscurrent == True,
Language.id != english.id).group_by(Language)
ordered_results = query.order_by(['Language.englishname'])
for (language, imported, changed, rosetta, unreviewed,
last_changed) in ordered_results:
psl = ProductSeriesLanguage(self, language)
translated = imported + rosetta
new = rosetta - changed
psl.setCounts(total, translated, new, changed, unreviewed)
psl.last_changed_date = last_changed
results.append(psl)
return results
def post_search_callback(cls, sresults):
select = sresults.get_select_expr(Count(1), Sum(cls.total))
return ('count', 'sum'), select
return store.find(cls, purchase_id=purchase.id)
#
# Views
#
# Summary for Purchased items
# Its faster to do the SUM() bellow in a subselect, since aggregate
# functions require group by for every other column, and grouping all the
# columns in PurchaseOrderView is extremelly slow, as it requires sorting all
# those columns
from stoqlib.domain.receiving import ReceivingOrder, ReceivingInvoice, PurchaseReceivingMap
_ItemSummary = Select(
columns=[PurchaseItem.order_id,
Alias(Sum(PurchaseItem.quantity), 'ordered_quantity'),
Alias(Sum(PurchaseItem.quantity_received), 'received_quantity'),
Alias(Sum(PurchaseItem.ipi_value), 'ipi_value'),
Alias(Sum((PurchaseItem.quantity *
PurchaseItem.cost) + PurchaseItem.ipi_value), 'subtotal')],
tables=[PurchaseItem],
group_by=[PurchaseItem.order_id])
PurchaseItemSummary = Alias(_ItemSummary, '_purchase_item')
_ReceivingOrder = Select(
columns=[Alias(Min(ReceivingOrder.receival_date), 'receival_date'),
Alias(ArrayToString(ArrayAgg(ReceivingInvoice.invoice_number), ', '),
'invoice_numbers'),
Alias(PurchaseReceivingMap.purchase_id, 'purchase_id')],
tables=[
PurchaseReceivingMap,
"""Views related to Daily Movement Reports"""
from storm.expr import Join, LeftJoin, Sum, Alias, Select, Coalesce
from storm.info import ClassAlias
from stoqlib.database.expr import Field, NullIf, Round
from stoqlib.domain.payment.views import InPaymentView, OutPaymentView
from stoqlib.domain.person import Branch, Client, Company, Person, SalesPerson
from stoqlib.domain.sale import Sale, SaleItem, InvoiceItemIpi
from stoqlib.lib.defaults import DECIMAL_PRECISION
_SaleItemSummary = Select(columns=[
SaleItem.sale_id,
Alias(
Sum(
Round(SaleItem.quantity * SaleItem.price + InvoiceItemIpi.v_ipi,
DECIMAL_PRECISION)), 'subtotal')
],
tables=[
SaleItem,
LeftJoin(
InvoiceItemIpi,
SaleItem.ipi_info_id == InvoiceItemIpi.id)
],
group_by=[SaleItem.sale_id])
SaleItemSummary = Alias(_SaleItemSummary, '_sale_items')
class DailyInPaymentView(InPaymentView):
@classmethod
def find_by_purchase(cls, store, purchase):
return store.find(cls, purchase_id=purchase.id)
#
# Views
#
# Summary for Purchased items
# Its faster to do the SUM() bellow in a subselect, since aggregate
# functions require group by for every other column, and grouping all the
# columns in PurchaseOrderView is extremelly slow, as it requires sorting all
# those columns
_ItemSummary = Select(columns=[PurchaseItem.order_id,
Alias(Sum(PurchaseItem.quantity), 'ordered_quantity'),
Alias(Sum(PurchaseItem.quantity_received), 'received_quantity'),
Alias(Sum(PurchaseItem.quantity *
PurchaseItem.cost), 'subtotal')],
tables=[PurchaseItem],
group_by=[PurchaseItem.order_id])
PurchaseItemSummary = Alias(_ItemSummary, '_purchase_item')
from stoqlib.domain.receiving import ReceivingOrder, PurchaseReceivingMap
_ReceivingOrder = Select(columns=[Alias(Min(ReceivingOrder.receival_date), 'receival_date'),
Alias(PurchaseReceivingMap.purchase_id, 'purchase_id')],
tables=[PurchaseReceivingMap,
LeftJoin(ReceivingOrder,
ReceivingOrder.id == PurchaseReceivingMap.receiving_id)],
group_by=[PurchaseReceivingMap.purchase_id])
class TransferOrderView(BaseTransferView):
id = TransferOrder.id
# Aggregates
total_items = Sum(TransferOrderItem.quantity)
@classmethod
def find_by_sale(cls, store, sale):
"""Returns all |workorders| associated with the given |sale|.
:param sale: The |sale| used to filter the existing |workorders|
:resturn: An iterable with all work orders:
:rtype: resultset
"""
return store.find(cls, sale=sale)
_WorkOrderItemsSummary = Alias(
Select(columns=[
WorkOrderItem.order_id,
Alias(Sum(WorkOrderItem.quantity), 'quantity'),
Alias(Sum(WorkOrderItem.quantity * WorkOrderItem.price), 'total')
],
tables=[WorkOrderItem],
group_by=[WorkOrderItem.order_id]), '_work_order_items')
class WorkOrderView(Viewable):
"""A view for |workorders|
This is used to get the most information of a |workorder|
without doing lots of database queries.
"""
# TODO: Maybe we should have a cache for branches, to avoid all this
# joins just to get the company name.
else:
a, b = self.get_range_value()
return a <= value <= b
def can_remove(self):
from stoqlib.domain.production import ProductionItemQualityResult
if self.store.find(ProductionItemQualityResult, quality_test=self).count():
return False
return True
_StockSummary = Alias(Select(
columns=[
ProductStockItem.batch_id,
ProductStockItem.branch_id,
Alias(Sum(ProductStockItem.quantity), 'stock')],
tables=[
ProductStockItem],
group_by=[
ProductStockItem.batch_id,
ProductStockItem.branch_id]),
'_stock_summary')
class StorableBatchView(Viewable):
"""A view for |batches|
This is used to get the most information of a |batch|
without doing lots of database queries.
It's bestly used with :meth:`.find_by_storable`
