编辑文件 - GetStuList.fnc.php

✏️ 正在编辑: GetStuList.fnc.php

路径: /home/eblama1/sms.karnplayinland.com/functions/GetStuList.fnc.php
提示: 您可以编辑任何文件（包括二进制文件），但请注意不当修改可能导致文件损坏。

<?php
/**
 * Get Student List functions
 *
 * @package RosarioSIS
 * @subpackage functions
 */

/**
 * Get Student List
 * Builds SQL request based on:
 * - User profile
 * - Extra parameters
 *
 * @example $fees_RET = GetStuList( $fees_extra );
 *
 * @since 11.7 Add 'LIMIT' to $extra param (default to 1000)
 * Note about 'LIMIT': if you display a list made of multiple GetStuList() / GetStaffList() calls
 * please make sure you sum SQL queries to COUNT total results before ListOutput().
 * @see example in Student_Billing/includes/DailyTransactions.php
 *
 * @see Search()
 *
 * @uses Widgets()               add Widgets SQL to $extra
 * @uses appendSQL()             add Search Student basic fields SQL to $extra['WHERE']
 * @uses CustomFields()          add Custom Fields SQL to $extra['WHERE']
 * @uses DBGet()                 return Students
 * @uses makeParents()           generate Parents info popup
 * @uses makeEmail()             format Email address
 * @uses makePhone()             format Phone number
 * @uses makeContactInfo()       generate Contact Info tooltip
 * @uses makeFieldTypeFunction() make / format custom fields based on their type
 *
 * @global $contacts_RET   Student Contacts array
 * @global $view_other_RET Used by makeParents() (see below)
 *
 * @param  array &$extra Extra for SQL request ('SELECT_ONLY', 'SELECT', 'FROM', 'WHERE', 'ORDER_BY', 'functions', 'columns_after', 'DATE',...).
 *
 * @return array DBGet return of the built SQL query
 */
function GetStuList( &$extra = [] )
{
	global $contacts_RET,
		$view_other_RET;

if ( ! isset( $extra ) )
	{
		$extra = [];
	}

// Fix Advanced Search.
	if ( isset( $_REQUEST['advanced'] )
		&& $_REQUEST['advanced'] === 'Y' )
	{
		Widgets( 'all', $extra );
	}
	else
	{
		Widgets( 'user', $extra );
	}

$extra['SELECT'] = issetVal( $extra['SELECT'], '' );

$extra['FROM'] = issetVal( $extra['FROM'], '' );

$extra['WHERE'] = issetVal( $extra['WHERE'], '' );

$extra['WHERE'] .= appendSQL( '', $extra );

$extra['WHERE'] .= CustomFields( 'where', 'student', $extra );

$extra['LIMIT'] = issetVal( $extra['LIMIT'], 1000 );

$functions = [];

if ( ( ! isset( $extra['SELECT_ONLY'] )
			|| mb_strpos( $extra['SELECT_ONLY'], 'GRADE_ID' ) !== false )
		&& ! isset( $extra['functions']['GRADE_ID'] ) )
	{
		$functions = [ 'GRADE_ID' => 'GetGrade' ];
	}

if ( isset( $extra['functions'] ) )
	{
		$functions += (array) $extra['functions'];
	}

if ( ! isset( $extra['MP'] )
		&& ! isset( $extra['DATE'] ) )
	{
		$extra['MP'] = UserMP();

$extra['DATE'] = DBDate();
	}
	elseif ( empty( $extra['MP'] ) )
	{
		$extra['MP'] = GetCurrentMP( 'QTR', $extra['DATE'], false );
	}
	elseif ( empty( $extra['DATE'] ) )
	{
		$extra['DATE'] = DBDate();
	}

// Expanded View.
	if ( isset( $_REQUEST['expanded_view'] )
		&& $_REQUEST['expanded_view'] == 'true' )
	{
		/**
		 * Add Sudent Photo Tip Message to Expanded View
		 *
		 * @since 3.8
		 */
		if ( empty( $functions['FULL_NAME'] ) )
		{
			$functions['FULL_NAME'] = 'makePhotoTipMessage';
		}

if ( empty( $extra['columns_after'] ) )
		{
			$extra['columns_after'] = [];
		}

$view_fields_RET = DBGet( "SELECT cf.ID,cf.TYPE,cf.TITLE
			FROM custom_fields cf,student_field_categories sfc
			WHERE ((SELECT VALUE
				FROM program_user_config
				WHERE TITLE=cast(cf.ID AS char(10))
				AND PROGRAM='StudentFieldsView'
				AND USER_ID='" . User( 'STAFF_ID' ) . "')='Y'" .
				( ! empty( $extra['student_fields']['view'] ) ?
					" OR cf.ID IN (" . $extra['student_fields']['view'] . ")" :
					'' ) . ")
			AND cf.CATEGORY_ID=sfc.ID
			ORDER BY sfc.SORT_ORDER IS NULL,sfc.SORT_ORDER,cf.SORT_ORDER IS NULL,cf.SORT_ORDER,cf.TITLE" );

$view_address_RET = DBGetOne( "SELECT VALUE
			FROM program_user_config
			WHERE PROGRAM='StudentFieldsView'
			AND TITLE='ADDRESS'
			AND USER_ID='" . User( 'STAFF_ID' ) . "'" );

$view_other_RET = DBGet( "SELECT TITLE,VALUE
			FROM program_user_config
			WHERE PROGRAM='StudentFieldsView'
			AND TITLE IN ('USERNAME','CONTACT_INFO','HOME_PHONE','GUARDIANS','ALL_CONTACTS')
			AND USER_ID='" . User( 'STAFF_ID' ) . "'", [], [ 'TITLE' ] );

$select = '';

if ( ! $view_fields_RET
			&& ! $view_address_RET
			&& empty( $view_other_RET['CONTACT_INFO'][1]['VALUE'] ) )
		{
			$extra['columns_after'] = [
				'ADDRESS' => _( 'Mailing Address' ),
				'CITY' => _( 'City' ),
				'STATE' => _( 'State' ),
				'ZIPCODE' => _( 'Zip Code' ) ]
				+ $extra['columns_after'];

// Add Gender + Ethnicity fields if exist.
			$custom_fields_RET = DBGet( "SELECT ID,TITLE,TYPE
				FROM custom_fields
				WHERE ID IN (200000000, 200000001)" );

foreach ( $custom_fields_RET as $field)
			{
				$extra['columns_after'] = [
					'CUSTOM_' . $field['ID'] => ParseMLField( $field['TITLE'] ) ]
					+ $extra['columns_after'];

// If gender and ethnicity are converted to exports type.
				if ( $field['TYPE'] === 'exports' )
				{
					$functions['CUSTOM_' . $field['ID']] = 'DeCodeds';
				}

$select .= ',s.CUSTOM_' . $field['ID'];
			}

$extra['columns_after'] = [
				'CONTACT_INFO' => button( 'down_phone' ) ]
				+ $extra['columns_after'];

$select .= ',ssm.STUDENT_ID AS CONTACT_INFO,coalesce(a.MAIL_ADDRESS,a.ADDRESS) AS ADDRESS,
				coalesce(a.MAIL_CITY,a.CITY) AS CITY,coalesce(a.MAIL_STATE,a.STATE) AS STATE,
				coalesce(a.MAIL_ZIPCODE,a.ZIPCODE) AS ZIPCODE ';

$extra['FROM'] = " LEFT OUTER JOIN students_join_address sam ON (ssm.STUDENT_ID=sam.STUDENT_ID AND sam.RESIDENCE='Y')
				LEFT OUTER JOIN address a ON (sam.ADDRESS_ID=a.ADDRESS_ID) " . $extra['FROM'];

$functions['CONTACT_INFO'] = 'makeContactInfo';

$extra['singular'] = 'Student Address';

$extra['plural'] = 'Student Addresses';

$extra2['NoSearchTerms'] = true;

$extra2['SELECT_ONLY'] = 'ssm.STUDENT_ID,p.PERSON_ID,
				p.FIRST_NAME,p.LAST_NAME,p.MIDDLE_NAME,
				sjp.STUDENT_RELATION,sjp.EMERGENCY,sjp.CUSTODY,pjc.TITLE,pjc.VALUE,a.PHONE,sjp.ADDRESS_ID ';

$extra2['FROM'] = ',address a,students_join_address sja LEFT OUTER JOIN students_join_people sjp ON (sja.STUDENT_ID=sjp.STUDENT_ID AND sja.ADDRESS_ID=sjp.ADDRESS_ID)
				LEFT OUTER JOIN people p ON (p.PERSON_ID=sjp.PERSON_ID)
				LEFT OUTER JOIN people_join_contacts pjc ON (pjc.PERSON_ID=p.PERSON_ID) ';

$extra2['WHERE'] = ' AND a.ADDRESS_ID=sja.ADDRESS_ID
				AND sja.STUDENT_ID=ssm.STUDENT_ID
				AND (sjp.CUSTODY=\'Y\' OR sjp.EMERGENCY=\'Y\') ';

$extra2['ORDER_BY'] = 'sjp.CUSTODY';

$extra2['group'] = [ 'STUDENT_ID', 'PERSON_ID' ];

// EXPANDED VIEW AND ADDR BREAKS THIS QUERY ... SO, TURN 'EM OFF.
			if ( ! isset( $_REQUEST['_ROSARIO_PDF'] ) )
			{
				$expanded_view = issetVal( $_REQUEST['expanded_view'] );

$_REQUEST['expanded_view'] = false;

$addr = issetVal( $_REQUEST['addr'] );

unset( $_REQUEST['addr'] );

$contacts_RET = GetStuList( $extra2 );

$_REQUEST['expanded_view'] = $expanded_view;

$_REQUEST['addr'] = $addr;
			}
			else
				unset( $extra['columns_after']['CONTACT_INFO'] );
		}
		else
		{
			if ( $view_other_RET['CONTACT_INFO'][1]['VALUE'] == 'Y'
				&& ! isset( $_REQUEST['_ROSARIO_PDF'] ) )
			{
				$select .= ',ssm.STUDENT_ID AS CONTACT_INFO ';

$extra['columns_after']['CONTACT_INFO'] = button( 'down_phone' );

$functions['CONTACT_INFO'] = 'makeContactInfo';

$extra2 = $extra;

$extra2['NoSearchTerms'] = true;

$extra2['SELECT'] = '';

$extra2['SELECT_ONLY'] = 'ssm.STUDENT_ID,p.PERSON_ID,p.FIRST_NAME,p.LAST_NAME,p.MIDDLE_NAME,
					sjp.STUDENT_RELATION,sjp.EMERGENCY,sjp.CUSTODY,pjc.TITLE,pjc.VALUE,a.PHONE,sjp.ADDRESS_ID ';

if ( ( empty( $extra2['FROM'] )
						|| stripos( $extra2['FROM'], 'address a' ) === false )
					&& empty( $extra['addr'] ) )
				{
					// Fix SQL error table address specified more than once.
					$extra2['FROM'] .= ',address a';
				}

$extra2['FROM'] .= ',students_join_address sja
					LEFT OUTER JOIN students_join_people sjp ON (sja.STUDENT_ID=sjp.STUDENT_ID AND sja.ADDRESS_ID=sjp.ADDRESS_ID)
					LEFT OUTER JOIN people p ON (p.PERSON_ID=sjp.PERSON_ID)
					LEFT OUTER JOIN people_join_contacts pjc ON (pjc.PERSON_ID=p.PERSON_ID) ';

$extra2['WHERE'] .= ' AND a.ADDRESS_ID=sja.ADDRESS_ID
					AND sja.STUDENT_ID=ssm.STUDENT_ID
					AND (sjp.CUSTODY=\'Y\' OR sjp.EMERGENCY=\'Y\') ';

$extra2['ORDER_BY'] = empty( $extra2['ORDER_BY'] ) ? 'sjp.CUSTODY' :
					$extra2['ORDER_BY'] . ',sjp.CUSTODY';

$extra2['group'] = [ 'STUDENT_ID', 'PERSON_ID' ];

$extra2['functions'] = [];

$extra2['link'] = [];

// EXPANDED VIEW AND ADDR BREAKS THIS QUERY ... SO, TURN 'EM OFF.
				$expanded_view = issetVal( $_REQUEST['expanded_view'] );

$_REQUEST['expanded_view'] = false;

$addr = issetVal( $_REQUEST['addr'] );

unset( $_REQUEST['addr'] );

$contacts_RET = GetStuList( $extra2 );

$_REQUEST['expanded_view'] = $expanded_view;

$_REQUEST['addr'] = $addr;
			}

// Student Fields: search Username.
			if ( $view_other_RET['USERNAME'][1]['VALUE'] === 'Y' )
			{
				$extra['columns_after']['USERNAME'] = _( 'Username' );

$select .= ',s.USERNAME';
			}

foreach ( $view_fields_RET as $field )
			{
				$field_key = 'CUSTOM_' . $field['ID'];
				$extra['columns_after'][ $field_key ] = $field['TITLE'];

if ( Config( 'STUDENTS_EMAIL_FIELD' ) === $field['ID'] )
				{
					$functions[ $field_key ] = 'makeEmail';
				}
				else
				{
					$functions[ $field_key ] = makeFieldTypeFunction( $field['TYPE'] );
				}

$select .= ',s.' . $field_key;
			}

if ( $view_address_RET )
			{
				$extra['FROM'] = " LEFT OUTER JOIN students_join_address sam ON (ssm.STUDENT_ID=sam.STUDENT_ID AND sam." . $view_address_RET . "='Y')
					LEFT OUTER JOIN address a ON (sam.ADDRESS_ID=a.ADDRESS_ID) " . $extra['FROM'];

$extra['columns_after'] += [
					'ADDRESS' => _( ucwords( mb_strtolower( str_replace( '_', ' ', $view_address_RET ) ) ) ) . ' ' . _( 'Address' ),
					'CITY' => _( 'City' ),
					'STATE' => _( 'State' ),
					'ZIPCODE' => _( 'Zip Code' ) ];

if ( $view_address_RET != 'MAILING' )
				{
					$select .= ",a.ADDRESS_ID,a.ADDRESS,a.CITY,a.STATE,a.ZIPCODE,a.PHONE,
						ssm.STUDENT_ID AS PARENTS";
				}
				else
				{
					$select .= ",a.ADDRESS_ID,coalesce(a.MAIL_ADDRESS,a.ADDRESS) AS ADDRESS,
						coalesce(a.MAIL_CITY,a.CITY) AS CITY,coalesce(a.MAIL_STATE,a.STATE) AS STATE,
						coalesce(a.MAIL_ZIPCODE,a.ZIPCODE) AS ZIPCODE,a.PHONE,
						ssm.STUDENT_ID AS PARENTS ";
				}

$extra['singular'] = 'Student Address';

$extra['plural'] = 'Student Addresses';

if ( $view_other_RET['HOME_PHONE'][1]['VALUE'] === 'Y' )
				{
					$functions['PHONE'] = 'makePhone';

$extra['columns_after']['PHONE'] = _( 'Home Phone' );
				}

if ( $view_other_RET['GUARDIANS'][1]['VALUE'] === 'Y'
					|| $view_other_RET['ALL_CONTACTS'][1]['VALUE'] === 'Y' )
				{
					$functions['PARENTS'] = 'makeParents';

if ( $view_other_RET['ALL_CONTACTS'][1]['VALUE'] === 'Y' )
					{
						$extra['columns_after']['PARENTS'] = _( 'Contacts' );
					}
					else
						$extra['columns_after']['PARENTS'] = _( 'Guardians' );
				}
			}
			elseif ( ! empty( $_REQUEST['addr'] )
				|| ! empty( $extra['addr'] ) )
			{
				$extra['FROM'] = " LEFT OUTER JOIN students_join_address sam ON (ssm.STUDENT_ID=sam.STUDENT_ID " . issetVal( $extra['STUDENTS_JOIN_ADDRESS'], '' ) . ")
					LEFT OUTER JOIN address a ON (sam.ADDRESS_ID=a.ADDRESS_ID) " . $extra['FROM'];

$distinct = 'DISTINCT ';
			}
		}

$extra['SELECT'] .= $select;
	}
	// Original View.
	else
	{
		if ( isset( $extra['student_fields']['view'] ) )
		{
			if ( ! isset( $extra['columns_after'] ) )
			{
				$extra['columns_after'] = [];
			}

$view_fields_RET = DBGet( "SELECT cf.ID,cf.TYPE,cf.TITLE
				FROM custom_fields cf
				WHERE cf.ID IN (" . $extra['student_fields']['view'] . ")
				ORDER BY cf.SORT_ORDER IS NULL,cf.SORT_ORDER,cf.TITLE" );

foreach ( $view_fields_RET as $field )
			{
				$field_key = 'CUSTOM_' . $field['ID'];

$extra['columns_after'][ $field_key ] = $field['TITLE'];

$select .= ',s.' . $field_key;
			}

$extra['SELECT'] .= $select;
		}

if ( ! empty( $_REQUEST['addr'] )
			|| ! empty( $extra['addr'] ) )
		{
			$extra['FROM'] = " LEFT OUTER JOIN students_join_address sam ON (ssm.STUDENT_ID=sam.STUDENT_ID " . issetVal( $extra['STUDENTS_JOIN_ADDRESS'], '' ) . ")
			LEFT OUTER JOIN address a ON (sam.ADDRESS_ID=a.ADDRESS_ID) " . $extra['FROM'];

$distinct = 'DISTINCT ';
		}
	}

// Get options:
	// SELECT only.
	$is_select_only = ! empty( $extra['SELECT_ONLY'] );

$is_include_inactive = isset( $_REQUEST['include_inactive'] ) && $_REQUEST['include_inactive'] === 'Y';

// Build SELECT.
	$sql = 'SELECT ';

// SELECT only.
	if ( $is_select_only )
	{
		$sql .= $extra['SELECT_ONLY'];
	}
	// Normal SELECT.
	else
	{
		// Student Full Name.
		$sql .= DisplayNameSQL( 's' ) . " AS FULL_NAME,";

// Student Details.
		$sql .='s.LAST_NAME,s.FIRST_NAME,s.MIDDLE_NAME,s.STUDENT_ID,ssm.SCHOOL_ID,ssm.GRADE_ID ' .
			$extra['SELECT'];
	}

switch ( User( 'PROFILE' ) )
	{
		case 'admin':

// Get Search All Schools option.
			$is_search_all_schools = isset( $_REQUEST['_search_all_schools'] )
				&& $_REQUEST['_search_all_schools'] == 'Y';

// Normal SELECT.
			if ( ! $is_select_only )
			{

// Search All Schools.
				if ( $is_search_all_schools )
				{
					// School Title.
					$sql .= ",(SELECT sch.TITLE FROM schools sch
						WHERE ssm.SCHOOL_ID=sch.ID
						AND sch.SYEAR='" . UserSyear() . "') AS SCHOOL_TITLE";
				}

// Include Inactive Students.
				if ( $is_include_inactive )
				{
					$active = "'" . DBEscapeString( '<span style="color:green">' . _( 'Active' ) . '</span>' ) . "'";

$inactive = "'" . DBEscapeString( '<span style="color:red">' . _( 'Inactive' ) . '</span>' ) . "'";

$sql .= ',' . db_case(
						[
							"(ssm.SYEAR='" . UserSyear() . "'
								AND ('" . $extra['DATE'] . "'>=ssm.START_DATE
									AND ('" . $extra['DATE'] . "'<=ssm.END_DATE
										OR ssm.END_DATE IS NULL ) ) )",
							'TRUE',
							$active,
							$inactive
						] ) . ' AS ACTIVE';

$extra['columns_after']['ACTIVE'] = _( 'Status' );
				}

}

// FROM.
			$sql .= " FROM students s JOIN student_enrollment ssm ON (ssm.STUDENT_ID=s.STUDENT_ID";

if ( $is_include_inactive )
			{
				// Include Inactive Students: enrollment.
				//$sql .= " AND ssm.ID=(SELECT max(ID) FROM student_enrollment WHERE STUDENT_ID=ssm.STUDENT_ID AND SYEAR<='".UserSyear()."')";
				$sql .= " AND ssm.ID=( SELECT ID
					FROM student_enrollment
					WHERE STUDENT_ID=ssm.STUDENT_ID
					AND SYEAR='" . UserSyear() . "'
					ORDER BY SYEAR DESC,START_DATE DESC
					LIMIT 1 )";
			}
			else
			{
				// Active / Enrolled students.
				$sql .= " AND ssm.SYEAR='" . UserSyear() . "'
					AND ('" . $extra['DATE'] . "'>=ssm.START_DATE
						AND (ssm.END_DATE IS NULL
							OR '" . $extra['DATE'] . "'<=ssm.END_DATE ) )";
			}

if ( UserSchool()
				&& ! $is_search_all_schools )
			{
				$sql .= " AND ssm.SCHOOL_ID='" . UserSchool() . "'";
			}
			else
			{
				// Search All Schools.
				if ( User( 'SCHOOLS' ) )
				{
					$sql .= " AND ssm.SCHOOL_ID IN (" . mb_substr( str_replace( ',', "','", User( 'SCHOOLS' ) ), 2, -2 ) . ") ";
				}

$extra['columns_after']['SCHOOL_TITLE'] = _( 'School' );
			}

break;

case 'teacher':

//$sql = 'SELECT '.$distinct;

$extra['MPTable'] = issetVal( $extra['MPTable'], '' );

// Normal SELECT.
			if ( ! $is_select_only )
			{
				// Include Inactive Students.
				if ( $is_include_inactive )
				{
					$active = "'" . DBEscapeString( '<span style="color:green">' . _( 'Active' ) . '</span>' ) . "'";

$inactive = "'" . DBEscapeString( '<span style="color:red">' . _( 'Inactive' ) . '</span>' ) . "'";

$sql .= ',' . db_case(
						[
							"('" . $extra['DATE'] . "'>=ss.START_DATE
								AND (ss.END_DATE IS NULL
									OR '" . $extra['DATE'] . "'<=ss.END_DATE))
							AND ss.MARKING_PERIOD_ID IN (" . GetAllMP( $extra['MPTable'], $extra['MP'] ) . ")",
							'TRUE',
							$active,
							$inactive
						] ) . ' AS ACTIVE_SCHEDULE';

$extra['columns_after']['ACTIVE'] = _( 'School Status' );
					$extra['columns_after']['ACTIVE_SCHEDULE'] = _( 'Course Status' );
				}
			}

// FROM.
			$sql .= " FROM students s JOIN schedule ss ON (ss.STUDENT_ID=s.STUDENT_ID AND ss.SYEAR='" . UserSyear() . "'";

if ( $is_include_inactive )
			{
				// Include Inactive Students: scheduled.
				$sql .= " AND ss.START_DATE=(SELECT START_DATE
					FROM schedule WHERE STUDENT_ID=s.STUDENT_ID
					AND SYEAR=ss.SYEAR
					AND COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID
					ORDER BY START_DATE DESC LIMIT 1)";
			}
			else
			{
				// Active / Scheduled Students.
				$sql .= " AND ss.MARKING_PERIOD_ID IN (" . GetAllMP( $extra['MPTable'], $extra['MP'] ) . ")
					AND ('" . $extra['DATE'] . "'>=ss.START_DATE
						AND ('" . $extra['DATE'] . "'<=ss.END_DATE
							OR ss.END_DATE IS NULL))";
			}

$sql .= ") JOIN course_periods cp ON (cp.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID AND " .
				( isset( $extra['all_courses'] ) && $extra['all_courses'] === 'Y' ?
					// @since 6.9 Add Secondary Teacher.
					"(cp.TEACHER_ID='" . User( 'STAFF_ID' ) . "'
						OR SECONDARY_TEACHER_ID='" . User( 'STAFF_ID' ) . "')" :
					"cp.COURSE_PERIOD_ID='" . UserCoursePeriod() . "'" ) . ")
				JOIN student_enrollment ssm ON (ssm.STUDENT_ID=s.STUDENT_ID
					AND ssm.SYEAR=ss.SYEAR
					AND ssm.SCHOOL_ID='" . UserSchool() . "'";

// Include Inactive Students: enrollment.
			if ( $is_include_inactive )
			{
				$sql .= " AND ssm.ID=(SELECT ID FROM student_enrollment
					WHERE STUDENT_ID=ssm.STUDENT_ID
					AND SYEAR=ssm.SYEAR
					ORDER BY START_DATE DESC LIMIT 1)";
			}
			// Active / Enrolled Students.
			else
			{
				$sql .= " AND ('" . $extra['DATE'] . "'>=ssm.START_DATE
					AND (ssm.END_DATE IS NULL OR '" . $extra['DATE'] . "'<=ssm.END_DATE))";
			}

break;

case 'parent':
		case 'student':

// FROM.
			$sql .= " FROM students s JOIN student_enrollment ssm ON (ssm.STUDENT_ID=s.STUDENT_ID
				AND ssm.SYEAR='" . UserSyear() . "'
				AND ssm.SCHOOL_ID='" . UserSchool() . "'
				AND ('" . $extra['DATE'] . "'>=ssm.START_DATE
					AND (ssm.END_DATE IS NULL OR '" . $extra['DATE'] . "'<=ssm.END_DATE))
				AND s.STUDENT_ID" . ( ! empty( $extra['ASSOCIATED'] ) ?
					" IN (SELECT STUDENT_ID FROM students_join_users WHERE STAFF_ID='" . (int) $extra['ASSOCIATED'] . "')" :
					"='" . UserStudentID() . "'" );

break;

default:

$error[] = 'Invalid user profile'; // Should never be displayed, so do not translate.

return ErrorMessage( $error, 'fatal' );
	}

// Extra FROM.
	$sql .= ")" . $extra['FROM'] . " WHERE TRUE";

//$sql = appendSQL($sql,array('NoSearchTerms' => $extra['NoSearchTerms']));

// WHERE.
	$sql .= ' ' . $extra['WHERE'] . ' ';

// GROUP BY.
	if ( isset( $extra['GROUP'] ) )
	{
		$sql .= ' GROUP BY ' . $extra['GROUP'];
	}

$sql_before_order_by = $sql;

// ORDER BY.
	if ( ! isset( $extra['ORDER_BY'] )
		&& ! isset( $extra['SELECT_ONLY'] ) )
	{
		$sql .= ' ORDER BY ';

if ( Preferences( 'SORT' ) === 'Grade' )
		{
			$sql .= '(SELECT SORT_ORDER FROM school_gradelevels WHERE ID=ssm.GRADE_ID),';
		}

$sql .= 'FULL_NAME';

if ( isset( $extra['ORDER'] ) )
		{
			$sql .= $extra['ORDER'];
		}
	}
	elseif ( ! empty( $extra['ORDER_BY'] ) )
	{
		$sql .= ' ORDER BY ' . $extra['ORDER_BY'];
	}

if ( $extra['LIMIT'] )
	{
		// @link https://stackoverflow.com/questions/5146978/count-number-of-records-returned-by-group-by
		$count = isset( $extra['GROUP'] ) ? "DISTINCT COUNT(1) OVER ()" : "COUNT(1)";

$sql_count = "SELECT " . $count . mb_substr(
			$sql_before_order_by,
			mb_strpos( $sql_before_order_by, " FROM students s " )
		);

$sql .= SQLLimitForList( $sql_count, (int) $extra['LIMIT'] );
	}

// FJ bugfix if PDF, dont echo SQL.
	if ( ! isset( $_REQUEST['_ROSARIO_PDF'] )
		&& ROSARIO_DEBUG ) // Activate only for debug purpose.
	{
		echo '';
	}

// DBGet group arg.
	$group = issetVal( $extra['group'], [] );

// Execute Query & return.
	return DBGet( $sql, $functions, $group );
}

/**
 * Make Contact Info
 * `DBGet()` callback
 *
 * @uses MakeTipMessage()
 *
 * @param  string $student_id Student ID.
 * @param  string $column     'CONTACT_INFO'.
 *
 * @return string Contact Info tooltip
 */
function makeContactInfo( $student_id, $column )
{
	global $contacts_RET;

if ( ! function_exists( 'MakeTipMessage' ) )
	{
		require_once 'ProgramFunctions/TipMessage.fnc.php';
	}

if ( ! isset( $contacts_RET[ $student_id ] ) )
	{
		return '';
	}

$tipmsg = '';

foreach ( (array) $contacts_RET[ $student_id ] as $person )
	{
		if ( ! $person[1]['FIRST_NAME'] && ! $person[1]['LAST_NAME'] )
		{
			continue;
		}

$img = '';

// PrintClassLists with all contacts.
		if ( isset( $person[1]['CUSTODY'] )
			&& $person[1]['CUSTODY'] == 'Y' )
		{
			$img = 'gavel';
		}
		elseif ( isset( $person[1]['EMERGENCY'] )
			&& $person[1]['EMERGENCY'] == 'Y' )
		{
			$img = 'emergency';
		}

$tipmsg .= '<table class="width-100p cellspacing-0 col1-align-right">';

$tipmsg .= '<tr><td class="size-1">' . ( ! empty( $img ) ? button( $img ) .'&nbsp;' : '' ) .
			$person[1]['STUDENT_RELATION'] .
			'</td><td>' .
			DisplayName(
				$person[1]['FIRST_NAME'],
				$person[1]['LAST_NAME'],
				$person[1]['MIDDLE_NAME']
			) .
			'</td></tr>';

if ( $person[1]['PHONE'] )
		{
			$tipmsg .= '<tr><td class="size-1">' . _( 'Home Phone' ) .
			'</td><td>' . $person[1]['PHONE'] . '</td></tr>';
		}

foreach ( (array) $person as $info )
		{
			if ( $info['TITLE']
				|| $info['VALUE'] )
			{
				$tipmsg .= '<tr><td class="size-1">' . $info['TITLE'] .
				'</td><td>' . $info['VALUE'] . '</td></tr>';
			}
		}

$tipmsg .= '</table><br>';
	}

if ( ! $tipmsg )
	{
		return '';
	}

return MakeTipMessage( $tipmsg, _( 'Contact Information' ), button( 'phone' ) );
}

/**
 * Remove .00 from float string
 *
 * @example if ( $field['TYPE'] === 'numeric' )	$functions[ $field_key ] = 'removeDot00';
 *
 * @see DBGet() callback
 *
 * @param  string $value  Value.
 * @param  string $column Column (optional). Defaults to ''.
 *
 * @return string Value without .00
 */
function removeDot00( $value, $column = '' )
{
	return str_replace( '.00', '', (string) $value );
}

/**
 * Make / Format Email address
 *
 * @example if ( Config( 'STUDENTS_EMAIL_FIELD' ) === $field['ID'] ) $functions['EMAIL'] = 'makeEmail';
 *
 * @since 2.9.10
 *
 * @see DBGet() callback
 *
 * @param  string $email  Email address.
 * @param  string $column Column (optional). Defaults to ''.
 *
 * @return string Formatted email address
 */
function makeEmail( $email, $column = '' )
{
	$email = trim( (string) $email );

if ( $email == '' )
	{
		return '';
	}

// Validate email.
	if ( ! filter_var( $email, FILTER_VALIDATE_EMAIL ) )
	{
		return $email;
	}

return '<a href="mailto:' . $email . '">' . $email . '</a>';
}

/**
 * Make / Format Phone number
 *
 * @example if ( $view_other_RET['HOME_PHONE'][1]['VALUE'] === 'Y' ) $functions['PHONE'] = 'makePhone';
 *
 * @since 2.9.10 Wrap phone inside tel dial link.
 *
 * @see DBGet() callback
 *
 * @param  string $phone  Phone number.
 * @param  string $column Column (optional). Defaults to ''.
 *
 * @return string Formatted phone number
 */
function makePhone( $phone, $column = '' )
{
	global $locale;

$phone = trim( (string) $phone );

if ( $phone === '' )
	{
		return '';
	}

// Keep numbers and extension.
	$unformatted_phone = preg_replace( "/[^0-9\+x]/", "", $phone );

$fphone = $phone;

if ( $unformatted_phone !== $phone )
	{
		// Phone already contains formatting chars, keep them.
	}
	elseif ( mb_strlen( $phone ) === 9 )
	{
		// Spain: 012 345 678.
		$fphone = mb_substr( $phone, 0, 3 ) . ' ' .
			mb_substr( $phone, 3, 3 ) . ' ' . mb_substr( $phone, 6 );
	}
	elseif ( mb_strlen( $phone ) === 10 )
	{
		if ( mb_strpos( $locale, 'fr' ) === 0 )
		{
			// France: 01 23 45 67 89.
			$fphone = mb_substr( $phone, 0, 2 ) . ' ' .
				mb_substr( $phone, 2, 2 ) . ' ' . mb_substr( $phone, 4, 2 ) . ' ' .
				mb_substr( $phone, 6, 2 ) . ' ' . mb_substr( $phone, 8 );
		}
		else
		{
			// US: (012) 345-6789.
			$fphone = '(' . mb_substr( $phone, 0, 3 ) . ') ' .
				mb_substr( $phone, 3, 3 ) . '-' . mb_substr( $phone, 6 );
		}
	}
	elseif ( mb_strlen( $phone ) === 7 )
	{
		// US: 345-6789.
		$fphone = mb_substr( $phone, 0, 3 ) . '-' . mb_substr( $phone, 3 );
	}

$dial_phone = $unformatted_phone;

$extension_pos = mb_strpos( $dial_phone, 'x' );

if ( $extension_pos !== false )
	{
		// Remove extension.
		$dial_phone = mb_substr( $dial_phone, 0, $extension_pos );
	}

return '<a href="' . URLEscape( 'tel:' . $dial_phone ) . '" title="' . AttrEscape( _( 'Call' ) ) . '" class="phone-link">' . $fphone . '</a>';
}

/**
 * Make Parents information popup
 *
 * @see DBGet() callback
 *
 * @global $THIS_RET       current return row
 * @global $view_other_RET checks $view_other_RET['ALL_CONTACTS'][1]['VALUE']
 * @global $_ROSARIO       checks $_ROSARIO['makeParents']
 *
 * @param  string $student_id Student ID.
 * @param  string $column     'PARENTS'.
 *
 * @return string Parents link to information popup or empty string if no Parents found
 */
function makeParents( $student_id, $column )
{
	global $THIS_RET,
		$view_other_RET,
		$_ROSARIO;

if ( $THIS_RET['PARENTS'] != $student_id )
	{
		return $THIS_RET['PARENTS'];
	}

if ( $THIS_RET['ADDRESS_ID'] == '' )
	{
		return '';
	}

$constraint = '';

if ( $_ROSARIO['makeParents'] )
	{
		$constraint = " AND sjp.STUDENT_RELATION IS NULL";

if ( $_ROSARIO['makeParents'] != '!' )
		{
			$constraint = " AND (lower(sjp.STUDENT_RELATION) LIKE '" .
				mb_strtolower( $_ROSARIO['makeParents'] ) . "%')";
		}
	}

if ( $view_other_RET['ALL_CONTACTS'][1]['VALUE'] != 'Y' )
	{
		$constraint .= " AND sjp.CUSTODY='Y'";
	}

$people_RET = DBGet( "SELECT p.PERSON_ID," . DisplayNameSQL( 'p' ) . " AS FULL_NAME,
		sjp.CUSTODY,sjp.EMERGENCY
		FROM students_join_people sjp,people p
		WHERE sjp.PERSON_ID=p.PERSON_ID
		AND sjp.STUDENT_ID='" . (int) $student_id . "'
		AND sjp.ADDRESS_ID='" . (int) $THIS_RET['ADDRESS_ID'] . "'" . $constraint .
		" ORDER BY sjp.CUSTODY,sjp.STUDENT_RELATION,FULL_NAME" );

if ( ! $people_RET )
	{
		return '';
	}

$parents = '';

foreach ( $people_RET as $person )
	{
		$img = $person['CUSTODY'] == 'Y' ? 'gavel' :
			( $person['EMERGENCY'] == 'Y' ? 'emergency' : '' );

$img_title = $person['CUSTODY'] == 'Y' ? _( 'Custody' ) :
			( $person['EMERGENCY'] == 'Y' ? _( 'Emergency' ) : '' );

$parents .= '<div>' . ( ! empty( $img ) ? button( $img, '', '', '" title="' . AttrEscape( $img_title ) ) . '&nbsp;' : '' );

if ( isset( $_REQUEST['_ROSARIO_PDF'] ) )
		{
			$parents .= $person['FULL_NAME'] . '</div>';

continue;
		}

$popup_url = URLEscape( 'Modules.php?modname=misc/ViewContact.php&person_id=' .
			$person['PERSON_ID'] . '&student_id=' . $student_id );

$parents .= '<a href="#" onclick="' . AttrEscape( 'popups.open(
				' . json_encode( $popup_url ) . ',
				"scrollbars=yes,resizable=yes,width=400,height=300"
			); return false;' ) . '">' .
				$person['FULL_NAME'] .
			'</a></div>';
	}

return $parents;
}

/**
 * DeCodeds
 * Decode codeds / exports type (custom) fields values.
 *
 * DBGet() callback function
 *
 * @since 2.9
 *
 * @param string $value  Value.
 * @param string $column Column.
 * @param string $table  'auto'|'staff' (optional). Defaults to 'auto'.
 */
function DeCodeds( $value, $column, $table = 'auto' )
{
	static $decodeds = [];

$field = explode( '_', $column );

if ( $table === 'auto' )
	{
		$table = $field[0];
	}

if ( ! isset( $decodeds[ $column ] ) )
	{
		$RET = DBGet( "SELECT TYPE,SELECT_OPTIONS
			FROM " . DBEscapeIdentifier( $table . '_FIELDS' ) .
			" WHERE ID='" . (int) $field[1] . "'" );

if ( $RET[1]['TYPE'] === 'exports' )
		{
			$select_options = [];

$options = explode( "\r", str_replace( [ "\r\n", "\n" ], "\r", $RET[1]['SELECT_OPTIONS'] ) );

foreach ( $options as $option )
			{
				$option = explode( '|', $option );

if ( $option[0] != ''
					&& $option[1] != '' )
				{
					$select_options[ $option[0] ] = $option[1];
				}
			}

$RET[1]['SELECT_OPTIONS'] = $select_options;

$decodeds[ $column ] = $RET[1];
		}
		else
			$decodeds[ $column ] = true;
	}

if ( $value == '' )
	{
		return '';
	}

if ( $decodeds[ $column ]['SELECT_OPTIONS'][ $value ] != '' )
	{
		if ( isset( $_REQUEST['_ROSARIO_PDF'] )
			&& $_REQUEST['LO_save'] )
		{
			return $decodeds[ $column ]['SELECT_OPTIONS'][ $value ];
		}

return $value;
	}

return '<span style="color:red">' . $value . '</span>';
}

/**
 * Make Checkbox
 *
 * DBGet() callback function
 *
 * @since 2.9
 *
 * @param  string $value  Checkbox value.
 * @param  string $column Column.
 *
 * @return string         'Yes' or 'No'.
 */
function makeCheckbox( $value, $column )
{
	return $value ? _( 'Yes' ) : _( 'No' );
}

/**
 * Make Textarea
 *
 * DBGet() callback function
 *
 * @uses marked JS plugin for MarkDown rendering called using the .markdown-to-html CSS class
 * @uses ColorBox jQuery plugin to display various lines texts in ListOutput on mobiles called using the .rt2colorBox CSS class
 *
 * @since 2.9
 *
 * @param  string $value  Textarea value.
 * @param  string $column Column.
 *
 * @return string         Markdown rendered text.
 */
function makeTextarea( $value, $column )
{
	static $i = 1;

return $value != '' ?
		'<div id="' . $column . $i++ . '" class="rt2colorBox"><div class="markdown-to-html">' .
			$value . '</div></div>' :
		'';
}

/**
 * Make Select Multiple from Options
 *
 * DBGet() callback function
 *
 * @since 6.9.1
 *
 * @param  string $value  Multiple value, example: '||123||456||'.
 * @param  string $column Column.
 *
 * @return string         Comma separated values, example: '123, 456'.
 */
function makeMultiple( $value, $column )
{
	if ( $value == '' )
	{
		return '';
	}

$values = explode( '||', trim( $value, '||' ) );

return implode( ', ', $values );
}

/**
 * Append:
 * - RosarioSIS ID(s)
 * - Last Name
 * - First Name
 * - Grade Level
 * - (Not) Grade Levels
 * - Address (City, State, Zip code)
 * Search terms to Students SQL WHERE part
 *
 * @example $extra['WHERE'] .= appendSQL( '', $extra );
 *
 * @global $_ROSARIO sets $_ROSARIO['SearchTerms']
 *
 * @uses SearchField()
 *
 * @param  string $sql   Students SQL query.
 * @param  array  $extra Extra for SQL request (optional). Defaults to empty array.
 *
 * @return string Appended SQL WHERE part
 */
function appendSQL( $sql, $extra = [] )
{
	global $_ROSARIO;

$_ROSARIO['SearchTerms'] = issetVal( $_ROSARIO['SearchTerms'], '' );

$no_search_terms = isset( $extra['NoSearchTerms'] ) && $extra['NoSearchTerms'];

// RosarioSIS ID(s).
	if ( isset( $_REQUEST['stuid'] )
		&& ! empty( $_REQUEST['stuid'] ) )
	{
		// FJ allow comma separated list of student IDs.
		$stuid_array = explode( ',', $_REQUEST['stuid'] );

$stuids = array_filter( $stuid_array, function( $stuid ){
			return (string)(int)$stuid == $stuid && $stuid > 0;
		});

if ( $stuids )
		{
			$stuids = implode( ',', $stuids );

//$sql .= " AND ssm.STUDENT_ID IN '".$_REQUEST['stuid']."'";
			$sql .= " AND ssm.STUDENT_ID IN (" . $stuids . ")";

if ( ! $no_search_terms )
			{
				$_ROSARIO['SearchTerms'] .= '<b>' . sprintf( _( '%s ID' ), Config( 'NAME' ) ) .
					': </b>' . $stuids . '<br />';
			}
		}
	}

// Last Name.
	if ( isset( $_REQUEST['last'] )
		&& $_REQUEST['last'] !== '' )
	{
		$last_name = [
			'COLUMN_NAME' => 'LAST_NAME',
			'VALUE' => $_REQUEST['last'],
			'TITLE' => _( 'Last Name' ),
			'TYPE' => 'text',
			'SELECT_OPTIONS' => null,
		];

$sql .= SearchField( $last_name, 'student', $extra );
	}

// First Name.
	if ( isset( $_REQUEST['first'] )
		&& $_REQUEST['first'] !== '' )
	{
		$first_name = [
			'COLUMN_NAME' => 'FIRST_NAME',
			'VALUE' => $_REQUEST['first'],
			'TITLE' => _( 'First Name' ),
			'TYPE' => 'text',
			'SELECT_OPTIONS' => null,
		];

$sql .= SearchField( $first_name, 'student', $extra );
	}

// Grade Level.
	if ( isset( $_REQUEST['grade'] )
		&& $_REQUEST['grade'] !== ''
		&& (string) (int) $_REQUEST['grade'] == $_REQUEST['grade']
		&& $_REQUEST['grade'] > 0 )
	{
		$sql .= " AND ssm.GRADE_ID = '" . $_REQUEST['grade'] . "'";

if ( ! $no_search_terms )
		{
			$_ROSARIO['SearchTerms'] .= '<b>' . _( 'Grade Level' ) . ': </b>' .
				GetGrade( $_REQUEST['grade'] ) . '<br />';
		}
	}

// (Not) Grade Levels.
	if ( isset( $_REQUEST['grades'] )
		&& count( $_REQUEST['grades'] ) )
	{
		$is_grades_not = isset( $_REQUEST['grades_not'] ) && $_REQUEST['grades_not'] === 'Y';

if ( ! $no_search_terms )
		{
			$_ROSARIO['SearchTerms'] .= '<b>' . ngettext( 'Grade Level', 'Grade Levels', count( $_REQUEST['grades'] ) ) .
				': </b>' . ( $is_grades_not ? _( 'Excluded' ) . ' ' : '' );
		}

$grade_list = $sep = '';

foreach ( (array) $_REQUEST['grades'] as $grade_id => $y )
		{
			$grade_list .= $sep . "'" . $grade_id . "'";

if ( ! $no_search_terms )
			{
				$_ROSARIO['SearchTerms'] .= $sep . GetGrade( $grade_id );
			}

$sep = ', ';
		}

if ( ! $no_search_terms )
		{
			$_ROSARIO['SearchTerms'] .= '<br />';
		}

$sql .= " AND ssm.GRADE_ID " . ( $is_grades_not ? 'NOT ' : '' ) . " IN (" . $grade_list . ")";
	}

// Address (City, State, Zip code) (contains, case insensitive).
	if ( ! empty( $_REQUEST['addr'] ) )
	{
		$sql .= " AND (LOWER(a.ADDRESS) LIKE '%" . mb_strtolower( $_REQUEST['addr'] ) .
			"%' OR LOWER(a.CITY) LIKE '" . mb_strtolower( $_REQUEST['addr'] ) .
			"%' OR LOWER(a.STATE)='" . mb_strtolower( $_REQUEST['addr'] ) .
			"' OR a.ZIPCODE LIKE '" . $_REQUEST['addr'] . "%')";

if ( ! $no_search_terms )
		{
			$_ROSARIO['SearchTerms'] .= '<b>' . _( 'Address contains' ) . ': </b>' .
				DBUnescapeString( $_REQUEST['addr'] ) . '<br />';
		}
	}

return $sql;
}

/**
 * Get make / format function by field type.
 *
 * @example $extra['functions'][ 'CUSTOM_' . $field['ID'] ] = makeFieldTypeFunction( $field['TYPE'] );
 *
 * @since 2.9.10
 * @since 6.9.1 Add multiple type case.
 *
 * @param string  $field_type Field type.
 * @param string  $table      'auto'|'staff' (optional). Defaults to 'auto'.
 *
 * @return string             Make function name or empty if type not found.
 */
function makeFieldTypeFunction( $field_type, $table = 'auto' )
{
	switch ( $field_type )
	{
		case 'date':

return 'ProperDate';

case 'numeric':

return 'removeDot00';

case 'codeds':
		case 'exports':

if ( $table === 'staff' )
			{
				return 'StaffDecodeds';
			}

return 'DeCodeds';

case 'radio':

return 'makeCheckbox';

case 'textarea':

return 'makeTextarea';

case 'multiple':

return 'makeMultiple';
	}

return '';
}

/**
 * Get Display Name SQL (SELECT)
 * Must be used when retrieving Student or User full names.
 *
 * @since 3.7
 * @since 9.3 SQL use CONCAT() instead of pipes || for MySQL compatibility
 *
 * @example "SELECT " . DisplayNameSQL( 's' ) . " AS FULL_NAME FROM students s"
 *
 * @uses Config DISPLAY_NAME option.
 *
 * @param  string $table_alias Table alias, optional.
 * @return string              Display name SQL (with table alias).
 */
function DisplayNameSQL( $table_alias = '' )
{
	$display_name = Config( 'DISPLAY_NAME' );

// Values have %s. placeholders for table alias.
	$display_names = [
		"CONCAT(FIRST_NAME,' ',LAST_NAME)" => "CONCAT(%s.FIRST_NAME,' ',%s.LAST_NAME)",
		"CONCAT(FIRST_NAME,' ',LAST_NAME,coalesce(NULLIF(CONCAT(' ',NAME_SUFFIX),' '),''))" => "CONCAT(%s.FIRST_NAME,' ',%s.LAST_NAME,coalesce(NULLIF(CONCAT(' ',%s.NAME_SUFFIX),' '),''))",
		"CONCAT(FIRST_NAME,coalesce(NULLIF(CONCAT(' ',MIDDLE_NAME,' '),'  '),' '),LAST_NAME)" => "CONCAT(%s.FIRST_NAME,coalesce(NULLIF(CONCAT(' ',%s.MIDDLE_NAME,' '),'  '),' '),%s.LAST_NAME)",
		"CONCAT(FIRST_NAME,', ',LAST_NAME,coalesce(NULLIF(CONCAT(' ',MIDDLE_NAME),' '),''))" => "CONCAT(%s.FIRST_NAME,', ',%s.LAST_NAME,coalesce(NULLIF(CONCAT(' ',%s.MIDDLE_NAME),' '),''))",
		"CONCAT(LAST_NAME,' ',FIRST_NAME)" => "CONCAT(%s.LAST_NAME,' ',%s.FIRST_NAME)",
		"CONCAT(LAST_NAME,', ',FIRST_NAME)" => "CONCAT(%s.LAST_NAME,', ',%s.FIRST_NAME)",
		"CONCAT(LAST_NAME,', ',FIRST_NAME,coalesce(NULLIF(CONCAT(' ',MIDDLE_NAME),' '),''))" => "CONCAT(%s.LAST_NAME,', ',%s.FIRST_NAME,' ',coalesce(%s.MIDDLE_NAME,''))",
		"CONCAT(LAST_NAME,coalesce(NULLIF(CONCAT(' ',MIDDLE_NAME,' '),'  '),' '),FIRST_NAME)" => "CONCAT(%s.LAST_NAME,coalesce(NULLIF(CONCAT(' ',%s.MIDDLE_NAME,' '),'  '),' '),%s.FIRST_NAME)",
	];

if ( ! isset( $display_names[ $display_name ] ) )
	{
		$display_name = key( $display_names );
	}

if ( $table_alias )
	{
		$display_name = $display_names[ $display_name ];

$display_name = str_replace( '%s', $table_alias, $display_name );
	}

return $display_name;
}

/**
 * Get Display Name from values.
 * Must be used when displaying Student or User full names.
 *
 * @since 3.7
 * @since 9.3 SQL use CONCAT() instead of pipes || for MySQL compatibility
 *
 * @example echo DisplayName( 'John', 'Smith', 'Simon', 'Jr.' );
 *
 * @uses Config DISPLAY_NAME option.
 *
 * @param  string $first_name  First Name.
 * @param  string $last_name   Last Name.
 * @param  string $middle_name Middle Name (optional).
 * @param  string $name_suffix Suffix (optional).
 * @return string              Display Name.
 */
function DisplayName( $first_name, $last_name, $middle_name = '', $name_suffix = '' )
{
	$display_name = Config( 'DISPLAY_NAME' );

// Values are not SQL formatted.
	$display_names = [
		"CONCAT(FIRST_NAME,' ',LAST_NAME)" => "FIRST_NAME LAST_NAME",
		"CONCAT(FIRST_NAME,' ',LAST_NAME,coalesce(NULLIF(CONCAT(' ',NAME_SUFFIX),' '),''))" => "FIRST_NAME LAST_NAME NAME_SUFFIX",
		"CONCAT(FIRST_NAME,coalesce(NULLIF(CONCAT(' ',MIDDLE_NAME,' '),'  '),' '),LAST_NAME)" => "FIRST_NAME MIDDLE_NAME LAST_NAME",
		"CONCAT(FIRST_NAME,', ',LAST_NAME,coalesce(NULLIF(CONCAT(' ',MIDDLE_NAME),' '),''))" => "FIRST_NAME, LAST_NAME MIDDLE_NAME",
		"CONCAT(LAST_NAME,' ',FIRST_NAME)" => "LAST_NAME FIRST_NAME",
		"CONCAT(LAST_NAME,', ',FIRST_NAME)" => "LAST_NAME, FIRST_NAME",
		"CONCAT(LAST_NAME,', ',FIRST_NAME,coalesce(NULLIF(CONCAT(' ',MIDDLE_NAME),' '),''))" => "LAST_NAME, FIRST_NAME MIDDLE_NAME",
		"CONCAT(LAST_NAME,coalesce(NULLIF(CONCAT(' ',MIDDLE_NAME,' '),'  '),' '),FIRST_NAME)" => "LAST_NAME MIDDLE_NAME FIRST_NAME",
	];

$display_name = isset( $display_names[ $display_name ] ) ?
		$display_names[ $display_name ] :
		$display_names["CONCAT(FIRST_NAME,' ',LAST_NAME)"];

return str_replace(
		[ 'FIRST_NAME', 'LAST_NAME', 'MIDDLE_NAME', 'NAME_SUFFIX' ],
		[ $first_name, $last_name, $middle_name, $name_suffix ],
		$display_name
	);
}

/**
 * Make Tip Message containing Student or User Photo
 *
 * Callback for DBGet() column formatting
 *
 * @since 3.8
 *
 * @uses MakeStudentPhotoTipMessage()
 * @uses MakeUserPhotoTipMessage()
 *
 * @see ProgramFunctions/TipMessage.fnc.php
 *
 * @global $THIS_RET, see DBGet()
 *
 * @param  string $full_name Student or User Full Name
 * @param  string $column    'FULL_NAME'
 *
 * @return string Student or User Full Name + Tip Message containing Student Photo
 */
function makePhotoTipMessage( $full_name, $column )
{
	global $THIS_RET;

require_once 'ProgramFunctions/TipMessage.fnc.php';

if ( ! empty( $_REQUEST['LO_save'] )
		|| isset( $_REQUEST['_ROSARIO_PDF'] ) )
	{
		return $full_name;
	}

if ( ! empty( $THIS_RET['STAFF_ID'] ) )
	{
		$rollover_id = ( empty( $THIS_RET['ROLLOVER_ID'] ) ? 0 : $THIS_RET['ROLLOVER_ID'] );

return MakeUserPhotoTipMessage( $THIS_RET['STAFF_ID'], $full_name, $rollover_id );
	}
	elseif ( ! empty( $THIS_RET['STUDENT_ID'] ) )
	{
		return MakeStudentPhotoTipMessage( $THIS_RET['STUDENT_ID'], $full_name );
	}
}

← 返回文件管理器