int intStartParam = intStart.intValue();
int intEndParam = intEnd.intValue();
int beginIndex = 0;
PurchaseOrderList poList = new PurchaseOrderList();
poList.setSortMember(strSortMem);
CVDal cvdl = new CVDal(dataSource);
Collection colList = null;
String sortType = "ASC";
if (charSort == 'D')
{
sortType = "DESC";
}
String selectQuery =
" select purchaseorder.PurchaseOrderID,purchaseorder.purchaseorderdate as Created, individual.IndividualID, concat(individual.firstname,' ',individual.lastname) as Creator,entity.EntityID,entity.name as Entity ,purchaseorder.SubTotal,purchaseorder.Tax, purchaseorder.Total, accstatus.title as Status";
String fromQuery =
" from purchaseorder left outer join individual on purchaseorder.creator = individual.individualid left outer join entity on purchaseorder.entity = entity.entityid left outer join accountingstatus accstatus on purchaseorder.status = accstatus.statusid";
String whereQuery =
" where 1=1 and purchaseorder.purchaseorderstatus != 'Deleted' ";
String groupByQuery =
" group by purchaseorder.PurchaseOrderID,purchaseorder.Created,Creator,Entity,purchaseorder.SubTotal,purchaseorder.Tax, purchaseorder.Total ,Status";
String orderByQuery = " order by " + strSortMem + " " + sortType;
String listQuery =
selectQuery + fromQuery + whereQuery + groupByQuery + orderByQuery;
if ((strSearch != null) && strSearch.startsWith("SIMPLE :"))
{
strSearch = strSearch.substring(8);
whereQuery =
whereQuery + "AND purchaseorder.PurchaseOrderID like '%" + strSearch
+ "%' OR purchaseorder.Created like '%" + strSearch
+ "%' OR concat(individual.firstname,' ',individual.lastname) like '%"
+ strSearch + "%' OR entity.name like '%" + strSearch
+ "%' OR purchaseorder.SubTotal like '%" + strSearch
+ "%' OR purchaseorder.Tax like '%" + strSearch
+ "%' OR purchaseorder.Total like '%" + strSearch
+ "%' OR accstatus.title like '%" + strSearch + "%' ";
}
String advSearchstr = strSearch;
if ((advSearchstr != null) && advSearchstr.startsWith("ADVANCE:"))
{
advSearchstr = advSearchstr.substring(8);
cvdl.setSqlQueryToNull();
cvdl.setSqlQuery("DROP TABLE IF EXISTS `purchaseorderlistsearch`;");
cvdl.executeUpdate();
cvdl.clearParameters();
strSearch =
"create TEMPORARY TABLE purchaseorderlistsearch " + advSearchstr;
cvdl.setSqlQuery(strSearch);
cvdl.executeUpdate();
cvdl.setSqlQueryToNull();
cvdl.clearParameters();
fromQuery = fromQuery + " ,purchaseorderlistsearch";
whereQuery =
whereQuery
+ " AND purchaseorder.PurchaseOrderID = purchaseorderlistsearch.PurchaseOrderID";
}
listQuery =
selectQuery + fromQuery + whereQuery + groupByQuery + orderByQuery;
cvdl.setSqlQuery(listQuery);
colList = cvdl.executeQuery();
cvdl.clearParameters();
if ((advSearchstr != null) && advSearchstr.startsWith("ADVANCE:"))
{
cvdl.setSqlQueryToNull();
cvdl.setSqlQuery("DROP TABLE purchaseorderlistsearch");
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 poID = Integer.parseInt((hm.get("PurchaseOrderID")).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 subTotal = null;
if (hm.get("SubTotal") != null)
{
subTotal =
new Double(Double.parseDouble((hm.get("SubTotal")).toString()));
}
Double tax = null;
if (hm.get("Tax") != null)
{
tax = new Double(Double.parseDouble((hm.get("Tax")).toString()));
}
Double total = null;
if (hm.get("Total") != null)
{
total = new Double(Double.parseDouble((hm.get("Total")).toString()));
}
try
{
IntMember intPOID =
new IntMember("PurchaseOrderID", poID, 10, null, 'T', true, 10);
IntMember intIndID =
new IntMember("IndividualID", indID, 10, null, 'T', true, 10);
IntMember intEntityID =
new IntMember("EntityID", entityID, 10, null, 'T', true, 10);
DoubleMember dblSubTotal =
new DoubleMember("SubTotal", subTotal, 10, "", 'T', false, 10);
DoubleMember dblTax =
new DoubleMember("Tax", tax, 10, "", 'T', false, 10);
DoubleMember dblTotal =
new DoubleMember("Total", total, 10, "", 'T', false, 10);
DateMember date =
new DateMember("Created", (Date) hm.get("Created"), 10, "", 'T',
false, 1, "EST");
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);
}
StringMember strStatus =
new StringMember("Status", (String) hm.get("Status"), 10, null,
'T', false);
StringMember strCreator = null;
if ((hm.get("Creator") != null))
{
strCreator =
new StringMember("Creator", (String) hm.get("Creator"), 10, null,
'T', true);
}
else
{
strCreator =
new StringMember("Creator", null, 10, null, 'T', false);
}
PurchaseOrderListElement poListElement =
new PurchaseOrderListElement(poID);
poListElement.put("PurchaseOrderID", intPOID);
poListElement.put("SubTotal", dblSubTotal);
poListElement.put("Tax", dblTax);
poListElement.put("Total", dblTotal);
poListElement.put("Created", date);
poListElement.put("Entity", strEntity);
poListElement.put("Status", strStatus);
poListElement.put("Creator", strCreator);
poListElement.put("IndividualID", intIndID);
poListElement.put("EntityID", intEntityID);
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();
poList.put(s4, poListElement);
}
catch (Exception e)
{
System.out.println(" Exception in packing data " + e);
//e.printStackTrace();
}
}
}
poList.setTotalNoOfRecords(poList.size());
poList.setListType("PurchaseOrder");
poList.setBeginIndex(beginIndex);
//poList.setEndIndex(endIndex);
poList.setEndIndex(poList.size());
return poList;
}