1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
|
<?php
require_once("PHPReportInputObject.php");
class PHPReportInputCrosstab extends PHPReportInputObject {
function run(){
// default values for aggregation functions
$this->_default["SUM"] = 0;
$this->_default["COUNT"] = "null";
$this->_default["MIN"] = "null";
$this->_default["MAX"] = "null";
// first step - find what columns we have, let's make a query that returns nothing but the columns
$stmt = PHPReportsDBI::db_query($this->_con,"select * from (".$this->_sql.") crosstab_table where 1=2");
$cols = Array();
for($i=1; $i<=PHPReportsDBI::db_colnum($stmt); $i++)
array_push($cols,PHPReportsDBI::db_columnName($stmt,$i));
PHPReportsDBI::db_free($stmt);
// find a delimiter
$deli = isNumericType($cols[$this->_group_key]) ? "" : "'";
// now we find the colums to work with the aggregated values - those are the values we'll create the columns
$cagr = array_diff(array_values($cols),array_merge($this->_group_desc,Array($this->_group_key)));
// ok, now we know that columns to work with, we need to know the values of the group key
$stmt = PHPReportsDBI::db_query($this->_con,"select distinct ".$this->_group_key." from (".$this->_sql.") crosstab_table order by ".$this->_group_key);
$keys = Array();
while($row=PHPReportsDBI::db_fetch($stmt))
array_push($keys,$row[$this->_group_key]);
PHPReportsDBI::db_free($stmt);
// create the sql query
// check if there is another default operation other than SUM
$oper = "SUM";
if($this->_options["DEFAULT_OPERATION"])
$oper = strtoupper($this->_options["DEFAULT_OPERATION"]);
// check if there is an order
$order = "";
if($this->_options["ORDER"])
$order = "order by ".$this->_options["ORDER"];
// first the description columns
$sql = "";
foreach($this->_group_desc as $col)
$sql .= $col.",";
$group = substr($sql,0,strlen($str)-1);
$sql = "select ".$sql;
$coln = Array(); // store the used column names
$apcn = $this->_options["APPEND_COLUMNS_NAMES"];
// then the aggregated values
foreach($keys as $key){ // here the key to compare
foreach($cagr as $col){ // here the column to manipulate
$op = $oper; // default operation
// if there is a customized function for this column ...
if($this->_options["COLUMNS_FUNCTIONS"][$col])
$op = $this->_options["COLUMNS_FUNCTIONS"][$col];
// check the default value of the aggregation function - convert to uppercase because they're uppercase there
if(!array_key_exists(strtoupper($op),$this->_default)){
print "THERE IS NO DEFAULT VALUE FOR $op!";
return;
}
$defv = $this->_default[strtoupper($op)];
// check if there is some alias to the function - some translation, for example
$alias= $this->_options["FUNCTIONS_ALIASES"][$op] ? $this->_options["FUNCTIONS_ALIASES"][$op] : $op;
// create the column name
$name = strtoupper($alias)."_".strtoupper($key).($apcn?"_$col":"");
// check if there is already a column name like this, if so create a new
// one based on how many times it was repeated.
if($coln[$name]){
$coln[$name] = $coln[$name]+1;
$name = strtoupper($alias)."_".$coln[$name]."_".strtoupper($key).($apcn?"_$col":"");
}else
$coln[$name] = 1;
$sql .= "$op(case when ".$this->_group_key."=$deli$key$deli then $col else $defv end) as $name,";
}
}
$sql = substr($sql,0,strlen($sql)-1)." from (".$this->_sql.") crosstab_table group by $group $order";
if($this->_options["SHOW_SQL"])
print $sql;
return $sql;
}
}
?>
|
