<?php

// !!!! en cas de modification, sécuriser les inputs

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



function get_modtype($conn,&$arraystuidlist)
{
  global $sessionGLOBALS;
  $sqlarray = array();
  foreach ($sessionGLOBALS['eoDACS_FDW'] as $key => &$fdw_site ) {
      if(!array_key_exists($key,$arraystuidlist))
        continue;

      $listofstuid =  implode(",", $arraystuidlist[$key]['stuidlist']);

      if($listofstuid == '')
        continue;

        if(!array_key_exists($key, $sqlarray))
          $sqlarray[$key] = array();


        $sqlarray[$key][] ="select modality.modalitytitle,department.name as dpt, institution.name as inst,to_char(date,'DD/MM/YYYY')  as sdate, to_char(date,'HH24:MI') as stime,'{$key}',stu_id
        from {$fdw_site['schemaremote']}.modality_profile,{$fdw_site['schemaremote']}.modality_identity,{$fdw_site['schemaremote']}.modality,{$fdw_site['schemaremote']}.department,{$fdw_site['schemaremote']}.institution,{$fdw_site['schemaremote']}.room,{$fdw_site['schemaremote']}.study
        where
        modality_profile.pro_id = study.pro_id and study.stu_id in (".$listofstuid.")
        and modality_identity.modident_id=modality_profile.modident_id
        and modality.mod_id=modality_identity.mod_id
        and modality.room_id=room.room_id and room.dep_id=department.dep_id and department.inst_id = institution.inst_id";

  }
  
  $sql = get_full_fdw_request($conn,$sqlarray,$sessionGLOBALS);

  if($query=pg_query($conn,$sql))
  {
    while($row = pg_fetch_row($query))
    {

      $modtype=$row[0];
      $dpt=$row[1];
      $inst=$row[2];
      $sdate=$row[3];
      $stime=$row[4];
      $key=$row[5];

      $modtypefromdb = $modtype;


      if($modtype != "CT" && $modtype != "MG" && $modtype != "NM")
       $modtype = "CR";
      //$xml.="<modtype>".$modtype."</modtype><department>".$dpt."</department><institution>".$inst."</institution><sdate>".$sdate."</sdate><stime>".$stime."</stime>";
      $groupname = $sessionGLOBALS["eoDACS_FDW"][$key]["groupname"];
      $arraystuidlist[$row[5]][$row[6]]['modtype'] = "<modtype>".$modtype."</modtype><department>".$dpt."</department><institution>".$inst."</institution><groupname>".$groupname."</groupname><sdate>".$sdate."</sdate><stime>".$stime."</stime>";

    }
  }
}

function get_studyanatomicregion(&$conn,&$arraystuidlist){

  global $sessionGLOBALS;
  $sqlarray = array();
  foreach ($sessionGLOBALS['eoDACS_FDW'] as $key => &$fdw_site ) {
    if(!array_key_exists($key,$arraystuidlist))
      continue;

    $listofstuid =  implode(",", $arraystuidlist[$key]['stuidlist']);

    if($listofstuid == '')
      continue;

    if(!array_key_exists($key, $sqlarray))
      $sqlarray[$key] = array();

    $sqlarray[$key][] ="select region,sumdap,sumdlp,sumctdivol,sumdgm,sumdosenm,site,stu_id
    ,nullif(sum(coalesce(sumdap,0)) over (partition by site,stu_id),0) as totalsumddap
    ,nullif(sum(coalesce(sumdlp,0)) over (partition by site,stu_id),0) as totalsumdlp
    ,nullif(sum(coalesce(sumctdivol,0)) over (partition by site,stu_id),0) as totalctdivol
    ,nullif(sum(coalesce(sumdgm,0)) over (partition by site,stu_id),0) as totalsumdgm
    ,nullif(sum(coalesce(sumdosenm,0)) over (partition by site,stu_id),0) as totalsumdosenm
    from (select region, sum(dap) as sumdap,sum(dlp) as sumdlp,sum(ctdivol) as sumctdivol,sum(dgm) as sumdgm,sum(dosenm) as sumdosenm,site,stu_id from (select coalesce(nullif(targetregion,''),'UNKNOWN') as region,round(sum(dap_auto)::numeric,2) as dap,round( sum (dlp_auto)::numeric,2) as dlp,round(sum(ctdivol_auto)::numeric,2) as ctdivol,round(sum(dgm_auto)::numeric,2) as dgm,sum(dosenm_auto) as dosenm,{$key} as site,stu_id
      from {$fdw_site['schemaremote']}.v_serie as serie where stu_id in({$listofstuid}) group by stu_id,targetregion
      union all
      select 'UNKNOWN' as region,round((dap_total-coalesce(dap_auto,0))::numeric,2) as dap ,NULL as dlp,NULL as ctdivol,NULL as dgm,NULL as dosenm, {$key} as site,stu_id
      from {$fdw_site['schemaremote']}.study where stu_id in({$listofstuid}) and dap_total-coalesce(dap_auto,0) > 0.05) as t1 group by site,stu_id,region) sumbyregion";
  }


  $sql = get_full_fdw_request($conn,$sqlarray,$sessionGLOBALS);

  if($query=pg_query($conn,$sql))
  {
    while($row = pg_fetch_row($query))
    {
      $arraystuidlist[$row[6]][$row[7]]['anatsum']="<anatsum>"
      ."<region>".$row[0]."</region><dap>".$row[1]."</dap><dlp>".$row[2]."</dlp><ctdivol>".$row[3]."</ctdivol><dgm>".$row[4]."</dgm><mbq>".$row[5]."</mbq>"
      ."</anatsum>";
      $arraystuidlist[$row[6]][$row[7]]['anatsumtot']="<anatsumtot>"
      ."<dap>".$row[8]."</dap><dlp>".$row[9]."</dlp><ctdivol>".$row[10]."</ctdivol><dgm>".$row[11]."</dgm><mbq>".$row[12]."</mbq>"
      ."</anatsumtot>";
    }
  }

}

function get_patientalert(&$conn,$fdw_site,$stuid)
{


  $sql = "select {$fdw_site['id']} as site, stu_id
       from {$fdw_site['schemaremote']}.alertwarning
       where stu_id in({$stuid}) and removed = false and pat_id > -1";

  if($query_alert=pg_query($conn,$sql))
  {
    if($row=pg_fetch_row($query_alert))
    {
      return 'true';
    }else{
      return 'false';
    }
  }else{
    error_log("Failed to fetch patient alert {$fdw_site['id']}_{$stuid}");
  }
  return 'false';
}

function get_studyalert(&$conn,$configs,&$arraystuidlist)
{
  global $sessionGLOBALS;
  $sqlarray = array();
  foreach ($sessionGLOBALS['eoDACS_FDW'] as $key => $fdw_site )
  {
    if($fdw_site['fetch_alert'] != true)
      continue;
    if(!array_key_exists($key,$arraystuidlist))
      continue;

    $listofstuid =  implode(",", $arraystuidlist[$key]['stuidlist']);

    if($listofstuid == '')
      continue;
     if(!array_key_exists($key, $sqlarray))
       $sqlarray[$key] = array();

     $sqlarray[$key][] ="select {$key} as site, stu_id,value,nrd_value,nrd_valuetype ,replace(replace(alert_name,'>','&gt;'),'<','&lt;'),nrd_id,pat_id
          from {$fdw_site['schemaremote']}.alertwarning
          where stu_id in({$listofstuid}) and ser_id = -1 and removed = false order by value/nrd_value desc";

  }

  if(!count($sqlarray))
  {
    return;
  }

  $sql = get_full_fdw_request($conn,$sqlarray,$sessionGLOBALS);

  if($query_alert=pg_query($conn,$sql))
  {
    while($row=pg_fetch_row($query_alert))
    {
      $xml_id = $arraystuidlist[$row[0]][$row[1]]['xmlconfig_idx'];
      $currentvalue=$row[2];
      $nrdvalue=$row[3];
      $nrdvaluetype=$row[4];
      $alertname=$row[5];
      $nrd_id=$row[6];
      $sapat_id=$row[7];
      $color="darkorange";
      $ratio=$currentvalue/$nrdvalue;
      if($ratio >=1.3) $color="#ff726f";
      else if ($ratio < 1.1) $color="darkkhaki";


      $alertorder=$configs[$xml_id]->xpath("Columnconfig/Line[@alert_line='{$nrdvaluetype}']/@order")[0];
      $alertcolumnorder=$configs[$xml_id]->xpath("Columnconfig/Line[@alert_line='{$nrdvaluetype}']/../@order")[0];
      $arraystuidlist[$row[0]][$row[1]]['xmlcolumn'] = "<alert patid='".$sapat_id."' currentvalue='".$currentvalue."' nrdvalue='".$nrdvalue."' nrdtype='".$nrdvaluetype."' alert='".$alertname."' nrd_id='".$nrd_id."' color='".$color."' column='".$alertcolumnorder."' line='".$alertorder."'/>". $arraystuidlist[$row[0]][$row[1]]['xmlcolumn'];
    }
  }
}

function get_seriealert(&$conn,$configs,&$arraystuidlist)
{
  global $sessionGLOBALS;
  $sqlarray = array();
  foreach ($sessionGLOBALS['eoDACS_FDW'] as $key => $fdw_site )
  {
    if($fdw_site['fetch_alert'] != true)
      continue;

    if(!array_key_exists($key,$arraystuidlist))
      continue;

    $listofserie = '';
    foreach($arraystuidlist[$key]['stuidlist'] as $stuid)
    {
      if(!array_key_exists('seridlist',$arraystuidlist[$key][$stuid]))
        continue;
      if($listofserie != '')
        $listofserie.= ',';
      $listofserie .= implode(",", $arraystuidlist[$key][$stuid]['seridlist']);
    }


    if($listofserie == '')
      continue;

     if(!array_key_exists($key, $sqlarray))
       $sqlarray[$key] = array();

     $sqlarray[$key][] ="select {$key} as site, ser_id,value as currentvalue,nrd_value as nrdvalue,nrd_valuetype as nrdvaluetype ,replace(replace(alert_name,'>','&gt;'),'<','&lt;'),nrd_id,stu_id
          from {$fdw_site['schemaremote']}.alertwarning
          where ser_id in({$listofserie}) and removed = false order by value/nrd_value desc";

  }

  if(!count($sqlarray))
  {
    return;
  }

  $sql = get_full_fdw_request($conn,$sqlarray,$sessionGLOBALS);
  if($query_alert=pg_query($conn,$sql))
  {
    while($row=pg_fetch_row($query_alert))
    {
      $key = $row[0];
      $serid = $row[1];
      $stuid = $row[7];
      $xml_id = $arraystuidlist[$key][$stuid]['xmlconfig_idx'];
      $currentvalue=$row[2];
      $nrdvalue=$row[3];
      $nrdvaluetype=$row[4];
      $alertname=$row[5];
      $nrd_id=$row[6];
      $color="darkorange";
      $ratio=$currentvalue/$nrdvalue;

      if($ratio >=1.3) $color="#ff726f";
      else if ($ratio < 1.1) $color="darkkhaki";


      $alertorder=$configs[$xml_id]->xpath("SerieConfig/Column[@alert_column='{$nrdvaluetype}']/@order")[0];
      $arraystuidlist[$key][$stuid]['xmlseriecolumn'][$serid]=
       "<alert currentvalue='".$currentvalue."' nrdvalue='".$nrdvalue."' nrdtype='".$nrdvaluetype."' alert='".$alertname."' nrd_id='".$nrd_id."' color='".$color."' column='".$alertorder."'/>".$arraystuidlist[$key][$stuid]['xmlseriecolumn'][$serid];

    }
  }
}

function get_studyconfig(&$conn,$configs,&$arraystuidlist)
{
  global $sessionGLOBALS;
  $sqlarray = array();
  foreach ($sessionGLOBALS['eoDACS_FDW'] as $key => $fdw_site ) {
    if(!array_key_exists($key,$arraystuidlist))
      continue;

    $listofstuid =  implode(",", $arraystuidlist[$key]['stuidlist']);
    $idx = 0;
    if($listofstuid == '')
      continue;

    foreach ($configs as $config) {
       $select_param=$config->xpath("@condition")[0];
       if(strlen($select_param)>1)
          $select_param=" and ".$select_param;

       if(!array_key_exists($key, $sqlarray))
         $sqlarray[$key] = array();

       $sqlarray[$key][] ="select {$idx} as idx,{$key} as site, stu_id, modality.modalitytitle from {$fdw_site['schemaremote']}.study,{$fdw_site['schemaremote']}.modality_profile,{$fdw_site['schemaremote']}.modality_identity,{$fdw_site['schemaremote']}.modality
         where stu_id in({$listofstuid}) and study.pro_id=modality_profile.pro_id and modality_identity.modident_id=modality_profile.modident_id and modality.mod_id=modality_identity.mod_id ".$select_param;
       $idx++;
      }
  }

  $sql = get_full_fdw_request($conn,$sqlarray,$sessionGLOBALS);


  $sql = "with t as ({$sql}),  t1 as (select min(idx),site,stu_id, modalitytitle from t group by site,stu_id, modalitytitle)
      select *,string_agg(stu_id::text,',') over (partition by site,modalitytitle)  from t1";

  $sqlarray = array();
  $array_row_fetched = array();
  $maxcol = 0;
  $maxcolserie = 0;
  if( $query = pg_query($conn,$sql))
  {

   while($row=pg_fetch_row($query))
   {

     if(!is_array($arraystuidlist[$row[1]][$row[2]]))
     {
       $arraystuidlist[$row[1]][$row[2]] = array();
     }
     $array_row_fetched[] = $row;
     $arraystuidlist[$row[1]][$row[2]]['xmlconfig_idx'] = $row[0];
     $key = $row[1];

     $fdw_site = $sessionGLOBALS['eoDACS_FDW'][$key];
     $arraystuidlist[$key][$row[2]]['xmlcolumn'] = '';

     if(!array_key_exists('nbr'.$row[4],$arraystuidlist[$row[1]]))
     {
       //-------------compute column value;
       $xml_id = $arraystuidlist[$row[1]][$row[2]]['xmlconfig_idx'];
       $columnconfig = $configs[$xml_id]->xpath("Columnconfig");
       foreach($configs[$xml_id] as $conf){
         $columns = $conf->xpath("Line");
         $nbrcol = count($columns);
         if($maxcol < $nbrcol)
          $maxcol = $nbrcol;
         $arraystuidlist[$row[1]]['nbr'.$row[4]] = "ok";
       }
       $seriecolumn = $configs[$xml_id]->xpath("SerieConfig/Column");
       $nbrcol = count($seriecolumn);
       if($maxcolserie < $nbrcol)
        $maxcolserie = $nbrcol;
      }
   }


   $sqlarrayserie = array();

   foreach($array_row_fetched as $row)
   {
     $stuid = $row[2];
     $stuidlist = $row[4];
     $arraystuidlist[$row[1]][$stuid]['groupstuid']=$stuidlist;
     if(!array_key_exists('c'.$stuidlist,$arraystuidlist[$row[1]]))
     {
       $key = $row[1];
       $fdw_site = $sessionGLOBALS['eoDACS_FDW'][$key];

       $arraystuidlist[$row[1]]['c'.$stuidlist] = 'done';
       //-------------compute column value;
       $xml_id = $arraystuidlist[$row[1]][$row[2]]['xmlconfig_idx'];
       $columnconfig = $configs[$xml_id]->xpath("Columnconfig");


       //study info;
       foreach($columnconfig as $conf){
         $lines = $conf->xpath("Line");

         $cn=-1;
         $sql_cols="";
         $nlines =  count($lines);
         for($i = 0; $i < $nlines || $i < $maxcol; $i++)
         {
            $cn++;
            if($cn>0)
              $sql_cols.=",";

            if($i >= $nlines)
            {
              $sql_cols.= "''";
              continue;
            }

            $line = $lines[$i];
            $title=$line->attributes()["title"][0];
            $col_select= $line->attributes()["col_select"][0];
            $order= $line->attributes()["order"][0];

            $sql_cols.="'<line order=\"{$order}\" title=\"{$title}\">'||replace(replace(".$col_select."::text,'>','&gt;'),'<','&lt;')||'</line>'";

          }

          $col_nbr++;

          if(!array_key_exists($key, $sqlarray))
            $sqlarray[$key] = array();

          $sqlarray[$key][] ="select {$key} as site, study.stu_id as studystuid,".$sql_cols."
          from {$fdw_site['schemaremote']}.study,{$fdw_site['schemaremote']}.modality_profile,{$fdw_site['schemaremote']}.modality_identity,{$fdw_site['schemaremote']}.modality
          where stu_id in({$row[4]}) and study.pro_id=modality_profile.pro_id and modality_identity.modident_id=modality_profile.modident_id
          and modality.mod_id=modality_identity.mod_id";

        }


        //serie info;
        $columnconfigserie = $configs[$xml_id]->xpath("SerieConfig/Column");
        $nlines = count($columnconfigserie);
        $cn=-1;
        $sql_cols="";
        $arraystuidlist[$key][$stuidlist]['xmldetailedtable'] = "<detailed_table>";
        for($i = 0; $i < $nlines || $i < $maxcolserie; $i++)
        {
          $cn++;
          if($cn>0)
            $sql_cols.=",";

           if($i >= $nlines)
           {
             $sql_cols.= "''";
             continue;
           }
           $line = $columnconfigserie[$i];
           $title=$line->attributes()["title"][0];
           $col_select= $line->attributes()["col_select"][0];
           $order= $line->attributes()["order"][0];
           $arraystuidlist[$key][$stuidlist]['xmldetailedtable'].="<title order='".$order."' name='".$title."'/>";

             $sql_cols.="'<_{$order}>'||replace(replace(".$col_select."::text,'>','&gt;'),'<','&lt;')||'</_{$order}>'";

           $cn++;
         }

         $arraystuidlist[$key][$stuidlist]['xmldetailedtable'] .= "</detailed_table>";

         if(!array_key_exists($key, $sqlarrayserie))
           $sqlarrayserie[$key] = array();

         $sqlarrayserie[$key][] = "select * from (select {$key} as site, stu_id, ser_id,".$sql_cols."
                 from {$fdw_site['schemaremote']}.v_serie as serie
                 where serie.stu_id in({$row[4]}) order by stu_id,coalesce(val->>'109',serie.seuid)) as tmp" ;

      }

    }


    $sql ="";
    if(count($sqlarray))
    {
      $sql = get_full_fdw_request($conn,$sqlarray,$sessionGLOBALS);
    }

    if($sql!= '' && $query_out=pg_query($conn,$sql))
    {
     while($row_out=pg_fetch_row($query_out))
     {
       $i = -1;
       $key = $row_out[0];
       $stuid = $row_out[1];
       $arraystuidlist[$key][$stuid]['xmlcolumn'].='<column>';
       foreach($row_out as $value)
       {
         $i++;
         if($i < 2 || $value == '')
         {
           continue;
         }

         $arraystuidlist[$key][$stuid]['xmlcolumn'] .= $value;
       }
       $arraystuidlist[$key][$stuid]['xmlcolumn'].='</column>';
     }
    }


    $sql ="";
    if(count($sqlarrayserie))
    {
      $sql = get_full_fdw_request($conn,$sqlarrayserie,$sessionGLOBALS);
    }

     if($sql!= '' && $query_out=pg_query($conn,$sql))
     {
    //   $xml.="<column>";
       while($row_out=pg_fetch_row($query_out))
       {
         $i = -1;
         $key = $row_out[0];
         $stuid = $row_out[1];
         $serid = $row_out[2];
         foreach($row_out as $value)
         {
           $i++;
           if($i < 3 || $value == '')
           {
             continue;
           }

           if(!array_key_exists('xmlseriecolumn',$arraystuidlist[$key][$stuid]))
           {
             $arraystuidlist[$key][$stuid]['xmlseriecolumn'] = array();

           }
		   if($arraystuidlist[$key])
			   $arraystuidlist[$key][$stuid]['xmlseriecolumn'][$serid] .= $value;
         }
         if(!array_key_exists('seridlist',$arraystuidlist[$key][$stuid]))
         {
           $arraystuidlist[$key][$stuid]['seridlist'] = array();
         }
         $arraystuidlist[$key][$stuid]['seridlist'][] = $serid;
       }
     }
   }
   get_studyalert($conn,$configs,$arraystuidlist);
   get_studyanatomicregion($conn,$arraystuidlist);
   get_seriealert($conn,$configs,$arraystuidlist);
}


/**
 * Valide qu'une valeur est un entier positif (ID de base de données).
 * Retourne l'entier castée ou false si invalide.
 */
function validate_integer_id($value) {
    if ($value === null || $value === '') return false;
    // Autorise le format "site_id" (ex: "2_1234") ou un entier simple
    if (!preg_match('/^\d+$/', $value)) return false;
    $int = intval($value);
    if ($int <= 0) return false;
    return $int;
}

/**
 * Valide un stu_id qui peut être au format "site_stuid" (ex: "0_42") ou un entier simple.
 * Retourne un tableau ['site' => int|string, 'stuid' => int] ou false si invalide.
 */
function validate_stu_id($value) {
    if ($value === null || $value === '') return false;
    // Format accepté : optionnellement un préfixe numérique suivi de "_", puis un entier
    if (!preg_match('/^(\d+_)?\d+$/', $value)) return false;
    return $value; // la décomposition est faite plus bas avec explode, on retourne la valeur assainie
}

/**
 * Valide le paramètre 'mode' : seules les valeurs whitelistées sont acceptées.
 */
function validate_mode($value) {
    $allowed = ['patient', 'detailed'];
    if (in_array($value, $allowed, true)) return $value;
    return 'patient'; // valeur par défaut sécurisée
}

/**
 * Sanitise une liste d'IDs entiers pour usage dans un IN(...) SQL.
 * Chaque valeur doit être un entier strict ; les autres sont exclues.
 * Retourne une chaîne prête pour SQL ou '' si la liste est vide.
 */
function sanitize_id_list(array $ids) {
    $safe = array_filter($ids, function($v) {
        return preg_match('/^\d+$/', (string)$v) && intval($v) > 0;
    });
    return implode(',', array_map('intval', $safe));
}


$xml = "<SDR>";
$conn = get_dbconnection();
if($conn)
{
  // --- Validation et assainissement des paramètres GET ---
  $sdrmode = 'patient';
  if(isset($_GET['mode']))
  {
    $sdrmode = validate_mode($_GET['mode']);
  }

  if(isset($_GET["stu_id"]) || isset($_GET["pat_id"]))
  {
    $default_idsite = 0;
    $stu_id=-1;
    $pat_id=-1;
    $idsite = 0;

    // Validation de stu_id
    if(isset($_GET["stu_id"]))
    {
      $raw_stu_id = $_GET["stu_id"];
      if(validate_stu_id($raw_stu_id) === false)
      {
        $xml .= "<error>Paramètre stu_id invalide.</error></SDR>";
        header('Content-Type: text/xml');
        echo $xml;
        exit();
      }
      $stu_id = $raw_stu_id; // validé : ne contient que des chiffres et éventuellement un "_"
    }

    // Validation de pat_id
    if(isset($_GET["pat_id"]))
    {
      $raw_pat_id = $_GET["pat_id"];
      if(!preg_match('/^\d+$/', $raw_pat_id) || intval($raw_pat_id) <= 0)
      {
        $xml .= "<error>Paramètre pat_id invalide.</error></SDR>";
        header('Content-Type: text/xml');
        echo $xml;
        exit();
      }
      $pat_id = intval($raw_pat_id);
    }

    $site_study = explode("_",$stu_id,2);
    if(count($site_study) == 1) // no remote site ID
    {
      $site_study[0] = $idsite;
      $site_study[1] = $stu_id;
    }

    // S'assurer que les deux parties sont bien des entiers
    $site_study[0] = intval($site_study[0]);
    $site_study[1] = intval($site_study[1]);

    $stu_id = $site_study[1];
    $idsite = $site_study[0];

    $sessionGLOBALS = get_active_remote_site($conn,$GLOBALS['eoDACS_FDW']);

    if(!array_key_exists($idsite,$sessionGLOBALS['eoDACS_FDW']))
    {
      $xml.="<error>Remote site ID({$idsite}) is not accessible.</error>";
      $xml.= "</SDR>";
      $xml = str_replace('&','&amp;',$xml );
      header('Content-Type: text/xml');
      echo $xml;
      exit();
    }


    $arr_patid_by_site = get_patidlist_by_site($conn,$sessionGLOBALS,$idsite,$pat_id,$stu_id);

    $pat_id = $arr_patid_by_site[$default_idsite][0]; // should be unique;

    // Validation du pat_id récupéré depuis la base avant insertion dans la requête
    $safe_pat_id = intval($pat_id);
    if($safe_pat_id <= 0)
    {
      $xml.="<error>Patient introuvable.</error></SDR>";
      header('Content-Type: text/xml');
      echo $xml;
      exit();
    }

    $sqlpatidfromstuid = "in({$safe_pat_id})";

    //always feeding data from local site.
    $sql = "select split_part(name, '^', 1) as lastname,split_part(name, '^', 2) as firstname,id,sex,to_char(birthdate,'DD/MM/YYYY'),'' as niss from patient where pat_id {$sqlpatidfromstuid} limit 1;";

    if($query=pg_query($conn,$sql))
    {
      if($row = pg_fetch_row($query))
      {
        $xml.="<patient>";
        $xml.="<lastname>".$row[0]."</lastname><firstname>".$row[1]."</firstname><patid>".$row[2]."</patid><sex>".$row[3]."</sex><birthdate>".$row[4]."</birthdate>";
        $xml.="</patient>";
      }
    }



    $sqlbysite = '';
    $sqltotalbysite = '';

    $sqlarray = array();
    $sqlarray2 = array();
    foreach ($sessionGLOBALS['eoDACS_FDW'] as $key => &$site ) {
      // Validation de la liste des pat_id pour chaque site
      $raw_list = $arr_patid_by_site[$key];
      $listofpatid = sanitize_id_list($raw_list);

      if($listofpatid == '')
      continue;

      if(!array_key_exists($key, $sqlarray))
      {
        $sqlarray[$key] = array();
        $sqlarray2[$key] = array();
      }
      //requestlist_globalanatsum
      $sqlarray[$key][] = "select coalesce(nullif(targetregion,''),'UNKNOWN') as region ,round(sum(serie.dap_auto)::numeric,2) as dap, round(sum (serie.dlp_auto)::numeric,2) as dlp,round(sum(serie.dgm_auto)::numeric,2) as dgm ,sum(serie.dosenm_auto) as mbq,round(sum(serie.ctdivol_auto)::numeric,2) as ctdivol
      from {$site['schemaremote']}.v_serie as serie,{$site['schemaremote']}.study  where serie.stu_id = study.stu_id  and pat_id in({$listofpatid}) group by targetregion
      union all
      select 'UNKNOWN' as region, round(sum(dap_total::numeric)- sum(coalesce(dap_auto::numeric,0)),2) as dap,NULL as dlp, NULL as dgm,NULL as mbq, NULL as ctdivol
      from {$site['schemaremote']}.study where pat_id in({$listofpatid}) and dap_total-coalesce(dap_auto,0)>0.05";

      //requestlist_globaltotalanatsum
      $sqlarray2[$key][] = "select round((sum(dap_total)::numeric),2) as dap, round(sum (dlp_auto)::numeric,2) as dlp,round(sum(dgm_auto)::numeric,2) as dgm,sum(dosenm_auto) as mbq,round(sum(ctdivol_auto)::numeric,2) as ctdivol
      from {$site['schemaremote']}.study where pat_id in({$listofpatid})";
    }

    $sqlbysite = get_full_fdw_request($conn,$sqlarray,$sessionGLOBALS);
    $sql="with t as ({$sqlbysite})
    select region,sum(dap),sum(dlp),sum(dgm),sum(mbq),sum(ctdivol) from t group by region";

    if($query=pg_query($conn,$sql))
    {
      while($row = pg_fetch_row($query))
      {
        if(strlen($row[1].$row[2].$row[3].$row[4].$row[5]."")>0){
          $xml.="<globalanatsum>";
          $xml.="<region>".$row[0]."</region><dap>".$row[1]."</dap><dlp>".$row[2]."</dlp><dgm>".$row[3]."</dgm><mbq>".$row[4]."</mbq><ctdivol>".$row[5]."</ctdivol>";
          $xml.="</globalanatsum>";
        }
      }
    }

    $sqltmp = get_full_fdw_request($conn,$sqlarray2,$sessionGLOBALS);
    $sql = "with t as({$sqltmp})
    select round((sum(dap)::numeric),2) as dap, round(sum (dlp)::numeric,2) as dlp,round(sum(dgm)::numeric,2) as dgm,sum(mbq) as mbq,round(sum(ctdivol)::numeric,2) as ctdivol from t";

    if($query=pg_query($conn,$sql))
    {
      while($row = pg_fetch_row($query))
      {

        $xml.="<globalanatsumtot>";
        $xml.="<dap>".$row[0]."</dap><dlp>".$row[1]."</dlp><dgm>".$row[2]."</dgm><mbq>".$row[3]."</mbq><ctdivol>".$row[4]."</ctdivol>";
        $xml.="</globalanatsumtot>";
      }
    }


    $modtype="CR";
    $dpt="";
    $inst="";
    $sdate="";
    $stime="";

    $studies=[];
    $arr_stuid_by_site = array();
    $arr_stuidall = array();

    $sqlarray = array();
    if($sdrmode=='detailed')
    {
      foreach ($sessionGLOBALS['eoDACS_FDW'] as $key => &$site ) {
        // Validation de la liste des pat_id pour chaque site
        $raw_list = $arr_patid_by_site[$key];
        $listofpatid = sanitize_id_list($raw_list);
        if($listofpatid == '')
          continue;
         if(!array_key_exists($key, $sqlarray))
         {
           $sqlarray[$key] = array();
         }
         $sqlarray[$key][] = "select '{$key}',stu_id,modalitytitle, date from {$site['schemaremote']}.study, {$site['schemaremote']}.v_modality_config where study.pro_id = v_modality_config.pro_id and pat_id in (".$listofpatid.")";
      }
    }else{
      $key = $idsite;
      $sqlarray[$key]= array();
      $site = $sessionGLOBALS['eoDACS_FDW'][$key];
      $safe_stu_id = intval($stu_id); // déjà validé plus haut, cast défensif
      $sqlarray[$key][] = "select '{$key}',stu_id,modalitytitle, date from {$site['schemaremote']}.study, {$site['schemaremote']}.v_modality_config where study.pro_id = v_modality_config.pro_id and stu_id in (".$safe_stu_id.")";
    }


      $sqltmp = get_full_fdw_request($conn,$sqlarray,$sessionGLOBALS);
      $sql = "with t as ($sqltmp) select * from t order by date desc";

      if($query=pg_query($conn,$sql))
      {
        $i = 0;
        while($row= pg_fetch_row($query))
        {
          if(!array_key_exists($row[0],$arr_stuid_by_site))
          {
            $arr_stuid_by_site[$row[0]] = array();
            $arr_stuid_by_site[$row[0]]['stuidlist'] = array();
          }
          if(!array_key_exists($row[2],$arr_stuid_by_site[$row[0]]))
          {
            $arr_stuid_by_site[$row[0]][$row[2]] = array();
          }

          array_push($arr_stuid_by_site[$row[0]][$row[2]],$row[1]);
          array_push($arr_stuid_by_site[$row[0]]['stuidlist'],$row[1]);
          $studytofetch['fdw'] = $sessionGLOBALS['eoDACS_FDW'][$row[0]];
          $studytofetch['stu_id'] = $row[1];
          $studytofetch['modalitytype'] =$row[2];
          $studytofetch['idx'] = $i++;

          array_push($studies,$studytofetch);
        }
      }



    for($index=0;$index < count($studies);$index++)
    {

      $stu_id=  $studies[$index]['stu_id'];
      $fdw_site =  $studies[$index]['fdw'];
      $modtypefromdb = $studies[$index]['modalitytype'];

      error_log ("Current path : " .getcwd() );
      // $xml_file=simplexml_load_file("../../../eoDACSConfig/dosereportconfig.xml");
      $xml_file=simplexml_load_file("../../../../Config/dosereportconfig.xml");

      $patientalert='false';
      if($fdw_site['fetch_alert'] == true){
        $patientalert = get_patientalert($conn,$fdw_site,$stu_id);
      }

      $xml.="<studyinf studyid='{$stu_id}' fdwsite='{$fdw_site['id']}' patientalert='{$patientalert}'>";

      $configs = $xml_file->xpath("//StudyInformationConfig/Config");



      if(!array_key_exists($stu_id,$arr_stuid_by_site[$fdw_site['id']]))
      {
        //first time fetch data
        get_studyconfig($conn,$configs,$arr_stuid_by_site);
        get_modtype($conn,$arr_stuid_by_site);
      }
      if(!array_key_exists($stu_id,$arr_stuid_by_site[$fdw_site['id']]))
      {
        error_log("failed to fetch study data site {$fdw_site['id']} - stu_id ".$stu_id);
        $xml.= "</studyinf>";
        continue;
      }


      if(array_key_exists('studyalert',$arr_stuid_by_site[$fdw_site['id']][$stu_id]))
      {
        $xml.=$arr_stuid_by_site[$fdw_site['id']][$stu_id]['studyalert'];
      }

     if($arr_stuid_by_site[$fdw_site['id']][$stu_id]['xmlcolumn'] != '')
     {
        $xml.=$arr_stuid_by_site[$fdw_site['id']][$stu_id]['xmlcolumn'];
     }



      $xml .= $arr_stuid_by_site[$fdw_site['id']][$stu_id]['modtype'];

      $xml .= $arr_stuid_by_site[$fdw_site['id']][$stu_id]['anatsum'];
      $xml .= $arr_stuid_by_site[$fdw_site['id']][$stu_id]['anatsumtot'];

      $groupstuid =  $arr_stuid_by_site[$fdw_site['id']][$stu_id]['groupstuid'];
      $xml .= $arr_stuid_by_site[$fdw_site['id']][$groupstuid]['xmldetailedtable'];

      foreach($arr_stuid_by_site[$fdw_site['id']][$stu_id]['xmlseriecolumn'] as &$serie_col)
      {
        $xml .= "<irrevent>".$serie_col."</irrevent>";
      }

      $xml.="</studyinf>";
    }

  }
}
else $xml.="<error>NO DB CONNEXION</error>";

$xml.= "</SDR>";
$xml = str_replace('&','&amp;',$xml );
header('Content-Type: text/xml');
echo $xml;

?>
