Fisheye: Tag 241 refers to a dead (removed) revision in file `trunk/WEB-INF/src/com/showcase/sql/SummaryReportDAOImpl.java'.
Fisheye: No comparison available.  Pass `N' to diff?
Fisheye: Tag 241 refers to a dead (removed) revision in file `trunk/WEB-INF/src/com/showcase/sql/SummaryRptDAOImpl.java'.
Fisheye: No comparison available.  Pass `N' to diff?
Index: trunk/WEB-INF/src/com/showcase/sql/ShowCaseEmailDAOImpl.java
===================================================================
diff -u -r208 -r241
--- trunk/WEB-INF/src/com/showcase/sql/ShowCaseEmailDAOImpl.java	(.../ShowCaseEmailDAOImpl.java)	(revision 208)
+++ trunk/WEB-INF/src/com/showcase/sql/ShowCaseEmailDAOImpl.java	(.../ShowCaseEmailDAOImpl.java)	(revision 241)
@@ -20,6 +20,198 @@
 public class ShowCaseEmailDAOImpl extends BaseDAO implements ShowCaseEmailDAO {
 
 	private static final Log log = LogFactory.getLog(ShowCaseEmailDAOImpl.class);
+
+	private static final String ATTENDEE_FINAL_EMAIL_LIST = "\n" +
+			"DECLARE @event_id INT = ?\n" +
+			"DECLARE @attendee_table table (\n" +
+			"       salesman varchar(10)\n" +
+			"     , region_code varchar(6)\n" +
+			"     , dealer_id char(6)\n" +
+			"     , given_dealer_name varchar(50)\n" +
+			"     , attendee_name varchar(100)\n" +
+			"     , title varchar(25)\n" +
+			"     , mega_dealer_id char(6)\n" +
+			"     , rep_id char(6)\n" +
+			"     , manager_id char(6)\n" +
+			")\n" +
+			"\n" +
+			"INSERT INTO @attendee_table (\n" +
+			"       salesman\n" +
+			"     , region_code\n" +
+			"     , dealer_id\n" +
+			"     , given_dealer_name\n" +
+			"     , attendee_name\n" +
+			"     , title\n" +
+			"     , mega_dealer_id\n" +
+			"     , rep_id\n" +
+			"     , manager_id\n" +
+			")\n" +
+			"SELECT \n" +
+			"       f.salesman\n" +
+			"     , f.region_code\n" +
+			"     , f.dealer_id\n" +
+			"     , f.given_dealer_name\n" +
+			"     , f.attendee_name\n" +
+			"     , f.title\n" +
+			"     , f.mega_dealer_id\n" +
+			"     , f.rep_id\n" +
+			"     , f.manager_id\n" +
+			"FROM (\n" +
+			"    SELECT\n" +
+			"        dl.salesman\n" +
+			"        , dl.region_code\n" +
+			"        , dl.dealer_id\n" +
+			"        , dl.given_dealer_name\n" +
+			"        , a.attendee_name\n" +
+			"        , title = CASE WHEN a.attended_show = 'YES' THEN a.role ELSE dl.dealerType END\n" +
+			"        , dl.mega_dealer_id\n" +
+			"        , dl.rep_id\n" +
+			"        , dl.manager_id\n" +
+			"    FROM (\n" +
+			"            -- DEALERS WHO ATTENDED THE SHOW (MAY INCLUDE REGULAR, SISTER, MEGA DEALERS)\n" +
+			"            SELECT ea.dealer_id, ea.attendee_name, r.role, attended_show = 'YES'\n" +
+			"            FROM dbo.attendees ea\n" +
+			"                LEFT OUTER JOIN dbo.roles r\n" +
+			"                    ON r.role_id = ea.role_id\n" +
+			"            WHERE ea.event_id = @event_id\n" +
+			"                AND ea.status = 'A'\n" +
+			"                AND (ea.checked_in = 'Y' OR ea.checked_in_2nd_day = 'Y')\n" +
+			"\n" +
+			"            UNION ALL\n" +
+			"\n" +
+			"            SELECT *\n" +
+			"            FROM (\n" +
+			"                    -- SISTER STORES OF A MEGA DEALER WHO ATTENDED THE SHOW\n" +
+			"                    SELECT --DISTINCT\n" +
+			"                        md.dealer_id\n" +
+			"                        , attendee_name = 'N/A'\n" +
+			"                        , role = NULL\n" +
+			"                        , attended_show = 'NO'\n" +
+			"                    FROM dbo.attendees a\n" +
+			"                        INNER JOIN dbo.mega_dealers md\n" +
+			"                            ON md.mega_dealer_id = a.dealer_id\n" +
+			"                    WHERE a.event_id = @event_id\n" +
+			"                        AND a.status = 'A'\n" +
+			"                        AND (a.checked_in = 'Y' OR a.checked_in_2nd_day = 'Y')\n" +
+			"                        AND NOT EXISTS (\n" +
+			"                                    SELECT *\n" +
+			"                                    FROM dbo.attendees a\n" +
+			"                                    WHERE a.event_id = @event_id\n" +
+			"                                        AND a.dealer_id = md.dealer_id\n" +
+			"                                        AND a.status = 'A'\n" +
+			"                                        AND (a.checked_in = 'Y' OR a.checked_in_2nd_day = 'Y')\n" +
+			"                                    )\n" +
+			"\n" +
+			"                    UNION\n" +
+			"\n" +
+			"                    -- MEGA DEALERS OF THE SISTER STORES WHO ATTENDED THE SHOW\n" +
+			"                    SELECT --DISTINCT\n" +
+			"                        md.mega_dealer_id\n" +
+			"                        , attendee_name = 'N/A'\n" +
+			"                        , role = NULL\n" +
+			"                        , attended_show = 'NO'\n" +
+			"                    FROM dbo.attendees a\n" +
+			"                        INNER JOIN dbo.mega_dealers md\n" +
+			"                            ON md.dealer_id = a.dealer_id\n" +
+			"                    WHERE a.event_id = @event_id\n" +
+			"                        AND a.status = 'A'\n" +
+			"                        AND (a.checked_in = 'Y' OR a.checked_in_2nd_day = 'Y')\n" +
+			"                        AND NOT EXISTS (\n" +
+			"                                    SELECT *\n" +
+			"                                    FROM dbo.attendees a\n" +
+			"                                    WHERE a.event_id = @event_id\n" +
+			"                                        AND a.dealer_id = md.mega_dealer_id\n" +
+			"                                        AND a.status = 'A'\n" +
+			"                                        AND (a.checked_in = 'Y' OR a.checked_in_2nd_day = 'Y')\n" +
+			"                                    )\n" +
+			"\n" +
+			"                    UNION\n" +
+			"\n" +
+			"                    -- SIBLINGS STORES WHO ATTENDED THE SHOW\n" +
+			"                    SELECT --DISTINCT \n" +
+			"                        md1.dealer_id\n" +
+			"                        , attendee_name = 'N/A'\n" +
+			"                        , role = NULL\n" +
+			"                        , attended_show = 'NO'\n" +
+			"                    FROM dbo.attendees a\n" +
+			"                        INNER JOIN dbo.mega_dealers md\n" +
+			"                            ON md.dealer_id = a.dealer_id\n" +
+			"                        INNER JOIN dbo.mega_dealers md1\n" +
+			"                            ON md.mega_dealer_id = md1.mega_dealer_id\n" +
+			"                    WHERE a.event_id = @event_id\n" +
+			"                        AND a.status = 'A'\n" +
+			"                        AND (a.checked_in = 'Y' OR a.checked_in_2nd_day = 'Y')\n" +
+			"                        AND NOT EXISTS (\n" +
+			"                                    SELECT *\n" +
+			"                                    FROM dbo.attendees a\n" +
+			"                                    WHERE a.event_id = @event_id\n" +
+			"                                        AND a.dealer_id = md1.dealer_id\n" +
+			"                                        AND a.status = 'A'\n" +
+			"                                        AND (a.checked_in = 'Y' OR a.checked_in_2nd_day = 'Y')\n" +
+			"                                    )\n" +
+			"                ) b\n" +
+			"        ) a\n" +
+			"        INNER JOIN dbo.vwDealerList dl\n" +
+			"                ON dl.dealer_id = a.dealer_id\n" +
+			"    ) f\n" +
+			"\n" +
+			"/*********************************************************\n" +
+			"* UPDATE REGIONAL MANAGER REP ID FOR \"Z\" DEALERS\n" +
+			"**********************************************************/\n" +
+			"UPDATE at SET manager_id = mr.manager_id\n" +
+			"               , rep_id = r.rep_id\n" +
+			"FROM @attendee_table at\n" +
+			"     INNER JOIN dbo.reps r\n" +
+			"          ON r.salesman_id = SUBSTRING(at.salesman, 1, LEN(at.salesman) - 1)\n" +
+			"     INNER JOIN dbo.manager_regions mr\n" +
+			"          ON mr.region_code = r.region_code\n" +
+			"     INNER JOIN dbo.rep_email re\n" +
+			"          ON re.rep_id = mr.manager_id\n" +
+			"WHERE at.salesman LIKE '%[A-Z]'\n" +
+			"\n" +
+			"\n" +
+			"/*********************************************************\n" +
+			"* BUILD REGIONAL MANAGER EMAIL LIST\n" +
+			"**********************************************************/\n" +
+			" -- LEFT OUTER ON rep_email TO MAKE IT WORK ON THE MIDDLE TIER CODE\n" +
+			"SELECT DISTINCT\n" +
+			"       at.region_code\n" +
+			"     , at.manager_id\n" +
+			"     , manager_name = [dbo].[udf_TitleCase](m.manager_name)\n" +
+			"     , email_address = LOWER(RTRIM(re.email_address))\n" +
+			"FROM @attendee_table at\n" +
+			"     LEFT OUTER JOIN rep_email re \n" +
+			"          ON re.rep_id = at.manager_id\n" +
+			"     INNER JOIN dbo.managers m\n" +
+			"          ON m.manager_id = at.manager_id\n" +
+			"WHERE at.region_code IS NOT NULL\n" +
+			"ORDER BY 1\n" +
+			"\n" +
+			"/*********************************************************\n" +
+			"* BUILD REP EMAIL LIST\n" +
+			"**********************************************************/\n" +
+			" -- LEFT OUTER ON rep_email TO MAKE IT WORK ON THE MIDDLE TIER CODE\n" +
+			"SELECT DISTINCT\n" +
+			"       at.region_code\n" +
+			"     , at.manager_id\n" +
+			"     , at.rep_id\n" +
+			"     , rep_name = [dbo].[udf_TitleCase](r.rep_name)\n" +
+			"     , email_address = LOWER(RTRIM(re.email_address))\n" +
+			"FROM @attendee_table at\n" +
+			"     INNER JOIN reps r \n" +
+			"          ON r.rep_id = at.rep_id \n" +
+			"               -- AND r.[status] = 'A' -- IF WE HAVE THIS CONDITION, WE HAVE MISMATCH IN RECORDS GENERATED IN ATTENDEE LIST AND REP EMAIL LIST.\n" +
+			"     LEFT OUTER JOIN rep_email re\n" +
+			"          ON re.rep_id = r.rep_id\n" +
+			"ORDER BY 1, 2, 3\n" +
+			"\n" +
+			"\n" +
+			"/*********************************************************\n" +
+			"* BUILD ATTENDEE LIST\n" +
+			"**********************************************************/\n" +
+			"SELECT * \n" +
+			"FROM @attendee_table\n" +
+			"ORDER BY 2, 3";
 	
 	public HashMap<String, RegionInfo> getShowCaseEmailRegionsList(int eventId, boolean preRegistration) throws DAOException {
 		Connection con = null;
@@ -33,7 +225,7 @@
 			if(preRegistration) {
 				cstmt = con.prepareCall("{call spGetDealerAttendeePreRegisteredEmailList (?) }");
 			} else {
-				cstmt = con.prepareCall("{call spGetDealerAttendeeFinalEmailList (?) }");
+				cstmt = con.prepareCall(ATTENDEE_FINAL_EMAIL_LIST);
 			}
 			cstmt.setInt(1, eventId);
 			cstmt.execute();