def select_taxable_transactions(self, start=None, end=None):
return [
trans
for trans in self
if (start is None or end is None or start < decode(trans["date"]) <= end)
and decode(trans["external"])
and trans["type"] == "Income"
]
def dues(self, members, collector, amount=100.0, plan=None, bank_id="Cash", effective_date=None, date=None, test="",
prorated=False, rounded=False, fees=(), **kwargs):
other_members = []
dues_amount = amount - sum(fee[0] for fee in fees)
if isinstance(members, (tuple, list)):
primary_member = members[0]
other_members = members[1:]
else:
primary_member = members
if effective_date is None:
query = "select effective_until, plan from {domain} where counter_party = '{primary_member}' and subtype='Dues'".format(
domain=domain.name, primary_member=primary_member.replace("'", "''"))
if plan is not None:
query += "and plan='{plan}'".format(plan=plan)
rs = domain.select(query, consistent_read=True)
try:
lastDues = max((result for result in rs), key=lambda result: decode(result['effective_until']))
effective_date, plan = decode(lastDues['effective_until']), lastDues['plan']
plan = str(plan)
except ValueError:
if date is not None:
effective_date = date
else:
raise
if date is None:
date = effective_date.date()
dependants = len(other_members)
dependantPlan = self.membership_plans["Dependant" if dependants else "Null"]
effectivePlan = self.membership_plans[plan]
effectiveRate = effectivePlan.rate + dependants * dependantPlan.rate
if not(prorated or rounded):
assert dues_amount % effectiveRate == 0, "Inexact dues, you must specify prorated=True or rounded=True"
if effectivePlan.period == SEMESTER:
if effective_date.month <= 5:
effective_until = effective_date.replace(month=8, day=31)
elif effective_date.month <= 8:
effective_until = effective_date.replace(month=12, day=31)
else: #if effective_date.month <= 12:
effective_until = effective_date.replace(year=effective_date.year + 1, month=5, day=31)
else:
if rounded:
effective_until = effective_date + timedelta(
days=effectivePlan.period * round(dues_amount / effectiveRate, 0))
else:
effective_until = effective_date + timedelta(days=effectivePlan.period * dues_amount / effectiveRate)
if len(other_members):
raise NotImplementedError("Family membership needs to be revised/fixed")
self.add(counter_party=primary_member, agent=collector, amount=amount, subtype="Dues", bank_id=bank_id,
effective_date=effective_date, effective_until=effective_until, plan=plan, test=test, date=date,
budget_account="Dues:" + plan, fees=fees, append_event=False, **kwargs)
def _mk_balance_group(depth, group):
if group in ("month", "effective_month"):
column = "effective_date" if group == "effective_month" else "date"
l = lambda result: decode(result[column]).replace(day=1, hour=0, minute=0, second=0, microsecond=0,
tzinfo=None)
elif group in ("day", "effective_day"):
column = "effective_date" if group == "effective_month" else "date"
l = lambda result: decode(result[column]).replace(hour=0, minute=0, second=0, microsecond=0,
tzinfo=None)
else:
column = group
l = (lambda result: ":".join(result[column].split(":")[0:depth])) if depth >= 0 else (
lambda result: result[column])
return column, l
def tax(self, where=""):
query = "select tax_inclusive from {domain} where tax_inclusive is not null".format(domain=self.domain.name)
if where:
query += " and " + where
rs = self._select(query)
ret = sum(decode(transaction['tax_inclusive']) for transaction in rs)
return (ret * self.tax_rate) / (1 + self.tax_rate)
def balance(self, where=""):
if where:
query = "select * from {domain} where {wheres}".format(domain=self.domain.name, wheres=where)
else:
query = "select * from {domain}".format(domain=self.domain.name)
rs = self._select(query)
return sum(decode(transaction["amount"]) for transaction in rs)
def select(self, before=None, external=None, state=None):
if before is None:
dateTest = lambda x: True
else:
dateTest = (
lambda entry: decode(entry["effective_date"]) < before
or decode(entry["entered"]) < before
or decode(entry["date"]) < before
)
return [
entry
for entry in self
if dateTest(entry)
and (state is None or entry["state"] == state)
and (external is None or entry["external"] == external)
]
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
:param columns: columns that must be non-null
:return: list of transactions
"""
if where != "":
raise NotImplementedError, "DictLedger does not support adhoc where clauses"
return [
transaction
for transaction in self
if all(column in transaction for column in columns)
and (external is None or bool(decode(transaction["external"])) == external)
and (effective_date_after is None or decode(transaction["effective_date"]) >= effective_date_after)
and (effective_date_before is None or decode(transaction["effective_date"]) < effective_date_before)
]
def member_report(ledger, max_days=90, asof_date=None):
if asof_date is None:
asof_date = datetime.now()
ret = "\nMembers\n"
writer = csv.writer(open("member_list.csv", "w"))
writer.writerow(("member_id", "name", "plan", "start", "end"))
ret += "Name\t\tPlan\tMember Until\n"
for member in sorted(ledger.member_list(), key=lambda member: member[2]):
member_id, name, plan, last_payment, last_bank_id, last_bank_acct, start, end = member
if decode(start) > asof_date:
continue
if decode(end) < asof_date - timedelta(days=max_days):
continue
writer.writerow(member)
if decode(end) < asof_date:
plan = "Expired " + plan
ret += "{name}\t{plan}\t{end}\n".format(name=name, plan=plan, end=end)
return ret
def format_entry(entry, verbose=False):
"""
Format entry in a standard, readable way.
* if not verbose, pop fields that are generally unnecessary
* unknown fields are pretty printed as a best effort fallback
"""
ret = unicode(entry.name)
entry = dict(entry)
posted = entry.pop('posted')
ret += "\t" + entry.pop('state')
if posted:
ret += "\t" + str(decode(posted).date()) + "\t" + entry.pop('checksum', "MISSING CHECKSUM")
ret += "\n"
flags = ("E" if decode(entry['external']) else ("T" if entry.pop("transfer_id", False) else "I"))
account = entry.pop("bank_account") + "\t" + entry.pop("budget_account")
ret += u"\t{date}\t{amount}\t{flags}\t{account}\t{type}:{subtype}\t{cpty}\t{agent}".format(
amount=entry.pop("amount")
,
account=account, cpty=entry.pop("counter_party", "Internal" if not decode(entry.pop("external")) else "ERROR"),
agent=entry.pop("agent"),
flags=flags, type=entry.pop("type"), subtype=entry.pop("subtype"),
date=decode(entry.pop("effective_date")).date())
if 'notes' in entry:
ret += "\n\t" + entry.pop('notes')
if not verbose:
entry.pop("test")
entry.pop("tax_inclusive")
entry.pop('entered')
entry.pop('modified')
entry.pop("bank_id")
entry.pop("checksum_version", "")
entry.pop("date")
entry.pop("effective_until", "")
entry.pop("plan", "")
entry.pop("event", "")
entry.pop("fee_for", '')
entry.pop("agent_id", "")
entry.pop("counter_party_id", "")
if not entry:
return ret
return ret + "\n" + pformat(entry)
def balances(self, group_by='bank_account', depth=-1, where=""):
"""
"""
ret = OrderedDict()
if not isinstance(group_by, (tuple, list)):
group_by = [group_by, ]
#Create special Rounding functions in the same order as the group bys
#These are needed to round dates down to months
#also crease columns for the query below
columns = []
roundingFuncs = []
for group in group_by:
column, l = self._mk_balance_group(depth, group)
columns.append(column)
roundingFuncs.append(l)
if where:
where += " and " + " and ".join(x + " is not null" for x in columns)
else:
where = " and ".join(x + " is not null" for x in columns)
# 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)
# keyfunc for both sorting and grouping is to use the rounding functions
keyfunc = lambda result: tuple(rnd(result) for rnd in roundingFuncs)
for group_key, transactions in groupby(sorted(rs, key=keyfunc), keyfunc):
total = sum(decode(transaction['amount']) for transaction in transactions)
if total:
if group_by[-1] == "bank_account":
net_key = group_key[0:-1]+("net",)
net = ret.get(net_key,0)
net += total
ret[net_key] = net
if not group_key[-1].endswith(FOUNDERS_LOAN):
net_key = group_key[0:-1]+("operating net",)
net = ret.get(net_key,0)
net += total
ret[net_key] = net
ret[group_key] = total
return ret
def balances(
self,
group_by="bank_account",
depth=-1,
external=None,
effective_date_after=None,
effective_date_before=None,
where="",
):
"""
"""
ret = OrderedDict()
if not isinstance(group_by, (tuple, list)):
group_by = [group_by]
# Create special Rounding functions in the same order as the group bys
# These are needed to round dates down to months
# also creates columns for the query below
columns = []
roundingFuncs = []
for group in group_by:
column, l = self._mk_balance_group(depth, group)
columns.append(column)
roundingFuncs.append(l)
rs = self.select_non_empty(
columns,
effective_date_after=effective_date_after,
effective_date_before=effective_date_before,
external=external,
where=where,
)
# keyfunc for both sorting and grouping is to use the rounding functions
keyfunc = lambda result: tuple(rnd(result) for rnd in roundingFuncs)
for group_key, transactions in groupby(sorted(rs, key=keyfunc), keyfunc):
total = sum(decode(transaction["amount"]) for transaction in transactions)
if total:
if group_by[-1] == "bank_account":
net_key = group_key[0:-1] + ("net",)
net = ret.get(net_key, 0)
net += total
ret[net_key] = net
if not group_key[-1].endswith(FOUNDERS_LOAN):
net_key = group_key[0:-1] + ("operating net",)
net = ret.get(net_key, 0)
net += total
ret[net_key] = net
ret[group_key] = total
return ret
def is_member(self, member, date, plan=None):
if plan is None:
return len(
[
trans
for trans in self
if trans["subtype"] == "Dues"
and trans["counter_party"] == member
and decode(trans["effective_date"]) <= date <= decode(trans["effective_until"])
]
)
return len(
[
trans
for trans in self
if trans["subtype"] == "Dues"
and trans["counter_party"] == member
and decode(trans["effective_date"]) <= date <= decode(trans["effective_until"])
and trans["plan"] == plan
]
)
def member_stats(ledger, format='text'):
#writer.writerow(("member_id", "name", "plan", "start", "end"))
#ret += "Name\t\tPlan\tMember Until\n"
memberList = ledger.member_list()
interestingDates = {}
for member in memberList:
member_id, name, plan, last_payment, last_bank_id, last_bank_acct, start, end = member
interestingDates[decode(start)] = Counter(dict(zip(ledger.membership_plans.keys(),[0]*len(ledger.membership_plans))))
interestingDates[decode(end)] = Counter(dict(zip(ledger.membership_plans.keys(),[0]*len(ledger.membership_plans))))
for member in memberList:
member_id, name, plan, last_payment, last_bank_id, last_bank_acct, start, end = member
for dateOfInterest, counts in interestingDates.iteritems():
if dateOfInterest<=decode(end) and dateOfInterest>= decode(start):
counts[plan]+=1
buffer = StringIO()
fields = ["Date"]+ledger.membership_plans.keys()
writer = DictWriter(buffer, fieldnames=fields,quoting=csv.QUOTE_ALL)
writer.writerow(dict(zip(fields,fields)))
for dateOfInterest, counts in sorted(interestingDates.iteritems()):
row = dict(counts)
row["Date"] = dateOfInterest
writer.writerow(row)
return buffer.getvalue()
def entities(ledger):
report = ""
members = set()
students = set()
vendors = set()
agents = set()
customers = set()
for transaction in ledger:
if decode(transaction['amount']) == 0:
continue
if transaction['subtype'] == "Dues":
if transaction['plan'] == "Class Only":
students.add((transaction['counter_party_id'],transaction['counter_party']))
else:
members.add((transaction['counter_party_id'],transaction['counter_party']))
elif 'transfer_id' in transaction:# transaction['subtype'] in ["Bank Transfer","Budget Transfer", "Reimbursement"]:
pass
elif transaction['type'] == "Income":
customers.add((transaction['counter_party_id'],transaction['counter_party']))
elif transaction['type'] == "Expense" and transaction['subtype'] != "Refund":
vendors.add((transaction['counter_party_id'],transaction['counter_party']))
agents.add((transaction['agent_id'],transaction['agent']))
leaders = members.intersection(agents)
customers.difference_update(members)
customers.difference_update(students)
nonMemberAgents = agents.difference(members)
for section, entities in [("Members",members),
("Students",students),
("Non-Student Customers",customers),
("Vendors",vendors),
("Leaders",leaders),
("Non-member Agents",nonMemberAgents)]:
report += section + "\n-------\n"+"\n".join("%s:%s"%(id,name) for id,name in entities) + "\n\n"
return report
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 select(self, before, state=None):
dateTest = lambda entry: decode(entry["effective_date"]) < before or decode(
entry["entered"]) < before or decode(entry["date"]) < before
if state is None:
return [entry for entry in self if dateTest(entry)]
return [entry for entry in self if dateTest(entry) and entry['state'] == state]
else:
print report
elif opt.command == "update-todos":
user_email = config.get('toodledo', 'username')
password = config.get('toodledo', 'password')
app_id = config.get('toodledo', 'id')
app_token = config.get('toodledo', 'token')
client = ApiClient(app_id=app_id, app_token=app_token)#, cache_xml=True)
client.authenticate(user_email, password)
# config.set('cache','user_token',str(cached_client._key))
# store_config(config)
for member in sorted(ledger.member_list(), key=lambda member: member[2]):
member_id, name, plan, last_payment, last_bank_id, last_bank_acct, start, end = member
start, end = decode(start), decode(end)
# if end < datetime.now():
# plan = "Expired " + plan
taskName = name + " dues reminder"
task = None
try:
task = client.getTask(taskName)
while task.completed:
print "Deleting Completed reminder: %s "%taskName
client.deleteTask(task.id)
task = client.getTask(taskName)
if task:
print "Found task: %s "%taskName
except PoodledoError:
task = None
def tax(self, where=""):
rs = self.select_taxable_transactions()
ret = sum(decode(transaction["tax_inclusive"]) for transaction in rs)
return (ret * self.tax_rate) / (1 + self.tax_rate)