Index: trunk/WEB-INF/src/com/showcase/sql/AllAttendeesSignedUpDAOImpl.java =================================================================== diff -u -r223 -r235 --- trunk/WEB-INF/src/com/showcase/sql/AllAttendeesSignedUpDAOImpl.java (.../AllAttendeesSignedUpDAOImpl.java) (revision 223) +++ trunk/WEB-INF/src/com/showcase/sql/AllAttendeesSignedUpDAOImpl.java (.../AllAttendeesSignedUpDAOImpl.java) (revision 235) @@ -1,136 +1,185 @@ package com.showcase.sql; -import java.sql.CallableStatement; -import java.sql.Connection; -import java.sql.PreparedStatement; -import java.sql.ResultSet; -import java.sql.SQLException; -import java.util.ArrayList; -import java.util.List; - -import org.apache.commons.logging.Log; -import org.apache.commons.logging.LogFactory; - import com.showcase.dao.AllAttendeesSignedUpDAO; import com.showcase.exception.DAOException; import com.showcase.model.AllAttendeesObject; import com.showcase.util.BaseDAO; +import org.apache.commons.logging.Log; +import org.apache.commons.logging.LogFactory; + +import java.sql.*; +import java.util.ArrayList; +import java.util.List; + @SuppressWarnings("unchecked") -public class AllAttendeesSignedUpDAOImpl extends BaseDAO implements AllAttendeesSignedUpDAO{ +public class AllAttendeesSignedUpDAOImpl extends BaseDAO implements AllAttendeesSignedUpDAO { - private static final Log log = LogFactory.getLog(AllAttendeesSignedUpDAOImpl.class); - - public AllAttendeesSignedUpDAOImpl(){ - - } + private static final Log log = LogFactory.getLog(AllAttendeesSignedUpDAOImpl.class); - public AllAttendeesObject[] getAllAttendeesSignedUpList(int event_id) throws DAOException{ - log.info("getAllAttendeesSignedUpList"); - CallableStatement cs; - PreparedStatement pstmt = null; - ResultSet rs=null; - Connection con=null; - AllAttendeesObject report = null; - List list = new ArrayList(); - List dealerIdList = new ArrayList(); - String dealerId = null; - try { - if(log.isDebugEnabled()) - log.debug("Getting all attendees signed up"); - con = getDBConnection(); - cs = con.prepareCall("{call dbo.spGetAllAttendeesSignedUpList(?)}"); - cs.setInt(1, event_id); - rs=cs.executeQuery(); - + private static final String ALL_ATTENDEES_SQL = "\n" + + "SELECT\n" + + " DISTINCT a.dealer_id ,\n" + + " r.role ,\n" + + " dl.given_dealer_name ,\n" + + " dl.salesman ,\n" + + " dl.second_salesman ,\n" + + " dl.address1 ,\n" + + " dl.address2 ,\n" + + " dl.city ,\n" + + " dl.state ,\n" + + " a.attendee_id ,\n" + + " a.attending_show ,\n" + + " a.attending_hosp ,\n" + + " a.attendee_name ,\n" + + " a.attendee_email ,\n" + + " a.checked_in,\n" + + " a.role_id ,\n" + + " a.checked_in_2nd_day ,\n" + + " dl.zip_code ,\n" + + " a.attending_meet_and_greet ,\n" + + " reps.region_code ,\n" + + " rd.relationship ,\n" + + " md.mega_dealer_id ,\n" + + " dl.level ,\n" + + " a.ticket_id ,\n" + + " a.has_ticket ,\n" + + " a.checked_in_hosp_day1 ,\n" + + " a.checked_in_hosp_day2 \n" + + "FROM\n" + + " attendees a \n" + + "LEFT OUTER JOIN\n" + + " dealers dl \n" + + " ON dl.dealer_id = a.dealer_id \n" + + "LEFT OUTER JOIN\n" + + " roles r \n" + + " ON a.role_id = r.role_id \n" + + "LEFT OUTER JOIN\n" + + " rep_dealers rd \n" + + " ON dl.dealer_id = rd.dealer_id \n" + + "LEFT OUTER JOIN\n" + + " reps \n" + + " ON reps.rep_id = rd.rep_id \n" + + "LEFT OUTER JOIN\n" + + " mega_dealers md \n" + + " ON dl.dealer_id = md.dealer_id \n" + + " OR dl.dealer_id = md.mega_dealer_id \n" + + "WHERE\n" + + " a.event_id = ? \n" + + " AND a.status != 'D' \n" + + " AND reps.status != 'D' \n" + + " AND (\n" + + " rd.relationship = 'P' \n" + + " OR rd.relationship IS NULL\n" + + " ) \n" + + "ORDER BY\n" + + " md.mega_dealer_id,\n" + + " a.dealer_id,\n" + + " a.attendee_name"; - while (rs.next()) - { - report = extractAttendeesFromResultSet(rs); - dealerId = report.getDealerId(); - if(dealerIdList.contains(dealerId)){ - // do not add notes - }else{ - // add notes - report = addDealerEventNoteList(event_id, report, dealerIdList); - dealerIdList.add(dealerId); - } - list.add(report); - } - - } catch (SQLException sqlE) - { - log.error("SQLException while getting all attendees signedup list", sqlE); - throw new DAOException("SQLException while getting all attendees signedup list " + sqlE.getMessage()); - } + public AllAttendeesSignedUpDAOImpl() { - catch (Exception e) - { - log.error("Exception while getting all attendees signedup list", e); - throw new DAOException("Exception while getting all attendees signedup list" + e.getMessage()); - } - finally - { - try { con.close(); } catch(SQLException sq) { } - } - - AllAttendeesObject[] reports = null; - if (list.size() > 0) - { - reports = (AllAttendeesObject[]) list.toArray(new AllAttendeesObject[list.size()]); - } - - return reports; - } - - protected AllAttendeesObject extractAttendeesFromResultSet(ResultSet rs) throws SQLException{ - - AllAttendeesObject report = new AllAttendeesObject(); - report.setDealerId(rs.getString("dealer_id")); - report.setRole(rs.getString("role")); - report.setGivenDealerName(rs.getString("given_dealer_name")); - report.setSalesman(rs.getString("salesman")); - report.setSecondSalesman(rs.getString("second_salesman")); - report.setAddress1(rs.getString("address1")); - report.setAddress2(rs.getString("address2")); - report.setCity(rs.getString("city")); - report.setState(rs.getString("state")); - report.setAttendeeId(rs.getInt("attendee_id")); - report.setAttendingShow(rs.getString("attending_show")); - report.setAttendingHosp(rs.getString("attending_hosp")); - report.setAttendeeName(rs.getString("attendee_name")); - report.setAttendeeEmail(rs.getString("attendee_email")); - report.setCheckIn(rs.getString("checked_in")); - report.setCheckIn2ndDay(rs.getString("checked_in_2nd_day")); - report.setRoleId(rs.getInt("role_id")); - report.setZipCode(rs.getString("zip_code")); - report.setAttendingMeetAndGreet(rs.getString("attending_meet_and_greet")); - report.setRegionCode(rs.getString("region_code")); - report.setRelationship(rs.getString("relationship")); - report.setMegaDealerId(rs.getString("mega_dealer_id")); - report.setDealerLevel(rs.getString("level")); - report.setTicketId(rs.getString("ticket_id")); - report.setHasTicket(rs.getString("has_ticket")); - report.setCheckedInHospDay1(rs.getString("checked_in_hosp_day1")); - report.setCheckedInHospDay2(rs.getString("checked_in_hosp_day2")); - - return report; - } - - public AllAttendeesObject addDealerEventNoteList(int event_id, AllAttendeesObject report,List dealerIdList) throws DAOException{ - DealerAttendeesDAOImpl dealerAttendeesDAOImpl = new DealerAttendeesDAOImpl(); - String dealer_id = report.getDealerId(); - List dealerEventNoteList = null; - - try{ - dealerEventNoteList = dealerAttendeesDAOImpl.getDealerEventNoteList(event_id, dealer_id); - //log.info("dealerEventNoteList size=>"+dealerEventNoteList.size()); - report.setDealerEventNoteList(dealerEventNoteList); - }catch (Exception e) { - log.error("Exception while adding Dealer Event Note List", e); - throw new DAOException("Exception while adding Dealer Event Note List" + e.getMessage()); - } - return report; - } - - + } + + public AllAttendeesObject[] getAllAttendeesSignedUpList(int event_id) throws DAOException { + log.info("getAllAttendeesSignedUpList"); + CallableStatement cs; + PreparedStatement pstmt = null; + ResultSet rs = null; + Connection con = null; + AllAttendeesObject report = null; + List list = new ArrayList(); + List dealerIdList = new ArrayList(); + String dealerId = null; + try { + if (log.isDebugEnabled()) + log.debug("Getting all attendees signed up"); + con = getDBConnection(); + cs = con.prepareCall(ALL_ATTENDEES_SQL); + cs.setInt(1, event_id); + rs = cs.executeQuery(); + + + while (rs.next()) { + report = extractAttendeesFromResultSet(rs); + dealerId = report.getDealerId(); + if (dealerIdList.contains(dealerId)) { + // do not add notes + } else { + // add notes + report = addDealerEventNoteList(event_id, report, dealerIdList); + dealerIdList.add(dealerId); + } + list.add(report); + } + + } catch (SQLException sqlE) { + log.error("SQLException while getting all attendees signedup list", sqlE); + throw new DAOException("SQLException while getting all attendees signedup list " + sqlE.getMessage()); + } catch (Exception e) { + log.error("Exception while getting all attendees signedup list", e); + throw new DAOException("Exception while getting all attendees signedup list" + e.getMessage()); + } finally { + try { + con.close(); + } catch (SQLException sq) { + } + } + + AllAttendeesObject[] reports = null; + if (list.size() > 0) { + reports = (AllAttendeesObject[]) list.toArray(new AllAttendeesObject[list.size()]); + } + + return reports; + } + + protected AllAttendeesObject extractAttendeesFromResultSet(ResultSet rs) throws SQLException { + + AllAttendeesObject report = new AllAttendeesObject(); + report.setDealerId(rs.getString("dealer_id")); + report.setRole(rs.getString("role")); + report.setGivenDealerName(rs.getString("given_dealer_name")); + report.setSalesman(rs.getString("salesman")); + report.setSecondSalesman(rs.getString("second_salesman")); + report.setAddress1(rs.getString("address1")); + report.setAddress2(rs.getString("address2")); + report.setCity(rs.getString("city")); + report.setState(rs.getString("state")); + report.setAttendeeId(rs.getInt("attendee_id")); + report.setAttendingShow(rs.getString("attending_show")); + report.setAttendingHosp(rs.getString("attending_hosp")); + report.setAttendeeName(rs.getString("attendee_name")); + report.setAttendeeEmail(rs.getString("attendee_email")); + report.setCheckIn(rs.getString("checked_in")); + report.setCheckIn2ndDay(rs.getString("checked_in_2nd_day")); + report.setRoleId(rs.getInt("role_id")); + report.setZipCode(rs.getString("zip_code")); + report.setAttendingMeetAndGreet(rs.getString("attending_meet_and_greet")); + report.setRegionCode(rs.getString("region_code")); + report.setRelationship(rs.getString("relationship")); + report.setMegaDealerId(rs.getString("mega_dealer_id")); + report.setDealerLevel(rs.getString("level")); + report.setTicketId(rs.getString("ticket_id")); + report.setHasTicket(rs.getString("has_ticket")); + report.setCheckedInHospDay1(rs.getString("checked_in_hosp_day1")); + report.setCheckedInHospDay2(rs.getString("checked_in_hosp_day2")); + + return report; + } + + public AllAttendeesObject addDealerEventNoteList(int event_id, AllAttendeesObject report, List dealerIdList) throws DAOException { + DealerAttendeesDAOImpl dealerAttendeesDAOImpl = new DealerAttendeesDAOImpl(); + String dealer_id = report.getDealerId(); + List dealerEventNoteList = null; + + try { + dealerEventNoteList = dealerAttendeesDAOImpl.getDealerEventNoteList(event_id, dealer_id); + //log.info("dealerEventNoteList size=>"+dealerEventNoteList.size()); + report.setDealerEventNoteList(dealerEventNoteList); + } catch (Exception e) { + log.error("Exception while adding Dealer Event Note List", e); + throw new DAOException("Exception while adding Dealer Event Note List" + e.getMessage()); + } + return report; + } } Index: trunk/WEB-INF/src/com/showcase/sql/SumRepDAOImpl.java =================================================================== diff -u -r233 -r235 --- trunk/WEB-INF/src/com/showcase/sql/SumRepDAOImpl.java (.../SumRepDAOImpl.java) (revision 233) +++ trunk/WEB-INF/src/com/showcase/sql/SumRepDAOImpl.java (.../SumRepDAOImpl.java) (revision 235) @@ -143,6 +143,9 @@ 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"))); + + + summaryReport.put("event_id", eventId); } } catch (SQLException sqlE) {