<?php
require_once('vo/com/imt/intimamedia/vo/AbacusVo.php');
require_once('vo/com/imt/intimamedia/vo/NormalValuesVo.php');
require_once('vo/com/imt/intimamedia/vo/ReportDataVo.php');
require_once('vo/com/imt/intimamedia/vo/ReturnObjectVo.php');
require_once('common/SQLServerManager.php');
require_once('common/SecurityManager.php');
require_once('common/PhpMail.php');
require_once('sendgrid-php/sendgrid-php.php');

class ReportService
{  
  public function saveData(ReportDataVo $reportDataVo, $appointmentId, $token)
  { 
   	$returnObjectVo		  	= new ReturnObjectVo();
    $returnObjectVo->token  = SecurityManager::verifyToken($token);
    
    // check if an entry exist, in case update it         
    $query = "SELECT * FROM [intimamedia_physician].[dbo].[t_report] WHERE fk_appointment = ". $appointmentId .
              " AND fk_abacus = " . $reportDataVo->abacus ." AND type = " . $reportDataVo->type . ";";
		$results = SQLServerManager::queryOnDatabase( $query );
    
    if ($results)
    {
      foreach( $results as $value ) 
  		  $reportId = $value->id;
        
      // update report and delete/insert mails
  		$query = "UPDATE [intimamedia_physician].[dbo].[t_report]  set conclusion = '".  $reportDataVo->conclusion ."',".
                " signs = '". $reportDataVo->signs ."', indications = '". $reportDataVo->indications  ."' WHERE fk_appointment = ". $appointmentId .
                " AND fk_abacus = " . $reportDataVo->abacus ." AND fk_type = " . $reportDataVo->type . ";";
      
  		SQLServerManager::queryOnDatabase( $query );
      
      $query = "DELETE FROM [intimamedia_physician].[dbo].[t_mail] WHERE fk_report = ". $reportDataVo->id . ";";
      
  		SQLServerManager::queryOnDatabase( $query );
    }
    else
    {
      // insert		                                             
  		$query = "INSERT INTO [intimamedia_physician].[dbo].[t_report] (type, conclusion, signs, indications, fk_appointment, fk_abacus) VALUES ('".
  					$reportDataVo->type ."','". $reportDataVo->conclusion ."','". $reportDataVo->signs ."','". $reportDataVo->indications  ."','". 
            $appointmentId ."','". $reportDataVo->abacus . "')";
      
  		SQLServerManager::queryOnDatabase( $query );
  	
    	$query = "SELECT MAX(id) AS lastId FROM [intimamedia_physician].[dbo].[t_report]";
  		$results = SQLServerManager::queryOnDatabase( $query );
  		     
  	  $reportId = -1;
  		foreach( $results as $value ) 
  		  $reportId = $value->lastId;
    }
     
		foreach( $reportDataVo->mails as $mail ) 
    {
		  $query = "INSERT INTO [intimamedia_physician].[dbo].[t_mail] (label, fk_report) VALUES ('".$mail."','". $reportId ."')";
		  SQLServerManager::queryOnDatabase( $query );
    }
      
    return $returnObjectVo;
  }
  
  public function getData($appointmentId, $token)
  {          
    $returnObjectVo		  	= new ReturnObjectVo();
    $returnObjectVo->token  = SecurityManager::verifyToken($token);
                                           
		$query = "SELECT * FROM [intimamedia_physician].[dbo].[t_report] WHERE fk_appointment = '". $appointmentId ."';";

		$results = SQLServerManager::queryOnDatabase( $query );
		     
		if (!$results)
    {
      $returnObjectVo->value  = null;
      return $returnObjectVo;
    }
	
    $resultArray = array();
		foreach( $results as $value )
    { 
      $reportDataVo = new ReportDataVo();
      $reportDataVo->id = $value->id;
      $reportDataVo->type = $value->type;
      $reportDataVo->conclusion = trim($value->conclusion);
      $reportDataVo->indications = trim($value->indications);
      $reportDataVo->signs = trim($value->signs);
      $reportDataVo->abacus = trim($value->fk_abacus);
      $reportDataVo->mails = null;
      $reportDataVo->previousMails = null;
      
      $currentAppointment = $value->number;
      $patientId = $value->fk_patient;
      
      $mailQuery = "SELECT * FROM [intimamedia_physician].[dbo].[t_mail] WHERE fk_report = '". $reportDataVo->id ."';";
      $mailResults = SQLServerManager::queryOnDatabase( $mailQuery );

  		if ($mailResults)
      {	
        $mails = array();
        foreach( $mailResults as $mail )
          array_push($mails, $mail->label);  
        $reportDataVo->mails = $mails;  
          
        // check if mail from previous appointment exist
        if ($currentAppointment - 1 > 0)
        {
          $q = "SELECT fk_report FROM [intimamedia_physician].[dbo].[tj_appointment] WHERE number = ". $currentAppointment-1 ." AND fk_patient = " . $patientId . ";";
  		    $r = SQLServerManager::queryOnDatabase( $q );
  		    if ($r)
          {
          	foreach( $r as $v )
              $reportId = $v->fk_report;
              
            $previousMailQuery = "SELECT * FROM [intimamedia_physician].[dbo].[t_mail] WHERE fk_report = '". $reportId ."';";
            $previousMailResults = SQLServerManager::queryOnDatabase( $previousMailQuery );
  
        		if ($previousMailResults)
            {	
              $previousMails = array();
              foreach( $previousMailResults as $previousMail )
                array_push($previousMails, $previousMail->label);  
              $reportDataVo->previousMails = $previousMails;
            }
          }
        }
      }
      
      array_push($resultArray, $reportDataVo);
    }
  
    $returnObjectVo->value  = $resultArray;
    
    return $returnObjectVo;
  }
  
  public function getAbacus($country) 
  {
		$query 		 = "SELECT * FROM [intimamedia_physician].[dbo].[t_abacus] a " 
                ."INNER JOIN [intimamedia_physician].[dbo].[tr_normal_values] as nv ON a.id = nv.fk_abacus";

     /* $ff=fopen("d:/tmp.jd", "a+");
    fprintf($ff, "getAbacus = %s\n", $query);
    fclose($ff);*/
    
		$results 	 = SQLServerManager::queryOnDatabase( $query );

		if( count($results) == 0 )
		{
			Throw new Exception("E022");
		} 

    $resultArray = array();
    $abacusVo = null;
    
		foreach( $results as $value ) 
		{		
        // pass to another abacus
      if ($abacusVo AND strcmp($abacusVo->name, trim($value->name)) != 0)
      {
         array_push( $resultArray, $abacusVo );
      }
      		
        // first passage or another abacus
      if (!$abacusVo OR strcmp($abacusVo->name, trim($value->name)) != 0)
      {
  		  $abacusVo = new AbacusVo();
  		  $abacusVo->normalValues = array();
        $abacusVo->id = $value->id;
        $abacusVo->name = trim($value->name);
        $abacusVo->description = trim($value->description);
        $abacusVo->url = trim($value->url);
        $abacusVo->country = trim($value->country);
      }
      
      $normalValuesVo = new NormalValuesVo();
      
      $normalValuesVo->age = $value->age;
      $normalValuesVo->n_age_category = $value->n_age_category;
      $normalValuesVo->n_indiv = $value->n_indiv;
      $normalValuesVo->r_l_m = $value->r_l_m;
      $normalValuesVo->age_min = $value->age_min;
      $normalValuesVo->age_max = $value->age_max;
      $normalValuesVo->scale_min = $value->scale_min;
      $normalValuesVo->scale_max = $value->scale_max;
      $normalValuesVo->description_type = $value->description_type;
      $normalValuesVo->men_minright = $value->men_minright;
      $normalValuesVo->men_meanright = $value->men_meanright;
      $normalValuesVo->men_maxright = $value->men_maxright;
      $normalValuesVo->men_minleft = $value->men_minleft;
      $normalValuesVo->men_meanleft = $value->men_meanleft;
      $normalValuesVo->men_maxleft = $value->men_maxleft;
      $normalValuesVo->women_minright = $value->women_minright;
      $normalValuesVo->women_meanright = $value->women_meanright;
      $normalValuesVo->women_maxright = $value->women_maxright;
      $normalValuesVo->women_minleft = $value->women_minleft;
      $normalValuesVo->women_meanleft = $value->women_meanleft;
      $normalValuesVo->women_maxleft = $value->women_maxleft;
      $normalValuesVo->abacus_id = $value->id;
      
      array_push ( $abacusVo->normalValues, $normalValuesVo );     
		}
    
      // for last abacus which is not added to result in loop above
    array_push( $resultArray, $abacusVo );
			
		return $resultArray;
	}
	
	public function generateReport()
	{
	 	$resultArray = array();
	 	sleep(1);
	 	return $resultArray;
	} 
  
  public function sendReport($pdfData, $pdfName, $userId, $lang, $mails)
  {             
    if ($mails && sizeof($mails))
    {
      $subject = "IntimaMedia.com : IMT pdf";
        
      if ($lang == 'fr')
        $message = "<HTML><HEAD></HEAD><BODY>Bonjour<br/><br/>Vous venez de recevoir un rapport pdf en provenance de l'application IntimaMedia.com.<br/><br/>Cordialement.</BODY></HTML>";
      else
        $message = "<HTML><HEAD></HEAD><BODY>Hello<br/><br/>Please, find in attachment a IMT pdf report.<br/><br/>Best regards.</BODY></HTML>";
        
      $report = fopen("pdf/".$pdfName, "w");
      fwrite($report, $pdfData->data);
      fclose($report);
        
      while (sizeof($mails))
      {
        $mail = array_pop($mails);
        
		if ($mail)
        {
			$sguser = 'support@iimt.fr';
			$sgpass = 'Marignan;/8';

			$sendgrid = new SendGrid($sguser, $sgpass);
			$email = new SendGrid\Email();
			$email
				->addTo($mail)
				->setFrom('support@iimt.fr')
				->setSubject($subject)
				->setText($subject)
				->setHtml($message)
				->addAttachment( "pdf/".$pdfName)
			;
	
			$sendgrid->send($email);
/*			try {
				$sendgrid->send($email);
			} catch(\SendGrid\Exception $e) {
		//		echo $e->getCode();
		//		foreach($e->getErrors() as $er) {
		//			echo $er;
		//		}
			}		
*/
		}     
  //       $ret =  PhpMail::sendMail($mail, $subject, $message, "pdf/".$pdfName);    
     }
  }
}
}