<?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 Shortage Reports</description>
			
	<named-native-query name="di4_summary_national" result-set-mapping="di4_summary">
	     <query><![CDATA[
	        select '' visn_name, '' facility_name, cd.vendor_name vendor_name, cd.model_name model_name, 
				cd.totalDevices totalDevices, rd.requiredDevices requiredDevices,  
				(rd.requiredDevices - cd.totalDevices) shortage
			from 			
			(select vendor.vendor_name vendor_name, mdt.name model_name, count(*) totalDevices
			from
			dbo.medical_device_detail mdd
			inner join dbo.medical_device_model_type mdt on mdd.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 mdd.facilities_id = f.id
			inner join dbo.visn v on f.visn_id = v.visn_id	
			inner join dbo.medical_device_status mds on mds.med_dvc_detail_id = mdd.med_dvc_detail_id
			where mds.med_dvc_status_type_id != 20116 			
			group by vendor.vendor_name, mdt.name
			) cd
			left join
			(
			select mdt.name model_name, sum(mdshort.quantity_needed) requiredDevices
			from 
			    dbo.medical_device_shortage mdshort
			    inner join dbo.medical_device_model_type mdt on mdshort.med_dvc_model_type_id  = mdt.id				
			    group by mdt.name
			) rd
			on cd.model_name = rd.model_name
			order by cd.vendor_name, cd.model_name		
	     		]]>
	     </query>	
	</named-native-query>
				
	<named-native-query name="di4_summary_visn" result-set-mapping="di4_summary">
	     <query><![CDATA[
	        select cd.visn_name visn_name, '' facility_name, cd.vendor_name vendor_name, cd.model_name model_name, 
				cd.totalDevices totalDevices, rd.requiredDevices requiredDevices,  
				(rd.requiredDevices - cd.totalDevices) shortage
			from 			
			(select cast(v.visn_name as varchar) visn_name, vendor.vendor_name vendor_name, mdt.name model_name, 
					count(*) totalDevices
			from
			dbo.medical_device_detail mdd
			inner join dbo.medical_device_model_type mdt on mdd.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 mdd.facilities_id = f.id
			inner join dbo.visn v on f.visn_id = v.visn_id	
			inner join dbo.medical_device_status mds on mds.med_dvc_detail_id = mdd.med_dvc_detail_id
			where mds.med_dvc_status_type_id != 20116 
			and ('allVisn' = :allVisn or v.visn_id = :visnId)
			group by v.visn_name, vendor.vendor_name, mdt.name
			) cd
			left join
			(
			select cast(v.visn_name as varchar) visn_name, mdt.name model_name, sum(mdshort.quantity_needed) requiredDevices
			from 
			    dbo.medical_device_shortage mdshort
			    inner join dbo.medical_device_model_type mdt on mdshort.med_dvc_model_type_id  = mdt.id
				inner join dbo.facilities f on mdshort.facilities_id = f.id
			    inner join dbo.visn v on f.visn_id = v.visn_id
			    where ('allVisn' = :allVisn or v.visn_id = :visnId)
			    group by v.visn_name, mdt.name
			) rd
			on cd.visn_name = rd.visn_name and cd.model_name = rd.model_name
			order by cast(substring(isnull(cd.visn_name,'VISN 00'),6,2) as integer), cd.vendor_name, cd.model_name		
	     		]]>
	     </query>	
	</named-native-query>						
				
	<named-native-query name="di4_summary_facility" result-set-mapping="di4_summary">
	     <query><![CDATA[
	        select cd.visn_name visn_name, cd.facility_name, cd.vendor_name vendor_name, cd.model_name model_name, 
				cd.totalDevices totalDevices, rd.requiredDevices requiredDevices,  
				(rd.requiredDevices - cd.totalDevices) shortage
			from 			
			(select cast(v.visn_name as varchar) visn_name, f.facility_name facility_name, vendor.vendor_name vendor_name, mdt.name model_name, 
					count(*) totalDevices
			from
			dbo.medical_device_detail mdd
			inner join dbo.medical_device_model_type mdt on mdd.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 mdd.facilities_id = f.id
			inner join dbo.visn v on f.visn_id = v.visn_id	
			inner join dbo.medical_device_status mds on mds.med_dvc_detail_id = mdd.med_dvc_detail_id
			where mds.med_dvc_status_type_id != 20116 
			and ('allFacilities' = :allFacilities or f.ID = :facilityId)
			group by v.visn_name, f.facility_name, vendor.vendor_name, mdt.name
			) cd
			left join
			(
			select f.facility_name, mdt.name model_name, sum(mdshort.quantity_needed) requiredDevices
			from 
			    dbo.medical_device_shortage mdshort
			    inner join dbo.medical_device_model_type mdt on mdshort.med_dvc_model_type_id  = mdt.id
				inner join dbo.facilities f on mdshort.facilities_id = f.id
			    inner join dbo.visn v on f.visn_id = v.visn_id
			    where ('allFacilities' = :allFacilities or f.ID = :facilityId)
			    group by f.facility_name, mdt.name
			) rd
			on cd.facility_name = rd.facility_name and cd.model_name = rd.model_name
			order by cast(substring(isnull(cd.visn_name,'VISN 00'),6,2) as integer), cd.facility_name, cd.vendor_name, cd.model_name		
	     		]]>
	     </query>	
	</named-native-query>
					
	<sql-result-set-mapping name="di4_summary" >
		<column-result name="visn_name"/>
		<column-result name="facility_name"/>
		<column-result name="vendor_name"/>
    	<column-result name="model_name"/>			
	   	<column-result name="totalDevices"/>
	   	<column-result name="requiredDevices"/>
	   	<column-result name="shortage"/>
	</sql-result-set-mapping>		
		
</entity-mappings>	
