[prev in list] [next in list] [prev in thread] [next in thread]
List: mifos-developer
Subject: Re: [Mifos-developer] Fwd: Loan Tracking Report not running
From: Sughosh Bharadwaj <sughosh () confluxtechnologies ! com>
Date: 2014-05-23 3:53:38
Message-ID: CACfgXuubmA1uGEX1XgFaYbJ1c3D1mTN_J7=ZdcaPp63TLWL8+A () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
Hello Gloria,
Please use the following query in the report.
SELECT mpl.name 'Product',ifnull(mcv.code_value,'NA')
'Gender',mc.display_name 'Borrower Name',ml.account_no 'Account
Number',ml.disbursedon_date 'Disbursal
Date',CONVERT(ml.principal_disbursed_derived,DECIMAL(10,2)) 'Amount
Disbursed',CASE mpl.allow_multiple_disbursalsWHEN 0 THEN
CONVERT(ml.principal_disbursed_derived,DECIMAL(10,2))ELSE
CONVERT(tl.ddam ,DECIMAL(10,2))END AS 'Principal
B/F',CONVERT(IFNULL(due.rpPrin,0),DECIMAL(10,2))
'Principal',CONVERT(IFNULL(due.rpInt,0),DECIMAL(10,2))
'Interest',CONVERT(IFNULL(due.rpTot,0),DECIMAL(10,2)) 'Total
Due',CONVERT(IFNULL(rep.rpPrin,0),DECIMAL(10,2)) 'Repaid
Principal',CONVERT(IFNULL(rep.rpInt,0),DECIMAL(10,2)) 'Repaid
Interest',CONVERT(IFNULL(rep.rpTot,0),DECIMAL(10,2)) 'Repaid
Total',(IFNULL(rep.rpTot,0)/ ml.principal_amount) * 100 '%age
Recovered',(IFNULL(due.rpPrin,0)/ ml.principal_amount) * 100 '%age
Unrecovered',CONVERT(IFNULL(due.rpPrin,0) -
IFNULL(rep.rpPrin,0),DECIMAL(10,2)) 'Principal
Arrears',CONVERT(IFNULL(due.rpInt,0) -
IFNULL(rep.rpInt,0),DECIMAL(10,2)) 'Interest
Arrears',CONVERT(IFNULL(due.rpTot,0) -
IFNULL(rep.rpTot,0),DECIMAL(10,2)) 'Total
Arrears',CONVERT(ml.principal_outstanding_derived,DECIMAL(10,2))
'Outstanding Principal',CASE
WHEN DATEDIFF(CURDATE(),laa.overdue_since_date_derived) BETWEEN 1 AND
30 THEN CONVERT(laa.total_overdue_derived,DECIMAL(10,2))ELSE '-'END AS
'1-30',CASE WHEN DATEDIFF(CURDATE(),laa.overdue_since_date_derived)
BETWEEN 31 AND 60THEN CONVERT(laa.total_overdue_derived,DECIMAL(10,2))
ELSE '-'END AS '31-60',CASE WHEN
DATEDIFF(CURDATE(),laa.overdue_since_date_derived) BETWEEN 61 AND
90THEN CONVERT(laa.total_overdue_derived,DECIMAL(10,2)) ELSE '-'END AS
'61-90',CASE WHEN DATEDIFF(CURDATE(),laa.overdue_since_date_derived)
BETWEEN 91 AND 120THEN
CONVERT(laa.total_overdue_derived,DECIMAL(10,2)) ELSE '-'END AS
'91-120',CASE WHEN DATEDIFF(CURDATE(),laa.overdue_since_date_derived)
> 120THEN CONVERT(laa.total_overdue_derived,DECIMAL(10,2)) ELSE '-'END
AS '121 & above'FROM m_loan mlINNER JOIN m_client mc ON
mc.id=ml.client_idINNER JOIN m_product_loan mpl ON
mpl.id=ml.product_idLEFT JOIN m_code_value mcv ON
mcv.id=mc.gender_cv_idLEFT JOIN (SELECT dd.loan_id ddl,dd.principal
ddamFROM m_loan_disbursement_detail ddWHERE dd.disbursedon_date IN
(SELECT MAX(dd.disbursedon_date)FROM m_loan_disbursement_detail
ddGROUP BY dd.loan_id)) tl ON tl.ddl=ml.idINNER JOIN (SELECT
rp.loan_id rpid,SUM(rp.principal_amount)
rpPrin,SUM(rp.interest_amount) rpInt,SUM(rp.principal_amount +
rp.interest_amount) rpTotFROM m_loan_repayment_schedule rpWHERE
rp.duedate <=CURDATE()GROUP BY rp.loan_id) due ON due.rpid=ml.idLEFT
JOIN (SELECT rp.loan_id rpid,SUM(rp.principal_amount)
rpPrin,SUM(rp.interest_amount) rpInt,SUM(rp.principal_amount +
rp.interest_amount) rpTotFROM m_loan_repayment_schedule rpWHERE
rp.completed_derived=1GROUP BY rp.loan_id) rep ON rep.rpid=ml.idLEFT
JOIN m_loan_arrears_aging laa ON laa.loan_id=ml.idWHERE
ml.loan_status_id=300ORDER BY 1,mc.gender_cv_id
Regards,
Sughosh
On Mon, May 19, 2014 at 1:52 PM, Lajeo Gloria <lajeogloria@gmail.com> wrote:
> Thanks.
> Looking forward to it.
>
>
>
> Thanks
> Regards
> Gloria
>
>
> On Mon, May 19, 2014 at 10:56 AM, Sughosh Bharadwaj <
> sughosh@confluxtechnologies.com> wrote:
>
> > Hi Gloria,
> >
> > Sorry, I couldn't reply.
> > I was caught up with some other issues.
> >
> > I will look into it today and get back to you ASAP.
> >
> > Regards,
> > Sughosh
> >
> >
> > On Mon, May 19, 2014 at 1:23 PM, Lajeo Gloria <lajeogloria@gmail.com>wrote:
> >
> > > Hi All,
> > > I have tried to work on the pentaho report for the Loan tracking report
> > > following the sql statement scrip given by Sughosh but when i try to
> > > preview a blank preview is brought.
> > >
> > > Even the .properties (resource file) is not loaded on the report.
> > > Am stuck now, could you kindly look at my work and fix or suggest
> > > solutions. Please see attachment
> > > Thanks
> > >
> > > Regards
> > > Gloria
> > > Loan Tracking Sheet.properties<https://docs.google.com/file/d/0B60YCFl5hnYuSTk5aFpEdFJXY3M/edit?usp=drive_web>
> > >
> > > Loan Tracking Sheet.prpt<https://docs.google.com/file/d/0B60YCFl5hnYub0NtN2hqUGYtUDQ/edit?usp=drive_web>
> > >
> > >
> > >
> > >
> > > ------------------------------------------------------------------------------
> > > "Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE
> > > Instantly run your Selenium tests across 300+ browser/OS combos.
> > > Get unparalleled scalability from the best Selenium testing platform
> > > available
> > > Simple to use. Nothing to install. Get started now for free."
> > > http://p.sf.net/sfu/SauceLabs
> > > Mifos-developer mailing list
> > > mifos-developer@lists.sourceforge.net
> > > Unsubscribe or change settings at:
> > > https://lists.sourceforge.net/lists/listinfo/mifos-developer
> > >
> >
> >
> >
> > ------------------------------------------------------------------------------
> > "Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE
> > Instantly run your Selenium tests across 300+ browser/OS combos.
> > Get unparalleled scalability from the best Selenium testing platform
> > available
> > Simple to use. Nothing to install. Get started now for free."
> > http://p.sf.net/sfu/SauceLabs
> > Mifos-developer mailing list
> > mifos-developer@lists.sourceforge.net
> > Unsubscribe or change settings at:
> > https://lists.sourceforge.net/lists/listinfo/mifos-developer
> >
>
>
>
> ------------------------------------------------------------------------------
> "Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE
> Instantly run your Selenium tests across 300+ browser/OS combos.
> Get unparalleled scalability from the best Selenium testing platform
> available
> Simple to use. Nothing to install. Get started now for free."
> http://p.sf.net/sfu/SauceLabs
> Mifos-developer mailing list
> mifos-developer@lists.sourceforge.net
> Unsubscribe or change settings at:
> https://lists.sourceforge.net/lists/listinfo/mifos-developer
>
[Attachment #5 (text/html)]
<div dir="ltr"><div>Hello Gloria,<br><br></div>Please use the following query in the \
report.<br><br><pre><code>
<span class="">SELECT</span><span class=""> </span><span class="">mpl</span><span \
class="">.</span><span class="">name</span><span class=""> </span><span \
class="">'Product' </span><span class="">,</span><span \
class="">ifnull</span><span class="">(</span><span class="">mcv</span><span \
class="">.</span><span class="">code_value</span><span class="">,</span><span \
class="">'NA'</span><span class="">)</span><span class=""> </span><span \
class="">'Gender' </span><span class="">,</span><span class="">mc</span><span \
class="">.</span><span class="">display_name</span><span class=""> </span><span \
class="">'Borrower Name' </span><span class="">,</span><span \
class="">ml</span><span class="">.</span><span class="">account_no</span><span \
class=""> </span><span class="">'Account Number' </span><span \
class="">,</span><span class="">ml</span><span class="">.</span><span \
class="">disbursedon_date</span><span class=""> </span><span class="">'Disbursal \
Date' </span><span class="">,</span><span class="">CONVERT</span><span \
class="">(</span><span class="">ml</span><span class="">.</span><span \
class="">principal_disbursed_derived</span><span class="">,</span><span \
class="">DECIMAL</span><span class="">(</span><span class="">10</span><span \
class="">,</span><span class="">2</span><span class="">))</span><span class=""> \
</span><span class="">'Amount Disbursed' </span><span class="">,</span><span \
class="">CASE</span><span class=""> </span><span class="">mpl</span><span \
class="">.</span><span class="">allow_multiple_disbursals </span><span \
class="">WHEN</span><span class=""> </span><span class="">0</span><span class=""> \
</span><span class="">THEN</span><span class=""> </span><span \
class="">CONVERT</span><span class="">(</span><span class="">ml</span><span \
class="">.</span><span class="">principal_disbursed_derived</span><span \
class="">,</span><span class="">DECIMAL</span><span class="">(</span><span \
class="">10</span><span class="">,</span><span class="">2</span><span class="">)) \
</span><span class="">ELSE</span><span class=""> </span><span \
class="">CONVERT</span><span class="">(</span><span class="">tl</span><span \
class="">.</span><span class="">ddam</span><span class=""> </span><span \
class="">,</span><span class="">DECIMAL</span><span class="">(</span><span \
class="">10</span><span class="">,</span><span class="">2</span><span class="">)) \
</span><span class="">END</span><span class=""> </span><span class="">AS</span><span \
class=""> </span><span class="">'Principal B/F' </span><span \
class="">,</span><span class="">CONVERT</span><span class="">(</span><span \
class="">IFNULL</span><span class="">(</span><span class="">due</span><span \
class="">.</span><span class="">rpPrin</span><span class="">,</span><span \
class="">0</span><span class="">),</span><span class="">DECIMAL</span><span \
class="">(</span><span class="">10</span><span class="">,</span><span \
class="">2</span><span class="">))</span><span class=""> </span><span \
class="">'Principal' </span><span class="">,</span><span \
class="">CONVERT</span><span class="">(</span><span class="">IFNULL</span><span \
class="">(</span><span class="">due</span><span class="">.</span><span \
class="">rpInt</span><span class="">,</span><span class="">0</span><span \
class="">),</span><span class="">DECIMAL</span><span class="">(</span><span \
class="">10</span><span class="">,</span><span class="">2</span><span \
class="">))</span><span class=""> </span><span class="">'Interest' \
</span><span class="">,</span><span class="">CONVERT</span><span \
class="">(</span><span class="">IFNULL</span><span class="">(</span><span \
class="">due</span><span class="">.</span><span class="">rpTot</span><span \
class="">,</span><span class="">0</span><span class="">),</span><span \
class="">DECIMAL</span><span class="">(</span><span class="">10</span><span \
class="">,</span><span class="">2</span><span class="">))</span><span class=""> \
</span><span class="">'Total Due' </span><span class="">,</span><span \
class="">CONVERT</span><span class="">(</span><span class="">IFNULL</span><span \
class="">(</span><span class="">rep</span><span class="">.</span><span \
class="">rpPrin</span><span class="">,</span><span class="">0</span><span \
class="">),</span><span class="">DECIMAL</span><span class="">(</span><span \
class="">10</span><span class="">,</span><span class="">2</span><span \
class="">))</span><span class=""> </span><span class="">'Repaid Principal' \
</span><span class="">,</span><span class="">CONVERT</span><span \
class="">(</span><span class="">IFNULL</span><span class="">(</span><span \
class="">rep</span><span class="">.</span><span class="">rpInt</span><span \
class="">,</span><span class="">0</span><span class="">),</span><span \
class="">DECIMAL</span><span class="">(</span><span class="">10</span><span \
class="">,</span><span class="">2</span><span class="">))</span><span class=""> \
</span><span class="">'Repaid Interest' </span><span class="">,</span><span \
class="">CONVERT</span><span class="">(</span><span class="">IFNULL</span><span \
class="">(</span><span class="">rep</span><span class="">.</span><span \
class="">rpTot</span><span class="">,</span><span class="">0</span><span \
class="">),</span><span class="">DECIMAL</span><span class="">(</span><span \
class="">10</span><span class="">,</span><span class="">2</span><span \
class="">))</span><span class=""> </span><span class="">'Repaid Total' \
</span><span class="">,(</span><span class="">IFNULL</span><span \
class="">(</span><span class="">rep</span><span class="">.</span><span \
class="">rpTot</span><span class="">,</span><span class="">0</span><span \
class="">)/</span><span class=""> </span><span class="">ml</span><span \
class="">.</span><span class="">principal_amount</span><span class="">)</span><span \
class=""> </span><span class="">*</span><span class=""> </span><span \
class="">100</span><span class=""> </span><span class="">'%age Recovered' \
</span><span class="">,(</span><span class="">IFNULL</span><span \
class="">(</span><span class="">due</span><span class="">.</span><span \
class="">rpPrin</span><span class="">,</span><span class="">0</span><span \
class="">)/</span><span class=""> </span><span class="">ml</span><span \
class="">.</span><span class="">principal_amount</span><span class="">)</span><span \
class=""> </span><span class="">*</span><span class=""> </span><span \
class="">100</span><span class=""> </span><span class="">'%age Unrecovered' \
</span><span class="">,</span><span class="">CONVERT</span><span \
class="">(</span><span class="">IFNULL</span><span class="">(</span><span \
class="">due</span><span class="">.</span><span class="">rpPrin</span><span \
class="">,</span><span class="">0</span><span class="">)</span><span class=""> \
</span><span class="">-</span><span class=""> </span><span \
class="">IFNULL</span><span class="">(</span><span class="">rep</span><span \
class="">.</span><span class="">rpPrin</span><span class="">,</span><span \
class="">0</span><span class="">),</span><span class="">DECIMAL</span><span \
class="">(</span><span class="">10</span><span class="">,</span><span \
class="">2</span><span class="">))</span><span class=""> </span><span \
class="">'Principal Arrears' </span><span class="">,</span><span \
class="">CONVERT</span><span class="">(</span><span class="">IFNULL</span><span \
class="">(</span><span class="">due</span><span class="">.</span><span \
class="">rpInt</span><span class="">,</span><span class="">0</span><span \
class="">)</span><span class=""> </span><span class="">-</span><span class=""> \
</span><span class="">IFNULL</span><span class="">(</span><span \
class="">rep</span><span class="">.</span><span class="">rpInt</span><span \
class="">,</span><span class="">0</span><span class="">),</span><span \
class="">DECIMAL</span><span class="">(</span><span class="">10</span><span \
class="">,</span><span class="">2</span><span class="">))</span><span class=""> \
</span><span class="">'Interest Arrears' </span><span class="">,</span><span \
class="">CONVERT</span><span class="">(</span><span class="">IFNULL</span><span \
class="">(</span><span class="">due</span><span class="">.</span><span \
class="">rpTot</span><span class="">,</span><span class="">0</span><span \
class="">)</span><span class=""> </span><span class="">-</span><span class=""> \
</span><span class="">IFNULL</span><span class="">(</span><span \
class="">rep</span><span class="">.</span><span class="">rpTot</span><span \
class="">,</span><span class="">0</span><span class="">),</span><span \
class="">DECIMAL</span><span class="">(</span><span class="">10</span><span \
class="">,</span><span class="">2</span><span class="">))</span><span class=""> \
</span><span class="">'Total Arrears'</span><span class="">, </span><span \
class="">CONVERT</span><span class="">(</span><span class="">ml</span><span \
class="">.</span><span class="">principal_outstanding_derived</span><span \
class="">,</span><span class="">DECIMAL</span><span class="">(</span><span \
class="">10</span><span class="">,</span><span class="">2</span><span \
class="">))</span><span class=""> </span><span class="">'Outstanding \
Principal' </span><span class="">,</span><span class="">CASE
WHEN</span><span class=""> </span><span class="">DATEDIFF</span><span \
class="">(</span><span class="">CURDATE</span><span class="">(),</span><span \
class="">laa</span><span class="">.</span><span \
class="">overdue_since_date_derived</span><span class="">)</span><span class=""> \
</span><span class="">BETWEEN</span><span class=""> </span><span \
class="">1</span><span class=""> </span><span class="">AND</span><span class=""> \
</span><span class="">30</span><span class=""> </span><span class="">THEN</span><span \
class=""> </span><span class="">CONVERT</span><span class="">(</span><span \
class="">laa</span><span class="">.</span><span \
class="">total_overdue_derived</span><span class="">,</span><span \
class="">DECIMAL</span><span class="">(</span><span class="">10</span><span \
class="">,</span><span class="">2</span><span class="">)) </span><span \
class="">ELSE</span><span class=""> </span><span class="">'-' </span><span \
class="">END</span><span class=""> </span><span class="">AS</span><span class=""> \
</span><span class="">'1-30' </span><span class="">,</span><span \
class="">CASE</span><span class=""> </span><span class="">WHEN</span><span class=""> \
</span><span class="">DATEDIFF</span><span class="">(</span><span \
class="">CURDATE</span><span class="">(),</span><span class="">laa</span><span \
class="">.</span><span class="">overdue_since_date_derived</span><span \
class="">)</span><span class=""> </span><span class="">BETWEEN</span><span class=""> \
</span><span class="">31</span><span class=""> </span><span class="">AND</span><span \
class=""> </span><span class="">60 </span><span class="">THEN</span><span class=""> \
</span><span class="">CONVERT</span><span class="">(</span><span \
class="">laa</span><span class="">.</span><span \
class="">total_overdue_derived</span><span class="">,</span><span \
class="">DECIMAL</span><span class="">(</span><span class="">10</span><span \
class="">,</span><span class="">2</span><span class="">))</span><span class=""> \
</span><span class="">ELSE</span><span class=""> </span><span class="">'-' \
</span><span class="">END</span><span class=""> </span><span class="">AS</span><span \
class=""> </span><span class="">'31-60' </span><span class="">,</span><span \
class="">CASE</span><span class=""> </span><span class="">WHEN</span><span class=""> \
</span><span class="">DATEDIFF</span><span class="">(</span><span \
class="">CURDATE</span><span class="">(),</span><span class="">laa</span><span \
class="">.</span><span class="">overdue_since_date_derived</span><span \
class="">)</span><span class=""> </span><span class="">BETWEEN</span><span class=""> \
</span><span class="">61</span><span class=""> </span><span class="">AND</span><span \
class=""> </span><span class="">90 </span><span class="">THEN</span><span class=""> \
</span><span class="">CONVERT</span><span class="">(</span><span \
class="">laa</span><span class="">.</span><span \
class="">total_overdue_derived</span><span class="">,</span><span \
class="">DECIMAL</span><span class="">(</span><span class="">10</span><span \
class="">,</span><span class="">2</span><span class="">))</span><span class=""> \
</span><span class="">ELSE</span><span class=""> </span><span class="">'-' \
</span><span class="">END</span><span class=""> </span><span class="">AS</span><span \
class=""> </span><span class="">'61-90' </span><span class="">,</span><span \
class="">CASE</span><span class=""> </span><span class="">WHEN</span><span class=""> \
</span><span class="">DATEDIFF</span><span class="">(</span><span \
class="">CURDATE</span><span class="">(),</span><span class="">laa</span><span \
class="">.</span><span class="">overdue_since_date_derived</span><span \
class="">)</span><span class=""> </span><span class="">BETWEEN</span><span class=""> \
</span><span class="">91</span><span class=""> </span><span class="">AND</span><span \
class=""> </span><span class="">120 </span><span class="">THEN</span><span class=""> \
</span><span class="">CONVERT</span><span class="">(</span><span \
class="">laa</span><span class="">.</span><span \
class="">total_overdue_derived</span><span class="">,</span><span \
class="">DECIMAL</span><span class="">(</span><span class="">10</span><span \
class="">,</span><span class="">2</span><span class="">))</span><span class=""> \
</span><span class="">ELSE</span><span class=""> </span><span class="">'-' \
</span><span class="">END</span><span class=""> </span><span class="">AS</span><span \
class=""> </span><span class="">'91-120' </span><span class="">,</span><span \
class="">CASE</span><span class=""> </span><span class="">WHEN</span><span class=""> \
</span><span class="">DATEDIFF</span><span class="">(</span><span \
class="">CURDATE</span><span class="">(),</span><span class="">laa</span><span \
class="">.</span><span class="">overdue_since_date_derived</span><span \
class="">)</span><span class=""> </span><span class="">></span><span class=""> \
</span><span class="">120 </span><span class="">THEN</span><span class=""> \
</span><span class="">CONVERT</span><span class="">(</span><span \
class="">laa</span><span class="">.</span><span \
class="">total_overdue_derived</span><span class="">,</span><span \
class="">DECIMAL</span><span class="">(</span><span class="">10</span><span \
class="">,</span><span class="">2</span><span class="">))</span><span class=""> \
</span><span class="">ELSE</span><span class=""> </span><span class="">'-' \
</span><span class="">END</span><span class=""> </span><span class="">AS</span><span \
class=""> </span><span class="">'121 & above' </span><span \
class="">FROM</span><span class=""> </span><span class="">m_loan</span><span \
class=""> </span><span class="">ml </span><span class="">INNER</span><span class=""> \
</span><span class="">JOIN</span><span class=""> </span><span \
class="">m_client</span><span class=""> </span><span class="">mc</span><span \
class=""> </span><span class="">ON</span><span class=""> </span><span \
class="">mc</span><span class="">.</span><span class="">id</span><span \
class="">=</span><span class="">ml</span><span class="">.</span><span \
class="">client_id </span><span class="">INNER</span><span class=""> </span><span \
class="">JOIN</span><span class=""> </span><span class="">m_product_loan</span><span \
class=""> </span><span class="">mpl</span><span class=""> </span><span \
class="">ON</span><span class=""> </span><span class="">mpl</span><span \
class="">.</span><span class="">id</span><span class="">=</span><span \
class="">ml</span><span class="">.</span><span class="">product_id </span><span \
class="">LEFT</span><span class=""> </span><span class="">JOIN</span><span class=""> \
</span><span class="">m_code_value</span><span class=""> </span><span \
class="">mcv</span><span class=""> </span><span class="">ON</span><span class=""> \
</span><span class="">mcv</span><span class="">.</span><span class="">id</span><span \
class="">=</span><span class="">mc</span><span class="">.</span><span \
class="">gender_cv_id </span><span class="">LEFT</span><span class=""> </span><span \
class="">JOIN</span><span class=""> </span><span class="">(</span><span \
class="">SELECT</span><span class=""> </span><span class="">dd</span><span \
class="">.</span><span class="">loan_id</span><span class=""> </span><span \
class="">ddl </span><span class="">,</span><span class="">dd</span><span \
class="">.</span><span class="">principal</span><span class=""> </span><span \
class="">ddam </span><span class="">FROM</span><span class=""> </span><span \
class="">m_loan_disbursement_detail</span><span class=""> </span><span class="">dd \
</span><span class="">WHERE</span><span class=""> </span><span \
class="">dd</span><span class="">.</span><span class="">disbursedon_date</span><span \
class=""> </span><span class="">IN</span><span class=""> </span><span \
class="">(</span><span class="">SELECT</span><span class=""> </span><span \
class="">MAX</span><span class="">(</span><span class="">dd</span><span \
class="">.</span><span class="">disbursedon_date</span><span class="">) </span><span \
class="">FROM</span><span class=""> </span><span \
class="">m_loan_disbursement_detail</span><span class=""> </span><span class="">dd \
</span><span class="">GROUP</span><span class=""> </span><span \
class="">BY</span><span class=""> </span><span class="">dd</span><span \
class="">.</span><span class="">loan_id</span><span class="">))</span><span class=""> \
</span><span class="">tl</span><span class=""> </span><span class="">ON</span><span \
class=""> </span><span class="">tl</span><span class="">.</span><span \
class="">ddl</span><span class="">=</span><span class="">ml</span><span \
class="">.</span><span class="">id </span><span class="">INNER</span><span class=""> \
</span><span class="">JOIN</span><span class=""> </span><span class="">(</span><span \
class="">SELECT</span><span class=""> </span><span class="">rp</span><span \
class="">.</span><span class="">loan_id</span><span class=""> </span><span \
class="">rpid </span><span class="">,</span><span class="">SUM</span><span \
class="">(</span><span class="">rp</span><span class="">.</span><span \
class="">principal_amount</span><span class="">)</span><span class=""> </span><span \
class="">rpPrin </span><span class="">,</span><span class="">SUM</span><span \
class="">(</span><span class="">rp</span><span class="">.</span><span \
class="">interest_amount</span><span class="">)</span><span class=""> </span><span \
class="">rpInt </span><span class="">,</span><span class="">SUM</span><span \
class="">(</span><span class="">rp</span><span class="">.</span><span \
class="">principal_amount</span><span class=""> </span><span class="">+</span><span \
class=""> </span><span class="">rp</span><span class="">.</span><span \
class="">interest_amount</span><span class="">)</span><span class=""> </span><span \
class="">rpTot </span><span class="">FROM</span><span class=""> </span><span \
class="">m_loan_repayment_schedule</span><span class=""> </span><span class="">rp \
</span><span class="">WHERE</span><span class=""> </span><span \
class="">rp</span><span class="">.</span><span class="">duedate</span><span class=""> \
</span><span class=""><=</span><span class="">CURDATE</span><span class="">() \
</span><span class="">GROUP</span><span class=""> </span><span \
class="">BY</span><span class=""> </span><span class="">rp</span><span \
class="">.</span><span class="">loan_id</span><span class="">)</span><span class=""> \
</span><span class="">due</span><span class=""> </span><span class="">ON</span><span \
class=""> </span><span class="">due</span><span class="">.</span><span \
class="">rpid</span><span class="">=</span><span class="">ml</span><span \
class="">.</span><span class="">id </span><span class="">LEFT</span><span class=""> \
</span><span class="">JOIN</span><span class=""> </span><span class="">(</span><span \
class="">SELECT</span><span class=""> </span><span class="">rp</span><span \
class="">.</span><span class="">loan_id</span><span class=""> </span><span \
class="">rpid </span><span class="">,</span><span class="">SUM</span><span \
class="">(</span><span class="">rp</span><span class="">.</span><span \
class="">principal_amount</span><span class="">)</span><span class=""> </span><span \
class="">rpPrin </span><span class="">,</span><span class="">SUM</span><span \
class="">(</span><span class="">rp</span><span class="">.</span><span \
class="">interest_amount</span><span class="">)</span><span class=""> </span><span \
class="">rpInt </span><span class="">,</span><span class="">SUM</span><span \
class="">(</span><span class="">rp</span><span class="">.</span><span \
class="">principal_amount</span><span class=""> </span><span class="">+</span><span \
class=""> </span><span class="">rp</span><span class="">.</span><span \
class="">interest_amount</span><span class="">)</span><span class=""> </span><span \
class="">rpTot </span><span class="">FROM</span><span class=""> </span><span \
class="">m_loan_repayment_schedule</span><span class=""> </span><span class="">rp \
</span><span class="">WHERE</span><span class=""> </span><span \
class="">rp</span><span class="">.</span><span class="">completed_derived</span><span \
class="">=</span><span class="">1 </span><span class="">GROUP</span><span class=""> \
</span><span class="">BY</span><span class=""> </span><span class="">rp</span><span \
class="">.</span><span class="">loan_id</span><span class="">)</span><span class=""> \
</span><span class="">rep</span><span class=""> </span><span class="">ON</span><span \
class=""> </span><span class="">rep</span><span class="">.</span><span \
class="">rpid</span><span class="">=</span><span class="">ml</span><span \
class="">.</span><span class="">id </span><span class="">LEFT</span><span class=""> \
</span><span class="">JOIN</span><span class=""> </span><span \
class="">m_loan_arrears_aging</span><span class=""> </span><span \
class="">laa</span><span class=""> </span><span class="">ON</span><span class=""> \
</span><span class="">laa</span><span class="">.</span><span \
class="">loan_id</span><span class="">=</span><span class="">ml</span><span \
class="">.</span><span class="">id </span><span class="">WHERE</span><span class=""> \
</span><span class="">ml</span><span class="">.</span><span \
class="">loan_status_id</span><span class="">=</span><span class="">300 </span><span \
class="">ORDER</span><span class=""> </span><span class="">BY</span><span class=""> \
</span><span class="">1</span><span class="">,</span><span class="">mc</span><span \
class="">.</span><span class="">gender_cv_id<br> \
<br><br><br></span></code></pre>Regards,<br>Sughosh<br></div><div \
class="gmail_extra"><br><br><div class="gmail_quote">On Mon, May 19, 2014 at 1:52 PM, \
Lajeo Gloria <span dir="ltr"><<a href="mailto:lajeogloria@gmail.com" \
target="_blank">lajeogloria@gmail.com</a>></span> wrote:<br> <blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div dir="ltr"><div><div><div><div>Thanks. <br></div>Looking \
forward to it.<br><br><br><br></div>Thanks<br></div>Regards<br> \
</div>Gloria<br></div><div class="gmail_extra"><br><br><div \
class="gmail_quote"><div><div class="h5">On Mon, May 19, 2014 at 10:56 AM, Sughosh \
Bharadwaj <span dir="ltr"><<a href="mailto:sughosh@confluxtechnologies.com" \
target="_blank">sughosh@confluxtechnologies.com</a>></span> wrote:<br>
</div></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px \
#ccc solid;padding-left:1ex"><div><div class="h5"><div \
dir="ltr"><div><div><div><div>Hi Gloria,<br><br></div>Sorry, I couldn't \
reply.<br> </div>I was caught up with some other issues.<br>
<br></div>I will look into it today and get back to you \
ASAP.<br><br></div>Regards,<br> Sughosh<br></div><div \
class="gmail_extra"><br><br><div class="gmail_quote"><div><div>On Mon, May 19, 2014 \
at 1:23 PM, Lajeo Gloria <span dir="ltr"><<a href="mailto:lajeogloria@gmail.com" \
target="_blank">lajeogloria@gmail.com</a>></span> wrote:<br>
</div></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px \
#ccc solid;padding-left:1ex"><div><div><div dir="ltr">Hi All,<br>I have tried to work \
on the pentaho report for the Loan tracking report following the sql statement scrip \
given by Sughosh but when i try to preview a blank preview is brought.<div>
<br><div class="gmail_quote">
<div dir="ltr"><div><div><div><div>Even the .properties (resource file) is not loaded \
on the report.<br></div>Am stuck now, could you kindly look at my work and fix or \
suggest solutions. Please see attachment <br> \
</div>Thanks<br><br></div>Regards<br></div>Gloria<br><div class="gmail_chip \
gmail_drive_chip" style="width:396px;min-height:18px;max-height:18px;background-color: \
#f5f5f5;padding:5px;color:#222;font-family:arial;font-style:normal;font-weight:bold;font-size:13px;border:1px \
solid #ddd">
<a href="https://docs.google.com/file/d/0B60YCFl5hnYuSTk5aFpEdFJXY3M/edit?usp=drive_web" \
style="display:inline-block;overflow:hidden;text-overflow:ellipsis;white-space:nowrap;text-decoration:none;padding:1px \
0px;border:medium none;width:100%" target="_blank"><img \
style="vertical-align:bottom;border:none" \
src="https://ssl.gstatic.com/docs/doclist/images/icon_10_generic_list.png"> <span \
dir="ltr" style="color:#15c;text-decoration:none;vertical-align:bottom">Loan Tracking \
Sheet.properties</span></a></div>
<br><div class="gmail_chip gmail_drive_chip" \
style="width:396px;min-height:18px;max-height:18px;background-color:#f5f5f5;padding:5p \
x;color:#222;font-family:arial;font-style:normal;font-weight:bold;font-size:13px;border:1px \
solid #ddd">
<a href="https://docs.google.com/file/d/0B60YCFl5hnYub0NtN2hqUGYtUDQ/edit?usp=drive_web" \
style="display:inline-block;overflow:hidden;text-overflow:ellipsis;white-space:nowrap;text-decoration:none;padding:1px \
0px;border:medium none;width:100%" target="_blank"><img \
style="vertical-align:bottom;border:none" \
src="https://ssl.gstatic.com/docs/doclist/images/icon_10_generic_list.png"> <span \
dir="ltr" style="color:#15c;text-decoration:none;vertical-align:bottom">Loan Tracking \
Sheet.prpt</span></a></div>
<br></div>
</div><br></div></div>
<br></div></div>------------------------------------------------------------------------------<br>
"Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE<br>
Instantly run your Selenium tests across 300+ browser/OS combos.<br>
Get unparalleled scalability from the best Selenium testing platform available<br>
Simple to use. Nothing to install. Get started now for free."<br>
<a href="http://p.sf.net/sfu/SauceLabs" \
target="_blank">http://p.sf.net/sfu/SauceLabs</a><br>Mifos-developer mailing list<br> \
<a href="mailto:mifos-developer@lists.sourceforge.net" \
target="_blank">mifos-developer@lists.sourceforge.net</a><br> Unsubscribe or change \
settings at:<br> <a href="https://lists.sourceforge.net/lists/listinfo/mifos-developer" \
target="_blank">https://lists.sourceforge.net/lists/listinfo/mifos-developer</a><br></blockquote></div><br></div>
<br></div></div>------------------------------------------------------------------------------<br>
"Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE<br>
Instantly run your Selenium tests across 300+ browser/OS combos.<br>
Get unparalleled scalability from the best Selenium testing platform available<br>
Simple to use. Nothing to install. Get started now for free."<br>
<a href="http://p.sf.net/sfu/SauceLabs" \
target="_blank">http://p.sf.net/sfu/SauceLabs</a><br>Mifos-developer mailing list<br> \
<a href="mailto:mifos-developer@lists.sourceforge.net" \
target="_blank">mifos-developer@lists.sourceforge.net</a><br> Unsubscribe or change \
settings at:<br> <a href="https://lists.sourceforge.net/lists/listinfo/mifos-developer" \
target="_blank">https://lists.sourceforge.net/lists/listinfo/mifos-developer</a><br></blockquote></div><br></div>
<br>------------------------------------------------------------------------------<br>
"Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE<br>
Instantly run your Selenium tests across 300+ browser/OS combos.<br>
Get unparalleled scalability from the best Selenium testing platform available<br>
Simple to use. Nothing to install. Get started now for free."<br>
<a href="http://p.sf.net/sfu/SauceLabs" \
target="_blank">http://p.sf.net/sfu/SauceLabs</a><br>Mifos-developer mailing list<br> \
<a href="mailto:mifos-developer@lists.sourceforge.net">mifos-developer@lists.sourceforge.net</a><br>
Unsubscribe or change settings at:<br>
<a href="https://lists.sourceforge.net/lists/listinfo/mifos-developer" \
target="_blank">https://lists.sourceforge.net/lists/listinfo/mifos-developer</a><br></blockquote></div><br></div>
------------------------------------------------------------------------------
"Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE
Instantly run your Selenium tests across 300+ browser/OS combos.
Get unparalleled scalability from the best Selenium testing platform available
Simple to use. Nothing to install. Get started now for free."
http://p.sf.net/sfu/SauceLabs
Mifos-developer mailing list
mifos-developer@lists.sourceforge.net
Unsubscribe or change settings at:
https://lists.sourceforge.net/lists/listinfo/mifos-developer
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic