Index: trunk/WEB-INF/src/com/showcase/sql/ShowCaseDAOImpl.java
===================================================================
diff -u -r223 -r236
--- trunk/WEB-INF/src/com/showcase/sql/ShowCaseDAOImpl.java (.../ShowCaseDAOImpl.java) (revision 223)
+++ trunk/WEB-INF/src/com/showcase/sql/ShowCaseDAOImpl.java (.../ShowCaseDAOImpl.java) (revision 236)
@@ -1,18 +1,5 @@
package com.showcase.sql;
-import java.sql.Connection;
-import java.sql.PreparedStatement;
-import java.sql.ResultSet;
-import java.sql.SQLException;
-import java.text.DateFormat;
-import java.text.ParseException;
-import java.text.SimpleDateFormat;
-import java.util.ArrayList;
-import java.util.List;
-
-import org.apache.commons.logging.Log;
-import org.apache.commons.logging.LogFactory;
-
import com.showcase.dao.ShowCaseDAO;
import com.showcase.exception.DAOException;
import com.showcase.mainframe.general.CustomVector;
@@ -22,375 +9,548 @@
import com.showcase.model.CompleteReportObject;
import com.showcase.model.ShowCaseDetailObject;
import com.showcase.util.BaseDAO;
+import org.apache.commons.logging.Log;
+import org.apache.commons.logging.LogFactory;
+import java.sql.*;
+import java.text.DateFormat;
+import java.text.ParseException;
+import java.text.SimpleDateFormat;
+import java.util.ArrayList;
+import java.util.List;
+
@SuppressWarnings("unchecked")
public class ShowCaseDAOImpl extends BaseDAO implements ShowCaseDAO {
-
- private static final Log log = LogFactory.getLog(ShowCaseDAOImpl.class);
-
- public ShowCaseDAOImpl(){
-
- }
- public ShowCaseDetailObject[] getShowCaseList() throws DAOException{
- ResultSet rs = null;
- Connection con=null;
- PreparedStatement pstmt = null;
- StringBuffer buff=null;
- ShowCaseDetailObject showcase;
- List list = new ArrayList();
- try {
- if(log.isDebugEnabled())
- log.debug("Getting New Showcase List");
- con=getDBConnection();
- buff=new StringBuffer("select e.event_code, e.event_name, e.location, e.event_lock_status, e.event_id, " );
- buff.append(" e.start_date, e.stop_date from events e where e.status != 'D' AND start_date > dateadd(m,-16, getDate()) ");
- buff.append("ORDER BY e.start_date desc, e.event_code");
- pstmt=con.prepareStatement(buff.toString());
- rs=pstmt.executeQuery();
- while (rs.next())
- {
- showcase = extractShowCaseListFromResultSet(rs);
- list.add(showcase);
- }
- } catch (SQLException sqlE)
- {
- log.error("SQLException while getting showcase list", sqlE);
- throw new DAOException("SQLException while getting showcase list " + sqlE.getMessage());
- }
- catch (Exception e)
- {
- log.error("Exception while getting showcase list", e);
- throw new DAOException("Exception while getting showcase list" + e.getMessage());
- }
- finally
- {
- try { con.close(); } catch(SQLException sq) { }
- }
-
- ShowCaseDetailObject[] showcaselist = null;
- if (list.size() > 0)
- {
- showcaselist = (ShowCaseDetailObject[]) list.toArray(new ShowCaseDetailObject[list.size()]);
- }
-
- return showcaselist;
-
+ private static final Log log = LogFactory.getLog(ShowCaseDAOImpl.class);
+
+ public ShowCaseDAOImpl() {
+
}
-
- protected ShowCaseDetailObject extractShowCaseListFromResultSet(ResultSet rs) throws SQLException
- {
- ShowCaseDetailObject showcase = new ShowCaseDetailObject();
- showcase.setEventCode(rs.getString(1));
- showcase.setEventName(rs.getString(2));
- showcase.setLocation(rs.getString(3));
- showcase.setLockStatus(rs.getString(4));
- showcase.setEventId(rs.getInt(5));
- showcase.setStartDate(rs.getString(6).substring(5, 10));
- showcase.setEndDate(rs.getString(7).substring(5, 10));
-
- DateFormat sdf =
- new java.text.SimpleDateFormat("yyyy-MM-dd");
- try{
- java.util.Date endDate= (java.util.Date) sdf.parse(rs.getString(7));
- java.util.Date today = new java.util.Date();
- if(endDate.before(today)){
- showcase.setPastEvent(true);
- }
- else
- showcase.setPastEvent(false);
- }catch(ParseException pe){
- }
-
- return showcase;
- }
-
- public int createNewShowCase(ShowCaseDetailObject showcase) throws DAOException{
- int insertedRows = 0;
- PreparedStatement pstmt = null;
- @SuppressWarnings("unused")
- ResultSet rs = null;
- Connection con=null;
-
- try {
- if(log.isDebugEnabled())
- log.debug("Creating New Showcase");
- con = getDBConnection();
- String eventCode=showcase.getEventCode();
- if(eventCode!=null)
- eventCode=eventCode.trim().toUpperCase();
- String startDate=null;
- String endDate=null;
- @SuppressWarnings("unused")
- String showName=null;
- String validated=null;
-
-
-
- java.util.Date d = new java.util.Date();
- SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yy HH:mm:ss");
-
- //If showcase do mainframe verfication and get the start date and end date for the show
- if(showcase.getType().equals("showcase")){
- Transaction transaction=new ShowVerification(eventCode);
- JMainframeImpl mf= new JMainframeImpl();
- String responseMF=mf.getData(transaction.getMID());
- CustomVector cvec=null;
- try {
- transaction.parseMOD(responseMF);
- cvec= transaction.getMODDefinition();
- } catch (Exception e) {
- //e.printStackTrace();
- }
+ public ShowCaseDetailObject[] getShowCaseList() throws DAOException {
+ ResultSet rs = null;
+ Connection con = null;
+ PreparedStatement pstmt = null;
+ StringBuffer buff = null;
+ ShowCaseDetailObject showcase;
+ List list = new ArrayList();
+ try {
+ if (log.isDebugEnabled())
+ log.debug("Getting New Showcase List");
+ con = getDBConnection();
+ buff = new StringBuffer("select e.event_code, e.event_name, e.location, e.event_lock_status, e.event_id, ");
+ buff.append(" e.start_date, e.stop_date from events e where e.status != 'D' AND start_date > dateadd(m,-16, getDate()) ");
+ buff.append("ORDER BY e.start_date desc, e.event_code");
+ pstmt = con.prepareStatement(buff.toString());
+ rs = pstmt.executeQuery();
+ while (rs.next()) {
+ showcase = extractShowCaseListFromResultSet(rs);
+ list.add(showcase);
+ }
+ } catch (SQLException sqlE) {
+ log.error("SQLException while getting showcase list", sqlE);
+ throw new DAOException("SQLException while getting showcase list " + sqlE.getMessage());
+ } catch (Exception e) {
+ log.error("Exception while getting showcase list", e);
+ throw new DAOException("Exception while getting showcase list" + e.getMessage());
+ } finally {
+ try {
+ con.close();
+ } catch (SQLException sq) {
+ }
+ }
-
- startDate =(String)cvec.get("Start Date");
- endDate = (String)cvec.get("End Date");
-
- showName=(String)cvec.get("Show Name");
- validated="N";
- String error = (String)cvec.get("Error");
- if (error.equals("Y"))
- validated="Y";
- else
- validated="N";
- }
- else{
- //If Hospitality do not do show verification
- startDate=sdf.format(d);
- endDate=sdf.format(d);
- showName=showcase.getEventName();
- validated="N";
- }
-
+ ShowCaseDetailObject[] showcaselist = null;
+ if (list.size() > 0) {
+ showcaselist = (ShowCaseDetailObject[]) list.toArray(new ShowCaseDetailObject[list.size()]);
+ }
+ return showcaselist;
-
- //pstmt = con.prepareStatement("INSERT INTO events (event_code, event_name, start_date, stop_date, deadline, location, validated, has_hosp, has_showcase, date_added, status, last_update_dt, event_lock_status, event_locker,rep_registration_deadline, email_reminder_date, zip_code, event_radius) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
- pstmt = con.prepareStatement("INSERT INTO events (event_code, event_name, start_date, stop_date, deadline, location, validated, has_hosp, has_showcase, date_added, status, last_update_dt, event_lock_status, event_locker,rep_registration_deadline, email_reminder_date, zip_code, event_radius, display) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
- pstmt.setString(1, eventCode);
- pstmt.setString(2, showcase.getEventName());
- if (startDate != null && !startDate.startsWith("0000"))
- pstmt.setString(3, startDate);
- else pstmt.setString(3, sdf.format(d));
-
- if (startDate != null && !startDate.startsWith("0000"))
- pstmt.setString(4, endDate);
- else pstmt.setString(4, sdf.format(d));
-
- pstmt.setString(5, showcase.getDeadline());
- pstmt.setString(6, showcase.getLocation());
- pstmt.setString(7, validated);
- // If showcase set has_hosp and has_showcase to yes
- if(showcase.getType().equals("showcase")){
- pstmt.setString(8, "N");
- pstmt.setString(9, "Y");
- }
- else
- {
- //If hospitality set has_hosp to yes and has_showcase to no
- pstmt.setString(8, "Y");
- pstmt.setString(9, "N");
- }
-
- pstmt.setString(10, sdf.format(d));
- pstmt.setString(11, "A");//set the showcase to active
- pstmt.setString(12, sdf.format(d));
- //set to unlock for now later when remote showcase download attendees for this show change the status to locked
- pstmt.setString(13, "U");
- pstmt.setString(14, "ME");
- pstmt.setString(15, showcase.getRepDeadline());
- pstmt.setString(16, showcase.getEmailDeadline());
- pstmt.setString(17, showcase.getZipCode());
- pstmt.setInt(18, Integer.parseInt(showcase.getRadius()));
- pstmt.setString(19, showcase.getDisplay());
- insertedRows = pstmt.executeUpdate();
- System.out.println("Inserted Value"+ insertedRows);
- } catch (SQLException sqlE)
- {
- log.error("SQLException while creating a new showcase ", sqlE);
- throw new DAOException("SQLException while creating a new showcase " + sqlE.getMessage());
- }
- catch (Exception e)
- {
- log.error("Exception while creating a new showcase ", e);
- throw new DAOException("Exception while creating a new showcase" + e.getMessage());
- }
- finally
- {
- try { con.close(); } catch(SQLException sq) { }
- }
-
- return insertedRows;
- }
-
-
- public ShowCaseDetailObject getShowCaseInfo(int event_id) throws DAOException{
- PreparedStatement pstmt = null;
- ResultSet rs=null;
- Connection con=null;
- StringBuffer buff= null;
- int eventId = event_id;
- ShowCaseDetailObject sc=new ShowCaseDetailObject();
- SimpleDateFormat sdf = new SimpleDateFormat("MM-dd-yyyy");
- try {
- if(log.isDebugEnabled())
- log.debug("Getting Showcase Information");
- con = getDBConnection();
- buff=new StringBuffer("select e.event_code, e.event_name, e.location, e.start_date, e.stop_date, e.deadline, e.has_hosp, e.has_showcase, e.event_lock_status, e.rep_registration_deadline, e.email_reminder_date, e.zip_code, e.event_radius, e.display");
- buff.append(" from events e");
- buff.append(" where e.event_id=");
- buff.append(eventId);
- pstmt=con.prepareStatement(buff.toString());
- rs=pstmt.executeQuery();
- if(rs.next()){
- sc.setEventId(event_id);
- sc.setEventCode(rs.getString(1).trim());
- sc.setEventName(rs.getString(2).trim());
- sc.setLocation(rs.getString(3).trim());
- sc.setStartDate(sdf.format(rs.getDate(4)));
- sc.setEndDate(sdf.format(rs.getDate(5)));
- sc.setDeadline(sdf.format(rs.getDate(6)));
- sc.setIsHospitality(rs.getString(7).trim());
- sc.setIsShowcase(rs.getString(8).trim());
- //sc.setIsValidated(rs.getString(7));
- sc.setLockStatus(rs.getString(9));
- if((rs.getDate("rep_registration_deadline"))!=null)
- sc.setRepDeadline(sdf.format(rs.getDate("rep_registration_deadline")));
- if((rs.getDate("email_reminder_date"))!=null)
- sc.setEmailDeadline(sdf.format(rs.getDate("email_reminder_date")));
- sc.setZipCode(rs.getString("zip_code"));
- sc.setRadius(rs.getString("event_radius"));
- sc.setDisplay(rs.getString("display"));
-
- return sc;
- }
-
- } catch (SQLException sqlE)
- {
- log.error("SQLException while getting showcase information", sqlE);
- throw new DAOException("SQLException while getting showcase information " + sqlE.getMessage());
- }
+ }
- catch (Exception e)
- {
- log.error("Exception while getting showcase information ", e);
- throw new DAOException("Exception while getting showcase information " + e.getMessage());
- }
- finally
- {
- try { con.close(); } catch(SQLException sq) { }
- }
-
- return null;
-
- }
+ protected ShowCaseDetailObject extractShowCaseListFromResultSet(ResultSet rs) throws SQLException {
+ ShowCaseDetailObject showcase = new ShowCaseDetailObject();
- public int isValidZipCode(String zipCode) throws DAOException{
- PreparedStatement pStmt = null;
- Connection con=null;
- ResultSet rs=null;
- int status=0;
- try {
- if(log.isDebugEnabled())
- log.debug("Checking if zipcode is valid");
- con = getDBConnection();
- pStmt=con.prepareStatement("select * from zip_codes where zip_code=?");
- pStmt.setString(1, zipCode);
- rs=pStmt.executeQuery();
- while(rs.next()){
- status=1;
- }
- } catch (SQLException sqlE)
- {
- log.error("SQLException while checking if zipcode is valid", sqlE);
- throw new DAOException("SQLException while checking if zipcode is valid " + sqlE.getMessage());
- }
+ showcase.setEventCode(rs.getString(1));
+ showcase.setEventName(rs.getString(2));
+ showcase.setLocation(rs.getString(3));
+ showcase.setLockStatus(rs.getString(4));
+ showcase.setEventId(rs.getInt(5));
+ showcase.setStartDate(rs.getString(6).substring(5, 10));
+ showcase.setEndDate(rs.getString(7).substring(5, 10));
- catch (Exception e)
- {
- log.error("Exception while checking if zipcode is valid ", e);
- throw new DAOException("Exception while checking if zipcode is valid " + e.getMessage());
- }
- finally
- {
- try { con.close(); } catch(SQLException sq) { }
- }
- return status;
- }
+ DateFormat sdf =
+ new java.text.SimpleDateFormat("yyyy-MM-dd");
+ try {
+ java.util.Date endDate = (java.util.Date) sdf.parse(rs.getString(7));
+ java.util.Date today = new java.util.Date();
+ if (endDate.before(today)) {
+ showcase.setPastEvent(true);
+ } else
+ showcase.setPastEvent(false);
+ } catch (ParseException pe) {
+ }
- public CompleteReportObject getShowTotals(int event_id, String dealer_id) throws DAOException{
- PreparedStatement pstmt = null;
- ResultSet rs=null;
- Connection con=null;
- CompleteReportObject report = new CompleteReportObject();
- List dealers = new ArrayList();
- List dealersNotCheckedIn = new ArrayList();
-
- try {
- if(log.isDebugEnabled())
- log.debug("Getting show totals");
- 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(event_id);
- buff.append(" and e.event_id=a.event_id and a.dealer_id=d.dealer_id ");
- buff.append(" and a.status!='D'");
+ return showcase;
+ }
- if(dealer_id!=null){
- buff.append(" and d.salesman='");
- buff.append(dealer_id);
- buff.append("'");
- }
- String common = buff.toString();
- StringBuffer checkedIn = new StringBuffer();
- checkedIn.append(common);
- checkedIn.append(" and a.checked_in='Y'");
- pstmt=con.prepareStatement(checkedIn.toString());
- rs=pstmt.executeQuery();
- int count=1;
- while(rs.next()){
- count++;
- String dealer = (String)rs.getString(2);
- if (!dealers.contains(dealer))
- dealers.add(dealer);
- }
- report.setNoOfDealers(dealers.size());
- //int ResultCount = rs.getInt("rowcount1") ;
- report.setNoOfShows(count-1);
-
- StringBuffer notCheckedIn = new StringBuffer();
- notCheckedIn.append(common);
- notCheckedIn.append(" and a.checked_in='N'");
- pstmt=con.prepareStatement(notCheckedIn.toString());
- rs=pstmt.executeQuery();
- count=1;
- while(rs.next()){
- count++;
- String dealer = (String)rs.getString(2);
- if (!dealersNotCheckedIn.contains(dealer))
- dealersNotCheckedIn.add(dealer);
- }
- report.setTotalDealers(dealersNotCheckedIn.size());
- //ResultCount = rs.getInt("rowcount1") ;
- report.setTotalAttendees(count-1);
- } catch (SQLException sqlE)
- {
- log.error("SQLException while getting show totals", sqlE);
- throw new DAOException("SQLException while getting show totals " + sqlE.getMessage());
- }
+ public int createNewShowCase(ShowCaseDetailObject showcase) throws DAOException {
+ int insertedRows = 0;
+ PreparedStatement pstmt = null;
+ @SuppressWarnings("unused")
+ ResultSet rs = null;
+ Connection con = null;
- catch (Exception e)
- {
- log.error("Exception while getting show totals", e);
- throw new DAOException("Exception while getting show totals" + e.getMessage());
- }
- finally
- {
- try { con.close(); } catch(SQLException sq) { }
- }
- return report;
- }
+ try {
+ if (log.isDebugEnabled())
+ log.debug("Creating New Showcase");
+ con = getDBConnection();
+ String eventCode = showcase.getEventCode();
+ if (eventCode != null)
+ eventCode = eventCode.trim().toUpperCase();
+ String startDate = null;
+ String endDate = null;
+ @SuppressWarnings("unused")
+ String showName = null;
+ String validated = null;
+
+
+ java.util.Date d = new java.util.Date();
+ SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yy HH:mm:ss");
+
+ //If showcase do mainframe verfication and get the start date and end date for the show
+ if (showcase.getType().equals("showcase")) {
+ Transaction transaction = new ShowVerification(eventCode);
+ JMainframeImpl mf = new JMainframeImpl();
+ String responseMF = mf.getData(transaction.getMID());
+ CustomVector cvec = null;
+ try {
+ transaction.parseMOD(responseMF);
+ cvec = transaction.getMODDefinition();
+ } catch (Exception e) {
+ //e.printStackTrace();
+ }
+
+
+ startDate = (String) cvec.get("Start Date");
+ endDate = (String) cvec.get("End Date");
+
+ showName = (String) cvec.get("Show Name");
+ validated = "N";
+ String error = (String) cvec.get("Error");
+ if (error.equals("Y"))
+ validated = "Y";
+ else
+ validated = "N";
+ } else {
+ //If Hospitality do not do show verification
+ startDate = sdf.format(d);
+ endDate = sdf.format(d);
+ showName = showcase.getEventName();
+ validated = "N";
+ }
+
+
+ //pstmt = con.prepareStatement("INSERT INTO events (event_code, event_name, start_date, stop_date, deadline, location, validated, has_hosp, has_showcase, date_added, status, last_update_dt, event_lock_status, event_locker,rep_registration_deadline, email_reminder_date, zip_code, event_radius) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
+ pstmt = con.prepareStatement("INSERT INTO events (event_code, event_name, start_date, stop_date, deadline, location, validated, has_hosp, has_showcase, date_added, status, last_update_dt, event_lock_status, event_locker,rep_registration_deadline, email_reminder_date, zip_code, event_radius, display) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
+ pstmt.setString(1, eventCode);
+ pstmt.setString(2, showcase.getEventName());
+ if (startDate != null && !startDate.startsWith("0000"))
+ pstmt.setString(3, startDate);
+ else pstmt.setString(3, sdf.format(d));
+
+ if (startDate != null && !startDate.startsWith("0000"))
+ pstmt.setString(4, endDate);
+ else pstmt.setString(4, sdf.format(d));
+
+ pstmt.setString(5, showcase.getDeadline());
+ pstmt.setString(6, showcase.getLocation());
+ pstmt.setString(7, validated);
+ // If showcase set has_hosp and has_showcase to yes
+ if (showcase.getType().equals("showcase")) {
+ pstmt.setString(8, "N");
+ pstmt.setString(9, "Y");
+ } else {
+ //If hospitality set has_hosp to yes and has_showcase to no
+ pstmt.setString(8, "Y");
+ pstmt.setString(9, "N");
+ }
+
+ pstmt.setString(10, sdf.format(d));
+ pstmt.setString(11, "A");//set the showcase to active
+ pstmt.setString(12, sdf.format(d));
+ //set to unlock for now later when remote showcase download attendees for this show change the status to locked
+ pstmt.setString(13, "U");
+ pstmt.setString(14, "ME");
+ pstmt.setString(15, showcase.getRepDeadline());
+ pstmt.setString(16, showcase.getEmailDeadline());
+ pstmt.setString(17, showcase.getZipCode());
+ pstmt.setInt(18, Integer.parseInt(showcase.getRadius()));
+ pstmt.setString(19, showcase.getDisplay());
+ insertedRows = pstmt.executeUpdate();
+ System.out.println("Inserted Value" + insertedRows);
+ } catch (SQLException sqlE) {
+ log.error("SQLException while creating a new showcase ", sqlE);
+ throw new DAOException("SQLException while creating a new showcase " + sqlE.getMessage());
+ } catch (Exception e) {
+ log.error("Exception while creating a new showcase ", e);
+ throw new DAOException("Exception while creating a new showcase" + e.getMessage());
+ } finally {
+ try {
+ con.close();
+ } catch (SQLException sq) {
+ }
+ }
+
+ return insertedRows;
+ }
+
+
+ public ShowCaseDetailObject getShowCaseInfo(int event_id) throws DAOException {
+ PreparedStatement pstmt = null;
+ ResultSet rs = null;
+ Connection con = null;
+ StringBuffer buff = null;
+ int eventId = event_id;
+ ShowCaseDetailObject sc = new ShowCaseDetailObject();
+ SimpleDateFormat sdf = new SimpleDateFormat("MM-dd-yyyy");
+ try {
+ if (log.isDebugEnabled())
+ log.debug("Getting Showcase Information");
+ con = getDBConnection();
+ buff = new StringBuffer("select e.event_code, e.event_name, e.location, e.start_date, e.stop_date, e.deadline, e.has_hosp, e.has_showcase, e.event_lock_status, e.rep_registration_deadline, e.email_reminder_date, e.zip_code, e.event_radius, e.display");
+ buff.append(" from events e");
+ buff.append(" where e.event_id=");
+ buff.append(eventId);
+ pstmt = con.prepareStatement(buff.toString());
+ rs = pstmt.executeQuery();
+ if (rs.next()) {
+ sc.setEventId(event_id);
+ sc.setEventCode(rs.getString(1).trim());
+ sc.setEventName(rs.getString(2).trim());
+ sc.setLocation(rs.getString(3).trim());
+ sc.setStartDate(sdf.format(rs.getDate(4)));
+ sc.setEndDate(sdf.format(rs.getDate(5)));
+ sc.setDeadline(sdf.format(rs.getDate(6)));
+ sc.setIsHospitality(rs.getString(7).trim());
+ sc.setIsShowcase(rs.getString(8).trim());
+ //sc.setIsValidated(rs.getString(7));
+ sc.setLockStatus(rs.getString(9));
+ if ((rs.getDate("rep_registration_deadline")) != null)
+ sc.setRepDeadline(sdf.format(rs.getDate("rep_registration_deadline")));
+ if ((rs.getDate("email_reminder_date")) != null)
+ sc.setEmailDeadline(sdf.format(rs.getDate("email_reminder_date")));
+ sc.setZipCode(rs.getString("zip_code"));
+ sc.setRadius(rs.getString("event_radius"));
+ sc.setDisplay(rs.getString("display"));
+
+ return sc;
+ }
+
+ } catch (SQLException sqlE) {
+ log.error("SQLException while getting showcase information", sqlE);
+ throw new DAOException("SQLException while getting showcase information " + sqlE.getMessage());
+ } catch (Exception e) {
+ log.error("Exception while getting showcase information ", e);
+ throw new DAOException("Exception while getting showcase information " + e.getMessage());
+ } finally {
+ try {
+ con.close();
+ } catch (SQLException sq) {
+ }
+ }
+
+ return null;
+
+ }
+
+ public int isValidZipCode(String zipCode) throws DAOException {
+ PreparedStatement pStmt = null;
+ Connection con = null;
+ ResultSet rs = null;
+ int status = 0;
+ try {
+ if (log.isDebugEnabled())
+ log.debug("Checking if zipcode is valid");
+ con = getDBConnection();
+ pStmt = con.prepareStatement("select * from zip_codes where zip_code=?");
+ pStmt.setString(1, zipCode);
+ rs = pStmt.executeQuery();
+ while (rs.next()) {
+ status = 1;
+ }
+ } catch (SQLException sqlE) {
+ log.error("SQLException while checking if zipcode is valid", sqlE);
+ throw new DAOException("SQLException while checking if zipcode is valid " + sqlE.getMessage());
+ } catch (Exception e) {
+ log.error("Exception while checking if zipcode is valid ", e);
+ throw new DAOException("Exception while checking if zipcode is valid " + e.getMessage());
+ } finally {
+ try {
+ con.close();
+ } catch (SQLException sq) {
+ }
+ }
+ return status;
+ }
+
+ public CompleteReportObject _getShowTotals(int event_id, String dealer_id) throws DAOException {
+ PreparedStatement pstmt = null;
+ ResultSet rs = null;
+ Connection con = null;
+ CompleteReportObject report = new CompleteReportObject();
+ List dealers = new ArrayList();
+ List dealersNotCheckedIn = new ArrayList();
+
+ try {
+ if (log.isDebugEnabled())
+ log.debug("Getting show totals");
+ 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(event_id);
+ buff.append(" and e.event_id=a.event_id and a.dealer_id=d.dealer_id ");
+ buff.append(" and a.status!='D'");
+
+ if (dealer_id != null) {
+ buff.append(" and d.salesman='");
+ buff.append(dealer_id);
+ buff.append("'");
+ }
+ String common = buff.toString();
+ StringBuffer checkedIn = new StringBuffer();
+ checkedIn.append(common);
+ checkedIn.append(" and a.checked_in='Y'");
+ pstmt = con.prepareStatement(checkedIn.toString());
+ rs = pstmt.executeQuery();
+ int count = 1;
+ while (rs.next()) {
+ count++;
+ String dealer = (String) rs.getString(2);
+ if (!dealers.contains(dealer))
+ dealers.add(dealer);
+ }
+ report.setNoOfDealers(dealers.size());
+ //int ResultCount = rs.getInt("rowcount1") ;
+ report.setNoOfShows(count - 1);
+
+ StringBuffer notCheckedIn = new StringBuffer();
+ notCheckedIn.append(common);
+ notCheckedIn.append(" and a.checked_in='N'");
+ pstmt = con.prepareStatement(notCheckedIn.toString());
+ rs = pstmt.executeQuery();
+ count = 1;
+ while (rs.next()) {
+ count++;
+ String dealer = (String) rs.getString(2);
+ if (!dealersNotCheckedIn.contains(dealer))
+ dealersNotCheckedIn.add(dealer);
+ }
+ report.setTotalDealers(dealersNotCheckedIn.size());
+ //ResultCount = rs.getInt("rowcount1") ;
+ report.setTotalAttendees(count - 1);
+ } catch (SQLException sqlE) {
+ log.error("SQLException while getting show totals", sqlE);
+ throw new DAOException("SQLException while getting show totals " + sqlE.getMessage());
+ } catch (Exception e) {
+ log.error("Exception while getting show totals", e);
+ throw new DAOException("Exception while getting show totals" + e.getMessage());
+ } finally {
+ try {
+ con.close();
+ } catch (SQLException sq) {
+ }
+ }
+ return report;
+ }
+
+
+ public CompleteReportObject getShowTotals(int event_id, String dealer_id) throws DAOException {
+ PreparedStatement pstmt = null;
+ Connection con = null;
+ CompleteReportObject report = new CompleteReportObject();
+ List dealers = new ArrayList();
+ List dealersNotCheckedIn = new ArrayList();
+
+ try {
+ if (log.isDebugEnabled())
+ log.debug("Getting show totals");
+ con = getDBConnection();
+ StringBuffer buff = new StringBuffer();
+ buff.append(QUERY);
+ CallableStatement cstmt = con.prepareCall(QUERY);
+ cstmt.setInt(1, event_id);
+ cstmt.execute();
+ ResultSet rs = cstmt.getResultSet();
+ if (rs.next()) {
+ report.setAttendeesCountFirstDayShow(new Integer(rs.getInt("first_day_show_checked_in_attendees")));
+ report.setAttendeesCountSecondDayShow(new Integer(rs.getInt("second_day_show_checked_in_attendees")));
+
+ report.setAttendeesNoShowCountFirstDayShow(new Integer(rs.getInt("no_show_first_day_show_checked_in_attendees")));
+ report.setAttendeesNoShowCountSecondDayShow(new Integer(rs.getInt("no_show_second_day_show_checked_in_attendees")));
+
+
+
+ report.setDealersCountFirstDayShow(new Integer(rs.getInt("first_day_show_checked_in_dealers")));
+ report.setDealersCountSecondDayShow(new Integer(rs.getInt("second_day_show_checked_in_dealers")));
+
+ report.setDealersNoShowCountFirstDayShow(new Integer(rs.getInt("no_show_first_day_show_checked_in_dealers")));
+ report.setDealersNoShowCountSecondDayShow(new Integer(rs.getInt("no_show_second_day_show_checked_in_dealers")));
+
+
+
+ report.setAttendeesCountFirstDayHosp(new Integer(rs.getInt("first_day_hosp_checked_in_attendees")));
+ report.setAttendeesCountSecondDayHosp(new Integer(rs.getInt("second_day_hosp_checked_in_attendees")));
+
+ report.setAttendeesNoShowCountFirstDayHosp(new Integer(rs.getInt("no_show_first_day_hosp_checked_in_attendees")));
+ report.setAttendeesNoShowCountSecondDayHosp(new Integer(rs.getInt("no_show_second_day_hosp_checked_in_attendees")));
+
+
+
+ report.setDealersCountFirstDayHosp(new Integer(rs.getInt("first_day_hosp_checked_in_dealers")));
+ report.setDealersCountSecondDayHosp(new Integer(rs.getInt("second_day_hosp_checked_in_dealers")));
+
+ report.setDealersNoShowCountFirstDayHosp(new Integer(rs.getInt("no_show_first_day_hosp_checked_in_dealers")));
+ report.setDealersNoShowCountSecondDayHosp(new Integer(rs.getInt("no_show_second_day_hosp_checked_in_dealers")));
+ }
+
+ } catch (SQLException sqlE) {
+ log.error("SQLException while getting show totals", sqlE);
+ throw new DAOException("SQLException while getting show totals " + sqlE.getMessage());
+ } catch (Exception e) {
+ log.error("Exception while getting show totals", e);
+ throw new DAOException("Exception while getting show totals" + e.getMessage());
+ } finally {
+ try {
+ con.close();
+ } catch (SQLException sq) {
+ }
+ }
+ return report;
+ }
+
+ private final static String QUERY = "\n" +
+ "DECLARE @eventId INT = ? \n" +
+ "SELECT * \n" +
+ "FROM\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 = @eventId ) a CROSS \n" +
+ "JOIN\n" +
+ " (\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 = @eventId \n" +
+ " ) b CROSS \n" +
+ "JOIN\n" +
+ " (\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 = @eventId \n" +
+ " ) c CROSS \n" +
+ "JOIN\n" +
+ " (\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 = @eventId \n" +
+ " ) d CROSS \n" +
+ "JOIN\n" +
+ " (\n" +
+ " SELECT\n" +
+ " no_show_first_day_show_checked_in_attendees = COUNT(attending_show) ,\n" +
+ " no_show_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 = 'N' \n" +
+ " WHERE\n" +
+ " e.event_id = @eventId \n" +
+ " ) e CROSS \n" +
+ "JOIN\n" +
+ " (\n" +
+ " SELECT\n" +
+ " no_show_second_day_show_checked_in_attendees = COUNT(attending_show) ,\n" +
+ " no_show_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 = 'N' \n" +
+ " WHERE\n" +
+ " e.event_id = @eventId \n" +
+ " ) f CROSS \n" +
+ "JOIN\n" +
+ " (\n" +
+ " SELECT\n" +
+ " no_show_first_day_hosp_checked_in_attendees = COUNT(attending_show) ,\n" +
+ " no_show_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 = 'N' \n" +
+ " WHERE\n" +
+ " e.event_id = @eventId \n" +
+ " ) g CROSS \n" +
+ "JOIN\n" +
+ " (\n" +
+ " SELECT\n" +
+ " no_show_second_day_hosp_checked_in_attendees = COUNT(attending_show) ,\n" +
+ " no_show_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 = 'N' \n" +
+ " WHERE\n" +
+ " e.event_id = @eventId \n" +
+ " ) h ";
}
Index: trunk/WEB-INF/src/com/showcase/model/CompleteReportObject.java
===================================================================
diff -u -r233 -r236
--- trunk/WEB-INF/src/com/showcase/model/CompleteReportObject.java (.../CompleteReportObject.java) (revision 233)
+++ trunk/WEB-INF/src/com/showcase/model/CompleteReportObject.java (.../CompleteReportObject.java) (revision 236)
@@ -25,6 +25,33 @@
private boolean attendedHospSecondDay;
+ private int attendeesCountFirstDayShow;
+ private int attendeesCountSecondDayShow;
+
+ private int attendeesNoShowCountFirstDayShow;
+ private int attendeesNoShowCountSecondDayShow;
+
+ private int attendeesCountFirstDayHosp;
+ private int attendeesCountSecondDayHosp;
+
+ private int attendeesNoShowCountFirstDayHosp;
+ private int attendeesNoShowCountSecondDayHosp;
+
+ private int dealersCountFirstDayShow;
+ private int dealersCountSecondDayShow;
+
+ private int dealersNoShowCountFirstDayShow;
+ private int dealersNoShowCountSecondDayShow;
+
+ private int dealersCountFirstDayHosp;
+ private int dealersCountSecondDayHosp;
+
+ private int dealersNoShowCountFirstDayHosp;
+ private int dealersNoShowCountSecondDayHosp;
+
+
+
+
private int noOfShows;
private int noOfDealers;
@@ -77,6 +104,135 @@
this.attendedHospSecondDay = attendedHospSecondDay;
}
+
+ public int getAttendeesCountFirstDayShow() {
+ return attendeesCountFirstDayShow;
+ }
+
+ public void setAttendeesCountFirstDayShow(int attendeesCountFirstDayShow) {
+ this.attendeesCountFirstDayShow = attendeesCountFirstDayShow;
+ }
+
+ public int getAttendeesCountSecondDayShow() {
+ return attendeesCountSecondDayShow;
+ }
+
+ public void setAttendeesCountSecondDayShow(int attendeesCountSecondDayShow) {
+ this.attendeesCountSecondDayShow = attendeesCountSecondDayShow;
+ }
+
+ public int getAttendeesNoShowCountFirstDayShow() {
+ return attendeesNoShowCountFirstDayShow;
+ }
+
+ public void setAttendeesNoShowCountFirstDayShow(int attendeesNoShowCountFirstDayShow) {
+ this.attendeesNoShowCountFirstDayShow = attendeesNoShowCountFirstDayShow;
+ }
+
+ public int getAttendeesNoShowCountSecondDayShow() {
+ return attendeesNoShowCountSecondDayShow;
+ }
+
+ public void setAttendeesNoShowCountSecondDayShow(int attendeesNoShowCountSecondDayShow) {
+ this.attendeesNoShowCountSecondDayShow = attendeesNoShowCountSecondDayShow;
+ }
+
+ public int getAttendeesCountFirstDayHosp() {
+ return attendeesCountFirstDayHosp;
+ }
+
+ public void setAttendeesCountFirstDayHosp(int attendeesCountFirstDayHosp) {
+ this.attendeesCountFirstDayHosp = attendeesCountFirstDayHosp;
+ }
+
+ public int getAttendeesCountSecondDayHosp() {
+ return attendeesCountSecondDayHosp;
+ }
+
+ public void setAttendeesCountSecondDayHosp(int attendeesCountSecondDayHosp) {
+ this.attendeesCountSecondDayHosp = attendeesCountSecondDayHosp;
+ }
+
+ public int getAttendeesNoShowCountFirstDayHosp() {
+ return attendeesNoShowCountFirstDayHosp;
+ }
+
+ public void setAttendeesNoShowCountFirstDayHosp(int attendeesNoShowCountFirstDayHosp) {
+ this.attendeesNoShowCountFirstDayHosp = attendeesNoShowCountFirstDayHosp;
+ }
+
+ public int getAttendeesNoShowCountSecondDayHosp() {
+ return attendeesNoShowCountSecondDayHosp;
+ }
+
+ public void setAttendeesNoShowCountSecondDayHosp(int attendeesNoShowCountSecondDayHosp) {
+ this.attendeesNoShowCountSecondDayHosp = attendeesNoShowCountSecondDayHosp;
+ }
+
+ public int getDealersCountFirstDayShow() {
+ return dealersCountFirstDayShow;
+ }
+
+ public void setDealersCountFirstDayShow(int dealersCountFirstDayShow) {
+ this.dealersCountFirstDayShow = dealersCountFirstDayShow;
+ }
+
+ public int getDealersCountSecondDayShow() {
+ return dealersCountSecondDayShow;
+ }
+
+ public void setDealersCountSecondDayShow(int dealersCountSecondDayShow) {
+ this.dealersCountSecondDayShow = dealersCountSecondDayShow;
+ }
+
+ public int getDealersNoShowCountFirstDayShow() {
+ return dealersNoShowCountFirstDayShow;
+ }
+
+ public void setDealersNoShowCountFirstDayShow(int dealersNoShowCountFirstDayShow) {
+ this.dealersNoShowCountFirstDayShow = dealersNoShowCountFirstDayShow;
+ }
+
+ public int getDealersNoShowCountSecondDayShow() {
+ return dealersNoShowCountSecondDayShow;
+ }
+
+ public void setDealersNoShowCountSecondDayShow(int dealersNoShowCountSecondDayShow) {
+ this.dealersNoShowCountSecondDayShow = dealersNoShowCountSecondDayShow;
+ }
+
+ public int getDealersCountFirstDayHosp() {
+ return dealersCountFirstDayHosp;
+ }
+
+ public void setDealersCountFirstDayHosp(int dealersCountFirstDayHosp) {
+ this.dealersCountFirstDayHosp = dealersCountFirstDayHosp;
+ }
+
+ public int getDealersCountSecondDayHosp() {
+ return dealersCountSecondDayHosp;
+ }
+
+ public void setDealersCountSecondDayHosp(int dealersCountSecondDayHosp) {
+ this.dealersCountSecondDayHosp = dealersCountSecondDayHosp;
+ }
+
+ public int getDealersNoShowCountFirstDayHosp() {
+ return dealersNoShowCountFirstDayHosp;
+ }
+
+ public void setDealersNoShowCountFirstDayHosp(int dealersNoShowCountFirstDayHosp) {
+ this.dealersNoShowCountFirstDayHosp = dealersNoShowCountFirstDayHosp;
+ }
+
+ public int getDealersNoShowCountSecondDayHosp() {
+ return dealersNoShowCountSecondDayHosp;
+ }
+
+ public void setDealersNoShowCountSecondDayHosp(int dealersNoShowCountSecondDayHosp) {
+ this.dealersNoShowCountSecondDayHosp = dealersNoShowCountSecondDayHosp;
+ }
+
/**
* @return Returns the attendeeName.
*/
Index: trunk/ver3/completeReport.jsp
===================================================================
diff -u -r233 -r236
--- trunk/ver3/completeReport.jsp (.../completeReport.jsp) (revision 233)
+++ trunk/ver3/completeReport.jsp (.../completeReport.jsp) (revision 236)
@@ -132,14 +132,57 @@
-
- | Total Attendees: |
- | Total Dealers: |
+
+ |
+ Day 1 |
+ Day 2 |
+
+
+
+ | Attendees |
+ |
+ |
+
+
+ | Dealers that attended |
+ |
+ |
+
- | Total No-Shows: |
- | Total Dealers: |
+
+ | Attendees Not Showed Up |
+ |
+ |
+
+
+ | Dealers Not Showed Up |
+ |
+ |
+
+
+
+
+ | Attendees |
+ |
+ |
+
+
+ | Dealers that attended |
+ |
+ |
+
+
+ | Attendees Not Showed Up |
+ |
+ |
+
+
+ | Dealers Not Showed Up |
+ |
+ |
+
+
-
| Attended: () |
Index: trunk/ver3/summaryReport.jsp
===================================================================
diff -u -r233 -r236
--- trunk/ver3/summaryReport.jsp (.../summaryReport.jsp) (revision 233)
+++ trunk/ver3/summaryReport.jsp (.../summaryReport.jsp) (revision 236)
@@ -105,7 +105,6 @@
| Attendees |
- |
|
|