* @return PaymentLines , invocies for the entity
*/
public PaymentLines getPaymentInvoices(int entityID)
{
CVDal dl = new CVDal(dataSource);
PaymentLines paymentLines= new PaymentLines();
try
{
String q1 = " create temporary table temppayinv select invoice.invoiceid invoiceid,invoice.title invoicetitle ,invoice.invoicedate invoicedate,invoice.total invoicetotal , applypayment.amount amountdue, applypayment.amount amountapplied from invoice, applypayment where 1 = 0";
String q2 = " insert into temppayinv ( invoiceid, invoicetitle, invoicedate, invoicetotal,amountdue ) select invoice.InvoiceID,invoice.title ,invoice.invoicedate, invoice.total ,invoice.total from invoice where invoice.customerid = ? group by invoice.InvoiceID,invoice.title ,invoice.invoicedate, invoice.total";
String q3 = " create temporary table t1 select applypayment.invoiceid, sum(applypayment.amount) as AmountApplied from temppayinv ,applypayment where temppayinv.invoiceid = applypayment.invoiceid and applypayment.linestatus != 'Deleted' group by applypayment.invoiceid ";
//String q4 = " create temporary table t2 select temppayinv.invoiceid, (temppayinv.invoicetotal - sum( applypayment.amount ) ) as AmountDue from applypayment ,temppayinv where applypayment.invoiceid = temppayinv.invoiceid and applypayment.linestatus != 'Deleted' group by temppayinv.invoiceid ";
String q5 = " update temppayinv ,t1 set temppayinv.AmountApplied = t1.AmountApplied where temppayinv.invoiceid = t1.invoiceid ";
//String q6 = " update temppayinv ,t2 set temppayinv.amountdue = t2.AmountDue where temppayinv.invoiceid = t2.invoiceid ";
String q7 = "select invoiceid,invoicetitle,invoicedate,invoicetotal,amountdue,amountapplied from temppayinv where amountapplied = 0";
dl.setSqlQuery(q1);
dl.executeUpdate();
dl.setSqlQueryToNull();
dl.setSqlQuery(q2);
dl.setInt(1,entityID);
dl.executeUpdate();
dl.setSqlQueryToNull();
dl.setSqlQuery(q3);
dl.executeUpdate();
dl.setSqlQueryToNull();
/*dl.setSqlQuery(q4);
dl.executeUpdate();
dl.setSqlQueryToNull();
*/
dl.setSqlQuery(q5);
dl.executeUpdate();
dl.setSqlQueryToNull();
/* dl.setSqlQuery(q6);
dl.executeUpdate();
dl.setSqlQueryToNull();*/
dl.setSqlQuery(q7);
Collection col = dl.executeQuery();
dl.setSqlQueryToNull();
dl.setSqlQuery("drop table temppayinv");
dl.executeUpdate();
dl.setSqlQueryToNull();
dl.setSqlQuery("drop table t1");
dl.executeUpdate();
dl.setSqlQueryToNull();
Iterator it = col.iterator();
int count = 1;
while (it.hasNext())
{
HashMap hm =(HashMap)it.next();
// Long lineID = (Long)hm.get("lineid");
IntMember LineId = new IntMember("LineId",1,10,"",'T',false,20);
IntMember invoiceID = new IntMember("InvoiceId",Integer.parseInt(hm.get("invoiceid").toString()),10,"",'T',false,20);
StringMember invoiceNum = null;
//if(hm.get("invoicetitle") != null)
invoiceNum = new StringMember("InvoiceNum",((Number) hm.get("invoiceid")).toString(),10,"",'T',false);
DateMember invDate = new DateMember( "Date" , (java.sql.Date)hm.get("invoicedate") ,10 , "" , 'T' , false ,1 ,"EST" );
DoubleMember dblTotal = null;
if(hm.get("invoicetotal") != null)
dblTotal = new DoubleMember( "Total" ,new Double(Double.parseDouble(hm.get("invoicetotal").toString())) , 10 , "", 'T' , false , 10 );
DoubleMember dblAppAmt = null;
if(hm.get("amountapplied") != null)
dblAppAmt = new DoubleMember( "AmountApplied" ,new Double(Double.parseDouble(hm.get("amountapplied").toString())) , 10 , "", 'T' , false , 10 );
DoubleMember dblAmtDue = null;
if(hm.get("amountdue") != null)
dblAmtDue = new DoubleMember( "AmountDue" ,new Double(Double.parseDouble(hm.get("amountdue").toString())) , 10 , "", 'T' , false , 10 );
ItemElement ie = new ItemElement(11);
ie.put ("LineId",LineId);
ie.put ("InvoiceId",invoiceID);
ie.put ("InvoiceNum",invoiceNum);
ie.put ("Date",invDate);
ie.put ("Total",dblTotal);
ie.put ("AmountDue",dblAmtDue);
ie.put ("AmountApplied",dblAppAmt);
ie.setLineStatus("Active");
paymentLines.put(""+count,ie);
count ++;
}
}
catch(Exception e)
{