?
Solved

Padding zeros in a table

Posted on 2009-02-12
48
Medium Priority
?
581 Views
Last Modified: 2012-08-13
Hi All,
           
      In the following code I am trying to display the data for last 7 days. but now all channel groups have data for every day. So we are padding zeros, if there is no data for today.
       
      Now the problem is , if you see the channel Group "HDU", in actual there is no data for 02/08/09, so the data for next dates are taking its place. I need some logic that would compare the dates or fill in Zeros when there in no data for a particular channel group via query.
<h2>EDD Metric Table</h2>
<%!public ArrayList getDays() {
 
		ArrayList dateList = new ArrayList();
		DateFormat dateFormat = new SimpleDateFormat("MM/dd/yyyy");
 
		Calendar cal = Calendar.getInstance();
		dateFormat.format(cal.getTime());
 
		for (int i = 0; i <= 6; i++) {
			cal.add(Calendar.DATE, -1);
			dateList.add(dateFormat.format(cal.getTime()));
 
		}
		return dateList;
	}%> <%!public ArrayList padZeros(ArrayList a, int count) {
		for (int i = 0; i < count; i++)
			a.add(new Integer(0));
		return a;
	}%> <%
 	java.util.ArrayList days = getDays();
 	java.util.Collections.reverse(days);
 %>
<h5><b>Results as of <%=df.format(prevDay.getTime())%></b></h5>
<table border="1" cellspacing="1" bgcolor="#FFFFFF" WIDTH=99%>
	<tr>
		<th><b>Channel Group</b></th>
		<th><b> Metric </b></th>
		<%
		for (int i = 0; i < days.size(); i++) {
		%>
		<th><%=days.get(i)%></th>
		<%
		}
		%>
		<th><b>Minimum SLA </b></th>
	</tr>
	<%
		Connection conn = null;
		Class.forName("oracle.jdbc.driver.OracleDriver");
		conn = DriverManager.getConnection(
				"jdbc:oracle:thin:@mlpi148.sfdc.sbc.com:1522:osfd002",
				"vsland", "mt_vsland");
 
		String sql = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
 
		try {
 
			///////// NEW /////////
			// CHANNELGROUP to SLAMIN lookup table
			Hashtable slaMinList = new Hashtable();
			sql = "SELECT METRIC, SLA_MIN FROM VSLAND.APM_WEB_SLA_MIN WHERE APPLICATION='EDD_CORE'";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery(sql);
			while (rs.next()) {
				String key = rs.getString(1);
				float value = rs.getFloat(2);
				slaMinList.put(key, new Float(value));
			}
			rs.close();
			pstmt.close();
			///////// NEW /////////
 
			sql = "SELECT CLIENT_GRP, SUCCESS_RATE, VOLUME, NVL(TO_CHAR(d, 'mm/dd/yyyy'), ' ') EVENT_DATE from  VSLAND.APM_WEB_EDDCORE_METRICPAGE_V, (SELECT TRUNC(SYSDATE - 1) - LEVEL + 1 d FROM DUAL CONNECT BY LEVEL <= 7) WHERE EVENT_DATE(+) = d ORDER BY CLIENT_GRP, d";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery(sql);
			ArrayList success = new ArrayList();
			ArrayList volume = new ArrayList();
			ArrayList hightlight = new ArrayList();
			float min = 0;
			String chggrp = "";
			if (rs.next()) {
				chggrp = rs.getString(1);
			}
			boolean loop = true;
			int rowcolor = 0; /// color code
			while (loop) {
				boolean innerloop = true;
	%>
	<%
	if (rowcolor % 2 == 0) {
	%>
	<tr>
		<%
		} else {
		%>
	
	<tr style='background-color:#EDEDED'>
		<%
		}
		%>
 
		<th rowspan=2><%=chggrp%></th>
		<td><b>Volume</b></td>
		<%
					java.text.DecimalFormat nf = new java.text.DecimalFormat(
					"#0.00");
					java.text.DecimalFormat nv = new java.text.DecimalFormat(
					"##,##,###");
					java.text.DecimalFormat np = new java.text.DecimalFormat(
					"#0.00%");
 
					while (innerloop) {
				float f = rs.getFloat(2);
 
				///////// NEW /////////
				//                               if (f < 87)
				Object ftemp = slaMinList.get(chggrp);
 
				if (null != ftemp) {
					min = ((Float) ftemp).floatValue();
				} else {
					min = 111;
				}
 
				if (f < min && min != 111)
					///////// NEW /////////
					hightlight.add(new Integer(1)); // true
				else
					hightlight.add(new Integer(0)); // false
				success.add(new Float(f));
 
				volume.add(new Integer(rs.getInt(3)));
 
				if (!rs.next()) {
					innerloop = false;
					loop = false;
					break;
				}
				if (!rs.getString(1).equals(chggrp))
					innerloop = false;
				chggrp = rs.getString(1);
					}
 
					success = padZeros(success, 7 - success.size());
					hightlight = padZeros(hightlight, 7 - hightlight.size());
					volume = padZeros(volume, 7 - volume.size());
					for (int v = 0; v < volume.size(); v++) {
 
				out.print("<td>" + nv.format(volume.get(v)) + "</td>");
					}
 
					///////// NEW /////////
					out.print("<td>&nbsp;</td>");
					///////// NEW /////////
		%>
	</tr>
	<%
	if (rowcolor % 2 == 0) { /// color code
	%>
	<tr>
 
		<%
		} else {
		%>
	
	<tr style='background-color:#EDEDED'>
		<%
		}
		%>
		<td><b>Sucess</b></td>
 
		<%
				for (int s = 0; s < success.size(); s++) {
				int h = ((Integer) hightlight.get(s)).intValue();
				if (h == 1)
					///////// NEW /////////
					//                                        out.print("<td style='background-color:#E33740'>"
					out.print("<td style='background-color:#FF0000'>"
					///////// NEW /////////
 
					+ np.format(success.get(s))
					+ "</span></td>");
				else
					out.print("<td>" + np.format(success.get(s))
					+ "</td>");
					}
					if (min != 111) {
				out.print("<td>" + np.format(min) + "</td>");
					} else {
				out.print("<td>NA</td>");
					}
		%>
	</tr>
	<%
				volume.clear();
				success.clear();
				hightlight.clear();
				rowcolor++;
			}
 
			conn.close();
			rs.close();
			pstmt.close();
 
		} catch (Exception e) {
 
			e.printStackTrace();
			conn = null;
 
		}
	%>
 
</table>

Open in new window

0
Comment
Question by:aman0711
  • 27
  • 21
48 Comments
 
LVL 10

Author Comment

by:aman0711
ID: 23623987

HDU doesnt have the data for 02/08/2009

core.JPG
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 23624037
Hmm as I have said in previous posts, in the current code, there is no co-relation between dates and the data when it comes to making the table..

we will have to establish that relation before it can be used.

I am no expert at Oracle or SQL, so not sure if it can be done via query.. i.e. query checks if the value is null and fills it up with zero or something.. may be some oracle expert will chip in for that..
I will try to see from the jsp side as to what can be done.

0
 
LVL 10

Author Comment

by:aman0711
ID: 23624106
Thank you Sir, please help me out.
What my team members decided and told me to do is:
Compare the dates in the Arraylist that we are using for Column headers... Compare those dates with the dates fetched frmo DB. and then for a particular channel group, thr is no date..then pad zeros or null
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 23624129
can you post result of your query when you run it on oracle..
I want to see how the data is arranged when data is not present for a date..
0
 
LVL 10

Author Comment

by:aman0711
ID: 23624219
This is the output I get from running the second query of the code.

part-1.JPG
part-2.JPG
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 23624718
try following code... should work..
<h2>EDD Metric Table</h2>
<%!public ArrayList getDays() {
 
                ArrayList dateList = new ArrayList();
                DateFormat dateFormat = new SimpleDateFormat("MM/dd/yyyy");
 
                Calendar cal = Calendar.getInstance();
                dateFormat.format(cal.getTime());
 
                for (int i = 0; i <= 6; i++) {
                        cal.add(Calendar.DATE, -1);
                        dateList.add(dateFormat.format(cal.getTime()));
 
                }
                return dateList;
        }%> <%!public ArrayList padZeros(ArrayList a, int count) {
                for (int i = 0; i < count; i++)
                        a.add(new Integer(0));
                return a;
        }%> <%
        java.util.ArrayList days = getDays();
        java.util.Collections.reverse(days);
 %>
<h5><b>Results as of <%=df.format(prevDay.getTime())%></b></h5>
<table border="1" cellspacing="1" bgcolor="#FFFFFF" WIDTH=99%>
        <tr>
                <th><b>Channel Group</b></th>
                <th><b> Metric </b></th>
                <%
                for (int i = 0; i < days.size(); i++) {
                %>
                <th><%=days.get(i)%></th>
                <%
                }
                %>
                <th><b>Minimum SLA </b></th>
        </tr>
        <%
                Connection conn = null;
                Class.forName("oracle.jdbc.driver.OracleDriver");
                conn = DriverManager.getConnection(
                                "jdbc:oracle:thin:@mlpi148.sfdc.sbc.com:1522:osfd002",
                                "vsland", "mt_vsland");
 
                String sql = "";
                PreparedStatement pstmt = null;
                ResultSet rs = null;
 
                try {
 
                        ///////// NEW /////////
                        // CHANNELGROUP to SLAMIN lookup table
                        Hashtable slaMinList = new Hashtable();
                        sql = "SELECT METRIC, SLA_MIN FROM VSLAND.APM_WEB_SLA_MIN WHERE APPLICATION='EDD_CORE'";
                        pstmt = conn.prepareStatement(sql);
                        rs = pstmt.executeQuery(sql);
                        while (rs.next()) {
                                String key = rs.getString(1);
                                float value = rs.getFloat(2);
                                slaMinList.put(key, new Float(value));
                        }
                        rs.close();
                        pstmt.close();
                        ///////// NEW /////////
 
                        sql = "SELECT CLIENT_GRP, SUCCESS_RATE, VOLUME, NVL(TO_CHAR(d, 'mm/dd/yyyy'), ' ') EVENT_DATE from  VSLAND.APM_WEB_EDDCORE_METRICPAGE_V, (SELECT TRUNC(SYSDATE - 1) - LEVEL + 1 d FROM DUAL CONNECT BY LEVEL <= 7) WHERE EVENT_DATE(+) = d ORDER BY CLIENT_GRP, d";
                        pstmt = conn.prepareStatement(sql);
                        rs = pstmt.executeQuery(sql);
                        ArrayList success = new ArrayList();
                        ArrayList volume = new ArrayList();
                        ArrayList hightlight = new ArrayList();
                        float min = 0;
                        String chggrp = "";
                        if (rs.next()) {
                                chggrp = rs.getString(1);
                        }
                        boolean loop = true;
                        int rowcolor = 0; /// color code
						int daycount =0; //index for dates.
                        while (loop) {
                                boolean innerloop = true;
        %>
        <%
        if (rowcolor % 2 == 0) {
        %>
        <tr>
                <%
                } else {
                %>
        
        <tr style='background-color:#EDEDED'>
                <%
                }
                %>
 
                <th rowspan=2><%=chggrp%></th>
                <td><b>Volume</b></td>
                <%
                                        java.text.DecimalFormat nf = new java.text.DecimalFormat(
                                        "#0.00");
                                        java.text.DecimalFormat nv = new java.text.DecimalFormat(
                                        "##,##,###");
                                        java.text.DecimalFormat np = new java.text.DecimalFormat(
                                        "#0.00%");
 
                                        while (innerloop) {
									String thisDate = rs.getString("EVENT_DATE");//get the record date								
                                if(!thisDate.equals(days(daycount))
								{//My dates dont match so we must be missing data, insert zeros
								success.add(new Float(0.0));
 
                                volume.add(new Integer(0));
								
								}else{ // else do the normal processing.
								float f = rs.getFloat(2);
 
                                ///////// NEW /////////
                                //                               if (f < 87)
                                Object ftemp = slaMinList.get(chggrp);
 
                                if (null != ftemp) {
                                        min = ((Float) ftemp).floatValue();
                                } else {
                                        min = 111;
                                }
 
                                if (f < min && min != 111)
                                        ///////// NEW /////////
                                        hightlight.add(new Integer(1)); // true
                                else
                                        hightlight.add(new Integer(0)); // false
                                success.add(new Float(f));
 
                                volume.add(new Integer(rs.getInt(3)));
 								}
								daycount++;
                                if (!rs.next()) {
                                        innerloop = false;
                                        loop = false;
                                        break;
                                }
                                if (!rs.getString(1).equals(chggrp))
                                        innerloop = false;
                                chggrp = rs.getString(1);
                                        }
 
                                        success = padZeros(success, 7 - success.size());
                                        hightlight = padZeros(hightlight, 7 - hightlight.size());
                                        volume = padZeros(volume, 7 - volume.size());
                                        for (int v = 0; v < volume.size(); v++) {
 
                                out.print("<td>" + nv.format(volume.get(v)) + "</td>");
                                        }
 
                                        ///////// NEW /////////
                                        out.print("<td> </td>");
                                        ///////// NEW /////////
                %>
        </tr>
        <%
        if (rowcolor % 2 == 0) { /// color code
        %>
        <tr>
 
                <%
                } else {
                %>
        
        <tr style='background-color:#EDEDED'>
                <%
                }
                %>
                <td><b>Sucess</b></td>
 
                <%
                                for (int s = 0; s < success.size(); s++) {
                                int h = ((Integer) hightlight.get(s)).intValue();
                                if (h == 1)
                                        ///////// NEW /////////
                                        //                                        out.print("<td style='background-color:#E33740'>"
                                        out.print("<td style='background-color:#FF0000'>"
                                        ///////// NEW /////////
 
                                        + np.format(success.get(s))
                                        + "</span></td>");
                                else
                                        out.print("<td>" + np.format(success.get(s))
                                        + "</td>");
                                        }
                                        if (min != 111) {
                                out.print("<td>" + np.format(min) + "</td>");
                                        } else {
                                out.print("<td>NA</td>");
                                        }
                %>
        </tr>
        <%
                                volume.clear();
                                success.clear();
                                hightlight.clear();
								daycount=0;
                                rowcolor++;
                        }
 
                        conn.close();
                        rs.close();
                        pstmt.close();
 
                } catch (Exception e) {
 
                        e.printStackTrace();
                        conn = null;
 
                }
        %>
 
</table>

Open in new window

0
 
LVL 10

Author Comment

by:aman0711
ID: 23624939
Sir in the above code block, its giving error on days.. I put in the required " ) " but getting the following:
The method days(int) is undefined for the type _NEW_5F_EDD_5F_CORE_5F_MET
 
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 23625036
my bad

if(!thisDate.equals(days(daycount))

shd be

if(!thisDate.equals(days.get(daycount)))
0
 
LVL 10

Author Comment

by:aman0711
ID: 23625106
thank you Sir,
 the code worked fine but this time a totally different out put.
I have attached the new and old outputs

OLD.JPG
NEW.JPG
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 23625208
I see what its doing.. as soon as it missed one day, I filled zeros in the if section but then I shd not stop the ELSE section of it because I have next day's data there... use below code..



<h2>EDD Metric Table</h2>
<%!public ArrayList getDays() {
 
                ArrayList dateList = new ArrayList();
                DateFormat dateFormat = new SimpleDateFormat("MM/dd/yyyy");
 
                Calendar cal = Calendar.getInstance();
                dateFormat.format(cal.getTime());
 
                for (int i = 0; i <= 6; i++) {
                        cal.add(Calendar.DATE, -1);
                        dateList.add(dateFormat.format(cal.getTime()));
 
                }
                return dateList;
        }%> <%!public ArrayList padZeros(ArrayList a, int count) {
                for (int i = 0; i < count; i++)
                        a.add(new Integer(0));
                return a;
        }%> <%
        java.util.ArrayList days = getDays();
        java.util.Collections.reverse(days);
 %>
<h5><b>Results as of <%=df.format(prevDay.getTime())%></b></h5>
<table border="1" cellspacing="1" bgcolor="#FFFFFF" WIDTH=99%>
        <tr>
                <th><b>Channel Group</b></th>
                <th><b> Metric </b></th>
                <%
                for (int i = 0; i < days.size(); i++) {
                %>
                <th><%=days.get(i)%></th>
                <%
                }
                %>
                <th><b>Minimum SLA </b></th>
        </tr>
        <%
                Connection conn = null;
                Class.forName("oracle.jdbc.driver.OracleDriver");
                conn = DriverManager.getConnection(
                                "jdbc:oracle:thin:@mlpi148.sfdc.sbc.com:1522:osfd002",
                                "vsland", "mt_vsland");
 
                String sql = "";
                PreparedStatement pstmt = null;
                ResultSet rs = null;
 
                try {
 
                        ///////// NEW /////////
                        // CHANNELGROUP to SLAMIN lookup table
                        Hashtable slaMinList = new Hashtable();
                        sql = "SELECT METRIC, SLA_MIN FROM VSLAND.APM_WEB_SLA_MIN WHERE APPLICATION='EDD_CORE'";
                        pstmt = conn.prepareStatement(sql);
                        rs = pstmt.executeQuery(sql);
                        while (rs.next()) {
                                String key = rs.getString(1);
                                float value = rs.getFloat(2);
                                slaMinList.put(key, new Float(value));
                        }
                        rs.close();
                        pstmt.close();
                        ///////// NEW /////////
 
                        sql = "SELECT CLIENT_GRP, SUCCESS_RATE, VOLUME, NVL(TO_CHAR(d, 'mm/dd/yyyy'), ' ') EVENT_DATE from  VSLAND.APM_WEB_EDDCORE_METRICPAGE_V, (SELECT TRUNC(SYSDATE - 1) - LEVEL + 1 d FROM DUAL CONNECT BY LEVEL <= 7) WHERE EVENT_DATE(+) = d ORDER BY CLIENT_GRP, d";
                        pstmt = conn.prepareStatement(sql);
                        rs = pstmt.executeQuery(sql);
                        ArrayList success = new ArrayList();
                        ArrayList volume = new ArrayList();
                        ArrayList hightlight = new ArrayList();
                        float min = 0;
                        String chggrp = "";
                        if (rs.next()) {
                                chggrp = rs.getString(1);
                        }
                        boolean loop = true;
                        int rowcolor = 0; /// color code
						int daycount =0; //index for dates.
                        while (loop) {
                                boolean innerloop = true;
        %>
        <%
        if (rowcolor % 2 == 0) {
        %>
        <tr>
                <%
                } else {
                %>
        
        <tr style='background-color:#EDEDED'>
                <%
                }
                %>
 
                <th rowspan=2><%=chggrp%></th>
                <td><b>Volume</b></td>
                <%
                                        java.text.DecimalFormat nf = new java.text.DecimalFormat(
                                        "#0.00");
                                        java.text.DecimalFormat nv = new java.text.DecimalFormat(
                                        "##,##,###");
                                        java.text.DecimalFormat np = new java.text.DecimalFormat(
                                        "#0.00%");
 
                                        while (innerloop) {
									String thisDate = rs.getString("EVENT_DATE");//get the record date								
                                if(!thisDate.equals(days.get(daycount)))
								{//My dates dont match so we must be missing data, insert zeros
								success.add(new Float(0.0));
 
                                volume.add(new Integer(0));
				//also increase the day by one here
                                  daycount++;
				
								}
								float f = rs.getFloat(2);
 
                                ///////// NEW /////////
                                //                               if (f < 87)
                                Object ftemp = slaMinList.get(chggrp);
 
                                if (null != ftemp) {
                                        min = ((Float) ftemp).floatValue();
                                } else {
                                        min = 111;
                                }
 
                                if (f < min && min != 111)
                                        ///////// NEW /////////
                                        hightlight.add(new Integer(1)); // true
                                else
                                        hightlight.add(new Integer(0)); // false
                                success.add(new Float(f));
 
                                volume.add(new Integer(rs.getInt(3)));
 								}
								daycount++;
                                if (!rs.next()) {
                                        innerloop = false;
                                        loop = false;
                                        break;
                                }
                                if (!rs.getString(1).equals(chggrp))
                                        innerloop = false;
                                chggrp = rs.getString(1);
                                        }
 
                                        success = padZeros(success, 7 - success.size());
                                        hightlight = padZeros(hightlight, 7 - hightlight.size());
                                        volume = padZeros(volume, 7 - volume.size());
                                        for (int v = 0; v < volume.size(); v++) {
 
                                out.print("<td>" + nv.format(volume.get(v)) + "</td>");
                                        }
 
                                        ///////// NEW /////////
                                        out.print("<td> </td>");
                                        ///////// NEW /////////
                %>
        </tr>
        <%
        if (rowcolor % 2 == 0) { /// color code
        %>
        <tr>
 
                <%
                } else {
                %>
        
        <tr style='background-color:#EDEDED'>
                <%
                }
                %>
                <td><b>Sucess</b></td>
 
                <%
                                for (int s = 0; s < success.size(); s++) {
                                int h = ((Integer) hightlight.get(s)).intValue();
                                if (h == 1)
                                        ///////// NEW /////////
                                        //                                        out.print("<td style='background-color:#E33740'>"
                                        out.print("<td style='background-color:#FF0000'>"
                                        ///////// NEW /////////
 
                                        + np.format(success.get(s))
                                        + "</span></td>");
                                else
                                        out.print("<td>" + np.format(success.get(s))
                                        + "</td>");
                                        }
                                        if (min != 111) {
                                out.print("<td>" + np.format(min) + "</td>");
                                        } else {
                                out.print("<td>NA</td>");
                                        }
                %>
        </tr>
        <%
                                volume.clear();
                                success.clear();
                                hightlight.clear();
								daycount=0;
                                rowcolor++;
                        }
 
                        conn.close();
                        rs.close();
                        pstmt.close();
 
                } catch (Exception e) {
 
                        e.printStackTrace();
                        conn = null;
 
                }
        %>
 
</table>

Open in new window

0
 
LVL 10

Author Comment

by:aman0711
ID: 23625315
Sorry sir its asknig for a finally block after line 205 in the above code. We are missin brackets somewhere?
I am sorry, I should fix it but I have to show it real soon
 
0
 
LVL 19

Accepted Solution

by:
Kuldeepchaturvedi earned 2000 total points
ID: 23625353
Use code below..

I removed the opening of Else statement & forgot to remove ending..
<h2>EDD Metric Table</h2>
<%!public ArrayList getDays() {
 
                ArrayList dateList = new ArrayList();
                DateFormat dateFormat = new SimpleDateFormat("MM/dd/yyyy");
 
                Calendar cal = Calendar.getInstance();
                dateFormat.format(cal.getTime());
 
                for (int i = 0; i <= 6; i++) {
                        cal.add(Calendar.DATE, -1);
                        dateList.add(dateFormat.format(cal.getTime()));
 
                }
                return dateList;
        }%> <%!public ArrayList padZeros(ArrayList a, int count) {
                for (int i = 0; i < count; i++)
                        a.add(new Integer(0));
                return a;
        }%> <%
        java.util.ArrayList days = getDays();
        java.util.Collections.reverse(days);
 %>
<h5><b>Results as of <%=df.format(prevDay.getTime())%></b></h5>
<table border="1" cellspacing="1" bgcolor="#FFFFFF" WIDTH=99%>
        <tr>
                <th><b>Channel Group</b></th>
                <th><b> Metric </b></th>
                <%
                for (int i = 0; i < days.size(); i++) {
                %>
                <th><%=days.get(i)%></th>
                <%
                }
                %>
                <th><b>Minimum SLA </b></th>
        </tr>
        <%
                Connection conn = null;
                Class.forName("oracle.jdbc.driver.OracleDriver");
                conn = DriverManager.getConnection(
                                "jdbc:oracle:thin:@mlpi148.sfdc.sbc.com:1522:osfd002",
                                "vsland", "mt_vsland");
 
                String sql = "";
                PreparedStatement pstmt = null;
                ResultSet rs = null;
 
                try {
 
                        ///////// NEW /////////
                        // CHANNELGROUP to SLAMIN lookup table
                        Hashtable slaMinList = new Hashtable();
                        sql = "SELECT METRIC, SLA_MIN FROM VSLAND.APM_WEB_SLA_MIN WHERE APPLICATION='EDD_CORE'";
                        pstmt = conn.prepareStatement(sql);
                        rs = pstmt.executeQuery(sql);
                        while (rs.next()) {
                                String key = rs.getString(1);
                                float value = rs.getFloat(2);
                                slaMinList.put(key, new Float(value));
                        }
                        rs.close();
                        pstmt.close();
                        ///////// NEW /////////
 
                        sql = "SELECT CLIENT_GRP, SUCCESS_RATE, VOLUME, NVL(TO_CHAR(d, 'mm/dd/yyyy'), ' ') EVENT_DATE from  VSLAND.APM_WEB_EDDCORE_METRICPAGE_V, (SELECT TRUNC(SYSDATE - 1) - LEVEL + 1 d FROM DUAL CONNECT BY LEVEL <= 7) WHERE EVENT_DATE(+) = d ORDER BY CLIENT_GRP, d";
                        pstmt = conn.prepareStatement(sql);
                        rs = pstmt.executeQuery(sql);
                        ArrayList success = new ArrayList();
                        ArrayList volume = new ArrayList();
                        ArrayList hightlight = new ArrayList();
                        float min = 0;
                        String chggrp = "";
                        if (rs.next()) {
                                chggrp = rs.getString(1);
                        }
                        boolean loop = true;
                        int rowcolor = 0; /// color code
						int daycount =0; //index for dates.
                        while (loop) {
                                boolean innerloop = true;
        %>
        <%
        if (rowcolor % 2 == 0) {
        %>
        <tr>
                <%
                } else {
                %>
        
        <tr style='background-color:#EDEDED'>
                <%
                }
                %>
 
                <th rowspan=2><%=chggrp%></th>
                <td><b>Volume</b></td>
                <%
                                        java.text.DecimalFormat nf = new java.text.DecimalFormat(
                                        "#0.00");
                                        java.text.DecimalFormat nv = new java.text.DecimalFormat(
                                        "##,##,###");
                                        java.text.DecimalFormat np = new java.text.DecimalFormat(
                                        "#0.00%");
 
                                        while (innerloop) {
									String thisDate = rs.getString("EVENT_DATE");//get the record date								
                                if(!thisDate.equals(days.get(daycount)))
								{//My dates dont match so we must be missing data, insert zeros
								success.add(new Float(0.0));
 
                                volume.add(new Integer(0));
				//also increase the day by one here
                                  daycount++;
				
								}
								float f = rs.getFloat(2);
 
                                ///////// NEW /////////
                                //                               if (f < 87)
                                Object ftemp = slaMinList.get(chggrp);
 
                                if (null != ftemp) {
                                        min = ((Float) ftemp).floatValue();
                                } else {
                                        min = 111;
                                }
 
                                if (f < min && min != 111)
                                        ///////// NEW /////////
                                        hightlight.add(new Integer(1)); // true
                                else
                                        hightlight.add(new Integer(0)); // false
                                success.add(new Float(f));
 
                                volume.add(new Integer(rs.getInt(3)));
 												daycount++;
                                if (!rs.next()) {
                                        innerloop = false;
                                        loop = false;
                                        break;
                                }
                                if (!rs.getString(1).equals(chggrp))
                                        innerloop = false;
                                chggrp = rs.getString(1);
                                        }
 
                                        success = padZeros(success, 7 - success.size());
                                        hightlight = padZeros(hightlight, 7 - hightlight.size());
                                        volume = padZeros(volume, 7 - volume.size());
                                        for (int v = 0; v < volume.size(); v++) {
 
                                out.print("<td>" + nv.format(volume.get(v)) + "</td>");
                                        }
 
                                        ///////// NEW /////////
                                        out.print("<td> </td>");
                                        ///////// NEW /////////
                %>
        </tr>
        <%
        if (rowcolor % 2 == 0) { /// color code
        %>
        <tr>
 
                <%
                } else {
                %>
        
        <tr style='background-color:#EDEDED'>
                <%
                }
                %>
                <td><b>Sucess</b></td>
 
                <%
                                for (int s = 0; s < success.size(); s++) {
                                int h = ((Integer) hightlight.get(s)).intValue();
                                if (h == 1)
                                        ///////// NEW /////////
                                        //                                        out.print("<td style='background-color:#E33740'>"
                                        out.print("<td style='background-color:#FF0000'>"
                                        ///////// NEW /////////
 
                                        + np.format(success.get(s))
                                        + "</span></td>");
                                else
                                        out.print("<td>" + np.format(success.get(s))
                                        + "</td>");
                                        }
                                        if (min != 111) {
                                out.print("<td>" + np.format(min) + "</td>");
                                        } else {
                                out.print("<td>NA</td>");
                                        }
                %>
        </tr>
        <%
                                volume.clear();
                                success.clear();
                                hightlight.clear();
								daycount=0;
                                rowcolor++;
                        }
 
                        conn.close();
                        rs.close();
                        pstmt.close();
 
                } catch (Exception e) {
 
                        e.printStackTrace();
                        conn = null;
 
                }
        %>
 
</table>

Open in new window

0
 
LVL 10

Author Comment

by:aman0711
ID: 23625389
Thank you so much Sir.
Could you please tell me the logic we used here?
Now I am left with just one last problem.. thats quite simple I guess... u will fix it in a muinute
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 23625452
If you read the comments, you shd be able to figure out the logic used..
0
 
LVL 10

Author Comment

by:aman0711
ID: 23625497
Yup, please correct me if I am wrong somewhere.
This time we made use of dayCount variable. We fetched the EVENT_DATE from the Table.. compared it, and wherever there was no Data, we filled in Success and Volume as 0.0 and 0 respectively.Am I correct Sir?
Sir one last problem.
In the following code. I dont want to display the channel groups which have Minimum SLA as NA. The metrics is first query as those 5 metrics which have values.

<h2>eBill Metric Table</h2>
<%!public ArrayList getDays() {
 
		ArrayList dateList = new ArrayList();
		DateFormat dateFormat = new SimpleDateFormat("MM/dd/yyyy");
 
		Calendar cal = Calendar.getInstance();
		dateFormat.format(cal.getTime());
 
		for (int i = 0; i <= 6; i++) {
			cal.add(Calendar.DATE, -1);
			dateList.add(dateFormat.format(cal.getTime()));
 
		}
		return dateList;
	}%> <%!public ArrayList padZeros(ArrayList a, int count) {
		for (int i = 0; i < count; i++)
			a.add(new Integer(0));
		return a;
	}%> <%
 	java.util.ArrayList days = getDays();
 	java.util.Collections.reverse(days);
 %>
<h5><b>Results as of <%=df.format(prevDay.getTime())%></b></h5>
<table border="1" cellspacing="1" bgcolor="#FFFFFF" WIDTH=99%>
	<tr>
		<th><b>Events</b></th>
		<th><b> Metric </b></th>
		<%
		for (int i = 0; i < days.size(); i++) {
		%>
		<th><%=days.get(i)%></th>
		<%
		}
		%>
		<th><b>Minimum SLA </b></th>
	</tr>
	<%
		Connection conn = null;
		Class.forName("oracle.jdbc.driver.OracleDriver");
		conn = DriverManager.getConnection(
				"jdbc:oracle:thin:@mlpi148.sfdc.sbc.com:1522:osfd002",
				"vsland", "mt_vsland");
 
		String sql = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
 
		try {
 
			///////// NEW /////////
			// CHANNELGROUP to SLAMIN lookup table
			Hashtable slaMinList = new Hashtable();
			sql = "SELECT METRIC, SLA_MIN FROM VSLAND.APM_WEB_SLA_MIN WHERE APPLICATION='eBill'";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery(sql);
			while (rs.next()) {
				String key = rs.getString(1);
				float value = rs.getFloat(2);
				slaMinList.put(key, new Float(value));
			}
			rs.close();
			pstmt.close();
			///////// NEW /////////
 
			sql = "SELECT TRANSACTIONS, SUCCESS_RATE, SUCCESS_TRX_COUNTS, NVL(TO_CHAR(d, 'mm/dd/yyyy'), ' ') TRX_DATE from  VSLAND.APM_WEB_EBILL_METRICPAGE_V, (SELECT TRUNC(SYSDATE - 1) - LEVEL + 1 d FROM DUAL CONNECT BY LEVEL <= 7) WHERE  TRX_DATE(+) = d ORDER BY TRANSACTIONS, d";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery(sql);
			ArrayList success = new ArrayList();
			ArrayList volume = new ArrayList();
			ArrayList hightlight = new ArrayList();
			float min = 0;
			String chggrp = "";
			if (rs.next()) {
				chggrp = rs.getString(1);
			}
			boolean loop = true;
			int rowcolor = 0; /// color code
			while (loop) {
				boolean innerloop = true;
	%>
	<%
	if (rowcolor % 2 == 0) {
	%>
	<tr>
		<%
		} else {
		%>
	
	<tr style='background-color:#EDEDED'>
		<%
		}
		%>
 
		<th rowspan=2><%=chggrp%></th>
		<td><b>Volume</b></td>
		<%
					java.text.DecimalFormat nf = new java.text.DecimalFormat(
					"##.00");
					java.text.DecimalFormat nv = new java.text.DecimalFormat(
					"##,##,###");
					java.text.DecimalFormat np = new java.text.DecimalFormat(
					"#0.00%");
 
					while (innerloop) {
				float f = rs.getFloat(2);
 
				///////// NEW /////////
				//                               if (f < 87)
				Object ftemp = slaMinList.get(chggrp);
 
				if (null != ftemp) {
					min = ((Float) ftemp).floatValue();
				} else {
					min = 111;
				}
 
				if (f < min && min != 111)
					///////// NEW /////////
					hightlight.add(new Integer(1)); // true
				else
					hightlight.add(new Integer(0)); // false
				success.add(new Float(f));
 
				volume.add(new Integer(rs.getInt(3)));
 
				if (!rs.next()) {
					innerloop = false;
					loop = false;
					break;
				}
				if (!rs.getString(1).equals(chggrp))
					innerloop = false;
				chggrp = rs.getString(1);
					}
 
					success = padZeros(success, 7 - success.size());
					hightlight = padZeros(hightlight, 7 - hightlight.size());
					volume = padZeros(volume, 7 - volume.size());
					for (int v = 0; v < volume.size(); v++) {
 
				out.print("<td>" + nv.format(volume.get(v)) + "</td>");
					}
 
					///////// NEW /////////
					out.print("<td>&nbsp;</td>");
					///////// NEW /////////
		%>
	</tr>
	<%
	if (rowcolor % 2 == 0) { /// color code
	%>
	<tr>
 
		<%
		} else {
		%>
	
	<tr style='background-color:#EDEDED'>
		<%
		}
		%>
		<td><b>Sucess</b></td>
 
		<%
				for (int s = 0; s < success.size(); s++) {
				int h = ((Integer) hightlight.get(s)).intValue();
				if (h == 1)
					///////// NEW /////////
					//                                        out.print("<td style='background-color:#E33740'>"
					out.print("<td style='background-color:#FF0000'>"
					///////// NEW /////////
 
					+ np.format(success.get(s))
					+ "</span></td>");
				else
					out.print("<td>" + np.format(success.get(s))
					+ "</td>");
					}
					if (min != 111) {
				out.print("<td>" + np.format(min) + "</td>");
					} else {
				out.print("<td>NA</td>");
					}
		%>
	</tr>
	<%
				volume.clear();
				success.clear();
				hightlight.clear();
				rowcolor++;
			}
 
			conn.close();
			rs.close();
			pstmt.close();
 
		} catch (Exception e) {
 
			e.printStackTrace();
			conn = null;
 
		}
	%>
 
</table>

Open in new window

EB.JPG
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 23625566
if you want to remove the whole channel group row for it.. then you need to check the MIN SLA before we get to the loops..

if its NA.. run rs.next() till we reach the next group.. repeat process..

I will not answer that question with code within this question
0
 
LVL 10

Author Comment

by:aman0711
ID: 23625592
Sir, Can I post it in a new question?
0
 
LVL 10

Author Closing Comment

by:aman0711
ID: 31546157
Thank you so much Sir.
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 23625715
Yes you should post it as a separate question because its not continuation of the same problem..

its the rule of EE
0
 
LVL 10

Author Comment

by:aman0711
ID: 23625741
0
 
LVL 10

Author Comment

by:aman0711
ID: 23634256
Hello Sir,
               Our code worked fine for HDU but there are some applications where... its just filling zeros.
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 23635242
Can you explain it a little better?

it is working for one set of data but not for other?
We will have to look at the data and the output screen to see whats going on.
0
 
LVL 10

Author Comment

by:aman0711
ID: 23635464
I will post the output of DB and jsp snap shot.
0
 
LVL 10

Author Comment

by:aman0711
ID: 23635780
Sir, if you see in this snap shot. HDU's data is perfect...
But if you move little down, at NBI, you can see the data mismatch.. between the DB output and snap shot

Output.JPG
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 23638020
Okay I looked at the data and figured that our code was assuming that at any given time there will be ONLY one missing date (hence the IF Condition and not a loop to match the dates..).. see the following code below, that shd help.


<h2>EDD Metric Table</h2>
<%!public ArrayList getDays() {
 
                ArrayList dateList = new ArrayList();
                DateFormat dateFormat = new SimpleDateFormat("MM/dd/yyyy");
 
                Calendar cal = Calendar.getInstance();
                dateFormat.format(cal.getTime());
 
                for (int i = 0; i <= 6; i++) {
                        cal.add(Calendar.DATE, -1);
                        dateList.add(dateFormat.format(cal.getTime()));
 
                }
                return dateList;
        }%> <%!public ArrayList padZeros(ArrayList a, int count) {
                for (int i = 0; i < count; i++)
                        a.add(new Integer(0));
                return a;
        }%> <%
        java.util.ArrayList days = getDays();
        java.util.Collections.reverse(days);
 %>
<h5><b>Results as of <%=df.format(prevDay.getTime())%></b></h5>
<table border="1" cellspacing="1" bgcolor="#FFFFFF" WIDTH=99%>
        <tr>
                <th><b>Channel Group</b></th>
                <th><b> Metric </b></th>
                <%
                for (int i = 0; i < days.size(); i++) {
                %>
                <th><%=days.get(i)%></th>
                <%
                }
                %>
                <th><b>Minimum SLA </b></th>
        </tr>
        <%
                Connection conn = null;
                Class.forName("oracle.jdbc.driver.OracleDriver");
                conn = DriverManager.getConnection(
                                "jdbc:oracle:thin:@mlpi148.sfdc.sbc.com:1522:osfd002",
                                "vsland", "mt_vsland");
 
                String sql = "";
                PreparedStatement pstmt = null;
                ResultSet rs = null;
 
                try {
 
                        ///////// NEW /////////
                        // CHANNELGROUP to SLAMIN lookup table
                        Hashtable slaMinList = new Hashtable();
                        sql = "SELECT METRIC, SLA_MIN FROM VSLAND.APM_WEB_SLA_MIN WHERE APPLICATION='EDD_CORE'";
                        pstmt = conn.prepareStatement(sql);
                        rs = pstmt.executeQuery(sql);
                        while (rs.next()) {
                                String key = rs.getString(1);
                                float value = rs.getFloat(2);
                                slaMinList.put(key, new Float(value));
                        }
                        rs.close();
                        pstmt.close();
                        ///////// NEW /////////
 
                        sql = "SELECT CLIENT_GRP, SUCCESS_RATE, VOLUME, NVL(TO_CHAR(d, 'mm/dd/yyyy'), ' ') EVENT_DATE from  VSLAND.APM_WEB_EDDCORE_METRICPAGE_V, (SELECT TRUNC(SYSDATE - 1) - LEVEL + 1 d FROM DUAL CONNECT BY LEVEL <= 7) WHERE EVENT_DATE(+) = d ORDER BY CLIENT_GRP, d";
                        pstmt = conn.prepareStatement(sql);
                        rs = pstmt.executeQuery(sql);
                        ArrayList success = new ArrayList();
                        ArrayList volume = new ArrayList();
                        ArrayList hightlight = new ArrayList();
                        float min = 0;
                        String chggrp = "";
                        if (rs.next()) {
                                chggrp = rs.getString(1);
                        }
                        boolean loop = true;
                        int rowcolor = 0; /// color code
						int daycount =0; //index for dates.
                        while (loop) {
                                boolean innerloop = true;
        %>
        <%
        if (rowcolor % 2 == 0) {
        %>
        <tr>
                <%
                } else {
                %>
        
        <tr style='background-color:#EDEDED'>
                <%
                }
                %>
 
                <th rowspan=2><%=chggrp%></th>
                <td><b>Volume</b></td>
                <%
                                        java.text.DecimalFormat nf = new java.text.DecimalFormat(
                                        "#0.00");
                                        java.text.DecimalFormat nv = new java.text.DecimalFormat(
                                        "##,##,###");
                                        java.text.DecimalFormat np = new java.text.DecimalFormat(
                                        "#0.00%");
 
                                        while (innerloop) {
									String thisDate = rs.getString("EVENT_DATE");//get the record date								
                                while(!thisDate.equals(days.get(daycount)))
								{//My dates dont match so we must be missing data, insert zeros
								success.add(new Float(0.0));
 
                                volume.add(new Integer(0));
				//also increase the day by one here
                                  daycount++;
				
								}
								float f = rs.getFloat(2);
 
                                ///////// NEW /////////
                                //                               if (f < 87)
                                Object ftemp = slaMinList.get(chggrp);
 
                                if (null != ftemp) {
                                        min = ((Float) ftemp).floatValue();
                                } else {
                                        min = 111;
                                }
 
                                if (f < min && min != 111)
                                        ///////// NEW /////////
                                        hightlight.add(new Integer(1)); // true
                                else
                                        hightlight.add(new Integer(0)); // false
                                success.add(new Float(f));
 
                                volume.add(new Integer(rs.getInt(3)));
 												daycount++;
                                if (!rs.next()) {
                                        innerloop = false;
                                        loop = false;
                                        break;
                                }
                                if (!rs.getString(1).equals(chggrp))
                                        innerloop = false;
                                chggrp = rs.getString(1);
                                        }
 
                                        success = padZeros(success, 7 - success.size());
                                        hightlight = padZeros(hightlight, 7 - hightlight.size());
                                        volume = padZeros(volume, 7 - volume.size());
                                        for (int v = 0; v < volume.size(); v++) {
 
                                out.print("<td>" + nv.format(volume.get(v)) + "</td>");
                                        }
 
                                        ///////// NEW /////////
                                        out.print("<td> </td>");
                                        ///////// NEW /////////
                %>
        </tr>
        <%
        if (rowcolor % 2 == 0) { /// color code
        %>
        <tr>
 
                <%
                } else {
                %>
        
        <tr style='background-color:#EDEDED'>
                <%
                }
                %>
                <td><b>Sucess</b></td>
 
                <%
                                for (int s = 0; s < success.size(); s++) {
                                int h = ((Integer) hightlight.get(s)).intValue();
                                if (h == 1)
                                        ///////// NEW /////////
                                        //                                        out.print("<td style='background-color:#E33740'>"
                                        out.print("<td style='background-color:#FF0000'>"
                                        ///////// NEW /////////
 
                                        + np.format(success.get(s))
                                        + "</span></td>");
                                else
                                        out.print("<td>" + np.format(success.get(s))
                                        + "</td>");
                                        }
                                        if (min != 111) {
                                out.print("<td>" + np.format(min) + "</td>");
                                        } else {
                                out.print("<td>NA</td>");
                                        }
                %>
        </tr>
        <%
                                volume.clear();
                                success.clear();
                                hightlight.clear();
								daycount=0;
                                rowcolor++;
                        }
 
                        conn.close();
                        rs.close();
                        pstmt.close();
 
                } catch (Exception e) {
 
                        e.printStackTrace();
                        conn = null;
 
                }
        %>
 
</table>

Open in new window

0
 
LVL 10

Author Comment

by:aman0711
ID: 23638038
Wow... perfect :)

One side tracked question.

In our jsp pages, thr is lot of code that is common to all the pages. Like the Array lists for date and stuff.
Si it possible for us to move it to a common class... and all the jsp's should share it.
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 23638058
is not possible.. its a MUST..! I think I said that before also on some question..

jsp is not a place to have placed methods in it..

ideally speaking, your database code, and method ( padding and days). should be in their own classes /beans ( one for dbase, another one for these methods)
0
 
LVL 10

Author Comment

by:aman0711
ID: 23652123
Sir, the Data is coming up perfectly now .. but the problem I am facing is:
whenver there is no data, we were filling 0.0 and 0. Now we have to keep the volume as 0 only but Success as 100.00% on the page. So I simple replaced 0.0 with 1.0. but didnt see any change on the pages.
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 23652185
you need to replace 0.0 with 100.00
@
success.add(new Float(0.0));

success.add(new Float(100.0));
that shd do it
0
 
LVL 10

Author Comment

by:aman0711
ID: 23652261
That is what I did Sir. I put in 1.0 coz later on we are formatting it with DateFormat.
If you see in the snap shot.. it filled in 100.00% for first entry of NBI.. but not after that..
and in RET_MAIL.. the last entry is still 0.00%
 

edd.JPG
0
 
LVL 10

Author Comment

by:aman0711
ID: 23652390
..
<%!public ArrayList getDays() {
 
                ArrayList dateList = new ArrayList();
                DateFormat dateFormat = new SimpleDateFormat("MM/dd/yyyy");
 
                Calendar cal = Calendar.getInstance();
                dateFormat.format(cal.getTime());
 
                for (int i = 0; i <= 6; i++) {
                        cal.add(Calendar.DATE, -1);
                        dateList.add(dateFormat.format(cal.getTime()));
 
                }
                return dateList;
        }%> <%!public ArrayList padZeros(ArrayList a, int count) {
                for (int i = 0; i < count; i++)
                        a.add(new Integer(0));
                return a;
        }%> <%
        java.util.ArrayList days = getDays();
        java.util.Collections.reverse(days);
 %>
<h5><b>Results as of <%=df.format(prevDay.getTime())%></b></h5>
<table border="1" cellspacing="1" bgcolor="#FFFFFF" WIDTH=99%>
        <tr>
                <th><b>Channel Group</b></th>
                <th><b> Metric </b></th>
                <%
                for (int i = 0; i < days.size(); i++) {
                %>
                <th><%=days.get(i)%></th>
                <%
                }
                %>
                <th><b>Minimum SLA </b></th>
        </tr>
        <%
                Connection conn = null;
                conn = db.getConnection();
 
                String sql = "";
                PreparedStatement pstmt = null;
                ResultSet rs = null;
 
                try {
 
                        ///////// NEW /////////
                        // CHANNELGROUP to SLAMIN lookup table
                        Hashtable slaMinList = new Hashtable();
                        sql = "SELECT METRIC, SLA_MIN FROM VSLAND.APM_WEB_SLA_MIN WHERE APPLICATION='EDD_CORE'";
                        pstmt = conn.prepareStatement(sql);
                        rs = pstmt.executeQuery(sql);
                        while (rs.next()) {
                                String key = rs.getString(1);
                                float value = rs.getFloat(2);
                                slaMinList.put(key, new Float(value));
                        }
                        rs.close();
                        pstmt.close();
                        ///////// NEW /////////
 
                        sql = "SELECT CLIENT_GRP, SUCCESS_RATE, VOLUME, NVL(TO_CHAR(d, 'mm/dd/yyyy'), ' ') EVENT_DATE from  VSLAND.APM_WEB_EDDCORE_METRICPAGE_V, (SELECT TRUNC(SYSDATE - 1) - LEVEL + 1 d FROM DUAL CONNECT BY LEVEL <= 7) WHERE EVENT_DATE(+) = d ORDER BY CLIENT_GRP, d";
                        pstmt = conn.prepareStatement(sql);
                        rs = pstmt.executeQuery(sql);
                        ArrayList success = new ArrayList();
                        ArrayList volume = new ArrayList();
                        ArrayList hightlight = new ArrayList();
                        float min = 0;
                        String chggrp = "";
                        if (rs.next()) {
                                chggrp = rs.getString(1);
                        }
                        boolean loop = true;
                        int rowcolor = 0; /// color code
						int daycount =0; //index for dates.
                        while (loop) {
                                boolean innerloop = true;
        %>
        <%
        if (rowcolor % 2 == 0) {
        %>
        <tr>
                <%
                } else {
                %>
        
        <tr style='background-color:#EDEDED'>
                <%
                }
                %>
 
                <th rowspan=2><%=chggrp%></th>
                <td><b>Volume</b></td>
                <%
                                        java.text.DecimalFormat nf = new java.text.DecimalFormat(
                                        "#0.00");
                                        java.text.DecimalFormat nv = new java.text.DecimalFormat(
                                        "##,##,###");
                                        java.text.DecimalFormat np = new java.text.DecimalFormat(
                                        "#0.00%");
      
                                        while (innerloop) {
									String thisDate = rs.getString("EVENT_DATE");//get the record date								
                                while(!thisDate.equals(days.get(daycount)))
								{      //My dates dont match so we must be missing data, insert zeros
								success.add(new Float(1.0));
 
                                volume.add(new Integer(0));
			                                           	//also increase the day by one here
                                  daycount++;
                                  
				
								}
								float f = rs.getFloat(2);
 
                                ///////// NEW /////////
                                //                               if (f < 87)
                                Object ftemp = slaMinList.get(chggrp);
 
                                if (null != ftemp) {
                                        min = ((Float) ftemp).floatValue();
                                } else {
                                        min = 111;
                                }
 
                                if (f < min && min != 111)
                                        ///////// NEW /////////
                                        hightlight.add(new Integer(1)); // true
                                else
                                        hightlight.add(new Integer(0)); // false
                                success.add(new Float(f));
 
                                volume.add(new Integer(rs.getInt(3)));
 												daycount++;
                                if (!rs.next()) {
                                        innerloop = false;
                                        loop = false;
                                        break;
                                }
                                if (!rs.getString(1).equals(chggrp))
                                        innerloop = false;
                                chggrp = rs.getString(1);
                                        }
 
                                        success = padZeros(success, 7 - success.size());
                                        hightlight = padZeros(hightlight, 7 - hightlight.size());
                                        volume = padZeros(volume, 7 - volume.size());
                                        for (int v = 0; v < volume.size(); v++) {
 
                                out.print("<td>" + nv.format(volume.get(v)) + "</td>");
                                        }
 
                                        ///////// NEW /////////
                                        out.print("<td>NA</td>");
                                        ///////// NEW /////////
                %>
        </tr>
        <%
        if (rowcolor % 2 == 0) { /// color code
        %>
        <tr>
 
                <%
                } else {
                %>
        
        <tr style='background-color:#EDEDED'>
                <%
                }
                %>
                <td><b>Sucess</b></td>
 
                <%
                                for (int s = 0; s < success.size(); s++) {
                                int h = ((Integer) hightlight.get(s)).intValue();
                                if (h == 1)
                                        ///////// NEW /////////
                                        //                                        out.print("<td style='background-color:#E33740'>"
                                        out.print("<td style='background-color:#FF0000'>"
                                        ///////// NEW /////////
 
                                        + np.format(success.get(s))
                                        + "</span></td>");
                                else
                                        out.print("<td>" + np.format(success.get(s))
                                        + "</td>");
                                        }
                                        if (min != 111) {
                                out.print("<td>" + np.format(min) + "</td>");
                                        } else {
                                out.print("<td>NA</td>");
                                        }
                %>
        </tr>
        <%
                                volume.clear();
                                success.clear();
                                hightlight.clear();
								daycount=0;
                                rowcolor++;
                        }
 
                        conn.close();
                        rs.close();
                        pstmt.close();
 
                } catch (Exception e) {
 
                        e.printStackTrace();
                        conn = null;
 
                }
        %>
 
</table>

Open in new window

0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 23652546
interesting..! that shd not be happening...
because when dates dont match, it shd simply be filling 1.. not sure from where its getting the zeros..!

I will try to look at it closely when I find time..
0
 
LVL 10

Author Comment

by:aman0711
ID: 23652555
Thanks Sir.
Its just filling the values for first column. after that its breaking.
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 23652579
Oops..! I see whats happening..!
Unforutnately, not much I can do in this logic right away... I need change the function PadZero's to make it work.. no other way ( atleast not without hacking the code)..

try the code below, it should fix the code, and then you try to explain what was wrong with original code..

Note: the below logic have absolutely no connection with the problem..


<%!public ArrayList getDays() {
 
                ArrayList dateList = new ArrayList();
                DateFormat dateFormat = new SimpleDateFormat("MM/dd/yyyy");
 
                Calendar cal = Calendar.getInstance();
                dateFormat.format(cal.getTime());
 
                for (int i = 0; i <= 6; i++) {
                        cal.add(Calendar.DATE, -1);
                        dateList.add(dateFormat.format(cal.getTime()));
 
                }
                return dateList;
        }%> <%!public ArrayList padZeros(ArrayList a, int count, int value) {
                for (int i = 0; i < count; i++)
                        a.add(new Integer(value));
                return a;
        }%> <%
        java.util.ArrayList days = getDays();
        java.util.Collections.reverse(days);
 %>
<h5><b>Results as of <%=df.format(prevDay.getTime())%></b></h5>
<table border="1" cellspacing="1" bgcolor="#FFFFFF" WIDTH=99%>
        <tr>
                <th><b>Channel Group</b></th>
                <th><b> Metric </b></th>
                <%
                for (int i = 0; i < days.size(); i++) {
                %>
                <th><%=days.get(i)%></th>
                <%
                }
                %>
                <th><b>Minimum SLA </b></th>
        </tr>
        <%
                Connection conn = null;
                conn = db.getConnection();
 
                String sql = "";
                PreparedStatement pstmt = null;
                ResultSet rs = null;
 
                try {
 
                        ///////// NEW /////////
                        // CHANNELGROUP to SLAMIN lookup table
                        Hashtable slaMinList = new Hashtable();
                        sql = "SELECT METRIC, SLA_MIN FROM VSLAND.APM_WEB_SLA_MIN WHERE APPLICATION='EDD_CORE'";
                        pstmt = conn.prepareStatement(sql);
                        rs = pstmt.executeQuery(sql);
                        while (rs.next()) {
                                String key = rs.getString(1);
                                float value = rs.getFloat(2);
                                slaMinList.put(key, new Float(value));
                        }
                        rs.close();
                        pstmt.close();
                        ///////// NEW /////////
 
                        sql = "SELECT CLIENT_GRP, SUCCESS_RATE, VOLUME, NVL(TO_CHAR(d, 'mm/dd/yyyy'), ' ') EVENT_DATE from  VSLAND.APM_WEB_EDDCORE_METRICPAGE_V, (SELECT TRUNC(SYSDATE - 1) - LEVEL + 1 d FROM DUAL CONNECT BY LEVEL <= 7) WHERE EVENT_DATE(+) = d ORDER BY CLIENT_GRP, d";
                        pstmt = conn.prepareStatement(sql);
                        rs = pstmt.executeQuery(sql);
                        ArrayList success = new ArrayList();
                        ArrayList volume = new ArrayList();
                        ArrayList hightlight = new ArrayList();
                        float min = 0;
                        String chggrp = "";
                        if (rs.next()) {
                                chggrp = rs.getString(1);
                        }
                        boolean loop = true;
                        int rowcolor = 0; /// color code
						int daycount =0; //index for dates.
                        while (loop) {
                                boolean innerloop = true;
        %>
        <%
        if (rowcolor % 2 == 0) {
        %>
        <tr>
                <%
                } else {
                %>
        
        <tr style='background-color:#EDEDED'>
                <%
                }
                %>
 
                <th rowspan=2><%=chggrp%></th>
                <td><b>Volume</b></td>
                <%
                                        java.text.DecimalFormat nf = new java.text.DecimalFormat(
                                        "#0.00");
                                        java.text.DecimalFormat nv = new java.text.DecimalFormat(
                                        "##,##,###");
                                        java.text.DecimalFormat np = new java.text.DecimalFormat(
                                        "#0.00%");
      
                                        while (innerloop) {
									String thisDate = rs.getString("EVENT_DATE");//get the record date								
                                while(!thisDate.equals(days.get(daycount)))
								{      //My dates dont match so we must be missing data, insert zeros
								success.add(new Float(1.0));
 
                                volume.add(new Integer(0));
			                                           	//also increase the day by one here
                                  daycount++;
                                  
				
								}
								float f = rs.getFloat(2);
 
                                ///////// NEW /////////
                                //                               if (f < 87)
                                Object ftemp = slaMinList.get(chggrp);
 
                                if (null != ftemp) {
                                        min = ((Float) ftemp).floatValue();
                                } else {
                                        min = 111;
                                }
 
                                if (f < min && min != 111)
                                        ///////// NEW /////////
                                        hightlight.add(new Integer(1)); // true
                                else
                                        hightlight.add(new Integer(0)); // false
                                success.add(new Float(f));
 
                                volume.add(new Integer(rs.getInt(3)));
 												daycount++;
                                if (!rs.next()) {
                                        innerloop = false;
                                        loop = false;
                                        break;
                                }
                                if (!rs.getString(1).equals(chggrp))
                                        innerloop = false;
                                chggrp = rs.getString(1);
                                        }
 
                                        success = padZeros(success, 7 - success.size(),1);
                                        hightlight = padZeros(hightlight, 7 - hightlight.size(),0);
                                        volume = padZeros(volume, 7 - volume.size(),0);
                                        for (int v = 0; v < volume.size(); v++) {
 
                                out.print("<td>" + nv.format(volume.get(v)) + "</td>");
                                        }
 
                                        ///////// NEW /////////
                                        out.print("<td>NA</td>");
                                        ///////// NEW /////////
                %>
        </tr>
        <%
        if (rowcolor % 2 == 0) { /// color code
        %>
        <tr>
 
                <%
                } else {
                %>
        
        <tr style='background-color:#EDEDED'>
                <%
                }
                %>
                <td><b>Sucess</b></td>
 
                <%
                                for (int s = 0; s < success.size(); s++) {
                                int h = ((Integer) hightlight.get(s)).intValue();
                                if (h == 1)
                                        ///////// NEW /////////
                                        //                                        out.print("<td style='background-color:#E33740'>"
                                        out.print("<td style='background-color:#FF0000'>"
                                        ///////// NEW /////////
 
                                        + np.format(success.get(s))
                                        + "</span></td>");
                                else
                                        out.print("<td>" + np.format(success.get(s))
                                        + "</td>");
                                        }
                                        if (min != 111) {
                                out.print("<td>" + np.format(min) + "</td>");
                                        } else {
                                out.print("<td>NA</td>");
                                        }
                %>
        </tr>
        <%
                                volume.clear();
                                success.clear();
                                hightlight.clear();
								daycount=0;
                                rowcolor++;
                        }
 
                        conn.close();
                        rs.close();
                        pstmt.close();
 
                } catch (Exception e) {
 
                        e.printStackTrace();
                        conn = null;
 
                }
        %>
 
</table>

Open in new window

0
 
LVL 10

Author Comment

by:aman0711
ID: 23652609
Thank you Sir,
 I just tried and it worked now. Will go through the code and email you the logic. please let me know if I get it right :)
0
 
LVL 10

Author Comment

by:aman0711
ID: 23653375
Sir are you still online?
0
 
LVL 10

Author Comment

by:aman0711
ID: 23653642
In the previous code, we were directly adding the 0's in ArrayList a with
a.add(new Integer(0))
In our padZeros method, we just had two arguments, one for the list and other one for the count, but nothing for the value to be filled.
So we added that this time,  and the value got added in all the interations of the loops.
Am I close Sir?
 
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 23653659
well thats how I fixed it right now, that is not an explanation of why it was not working before...
0
 
LVL 10

Author Comment

by:aman0711
ID: 23653687
ohk.. I will try again to check it why it wasnt working and get back to you.
one question Sir, right now we dont have anything for success and value when the dates are missing. lets suppose, for a particular channel group, all the 7 dates are present and for success there in null data in the Table. will our code handle that?
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 23653778
nopes, the currently the code is based on missing date column, and assuming that other values will be available with it..

if there are null values for either columns, it will show nulls ( or Zero becasue I believe in the code we are handling nulls to be zero in success)
0
 
LVL 10

Author Comment

by:aman0711
ID: 23653820
Yes thats the problem I am facing now :(
There is this one table , where all the dates are present for a channel group, with volume as 0 and Success as null
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 23654378
if that is the case, the table shd still be getting created, with success as 100% or 0 and volume as zero, which is correct data in my opinion
0
 
LVL 10

Author Comment

by:aman0711
ID: 23654541
Yes Sir, In that case, volume in coming up as 0 and Success as 0.00%, where as it should come as 100.00%.
Today they finalized on keeping null data.
Could you please give me some hints , how to handle null data and get the same output?
I will think of something tonight and then let u know tommorrow
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 23654612
Here is the documentation of rs.getFloat from javadocs

public float getFloat(int columnIndex)
               throws SQLException
Retrieves the value of the designated column in the current row of this ResultSet object as a float in the Java programming language.
Parameters:
columnIndex - the first column is 1, the second is 2, ...
Returns:
the column value; if the value is SQL NULL, the value returned is 0

& in your code your are getting the value of success at this place..
float f = rs.getFloat(2);
 

0
 
LVL 10

Author Comment

by:aman0711
ID: 23654669
yes Sir you are abosutely right. I guess I didnt explain it right.
if the data on the back end is coming up as NULL... how do I convert it to 100%.
I will explaing it here:
If you see the last snapshot I attached. The number of channel groups are 14. In actual there are 15 channel groups, but for the missin channel group, we dont have data for last 7 days , and hence it doesnt get populated in the view We are using for our jsp page.
but with todays decision, they will make a new view, that will populate, the missin channel group will null data.
so now I need to include that missin channel group in this table.
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 23654917
>>yes Sir you are abosutely right. I guess I didnt explain it right.

if the data on the back end is coming up as NULL... how do I convert it to 100%.

Its so simple an answer that its not even worth mentioning..

I actually almost answered it in my previous post where I posted the javadoc.
0
 
LVL 10

Author Comment

by:aman0711
ID: 23655444
Sorry Sir,
I didnt explain the problem correctly and acted dumb. I am sorry.

I will show u the snap shot tomorrow.. dont know who to explain it right now :)
again sorry sir
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 23655457
I did get your problem..

your problem is, if there is NULL or 0 in success rate, how to show 100% instead of null..

& it is as simple as if(f==0)... hence I said there is not much to explain
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question