kenken999/php
0
1<?php2$group_sort_y = array();3class CrossTableWebReport4{5 var $tableName;6 var $shortTableName;7 var $col_summary = array();8 var $group_header = array();9 var $rowinfo = array();10 var $total_summary;11 var $xml_array;12 var $is_value_empty;13 var $pageType;14 15 /*16 * @type Array17 * The list of field aliases from webreports18 */19 var $arrDBFieldsList = array();20 21 /*22 * @type Boolean23 * Webreports - true if the report is database table based.24 */25 var $wrdb = false;26 27 var $index_field_x;28 var $index_field_y;29 30 /**31 * @type String32 * The possible values are: "project", "db", "custom"33 */34 protected $table_type = "project";35 36 /**37 * An Instance of the 'ProjectSettings' class38 * @type Object39 */40 protected $pSet = null;41 42 /**43 * The selected data field settings packed in an array.44 * @type Array45 */46 protected $dataFieldSettings = null;47 48 /**49 * The report current data field name50 */51 protected $dataField = "";52 53 /**54 * The report current aggregate function55 */56 protected $dataGroupFunction = "";57 58 /**59 * An array of table keys60 * @type Array 61 */62 protected $tableKeys = null;63 64 /**65 * @type Connection66 */67 protected $connection;68 69 protected $sessionPrefix;70 71 /**72 * The flag indicating if the report73 * is show on a dashboard74 */75 protected $dashBased = false;76 77 /**78 * The dashboard table name79 */ 80 protected $dashTName = "";81 82 protected $groupFieldsData;83 protected $sortFields;84 85 /**86 * @constructor87 * @param Array rpt_array88 * @param String strSQL89 */90 function __construct($rpt_array, $strSQL)91 {92 global $cman;93 94 $this->xml_array = $rpt_array;95 96 if( $rpt_array["table_type"] )97 $this->table_type = $rpt_array["table_type"];98 99 $this->wrdb = $rpt_array["wrdb"];100 $this->arrDBFieldsList = $rpt_array["arrDBFieldsList"];101 102 $this->pageType = $rpt_array["pageType"];103 104 $this->tableName = $this->xml_array["tables"][0];105 $this->setDbConnection();106 107 $this->shortTableName = GetTableURL($this->tableName);108 if( strlen($this->shortTableName) == 0 )109 $this->shortTableName = $this->tableName;110 111 $this->pSet = new ProjectSettings($this->tableName, PAGE_REPORT);112 113 $this->setSessionPrefix( $rpt_array["sessionPrefix"] );114 $this->fillSessionVariables();115 116 117 $this->dataField = $this->getDataField( $_SESSION[$this->sessionPrefix."_field"] );118 if( !strlen($this->dataField) )119 $this->dataField = $_SESSION['webreports']['group_fields'][0]["name"];120 121 $this->initDataFieldSettings();122 $this->initDataGroupFunction($_SESSION[$this->sessionPrefix."_group_func"]);123 124 125 // assign index_field_x, index_field_y properties126 $this->setAxisFieldsIndices();127 128 $fName = $this->getDBFieldName($this->CrossGoodFieldName( $this->dataField ));129 130 if( $fName != " " )131 $ftype = $this->getFieldType($fName);132 133 $group_y = array();134 $group_x = array();135 $sort_y = array();136 137 $arrdata = array();138 $arravgsum = array();139 $arravgcount = array();140 141 $avgsumx = array();142 $avgcountx = array();143 144 $this->groupFieldsData = $rpt_array["group_fields"];145 $this->sortFields = $rpt_array["sort_fields"];146 147 if( is_wr_project() ) {148 $dataSource = getDataSource( $this->tableName );149 $qResult = $dataSource->getTotals( $this->getProjectWRCommand() ); 150 } else {151 $crtableSQL = $this->getstrSQL( $strSQL );152 $qResult = $this->connection->query( $crtableSQL );153 }154 155 while( $data = $qResult->fetchNumeric() )156 {157 if( !in_array($data[1], $group_y) )158 {159 $group_y[] = $data[1];160 $sort_y[] = pre8count($sort_y);161 }162 163 if( !in_array($data[2], $group_x) )164 {165 $group_x[] = $data[2];166 $this->col_summary["data"][ pre8count($group_x) - 1 ]["col_summary"] = " ";167 $this->col_summary["data"][ pre8count($group_x) - 1 ]["id_col_summary"] = "total_x_".( pre8count($group_x) - 1);168 }169 170 $key_y = array_search( $data[1], $group_y );171 172 $key_x = array_search( $data[2], $group_x );173 174 $avgsumx[ $key_x ] = 0;175 $avgcountx[ $key_x ] = 0;176 177 if( !$this->is_value_empty )178 {179 $arrdata[ $key_y ][ $key_x ] = $data[0];180 $arravgsum[ $key_y ][ $key_x ] = $data[3];181 $arravgcount[ $key_y ][ $key_x ] = $data[4];182 }183 else184 $arrdata[ $key_y ][ $key_x ] = " ";185 }186 187 // sort y groups188 global $group_sort_y;189 $group_sort_y = $group_y;190 SortForCrosstable($sort_y);191 192 193 foreach($sort_y as $key_y)194 {195 $value_y = $group_y[ $key_y ];196 $this->rowinfo[ $key_y ]["row_summary"] = " ";197 $this->rowinfo[ $key_y ]["group_y"] = $this->getAxisDisplayValue($this->index_field_y, $value_y);198 199 foreach($group_x as $key_x => $value_x)200 {201 if( array_key_exists($key_y, $arrdata) )202 {203 $rowValue = " ";204 205 if( array_key_exists($key_x, $arrdata[ $key_y ]) && !$this->is_value_empty && !is_null($arrdata[ $key_y ][ $key_x ]) )206 {207 $rowValue = $arrdata[ $key_y ][ $key_x ];208 if( $this->dataGroupFunction == "avg" && !IsTimeType($ftype))209 $rowValue = round($rowValue, 2);210 }211 212 $this->rowinfo[ $key_y ]["row_record"]["data"][ $key_x ]["row_value"] = $rowValue; 213 $this->rowinfo[ $key_y ]["row_record"]["data"][ $key_x ]["id_data"] = $key_y."_".$key_x;214 }215 }216 $this->rowinfo[ $key_y ]["id_row_summary"] = "total_y_".$key_y;217 }218 219 foreach($group_x as $key_x => $value_x)220 {221 if($value_x != "")222 $this->group_header["data"][ $key_x ]["gr_value"] = $this->getAxisDisplayValue($this->index_field_x, $value_x);223 else224 $this->group_header["data"][ $key_x ]["gr_value"]=" ";225 }226 227 $sum_x = $this->xml_array["group_fields"][ pre8count($this->xml_array["group_fields"]) - 1 ]["sum_x"];228 $sum_y = $this->xml_array["group_fields"][ pre8count($this->xml_array["group_fields"]) - 1 ]["sum_y"];229 $sum_total = $this->xml_array["group_fields"][ pre8count($this->xml_array["group_fields"]) - 1 ]["sum_total"];230 231 $this->total_summary = " ";232 foreach($this->rowinfo as $key_y => $obj_y)233 {234 $obj_x = $obj_y["row_record"]["data"];235 foreach($obj_x as $key_x => $value)236 {237 if($value["row_value"] !== " ")238 {239 switch($this->dataGroupFunction)240 {241 case "sum":242 if(!is_null($value["row_value"]))243 {244 $this->rowinfo[$key_y]["row_summary"] += $value["row_value"];245 $this->col_summary["data"][$key_x]["col_summary"] += $value["row_value"];246 $this->total_summary += $value["row_value"];247 }248 break;249 case "min":250 if(($this->rowinfo[$key_y]["row_summary"] === " " || $value["row_value"]<$this->rowinfo[$key_y]["row_summary"]) && !is_null($value["row_value"]))251 $this->rowinfo[$key_y]["row_summary"] = $value["row_value"];252 if(($this->col_summary["data"][$key_x]["col_summary"] === " " || $this->col_summary["data"][$key_x]["col_summary"]>$value["row_value"]) && !is_null($value["row_value"]))253 $this->col_summary["data"][$key_x]["col_summary"] = $value["row_value"];254 if(($this->total_summary === " " || $this->total_summary>$value["row_value"]) && !is_null($value["row_value"]))255 $this->total_summary = $value["row_value"];256 257 break;258 case "max":259 if($this->rowinfo[$key_y]["row_summary"] === " " || $value["row_value"]>$this->rowinfo[$key_y]["row_summary"])260 $this->rowinfo[$key_y]["row_summary"] = $value["row_value"];261 if($this->col_summary["data"][$key_x]["col_summary"] === " " || $this->col_summary["data"][$key_x]["col_summary"]<$value["row_value"]) 262 $this->col_summary["data"][$key_x]["col_summary"] = $value["row_value"];263 if($this->total_summary === " " || $this->total_summary<$value["row_value"])264 $this->total_summary = $value["row_value"];265 break;266 case "avg":267 $this->rowinfo[$key_y]["avgsumy"] += $arravgsum[$key_y][$key_x];268 $this->rowinfo[$key_y]["avgcounty"] += $arravgcount[$key_y][$key_x];269 $this->rowinfo[$key_y]["row_record"]["data"][$key_x]["avgsumx"] += $arravgsum[$key_y][$key_x];270 $this->rowinfo[$key_y]["row_record"]["data"][$key_x]["avgcountx"] += $arravgcount[$key_y][$key_x];271 break;272 }273 if($sum_x == true && !$this->is_value_empty && !is_null($this->col_summary["data"][$key_x]["col_summary"]))274 {275 if(is_numeric($this->col_summary["data"][$key_x]["col_summary"]))276 $this->col_summary["data"][$key_x]["col_summary"] = round($this->col_summary["data"][$key_x]["col_summary"],2);277 }278 else279 $this->col_summary["data"][$key_x]["col_summary"] = " ";280 }281 }282 if($sum_y == true && !$this->is_value_empty && !is_null($this->rowinfo[$key_y]["row_summary"]))283 {284 if(is_numeric($this->rowinfo[$key_y]["row_summary"]))285 $this->rowinfo[$key_y]["row_summary"] = round($this->rowinfo[$key_y]["row_summary"],2);286 }287 else288 $this->rowinfo[$key_y]["row_summary"] = " ";289 }290 291 if($this->dataGroupFunction == "avg")292 {293 $total_sum = 0;294 $total_count = 0;295 296 foreach($this->rowinfo as $key_y => $valuey)297 {298 if($valuey["avgcounty"])299 {300 $this->rowinfo[$key_y]["row_summary"] = round($valuey["avgsumy"]/$valuey["avgcounty"],2);301 $total_sum += $valuey["avgsumy"];302 $total_count += $valuey["avgcounty"];303 }304 foreach($valuey["row_record"]["data"] as $key_x => $valuex)305 {306 if($valuex["avgcountx"])307 {308 $avgsumx[$key_x] += $valuex["avgsumx"];309 $avgcountx[$key_x] += $valuex["avgcountx"];310 $total_sum += $valuex["avgsumx"];311 $total_count += $valuex["avgcountx"];312 }313 }314 }315 foreach($avgsumx as $key => $value)316 {317 if($avgcountx[$key])318 $this->col_summary["data"][$key]["col_summary"] = round($value/$avgcountx[$key],2);319 }320 if($total_count)321 $this->total_summary = $total_sum/$total_count;322 }323 324 if( $sum_total != true || $this->is_value_empty )325 $this->total_summary = " ";326 elseif( is_numeric($this->total_summary) )327 $this->total_summary = round($this->total_summary,2);328 329 $this->updateRecordsDisplayedFields();330 }331 332 /**333 * @return DsCommand334 */335 protected function getProjectWRCommand() {336 $dc = getProjectWRSubsetDataCommand( $this->tableName, $this->sortFields, $this->pSet );337 338 $ftype = $this->pSet->getFieldType( $this->dataField );339 340 $axis_x = $_SESSION[ $this->sessionPrefix."_gr_x" ];341 $axis_y = $_SESSION[ $this->sessionPrefix."_gr_y" ]; 342 343 $xFName = $this->getGroupFieldByParam( "x", $axis_x );344 $yFName = $this->getGroupFieldByParam( "y", $axis_y, $xFName );345 346 $arr = $this->xml_array["group_fields"];347 for( $i = 0; $i < pre8count($arr) - 1; $i++ ) {348 if( $xFName == $arr[$i]["name"] && $this->index_field_x == $i ) {349 $xType = $arr[$i]["int_type"];350 break;351 }352 }353 354 for( $i = 0; $i < pre8count($arr) - 1; $i++ ) {355 if( $yFName == $arr[$i]["name"] && $this->index_field_y == $i ) {356 $yType = $arr[$i]["int_type"];357 break;358 }359 } 360 361 $xIntervalType = $this->getIntervalTypeByParam( "x", $xFName, $xType );362 $yIntervalType = $this->getIntervalTypeByParam( "y", $yFName, $yType ); 363 364 $dc->totals[] = array(365 "field" => $this->dataField,366 "total" => $this->dataGroupFunction,367 "timeToSec" => $this->pSet->getViewFormat( $this->dataField ) == FORMAT_TIME || IsTimeType($ftype)368 );369 370 $dc->totals[] = array(371 "field" => $yFName,372 "modifier" => $yIntervalType,373 "direction" => $this->getGroupOrderDirection( $yFName )374 );375 376 $dc->totals[] = array(377 "field" => $xFName,378 "modifier" => $xIntervalType379 );380 381 if( $this->dataGroupFunction == "avg" && !IsDateFieldType($ftype) ) {382 $dc->totals[] = array(383 "field" => $this->dataField,384 "alias" => "avg_sum",385 "total" => "sum"386 );387 388 $dc->totals[] = array(389 "field" => $this->dataField,390 "alias" => "avg_count",391 "total" => "count"392 );393 } else {394 $dc->totals[] = array(395 "alias" => "avg_sum",396 "total" => "count"397 );398 $dc->totals[] = array(399 "alias" => "avg_count",400 "total" => "count"401 );402 }403 404 $BeforeQueryReport = $this->pageType == PAGE_REPORT405 && tableEventExists("BeforeQueryReport", $this->tableName);406 $BeforeQueryReportPrint = $this->pageType == PAGE_RPRINT407 && tableEventExists("BeforeQueryReportPrint", $this->tableName);408 409 if( $BeforeQueryReport || $BeforeQueryReportPrint ) {410 $dataSource = getDataSource( $this->tableName );411 $prep = $dataSource->prepareSQL( $dc );412 $where = $prep["where"];413 414 $eventObj = getEventObject( $this->tableName );415 416 if( $BeforeQueryReport )417 $eventObj->BeforeQueryReport( $where );418 else419 $eventObj->BeforeQueryReportPrint( $where );420 421 if( $where != $prep["where"] )422 $dataSource->overrideWhere( $dc, $where );423 } 424 425 return $dc; 426 }427 428 protected function getGroupOrderDirection( $fName ) {429 $orderIndices =& $this->pSet->getOrderIndexes(); 430 $fieldIdx = $this->pSet->getFieldIndex( $fName );431 foreach( $orderIndices as $o ) {432 if( $o[0] == $fieldIdx ) {433 return $o[1];434 }435 }436 return 'ASC'; 437 }438 439 protected function getGroupFieldByParam( $axis, $paramField, $otherField = "" )440 {441 $firstField = "";442 foreach( $this->groupFieldsData as $fData )443 {444 if( $fData["group_type"] == "all" || $fData["group_type"] == $axis ) {445 if( $fData["name"] == $paramField )446 {447 return $paramField;448 }449 if( $firstField === "" && (!$otherField || $otherField !== $firstField ))450 $firstField = $fData["name"];451 }452 }453 return $firstField;454 } 455 456 /**457 * @param String axis458 * @param String crossName459 * @param String userIntType460 * @return Number461 */462 protected function getIntervalTypeByParam( $axis, $crossName, $userIntType )463 {464 $iType = $this->getRefineIntervalType( $userIntType, $crossName );465 466 $int_type = -1;467 $intTypes = array();468 foreach( $this->groupFieldsData as $fData )469 {470 if( $fData["name"] == $crossName && ( $fData["group_type"] == "all" || $fData["group_type"] == $axis ) )471 {472 if( !strlen( $userIntType ) || $iType == $fData["int_type"] )473 {474 $int_type = $fData["int_type"];475 break;476 }477 478 $intTypes[] = $fData["int_type"];479 }480 }481 482 if( $int_type != -1 )483 return $int_type;484 485 if( pre8count( $intTypes ) > 0 )486 return $intTypes[0];487 488 // something went wrong489 return 0;490 } 491 492 /**493 *494 */495 protected function getRefineIntervalType( $intType, $fName )496 {497 if( $intType === 0 )498 return "normal";499 500 $ftype = $this->pSet->getFieldType( $fName );501 502 if( IsNumberType( $ftype ) )503 return substr( $intType, 1 );504 505 if( IsCharType( $ftype ) )506 return substr( $intType, strlen("first") );507 508 if( IsDateFieldType( $ftype ) )509 {510 switch( $intType )511 {512 case "year":513 return 1;514 case "quarter":515 return 2;516 case "month":517 return 3;518 case "week":519 return 4;520 case "day":521 return 5;522 case "hour":523 return 6;524 case "minute":525 return 7;526 }527 }528 529 return -1;530 } 531 532 /**533 * Update the records and summaries data basing on 'view as' and 'total' settings 534 */535 protected function updateRecordsDisplayedFields()536 { 537 if( !pre8count($this->rowinfo) )538 return;539 540 541 $this->updateWebReportRecordsDisplayedFields();542 }543 544 /**545 * Apply currency formatting to the data field values and totals (for Web reports only)546 */547 protected function updateWebReportRecordsDisplayedFields()548 {549 if( $this->dataFieldSettings['curr'] != true )550 return;551 552 foreach($this->rowinfo as $arrkey => $arrfield)553 {554 foreach($arrfield["row_record"]["data"] as $fieldkey => $fieldvalue)555 {556 if( is_numeric($fieldvalue["row_value"]) )557 $this->rowinfo[$arrkey]["row_record"]["data"][$fieldkey]["row_value"] = str_format_currency($fieldvalue["row_value"]);558 }559 560 if( is_numeric($arrfield["row_summary"]) )561 $this->rowinfo[$arrkey]["row_summary"] = str_format_currency($arrfield["row_summary"]);562 }563 564 if( is_numeric($this->total_summary) )565 $this->total_summary = str_format_currency($this->total_summary);566 567 foreach( $this->col_summary["data"] as $arrkey => $arrvalue )568 {569 if(is_numeric($arrvalue["col_summary"]))570 $this->col_summary["data"][$arrkey]["col_summary"] = str_format_currency($arrvalue["col_summary"]);571 } 572 }573 574 /**575 *576 */577 protected function setSessionPrefix( $sessionPrefix = "")578 {579 if( $sessionPrefix )580 $this->sessionPrefix = $sessionPrefix;581 else582 $this->sessionPrefix = $this->shortTableName;583 }584 585 /**586 * Fill a cross-table report's session variable587 */588 protected function fillSessionVariables()589 {590 if( postvalue("operation") != "" )591 $_SESSION[$this->sessionPrefix."_group_func"] = postvalue("operation");592 593 if( postvalue("field") != "" )594 $_SESSION[$this->sessionPrefix."_field"] = postvalue("field");595 596 if( postvalue("axis_x") != "" )597 $_SESSION[$this->sessionPrefix."_gr_x"] = postvalue("axis_x");598 599 if( postvalue("axis_y") != "" )600 $_SESSION[$this->sessionPrefix."_gr_y"] = postvalue("axis_y");601 602 if( postvalue("rname") != "" )603 $_SESSION[$this->sessionPrefix."_rname"] = postvalue("rname"); 604 }605 606 public function getCrossTableData()607 {608 return $this->rowinfo;609 }610 611 public function getCrossTableHeader()612 {613 return $this->group_header;614 }615 616 public function getCrossTableSummary()617 {618 return $this->col_summary;619 }620 621 public function getTotalSummary()622 {623 return $this->total_summary;624 }625 626 /**627 * Assign index_field_x, index_field_y properties628 */629 protected function setAxisFieldsIndices()630 {631 $gr_x = $_SESSION[$this->sessionPrefix."_gr_x"];632 $gr_y = $_SESSION[$this->sessionPrefix."_gr_y"];633 634 if( $gr_x == "" )635 $this->index_field_x = $this->getFirstGroupField("x");636 else 637 $this->index_field_x = $gr_x;638 639 if( $gr_y == "" )640 $this->index_field_y = $this->getFirstGroupField("y");641 else642 $this->index_field_y = $gr_y; 643 } 644 645 /**646 * Assign 'connection' property 647 */648 649 protected function setDbConnection()650 {651 global $cman;652 if($this->isProjectDB())653 $this->connection = $cman->byTable( $this->tableName ); //#9875654 else655 $this->connection = $cman->getDefault();656 } 657 658 /**659 * Get a report's SQL query string660 * @param String strSQL The report table's SQL query661 * @return String 662 */663 protected function getstrSQL( $strSQL )664 {665 global $strTableName;666 667 $group_x = $this->getIntervalType( $this->index_field_x );668 $group_y = $this->getIntervalType( $this->index_field_y );669 670 $fName = $this->getDBFieldName($this->CrossGoodFieldName( $this->dataField )); 671 672 $select_field = "' ', ";673 $avg_func = "";674 if( $fName != " " )675 { 676 $strViewFormat = $this->pSet->getViewFormat( $this->dataField );677 $ftype = $this->getFieldType($fName);678 $isTime = $strViewFormat == FORMAT_TIME || IsTimeType($ftype);679 680 if ( $isTime )681 {682 $select_field = $this->dataGroupFunction."(".$this->connection->timeToSecWrapper($fName)."), ";683 }684 else685 {686 $select_field = $this->dataGroupFunction."(".$this->connection->addFieldWrappers($fName)."), ";687 }688 689 $this->is_value_empty = false;690 691 if( $this->dataGroupFunction == "avg" && !IsDateFieldType($ftype) )692 {693 $sum_for_avg = !$isTime ? "sum(".$this->connection->addFieldWrappers($fName).")" : "sum(".$this->connection->timeToSecWrapper($fName).")";694 $avg_func = ", " . $sum_for_avg . " as ".$this->connection->addFieldWrappers("avg_sum")695 .", pre8count(".$this->connection->addFieldWrappers($fName).") as ".$this->connection->addFieldWrappers("avg_count");696 }697 else698 $avg_func = ", 1 as ".$this->connection->addFieldWrappers("avg_sum").", 1 as ".$this->connection->addFieldWrappers("avg_count"); 699 }700 701 $whereClause = "";702 703 if( $this->pageType == PAGE_REPORT ) {704 if( tableEventExists("BeforeQueryReport", $strTableName) ) 705 {706 $eventObj = getEventObject($strTableName);707 $eventObj->BeforeQueryReport($whereClause);708 if( $whereClause )709 $whereClause = " where ".$whereClause;710 }711 }712 else {713 if( tableEventExists("BeforeQueryReportPrint", $strTableName) ) 714 {715 $eventObj = getEventObject($strTableName);716 $eventObj->BeforeQueryReportPrint($whereClause);717 if( $whereClause )718 $whereClause = " where ".$whereClause;719 }720 721 } 722 723 $gx0 = $this->getDBFieldName($group_x[0]);724 $gx1 = $this->getDBFieldName($group_x[1]);725 $gy0 = $this->getDBFieldName($group_y[0]);726 $gy1 = $this->getDBFieldName($group_y[1]);727 728 $selectClause = "select ".$select_field.$gy0.", ".$gx0. $avg_func;729 $groupByClause = "group by ".$gx1.", ".$gy1;730 $orderByClause = "order by ".$gx1.",".$gy1;731 732 if( $this->connection->dbType != nDATABASE_Oracle )733 {734 if( $this->connection->dbType == nDATABASE_MSSQLServer )735 {736 $pos = strrpos(strtoupper($strSQL), "ORDER BY");737 if( $pos )738 $strSQL = substr($strSQL, 0, $pos);739 }740 return $selectClause." from (".$strSQL.") as cross_table".$whereClause." ".$groupByClause." ".$orderByClause;741 }742 743 return $selectClause." from (".$strSQL.")".$whereClause." ".$groupByClause." ".$orderByClause;744 }745 746 /**747 * @param Number index748 * @return Array749 */750 protected function getIntervalType($index)751 {752 $field = $this->xml_array["group_fields"][$index]["name"];753 $ftype = $this->getFieldType($field);754 755 $arr = $this->xml_array["group_fields"];756 for($i = 0; $i < pre8count($arr) - 1; $i++)757 {758 if($field == $arr[$i]["name"] && $index == $i)759 {760 $int_type = $arr[$i]["int_type"];761 break;762 }763 }764 765 if( $int_type == 0 ) 766 {767 $wrappedGoodFieldName = $this->connection->addFieldWrappers($this->CrossGoodFieldName($field));768 return array( $wrappedGoodFieldName, $wrappedGoodFieldName );769 }770 771 if( IsNumberType($ftype) )772 return $this->getNumberTypeInterval($field, $int_type);773 774 if( IsCharType( $ftype ) )775 return $this->getCharTypeInterval($field, $int_type);776 777 if( IsDateFieldType( $ftype ) )778 return $this->getDateTypeInterval($field, $int_type);779 }780 781 protected function getDateTypeInterval($field, $int_type)782 {783 $field = $this->connection->addFieldWrappers( $this->CrossGoodFieldName($field) );784 switch( $this->connection->dbType )785 {786 case nDATABASE_MySQL:787 if($int_type == 1) // DATE_INTERVAL_YEAR788 return array("year(".$field.")*10000+0101","YEAR(".$field.")");789 elseif($int_type == 2) // DATE_INTERVAL_QUARTER790 return array("year(".$field.")*10000+QUARTER(".$field.")*100+1","year(".$field."),QUARTER(".$field.")");791 elseif($int_type == 3) // DATE_INTERVAL_MONTH792 return array("year(".$field.")*10000+month(".$field.")*100+1","year(".$field."),month(".$field.")");793 elseif($int_type == 4) // DATE_INTERVAL_WEEK794 return array("year(".$field.")*10000+week(".$field.")*100+01","year(".$field."),WEEK(".$field.")");795 elseif($int_type == 5) // DATE_INTERVAL_DAY796 return array("year(".$field.")*10000+month(".$field.")*100+day(".$field.")","year(".$field."),month(".$field."),day(".$field.")");797 elseif($int_type == 6) // DATE_INTERVAL_HOUR798 return array("year(".$field.")*1000000+month(".$field.")*10000+day(".$field.")*100+HOUR(".$field.")","year(".$field."),month(".$field."),day(".$field."),hour(".$field.")");799 elseif($int_type == 7) // DATE_INTERVAL_MINUTE800 return array("year(".$field.")*1000000+month(".$field.")*1000000+day(".$field.")*10000+HOUR(".$field.")*100+minute(".$field.")","year(".$field."),month(".$field."),day(".$field."),hour(".$field."),minute(".$field.")");801 break;802 803 case nDATABASE_Oracle:804 if($int_type == 1) // DATE_INTERVAL_YEAR805 return array("TO_CHAR(".$field.", 'YYYY')*10000+0101","TO_CHAR(".$field.", 'YYYY')");806 elseif($int_type == 2) // DATE_INTERVAL_QUARTER807 return array("TO_CHAR(".$field.", 'YYYY')*10000+TO_CHAR(".$field.",'Q')*100+1","TO_CHAR(".$field.", 'YYYY'),TO_CHAR(".$field.",'Q')");808 elseif($int_type == 3) // DATE_INTERVAL_MONTH809 return array("TO_CHAR(".$field.", 'YYYY')*10000+TO_CHAR(".$field.".'MM')*100+1","TO_CHAR(".$field.", 'YYYY'),TO_CHAR(".$field.".'MM')");810 elseif($int_type == 4) // DATE_INTERVAL_WEEK811 return array("TO_CHAR(".$field.", 'YYYY')*10000+TO_CHAR(".$field.",'W')*100+01","TO_CHAR(".$field.", 'YYYY'),TO_CHAR(".$field.",'W')");812 elseif($int_type == 5) // DATE_INTERVAL_DAY813 return array("TO_CHAR(".$field.", 'YYYY')*10000+TO_CHAR(".$field.",'MM')*100+TO_CHAR(".$field.",'DD')","TO_CHAR(".$field.", 'YYYY'),TO_CHAR(".$field.",'MM'),TO_CHAR(".$field.",'DD')");814 elseif($int_type == 6) // DATE_INTERVAL_HOUR815 return array("TO_CHAR(".$field.", 'YYYY')*1000000+TO_CHAR(".$field.",'MM')*10000+TO_CHAR(".$field.",'DD')*100+TO_CHAR(".$field.",'HH')","TO_CHAR(".$field.", 'YYYY'),TO_CHAR(".$field.",'MM'),TO_CHAR(".$field.",'DD'),TO_CHAR(".$field.",'HH')");816 elseif($int_type == 7) // DATE_INTERVAL_MINUTE817 return array("TO_CHAR(".$field.", 'YYYY')*1000000+TO_CHAR(".$field.",'MM')*1000000+TO_CHAR(".$field.",'DD')*10000+TO_CHAR(".$field.",'HH')*100+TO_CHAR(".$field.",'MI')","TO_CHAR(".$field.", 'YYYY'),TO_CHAR(".$field.",'MM'),TO_CHAR(".$field.",'DD'),TO_CHAR(".$field.",'HH'),TO_CHAR(".$field.",'MI')");818 break;819 820 case nDATABASE_MSSQLServer:821 if($int_type == 1) // DATE_INTERVAL_YEAR822 return array("datepart(yyyy,".$field.")*10000+0101","datepart(yyyy,".$field.")");823 elseif($int_type == 2) // DATE_INTERVAL_QUARTER824 return array("datepart(yyyy,".$field.")*10000+datepart(qq,".$field.")*100+1","datepart(yyyy,".$field."),datepart(qq,".$field.")");825 elseif($int_type == 3) // DATE_INTERVAL_MONTH826 return array("datepart(yyyy,".$field.")*10000+datepart(mm,".$field.")*100+1","datepart(yyyy,".$field."),datepart(mm,".$field.")");827 elseif($int_type == 4) // DATE_INTERVAL_WEEK828 return array("datepart(yyyy,".$field.")*10000+(datepart(ww,".$field.")-1)*100+01","datepart(yyyy,".$field."),datepart(ww,".$field.")");829 elseif($int_type == 5) // DATE_INTERVAL_DAY830 return array("datepart(yyyy,".$field.")*10000+datepart(mm,".$field.")*100+datepart(dd,".$field.")","datepart(yyyy,".$field."),datepart(mm,".$field."),datepart(dd,".$field.")");831 elseif($int_type == 6) // DATE_INTERVAL_HOUR832 return array("datepart(yyyy,".$field.")*1000000+datepart(mm,".$field.")*10000+datepart(dd,".$field.")*100+datepart(hh,".$field.")","datepart(yyyy,".$field."),datepart(mm,".$field."),datepart(dd,".$field."),datepart(hh,".$field.")");833 elseif($int_type == 7) // DATE_INTERVAL_MINUTE834 return array("datepart(yyyy,".$field.")*1000000+datepart(mm,".$field.")*1000000+datepart(dd,".$field.")*10000+datepart(hh,".$field.")*100+datepart(mi,".$field.")","datepart(yyyy,".$field."),datepart(mm,".$field."),datepart(dd,".$field."),datepart(hh,".$field."),datepart(mi,".$field.")");835 break;836 837 case nDATABASE_Access:838 if($int_type == 1) // DATE_INTERVAL_YEAR839 return array("datepart('yyyy',".$field.")*10000+0101","datepart('yyyy',".$field.")");840 elseif($int_type == 2) // DATE_INTERVAL_QUARTER841 return array("datepart('yyyy',".$field.")*10000+datepart('q',".$field.")*100+1","datepart('yyyy',".$field."),datepart('q',".$field.")");842 elseif($int_type == 3) // DATE_INTERVAL_MONTH843 return array("datepart('yyyy',".$field.")*10000+datepart('m',".$field.")*100+1","datepart('yyyy',".$field."),datepart('m',".$field.")");844 elseif($int_type == 4) // DATE_INTERVAL_WEEK845 return array("datepart('yyyy',".$field.")*10000+(datepart('ww',".$field.")-1)*100+01","datepart('yyyy',".$field."),datepart('ww',".$field.")");846 elseif($int_type == 5) // DATE_INTERVAL_DAY847 return array("datepart('yyyy',".$field.")*10000+datepart('m',".$field.")*100+datepart('d',".$field.")","datepart('yyyy',".$field."),datepart('m',".$field."),datepart('d',".$field.")");848 elseif($int_type == 6) // DATE_INTERVAL_HOUR849 return array("datepart('yyyy',".$field.")*1000000+datepart('m',".$field.")*10000+datepart('d',".$field.")*100+datepart('h',".$field.")","datepart('yyyy',".$field."),datepart('m',".$field."),datepart('d',".$field."),datepart('h',".$field.")");850 elseif($int_type == 7) // DATE_INTERVAL_MINUTE851 return array("datepart('yyyy',".$field.")*1000000+datepart('m',".$field.")*1000000+datepart('d',".$field.")*10000+datepart('h',".$field.")*100+datepart('n',".$field.")","datepart('yyyy',".$field."),datepart('m',".$field."),datepart('d',".$field."),datepart('h',".$field."),datepart('n',".$field.")");852 break;853 854 case nDATABASE_PostgreSQL: 855 if($int_type == 1) // DATE_INTERVAL_YEAR856 return array("date_part('year',".$field.")*10000+0101","date_part('year',".$field.")");857 elseif($int_type == 2) // DATE_INTERVAL_QUARTER858 return array("date_part('year',".$field.")*10000+date_part('quarter',".$field.")*100+1","date_part('year',".$field."),date_part('quarter',".$field.")");859 elseif($int_type == 3) // DATE_INTERVAL_MONTH860 return array("date_part('year',".$field.")*10000+date_part('month',".$field.")*100+1","date_part('year',".$field."),date_part('month',".$field.")");861 elseif($int_type == 4) // DATE_INTERVAL_WEEK862 return array("date_part('year',".$field.")*10000+(date_part('week',".$field.")-1)*100+01","date_part('year',".$field."),date_part('week',".$field.")");863 elseif($int_type == 5) // DATE_INTERVAL_DAY864 return array("date_part('year',".$field.")*10000+date_part('month',".$field.")*100+date_part('days',".$field.")","date_part('year',".$field."),date_part('month',".$field."),date_part('days',".$field.")");865 elseif($int_type == 6) // DATE_INTERVAL_HOUR866 return array("date_part('year',".$field.")*1000000+date_part('month',".$field.")*10000+date_part('days',".$field.")*100+date_part('hour',".$field.")","date_part('year',".$field."),date_part('month',".$field."),date_part('days',".$field."),date_part('hour',".$field.")");867 elseif($int_type == 7) // DATE_INTERVAL_MINUTE868 return array("date_part('year',".$field.")*1000000+date_part('month',".$field.")*1000000+date_part('days',".$field.")*10000+date_part('hour',".$field.")*100+date_part('minute',".$field.")","date_part('year',".$field."),date_part('month',".$field."),date_part('days',".$field."),date_part('hour',".$field."),date_part('minute',".$field.")");869 break;870 871 case nDATABASE_Informix:872 return "substring(".$field." from 1 for ".$int_type.")"; //fix it!873 874 case nDATABASE_SQLite3:875 return array($field, $field);876 877 case nDATABASE_DB2: 878 if($int_type == 1) // DATE_INTERVAL_YEAR879 return array("year(".$field.")*10000+0101","YEAR(".$field.")");880 elseif($int_type == 2) // DATE_INTERVAL_QUARTER881 return array("year(".$field.")*10000+QUARTER(".$field.")*100+1","year(".$field."),QUARTER(".$field.")");882 elseif($int_type == 3) // DATE_INTERVAL_MONTH883 return array("year(".$field.")*10000+month(".$field.")*100+1","year(".$field."),month(".$field.")");884 elseif($int_type == 4) // DATE_INTERVAL_WEEK885 return array("year(".$field.")*10000+week(".$field.")*100+01","year(".$field."),WEEK(".$field.")");886 elseif($int_type == 5) // DATE_INTERVAL_DAY887 return array("year(".$field.")*10000+month(".$field.")*100+day(".$field.")","year(".$field."),month(".$field."),day(".$field.")");888 elseif($int_type == 6) // DATE_INTERVAL_HOUR889 return array("year(".$field.")*1000000+month(".$field.")*10000+day(".$field.")*100+HOUR(".$field.")","year(".$field."),month(".$field."),day(".$field."),hour(".$field.")");890 elseif($int_type == 7) // DATE_INTERVAL_MINUTE891 return array("year(".$field.")*1000000+month(".$field.")*1000000+day(".$field.")*10000+HOUR(".$field.")*100+minute(".$field.")","year(".$field."),month(".$field."),day(".$field."),hour(".$field."),minute(".$field.")");892 break;893 }894 }895 896 protected function getNumberTypeInterval($field, $int_type)897 {898 return array("floor(".$this->connection->addFieldWrappers( $this->CrossGoodFieldName($field) )."/".$int_type.")*".$int_type,899 "floor(".$this->connection->addFieldWrappers( $this->CrossGoodFieldName($field) )."/".$int_type.")*".$int_type);900 }901 902 /**903 * @param String field904 * @param Number int_type905 * @return Array906 */907 protected function getCharTypeInterval($field, $int_type)908 {909 $field = $this->connection->addFieldWrappers( $this->CrossGoodFieldName($field) );910 switch( $this->connection->dbType )911 {912 case nDATABASE_MySQL:913 case nDATABASE_MSSQLServer:914 case nDATABASE_Access:915 return array("left(".$field.",".$int_type.")","left(".$field.",".$int_type.")");916 917 case nDATABASE_PostgreSQL:918 case nDATABASE_Informix:919 return array("substring(".$field." from 1 for ".$int_type.")","substring(".$field." from 1 for ".$int_type.")");920 921 case nDATABASE_Oracle:922 case nDATABASE_SQLite3:923 case nDATABASE_DB2:924 return array("substr(".$field.",1,".$int_type.")","substr(".$field.",1,".$int_type.")");925 }926 }927 928 /**929 * @return Array930 */931 public function getSelectedValue()932 {933 $arr = array();934 $firstarr = array();935 foreach($this->xml_array["totals"] as $key => $value)936 {937 if(pre8count($firstarr) == 0)938 $firstarr[] = $this->FullFieldName($value["name"],$value["table"]);939 if($value["min"] == true || $value["max"] == true || $value["sum"] == true || $value["avg"] == true)940 {941 $arr[] = $this->FullFieldName($value["name"],$value["table"]);942 }943 }944 if(pre8count($arr) == 0)945 $arr = $firstarr;946 return $arr;947 }948 949 /**950 * 951 * @param {String} hostPageLocation (optional)952 * @param {String} hostPageId (optional)953 */954 public function getRadioGroupFunctions( $hostPageLocation = "", $hostPageId = "" )955 {956 $arr = array();957 $arrDisplay = array();958 $res = "";959 if($this->dataFieldSettings["sum"] == true)960 {961 $arrDisplay[] = "Sum";962 $arr[] = "sum";963 }964 if($this->dataFieldSettings["max"] == true)965 {966 $arrDisplay[] = "Max";967 $arr[] = "max";968 }969 if($this->dataFieldSettings["min"] == true)970 {971 $arrDisplay[] = "Min";972 $arr[] = "min";973 }974 if($this->dataFieldSettings["avg"] == true)975 {976 $arrDisplay[] = "Average";977 $arr[] = "avg";978 }979 980 if(!pre8count($arr))981 {982 $arr[] = "sum";983 $arrDisplay[] = "Sum";984 }985 986 $res = ""; 987 $onclick = "onclick='refresh_crosstable(\"".$hostPageLocation."\", \"".$hostPageId."\", \"".$this->dashBased."\", \"".$this->dashTName."\");'";988 for($j = 0; $j < pre8count($arr); $j++)989 {990 $s = "";991 if($res == "" || $this->dataGroupFunction == $arr[$j])992 $s = "checked";993 994 $res.= "<input type=radio value='".$arr[$j]."' name=\"group_func".$hostPageId."\" ".$s." ".$onclick."> "995 .$arrDisplay[$j]." ";996 }997 return $res;998 }999 1000 public function ajax_refresh_crosstable( $hostPageLocation = "", $hostPageId = "" )1001 {1002 $reportData = array(1003 $this->rowinfo,1004 $this->col_summary,1005 $this->total_summary,1006 $this->getTotalsName( $this->dataGroupFunction ),1007 $this->getRadioGroupFunctions( $hostPageLocation, $hostPageId )1008 );1009 1010 echo my_json_encode( $reportData );1011 }1012 1013 public function getGroupFields($axis)1014 {1015 $res = "";1016 $label = $this->xml_array["totals"];1017 $arr = $this->xml_array["group_fields"];1018 for($i = 0; $i < pre8count($arr) - 1; $i++)1019 {1020 $s = "";1021 if($axis == "x" && $arr[$i]["group_type"] == "x" || $axis == "y" && $arr[$i]["group_type"] == "y" || $arr[$i]["group_type"] == "all")1022 {1023 if($axis == "x" && $this->index_field_y != $i || $axis == "y" && $this->index_field_x != $i)1024 {1025 if($this->index_field_x == $i && $axis == "x" || $this->index_field_y == $i && $axis == "y")1026 $s = "selected";1027 $strlabel = "";1028 foreach($label as $val)1029 {1030 if($arr[$i]["name"] == $this->FullFieldName($val["name"],$val["table"]))1031 {1032 $strlabel = $val["label"];1033 break;1034 }1035 }1036 $res.= "<option value='".$i."' ".$s.">".$strlabel."</option>";1037 }1038 }1039 }1040 return $res;1041 }1042 1043 protected function getFirstGroupField($axis)1044 {1045 $arr = $this->xml_array["group_fields"];1046 $arrX = array();1047 $arrY = array();1048 $arrAll = array();1049 1050 for($i = 0; $i < pre8count($arr) - 1; $i++)1051 {1052 if($arr[$i]["group_type"] == "x")1053 $arrX[] = $i;1054 1055 if($arr[$i]["group_type"] == "y")1056 $arrY[] = $i;1057 1058 if($arr[$i]["group_type"] == "all")1059 $arrAll[] = $i;1060 }1061 1062 if(pre8count($arrX) > 0 && $axis == "x")1063 return $arrX[0];1064 1065 if(pre8count($arrY) > 0 && $axis == "y")1066 return $arrY[0];1067 1068 if(pre8count($arrX) == 0 && $axis == "x")1069 return $arrAll[0];1070 1071 if(pre8count($arrY) == 0 && $axis == "y")1072 {1073 if(pre8count($arrX) == 0)1074 return $arrAll[1];1075 else1076 return $arrAll[0];1077 }1078 }1079 1080 /**1081 * Get axes displyed values1082 * @param Number index1083 * @param String value1084 * @return String1085 */1086 protected function getAxisDisplayValue($index, $value)1087 {1088 global $locale_info;1089 1090 if( $value == "" || is_null($value) )1091 return "";1092 1093 $groupFieldsData = $this->xml_array["group_fields"];1094 $field = $groupFieldsData[ $index ]["name"];1095 $int_type = $groupFieldsData[ $index ]["int_type"];1096 1097 1098 $ftype = $this->getFieldType($field); 1099 1100 if ( $int_type == 0 ) 1101 { 1102 // The 'Normal' interval is set1103 if ( $this->table_type != "db" )1104 $fieldIdentifier = $this->xml_array["tables"][0]."_".$field;1105 else1106 $fieldIdentifier = $this->CrossGoodFieldName($field);1107 1108 if( $this->xml_array['totals'][ $fieldIdentifier ]['curr'] == true )1109 return str_format_currency($value);1110 1111 if( IsDateFieldType( $ftype ) )1112 {1113 return format_shortdate( db2time($value) );1114 }1115 1116 return xmlencode($value);1117 }1118 1119 1120 if ( IsNumberType( $ftype ) ) 1121 {1122 $start = $value - ($value % $int_type);1123 $end = $start + $int_type;1124 1125 if( $this->table_type != "db" )1126 $fieldIdentifier = $this->xml_array["tables"][0]."_".$field;1127 else1128 $fieldIdentifier = $this->CrossGoodFieldName($field);1129 1130 if( $this->xml_array['totals'][ $fieldIdentifier ]['curr'] == true )1131 return str_format_currency($start)." - ".str_format_currency($end);1132 1133 return $start." - ".$end;1134 } 1135 1136 if ( IsCharType( $ftype ) ) 1137 {1138 return xmlencode(substr($value,0,$int_type));1139 } 1140 1141 if ( IsDateFieldType( $ftype ) ) 1142 {1143 $dvalue = substr($value, 0, 4).'-'.substr($value, 4, 2).'-'.substr($value, 6, 2);1144 1145 if( strlen($value) == 10 )1146 $dvalue.=" ".substr($value, 8, 2)."00:00";1147 elseif( strlen($value) == 12 )1148 $dvalue.=" ".substr($value, 8, 2).":".substr($value, 10, 2).":00";1149 1150 $tm = db2time($dvalue);1151 if( !pre8count($tm) )1152 return "";1153 1154 switch( $int_type )1155 {1156 case 1: // DATE_INTERVAL_YEAR1157 return $tm[0];1158 case 2: // DATE_INTERVAL_QUARTER1159 return $tm[0]."/Q".$tm[1];1160 case 3: // DATE_INTERVAL_MONTH1161 return @$locale_info[ "LOCALE_SABBREVMONTHNAME".$tm[1] ]." ".$tm[0];1162 case 4: // DATE_INTERVAL_WEEK1163 $dates = $this->getDatesByWeek($tm[1] + 1, $tm[0]);1164 return format_shortdate( db2time($dates[0]) ) . ' - ' . format_shortdate( db2time($dates[1]) ); 1165 case 5: // DATE_INTERVAL_DAY1166 return format_shortdate($tm);1167 case 6: // DATE_INTERVAL_HOUR1168 $tm[4] = 0;1169 $tm[5] = 0;1170 return str_format_datetime($tm);1171 case 7: // DATE_INTERVAL_MINUTE1172 $tm[5] = 0;1173 return str_format_datetime($tm);1174 default:1175 return str_format_datetime($tm);1176 }1177 }1178 1179 return "";1180 }1181 1182 protected function getDatesByWeek($week, $year) 1183 {1184 global $locale_info;1185 $startweekday = 0;1186 if($locale_info["LOCALE_IFIRSTDAYOFWEEK"]>0)1187 $startweekday = 7 - $locale_info["LOCALE_IFIRSTDAYOFWEEK"]; 1188 1189 $L = isleapyear($year) ? 1 : 0;1190 $months = array(31, 28 + $L, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31);1191 $total_days = ($week - 1) * 7; 1192 $i = 0;1193 $sum = 0;1194 while($sum <= $total_days)1195 {1196 $sum += $months[$i++];1197 }1198 $sum -= $months[$i-1];1199 $month = $i;1200 $day = $total_days - $sum;