Categories Total last 12 months

Build and share your cool customized reports built w/ one sql, Chart.js and Lua

Moderator: Renato

Post Reply
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Categories Total last 12 months

Post by Nikolay »

just template:

Code: Select all

select 
case t.subcateg when -1 then ca.categname else ca.categname ||':'||sc.subcategname end category
, t.transid, t.categ, t.subcateg
, total( case strftime('%m', date('now', 'start of month','-11 month','localtime')) when month then amount end ) as twe
, total( case strftime('%m', date('now', 'start of month','-10 month','localtime')) when month then amount end ) as ele
, total( case strftime('%m', date('now', 'start of month','-9 month','localtime')) when month then amount end ) as ten
, total( case strftime('%m', date('now', 'start of month','-8 month','localtime')) when month then amount end ) as nin
, total( case strftime('%m', date('now', 'start of month','-7 month','localtime')) when month then amount end ) as egh
, total( case strftime('%m', date('now', 'start of month','-6 month','localtime')) when month then amount end ) as sev
, total( case strftime('%m', date('now', 'start of month','-5 month','localtime')) when month then amount end ) as six
, total( case strftime('%m', date('now', 'start of month','-4 month','localtime')) when month then amount end ) as fiv
, total( case strftime('%m', date('now', 'start of month','-3 month','localtime')) when month then amount end ) as fou
, total( case strftime('%m', date('now', 'start of month','-2 month','localtime')) when month then amount end ) as thr
, total( case strftime('%m', date('now', 'start of month','-1 month','localtime')) when month then amount end ) as two
, total( case strftime('%m', date('now', 'start of month','-0 month','localtime')) when month then amount end ) as one
from(
    select 
         strftime('%m', TRANSDATE) as month
    , c.transid, cf.BaseConvRate
       , c.accountid, c.transcode
       , case ifnull(c.categid, -1) when -1 then s.categid else c.categid end as categ
       , case ifnull(c.subcategid,-1) when -1 then ifnull(s.subcategid,-1) else ifnull(c.subcategid,-1) end as subcateg
       , c.payeeid
       , sum((case c.categid when -1 then  splittransamount else  transamount  end) 
           * (case transcode when 'Withdrawal' then - cf.BaseConvRate else cf.BaseConvRate end)
            ) amount
    from checkingaccount_v1 c
    left join splittransactions_v1 s on s.transid=c.transid
    left join ACCOUNTLIST_V1 AC on AC.ACCOUNTID = c.ACCOUNTID
    left join currencyformats_v1 cf on cf.currencyid=AC.currencyid
    where transcode != 'Transfer'
    and c.status !='V'
    and ac.status !='Closed'
    and (date('now', 'start of month','-11 month','localtime') <= transdate
        and transdate < date('now', 'start of month','+1 month','localtime'))
    group by month, categ, subcateg
    ) t 
    left join category_v1 ca on ca.categid=t.categ
    left join subcategory_v1 sc on sc.categid=t.categ and sc.subcategid=t.subcateg

group by category
order by category
Lua

Code: Select all

local grandTotal = 0;
local total = {0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0};
local cols = {"one", "two", "thr" , "fou", "fiv", "six", "sev", "egh", "nin", "ten", "twe", "ele"};

function handle_record(record)
    grandTotal = grandTotal + record:get('OVERALL');
    for key, value in ipairs(cols) do
        total[key] = total[key] + record:get(value);
    end
end

function complete(result)
    result:set("GRAND_TOTAL", grandTotal);
    for key, value in ipairs(cols) do
        result:set("TOTAL_" .. value, total[key]);
    end
end
htt

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>
    <link href = "master.css" rel = "stylesheet" />
</head>
<body>
<TMPL_VAR TODAY><hr>
<table class='table'>
  <thead>
    <tr>
        <th></th>
        <th id='lastYear', style="text-align:center;" ></th>
        <th id='thisYear', style="text-align:center;"></th>
        <th></th>
    </tr>
    <tr>
        <th>Category</th>
        <th id='1'></th>
        <th id='2'></th>
        <th id='3'></th>
        <th id='4'></th>
        <th id='5'></th>
        <th id='6'></th>
        <th id='7'></th>
        <th id='8'></th>
        <th id='9'></th>
        <th id='10'></th>
        <th id='11'></th>
        <th id='12'></th>
        <th>Overall</th>
    </tr>

  </thead>
  <tbody>
    <TMPL_LOOP NAME=CONTENTS>
        <tr>
        <td><TMPL_VAR CATEGORY></td>
        <td class='money'><TMPL_VAR twe></td>
        <td class='money'><TMPL_VAR ele></td>
        <td class='money'><TMPL_VAR ten></td>
        <td class='money'><TMPL_VAR nin></td>
        <td class='money'><TMPL_VAR egh></td>
        <td class='money'><TMPL_VAR sev></td>
        <td class='money'><TMPL_VAR six></td>
        <td class='money'><TMPL_VAR fiv></td>
        <td class='money'><TMPL_VAR fou></td>
        <td class='money'><TMPL_VAR thr></td>
        <td class='money'><TMPL_VAR two></td>
        <td class='money'><TMPL_VAR one></td>
        <td class='money'><TMPL_VAR OVERALL></td>
    </tr>
    </TMPL_LOOP>
</tbody>
<tfoot>
    <tr class="total">
        <td>Total: </td>
        <td class='money'><TMPL_VAR TOTAL_twe></td>
        <td class='money'><TMPL_VAR TOTAL_ele></td>
        <td class='money'><TMPL_VAR TOTAL_ten></td>
        <td class='money'><TMPL_VAR TOTAL_nin></td>
        <td class='money'><TMPL_VAR TOTAL_egh></td>
        <td class='money'><TMPL_VAR TOTAL_sev></td>
        <td class='money'><TMPL_VAR TOTAL_six></td>
        <td class='money'><TMPL_VAR TOTAL_fiv></td>
        <td class='money'><TMPL_VAR TOTAL_fou></td>
        <td class='money'><TMPL_VAR TOTAL_thr></td>
        <td class='money'><TMPL_VAR TOTAL_two></td>
        <td class='money'><TMPL_VAR TOTAL_ONE></td>
        <td class='money'><TMPL_VAR GRAND_TOTAL></td> 
    </tr>
</tfoot>
</table>
<TMPL_LOOP ERRORS>
    <hr>    <TMPL_VAR ERROR>
</TMPL_LOOP>
</body>
<script>
    var d = new Date();
    var n = d.getMonth();
    var months = {1:'Jan', 2:'Feb', 3:'Mar', 4:'Apr', 5:'May', 6:'Jun', 7:'Jul', 8:'Aug', 9:'Sep', 10:'Oct', 11:'Nov', 12:'Dec'};
    for(var i in months){
        var x = (n + parseInt(i))%12 +1;
        document.getElementById(i).innerHTML = months[x];
    }
    var element= document.getElementById('lastYear');
    element.innerHTML = d.getFullYear() - 1;
    element.colSpan=12 -n;
    element = document.getElementById('thisYear');
    element.innerHTML = d.getFullYear();
    element.colSpan=n;

</script>
<script type="text/javascript">
    <!-- Format numbers -->
    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 = currency(elements[i].innerHTML);
        elements[i].style.textAlign='right';
        if (elements[i].innerHTML.indexOf("-") > -1) {
            elements[i].style.color="#ff0000";
        } 
    }
</script>
</html>

Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Categories Total last 12 months

Post by Nikolay »

package for import
Attachments
Categs Last 12 months.grm
(2.4 KiB) Downloaded 621 times
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Categories Total last 12 months

Post by Nikolay »

Alter Lua script:

Code: Select all

local grandTotal = 0;
local cols = {["one"]=0, ["two"]=0, ["thr"]=0 , ["fou"]=0, ["fiv"]=0, ["six"]=0, ["sev"]=0, ["egh"]=0, ["nin"]=0, ["ten"]=0, ["ele"]=0, ["twe"]=0};
function handle_record(record)
    grandTotal = grandTotal + record:get('OVERALL');
    for key, value in pairs(cols) do
        cols[key] = cols[key] + record:get(key);
    end
end
function complete(result)
    result:set("GRAND_TOTAL", grandTotal);
    for key, value in pairs(cols) do
        result:set("TOTAL_" .. key, value);
    end
end
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Categories Total last 12 months

Post by Nikolay »

More nice formating
Attachments
Categs Last 12 months.grm
(2.43 KiB) Downloaded 679 times
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Categories Total last 12 months

Post by Nikolay »

Code: Select all

        <th  id='lastYear', style="text-align:center; background:#B0E0E6" ></th>
        <th id='thisYear', style="text-align:center; background:#AFEEEE"></th>
miki
New MMEX User
Posts: 14
Joined: Wed Jul 11, 2012 3:50 pm
Are you a spam bot?: No
Location: IT
Contact:

Re: Categories Total last 12 months

Post by miki »

I like it :!:
MMEX v1.7.0 x64 [portable mode] / Windows 10 22H2 Home x64
Post Reply