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

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

<%
	String module = "issueListReport";
	SQLUtil sqlUtil = new SQLUtil();
	Connection connection = sqlUtil.getConnection(delegator); 
	boolean useOracle = false;
	String lsOracle = "oracle";
	String lsDriver = "";
	String lsCompare = "";
	String lsQuery = " ";
	StringBuffer sbSql = new StringBuffer();
	String pName = "";
	String queryVal = "";
	String dbName = "";
	String paramCheck = "";
	String sectionName = "";
	
	try {
		SQLUtil sqlParseUtil = new SQLUtil();
	    Enumeration params = request.getParameterNames();
	    while(params.hasMoreElements()){
	   
			pName = (String)params.nextElement();
				
			dbName = UIWebUtility.getDbNameFromParamName("issueListReportQuery", 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);
	} 

   sbSql = new StringBuffer("select it_issue.issue_id, ");
	sbSql.append("it_issue.short_name, ");
	sbSql.append("t.code_value as issue_type, ");
	sbSql.append("p.code_value as priority, ");
	sbSql.append("project.code_value as project, ");
	sbSql.append("sys.code_value as system, ");
	sbSql.append("proc.code_value as procedure_name, ");
	sbSql.append("s.code_value as status, ");
	sbSql.append("concat(cat.first_name, ' ', cat.last_name) as assigned_to, ");
	sbSql.append("d.data_value as Notes, ");
	sbSql.append("concat(cmb.first_name, ' ', cmb.last_name) as submitted_by, ");
	sbSql.append("h.modified_date, ");
	sbSql.append("it_issue.due_date, ");
	sbSql.append("it_issue.estimated_hours ");
	sbSql.append("from it_issue, it_issue_history h, large_data d, code p, code s, code t, ");
	sbSql.append("code project, code sys, code proc, contact cmb, contact cat ");
	sbSql.append("where "+lsQuery+" it_issue.issue_id = h.issue_id ");
	sbSql.append("and h.data_id = d.data_id ");
	sbSql.append("and it_issue.status_id <> 'C' ");
	sbSql.append("and it_issue.status_id <> 'X' ");
	sbSql.append("and s.code_type_id = 'STATUS_ID' ");
	sbSql.append("and s.code_id = h.status_id ");
	sbSql.append("and p.code_type_id = 'PRIORITY_ID' ");
	sbSql.append("and p.code_id = h.priority_id ");
	sbSql.append("and project.code_id = it_issue.project_id ");
	sbSql.append("and project.code_type_id = 'PROJECT' ");
	sbSql.append("and sys.code_id = it_issue.system_id ");
	sbSql.append("and sys.code_type_id = 'SYSTEM' ");
	sbSql.append("and proc.code_id = it_issue.procedure_id ");
	sbSql.append("and proc.code_type_id = 'PROCEDURE' ");
	sbSql.append("and p.code_id = h.priority_id ");
	sbSql.append("and t.code_id = it_issue.issue_type_id ");
	sbSql.append("and t.code_type_id = 'ISSUE_TYPE_ID' ");
	sbSql.append("and cmb.contact_id = h.modified_by ");
	sbSql.append("and cat.contact_id = h.assigned_to ");
  //sbSql.append("'%" + userInfo.getRoleId() + "%'");
  sbSql.append(" order by it_issue.issue_id desc, h.modified_date  ");
  double rowCount = 0;

	Debug.logVerbose("FinalSQL:"+sbSql, module);

   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/IssueListReport.jasper"));
		Debug.logVerbose("filepath = " + application.getRealPath("/reports/IssueListReport.jasper") , module);
		Map parameters = new HashMap();
		parameters.put("ReportTitle", "Current Issues");
		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);
        }
      }

%>