<?php


require_once '../../autoload.php';



header('Content-type: text/xml');
set_time_limit(0); // 20 minutes


function create_tmp_datafile(&$fields,$modifnbr)
{
    global $tmpdir;
    $filename = uniqid();
    $filepath = $tmpdir."/".$filename.".dataxmlsx";
    $file = fopen($filepath,'w+');
    for($i=0;$i<$modifnbr;$i++)
    {
     	$v = $fields['v'.$i];
      unset($fields['v'.$i]);
     	$s = $fields['s'.$i];
      unset($fields['s'.$i]);
     	$r = $fields['r'.$i];
      unset($fields['r'.$i]);
     	$c = $fields['c'.$i];
      unset($fields['c'.$i]);
     	$line = $s . ";" . $r . ";" . $c . "=" . $v .PHP_EOL;
      fwrite($file, $line); // Écriture de la ligne dans le fichier
    }

    fclose($file);

    register_shutdown_function(function() use($filepath) {
       unlink($filepath);
    });
    return $filepath;
}



$afcnexport_url = $config_servernameorip.$EODACS_FOLDER."/eoDACS/afcnexport.php";


$sendbymail = $_GET['sendbymail'];
$generateasync = $_GET['generateasync'];
if ($sendbymail == 'true' || $generateasync == 'true') {
    ignore_user_abort(true);
    header("Connection: close");
    header("Cache-Control: no-store, no-cache, must-revalidate, max-age=0");
    header("Cache-Control: post-check=0, pre-check=0", false);
    header("Pragma: no-cache");
    ob_start();
    session_write_close();
    ob_end_flush();
}

$from = $_GET['from'];
$to = $_GET['to'];

$type = $_GET['type'];


$file = '';

$sql = '';
$filter_studymaster = '';
$filter_seriemaster = '';
$filter_modality = '';
$filter_weight = '';
$filter_age = '';
$filter_mandatory = '';
$filter_exportlist = '';

$list_modality = array();
$list_mstudid = '';
$list_rstudid = '';

$modalityri = 'and ri = false';

function getexportedmodality($modalityname)
{
  global $type;
  $modmapping = array();
  $modmapping['/^GE\\s?/'] = 'GE';

  if($type == AFCN_ANNEXE6)
    $modmapping['/fujifilm/i'] = 'Fujifilm';
  else
    $modmapping['/fujifilm/i'] = 'Fuji';

  $modmapping['/hologic/i'] = 'Hologic';
  $modmapping['/IMS/'] = 'IMS';
  $modmapping['/instrumentarium/i'] = 'Instrumentarium';
  $modmapping['/metaltronica/i'] = 'Metaltronica';
  $modmapping['/philips/i'] = 'Philips';
  $modmapping['/planmed/i'] = 'Planmed';
  $modmapping['/sectra/i'] = 'Sectra';
  $modmapping['/siemens/i'] = 'Siemens';
  $modmapping['/toshiba/i'] = 'Toshiba';
  $modmapping['/gmm/i'] = 'GMM';
  $modmapping['/italray/i'] = 'Italray';
  $modmapping['/odelft/i'] = 'Oldelft';
  $modmapping['/shimadzu/i'] = 'Shimadzu';
  $modmapping['/stephanix/i'] = 'Stephanix';


  foreach( $modmapping as $key => $value ){
    if(preg_match($key, $modalityname)){
      return $value;
    }
  }

  return 'AUTRES';
}

if ($type == IRSN_NMADULTE || $type == IRSN_NMPEDIATRIQUE || $type == IRSN_RXADULTE || $type ==IRSN_RXPEDIATRIQUE
  || $type == IRSN_CTADULTE || $type ==IRSN_CTPEDIATRIQUE || $type == IRSN_NMCTADULTE) {
    //patient weight and size are mandatory
    //IMC for IRSN export must be in the IMC interval[18-35]
    $filter_mandatory = " and study.pat_size is not null and study.pat_weight is not null and study.pat_size > 0 and study.pat_weight > 0 and (study.pat_weight/(study.pat_size*study.pat_size/1000000)) >= 18 and (study.pat_weight/(study.pat_size*study.pat_size/1000000)) <= 35";
}

if ($type == IRSN_RIADULTE || $type == AFCN_ANNEXE4) {
    $modalityri = 'and ri = true';
}

if (isset($_GET['mstuid']) && $_GET['mstuid'] != '') {
    $filter_studymaster .= " and study.mstud_id_auto in ({$_GET['mstuid']})";
}

if (isset($_GET['mserid']) && $_GET['mserid'] != '') {
    $filter_seriemaster .= " and serie.mserd_id_auto in ({$_GET['mserid']})";
}

if (isset($_GET['agemin']) && $_GET['agemin'] != '') {
    if ($_GET['agemin'] != EXPORT_AGEMIN) {
        $filter_age .= " and coalesce(EXTRACT(year FROM age(study.date,patient.birthdate))::integer,{$_GET['agemin']}) >=  {$_GET['agemin']}";
    }
}

if (isset($_GET['agemax']) && $_GET['agemax'] != '') {
    if ($_GET['agemax'] != EXPORT_AGEMAX) {
        $filter_age .= " and coalesce(EXTRACT(year FROM age(study.date,patient.birthdate))::integer,{$_GET['agemax']}) <=  {$_GET['agemax']}";
    }
}

if (isset($_GET['weightmin']) && $_GET['weightmin'] != '') {
    if ($_GET['weightmin'] != EXPORT_WEIGHTMIN) {
        $filter_weight .= " and study.pat_weight >=  ({$_GET['weightmin']})";
    }
}

if (isset($_GET['weightmax']) && $_GET['weightmax'] != '') {
    if ($_GET['weightmax'] != EXPORT_WEIGHTMAX) {
        $filter_weight .= " and study.pat_weight <=  ({$_GET['weightmax']})";
    }
}

if (isset($_GET['exportlist']) && $_GET['exportlist']!= '') {
    $filter_exportlist = "and la.desc_id in ({$_GET['exportlist']})";
}

if ($_GET['modid'] != '') {
    $filter_modality = " where v_modality_config.mod_id in({$_GET['modid']})";
}

if(isset($_SESSION['filter_modalitylist']) && $_SESSION['filter_modalitylist'] != '')
{
    if($filter_modality == '')
      $filter_modality .= ' where ';
    else
      $filter_modality .= ' and ';

    $filter_modality .= "v_modality_config.mod_id in ({$_SESSION['filter_modalitylist']})";
    $filter_modality .= " and modactive = true";
}




$sqllistmodality = '';

  $sqllistmodality = "with groupexport as(
  select id, e.desc_id as exportdesc_id,case when(id = 'afcnannexe7' and e.description_fr = 'Thorax') then (select string_agg(da1.desc_id::text,',') from description_afcn da1 where da1.description_fr in ('Thorax PA', 'Thorax complet')) else e.desc_id::text end  as mappedexportdesc_id,e.description_{$_SESSION['lang']} from annexe_afcn a,export_afcn la,description_afcn e
  where a.ann_id = la.ann_id and la.desc_id = e.desc_id
  )
  select replace(a.tabname ||'_'|| e.desc_id||'_'||modality.name,' ','_') as filename,modality.profile_list,groupexport.mappedexportdesc_id,modality.mod_id,e.description_{$_SESSION['lang']}
  from (select string_agg(pro_id::text,',') as profile_list,mod_id,name,modalitytitle,ri
  from v_modality_config  {$filter_modality}  group by mod_id,name,modalitytitle,ri) as modality,annexe_afcn a,export_afcn la,description_afcn e,modalitytitle_conv mc,groupexport
  where a.id= '{$type}' and a.ann_id = la.ann_id and la.desc_id = e.desc_id
  and groupexport.exportdesc_id = la.desc_id and groupexport.id = a.id
  and mc.title = case when (modality.ri = true) then '*'
  else modality.modalitytitle end and mc.conv = a.modalitytitle {$filter_exportlist} order by modality.name,e.description_{$_SESSION['lang']};";

//TODELETE
//$sqllistmodality = "select replace(a.tabname ||'_'|| e.desc_id||'_'||modality.name,' ','_') as filename,modality.profile_list,e.desc_id,modality.mod_id,e.description_{$_SESSION['lang']} from (select string_agg(pro_id::text,',') as profile_list,mod_id,name,modalitytitle from v_modality_config {$filter_modality} group by mod_id,name,modalitytitle) as modality,annexe_afcn a,export_afcn la,description_afcn e,modalitytitle_conv mc
//where a.id= '{$type}' and a.ann_id = la.ann_id and la.desc_id = e.desc_id and mc.title = modality.modalitytitle {$filter_exportlist}  order by modality.name,e.description_{$_SESSION['lang']};";
//error_log($sqllistmodality);

$conn = get_dbconnection();
if ($query = pg_query($conn, $sqllistmodality)) {
    while ($row = pg_fetch_row($query)) {
        $modality = new stdClass();
        $modality->filename = date("Ymdhis").'_'.$row[0];
        $modality->profile_list = $row[1];
        $modality->desc_id = $row[2];
        $modality->rstud_id_list = '';
        $modality->mstud_id_list = '';
        $modality->rserd_id_list = '';
        $modality->mod_id= $row[3];
        $modality->desc_export=$row[4];
        $list_modality[] = $modality;
    }
}

foreach ($list_modality as &$modality) {
    $sqllistrawstuid = "select string_agg(rstud_id::text,',') from raw_study_description rsd,master_study_description msd,modality_identity,modality_profile where rsd.modident_id =  modality_identity.modident_id and modality_identity.modident_id = modality_profile.modident_id
  and pro_id in({$modality->profile_list}) and msd.mstud_id = rsd.mstud_id and coalesce(rsd.desc_id,msd.desc_id) in({$modality->desc_id})";

    if ($query = pg_query($conn, $sqllistrawstuid)) {
        if ($row = pg_fetch_row($query)) {
            $modality->rstud_id_list = $row[0];
        }
    }

    $sqllistrawserid = " select string_agg(distinct rsdl.rserd_id::text,',') from
  master_serie_description msd,raw_serie_description rsd, raw_serie_description_link rsdl,modality_identity,modality_profile where rsd.modident_id =  modality_identity.modident_id and modality_identity.modident_id = modality_profile.modident_id
  and pro_id in({$modality->profile_list}) and rsd.mserd_id = msd.mserd_id
  and coalesce(rsd.desc_id,msd.desc_id) in({$modality->desc_id}) and rsdl.selected = true and rsd.rserd_id = rsdl.rserd_id";

    if ($query = pg_query($conn, $sqllistrawserid)) {
        if ($row = pg_fetch_row($query)) {
            $modality->rserd_id_list = $row[0];
        }
    }
    /*Before department contact and Radiophysicist
    $sqlgeneralinfo="select institution.name,institution.address,room.name,modality.company ,coalesce(modality.detector_type,'DR') as sensor_type,
  (select account.lastname from account where account.acc_id=institution.contact_acc_id),(select account.firstname from account where account.acc_id=institution.contact_acc_id) ,
  (select account.mail from account where account.acc_id=institution.contact_acc_id),(select account.lastname from account where account.acc_id=institution.radiophysicist_acc_id),(select account.firstname from account where account.acc_id=institution.radiophysicist_acc_id) ,
  (select account.mail from account where account.acc_id=institution.radiophysicist_acc_id),coalesce(modality.ct_number_barette,'?'),coalesce(modality.reconstruction_software,''),modality.companymodel,to_char(installation,'DD/MM/YYYY') as mod_insdate,
  coalesce(to_char(modality.dap_calibration_date,'YYYYMMDD'),''),
  modality.name
  from modality,room,institution,department where room.room_id=modality.room_id and room.dep_id=department.dep_id
  and department.inst_id=institution.inst_id and  modality.mod_id=".$modality->mod_id.";";
  */
  $sqlgeneralinfo="select institution.name,institution.address,room.name,modality.company ,coalesce(modality.detector_type,'DR') as sensor_type,
  coalesce ((select account.lastname from account where account.acc_id=department.contact_acc_id),(select account.lastname from account where account.acc_id=institution.contact_acc_id))
  ,coalesce((select account.firstname from account where account.acc_id=department.contact_acc_id),(select account.firstname from account where account.acc_id=institution.contact_acc_id))
  ,coalesce((select account.mail from account where account.acc_id=department.contact_acc_id),(select account.mail from account where account.acc_id=institution.contact_acc_id))
  ,coalesce((select account.lastname from account where account.acc_id=department.radiophysicist_acc_id),(select account.lastname from account where account.acc_id=institution.radiophysicist_acc_id))
  ,coalesce((select account.firstname from account where account.acc_id=department.radiophysicist_acc_id),(select account.firstname from account where account.acc_id=institution.radiophysicist_acc_id))
  ,coalesce((select account.mail from account where account.acc_id=department.radiophysicist_acc_id),(select account.mail from account where account.acc_id=institution.radiophysicist_acc_id))
  ,coalesce(modality.ct_number_barette,'?'),coalesce(modality.reconstruction_software,''),modality.companymodel,to_char(installation,'DD/MM/YYYY') as mod_insdate,
  coalesce(to_char(modality.dap_calibration_date,'YYYYMMDD'),''),  modality.name
  from modality,room,institution,department where room.room_id=modality.room_id and room.dep_id=department.dep_id
  and department.inst_id=institution.inst_id and modality.mod_id=".$modality->mod_id.";";

  //error_log("******");
  if($query = pg_query($conn,$sqlgeneralinfo))
  {
    if($row = pg_fetch_row($query))
    {
      $modality->inst_name=$row[0];
      $modality->inst_address=$row[1];
      $modality->room_name=$row[2];
      $modality->mod_brand=$row[3];
      $modality->sensor_type=$row[4];
      $modality->contact_name=$row[5];
      $modality->contact_fname=$row[6];
      $modality->contact_mail=$row[7];
      $modality->phys_name=$row[8];
      $modality->phys_fname=$row[9];
      $modality->phys_mail=$row[10];
      $modality->valid_name='';//$row[8];
      $modality->valid_fname='';//$row[9];
      $modality->valid_mail='';//$row[10];
      $modality->nbr_slices=$row[11];
      $modality->log_reconstruction=$row[12];
      $modality->mod_model=$row[13];
      $modality->mod_insdate=$row[14];
	    $modality->mod_dap_calibration_date=$row[15];
      $modality->mod_name = $row[16];
      $modality->mod_brandxlsvalue = getexportedmodality($modality->mod_brand);
    }
  }
}

//date filter
$condstudyinit =  " and study.date::date >= to_date('{$from}','YYYY-MM-DD') and study.date::date <= to_date('{$to}','YYYY-MM-DD')";
$condstudyinit.= $filter_age;
$condstudyinit.= $filter_weight;
$condstudyinit.= $filter_mandatory;


$listoffile  = array();

foreach( $list_modality as &$modality)
{

  $sql = '';
  $condstudy = $condstudyinit;
  $generate_file = true;

  if($modality->rstud_id_list != '' && $modality->rserd_id_list != '')
  {
    $condstudy .= " and (rstud_id in ({$modality->rstud_id_list}) or rserd_id in ({$modality->rserd_id_list}))";
  }else if($modality->rstud_id_list != '')
  {
    $condstudy .= " and rstud_id in ({$modality->rstud_id_list})";
  }else
  if($modality->rserd_id_list != '')
  {
    $condstudy .= " and rserd_id in ({$modality->rserd_id_list})";
  }
  else{
    // do nothing
    //error_log('AFCN export not configured for export '.$modality->filename );
    $generate_file = false;
  }

  if($generate_file == true)
  {

	if($type == AFCN_ANNEXE4)
	{
    //dap_total en Gy.cm²
		$querysql="select study.stu_id,to_char(study.date,'DD/MM/YYYY') as sdate,
      patient.sex,EXTRACT(year FROM age(study.date,patient.birthdate)) as age,' ' as codedocteur,
      study.total_fluoro_time/1000,study.dap_total/1000,study.total_number_radiographic_frames,upper(replace(operator,'^','')) as initial
     from study,patient where study.pat_id=patient.pat_id
	 and study.pro_id in({$modality->profile_list})	{$condstudy} order by study.date, EXTRACT(year FROM age(study.date,patient.birthdate))";

		if($query=pg_query($conn,$querysql))
		{

			$modifnbr=0;
			$nbrlines=0;
			$file=$modality->filename."_.xlsx";

			$fields = array(
			  'filename' => $GLOBALS["afcnfolder"]."\annexe_4_ri.xlsx",
			  'fileout' => $tmpdir.'/'.$file
			);


			$fields["s".$modifnbr]=0;
			$fields["r".$modifnbr]=2;
			$fields["c".$modifnbr]=4;
			$fields["v".$modifnbr]=$modality->inst_name;
			$modifnbr++;
			$fields["s".$modifnbr]=0;
			$fields["r".$modifnbr]=3;
			$fields["c".$modifnbr]=4;
			$fields["v".$modifnbr]=$modality->inst_address;
			$modifnbr++;
			$fields["s".$modifnbr]=0;
			$fields["r".$modifnbr]=4;
			$fields["c".$modifnbr]=4;
			$fields["v".$modifnbr]=$modality->mod_name;
			$modifnbr++;
			$fields["s".$modifnbr]=0;
			$fields["r".$modifnbr]=5;
			$fields["c".$modifnbr]=4;
			$fields["v".$modifnbr]=$modality->mod_brandxlsvalue;
			$modifnbr++;
			$fields["s".$modifnbr]=0;
			$fields["r".$modifnbr]=6;
			$fields["c".$modifnbr]=4;
			$fields["v".$modifnbr]=$modality->mod_model;
			$modifnbr++;
			$fields["s".$modifnbr]=0;
			$fields["r".$modifnbr]=7;
			$fields["c".$modifnbr]=4;
			$fields["v".$modifnbr]=$modality->sensor_type;
			$modifnbr++;

			/// TO ADD Date dernière vérif DAP
			$fields["s".$modifnbr]=0;
			$fields["r".$modifnbr]=9;
			$fields["c".$modifnbr]=4;
			$fields["v".$modifnbr]=$modality->desc_export;
			$modifnbr++;

			////////////////
			$fields["s".$modifnbr]=0;
			$fields["r".$modifnbr]=11;
			$fields["c".$modifnbr]=4;
			$fields["v".$modifnbr]=$modality->contact_name;
			$modifnbr++;
			$fields["s".$modifnbr]=0;
			$fields["r".$modifnbr]=11;
			$fields["c".$modifnbr]=8;
			$fields["v".$modifnbr]=$modality->contact_fname;
			$modifnbr++;
			$fields["s".$modifnbr]=0;
			$fields["r".$modifnbr]=11;
			$fields["c".$modifnbr]=11;
			$fields["v".$modifnbr]=$modality->contact_mail;
			$modifnbr++;

			$fields["s".$modifnbr]=0;
			$fields["r".$modifnbr]=12;
			$fields["c".$modifnbr]=4;
			$fields["v".$modifnbr]=$modality->phys_name;
			$modifnbr++;
			$fields["s".$modifnbr]=0;
			$fields["r".$modifnbr]=12;
			$fields["c".$modifnbr]=8;
			$fields["v".$modifnbr]=$modality->phys_fname;
			$modifnbr++;
			$fields["s".$modifnbr]=0;
			$fields["r".$modifnbr]=12;
			$fields["c".$modifnbr]=11;
			$fields["v".$modifnbr]=$modality->phys_mail;
			$modifnbr++;

			$fields["s".$modifnbr]=0;
			$fields["r".$modifnbr]=13;
			$fields["c".$modifnbr]=4;
			$fields["v".$modifnbr]=$modality->valid_name;
			$modifnbr++;
			$fields["s".$modifnbr]=0;
			$fields["r".$modifnbr]=13;
			$fields["c".$modifnbr]=8;
			$fields["v".$modifnbr]=$modality->valid_fname;
			$modifnbr++;
			$fields["s".$modifnbr]=0;
			$fields["r".$modifnbr]=13;
			$fields["c".$modifnbr]=11;
			$fields["v".$modifnbr]=$modality->valid_mail;
			$modifnbr++;

			$nbrlines=19;

			while($row=pg_fetch_row($query))
			{
				$fields["s".$modifnbr]=0;
				$fields["r".$modifnbr]=$nbrlines;
				$fields["c".$modifnbr]=0;
				$fields["v".$modifnbr]=$nbrlines-18;
				$modifnbr++;

				$fields["s".$modifnbr]=0;
				$fields["r".$modifnbr]=$nbrlines;
				$fields["c".$modifnbr]=1;
				$fields["v".$modifnbr]=$row[1];
				$modifnbr++;

				$fields["s".$modifnbr]=0;
				$fields["r".$modifnbr]=$nbrlines;
				$fields["c".$modifnbr]=2;
				$fields["v".$modifnbr]=$row[2];
				$modifnbr++;

				$fields["s".$modifnbr]=0;
				$fields["r".$modifnbr]=$nbrlines;
				$fields["c".$modifnbr]=3;
				$fields["v".$modifnbr]=$row[3];
				$modifnbr++;

				$fields["s".$modifnbr]=0;
				$fields["r".$modifnbr]=$nbrlines;
				$fields["c".$modifnbr]=4;
				$fields["v".$modifnbr]=$row[4];
				$modifnbr++;

				$fields["s".$modifnbr]=0;
				$fields["r".$modifnbr]=$nbrlines;
				$fields["c".$modifnbr]=7;
				$fields["v".$modifnbr]=$row[5];
				$modifnbr++;

				$fields["s".$modifnbr]=0;
				$fields["r".$modifnbr]=$nbrlines;
				$fields["c".$modifnbr]=8;
				$fields["v".$modifnbr]=$row[6];
				$modifnbr++;

				$fields["s".$modifnbr]=0;
				$fields["r".$modifnbr]=$nbrlines;
				$fields["c".$modifnbr]=9;
				$fields["v".$modifnbr]=$row[7];
				$modifnbr++;

				$fields["s".$modifnbr]=0;
				$fields["r".$modifnbr]=$nbrlines;
				$fields["c".$modifnbr]=10;
				$fields["v".$modifnbr]=$row[8];
				$modifnbr++;

				$nbrlines++;
			}
			$fields["modifnbr"]=$modifnbr;

      $tmpfilename = create_tmp_datafile($fields,$modifnbr);
      $fields["source_datafile"] = $tmpfilename;
      $fields["offset_0"] = "22";
      $fields["rolloverrow"] = "1";

			$postdata = http_build_query(
			  $fields
			);



			$opts = array('http' =>
			  array(
				'method'  => 'POST',
				'content' => $postdata
				//'header'  => 'Content-type: application/x-www-form-urlencoded',

			  )
			);
			$context  = stream_context_create($opts);


			//$url= "http://localhost".$EODACS_FOLDER."/eoDACS/afcnexport.php";


			$result=file_get_contents($afcnexport_url,false,$context);
			$listoffile[] = $file;
		}

	}

	if($type == AFCN_ANNEXE7)
	{
		/*$querysql="select study.stu_id,serie.ser_id,to_char(study.date,'DD/MM/YYYY') as sdate,
		patient.sex,EXTRACT(year FROM age(study.date,patient.birthdate)) as age,study.pat_weight,study.pat_size,val->>'23' as kv,
		round(coalesce((val->>'79')::numeric,(val->>'32')::numeric*(val->>'30')::numeric/1000),2) as mas,
		total_fluoro_time/1000,serie.dap_auto/10,count(*) over (partition by serie.stu_id) as nbr_serie,
		upper(replace(operator,'^','')) as initial,'Détail de '||(count(*) over (partition by serie.stu_id)) ||' acquisitions' as remarque
		from study,serie, patient,raw_serie_description_link
		where
		serie.ser_id = raw_serie_description_link.ser_id and raw_serie_description_link.selected = true
		and patient.pat_id = study.pat_id and serie.stu_id = study.stu_id and serie.dap_auto is not null
		and study.pro_id in({$modality->profile_list})	{$condstudy} order by study.date, EXTRACT(year FROM age(study.date,patient.birthdate)), serie.stu_id,serie.ser_id;";
		*/
    //dap_total en mGy.cm²
	  $querysql="select * from (select distinct (study.stu_id),'' as ser_id,to_char(study.date,'DD/MM/YYYY') as sdate,
      patient.sex,EXTRACT(year FROM age(study.date,patient.birthdate)) as age,study.pat_weight,study.pat_size,'' as kv,
      '' as mas,
      total_fluoro_time/1000 as fluoro_time,dap_total as daptotal,sum((acquisition_type not ilike 'fluoro%')::integer) over (partition by serie.stu_id) as nbr_serie,
      upper(replace(operator,'^','')) as initial,'' as remarque,study.date
      from study,serie, patient,raw_serie_description_link
      where
      serie.ser_id = raw_serie_description_link.ser_id and raw_serie_description_link.selected = true
	  and patient.pat_id = study.pat_id and serie.stu_id = study.stu_id
      and study.pro_id in({$modality->profile_list}) {$condstudy}) as exportlistorder order by date, age,stu_id;";


    //  	error_log($querysql);

    if($query=pg_query($conn,$querysql))
      {
      //error_log("-- query OK");
        $modifnbr=0;
        $nbrlines=0;
        $file=$modality->filename."_.xlsx";

        $fields = array(
          'filename' => $GLOBALS["afcnfolder"]."\annexe_7_rx_pedia.xlsx",
          'fileout' => $tmpdir.'/'.$file
        );



        $fields["s".$modifnbr]=0;
        $fields["r".$modifnbr]=2;
        $fields["c".$modifnbr]=4;
        $fields["v".$modifnbr]=$modality->inst_name;
        $modifnbr++;
        $fields["s".$modifnbr]=0;
        $fields["r".$modifnbr]=3;
        $fields["c".$modifnbr]=4;
        $fields["v".$modifnbr]=$modality->inst_address;
        $modifnbr++;
        $fields["s".$modifnbr]=0;
        $fields["r".$modifnbr]=4;
        $fields["c".$modifnbr]=4;
        $fields["v".$modifnbr]=$modality->mod_name;
        $modifnbr++;
        $fields["s".$modifnbr]=0;
        $fields["r".$modifnbr]=5;
        $fields["c".$modifnbr]=4;
        $fields["v".$modifnbr]=$modality->mod_brandxlsvalue;
        $modifnbr++;
        $fields["s".$modifnbr]=0;
        $fields["r".$modifnbr]=6;
        $fields["c".$modifnbr]=4;
        $fields["v".$modifnbr]=$modality->mod_model;
        $modifnbr++;
        $fields["s".$modifnbr]=0;
        $fields["r".$modifnbr]=7;
        $fields["c".$modifnbr]=4;
        $fields["v".$modifnbr]=$modality->sensor_type;
        $modifnbr++;

        /// TO ADD Date dernière vérif DAP
        $fields["s".$modifnbr]=0;
        $fields["r".$modifnbr]=9;
        $fields["c".$modifnbr]=4;
        $fields["v".$modifnbr]=$modality->desc_export;
        $modifnbr++;

        ////////////////
        $fields["s".$modifnbr]=0;
        $fields["r".$modifnbr]=11;
        $fields["c".$modifnbr]=4;
        $fields["v".$modifnbr]=$modality->contact_name;
        $modifnbr++;
        $fields["s".$modifnbr]=0;
        $fields["r".$modifnbr]=11;
        $fields["c".$modifnbr]=9;
        $fields["v".$modifnbr]=$modality->contact_fname;
        $modifnbr++;
        $fields["s".$modifnbr]=0;
        $fields["r".$modifnbr]=11;
        $fields["c".$modifnbr]=12;
        $fields["v".$modifnbr]=$modality->contact_mail;
        $modifnbr++;

        $fields["s".$modifnbr]=0;
        $fields["r".$modifnbr]=12;
        $fields["c".$modifnbr]=4;
        $fields["v".$modifnbr]=$modality->phys_name;
        $modifnbr++;
        $fields["s".$modifnbr]=0;
        $fields["r".$modifnbr]=12;
        $fields["c".$modifnbr]=9;
        $fields["v".$modifnbr]=$modality->phys_fname;
        $modifnbr++;
        $fields["s".$modifnbr]=0;
        $fields["r".$modifnbr]=12;
        $fields["c".$modifnbr]=12;
        $fields["v".$modifnbr]=$modality->phys_mail;
        $modifnbr++;

        $fields["s".$modifnbr]=0;
        $fields["r".$modifnbr]=13;
        $fields["c".$modifnbr]=4;
        $fields["v".$modifnbr]=$modality->valid_name;
        $modifnbr++;
        $fields["s".$modifnbr]=0;
        $fields["r".$modifnbr]=13;
        $fields["c".$modifnbr]=9;
        $fields["v".$modifnbr]=$modality->valid_fname;
        $modifnbr++;
        $fields["s".$modifnbr]=0;
        $fields["r".$modifnbr]=13;
        $fields["c".$modifnbr]=12;
        $fields["v".$modifnbr]=$modality->valid_mail;
        $modifnbr++;
        $studynbr=0;
        $prev_stuid=-1;
        while($row=pg_fetch_row($query))
        {

          $stuid=$row[0];
          if($stuid!=$prev_stuid)
          {
            $fields["s".$modifnbr]=0;
            $fields["r".$modifnbr]=$nbrlines+19;
            $fields["c".$modifnbr]=0;
            $fields["v".$modifnbr]=++$studynbr;
            $modifnbr++;
            $prev_stuid=$stuid;

            $fields["s".$modifnbr]=0;
            $fields["r".$modifnbr]=$nbrlines+19;
            $fields["c".$modifnbr]=1;
            $fields["v".$modifnbr]=$row[2];
            $modifnbr++;

            $fields["s".$modifnbr]=0;
            $fields["r".$modifnbr]=$nbrlines+19;
            $fields["c".$modifnbr]=2;
            $fields["v".$modifnbr]=$row[3];
            $modifnbr++;

            $fields["s".$modifnbr]=0;
            $fields["r".$modifnbr]=$nbrlines+19;
            $fields["c".$modifnbr]=3;
            $fields["v".$modifnbr]=$row[4];
            $modifnbr++;

			$fields["s".$modifnbr]=0;
            $fields["r".$modifnbr]=$nbrlines+19;
            $fields["c".$modifnbr]=4;
            $fields["v".$modifnbr]=$row[5];
            $modifnbr++;

			$fields["s".$modifnbr]=0;
            $fields["r".$modifnbr]=$nbrlines+19;
            $fields["c".$modifnbr]=5;
            $fields["v".$modifnbr]=$row[6];
            $modifnbr++;

            $fields["s".$modifnbr]=0;
            $fields["r".$modifnbr]=$nbrlines+19;
            $fields["c".$modifnbr]=12;
            $fields["v".$modifnbr]=$row[11];
            $modifnbr++;


            $fields["s".$modifnbr]=0;
            $fields["r".$modifnbr]=$nbrlines+19;
            $fields["c".$modifnbr]=14;
            $fields["v".$modifnbr]=$row[13];
            $modifnbr++;

            $fields["s".$modifnbr]=0;
            $fields["r".$modifnbr]=$nbrlines+19;
            $fields["c".$modifnbr]=8;
            $fields["v".$modifnbr]=$row[9];
            $modifnbr++;

          }
          $fields["s".$modifnbr]=0;
          $fields["r".$modifnbr]=$nbrlines+19;
          $fields["c".$modifnbr]=11;
          $fields["v".$modifnbr]=$row[10];
          $modifnbr++;
          $nbrlines++;
        }
        $fields["modifnbr"]=$modifnbr;

        $tmpfilename = create_tmp_datafile($fields,$modifnbr);
        $fields["source_datafile"] = $tmpfilename;
        $fields["offset_0"] = "22";
        $fields["rolloverrow"] = "1";

        $postdata = http_build_query(
          $fields
        );



        $opts = array('http' =>
          array(
            'method'  => 'POST',
            'content' => $postdata
            //'header'  => 'Content-type: application/x-www-form-urlencoded',

          )
        );
        $context  = stream_context_create($opts);


//        $url= "http://localhost".$EODACS_FOLDER."/eoDACS/afcnexport.php";


        $result=file_get_contents($afcnexport_url,false,$context);
        $listoffile[] = $file;
      }

	}
    if($type == AFCN_ANNEXE3)
    {
      /*$querysql="select study.stu_id,serie.ser_id,to_char(study.date,'DD/MM/YYYY') as sdate,
      patient.sex,EXTRACT(year FROM age(study.date,patient.birthdate)) as age,val->>'23' as kv,
      round(coalesce((val->>'79')::numeric,(val->>'32')::numeric*(val->>'30')::numeric/1000),2) as mas,
      total_fluoro_time/1000,serie.dap_auto/10,count(*) over (partition by serie.stu_id) as nbr_serie,
      upper(replace(operator,'^','')) as initial,'Détail de '||(count(*) over (partition by serie.stu_id)) ||' acquisitions' as remarque
      from study,serie, patient,raw_serie_description_link
      where
      serie.ser_id = raw_serie_description_link.ser_id and raw_serie_description_link.selected = true
	  and patient.pat_id = study.pat_id and serie.stu_id = study.stu_id and serie.dap_auto is not null
      and study.pro_id in({$modality->profile_list})	{$condstudy} order by study.date, EXTRACT(year FROM age(study.date,patient.birthdate)), serie.stu_id,serie.ser_id;";
	  */
    //dap_total en mGy.cm²
	   $querysql="select * from (select distinct (study.stu_id),'' as ser_id,to_char(study.date,'DD/MM/YYYY') as sdate,
      patient.sex,EXTRACT(year FROM age(study.date,patient.birthdate)) as age,'' as kv,
      '' as mas,
      total_fluoro_time/1000 as fluoro_time,dap_total as daptotal,sum((acquisition_type not ilike 'fluoro%')::integer) over (partition by serie.stu_id) as nbr_serie,
      upper(replace(operator,'^','')) as initial,'' as remarque,study.date
      from study,serie, patient,raw_serie_description_link
      where
      serie.ser_id = raw_serie_description_link.ser_id and raw_serie_description_link.selected = true
	  and patient.pat_id = study.pat_id and serie.stu_id = study.stu_id
      and study.pro_id in({$modality->profile_list}) {$condstudy}) as exportlistorder order by date, age,stu_id;";


	  if($query=pg_query($conn,$querysql))
      {
      //error_log("-- query OK");
        $modifnbr=0;
        $nbrlines=0;
        $file=$modality->filename."_.xlsx";

        $fields = array(
          'filename' => $GLOBALS["afcnfolder"]."\annexe_3_rx.xlsx",
          'fileout' => $tmpdir.'/'.$file
        );


        $fields["s".$modifnbr]=0;
        $fields["r".$modifnbr]=2;
        $fields["c".$modifnbr]=4;
        $fields["v".$modifnbr]=$modality->inst_name;
        $modifnbr++;
        $fields["s".$modifnbr]=0;
        $fields["r".$modifnbr]=3;
        $fields["c".$modifnbr]=4;
        $fields["v".$modifnbr]=$modality->inst_address;
        $modifnbr++;
        $fields["s".$modifnbr]=0;
        $fields["r".$modifnbr]=4;
        $fields["c".$modifnbr]=4;
        $fields["v".$modifnbr]=$modality->mod_name;
        $modifnbr++;
        $fields["s".$modifnbr]=0;
        $fields["r".$modifnbr]=5;
        $fields["c".$modifnbr]=4;
        $fields["v".$modifnbr]=$modality->mod_brandxlsvalue;
        $modifnbr++;
        $fields["s".$modifnbr]=0;
        $fields["r".$modifnbr]=6;
        $fields["c".$modifnbr]=4;
        $fields["v".$modifnbr]=$modality->mod_model;
        $modifnbr++;
        $fields["s".$modifnbr]=0;
        $fields["r".$modifnbr]=7;
        $fields["c".$modifnbr]=4;
        $fields["v".$modifnbr]=$modality->sensor_type;
        $modifnbr++;

        /// TO ADD Date dernière vérif DAP
        $fields["s".$modifnbr]=0;
        $fields["r".$modifnbr]=9;
        $fields["c".$modifnbr]=4;
        $fields["v".$modifnbr]=$modality->desc_export;
        $modifnbr++;

        ////////////////
        $fields["s".$modifnbr]=0;
        $fields["r".$modifnbr]=11;
        $fields["c".$modifnbr]=4;
        $fields["v".$modifnbr]=$modality->contact_name;
        $modifnbr++;
        $fields["s".$modifnbr]=0;
        $fields["r".$modifnbr]=11;
        $fields["c".$modifnbr]=8;
        $fields["v".$modifnbr]=$modality->contact_fname;
        $modifnbr++;
        $fields["s".$modifnbr]=0;
        $fields["r".$modifnbr]=11;
        $fields["c".$modifnbr]=11;
        $fields["v".$modifnbr]=$modality->contact_mail;
        $modifnbr++;

        $fields["s".$modifnbr]=0;
        $fields["r".$modifnbr]=12;
        $fields["c".$modifnbr]=4;
        $fields["v".$modifnbr]=$modality->phys_name;
        $modifnbr++;
        $fields["s".$modifnbr]=0;
        $fields["r".$modifnbr]=12;
        $fields["c".$modifnbr]=8;
        $fields["v".$modifnbr]=$modality->phys_fname;
        $modifnbr++;
        $fields["s".$modifnbr]=0;
        $fields["r".$modifnbr]=12;
        $fields["c".$modifnbr]=11;
        $fields["v".$modifnbr]=$modality->phys_mail;
        $modifnbr++;

        $fields["s".$modifnbr]=0;
        $fields["r".$modifnbr]=13;
        $fields["c".$modifnbr]=4;
        $fields["v".$modifnbr]=$modality->valid_name;
        $modifnbr++;
        $fields["s".$modifnbr]=0;
        $fields["r".$modifnbr]=13;
        $fields["c".$modifnbr]=8;
        $fields["v".$modifnbr]=$modality->valid_fname;
        $modifnbr++;
        $fields["s".$modifnbr]=0;
        $fields["r".$modifnbr]=13;
        $fields["c".$modifnbr]=11;
        $fields["v".$modifnbr]=$modality->valid_mail;
        $modifnbr++;
        $studynbr=0;
        $prev_stuid=-1;
        while($row=pg_fetch_row($query))
        {

          $stuid=$row[0];
          if($stuid!=$prev_stuid)
          {
            $fields["s".$modifnbr]=0;
            $fields["r".$modifnbr]=$nbrlines+19;
            $fields["c".$modifnbr]=0;
            $fields["v".$modifnbr]=++$studynbr;
            $modifnbr++;
            $prev_stuid=$stuid;

            $fields["s".$modifnbr]=0;
            $fields["r".$modifnbr]=$nbrlines+19;
            $fields["c".$modifnbr]=1;
            $fields["v".$modifnbr]=$row[2];
            $modifnbr++;

            $fields["s".$modifnbr]=0;
            $fields["r".$modifnbr]=$nbrlines+19;
            $fields["c".$modifnbr]=2;
            $fields["v".$modifnbr]=$row[3];
            $modifnbr++;

            $fields["s".$modifnbr]=0;
            $fields["r".$modifnbr]=$nbrlines+19;
            $fields["c".$modifnbr]=3;
            $fields["v".$modifnbr]=$row[4];
            $modifnbr++;

            $fields["s".$modifnbr]=0;
            $fields["r".$modifnbr]=$nbrlines+19;
            $fields["c".$modifnbr]=11;
            $fields["v".$modifnbr]=$row[9];
            $modifnbr++;


            $fields["s".$modifnbr]=0;
            $fields["r".$modifnbr]=$nbrlines+19;
            $fields["c".$modifnbr]=13;
            $fields["v".$modifnbr]=$row[11];
            $modifnbr++;

            $fields["s".$modifnbr]=0;
            $fields["r".$modifnbr]=$nbrlines+19;
            $fields["c".$modifnbr]=6;
            $fields["v".$modifnbr]=$row[7];
            $modifnbr++;

          }
          $fields["s".$modifnbr]=0;
          $fields["r".$modifnbr]=$nbrlines+19;
          $fields["c".$modifnbr]=10;
          $fields["v".$modifnbr]=$row[8];
          $modifnbr++;
          $nbrlines++;
        }
        $fields["modifnbr"]=$modifnbr;

        $tmpfilename = create_tmp_datafile($fields,$modifnbr);
        $fields["source_datafile"] = $tmpfilename;
        $fields["offset_0"] = "22";
        $fields["rolloverrow"] = "1";

        $postdata = http_build_query(
          $fields
        );

        $opts = array('http' =>
          array(
            'method'  => 'POST',
            'content' => $postdata
            //'header'  => 'Content-type: application/x-www-form-urlencoded',

          )
        );
        $context  = stream_context_create($opts);


       //$url= "http://localhost".$EODACS_FOLDER."/eoDACS/afcnexport.php";


        $result=file_get_contents($afcnexport_url,false,$context);
        $listoffile[] = $file;
      }
    }
	if($type==AFCN_ANNEXE8)
	{
    $excluded_serie = " and serie.acquisition_type not in('Constant Angle Acquisition')";

		$querysql="select study.stu_id,serie.ser_id,to_char(study.date,'DD/MM/YYYY') as sdate,
              patient.sex,EXTRACT(year FROM age(study.date,patient.birthdate)) as age
              ,val->>'23' as kv,
              case when val->>'120' is not null and val->>'120' !='NONE' then 'OUI'
                   when serie.acquisition_type ilike '%constant%' then 'NON'
                   when ((val->>'118')::numeric - (val->>'30')::numeric)!=0 then 'OUI'
                   else 'NON' end,case when val->>'121' ='ITERATIVE' then 'OUI' else 'NON' end as iteratif,case when study.description_auto ilike '%low%' then 'OUI' else 'NON' end as lowdose,case when val->>'121'='Diagnostic radiography with contrast media' then 'OUI' when val->>'121'='CT without contrast' then 'NON' when serie.description_auto ilike '%+c%' then 'OUI' else 'NON' end as contraste ,val->>'34' as ctdivol,val->>'36' as dlp,count(*) over (partition by serie.stu_id) as nbr_serie,
  			upper(replace(operator,'^','')) as initial,'Détail de '||(count(*) over (partition by serie.stu_id)) ||' acquisitions' as remarque
			,study.pat_weight, study.pat_size from study,serie, patient,raw_serie_description_link
  			where
			raw_serie_description_link.ser_id = serie.ser_id and raw_serie_description_link.selected = true
  			and patient.pat_id = study.pat_id and serie.stu_id = study.stu_id
  			and study.pro_id in({$modality->profile_list})	{$condstudy} {$excluded_serie} order by study.date, EXTRACT(year FROM age(study.date,patient.birthdate)), serie.stu_id,serie.ser_id";

    if($query=pg_query($conn,$querysql))
  		{

  			$modifnbr=0;
  			$nbrlines=0;

  			$file=$modality->filename."_.xlsx";

  			$fields = array(
  				'filename' => $GLOBALS["afcnfolder"]."\annexe_8_ct_pedia.xlsx",
  				'fileout' => $tmpdir.'/'.$file
  			);

  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=12;
  			$fields["c".$modifnbr]=4;
  			$fields["v".$modifnbr]=$from;
  			$modifnbr++;

  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=12;
  			$fields["c".$modifnbr]=8;
  			$fields["v".$modifnbr]=$to;
  			$modifnbr++;

  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=2;
  			$fields["c".$modifnbr]=4;
  			$fields["v".$modifnbr]=$modality->inst_name;
  			$modifnbr++;
  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=3;
  			$fields["c".$modifnbr]=4;
  			$fields["v".$modifnbr]=$modality->inst_address;
  			$modifnbr++;
  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=4;
  			$fields["c".$modifnbr]=4;
  			$fields["v".$modifnbr]=$modality->mod_name;
  			$modifnbr++;
  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=5;
  			$fields["c".$modifnbr]=4;
  			$fields["v".$modifnbr]=$modality->mod_brandxlsvalue;
  			$modifnbr++;
  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=6;
  			$fields["c".$modifnbr]=4;
  			$fields["v".$modifnbr]=$modality->mod_model;
  			$modifnbr++;
  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=7;
  			$fields["c".$modifnbr]=4;
  			$fields["v".$modifnbr]=$modality->nbr_slices;
  			$modifnbr++;
  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=8;
  			$fields["c".$modifnbr]=4;
  			$fields["v".$modifnbr]=$modality->log_reconstruction;
  			$modifnbr++;
  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=9;
  			$fields["c".$modifnbr]=4;
  			$fields["v".$modifnbr]=$modality->mod_insdate;
  			$modifnbr++;
  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=10;
  			$fields["c".$modifnbr]=4;
  			$fields["v".$modifnbr]=$modality->desc_export;
  			$modifnbr++;

  			//////////////////////////////////////////////////////////
  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=12;
  			$fields["c".$modifnbr]=4;
  			$fields["v".$modifnbr]=$modality->contact_name;
  			$modifnbr++;
  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=12;
  			$fields["c".$modifnbr]=9;
  			$fields["v".$modifnbr]=$modality->contact_fname;
  			$modifnbr++;
  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=12;
  			$fields["c".$modifnbr]=12;
  			$fields["v".$modifnbr]=$modality->contact_mail;
  			$modifnbr++;

  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=13;
  			$fields["c".$modifnbr]=4;
  			$fields["v".$modifnbr]=$modality->phys_name;
  			$modifnbr++;
  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=13;
  			$fields["c".$modifnbr]=9;
  			$fields["v".$modifnbr]=$modality->phys_fname;
  			$modifnbr++;
  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=13;
  			$fields["c".$modifnbr]=12;
  			$fields["v".$modifnbr]=$modality->phys_mail;
  			$modifnbr++;

  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=14;
  			$fields["c".$modifnbr]=4;
  			$fields["v".$modifnbr]=$modality->valid_name;
  			$modifnbr++;
  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=14;
  			$fields["c".$modifnbr]=9;
  			$fields["v".$modifnbr]=$modality->valid_fname;
  			$modifnbr++;
  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=14;
  			$fields["c".$modifnbr]=12;
  			$fields["v".$modifnbr]=$modality->valid_mail;
  			$modifnbr++;
  			$studynbr=0;
  			$prev_stuid=-1;
  			while($row=pg_fetch_row($query))
  			{
  				$stuid=$row[0];
  				if($stuid!=$prev_stuid)
  				{
  					$fields["s".$modifnbr]=0;
  					$fields["r".$modifnbr]=$nbrlines+20;
  					$fields["c".$modifnbr]=0;
  					$fields["v".$modifnbr]=++$studynbr;
  					$modifnbr++;
  					$prev_stuid=$stuid;

  					$fields["s".$modifnbr]=0;
  					$fields["r".$modifnbr]=$nbrlines+20;
  					$fields["c".$modifnbr]=1;
  					$fields["v".$modifnbr]=$row[2];
  					$modifnbr++;

  					$fields["s".$modifnbr]=0;
  					$fields["r".$modifnbr]=$nbrlines+20;
  					$fields["c".$modifnbr]=2;
  					$fields["v".$modifnbr]=$row[3];
  					$modifnbr++;

  					$fields["s".$modifnbr]=0;
  					$fields["r".$modifnbr]=$nbrlines+20;
  					$fields["c".$modifnbr]=3;
  					$fields["v".$modifnbr]=$row[4];
  					$modifnbr++;

					$fields["s".$modifnbr]=0;
  					$fields["r".$modifnbr]=$nbrlines+20;
  					$fields["c".$modifnbr]=4;
  					$fields["v".$modifnbr]=$row[15];
  					$modifnbr++;

					$fields["s".$modifnbr]=0;
  					$fields["r".$modifnbr]=$nbrlines+20;
  					$fields["c".$modifnbr]=5;
  					$fields["v".$modifnbr]=$row[16];
  					$modifnbr++;

  					$fields["s".$modifnbr]=0;
  					$fields["r".$modifnbr]=$nbrlines+20;
  					$fields["c".$modifnbr]=13;
  					$fields["v".$modifnbr]=$row[12];
  					$modifnbr++;

					$nomprenom=explode(" ",$row[13]);
					$initials = "";
					foreach ($nomprenom as $w) {
					  $initials .= $w[0];
					}



					$fields["s".$modifnbr]=0;
					$fields["r".$modifnbr]=$nbrlines+20;
					$fields["c".$modifnbr]=14;
					$fields["v".$modifnbr]=$initials;
					$modifnbr++;

  					$fields["s".$modifnbr]=0;
  					$fields["r".$modifnbr]=$nbrlines+20;
  					$fields["c".$modifnbr]=15;
  					$fields["v".$modifnbr]=$row[14];
  					$modifnbr++;
  				}
  				$fields["s".$modifnbr]=0;
  				$fields["r".$modifnbr]=$nbrlines+20;
  				$fields["c".$modifnbr]=6;
  				$fields["v".$modifnbr]=$row[5];
  				$modifnbr++;

  				$fields["s".$modifnbr]=0;
  				$fields["r".$modifnbr]=$nbrlines+20;
  				$fields["c".$modifnbr]=7;
  				$fields["v".$modifnbr]=$row[6];
  				$modifnbr++;

  				$fields["s".$modifnbr]=0;
  				$fields["r".$modifnbr]=$nbrlines+20;
  				$fields["c".$modifnbr]=8;
  				$fields["v".$modifnbr]=$row[7];
  				$modifnbr++;

  				$fields["s".$modifnbr]=0;
  				$fields["r".$modifnbr]=$nbrlines+20;
  				$fields["c".$modifnbr]=9;
  				$fields["v".$modifnbr]=$row[8];
  				$modifnbr++;

  				$fields["s".$modifnbr]=0;
  				$fields["r".$modifnbr]=$nbrlines+20;
  				$fields["c".$modifnbr]=10;
  				$fields["v".$modifnbr]=$row[9];
  				$modifnbr++;

  				$fields["s".$modifnbr]=0;
  				$fields["r".$modifnbr]=$nbrlines+20;
  				$fields["c".$modifnbr]=11;
  				$fields["v".$modifnbr]=$row[10];
  				$modifnbr++;

  				$fields["s".$modifnbr]=0;
  				$fields["r".$modifnbr]=$nbrlines+20;
  				$fields["c".$modifnbr]=12;
  				$fields["v".$modifnbr]=$row[11];
  				$modifnbr++;



  				$nbrlines++;
  			}
  			$fields["modifnbr"]=$modifnbr;

        $tmpfilename = create_tmp_datafile($fields,$modifnbr);
        $fields["source_datafile"] = $tmpfilename;
        $fields["offset_0"] = "22";
        $fields["rolloverrow"] = "1";

  			$postdata = http_build_query(
  				$fields
  			);


  			$opts = array('http' =>
  				array(
  					'method'  => 'POST',
  					'content' => $postdata
  					//'header'  => 'Content-type: application/x-www-form-urlencoded',

  				)
  			);
  			$context  = stream_context_create($opts);

  			//$url= "http://localhost".$EODACS_FOLDER."/eoDACS/afcnexport.php";

  			$result=file_get_contents($afcnexport_url,false,$context);
  			$listoffile[] = $file;
  		}
	}
    if($type == AFCN_ANNEXE5)
  	{


      $excluded_serie = " and serie.acquisition_type not in('Constant Angle Acquisition')";

  		$querysql = "select study.stu_id,serie.ser_id,to_char(study.date,'DD/MM/YYYY') as sdate
			,patient.sex,EXTRACT(year FROM age(study.date,patient.birthdate)) as age
            ,val->>'23' as kv,
            case when val->>'120' is not null and val->>'120' !='NONE' then 'OUI'
                 when serie.acquisition_type ilike '%constant%' then 'NON'
                 when ((val->>'118')::numeric - (val->>'30')::numeric)!=0 then 'OUI'
			     else 'NON'
			end as tcm
			,case when val->>'121' ='ITERATIVE' then 'OUI' else 'NON' end as iteratif
			,case when study.description_auto ilike '%low%' then 'OUI' else 'NON' end as lowdose
			,case when val->>'121'='Diagnostic radiography with contrast media' then 'OUI' when val->>'121'='CT without contrast' then 'NON' when serie.description_auto ilike '%+c%' then 'OUI' else 'NON' end as contraste ,val->>'34' as ctdivol,val->>'36' as dlp,count(*) over (partition by serie.stu_id) as nbr_serie,
  			upper(replace(operator,'^','')) as initial,'Détail de '||(count(*) over (partition by serie.stu_id)) ||' acquisitions' as remarque
from study,serie, patient,raw_serie_description_link
  			where
			raw_serie_description_link.ser_id = serie.ser_id and raw_serie_description_link.selected = true
  			and patient.pat_id = study.pat_id and serie.stu_id = study.stu_id
  			and study.pro_id in({$modality->profile_list})	{$condstudy} {$excluded_serie} order by study.date, EXTRACT(year FROM age(study.date,patient.birthdate)), serie.stu_id,serie.ser_id";

  		//error_log($querysql);

  		if($query=pg_query($conn,$querysql))
  		{

  			$modifnbr=0;
  			$nbrlines=0;

  			$file=$modality->filename."_.xlsx";

  			$fields = array(
  				'filename' => $GLOBALS["afcnfolder"]."\annexe_5_ct.xlsx",
  				'fileout' => $tmpdir.'/'.$file
  			);

  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=12;
  			$fields["c".$modifnbr]=4;
  			$fields["v".$modifnbr]=$from;
  			$modifnbr++;

  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=12;
  			$fields["c".$modifnbr]=8;
  			$fields["v".$modifnbr]=$to;
  			$modifnbr++;

  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=2;
  			$fields["c".$modifnbr]=4;
  			$fields["v".$modifnbr]=$modality->inst_name;
  			$modifnbr++;
  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=3;
  			$fields["c".$modifnbr]=4;
  			$fields["v".$modifnbr]=$modality->inst_address;
  			$modifnbr++;
  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=4;
  			$fields["c".$modifnbr]=4;
  			$fields["v".$modifnbr]=$modality->mod_name;
  			$modifnbr++;
  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=5;
  			$fields["c".$modifnbr]=4;
  			$fields["v".$modifnbr]=$modality->mod_brandxlsvalue;
  			$modifnbr++;
  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=6;
  			$fields["c".$modifnbr]=4;
  			$fields["v".$modifnbr]=$modality->mod_model;
  			$modifnbr++;
  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=7;
  			$fields["c".$modifnbr]=4;
  			$fields["v".$modifnbr]=$modality->nbr_slices;
  			$modifnbr++;
  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=8;
  			$fields["c".$modifnbr]=4;
  			$fields["v".$modifnbr]=$modality->log_reconstruction;
  			$modifnbr++;
  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=9;
  			$fields["c".$modifnbr]=4;
  			$fields["v".$modifnbr]=$modality->mod_insdate;
  			$modifnbr++;
  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=10;
  			$fields["c".$modifnbr]=4;
  			$fields["v".$modifnbr]=$modality->desc_export;
  			$modifnbr++;

  			//////////////////////////////////////////////////////////
  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=12;
  			$fields["c".$modifnbr]=4;
  			$fields["v".$modifnbr]=$modality->contact_name;
  			$modifnbr++;
  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=12;
  			$fields["c".$modifnbr]=8;
  			$fields["v".$modifnbr]=$modality->contact_fname;
  			$modifnbr++;
  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=12;
  			$fields["c".$modifnbr]=11;
  			$fields["v".$modifnbr]=$modality->contact_mail;
  			$modifnbr++;

  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=13;
  			$fields["c".$modifnbr]=4;
  			$fields["v".$modifnbr]=$modality->phys_name;
  			$modifnbr++;
  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=13;
  			$fields["c".$modifnbr]=8;
  			$fields["v".$modifnbr]=$modality->phys_fname;
  			$modifnbr++;
  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=13;
  			$fields["c".$modifnbr]=11;
  			$fields["v".$modifnbr]=$modality->phys_mail;
  			$modifnbr++;

  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=14;
  			$fields["c".$modifnbr]=4;
  			$fields["v".$modifnbr]=$modality->valid_name;
  			$modifnbr++;
  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=14;
  			$fields["c".$modifnbr]=8;
  			$fields["v".$modifnbr]=$modality->valid_fname;
  			$modifnbr++;
  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=14;
  			$fields["c".$modifnbr]=11;
  			$fields["v".$modifnbr]=$modality->valid_mail;
  			$modifnbr++;
  			$studynbr=0;
  			$prev_stuid=-1;
  			while($row=pg_fetch_row($query))
  			{
  				$stuid=$row[0];
  				if($stuid!=$prev_stuid)
  				{
  					$fields["s".$modifnbr]=0;
  					$fields["r".$modifnbr]=$nbrlines+20;
  					$fields["c".$modifnbr]=0;
  					$fields["v".$modifnbr]=++$studynbr;
  					$modifnbr++;
  					$prev_stuid=$stuid;

  					$fields["s".$modifnbr]=0;
  					$fields["r".$modifnbr]=$nbrlines+20;
  					$fields["c".$modifnbr]=1;
  					$fields["v".$modifnbr]=$row[2];
  					$modifnbr++;

  					$fields["s".$modifnbr]=0;
  					$fields["r".$modifnbr]=$nbrlines+20;
  					$fields["c".$modifnbr]=2;
  					$fields["v".$modifnbr]=$row[3];
  					$modifnbr++;

  					$fields["s".$modifnbr]=0;
  					$fields["r".$modifnbr]=$nbrlines+20;
  					$fields["c".$modifnbr]=3;
  					$fields["v".$modifnbr]=$row[4];
  					$modifnbr++;



  					$fields["s".$modifnbr]=0;
  					$fields["r".$modifnbr]=$nbrlines+20;
  					$fields["c".$modifnbr]=11;
  					$fields["v".$modifnbr]=$row[12];
  					$modifnbr++;

					$nomprenom=explode(" ",$row[13]);
					$initials = "";
					foreach ($nomprenom as $w) {
					  $initials .= $w[0];
					}



					$fields["s".$modifnbr]=0;
					$fields["r".$modifnbr]=$nbrlines+20;
					$fields["c".$modifnbr]=12;
					$fields["v".$modifnbr]=$initials;
					$modifnbr++;

  					$fields["s".$modifnbr]=0;
  					$fields["r".$modifnbr]=$nbrlines+20;
  					$fields["c".$modifnbr]=13;
  					$fields["v".$modifnbr]=$row[14];
  					$modifnbr++;
  				}
  				$fields["s".$modifnbr]=0;
  				$fields["r".$modifnbr]=$nbrlines+20;
  				$fields["c".$modifnbr]=4;
  				$fields["v".$modifnbr]=$row[5];
  				$modifnbr++;

  				$fields["s".$modifnbr]=0;
  				$fields["r".$modifnbr]=$nbrlines+20;
  				$fields["c".$modifnbr]=5;
  				$fields["v".$modifnbr]=$row[6];
  				$modifnbr++;

  				$fields["s".$modifnbr]=0;
  				$fields["r".$modifnbr]=$nbrlines+20;
  				$fields["c".$modifnbr]=6;
  				$fields["v".$modifnbr]=$row[7];
  				$modifnbr++;

  				$fields["s".$modifnbr]=0;
  				$fields["r".$modifnbr]=$nbrlines+20;
  				$fields["c".$modifnbr]=7;
  				$fields["v".$modifnbr]=$row[8];
  				$modifnbr++;

  				$fields["s".$modifnbr]=0;
  				$fields["r".$modifnbr]=$nbrlines+20;
  				$fields["c".$modifnbr]=8;
  				$fields["v".$modifnbr]=$row[9];
  				$modifnbr++;

  				$fields["s".$modifnbr]=0;
  				$fields["r".$modifnbr]=$nbrlines+20;
  				$fields["c".$modifnbr]=9;
  				$fields["v".$modifnbr]=$row[10];
  				$modifnbr++;

  				$fields["s".$modifnbr]=0;
  				$fields["r".$modifnbr]=$nbrlines+20;
  				$fields["c".$modifnbr]=10;
  				$fields["v".$modifnbr]=$row[11];
  				$modifnbr++;



  				$nbrlines++;
  			}
  			$fields["modifnbr"]=$modifnbr;

        $tmpfilename = create_tmp_datafile($fields,$modifnbr);
        $fields["source_datafile"] = $tmpfilename;
        $fields["offset_0"] = "22";
        $fields["rolloverrow"] = "1";

  			$postdata = http_build_query(
  				$fields
  			);


  			$opts = array('http' =>
  				array(
  					'method'  => 'POST',
  					'content' => $postdata
  					//'header'  => 'Content-type: application/x-www-form-urlencoded',

  				)
  			);
  			$context  = stream_context_create($opts);

  			//$url= "http://localhost".$EODACS_FOLDER."/eoDACS/afcnexport.php";

  			$result=file_get_contents($afcnexport_url,false,$context);
  			$listoffile[] = $file;
  		}
  	}
    if($type == AFCN_ANNEXE6)
    {
        $querysql = "select  study.stu_id,serie.ser_id,to_char(study.date,'DD/MM/YYYY') as sdate,
      EXTRACT(year FROM age(study.date,patient.birthdate)) as age,
      left((val->>'44'),1)::text||(val->>'75acr')::text as incidence,
      val->>'46' as compression,
      val->>'73' as forcecompression,
      case when val->>'74' ilike '%tungsten%' then 'W' when val->>'74' ilike '%rhodium%' then 'Rh' when val->>'74' ilike '%molybdenum%' then 'Mo' else val->>'74' end as anode,
      case when val->>'72' ilike '%aluminum%' then 'Al' when val->>'72' ilike '%copper%' then 'Cu' when val->>'72' ilike '%silver%' then 'Ag' when val->>'72' ilike '%molybdenum%' then 'Mo' when val->>'72' ilike '%rhodium%' then 'Rh' else val->>'72' end  as filter,
      val->>'23'as kv,
      val->>'79'as mas,
      replace(val->>'42','.',',') as dgm,
      upper(replace(operator,'^','')) as initial,serie.acquisition_type as remarque
	  from study,serie, patient,raw_serie_description_link
      where
      serie.ser_id = raw_serie_description_link.ser_id and raw_serie_description_link.selected = true
	  and patient.pat_id = study.pat_id and serie.stu_id = study.stu_id
      and study.pro_id in({$modality->profile_list})	{$condstudy} order by study.date, EXTRACT(year FROM age(study.date,patient.birthdate)), serie.stu_id,serie.ser_id;
	  ";

  //  error_log($querysql);
		if($query=pg_query($conn,$querysql))
  		{

  			$modifnbr=0;
  			$nbrlines=17;

  			$file=$modality->filename."_.xlsx";

  			$fields = array(
  				'filename' => $GLOBALS["afcnfolder"]."\annexe_6_mg.xlsx",
  				'fileout' => $tmpdir.'/'.$file
  			);

  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=2;
  			$fields["c".$modifnbr]=4;
  			$fields["v".$modifnbr]=$modality->inst_name;
  			$modifnbr++;
  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=3;
  			$fields["c".$modifnbr]=4;
  			$fields["v".$modifnbr]=$modality->inst_address;
  			$modifnbr++;
  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=4;
  			$fields["c".$modifnbr]=4;
  			$fields["v".$modifnbr]=$modality->mod_name;
  			$modifnbr++;
  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=5;
  			$fields["c".$modifnbr]=4;
  			$fields["v".$modifnbr]=$modality->mod_brandxlsvalue;
  			$modifnbr++;
  			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=6;
  			$fields["c".$modifnbr]=4;
  			$fields["v".$modifnbr]=$modality->mod_model;
			$modifnbr++;
			$fields["s".$modifnbr]=0;
  			$fields["r".$modifnbr]=7;
  			$fields["c".$modifnbr]=4;
  			$fields["v".$modifnbr]=$modality->sensor_type;
			$modifnbr++;
			$fields["s".$modifnbr]=0;
			$fields["r".$modifnbr]=9;
			$fields["c".$modifnbr]=4;
			$fields["v".$modifnbr]=$modality->contact_name;
			$modifnbr++;
			$fields["s".$modifnbr]=0;
			$fields["r".$modifnbr]=9;
			$fields["c".$modifnbr]=8;
			$fields["v".$modifnbr]=$modality->contact_fname;
			$modifnbr++;
			$fields["s".$modifnbr]=0;
			$fields["r".$modifnbr]=9;
			$fields["c".$modifnbr]=12;
			$fields["v".$modifnbr]=$modality->contact_mail;
			$modifnbr++;

			$fields["s".$modifnbr]=0;
			$fields["r".$modifnbr]=10;
			$fields["c".$modifnbr]=4;
			$fields["v".$modifnbr]=$modality->phys_name;
			$modifnbr++;
			$fields["s".$modifnbr]=0;
			$fields["r".$modifnbr]=10;
			$fields["c".$modifnbr]=8;
			$fields["v".$modifnbr]=$modality->phys_fname;
			$modifnbr++;
			$fields["s".$modifnbr]=0;
			$fields["r".$modifnbr]=10;
			$fields["c".$modifnbr]=12;
			$fields["v".$modifnbr]=$modality->phys_mail;
			$modifnbr++;

			$fields["s".$modifnbr]=0;
			$fields["r".$modifnbr]=11;
			$fields["c".$modifnbr]=4;
			$fields["v".$modifnbr]=$modality->valid_name;
			$modifnbr++;
			$fields["s".$modifnbr]=0;
			$fields["r".$modifnbr]=11;
			$fields["c".$modifnbr]=8;
			$fields["v".$modifnbr]=$modality->valid_fname;
			$modifnbr++;
			$fields["s".$modifnbr]=0;
			$fields["r".$modifnbr]=11;
			$fields["c".$modifnbr]=12;
			$fields["v".$modifnbr]=$modality->valid_mail;
			$modifnbr++;
			$studynbr=0;
			$prev_stuid=-1;
			$rcc_counter=0;
			$lcc_counter=0;
			$rmlo_counter=0;
			$lmlo_counter=0;
			while($row=pg_fetch_row($query))
			{

				$stuid=$row[0];
				if($stuid!=$prev_stuid)
				{
					$prev_stuid=$stuid;
					$maxx=max($lcc_counter,$rcc_counter,$lmlo_counter,$rmlo_counter);
					$nbrlines+= ($maxx*4);
					$rcc_counter=0;
					$lcc_counter=0;
					$rmlo_counter=0;
					$lmlo_counter=0;
					$fields["s".$modifnbr]=0;
  					$fields["r".$modifnbr]=$nbrlines;
  					$fields["c".$modifnbr]=0;
  					$fields["v".$modifnbr]=++$studynbr;
  					$modifnbr++;


  					$fields["s".$modifnbr]=0;
  					$fields["r".$modifnbr]=$nbrlines;
  					$fields["c".$modifnbr]=1;
  					$fields["v".$modifnbr]=$row[2];
  					$modifnbr++;

  					$fields["s".$modifnbr]=0;
  					$fields["r".$modifnbr]=$nbrlines;
  					$fields["c".$modifnbr]=2;
  					$fields["v".$modifnbr]=$row[3];
  					$modifnbr++;

					$fields["s".$modifnbr]=0;
  					$fields["r".$modifnbr]=$nbrlines;
  					$fields["c".$modifnbr]=13;
  					$fields["v".$modifnbr]=$row[12];
  					$modifnbr++;


				}

				$incidence=$row[4];
			    $lineadder=-1;
				switch($incidence)
				{
					case "RCC":
								$lineadder=4*$rcc_counter;
								$rcc_counter++;
								break;
					case "LCC":
								$lineadder=1+(4*$lcc_counter);
								$lcc_counter++;
								break;
					case "RMLO":
								$lineadder=2+(4*$rmlo_counter);
								$rmlo_counter++;
								break;
					case "LMLO":
								$lineadder=3+(4*$lmlo_counter);
								$lmlo_counter++;
								break;
				}
				$fields["s".$modifnbr]=0;
  				$fields["r".$modifnbr]=$nbrlines+$lineadder;
  				$fields["c".$modifnbr]=4;
  				$fields["v".$modifnbr]=$row[5];
  				$modifnbr++;

				$fields["s".$modifnbr]=0;
  				$fields["r".$modifnbr]=$nbrlines+$lineadder;
  				$fields["c".$modifnbr]=8;
  				$fields["v".$modifnbr]=$row[7];
  				$modifnbr++;

				$fields["s".$modifnbr]=0;
  				$fields["r".$modifnbr]=$nbrlines+$lineadder;
  				$fields["c".$modifnbr]=9;
  				$fields["v".$modifnbr]=$row[8];
  				$modifnbr++;

				$fields["s".$modifnbr]=0;
  				$fields["r".$modifnbr]=$nbrlines+$lineadder;
  				$fields["c".$modifnbr]=10;
  				$fields["v".$modifnbr]=$row[9];
  				$modifnbr++;

				$fields["s".$modifnbr]=0;
  				$fields["r".$modifnbr]=$nbrlines+$lineadder;
  				$fields["c".$modifnbr]=11;
  				$fields["v".$modifnbr]=$row[10];
  				$modifnbr++;

				$fields["s".$modifnbr]=0;
  				$fields["r".$modifnbr]=$nbrlines+$lineadder;
  				$fields["c".$modifnbr]=12;
  				$fields["v".$modifnbr]=$row[11];
  				$modifnbr++;

				$fields["s".$modifnbr]=0;
  				$fields["r".$modifnbr]=$nbrlines+$lineadder;
  				$fields["c".$modifnbr]=14;
  				$fields["v".$modifnbr]=$row[13];
  				$modifnbr++;

			}
			$fields["modifnbr"]=$modifnbr;

      $tmpfilename = create_tmp_datafile($fields,$modifnbr);
      $fields["source_datafile"] = $tmpfilename;
      $fields["offset_0"] = "22";
      $fields["rolloverrow"] = "4";


      	$postdata = http_build_query(
  				$fields
  			);

  			$opts = array('http' =>
  				array(
  					'method'  => 'POST',
  					'content' => $postdata
  					//'header'  => 'Content-type: application/x-www-form-urlencoded',

  				)
  			);

  			$context  = stream_context_create($opts);

  			//$url= "http://localhost".$EODACS_FOLDER."/eoDACS/afcnexport.php";

  			$result=file_get_contents($afcnexport_url,false,$context);
  			$listoffile[] = $file;
		}
    }
    //    ...
    //IRSN................................................................//
    //
    //...................................................................//
    //

    if($type == IRSN_NMADULTE || $type == IRSN_NMPEDIATRIQUE)
    {
      $file = 'csv_mesn_nrd-mn_'.$modality->filename.'.csv';
      //$file = $modality->filename.'.cv'
      $filepath = $tmpdir .'/'.$file;

      $querysql = 'select  EXTRACT(year FROM age(study.date,patient.birthdate)) as "mesnAge",
      replace(study.pat_weight::text, \'.\', \',\') as "mesnPoids",
      replace((study.pat_size/10)::text, \'.\', \',\') as "mesnTaille",
      replace(study.dosenm_auto::text, \'.\', \',\') as "mesnActivite1",
      \'\' as "mesnActivite2",
      replace((val->>\'23\')::text, \'.\', \',\') as "mesnKv",
      replace(serie.ctdivol_auto::text, \'.\', \',\') as "mesnIdsvCtdi",
      replace(serie.dlp_auto::text, \'.\', \',\') as "mesnPdl"
      from study, patient, serie,
      raw_serie_description_link
      where
      raw_serie_description_link.selected = true and
      raw_serie_description_link.ser_id = serie.ser_id and
      study.stu_id = serie.stu_id and
      patient.pat_id = study.pat_id
      and study.pro_id in('.$modality->profile_list.')'.$condstudy.$filter_mandatory.' order by study.date';
      $sql = 'COPY
      ('.$querysql.'
      )
      TO \''.$filepath.'\' DELIMITER \';\' CSV HEADER encoding \'latin1\';';
    }

    if($type == IRSN_NMCTADULTE)
    {
      $file = 'csv_mesn_nrd-mn_'.$modality->filename.'.csv';
      //$file = $modality->filename.'.cv'
      $filepath = $tmpdir .'/'.$file;

      $excluded_serie = " and serie.acquisition_type not in('Constant Angle Acquisition')";

      $querysql = 'select  EXTRACT(year FROM age(study.date,patient.birthdate)) as "mesnAge",
      replace(study.pat_weight::text, \'.\', \',\') as "mesnPoids",
      replace((study.pat_size/10)::text, \'.\', \',\') as "mesnTaille",
      replace(study.dosenm_auto::text, \'.\', \',\') as "mesnActivite1",
      \'\' as "mesnActivite2",
      replace((val->>\'23\')::text, \'.\', \',\') as "mesnKv",
      replace(serie.ctdivol_auto::text, \'.\', \',\') as "mesnIdsvCtdi",
      replace(serie.dlp_auto::text, \'.\', \',\') as "mesnPdl"
      from study, patient, serie,
      raw_serie_description_link
      where
      raw_serie_description_link.selected = true and
      raw_serie_description_link.ser_id = serie.ser_id and
      study.stu_id = serie.stu_id and
      patient.pat_id = study.pat_id
      and study.pro_id in('.$modality->profile_list.')'.$condstudy.$excluded_serie.$filter_mandatory.' order by study.date';

      $sql = 'COPY
      ('.$querysql.'
      )
      TO \''.$filepath.'\' DELIMITER \';\' CSV HEADER encoding \'latin1\';';
    }

    if($type == IRSN_CTADULTE || $type ==IRSN_CTPEDIATRIQUE )
    {
      $file = 'csv_mess_nrd-scanographie_'.$modality->filename.'.csv';
      //$file = $modality->filename.'.cv'
      $filepath = $tmpdir .'/'.$file;

      $excluded_serie = " and serie.acquisition_type not in('Constant Angle Acquisition')";

      $querysql = 'select EXTRACT(year FROM age(study.date,patient.birthdate)) as "messAge",
      replace(study.pat_weight::text, \'.\', \',\') as "messPoids",
      replace((study.pat_size/10)::text, \'.\', \',\') as "messTaille",
      replace(val->>\'23\'::text, \'.\', \',\') as "messKv",
      replace(round((val->>\'94\')::numeric,2)::text, \'.\', \',\') as "messPitch",
      null as "messAvanceTable",
      null as "messCollimationNb",
      null as "messCollimationVal",
      replace(serie.ctdivol_auto::text, \'.\', \',\') as "messIdsvCtdi",
      replace(serie.dlp_auto::text, \'.\', \',\') as "messPdl"
      from study, patient, serie,
      raw_serie_description_link
      where
      raw_serie_description_link.selected = true and
      raw_serie_description_link.ser_id = serie.ser_id and
      study.stu_id = serie.stu_id and
      patient.pat_id = study.pat_id
      and study.pro_id in('.$modality->profile_list.')'.$condstudy.$excluded_serie.$filter_mandatory.' order by study.date';

      $sql = 'COPY
      ('.$querysql.'
      )
      TO \''.$filepath.'\' DELIMITER \';\' CSV HEADER encoding \'latin1\';';
    }

    //Radiologie conventionnel
    if($type == IRSN_RXADULTE || $type ==IRSN_RXPEDIATRIQUE )
    {
      $file = 'csv_mesr_nrd-radiologie-pds_'.$modality->filename.'.csv';
      //$file = $modality->filename.'.cv'
      $filepath = $tmpdir .'/'.$file;

      $querysql = 'select EXTRACT(year FROM age(study.date,patient.birthdate)) as "mesrAge",
      replace(study.pat_weight::text, \'.\', \',\') as "mesrPoids",
      replace((study.pat_size/10)::text, \'.\', \',\') as "mesrTaille",
      replace(val->>\'23\'::text, \'.\', \',\') as "mesrKv",
      replace(coalesce(round((val->>\'79\')::numeric,2)::text,(round((val->>\'30\')::numeric*(val->>\'101\')::numeric/(1000*coalesce(NULLIF((val->>\'94\')::numeric,0),1)),2)::text),\'\')::text, \'.\', \',\') as "mesrMas",
      replace(round(((val->>\'23\')::numeric)/10,2)::text, \'.\', \',\') as "mesrDistance",
      replace(round(serie.dap_auto::numeric,2)::text, \'.\', \',\') as "mesrPds",
      \'mGy.cm²\' as "unitePdsRef"
      from study, patient, serie,
      raw_serie_description_link
      where
      raw_serie_description_link.selected = true and
      raw_serie_description_link.ser_id = serie.ser_id and
      study.stu_id = serie.stu_id and
      patient.pat_id = study.pat_id
      and study.pro_id in('.$modality->profile_list.')'.$condstudy.$excluded_serie.$filter_mandatory.' order by study.date';
      error_log ( "sql : " .$sql );
      $sql = 'COPY
      ('.$querysql.'
      )
      TO \''.$filepath.'\' DELIMITER \';\' CSV HEADER encoding \'latin1\';';
    }

	if($sql != ''){

		if(!pg_query($conn,$sql))
		{
		  error_log("Failed to execute query:".$sql);

		}else{
		  $listoffile[] = $file;
		}
	}
  }
}

echo '<data>';

if (count($listoffile)  == 0) {
    echo '<status>1</status><id></id><errormessage>Pas de donnée à exporter</errormessage>';
} else {
  $newfile='';
  if (count($listoffile) == 1) {
      $newfile=$listoffile[0];
  } else {
      $zipname = $type.date("Ymdhis").'.zip';
      $zip = new ZipArchive;
	  $zippath = $tmpdir.'/'.$zipname;

      $zip->open($zippath, ZipArchive::CREATE);
      foreach ($listoffile as $file) {
          $zip->addFile($tmpdir.'/'.$file, $file);

      }
      $zip->close();

      foreach ($listoffile as $file) {
          unlink($tmpdir.'/'.$file);
      }
      $newfile = $zipname;
  }
  $sql = "insert into async_download(title,created_at,linked_file,account_id,sendbymail,visible) values('',now(),'".$newfile."',".$_SESSION['id'].",".$sendbymail.",".$generateasync.") returning ad_id;";
  $conn = get_dbconnection();

  if($conn)
  {
    if (!($result = pg_query($conn, $sql))){
        error_log("Failed to execute query:".$sql);
        echo '<status>1</status><id></id><errormessage>Export impossible (query failed)</errormessage>';
    }else{

        $row = pg_fetch_row($result);
        $ad_id = $row[0];
        if ($generateasync == 'true' || $sendbymail == 'true') {
          if($sendbymail == true){

          $sql = "insert into joblist(option1,service,info1,comment,status) select mail,4,'Export ' ||to_char(created_at,'DD/MM/YYYY HH24:MI'),'<html><head></head><body><h1 style=\"background-color: rgba(52, 56, 62, 0.86);color: #f2f2f2;\"> An export is ready</h1>
              <p>Click the following link to download :&nbsp;<a href=\"".$config_servernameorip.$EODACS_FOLDER."/eoDACSCommon/async_download.php?id='||md5(ad_id||'''hash''')||'\">'||linked_file||'</a></p>
            </body>
            </html>',0 from async_download,account where ad_id = {$ad_id} and sendbymail = true and account_id = account.acc_id and sendbymail = true and coalesce(mail,'') != '';";

            if (!(pg_query($conn, $sql))){
                error_log("Failed to execute query:".$sql);
                echo '<status>1</status><id></id><errormessage>Envoi par mail impossible</errormessage>';
            }
          }
          echo '<status>1</status><id></id><errormessage>Export réalisé</errormessage>';
        } else {
          echo '<status>0</status><id>'.$newfile.'</id>';
        }
    }
  }else{
    echo '<status>1</status><id></id><errormessage>Export impossible (Pas de connexion à la DB)</errormessage>';
  }


}

echo '</data>';
