EVOLUTION-NINJA

Edit File: Training_Model.php

<?php

namespace App\Models;

use CodeIgniter\Model;

class Training_Model extends Model
{

protected $table = 'training_plans';
    protected $primaryKey = 'training_plan_id';
    protected $useSoftDeletes = true;
    protected $allowedFields = ['chapter_ids', 'created_by', 'delete_status'];

// public function getTrainingPlanChapters($subDomainId, $loginId)
    // {
    //     $query = $this->db->table('training_plans A')
    //         ->select('*')
    //         ->where('A.delete_status', 'ACTIVE')
    //         ->where('A.created_by', $loginId)
    //         ->get();

//     $trainingResult = $query->getResult();

//     if ($trainingResult) {
    //         $chapterArray = [];
    //         foreach ($trainingResult as $value) {
    //             $chapterIds = json_decode($value->chapter_ids);
    //             foreach ($chapterIds as $val) {
    //                 $chapterArray[] = $val;
    //             }
    //         }

//         $chapterArray = implode(',', $chapterArray);

//         // Check if $subDomainId is not NULL before executing the query
    //         if ($subDomainId !== null) {
    //             // Use query bindings to avoid SQL injection
    //             $sql = "SELECT chapter_id, chapter_name FROM syllabus_chapter WHERE chapter_id NOT IN ($chapterArray) AND sub_domain_id = ? AND delete_status = 'ACTIVE'";
    //             $result = $this->db->query($sql, [$subDomainId])->getResult();
    //         } else {
    //             // Use query bindings to avoid SQL injection
    //             $sql = "SELECT chapter_id, chapter_name FROM syllabus_chapter WHERE delete_status = 'ACTIVE'";
    //             $result = $this->db->query($sql)->getResult();
    //         }

//         return $result;
    //     } else {
    //         // Use query bindings to avoid SQL injection
    //         $sql = "SELECT chapter_id, chapter_name FROM syllabus_chapter WHERE sub_domain_id = ? AND delete_status = 'ACTIVE'";
    //         $result = $this->db->query($sql, [$subDomainId])->getResult();

//         return $result;
    //     }
    // }

public function get_training_plan_chapters($sub_domain_id, $login_id)
    {
        $db = \Config\Database::connect();
        $builder = $db->table('training_plans A');
        $builder->select('*');
        $builder->where('A.delete_status', 'ACTIVE');
        $builder->where('A.created_by', $login_id);
        $query = $builder->get();
        $training_result = $query->getResult();
    
        if ($training_result) {
            $chapter_array = [];
            foreach ($training_result as $value) {
                $chapter_ids = json_decode($value->chapter_ids);
                foreach ($chapter_ids as $val) {
                    array_push($chapter_array, $val);
                }
            }
            $chapter_array = implode(',', $chapter_array);
    
            // $sql = "SELECT chapter_id, chapter_name FROM syllabus_chapter WHERE chapter_id NOT IN ($chapter_array) AND sub_domain_id = $sub_domain_id AND delete_status = 'ACTIVE'";
            // $result = $db->query($sql);
            // $result = $result->getResult();

$builder = $this->db->table('syllabus_chapter');
            $builder->select('chapter_id, chapter_name');
            $builder->whereNotIn('chapter_id', explode(',', $chapter_array));
            $builder->where('sub_domain_id', $sub_domain_id);
            $builder->where('delete_status', 'ACTIVE');
            $result = $builder->get()->getResult();

return $result;
        } else {
            // $sql = "SELECT chapter_id, chapter_name FROM syllabus_chapter WHERE sub_domain_id = $sub_domain_id AND delete_status = 'ACTIVE'";
            // $result = $db->query($sql);
            // $result = $result->getResult();

$builder = $this->db->table('syllabus_chapter');
            $builder->select('chapter_id, chapter_name');
            $builder->where('sub_domain_id', $sub_domain_id);
            $builder->where('delete_status', 'ACTIVE');
            $result = $builder->get()->getResult();
    
            return $result;
        }
    }

public function getTrainingPlanEditChapters($sub_domain_id, $login_id, $id)
    {
        $builder = $this->db->table('training_plans A');
        $builder->select('*');
        $builder->where('A.delete_status', 'ACTIVE');
        $builder->where('A.created_by', $login_id);
        $builder->where('A.training_plan_id !=', $id);
        $query = $builder->get();
        $trainingResult = $query->getResult();
    
        if ($trainingResult) {
            $chapterArray = [];
    
            foreach ($trainingResult as $value) {
                $chapterIds = json_decode($value->chapter_ids);
                foreach ($chapterIds as $val) {
                    $chapterArray[] = $val;
                }
            }
    
            $chapterIdsStr = implode(',', $chapterArray);
    
            $builder = $this->db->table('syllabus_chapter');
            $builder->select('chapter_id, chapter_name');
            $builder->whereNotIn('chapter_id', explode(',', $chapterIdsStr));
            $builder->where('sub_domain_id', $sub_domain_id);
            $builder->where('delete_status', 'ACTIVE');
            $result = $builder->get()->getResult();
    
            return $result;
        } else {
            $builder = $this->db->table('syllabus_chapter');
            $builder->select('chapter_id, chapter_name');
            $builder->where('sub_domain_id', $sub_domain_id);
            $builder->where('delete_status', 'ACTIVE');
            $result = $builder->get()->getResult();
    
            return $result;
        }
    }

public function getWhereRow($table, $where)
    {
        $builder = $this->db->table($table);
        $builder->select('*');
        $builder->where($where);
        $query = $builder->get();

return $query->getRow();
    }

public function getDomains($id)
    {
        $builder = $this->db->table('faculty_subject_mapping A');
        $builder->select('*');
        $builder->join('domains B', 'B.domain_id = A.domain_id');
        $builder->whereIn('A.login_id', [$id]);
        $builder->where('A.delete_status', 'ACTIVE');
        $builder->where('B.delete_status', 'ACTIVE');
        $builder->where('A.approval_status', 'APPROVED');
        $builder->where('A.return_status', 'NOT_RETURNED');
        $builder->groupBy('A.domain_id');

$query = $builder->get();
        return $query->getResult();
    }

public function insertData($table, $data)
    {
        $builder = $this->db->table($table);
        $builder->insert($data);
        return $this->db->insertID();
    }

public function getSessionList($login_id)
    {
        $builder = $this->db->table('training_plans A');
        $builder->select('A.*, C.sub_domain_id, C.sub_domain, D.domain_id, D.domain_name');
        $builder->join('sub_domains C', 'A.sub_domain_id = C.sub_domain_id');
        $builder->join('domains D', 'D.domain_id = C.domain_id');
        $builder->where('A.delete_status', 'ACTIVE');
        $builder->where('A.created_by', $login_id);
        $builder->where('C.delete_status', 'ACTIVE');
        $builder->where('D.delete_status', 'ACTIVE');
        $builder->groupBy('A.sub_domain_id');
        $query = $builder->get();
        $result = $query->getResult();

$resArray = [];

foreach ($result as $res) {
            $subDomainQuery = $this->db->table('training_plans A');
            $subDomainQuery->select('*');
            $subDomainQuery->where('A.delete_status', 'ACTIVE');
            $subDomainQuery->where('A.sub_domain_id', $res->sub_domain_id);
            $subDomainQuery->where('A.created_by', $res->created_by);
            $subDomainResult = $subDomainQuery->get()->getResult();

$totalDuration = '00:00';

foreach ($subDomainResult as $val) {
                $duration = explode(':', $totalDuration);
                $dur = explode(':', $val->session_time);
                $totalTimeHr = number_format($duration[0]) + number_format($dur[0]);
                $totalTimeMin = number_format($duration[1]) + number_format($dur[1]);

if ($totalTimeMin >= 60) {
                    $totalTimeHr += 1;
                    $totalTimeMin -= 60;
                }

$totalDuration = $totalTimeHr . ':' . $totalTimeMin;
            }

$totalDur = explode(':', $totalDuration);

if (strlen((string)$totalDur[1]) == 1) {
                $totalTimeMin = '0' . $totalTimeMin;
                $totalDuration = $totalTimeHr . ':' . $totalTimeMin;
            }

$data = [
                'training_plan_id' => $res->training_plan_id,
                'sub_domain_id' => $res->sub_domain_id,
                'sub_domain' => $res->sub_domain,
                'domain_name' => $res->domain_name,
                'total_duration' => $totalDuration,
            ];

array_push($resArray, $data);
        }

return $resArray;
    }

public function chapter_wise_training_plans($sub_domain_id, $login_id)
    {
        $builder = $this->db->table('training_plans A');
        $builder->select('A.*, C.sub_domain_id, C.sub_domain, D.domain_id, D.domain_name');
        $builder->join('sub_domains C', 'A.sub_domain_id = C.sub_domain_id');
        $builder->join('domains D', 'D.domain_id = C.domain_id');
        $builder->where('A.delete_status', 'ACTIVE');
        $builder->where('A.created_by', $login_id);
        $builder->where('C.delete_status', 'ACTIVE');
        $builder->where('D.delete_status', 'ACTIVE');
        $builder->where('A.sub_domain_id', $sub_domain_id);
        $query = $builder->get();
        $result = $query->getResult();
        $result_array = [];
    
        foreach ($result as $value) {
            $builder = $this->db->table('syllabus_chapter A');
            $builder->select('*');
            $builder->whereIn('A.chapter_id', json_decode($value->chapter_ids));
            $builder->where('A.delete_status', 'ACTIVE');
            $query = $builder->get();
            $chapter_result = $query->getResult();
            $chapter_array = [];
    
            foreach ($chapter_result as $val) {
                array_push($chapter_array, $val->chapter_name);
            }
    
            $data['chapters'] = implode(', ', $chapter_array);
            $data['sub_domain'] = $value->sub_domain;
            $data['domain_name'] = $value->domain_name;
            $data['session_time'] = $value->session_time;
    
            if ($value->updated_at != '0000-00-00 00:00:00') {
                $data['created_at'] = date("d-m-Y H:i:s", strtotime($value->updated_at));
            } else {
                $data['created_at'] = date("d-m-Y H:i:s", strtotime($value->created_at));
            }
    
            $data['training_plan_id'] = $value->training_plan_id;
    
            if ($value->return_status == 'RETURNED') {
                $retVal = 'RETURNED';
                $data['approval_status'] = $retVal;
            } else {
                $retVal = $value->approval_status;
                $data['approval_status'] = $retVal;
            }
    
            array_push($result_array, $data);
        }
    
        return $result_array;
    }
    
    public function UpdateData($table, $data, $where)
    {
        $builder = $this->db->table($table);
        $builder->where($where);
        $result = $builder->update($data);
        return $result;
    }

public function get_domain_subdomain($login_id, $domain_id)
    {
        $builder = $this->db->table('faculty_subject_mapping A');

$builder->select('*');
        $builder->where('A.domain_id', $domain_id);
        $builder->where('A.login_id', $login_id);
        $builder->where('A.delete_status', 'ACTIVE');
        $builder->where('A.approval_status', 'APPROVED');
        $builder->where('A.return_status', 'NOT_RETURNED');

$query = $builder->get();
        $result = $query->getResult();

if ($result) {
            $id_array = [];
            foreach ($result as $value) {
                $ids = json_decode($value->sub_domain_id);
                foreach ($ids as $val_1) {
                    array_push($id_array, $val_1);
                }
            }

$sub_domains = implode(',', $id_array);

$sql = "SELECT sub_domain, sub_domain_id FROM sub_domains WHERE sub_domain_id IN ($sub_domains) AND delete_status='ACTIVE'";
            $domain_result = $this->db->query($sql);
            $domain_result = $domain_result->getResult();

return $domain_result;
        }
    }
}