int intStartParam = intStart.intValue();
int intEndParam = intEnd.intValue();
int beginIndex = 0;
InventoryList invList = new InventoryList();
invList.setSortMember(strSortMem);
CVDal cvdl = new CVDal(dataSource);
Collection colList = null;
String sortType = "ASC";
if (charSort == 'D')
{
sortType = "DESC";
}
String selectQuery =
" select ven.entityid as EntityID,inventory.inventoryid as InventoryID,item.title as ItemName,inventory.title as Identifier ,man.name as Manufacturer,ven.name as Vendor";
String fromQuery =
" from inventory left outer join item on inventory.item = item.itemid left outer join entity man on item.manufacturerid = man.entityid left outer join entity ven on item.vendorid = ven.entityid";
String whereQuery = " where inventory.linestatus != 'deleted' ";
String groupByQuery =
" group by EntityID,InventoryID,ItemName,Identifier,Manufacturer,Vendor";
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 inventory.inventoryid like '%" + strSearch
+ "%' OR item.title like '%" + strSearch
+ "%' OR inventory.title like '%" + strSearch
+ "%' OR man.name like '%" + strSearch + "%' OR ven.name like '%"
+ strSearch + "%' ";
}
String advSearchstr = strSearch;
if ((advSearchstr != null) && advSearchstr.startsWith("ADVANCE:"))
{
advSearchstr = advSearchstr.substring(8);
cvdl.setSqlQueryToNull();
cvdl.setSqlQuery("DROP TABLE IF EXISTS `inventorylistsearch`;");
cvdl.executeUpdate();
cvdl.clearParameters();
strSearch = "create TEMPORARY TABLE inventorylistsearch " + advSearchstr;
cvdl.setSqlQuery(strSearch);
cvdl.executeUpdate();
cvdl.setSqlQueryToNull();
cvdl.clearParameters();
fromQuery = fromQuery + " ,inventorylistsearch";
whereQuery =
whereQuery
+ " AND inventory.inventoryid = inventorylistsearch.inventoryid";
}
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 inventorylistsearch");
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 invID = ((Long) hm.get("InventoryID")).intValue();
int entityID = 0;
if ((hm.get("EntityID") != null))
{
entityID = ((Long) hm.get("EntityID")).intValue();
}
try
{
IntMember intInvID =
new IntMember("InventoryID", invID, 10, null, 'T', true, 10);
IntMember intEntityID =
new IntMember("EntityID", entityID, 10, null, 'T', false, 10);
StringMember strItemName = null;
if ((hm.get("ItemName") != null))
{
strItemName =
new StringMember("ItemName", (String) hm.get("ItemName"), 10,
null, 'T', false);
}
else
{
strItemName =
new StringMember("ItemName", null, 10, null, 'T', false);
}
StringMember strIdentifier =
new StringMember("Identifier", (String) hm.get("Identifier"), 10,
null, 'T', false);
StringMember strManufacturer =
new StringMember("Manufacturer", (String) hm.get("Manufacturer"),
10, null, 'T', false);
StringMember strVendor =
new StringMember("Vendor", (String) hm.get("Vendor"), 10, "", 'T',
true);
InventoryListElement invListElement = new InventoryListElement(invID);
invListElement.put("InventoryID", intInvID);
invListElement.put("ItemName", strItemName);
invListElement.put("Identifier", strIdentifier);
invListElement.put("Manufacturer", strManufacturer);
invListElement.put("Vendor", strVendor);
invListElement.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();
invList.put(s4, invListElement);
}
catch (Exception e)
{
System.out.println(" Exception in packing data " + e);
//e.printStackTrace();
}
}
}
invList.setTotalNoOfRecords(invList.size());
invList.setListType("Inventory");
invList.setBeginIndex(beginIndex);
//invList.setEndIndex(endIndex);
invList.setEndIndex(invList.size());
return invList;
}