<?xml version="1.0" encoding="ISO-8859-1"?>
<VUE name="PMSI000003" 
	label="PMSI. Chiffrier OVALIDE. Tableaux OVALIDE" 
	database="iCTI" 
	swf="*CTI_view1" 
	softCode="iCTI_pmsi"
	componentFile="PMSI_components.XML">
	<CACHE cachable="false" />
	<VIEWPROPERTIES>
		<PROPERTY dataLink="VIEWPROPERTIES" nameField="name" valueField="value" />
	</VIEWPROPERTIES>
	
	<SELECTIONS label="Paramètres">
		<GROUP label="Année">
			<FIELD name="MAT2AANNEE" label="" UI="combo"  width="400" default="2014"  >
				<OPTION dataLink="MAT2AIMPORT" dataField="cod" labelField="txt" />
			</FIELD>
		</GROUP>
	</SELECTIONS>
	
	<QUERIES>
		<QUERY> 
			
			<SQL>
				<select><![CDATA[ 
				
					DROP TABLE IF EXISTS w_mat2a_data_cti;
					CREATE TEMP TABLE w_mat2a_data_cti AS
					SELECT mat2a_import_id, file_code, table_sequence, SUM(nb_data_err) AS nb_data_err
					FROM pmsi.v_mat2a_data_1
					WHERE mat2a_import_id = [VIEW.MAT2AID] AND 
						data_cti IS NOT NULL
					GROUP BY 1,2,3;
					
					
					SELECT oid, 
						p_mat2a_data.mat2a_import_id, 
						p_mat2a_data.file_code, 
						p_mat2a_data.file_code || '-' || to_char(p_mat2a_data.table_sequence,'FM00'),
						COALESCE(data_mat2a[1],'') || ' ' || COALESCE(data_mat2a[2],'') || ' ' || COALESCE(data_mat2a[3],'') || ' ' || COALESCE(data_mat2a[4],''),
						nb_data_err
					FROM pmsi.p_mat2a_data
					JOIN w_mat2a_data_cti ON
						p_mat2a_data.mat2a_import_id = w_mat2a_data_cti.mat2a_import_id AND
						p_mat2a_data.file_code = w_mat2a_data_cti.file_code AND
						p_mat2a_data.table_sequence = w_mat2a_data_cti.table_sequence
					WHERE p_mat2a_data.mat2a_import_id = [VIEW.MAT2AID] AND
						line_type = 'T'
					ORDER BY 3,4;
				
				]]></select>
				<FIELDS>
					<FIELD name="TABOID" />
					<FIELD name="IMPOID" />
					<FIELD name="FIL" />
					<FIELD name="TAB" />
					<FIELD name="TIT" />
					<FIELD name="ERR" />
				</FIELDS>
			</SQL>
			
		</QUERY>
		
		<QUERY type="comboLink" name="MAT2AIMPORT" >
			<SQL>
				<select><![CDATA[
				
					SELECT annee, annee
					FROM
					(								
						SELECT date_part('year', p_imports.date_fin) AS annee,
						(MAX(Array[p_imports.date_fin::text, COALESCE(p_mat2a_import.oid,0)::text]))[2]::bigint AS mat2a_import_id
							FROM pmsi.p_imports 
							LEFT JOIN pmsi.p_mat2a_import ON mat2a_import_id = p_mat2a_import.oid
							WHERE etat = 'G' AND p_imports.date_fin >= '20120101'
							GROUP BY 1
							ORDER BY 1 DESC
					) subview;
				]]></select>
				<FIELDS>
					<FIELD name="cod" />
					<FIELD name="txt" />
				</FIELDS>
			</SQL>
		</QUERY>

		
		<QUERY type="propertiesLink" name="VIEWPROPERTIES" forSelections="false">
			<SQL>
				<select><![CDATA[
						SELECT 'MAT2AID', 
							COALESCE((SELECT (MAX(Array[p_imports.date_fin::text, COALESCE(p_mat2a_import.oid,0)::text]))[2]::bigint
									FROM pmsi.p_imports 
									LEFT JOIN pmsi.p_mat2a_import ON mat2a_import_id = p_mat2a_import.oid
									WHERE etat = 'G' AND p_imports.date_fin BETWEEN '[MAT2AANNEE]0101' AND [MAT2AANNEE]1231),0);
				]]></select>
				<FIELDS>
					<FIELD name="name"/>
					<FIELD name="value"/>
				</FIELDS>
			</SQL>
		</QUERY>
		
		

	</QUERIES>
	
	<CALCFIELDS>
	</CALCFIELDS>
	
	<PRESENTATION>
		<VIEWLINKS>
			
			<VIEWLINK label="Détail" shortLabel="Détail" view="PMSI000004.XML">
				<ARG name="TABOID" value="ROW.TABOID" />
			</VIEWLINK>


		</VIEWLINKS>
		
		<ONGLET label="Tableaux">
		
			<DATAGRID title="Tableaux avec comparaison CTI" headerHeight="48" >
				<COLUMN dataField="TABOID" 
					type="Char"
					width="110"
					visible="false"
					outputFormat="#"
					headerText="OID data" 
					textAlign="right" />
				<COLUMN dataField="IMPOID" 
					type="Number"
					width="110"
					visible="false"
					outputFormat="#"
					headerText="OID import" 
					textAlign="right" />
				<COLUMN dataField="TAB"
					type="Char"
					width="100"
					headerText="Table"
					textAlign="left" >
					<CELLSTYLE name="cellIndicator" value="circle, 5, right top, 0xFF6600" 
							condition="ROW.ERR != 0" />
				</COLUMN>
				<COLUMN dataField="TIT"
					type="Char"
					minWidth="100"
					headerText="Titre"
					textAlign="left" />
				<COLUMN dataField="ERR"
					type="Number"
					width="80"
					outputFormat="0"
					headerText="Erreurs" />
				
			</DATAGRID>
		
		</ONGLET>
		
	</PRESENTATION>
	
</VUE>

			
