List of usage examples for java.sql PreparedStatement setTimestamp
void setTimestamp(int parameterIndex, java.sql.Timestamp x) throws SQLException;
java.sql.Timestamp value. From source file:com.portfolio.data.provider.MysqlDataProvider.java
private int updateMysqlResource(String uuid, String xsiType, String content, int userId) { String sql = ""; PreparedStatement st; try {/*ww w .j a va 2 s . c o m*/ if (xsiType != null) { if (dbserveur.equals("mysql")) { sql = "REPLACE INTO resource_table(node_uuid,xsi_type,content,user_id,modif_user_id,modif_date) "; sql += "VALUES(uuid2bin(?),?,?,?,?,?)"; } else if (dbserveur.equals("oracle")) { sql = "MERGE INTO resource_table d USING (SELECT uuid2bin(?) node_uuid,? xsi_type,? content,? user_id,? modif_user_id,? modif_date FROM DUAL) s ON (d.node_uuid = s.node_uuid) WHEN MATCHED THEN UPDATE SET d.xsi_type = s.xsi_type, d.content = s.content, d.user_id = s.user_id, d.modif_user_id = s.modif_user_id, d.modif_date = s.modif_date WHEN NOT MATCHED THEN INSERT (d.node_uuid, d.xsi_type, d.content, d.user_id, d.modif_user_id, d.modif_date) VALUES (s.node_uuid, s.xsi_type, s.content, s.user_id, s.modif_user_id, s.modif_date)"; } st = connection.prepareStatement(sql); st.setString(1, uuid); st.setString(2, xsiType); st.setString(3, content); st.setInt(4, userId); st.setInt(5, userId); if (dbserveur.equals("mysql")) { st.setString(6, SqlUtils.getCurrentTimeStamp()); } else if (dbserveur.equals("oracle")) { st.setTimestamp(6, SqlUtils.getCurrentTimeStamp2()); } return st.executeUpdate(); } else { sql = "UPDATE resource_table SET content = ?,user_id = ?,modif_user_id = ?,modif_date = ? WHERE node_uuid = uuid2bin(?) "; st = connection.prepareStatement(sql); st.setString(1, content); st.setInt(2, userId); st.setInt(3, userId); if (dbserveur.equals("mysql")) { st.setString(4, SqlUtils.getCurrentTimeStamp()); } else if (dbserveur.equals("oracle")) { st.setTimestamp(4, SqlUtils.getCurrentTimeStamp2()); } st.setString(5, uuid); return st.executeUpdate(); } } catch (Exception ex) { //System.out.println("root_node_uuid : "+uuid); ex.printStackTrace(); return -1; } }
From source file:com.portfolio.data.provider.MysqlDataProvider.java
private int insertMysqlResource(String uuid, String parentUuid, String xsiType, String content, String portfolioModelId, int sharedNodeRes, int sharedRes, int userId) { String sql = ""; PreparedStatement st = null; int status = -1; try {/* w ww .j av a2 s . c o m*/ if (((xsiType.equals("nodeRes") && sharedNodeRes == 1) || (!xsiType.equals("context") && !xsiType.equals("nodeRes") && sharedRes == 1)) && portfolioModelId != null) { // On ne fait rien } else { if (dbserveur.equals("mysql")) { sql = "REPLACE INTO resource_table(node_uuid,xsi_type,content,user_id,modif_user_id,modif_date) "; sql += "VALUES(uuid2bin(?),?,?,?,?,?)"; } else if (dbserveur.equals("oracle")) { sql = "MERGE INTO resource_table d USING (SELECT uuid2bin(?) node_uuid,? xsi_type,? content,? user_id,? modif_user_id,? modif_date FROM DUAL) s ON (d.node_uuid = s.node_uuid) WHEN MATCHED THEN UPDATE SET d.xsi_type = s.xsi_type, d.content = s.content, d.user_id = s.user_id, d.modif_user_id = s.modif_user_id, d.modif_date = s.modif_date WHEN NOT MATCHED THEN INSERT (d.node_uuid, d.xsi_type, d.content, d.user_id, d.modif_user_id, d.modif_date) VALUES (s.node_uuid, s.xsi_type, s.content, s.user_id, s.modif_user_id, s.modif_date)"; } st = connection.prepareStatement(sql); st.setString(1, uuid); st.setString(2, xsiType); st.setString(3, content); st.setInt(4, userId); st.setInt(5, userId); if (dbserveur.equals("mysql")) { st.setString(6, SqlUtils.getCurrentTimeStamp()); } else if (dbserveur.equals("oracle")) { st.setTimestamp(6, SqlUtils.getCurrentTimeStamp2()); } st.executeUpdate(); if (st != null) try { st.close(); } catch (SQLException e) { e.printStackTrace(); } } // Ensuite on met jour les id ressource au niveau du noeud parent if (xsiType.equals("nodeRes")) { sql = " UPDATE node SET res_res_node_uuid =uuid2bin(?), shared_node_res_uuid=uuid2bin(?) "; sql += " WHERE node_uuid = uuid2bin(?) "; st = connection.prepareStatement(sql); st.setString(1, uuid); if (sharedNodeRes == 1 && portfolioModelId != null) st.setString(2, uuid); else st.setString(2, null); st.setString(3, parentUuid); } else if (xsiType.equals("context")) { sql = " UPDATE node SET res_context_node_uuid=uuid2bin(?)"; sql += " WHERE node_uuid = uuid2bin(?) "; st = connection.prepareStatement(sql); st.setString(1, uuid); st.setString(2, parentUuid); } else { sql = " UPDATE node SET res_node_uuid=uuid2bin(?), shared_res_uuid=uuid2bin(?) "; sql += " WHERE node_uuid = uuid2bin(?) "; st = connection.prepareStatement(sql); st.setString(1, uuid); if (sharedRes == 1 && portfolioModelId != null) st.setString(2, uuid); else st.setString(2, null); st.setString(3, parentUuid); } // return st.executeUpdate(); st.executeUpdate(); status = st.getUpdateCount(); } catch (Exception ex) { //System.out.println("root_node_uuid : "+uuid); ex.printStackTrace(); status = -1; } finally { if (st != null) try { st.close(); } catch (SQLException e) { e.printStackTrace(); } } return status; }
From source file:com.portfolio.data.provider.MysqlDataProvider.java
private int updateMysqlResourceByXsiType(String nodeUuid, String xsiType, String content, int userId) { String sql = ""; PreparedStatement st; try {//ww w . java 2s . c o m if (xsiType.equals("nodeRes")) { sql = " UPDATE resource_table SET content=?,user_id=?,modif_user_id=?,modif_date=? "; sql += " WHERE node_uuid IN (SELECT res_res_node_uuid FROM node "; sql += " WHERE node_uuid=uuid2bin(?)) "; /// Interptation du code (vive le hack... Non) Document doc = DomUtils.xmlString2Document( "<?xml version='1.0' encoding='UTF-8' standalone='no'?><res>" + content + "</res>", new StringBuffer()); NodeList nodes = doc.getElementsByTagName("code"); Node code = nodes.item(0); if (code != null) { Node codeContent = code.getFirstChild(); String codeVal; if (codeContent != null) { codeVal = codeContent.getNodeValue(); String sq = "UPDATE node SET code=? WHERE node_uuid=uuid2bin(?)"; st = connection.prepareStatement(sq); st.setString(1, codeVal); st.setString(2, nodeUuid); st.executeUpdate(); st.close(); } } } else if (xsiType.equals("context")) { sql = " UPDATE resource_table SET content=?,user_id=?,modif_user_id=?,modif_date=? "; sql += " WHERE node_uuid IN (SELECT res_context_node_uuid FROM node "; sql += " WHERE node_uuid=uuid2bin(?)) "; } else { sql = " UPDATE resource_table SET content=?,user_id=?,modif_user_id=?,modif_date=? "; sql += " WHERE node_uuid IN (SELECT res_node_uuid FROM node "; sql += " WHERE node_uuid=uuid2bin(?)) "; } st = connection.prepareStatement(sql); st.setString(1, content); st.setInt(2, userId); st.setInt(3, userId); if (dbserveur.equals("mysql")) { st.setString(4, SqlUtils.getCurrentTimeStamp()); } else if (dbserveur.equals("oracle")) { st.setTimestamp(4, SqlUtils.getCurrentTimeStamp2()); } st.setString(5, nodeUuid); // st.setString(6,xsiType); return st.executeUpdate(); } catch (Exception ex) { //System.out.println("root_node_uuid : "+uuid); ex.printStackTrace(); return -1; } }
From source file:com.globalsight.webservices.Ambassador.java
private String getTaskAvailableDate(Task t, Connection connection) { String availableDate = "null"; if (t == null) return availableDate; long workflowId = t.getWorkflow().getId(); long taskId = t.getId(); PreparedStatement pstmt = null; ResultSet rs = null;/*from w w w .jav a 2 s.c o m*/ try { if (connection == null) connection = ConnectionPool.getConnection(); StringBuilder sql = new StringBuilder(); java.sql.Timestamp tmp = null; Date acceptedDate = t.getAcceptedDate(); sql.append("SELECT MAX(Completed_Date) FROM Task_Info"); sql.append(" WHERE Workflow_ID=? AND State=?"); if (acceptedDate != null) { sql.append(" AND Accepted_Date<?"); } pstmt = connection.prepareStatement(sql.toString()); pstmt.setLong(1, workflowId); pstmt.setString(2, Task.COMPLETED); if (acceptedDate != null) pstmt.setTimestamp(3, new java.sql.Timestamp(acceptedDate.getTime())); rs = pstmt.executeQuery(); if (rs.next()) { tmp = rs.getTimestamp(1); if (tmp != null) availableDate = convertDateToString(new Date(tmp.getTime())); else availableDate = convertDateToString(t.getWorkflow().getDispatchedDate()); } } catch (Exception e) { logger.error("Error found.", e); } finally { ConnectionPool.silentClose(pstmt); } return availableDate; }
From source file:com.portfolio.data.provider.MysqlDataProvider.java
private int insertMySqlNode(String nodeUuid, String nodeParentUuid, String nodeChildrenUuid, String asmType, String xsiType, int sharedRes, int sharedNode, int sharedNodeRes, String sharedResUuid, String sharedNodeUuid, String sharedNodeResUuid, String metadata, String metadataWad, String metadataEpm, String semtag, String semanticTag, String label, String code, String descr, String format, int order, int modifUserId, String portfolioUuid) { String sql = ""; PreparedStatement st; try {/*from w w w. j a va 2 s . c om*/ if (nodeChildrenUuid == null) { nodeChildrenUuid = getMysqlNodeResultset(nodeUuid).getString("node_children_uuid"); } } catch (Exception ex) { } /// Because Oracle can't do its work properly if ("".equals(semanticTag)) semanticTag = null; if ("".equals(nodeChildrenUuid)) nodeChildrenUuid = null; if ("".equals(xsiType)) xsiType = null; if ("".equals(code)) code = null; try { if (dbserveur.equals("mysql")) { sql = "REPLACE INTO node(node_uuid,node_parent_uuid,node_children_uuid,node_order,"; sql += "asm_type,xsi_type,shared_res,shared_node,shared_node_res,shared_res_uuid,shared_node_uuid,shared_node_res_uuid, metadata,metadata_wad,metadata_epm,semtag,semantictag,label,code,descr,format,modif_user_id,modif_date,portfolio_id) "; sql += "VALUES(uuid2bin(?),uuid2bin(?),?,?,?,?,?,?,?,uuid2bin(?),uuid2bin(?),uuid2bin(?),?,?,?,?,?,?,?,?,?,?,?,uuid2bin(?))"; } else if (dbserveur.equals("oracle")) { sql = "MERGE INTO node d USING (SELECT uuid2bin(?) node_uuid,uuid2bin(?) node_parent_uuid,? node_children_uuid,? node_order,? asm_type,? xsi_type,? shared_res,? shared_node,? shared_node_res,uuid2bin(?) shared_res_uuid,uuid2bin(?) shared_node_uuid,uuid2bin(?) shared_node_res_uuid,? metadata,? metadata_wad,? metadata_epm,? semtag,? semantictag,? label,? code,? descr,? format,? modif_user_id,? modif_date,uuid2bin(?) portfolio_id FROM DUAL) s ON (d.node_uuid = s.node_uuid) WHEN MATCHED THEN UPDATE SET d.node_parent_uuid=s.node_parent_uuid,d.node_children_uuid=s.node_children_uuid,d.node_order=s.node_order,d.asm_type=s.asm_type,d.xsi_type=s.xsi_type,d.shared_res=s.shared_res,d.shared_node=s.shared_node,d.shared_node_res=s.shared_node_res,d.shared_res_uuid=s.shared_res_uuid,d.shared_node_uuid=s.shared_node_uuid,d.shared_node_res_uuid=s.shared_node_res_uuid,d.metadata=s.metadata,d.metadata_wad=s.metadata_wad,d.metadata_epm=s.metadata_epm,d.semtag=s.semtag,d.semantictag=s.semantictag,d.label=s.label,d.code=s.code,d.descr=s.descr,d.format=s.format,d.modif_user_id=s.modif_user_id,d.modif_date=s.modif_date,d.portfolio_id=s.portfolio_id WHEN NOT MATCHED THEN INSERT (d.node_uuid,d.node_parent_uuid,d.node_children_uuid,d.node_order,d.asm_type,d.xsi_type,d.shared_res,d.shared_node,d.shared_node_res,d.shared_res_uuid,d.shared_node_uuid,d.shared_node_res_uuid,d.metadata,d.metadata_wad,d.metadata_epm,d.semtag,d.semantictag,d.label,d.code,d.descr,d.format,d.modif_user_id,d.modif_date,d.portfolio_id) VALUES (s.node_uuid,s.node_parent_uuid,s.node_children_uuid,s.node_order,s.asm_type,s.xsi_type,s.shared_res,s.shared_node,s.shared_node_res,s.shared_res_uuid,s.shared_node_uuid,s.shared_node_res_uuid,s.metadata,s.metadata_wad,s.metadata_epm,s.semtag,s.semantictag,s.label,s.code,s.descr,s.format,s.modif_user_id,s.modif_date,s.portfolio_id)"; } st = connection.prepareStatement(sql); st.setString(1, nodeUuid); st.setString(2, nodeParentUuid); st.setString(3, nodeChildrenUuid); st.setInt(4, order); st.setString(5, asmType); st.setString(6, xsiType); st.setInt(7, sharedRes); st.setInt(8, sharedNode); st.setInt(9, sharedNodeRes); st.setString(10, sharedResUuid); st.setString(11, sharedNodeUuid); st.setString(12, sharedNodeResUuid); st.setString(13, metadata); st.setString(14, metadataWad); st.setString(15, metadataEpm); st.setString(16, semtag); st.setString(17, semanticTag); st.setString(18, label); st.setString(19, code); st.setString(20, descr); st.setString(21, format); st.setInt(22, modifUserId); if (dbserveur.equals("mysql")) { st.setString(23, SqlUtils.getCurrentTimeStamp()); } else if (dbserveur.equals("oracle")) { st.setTimestamp(23, SqlUtils.getCurrentTimeStamp2()); } st.setString(24, portfolioUuid); return st.executeUpdate(); } catch (Exception ex) { ex.printStackTrace(); return -1; } }
From source file:com.funambol.foundation.items.dao.PIMCalendarDAO.java
/** * Updates a calendar.//from w w w . j a va 2 s . c om * * @param cw as a CalendarWrapper object. If its last update time is null, * then it's set to the current time. * @return the UID of the contact * @throws DAOException * @throws java.lang.Exception * @see CalendarWrapper */ public String updateItem(CalendarWrapper cw) throws DAOException, Exception { Connection con = null; PreparedStatement ps = null; ResultSet rs = null; RecurrencePattern rp = null; String id = null; String allDay = null; String body = null; Boolean allDayBoolean = null; Short busyStatus = null; String categories = null; String companies = null; int duration = 0; Date dend = null; short importance = 0; String location = null; Short meetingStatus = null; String mileage = null; Date replyTime = null; short sensitivity = 0; Date dstart = null; String subject = null; short recurrenceType = -1; int interval = 0; short monthOfYear = 0; short dayOfMonth = 0; String dayOfWeekMask = null; String priority = null; short instance = 0; String startDatePattern = null; String noEndDate = null; String endDatePattern = null; int occurrences = -1; Reminder reminder = null; CalendarContent c = null; Date completed = null; String complete = null; short percentComplete = -1; String folder = null; String dstartTimeZone = null; String dendTimeZone = null; String reminderTimeZone = null; StringBuffer queryUpdateFunPimCalendar = null; try { Timestamp lastUpdate = (cw.getLastUpdate() == null) ? new Timestamp(System.currentTimeMillis()) : cw.getLastUpdate(); c = cw.getCalendar().getCalendarContent(); rp = c.getRecurrencePattern(); id = cw.getId(); boolean allDayB; allDayBoolean = c.getAllDay(); if (allDayBoolean != null && allDayBoolean.booleanValue()) { allDayB = true; allDay = "1"; } else { allDayB = false; allDay = "0"; } body = Property.stringFrom(c.getDescription()); if (c.getBusyStatus() != null) { busyStatus = new Short(c.getBusyStatus().shortValue()); } categories = Property.stringFrom(c.getCategories()); companies = Property.stringFrom(c.getOrganizer()); if (c.getPriority() != null) { priority = c.getPriority().getPropertyValueAsString(); if (priority != null && priority.length() > 0) { importance = Short.parseShort(priority); } // priority / importance ?? } location = Property.stringFrom(c.getLocation()); meetingStatus = c.getMeetingStatus(); if (c.getMileage() != null) { mileage = String.valueOf(c.getMileage()); } reminder = c.getReminder(); String rt = null; if (c instanceof Event) { rt = Property.stringFrom(((Event) c).getReplyTime()); replyTime = getDateFromString(allDayB, // @todo or false? rt, "000000"); } if (c.getAccessClass() != null) { String classEvent = null; classEvent = c.getAccessClass().getPropertyValueAsString(); if (classEvent != null && classEvent.length() > 0) { sensitivity = Short.parseShort(classEvent); } } if (c.getSummary() != null) { subject = c.getSummary().getPropertyValueAsString(); } else if (body != null && body.length() > 0) { String tmpBody = body; if (tmpBody.indexOf('.') != -1) { tmpBody = tmpBody.substring(0, tmpBody.indexOf('.')); } if (tmpBody.length() > SQL_SUBJECT_DIM) { tmpBody = tmpBody.substring(0, SQL_SUBJECT_DIM); } subject = tmpBody; } folder = Property.stringFrom(c.getFolder()); dstartTimeZone = timeZoneFrom(c.getDtStart()); dendTimeZone = timeZoneFrom(c.getDtEnd()); reminderTimeZone = timeZoneFrom(c.getReminder()); String sd = null; if (c.getDtStart() != null) { sd = c.getDtStart().getPropertyValueAsString(); dstart = getDateFromString(allDayB, sd, "000000"); } String ed = null; if ((sd != null && sd.length() > 0) || (c.getDtEnd() != null)) { ed = c.getDtEnd().getPropertyValueAsString(); // // Fix for Siemens S56 end date issue only for event // @todo: verify if is really need to do this // Due to this fix, in method getTwinItems() if the incoming // Event has an empty EndDate we seek into the db for Events // with EndDate equal to the StartDate value. // if (c instanceof Event) { if (ed == null || ed.length() == 0) { ed = sd; } } dend = getDateFromString(allDayB, ed, "235900"); } if (rp != null) { recurrenceType = rp.getTypeId(); interval = rp.getInterval(); monthOfYear = rp.getMonthOfYear(); dayOfMonth = rp.getDayOfMonth(); dayOfWeekMask = String.valueOf(rp.getDayOfWeekMask()); instance = rp.getInstance(); startDatePattern = rp.getStartDatePattern(); boolean noEndDateB = rp.isNoEndDate(); if (noEndDateB) { noEndDate = "1"; } else { noEndDate = "0"; } endDatePattern = rp.getEndDatePattern(); occurrences = rp.getOccurrences(); } String dc = null; if (c instanceof Task) { Task t = (Task) c; if (t.getDateCompleted() != null) { dc = t.getDateCompleted().getPropertyValueAsString(); completed = getDateFromString(allDayB, dc, "000000"); } complete = Property.stringFrom(t.getComplete()); if (complete != null && "1".equals(complete)) { percentComplete = 100; } else { try { percentComplete = Short.parseShort(Property.stringFrom(t.getPercentComplete())); if (percentComplete < 0 || percentComplete > 100) { throw new NumberFormatException("A percentage can't be " + percentComplete); } } catch (NumberFormatException nfe) { percentComplete = -1; // the short must go on } } meetingStatus = getTaskStatus(t); } queryUpdateFunPimCalendar = new StringBuffer(); queryUpdateFunPimCalendar.append(SQL_UPDATE_FNBL_PIM_CALENDAR_BEGIN).append(SQL_FIELD_LAST_UPDATE) .append(SQL_EQUALS_QUESTIONMARK_COMMA); if (allDayBoolean != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_ALL_DAY).append(SQL_EQUALS_QUESTIONMARK_COMMA); } if (body != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_BODY).append(SQL_EQUALS_QUESTIONMARK_COMMA); } queryUpdateFunPimCalendar.append(SQL_FIELD_BUSY_STATUS).append(SQL_EQUALS_QUESTIONMARK_COMMA); if (categories != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_CATEGORIES).append(SQL_EQUALS_QUESTIONMARK_COMMA); } if (companies != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_COMPANIES).append(SQL_EQUALS_QUESTIONMARK_COMMA); } queryUpdateFunPimCalendar.append(SQL_FIELD_DURATION).append(SQL_EQUALS_QUESTIONMARK_COMMA); if (dend != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_DATE_END).append(SQL_EQUALS_QUESTIONMARK_COMMA); } else if (ed != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_DATE_END).append(SQL_EQUALS_NULL_COMMA); } if (priority != null && priority.length() > 0) { queryUpdateFunPimCalendar.append(SQL_FIELD_IMPORTANCE).append(SQL_EQUALS_QUESTIONMARK_COMMA); } if (location != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_LOCATION).append(SQL_EQUALS_QUESTIONMARK_COMMA); } if (meetingStatus != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_MEETING_STATUS).append(SQL_EQUALS_QUESTIONMARK_COMMA); } if (mileage != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_MILEAGE).append(SQL_EQUALS_QUESTIONMARK_COMMA); } if (reminder != null) { if (reminder.isActive()) { queryUpdateFunPimCalendar.append(SQL_FIELD_REMINDER).append(SQL_EQUALS_ONE_COMMA); } else { queryUpdateFunPimCalendar.append(SQL_FIELD_REMINDER).append(SQL_EQUALS_ZERO_COMMA); } queryUpdateFunPimCalendar.append(SQL_FIELD_REMINDER_TIME).append(SQL_EQUALS_QUESTIONMARK_COMMA); queryUpdateFunPimCalendar.append(SQL_FIELD_REMINDER_REPEAT_COUNT) .append(SQL_EQUALS_QUESTIONMARK_COMMA); queryUpdateFunPimCalendar.append(SQL_FIELD_REMINDER_SOUND_FILE) .append(SQL_EQUALS_QUESTIONMARK_COMMA); queryUpdateFunPimCalendar.append(SQL_FIELD_REMINDER_OPTIONS).append(SQL_EQUALS_QUESTIONMARK_COMMA); } if (replyTime != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_REPLY_TIME).append(SQL_EQUALS_QUESTIONMARK_COMMA); } else if (rt != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_REPLY_TIME).append(SQL_EQUALS_NULL_COMMA); } queryUpdateFunPimCalendar.append(SQL_FIELD_SENSITIVITY).append(SQL_EQUALS_QUESTIONMARK_COMMA); if (dstart != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_DATE_START).append(SQL_EQUALS_QUESTIONMARK_COMMA); } else if (sd != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_DATE_START).append(SQL_EQUALS_NULL_COMMA); } if (subject != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_SUBJECT).append(SQL_EQUALS_QUESTIONMARK_COMMA); } queryUpdateFunPimCalendar.append(SQL_FIELD_RECURRENCE_TYPE).append(SQL_EQUALS_QUESTIONMARK_COMMA); queryUpdateFunPimCalendar.append(SQL_FIELD_INTERVAL).append(SQL_EQUALS_QUESTIONMARK_COMMA); queryUpdateFunPimCalendar.append(SQL_FIELD_MONTH_OF_YEAR).append(SQL_EQUALS_QUESTIONMARK_COMMA); queryUpdateFunPimCalendar.append(SQL_FIELD_DAY_OF_MONTH).append(SQL_EQUALS_QUESTIONMARK_COMMA); if (dayOfWeekMask != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_DAY_OF_WEEK_MASK).append(SQL_EQUALS_QUESTIONMARK_COMMA); } queryUpdateFunPimCalendar.append(SQL_FIELD_INSTANCE).append(SQL_EQUALS_QUESTIONMARK_COMMA); if (startDatePattern != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_START_DATE_PATTERN) .append(SQL_EQUALS_QUESTIONMARK_COMMA); } if (noEndDate != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_NO_END_DATE).append(SQL_EQUALS_QUESTIONMARK_COMMA); } if (endDatePattern != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_END_DATE_PATTERN).append(SQL_EQUALS_QUESTIONMARK_COMMA); } else { // // When NoEndDate is true, the PatterEndDate must be empty. // if ("1".equals(noEndDate)) { queryUpdateFunPimCalendar.append(SQL_FIELD_END_DATE_PATTERN).append(SQL_EQUALS_EMPTY_COMMA); } } queryUpdateFunPimCalendar.append(SQL_FIELD_OCCURRENCES).append(SQL_EQUALS_QUESTIONMARK_COMMA); if (completed != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_COMPLETED).append(SQL_EQUALS_QUESTIONMARK_COMMA); } else if (dc != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_COMPLETED).append(SQL_EQUALS_NULL_COMMA); } if (percentComplete != -1) { queryUpdateFunPimCalendar.append(SQL_FIELD_PERCENT_COMPLETE).append(SQL_EQUALS_QUESTIONMARK_COMMA); } else if ("0".equals(complete)) { queryUpdateFunPimCalendar.append(SQL_FIELD_PERCENT_COMPLETE).append(SQL_PERCENT_COMPLETE_FORMULA); } if (folder != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_FOLDER).append(SQL_EQUALS_QUESTIONMARK_COMMA); } if (dstartTimeZone != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_START_DATE_TIME_ZONE) .append(SQL_EQUALS_QUESTIONMARK_COMMA); } if (dendTimeZone != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_END_DATE_TIME_ZONE) .append(SQL_EQUALS_QUESTIONMARK_COMMA); } if (reminderTimeZone != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_REMINDER_TIME_ZONE) .append(SQL_EQUALS_QUESTIONMARK_COMMA); } queryUpdateFunPimCalendar.append(SQL_FIELD_STATUS).append(SQL_EQUALS_QUESTIONMARK) .append(SQL_UPDATE_FNBL_PIM_CALENDAR_END); con = getUserDataSource().getRoutedConnection(userId); ps = con.prepareStatement(queryUpdateFunPimCalendar.toString()); int k = 1; // // lastUpdate // ps.setLong(k++, lastUpdate.getTime()); // // allDay // if (allDayBoolean != null) { ps.setString(k++, allDay); } // // body // if (body != null) { if (body.length() > SQL_BODY_DIM) { body = body.substring(0, SQL_BODY_DIM); } ps.setString(k++, body); } // // busy status // if (busyStatus != null) { ps.setShort(k++, busyStatus.shortValue()); } else { ps.setNull(k++, Types.SMALLINT); } // // categories // if (categories != null) { if (categories.length() > SQL_CATEGORIES_DIM) { categories = categories.substring(0, SQL_CATEGORIES_DIM); } ps.setString(k++, categories); } // // companies // if (companies != null) { if (companies.length() > SQL_COMPANIES_DIM) { companies = companies.substring(0, SQL_COMPANIES_DIM); } ps.setString(k++, companies); } // // duration // ps.setInt(k++, duration); // // date End // if (dend != null) { ps.setTimestamp(k++, new Timestamp(dend.getTime())); } // // priority // if (priority != null && priority.length() > 0) { ps.setShort(k++, importance); } // // location // if (location != null) { if (location.length() > SQL_COMPANIES_DIM) { location = location.substring(0, SQL_LOCATION_DIM); } ps.setString(k++, location); } // // meeting status // if (meetingStatus != null) { ps.setShort(k++, meetingStatus.shortValue()); } // // mileage // if (mileage != null) { ps.setString(k++, mileage); } // // reminder // if (reminder != null) { if (reminder.isActive()) { ps.setTimestamp(k++, getReminderTime(dstart, reminder)); ps.setInt(k++, reminder.getRepeatCount()); String soundFileValue = reminder.getSoundFile(); if (soundFileValue != null && soundFileValue.length() > SQL_SOUNDFILE_DIM) { soundFileValue = soundFileValue.substring(0, SQL_SOUNDFILE_DIM); } ps.setString(k++, soundFileValue); ps.setInt(k++, reminder.getOptions()); } else { ps.setNull(k++, Types.TIMESTAMP); ps.setInt(k++, 0); ps.setString(k++, null); ps.setInt(k++, 0); } } // // reply time // if (replyTime != null) { ps.setTimestamp(k++, new Timestamp(replyTime.getTime())); } // // sensitivity // ps.setShort(k++, sensitivity); // // date start // if (dstart != null) { ps.setTimestamp(k++, new Timestamp(dstart.getTime())); } // // subject // if (subject != null) { if (subject.length() > SQL_SUBJECT_DIM) { subject = subject.substring(0, SQL_SUBJECT_DIM); } ps.setString(k++, subject); } // // recurrence Type // ps.setShort(k++, recurrenceType); // // interval // ps.setInt(k++, interval); // // month of year // ps.setShort(k++, monthOfYear); // // day of month // ps.setShort(k++, dayOfMonth); // // day of week mask // if (dayOfWeekMask != null) { if (dayOfWeekMask.length() > SQL_DAYOFWEEKMASK_DIM) { dayOfWeekMask = dayOfWeekMask.substring(0, SQL_DAYOFWEEKMASK_DIM); } ps.setString(k++, dayOfWeekMask); } // // instance // ps.setShort(k++, instance); // // start date pattern // if (startDatePattern != null) { if (startDatePattern.length() > SQL_STARTDATEPATTERN_DIM) { startDatePattern = startDatePattern.substring(0, SQL_STARTDATEPATTERN_DIM); } ps.setString(k++, startDatePattern); } // // no end date // if (noEndDate != null) { ps.setString(k++, noEndDate); } // // end date pattern // if (endDatePattern != null) { if (endDatePattern.length() > SQL_ENDDATEPATTERN_DIM) { endDatePattern = endDatePattern.substring(0, SQL_ENDDATEPATTERN_DIM); } ps.setString(k++, endDatePattern); } // // occurrences // ps.setInt(k++, occurrences); // // completed // if (completed != null) { ps.setTimestamp(k++, new Timestamp(completed.getTime())); } // // percent completed // if (percentComplete != -1) { ps.setShort(k++, percentComplete); } // // folder // if (folder != null) { if (folder.length() > SQL_FOLDER_DIM) { folder = folder.substring(0, SQL_FOLDER_DIM); } ps.setString(k++, folder); } // // time zones // if (dstartTimeZone != null) { if (dstartTimeZone.length() > SQL_TIME_ZONE_DIM) { dstartTimeZone = dstartTimeZone.substring(0, SQL_TIME_ZONE_DIM); } ps.setString(k++, dstartTimeZone); } if (dendTimeZone != null) { if (dendTimeZone.length() > SQL_TIME_ZONE_DIM) { dendTimeZone = dendTimeZone.substring(0, SQL_TIME_ZONE_DIM); } ps.setString(k++, dendTimeZone); } if (reminderTimeZone != null) { if (reminderTimeZone.length() > SQL_TIME_ZONE_DIM) { reminderTimeZone = reminderTimeZone.substring(0, SQL_TIME_ZONE_DIM); } ps.setString(k++, reminderTimeZone); } // // status // ps.setString(k++, String.valueOf('U')); // // id // ps.setLong(k++, Long.parseLong(id)); // // user id // ps.setString(k++, cw.getUserId()); ps.executeUpdate(); DBTools.close(null, ps, null); ps = con.prepareStatement(SQL_DELETE_CALENDAR_EXCEPTIONS_BY_CALENDAR); ps.setLong(1, Long.parseLong(id)); ps.executeUpdate(); DBTools.close(null, ps, null); if (recurrenceType != -1) { List<ExceptionToRecurrenceRule> exceptions = rp.getExceptions(); for (ExceptionToRecurrenceRule etrr : exceptions) { ps = con.prepareStatement(SQL_INSERT_INTO_FNBL_PIM_CALENDAR_EXCEPTION); ps.setLong(1, Long.parseLong(id)); ps.setString(2, (etrr.isAddition() ? "1" : "0")); ps.setTimestamp(3, new Timestamp(getDateFromString(allDayB, etrr.getDate(), "000000").getTime())); ps.executeUpdate(); DBTools.close(null, ps, null); } } } catch (Exception e) { throw new DAOException("Error updating a calendar item: " + e.getMessage()); } finally { DBTools.close(con, ps, rs); } return id; }
From source file:com.krawler.esp.servlets.deskeramob.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods. * @param request servlet request/*from w w w. j a va2 s . co m*/ * @param response servlet response */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SessionExpiredException { response.setContentType("text/html;charset=UTF-8"); ResultSet rs = null; ResultSet rsForSubQ = null; PreparedStatement pstmt = null; String result = ""; Connection conn = null; try { conn = DbPool.getConnection(); int action = Integer.parseInt(request.getParameter("action")); int mode = Integer.parseInt(request.getParameter("mode")); switch (action) { case 0: // generate application id String u = request.getParameter("u"); String p = request.getParameter("p"); String d = request.getParameter("d"); String udid = request.getParameter("udid"); result = generateAppID(conn, u, p, d, udid); break; case 1: // dashboard request int limit = 15, offset = 0; String userid = getUserid(conn, request.getParameter("applicationid").toString()); String projectlist = DashboardHandler.getProjectList(conn, userid, 1000, 0, ""); JSONArray projList = null; try { JSONObject projListObj = new JSONObject(projectlist); projList = projListObj.getJSONArray("data"); } catch (JSONException ex) { result = "{\"data\":[{\"success\":false,\"data\":" + ex.getMessage() + "}]}"; } switch (mode) { case 1: // due tasks try { PreparedStatement pstmt1 = null; JSONObject jobj = new JSONObject(); String query = "Select count(post_id) AS count from mailmessages inner join users " + "on users.userid = mailmessages.poster_id where folder = ? and to_id = ? and readflag = false ORDER BY post_time"; pstmt1 = conn.prepareStatement(query); pstmt1.setString(1, "0"); pstmt1.setString(2, userid); ResultSet rs1 = pstmt1.executeQuery(); int count = 0; if (rs1.next()) { count = rs1.getInt("count"); } if (projList == null) { result = "{\"success\":[{\"result\": true}],\"msgcount\":[{\"count\":\"" + Integer.toString(count) + "\"}],\"data\":[]}"; } else { for (int i = 0; i < projList.length(); i++) { JSONObject temp = projList.getJSONObject(i); String projid = temp.getString("id"); String qry = ""; String projName = ""; boolean moderator = DashboardHandler.isModerator(conn, userid, projid); if (!moderator) { qry = "(SELECT taskid FROM proj_task WHERE projectid=? AND taskid NOT IN " + "(SELECT taskid FROM proj_taskresourcemapping)) " + "UNION " + "(SELECT taskid FROM proj_taskresourcemapping WHERE resourceid = ? " + "AND taskid IN (SELECT taskid FROM proj_task WHERE projectid = ?))"; pstmt1 = conn.prepareStatement(qry); pstmt1.setString(1, projid); pstmt1.setString(2, userid); pstmt1.setString(3, projid); } else { qry = "SELECT taskid FROM proj_task WHERE projectid = ?"; pstmt1 = conn.prepareStatement(qry); pstmt1.setString(1, projid); } rs1 = pstmt1.executeQuery(); String tids = ""; while (rs1.next()) { tids += "'" + rs1.getString("taskid") + "',"; } if (tids.length() > 0) { tids = tids.substring(0, (tids.length() - 1)); pstmt1 = conn.prepareStatement( "SELECT projectname FROM project WHERE projectid = ?"); pstmt1.setString(1, projid); rs1 = pstmt1.executeQuery(); if (rs1.next()) { projName = rs1.getString("projectname"); } qry = "SELECT priority,taskname,percentcomplete,DATE_FORMAT(startdate,'%D %b %y') AS startdate,DATE_FORMAT(enddate,'%D %b %y') AS enddate, taskid " + "FROM proj_task " + "WHERE percentcomplete < 100 AND taskid IN (" + tids + ") AND (date(enddate)>=date(now())) AND (date(startdate) <= date(now()))"; // pstmt.setInt(1, limit); pstmt1 = conn.prepareStatement(qry); rs1 = pstmt1.executeQuery(); while (rs1.next()) { JSONObject j = new JSONObject(); j.put("projectname", projName); j.put("taskname", rs1.getString("taskname")); j.put("taskid", rs1.getString("taskid")); j.put("complete", rs1.getString("percentcomplete")); j.put("startdate", rs1.getString("startdate")); j.put("enddate", rs1.getString("enddate")); int ptr = rs1.getInt("priority"); String pStr = "medium"; if (ptr == 0) { pStr = "low"; } else if (ptr == 2) { pStr = "high"; } j.put("priority", pStr); if (moderator) { String res = DashboardHandler.getTaskResources(conn, rs1.getString("taskid"), projid, userid); if (!StringUtil.equal(res, "{}")) { JSONObject jobj1 = new JSONObject(res); JSONArray jarr = jobj1.getJSONArray("data"); String resr = ""; for (int cnt = 0; cnt < jarr.length(); cnt++) { resr += jarr.getJSONObject(cnt).getString("resourcename") + ", "; } resr = resr.substring(0, (resr.length() - 2)); if (!StringUtil.isNullOrEmpty(resr)) { j.put("assignedto", resr); } } } jobj.append("data", j); } } } } if (jobj.has("data")) { JSONObject fin = new JSONObject(); fin.put("count", String.valueOf(count)); jobj.append("msgcount", fin); result = jobj.toString(); } else { result = "{\"success\":[{\"result\": true}],\"msgcount\":[{\"count\":\"" + Integer.toString(count) + "\"}],\"data\":[]}"; } } catch (JSONException ex) { } break; case 0: // overdue tasks try { JSONObject jobj = new JSONObject(); PreparedStatement pstmt1 = null; String query = "Select count(post_id) AS count from mailmessages inner join users " + "on users.userid = mailmessages.poster_id where folder = ? and to_id = ? and readflag = false ORDER BY post_time"; pstmt1 = conn.prepareStatement(query); pstmt1.setString(1, "0"); pstmt1.setString(2, userid); ResultSet rs1 = pstmt1.executeQuery(); int count = 0; if (rs1.next()) { count = rs1.getInt("count"); } if (projList == null) { result = "{\"success\":[{\"result\": true}],\"msgcount\":[{\"count\":\"" + Integer.toString(count) + "\"}],\"data\":[]}"; } else { for (int i = 0; i < projList.length(); i++) { JSONObject temp = projList.getJSONObject(i); String projid = temp.getString("id"); String qry = ""; String projName = ""; boolean moderator = DashboardHandler.isModerator(conn, userid, projid); if (!moderator) { qry = "(SELECT taskid FROM proj_task WHERE projectid=? AND taskid NOT IN " + "(SELECT taskid FROM proj_taskresourcemapping)) " + "UNION " + "(SELECT taskid FROM proj_taskresourcemapping WHERE resourceid = ?)"; pstmt1 = conn.prepareStatement(qry); pstmt1.setString(1, projid); pstmt1.setString(2, userid); } else { qry = "SELECT taskid FROM proj_task WHERE projectid = ?"; pstmt1 = conn.prepareStatement(qry); pstmt1.setString(1, projid); } rs1 = pstmt1.executeQuery(); String tids = ""; while (rs1.next()) { tids += "'" + rs1.getString("taskid") + "',"; } if (tids.length() > 0) { tids = tids.substring(0, (tids.length() - 1)); pstmt1 = conn.prepareStatement( "SELECT projectname FROM project WHERE projectid = ?"); pstmt1.setString(1, projid); rs1 = pstmt1.executeQuery(); if (rs1.next()) { projName = rs1.getString("projectname"); } qry = "SELECT priority,taskname,percentcomplete, datediff(CURRENT_DATE,date(enddate)) as overdueby,DATE_FORMAT(enddate,'%D %b %y') AS enddate, taskid " + "FROM proj_task " + "WHERE percentcomplete < 100 AND taskid IN (" + tids + ") AND date(proj_task.enddate) < date(now()) LIMIT ? OFFSET ?"; pstmt1 = conn.prepareStatement(qry); pstmt1.setInt(1, limit); pstmt1.setInt(2, offset); rs1 = pstmt1.executeQuery(); while (rs1.next()) { JSONObject j = new JSONObject(); j.put("projectname", projName); j.put("taskid", rs1.getString("taskid")); j.put("taskname", rs1.getString("taskname")); j.put("complete", rs1.getString("percentcomplete")); j.put("overdueby", rs1.getString("overdueby")); j.put("enddate", rs1.getString("enddate")); int ptr = rs1.getInt("priority"); String pStr = "medium"; if (ptr == 0) { pStr = "low"; } else if (ptr == 2) { pStr = "high"; } j.put("priority", pStr); if (moderator) { String res = DashboardHandler.getTaskResources(conn, rs1.getString("taskid"), projid, userid); if (!StringUtil.equal(res, "{}")) { JSONObject jobj1 = new JSONObject(res); JSONArray jarr = jobj1.getJSONArray("data"); String resr = ""; for (int cnt = 0; cnt < jarr.length(); cnt++) { resr += jarr.getJSONObject(cnt).getString("resourcename") + ", "; } resr = resr.substring(0, (resr.length() - 2)); if (!StringUtil.isNullOrEmpty(resr)) { j.put("assignedto", resr); } } } jobj.append("data", j); } } } } if (jobj.has("data")) { JSONObject fin = new JSONObject(); fin.put("count", count); jobj.append("msgcount", fin); result = jobj.toString(); } else { result = "{\"success\":[{\"result\": true}],\"msgcount\":[{\"count\":\"" + Integer.toString(count) + "\"}],\"data\":[]}"; } } catch (JSONException ex) { } break; case 2: // calendar events PreparedStatement pstmt1 = null; String query = "Select count(post_id) AS count from mailmessages inner join users " + "on users.userid = mailmessages.poster_id where folder = ? and to_id = ? and readflag = false ORDER BY post_time"; pstmt1 = conn.prepareStatement(query); pstmt1.setString(1, "0"); pstmt1.setString(2, userid); ResultSet rs1 = pstmt1.executeQuery(); int count = 0; if (rs1.next()) { count = rs1.getInt("count"); } String sqlquery = "SELECT calendarevents.subject,project.projectname, DATE_FORMAT(calendarevents.startts,'%D %b %y') AS 'startdate' ," + "DATE_FORMAT(calendarevents.startts,'%h:%i %p') AS 'starttime'," + "CASE calendarevents.priority WHEN 'm' THEN 'Medium' WHEN 'l' THEN 'Low' WHEN 'h' THEN 'High' END AS priority" + " FROM calendarevents INNER JOIN calendars ON calendars.cid =calendarevents.cid INNER JOIN project ON project.projectid = calendars.userid " + " WHERE project.projectid IN (SELECT project.projectid FROM project INNER JOIN projectmembers ON " + " projectmembers.projectid = project.projectid WHERE userid = ?) AND calendars.timestamp> ? " + " AND date(startts)>=CURRENT_DATE AND date(startts)<=(ADDDATE(CURRENT_DATE, 7)) ORDER BY startts LIMIT ? OFFSET ?"; pstmt = conn.prepareStatement(sqlquery); pstmt.setString(1, userid); pstmt.setString(2, "1970-01-01 00:00:00"); pstmt.setInt(3, limit); pstmt.setInt(4, offset); rsForSubQ = pstmt.executeQuery(); try { JSONObject jobj = new JSONObject(); while (rsForSubQ.next()) { JSONObject j = new JSONObject(); j.put("subject", rsForSubQ.getString("subject")); j.put("projectname", rsForSubQ.getString("projectname")); j.put("startdate", rsForSubQ.getString("startdate")); j.put("starttime", rsForSubQ.getString("starttime")); j.put("priority", rsForSubQ.getString("priority")); jobj.append("data", j); } if (jobj.has("data")) { JSONObject fin = new JSONObject(); fin.put("count", String.valueOf(count)); jobj.append("msgcount", fin); result = jobj.toString(); } else { result = "{\"success\":true,\"data\":[]}"; } } catch (JSONException ex) { result = "{\"success\":false,\"data\":" + ex.getMessage() + "}"; } //result = kwljson.GetJsonForGrid(rsForSubQ); break; case 3: // unread personal msgs query = "Select count(post_id) AS count from mailmessages inner join users " + "on users.userid = mailmessages.poster_id where folder = ? and to_id = ? and readflag = false ORDER BY post_time"; pstmt = conn.prepareStatement(query); pstmt.setString(1, "0"); pstmt.setString(2, userid); rsForSubQ = pstmt.executeQuery(); count = 0; if (rsForSubQ.next()) { count = rsForSubQ.getInt("count"); } query = "Select post_id ,concat(fname,' ',lname) as post_fullname,userlogin.username as poster_id , post_text , post_subject ," + " DATE_FORMAT(post_time,'%D %b %y %h:%i%p') as post_time from mailmessages inner join users " + "on users.userid = mailmessages.poster_id inner join userlogin on users.userid =userlogin.userid where folder = ? and to_id = ? and readflag = false ORDER BY post_time DESC"; pstmt = conn.prepareStatement(query); pstmt.setString(1, "0"); pstmt.setString(2, userid); // pstmt.setInt(3, limit); // pstmt.setInt(4, offset); rsForSubQ = pstmt.executeQuery(); //result = kwljson.GetJsonForGrid(rsForSubQ); try { JSONObject jobj = new JSONObject(); String companyid = getCompanyID(conn, userid); String subdomain = CompanyHandler.getCompanySubdomainByCompanyID(conn, companyid); while (rsForSubQ.next()) { JSONObject j = new JSONObject(); j.put("post_id", rsForSubQ.getString("post_id")); j.put("post_fullname", rsForSubQ.getString("post_fullname")); j.put("poster_id", rsForSubQ.getString("poster_id")); j.put("post_text", insertSmiley(rsForSubQ.getString("post_text"), URLUtil.getPageURL(request, com.krawler.esp.web.resource.Links.loginpageFull, subdomain))); j.put("post_subject", rsForSubQ.getString("post_subject")); j.put("post_time", rsForSubQ.getString("post_time")); jobj.append("data", j); } if (jobj.has("data")) { JSONObject temp = new JSONObject(); temp.put("count", String.valueOf(count)); jobj.append("msgcount", temp); result = jobj.toString(); } else { result = "{\"success\":true,\"data\":[]}"; } } catch (JSONException ex) { result = "{\"success\":false,\"data\":" + ex.getMessage() + "}"; } break; case 4: // list of users's projects String companyid = getCompanyID(conn, userid); boolean isSuperUser = DashboardHandler.isSuperUser(conn, companyid, userid); try { JSONObject projectList = new JSONObject( DashboardHandler.getProjectListMember(conn, userid, 10, 0)); JSONArray projArray = projectList.getJSONArray("data"); int prc = projArray.length(); JSONObject jobj = new JSONObject(); if (prc > 0) { for (int i = 0; i < projArray.length(); i++) { JSONObject j = new JSONObject(); j.put("name", projArray.getJSONObject(i).getString("name")); j.put("id", projArray.getJSONObject(i).getString("id")); jobj.append("data", j); } } if (jobj.has("data")) { result = jobj.toString(); } else { result = "{\"success\":true,\"data\":[]}"; } } catch (JSONException ex) { result = "{\"success\":false,\"data\":" + ex.getMessage() + "}"; } break; case 5: // list of project resources String responseText = projdb.getProjectResources(conn, request.getParameter("id")); try { JSONObject jobj = new JSONObject(); if (responseText.compareTo("{data:{}}") != 0) { JSONObject resJOBJ = new JSONObject(responseText); JSONArray jArray = resJOBJ.getJSONArray("data"); int prec = jArray.length(); if (prec > 0) { for (int i = 0; i < prec; i++) { JSONObject j = new JSONObject(); j.put("name", jArray.getJSONObject(i).getString("resourcename")); j.put("id", jArray.getJSONObject(i).getString("resourceid")); jobj.append("data", j); } } } if (jobj.has("data")) { result = jobj.toString(); } else { result = "{\"success\":true,\"data\":[]}"; } } catch (JSONException ex) { result = "{\"success\":false,\"data\":" + ex.getMessage() + "}"; } break; case 6:// display project list companyid = getCompanyID(conn, userid); result = AdminServlet.getProjData(conn, request, companyid, ""); break; case 7:// fetch assigned/unassigned members result = getAssiUnAssiProjctMembers(conn, request.getParameter("projectid")); break; case 8:// isSuperUser companyid = getCompanyID(conn, userid); boolean isSuper = DashboardHandler.isSuperUser(conn, companyid, userid); JSONObject temp = new JSONObject(); JSONObject jobj = new JSONObject(); temp.put("superuser", isSuper); jobj.append("data", temp); result = jobj.toString(); break; case 9:// manage members String userids = request.getParameter("userid"); if (StringUtil.isNullOrEmpty(userids)) { result = "{\"result\":[{\"success\":false}], \"msg\":[{\"msgText\": \"Can not remove all project members.\"}]}"; } else { userids = userids.substring(0, (userids.length() - 1)); String[] uids = userids.split(","); String pid = request.getParameter("projectid"); result = manageMembers(conn, pid, uids); } break; case 10: // company user list companyid = getCompanyID(conn, userid); String companyMembers = AdminServlet.getAdminUserData(conn, request, companyid, "", false); // result = "{\"result\":[{\"success\":false}], \"msg\":[{\"msgText\": \"Could not complete your request.\"}]}"; result = "{\"result\":[{\"success\":true}],\"data\":" + companyMembers + "}"; break; } break; case 2: // Update Records userid = getUserid(conn, request.getParameter("applicationid").toString()); switch (mode) { case 1:// set read flag String post_id = request.getParameter("post_id"); String query = "update mailmessages set readflag=true where post_id = ?"; pstmt = conn.prepareStatement(query); pstmt.setString(1, post_id); int rows = pstmt.executeUpdate(); if (rows > 0) { conn.commit(); result = "{\"success\":true}"; } else { result = "{\"success\":false}"; } break; case 2:// update percent value for record String taskid = request.getParameter("taskid"); String pcomplete = request.getParameter("complete"); query = "update proj_task set percentcomplete = ? where taskid = ?"; pstmt = conn.prepareStatement(query); pstmt.setString(1, pcomplete); pstmt.setString(2, taskid); rows = pstmt.executeUpdate(); if (rows > 0) { conn.commit(); result = "{\"success\":true}"; } else { result = "{\"success\":false}"; } break; case 3:// insert tasks try { String projId = request.getParameter("projectid"); pstmt = conn.prepareStatement( "select max(taskindex) as maxindex from proj_task where projectid=?"); pstmt.setString(1, projId); rs = pstmt.executeQuery(); int rowindex = 0; if (rs.next()) { rowindex = rs.getInt(1) + 1; } java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy/MM/dd"); UUID ud = new UUID(2312, 4123); taskid = ud.randomUUID().toString(); String taskname = request.getParameter("name"); String stdate = request.getParameter("start"); String enddate = request.getParameter("end"); int priority = 1; if (!StringUtil.isNullOrEmpty(request.getParameter("priority"))) { priority = Integer.parseInt(request.getParameter("priority")); } String duration = "1"; String nonworkdays = projdb.getNonWorkWeekdays(conn, projId); String Holidays = projdb.getCmpHolidaydays(conn, projId); JSONObject nmweekObj = new JSONObject(nonworkdays); int nonworkweekArr[] = new int[nmweekObj.getJSONArray("data").length()]; for (int cnt = 0; cnt < nmweekObj.getJSONArray("data").length(); cnt++) { nonworkweekArr[cnt] = Integer .parseInt(nmweekObj.getJSONArray("data").getJSONObject(cnt).getString("day")); } String holidayArr[] = new String[1]; holidayArr[0] = ""; if (Holidays.compareTo("{data:{}}") != 0) { nmweekObj = new JSONObject(Holidays); holidayArr = new String[nmweekObj.getJSONArray("data").length()]; for (int cnt = 0; cnt < nmweekObj.getJSONArray("data").length(); cnt++) { holidayArr[cnt] = nmweekObj.getJSONArray("data").getJSONObject(cnt) .getString("holiday"); } } duration = projectReport.calculateWorkingDays(sdf.parse(stdate), sdf.parse(enddate), nonworkweekArr, holidayArr) + ""; pstmt = conn.prepareStatement( "INSERT INTO proj_task(taskid, taskname, duration, startdate, enddate, projectid, " + "taskindex, level, parent, actualstartdate, actualduration, percentcomplete, notes, priority, " + "isparent) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); pstmt.setString(1, taskid); pstmt.setString(2, taskname); pstmt.setString(3, duration); java.util.Date DateVal = sdf.parse(stdate); Timestamp ts = new Timestamp(DateVal.getTime()); pstmt.setTimestamp(4, ts); DateVal = sdf.parse(enddate); ts = new Timestamp(DateVal.getTime()); pstmt.setTimestamp(5, ts); pstmt.setString(6, projId); pstmt.setString(7, String.valueOf(rowindex)); pstmt.setString(8, "0"); pstmt.setString(9, "0"); DateVal = sdf.parse(stdate); ts = new Timestamp(DateVal.getTime()); pstmt.setTimestamp(10, ts); pstmt.setString(11, duration); pstmt.setString(12, "0"); pstmt.setString(13, ""); pstmt.setInt(14, priority); pstmt.setBoolean(15, false); boolean flag = pstmt.execute(); if (!request.getParameter("assignto").equals("")) { String[] resArray = request.getParameter("assignto").split(","); for (int i = 0; i < resArray.length; i++) { int dur = 0; String rid = resArray[i]; pstmt = conn.prepareStatement( "insert into proj_taskresourcemapping (taskid,resourceid,resduration) values(?,?,?)"); pstmt.setString(1, taskid); pstmt.setString(2, rid); pstmt.setInt(3, dur); pstmt.execute(); } } conn.commit(); result = "{\"success\":[{\"result\":true}]}"; } catch (ParseException ex) { Logger.getLogger(deskeramob.class.getName()).log(Level.SEVERE, null, ex); result = "{\"success\":[{\"result\":false}],\"data\":" + ex.getMessage() + "}"; } catch (JSONException ex) { result = "{\"success\":[{\"result\":false}],\"data\":" + ex.getMessage() + "}"; } break; case 5: // import contacts result = importContacts(conn, request); break; case 6: // export contacts result = exportContacts(conn, request); if (!StringUtil.isNullOrEmpty(result)) { result = "{\"import\":[{\"result\":true}]," + result.substring(1, (result.length() - 1)) + "}"; } else { result = "{\"import\":[{\"result\":true,\"error\":\"There seem to be some problem with server. Could not import contacts.\"}]}"; } break; } break; case 3: // company updates switch (mode) { case 2: //create project userid = getUserid(conn, request.getParameter("applicationid").toString()); String subdomain = CompanyHandler.getCompanySubdomainByUser(conn, userid); String companyid = getCompanyID(conn, userid); result = createProject(conn, request, companyid, subdomain, userid); if (StringUtil.equal("success", result)) { result = "{\"success\":[{\"result\":true}]}"; } else { result = "{\"success\":[{\"result\":false,\"error\":\"" + result + "\"}]}"; } break; case 3: //delete project userid = getUserid(conn, request.getParameter("applicationid").toString()); companyid = getCompanyID(conn, userid); result = AdminServlet.deleteProject(conn, request, companyid, userid, "iPhone"); if (StringUtil.equal("failure", result)) { result = "{\"success\":[{\"result\":false}]}"; DbPool.quietRollback(conn); } else { result = "{\"success\":[{\"result\":true}]}"; conn.commit(); } break; } break; } } catch (JSONException ex) { Logger.getLogger(deskeramob.class.getName()).log(Level.SEVERE, null, ex); result = "{\"success\":[{\"result\":\"1\"}],\"data\":" + ex.getMessage() + "}"; } catch (ServiceException ex) { result = "{\"success\":[{\"result\":\"0\"}],\"data\":" + ex.getMessage() + "}"; } catch (SQLException ex) { result = "{\"success\":[{\"result\":\"0\"}],\"data\":" + ex.getMessage() + "}"; } finally { DbPool.quietClose(conn); response.getWriter().println(result); } response.getWriter().close(); }
From source file:com.krawler.esp.servlets.deskeramob_V1.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods. * @param request servlet request//www .j a v a2 s .c o m * @param response servlet response */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SessionExpiredException { response.setContentType("text/html;charset=UTF-8"); ResultSet rs = null; ResultSet rsForSubQ = null; PreparedStatement pstmt = null; String result = ""; boolean android = false; Connection conn = null; try { conn = DbPool.getConnection(); int action = Integer.parseInt(request.getParameter("action")); int mode = Integer.parseInt(request.getParameter("mode")); if (!StringUtil.isNullOrEmpty(request.getParameter("android"))) { android = Boolean.parseBoolean(request.getParameter("android")); } switch (action) { case 0: // generate application id String u = request.getParameter("u"); String p = request.getParameter("p"); String d = request.getParameter("d"); result = authUser(conn, u, p, d); break; case 1: // dashboard request int limit = 15, offset = 0; String userid = request.getParameter("userid").toString(); String projectlist = DashboardHandler.getProjectList(conn, userid, 1000, 0, ""); JSONArray projList = null; try { JSONObject projListObj = new JSONObject(projectlist); projList = projListObj.getJSONArray("data"); } catch (JSONException ex) { result = "{\"data\":[{\"success\":false,\"data\":" + ex.getMessage() + "}]}"; } switch (mode) { case 1: // due tasks try { boolean projMod = false; for (int c = 0; c < projList.length(); c++) { JSONObject te = projList.getJSONObject(c); if (DashboardHandler.isModerator(conn, userid, te.getString("id"))) { projMod = true; break; } } PreparedStatement pstmt1 = null; JSONObject jobj = new JSONObject(); String query = "Select count(post_id) AS count from mailmessages inner join users " + "on users.userid = mailmessages.poster_id where folder = ? and to_id = ? and readflag = false ORDER BY post_time"; pstmt1 = conn.prepareStatement(query); pstmt1.setString(1, "0"); pstmt1.setString(2, userid); ResultSet rs1 = pstmt1.executeQuery(); int count = 0; if (rs1.next()) { count = rs1.getInt("count"); } if (projList == null) { result = "{\"success\":[{\"result\": true}],\"msgcount\":[{\"count\":\"" + Integer.toString(count) + "\"}],\"data\":[]}"; } else { for (int i = 0; i < projList.length(); i++) { JSONObject temp = projList.getJSONObject(i); String projid = temp.getString("id"); String qry = ""; String projName = ""; boolean moderator = DashboardHandler.isModerator(conn, userid, projid); if (!moderator) { qry = "(SELECT taskid FROM proj_task WHERE projectid=? AND taskid NOT IN " + "(SELECT taskid FROM proj_taskresourcemapping)) " + "UNION " + "(SELECT taskid FROM proj_taskresourcemapping WHERE resourceid = ? " + "AND taskid IN (SELECT taskid FROM proj_task WHERE projectid = ?))"; pstmt1 = conn.prepareStatement(qry); pstmt1.setString(1, projid); pstmt1.setString(2, userid); pstmt1.setString(3, projid); } else { qry = "SELECT taskid FROM proj_task WHERE projectid = ?"; pstmt1 = conn.prepareStatement(qry); pstmt1.setString(1, projid); } rs1 = pstmt1.executeQuery(); String tids = ""; while (rs1.next()) { tids += "'" + rs1.getString("taskid") + "',"; } if (tids.length() > 0) { tids = tids.substring(0, (tids.length() - 1)); pstmt1 = conn.prepareStatement( "SELECT projectname FROM project WHERE projectid = ?"); pstmt1.setString(1, projid); rs1 = pstmt1.executeQuery(); if (rs1.next()) { projName = rs1.getString("projectname"); } qry = "SELECT priority,taskname,percentcomplete,DATE_FORMAT(startdate,'%D %b %y') AS startdate,DATE_FORMAT(enddate,'%D %b %y') AS enddate, taskid " + "FROM proj_task " + "WHERE percentcomplete < 100 AND taskid IN (" + tids + ") AND (date(enddate)>=date(now())) AND (date(startdate) <= date(now()))"; // pstmt.setInt(1, limit); pstmt1 = conn.prepareStatement(qry); rs1 = pstmt1.executeQuery(); while (rs1.next()) { JSONObject j = new JSONObject(); j.put("projectname", projName); j.put("taskname", rs1.getString("taskname")); j.put("taskid", rs1.getString("taskid")); j.put("complete", rs1.getString("percentcomplete")); j.put("startdate", rs1.getString("startdate")); j.put("enddate", rs1.getString("enddate")); int ptr = rs1.getInt("priority"); String pStr = "medium"; if (ptr == 0) { pStr = "high"; } else if (ptr == 2) { pStr = "low"; } j.put("priority", pStr); if (moderator) { String res = DashboardHandler.getTaskResources(conn, rs1.getString("taskid"), projid, userid); if (!StringUtil.equal(res, "{}")) { JSONObject jobj1 = new JSONObject(res); JSONArray jarr = jobj1.getJSONArray("data"); String resr = ""; for (int cnt = 0; cnt < jarr.length(); cnt++) { resr += jarr.getJSONObject(cnt).getString("resourcename") + ", "; } resr = resr.substring(0, (resr.length() - 2)); if (!StringUtil.isNullOrEmpty(resr)) { j.put("assignedto", resr); } } } jobj.append("data", j); } } } } if (jobj.has("data")) { JSONObject fin = new JSONObject(); fin.put("count", String.valueOf(count)); jobj.append("msgcount", fin); jobj.put("moderator", projMod); result = jobj.toString(); } else { result = "{\"success\":[{\"result\": true}],\"msgcount\":[{\"count\":\"" + Integer.toString(count) + "\"}],\"data\":[],\"moderator\":" + Boolean.toString(projMod) + "}"; } } catch (JSONException ex) { } break; case 0: // overdue tasks try { JSONObject jobj = new JSONObject(); PreparedStatement pstmt1 = null; String query = "Select count(post_id) AS count from mailmessages inner join users " + "on users.userid = mailmessages.poster_id where folder = ? and to_id = ? and readflag = false ORDER BY post_time"; pstmt1 = conn.prepareStatement(query); pstmt1.setString(1, "0"); pstmt1.setString(2, userid); ResultSet rs1 = pstmt1.executeQuery(); int count = 0; if (rs1.next()) { count = rs1.getInt("count"); } if (projList == null) { result = "{\"success\":[{\"result\": true}],\"msgcount\":[{\"count\":\"" + Integer.toString(count) + "\"}],\"data\":[]}"; } else { for (int i = 0; i < projList.length(); i++) { JSONObject temp = projList.getJSONObject(i); String projid = temp.getString("id"); String qry = ""; String projName = ""; boolean moderator = DashboardHandler.isModerator(conn, userid, projid); if (!moderator) { qry = "(SELECT taskid FROM proj_task WHERE projectid=? AND taskid NOT IN " + "(SELECT taskid FROM proj_taskresourcemapping)) " + "UNION " + "(SELECT taskid FROM proj_taskresourcemapping WHERE resourceid = ?)"; pstmt1 = conn.prepareStatement(qry); pstmt1.setString(1, projid); pstmt1.setString(2, userid); } else { qry = "SELECT taskid FROM proj_task WHERE projectid = ?"; pstmt1 = conn.prepareStatement(qry); pstmt1.setString(1, projid); } rs1 = pstmt1.executeQuery(); String tids = ""; while (rs1.next()) { tids += "'" + rs1.getString("taskid") + "',"; } if (tids.length() > 0) { tids = tids.substring(0, (tids.length() - 1)); pstmt1 = conn.prepareStatement( "SELECT projectname FROM project WHERE projectid = ?"); pstmt1.setString(1, projid); rs1 = pstmt1.executeQuery(); if (rs1.next()) { projName = rs1.getString("projectname"); } qry = "SELECT priority,taskname,percentcomplete, datediff(CURRENT_DATE,date(enddate)) as overdueby,DATE_FORMAT(enddate,'%D %b %y') AS enddate, taskid " + "FROM proj_task " + "WHERE percentcomplete < 100 AND taskid IN (" + tids + ") AND date(proj_task.enddate) < date(now()) LIMIT ? OFFSET ?"; pstmt1 = conn.prepareStatement(qry); pstmt1.setInt(1, limit); pstmt1.setInt(2, offset); rs1 = pstmt1.executeQuery(); while (rs1.next()) { JSONObject j = new JSONObject(); j.put("projectname", projName); j.put("taskid", rs1.getString("taskid")); j.put("taskname", rs1.getString("taskname")); j.put("complete", rs1.getString("percentcomplete")); j.put("overdueby", rs1.getString("overdueby")); j.put("enddate", rs1.getString("enddate")); int ptr = rs1.getInt("priority"); String pStr = "medium"; if (ptr == 0) { pStr = "high"; } else if (ptr == 2) { pStr = "low"; } j.put("priority", pStr); if (moderator) { String res = DashboardHandler.getTaskResources(conn, rs1.getString("taskid"), projid, userid); if (!StringUtil.equal(res, "{}")) { JSONObject jobj1 = new JSONObject(res); JSONArray jarr = jobj1.getJSONArray("data"); String resr = ""; for (int cnt = 0; cnt < jarr.length(); cnt++) { resr += jarr.getJSONObject(cnt).getString("resourcename") + ", "; } resr = resr.substring(0, (resr.length() - 2)); if (!StringUtil.isNullOrEmpty(resr)) { j.put("assignedto", resr); } } } jobj.append("data", j); } } } } if (jobj.has("data")) { JSONObject fin = new JSONObject(); fin.put("count", count); jobj.append("msgcount", fin); result = jobj.toString(); } else { result = "{\"success\":[{\"result\": true}],\"msgcount\":[{\"count\":\"" + Integer.toString(count) + "\"}],\"data\":[]}"; } } catch (JSONException ex) { } break; case 2: // calendar events PreparedStatement pstmt1 = null; String query = "Select count(post_id) AS count from mailmessages inner join users " + "on users.userid = mailmessages.poster_id where folder = ? and to_id = ? and readflag = false ORDER BY post_time"; pstmt1 = conn.prepareStatement(query); pstmt1.setString(1, "0"); pstmt1.setString(2, userid); ResultSet rs1 = pstmt1.executeQuery(); int count = 0; if (rs1.next()) { count = rs1.getInt("count"); } String timezone = "+00:00"; timezone = Timezone.getTimeZone(conn, userid); String sqlquery = "SELECT calendarevents.subject,project.projectname, startts, endts, DATE_FORMAT(calendarevents.startts,'%D %b %y') AS 'startdate' ," + "DATE_FORMAT(calendarevents.startts,'%h:%i %p') AS 'starttime'," + "CASE calendarevents.priority WHEN 'm' THEN 'Medium' WHEN 'l' THEN 'Low' WHEN 'h' THEN 'High' END AS priority" + " FROM calendarevents INNER JOIN calendars ON calendars.cid =calendarevents.cid INNER JOIN project ON project.projectid = calendars.userid " + " WHERE project.projectid IN (SELECT project.projectid FROM project INNER JOIN projectmembers ON " + " projectmembers.projectid = project.projectid WHERE userid = ?) AND calendars.timestamp> ? " + " AND date(startts)>=CURRENT_DATE AND date(startts)<=(ADDDATE(CURRENT_DATE, 7)) ORDER BY startts LIMIT ? OFFSET ?"; pstmt = conn.prepareStatement(sqlquery); pstmt.setString(1, userid); pstmt.setString(2, "1970-01-01 00:00:00"); pstmt.setInt(3, limit); pstmt.setInt(4, offset); rsForSubQ = pstmt.executeQuery(); try { JSONObject jobj = new JSONObject(); while (rsForSubQ.next()) { timezone = timezone.substring(0, 4) + "00"; String startts = rsForSubQ.getString("startts"); startts = Timezone.toUserDefTimezone(conn, startts, timezone); Date startDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(startts); int date = Integer.parseInt(new SimpleDateFormat("d").format(startDate)); String ordinal = getDateOrdinal(date); JSONObject j = new JSONObject(); j.put("subject", rsForSubQ.getString("subject")); j.put("projectname", rsForSubQ.getString("projectname")); j.put("startdate", new SimpleDateFormat("d'".concat(ordinal).concat("' MMM yy")) .format(startDate)); j.put("starttime", new SimpleDateFormat("hh:mm a").format(startDate)); j.put("priority", rsForSubQ.getString("priority")); jobj.append("data", j); } if (jobj.has("data")) { JSONObject fin = new JSONObject(); fin.put("count", String.valueOf(count)); jobj.append("msgcount", fin); result = jobj.toString(); } else { result = "{\"success\":true,\"data\":[]}"; } } catch (ParseException ex) { result = "{\"success\":false,\"data\":" + ex.getMessage() + "}"; } catch (JSONException ex) { result = "{\"success\":false,\"data\":" + ex.getMessage() + "}"; } //result = kwljson.GetJsonForGrid(rsForSubQ); break; case 3: // unread personal msgs query = "Select count(post_id) AS count from mailmessages inner join users " + "on users.userid = mailmessages.poster_id where folder = ? and to_id = ? and readflag = false ORDER BY post_time"; pstmt = conn.prepareStatement(query); pstmt.setString(1, "0"); pstmt.setString(2, userid); rsForSubQ = pstmt.executeQuery(); count = 0; if (rsForSubQ.next()) { count = rsForSubQ.getInt("count"); } query = "Select post_id ,concat(fname,' ',lname) as post_fullname,userlogin.username as poster_id , post_text , post_subject ," + " post_time from mailmessages inner join users " + "on users.userid = mailmessages.poster_id inner join userlogin on users.userid=userlogin.userid where folder = ? and to_id = ? and readflag = false ORDER BY post_time DESC"; pstmt = conn.prepareStatement(query); pstmt.setString(1, "0"); pstmt.setString(2, userid); // pstmt.setInt(3, limit); // pstmt.setInt(4, offset); rsForSubQ = pstmt.executeQuery(); //result = kwljson.GetJsonForGrid(rsForSubQ); try { JSONObject jobj = new JSONObject(); String companyid = getCompanyID(conn, userid); String subdomain = CompanyHandler.getCompanySubdomainByCompanyID(conn, companyid); while (rsForSubQ.next()) { JSONObject j = new JSONObject(); String postTime = Timezone.toCompanyTimezone(conn, rsForSubQ.getString("post_time"), companyid); Date startDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(postTime); int date = Integer.parseInt(new SimpleDateFormat("d").format(startDate)); String ordinal = getDateOrdinal(date); j.put("post_id", rsForSubQ.getString("post_id")); j.put("post_fullname", rsForSubQ.getString("post_fullname")); j.put("poster_id", rsForSubQ.getString("poster_id")); j.put("post_text", insertSmiley(rsForSubQ.getString("post_text"), URLUtil.getPageURL(request, com.krawler.esp.web.resource.Links.loginpageFull, subdomain))); j.put("post_subject", rsForSubQ.getString("post_subject")); j.put("post_time", new SimpleDateFormat( "dd'".concat(ordinal).concat("' MMM yy").concat(" hh:mm a")) .format(startDate)); jobj.append("data", j); } if (jobj.has("data")) { JSONObject temp = new JSONObject(); temp.put("count", String.valueOf(count)); jobj.append("msgcount", temp); result = jobj.toString(); } else { result = "{\"success\":true,\"data\":[]}"; } } catch (ParseException ex) { result = "{\"success\":false,\"data\":" + ex.getMessage() + "}"; } catch (JSONException ex) { result = "{\"success\":false,\"data\":" + ex.getMessage() + "}"; } break; case 4: // list of users's projects String companyid = getCompanyID(conn, userid); // boolean isSuperUser = DashboardHandler.isSuperUser(conn, companyid, userid); try { JSONObject jobj = getUserProjectList(conn, userid); // JSONObject projectList = new JSONObject(DashboardHandler.getProjectListMember(conn, userid, 10, 0)); // JSONArray projArray = projectList.getJSONArray("data"); // int prc = projArray.length(); // JSONObject jobj = new JSONObject(); // if (prc > 0) { // for (int i = 0; i < projArray.length(); i++) { // JSONObject tempProj = projArray.getJSONObject(i); // if(tempProj.getInt("status") > 3){ // JSONObject j = new JSONObject(); // j.put("name", tempProj.getString("name")); // j.put("id", tempProj.getString("id")); // jobj.append("data", j); // } // } // } if (jobj.has("data")) { result = jobj.toString(); } else { result = "{\"success\":true,\"data\":[]}"; } } catch (JSONException ex) { result = "{\"success\":false,\"data\":" + ex.getMessage() + "}"; } break; case 5: // list of project resources try { JSONObject jobj = new JSONObject(); jobj = getProjectResourceList(conn, request); // String responseText = projdb.getProjectResources(conn, request.getParameter("id")); // try { // JSONObject jobj = new JSONObject(); // if (responseText.compareTo("{data:{}}") != 0) { // JSONObject resJOBJ = new JSONObject(responseText); // JSONArray jArray = resJOBJ.getJSONArray("data"); // int prec = jArray.length(); // if (prec > 0) { // for (int i = 0; i < prec; i++) { // JSONObject j = new JSONObject(); // j.put("name", jArray.getJSONObject(i).getString("resourcename")); // j.put("id", jArray.getJSONObject(i).getString("resourceid")); // jobj.append("data", j); // } // } // } if (jobj.has("data")) { result = jobj.toString(); } else { result = "{\"success\":true,\"data\":[]}"; } } catch (JSONException ex) { result = "{\"success\":false,\"data\":" + ex.getMessage() + "}"; } break; case 6:// display project list if (!AdminServlet.isCreator(conn, userid) || android) { result = projectlist; } else { companyid = getCompanyID(conn, userid); result = AdminServlet.getProjData(conn, request, companyid, ""); } break; case 7:// fetch assigned/unassigned members result = getAssiUnAssiProjctMembers(conn, request.getParameter("projectid")); break; case 8:// isSuperUser int projectcount = 0; companyid = getCompanyID(conn, userid); boolean isSuper = DashboardHandler.isSuperUser(conn, companyid, userid); JSONObject temp = new JSONObject(); JSONObject jobj = new JSONObject(); temp.put("superuser", isSuper); jobj.append("data", temp); try { JSONObject projectList = getUserProjectList(conn, userid); JSONArray projectArray = projectList.getJSONArray("data"); for (int c = 0; c < projectArray.length(); c++) { JSONObject te = projectArray.getJSONObject(c); request.setAttribute("id", te.getString("id")); JSONArray member = getProjectResourceList(conn, request).getJSONArray("data"); for (int i = 0; i < member.length(); i++) { JSONObject j = member.getJSONObject(i); jobj.append("projectmember", j); } jobj.append("data", te); projectcount++; } } catch (Exception e) { result = e.toString(); } jobj.put("projectcount", projectcount); jobj.put("userid", userid); result = jobj.toString(); break; case 9:// manage members String userids = request.getParameter("userid"); if (StringUtil.isNullOrEmpty(userids)) { result = "{\"result\":[{\"success\":false}], \"msg\":[{\"msgText\": \"Can not remove all project members.\"}]}"; } else { userids = userids.substring(0, (userids.length() - 1)); String[] uids = userids.split(","); String pid = request.getParameter("projectid"); result = manageMembers(conn, pid, uids, userid); } break; case 10: // company user list companyid = getCompanyID(conn, userid); String companyMembers = AdminServlet.getAdminUserData(conn, request, companyid, "", false); // result = "{\"result\":[{\"success\":false}], \"msg\":[{\"msgText\": \"Could not complete your request.\"}]}"; result = "{\"result\":[{\"success\":true}],\"data\":" + companyMembers + "}"; break; } break; case 2: // Update Records userid = request.getParameter("userid").toString(); switch (mode) { case 1:// set read flag String post_id = request.getParameter("post_id"); String query = "update mailmessages set readflag=true where post_id = ?"; pstmt = conn.prepareStatement(query); pstmt.setString(1, post_id); int rows = pstmt.executeUpdate(); if (rows > 0) { conn.commit(); result = "{\"success\":true}"; } else { result = "{\"success\":false}"; } break; case 2:// update percent value for record String taskid = request.getParameter("taskid"); String pcomplete = request.getParameter("complete"); query = "update proj_task set percentcomplete = ? where taskid = ?"; pstmt = conn.prepareStatement(query); pstmt.setString(1, pcomplete); pstmt.setString(2, taskid); rows = pstmt.executeUpdate(); if (rows > 0) { conn.commit(); result = "{\"success\":true}"; } else { result = "{\"success\":false}"; } break; case 3:// insert tasks try { String projId = request.getParameter("projectid"); pstmt = conn.prepareStatement( "select max(taskindex) as maxindex from proj_task where projectid=?"); pstmt.setString(1, projId); rs = pstmt.executeQuery(); int rowindex = 1; if (rs.next()) { rowindex = rs.getInt(1) + 1; } java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy/MM/dd"); taskid = UUID.randomUUID().toString(); String taskname = request.getParameter("name"); String stdate = request.getParameter("start"); String enddate = request.getParameter("end"); int priority = 1; if (!StringUtil.isNullOrEmpty(request.getParameter("priority"))) { priority = Integer.parseInt(request.getParameter("priority")); } pstmt = conn.prepareStatement( "INSERT INTO proj_task(taskid, taskname, duration, startdate, enddate, projectid, " + "taskindex, level, parent, actualstartdate, actualduration, percentcomplete, notes, priority, " + "isparent) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); pstmt.setString(1, taskid); pstmt.setString(2, taskname); java.util.Date DateVal = sdf.parse(stdate); java.util.Date sDate = sdf.parse(stdate); Timestamp ts = new Timestamp(DateVal.getTime()); pstmt.setTimestamp(4, ts); DateVal = sdf.parse(enddate); int duration = SchedulingUtilities.calculateWorkingDays(conn, projId, sDate, DateVal); pstmt.setString(3, String.valueOf(duration)); ts = new Timestamp(DateVal.getTime()); pstmt.setTimestamp(5, ts); pstmt.setString(6, projId); pstmt.setString(7, String.valueOf(rowindex)); pstmt.setString(8, "0"); pstmt.setString(9, "0"); DateVal = sdf.parse(stdate); ts = new Timestamp(DateVal.getTime()); pstmt.setTimestamp(10, ts); pstmt.setString(11, String.valueOf(duration)); //if(request.getParameter("completed").equals(null)) pstmt.setString(12, "0"); // else // pstmt.setString(12, request.getParameter("completed")); pstmt.setString(13, ""); pstmt.setInt(14, priority); pstmt.setBoolean(15, false); boolean flag = pstmt.execute(); if (!request.getParameter("assignto").equals("")) { String[] resArray = request.getParameter("assignto").split(","); for (int i = 0; i < resArray.length; i++) { int dur = 0; String rid = resArray[i]; pstmt = conn.prepareStatement( "insert into proj_taskresourcemapping (taskid,resourceid,resduration) values(?,?,?)"); pstmt.setString(1, taskid); pstmt.setString(2, rid); pstmt.setInt(3, dur); pstmt.execute(); } } conn.commit(); result = "{\"success\":[{\"result\":true}]}"; } catch (ParseException ex) { Logger.getLogger(deskeramob.class.getName()).log(Level.SEVERE, null, ex); result = "{\"success\":[{\"result\":false}],\"data\":" + ex.getMessage() + "}"; } break; case 4: // create user // String companyid = getCompanyID(conn, userid); // result = createUser(conn, request, companyid, userid); // if (!StringUtil.isNullOrEmpty(result) && result.contains("failure")) { // result = "{\"success\":[{\"result\":false,\"error\":\"Could not complete your request. Please try again later.\"}]}"; // } else { // conn.commit(); // result = "{\"success\":[{\"result\":true,\"error\":\"User created successfully.\"}]}"; // } break; case 5: // import contacts result = importContacts(conn, request); break; case 6: // export contacts result = exportContacts(conn, request); if (!StringUtil.isNullOrEmpty(result)) { result = "{\"import\":[{\"result\":true}]," + result.substring(1, (result.length() - 1)) + "}"; } else { result = "{\"import\":[{\"result\":true,\"error\":\"There seem to be some problem with server. Could not import contacts.\"}]}"; } break; } break; case 3: // company updates switch (mode) { case 1:// create new company // result = createNewCompany(conn, request, response); // JSONObject j = new JSONObject(result); // if (j.getJSONArray("data").getJSONObject(0).has("success") && j.getJSONArray("data").getJSONObject(0).getString("success").equals("true")) { // result = authUser(conn, request.getParameter("u"), request.getParameter("p"), request.getParameter("cdomain")); // } break; case 2: //create project userid = request.getParameter("userid").toString(); String subdomain = CompanyHandler.getCompanySubdomainByUser(conn, userid); String companyid = getCompanyID(conn, userid); result = createProject(conn, request, companyid, subdomain, userid); if (StringUtil.equal("success", result)) { result = "{\"success\":[{\"result\":true}]}"; } else { result = "{\"success\":[{\"result\":false,\"error\":\"" + result + "\"}]}"; } break; case 3: //delete project userid = request.getParameter("userid").toString(); companyid = getCompanyID(conn, userid); result = AdminServlet.deleteProject(conn, request, companyid, userid, "iPhone"); if (StringUtil.equal("failure", result)) { result = "{\"success\":[{\"result\":false}]}"; DbPool.quietRollback(conn); } else { result = "{\"success\":[{\"result\":true}]}"; conn.commit(); } break; } break; case 4: //Project Reports switch (mode) { case 0://Project chart(type="milestones") if (!StringUtil.isNullOrEmpty(request.getParameter("projid")) && !StringUtil.isNullOrEmpty(request.getParameter("uid"))) { result = DashboardHandler.getChartURL(conn, request.getParameter("projid"), request.getParameter("uid"), "progress"); } break; case 1: //milestone(rtype="milestone"), overdue(rtype="overdue"), task in progress(rtype="taskinprogress", percent=-99), un-started task(rtype="taskinprogress", percent=0) if (!StringUtil.isNullOrEmpty(request.getParameter("projid")) && !StringUtil.isNullOrEmpty(request.getParameter("userid"))) { result = projectReport.getProjectReportJson(request, false, false); if (result.compareToIgnoreCase("{data:{}}") == 0) { result = "{\"data\":[]}"; } } break; } break; } } catch (JSONException ex) { Logger.getLogger(deskeramob.class.getName()).log(Level.SEVERE, null, ex); result = "{\"success\":[{\"result\":\"1\"}],\"data\":" + ex.getMessage() + "}"; } catch (ServiceException ex) { result = "{\"success\":[{\"result\":\"0\"}],\"data\":" + ex.getMessage() + "}"; } catch (SQLException ex) { result = "{\"success\":[{\"result\":\"0\"}],\"data\":" + ex.getMessage() + "}"; } finally { DbPool.quietClose(conn); response.getWriter().println(result); } response.getWriter().close(); }