Change Log of Joomsport: Create view 'arrangement' with parameter 'month'

Target

Database Change

Create view 'jos_bl_arrangement'

Type in the following SQL in phpMyAdmin panel for creating view:

SELECT m_id AS mday_id,month(m_date) AS mon,date_format(m_date,'%d/%m/%Y') AS m_date,dayofweek(m_date) AS dayweek,team2_id,team1_id,score1,score2,m_time,m_location,m_remark,t1.t_name AS home,t2.t_name AS away FROM jos_bl_match AS m, jos_bl_teams AS t1, jos_bl_teams AS t2 WHERE t1.id = m.team1_id AND t2.id = m.team2_id;

and here is the view we get:

PHP Change

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

add 'bl_MonthMenu' function definition

function bl_MonthMenu( $rows, $option ){$jsf = JRequest::getVar('function','jSelectArticle','','string');JHTML::_('behavior.tooltip');?><table class="adminlist"><thead><tr><th width="2%" align="left"><?php echo JText::_( 'Num' ); ?></th><th class="title"><?php echo JText::_( 'Name' ); ?></th></tr></thead><tbody><?php$k = 0;if( count( $rows ) ){for ($i=0, $n=count( $rows ); $i < $n; $i++){$row = $rows[$i];JFilterOutput::objectHtmlSafe($row);?><tr class="<?php echo "row$k"; ?>"><td><?php echo  $i+1; ?></td><td><a href="javascript:window.parent.<?php echo $jsf;?>('<?php echo $row->num?>', '<?php echo htmlspecialchars($row->name, ENT_QUOTES, 'UTF-8')?>', 'mon');"><?php echo $row->name; ?></a></td></tr><?php}}?></tbody></table><?php}//end

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

add 'BL_Month_Menu' function 

// added by Vincent 27-Oct-2011function BL_Month_Menu($option){$monChar = "月";$monNum = array(9, 10, 11, 12, 1, 2, 3, 4, 5, 6, 7);$length = count($monNum);$row = array();for ($i=0; $i<$length; $i++){$month = new stdClass;$month->num = $monNum[$i];$month->name = $monNum[$i] . $monChar;$row[] = $month;}joomsport_html::bl_MonthMenu($row, $option);}

add task 'month_menu'

// added by Vincent 27-Oct-2011case 'month_menu':BL_Month_Menu($option);break;

/administrator/components/com_joomsport/elements/month.php

<?phpdefined('_JEXEC') or die( 'Restricted access' );class JElementMonth extends JElement{/** * Element name * * @accessprotected * @varstring */var$_name = 'Month';function fetchElement($name, $value, &$node, $control_name){global $mainframe;$db=& JFactory::getDBO();$doc =& JFactory::getDocument();$template = $mainframe->getTemplate();$fieldName= $control_name.'['.$name.']';$article->title = '';$monChar = "月";if($name == 'mon'){if ($value){$article->title = $value . $monChar;}else{$article->title = JText::_('Select Month');}$task = 'month_menu';}$js = "function jSelectArticle(id, title, object) " ."{document.getElementById(object + '_id').value = id;document.getElementById(object + '_name').value = title;document.getElementById('sbox-window').close();}";$doc->addScriptDeclaration($js);$link = 'index.php?option=com_joomsport&task='.$task.'&tmpl=component&object='.$name;JHTML::_('behavior.modal', 'a.modal');$html = "\n".'<div style="float: left;"><input style="background: #ffffff;" type="text" id="'.$name.'_name" value="'.htmlspecialchars($article->title, ENT_QUOTES, 'UTF-8').'" disabled="disabled" /></div>';$html .= '<div class="button2-left"><div class="blank"><a class="modal" title="'.$article->title.'"  href="'.$link.'" rel="{handler: \'iframe\', size: {x: 650, y: 375}}">'.JText::_('Select').'</a></div></div>'."\n";$html .= "\n".'<input type="hidden" id="'.$name.'_id" name="'.$fieldName.'" value="'.(int)$value.'" />';return $html;}}

create files for arrangement view:

/components/com_joomsport/views/arrangement/tmpl/default.xml

<?xml version="1.0" encoding="utf-8"?><metadata><layout title="默認佈局"><message><![CDATA[賽事編排默認佈局]]></message></layout><state><name>賽事編排默認佈局</name><description>賽事編排默認佈局</description><url addpath="/administrator/components/com_joomsport/elements"><param name="mon" type="month" default="0" label="Select Month" description="月份" /></url><params></params></state></metadata>

/components/com_joomsport/views/arrangement/tmpl/default.php

<?php // no direct accessdefined('_JEXEC') or die('Restricted access'); ?>  <table border="1px">  <thead><tr bgcolor='#FFE168'><th colspan="8" align="center""><strong>賽事編排</strong></th></tr>        <tr>            <th colspan="8" align="center"><?php echo $this->mon ?>月份</th>        </tr></thead>    <tbody><tr><td>日期（日/月/年）</td><td>星期</td><td>時間</td><td>比賽場地</td><td>主場</td><td>作客</td><td>賽果</td><td>備註</td></tr><?phpforeach ( $this->matchs as $match ){echo "<tr>";echo "<td>".$match->m_date."</td>";echo "<td>".$this->weekdayChars[$match->dayweek]."</td>";echo "<td>".$match->m_time."</td>";echo "<td>".$match->m_location."</td>";echo "<td>".$match->home."</td>";echo "<td>".$match->away."</td>";echo "<td>".$match->score1.":".$match->score2."</td>";echo "<td>".$match->m_remark."</td>";echo "</tr>";}?></tbody>  </table>

/components/com_joomsport/views/arrangement/metadata.xml

<?xml version="1.0" encoding="utf-8"?><metadata><view title="賽事編排"><message><![CDATA[賽事編排]]></message></view></metadata>

/components/com_joomsport/views/arrangement/view.html.php

<?php// Check to ensure this file is included in Joomla!defined( '_JEXEC' ) or die( 'Restricted access' );jimport( 'joomla.application.component.view');class bleagueViewarrangement extends JView{function display($tpl = null){$mon = JRequest::getVar( 'mon', 0, '', 'int' );if(!$mon){JError::raiseError( 403, JText::_('Access Forbidden') );return;}$db= & JFactory::getDBO();$query = "SELECT * FROM #__bl_arrangement WHERE mon = ".$mon." ORDER BY m_date";$db->setQuery($query);$matchs = $db->loadObjectList();$weekdayChars = array(1=>"日", "一", "二", "三", "四", "五", "六");$this->assignRef('matchs',$matchs);$this->assignRef('weekdayChars', $weekdayChars);$this->assignRef('mon', $mon);parent::display($tpl);}}

6th-Nov-2011:

Here I miss something at the very beginning, and now I correct it! The query should take the season into account, it should select the matchDays' id from the published seasons, and then use the match-day id to filter the records from view 'jos_bl_arrangement'. 

Get seasons:

SELECT MAX(s_id) AS seasonid FROM jos_bl_seasons WHERE published = 1 GROUP BY t_id

Get match days:

SELECT md.id AS md_idFROM jos_bl_matchday AS md, jos_bl_seasons AS sWHERE s.s_id = md.s_idAND s.s_id IN('1')

This query could get the id of match-days.

SELECT *FROM jos_bl_arrangementWHERE mon =9AND mday_idIN ( 1 )ORDER BY m_date

So the php file:

/components/com_joomsport/views/arrangement/view.html.php

<?php// Check to ensure this file is included in Joomla!defined( '_JEXEC' ) or die( 'Restricted access' );jimport( 'joomla.application.component.view');class bleagueViewarrangement extends JView{/* * 賽事編排 */function display($tpl = null){$mon = JRequest::getVar( 'mon', 0, '', 'int' );if(!$mon){JError::raiseError( 403, JText::_('Access Forbidden') );return;}if( ($mon > 12) || (8 == $mon) ){JError::raiseError( 403, JText::_('Access Forbidden') );return;}/* * get the published season id */$db= & JFactory::getDBO();$query = "SELECT MAX(s_id) AS seasonid FROM " ."#__bl_seasons WHERE published = 1 GROUP BY t_id";$db->setQuery($query);$season_ids = $db->loadResultArray();if(empty($season_ids)){$cosm_err = "當下賽季不存在，請聯繫網站管理員。";$this->assignRef('cosm_err', $cosm_err);parent::display($tpl);return;}$seaonid_str = implode("','", $season_ids);/* * get the match-day id */$query = "SELECT md.id AS md_id FROM #__bl_matchday AS md, " ."#__bl_seasons AS s WHERE s.s_id = md.s_id AND s.s_id IN('".$seaonid_str."')";$db->setQuery($query);$md_ids = $db->loadResultArray();/* * $md_ids is an array id ids, implode it to a string */$mdid_str = implode("','", $md_ids);/* * select the matchs within the published season s */$query = "SELECT * FROM #__bl_arrangement WHERE mon = ". $mon. " AND mday_id IN ('". $mdid_str."') ORDER BY m_date";dump($query);$db->setQuery($query);$matchs = $db->loadObjectList();if(empty($matchs)){$cosm_err = "該月份之內目前尚無比賽，請聯繫網站管理員。";$this->assignRef('cosm_err', $cosm_err);parent::display($tpl);return;}$weekdayChars = array(1=>"日", "一", "二", "三", "四", "五", "六");$this->assignRef('matchs',$matchs);$this->assignRef('weekdayChars', $weekdayChars);$this->assignRef('mon', $mon);parent::display($tpl);}}