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`