<?php

	include('../../Config/config.php');
	include('../../Config/odbc.php');



	$type 	= '';
	$from 	= '';
	$to 	= '';
  $radiologist = '';
  $modalitylist = 'modality.removed = false and modality.active =true and modality.mod_id != -1';
	$mstuid= '';
	if ( isset ( $_GET['type'] ) && $_GET['type'] != '' 	)		$type 			= $_GET['type'];

	if ( isset ( $_GET['modid'] ) && $_GET['modid'] != '' 	)
	{
		$modalitylist .= " and modality.mod_id in({$_GET['modid']})";
	}
  if( isset($_GET['mstuid']) && $_GET['mstuid'] != '')
	{
		$mstuid = ' and study.mstud_id_auto in('.$_GET['mstuid'].')';
	}
	if ( isset ( $_GET['from']) && $_GET['from'] != ''		)		$fromDate 		= $_GET['from'];	else 	$fromDate 	= date("Ymd");
	if ( isset ( $_GET['to'] ) && $_GET['to'] != ''			)		$toDate 		= $_GET['to'];  	else 	$toDate 	= date("Ymd");
  if ( isset ( $_GET['dow'] ) && $_GET['dow'] != '' 		)		$daysOfWeek 	= $_GET['dow'];

	$rangeofdate = ' and study.date::date >= to_date(\''.$fromDate.'\',\'YYYYMMDD\')';
	$rangeofdate .= ' and study.date::date <= to_date(\''.$toDate.'\',\'YYYYMMDD\')';



	//**************************************************************
	// Fill modalities array
	//

	$modArray = array ();

	$connQUAL = get_dbconnection ();
	$requestQUAL =  "select name,mod_id from modality where {$modalitylist}";
	$resultQUAL = pg_query ( $connQUAL, $requestQUAL );

	if ( $resultQUAL != FALSE )
	{
		while ( $rowQUAL = pg_fetch_row ($resultQUAL) )
		{
			$modArray [$rowQUAL [1]] = $rowQUAL [0];
		}
	}
	pg_close ( $connQUAL );

	// *********************************************************************************************************************
	// GET DATA FROM RIS
	//



	$conn = get_dbconnection();

	if ( $conn )
	{
		$request = '';

		switch ( $type )
		{
			case 'dacs_nstudybymodality':
				$request = "select modality.mod_id,'Nb examens' as label, count(*) as value,true as co0isid,false col1isid, false as col2isid from modality,modality_identity,modality_profile,study
				where modality.mod_id = modality_identity.mod_id and modality_identity.modident_id = modality_profile.modident_id and study.pro_id = modality_profile.pro_id and {$modalitylist} {$rangeofdate} {$mstuid} group by modality.mod_id,modality.name order by modality.name";
			break;
			case 'dacs_dgm95bystudy':
				$request = "select study.description_auto as group,
				modality.mod_id as label,TRIM(BOTH FROM to_char(
						percentile_disc(0.95) within group (order by dgm_auto),
						'999999999999999.99')
					) as value,false as co0isid,true col1isid, false as col3isid
				from study,modality,modality_identity,modality_profile
				where modality.mod_id = modality_identity.mod_id
				and modality.modalitytitle = 'MG'
				and modality_identity.modident_id = modality_profile.modident_id and study.pro_id = modality_profile.pro_id and study.description_auto != ''
				and {$modalitylist} {$rangeofdate} {$mstuid}
				group by modality.mod_id, study.description_auto";
			break;
			case 'dacs_dlpbystudy':
				$request = "select master_study_description.description as group,
							modality.mod_id as label,
							TRIM(BOTH FROM to_char(percentile_disc(0.50) within group (order by dlp_auto),'999999999999999.99')) as value,
							false as co0isid,true as  col1isid, false as col2isid
							from study,modality,modality_identity,modality_profile,master_study_description
							where modality.mod_id = modality_identity.mod_id
							and modality.modalitytitle = 'CT'
							and modality_identity.modident_id = modality_profile.modident_id and study.pro_id = modality_profile.pro_id and study.description_auto != '' and master_study_description.mstud_id = study.mstud_id_auto
							and study.mstud_id_auto !=0 and master_study_description.description !='' and {$modalitylist} {$rangeofdate} {$mstuid}
							group by modality.mod_id, master_study_description.description";
				break;
				case 'dacs_ctdibystudy':
				$request = "select master_study_description.description as group,
							modality.mod_id as label,
							TRIM(BOTH FROM to_char(percentile_disc(0.50) within group (order by ctdivol_auto),'999999999999999.99')) as value,
							false as co0isid,true as  col1isid, false as col2isid
							from study,modality,modality_identity,modality_profile,master_study_description
							where modality.mod_id = modality_identity.mod_id
							and modality.modalitytitle = 'CT'
							and modality_identity.modident_id = modality_profile.modident_id and study.pro_id = modality_profile.pro_id and study.description_auto != '' and master_study_description.mstud_id = study.mstud_id_auto
							and study.mstud_id_auto !=0 and master_study_description.description !='' and {$modalitylist} {$rangeofdate} {$mstuid}
							group by modality.mod_id, master_study_description.description";
				break;
				case 'dacs_fluorobystudy':
				$request = "select master_study_description.description as group,
							modality.mod_id as label,
							TRIM(BOTH FROM to_char(percentile_disc(0.50) within group (order by total_fluoro_time/1000),'999999999999999.99')) as value,
							false as co0isid,true as  col1isid, false as col2isid
							from study,modality,modality_identity,modality_profile,master_study_description
							where modality.mod_id = modality_identity.mod_id
							and modality.modalitytitle = 'CR'
							and modality_identity.modident_id = modality_profile.modident_id and study.pro_id = modality_profile.pro_id and study.description_auto != '' and master_study_description.mstud_id = study.mstud_id_auto
							and study.mstud_id_auto !=0 and master_study_description.description !='' and {$modalitylist} {$rangeofdate} {$mstuid}
							group by modality.mod_id, master_study_description.description";
				break;
				case 'dacs_dapbystudy':
				$request = "select master_study_description.description as group,
							modality.mod_id as label,
							TRIM(BOTH FROM to_char(percentile_disc(0.50) within group (order by dap_auto/1000),'999999999999999.99')) as value,
							false as co0isid,true as  col1isid, false as col2isid
							from study,modality,modality_identity,modality_profile,master_study_description
							where modality.mod_id = modality_identity.mod_id
							and modality.modalitytitle = 'CR'
							and modality_identity.modident_id = modality_profile.modident_id and study.pro_id = modality_profile.pro_id and study.description_auto != '' and master_study_description.mstud_id = study.mstud_id_auto
							and study.mstud_id_auto !=0 and master_study_description.description !='' and {$modalitylist} {$rangeofdate} {$mstuid}
							group by modality.mod_id, master_study_description.description order by master_study_description.description ";
				break;
				case 'dacs_nstudybysite':
							$request = "select to_date(to_char(study.date, 'DDMMYY'),'DDMMYY') as group1,
							'Nb examens' as label,
							count(*) as value,
							false as co0isid,
							false as  col1isid,
							false as col2isid
							from modality,modality_identity,modality_profile,study
							where modality.mod_id = modality_identity.mod_id and modality_identity.modident_id = modality_profile.modident_id and study.pro_id = modality_profile.pro_id and {$modalitylist} {$rangeofdate} {$mstuid}  group by   group1 order by group1 asc";
				break;
				case 'dacs_nstudymgbyage':
							$request = "with T2 AS(
select (DATE_PART('year', to_date(to_char(study.date, 'YYYYMMDD'),'YYYYMMDD')) - DATE_PART('year',patient.birthdate )) as age ,'Nb examens' as label,
							count(*) as value,false as co0isid,
							false as  col1isid,
							false as col2isid
							from study,modality,modality_identity,modality_profile,patient
							where  modality.mod_id = modality_identity.mod_id
							and modality_identity.modident_id = modality_profile.modident_id and study.pro_id = modality_profile.pro_id and study.description_auto != ''
							and patient.birthdate is not null and patient.pat_id = study.pat_id and modality.modalitytitle = 'MG' and {$modalitylist} {$rangeofdate} {$mstuid}
							group by age order by age)
							select agee,'Nb examens',coalesce(value,0),co0isid,col1isid,col2isid from t2 right join (select generate_series(0,120) as agee )  t1 on age=agee order by agee";
				break;
				case 'dacs_nstudybyage':
							$request = "with T2 AS(
select (DATE_PART('year', to_date(to_char(study.date, 'YYYYMMDD'),'YYYYMMDD')) - DATE_PART('year',patient.birthdate )) as age ,'Nb examens' as label,
							count(*) as value,false as co0isid,
							false as  col1isid,
							false as col2isid
							from study,modality,modality_identity,modality_profile,patient
							where  modality.mod_id = modality_identity.mod_id
							and modality_identity.modident_id = modality_profile.modident_id and study.pro_id = modality_profile.pro_id and study.description_auto != ''
							and patient.birthdate is not null and patient.pat_id = study.pat_id and {$modalitylist} {$rangeofdate} {$mstuid}
							group by age order by age)
							select agee,'Nb examens',coalesce(value,0),co0isid,col1isid,col2isid from t2 right join (select generate_series(0,120) as agee )  t1 on age=agee order by agee";
				break;

				case 'dacs_50-75percentilbytime':
							$request = "select to_date(to_char(study.date, 'MMYY'),'MMYY') as group1,
							'50' as label,
							TRIM(BOTH FROM to_char(percentile_disc(0.50) within group (order by dap_auto	),'999999999999999.99')) as value,
							false as co0isid,
							false as  col1isid,
							false as col2isid
							from modality,modality_identity,modality_profile,study
							where modality.mod_id = modality_identity.mod_id and modality.modalitytitle = 'CR' and modality_identity.modident_id = modality_profile.modident_id and study.pro_id = modality_profile.pro_id and {$modalitylist} {$rangeofdate} {$mstuid}   group by   group1
							union
							select to_date(to_char(study.date, 'MMYY'),'MMYY') as group1,
							'75' as label,
							TRIM(BOTH FROM to_char(percentile_disc(0.75) within group (order by dap_auto	),'999999999999999.99')) as value,
							false as co0isid,
							false as  col1isid,
							false as col2isid
							from modality,modality_identity,modality_profile,study
							where modality.mod_id = modality_identity.mod_id and modality.modalitytitle = 'CR' and modality_identity.modident_id = modality_profile.modident_id and study.pro_id = modality_profile.pro_id and {$modalitylist} {$rangeofdate} {$mstuid}   group by   group1 order by group1 asc";
				break;
				case 'dacs_50-75percentilctdibytime':
							$request = "select to_date(to_char(study.date, 'MMYY'),'MMYY') as group1,
							'percentile 50 ctdi' as label,
							TRIM(BOTH FROM to_char(percentile_disc(0.50) within group (order by ctdivol_auto	),'999999999999999.99')) as value,
							false as co0isid,
							false as  col1isid,
							false as col2isid
							from modality,modality_identity,modality_profile,study
							where modality.mod_id = modality_identity.mod_id and modality.modalitytitle = 'CT' and modality_identity.modident_id = modality_profile.modident_id and study.pro_id = modality_profile.pro_id and {$modalitylist} {$rangeofdate} {$mstuid}   group by   group1
							union
							select to_date(to_char(study.date, 'MMYY'),'MMYY') as group1,
							'percentile 75 ctdi' as label,
							TRIM(BOTH FROM to_char(percentile_disc(0.75) within group (order by ctdivol_auto	),'999999999999999.99')) as value,
							false as co0isid,
							false as  col1isid,
							false as col2isid
							from modality,modality_identity,modality_profile,study
							where modality.mod_id = modality_identity.mod_id and modality.modalitytitle = 'CT' and modality_identity.modident_id = modality_profile.modident_id and study.pro_id = modality_profile.pro_id and {$modalitylist} {$rangeofdate} {$mstuid}   group by   group1 order by group1 asc";
				break;

				case 'dacs_50-75percentildlpbytime':
							$request = "select to_date(to_char(study.date, 'MMYY'),'MMYY') as group1,
							'percentile 50 dlp' as label,
							TRIM(BOTH FROM to_char(percentile_disc(0.50) within group (order by dlp_auto	),'999999999999999.99')) as value,
							false as co0isid,
							false as  col1isid,
							false as col2isid
							from modality,modality_identity,modality_profile,study
							where modality.mod_id = modality_identity.mod_id and modality.modalitytitle = 'CT' and modality_identity.modident_id = modality_profile.modident_id and study.pro_id = modality_profile.pro_id and {$modalitylist} {$rangeofdate} {$mstuid}   group by   group1
							union
							select to_date(to_char(study.date, 'MMYY'),'MMYY') as group1,
							'percentile 75 dlp' as label,
							TRIM(BOTH FROM to_char(percentile_disc(0.75) within group (order by dlp_auto	),'999999999999999.99')) as value,
							false as co0isid,
							false as  col1isid,
							false as col2isid
							from modality,modality_identity,modality_profile,study
							where modality.mod_id = modality_identity.mod_id and modality.modalitytitle = 'CT' and modality_identity.modident_id = modality_profile.modident_id and study.pro_id = modality_profile.pro_id and {$modalitylist} {$rangeofdate} {$mstuid}
							group by  group1 order by group1 asc";
				break;


			default:
				break;
		}

		error_log($request);

		$ToSend = array();
		if ( $request != '' )
		{
		//	error_log ( "Type : ".$type );
		//	error_log ( "	request : ".$request );

			if ($result = pg_query ( $conn, $request ) )
			{
				if(isset($_GET["mapidtoname"]) && $_GET["mapidtoname"] == "false")
				{
					while ( $row = pg_fetch_row ($result) )
					{
						$ToSend[] = $row;
					}
				}else{
					while ( $row = pg_fetch_row ($result) )
					{
							if($row[3] == 't')
							{
								$row[0]=$modArray[$row[0]];
							}

							$group = $row[0];

							if ( !array_key_exists ( $group, $ToSend ) )
								$ToSend[$group] = array();

							if($row[4] == 't')
							{
								$row[1]=$modArray[$row[1]];
							}

							if($row[5] == 't')
							{
								$row[2]=$modArray[$row[2]];
							}

							$dataset["label"] = $row[1];
							$dataset["value"] 		= 	$row[2];
							$ToSend [ $group ][] 	= $dataset;
					}
				}
			}
		}
	}

	header('Content-Type: application/json');

	echo json_encode ( $ToSend );

?>
