<?

//error_reporting(E_ALL);

require_once('class.stdModule.php');

require_once('tools.php');
require_once('functions.php');

define('CRD_RESULTS_PER_PAGE', 25);
define('CRD_RESULTS_PER_PAGE_POPUP', 15);

define('CRD_ERR_BEDRIJF_EXIST_IN_STRUCTURE', 101);

define('CRD_MAX_ADDRESS_RESULTS', 5);

define('CRD_BRON_TYPE_BEDRIJF', 1);
define('CRD_BRON_TYPE_BALANS', 2);
define('CRD_BRON_TYPE_KERNGEGEVEN', 3);
define('CRD_BRON_TYPE_AANDEELHOUDER', 4);

define('CRD_RELATIE_TYPE_SUPER', 1);
define('CRD_RELATIE_TYPE_PARENT', 2);
define('CRD_RELATIE_TYPE_CHILD', 3);
define('CRD_RELATIE_TYPE_SISTER', 4);


class ccrd0200 extends stdModule {

	function ccrd0200() {

		$this->stdModule('ccrd0200');

		$this->form_mode = (strpos($_SERVER['REQUEST_URI'], '/popup/') !== false ? 'popup' : 'screen');

		$this->settings->search->results_per_page = CRD_RESULTS_PER_PAGE;

		$this->_settings->templates->fo_search_bedrijf		= "fo_bed_search.tpl";
		$this->_settings->templates->fo_search_calamiteit	= "fo_cmt_search.tpl";

	}

	function show_form($form_code, $id=null) {
		if (!($file_path = $this->form_exists($form_code))) {
			$this->triggerError('0', 'Form ['.$form_code.'] does not exist');
			return;
		}

		require_once($this->_settings->paths->absolute.'class.ccrd0200_form.php');

		//--- include form settings (/inc/ccrd0200/forms/{form}.php)
		include($file_path);

		if (isset($_form)) {
			$this->_smrt->assign('form_data', $_form);
		}

		$this->_smrt->assign('form_code', $form_code);
		$this->_smrt->assign('id', $id);

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

		$this->settings->search->results_per_page = ($this->form_mode == 'popup') ? CRD_RESULTS_PER_PAGE_POPUP : CRD_RESULTS_PER_PAGE;



		$this->assignData($form_code, $id);


		if (!($form_tpl = $this->form_tpl_exists($form_code))) {
			$form_tpl = 'form.tpl';
		}

		// Process the template
		$this->_smrt->display($form_tpl);

		// Clear template
		$this->_smrt->clear_all_assign();


	}

	function assignData($form_code, $id) {

		if ($_SESSION['crd_error_message']) {
			$this->assignError($_SESSION['crd_error_message']);
			$_SESSION['crd_error_message'] = '';
		}

		switch ($form_code) {

			case 'bedrijf.handelsnaam':

				$data = $this->getHandelsnaam($id);
				$this->_smrt->assign('data', $data);
				break;

			case 'bedrijf.adres':

				$data = $this->getAdressen($id);
				$data_sub = $this->getAdressenSubs($id);

				$this->_smrt->assign('data', $data);
				$this->_smrt->assign('data_subs', $data_sub);
				break;

			case 'bedrijf.biknacecode':

				$data = $this->getBIKNACE($id);
				$this->_smrt->assign('data', $data);
				break;

			case 'bedrijf.bedrijfsleiding':

				if (is_numeric($id)) {
					$data = $this->getBedrijfContactpersoon($id);
					$this->_smrt->assign('data', $data);
				} else {
					$this->assignError('Geen geldig bedrijf geselecteerd');
				}

				break;

			case 'bedrijf.contactpersoon':

				list($bed_id, $ctc_id) = explode('|',$id);
				if (!($data = $this->getBedrijfContactpersoon($bed_id, $ctc_id))) {
					$this->setBedrijfContactPersoon($bed_id, $ctc_id, true);
					$data = $this->getBedrijfContactpersoon($bed_id, $ctc_id);
				}

				$this->_smrt->assign('data', $data);
				break;

			case 'bedrijf.contactpersoon.bedrijf':

				if (!is_numeric($id)) {
					list($bed_id, $bed_id_connect) = explode('|',$id);
				} else {
					$bed_id = $id;
				}

				if (is_numeric($bed_id_connect)) {
					$data = $this->getBedrijfContactpersoon($bed_id, null, $bed_id_connect);
					$this->_smrt->assign('data', $data);
				}

				break;

			case 'bedrijf.relaties':

				$data = $this->getConcernGegeven($id);
				$this->_smrt->assign('data', $data);
				break;

			case 'bedrijf.relaties.view':

				$data = $this->getConcernGegevenStructure($id);
				$this->_smrt->assign('data', $data);

				break;

			case 'bedrijf.concerngegeven':

				list($bed_id, $con_id, $action_type) = explode('|',$id);

				if ($action_type == 'new') {

					$data = $this->getBedrijf($con_id);

				} else {

					$data = $this->getConcernGegeven($bed_id, $con_id);

				}


				$this->_smrt->assign('bedrijf_relation', $data);

//				$this->_smrt->assign('bedrijf_relation', $bedrijf_relation);
//				if ($bed_id == $bed_id_relation) {
//					$this->_smrt->assign('relation_is_contra', 1);
//				}
				break;

			case 'bedrijf.kapitaal':

				$data = $this->getKapitaal($id);
				$this->_smrt->assign('data', $data);
				break;

			case 'bedrijf.kredietlimiet':

				$data = $this->getKredietlimiet($id);
				$this->_smrt->assign('data', $data);
				break;

			case 'bedrijf.personeel':

				$data = $this->getPersoneel($id);
				while (count($data) > 3) {
					array_shift($data);
				}
				$this->_smrt->assign('data', array_transpose($data));

				break;

			case 'bedrijf.onroerendgoedinformatie':

				$data = $this->getOnroerendgoedinformatie($id);
				$this->_smrt->assign('data', $data);

				break;

			case 'bedrijf.import_export':

				$data = $this->getImportExport($id);
				$this->_smrt->assign('data', $data);

				break;

			case 'bedrijf.bank':

				$data = $this->getBedrijfBank($id);
				$this->_smrt->assign('data', $data);

				break;

			case 'bedrijf.merk':

				$data = $this->getMerk($id);
				$this->_smrt->assign('data', $data);
				break;

			case 'bedrijf.select':
				if (isset($_REQUEST['argv'])) {
					$rows = explode('|',$_REQUEST['argv']);
					$argv = array();
					foreach ($rows as $r) {
						list($k, $v) = explode('=', $r);
						$argv[$k] = $v;
					}
					$this->_smrt->assign('argv', $argv);
				}

				if (!is_numeric($id)) {
					list($bed_id, $bed_id_relation) = explode('|',$id);
				} else {
					$bed_id = $id;
				}

				if (is_numeric($bed_id_relation)) {
					$this->_smrt->assign('data_selected', $this->getBedrijf($bed_id_relation));
				}


				break;

			case 'bedrijf.accountant':

				if (!is_numeric($id)) {
					list($bed_id, $acc_id) = explode('|',$id);
				} else {
					$bed_id = $id;
				}
				$bedrijf = $this->getBedrijf($bed_id);


				$data = $this->getAccountant($bedrijf['bed_acc_id']);
				$this->_smrt->assign('data', $data);
				break;

			case 'bedrijf.omzetwinst':

				$data = $this->getOmzetWinst($id);
				$this->_smrt->assign('data', $data);

				break;

			case 'bedrijf.bedrijfscore':

				$data = $this->getBedrijfScore($id);
				$this->_smrt->assign('data', $data);

				break;


			case 'bedrijf.aandeelhouder':

				list($bed_id, $and_id) = explode('|',$id);
				$data = $this->getAandeelhouder($bed_id, $and_id);
				$this->_smrt->assign('data', $data);

				break;

			case 'bedrijf.aandeelhouder.bedrijf':

				if (!is_numeric($id)) {
					list($bed_id, $bed_id_connect) = explode('|',$id);
				} else {
					$bed_id = $id;
				}
				if (is_numeric($bed_id_connect)) {
					$data = $this->getAandeelhouder($bed_id, null, $bed_id_connect);
					$this->_smrt->assign('data', $data);
				}

				break;


			case 'bedrijf.bedrijf_aandeelhouder':

				$data = $this->getAandeelhouder($id);
				$this->_smrt->assign('data', $data);


				break;

			case 'bedrijf.betalingservaring':

				$data = $this->getBetalingservaring($id);
				$this->_smrt->assign('data', $data);

				break;


			case 'bedrijf.betalingservaring_kwartaal':

				$data = $this->getBetalingservaringKwartaal($id);
				$this->_smrt->assign('data', $data);

				break;

			case 'bedrijf.balans':

				$data = $this->getBalans($id);
				$this->_smrt->assign('data', $data);

				break;

			case 'bedrijf.balans.edit':

				list($bed_id, $bal_id) = explode('|',$id);

				if (is_numeric($bal_id) && $bal_id > 0) {
					$data = $this->getBalans(null, $bal_id);
					$this->_smrt->assign('data', $data);
				}

				break;


			case 'bedrijf.winstverlies':

				$data = $this->getWinstVerlies($id);
				$this->_smrt->assign('data', $data);

				break;

			case 'bedrijf.winstverlies.edit':

				list($bed_id, $win_id) = explode('|',$id);
				if (is_numeric($win_id) && $win_id > 0) {

					$data = $this->getWinstVerlies(null, $win_id);
					$this->_smrt->assign('data', $data);
				}

				break;


			case 'bedrijf.beroepsuitoefening':

				$data = $this->getBeroepsuitoefening($id);
				$this->_smrt->assign('data', $data);

				break;

			case 'bedrijf.verklaring_instemming':

				$data = $this->getVerklaringInstemming($id);
				$this->_smrt->assign('data', $data);

				break;

			case 'bedrijf.verklaring_aansprakelijkheid':

				$data = $this->getVerklaringAansprakelijkheid($id);
				$this->_smrt->assign('data', $data);


				break;

			case 'bedrijf.creditscore':

				$null = null;
				$cs = &$this->getCreditScoring($id, $null, true);

				ob_start();
				$cs->write_data();
				$debug_data = ob_get_contents();
				ob_end_clean();
				$this->_smrt->assign('debug_data', $debug_data);


				break;

			case 'calamiteit.new':

				if (is_numeric($id)) {
					$cmt_id = $id;
				} else {
					list($cmt_id, $rtb_id, $cty_id) = explode('|',$id);
				}
				$this->_smrt->assign('cmt_id', $cmt_id);

				if (is_numeric($rtb_id)) {
					$_SESSION['crd_current_rtb_id'] = $rtb_id;
				}
				if (is_numeric($cty_id)) {
					$_SESSION['crd_current_cty_id'] = $cty_id;
				}

				break;

			case 'calamiteit.select':

				list(,, $cmt_id) = explode('|', $id);

				if (is_numeric($cmt_id)) {
					$data = $this->getCalamiteit($cmt_id);

					$data['bedrijf'] = $this->getBedrijf($data['cmt_bed_id']);
					$this->_smrt->assign('data_selected', $data);
				}

				$this->_smrt->assign('rtb_list', $this->get_options('cmt_rtb_id'));


				break;


			case 'calamiteit.list':

				$data = $this->getCalamiteit(null, $id);
				$this->_smrt->assign('data', $data);

				break;


			case 'calamiteit.calamiteit_curator':

				$data = $this->getCalamiteitCurator($id);
				$this->_smrt->assign('data', $data);

				break;

			case 'calamiteit.calamiteit_curator.edit':

				list($cmt_id, $cur_id) = explode('|', $id);

				if (is_numeric($cmt_id) && is_numeric($cur_id)) {
					$data['curator'] = $this->getCurator($cur_id, $cmt_id);

				}
				$this->_smrt->assign('data', $data);

				break;

			case 'calamiteit.curator':

				$cmt_id = null;
				if (!is_numeric($id)) {
					list($cur_id, $cmt_id) = explode('|', $id);

				} else {
					$cur_id = $id;
				}

				$data = $this->getCurator($cur_id, $cmt_id);
				$this->_smrt->assign('data', $data);

				break;

			case 'calamiteit.calamiteitmutatie':
			case 'calamiteit.calamiteitmutatie.new':

				if (is_numeric($id)) {
					$cmt_id = $id;
				} else {
					list($cmt_id, $cty_id) = explode('|', $id);
					if (is_numeric($cty_id)) {
						$_SESSION['crd_current_cty_id'] = $cty_id;
					}
				}

				if ($cmt_id) {
					$data = $this->getCalamiteitmutatie($cmt_id);
					$this->_smrt->assign('data', $data);
				}

				break;


			case 'calamiteit.calamiteitbericht':

				$data = $this->getCalamiteitbericht($id);
				$this->_smrt->assign('data', $data);

				break;

			case 'beheer.score':

				$data = $this->getScoreStaffels();
				$this->_smrt->assign('data', $data);

				break;

			case 'beheer.score.staffeltabel':

				$stf_data = $this->getScoreStaffels($id);
				$this->_smrt->assign('stf_data', $stf_data);

				$stf_values = $this->getScoreStaffelValues($id);
				$this->_smrt->assign('stf_values', $stf_values);

				break;

			case 'beheer.bedrijfsscore':

				$data = $this->getBedrijfsScoreOptions();
				$this->_smrt->assign('data', $data);

				break;

			case 'beheer.general_options':

				$data = $this->getGeneralOptions();
				$this->_smrt->assign('data', $data);

				break;


		}

	}

	function assignError($msg) {
		$this->_smrt->assign('error', $msg);
	}

	function handleFormSave($form_code, $id, $data) {
		switch ($form_code) {

			case 'bedrijf.new':
			case 'bedrijf.edit':

				if (is_numeric($id)) {
					$bed_id = $id;
				} elseif (is_numeric($data['bed_id'])) {
					$bed_id = $data['bed_id'];
				} else {
					return;
				}

				$kvk_bed_id = $this->getBedrijfByKVK($data['bed_handelsregisternummer'], $data['bed_subdossiernummer']);

				if ($kvk_bed_id && $kvk_bed_id != $bed_id) {
					//--- kvk exists
					$data['bed_handelsregisternummer'] = '';
					$data['bed_subdossiernummer'] = '';
					$_SESSION['crd_error_message'] = 'Ingevoerd KVK nummer bestaat al';
				}

				$this->setBedrijf($bed_id, $data);

				if ($this->form_mode == 'popup') {
					$this->_smrt->assign('close_popup', true);
				}

				break;

			case 'bedrijf.handelsnaam':

				if (isset($data['hnm_beschrijving']['new']) && strlen(trim($data['hnm_beschrijving']['new'])) > 0) {
					$check = $this->getHandelsnaam($id, $data['hnm_beschrijving']['new']);
					if (!is_array($check) || count($check) == 0) {
						$this->setHandelsnaam(null, $id, $data['hnm_beschrijving']['new']);
					} else {
						$this->assignError('Fout: Handelsnaam bestaat al');
					}

				}
				if (isset($data['hnm_beschrijving']) && is_array($data['hnm_beschrijving'])) {

					foreach ($data['hnm_beschrijving'] as $hnm_id => $hnm_beschrijving) {
						if (is_numeric($hnm_id)) {
							$this->setHandelsnaam($hnm_id, $id, $hnm_beschrijving);
						}
					}
				}
				if (isset($_REQUEST['id_delete']) && is_numeric($_REQUEST['id_delete'])) {
					$this->setHandelsnaam($_REQUEST['id_delete'], '', '', true);
				}

				break;

			case 'bedrijf.adres':

				$adres_data['adr_bed_id'] = $id;
				$adres_data['adr_adres'] = $data['adr_adres'];
				$adres_data['adr_huisnummer'] = $data['adr_huisnummer'];
				$adres_data['adr_adrestoevoeging'] = $data['adr_adrestoevoeging'];
				$adres_data['adr_postcode'] = $data['adr_postcode'];
				$adres_data['adr_plaats'] = $data['adr_plaats'];
				$adres_data['adr_provincie'] = $data['adr_provincie'];
				$adres_data['adr_type'] = $data['adr_type'];
				$adres_data['adr_lnd_id'] = $data['adr_lnd_id'];
				$adres_data['adr_actief_tot'] = $data['adr_actief_tot'];

				if (isset($data['id_edit']) && is_numeric($data['id_edit'])) {
					//-- edit adres
					$this->setAdres($data['id_edit'], $adres_data);
				} elseif (isset($data['id_delete']) && is_numeric($data['id_delete'])) {
					$this->setAdres($data['id_delete'], $adres_data, true);

				} else {
					$this->setAdres(null, $adres_data);
				}

				if (isset($_REQUEST['but_pressed']) && $_REQUEST['but_pressed'] == 'go_next') {
					$this->_smrt->assign('close_popup', true);
				}

				break;

			case 'bedrijf.biknacecode':
				if (isset($_REQUEST['id_add']) && is_numeric($data['id_add'])) {
					$this->setBIKNACE($id, $_REQUEST['search_result_type'], $_REQUEST['id_add']);
				}

				if (isset($_REQUEST['id_delete'])) {
					list($type, $id_delete) = explode('|', $_REQUEST['id_delete']);
					if (is_numeric($id_delete)) {
						$this->setBIKNACE($id, $type, $id_delete, false);
					}
				}

				if (isset($_REQUEST['search_string']) && strlen($_REQUEST['search_string']) > 0) {

					$result = $this->searchBIKNACE($_REQUEST['search_code_type'], $_REQUEST['search_string'], $id);
					$this->_smrt->assign('search_string', $_REQUEST['search_string']);
					$this->_smrt->assign('search_result', $result);
					$this->_smrt->assign('search_result_type', $_REQUEST['search_code_type']);
					$this->_smrt->assign('search_result_page', $_REQUEST['search_result_page']);

				}



			case 'bedrijf.bedrijfsleiding':

				if (isset($_REQUEST['id_delete'])) {
					list($bed_id, $bct_id) = explode('|', $_REQUEST['id_delete']);
					if (is_numeric($bed_id) && is_numeric($bct_id)) {
						$this->setBedrijfContactPersoon($bed_id, $bct_id, false);
					}
				}
				if (isset($_REQUEST['id_add'])) {
					list($bed_id, $ctc_id) = explode('|', $_REQUEST['id_add']);
					if (is_numeric($bed_id) && is_numeric($ctc_id)) {
						$this->setBedrijfContactPersoon($bed_id, $ctc_id, true);
					}
				}

				if (	(isset($_REQUEST['search_ctc_voornaam']) && strlen(trim($_REQUEST['search_ctc_voornaam'])))
					||	(isset($_REQUEST['search_ctc_naam']) && strlen(trim($_REQUEST['search_ctc_naam'])))
					||	(isset($_REQUEST['search_ctc_geboortedatum']) && strlen(trim($_REQUEST['search_ctc_geboortedatum'])))) {
					//--- search! ;)

					$result = $this->getContactPersoon($_REQUEST['search_ctc_voornaam'], $_REQUEST['search_ctc_naam'], $_REQUEST['search_ctc_geboortedatum'], $id);
					$this->_smrt->assign('search_string', $_REQUEST['search_string']);
					$this->_smrt->assign('search_result', $result);
					$this->_smrt->assign('search_result_page', $_REQUEST['search_result_page']);

				}


				break;

			case 'bedrijf.contactpersoon':

				if (!is_numeric($id)) {
					list($id,) = explode('|', $id);
				}

				$this->setContactPersoon($id, $_REQUEST);
				$this->_smrt->assign('close_popup', true);

				break;

			case 'bedrijf.contactpersoon.bedrijf':

				if (!is_numeric($id)) {
					list($bed_id,$bed_id_connect) = explode('|', $id);
				} else {
					$bed_id = $id;
				}

				if (is_numeric($bed_id)) {

					$this->setContactPersoon($bed_id, $_REQUEST);

				}

				$this->_smrt->assign('close_popup', true);

				break;

			case 'bedrijf.relaties':

				if (isset($_REQUEST['id_delete']) && is_numeric($_REQUEST['id_delete'])) {

					$this->deleteConcernGegeven($_REQUEST['id_delete']);

				}


				if (	(isset($_REQUEST['search_nam_beschrijving']) && strlen($_REQUEST['search_nam_beschrijving']) > 0)
					||	(isset($_REQUEST['search_adr_plaats']) && strlen($_REQUEST['search_adr_plaats']) > 0)
					||	(isset($_REQUEST['search_adr_postcode']) && strlen($_REQUEST['search_adr_postcode']) > 0)
					||	(isset($_REQUEST['search_bed_handelsregisternummer']) && strlen($_REQUEST['search_bed_handelsregisternummer']) > 0)
					) {

//					$result = $this->searchBedrijf(true, $_REQUEST['search_nam_beschrijving'], $_REQUEST['search_adr_plaats'], $_REQUEST['search_adr_postcode'], $_REQUEST['search_adr_huisnummer'], $_REQUEST['search_bed_handelsregisternummer'], $id, $_REQUEST['search_page']);

					$result = $this->searchBedrijf2(
												$_REQUEST['search_nam_beschrijving']
											,	$_REQUEST['search_adr_plaats']
											,	$_REQUEST['search_adr_postcode']
											,	$_REQUEST['search_adr_huisnummer']
											,	$_REQUEST['search_bed_handelsregisternummer']
											,	null
											,	$_REQUEST['search_page']);

					$this->_smrt->assign('search_result', $result);
				}

				break;

			case 'bedrijf.concerngegeven':

				list($bed_id, $con_id, $action_type) = explode('|', $id);
				if ($action_type == 'new') {
					$bed_id_relation = $con_id;
					$con_id=null;
				}
				if (is_numeric($bed_id)) {
					if ($this->setConcernGegeven($con_id, $bed_id, $bed_id_relation, $_REQUEST['con_aandelen_percentage'], $_REQUEST['con_rel_id'])) {
						$this->_smrt->assign('close_popup', true);
					}

				}

				break;

			case 'bedrijf.kapitaal':

				if (isset($data['id_delete']) && is_numeric($data['id_delete'])) {

					$this->deleteKapitaal($data['id_delete']);

				} else {

					$kap_id = (isset($data['id_edit']) && is_numeric($data['id_edit'])) ? $data['id_edit'] : null;
					$this->setKapitaal($id, $kap_id, $_REQUEST['kap_bedrag_maatschappelijk'], $_REQUEST['kap_bedrag_geplaatst'], $_REQUEST['kap_bedrag_gestort'], $_REQUEST['kap_bedrag_commanditair'], $_REQUEST['kap_datum_tot']);

				}

				break;

			case 'bedrijf.kredietlimiet':

				if (isset($data['id_delete']) && is_numeric($data['id_delete'])) {

					$this->setKredietlimiet($id, $data['id_delete'], null, null, false);

				} else {

					$this->setKredietlimiet($id, null, $_REQUEST['kli_datum'], $_REQUEST['kli_kredietlimiet']);

				}



				break;

			case 'bedrijf.personeel':

				if (isset($data['id_delete']) && is_numeric($data['id_delete'])) {

					$this->setPersoneel($id, $data['id_delete'], null, false);

				} elseif (isset($data['id_edit']) && is_numeric($data['id_edit'])) {

					$this->setPersoneel($id, $data['id_edit'], $_REQUEST);

				} else {

					$this->setPersoneel($id, null, $_REQUEST);

				}

				break;

			case 'bedrijf.onroerendgoedinformatie':

				if (isset($data['id_delete']) && is_numeric($data['id_delete'])) {

					$this->setOnroerendgoedinformatie($id, $data['id_delete'], null, false);

				} elseif (isset($data['id_edit']) && is_numeric($data['id_edit'])) {

					$this->setOnroerendgoedinformatie($id, $data['id_edit'], $_REQUEST);

				} else {

					$this->setOnroerendgoedinformatie($id, null, $_REQUEST);

				}

				break;


			case 'bedrijf.import_export':

				if (isset($data['id_delete']) && is_numeric($data['id_delete'])) {

					$this->setImportExport($id, $data['id_delete'], null, false);

				} elseif (isset($data['id_edit']) && is_numeric($data['id_edit'])) {

					$this->setImportExport($id, $data['id_edit'], $_REQUEST);

				} else {

					$this->setImportExport($id, null, $_REQUEST);

				}

				break;

			case 'bedrijf.bank':

				if (isset($data['id_delete']) && is_numeric($data['id_delete'])) {

					$this->setBedrijfBank($id, $data['id_delete'], null, false);

				} elseif (isset($data['id_edit']) && is_numeric($data['id_edit'])) {

					$this->setBedrijfBank($id, $data['id_edit'], $_REQUEST);

				} else {

					$this->setBedrijfBank($id, null, $_REQUEST);

				}

				break;

			case 'bedrijf.merk':

				if (isset($data['mrk_beschrijving']['new']) && strlen(trim($data['mrk_beschrijving']['new'])) > 0) {
					$check = $this->getMerk($id, $data['mrk_beschrijving']['new']);
					if (!is_array($check) || count($check) == 0) {
						$this->setMerk(null, $id, $data['mrk_beschrijving']['new']);
					} else {
						$this->assignError('Fout: Merk bestaat al');
					}

				}
				if (isset($data['mrk_beschrijving']) && is_array($data['mrk_beschrijving'])) {

					foreach ($data['mrk_beschrijving'] as $hnm_id => $mrk_beschrijving) {
						if (is_numeric($hnm_id)) {
							$this->setMerk($hnm_id, $id, $mrk_beschrijving);
						}
					}
				}
				if (isset($_REQUEST['id_delete']) && is_numeric($_REQUEST['id_delete'])) {
					$this->setMerk($_REQUEST['id_delete'], '', '', true);
				}

				break;

			case 'bedrijf.select':


				if (	(isset($_REQUEST['search_nam_beschrijving']) && strlen($_REQUEST['search_nam_beschrijving']) > 0)
					||	(isset($_REQUEST['search_adr_plaats']) && strlen($_REQUEST['search_adr_plaats']) > 0)
					||	(isset($_REQUEST['search_adr_postcode']) && strlen($_REQUEST['search_adr_postcode']) > 0)
					||	(isset($_REQUEST['search_bed_handelsregisternummer']) && strlen($_REQUEST['search_bed_handelsregisternummer']) > 0)
					) {

//					$result = $this->searchBedrijf(true, $_REQUEST['search_nam_beschrijving'], $_REQUEST['search_adr_plaats'], $_REQUEST['search_adr_postcode'], $_REQUEST['search_adr_huisnummer'], $_REQUEST['search_bed_handelsregisternummer'], $id);
//					$this->_smrt->assign('search_result', $result);

					$result = $this->searchBedrijf2(
												$_REQUEST['search_nam_beschrijving']
											,	$_REQUEST['search_adr_plaats']
											,	$_REQUEST['search_adr_postcode']
											,	$_REQUEST['search_adr_huisnummer']
											,	$_REQUEST['search_bed_handelsregisternummer']
											,	null
											,	$_REQUEST['search_page']);

					$this->_smrt->assign('search_result', $result);

				}

				break;

			case 'bedrijf.accountant':

				if (isset($data['id_add']) && is_numeric($data['id_add'])) {
					$this->setAccountant($id, $data['id_add']);
				} elseif (isset($data['id_delete']) && is_numeric($data['id_delete'])) {
					$this->setAccountant($id, $data['id_delete'], null, null, false);
				} elseif ((isset($_REQUEST['acc_naam']) && strlen($_REQUEST['acc_naam']) > 0)) {
					$this->setAccountant($id, null, $_REQUEST['acc_naam'], $_REQUEST['acc_plaats']);
				}


				if ((isset($_REQUEST['search_acc_naam']) && strlen($_REQUEST['search_acc_naam']) > 0) || (isset($_REQUEST['search_acc_plaats']) && strlen($_REQUEST['search_acc_plaats']) > 0)) {

					$result = $this->getAccountant(null, $_REQUEST['search_acc_naam'], $_REQUEST['search_acc_plaats']);
					$this->_smrt->assign('search_result', $result);
				}


				break;

			case 'bedrijf.omzetwinst':

				if (isset($data['id_delete']) && is_numeric($data['id_delete'])) {
					$this->setOmzetWinst($id, $data['id_delete'], null, false);
				} elseif (isset($data['id_edit']) && is_numeric($data['id_edit'])) {
					$this->setOmzetWinst($id, $data['id_edit'], $_REQUEST);
				} else {
					$this->setOmzetWinst($id, null, $_REQUEST);
				}

				break;

			case 'bedrijf.bedrijfscore':

				if (isset($data['id_delete']) && is_numeric($data['id_delete'])) {
					$this->setBedrijfScore($id, $data['id_delete'], null, false);
				} elseif (isset($data['id_edit']) && is_numeric($data['id_edit'])) {
					$this->setBedrijfScore($id, $data['id_edit'], $_REQUEST);
				} else {
					$this->setBedrijfScore($id, null, $_REQUEST);
				}

				break;

			case 'bedrijf.aandeelhouder':

				if (!is_numeric($id)) {
					list($bed_id,$and_id) = explode('|', $id);
				} else {
					$bed_id = $id;
				}

				$this->setAandeelhouder($bed_id, $and_id, null, $_REQUEST);
				$this->_smrt->assign('close_popup', true);

				break;

			case 'bedrijf.aandeelhouder.bedrijf':

				if (!is_numeric($id)) {
					list($bed_id,$bed_id_connect) = explode('|', $id);
				} else {
					$bed_id = $id;
				}

				$this->setAandeelhouder($bed_id, null, $bed_id_connect, $_REQUEST);
				$this->_smrt->assign('close_popup', true);

				break;


			case 'bedrijf.bedrijf_aandeelhouder':

				if (isset($data['id_delete']) && is_numeric($data['id_delete'])) {
					$this->deleteAandeelhouder($data['id_delete']);
				}

				if (	(isset($_REQUEST['search_and_naam']) && strlen($_REQUEST['search_and_naam']) > 0)
					||	(isset($_REQUEST['search_and_adres']) && strlen($_REQUEST['search_and_adres']) > 0)
					||	(isset($_REQUEST['search_and_postcode']) && strlen($_REQUEST['search_and_postcode']) > 0)
					||	(isset($_REQUEST['search_and_plaats']) && strlen($_REQUEST['search_and_plaats']) > 0)

					) {

					$result = $this->getAandeelhouder($id, null, null, $_REQUEST['search_and_naam'], $_REQUEST['search_and_adres'], $_REQUEST['search_and_postcode'],$_REQUEST['search_and_plaats']);
					$this->_smrt->assign('search_result', $result);
				}


				break;

			case 'bedrijf.betalingservaring':

				if (isset($data['id_delete']) && is_numeric($data['id_delete'])) {
					$this->setBetalingservaring($id, $data['id_delete'], null, false);
				} elseif (isset($data['id_edit']) && is_numeric($data['id_edit'])) {
					$this->setBetalingservaring($id, $data['id_edit'], $_REQUEST);
				} else {
					$this->setBetalingservaring($id, null, $_REQUEST);
				}

				break;

			case 'bedrijf.betalingservaring_kwartaal':

				if (isset($_REQUEST['bed_betalingservaring_max_factuur']) && isset($_REQUEST['bed_betalingservaring_betalingsgedrag'])) {

					$this->setBedrijf($id, array(	'bed_betalingservaring_max_factuur'		=> $_REQUEST['bed_betalingservaring_max_factuur']
												,	'bed_betalingservaring_betalingsgedrag'	=> $_REQUEST['bed_betalingservaring_betalingsgedrag']
											)
									);

				}

				if (isset($data['id_delete']) && is_numeric($data['id_delete'])) {
					$this->setBetalingservaringKwartaal($id, $data['id_delete'], null, false);
				} elseif (isset($data['id_edit']) && is_numeric($data['id_edit'])) {
					$this->setBetalingservaringKwartaal($id, $data['id_edit'], $_REQUEST);
				} else {
					$this->setBetalingservaringKwartaal($id, null, $_REQUEST);
				}

				break;

			case 'bedrijf.balans':


				if (isset($data['id_delete']) && is_numeric($data['id_delete'])) {
					$this->setBalans($id, $data['id_delete'], null, false);
				} elseif (isset($data['id_edit']) && is_numeric($data['id_edit'])) {
					$this->setBalans($id, $data['id_edit'], $_REQUEST);
				} else {
					$this->setBalans($id, null, $_REQUEST);
				}

				break;

			case 'bedrijf.balans.edit':

				list($bed_id, $bal_id) = explode('|',$id);

				if (is_numeric($bal_id) && $bal_id > 0) {

					$this->setBalans($bed_id, $bal_id, $_REQUEST);

				} else {

					$this->setBalans($bed_id, null, $_REQUEST);

				}

				break;


			case 'bedrijf.winstverlies':

				if (isset($data['id_delete']) && is_numeric($data['id_delete'])) {
					$this->setWinstVerlies($id, $data['id_delete'], null, false);
				}

				break;

			case 'bedrijf.winstverlies.edit':

				list($bed_id, $win_id) = explode('|',$id);

				if (is_numeric($win_id) && $win_id > 0) {

					$this->setWinstVerlies($bed_id, $win_id, $_REQUEST);

				} else {

					$this->setWinstVerlies($bed_id, null, $_REQUEST);

				}

				break;




			case 'bedrijf.beroepsuitoefening':

				if (isset($data['id_delete']) && is_numeric($data['id_delete'])) {
					$this->setBeroepsuitoefening($id, $data['id_delete'], null, false);
				} elseif (isset($data['id_edit']) && is_numeric($data['id_edit'])) {
					$this->setBeroepsuitoefening($id, $data['id_edit'], $_REQUEST);
				} else {
					$this->setBeroepsuitoefening($id, null, $_REQUEST);
				}

				break;


			case 'bedrijf.verklaring_instemming':

				if (isset($_REQUEST['id_delete']) && is_numeric($_REQUEST['id_delete'])) {

					$this->setVerklaringInstemming($id, $_REQUEST['id_delete'], null, false);

				}

				break;

			case 'bedrijf.verklaring_instemming.edit':

				list($bed_id, $inv_id) = explode("|", $id);

				if (is_numeric($bed_id) && is_numeric($inv_id) && $inv_id > 0) {

					$this->setVerklaringInstemming($bed_id, $inv_id, $_REQUEST);

				} elseif (is_numeric($bed_id)) {

					$this->setVerklaringInstemming($bed_id, null, $_REQUEST);
					$this->_smrt->assign('close_popup', true);

				}

				break;

			case 'bedrijf.verklaring_aansprakelijkheid':

				if (isset($_REQUEST['id_delete']) && is_numeric($_REQUEST['id_delete'])) {

					$this->setVerklaringAansprakelijkheid($id, $_REQUEST['id_delete'], null, false);

				}

				break;


			case 'bedrijf.verklaring_aansprakelijkheid.edit':

				list($bed_id, $ans_id) = explode("|", $id);

				if (is_numeric($bed_id) && is_numeric($ans_id) && $ans_id > 0) {

					$this->setVerklaringAansprakelijkheid($bed_id, $ans_id, $_REQUEST);

				} elseif (is_numeric($bed_id)) {

					$this->setVerklaringAansprakelijkheid($bed_id, null, $_REQUEST);
					$this->_smrt->assign('close_popup', true);

				}

				break;


			case 'bedrijf.bronnen':

				$this->setBedrijfBron($id, $_REQUEST);

				break;

			case 'calamiteit.select':

				if (((isset($_REQUEST['search_cmt_insolventienummer']) && strlen($_REQUEST['search_cmt_insolventienummer']) > 0))
					||
					(isset($_REQUEST['search_bed_naam']) && strlen($_REQUEST['search_bed_naam']) > 0)
					||
					(isset($_REQUEST['search_kvk_nummer']) && strlen($_REQUEST['search_kvk_nummer']) > 0)) {

					$this->settings->search->results_per_page = 1000;

					if (!$_REQUEST['search_bed_naam'] && !$_REQUEST['search_kvk_nummer']) {

						$result = $this->searchCalamiteiten(null, null, null, null, $_REQUEST['search_cmt_insolventienummer']);

					} else {


						$result = $this->searchBedrijf2(
													$_REQUEST['search_bed_naam']
												,	null
												,	null
												,	null
												,	$_REQUEST['search_kvk_nummer']
												,	array('insolventienummer' => $_REQUEST['search_cmt_insolventienummer'])
												,	$_REQUEST['search_page']);
					}

					$this->_smrt->assign('search_result', $result);
				}
				if (isset($_REQUEST['cmt_bed_id']) && is_numeric($_REQUEST['cmt_bed_id']) && isset($_REQUEST['cmt_rolnummer']) && strlen($_REQUEST['cmt_rolnummer']) > 0) {

					//--- new cmt toevoegen

					$form_data = array();
					$form_data['cmt_bed_id'] = $_REQUEST['cmt_bed_id'];
					$form_data['cmt_rtb_id'] = $_REQUEST['cmt_rtb_id'];

					preg_match('/([A-Z]{1})\.([0-9]{2})\/(.*)/i', $_REQUEST['cmt_rolnummer'], $out);

					$form_data['cmt_datum'] = '01-01-2005'; //.$out[2]; altijd 01-01-2005 ivm export
					$form_data['cmt_insolventienummer'] = $out[3];
					$form_data['cmt_cty_voorloopcode'] = $out[1];

					$cmt_id = $this->setCalamiteit(null, $form_data);
					$this->_smrt->assign('close_popup', true);
					$cmt_bedrijf_info = $this->getBedrijf($_REQUEST['cmt_bed_id']);
					$this->_smrt->assign('close_arg', "[$cmt_id, '".$cmt_bedrijf_info['nam_beschrijving']." (".$cmt_bedrijf_info['bed_handelsregisternummer'].") - ".$_REQUEST['cmt_rolnummer']."']");
				}

				break;


			case 'calamiteit.new':


				if (is_numeric($_REQUEST['cmt_id'])) {
					$cmt_id = $_REQUEST['cmt_id'];
				} else {
					list($cmt_id, $rtb_id, $cty_id) = explode('|',$_REQUEST['cmt_id']);
				}


				if (is_numeric($_REQUEST['cmt_rtb_id'])) {
					$_SESSION['crd_current_rtb_id'] = $_REQUEST['cmt_rtb_id'];
				}
				if (is_numeric($_REQUEST['cmt_cty_id'])) {
					$_SESSION['crd_current_cty_id'] = $_REQUEST['cmt_cty_id'];
				}


				if ($this->setCalamiteit($cmt_id, $_REQUEST)) {

					$this->_smrt->assign('error', 'Uitspraak succesvol opgeslagen');

				}

				break;


			case 'calamiteit.calamiteit_curator':

				if (isset($data['id_add']) && is_numeric($data['id_add'])) {

					$this->setCalamiteitCurator($id, $data['id_add']);
					$this->_smrt->assign('close_popup', true);


				} elseif (isset($data['id_delete']) && is_numeric($data['id_delete'])) {

					$this->setCalamiteitCurator($id, $data['id_delete'], null, false);


				} elseif (isset($data['id_edit']) && is_numeric($data['id_edit'])) {


				} else {


				}


				$result = $this->searchCurator($id, $_REQUEST);
				$this->_smrt->assign('search_result', $result);


				break;

			case 'calamiteit.calamiteit_curator.edit':

				list($cmt_id, $cur_id) = explode('|', $id);

				if (is_numeric($cmt_id) && is_numeric($cur_id)) {
					$this->setCalamiteitCurator($cmt_id, $cur_id, $_REQUEST);
					$this->_smrt->assign('close_popup', true);

				}


				break;


			case 'calamiteit.curator':

				$cur_id = null;
				if (!is_numeric($id)) {
					list($cur_id, $cmt_id) = explode('|', $id);

				} else {
					$cur_id = $id;
				}

				$cur_id = $this->setCurator($cur_id, $_REQUEST);

				if ($cur_id && $cmt_id) {
					$this->setCalamiteitCurator($cmt_id, $cur_id, $_REQUEST);
				}

				$this->_smrt->assign('close_popup', true);

				break;

			case 'calamiteit.calamiteitmutatie.new':
			case 'calamiteit.calamiteitmutatie':
				$data = $_REQUEST;

				if (!is_numeric($_REQUEST['mut_cmt_id'])) {
					$data['mut_cmt_id'] = $id;
				}

				if (isset($_REQUEST['id_delete']) && is_numeric($_REQUEST['id_delete'])) {
					$this->setCalamiteitmutatie($_REQUEST['id_delete'], null, false);
				} elseif (isset($_REQUEST['id_edit']) && is_numeric($_REQUEST['id_edit'])) {
					$this->setCalamiteitmutatie($_REQUEST['id_edit'], $data);
				} else {
					$this->setCalamiteitmutatie(null, $data);
				}

				if ($form_code == 'calamiteit.calamiteitmutatie.new') {
					$this->_smrt->assign('error', 'Mutatie succesvol opgeslagen');
				}

				break;

			case 'calamiteit.calamiteitbericht':

				$data = $_REQUEST;
				$data['cmb_cmt_id'] = $id;

				if (isset($_REQUEST['id_delete']) && is_numeric($_REQUEST['id_delete'])) {
					$this->setCalamiteitbericht($_REQUEST['id_delete'], null, false);
				} elseif (isset($_REQUEST['id_edit']) && is_numeric($_REQUEST['id_edit'])) {
					$this->setCalamiteitbericht($_REQUEST['id_edit'], $data);
				} else {
					$this->setCalamiteitbericht(null, $data);
				}

				break;

			case 'beheer.score.staffeltabel':

				if (isset($_REQUEST['stf_weight']) && is_numeric($_REQUEST['stf_weight'])) {

					$this->setScoreStaffel($id, array('stf_weight'=>$_REQUEST['stf_weight']));

				}

				if (is_array) {
				}
				$this->_smrt->assign('stf_data', $stf_data);

				$this->setScoreStaffelValues($_REQUEST['stv_in'], $_REQUEST['stv_out']);


				break;

			case 'beheer.bedrijfsscore':

				if (isset($_REQUEST['id_delete']) && is_numeric($_REQUEST['id_delete'])) {
					$this->setBedrijfsScoreOption($_REQUEST['id_delete'], null, false);
				}

				break;

			case 'beheer.bedrijfsscore.edit':

				$generate_id = $this->setBedrijfsScoreOption($id, $_REQUEST);

				if (!$id) {
					$this->_smrt->assign('close_popup', true);
				}

				break;

			case 'beheer.general_options':

				if (isset($_REQUEST['id_delete']) && is_numeric($_REQUEST['id_delete'])) {
					$this->setGeneralOption($_REQUEST['id_delete'], null, false);
				}

				break;

			case 'beheer.general_options.edit':

				$generate_id = $this->setGeneralOption($id, $_REQUEST);

				if (!$id) {
					$this->_smrt->assign('close_popup', true);
				}

				break;


		}
	}


	function parseField($bed_id, $field) {
		$field = strtolower($field);
		$prefix = substr($field, 0, strpos($field, '_'));

		$bedrijf = $this->getBedrijf($bed_id);
		if (isset($bedrijf[$field])) {

			if (!empty($bedrijf[$field])) {
				switch ($field) {
					case 'bed_betalingservaring_betalingsgedrag':
						return $this->getGeneralOptionLabel($field, $bedrijf[$field]);
						break;
					case 'bed_betalingservaring_max_factuur':
						return number_format($bedrijf[$field],0,',','.');
						break;

					case 'bed_datum_oprichting':

						if (strlen(trim($bedrijf['bed_datum_oprichting'])) > 0 && substr_count($bedrijf['bed_datum_oprichting'],'-') == 2) {
							list($d,$m,$y) = explode('-', $bedrijf['bed_datum_oprichting']);
							return date('d-m-Y', mktime(0,0,0,$m,$d,$y));
						} else {
							return $bedrijf['bed_datum_oprichting'];
						}


						break;

					case 'bed_opmerking':
						$ts_now = mktime(0,0,0,date('m'),date('d'),date('Y'));

						if (!$bedrijf['bed_opmerking_verloopdatum_orig'] || strtotime($bedrijf['bed_opmerking_verloopdatum_orig']) > $ts_now) {
							return  $bedrijf[$field];
						} else {
							return '';
						}
						break;


					default:
						return $bedrijf[$field];
						break;

				}
			} else {
				return '';
			}

		}

		switch ($prefix) {
			case 'adres':

				list(,$adres_type) = explode('_', $field);
				$adressen = $this->getAdressen($bed_id, $adres_type);
				foreach ($adressen as $adres) {
					if (!$adres['adr_actief_tot']) {
						//-- empty adres-type so it won't be showed
						$adres['adr_type'] = '';
						return fmt_plain_text('adres', array($adres));
					}
				}
				return '';
				break;

			case 'bsc': //--- bedrijfscore
				$scores = $this->getBedrijfScore($bed_id);
				$last_score = end($scores);

				$label_fields = array('bsc_trend', 'bsc_solvabiliteit', 'bsc_rentabiliteit', 'bsc_liquiditeit', 'bsc_betalingservaring', 'bsc_krediet_beoordeling_label', 'bsc_balans');

				if (isset($last_score[$field])) {
					if ($last_score[$field] != '0') {

						if (in_array($field, $label_fields)) {
							$return = $this->getBedrijfsScoreOptionLabel($field, $last_score[$field]);
						} else {
							$return = $last_score[$field];
						}


						if ($field == 'bsc_krediet_beoordeling_label') {
							$return = str_replace('[amount_advice]', '[de:bsc_krediet_beoordeling]', $return);
						} elseif ($field == 'bsc_krediet_beoordeling') {
							$return = number_format($last_score[$field],0,',','.');
						}

						return $return;
					}
				}
				return '';


				break;

			case 'bnk': //--- bank

				$return = array();
				$bank_info = $this->getBedrijfBank($bed_id);
				foreach ($bank_info as $bank) {
					if (!$bank['bbk_actief_tot']) $return[] = $bank['bnk_naam'];
				}

				return implode(', ', $return);

				break;

			case 'bps':
				$personeel = $this->getPersoneel($bed_id);

				$last_personeel = null;
				foreach ($personeel as $p) {
					if (!$p['bps_bm_verloopdatum']) $last_personeel = $p;
				}

				if (isset($last_personeel[$field])) {
					return $last_personeel[$field];
				}

			case 'kap':

				$kapitalen = $this->getKapitaal($bed_id, true);
				$kap = current($kapitalen);

				if (isset($kap[$field])) {
					return number_format($kap[$field],0,',','.');
				}
				return '';

				break;

			case 'ozw':

				$list = $this->getOmzetWinst($bed_id);
				if (!is_array($list) || count($list) == 0) {
					$cons_bedrijf = $this->getGrootBedrijfInStructure($bed_id);

					if (is_numeric($cons_bedrijf['bed_id']) && $cons_bedrijf['bed_id'] > 0) {
						$list = $this->getOmzetWinst($cons_bedrijf['bed_id']);
					}
				}

				$last_omzetwinst = end($list);

				if ($field == 'ozw_type_full') {
					$field = 'ozw_type';
					$return_type = 'full';
				} else {
					$return_type = '';
				}

				if (isset($last_omzetwinst[$field])) {

					if (empty($last_omzetwinst[$field])) {
						return '';
					}

					switch ($field) {
						case 'ozw_winst':
						case 'ozw_omzet':
						case 'ozw_brutowinst':
							return number_format($last_omzetwinst[$field],0,',','.');
							break;
						case 'ozw_type':
							$types = array('V'=>'Vennootschappelijk','C'=>'Geconsolideerd','E'=>'Eigen opgave');

							if ($return_type == 'full') {
								return $types[$last_omzetwinst['ozw_type']];
							} else {
								return $last_omzetwinst[$field] == 'C' ? $types['C']: '';
							}
							break;

						default:
							return $last_omzetwinst[$field];
							break;
					}

				}
				return '';

				break;

			case 'beterv':
				$list = array_reverse($this->getBetalingservaringKwartaal($bed_id));
				$beterv_translate = array('beterv_q1' => 3, 'beterv_q2' => 2, 'beterv_q3' => 1, 'beterv_q4' => 0);

				$index = $beterv_translate[$field];
				if ($list[$index]) {
					return substr($list[$index]['bek_kwartaal'],5,1) .' '.substr($list[$index]['bek_kwartaal'],0,4) .': '.$list[$index]['bek_dagen'];
				} else {
					return '';
				}

				break;

			case 'acc':

				$data = $this->getAccountant($bedrijf['bed_acc_id']);
				if (count($data)) {
					return fmt_plain_text('accountant', $data);
				}
				return '';

				break;

			case 'bbr':

				$bbr = $this->getBedrijfBron($bed_id);
				if (isset($bbr[$field]) && $bbr[$field] != '0') {

					switch ($field) {
						case 'bbr_jaarrekening_soort':
						case 'bbr_aanvullende_info':
							return $this->getGeneralOptionLabel($field, $bbr[$field]);
							break;

						case 'bbr_volledig_onderzoek':
						case 'bbr_update_onderzoek':
						case 'bbr_limiet_onderzoek':
						case 'bbr_samensteller_user':
						case 'bbr_data_entry_user':
						case 'bbr_handelsregister_gekocht':
						case 'bbr_jaarrekening_gekocht':
						case 'bbr_concernrelaties_gekocht':
						case 'bbr_betalingservaringen':
						case 'bbr_controle_abc':
							return ($bbr[$field] == '1') ? 'Ja' : 'Nee';
							break;

						default:
							return $bbr[$field];

					}
				} else {
					return '';
				}

				break;

			case 'kli':
				$list = $this->getKredietlimiet($bed_id);
				$kl = end($list);

				return number_format($kl['kli_kredietlimiet'], 0,',','.');

				break;

			case 'cmb':
			case 'cbr':

				$calamiteit = current($this->getCalamiteit(null, $bed_id));

				if ($calamiteit['cmt_id']) {

					$berichten = $this->getCalamiteitbericht($calamiteit['cmt_id']);
					if ($berichten) {

						$last_bericht = end($berichten);
						if (isset($last_bericht[$field])) {
							return $last_bericht[$field];
						}
					}
				}

				break;

			case 'bal':
				list($cons_bed_id, $balansen) = $this->getAllBalansData($bed_id);
				$balansen = array_reverse((array)$balansen);

				$return = array();
				$count = 0;
				foreach ($balansen as $balans) {
					if (++$count > 3) break;
					if (isset($balans[$field])) {

						$return[] = $balans[$field];

					} elseif ($field == 'bal_jaar_last' && count($return) == 0) {

						//$return[] = $balans['bal_jaar'];
						return $balans['bal_jaar'];

					} elseif ($field == 'bal_eigen_vermogen_last' && count($return) == 0) {

						//$return[] = number_format($balans['balans_data']['eigen_vermogen'], 0,',','.');
						return number_format($balans['balans_data']['eigen_vermogen'], 0,',','.');

					} elseif ($field == 'bal_totaal_vreemd_vermogen_last' && count($return) == 0) {

						//$return[] = number_format($balans['balans_data']['totaal_vreemd_vermogen'], 0,',','.');
						return number_format($balans['balans_data']['totaal_vreemd_vermogen'], 0,',','.');

					} elseif ($field == 'bal_consolidatie') {

						if (is_numeric($cons_bed_id)) {
							return fmt_plain_text('bedrijf_full', $this->getBedrijf($cons_bed_id));
						} else {
							return '';
						}
					} elseif ($field == 'bal_consolidatie_type') {

						//if (is_numeric($cons_bed_id) && $cons_bed_id != $bed_id) {
						if (is_numeric($cons_bed_id)) {
							return 'Geconsolideerd';
						} else {
							return 'Vennootschappelijk';
						}

					} elseif (isset($balans['balans_data'][str_replace('bal_','',$field)])) {
						$bal_value = $balans['balans_data'][str_replace('bal_','',$field)];
						$return[] = (is_numeric($bal_value) && $bal_value != '') ? number_format($bal_value, 0,',','.') : '';

					} else {
						$return[] = '';
					}

				}

				if ($field == 'bal_jaar' && !isset($GLOBALS['ccrd_krediet_rapport_years'])) {
					$GLOBALS['ccrd_krediet_rapport_years'] = $return;
				}

//				return implode(" | ", $return);
				return (strlen(trim(implode('',$return))) > 0) ? implode(" | ", $return) : '';

				break;

			case 'win':

				list($cons_bed_id, $wvs) = $this->getAllWinstVerliesData($bed_id);
				$wvs = array_reverse((array)$wvs);
				$return = array();
				$count = 0;
				foreach ($wvs as $wv) {

					if (++$count > 3) break;
					if (isset($wv[$field])) {
						$return[$wv['win_jaar']] = $wv[$field];
					} elseif (isset($wv['wv_data'][str_replace('win_','',$field)])) {
						$wiv_value = $wv['wv_data'][str_replace('win_','',$field)];
						$return[$wv['win_jaar']] = (is_numeric($wiv_value) && $wiv_value != '') ? number_format($wiv_value, 0,',','.') : '';

					} elseif ($field == 'win_jaar_last' && count($return) == 0) {
						$return[$wv['win_jaar']] = $wv['win_jaar'];
					} else {
						$return[$wv['win_jaar']] = '';
					}

				}

				if (isset($GLOBALS['ccrd_krediet_rapport_years']) && count($GLOBALS['ccrd_krediet_rapport_years'])) {
					$tmp_return = array();
					foreach ($GLOBALS['ccrd_krediet_rapport_years'] as $year) {
						$tmp_return[] = $return[$year];
					}
					$return = $tmp_return;
				}

				return (strlen(trim(implode('',$return))) > 0) ? implode(" | ", $return) : '';

				break;

			case 'fin':
				//list($cons_bed_id, $balansen) = $this->getAllBalansData($bed_id);
				//$balansen = array_reverse((array)$balansen);

				//$return = $this->_getFinancialData($balansen, $field);

				$cs = $this->getCreditScoring($bed_id, $null);
				list($cons_bed_id, $balans) = $this->getAllBalansData($bed_id);

				switch ($field) {
					case 'fin_quick_ratio':
					case 'fin_quick_ratio_last':
						$data = array_reverse($cs->data['quick_ratio']['source_data'], true);
						if (count($data)) {
							list($data,) = array_chunk($data, 3, true);
							array_walk($data, create_function('&$v', '$v = number_format($v, 2, ",",".");'));

							return ($field == 'fin_quick_ratio_last') ? ($data ? current($data) : '') : implode(" | ", $data);

						} else {
							return '';
						}


						break;

					case 'fin_current_ratio':
					case 'fin_current_ratio_last':
						$data = array_reverse($cs->data['current_ratio']['source_data'], true);
						if (count($data)) {
							list($data,) = array_chunk($data, 3, true);
							array_walk($data, create_function('&$v', '$v = number_format($v, 2, ",",".");'));

							return ($field == 'fin_current_ratio_last') ? ($data ? current($data) : '') : implode(" | ", $data);
						} else {
							return '';
						}

						break;

					case 'fin_werkkapitaal':
					case 'fin_werkkapitaal_last':

						$data = array_reverse($cs->data['werkkapitaal']['source_data'], true);
						if (count($data)) {
							list($data,) = array_chunk($data, 3, true);
							array_walk($data, create_function('&$v', '$v = number_format($v, 0, ",",".");'));

							return ($field == 'fin_werkkapitaal_last') ? ($data ? current($data) : '') : implode(" | ", $data);
						} else {
							return '';
						}

						break;

					case 'fin_werkkapitaal-balanstotaal':

						$balans_totaal = $this->getYearOverview($balans,'bal_jaar', 'balans_data.balanstotaal');

						$werk_kapitaal = array_reverse($cs->data['werkkapitaal']['source_data'], true);
						list($werk_kapitaal,) = array_chunk($werk_kapitaal, 3, true);

						$return = array();
						foreach ((array)$werk_kapitaal as $jaar => $value) {
							$return[$jaar] = number_format($value / $balans_totaal[$jaar], 2, ',','.');
						}

						return implode(" | ", $return);

						break;

					case 'fin_eigenvermogen-balanstotaal':
						$balans_totaal = $this->getYearOverview($balans,'bal_jaar', 'balans_data.balanstotaal');

						$eigen_vermogen = array_reverse($cs->data['eigen_vermogen']['source_data'], true);
						list($eigen_vermogen,) = array_chunk($eigen_vermogen, 3, true);

						$return = array();
						foreach ((array)$eigen_vermogen as $jaar => $value) {
							$return[$jaar] = number_format($value / $balans_totaal[$jaar], 2, ',','.');
						}

						return implode(" | ", $return);
						break;

					case 'fin_eigenvermogen-vasteactiva':
						$vaste_activa = $this->getYearOverview($balans,'bal_jaar', 'balans_data.vaste_activa');

						$eigen_vermogen = array_reverse($cs->data['eigen_vermogen']['source_data'], true);
						list($eigen_vermogen,) = array_chunk($eigen_vermogen, 3, true);

						$return = array();
						foreach ((array)$eigen_vermogen as $jaar => $value) {
							$return[$jaar] = ($vaste_activa[$jaar] != 0) ? number_format($value / $vaste_activa[$jaar], 2, ',','.') : '-';
						}

						return implode(" | ", $return);

						break;

				}


				break;


			case 'concern':


				switch ($field) {
					case 'concern_moeder':
						if (is_numeric($bedrijf['bed_id_moeder'])) {
							$moeder = $this->getBedrijf($bedrijf['bed_id_moeder']);
							return fmt_plain_text('bedrijf_full', $moeder);

						}

						break;

					case 'concern_moeder_uiteindelijk':

						if (is_numeric($bedrijf['bed_id_moeder_super'])) {

							if ($bedrijf['bed_id_moeder_super'] != $bedrijf['bed_id_moeder']) {
								$moeder = $this->getBedrijf($bedrijf['bed_id_moeder_super']);
								return fmt_plain_text('bedrijf_full', $moeder);
							} else {
								return '';
							}

						}

						break;

					case 'concern_dochters':
						//_getConcernChildren
						$children = $this->_getConcernChildren($bed_id);
						$return = array();
						foreach ((array)$children['children'] as $child) {
							$return[] = fmt_plain_text('bedrijf_full', $child['data']);
						}

						return implode("\n\n", $return);
						break;

					case 'concern_zusters':
						//_getConcernSisters

						//$sisters = $this->_getConcernSisters($bed_id);
						$sisters = $this->_getConcernChildren($bedrijf['bed_id_moeder']);
						$sisters = $sisters['children'];
						$return = array();
						foreach ((array)$sisters as $sister) {
							if ($sister['id'] != $bed_id) {
								$return[] = fmt_plain_text('bedrijf_full', $this->getBedrijf($sister['id']));
							}
						}

						return implode("\n\n", $return);

						break;
				}

				return '';

				break;


			case 'list':

				switch ($field) {
					case 'list_handelsnaam':
					case 'list_handelsnamen':
						$namen = $this->getHandelsnaam($bed_id);
						return implode("\n", $namen);

						break;

					case 'list_beroepsuitoefening':
						$list = $this->getBeroepsuitoefening($bed_id);
						$return = array();
						foreach ($list as $r) {
							$return[] = $r['bui_beschrijving'];
						}

						return implode("\n", $return);
						break;

					case 'list_bik':
					case 'list_nace':
					case 'list_bik_nace':

						$list = $this->getBIKNACE($bed_id);
						$return = array();
						foreach ($list as $r) {
							if ($field == 'list_bik_nace' || ($field == 'list_bik' && $r['type'] == 'bik') || ($field == 'list_nace' && $r['type'] == 'nace')) {
								$return[] = $r['beschrijving'] .' ('.$r['code'].')';;
							}
						}

						return implode("\n", $return);

						break;

					case 'list_contactpersoon':
					case 'list_contactpersonen':

						$list = $this->getBedrijfContactpersoon($bed_id, null, null, true);
						$return = fmt_plain_text('contactpersoon_full', $list);

						return $return;

						break;

					case 'list_import':
					case 'list_export':
					case 'list_importexport':
						$list = $this->getImportExport($bed_id);

						$return = array();
						foreach ($list as $item) {
							if ($item['iex_type'] == 'beide' || $field == 'list_importexport' ||
								($field == 'list_import' && $item['iex_type'] == 'import') ||
								($field == 'list_export' && $item['iex_type'] == 'export')) {
								$return[] = $item['lnd_beschrijving'];
							}
						}

						return implode(", ", $return);

						break;

					case 'list_aandeelhouders':
					case 'list_aandeelhouders_uiteindelijk':
						$list = $this->getAandeelhouder($bed_id, null, null, null, null, null, null, ($field == 'list_aandeelhouders_uiteindelijk'));
						return fmt_plain_text('aandeelhouder_full', $list);

						break;

					case 'list_bank':

						$return = array();
						$bank_info = $this->getBedrijfBank($bed_id);
						foreach ($bank_info as $bank) {
							if (!$bank['bbk_actief_tot']) $return[] = fmt_plain_text('bank_info', $bank);
						}

						return implode("\n", $return);
						break;

					case 'list_onroerendgoed':

						$onr_info = $this->getOnroerendgoedinformatie($bed_id);
						return fmt_plain_text('onroerend_goed', $onr_info);
						break;

					case 'list_nevenvestigingen':
					case 'list_nevenvestiging':

						$vestigingen = $this->getAdressenSubs($bed_id);
						if ($vestigingen['total_rows'] > CRD_MAX_ADDRESS_RESULTS) {
							return $vestigingen['total_rows'] .' vestigingen';
						} else {
							return fmt_plain_text('neven_vestigingen', $vestigingen['result']);
						}

						break;

					case 'list_merk':
					case 'list_merken':
						$merken = $this->getMerk($bed_id);
						return implode(", ", $merken);

						break;

					case 'list_winst':
					case 'list_omzet':
					case 'list_brutowinst':
						$list = $this->getOmzetWinst($bed_id);

						if (!is_array($list) || count($list) == 0) {
							$cons_bedrijf = $this->getGrootBedrijfInStructure($bed_id);

							if (is_numeric($cons_bedrijf['bed_id']) && $cons_bedrijf['bed_id'] > 0) {
								$list = $this->getOmzetWinst($cons_bedrijf['bed_id']);
							}
						}


						$list = array_reverse($list, true);

						$return = array();
						foreach ($list as $ozw) {
							switch ($field) {
								case 'list_winst':
									if ($ozw['ozw_winst']) $return[] = $ozw['ozw_jaar'] .": ".number_format($ozw['ozw_winst'], 0, ',','.');
									break;
								case 'list_omzet':
									if ($ozw['ozw_omzet']) $return[] = $ozw['ozw_jaar'] .": ".number_format($ozw['ozw_omzet'], 0, ',','.');
									break;
								case 'list_brutowinst':
									if ($ozw['ozw_brutowinst']) $return[] = $ozw['ozw_jaar'] .": ".number_format($ozw['ozw_brutowinst'], 0, ',','.');
									break;
							}
						}

						return implode("\n", $return);

						break;

					case 'list_instemmingsverklaring':
					case 'list_instemmingsverklaringen':

						$list = $this->getVerklaringInstemming($bed_id);
						return fmt_plain_text('instemmingsverklaring', $list);

						break;

					case 'list_aansprakelijkheidsverklaring':
					case 'list_aansprakelijkheidsverklaringen':

						$list = $this->getVerklaringAansprakelijkheid($bed_id);
						return fmt_plain_text('aansprakelijkheidsverklaring', $list);

						break;

					case 'list_structuur':

						$bedrijf_structure = $this->getConcernGegevenStructure($bed_id);

						return fmt_plain_text('structuur', $bedrijf_structure);

						return "stom";
						break;


				}

				break;


		}


		return false;
	}


	function goSearch($search_words, $nam_beschrijving='', $adr_plaats='', $adr_postcode='', $adr_huisnummer='', $bed_handelsregisternummer='', $page) {

//		$result = $this->searchBedrijf($search_words, $nam_beschrijving, $adr_plaats, $adr_postcode, $adr_huisnummer, $bed_handelsregisternummer, '' ,$page);

		$result = $this->searchBedrijf2(
									$nam_beschrijving
								,	$adr_plaats
								,	$adr_postcode
								,	$adr_huisnummer
								,	$bed_handelsregisternummer
								,	null
								,	$page);


		$this->_smrt->assign('search_result', $result);

		// Process the template
		$this->_smrt->display('bedrijf.search.tpl');

	}

	function goSearchCalamiteit($nam_beschrijving='', $adr_plaats='', $adr_postcode='', $adr_huisnummer='', $bed_handelsregisternummer='', $rtb_id, $cty_id, $page) {

		$cmt_data = array(	'rtb_id'	=> $rtb_id
						,	'cty_id'	=> $cty_id
										);

		if (!$nam_beschrijving && !$adr_postcode && !$bed_handelsregisternummer) {

			$result = $this->searchCalamiteiten($rtb_id, $cty_id, null, null, null, $page);

		} else {

			$result = $this->searchBedrijf2($nam_beschrijving, $adr_plaats, $adr_postcode, $adr_huisnummer, $bed_handelsregisternummer, $cmt_data ,$page);

		}

		$this->_smrt->assign('search_result', $result);

		// Process the template
		$this->_smrt->display('calamiteit.search.tpl');

	}

	function getHandelsnaam($bed_id, $hnm_beschrijving='') {
		if (is_numeric($bed_id)) {
			if (strlen($hnm_beschrijving) > 0) {
				$sql = "SELECT hnm_id, hnm_beschrijving FROM crd_handelsnaam WHERE hnm_bed_id = ".$bed_id ." AND hnm_beschrijving = '".mysql_escape_string($hnm_beschrijving)."'";
			} else {
				$sql = "SELECT hnm_id, hnm_beschrijving FROM crd_handelsnaam WHERE hnm_bed_id = ".$bed_id;
			}
			$rs = $this->_db->query($sql);
			return rs2arr($rs, 'hnm_id');
		}
	}

	function setHandelsnaam($hnm_id, $bed_id, $hnm_beschrijving, $delete=false) {

		if (is_numeric($hnm_id)) {

			if ($delete) {
				$sql = "DELETE FROM crd_handelsnaam WHERE hnm_id =".$hnm_id;
			} else {
				$sql = "UPDATE crd_handelsnaam SET hnm_beschrijving = '".mysql_escape_string(trim($hnm_beschrijving))."' WHERE hnm_id =".$hnm_id;
			}
			$this->_db->execute($sql);

		} elseif (is_numeric($bed_id)) {
			$sql = "INSERT INTO crd_handelsnaam SET hnm_bed_id = ".$bed_id.", hnm_beschrijving = '".mysql_escape_string(trim($hnm_beschrijving))."'";
			$this->_db->execute($sql);
		}

		$this->setLastEditor($bed_id);

	}

	function getBedrijf($bed_id) {
		if (is_numeric($bed_id)) {

			if (!isset($this->_cache['bedrijf'][$bed_id])) {
				$sql = "SELECT b1.*
						,	b2.bed_id as filiaal_van_bed_id
						,	crd_adres.*
						,	crd_land.lnd_beschrijving
						,	con_bed_hoort_bij as bed_id_moeder_super
						,	rcv_beschrijving
						,	rcv_creditscore
						,	hrg_naam
						,	REPLACE(DATE_FORMAT(b1.bed_voortgezet_op, '%d-%m-%Y'),'00-00-0000','') as bed_voortgezet_op
						,	REPLACE(DATE_FORMAT(b1.bed_laatstewijziging_statuten, '%d-%m-%Y'),'00-00-0000','') as bed_laatstewijziging_statuten
						,	b1.bed_opmerking_verloopdatum as bed_opmerking_verloopdatum_orig
						,	REPLACE(DATE_FORMAT(b1.bed_opmerking_verloopdatum, '%d-%m-%Y'),'00-00-0000','') as bed_opmerking_verloopdatum
						FROM crd_bedrijf b1
						LEFT JOIN crd_bedrijf as b2 on b2.bed_handelsregisternummer = b1.bed_handelsregisternummer AND b1.bed_handelsregisternummer != '00000000' AND b1.bed_subdossiernummer != '0000' AND b2.bed_subdossiernummer = '0000'
						LEFT JOIN crd_adres ON adr_bed_id = b1.bed_id AND adr_actief_tot IS NULL AND adr_type = 'kantoor'
						LEFT JOIN crd_land ON lnd_id = adr_lnd_id
						LEFT JOIN crd_concern_relatie ON rel_type = '".CRD_RELATIE_TYPE_SUPER."'
						LEFT JOIN crd_concerngegeven ON con_bed_id = b1.bed_id AND rel_id = con_rel_id
						LEFT JOIN crd_rechtsvorm ON rcv_id = b1.bed_rcv_id_huidig
						LEFT JOIN crd_handelsregister ON hrg_id = b1.bed_hrg_id
						WHERE b1.bed_id = ".$bed_id;


				$bedrijf_data = rs2arr($this->_db->query($sql), false, true);
				if ($bedrijf_data['bed_id']) {

					$bedrijf_data['nam_beschrijving'] = $this->getBedrijfsNaam($bed_id);

					if (!is_numeric($bedrijf_data['bed_id_moeder_super'])) {
						//-- get the POAP (Parent of all parents)
						$super_parent_id = $bed_id;
						while ($id = $this->_getConcernParent($super_parent_id)) {
							$super_parent_id = $id;
						}
						if ($super_parent_id != $bed_id) {
							$bedrijf_data['bed_id_moeder_super'] = $super_parent_id;
						}

					}
					if (is_numeric($bedrijf_data['bed_id_moeder_super'])) {

						$bedrijf_data['nam_beschrijving_moeder_super'] = $this->getBedrijfsNaam($bedrijf_data['bed_id_moeder_super']);

					}

					if ($bed_id_parent = $this->_getConcernParent($bed_id)) {

						$bedrijf_data['bed_id_moeder'] = $bed_id_parent;
						$bedrijf_data['nam_beschrijving_moeder'] = $this->getBedrijfsNaam($bed_id_parent);

					}

				}

				$this->_cache['bedrijf'][$bed_id] = $bedrijf_data;

			}

			return $this->_cache['bedrijf'][$bed_id];
		}
	}

	function searchBedrijf($search_words, $nam_beschrijving='', $adr_plaats='', $adr_postcode='', $adr_huisnummer='', $bed_handelsregisternummer='', $con_bed_id=null, $page=1) {

		if (!is_numeric($search_words)) {
			$search_words = 1;
		}
		$starttime = getmicroseconds();

		$return = array();

//		if ($nam_beschrijving && $adr_plaats) {
		if ($nam_beschrijving) {


			$sql = "CREATE TEMPORARY TABLE _temp_search_result (i INT not null AUTO_INCREMENT, INDEX (i))
					SELECT nam_bed_id as tmp_bed_id, nam_id as tmp_nam_id FROM crd_naam WHERE nam_actief_tot IS NULL AND MATCH(nam_beschrijving) AGAINST ('+".mysql_escape_string(str_replace(' ',' +',$nam_beschrijving))."' IN BOOLEAN MODE)";

			$this->_db->execute($sql);

			$sql = "SELECT SQL_CALC_FOUND_ROWS distinct b1.bed_id, b1.*, b2.bed_id as filiaal_id, nam_beschrijving, a2.*
					FROM _temp_search_result
					".($adr_plaats ? 'INNER JOIN crd_adres a1 ON a1.adr_bed_id = tmp_bed_id' : '') ."
					LEFT JOIN crd_adres a2 ON a2.adr_bed_id = tmp_bed_id AND a2.adr_type='kantoor'
					LEFT JOIN crd_bedrijf as b1 on b1.bed_id = tmp_bed_id
					LEFT JOIN crd_bedrijf as b2 on b2.bed_handelsregisternummer = b1.bed_handelsregisternummer AND b2.bed_subdossiernummer = '0000'
					INNER JOIN crd_naam ON nam_id = tmp_nam_id
					".($adr_plaats ? "WHERE a1.adr_plaats LIKE '%".mysql_escape_string($adr_plaats)."%'" : '');

		} elseif ($adr_postcode) {

			$sql = "SELECT SQL_CALC_FOUND_ROWS distinct b1.bed_id, b1.*, b2.bed_id as filiaal_id, nam_beschrijving, a2.*
					FROM crd_bedrijf b1
					LEFT JOIN crd_adres a1 ON
							a1.adr_bed_id = b1.bed_id
						AND a1.adr_actief_tot IS NULL
						AND a1.adr_postcode LIKE '".mysql_escape_string($adr_postcode)."%'
						".($adr_huisnummer ? "AND a1.adr_huisnummer LIKE '".mysql_escape_string($adr_huisnummer)."%'" : '')."
					LEFT JOIN crd_adres a2 ON a2.adr_bed_id = b1.bed_id AND a2.adr_actief_tot IS NULL AND a2.adr_type = 'kantoor'
					INNER JOIN crd_naam ON nam_bed_id = b1.bed_id AND nam_actief_tot IS NULL
					LEFT JOIN crd_bedrijf as b2 on b2.bed_handelsregisternummer = b1.bed_handelsregisternummer AND b2.bed_subdossiernummer = '0000'
					ORDER BY nam_beschrijving";



		} elseif ($bed_handelsregisternummer) {

			$sql = "SELECT SQL_CALC_FOUND_ROWS distinct b1.bed_id, b1.*, b2.bed_id as filiaal_id, nam_beschrijving, crd_adres.*
					FROM crd_bedrijf b1
					LEFT JOIN crd_adres ON adr_bed_id = b1.bed_id AND adr_actief_tot IS NULL AND adr_type = 'kantoor'
					INNER JOIN crd_naam ON nam_bed_id = b1.bed_id AND nam_actief_tot IS NULL
					LEFT JOIN crd_bedrijf as b2 on b2.bed_handelsregisternummer = b1.bed_handelsregisternummer AND b2.bed_subdossiernummer = '0000'
					WHERE b1.bed_handelsregisternummer LIKE '".mysql_escape_string($bed_handelsregisternummer)."%'";

		}

		$result = array();
		if ($sql) {


			if (!is_numeric($page)) $page = 1;

			if (isset($_SESSION['crd_last_search']['search_toon']) && is_numeric($_SESSION['crd_last_search']['search_toon']))  {
				$max = $_SESSION['crd_last_search']['search_toon'];
			} else {
				$max = ($this->form_mode == 'popup') ? CRD_RESULTS_PER_PAGE_POPUP : CRD_RESULTS_PER_PAGE;
			}



			$offset = is_numeric($page) ? ($page-1)*$max : 0;

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

			while ($r = $rs->row()) {

				$r = array_map('stripslashes', $r);

				$r['master_id'] = is_numeric($r['filiaal_id']) ? $r['filiaal_id'] : $r['bed_id'];

				$result[$r['bed_id']] = $r;

				$rs->moveNext();
			}
			$rs->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_per_page', $max);


		}

		return $result;
	}

	function searchBedrijf2($nam_beschrijving='', $adr_plaats='', $adr_postcode='', $adr_huisnummer='', $bed_handelsregisternummer='', $cmt_data=null, $page=1) {

		$starttime = getmicroseconds();

		$return = array('search_total'=>0, 'search_page'=>0, 'search_per_page'=>0);

		$fields = array();
		$froms = array();
		$joins = array();
		$wheres = array();

		if ($nam_beschrijving) {

			$sql = "CREATE TEMPORARY TABLE _temp_search_result (i INT not null AUTO_INCREMENT, INDEX (i))
					SELECT nam_bed_id as tmp_bed_id, nam_id as tmp_nam_id FROM crd_naam WHERE nam_actief_tot IS NULL AND MATCH(nam_beschrijving) AGAINST ('+".mysql_escape_string(str_replace(' ',' +',$nam_beschrijving))."' IN BOOLEAN MODE)";

			$this->_db->execute($sql);

			$froms[] = '_temp_search_result';
			$joins[] = "INNER JOIN crd_bedrijf as b1 on b1.bed_id = tmp_bed_id";
			$joins[] = "LEFT JOIN crd_naam ON nam_id = tmp_nam_id";

		} else {
			$froms[] = 'crd_bedrijf b1';

			//--- get the company name
			$joins[] = "LEFT JOIN crd_naam ON nam_bed_id = b1.bed_id AND nam_actief_tot IS NULL";
		}


		//--- always get 'kantoor' address
		$joins[] = "LEFT JOIN crd_adres a2 ON a2.adr_bed_id = b1.bed_id AND a2.adr_type='kantoor' and a2.adr_actief_tot IS NULL";
		//--- if company is a 'filiaal' get its owner
		$joins[] = "LEFT JOIN crd_bedrijf as b2 on b2.bed_handelsregisternummer = b1.bed_handelsregisternummer AND b1.bed_handelsregisternummer != '00000000' AND b2.bed_subdossiernummer = '0000'";


		if ($adr_plaats || $adr_postcode) {

			//--- search in the address
			$joins[] = "LEFT JOIN crd_adres a1 ON a1.adr_bed_id = b1.bed_id";
			$adr_wheres = array();
			if ($adr_plaats) $adr_wheres[] = "a1.adr_plaats LIKE '%".mysql_escape_string($adr_plaats)."%'";
			if ($adr_postcode) $adr_wheres[] = "a1.adr_postcode LIKE '".mysql_escape_string($adr_postcode)."%'";
			if ($adr_huisnummer) $adr_wheres[] = "a1.adr_huisnummer = '".mysql_escape_string($adr_huisnummer)."'";


			$wheres[] = '('.implode(' AND ', $adr_wheres) .')';
		}

		if ($bed_handelsregisternummer) {
			$wheres[] = "b1.bed_handelsregisternummer LIKE '".mysql_escape_string($bed_handelsregisternummer)."%'";
		}

		if (is_array($cmt_data)) {

			$ons = array();
			if (isset($cmt_data['rtb_id']) && is_numeric($cmt_data['rtb_id']))	$ons[] = "cmt_rtb_id = ". $cmt_data['rtb_id'];
			if (isset($cmt_data['year']) && is_numeric($cmt_data['year']))		$ons[] = "YEAR(cmt_datum) = ". $cmt_data['year'];
			if (isset($cmt_data['week']) && is_numeric($cmt_data['week']))		$ons[] = "WEEK(cmt_datum, 1) = ". $cmt_data['week'];
			if (isset($cmt_data['insolventienummer']) && strlen($cmt_data['insolventienummer']) > 0)		$ons[] = "cmt_insolventienummer LIKE '%".$cmt_data['insolventienummer']."%'";


			$joins[] = "INNER JOIN crd_calamiteit ON cmt_bed_id = b1.bed_id".(count($ons) ? " AND " . implode(' AND ', $ons) : '');
			$joins[] = "INNER JOIN crd_calamiteittype ON cty_id = cmt_cty_id";
			$fields[] = 'crd_calamiteit.*, crd_calamiteittype.*';
			$fields[] = "CONCAT(cty_voorloopcode,'.',DATE_FORMAT(cmt_datum, '%y'),'/',cmt_insolventienummer) as rolnummer";
		}


		$sql = "SELECT SQL_CALC_FOUND_ROWS distinct b1.bed_id, b1.*, b2.bed_id as filiaal_id, nam_beschrijving, a2.*
				".($fields ? ','.implode(',',$fields) : '')."
				FROM ".implode(', ', $froms)."
				".implode("\n", $joins)."
				".($wheres ? ("WHERE ".implode(" AND ", $wheres)) : '')."
				GROUP BY b1.bed_id
				ORDER BY nam_beschrijving, a2.adr_plaats";



		if (!$nam_beschrijving && count($wheres) == 0) {
			// ERROR - no valid search query
			$sql = '';
		}

		$result = array();

		if ($sql) {

			if (!is_numeric($page)) $page = 1;
			$max = $this->settings->search->results_per_page == 'all' ? 50000 : $this->settings->search->results_per_page;
			$offset = is_numeric($page) ? ($page-1)*$max : 0;

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

			while ($r = $rs->row()) {

				$r = array_map('stripslashes', $r);

				$r['master_id'] = is_numeric($r['filiaal_id']) ? $r['filiaal_id'] : $r['bed_id'];

//				$result[$r['bed_id']] = $r;
				$result[] = $r;

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

			$tmp = $this->_db->query('SELECT FOUND_ROWS()');
			$total_found_rows_adres = $tmp->Row('FOUND_ROWS()');

			$return['search_total'] = $total_found_rows_adres;
			$return['search_page'] = $page;
			$return['search_per_page'] = $max;

		}

		$return['result'] = $result;

		return $return;
	}

	function setBedrijf($bed_id, $data) {
		if (!is_numeric($bed_id)) return;

		$fields = array();

		if ($data['bed_handelsregisternummer'] == '99') {
			$data['bed_handelsregisternummer'] = '99'.str_pad($bed_id, 10, '0', STR_PAD_LEFT);
		} elseif ($data['bed_handelsregisternummer'] == '98') {
			$data['bed_handelsregisternummer'] = '98'.str_pad($bed_id, 10, '0', STR_PAD_LEFT);
		} elseif ($data['bed_handelsregisternummer'] == '97') {
			$data['bed_handelsregisternummer'] = '97'.str_pad($bed_id, 10, '0', STR_PAD_LEFT);
		}

		if (isset($data['bed_handelsregisternummer']))		$fields['bed_handelsregisternummer']	= "bed_handelsregisternummer = '".	mysql_escape_string(trim($data['bed_handelsregisternummer']))."'";
		if (isset($data['bed_subdossiernummer']))			$fields['bed_subdossiernummer']			= "bed_subdossiernummer = '".		mysql_escape_string(trim($data['bed_subdossiernummer']))."'";
		if (isset($data['bed_email_adres']))				$fields['bed_email_adres']				= "bed_email_adres = '".			mysql_escape_string(trim($data['bed_email_adres']))."'";
		if (isset($data['bed_web_adres']))					$fields['bed_web_adres']				= "bed_web_adres = '".				mysql_escape_string(trim($data['bed_web_adres']))."'";
		if (isset($data['bed_telefoonnummer']))				$fields['bed_telefoonnummer']			= "bed_telefoonnummer = '".			mysql_escape_string(trim($data['bed_telefoonnummer']))."'";
		if (isset($data['bed_faxnummer']))					$fields['bed_faxnummer']				= "bed_faxnummer = '".				mysql_escape_string(trim($data['bed_faxnummer']))."'";
		if (isset($data['bed_btw_nummer']))					$fields['bed_btw_nummer']				= "bed_btw_nummer = '".				mysql_escape_string(trim($data['bed_btw_nummer']))."'";
		if (isset($data['bed_datum_oprichting']))			$fields['bed_datum_oprichting']			= "bed_datum_oprichting = '".		mysql_escape_string(trim($data['bed_datum_oprichting']))."'";
		if (isset($data['bed_plaats_oprichting']))			$fields['bed_plaats_oprichting']		= "bed_plaats_oprichting = '".		mysql_escape_string(trim($data['bed_plaats_oprichting']))."'";

		if (isset($data['bed_voortgezet']))					$fields['bed_voortgezet']				= "bed_voortgezet = '".				($data['bed_voortgezet'] ? 1 : 0)."'";
		if (isset($data['bed_voortgezet_op']))				$fields['bed_voortgezet_op']			= "bed_voortgezet_op = ".			($data['bed_voortgezet_op'] ? "'".$this->_fmt_date($data['bed_voortgezet_op']) ."'": 'NULL');
		if (isset($data['bed_laatstewijziging_statuten']))	$fields['bed_laatstewijziging_statuten']= "bed_laatstewijziging_statuten= ".($data['bed_laatstewijziging_statuten'] ? "'".$this->_fmt_date($data['bed_laatstewijziging_statuten']) ."'": 'NULL');
		if (isset($data['bed_beeindigd']))					$fields['bed_beeindigd']				= "bed_beeindigd = '".				($data['bed_beeindigd'] ? 1 : 0)."'";


		if (isset($data['bed_grootbedrijf_code']))			$fields['bed_grootbedrijf_code']		= "bed_grootbedrijf_code = ".		($data['bed_grootbedrijf_code'] ? 1 : 0)."";
		if (isset($data['bed_grootbedrijf_type']))			$fields['bed_grootbedrijf_type']		= "bed_grootbedrijf_type = ".		(($data['bed_grootbedrijf_type'] == 'N' || $data['bed_grootbedrijf_type'] == 'I') ? "'".$data['bed_grootbedrijf_type']."'" : 'NULL')."";

		if (isset($data['bed_hrg_id']))						$fields['bed_hrg_id']					= "bed_hrg_id = ".					(is_numeric($data['bed_hrg_id']) ? $data['bed_hrg_id'] : 'NULL');
		if (isset($data['bed_rcv_id_oprichting']))			$fields['bed_rcv_id_oprichting']		= "bed_rcv_id_oprichting = ".		(is_numeric($data['bed_rcv_id_oprichting']) ? $data['bed_rcv_id_oprichting'] : 'NULL');
		if (isset($data['bed_rcv_id_huidig']))				$fields['bed_rcv_id_huidig']			= "bed_rcv_id_huidig = ".			(is_numeric($data['bed_rcv_id_huidig']) ? $data['bed_rcv_id_huidig'] : 'NULL');

		if (isset($data['bed_betalingservaring_max_factuur']))		$fields['bed_betalingservaring_max_factuur']		= "bed_betalingservaring_max_factuur = '".		mysql_escape_string(trim($data['bed_betalingservaring_max_factuur']))."'";
		if (isset($data['bed_betalingservaring_betalingsgedrag']))	$fields['bed_betalingservaring_betalingsgedrag']	= "bed_betalingservaring_betalingsgedrag = '".	mysql_escape_string(trim($data['bed_betalingservaring_betalingsgedrag']))."'";

		if (isset($data['bed_opmerking']))					$fields['bed_opmerking']				= "bed_opmerking = '".	mysql_escape_string(trim($data['bed_opmerking']))."'";
		if (isset($data['bed_opmerking_verloopdatum']))		$fields['bed_opmerking_verloopdatum']	= "bed_opmerking_verloopdatum = ".	($data['bed_opmerking_verloopdatum'] ? "'".$this->_fmt_date($data['bed_opmerking_verloopdatum']) ."'": 'NULL');


		if (count($fields)) {

			$sql = "UPDATE crd_bedrijf SET ". implode(', ',$fields) .' WHERE bed_id = '.$bed_id;
			$this->_db->execute($sql);

		}

		//--- update name
		if (isset($data['nam_beschrijving'])) $this->setBedrijfsNaam($bed_id, $data['nam_beschrijving']);

		$this->bronUpdate(CRD_BRON_TYPE_BEDRIJF, 'update', $bed_id);
		$this->setLastEditor($bed_id);


	}

	function deleteBedrijf($bed_id) {
		if (!is_numeric($bed_id)) return false;

		$this->_db->Execute("DELETE FROM crd_adres WHERE adr_bed_id = ".$bed_id);
		$this->_db->Execute("DELETE FROM crd_aansprakelijkheidsverklaring WHERE ans_bed_id = ".$bed_id);
		$this->_db->Execute("DELETE FROM crd_aansprakelijkheidsverklaring WHERE ans_bed_id_aansprakelijk = ".$bed_id);

		$this->_db->Execute("UPDATE crd_balans SET bal_cons_bed_id = NULL WHERE bal_cons_bed_id = ".$bed_id);

		$rs_balans = $this->_db->Query("SELECT bal_id FROM crd_balans WHERE bal_bed_id = ". $bed_id);
		$bal_ids = rs2arr($rs_balans);
		foreach ($bal_ids as $bal_id) {
			$this->setBalans($bed_id, $bal_id, null, false);
		}

		$this->_db->Execute("DELETE FROM crd_bedrijf_aandeelhouder WHERE ban_bed_id = ".$bed_id);
		$this->_db->Execute("DELETE FROM crd_bedrijf_aandeelhouder WHERE ban_bed_id_connect = ".$bed_id);
		$this->_db->Execute("DELETE FROM crd_bedrijf_bank WHERE bbk_bed_id = ".$bed_id);
		$this->_db->Execute("DELETE FROM crd_bedrijf_bikcode WHERE beb_bed_id = ".$bed_id);
		$this->_db->Execute("DELETE FROM crd_bedrijf_contactpersoon WHERE bct_bed_id = ".$bed_id);
		$this->_db->Execute("DELETE FROM crd_bedrijf_contactpersoon WHERE bct_bed_id_connect = ".$bed_id);
		$this->_db->Execute("DELETE FROM crd_bedrijf_debiteurennummer WHERE deb_bed_id_leverancier = ".$bed_id);
		$this->_db->Execute("DELETE FROM crd_bedrijf_debiteurennummer WHERE deb_bed_id_bedrijf = ".$bed_id);
		$this->_db->Execute("DELETE FROM crd_bedrijf_nacecode WHERE bna_bed_id = ".$bed_id);
		$this->_db->Execute("DELETE FROM crd_bedrijf_organisatie WHERE bor_bed_id = ".$bed_id);
		$this->_db->Execute("DELETE FROM crd_bedrijfpersoneel WHERE bps_bed_id = ".$bed_id);
		$this->_db->Execute("DELETE FROM crd_bedrijfscore WHERE bsc_bed_id = ".$bed_id);
		$this->_db->Execute("DELETE FROM crd_beroepsuitoefening WHERE bui_bed_id = ".$bed_id);
		$this->_db->Execute("DELETE FROM crd_betalingservaring WHERE brv_bed_id_leverancier = ".$bed_id);
		$this->_db->Execute("DELETE FROM crd_betalingservaring WHERE brv_bed_id = ".$bed_id);
		$this->_db->Execute("DELETE FROM crd_betalingservaring_kwartaal WHERE bek_bed_id = ".$bed_id);
		$this->_db->Execute("DELETE FROM crd_bron_bedrijf WHERE brb_bed_id = ".$bed_id);

		$rs_cmt = $this->_db->Query("SELECT cmt_id FROM crd_calamiteit WHERE cmt_bed_id = ". $bed_id);
		$cmt_ids = rs2arr($rs_cmt);
		foreach ($cmt_ids as $cmt_id) {
			$this->setCalamiteit($cmt_id, null, false);
		}

		$this->_db->Execute("DELETE FROM crd_concerngegeven WHERE con_bed_id = ".$bed_id);
		$this->_db->Execute("DELETE FROM crd_concerngegeven WHERE con_bed_hoort_bij = ".$bed_id);

		$this->_db->Execute("DELETE FROM crd_handelsnaam WHERE hnm_bed_id = ".$bed_id);
		$this->_db->Execute("DELETE FROM crd_import_export WHERE iex_bed_id = ".$bed_id);
		$this->_db->Execute("DELETE FROM crd_instemmingsverklaring WHERE inv_bed_id = ".$bed_id);
		$this->_db->Execute("DELETE FROM crd_instemmingsverklaring WHERE inv_bed_id_instemming = ".$bed_id);
		$this->_db->Execute("DELETE FROM crd_kapitaal WHERE kap_bed_id = ".$bed_id);
		$this->_db->Execute("DELETE FROM crd_kerngegeven WHERE krn_bed_id = ".$bed_id);
		$this->_db->Execute("DELETE FROM crd_kredietlimiet WHERE kli_bed_id = ".$bed_id);
		$this->_db->Execute("DELETE FROM crd_merk WHERE mrk_bed_id = ".$bed_id);
		$this->_db->Execute("DELETE FROM crd_omzetwinst WHERE ozw_bed_id = ".$bed_id);
		$this->_db->Execute("DELETE FROM crd_onroerendgoedinformatie WHERE onr_bed_id = ".$bed_id);

		$this->_db->Execute("UPDATE crd_winstenverlies SET win_cons_bed_id = NULL WHERE win_cons_bed_id = ".$bed_id);

		$rs_win = $this->_db->Query("SELECT win_id FROM crd_winstenverlies WHERE win_bed_id = ". $bed_id);
		$win_ids = rs2arr($rs_win);
		foreach ($win_ids as $win_id) {
			$this->setWinstVerlies($bed_id, $win_id, null, false);
		}

		$this->_db->Execute("DELETE FROM crd_bedrijf WHERE bed_id = ".$bed_id);

	}

	function getBedrijfByKVK($kvk, $sub='') {
		$sql = "SELECT bed_id FROM crd_bedrijf WHERE bed_handelsregisternummer = '".mysql_escape_string(trim($kvk))."'";
		if ($sub) $sql .= " AND bed_subdossiernummer = '".mysql_escape_string(trim($sub))."'";

		$rs = $this->_db->query($sql);
		$bed_id = $rs->Row('bed_id');
		$rs->Close();
		return is_numeric($bed_id) ? $bed_id : false;
	}

	function getBedrijfsNaam($bed_id) {
		if (!is_numeric($bed_id)) return;

		$sql = "SELECT nam_beschrijving FROM crd_naam WHERE nam_bed_id = ".$bed_id." AND nam_actief_tot IS NULL LIMIT 1";
		$rs = $this->_db->query($sql);
		return stripslashes($rs->Row('nam_beschrijving'));
	}

	function setBedrijfsNaam($bed_id, $nam_beschrijving) {
		if (strlen(trim($nam_beschrijving)) > 0 && $this->getBedrijfsNaam($bed_id) != $nam_beschrijving) {
			$sql = "UPDATE crd_naam SET nam_actief_tot = NOW() WHERE nam_bed_id = ".$bed_id." AND nam_actief_tot IS NULL";
			$this->_db->execute($sql);

			$sql = "INSERT INTO crd_naam SET
						nam_beschrijving	= '".mysql_escape_string(trim($nam_beschrijving))."'
					,	nam_bed_id			= ".$bed_id."
					,	nam_actief_tot		= NULL";
			$this->_db->execute($sql);

			$this->setLastEditor($bed_id);

		}
	}


	function getAdressen($bed_id, $adr_type=null) {
		if (is_numeric($bed_id)) {
			$sql = "SELECT
						*
					,	adr_actief_tot
					,	DATE_FORMAT(adr_actief_tot, '%d-%m-%Y') AS adr_actief_tot
					FROM crd_adres
					WHERE adr_bed_id = ".$bed_id."
					".($adr_type ? " AND adr_type = '".$adr_type."'" : '');
			$rs = $this->_db->query($sql);
			return rs2arr($rs, 'adr_id');
		}
	}

	function getAdressenSubs($bed_id) {
		if (is_numeric($bed_id)) {
			$sql = "SELECT SQL_CALC_FOUND_ROWS
						*
					,	DATE_FORMAT(adr_actief_tot, '%d-%m-%Y') AS adr_actief_tot
					,	nam_beschrijving
					FROM crd_adres
					INNER JOIN crd_bedrijf AS bed_hoofd ON bed_hoofd.bed_id = ".$bed_id."
					INNER JOIN crd_bedrijf AS bed_sub ON bed_sub.bed_subdossiernummer != '0000' AND bed_sub.bed_handelsregisternummer = bed_hoofd.bed_handelsregisternummer
					INNER JOIN crd_naam ON nam_bed_id = bed_sub.bed_id
					WHERE adr_bed_id = bed_sub.bed_id AND adr_type = 'kantoor'";


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

			$tmp = $this->_db->query('SELECT FOUND_ROWS()');
			$total_found_rows_adres = $tmp->Row('FOUND_ROWS()');

			$return['total_rows'] = $total_found_rows_adres;
			$return['result'] = rs2arr($rs, 'adr_id');

			return $return;
		}

	}

	function setAdres($adr_id, $adr_data, $delete=false) {
		if (is_numeric($adr_id) && $delete) {

			$sql = "DELETE FROM crd_adres WHERE adr_id =". $adr_id;

		} elseif (is_array($adr_data)) {

			$fields = array();
			foreach ($adr_data as $adr_fld => $adr_val) {
				if (strlen(trim($adr_val)) > 0) {
					if ($adr_fld == 'adr_actief_tot') {
						$fields[] = $adr_fld ." = '".$this->_fmt_date($adr_val)."'";
					} else {
						$fields[] = $adr_fld ." = '".mysql_escape_string(trim($adr_val))."'";
					}
				} else {
					$fields[] = $adr_fld ." = NULL";
				}
			}

			if (is_numeric($adr_id)) {
				$sql = "UPDATE crd_adres SET ". implode(', ',$fields) .' WHERE adr_id = '.$adr_id;
			} else {
				$sql = "INSERT INTO crd_adres SET ". implode(', ',$fields);
			}
		}

		if ($sql) {
			$this->_db->execute($sql);
			if (is_numeric($adr_data['adr_bed_id'])) $this->setLastEditor($adr_data['adr_bed_id']);
		}

	}

	function searchBIKNACE($type, $search_string, $bed_id=null) {

		switch ($type) {

			case 'bik':
				$sql = "SELECT bik_id as id, bik_code as code, bik_beschrijving as beschrijving, IF(beb_bed_id,1,0) AS checked
						FROM crd_bikcode
						LEFT JOIN crd_bedrijf_bikcode ON beb_bik_id = bik_id AND beb_bed_id = ".$bed_id."
						WHERE bik_beschrijving LIKE '%".mysql_escape_string($search_string)."%'
						OR bik_code LIKE '".mysql_escape_string($search_string)."%'";
				$rs = $this->_db->query($sql);
				return rs2arr($rs);

				break;

			case 'nace':

				$sql = "SELECT nac_id as id, nac_code as code, nac_beschrijving as beschrijving, IF(bna_bed_id,1,0) AS checked
						FROM crd_nacecode
						LEFT JOIN crd_bedrijf_nacecode ON bna_nac_id = nac_id AND bna_bed_id = ".$bed_id."
						WHERE nac_beschrijving LIKE '%".mysql_escape_string($search_string)."%'
						OR nac_code LIKE '".mysql_escape_string($search_string)."%'";
				$rs = $this->_db->query($sql);
				return rs2arr($rs);

				break;

		}

	}

	function getBIKNACE($bed_id) {
		if (!is_numeric($bed_id)) return;

		$result = array();

		$sql = "SELECT bik_id as id, bik_code as code, bik_beschrijving as beschrijving, 'bik' as type
				FROM crd_bikcode
				INNER JOIN crd_bedrijf_bikcode ON beb_bik_id = bik_id AND beb_bed_id = ".$bed_id;
		$result = rs2arr($this->_db->query($sql));

		$sql = "SELECT nac_id as id, nac_code as code, nac_beschrijving as beschrijving, 'nace' as type
				FROM crd_nacecode
				INNER JOIN crd_bedrijf_nacecode ON bna_nac_id = nac_id AND bna_bed_id = ".$bed_id;
		$result = array_merge($result, rs2arr($this->_db->query($sql)));

		return $result;
	}

	function setBIKNACE($bed_id, $type, $id, $state=true) {
		if (!is_numeric($bed_id) || !is_numeric($id)) return;

		switch ($type) {

			case 'bik':
				if ($state) {
					$sql = "REPLACE INTO crd_bedrijf_bikcode (beb_bik_id, beb_bed_id) VALUES (".$id.",".$bed_id.")";
				} else {
					$sql = "DELETE FROM crd_bedrijf_bikcode WHERE beb_bik_id = ".$id." AND beb_bed_id = ".$bed_id;
				}
				break;

			case 'nace':
				if ($state) {
					$sql = "REPLACE INTO crd_bedrijf_nacecode (bna_nac_id, bna_bed_id) VALUES (".$id.",".$bed_id.")";
				} else {
					$sql = "DELETE FROM crd_bedrijf_nacecode WHERE bna_nac_id = ".$id." AND bna_bed_id = ".$bed_id;
				}

				break;
		}

		if ($sql) {
			$this->_db->execute($sql);
			$this->setLastEditor($bed_id);
		}

	}

	function setContactPersoon($bed_id, $data) {

		$bct_bed_id_connect = 'NULL';
		$bct_ctc_id = 'NULL';

		if (isset($data['bct_bed_id_connect']) && is_numeric($data['bct_bed_id_connect'])) {

			$bct_bed_id_connect = $data['bct_bed_id_connect'];

		} else {
			$fields = array();
			$fields['ctc_voorletters']		= "ctc_voorletters = '".	mysql_escape_string(trim($data['ctc_voorletters']))."'";
			$fields['ctc_voornaam']			= "ctc_voornaam = '".		mysql_escape_string(trim($data['ctc_voornaam']))."'";
			$fields['ctc_tussenvoegsels']	= "ctc_tussenvoegsels = '".	mysql_escape_string(trim($data['ctc_tussenvoegsels']))."'";
			$fields['ctc_naam']				= "ctc_naam = '".			mysql_escape_string(trim($data['ctc_naam']))."'";
			$fields['ctc_sexecode']			= "ctc_sexecode = '".		mysql_escape_string(trim($data['ctc_sexecode']))."'";
			$fields['ctc_adres']			= "ctc_adres = '".			mysql_escape_string(trim($data['ctc_adres']))."'";
			$fields['ctc_postcode']			= "ctc_postcode = '".		mysql_escape_string(trim($data['ctc_postcode']))."'";
			$fields['ctc_plaats']			= "ctc_plaats = '".			mysql_escape_string(trim($data['ctc_plaats']))."'";
			$fields['ctc_land']				= "ctc_land = '".			mysql_escape_string(trim($data['ctc_land']))."'";
			$fields['ctc_geboortedatum']	= "ctc_geboortedatum = '".	$this->_fmt_date($data['ctc_geboortedatum'])."'";
			$fields['ctc_titel']			= "ctc_titel = '".			mysql_escape_string(trim($data['ctc_titel']))."'";

			if (isset($data['ctc_id']) && is_numeric($data['ctc_id'])) {
				$sql = "UPDATE crd_contactpersoon SET ". implode(', ',$fields) .' WHERE ctc_id = '.$data['ctc_id'];
				$this->_db->execute($sql);
				$ctc_id = $data['ctc_id'];
			} else {
				$sql = "INSERT INTO crd_contactpersoon SET ". implode(', ',$fields);
				$this->_db->execute($sql);
				$ctc_id = $this->_db->lastInsertID();
			}

			$bct_ctc_id = $ctc_id;


		}

		$fields = array();
		$fields['bct_bed_id_connect']	= "bct_bed_id_connect = ". $bct_bed_id_connect;
		$fields['bct_fnc_id']			= "bct_fnc_id = ". (is_numeric($data['bct_fnc_id']) ? $data['bct_fnc_id'] : 'NULL');
		$fields['bct_is_bedrijfsleiding']	= "bct_is_bedrijfsleiding = 1";
		$fields['bct_datum_in_dienst']	= "bct_datum_in_dienst = '".$this->_fmt_date($data['bct_datum_in_dienst'])."'";
		$fields['bct_datum_uit_dienst']	= "bct_datum_uit_dienst = '".$this->_fmt_date($data['bct_datum_uit_dienst'])."'";
		$fields['bct_bvd_id']			= "bct_bvd_id = ". (is_numeric($data['bct_bvd_id']) ? $data['bct_bvd_id'] : 'NULL');
		$fields['bct_bedrag_bevoegd']	= "bct_bedrag_bevoegd = ".(is_numeric($data['bct_bedrag_bevoegd']) ? $data['bct_bedrag_bevoegd'] : 'NULL');

		if (isset($data['ctc_id']) && is_numeric($data['ctc_id']) && is_numeric($bct_ctc_id)) {

			$sql = "UPDATE crd_bedrijf_contactpersoon SET ". implode(', ',$fields) ."
					WHERE bct_bed_id = ". $bed_id."
					AND bct_ctc_id = ". $bct_ctc_id;

		} else {

			$fields['bct_bed_id']			= "bct_bed_id = ". $bed_id;
			$fields['bct_ctc_id']			= "bct_ctc_id = ". $bct_ctc_id;
			$sql = "REPLACE INTO crd_bedrijf_contactpersoon SET ". implode(', ',$fields);

		}


		$this->_db->execute($sql);
		$this->setLastEditor($bed_id);

	}

	function getContactPersoon($voornaam, $naam, $geboortedatum, $bed_id) {

		$wheres = array();
		if (strlen($voornaam) > 0)		$wheres[] = "ctc_voornaam LIKE '%".mysql_escape_string($voornaam)."%'";
		if (strlen($naam) > 0)			$wheres[] = "ctc_naam LIKE '%".mysql_escape_string($naam)."%'";
		if (strlen($geboortedatum) > 0) $wheres[] = "ctc_geboortedatum = '".$this->_fmt_date($geboortedatum)."%'";

		if (count($wheres)) {
			$sql = "SELECT crd_contactpersoon.*
					,	REPLACE(DATE_FORMAT(ctc_geboortedatum, '%d-%m-%Y'),'00-00-0000','') as ctc_geboortedatum
					FROM crd_contactpersoon
					LEFT JOIN crd_bedrijf_contactpersoon ON bct_bed_id = ".$bed_id." AND bct_ctc_id = ctc_id
					WHERE ". implode(' AND ', $wheres) ."
					AND bct_ctc_id IS NULL";

			return rs2arr($this->_db->query($sql));
		}
	}

	function getBedrijfContactpersoon($bed_id, $ctc_id=null, $bed_id_connect=null, $hide_uit_dienst=false) {
		if (!is_numeric($bed_id)) return;

		$sql = "SELECT *
				,	REPLACE(DATE_FORMAT(ctc_geboortedatum, '%d-%m-%Y'),'00-00-0000','') as ctc_geboortedatum
				,	REPLACE(DATE_FORMAT(bct_datum_in_dienst, '%d-%m-%Y'),'00-00-0000','') as bct_datum_in_dienst
				,	REPLACE(DATE_FORMAT(bct_datum_uit_dienst, '%d-%m-%Y'),'00-00-0000','') as bct_datum_uit_dienst
				FROM crd_bedrijf_contactpersoon
				LEFT JOIN crd_contactpersoon ON ctc_id = bct_ctc_id
				LEFT JOIN crd_functie ON fnc_id = bct_fnc_id
				LEFT JOIN crd_bevoegdheid ON bvd_id = bct_bvd_id
				WHERE bct_bed_id = ". $bed_id .
				(is_numeric($ctc_id) ? (' AND ctc_id = '.$ctc_id) : '')
				.(is_numeric($bed_id_connect) ? (' AND bct_bed_id_connect = '.$bed_id_connect) : '')
				.($hide_uit_dienst ? " AND (bct_datum_uit_dienst IS NULL OR bct_datum_uit_dienst = '0000-00-00' OR DATE_SUB(NOW(), INTERVAL 1 YEAR) <= bct_datum_uit_dienst)" : '')
				." ORDER BY fnc_order, ctc_naam";

		$ctc_items = array();
		$rs = $this->_db->query($sql);
		while ($r = $rs->row()) {
			if (is_numeric($r['bct_bed_id_connect'])) {
				$r['bedrijf'] = $this->getBedrijf($r['bct_bed_id_connect']);
			}
			$ctc_items[] = $r;

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

		return (is_numeric($ctc_id) || is_numeric($bed_id_connect)) ? current($ctc_items) : $ctc_items;
	}

	function setBedrijfContactPersoon($bed_id, $id, $state=true) {

		if (!is_numeric($bed_id) || !is_numeric($id)) return false;

		if ($state) {
			$sql = "INSERT INTO crd_bedrijf_contactpersoon (bct_bed_id, bct_ctc_id) VALUES (".$bed_id.",".$id.")";
		} else {
			$sql = "DELETE FROM crd_bedrijf_contactpersoon WHERE bct_bed_id = ".$bed_id." AND bct_id = ".$id;
		}

		$this->_db->execute($sql);
		$this->setLastEditor($bed_id);

	}

	function getConcernGegevenStructure($bed_id) {

		//-- get the POAP (Parent of all parents)
		$super_parent_id = $bed_id;
		while ($id = $this->_getConcernParent($super_parent_id)) {
			$super_parent_id = $id;
		}

		if (is_numeric($super_parent_id)) {
			$lijst = $this->_getConcernChildren($super_parent_id);

			return array($lijst);
		}

	}

		function _extractBedIdsFromConcernStructure($structure) {
			$bed_ids = array();
			foreach ($structure as $s) {
				$bed_ids[] = $s['id'];
				if ($s['children']) {
					$bed_ids = array_merge($bed_ids, $this->_extractBedIdsFromConcernStructure($s['children']));
				}
			}

			return $bed_ids;
		}

		function _getConcernParent($bed_id) {

			$sisters = $this->_getConcernSisters($bed_id);
			$sisters[] = $bed_id;

			$sql = "SELECT
						IF (con_bed_id IN (".implode(',',$sisters).") AND rel_type = ".CRD_RELATIE_TYPE_PARENT.", con_bed_hoort_bij, con_bed_id) as bed_id_parent
					FROM crd_concerngegeven
					INNER JOIN crd_concern_relatie ON rel_id = con_rel_id
					WHERE
						(con_bed_id IN (".implode(',',$sisters).") AND rel_type = ".CRD_RELATIE_TYPE_PARENT.")
						OR
						(con_bed_hoort_bij IN (".implode(',',$sisters).") AND rel_type = ".CRD_RELATIE_TYPE_CHILD.")";

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

			return is_numeric($rs->Row('bed_id_parent')) ? $rs->Row('bed_id_parent') : false;

		}

		function _getConcernSisters($bed_ids, $bed_ids_ignore=array()) {

			if (is_numeric($bed_ids)) $bed_ids = array($bed_ids);

			$sql = "SELECT
						IF (con_bed_id IN (".implode(',',$bed_ids).")
						".(count($bed_ids_ignore) > 0 ? " AND con_bed_id NOT IN (".implode(',',$bed_ids_ignore).")" : "").", con_bed_hoort_bij, con_bed_id) as bed_id_sister
					FROM crd_concerngegeven
					INNER JOIN crd_concern_relatie ON rel_id = con_rel_id
					WHERE rel_type = ".CRD_RELATIE_TYPE_SISTER."
					AND
						(
							con_bed_id IN (".implode(',',$bed_ids).")
							OR
							con_bed_hoort_bij IN (".implode(',',$bed_ids).")
						)
						".(count($bed_ids_ignore) > 0 ? " AND con_bed_id NOT IN (".implode(',',$bed_ids_ignore).") AND con_bed_hoort_bij NOT IN (".implode(',',$bed_ids_ignore).")" : "")."
						";

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

			if (count($sister_ids) ) {

				$sister_ids = array_merge($sister_ids, $this->_getConcernSisters($sister_ids, $bed_ids));

			}

			return $sister_ids;
		}

		function _getConcernChildren($bed_id) {
			if (!is_numeric($bed_id)) {
				return;
			}

			$sql = "SELECT
						IF (con_bed_id = ".$bed_id." AND rel_type = ".CRD_RELATIE_TYPE_CHILD.", con_bed_hoort_bij, con_bed_id) as bed_id_child
					FROM crd_concerngegeven
					INNER JOIN crd_concern_relatie ON rel_id = con_rel_id
					WHERE
						(con_bed_id = ".$bed_id." AND rel_type = ".CRD_RELATIE_TYPE_CHILD.")
						OR
						(con_bed_hoort_bij = ".$bed_id." AND rel_type = ".CRD_RELATIE_TYPE_PARENT.")";

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

			if (count($bed_id_childs)) {

				$bed_id_sisters = $this->_getConcernSisters($bed_id_childs);
				$the_child_ids = array_merge($bed_id_childs, $bed_id_sisters);

			} else {
				$the_child_ids = $bed_id_childs;
			}

			$the_child_ids = array_unique($the_child_ids);

			$return = array('id'	=> $bed_id
						,	'name'	=> $this->getBedrijfsNaam($bed_id)
						,	'data'	=> $this->getBedrijf($bed_id)
						);

			foreach ($the_child_ids as $child_id) {
				$children[] = $this->_getConcernChildren($child_id);
			}

			$return['children'] = $children;

			return $return;

		}

	function getGrootBedrijfInStructure($bed_id) {

		if ($bed_id_parent = $this->_getConcernParent($bed_id)) {
			$bedrijf_parent = $this->getBedrijf($bed_id_parent);

			if ($bedrijf_parent['bed_grootbedrijf_code']) {
				return $bedrijf_parent;
			} else {

				return $this->getGrootBedrijfInStructure($bed_id_parent);

			}
		}

		return false;
	}


	function getConcernGegeven($bed_id, $con_id=null) {
		if (!is_numeric($bed_id)) return;


		if (is_numeric($con_id)) {
			//--- get specific concerngegeven

			$sql = "SELECT
						crd_concerngegeven.*
					,	rel.rel_name as rel_name
					,	rel.rel_id as rel_id
					,	rel_contra.rel_name as rel_name_contra
					,	rel_contra.rel_id as rel_id_contra
 					FROM crd_concerngegeven
					LEFT JOIN crd_concern_relatie as rel ON rel.rel_id = con_rel_id
					LEFT JOIN crd_concern_relatie as rel_contra ON rel_contra.rel_id = rel.rel_id_contra
					WHERE con_id = ".$con_id;
			$rs = $this->_db->query($sql);
			$r = $rs->Row();

			if ($r['con_bed_id'] != $bed_id) {
				//-- 'contra relatie'
				$id = $r['con_bed_id'];
				$r['con_relatie'] = $r['rel_name_contra'];
				$r['con_relatie_id'] = $r['rel_id_contra'];
			} else {
				$id = $r['con_bed_hoort_bij'];
				$r['con_relatie'] = $r['rel_name'];
				$r['con_relatie_id'] = $r['rel_id'];
			}

			$r = array_merge($r, $this->getBedrijf($id));

			return $r;

		} else {
			//--- get all concerngegeven of specified bedrijf

			$sql = "SELECT
						crd_concerngegeven.*
					,	rel.rel_name as rel_name
					,	rel_contra.rel_name as rel_name_contra
					,	rel_contra.rel_type as rel_type_contra
					,	rel.rel_type as rel_type
					FROM crd_concerngegeven
					INNER JOIN crd_concern_relatie as rel ON rel.rel_id = con_rel_id
					LEFT JOIN crd_concern_relatie as rel_contra ON rel_contra.rel_id = rel.rel_id_contra
					WHERE (con_bed_id = ".$bed_id." OR con_bed_hoort_bij = ".$bed_id.")
					AND con_actief_tot IS NULL";

			$result = array();
			$rs = $this->_db->query($sql);
			while ($r = $rs->row()) {

				if ($bed_id != $r['con_bed_id']) {

					if ($r['rel_type'] == CRD_RELATIE_TYPE_SUPER) {
						//--- don't show relations of type 'uiteindelijke moedermaatschappij' when bedrijf is not the 'owner' of the relation
						$rs->moveNext();
						continue;
					}

					$id = $r['con_bed_id'];
					$r['con_relatie'] = $r['rel_name_contra'];
				} else {
					$id = $r['con_bed_hoort_bij'];
					$r['con_relatie'] = $r['rel_name'];
				}

				$r = array_merge($r, $this->getBedrijf($id));

				$result[] = $r;

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

			return $result;

		}
	}



	function getConcernRelatieContra($rel_id) {
		if (!is_numeric($rel_id)) return;
		$rs = $this->_db->query("SELECT rel_id_contra FROM crd_concern_relatie WHERE rel_id = ".$rel_id);
		return $rs->Row('rel_id_contra');

	}

/*	function getConcernStatusContra($status) {

		$trl = array('moedermaatschappij'	=> 'dochter'
				,	'dochter'				=> 'moedermaatschappij'
				,	'holding'				=> 'werkmaatschappij'
				,	'werkmaatschappij'		=> 'holding'
				);
		return isset($trl[$status]) ? $trl[$status] : null;

	}
*/
/*	function getConcernStatusAbove($status) {
		$trl = array('moedermaatschappij'	=> true
				,	'dochter'				=> false
				,	'holding'				=> true
				,	'werkmaatschappij'		=> false
				);
		return isset($trl[$status]) ? $trl[$status] : null;
	}
*/
	function setConcernGegeven($con_id, $bed_id, $bed_id_hoort_bij, $con_aandelen_percentage, $con_rel_id) {
		if (!is_numeric($bed_id)) return;

		if (is_numeric($con_id)) {

			$current_data = $this->getConcernGegeven($bed_id, $con_id);

			if ($current_data['con_bed_id'] != $bed_id) {
				$correct_rel_id = $this->getConcernRelatieContra($con_rel_id);
			} else {
				$correct_rel_id = $con_rel_id;
			}

			//-- update
			$sql = "UPDATE crd_concerngegeven SET
						con_rel_id			= '".$correct_rel_id."'
					,	con_aandelen_percentage	= ".(int)$con_aandelen_percentage."
					WHERE con_id = ".$con_id;

		} else {
			//--- insert
			if (is_numeric($bed_id_hoort_bij) && $bed_id != $bed_id_hoort_bij) {

				//-- controle of bedrijf al voorkomt in concern-structuur

				$structure_ids = $this->_extractBedIdsFromConcernStructure($this->getConcernGegevenStructure($bed_id));

				if (!in_array($bed_id_hoort_bij, $structure_ids)) {
					$sql = "INSERT INTO crd_concerngegeven SET
								con_bed_id			= ".$bed_id."
							,	con_bed_hoort_bij	= ".$bed_id_hoort_bij."
							,	con_rel_id			= ".$con_rel_id."
							,	con_aandelen_percentage	= ".(int)$con_aandelen_percentage."
							,	con_actief_tot		= NULL";

				} else {
					$this->triggerError(CRD_ERR_BEDRIJF_EXIST_IN_STRUCTURE, 'Bedrijf komt al voor in de structuur');
					return false;
				}


			}
		}

		if ($sql) {
			$this->_db->execute($sql);
			$this->setLastEditor($bed_id);
		}

		return true;
	}

	function deleteConcernGegeven($con_id) {
		if (!is_numeric($con_id)) return;

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

	}

	function setKapitaal($bed_id, $kap_id, $kap_bedrag_maatschappelijk, $kap_bedrag_geplaatst, $kap_bedrag_gestort, $kap_bedrag_commanditair, $kap_datum_tot) {
		if (!is_numeric($bed_id)) return;

		if (is_numeric($kap_id)) {

			$sql = "UPDATE crd_kapitaal SET
						kap_bedrag_maatschappelijk	= ".(is_numeric($kap_bedrag_maatschappelijk) ? $kap_bedrag_maatschappelijk : 'NULL')."
					,	kap_bedrag_geplaatst		= ".(is_numeric($kap_bedrag_geplaatst) ? $kap_bedrag_geplaatst : 'NULL')."
					,	kap_bedrag_gestort			= ".(is_numeric($kap_bedrag_gestort) ? $kap_bedrag_gestort : 'NULL')."
					,	kap_bedrag_commanditair		= ".(is_numeric($kap_bedrag_commanditair) ? $kap_bedrag_commanditair : 'NULL')."
					,	kap_datum_tot				= ".($kap_datum_tot ? "'".$this->_fmt_date($kap_datum_tot) ."'": 'NULL')."
					WHERE kap_id = ".$kap_id;

		} else {
			$sql = "INSERT INTO crd_kapitaal SET
						kap_bed_id					= ".$bed_id."
					,	kap_bedrag_maatschappelijk	= ".(is_numeric($kap_bedrag_maatschappelijk) ? $kap_bedrag_maatschappelijk : 'NULL')."
					,	kap_bedrag_geplaatst		= ".(is_numeric($kap_bedrag_geplaatst) ? $kap_bedrag_geplaatst : 'NULL')."
					,	kap_bedrag_gestort			= ".(is_numeric($kap_bedrag_gestort) ? $kap_bedrag_gestort : 'NULL')."
					,	kap_bedrag_commanditair		= ".(is_numeric($kap_bedrag_commanditair) ? $kap_bedrag_commanditair : 'NULL')."
					,	kap_datum_tot				= ".($kap_datum_tot ? $this->_fmt_date($kap_datum_tot) : 'NULL');
		}

		$this->_db->execute($sql);
		$this->setLastEditor($bed_id);

	}

	function deleteKapitaal($kap_id) {
		if (!is_numeric($kap_id)) return;

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

	}


	function getKapitaal($bed_id, $active_only=false) {
		if (!is_numeric($bed_id)) return;

		$sql = "SELECT * FROM crd_kapitaal
				WHERE kap_bed_id = ". $bed_id ."
				".($active_only ? ' AND kap_datum_tot IS NULL':'');

		return rs2arr($this->_db->query($sql));

	}

	function setKredietlimiet($bed_id, $kli_id=null, $kli_datum, $kli_kredietlimiet, $state=true) {
		if (!is_numeric($bed_id)) return;

		if (!$state && is_numeric($kli_id)) {
			$sql = "DELETE FROM crd_kredietlimiet WHERE kli_id = ".$kli_id;
		} else {
			$sql = "INSERT INTO	crd_kredietlimiet SET
						kli_bed_id		= ".$bed_id."
					,	kli_datum		= '". $this->_fmt_date($kli_datum)."'
					,	kli_kredietlimiet = '".mysql_escape_string(trim($kli_kredietlimiet))."'";

		}

		$this->_db->execute($sql);
		$this->setLastEditor($bed_id);
	}

	function getKredietlimiet($bed_id) {
		if (!is_numeric($bed_id)) return;

		$sql = "SELECT
					crd_kredietlimiet.*
				,	kli_datum as kli_datum_orig
				,	REPLACE(DATE_FORMAT(kli_datum, '%d-%m-%Y'),'00-00-0000','') as kli_datum
				FROM crd_kredietlimiet WHERE kli_bed_id = ". $bed_id ." ORDER BY kli_datum_orig";

		return rs2arr($this->_db->query($sql));
	}

	function setPersoneel($bed_id, $bps_id, $data, $state=true) {
		if (!is_numeric($bed_id)) return;

		if (!$state && is_numeric($bps_id)) {
			$sql = "DELETE FROM crd_bedrijfpersoneel WHERE bps_id = ".$bps_id;
		} elseif (is_numeric($bps_id)) {
			$sql = "UPDATE crd_bedrijfpersoneel SET
						bps_datum				= '".$this->_fmt_date($data['bps_datum'])."'
					,	bps_personeel_kvk		= ".(is_numeric($data['bps_personeel_kvk']) ? $data['bps_personeel_kvk'] : 'NULL')."
					,	bps_personeel_totaal	= ".(is_numeric($data['bps_personeel_totaal']) ? $data['bps_personeel_totaal'] : 'NULL')."
					,	bps_personeel_fulltime	= ".(is_numeric($data['bps_personeel_fulltime']) ? $data['bps_personeel_fulltime'] : 'NULL')."
					,	bps_personeel_parttime	= ".(is_numeric($data['bps_personeel_parttime']) ? $data['bps_personeel_parttime'] : 'NULL')."
					,	bps_concern				= ".(is_numeric($data['bps_concern']) ? $data['bps_concern'] : 'NULL')."
					,	bps_bijzondere_mutaties	= '".mysql_escape_string(trim($data['bps_bijzondere_mutaties']))."'
					,	bps_bm_verloopdatum		= ".($data['bps_bm_verloopdatum'] ? "'".$this->_fmt_date($data['bps_bm_verloopdatum'])."'" : 'NULL') ."
					WHERE bps_id = ".$bps_id;

		} else {

			if (is_numeric($data['bps_personeel_kvk']) || is_numeric($data['bps_personeel_totaal']) || is_numeric($data['bps_personeel_fulltime']) || is_numeric($data['bps_personeel_parttime']) || is_numeric($data['bps_concern'])) {

				$sql = "INSERT INTO crd_bedrijfpersoneel SET
							bps_bed_id				= ".$bed_id."
						,	bps_datum				= '".$this->_fmt_date($data['bps_datum'])."'
						,	bps_personeel_kvk		= ".(is_numeric($data['bps_personeel_kvk']) ? $data['bps_personeel_kvk'] : 'NULL')."
						,	bps_personeel_totaal	= ".(is_numeric($data['bps_personeel_totaal']) ? $data['bps_personeel_totaal'] : 'NULL')."
						,	bps_personeel_fulltime	= ".(is_numeric($data['bps_personeel_fulltime']) ? $data['bps_personeel_fulltime'] : 'NULL')."
						,	bps_personeel_parttime	= ".(is_numeric($data['bps_personeel_parttime']) ? $data['bps_personeel_parttime'] : 'NULL')."
						,	bps_concern				= ".(is_numeric($data['bps_concern']) ? $data['bps_concern'] : 'NULL')."
						,	bps_bijzondere_mutaties	= '".mysql_escape_string(trim($data['bps_bijzondere_mutaties']))."'
						,	bps_bm_verloopdatum		= ".($data['bps_bm_verloopdatum'] ? "'".$this->_fmt_date($data['bps_bm_verloopdatum'])."'" : 'NULL');
			}
		}

		if ($sql) {
			$this->_db->execute($sql);
			$this->setLastEditor($bed_id);
		}

	}

	function getPersoneel($bed_id) {
		if (!is_numeric($bed_id)) return;

		$sql = "SELECT *
				,	bps_datum as bps_datum_orig
				,	REPLACE(DATE_FORMAT(bps_datum, '%d-%m-%Y'),'00-00-0000','') as bps_datum
				,	REPLACE(DATE_FORMAT(bps_bm_verloopdatum, '%d-%m-%Y'),'00-00-0000','') as bps_bm_verloopdatum
				FROM crd_bedrijfpersoneel
				WHERE bps_bed_id = ".$bed_id ."
				ORDER BY bps_datum_orig";

		return rs2arr($this->_db->query($sql));

	}

	function getOnroerendgoedinformatie($bed_id) {
		if (!is_numeric($bed_id)) return;

		$sql = "SELECT *
				FROM crd_onroerendgoedinformatie
				WHERE onr_bed_id = ".$bed_id;

		return rs2arr($this->_db->query($sql));

	}

	function setOnroerendgoedinformatie($bed_id, $onr_id, $data, $state=true) {
		if (!is_numeric($bed_id)) return;

		if (!$state && is_numeric($onr_id)) {
			$sql = "DELETE FROM crd_onroerendgoedinformatie WHERE onr_id = ".$onr_id;
		} elseif (is_numeric($onr_id)) {
			$sql = "UPDATE crd_onroerendgoedinformatie SET
						onr_onroerend_goed			= '".mysql_escape_string(trim($data['onr_onroerend_goed']))."'
					,	onr_hypothecaire_belasting	= ".($data['onr_hypothecaire_belasting'] ? 1 : 0)."
					,	onr_hypotheek_bedrag		= ".(is_numeric($data['onr_hypotheek_bedrag']) ? $data['onr_hypotheek_bedrag'] : 'NULL')."
					,	onr_oppervlakte				= ".(is_numeric($data['onr_oppervlakte']) ? $data['onr_oppervlakte'] : 'NULL')."
					WHERE onr_id = ".$onr_id;
		} else {
			$sql = "INSERT INTO crd_onroerendgoedinformatie SET
						onr_bed_id					= ".$bed_id."
					,	onr_onroerend_goed			= '".mysql_escape_string(trim($data['onr_onroerend_goed']))."'
					,	onr_hypothecaire_belasting	= ".($data['onr_hypothecaire_belasting'] ? 1 : 0)."
					,	onr_hypotheek_bedrag		= ".(is_numeric($data['onr_hypotheek_bedrag']) ? $data['onr_hypotheek_bedrag'] : 'NULL')."
					,	onr_oppervlakte				= ".(is_numeric($data['onr_oppervlakte']) ? $data['onr_oppervlakte'] : 'NULL');
		}

		$this->_db->execute($sql);
		$this->setLastEditor($bed_id);

	}

	function setImportExport($bed_id, $iex_id, $data, $state=true) {
		if (!is_numeric($bed_id)) return;

		if (!$state && is_numeric($iex_id)) {
			$sql = "DELETE FROM crd_import_export WHERE iex_id = ".$iex_id;
		} elseif (is_numeric($onr_id)) {
			$sql = "UPDATE crd_import_export SET
						iex_lnd_id	= ".mysql_escape_string(trim($data['iex_lnd_id']))."
					,	iex_type	= '".$data['iex_type']."'
					WHERE iex_id = ".$iex_id;
		} else {
			$sql = "REPLACE INTO crd_import_export SET
						iex_bed_id	= ".$bed_id."
					,	iex_lnd_id	= ".mysql_escape_string(trim($data['iex_lnd_id']))."
					,	iex_type	= '".$data['iex_type']."'";
		}

		$this->_db->execute($sql);
		$this->setLastEditor($bed_id);
	}

	function getImportExport($bed_id) {
		if (!is_numeric($bed_id)) return;

		$sql = "SELECT *
				FROM crd_import_export
				INNER JOIN crd_land ON lnd_id = iex_lnd_id
				WHERE iex_bed_id = ".$bed_id;

		return rs2arr($this->_db->query($sql));

	}

	function setBedrijfBank($bed_id, $bbk_id, $data, $state=true) {
		if (!is_numeric($bed_id)) return;

		if (!$state && is_numeric($bbk_id)) {
			$sql = "DELETE FROM crd_bedrijf_bank WHERE bbk_id = ".$bbk_id;
		} elseif (is_numeric($bbk_id)) {
			$sql = "UPDATE crd_bedrijf_bank SET
						bbk_bnk_id				= ".$data['bbk_bnk_id']."
					,	bbk_nummer				= '".mysql_escape_string($data['bbk_nummer'])."'
					,	bbk_kredietfaciliteit	= ".($data['bbk_kredietfaciliteit'] ? 1 : 0)."
					,	bbk_krediet_hoogte		= ".(float)$data['bbk_krediet_hoogte']."
					,	bbk_actief_tot			= ".($data['bbk_actief_tot'] ? "'".$this->_fmt_date($data['bbk_actief_tot'])."'" : 'NULL'). "
					WHERE bbk_id = ".$bbk_id;
		} else {
			$sql = "INSERT INTO crd_bedrijf_bank SET
						bbk_bed_id				= ".$bed_id."
					,	bbk_bnk_id				= ".$data['bbk_bnk_id']."
					,	bbk_nummer				= '".mysql_escape_string(trim($data['bbk_nummer']))."'
					,	bbk_kredietfaciliteit	= ".($data['bbk_kredietfaciliteit'] ? 1 : 0)."
					,	bbk_krediet_hoogte		= ".(float)$data['bbk_krediet_hoogte']."
					,	bbk_actief_tot			= ".($data['bbk_actief_tot'] ? "'".$this->_fmt_date($data['bbk_actief_tot'])."'" : 'NULL');
		}

		$this->_db->execute($sql);
		$this->setLastEditor($bed_id);
	}

	function getBedrijfBank($bed_id) {
		if (!is_numeric($bed_id)) return;

		$sql = "SELECT *
				FROM crd_bedrijf_bank
				INNER JOIN crd_bank ON bnk_id = bbk_bnk_id
				WHERE bbk_bed_id = ".$bed_id;

		return rs2arr($this->_db->query($sql));
	}

	function getMerk($bed_id, $mrk_beschrijving='') {
		if (is_numeric($bed_id)) {
			if (strlen($mrk_beschrijving) > 0) {
				$sql = "SELECT mrk_id, mrk_beschrijving FROM crd_merk WHERE mrk_bed_id = ".$bed_id ." AND mrk_beschrijving = '".mysql_escape_string($mrk_beschrijving)."'";
			} else {
				$sql = "SELECT mrk_id, mrk_beschrijving FROM crd_merk WHERE mrk_bed_id = ".$bed_id;
			}
			$rs = $this->_db->query($sql);
			return rs2arr($rs, 'mrk_id');
		}
	}

	function setMerk($mrk_id, $bed_id, $mrk_beschrijving, $delete=false) {

		if (is_numeric($mrk_id)) {

			if ($delete) {
				$sql = "DELETE FROM crd_merk WHERE mrk_id =".$mrk_id;
			} else {
				$sql = "UPDATE crd_merk SET mrk_beschrijving = '".mysql_escape_string(trim($mrk_beschrijving))."' WHERE mrk_id =".$mrk_id;
			}
			$this->_db->execute($sql);

		} elseif (is_numeric($bed_id)) {
			$sql = "INSERT INTO crd_merk SET mrk_bed_id = ".$bed_id.", mrk_beschrijving = '".mysql_escape_string(trim($mrk_beschrijving))."'";
			$this->_db->execute($sql);
			$this->setLastEditor($bed_id);
		}

	}

	function getAccountant($acc_id=null, $acc_naam=null, $acc_plaats=null) {

		if (is_numeric($acc_id)) {
			$sql = "SELECT * FROM crd_accountant WHERE acc_id = ".$acc_id;
			return current(rs2arr($this->_db->query($sql)));
		} elseif (strlen(trim($acc_naam)) > 0 || strlen(trim($acc_plaats)) > 0) {
			//-- zoeken
			$search = array();
			if ($acc_naam) $search[] = " acc_naam LIKE '%".mysql_escape_string($acc_naam)."%'";
			if ($acc_plaats) $search[] = " acc_plaats LIKE '%".mysql_escape_string($acc_plaats)."%'";

			$sql = "SELECT * FROM crd_accountant
					WHERE ". implode(' AND ', $search);

			return rs2arr($this->_db->query($sql));
		}

	}

	function setAccountant($bed_id, $acc_id, $acc_naam='', $acc_plaats='', $state=true) {
		if (is_numeric($bed_id) && is_numeric($acc_id)) {
			if ($state) {
				$sql = "UPDATE crd_bedrijf SET bed_acc_id = ".$acc_id." WHERE bed_id = ".$bed_id;
			} else {
				$sql = "UPDATE crd_bedrijf SET bed_acc_id = NULL WHERE bed_id = ".$bed_id;
			}
			$this->_db->execute($sql);
		} elseif (strlen($acc_naam) > 0) {

			$sql = "INSERT INTO crd_accountant SET
						acc_naam   = '".mysql_escape_string(trim($acc_naam))."'
					,	acc_plaats = '".mysql_escape_string(trim($acc_plaats))."'";
			$this->_db->execute($sql);
			$new_acc_id = $this->_db->lastInsertID();
			$this->setAccountant($bed_id, $new_acc_id);

		}
		$this->setLastEditor($bed_id);

	}

	function getOmzetWinst($bed_id) {
		if (!is_numeric($bed_id)) return;

		$sql = "SELECT *
				,	date_format(ozw_datum, '%d-%m-%Y') ozw_datum
				FROM crd_omzetwinst
				WHERE ozw_bed_id = ".$bed_id ."
				ORDER BY ozw_jaar, ozw_datum";

		return rs2arr($this->_db->query($sql));

	}

	function setOmzetWinst($bed_id, $ozw_id, $data, $state=true) {
		if (!is_numeric($bed_id)) return;

		if (!$state && is_numeric($ozw_id)) {
			$sql = "DELETE FROM crd_omzetwinst WHERE ozw_id = ".$ozw_id;
		} elseif (is_numeric($ozw_id)) {
			$sql = "UPDATE crd_omzetwinst SET
						ozw_datum			= ".($data['ozw_datum'] ? "'".$this->_fmt_date($data['ozw_datum'])."'" : 'NULL')."
					,	ozw_jaar			= YEAR(ozw_datum)
					,	ozw_winst 			= ".($data['ozw_winst'] ? $data['ozw_winst'] : 'NULL')."
					,	ozw_omzet 			= ".($data['ozw_omzet'] ? $data['ozw_omzet'] : 'NULL')."
					,	ozw_brutowinst		= ".($data['ozw_brutowinst'] ? $data['ozw_brutowinst'] : 'NULL')."
					,	ozw_type 			= '".mysql_escape_string($data['ozw_type'])."'
					WHERE ozw_id = ".$ozw_id;
		} else {
			$sql = "INSERT INTO crd_omzetwinst SET
						ozw_bed_id			= ".$bed_id."
					,	ozw_datum			= ".($data['ozw_datum'] ? "'".$this->_fmt_date($data['ozw_datum'])."'" : 'NULL')."
					,	ozw_jaar			= YEAR(ozw_datum)
					,	ozw_winst 			= ".($data['ozw_winst'] ? $data['ozw_winst'] : 'NULL')."
					,	ozw_omzet 			= ".($data['ozw_omzet'] ? $data['ozw_omzet'] : 'NULL')."
					,	ozw_brutowinst		= ".($data['ozw_brutowinst'] ? $data['ozw_brutowinst'] : 'NULL')."
					,	ozw_type 			= '".mysql_escape_string($data['ozw_type'])."'";
		}

		$this->_db->execute($sql);
		$this->setLastEditor($bed_id);

	}


	function getBedrijfScore($bed_id) {
		if (!is_numeric($bed_id)) return;

		$sql = "SELECT *
				FROM crd_bedrijfscore
				WHERE bsc_bed_id = ".$bed_id ."
				ORDER BY bsc_datum";

		return rs2arr($this->_db->query($sql));

	}

	function setBedrijfScore($bed_id, $bsc_id, $data, $state=true) {
		if (!is_numeric($bed_id)) return;


		if (!$state && is_numeric($bsc_id)) {
			$sql = "DELETE FROM crd_bedrijfscore WHERE bsc_id = ".$bsc_id;
		} elseif (is_numeric($bsc_id)) {
			$sql = "UPDATE crd_bedrijfscore SET
						bsc_datum				= ".($data['bsc_datum'] ? "'".$this->_fmt_date($data['bsc_datum'])."'" : 'NULL')."
					,	bsc_trend				= '".mysql_escape_string($data['bsc_trend'])."'
					,	bsc_betalingservaring	= '".mysql_escape_string($data['bsc_betalingservaring'])."'
					,	bsc_solvabiliteit		= '".mysql_escape_string($data['bsc_solvabiliteit'])."'
					,	bsc_rentabiliteit		= '".mysql_escape_string($data['bsc_rentabiliteit'])."'
					,	bsc_liquiditeit			= '".mysql_escape_string($data['bsc_liquiditeit'])."'
					,	bsc_score				= '".mysql_escape_string($data['bsc_score'])."'
					,	bsc_krediet_beoordeling	= '".mysql_escape_string($data['bsc_krediet_beoordeling'])."'
					,	bsc_krediet_beoordeling_label	= '".mysql_escape_string($data['bsc_krediet_beoordeling_label'])."'
					,	bsc_balans				= '".mysql_escape_string($data['bsc_balans'])."'
					,	bsc_beschrijving 		= '".mysql_escape_string($data['bsc_beschrijving'])."'
					WHERE bsc_id = ".$bsc_id;
		} else {
			$sql = "INSERT INTO crd_bedrijfscore SET
						bsc_bed_id				= ".$bed_id."
					,	bsc_datum				= ".($data['bsc_datum'] ? "'".$this->_fmt_date($data['bsc_datum'])."'" : 'NULL')."
					,	bsc_trend				= '".mysql_escape_string($data['bsc_trend'])."'
					,	bsc_betalingservaring	= '".mysql_escape_string($data['bsc_betalingservaring'])."'
					,	bsc_solvabiliteit		= '".mysql_escape_string($data['bsc_solvabiliteit'])."'
					,	bsc_rentabiliteit		= '".mysql_escape_string($data['bsc_rentabiliteit'])."'
					,	bsc_liquiditeit			= '".mysql_escape_string($data['bsc_liquiditeit'])."'
					,	bsc_score				= '".mysql_escape_string($data['bsc_score'])."'
					,	bsc_krediet_beoordeling	= '".mysql_escape_string($data['bsc_krediet_beoordeling'])."'
					,	bsc_krediet_beoordeling_label	= '".mysql_escape_string($data['bsc_krediet_beoordeling_label'])."'
					,	bsc_balans				= '".mysql_escape_string($data['bsc_balans'])."'
					,	bsc_beschrijving 		= '".mysql_escape_string($data['bsc_beschrijving'])."'";
		}

		$this->_db->execute($sql);
		$this->setLastEditor($bed_id);

	}

	function deleteAandeelhouder($ban_id) {
		$sql = "DELETE FROM crd_bedrijf_aandeelhouder WHERE ban_id = ".$ban_id;
		$this->_db->execute($sql);
	}

	function setAandeelhouder($bed_id, $and_id = null, $bed_id_connect = null, $data) {
		if (!is_numeric($bed_id)) return;

		$ban_and_id = 'NULL';
		$ban_bed_id_connect = 'NULL';

		if (is_numeric($and_id)) {

			$ban_and_id = $and_id;
			$sql = "UPDATE crd_aandeelhouder SET
						and_naam	= '".mysql_escape_string(trim($data['and_naam']))."'
					,	and_adres	= '".mysql_escape_string(trim($data['and_adres']))."'
					,	and_postcode= '".mysql_escape_string(trim($data['and_postcode']))."'
					,	and_plaats	= '".mysql_escape_string(trim($data['and_plaats']))."'
					WHERE and_id = ".$and_id;

			$this->_db->execute($sql);
		} elseif (is_numeric($bed_id_connect)) {

			$ban_bed_id_connect = $bed_id_connect;

		} elseif (isset($data['ban_bed_id_connect']) && is_numeric($data['ban_bed_id_connect'])) {

			$ban_bed_id_connect = $data['ban_bed_id_connect'];

		} elseif ($data['and_naam']) {
			$sql = "INSERT INTO crd_aandeelhouder SET
						and_naam	= '".mysql_escape_string(trim($data['and_naam']))."'
					,	and_adres	= '".mysql_escape_string(trim($data['and_adres']))."'
					,	and_postcode= '".mysql_escape_string(trim($data['and_postcode']))."'
					,	and_plaats	= '".mysql_escape_string(trim($data['and_plaats']))."'";
			$this->_db->execute($sql);
			$ban_and_id = $this->_db->lastInsertID();
		}

		if (is_numeric($ban_and_id) || is_numeric($ban_bed_id_connect)) {

			$sql = "REPLACE INTO crd_bedrijf_aandeelhouder SET
						ban_bed_id		= ".$bed_id."
					,	ban_and_id		= ".$ban_and_id."
					,	ban_bed_id_connect	= ".$ban_bed_id_connect."
					,	ban_uiteindelijk	= ".($data['ban_uiteindelijk'] ? 1 : 0)."
					,	ban_percentage	= ".($data['ban_percentage'] ? (int)$data['ban_percentage'] : 'NULL')."
					,	ban_actief_vanaf= ".($data['ban_actief_vanaf'] ? "'".$this->_fmt_date($data['ban_actief_vanaf'])."'" : 'NULL')."
					,	ban_actief_tot	= ".($data['ban_actief_tot'] ? "'".$this->_fmt_date($data['ban_actief_tot'])."'" : 'NULL');

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

		//$this->bronUpdate(CRD_BRON_TYPE_AANDEELHOUDER, 'insert', $bed_id, $and_id);
		$this->setLastEditor($bed_id);


	}

	function getAandeelhouder($bed_id, $and_id=null, $bed_id_connect=null, $and_naam=null, $and_adres=null, $and_postcode=null, $and_plaats=null, $ban_uiteindelijk=null) {

		if (is_numeric($bed_id)) {

			if (is_numeric($and_id)) {
				$sql = "SELECT *
						, date_format(ban_actief_vanaf, '%d-%m-%Y') ban_actief_vanaf
						, date_format(ban_actief_tot, '%d-%m-%Y') ban_actief_tot
						FROM crd_aandeelhouder
						LEFT JOIN crd_bedrijf_aandeelhouder ON ban_and_id = and_id AND ban_bed_id = ".$bed_id."
						WHERE and_id = ".$and_id;
				return current(rs2arr($this->_db->query($sql)));
			} elseif (is_numeric($bed_id_connect)) {

				$sql = "SELECT *
						, date_format(ban_actief_vanaf, '%d-%m-%Y') ban_actief_vanaf
						, date_format(ban_actief_tot, '%d-%m-%Y') ban_actief_tot
						FROM crd_bedrijf_aandeelhouder
						WHERE ban_bed_id = ".$bed_id."
						AND ban_bed_id_connect = ".$bed_id_connect;
				$adh = rs2arr($this->_db->query($sql), false, true);
				if (is_numeric($adh['ban_bed_id_connect'])) {
					$adh['bedrijf'] = $this->getBedrijf($adh['ban_bed_id_connect']);
				}

				return $adh;

			} elseif ($and_naam || $and_adres || $and_postcode || $and_plaats) {

				$search = array();
				if ($and_naam) $search[] = "and_naam LIKE '%".mysql_escape_string($and_naam)."%'";
				if ($and_adres) $search[] = "and_adres LIKE '%".mysql_escape_string($and_adres)."%'";
				if ($and_postcode) $search[] = "and_postcode LIKE '%".mysql_escape_string($and_postcode)."%'";
				if ($and_plaats) $search[] = "and_plaats LIKE '%".mysql_escape_string($and_plaats)."%'";


				$sql = "SELECT *
						FROM crd_aandeelhouder
						LEFT JOIN crd_bedrijf_aandeelhouder ON ban_and_id = and_id AND ban_bed_id = ".$bed_id."
						WHERE ban_bed_id IS NULL
						AND ".implode(' AND ', $search);

				return rs2arr($this->_db->query($sql));

			} else {

				$sql = "SELECT *
						, date_format(ban_actief_vanaf, '%d-%m-%Y') ban_actief_vanaf
						, date_format(ban_actief_tot, '%d-%m-%Y') ban_actief_tot
						FROM crd_bedrijf_aandeelhouder
						LEFT JOIN crd_aandeelhouder ON and_id = ban_and_id
						WHERE ban_bed_id = ".$bed_id;
				if (!is_null($ban_uiteindelijk)) {
					$sql .= ' AND ban_uiteindelijk = '.($ban_uiteindelijk ? 1 : 0);
				}

				$result = array();
				$rs = $this->_db->query($sql);
				while ($r = $rs->row()) {
					if (is_numeric($r['ban_bed_id_connect'])) {
						$r['bedrijf'] = $this->getBedrijf($r['ban_bed_id_connect']);
					}

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

				return $result;
			}
		}

	}

	function setBetalingservaring($bed_id, $brv_id, $data, $state=true) {
		if (!is_numeric($bed_id)) return;

		if (!$state && is_numeric($brv_id)) {
			$sql = "DELETE FROM crd_betalingservaring WHERE brv_id = ".$brv_id;
		} elseif (is_numeric($brv_id)) {
			if (!is_numeric($data['brv_bed_id_leverancier'])) return;

			$sql = "UPDATE crd_betalingservaring SET
						brv_bed_id_leverancier	= ".$data['brv_bed_id_leverancier']."
					,	brv_0_30				= ".(float)mysql_escape_string($data['brv_0_30'])."
					,	brv_31_60				= ".(float)mysql_escape_string($data['brv_31_60'])."
					,	brv_61_90				= ".(float)mysql_escape_string($data['brv_61_90'])."
					,	brv_90_120				= ".(float)mysql_escape_string($data['brv_90_120'])."
					,	brv_120_plus			= ".(float)mysql_escape_string($data['brv_120_plus'])."
					WHERE brv_id = ".$brv_id;
		} else {
			if (!is_numeric($data['brv_bed_id_leverancier'])) return;

			$sql = "INSERT INTO crd_betalingservaring SET
						brv_bed_id				= ".$bed_id."
					,	brv_invoerdatum			= NOW()
					,	brv_bed_id_leverancier	= ".$data['brv_bed_id_leverancier']."
					,	brv_0_30				= ".(float)mysql_escape_string($data['brv_0_30'])."
					,	brv_31_60				= ".(float)mysql_escape_string($data['brv_31_60'])."
					,	brv_61_90				= ".(float)mysql_escape_string($data['brv_61_90'])."
					,	brv_90_120				= ".(float)mysql_escape_string($data['brv_90_120'])."
					,	brv_120_plus			= ".(float)mysql_escape_string($data['brv_120_plus']);
		}

		$this->_db->execute($sql);
		$this->setLastEditor($bed_id);
	}

	function getBetalingservaring($bed_id) {
		if (!is_numeric($bed_id)) return;
		$sql = "SELECT *
				FROM crd_betalingservaring
				INNER JOIN crd_bedrijf ON bed_id = brv_bed_id_leverancier
				INNER JOIN crd_naam ON nam_bed_id = bed_id AND nam_actief_tot IS NULL
				LEFT JOIN crd_adres ON adr_bed_id = bed_id AND adr_actief_tot IS NULL AND adr_type = 'kantoor'
				WHERE brv_bed_id = ".$bed_id."
				ORDER BY brv_invoerdatum";

		return rs2arr($this->_db->query($sql));

	}

	function getBetalingservaringKwartaal($bed_id, $kwartaal = null) {
		if (!is_numeric($bed_id)) return;
		$sql = "SELECT *
				FROM crd_betalingservaring_kwartaal
				WHERE bek_bed_id = ".$bed_id."
				".(is_numeric($kwartaal) ? ' AND bek_kwartaal = '.$kwartaal : '')."
				ORDER BY bek_kwartaal";

		$result = rs2arr($this->_db->query($sql));
		return is_numeric($kwartaal) ? current($result) : $result;

	}


	function setBetalingservaringKwartaal($bed_id, $bek_id, $data, $state=true) {
		if (!is_numeric($bed_id)) return;

		if (!$state && is_numeric($bek_id)) {
			$sql = "DELETE FROM crd_betalingservaring_kwartaal WHERE bek_id = ".$bek_id;
		} else {

			if (!is_numeric($data['bek_kwartaal'])) return;

			$bek_data = array();


			if (is_numeric($bek_id) && $bek_id > 0) {
				$bek_data['bek_id'] = $bek_id;
			} else {

				if ($this->getBetalingservaringKwartaal($bed_id, $data['bek_kwartaal'])) {
					$this->assignError('Er bestaat al een betalingservaring voor dit kwartaal.');
					return;
				}
			}

			$bek_data['bek_bed_id']		= $bed_id;
			$bek_data['bek_kwartaal']	= $data['bek_kwartaal'];
			$bek_data['bek_dagen']		= ($data['bek_dagen'] ? $data['bek_dagen'] : 0);

			$sql = generate_query('crd_betalingservaring_kwartaal', $bek_data, 'bek_id');

		}

		$this->_db->execute($sql);
		$this->setLastEditor($bed_id);
	}

	function getVerklaringInstemming($bed_id, $inv_id=null) {
		if (!is_numeric($bed_id)) return;

		$sql = "SELECT
					*
				,	REPLACE(DATE_FORMAT(inv_boekjaar, '%d-%m-%Y'),'00-00-0000','') as inv_boekjaar
				,	REPLACE(DATE_FORMAT(inv_datum_afgifte, '%d-%m-%Y'),'00-00-0000','') as inv_datum_afgifte
				FROM
					crd_instemmingsverklaring
				WHERE inv_bed_id = ".$bed_id. "
				".(is_numeric($inv_id) ? ' AND inv_id = '.$inv_id : '');

		$result = array();
		$rs = $this->_db->query($sql);
		while ($r = $rs->row()) {
			if (is_numeric($r['inv_bed_id_instemming'])) {
				$r['bedrijf'] = $this->getBedrijf($r['inv_bed_id_instemming']);
			}

			$result[] = $r;

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

		return is_numeric($inv_id) ? current($result) : $result;

	}

	function setVerklaringInstemming($bed_id, $inv_id=null, $data=null, $state=true) {

		if (!is_numeric($bed_id)) return;

		if (!$state && is_numeric($inv_id)) {

			$sql = "DELETE FROM crd_instemmingsverklaring WHERE inv_id = ".$inv_id;

		} elseif (is_numeric($inv_id)) {
			if (!is_numeric($data['inv_bed_id_instemming'])) return;

			$sql = "UPDATE crd_instemmingsverklaring SET
						inv_bed_id_instemming	= ".$data['inv_bed_id_instemming']."
					,	inv_datum_afgifte		= ".($data['inv_datum_afgifte'] ? "'".$this->_fmt_date($data['inv_datum_afgifte'])."'" : 'NULL')."
					,	inv_boekjaar			= ".($data['inv_boekjaar'] ? "'".$this->_fmt_date($data['inv_boekjaar'])."'" : 'NULL')."
					WHERE inv_id = ".$inv_id;
		} else {
			if (!is_numeric($data['inv_bed_id_instemming'])) return;

			$sql = "INSERT INTO crd_instemmingsverklaring SET
						inv_bed_id				= ".$bed_id."
					,	inv_bed_id_instemming	= ".$data['inv_bed_id_instemming']."
					,	inv_datum_afgifte		= ".($data['inv_datum_afgifte'] ? "'".$this->_fmt_date($data['inv_datum_afgifte'])."'" : 'NULL')."
					,	inv_boekjaar			= ".($data['inv_boekjaar'] ? "'".$this->_fmt_date($data['inv_boekjaar'])."'" : 'NULL');
		}

		$this->_db->execute($sql);
		$this->setLastEditor($bed_id);

	}

	function getVerklaringAansprakelijkheid($bed_id, $ans_id=null) {
		if (!is_numeric($bed_id)) return;

		$sql = "SELECT
					*
				,	REPLACE(DATE_FORMAT(ans_datum_afgifte, '%d-%m-%Y'),'00-00-0000','') as ans_datum_afgifte
				,	REPLACE(DATE_FORMAT(ans_datum_ingang, '%d-%m-%Y'),'00-00-0000','') as ans_datum_ingang
				,	REPLACE(DATE_FORMAT(ans_datum_einde, '%d-%m-%Y'),'00-00-0000','') as ans_datum_einde
				FROM
					crd_aansprakelijkheidsverklaring
				WHERE ans_bed_id = ".$bed_id. "
				".(is_numeric($ans_id) ? ' AND ans_id = '.$ans_id : '');

		$result = array();
		$rs = $this->_db->query($sql);
		while ($r = $rs->row()) {
			if (is_numeric($r['ans_bed_id_aansprakelijk'])) {
				$r['bedrijf'] = $this->getBedrijf($r['ans_bed_id_aansprakelijk']);
			}

			$result[] = $r;

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

		return is_numeric($ans_id) ? current($result) : $result;

	}

	function setVerklaringAansprakelijkheid($bed_id, $ans_id=null, $data=null, $state=true) {

		if (!is_numeric($bed_id)) return;

		if (!$state && is_numeric($ans_id)) {

			$sql = "DELETE FROM crd_aansprakelijkheidsverklaring WHERE ans_id = ".$ans_id;

		} elseif (is_numeric($ans_id)) {
			if (!is_numeric($data['ans_bed_id_aansprakelijk'])) return;

			$sql = "UPDATE crd_aansprakelijkheidsverklaring SET
						ans_bed_id_aansprakelijk	= ".$data['ans_bed_id_aansprakelijk']."
					,	ans_datum_afgifte			= ".($data['ans_datum_afgifte'] ? "'".$this->_fmt_date($data['ans_datum_afgifte'])."'" : 'NULL')."
					,	ans_datum_ingang			= ".($data['ans_datum_ingang'] ? "'".$this->_fmt_date($data['ans_datum_ingang'])."'" : 'NULL')."
					,	ans_datum_einde				= ".($data['ans_datum_einde'] ? "'".$this->_fmt_date($data['ans_datum_einde'])."'" : 'NULL')."
					WHERE ans_id = ".$ans_id;
		} else {
			if (!is_numeric($data['ans_bed_id_aansprakelijk'])) return;

			$sql = "INSERT INTO crd_aansprakelijkheidsverklaring SET
						ans_bed_id					= ".$bed_id."
					,	ans_bed_id_aansprakelijk	= ".$data['ans_bed_id_aansprakelijk']."
					,	ans_datum_afgifte			= ".($data['ans_datum_afgifte'] ? "'".$this->_fmt_date($data['ans_datum_afgifte'])."'" : 'NULL')."
					,	ans_datum_ingang			= ".($data['ans_datum_ingang'] ? "'".$this->_fmt_date($data['ans_datum_ingang'])."'" : 'NULL')."
					,	ans_datum_einde				= ".($data['ans_datum_einde'] ? "'".$this->_fmt_date($data['ans_datum_einde'])."'" : 'NULL');
		}

		$this->_db->execute($sql);
		$this->setLastEditor($bed_id);

	}

	function setBedrijfBron($bed_id, $data) {

		if (!is_numeric($bed_id)) return;


		$fields = array();
		$fields['bbr_bed_id'] = $bed_id;

		$fields['bbr_volledig_onderzoek']				= ($data['bbr_volledig_onderzoek'] ? 1 : 0);
		$fields['bbr_volledig_onderzoek_datum']			= ($data['bbr_volledig_onderzoek_datum'] ? "'".$this->_fmt_date($data['bbr_volledig_onderzoek_datum'])."'" : 'NULL');
		$fields['bbr_update_onderzoek']					= ($data['bbr_update_onderzoek'] ? 1 : 0);
		$fields['bbr_update_onderzoek_datum']			= ($data['bbr_update_onderzoek_datum'] ? "'".$this->_fmt_date($data['bbr_update_onderzoek_datum'])."'" : 'NULL');
		$fields['bbr_limiet_onderzoek']					= ($data['bbr_limiet_onderzoek'] ? 1 : 0);
		$fields['bbr_limiet_onderzoek_datum']			= ($data['bbr_limiet_onderzoek_datum'] ? "'".$this->_fmt_date($data['bbr_limiet_onderzoek_datum'])."'" : 'NULL');

		$fields['bbr_samensteller_user']				= ($data['bbr_samensteller_user'] ? "'".mysql_escape_string(trim($data['bbr_samensteller_user']))."'" : 'NULL');

		//if (isset($fields['bbr_data_entry_user']))
		//	$fields['bbr_data_entry_user']				= ($data['bbr_data_entry_user'] ? "'".mysql_escape_string(trim($data['bbr_data_entry_user']))."'" : 'NULL');

		$fields['bbr_handelsregister_gekocht']			= ($data['bbr_handelsregister_gekocht'] ? 1 : 0);
		$fields['bbr_handelsregister_gekocht_datum']	= ($data['bbr_handelsregister_gekocht_datum'] ? "'".$this->_fmt_date($data['bbr_handelsregister_gekocht_datum'])."'" : 'NULL');
		$fields['bbr_jaarrekening_gekocht']				= ($data['bbr_jaarrekening_gekocht'] ? 1 : 0);
		$fields['bbr_jaarrekening_gekocht_datum']		= ($data['bbr_jaarrekening_gekocht_datum'] ? "'".$this->_fmt_date($data['bbr_jaarrekening_gekocht_datum'])."'" : 'NULL');
		$fields['bbr_jaarrekening_pub_datum']			= ($data['bbr_jaarrekening_pub_datum'] ? "'".$this->_fmt_date($data['bbr_jaarrekening_pub_datum'])."'" : 'NULL');

		$fields['bbr_jaarrekening_soort']				= ($data['bbr_jaarrekening_soort'] ? "'".mysql_escape_string(trim($data['bbr_jaarrekening_soort']))."'" : 'NULL');

		$fields['bbr_concernrelaties_gekocht']			= ($data['bbr_concernrelaties_gekocht'] ? 1 : 0);
		$fields['bbr_concernrelaties_gekocht_datum']	= ($data['bbr_concernrelaties_gekocht_datum'] ? "'".$this->_fmt_date($data['bbr_concernrelaties_gekocht_datum'])."'" : 'NULL');

		$fields['bbr_betalingservaringen']				= ($data['bbr_betalingservaringen'] ? 1 : 0);
		$fields['bbr_betalingservaringen_datum']		= ($data['bbr_betalingservaringen_datum'] ? "'".$this->_fmt_date($data['bbr_betalingservaringen_datum'])."'" : 'NULL');

		$fields['bbr_aanvullende_info']					= ($data['bbr_aanvullende_info'] ? "'".mysql_escape_string(trim($data['bbr_aanvullende_info']))."'" : 'NULL');
		$fields['bbr_aanvullende_info_datum']			= ($data['bbr_aanvullende_info_datum'] ? "'".$this->_fmt_date($data['bbr_aanvullende_info_datum'])."'" : 'NULL');

		$fields['bbr_controle_abc']						= ($data['bbr_controle_abc'] ? 1 : 0);

		$sql = "REPLACE INTO crd_bedrijf_bron (".implode(', ',array_keys($fields)).') VALUES ('.implode(', ',array_values($fields)).')';
		$this->_db->execute($sql);

	}

		function setLastEditor($bed_id) {
			if (strlen($_SESSION['session_usr_greeting']) && is_numeric($bed_id)) {
				$rs = $this->_db->query("SELECT bbr_id FROM crd_bedrijf_bron WHERE bbr_bed_id = ".$bed_id);
				$sql = $rs->RowCount > 0
							? ("UPDATE crd_bedrijf_bron SET bbr_data_entry_user = '".mysql_escape_string($_SESSION['session_usr_greeting'])."' WHERE bbr_bed_id = ".$bed_id)
							: ("INSERT INTO crd_bedrijf_bron SET bbr_data_entry_user = '".mysql_escape_string($_SESSION['session_usr_greeting'])."', bbr_bed_id = ".$bed_id);
				$rs->Close();
				$this->_db->execute($sql);
			}
		}

	function getBedrijfBron($bed_id) {
		if (!is_numeric($bed_id)) return;

		if (!isset($this->_cache['bedrijf_bron'][$bed_id])) {

			$sql = "SELECT crd_bedrijf_bron.*
						,	REPLACE(DATE_FORMAT(bbr_volledig_onderzoek_datum, '%d-%m-%Y'),'00-00-0000','') as bbr_volledig_onderzoek_datum
						,	REPLACE(DATE_FORMAT(bbr_update_onderzoek_datum, '%d-%m-%Y'),'00-00-0000','') as bbr_update_onderzoek_datum
						,	REPLACE(DATE_FORMAT(bbr_limiet_onderzoek_datum, '%d-%m-%Y'),'00-00-0000','') as bbr_limiet_onderzoek_datum
						,	REPLACE(DATE_FORMAT(bbr_handelsregister_gekocht_datum, '%d-%m-%Y'),'00-00-0000','') as bbr_handelsregister_gekocht_datum
						,	REPLACE(DATE_FORMAT(bbr_jaarrekening_gekocht_datum, '%d-%m-%Y'),'00-00-0000','') as bbr_jaarrekening_gekocht_datum
						,	REPLACE(DATE_FORMAT(bbr_jaarrekening_pub_datum, '%d-%m-%Y'),'00-00-0000','') as bbr_jaarrekening_pub_datum
						,	REPLACE(DATE_FORMAT(bbr_concernrelaties_gekocht_datum, '%d-%m-%Y'),'00-00-0000','') as bbr_concernrelaties_gekocht_datum
						,	REPLACE(DATE_FORMAT(bbr_betalingservaringen_datum, '%d-%m-%Y'),'00-00-0000','') as bbr_betalingservaringen_datum
						,	REPLACE(DATE_FORMAT(bbr_aanvullende_info_datum, '%d-%m-%Y'),'00-00-0000','') as bbr_aanvullende_info_datum
					FROM crd_bedrijf_bron WHERE bbr_bed_id = ".$bed_id;
			$this->_cache['bedrijf_bron'][$bed_id] = rs2arr($this->_db->query($sql), false, true);

		}

		return $this->_cache['bedrijf_bron'][$bed_id];

	}

	function getBalans($bed_id=null, $bal_id=null, $recalc_duizendtal=false) {
		if (!is_numeric($bed_id) && !is_numeric($bal_id) ) return;

		$sql = "SELECT
					crd_balans.*
				,	REPLACE(DATE_FORMAT(bal_datum, '%d-%m-%Y'),'00-00-0000','') as bal_datum
				FROM crd_balans
				WHERE ".($bed_id ? "bal_bed_id = ".$bed_id : "bal_id = ".$bal_id)."
				ORDER BY bal_jaar";

		$result = array();
		$rs = $this->_db->query($sql);
		while ($r = $rs->row()) {
			$r['balans_data'] = $this->getBalansdata($r['bal_id']);

			if ($r['bal_duizendtal'] && $recalc_duizendtal) {
				$r['balans_data'] = array_map(create_function('$a', 'if(is_numeric($a)) return $a*1000;'), $r['balans_data']);
			}


			$result[] = $r;

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

		return $bal_id ? current($result) : $result;
	}


	function getBalansData($bal_id) {

		$sql = "SELECT blv_beschrijving, bbv_waarde
				FROM crd_balans_balansveld
				INNER JOIN crd_balansveld ON blv_id = bbv_blv_id
				WHERE bbv_bal_id = ".$bal_id;

		return rs2arr($this->_db->query($sql), 'blv_beschrijving');

	}

	function handleBalansConsolidatie(&$balans) {

		$return = false;

		foreach ($balans as $index => $b) {
			if (is_numeric($b['bal_cons_bed_id']) && $b['bal_cons_bed_id'] > 0 && is_numeric($b['bal_jaar'])) {

				$sql = "SELECT bal_id FROM crd_balans WHERE bal_bed_id = ". $b['bal_cons_bed_id'] ." AND bal_jaar = ".$b['bal_jaar'];
				$bal_id = rs2arr($this->_db->query($sql), false, true);

				if (is_numeric($bal_id)) {
					$balans[$index] = $this->getBalans(null, $bal_id, true);
					$return = $balans[$index]['bal_bed_id'];
				}

			}
		}

		return $return;
	}

	function getAllBalansData($bed_id) {

		if (!isset($this->_cache['all_balans_data'][$bed_id])) {

			$balans = $this->getBalans($bed_id, null, true);

			//--- replace balans-data by geconsolideerde balans (if available)
			if ($balans_bed_id = $this->handleBalansConsolidatie($balans)) {

				$return = array($balans_bed_id, $balans);

			} elseif (count($balans) == 0) {

				if ($groot_bedrijf = $this->getGrootBedrijfInStructure($bed_id)) {

					$balans = $this->getBalans($groot_bedrijf['bed_id'], null, true);
					$return = array($groot_bedrijf['bed_id'], $balans);
				}
			} else {
				$return = array(null, $balans);
			}
			$this->_cache['all_balans_data'][$bed_id] = $return;
		}

		return $this->_cache['all_balans_data'][$bed_id];
	}

	function setBalans($bed_id, $bal_id, $data, $state=true) {
		if (!is_numeric($bed_id)) return;

		if (!$state && is_numeric($bal_id)) {

			$this->_db->execute("DELETE FROM crd_balans_balansveld WHERE bbv_bal_id = ".$bal_id);
			$this->_db->execute("DELETE FROM crd_balanstoelichting WHERE bat_bal_id = ".$bal_id);
			$this->_db->execute("DELETE FROM crd_bron_balans WHERE brl_bal_id = ".$bal_id);
			$this->_db->execute("DELETE FROM crd_balans WHERE bal_id = ".$bal_id);

		} elseif (is_numeric($bal_id)) {

			if (is_array($data['balans_data'])) {

				$this->bronUpdate(CRD_BRON_TYPE_BALANS, 'update', $bal_id);

				$sql = "UPDATE crd_balans SET
							bal_datum		= ".($data['bal_datum'] ? "'".$this->_fmt_date($data['bal_datum'])."'" : 'NULL')."
						,	bal_cons_bed_id = ".(!$data['bal_cons_delete'] && is_numeric($data['bal_cons_bed_id'])&&$data['bal_cons_bed_id']>0 ? $data['bal_cons_bed_id'] : 'NULL')."
						,	bal_jaar		= YEAR(bal_datum)
						,	bal_duizendtal	= ".((isset($data['bal_duizendtal']) && $data['bal_duizendtal']) ? 1 : 0)."
						WHERE bal_id = ".$bal_id;

				$this->_db->execute($sql);

				foreach ($data['balans_data'] as $blv_beschrijving => $bbv_waarde) {

					$this->setBalansveld($bal_id, $blv_beschrijving, $bbv_waarde);

				}


			}


		} else {

			$sql = "INSERT INTO crd_balans SET
						bal_bed_id			= ".$bed_id."
					,	bal_datum			= ".($data['bal_datum'] ? "'".$this->_fmt_date($data['bal_datum'])."'" : 'NULL')."
					,	bal_jaar			= YEAR(bal_datum)
					,	bal_duizendtal		= ".((isset($data['bal_duizendtal']) && $data['bal_duizendtal']) ? 1 : 0)."
					,	bal_cons_bed_id		= ".(is_numeric($data['bal_cons_bed_id'])&&$data['bal_cons_bed_id']>0 ? $data['bal_cons_bed_id'] : 'NULL')."
					,	bal_datum_deponering	= NOW()";
			$this->_db->execute($sql);
			$sql = '';
			$bal_id = $this->_db->lastInsertID();

			$this->bronUpdate(CRD_BRON_TYPE_BALANS, 'insert', $bal_id);

			if (is_array($data['balans_data'])) {

				foreach ($data['balans_data'] as $blv_beschrijving => $bbv_waarde) {

					$this->setBalansveld($bal_id, $blv_beschrijving, $bbv_waarde);
				}
			}
		}

		$this->setLastEditor($bed_id);

	}

	function setBalansveld($bal_id, $blv, $bbv_waarde) {

		if (!is_numeric($blv)) {
			$rs = $this->_db->query("SELECT blv_id FROM crd_balansveld WHERE blv_beschrijving = '".mysql_escape_string($blv)."'");
			$blv_id = $rs->Row('blv_id');
		} else {
			$blv_id = $blv;
		}

		$sql = "REPLACE INTO crd_balans_balansveld SET
					bbv_bal_id = ".$bal_id."
				,	bbv_blv_id = ".$blv_id."
				,	bbv_waarde = ".(is_numeric($bbv_waarde) ? $bbv_waarde : 'NULL')."
				,	bbv_type = '".($bbv_waarde >= 0 ? 'debet' : 'credit')."'";

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


	function getWinstVerlies($bed_id, $win_id=null,$recalc_duizendtal=false) {
		if (!is_numeric($bed_id) && !is_numeric($win_id)) return;
		$sql = "SELECT crd_winstenverlies.*
				,	REPLACE(DATE_FORMAT(win_datum, '%d-%m-%Y'),'00-00-0000','') as win_datum
				FROM crd_winstenverlies
				WHERE ".($bed_id ? "win_bed_id = ".$bed_id : "win_id = ".$win_id)."
				ORDER BY win_jaar";

		$result = array();
		$rs = $this->_db->query($sql);
		while ($r = $rs->row()) {
			$r['wv_data'] = $this->getWinstVerliesdata($r['win_id']);

			if ($r['win_duizendtal'] && $recalc_duizendtal) {
				$r['wv_data'] = array_map(create_function('$a', 'if(is_numeric($a)) return $a*1000;'), $r['wv_data']);
			}

			$result[] = $r;

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

		return $win_id ? current($result) : $result;
	}

	function handleWinstVerliesConsolidatie(&$wv) {

		$return = false;

		foreach ($wv as $index => $b) {
			if (is_numeric($b['win_cons_bed_id']) && $b['win_cons_bed_id'] > 0 && is_numeric($b['win_jaar'])) {

				$sql = "SELECT win_id FROM crd_winstverlies WHERE win_bed_id = ". $b['win_cons_bed_id'] ." AND win_jaar = ".$b['win_jaar'];
				$win_id = rs2arr($this->_db->query($sql), false, true);

				if (is_numeric($win_id)) {
					$wv[$index] = $this->getWinstVerlies(null, $win_id, true);
					$return = $wv[$index]['win_bed_id'];
				}

			}
		}

		return $return;
	}


	function getAllWinstVerliesData($bed_id) {

		if (!isset($this->_cache['all_winstverlies_data'][$bed_id])) {

			$wv = $this->getWinstVerlies($bed_id, null, true);

			//--- replace wv-data by geconsolideerde wv (if available)
			if ($wv_bed_id = $this->handleBalansConsolidatie($wv)) {

				$return = array($wv_bed_id, $wv);

			} elseif (count($wv) == 0) {

				if ($groot_bedrijf = $this->getGrootBedrijfInStructure($bed_id)) {

					$wv = $this->getWinstVerlies($groot_bedrijf['bed_id'], null, true);
					$return = array($groot_bedrijf['bed_id'], $wv);
				}
			} else {
				$return = array($bed_id, $wv);
			}
			$this->_cache['all_winstverlies_data'][$bed_id] = $return;
		}

		return $this->_cache['all_winstverlies_data'][$bed_id];
	}


	function getWinstVerliesdata($win_id) {

		$sql = "SELECT wiv_beschrijving, wwv_waarde
				FROM crd_winstenverlies_winstenverliesveld
				INNER JOIN crd_winstenverliesveld ON wiv_id = wwv_wiv_id
				WHERE wwv_win_id = ".$win_id;

		return rs2arr($this->_db->query($sql), 'wiv_beschrijving');

	}

	function setWinstVerlies($bed_id, $win_id, $data, $state=true) {

		if (!is_numeric($bed_id)) return;

		if (!$state && is_numeric($win_id)) {

			$this->_db->execute("DELETE FROM crd_winstenverlies_winstenverliesveld WHERE wwv_win_id = ".$win_id);
			$this->_db->execute("DELETE FROM crd_winstenverlies WHERE win_id = ".$win_id);

		} elseif (is_numeric($win_id)) {

			if (is_array($data['wv_data'])) {

				$sql = "UPDATE crd_winstenverlies SET
							win_datum		= ".($data['win_datum'] ? "'".$this->_fmt_date($data['win_datum'])."'" : 'NULL')."
						,	win_cons_bed_id = ".(!$data['win_cons_delete'] && is_numeric($data['win_cons_bed_id'])&&$data['win_cons_bed_id']>0 ? $data['win_cons_bed_id'] : 'NULL')."
						,	win_jaar		= YEAR(win_datum)
						,	win_duizendtal	= ".((isset($data['win_duizendtal']) && $data['win_duizendtal']) ? 1 : 0)."
						WHERE win_id = ".$win_id;
				$this->_db->execute($sql);

				foreach ($data['wv_data'] as $wiv_beschrijving => $wwv_waarde) {

					$this->setWinstVerliesveld($win_id, $wiv_beschrijving, $wwv_waarde);

				}


			}


		} else {

			$sql = "INSERT INTO crd_winstenverlies SET
						win_datum			= ".($data['win_datum'] ? "'".$this->_fmt_date($data['win_datum'])."'" : 'NULL')."
					,	win_bed_id			= ".$bed_id."
					,	win_cons_bed_id		= ".(!$data['win_cons_delete'] && is_numeric($data['win_cons_bed_id'])&&$data['win_cons_bed_id']>0 ? $data['win_cons_bed_id'] : 'NULL')."
					,	win_jaar			= YEAR(win_datum)
					,	win_duizendtal		= ".((isset($data['win_duizendtal']) && $data['win_duizendtal']) ? 1 : 0)."
					,	win_datum_deponering	= NOW()";

			$this->_db->execute($sql);
			$sql = '';
			$win_id = $this->_db->lastInsertID();

			if (is_array($data['wv_data'])) {
				foreach ($data['wv_data'] as $wiv_beschrijving => $wwv_waarde) {
					$this->setWinstVerliesveld($win_id, $wiv_beschrijving, $wwv_waarde);
				}
			}
		}
		$this->setLastEditor($bed_id);
	}

	function setWinstVerliesveld($win_id, $wiv, $wwv_waarde) {

		if (!is_numeric($wiv)) {
			$rs = $this->_db->query("SELECT wiv_id FROM crd_winstenverliesveld WHERE wiv_beschrijving = '".mysql_escape_string($wiv)."'");
			$wiv_id = $rs->Row('wiv_id');
		} else {
			$wiv_id = $wiv;
		}

		$sql = "REPLACE INTO crd_winstenverlies_winstenverliesveld SET
					wwv_win_id = ".$win_id."
				,	wwv_wiv_id = ".$wiv_id."
				,	wwv_waarde = ".(is_numeric($wwv_waarde) && $wwv_waarde != '' ? $wwv_waarde : 'NULL')."
				,	wwv_type = '".($wwv_waarde >= 0 ? 'debet' : 'credit')."'";

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



	function getBeroepsuitoefening($bed_id) {
		if (!is_numeric($bed_id)) return;

		$sql = "SELECT * FROM crd_beroepsuitoefening WHERE bui_bed_id = ".$bed_id;
		return rs2arr($this->_db->query($sql));
	}


	function setBeroepsuitoefening($bed_id, $bui_id, $data, $state=true) {
		if (!is_numeric($bed_id)) return;

		if (!$state && is_numeric($bui_id)) {
			$sql = "DELETE FROM crd_beroepsuitoefening WHERE bui_id = ".$bui_id;
		} elseif (is_numeric($bui_id)) {

			$sql = "UPDATE crd_beroepsuitoefening SET
						bui_beschrijving	= '".mysql_escape_string($data['bui_beschrijving'])."'
					WHERE bui_id = ".$bui_id;
		} else {

			$sql = "INSERT INTO crd_beroepsuitoefening SET
						bui_bed_id				= ".$bed_id."
					,	bui_beschrijving		= '".mysql_escape_string($data['bui_beschrijving'])."'";
		}

		$this->_db->execute($sql);
		$this->setLastEditor($bed_id);
	}

	function setCalamiteit($cmt_id, $data, $state=true) {

		if (is_numeric($cmt_id)) {

			if (!$state) {

				$this->_db->execute("DELETE FROM crd_calamiteitmutatie WHERE mut_cmt_id = ".$cmt_id);
				$this->_db->execute("DELETE FROM crd_calamiteitbericht WHERE cmb_cmt_id = ".$cmt_id);
				$this->_db->execute("DELETE FROM crd_calamiteit_curator WHERE clc_cmt_id = ".$cmt_id);
				$this->_db->execute("DELETE FROM crd_calamiteit WHERE cmt_id = ".$cmt_id);

			} else {

				$sql = "UPDATE crd_calamiteit SET
							cmt_bed_id		= ".$data['cmt_bed_id']."
						,	cmt_cty_id		= ".$data['cmt_cty_id']."
						,	cmt_datum		= ".($data['cmt_datum'] ? "'".$this->_fmt_date($data['cmt_datum'])."'" : 'NULL')."
						,	cmt_rtb_id		= ".$data['cmt_rtb_id']."
						,	cmt_insolventienummer	= '".mysql_escape_string($data['cmt_insolventienummer'])."'
						,	cmt_rechtercommissaris	= '".mysql_escape_string($data['cmt_rechtercommissaris'])."'
						,	cmt_surseance_van		= ".($data['cmt_surseance_van'] ? "'".$this->_fmt_date($data['cmt_surseance_van'])."'" : 'NULL')."
						,	cmt_surseance_tot		= ".($data['cmt_surseance_tot'] ? "'".$this->_fmt_date($data['cmt_surseance_tot'])."'" : 'NULL')."
						,	cmt_surseance_maanden	= ".(int)$data['cmt_surseance_maanden']."
						,	cmt_datum_crediteuren_vergadering		= ".($data['cmt_datum_crediteuren_vergadering'] ? "'".$this->_fmt_date($data['cmt_datum_crediteuren_vergadering'])."'" : 'NULL')."
						,	cmt_plaats_crediteuren_vergadering	= '".mysql_escape_string($data['cmt_plaats_crediteuren_vergadering'])."'
						WHERE cmt_id = ".$cmt_id;
				$this->_db->execute($sql);
			}


		} else {

			if (!is_numeric($data['cmt_cty_id']) && strlen($data['cmt_cty_voorloopcode']) == 1) {
				$sql = "SELECT cty_id FROM crd_calamiteittype WHERE cty_voorloopcode = '".$data['cmt_cty_voorloopcode']."'";
				$rs = $this->_db->query($sql);
				$data['cmt_cty_id'] = $rs->Row('cty_id');
			}

			if (is_numeric($data['cmt_bed_id']) && is_numeric($data['cmt_cty_id']) && is_numeric($data['cmt_rtb_id'])) {

				$sql = "INSERT INTO crd_calamiteit SET
							cmt_bed_id		= ".$data['cmt_bed_id']."
						,	cmt_cty_id		= ".$data['cmt_cty_id']."
						,	cmt_datum		= ".($data['cmt_datum'] ? "'".$this->_fmt_date($data['cmt_datum'])."'" : 'NULL')."
						,	cmt_rtb_id		= ".$data['cmt_rtb_id']."
						,	cmt_insolventienummer	= '".mysql_escape_string($data['cmt_insolventienummer'])."'
						,	cmt_rechtercommissaris	= '".mysql_escape_string($data['cmt_rechtercommissaris'])."'
						,	cmt_surseance_van		= ".($data['cmt_surseance_van'] ? "'".$this->_fmt_date($data['cmt_surseance_van'])."'" : 'NULL')."
						,	cmt_surseance_tot		= ".($data['cmt_surseance_tot'] ? "'".$this->_fmt_date($data['cmt_surseance_tot'])."'" : 'NULL')."
						,	cmt_surseance_maanden	= ".(int)$data['cmt_surseance_maanden']."
						,	cmt_datum_crediteuren_vergadering		= ".($data['cmt_datum_crediteuren_vergadering'] ? "'".$this->_fmt_date($data['cmt_datum_crediteuren_vergadering'])."'" : 'NULL')."
						,	cmt_plaats_crediteuren_vergadering	= '".mysql_escape_string($data['cmt_plaats_crediteuren_vergadering'])."'";
				$this->_db->execute($sql);
				$cmt_id = $this->_db->lastInsertID();

			}
		}

		return $cmt_id;
	}

	function getCalamiteit($cmt_id=null, $cmt_bed_id=null) {
		if (!is_numeric($cmt_id) && !is_numeric($cmt_bed_id)) return;

		if (is_numeric($cmt_id)) {

			if (!isset($this->_cache['calamiteit'][$cmt_id])) {

				$sql = "SELECT *
						,	CONCAT(cty_voorloopcode,'.',DATE_FORMAT(cmt_datum, '%y'),'/',cmt_insolventienummer) as rolnummer
						,	cmt_datum as cmt_datum_orig
						,	REPLACE(DATE_FORMAT(cmt_datum, '%d-%m-%Y'),'00-00-0000','') as cmt_datum
						,	REPLACE(DATE_FORMAT(cmt_surseance_van, '%d-%m-%Y'),'00-00-0000','') as cmt_surseance_van
						,	REPLACE(DATE_FORMAT(cmt_surseance_tot, '%d-%m-%Y'),'00-00-0000','') as cmt_surseance_tot
						,	REPLACE(DATE_FORMAT(cmt_datum_crediteuren_vergadering, '%d-%m-%Y'),'00-00-0000','') as cmt_datum_crediteuren_vergadering
						FROM crd_calamiteit
						INNER JOIN crd_calamiteittype ON cty_id = cmt_cty_id
						INNER JOIN crd_rechtbank ON rtb_id = cmt_rtb_id
						WHERE cmt_id = ".$cmt_id;

				$this->_cache['calamiteit'][$cmt_id] = current(rs2arr($this->_db->query($sql)));
			}
			return $this->_cache['calamiteit'][$cmt_id];

		} else {
			$sql = "SELECT *
					,	CONCAT(cty_voorloopcode,'.',DATE_FORMAT(cmt_datum, '%y'),'/',cmt_insolventienummer) as rolnummer
					,	cmt_datum as cmt_datum_orig
					,	REPLACE(DATE_FORMAT(cmt_datum, '%d-%m-%Y'),'00-00-0000','') as cmt_datum
					,	REPLACE(DATE_FORMAT(cmt_surseance_van, '%d-%m-%Y'),'00-00-0000','') as cmt_surseance_van
					,	REPLACE(DATE_FORMAT(cmt_surseance_tot, '%d-%m-%Y'),'00-00-0000','') as cmt_surseance_tot
					,	REPLACE(DATE_FORMAT(cmt_datum_crediteuren_vergadering, '%d-%m-%Y'),'00-00-0000','') as cmt_datum_crediteuren_vergadering
					FROM crd_calamiteit
					INNER JOIN crd_calamiteittype ON cty_id = cmt_cty_id
					INNER JOIN crd_rechtbank ON rtb_id = cmt_rtb_id
					WHERE cmt_bed_id = ".$cmt_bed_id."
					ORDER BY cmt_datum_orig";

			return rs2arr($this->_db->query($sql));


		}

	}


	function searchCalamiteiten($rtb_id=null, $cty_id=null, $week=null, $year=null, $insolventienummer=null,$page=1) {

		$return = array('search_total'=>0, 'search_page'=>0, 'search_per_page'=>0);
		$result = array();

		$filters = array();
		if (is_numeric($rtb_id)) $filters[] = "cmt_rtb_id = ".$rtb_id;
		if (is_numeric($cty_id)) $filters[] = "cmt_cty_id = ".$cty_id;
		if (is_numeric($week)) $filters[] = "WEEK(cmt_datum,1) = ".$week;
		if (is_numeric($year)) $filters[] = "YEAR(cmt_datum) = ".$year;
		if ($insolventienummer) $filters[] = "cmt_insolventienummer LIKE '%".$insolventienummer."%'";

		if (count($filters)) {

			$sql = "SELECT SQL_CALC_FOUND_ROWS *,
					CONCAT(cty_voorloopcode,'.',DATE_FORMAT(cmt_datum, '%y'),'/',cmt_insolventienummer) as rolnummer
					FROM crd_calamiteit
					INNER JOIN crd_rechtbank ON rtb_id = cmt_rtb_id
					INNER JOIN crd_calamiteittype ON cty_id = cmt_cty_id
					INNER JOIN crd_bedrijf ON bed_id = cmt_bed_id
					INNER JOIN crd_naam ON nam_bed_id = cmt_bed_id AND nam_actief_tot IS NULL
					LEFT JOIN crd_adres ON adr_bed_id = cmt_bed_id AND adr_actief_tot IS NULL AND adr_type = 'kantoor'
					WHERE ".implode(' AND ', $filters)."
					ORDER BY nam_beschrijving";


			if (!is_numeric($page)) $page = 1;
			$max = $this->settings->search->results_per_page == 'all' ? 50000 : $this->settings->search->results_per_page;
			$offset = is_numeric($page) ? ($page-1)*$max : 0;

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


			while ($r = $rs->row()) {

				$r = array_map('stripslashes', $r);
				$result[] = $r;

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

			$tmp = $this->_db->query('SELECT FOUND_ROWS()');
			$total_found_rows_adres = $tmp->Row('FOUND_ROWS()');

			$return['search_total'] = $total_found_rows_adres;
			$return['search_page'] = $page;
			$return['search_per_page'] = $max;
		}

		$return['result'] = $result;

		return $return;

	}


	function getCalamiteitCurator($cmt_id, $active_only=false) {
		if (!is_numeric($cmt_id)) return;

		$sql = "SELECT
					crd_calamiteit_curator.*
				,	crd_curator.*
				,	REPLACE(DATE_FORMAT(clc_actief_tot, '%d-%m-%Y'),'00-00-0000','') as clc_actief_tot
				FROM crd_calamiteit_curator
				INNER JOIN crd_curator ON cur_id = clc_cur_id
				WHERE clc_cmt_id = ".$cmt_id."
				".($active_only ? 'AND clc_actief_tot IS NULL' : '');

		return rs2arr($this->_db->query($sql));

	}

	function setCalamiteitCurator($cmt_id, $cur_id, $data=null, $state=true) {

		if (!is_numeric($cmt_id) || !is_numeric($cur_id)) return;

		if ($state) {

			$sql = "REPLACE INTO crd_calamiteit_curator SEt
						clc_cmt_id = ".$cmt_id."
					,	clc_cur_id = ".$cur_id."
					".(isset($data['clc_actief_tot']) && $data['clc_actief_tot'] ? ", clc_actief_tot = '".$this->_fmt_date($data['clc_actief_tot'])."'": '' );

			$this->_db->execute($sql);

		} else {
			$sql = "DELETE FROM crd_calamiteit_curator WHERE clc_cmt_id = ".$cmt_id." AND clc_cur_id = ".$cur_id;
			$this->_db->execute($sql);
		}


	}

//	function getCurator($cmt_id=null, $data) {
	function getCurator($cur_id, $cmt_id) {
		if (!is_numeric($cur_id)) return;

		$sql = "SELECT
				". (is_numeric($cmt_id) ? "REPLACE(DATE_FORMAT(clc_actief_tot, '%d-%m-%Y'),'00-00-0000','') as clc_actief_tot," : '') ."
					crd_curator.*
				FROM crd_curator
				". (is_numeric($cmt_id) ? "LEFT JOIN crd_calamiteit_curator ON clc_cur_id = cur_id AND clc_cmt_id = ".$cmt_id : '')."
				WHERE cur_id = ".$cur_id;

		return current(rs2arr($this->_db->query($sql)));
	}

	function searchCurator($cmt_id=null, $data) {
		$search = array();
		if (isset($data['search_cur_naam']) && strlen($data['search_cur_naam']) > 0) $search[] = "cur_naam LIKE '%".mysql_escape_string($data['search_cur_naam'])."%'";
		if (isset($data['search_cur_plaats']) && strlen($data['search_cur_plaats']) > 0) $search[] = "cur_plaats LIKE '%".mysql_escape_string($data['search_cur_plaats'])."%'";
		if (isset($data['search_cur_adres']) && strlen($data['search_cur_adres']) > 0) $search[] = "cur_adres LIKE '%".mysql_escape_string($data['search_cur_adres'])."%'";
		if (isset($data['search_cur_postcode']) && strlen($data['search_cur_postcode']) > 0) $search[] = "cur_postcode LIKE '%".mysql_escape_string($data['search_cur_postcode'])."%'";

		if (count($search) > 0) {
			$sql = "SELECT *
					FROM crd_curator
					". (is_numeric($cmt_id) ? "LEFT JOIN crd_calamiteit_curator ON clc_cur_id = cur_id AND clc_cmt_id = ".$cmt_id : '')."
					WHERE
					". (is_numeric($cmt_id) ? "clc_cur_id IS NULL AND" : '')."
					".implode(' AND ', $search);

			return rs2arr($this->_db->query($sql));
		}

	}

	function setCurator($cur_id, $data) {

		$fields = array();
		$fields[] = "cur_naam = '".mysql_escape_string($data['cur_naam'])."'";
		$fields[] = "cur_adres = '".mysql_escape_string($data['cur_adres'])."'";
		$fields[] = "cur_postcode = '".mysql_escape_string($data['cur_postcode'])."'";
		$fields[] = "cur_plaats = '".mysql_escape_string($data['cur_plaats'])."'";
		$fields[] = "cur_maatschap = '".mysql_escape_string($data['cur_maatschap'])."'";
		$fields[] = "cur_telefoonnummer = '".mysql_escape_string($data['cur_telefoonnummer'])."'";
		$fields[] = "cur_faxnummer = '".mysql_escape_string($data['cur_faxnummer'])."'";
		$fields[] = "cur_email_adres = '".mysql_escape_string($data['cur_email_adres'])."'";

		if (is_numeric($cur_id)) {

			$sql = "UPDATE crd_curator SET ". implode(', ', $fields) ." WHERE cur_id = ".$cur_id;
			$this->_db->execute($sql);

		} else {

			$sql = "INSERT INTO crd_curator SET ". implode(', ', $fields);
			$this->_db->execute($sql);
			$cur_id = $this->_db->lastInsertID();

		}

		return $cur_id;

	}

	function getCalamiteitmutatie($cmt_id) {
		if (!is_numeric($cmt_id)) return false;

		$sql = "SELECT *
				,	CONCAT(cty_voorloopcode,'.',DATE_FORMAT(mut_datum, '%y'),'/',mut_insolventienummer) as rolnummer
				,	mut_datum as mut_datum_orig
				,	REPLACE(DATE_FORMAT(mut_datum, '%d-%m-%Y'),'00-00-0000','') as mut_datum
				,	REPLACE(DATE_FORMAT(mut_indienen_tot, '%d-%m-%Y'),'00-00-0000','') as mut_indienen_tot
				,	REPLACE(DATE_FORMAT(mut_surseance_tot, '%d-%m-%Y'),'00-00-0000','') as mut_surseance_tot
				,	REPLACE(DATE_FORMAT(mut_datum_verificatie_vergadering, '%d-%m-%Y'),'00-00-0000','') as mut_datum_verificatie_vergadering
				FROM crd_calamiteitmutatie
				INNER JOIN crd_calamiteitmutatietype ON mty_id = mut_mty_id
				LEFT JOIN crd_calamiteittype ON cty_id = mut_cty_id
				WHERE mut_cmt_id = ".$cmt_id;

		return rs2arr($this->_db->query($sql));

	}

	function setCalamiteitmutatie($mut_id=null, $data, $state=true) {


		if (!$state && is_numeric($mut_id)) {
			$sql = "DELETE FROM crd_calamiteitmutatie WHERE mut_id = ".$mut_id;
		} elseif (is_numeric($mut_id)) {
			if (!is_numeric($data['mut_cmt_id']) || !is_numeric($data['mut_mty_id'])) return;

			$sql = "UPDATE crd_calamiteitmutatie SET
						mut_cmt_id				= ".$data['mut_cmt_id']."
					,	mut_mty_id				= ".$data['mut_mty_id']."
					,	mut_cty_id				= ".(is_numeric($data['mut_cty_id']) ? $data['mut_cty_id'] : 'NULL')."
					,	mut_datum				= ".($data['mut_datum'] ? "'".$this->_fmt_date($data['mut_datum'])."'" : 'NULL')."
					,	mut_insolventienummer	= '".mysql_escape_string($data['mut_insolventienummer'])."'
					,	mut_indienen_tot		= ".($data['mut_indienen_tot'] ? "'".$this->_fmt_date($data['mut_indienen_tot'])."'" : 'NULL')."
					,	mut_surseance_tot		= ".($data['mut_surseance_tot'] ? "'".$this->_fmt_date($data['mut_surseance_tot'])."'" : 'NULL')."
					,	mut_surseance_maanden	= ".($data['mut_surseance_maanden'] ? (int)$data['mut_surseance_maanden'] : 'NULL')."
					,	mut_datum_verificatie_vergadering	= ".($data['mut_datum_verificatie_vergadering'] ? "'".$this->_fmt_date($data['mut_datum_verificatie_vergadering'])."'" : 'NULL')."
					,	mut_plaats_verificatie_vergadering	= '".mysql_escape_string($data['mut_plaats_verificatie_vergadering'])."'
					WHERE mut_id = ".$mut_id;
		} else {
			if (!is_numeric($data['mut_cmt_id']) || !is_numeric($data['mut_mty_id'])) return;

			$sql = "INSERT INTO crd_calamiteitmutatie SET
						mut_cmt_id				= ".$data['mut_cmt_id']."
					,	mut_mty_id				= ".$data['mut_mty_id']."
					,	mut_cty_id				= ".(is_numeric($data['mut_cty_id']) ? $data['mut_cty_id'] : 'NULL')."
					,	mut_datum				= ".($data['mut_datum'] ? "'".$this->_fmt_date($data['mut_datum'])."'" : 'NULL')."
					,	mut_insolventienummer	= '".mysql_escape_string($data['mut_insolventienummer'])."'
					,	mut_indienen_tot		= ".($data['mut_indienen_tot'] ? "'".$this->_fmt_date($data['mut_indienen_tot'])."'" : 'NULL')."
					,	mut_surseance_tot		= ".($data['mut_surseance_tot'] ? "'".$this->_fmt_date($data['mut_surseance_tot'])."'" : 'NULL')."
					,	mut_surseance_maanden	= ".($data['mut_surseance_maanden'] ? (int)$data['mut_surseance_maanden'] : 'NULL')."
					,	mut_datum_verificatie_vergadering	= ".($data['mut_datum_verificatie_vergadering'] ? "'".$this->_fmt_date($data['mut_datum_verificatie_vergadering'])."'" : 'NULL')."
					,	mut_plaats_verificatie_vergadering	= '".mysql_escape_string($data['mut_plaats_verificatie_vergadering'])."'
					,	mut_createdate			= NOW()";
		}

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

	}

	function getCalamiteitbericht($cmt_id) {
		if (!is_numeric($cmt_id)) return false;

		$sql = "SELECT *
				,	cmb_datum AS cmb_datum_orig
				,	REPLACE(DATE_FORMAT(cmb_datum, '%d-%m-%Y'),'00-00-0000','') as cmb_datum
				,	REPLACE(DATE_FORMAT(cmb_verloopdatum, '%d-%m-%Y'),'00-00-0000','') as cmb_verloopdatum
				FROM crd_calamiteitbericht
				INNER JOIN crd_calamiteitbron ON cbr_id = cmb_cbr_id
				WHERE cmb_cmt_id = ".$cmt_id ."
				ORDER BY cmb_datum_orig";

		return rs2arr($this->_db->query($sql));

	}

	function setCalamiteitbericht($cmb_id=null, $data, $state=true) {


		if (!$state && is_numeric($cmb_id)) {
			$sql = "DELETE FROM crd_calamiteitbericht WHERE cmb_id = ".$cmb_id;
		} elseif (is_numeric($cmb_id)) {
			if (!is_numeric($data['cmb_cmt_id']) || !is_numeric($data['cmb_cbr_id'])) return;

			$sql = "UPDATE crd_calamiteitbericht SET
						cmb_cmt_id				= ".$data['cmb_cmt_id']."
					,	cmb_cbr_id				= ".$data['cmb_cbr_id']."
					,	cmb_datum				= ".($data['cmb_datum'] ? "'".$this->_fmt_date($data['cmb_datum'])."'" : 'NULL')."
					,	cmb_beschrijving		= '".mysql_escape_string($data['cmb_beschrijving'])."'
					,	cmb_verloopdatum		= ".($data['cmb_verloopdatum'] ? "'".$this->_fmt_date($data['cmb_verloopdatum'])."'" : 'NULL')."
					WHERE cmb_id = ".$cmb_id;
		} else {
			if (!is_numeric($data['cmb_cmt_id']) || !is_numeric($data['cmb_cbr_id'])) return;

			$sql = "INSERT INTO crd_calamiteitbericht SET
						cmb_cmt_id				= ".$data['cmb_cmt_id']."
					,	cmb_cbr_id				= ".$data['cmb_cbr_id']."
					,	cmb_datum				= ".($data['cmb_datum'] ? "'".$this->_fmt_date($data['cmb_datum'])."'" : 'NULL')."
					,	cmb_beschrijving		= '".mysql_escape_string($data['cmb_beschrijving'])."'
					,	cmb_verloopdatum		= ".($data['cmb_verloopdatum'] ? "'".$this->_fmt_date($data['cmb_verloopdatum'])."'" : 'NULL');
		}

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

	}


	function getScoreStaffels($stf_id=null) {

		$sql = "SELECT * FROM crd_crs_staffel
				".(is_numeric($stf_id) ? " WHERE stf_id = ".$stf_id : '')."
				ORDER BY stf_name";
		return rs2arr($this->_db->query($sql), false, is_numeric($stf_id));

	}

	function getScoreStaffelValues($stf_id) {
		if (!is_numeric($stf_id)) return;

		$sql = "SELECT * FROM crd_crs_staffel_values WHERE stv_stf_id = ".$stf_id."
				ORDER BY stv_in";

		return rs2arr($this->_db->query($sql));

	}

	function setScoreStaffel($stf_id, $stf_data) {
		if (!is_numeric($stf_id)) return;

		if (is_array($stf_data) && count($stf_data)) {

			$sql = "UPDATE crd_crs_staffel SET
						stf_weight		= ".$stf_data['stf_weight']."
					WHERE stf_id = ".$stf_id;

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


	}

	function setScoreStaffelValues($data_in, $data_out) {
		if (!is_array($data_in) || !is_array($data_out)) return;

		foreach ($data_in as $stv_id => $stv_in) {
			if (isset($data_out[$stv_id])) {
				$stv_out = $data_out[$stv_id];

				$sql = "UPDATE crd_crs_staffel_values SET
							stv_in	= ".$stv_in."
						,	stv_out = ".$stv_out."
						WHERE stv_id = ".$stv_id;
				$this->_db->execute($sql);

			}
		}

	}

	function getBedrijfsScoreOptionLabel($bso_type, $bso_code) {

		$sql = "SELECT bso_value
				FROM crd_bedrijfsscore_opties
				WHERE bso_type = '".$bso_type ."'
				AND bso_code = '".$bso_code ."'";

		return rs2arr($this->_db->query($sql), true, 'bso_value');

	}

	function getBedrijfsScoreOptions($bso_id=null, $bso_type=null) {
		$sql = "SELECT *
				FROM crd_bedrijfsscore_opties
				".($bso_id ? 'WHERE bso_id = '.$bso_id : '') ."
				".($bso_type ? "WHERE bso_type = '".$bso_type ."'": '') ."
				ORDER BY bso_type, bso_value";

		$result = rs2arr($this->_db->query($sql));


		if ($bso_type) {
			//-- different returnvalue for lookup by type

			$return = array();
			foreach ($result as $r) {
//				$return[$r['bso_code']] = '<span title="'.$r['bso_value'].'">'.substr($r['bso_value'],0,50) .(strlen($r['bso_value'])>50 ? '...' : '').'</span>';
				$return[$r['bso_code']] = $r['bso_value'];
			}
			$result = $return;

		}

		return $bso_id ? current($result) : $result;
	}

	function setBedrijfsScoreOption($bso_id, $data, $state=true) {

		if (!$state && is_numeric($bso_id)) {

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

		} else {

			$bso_data = array();
			if (is_numeric($bso_id) && $bso_id > 0) $bso_data['bso_id'] = $bso_id;
			$bso_data['bso_type'] = "'".mysql_escape_string($data['bso_type']) ."'";
			$bso_data['bso_code'] = "'".mysql_escape_string(str_replace(' ','',$data['bso_code'])) ."'";
			$bso_data['bso_value'] = "'".mysql_escape_string($data['bso_value']) ."'";

			$sql = generate_query('crd_bedrijfsscore_opties', $bso_data, 'bso_id');

			$this->_db->execute($sql);
			if (!$bso_id) $bso_id = $this->_db->lastInsertID();
		}

		return $bso_id;

	}

	function getGeneralOptions($opt_id=null, $opt_field_name=null) {
		$sql = "SELECT *
				FROM crd_general_options
				".($opt_id ? 'WHERE opt_id = '.$opt_id : '') ."
				".($opt_field_name ? "WHERE opt_field_name = '".$opt_field_name ."'": '') ."
				ORDER BY opt_field_name, opt_label";

		$fieldnames = $this->get_options('opt_field_name');

		$result = array();
		$rs = $this->_db->query($sql);
		while ($r = $rs->row()) {
			$r['opt_field_name_label'] = $fieldnames[$r['opt_field_name']];
			$result[] = $r;

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

		return $opt_id ? current($result) : $result;
	}
		function getGeneralOptionLabel($field_name, $value) {
			$sql = "SELECT opt_label
					FROM crd_general_options
					WHERE opt_field_name = '".mysql_escape_string($field_name)."'
					AND opt_value = '".mysql_escape_string($value)."'";
			$rs = $this->_db->query($sql);
			return $rs->Row('opt_label');
		}


	function setGeneralOption($opt_id, $data, $state=true) {

		if (!$state && is_numeric($opt_id)) {

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

		} else {

			$opt_data = array();
			if (is_numeric($opt_id) && $opt_id > 0) $opt_data ['opt_id'] = $opt_id;
			$opt_data['opt_field_name'] = "'".mysql_escape_string($data['opt_field_name']) ."'";
			$opt_data['opt_value'] = "'".mysql_escape_string(str_replace(' ','',$data['opt_value'])) ."'";
			$opt_data['opt_label'] = "'".mysql_escape_string($data['opt_label']) ."'";

			$sql = generate_query('crd_general_options', $opt_data , 'opt_id');

			$this->_db->execute($sql);
			if (!$opt_id) $opt_id = $this->_db->lastInsertID();
		}

		return $opt_id;

	}

	function form_exists($form_code) {
		list($form, ) = explode('.', $form_code);
		$file_path = $this->_settings->paths->absolute .'forms/'.$form.'.php';
		return file_exists($file_path) ? $file_path : false;
	}
	function form_tpl_exists($form_code) {
		$file_path = $this->_settings->paths->templates .'/form.'.$form_code .'.tpl';
		return file_exists($file_path) ? $file_path : false;
	}

	function get_options($fld_name, $add_empty=false) {

		switch ($fld_name) {

			case 'bed_rcv_id_oprichting':
			case 'bed_rcv_id_huidig':

				$table		= 'crd_rechtsvorm';
				$fld_id		= 'rcv_id';
				$fld_value	= 'rcv_beschrijving';
				$fld_order	= 'rcv_beschrijving';

				break;

			case 'bed_hrg_id':

				$table		= 'crd_handelsregister ';
				$fld_id		= 'hrg_id';
				$fld_value	= 'hrg_naam';
				$fld_order	= 'hrg_naam';

				break;

			case 'adr_type':
				$adr_types = array(	'bezoek'	=> 'Bezoek'
								,	'magazijn'	=> 'Magazijn'
								,	'filiaal'	=> 'Filiaal'
								,	'kantoor'	=> 'Kantoor'
								,	'post'		=> 'Post');
				return $adr_types;
				break;

			case 'adr_provincie':
				$provs = array(		'drente'		=> 'Drente'
								,	'flevoland'		=> 'Flevoland'
								,	'friesland'		=> 'Friesland'
								,	'gelderland'	=> 'Gelderland'
								,	'groningen'		=> 'Groningen'
								,	'limburg'		=> 'Limburg'
								,	'noord-brabant'	=> 'Noord-Brabant'
								,	'noord-holland'	=> 'Noord-Holland'
								,	'overijssel'	=> 'Overijssel'
								,	'utrecht'		=> 'Utrecht'
								,	'zeeland'		=> 'Zeeland'
								,	'zuid-holland'	=> 'Zuid-Holland'
							);
				return $provs;
				break;

			case 'iex_lnd_id':
			case 'adr_lnd_id':
				$table		= 'crd_land';
				$fld_id		= 'lnd_id';
				$fld_value	= 'lnd_beschrijving';
				$fld_order	= 'lnd_beschrijving';
				break;


			case 'bct_fnc_id':
				$table		= 'crd_functie';
				$fld_id		= 'fnc_id';
				$fld_value	= 'fnc_beschrijving';
				$fld_order	= 'fnc_beschrijving';
				break;

			case 'bct_bvd_id':
				$table		= 'crd_bevoegdheid';
				$fld_id		= 'bvd_id';
				$fld_value	= 'bvd_beschrijving';
				$fld_order	= 'bvd_beschrijving';
				break;

			case 'con_status':
				$list = array(	'u-moedermaatschappij'	=> 'Uiteindelijke moedermaatschappij'
							,	'moedermaatschappij'	=> 'Moedermaatschappij'
							,	'dochter'				=> 'Dochteronderneming'
							,	'holding'				=> 'Holding maatschappij'
							,	'werkmaatschappij'		=> 'Werkmaatschappij'
							);
				return $list;
				break;

			case 'con_rel_id':
				$table		= 'crd_concern_relatie';
				$fld_id		= 'rel_id';
				$fld_value	= 'rel_name';
				$fld_order	= 'rel_name';
				break;

			case 'onr_onroerend_goed':
				$list = array(	'onbekend'			=> 'Onbekend'
							,	'eigendom'			=> 'Eigendom'
							,	'huur'				=> 'Huur'
							,	'eigendom-huur'		=> 'Gedeelte eigendom, gedeelte huur'
							,	'concern-eigendom'	=> 'Concern eigendom'
							,	'concern-eigendom'	=> 'Concern eigendom'
							,	'prive-eigendom'	=> 'Prive eigendom'
							);
				return $list;
				break;

			case 'bbk_bnk_id':
				$table		= 'crd_bank';
				$fld_id		= 'bnk_id';
				$fld_value	= 'bnk_naam';
				$fld_order	= 'bnk_naam';
				break;

			case 'bsc_trend':
			case 'bsc_liquiditeit':
			case 'bsc_rentabiliteit':
			case 'bsc_solvabiliteit':
			case 'bsc_betalingservaring':
			case 'bsc_krediet_beoordeling_label':
			case 'bsc_balans':

				return array('0' => '-') + $this->getBedrijfsScoreOptions(null, $fld_name);

				break;

			case 'bsc_score':

				break;

			case 'rtb_id':
			case 'cmt_rtb_id':
				$table		= 'crd_rechtbank';
				$fld_id		= 'rtb_id';
				$fld_value	= 'rtb_naam';
				$fld_order	= 'rtb_naam';
				break;

			case 'mut_cty_id':
			case 'cmt_cty_id':
				$table		= 'crd_calamiteittype';
				$fld_id		= 'cty_id';
				$fld_value	= 'cty_beschrijving';
				$fld_order	= 'cty_beschrijving';
				break;

			case 'mut_mty_id':
				$table		= 'crd_calamiteitmutatietype';
				$fld_id		= 'mty_id';
				$fld_value	= 'mty_beschrijving';
				$fld_order	= 'mty_beschrijving';
				break;

			case 'cmb_cbr_id':
				$table		= 'crd_calamiteitbron';
				$fld_id		= 'cbr_id';
				$fld_value	= 'cbr_beschrijving';
				$fld_order	= 'cbr_beschrijving';
				break;

			case 'bso_type':
				$list = array(	'bsc_trend'				=> 'Trend'
							,	'bsc_solvabiliteit'		=> 'Solvabiliteit'
							,	'bsc_rentabiliteit'		=> 'Rentabiliteit'
							,	'bsc_liquiditeit'		=> 'Liquiditeit'
							,	'bsc_betalingservaring'	=> 'Betalingservaring'
							,	'bsc_krediet_beoordeling_label' => 'Krediet beoordeling beschrijving'
							,	'bsc_balans' => 'Balans'
					);
				return $list;

			case 'bek_kwartaal':

				$start_year = 2005;

				$list = array();
				for ($y = $start_year; $y <= date('Y'); $y++) {
					$list[$y.'01'] = $y .' kwartaal 1';
					$list[$y.'02'] = $y .' kwartaal 2';
					$list[$y.'03'] = $y .' kwartaal 3';
					$list[$y.'04'] = $y .' kwartaal 4';
				}

				return $list;

				break;

			case 'bed_betalingservaring_betalingsgedrag':
			case 'bbr_jaarrekening_soort':
			case 'bbr_aanvullende_info':

				$sql = "SELECT opt_label, opt_value FROM crd_general_options
						WHERE opt_field_name = '".$fld_name."'
						ORDER BY opt_label";
				$rs = $this->_db->query($sql);
				$result = rs2arr($rs, 'opt_value');
				return $result;

				break;

			case 'opt_field_name':
				$list = array(	'bed_betalingservaring_betalingsgedrag'	=> 'Betalingservaring - betalingsgedrag'
							,	'bbr_jaarrekening_soort'				=> 'Bronnenbeheer - soort jaarrekening'
							,	'bbr_aanvullende_info'					=> 'Bronnenbeheer - Aanvullende informatie ingekocht'
					);
				return $list;

				break;


		}

		if ($table && $fld_id && $fld_value) {

			$sql = "SELECT $fld_id, $fld_value FROM $table ". ($fld_order ? "ORDER BY $fld_order" : '');
			$rs = $this->_db->query($sql);
			$result = rs2arr($rs, $fld_id);
			if ($add_empty) {
				$result = array(0=>$add_empty) + $result;
			}

			return $result;

		}

	}

	function get_field_value($fld_name, $id) {

		switch ($fld_name) {
			case 'nam_beschrijving':
			case 'bed_handelsregisternummer':
			case 'bed_subdossiernummer':
			case 'bed_voortgezet':
			case 'bed_voortgezet_op':
			case 'bed_laatstewijziging_statuten':
			case 'bed_beeindigd':
			case 'bed_telefoonnummer':
			case 'bed_faxnummer':
			case 'bed_email_adres':
			case 'bed_web_adres':
			case 'bed_btw_nummer':
			case 'bed_datum_oprichting':
			case 'bed_plaats_oprichting':
			case 'bed_rcv_id_oprichting':
			case 'bed_rcv_id_huidig':
			case 'bed_hrg_id':
			case 'bed_grootbedrijf_code':
			case 'bed_grootbedrijf_type':
			case 'bed_betalingservaring_max_factuur':
			case 'bed_betalingservaring_betalingsgedrag':
			case 'bed_opmerking':
			case 'bed_opmerking_verloopdatum':

				$bedrijf_data = $this->getBedrijf($id);
				return $bedrijf_data[$fld_name];

				break;

			case 'bedrijf.handelsnaam':
				return implode("\n",(array)$this->getHandelsnaam($id));
				break;

			case 'bedrijf.adres':
				$data = $this->getAdressen($id);
				$return = '';
				foreach ($data as $adr) {
					if (!$adr['adr_actief_tot']) {
						$return .= $adr['adr_adres'] . ' '. $adr['adr_huisnummer'] . ' '. $adr['adr_adrestoevoeging']. "\n".
									$adr['adr_postcode'] . ' '. $adr['adr_plaats'] . ' ('. $adr['adr_type']. ")\n";
					}

				}
				return $return;
				break;

			case 'bedrijf.kapitaal':

				return fmt_plain_text('kapitaal', $this->getKapitaal($id, true));


				break;

			case 'bedrijf.bedrijfsleiding':

				if ($_REQUEST['action'] != 'calamiteit.new') {
					return fmt_plain_text('bedrijfsleiding', $this->getBedrijfContactpersoon($id));
				}

				break;

			case 'bedrijf.biknacecode':

				return fmt_plain_text('biknacecode', $this->getBIKNACE($id));
				break;

			case 'bedrijf.relaties':

				return fmt_plain_text('bedrijf.relaties', $this->getConcernGegeven($id));
				break;

			case 'search_string':
			case 'search_code_type':
			case 'search_ctc_voornaam':
			case 'search_ctc_naam':
			case 'search_ctc_geboortedatum':

				return $_REQUEST[$fld_name];
				break;

			case 'con_aandelen_percentage':
			case 'con_status':
			case 'con_rel_id':
				list($bed_id, $con_id, $action_type) = explode('|',$id);
				if (is_numeric($bed_id) && is_numeric($con_id)) {

					if ($action_type == 'new') return;

					$data = $this->getConcernGegeven($bed_id, $con_id);

					if ($fld_name == 'con_rel_id') {
//						return $this->getConcernStatusContra($data[$fld_name]);
						return $data['con_relatie_id'];

					} else {
						return $data[$fld_name];
					}
				}


				break;

			case 'bsc_datum':
			case 'kli_datum':
			case 'bps_datum':
				return date('d-m-Y');
				break;

			case 'bed_bed_id_filiaal_van':
				$bedrijf_data = $this->getBedrijf($id);
				$bed_bed_id_filiaal_van = $bedrijf_data['bed_bed_id_filiaal_van'];
				$bedrijf_data = $this->getBedrijf($bed_bed_id_filiaal_van);

				$bedrijf_txt = $bedrijf_data['nam_beschrijving']
							. ($bedrijf_data['adr_plaats'] ? ' - '.$bedrijf_data['adr_plaats'] : '')
							. ' ('.$bedrijf_data['bed_handelsregisternummer'].')';

				$return_var = array ('id'		=> $bed_bed_id_filiaal_van
									,'value'	=> $bedrijf_txt);
				return $return_var;

				break;

			case 'cmt_bed_id':
				if (is_numeric($id)) {
					$cmt = $this->getCalamiteit($id);
					$bed_id = $cmt['cmt_bed_id'];

				}
				if (!is_numeric($bed_id) && isset($_SESSION['crd_current_bed_id']) && is_numeric($_SESSION['crd_current_bed_id'])) {
					$bed_id = $_SESSION['crd_current_bed_id'];
				}

				if (is_numeric($bed_id)) {

					$return_var = '';
					$bedrijf_data = $this->getBedrijf($bed_id);

					if ($bedrijf_data) {
						$bedrijf_txt = $bedrijf_data['nam_beschrijving']
									. ($bedrijf_data['adr_plaats'] ? ' - '.$bedrijf_data['adr_plaats'] : '')
									. ' ('.$bedrijf_data['bed_handelsregisternummer'].')';

						$return_var = array ('id'		=> $bed_id
											,'value'	=> $bedrijf_txt);
					}

					return $return_var;


				}


				break;

			case 'bct_bed_id_connect':

				list($bed_id, $bed_id_connect) = explode('|', $id);

				if (is_numeric($bed_id_connect)) {
					$bedrijf_data = $this->getBedrijf($bed_id_connect);
					if ($bedrijf_data) {
						$return_var = array ('id'		=> $bed_id_connect
											,'value'	=> fmt_plain_text('bedrijf', $bedrijf_data));
						return $return_var;
					}
				}

				break;

			case 'bal_datum':
			case 'bal_duizendtal':
			case 'bal_cons_bed_id':
			case strpos($fld_name, 'balans_data['):

				list($bed_id, $bal_id) = explode('|', $id);

				if (is_numeric($bal_id) && $bal_id > 0) {
					$balans_data = $this->getBalans(null, $bal_id);

					if ($fld_name == 'bal_cons_bed_id') {

						if (is_numeric($balans_data['bal_cons_bed_id'])) {
							$bedrijf_data = $this->getBedrijf($balans_data['bal_cons_bed_id']);
							if ($bedrijf_data) {
								$return_var = array ('id'		=> $balans_data['bal_cons_bed_id']
													,'value'	=> fmt_plain_text('bedrijf', $bedrijf_data));
								return $return_var;
							}
						}

					} elseif (strpos($fld_name, 'balans_data[') !== false) {

						$blv = substr($fld_name, 12, -1);
						return is_numeric($balans_data['balans_data'][$blv]) ? round($balans_data['balans_data'][$blv]) : '';

					} else {

						return $balans_data[$fld_name];
					}

				}


				break;

			case 'win_datum':
			case 'win_duizendtal':
			case 'win_cons_bed_id':
			case strpos($fld_name, 'wv_data['):

				list($bed_id, $win_id) = explode('|', $id);

				if (is_numeric($win_id) && $win_id > 0) {
					$balans_data = $this->getWinstverlies(null, $win_id);

					if ($fld_name == 'win_cons_bed_id') {

						if (is_numeric($balans_data['win_cons_bed_id'])) {
							$bedrijf_data = $this->getBedrijf($balans_data['win_cons_bed_id']);
							if ($bedrijf_data) {
								$return_var = array ('id'		=> $balans_data['win_cons_bed_id']
													,'value'	=> fmt_plain_text('bedrijf', $bedrijf_data));
								return $return_var;
							}
						}

					} elseif (strpos($fld_name, 'wv_data[') !== false) {

						$blv = substr($fld_name, 8, -1);
						return is_numeric($balans_data['wv_data'][$blv]) ? round($balans_data['wv_data'][$blv]) : '';

					} else {

						return $balans_data[$fld_name];
					}

				}


				break;

			case 'inv_bed_id_instemming':
			case 'inv_datum_afgifte':
			case 'inv_boekjaar':

				list($bed_id, $inv_id) = explode("|", $id);

				if (is_numeric($bed_id) && is_numeric($inv_id)) {

					$inv_data = $this->getVerklaringInstemming($bed_id, $inv_id);

					if ($fld_name == 'inv_bed_id_instemming') {

						if (is_numeric($inv_data['inv_bed_id_instemming'])) {
							$bedrijf_data = $this->getBedrijf($inv_data['inv_bed_id_instemming']);
							if ($bedrijf_data) {
								$return_var = array ('id'		=> $inv_data['inv_bed_id_instemming']
													,'value'	=> fmt_plain_text('bedrijf', $bedrijf_data));
								return $return_var;
							}
						}

					} else {

						return $inv_data[$fld_name];
					}
				}


				break;

			case 'ans_bed_id_aansprakelijk':
			case 'ans_datum_afgifte':
			case 'ans_datum_ingang':
			case 'ans_datum_einde':

				list($bed_id, $ans_id) = explode("|", $id);

				if (is_numeric($bed_id) && is_numeric($ans_id)) {

					$inv_data = $this->getVerklaringAansprakelijkheid($bed_id, $ans_id);

					if ($fld_name == 'ans_bed_id_aansprakelijk') {

						if (is_numeric($inv_data['ans_bed_id_aansprakelijk'])) {
							$bedrijf_data = $this->getBedrijf($inv_data['ans_bed_id_aansprakelijk']);
							if ($bedrijf_data) {
								$return_var = array ('id'		=> $inv_data['ans_bed_id_aansprakelijk']
													,'value'	=> fmt_plain_text('bedrijf', $bedrijf_data));
								return $return_var;
							}
						}

					} else {

						return $inv_data[$fld_name];
					}
				}


				break;

			case 'ban_bed_id_connect':

				list($bed_id, $bed_id_connect) = explode('|', $id);

				if (is_numeric($bed_id_connect)) {
					$bedrijf_data = $this->getBedrijf($bed_id_connect);
					if ($bedrijf_data) {
						$return_var = array ('id'		=> $bed_id_connect
											,'value'	=> fmt_plain_text('bedrijf', $bedrijf_data));
						return $return_var;
					}
				}

				break;

			case 'clc_actief_tot':
				list($cmt_id, $cur_id) = explode('|', $id);

				if (is_numeric($cmt_id) && is_numeric($cur_id)) {
					$data['curator'] = $this->getCurator($cur_id, $cmt_id);

				}

				break;

			case 'cmt_cty_id':
			case 'cmt_rtb_id':
			case 'cmt_datum':
			case 'cmt_insolventienummer':
			case 'cmt_rechtercommissaris':
			case 'cmt_surseance_van':
			case 'cmt_surseance_tot':
			case 'cmt_surseance_maanden':
			case 'cmt_datum_crediteuren_vergadering':
			case 'cmt_plaats_crediteuren_vergadering':

				if (is_numeric($id)) {
					$cmt_id = $id;
				} else {
					list($cmt_id, $rtb_id, $cty_id) = explode('|',$id);
				}

				if (is_numeric($cmt_id)) {

					$cmt = $this->getCalamiteit($id);

					if ($fld_name == 'cmt_cty_id' && !is_numeric($cmt['cmt_cty_id']) && isset($cty_id) && is_numeric($cty_id)) {
						return $cty_id;
					} elseif ($fld_name == 'cmt_cty_id' && !is_numeric($cmt['cmt_cty_id']) && isset($_SESSION['crd_current_cty_id']) && is_numeric($_SESSION['crd_current_cty_id'])) {
						return $_SESSION['crd_current_cty_id'];

					} elseif ($fld_name == 'cmt_rtb_id' && !is_numeric($cmt['cmt_rtb_id']) && isset($rtb_id) && is_numeric($rtb_id)) {
						return $rtb_id;
					} elseif ($fld_name == 'cmt_rtb_id' && !is_numeric($cmt['cmt_rtb_id']) && isset($_SESSION['crd_current_rtb_id']) && is_numeric($_SESSION['crd_current_rtb_id'])) {
						return $_SESSION['crd_current_rtb_id'];
					} else {
						return $cmt[$fld_name];
					}
				}

				return;

			case 'mut_cty_id':

				if (is_numeric($id)) {
					$cmt_id = $id;
				} else {
					list($cmt_id, $cty_id) = explode('|', $id);
				}
				if (is_numeric($cty_id)) {
					return $cty_id;
				}

				break;

			case 'calamiteit.calamiteit_curator':

				return fmt_plain_text('calamiteitcurator', $this->getCalamiteitCurator($id));

				break;

			case 'bso_type':
			case 'bso_code':
			case 'bso_value':

				if (is_numeric($id) && $id > 0) {
					$data = $this->getBedrijfsScoreOptions($id);
					return $data[$fld_name];
				}

				break;


			case strpos($fld_name, 'bbr_') === 0:

				$bron_data = $this->getBedrijfBron($id);

				if ($fld_name == 'bbr_samensteller_user' && empty($bron_data[$fld_name])) {
					return $_SESSION['session_usr_greeting'];
				} elseif ($fld_name == 'bbr_handelsregister_gekocht_datum' && empty($bron_data[$fld_name])) {
					return date('d-m-Y');
				} elseif ($fld_name == 'bbr_jaarrekening_gekocht_datum' && empty($bron_data[$fld_name])) {
					return date('d-m-Y');
				} else {
					return $bron_data[$fld_name];
				}

				break;

			case 'opt_field_name':
			case 'opt_value':
			case 'opt_label':

				$data = $this->getGeneralOptions($id);
				return $data[$fld_name];
				break;

		}
	}


	function bronUpdate($type, $action, $id_1, $id_2=null) {

		//--- create new bron-entry
		$sql = "INSERT INTO crd_bron SET
					bro_datum	= NOW()
				,	bro_brt_id	= 2";
		$this->_db->execute($sql);
		$bro_id = $this->_db->lastInsertID();

		$action = strtolower($action) == 'insert' ? 'insert' : 'update';

		switch ($type) {
			case CRD_BRON_TYPE_BEDRIJF:
				$table = 'crd_bron_bedrijf';
				$fields = array();
				$fields['brb_bro_id'] = $bro_id;
				$fields['brb_bed_id'] = $id_1;
				$fields['brb_type'] = "'".$action."'";

				break;
			case CRD_BRON_TYPE_BALANS:
				$table = 'crd_bron_balans';
				$fields = array();
				$fields['brl_bro_id'] = $bro_id;
				$fields['brl_bal_id'] = $id_1;
				$fields['brl_type'] = "'".$action."'";

				break;
			case CRD_BRON_TYPE_KERNGEGEVEN:
				$table = 'crd_bron_kerngegeven';
				$fields = array();
				$fields['brk_bro_id'] = $bro_id;
				$fields['brk_krn_id'] = $id_1;
				$fields['brk_type'] = "'".$action."'";
				break;
			case CRD_BRON_TYPE_AANDEELHOUDER:
				$table = 'crd_bron_aandeelhouder';
				$fields = array();
				$fields['bra_bro_id'] = $bro_id;
				$fields['bra_ban_bed_id'] = $id_1;
				$fields['bra_ban_and_id'] = $id_2;
				$fields['brk_type'] = "'".$action."'";
				break;
		}

		if (is_numeric($bro_id) && $table && count($fields)) {

			$sql = "INSERT INTO ".$table." (".implode(',',array_keys($fields)).") VALUES (".implode(',',array_values($fields)).")";
			$this->_db->execute($sql);

		}


	}


	function generate_bed_id() {
		$sql = "INSERT INTO crd_bedrijf (bed_datum_creatie) VALUES (NOW())";
		$this->_db->execute($sql);
		$bed_id = $this->_db->lastInsertID();

		$this->bronUpdate(CRD_BRON_TYPE_BEDRIJF, 'insert', $bed_id);
		return $bed_id;
	}

	function generate_cmt_id() {
		$sql = "INSERT INTO crd_calamiteit (cmt_createdate) VALUES (NOW())";
		$this->_db->execute($sql);
		return $this->_db->lastInsertID();
	}

	function _fmt_date($date) {
		require_once('class.date.php');
		if ($date) {
			$d = new stdDate();
			return $d->convert($date);
		}
	}

	function _sql_generate_in($ids, $fld_name) {
		if (count($ids) > 1000) {

			$chunks = array_chunk($ids, 1000);
			$chunks_sql = array();
			foreach ($chunks as $ch) {
				$chunks_sql[] = $fld_name .' IN ('.implode(', ',$ch).')';
			}

			return '('.implode(' OR ', $chunks_sql) .')';

		} else {
			return $fld_name .' IN ('.implode(', ',$ids).')';
		}
	}

	function _sql_generate_like($fld_name, $args) {
		$likes = array();
		foreach ($args as $arg) {
			$likes[] = $fld_name ." LIKE '".mysql_escape_string(str_replace('*','%',trim($arg)))."'";
		}

		return '( '. implode(' AND ', $likes) .')';
	}

	/**/

	/***
		$type in (bedrijf / calamiteit)
	**/

	function foSearch($type) {

		$this->_smrt->assign('rtb_list', $this->get_options('rtb_id'));

		$go_search = false;

//		if ($_SERVER['REQUEST_METHOD'] == 'POST') {
		if (isset($_REQUEST['search_submit']) || isset($_REQUEST['search_page'])) {


			$go_search = true;

			$search_naam		= $_REQUEST['search_naam'];
			$search_plaats		= $_REQUEST['search_plaats'];
			$search_postcode	= $_REQUEST['search_postcode'];
			$search_huisnummer	= $_REQUEST['search_huisnummer'];
			$search_kvknummer	= $_REQUEST['search_kvknummer'];

			if ($type == 'calamiteit') {
				$search_rtb			= $_REQUEST['search_rtb'];
				$search_year		= $_REQUEST['search_year'];
				$search_week		= $_REQUEST['search_week'];
			}

			$search_page		= $_REQUEST['search_page'];
			$search_toon		= $_REQUEST['search_toon'];

			$_SESSION['crd_last_search'] = array();
			$_SESSION['crd_last_search']['search_naam'] = $_REQUEST['search_naam'];
			$_SESSION['crd_last_search']['search_plaats'] = $_REQUEST['search_plaats'];
			$_SESSION['crd_last_search']['search_postcode'] = $_REQUEST['search_postcode'];
			$_SESSION['crd_last_search']['search_huisnummer'] = $_REQUEST['search_huisnummer'];
			$_SESSION['crd_last_search']['search_kvknummer'] = $_REQUEST['search_kvknummer'];

			if ($type == 'calamiteit') {
				$_SESSION['crd_last_search']['search_rtb'] = $_REQUEST['search_rtb'];
				$_SESSION['crd_last_search']['search_year'] = $_REQUEST['search_year'];
				$_SESSION['crd_last_search']['search_week'] = $_REQUEST['search_week'];
			}

			$_SESSION['crd_last_search']['search_page'] = $_REQUEST['search_page'];
			$_SESSION['crd_last_search']['search_toon'] = $_REQUEST['search_toon'];



		} elseif (isset($_REQUEST['from']) && $_REQUEST['from'] == 'session' && is_array($_SESSION['crd_last_search'])) {

			$go_search = true;

			$search_naam		= $_SESSION['crd_last_search']['search_naam'];
			$search_plaats		= $_SESSION['crd_last_search']['search_plaats'];
			$search_postcode	= $_SESSION['crd_last_search']['search_postcode'];
			$search_huisnummer	= $_SESSION['crd_last_search']['search_huisnummer'];
			$search_kvknummer	= $_SESSION['crd_last_search']['search_kvknummer'];

			if ($type == 'calamiteit') {
				$search_rtb			= $_SESSION['crd_last_search']['search_rtb'];
				$search_year		= $_SESSION['crd_last_search']['search_year'];
				$search_week		= $_SESSION['crd_last_search']['search_week'];
			}

			$search_page		= $_SESSION['crd_last_search']['search_page'];
			$search_toon		= $_SESSION['crd_last_search']['search_toon'];

		}

		if ($go_search) {

			$this->settings->search->results_per_page = $search_toon;

			if ($type == 'calamiteit') {
				$calamiteit_search_data = array(	'rtb_id'=> $search_rtb
												,	'year'	=> $search_year
												,	'week'	=> $search_week
											);
			} else {
				$calamiteit_search_data = null;
			}


			if ($type == 'calamiteit' && !$search_naam && !$search_postcode && !$search_kvknummer) {

				$result = $this->searchCalamiteiten($search_rtb, null, $search_week, $search_year, null, $search_page);

			} else {


				$result = $this->searchBedrijf2(
											$search_naam
										,	$search_plaats
										,	$search_postcode
										,	$search_huisnummer
										,	$search_kvknummer
										,	$calamiteit_search_data
										,	$search_page);
			}



			$this->_smrt->assign('search_naam', $search_naam);
			$this->_smrt->assign('search_plaats', $search_plaats);
			$this->_smrt->assign('search_postcode', $search_postcode);
			$this->_smrt->assign('search_huisnummer', $search_huisnummer);
			$this->_smrt->assign('search_kvknummer', $search_kvknummer);
			if ($type == 'calamiteit') {
				$this->_smrt->assign('search_rtb', $search_rtb);
				$this->_smrt->assign('search_year', $search_year);
				$this->_smrt->assign('search_week', $search_week);
			}
			$this->_smrt->assign('search_toon', $search_toon);

			$this->_smrt->assign('search_result', $result);


			if ($result['search_total'] == 1) {
				$search_text = '1 zoekresultaat';
			} else {
				$search_text = $result['search_total']. ' zoekresultaten';
			}
			if ($search_naam) {
				$search_text .= ' voor <span class="highlight">'.$search_naam.'</span>';
			}
			if ($search_plaats) {
				$search_text .= ' in <span class="highlight">'.$search_plaats.'</span>';
			}
			$search_text_extra = array();
			$search_text_extra[] = ($search_postcode) ? 'postcode <span class="highlight">'.$search_postcode.'</span>' : '<span class="highlight">alle postcodes</span>';
			$search_text_extra[] = ($search_huisnummer) ? 'huisnummer <span class="highlight">'.$search_huisnummer.'</span>' : '<span class="highlight">alle huisnummers</span>';
			$search_text_extra[] = ($search_kvknummer) ? 'KvK <span class="highlight">'.$search_kvknummer.'</span>' : '<span class="highlight">alle kvk-nummers</span>';

			$search_text .= '<br><span style="font-size:10px;">('.implode(', ',$search_text_extra) .')</span><br>';

			$_SESSION['crd_last_search']['search_text'] = $search_text;

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

		}


		// Process the template
		$tpl = ($type == 'calamiteit')
					? $this->_settings->templates->fo_search_calamiteit
					: $this->_settings->templates->fo_search_bedrijf;

		$this->_smrt->display($tpl);

		// Clear template
		$this->_smrt->clear_all_assign();

	}




	function foPrintBedrijf($bed_id) {
		if (!is_numeric($bed_id)) return;

		$bedrijf = $this->getBedrijf($bed_id);
		$calamiteit = $this->getCalamiteit(null, $bed_id);

		$cs = &$this->getCreditScoring($bed_id, $bedrijf);

		$score = $cs->grand_total_text;

		$this->_smrt->assign('credit_score', $score);

		$this->_smrt->assign('bedrijf', $bedrijf);
		$this->_smrt->assign('calamiteit', $calamiteit);

		// Process the template
		$this->_smrt->display('fo_bedrijf.tpl');

		// Clear template
		$this->_smrt->clear_all_assign();


	}

	function foPrintCalamiteit($bed_id) {

		if (!is_numeric($bed_id)) return;

		$bedrijf = $this->getBedrijf($bed_id);
		$calamiteit = current($this->getCalamiteit(null, $bed_id));
		$cmt_id = $calamiteit['cmt_id'];

		$curator = current($this->getCalamiteitCurator($cmt_id, true));

		$mutaties = $this->getCalamiteitmutatie($cmt_id);

		$branche_info = $this->getBIKNACE($bed_id);

		$this->_smrt->assign('bedrijf', $bedrijf);
		$this->_smrt->assign('calamiteit', $calamiteit);
		$this->_smrt->assign('curator', $curator);
		$this->_smrt->assign('mutaties', $mutaties);
		$this->_smrt->assign('branche_info', $branche_info);

		$this->_smrt->assign('bedrijfsleiding', $this->getBedrijfContactpersoon($bed_id));



		// Process the template
		$this->_smrt->display('fo_calamiteit.tpl');

		// Clear template
		$this->_smrt->clear_all_assign();

	}

	function foPrintCreditScoring($bed_id) {
		if (!is_numeric($bed_id)) return;

		$bedrijf		= $this->getBedrijf($bed_id);

		if (is_numeric($bedrijf['bed_id_moeder'])) {

			$bedrijf_moeder = $this->getBedrijf($bedrijf['bed_id_moeder']);
			if ($bedrijf_moeder['bed_grootbedrijf_code']) {
				$bedrijf['moeder']['bed_grootbedrijf_code'] = $bedrijf_moeder['bed_grootbedrijf_code'];
				$bedrijf['moeder']['bed_grootbedrijf_type'] = $bedrijf_moeder['bed_grootbedrijf_type'];
			}
		}

		$calamiteit		= current($this->getCalamiteit(null, $bed_id));
		$branche_info	= $this->getBIKNACE($bed_id);
		$bank_info		= $this->getBedrijfBank($bed_id);

		$cs = &$this->getCreditScoring($bed_id, $bedrijf);

		//--- debug table
		ob_start();
		$cs->write_data();
		$debug_data = ob_get_contents();
		ob_end_clean();
		$this->_smrt->assign('debug_data', $debug_data);

		$chart_data = $cs->getChartData();

		if (count($chart_data['x']) > 1) {
			$this->_smrt->assign('chart_url_data', base64_encode(serialize($chart_data)));
		}

		$bedrijf_structure = $this->getConcernGegevenStructure($bed_id);


		$this->_smrt->assign('bedrijf', $bedrijf);
		$this->_smrt->assign('calamiteit', $calamiteit);
		$this->_smrt->assign('structure', $bedrijf_structure);
		$this->_smrt->assign('branche_info', $branche_info);
		$this->_smrt->assign('bank_info', $bank_info);

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

		$this->assignTemplateGlobals();

		// Process the template
		$this->_smrt->display('fo_creditscoring.tpl');

		// Clear template
		$this->_smrt->clear_all_assign();

	}

	function getCreditScoring($bed_id, &$bedrijf, $all_years=false) {
		if (!is_numeric($bed_id)) return;

		$cs = new creditScoring($bed_id, $this);

		if (!$bedrijf || !is_numeric($bedrijf['bed_id'])) {
			$bedrijf = $this->getBedrijf($bed_id);
		}

	//------------------------------------------------------------------------------------------------------------------
	//--- balans gegevens
		$balans = $this->getBalans($bed_id, null, true);
		//--- replace balans-data by geconsolideerde balans (if available)
		if ($cons_bed_id = $this->handleBalansConsolidatie($balans)) {
			//-- balans is consolidated
			$bedrijf['balans_owner'] = $this->getBedrijfsNaam($cons_bed_id);
		}


		if ($groot_bedrijf = $this->getGrootBedrijfInStructure($bed_id)) {

			$bedrijf['concern'] = $groot_bedrijf;


			if (count($balans) == 0) {
				$balans = $this->getBalans($groot_bedrijf['bed_id'], null, true);
				$bedrijf['balans_owner'] = $groot_bedrijf['nam_beschrijving'];
			}
		}


		if (!$all_years) {
			$min_year = date('Y') - 5;
			$new_balans = array();
			foreach ($balans as $key=>$value) {
				if ($value['bal_jaar'] >= $min_year) {
					$new_balans[] = $value;
				}
			}
			$balans = $new_balans;
		}

		$years = array();
		$cnt = 0;
		for ($i=count($balans)-1; $i >= 0; $i--) {
			if (++$cnt <= 4) {
				$years[] = $balans[$i]['bal_jaar'];
			}
		}
		sort($years);

		$cs->setYears($years);


	//------------------------------------------------------------------------------------------------------------------
	//--- personeel
		$personeel = $this->getYearOverview($this->getPersoneel($bed_id),'bps_datum_orig', 'bps_personeel_totaal');

		if (count($personeel) > 1) {

			$personeel_perc = $this->getYearDiff($personeel, true);

			$cs->setData('personeel', $personeel);
			$cs->setData('personeel_perc', $personeel_perc, true);

		}

	//------------------------------------------------------------------------------------------------------------------
	//--- Eigen vermogen
		$eigen_vermogen = $this->getYearOverview($balans,'bal_jaar', 'balans_data.eigen_vermogen');
		$eigen_vermogen_perc = $this->getYearDiff($eigen_vermogen, true);

		$cs->setData('eigen_vermogen', $eigen_vermogen);
		$cs->setData('eigen_vermogen_perc', $eigen_vermogen_perc, true);


	//------------------------------------------------------------------------------------------------------------------
	//--- Kortlopende schulden
		$schulden = $this->getYearOverview($balans,'bal_jaar', 'balans_data.kortlopende_schulden');
		$schulden_lang = $this->getYearOverview($balans,'bal_jaar', 'balans_data.langlopende_schulden');
		$schulden_perc = $this->getYearDiff($schulden, true);

		$cs->setData('schulden', $schulden);
		$cs->setData('schulden_perc', $schulden_perc, true);

	//------------------------------------------------------------------------------------------------------------------
	//--- werkkapitaal
		$vlottende_active = $this->getYearOverview($balans,'bal_jaar', 'balans_data.vlottende_activa');
		$vlottende_passiva = $this->getYearOverview($balans,'bal_jaar', 'balans_data.kortlopende_schulden');

		$cs->setData('vlottende_activa', $vlottende_active);
		$cs->setData('vlottende_passiva', $vlottende_passiva);

		$werkkapitaal = array();
		$solvabiliteit = array();

		foreach ($years as $year) {
			$werkkapitaal[$year] = ($vlottende_active[$year] - $vlottende_passiva[$year]);
			$solvabiliteit[$year] = $eigen_vermogen[$year] / ($schulden_lang[$year] + $schulden[$year]);
		}


		$cs->setData('werkkapitaal', $werkkapitaal);
		$cs->setData('solvabiliteit', $solvabiliteit);

	//------------------------------------------------------------------------------------------------------------------
	//--- quick ratio
		$voorraden = $this->getYearOverview($balans,'bal_jaar', 'balans_data.voorraden');

		$quick_ratio = array();
		foreach ($years as $year) {
			if ($schulden[$year] != 0) {
				$quick_ratio[$year] = ($vlottende_active[$year]-$voorraden[$year]) / $schulden[$year];
			} else {
				$quick_ratio[$year] = 0;
			}

		}
		$cs->setData('quick_ratio', $quick_ratio);
		$quick_ratio_perc = $this->getYearDiff($quick_ratio, true);
		$cs->setData('quick_ratio_perc', $quick_ratio_perc, true);

	//------------------------------------------------------------------------------------------------------------------
	//--- current_ratio
		$current_ratio = array();
		foreach ($years as $year) {
			if ($schulden[$year] != 0) {
				$current_ratio[$year] = $vlottende_active[$year] / $schulden[$year];
			} else {
				$current_ratio[$year] = 0;
			}
		}

		$cs->setData('current_ratio', $current_ratio);
		$current_ratio_perc = $this->getYearDiff($current_ratio, true);
		$cs->setData('current_ratio_perc', $current_ratio_perc, true);

	//------------------------------------------------------------------------------------------------------------------
	//--- leeftijd
		$ts_oprichting = 0;
		if (strlen(trim($bedrijf['bed_datum_oprichting'])) > 0) {
			if (substr_count($bedrijf['bed_datum_oprichting'],'-') == 2) {
				list($d,$m,$y) = explode('-', $bedrijf['bed_datum_oprichting']);
				$ts_oprichting = mktime(0,0,0,$m,$d,$y);
			}
		}

		if ($ts_oprichting > 0) {
			$leeftijd = array();

			foreach ($years as $year) {
				$leeftijd[$year] = (mktime(0,0,0,1,1,$year) - $ts_oprichting) / (86400*365.25);
			}
			$cs->setData('leeftijd', $leeftijd);
		}

	//------------------------------------------------------------------------------------------------------------------
	//--- betalingsscore

		//-- TODO

		$cs->calcTotals();

		return $cs;

	}

	function getYearOverview($data, $year_col, $data_col) {

		$return = array();
		foreach ((array)$data as $item) {

			if (strpos($data_col,'.') !== false) {
				list($v1, $v2) = explode('.', $data_col);
				$the_value = $item[$v1][$v2];
			} else {
				$the_value = $item[$data_col];
			}
			if (is_numeric($item[$year_col])) {
				$year = $item[$year_col];
			} else {
				$year = date('Y',strtotime($item[$year_col]));
			}

			$return[$year] = $the_value;
		}

		ksort($return);
		return $return;

	}

	function getYearDiff($data, $percentage=false) {
		$return = array();
		$prev = null;
		foreach ($data as $year => $value) {

			if (!is_null($prev)) {
				if ($percentage && $prev == 0) {
					$return[$year] = null;
				} else {

					if ($percentage) {

						if ($prev < 0) {
							$return[$year] = $value > 0 ? ((-$prev+$value)/-$prev) : (1-$value/$prev);
						} else {
							$return[$year] = $value > 0 ? -(1-$value/$prev) : -((-$prev+$value)/-$prev);
						}

					} else {

						//$return[$year] = ($percentage) ? ($value - $prev) / $prev : $value - $prev;
						$return[$year] = $value - $prev;
					}
				}


			}
			$prev = $value;

		}

		return $return;
	}



}

class creditScoring {

	var $bed_id = null;
	var $data = array();
	var $start_year = null;
	var $_required_fields = array();
	var $_weights = array();
	var $_score_fields = array();
	var $website_fields = array();

	function creditScoring($bed_id, &$ccrd0200) {
		$this->bed_id = $bed_id;
		$this->_ccrd = &$ccrd0200;
		$this->_db = &$ccrd0200->_db;

		$this->_initData();
	}

	function _initData() {

		$sql = "SELECT stf_code
				,	stf_name
				,	stf_weight
				,	stf_req_for_scoring
				,	stf_view_on_website
				,	stf_use_for_score
				FROM crd_crs_staffel
				ORDER BY stf_order";

		$rs = $this->_db->query($sql);
		while ($r = $rs->row()) {
			if ($r['stf_use_for_score'] == 1)	$this->_weights[$r['stf_code']] = $r['stf_weight'];
			if ($r['stf_req_for_scoring'] == 1) $this->_required_fields[] = $r['stf_code'];
			if ($r['stf_use_for_score'] == 1)	$this->_score_fields[] = $r['stf_code'];
			if ($r['stf_view_on_website'])	$this->website_fields[$r['stf_view_on_website']][$r['stf_code']] = $r['stf_name'];

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

	function _getWeights() {
		$sql = "SELECT stf_code, stf_weight FROM crd_crs_staffel WHERE stf_use_for_score = 1";
		$this->_weights = rs2arr($this->_db->query($sql), 'stf_code');
	}

	function setYears($years) {
		$this->years = $years;

	}

	function calcTotals() {

		$year_totals = array();
		foreach ($this->years as $year) {
			$year_totals[$year] = null;
		}

		foreach ($this->data as $type => $data) {
			foreach ($this->years as $year) {
				$year_totals[$year] += $data['weights'][$year];
			}
		}
		$this->year_totals = $year_totals;
		foreach ($this->years as $year) {
			$year_totals[$year] = $year_totals[$year] / array_sum($this->_weights);

			$this->data['eind_cijfer'][$year] = $this->data['mark_sum'][$year] / $this->data['weights_sum'][$year];

		}

		//--- calc grand total
		$years_flip = array_reverse($this->years);

		$gt_sum = 0;
		$gt_wegingsfactor_sum = 0;
		foreach ($years_flip as $year) {
			if (!$this->years_invalid[$year]) {
				$gt_wegingsfactor = ($gt_wegingsfactor_sum == 0) ? 0.6 : 0.2;
				$gt_wegingsfactor_sum += $gt_wegingsfactor;

				$gt_sum += $gt_wegingsfactor*$this->data['eind_cijfer'][$year];
			}
		}

		//-- max 3 years
		sort($this->years);
		$this->years_website = $this->years;
		while (count($this->years_website) > 3) {
			array_shift($this->years_website);
		}

		if (count($this->years_website)) {
			sort($this->years_website);
			$this->years_website = array_reverse($this->years_website);
		}

		if ($gt_wegingsfactor_sum > 0) {

			$this->grand_total = $gt_sum / $gt_wegingsfactor_sum;
			$this->grand_total_text = round($this->_getStaffelValue('risicofactor', $this->grand_total));

		} else {
			$this->grand_total = 0;
		}


		$this->year_totals = $year_totals;

		$_calamiteit = $this->_ccrd->getCalamiteit(null, $this->bed_id);


		//--- calc limieten

		if (count($_calamiteit) > 0) {

			$this->grand_total = 1;
			$this->grand_total_text = 1;

			$this->limit_advice = 'Wij adviseren geen zakelijke relatie aan te gaan met dit bedrijf';

		} else {


			if ($this->grand_total < 5) {
				$this->limit_advice = 'Wij raden aan op contante basis zaken te doen';
			} else {

				$eigen_vermogen = end($this->data['eigen_vermogen']['source_data']);

				if ($this->grand_total <= 5.5 && $eigen_vermogen < 0) {
					$this->limit_amount = 2500;
				} else {

					$percentage = $this->_getStaffelValue('limiet', $this->grand_total);
					$max_limit = ($percentage/100) * $eigen_vermogen;
					if ($max_limit < 0) $max_limit = 0;

					$this->limit_amount = $this->_getStaffelValue('limiet_amount', $max_limit);

				}

			}
		}

		$bedrijf = $this->_ccrd->getBedrijf($this->bed_id);
		if ($bedrijf['bed_grootbedrijf_code'] == 1) {

			//--- grootbedrijfcode available, maximize limit-amount
			$this->limit_amount = 25000;

		}


	}


	function setData($data_code, $data, $percentage=false) {

		$this->data[$data_code]['percentage'] = $percentage;
		$this->data[$data_code]['source_data'] = $data;

		//foreach ($this->data[$data_code]['source_data'] as $year => $value) {
		foreach ($this->years as $year) {
			if (isset($this->data[$data_code]['source_data'][$year])) {
				$value = $this->data[$data_code]['source_data'][$year];

				if (in_array($data_code, $this->_score_fields)) {
					$this->data[$data_code]['staffel_data'][$year] = $this->_getStaffelValue($data_code, ($percentage ? $value*100 : $value));

					$this->data[$data_code]['weights'][$year] = $this->data[$data_code]['staffel_data'][$year] * $this->_weights[$data_code];

					$this->data['weights_sum'][$year] += $this->_weights[$data_code];
					$this->data['mark_sum'][$year] += $this->data[$data_code]['weights'][$year];
				}
			} else {

				if (in_array($data_code, $this->_required_fields)) {
					$this->years_invalid[$year] = $year;
				}
			}
		}
	}

	function _getStaffelValue($stf_code, $value) {
		if (!is_numeric($value)) $value = 0;

		$sql = "SELECT stv_out
				FROM crd_crs_staffel
				INNER JOIN crd_crs_staffel_values ON stv_stf_id = stf_id
				WHERE stf_code = '".mysql_escape_string(strtolower($stf_code))."'
				AND stv_in <= ".$value."
				ORDER BY stv_in DESC
				LIMIT 1";

		$rs = $this->_db->query($sql);
		return $rs->Row('stv_out');

	}

	function getChartData() {

		$values_x = array();
		$values_y = array();
		foreach ($this->years as $year) {
			if (!$this->years_invalid[$year]) {
				$values_x[] = $year;
				$values_y[] = $this->data['eind_cijfer'][$year];
			}
		}

		$data = array();
		$data['x'] = $values_x;
		$data['y'] = $values_y;

		return $data;

	}

	function write_data() {
//		echo "<style> table td {text-align:right;}</style>";
		echo '<table border=1>
				<tr>
					<th rowspan="2">type</th>
					<th colspan="'.count($this->years).'">Bron</th>
					<th colspan="'.count($this->years).'">Cijfer</th>
					<th rowspan="2">W</th>
					<th colspan="'.count($this->years).'">C*W</th>
				</tr>
				<tr>';
		foreach ($this->years as $year) {
			$text_color = ($this->years_invalid[$year]) ? 'gray' : 'black';
			echo "<th style=\"color:$text_color;\">$year</th>";
		}
		foreach ($this->years as $year) {
			$text_color = ($this->years_invalid[$year]) ? 'gray' : 'black';
			echo "<th style=\"color:$text_color;\">$year</th>";
		}
		foreach ($this->years as $year) {
			$text_color = ($this->years_invalid[$year]) ? 'gray' : 'black';
			echo "<th style=\"color:$text_color;\">$year</th>";
		}

		foreach ($this->_weights as $type => $null) {
			$data = $this->data[$type];
//		foreach ($this->data as $type => $data) {
			echo "<tr><td>$type</td>";
			foreach ($this->years as $year) {
				$text_color = ($this->years_invalid[$year]) ? 'gray' : 'black';

				if (is_numeric($data['source_data'][$year])) {
					$v = ($data['percentage'] ? $data['source_data'][$year]*100 : $data['source_data'][$year]);
					echo '<td style="color:'.$text_color.';">'.number_format($v,2).($data['percentage'] ? '%' : '').'</td>';
				} else {
					echo "<td style=\"color:$text_color;\">&nbsp;</td>";
				}

			}
			foreach ($this->years as $year) {
				$text_color = ($this->years_invalid[$year]) ? 'gray' : 'black';
				if (is_numeric($data['source_data'][$year])) {
					echo '<td style="color:'.$text_color.';">!'.$data['staffel_data'][$year].'</td>';
				} else {
					echo "<td style=\"color:$text_color;\">&nbsp;</td>";
				}
			}
			echo '<td>' .$this->_weights[$type]. '</td>';
			foreach ($this->years as $year) {
				$text_color = ($this->years_invalid[$year]) ? 'gray' : 'black';
				if (is_numeric($data['source_data'][$year])) {
					echo '<td style="color:'.$text_color.';">'.$data['weights'][$year].'</td>';
				} else {
					echo "<td style=\"color:$text_color;\">&nbsp;</td>";
				}
			}


		}

		echo '<tr><td>TOTAAL</td><td colspan="'.(count($this->years)*2).'">&nbsp;</td>';
		echo '<td>'.array_sum($this->_weights) .'</td>';
		foreach ($this->years as $year) {
			$text_color = ($this->years_invalid[$year]) ? 'gray' : 'black';
			echo "<td style=\"color:$text_color;\">".$this->data['mark_sum'][$year] . ' ('.$this->data['weights_sum'][$year].")<br>".number_format($this->data['eind_cijfer'][$year],2)."</td>";
		}

		echo '</tr>';

		echo "</table>";

		echo "<h1> ---> ".number_format($this->grand_total, 2, ',', '.')."</h1>";



	}

}

if (!function_exists('array_intersect_key')) {
	function array_intersect_key ($isec, $arr2) {
		$argc = func_num_args();

		for ($i = 1; !empty($isec) && $i < $argc; $i++) {
			$arr = func_get_arg($i);

			foreach ($isec as $k => $v) {
				if (!isset($arr[$k])) unset($isec[$k]);
			}
		}
		return $isec;
	}
}

if (!function_exists('array_diff_key')) {
	function array_diff_key() {
		$argCount  = func_num_args();
		$diff_arg_prefix = 'diffArg';
		$diff_arg_names = array();
		for ($i=0; $i < $argCount; $i++) {
			$diff_arg_names[$i] = 'diffArg'.$i;
			$$diff_arg_names[$i] = array_keys((array)func_get_arg($i));
		}
		$diffArrString = '';
		if (!empty($diff_arg_names)) $diffArrString =  '$'.implode(', $', $diff_arg_names);
		eval("\$result = array_diff(".$diffArrString.");");
		return $result;
	}
}


?>