Thanks Nikolay Sir,
That above link for the report is good but i need some another just explaining below :
Now I am trying to make a
Credit Card Monthly Statement by general reports. I complete the following code but i am need some help for the calculation of the opening balance on the starting date of the bill cycle(means that day's credit limit). Please go thru' the below code
Description
Please update the values in Sql & Lua module.
SQL
Code: Select all
-- Please update the values of Billing cycle start & end date in line no 35
-- Please update the values of Credit Card ID no in line 37
SELECT
strftime('%d-%m-%Y',chk.TRANSDATE) As Date
,case chk.SUBCATEGID when -1 then
cat.CATEGNAME --chk.CATEGID
else
cat.CATEGNAME ||':'|| sc.SUBCATEGNAME end AS "Transaction Description"
,round((CASE chk.ACCOUNTID WHEN 4 THEN
(CASE chk.TRANSCODE WHEN 'Transfer' THEN
-chk.TRANSAMOUNT
ELSE
CASE chk.TRANSCODE WHEN 'Deposit' THEN
chk.TRANSAMOUNT
ELSE
-chk.TRANSAMOUNT
END
END)
ELSE
(CASE chk.TOACCOUNTID WHEN 4 THEN
(CASE chk.TRANSCODE WHEN 'Transfer' THEN
chk.TRANSAMOUNT
ELSE
-chk.TRANSAMOUNT
END)
END)
END),2) AS Amount
,chk.transamount as RealAmt
, chk.notes as Remarks
from checkingaccount_v1 as chk
inner join category_v1 cat on cat.categid=chk.categid
inner join subcategory_v1 sc on sc.categid=chk.categid and sc.subcategid=chk.subcategid
where chk.transdate>= "2015-01-06" /* Start Date */ and chk.transdate <= "2015-02-05" /* End Date */
AND (chk.accountid=4 /* put credit card a/c id instead of 4*/ OR chk.toaccountid=4 /* put credit card a/c id instead of 4*/ )
Lua
Code: Select all
-- Please update the Credit limit & paymentdate values in line 1 & 2.
local Credit_limit="52000"
local Payment_date="25-02-2015"
local Bill_Start="06-01-2015"
local Bill_End="05-02-2015"
local grandTotal = 0;
local deposit=0;
local expenses=0;
function handle_record(record)
if (math.floor(record:get('Amount'))>math.floor(0)) then
deposit= deposit + record:get('RealAmt');
end
if (math.floor(record:get('Amount'))<=math.floor(0)) then
expenses = expenses + record:get('RealAmt');
end
grandTotal = grandTotal + record:get('Amount');
end
function complete(result)
result:set("GRAND_TOTAL", grandTotal);
result:set("TOTAL_DEPOSIT", deposit);
result:set("TOTAL_EXPENSES", -expenses);
result:set("Credit_Limit",Credit_limit);
result:set("P_date",Payment_date);
result:set("Bill_Start",Bill_Start);
result:set("Bill_End",Bill_End);
end
Template
Code: Select all
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta http - equiv = "Content-Type" content = "text/html">
<title>Statement for <TMPL_VAR REPORTNAME></title>
<script src = "ChartNew.js"></script>
<script src = "sorttable.js"></script>
<link href = "master.css" rel = "stylesheet">
</head>
<body>
<div class = "container">
<h3><center><TMPL_VAR REPORTNAME></center></h3>
<TMPL_VAR TODAY>
<br>
<table rules='all' border='1' rows='4' cols='5'>
<th colspan ='5' > <Center> Summary</th>
<tr>
<td width='100'>Bill Cycle Start</td>
<td width='80'><TMPL_VAR Bill_Start></td>
<td width='80' rowspan='5'></td>
<td width='120'>Credit Card Limit</td>
<td width='80'>Rs. <TMPL_VAR Credit_Limit></td>
</tr>
<tr>
<td width='80'>Bill Cycle End</td>
<td width='80'><TMPL_VAR Bill_End></td>
<td width='80'>Opening Balance</td>
<td width='80'></td>
</tr>
<tr>
<td width='80'>Bill Payment Date</td>
<td width='80'><TMPL_VAR P_date></td>
<td width='80'>Total Expenses</td>
<td width='80'>Rs. <TMPL_VAR TOTAL_EXPENSES></td>
</tr>
<tr>
<td width='80' rowspan='2'></td>
<td rowspan='2'></td>
<td width='80'>Total Deposits</td>
<td width='80'>Rs. <TMPL_VAR TOTAL_DEPOSIT></td>
</tr>
<tr>
<td width='80'>Available Balance</td>
<td width='80'></td>
</tr>
</table>
<br>
<div class = "row">
<div class = "col-xs-2"></div>
<div class = "col-xs-8">
<table class = "table">
<thead>
<tr >
<th class="text-center" width='100' >Date</th>
<th class="text-center"width='250'>Transaction Description</th>
<th class="text-center" >Amount</th>
<th class="text-center" width='200'>Remarks</th>
</tr>
</thead>
<tbody>
<TMPL_LOOP NAME=CONTENTS>
<tr>
<td><TMPL_VAR "Date"></td>
<td><TMPL_VAR "Transaction Description"></td>
<td class = "money"><TMPL_VAR "Amount"></td>
<td><TMPL_VAR "Remarks"></td>
</tr>
</TMPL_LOOP>
</tbody>
<tfoot>
<tr class="total">
<td></td>
<td>Grand Total </td>
<td class='money'><TMPL_VAR GRAND_TOTAL></td>
</tr>
</tfoot>
</table>
</div>
<TMPL_LOOP ERRORS>
<TMPL_VAR ERROR>
</TMPL_LOOP>
</div>
</div>
</body>
<script>
<!-- Format double to base currency -->
function currency(n) {
n = parseFloat(n);
n = isNaN(n) ? 0 : n.toFixed(2);
var out = n.toString().replace(".", "|");
out = out.toString().replace(/\B(?=(\d{3})+(?!\d))/g, "<TMPL_VAR GROUP_SEPARATOR>");
out = out.replace("|", "<TMPL_VAR DECIMAL_POINT>");
return out;
}
var elements= document.getElementsByClassName("money");
for (var i = 0; i < elements.length; i++) {
var element = elements[i];
element.style.textAlign='right';
if (element.innerHTML.indexOf("-") > -1) {
element.style.color="#ff0000";
}
element.innerHTML = '<TMPL_VAR PFX_SYMBOL>' + currency(element.innerHTML) +'<TMPL_VAR SFX_SYMBOL>';
}
</script>
</html>
Please help anybody for calculating the opening balance coding of my credit card.