if (preference != null)
{
advSearchstr = (String)preference.get("ADVANCESEARCHSTRING");
}
EmployeeList DL = new EmployeeList();
DL.setSortMember("Name");
CVDal cvdl = new CVDal(dataSource);
cvdl.setSqlQueryToNull();
cvdl.setSqlQuery("DROP TABLE IF EXISTS individuallist");
cvdl.executeUpdate();
cvdl.setSqlQueryToNull();
cvdl.setSqlQuery("DROP TABLE IF EXISTS individuallistSearch");
cvdl.executeUpdate();
cvdl.setSqlQuery("CREATE TEMPORARY TABLE individuallist "+
" SELECT i.IndividualID AS individualID, i.List AS dbid, i.Entity AS EntityID, CONCAT(i.FirstName, ' ', i.LastName) AS Name, i.FirstName, i.MiddleInitial, "+
"i.LastName, i.Title, e.Name AS Entity, a.Street1, a.Street2, a.City, a.State, "+
"a.Zip, a.Country, moc.Content AS Phone, moc.Content AS Email, moc.Content AS Fax "+
"FROM individual i LEFT OUTER JOIN entity e ON (i.Entity=e.EntityID) LEFT OUTER "+
"JOIN addressrelate ar ON (i.IndividualID=ar.Contact) LEFT OUTER JOIN address a ON "+
"(ar.Address=a.AddressID) LEFT OUTER JOIN methodofcontact moc ON (moc.MOCID=0) , employee "+
"WHERE i.IndividualID = employee.IndividualID AND ar.contacttype = 2");
cvdl.executeUpdate();
cvdl.clearParameters();
cvdl.setSql("contact.individuallistupdate1");
cvdl.executeUpdate();
cvdl.clearParameters();
cvdl.setSql("contact.individuallistupdate2");
cvdl.executeUpdate();
cvdl.clearParameters();
cvdl.setSql("contact.individuallistupdate3");
cvdl.executeUpdate();
cvdl.clearParameters();
/*
cvdl.setSql("contact.individuallistupdate4");
cvdl.executeUpdate();
cvdl.clearParameters();
cvdl.setSql("contact.individuallistupdate5");
cvdl.executeUpdate();
cvdl.clearParameters();
*/
Collection v = null;
/* Added for Advance Search */
if (advSearchstr != null && advSearchstr.startsWith("ADVANCE:"))
{
advSearchstr = advSearchstr.substring(8);
String str = "create TEMPORARY TABLE individuallistSearch "+advSearchstr;
cvdl.setSqlQueryToNull();
cvdl.setSqlQuery(str);
cvdl.executeUpdate();
cvdl.clearParameters();
// If some field change then change also ContactListEjb
str = "Select individuallist.individualID ,individuallist.EntityID,concat(individuallist.FirstName ,' ', individuallist.LastName) Name, individuallist.FirstName, individuallist.MiddleInitial, individuallist.LastName, individuallist.Title, individuallist.Entity, individuallist.Phone ,individuallist.Email, individuallist.Fax,concat(individuallist.Street1,' ',individuallist.Street2,' ',individuallist.City ,' ', individuallist.State ,' ' , individuallist.Zip , ' ',individuallist.Country ) Address from individuallist ,individuallistSearch where individuallist.individualID = individuallistSearch.individualID";
cvdl.setSqlQueryToNull();
cvdl.setSqlQuery(str);
v = cvdl.executeQuery();
cvdl.setSqlQueryToNull();
cvdl.setSqlQuery("DROP TABLE individuallistSearch");
cvdl.executeUpdate();
}
else
{
cvdl.setSql("contact.individuallistselect");
v = cvdl.executeQuery();
cvdl.clearParameters();
}
cvdl.setSql("contact.individuallistdroptable");
cvdl.executeUpdate();
cvdl.clearParameters();
cvdl.destroy();
Iterator it = v.iterator();
while( it.hasNext() )
{
HashMap hm = ( HashMap )it.next();
//int EntityID = ((Integer)hm.get("EntityID")).intValue();
String IndividualName = (String)hm.get( "Name" );
int IndividualID = ((Long)hm.get("individualID")).intValue();
int entId = ((Long)hm.get("EntityID")).intValue();
IntMember intmem = new IntMember( "IndividualID" , IndividualID , 10 , "", 'T' , false , 10 );
IntMember entityId = new IntMember( "EntityID", entId ,10 , "" , 'T' , false,10 );
StringMember one = new StringMember( "Name", (String) hm.get( "Name" ) ,10 , "/centraview/ViewHandler.do?" + Constants.TYPEOFCONTACT + "=" + Constants.INDIVIDUAL + "&rowId=" + IndividualID , 'T' , true );
//Added by Parshruam
StringMember fname = new StringMember( "FirstName", (String)hm.get( "FirstName" ) ,10 , "" , 'T' , false );
StringMember lName = new StringMember( "LastName", (String)hm.get( "LastName" ) ,10 , "" , 'T' , false );
StringMember mName = new StringMember( "MiddleInitial", (String)hm.get( "MiddleInitial" ) ,10 , "" , 'T' , false );
StringMember two = new StringMember( "Title", (String)hm.get( "Title" ) ,10 , "" , 'T' , false );
StringMember three= new StringMember( "Company",(String)hm.get( "Entity" ) ,10 , "/centraview/ViewHandler.do?" + Constants.TYPEOFCONTACT + "=" + Constants.INDIVIDUAL + "&rowId=" + IndividualID , 'T' , true );
StringMember four = new StringMember( "Phone" , (String)hm.get( "Phone" ) ,10 , " " , 'T' ,false );
StringMember five = new StringMember( "Fax" , (String)hm.get( "Fax" ) ,10 , "#" , 'T' , false );
StringMember six = new StringMember( "Email", (String)hm.get( "Email" ) ,10 , "/centraview/jsp/common/MailCompose.jsp" , 'T' , true );
IndividualListElement ele = new IndividualListElement( IndividualID );
ele.put( "IndividualID", intmem );
ele.put( "EntityID", entityId );
ele.put( "Name", one );
ele.put( "Title" , two );
// ele.put( "Entity" , three );
ele.put( "Company" , three );
ele.put( "Phone", four );
ele.put( "Fax" , five );
ele.put( "Email" , six );
ele.put( "FirstName" , fname );
ele.put( "LastName" , lName );
ele.put( "MiddleInitial" , mName );
DL.put( IndividualName+IndividualID , ele );
}
// Added by Parshuram
DL.setTotalNoOfRecords(DL.size());
DL.setBeginIndex(1);
DL.setEndIndex(DL.getTotalNoOfRecords());
DL.setStartAT( 1 );
DL.setEndAT ( 10 );
return DL;
}