<?php


require_once '../../autoload.php';
require_once '../../../Common/eoServerRequest.php';

set_time_limit(600);

header('Content-type: text/xml');

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

$type = $_GET['type'];


$file = '';

$sql = '';
$studymaster_filter = '';
$seriemaster_filter = '';
$imc_filter = '';
$age_filter = '';
$modality_filter = '';

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

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

if($_GET['imc'] != '')
{
  $imc_filter = " and f_get_imc_id(study.pat_size,study.pat_weight) in ({$_GET['imc']})";
}

if($_GET['age'] != '')
{
    $ageRanges = [
        0 => ['min' => 16, 'max' => -1],
        1 => ['min' => 11, 'max' => 15],
        2 => ['min' => 6, 'max' => 11],
        3 => ['min' => 1, 'max' => 5],
        4 => ['min' => 0, 'max' => 1],
    ];

    $selectedAges = explode(',', $_GET['age']);
    $ageConditions = [];

    foreach ($selectedAges as $age) {
        if (array_key_exists($age, $ageRanges)) {
            $range = $ageRanges[$age];
            $condition = '';

            if ($range['min'] != -1) {
                if (!empty($condition)) {
                    $condition .= " AND ";
                }
                $condition .= "EXTRACT(year FROM age(study.date,patient.birthdate))::integer >= {$range['min']}";
            }

            if ($range['max'] != -1) {
                if (!empty($condition)) {
                    $condition .= " AND ";
                }
                $condition .= "EXTRACT(year FROM age(study.date,patient.birthdate))::integer <= {$range['max']}";
            }

            if (!empty($condition)) {
                $ageConditions[] = "($condition)";
            }
        }
    }

    if (!empty($ageConditions)) {
        $age_filter .= " AND (" . implode(' OR ', $ageConditions) . ")";
    }
}

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

if(isset($_SESSION['filter_modalitylist']) && $_SESSION['filter_modalitylist'] != '')
{
    $modality_filter .= " and v_modality_config.mod_id in ({$_SESSION['filter_modalitylist']})";
}

$CSV_delimiter = "DELIMITER ';'";
$export_to_templateid = '';
if(isset($_GET['statid']) && $_GET['statid'] != '' )
{
  $export_to_templateid = $_GET['statid'];
  $CSV_delimiter = '';
}

$datacsvtype= '';

if($type == 'exportstudy')
{
  $file = 'exportstudy_'.date("Ymdhis").'.csv';
  $filepath = $tmpdir .'/'.$file;
  $sql='COPY
  (select to_char(date,\'DD/MM/YYYY\') as "Date",
  study.description_auto as "Etude",
  total_number_radiographic_frames as "Nbre phase",
  patient.name as "Patient",
  f_get_age_classification(EXTRACT(year FROM age(study.date,patient.birthdate))::integer) as "Classification",
  EXTRACT(year FROM age(study.date,patient.birthdate)) as Age,
  replace(f_get_imc(study.pat_size,study.pat_weight), \'.\', \',\') as IMC,
  replace((nullif(study.pat_size,\'0\')/10)::text, \'.\', \',\') as Taille,
  study.pat_weight as Poids,
  v_modality_config.name as Modality,
  study.performingphysician as Radiologist,
  study.operator as "Manipulateur",
  replace(round(study.dlp_auto::numeric,2)::text, \'.\', \',\') as "PDL (mGy.cm)",
  replace(round(study.ctdivol_auto::numeric,2)::text, \'.\', \',\') as "CTDIVol(mGy)",
  rsd.description as "Original Study Description",
  replace(round(coalesce(study.dap_total::numeric,study.dap_auto::numeric),2)::text, \'.\', \',\') as "PDS (mGy.cm²)",
  replace(round(study.dap3d_auto::numeric,2)::text, \'.\', \',\') as "PDS 3D (mGy.cm²)",
  replace(round(study.dgm_auto::numeric,2)::text, \'.\', \',\') as "DGM (mGy)",
  \'\' as CCAM,
  patient.id as "Patient ID",
  study.accessionnumber as "Accession Number",
  replace((nullif(study.total_fluoro_time,0)/1000)::text, \'.\', \',\')  as "Temps de scopie (s)",
  replace(study.fluoro_dap_total::text, \'.\', \',\')  as "PDS de scopie (mGy.cm²)",
  study.comments_on_radiation_dose as "Commentaire 1",
  \'\' as "Commentaire 2",
  \'\' as "Commentaire 3",
  replace(round(study.dose_rp_total::numeric,2)::text, \'.\', \',\') as "RP Dose Total (mGy)",
  replace(round(study.fluoro_dose_rp_total::numeric,2)::text, \'.\', \',\') as "Fluoro Dose (RP) Total (mGy)",
  to_char(patient.birthdate,\'DD/MM/YYYY\') as "Date de naissance"
  from study, patient,v_modality_config,raw_study_description rsd
  where
  rsd.rstud_id = study.rstud_id and
  patient.pat_id = study.pat_id and study.pro_id = v_modality_config.pro_id
  and study.date::date >= to_date(\''.$from.'\',\'YYYY-MM-DD\')
  and study.date::date <= to_date(\''.$to.'\',\'YYY-MM-DDY\')
  '.$studymaster_filter.$imc_filter.$age_filter.$modality_filter.' order by study.date
  )
  TO \''.$filepath.'\' '.$CSV_delimiter.' CSV HEADER encoding \'latin1\';';
  //type 0 = integer, 1 = double, 2 = string,3 date
  $datacsvtype = '3,2,0,2,2,0,2,1,1,2,2,2,1,1,2,1,1,1,2,2,2,1,1,2,2,2,1,1,3';
}else{
  $file = 'exportserie_'.date("Ymdhis").'.csv';
  $filepath = $tmpdir .'/'.$file;
  $sql = 'COPY
  (select to_char(date,\'DD/MM/YYYY\') as "Date",
  study.description_auto as "Etude",
  serie.description_auto as "Serie",
  serie.acquisition_type as "Type d\'acquisition",
  patient.name as "Patient",
  f_get_age_classification(EXTRACT(year FROM age(study.date,patient.birthdate))::integer) as "Classification",
  to_char(patient.birthdate,\'DD/MM/YYYY\') as "Date de naissance",
  EXTRACT(year FROM age(study.date,patient.birthdate)) as "Age",
  replace(f_get_imc(study.pat_size,study.pat_weight)::text, \'.\', \',\') as "IMC",
  replace(nullif(study.pat_size,\'0\')::text, \'.\', \',\') as "Taille (cm)",
  replace(study.pat_weight::text, \'.\', \',\') as "Poids (Kg)",
  v_modality_config.name as "Modalité",
  study.performingphysician as "Radiologue",
  study.operator as "Manipulateur",
  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 "mAs",
  replace(round(nullif(val->>\'23\',\'\')::numeric,2)::text, \'.\', \',\') as "kV",
  replace(round(serie.ctdivol_auto::numeric,2)::text, \'.\', \',\') as "CTDIVol(mGy)",
  replace(round((val->>\'28\')::numeric/10,2)::text, \'.\', \',\') as "Longueur de spirale(cm)",
  replace(round(serie.dlp_auto::numeric,2)::text, \'.\', \',\') as "PDL (mGy.cm)",
  replace(round(serie.dap_auto::numeric,2)::text, \'.\', \',\') as "PDS (mGy.cm²)",
  replace(round(serie.dgm_auto::numeric,2)::text, \'.\', \',\') as "DGM (mGy)",
  rsd.description as "Original Study Description",
  rsd2.description as "Original Serie Description",
  serie.protocolname as "Protocol Name",
  \'\' as CCAM,
  patient.sex as "Sex",
  patient.id as "Patient ID",
  study.accessionnumber as "Accession Number",
  replace(round((val->>\'73\')::numeric,2)::text, \'.\', \',\') as "Force de compression(N)",
  replace(round((val->>\'46\')::numeric,2)::text, \'.\', \',\') as "Epaisseur du sein(mm)",
  replace(coalesce((val->>\'45\')::text,left((val->>\'44\'),1)::text||(val->>\'75acr\')::text), \'.\', \',\') as "Incidence",
  coalesce(val->>\'74\'::text,\'\')||\'/\'||coalesce(val->>\'72\'::text,\'\') as "Anode/Filter",
  val->>\'100\' as "Image comments",
  \'\' as "Micro dose",
  replace(round((val->>\'32\')::numeric,2)::text, \'.\', \',\') as "Exposure time(ms)",
  replace(round((val->>\'30\')::numeric,2)::text, \'.\', \',\') as "mA",
  replace(round((val->>\'94\')::numeric,2)::text, \'.\', \',\') as "Pitch",
  replace((val->>\'65\')::text, \'.\', \',\') as "Acquisition plane",
  \'\' as "Ouverture",
  replace(round((val->>\'66\')::numeric,2)::text, \'.\', \',\') as "Air Kerma (mGy)",
  study.comments_on_radiation_dose as "Commentaire study",
  \'\' as "Commentaire 2",
  \'\' as "Commentaire 3",
  replace(round((val->>\'68\')::numeric,2)::text, \'.\', \',\') as "Primary Angle(°)",
  replace(round((val->>\'69\')::numeric,2)::text, \'.\', \',\') as "Primary Angle End(°)",
  replace(round((val->>\'90\')::numeric,2)::text, \'.\', \',\') as "Secondary Angle(°)",
  replace(round((val->>\'92\')::numeric,2)::text, \'.\', \',\') as "Secondary Angle End(°)",
  replace(round((val->>\'63\')::numeric,2)::text, \'.\', \',\') as "Distance foyer detecteur(mm)",
  replace(round((val->>\'112\')::numeric,2)::text, \'.\', \',\') as "Longueur de champs(mm)",
  replace(round((val->>\'114\')::numeric,2)::text, \'.\', \',\') as "Largeur de champs(mm)",
  replace(round((val->>\'116\')::numeric,2)::text, \'.\', \',\') as "Aire de champs(dm²)",
  replace(round(coalesce((val->>\'125\')::numeric/(val->>\'124\')::numeric,(val->>\'127\')::numeric/1000),2)::text, \'.\', \',\') as "Temps de scopie (s)",
  replace(round((val->>\'133\')::numeric,2)::text, \'.\', \',\') as "Epaisseur MAX de la filtration (mm)",
  replace(round((val->>\'76\')::numeric,2)::text, \'.\', \',\') as "Dose RP (mGy)",
  null as "NRD"
  from study, patient,v_modality_config,raw_study_description rsd, serie, raw_serie_description rsd2,raw_serie_description_link rsdl2
  where
  rsd.rstud_id = study.rstud_id and
  rsdl2.selected = true and
  rsd2.rserd_id = rsdl2.rserd_id and
  rsdl2.ser_id = serie.ser_id and
  study.stu_id = serie.stu_id and
  patient.pat_id = study.pat_id and
  study.pro_id = v_modality_config.pro_id
  and study.date::date >= to_date(\''.$from.'\',\'DD/MM/YYYY\')
  and study.date::date <= to_date(\''.$to.'\',\'DD/MM/YYYY\')
  '.$studymaster_filter.$seriemaster_filter.$imc_filter.$age_filter.$modality_filter.' order by study.date, serie.serorder, serie.ser_id
  )
  TO \''.$filepath.'\' '.$CSV_delimiter.' CSV HEADER encoding \'latin1\';';
  //type 0 = integer, 1 = double, 2 = string,3 date
  $datacsvtype = '3,2,2,2,2,2,2,0,2,1,1,2,2,2,1,1,1,1,1,1,1,2,2,2,2,2,2,2,1,1';
  $datacsvtype .= ',2,2,2,1,1,1,1,2,2,1,2,2,2,1,1,1,1,1,1,1,1,1,1,1,1';
}

$conn = get_dbconnection();

error_log($sql);
error_log($sql);


if(!pg_query($conn,$sql))
{
  echo '<data><status>1</status><id>'.$file.'</id><errormessage>Failed to execute the request</errormessage></data>';
}else{
  // from here, csv file exists
  if( $export_to_templateid != '')
  {
    $templatein ='';
    $sql = "select s.stat_id, v.path ||'/'||s.storefilename,s.filename from stats_template s,volume v where v.vol_id = s.vol_id and s.stat_id = {$export_to_templateid};";

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

        if(file_exists($row[1]))
        {
          //create copy | if something goes wrong, template can be corrupted.
          $templatein = $tmpdir.'/'.$row[2];
          copy($row[1], $templatein);

        }else{
          echo '<data><status>1</status><id>'.$file.'</id><errormessage>Template not found</errormessage></data>';
          exit(-1);
        }
      }else{
        echo '<data><status>1</status><id>'.$file.'</id><errormessage>Template not found</errormessage></data>';
        exit(-1);
      }
    }else{
      echo '<data><status>1</status><id>'.$file.'</id><errormessage>Failed to get template</errormessage></data>';
      exit(-1);
    }


    $messagetype="merge_statscsv";
    $xml = new SimpleXMLElement('<data/>');

    $eoService = new eoServerRequest($GLOBALS['eoxserverreport_ip'], $GLOBALS['eoxserverreport_port'] ,$GLOBALS['eoxserverreport_eoc'],$xml);

    if($eoService->connect())
    {
      $templateout = '';
      $templatename = $type.'_T_'.date("Ymdhis").'.xlsm';
      $templateout = $tmpdir.'/'.$templatename;

      $in = "Command.method=".$messagetype;
      $in .= "|message.debug=1";
      $in .= "|merge.csvdata={$filepath}";
      $in .= "|merge.csvcolumntype={$datacsvtype}"; //type 0 = integer, 1 = double, 2 = string,3 date
      $in .= "|merge.separator=,";
      $in .= "|merge.srcfile={$templatein}";
      $in .= "|merge.dstfile={$templateout}";

      $eoService->send($in);
      $eoService->receive();
      $eoService->disconnect();
      unlink($templatein);
    }
    if($xml->{'status'}==200)
    {
      unlink($filepath);
      $file = $templatename;
      echo '<data><status>0</status><id>'.$file.'</id></data>';
    }else{
      echo '<data><status>1</status><id>'.$file.'</id><errormessage>XSLX Creation FAILED: '.$xml->{'message'}.'</errormessage></data>';
    }
  }else{
    echo '<data><status>0</status><id>'.$file.'</id></data>';
  }
}
?>
