Account Summary Report

Page 1 of 1
5 posts
Account Summary Report

siena123
MMEX Developer

Posts: 12
Joined: Wed Jan 23, 2013 7:00 pm
  • SQL

  • Code: Select all
    select a.ACCOUNTNAME, c.BASECONVRATE, c.PFX_SYMBOL, c.SFX_SYMBOL,
        (select INITIALBAL + total(TRANSAMOUNT)
        from
            (select ACCOUNTID, STATUS,
                (case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT
            from CHECKINGACCOUNT_V1
            union all
            select TOACCOUNTID, STATUS, TOTRANSAMOUNT
            from CHECKINGACCOUNT_V1
            where TRANSCODE = 'Transfer') as t
        where  t.ACCOUNTID = a.ACCOUNTID
            and t.STATUS <> 'V') as Balance
    from ACCOUNTLIST_V1 as a
    inner join CURRENCYFORMATS_V1 as c on c.CURRENCYID = a.CURRENCYID
    where a.ACCOUNTTYPE in ('Checking', 'Term') and a.STATUS = 'Open'
    group by a.ACCOUNTNAME
    order by a.ACCOUNTNAME asc;

  • Lua

  • Code: Select all
    local total = 0;

    function handle_record(record)
        local bal = string.format("%.2f", record:get('Balance'));
        local base = bal * record:get("BASECONVRATE");
        record:set("Balance", record:get('PFX_SYMBOL') .. bal .. record:get('SFX_SYMBOL'));
        record:set("Base", base);
        total = total + base;
    end

    function complete(result)
        result:set("Total", total);
    end


  • Template

  • Code: Select all
    <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN">
    <html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
        <title><TMPL_VAR REPORTNAME></title>
        <STYLE><TMPL_INCLUDE NAME="master.css"></STYLE>
    </head>
    <body>
        <div class="container">
            <h3>Account Summary Report</h3>
            <p><TMPL_VAR TODAY></p>
            <div class="row">
                <div class="col-xs-2"></div>
                <div class="col-xs-8">
                    <table class="table">
                    <thead>
                      <tr>
                        <th>Account Name</th>
                        <th  class="text-right">Balance</th>
                        <th  class="text-right">Base Currency</th>
                      </tr>
                    </thead>
                    <tbody>
                        <TMPL_LOOP NAME=CONTENTS>
                        <tr>
                            <th><TMPL_VAR ACCOUNTNAME></th>
                            <td class="text-right"><TMPL_VAR Balance></td>
                            <td class="money text-right"><TMPL_VAR Base></td>
                        </tr>
                        </TMPL_LOOP>
                    </tbody>
                    <tfoot>
                        <tr class="total">
                            <td>Total</td>
                            <td></td>
                            <td class="money text-right"><TMPL_VAR Total></td>
                        </tr>
                    </tfoot>
                    </table>
                </div>
                <table class="table">
                    <tbody>
                    <TMPL_LOOP ERRORS>
                        <tr><td><h3><span class="label label-danger">Error</span></h3></td></tr>
                        <tr><td><TMPL_VAR ERROR></td></tr>
                    </TMPL_LOOP>
                    </tbody>
                </table>
            </div>
        </div>   
    </body>
    <script type="text/javascript">
      function currency(n) {n = parseFloat(n); return isNaN(n) ? 0 : n.toFixed(2);}
      var elements= document.getElementsByClassName("money");
      for (var i = 0; i < elements.length; i++) {elements[i].innerHTML = "<TMPL_VAR PFX_SYMBOL>" + currency(elements[i].innerHTML) + "<TMPL_VAR SFX_SYMBOL>";}
    </script>
    </html>

Attachments
AccountSummary.grm
(1.7 KiB) Downloaded 448 times
Re: Account Summary Report

ogouin
New MMEX User

Posts: 4
Joined: Mon May 17, 2010 4:15 am
Thanks for your report. How can I transform the SQL code to have the Account Summary by Year ?

Many Thanks
Olivier
Re: Account Summary Report

gwalker
New MMEX User

Posts: 5
Joined: Fri Oct 17, 2014 8:29 pm
I get the error Block not terminated when i run the report.  I cut and pasted the relevant code into the respective sections.

Any ideas please?
Re: Account Summary Report

Mukund
Super MMEX User

Posts: 22
Joined: Thu Oct 30, 2014 10:51 am
gwalker wrote:I get the error Block not terminated when i run the report.  I cut and pasted the relevant code into the respective sections.

Any ideas please?
Please find & compare.
Attachments
Account Summary.grm
(1.94 KiB) Downloaded 297 times
Re: Account Summary Report

guanlisheng
MMEX Developer

Posts: 279
Joined: Tue Dec 20, 2011 11:58 pm
Location: China
Any package in the forum should be considered as outdated one and please download from github directly


LiSheng
Page 1 of 1
Who is online

Users browsing this forum: No registered users and 4 guests

cron