<?php
/**
 * Addresses & People (Contacts) Import functions
 *
 * @package Students Import module
 * @subpackage includes
 */


/**
 * Echoes Addresses & People form
 *
 * @uses _addressForm()
 * @uses _peopleForm()
 */
function AddressesPeopleForm()
{

	ob_start();

	echo '<table class="widefat cellspacing-0 center" id="student-address-table" style="float: left; clear: none; margin-right: 8px;">';

	_addressForm( 'student', dgettext( 'Students_Import', 'Student\'s Address' ) );

	echo '</table>';

	$student_address = ob_get_clean();

	ob_start();

	echo '<table class="widefat cellspacing-0 center people-table" style="float: left; clear: none; margin-right: 8px;">';

	_peopleForm( 'contactid0', _( 'Contact' ) );

	echo '</table>';

	$contact = ob_get_clean();

	?>
	<script>
		function addStudentAddress(el) {
			var studentAddressHTML = <?php echo json_encode( $student_address ); ?>;

			// Add Contact at the end of the Addresses & People table.
			addHTML( studentAddressHTML, 'addresses-people-td' );

			$(el).remove();

			$('#add-contact-link').show();

			$('.chosen-select').chosen('.chosen-select');
		}

		var contactNumber = 0;

		function addContact() {
			var contactHTML = <?php echo json_encode( $contact ); ?>,
				newContactHTML = contactHTML;

			if ( contactNumber ) {
				newContactHTML = contactHTML.replace(
					new RegExp('contactid' + (contactNumber - 1 ), 'g'),
					'contactid' + contactNumber
				);
			}

			// Add Contact at the end of the Addresses & People table.
			addHTML( newContactHTML, 'addresses-people-td' );

			contactNumber++;

			$('.chosen-select').chosen('.chosen-select');
		}
	</script>
	<?php
}


/**
 * Address form
 * Includes Address custom fields.
 *
 * @param  string $array addresses array key.
 * @param  string $title Address fields section title.
 */
function _addressForm( $array, $title )
{
	global $csv_columns;

	/**
	 * Address Fields.
	 */
	if ( $title )
	{
		echo '<tr><td><h4>' . $title . '</h4></td></tr>';
	}

	echo '<tr><td>' .
		_makeSelectInput( 'ADDRESS', $csv_columns,  _( 'Street' ), 'required', true, 'addresses[' . $array . ']' ) .
	'</td></tr>';

	echo '<tr><td>' .
		_makeSelectInput( 'CITY', $csv_columns,  _( 'City' ), '', true, 'addresses[' . $array . ']' ) .
	'</td></tr>';

	echo '<tr><td>' .
		_makeSelectInput( 'STATE', $csv_columns,  _( 'State' ), '', true, 'addresses[' . $array . ']' ) .
	'</td></tr>';

	echo '<tr><td>' .
		_makeSelectInput( 'ZIPCODE', $csv_columns,  _( 'Zip' ), '', true, 'addresses[' . $array . ']' ) .
	'</td></tr>';

	echo '<tr><td>' .
		_makeSelectInput( 'PHONE', $csv_columns,  _( 'Phone' ), '', true, 'addresses[' . $array . ']' ) .
	'</td></tr>';

	// Students Join Address:
	$tooltip_checkbox = _makeFieldTypeTooltip( 'checkbox' );

	echo '<tr><td>' .
		_makeCheckboxInput(
			'STUDENTS_JOIN_ADDRESS][RESIDENCE',
			'Y',
			button( 'house', '', '', 'bigger' ) . ' ' . _( 'Residence' ) . $tooltip_checkbox,
			'addresses[' . $array . ']'
		) .
	'</td></tr>';

	echo '<tr><td>' .
		_makeCheckboxInput(
			'STUDENTS_JOIN_ADDRESS][BUS_PICKUP',
			'Y',
			button( 'bus', '', '', 'bigger' ) . ' ' . _( 'Bus Pickup' ) . $tooltip_checkbox,
			'addresses[' . $array . ']'
		) .
	'</td></tr>';

	echo '<tr><td>' .
		_makeCheckboxInput(
			'STUDENTS_JOIN_ADDRESS][BUS_DROPOFF',
			'Y',
			button( 'bus', '', '', 'bigger' ) . ' ' . _( 'Bus Dropoff' ) . $tooltip_checkbox,
			'addresses[' . $array . ']'
		) .
	'</td></tr>';

	echo '<tr><td>' .
		_makeCheckboxInput(
			'STUDENTS_JOIN_ADDRESS][MAILING',
			'Y',
			button( 'mailbox', '', '', 'bigger' ) . ' ' . _( 'Mailing Address' ) . $tooltip_checkbox,
			'addresses[' . $array . ']'
		) .
	'</td></tr>';

	/**
	 * Custom Address Fields.
	 */
	$fields_RET = DBGet( DBQuery( "SELECT af.ID,af.TITLE,af.TYPE,af.SELECT_OPTIONS,
		af.REQUIRED,af.CATEGORY_ID,afc.TITLE AS CATEGORY_TITLE
		FROM ADDRESS_FIELDS af, ADDRESS_FIELD_CATEGORIES afc
		WHERE af.CATEGORY_ID=afc.ID
		ORDER BY afc.SORT_ORDER, af.SORT_ORDER") );

	$category_id_last = 0;

	foreach ( (array) $fields_RET as $field )
	{
		if ( $category_id_last !== $field['CATEGORY_ID'] )
		{
			// Add Category name as Address Fields separator!
			echo '<tr><td><h4>' . ParseMLField( $field['CATEGORY_TITLE'] ) . '</h4></td></tr>';
		}

		$category_id_last = $field['CATEGORY_ID'];

		$tooltip = _makeFieldTypeTooltip( $field['TYPE'] );

		echo '<tr><td>' .
			_makeSelectInput(
				'CUSTOM_' . $field['ID'],
				$csv_columns,
				ParseMLField( $field['TITLE'] ) . $tooltip,
				$field['REQUIRED'] ? 'required' : '',
				true,
				'addresses[' . $array . ']'
			) .
		'</td></tr>';
	}
}


function _peopleForm( $array, $title )
{
	global $csv_columns;

	static $js_included = false;

	/**
	 * People Fields.
	 */
	echo '<tr><td><h4>' . $title . '</h4></td></tr>';

	echo '<tr><td>' .
		_makeSelectInput( 'FIRST_NAME', $csv_columns,  _( 'First Name' ), 'required', true, 'people[' . $array . ']' ) .
	'</td></tr>';

	echo '<tr><td>' .
		_makeSelectInput( 'MIDDLE_NAME', $csv_columns,  _( 'Middle Name' ), '', true, 'people[' . $array . ']' ) .
	'</td></tr>';

	echo '<tr><td>' .
		_makeSelectInput( 'LAST_NAME', $csv_columns,  _( 'Last Name' ), 'required', true, 'people[' . $array . ']' ) .
	'</td></tr>';

	// Students Join People:
	$existing_relations_RET = DBGet( DBQuery( "SELECT DISTINCT STUDENT_RELATION,upper(STUDENT_RELATION)
		FROM STUDENTS_JOIN_PEOPLE
		WHERE STUDENT_RELATION<>'---'
		ORDER BY STUDENT_RELATION" ) );

	$existing_relations = '';

	foreach ( (array) $existing_relations_RET as $existing_relation )
	{
		if ( $existing_relation['STUDENT_RELATION'] )
		{
			$existing_relations .= $existing_relation['STUDENT_RELATION'] . ', ';
		}
	}

	echo '<tr><td>' .
		TextInput(
			'',
			'people[' . $array . '][STUDENTS_JOIN_PEOPLE][STUDENT_RELATION]',
			_( 'Relation' ) .
			_makeFieldTypeTooltip(
				'edits',
				'; ' . ( $existing_relations ?
					sprintf(
						dgettext( 'Students_Import', 'Existing relations: %s' ),
						mb_substr( $existing_relations, 0, -2 )
					) :
					dgettext( 'Students_Import', 'Example: Father, Mother, Guardian' ) )
			)
		) .
	'</td></tr>';


	$tooltip_checkbox = _makeFieldTypeTooltip( 'checkbox' );

	echo '<tr><td>' .
		_makeCheckboxInput(
			'STUDENTS_JOIN_PEOPLE][CUSTODY',
			'',
			button( 'gavel', '', '', 'bigger' ) . ' ' . _( 'Custody' ) . $tooltip_checkbox,
			'people[' . $array . ']'
		) .
	'</td></tr>';

	echo '<tr><td>' .
		_makeCheckboxInput(
			'STUDENTS_JOIN_PEOPLE][EMERGENCY',
			'',
			button( 'emergency', '', '', 'bigger' ) . ' ' . _( 'Emergency' ) . $tooltip_checkbox,
			'people[' . $array . ']'
		) .
	'</td></tr>';


	/**
	 * Custom People Fields.
	 */
	$fields_RET = DBGet( DBQuery( "SELECT pf.ID,pf.TITLE,pf.TYPE,pf.SELECT_OPTIONS,
		pf.REQUIRED,pf.CATEGORY_ID,pfc.TITLE AS CATEGORY_TITLE
		FROM PEOPLE_FIELDS pf, PEOPLE_FIELD_CATEGORIES pfc
		WHERE pf.CATEGORY_ID=pfc.ID
		ORDER BY pfc.SORT_ORDER, pf.SORT_ORDER") );

	$category_id_last = 0;

	foreach ( (array) $fields_RET as $field )
	{
		if ( $category_id_last !== $field['CATEGORY_ID'] )
		{
			// Add Category name as People Fields separator!
			echo '<tr><td><h4>' . ParseMLField( $field['CATEGORY_TITLE'] ) . '</h4></td></tr>';
		}

		$category_id_last = $field['CATEGORY_ID'];

		$tooltip = _makeFieldTypeTooltip( $field['TYPE'] );

		echo '<tr><td>' .
			_makeSelectInput(
				'CUSTOM_' . $field['ID'],
				$csv_columns,
				ParseMLField( $field['TITLE'] ) . $tooltip,
				$field['REQUIRED'] ? 'required' : '',
				true,
				'people[' . $array . ']'
			) .
		'</td></tr>';
	}


	/**
	 * Contact Fields.
	 */
	_contactFields( $array );


	/**
	 * Contact's Address.
	 *
	 * N/A,
	 * Same a Student,
	 * or own Address.
	 */
	echo '<tr><td><h4>' . dgettext( 'Students_Import', 'Contact\'s Address' ) . '</h4></td></tr>';

	$address_options = array(
		'Same as Student' => dgettext( 'Students_Import', 'Same as Student' ),
		'New Address' => dgettext( 'Students_Import', 'New Address' ),
	);

	echo '<tr><td>' . _makeSelectInput(
		'ADDRESS',
		$address_options,
		dgettext( 'Students_Import', 'Contact\'s Address' ),
		'onchange="contactAddress(this, \'' . $array . '\'); return false;"',
		false,
		'addresses[' . $array . ']'
	) . '</td></tr>';

	ob_start();

	_addressForm( $array, '' );

	$contact_extra_address = ob_get_clean();


	if ( ! $js_included ) : ?>
	<script>
	function contactAddress( field, array ) {
		if ( field.value === 'New Address' ) {

			var newAddress = <?php echo json_encode( '<table class="contact-address-wrapper cellspacing-0" style="margin: 0 -7px;">' .
				$contact_extra_address . '</table>' ); ?>,
				newAddressArray = newAddress.replace(
					/contactid\d{1}/g,
					array
				);

			// Add Contact New Address.
			$( field ).parents( 'td' ).append( newAddressArray );

			$('.chosen-select').chosen('.chosen-select');
		} else {

			// Remove Contact New Address.
			$( field ).nextAll( '.contact-address-wrapper' ).remove();
		}
	}
	</script>
	<?php $js_included = true;
	endif;
}


function _contactFields( $array )
{
	global $csv_columns;

	static $js_included = false;

	/**
	 * Contact Fields.
	 */
	echo '<tr><td><h4>' . dgettext( 'Students_Import', 'Information' ) . '</h4></td></tr>';

	echo '<tr><td style="padding:0">
		<table class="width-100p cellspacing-0" id="contact-field-table-' . $array . '">';

	// People Join Contacts:
	$existing_titles_RET = DBGet( DBQuery( "SELECT DISTINCT TITLE,upper(TITLE)
		FROM PEOPLE_JOIN_CONTACTS
		WHERE TITLE<>'---'
		ORDER BY TITLE" ) );

	$existing_titles = '';

	foreach ( (array) $existing_titles_RET as $existing_title )
	{
		if ( $existing_title['TITLE'] )
		{
			$existing_titles .= $existing_title['TITLE'] . ', ';
		}
	}

	$contact_field = '<tr><td>' .
		_makeSelectInput(
			'VALUE',
			$csv_columns,
			_( 'Value' ),
			'',
			true,
			'people[' . $array . '][PEOPLE_JOIN_CONTACTS][0]'
		) .
	'<br />';

	$contact_field .= TextInput(
			'',
			'people[' . $array . '][PEOPLE_JOIN_CONTACTS][0][TITLE]',
			_( 'Description' ) .
			_makeFieldTypeTooltip(
				'edits',
				( $existing_titles ?
					'; ' .
					sprintf(
						dgettext( 'Students_Import', 'Existing descriptions: %s' ),
						mb_substr( $existing_titles, 0, -2 )
					) :	'' )
			)
		) .
	'</td></tr>';

	echo $contact_field;

	echo '</table></td></tr>';

	if ( ! $js_included ) : ?>
	<script>
		var contactFieldNumber = 1;

		function addContactField( array ) {
			var contactFieldHTML = <?php echo json_encode( $contact_field ); ?>,
				contactFieldHTMLArray = contactFieldHTML.replace(
					/contactid\d{1}/g,
					array
				);

			contactFieldHTMLArray = contactFieldHTMLArray.replace(
				new RegExp('PEOPLE_JOIN_CONTACTS\\]\\[0', 'g'),
				'PEOPLE_JOIN_CONTACTS][' + contactFieldNumber
			);

			// Add Contact at the end of the current People table.
			addHTML( contactFieldHTMLArray, 'contact-field-table-' + array );

			contactFieldNumber++;

			$('.chosen-select').chosen('.chosen-select');
		}
	</script>
	<?php $js_included = true;
	endif;

	echo '<tr><td>' .
		'<a href="#" onclick="addContactField(\'' . $array . '\'); return false;">' .
			button( 'add' ) . ' ' .
		'<b>' . dgettext( 'Students_Import', 'Add Information' ) . '</b>' .
	'</td></tr>';

}


/**
 * Import Addresses, People for current Student
 *
 * @see CSVImport()
 *
 * @uses functions below.
 *
 * @param string $student_id Student ID.
 * @param array  $addresses  Addresses fields.
 * @param array  $people     People fields.
 */
function AddressesPeopleImport( $student_id, $addresses, $people )
{
	$address = $addresses['student'];

	$student_address_id = _checkAddress( $address );

	$address_sql = array();

	// Unset STUDENTS_JOIN_ADDRESS before inserting.
	$address_insert = $address;

	unset( $address_insert['STUDENTS_JOIN_ADDRESS'] );

	if ( ! $student_address_id
		&& $address['ADDRESS'] )
	{
		list( $student_address_id, $address_sql[] ) = _insertAddress( $address_insert );
	}

	if ( $student_address_id )
	{
		$address_sql[] = _joinStudentAddress(
			$student_id,
			$student_address_id,
			$address['STUDENTS_JOIN_ADDRESS']
		);
	}

	if ( $address_sql )
	{
		DBQuery( implode( '', $address_sql ) );
	}

	// INSERT people: Father, Mother, (Guardian) & their eventual addresses.
	foreach ( (array) $people as $i => $person )
	{
		$people_sql = array();

		$address_id = '';

		$address = $addresses[ $i ];

		// Address Father, Mother, Guardian.
		if ( ! empty( $address['ADDRESS'] ) )
		{
			$address_id = _checkAddress( $address );

			if ( $address['ADDRESS'] != 'Same as Student'
				&& mb_strtolower( $address['ADDRESS'] ) != mb_strtolower( $addresses['student']['ADDRESS'] ) )
			{
				if ( ! $address_id )
				{
					// Unset STUDENTS_JOIN_ADDRESS before inserting.
					$address_insert = $address;

					unset( $address_insert['STUDENTS_JOIN_ADDRESS'] );

					list( $address_id, $people_sql[] ) = _insertAddress( $address_insert );

					$people_sql[] = _joinStudentAddress(
						$student_id,
						$address_id,
						$address['STUDENTS_JOIN_ADDRESS']
					);
				}
			}
			// Student Address
			elseif ( $address['ADDRESS'] == 'Same as Student' )
			{
				$address_id = $student_address_id;
			}
		}

		if ( ! $address_id )
		{
			// Join Student to No Address (Address ID 0).
			$people_sql[] = _joinStudentAddress(
				$student_id,
				$address_id,
				$address['STUDENTS_JOIN_ADDRESS']
			);
		}

		if ( $person['FIRST_NAME']
			&& $person['LAST_NAME'] )
		{
			// INSERT Person.
			$person_id = _checkPerson( $person );

			if ( ! $person_id )
			{
				$person_only = $person;

				unset( $person_only['PEOPLE_JOIN_CONTACTS'] );
				unset( $person_only['STUDENTS_JOIN_PEOPLE'] );

				list( $person_id, $people_sql[] ) = _insertPerson( $person_only );
			}

			// Contact fields.
			$contact_fields = $person['PEOPLE_JOIN_CONTACTS'];

			if ( ! empty( $contact_fields ) )
			{
				$people_sql[] = _insertContactFields( $person_id, $contact_fields );
			}

			// Link Person to Student.
			$people_sql[] = _joinStudentPerson(
				$student_id,
				$person_id,
				$address_id,
				$person['STUDENTS_JOIN_PEOPLE']
			);
		}

		if ( $people_sql )
		{
			DBQuery( implode( '', $people_sql ) );
		}
	}
}


/**
 * Check for existing Address
 *
 * Local function
 *
 * @see AddressPeopleImport()
 *
 * @param  array $address Address.
 *
 * @return string '' if not found, else Address ID
 */
function _checkAddress( $address )
{
	$address_id = '';

	if ( ! isset( $address['ADDRESS'] )
		|| $address['ADDRESS'] === '' )
	{
		return false;
	}

	$city_sql = $zipcode_sql = '';

	if ( isset( $address['CITY'] )
		&& $address['CITY'] )
	{
		$city_sql = " AND UPPER(CITY)=UPPER('" . DBEscapeString( $address['CITY'] ) . "')";
	}

	if ( isset( $address['ZIPCODE'] )
		&& $address['ZIPCODE'] )
	{
		$zipcode_sql = " AND UPPER(ZIPCODE)=UPPER('" . DBEscapeString( $address['ZIPCODE'] ) . "')";
	}

	$address = DBGet( DBQuery( "SELECT ADDRESS_ID
		FROM ADDRESS
		WHERE UPPER(ADDRESS)=UPPER('" . DBEscapeString( $address['ADDRESS'] ) . "')" . $zipcode_sql . $city_sql ) );

	if ( $address )
	{
		$address_id = $address[1]['ADDRESS_ID'];
	}

	return $address_id;
}


/**
 * Insert Address in Database
 *
 * Local function
 *
 * @see AddressPeopleImport()
 *
 * @param  array $address_fields Address Fields.
 *
 * @return array (Address ID, SQL INSERT)
 */
function _insertAddress( $address_fields )
{
	static $custom_fields_RET = null;

	if ( version_compare( ROSARIO_VERSION, '5.0-beta', '<' ) )
	{
		// @deprecated
		$address_id = DBGet( DBQuery( 'SELECT ' . db_seq_nextval( 'ADDRESS_SEQ' ) . ' AS ID' ) );
		$address_id = $address_id[1]['ID'];
	}
	else
	{
		// Uses new serial ID sequence.
		$address_id = DBSeqNextID( 'address_address_id_seq' );
	}

	if ( ! $custom_fields_RET )
	{
		$custom_fields_RET = DBGet( DBQuery( "SELECT ID,TYPE
			FROM ADDRESS_FIELDS
			ORDER BY SORT_ORDER"), array(), array( 'ID' ) );
	}

	$sql = "INSERT INTO ADDRESS ";

	$fields = 'ADDRESS_ID,';

	$values = "'" . $address_id . "',";

	foreach ( $address_fields as $field => $value )
	{
		if ( ! empty( $value )
			|| $value == '0' )
		{
			$field_type = $custom_fields_RET[ str_replace( 'CUSTOM_', '', $field ) ][1]['TYPE'];

			// Check field type.
			if ( ( $value = _checkFieldType( $value, $field_type ) ) === false )
			{
				continue;
			}

			if ( function_exists( 'DBEscapeIdentifier' ) ) // RosarioSIS 3.0+.
			{
				$fields .= DBEscapeIdentifier( $field ) . ',';
			}
			else
			{
				$fields .= '"' . mb_strtolower( $field ) . '",';
			}

			$values .= "'" . DBEscapeString( $value ) . "',";
		}
	}

	$sql .= '(' . mb_substr( $fields, 0, -1 ) . ') values(' . mb_substr( $values, 0, -1 ) . ');';

	return array( $address_id, $sql );
}


/**
 * Update Address in Database
 *
 * Local function
 *
 * @see AddressPeopleImport()
 *
 * @param  string $address_id     Address ID.
 * @param  array  $address_fields Address Fields.
 */
function _updateAddress( $address_id, $address_fields )
{
	static $custom_fields_RET = null;

	if ( ! $custom_fields_RET )
	{
		$custom_fields_RET = DBGet( DBQuery( "SELECT ID,TYPE
			FROM ADDRESS_FIELDS
			ORDER BY SORT_ORDER"), array(), array( 'ID' ) );
	}

	$sql = "UPDATE ADDRESS SET ";

	$where = " WHERE ADDRESS_ID='" . $address_id . "'";

	$fields = '';

	foreach ( $address_fields as $field => $value )
	{
		if ( ! empty( $value )
			|| $value == '0' )
		{
			$field_type = $custom_fields_RET[ str_replace( 'CUSTOM_', '', $field ) ][1]['TYPE'];

			// Check field type.
			if ( ( $value = _checkFieldType( $value, $field_type ) ) === false )
			{
				continue;
			}

			if ( function_exists( 'DBEscapeIdentifier' ) ) // RosarioSIS 3.0+.
			{
				$field = DBEscapeIdentifier( $field );
			}
			else
			{
				$field = '"' . mb_strtolower( $field ) . '"';
			}

			$fields .= $field . "='" . $value . "',";
		}
	}

	$sql .= mb_substr( $fields, 0, -1 ) . $where;

	DBQuery( $sql );
}

/**
 * Join Student to Address in Database
 *
 * Local function
 *
 * @see AddressPeopleImport()
 *
 * @param  string $student_id Student ID.
 * @param  string $address_id Address ID.
 * @param  array  $address    Address array.
 *
 * @return string SQL INSERT
 */
function _joinStudentAddress( $student_id, $address_id, $students_join_address )
{
	if ( version_compare( ROSARIO_VERSION, '5.0-beta', '<' ) )
	{
		// @deprecated
		$id_nextval = db_seq_nextval( 'students_join_address_seq' );
	}
	else
	{
		// Uses new serial ID sequence.
		$id_nextval = db_seq_nextval( 'students_join_address_id_seq' );
	}

	return "INSERT INTO students_join_address (ID,STUDENT_ID,ADDRESS_ID,MAILING,RESIDENCE,BUS_PICKUP,BUS_DROPOFF)
		values(
			" . $id_nextval . ",'" .
			$student_id . "','" .
			(int) $address_id . "','" .
			( $students_join_address && $students_join_address['MAILING'] ? 'Y' : '' ) . "','" .
			( $students_join_address && $students_join_address['RESIDENCE'] ? 'Y' : '' ) . "','" .
			( $students_join_address && $students_join_address['BUS_PICKUP'] ? 'Y' : '' ) . "','" .
			( $students_join_address && $students_join_address['BUS_DROPOFF'] ? 'Y' : '' ) . "'
		);";
}


/**
 * Check for existing Person
 *
 * Local function
 *
 * @see AddressPeopleImport()
 *
 * @param  array $person_fields Person Fields.
 *
 * @return string false if not found, else Person ID
 */
function _checkPerson( $person_fields )
{
	$person_id = false;

	$person = DBGet( DBQuery( "SELECT PERSON_ID
		FROM PEOPLE
		WHERE UPPER(FIRST_NAME)=UPPER('" . DBEscapeString( $person_fields['FIRST_NAME'] ) . "')
		AND UPPER(LAST_NAME)=UPPER('" . DBEscapeString( $person_fields['LAST_NAME'] ) . "')
		AND (MIDDLE_NAME IS NULL
			OR UPPER(MIDDLE_NAME)=UPPER('" . DBEscapeString( $person_fields['MIDDLE_NAME'] ) . "'))" ) );

	if ( $person )
	{
		$person_id = $person[1]['PERSON_ID'];
	}

	return $person_id;
}


/**
 * Insert Person in Database (table PEOPLE)
 *
 * Local function
 *
 * @see AddressPeopleImport()
 *
 * @param  array $person_fields Person Fields.
 *
 * @return array (Person ID, SQL INSERT)
 */
function _insertPerson( $person_fields )
{
	static $custom_fields_RET = null;

	if ( ! $custom_fields_RET )
	{
		$custom_fields_RET = DBGet( DBQuery( "SELECT ID,TYPE
			FROM PEOPLE_FIELDS
			ORDER BY SORT_ORDER"), array(), array( 'ID' ) );
	}

	if ( version_compare( ROSARIO_VERSION, '5.0-beta', '<' ) )
	{
		// @deprecated
		$person_id = DBGet( DBQuery( 'SELECT ' . db_seq_nextval( 'PEOPLE_SEQ' ) . ' AS ID' ) );
		$person_id = $person_id[1]['ID'];
	}
	else
	{
		// Uses new serial ID sequence.
		$person_id = DBSeqNextID( 'people_person_id_seq' );
	}

	$sql_people = "INSERT INTO PEOPLE ";

	$fields_people = 'PERSON_ID,';

	$values = "'" . $person_id . "',";

	foreach ( (array) $person_fields as $field => $value )
	{
		if ( ! empty( $value )
			|| $value == '0' )
		{
			$field_type = $custom_fields_RET[ str_replace( 'CUSTOM_', '', $field ) ][1]['TYPE'];

			// Check field type.
			if ( ( $value = _checkFieldType( $value, $field_type ) ) === false )
			{
				continue;
			}

			$fields_people .= $field . ',';

			$values .= "'" . DBEscapeString( $value ) . "',";
		}
	}

	$sql = $sql_people . '(' . mb_substr( $fields_people, 0, -1 ) . ') values(' . mb_substr( $values, 0, -1 ) . ');';

	return array( $person_id, $sql );
}


/**
 * Insert Contact Field in Database (PEOPLE_JOIN_CONTACTS table)
 *
 * Local function
 *
 * @see AddressPeopleImport()
 *
 * @param  string $person_id      Person ID.
 * @param  array  $contact_fields Contact Fields.
 *
 * @return string SQL INSERT
 */
function _insertContactFields( $person_id, $contact_fields )
{
	// Inset SQL.
	$sql_insert_contact = "INSERT INTO PEOPLE_JOIN_CONTACTS ";

	$fields_insert_contact = 'ID,PERSON_ID,TITLE,VALUE';

	// Update SQL.
	$sql_update_contact = "UPDATE PEOPLE_JOIN_CONTACTS SET ";

	$sql = '';

	foreach ( (array) $contact_fields as $contact_field )
	{
		$title = $contact_field['TITLE'];

		$value = $contact_field['VALUE'];

		if ( empty( $value )
			&& $value !== '0' )
		{
			continue;
		}

		if ( empty( $title ) )
		{
			continue;
		}

		// Check if Contact already exists.
		$contact_exists = DBGet( DBQuery( "SELECT ID
			FROM PEOPLE_JOIN_CONTACTS
			WHERE PERSON_ID='" . $person_id . "'
			AND UPPER(TITLE)=UPPER('" . DBEscapeString( $title ) . "')" ) );

		if ( $contact_exists
			&& $contact_exists[1]['ID'] )
		{
			// Update Contact.
			$sql .= $sql_update_contact;

			$sql .= "TITLE='" . DBEscapeString( $title ) . "',VALUE='" . DBEscapeString( $value ) . "' ";

			$sql .= "WHERE ID='" . $contact_exists[1]['ID'] . "';";

			continue;
		}

		if ( version_compare( ROSARIO_VERSION, '5.0-beta', '<' ) )
		{
			// @deprecated
			$id_nextval = db_seq_nextval( 'PEOPLE_JOIN_CONTACTS_SEQ' );
		}
		else
		{
			// Uses new serial ID sequence.
			$id_nextval = db_seq_nextval( 'people_join_contacts_id_seq' );
		}

		// Insert Contact.
		$values = $id_nextval . ",'" . $person_id . "','" . DBEscapeString( $title ) . "','" . DBEscapeString( $value ) . "'";

		$sql .= $sql_insert_contact . '(' . $fields_insert_contact . ') values(' . $values . ');';
	}

	return $sql;
}


/**
 * Join Student to Person (and Address) in Database
 *
 * Local function
 *
 * @see AddressPeopleImport()
 *
 * @param  string $student_id Student ID.
 * @param  string $person_id  Person ID.
 * @param  string $address_id Address ID.
 * @param  array  $person     Person array.
 *
 * @return string SQL INSERT
 */
function _joinStudentPerson( $student_id, $person_id, $address_id, $person )
{
	if ( version_compare( ROSARIO_VERSION, '5.0-beta', '<' ) )
	{
		// @deprecated
		$id_nextval = db_seq_nextval( 'students_join_people_seq' );
	}
	else
	{
		// Uses new serial ID sequence.
		$id_nextval = db_seq_nextval( 'students_join_people_id_seq' );
	}

	return "INSERT INTO students_join_people (ID,STUDENT_ID,PERSON_ID,ADDRESS_ID,CUSTODY,EMERGENCY,STUDENT_RELATION)
		values(
			" . $id_nextval . ",'" .
			$student_id . "','" .
			$person_id . "','" .
			(int) $address_id . "','" .
			( $person['CUSTODY'] ? 'Y' : '' ) . "','" .
			( $person['EMERGENCY'] ? 'Y' : '' ) . "','" .
			DBEscapeString( $person['STUDENT_RELATION'] ) . "'
		);";
}

