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
