<?

define('CCRD01_KA_LIST_PER_PAGE', 25);

define('CCRD01_ABO_OK'			, 200);
define('CCRD01_ABO_NOT_STARTED'	, 201);
define('CCRD01_ABO_EXPIRED'		, 202);
define('CCRD01_ABO_NO_SALDO'	, 203);
define('CCRD01_ABO_ABSENT'		, 204);


require_once('class.stdModule.php');

class ccrd0100 extends stdModule {

	function ccrd0100() {
		$this->stdModule('ccrd0100');

		$this->_settings->templates->orderbox				= "orderbox.tpl";
		$this->_settings->templates->error					= "error.tpl";
		$this->_settings->templates->kredietrapport_email	= "kredietrapport_email.tpl";
		$this->_settings->templates->verbruiksoverzicht		= "verbruiksoverzicht.tpl";
		$this->_settings->templates->verbruiksoverzicht_csv	= "verbruiksoverzicht_csv.tpl";
		
		$this->_settings->templates->kredietaanvraag		= "kredietaanvraag.tpl";
		$this->_settings->templates->updateservice			= "updateservice.tpl";

		$this->_settings->templates->faillissement_int		= "faillissement_int.tpl";
		$this->_settings->templates->faillissement_old		= "faillissement_old.tpl";

		



		//$this->_settings->assignTemplateFunction	= '$this->_assignGlobals();';
	
	}

	// quick e-mail function
	function _send_email($code, $usr_id = null, $email_address = null, $variables = null, $attachment = null) {

		if (is_object($this->_tcc)) {

			$attachment = ($attachment) ? array($attachment) : null;

			$err = $this->_tcc->doCall("SEND_EMAIL", array(
					'code'		=> $code
				,	'org_id'	=> $this->_variables->public->org_id
				,	'usr_id'	=> $usr_id
				,	'lng_code'	=> $this->_variables->public->lng_code
				,	'address'	=> array('to' => $email_address)
				,	'variables'	=> $variables
				,	'attachments'=> $attachment
				,	'debug'		=> false
				), $return);

			return $return;

		} else {

			$this->_debug('_send_email::error: no tcc object available', 1);
			trigger_error('no tcc object available in _send_email', E_USER_WARNING);

		}

	}

	function showError($error_message='') {
		
		$this->_smrt->assign('error_message', $error_message);
		$this->_smrt->display($this->_settings->templates->error);

	}

	function _getNextSequence($seq_name) {
		$rs = $this->_db->Query("SELECT ".$seq_name.".NEXTVAL as nextval FROM dual");
		return $rs->Row('NEXTVAL');
	}


	function _getUser($usr_id, $atts=array()) {
		global $g_obj_ti3a0365;
		if (!is_numeric($usr_id)) return false;

		$g_obj_ti3a0365->getUsers(	$users
								,	array(array('USR_ID', '='.$usr_id), 'USR_INFIX','USR_ORG_ID','USR_INITIALS','USR_FAMILYNAME', 'USR_EMAIL')
								,	array('GATT_ORG_STREETNAME','GATT_ORG_STREETNUMBER','GATT_ORG_ZIPCODE','GATT_ORG_CITY','GATT_USR_ORGNAME','GATT_ORG_PHONE','GATT_ORG_KVK','GATT_USR_FUNCTION')
							);

		return current($users);			 

	}


	function _getOrganization($org_id, $atts=array()) {
		global $g_obj_ti3a0365;
		if (!is_numeric($org_id)) return false;

		$atts = ($atts) ? $atts : array('GATT_ORG_STREETNAME','GATT_ORG_STREETNUMBER','GATT_ORG_ZIPCODE','GATT_ORG_CITY','GATT_ORG_NUMBER','GATT_ORG_PHONE','GATT_ORG_KVK','GATT_ORG_EMAIL');

		$g_obj_ti3a0365->getOrganizations(	$orgs
								,	array(array('ORG_ID', '='.$org_id), 'ORG_NAME')
								,	$atts
							);

		return current($orgs);

	}

	/****************************************************************************/
	/* KREDIETRAPPORT AANVRAGEN													*/
	/****************************************************************************/

		function setKredietAanvraag($values) {
			
			if (!is_numeric($_SESSION['session_usr_id'])) {
				return;
			}

			$new_ckre_id = $this->_getNextSequence('CCRD_KREDIET_SEQ');

			$ka_values = array();
			$ka_values['CKRE_ID']				= $new_ckre_id;
			$ka_values['CKRE_USR_ID']			= $_SESSION['session_usr_id'];
			$ka_values['CKRE_ORG_ID']			= (is_numeric($_SESSION['session_org_id']) ? $_SESSION['session_org_id'] : $this->getSetting('default_org_id'));
			$ka_values['CKRE_CSTA_ID']			= ($values['CKRE_US_FREQ']) ? 10 : 1;
			$ka_values['CKRE_REQUEST_DATE']		= 'SYSDATE';
			$ka_values['CKRE_CLOSE_DATE']		= 'NULL';
			$ka_values['CKRE_NAME']				= "'".$this->_db->db_escape_string($values['CKRE_NAME'])."'";
			$ka_values['CKRE_ADDRESS_STREET']	= "'".$this->_db->db_escape_string($values['CKRE_ADDRESS_STREET'])."'";
			$ka_values['CKRE_ADDRESS_NUMBER']	= "'".substr($this->_db->db_escape_string($values['CKRE_ADDRESS_NUMBER']),0,15)."'";
			$ka_values['CKRE_ADDRESS_ZIP']		= "'".substr($this->_db->db_escape_string($values['CKRE_ADDRESS_ZIP']),0,15)."'";
			$ka_values['CKRE_ADDRESS_CITY']		= "'".$this->_db->db_escape_string($values['CKRE_ADDRESS_CITY'])."'";
			$ka_values['CKRE_ADDRESS_PHONE']	= "'".$this->_db->db_escape_string($values['CKRE_ADDRESS_PHONE'])."'";
			$ka_values['CKRE_ADDRESS_COUNTRY']	= "'".($values['CKRE_ADDRESS_COUNTRY'] ? $this->_db->db_escape_string($values['CKRE_ADDRESS_COUNTRY']) : 'NL')."'";
			$ka_values['CKRE_BTW_NUMBER']		= "'".$this->_db->db_escape_string($values['CKRE_BTW_NUMBER'])."'";
			$ka_values['CKRE_CPRO_ID']			= $values['CKRE_CPRO_ID'];
			$ka_values['CKRE_AMOUNT']			= ($values['CKRE_AMOUNT'] ? (float)str_replace(',','.',$values['CKRE_AMOUNT']) : 'NULL');
			$ka_values['CKRE_CURRENCY']			= "'".($values['CKRE_CURRENCY'] ? $values['CKRE_CURRENCY'] : 'EUR')."'";
			$ka_values['CKRE_REASON']			= "'".$this->_db->db_escape_string($values['CKRE_REASON'])."'";
			$ka_values['CKRE_REFERENCE_NAME']	= "'".$this->_db->db_escape_string($values['CKRE_REFERENCE_NAME'])."'";

			$ka_values['CKRE_US_BASE_ID']		= ($values['CKRE_US_FREQ']) ? $new_ckre_id : 'NULL';
			$ka_values['CKRE_US_FREQ']			= (is_numeric($values['CKRE_US_FREQ']) ? (int)$values['CKRE_US_FREQ'] : 'NULL');
			$ka_values['CKRE_BED_ID']			= (is_numeric($values['CKRE_BED_ID']) ? (int)$values['CKRE_BED_ID'] : 'NULL');
			$ka_values['CKRE_PARTNER_ORG_ID']	= (is_numeric($this->_variables->public->org_id) ? $this->_variables->public->org_id : 'NULL');

			$sql = "INSERT INTO CCRD_KREDIET (".implode(", ",array_keys($ka_values)).") VALUES (".implode(", ",array_values($ka_values)).")";

			if ($this->_db->execute($sql)) {

				if (is_numeric($values['CKRE_US_FREQ']) && $values['CKRE_US_FREQ'] > 0) {

					//--- if record is an update-service, create a child record 

					//-- make changes
					$ka_values['CKRE_ID']			= $this->_getNextSequence('CCRD_KREDIET_SEQ');
					$ka_values['CKRE_CSTA_ID']		= '1';
					$ka_values['CKRE_US_BASE_ID']	= $new_ckre_id;

					$strSQL = "INSERT INTO ccrd_krediet (".implode(',',array_keys($ka_values)). ") 
								VALUES (".implode(',',array_values($ka_values)). ")";

					$this->_db->execute($strSQL);
				
				}

				if ($_SESSION['session_org_id']) {
					$org_info = $this->_getOrganization($_SESSION['session_org_id'], array('GATT_CSA_STATUS'));
				}

				return $new_ckre_id;

			} else {
				return false;
			}

		}

		function sendKredietAanvraagMail($ckre_id, $send_to_client=false) {

			$ckre = $this->getKredietAanvraag($ckre_id);
			$applicant = $this->_getUser($ckre['CKRE_USR_ID']);
			
			if (is_numeric($applicant['USR_ORG_ID'])) {
				$org = $this->_getOrganization($applicant['USR_ORG_ID']);
				$applicant = array_merge($applicant, $org);
			}

			if (is_numeric($ckre['CKRE_US_BASE_ID'])) {
				if ($ckre['CKRE_US_BASE_ID'] == $ckre_id) {
					//--- base record, get the first 'child' id
					$rs_child = $this->_db->Query("SELECT CKRE_ID FROM ccrd_krediet WHERE CKRE_ID != ".$ckre_id." AND CKRE_US_BASE_ID = ".$ckre_id);
					$ckre['us_child_id'] = $rs_child->Row('CKRE_ID');
					$ckre['us_state'] = 'Basisrapport met updates';
				} else {
					$ckre['us_state'] = 'Automatische vervolgaanvraag';
				}
				$ckre['us_freq'] = $ckre['CKRE_US_FREQ'];
			}

			//--- create attachment
			$this->_smrt->assign('kr', $ckre);
			$this->_smrt->assign('applicant', $applicant);
			$kr_info = $this->_smrt->fetch($this->_settings->templates->kredietrapport_email);

			$attachment = array(	'data'		=> $kr_info
								,	'filename'	=> 'kredietaanvraag_'.$ckre_id.'.txt'
								,	'mimetype'	=> 'text/plain'
								,	'inline'	=> 0);

			if ($send_to_client) {

				$this->_send_email('CKRE_CLT', $ckre['CKRE_USR_ID'], null, null, $attachment);

			}

			$this->_send_email('CKRE_PAR', null, null, null, $attachment);
		

		}

		function deleteKredietAanvraag($ckre_id) {
			if (!is_numeric($ckre_id)) return false;

			$sql = "DELETE FROM ccrd_krediet WHERE ckre_id = ".$ckre_id;
			$this->_db->execute($sql);

		}

		function removeDupKredietAanvraag($values) {
			
			if (!isset($values['CKRE_USR_ID'])) $values['CKRE_USR_ID'] = $_SESSION['session_usr_id'];
			if (!isset($values['CKRE_ORG_ID'])) $values['CKRE_ORG_ID'] = (is_numeric($_SESSION['session_org_id']) ? $_SESSION['session_org_id'] : $this->getSetting('default_org_id'));

			$sql = "SELECT ckre_id 
					FROM ccrd_krediet 
					WHERE ckre_usr_id = ".$values['CKRE_USR_ID']."
					AND	ckre_org_id = ".$values['CKRE_ORG_ID']."
					AND ckre_request_date > SYSDATE-1
					AND ckre_cpro_id = ".$this->getSetting('creditscore_product_id')."
					AND ckre_csta_id = 1
					AND LOWER(ckre_name) LIKE '%".strtolower($this->_db->db_escape_string($values['CKRE_NAME']))."%'";

			
			$rs = $this->_db->query($sql);
			$dups = $rs->RowCount;
			while ($r = $rs->row()) {
				$this->deleteKredietAanvraag($r['CKRE_ID']);	
				$rs->moveNext();
			}
			$rs->Close();

			return $dups;
		}

		function getKredietAanvraag($ckre_id) {
			if (!is_numeric($ckre_id)) return false;

			$sql = "SELECT * 
					FROM ccrd_krediet 
					LEFT JOIN ccrd_product ON cpro_id = ckre_cpro_id
					LEFT JOIN ccrd_countries ON ccou_code = ckre_address_country
					LEFT JOIN ccrd_aanvraag_status ON csta_id = ckre_csta_id
					WHERE ckre_id = ".$ckre_id;
			$rs = $this->_db->query($sql);
			$ka = $rs->Row();
			$rs->Close();

			return $ka;

		}



	function getTemplatesByProduct($cpro_id) {
		if (!is_numeric($cpro_id)) return false;
		$sql = "SELECT * 
				FROM ccrd_product_templates
				WHERE ctpl_cpro_id = ".$cpro_id;

		$result = array();
		$rs = $this->_db->query($sql);
		while ($r = $rs->row()) {
			$result[] = $r;
			$rs->moveNext();
		}
		$rs->Close();

		return $result;

	}

	function _getCountryList() {
		$rs = $this->_db->Query("SELECT ccou_code, ccou_name FROM ccrd_countries ORDER BY ccou_name");
		return rs2arr($rs, 'CCOU_CODE');
	}

	function _getProductList($org_id=null) {

		if ($org_id && is_numeric($org_id)) {
			$sql = "SELECT cpro_id, cpro_name 
					FROM ccrd_product 
					INNER JOIN ccrd_product_org_connections ON cpoc_cpro_id = cpro_id AND cpoc_org_id = ".$org_id."
					ORDER BY cpro_name";
		} else {
			$sql = "SELECT cpro_id, cpro_name FROM ccrd_product ORDER BY cpro_name";
		}

		$rs = $this->_db->Query($sql);
		return rs2arr($rs, 'CPRO_ID');

	}

	function _getKredietAanvragen($org_id, $filters=array(), $offset=null, $limit=null) {
		if (!is_numeric($org_id)) return false;

		$where = '';
		if (count($filters) > 0) {
			$where = ' AND ' . implode(' AND ', $filters);
		}

		$sql = "SELECT 
					ckre_id
				,	ckre_usr_id
				,	ckre_address_city
				,	ckre_address_street
				,	ckre_address_number
				,	ckre_address_zip
				,	ckre_address_phone
				,	ckre_btw_number
				,	csta_label
				,	TO_CHAR(ckre_request_date,'DD-MM-YYYY') AS ckre_date
				,	TO_CHAR(ckre_close_date,'DD-MM-YYYY') AS ckre_close_date
				,	ckre_name
				,	cpro_name
				,	ckre_reference_name
				,	ccou_name
				FROM ccrd_krediet
				INNER JOIN ccrd_aanvraag_status ON csta_id = ckre_csta_id
				INNER JOIN ccrd_product ON cpro_id = ckre_cpro_id
				INNER JOIN ccrd_countries ON ccou_code = ckre_address_country
				WHERE ckre_org_id = ".$org_id."
				AND	  ((ckre_id != ckre_us_base_id) OR (ckre_us_base_id IS NULL))
				". $where."
				ORDER BY ckre_request_date DESC";

		$rs = $this->_db->Query($sql, $offset, $limit);

		return rs2arr($rs);
	}

	function _getKredietAanvragenCount($org_id, $filters=array()) {
		if (!is_numeric($org_id)) return false;

		$where = '';
		if (count($filters) > 0) {
			$where = ' AND ' . implode(' AND ', $filters);
		}

		$sql = "SELECT COUNT(ckre_id) AS aantal
				FROM CCRD_KREDIET
				WHERE ckre_org_id = ".$org_id."
				AND ((ckre_id != ckre_us_base_id) OR (ckre_us_base_id IS NULL))
				". $where;

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

		return $rs->Row('AANTAL');
	
	}

	function _getKredietAanvragenUpdateService($org_id, $filters=array(), $offset=0) {

		if (!is_numeric($org_id)) return false;

		$where = '';
		if (count($filters) > 0) {
			$where = ' AND ' . implode(' AND ', $filters);
		}


		$sql = "SELECT 
					k1.ckre_id
				,	k1.ckre_usr_id
				,	k1.ckre_address_city
				,	csta_label
				,	TO_CHAR(k1.ckre_request_date,'DD-MM-YYYY') AS ckre_date
				,	TO_CHAR(k1.ckre_close_date,'DD-MM-YYYY') AS ckre_close_date
				,	k1.ckre_name
				,	cpro_name
				,	k1.ckre_reference_name
				,	k1.ckre_us_freq
				,	TO_CHAR(MAX(k2.ckre_close_date), 'DD-MM-YYYY') as last_update
				FROM ccrd_krediet k1
				INNER JOIN ccrd_aanvraag_status ON csta_id = k1.ckre_csta_id
				INNER JOIN ccrd_product ON cpro_id = k1.ckre_cpro_id
				LEFT JOIN ccrd_krediet k2 ON k2.ckre_us_base_id = k1.ckre_id AND k2.ckre_id != k1.ckre_id AND k2.ckre_csta_id = 11
				WHERE
					k1.ckre_org_id = ".$org_id."
				AND k1.ckre_us_base_id = k1.ckre_id
				AND k1.ckre_csta_id = 10
				". $where."
				GROUP BY k1.ckre_id, k1.ckre_usr_id, k1.ckre_address_city, csta_label, k1.ckre_request_date, k1.ckre_close_date, k1.ckre_name, cpro_name, k1.ckre_reference_name, k1.ckre_us_freq
				ORDER BY k1.ckre_request_date DESC";

		$return = array();

		$rs = $this->_db->Query($sql, $offset, CCRD01_KA_LIST_PER_PAGE);
		while ($r = $rs->Row()) {

			if ($r['LAST_UPDATE']) {
				list($d,$m,$y) = explode('-', $r['LAST_UPDATE']);
				$r['NEXT_UPDATE'] = date('d-m-Y', mktime(0,0,0,($m+(12/$r['CKRE_US_FREQ'])),$d,$y));
			} else {
				$r['LAST_UPDATE'] = 'n.v.t.';
				$r['NEXT_UPDATE'] = 'z.s.m.';
			}

			$return[] = $r;

			$rs->moveNext();
		}
		$rs->Close();
		return $return;

	}

	function _getKredietAanvragenUpdateServiceCount($org_id, $filters=array()) {
		if (!is_numeric($org_id)) return false;

		$where = '';
		if (count($filters) > 0) {
			$where = ' AND ' . implode(' AND ', $filters);
		}

		$sql = "SELECT COUNT(ckre_id) AS aantal
				FROM ccrd_krediet k1
				WHERE
					k1.ckre_org_id = ".$org_id."
				AND k1.ckre_us_base_id = k1.ckre_id
				AND k1.ckre_csta_id = 10
				". $where."";

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

		return $rs->Row('AANTAL');
	
	}


	function deleteUpdateService($ckre_id) {
		if (!is_numeric($ckre_id) && is_numeric($_SESSION['session_org_id'])) return false;

		$sql = "UPDATE ccrd_krediet SET 
					CKRE_CSTA_ID = 11
				,	CKRE_CLOSE_DATE = SYSDATE 
				WHERE	ckre_id = ".$ckre_id ."
				AND		ckre_org_id = ".$_SESSION['session_org_id'];
		$this->_db->execute($sql);
	}


	/**********************************************************************************/
	/*	FAILLISSEMENTEN - INTERNATIONAAL	*/
	/**********************************************************************************/

	function _getFaillissementenInternationaal($filters, $extended_info = false) {
	
		$where = '';
		if (count($filters) > 0) {
			$where = ' AND ' . implode(' AND ', $filters);
		}

		$sql = "SELECT 
					ccrd_fail_int.*
				,	ccou_name 
				FROM ccrd_fail_int 
				INNER JOIN ccrd_countries ON ccou_code = cfin_ccou_code 
				WHERE 1=1
				".$where."
				ORDER BY cfin_week desc, cfin_year desc, cfin_naam_bedrijf";
		
		$rs = $this->_db->Query($sql);


		if ($extended_info) {
			$return = array();
		
			while ($r = $rs->Row()) {
				$rs_ext = $this->_db->Query("SELECT cfip_info FROM ccrd_fail_int_plus WHERE cfip_cfin_id = ".$r['CFIN_ID']." ORDER BY cfip_id");
				$r['extended'] = rs2arr($rs_ext);

				$return[] = $r;
				$rs->moveNext();
			}
			$rs->Close();

			return $return;

		} else {
			return rs2arr($rs);
		}
	}

	/**********************************************************************************/
	/*	FAILLISSEMENTEN - OUD	*/
	/**********************************************************************************/

	function _getFaillissementenOud($filters, $extended_info = false) {
	
		$where = '';
		if (count($filters) > 0) {
			$where = ' AND ' . implode(' AND ', $filters);
		}

		$sql = "SELECT *
				FROM ccrd_faillisement 
				WHERE 1=1
				".$where."
				ORDER BY cfai_week desc, cfai_year desc, cfai_naam_bedrijf";
		
		$rs = $this->_db->Query($sql);


		if ($extended_info) {
			$return = array();
		
			while ($r = $rs->Row()) {
				$rs_ext = $this->_db->Query("SELECT cfaa_info FROM ccrd_faillisement_plus WHERE cfaa_cfai_id = ".$r['CFAI_ID']." ORDER BY cfaa_id");
				$r['extended'] = rs2arr($rs_ext);

				$return[] = $r;
				$rs->moveNext();
			}
			$rs->Close();

			return $return;

		} else {
			return rs2arr($rs);
		}
	}


	/*************************************************************
	*  print functions
	**************************************************************/

	function print_kr_orderbox() {

		//--- assign countries
		$this->_smrt->assign('countries', $this->_getCountryList());

		//--- assign products
		$this->_smrt->assign('products', $this->_getProductList($this->_variables->public->org_id));

		// Assign globals
		$this->assignTemplateGlobals();

		// Display
		$this->_smrt->display($this->_settings->templates->orderbox);

	}

	function csv_verbruiksoverzicht($search_data) {

		$filters = array();
		if (isset($search_data['search_name']) && strlen(trim($search_data['search_name']))) {
			$filters[] = "LOWER(ckre_name) LIKE '%".$this->_db->db_escape_string(strtolower(trim($search_data['search_name'])))."%'";
		}
		if (isset($search_data['search_city']) && strlen(trim($search_data['search_city']))) {
			$filters[] = "LOWER(ckre_address_city) LIKE '%".$this->_db->db_escape_string(strtolower(trim($search_data['search_city'])))."%'";
		}
		if (isset($search_data['search_date_from']) && preg_match('/([0-9]{2})-([0-9]{2})-([0-9]{4})/', $search_data['search_date_from'])) {
			$filters[] = "ckre_request_date >= TO_DATE('".$search_data['search_date_from']."', 'DD-MM-YYYY')";
		}
		if (isset($search_data['search_date_till']) && preg_match('/([0-9]{2})-([0-9]{2})-([0-9]{4})/', $search_data['search_date_till'])) {
			$filters[] = "ckre_request_date <= TO_DATE('".$search_data['search_date_till']."', 'DD-MM-YYYY')";
		}

		$ka = $this->_getKredietAanvragen($_SESSION['session_org_id'], $filters); 

		//--- assign kredietaanvragen
		$this->_smrt->assign('ka', $ka);

		// Assign globals
		$this->assignTemplateGlobals();

		// Display
		$csv_data = $this->_smrt->fetch($this->_settings->templates->verbruiksoverzicht_csv);
	
		Header("Content-type: text/csv");
		Header("Pragma: public");
		Header("Expires:0");
		Header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
		Header("Content-Disposition: attachment; filename=verbruiksoverzicht_".date("Ymd").".csv");
		Header("Connection: close");

//echo '<pre>';
		echo $csv_data;

		exit();
	}

	function print_verbruiksoverzicht($search_data) {
	

		$filters = array();
		if (isset($search_data['search_name']) && strlen(trim($search_data['search_name']))) {
			$filters[] = "LOWER(ckre_name) LIKE '%".$this->_db->db_escape_string(strtolower(trim($search_data['search_name'])))."%'";
		}
		if (isset($search_data['search_city']) && strlen(trim($search_data['search_city']))) {
			$filters[] = "LOWER(ckre_address_city) LIKE '%".$this->_db->db_escape_string(strtolower(trim($search_data['search_city'])))."%'";
		}
		if (isset($search_data['search_date_from']) && preg_match('/([0-9]{2})-([0-9]{2})-([0-9]{4})/', $search_data['search_date_from'])) {
			$filters[] = "ckre_request_date >= TO_DATE('".$search_data['search_date_from']."', 'DD-MM-YYYY')";
		}
		if (isset($search_data['search_date_till']) && preg_match('/([0-9]{2})-([0-9]{2})-([0-9]{4})/', $search_data['search_date_till'])) {
			$filters[] = "ckre_request_date <= TO_DATE('".$search_data['search_date_till']."', 'DD-MM-YYYY')";
		}

		$browse_current_page = isset($search_data['browse_current_page']) && is_numeric($search_data['browse_current_page']) && $search_data['browse_current_page'] > 0 ? $search_data['browse_current_page'] : 1;

		$total_aanvragen = $this->_getKredietAanvragenCount($_SESSION['session_org_id'], $filters);
		$browse_info = array (	'total_items'	=> $total_aanvragen
							,	'total_pages'	=> ceil($total_aanvragen/CCRD01_KA_LIST_PER_PAGE)
							,	'current_page'	=> $browse_current_page);

		
		$this->_smrt->assign('browse_info', $browse_info);

		$ka = $this->_getKredietAanvragen($_SESSION['session_org_id'], $filters, ($browse_current_page-1)*CCRD01_KA_LIST_PER_PAGE, CCRD01_KA_LIST_PER_PAGE); 

		//--- assign kredietaanvragen
		$this->_smrt->assign('ka', $ka);

		// Assign globals
		$this->assignTemplateGlobals();

		// Display
		$this->_smrt->display($this->_settings->templates->verbruiksoverzicht);

	}

	function printKredietAanvraag($ckre_id) {
		if (!is_numeric($ckre_id)) return false;

		//--- assign kredietaanvraag
		$this->_smrt->assign('ka', $this->getKredietAanvraag($ckre_id));


		// Assign globals
		$this->assignTemplateGlobals();

		// Display
		$this->_smrt->display($this->_settings->templates->kredietaanvraag);
	
	}

	function printUpdateService($search_data) {

		$filters = array();

		if (isset($search_data['search_name']) && strlen(trim($search_data['search_name']))) {
			$filters[] = "LOWER(ckre_name) LIKE '%".$this->_db->db_escape_string(strtolower(trim($search_data['search_name'])))."%'";
		}
		if (isset($search_data['search_city']) && strlen(trim($search_data['search_city']))) {
			$filters[] = "LOWER(ckre_address_city) LIKE '%".$this->_db->db_escape_string(strtolower(trim($search_data['search_city'])))."%'";
		}
		if (isset($search_data['search_date_from']) && preg_match('/([0-9]{2})-([0-9]{2})-([0-9]{4})/', $search_data['search_date_from'])) {
			$filters[] = "ckre_request_date >= TO_DATE('".$search_data['search_date_from']."', 'DD-MM-YYYY')";
		}
		if (isset($search_data['search_date_till']) && preg_match('/([0-9]{2})-([0-9]{2})-([0-9]{4})/', $search_data['search_date_till'])) {
			$filters[] = "ckre_request_date <= TO_DATE('".$search_data['search_date_till']."', 'DD-MM-YYYY')";
		}
		if (isset($search_data['search_ref']) && strlen(trim($search_data['search_ref']))) {
			$filters[] = "LOWER(ckre_reference_name) LIKE '%".$this->_db->db_escape_string(strtolower(trim($search_data['search_ref'])))."%'";
		}

		$browse_current_page = isset($search_data['browse_current_page']) && is_numeric($search_data['browse_current_page']) && $search_data['browse_current_page'] > 0 ? $search_data['browse_current_page'] : 1;

		$total_aanvragen = $this->_getKredietAanvragenUpdateServiceCount($_SESSION['session_org_id'], $filters);
		$browse_info = array (	'total_items'	=> $total_aanvragen
							,	'total_pages'	=> ceil($total_aanvragen/CCRD01_KA_LIST_PER_PAGE)
							,	'current_page'	=> $browse_current_page);

		$this->_smrt->assign('browse_info', $browse_info);

		$ka = $this->_getKredietAanvragenUpdateService($_SESSION['session_org_id'], $filters, ($browse_current_page-1)*CCRD01_KA_LIST_PER_PAGE);	

		//--- assign kredietaanvragen
		$this->_smrt->assign('ka', $ka);

		// Assign globals
		$this->assignTemplateGlobals();

		// Display
		$this->_smrt->display($this->_settings->templates->updateservice);

	}


	/**********************************************************************************/
	/*	FAILLISSEMENTEN - INTERNATIONAAL	*/
	/**********************************************************************************/

	function printFaillissementIndexInternationaal($search_data) {

		$filters = array();

		if (isset($search_data['search_jaar']) && is_numeric($search_data['search_jaar'])) {
			$filters[] = "cfin_year = '".$this->_db->db_escape_string(strtolower(trim($search_data['search_jaar'])))."'";
		}
		if (isset($search_data['search_week']) && is_numeric($search_data['search_week'])) {
			$filters[] = "cfin_week = '".$this->_db->db_escape_string(strtolower(trim($search_data['search_week'])))."'";
		}

		if (isset($search_data['search_trefwoord']) && strlen(trim($search_data['search_trefwoord']))) {
			$filters[] = "LOWER(cfin_trefwoord) LIKE '%".$this->_db->db_escape_string(strtolower(trim($search_data['search_trefwoord'])))."%'";
		}
		if (isset($search_data['search_naam']) && strlen(trim($search_data['search_naam']))) {
			$filters[] = "LOWER(cfin_naam_bedrijf) LIKE '%".$this->_db->db_escape_string(strtolower(trim($search_data['search_naam'])))."%'";
		}
		if (isset($search_data['search_plaats']) && strlen(trim($search_data['search_plaats']))) {
			$filters[] = "LOWER(cfin_plaats_bedrijf) LIKE '%".$this->_db->db_escape_string(strtolower(trim($search_data['search_plaats'])))."%'";
		}

		if ($filters) {
			$fail_list = $this->_getFaillissementenInternationaal($filters);

			//--- assign faillissementen
			$this->_smrt->assign('fail_list', $fail_list);
		}

		// Assign globals
		$this->assignTemplateGlobals();

		// Display
		$this->_smrt->display($this->_settings->templates->faillissement_int);
	}

	function printFaillissementInternationaal ($fail_ids) {
		if (is_numeric($fail_ids)) $fail_ids[] = $fail_ids;

		if (count($fail_ids) > 0) {

			$filters = array('cfin_id IN ('.implode(',', $fail_ids).')');

			$fail_list = $this->_getFaillissementenInternationaal($filters, true);

			//--- assign faillissementen
			$this->_smrt->assign('fail_list', $fail_list);
		}

		$this->_smrt->assign('view_mode', 'detail');


		// Assign globals
		$this->assignTemplateGlobals();

		// Display
		$this->_smrt->display($this->_settings->templates->faillissement_int);

	}

	/**********************************************************************************/
	/*	FAILLISSEMENTEN - OUD	*/
	/**********************************************************************************/


	function printFaillissementIndexOud($search_data) {

		$filters = array();

		if (isset($search_data['search_jaar']) && is_numeric($search_data['search_jaar'])) {
			$filters[] = "cfai_year = '".$this->_db->db_escape_string(strtolower(trim($search_data['search_jaar'])))."'";
		}
		if (isset($search_data['search_week']) && is_numeric($search_data['search_week'])) {
			$filters[] = "cfai_week = '".$this->_db->db_escape_string(strtolower(trim($search_data['search_week'])))."'";
		}
		if (isset($search_data['search_trefwoord']) && strlen(trim($search_data['search_trefwoord']))) {
			$filters[] = "LOWER(cfai_trefwoord) LIKE '%".$this->_db->db_escape_string(strtolower(trim($search_data['search_trefwoord'])))."%'";
		}
		if (isset($search_data['search_naam']) && strlen(trim($search_data['search_naam']))) {
			$filters[] = "LOWER(cfai_naam_bedrijf) LIKE '%".$this->_db->db_escape_string(strtolower(trim($search_data['search_naam'])))."%'";
		}
		if (isset($search_data['search_plaats']) && strlen(trim($search_data['search_plaats']))) {
			$filters[] = "LOWER(cfai_plaats_bedrijf) LIKE '%".$this->_db->db_escape_string(strtolower(trim($search_data['search_plaats'])))."%'";
		}

		if ($filters) {
			$fail_list = $this->_getFaillissementenOud($filters);

			//--- assign faillissementen
			$this->_smrt->assign('fail_list', $fail_list);
		}

		// Assign globals
		$this->assignTemplateGlobals();

		// Display
		$this->_smrt->display($this->_settings->templates->faillissement_old);
	}

	function printFaillissementOud ($fail_ids) {
		if (is_numeric($fail_ids)) $fail_ids[] = $fail_ids;

		if (count($fail_ids) > 0) {

			$filters = array('cfai_id IN ('.implode(',', $fail_ids).')');

			$fail_list = $this->_getFaillissementenOud($filters, true);

			//--- assign faillissementen
			$this->_smrt->assign('fail_list', $fail_list);
		}

		$this->_smrt->assign('view_mode', 'detail');

		// Assign globals
		$this->assignTemplateGlobals();

		// Display
		$this->_smrt->display($this->_settings->templates->faillissement_old);

	}

	function getOrgAboInfo($org_id) {
		$org_info = $this->_getOrganization($org_id, array('GATT_CSA_STATUS','GATT_CSA_START','GATT_CSA_END','GATT_CSA_AMOUNT','GATT_CSA_AMT_REQ'));	

		if ($org_info['GATT_CSA_STATUS'] == 'Y') {
			
			$today = mktime(0,0,0,date('m'), date('d'), date('Y'));

			$org_info['ABO_STATE'] = CCRD01_ABO_OK;

			if ($org_info['GATT_CSA_START']) {
				list($d,$m,$y) = explode('-', $org_info['GATT_CSA_START']);
				$org_info['GATT_CSA_START_TS'] = mktime(0,0,0,$m,$d,$y);

				if ($org_info['GATT_CSA_START_TS'] > $today) {
					$org_info['ABO_STATE'] = CCRD01_ABO_NOT_STARTED;
				}
			}

			if ($org_info['GATT_CSA_END']) {
				list($d,$m,$y) = explode('-', $org_info['GATT_CSA_END']);
				$org_info['GATT_CSA_END_TS'] = mktime(0,0,0,$m,$d,$y);

				if ($org_info['GATT_CSA_END_TS'] < $today) {
					$org_info['ABO_STATE'] = CCRD01_ABO_EXPIRED;
				}
			}

			if ($org_info['GATT_CSA_AMT_REQ'] >= $org_info['GATT_CSA_AMOUNT']) {
				$org_info['ABO_STATE'] = CCRD01_ABO_NO_SALDO;
			}

		} else {
			$org_info['ABO_STATE'] = CCRD01_ABO_ABSENT;
		}

		return $org_info;
	}
}

?>