202 lines
9.1 KiB
PHP
Executable File
202 lines
9.1 KiB
PHP
Executable File
<?php
|
|
|
|
namespace App\Models;
|
|
|
|
use CodeIgniter\Model;
|
|
|
|
class EmpEndorsementModel extends Model
|
|
{
|
|
protected $table = 'emp_endorsement';
|
|
protected $primaryKey = 'id';
|
|
protected $allowedFields = [
|
|
'id',
|
|
'pk',
|
|
'group_key',
|
|
'endorsement_id',
|
|
'emp_code',
|
|
'table_name',
|
|
'actions',
|
|
'name',
|
|
'field_name',
|
|
'old_value',
|
|
'new_value',
|
|
'date_of_correction',
|
|
'status',
|
|
"created_by",
|
|
"updated_by",
|
|
"is_active",
|
|
"remarks",
|
|
"file_id"
|
|
];
|
|
|
|
// Callbacks
|
|
protected $allowCallbacks = true;
|
|
protected $beforeInsert = [];
|
|
protected $afterInsert = [];
|
|
protected $beforeUpdate = [];
|
|
protected $afterUpdate = [];
|
|
protected $beforeFind = [];
|
|
protected $afterFind = [];
|
|
protected $beforeDelete = [];
|
|
protected $afterDelete = [];
|
|
|
|
|
|
// protected function afterFind(array $data)
|
|
// {
|
|
// foreach ($data as &$row) {
|
|
// // Format date_of_coverage field to Indian date format
|
|
// if (isset($row['field_name'])) {
|
|
// $row['field_name'] = remove_underscore_capitalize_first_letter($row['field_name']);
|
|
// }
|
|
// }
|
|
|
|
// return $data;
|
|
// }
|
|
|
|
|
|
public function getDataForEnodrsementListinSIEnhancement($group_key){
|
|
$query = "
|
|
SELECT
|
|
employee_polices.days as old_days,
|
|
employee_polices.rata_premimum as old_rata,
|
|
employee_polices.gst as old_gst,
|
|
client_policy.policy_start_date as old_date,
|
|
|
|
MAX(CASE WHEN emp_endorsement.field_name = 'basic_cover_si' THEN emp_endorsement.new_value ELSE NULL END) AS new_basic_cover_si,
|
|
MAX(CASE WHEN emp_endorsement.field_name = 'premium' THEN emp_endorsement.new_value ELSE NULL END) AS new_si_premium,
|
|
MAX(CASE WHEN emp_endorsement.field_name = 'si_enhancement_date' THEN emp_endorsement.new_value ELSE NULL END) AS date_of_coverage,
|
|
MAX(CASE WHEN emp_endorsement.field_name = 'basic_cover_si' THEN emp_endorsement.old_value ELSE NULL END) AS old_basic_cover_si,
|
|
MAX(CASE WHEN emp_endorsement.field_name = 'premium' THEN emp_endorsement.old_value ELSE NULL END) AS old_si_premium,
|
|
|
|
(MAX(CASE WHEN emp_endorsement.field_name = 'premium' THEN emp_endorsement.new_value ELSE NULL END) - MAX(CASE WHEN emp_endorsement.field_name = 'premium' THEN emp_endorsement.old_value ELSE NULL END)) AS difference_premium,
|
|
|
|
DATEDIFF(employee_polices.policy_end_date, MAX(CASE WHEN emp_endorsement.field_name = 'si_enhancement_date' THEN emp_endorsement.new_value END)) + 1 AS no_of_days,
|
|
|
|
ROUND(
|
|
(
|
|
(
|
|
MAX(CASE WHEN emp_endorsement.field_name = 'premium' THEN emp_endorsement.new_value END) -
|
|
MAX(CASE WHEN emp_endorsement.field_name = 'premium' THEN emp_endorsement.old_value END)
|
|
) *
|
|
DATEDIFF(
|
|
employee_polices.policy_end_date,
|
|
MAX(CASE WHEN emp_endorsement.field_name = 'si_enhancement_date' THEN emp_endorsement.new_value END)
|
|
) / 365
|
|
),
|
|
2
|
|
) AS pro_rata_premimum,
|
|
|
|
ROUND(((MAX(CASE WHEN emp_endorsement.field_name = 'premium' THEN emp_endorsement.new_value ELSE NULL END) - MAX(CASE WHEN emp_endorsement.field_name = 'premium' THEN emp_endorsement.old_value ELSE NULL END)) * DATEDIFF(employee_polices.policy_end_date, MAX(CASE WHEN emp_endorsement.field_name = 'si_enhancement_date' THEN emp_endorsement.new_value ELSE NULL END)) / 365) * 0.18, 2) AS gst,
|
|
ROUND(((MAX(CASE WHEN emp_endorsement.field_name = 'premium' THEN emp_endorsement.new_value ELSE NULL END) - MAX(CASE WHEN emp_endorsement.field_name = 'premium' THEN emp_endorsement.old_value ELSE NULL END)) * DATEDIFF(employee_polices.policy_end_date, MAX(CASE WHEN emp_endorsement.field_name = 'si_enhancement_date' THEN emp_endorsement.new_value ELSE NULL END)) / 365), 2) +
|
|
ROUND(((MAX(CASE WHEN emp_endorsement.field_name = 'premium' THEN emp_endorsement.new_value ELSE NULL END) - MAX(CASE WHEN emp_endorsement.field_name = 'premium' THEN emp_endorsement.old_value ELSE NULL END)) * DATEDIFF(employee_polices.policy_end_date, MAX(CASE WHEN emp_endorsement.field_name = 'si_enhancement_date' THEN emp_endorsement.new_value ELSE NULL END)) / 365) * 0.18, 2) AS total
|
|
|
|
FROM emp_endorsement
|
|
JOIN employee_polices ON employee_polices.id = emp_endorsement.pk
|
|
JOIN client_policy ON client_policy.id = employee_polices.client_policy_id
|
|
WHERE group_key = '{$group_key}'
|
|
";
|
|
|
|
$results = $this->db->query($query)->getResult();
|
|
return $results;
|
|
|
|
}
|
|
|
|
|
|
public function getDataForEnodrsementListinDeletion($group_key){
|
|
$query = "
|
|
SELECT
|
|
|
|
employee_polices.days as old_days,
|
|
employee_polices.rata_premimum as old_rata,
|
|
employee_polices.gst as old_gst,
|
|
employee_polices.policy_end_date as end_date,
|
|
client_policy.policy_start_date as start_date,
|
|
|
|
MAX(CASE WHEN a.field_name = 'date_of_exit' THEN a.new_value ELSE NULL END) AS date_of_leaving,
|
|
MAX(CASE WHEN a.field_name = 'reason_for_exit' THEN a.new_value ELSE NULL END) AS exist_reason,
|
|
DATEDIFF(employee_polices.policy_end_date, deletiondata.dateofexit) AS no_of_days,
|
|
ROUND((employee_polices.rata_premimum * DATEDIFF(employee_polices.policy_end_date, deletiondata.dateofexit)) / 365, 2) AS pro_rata_premium,
|
|
ROUND(((employee_polices.rata_premimum * DATEDIFF(employee_polices.policy_end_date, deletiondata.dateofexit)) / 365) * 0.18, 2) AS gst,
|
|
ROUND(((employee_polices.rata_premimum * DATEDIFF(employee_polices.policy_end_date, deletiondata.dateofexit)) / 365) + (((employee_polices.rata_premimum * DATEDIFF(employee_polices.policy_end_date, deletiondata.dateofexit)) / 365) * 0.18), 2) AS total
|
|
FROM
|
|
emp_endorsement a
|
|
LEFT JOIN
|
|
employees ON a.emp_code = employees.emp_code
|
|
LEFT JOIN
|
|
employee_polices ON employees.id = employee_polices.employee_id
|
|
LEFT JOIN
|
|
client_policy ON client_policy.id = employee_polices.client_policy_id
|
|
LEFT JOIN (
|
|
SELECT
|
|
aa.emp_code,
|
|
aa.new_value AS 'empstatus',
|
|
bb.new_value AS 'changeevent',
|
|
cc.new_value AS 'dateofexit',
|
|
dd.new_value AS 'reasonforexit',
|
|
ee.new_value AS 'status'
|
|
FROM
|
|
(
|
|
SELECT
|
|
a1.emp_code,
|
|
a1.field_name,
|
|
a1.new_value
|
|
FROM
|
|
emp_endorsement AS a1
|
|
WHERE
|
|
a1.field_name = 'emp_status'
|
|
) aa
|
|
LEFT JOIN
|
|
(
|
|
SELECT
|
|
b1.emp_code,
|
|
b1.field_name,
|
|
b1.new_value
|
|
FROM
|
|
emp_endorsement AS b1
|
|
WHERE
|
|
b1.field_name = 'change_event'
|
|
) bb ON aa.emp_code = bb.emp_code
|
|
LEFT JOIN
|
|
(
|
|
SELECT
|
|
c1.emp_code,
|
|
c1.field_name,
|
|
c1.new_value
|
|
FROM
|
|
emp_endorsement AS c1
|
|
WHERE
|
|
c1.field_name = 'date_of_exit'
|
|
) cc ON aa.emp_code = cc.emp_code
|
|
LEFT JOIN
|
|
(
|
|
SELECT
|
|
d1.emp_code,
|
|
d1.field_name,
|
|
d1.new_value
|
|
FROM
|
|
emp_endorsement AS d1
|
|
WHERE
|
|
d1.field_name = 'reason_for_exit'
|
|
) dd ON aa.emp_code = dd.emp_code
|
|
LEFT JOIN
|
|
(
|
|
SELECT
|
|
e1.emp_code,
|
|
e1.field_name,
|
|
e1.new_value
|
|
FROM
|
|
emp_endorsement AS e1
|
|
WHERE
|
|
e1.field_name = 'status'
|
|
) ee ON aa.emp_code = ee.emp_code
|
|
) AS deletiondata ON a.emp_code = deletiondata.emp_code
|
|
WHERE group_key = '{$group_key}';
|
|
";
|
|
|
|
$results = $this->db->query($query)->getResult();
|
|
return $results;
|
|
|
|
}
|
|
|
|
}
|