最近需要做到交叉表,而公司的需求比较复杂,一般的交叉表工具都不适合用
如果使用sql语句做的话 工作量太大了,于是尝试自己写一个交叉表的类,好二话不说,我们看看代码
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
/*
*
* 基本交叉表
* @author hugh
*
*/
class Pivot
{
private $HORIZONTAL_TOTAL_FIELD = ' total ' ;
private $VERTICAL_TOTAL_FIELD = ' total ' ;
private $data ;
private $topPivot ;
private $leftPivot ;
private $measure ;
private $horizontalColumn = array ();
private $verticalColumn = array ();
private $pivotValue = array ();
private $isHorizontalTotal = true ;
private $isVerticalTotal = true ;
private $horizontalTotal = null ;
private $verticalTotal = null ;
private $title = ' PivotTab ' ;
/* *
* 初始化交叉表
*/
private function InitPivot()
{
$this -> topPivot;
foreach ( $this -> data as $d )
{
$this -> horizontalColumn [] = $d [ $this -> leftPivot];
$this -> verticalColumn [] = $d [ $this -> topPivot];
}
$this -> horizontalColumn = array_unique ( $this -> horizontalColumn );
$this -> verticalColumn = array_unique ( $this -> verticalColumn );
$reasult = array ();
foreach ( $this -> horizontalColumn as $h )
{
foreach ( $this -> verticalColumn as $v )
{
$this -> pivotValue [ $h ] [ $v ] = 0 ;
}
}
}
/* *
* 填充数据
*/
private function fillData()
{
foreach ( $this -> data as $row )
{
$this -> pivotValue [ $row [ $this -> leftPivot]] [ $row [ $this -> topPivot]] += $row [ $this -> measure];
}
if ( $this -> isHorizontalTotal)
{
$this -> setHorizontalTotal ();
}
if ( $this -> isVerticalTotal)
{
$this -> setVerticalTotal ();
}
}
/* *
* 设置纵向合计
*/
private function setVerticalTotal()
{
$this -> verticalColumn [] = $this -> VERTICAL_TOTAL_FIELD;
foreach ( $this -> horizontalColumn as $i )
{
$rowsum = 0 ;
foreach ( $this -> verticalColumn as $j )
{
$rowsum += $this -> pivotValue [ $i ] [ $j ];
}
$this -> pivotValue [ $i ] [ $this -> TOTAL_FIELD] = $rowsum ;
}
}
/* *
* 设置横向合计
*/
private function setHorizontalTotal()
{
$this -> horizontalColumn [] = $this -> HORIZONTAL_TOTAL_FIELD;
foreach ( $this -> verticalColumn as $i )
{
$rowsum = 0 ;
foreach ( $this -> horizontalColumn as $j )
{
$rowsum += $this -> pivotValue [ $j ] [ $i ];
}
$this -> pivotValue [ $this -> HORIZONTAL_TOTAL_FIELD] [ $i ] = $rowsum ;
}
}
/* *
* 渲染
*/
function Render()
{
echo ' <pre> ' ;
print_r ( $this -> pivotValue );
}
/* *
* 渲染为table
*/
function RenderToTable()
{
$resault = " <table border='1' width='250'>\n " ;
$resault .= " <tr><td> $this ->title</td>\n " ;
foreach ( $this -> verticalColumn as $value )
{
$resault .= " <td> $value </td>\n " ;
}
$resault .= " </tr>\n " ;
foreach ( $this -> horizontalColumn as $i )
{
$resault .= " <tr><td> $i </td>\n " ;
foreach ( $this -> pivotValue [ $i ] as $value )
{
$resault .= " <td> $value </td>\n " ;
}
$resault .= " </tr>\n " ;
}
$resault .= " </table> " ;
return $resault ;
}
/* *
* 构造交叉表
* @param $data 数据源
* @param $topPivot 头栏目字段
* @param $leftPivot 左栏目字段
* @param $measure 计算量
*/
function __construct( array $data , $topPivot , $leftPivot , $measure )
{
$this -> data = $data ;
$this -> leftPivot = $leftPivot ;
$this -> topPivot = $topPivot ;
$this -> measure = $measure ;
$this -> horizontalColumn = array ();
$this -> verticalColumn = array ();
$this -> InitPivot ();
$this -> fillData ();
}
}
* 基本交叉表
* @author hugh
*
*/
class Pivot
{
private $HORIZONTAL_TOTAL_FIELD = ' total ' ;
private $VERTICAL_TOTAL_FIELD = ' total ' ;
private $data ;
private $topPivot ;
private $leftPivot ;
private $measure ;
private $horizontalColumn = array ();
private $verticalColumn = array ();
private $pivotValue = array ();
private $isHorizontalTotal = true ;
private $isVerticalTotal = true ;
private $horizontalTotal = null ;
private $verticalTotal = null ;
private $title = ' PivotTab ' ;
/* *
* 初始化交叉表
*/
private function InitPivot()
{
$this -> topPivot;
foreach ( $this -> data as $d )
{
$this -> horizontalColumn [] = $d [ $this -> leftPivot];
$this -> verticalColumn [] = $d [ $this -> topPivot];
}
$this -> horizontalColumn = array_unique ( $this -> horizontalColumn );
$this -> verticalColumn = array_unique ( $this -> verticalColumn );
$reasult = array ();
foreach ( $this -> horizontalColumn as $h )
{
foreach ( $this -> verticalColumn as $v )
{
$this -> pivotValue [ $h ] [ $v ] = 0 ;
}
}
}
/* *
* 填充数据
*/
private function fillData()
{
foreach ( $this -> data as $row )
{
$this -> pivotValue [ $row [ $this -> leftPivot]] [ $row [ $this -> topPivot]] += $row [ $this -> measure];
}
if ( $this -> isHorizontalTotal)
{
$this -> setHorizontalTotal ();
}
if ( $this -> isVerticalTotal)
{
$this -> setVerticalTotal ();
}
}
/* *
* 设置纵向合计
*/
private function setVerticalTotal()
{
$this -> verticalColumn [] = $this -> VERTICAL_TOTAL_FIELD;
foreach ( $this -> horizontalColumn as $i )
{
$rowsum = 0 ;
foreach ( $this -> verticalColumn as $j )
{
$rowsum += $this -> pivotValue [ $i ] [ $j ];
}
$this -> pivotValue [ $i ] [ $this -> TOTAL_FIELD] = $rowsum ;
}
}
/* *
* 设置横向合计
*/
private function setHorizontalTotal()
{
$this -> horizontalColumn [] = $this -> HORIZONTAL_TOTAL_FIELD;
foreach ( $this -> verticalColumn as $i )
{
$rowsum = 0 ;
foreach ( $this -> horizontalColumn as $j )
{
$rowsum += $this -> pivotValue [ $j ] [ $i ];
}
$this -> pivotValue [ $this -> HORIZONTAL_TOTAL_FIELD] [ $i ] = $rowsum ;
}
}
/* *
* 渲染
*/
function Render()
{
echo ' <pre> ' ;
print_r ( $this -> pivotValue );
}
/* *
* 渲染为table
*/
function RenderToTable()
{
$resault = " <table border='1' width='250'>\n " ;
$resault .= " <tr><td> $this ->title</td>\n " ;
foreach ( $this -> verticalColumn as $value )
{
$resault .= " <td> $value </td>\n " ;
}
$resault .= " </tr>\n " ;
foreach ( $this -> horizontalColumn as $i )
{
$resault .= " <tr><td> $i </td>\n " ;
foreach ( $this -> pivotValue [ $i ] as $value )
{
$resault .= " <td> $value </td>\n " ;
}
$resault .= " </tr>\n " ;
}
$resault .= " </table> " ;
return $resault ;
}
/* *
* 构造交叉表
* @param $data 数据源
* @param $topPivot 头栏目字段
* @param $leftPivot 左栏目字段
* @param $measure 计算量
*/
function __construct( array $data , $topPivot , $leftPivot , $measure )
{
$this -> data = $data ;
$this -> leftPivot = $leftPivot ;
$this -> topPivot = $topPivot ;
$this -> measure = $measure ;
$this -> horizontalColumn = array ();
$this -> verticalColumn = array ();
$this -> InitPivot ();
$this -> fillData ();
}
}
重点在于InitPivot方法及fillData方法。
InitPivot里面保证了所有的item都会有值(默认为0)
fillData方法使用选择填充添加的方法,将数据填充入我们装数据的$pivotValue里面。
然后喜欢怎么输出都可以了