csvimport.jsp

国外的一套开源CRM

<%@ page import="org.ofbiz.entity.*" %>
<%@ page import="org.ofbiz.entity.model.*" %>
<%@ page import="java.lang.reflect.Method" %>
<%@ page import="org.ofbiz.entity.util.SequenceUtil" %>
<%@ page import="com.sourcetap.sfa.ui.*" %>
<%@ page import="com.sourcetap.sfa.util.*" %>

<%@ page import="java.util.*, java.net.*" %>

<%@ page import="javax.servlet.ServletException" %>
<%@ page import="javax.servlet.http.HttpServletRequest" %>
<%@ page import="javax.servlet.http.HttpServletResponse" %>
<%@ page import="javax.servlet.http.HttpServlet" %>

<%@ page import="java.io.IOException" %>
<%@ page import="java.io.PrintWriter" %>
<%@ page import="java.io.InputStream" %>
<%@ page import="java.io.File" %>

<%@ page import="http.utils.multipartrequest.ServletMultipartRequest" %>
<%@ page import="http.utils.multipartrequest.MultipartRequest" %>
<%@ page import="com.sourcetap.sfa.replication.*" %>
<%@ page import="com.infomata.data.CSVFormat" %>
<%@ page import="com.infomata.data.DataFile" %>
<%@ page import="com.infomata.data.DataRow" %>

<%@ include file="/includes/header.jsp" %>


<%
	String screen = "";
	String screenSection = "";
	String action = "upload";

	try{
 
		if(request.getParameter("action") != null){
			//actions=search, update, create
			action = request.getParameter("action");
		}
					   
		if ( action.equals("start") )
		{
%>
			<table width="100%" height="100%" ><tr><td valign="top">
			      <!-- title table -->
			      <table width="100%" class="viewManyHeader" cellspacing="0" cellpadding="2"><tr><td>Import CSV File</td></tr></table>
			      <table width="100%" cellspacing="0" cellpadding="2">
			      <form method="post" action="/sfa/control/csvImport" ENCTYPE="multipart/form-data">
			           <input type=hidden name=action value="upload">
			           <table class=viewOneHeader>
			             <tr><td>Step 1. Find the file to upload.</td></tr>
			             <tr>
			                <td class="viewOneLabel">
			                   <input type="file" name="file" size="55">
			                </td>
			             </tr>
			             <tr>
			                <td class="viewOneLabel">
			                   <input type="submit" value="upload">
			                </td>
			             </tr>
			           </table>
			         </form>
			       </table>
			
			</td></tr></table>
<%
	   	}
	   	else if ( action.equals("upload") )
		{

			URL sfaPropertiesUrl = application.getResource("/WEB-INF/sfa.properties");
			
			String filePath = UtilProperties.getPropertyValue(sfaPropertiesUrl, "fileImport.path", "imports");
			
			if ( (filePath.indexOf('\\') == - 1) && (filePath.indexOf('/') == -1) )
				filePath = getServletContext().getRealPath("/") + filePath;
			
			File directory = new File(filePath);
			if ( !directory.isDirectory() )
			{
				boolean created = false;
				try {
					created = directory.mkdir();
				} catch ( Exception e) {}
				if ( !created )
				{
					throw new Exception("Unable to create directory:" + filePath);
				}
			}
			
			String fileName = "imp" + userInfo.getPartyId() + "-" + System.currentTimeMillis();
			
			MultipartRequest parser = new ServletMultipartRequest(request, filePath, fileName);
			
			File importFile = parser.getFile("file");
			
			if ( importFile == null )
			{
				out.write("<P><B>No file specified<P>");
				return;
			}
			
			String importFilePath = importFile.getCanonicalPath();
%>
			<table width="100%" height="100%" ><tr><td valign="top">
			      <!-- title table -->
			      <table width="100%" class="viewManyHeader" cellspacing="0" cellpadding="2"><tr><td>Choose Table to Import Into</td></tr></table>
			      <table width="100%" cellspacing="0" cellpadding="2">
			      <form method="post" action="/sfa/control/csvImport">
			           <input type=hidden name=action value="mapFields">
			           <input type=hidden name=importFile value="<%=importFilePath%>">
			           <table class=viewOneHeader>
			             <tr><td>Step 2. Choose a Table to import the Data into.</td></tr>
						 <TR><TD class=freeFormSectionField>
						    <SELECT NAME="importTable" CLASS="selectBox">
						    	<OPTION VALUE="NtContact" SELECTED>NtContact</OPTION>
						    	<OPTION VALUE="NtActivity">NtActivity</OPTION>
						    	<OPTION VALUE="NtChildren">NtChildren</OPTION>
						    	<OPTION VALUE="NtEmployment">NtEmployment</OPTION>
						    	<OPTION VALUE="NtExtDegree">NtExtDegree</OPTION>
						    	<OPTION VALUE="NtGift">NtGift</OPTION>
						    	<OPTION VALUE="NtNote">NtNote</OPTION>
						    	<OPTION VALUE="NtNtDegree">NtNtDegree</OPTION>
						    	<OPTION VALUE="NtPledge">NtPledge</OPTION>
						    	<OPTION VALUE="NtRelationship">NtRelationship</OPTION>
							</SELECT>
					        </TD>
					     </TR>
			             <tr>
			                <td class="viewOneLabel">
			                   <input type="submit" value="upload">
			                </td>
			             </tr>
			           </table>
			         </form>
			       </table>
			
			</td></tr></table>


<%
		}	
		else if ( action.equals("mapFields") )
		{
			String importTable = UtilFormatOut.checkNull(request.getParameter("importTable"));
			String importFile =  UtilFormatOut.checkNull(request.getParameter("importFile"));
%>
			<table width="100%" height="100%" ><tr><td valign="top">
			      <!-- title table -->
			      <table width="100%" class="viewManyHeader" cellspacing="0" cellpadding="2"><tr><td>Choose Table to Import Into</td></tr></table>
			      <table width="100%" cellspacing="0" cellpadding="2">
			      <form method="post" action="/sfa/control/csvImport">
			           <input type=hidden name=action value="import">
			           <input type=hidden name=importFile value="<%=importFile%>">
			           <input type=hidden name=importTable value="<%=importTable%>">
			           <table class=viewManyHeader>
			             <tr><td colspan=2>Step 3. Map fields in the File to the Table.</td></tr>
						 <tr><td class=freeFormSectionLabel>Table Field</td><td class=freeFormSectionLabel><center>File Field</center></td></tr>

<%
			ModelEntity entity = delegator.getModelEntity(importTable);
			List  fields = entity.getFieldsCopy();
			Iterator fieldIter = fields.iterator();
			
			DataFile read =  DataFile.createReader("8859_1");

			read.setDataFormat(new CSVFormat());
	
			// first line is column header
			read.containsHeader(false);
	
			read.open(new File(importFile));
		    DataRow header = read.next();
		    if ( header == null )
		    {
		    	out.write("<BR>No data in file");
		    	return;
		    }
			
			

			while (fieldIter.hasNext())
			{
				ModelField field = (ModelField) fieldIter.next();
				String fieldName = field.getName();
				
				if ( fieldName.equals("lastUpdatedStamp") || fieldName.equals("lastUpdatedTxStamp") ||
				     fieldName.equals("createdStamp") || fieldName.equals("createdTxStamp") )
				     continue;
%>
				<tr><td class=tabularSectionField><%=fieldName%></td><td class=tabularSectionField>
					<SELECT NAME="fld_<%=fieldName%>" CLASS="selectBox">
					   <OPTION VALUE=-1>NOT MAPPED</OPTION>
<%
					for ( int i = 0; i < header.size(); i ++)
					{
						String selected = "";
						String fileFieldName = header.getString(i);
						
						if ( fileFieldName.equals(fieldName) )
							selected = " selected";
							
%>						<OPTION VALUE=<%=i + selected%>><%=fileFieldName%></OPTION>
<%
					}
%>					</SELECT>
				    </td>
				</tr>
<%				
			}
			
%>				<tr>
			        <td class="viewOneLabel"><input type="submit" value="import"></td>
			    </tr>
			</table>
			</table>
			</td></tr>
			</table>
	
<%			
		}
		else if ( action.equals("import") )
		{
			String importTable = UtilFormatOut.checkNull(request.getParameter("importTable"));
			String importFile =  UtilFormatOut.checkNull(request.getParameter("importFile"));

			ModelEntity entity = delegator.getModelEntity(importTable);
			List  fields = entity.getFieldsCopy();
			
			Iterator fieldIter = fields.iterator();
			HashMap fldMapping = new HashMap();
			while (fieldIter.hasNext())
			{
				ModelField field = (ModelField) fieldIter.next();
				String fldLoc = request.getParameter("fld_" + field.getName());
				if ( ( fldLoc != null ) && (!fldLoc.equals("-1")))
				{
					try {
						Integer loc = Integer.valueOf(fldLoc);
						fldMapping.put(field, loc);
					}
					catch (NumberFormatException nfe)
					{
						//skip fld
						Debug.logError("error converting number " + fldLoc, "csvImport");
					}
				}
			}
			
			DataFile read =  DataFile.createReader("8859_1");

			read.setDataFormat(new CSVFormat());
	
			// first line is column header
			read.containsHeader(false);
	
			read.open(new File(importFile));
		    DataRow header = read.next();
		    if ( header == null )
		    {
		    	out.write("<BR>No data in file");
		    	return;
		    }
		    
			int i=0;
			long start = System.currentTimeMillis();
			
			for (DataRow row = read.next(); row != null; row = read.next()) 
			{

				i++;
				GenericValue rowGV = new GenericValue(entity);
        		rowGV.setDelegator(delegator);
				
				Iterator mapIter = fldMapping.entrySet().iterator();
				while ( mapIter.hasNext() )
				{
					Map.Entry entry = (Map.Entry) mapIter.next();
					ModelField field = (ModelField) entry.getKey();
					int loc = ((Integer) entry.getValue()).intValue();
					
					rowGV.setString( field.getName(), row.getString(loc));					
				}
				
				try {
					
					delegator.create(rowGV);
				}
				catch (GenericEntityException gee)
				{
					delegator.store(rowGV);
				}	
			
			}

			long end = System.currentTimeMillis();
			float time = (end - start)/1000;
			float per_record = time/i;

		read.close();
		
		out.write("<BR><h3>Imported " + i + " records in " + time + " seconds (" + per_record + "/per record");

		}
	}
	catch (Exception e)
	{
		try{
			InputStream in = request.getInputStream();
			while ( in.read() !=-1);
		}catch(Exception e2)
		{
		}
		e.printStackTrace();
		out.write("<P><I>" + e.toString());
	}

%>  


<%@ include file="/includes/footer.jsp" %>