1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
|
<?php
defined('BASEPATH') or exit('No direct script access allowed');
use Illuminate\Database\Capsule\Manager as DB;
class Score_model extends BaseModel
{
public function __construct()
{
$this->table = strtolower(substr(__CLASS__, 0, -strlen('_model')));
parent::$sort_field = false;
}
public static function get_data_by_application_id($application_id, $searches, $via = 'home')
{
if (empty($searches['from']) || empty($searches['to'])){
return;
}
$where = ' AND a.created_at BETWEEN \'' . $searches['from'] . ' 00:00:00' . '\' AND \'' . $searches['to'] . ' 23:59:59'. '\'';
$scoring_item_data = Scoring_item_model::where('deleted', 0)->where('status', 1)->orderBy('id')->get();
$scoring_item_id_arr = [];
foreach ($scoring_item_data as $scoring_item) {
$scoring_item_id_arr[] = $scoring_item['id'];
}
$max_str = $case_str = '';
foreach ($scoring_item_id_arr as $key => $scoring_item_id){
$max_str .= 'MAX(point_' . $scoring_item_id .') as point_' . $scoring_item_id;
$case_str .= 'CASE WHEN scoring_item_id = ' . $scoring_item_id .' THEN COALESCE(point, 0) END as point_' . $scoring_item_id;
if ($key != count($scoring_item_id_arr) - 1) {
$max_str .= ', ';
$case_str .= ', ';
}
}
$sql = '
SELECT a.*, j.name AS judge,
SUM(s.point) as total, sr.remark, ' . $max_str . '
FROM application AS a
LEFT JOIN (
SELECT application_id, judge_id, point, ' . $case_str . '
FROM score
WHERE deleted = 0 AND status = 1
) AS s ON a.id = s.application_id
LEFT JOIN (
SELECT application_id, judge_id, remark
FROM score_remark
WHERE deleted = 0 AND status = 1
) AS sr ON a.id = sr.application_id AND s.judge_id = sr.judge_id
INNER JOIN (
SELECT id AS j_id, name, created_at
FROM judge
WHERE deleted = 0 AND status = 1
) AS j ON j.j_id = s.judge_id
INNER JOIN (
SELECT application_id, judge_id, judge_submitted
FROM score_status
WHERE deleted = 0 AND status = 1 AND judge_submitted = 1
) AS ss ON a.id = ss.application_id AND s.judge_id = ss.judge_id
WHERE id = ' . $application_id . ' AND (a.nominee_submitted = 1 OR a.nominator_submitted = 1) AND a.condition_master_id = 5 AND a.deleted = 0 AND a.status = 1 ' . $where . '
GROUP BY s.application_id, s.judge_id
ORDER BY j.created_at desc
';
return json_decode(json_encode(DB::select($sql)), true);
}
public static function get_data_by_searching($searches, $via = 'home')
{
$where = '';
if (!empty($searches)) {
if (!empty($searches['application_id'])) {
$where .= ' AND a.id = \'' . $searches['application_id'] . '\'';
}
if (!empty($searches['category_id'])) {
$where .= ' AND a.category_id = \'' . $searches['category_id'] . '\'';
}
if (!empty($searches['from']) && !empty($searches['to'])){
$where .= ' AND a.created_at BETWEEN \'' . $searches['from'] . ' 00:00:00' . '\' AND \'' . $searches['to'] . ' 23:59:59'. '\'';
}
}
$sql = "SELECT *, s.judge_id, s.point
FROM application AS a
LEFT JOIN (
SELECT application_id, judge_id, SUM(point) as point
FROM score
WHERE deleted = 0 AND status = 1
GROUP BY application_id, judge_id
) AS s
ON a.id = s.application_id
WHERE (a.nominee_submitted = 1 OR a.nominator_submitted = 1) AND a.condition_master_id = 5 AND a.deleted = 0 AND a.status = 1 {$where}
";
$applications_scores = json_decode(json_encode(DB::select($sql)), true);
$model = array();
foreach ($applications_scores as $row) {
$model[$row['id']]['reference_no'] = $row['reference_no'];
$model[$row['id']]['application_id'] = $row['application_id'];
$model[$row['id']]['category_id'] = $row['category_id'];
$is_judge_submitted = count(Score_status_model::get_data_by_field_with_value(['application_id' => $row['id'], 'judge_id' => $row['judge_id'], 'judge_submitted' => 1])) != 0;
if ($row['point'] && $is_judge_submitted) {
$model[$row['id']]['judge'][$row['judge_id']] = $row['point'];
$model[$row['id']]['total'] += $row['point'];
$model[$row['id']]['count']++;
}
}
return $model;
}
public static function get_index_data_by_paginate_and_searching($page, $per_page, $searches, $via = 'home')
{
$model = self::get_data_by_searching($searches, $via);
$temp_model['total'] = count($model);
$temp_model['model'] = array_slice($model, (max($page, 1) - 1) * $per_page, $per_page);
return $temp_model;
}
}
|