def test_get_birthday_interval_query(self):
start = localdatetime(2000, 3, 1)
end = localdatetime(2000, 3, 25)
query = Individual.get_birthday_query(start, end)
start_year = DateTrunc(u'year', Date(start))
age_in_year = Age(Individual.birth_date,
DateTrunc(u'year', Individual.birth_date))
test_query = (
start_year + age_in_year +
Case(condition=age_in_year < Age(Date(start), start_year),
result=Interval(u'1 year'),
else_=Interval(u'0 year')))
test_query = And(test_query >= Date(start), test_query <= Date(end))
self.assertEquals(query, test_query)
individuals = list(self.store.find(Individual, test_query))
self.assertEquals(len(individuals), 0)
client1 = self.create_client(u'Junio C. Hamano')
client1.person.individual.birth_date = localdate(1972, 10, 15)
client2 = self.create_client(u'Richard Stallman')
client2.person.individual.birth_date = localdate(1989, 3, 7)
client3 = self.create_client(u'Linus Torvalds')
client3.person.individual.birth_date = localdate(2000, 3, 4)
client4 = self.create_client(u'Guido van Rossum')
client4.person.individual.birth_date = localdate(2005, 3, 20)
individuals = list(self.store.find(Individual, test_query))
self.assertEquals(len(individuals), 3)
self.assertTrue(client2.person.individual in individuals)
self.assertTrue(client3.person.individual in individuals)
self.assertTrue(client4.person.individual in individuals)
class TillClosedView(Viewable):
id = Till.id
observations = Till.observations
opening_date = Date(Till.opening_date)
closing_date = Date(Till.closing_date)
initial_cash_amount = Till.initial_cash_amount
final_cash_amount = Till.final_cash_amount
branch_id = BranchStation.branch_id
_ResponsibleOpen = ClassAlias(Person, "responsible_open")
_ResponsibleClose = ClassAlias(Person, "responsible_close")
_LoginUserOpen = ClassAlias(LoginUser, "login_responsible_open")
_LoginUserClose = ClassAlias(LoginUser, "login_responsible_close")
responsible_open_name = _ResponsibleOpen.name
responsible_close_name = _ResponsibleClose.name
tables = [
Till,
Join(BranchStation, BranchStation.id == Till.station_id),
# These two need to be left joins, since historical till dont have a
# responsible
LeftJoin(_LoginUserOpen, Till.responsible_open_id == _LoginUserOpen.id),
LeftJoin(_LoginUserClose, Till.responsible_close_id == _LoginUserClose.id),
LeftJoin(_ResponsibleOpen, _LoginUserOpen.person_id == _ResponsibleOpen.id),
LeftJoin(_ResponsibleClose, _LoginUserClose.person_id == _ResponsibleClose.id),
]
clause = Till.status == Till.STATUS_CLOSED
def get_total_for_interval(self, start, end):
"""Fetch total value for a given interval
:param datetime start: beginning of interval
:param datetime end: of interval
:returns: total value or one
"""
if not isinstance(start, datetime.datetime):
raise TypeError("start must be a datetime.datetime, not %s" %
(type(start), ))
if not isinstance(end, datetime.datetime):
raise TypeError("end must be a datetime.datetime, not %s" %
(type(end), ))
query = And(
Date(AccountTransaction.date) >= start,
Date(AccountTransaction.date) <= end,
AccountTransaction.source_account_id !=
AccountTransaction.account_id)
transactions = self.store.find(AccountTransaction, query)
incoming = transactions.find(AccountTransaction.account_id == self.id)
outgoing = transactions.find(
AccountTransaction.source_account_id == self.id)
positive_values = incoming.sum(AccountTransaction.value) or 0
negative_values = outgoing.sum(AccountTransaction.value) or 0
return currency(positive_values - negative_values)
def _parse_date_interval_state(self, state, table_field):
queries = []
if state.start:
queries.append(Date(table_field) >= Date(state.start))
if state.end:
queries.append(Date(table_field) <= Date(state.end))
if queries:
return And(*queries)
def _get_sales(self, returned=False):
# TODO: We need to add station_id to the sales table
query = And(Date(Sale.confirm_date) == self.start,
# Sale.station_id == self.printer.station_id
)
if returned:
query = And(Date(Sale.return_date) == self.end, )
return self.store.find(Sale, query)
def _get_query(self, date_attr, branch_attr):
daterange = self.get_daterange()
query = [Date(date_attr) >= Date(daterange[0]),
Date(date_attr) <= Date(daterange[1])]
branch = self.model.branch
if branch is not None:
query.append(branch_attr == branch)
return And(*query)
def _append_date_query(self, field):
date = self.date_filter.get_state()
queries = []
if isinstance(date, DateQueryState) and date.date is not None:
queries.append(Date(field) == date.date)
elif isinstance(date, DateIntervalQueryState):
queries.append(Date(field) >= date.start)
queries.append(Date(field) <= date.end)
return queries
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 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)
def open_till(self):
"""Open the till.
It can only be done once per day.
The final cash amount of the previous till will be used
as the initial value in this one after opening it.
"""
if self.status == Till.STATUS_OPEN:
raise TillError(_('Till is already open'))
# Make sure that the till has not been opened today
today = localtoday().date()
if not self.store.find(
Till,
And(
Date(Till.opening_date) >= today, Till.station_id
== self.station.id)).is_empty():
raise TillError(_("A till has already been opened today"))
last_till = self._get_last_closed_till()
if last_till:
if not last_till.closing_date:
raise TillError(_("Previous till was not closed"))
initial_cash_amount = last_till.final_cash_amount
else:
initial_cash_amount = 0
self.initial_cash_amount = initial_cash_amount
self.opening_date = TransactionTimestamp()
self.status = Till.STATUS_OPEN
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)
def test_find_pending(self):
for i in range(5):
payment = self.create_payment(payment_type=Payment.TYPE_IN)
payment.status = Payment.STATUS_PENDING
if i % 2 == 0:
payment.due_date = localtoday() + datetime.timedelta(-2)
else:
payment.due_date = Date(localtoday())
# Between 4 days ago and today
due_date = (localtoday() + datetime.timedelta(-4), localtoday())
result = InPaymentView.find_pending(store=self.store,
due_date=due_date)
self.assertEqual(result.count(), 5)
result = InPaymentView.find_pending(store=self.store,
due_date=Date(localtoday()))
self.assertEqual(result.count(), 2)
result = InPaymentView.find_pending(store=self.store, due_date=None)
self.assertEqual(result.count(), 7)
def get_divergent_payments(self):
"""Returns a :class:`Payment` sequence that meet the following requirements:
* The payment due date, paid date or cancel date is the current
PaymentFlowHistory date.
* The payment was paid/received with different values (eg with
discount or surcharge).
* The payment was scheduled to be paid/received on the current,
but it was not.
* The payment was not expected to be paid/received on the current date.
"""
from stoqlib.domain.payment.payment import Payment
date = self.history_date
query = And(Or(Date(Payment.due_date) == date,
Date(Payment.paid_date) == date,
Date(Payment.cancel_date) == date),
Or(Eq(Payment.paid_value, None),
Payment.value != Payment.paid_value,
Eq(Payment.paid_date, None),
Date(Payment.due_date) != Date(Payment.paid_date)))
return self.store.find(Payment, query)
def _collect_timeline(store):
today = datetime.date.today()
sales = store.find(SaleView, Date(SaleView.confirm_date) == today)
items = []
for i in sales:
items.append(
dict(type='sale',
identifier=str(i.identifier),
when=i.confirm_date,
value=i.total))
items = sorted(items, key=lambda i: i['when'], reverse=True)
return items
def _query_executer(self, store):
# We should only show Sales that
# 1) In the current branch (FIXME: Should be on the same station.
# See bug 4266)
# 2) Are in the status QUOTE or ORDERED.
# 3) For the order statuses, the date should be the same as today
query = And(Sale.branch == self.current_branch,
Or(Sale.status == Sale.STATUS_QUOTE,
Sale.status == Sale.STATUS_ORDERED,
Date(Sale.open_date) == date.today()))
return store.find(self.search_spec, query)
def test_changed_field(self):
payment = self.create_payment()
history = PaymentChangeHistory(payment=payment,
change_reason=u'Teste test test')
view = self.store.find(PaymentChangeHistoryView, id=history.id).one()
self.assertIsNotNone(view.changed_field)
history.last_due_date = Date(localtoday())
history.last_status = Payment.STATUS_PENDING
view = self.store.find(PaymentChangeHistoryView, id=history.id).one()
self.assertEquals(view.changed_field, u'Due Date')
history.last_due_date = None
view = self.store.find(PaymentChangeHistoryView, id=history.id).one()
self.assertEquals(view.changed_field, u'Status')
def test_to_value(self):
payment = self.create_payment()
history = PaymentChangeHistory(payment=payment,
change_reason=u'Teste test test')
view = self.store.find(PaymentChangeHistoryView, id=history.id).one()
self.assertIsNotNone(view.to_value)
history.new_due_date = Date(localtoday())
due_date = converter.as_string(datetime.date, history.new_due_date)
history.new_status = Payment.STATUS_CONFIRMED
view = self.store.find(PaymentChangeHistoryView, id=history.id).one()
self.assertEquals(view.to_value, due_date)
history.new_due_date = None
status = Payment.statuses[history.new_status]
view = self.store.find(PaymentChangeHistoryView, id=history.id).one()
self.assertEquals(view.to_value, 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 _FiscalBookEntryView(Viewable):
book_entry = FiscalBookEntry
id = FiscalBookEntry.id
date = Date(FiscalBookEntry.date)
invoice_number = FiscalBookEntry.invoice_number
cfop_id = FiscalBookEntry.cfop_id
branch_id = FiscalBookEntry.branch_id
drawee_id = FiscalBookEntry.drawee_id
payment_group_id = FiscalBookEntry.payment_group_id
cfop_code = CfopData.code
drawee_name = Person.name
tables = [
FiscalBookEntry,
LeftJoin(Person, Person.id == FiscalBookEntry.drawee_id),
Join(CfopData, CfopData.id == FiscalBookEntry.cfop_id)
]
def open_till(self):
"""Open the till.
It can only be done once per day.
The final cash amount of the previous till will be used
as the initial value in this one after opening it.
"""
if self.status == Till.STATUS_OPEN:
raise TillError(_('Till is already open'))
manager = get_plugin_manager()
# The restriction to only allow opening the till only once per day comes from
# the (soon to be obsolete) ECF devices.
if manager.is_active('ecf'):
# Make sure that the till has not been opened today
today = localtoday().date()
if not self.store.find(
Till,
And(
Date(Till.opening_date) >= today, Till.station_id
== self.station.id)).is_empty():
raise TillError(_("A till has already been opened today"))
last_till = self._get_last_closed_till()
if last_till:
if not last_till.closing_date:
raise TillError(_("Previous till was not closed"))
initial_cash_amount = last_till.final_cash_amount
else:
initial_cash_amount = 0
self.initial_cash_amount = initial_cash_amount
self.opening_date = TransactionTimestamp()
self.status = Till.STATUS_OPEN
self.responsible_open = get_current_user(self.store)
assert self.responsible_open is not None
TillOpenedEvent.emit(self)
from stoq.gui.shell.shellapp import ShellApp
class FilterItem(object):
def __init__(self, name, value=None):
self.name = name
self.value = value
self.id = value
SALES_FILTERS = {
'sold':
Sale.status == Sale.STATUS_CONFIRMED,
'sold-today':
And(
Date(Sale.open_date) == date.today(),
Sale.status == Sale.STATUS_CONFIRMED),
'sold-7days':
And(
Date(Sale.open_date) <= date.today(),
Date(Sale.open_date) >= date.today() - relativedelta(days=7),
Sale.status == Sale.STATUS_CONFIRMED),
'sold-28days':
And(
Date(Sale.open_date) <= date.today(),
Date(Sale.open_date) >= date.today() - relativedelta(days=28),
Sale.status == Sale.STATUS_CONFIRMED),
'expired-quotes':
And(
Date(Sale.expire_date) < date.today(),
Sale.status == Sale.STATUS_QUOTE),
def _get_other_documents(self):
return self.store.find(
ECFDocumentHistory,
And(
Date(ECFDocumentHistory.emission_date) == self.start,
ECFDocumentHistory.printer_id == self.printer.id))
def _get_total_paid_payment(self):
"""Returns the total of payments of the day"""
payments = self.store.find(Payment,
Date(Payment.paid_date) == localtoday())
return payments.sum(Payment.paid_value) or 0
from stoqlib.lib.translation import stoqlib_gettext as _
from stoqlib.reporting.sale import SalesReport
from stoq.gui.shell.shellapp import ShellApp
class FilterItem(object):
def __init__(self, name, value=None):
self.name = name
self.value = value
self.id = value
SALES_FILTERS = {
'sold': Sale.status == Sale.STATUS_CONFIRMED,
'sold-today': And(Date(Sale.open_date) == date.today(),
Sale.status == Sale.STATUS_CONFIRMED),
'sold-7days': And(Date(Sale.open_date) <= date.today(),
Date(Sale.open_date) >= date.today() - relativedelta(days=7),
Sale.status == Sale.STATUS_CONFIRMED),
'sold-28days': And(Date(Sale.open_date) <= date.today(),
Date(Sale.open_date) >= date.today() - relativedelta(days=28),
Sale.status == Sale.STATUS_CONFIRMED),
'expired-quotes': And(Date(Sale.expire_date) < date.today(),
Sale.status == Sale.STATUS_QUOTE),
}
class SalesApp(ShellApp):
app_title = _('Sales')
def _get_z_reductions(self):
return self.store.find(
FiscalDayHistory,
And(
Date(FiscalDayHistory.emission_date) == self.start,
FiscalDayHistory.serial == self.printer.device_serial))
def __init__(self, filename, store, start_date, end_date=None):
self.start_date = start_date
self.end_date = end_date
query = And(Payment.status == Payment.STATUS_PAID,
Date(Payment.paid_date) == Date(start_date))
# Keys are the sale objects, and values are lists with all payments
self.sales = {}
# Keys are the returned sale objects, and values are lists with all payments
self.return_sales = {}
self.purchases = {}
# lonely input and output payments
self.lonely_in_payments = []
self.lonely_out_payments = []
# values are lists with the first element the summary of the input, and
# the second the summary of the output
self.method_summary = {}
self.card_summary = {}
for p in store.find(InPaymentView, query).order_by(Payment.identifier):
if p.sale:
sale_payments = self.sales.setdefault(p.sale, [])
sale_payments.append(p)
else:
self.lonely_in_payments.append(p)
self.method_summary.setdefault(p.method, [0, 0])
self.method_summary[p.method][0] += p.value
if p.card_data:
type_desc = p.card_data.short_desc[p.card_data.card_type]
key = (p.card_data.provider.short_name, type_desc)
self.card_summary.setdefault(key, 0)
self.card_summary[key] += p.value
for p in store.find(OutPaymentView,
query).order_by(Payment.identifier):
if p.purchase:
purchase_payments = self.purchases.setdefault(p.purchase, [])
purchase_payments.append(p)
elif p.sale:
return_sales_payment = self.return_sales.setdefault(p.sale, [])
return_sales_payment.append(p)
else:
self.lonely_out_payments.append(p)
self.method_summary.setdefault(p.method, [0, 0])
self.method_summary[p.method][1] += p.value
# Till removals
query = And(Eq(TillEntry.payment_id, None),
Date(TillEntry.date) == Date(start_date),
TillEntry.value < 0)
self.till_removals = store.find(TillEntry, query)
# Till supply
query = And(Eq(TillEntry.payment_id, None),
Date(TillEntry.date) == Date(start_date),
TillEntry.value > 0)
self.till_supplies = store.find(TillEntry, query)
HTMLReport.__init__(self, filename)
def _parse_date_state(self, state, table_field):
if state.date:
return Date(table_field) == Date(state.date)
