contacttransfer.jsp

国外的一套开源CRM

<%@ page errorPage="jasperError.jsp" %>
<%@ page import="dori.jasper.engine.*" %>
<%@ page import="java.util.*" %>
<%@ page import="java.io.*" %>
<%@ page import="java.sql.*" %>
<%@ page import="java.awt.*" %>
<%@ page import="com.sourcetap.sfa.sql.*" %>
<%@ page import="org.ofbiz.entity.util.SequenceUtil" %>
<%@ page import="org.ofbiz.core.security.*" %>
<%@ page import="org.ofbiz.entity.*" %>
<%@ page import="org.ofbiz.entity.model.*" %>
<%@ page import="org.ofbiz.base.util.*" %>
<%@ page import="com.sourcetap.sfa.ui.*" %>
<%@ page import="com.sourcetap.sfa.event.*" %>
<%@ page import="com.sourcetap.sfa.util.UserInfo" %>
<%@ page import="com.sourcetap.sfa.ui.UIWebUtility" %>
<link rel="stylesheet" href="/sfa/includes/maincss.css" type="text/css">
<jsp:useBean id="delegator" type="org.ofbiz.entity.GenericDelegator" scope="application" />
<% UserInfo userInfo  = (UserInfo) session.getAttribute("userInfo"); %>


<%

   String action = "";
   String oldContact = "";
   String newContact = "";
   String oldRole = "";
   String newRole = "";
   StringBuffer sbSql = new StringBuffer(" ");
   int numStatements = 11;
   int currStatement = 1;

   if(request.getParameter("action") != null){
     //actions=search, update, create
     action = request.getParameter("action");
   }
%>
     <!-- title table -->
     <table width="100%"><tr><td><div class="viewOneHeader">User Data Transfer</div></td></tr></table>

     <table border=0 width='100%' class="viewManyHeader'>
       <tr>
       <td width='100%' height='100%'>
         <table cellspacing="0" cellpadding="2" width="100%">
           <form method="post" action="contactTransfer">
             <input type="hidden" value="transfer" name="action" >
            <tr>
                <td width='20%'><b>From Old User (Contact ID)</b></div></td> <td width='80%'><input type="text" name="oldContact" value="<%=oldContact%>" ></td>
            </tr>
            <tr>
                <td width='20%'><b>To New User (Contact ID)</b></div></td> <td width='80%'><input type="text" name="newContact" value="<%=newContact%>" ></td>
            </tr>
            <tr>
                <td></td><td width='20%'><input width='80%' type="submit" value="transfer" ></div></td>
            </tr>
            </form>
           </table>
         </td></tr>
     </table>
<%
   //handle transfer of data from a user no longer with the company to a new hire.
   //we use SQL here for speed of transfer... may decide to use generic values later.
   if(action.equals("transfer")){
		oldContact = request.getParameter("oldContact");
		newContact = request.getParameter("newContact");

		if ( newContact.equals( null ) || newContact.equals( " " ) || newContact.equals("") ){
			out.write("<br><B> Please fill in the tranfer 'To New' user. </B>");
			currStatement = 999;
		}
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		SQLUtil sqlUtil = new SQLUtil();
		connection = sqlUtil.getConnection(delegator);

		while( currStatement <= numStatements ) {
			sbSql = new StringBuffer(" ");
			preparedStatement = null;
			rs = null;

			if ( currStatement == 1 ) {
				out.write("<br> Updating account owners... ");
				sbSql.append("update account set account_owner_id = " + newContact + " where account_owner_id = " + oldContact );
			}
			if ( currStatement == 2 ) {
				out.write("<br> Updating account creators... ");
				sbSql.append("update account set created_by = " + newContact + " where created_by = " + oldContact );
			}
			if ( currStatement == 3 ) {
				out.write("<br> Updating account modifiers... ");
				sbSql.append("update account set modified_by = " + newContact + " where modified_by = " + oldContact );
			}
			if ( currStatement == 4 ) {
				out.write("<br> Updating activities... ");
				sbSql.append("update activity set contact_id = " + newContact + " where contact_id = " + oldContact );
			}
			if ( currStatement == 5 ) {
				out.write("<br> Updating activity owners... ");
				sbSql.append("update activity set activity_owner_id = " + newContact + " where activity_owner_id = " + oldContact );
			}

			if ( currStatement == 6 ) {
				//get duplicate activities where both users were attending the same activity
				out.write("<br> Removing duplicate activities... ");
				StringBuffer isbSql = new StringBuffer(" ");
				PreparedStatement ipreparedStatement = null;
				ResultSet irs = null;
				ResultSet iirs = null;
				SQLUtil isqlUtil = new SQLUtil();

				isbSql.append(" select a.activity_id, a.contact_id from activity_contact a, activity_contact b where a.activity_id = b.activity_id ");
				isbSql.append(" and a.contact_id = " + oldContact );
				isbSql.append(" and b.contact_id = " + newContact  );

				try{
                	ipreparedStatement = connection.prepareStatement(isbSql.toString());
                	irs = ipreparedStatement.executeQuery();
                } catch(SQLException e){
                	Debug.logError(e.getMessage(), module);
                	ByteArrayOutputStream baos = new ByteArrayOutputStream();
                	e.printStackTrace(new PrintWriter(baos));
                	Debug.logError(new String(baos.toByteArray()), module);
                	out.write("SQL Error see tomcat log file! " + e.getMessage());
                	currStatement = 999;
                } catch(Exception e){
                	e.printStackTrace();
                	Debug.logError(e.getMessage(), module);
                	ByteArrayOutputStream baos = new ByteArrayOutputStream();
                	e.printStackTrace(new PrintWriter(baos));
                	Debug.logError(new String(baos.toByteArray()), module);
                	out.write("Error see tomcat log file! " + e.getMessage());
                	currStatement = 999;
                }


				while(irs.next()){
					//delete duplicate activities where both the old and new user were attending the same activity
					isbSql = new StringBuffer(" ");
					PreparedStatement iipreparedStatement = null;
					iirs = null;
					String delActivityId = "";
					delActivityId = irs.getString("activity_id");
					isbSql.append(" delete from activity_contact where contact_id = " + oldContact + " and activity_id = " + delActivityId );

					try{
                		iipreparedStatement = connection.prepareStatement(isbSql.toString());
                		iirs = iipreparedStatement.executeQuery();
					} catch(SQLException e){
                		Debug.logError(e.getMessage(), module);
                		ByteArrayOutputStream baos = new ByteArrayOutputStream();
                		e.printStackTrace(new PrintWriter(baos));
                		Debug.logError(new String(baos.toByteArray()), module);
                		out.write("SQL Error see tomcat log file! " + e.getMessage());
                		currStatement = 999;
					} catch(Exception e){
                		e.printStackTrace();
                		Debug.logError(e.getMessage(), module);
                		ByteArrayOutputStream baos = new ByteArrayOutputStream();
                		e.printStackTrace(new PrintWriter(baos));
                		Debug.logError(new String(baos.toByteArray()), module);
                		out.write("Error see tomcat log file! " + e.getMessage());
                		currStatement = 999;
					} finally {
                		try{
                		if(iirs != null) iirs.close();
                		if(iipreparedStatement != null) iipreparedStatement.close();
                		} catch (SQLException e){
                		Debug.logError(e.getMessage(), module);
                		ByteArrayOutputStream baos = new ByteArrayOutputStream();
                		e.printStackTrace(new PrintWriter(baos));
                		Debug.logError(new String(baos.toByteArray()), module);
                		out.write("Error see tomcat log file! " + e.getMessage());
                		currStatement = 999;
                		}
					}
				}
				if(irs != null) irs.close();
				if(ipreparedStatement != null) ipreparedStatement.close();
				out.write("<br> Updating activity contacts... ");
				sbSql.append("update activity_contact set contact_id = " + newContact + " where contact_id = " + oldContact );

			}
			if ( currStatement == 7 ) {
				sbSql.append("update contact set contact_owner_id = " + newContact + " where contact_owner_id = " + oldContact );
				out.write("<br> Updating contact owners... ");
			}
			if ( currStatement == 8 ) {
				sbSql.append("update contact_file set contact_id = " + newContact + " where contact_id = " + oldContact );
				out.write("<br> Updating contact files... ");
			}
			if ( currStatement == 9 ) {
				sbSql.append("update deal set owner_id = " + newContact + " where owner_id = " + oldContact );
				out.write("<br> Updating opportunity owners... ");
			}

			if ( currStatement == 10 ){
				sbSql.append("update team_member set party_id = " + newContact + " where party_id = " + oldContact  );
				out.write("<br> Updating team members... ");
			}
			if ( currStatement == 11 ){
				sbSql.append("update contact set status_id = 'I', account_id = 999, role_id = '' where contact_id = " + oldContact );
				out.write("<br> Inactivating old contact... ");
				out.write("<br> Transfer of data complete.");
			}

			//skip role tranfer for now... may not want to change the role of the data owned by the previous owner
			//if ( currStatement == 10 ){
			//	sbSql.append("update enity_access set role_id = " newRoleId + " where role_id = " + oldRoleId );
			//	out.write("<br> Updating access roles... ");
			//}

            try{
                	preparedStatement = connection.prepareStatement(sbSql.toString());
                	rs = preparedStatement.executeQuery();
                } catch(SQLException e){
                	Debug.logError(e.getMessage(), module);
                	ByteArrayOutputStream baos = new ByteArrayOutputStream();
                	e.printStackTrace(new PrintWriter(baos));
                	Debug.logError(new String(baos.toByteArray()), module);
                	out.write("SQL Error see tomcat log file! " + e.getMessage());
                	currStatement = 999;
                } catch(Exception e){
                	e.printStackTrace();
                	Debug.logError(e.getMessage(), module);
                	ByteArrayOutputStream baos = new ByteArrayOutputStream();
                	e.printStackTrace(new PrintWriter(baos));
                	Debug.logError(new String(baos.toByteArray()), module);
                	out.write("Error see tomcat log file! " + e.getMessage());
                	currStatement = 999;
                } finally {
                	try{
                	if(rs != null) rs.close();
                	if(preparedStatement != null) preparedStatement.close();
                	} catch (SQLException e){
                	Debug.logError(e.getMessage(), module);
                	ByteArrayOutputStream baos = new ByteArrayOutputStream();
                	e.printStackTrace(new PrintWriter(baos));
                	Debug.logError(new String(baos.toByteArray()), module);
                	out.write("Error see tomcat log file! " + e.getMessage());
                	currStatement = 999;
                	}
                }

			currStatement ++;

		}

		//transfer complete
		if(connection != null) connection.close();

   }

%>

<table width="100%"><tr><td><div class="viewOneHeader"><%=oldContact%> -> <%=newContact%></div></td></tr></table>