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)