<?php

function get_student_list(& $addition)
{	global $view_other_array,$_SYSTEM, $DBType;
//echo "ddddddddddddddddddd".$_REQUEST['servicemod'].get_user('PROFILE');
	if(!$addition)
		$addition = array();

	if((!$addition['SELECT_ONLY'] || strpos($addition['SELECT_ONLY'],'CLASSLEVEL_ID')!==false) && !$addition['functions']['CLASSLEVEL_ID'])
		$functions = array('CLASSLEVEL_ID'=>'get_classlevel','GROUP_ID'=>'get_group_title');
	else
		$functions = array();

	if($addition['functions'])
		$functions += $addition['functions'];
	
	if(!$addition['DATE'])
		$addition['DATE'] = get_db_date();
        
        if($_REQUEST['customised_view']=='true' && !$addition['SELECT_ONLY'])
	{
		if(!$addition['columns_after'])
			$addition['columns_after'] = array();
		$view_fields_array = db_request(db_execute("SELECT cf.ID,cf.TYPE,cf.TITLE FROM SYSTEM_USER_SETTINGS puc,STUDENT_FIELDS cf LEFT OUTER JOIN STUDENT_FIELD_CATEGORIES sfc ON (cf.CATEGORY_ID=sfc.ID) WHERE puc.TITLE=CAST(cf.ID AS TEXT) AND puc.PROGRAM='StudentFieldsView' AND puc.USERNAME='".get_user('USERNAME')."' AND puc.VALUE='Y' ORDER BY sfc.SORT_ORDER,cf.SORT_ORDER"));
		$view_address_array = db_request(db_execute("SELECT VALUE FROM SYSTEM_USER_SETTINGS WHERE PROGRAM='StudentFieldsView' AND TITLE='ADDRESS' AND USERNAME='".get_user('USERNAME')."'"));
		$view_address_array = $view_address_array[1]['VALUE'];
		$view_other_array = db_request(db_execute("SELECT TITLE,VALUE FROM SYSTEM_USER_SETTINGS WHERE PROGRAM='StudentFieldsView' AND TITLE IN ('CONTACT_INFO','HOME_PHONE','GUARDIANS','ALL_CONTACTS') AND USERNAME='".get_user('USERNAME')."'"),array(),array('TITLE'));
		$view_adm_fields_array = db_request(db_execute("SELECT TITLE,VALUE FROM SYSTEM_USER_SETTINGS WHERE PROGRAM='StudentFieldsView' AND TITLE IN ('APP_SCHOOL_ID','CPT_GROUP_ID','FACULTY_ID','SPECIALTY_ID','SPECIALIZATION_ID','CPTV_CAT_ID','TYPE_OF_STUDY') AND USERNAME='".get_user('USERNAME')."'"),array(),array('TITLE'));
		if(!count($view_fields_array) && !isset($view_address_array) && !isset($view_other_array['CONTACT_INFO']))
		{
			$view_fields_array = db_request(db_execute("SELECT cf.ID,cf.TYPE,cf.TITLE FROM SYSTEM_CONFIG puc,STUDENT_FIELDS cf LEFT OUTER JOIN STUDENT_FIELD_CATEGORIES sfc ON (cf.CATEGORY_ID=sfc.ID) WHERE puc.TITLE=CAST(cf.ID AS TEXT) AND puc.PROGRAM='StudentFieldsView' AND puc.VALUE='Y' ORDER BY sfc.SORT_ORDER,cf.SORT_ORDER"));
			$view_address_array = db_request(db_execute("SELECT VALUE FROM SYSTEM_CONFIG WHERE PROGRAM='StudentFieldsView' AND TITLE='ADDRESS'"));			
			$view_address_array = $view_address_array[1]['VALUE'];
			$view_other_array = db_request(db_execute("SELECT TITLE,VALUE FROM SYSTEM_CONFIG WHERE PROGRAM='StudentFieldsView' AND TITLE IN ('CONTACT_INFO','HOME_PHONE','GUARDIANS','ALL_CONTACTS')"),array(),array('TITLE'));
		}
//		var_dump($view_adm_fields_array);
		if($view_adm_fields_array)
		{
//			$addition['SELECT'] .= ',s.STUDENT_ID AS APP_SCHOOL_ID,s.STUDENT_ID AS CPT_GROUP_ID,s.STUDENT_ID AS FACULTY_ID,s.STUDENT_ID AS SPECIALTY_ID,s.STUDENT_ID AS SPECIALIZATION_ID,s.STUDENT_ID AS CPTV_CAT_ID,s.STUDENT_ID AS TYPE_OF_STUDY';
			$addition['columns_after'] =  array('APP_SCHOOL_ID'=>_('School Name'),
							    'CPT_GROUP_ID'=>_('Competitive Group'),
							    'FACULTY_ID'=>_('Faculty Title'),
							    'SPECIALTY_ID'=>_('Major Title'),
							    'SPECIALIZATION_ID'=>_('Special Subject Title'),
							    'CPTV_CAT_ID'=>_('Competitive Category'),
							    'TYPE_OF_STUDY'=>_('Form of Education')
			
							);
//			var_dump($addition['columns_after']);
//			foreach($view_adm_fields_array as $adm_field=>$true)

			foreach($addition['columns_after'] as $adm_field=>$true)
			{

				if($view_adm_fields_array[$adm_field] && substr($_REQUEST['servicemod'],0,9) == 'mod_apply')
				{
//					echo $adm_field;


//(CASE WHEN NOT EXISTS (SELECT '' FROM student_spec_fields ssf2 WHERE s.student_id=ssf2.STUDENT_ID AND ssf2.ENROLLMENT_CODE LIKE '%C%') THEN 
//(SELECT TOP 1 TITLE FROM SPECIALTY sps, STUDENT_SPEC_FIELDS ssf WHERE sps.ID=ssf.SPECIALTY_ID AND s.STUDENT_ID=ssf.STUDENT_ID AND ssf.PRIORITY='1' AND sps.SCHOOL_YEAR = '2010') 
//ELSE
//(SELECT TOP 1 TITLE FROM SPECIALTY sps, STUDENT_SPEC_FIELDS ssf WHERE sps.ID=ssf.SPECIALTY_ID AND s.STUDENT_ID=ssf.STUDENT_ID AND ssf.ENROLLMENT_CODE LIKE '%C%' AND sps.SCHOOL_YEAR = '2010') 
//END)
//AS SPECIALTY_ID,
					if($adm_field == 'CPT_GROUP_ID')
						$addition['SELECT'] .= ",".db_limit("(SELECT TITLE FROM APP_ADM_GROUPS aag, STUDENT_SPEC_FIELDS ssf WHERE aag.ID=ssf.GROUP_ID AND s.STUDENT_ID=ssf.STUDENT_ID AND ssf.PRIORITY='1' AND aag.SCHOOL_YEAR = '".get_school_year()."') AS CPT_GROUP_ID",1);
					elseif($adm_field == 'FACULTY_ID')
						$addition['SELECT'] .= ",(CASE WHEN NOT EXISTS (SELECT '' FROM STUDENT_SPEC_FIELDS ssf2 WHERE s.STUDENT_ID=ssf2.STUDENT_ID AND ssf2.ENROLLMENT_CODE LIKE '%C%') THEN ".
							db_limit("(SELECT TITLE FROM FACULTY f, STUDENT_SPEC_FIELDS ssf WHERE f.ID=ssf.FACULTY_ID AND s.STUDENT_ID=ssf.STUDENT_ID AND ssf.PRIORITY='1' AND f.SCHOOL_YEAR = '".get_school_year()."')",1)." ELSE ".
							db_limit("(SELECT TITLE FROM FACULTY f, STUDENT_SPEC_FIELDS ssf WHERE f.ID=ssf.FACULTY_ID AND s.STUDENT_ID=ssf.STUDENT_ID AND ssf.ENROLLMENT_CODE LIKE '%C%' AND f.SCHOOL_YEAR = '".get_school_year()."')",1).
							" END) AS FACULTY_ID";
					elseif($adm_field == 'SPECIALTY_ID')
						$addition['SELECT'] .= ",(CASE WHEN NOT EXISTS (SELECT '' FROM STUDENT_SPEC_FIELDS ssf2 WHERE s.STUDENT_ID=ssf2.STUDENT_ID AND ssf2.ENROLLMENT_CODE LIKE '%C%') THEN ".
							db_limit("(SELECT TITLE FROM SPECIALTY sps, STUDENT_SPEC_FIELDS ssf WHERE sps.ID=ssf.SPECIALTY_ID AND s.STUDENT_ID=ssf.STUDENT_ID AND ssf.PRIORITY='1' AND sps.SCHOOL_YEAR = '".get_school_year()."')",1)." ELSE ".
							db_limit("(SELECT TITLE FROM SPECIALTY sps, STUDENT_SPEC_FIELDS ssf WHERE sps.ID=ssf.SPECIALTY_ID AND s.STUDENT_ID=ssf.STUDENT_ID AND ssf.ENROLLMENT_CODE LIKE '%C%' AND sps.SCHOOL_YEAR = '".get_school_year()."')",1).
							" END) AS SPECIALTY_ID";
					elseif($adm_field == 'SPECIALIZATION_ID')
						$addition['SELECT'] .= ",(CASE WHEN NOT EXISTS (SELECT '' FROM STUDENT_SPEC_FIELDS ssf2 WHERE s.STUDENT_ID=ssf2.STUDENT_ID AND ssf2.ENROLLMENT_CODE LIKE '%C%') THEN ".
							db_limit("(SELECT TITLE FROM SPECIALIZATION spz, STUDENT_SPEC_FIELDS ssf WHERE spz.ID=ssf.SPECIALIZATION_ID AND s.STUDENT_ID=ssf.STUDENT_ID AND ssf.PRIORITY='1' AND spz.SCHOOL_YEAR = '".get_school_year()."')",1)." ELSE ".
							db_limit("(SELECT TITLE FROM SPECIALIZATION spz, STUDENT_SPEC_FIELDS ssf WHERE spz.ID=ssf.SPECIALIZATION_ID AND s.STUDENT_ID=ssf.STUDENT_ID AND ssf.ENROLLMENT_CODE LIKE '%C%' AND spz.SCHOOL_YEAR = '".get_school_year()."')",1).
							" END) AS SPECIALIZATION_ID";
					elseif($adm_field == 'CPTV_CAT_ID')
						$addition['SELECT'] .= ",".db_limit("(SELECT TITLE FROM APP_EDU_FORMS aef, STUDENT_SPEC_FIELDS ssf WHERE aef.ID=ssf.EDU_FORM_ID AND s.STUDENT_ID=ssf.STUDENT_ID AND ssf.PRIORITY='1' AND aef.SCHOOL_YEAR = '".get_school_year()."') AS CPTV_CAT_ID",1);
					elseif($adm_field == 'TYPE_OF_STUDY')
						$addition['SELECT'] .= ",".db_limit("(SELECT TITLE FROM TYPE_OF_STUDY tos, STUDENT_SPEC_FIELDS ssf WHERE tos.ID=ssf.TYPE_OF_STUDY AND s.STUDENT_ID=ssf.STUDENT_ID AND ssf.PRIORITY='1' AND tos.SCHOOL_YEAR = '".get_school_year()."') AS TYPE_OF_STUDY",1);
					elseif($adm_field == 'APP_SCHOOL_ID')
						$addition['SELECT'] .= ",".db_limit("(SELECT TITLE FROM SCHOOLS sc, STUDENT_SPEC_FIELDS ssf WHERE sc.ID=ssf.SCHOOL_ID AND s.STUDENT_ID=ssf.STUDENT_ID AND ssf.PRIORITY='1') AS APP_SCHOOL_ID",1);

////					$functions[$adm_field] = getStaticFuncByField($adm_field);
/*					if($adm_field == 'APP_SCHOOL_ID')
						$functions[$adm_field] = 'get_AppSchool';
					elseif($adm_field == 'CPT_GROUP_ID')
						$functions[$adm_field] = 'get_AppCptvGroup';
					elseif($adm_field == 'FACULTY_ID')
						$functions[$adm_field] = 'get_AppFaculty';
					elseif($adm_field == 'SPECIALTY_ID')
						$functions[$adm_field] = 'get_AppMajor';
					elseif($adm_field == 'SPECIALIZATION_ID')
						$functions[$adm_field] = 'get_AppSpecSubj';
					elseif($adm_field == 'CPTV_CAT_ID')
						$functions[$adm_field] = 'get_AppCptvCat';
					elseif($adm_field == 'TYPE_OF_STUDY')
						$functions[$adm_field] = 'get_AppEduForm';
*/
				}
				else
					unset($addition['columns_after'][$adm_field]);
			}
		}
		if(!count($view_fields_array) && !isset($view_address_array) && !isset($view_other_array['CONTACT_INFO']))
		{
			if(!$addition['APPLY_MODE'] && !count($view_fields_array) && !isset($view_address_array) && !isset($view_other_array['CONTACT_INFO']))
			{
			$addition['columns_after'] = array('CONTACT_INFO'=>_('Contact Info'),'CFIELD_50000'=>_('Gender'),'CFIELD_50001'=>_('Ethnicity'),'ADDRESS'=>_('Mailing Address'),'CITY'=>_('City'),'STATE'=>_('State'),'ZIPCODE'=>_('Zipcode')) + $addition['columns_after'];
			if($_REQUEST['CREATEPDF'])
				unset($addition['columns_after']['CONTACT_INFO']);
			$select = ',s.STUDENT_ID AS CONTACT_INFO,s.CFIELD_50000,s.CFIELD_50001,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 ';
			$addition['FROM'] = " LEFT OUTER JOIN STUDENTS_IN_ADDRESS sam ON (ssm.STUDENT_ID=sam.STUDENT_ID AND sam.MAILING='Y') LEFT OUTER JOIN ADDRESS a ON (sam.ADDRESS_ID=a.ADDRESS_ID) ".$addition['FROM'];
			$functions['CONTACT_INFO'] = 'makeContactInfo';
			$addition['singular'] = _('Students');
			$addition['plural'] = _('Students');

			$addition2['DisableSearchResDisplay'] = true;
			$addition2['addr'] = false;
			// GridOutputCount is superfolous here ... plus, it adds extra data to the array which throws off makeContactInfo
			$addition2['GridOutputCount'] = false;
			$addition2['SELECT_ONLY'] = 'ssm.STUDENT_ID,s.CFIELD_50012,COALESCE(p.PERSON_ID,\'0\') AS PERSON_ID,p.FIRST_NAME,p.LAST_NAME,sjp.STUDENT_RELATION,pjc.TITLE,pjc.VALUE,addr.PHONE,p.EMAIL,sjp.ADDRESS_ID,COALESCE(sjp.CUSTODY,\'N\') AS CUSTODY,addr.ADDRESS,addr.CITY,addr.STATE,addr.ZIPCODE ';
			$addition2['FROM'] .= ',ADDRESS addr,STUDENTS_IN_ADDRESS sja LEFT OUTER JOIN STUDENTS_IN_SR sjp ON (sja.STUDENT_ID=sjp.STUDENT_ID AND sja.ADDRESS_ID=sjp.ADDRESS_ID AND (sjp.CUSTODY=\'Y\' OR sjp.EMERGENCY=\'Y\')) LEFT OUTER JOIN STUDENT_RELATIONS p ON (p.PERSON_ID=sjp.PERSON_ID) LEFT OUTER JOIN STUDENT_RELATIONS_IN_CONTACTS pjc ON (pjc.PERSON_ID=p.PERSON_ID) ';
                            
			$addition2['WHERE'] .= ' AND addr.ADDRESS_ID=sja.ADDRESS_ID AND sja.STUDENT_ID=ssm.STUDENT_ID ';
			if($addition2['ORDER_BY'])
				$addition2['ORDER_BY'] .= ',';
			$addition2['ORDER_BY'] .= 'COALESCE(sjp.CUSTODY,\'N\') DESC';
			$addition2['group'] = array('STUDENT_ID','ADDRESS_ID','PERSON_ID');
	
			if(!$_REQUEST['CREATEPDF'])
			{
				$customised_view = $_REQUEST['customised_view'];
				$_REQUEST['customised_view'] = false;
				$search_terms = $_SYSTEM['SearchTerms'];
				$_SYSTEM['makeContactInfo'] = get_student_list($addition2);
				$_REQUEST['customised_view'] = $customised_view;
				$_SYSTEM['SearchTerms'] = $search_terms;
			}
			else
				unset($addition2['columns_after']['CONTACT_INFO']);
			}			
		}
		else
		{
			if($view_other_array['CONTACT_INFO'][1]['VALUE']=='Y' && !$_REQUEST['CREATEPDF'])
			{
				$select .= ',NULL AS CONTACT_INFO ';
				$addition['columns_after']['CONTACT_INFO'] = _('Contact Information');
				$functions['CONTACT_INFO'] = 'makeContactInfo';

				$addition2 = $addition;
				$addition2['DisableSearchResDisplay'] = true;
				$addition2['addr'] = false;
				// GridOutputCount is superfolous here ... plus, it adds extra data to the array which throws off makeContactInfo
				$addition2['GridOutputCount'] = false;
				$addition2['SELECT'] = '';
				$addition2['SELECT_ONLY'] = 'ssm.STUDENT_ID,s.CFIELD_50012,COALESCE(p.PERSON_ID,\'0\') AS PERSON_ID,p.FIRST_NAME,p.LAST_NAME,sjp.STUDENT_RELATION,pjc.TITLE,pjc.VALUE,addr.PHONE,p.EMAIL,sjp.ADDRESS_ID,COALESCE(sjp.CUSTODY,\'N\') AS CUSTODY,addr.ADDRESS,addr.CITY,addr.STATE,addr.ZIPCODE ';
				$addition2['FROM'] .= ',ADDRESS addr,STUDENTS_IN_ADDRESS sja LEFT OUTER JOIN STUDENTS_IN_SR sjp ON (sja.STUDENT_ID=sjp.STUDENT_ID AND sja.ADDRESS_ID=sjp.ADDRESS_ID AND (sjp.CUSTODY=\'Y\' OR sjp.EMERGENCY=\'Y\')) LEFT OUTER JOIN STUDENT_RELATIONS p ON (p.PERSON_ID=sjp.PERSON_ID) LEFT OUTER JOIN STUDENT_RELATIONS_IN_CONTACTS pjc ON (pjc.PERSON_ID=p.PERSON_ID) ';
				$addition2['WHERE'] .= ' AND addr.ADDRESS_ID=sja.ADDRESS_ID AND sja.STUDENT_ID=ssm.STUDENT_ID ';
				if($addition2['ORDER_BY'])
					$addition2['ORDER_BY'] .= ',';
				$addition2['ORDER_BY'] .= 'COALESCE(sjp.CUSTODY,\'N\') DESC';
				$addition2['group'] = array('STUDENT_ID','ADDRESS_ID','PERSON_ID');
				$addition2['functions'] = array();
				$addition2['link'] = array();

				$customised_view = $_REQUEST['customised_view'];
				$_REQUEST['customised_view'] = false;
				$search_terms = $_SYSTEM['SearchTerms'];
				$_SYSTEM['makeContactInfo'] = get_student_list($addition2);
				$_REQUEST['customised_view'] = $customised_view;
				$_SYSTEM['SearchTerms'] = $search_terms;
			}
			foreach($view_fields_array as $field)
			{
				$addition['columns_after']['CFIELD_'.$field['ID']] = $field['TITLE'];
				if($field['TYPE']=='date')
					$functions['CFIELD_'.$field['ID']] = 'ProperDate';
				if($field['TYPE']=='numeric')
					$functions['CFIELD_'.$field['ID']] = 'removeDot00';
				if($field['TYPE']=='multiple')
					$functions['CFIELD_'.$field['ID']] = 'prepareMultiple';
				$select .= ',s.CFIELD_'.$field['ID'];
			}
			if($view_address_array)
			{
				$addition['FROM'] = " LEFT OUTER JOIN STUDENTS_IN_ADDRESS sam ON (ssm.STUDENT_ID=sam.STUDENT_ID AND sam.".$view_address_array."='Y') LEFT OUTER JOIN ADDRESS a ON (sam.ADDRESS_ID=a.ADDRESS_ID) ".$addition['FROM'];
				if(Preferences('LANGUAGE')=='ru_RU')
				{
				    $addition['columns_after'] += array('ADDRESS'=>_(ucwords(strLower(strtolower(str_replace('_',' ',$view_address_array))))),'CITY'=>_('City'),'STATE'=>_('State'),'ZIPCODE'=>_('Zipcode'));
				}else{
				    $addition['columns_after'] += array('ADDRESS'=>_(ucwords(strLower(strtolower(str_replace('_',' ',$view_address_array))))).' '._('Address'),'CITY'=>_('City'),'STATE'=>_('State'),'ZIPCODE'=>_('Zipcode'));
				}    
				if($view_address_array!='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 ";
				if(substr($_REQUEST['servicemod'],0,9) != 'mod_apply')
				{
					$addition['singular'] = _('Student Addresses');
					$addition['plural'] = _('Amount of Student Addresses');
				}
				else
				{
					$addition['singular'] = _('Applicant Addresses');
					$addition['plural'] = _('Amount of Applicant Addresses');
				}
				if($view_other_array['HOME_PHONE'][1]['VALUE']=='Y')
				{
					$functions['PHONE'] = 'makePhone';
					$addition['columns_after']['PHONE'] = _('Home Phone');
				}
				if($view_other_array['GUARDIANS'][1]['VALUE']=='Y' || $view_other_array['ALL_CONTACTS'][1]['VALUE']=='Y')
				{
					$functions['PARENTS'] = 'makeParents';
					if($view_other_array['ALL_CONTACTS'][1]['VALUE']=='Y')
						$addition['columns_after']['PARENTS'] = _('Contacts');
					else
						$addition['columns_after']['PARENTS'] = _('Guardians');
				}
			}
		}
		$addition['SELECT'] .= $select;
	}
	elseif($_REQUEST['addr'] || $addition['addr'])
	{
		//need to check why it duplicate when print out docs
		if(strpos($addition['FROM'],'LEFT OUTER JOIN STUDENTS_IN_ADDRESS sam ON') === false)
			$addition['FROM'] = " LEFT OUTER JOIN STUDENTS_IN_ADDRESS sam ON (ssm.STUDENT_ID=sam.STUDENT_ID ".$addition['STUDENTS_IN_ADDRESS'].") LEFT OUTER JOIN ADDRESS a ON (sam.ADDRESS_ID=a.ADDRESS_ID) ".$addition['FROM'];
		$distinct = 'DISTINCT ';
	}

	if(Preferences('NAME_FORMAT')=='WCM')
		$full_name = "s.LAST_NAME".CCAT."', '".CCAT."s.FIRST_NAME".CCAT."' '".CCAT."COALESCE(s.MIDDLE_NAME,' ')";
	else
		$full_name = "s.LAST_NAME".CCAT."' '".CCAT."s.FIRST_NAME".CCAT."' '".CCAT."COALESCE(s.MIDDLE_NAME,' ')";
//		$full_name = "s.FIRST_NAME".CCAT."' '".CCAT."COALESCE(s.MIDDLE_NAME,' ')".CCAT."' '".CCAT."s.LAST_NAME";
	switch(get_user('PROFILE'))
	{
                case 'online_apply':
		case 'admin':
//			if($_REQUEST['include_inactive']=='Y')
//				$distinct = 'DISTINCT ';
			//$sql = 'SELECT '.$distinct;
			$sql = 'SELECT ';
			if($addition['SELECT_ONLY'])
				$sql .= $addition['SELECT_ONLY'];
			else
			{
				$sql .= $full_name." AS FULL_NAME,s.LAST_NAME,s.FIRST_NAME,s.MIDDLE_NAME,s.STUDENT_ID,ssm.SCHOOL_ID,ssm.SCHOOL_ID AS LIST_SCHOOL_ID,ssm.GROUP_ID,ssm.CLASSLEVEL_ID,s.PROFILE ".$addition['SELECT'];
	                        if($addition['APPLY_MODE'])
	                        {
		                        $sql .= ",ssm.CFIELD_15,ssm.CFIELD_16,ai.APPLICANT_VERIFIED,ai.ID_TITLE,s.STUDENT_ID AS APP_SPEC";
		                        $functions['CFIELD_15'] = 'get_addtype';
		                        $functions['CFIELD_16'] = 'get_teacher_allschools';
		                        $functions['APPLICANT_VERIFIED'] = 'get_applicant_vfd';
		    //                    $functions['APP_SPEC'] = 'get_AppSpec';
		                        $addition['columns_after']['CFIELD_15'] = _('Type of addition');
		                        $addition['columns_after']['CFIELD_16'] = _('Added By');
		                        $addition['columns_after']['ID_TITLE'] = _('Unique ID');
//		                        $addition['columns_after']['APP_SPEC'] = _('Competitive Group');
		                        $addition['columns_after']['APPLICANT_VERIFIED'] = _('Verified');
				}
//				if($_REQUEST['include_inactive']=='Y')
//					$sql .= ','.db_case(array("ssm.SCHOOL_YEAR!='".get_school_year()."'","'"._('Graduated')."'","('".get_db_date()."' BETWEEN ssm.START_DATE AND ssm.END_DATE OR ssm.END_DATE IS NULL)",'true',"'"._('Active')."'","'"._('Inactive')."'")).' AS ACTIVE';
                                if($_REQUEST['servicemod']!=='applicants_online'){
                                    if($_REQUEST['include_inactive']=='Y')
                                    {
                                            $_SYSTEM['db_case_dont_array_0'] = true;
                                            if($DBType == 'mssql')
                                              $sql .= ','.db_case(array("ssm.SCHOOL_YEAR <> '".get_school_year()."'","'"._('Graduated')."'","('".get_db_date()."' BETWEEN ssm.START_DATE AND ssm.END_DATE OR ssm.END_DATE IS NULL)","'"._('Active')."'","'"._('Inactive')."'")).' AS ACTIVE';
                                            else
                                              $sql .= ','.db_case(array("ssm.SCHOOL_YEAR <> '".get_school_year()."'","'"._('Graduated')."'","('".get_db_date()."' BETWEEN ssm.START_DATE AND ssm.END_DATE OR ssm.END_DATE IS NULL)='true'","'"._('Active')."'","'"._('Inactive')."'")).' AS ACTIVE';
                                            $_SYSTEM['db_case_dont_array_0'] = false;
                                    }
                                }
			}
			unset($app_sql);
			if($addition['APPLY_MODE'])
			{
//				$addition['FROM'] .= ',STUDENT_SPEC_FIELDS ssf';
				if(strpos($addition['FROM'],"APP_IDENTIFY ai") == false)
				    $addition['FROM'] .= ',APP_IDENTIFY ai';
				$app_sql = 'AND ssm.STUDENT_ID = ai.STUDENT_ID AND s.STUDENT_ID = ai.STUDENT_ID '; //AND ssf.STUDENT_ID=s.STUDENT_ID AND ssf.STUDENT_ID=ssm.STUDENT_ID AND ssf.STUDENT_ID=ai.STUDENT_ID AND ssf.PRIORITY=\'1\' ';
			}
			$sql .= " FROM STUDENTS s,STUDENT_REGISTRATION ssm ".$addition['FROM']." WHERE ssm.STUDENT_ID=s.STUDENT_ID $app_sql";
//			echo "0000000000000000000000";
//			var_dump($addition['APPLY_MODE']);
                        if($addition['APPLY_MODE'] || $_REQUEST['servicemod']=='applicants_online')
                        {
                            $sql .=  "AND s.PROFILE = 'applicant'";
                            unset($addition['APPLY_MODE']);
                        }
                        else
                            $sql .=  " AND (s.PROFILE IS NULL OR s.PROFILE = 'student') ";
			if(get_school_id() && $_REQUEST['_search_all_schools']!='Y')
				$sql .= " AND ssm.SCHOOL_ID='".get_school_id()."' ";
			else
			{
				$schools = substr(str_replace(",","','",get_user('SCHOOLS')),2,-2);
				if($schools)
					$sql .= " AND ssm.SCHOOL_ID IN ($schools) ";
				$addition['columns_after']['LIST_SCHOOL_ID'] = _('School');
				$functions['LIST_SCHOOL_ID'] = 'get_school';
			}
			//fixed for documents
                        if($_REQUEST['servicemod']!=='applicants_online')
                        {
                            if($_REQUEST['include_inactive']!='Y' && !$_REQUEST['doc_id'])
                                    $sql .= " AND ssm.SCHOOL_YEAR='".get_school_year()."' AND ('".$addition['DATE']."' BETWEEN ssm.START_DATE AND ssm.END_DATE OR (ssm.END_DATE IS NULL)) ";
                            elseif($_REQUEST['doc_id'])
                        	    $sql .= " AND ssm.SCHOOL_YEAR='".get_school_year()."' ";
                            elseif(!$addition['allow_duplicates'])
                            {
                                    if($_REQUEST['include_graduates']=='Y')
                                            $operator = '<=';
                                    else
                                            $operator = '=';

                                    $school_id = '';//for search in all schools
                                    if($_REQUEST['_search_all_schools']!='Y')
                                           $school_id = " AND SCHOOL_ID='".get_school_id()."'";

                                    $sql .= " AND ssm.ID=(".db_limit("SELECT ID FROM STUDENT_REGISTRATION WHERE STUDENT_ID=ssm.STUDENT_ID AND SCHOOL_YEAR".$operator."'".get_school_year()."'".$school_id." ORDER BY START_DATE DESC ",1).") ";
                                    $addition['columns_after']['ACTIVE'] = _('Status');
                            }
                            else
                                    $sql .= " AND ssm.SCHOOL_YEAR='".get_school_year()."' ";
                        }
			//$sql .= " AND ssm.SCHOOL_YEAR='".get_school_year()."' AND ('".get_db_date()."' BETWEEN ssm.START_DATE AND ssm.END_DATE OR ssm.END_DATE IS NULL)";
		break;
		case 'teacher':
			//$sql = 'SELECT '.$distinct;
			$sql = 'SELECT ';
			if($addition['SELECT_ONLY'])
				$sql .= $addition['SELECT_ONLY'];
			else
			{
				$sql .= ' '.$full_name." AS FULL_NAME,s.STUDENT_ID,ssm.SCHOOL_ID,ssm.GROUP_ID,ssm.CLASSLEVEL_ID,cp.COURSE_SECTION_ID ".$addition['SELECT'];
				if($_REQUEST['include_inactive']=='Y')
				{
					if($DBType == 'mssql')
					{
						$sql .= ", CASE WHEN (ssm.SCHOOL_YEAR='".get_school_year()."' AND ('".$addition['DATE']."' BETWEEN ssm.START_DATE AND ssm.END_DATE OR ssm.END_DATE IS NULL AND '".$addition['DATE']."'>=ssm.START_DATE)) THEN '<FONT color=green>"._('Active')."</FONT>' ELSE '<FONT color=red>"._('Inactive')."</FONT>' END AS ACTIVE";					 
						$sql .= ", CASE WHEN (ss.SCHOOL_YEAR='".get_school_year()."' AND ('".$addition['DATE']."' BETWEEN ss.START_DATE AND ss.END_DATE OR ss.END_DATE IS NULL AND '".$addition['DATE']."'>=ss.START_DATE)) THEN '<FONT color=green>"._('Active')."</FONT>' ELSE '<FONT color=red>"._('Inactive')."</FONT>' END AS ACTIVE_STUDENT_SCHEDULE";
					} 
					else 
					{
						$sql .= ','.db_case(array("(ssm.SCHOOL_YEAR='".get_school_year()."' AND ('".$addition['DATE']."' BETWEEN ssm.START_DATE AND ssm.END_DATE OR ssm.END_DATE IS NULL AND '".$addition['DATE']."'>=ssm.START_DATE))",'true',"'<FONT color=green>"._('Active')."</FONT>'","'<FONT color=red>"._('Inactive')."</FONT>'")).' AS ACTIVE';
						$sql .= ','.db_case(array("(ss.SCHOOL_YEAR='".get_school_year()."' AND ('".$addition['DATE']."' BETWEEN ss.START_DATE AND ss.END_DATE OR ss.END_DATE IS NULL AND '".$addition['DATE']."'>=ss.START_DATE))",'true',"'<FONT color=green>"._('Active')."</FONT>'","'<FONT color=red>"._('Inactive')."</FONT>'")).' AS ACTIVE_STUDENT_SCHEDULE';
					}	
				}
			}
			$sql .= " FROM STUDENTS s,COURSE_SECTIONS cp,STUDENT_SCHEDULE ss,STUDENT_REGISTRATION ssm ".$addition['FROM']."
					WHERE ssm.SCHOOL_YEAR='".get_school_year()."' AND ssm.SCHOOL_YEAR=cp.SCHOOL_YEAR AND ssm.SCHOOL_YEAR=ss.SCHOOL_YEAR ";
					
			if(!$addition['mp_list'])
			{
				$sql .= " AND ss.SCHOOL_TERM_ID IN (".get_all_school_term('QTR',get_user_school_term()).") ";
			}
			else
				$sql .= " AND ss.SCHOOL_TERM_ID IN (".$addition['mp_list'].") ";
				$sql .=	" AND ssm.STUDENT_ID=s.STUDENT_ID AND s.STUDENT_ID=ss.STUDENT_ID AND ss.STUDENT_ID = ssm.STUDENT_ID
						AND cp.TEACHER_ID='".get_user('USER_ID')."'
						AND cp.COURSE_ID=ss.COURSE_ID AND cp.COURSE_CREDIT_TITLE=ss.COURSE_CREDIT_TITLE AND cp.COURSE_SECTION_ID=ss.COURSE_SECTION_ID ";
			if($_REQUEST['_search_all_periods']!='Y')
				$sql .= " AND cp.COURSE_SECTION_ID='".get_user_coursesection()."' ";

			if($_REQUEST['include_inactive']!='Y')
			{
				$sql .= " AND ssm.SCHOOL_ID='".get_school_id()."'";
				$sql .= " AND ('".$addition['DATE']."' BETWEEN ss.START_DATE AND ss.END_DATE OR (ss.END_DATE IS NULL))";
				$sql .= " AND ('".$addition['DATE']."' BETWEEN ssm.START_DATE AND ssm.END_DATE OR (ssm.END_DATE IS NULL))";
			}
			else
				$sql .= " AND ssm.ID=(".db_limit("SELECT ID FROM STUDENT_REGISTRATION WHERE STUDENT_ID=ssm.STUDENT_ID AND SCHOOL_YEAR=ssm.SCHOOL_YEAR ORDER BY START_DATE DESC ",1).")";

			if($addition['WHERE'])
				$sql .= ' '.$addition['WHERE'];
				
			if(!$addition['SELECT_ONLY'] && $_REQUEST['include_inactive']=='Y')
			{
				$addition['columns_after']['ACTIVE'] = _('School Status');
				$addition['columns_after']['ACTIVE_STUDENT_SCHEDULE'] = _('Course Status');
			}
		break;
		
		case 'parent':
		case 'student':
			$sql = 'SELECT ';
			if($addition['SELECT_ONLY'])
				$sql .= $addition['SELECT_ONLY'];
			else
				$sql .= ' '.$full_name." AS FULL_NAME,s.STUDENT_ID,ssm.SCHOOL_ID,ssm.CLASSLEVEL_ID ".$addition['SELECT'];
			$sql .= " FROM STUDENTS s,STUDENT_REGISTRATION ssm ".$addition['FROM']."
					WHERE ssm.STUDENT_ID=s.STUDENT_ID AND ssm.SCHOOL_YEAR='".get_school_year()."' AND ssm.SCHOOL_ID='".get_school_id()."' AND ('".get_db_date()."' BETWEEN ssm.START_DATE AND ssm.END_DATE OR (ssm.END_DATE IS NULL AND '".get_db_date()."' > ssm.START_DATE)) AND ssm.STUDENT_ID='".get_student_id()."' ";
		break;
	}
//var_dump($_REQUEST['cust_begin']);
	$sql = appendSQL($sql,$addition);
	$sql .= $addition['WHERE'].' ';
	$sql .= CustomFields('where');
	if($addition['GROUP'])
		$sql .= ' GROUP BY '.$addition['GROUP'];

        if($_REQUEST['servicemod']!=='applicants_online'){
	if(!$addition['ORDER_BY'] && !$addition['SELECT_ONLY'])
	{
		if(Preferences('SORT')=='Enrolled')
		{
			if(get_user('PROFILE')=='teacher')
				$sql .= " ORDER BY ss.START_DATE,s.LAST_NAME,s.FIRST_NAME";
			else
				$sql .= " ORDER BY ssm.START_DATE,s.LAST_NAME,s.FIRST_NAME";
		}
		elseif(Preferences('SORT')=='Grade')
			$sql .= " ORDER BY ssm.CLASSLEVEL_ID,s.LAST_NAME,s.FIRST_NAME";
		else
			$sql .= " ORDER BY s.LAST_NAME,s.FIRST_NAME";
		$sql .= $addition['ORDER'];
	}
	elseif($addition['ORDER_BY'])
		$sql .= ' ORDER BY '.$addition['ORDER_BY'];
        }
	
	if($addition['DEBUG_OutPut']===true)
		echo '<!--'.$sql.'-->';
//echo $sql;
//echo '<br><hr>';
//die();
//var_dump($addition['group']);
	$array = db_request(db_execute($sql),$functions,$addition['group']);
	
	if($addition['GridOutputCount']!==false && ($addition['GridOutputCount']===true || $_REQUEST['servicemod']=='mod_students/Search.php' || $_REQUEST['servicemod']=='mod_apply/Applicants.php' || $_REQUEST['servicemod']=='mod_students/Students.php' || $_REQUEST['servicemod']=='mod_curriculum/Curriculum.php' || $_REQUEST['servicemod']=='mod_curriculum/ScdStudReqProperties.php' || $_REQUEST['servicemod']=='mod_grades/GrdPrintStudGrades.php' || $_REQUEST['servicemod']=='mod_attendance/AttdStuSumAttendance.php' || $_REQUEST['servicemod']=='mod_activity/ActStudMain.php' || $_REQUEST['servicemod']=='mod_students/Students.php?include=comments' || $_REQUEST['servicemod']=='mod_billing/BilStudFeesList.php' || $_REQUEST['servicemod']=='mod_billing/BilStudPaysList.php'))
	{
		if($_REQUEST['GridSortingBy'])
		{
			$result_count = count($array);
			foreach($array as $sort)
			{	
				if(substr($sort[$_REQUEST['GridSortingBy']],0,4)!='<!--')
					$sort_array[] = $sort[$_REQUEST['GridSortingBy']];
				else
					$sort_array[] = substr($sort[$_REQUEST['GridSortingBy']],4,strpos($sort[$_REQUEST['GridSortingBy']],'-->')-5);
			}
			if($_REQUEST['GridOrder']==-1)
				$dir = SORT_DESC;
			else
				$dir = SORT_ASC;
			
			if($result_count>1)	
			{
				if(is_int($sort_array[1]) || is_double($sort_array[1]))	
					array_multisort($sort_array,$dir,SORT_NUMERIC,$array);
				else
					array_multisort($sort_array,$dir,$array);
				for($i=$result_count-1;$i>=0;$i--)
					$array[$i+1] = $array[$i];
				unset($array[0]);
			}
			$_SYSTEM['GridSorted'] = true;
		}
                
		$i = 0;
		$_SESSION['student_list'] = array();
		foreach($array as $index=>$student)
		{
			$i++;
			$_SESSION['student_list'][] = array('student_id'=>$student['STUDENT_ID'],'school_id'=>$student['SCHOOL_ID'],'profile'=>$student['PROFILE']);
			$array[$index]['index'] = $i;
		}
	}
	return $array;
}

function makeContactInfo($student_id,$column)
{	global $THIS_array,$_SYSTEM;

	$contacts_array = $_SYSTEM['makeContactInfo'];
	// student has only an address with no info
	if(is_countable($contacts_array[$THIS_array['STUDENT_ID']])==1 && $contacts_array[$THIS_array['STUDENT_ID']][key($contacts_array[$THIS_array['STUDENT_ID']])][1]['COALESCE']=='N' && $contacts_array[$THIS_array['STUDENT_ID']][key($contacts_array[$THIS_array['STUDENT_ID']])][1]['PHONE']=='' && $contacts_array[$THIS_array['STUDENT_ID']][key($contacts_array[$THIS_array['STUDENT_ID']])][1]['ADDRESS']=='' && $contacts_array[$THIS_array['STUDENT_ID']][key($contacts_array[$THIS_array['STUDENT_ID']])][1]['CFIELD_50012']=='')
		$tipmessage = _('This student has no contact information.');
	elseif(is_countable($contacts_array[$THIS_array['STUDENT_ID']]))
	{
		$email = $contacts_array[$THIS_array['STUDENT_ID']][key($contacts_array[$THIS_array['STUDENT_ID']])];
		$email = $email[key($email)][1]['CFIELD_50012'];
		
		foreach($contacts_array[$THIS_array['STUDENT_ID']] as $address_id=>$people)
		{
			if(!$_REQUEST['CREATEPDF'])
				$tipmessage .= '<BR><small>'.$people[key($people)][1]['ADDRESS'].' '.$people[key($people)][1]['CITY'].', '.$people[key($people)][1]['STATE'].' '.$people[key($people)][1]['ZIPCODE'].'</small><BR>';
			foreach($people as $person_id=>$person)
			{
				if($_REQUEST['CREATEPDF'])
				{
					if($person[1]['FIRST_NAME'] || $person[1]['LAST_NAME'])
						$tipmessage .= '('.$person[1]['STUDENT_RELATION'].': '.$person[1]['FIRST_NAME'].' '.$person[1]['LAST_NAME'].') ';
					if($person[1]['PHONE'])
						$tipmessage .= _('Home Phone').': '.$person[1]['PHONE'].'; ';
					if($person[1]['EMAIL'])
						$tipmessage .= _('Email').': '.$person[1]['EMAIL'].'; ';
					foreach($person as $info)
					{
						if($info['TITLE'] || $info['VALUE'])
							$tipmessage .= $info['TITLE'].': '.$info['VALUE'].'; ';
					}
				}
				else
				{
					$tipmessage .= '<div style=padding-left:5px;>';
					if($person[1]['FIRST_NAME'] || $person[1]['LAST_NAME'])
						$tipmessage .= '<B>'.$person[1]['STUDENT_RELATION'].': '.$person[1]['FIRST_NAME'].' '.$person[1]['LAST_NAME'].'</B><BR>';
					$tipmessage .= '<TABLE>';
					if($person[1]['PHONE'])
						$tipmessage .= '<TR><TD align='.ALIGN_RIGHT.' class=rolloverGray>'._('Home Phone').'</TD><TD class=rolloverBlack>'.$person[1]['PHONE'].'</TD></TR>';
					if($person[1]['EMAIL'])
						$tipmessage .= '<TR><TD align='.ALIGN_RIGHT.' class=rolloverGray>'._('Email').'</TD><TD class=rolloverBlack>'.$person[1]['EMAIL'].'</TD></TR>';
					foreach($person as $info)
					{
						if($info['TITLE'] || $info['VALUE'])
							$tipmessage .= '<TR><TD align='.ALIGN_RIGHT.' class=rolloverGray>'.str_replace('"','',$info['TITLE']).'</TD><TD class=rolloverBlack>'.str_replace('"','',$info['VALUE']).'</TD></TR>';
					}
					$tipmessage .= '</TABLE></div>';
				}
			}
		}
		$tipmessage = substr($tipmessage,4);
		if($email)
			$tipmessage = '<B>'._('Email').': '.$email.'</B>';

	}
	else
		$tipmessage = _('This student has no contact information.');

//	if($_REQUEST['CREATEPDF'])
		return $tipmessage;
//	else
//		return button('phone','','# onclick=\'this.onmouseout=null;return false;\' onMouseOver=\'stm(["'._('Contact Information').'","'.str_replace("'","&rsquo;",$tipmessage).'"],["white","#'.Preferences('HIGHLIGHT').'","","","",,"black","#e8e8ff","","","",,,,2,"#'.Preferences('HIGHLIGHT').'",2,,,,,"",5,,,]);\' onMouseOut=\'htm()\'',20);
}

function removeDot00($value,$column)
{
	return str_replace('.00','',$value);	
}

function makePhone($phone,$column='')
{	global $THIS_array;
	
	if(strlen($phone)==10)
		$return .= '('.substr($phone,0,3).')'.substr($phone,3,7).'-'.substr($phone,7);
	if(strlen($phone)=='7')
		$return .= substr($phone,0,3).'-'.substr($phone,3);
	else
		$return .= $phone;
		
	return $return;
}

function makeParents($student_id,$column='')
{	global $THIS_array,$view_other_array,$_SYSTEM;

	if($THIS_array['PARENTS']==$student_id)
	{
		if(!$THIS_array['ADDRESS_ID'])
			$THIS_array['ADDRESS_ID'] = 0;

		$THIS_array['PARENTS'] = '';

		if($_SYSTEM['makeParents'])
			$constraint = 'AND (LOWER(sjp.STUDENT_RELATION) LIKE \''.strLower(strtolower($_SYSTEM['makeParents'])).'%\')';
		elseif($view_other_array['ALL_CONTACTS'][1]['VALUE']=='Y')
			$constraint = "AND (sjp.CUSTODY='Y' OR sjp.EMERGENCY='Y')";
		else
			$constraint = "AND sjp.CUSTODY='Y'";

		$people_array = db_request(db_execute("SELECT p.PERSON_ID,p.FIRST_NAME,p.LAST_NAME,sjp.ADDRESS_ID,sjp.CUSTODY,sjp.EMERGENCY FROM STUDENTS_IN_SR sjp,STUDENT_RELATIONS p WHERE sjp.PERSON_ID=p.PERSON_ID AND sjp.STUDENT_ID='$student_id' ".$constraint." ORDER BY p.LAST_NAME,p.FIRST_NAME"));
		if(count($people_array))
		{
			foreach($people_array as $person)
			{
				if($person['ADDRESS_ID']==$THIS_array['ADDRESS_ID'])
				{
					if($person['CUSTODY']=='Y')
						$color = '0000FF';
					elseif($person['EMERGENCY']=='Y')
						$color = 'FFFF00';

					$THIS_array['PARENTS'] .= '<TR><TD>'.button('dot',$color,'',6).'</TD><TD><A HREF=# onclick=\'window.open("modloader.php?servicemod=mod_portal/ContactLinker.php?person_id='.$person['PERSON_ID'].'&useontop=false","","scrollbars=yes,resizable=yes,width=400,height=200");\'>'.$person['FIRST_NAME'].' '.$person['LAST_NAME'].'</TR>';
					if($_REQUEST['CREATEPDF'])
						$THIS_array['PARENTS'] .= ', ';
				}
			}
			if($_REQUEST['CREATEPDF'])
				$THIS_array['PARENTS'] = substr($THIS_array['PARENTS'],0,-2);
		}
	}
	
	if($THIS_array['PARENTS'])
		return '<TABLE border=0 cellpadding=0 cellspacing=0 class=GridOutput_fieldint>'.$THIS_array['PARENTS'].'</TABLE>';
}

function appendSQL($sql='',$addition='')
{	global $_SYSTEM,$StudentPicturesColumn;
//var_dump($_REQUEST);
	if($_REQUEST['stuid'])
	{
		$sql .= " AND ssm.STUDENT_ID = '$_REQUEST[stuid]' ";
		if(!$addition['DisableSearchResDisplay'])
			$_SYSTEM['SearchTerms'] .= '<font color=gray><b>'._('ID number').': </b></font>'.$_REQUEST['stuid'].'<BR>';
	}
	if($_REQUEST['last'])
	{
		$sql .= " AND LOWER(s.LAST_NAME) LIKE '".strLower(strtolower(str_replace("\'","''",$_REQUEST['last'])))."%' ";
		if(!$addition['DisableSearchResDisplay'])
			$_SYSTEM['SearchTerms'] .= '<font color=gray><b>'._('Last Name starts with').': </b></font>'.str_replace("\'","'",$_REQUEST['last']).'<BR>';
	}
	if($_REQUEST['first'])
	{
		$sql .= " AND LOWER(s.FIRST_NAME) LIKE '".strLower(strtolower(str_replace("\'","''",$_REQUEST['first'])))."%' ";
		if(!$addition['DisableSearchResDisplay'])
			$_SYSTEM['SearchTerms'] .= '<font color=gray><b>'._('First Name starts with').': </b></font>'.str_replace("\'","'",$_REQUEST['first']).'<BR>';
	}
	if($_REQUEST['smart_name'])
	{
		$_REQUEST['smart_name'] = trim($_REQUEST['smart_name']);
		if(is_numeric($_REQUEST['smart_name']))
		{
			if($_SYSTEM['user_search']==true)
				$sql .= " AND s.USER_ID='".$_REQUEST['smart_name']."'";
			elseif($StudentPicturesColumn && $StudentPicturesColumn!='STUDENT_ID' && $_SYSTEM['user_search']!=true)
				$sql .= " AND (s.STUDENT_ID='".$_REQUEST['smart_name']."' OR s.".$StudentPicturesColumn."='".$_REQUEST['smart_name']."')";
			else
				$sql .= " AND s.STUDENT_ID='".$_REQUEST['smart_name']."'";
		}
		elseif(substr_count(trim($_REQUEST['smart_name']),' ')==0)
		{
			$smart_name = strLower(strtolower(str_replace("\'","''",$_REQUEST['smart_name'])));
			$sql .= " AND (LOWER(s.LAST_NAME) LIKE '".strLower(strtolower($smart_name))."%' OR LOWER(s.FIRST_NAME) LIKE '".strLower(strtolower($smart_name))."%' ";
			if($StudentPicturesColumn && $StudentPicturesColumn!='STUDENT_ID' && $_SYSTEM['user_search']!=true)
				$sql .= " OR s.".$StudentPicturesColumn."='".$_REQUEST['smart_name']."' ";
			$sql .= ')';
		}
		else
		{
			$smart_names = explode(' ',str_replace("\'","''",$_REQUEST['smart_name']));
			$sql .= ' AND ((';
			for($i=0;$i<count($smart_names)-1;$i++)
			{
				$sql .= "LOWER(s.FIRST_NAME) LIKE '".strLower(strtolower($smart_names[$i]))."%' OR ";
			}
			$sql = substr($sql,0,-3).") AND LOWER(s.LAST_NAME) LIKE '".strLower(strtolower($smart_names[$i]))."%')";
		}
//		echo $_REQUEST['smart_name'];
		if(!$_SESSION['SN'][$_REQUEST['smart_name']])
		if($addition && !$addition['DisableSearchResDisplay'])
		{
			$_SESSION['SN'][$_REQUEST['smart_name']] = true;
			if($_SYSTEM['user_search']==true)
				$_SYSTEM['SearchTerms'] .= '<font color=gray><b>'._('User Name').': </b></font>'.str_replace("\'","'",$_REQUEST['smart_name']).'<BR>';
			else
				$_SYSTEM['SearchTerms'] .= '<font color=gray><b>'._('Student Name').': </b></font>'.str_replace("\'","'",$_REQUEST['smart_name']).'<BR>';
		}
	}
	if($_REQUEST['grade'] && is_array($_REQUEST['grade']))
	{
		foreach($_REQUEST['grade'] as $gradelevel)
		{
			$gradelevels .= ",'".$gradelevel."'";
			$gradelevel_titles .= ', '.get_classlevel($gradelevel);
		}
		$sql .= "AND ssm.CLASSLEVEL_ID IN (".substr($gradelevels,1).")";	

		if(!$addition['DisableSearchResDisplay'])
			$_SYSTEM['SearchTerms'] .= '<font color=gray><b>'._('Class Level').': </b></font>'.substr($gradelevel_titles,2).'<BR>';
	}
	if($_REQUEST['calendar_id'])
	{
		$sql .= "AND ssm.CALENDAR_ID = '$_REQUEST[calendar_id]' ";
		$title_array = db_request(db_execute("SELECT TITLE FROM SCHOOL_CALENDARS WHERE CALENDAR_ID='".$_REQUEST['calendar_id']."'"));
		if(!$addition['DisableSearchResDisplay'])
			$_SYSTEM['SearchTerms'] .= '<font color=gray><b>'._('Calendar').': </b></font>'.$title_array[1]['TITLE'].'<BR>';
	}
	if($_REQUEST['group_id'])
	{
		$sql .= "AND ssm.GROUP_ID = '$_REQUEST[group_id]' ";
		$title_array = db_request(db_execute("SELECT TITLE FROM SCHOOL_GROUPS WHERE ID='".$_REQUEST['group_id']."'"));
		if(!$addition['DisableSearchResDisplay'])
			$_SYSTEM['SearchTerms'] .= '<font color=gray><b>'._('Class Group').': </b></font>'.$title_array[1]['TITLE'].'<BR>';
	}
	
	if($_REQUEST['addr'])
	{
		if(strlen($_REQUEST['addr'])==13 && ereg("[0-9]{5}\.\.\.[0-9]{5}",$_REQUEST['addr'])!==false)
			$sql .= " AND a.ZIPCODE BETWEEN ".substr($_REQUEST['addr'],0,5).' AND '.substr($_REQUEST['addr'],8).' ';
		else
			$sql .= " AND (LOWER(a.ADDRESS) LIKE '%".strLower(strtolower(str_replace("\'","''",$_REQUEST['addr'])))."%' OR LOWER(a.CITY) LIKE '".strLower(strtolower(str_replace("\'","''",$_REQUEST['addr'])))."%' OR LOWER(a.STATE)='".strLower(strtolower(str_replace("\'","''",$_REQUEST['addr'])))."' OR a.ZIPCODE LIKE '".str_replace("\'","''",$_REQUEST['addr'])."%')";
		if(!$addition['DisableSearchResDisplay'])
			$_SYSTEM['SearchTerms'] .= '<font color=gray><b>'._('Address contains').': </b></font>'.str_replace("\'","'",$_REQUEST['addr']).'<BR>';
	}
	return $sql;	
}

function strLower($str)
{
    $arr = array(
        'А'=>'а',
        'Б'=>'б',
        'В'=>'в',
        'Г'=>'г',
        'Д'=>'д',
        'Е'=>'е',
        'Ж'=>'ж',
        'З'=>'з',
        'И'=>'и',
        'Й'=>'й',
        'К'=>'к',
        'Л'=>'л',
        'М'=>'м',
        'Н'=>'н',
        'О'=>'о',
        'П'=>'п',
        'Р'=>'р',
        'С'=>'с',
        'Т'=>'т',
        'У'=>'у',
        'Ф'=>'ф',
        'Х'=>'х',
        'Ц'=>'ц',
        'Ч'=>'ч',
        'Ш'=>'ш',
        'Щ'=>'щ',
        'Ъ'=>'ъ',
        'Ы'=>'ы',
        'Ь'=>'ь',
        'Э'=>'э',
        'Ю'=>'ю',
        'Я'=>'я',
    );
    return strtr($str,$arr);
}

function prepareMultiple($value,$column)
{
	return str_replace('||',', ',substr($value,2,-2));
}
?>