DECLARE @userRole VARCHAR(10) = '".$userRole."'

SELECT

	*

 -- NEW BATCH COUNT AND BATCH COMPLETENESS COUNT
-- DEBUG: Return what the determined role was.
, @userRole AS 'userRole'

-- Get the total count for the batch.
,dbo.pipeline_getBatchCount(outerRes.BATCHID) AS count_surveysInBatch_total

 -- Get the counts for the current user role.
,dbo.pipeline_getBatchStatuses(outerRes.BATCHID, outerRes.SSID, @userRole) AS count_surveysInBatch_completed
 -- NEW BATCH COUNT AND BATCH COMPLETENESS COUNT



	-- Get latest status condition.
	, (
		SELECT 	CASE
			WHEN outerRes.STATUS IN (5, 7)                                                                              THEN '[Cancelled]'     -- Cancelled
			WHEN outerRes.IS_HELD = 1                                                                                   THEN '[Held]'          -- Held
			WHEN (outerRes.ORIG_SITE_NUMBER != outerRes.FINAL_SITE_NUMBER) AND (outerRes.home_on_perm_site IS NOT NULL) THEN '[Site Mismatch]' -- Unmatched Site Numbers
			WHEN ((outerRes.IS_RESUBMITTED = 1     ) AND (outerRes.IS_REJECTED IS NULL) )                               THEN '[Resubmited]'    -- '[Resubmited]'
			WHEN ((outerRes.IS_RESUBMITTED IS NULL ) AND (outerRes.IS_REJECTED = 1)     )                               THEN '[Rejected]'      -- '[Rejected]'
			 -- ELSE '0'
		END
	 ) AS LATESTSTATUSCONDITION

	 ,(
		SELECT 	CASE
			WHEN outerRes.STATUS IN (5, 7)                                                                              THEN '1' -- '[Cancelled]'
			WHEN outerRes.IS_HELD = 1                                                                                   THEN '2' -- '[Held]'
			WHEN (outerRes.ORIG_SITE_NUMBER != outerRes.FINAL_SITE_NUMBER) AND (outerRes.home_on_perm_site IS NOT NULL) THEN '3' -- '[Site Mismatch]'
			WHEN ((outerRes.IS_RESUBMITTED = 1     ) AND (outerRes.IS_REJECTED IS NULL) )                               THEN '4' -- '[Resubmited]'
			WHEN ((outerRes.IS_RESUBMITTED IS NULL ) AND (outerRes.IS_REJECTED = 1)     )                               THEN '5' -- '[Rejected]'
			 -- ELSE '0'
		END
	 ) AS LATESTSTATUSCONDITION_INT

	FROM (
	SELECT

	-- ss.is_resubmitted_after_rejected               AS IS_RESUBMITTED_AFTER_REJECTED ,
	ss.previous_status                             AS PREVIOUS_STATUS               ,

	ss.is_resubmitted_after_rejected AS IS_RESUBMITTED_AFTER_REJECTED ,
	ss.is_resubmitted                AS IS_RESUBMITTED                ,
	ss.is_rejected                   AS IS_REJECTED                   ,
	ss.is_rejected                   AS REJECTED ,

--	(
--		SELECT
--			CASE
--				WHEN ss.previous_status > ss.status THEN 'true'
--				ELSE 'false'
--			END
--	) AS REJECTED,



	(
		SELECT
			ma.site_no + ' (' + dbo.F_SOS_TRANSLATION(ma.sos) + ')'
		FROM ma_community_data ma
			INNER JOIN ms_locations   l ON l.mri_entity_id = ma.mri_entity_id
			INNER JOIN bs_communities c ON c.location_id   = l.location_id
				AND c.community_id = bsc.community_id
		WHERE
			-- ma.site_no = COALESCE(ho.placed_on_site_no, ss.site_number)
			ma.site_no = ss.site_number
	) AS SOS,

	(
		SELECT CASE
			WHEN ss.batch_id IS NULL     THEN
				(
					-- bsc.community_name + ' - ' + CONVERT(VARCHAR, ss.created_date, 101) + ' - NO BATCH '
					'NO BATCH'
				)
			WHEN ss.batch_id IS NOT NULL THEN
				(
					bsc.community_name + ' - ' + CONVERT(VARCHAR, ss.created_date, 101) + ' - ID ' + CONVERT(VARCHAR, ss.batch_id) + ' ('
						+
							CONVERT(VARCHAR, (
								-- Get the total count for the batch.
								--dbo.pipeline_getBatchCount(pl_site_surveys.batch_id) AS count_surveysInBatch_total
								SELECT dbo.pipeline_getBatchCount(ss.batch_id) AS count_surveysInBatch_total

								-- SELECT COUNT(pl_site_surveys.id)
								-- FROM   pl_site_surveys
								-- WHERE  pl_site_surveys.batch_id = ss.batch_id AND ss.batch_id IS NOT NULL
							))
						+
					')'
				)
			ELSE ''
		END
	) AS BATCHNAME,

	(
		SELECT CASE
			-- Confirmed working.
			WHEN
			(
				ss.means_of_purchase = '21st Cash'
				AND ho.cm_confirms_arrival IS NULL
				AND ss.status = 6
				AND hat.status = 3
			) THEN 'cm_confirms_arrival'

			-- Confirmed working.
			WHEN
			(
				ss.means_of_purchase = 'RHP Wire'
				AND ho.cm_confirms_arrival IS     NULL
				AND ss.status = 6
				AND ho.po_approved         IS NOT NULL
			) THEN 'cm_confirms_arrival'

			WHEN
			(
				ho.cm_confirms_arrival   IS NOT NULL
				AND ho.home_on_perm_site IS     NULL
				AND ss.status = 6
			)
			THEN 'home_on_perm_site'

			WHEN (
				ho.home_on_perm_site IS NOT NULL
				AND ho.haf_submitted IS     NULL
				AND ss.status = 6
			) THEN 'noa_accepts_perm_site'

			WHEN (
				ho.home_on_perm_site IS NOT NULL
				AND ho.haf_submitted IS NOT NULL
				AND ss.status = 6
			) THEN 'home_is_placed'

			ELSE '**** NOT READY YET ****'
		END
	) AS ARRIVALSTAGE2,

	(
		SELECT CASE
			-- Confirmed working.
			WHEN
			(
				ss.means_of_purchase = '21st Cash'
				AND ho.cm_confirms_arrival IS NULL
				AND ss.status = 6
				AND hat.status = 3
			) THEN 'Waiting for initial delivery'

			-- Confirmed working.
			WHEN
			(
				ss.means_of_purchase = 'RHP Wire'
				AND ho.cm_confirms_arrival IS     NULL
				AND ss.status = 6
				AND ho.po_approved         IS NOT NULL
			) THEN 'Waiting for initial delivery'

			WHEN
			(
				ho.cm_confirms_arrival   IS NOT NULL
				AND ho.home_on_perm_site IS     NULL
				AND ss.status = 6
			)
			THEN 'Waiting for Final Placement'

			WHEN (
				ho.home_on_perm_site IS NOT NULL
				AND ho.haf_submitted IS     NULL
				AND ss.status = 6
			) THEN 'Final Placement complete'

			ELSE '**** NOT READY YET ****'
		END
	) AS ARRIVALSTAGE,

	(
		SELECT CASE
			WHEN ss.status = 1  THEN 'Community Review'
			WHEN ss.status = 10 THEN 'NHPM Review'
			WHEN ss.status = 2  THEN 'NHSPM Review'
			WHEN ss.status = 3  THEN 'COO Review'
			WHEN ss.status = 4  THEN 'Completed'
			WHEN ss.status = 5  THEN 'Cancelled'
			WHEN ss.status = 6  THEN 'Home Order Request'
			WHEN ss.status = 7  THEN 'Home Order Request Cancelled'
			WHEN ss.status = 8  THEN 'NOA Review'
			WHEN ss.status = 9  THEN 'NHSPM Review'
		END 'INVALID STATUS'
	) AS STATUSTEXT,

	ss.is_site_change                              AS IS_SITE_CHANGE                ,
	ss.id                                          AS SSID                          ,
	ho.id                                          AS HOID                          ,
	ss.status                                      AS STATUS                        ,
	ss.community_id                                AS COMMUNITY_ID                  ,
	bsc.community_name                             AS COMMUNITY_NAME                ,
	CONVERT(VARCHAR, ss.created_date, 101)         AS CREATED_DATESS                ,
	CONVERT(VARCHAR, ho.date_created, 101)         AS CREATED_DATEHO                ,
	CONVERT(VARCHAR, ss.last_update, 101)          AS LAST_UPDATESS                 ,
	CONVERT(VARCHAR, ho.last_update, 101)          AS LAST_UPDATEHO                 ,
	CONVERT(VARCHAR, COALESCE(ho.last_update, ss.last_update), 101) AS LASTUPDATEANY,
	dbo.getNHPM(ss.community_id)                   AS NHPMNAME                      ,
	ss.batch_id                                    AS BATCHID                       ,
	ss.batch_id                                    AS batch_id                      ,
	ss.means_of_purchase                           AS MEANS_OF_PURCHASE             ,
	ss.insurance_claim                             AS INSURANCE_CLAIM               ,
	-- COALESCE(ho.placed_on_site_no, ss.site_number) AS SITE_NUMBER                   ,
	ss.site_number                                 AS SITE_NUMBER                   ,
	ss.site_number                                 AS ORIG_SITE_NUMBER              ,
	ho.placed_on_site_no                           AS FINAL_SITE_NUMBER             ,
	CONVERT(varchar, ho.home_on_perm_site , 101)   AS HOME_ON_PERM_SITE             ,
	ss.is_held                                     AS IS_HELD                       ,
	bsc.state                                      AS STATE                         ,
	ss.purchasing_entity                           AS PURCHASING_ENTITY             ,

	ho.signed_quote                                AS SIGNED_QUOTE                  ,
	CONVERT(VARCHAR, pl_ho_files.upload_date, 101) AS SIGNEDQUOTE_UPLOAD_DATE       ,
	pl_ho_files.original_file_name                 AS SIGNEDQUOTE_ORIGINAL_FILE_NAME,
	pl_ho_files.file_name                          AS SIGNEDQUOTE_FILE_NAME         ,

	ho.job_number                                  AS JOB_NUMBER                    , -- Might not be needed.
	ho.job_number_created                          AS JOB_NUMBER_CREATED            ,
	ho.job_number_requested                        AS JOB_NUMBER_REQUESTED          ,

	(
	SELECT CASE
		WHEN ms_states.is_hud_state = 1 THEN 1
		ELSE 0
		END
	) AS IS_HUD_STATE ,



--	-- Total batch count
--	(
--		SELECT COUNT(*)
--		FROM   pl_site_surveys
--		WHERE
--			pl_site_surveys.batch_id = ss.batch_id
--	)   AS BATCHCOUNT,
--
--	-- Total status 9 count
--	(
--		SELECT COUNT(*)
--		FROM   pl_site_surveys
--		WHERE pl_site_surveys.batch_id = ss.batch_id
--			-- AND pl_site_surveys.status >= 9
--			AND pl_site_surveys.status IN (1, 2, 8, 9, 3, 4)
--	)   AS COUNT_SPMREVIEW9_ANDABOVE,
--
--	-- Total status 8 count
--	(
--		SELECT COUNT(*)
--		FROM   pl_site_surveys
--		WHERE pl_site_surveys.batch_id = ss.batch_id
--			-- AND pl_site_surveys.status >= 8
--			AND pl_site_surveys.status IN (1, 2, 8, 9, 3, 4)
--
--
--	)   AS COUNT_NOAREVIEW8_ANDABOVE,
--
--
--	-- Total status >= 4 count
--	(
--		SELECT COUNT(*)
--		FROM   pl_site_surveys
--		WHERE pl_site_surveys.batch_id = ss.batch_id
--			-- AND pl_site_surveys.status >= 4
--			AND pl_site_surveys.status IN (4, 6)
--	)   AS COMPLETEINBATCH,
--
--	-- Total status > 3 count
--	(
--		SELECT Count(id)
--		FROM   pl_site_surveys
--		WHERE  pl_site_surveys.batch_id = ss.batch_id
--			-- AND pl_site_surveys.status >= 3
--			AND pl_site_surveys.status IN (3, 4, 6)
--	)   AS COUNT_COOANDABOVE,
--
--	-- Total status > 2 count
--	(
--		SELECT Count(id)
--		FROM   pl_site_surveys
--		WHERE  pl_site_surveys.batch_id = ss.batch_id
--			-- AND pl_site_surveys.status >= 2
--			AND pl_site_surveys.status IN (2, 8, 9, 3, 4, 6)
--
--	)   AS COUNT_SPMANDABOVE,
--
--	-- Total status > 1 count
--	(
--		SELECT Count(id)
--		FROM   pl_site_surveys
--		WHERE  pl_site_surveys.batch_id = ss.batch_id
--			-- AND pl_site_surveys.status >= 1
--			AND pl_site_surveys.status IN (1, 2, 8, 9, 3, 4, 6 )
--	)   AS COUNT_CMANDABOVE,



	-- Is this record a home order that requires a job number?
	(
		SELECT CASE
		WHEN
			ss.status = 6
			AND
				(
						ho.job_number_created   IS     NULL
					AND ho.job_number_requested IS NOT NULL
				)
			THEN 1
			ELSE 0
		END
	) AS BSJOBNUMBERNEEDED,

	(
		SELECT job_no
		FROM bs_job_numbers_held
		WHERE
			bs_job_numbers_held.community_id = ss.community_id
		AND site_no = ss.site_number
		AND used_on IS NULL
	) AS BS_JOB_NUMBER,

	dbo.pipeline_getNai(ss.id, 1)                     AS NAI_TEXT1 ,
	-- dbo.pipeline_getNai(ss.id, 2)                     AS NAI_TEXT2 ,
	-- dbo.pipeline_getNai(ss.id, 3)                     AS NAI_STEP  ,

	-- newbury..nb_communities.nhpm_involvement AS NHPM_INVOLVEMENT,
				(
					SELECT newbury..nb_communities.nhpm_involvement
					FROM newbury..nb_staff s
					INNER JOIN newbury..nb_staff_community_ref scr ON scr.staff_id = s.staff_id
						   AND scr.role_id = 16
					INNER JOIN ms_locations l ON l.ndb_community_id = scr.community_id
					INNER JOIN bs_communities c ON c.location_id = l.location_id
						   AND c.community_id = ss.community_id
					INNER JOIN newbury..nb_communities ON  newbury..nb_communities.community_id = l.ndb_community_id
					WHERE s.is_inactive <> 1
				) AS NHPM_INVOLVEMENT ,

	'x' AS 'x',
	f.floorplan_name

FROM pl_site_surveys     ss
LEFT JOIN pl_home_orders ho  ON ho.site_survey_id = ss.id
LEFT JOIN bs_communities bsc ON bsc.community_id  = ss.community_id
LEFT JOIN bshat_data     hat ON hat.id            = ho.hat_id
LEFT JOIN pl_ho_files        ON pl_ho_files.ho_id = ho.id AND ho.signed_quote = pl_ho_files.file_name
LEFT JOIN ms_states          ON ms_states.state   = bsc.state

left join pl_floorplans f on f.id = ss.approved_size

-- LEFT JOIN newbury..nb_communities ON newbury..nb_communities.community_id   = ss.community_id
WHERE
	1=1
	-- $statusFilter_WHERE

) outerRes

	WHERE outerRes.NAI_TEXT1 != 'COMPLETED'

-- Order by rejected/resubmitted, then open items, then held items at the bottom.
ORDER BY
	outerRes.IS_HELD                       ASC,
	outerRes.REJECTED                      DESC,
	-- outerRes.IS_RESUBMITTED_AFTER_REJECTED DESC
	outerRes.IS_RESUBMITTED DESC