Odyssey
Public Member Functions | Private Attributes | List of all members
PivotCTE Class Reference

Public Member Functions

 __construct ()
 
 GetCTE ()
 
 GetColumnSQL ($toBeSummed)
 
 GetColumns ($columnArray)
 
 __construct ()
 
 getCTE ()
 
 getColumnSQL ($toBeSummed)
 
 getColumns ($columnArray)
 

Private Attributes

 $pivotArray
 
 $cte
 
 $columnSQLArray
 

Detailed Description

class PivotCTE NOTE: Odyssey only. This is in Mammoth to keep this file in sync but it can only be used in Odyssey. Mammoth uses a prior version of Postgres that doesn't have support for Common Table Expressions (CTEs).

This class represents the CTE for months. It replaces the culogtrack_pivot which has the current month plus the past 12 months. There is a column per month and quarter which is zero or one.

class PivotCTE NOTE: Odyssey only. This is in Mammoth to keep this file in sync but it can only be used in Odyssey because Mammoth uses a prior version of Postgres that doesn't have support for Common Table Expressions (CTEs).

This class represents the CTE for months. It replaces the culogtrack_pivot which has the current month plus the past 12 months. There is a column per month and quarter which is zero or one.

Definition at line 75 of file reporting.i.

Constructor & Destructor Documentation

◆ __construct() [1/2]

PivotCTE::__construct ( )

function __construct()

This function gets the current timestamp, strips out everything but the year and the month, and then fills in the pivotArray values based on that. The pivotArray will have 13 records: 12 months ago to the current month. Each record has the date as varchar Ym, title as "Jan", "Feb", etc., months as an array of zeroes and ones– the ones will be the month that that date corresponds to, quarters as an array of zeroes and ones – the ones will be the quarter that the date corresponds to.

Definition at line 93 of file reporting.i.

93  {
94  $pivotArray = array();
95  try {
96  $date = DateTime::createFromFormat("Y-m-d H:i:s", date("Y-m")."-1 00:00:00");
97  // NOTE: doing createFromFormat("Ym") is STILL going to create a date with day, hours, minutes, etc.
98 
99  for($i = 0; $i <= 12; $i++) {
100  $row = array("date" => "'" . $date->format("Ym") . "'::varchar",
101  "title" => $date->format("M"),
102  "months" => array_fill(0,13, 0),
103  "quarters" => array_fill(0,4,0));
104  $row["months"][$i] = 1;
105  $row["quarters"][floor($i/4)] = 1;
106  $pivotArray[] = $row;
107  $date->modify("-1 month");
108  }
109  } catch(exception $e) {
110  throw new exception ("Pivot CTE not created correctly: " . $e->getMessage());
111  }
112 
113  $this->pivotArray = $pivotArray;
114  $this->columnSQLArray = array();
115  }

◆ __construct() [2/2]

PivotCTE::__construct ( )

function __construct()

This function gets the current timestamp, strips out everything but the year and the month, and then fills in the pivotArray values based on that. The pivotArray will have 13 records: 12 months ago to the current month. Each record has the date as varchar Ym, title as "Jan", "Feb", etc., months as an array of zeroes and ones– the ones will be the month that that date corresponds to, quarters as an array of zeroes and ones – the ones will be the quarter that the date corresponds to.

Definition at line 44 of file reports.i.

45  {
46  $pivotArray= array();
47  try
48  {
49  $date= new DateTime();
50  $dateText= $date->format("Ym");
51  $date= DateTime::createFromFormat("Ym", $dateText); // Clears out the rest of the date. Just concerned with the year and the month.
52 
53  for($i=0; $i <= 12; $i++)
54  {
55  $row= array("date" => "'" . $date->format("Ym") . "'::varchar", "title" => $date->format("M"), "months" => array_fill(0,13, 0), "quarters" => array_fill(0,4,0));
56  $row["months"][$i]= 1;
57  $row["quarters"][floor($i/4)]= 1;
58  $pivotArray[]= $row;
59  $date->modify("-1 month");
60  }
61  }
62  catch(exception $e)
63  {
64  throw new exception ("Pivot CTE not created correctly: " . $e->getMessage());
65  }
66 
67  $this->pivotArray= $pivotArray;
68  $this->columnSQLArray= array();
69  }

Member Function Documentation

◆ getColumns()

PivotCTE::getColumns (   $columnArray)

public function getColumns($columnArray)

This function gets the column list.

Parameters
array$columnArray– Set the column list as usual here minus the columns for the pivot.
Returns
array $columnArray – The starting columns plus "MTD" => "number", "Jan" => "number", "Dec" => "number" etc. (That would be if the current date was in February.)

Definition at line 122 of file reports.i.

123  {
124  $first= true;
125  foreach($this->pivotArray as $pivotRow)
126  {
127  $col= $first ? "MTD" : $pivotRow["title"];
128  $columnArray[$col]= "number";
129  $first= false;
130  }
131  return $columnArray;
132  }

◆ GetColumns()

PivotCTE::GetColumns (   $columnArray)

public function GetColumns($columnArray) This function gets the column list.

Parameters
array$columnArray– Set the column list as usual here minus the columns for the pivot.
Returns
array $columnArray – The starting columns plus "MTD" => "number", "Jan" => "number", "Dec" => "number" etc. (That would be if the current date was in February.)

Definition at line 163 of file reporting.i.

163  {
164  $first = true;
165  foreach($this->pivotArray as $pivotRow) {
166  $col = $first ? "MTD" : $pivotRow["title"];
167  $columnArray[$col] = "number";
168  $first = false;
169  }
170  return $columnArray;
171  }

◆ getColumnSQL()

PivotCTE::getColumnSQL (   $toBeSummed)

public function getColumnSQL($toBeSummed)

This function gets the SQL for the columns. Each column looks like "sum($toBeSummed * monthColumn)".

Parameters
string$toBeSummed– This is what needs to be summed to be summed by month.
Returns
string – Add this to the column section of the SQL.

Definition at line 99 of file reports.i.

100  {
101  if (isset($this->columnSQLArray[$toBeSummed]))
102  return $this->columnSQLArray[$toBeSummed];
103 
104  $columnArray= array();
105  for($i=0; $i <= 12; $i++)
106  {
107  $columnArray[]= "sum($toBeSummed * m$i)";
108  }
109 
110  $this->columnSQLArray[$toBeSummed]= implode(",", $columnArray);
111  return $this->columnSQLArray[$toBeSummed];
112  }

◆ GetColumnSQL()

PivotCTE::GetColumnSQL (   $toBeSummed)

public function GetColumnSQL($toBeSummed) This function gets the SQL for the columns. Each column looks like "sum($toBeSummed * monthColumn)".

Parameters
string$toBeSummed– This is what needs to be summed to be summed by month.
Returns
string – Add this to the column section of the SQL.

Definition at line 142 of file reporting.i.

142  {
143  if (isset($this->columnSQLArray[$toBeSummed])) {
144  return $this->columnSQLArray[$toBeSummed];
145  }
146 
147  $columnArray = array();
148  for($i = 0; $i <= 12; $i++) {
149  $columnArray[] = "sum($toBeSummed * m$i)";
150  }
151 
152  $this->columnSQLArray[$toBeSummed] = implode(",", $columnArray);
153  return $this->columnSQLArray[$toBeSummed];
154  }

◆ getCTE()

PivotCTE::getCTE ( )

public function getCTE()

This function gets the WITH clause to the SQL. It uses the "values" syntax and gets all the values from the pivotArray. If the CTE is already calculated, then it just returns that.

Definition at line 76 of file reports.i.

77  {
78  if (isset($this->cte))
79  return $this->cte;
80 
81  $cteArray= array();
82  foreach($this->pivotArray as $pivotRow)
83  {
84  $cteArray[]= "(" . $pivotRow["date"] . "," . implode(",", $pivotRow["months"]) . "," . implode(",", $pivotRow["quarters"]) . ")";
85  }
86 
87  $this->cte= "with monthPivotCTE(yearmo,m0,m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12,q0,q1,q2,q3) as (values " . implode(",", $cteArray) . ")";
88  return $this->cte;
89  }

◆ GetCTE()

PivotCTE::GetCTE ( )

public function GetCTE() This function gets the WITH clause to the SQL. It uses the "values" syntax and gets all the values from the pivotArray. If the CTE is already calculated, then it just returns that.

Definition at line 121 of file reporting.i.

121  {
122  if (isset($this->cte)) {
123  return $this->cte;
124  }
125 
126  $cteArray = array();
127  foreach($this->pivotArray as $pivotRow) {
128  $cteArray[] = "(" . $pivotRow["date"] . "," . implode(",", $pivotRow["months"]) . "," . implode(",", $pivotRow["quarters"]) . ")";
129  }
130 
131  $this->cte = "with monthPivotCTE(yearmo,m0,m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12,q0,q1,q2,q3) as (values " . implode(",", $cteArray) . ")";
132  return $this->cte;
133  }
The documentation for this class was generated from the following files:
Generated by   doxygen 1.8.15