/**
   * [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;
  } //