<?php
require_once('class.stdModule.php');

class ckwz0100 extends stdModule {

	function ckwz0100() {
	
		$this->stdModule('ckwz0100');
		$this->_settings->templates->overview		= "overview.tpl";
		$this->_settings->templates->aanmelden		= "aanmelden.tpl";
		$this->_settings->templates->detail			= "detail.tpl";
		$this->_settings->templates->afmelden		= "afmelden.tpl";
		$this->_settings->templates->bewerken		= "bewerken.tpl";
	}
	
	
	function printOverview($page) {
        
		$applicants = $this->getApplicants($page, $_REQUEST);
		
		$this->_smrt->assign('job_area', 			$this->getOptFields('job_area'));
		$this->_smrt->assign('job_org_type', 		$this->getOptFields('job_org_type'));
		//$this->_smrt->assign('job_function', 		$this->getOptFields('job_function'));
		$this->_smrt->assign('job_function_type', 	$this->getOptFields('job_function_type'));
		$this->_smrt->assign('job_experience', 		$this->getOptFields('job_experience'));
		
		$this->assignTemplateGlobals();	
		$this->_smrt->display($this->_settings->templates->overview);

	}
	
	function getApplicants($page=1, $data) {
		
		$page = (isset($page) && $page != '') ? $page : 1;
		
		$max = 20;
		$offset = is_numeric($page) ? ($page-1)*$max : 0; 
		
		$joins = array();
		// regio's
		$_SESSION['job_area'] = $data['job_area'];
		if ($data['job_area'] != '0' && is_numeric($data['job_area'])) {	
			$joins[] = "inner join ckwz_app_fop as caf_job_area 
							on ca.app_id = caf_job_area.app_id and caf_job_area.fop_id = ".$data['job_area'];
		}
		// soort org
		$_SESSION['job_org_type'] = $data['job_org_type'];
		if ($data['job_org_type'] != '0' && is_numeric($data['job_org_type'])) {
			
			$joins[] = "inner join ckwz_app_fop as caf_job_org_type 
							on ca.app_id = caf_job_org_type.app_id and caf_job_org_type.fop_id = ".$data['job_org_type'];
		}
		// werktijden
		$_SESSION['job_function_type'] = $data['job_function_type'];
		if ($data['job_function_type'] != '0' && is_numeric($data['job_function_type'])) {	
			$joins[] = "inner join ckwz_app_fop as caf_job_function_type 
							on ca.app_id = caf_job_function_type.app_id and caf_job_function_type.fop_id = ".$data['job_function_type'];
		}
		// ervaring
		$_SESSION['job_experience'] = $data['job_experience'];
		if ($data['job_experience'] != '0' && is_numeric($data['job_experience'])) {
			$joins[] = "inner join ckwz_app_fop as caf_job_experience
							on ca.app_id = caf_job_experience.app_id and caf_job_experience.fop_id = ".$data['job_experience'];
		}
		
		$ands = array();
		// beschikbaarheid
		if ($data['app_type_available'] !='') {
			$datum = implode('-', array_reverse(explode('-',$data['app_type_available'])));
			$_SESSION['app_type_available'] = $datum;
			$ands[] = " and ca.app_type_available <= '".$datum."' ";	
		} else {
			if (isset($_SESSION['app_type_available'])) unset($_SESSION['app_type_available']);
		}
				
		$sql = "SELECT SQL_CALC_FOUND_ROWS distinct ca.app_id, ca.*
				
				from ckwz_applicants ca
				".implode(' ', $joins)."
					
				where ca.app_active = 'Y'
				".implode (' ', $ands). "
				order by ca.app_created_on desc";
		
		//print $sql;
					
		$rst = $this->_db->Query($sql, $offset, $max);
		$data = array();
		$i = 0;
		while($rst->Row()){
			$data[$i]				= $rst->Row();
			$data[$i]['naam'] 		= $rst->Row('app_firstname').' '.$rst->Row('app_infix').' '.$rst->Row('app_surname');
			
			$rst->MoveNext();
			$i++;
		}
		$rst->Close();
		
		$tmp = $this->_db->query('SELECT FOUND_ROWS()');
		$total_found_rows_adres = $tmp->Row('FOUND_ROWS()');

		$this->_smrt->assign('search_total', $total_found_rows_adres);
		$this->_smrt->assign('search_page', $page);
		$this->_smrt->assign('search_prev', ($page-1));
		$this->_smrt->assign('search_next', ($page+1));
		
		$this->_smrt->assign('search_pages', ceil($total_found_rows_adres/$max));
		$this->_smrt->assign('search_per_page', $max);
		$this->_smrt->assign('applicants', $data);
	
	}
	
		
	function aanmelden() {
		
		$this->getOptions();
		$this->_smrt->assign('handle', 'aanmelden');
		
		$this->assignTemplateGlobals();	
		$this->_smrt->display($this->_settings->templates->aanmelden);
	}
	
	function bedankt() {
		$this->_smrt->assign('handle', 'bedankt');
		$this->assignTemplateGlobals();	
		$this->_smrt->display($this->_settings->templates->aanmelden);
	}
	
	function bewerkt() {
		$this->_smrt->assign('handle', 'bewerkt');
		$this->assignTemplateGlobals();	
		$this->_smrt->display($this->_settings->templates->aanmelden);
	}
	
	function bewerken($app_id) {
		
		$this->getOptions();
		$this->_smrt->assign('data', $this->getApplicant($app_id));
		$this->_smrt->assign('handle', 'bewerken');
		$this->assignTemplateGlobals();	
		$this->_smrt->display($this->_settings->templates->aanmelden);
	}
	
	
	function afmelden() {
		
		$this->_smrt->display($this->_settings->templates->afmelden);
	}
	
	function printDetail($app_id) {
		
		if (!isset($app_id) && !is_numeric($app_id)) return false;
		
		$sql = "select ca.*, cb.bin_id from ckwz_applicants as ca
				left join ckwz_bin as cb on ca.app_id = cb.app_id
				where ca.app_id = " . $app_id;
				
		$rst = $this->_db->Query($sql);
		
		$data = array();
		while ($c = $rst->Row()) {
			$data = $c;
			$data['app_birthdate_fmt']	= implode('-',array_reverse(explode('-',$data['app_birthdate'])));
			$data['app_graduated_on_fmt']	= implode('-',array_reverse(explode('-',$data['app_graduated_on'])));
			$data['job_area'] 			= $this->getOptValues('job_area',$app_id);
			$data['job_function'] 		= $this->getOptValues('job_function',$app_id);
			$data['job_org_type'] 		= $this->getOptValues('job_org_type',$app_id);
			$data['job_function_type'] 	= $this->getOptValues('job_function_type',$app_id);
			$data['job_experience'] 	= $this->getOptValues('job_experience',$app_id);
			$rst->moveNext();
		}
		
		$rst->Close();
		
		$this->_smrt->assign('data', $data);
		$this->assignTemplateGlobals();	
		$this->_smrt->display($this->_settings->templates->detail);
	}
	
	function getOptValues($option, $app_id) {
	
		$sql = "select caf.fop_id, cfo.fop_opt_value
				from ckwz_app_fop as caf
				left join ckwz_field_options as cfo on caf.fop_id = cfo.fop_id
				where caf.app_id = ".$app_id."
				and cfo.fop_opt_code = '".$option."'";
		$rst = $this->_db->Query($sql);
		return rs2arr($rst);
				
	}
	
	function checkApplicant($app_id, $security_code) {
		
		if (!is_numeric($app_id) || $security_code == '') return false;
		
		$sql = "select count(app_id) as count 
					from ckwz_applicants where app_id = ".$app_id." 
					and app_security_token = '" . mysql_real_escape_string($security_code) . "'";
		$rst = $this->_db->Query($sql);
		if ($rst->Row('count') == 1) return true;
		return false;
	}
	
	function activateApplicant($app_id) {
	
		if (!is_numeric($app_id)) return false;
		
		$this->_db->Execute("UPDATE ckwz_applicants SET app_active = 'Y' WHERE app_id =".$app_id);
		
	}
	
	function saveForm($form_data) {
		
		$app_id = $this->saveApplicant($form_data);
		
		if ($_FILES['app_cv']) {
			$this->savePdf($app_id, $FILES);
		}
	
		$opt = $this->saveOptions($app_id, $form_data);
		
		return true;
		
	}
	
	function saveApplicant($form_data) {
		
		//print "<pre>".print_r($form_data)."</pre>";die();
		
		if (isset($form_data['app_id']) && is_numeric($form_data['app_id'])) {
			$data['app_id'] 			= "'" . $form_data['app_id'] . "'";
			$data['app_updated_on'] 	= "NOW()";
			$data['app_active']			= "'" . mysql_real_escape_string($form_data['app_active']) . "'";
		} else {
			$data['app_created_on'] 	= "NOW()";
			$data['app_updated_on'] 	= "NOW()";
			$data['app_active']			= "'N'";
			$security_code 				= $this->generateToken();
			$data['app_security_token']	= "'" . $security_code . "'";
		}
		
		$data['app_firstname'] 		= "'" . mysql_real_escape_string($form_data['app_firstname']) . "'";
		$data['app_surname'] 		= "'" . mysql_real_escape_string($form_data['app_surname']) . "'";
		$data['app_email'] 			= "'" . mysql_real_escape_string($form_data['app_email']) . "'";
		
		if (isset($form_data['app_infix'])) 	$data['app_infix'] 		= "'" . mysql_real_escape_string($form_data['app_infix']) . "'";
		if (isset($form_data['app_gender'])) 	$data['app_gender'] 	= "'" . mysql_real_escape_string($form_data['app_gender']) . "'";
		if (isset($form_data['app_birthdate'])) $data['app_birthdate'] 	= "'" . mysql_real_escape_string(implode('-',array_reverse(explode('-',$form_data['app_birthdate'])))) . "'";
		if (isset($form_data['app_address'])) 	$data['app_address'] 	= "'" . mysql_real_escape_string($form_data['app_address']) . "'";
		if (isset($form_data['app_zipcode'])) 	$data['app_zipcode'] 	= "'" . mysql_real_escape_string($form_data['app_zipcode']) . "'";
		if (isset($form_data['app_city']))		$data['app_city'] 		= "'" . mysql_real_escape_string($form_data['app_city']) . "'";
		if (isset($form_data['app_phone']))		$data['app_phone'] 		= "'" . mysql_real_escape_string($form_data['app_phone']) . "'";
		if (isset($form_data['app_mobile'])) 	$data['app_mobile'] 	= "'" . mysql_real_escape_string($form_data['app_mobile']) . "'";
		
		if (isset($form_data['app_func_other']))$data['app_func_other']	= "'" . mysql_real_escape_string($form_data['app_func_other']) . "'";
		
		if (isset($form_data['app_type_available']))	$data['app_type_available'] = "'" . mysql_real_escape_string(implode('-',array_reverse(explode('-',$form_data['app_type_available'])))) . "'";
		
		if (isset($form_data['app_vaste_baan'])) {
			$data['app_vaste_baan'] = "'Y'";
		} 
		else {
			$data['app_vaste_baan'] = "'N'";
		}
		
		if (isset($form_data['app_waarneming'])) {
			$data['app_waarneming'] = "'Y'";
		} 
		else {
			$data['app_waarneming'] = "'N'";
		}

		if (isset($form_data['app_waarn_a_start'])) $data['app_waarn_a_start'] 	= "'" . mysql_real_escape_string(implode('-',array_reverse(explode('-',$form_data['app_waarn_a_start'])))) . "'";
		if (isset($form_data['app_waarn_a_stop'])) 	$data['app_waarn_a_stop'] 	= "'" . mysql_real_escape_string(implode('-',array_reverse(explode('-',$form_data['app_waarn_a_stop'])))) . "'";
		if (isset($form_data['app_waarn_b_start'])) $data['app_waarn_b_start'] 	= "'" . mysql_real_escape_string(implode('-',array_reverse(explode('-',$form_data['app_waarn_b_start'])))) . "'";
		if (isset($form_data['app_waarn_b_stop'])) 	$data['app_waarn_b_stop'] 	= "'" . mysql_real_escape_string(implode('-',array_reverse(explode('-',$form_data['app_waarn_b_stop'])))) . "'";
		if (isset($form_data['app_waarn_c_start'])) $data['app_waarn_c_start'] 	= "'" . mysql_real_escape_string(implode('-',array_reverse(explode('-',$form_data['app_waarn_c_start'])))) . "'";
		if (isset($form_data['app_waarn_c_stop'])) 	$data['app_waarn_c_stop'] 	= "'" . mysql_real_escape_string(implode('-',array_reverse(explode('-',$form_data['app_waarn_c_stop'])))) . "'";	

		if (isset($form_data['app_graduated_on'])) 	$data['app_graduated_on'] 	= "'" . mysql_real_escape_string(implode('-',array_reverse(explode('-',$form_data['app_graduated_on'])))) . "'";	
		
		if (isset($form_data['app_extra_info'])) $data['app_extra_info'] = "'" . mysql_real_escape_string($form_data['app_extra_info']) . "'";
		
		$query = generate_query('ckwz_applicants',$data, 'app_id');
		$this->_db->Execute($query);
		
		if (isset($form_data['app_id']) && is_numeric($form_data['app_id'])) {
			$app_id =  $form_data['app_id'];
		} else {
			$app_id =  $this->_db->lastInsertID();
			// verstuur email
			
			$err = $this->_tcc->doCall("SEND_EMAIL", array(
				'code'		=> 'WZAANMLD'
			,	2			=> array (
					'to' 	=> $form_data['app_email'],
					'toname'=> $form_data['app_surname']
				)
			,	'org_id'	=> $this->_variables->public->org_id
			,	'lng_code'	=> 'nl'
			,	'variables'	=> 	array(
									'NAME'	=> $form_data['app_surname']
								, 	'URL'	=> "http://www." . $this->_tcc->_settings->general->project->mainhostname . "/werkzoekenden/bewerken/&id=".$app_id."&sc=".$security_code
								)
			,	'debug'		=> false
			), $return , true);	
			if ($err->id != ICH_RESPONSE_OK) die($err->msg);
		}
		
		return $app_id;
	}
	
	function saveOptions($app_id, $form_data) {
		
		if(!isset($app_id) && !is_numeric($app_id)) return false;
		
		// clean applicant options
		$this->_db->Execute("delete from ckwz_app_fop where app_id =".$app_id);
		
		// functie
		$sql_values = array();
		if (isset($form_data['job_function'])) {
			foreach($form_data['job_function'] as $key=>$val) {
				$sql_values[] = "('" . $app_id . "','" . mysql_real_escape_string($val) . "') ";
			}
		}
		//$sql .= " ('" . $app_id . "','" . mysql_real_escape_string($form_data['job_function']) . "') ";
		// ervaring
		$sql_values[] = " ('" . $app_id . "','" . mysql_real_escape_string($form_data['job_experience']) . "') ";
		if (isset($form_data['job_area'])) {
			foreach($form_data['job_area'] as $val) {
				$sql_values[] = "('" . $app_id . "','" . mysql_real_escape_string($val) . "') ";
			}
		}	
		if (isset($form_data['job_org_type'])) {
			foreach($form_data['job_org_type'] as $val) {
				$sql_values[] = "('" . $app_id . "','" . mysql_real_escape_string($val) . "') ";
			}
		}
		if (isset($form_data['job_function_type'])) {
			foreach($form_data['job_function_type'] as $val) {
				$sql_values[] = "('" . $app_id . "','" . mysql_real_escape_string($val) . "') ";
			}
		}
		
		if ($sql_values) {
				$sql = "insert into ckwz_app_fop (app_id , fop_id) values "
					. implode(', ', $sql_values);
				$this->_db->Execute($sql);
		}
		
		return true;
	}
	
	function savePdf($app_id, $pdf_data) {
		
		if(!isset($app_id) && !is_numeric($app_id)) return false;
		
		if (file_exists($_FILES['app_cv']['tmp_name'])) {
			$this->_db->Execute("delete from ckwz_bin where app_id =".$app_id);
			
			$fstream = fopen($_FILES['app_cv']['tmp_name'], "r");
			$contents = addslashes(fread ($fstream, filesize ($_FILES['app_cv']['tmp_name'])));
			fclose($fstream);
			
			$this->_db->Execute("insert into ckwz_bin 
				(app_id, bin_createdate, bin_name, bin_bin, bin_mime_type, bin_size) 
					values (
						" . $app_id . "
						, NOW()
						, '" . $_FILES['app_cv']['name'] . "'
						, '" . $contents . "'
						, '" . $_FILES['app_cv']['type'] . "'
						, '" . $_FILES['app_cv']['size'] . "'
					)");
		}
	}
	
	function getApplicant($app_id) {
		
		if(!isset($app_id) && !is_numeric($app_id)) return false;
		
		$sql = "select ca.*, cb.bin_id from ckwz_applicants as ca
				left join ckwz_bin as cb on ca.app_id = cb.app_id
				where ca.app_id = " . $app_id;
		$rst = $this->_db->Query($sql);	
		$data = array();
		if (!$rst->EOF()) {
			$data['app_id'] 			= $rst->Row('app_id');
			$data['app_firstname'] 		= $rst->Row('app_firstname');
			$data['app_infix'] 			= $rst->Row('app_infix');
			$data['app_surname'] 		= $rst->Row('app_surname');
			$data['app_gender'] 		= $rst->Row('app_gender');
			$data['app_birthdate'] 		= implode('-',array_reverse(explode('-',$rst->Row('app_birthdate'))));
			$data['app_address'] 		= $rst->Row('app_address');
			$data['app_zipcode'] 		= $rst->Row('app_zipcode');
			$data['app_city'] 			= $rst->Row('app_city');
			$data['app_phone'] 			= $rst->Row('app_phone');
			$data['app_mobile'] 		= $rst->Row('app_mobile');
			$data['app_email'] 			= $rst->Row('app_email');
			$data['app_func_other'] 	= $rst->Row('app_func_other');
			$data['app_vaste_baan'] 	= $rst->Row('app_vaste_baan');
			$data['app_waarneming'] 	= $rst->Row('app_waarneming');
			$data['app_type_available'] = $rst->Row('app_type_available');
			//$data['app_type_notice'] 	= $rst->Row('app_type_notice');
			$data['app_waarn_a_start'] 	= $rst->Row('app_waarn_a_start');
			$data['app_waarn_b_start'] 	= $rst->Row('app_waarn_b_start');
			$data['app_waarn_c_start'] 	= $rst->Row('app_waarn_c_start');
			$data['app_waarn_a_stop'] 	= $rst->Row('app_waarn_a_stop');
			$data['app_waarn_b_stop'] 	= $rst->Row('app_waarn_b_stop');
			$data['app_waarn_c_stop'] 	= $rst->Row('app_waarn_c_stop');
			$data['app_graduated_on'] 	= implode('-',array_reverse(explode('-',$rst->Row('app_graduated_on'))));
			$data['app_extra_info'] 	= $rst->Row('app_extra_info');
			$data['app_active'] 		= $rst->Row('app_active');
			$data['app_active'] 		= $rst->Row('app_active');
			$data['bin_id'] 			= $rst->Row('bin_id');
            $data['updated_on']         = $rst->Row('app_updated_on');
			
		}

		$sql = "select
					distinct fop_opt_code
				,	convert(group_concat(ckwz_app_fop.fop_id), char) as fid
				from ckwz_field_options
				left join ckwz_app_fop on ckwz_app_fop.fop_id = ckwz_field_options.fop_id 
					and ckwz_app_fop.app_id = ".$app_id."
				group by fop_opt_code";
		$rst = $this->_db->Query($sql);	
		
		while($rst->Row()){
			if ($rst->Row('fop_opt_code') == 'job_experience') {
				$data[$rst->Row('fop_opt_code')] = $rst->Row('fid');
			} else {
				$data[$rst->Row('fop_opt_code')] = explode(',', $rst->Row('fid'));
			}
			$rst->MoveNext();
		}	
		
		$rst->Close();
		return $data;
	}
	
	function generateToken() {
		
		return md5(strtotime("now"));
	}
	
	function getOptions() {
		
		$this->_smrt->assign('job_area', 			$this->getOptFields('job_area'));
		$this->_smrt->assign('job_function', 		$this->getOptFields('job_function'));
		$this->_smrt->assign('job_org_type', 		$this->getOptFields('job_org_type'));
		$this->_smrt->assign('job_function_type', 	$this->getOptFields('job_function_type'));
		$this->_smrt->assign('job_experience', 		$this->getOptFields('job_experience'));
	}
	
	
	function getOptFields($opt_field_code) {
		
		if ($opt_field_code == '') return false;
		
		$rst = $this->_db->Query("select * from ckwz_field_options 
									where fop_opt_code = '" . mysql_real_escape_string($opt_field_code) . "'
									order by fop_opt_order asc");
		
		$data = array();
		$i = 0;		
		while($rst->Row()){
			$data[$i]['id'] 	= $rst->Row('fop_id');
			$data[$i]['key'] 	= $rst->Row('fop_opt_key');
			$data[$i]['val'] 	= $rst->Row('fop_opt_value');
			$rst->MoveNext();
			$i++;
		}
		$rst->Close();
		
		return $data;
		
	}
	// pdf stuff
	function get_binary($id) {

		if (!is_numeric($id)) return;

		/*if ($bin_type == VWK_BINARY_TYPE_ATT) {
			$sql_where = "bin_id = ". $id;
		} elseif ($bin_type == VWK_BINARY_TYPE_LINK) {
			$sql_where = "bin_lnk_id = ". $id;
		}*/

		$sql = "SELECT bin_name, bin_mime_type, bin_size, bin_bin
				FROM ckwz_bin
				WHERE app_id =". $id;

		$rs = $this->_db->query($sql);

		if ($rs->RowCount && $rs->Row('bin_bin') && $rs->Row('bin_mime_type')) {

			header("Content-type: ".str_replace('-','/',$rs->Row('bin_mime_type')).", Expires: ".gmdate("D, d M Y H:i:s", time() + 3600));
			header("Content-Length: ".$rs->Row('bin_size'));
			header("Content-Disposition: attachment; filename=".$rs->Row('bin_name'));
			header("Cache-Control: max-age=3600, must-revalidate, pre-check=0, post-check=0");
			echo $rs->Row('bin_bin');
			die();

		}

	}
	// cron stuff
	function getActiveApplicants () {
	
		$sql = "select app_id
				, app_email
				, app_infix
				, app_surname
				, app_gender
				, app_security_token 
				, app_updated_on
					from ckwz_applicants
					where app_active = 'Y'
				";
	
		$rst = $this->_db->Query($sql);
		
		$data = array();
		$i = 0;
		while ($rst->Row()) {
			$data[$i]['email'] 		= $rst->Row('app_email');
			$data[$i]['naam']		= $rst->Row('app_infix') . " " . $rst->Row('app_surname');
			$data[$i]['geslacht']	= ($rst->Row('app_gender') == 'V') ? "mevrouw" : "heer";
			$data[$i]['url']		= 'http://www.' . $this->_tcc->_settings->general->project->mainhostname . '/werkzoekenden/bewerken/&id='.$rst->Row('app_id').'&sc='.$rst->Row('app_security_token');
			$data[$i]['updated']	= $rst->Row('app_updated_on');
			$rst->MoveNext();
			$i++;
		}
		$rst->Close();

		return $data;
	}
	
	function sendActivityEmail ($app_arr) {
		
		if (!is_array($app_arr) || empty($app_arr)) return false;
		
		foreach ($app_arr as $data) {
			$err = $this->_tcc->doCall("SEND_EMAIL", array(
					'code'		=> 'WZCRONML'
				,	2			=> array (
						'to' 	=> $data['email'],
						'toname'=> $data['naam']
					)
				,	'org_id'	=> $this->_variables->public->org_id
				,	'lng_code'	=> $this->_variables->public->lng_code
				,	'variables'	=> 	array(
										'GENDER'	=> $data['geslacht']
									, 	'NAME'		=> $data['naam']
									, 	'URL'		=> $data['url']
									)
				,	'debug'		=> false
				), $return , true);	
				if ($err->id != ICH_RESPONSE_OK) die($err->msg);
		}
	}
	
	function checkActivity () {
		
		$sql = "update ckwz_applicants 
					set 
						app_active = 'N' 
					where 
						app_updated_on < DATE_SUB(NOW(), INTERVAL 7 DAY)
					and 
						app_active = 'Y'";
		
		
		return $this->_db->Execute($sql);
		
		
	}
	
}
?>