<?php
/**
 * Gradebook Grades Import functions
 *
 * @package Grades Import module
 * @subpackage includes
 */

/**
 * Convert Excel file to CSV
 * Only 1st sheet!
 * (Deletes Excel file)
 * Or, simply convert CSV to UTF8!
 *
 * @uses PHPExcel class
 *
 * @param string $import_file_path Excel file path.
 *
 * @return string CSV file path.
 */
function ConvertExcelToCSV( $import_file_path )
{
	$excel_extensions = array( '.csv', '.xls', '.xlsx' );

	$file_ext = mb_strtolower( mb_strrchr( $import_file_path, '.' ) );

	if ( ! in_array( $file_ext, $excel_extensions ) )
	{
		// Not an Excel file.
		return $import_file_path;
	}


	if ( $file_ext === '.csv' )
	{
		$csv_file_path = $import_file_path;

		$csv_text = file_get_contents( $csv_file_path );

		/**
		 * Check if CSV is encoded in ISO-8859-1 or windows-1252.
		 *
		 * @todo How could we support other encodings?
		 */
		$is_windows1252_or_iso88591 = mb_check_encoding( $csv_text, 'ISO-8859-1' ) ||
			mb_check_encoding( $csv_text, 'windows-1252' );

		if ( $is_windows1252_or_iso88591 )
		{
			$csv_text_utf8 = utf8_encode( $csv_text );

			file_put_contents( $csv_file_path, $csv_text_utf8 );
		}

		return $csv_file_path;
	}

	require_once 'modules/Grades_Import/classes/PHPExcel/IOFactory.php';

	$objPHPExcel = PHPExcel_IOFactory::load( $import_file_path );

	$loadedSheetNames = $objPHPExcel->getSheetNames();

	$objWriter = PHPExcel_IOFactory::createWriter( $objPHPExcel, 'CSV' );

	unlink( $import_file_path );

	$csv_file_path = mb_substr(
		$import_file_path,
		0,
		mb_strrpos( $import_file_path, '.' )
	) . '.csv';

	foreach ( $loadedSheetNames as $sheetIndex => $loadedSheetName )
	{
		$objWriter->setSheetIndex( $sheetIndex );
		$objWriter->save( $csv_file_path );

		// Only 1st Excel sheet.
		break;
	}

	return $csv_file_path;
}


/**
 * Detect delimiter of cells.
 *
 * @param string $name CSV file path.
 *
 * @return array Return detected delimiter.
 */
function DetectCSVDelimiter( $name )
{
	$delimiters = array(
		';' => 0,
		',' => 0,
	);

	$handle = fopen( $name, 'r' );

	$first_line = fgets( $handle );

	fclose( $handle );

	foreach ( $delimiters as $delimiter => &$count )
	{
		$count = count( str_getcsv( $first_line, $delimiter ) );
	}

	return array_search( max( $delimiters ), $delimiters );
}



/**
 * Get CSV column name from number
 *
 * @param int $num Column number.
 *
 * @return string Column letter (eg.: "AB")
 */
function GetCSVColumnNameFromNumber( $num )
{
	$numeric = $num % 26;

	$letter = chr( 65 + $numeric );

	$num2 = intval( $num / 26 );

	if ( $num2 > 0 )
	{
		return GetCSVColumnNameFromNumber( $num2 - 1 ) . $letter;
	}
	else
	{
		return $letter;
	}
}


/**
 * Get CSV columns
 *
 * @param  string $csv_file_path  CSV file path.
 *
 * @return array  $csv_columns    CSV columns, eg.: "AB: Teacher Name".
 */
function GetCSVColumns( $csv_file_path )
{
	$csv_handle = fopen( $csv_file_path, 'r' );

	if ( ! $csv_handle )
	{
		return array();
	}

	// Get 1st CSV row, columns delimited by comma (,).
	$csv_columns = fgetcsv( $csv_handle, 0, DetectCSVDelimiter( $csv_file_path ) );

	fclose( $csv_handle );

	$max = count( $csv_columns );

	for ( $i = 0; $i < $max; $i++ )
	{
		// Add column name before value.
		$csv_columns[ $i ] = GetCSVColumnNameFromNumber( $i ) . ': ' . $csv_columns[ $i ];
	}

	return $csv_columns;
}


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

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

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

	$row = 0;

	$lines = 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 )
				{
					$lines[ $row ][ $field ] = $data[ $col ];
				}
			}
		}

		$row++;
	}

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

	// var_dump( $lines ); exit;

	$max = count( $lines );

	$i = $gradebook_grades_imported = 0;

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

		$i++;
	}

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

		$gradebook_grade = $lines[ $i ];

		// INSERT Grades.
		foreach ( (array) $gradebook_grade as $assignment_id => $points )
		{
			if ( strpos( $assignment_id, 'ASSIGNMENT_' ) === false )
			{
				continue;
			}

			$assignment_id = str_replace( 'ASSIGNMENT_', '', $assignment_id );

			$gradebook_grade['ASSIGNMENT_ID'] = $assignment_id;

			$gradebook_grade['POINTS'] = _checkPoints( $points );

			// INSERT Student.
			$gradebook_grade['STUDENT_ID'] = _getExistingStudentID( $gradebook_grade );

			if ( ! _checkRequired( $gradebook_grade ) )
			{
				if ( ! $gradebook_grade['STUDENT_ID'] )
				{
					// Skip all Assignments.
					break;
				}

				continue;
			}

			if ( ! _checkEnrolledStudentID(
					$gradebook_grade['STUDENT_ID'],
					UsercoursePeriod(),
					$gradebook_grade['ASSIGNMENT_ID'] ) )
			{
				break;
			}

			// INSERT or UPDATE Gradebook Grade.
			if ( _getGradebookGradeID( $gradebook_grade ) )
			{
				$gradebook_grades_imported++;
			}
		}
	}

	fclose( $csv_handle );

	return $gradebook_grades_imported;
}


/**
 * Check Required columns for missing data.
 *
 * Local function
 *
 * @see CSVImport()
 *
 * @param  array $fields Fields.
 *
 * @return string false if incomplete.
 */
function _checkRequired( $fields )
{
	global $warning,
		$i;

	// Student ID name cannot be empty.
	if ( ! $fields['STUDENT_ID'] )
	{
		$warning[] = 'Row #' . ( $i + 1 ) . ': ' .
			dgettext( 'Grades_Import', 'No student found.' );

		return false;
	}

	// Points cannot be empty.
	if ( ! $fields['POINTS'] )
	{
		if ( $fields['POINTS'] === false )
		{
			$warning[] = 'Row #' . ( $i + 1 ) . ': ' .
				dgettext( 'Grades_Import', 'No points found.' );
		}

		// No error if points are empty, just skip.
		return false;
	}

	return true;
}


/**
 * Check if Student is Enrolled in Course Period when the Assignment was assigned.
 *
 * @param  int $student_id       Student ID.
 * @param  int $course_period_id Course Period ID.
 * @param  int $assignment_id    Assignment ID.
 * @return bool                  False if not enrolled, else true.
 */
function _checkEnrolledStudentID( $student_id, $course_period_id, $assignment_id )
{
	global $warning,
		$i;

	$assigned_date = DBGetOne( "SELECT ASSIGNED_DATE
		FROM GRADEBOOK_ASSIGNMENTS
		WHERE ASSIGNMENT_ID='" . $assignment_id . "'" );

	if ( ! $assigned_date )
	{
		$assignment_mp_id = DBGetOne( "SELECT MARKING_PERIOD_ID
		FROM GRADEBOOK_ASSIGNMENTS
		WHERE ASSIGNMENT_ID='" . $assignment_id . "'" );

		$assigned_date = GetMP( $assignment_mp_id, 'START_DATE' );
	}

	$is_student_enrolled = (bool) DBGetOne( "SELECT STUDENT_ID
		FROM SCHEDULE
		WHERE COURSE_PERIOD_ID='" . $course_period_id . "'
		AND STUDENT_ID='" . $student_id . "'
		AND '" . $assigned_date . "'>=START_DATE
		AND ('" . $assigned_date . "'<=END_DATE OR END_DATE IS NULL)" );

	if ( ! $is_student_enrolled )
	{
		$warning[] = 'Row #' . ( $i + 1 ) . ': ' .
			dgettext( 'Grades_Import', 'Student not scheduled in course Period.' );

		return false;
	}

	return true;
}


/**
 * Check Points
 *
 * @param  string $points Points.
 *
 * @return false if not a date, else ISO formatted Date
 */
function _checkPoints( $points )
{
	if ( $points === '' )
	{
		return '';
	}

	if ( $points === '*' )
	{
		// Excuse Student.
		return '-1';
	}

	// Warning: convert European float format.
	$points = str_replace( ',', '.', $points );

	// Remove non numeric characters.
	$points = preg_replace( '/[^0-9.]/', '', $points );

	if ( ! $points )
	{
		return false;
	}

	return $points;
}


function _getExistingStudentID( $fields )
{
	$assigned_date = DBGetOne( "SELECT ASSIGNED_DATE
		FROM GRADEBOOK_ASSIGNMENTS
		WHERE ASSIGNMENT_ID='" . $fields['ASSIGNMENT_ID'] . "'" );

	if ( ! $assigned_date )
	{
		$assignment_mp_id = DBGetOne( "SELECT MARKING_PERIOD_ID
		FROM GRADEBOOK_ASSIGNMENTS
		WHERE ASSIGNMENT_ID='" . $fields['ASSIGNMENT_ID'] . "'" );

		$assigned_date = GetMP( $assignment_mp_id, 'START_DATE' );
	}

	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 ('" . $assigned_date . "'>=ssm.START_DATE
			AND (ssm.END_DATE IS NULL OR '" . $assigned_date . "'<=ssm.END_DATE ) )
		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 ('" . $assigned_date . "'>=ssm.START_DATE
			AND (ssm.END_DATE IS NULL OR '" . $assigned_date . "'<=ssm.END_DATE ) )
		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 ) ) . "%'" );
}


/**
 * Insert in Database
 *
 * Local function
 *
 * @see CSVImport()
 *
 * @param  array $fields Fields.
 *
 * @return string SQL INSERT
 */
function _insert( $fields, $table )
{
	if ( ! in_array(
		$table,
		array( 'GRADEBOOK_GRADES' )
	) )
	{
		return '';
	}

	// INSERT lines.
	$sql = "INSERT INTO " . DBEscapeIdentifier( $table ) . " ";

	$columns = $values = '';

	foreach ( $fields as $field => $value )
	{
		if ( ! empty( $value )
			|| $value == '0' )
		{
			$columns .= DBEscapeIdentifier( $field ) . ',';

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

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

	return $sql;
}


/**
 * Update in Database
 *
 * Local function
 *
 * @see CSVImport()
 *
 * @param  array $fields Fields.
 *
 * @return string SQL UPDATE
 */
function _update( $fields, $table, $where_fields )
{
	if ( ! in_array(
		$table,
		array( 'GRADEBOOK_GRADES' )
	) )
	{
		return '';
	}

	// UPDATE lines.
	$sql = "UPDATE " . DBEscapeIdentifier( $table ) . " SET ";

	$values = '';

	foreach ( $fields as $field => $value )
	{
		if ( ! empty( $value )
			|| $value == '0' )
		{
			$values .= DBEscapeIdentifier( $field ) . "='" . DBEscapeString( $value ) . "',";
		}
	}

	$sql .= mb_substr( $values, 0, -1 ) . " WHERE TRUE ";

	$where_sql = '';

	foreach ( $where_fields as $field => $value )
	{
		if ( ! empty( $value )
			|| $value == '0' )
		{
			$where_sql .= " AND " . DBEscapeIdentifier( $field ) . "='" . DBEscapeString( $value ) . "'";
		}
	}

	$sql .= $where_sql . ";";

	return $sql;
}



/**
 * Get Gradebook Grade ID.
 * Insert Gradebook Grade.
 *
 * Local function
 *
 * @see CSVImport()
 *
 * @param  array $fields Gradebook Grade Fields.
 *
 * @return string Gradebook Grade ID
 */
function _getGradebookGradeID( $fields )
{
	static $i = 0;

	$gradebook_grade = $fields;

	foreach ( $gradebook_grade as $assignment_id => $points )
	{
		if ( $assignment_id !== 'ASSIGNMENT_ID'
			&& strpos( $assignment_id, 'ASSIGNMENT_' ) !== false )
		{
			unset( $gradebook_grade[ $assignment_id ] );
		}
	}

	unset( $gradebook_grade['USERNAME'] );

	unset( $gradebook_grade['FIRST_NAME'] );

	unset( $gradebook_grade['LAST_NAME'] );

	$gradebook_grade['COURSE_PERIOD_ID'] = UserCoursePeriod();

	$grade_exists = DBGetOne( "SELECT 1 AS GRADE_EXISTS
		FROM GRADEBOOK_GRADES
		WHERE COURSE_PERIOD_ID='" . $gradebook_grade['COURSE_PERIOD_ID'] . "'
		AND ASSIGNMENT_ID='" . $gradebook_grade['ASSIGNMENT_ID'] . "'
		AND STUDENT_ID='" . $gradebook_grade['STUDENT_ID'] . "'" );

	if ( $grade_exists )
	{
		$where_grade = array(
			'COURSE_PERIOD_ID' => $gradebook_grade['COURSE_PERIOD_ID'],
			'ASSIGNMENT_ID' => $gradebook_grade['ASSIGNMENT_ID'],
			'STUDENT_ID' => $gradebook_grade['STUDENT_ID'],
		);

		$sql = _update(
			array( 'POINTS' => $gradebook_grade['POINTS'] ),
			'GRADEBOOK_GRADES',
			$where_grade
		);
	}
	else
	{
		$sql = _insert( $gradebook_grade, 'GRADEBOOK_GRADES' );
	}

	DBQuery( $sql );

	return true;
}

function _makeCheckboxInput( $column, $value, $title, $array = 'values' )
{
	return CheckboxInput( $value, $array . '[' . $column . ']', $title, '', true );
}



function _makeSelectInput( $column, $options, $title, $extra = '', $chosen = true, $array = 'values' )
{
	if (  ! $chosen )
	{
		return SelectInput( '', $array . '[' . $column . ']', $title, $options, 'N/A', $extra );
	}

	return ChosenSelectInput( '', $array . '[' . $column . ']', $title, $options, 'N/A', $extra );
}


/**
 * My array_flip()
 * Handles multiple occurrences of a value
 *
 * @param  array $array Input array.
 *
 * @return array        Flipped array.
 */
function my_array_flip( $array )
{
	$flipped = array();

	foreach ( $array as $key => $value )
	{
		$flipped[ $value ][] = $key;
	}

	return $flipped;
}

