/**
* [Web Service] User Calendar functionality
*
* @param sessionId
* @param securityKey
* @param typeId
* @param saveJobId
* @param calendarId
* @param cdcId
* @param eventTitle
* @param eventType
* @param eventDate
* @param eventTime
* @param reminderFlag
* @param freq
* @param periodVal
* @param sendEmail
* @param notes
* @return
*/
@RequestMapping(value = "/calendar")
public String wsUserCalendar(
@RequestParam("sessionId") int sessionId,
@RequestParam("securityKey") String securityKey,
@RequestParam("typeId") int typeId,
@RequestParam("saveJobId") int saveJobId,
@RequestParam("calendarId") int calendarId,
@RequestParam("cdcId") String cdcId,
@RequestParam("eventTitle") String eventTitle,
@RequestParam("eventType") String eventType,
@RequestParam("eventDate") String eventDate,
@RequestParam("eventTime") String eventTime,
@RequestParam("reminderFlag") String reminderFlag,
@RequestParam("freq") String freq,
@RequestParam("periodVal") String periodVal,
@RequestParam("sendEmail") String sendEmail,
@RequestParam("notes") String notes) {
Map<String, Object> map = null;
Gson gson = null;
LeadManagerSessionData lmData = null;
Connection con = null;
try {
gson = new Gson();
map = new HashMap<String, Object>();
boolean validSessionId = false;
boolean dbFlag = false;
if (con == null) con = JDBCUtil.getDBConnectionFromDatabase();
validSessionId = LoginUtil.checkValidSession(sessionId, securityKey, con);
if (validSessionId == true) {
/**
* 1 - getCalendar events 2 - InsertCalender event 3 - updated calendar event 4 - deleted
* calendar event
*/
lmData = LoginUtil.getLeadManagerSessionDetails(sessionId, con);
if (typeId == 1) {
ArrayList calList = getUserCalendarInfo(calendarId, con);
map.put("iTotalRecords", String.valueOf(calList.size()));
map.put("aaData", calList);
log(
"loginID: "
+ lmData.getLogin()
+ ": Type : "
+ typeId
+ " : iTotalRecords : "
+ calList.size());
} else if (typeId == 2) {
dbFlag =
insertUserCalendarEvent(
lmData.getLogin(),
saveJobId,
cdcId,
eventTitle,
eventType,
eventDate,
eventTime,
reminderFlag,
freq,
periodVal,
sendEmail,
notes,
con);
map.put("CalendarInsertedFlag", "" + dbFlag);
log("loginID: " + lmData.getLogin() + ": Type : " + typeId + " : dbFlag : " + dbFlag);
} else if (typeId == 3) {
dbFlag =
updateUserCalendarEvent(
calendarId,
eventTitle,
eventType,
eventDate,
eventTime,
reminderFlag,
freq,
periodVal,
sendEmail,
notes,
con);
map.put("CalendarUpdatedFlag", "" + dbFlag);
log("loginID: " + lmData.getLogin() + ": Type : " + typeId + " : dbFlag : " + dbFlag);
} else if (typeId == 4) {
dbFlag = deleteUserCalendarEvent(calendarId, con);
map.put("CalendarDeletedFlag", "" + dbFlag);
log("loginID: " + lmData.getLogin() + ": Type : " + typeId + " : dbFlag : " + dbFlag);
}
} else {
map.put("iTotalRecords", "0");
map.put("aaData", "");
map.put("message", "Invalid Login");
}
} catch (Exception ex) {
ex.printStackTrace();
log("Exception in wsUserCalendar() " + ex);
} finally {
JDBCUtil.releaseDBConnection(con);
}
return gson.toJson(map);
}
/**
* @param sessionId
* @param securityKey
* @param stateIds
* @param sectionIdList
* @param newUpdatedFlag
* @param subSectionList
* @param constructionTypes
* @param divisionIdList
* @param showAll
* @param displayMode
* @param con
* @return
*/
public String getQuickSearchContentIds(
int sessionId,
String securityKey,
String stateIds,
String sectionIdList,
int newUpdatedFlag,
String subSectionList,
String constructionTypes,
String divisionIdList,
int showAll,
String displayMode,
Connection con) {
ResultSet rs = null;
Statement stmt = null;
String sql = null;
// String userStateIdList = null;
String userCountyIdList = null;
// String geoUserStateIdList = null;
String geoUserCountyIdList = null;
// String userStateIds = null;
String userCountyIds = null;
// String nationalChainUser = null;
String contentIdList = null;
try {
LeadManagerSessionData lmData = LoginUtil.getLeadManagerSessionDetails(sessionId, con);
// USER SUBSCRIBED STATE AND COUNTY LIST
// userStateIdList = lmData.getUserStateList();
userCountyIdList = lmData.getUserCountyList();
// geoUserStateIdList = lmData.getGeoUserStateList();
geoUserCountyIdList = lmData.getGeoUserCountyList();
/* User state list */
/*if (geoUserStateIdList != null && userStateIdList != null) {
userStateIds = userStateIdList + "," + geoUserStateIdList;
} else if (userStateIdList != null) {
userStateIds = userStateIdList;
} else if (geoUserStateIdList != null) {
userStateIds = geoUserStateIdList;
}*/
/* User county list */
if (geoUserCountyIdList != null && userCountyIdList != null) {
userCountyIds = userCountyIdList + "," + geoUserCountyIdList;
} else if (userCountyIdList != null) {
userCountyIds = userCountyIdList;
} else if (geoUserCountyIdList != null) {
userCountyIds = geoUserCountyIdList;
}
// SHOW ALL
if (showAll == 1) {
sql =
"select distinct c.id,c.cdc_id,c.publication_id,c.section_id,c.sub_section,"
+ " c.title,c.state_id,c.estimated_amount_lower,c.bid_date,c.prebid_mtg,c.bids_details, "
+ " c.plan_express,c.entry_date,c.short_cdcid,c.state_multiple,c.county_multiple,"
+ " c.plan_availability_status,c.leads_id,c.county_id,gc.county_name,s.state_abb "
+ " from content c,pub_section ps,state s,county gc";
} else {
sql =
"select distinct top 200 c.id,c.cdc_id,c.publication_id,c.section_id,c.sub_section,"
+ " c.title,c.state_id,c.estimated_amount_lower,c.bid_date,c.prebid_mtg,c.bids_details, "
+ " c.plan_express,c.entry_date,c.short_cdcid,c.state_multiple,c.county_multiple,"
+ " c.plan_availability_status,c.leads_id,c.county_id,gc.county_name,s.state_abb "
+ " from content c,pub_section ps,state s,county gc";
}
// CONTENT_DIVISIONS
if (divisionIdList != null && divisionIdList.equals("") != true) {
sql += ",content_divisions_2004 cd";
}
/*
* if (nationalChainUser != null && nationalChainUser.equals("Y") == true) { sql += ",content_details cdt"; }
*/
sql +=
" where c.activate =1 and c.state_id=s.state_id and c.id=ps.content_id and c.county_id=gc.county_id ";
// NEW/UPDATED FLAG
if (newUpdatedFlag == 1) {
sql += "AND c.new_updated = 'N'";
}
sql += "AND entry_date >= convert(varchar,dateadd(day,-90,getdate()),101)";
// stateIds
if (stateIds != null && !stateIds.trim().equals("")) {
sql += "AND s.state_id in (" + stateIds + ")";
}
/* FOR FILTER BASED ON COUNTIES SUBSCRIBED BY USER */
if (userCountyIds != null && !userCountyIds.trim().equals("")) {
sql += "AND c.county_id in (" + userCountyIds + ")";
}
/* NATIONAL CHAIN FLAG CHECK */
/*
* if (nationalChainUser != null && nationalChainUser.equals("Y") == true) { sql += "and c.id=cdt.content_id and cdt.national_chain='Y'"; }
*/
/** ** Section ***** */
if (sectionIdList != null && !sectionIdList.equals("")) {
sql += "and ps.section_id in (" + sectionIdList + ")";
}
/** ** Sub Section ***** */
if (subSectionList != null && !subSectionList.equals("")) {
sql += "and sub_section in ('" + subSectionList.replaceAll(",", "','") + "')";
}
/* Divisions */
if (divisionIdList != null && !(divisionIdList.equals(""))) {
sql +=
" AND c.id=cd.content_id AND (cd.division_2004_id in (SELECT node.division_2004_id FROM "
+ " divisions_2004 AS node, divisions_2004 AS parent WHERE node.lft BETWEEN parent.lft AND "
+ " parent.rgt AND parent.division_2004_id in ("
+ divisionIdList
+ ")))";
}
// CONSTRUCTION TYPE
// String constT = null;
String cons[] = null;
if (constructionTypes != null && constructionTypes.equals("") != true) {
cons = constructionTypes.split("\\,");
for (int x = 0; x < cons.length; x++) {
if (x == 0) {
sql += "and (";
} else {
sql += " or ";
}
sql += " (c.const_" + cons[x] + "='Y'";
sql += ")";
}
sql += ")";
}
sql += " order by c.bid_date asc , c.entry_date desc";
log.info("quick search query: " + sql);
stmt = con.createStatement();
rs = stmt.executeQuery(sql);
while (rs.next()) {
if (contentIdList == null) {
contentIdList = String.valueOf(rs.getInt("id"));
} else {
contentIdList = contentIdList + "," + String.valueOf(rs.getInt("id"));
}
} // while.
stmt.close();
} catch (SQLException se) {
se.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
} catch (SQLException se) {
log.error("!exception3.2!SQL error in getContentIds" + se);
} finally {
try {
if (stmt != null) {
stmt.close();
}
} catch (SQLException se) {
log.error("!exception3.3!SQL error in getContentIds" + se);
}
}
}
return contentIdList;
} //