int intEndParam = intEnd.intValue();
int beginIndex = Math.max(intStartParam - 100, 1);
int endindex = intEndParam + 100;
TicketList ticketList = new TicketList();
ticketList.setSortMember(strSortMem);
CVDal cvdl = new CVDal(dataSource);
Collection colList = null;
String sortOn = strSortMem;
if (sortOn != null && sortOn.equals("AssignedTo"))
{
sortOn = "assignindv";
}
if (sortOn != null && sortOn.equals("Number"))
{
sortOn = "ticketid";
}
if (strSearch != null && strSearch.startsWith("ADVANCE:"))
{
strSearch = strSearch.substring(8);
String str = "create TEMPORARY TABLE ticketlistSearch " + strSearch;
cvdl.setSqlQueryToNull();
cvdl.setSqlQuery(str);
cvdl.executeUpdate();
cvdl.clearParameters();
String strQuery = "";
String sortType = "ASC";
if (charSort == 'A')
{
sortType = "ASC";
} else {
sortType = "DESC";
}
strQuery = "select tick.ticketid as ticketid,tick.subject as subject,en.name as entity, tick.entityid as entityid,tick.individualid " + "as individualid,tick.created as dateopened,tick.assignedto as assignindv,supstat.name as status, " + "tick.dateclosed as dateclosed,concat(indv.FirstName,' ',indv.LastName) as assignedto from ticket tick "
+ "left outer join entity en on en.entityid=tick.entityid " + "left outer join individual indv on indv.individualid=tick.assignedto,supportstatus supstat,ticketlistSearch tsearch " + "where tick.status=supstat.statusid and tsearch.ticketid=tick.ticketid order by '" + sortOn + "' " + sortType;
cvdl.setSqlQueryToNull();
cvdl.setSqlQuery(strQuery);
colList = cvdl.executeQuery();
cvdl.clearParameters();
cvdl.setSqlQueryToNull();
cvdl.setSqlQuery("DROP TABLE ticketlistSearch");
cvdl.executeUpdate();
ticketList.setTotalNoOfRecords(colList.size());
} else {
String sortType = "ASC";
if (charSort == 'A')
{
sortType = "ASC";
} else {
sortType = "DESC";
}
String query = "SELECT tick.ticketid AS ticketid, tick.subject AS subject, en.name AS entity, " + "tick.entityid AS entityid,tick.individualid AS individualid,tick.created AS " + " dateopened, tick.assignedto AS assignindv, supstat.name AS status, tick.dateclosed "
+ " AS dateclosed, concat(indv.FirstName,' ',indv.LastName) AS assignedto FROM ticket " + " tick LEFT OUTER JOIN entity en ON en.entityid=tick.entityid LEFT OUTER JOIN individual " + " indv ON indv.individualid=tick.assignedto LEFT OUTER JOIN supportstatus supstat ON "
+ " tick.status=supstat.statusid WHERE tick.owner = ? UNION SELECT tick.ticketid AS " + " ticketid,tick.subject AS subject,en.name AS entity, tick.entityid AS entityid," + " tick.individualid AS individualid,tick.created AS dateopened, tick.assignedto AS " + " assignindv,supstat.name AS status,tick.dateclosed AS dateclosed, "
+ " concat(indv.FirstName,' ',indv.LastName) AS assignedto FROM ticket tick, " + " recordauthorisation b LEFT OUTER JOIN entity en ON en.entityid=tick.entityid " + " LEFT OUTER JOIN individual indv ON indv.individualid=tick.assignedto LEFT OUTER " + " JOIN supportstatus supstat ON tick.status=supstat.statusid WHERE tick.ticketid = "
+ " b.recordid and b.recordtypeid = 39 and b.privilegelevel < 40 and b.privilegelevel > 0 " + " and b.individualID = ? UNION SELECT tick.ticketid AS ticketid,tick.subject AS subject," + " en.name AS entity, tick.entityid AS entityid,tick.individualid AS individualid,"
+ " tick.created AS dateopened, tick.assignedto AS assignindv,supstat.name AS status," + " tick.dateclosed AS dateclosed,concat(indv.FirstName,' ',indv.LastName) AS assignedto " + " FROM ticket AS tick INNER JOIN publicrecords pub ON tick.ticketid = pub.recordid LEFT "
+ " OUTER JOIN entity en ON en.entityid=tick.entityid LEFT OUTER JOIN individual indv ON " + " indv.individualid=tick.assignedto LEFT OUTER JOIN supportstatus supstat ON " + " tick.status=supstat.statusid WHERE pub.moduleid=39 order by '" + sortOn + "' " + sortType;
cvdl.setSqlQuery(query);
cvdl.setInt(1, individualId);
cvdl.setInt(2, individualId);
colList = cvdl.executeQuery();
cvdl.setSqlQueryToNull();
cvdl.setSql("support.ticket.allticketcount");
Collection count = cvdl.executeQuery();
Iterator itCount = count.iterator();
HashMap hmx = (HashMap)itCount.next();
Integer endCount = (Integer)hmx.get("allticketcount");
cvdl.clearParameters();
int totalCount = endCount.intValue();
ticketList.setTotalNoOfRecords(totalCount);
}
cvdl.destroy();
if (colList.size() > 0)
{
Iterator it = colList.iterator();
int i = 0;
while (it.hasNext())
{
i++;
HashMap hm = (HashMap)it.next();
int ticketID = ((Long)hm.get("ticketid")).intValue();
try
{
IntMember intTicketID = new IntMember("TicketID", ticketID, 10, "", 'T', false, 10);
StringMember strSubject = null;
if ((hm.get("subject") != null))
{
strSubject = new StringMember("Subject", (String)hm.get("subject"), 10, "", 'T', true);
} else {
strSubject = new StringMember("Subject", null, 10, "", 'T', true);
}
StringMember strStatus = new StringMember("Status", (String)hm.get("status"), 10, "", 'T', false);
StringMember strAssignedTo = new StringMember("AssignedTo", (String)hm.get("assignedto"), 10, "", 'T', true);
StringMember strEntity = new StringMember("Entity", (String)hm.get("entity"), 10, "", 'T', true);
IntMember intIndividualID = null;
IntMember intEntityID = null;
if ((hm.get("assignindv") != null))
{
intIndividualID = new IntMember("IndividualID", ((Long)hm.get("assignindv")).intValue(), 10, "", 'T', false, 10);
} else {
intIndividualID = new IntMember("IndividualID", 0, 10, "", 'T', false, 10);
}
if ((hm.get("entityid") != null))
{
intEntityID = new IntMember("EntityID", ((Long)hm.get("entityid")).intValue(), 10, "", 'T', false, 10);
} else {
intEntityID = new IntMember("EntityID", 0, 10, "", 'T', false, 10);
}
Timestamp dtDateOpened = null;
DateMember dmDateOpened = null;
String timezoneid = "EST";
if ((hm.get("dateopened") != null))
{
dtDateOpened = (Timestamp)hm.get("dateopened");
dmDateOpened = new DateMember("DateOpened", dtDateOpened, 10, "", 'T', false, 1, timezoneid);
} else {
dmDateOpened = new DateMember("DateOpened", null, 10, "", 'T', false, 1, timezoneid);
}
Timestamp dtDateClosed = null;
DateMember dmDateClosed = null;
if ((hm.get("dateclosed") != null))
{
dtDateClosed = (Timestamp)hm.get("dateclosed");
dmDateClosed = new DateMember("DateClosed", dtDateClosed, 10, "", 'T', false, 1, timezoneid);
} else {
dmDateClosed = new DateMember("DateClosed", null, 10, "", 'T', false, 1, timezoneid);
}
TicketListElement ticketlistelement = new TicketListElement(ticketID);
ticketlistelement.put("TicketID", intTicketID);
ticketlistelement.put("Subject", strSubject);
ticketlistelement.put("DateOpened", dmDateOpened);
ticketlistelement.put("Status", strStatus);
ticketlistelement.put("DateClosed", dmDateClosed);
ticketlistelement.put("AssignedTo", strAssignedTo);
ticketlistelement.put("Entity", strEntity);
ticketlistelement.put("IndividualID", intIndividualID);
ticketlistelement.put("EntityID", intEntityID);
ticketlistelement.put("Number", intTicketID);
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();
ticketList.put(s4, ticketlistelement);
} catch (Exception e) {
logger.debug(" [Exception] SupportListEJB.getTicketList " + e);
}
}
}
ticketList.setListType("Ticket");
ticketList.setBeginIndex(beginIndex);
ticketList.setEndIndex(ticketList.size());
return ticketList;
}