Index: trunk/ver3/completeReport.jsp
===================================================================
diff -u -r223 -r233
--- trunk/ver3/completeReport.jsp (.../completeReport.jsp) (revision 223)
+++ trunk/ver3/completeReport.jsp (.../completeReport.jsp) (revision 233)
@@ -71,46 +71,51 @@
|
-
+
- |
- pre-registered attendees |
+ |
+ Count |
+ Day1 |
+ Day2 |
- |
- pre-registered dealers |
+ Pre-Registered Attendees |
+ |
+ |
+ |
- |
- showed up and checked in (w/walk-ins) |
+ Pre-Registered Dealers |
+ |
+ |
+ |
-
- |
- walk-ins |
-
-
- |
- dealerships that attended |
-
-
- |
- average attendees per dealership |
-
+
+
+ | Attendees |
+ |
+ |
+
+
+ | Dealers that attended |
+ |
+ |
+
+
+
+
+ | Attendees |
+ |
+ |
+
+
+ | Dealers that attended |
+ |
+ |
+
+
- | |
- attended the same event last year |
-
-
- | |
- new dealers who was not at the event last year |
-
-
- | |
- dealers attended this year as well as last year |
-
-
-
Download Report
|
@@ -147,38 +152,72 @@
Attended 2nd Day |
-
-
-
-
-
- |
- |
- |
- |
-  |
- |
-
-
-
-
-
-
-
- |
- |
- |
- |
- |
- |
-
-
-
-
- |
+
+
+
+
+
+ |
+ |
+ |
+ |
+ |
+ |
+
+
+
+
+
+
+ |
+ |
+ |
+ |
+ |
+ |
+
+
+
+
+
+
+
+
+
+
+
+
+ |
+ |
+ |
+ |
+ |
+ |
+
+
+
+
+
+
+
+ |
+ |
+ |
+ |
+ |
+ |
+
+
+
+
+
+
+ |
+
+
Index: trunk/ver3/summaryReport.jsp
===================================================================
diff -u -r208 -r233
--- trunk/ver3/summaryReport.jsp (.../summaryReport.jsp) (revision 208)
+++ trunk/ver3/summaryReport.jsp (.../summaryReport.jsp) (revision 233)
@@ -71,72 +71,64 @@
|
-
+
- |
- pre-registered attendees |
-
-
- |
- pre-registered dealers |
+ |
+ Count |
Day1 |
Day2 |
- |
- showed up and checked in (w/walk-ins) |
- |
- |
+ Pre-Registered Attendees |
+ |
+ |
+ |
- |
- walk-ins |
- |
- |
+ Pre-Registered Dealers |
+ |
+ |
+ |
-
- |
- dealerships that attended |
- |
- |
-
-
-
- |
- average attendees per dealership |
- |
- |
-
+
+
+ | Attendees |
+ |
+ |
+
+
+ | Dealers that attended |
+ |
+ |
+
+
+
+
+ | Attendees |
+ |
+ |
+ |
+
+
+ | Dealers that attended |
+ |
+ |
+
+
+
+
- | |
- showed up and checked in (last year) |
- |
- |
-
-
- | |
- dealerships that attended (last year) |
-
-
- | |
- new dealers who was not at the event last year |
-
-
- | |
- dealers attended this year as well as last year |
-
-
- |
+ |
Pre-show Download Report
|
- |
+ |
Post Show Summary Report
|
- |
+ |
Download Per Rep Radius Report
|
@@ -151,4 +143,4 @@
|
-
\ No newline at end of file
+
Index: trunk/WEB-INF/src/com/showcase/model/CompleteReportObject.java
===================================================================
diff -u -r223 -r233
--- trunk/WEB-INF/src/com/showcase/model/CompleteReportObject.java (.../CompleteReportObject.java) (revision 223)
+++ trunk/WEB-INF/src/com/showcase/model/CompleteReportObject.java (.../CompleteReportObject.java) (revision 233)
@@ -21,6 +21,10 @@
private boolean attendedSecondDay;
+ private boolean attendedHospFirstDay;
+
+ private boolean attendedHospSecondDay;
+
private int noOfShows;
private int noOfDealers;
@@ -57,6 +61,22 @@
this.attendedSecondDay = attendedSecondDay;
}
+ public boolean isAttendedHospFirstDay() {
+ return attendedHospFirstDay;
+ }
+
+ public void setAttendedHospFirstDay(boolean attendedHospFirstDay) {
+ this.attendedHospFirstDay = attendedHospFirstDay;
+ }
+
+ public boolean isAttendedHospSecondDay() {
+ return attendedHospSecondDay;
+ }
+
+ public void setAttendedHospSecondDay(boolean attendedHospSecondDay) {
+ this.attendedHospSecondDay = attendedHospSecondDay;
+ }
+
/**
* @return Returns the attendeeName.
*/
Index: trunk/WEB-INF/src/com/showcase/sql/SummaryRptDAOImpl.java
===================================================================
diff -u -r208 -r233
--- trunk/WEB-INF/src/com/showcase/sql/SummaryRptDAOImpl.java (.../SummaryRptDAOImpl.java) (revision 208)
+++ trunk/WEB-INF/src/com/showcase/sql/SummaryRptDAOImpl.java (.../SumRepDAOImpl.java) (revision 233)
@@ -1,43 +1,148 @@
package com.showcase.sql;
+import com.showcase.dao.SummaryReportDAO;
+import com.showcase.exception.DAOException;
+import com.showcase.util.BaseDAO;
+import org.apache.commons.logging.Log;
+import org.apache.commons.logging.LogFactory;
+
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
-import org.apache.commons.logging.Log;
-import org.apache.commons.logging.LogFactory;
-
-import com.showcase.dao.SummaryReportDAO;
-import com.showcase.exception.DAOException;
-import com.showcase.util.BaseDAO;
-
@SuppressWarnings("unchecked")
-public class SummaryRptDAOImpl extends BaseDAO implements SummaryReportDAO {
+public class SumRepDAOImpl extends BaseDAO implements SummaryReportDAO {
- private static final Log log = LogFactory.getLog(SummaryRptDAOImpl.class);
+ private static final Log log = LogFactory.getLog(SumRepDAOImpl.class);
+ private static final String STATS_QUERY = "\n" +
+ "\n" +
+ "SELECT * \n" +
+ "FROM\n" +
+ " ( --pre-registered attendees && pre-registered dealers \n" +
+ " SELECT\n" +
+ " [year] = YEAR(e.deadline) ,\n" +
+ " e.event_code ,\n" +
+ " e.event_name ,\n" +
+ " pre_registered_attendees = COUNT(attending_show) ,\n" +
+ " pre_registered_dealers = COUNT(distinct dealer_id) \n" +
+ " FROM\n" +
+ " [events] e \n" +
+ " LEFT OUTER JOIN\n" +
+ " attendees a \n" +
+ " ON a.event_id = e.event_id \n" +
+ " AND a.status != 'D' \n" +
+ " AND a.prereg_date < e.deadline \n" +
+ " AND (\n" +
+ " a.attending_show = 'Y' \n" +
+ " OR a.attending_meet_and_greet = 'Y' \n" +
+ " OR a.attending_hosp = 'Y'\n" +
+ " ) \n" +
+ " WHERE\n" +
+ " e.event_id = ? \n" +
+ " GROUP BY\n" +
+ " e.deadline,\n" +
+ " e.event_code,\n" +
+ " e.event_name ) a CROSS \n" +
+ "JOIN\n" +
+ " (\n" +
+ " --attended first day show \n" +
+ " SELECT\n" +
+ " first_day_show_checked_in_attendees = COUNT(attending_show) ,\n" +
+ " first_day_show_checked_in_dealers = COUNT(DISTINCT dealer_id) \n" +
+ " FROM\n" +
+ " [events] e \n" +
+ " LEFT OUTER JOIN\n" +
+ " attendees a \n" +
+ " ON a.event_id = e.event_id \n" +
+ " AND a.status != 'D' \n" +
+ " AND a.checked_in = 'Y' \n" +
+ " WHERE\n" +
+ " e.event_id = ? \n" +
+ " ) b CROSS \n" +
+ "JOIN\n" +
+ " (\n" +
+ " --attended second day show \n " +
+ " SELECT\n" +
+ " second_day_show_checked_in_attendees = COUNT(attending_show) ,\n" +
+ " second_day_show_checked_in_dealers = COUNT(DISTINCT dealer_id) \n" +
+ " FROM\n" +
+ " [events] e \n" +
+ " LEFT OUTER JOIN\n" +
+ " attendees a \n" +
+ " ON a.event_id = e.event_id \n" +
+ " AND a.status != 'D' \n" +
+ " AND a.checked_in_2nd_day = 'Y' \n" +
+ " WHERE\n" +
+ " e.event_id = ? \n" +
+ " ) c CROSS \n" +
+ "JOIN\n" +
+ " (\n" +
+ " --attended first day hosp \n " +
+ " SELECT\n" +
+ " first_day_hosp_checked_in_attendees = COUNT(attending_show) ,\n" +
+ " first_day_hosp_checked_in_dealers = COUNT(DISTINCT dealer_id) \n" +
+ " FROM\n" +
+ " [events] e \n" +
+ " LEFT OUTER JOIN\n" +
+ " attendees a \n" +
+ " ON a.event_id = e.event_id \n" +
+ " AND a.status != 'D' \n" +
+ " AND a.checked_in_hosp_day1 = 'Y' \n" +
+ " WHERE\n" +
+ " e.event_id = ? \n" +
+ " ) d CROSS \n" +
+ "JOIN\n" +
+ " (\n" +
+ " --attended second day hosp \n" +
+ " SELECT\n" +
+ " second_day_hosp_checked_in_attendees = COUNT(attending_show) ,\n" +
+ " second_day_hosp_checked_in_dealers = COUNT(DISTINCT dealer_id) \n" +
+ " FROM\n" +
+ " [events] e \n" +
+ " LEFT OUTER JOIN\n" +
+ " attendees a \n" +
+ " ON a.event_id = e.event_id \n" +
+ " AND a.status != 'D' \n" +
+ " AND a.checked_in_hosp_day2 = 'Y' \n" +
+ " WHERE\n" +
+ " e.event_id = ? \n" +
+ " ) e";
+
public Map getSummaryReport(int eventId) throws DAOException {
Connection con = null;
Map summaryReport = new HashMap();
try {
log.debug("Getting summary report");
-
con = getDBConnection();
- CallableStatement cstmt = con.prepareCall("{call spGetDealerAttendeeAggregate (?) }");
+ CallableStatement cstmt = con.prepareCall(STATS_QUERY);
cstmt.setInt(1, eventId);
+ cstmt.setInt(2, eventId);
+ cstmt.setInt(3, eventId);
+ cstmt.setInt(4, eventId);
+ cstmt.setInt(5, eventId);
cstmt.execute();
ResultSet rs = cstmt.getResultSet();
if (rs.next()) {
summaryReport.put("showCode", rs.getString("event_code"));
- summaryReport.put("preRegisteredAttendees", new Integer(rs.getInt("preregistered_attendee_count")));
- summaryReport.put("preRegisteredDealers", new Integer(rs.getInt("preregistered_dealer_count")));
- summaryReport.put("showedUpAndCheckedIn", String.valueOf(rs.getInt("attending_attendee_count")));
- summaryReport.put("walkins", String.valueOf(rs.getInt("walkin_attendee_count")));
- summaryReport.put("dealershipsThatAttended", String.valueOf(rs.getInt("attending_dealer_count")));
+ summaryReport.put("preRegisteredAttendees", new Integer(rs.getInt("pre_registered_attendees")));
+ summaryReport.put("preRegisteredDealers", new Integer(rs.getInt("pre_registered_dealers")));
+
+ summaryReport.put("first_day_show_checked_in_attendees", new Integer(rs.getInt("first_day_show_checked_in_attendees")));
+ summaryReport.put("first_day_show_checked_in_dealers", new Integer(rs.getInt("first_day_show_checked_in_dealers")));
+
+ summaryReport.put("second_day_show_checked_in_attendees", new Integer(rs.getInt("second_day_show_checked_in_attendees")));
+ summaryReport.put("second_day_show_checked_in_dealers", new Integer(rs.getInt("second_day_show_checked_in_dealers")));
+
+ summaryReport.put("first_day_hosp_checked_in_attendees", new Integer(rs.getInt("first_day_show_checked_in_attendees")));
+ summaryReport.put("first_day_hosp_checked_in_dealers", new Integer(rs.getInt("first_day_show_checked_in_dealers")));
+
+ summaryReport.put("second_day_hosp_checked_in_attendees", new Integer(rs.getInt("second_day_show_checked_in_attendees")));
+ summaryReport.put("second_day_hosp_checked_in_dealers", new Integer(rs.getInt("second_day_show_checked_in_dealers")));
}
} catch (SQLException sqlE) {
Index: trunk/WEB-INF/src/com/showcase/sql/DAOFactoryImpl.java
===================================================================
diff -u -r208 -r233
--- trunk/WEB-INF/src/com/showcase/sql/DAOFactoryImpl.java (.../DAOFactoryImpl.java) (revision 208)
+++ trunk/WEB-INF/src/com/showcase/sql/DAOFactoryImpl.java (.../DAOFactoryImpl.java) (revision 233)
@@ -41,7 +41,7 @@
}
public SummaryReportDAO getSummaryReportDAO() {
- return new SummaryReportDAOImpl();
+ return new SumRepDAOImpl();
}
public CompleteReportDAO getCompleteReportDAO() {
@@ -68,4 +68,4 @@
return new ShowCaseEmailDAOImpl();
}
-}
\ No newline at end of file
+}
Index: trunk/WEB-INF/src/com/showcase/sql/PerRepReportDAOImpl.java
===================================================================
diff -u -r223 -r233
--- trunk/WEB-INF/src/com/showcase/sql/PerRepReportDAOImpl.java (.../PerRepReportDAOImpl.java) (revision 223)
+++ trunk/WEB-INF/src/com/showcase/sql/PerRepReportDAOImpl.java (.../PerRepReportDAOImpl.java) (revision 233)
@@ -106,12 +106,12 @@
con = getDBConnection();
StringBuffer buff = new StringBuffer();
buff.append(" select DISTINCT 'attended', d.salesman, d.dealer_id, d.given_dealer_name, ");
- buff.append(" a.attendee_name, a.checked_in, a.checked_in_2nd_day FROM dealers d JOIN attendees a ON a.dealer_id=d.dealer_id WHERE a.event_id=");
+ buff.append(" a.attendee_name, a.checked_in, a.checked_in_2nd_day, a.checked_in_hosp_day1, a.checked_in_hosp_day2 FROM dealers d JOIN attendees a ON a.dealer_id=d.dealer_id WHERE a.event_id=");
buff.append(event_id);
buff.append(" AND d.salesman='");
buff.append(salesman);
buff.append("' AND checked_in = 'y' UNION ALL ");
- buff.append(" SELECT DISTINCT 'noshow', d.salesman, d.dealer_id, d.given_dealer_name, a.attendee_name, a.checked_in, a.checked_in_2nd_day ");
+ buff.append(" SELECT DISTINCT 'noshow', d.salesman, d.dealer_id, d.given_dealer_name, a.attendee_name, a.checked_in, a.checked_in_2nd_day, a.checked_in_hosp_day1, a.checked_in_hosp_day2 ");
buff.append(" FROM dealers d JOIN attendees a on a.dealer_id=d.dealer_id ");
buff.append(" WHERE a.event_id=");
buff.append(event_id);
@@ -182,6 +182,18 @@
} else {
report.setAttendedSecondDay(false);
}
+
+ if (rs.getString("checked_in_hosp_day1").equals("Y")) {
+ report.setAttendedHospFirstDay(true);
+ } else if (rs.getString("checked_in_hosp_day1").equals("N")) {
+ report.setAttendedHospFirstDay(false);
+ }
+
+ if (rs.getString("checked_in_hosp_day2") != null && rs.getString("checked_in_hosp_day2").equals("Y")) {
+ report.setAttendedHospSecondDay(true);
+ } else {
+ report.setAttendedHospSecondDay(false);
+ }
return report;
}
Index: trunk/WEB-INF/src/com/showcase/sql/CompleteReportDAOImpl.java
===================================================================
diff -u -r223 -r233
--- trunk/WEB-INF/src/com/showcase/sql/CompleteReportDAOImpl.java (.../CompleteReportDAOImpl.java) (revision 223)
+++ trunk/WEB-INF/src/com/showcase/sql/CompleteReportDAOImpl.java (.../CompleteReportDAOImpl.java) (revision 233)
@@ -37,7 +37,7 @@
con = getDBConnection();
StringBuffer buff = new StringBuffer();
buff.append(" select d.salesman, d.dealer_id, d.given_dealer_name, ");
- buff.append(" a.attendee_name , a.checked_in, a.checked_in_2nd_day from dealers d, attendees a, events e where e.event_id=");
+ buff.append(" a.attendee_name , a.checked_in, a.checked_in_2nd_day, a.checked_in_hosp_day1, a.checked_in_hosp_day2 from dealers d, attendees a, events e where e.event_id=");
buff.append(event_id);
buff.append(" and e.event_id=a.event_id and a.dealer_id=d.dealer_id ");
buff.append(" and a.status!='D'ORDER BY d.salesman, d.dealer_id");
@@ -93,6 +93,19 @@
else
if(rs.getString(6).equals("N"))
report.setAttendedSecondDay(false);
+
+
+ if (rs.getString(7).equals("Y")) {
+ report.setAttendedHospFirstDay(true);
+ } else if (rs.getString(7).equals("N")) {
+ report.setAttendedHospFirstDay(false);
+ }
+
+ if (rs.getString(8) != null && rs.getString(8).equals("Y")) {
+ report.setAttendedHospSecondDay(true);
+ } else {
+ report.setAttendedHospSecondDay(false);
+ }
return report;
}
| |