* @author Romain Quetiez * @author Denis Flaven * @license http://www.opensource.org/licenses/gpl-3.0.html LGPL */ require_once(APPROOT.'/application/webpage.class.inc.php'); require_once(APPROOT.'/application/utils.inc.php'); require_once(APPROOT.'/pages/php-ofc-library/open-flash-chart.php'); /** * Helper class to design optimized dashboards, based on an SQL query * */ class SqlBlock { protected $m_sQuery; protected $m_aColumns; protected $m_sTitle; protected $m_sType; public function __construct($sQuery, $aColumns, $sTitle, $sType) { $this->m_sQuery = $sQuery; $this->m_aColumns = $aColumns; $this->m_sTitle = $sTitle; $this->m_sType = $sType; } /** * Constructs a SqlBlock object from an XML template /* * * * SELECT date_format(start_date, '%d') AS Date, count(*) AS Count FROM ticket WHERE DATE_SUB(NOW(), INTERVAL 15 DAY) < start_date AND finalclass = 'UserIssue' GROUP BY date_format(start_date, '%d') * table * UserRequest:Overview-Title * * Date * * * * * Count * * SELECT UserIssue WHERE date_format(start_date, '%d') = :Date * * * * Tags * - sql: a (My)SQL query. Do not forget to use html entities (e.g. < for <) * - type: table (default), bars or pie. If bars or pie is selected only the two first columns are taken into account. * - title: optional title, typed in clear or given as a dictionnary entry * - column: specification of a column (not displayed if omitted) * - column / name: name of the column in the SQL query (use aliases) * - column / label: label, typed in clear or given as a dictionnary entry * - column / drilldown: NOT IMPLEMENTED YET - OQL with parameters corresponding to column names (in the query) * * @param $sTemplate string The XML template * @return DisplayBlock The DisplayBlock object, or null if the template is invalid */ public static function FromTemplate($sTemplate) { $oXml = simplexml_load_string(''.$sTemplate.''); if (false) { // Debug echo "
\n";
			print_r($oXml);
			echo "
\n"; } if (isset($oXml->title)) { $sTitle = (string)$oXml->title; } if (isset($oXml->type)) { $sType = (string)$oXml->type; } else { $sType = 'table'; } if (!isset($oXml->sql)) { throw new Exception('Missing tag "sql" in sqlblock'); } $sQuery = (string)$oXml->sql; $aColumns = array(); if (isset($oXml->column)) { foreach ($oXml->column AS $oColumnData) { if (!isset($oColumnData->name)) { throw new Exception("Missing tag 'name' in sqlblock/column"); } $sName = (string) $oColumnData->name; if (strlen($sName) == 0) { throw new Exception("Empty tag 'name' in sqlblock/column"); } $aColumns[$sName] = array(); if (isset($oColumnData->label)) { $sLabel = (string)$oColumnData->label; if (strlen($sLabel) > 0) { $aColumns[$sName]['label'] = Dict::S($sLabel); } } if (isset($oColumnData->drilldown)) { $sDrillDown = (string)$oColumnData->drilldown; if (strlen($sDrillDown) > 0) { $aColumns[$sName]['drilldown'] = $sDrillDown; } } } } return new SqlBlock($sQuery, $aColumns, $sTitle, $sType); } public function RenderContent(WebPage $oPage, $aExtraParams = array()) { if (empty($aExtraParams['currentId'])) { $sId = 'sqlblock_'.$oPage->GetUniqueId(); // Works only if the page is not an Ajax one ! } else { $sId = $aExtraParams['currentId']; } // $oPage->add($this->GetRenderContent($oPage, $aExtraParams, $sId)); $res = CMDBSource::Query($this->m_sQuery); $aQueryCols = CMDBSource::GetColumns($res); // Prepare column definitions (check + give default values) // foreach($this->m_aColumns as $sName => $aColumnData) { if (!in_array($sName, $aQueryCols)) { throw new Exception("Unknown column name '$sName' in sqlblock column"); } if (!isset($aColumnData['label'])) { $this->m_aColumns[$sName]['label'] = $sName; } if (isset($aColumnData['drilldown'])) { } } if (strlen($this->m_sTitle) > 0) { $oPage->add("

".Dict::S($this->m_sTitle)."

\n"); } switch ($this->m_sType) { case 'bars': case 'pie': $aColNames = array_keys($this->m_aColumns); $sXColName = $aColNames[0]; $sYColName = $aColNames[1]; $aData = array(); while($aRow = CMDBSource::FetchArray($res)) { $aData[$aRow[$sXColName]] = $aRow[$sYColName]; } $this->RenderChart($oPage, $sId, $aData); break; default: case 'table': $aDisplayConfig = array(); foreach($this->m_aColumns as $sName => $aColumnData) { $aDisplayConfig[$sName] = array('label' => $aColumnData['label'], 'description' => ''); } $aDisplayData = array(); while($aRow = CMDBSource::FetchArray($res)) { $aDisplayRow = array(); foreach($this->m_aColumns as $sName => $aColumnData) { $aDisplayRow[$sName] = $aRow[$sName]; } $aDisplayData[] = $aRow; } $oPage->table($aDisplayConfig, $aDisplayData); break; } } public function GetRenderContent(WebPage $oPage, $aExtraParams = array(), $sId) { $sHtml = ''; return $sHtml; } protected function RenderChart($oPage, $sId, $aValues) { // 1- Compute Open Flash Chart data // $aValueKeys = array(); foreach($aValues as $key => $value) { // Make sure that values are integers (so that max() will work....) // and build an array of STRING with the keys (numeric keys are transformed into string by PHP :-( $aValues[$key] = (int)$value; $aValueKeys[] = (string)$key; } $oChart = new open_flash_chart(); if ($this->m_sType == 'bars') { $oChartElement = new bar_glass(); $maxValue = max($aValues); $oYAxis = new y_axis(); $aMagicValues = array(1,2,5,10); $iMultiplier = 1; $index = 0; $iTop = $aMagicValues[$index % count($aMagicValues)]*$iMultiplier; while($maxValue > $iTop) { $index++; $iTop = $aMagicValues[$index % count($aMagicValues)]*$iMultiplier; if (($index % count($aMagicValues)) == 0) { $iMultiplier = $iMultiplier * 10; } } //echo "oYAxis->set_range(0, $iTop, $iMultiplier);\n"; $oYAxis->set_range(0, $iTop, $iMultiplier); $oChart->set_y_axis( $oYAxis ); $oChartElement->set_values(array_values($aValues)); $oXAxis = new x_axis(); $oXLabels = new x_axis_labels(); // set them vertical $oXLabels->set_vertical(); // set the label text $oXLabels->set_labels($aValueKeys); // Add the X Axis Labels to the X Axis $oXAxis->set_labels( $oXLabels ); $oChart->set_x_axis( $oXAxis ); } else { $oChartElement = new pie(); $oChartElement->set_start_angle( 35 ); $oChartElement->set_animate( true ); $oChartElement->set_tooltip( '#label# - #val# (#percent#)' ); $oChartElement->set_colours( array('#FF8A00', '#909980', '#2C2B33', '#CCC08D', '#596664') ); $aData = array(); foreach($aValues as $sValue => $iValue) { $aData[] = new pie_value($iValue, $sValue); //@@ BUG: not passed via ajax !!! } $oChartElement->set_values( $aData ); $oChart->x_axis = null; } // Title given in HTML //$oTitle = new title($this->m_sTitle); //$oChart->set_title($oTitle); $oChart->set_bg_colour('#FFFFFF'); $oChart->add_element( $oChartElement ); $sData = $oChart->toPrettyString(); $sData = json_encode($sData); // 2- Declare the Javascript function that will render the chart data\ // $oPage->add_script( <<< EOF function ofc_get_data_{$sId}() { return $sData; } EOF ); // 3- Insert the Open Flash chart // $oPage->add("
If blah blah cd romain
\n"); $oPage->add_ready_script( <<