<?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>CCHT Inventory Reports</description>
	 
	<named-native-query name="vendorComplianceCurrent" result-set-mapping="vendorComplianceCurrentMapping">
	     <query><![CDATA[	
			SELECT
			Vendor.Vendor_Name AS [vendor_name],
			isnull(CensusPatient.Home_Dev_Model, '{none}') AS [model_name],
			count(*) device_count,
			(count(*) - count(CensusPatient.Home_Dev_Serial)) as device_nsl_count
		    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)
		    group by Vendor.Vendor_Name, CensusPatient.Home_Dev_Model
		    ORDER BY Vendor.Vendor_Name, CensusPatient.Home_Dev_Model
	     		]]>
	     </query>	
	</named-native-query>	

	<named-native-query name="vendorComplianceHistory" result-set-mapping="vendorComplianceHistoryMapping">
	     <query><![CDATA[
			SELECT
				Census.Report_End_Date as report_end_date,
		        Vendor.Vendor_Name AS vendor_name,
				count(*) device_count,
		        count(CensusPatient.Home_Dev_Model) model_name_count,		
				count(CensusPatient.Home_Dev_Serial) serial_number_count
		    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)	        
		    GROUP by report_end_date, Vendor.Vendor_Name
		    ORDER BY report_end_date desc, Vendor.Vendor_Name	     				
	     		]]>
	     </query>	
	</named-native-query>
				
	<named-native-query name="facilityDeviceInventory" result-set-mapping="facilityDeviceInventory">
	     <query><![CDATA[
			select mdt.code code, count(*) device_count
			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.facilities f on mdd.facilities_id = f.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 f.ID = :facilityId
			group by mdt.code	     				
	     		]]>
	     </query>	
	</named-native-query>			
							
	<sql-result-set-mapping name="vendorComplianceCurrentMapping" >
		<column-result name="vendor_name"/>
		<column-result name="model_name"/>
    	<column-result name="device_count"/>
    	<column-result name="device_nsl_count"/>    	
	</sql-result-set-mapping>	
						
	<sql-result-set-mapping name="vendorComplianceHistoryMapping" >	
		<column-result name="report_end_date"/>
		<column-result name="vendor_name"/>
    	<column-result name="device_count"/>
		<column-result name="model_name_count"/>    	
    	<column-result name="serial_number_count"/>    	
	</sql-result-set-mapping>		
	
	<sql-result-set-mapping name="facilityDeviceInventory" >	
		<column-result name="code"/>
		<column-result name="device_count"/>
 	</sql-result-set-mapping>				
						
						
</entity-mappings>
