Discover Exceptional Books

SELECT	a.bot_number
        FROM	bots a
        WHERE	charindex(a.bot_name, ?) > 0
		OR a.bot_ip = ?

select 	a.*, 
    		b.member_account_type_name, b.member_account_type_charge, b.member_account_type_charge_frequency,  b.member_account_type_months, 
            c.member_chargetype_name, c.member_chargetype_number, a.member_classification_number, a.member_book_format
	
    from members a, member_account_types b, member_chargetype c
	
    where a.member_account_type_number = b.member_account_type_number
	and a.payment_method_id = c.member_chargetype_number
	and a.member_number = ?

SELECT	viewby, section
    FROM	adsystem 
    WHERE	active  = 1 
    AND		start_date <= GETDATE()
    AND 	(viewby = ? OR viewby = 'all')

select		top 1 ezine_number, ezine_dt, ezine_image, ezine_brief_description, ezine_introduction
from		ezines
where		ezine_active_flag = 1
and 		ezine_type_number = 4
and			ezine_dt < getdate()
order by 	ezine_dt DESC

SELECT		a.wordplay_number, a.wordplay_dt, a.wordplay_name, a.wordplay_intro_text, b.wordplay_puzzle_number, b.wordplay_puzzle_question
    FROM		wordplays a 
	INNER JOIN	wordplay_puzzle_mapping c on c.wordplay_number = a.wordplay_number
	INNER JOIN	wordplay_puzzles b on b.wordplay_puzzle_number = c.wordplay_puzzle_number
	WHERE		a.wordplay_number = (	select top 1 a.wordplay_number
                                from wordplays a, wordplay_puzzles b, wordplay_puzzle_mapping c
                                where a.wordplay_number = c.wordplay_number
                                and b.wordplay_puzzle_number = c.wordplay_puzzle_number
                                and a.wordplay_dt < getdate()
                                and a.wordplay_expiration_dt > dateadd(day, -1, getdate())
                                order by a.wordplay_dt asc
                                )

SELECT 	TOP 1 	x.competition_number, x.bb_briefs_flag
	from			competitions x
	
		WHERE	x.competition_dt <= getdate()
		AND		x.competition_expiration_dt > dateadd(d,-1,getdate())

SELECT 	x.competition_number, x.book_number, x.competition_teaser_title, x.competition_teaser_description, x.competition_intro_text, x.competition_expiration_dt, x.bb_briefs_flag, x.competition_full_info_flag, x.competition_type, x.competition_optin,x.competition_optin_text, 
				a.book_title as "title",
				(b.author_first_name + ' ' +  b.author_middle_initial + ' ' + b.author_last_name) as "author",
	            f.edition_publish_dt AS "hardcover_publish_dt", f.edition_jacket_image as "hardcover_jacket_image",
				g.edition_publish_dt AS "paperback_publish_dt", g.edition_jacket_image as "paperback_jacket_image"
	
		FROM 		competitions x
		INNER JOIN	books a on a.book_number = x.book_number
		INNER JOIN	book_author_mapping c on c.book_number = x.book_number
		INNER JOIN	authors b on c.author_number = b.author_number
		LEFT JOIN	editions f on a.book_number = f.book_number and f.edition_paperback_flag = 0
		LEFT JOIN	editions g on a.book_number = g.book_number and g.edition_paperback_flag = 1

		WHERE		x.competition_number = ?

SELECT 	a.arc_number,a.arc_client_url,a.arc_active_dt, a.arc_off_ad_dt,
		b.ezine_preview_number, b.ezine_preview_title, b.ezine_preview_subtitle, b.ezine_preview_jacket_image, b.ezine_preview_author, b.ezine_preview_publisher, 
		b.ezine_preview_publish_dt, b.ezine_preview_jacket_desc, b.ezine_preview_number_of_pages, b.ezine_preview_bb_comments, b.ezine_preview_isbn13,
		c.ezine_preview_category_name AS "ezine_preview_category",
		(select count(*) from arc_allocator d where d.arc_number = a.arc_number and arc_allocator_review_approved_flag = 1) as "reviews",
		(select (CAST(AVG(CAST(arc_allocator_review_rating AS numeric(12,0))) AS numeric(12,0)))+
				(CASE WHEN right(CAST(AVG(CAST(arc_allocator_review_rating AS numeric(12,2))) AS numeric(12,2)),2) > 25 AND right(CAST(AVG(CAST(arc_allocator_review_rating AS numeric(12,2))) AS numeric(12,2)),2) < 50 THEN 0.5
				 WHEN right(CAST(AVG(CAST(arc_allocator_review_rating AS numeric(12,2))) AS numeric(12,2)),2) > 50 AND right(CAST(AVG(CAST(arc_allocator_review_rating AS numeric(12,2))) AS numeric(12,2)),2) < 75 THEN -0.5
				 ELSE 0
				 END)
 			from arc_allocator	where arc_number = a.arc_number and arc_allocator_review_approved_flag = 1) AS arcrating,
 			(select (CAST(AVG(CAST(arc_allocator_review_rating AS numeric(12,1))) AS numeric(12,1)))from arc_allocator where arc_number = a.arc_number and arc_allocator_review_approved_flag = 1) AS "decrating"

FROM arcs a
INNER JOIN ezine_previews b ON b.ezine_preview_number = a.ezine_preview_number
INNER JOIN ezine_preview_categories c ON b.ezine_preview_category_number = c.ezine_preview_category_number

WHERE arc_closed_flag = 1
AND arc_obc_flag = 0
AND getdate() >= arc_on_ad_dt
AND getdate() < arc_off_ad_dt

ORDER BY a.arc_on_ad_dt DESC

SELECT 		a.arc_forumidfk, a.discourse_flag, a.arc_promo_text, arc_on_ad_dt,
				b.ezine_preview_number, b.ezine_preview_title, b.ezine_preview_subtitle, b.ezine_preview_jacket_image, b.ezine_preview_author, b.ezine_preview_jacket_desc, b.ezine_preview_publisher, ezine_preview_publish_dt, ezine_preview_number_of_pages, ezine_preview_isbn, ezine_preview_short_summary,
	            b.ezine_preview_bb_author_link, ezine_preview_bb_link
	
	FROM 		arcs a
	INNER JOIN 	ezine_previews b ON b.ezine_preview_number = a.ezine_preview_number
	
	WHERE 		arc_obc_flag = 1

	AND 		getdate() < arc_on_ad_dt
	
		ORDER BY  	NEWID()

SELECT 		a.arc_forumidfk, a.discourse_flag, a.arc_promo_text,
				b.ezine_preview_number, b.ezine_preview_title, b.ezine_preview_subtitle, b.ezine_preview_jacket_image, b.ezine_preview_author, b.ezine_preview_jacket_desc, b.ezine_preview_publisher, ezine_preview_publish_dt, ezine_preview_number_of_pages,  ezine_preview_isbn, ezine_preview_short_summary,
	            b.ezine_preview_bb_author_link, ezine_preview_bb_link,
	            c.book_reading_guide
	
	FROM 		arcs a
	INNER JOIN 	ezine_previews b ON b.ezine_preview_number = a.ezine_preview_number
	LEFT JOIN  	books c on c.book_number = b.ezine_preview_bb_link
	
	WHERE 		a.arc_obc_flag = 1
	AND			a.arc_active_flag = 0
	AND 		arc_closed_flag = 1
	AND 		getdate() >= arc_on_ad_dt
	AND 		getdate() < arc_off_ad_dt
	ORDER BY  	NEWID()

SELECT 		a.arc_forumidfk, a.discourse_flag, a.arc_promo_text,
				b.ezine_preview_number, b.ezine_preview_title, b.ezine_preview_subtitle, b.ezine_preview_jacket_image, b.ezine_preview_author, b.ezine_preview_jacket_desc, b.ezine_preview_publisher, ezine_preview_publish_dt, ezine_preview_number_of_pages,  ezine_preview_isbn, ezine_preview_short_summary,
	            b.ezine_preview_bb_author_link, ezine_preview_bb_link,
	            c.book_reading_guide
	
	FROM 		arcs a
	INNER JOIN 	ezine_previews b ON b.ezine_preview_number = a.ezine_preview_number
	LEFT JOIN  	books c on c.book_number = b.ezine_preview_bb_link
	
	WHERE a.arc_number IN (select top 4 arc_number
							from		arcs
							WHERE 		arc_obc_flag = 1
							AND			arc_active_flag = 0
							AND 		arc_closed_flag = 1
							AND 		getdate() > arc_off_ad_dt
							ORDER BY	arc_on_ad_dt DESC)
	ORDER BY  	NEWID()

SELECT	top 10 a.book_number, a.editors_choice_dt,
				b.media_review_rating, b.media_review,
                c.book_title, c.book_sub_title, c.book_short_summary,book_jacket_info,
                
                   (select top 1 (dbo.WordCount(c.ezine_review) + dbo.WordCount(c.ezine_boxed_content)) from ezine_edition_mapping c INNER JOIN editions d ON (d.edition_number = c.edition_number) INNER JOIN ezines e ON e.ezine_number = c.ezine_number
		 			WHERE d.book_number = a.book_number AND e.ezine_bbsays_flag = 1 ORDER BY d.edition_publish_dt DESC, c.ezine_edition_mapping_number DESC) 
                AS  "wordc",
                
                    (select top 1 ezine_review_2 from ezine_edition_mapping c INNER JOIN editions d ON (d.edition_number = c.edition_number) INNER JOIN ezines e ON e.ezine_number = c.ezine_number
		 			WHERE d.book_number = a.book_number AND e.ezine_bbsays_flag = 1 ORDER BY d.edition_publish_dt DESC, c.ezine_edition_mapping_number DESC) 
                AS  "review",

                   (select top 1 ezine_short_title from ezine_edition_mapping c INNER JOIN editions d ON (d.edition_number = c.edition_number) INNER JOIN ezines e ON e.ezine_number = c.ezine_number
		 			WHERE d.book_number = a.book_number AND e.ezine_bbsays_flag = 1 ORDER BY d.edition_publish_dt DESC, c.ezine_edition_mapping_number DESC) 
                AS  "ezine_short_title",

                   (select top 1 ezine_boxed_content from ezine_edition_mapping c INNER JOIN editions d ON (d.edition_number = c.edition_number) INNER JOIN ezines e ON e.ezine_number = c.ezine_number
		 			WHERE d.book_number = a.book_number AND e.ezine_bbsays_flag = 1 ORDER BY d.edition_publish_dt DESC, c.ezine_edition_mapping_number DESC) 
                AS  "boxed_content",
                
                   (select top 1 ezine_boxed_content_title from ezine_edition_mapping c INNER JOIN editions d ON (d.edition_number = c.edition_number) INNER JOIN ezines e ON e.ezine_number = c.ezine_number
		 			WHERE d.book_number = a.book_number AND e.ezine_bbsays_flag = 1 ORDER BY d.edition_publish_dt DESC, c.ezine_edition_mapping_number DESC) 
                AS  "boxed_content_title",
                
                   (select top 1 edition_jacket_image from ezine_edition_mapping c INNER JOIN editions d ON (d.edition_number = c.edition_number) INNER JOIN ezines e ON e.ezine_number = c.ezine_number 
                 	WHERE d.book_number = a.book_number AND e.ezine_bbsays_flag = 1 ORDER BY d.edition_publish_dt DESC, c.ezine_edition_mapping_number DESC) 
                AS "jacket",

                   (select top 1 edition_paperback_flag from ezine_edition_mapping c INNER JOIN editions d ON (d.edition_number = c.edition_number) INNER JOIN ezines e ON e.ezine_number = c.ezine_number 
                 	WHERE d.book_number = a.book_number AND e.ezine_bbsays_flag = 1 ORDER BY d.edition_publish_dt DESC, c.ezine_edition_mapping_number DESC) 
                AS "edition_paperback_flag",
                
                   (select top 1 reviewer_number from ezine_edition_mapping c INNER JOIN editions d ON (d.edition_number = c.edition_number) INNER JOIN ezines e ON e.ezine_number = c.ezine_number 
                 	WHERE d.book_number = a.book_number AND e.ezine_bbsays_flag = 1 ORDER BY d.edition_publish_dt DESC, c.ezine_edition_mapping_number DESC) 
                AS "reviewer_number",
                
                   (select top 1 reviewer_first_name + ' ' + reviewer_last_name AS "reviewer_name" from ezine_edition_mapping c INNER JOIN editions d ON (d.edition_number = c.edition_number) INNER JOIN ezines e ON e.ezine_number = c.ezine_number INNER JOIN reviewers r on r.reviewer_number = c.reviewer_number
		 			WHERE d.book_number = a.book_number AND e.ezine_bbsays_flag = 1 ORDER BY d.edition_publish_dt DESC, c.ezine_edition_mapping_number DESC) 
                AS "reviewer_name"
	
	FROM		editors_choice a 
    inner join	media_reviews b on b.book_number = a.book_number 
    inner join	books c	on c.book_number = a.book_number
	
	
	WHERE		a.editors_choice_dt < getdate()
	AND			b.media_review_type_number = 9
	AND			EXISTS (
				SELECT 1 FROM editions e 
				WHERE e.book_number = a.book_number 
				AND e.edition_publish_dt >= DATEADD(year, -5, GETDATE())
			)
	
	ORDER BY	a.editors_choice_dt DESC

SELECT TOP 10 *
    FROM news
	where news_item_dt <= getdate()
    ORDER BY news_item_dt DESC, news_item_number DESC

SELECT 	top 10 a.arc_number,arc_promo_text,
		b.ezine_preview_number, b.ezine_preview_title, b.ezine_preview_subtitle, b.ezine_preview_jacket_image, b.ezine_preview_author, b.ezine_preview_publish_dt, 
		(select (CAST(AVG(CAST(arc_allocator_review_rating AS numeric(12,0))) AS numeric(12,0)))+
				(CASE WHEN right(CAST(AVG(CAST(arc_allocator_review_rating AS numeric(12,2))) AS numeric(12,2)),2) > 25 AND right(CAST(AVG(CAST(arc_allocator_review_rating AS numeric(12,2))) AS numeric(12,2)),2) < 50 THEN 0.5
				 WHEN right(CAST(AVG(CAST(arc_allocator_review_rating AS numeric(12,2))) AS numeric(12,2)),2) > 50 AND right(CAST(AVG(CAST(arc_allocator_review_rating AS numeric(12,2))) AS numeric(12,2)),2) < 75 THEN -0.5
				 ELSE 0
				 END)
 			from arc_allocator	where arc_number = a.arc_number and arc_allocator_review_approved_flag = 1) AS arcrating

FROM arcs a
INNER JOIN ezine_previews b ON b.ezine_preview_number = a.ezine_preview_number

WHERE arc_closed_flag = 1
AND arc_obc_flag = 0
AND getdate() >= arc_on_ad_dt
AND (select count(*) from arc_allocator d where d.arc_number = a.arc_number and arc_allocator_review_approved_flag = 1) > 1
and getdate() <= dateadd(day,1,a.arc_off_ad_dt)

ORDER BY NEWID()

SELECT 	top 3 a.arc_number,arc_promo_text,
			b.ezine_preview_number, b.ezine_preview_title, b.ezine_preview_subtitle, b.ezine_preview_jacket_image, b.ezine_preview_author, b.ezine_preview_publish_dt, 
			(select (CAST(AVG(CAST(arc_allocator_review_rating AS numeric(12,0))) AS numeric(12,0)))+
					(CASE WHEN right(CAST(AVG(CAST(arc_allocator_review_rating AS numeric(12,2))) AS numeric(12,2)),2) > 25 AND right(CAST(AVG(CAST(arc_allocator_review_rating AS numeric(12,2))) AS numeric(12,2)),2) < 50 THEN 0.5
					 WHEN right(CAST(AVG(CAST(arc_allocator_review_rating AS numeric(12,2))) AS numeric(12,2)),2) > 50 AND right(CAST(AVG(CAST(arc_allocator_review_rating AS numeric(12,2))) AS numeric(12,2)),2) < 75 THEN -0.5
					 ELSE 0
					 END)
				from arc_allocator	where arc_number = a.arc_number and arc_allocator_review_approved_flag = 1) AS arcrating

	FROM arcs a
	INNER JOIN ezine_previews b ON b.ezine_preview_number = a.ezine_preview_number

	WHERE arc_closed_flag = 1
	AND arc_obc_flag = 0
	AND getdate() >= arc_on_ad_dt
	AND (select count(*) from arc_allocator d where d.arc_number = a.arc_number and arc_allocator_review_approved_flag = 1) > 1

	ORDER BY a.arc_off_ad_dt DESC

SELECT		top 10
                tblblogentries.id, 
                tblblogentries.title, 
                tblblogentries.alias,
                tblblogentries.body,
                dateAdd(hh, -3, tblblogentries.posted) as posted,
                tblUsers.name

    FROM		tblblogentries
    inner join 	tblUsers on tblUsers.username = tblblogentries.username
    
    WHERE 		posted < getdate()
    AND			released = 1
    AND			posted > dateadd(month, -3, getdate())
    
    ORDER BY	posted DESC

select top 1 bookclub_number, bookclub_name, bookclub_title, bookclub_summary, bookclub_interview, bookclub_image, bookclub_entry_dt
from bookclubs
where bookclub_display_flag = 1
and bookclub_entry_dt < GETDATE()
order by bookclub_entry_dt DESC

SELECT TOP 10
		r.*,
		e.ezine_preview_jacket_image,

		CASE WHEN r.book_number > 0
			 THEN ''
			 ELSE e.ezine_preview_author
		END AS "author_name",

		CASE WHEN r.book_number > 0
			 THEN b.book_title
			 ELSE e.ezine_preview_title
		END AS "book_title",

		COALESCE(NULLIF(LTRIM(RTRIM(m.discourse_username)), ''), NULLIF(LTRIM(RTRIM(r.reader_review_reviewer_name)), '')) AS reviewer_display_name


    FROM reader_reviews r
    LEFT JOIN books b ON b.book_number = r.book_number
	left join ezine_previews e on e.ezine_preview_number = r.ezine_preview_number
	LEFT JOIN members m ON m.member_number = r.member_number
    WHERE r.reader_review_approved_flag = 1
    AND r.reader_review_recommended = 1
    ORDER BY reader_review_number DESC

SELECT 		top 50 reader_review_reviewer_email_address, count(reader_review_reviewer_email_address) as "count"
	FROM 		reader_reviews
	WHERE 		reader_review_reviewer_email_address like '%@%'
	AND 		reader_review_reviewer_email_address <> 'reviews@bookbrowse.com'
	GROUP BY 	reader_review_reviewer_email_address
	
	
	ORDER BY 	count desc

SELECT TOP 10 
		d.book_number,
		d.post_id,
		d.topic_id,
		d.blurb,
		d.username,
		d.topic_title,
		d.last_updated,
		b.book_title,
		b.book_short_summary,
		b.book_jacket_info,
		ba.author_name
	FROM DiscourseSearchResults d
	INNER JOIN books b ON b.book_number = d.book_number
	LEFT JOIN (
		SELECT book_number, 
			   STUFF((
				   SELECT ', ' + author_first_name + ' ' + author_last_name
				   FROM book_author_mapping bam2
				   INNER JOIN authors a2 ON a2.author_number = bam2.author_number
				   WHERE bam2.book_number = bam1.book_number
				   FOR XML PATH('')
			   ), 1, 2, '') AS author_name
		FROM book_author_mapping bam1
		GROUP BY book_number
	) ba ON ba.book_number = d.book_number
	WHERE d.topic_id IS NOT NULL
	AND d.post_id IS NOT NULL
	AND d.username IS NOT NULL
	AND d.topic_title IS NOT NULL
	AND d.book_number NOT LIKE 'p%'
	ORDER BY d.last_updated DESC

SELECT TOP 5
		d.author_number,
		CASE 
			WHEN datalength(d.author_middle_initial) > 0 
			THEN d.author_first_name + ' ' + d.author_middle_initial + ' ' + d.author_last_name
			ELSE d.author_first_name + ' ' + d.author_last_name
		END AS author_full_name,
		d.author_first_name,
		d.author_last_name,
		i.author_info_number,
		i.author_info_image,
		i.author_info_url,
		i.author_info_interview_summary,
		i.author_info_interview_dt,
		i.author_info_copyright_info,
		-- Get the most recent book by this author
		(
			SELECT TOP 1 b.book_number
			FROM book_author_mapping bam
			INNER JOIN books b ON b.book_number = bam.book_number
			WHERE bam.author_number = d.author_number
			ORDER BY b.book_entry_dt DESC
		) AS book_number,
		(
			SELECT TOP 1 b.book_title
			FROM book_author_mapping bam
			INNER JOIN books b ON b.book_number = bam.book_number
			WHERE bam.author_number = d.author_number
			ORDER BY b.book_entry_dt DESC
		) AS book_title
	FROM authors d
	INNER JOIN author_info i ON d.author_number = i.author_number
	WHERE 
		DATALENGTH(LTRIM(RTRIM(CAST(i.author_info_interview_summary AS varchar(max))))) > 0
		AND DATALENGTH(LTRIM(RTRIM(CAST(i.author_info_interview AS varchar(max))))) > 0
		AND d.author_number IN (
			SELECT DISTINCT c.author_number 
			FROM categories a
			INNER JOIN category_book_mapping b ON a.category_number = b.category_number
			INNER JOIN book_author_mapping c ON b.book_number = c.book_number
			WHERE a.category_active_flag = 1
		)
	ORDER BY i.author_info_interview_dt DESC

-- Get 1 source book and 3 read-alike recommendations
	-- Top: Featured source book, Bottom: 3 similar books
	SELECT TOP 1
		source_book.book_number AS source_book_number,
		source_book.book_title AS source_book_title,
		source_author.author_name AS source_author_name,
		source_book.category_number
	FROM (
		-- Get a random source book that has a category and was published in last 5 years
		SELECT TOP 1
			b.book_number,
			b.book_title,
			c.category_number
		FROM books b
		INNER JOIN category_book_mapping cbm ON cbm.book_number = b.book_number
		INNER JOIN categories c ON c.category_number = cbm.category_number
		INNER JOIN editions e ON e.book_number = b.book_number
		WHERE c.category_active_flag = 1
		AND e.edition_publish_dt >= DATEADD(year, -5, GETDATE())
		ORDER BY NEWID()
	) source_book
	-- Get the author name for source book
	CROSS APPLY (
		SELECT TOP 1
			CASE 
				WHEN datalength(a.author_middle_initial) > 0 
				THEN a.author_first_name + ' ' + a.author_middle_initial + ' ' + a.author_last_name
				ELSE a.author_first_name + ' ' + a.author_last_name
			END AS author_name
		FROM book_author_mapping bam
		INNER JOIN authors a ON a.author_number = bam.author_number
		WHERE bam.book_number = source_book.book_number
	) source_author

-- Get 3 matching read-alike books published in last 5 years
		SELECT TOP 3
			b.book_number,
			b.book_title,
			author_name = (
				SELECT TOP 1
					CASE 
						WHEN datalength(a.author_middle_initial) > 0 
						THEN a.author_first_name + ' ' + a.author_middle_initial + ' ' + a.author_last_name
						ELSE a.author_first_name + ' ' + a.author_last_name
					END
				FROM book_author_mapping bam
				INNER JOIN authors a ON a.author_number = bam.author_number
				WHERE bam.book_number = b.book_number
			)
		FROM books b
		INNER JOIN category_book_mapping cbm ON cbm.book_number = b.book_number
		INNER JOIN editions e ON e.book_number = b.book_number
		WHERE cbm.category_number = ?
			AND b.book_number != ?
			AND e.edition_publish_dt >= DATEADD(year, -5, GETDATE())
		ORDER BY NEWID()

SELECT TOP 5
		hot_topic_id,
		topic_id,
		topic_title,
		topic_slug,
		topic_excerpt,
		topic_url,
		views_count,
		posts_count,
		likes_count,
		hotness_score,
		created_at,
		last_posted_at,
		category_id,
		category_name,
		category_slug,
		last_updated
	FROM discourse_hot_topics
	WHERE hotness_score > 0
	ORDER BY hotness_score DESC, last_posted_at DESC

select		top 1 ezine_number, ezine_dt, ezine_image, ezine_brief_description, ezine_introduction
from		ezines
where		ezine_active_flag = 1
and 		ezine_type_number = 4
and			ezine_dt < getdate()
order by 	ezine_dt DESC

SELECT		a.wordplay_number, a.wordplay_dt, a.wordplay_name, a.wordplay_intro_text, b.wordplay_puzzle_number, b.wordplay_puzzle_question
    FROM		wordplays a 
	INNER JOIN	wordplay_puzzle_mapping c on c.wordplay_number = a.wordplay_number
	INNER JOIN	wordplay_puzzles b on b.wordplay_puzzle_number = c.wordplay_puzzle_number
	WHERE		a.wordplay_number = (	select top 1 a.wordplay_number
                                from wordplays a, wordplay_puzzles b, wordplay_puzzle_mapping c
                                where a.wordplay_number = c.wordplay_number
                                and b.wordplay_puzzle_number = c.wordplay_puzzle_number
                                and a.wordplay_dt < getdate()
                                and a.wordplay_expiration_dt > dateadd(day, -1, getdate())
                                order by a.wordplay_dt asc
                                )

select a.category_qry_template
	    from categories a
			where a.category_number = ?
				and a.category_qry_template IS NOT NULL

select 	a.book_number, a.book_title, a.book_sub_title, 
				CAST(a.book_jacket_info AS VARCHAR(10)) book_jacket_info, 
				CAST(a.book_excerpt AS VARCHAR(10)) book_excerpt, 
				CAST(a.book_reading_guide AS VARCHAR(10)) book_reading_guide, 
				a.book_short_summary, 
				b.category_name, b.category_description, 
				d.author_number, d.author_first_name, d.author_last_name, d.author_middle_initial, d.author_pronunciation, d.author_notes, d.last_modified_dt, 
				f.edition_publish_dt "hardcover_publish_dt", f.edition_number_of_pages "hardcover_number_of_pages", f.edition_isbn "hardcover_isbn", f.edition_isbn13 "hardcover_isbn13", f.edition_jacket_image "hardcover_jacket_image", f.edition_publisher "hardcover_publisher", 
				g.edition_publish_dt "paperback_publish_dt", g.edition_number_of_pages "paperback_number_of_pages", g.edition_isbn "paperback_isbn", g.edition_isbn13 "paperback_isbn13", g.edition_jacket_image "paperback_jacket_image", g.edition_publisher "paperback_publisher", 
				i.author_info_number, i.author_number, i.author_info_image, i.author_info_entry_dt, i.author_info_interview_summary, i.author_info_interview, i.author_info_biography, i.author_info_copyright_info, 
				(select top 1 edition_publish_dt from editions where book_number = a.book_number and edition_publish_dt < getdate() order by edition_publish_dt desc) "sort_publish_dt",
				(select AVG(CAST(media_review_rating AS numeric(12,2))) from media_reviews  where book_number = a.book_number and media_review_type_number <> 7 and media_review_rating > 0) "critrating"
		    
		from books a
			inner join category_book_mapping c on c.book_number = a.book_number
			inner join categories b on c.category_number = b.category_number and b.category_active_flag = 1
			inner join book_author_mapping e on a.book_number = e.book_number
			inner join authors d on e.author_number = d.author_number
			inner join author_info i on d.author_number = i.author_number
			inner join editions h on h.book_number = a.book_number
			left outer join editions f on a.book_number = f.book_number and f.edition_paperback_flag = 0
			left outer join editions g on a.book_number = g.book_number and g.edition_paperback_flag = 1
			
		where 	b.category_number = ?
				and h.edition_publish_dt >= DATEADD(year, -5, GETDATE())
				
				

		order by sort_publish_dt desc, a.book_entry_dt desc

SELECT		a.book_number, a.book_title, a.book_short_summary,
					d.author_first_name, d.author_last_name, d.author_middle_initial,
					CASE WHEN datalength(d.author_middle_initial) > 0 
						THEN d.author_first_name + ' ' + d.author_middle_initial + ' ' + d.author_last_name
						ELSE d.author_first_name + ' ' + d.author_last_name
					END AS "author_full_name",
					(select top 1 edition_publish_dt from editions where book_number = a.book_number and edition_publish_dt < getdate() order by edition_publish_dt desc) "publish_dt",
					(select cast(AVG(CAST(media_review_rating AS numeric(12,2)))AS decimal(12,1)) from media_reviews where book_number = a.book_number and media_review_type_number <> 7 and media_review_rating > 0) "critrating"
		FROM		books a
		INNER JOIN	category_book_mapping c ON c.book_number = a.book_number
		INNER JOIN	book_author_mapping e ON e.book_number = a.book_number
		INNER JOIN	authors d ON d.author_number = e.author_number
		WHERE		c.category_number = 59
		AND			datalength(a.book_reading_guide) > 1
		AND			(select top 1 edition_publish_dt from editions where book_number = a.book_number and edition_publish_dt < getdate() order by edition_publish_dt desc) > dateadd(year, -5, getdate())
		ORDER BY 	NEWID()

select *
						from get_reader_reviews_power_reviewers
						where reader_review_reviewer_email_address LIKE 'trishathomas78@gmail.com'

select 	f.edition_publish_dt "hardcover_publish_dt", f.edition_jacket_image "hardcover_jacket_image", 
            g.edition_publish_dt "paperback_publish_dt", g.edition_jacket_image "paperback_jacket_image"
    
    from   	books a
	left join editions f on a.book_number = f.book_number and f.edition_paperback_flag = 0
	left join editions g on a.book_number = g.book_number and g.edition_paperback_flag = 1
                    
    where 	a.book_number = ?

select 	f.edition_publish_dt "hardcover_publish_dt", f.edition_jacket_image "hardcover_jacket_image", 
            g.edition_publish_dt "paperback_publish_dt", g.edition_jacket_image "paperback_jacket_image"
    
    from   	books a
	left join editions f on a.book_number = f.book_number and f.edition_paperback_flag = 0
	left join editions g on a.book_number = g.book_number and g.edition_paperback_flag = 1
                    
    where 	a.book_number = ?

select 	f.edition_publish_dt "hardcover_publish_dt", f.edition_jacket_image "hardcover_jacket_image", 
            g.edition_publish_dt "paperback_publish_dt", g.edition_jacket_image "paperback_jacket_image"
    
    from   	books a
	left join editions f on a.book_number = f.book_number and f.edition_paperback_flag = 0
	left join editions g on a.book_number = g.book_number and g.edition_paperback_flag = 1
                    
    where 	a.book_number = ?

select 	f.edition_publish_dt "hardcover_publish_dt", f.edition_jacket_image "hardcover_jacket_image", 
            g.edition_publish_dt "paperback_publish_dt", g.edition_jacket_image "paperback_jacket_image"
    
    from   	books a
	left join editions f on a.book_number = f.book_number and f.edition_paperback_flag = 0
	left join editions g on a.book_number = g.book_number and g.edition_paperback_flag = 1
                    
    where 	a.book_number = ?

select 	f.edition_publish_dt "hardcover_publish_dt", f.edition_jacket_image "hardcover_jacket_image", 
            g.edition_publish_dt "paperback_publish_dt", g.edition_jacket_image "paperback_jacket_image"
    
    from   	books a
	left join editions f on a.book_number = f.book_number and f.edition_paperback_flag = 0
	left join editions g on a.book_number = g.book_number and g.edition_paperback_flag = 1
                    
    where 	a.book_number = ?

select 	f.edition_publish_dt "hardcover_publish_dt", f.edition_jacket_image "hardcover_jacket_image", 
            g.edition_publish_dt "paperback_publish_dt", g.edition_jacket_image "paperback_jacket_image"
    
    from   	books a
	left join editions f on a.book_number = f.book_number and f.edition_paperback_flag = 0
	left join editions g on a.book_number = g.book_number and g.edition_paperback_flag = 1
                    
    where 	a.book_number = ?

select 	f.edition_publish_dt "hardcover_publish_dt", f.edition_jacket_image "hardcover_jacket_image", 
            g.edition_publish_dt "paperback_publish_dt", g.edition_jacket_image "paperback_jacket_image"
    
    from   	books a
	left join editions f on a.book_number = f.book_number and f.edition_paperback_flag = 0
	left join editions g on a.book_number = g.book_number and g.edition_paperback_flag = 1
                    
    where 	a.book_number = ?

select 	f.edition_publish_dt "hardcover_publish_dt", f.edition_jacket_image "hardcover_jacket_image", 
            g.edition_publish_dt "paperback_publish_dt", g.edition_jacket_image "paperback_jacket_image"
    
    from   	books a
	left join editions f on a.book_number = f.book_number and f.edition_paperback_flag = 0
	left join editions g on a.book_number = g.book_number and g.edition_paperback_flag = 1
                    
    where 	a.book_number = ?

select 	f.edition_publish_dt "hardcover_publish_dt", f.edition_jacket_image "hardcover_jacket_image", 
            g.edition_publish_dt "paperback_publish_dt", g.edition_jacket_image "paperback_jacket_image"
    
    from   	books a
	left join editions f on a.book_number = f.book_number and f.edition_paperback_flag = 0
	left join editions g on a.book_number = g.book_number and g.edition_paperback_flag = 1
                    
    where 	a.book_number = ?

select 	f.edition_publish_dt "hardcover_publish_dt", f.edition_jacket_image "hardcover_jacket_image", 
            g.edition_publish_dt "paperback_publish_dt", g.edition_jacket_image "paperback_jacket_image"
    
    from   	books a
	left join editions f on a.book_number = f.book_number and f.edition_paperback_flag = 0
	left join editions g on a.book_number = g.book_number and g.edition_paperback_flag = 1
                    
    where 	a.book_number = ?

SELECT 	*
    FROM 	free_newsletters

select 	f.edition_publish_dt "hardcover_publish_dt", f.edition_jacket_image "hardcover_jacket_image", 
            g.edition_publish_dt "paperback_publish_dt", g.edition_jacket_image "paperback_jacket_image"
    
    from   	books a
	left join editions f on a.book_number = f.book_number and f.edition_paperback_flag = 0
	left join editions g on a.book_number = g.book_number and g.edition_paperback_flag = 1
                    
    where 	a.book_number = ?

select 	f.edition_publish_dt "hardcover_publish_dt", f.edition_jacket_image "hardcover_jacket_image", 
            g.edition_publish_dt "paperback_publish_dt", g.edition_jacket_image "paperback_jacket_image"
    
    from   	books a
	left join editions f on a.book_number = f.book_number and f.edition_paperback_flag = 0
	left join editions g on a.book_number = g.book_number and g.edition_paperback_flag = 1
                    
    where 	a.book_number = ?

select 	f.edition_publish_dt "hardcover_publish_dt", f.edition_jacket_image "hardcover_jacket_image", 
            g.edition_publish_dt "paperback_publish_dt", g.edition_jacket_image "paperback_jacket_image"
    
    from   	books a
	left join editions f on a.book_number = f.book_number and f.edition_paperback_flag = 0
	left join editions g on a.book_number = g.book_number and g.edition_paperback_flag = 1
                    
    where 	a.book_number = ?

select top 1 ezine_number, ezine_quote, ezine_quote_title
		    from ezines
		    where len(ezine_quote_title) > 1
		    order by newid()

SELECT 	*
    FROM 	free_newsletters

SELECT		a.adid,	adweight
	FROM		adsystem a
	INNER JOIN 	ad_category_mapping b ON b.adid = a.adid
	WHERE		a.section = ?
	AND			a.active  = 1
	AND 		a.start_date <= GETDATE()
	
	AND (a.viewby LIKE '%n%' OR a.viewby = 'all')
            

	AND     (b.category_number IN (	SELECT	category_number
									FROM	category_book_mapping
									WHERE	book_number = ?)
      		OR 	b.category_number = ?)

SELECT	a.adid,	adweight
        FROM	adsystem a
        WHERE	a.section = ? 
        AND 	a.active  = 1 
        AND		a.start_date <= GETDATE()	
        
        AND (a.viewby LIKE '%n%' OR a.viewby = 'all')
        AND (select count(*) from ad_category_mapping where datalength(category_number) >= 1 and adid = a.adid) = 0

SELECT	a.start_date, a.adid, a.end_date, a.camp_views, a.image_name, a.alt_text, a.views, a.code, a.adsize
        FROM	adsystem a
        WHERE	a.adid = ?

CGI Variables:
AUTH_PASSWORD=
AUTH_TYPE=
AUTH_USER=
CERT_COOKIE=
CERT_FLAGS=
CERT_ISSUER=
CERT_KEYSIZE=
CERT_SECRETKEYSIZE=
CERT_SERIALNUMBER=
CERT_SERVER_ISSUER=
CERT_SERVER_SUBJECT=
CERT_SUBJECT=
CF_TEMPLATE_PATH=/root/website/index.cfm
CONTENT_LENGTH=
CONTENT_TYPE=
CONTEXT_PATH=
GATEWAY_INTERFACE=
HTTPS=
HTTPS_KEYSIZE=
HTTPS_SECRETKEYSIZE=
HTTPS_SERVER_ISSUER=
HTTPS_SERVER_SUBJECT=
HTTP_ACCEPT=*/*
HTTP_ACCEPT_ENCODING=gzip, br, zstd, deflate
HTTP_ACCEPT_LANGUAGE=
HTTP_CONNECTION=upgrade
HTTP_COOKIE=
HTTP_HOST=dev.bookbrowse.com
HTTP_REFERER=
HTTP_URL=
HTTP_USER_AGENT=Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)
LOCAL_ADDR=127.0.0.1
PATH_INFO=
PATH_TRANSLATED=/root/website/index.cfm
QUERY_STRING=
REMOTE_ADDR=127.0.0.1
REMOTE_HOST=127.0.0.1
REMOTE_USER=
REQUEST_METHOD=GET
SCRIPT_NAME=/index.cfm
SERVER_NAME=dev.bookbrowse.com
SERVER_PORT=8500
SERVER_PORT_SECURE=0
SERVER_PROTOCOL=HTTP/1.1
SERVER_SOFTWARE=
WEB_SERVER_API=

Client Variables:
cfid=4120164
cftoken=14fb7fcb379ba40e-3B62478A-9AD0-51F5-FB90778676B16DB4
hitcount=1
homepage_layout=new
lastvisit={ts '2026-06-02 01:26:13'}
library_user_flag=0
member_active_flag=0
member_logged_in_flag=0
member_number=0
order=p
timecreated={ts '2026-06-02 01:26:13'}
urltoken=CFID=4120164&CFTOKEN=14fb7fcb379ba40e-3B62478A-9AD0-51F5-FB90778676B16DB4
view=books

Cookie Variables:
CFID=4120164
CFTOKEN=14fb7fcb379ba40e-3B62478A-9AD0-51F5-FB90778676B16DB4

Session Variables:
cfid=4120164
cftoken=14fb7fcb379ba40e-3B62478A-9AD0-51F5-FB90778676B16DB4
sessionid=BOOKBROWSE_4120164_14fb7fcb379ba40e-3B62478A-9AD0-51F5-FB90778676B16DB4
urltoken=CFID=4120164&CFTOKEN=14fb7fcb379ba40e-3B62478A-9AD0-51F5-FB90778676B16DB4