Example #1
0
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
Example #2
0
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
Example #3
0
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
Example #4
0
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