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), ]
def test_json_element(self): "JSONElement returns an element from a json field." connection = self.database.connect() json_value = Cast(u'{"a": 1}', "json") expr = JSONElement(json_value, u"a") # Need to cast as text since newer psycopg versions decode JSON # automatically. result = connection.execute(Select(Cast(expr, "text"))) self.assertEqual("1", result.get_one()[0]) result = connection.execute(Select(Func("pg_typeof", expr))) self.assertEqual("json", result.get_one()[0])
def test_generate_series_date(self): a = datetime.datetime(2012, 1, 1) b = datetime.datetime(2012, 4, 1) series = GenerateSeries(Cast(a, 'timestamp'), Cast(b, 'timestamp'), Cast(u'1 month', 'interval')), data = list( self.store.using(series).find( Field('generate_series', 'generate_series'))) self.assertEquals(len(data), 4) self.assertEquals(data[0], a) self.assertEquals(data[1], datetime.datetime(2012, 2, 1)) self.assertEquals(data[2], datetime.datetime(2012, 3, 1)) self.assertEquals(data[3], b)
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), ]
class TillFiscalOperationsView(Viewable): """Stores informations about till payment tables :attribute date: the date when the entry was created :attribute description: the entry description :attribute value: the entry value :attribute station_name: the value of name branch_station name column """ id = TillEntry.id identifier = Payment.identifier identifier_str = Cast(Payment.identifier, 'text') date = Payment.open_date description = Payment.description value = Payment.value cfop = CfopData.code station_name = BranchStation.name branch_id = Branch.id status = Till.status tables = [ TillEntry, Join(Payment, Payment.id == TillEntry.payment_id), Join(Till, Till.id == TillEntry.till_id), Join(BranchStation, BranchStation.id == Till.station_id), Join(Branch, Branch.id == BranchStation.branch_id), Join(PaymentGroup, PaymentGroup.id == Payment.group_id), Join(Sale, Sale.group_id == PaymentGroup.id), Join(CfopData, CfopData.id == Sale.cfop_id), ]
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 identifier_str = Cast(Loan.identifier, 'text') 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 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 CommissionView(Viewable): """ Stores information about commissions and it's related sale and payment. """ id = Sale.id identifier = Sale.identifier identifier_str = Cast(Sale.identifier, 'text') sale_status = Sale.status code = Commission.id commission_value = Commission.value commission_percentage = Commission.value / Payment.value * 100 salesperson_name = Person.name payment_id = Payment.id payment_value = Payment.value confirm_date = Sale.confirm_date paid_date = Payment.paid_date tables = [ Sale, Join(Commission, Commission.sale_id == Sale.id), Join(SalesPerson, SalesPerson.id == Commission.salesperson_id), Join(Person, Person.id == SalesPerson.person_id), Join(Payment, Payment.id == Commission.payment_id), ] @property def sale(self): return self.store.get(Sale, self.id) @property def payment(self): return self.store.get(Payment, self.payment_id) def quantity_sold(self): if self.sale_returned(): # zero means 'this sale does not changed our stock' return Decimal(0) return self.sale.get_items_total_quantity() def get_payment_amount(self): # the returning payment should be shown as negative one if self.payment.is_outpayment(): return -self.payment_value return self.payment_value def get_total_amount(self): # XXX: No, the sale amount does not change. But I return different # values based in type of the payment to guess how I might show the # total sale amount. if self.payment.is_outpayment(): return -self.sale.total_amount return self.sale.total_amount def sale_returned(self): return self.sale_status == Sale.STATUS_RETURNED
def test_json_text_element(self): "JSONTextElement returns an element from a json field as text." connection = self.database.connect() json_value = Cast(u'{"a": 1}', "json") expr = JSONTextElement(json_value, u"a") result = connection.execute(Select(expr)) self.assertEqual("1", result.get_one()[0]) result = connection.execute(Select(Func("pg_typeof", expr))) self.assertEqual("text", result.get_one()[0])
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 DeliveryView(Viewable): PersonTransporter = ClassAlias(Person, 'person_transporter') PersonClient = ClassAlias(Person, 'person_client') delivery = Delivery # Delivery id = Delivery.id status = Delivery.status tracking_code = Delivery.tracking_code open_date = Delivery.open_date deliver_date = Delivery.deliver_date receive_date = Delivery.receive_date identifier_str = Cast(Sale.identifier, 'text') # Transporter transporter_name = PersonTransporter.name # Client client_name = PersonClient.name # Sale sale_identifier = Sale.identifier # Address address_id = Delivery.address_id tables = [ Delivery, LeftJoin(Transporter, Transporter.id == Delivery.transporter_id), LeftJoin(PersonTransporter, PersonTransporter.id == Transporter.person_id), LeftJoin(SaleItem, SaleItem.id == Delivery.service_item_id), LeftJoin(Sale, Sale.id == SaleItem.sale_id), LeftJoin(Client, Client.id == Sale.client_id), LeftJoin(PersonClient, PersonClient.id == Client.person_id), # LeftJoin(Address, # Address.person_id == Client.person_id), ] @property def status_str(self): return Delivery.statuses[self.status] @property def address_str(self): return self.store.get(Address, self.address_id).get_description()
class PurchaseReceivingView(Viewable): """Stores information about received orders. :cvar id: the id of the receiving order :cvar receival_date: the date when the receiving order was closed :cvar invoice_number: the number of the order that was received :cvar invoice_total: the total value of the received order :cvar purchase_identifier: the identifier of the received order :cvar branch_id: the id branch where the order was received :cvar purchase_responsible_name: the one who have confirmed the purchase :cvar responsible_name: the one who has received the order :cvar supplier_name: the supplier name """ _Responsible = ClassAlias(Person, "responsible") _Supplier = ClassAlias(Person, "supplier_person") _PurchaseUser = ClassAlias(LoginUser, "purchase_user") _PurchaseResponsible = ClassAlias(Person, "purchase_responsible") order = ReceivingOrder id = ReceivingOrder.id receival_date = ReceivingOrder.receival_date invoice_number = ReceivingOrder.invoice_number invoice_total = ReceivingOrder.invoice_total purchase_identifier = PurchaseOrder.identifier purchase_identifier_str = Cast(PurchaseOrder.identifier, 'text') branch_id = ReceivingOrder.branch_id purchase_responsible_name = _PurchaseResponsible.name responsible_name = _Responsible.name supplier_name = _Supplier.name tables = [ ReceivingOrder, LeftJoin(PurchaseOrder, ReceivingOrder.purchase_id == PurchaseOrder.id), LeftJoin(_PurchaseUser, PurchaseOrder.responsible_id == _PurchaseUser.id), LeftJoin(_PurchaseResponsible, _PurchaseUser.person_id == _PurchaseResponsible.id), LeftJoin(Supplier, ReceivingOrder.supplier_id == Supplier.id), LeftJoin(_Supplier, Supplier.person_id == _Supplier.id), LeftJoin(LoginUser, ReceivingOrder.responsible_id == LoginUser.id), LeftJoin(_Responsible, LoginUser.person_id == _Responsible.id), ]
class BaseTransferView(Viewable): BranchDest = ClassAlias(Branch, 'branch_dest') PersonDest = ClassAlias(Person, 'person_dest') CompanyDest = ClassAlias(Company, 'company_dest') transfer_order = TransferOrder identifier = TransferOrder.identifier identifier_str = Cast(TransferOrder.identifier, 'text') status = TransferOrder.status open_date = TransferOrder.open_date finish_date = Coalesce(TransferOrder.receival_date, TransferOrder.cancel_date) source_branch_id = TransferOrder.source_branch_id destination_branch_id = TransferOrder.destination_branch_id source_branch_name = Coalesce(NullIf(Company.fancy_name, u''), Person.name) destination_branch_name = Coalesce(NullIf(CompanyDest.fancy_name, u''), PersonDest.name) group_by = [TransferOrder, source_branch_name, destination_branch_name] tables = [ TransferOrder, Join(TransferOrderItem, TransferOrder.id == TransferOrderItem.transfer_order_id), # Source LeftJoin(Branch, TransferOrder.source_branch_id == Branch.id), LeftJoin(Person, Branch.person_id == Person.id), LeftJoin(Company, Company.person_id == Person.id), # Destination LeftJoin(BranchDest, TransferOrder.destination_branch_id == BranchDest.id), LeftJoin(PersonDest, BranchDest.person_id == PersonDest.id), LeftJoin(CompanyDest, CompanyDest.person_id == PersonDest.id), ] @property def branch(self): # We need this property for the acronym to appear in the identifier return self.store.get(Branch, self.source_branch_id)
class LoanView(Viewable): PersonBranch = ClassAlias(Person, 'person_branch') PersonResponsible = ClassAlias(Person, 'person_responsible') PersonClient = ClassAlias(Person, 'person_client') loan = Loan id = Loan.id identifier = Loan.identifier identifier_str = Cast(Loan.identifier, 'text') status = Loan.status open_date = Loan.open_date close_date = Loan.close_date expire_date = Loan.expire_date removed_by = Loan.removed_by client_id = Loan.client_id branch_id = Loan.branch_id branch_name = PersonBranch.name responsible_name = PersonResponsible.name client_name = PersonClient.name # Aggregates loaned = Sum(LoanItem.quantity) total = Sum(LoanItem.quantity * LoanItem.price) tables = [ Loan, Join(LoanItem, Loan.id == LoanItem.loan_id), LeftJoin(Branch, Loan.branch_id == Branch.id), LeftJoin(LoginUser, Loan.responsible_id == LoginUser.id), LeftJoin(Client, Loan.client_id == Client.id), LeftJoin(PersonBranch, Branch.person_id == PersonBranch.id), LeftJoin(PersonResponsible, LoginUser.person_id == PersonResponsible.id), LeftJoin(PersonClient, Client.person_id == PersonClient.id), ] group_by = [Loan, branch_name, responsible_name, client_name]
class SellableView(Viewable): sellable = Sellable product = Product storable = Storable service = Service id = Sellable.id markup = Case(condition=(Sellable.cost == 0), result=0, else_=(Sellable.base_price / Sellable.cost - 1) * 100) # Be explict about the type to workaround an issue with storm need_price_update = Cast( Sellable.cost_last_updated > Sellable.price_last_updated, 'boolean') 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 InventoryView(Viewable): """Stores general information's about inventories""" inventory = Inventory # Inventory #: Inventory Id id = Inventory.id #: Inventory Identifier identifier = Inventory.identifier #: Inventory Identifier ToString identifier_str = Cast(Inventory.identifier, 'text') #: Invoice number invoice_number = Inventory.invoice_number #: Date of open operation open_date = Inventory.open_date #: Date of close operation close_date = Inventory.close_date #: Status of Inventory status = Inventory.status #: Id of referenced Branch branch_id = Inventory.branch_id tables = [Inventory] @classmethod def find_by_branch(cls, store, branch=None): """find results for this Inventory View that refenrences *Branch* :param store: the store that will be used for find the results :param branch: the |branch| used to filter the results :return: the matching views """ if branch is not None: return store.find(cls, branch_id=branch.id) return store.find(cls)
class ProductionItemView(Viewable): production_item = ProductionItem id = ProductionItem.id order_identifier = ProductionOrder.identifier order_identifier_str = Cast(ProductionOrder.identifier, 'text') order_status = ProductionOrder.status quantity = ProductionItem.quantity produced = ProductionItem.produced lost = ProductionItem.lost category_description = SellableCategory.description unit_description = SellableUnit.description description = Sellable.description tables = [ ProductionItem, LeftJoin(ProductionOrder, ProductionItem.order_id == ProductionOrder.id), LeftJoin(Product, ProductionItem.product_id == Product.id), LeftJoin(Sellable, Sellable.id == Product.sellable_id), LeftJoin(SellableCategory, SellableCategory.id == Sellable.category_id), LeftJoin(SellableUnit, Sellable.unit_id == SellableUnit.id), ]
class BasePaymentView(Viewable): PaymentGroup_Sale = ClassAlias(PaymentGroup, 'payment_group_sale') PaymentGroup_Purchase = ClassAlias(PaymentGroup, 'payment_group_purchase') payment = Payment group = PaymentGroup purchase = PurchaseOrder sale = Sale method = PaymentMethod card_data = CreditCardData check_data = CheckData # Payment id = Payment.id identifier = Payment.identifier identifier_str = Cast(Payment.identifier, 'text') description = Payment.description due_date = Payment.due_date status = Payment.status paid_date = Payment.paid_date value = Payment.value paid_value = Payment.paid_value payment_number = Payment.payment_number group_id = Payment.group_id # PaymentGroup renegotiated_id = PaymentGroup.renegotiation_id # PaymentMethod method_name = PaymentMethod.method_name method_id = PaymentMethod.id # PaymentCategory color = PaymentCategory.color category = PaymentCategory.name # PaymentComment comments_number = Field('_comments', 'comments_number') # Sale sale_id = Sale.id # Purchase purchase_id = PurchaseOrder.id purchase_status = PurchaseOrder.status _count_tables = [ Payment, LeftJoin(PaymentGroup, PaymentGroup.id == Payment.group_id), LeftJoin(PaymentCategory, PaymentCategory.id == Payment.category_id), Join(PaymentMethod, Payment.method_id == PaymentMethod.id), LeftJoin(CreditCardData, Payment.id == CreditCardData.payment_id), LeftJoin(CheckData, Payment.id == CheckData.payment_id), # Purchase LeftJoin(PaymentGroup_Purchase, PaymentGroup_Purchase.id == Payment.group_id), LeftJoin(PurchaseOrder, PurchaseOrder.group_id == PaymentGroup_Purchase.id), # Sale LeftJoin(PaymentGroup_Sale, PaymentGroup_Sale.id == Payment.group_id), LeftJoin(Sale, Sale.group_id == PaymentGroup_Sale.id), ] tables = _count_tables + [ LeftJoin(CommentsSummary, Field('_comments', 'payment_id') == Payment.id), ] @classmethod def post_search_callback(cls, sresults): select = sresults.get_select_expr(Count(1), Sum(cls.value)) return ('count', 'sum'), select def can_change_due_date(self): return self.status not in [ Payment.STATUS_PAID, Payment.STATUS_CANCELLED ] def can_cancel_payment(self): """Only lonely payments and pending can be cancelled """ if self.sale_id or self.purchase_id: return False return self.status == Payment.STATUS_PENDING def get_status_str(self): return Payment.statuses[self.status] def is_late(self): if self.status in [Payment.STATUS_PAID, Payment.STATUS_CANCELLED]: return False return (localtoday().date() - self.due_date.date()).days > 0 def get_days_late(self): if not self.is_late(): return 0 days_late = localtoday().date() - self.due_date.date() if days_late.days < 0: return 0 return days_late.days def is_paid(self): return self.status == Payment.STATUS_PAID @property def operation(self): return self.method.operation @classmethod def find_pending(cls, store, due_date=None): query = cls.status == Payment.STATUS_PENDING if due_date: if isinstance(due_date, tuple): date_query = And( Date(cls.due_date) >= due_date[0], Date(cls.due_date) <= due_date[1]) else: date_query = Date(cls.due_date) == due_date query = And(query, date_query) return store.find(cls, query)
class PurchaseOrderView(Viewable): """General information about purchase orders :cvar id: the id of purchase_order table :cvar status: the purchase order status :cvar open_date: the date when the order was started :cvar quote_deadline: the date when the quotation expires :cvar expected_receival_date: expected date to receive products :cvar expected_pay_date: expected date to pay the products :cvar receival_date: the date when the products were received :cvar confirm_date: the date when the order was confirmed :cvar salesperson_name: the name of supplier's salesperson :cvar expected_freight: the expected freight value :cvar surcharge_value: the surcharge value for the order total :cvar discount_value: the discount_value for the order total :cvar supplier_name: the supplier name :cvar transporter_name: the transporter name :cvar branch_name: the branch company name :cvar ordered_quantity: the total quantity ordered :cvar received_quantity: the total quantity received :cvar subtotal: the order subtotal (sum of product values) :cvar total: subtotal - discount_value + surcharge_value """ Person_Supplier = ClassAlias(Person, 'person_supplier') Person_Transporter = ClassAlias(Person, 'person_transporter') Person_Branch = ClassAlias(Person, 'person_branch') Person_Responsible = ClassAlias(Person, 'person_responsible') purchase = PurchaseOrder branch = Branch id = PurchaseOrder.id identifier = PurchaseOrder.identifier identifier_str = Cast(PurchaseOrder.identifier, 'text') status = PurchaseOrder.status open_date = PurchaseOrder.open_date quote_deadline = PurchaseOrder.quote_deadline expected_receival_date = PurchaseOrder.expected_receival_date expected_pay_date = PurchaseOrder.expected_pay_date confirm_date = PurchaseOrder.confirm_date salesperson_name = NullIf(PurchaseOrder.salesperson_name, u'') expected_freight = PurchaseOrder.expected_freight surcharge_value = PurchaseOrder.surcharge_value discount_value = PurchaseOrder.discount_value branch_id = Branch.id supplier_id = Supplier.id supplier_name = Person_Supplier.name transporter_name = Coalesce(Person_Transporter.name, u'') branch_name = Coalesce(NullIf(Company.fancy_name, u''), Person_Branch.name) responsible_name = Person_Responsible.name receival_date = Field('_receiving_order', 'receival_date') ordered_quantity = Field('_purchase_item', 'ordered_quantity') received_quantity = Field('_purchase_item', 'received_quantity') subtotal = Field('_purchase_item', 'subtotal') total = Field('_purchase_item', 'subtotal') - \ PurchaseOrder.discount_value + PurchaseOrder.surcharge_value tables = [ PurchaseOrder, Join(PurchaseItemSummary, Field('_purchase_item', 'order_id') == PurchaseOrder.id), LeftJoin(PurchaseReceivingSummary, Field('_receiving_order', 'purchase_id') == PurchaseOrder.id), LeftJoin(Supplier, PurchaseOrder.supplier_id == Supplier.id), LeftJoin(Transporter, PurchaseOrder.transporter_id == Transporter.id), LeftJoin(Branch, PurchaseOrder.branch_id == Branch.id), LeftJoin(LoginUser, PurchaseOrder.responsible_id == LoginUser.id), LeftJoin(Person_Supplier, Supplier.person_id == Person_Supplier.id), LeftJoin(Person_Transporter, Transporter.person_id == Person_Transporter.id), LeftJoin(Person_Branch, Branch.person_id == Person_Branch.id), LeftJoin(Company, Company.person_id == Person_Branch.id), LeftJoin(Person_Responsible, LoginUser.person_id == Person_Responsible.id), ] @classmethod def post_search_callback(cls, sresults): select = sresults.get_select_expr(Count(1), Sum(cls.total)) return ('count', 'sum'), select # # Public API # def get_open_date_as_string(self): return self.open_date.strftime("%x") @property def status_str(self): return PurchaseOrder.translate_status(self.status) @classmethod def find_confirmed(cls, store, due_date=None): query = cls.status == PurchaseOrder.ORDER_CONFIRMED if due_date: if isinstance(due_date, tuple): date_query = And(Date(cls.expected_receival_date) >= due_date[0], Date(cls.expected_receival_date) <= due_date[1]) else: date_query = Date(cls.expected_receival_date) == due_date query = And(query, date_query) return store.find(cls, query)
def get(self, store): from stoqnfe.domain.distribution import ImportedNfe cnpj = self.get_arg('cnpj') limit = self.get_arg('limit') offset = self.get_arg('offset') if not cnpj: message = "'cnpj' not provided" log.error(message) abort(400, message) if not validate_cnpj(cnpj): message = "Invalid 'cnpj' provided" log.error(message) abort(400, message) if limit is not None: try: limit = int(limit) except (TypeError, ValueError): message = "'limit' must be a number" log.error(message) abort(400, message) if limit > MAX_PAGE_SIZE: message = "'limit' must be lower than %s" % MAX_PAGE_SIZE log.error(message) abort(400, message) if offset is not None: try: offset = int(offset) except (TypeError, ValueError): message = "'offset' must be a number" log.error(message) abort(400, message) cnpj = format_cnpj(raw_document(cnpj)) limit = limit or 20 offset = offset or 0 login_user = self.get_current_user(store) tables = [ Branch, Join(Person, Branch.person_id == Person.id), Join(Company, Company.person_id == Person.id) ] query = Eq(Company.cnpj, cnpj) branches = store.using(*tables).find(Branch, query) # XXX There should exist at least one branch in database with # the cnpj from ImportedNfes. Otherwise, there is something wrong that # could lead to unwanted access to these ImportedNfes. assert branches for branch in branches: has_access = UserBranchAccess.has_access(store, login_user, branch) if has_access: continue message = 'login_user %s does not have access to branch %s' % \ (login_user.id, branch.id) log.error(message) abort(403, message) query = And(ImportedNfe.cnpj == cnpj, Cast(ImportedNfe.xml, 'text').startswith(NFEPROC_TYPE)) result = store.find(ImportedNfe, query).order_by(Desc(ImportedNfe.te_id)) result_count = result.count() imported_nfes = result.config(offset=offset, limit=limit) records = [] for imported_nfe in imported_nfes: # FIXME: Change it to a store.find() when NFePurchase.key had been implemented query = "SELECT id FROM nfe_purchase WHERE cnpj='{}' AND xml::text ilike '%{}%'" nfe_purchase = store.execute( query.format(imported_nfe.cnpj, imported_nfe.key)).get_one() process_date = imported_nfe.process_date record = { 'id': imported_nfe.id, 'key': imported_nfe.key, # Since process_date is a new column, we can't assure that # all entries have it fulfilled 'process_date': process_date and process_date.isoformat(), 'purchase_invoice_id': nfe_purchase and nfe_purchase[0] } records.append(record) next_offset = offset + limit has_next = result_count > next_offset next_ = None if has_next: next_ = self.routes[0] + '?limit={}&offset={}&cnpj={}'.format( limit, offset + limit, cnpj) has_previous = offset > 0 previous = None if has_previous: previous = self.routes[0] + '?limit={}&offset={}&cnpj={}'.format( limit, max(offset - limit, 0), cnpj) response = { 'previous': previous, 'next': next_, 'count': len(records), 'total_records': result_count, 'records': records } return make_response(jsonify(response), 200)
def calculate_bugsummary_rows(target): """Calculate BugSummary row fragments for the given `IBugTarget`. The data is re-aggregated from BugTaskFlat, BugTag and BugSubscription. """ # Use a CTE to prepare a subset of BugTaskFlat, filtered to the # relevant target and to exclude duplicates, and with has_patch # calculated. relevant_tasks = With( 'relevant_task', Select((BugTaskFlat.bug_id, BugTaskFlat.information_type, BugTaskFlat.status, BugTaskFlat.milestone_id, BugTaskFlat.importance, Alias(BugTaskFlat.latest_patch_uploaded != None, 'has_patch'), BugTaskFlat.access_grants, BugTaskFlat.access_policies), tables=[BugTaskFlat], where=And(BugTaskFlat.duplicateof_id == None, *get_bugtaskflat_constraint(target)))) # Storm class to reference the CTE. class RelevantTask(BugTaskFlat): __storm_table__ = 'relevant_task' has_patch = Bool() # Storm class to reference the union. class BugSummaryPrototype(RawBugSummary): __storm_table__ = 'bugsummary_prototype' # Prepare a union for all combination of privacy and taggedness. # It'll return a full set of # (status, milestone, importance, has_patch, tag, viewed_by, access_policy) # rows. common_cols = (RelevantTask.status, RelevantTask.milestone_id, RelevantTask.importance, RelevantTask.has_patch) null_tag = Alias(Cast(None, 'text'), 'tag') null_viewed_by = Alias(Cast(None, 'integer'), 'viewed_by') null_policy = Alias(Cast(None, 'integer'), 'access_policy') tag_join = Join(BugTag, BugTag.bugID == RelevantTask.bug_id) public_constraint = RelevantTask.information_type.is_in( PUBLIC_INFORMATION_TYPES) private_constraint = RelevantTask.information_type.is_in( PRIVATE_INFORMATION_TYPES) unions = Union( # Public, tagless Select(common_cols + (null_tag, null_viewed_by, null_policy), tables=[RelevantTask], where=public_constraint), # Public, tagged Select(common_cols + (BugTag.tag, null_viewed_by, null_policy), tables=[RelevantTask, tag_join], where=public_constraint), # Private, access grant, tagless Select(common_cols + (null_tag, Unnest(RelevantTask.access_grants), null_policy), tables=[RelevantTask], where=private_constraint), # Private, access grant, tagged Select(common_cols + (BugTag.tag, Unnest(RelevantTask.access_grants), null_policy), tables=[RelevantTask, tag_join], where=private_constraint), # Private, access policy, tagless Select( common_cols + (null_tag, null_viewed_by, Unnest(RelevantTask.access_policies)), tables=[RelevantTask], where=private_constraint), # Private, access policy, tagged Select( common_cols + (BugTag.tag, null_viewed_by, Unnest(RelevantTask.access_policies)), tables=[RelevantTask, tag_join], where=private_constraint), all=True) # Select the relevant bits of the prototype rows and aggregate them. proto_key_cols = (BugSummaryPrototype.status, BugSummaryPrototype.milestone_id, BugSummaryPrototype.importance, BugSummaryPrototype.has_patch, BugSummaryPrototype.tag, BugSummaryPrototype.viewed_by_id, BugSummaryPrototype.access_policy_id) origin = IStore(BugTaskFlat).with_(relevant_tasks).using( Alias(unions, 'bugsummary_prototype')) results = origin.find(proto_key_cols + (Count(), )) results = results.group_by(*proto_key_cols).order_by(*proto_key_cols) return results
class CommissionView(Viewable): """ Stores information about commissions and it's related sale and payment. """ #: the branch this commission was generated branch = Branch payment = Payment sale = Sale # Sale id = Sale.id identifier = Sale.identifier identifier_str = Cast(Sale.identifier, 'text') sale_status = Sale.status confirm_date = Sale.confirm_date # Commission code = Commission.id commission_value = Commission.value commission_percentage = Commission.value / Payment.value * 100 # Payment payment_id = Payment.id payment_value = Payment.value method_name = PaymentMethod.method_name paid_date = Payment.paid_date # Salesperson salesperson_id = SalesPerson.id salesperson_name = Person.name tables = [ Sale, Join(Branch, Sale.branch_id == Branch.id), Join(Commission, Commission.sale_id == Sale.id), Join(SalesPerson, SalesPerson.id == Sale.salesperson_id), Join(Person, Person.id == SalesPerson.person_id), Join(Payment, Payment.id == Commission.payment_id), Join(PaymentMethod, Payment.method_id == PaymentMethod.id), ] @property def method_description(self): from stoqlib.domain.payment.operation import get_payment_operation return get_payment_operation(self.method_name).description # pylint: disable=E1120 @property def quantity_sold(self): if self.sale_returned: # zero means 'this sale does not changed our stock' return Decimal(0) # FIXME: This is doing one extra query per row when printing the report return self.sale.get_items_total_quantity() @property def payment_amount(self): # the returning payment should be shown as negative one if self.payment.is_outpayment(): return -self.payment_value return self.payment_value @property def total_amount(self): # XXX: No, the sale amount does not change. But I return different # values based in type of the payment to guess how I might show the # total sale amount. if self.payment.is_outpayment(): return -self.sale.total_amount return self.sale.total_amount # pylint: enable=E1120 @property def sale_returned(self): return self.sale_status == Sale.STATUS_RETURNED