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();
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>