char charSort = chrSortType.charValue();
int intStartParam = intStart.intValue();
int intEndParam = intEnd.intValue();
int beginIndex = 0;
OrderList list = new OrderList();
list.setSortMember(strSortMem);
CVDal cvdl = new CVDal(dataSource);
Collection colList = null;
// order by '"+ sortMember +"' "+sortType
if (charSort == 'A')
{
cvdl.setDynamicQuery("account.getorderlist", "ASC", strSortMem);
}
else
{
cvdl.setDynamicQuery("account.getorderlist", "DESC", strSortMem);
}
String sortType = "ASC";
if (charSort != 'A')
{
sortType = "DESC";
}
String selectQuery = "SELECT cvorder.orderid AS OrderNO, "
+ "cvorder.entityid, entity.name AS Entity, cvorder.created AS date, "
+ "cvorder.total, cvorder.accountmgr, "
+ "CONCAT(indv1.firstname,' ',indv1.lastname) AS salesrep, "
+ "accountingstatus.title AS status, cvorder.discount, cvorder.creator, "
+ "CONCAT(indv2.firstname, ' ', indv2.lastname) AS creator, cvorder.ponumber ";
String fromQuery = "FROM cvorder LEFT OUTER JOIN entity ON "
+ "cvorder.entityid = entity.entityid LEFT OUTER JOIN individual indv1 ON "
+ "cvorder.accountmgr = indv1.individualid LEFT OUTER JOIN individual indv2 ON "
+ "cvorder.creator = indv2.individualid LEFT OUTER JOIN accountingstatus ON "
+ "cvorder.status = accountingstatus.statusid ";
String whereQuery = "WHERE orderstatus != 'Deleted' ";
String orderByQuery = " ORDER BY '" + strSortMem + "' " + sortType;
String listQuery = selectQuery + fromQuery + whereQuery + orderByQuery;
if ((strSearch != null) && strSearch.startsWith("ADVANCE:"))
{
String advSearchstr = strSearch.substring(8);
cvdl.setSqlQueryToNull();
cvdl.setSqlQuery("DROP TABLE IF EXISTS `orderlistsearch`;");
cvdl.executeUpdate();
cvdl.clearParameters();
cvdl.setSqlQueryToNull();
cvdl.setSqlQuery("CREATE TEMPORARY TABLE orderlistsearch " + advSearchstr);
cvdl.executeUpdate();
cvdl.setSqlQueryToNull();
cvdl.clearParameters();
fromQuery = fromQuery + " , orderlistsearch ";
whereQuery = whereQuery + " AND cvorder.orderid = orderlistsearch.orderid ";
}
listQuery = selectQuery + fromQuery + whereQuery + orderByQuery;
cvdl.setSqlQuery(listQuery);
colList = cvdl.executeQuery();
cvdl.clearParameters();
cvdl.setSqlQueryToNull();
cvdl.setSqlQuery("DROP TABLE IF EXISTS orderlistsearch;");
cvdl.executeUpdate();
cvdl.clearParameters();
cvdl.destroy();
if (colList != null)
{
Iterator it = colList.iterator();
int i = 0;
while (it.hasNext())
{
i++;
HashMap hm = (HashMap) it.next();
int id = ((Long) hm.get("OrderNO")).intValue();
try
{
IntMember intListID =
new IntMember("OrderID", id, 10, "", 'T', false, 10);
IntMember intorderNo =
new IntMember("OrderNO", id, 10, "", 'T', true, 10);
OrderListElement listElement = new OrderListElement(id);
StringMember strentityName =
new StringMember("Entity", (String) hm.get("Entity"), 10, "", 'T',
true);
id = ((Long) hm.get("entityid")).intValue();
IntMember entityID =
new IntMember("EntityID", id, 10, "", 'T', false, 10);
DateMember date =
new DateMember("Date",
new Date(((Timestamp) hm.get("date")).getTime()), 10, "", 'T',
false, 1, timezoneid);
DoubleMember total =
new DoubleMember("Total",
new Double(Double.parseDouble(hm.get("total").toString())), 10,
"", 'T', false, 10);
id = ((Long) hm.get("accountmgr")).intValue();
IntMember saleID =
new IntMember("SalesRepID", id, 10, "", 'T', false, 10);
StringMember salesrep =
new StringMember("SalesRep", (String) hm.get("salesrep"), 10, "",
'T', true);
StringMember status =
new StringMember("Status", (String) hm.get("status"), 10, "", 'T',
false);
DoubleMember discount =
new DoubleMember("Discount", (Double) hm.get("Discount"), 10, "",
'T', false, 10);
StringMember creator =
new StringMember("Creator", (String) hm.get("creator"), 10, "",
'T', false);
StringMember poNumber =
new StringMember("PONumber", (String) hm.get("ponumber"), 10, "",
'T', false);
listElement.put("OrderID", intListID);
listElement.put("OrderNO", intorderNo);
listElement.put("Entity", strentityName);
listElement.put("EntityID", entityID);
listElement.put("Date", date);
listElement.put("Total", total);
listElement.put("SalesRepID", saleID);
listElement.put("SalesRep", salesrep);
listElement.put("Status", status);
listElement.put("Discount", discount);
listElement.put("Creator", creator);
listElement.put("PONumber", poNumber);
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();
list.put(s4, listElement);
}
catch (Exception e)
{
System.out.println(" Exception in getOrderList" + e);
//e.printStackTrace();
}
}
}
list.setTotalNoOfRecords(list.size());
list.setListType("Order");
list.setBeginIndex(beginIndex);
list.setEndIndex(list.size());
return list;
}