<?php
define_class(__FILE__, 'Import');
ini_set('max_execution_time', 300);
ini_set('memory_limit', '512M');
require_once ('class/DB/S_db2.php');
require_once ('class/DB/DBTable.php');

class Import {

	var $msdb = null;
	var $counter = 0;

	function Import()
	{
		//		echo '~'.mssql_connect ('209.98.199.153', 'mncar3', 'gomncar').'~ || ';
		//		//echo '~'.mssql_connect ('10.0.0.2', 'mncar3', 'gomncar').'~';
		//		vdie(0);

		$opt = array(
		'portability' => MDB2_PORTABILITY_ALL ^ MDB2_PORTABILITY_EMPTY_TO_NULL ^ MDB2_PORTABILITY_FIX_CASE);
		$this->msdb = &MDB2::factory(OLD_SITE_DB_DSN, $opt);
		if (PEAR::isError($this->msdb)) {
			$this->raiseError($this->msdb->getMessage());
		}
		$this->msdb->setFetchMode(MDB2_FETCHMODE_ASSOC);
		$this->db = S_db2::getInstance();
		mysql_query("SET NAMES utf8");
		mysql_query("SET CHARACTER SET utf8");
		mysql_query("SET SESSION collation_connection = 'utf8_general'");

		$this->member = new DBTable('Member');
		$this->member2specialty = new DBTable('Member2Specialty');
		$this->member2area = new DBTable('Member2Area');
		$this->member2lineofwork = new DBTable('Member2LineOfWork');
		$this->member2specialtyinfo = new DBTable('Member2SpecialtyInfo');
		$this->member2areainfo = new DBTable('Member2AreaInfo');
		$this->member2emailspecialty = new DBTable('Member2EmailSpecialty');
		$this->member2emailarea = new DBTable('Member2EmailArea');
		$this->member2emailtype = new DBTable('Member2EmailType');
		$this->member2volunteer = new DBTable('Member2Volunteer');
		$this->memberlink = new DBTable('MemberLink');
		$this->location = new DBTable('Location');
		$this->company = new DBTable('Company');
		$this->event = new DBTable('Event');
		$this->eventformreq = new DBTable('EventFormReq');
		//			$this->membernotemptyfields = array('email');
		$this->membermapping = array('id' => 'agent_id',
		//				'id_record_type' => '',
		//				'mcpe_id' => '',
		'nrds_id' => 'nrds_id',
		'id_member' => array('field' => 'mncar_member',
		'values' => array('Affiliate Member' => 1,
		'Other Association' => 2,
		'MNCAR/ADN Member' => 1,
		'MNCAR Member' => 1,
		'ADN Member' => 2,
		'Non-Member' => 1)),
		'id_member_type' => array('field' => 'mncar_member_type',
		'values' => array(10 => 1,
		2 => 2,
		5 => 3,
		8 => 4,
		14 => 5,
		99 => 0,
		25 => 7,
		50 => 8,
		27 => 'other',
		4 => 10,
		6 => 11,
		37 => 12,
		26 => 13,
		66 => 14,
		77 => 'other')),
		'id_location' => 'broker_id',
		//				'password' => '',
		'email' => 'email',
		//'inbox_type' => array('field' => 'email_type', 'values' => array('HTML' => 'html', null => 'text')),
		'id_status' => array('field' => 'current_status',
		'values' => array('Events Only' => 'other',
		'Active' => 1,
		'Suspended' => 'other',
		'Waived' => 'other',
		'CE Only' => 'other',
		'Inactive' => 2,
		'Pending' => 3,
		'Terminated' => 'other')),
		'status_date' => 'status_date_current',
		'allow_mcpe_access' => array('field' => 'dir_ce_member', 'values' => array(0 => 'n', 1 => 'y')),
		'id_mcpe_access_type' => 'dir_ce_member',
		//'id_current_mcpe_status' => array('field' => 'dir_ce_member', 'values' => array(0 => 2, 1 => 1)),
		//				'mcpe_status_date' => '',
		'salutation' => 'salutation',
		'first_name' => 'first_name',
		'middle_initial' => 'middle_initial',
		'last_name' => 'last_name',
		'generation' => 'generation',
		'designations' => 'designation',
		'birth_date' => 'birthdate_full',
		'industry_experience' => 'industry_experience_years',
		'address' => 'home_address',
		'city' => 'home_city',
		'state' => 'home_state',
		'zip' => 'home_zip',
		're_license_number' => 're_license_number',
		'primary_phone' => 'direct_phone',
		'secondary_phone' => 'cell_phone',
		'fax' => 'direct_fax',
		'website' => 'website',
		'note' => 'notes_full',
		'bio' => 'public_bio_full',
		'send_focus_newsletter' => array('field' => 'no_focus', 'values' => array(0 => 'y', 1 => 'n')),
		'other_line_of_work' => 'current_line_of_work_other',
		'other_specialty' => 'speciality_other',
		'other_area' => 'area_other',
		'email_signature' => 'email_signature_custom',
		//				'specific_senders' => '',
		'added' => 'very_last_updated',
		'changed' => 'very_last_updated',
		'img' => 'public_image');
		$this->member2areamapping = array(1 => 'minneapolis',
		2 => 'southeast',
		3 => 'southwest',
		4 => 'stpaul',
		5 => 'stpaulsuburban',
		6 => array('west', 'northwest'),
		7 => 'stcloud',
		8 => 'rochester',
		9 => array('east', 'northeast'));
		$this->member2specialtymapping = array(1 => 'industrial',
		2 => 'office',
		3 => 'land',
		4 => 'retail',
		5 => 'investment');
		$this->member2lineofworkmapping = array(1 => 'transacti',
		2 => 'consultin',
		3 => 'appraisal',
		4 => 'developme',
		5 => 'design');
		$this->member2areainfomapping = array(1 => 'Minneapolis',
		2 => 'Southeast',
		3 => 'Southwest',
		4 => 'St. Paul',
		7 => 'St Cloud',
		8 => 'Rochester',
		10 => 'Northeast',
		11 => 'Northwest',
		16 => 'Out of Metro');
		$this->member2specialtyinfomapping = array(1 => 'Industrial',
		2 => 'Office',
		3 => 'Land',
		4 => 'Retail',
		5 => 'Investment',
		6 => 'Appraisal',
		7 => 'Multi-Family',
		8 => 'Developer',
		9 => 'Hospitality',
		10 => 'Tenant Rep');
		$this->member2emailareamapping = array(7 => 'stcloud', 8 => 'rochester');
		$this->member2emailspecialtymapping = array(1 => 'industrial',
		2 => 'office',
		3 => 'land',
		4 => 'retail',
		5 => 'investment');
		$this->member2volunteermapping = array(1 => 'mbod',
		2 => 'ebod',
		3 => 'prco',
		4 => 'ebco',
		5 => 'awco',
		6 => 'spco',
		7 => 'goco',
		8 => 'exco',
		9 => 'coco',
		10 => 'weco',
		11 => 'crco',
		12 => 'dico',
		13 => 'sctf',
		14 => 'rotf',
		15 => 'dare');

		$this->locationmapping = array('id' => 'broker_id',
		//				'status' => '',3
		//				'id_company' => '',
		'nrds_id' => 'nrds_id',
		'name' => 'bkr_company',
		'id_type' => '',
		'phone' => 'bkr_phone',
		'fax' => 'bkr_fax',
		'website' => 'website',
		'contact_name' => '',
		'contact_phone' => 'bkr_phone_other',
		'physical_address' => 'bkr_address',
		'physical_city' => 'bkr_city',
		'physical_state' => 'bkr_state',
		'physical_zip' => 'bkr_zip',
		//'mailing_as_physical' => '',
		'mailing_address' => 'bkr_mail_address',
		'mailing_city' => 'bkr_mail_city',
		'mailing_state' => 'bkr_mail_state',
		'mailing_zip' => 'bkr_mail_zip',
		//				'allow_mcpe_access' => '',
		//				'id_mcpe_access_type' => '',
		//				'id_current_mcpe_status' => '',
		//				'mcpe_status_date' => '',
		//				'id_designated_realtor' => '',
		'note' => 'notes_full',
		'added' => 'last_updated',
		'changed' => 'last_updated');
		$this->companymapping = array('id' => 'mgmt_id', 'name' => 'mgmt_company', 'added' => 'last_updated', 'changed' => 'last_updated');
		$this->eventmapping = array('id' => 'event_id',
		//					'id_type' => '',
		'name' => 'event_name',
		'eventdate' => 'event_dte',
		//					'time_end' => '',
		//					'image' => '',
		//					'address' => '',
		'registration_limit' => 'registration_limit',
		'member_cost' => 'member_cost',
		'nonmember_cost' => 'nonmember_cost',
		//					'group_name' => '',
		//					'group_limit' => '',
		//					'registration_start' => '',
		//					'registration_end' => '',
		//					'exchange' => '',
		//					'education' => '',
		//					'id_form' => '',
		//					'title' => '',
		//					'metakeywords' => '',
		//					'metadescription' => '',
		//					'added' => '',
		//					'changed' => ''
		'description' => 'event_description_full');
		$this->eventformreqmapping = array(//					'id' => '',
		'id_event' => 'event_id',
		'id_member' => 'agent_id',
		//					'id_team' => '',
		//					'id_fm' => '',
		'payment_type' => array('field' => 'payment_type',
		'values' => array('Credit' => 1, 'MNCAR Credit' => 2, 'default' => 3)),
		'amount' => 'event_cost',
		//					'lang' => '',
		//					'ip' => '',
		//					'attended' => '',
		//					'deleted' => '',
		//					'changed' => ''
		'added' => 'date_associated');
	}

	function raiseError($error)
	{
		if (defined('DEBUG') && DEBUG) {
			die($error);
		}
	}

	function exec(&$Doc)
	{
		if (true) {
			$Doc->addContent(array(
				'tpl' => 'text.tpl',
				'text' => '<div style="text-align:center; font-weight:bold; padding-top:40px;">Import is disabled now</div>'));
		} else {
			$nLastImport = 0;
			if (!$fileBioHandler = fopen('importdata.bio', 'w')) vdie(1);
			if ($fileHandler = fopen('importdata.log', 'r')) $nLastImport = (int) fread($fileHandler, 10);
			if (isset($_REQUEST['submit_grp']['submit']) && time() - $nLastImport > 0/*15 * 60*/) {
				mail('mncar_import@bublik.fr-wd.com', 'Import started at ' . $_SERVER['HTTP_HOST'] . $_SERVER['REQUEST_URI'], date('d/m/Y H:i:s') . "\n\n".$_SERVER['REMOTE_ADDR']);
				if (!$fileHandler = fopen('importdata.log', 'w')) vdie('could not create importfilelog');
				fwrite($fileHandler, time());
				error_reporting(E_ALL);

				$this->msdb->query('SET TEXTSIZE 65536');

				$res = $this->msdb->query(
				'SELECT agent_id, birthdate FROM agent a LEFT JOIN person p ON a.person_id=p.person_id
				WHERE agent_id NOT IN (SELECT agent_id FROM stand_agent)
					ORDER BY agent_id');

				if (PEAR::isError($res)) {
					$this->raiseError($res->getMessage());
				}

				while (($aRow = $res->fetchRow())) {
					if ($aRow['birthdate']) {
						$aBirthDate=split('/',$aRow['birthdate']);
						$aMemberUpdateData=array('birth_date'=>'2000-'.(int)$aBirthDate[0].'-'.(int)$aBirthDate[1]);
						$cTimeAdded=$this->db->queryOne('SELECT TIME_FORMAT(added,"%H%i%s") FROM mn_Member WHERE id='.$aRow['agent_id']);
						if ($cTimeAdded=="000000") $this->member->update($aMemberUpdateData,'id='.$aRow['agent_id']);
						echo (++$i).'. Update Agent#'.$aRow['agent_id'].' - '.$aMemberUpdateData['birth_date'].'<br>';
					}
				}

//				$res = $this->msdb->query(
//				'SELECT *, case when a.last_updated > p.last_updated then a.last_updated else p.last_updated end as very_last_updated,
//						convert(text,notes) notes_full, CAST(public_bio AS text) AS public_bio_full, (SELECT COUNT(event_id) FROM agent_events ae WHERE ae.agent_id=a.agent_id) AS event_count
//				FROM agent a LEFT JOIN person p ON a.person_id=p.person_id
//					LEFT JOIN users u ON u.person_id=p.person_id
//				WHERE agent_id NOT IN (SELECT agent_id FROM stand_agent)
//					ORDER BY agent_id');
//	//			WHERE agent_id NOT IN (SELECT agent_id FROM stand_agent)
//
//				if (PEAR::isError($res)) {
//					$this->raiseError($res->getMessage());
//				}
//
//				$this->member->delete('1');
//				$this->member2area->delete('1');
//				$this->member2specialty->delete('1');
//				$this->member2lineofwork->delete('1');
//				$this->member2areainfo->delete('1');
//				$this->member2specialtyinfo->delete('1');
//				$this->member2emailarea->delete('1');
//				$this->memberlink->delete('1');
//				$this->member2emailspecialty->delete('1');
//				$this->member2volunteer->delete('1');
//				if (!$fileHandler = fopen('agent_import.csv', 'w')) vdie('could not create filelog for agent import');
//				fwrite($fileHandler, "Agent ID#,CE Member,CURRENT STATUS,Email,NRDS#,Person ID#");
//				foreach ($this->membermapping as $From)
//				if (is_array($From)) fwrite($fileHandler, "," . $From['field']);
//				fwrite($fileHandler, "\n");
//				if (!$fileHandlerIgnored = fopen('agent_import_ignored.csv', 'w')) vdie('could not create filelog for agent import');
//				fwrite($fileHandlerIgnored, "Agent ID#,CE Member,CURRENT STATUS,Email,NRDS#,Person ID#");
//				foreach ($this->membermapping as $From)
//				if (is_array($From)) fwrite($fileHandlerIgnored, "," . $From['field']);
//				fwrite($fileHandlerIgnored, "\n");
//				echo '<br><b>members</b><br>';
//				$aMncarMemberTypes=array(10 => 'Designated Realtor',
//				2 => 'Realtor',
//				5 => 'Secondary Member',
//				8 => 'Senior Member',
//				14 => 'Paid Non-Member',
//				99 => 'Alternate Association Member',
//				25 => 'MNCAR Staff',
//				50 => 'Focus',
//				27 => 'Support Staff',
//				4 => 'Affiliate',
//				6 => 'Institute Affiliate',
//				37 => 'Community/Economic Development Affiliate',
//				26 => 'Designated Affiliate',
//				66 => 'Events Only',
//				77 => 'Archive');
//				$aDuplicatedAgentsIDs=array();
//				$aDuplicatedAgentsEmails=array();
//				$aOtherMember=array();
//
//				while (($aRow = $res->fetchRow())) {
//					//				if (!$fileDataHandler = fopen('import.data', 'w')) vdie(1);
//					//				fwrite($fileDataHandler,implode("#\t#",$aRow));
//					//			}
//					//			vdie();
//					//			while (($aRow = $res->fetchRow())) {
//					$aRow['email']=trim($aRow['email']);
//					/*if ($aRow['current_status']=='Events Only' || $aRow['current_status']=='Inactive') $file=$fileHandlerIgnored;
//					else*/ $file=$fileHandler;
//					if (!$this->db->queryOne('SELECT COUNT(id) FROM mn_Member WHERE id=' . (int) $aRow['agent_id'])) {
//						if (!$aRow['nrds_id'] || !$this->db->queryOne('SELECT COUNT(id) FROM mn_Member WHERE nrds_id=' . $this->db->quote($aRow['nrds_id']))) {
//							$cMsg = '';
//							$aMemberData = array();
//							foreach ($this->membermapping as $cTo => $From) {
//								if (is_array($From)) {
//									$cMsg .= ',';
//									$aMemberData[$cTo] = $From['values'][$aRow[$From['field']]];
//									if ($aMemberData[$cTo] === 'other') $aRow['other']=1;
//								} else {
//									$aMemberData[$cTo] = $aRow[$From];
//								}
//								if (!$aMemberData[$cTo]) $aMemberData[$cTo] = '';
//							}
//							if ($aRow['home_zip_4']) $aMemberData['zip'] .= '-' . $aRow['home_zip_4'];
//							$aMemberData['primary_phone_title'] = 'Office';
//							$aMemberData['secondary_phone_title'] = 'Mobile';
//							$aMemberData['alternate_contact_phone_title'] = 'Other';
//							if (!$aMemberData['email']) $aMemberData['email']='email_'.$aMemberData['id'].'@mncar.org';
//							$aMemberData['primary_phone'] = format_phone($aMemberData['primary_phone'].$aRow['direct_phone_extension']);
//							$aMemberData['secondary_phone'] = format_phone($aMemberData['secondary_phone']);
//							$aMemberData['fax'] = format_phone($aMemberData['fax']);
//							$aMemberData['bio']=iconv("CP1252","UTF-8", nl2br($aMemberData['bio']));
//							$aMemberData['alternate_contact_phone'] = format_phone($aMemberData['alternate_contact_phone']);
//							if (!$aRow['alternate_contact_yn']) {
//								unset($aMemberData['alternate_contact_name']);
//								unset($aMemberData['alternate_contact_phone']);
//								unset($aMemberData['alternate_contact_email']);
//							}
//							if (!(int)$aMemberData['birth_date']) unset($aMemberData['birth_date']);
//							if (!(int)$aMemberData['nrds_id']) unset($aMemberData['nrds_id']);
//
//							if ($aMemberData['id_member_type']==14) {
//								$aMemberData['id_member_type']=0;
//								$aMemberData['id_member']=3;
//								$aMemberData['id_status']=0;
//								$aMemberData['allow_mcpe_access']='n';
//								$aMemberData['id_mcpe_access_type']=0;
//								$cMsg='';
//							} else
//							if ($aMemberData['id_member_type']==8) {
//								$aMemberData['id_member_type']=0;
//								$aMemberData['id_member']=7;
//								$aMemberData['allow_mcpe_access']='n';
//								$aMemberData['id_mcpe_access_type']=0;
//							}
//							if ($aMemberData['id_member_type']==7) {
//								$aMemberData['allow_mcpe_access']='y';
//								$aMemberData['id_mcpe_access_type']=3;
//							}
//							if ($aMemberData['id_member']==2) {
//								$aMemberData['id_member_type']=0;
//								$aMemberData['id_status']=0;
//								if ($aMemberData['id_mcpe_access_type'])
//									$aMemberData['id_mcpe_access_type']=$aRow['mncar_member']=='ADN Member'?2:1;
//								if ($aRow['mncar_member']=='Other Association' && $aRow['mncar_member_type']=99 && $aRow['current_status']=='Inactive') {
//									$aMemberData['allow_mcpe_access']='n';
//									$aMemberData['id_mcpe_access_type']=0;
//								}
//							}
//							if ($aRow['mncar_member']=='ADN Member')
//							if (in_array($aMemberData['id_member_type'],array(5,10,11,13))) {
//								$aMemberData['allow_mcpe_access']='n';
//								$aMemberData['id_mcpe_access_type']=0;
//							}
//							if ($aRow['mncar_member']=='MNCAR/ADN Member' && $aMemberData['id_mcpe_access_type']){
//								$aMemberData['id_mcpe_access_type']=2;
//							}
//							if (
//							$aRow['current_status']!='Active' &&
//							$aMemberData['id_member']!=3 &&
//								($aRow['current_status']!='Pending' ||
//								(
//									$aRow['mncar_member']!='MNCAR/ADN Member' && $aMemberData['id_member_type']!=1 && $aMemberData['id_member_type']!=2)
//								)
//							) {
//								$aRow['other'] = 1;
//							}
//							if ($aRow['other'] == 1 && !$aRow['event_count']) {
//								$aMemberData['id_status']=2;
//								$aOtherMember[]=array('member'=>$aMemberData,'row'=>$aRow);
//							} else {
//								if ($aRow['other'] == 1) {
//									$aMemberData['id_member']=3;
//									$aMemberData['id_status']=0;
//								}
//								if (!trim($cMsg, ',')) {  //&& !$lIgnore
//									if (!$this->db->queryOne('SELECT COUNT(id) FROM mn_Member WHERE email=' . $this->db->quote($aRow['email']))) {
//										$this->insertMember($aMemberData, $aRow);
//									} else {
//										if ($aRow['email']) {
//											fwrite($file, "{$aRow['agent_id']},".($aRow['dir_ce_member']?'Yes':'No').",{$aRow['current_status']},{$aRow['email']} - duplicated\n");
//											$aDuplicatedAgentsEmails[]='"'.$aRow['email'].'"';
//										} else fwrite($file, "{$aRow['agent_id']},".($aRow['dir_ce_member']?'Yes':'No').",{$aRow['current_status']},empty\n");
//									}
//								} else {
//									fwrite($file, "{$aRow['agent_id']},".($aRow['dir_ce_member']?'Yes':'No').",{$aRow['current_status']},,,{$cMsg}\n");
//								}
//							}
//						} else {
//							fwrite($file, "{$aRow['agent_id']},".($aRow['dir_ce_member']?'Yes':'No').",{$aRow['current_status']},,{$aRow['nrds_id']} - duplicated\n");
//						}
//					} else {
//						fwrite($file, "{$aRow['agent_id']},".($aRow['dir_ce_member']?'Yes':'No').",{$aRow['current_status']},,,{$aRow['person_id']} - duplicated\n");
//						$aDuplicatedAgentsIDs[]=$aRow['agent_id'];
//					}
//				}
//				echo 'othermembers';
//				/*Other (non-standatized members)*/
//				foreach ($aOtherMember as $aMember) {
//					if (!$this->db->queryOne('SELECT COUNT(id) FROM mn_Member WHERE id=' . (int) $aMember['member']['id'])) {
//						if (!$aMember['member']['nrds_id'] || !$this->db->queryOne('SELECT COUNT(id) FROM mn_Member WHERE nrds_id=' . $this->db->quote($aMember['member']['nrds_id']))) {
//							if (!$this->db->queryOne('SELECT COUNT(id) FROM mn_Member WHERE email=' . $this->db->quote($aMember['member']['email']))) {
//								$this->insertMember($aMember['member'],$aMember['row']);
//							} else {
//								if ($aMember['row']['mncar_member_type']==66) {
//									fwrite($file, "{$aMember['row']['agent_id']},".($aMember['row']['dir_ce_member']?'Yes':'No').",{$aMember['row']['current_status']},{$aMember['row']['email']} - duplicated\n");
//									$aDuplicatedAgentsEmails[]='"'.$aMember['row']['email'].'"';
//								} else {
//									fwrite($fileHandlerIgnored, "{$aMember['row']['agent_id']},".($aMember['row']['dir_ce_member']?'Yes':'No').",{$aMember['row']['current_status']},{$aMember['row']['email']} - duplicated\n");
//								}
//							}
//						} else {
//							if ($aMember['row']['mncar_member_type']==66) {
//								fwrite($file, "{$aMember['row']['agent_id']},".($aMember['row']['dir_ce_member']?'Yes':'No').",{$aMember['row']['current_status']},,{$aMember['row']['nrds_id']} - duplicated\n");
//							} else {
//								fwrite($fileHandlerIgnored, "{$aMember['row']['agent_id']},".($aMember['row']['dir_ce_member']?'Yes':'No').",{$aMember['row']['current_status']},,{$aMember['row']['nrds_id']} - duplicated\n");
//							}
//						}
//					} else {
//						if ($aMember['row']['mncar_member_type']==66) {
//							fwrite($file, "{$aMember['row']['agent_id']},".($aMember['row']['dir_ce_member']?'Yes':'No').",{$aMember['row']['current_status']},,,{$aMember['row']['person_id']} - duplicated\n");
//							$aDuplicatedAgentsIDs[]=$aMember['row']['agent_id'];
//						} else {
//							fwrite($fileHandlerIgnored, "{$aMember['row']['agent_id']},".($aMember['row']['dir_ce_member']?'Yes':'No').",{$aMember['row']['current_status']},,,{$aMember['row']['person_id']} - duplicated\n");
//						}
//					}
//				}
//
//				if (count($aDuplicatedAgentsIDs) || count($aDuplicatedAgentsEmails)) {
//					$cWhere='WHERE ';
//					if (count($aDuplicatedAgentsIDs)) $cWhere.='agent_id IN ('.implode(',',$aDuplicatedAgentsIDs).')';
//					if (count($aDuplicatedAgentsIDs) && count($aDuplicatedAgentsEmails)) $cWhere.=' OR ';
//					if (count($aDuplicatedAgentsEmails)) $cWhere.='email IN ('.implode(',',$aDuplicatedAgentsEmails).')';
//					$res = $this->msdb->query('SELECT nrds_id, first_name, last_name, mgmt_company, direct_phone, email, current_status, user_id, a.agent_id, a.person_id
//						FROM agent a LEFT JOIN person p ON a.person_id=p.person_id
//							LEFT JOIN users u ON u.person_id=p.person_id
//							LEFT JOIN management m ON a.mgmt_id=m.mgmt_id
//							'.$cWhere.'
//							ORDER BY agent_id');
//					if (PEAR::isError($res)) {
//						$this->raiseError($res->getMessage());
//					}
//
//					if (!$fileHandler = fopen('agent_duplicated_import.csv', 'w')) vdie('could not create filelog for agent(duplicated) import');
//					fwrite($fileHandler, "NRDS#, Name, Company, Phone, Email, Status, User#, Agent#, Person#\n");
//					while (($aRow = $res->fetchRow())) fwrite($fileHandler, $aRow['nrds_id'].",".$aRow['first_name']." ".$aRow['last_name'].", ".$aRow['mgmt_company'].", ".$aRow['direct_phone'].", ".$aRow['email'].", ".$aRow['current_status'].", ".$aRow['user_id'].", ".$aRow['agent_id'].", ".$aRow['person_id']."\n");
//					fclose($fileHandler);
//				}
//
//				$this->db->query('DELETE FROM  mn_zz_File WHERE name IN (SELECT DISTINCT `img` FROM `mn_Member` WHERE `img`)');
//				$this->db->query(
//				'INSERT INTO mn_zz_File SELECT DISTINCT NULL , `img` , `img` , 0, "image", NOW( ) , NOW( ) FROM `mn_Member` WHERE `img`');
//				$aAllImages = $this->db->queryAll('SELECT id, name FROM mn_zz_File WHERE size=0');
//				$this->db->query('INSERT INTO mn_Member2EmailArea SELECT m2es.id_parent, at2a.id_area_type, at2a.id_area
//					FROM  mn_Member2EmailSpecialty m2es INNER JOIN mn_DAreaType2Area at2a ON m2es.id_specialty=at2a.id_area_type
//						WHERE at2a.id_area_type=1 OR at2a.id_area_type=2');
//				$this->db->query('INSERT INTO mn_Member2EmailArea SELECT m2es.id_parent, at2a.id_area_type, at2a.id_area
//					FROM  mn_Member2EmailSpecialty m2es INNER JOIN mn_DAreaType2Area at2a ON m2es.id_specialty=4 AND at2a.id_area_type=3');
//				foreach ($aAllImages as $aImage) {
//					$aSize = getimagesize(WWW_PATH . '/userfile/' . $aImage['name']);
//					$this->db->query('UPDATE mn_zz_File SET type=' . $this->db->quote($aSize['type']) . ' WHERE id=' . $this->db->quote($aImage['id']));
//					$this->db->query('DELETE FROM mn_zz_FileOpt WHERE id_file=' . $this->db->quote($aImage['id']));
//					$this->db->query(
//					'INSERT INTO mn_zz_FileOpt VALUE(' . $this->db->quote($aImage['id']) . ',"width",' . $this->db->quote($aSize[0]) . ')');
//					$this->db->query(
//					'INSERT INTO mn_zz_FileOpt VALUE(' . $this->db->quote($aImage['id']) . ',"height",' . $this->db->quote($aSize[1]) . ')');
//				}
//				fclose($fileHandler);
//				fclose($fileHandlerIgnored);
//				$res = $this->msdb->query('SELECT *, convert(text,notes) notes_full FROM broker');
//				if (PEAR::isError($res)) {
//					$this->raiseError($res->getMessage());
//				}
//				$this->location->delete('1');
//				if (!$fileHandler = fopen('broker_import.csv', 'w')) vdie('could not create filelog for broker import');
//				fwrite($fileHandler, "Broker ID#,NRDS#,Error\n");
//				echo '<br><b>locations</b><br>';
//				while (($aRow = $res->fetchRow())) {
//					if ($this->db->queryOne('SELECT COUNT(id) FROM mn_Member WHERE id_location=' . (int) $aRow['broker_id'])) {
//						if (!$aRow['nrds_id'] || !$this->db->queryOne('SELECT COUNT(id) FROM mn_Location WHERE nrds_id=' . $this->db->quote($aRow['nrds_id']))) {
//							$cMsg = '';
//							$aLocationData = array();
//							foreach ($this->locationmapping as $cTo => $From) {
//								$aLocationData[$cTo] = $aRow[$From];
//								if (!$aLocationData[$cTo]) $aLocationData[$cTo] = '';
//							}
//							if ($aRow['bkr_zip_4']) $aLocationData['physical_zip'] .= '-' . $aRow['bkr_zip_4'];
//							if ($aRow['bkr_mail_zip_4']) $aLocationData['mailing_zip'] .= '-' . $aRow['bkr_mail_zip_4'];
//							$aLocationData['id_type'] = 3;
//							$aLocationData['id_company'] = 1;
//							$aLocationData['phone'] = format_phone($aLocationData['phone']);
//							$aLocationData['fax'] = format_phone($aLocationData['fax']);
//							$aLocationData['contact_phone'] = format_phone($aLocationData['contact_phone']);
//							if (!(int)$aLocationData['nrds_id']) unset($aLocationData['nrds_id']);
//							$this->location->insert($aLocationData);
//							if ($counter++ % 10 == 0) echo '. ';
//						} else {
//							fwrite($fileHandler, $aRow['broker_id'] . ", {$aRow['nrds_id']} - duplicated\n");
//						}
//	//				} else {
//	//					fwrite($fileHandler, $aRow['broker_id'] . ",,no individuals imported\n");
//					}
//				}
//				$this->company->delete('1');
//				$this->company->insert(array('id' => 1, 'name' => 'Default Company'));
//
//				$this->db->query("TRUNCATE TABLE `mn_NRDS`");
//				$this->db->query("INSERT INTO mn_NRDS
//					SELECT nrds_id, id, 'individual', IF(nrds_id BETWEEN 502200001 AND 502299999, 'n', 'y'), NOW()
//					FROM mn_Member
//					WHE	RE nrds_id IS NOT NULL");
//				$this->db->query("INSERT INTO mn_NRDS
//					SELECT nrds_id, id, 'location', IF(nrds_id BETWEEN 502200001 AND 502299999, 'n', 'y'), NOW()
//					FROM mn_Location
//					WHERE nrds_id IS NOT NULL");

//				$res = $this->msdb->query('SELECT *, convert(text,event_description) event_description_full FROM events');
//				if (PEAR::isError($res)) {
//					$this->raiseError($res->getMessage());
//				}
//				$this->event->delete('1');
//				echo '<br><b>events</b><br>';
//				while (($aRow = $res->fetchRow())) {
//					$aEventData = array();
//					foreach ($this->eventmapping as $cTo => $From) {
//						$aEventData[$cTo] = $aRow[$From];
//						if (!$aEventData[$cTo]) $aEventData[$cTo] = '';
//					}
//					$aEventData['id_type'] = 2;
//					$aEventData['description']=str_replace('../../images/','{BASE_URL}/img/mncar/',$aEventData['description']);
//					$aEventData['description_vcs']=html_entity_decode(strip_tags(str_replace(array("&nbsp;","<br />","</p>"),array(" ","\n\r","</p>\n\r"),$aEventData['description'])));
//					if (strpos($aEventData['name'], 'rokerage') !== false) $aEventData['id_type'] = 3;
//					if (in_array($aEventData['id'], array(214, 295, 383, 576))) $aEventData['id_type'] = 4;
//					$this->event->insert($aEventData);
//					if ($counter++ % 10 == 0) echo '. ';
//				}
//				$this->event->update(array('education' => 1), "`name` LIKE '% CE %'");
//				$this->event->update(array('education' => 1), "`name` LIKE '% education %'");
//				$this->event->update(array('exchange' => 1), "`name` LIKE '% exchange %'");
//				$this->event->update(array('exchange' => 1), "`name` LIKE '% exchange %'");
//				$this->db->query('UPDATE `mn_Event` SET `eventdate`="2001-01-01 00:00:00" WHERE `eventdate`="0000-00-00 00:00:00"');
//				$this->db->query('UPDATE `mn_Event` SET `registration_end`=`eventdate`, `registration_start`=`eventdate`- INTERVAL 1 MONTH');
//				$this->db->query('UPDATE mn_Event SET doc_number = (SELECT doc_id FROM `event2doc` WHERE event_id=mn_Event.id), education=1
//	WHERE mn_Event.id IN (SELECT event_id FROM `event2doc`)');
//				$this->db->query('UPDATE mn_Event SET ce_credits = (SELECT ce_hours FROM `mn_EventDocumentofCommerce` WHERE doc_number=mn_Event.doc_number) WHERE doc_number<>""');
//
//				if (!$fileHandler = fopen('agent_event_import.csv', 'w')) vdie('could not create filelog for agentevent import');
//				fwrite($fileHandler, "EventID#,# of Imported Agents,# of Agents not imported\n");
//				$res = $this->msdb->query('SELECT agent_events.* FROM agent_events INNER JOIN agent ON agent_events.agent_id=agent.agent_id WHERE event_id=579 OR event_id=695 OR event_id=675');
////				$res = $this->msdb->query('SELECT agent_events.* FROM agent_events INNER JOIN agent ON agent_events.agent_id=agent.agent_id');
//				if (PEAR::isError($res)) {
//					$this->raiseError($res->getMessage());
//				}
//				$this->eventformreq->delete('id_event=579 OR id_event=695 OR id_event=675)');
////				$this->eventformreq->delete('1');
//				echo '<br><b>event reqs</b><br>';
//				$aResut = array();
//				while (($aRow = $res->fetchRow())) {
//					if (!isset($aResut[$aRow['event_id']])) $aResut[$aRow['event_id']] = array('imported' => 0, 'nonimported' => 0);
//					if ($this->db->queryOne('SELECT COUNT(id) FROM mn_Member WHERE id=' . (int) $aRow['agent_id'])) {
//						$aEventFormReqData = array();
//						foreach ($this->eventformreqmapping as $cTo => $From) {
//							if (is_array($From)) {
//								$aEventFormReqData[$cTo] = $From['values'][$aRow[$From['field']]];
//								if ($aMemberData[$cTo] == 'error' || !isset($From['values'][$aRow[$From['field']]])) {
//									$aEventFormReqData[$cTo]=$From['values']['default'];
//								}
//							} else {
//								$aEventFormReqData[$cTo] = $aRow[$From];
//							}
//
//							if (!$aEventFormReqData[$cTo]) $aEventFormReqData[$cTo] = '';
//						}
//						$aResut[$aRow['event_id']]['imported']++;
//						$aEventFormReqData['attended']=1-(int)$aRow['no_show'];
//						$this->eventformreq->insert($aEventFormReqData);
//						if ($counter++ % 100 == 0) echo '. ';
//					} else {
//						$aResut[$aRow['event_id']]['nonimported']++;
//					}
//				}
//				$this->db->query('DELETE FROM mn_EventCECreditHistory');
//				$this->db->query('INSERT INTO mn_EventCECreditHistory SELECT NULL, id_member, id_event, eventdate, name, ce_credits, NOW(), NOW()
//					FROM mn_EventFormReq efr INNER JOIN mn_Event e ON efr.id_event=e.id
//					WHERE ce_credits>0');

//				foreach ($aResut as $nEventID => $aI)
//				fwrite($fileHandler, "$nEventID,{$aI['imported']},{$aI['nonimported']}\n");

				/*
				if (!$fileHandler = fopen('agent_without_event.csv', 'w')) vdie('could not create filelog for notaventagent import');
				fwrite($fileHandler, "AgentID#,Agent Name\n");
				$aAgents = $this->db->queryAll(
				'SELECT id, first_name, last_name FROM mn_Member WHERE id_member=3 AND id NOT IN (SELECT DISTINCT id_member FROM mn_EventFormReq)');
				foreach ($aAgents as $aAgent)
				fwrite($fileHandler, "{$aAgent['id']},{$aAgent['first_name']} {$aAgent['last_name']}\n");
				$this->db->query('DELETE FROM mn_Member WHERE id_member=3 AND id NOT IN (SELECT DISTINCT id_member FROM mn_EventFormReq)');
				*/
				$Doc->addContent(
				array('tpl' => 'center_link.tpl',
				'separator' => '<br><br>',
				'link_list' => array(array('link' => 'agent_import.csv', 'title' => 'Member Import Log (contain members, that were NOT imported - csv format)'),
				array('link' => 'agent_without_event.csv', 'title' => 'Agent Without Events (contain members, that were NOT imported, because they has no events - csv format)'),
				array('link' => 'agent_duplicated_import.csv', 'title' => 'Duplicated Agents (csv format)'),
				array('link' => 'broker_import.csv', 'title' => 'Location Import Log (contain location, that were NOT imported - csv format)'),
				array('link' => 'agent_event_import.csv', 'title' => 'Event Attedance Import Log (contains events and # of imported and nonimported attendees - csv format)'))));
				if (!$fileHandler = fopen('importdata.log', 'w')) vdie('could not create importfilelog');
				fwrite($fileHandler, 0);
				mail('mncar_import@bublik.fr-wd.com', 'Import finished at ' . $_SERVER['HTTP_HOST'] . $_SERVER['REQUEST_URI'], date('d/m/Y H:i:s') . "\n\n".$_SERVER['REMOTE_ADDR']);
			} else {
				if (time() - $nLastImport < 15 * 60) {
					$Doc->addContent(array('tpl' => 'text.tpl', 'text' => 'Another import script running now. Please wait while it complete'));
				} else {
					require_once 'class/Form/Form.class.php';
					$form = new Form($Doc);
					$form->setTitle('WARNING: All members/locations/companies/events data would be deleted!');
					$form->setSubmitTitle('Import');
					$form->exec();
				}
				$Doc->addContent(array('tpl' => 'text.tpl', 'text' => '<b>Logfiles of previous import:</b>'));
				$Doc->addContent(
				array('tpl' => 'center_link.tpl',
				'separator' => '<br><br>',
				'link_list' => array(array('link' => 'agent_import.csv', 'title' => 'Member Import Log (contain members, that were NOT imported - csv format)'),
				//									array('link' => 'agent_without_event.csv', 'title' => 'Agent Without Events (contain members, that were NOT imported, because they has no events - csv format)'),
				array('link' => 'agent_duplicated_import.csv', 'title' => 'Duplicated Agents (csv format)'),
				array('link' => 'broker_import.csv', 'title' => 'Location Import Log (contain location, that were NOT imported - csv format)'),
				array('link' => 'agent_event_import.csv', 'title' => 'Event Attedance Import Log (contains events and # of imported and nonimported attendees - csv format)'))));
			}
		}
	}

	function insertMember($aMemberData, $aRow)
	{
		$this->updatebirthdate($aMemberData, $aRow);
		return;
		if ($this->counter++ % 10 == 0) echo '. ';
		if (!$aMemberData['password']) $aMemberData['password']='123';
		$this->member->insert($aMemberData);
		$cInsertedBio=$this->db->queryOne('SELECT bio FROM mn_Member WHERE id='.(int)$aMemberData['id']);
		if (strlen($cInsertedBio)!=strlen($aMemberData['bio'])) fwrite($fileBioHandler,$aMemberData['id']."\n");
		dbinsert($this->db, 'mn_Member', $aMemberData);

		$aMember2AreaData = array();
		foreach ($this->member2areamapping as $id_area => $From) {
			$insert = false;
			if (is_array($From)) {
				foreach ($From as $Field)
				if ($aRow['area_' . $Field]) $insert = true;
			} elseif ($aRow['area_' . $From]) $insert = true;
			if ($insert) $this->member2area->insert(array('id_parent' => $aMemberData['id'], 'id_area' => $id_area));
		}
		foreach ($this->member2specialtymapping as $id_specialty => $From) {
			if ($aRow['specialty_' . $From]) $this->member2specialty->insert(
			array('id_parent' => $aMemberData['id'], 'id_specialty' => $id_specialty));
		}
		foreach ($this->member2lineofworkmapping as $id_line_of_work => $From) {
			//									echo $id_line_of_work . '~' . $From . '~' . $aRow['current_line_of_work_' . $From] . '~' . 'current_line_of_work_' . $From;
			//									vdie($aRow);
			if ($aRow['current_line_of_work_' . $From]) $this->member2lineofwork->insert(
			array('id_parent' => $aMemberData['id'], 'id_line_of_work' => $id_line_of_work));
		}
		foreach ($this->member2areainfomapping as $id_area => $From) {
			if (strpos($aRow['area'],"' ".$From."'")) $this->member2areainfo->insert(
			array('id_parent' => $aMemberData['id'], 'id_area_info' => $id_area));
		}
		foreach ($this->member2specialtyinfomapping as $id_specialty => $From) {
			if (strpos($aRow['specialty'],"' ".$From."'")) $this->member2specialtyinfo->insert(
			array('id_parent' => $aMemberData['id'], 'id_specialty_info' => $id_specialty));
		}
		for($i = 1; $i < 5; $i++) {
			if ($aRow['public_link' . $i]) $this->memberlink->insert(
			array('id_parent' => $aMemberData['id'],
			'address' => $aRow['public_link' . $i],
			'title' => $aRow['public_link_name' . $i] ? $aRow['public_link_name' . $i] : ''));
		}
		foreach ($this->member2emailareamapping as $id_area => $From) {
			if ($aRow['email_' . $From]) $this->member2emailarea->insert(
			array('id_parent' => $aMemberData['id'], 'id_area_type' => 4, 'id_area' => $id_area));
		}
		if ($aMemberData['allow_mcpe_access']=='y' && ($aMemberData['id_member']==2 || ($aMemberData['id_member']==1 && (in_array($aMemberData['id_member_type'],array(1,2,3,4,7,12))))))
			$this->member2emailarea->insert(
				array('id_parent' => $aMemberData['id'], 'id_area_type' => 4, 'id_area' => 14));
		$aIDAreaTypes=array();
		foreach ($this->member2emailspecialtymapping as $id_specialty => $From) {
			if ($aRow['email_' . $From]) {
				$this->member2emailspecialty->insert(array('id_parent' => $aMemberData['id'], 'id_specialty' => $id_specialty));
				if ($id_specialty<5 && $id_specialty!=3) $aIDAreaTypes[]=$id_specialty;
			}
		}
		if ($aMemberData['allow_mcpe_access'] && $aRow['active']) {
			if (count($aIDAreaTypes)) $this->db->query('INSERT INTO mn_Member2EmailArea SELECT '.$aMemberData['id'].', id_area_type, id_area FROM mn_DAreaType2Area WHERE id_area_type IN ('.implode(','.$aIDAreaTypes).')');
			$this->db->query('INSERT INTO mn_Member2EmailType SELECT '.$aMemberData['id'].', id FROM mn_DEmailType');
		}
		foreach ($this->member2volunteermapping as $id_volunteer => $From) {
			if ($aRow['volunteer_' . $From]) $this->member2volunteer->insert(
			array('id_parent' => $aMemberData['id'], 'id_volunteer' => $id_volunteer));
		}
	}

}

function format_phone($cPhone)
{
	if (!$cPhone) return '';
	$aP = str_split(preg_replace('/\D/', '', $cPhone));
	$cPhone = '(' . $aP[0] . $aP[1] . $aP[2] . ') ' . $aP[3] . $aP[4] . $aP[5] . '-' . $aP[6] . $aP[7] . $aP[8] . $aP[9];
	if (count($aP) > 10) $cPhone .= ' ext. ';
	for($i = 10; $i < count($aP); $i++)
	$cPhone .= $aP[$i];
	return $cPhone;
}

function dbinsert ($db, $cTabe, $aData)
{
	$sql='INSERT INTO `'.$cTabe.'` SET ';
	foreach ($aData as $cField=>$cValue) $sql.='`'.mysql_real_escape_string($cField).'`="'. mysql_real_escape_string($cValue).'", ';
	$db->query(substr($sql,0,-2));
}