leadreport.jsp

国外的一套开源CRM

<%@ page errorPage="jasperError.jsp" %>
<%@ page import="dori.jasper.engine.*" %>
<%@ page import="dori.jasper.engine.util.*" %>
<%@ page import="dori.jasper.engine.export.*" %>
<%@ 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.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.Preference" %>
<%@ page import="com.sourcetap.sfa.util.UserInfo" %>
<%@ page import="com.sourcetap.sfa.ui.UIWebUtility" %>

<jsp:useBean id="delegator" type="org.ofbiz.entity.GenericDelegator" scope="application" />
<% UserInfo userInfo  = (UserInfo) session.getAttribute("userInfo"); %>

  
<%
	String module = "leadReport";
	StringBuffer sbSql = new StringBuffer("");
	String groupDisp = "";
	String pName = "";
	String dbName = "";
	String lsQuery = "";
	String queryVal = "";

	Preference preference = new Preference(delegator);
	String securityMode = preference.getPreference(userInfo.getAccountId(), "SECURITY_MODE", "team");



String groupName = request.getParameter("groupName");
if (groupName.equals("owner")) {
	groupName = "concat(c.first_name, ' ', c.last_name)";
}
if (groupName.equals("status")) {
	groupName = "cstat.code_value ";
}
if (groupName.equals("type")) {
	groupName = "ctype.code_value ";
}
if (groupName.equals("source")) {
	groupName = "code.code_value";
}
sbSql.append(" select distinct ");
sbSql.append( groupName +" rpt_group_name, ");
sbSql.append(" concat(c.first_name, ' ', c.last_name) as lead_owner, ");
sbSql.append(" ctype.code_value as lead_type, ");
sbSql.append(" concat(lead.first_name, ' ', lead.last_name) as lead_name, ");
sbSql.append(" lead.company_name, ");
sbSql.append(" lead.business_phone, ");
sbSql.append(" cstat.code_value as status, ");
sbSql.append(" lead.lead_source_details as lead_source_name, ");
sbSql.append(" code.code_value as lead_source_type, ");
sbSql.append(" address.mailing_address, ");
sbSql.append(" address.city, ");
sbSql.append(" address.state, ");
sbSql.append(" address.zip, ");
sbSql.append(" address.country, ");
sbSql.append(" lead.created_date as created, ");
sbSql.append(" lead.modified_date as last_update ");
sbSql.append(" from ");
sbSql.append(" lead LEFT OUTER JOIN code ON code.code_id = lead.lead_source_id and code.code_type_id = 'LEAD_SOURCE_TYPE' ");

sbSql.append(" LEFT OUTER JOIN code cstat ON cstat.code_type_id = 'LEAD_STATUS' and cstat.code_id = lead.status_id "); 
sbSql.append(" LEFT OUTER JOIN code ctype ON ctype.code_type_id = 'LEAD_TYPE'  and ctype.code_id = lead.lead_type_id ,"); 

if ( securityMode.equals("territory") ) {
	sbSql.append(" role, contact, ");
}
if ( securityMode.equals("team") ) {
	sbSql.append(" team_member, ");
}
sbSql.append(" contact c, address, entity_access   ");
sbSql.append(" where c.contact_id = lead.lead_owner_id and ");
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 " + groupName + ",2,3,4,5 ");
	  
	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();
           
		File reportFile = new File(application.getRealPath("/reports/leadReport.jasper"));
		Debug.logVerbose("filepath = " + application.getRealPath("/reports/leadReport.jasper"), module );
		Map parameters = new HashMap();
		parameters.put("ReportTitle", "Lead Report");
		//parameters.put("BaseDir", reportFile.getParentFile());
		byte[] bytes;
		if ( rs != null ) {		
		    Debug.logVerbose("Calling Jasper" , module);	
			bytes = 
				JasperManager.runReportToPdf(
					reportFile.getPath(), 
					parameters, 
					new JRResultSetDataSource(rs)
					);
		} else {		
			bytes = 
				JasperManager.runReportToPdf(
					reportFile.getPath(), 
					parameters, 
					new JREmptyDataSource()
					);
		}
	
		response.setContentType("application/pdf");
		response.setContentLength(bytes.length);
		ServletOutputStream ouputStream = response.getOutputStream();
		ouputStream.write(bytes, 0, bytes.length);
		ouputStream.flush();
		ouputStream.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);
      } 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);
        }
      }
      
%>