Credit Card Statement

Dear All,
I try to make a credit card monthly statement  from 6th of Jan 2015 to 5th Feb 2015. Here i want to make 2 columns as Expenses & Deposits with at last total of each. Here credit card payments are done by transferring amount from another a/cs & receives some petrol surcharge amount as deposit. I try to produce the sql code as below but i am unable to put the petrol surchage entry list in the  Deposit column . Here 4 is my cc a/c id.

Please someone help me to complete. 

Code: Select all

select strftime('%m', TRANSDATE) as MONTH
,strftime('%Y', TRANSDATE) as YEAR 
, strftime('%d-%m-%Y',transdate) as Date
--, accountid
--, toaccountid
, transcode
,ROUND((case accountid when 4  then 
             case transcode when 'Deposit' then 
                  TRANSAMOUNT 
             ELSE
                -TRANSAMOUNT 
             END 
        end ),2) As Expenses
,ROUND((case toaccountid when 4  then 
              case transcode when 'Transfer' then TRANSAMOUNT 
             ELSE
                 -TRANSAMOUNT
              END
         end ),2) as Deposit
from checkingaccount_v1 
where  transdate>= "2015-01-06" and transdate <= "2015-02-05"
AND (accountid=4 OR toaccountid=4)
order by YEAR ;

[Nikolay]

Try to modify this report
viewtopic.php?f=16&t=5949
Or
viewtopic.php?f=16&t=5439

Try to modify this report
viewtopic.php?f=16&t=5949
Or
viewtopic.php?f=16&t=543

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.

[Nikolay]

To difficalt for me, sorry.

May be this query will help.
This return account balance before some date and addition amount for late transactions.

Code: Select all

select t.id
, a.INITIALBAL + total(t.TRANSAMOUNT) as Balance
from
(select ACCOUNTID
, case when (julianday(TRANSDATE)- julianday('2015-01-01'))<0 then 0 else TRANSID end as ID
, STATUS,
(case when TRANSCODE='Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT
from CHECKINGACCOUNT_V1
union all
select TOACCOUNTID
, case when (julianday(TRANSDATE)- julianday('2015-01-01'))<0 then 0 else TRANSID end
, STATUS, TOTRANSAMOUNT
from CHECKINGACCOUNT_V1
where TRANSCODE='Transfer') as t
inner join ACCOUNTLIST_V1 as a on a.ACCOUNTID=t.ACCOUNTID
inner join CURRENCYFORMATS_V1 as c on a.CURRENCYID=c.CURRENCYID
where a.Status='Open'
and t.STATUS<>'V'
and a.accountname = 'BBP:1'
group by t.id

This return account amount for id=0 on 01-01-2015 and id of any oter transactions with amount.
I think this query may be joined with another query to get detailes.