{
if (!CVUtility.isModuleVisible("ListManager", userId, this.dataSource)) {
throw new AuthorizationFailedException("ListManager - getMarketingList");
}
MarketingList DL = new MarketingList();
try {
Integer startATparam = (Integer)preference.get("startATparam");
Integer EndAtparam = (Integer)preference.get("EndAtparam");
String searchString = (String)preference.get("searchString");
String sortmem = (String)preference.get("sortmem");
Character chr = (Character)preference.get("sortType");
sortmem = "description";
char sorttype = chr.charValue();
int startat = startATparam.intValue();
int endat = EndAtparam.intValue();
int begainindex = Math.max(startat - 100, 1);
int endindex = endat + 100;
DL.setSortMember(sortmem);
CVDal cvdl = new CVDal(dataSource);
if (searchString == null) {
searchString = "";
}
String appendStr = "";
String strSQL = "";
String sqlSortType = new String("ASC");
if (sorttype != 'A') {
sqlSortType = "ORDER BY " + sortmem + " DESC";
}
strSQL = " select ml.listID , count(o.OpportunityID) Opportunity ,sum(o.ForecastAmmount) OpportunityValue,count(cvo.Orderid) ordercount ,sum(cvo.total) ordervalue "
+ " from entity e , marketinglist ml LEFT OUTER JOIN cvorder cvo ON (e.entityid=cvo.entityid and cvo.orderstatus='Active') "
+ " LEFT OUTER JOIN opportunity o ON (e.entityid=o.entityid) "
+ " where e.list=ml.listID group by ml.ListID ";
cvdl.setSqlQuery(strSQL);
Collection OrderOpportunity = cvdl.executeQuery();
cvdl.clearParameters();
HashMap OrderOpportunityList = new HashMap();
Iterator itOrderOpportunity = OrderOpportunity.iterator();
if (OrderOpportunity != null && OrderOpportunity.size() != 0) {
while (itOrderOpportunity.hasNext()) {
HashMap hm = (HashMap)itOrderOpportunity.next();
HashMap OrderOpportunityValue = new HashMap();
int ListID = ((Long)hm.get("listID")).intValue();
int Opportunity = 0;
double OpportunityValue = 0.0;
int order = 0;
double ordervalue = 0.0;
if (hm.get("Opportunity") != null) {
Opportunity = ((Number)hm.get("Opportunity")).intValue();
}
if (hm.get("OpportunityValue") != null) {
OpportunityValue = ((Number)hm.get("OpportunityValue")).doubleValue();
}
if (hm.get("ordercount") != null) {
order = ((Number)hm.get("ordercount")).intValue();
}
if (hm.get("ordervalue") != null) {
ordervalue = ((Number)hm.get("ordervalue")).doubleValue();
}
OrderOpportunityValue.put("Opportunity", Opportunity + "");
OrderOpportunityValue.put("OpportunityValue", OpportunityValue + "");
OrderOpportunityValue.put("order", order + "");
OrderOpportunityValue.put("ordervalue", ordervalue + "");
OrderOpportunityList.put(ListID + "", OrderOpportunityValue);
}
}
strSQL = "";
// first, get all records that the logged-in user owns
// then, get all records that are marked as "Public"
// then, get all records that the logged-in user has
// been given permissions to view (or greater)
strSQL = "SELECT m.ListID, m.title, m.description, COUNT(i.IndividualID) "
+ "AS numberofrecords, m.proposals, m.orders, m.ordervalue "
+ "FROM marketinglist m LEFT JOIN individual i ON (m.ListId=i.List) "
+ "WHERE m.status='YES' AND m.owner="
+ userId
+ " "
+ "GROUP BY m.ListID "
+ " UNION "
+ "SELECT m.ListID, m.title, m.description, COUNT(i.IndividualID) "
+ "AS numberofrecords, m.proposals, m.orders, m.ordervalue "
+ "FROM marketinglist m LEFT JOIN individual i ON (m.ListId=i.List) "
+ "LEFT JOIN publicrecords p ON (m.ListID=p.recordid) "
+ "WHERE m.status='YES' AND p.moduleid=32 "
+ "GROUP BY m.ListID "
+ " UNION "
+ "SELECT m.ListID, m.title, m.description, COUNT(i.IndividualID) "
+ "AS numberofrecords, m.proposals, m.orders, m.ordervalue "
+ "FROM marketinglist m, recordauthorisation ra LEFT JOIN individual i ON (m.ListId=i.List) "
+ "WHERE m.listid=ra.recordid AND ra.individualid=" + userId
+ " AND ra.recordtypeid=32 AND " + "ra.privilegelevel <= 30 AND m.status='YES' "
+ "GROUP BY m.ListID " + sqlSortType + " LIMIT " + (begainindex - 1) + ", "
+ (endindex + 1);
if (searchString.startsWith("SIMPLE :")) {
searchString = searchString.substring(8);
appendStr = "WHERE (ListID LIKE '%" + searchString + "%' OR m.title LIKE '%" + searchString
+ "%' OR " + "description LIKE '%" + searchString + "%' OR numberofrecords LIKE '%"
+ searchString + "%' OR " + "proposals LIKE '%" + searchString
+ "%' OR orders LIKE '%" + searchString + "%' OR " + "ordervalue LIKE '%"
+ searchString + "%') AND ";
strSQL = strSQL.replaceFirst("where", appendStr);
}
if ((null != searchString) && (searchString.startsWith("ADVANCE:"))) {
int searchIndex = (searchString.toUpperCase()).indexOf("WHERE");
strSQL = " SELECT marketinglist.ListID, marketinglist.title, marketinglist.description, COUNT(i.IndividualID) "
+ "AS numberofrecords, marketinglist.proposals, marketinglist.orders, marketinglist.ordervalue "
+ "FROM marketinglist LEFT JOIN individual i ON (ListId=i.List) "
+ "WHERE status='YES' AND "
+ searchString.substring((searchIndex + 5), searchString.length())
+ " GROUP BY ListID " + sqlSortType;
}
cvdl.setSqlQuery(strSQL);
Collection v = cvdl.executeQuery();
cvdl.clearParameters();
cvdl.destroy();
Iterator it = v.iterator();
int i = 0;
while (it.hasNext()) {
i++;
HashMap hm = (HashMap)it.next();
int ID = ((Long)hm.get("ListID")).intValue();
IntMember intmem = new IntMember("ListID", ID, 10, "", 'T', true, 10);
StringMember entity = new StringMember("title", (String)hm.get("title"), 10, "", 'T', true);
/*
* PLEASE NOTE: this code *MUST* be cleaned up!!!! the variable names
* here are EXTREMELY confusing because someone copied and pasted code
* from another location, and never changed the variable names to make
* any sense within this scope. ie: "whorequested" makes no sense, as
* there is no such field in the Marketing Lists module.
*/
StringMember whorequested = new StringMember("description", (String)hm.get("description")
.toString(), 10, "", 'T', false);
StringMember daterequested = new StringMember("numberofrecords", (String)hm.get(
"numberofrecords").toString(), 10, "URL", 'T', false);
StringMember literaturename = new StringMember("proposals", (String)hm.get("proposals")
.toString(), 10, "", 'T', true);
StringMember orders = new StringMember("opportunities", "0", 10, "", 'T', false);
StringMember ordervalue = new StringMember("opportunityvalue", "0.00", 10, "", 'T', false);
StringMember opportunity = new StringMember("orders", "0", 10, "", 'T', false);
StringMember opportunityvalue = new StringMember("ordervalue", "0.00", 10, "", 'T', false);
boolean listFlag = OrderOpportunityList.containsKey(ID + "");
if (listFlag) {
HashMap orderOpportunityValues = (HashMap)OrderOpportunityList.get(ID + "");
opportunity = new StringMember("opportunities", (String)orderOpportunityValues
.get("Opportunity"), 10, "", 'T', false);
opportunityvalue = new StringMember("opportunityvalue", (String)orderOpportunityValues
.get("OpportunityValue"), 10, "", 'T', false);
orders = new StringMember("orders", (String)orderOpportunityValues.get("order"), 10, "",
'T', false);
ordervalue = new StringMember("ordervalue", (String)orderOpportunityValues
.get("ordervalue"), 10, "", 'T', false);
}
MarketingListElement ele = new MarketingListElement(ID);
ele.put("ListID", intmem);
ele.put("title", entity);
ele.put("description", whorequested);
ele.put("numberofrecords", daterequested);
ele.put("proposals", literaturename);
ele.put("Order", orders);
ele.put("OrderValue", ordervalue);
ele.put("Opportunities", opportunity);
ele.put("OpportunityValue", opportunityvalue);
StringBuffer sb = new StringBuffer("00000000000");
sb.setLength(11);
String str = (new Integer(i)).toString();
sb.replace((sb.length() - str.length()), (sb.length()), str);
String newOrd = sb.toString();
DL.put(newOrd, ele);
DL.setTotalNoOfRecords(DL.size());
DL.setListType("Marketing");
DL.setBeginIndex(begainindex);
DL.setEndIndex(endindex);
} // end while(it.hasNext())
} catch (Exception e) {
e.printStackTrace();
}
return (DL);