def auto_remove_waitinglist(dbo):
"""
Finds and automatically marks entries removed that have gone past
the last contact date + weeks.
"""
l = dbo.locale
rows = db.query(dbo, "SELECT a.ID, a.DateOfLastOwnerContact, " \
"a.AutoRemovePolicy " \
"FROM animalwaitinglist a WHERE a.DateRemovedFromList Is Null " \
"AND AutoRemovePolicy > 0 AND DateOfLastOwnerContact Is Not Null")
updates = []
for r in rows:
xdate = add_days(r["DATEOFLASTOWNERCONTACT"], 7 * r["AUTOREMOVEPOLICY"])
if after(now(dbo.timezone), xdate):
al.debug("auto removing waitinglist entry %d due to policy" % int(r["ID"]), "waitinglist.auto_remove_waitinglist", dbo)
updates.append((now(dbo.timezone), _("Auto removed due to lack of owner contact.", l), r["ID"]))
if len(updates) > 0:
db.execute_many(dbo, "UPDATE animalwaitinglist SET DateRemovedFromList = %s, " \
"ReasonForRemoval=%s WHERE ID=%s", updates)
def auto_remove_waitinglist(dbo):
"""
Finds and automatically marks entries removed that have gone past
the last contact date + weeks.
"""
l = dbo.locale
rows = dbo.query("SELECT a.ID, a.DateOfLastOwnerContact, " \
"a.AutoRemovePolicy " \
"FROM animalwaitinglist a WHERE a.DateRemovedFromList Is Null " \
"AND AutoRemovePolicy > 0 AND DateOfLastOwnerContact Is Not Null")
updates = []
for r in rows:
xdate = add_days(r.DATEOFLASTOWNERCONTACT, 7 * r.AUTOREMOVEPOLICY)
if after(now(dbo.timezone), xdate):
al.debug("auto removing waitinglist entry %d due to policy" % r.ID,
"waitinglist.auto_remove_waitinglist", dbo)
updates.append((now(dbo.timezone),
_("Auto removed due to lack of owner contact.",
l), r.ID))
if len(updates) > 0:
dbo.execute_many("UPDATE animalwaitinglist SET DateRemovedFromList = ?, " \
"ReasonForRemoval=? WHERE ID=?", updates)
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
