编辑文件 - CreateParents.php

✏️ 正在编辑: CreateParents.php

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

<?php

require_once 'ProgramFunctions/Template.fnc.php';
require_once 'ProgramFunctions/Substitutions.fnc.php';
require_once 'ProgramFunctions/SendEmail.fnc.php';

// This script will automatically create parent accounts and associate students based on an email address which is part of the student record.

DrawHeader( ProgramTitle() );

// Remove current student.

if ( UserStudentID() )
{
	unset( $_SESSION['student_id'] );

// Unset student ID & redirect URL.
	RedirectURL( 'student_id' );
}

// The $email_column corresponds to a student field or an address field which is created for the email address.  The COLUMN_# is the column in the
// students table or the address table which holds the student contact email address.  You will need to create the column and inspect rosario database
// to determine the email column and assign it here.
// Making the email address an address field is useful when using 'ganged' address (address record is shared by multiple students by using the 'add
// existing address feature).
// The column name should start with 's.' if a student field or 'a.' if an address field.
//FJ Moodle Integrator: the "family" email field must be different from the student email field in the Moodle/config.inc.php
$email_column = ''; //example: 'a.CUSTOM_2'

//save $email_column var in SESSION

if ( isset( $_SESSION['email_column'] ) && empty( $email_column ) )
{
	$email_column = $_SESSION['email_column'];
}
elseif ( isset( $_REQUEST['email_column'] ) )
{
	$email_column = $_SESSION['email_column'] = $_REQUEST['email_column'];
}

if ( empty( $email_column ) )
{
	echo '<form action="' . URLEscape( 'Modules.php?modname=' . $_REQUEST['modname']  ) . '" method="POST">';

//get Student / Address fields
	$student_columns = DBGet( "SELECT CONCAT('s.CUSTOM_',f.ID) AS COLUMN_NAME, f.TITLE, c.TITLE AS CATEGORY
		FROM custom_fields f, student_field_categories c
		WHERE f.TYPE='text'
		AND c.ID=f.CATEGORY_ID
		ORDER BY f.CATEGORY_ID, f.SORT_ORDER" );

$address_columns = DBGet( "SELECT CONCAT('a.CUSTOM_',f.ID) AS COLUMN_NAME, f.TITLE, c.TITLE AS CATEGORY
		FROM address_fields f, address_field_categories c
		WHERE f.TYPE='text'
		AND c.ID=f.CATEGORY_ID
		ORDER BY f.CATEGORY_ID, f.SORT_ORDER" );

//display SELECT input
	$select_html = _( 'Select Parents email field' ) . ': <select id="email_column" name="email_column">';

$select_html .= '<optgroup label="' . AttrEscape( _( 'Student Fields' ) ) . '">';

foreach ( (array) $student_columns as $student_column )
	{
		$select_html .= '<option value="' . AttrEscape( $student_column['COLUMN_NAME'] ) . '">' . ParseMLField( $student_column['CATEGORY'] ) . ' - ' . ParseMLField( $student_column['TITLE'] ) . '</option>';
	}

$select_html .= '</optgroup><optgroup label="' . AttrEscape( _( 'Address Fields' ) ) . '">';

foreach ( (array) $address_columns as $address_column )
	{
		$select_html .= '<option value="' . AttrEscape( $address_column['COLUMN_NAME'] ) . '">' . ParseMLField( $address_column['CATEGORY'] ) . ' - ' . ParseMLField( $address_column['TITLE'] ) . '</option>';
	}

$select_html .= '</optgroup></select>';

DrawHeader( '', '', $select_html );

echo '<br /><div class="center">' . SubmitButton( _( 'Select Parents email field' ) ) . '</div>';
	echo '</form>';
}

// A list of potential users is obtained from the student contacts with an address.  The student must have at least one such contact.  Students which
// have the same email will be associated to the same user and grouped together in the list and even though each will have contacts listed for selection
// only that of the first student selected in the group will be used in the creation of the account.

// Parent users are created with the following profile id. '3' is the default 'parent' profile.
$profile_id = '3';

// end of user configuration

if ( $_REQUEST['modfunc'] === 'save'
	&& AllowEdit()
	&& ! empty( $email_column ) )
{
	// If $test email is set then this script will only 'go through the motions' and email the results to the $test_email address instead of parents
	// no accounts are created and no associations are made.  Use this to verify the behavior and email operation before actual use.
	$test_email = issetVal( $_REQUEST['test_email'] );

// Set the from and cc emails here - the emails can be comma separated list of emails.
	$reply_to = '';

if ( filter_var( User( 'EMAIL' ), FILTER_VALIDATE_EMAIL ) )
	{
		$reply_to = User( 'NAME' ) . ' <' . User( 'EMAIL' ) . '>';
	}
	elseif ( ! filter_var( $test_email, FILTER_VALIDATE_EMAIL ) )
	{
		$error[] = _( 'You must set the <b>test mode email</b> or have a user email address to use this script.' );

ErrorMessage( $error, 'fatal' );
	}

// New for when parent account was created new.
	$subject['new'] = _( 'New Parent Account' );
	// Old for when parent account was existing.
	$subject['old'] = _( 'Updated Parent Account' );

if ( isset( $_REQUEST['inputcreateparentstext_new'] ) )
	{
		// FJ add Template.
		$createparentstext = $_REQUEST['inputcreateparentstext_new'] .
			'__BLOCK2__' . $_REQUEST['inputcreateparentstext_old'];

SaveTemplate( $createparentstext );
	}

$template = GetTemplate();

list( $message['new'], $message['old'] ) = explode( '__BLOCK2__', $template );

if ( ! empty( $_REQUEST['student'] ) )
	{
		$st_list = implode( ',', array_map( 'intval', $_REQUEST['student'] ) );

$email_table_prefix = mb_substr( $email_column, 0, 2 ) === 's.' ? 's.' : 'a.';

// Remove table prefix so we can use DBEscapeIdentifier() below (and prevent SQL injection).
		$email_column = mb_substr( $email_column, 2 );

$extra['SELECT'] = ",trim(lower(" . $email_table_prefix . DBEscapeIdentifier( $email_column ) . ")) AS EMAIL";
		$extra['SELECT'] .= ",(SELECT STAFF_ID
			FROM staff
			WHERE trim(lower(EMAIL))=trim(lower(" . $email_table_prefix . DBEscapeIdentifier( $email_column ) . "))
			AND PROFILE='parent'
			AND SYEAR=ssm.SYEAR
			LIMIT 1) AS STAFF_ID";
		$extra['WHERE'] = " AND s.STUDENT_ID IN (" . $st_list . ")";
		$extra['group'] = [ 'EMAIL' ];
		$extra['addr'] = true;
		$extra['STUDENTS_JOIN_ADDRESS'] = "AND sam.RESIDENCE='Y'";

$RET = GetStuList( $extra );
		//echo '<pre>'; var_dump($RET); echo '</pre>';

foreach ( (array) $RET as $email => $students )
		{
			unset( $id );

$password = '';

$student_id = $students[1]['STUDENT_ID'];

if ( ! $students[1]['STAFF_ID'] )
			{
				if ( ! empty( $_REQUEST['contact'][$student_id] ) )
				{
					// Username = email.
					$tmp_username = $username = $students[1]['EMAIL'];

$username_exists_sql = "SELECT STAFF_ID
						FROM staff
						WHERE upper(USERNAME)=upper('" . $username . "')
						AND SYEAR='" . UserSyear() . "'";

$i = 1;

// If username already exists.

while ( DBGet( $username_exists_sql ) )
					{
						// Fix infinite loop when username already exists.
						$username_exists_sql = "SELECT STAFF_ID
							FROM staff
							WHERE upper(USERNAME)=upper('" . $username . "')
							AND SYEAR='" . UserSyear() . "'";

$username = $tmp_username . $i++;
					}

$user = DBGet( "SELECT FIRST_NAME,MIDDLE_NAME,LAST_NAME
						FROM people
						WHERE PERSON_ID='" . (int) $_REQUEST['contact'][$student_id] . "'" );

$user = $user[1];

// Use big random number for parent password generation.
					$password = $username . rand( 1, 99999999999 );

// Moodle integrator / password.
					$password = ucfirst( $password ) . '*';

if ( ! $test_email )
					{
						$password_encrypted = encrypt_password( $password );

$id = DBInsert(
							'staff',
							[
								'SYEAR' => UserSyear(),
								'PROFILE' => 'parent',
								'PROFILE_ID' => (int) $profile_id,
								'FIRST_NAME' => DBEscapeString( $user['FIRST_NAME'] ),
								'MIDDLE_NAME' => DBEscapeString( $user['MIDDLE_NAME'] ),
								'LAST_NAME' => DBEscapeString( $user['LAST_NAME'] ),
								'USERNAME' => DBEscapeString( $username ),
								'PASSWORD' => $password_encrypted,
								'EMAIL' => DBEscapeString( $students[1]['EMAIL'] ),
							],
							'id'
						);

// Hook.
						do_action( 'Custom/CreateParents.php|create_user' );

$staff = DBGet( "SELECT " . DisplayNameSQL() . " AS NAME,USERNAME
							FROM staff
							WHERE STAFF_ID='" . (int) $id . "'" );
					}
					else
					{
						$id = true;
						$staff = [
							1 => [
								'NAME' => DisplayName(
									$user['FIRST_NAME'],
									$user['LAST_NAME'],
									$user['MIDDLE_NAME']
								),
								'USERNAME' => $username,
							],
						];
					}

$account = 'new';
				}
			}
			else // If user already exists.
			{
				$id = $students[1]['STAFF_ID'];

$staff = DBGet( "SELECT " . DisplayNameSQL() . " AS NAME,USERNAME
					FROM staff
					WHERE STAFF_ID='" . (int) $id . "'" );

$account = 'old';
			}

if ( ! $id )
			{
				// No Staff ID, failure.
				$RET[$email][1]['RESULT'] = _( 'Fail' );

continue;
			}

$staff = $staff[1];
			$student_list = '';

foreach ( (array) $students as $student )
			{
				// Fix SQL error, check if student not already associated!
				$parent_associated_to_student_RET = DBGet( "SELECT 1
					FROM students_join_users
					WHERE STAFF_ID='" . (int) $id . "'
					AND STUDENT_ID='" . (int) $student['STUDENT_ID'] . "'" );

if ( ! $test_email
					&& ! $parent_associated_to_student_RET )
				{
					// Join user to student.
					DBInsert(
						'students_join_users',
						[
							'STAFF_ID' => (int) $id,
							'STUDENT_ID' => (int) $student['STUDENT_ID'],
						]
					);

// Hook.
					do_action( 'Custom/CreateParents.php|user_assign_role' );
				}

$student_list .= str_replace( '&nbsp;', ' ', $student['FULL_NAME'] ) . "\r";
			}

$substitutions = [
				'__PARENT_NAME__' => $staff['NAME'],
				'__ASSOCIATED_STUDENTS__' => $student_list,
				'__SCHOOL_ID__' => SchoolInfo( 'TITLE' ),
				'__USERNAME__' => $staff['USERNAME'],
				'__PASSWORD__' => $password,
			];

$msg = SubstitutionsTextMake( $substitutions, $message[$account] );

$to = empty( $test_email ) ? $students[1]['EMAIL'] : $test_email;

$result = true;

if ( ! empty( $_REQUEST['modfunc_send_email_notification'] ) )
			{
				// @since 5.8 Send email notification to Parents is optional.
				$result = SendEmail( $to, $subject[$account], $msg, $reply_to );
			}

$RET[$email][1]['PARENT'] = $staff['NAME'];
			$RET[$email][1]['USERNAME'] = $staff['USERNAME'];
			$RET[$email][1]['PASSWORD'] = $password;
			$RET[$email][1]['RESULT'] = $result ? _( 'Success' ) : _( 'Fail' );
		}

$columns = [
			'FULL_NAME' => _( 'Student' ),
			'PARENT' => _( 'Parent' ),
			'USERNAME' => _( 'Username' ),
			'PASSWORD' => _( 'Password' ),
			'EMAIL' => _( 'Email' ),
			'RESULT' => _( 'Result' ),
		];

ListOutput(
			$RET,
			$columns,
			'Creation Result',
			'Creation Results',
			false,
			[ 'EMAIL' ],
			[ 'save' => false, 'search' => false ]
		);

// Unset student, contact & redirect URL.
		RedirectURL( [ 'student', 'contact' ] );
	}
	else
	{
		$error[] = _( 'You must choose at least one student.' );

// Unset modfunc, student, contact & redirect URL.
		RedirectURL( [ 'modfunc', 'student', 'contact' ] );
	}

// Reset $email_column var.
	unset( $_SESSION['email_column'], $email_column );
}

echo ErrorMessage( $error );

if ( ! $_REQUEST['modfunc'] && ! empty( $email_column ) )
{
	if ( $_REQUEST['search_modfunc'] === 'list' )
	{
		echo '<form action="' . URLEscape( 'Modules.php?modname=' . $_REQUEST['modname'] . '&modfunc=save' ) . '" method="POST">';

$extra['header_right'] = SubmitButton( _( 'Create Parent Accounts for Selected Students' ) );

// @since 5.8 Send email notification to Parents is optional.
		$extra['extra_header_left'] = CheckboxInput(
			'',
			'modfunc_send_email_notification',
			_( 'Send email notification to Parents' ),
			'',
			true,
			'Yes',
			'No',
			false,
			'autocomplete="off" onclick="$(\'#email-form-inputs\').toggle();"'
		);

$extra['extra_header_left'] .= '<table id="email-form-inputs" class="width-100p hide">';

$template = GetTemplate();

list( $template_new, $template_old ) = explode( '__BLOCK2__', $template );

$extra['extra_header_left'] .= '<tr class="st"><td>' .
		'<textarea name="inputcreateparentstext_new" id="inputcreateparentstext_new" cols="100" rows="5">' .
		$template_new . '</textarea>' .
		FormatInputTitle(
			_( 'New Parent Account' ) . ' - ' . _( 'Email Text' ),
			'inputcreateparentstext_new'
		) . '<br /><br /></td></tr>';

$extra['extra_header_left'] .= '<tr class="st"><td>' .
		'<textarea name="inputcreateparentstext_old" id="inputcreateparentstext_old" cols="100" rows="5">' .
		$template_old . '</textarea>' .
		FormatInputTitle(
			_( 'Updated Parent Account' ) . ' - ' . _( 'Email Text' ),
			'inputcreateparentstext_old'
		) . '<br /><br /></td></tr>';

$substitutions = [
			'__PARENT_NAME__' => _( 'Parent Name' ),
			'__ASSOCIATED_STUDENTS__' => _( 'Associated Students' ),
			'__SCHOOL_ID__' => _( 'School' ),
			'__USERNAME__' => _( 'Username' ),
			'__PASSWORD__' => _( 'Password' ),
		];

$extra['extra_header_left'] .= '<tr class="st"><td class="valign-top">' .
			SubstitutionsInput( $substitutions ) .
		'<hr></td></tr>';

$extra['extra_header_left'] .= '<tr class="st"><td>' . _( 'Test Mode' ) . ':<br />' .
		TextInput(
			'',
			'test_email',
			_( 'Email' ),
			'maxlength=255 type="email" placeholder="' . AttrEscape( _( 'Email' ) ) . '" size="24"',
			false
		) . '</td></tr>';

$extra['extra_header_left'] .= '</table>';
	}

$email_table_prefix = mb_substr( $email_column, 0, 2 ) === 's.' ? 's.' : 'a.';

// Remove table prefix so we can use DBEscapeIdentifier() below (and prevent SQL injection).
	$email_column = mb_substr( $email_column, 2 );

$extra['SELECT'] = ",s.STUDENT_ID AS CHECKBOX,trim(lower(" . $email_table_prefix . DBEscapeIdentifier( $email_column ) . ")) AS EMAIL,s.STUDENT_ID AS CONTACT";
	// @todo SQL performance: really slow subquery.
	// But DO NOT use LEFT OUTER JOIN, cannot LIMIT 1...
	$extra['SELECT'] .= ",(SELECT STAFF_ID
		FROM staff
		WHERE trim(lower(EMAIL))=trim(lower(" . $email_table_prefix . DBEscapeIdentifier( $email_column ) . "))
		AND PROFILE='parent'
		AND SYEAR=ssm.SYEAR
		LIMIT 1) AS STAFF_ID";

$extra['SELECT'] .= ",(SELECT 1
		FROM students_join_users sju,staff st
		WHERE sju.STUDENT_ID=s.STUDENT_ID
		AND st.STAFF_ID=sju.STAFF_ID
		AND st.SYEAR='" . UserSyear() . "'
		AND trim(lower(st.EMAIL))=trim(lower(" . $email_table_prefix . DBEscapeIdentifier( $email_column ) . "))
		LIMIT 1) AS HAS_ASSOCIATED_PARENTS";
	//$extra['WHERE'] = " AND " . $email_table_prefix . DBEscapeIdentifier( $email_column ) . " IS NOT NULL";

$extra['link'] = [ 'FULL_NAME' => false ];
	$extra['functions'] = [
		'CHECKBOX' => '_makeChooseCheckbox',
		'CONTACT' => '_makeContactSelect',
		'EMAIL' => '_makeEmail',
	];
	$extra['columns_before'] = [ 'CHECKBOX' => MakeChooseCheckbox( '', '', 'student' ) ];
	$extra['columns_after'] = [ 'EMAIL' => _( 'Email' ), 'CONTACT' => _( 'Contact' ) ];
	$extra['LO_group'] = $extra['group'] = [ 'EMAIL' ];
	$extra['addr'] = true;
	$extra['SELECT'] .= ",a.ADDRESS_ID";

$extra['STUDENTS_JOIN_ADDRESS'] = issetVal( $extra['STUDENTS_JOIN_ADDRESS'], '' );
	$extra['STUDENTS_JOIN_ADDRESS'] .= " AND sam.RESIDENCE='Y'";

$extra['search_title'] = _( 'Students having Contacts' );

$extra['singular'] = 'Contact';
	$extra['plural'] = 'Contacts';

Search( 'student_id', $extra );

if ( $_REQUEST['search_modfunc'] === 'list' )
	{
		echo '<br /><div class="center">' . SubmitButton( _( 'Create Parent Accounts for Selected Students' ) ) . '</div>';
		echo '</form>';
	}
}

/**
 * Make Choose Checkbox
 *
 * Local function
 * DBGet() callback
 *
 * @param  string $value  STUDENT_ID value.
 * @param  string $column 'CHECKBOX'.
 *
 * @return string Checkbox or empty string if no Email or has no Parents
 */
function _makeChooseCheckbox( $value, $column )
{
	global $THIS_RET;

if ( empty( $THIS_RET['STAFF_ID'] ) )
	{
		$has_parents = DBGet( "SELECT 1
			FROM students_join_people sjp,people p
			WHERE p.PERSON_ID=sjp.PERSON_ID
			AND sjp.STUDENT_ID='" . (int) $value . "'
			AND sjp.ADDRESS_ID='" . (int) $THIS_RET['ADDRESS_ID'] . "'
			ORDER BY sjp.STUDENT_RELATION" );
	}
	else
	{
		$has_parents = true;
	}

if ( filter_var( $THIS_RET['EMAIL'], FILTER_VALIDATE_EMAIL )
		&& $has_parents
		&& ! $THIS_RET['HAS_ASSOCIATED_PARENTS'] )
	{
		return MakeChooseCheckbox( $value, $column );
	}
	else
	{
		return '';
	}
}

/**
 * @param $value
 * @param $column
 * @return mixed
 */
function _makeContactSelect( $value, $column )
{
	global $THIS_RET;

if ( empty( $THIS_RET['STAFF_ID'] ) )
	{
		$RET = DBGet( "SELECT sjp.PERSON_ID,sjp.STUDENT_RELATION,
			p.FIRST_NAME,p.LAST_NAME,p.MIDDLE_NAME
		FROM students_join_people sjp,people p
		WHERE p.PERSON_ID=sjp.PERSON_ID
		AND sjp.STUDENT_ID='" . (int) $value . "'
		AND sjp.ADDRESS_ID='" . (int) $THIS_RET['ADDRESS_ID'] . "'
		ORDER BY sjp.STUDENT_RELATION" );
	}
	else
	{
		$RET = DBGet( "SELECT '' AS PERSON_ID,STAFF_ID AS STUDENT_RELATION,
			FIRST_NAME,LAST_NAME,MIDDLE_NAME
			FROM staff WHERE
			STAFF_ID='" . (int) $THIS_RET['STAFF_ID'] . "'" );
	}

if ( empty( $RET ) )
	{
		return '';
	}

$checked = ' checked';

$return = '';

$i = 0;

foreach ( (array) $RET as $contact )
	{
		$return .= ( $contact['PERSON_ID'] ? '<label><input type="radio" name="contact[' . $value . ']" value=' . $contact['PERSON_ID'] . $checked . ' /> ' : '&nbsp; ' );

$return .= DisplayName(
			$contact['FIRST_NAME'],
			$contact['LAST_NAME'],
			$contact['MIDDLE_NAME']
		);

$return .= ' (' . $contact['STUDENT_RELATION'] . ')';

if ( $contact['PERSON_ID'] )
		{
			$return .= '</label>&nbsp; ';
		}

$checked = '';
	}

return $return;
}

/**
 * Make Email address
 *
 * Local function
 * DBGet() callback
 *
 * @since 4.3
 *
 * @param  string $value  Contact email address.
 * @param  string $column 'EMAIL'.
 * @return string         Email address or red cross plus link to Student Info program.
 */
function _makeEmail( $value, $column )
{
	global $THIS_RET;

if ( filter_var( $value, FILTER_VALIDATE_EMAIL )
		|| isset( $_REQUEST['_ROSARIO_PDF'] ) )
	{
		return $value;
	}

if ( ! $value )
	{
		$return = button( 'x' );
	}
	else
	{
		$return = '<span style="color:red">' . $value . '</span>';
	}

if ( AllowEdit( 'Students/Student.php' ) )
	{
		// Link to User Info program.
		$return .= ' <a href="' . URLEscape( 'Modules.php?modname=Students/Student.php&student_id=' . $THIS_RET['STUDENT_ID'] ) . '">' .
			_( 'Student Info' ) . '</a>';
	}

return $return;
}

← 返回文件管理器