{
if (!CVUtility.isModuleVisible("ListManager", userID, this.dataSource)) {
throw new AuthorizationFailedException("ListManager - getMarketingListMemberList");
}
MarketingListMemberList returnList = new MarketingListMemberList();
Integer startAtParam = (Integer)preferences.get("startATparam");
Integer EndAtParam = (Integer)preferences.get("EndAtparam");
String searchString = (String)preferences.get("searchString");
String sortMember = (String)preferences.get("sortmem");
Character sortTypeCharacter = (Character)preferences.get("sortType");
char sortType = sortTypeCharacter.charValue();
int startAt = startAtParam.intValue();
int endAt = EndAtParam.intValue();
int beginIndex = Math.max(startAt - 100, 1);
int endIndex = endAt + 100;
CVDal dataConnection = new CVDal(dataSource);
try {
// Create the table
dataConnection
.setSqlQuery("CREATE TEMPORARY TABLE marketinglistmembers (EntityID int(10) unsigned default null, Entity varchar(40) default null, IndividualID int(10) unsigned default null, Individual varchar (51) default null, PhoneNumber varchar(50) default null, Email varchar(50) default null, PRIMARY KEY (IndividualID)) TYPE=HEAP");
dataConnection.executeUpdate();
dataConnection.clearParameters();
// Insert the Entities
// dataConnection.setSqlQuery("INSERT INTO marketinglistmembers (EntityID,
// Entity) SELECT DISTINCT ent.EntityID, ent.name from entity ent where
// ent.list = ?");
// dataConnection.setInt(1, marketingListID);
// dataConnection.executeUpdate();
// dataConnection.clearParameters();
//
// //Insert the entity Phone Number
// dataConnection.setSqlQuery("UPDATE marketinglistmembers, mocrelate,
// methodofcontact, moctype, contacttype SET
// marketinglistmembers.PhoneNumber = methodofcontact.Content WHERE
// marketinglistmembers.EntityID = mocrelate.ContactID AND mocrelate.MOCID
// = methodofcontact.MOCID AND methodofcontact.MOCType = moctype.MOCTypeID
// AND moctype.Name = 'Phone' AND contacttype.contacttypeid =
// mocrelate.ContactType AND contacttype.Name = 'Entity'");
// dataConnection.executeUpdate();
// dataConnection.clearParameters();
//
// //Insert the entity Email Address
// dataConnection.setSqlQuery("UPDATE marketinglistmembers, mocrelate,
// methodofcontact, moctype, contacttype SET marketinglistmembers.Email =
// methodofcontact.Content WHERE marketinglistmembers.EntityID =
// mocrelate.ContactID AND mocrelate.MOCID = methodofcontact.MOCID AND
// methodofcontact.MOCType = moctype.MOCTypeID AND moctype.Name = 'Email'
// AND contacttype.contacttypeid = mocrelate.ContactType AND
// contacttype.Name = 'Entity'");
// dataConnection.executeUpdate();
// dataConnection.clearParameters();
// Insert the individuals
dataConnection
.setSqlQuery("INSERT INTO marketinglistmembers (EntityID, Entity, IndividualID, Individual) SELECT DISTINCT ind.Entity, ent.name, ind.individualID, concat(ind.FirstName, ' ', ind.LastName) from individual ind left join entity ent on ind.entity = ent.entityid where ind.list = ?");
dataConnection.setInt(1, marketingListID);
dataConnection.executeUpdate();
dataConnection.clearParameters();
// Insert the individual Phone Number
dataConnection
.setSqlQuery("UPDATE marketinglistmembers, mocrelate, methodofcontact, moctype, contacttype SET marketinglistmembers.PhoneNumber = methodofcontact.Content WHERE marketinglistmembers.individualID = mocrelate.ContactID AND mocrelate.MOCID = methodofcontact.MOCID AND methodofcontact.MOCType = moctype.MOCTypeID AND moctype.Name = 'Phone' AND contacttype.contacttypeid = mocrelate.ContactType AND contacttype.Name = 'Individual'");
dataConnection.executeUpdate();
dataConnection.clearParameters();
// Insert the individual Email Address
dataConnection
.setSqlQuery("UPDATE marketinglistmembers, mocrelate, methodofcontact, moctype, contacttype SET marketinglistmembers.Email = methodofcontact.Content WHERE marketinglistmembers.individualID = mocrelate.ContactID AND mocrelate.MOCID = methodofcontact.MOCID AND methodofcontact.MOCType = moctype.MOCTypeID AND moctype.Name = 'Email' AND contacttype.contacttypeid = mocrelate.ContactType AND contacttype.Name = 'Individual'");
dataConnection.executeUpdate();
dataConnection.clearParameters();
// do the search
returnList.setSortMember(sortMember);
if (searchString == null) {
searchString = "";
} // end of if statement (searchString == null)
StringBuffer query = new StringBuffer();
query
.append("SELECT EntityID, Entity, IndividualID, Individual, PhoneNumber, Email FROM marketinglistmembers ");
query.append("order by " + sortMember + " ");
if (sortType == 'A') {
query.append("ASC ");
} // end of if statement (sortType == 'A')
else {
query.append(" DESC ");
} // end of else statement (sortType == 'A')
// add the limit statement
// query.append(" LIMIT " + (beginIndex - 1) + " , " + (endIndex + 1));
dataConnection.setSqlQuery(query.toString());
Collection results = dataConnection.executeQuery();
// drop the table
dataConnection.setSqlQuery("DROP TABLE marketinglistmembers");
dataConnection.executeUpdate();
dataConnection.clearParameters();
if (results != null) {
Iterator iterator = results.iterator();
int i = 1;
while (iterator.hasNext()) {
HashMap hashMap = (HashMap)iterator.next();
// int entityIntID = ((Long) hashMap.get("EntityID")).intValue();
// int individualIntID = ((Long)
// hashMap.get("IndividualID")).intValue();
StringMember entityID;
StringMember individualID;
if (hashMap.get("EntityID") != null) {
entityID = new StringMember("EntityID", ((Long)hashMap.get("EntityID")).toString(), 10,
"", 'T', true);
} else {
entityID = new StringMember("EntityID", null, 10, "", 'T', true);
}
if (hashMap.get("IndividualID") != null) {
individualID = new StringMember("IndividualID", ((Long)hashMap.get("IndividualID"))
.toString(), 10, "", 'T', true);
} else {
individualID = new StringMember("IndividualID", null, 10, "", 'T', true);
}
StringMember entityName = new StringMember("Entity", (String)hashMap.get("Entity"), 10,
"", 'T', true);
StringMember individualName = new StringMember("Individual", (String)hashMap
.get("Individual"), 10, "", 'T', true);
StringMember emailAddress = new StringMember("Email", (String)hashMap.get("Email"), 10,
"", 'T', true);
StringMember phoneNumber = new StringMember("PhoneNumber", (String)hashMap
.get("PhoneNumber"), 10, "", 'T', false);
MarketingListMemberListElement currentElement = new MarketingListMemberListElement(i);
currentElement.put("EntityID", entityID);
currentElement.put("IndividualID", individualID);
currentElement.put("Entity", entityName);
currentElement.put("Individual", individualName);
currentElement.put("Email", emailAddress);
currentElement.put("PhoneNumber", phoneNumber);
// TODO Move code to Delete Handler to make sure
// we get the correct Element.
StringBuffer sb = new StringBuffer("00000000000");
sb.setLength(11);
String countString = Integer.toString(i);
sb.replace((sb.length() - countString.length()), (sb.length()), countString);
String newElementID = sb.toString();
returnList.put(newElementID, currentElement);
i++;
} // end of while loop (iterator.hasNext())
} // end of if statement (results != null)
returnList.setTotalNoOfRecords(returnList.size());
returnList.setListType("MarketingListMembers");
returnList.setBeginIndex(beginIndex);
returnList.setEndIndex(endIndex);
// do stuff
} // end of try block
catch (Exception e) {
System.out