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)