int intStartParam = intStart.intValue();
int intEndParam = intEnd.intValue();
int beginIndex = 0;
GLAccountList glList = new GLAccountList();
glList.setSortMember(strSortMem);
CVDal cvdl = new CVDal(dataSource);
Collection colList = null;
String sortType = "ASC";
if (charSort == 'D')
{
sortType = "DESC";
}
//SELECT gla.glaccountsid GLAccountsID,gla.title Name ,gla.accounttype Type ,gla.Balance Balance ,gla1.title ParentAccount FROM glaccount gla left outer join glaccount gla1 on gla.parent = gla1.glaccountsid
String selectQuery =
" SELECT gla.glaccountsid GLAccountsID,gla.title Name ,gla.glaccounttype Type ,gla.Balance Balance ,gla1.title ParentAccount ";
String fromQuery =
" FROM glaccount gla left outer join glaccount gla1 on gla.parent = gla1.glaccountsid";
String whereQuery = " where 1=1 ";
String groupByQuery =
" group by GLAccountsID,Name,Type,Balance,ParentAccount";
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 gla.glaccountsid like '%" + strSearch
+ "%' OR gla.title like '%" + strSearch
+ "%' OR gla.accounttype like '%" + strSearch
+ "%' OR gla.Balance like '%" + strSearch + "%' OR gla1.title like '%"
+ strSearch + "%' ";
}
String advSearchstr = strSearch;
if ((advSearchstr != null) && advSearchstr.startsWith("ADVANCE:"))
{
advSearchstr = advSearchstr.substring(8);
cvdl.setSqlQueryToNull();
cvdl.setSqlQuery("DROP TABLE IF EXISTS `glaccountlistsearch`;");
cvdl.executeUpdate();
cvdl.clearParameters();
strSearch = "create TEMPORARY TABLE glaccountlistsearch " + advSearchstr;
cvdl.setSqlQuery(strSearch);
cvdl.executeUpdate();
cvdl.setSqlQueryToNull();
cvdl.clearParameters();
fromQuery = fromQuery + " ,glaccountlistsearch";
whereQuery =
whereQuery + " AND gla.glaccountsid = glaccountlistsearch.glaccountsid";
}
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 glaccountlistsearch");
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 glAccID = ((Long) hm.get("GLAccountsID")).intValue();
String strGLAccountID = new String();
strGLAccountID = String.valueOf(glAccID);
try
{
StringMember strName = null;
if ((hm.get("Name") != null))
{
strName =
new StringMember("Name", (String) hm.get("Name"), 10, "", 'T',
false);
}
else
{
strName = new StringMember("Name", null, 10, "", 'T', false);
}
StringMember strType =
new StringMember("Type", (String) hm.get("Type"), 10, "", 'T', false);
DoubleMember dblBalance =
new DoubleMember("Balance",
new Double(Double.parseDouble(hm.get("Balance").toString())), 10,
"", 'T', false, 10);
StringMember strParentAccount =
new StringMember("ParentAccount", (String) hm.get("ParentAccount"),
10, "", 'T', false);
GLAccountListElement glListElement =
new GLAccountListElement(glAccID);
glListElement.put("GLAccountsID", strGLAccountID);
glListElement.put("Name", strName);
glListElement.put("Type", strType);
glListElement.put("Balance", dblBalance);
glListElement.put("ParentAccount", strParentAccount);
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();
glList.put(s4, glListElement);
}
catch (Exception e)
{
System.out.println(" Exception in packing data " + e);
//e.printStackTrace();
}
}
}
glList.setTotalNoOfRecords(glList.size());
glList.setListType("GLAccount");
glList.setBeginIndex(beginIndex);
//filelist.setEndIndex(endIndex);
glList.setEndIndex(glList.size());
return glList;
}