/*
author dc_cool@163.com
date 2014-2-26
区域热点图的接口
*/
public function getRoomData(){
//获取mysql中东区-综合楼-101类似数据中第一个'-'号前面的字符以及3-7-9的第一个也就是区域的id
//substring_index为mysql提供的截取字符串函数
$qu1sql="select distinct substring_index(address_value,'-', 1) as qid from order_view";
$qu2sql="select distinct substring_index(address,'-', 1) as qname from order_view";
$quyuarr=array();
//处理结果集先将结果放入区域数组中
$qu1query=$this->db->query($qu1sql);
$qu2query=$this->db->query($qu2sql);
$qu1result=$qu1query->result();
$qu2result=$qu2query->result();
for ($i=0;$i<=sizeof($qu1result);$i++){
if($qu1result[$i]->qid){
$quyuarr[$i]['pid']=$qu1result[$i]->qid;
$quyuarr[$i]['name']=$qu2result[$i]->qname;
$quyuarr[$i]['children']=null;
}
}
$louf=array();
$fangj=array();
$loufarr=array();
//按照区域的id来查询楼房的数据
$max=max(array_keys($quyuarr));
for($i=0;$i<=$max;$i++){
$id=$quyuarr[$i]['pid'];
$sql="select id,name from building where district='$id'";
$query=$this->db->query($sql);
$result=$query->result();
$max=max(array_keys($result));
$louf[$i]['name']=$quyuarr[$i]['name'];
for($j=0;$j<=$max;$j++){
$louf[$i][$j]['name']=$result[$j]->name;
$louf[$i][$j]['id']=$result[$j]->id;
//这里原来会产生空数据被注释
//$louf[$i][$j]['children']=null;
//$louf[$i][$j]['order_num']=null;
$rid=$result[$j]->id;
//将楼房的id放入一个新数组中留着以后用
array_push($loufarr,$rid);
$rsql="select count(*) as order_num from room where building=$rid";
$rquery=$this->db->query($rsql);
$rresult=$rquery->result();
$rorder_num=$rresult[0]->order_num;
//注意数据是否是0
if($rorder_num!=0){
$louf[$i][$j]['order_num']=$rresult[0]->order_num;
}
$name=$rresult[0]->name;
}
//无用的id销毁掉
unset($quyuarr[$i]['pid']);
$quyuarr[$i]['children']=$louf[$i];
}
$rom=array();
//变量用完删除
unset($i);
unset($max);
unset($j);
//遍历楼房id取出房间数据，不用一位数组的话，用原来的数组将面对三层循环，直接卡死机器
foreach($loufarr as &$vv){
$asql="select id,name from room where building=$vv";
$aquery=$this->db->query($asql);
$aresult=$aquery->result();
$vv=array('id'=>$vv,'children'=>array());
//一直没有用相同的变量名
foreach($aresult as $v){
$zid=$v->id;
$name=$v->name;
$qwe="select count(*) as order_num from order_view where substring_index(address_value,'-', -1)=$zid";
$asd=$this->db->query($qwe);
$ziyuan=$asd->result();
$order_num=$ziyuan[0]->order_num;
if($order_num!=0){
$arr=array('name'=>$name,'order_num'=>$order_num);
array_push($vv['children'],$arr);
}
}
}
//最后产生了三个数组，一个是quyuarr保存知道楼房的id三维，loufarr保存知道房间的id三维，louf保存房间的名字和工单数二维
//本来的想法是利用id的相同来将子数组push进母数组中，但是循环次数过多，直接逻辑处理的方法也还没有想出来，现在有的想//法是将数据存入数据库中，然后利用mysql的强大搜索来匹配，不过因为时间不多，这里直接循环赋值，考虑到数组的个数，顺序
//可能没变，直接for循环然后等于各种等于
$max=max(count($louf),count($loufarr));
for($i=0;$i<=$max;$i++){
for($j=0;$j<=$i;$j++){
if(isset($louf[$i][$j])&&isset($loufarr[$i]['children'])){
$louf[$i][$j]['children']=$loufarr[$i]['children'];
unset($louf[$i][$j]['id']);
unset($loufarr[$i]['id']);
}
}
}
unset($i);
unset($j);
unset($max);
$array=array('name'=>'报错情况','children'=>$louf);
return $array;
}
大概流程是：先取出所有区域的名称和id，然后更具区域id取出下属楼房的名称和id，最后根据楼房的id取出下属各个房间的工单数量和房间名称
这个接口用来为一张用d3js的技术制作的图提供数据，返回的数据可以直接放进js里使用，大概图形地址:
http://bl.ocks.org/mbostock/7607535
返回的数据
Array ( [name] => 报错情况 [children] =>
Array ( [0] => Array ( [name] => 东区
[0] => Array ( [name] => 计算中心 [order_num] => 3
[children] => Array (
[0] => Array ( [name] => 101 [order_num] => 2 ) [1] => Array ( [name] => 105 [order_num] => 1 ) ) )
[1] => Array ( [name] => 计算机学院 [id] => 6 [order_num] => 3 )
[2] => Array ( [name] => 综合楼 [id] => 7 [order_num] => 3 ) )
[1] => Array ( [name] => 西区 [0] => Array ( [name] => 科技楼 [order_num] => 3
[children] => Array (
[0] => Array ( [name] => 会议室 [order_num] => 1 ) ) )
[1] => Array ( [name] => 文学院 [order_num] => 3 [children] => Array ( [0] => Array ( [name] => 会议室 [order_num] => 1 ) ) ) [2] => Array ( [name] => 体育馆 [id] => 8 ) [3] => Array ( [name] => 49号楼 [id] => 9 ) )
[2] => Array ( [name] => 北区 [0] => Array ( [name] => 45号楼 [order_num] => 5 [children] => Array ( [0] => Array ( [name] => 101 [order_num] => 24 ) [1] => Array ( [name] => 会议室 [order_num] => 1 ) [2] => Array ( [name] => 1084 [order_num] => 20 ) ) ) [1] => Array ( [name] => 48号楼 [order_num] => 4
[children] => Array (
[0] => Array ( [name] => 101 [order_num] => 24 ) [1] => Array ( [name] => 会议室 [order_num] => 1 ) [2] => Array ( [name] => 1084 [order_num] => 20 ) ) ) ) ) )