<?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="di3_summary_national" result-set-mapping="di3_summary_national">
	     <query><![CDATA[			
			select '' visn_name, '' facility_name, vendor.vendor_name vendor_name, mdt.name model_name, 
					count(*) totalDevices, count(fd.med_dvc_detail_id) failedDevices, 
					CEILING((100.0*count(fd.med_dvc_detail_id)/count(*))) failureRate
			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
			left join
			(select distinct mdsh.med_dvc_detail_id med_dvc_detail_id from dbo.medical_device_status_h mdsh
			where med_dvc_status_type_id = 20115
			and status_change_date >= :fromDate and status_change_date <= :toDate 
			) fd on mdd.med_dvc_detail_id = fd.med_dvc_detail_id
			group by vendor.vendor_name, mdt.name
			order by vendor.vendor_name, mdt.name
	     		]]>
	     </query>	
	</named-native-query>
		
	<named-native-query name="di3_summary_visn" result-set-mapping="di3_summary_visn">
	     <query><![CDATA[				
			select cast(v.visn_name as varchar) visn_name, '' facility_name, vendor.vendor_name vendor_name, mdt.name model_name, 
					count(*) totalDevices, count(fd.med_dvc_detail_id) failedDevices, 
					CEILING((100.0*count(fd.med_dvc_detail_id)/count(*))) failureRate
			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	
			left join
			(select distinct mdsh.med_dvc_detail_id med_dvc_detail_id from dbo.medical_device_status_h mdsh
				where med_dvc_status_type_id = 20115
				and status_change_date >= :fromDate and status_change_date <= :toDate 
			) fd on mdd.med_dvc_detail_id = fd.med_dvc_detail_id
			where ('allVisn' = :allVisn or v.visn_id = :visnId)
			group by v.visn_name, vendor.vendor_name, mdt.name
			order by cast(substring(isnull(visn_name,'VISN 00'),6,2) as integer), vendor.vendor_name, mdt.name		
	     		]]>
	     </query>	
	</named-native-query>			
	
	<named-native-query name="di3_summary_facility" result-set-mapping="di3_summary_facility">
	     <query><![CDATA[
		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, count(fd.med_dvc_detail_id) failedDevices, 
				CEILING((100.0*count(fd.med_dvc_detail_id)/count(*))) failureRate
		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	
		left join
		(select distinct mdsh.med_dvc_detail_id med_dvc_detail_id from dbo.medical_device_status_h mdsh
			where med_dvc_status_type_id = 20115
			and status_change_date >= :fromDate and status_change_date <= :toDate 
		) fd on mdd.med_dvc_detail_id = fd.med_dvc_detail_id
		where ('allFacilities' = :allFacilities or f.ID = :facilityId)
		group by v.visn_name, f.facility_name, vendor.vendor_name, mdt.name
		order by cast(substring(isnull(visn_name,'VISN 00'),6,2) as integer), f.facility_name, vendor.vendor_name, mdt.name		
	     		]]>
	     </query>	
	</named-native-query>
							
	<sql-result-set-mapping name="di3_summary_national" >
		<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="failedDevices"/>
	   	<column-result name="failureRate"/>
	</sql-result-set-mapping>				
				
	<sql-result-set-mapping name="di3_summary_visn" >
		<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="failedDevices"/>
	   	<column-result name="failureRate"/>
	</sql-result-set-mapping>		
				
	<sql-result-set-mapping name="di3_summary_facility" >
	    <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="failedDevices"/>
	   	<column-result name="failureRate"/>
	</sql-result-set-mapping>
		
</entity-mappings>	
