public function display(Request $request)
{
$jobType = $request->input('jobtype', 'all');
switch ($jobType) {
case 'fixed':
$jobType = 2;
break;
case 'hourly':
$jobType = 3;
break;
case 'services':
$jobType = 1;
break;
default:
$jobType = 0;
}
$startDate = date('Y-m-d', strtotime('-12 month'));
$endDate = date('Y-m-d');
$jobModel = new Job();
$jobCounts = $jobModel->select('jobtype', DB::raw('count(id) as counts'))->where('updated_at', '>=', $startDate)->where('updated_at', '<=', $endDate)->groupBy('jobtype')->get()->toArray();
$chartData = array();
foreach ($jobCounts as $counts) {
switch ($counts['jobtype']) {
case 1:
$label = 'Services';
break;
case 2:
$label = 'Fixed';
break;
case 3:
$label = 'Hourly';
break;
default:
$label = 'All';
}
$chartData[] = array('label' => $label, 'value' => (int) $counts['counts']);
}
$jobStats = $jobModel->select(DB::raw('count(id) as counts'), DB::raw('date(updated_at) as date'))->whereIn('jobtype', array(2, 3))->where('status', 6)->where('updated_at', '>=', $startDate)->where('updated_at', '<=', $endDate)->groupBy('status')->groupBy('updated_at')->get()->toArray();
$statsData = array();
$maxValue = 3;
foreach ($jobStats as $stats) {
$statsData[strtotime($stats['date'])] = (int) $stats['counts'];
$maxValue = $stats['counts'] > $maxValue ? (int) $stats['counts'] : $maxValue;
}
$xtmp = array();
for ($i = 365; $i >= 0; $i--) {
$date = strtotime($endDate . '-' . $i . ' day');
if (isset($statsData[$date])) {
$xtmp[] = array('x' => $date, 'y' => $statsData[$date]);
} else {
$xtmp[] = array('x' => $date, 'y' => 0);
}
}
$tmp[] = array('key' => 'Project completed', 'values' => $xtmp);
$totalProjects = $jobModel->where('status', '>', 0)->whereIn('jobtype', array(2, 3))->where('updated_at', '>=', $startDate)->where('updated_at', '<=', $endDate)->count();
$totalPostedProjects = $jobModel->whereIn('jobtype', array(2, 3))->where('updated_at', '>=', $startDate)->where('updated_at', '<=', $endDate)->count();
$projectStats = $jobModel->select('status', DB::raw('count(id) as counts'))->whereIn('jobtype', array(2, 3))->where('status', '>', 0)->where('updated_at', '>=', $startDate)->where('updated_at', '<=', $endDate)->groupBy('status')->lists('counts', 'status');
$serviceStats = $jobModel->select('status', DB::raw('count(id) as counts'))->where('jobtype', 1)->where('updated_at', '>=', $startDate)->where('updated_at', '<=', $endDate)->groupBy('status')->lists('counts', 'status');
$totalPostedServices = array_sum($serviceStats->all());
$services = $jobModel->select('id')->where('jobtype', 1)->lists('id');
$serviceWorkstream = Workstream::select('type_id', DB::raw('count(id) as counts'))->where('type', 0)->whereIn('type_id', $services)->where('status', 11)->where('updated_at', '>=', $startDate)->where('updated_at', '<=', $endDate)->groupBy('type_id')->lists('counts', 'type_id');
$serviceWorkstream = $serviceWorkstream->all();
$totalSold = array_sum($serviceWorkstream);
$totalSellingServices = count($serviceWorkstream);
if ($jobType != 0) {
$jobModel = $jobModel->where('jobtype', $jobType);
}
$jobs = $jobModel->where('updated_at', '>=', $startDate)->where('updated_at', '<=', $endDate)->orderBy('id', 'desc')->paginate(10);
$data = array();
$data['page'] = 'job';
$data['totalPostedProjects'] = $totalPostedProjects;
$data['totalProjects'] = $totalProjects;
$data['jobs'] = $jobs;
$data['chartData'] = $chartData;
$data['statsData'] = $tmp;
$data['totalCompletedProject'] = isset($projectStats[6]) ? $projectStats[6] : 0;
$data['totalPostedServices'] = $totalPostedServices;
$data['totalActiveServices'] = isset($serviceStats[2]) ? $serviceStats[2] : 0;
$data['totalSellingServices'] = $totalSellingServices;
$data['totalSold'] = $totalSold;
$data['maxValue'] = $maxValue;
//how many jobs are posted and how many are completed
//calculate project convertion ratio
return view('backend.display')->with($data);
}