activitiesreport.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.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" %>

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

  
<%
	String module = "activitiesReport";
	StringBuffer sbSql = new StringBuffer("");
	String groupBy = request.getParameter("ActivitiesReportQuery_Activity_activityId_0");
	String groupDisp = "";
	Debug.logVerbose("OrderBy = " + groupBy, module );
	String pName = "";
	String dbName = "";
	String lsQuery = "";
	String queryVal = "";


	if (groupBy.equals("account")) {
		groupBy = "activity.account_id";
		groupDisp = "account.account_name";
	} else if (groupBy.equals("owner")) {
		groupBy = "activity.activity_owner_id";
		groupDisp = "CONCAT(contact.first_name,' ',contact.last_name)";
	} else {
		groupBy = "activity.opportunity_id";
		groupDisp = "deal.deal_name";
	}



	try {
		SQLUtil sqlParseUtil = new SQLUtil();
	    Enumeration params = request.getParameterNames();
	    while(params.hasMoreElements()){
	   
			pName = (String)params.nextElement();
			//Debug.logVerbose("Parameter Name = " + pName, module );
			if (!pName.equals("ActivitiesReportQuery_Activity_activityId_0")){	
				dbName = UIWebUtility.getDbNameFromParamName("ActivitiesReportQuery", pName);		
				if ( dbName != null && dbName != "" ){
					queryVal =  request.getParameter(pName);	
					if ( queryVal == null || queryVal == "" || queryVal.length() < 1  ){
							//Skip this value
							//lsQuery = lsQuery + dbName + " like '%' and ";
					} else {
							lsQuery = lsQuery + dbName.toLowerCase() + " like '%" + queryVal + "%' and ";
					}
				}
			}
	    } 
	}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);
	} 

  Debug.logVerbose("Final query = " + lsQuery, module );




sbSql.append("  select ");
sbSql.append(groupBy +" rpt_group_by, ");
sbSql.append(groupDisp +" rpt_group_name, ");
sbSql.append("	activity.activity_id, ");
sbSql.append("	activity.activity_name, ");
sbSql.append("	activity.created_date,");
sbSql.append("	CONCAT(contact.first_name,' ',contact.last_name) owner_name,");
sbSql.append("	account.account_name,");
sbSql.append("	deal.deal_name opportunity_name,");
sbSql.append("	CONCAT(contact2.first_name,' ',contact2.last_name) participant_name,	");
sbSql.append("	contact2.title participant_title,");
sbSql.append("	activity.activity_description ");
sbSql.append("from ");
sbSql.append("	activity LEFT OUTER JOIN contact 		ON activity.activity_owner_id = contact.contact_id ");
sbSql.append("	LEFT OUTER JOIN activity_contact 	ON activity.activity_id = activity_contact.activity_id ");
sbSql.append("	LEFT OUTER JOIN deal 			ON activity.opportunity_id = deal.deal_id,");
sbSql.append("	account, entity_access, role, ");
sbSql.append("	contact contact2 "); 
sbSql.append("where ");
sbSql.append(lsQuery);
sbSql.append("	activity.account_id = account.account_id ");
sbSql.append("and	activity_contact.contact_id = contact2.contact_id ");
sbSql.append(" and " + groupDisp + " is not null ");
sbSql.append(" and " + groupDisp + " != ''");
sbSql.append(" and  account.account_id = entity_access.entity_id ");
sbSql.append(" and entity_access.entity = 'Account' ");
sbSql.append(" and entity_access.party_entity_type = 'Role' ");
sbSql.append(" and role.role_id = entity_access.party_id ");
sbSql.append(" and role.account_id = ");
sbSql.append("'" + userInfo.getAccountId() + "' ");
sbSql.append(" order by "+ groupDisp);
	
	Debug.logVerbose("Sql = " + sbSql.toString(), module );
  
	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/ActivityListReport.jasper"));
		Debug.logVerbose("filepath = " + application.getRealPath("/reports/ActivityListReport.jasper"), module );
		Map parameters = new HashMap();
		parameters.put("ReportTitle", "Activity List");
		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);
        }
      }
      
%>