<?xml version="1.0" encoding="UTF-8" ?>
<entity-mappings xmlns="http://java.sun.com/xml/ns/persistence/orm"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm http://java.sun.com/xml/ns/persistence/orm_1_0.xsd"
	version="1.0">
	<description>Device Inventory Reports</description>
	<named-native-query name="di1_summary_national_vendor" result-set-mapping="di1_summary">
	     <query><![CDATA[				
		select '' visn_name, '' facility_name, vendor_name, model_name, device_count, 
			(vgroup + dgroup) glevel 
			from (	select t.vendor_name, t.model_name,
					count(*) AS device_count,
					grouping(t.model_name) dgroup,
					grouping(t.Vendor_Name) vgroup
					from (
						SELECT	Vendor_Name,
								model_name = 
									case CensusPatient.Home_Dev_Model
									when 'N' then 'Not Reported'
									when 'N/A' then 'Not Reported'
									when 'NA' then 'Not Reported'
									when 'None' then 'Not Reported'
									when 'NULL' then 'Not Reported'
									else isnull(CensusPatient.Home_Dev_Model,'Not Reported')
									end
						FROM	Census INNER JOIN
								CensusPatient ON Census.Census_ID = CensusPatient.Census_ID INNER JOIN
								Vendor ON Census.Vendor_ID = Vendor.Vendor_ID
						WHERE   (Census.Report_Start_Date >= :reportStartDate)
								AND (Census.Report_End_Date <= :reportEndDate)
					) t
					GROUP BY  t.Vendor_Name, t.model_name with rollup) q
		ORDER BY vgroup, vendor_name, dgroup, model_name
	     		]]>
	     </query>	
	</named-native-query>
	
	<named-native-query name="di1_summary_national_device" result-set-mapping="di1_summary">
	     <query><![CDATA[				
		select '' visn_name, '' facility_name, vendor_name, model_name, 
		device_count, (dgroup + vgroup) glevel from (
		SELECT t.model_name, t.vendor_name,
			count(*) AS device_count,
			grouping(t.model_name) dgroup,
			grouping(t.vendor_name) vgroup
		from (
				SELECT	Vendor_Name,
						model_name = 
							case CensusPatient.Home_Dev_Model
							when 'N' then 'Not Reported'
							when 'N/A' then 'Not Reported'
							when 'NA' then 'Not Reported'
							when 'None' then 'Not Reported'
							when 'NULL' then 'Not Reported'
							else isnull(CensusPatient.Home_Dev_Model,'Not Reported')
							end
				FROM	Census INNER JOIN
						CensusPatient ON Census.Census_ID = CensusPatient.Census_ID INNER JOIN
						Vendor ON Census.Vendor_ID = Vendor.Vendor_ID
				WHERE   (Census.Report_Start_Date >= :reportStartDate)
						AND (Census.Report_End_Date <= :reportEndDate)
			) t
			GROUP BY  t.Vendor_Name, t.model_name with rollup) q where (dgroup + vgroup) in (0,2)
		ORDER BY dgroup, model_name, vgroup, vendor_name
	     		]]>
	     </query>	
	</named-native-query>
	
	<named-native-query name="di1_summary_visn_vendor" result-set-mapping="di1_summary">
	     <query><![CDATA[				
		select visn_name, '' facility_name, vendor_name, model_name, device_count, 
			(visngroup + vgroup + dgroup) glevel from (
		SELECT	t.visn_name,
				t.vendor_name,
				t.model_name,
				count(*) AS device_count,
				grouping(t.model_name) dgroup,
				grouping(t.vendor_name) vgroup,
				grouping(t.visn_name) visngroup				
			from (
				SELECT	cast(VISN.VISN_Name As VARCHAR) As visn_name,
				        Vendor_Name,
						model_name = 
							case CensusPatient.Home_Dev_Model
							when 'N' then 'Not Reported'
							when 'N/A' then 'Not Reported'
							when 'NA' then 'Not Reported'
							when 'None' then 'Not Reported'
							when 'NULL' then 'Not Reported'
							else isnull(CensusPatient.Home_Dev_Model,'Not Reported')
							end
				FROM	Census INNER JOIN
					CensusPatient ON Census.Census_ID = CensusPatient.Census_ID INNER JOIN
					Facilities ON CensusPatient.Facility_ID = Facilities.ID INNER JOIN
					VISN ON Facilities.VISN_ID = VISN.VISN_ID INNER JOIN
					Vendor ON Census.Vendor_ID = Vendor.Vendor_ID
				WHERE   (Census.Report_Start_Date >= :reportStartDate)
					AND (Census.Report_End_Date <= :reportEndDate)
					AND ('allVisn' = :allVisn or VISN.VISN_ID = :visnId)
			) t
		GROUP BY t.VISN_Name, t.Vendor_Name, t.model_name
		with rollup) q
		ORDER BY visngroup, cast(substring(isnull(visn_name,'VISN 00'),6,2) as integer), vgroup, vendor_name, dgroup, model_name
	     		]]>
	     </query>	
	</named-native-query>
	
	<named-native-query name="di1_summary_visn_device" result-set-mapping="di1_summary">
	     <query><![CDATA[				
		select visn_name, '' facility_name, vendor_name, model_name, device_count, 
			(visngroup + vgroup + dgroup) glevel from (
		SELECT	t.visn_name,
				t.vendor_name,
				t.model_name,
				count(*) AS device_count,
				grouping(t.model_name) dgroup,
				grouping(t.vendor_name) vgroup,
				grouping(t.visn_name) visngroup				
			from (
				SELECT	cast(VISN.VISN_Name As VARCHAR) As visn_name,
				        Vendor_Name,
						model_name = 
							case CensusPatient.Home_Dev_Model
							when 'N' then 'Not Reported'
							when 'N/A' then 'Not Reported'
							when 'NA' then 'Not Reported'
							when 'None' then 'Not Reported'
							when 'NULL' then 'Not Reported'
							else isnull(CensusPatient.Home_Dev_Model,'Not Reported')
							end
				FROM	Census INNER JOIN
					CensusPatient ON Census.Census_ID = CensusPatient.Census_ID INNER JOIN
					Facilities ON CensusPatient.Facility_ID = Facilities.ID INNER JOIN
					VISN ON Facilities.VISN_ID = VISN.VISN_ID INNER JOIN
					Vendor ON Census.Vendor_ID = Vendor.Vendor_ID
				WHERE   (Census.Report_Start_Date >= :reportStartDate)
						AND (Census.Report_End_Date <= :reportEndDate)
						AND ('allVisn' = :allVisn or VISN.VISN_ID = :visnId)
			) t
		GROUP BY t.VISN_Name, t.model_name, t.Vendor_Name
		with rollup) q where (visngroup + vgroup + dgroup) in (0,2,3)
		ORDER BY visngroup, cast(substring(isnull(visn_name,'VISN 00'),6,2) as integer), dgroup, model_name, vgroup, vendor_name, glevel
	     		]]>
	     </query>	
	</named-native-query>			
	
	<named-native-query name="di1_summary_facility_vendor" result-set-mapping="di1_summary">
	     <query><![CDATA[				
			select visn_name, facility_name, vendor_name, model_name, device_count, (visngroup + fgroup + vgroup + dgroup) glevel from (
			SELECT	t.visn_name, t.facility_name, t.vendor_name, t.model_name,
					count(*) AS device_count,
					grouping(t.model_name) dgroup,
					grouping(t.Vendor_Name) vgroup,
					grouping(t.Facility_Name) fgroup,
					grouping(t.visn_name) visngroup
			from (
				SELECT	cast(VISN.VISN_Name As VARCHAR) As visn_name,
						Facilities.Facility_Name as facility_name,
						Vendor_Name,
						model_name = 
							case CensusPatient.Home_Dev_Model
							when 'N' then 'Not Reported'
							when 'N/A' then 'Not Reported'
							when 'NA' then 'Not Reported'
							when 'None' then 'Not Reported'
							when 'NULL' then 'Not Reported'
							else isnull(CensusPatient.Home_Dev_Model,'Not Reported')
							end
					FROM	Census INNER JOIN
							CensusPatient ON Census.Census_ID = CensusPatient.Census_ID INNER JOIN
							Facilities ON CensusPatient.Facility_ID = Facilities.ID INNER JOIN
							VISN ON Facilities.VISN_ID = VISN.VISN_ID INNER JOIN
							Vendor ON Census.Vendor_ID = Vendor.Vendor_ID
					WHERE   (Census.Report_Start_Date >= :reportStartDate)
							AND (Census.Report_End_Date <= :reportEndDate)
							AND ('allFacilities' = :allFacilities or Facilities.ID = :facilityId)
			) t
			GROUP BY t.visn_name, t.Facility_Name, t.Vendor_Name, t.model_name
			with rollup) q
			ORDER BY visngroup, cast(substring(isnull(visn_name,'VISN 00'),6,2) as integer), 
					fgroup, facility_name, vgroup, vendor_name, dgroup, model_name
	     		]]>
	     </query>	
	</named-native-query>
	
	<named-native-query name="di1_summary_facility_device" result-set-mapping="di1_summary">
	     <query><![CDATA[				
			select visn_name, facility_name, vendor_name, model_name, device_count, (visngroup + fgroup + vgroup + dgroup) glevel from (
			SELECT	t.visn_name, t.facility_name, t.vendor_name, t.model_name,
					count(*) AS device_count,
					grouping(t.model_name) dgroup,
					grouping(t.Vendor_Name) vgroup,
					grouping(t.Facility_Name) fgroup,
					grouping(t.visn_name) visngroup
			from (
				SELECT	cast(VISN.VISN_Name As VARCHAR) As visn_name,
						Facilities.Facility_Name as facility_name,
						Vendor_Name,
						model_name = 
							case CensusPatient.Home_Dev_Model
							when 'N' then 'Not Reported'
							when 'N/A' then 'Not Reported'
							when 'NA' then 'Not Reported'
							when 'None' then 'Not Reported'
							when 'NULL' then 'Not Reported'
							else isnull(CensusPatient.Home_Dev_Model,'Not Reported')
							end
					FROM	Census INNER JOIN
							CensusPatient ON Census.Census_ID = CensusPatient.Census_ID INNER JOIN
							Facilities ON CensusPatient.Facility_ID = Facilities.ID INNER JOIN
							VISN ON Facilities.VISN_ID = VISN.VISN_ID INNER JOIN
							Vendor ON Census.Vendor_ID = Vendor.Vendor_ID
					WHERE   (Census.Report_Start_Date >= :reportStartDate)
							AND (Census.Report_End_Date <= :reportEndDate)
							AND ('allFacilities' = :allFacilities or Facilities.ID = :facilityId)
			) t
			GROUP BY t.visn_name, t.Facility_Name, t.model_name, t.Vendor_Name
			with rollup) q where (visngroup + fgroup + vgroup + dgroup) in (0,2,3,4)
			ORDER BY visngroup, cast(substring(isnull(visn_name,'VISN 00'),6,2) as integer), 
					fgroup, facility_name, dgroup, model_name, vgroup, vendor_name
	     		]]>
	     </query>	
	</named-native-query>
					
	<sql-result-set-mapping name="di1_summary" >
		<column-result name="visn_name"/>
		<column-result name="facility_name"/>
		<column-result name="vendor_name"/>
    	<column-result name="model_name"/>
		<column-result name="device_count"/>    	
    	<column-result name="glevel"/>    	
	</sql-result-set-mapping>
	
</entity-mappings>	
