int intStartParam = intStart.intValue();
int intEndParam = intEnd.intValue();
int beginIndex = 0;
ExpenseList expList = new ExpenseList();
expList.setSortMember(strSortMem);
CVDal cvdl = new CVDal(dataSource);
Collection colList = null;
String sortType = "ASC";
if (charSort == 'D')
{
sortType = "DESC";
}
String selectQuery =
" select expense.ExpenseID,expense.Amount,expense.Created,entity.name as Reference ,expense.Status, concat(individual.firstname,' ',individual.lastname) as Creator,individual.IndividualID";
String fromQuery =
" from expense left outer join individual on expense.owner = individual.individualid left outer join entity on expense.entityid = entity.entityid";
String whereQuery = " where expense.linestatus != 'deleted'";
String groupByQuery =
" group by expense.ExpenseID,expense.Amount,expense.Created,Reference ,expense.Status, Creator";
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 expense.ExpenseID like '%" + strSearch
+ "%' OR expense.Amount like '%" + strSearch
+ "%' OR expense.Created like '%" + strSearch
+ "%' OR entity.name like '%" + strSearch
+ "%' OR expense.Status like '%" + strSearch
+ "%' OR concat(individual.firstname,' ',individual.lastname) like'%"
+ strSearch + "%' ";
}
String advSearchstr = strSearch;
if ((advSearchstr != null) && advSearchstr.startsWith("ADVANCE:"))
{
String tmpAdvSearchstr = advSearchstr.substring(8);
cvdl.setSqlQueryToNull();
cvdl.setSqlQuery("DROP TABLE IF EXISTS `expenselistsearch`;");
cvdl.executeUpdate();
cvdl.clearParameters();
cvdl.setSqlQueryToNull();
strSearch = "create TEMPORARY TABLE expenselistsearch " + tmpAdvSearchstr;
cvdl.setSqlQuery(strSearch);
cvdl.executeUpdate();
cvdl.setSqlQueryToNull();
cvdl.clearParameters();
fromQuery = fromQuery + " ,expenselistsearch";
whereQuery =
whereQuery + " AND expense.ExpenseID = expenselistsearch.ExpenseID";
}
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 expenselistsearch");
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 expenseID = Integer.parseInt(hm.get("ExpenseID").toString());
int indID = 0;
if ((hm.get("IndividualID") != null))
{
indID = Integer.parseInt(hm.get("IndividualID").toString());
}
Double amount = null;
if (hm.get("Amount") != null)
{
amount = new Double(Double.parseDouble(hm.get("Amount").toString()));
}
try
{
IntMember intExpID =
new IntMember("ExpenseID", expenseID, 10, null, 'T', true, 10);
IntMember intIndID =
new IntMember("IndividualID", indID, 10, null, 'T', false, 10);
DoubleMember dblAmount =
new DoubleMember("Amount", amount, 10, "", 'T', false, 10);
DateMember submitedDt =
new DateMember("Created", (Date) hm.get("Created"), 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 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);
}
ExpenseListElement expListElement = new ExpenseListElement(expenseID);
expListElement.put("ExpenseID", intExpID);
expListElement.put("Amount", dblAmount);
expListElement.put("Created", submitedDt);
expListElement.put("Reference", strReference);
expListElement.put("Status", strStatus);
expListElement.put("Creator", strCreator);
expListElement.put("IndividualID", intIndID);
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();
expList.put(s4, expListElement);
}
catch (Exception e)
{
System.out.println(" Exception in packing data " + e);
//e.printStackTrace();
}
}
}
expList.setTotalNoOfRecords(expList.size());
expList.setListType("Expense");
expList.setBeginIndex(beginIndex);
//expList.setEndIndex(endIndex);
expList.setEndIndex(expList.size());
return expList;
}