opportunitypipelinereport.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.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" %>
<%
	String module = "opportunityPipelineReport";
%>
<%@ include file="/reports/piplineChartGen.jsp" %>


<%
	rowCount = 0;
	//Create the sql statement
	sbSql = new StringBuffer("select distinct ");

	connection = sqlUtil.getConnection(delegator); 

	sbSql.append(" code.code_id as status_id, account.account_name, deal.deal_id as opportunity_id, 2 as sort_order, account.account_id, deal.deal_name as 'opportunity_name', deal_stage.stage_id, deal_stage.stage_name as opportunity_stage, deal.projected_close_date, ");
	sbSql.append(" code.code_value as 'opportunity_status_name', deal_stage.probability as 'Probability', ");
	sbSql.append(" crole.code_value as sales_role, ");
	sbSql.append(" concat(  contact.first_name, ' ', contact.last_name) as sales_team, ");
	sbSql.append(" code.code_value deal_status_name, ");
	sbSql.append(" 0 as 'projected_amount' ");
	sbSql.append(" from deal LEFT JOIN deal_stage on deal.stage_id = deal_stage.stage_id, ");
    sbSql.append(" deal dt LEFT JOIN entity_access ea1 on dt.deal_id = ea1.entity_id LEFT JOIN team_member on ea1.party_id = team_member.team_id LEFT JOIN contact on team_member.party_id = contact.contact_id, ");
	sbSql.append(" deal dd LEFT JOIN code on dd.deal_status_id = code.code_id, ");
	sbSql.append(" deal ddd LEFT JOIN opportunity_product on (ddd.deal_id = opportunity_product.deal_id ");
	sbSql.append(" and ddd.use_products_for_amount = 1 ), ");
	sbSql.append(" entity_access, account, role, code crole");
	sbSql.append(" where "+lsQuery+" dd.deal_status_id = deal.deal_status_id and dd.deal_id = deal.deal_id and ddd.deal_id = deal.deal_id and ddd.deal_id = dd.deal_id and ");
	sbSql.append("  dt.deal_id = deal.deal_id and ");
	sbSql.append("  account.account_id = deal.account_id and ");
	sbSql.append("  account.account_id = dd.account_id and ");
	sbSql.append("  account.account_id = ddd.account_id and ");
	sbSql.append("  account.account_id = dt.account_id and ");
	sbSql.append("  code.code_type_id = 'OPPORTUNITY_STATUS' and ");
	sbSql.append("  code.code_value not like '%lost%' and ");
	sbSql.append("  code.code_value not like '%sold%' and ");
	sbSql.append("  crole.code_type_id = 'OPP_TEAM_MEMBER_ROLE' and ");
	sbSql.append("  crole.code_id = team_member.team_member_role_id and ");
	sbSql.append("  account.account_id = entity_access.entity_id and ");
	sbSql.append("  ea1.entity = 'Deal' and ");
	sbSql.append("  ea1.party_entity_type = 'Team' and ");
	sbSql.append("  entity_access.entity = 'Account' and ");
	sbSql.append("  entity_access.party_entity_type = 'Role' and ");
	sbSql.append("  role.role_id = entity_access.party_id and ");
	sbSql.append("  role.account_id = ");
	sbSql.append("'" + userInfo.getAccountId() + "' ");
	sbSql.append(" group by code.code_id, account.account_name, deal.deal_id, team_member.team_member_role_id, team_member.party_id  ");
	sbSql.append(" UNION ");
	sbSql.append(" select code.code_id as status_id, account.account_name, deal.deal_id as opportunity_id, 1 as sort_order, account. account_id, deal.deal_name as 'opportunity_name', deal_stage.stage_id, deal_stage.stage_name as opportunity_stage, deal.projected_close_date, ");
	sbSql.append(" code.code_value as 'opportunity_status_name', deal_stage.probability as 'Probability', ");
	sbSql.append(" ' ' as sales_role, ' ' as sales_team, ");
	sbSql.append(" code.code_value deal_status_name, ");
	sbSql.append(" if(deal.use_products_for_amount = 1, sum(opportunity_product.amount), deal.amount) as 'projected_amount' ");
	sbSql.append(" from deal LEFT JOIN deal_stage on deal.stage_id = deal_stage.stage_id, ");
    sbSql.append(" deal dd LEFT JOIN code on dd.deal_status_id = code.code_id, ");
	sbSql.append(" deal ddd LEFT JOIN opportunity_product on (ddd.deal_id = opportunity_product.deal_id), ");
	sbSql.append(" entity_access, account, role ");
	sbSql.append(" where "+lsQuery+" dd.deal_status_id = deal.deal_status_id and dd.deal_id = deal.deal_id and ddd.deal_id = deal.deal_id and ddd.deal_id = dd.deal_id and ");
	sbSql.append("  account.account_id = deal.account_id and ");
	sbSql.append("  account.account_id = dd.account_id and ");
	sbSql.append("  account.account_id = ddd.account_id and ");
	sbSql.append("  code.code_type_id = 'OPPORTUNITY_STATUS' and ");
	sbSql.append("  code.code_value not like '%lost%' and ");
	sbSql.append("  code.code_value not like '%sold%' and ");
	sbSql.append("  account.account_id = entity_access.entity_id and ");
	sbSql.append("  entity_access.entity = 'Account' and ");
	sbSql.append("  entity_access.party_entity_type = 'Role' and ");
	sbSql.append("  role.role_id = entity_access.party_id and ");
	sbSql.append("  role.account_id = ");
	sbSql.append("'" + userInfo.getAccountId() + "' ");
	sbSql.append(" group by code.code_id, account.account_name, deal.deal_id ");

	sbSql.append(" order by 1, 2, 3, 4 ");
	preparedStatement = null;
    rs = null;
      try{
        preparedStatement = connection.prepareStatement(sbSql.toString());
        rs = preparedStatement.executeQuery();
            
		File reportFile = new File(application.getRealPath("/reports/PipelineReport.jasper"));
		Debug.logVerbose("filepath = " + application.getRealPath("/reports/PipelineReport.jasper"), module );
		
		//Set the pie chart properties
		pieChart.setProperty("writeDirectory", application.getRealPath("/reports") );
		pieChart.setProperty("fileName", userInfo.getRoleId() + "Pipeline.png");
		pieChart.setProperty("width", "875");
		pieChart.setProperty("height", "438"); 
		Map parameters = new HashMap();
		
		parameters.put("ReportTitle", "Pipeline Report");
		parameters.put("PipelineGraph", pieChart.getFileName() );
		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(rs != null) rs.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);
        }
      }
	
%>