piplinechartgen.jsp

国外的一套开源CRM

<%@ 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.base.util.*" %> 
<%@ page import="com.sourcetap.sfa.util.UserInfo" %>
<%@ page import = "javachart.servlet.pieApp" %>

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

<%
	double rowCount = 0;
	//Create the sql statement

	SQLUtil sqlUtil = new SQLUtil();
	Connection connection = sqlUtil.getConnection(delegator); 
	boolean useOracle = false;
	String lsOracle = "oracle";
	String lsDriver = "";
	String lsCompare = "";
	String lsQuery = " ";
	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("OpportunityPipelineReportQuery", 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 {
						if ( queryVal.equals("on") ) {
							queryVal = "Y";	
						} 
						
						if ( queryVal.equals("off") ) {
							queryVal = "N";	 	
						}
						
						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);
	} 


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


	StringBuffer sbSql = new StringBuffer("select sum(deal.amount) as amount, code.code_value as name, code.code_id ");
	sbSql.append("from deal, ");
	sbSql.append("code, ");
	sbSql.append("account, entity_access, role ");
	sbSql.append(" where "+lsQuery+" account.account_id = deal.account_id ");
	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 = " + userInfo.getAccountId() + " ");
	sbSql.append("and code.code_id = deal.deal_status_id ");
	sbSql.append("and code.code_value not like '%lost%' ");
	sbSql.append("and code.code_value not like '%sold%' ");
	sbSql.append("and code.code_type_id = 'OPPORTUNITY_STATUS' ");
	sbSql.append("group by code.code_value, code.code_id ");
	sbSql.append("order by code.code_id ");
	
    Debug.logVerbose("SQL query = " +  sbSql.toString(), module);

PreparedStatement preparedStatement = null;
ResultSet rs = null;
javachart.servlet.pieApp pieChart = new javachart.servlet.pieApp();	
  try{
    preparedStatement = connection.prepareStatement(sbSql.toString());
    rs = preparedStatement.executeQuery();
	
	String valueName = "";
	int x = 0;
	int y = 100;
	boolean lbStop = false;
	Double  ldTemp = new Double(0.0);
	double  ldSimple = 0;
	//new Double(returnAmount)
		
		
	while ( rs.next() ){
			x++;
			valueName = rs.getString("name");
			ldSimple = rs.getDouble("amount")/1000; 
 			pieChart.accumulateProperty("dataset0yValues", Double.toString(ldSimple));
			pieChart.accumulateProperty("dataset0Labels", valueName.toUpperCase() );
		 	
	}
	    pieChart.setProperty("legendVertical", "true");
		pieChart.setProperty("pieHeight", "0.4");
		pieChart.setProperty("pieWidth", "0.4");
		pieChart.setProperty("yOffset", "30");
		pieChart.setProperty("startDegrees", "90");
		pieChart.setProperty("explodeSlice", "20");
		pieChart.setProperty("explodeSlices", "1");
		pieChart.setProperty("pieLegend", "true");
		pieChart.setProperty("legendOn", "true");
		pieChart.setProperty("3D", "true");
		pieChart.setProperty("xDepth", "50");
		pieChart.setProperty("yDepth", "100");	
				  

		//set a few other properties to make the chart look nice
		pieChart.setProperty("plotAreaColor", "lightGray");
		pieChart.setProperty("dwellUseXValue", "true");
		pieChart.setProperty("dwellYString", "value: XX");
		pieChart.setProperty("mapName", "pieMap");
		pieChart.setProperty("imageType", "j_png");
		pieChart.setProperty("dataset0Colors", "red,blue,green,gray,cyan,black");
		pieChart.setProperty("byteStream", "false");
		pieChart.setProperty("useCache", "false");			    
		pieChart.setProperty("debug", "true");

		//let's use PNG format
		pieChart.setProperty("imageType", "j_png");
		
  } 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);
    }
  }	
  
		  
%>