Change Log for Joomsport: Get Extra Field back for Player & Team

The Reasons:

actually it is for the extra unregulated field:

player

team

The relations in Database:

we can make use of the two tables: 'extra_filds', and 'extra_values'.

To make use of them, we need to get back some lines of code:

Player:

administrator/components/com_joomsport/admin.joomsport.php

add lines to definition of function 'BL_PlayerEdit':

	// added back by Vincent 11-NOV-2011
	$query = "SELECT ef.*,ev.fvalue as fvalue FROM #__bl_extra_filds as ef LEFT JOIN #__bl_extra_values as ev ON ef.id=ev.f_id AND ev.uid=".$row->id." WHERE ef.published=1 AND ef.type='0' ORDER BY ef.ordering";
	$db->setQuery($query);
	$lists['ext_fields'] = $db->loadObjectList();

add lines to definition of function 'BL_PlayerSave':

	// added back by Vincent 11-NOV-2011
	//-------extra fields-----------//
	if(isset($_POST['extraf']) && count($_POST['extraf'])){
		for($p=0;$p<count($_POST['extraf']);$p++){
			$query = "DELETE FROM #__bl_extra_values WHERE f_id = ".$_POST['extra_id'][$p]." AND uid = ".$row->id;
			$db->setQuery($query);
			$db->query();
			$query = "INSERT INTO #__bl_extra_values(f_id,uid,fvalue) VALUES(".$_POST['extra_id'][$p].",".$row->id.",'".$_POST['extraf'][$p]."')";
			$db->setQuery($query);
			$db->query();
		}
	}

add lines to definition of function 'BL_FieldsDel', these lines were not included originally, they were added by me. It's for cleaning the orphan records in 'extra_values' table: 

// added by Vincent 11-Nov-2011
		$db->setQuery("DELETE FROM #__bl_extra_values WHERE f_id IN (".$cids.")");
		$db->query();

/administrator/components/com_joomsport/admin.joomsport.html.php

add lines to definition of function 'bl_editPlayer':

	<!-- extra field -->
	<?php
	for($p=0;$p<count($lists['ext_fields']);$p++){
	?>
	<tr>
		<td width="100">
			<?php echo $lists['ext_fields'][$p]->name;?>
		</td>
		<td>
			<input type="text" maxlength="255" size="60" name="extraf[]" value="<?php echo isset($lists['ext_fields'][$p]->fvalue)?htmlspecialchars($lists['ext_fields'][$p]->fvalue):""?>" />
			<input type="hidden" name="extra_id[]" value="<?php echo $lists['ext_fields'][$p]->id?>" />
		</td>
	</tr>
	<?php
	}
	?>
	<tr>
		<td colspan="2">
		<em><?php echo JText::_( 'BLBE_EMPTYFIELD' ); ?>


		</td>
	</tr>
	<!-- extra field -->

And next up is to modify the views. 

For 'paneltyrecord', add lines to view.html.php:

			$query = "SELECT fvalue FROM #__bl_extra_values WHERE f_id = 2 AND uid =".$rec->player_id;
			$db->setQuery($query);
			$remark = $db->loadResult();

			$rec->remark = $remark;

and add lines to tmpl/default.php:

echo "<td>" . $record['remark'] . "</td>";

Please note that the 'f_id' may be different in different cases. 

Then use query to get the players with extra discipline points deduction:

SELECT * 
FROM jos_bl_extra_values
WHERE f_id =3
AND fvalue !=  ''

 

After merging red card records and yellow card records, we need to do second merge with that and the extra discipline deduction, the final view.html.php file is 

<?php
// Check to ensure this file is included in Joomla!
defined( '_JEXEC' ) or die( 'Restricted access' );
jimport( 'joomla.application.component.view');
/**
 * HTML View class for the Registration component
 *
 * @package		Joomla
 * @subpackage	Registration
 * @since 1.0
 */
class bleagueViewpaneltyrecord extends JView
{
	/*
	 * 罰牌紀錄
	 */
	function display($tpl = null)
	{
		$gr_id =  JRequest::getVar( 'gr_id', 0, '', 'int' );

		if(!$gr_id)
		{
			JError::raiseError( 403, JText::_('Access Forbidden') );
			return;
		}

		$sid = JRequest::getVar( 'sid', 0, '', 'int' );
		if(!$sid)
		{
			JError::raiseError( 403, JText::_('Access Forbidden') );
			return;
		}

		$db	= & JFactory::getDBO();
		$query = "SELECT group_name FROM  #__bl_groups WHERE id = ".$gr_id;
		$db->setQuery($query);
		$group_name = $db->loadResult();

		$this->assignRef('groupName', $group_name);

		/*
		 * 31st Oct 2011
		 * step one: get match-day id s
		 */

		$season_record = array();
		/*
		 * get tourn-season name
		 */
		$query = "SELECT s.s_id as id, CONCAT(t.name,' ',s.s_name) as name " .
				"FROM #__bl_tournament as t, #__bl_seasons as s " .
				"WHERE s.published = '1' AND s.t_id = t.id AND s.s_id = "
				. $sid;
		$db->setQuery($query);
		$st_name = $db->loadAssoc();

		/*
		 * push name to associative array
		 */
		$season_record["seasonName"] = $st_name["name"];

		$query = "SELECT id AS mday_id FROM #__bl_matchday WHERE s_id = ". $sid;
		$db->setQuery($query);
		$mday_ids = $db->loadResultArray();

		$panelty = array();
		$pan_marr = array();

		if(empty($mday_ids))
		{
			$cosm_err = "當下賽季不存在賽程，請聯繫網站管理員。";
			$this->assignRef('cosm_err', $cosm_err);
			parent::display($tpl);
			return;
		}

		$mdid_str = implode("','", $mday_ids);

		$query = "SELECT SUM(me.ecount) AS yellow_card, " .
				"me.player_id, t.t_name, pl.first_name, pl.last_name
				FROM #__bl_match_events AS me,
				#__bl_teams AS t, #__bl_players AS pl, #__bl_grteams AS gt,
				#__bl_match AS m
				WHERE me.e_id = 1 AND me.t_id = t.id AND me.player_id = pl.id
				AND gt.t_id = me.t_id AND gt.g_id = "
				. $gr_id
				. " AND m.id = me.match_id
				AND m.m_id IN('"
				. $mdid_str
				. "')
				GROUP BY me.player_id";
		$db->setQuery($query);
		$yellow_records = $db->loadObjectList();

		$query = "SELECT SUM(me.ecount) AS red_card, " .
				"me.player_id, t.t_name, pl.first_name, pl.last_name
				FROM #__bl_match_events AS me,
				#__bl_teams AS t, #__bl_players AS pl, #__bl_grteams AS gt,
				#__bl_match AS m
				WHERE me.e_id = 2 AND me.t_id = t.id AND me.player_id = pl.id
				AND gt.t_id = me.t_id AND gt.g_id = "
				. $gr_id
				. " AND m.id = me.match_id
				AND m.m_id IN('"
				. $mdid_str
				."')
				GROUP BY me.player_id";
		$db->setQuery($query);
		$red_records = $db->loadObjectList();

		$query = "SELECT ev.fvalue AS disci,p.id AS player_id, p.first_name AS first_name, " .
				"p.last_name AS last_name, t.t_name AS t_name " .
				"FROM #__bl_extra_values AS ev, #__bl_players AS p, " .
				"#__bl_teams AS t WHERE f_id = 3 AND fvalue != '' " .
				"AND ev.uid = p.id AND p.team_id = t.id";
		$db->setQuery($query);
		$extra_deductions = $db->loadObjectList();

		if(  ( !empty($yellow_records) ) && ( !empty($red_records) )  )
		{
			/*
			 * neither table is empty
			 */
			$duplicated_ids = array();
			$red_length = count($red_records);

			foreach($yellow_records as $ykey=>$yvalue)
			{
				$yvalue->red_card = "0";
				for($x=0; $x<$red_length; $x++)
				{
					if( $yvalue->player_id == $red_records[$x]->player_id )
					{
						$yvalue->red_card = $red_records[$x]->red_card;
						$duplicated_ids[] = $x;
						break;
					}
				}
				$panelty[] = $yvalue;
			}

			for($y=0; $y<$red_length; $y++)
			{
				if(in_array($y, $duplicated_ids))
				{
				}
				else
				{
					$red_records[$y]->yellow_card = "0";
					$panelty[] = $red_records[$y];
				}
			}



			// second merge, yellow/red with extra discipline deduction table
			if( !empty($extra_deductions) )
			{
				$duplicated_ids_2 = array();
				$len_discip = count($extra_deductions);

				foreach($panelty as $p_rec)
				{
					$p_rec->disci = "0";
					for($m=0; $m<$len_discip; $m++)
					{
						if($extra_deductions[$m]->uid == $p_rec->player_id)
						{
							$p_rec->disci = $extra_deductions[$m]->disci;
							$duplicated_ids_2[] = $m;
							break;
						}
					}
				}

				for($n=0; $n<$len_discip; $n++)
				{
					if(in_array($n, $duplicated_ids_2))
					{
					}
					else
					{
						$extra_deductions[$n]->red_card = "0";
						$extra_deductions[$n]->yellow_card = "0";
						$panelty[] = $extra_deductions[$n];
					}
				}

			}

		}
		else if(  ( !empty($yellow_records) ) &&  empty($red_records)   )
		{
			//only need to assign yellow records to &this
			foreach($yellow_records as $ykey=>$yvalue)
			{
				$yvalue->red_card = "0";
			}
			$panelty = $yellow_records;

			// merge yellow table with extra discipline deduction table
			if( !empty($extra_deductions) )
			{
				$duplicated_ids_2 = array();
				$len_discip = count($extra_deductions);

				foreach($panelty as $p_rec)
				{
					$p_rec->disci = "0";
					for($m=0; $m<$len_discip; $m++)
					{
						if($extra_deductions[$m]->uid == $p_rec->player_id)
						{
							$p_rec->disci = $extra_deductions[$m]->disci;
							$duplicated_ids_2[] = $m;
							break;
						}
					}
				}

				for($n=0; $n<$len_discip; $n++)
				{
					if(in_array($n, $duplicated_ids_2))
					{
					}
					else
					{
						$extra_deductions[$n]->red_card = "0";
						$extra_deductions[$n]->yellow_card = "0";
						$panelty[] = $extra_deductions[$n];
					}
				}

			}
		}
		else if(  empty($yellow_records) &&  ( !empty($red_records) )   )
		{
			//only need to assign red records to &this
			foreach($red_records as $rkey=>$rvalue)
			{
				$rvalue->yellow_card = "0";
			}
			$panelty = $red_records;

			// merge red table with extra discipline deduction table
			if( !empty($extra_deductions) )
			{
				$duplicated_ids_2 = array();
				$len_discip = count($extra_deductions);

				foreach($panelty as $p_rec)
				{
					$p_rec->disci = "0";
					for($m=0; $m<$len_discip; $m++)
					{
						if($extra_deductions[$m]->uid == $p_rec->player_id)
						{
							$p_rec->disci = $extra_deductions[$m]->disci;
							$duplicated_ids_2[] = $m;
							break;
						}
					}
				}

				for($n=0; $n<$len_discip; $n++)
				{
					if(in_array($n, $duplicated_ids_2))
					{
					}
					else
					{
						$extra_deductions[$n]->red_card = "0";
						$extra_deductions[$n]->yellow_card = "0";
						$panelty[] = $extra_deductions[$n];
					}
				}

			}

		}
		else
		{
			//assign an empty to &this
			if( !empty($extra_deductions) )
			{
				foreach($extra_deductions as $rkey=>$disvalue)
				{
					$disvalue->yellow_card = "0";
					$disvalue->red_card = "0";
				}
				$panelty = $extra_deductions;
			}
		}

		foreach($panelty as $rec)
		{
			$rec->yellow_card = intval($rec->yellow_card);
			$rec->red_card = intval($rec->red_card);
			$rec->disci = intval($rec->disci);
			$rec->overall = 2 * $rec->red_card + $rec->yellow_card + $rec->disci;

			$query = "SELECT fvalue FROM #__bl_extra_values WHERE f_id = 2 AND uid =".$rec->player_id;
			$db->setQuery($query);
			$remark = $db->loadResult();

			$rec->remark = $remark;

			$pan_marr[] = (array)$rec;
		}

		foreach($pan_marr as $xxx)
		{
			$overall[] = $xxx['overall'];
			$red[] = $xxx['red_card'];
			$yellow[] = $xxx['yellow_card'];
		}

		array_multisort($overall, SORT_DESC, $red, SORT_DESC, $yellow, SORT_DESC, $pan_marr);

		$season_record["seasonPanelty"] = $pan_marr;

		$this->assignRef('seasonPaneltyReds', $season_record);

		parent::display($tpl);
	}
}

For 'shooterlist', add lines to view.html.php:

			$query = "SELECT fvalue FROM #__bl_extra_values WHERE f_id = 1 AND uid =" . $shooter->player_id;
			$db->setQuery($query);
			$remark = $db->loadResult();

			$shooter->remark = $remark;

and add lines to tmpl/default.php:

echo "<td>" . $record['remark'] . "</td>";

The final file:

<?php
// Check to ensure this file is included in Joomla!
defined( '_JEXEC' ) or die( 'Restricted access' );
jimport( 'joomla.application.component.view');
/**
 * HTML View class for the Registration component
 *
 * @package		Joomla
 * @subpackage	Registration
 * @since 1.0
 */
class bleagueViewshooterlist extends JView
{
	/*
	 * 金靴射手榜
	 */
	function display($tpl = null)
	{
		/*
		 * 10th Nov 2011
		 */

		$gr_id =  JRequest::getVar( 'gr_id', 0, '', 'int' );

		if(!$gr_id)
		{
			JError::raiseError( 403, JText::_('Access Forbidden') );
			return;
		}

		$sid = JRequest::getVar( 'sid', 0, '', 'int' );
		if(!$sid)
		{
			JError::raiseError( 403, JText::_('Access Forbidden') );
			return;
		}

		$db	= & JFactory::getDBO();
		$query = "SELECT group_name FROM  #__bl_groups WHERE id = ".$gr_id;
		$db->setQuery($query);
		$group_name = $db->loadResult();

		$this->assignRef('groupName', $group_name);

		$goal_record = array();
		/*
		 * get tourn-season name
		 */
		$query = "SELECT s.s_id as id, CONCAT(t.name,' ',s.s_name) as name " .
				"FROM #__bl_tournament as t, #__bl_seasons as s " .
				"WHERE s.published = '1' AND s.t_id = t.id AND s.s_id = "
				. $sid;
		$db->setQuery($query);
		$st_name = $db->loadAssoc();

		/*
		 * push name to associative array
		 */
		$goal_record["seasonName"] = $st_name["name"];

		$query = "SELECT id AS mday_id FROM #__bl_matchday WHERE s_id = ". $sid;
		$db->setQuery($query);
		$mday_ids = $db->loadResultArray();

		$panelty = array();
		$pan_marr = array();

		if(empty($mday_ids))
		{
			$cosm_err = "當下賽季不存在賽程，請聯繫網站管理員。";
			$this->assignRef('cosm_err', $cosm_err);
			parent::display($tpl);
			return;
		}

		$mdid_str = implode("','", $mday_ids);

		$query = "SELECT SUM(me.ecount) AS goal, " .
				"me.player_id, t.t_name, pl.first_name, pl.last_name
				FROM #__bl_match_events AS me,
				#__bl_teams AS t, #__bl_players AS pl, #__bl_grteams AS gt,
				#__bl_match AS m
				WHERE me.e_id = 3 AND me.t_id = t.id AND me.player_id = pl.id
				AND gt.t_id = me.t_id AND gt.g_id = "
				. $gr_id
				. " AND m.id = me.match_id
				AND m.m_id IN('"
				. $mdid_str
				. "')
				GROUP BY me.player_id";
		$db->setQuery($query);
		$shooters = $db->loadObjectList();

		// to be sorted later on
		$sorted_goal_rec = array();
		foreach($shooters as $shooter)
		{
			$query = "SELECT MAX( STR_TO_DATE( m_date, '%Y-%m-%d' ) ) "
					."FROM #__bl_match AS m, #__bl_match_events AS me "
					."WHERE m.id = me.match_id AND me.player_id = "
					.$shooter->player_id
					." AND me.e_id = 3 AND m.m_id  IN('"
					. $mdid_str
					. "')";
			$db->setQuery($query);
			$recent_time = $db->loadResult();

			$query = "SELECT SUM(me.ecount) AS recent_goal "
					."FROM #__bl_match_events AS me, #__bl_match AS m "
					."WHERE me.e_id = 3 AND me.player_id = "
					.$shooter->player_id
					." AND m.id = me.match_id AND m.m_date = '"
					.$recent_time
					."'";

			$db->setQuery($query);
			$recent_goals = $db->loadResult();

			$shooter->goal = intval($shooter->goal);
			$shooter->recent = intval($recent_goals);
			$shooter->before = $shooter->goal - $shooter->recent;

			$query = "SELECT fvalue FROM #__bl_extra_values WHERE f_id = 1 AND uid =" . $shooter->player_id;
			$db->setQuery($query);
			$remark = $db->loadResult();

			$shooter->remark = $remark;

			$sorted_goal_rec [] = (array)$shooter;
		}

		/*
		 * sort the array shooters
		 */

		foreach($sorted_goal_rec as $xxx)
		{
			$overall[] = $xxx['goal'];
			$recent[] = $xxx['recent'];
			$before[] = $xxx['before'];
		}

		array_multisort($overall, SORT_DESC, $recent, SORT_DESC, $before, SORT_DESC, $sorted_goal_rec);
		$goal_record["goals_record"] = $sorted_goal_rec;
		$this->assignRef('seasonGoals', $goal_record);

		parent::display($tpl);
	}
}

Team:

/administrator/components/com_joomsport/admin.joomsport.php

get back lines for function 'BL_TeamEdit':

	// added back by Vincent 11-Nov-2011
	$query = "SELECT ef.*,ev.fvalue as fvalue FROM #__bl_extra_filds as ef LEFT JOIN #__bl_extra_values as ev ON ef.id=ev.f_id AND ev.uid=".$row->id." WHERE ef.published=1 AND ef.type='1' ORDER BY ef.ordering";
	$db->setQuery($query);
	$lists['ext_fields'] = $db->loadObjectList();

get back lines for function 'BL_TeamSave':

	// added back by Vincent 11-Nov-2011
	//-------extra fields-----------//
	if(isset($_POST['extraf']) && count($_POST['extraf'])){
		for($p=0;$p<count($_POST['extraf']);$p++){
			$query = "DELETE FROM #__bl_extra_values WHERE f_id = ".$_POST['extra_id'][$p]." AND uid = ".$row->id;
			$db->setQuery($query);
			$db->query();
			$query = "INSERT INTO #__bl_extra_values(f_id,uid,fvalue) VALUES(".$_POST['extra_id'][$p].",".$row->id.",'".$_POST['extraf'][$p]."')";
			$db->setQuery($query);
			$db->query();
		}
	}

/administrator/components/com_joomsport/admin.joomsport.html.php

gat back lines for function 'bl_editTeam':

			<!-- extra field -->
			<!-- added back by Vincent-->
			<?php
			for($p=0;$p<count($lists['ext_fields']);$p++){
			?>
			<tr>
				<td width="100">
					<?php echo $lists['ext_fields'][$p]->name;?>
				</td>
				<td>
					<input type="text" maxlength="255" size="60" name="extraf[]" value="<?php echo isset($lists['ext_fields'][$p]->fvalue)?htmlspecialchars($lists['ext_fields'][$p]->fvalue):""?>" />
					<input type="hidden" name="extra_id[]" value="<?php echo $lists['ext_fields'][$p]->id?>" />
				</td>
			</tr>
			<?php
			}
			?>
			<tr>
				<td colspan="2">
				<em><?php echo JText::_( 'BLBE_EMPTYFIELD' ); ?>

				</td>
			</tr>