<?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="di2_summary_national_vendor" result-set-mapping="di2_summary_national_vendor">
	     <query><![CDATA[	
		select 	'' location, vendor_name, model_name,
			isnull(assigned,0) assigned, isnull(available,0) available,  
			isnull(refurbish,0) refurbish, isnull(repair,0) repair, isnull(decommissioned,0) decommissioned,
			isnull(total,0) total, (vgroup + dgroup) glevel
		from (
			select 
			vendor_name, model_name,
			sum(case when dev_status = 'ASN' then dev_count else 0 end) as assigned,
			sum(case when dev_status = 'AVL' then dev_count else 0 end) as available,			
			sum(case when dev_status = 'REF' then dev_count else 0 end) as refurbish,
			sum(case when dev_status = 'RPR' then dev_count else 0 end) as repair,
			sum(case when dev_status = 'DEC' then dev_count else 0 end) as decommissioned,
			sum(dev_count) as total,
			grouping(vendor_name) vgroup,
			grouping(model_name) dgroup			
			from (
					select vendor.vendor_name vendor_name, mdt.name model_name, 
					mdst.code dev_status, count(*) dev_count from
					dbo.medical_device_detail di 
					inner join dbo.medical_device_status mds on di.med_dvc_detail_id = mds.med_dvc_detail_id
					inner join dbo.medical_device_status_type mdst on mds.med_dvc_status_type_id = mdst.id
					inner join dbo.medical_device_model_type mdt on di.med_dvc_model_type_id = mdt.id
					inner join dbo.vendor vendor on mdt.vendor_id = vendor.vendor_id
					where mds.med_dvc_status_type_id in (:deviceStatuses)	
					group by vendor.vendor_name, mdt.name, mdst.code
			) ds group by vendor_name, model_name with rollup
		) mds ORDER BY vgroup, vendor_name, dgroup, model_name
	     		]]>
	     </query>	
	</named-native-query>
	
	<named-native-query name="di2_summary_national_device" result-set-mapping="di2_summary_national_device">
	     <query><![CDATA[	
		select 	'' location, vendor_name, model_name, 
			isnull(assigned,0) assigned, isnull(available,0) available, 
			isnull(refurbish,0) refurbish, isnull(repair,0) repair, isnull(decommissioned,0) decommissioned,
			isnull(total,0) total, (vgroup + dgroup) glevel
		from (
			select 
			vendor_name, model_name,
			sum(case when dev_status = 'ASN' then dev_count else 0 end) as assigned,
			sum(case when dev_status = 'AVL' then dev_count else 0 end) as available,			
			sum(case when dev_status = 'REF' then dev_count else 0 end) as refurbish,
			sum(case when dev_status = 'RPR' then dev_count else 0 end) as repair,
			sum(case when dev_status = 'DEC' then dev_count else 0 end) as decommissioned,
			sum(dev_count) as total,
			grouping(vendor_name) vgroup,
			grouping(model_name) dgroup			
			from (
					select vendor.vendor_name vendor_name, mdt.name model_name, mdst.code dev_status, count(*) dev_count from
					dbo.medical_device_detail di 
					inner join dbo.medical_device_status mds on di.med_dvc_detail_id = mds.med_dvc_detail_id
					inner join dbo.medical_device_status_type mdst on mds.med_dvc_status_type_id = mdst.id
					inner join dbo.medical_device_model_type mdt on di.med_dvc_model_type_id = mdt.id
					inner join dbo.vendor vendor on mdt.vendor_id = vendor.vendor_id
					where mds.med_dvc_status_type_id in (:deviceStatuses)	
					group by mdt.name, vendor.vendor_name,  mdst.code
			) ds group by model_name, vendor_name  with rollup
		) mds where (dgroup + vgroup) in (0,2) ORDER BY dgroup, model_name, vgroup, vendor_name
	     		]]>
	     </query>	
	</named-native-query>	
	
	<named-native-query name="di2_summary_visn_vendor" result-set-mapping="di2_summary_visn_vendor">
	     <query><![CDATA[				
		select visn_name, vendor_name, model_name,
			assigned, available, refurbish, repair, decommissioned, total, (visngroup + vgroup + dgroup) glevel
		from (
			select visn_name, vendor_name, model_name,
			sum(case when dev_status = 'ASN' then dev_count else 0 end) as assigned,
			sum(case when dev_status = 'AVL' then dev_count else 0 end) as available,
			sum(case when dev_status = 'REF' then dev_count else 0 end) as refurbish,
			sum(case when dev_status = 'RPR' then dev_count else 0 end) as repair,
			sum(case when dev_status = 'DEC' then dev_count else 0 end) as decommissioned,
			sum(dev_count) as total,
			grouping(visn_name) visngroup, grouping(vendor_name) vgroup, grouping(model_name) dgroup
		from (
			select cast(v.visn_name as varchar) visn_name,
			vendor.vendor_name vendor_name, mdt.name model_name, 
			mdst.code dev_status, count(*) dev_count 
			from dbo.medical_device_detail di 
				inner join dbo.medical_device_status mds on di.med_dvc_detail_id = mds.med_dvc_detail_id
				inner join dbo.medical_device_status_type mdst on mds.med_dvc_status_type_id = mdst.id
				inner join dbo.medical_device_model_type mdt on di.med_dvc_model_type_id = mdt.id
				inner join dbo.vendor vendor on mdt.vendor_id = vendor.vendor_id				
				inner join dbo.facilities f on di.facilities_id = f.id
				inner join dbo.visn v on f.visn_id = v.visn_id	
			where mds.med_dvc_status_type_id in (:deviceStatuses)	
				and('allVisn' = :allVisn or v.visn_id = :visnId)
			group by v.visn_name, vendor.vendor_name, mdt.name,  mdst.code
		) ds
		group by visn_name, vendor_name, model_name with rollup )mds
		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="di2_summary_facility_vendor" result-set-mapping="di2_summary_facility_vendor">
	     <query><![CDATA[
		select (visn_name + ' ' + facility_name) facility_name, vendor_name, model_name,
			assigned, available, refurbish, repair, decommissioned, total, (visngroup + fgroup + vgroup + dgroup) glevel
		from (	     				
		select  visn_name, facility_name facility_name, vendor_name, model_name,
			sum(case when dev_status = 'ASN' then dev_count else 0 end) as assigned,
			sum(case when dev_status = 'AVL' then dev_count else 0 end) as available,
			sum(case when dev_status = 'REF' then dev_count else 0 end) as refurbish,
			sum(case when dev_status = 'RPR' then dev_count else 0 end) as repair,
			sum(case when dev_status = 'DEC' then dev_count else 0 end) as decommissioned,
			sum(dev_count) as total,
			grouping(visn_name) visngroup, grouping(facility_name) fgroup, grouping(vendor_name) vgroup, grouping(model_name) dgroup
		from (
			select cast(v.visn_name as varchar) visn_name, f.facility_name facility_name, 
			vendor.vendor_name vendor_name, mdt.name model_name, 			
			mdst.code dev_status, count(*) dev_count 
			from dbo.medical_device_detail di 
				inner join dbo.medical_device_status mds on di.med_dvc_detail_id = mds.med_dvc_detail_id
				inner join dbo.medical_device_status_type mdst on mds.med_dvc_status_type_id = mdst.id
				inner join dbo.medical_device_model_type mdt on di.med_dvc_model_type_id = mdt.id
				inner join dbo.vendor vendor on mdt.vendor_id = vendor.vendor_id								
				inner join dbo.facilities f on di.facilities_id = f.id
				inner join dbo.visn v on f.visn_id = v.visn_id
				where mds.med_dvc_status_type_id in (:deviceStatuses)	
					and ('allFacilities' = :allFacilities or f.ID = :facilityId)
			group by v.visn_name, f.facility_name, vendor.vendor_name, mdt.name, mdst.code
		) ds
		group by visn_name, facility_name, vendor_name, model_name with rollup )mds
		where (visngroup + fgroup + dgroup + vgroup) in (0,1,2,4)
		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="di2_summary_visn_device" result-set-mapping="di2_summary_visn_device">
	     <query><![CDATA[				
		select visn_name, vendor_name, model_name,
			assigned, available, refurbish, repair, decommissioned, total, (visngroup + vgroup + dgroup) glevel
		from (
			select visn_name, vendor_name, model_name,
			sum(case when dev_status = 'ASN' then dev_count else 0 end) as assigned,
			sum(case when dev_status = 'AVL' then dev_count else 0 end) as available,
			sum(case when dev_status = 'REF' then dev_count else 0 end) as refurbish,
			sum(case when dev_status = 'RPR' then dev_count else 0 end) as repair,
			sum(case when dev_status = 'DEC' then dev_count else 0 end) as decommissioned,
			sum(dev_count) as total,
			grouping(visn_name) visngroup,
			grouping(vendor_name) vgroup,
			grouping(model_name) dgroup
		from (
			select cast(v.visn_name as varchar) visn_name,
			vendor.vendor_name vendor_name, mdt.name model_name, 
			mdst.code dev_status, count(*) dev_count 
			from dbo.medical_device_detail di 
				inner join dbo.medical_device_status mds on di.med_dvc_detail_id = mds.med_dvc_detail_id
				inner join dbo.medical_device_status_type mdst on mds.med_dvc_status_type_id = mdst.id
				inner join dbo.medical_device_model_type mdt on di.med_dvc_model_type_id = mdt.id
				inner join dbo.vendor vendor on mdt.vendor_id = vendor.vendor_id				
				inner join dbo.facilities f on di.facilities_id = f.id
				inner join dbo.visn v on f.visn_id = v.visn_id	
			where mds.med_dvc_status_type_id in (:deviceStatuses)	
				and('allVisn' = :allVisn or v.visn_id = :visnId)
			group by v.visn_name, vendor.vendor_name, mdt.name,  mdst.code
		) ds
		group by visn_name, vendor_name, model_name with rollup )mds
		where (visngroup + dgroup + vgroup) in (0,2,3)
		order by visngroup, cast(substring(isnull(visn_name,'VISN 00'),6,2) as integer),
		vgroup, model_name, vendor_name
	     		]]>
	     </query>	
	</named-native-query>
		
	<named-native-query name="di2_summary_facility_device" result-set-mapping="di2_summary_facility_device">
	     <query><![CDATA[
		select (visn_name + ' ' + facility_name) facility_name, vendor_name, model_name,
			assigned, available, refurbish, repair, decommissioned, total, (visngroup + fgroup + vgroup + dgroup) glevel
		from (	     				
		select visn_name, facility_name, vendor_name, model_name,
			sum(case when dev_status = 'ASN' then dev_count else 0 end) as assigned,
			sum(case when dev_status = 'AVL' then dev_count else 0 end) as available,
			sum(case when dev_status = 'REF' then dev_count else 0 end) as refurbish,
			sum(case when dev_status = 'RPR' then dev_count else 0 end) as repair,
			sum(case when dev_status = 'DEC' then dev_count else 0 end) as decommissioned,
			sum(dev_count) as total,
			grouping(visn_name) visngroup, grouping(facility_name) fgroup, grouping(vendor_name) vgroup, grouping(model_name) dgroup
		from (
			select cast(v.visn_name as varchar) visn_name,
			f.facility_name facility_name, 
			vendor.vendor_name vendor_name, mdt.name model_name, 			
			mdst.code dev_status, count(*) dev_count 
			from dbo.medical_device_detail di 
				inner join dbo.medical_device_status mds on di.med_dvc_detail_id = mds.med_dvc_detail_id
				inner join dbo.medical_device_status_type mdst on mds.med_dvc_status_type_id = mdst.id
				inner join dbo.medical_device_model_type mdt on di.med_dvc_model_type_id = mdt.id
				inner join dbo.vendor vendor on mdt.vendor_id = vendor.vendor_id								
				inner join dbo.facilities f on di.facilities_id = f.id
				inner join dbo.visn v on f.visn_id = v.visn_id
				where mds.med_dvc_status_type_id in (:deviceStatuses)	
				  and ('allFacilities' = :allFacilities or f.ID = :facilityId)
			group by v.visn_name, f.facility_name, vendor.vendor_name, mdt.name, mdst.code
		) ds
		group by visn_name, facility_name, vendor_name, model_name with rollup )mds
		where (visngroup + fgroup + dgroup + vgroup) in (0,2,4)
		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>
					
	<sql-result-set-mapping name="di2_summary_national_vendor" >
		<column-result name="location"/>
		<column-result name="vendor_name"/>
    	<column-result name="model_name"/>	
	   	<column-result name="assigned"/>
	   	<column-result name="available"/>
		<column-result name="refurbish"/>
    	<column-result name="repair"/>
		<column-result name="decommissioned"/>    	
    	<column-result name="total"/> 
    	<column-result name="glevel"/>    	   	
	</sql-result-set-mapping>				
	<sql-result-set-mapping name="di2_summary_national_device" >
		<column-result name="location"/>	
		<column-result name="vendor_name"/>
    	<column-result name="model_name"/>	
	   	<column-result name="assigned"/>
	   	<column-result name="available"/>
		<column-result name="refurbish"/>
    	<column-result name="repair"/>
		<column-result name="decommissioned"/>    	
    	<column-result name="total"/> 
    	<column-result name="glevel"/>    	   	
	</sql-result-set-mapping>
				
	<sql-result-set-mapping name="di2_summary_visn_vendor" >
		<column-result name="visn_name"/>
		<column-result name="vendor_name"/>
    	<column-result name="model_name"/>			
	   	<column-result name="assigned"/>
	   	<column-result name="available"/>
		<column-result name="refurbish"/>
    	<column-result name="repair"/>
		<column-result name="decommissioned"/>    	
    	<column-result name="total"/>   
    	<column-result name="glevel"/>    	
	</sql-result-set-mapping>		
			
	<sql-result-set-mapping name="di2_summary_visn_device" >
		<column-result name="visn_name"/>
		<column-result name="vendor_name"/>
    	<column-result name="model_name"/>			
	   	<column-result name="assigned"/>
	   	<column-result name="available"/>
		<column-result name="refurbish"/>
    	<column-result name="repair"/>
		<column-result name="decommissioned"/>    	
    	<column-result name="total"/>   
    	<column-result name="glevel"/>    	
	</sql-result-set-mapping>
				
	<sql-result-set-mapping name="di2_summary_facility_vendor" >
		<column-result name="facility_name"/>
		<column-result name="vendor_name"/>
    	<column-result name="model_name"/>					
	   	<column-result name="assigned"/>
	   	<column-result name="available"/>
		<column-result name="refurbish"/>
    	<column-result name="repair"/>
		<column-result name="decommissioned"/>    	
    	<column-result name="total"/>   
    	<column-result name="glevel"/>    	
	</sql-result-set-mapping>
	<sql-result-set-mapping name="di2_summary_facility_device" >
		<column-result name="facility_name"/>
		<column-result name="vendor_name"/>
    	<column-result name="model_name"/>					
	   	<column-result name="assigned"/>
	   	<column-result name="available"/>
		<column-result name="refurbish"/>
    	<column-result name="repair"/>
		<column-result name="decommissioned"/>    	
    	<column-result name="total"/>   
    	<column-result name="glevel"/>    	
	</sql-result-set-mapping>			
	
</entity-mappings>	
