Guys,
I need to create a report using PIE (Google Charts) to see how many jobs were delivered but the problem is that my query is affecting a lot of the performance. I already added the index in the DB but still not enough.
I did a lot of research and people say to create a view in the DB but I don’t know how to do that. This is my code below, any suggestion? I don’t know what to do anymore.
I’m trying to show how many jobs were delivered and the total of jobs delivered by region.
public function actionGetregion() {
$jobHistory = JobHistory::find()
->where(['status' => "Order Delivered"])
->andWhere(['>=', 'job_datetime', '2016-01-01'])
->andWhere(['<', 'job_datetime', '2016-01-31'])
->with(['job'])
->asArray()
->all();
$myJobs = array();
$regions = array();
foreach($jobHistory as $currentJob) {
array_push($myJobs, $currentJob['job_id']);
array_push($regions, $currentJob['job']['delivery_region']);
}
$uniqueArray = array_unique($regions);
$rows = array();
$table = array();
$table['cols'] = array(
array('label' => 'Region', 'type' => 'string'),
array('label' => 'Percentage', 'type' => 'number')
);
foreach ($uniqueArray as $singleRegion) {
$jobTable = Job::find()
->where(['id' => $myJobs])
->andWhere(['delivery_region' => $singleRegion])
->joinWith('deliveryRegion')
->asArray()
->all();
$temp = array();
$temp[] = array('v' => $jobTable[0]['deliveryRegion']['region_group']);
// Values of each slice
$temp[] = array('v' => count($jobTable));
$rows[] = array('c' => $temp);
}
$table['rows'] = $rows;
$jsonTable = json_encode($table);
return $jsonTable;
}
the rows and temp var is just to put in the PIE chart format for Google.