def get_accounts(dbo):
"""
Returns all of the accounts with reconciled/balance figures
ID, CODE, DESCRIPTION, ACCOUNTTYPE, DONATIONTYPEID, RECONCILED, BALANCE, VIEWROLEIDS, VIEWROLES, EDITROLEIDS, EDITROLES
If an accounting period has been set, balances are calculated from that point
"""
l = dbo.locale
pfilter = ""
aperiod = configuration.accounting_period(dbo)
if aperiod != "":
pfilter = " AND TrxDate >= " + db.dd(i18n.display2python(l, aperiod))
roles = db.query(
dbo,
"SELECT ar.*, r.RoleName FROM accountsrole ar INNER JOIN role r ON ar.RoleID = r.ID"
)
accounts = db.query(dbo, "SELECT a.*, at.AccountType AS AccountTypeName, " \
"dt.DonationName, " \
"(SELECT SUM(Amount) FROM accountstrx WHERE DestinationAccountID = a.ID%s) AS dest," \
"(SELECT SUM(Amount) FROM accountstrx WHERE SourceAccountID = a.ID%s) AS src," \
"(SELECT SUM(Amount) FROM accountstrx WHERE Reconciled = 1 AND DestinationAccountID = a.ID%s) AS recdest," \
"(SELECT SUM(Amount) FROM accountstrx WHERE Reconciled = 1 AND SourceAccountID = a.ID%s) AS recsrc " \
"FROM accounts a " \
"INNER JOIN lksaccounttype at ON at.ID = a.AccountType " \
"LEFT OUTER JOIN donationtype dt ON dt.ID = a.DonationTypeID " \
"ORDER BY a.AccountType, a.Code" % (pfilter, pfilter, pfilter, pfilter))
for a in accounts:
dest = a["DEST"]
src = a["SRC"]
recdest = a["RECDEST"]
recsrc = a["RECSRC"]
if dest is None: dest = 0
if src is None: src = 0
if recdest is None: recdest = 0
if recsrc is None: recsrc = 0
a["BALANCE"] = dest - src
a["RECONCILED"] = recdest - recsrc
if a["ACCOUNTTYPE"] == INCOME or a["ACCOUNTTYPE"] == EXPENSE:
a["BALANCE"] = abs(a["BALANCE"])
a["RECONCILED"] = abs(a["RECONCILED"])
viewroleids = []
viewrolenames = []
editroleids = []
editrolenames = []
for r in roles:
if r["ACCOUNTID"] == a["ID"] and r["CANVIEW"] == 1:
viewroleids.append(str(r["ROLEID"]))
viewrolenames.append(str(r["ROLENAME"]))
if r["ACCOUNTID"] == a["ID"] and r["CANEDIT"] == 1:
editroleids.append(str(r["ROLEID"]))
editrolenames.append(str(r["ROLENAME"]))
a["VIEWROLEIDS"] = "|".join(viewroleids)
a["VIEWROLES"] = "|".join(viewrolenames)
a["EDITROLEIDS"] = "|".join(editroleids)
a["EDITROLES"] = "|".join(editrolenames)
return accounts
def get_accounts(dbo):
"""
Returns all of the accounts with reconciled/balance figures
ID, CODE, DESCRIPTION, ACCOUNTTYPE, DONATIONTYPEID, RECONCILED, BALANCE, VIEWROLEIDS, VIEWROLES, EDITROLEIDS, EDITROLES
If an accounting period has been set, balances are calculated from that point
"""
l = dbo.locale
pfilter = ""
aperiod = configuration.accounting_period(dbo)
if aperiod != "":
pfilter = " AND TrxDate >= " + db.dd(i18n.display2python(l, aperiod))
roles = db.query(dbo, "SELECT ar.*, r.RoleName FROM accountsrole ar INNER JOIN role r ON ar.RoleID = r.ID")
accounts = db.query(dbo, "SELECT a.*, at.AccountType AS AccountTypeName, " \
"dt.DonationName, " \
"(SELECT SUM(Amount) FROM accountstrx WHERE DestinationAccountID = a.ID%s) AS dest," \
"(SELECT SUM(Amount) FROM accountstrx WHERE SourceAccountID = a.ID%s) AS src," \
"(SELECT SUM(Amount) FROM accountstrx WHERE Reconciled = 1 AND DestinationAccountID = a.ID%s) AS recdest," \
"(SELECT SUM(Amount) FROM accountstrx WHERE Reconciled = 1 AND SourceAccountID = a.ID%s) AS recsrc " \
"FROM accounts a " \
"INNER JOIN lksaccounttype at ON at.ID = a.AccountType " \
"LEFT OUTER JOIN donationtype dt ON dt.ID = a.DonationTypeID " \
"ORDER BY a.AccountType, a.Code" % (pfilter, pfilter, pfilter, pfilter))
for a in accounts:
dest = a["DEST"]
src = a["SRC"]
recdest = a["RECDEST"]
recsrc = a["RECSRC"]
if dest is None: dest = 0
if src is None: src = 0
if recdest is None: recdest = 0
if recsrc is None: recsrc = 0
a["BALANCE"] = dest - src
a["RECONCILED"] = recdest - recsrc
if a["ACCOUNTTYPE"] == INCOME or a["ACCOUNTTYPE"] == EXPENSE:
a["BALANCE"] = abs(a["BALANCE"])
a["RECONCILED"] = abs(a["RECONCILED"])
viewroleids = []
viewrolenames = []
editroleids = []
editrolenames = []
for r in roles:
if r["ACCOUNTID"] == a["ID"] and r["CANVIEW"] == 1:
viewroleids.append(str(r["ROLEID"]))
viewrolenames.append(str(r["ROLENAME"]))
if r["ACCOUNTID"] == a["ID"] and r["CANEDIT"] == 1:
editroleids.append(str(r["ROLEID"]))
editrolenames.append(str(r["ROLENAME"]))
a["VIEWROLEIDS"] = "|".join(viewroleids)
a["VIEWROLES"] = "|".join(viewrolenames)
a["EDITROLEIDS"] = "|".join(editroleids)
a["EDITROLES"] = "|".join(editrolenames)
return accounts
def get_transactions(dbo, accountid, datefrom, dateto, reconciled):
"""
Gets a list of transactions for the account given, between
two python dates. PERSONID and PERSONNAME are returned for
linked donations.
accountid: Account ID as integer
datefrom: Python from date
dateto: Python to date
reconciled: one of RECONCILED, NONRECONCILED or BOTH to filter
It creates extra columns, THISACCOUNT and OTHERACCOUNT
for use by the UI when displaying transactions. It also adds
THISACCOUNTCODE and OTHERACCOUNTCODE for display purposes,
the BALANCE column and WITHDRAWAL and DEPOSIT.
"""
l = dbo.locale
period = configuration.accounting_period(dbo)
if not configuration.account_period_totals(dbo):
period = ""
# If we have an accounting period set and it's after the from date,
# use that instead
if period != "" and i18n.after(i18n.display2python(l, period), datefrom):
datefrom = i18n.display2python(l, period)
recfilter = ""
if reconciled == RECONCILED:
recfilter = " AND Reconciled = 1"
elif reconciled == NONRECONCILED:
recfilter = " AND Reconciled = 0"
rows = db.query(dbo, "SELECT t.*, srcac.Code AS SrcCode, destac.Code AS DestCode, " \
"o.OwnerName AS PersonName, o.ID AS PersonID, a.ID AS AnimalID, " \
"a.AnimalName AS AnimalName, " \
"CASE " \
"WHEN 'Yes' = (SELECT ItemValue FROM configuration WHERE ItemName Like 'UseShortShelterCodes') " \
"THEN a.ShelterCode ELSE a.ShortCode END AS AnimalCode " \
"FROM accountstrx t " \
"INNER JOIN accounts srcac ON srcac.ID = t.SourceAccountID " \
"INNER JOIN accounts destac ON destac.ID = t.DestinationAccountID " \
"LEFT OUTER JOIN ownerdonation od ON od.ID = t.OwnerDonationID " \
"LEFT OUTER JOIN owner o ON o.ID = od.OwnerID " \
"LEFT OUTER JOIN animal a ON a.ID = od.AnimalID " \
"WHERE t.TrxDate >= %s AND t.TrxDate <= %s%s " \
"AND (t.SourceAccountID = %d OR t.DestinationAccountID = %d) " \
"ORDER BY t.TrxDate" % ( db.dd(datefrom), db.dd(dateto), recfilter, int(accountid), int(accountid)))
balance = 0
if period != "":
balance = get_balance_fromto_date(dbo, accountid, i18n.display2python(l, period), datefrom)
else:
balance = get_balance_to_date(dbo, accountid, datefrom)
for r in rows:
# Error scenario - this account is both source and destination
if r["SOURCEACCOUNTID"] == accountid and r["DESTINATIONACCOUNTID"] == accountid:
r["WITHDRAWAL"] = 0
r["DEPOSIT"] = 0
r["THISACCOUNT"] = accountid
r["THISACCOUNTCODE"] = r["SRCCODE"]
r["OTHERACCOUNT"] = accountid
r["OTHERACCOUNTCODE"] = "<-->"
r["BALANCE"] = balance
# This account is the source - it's a withdrawal
elif r["SOURCEACCOUNTID"] == accountid:
r["WITHDRAWAL"] = r["AMOUNT"]
r["DEPOSIT"] = 0
r["OTHERACCOUNT"] = r["DESTINATIONACCOUNTID"]
r["OTHERACCOUNTCODE"] = r["DESTCODE"]
r["THISACCOUNT"] = accountid
r["THISACCOUNTCODE"] = r["SRCCODE"]
balance -= r["AMOUNT"]
r["BALANCE"] = balance
# This account is the destination - it's a deposit
else:
r["WITHDRAWAL"] = 0
r["DEPOSIT"] = r["AMOUNT"]
r["OTHERACCOUNT"] = r["SOURCEACCOUNTID"]
r["OTHERACCOUNTCODE"] = r["SRCCODE"]
r["THISACCOUNT"] = accountid
r["THISACCOUNTCODE"] = r["DESTCODE"]
balance += r["AMOUNT"]
r["BALANCE"] = balance
return rows
def get_transactions(dbo, accountid, datefrom, dateto, reconciled):
"""
Gets a list of transactions for the account given, between
two python dates. PERSONID and PERSONNAME are returned for
linked donations.
accountid: Account ID as integer
datefrom: Python from date
dateto: Python to date
reconciled: one of RECONCILED, NONRECONCILED or BOTH to filter
It creates extra columns, THISACCOUNT and OTHERACCOUNT
for use by the UI when displaying transactions. It also adds
THISACCOUNTCODE and OTHERACCOUNTCODE for display purposes,
the BALANCE column and WITHDRAWAL and DEPOSIT.
"""
l = dbo.locale
period = configuration.accounting_period(dbo)
if not configuration.account_period_totals(dbo):
period = ""
# If we have an accounting period set and it's after the from date,
# use that instead
if period != "" and i18n.after(i18n.display2python(l, period), datefrom):
datefrom = i18n.display2python(l, period)
recfilter = ""
if reconciled == RECONCILED:
recfilter = " AND Reconciled = 1"
elif reconciled == NONRECONCILED:
recfilter = " AND Reconciled = 0"
rows = db.query(dbo, "SELECT t.*, srcac.Code AS SrcCode, destac.Code AS DestCode, " \
"o.OwnerName AS PersonName, o.ID AS PersonID, a.ID AS AnimalID, " \
"a.AnimalName AS AnimalName, " \
"CASE " \
"WHEN 'Yes' = (SELECT ItemValue FROM configuration WHERE ItemName Like 'UseShortShelterCodes') " \
"THEN a.ShelterCode ELSE a.ShortCode END AS AnimalCode " \
"FROM accountstrx t " \
"INNER JOIN accounts srcac ON srcac.ID = t.SourceAccountID " \
"INNER JOIN accounts destac ON destac.ID = t.DestinationAccountID " \
"LEFT OUTER JOIN ownerdonation od ON od.ID = t.OwnerDonationID " \
"LEFT OUTER JOIN owner o ON o.ID = od.OwnerID " \
"LEFT OUTER JOIN animal a ON a.ID = od.AnimalID " \
"WHERE t.TrxDate >= %s AND t.TrxDate <= %s%s " \
"AND (t.SourceAccountID = %d OR t.DestinationAccountID = %d) " \
"ORDER BY t.TrxDate" % ( db.dd(datefrom), db.dd(dateto), recfilter, int(accountid), int(accountid)))
balance = 0
if period != "":
balance = get_balance_fromto_date(dbo, accountid,
i18n.display2python(l, period),
datefrom)
else:
balance = get_balance_to_date(dbo, accountid, datefrom)
for r in rows:
# Error scenario - this account is both source and destination
if r["SOURCEACCOUNTID"] == accountid and r[
"DESTINATIONACCOUNTID"] == accountid:
r["WITHDRAWAL"] = 0
r["DEPOSIT"] = 0
r["THISACCOUNT"] = accountid
r["THISACCOUNTCODE"] = r["SRCCODE"]
r["OTHERACCOUNT"] = accountid
r["OTHERACCOUNTCODE"] = "<-->"
r["BALANCE"] = balance
# This account is the source - it's a withdrawal
elif r["SOURCEACCOUNTID"] == accountid:
r["WITHDRAWAL"] = r["AMOUNT"]
r["DEPOSIT"] = 0
r["OTHERACCOUNT"] = r["DESTINATIONACCOUNTID"]
r["OTHERACCOUNTCODE"] = r["DESTCODE"]
r["THISACCOUNT"] = accountid
r["THISACCOUNTCODE"] = r["SRCCODE"]
balance -= r["AMOUNT"]
r["BALANCE"] = balance
# This account is the destination - it's a deposit
else:
r["WITHDRAWAL"] = 0
r["DEPOSIT"] = r["AMOUNT"]
r["OTHERACCOUNT"] = r["SOURCEACCOUNTID"]
r["OTHERACCOUNTCODE"] = r["SRCCODE"]
r["THISACCOUNT"] = accountid
r["THISACCOUNTCODE"] = r["DESTCODE"]
balance += r["AMOUNT"]
r["BALANCE"] = balance
return rows