if (hashmap.get("InvoiceID") != null)
{
invoiceID = Integer.parseInt(hashmap.get("InvoiceID").toString());
}
PaymentList paymentList = new PaymentList();
paymentList.setSortMember(strSortMem);
CVDal cvdl = new CVDal(dataSource);
//Create Temp table to store AmountApplied and amount unapplied
cvdl.setSqlQuery(
"create TEMPORARY table temppayment select PaymentID ,sum(amount) as AppliedAmount from applypayment group by PaymentID ");
cvdl.executeUpdate();
cvdl.setSqlQueryToNull();
cvdl.clearParameters();
Collection colList = null;
String sortType = "ASC";
if (charSort == 'D')
{
sortType = "DESC";
}
String selectQuery =
"select payment.PaymentID, entity.EntityID,entity.name as Entity,payment.amount as AmountPaid,temppayment.AppliedAmount , ( payment.amount - temppayment.AppliedAmount ) as UnAppliedAmount,payment.created as PaymentDate,pm.title as PaymentMethod,payment.Reference, individual.IndividualID ,concat(individual.firstName , ' ',individual.lastname) as EnteredBy ";
String fromQuery =
" from payment left outer join paymentmethod pm on payment.paymentmethod = pm.methodid left outer join individual on individual.individualid = payment.creator left outer join entity on entity.entityid = payment.entityid left outer join temppayment on temppayment.PaymentID = payment.PaymentID";
String whereQuery = " where payment.linestatus != 'deleted'";
String groupByQuery =
" group by PaymentID, EntityID,Entity, AmountPaid ,AppliedAmount,UnAppliedAmount,PaymentDate, PaymentMethod,Reference, EnteredBy";
String orderByQuery = " order by '" + strSortMem + "' " + sortType;
if (invoiceID > 0)
{
fromQuery =
fromQuery
+ " inner join applypayment on payment.paymentid = applypayment.paymentid";
whereQuery = whereQuery + " AND applypayment.invoiceid = " + invoiceID;
}
String listQuery =
selectQuery + fromQuery + whereQuery + groupByQuery + orderByQuery;
if ((strSearch != null) && strSearch.startsWith("SIMPLE :"))
{
strSearch = strSearch.substring(8);
whereQuery =
whereQuery + "AND payment.PaymentID like '%" + strSearch
+ "%' OR entity.name like '%" + strSearch
+ "%' OR payment.amount like '%" + strSearch
+ "%' OR payment.Reference like '%" + strSearch
+ "%' OR payment.created like '%" + strSearch
+ "%' OR pm.title like '%" + strSearch
+ "%' OR concat(individual.firstName , ' ',individual.lastname) like '%"
+ strSearch + "%' OR temppayment.AppliedAmount like '%" + strSearch
+ "%' OR ( payment.amount - temppayment.AppliedAmount ) like '%"
+ strSearch + "%'";
}
String advSearchstr = strSearch;
if ((advSearchstr != null) && advSearchstr.startsWith("ADVANCE:"))
{
advSearchstr = advSearchstr.substring(8);
cvdl.setSqlQueryToNull();
cvdl.setSqlQuery("DROP TABLE IF EXISTS `paymentlistsearch`;");
cvdl.executeUpdate();
cvdl.clearParameters();
cvdl.setSqlQueryToNull();
strSearch = "create TEMPORARY TABLE paymentlistsearch " + advSearchstr;
cvdl.setSqlQuery(strSearch);
cvdl.executeUpdate();
cvdl.setSqlQueryToNull();
cvdl.clearParameters();
fromQuery = fromQuery + " ,paymentlistsearch";
whereQuery =
whereQuery + " AND payment.PaymentID = paymentlistsearch.PaymentID";
}
listQuery =
selectQuery + fromQuery + whereQuery + groupByQuery + orderByQuery;
cvdl.setSqlQuery(listQuery);
colList = cvdl.executeQuery();
cvdl.clearParameters();
cvdl.setSqlQueryToNull();
cvdl.setSqlQuery("DROP TABLE IF EXISTS paymentlistsearch");
cvdl.executeUpdate();
cvdl.clearParameters();
cvdl.setSqlQueryToNull();
cvdl.setSqlQuery("DROP TABLE temppayment");
cvdl.executeUpdate();
cvdl.clearParameters();
cvdl.destroy();
//System.out.println("colList " +colList);
if (colList != null)
{
Iterator it = colList.iterator();
int i = 0;
while (it.hasNext())
{
i++;
HashMap hm = (HashMap) it.next();
int paymentID = Integer.parseInt(hm.get("PaymentID").toString());
int indID = 0;
if ((hm.get("IndividualID") != null))
{
indID = Integer.parseInt(hm.get("IndividualID").toString());
}
int entityID = 0;
if ((hm.get("EntityID") != null))
{
entityID = Integer.parseInt(hm.get("EntityID").toString());
}
Double amount = null;
if (hm.get("AmountPaid") != null)
{
amount =
new Double(Double.parseDouble(hm.get("AmountPaid").toString()));
}
Double appliedAmt = null;
if (hm.get("AppliedAmount") != null)
{
appliedAmt =
new Double(Double.parseDouble(hm.get("AppliedAmount").toString()));
}
Double unAppliedAmt = null;
if (hm.get("UnAppliedAmount") != null)
{
unAppliedAmt =
new Double(Double.parseDouble(hm.get("UnAppliedAmount").toString()));
}
try
{
IntMember intPayID =
new IntMember("PaymentID", paymentID, 10, null, 'T', true, 10);
IntMember intIndID =
new IntMember("IndividualID", indID, 10, null, 'T', false, 10);
IntMember intEntID =
new IntMember("EntityID", entityID, 10, null, 'T', false, 10);
DoubleMember dblAmount =
new DoubleMember("AmountPaid", amount, 10, "", 'T', false, 10);
DoubleMember dblAppAmt =
new DoubleMember("AppliedAmount", appliedAmt, 10, "", 'T', false, 10);
DoubleMember dblUnAppAmt =
new DoubleMember("UnAppliedAmount", unAppliedAmt, 10, "", 'T',
false, 10);
DateMember paymentDt =
new DateMember("PaymentDate", (Date) hm.get("PaymentDate"), 10, "",
'T', false, 1, "EST");
StringMember strReference = null;
if ((hm.get("Reference") != null))
{
strReference =
new StringMember("Reference", (String) hm.get("Reference"), 10,
null, 'T', false);
}
else
{
strReference =
new StringMember("Reference", null, 10, null, 'T', false);
}
StringMember strMethod =
new StringMember("PaymentMethod", (String) hm.get("PaymentMethod"),
10, null, 'T', false);
StringMember strEnteredBy = null;
if ((hm.get("EnteredBy") != null))
{
strEnteredBy =
new StringMember("EnteredBy", (String) hm.get("EnteredBy"), 10,
null, 'T', true);
}
else
{
strEnteredBy =
new StringMember("EnteredBy", null, 10, null, 'T', false);
}
StringMember strEntity = null;
if ((hm.get("Entity") != null))
{
strEntity =
new StringMember("Entity", (String) hm.get("Entity"), 10, null,
'T', true);
}
else
{
strEntity = new StringMember("Entity", null, 10, null, 'T', false);
}
PaymentListElement payListElement = new PaymentListElement(paymentID);
payListElement.put("PaymentID", intPayID);
payListElement.put("AmountPaid", dblAmount);
payListElement.put("AppliedAmount", dblAppAmt);
payListElement.put("UnAppliedAmount", dblUnAppAmt);
payListElement.put("PaymentDate", paymentDt);
payListElement.put("Reference", strReference);
payListElement.put("PaymentMethod", strMethod);
payListElement.put("EnteredBy", strEnteredBy);
payListElement.put("Entity", strEntity);
payListElement.put("IndividualID", intIndID);
payListElement.put("EntityID", intEntID);
StringBuffer stringbuffer = new StringBuffer("00000000000");
stringbuffer.setLength(11);
String s3 = (new Integer(i)).toString();
stringbuffer.replace(stringbuffer.length() - s3.length(),
stringbuffer.length(), s3);
String s4 = stringbuffer.toString();
paymentList.put(s4, payListElement);
}
catch (Exception e)
{
System.out.println(" Exception in packing data of Payment" + e);
//e.printStackTrace();
}
}
}
paymentList.setTotalNoOfRecords(paymentList.size());
paymentList.setListType("Payment");
paymentList.setBeginIndex(beginIndex);
//paymentList.setEndIndex(endIndex);
paymentList.setEndIndex(paymentList.size());
return paymentList;
}