<?php
/**
 * Students Update functions
 *
 * @package Students Import Premium module
 */


function CSVUpdate( $csv_file_path )
{
	global $i,
		$warning;

	$csv_handle = fopen( $csv_file_path, 'r' );

	if ( ! $csv_handle
		|| ! isset( $_REQUEST['values'] ) )
	{
		return 0;
	}

	$row = 0;

	$students = $enrollment = array();

	$columns_values = my_array_flip( $_REQUEST['values'] );

	$delimiter = DetectCSVDelimiter( $csv_file_path );

	// Get CSV row.
	while ( ( $data = fgetcsv( $csv_handle, 0, $delimiter ) ) !== false )
	{
		// Trim.
		$data = array_map( 'trim', $data );

		// Import first row? (generally column names).
		if ( $row === 0 && ! $_REQUEST['import-first-row'] )
		{
			$row++;

			continue;
		}

		// For each column.
		for ( $col = 0, $col_max = count( $data ); $col < $col_max; $col++ )
		{
			if ( isset( $columns_values[ $col ] ) )
			{
				foreach ( (array) $columns_values[ $col ] as $field )
				{
					$students[ $row ][ $field ] = $data[ $col ];
				}
			}
		}

		$row++;
	}

	// Sanitize input: Remove HTML tags.
	array_rwalk( $students, 'strip_tags' );

	//var_dump( $students ); //exit;

	$max = count( $students );

	$i = $students_imported = 0;

	// Import first row? (generally column names).
	if ( ! $_REQUEST['import-first-row'] )
	{
		$max++;

		$i++;
	}

	for ( ; $i < $max; $i++ )
	{
		$student_sql = array();

		$student = $students[ $i ];

		// Get existing STUDENT_ID based on ID, username or name.
		$student['STUDENT_ID'] = _getExistingStudentID( $student );

		if ( ! $student['STUDENT_ID'] )
		{
			$warning[] = 'Row #' . ( $i + 1 ) . ': ' .
				dgettext( 'Students_Import', 'No student found.' );

			continue;
		}

		// INSERT Student.
		$student_sql[] = _updateStudent( $student );

		DBQuery( implode( '', $student_sql ) );

		$students_imported++;
	}

	fclose( $csv_handle );

	return $students_imported;
}


function _getExistingStudentID( $fields )
{
	if ( $_REQUEST['student_identify'] === 'STUDENT_ID'
		|| $_REQUEST['student_identify'] === 'USERNAME' )
	{
		if ( empty( $fields[ $_REQUEST['student_identify'] ] ) )
		{
			// No Student ID or Username...
			return 0;
		}

		// Get Student ID by Student ID or Username.
		$where_sql = $_REQUEST['student_identify'] === 'STUDENT_ID' ?
			"s.STUDENT_ID='" . (int) $fields['STUDENT_ID'] . "'" :
			"s.USERNAME='" . $fields['USERNAME'] . "'";

		return (int) DBGetOne( "SELECT s.STUDENT_ID
		FROM STUDENTS s
		JOIN STUDENT_ENROLLMENT ssm ON (ssm.STUDENT_ID=s.STUDENT_ID
			AND ssm.SYEAR='" . UserSyear() . "'
			AND ssm.SCHOOL_ID='" . UserSchool() . "')
		WHERE " . $where_sql );
	}

	// Identify Student by Name.
	$first_name = explode( ' ', $fields['FIRST_NAME'] );

	$first_name = $first_name[0];

	$last_name = $fields['LAST_NAME'];

	// Get Student ID where First and Last Names match.
	return (int) DBGetOne( "SELECT s.STUDENT_ID
		FROM STUDENTS s
		JOIN STUDENT_ENROLLMENT ssm ON (ssm.STUDENT_ID=s.STUDENT_ID
			AND ssm.SYEAR='" . UserSyear() . "'
			AND ssm.SCHOOL_ID='" . UserSchool() . "')
		WHERE LOWER(s.LAST_NAME)='" . DBEscapeString( mb_strtolower( $last_name ) ) . "'
		AND LOWER(s.FIRST_NAME) LIKE '" . DBEscapeString( mb_strtolower( $first_name ) ) . "%'" );
}


/**
 * Update existing Student in Database
 *
 * Local function
 *
 * @see CSVUpdate()
 *
 * @param  array $student_fields Student Fields.
 *
 * @return string SQL UPDATE
 */
function _updateStudent( $student_fields )
{
	static $custom_fields_RET = null;

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

	$student_id = $student_fields['STUDENT_ID'];

	unset( $student_fields['STUDENT_ID'] );

	// UPDATE students.
	$sql = "UPDATE STUDENTS SET ";

	if ( isset( $student_fields['PASSWORD'] )
		&& $student_fields['PASSWORD'] != '' )
	{
		$student_fields['PASSWORD'] = encrypt_password( $student_fields['PASSWORD'] );
	}

	foreach ( $student_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;
			}

			$sql .= DBEscapeIdentifier( $field ) . "='" . DBEscapeString( $value ) . "',";
		}
	}

	$sql = mb_substr( $sql, 0, -1 ) . " WHERE STUDENT_ID='" . $student_id . "'";

	return $sql;
}
