piplinechart.jsp

国外的一套开源CRM

<%@ include file="/includes/chartheader.jsp" %>
<%@ 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 = "pipelineChart";
%>

function Populate(){
	setTitle("Pipline");
	setAxis("1000's of Dollars");
	<%
	
	StringBuffer sbSql = new StringBuffer("select sum(d.amount) as amount, st.code_value as name, st.code_id as stage_id ");
	sbSql.append("from deal d, ");
	//sbSql.append("deal_stage s, ");
	sbSql.append("code st, ");
	sbSql.append("account a, entity_access ac, role r ");
	//sbSql.append("where s.stage_id = d.stage_id ");
	sbSql.append(" where a.account_id = d.account_id ");
	sbSql.append("and a.account_id = ac.entity_id ");
	sbSql.append("and ac.entity = 'Account' ");
	sbSql.append("and ac.party_entity_type = 'Role' ");
	sbSql.append("and r.role_id = ac.party_id ");
	sbSql.append("and r.role_path like ");
	sbSql.append("'%" + userInfo.getRoleId() + "%' ");
	sbSql.append("and st.code_id = d.deal_status_id ");
	sbSql.append("and st.code_value not like '%lost%' ");
	sbSql.append("and st.code_value not like '%sold%' ");
	sbSql.append("and st.code_type_id = 'OPPORTUNITY_STATUS' ");
	sbSql.append("group by st.code_value, st.code_id ");
	sbSql.append("order by st.code_id ");
	
	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();
	
		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; 
				ldTemp = Double.valueOf(Double.toString(ldSimple));
				y = ldTemp.intValue();
				if (y < 1) {
					y = 1;
				}
				%>
				addChartValue(<%=y%>,"<%=valueName%>", true);
				<% 	
		}
		
	  } 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);
        }
      }	
		  
	%>
		
}
<%@ include file="/includes/chartfooter.jsp" %>