<?php

/************************************************************************/
/* DOCEBO LMS - Learning managment system								*/
/* ============================================							*/
/*																		*/
/* Copyright (c) 2005													*/
/* http://www.docebo.com												*/
/*																		*/
/* This program is free software. You can redistribute it and/or modify	*/
/* it under the terms of the GNU General Public License as published by	*/
/* the Free Software Foundation; either version 2 of the License.		*/
/************************************************************************/

if(!defined('IN_DOCEBO')) die('You cannot access this file directly');

define("CERT_ID", 			0);
define("CERT_NAME", 		1);
define("CERT_DESCR", 		2);
define("CERT_LANG", 		3);
define("CERT_STRUCTURE", 	4);
define("CERT_CODE", 		5);

define("CERT_ID_COURSE", 	4);
define("CERT_AV_STATUS", 	5);

define("CERTIFICATE_PATH", '/doceboLms/certificate/');

define("AVS_NOT_ASSIGNED", 					0);
define("AVS_ASSIGN_FOR_ALL_STATUS", 		1);
define("AVS_ASSIGN_FOR_STATUS_INCOURSE", 	2);
define("AVS_ASSIGN_FOR_STATUS_COMPLETED", 	3);

define("ASSIGN_CERT_ID", 		0);
define("ASSIGN_COURSE_ID", 		1);
define("ASSIGN_USER_ID", 		2);
define("ASSIGN_OD_DATE", 		3);
define("ASSIGN_CERT_FILE", 		4);

require_once($GLOBALS['where_framework'].'/addons/html2pdf/html2fpdf.php');
require_once($GLOBALS['where_framework'].'/addons/html2pdf/protectedfpdf.php');

class HTML2FPDF_facs extends FPDF_Protection {
	//Page header
	/*function Header() {
		
	   //Logo
	   $this->Image(getPathImage('lms').'/certificate/facs_simile.jpg', 2, 2, 205);
	   $this->setXY($this->lMargin, $this->tMargin);
	}*/
	
	var $angle=0;
	
	function PlaceWater() {
		//Put watermark  Author: Ivan
		$this->SetFont('Arial','',50);
		$this->SetTextColor(230, 230, 230);
		$this->RotatedText(90, 150, 'F a c s - s i m i l e', 45);
	}
	
	function RotatedText($x,$y,$txt,$angle) {
		//Text rotated around its origin
		$this->Rotate($angle,$x,$y);
		$this->Text($x,$y,$txt);
		$this->Rotate(0);
	}
	
	
	function Rotate($angle,$x=-1,$y=-1) {
		
		//Author: Olivier
	    if($x==-1)
	        $x=$this->x;
	    if($y==-1)
	        $y=$this->y;
	    if($this->angle!=0)
	        $this->_out('Q');
	    $this->angle=$angle;
	    if($angle!=0)
	    {
	        $angle*=M_PI/180;
	        $c=cos($angle);
	        $s=sin($angle);
	        $cx=$x*$this->k;
	        $cy=($this->h-$y)*$this->k;
	        $this->_out(sprintf('q %.5f %.5f %.5f %.5f %.2f %.2f cm 1 0 0 1 %.2f %.2f cm',$c,$s,-$s,$c,$cx,$cy,-$cx,-$cy));
	    }
	}
	
	function _endpage() {
	    if($this->angle!=0)
	    {
	        $this->angle=0;
	        $this->_out('Q');
	    }
	    parent::_endpage();
	}
	
}

class Certificate {
	
	function getCertificateList($name_filter = false, $code_filter = false) {
		
		$cert = array(); 
		$query_certificate = "
		SELECT id_certificate, name, description, base_language, cert_structure, code
		FROM ".$GLOBALS['prefix_lms']."_certificate";
		
		if ($name_filter && $code_filter)
			$query_certificate .= " WHERE name LIKE '%".$name_filter."%'" .
									" AND code LIKE '%".$code_filter."%'";
		elseif ($name_filter)
			$query_certificate .= " WHERE name LIKE '%".$name_filter."%'";
		elseif ($code_filter)
			$query_certificate .= " WHERE code LIKE '%".$code_filter."%'";
		
		$query_certificate .= " ORDER BY name";
		
		$re_certificate = mysql_query($query_certificate);
		
		while($row = mysql_fetch_row($re_certificate))
		{
			$cert[$row[CERT_ID]] = $row;
		}
		
		return $cert;
	}
	
	function getCourseCertificate($id_course) {
		
		$cert = array(); 
		$query_certificate = "
		SELECT id_certificate, available_for_status
		FROM ".$GLOBALS['prefix_lms']."_certificate_course
		WHERE id_course = '".$id_course."' ";
		$re_certificate = mysql_query($query_certificate);
		while(list($id, $available_for_status) = mysql_fetch_row($re_certificate)) {
			
			$cert[$id] = $available_for_status;
		}
		return $cert;
	}
	
	/**
	 * @return array 	idcourse => array( idcert => array( CERT_ID, CERT_NAME, CERT_DESCR, CERT_LANG, CERT_STRUCTURE, CERT_ID_COURSE, CERT_AV_STATUS ) )
	 */
	function certificateForCourses($arr_course = false, $base_language = false) {
		
		$query_certificate = ""
		." SELECT c.id_certificate, c.name, c.description, c.base_language, course.id_course, course.available_for_status "
		." FROM ".$GLOBALS['prefix_lms']."_certificate AS c "
		." 		JOIN ".$GLOBALS['prefix_lms']."_certificate_course AS course"
		." WHERE c.id_certificate = course.id_certificate "
		." 		AND course.available_for_status <> '".AVS_NOT_ASSIGNED."' ";
		if($arr_course !== false && !empty($arr_course)) {
			$query_certificate .= " AND course.id_course IN ( ".implode(',', $arr_course)." )";
		}
		if($base_language !== false) {
			$query_certificate .= " AND c.base_language = '".$base_language."' ";
		}
		$query_certificate .= " ORDER BY course.available_for_status, c.name";
		
		$re = mysql_query($query_certificate);
		if(!$re) return array();
		
		$list_of = array();
		while($row = mysql_fetch_row($re)) {
			
			$list_of[$row[CERT_ID_COURSE]][$row[CERT_ID]] = $row;
		}
		return $list_of;
	}
	
	function numberOfCertificateReleased($id_certificate = false) {
		
		$query_certificate = "
		SELECT id_certificate, COUNT(*)
		FROM ".$GLOBALS['prefix_lms']."_certificate_assign
		WHERE 1 ";
		if($id_certificate !== false) $query_certificate .= " AND id_certificate = '".$id_certificate."' ";
		
		$re = mysql_query($query_certificate);
		if(!$re) return array();
		
		$list_of = array();
		while(list($id_c, $number) = mysql_fetch_row($re)) {
			$list_of[$id_c] = $number;
		}
		reset($list_of);
		if($id_certificate !== false) return current($list_of);
		return $list_of;
	}
	
	function certificateReleased($id_user, $arr_course = false) {
		
		$query_certificate = "
		SELECT id_course, id_certificate, on_date
		FROM ".$GLOBALS['prefix_lms']."_certificate_assign
		WHERE id_user = '".$id_user."' ";
		if($arr_course) {
			$query_certificate .= " AND id_course IN ( ".implode(',', $arr_course)."";
		}
		$re = mysql_query($query_certificate);
		if(!$re) return array();
		
		$list_of = array();
		while(list($id_course, $id_cert, $on_date) = mysql_fetch_row($re)) {
			
			$list_of[$id_course][$id_cert] = $on_date;
		}
		return $list_of;
	}
	
	function certificateReleasedMultiUser($arr_user = false, $arr_course = false) {
		
		$query_certificate = "
		SELECT id_user, id_certificate, id_course, on_date
		FROM ".$GLOBALS['prefix_lms']."_certificate_assign
		WHERE 1 ";
		if(is_array($arr_user) && !empty($arr_user)) {
			$query_certificate .= " AND id_user IN ( ".implode(',', $arr_user)."";
		}
		if(is_array($arr_course) && !empty($arr_course)) {
			$query_certificate .= " AND id_course IN ( ".implode(',', $arr_course)."";
		}
		$re = mysql_query($query_certificate);
		if(!$re) return array();
		
		$list_of = array();
		while(list($id_user, $id_course, $id_cert, $on_date) = mysql_fetch_row($re)) {
			
			$list_of[$id_user][$id_cert]['on_date'] = $on_date;
			$list_of[$id_user][$id_cert]['id_course'] = $id_course;
		}
		return $list_of;
	}
	
	function certificateReleasedForCourse($id_course) {
		
		$query_certificate = "
		SELECT id_user, id_certificate, on_date
		FROM ".$GLOBALS['prefix_lms']."_certificate_assign
		WHERE id_course = '".$id_course."' ";
		$re = mysql_query($query_certificate);
		if(!$re) return array();
		
		$list_of = array();
		while(list($id_user, $id_cert, $on_date) = mysql_fetch_row($re)) {
			$list_of[$id_user][$id_cert] = $on_date;
		}
		return $list_of;
	}
	
	function isReleased($id_certificate, $id_course, $id_user) {
		
		$query_certificate = "
		SELECT cert_file
		FROM ".$GLOBALS['prefix_lms']."_certificate_assign
		WHERE id_certificate = '".$id_certificate."'
			 AND id_course = '".$id_course."' 
			 AND id_user = '".$id_user."' ";
		
		$re = mysql_query($query_certificate);
		if(!$re) return false;
		return (mysql_num_rows($re) > 0);
	}
	
	function canRelease($av_for_status, $user_status) {
		
		require_once($GLOBALS['where_lms'].'/lib/lib.course.php');
		
		switch($av_for_status) {
			case AVS_NOT_ASSIGNED 				: { return false; };break;
			case AVS_ASSIGN_FOR_ALL_STATUS 		: { return true; };break;
			case AVS_ASSIGN_FOR_STATUS_INCOURSE : { return ($user_status == _CUS_BEGIN); };break;
			case AVS_ASSIGN_FOR_STATUS_COMPLETED : { return ($user_status == _CUS_END); };break;
		}
		return false;
	}
	
	function updateCertificateCourseAssign($id_course, $list_of_assign) {
		
		$cert = array(); 
		$query_certificate = "SELECT id_certificate, available_for_status FROM ".$GLOBALS['prefix_lms']."_certificate_course WHERE id_course = '".$id_course."' ";
		$re_certificate = mysql_query($query_certificate);
		while(list($id, $available_for_status) = mysql_fetch_row($re_certificate)) { $actual_assign[$id] = $available_for_status; }
		
		$re = true;
		foreach($list_of_assign as $id_cert => $new_status) {
			
			if(($new_status == '0') && isset($actual_assign[$id_cert])) {
				// delete
				$query_certificate = "
				DELETE FROM ".$GLOBALS['prefix_lms']."_certificate_course
				WHERE id_certificate = '".$id_cert."' AND id_course = '".$id_course."' ";
				
			} elseif(isset($actual_assign[$id_cert])) {
				// update
				$query_certificate = "
				UPDATE ".$GLOBALS['prefix_lms']."_certificate_course
				SET available_for_status = '".$new_status."'
				WHERE id_certificate = ".(int)$id_cert." AND id_course = ".(int)$id_course." ";
			} else {
				// insert
				$query_certificate = "
				INSERT INTO ".$GLOBALS['prefix_lms']."_certificate_course
				( id_certificate, id_course, available_for_status ) VALUES 
				( ".(int)$id_cert.", ".(int)$id_course.", ".(int)$new_status." )";
			}
			$re &= mysql_query($query_certificate);	
		}
		return $re;
	}
	
	function getSubstitutionArray($id_user, $id_course) {
		
		$query_certificate = "
		SELECT file_name, class_name
		FROM ".$GLOBALS['prefix_lms']."_certificate_tags ";
		$re = mysql_query($query_certificate);
		
		$subst = array();
		while(list($file_name, $class_name) = mysql_fetch_row($re)) {
			
			if(file_exists($GLOBALS['where_lms'].'/lib/certificate/'.$file_name)) {
				
				require_once($GLOBALS['where_lms'].'/lib/certificate/'.$file_name);
				$instance = new $class_name($id_user, $id_course);
				$this_subs = $instance->getSubstitution();
				$subst = $subst + $this_subs;
			}
		}
		return $subst;	
	}
	
	function send_preview_certificate($id_certificate, $array_substituton = false) {
		
		$query_certificate = "
		SELECT name, cert_structure, base_language, orientation, bgimage
		FROM ".$GLOBALS['prefix_lms']."_certificate
		WHERE id_certificate = '".$id_certificate."'";
		list($name, $cert_structure, $base_language, $orientation, $bgimage) = mysql_fetch_row(mysql_query($query_certificate));
		
		require_once($GLOBALS['where_framework'].'/addons/html2pdf/html2fpdf.php');
		
		if($array_substituton !== false) {
			$cert_structure = str_replace(array_keys($array_substituton), $array_substituton, $cert_structure);
		}
		$cert_structure = fillSiteBaseUrlTag($cert_structure);
		
		$name = str_replace(
			array('\\', '/', 	':', 	'\'', 	'\*', 	'?', 	'"', 	'<', 	'>', 	'|'),
			array('', 	'', 	'', 	'', 	'', 	'', 	'', 	'', 	'', 	'' ),
			$name
		);
		ob_end_clean();
		$h2p=new FPDF_Protection($orientation);
		$h2p->SetProtection(array('print'));
		$h2p->Open();
		$h2p->AddPage();
		
		if($bgimage != '') {
		    $h2p->setXY(0, 0);
		    $h2p->Image($GLOBALS['where_files_relative'].'/doceboLms/certificate/'.$bgimage, 2, 2, ( $orientation == 'P' ? 206 : 275 ) );
		    $h2p->setXY(0, 0);
		}
		
		$h2p->UseCSS(true);
		
		$h2p->WriteHTML($cert_structure);
		$h2p->Output(('"'.$name.'.pdf"'), 'D');
		die();
	}
	
	
	function send_facsimile_certificate($id_certificate, $id_user, $id_course, $array_substituton = false) {
		
		$query_certificate = "
		SELECT name, cert_structure, base_language, orientation, bgimage
		FROM ".$GLOBALS['prefix_lms']."_certificate
		WHERE id_certificate = '".$id_certificate."'";
		list($name, $cert_structure, $base_language, $orientation, $bgimage) = mysql_fetch_row(mysql_query($query_certificate));
		
		require_once($GLOBALS['where_framework'].'/addons/html2pdf/html2fpdf.php');
		
		if($array_substituton !== false) {
			$cert_structure = str_replace(array_keys($array_substituton), $array_substituton, $cert_structure);
		}
		$cert_structure = fillSiteBaseUrlTag($cert_structure);
		
		ob_end_clean();
		$h2p = new HTML2FPDF_facs($orientation);
		$h2p->Open();
		$h2p->SetProtection(array('print'));
		$h2p->AddPage();
		
		if($bgimage != '') {
		    $h2p->Image($GLOBALS['where_files_relative'].'/doceboLms/certificate/'.$bgimage, 2, 2, ( $orientation == 'P' ? 206 : 275 ) );
		    $h2p->setXY(0, 0);
		}
		$h2p->UseCSS(true); 
		$h2p->WriteHTML($cert_structure);
		
		
		$name = str_replace(
			array('\\', '/', 	':', 	'\'', 	'\*', 	'?', 	'"', 	'<', 	'>', 	'|'),
			array('', 	'', 	'', 	'', 	'', 	'', 	'', 	'', 	'', 	'' ),
			$name
		);

		$h2p->PlaceWater();
		// maybe there is a way to understand why ie6 doesn't use correctly the 'D' option, but this work so....
		
		$pdf = $h2p->Output(('"'.$name.'.pdf"'), 'S');
		
		session_write_close();
		//ini_set("output_buffering", 0);
		//Download file
		//send file length info
		header('Content-Length:'. strlen($pdf));
		//content type forcing dowlad
		header("Content-type: application/download\n");
		//cache control
		header("Cache-control: private");
		//sending creation time
		header('Expires: ' . gmdate('D, d M Y H:i:s') . ' GMT');
		//content type
		header('Content-Disposition: attachment; filename="'.$name.'.pdf"');
		
		
		echo $pdf; 
		exit();
	}
	
	function send_certificate($id_certificate, $id_user, $id_course, $array_substituton = false) {
		
		$query_certificate = "
		SELECT cert_file
		FROM ".$GLOBALS['prefix_lms']."_certificate_assign 
		WHERE id_certificate = '".$id_certificate."'
			 AND id_course = '".$id_course."' 
			 AND id_user = '".$id_user."' ";
		$re = mysql_query($query_certificate);
		echo mysql_error();
		if((mysql_num_rows($re) > 0)) {
			
			require_once($GLOBALS['where_framework'].'/lib/lib.download.php');
			list($cert_file) = mysql_fetch_row($re);
			sendFile(CERTIFICATE_PATH, $cert_file);
			return;
		}
		
		$query_certificate = "
		SELECT name, cert_structure, base_language, orientation, bgimage
		FROM ".$GLOBALS['prefix_lms']."_certificate
		WHERE id_certificate = '".$id_certificate."'";
		list($name, $cert_structure, $base_language, $orientation, $bgimage) = mysql_fetch_row(mysql_query($query_certificate));
		
		require_once($GLOBALS['where_framework'].'/lib/lib.upload.php');
		require_once($GLOBALS['where_framework'].'/addons/html2pdf/html2fpdf.php');
		
		if($array_substituton !== false) {
			$cert_structure = str_replace(array_keys($array_substituton), $array_substituton, $cert_structure);
		}
		$cert_structure = fillSiteBaseUrlTag($cert_structure);
		
		ob_end_clean();
		
		$h2p = new FPDF_Protection($orientation);
		$h2p->SetProtection(array('print'));
		$h2p->Open();

		$h2p->AddPage();
		
		$h2p->UseCSS(true);
		
		
		if($bgimage != '') {
		    $h2p->Image($GLOBALS['where_files_relative'].'/doceboLms/certificate/'.$bgimage, 2, 2, ( $orientation == 'P' ? 206 : 275 ) );
		    $h2p->setXY(0, 0);
		}
		
		$h2p->WriteHTML($cert_structure);
		
		$name = str_replace(
			array('\\', '/', 	':', 	'\'', 	'\*', 	'?', 	'"', 	'<', 	'>', 	'|'),
			array('', 	'', 	'', 	'', 	'', 	'', 	'', 	'', 	'', 	'' ),
			$name
		);
		$cert_file = $id_course.'_'.$id_certificate.'_'.time().'_'.$name.'.pdf';
		
		sl_open_fileoperations();
		if(!$fp = sl_fopen(CERTIFICATE_PATH.$cert_file, 'w')) { sl_close_fileoperations(); return false; }
		if(!fwrite($fp, $h2p->Output(($name.'.pdf'), 'S'))) { sl_close_fileoperations(); return false; }
		fclose($fp);
		sl_close_fileoperations();
		
		//save the generated file in database
		$query = "INSERT INTO ".$GLOBALS['prefix_lms']."_certificate_assign " 
		." ( id_certificate, id_course, id_user, on_date, cert_file ) "
		." VALUES "
		." ( '".$id_certificate."', '".$id_course."', '".$id_user."', '".date("Y-m-d H:i:s")."', '".addslashes($cert_file)."' ) ";
		if(!mysql_query($query)) return false;
		
		// maybe there is a way to understand why ie6 doesn't use correctly the 'D' option, but this work so....
		
		$pdf = $h2p->Output(('"'.$name.'.pdf"'), 'S');
		
		session_write_close();
		//ini_set("output_buffering", 0);
		//Download file
		//send file length info
		header('Content-Length:'. strlen($pdf));
		//content type forcing dowlad
		header("Content-type: application/download\n");
		//cache control
		header("Cache-control: private");
		//sending creation time
		header('Expires: ' . gmdate('D, d M Y H:i:s') . ' GMT');
		//content type
		header('Content-Disposition: attachment; filename="'.$name.'.pdf"');
		
		echo $pdf; 
		exit();
	}
	
	function getCourseForCertificate($id_certificate)
	{
		$id_course = array();
		
		$query_id_course = "SELECT id_course" .
						" FROM ".$GLOBALS['prefix_lms']."_certificate_course" .
						" WHERE id_certificate = '".$id_certificate."'" .
						" AND available_for_status <> '".AVS_NOT_ASSIGNED."'";
		
		$result_id_course = mysql_query($query_id_course);
		
		while (list($id_course_find) = mysql_fetch_row($result_id_course))
			$id_course[] = $id_course_find;
		
		return $id_course;
	}
	
	function getInfoForCourseCertificate($id_course, $id_certificate, $id_user = false)
	{
		$info = array();
		
		$query = "SELECT *" .
				" FROM ".$GLOBALS['prefix_lms']."_certificate_assign" .
				" WHERE id_certificate = '".$id_certificate."'" .
				" AND id_course = '".$id_course."'";
		if ($id_user)
			$query .= " AND id_user = $id_user";
		
		$result = mysql_query($query);
		
		while ($row = mysql_fetch_row($result))
			$info[] = $row; 
		
		return $info;
	}
	
	function getCertificateInfo($id_certificate)
	{
		$info = array();
		
		$query = "SELECT id_certificate, name, description, base_language, cert_structure" .
				" FROM ".$GLOBALS['prefix_lms']."_certificate" .
				" WHERE id_certificate = '".$id_certificate."'";
		
		$result = mysql_query($query);
		
		while ($row = mysql_fetch_row($result))
			$info[$row[CERT_ID]] = $row; 
		
		return $info;
	}
	
	function delCertificateForUserInCourse($id_certificate, $id_user, $id_course)
	{
		$query = "DELETE " .
				" FROM ".$GLOBALS['prefix_lms']."_certificate_assign " .
				" WHERE id_certificate = '".$id_certificate."'" .
				" AND id_course = '".$id_course."'" .
				" AND id_user = '".$id_user."'";
		
		return mysql_query($query);
	}
	
	function getNumberOfCertificateForCourse($id_certificate, $id_course)
	{
		$query = "SELECT COUNT(*)" .
				" FROM ".$GLOBALS['prefix_lms']."_certificate_assign" .
				" WHERE id_certificate = '".$id_certificate."'" .
				" AND id_course = '".$id_course."'";
		
		list ($res) = mysql_fetch_row(mysql_query($query));
		
		return $res;
	}
}


?>