trying 2nd time, had google links for charting
yii query
[sql]$sql=‘SELECT concat("[\’",’
.' case'
.' when length(src) = 10 then left(src,3)'
.' when length(src) = 11 then mid(src,2,3)'
.' end,'
.' " ==> ",'
.' concat(t2.city, ", ",t2.st),'
.' "\',",'
.' count(src),'
.' ",\'",'
.' SEC_TO_TIME(sum(duration)),'
.' "\',\'",'
.' SEC_TO_TIME(avg(duration)),'
.' "\'],") as areacodes '
.'FROM cdr8 ’
.’ join ac as t2’
.’ on t2.acs = ’
.’ case ’
.’ when length(src) = 10 then left(src,3) ’
.’ when length(src) = 11 then mid(src,2,3) ’
.’ end ’
.'WHERE length(src) > 9 ’
.'group by ’
.’ case ’
.’ when length(src) = 10 then left(src,3) ’
.’ when length(src) = 11 then mid(src,2,3) ’
.’ end’;
// echo "<pre>$sql</pre>";
$result=Cdr8::model()->findBySql($sql); [/sql]
yii result snippet
[html]<h1>Cdr8s</h1>
<div id="yw0" class="list-view">
<div class="summary">Displaying 1-50 of 573243 results.</div>
<div class="items">
<!-- div class="view" -->
<!-- /div–>
<!-- div class="view" -->
<!-- /div–>
<!-- div class="view" -->
<!-- /div–>
<!-- div class="view" -->[/html]
orig sql
[sql]SELECT concat("[’",
case
when length(src) = 10 then left(src,3)
when length(src) = 11 then mid(src,2,3)
end,
" ==> ",
concat(t2.city, ", ",t2.st),
"',",
count(src),
",'",
SEC_TO_TIME(sum(duration)),
"','",
SEC_TO_TIME(avg(duration)),
"'],") as areacodes
FROM cdr8
join ac as t2
on t2.acs =
case
when length(src) = 10 then left(src,3)
when length(src) = 11 then mid(src,2,3)
end
WHERE length(src) > 9
group by
case
when length(src) = 10 then left(src,3)
when length(src) = 11 then mid(src,2,3)
end [/sql]
Results externally of yii snippet 113 rows
['202 ==> District Of Columbia, DC',1,'00:00:52','00:00:52'],
['208 ==> Boise City, ID',1,'00:01:15','00:01:15'],
['209 ==> Stockton, CA',8,'00:10:37','00:01:20'],
['213 ==> Los Angeles, CA',679,'12:53:29','00:01:08'],
['218 ==> Duluth, MN',1,'00:01:29','00:01:29'],
['219 ==> Gary, IN',1,'00:00:47','00:00:47'],
['231 ==> Muskegon, MI',4,'00:00:48','00:00:12'],
['239 ==> Bokeelia, FL',6,'00:14:26','00:02:24'],
['240 ==> Gaithersburg, MD',3,'00:02:02','00:00:41'],
['252 ==> Greenville, NC',8,'00:12:51','00:01:36'],
['254 ==> Waco, TX',1,'00:00:13','00:00:13'],
google visulization script
<script>
google.load('visualization', '1', {packages: ['table']});
function drawVisualization() {
// Create and populate the data table.
var data = google.visualization.arrayToDataTable([
['<==outbound - inbound==>','Answered Calls','Tot Time','AVG Call'],
['000 ==> Bad000, zz',1,'00:00:58','00:00:58'],
['202 ==> District Of Columbia, DC',1,'00:00:52','00:00:52'],
...
['972 <== Dallas, TX',5,'00:12:45','00:02:33'],
['973 <== Newark, NJ',2,'00:01:15','00:00:38']
]);
thc="google-visualization-table-th";
c2 =0;
c3 =0;
var x1 = data.getNumberOfRows();
for (var i = 1; i < x1; i++) {
c2 = c2+data.getValue(i, 1);
// c3 = c3+data.getValue(i, 2);
}
tdiv = document.getElementById('totals');
tc="<table id=tot><tr><td align='right' width='120px'></td><td align='right' width='420px'>Calls = "+c2+
"</td><td align='right' width='20px'> <!-- hours=+c3.toFixed(2) -->"+
"</td><td align='right' width='20px'></td></tr></table>";
tdiv.innerHTML=tc;
// Create and draw the visualization.
var table = new google.visualization.Table(document.getElementById('visualization'));
var formatter = new google.visualization.TableBarFormat({width: 420});
formatter.format(data, 1); // Apply formatter to second column
table.draw(data, {allowHtml: true, showRowNumber: true});
}
google.setOnLoadCallback(drawVisualization);
</script>
</head>
<body style="font-family: Arial;border: 0 none;">
<center><h1>Area Codes Answered<br>Dec 2012 QAF - Upland</h1>
<h3>external calls</h3>
<div id=totals></div>
<div id="visualization" style="width: 900px; height: 1400px;font-size:20%;line-height:10px;"></div>
</center>
</body>
<style>
* {line-height:80%;}
tr {line-height:80%;}
.google-visualization-table-td:nth-of-type(1) {color:black;font-size:9px;text-align: left;}
table tr td span {height:8px;font-size:9px}
</style>
model
class RmDbCriteria extends CDbCriteria
{
public function compare($column, $value, $partialMatch=false, $operator='AND', $escape=true, $nullSign='null', $betweenSign='-')
{
if(is_array($value))
{
if($value===array())
return $this;
return $this->addInCondition($column,$value,$operator);
}
else
$value="$value";
if(preg_match('/^(?:\s*([\w\d\s]*)(<>|<=|>=|<|>|=|'.$betweenSign.'))?(.*)$/',$value,$matches))
{
$value1=$matches[1];
$value=$matches[3];
$op=$matches[2];
}
else
$op='';
if($value==='')
return $this;
if($partialMatch)
{
if($value==$nullSign)
{
$this->addCondition($column.' is null');
return $this;
}
if($op==='')
return $this->addSearchCondition($column,$value,$escape,$operator);
if($op==='<>')
return $this->addSearchCondition($column,$value,$escape,$operator,'NOT LIKE');
}
else if($op==='')
$op='=';
if($op==$betweenSign)
{
$this->addBetweenCondition($column,$value1,$value);
return $this;
}
if($value==$nullSign && $nullSign!='0')
{
$this->addCondition($column.' is null');
return $this;
}
if($nullSign=='0')
{
$column='IFNULL('.$column.',0)';
}
$this->addCondition($column.$op.self::PARAM_PREFIX.self::$paramCount,$operator);
$this->params[self::PARAM_PREFIX.self::$paramCount++]=$value;
return $this;
}
}
class Cdr8 extends CActiveRecord
{
/**
* Returns the static model of the specified AR class.
* @param string $className active record class name.
* @return Cdr8 the static model class
*/
public static function model($className=__CLASS__)
{
return parent::model($className);
}
/**
* @return string the associated database table name
*/
public function tableName()
{
return 'cdr8';
}
/**
* @return array validation rules for model attributes.
*/
public function rules()
{
// NOTE: you should only define rules for those attributes that
// will receive user inputs.
return array(
array('calldate, channel, src, clid, dst, dstchannel, disposition, duration', 'required'),
array('duration', 'numerical', 'integerOnly'=>true),
array('calldate, channel, src, clid, dst, dstchannel, disposition', 'length', 'max'=>50),
// The following rule is used by search().
// Please remove those attributes that should not be searched.
array('cdrid, calldate, channel, src, clid, dst, dstchannel, disposition, duration', 'safe', 'on'=>'search'),
);
}
/**
* @return array relational rules.
*/
public function relations()
{
// NOTE: you may need to adjust the relation name and the related
// class name for the relations automatically generated below.
return array(
);
}
/**
* @return array customized attribute labels (name=>label)
*/
public function attributeLabels()
{
return array(
'cdrid' => 'Cdrid',
'calldate' => 'Calldate',
'channel' => 'Channel',
'src' => 'Src',
'clid' => 'Clid',
'dst' => 'Dst',
'dstchannel' => 'Dstchannel',
'disposition' => 'Disposition',
'duration' => 'Duration',
);
}
/**
* Retrieves a list of models based on the current search/filter conditions.
* @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions.
*/
public function search()
{
// Warning: Please modify the following code to remove attributes that
// should not be searched.
$criteria=new RmDbCriteria; //CDbCriteria;
// $criteria->limit = 25;
$criteria->compare('cdrid',$this->cdrid);
$criteria->compare('calldate',$this->calldate,true);
$criteria->compare('channel',$this->channel,true);
$criteria->compare('src',$this->src,true);
$criteria->compare('clid',$this->clid,true);
$criteria->compare('dst',$this->dst,true);
$criteria->compare('dstchannel',$this->dstchannel,true);
$criteria->compare('disposition',$this->disposition,true);
$criteria->compare('duration',$this->duration);
return new CActiveDataProvider($this, array(
'criteria'=>$criteria,
));
}
}
