def select_non_empty(self, columns, external=None, effective_date_after=None, effective_date_before=None, where=""): """ Select transactions where the given columns are not empty will be sorted by the first column :param columns: columns that must be non-null :param where: existing where clause if any :return: list of transactions """ wheres = [] if where: wheres = [where] if external is not None: wheres.append("external='%s'" % external) if effective_date_after is not None: wheres.append("effective_date >= '{date}'".format(date=encode(effective_date_after))) if effective_date_before is not None: wheres.append("effective_date < '{date}'".format(date=encode(effective_date_before))) wheres.extend(x + " is not null" for x in columns) where = " and ".join(wheres) # for filter, value in filter_by.iteritems(): # if isinstance(value,(tuple,list)): # where += "and {field} in ({values})".format(field= filter, values = ",".join('{value}'.format(value = x) for x in value)) # else: # where += "and {field} in ({values})".format(field= filter, values = ",".join('{value}'.format(value = x) for x in value)) query = "select * from {domain} where {wheres} order by {group_by}".format( domain=self.domain.name, wheres=where, group_by=columns[0] ) rs = self._select(query) return rs
def select_taxable_transactions(self, start=None, end=None): query = """select amount, tax_inclusive from {domain} where type = 'Income' and external = 'True'""".format( domain=self.domain.name ) if start is not None: query += """and date between '{start}' and '{end}'""".format( domain=self.domain.name, start=encode(start), end=encode(end, epsilon=True) ) rs = self._select(query) return rs
def post_transactions(self, transactions): toPost = [] postingTime = encode(datetime.now()) # final check, transactions already posted or in the "Hold" state may not be posted for transaction in transactions: entry = self._get_entry(transaction) if entry["posted"]: continue if entry["state"] == "Hold": continue toPost.append(entry) for entry in toPost: entry["checksum"] = self.calculate_checksum(entry) entry["checksum_version"] = 1 entry["posted"] = postingTime entry["state"] = "Posted" make_posting_reports(postingTime, toPost) for entry in toPost: entry.save(replace=True) return postingTime
def calculate_checksum(self, entry): try: return hashlib.sha256( ",".join(encode(entry[item]) for item in self.checksum_fields if item in entry)).hexdigest() except: print "Failed to checksum", entry raise
def set_state(self, entry_or_id, state): """ Sets the state of the given transaction, managing any details as needed. """ if state == "Posted": raise TypeError("set_state cannot set state to 'Posted' use post_transactions instead.") entry = self._get_entry(entry_or_id, consistent_read=True) entry["state"] = state entry["modified"] = encode(datetime.now()) entry.save()
def is_member(self, member, date, plan=None): query = u"select effective_until, plan from {domain} where counter_party = '{member}' and subtype='Dues' and effective_until>='{date}' and effective_date<='{date} '".format( domain=self.domain.name, member=member, date=encode(date)) if plan is not None: query += "and plan='{plan} '".format(plan=plan) rs = self._select(query) try: rs.next() return True except StopIteration: return False
def cash_flow_report_set(ledger, start, end, account_grouping): ret = {} months = set() end -= timedelta(microseconds=1) inWhere = "effective_date between '{start}' and '{end}'".format(start=encode(start), end=encode(end, epsilon=True)) startWhere = "effective_date < '{start}'".format(start=encode(start), end=encode(end, epsilon=True)) endWhere = "effective_date <= '{end}'".format(start=encode(start), end=encode(end, epsilon=True)) query = """select amount, tax_inclusive from {domain} where type = 'Income' and external = 'True' and date between '{start}' and '{end}'""".format(domain=ledger.domain.name, start=encode(start), end=encode(end, epsilon=True)) rs = ledger._select(query) gross = sum(decode(transaction['amount']) for transaction in rs) tax_inclusive = sum(decode(transaction['tax_inclusive']) for transaction in rs) taxable = tax_inclusive / (1 + ledger.tax_rate) tax = taxable * ledger.tax_rate gross -= tax deductions = gross - taxable ret["Tax"] = "Quarterly Tax Statement\n" ret["Tax"] += "Gross Receipts\tDeductions\tTaxable\tTax Due\n" ret["Tax"] += "\t".join(str(x) for x in [gross, deductions, taxable, tax]) + "\n" ret["Tax"] += "Sales Tax Due this quarter {tax} on {taxable}".format(tax=tax, taxable=taxable) startingBalances = all_balances(ledger, group_by=account_grouping, where=startWhere) startingBalanceReport = format_account_balances(startingBalances) activeBudgetAccounts = set(x[0] for x in startingBalances.keys()) endingBalances = all_balances(ledger, group_by=account_grouping, where=endWhere) endingBalanceReport = format_account_balances(endingBalances) monthlyFlowReport = "Month\tAccount\tAmount\n" monthlyFlow = all_balances(ledger, group_by=('effective_month', account_grouping), where=inWhere) for (month, budgetAccount), amount in monthlyFlow.iteritems(): monthlyFlowReport += "{month}\t{budget_account}\t${amount}\n".format(month=month.strftime("%B %Y"), budget_account=budgetAccount, amount=amount) activeBudgetAccounts.add(budgetAccount) months.add(month) activeBudgetAccounts = sorted(activeBudgetAccounts) #Move total to end activeBudgetAccounts.remove('') activeBudgetAccounts.append('') months = sorted(months) quarterFlow = all_balances(ledger, group_by=account_grouping, where=inWhere) quarterFlowReport = format_account_balances(quarterFlow) flowSummary = OrderedDict() flowSummaryBuffer = StringIO() netFlow = OrderedDict() netFlowBuffer = StringIO() flowSummaryWriter = initialize_writer(["Account", "Start"] + months + ["Net", "End"], flowSummaryBuffer, months) netFlowWriter = initialize_writer(["Account", ] + months + ["Net"], netFlowBuffer, months) for budgetAccount in activeBudgetAccounts: row = {"Account": "\t " * budgetAccount.count(":") + budgetAccount if budgetAccount else "Total", "Net": quarterFlow.get((budgetAccount,), "")} for month in months: row[month] = monthlyFlow.get((month, budgetAccount), "") if row['Net']: netFlow[budgetAccount] = row row = dict(row) row.update({ "Start": startingBalances.get((budgetAccount,), 0), "End": endingBalances.get((budgetAccount,), 0) }) flowSummary[budgetAccount] = row flowSummaryWriter.writerows(flowSummary.itervalues()) netFlowWriter.writerows(netFlow.itervalues()) ret["Flow Summary"] = flowSummaryBuffer.getvalue() ret["Net Flow"] = netFlowBuffer.getvalue() ret["Monthly Net Cash Flow"] = monthlyFlowReport ret["Quarter Net Cash Flow"] = quarterFlowReport ret["Starting Balances"] = startingBalanceReport ret["Ending Balances"] = endingBalanceReport return ret
def add( self, amount, agent, subtype, counter_party=None, event=None, bank_id="Cash", bank_account=None, external=True, date=None, effective_date=None, budget_account=None, test="", income=None, notes="", tax_inclusive=0, fees=(), state="New", append_event=True, **other_fields ): if counter_party is None and external: if event is None: raise TypeError("Either event or counter_party must be specified for external transactions") counter_party = "Event:" + event if income is None: income = amount > 0 if date is None: date = datetime.now() elif isinstance(date, str): date = dateutil.parser.parse(date) if effective_date is None: effective_date = date elif isinstance(effective_date, str): effective_date = dateutil.parser.parse(effective_date) if bank_account is None: if income: if bank_id == "Cash": bank_account = CASH_BOX else: bank_account = PRIMARY_CHECKING else: bank_account = EXPENSE if bank_account in self.agent_account_types: bank_account = ":".join((agent, bank_account)) subtype = subtype.title() if budget_account is None: budget_account = [subtype] elif isinstance(budget_account, str): budget_account = [budget_account] if event and not append_event: budget_account.append(event) budget_account = ":".join(budget_account) if tax_inclusive is True: tax_inclusive = amount # for now other_fields["tax_inclusive"] = tax_inclusive if tax_inclusive > 0: assert external and income, "Tax may only be collected on external sales" item = self._create_item() item["amount"] = encode(Decimal(amount)) item["agent"] = encode(agent) item["agent_id"] = self.get_entity_id(agent) item["counter_party"] = encode(counter_party) item["counter_party_id"] = self.get_entity_id(counter_party) if event: item["event"] = event item["bank_account"] = encode(bank_account) item["budget_account"] = encode(budget_account) item["external"] = encode(external) item["date"] = encode(date) item["effective_date"] = encode(effective_date) item["entered"] = encode(datetime.now()) item["modified"] = encode(datetime.now()) item["type"] = encode("Income" if income else "Expense") item["subtype"] = encode(subtype) item["bank_id"] = unicode(bank_id) item["notes"] = encode(notes) item["test"] = encode(test) item["state"] = encode(state) for key, value in other_fields.iteritems(): item[key] = encode(value) item["posted"] = "" assert amount != 0, "You must be saving a transaction with some amount to it." self._save_item(item) other_fields.pop("tax_inclusive") # fees are not tax inclusive for fee in fees: if len(fee) == 3: fee_amount, fee_cpty, bank_id = fee else: fee_amount, fee_cpty = fee self.add( -fee_amount, agent, subtype="Fees:" + fee_cpty, counter_party=fee_cpty, event=event, bank_id=bank_id, bank_account=bank_account, external=True, date=date, test=test, income=False, fee_for=item.name, **other_fields )
def find_transactions(self, **conditions): where = " and ".join("%s = '%s'" % (field, encode(value)) for field, value in conditions.iteritems()) query = "select * from {domain} where {wheres}".format(domain=self.domain.name, wheres=where) rs = self._select(query) return list(rs)