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)
