leadexport.jsp

国外的一套开源CRM

<%@ page errorPage="jasperError.jsp" %>
<%@ 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" %>
<%@ page import="com.sourcetap.sfa.util.Preference" %>
<%@ page import="com.sourcetap.sfa.util.CsvConverter"%>
<jsp:useBean id="delegator" type="org.ofbiz.entity.GenericDelegator" scope="application" />
<% UserInfo userInfo  = (UserInfo) session.getAttribute("userInfo"); %>


<%
	StringBuffer sbSql = new StringBuffer("");
	String groupDisp = "";
	String pName = "";
	String dbName = "";
	String lsQuery = "";
	String queryVal = "";
	String fileName = application.getRealPath("/reports/leadExport.csv");
	String module = "leadExport";
	
	Preference preference = new Preference(delegator);
	String securityMode = preference.getPreference(userInfo.getAccountId(), "SECURITY_MODE", "team");

sbSql.append(" select distinct ");
sbSql.append(" lead.lead_owner_id, ");
sbSql.append(" lead.lead_type_id, ");
sbSql.append(" lead.first_name, ");
sbSql.append(" lead.last_name, ");
sbSql.append(" lead.company_name, ");
sbSql.append(" lead.business_phone, ");
sbSql.append(" lead.title, ");
sbSql.append(" lead.status_id, ");
sbSql.append(" lead.lead_source_id, ");
sbSql.append(" lead.lead_source_details, ");
sbSql.append(" lead.active_flag, ");
sbSql.append(" lead.rating_id, ");
sbSql.append(" address.mailing_address, ");
sbSql.append(" address.city, ");
sbSql.append(" address.state, ");
sbSql.append(" address.zip, ");
sbSql.append(" address.country, ");
sbSql.append(" lead.units_required ");
sbSql.append(" from ");
if ( securityMode.equals("territory") ) {
	sbSql.append(" role, contact, ");
}
if ( securityMode.equals("team") ) {
	sbSql.append(" team_member, ");
}
sbSql.append(" lead, address, entity_access ");
sbSql.append(" where ");
sbSql.append(" address.address_owner_id = lead.lead_id and ");
sbSql.append(" address.address_owner_type = 'Lead' and ");
	sbSql.append("  lead.account_id = ");
	sbSql.append("'" + userInfo.getAccountId() + "' ");
sbSql.append(" and lead.lead_Id = entity_access.entity_Id ");
sbSql.append(" and entity_access.entity = 'Lead' ");
if ( securityMode.equals("team") ) {
	sbSql.append(" and entity_access.party_entity_type = 'Team' ");
	sbSql.append(" and team_member.team_id = entity_access.party_id ");
}
if ( securityMode.equals("territory") ) {
	sbSql.append(" and entity_access.party_Entity_Type = 'Role' ");
	sbSql.append(" and role.role_Id = entity_access.party_id ");
	sbSql.append(" and instr(role.role_path, contact.role_id) > 0 ");
	sbSql.append(" and contact.contact_id = " + userInfo.getPartyId());
}

sbSql.append(" order by 1,2,3,4 ");

	Debug.logVerbose("Sql = " + sbSql.toString(), "leadExport" );

	double rowCount = 0;


   Connection connection = null;
   PreparedStatement preparedStatement = null;
   ResultSet rs = null;
      try{
        SQLUtil sqlUtil = new SQLUtil();
        connection = sqlUtil.getConnection(delegator);
        preparedStatement = connection.prepareStatement(sbSql.toString());
        rs = preparedStatement.executeQuery();

		if ( rs != null ) {
				FileWriter fw = new FileWriter (fileName, false);
				BufferedWriter br = new BufferedWriter (fw);
				PrintWriter pw = new PrintWriter (br);
				String exportString ="";
					
				BufferedReader myReader = new BufferedReader(new StringReader(exportString));
				CsvConverter csvConverter = new CsvConverter(myReader);
				
				StringBuffer expBuf = new StringBuffer("lead_owner_id,lead_type_id,first_name,last_name,company_name,business_phone,title,status_id,lead_source_id,lead_source_details,active_flag,rating_id,mailing_address,city,state,zip,country,units_required");
				exportString = expBuf.toString();
				//pw.println (csvConverter.createCSVField(exportString));
				pw.println (exportString);
		    while(rs.next()){
				expBuf = new StringBuffer();
				expBuf.append("\"");
				expBuf.append(rs.getString("lead_owner_id"));
				expBuf.append("\",\"");
				expBuf.append(rs.getString("lead_type_id"));
				expBuf.append("\",\"");
				expBuf.append(rs.getString("first_name"));
				expBuf.append("\",\"");
				expBuf.append(rs.getString("last_name"));
				expBuf.append("\",\"");
				expBuf.append(rs.getString("company_name"));
				expBuf.append("\",\"");
				expBuf.append(rs.getString("business_phone"));
				expBuf.append("\",\"");
				expBuf.append(rs.getString("title"));
				expBuf.append("\",\"");
				expBuf.append(rs.getString("status_id"));
				expBuf.append("\",\"");
				expBuf.append(rs.getString("lead_source_id"));
				expBuf.append("\",\"");
				expBuf.append(rs.getString("lead_source_details"));
				expBuf.append("\",\"");
				expBuf.append(rs.getString("active_flag"));
				expBuf.append("\",\"");
				expBuf.append(rs.getString("rating_id"));
				expBuf.append("\",\"");
				expBuf.append(rs.getString("mailing_address"));
				expBuf.append("\",\"");
				expBuf.append(rs.getString("city"));
				expBuf.append("\",\"");
				expBuf.append(rs.getString("state"));
				expBuf.append("\",\"");
				expBuf.append(rs.getString("zip"));
				expBuf.append("\",\"");
				expBuf.append(rs.getString("country"));
				expBuf.append("\",\"");
				expBuf.append(rs.getString("units_required"));
				expBuf.append("\"");
				
				exportString = expBuf.toString();
				pw.println (exportString);
				//pw.println (csvConverter.createCSVField(exportString));
				
		    }
		    pw.flush();
			pw.close();
			br.close();
			fw.close();

            File file = new File(fileName);
            FileInputStream fileIn = new FileInputStream(file);
			byte[] b = new byte[fileIn.available()];
			fileIn.read(b);
			fileIn.close();
			ServletOutputStream downLoad = response.getOutputStream();
			response.setContentType("application/zip");//or set the content type to a more specific file if you know what is going to be downloaded
			response.setHeader("Content-Disposition", "attachment;filename=" + file.getName());
			downLoad.write(b);
			response.getOutputStream().flush();
		    //out.write("Lead Export Completed");
		    
		    
		   
		} else {
			out.write("No Data found");
		}


      } catch(SQLException e){
        Debug.logError(e.getMessage(), module);
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        e.printStackTrace(new PrintWriter(baos));
        Debug.logError(new String(baos.toByteArray()), module);
      } 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);
      } finally {
        try{
          //if(resultSet != null) resultSet.close();
          if(preparedStatement != null) preparedStatement.close();
          if(connection != null) connection.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);
        }
      }

%>