EVOLUTION-NINJA
Edit File: New_Model.php
<?php namespace App\Models; use CodeIgniter\Model; class New_Model extends Model { public function get_subdomain_courses($id) { $db = \Config\Database::connect(); $query = $db->table('courses A') ->select('*') ->where('A.domain_id', $id) ->where('A.delete_status', 'ACTIVE') ->get(); $result = $query->getResult(); $subDomainArray = []; foreach ($result as $val) { $subDomainIds = json_decode($val->sub_domain_id); foreach ($subDomainIds as $subDomainId) { $subDomainArray[] = $subDomainId; } } $query = $db->table('sub_domains A') ->select('*') ->whereIn('A.sub_domain_id', $subDomainArray) ->where('A.delete_status', 'ACTIVE') ->distinct('A.sub_domain_id') ->get(); $result = $query->getResult(); $subDomains = []; foreach ($result as $subDomain) { $subDomains[$subDomain->sub_domain_id] = $subDomain->sub_domain; } return $subDomains; } public function search_all_1($search_value) { $sql = "(SELECT domain_id, domain_name, 'category' as type FROM domains WHERE domain_name LIKE '%" . $search_value . "%' and domains.delete_status='ACTIVE' and domains.user_type_id='1') UNION (SELECT sub_domain_id, sub_domain, 'sub_category' as type FROM sub_domains INNER JOIN domains ON domains.domain_id=sub_domains.domain_id WHERE sub_domain LIKE '%" . $search_value . "%' and sub_domains.delete_status='ACTIVE' and domains.delete_status='ACTIVE' and sub_domains.user_type_id='1') UNION (SELECT course_id, course_title, 'course' as type FROM courses INNER JOIN domains ON domains.domain_id=courses.domain_id WHERE course_title LIKE '%" . $search_value . "%' and courses.delete_status='ACTIVE' and domains.delete_status='ACTIVE' and courses.user_type_id='1')"; $query = $this->db->query($sql); $domain_result = $query->getResult(); $category_array = []; $sub_cat_array = []; $course_array = []; foreach ($domain_result as $val) { if ($val->type === 'category') { $course_query = $this->db->query("Select * from courses where domain_id LIKE '%" . $val->domain_id . "%' and delete_status='ACTIVE'"); $course_result = $course_query->getResult(); if ($course_result) { $data['domain_id'] = $val->domain_id; $data['domain_name'] = $val->domain_name; $data['type'] = 'category'; array_push($category_array, $data); } } elseif ($val->type === 'sub_category') { $course_query = $this->db->query("Select * from courses where sub_domain_id LIKE '%" . $val->domain_id . "%' and delete_status='ACTIVE'"); $course_result = $course_query->getResult(); if ($course_result) { $data['domain_id'] = $val->domain_id; $data['domain_name'] = $val->domain_name; $data['type'] = 'sub_category'; array_push($sub_cat_array, $data); } } elseif ($val->type === 'course') { $domain_query = $this->db->query("Select * from domains where domain_id LIKE '%" . $val->domain_id . "%' and delete_status='ACTIVE'"); $course_result = $domain_query->getResult(); if ($course_result) { $data['domain_id'] = $val->domain_id; $data['domain_name'] = $val->domain_name; $data['type'] = 'course'; array_push($course_array, $data); } } } $final_array = array_merge($category_array, $sub_cat_array, $course_array); return $final_array; } public function get_searched_value_subdomain_id($search_bar, $search_value) { if ($search_value == 'category') { $sql = "Select * from domains A where A.domain_name LIKE '%$search_bar%' and A.delete_status='ACTIVE'"; $query = $this->db->query($sql); return $query->getRow(); } elseif ($search_value == 'sub_category') { $sql = "Select * from sub_domains A where A.sub_domain LIKE '%$search_bar%' and A.delete_status='ACTIVE'"; $query = $this->db->query($sql); return $query->getRow(); } elseif ($search_value == 'course') { $sql = "Select * from courses A where A.course_title LIKE '%$search_bar%' and A.delete_status='ACTIVE'"; $query = $this->db->query($sql); return $query->getRow(); } } public function fetchData($table) { return $this->db->table($table) ->get() ->getResult(); } protected $primaryKey = 'id'; protected $allowedFields = [ 'user_id', 'course_id', 'course_price', 'batch_ids', 'faculty_ids', 'sub_domain_ids', 'from_time', 'to_time', 'session_type', 'delete_status', 'created_at', ]; protected $returnType = 'object'; public function insert($data = NULL, bool $returnID = true) { return $this->db->table($this->table)->insert($data, $returnID); } public function insert_val($data = NULL, bool $returnID = true) { return $this->db->table('course_cart')->insert($data, $returnID); } public function number_rows($table, $where) { $builder = $this->db->table($table) ->select('*') ->where($where); $query = $builder->get(); return $query->getNumRows(); } public function get_where_result_orderby_asc($table, $where, $order_by) { $builder = $this->db->table($table); $builder->select('*'); $builder->where($where); $builder->orderBy($order_by, 'ASC'); $query = $builder->get(); return $query->getResult(); } public function getWhereRow($table, $where) { $whereClause = ''; foreach ($where as $key => $value) { $whereClause .= "$key = '$value' AND "; } $whereClause = rtrim($whereClause, ' AND '); $query = $this->db->query("SELECT * FROM $table WHERE $whereClause LIMIT 1"); return $query->getRow(); } public function customUpdate($table, $data, $where) { $builder = $this->db->table($table); $builder->where($where); $result = $builder->update($data); return $result; } public function getWhereResult($table, $where) { $builder = $this->db->table($table); $builder->select('*'); $builder->where($where); $query = $builder->get(); return $query->getResult(); } public function customInsert($table, $data) { $builder = $this->db->table($table); $builder->insert($data); return $this->db->insertID(); } public function get_user_registrations() { $this->select('*') ->from($this->table . ' A') ->join('login B', 'B.login_id = A.user_id') ->groupBy('A.user_id') ->where('A.payment_status', 'PAID') ->where('B.user_type_id', 3) ->where('A.delete_status', 'ACTIVE') ->where('B.delete_status', 'ACTIVE') ->where('A.institution_id', '0'); $query = $this->get(); return $query->getResult(); } public function get_where_result($table, $where) { $builder = $this->db->table($table); $builder->select('*'); $builder->where($where); $query = $builder->get(); return $query->getResult(); } public function get_institutes_details() { $builder = $this->db->table('institutions A'); $builder->select('*'); $builder->join('login B', 'B.institution_id = A.institution_id'); $builder->where('A.delete_status', 'ACTIVE'); $builder->where('B.delete_status', 'ACTIVE'); $builder->where('B.user_type_id', '4'); $builder->where('B.verified_status', 'VERIFIED'); $query = $builder->get()->getResult(); $resultArray = array(); foreach ($query as $val) { $data['login_id'] = $val->login_id; $data['username'] = $val->username; $data['email'] = $val->email; $data['inst_phone'] = $val->inst_phone; $data['created_at'] = date("d-m-Y H:i:s", strtotime($val->created_at)); $data['approval_status'] = $val->approval_status; $data['return_status'] = $val->return_status; $data['address'] = $val->address; $data['broadcast_type'] = $val->audience_location; if ($val->approval_status == 'APPROVED') { $retVal = ''; if ($val->return_status == 'NOT_RETURNED') { $retVal = '<b>APPROVED/</b><input type="button" value="Return" onclick="fun_return(' . $val->login_id . ')" />'; } $data['approval_status'] = $retVal; } else { $retVal = ''; if ($val->return_status == 'NOT_RETURNED') { $retVal = '<input type="button" class="approve' . $val->login_id . '" value="Approve" onclick="fun_approve(' . $val->login_id . ')" /><b>/</b><input type="button" value="Return" onclick="fun_return(' . $val->login_id . ')" />'; } else { $retVal = '<input type="button" value="Approve" class="approve' . $val->login_id . '" onclick="fun_approve(' . $val->login_id . ')" /><b>/ RETURNED</b>'; } $data['approval_status'] = $retVal; } array_push($resultArray, $data); } return $resultArray; } public function getCartDataFromDb($user_id) { $builder = $this->db->table('course_cart A'); $builder->select('A.*, B.*'); $builder->join('courses B', 'B.course_id = A.course_id'); $builder->where('A.delete_status', 'ACTIVE'); $builder->where('B.delete_status', 'ACTIVE'); $builder->where('A.user_id', $user_id); $query = $builder->get(); return $query->getResult(); } public function user_added_courses($login_id) { $db = db_connect(); $query = $db->table('courses A') ->join('user_course_mapping B', 'A.course_id = B.course_id') ->where('B.user_id', $login_id) ->where('A.delete_status', 'ACTIVE') ->where('B.payment_status', 'PAID') ->where('B.delete_status', 'ACTIVE') ->get(); $result = $query->getResult(); $result_array = []; foreach ($result as $value) { $sub_domain_id = json_decode($value->sub_domain_id); $query = $db->table('sub_domains A') ->whereIn('A.sub_domain_id', $sub_domain_id) ->where('A.delete_status', 'ACTIVE') ->get(); $res = $query->getResult(); $sub_array = []; foreach ($res as $val) { array_push($sub_array, $val->sub_domain); } $data = [ 'sub_domains' => implode(', ', $sub_array), 'mapping_id' => $value->mapping_id, 'course_price' => $value->course_price, 'course_title' => $value->course_title, 'course_image' => $value->course_image, 'institution_id' => $value->institution_id, ]; array_push($result_array, $data); } return json_encode($result_array); } public function getWhereResultOrderByAsc($table, $where, $orderBy) { $builder = $this->db->table($table); $builder->select('*'); $builder->where($where); $builder->orderBy($orderBy, 'ASC'); $query = $builder->get(); return $query->getResult(); } public function get_user_signup_details($loginId) { $db = db_connect(); $builder = $db->table('login A'); $builder->select('*'); $builder->join('user_registrations B', 'B.login_id = A.login_id'); $builder->where('A.delete_status', 'ACTIVE'); $builder->where('B.delete_status', 'ACTIVE'); $builder->where('A.verified_status', 'VERIFIED'); $builder->where('A.login_id', $loginId); $query = $builder->get(); return $query->getRow(); } public function get_where_row($where, $table) { return $this->db->table($table) ->select('*') ->where($where) ->get() ->getRow(); } public function insertData($table, $data) { $builder = $this->db->table($table); $builder->insert($data); return $builder->getInsertID(); } public function updateData($where, $table, $data) { $builder = $this->db->table($table); $builder->where($where); return $builder->update($data); } public function deleteData($table, $where) { $builder = $this->db->table('course_cart'); $builder->where($where, 'course_id'); return $builder->delete(); } protected $table = 'popular_course_clicks'; public function get_popular_course_on_clicks() { $result = $this->select('A.clicks, B.course_id, B.course_title, B.course_price, B.course_image') ->from('popular_course_clicks A') ->join('courses B', 'B.course_id = A.course_id') ->where('A.delete_status', 'ACTIVE') ->where('B.delete_status', 'ACTIVE') ->where('B.user_type_id', '1') ->orderBy('A.clicks', 'DESC') ->get() ->getResult(); return $result; } public function get_where_orderby_row($table, $where, $order_by) { return $this->db->table($table) ->select('*') ->where($where) ->orderBy($order_by) ->get() ->getRow(); } public function get_instructors_details($course_id) { $query2 = $this->db->table('courses A') ->select('A.course_id, A.sub_domain_id, A.course_title, A.course_type') ->where('A.course_id', $course_id) ->where('A.delete_status', 'ACTIVE') ->get() ->getRow(); if ($query2) { $sub_domain_id = json_decode($query2->sub_domain_id); $course_type = json_decode($query2->course_type); $type = implode(', ', $course_type); $mapping_result = $this->db->table('faculty_subject_mapping B') ->select('*') ->where('B.delete_status', 'ACTIVE') ->get() ->getResult(); $login_ids = $this->db->table('faculty_subject_mapping B') ->select('*') ->where('B.delete_status', 'ACTIVE') ->groupBy('B.login_id') ->get() ->getResult(); $mappingResultArray = []; foreach ($login_ids as $subIds) { $finSubArray = []; $subArray = []; foreach ($mapping_result as $map) { if ($map->login_id == $subIds->login_id) { $subDomainIds = json_decode($map->sub_domain_id); if (is_array($subDomainIds)) { foreach ($subDomainIds as $ids) { $subArray[] = $ids; } } } } foreach ($subArray as $sub) { $finSubArray[] = $sub; } $mappingData['login_id'] = $subIds->login_id; $mappingData['sub_domain_ids'] = $finSubArray; $mappingResultArray[] = $mappingData; } $i = 0; $resArray = []; foreach ($mappingResultArray as $value) { $mappingSubDomainId = $value['sub_domain_ids']; $subDomainIdArray = []; foreach ($mappingSubDomainId as $val) { foreach ($sub_domain_id as $id) { if ($val == $id) { $i = $i + 1; $subDomainIdArray[] = $id; } } } if ($i > 0) { $query = $this->db->table('faculty_registration C') ->select('*') ->join('faculty_available_timings D', 'C.login_id = D.faculty_id', 'left') ->join('login E', 'E.login_id = C.login_id') ->where('D.faculty_id', $value['login_id']) ->where('D.delete_status', 'ACTIVE') ->where('E.delete_status', 'ACTIVE') ->where('C.delete_status', 'ACTIVE') ->where('D.approval_status', 'APPROVED') ->where('E.approval_status', 'APPROVED') ->get(); $fullname = $query->getRow(); $availableTimings = $query->getResult(); foreach ($availableTimings as $timings) { $j = 0; $subIds = json_decode($timings->subject_id); $subIdsArray = []; foreach ($subIds as $sub) { foreach ($sub_domain_id as $subs) { if ($subs == $sub) { $j = $j + 1; $subIdsArray[] = $sub; } } } if ($j > 0) { foreach ($subIdsArray as $subId) { if ($timings->available_type != '') { if ($timings->available_type == 'timings') { $data['faculty_id'] = $timings; $courseType = json_decode($timings->course_type); if (count($courseType) == 2) { $data['course_type'] = 'Live, Recorded'; } else { $data['course_type'] = 'Live'; } $data['available_type'] = 'timings'; $data['available_timings'] = $timings; $subArray = []; $subDomainResult = $this->db->table('sub_domains A') ->select('A.sub_domain, A.sub_domain_id') ->where('A.sub_domain_id', $subId) ->where('A.delete_status', 'ACTIVE') ->get() ->getResult(); foreach ($subDomainResult as $sub) { $subArray[] = $sub->sub_domain; } $data['sub_domains'] = implode(', ', $subArray); $data['fullname'] = $fullname->fullname; $data['sub_domain_ids'] = implode(',', $subIdsArray); $resArray[] = $data; } else { $fullDayArray = []; $timeResult = json_decode($timings->course_type); foreach ($timeResult as $time) { $fullDayTime = ucfirst($time); $fullDayArray[] = $fullDayTime; $data['faculty_id'] = $timings; $data['course_type'] = $fullDayTime; $data['available_type'] = 'fullday'; $data['available_timings'] = 'Fullday'; $subArray = []; $subDomainResult = $this->db->table('sub_domains A') ->select('A.sub_domain, A.sub_domain_id') ->where('A.sub_domain_id', $subId) ->where('A.delete_status', 'ACTIVE') ->get() ->getResult(); foreach ($subDomainResult as $sub) { $subArray[] = $sub->sub_domain; } $data['sub_domains'] = implode(', ', $subArray); $data['fullname'] = $fullname->fullname; $data['sub_domain_ids'] = implode(',', $subIdsArray); $resArray[] = $data; } } } else { $fullDayArray = []; $timeResult = json_decode($timings->course_type); foreach ($timeResult as $time) { $fullDayTime = ucfirst($time); $fullDayArray[] = $fullDayTime; } $data['faculty_id'] = $timings; $data['course_type'] = implode(', ', $fullDayArray); $data['available_type'] = 'recorded'; $data['available_timings'] = implode(', ', $fullDayArray); $subArray = []; $subDomainResult = $this->db->table('sub_domains A') ->select('A.sub_domain, A.sub_domain_id') ->where('A.sub_domain_id', $subId) ->where('A.delete_status', 'ACTIVE') ->get() ->getResult(); foreach ($subDomainResult as $sub) { $subArray[] = $sub->sub_domain; } $data['sub_domains'] = implode(', ', $subArray); $data['fullname'] = $fullname->fullname; $data['sub_domain_ids'] = implode(',', $subIdsArray); $resArray[] = $data; } } } else { $data['available_timings'] = ''; $data['course_type'] = ''; $data['sub_domains'] = ''; $data['fullname'] = ''; $data['sub_domain_ids'] = ''; } } $i = 0; } } return $resArray; } else { return []; } } public function get_batch_list($course_id) { $query = $this->db->table('courses A') ->select('*') ->where('A.course_id', $course_id) ->where('A.delete_status', 'ACTIVE') ->get() ->getRow(); $sub_domain_ids = json_decode($query->sub_domain_id); $array = []; $batchesQuery = $this->db->table('batches A') ->select('*') ->join('faculty_registration B', 'A.faculty_id = B.login_id') ->join('sub_domains C', 'C.sub_domain_id = A.sub_domain_id') ->join('domains D', 'D.domain_id = C.domain_id') ->whereIn('A.sub_domain_id', $sub_domain_ids) ->where('A.batch_approval_status', 'APPROVED') ->where('A.delete_status', 'ACTIVE') ->where('B.delete_status', 'ACTIVE') ->where('C.delete_status', 'ACTIVE') ->where('D.delete_status', 'ACTIVE') ->where('A.institution_id', '0') ->get() ->getResult(); $batchArray = []; foreach ($batchesQuery as $val) { $batch_size = $val->batch_size; $userCourseMappingQuery = $this->db->table('user_course_mapping A') ->select('*') ->where('A.delete_status', 'ACTIVE') ->get() ->getResult(); $i = 0; foreach ($userCourseMappingQuery as $value) { if ($value->batch_ids != 'null') { $batch_ids = json_decode($value->batch_ids); foreach ($batch_ids as $batch_id) { if ($batch_id == $val->batch_id) { $i = $i + 1; } } } } if ($batch_size > $i) { $data['course_type'] = implode(', ', json_decode($query->course_type)); $data['batch_details'] = $val; } else { $data['course_type'] = ''; $data['batch_details'] = ''; } $array[] = $data; } $adminBatchesQuery = $this->db->table('batches A') ->select('*') ->join('faculty_registration B', 'A.faculty_id = B.login_id') ->join('sub_domains C', 'C.sub_domain_id = A.sub_domain_id') ->join('domains D', 'D.domain_id = C.domain_id') ->whereIn('A.sub_domain_id', $sub_domain_ids) ->where('A.created_by', '1') ->where('A.batch_approval_status', 'APPROVED') ->where('A.delete_status', 'ACTIVE') ->where('B.delete_status', 'ACTIVE') ->where('C.delete_status', 'ACTIVE') ->where('D.delete_status', 'ACTIVE') ->get() ->getResult(); $adminBatchArray = []; foreach ($adminBatchesQuery as $val) { $batch_size = $val->batch_size; $userCourseMappingQuery = $this->db->table('user_course_mapping A') ->select('*') ->where('A.delete_status', 'ACTIVE') ->get() ->getResult(); $i = 0; foreach ($userCourseMappingQuery as $value) { if ($value->batch_ids != 'null') { $batch_ids = json_decode($value->batch_ids); foreach ($batch_ids as $batch_id) { if ($batch_id == $val->batch_id) { $i = $i + 1; } } } } if ($batch_size >= $i) { $data['course_type'] = implode(', ', json_decode($query->course_type)); $data['batch_details'] = $val; } else { $data['course_type'] = ''; $data['batch_details'] = ''; } $adminBatchArray[] = $data; } $finalArray = array_merge($adminBatchArray, $array); return $finalArray; } public function get_feedbacks_list($course_id) { return $this->select('A.feedback, C.username, D.fullname, A.created_at') ->from('feedback A') ->join('courses B', 'B.course_id = A.course_id') ->join('login C', 'C.login_id = A.created_by') ->join('faculty_registration D', 'D.login_id = A.faculty_id') ->where('B.course_id', $course_id) ->where('A.display_status', 'DISPLAY') ->where('A.delete_status', 'ACTIVE') ->where('B.delete_status', 'ACTIVE') ->where('C.delete_status', 'ACTIVE') ->where('D.delete_status', 'ACTIVE') ->get() ->getResult(); } public function get_all_courses() { $builder = $this->db->table('domains A'); $builder->select('*'); $builder->where('A.delete_status', 'ACTIVE'); $query = $builder->get(); $categories = $query->getResult(); $array = array(); foreach ($categories as $val) { $data['domain_name'] = $val->domain_name; $data['domain_id'] = $val->domain_id; $builder = $this->db->table('courses B'); $builder->select('*'); $builder->where('B.delete_status', 'ACTIVE'); $builder->where('B.domain_id', $val->domain_id); $builder->orderBy('B.created_at', 'DESC'); $builder->where('B.user_type_id', '1'); $builder->limit(4); $query = $builder->get(); $result = $query->getResult(); $builder = $this->db->table('courses B'); $builder->select('*'); $builder->where('B.delete_status', 'ACTIVE'); $builder->where('B.domain_id', $val->domain_id); $builder->orderBy('B.created_at', 'DESC'); $builder->where('B.user_type_id', '1'); $query = $builder->get(); $course_count = $query->getResult(); $new_array = array(); foreach ($result as $value) { $new_data['course_id'] = $value->course_id; $new_data['course_title'] = $value->course_title; $new_data['course_image'] = $value->course_image; $new_data['course_price'] = $value->course_price; $sub_domains = json_decode($value->sub_domain_id); $sub_domains = implode(',', $sub_domains); $sql = "SELECT sub_domain 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(); $domain_array = array(); foreach ($domain_result as $value1) { array_push($domain_array, $value1->sub_domain); } $new_data['sub_domains'] = implode(',', $domain_array); $builder = $this->db->table('user_course_mapping C'); $builder->select('*'); $builder->where('C.delete_status', 'ACTIVE'); $builder->where('C.course_id', $value->course_id); $query = $builder->get(); $new_data['total_enrollment'] = count($query->getResult()); array_push($new_array, $new_data); } $data['course_count'] = $course_count; $data['course'] = $new_array; array_push($array, $data); } return $array; } public function get_category_courses($domain_id) { $builder = $this->db->table('domains A'); $builder->select('*'); $builder->join('courses B', 'A.domain_id = B.domain_id'); $builder->where('A.delete_status', 'ACTIVE'); $builder->where('B.delete_status', 'ACTIVE'); $builder->where('A.domain_id', $domain_id); $query = $builder->get(); $result = $query->getResult(); $new_array = array(); foreach ($result as $value) { $new_data['course_id'] = $value->course_id; $new_data['course_title'] = $value->course_title; $new_data['course_image'] = $value->course_image; $new_data['course_price'] = $value->course_price; $new_data['domain_name'] = $value->domain_name; $sub_domains = json_decode($value->sub_domain_id); $sub_domains = implode(',', $sub_domains); $sql = "SELECT sub_domain FROM sub_domains WHERE sub_domain_id IN ($sub_domains) AND delete_status='ACTIVE'"; $domain_result = $this->db->query($sql)->getResult(); $domain_array = array(); foreach ($domain_result as $value1) { array_push($domain_array, $value1->sub_domain); } $new_data['sub_domains'] = implode(',', $domain_array); $builder = $this->db->table('user_course_mapping C'); $builder->select('*'); $builder->where('C.delete_status', 'ACTIVE'); $builder->where('C.course_id', $value->course_id); $query = $builder->get(); $new_data['total_enrollment'] = count($query->getResult()); array_push($new_array, $new_data); } return $new_array; } public function get_subdomain_wise_courses($sub_domain_id) { $builder = $this->db->table('domains A'); $builder->select('*'); $builder->where('A.delete_status', 'ACTIVE'); $query = $builder->get(); $categories = $query->getResult(); $array = array(); $i = 0; foreach ($categories as $val) { $data['domain_name'] = $val->domain_name; $data['domain_id'] = $val->domain_id; $builder = $this->db->table('courses B'); $builder->select('*'); $builder->where('B.delete_status', 'ACTIVE'); $builder->where('B.domain_id', $val->domain_id); $builder->orderBy('B.created_at', 'DESC'); $query = $builder->get(); $result = $query->getResult(); $new_array = array(); foreach ($result as $value) { $sub_domains = json_decode($value->sub_domain_id); foreach ($sub_domains as $val) { if ($val == $sub_domain_id) { $data['course_count'] = $result; $i = $i + 1; $new_data['course_id'] = $value->course_id; $new_data['course_title'] = $value->course_title; $new_data['course_image'] = $value->course_image; $new_data['course_price'] = $value->course_price; $sub_domains = implode(',', $sub_domains); $sql = "SELECT sub_domain FROM sub_domains WHERE sub_domain_id IN ($sub_domains) AND delete_status='ACTIVE'"; $domain_result = $this->db->query($sql)->getResult(); $domain_array = array(); foreach ($domain_result as $value1) { array_push($domain_array, $value1->sub_domain); } $new_data['sub_domains'] = implode(',', $domain_array); $builder = $this->db->table('user_course_mapping C'); $builder->select('*'); $builder->where('C.delete_status', 'ACTIVE'); $builder->where('C.course_id', $value->course_id); $query = $builder->get(); $new_data['total_enrollment'] = count($query->getResult()); array_push($new_array, $new_data); } } } $data['course'] = $new_array; array_push($array, $data); } $result_data = array(); $result_data['i'] = $i; $result_data['result'] = $array; return $result_data; } public function get_domain_courses() { $query = $this->db->table('courses A') ->select('*') ->join('domains B', 'B.domain_id = A.domain_id') ->where('A.delete_status', 'ACTIVE') ->where('B.delete_status', 'ACTIVE') ->where('A.user_type_id', '1') ->where('B.user_type_id', '1') ->groupBy('A.domain_id') ->get(); $result = $query->getResult(); $res_array = []; foreach ($result as $values) { $sub_domain_array = []; $sub_domain_ids = []; $query = $this->db->table('courses A') ->select('*') ->where('A.domain_id', $values->domain_id) ->where('A.delete_status', 'ACTIVE') ->where('A.user_type_id', '1') ->get(); $courses_result = $query->getResult(); foreach ($courses_result as $val) { $sub_domain_ids = array_merge($sub_domain_ids, json_decode($val->sub_domain_id)); } $sub_domain_ids = array_unique($sub_domain_ids); $query = $this->db->table('sub_domains A') ->select('A.sub_domain_id') ->whereIn('A.sub_domain_id', $sub_domain_ids) ->where('A.delete_status', 'ACTIVE') ->where('A.user_type_id', '1'); $query = $query->distinct()->get(); $sub_domains_result = $query->getResult(); foreach ($sub_domains_result as $sub_domain) { $sub_domain_array[] = $sub_domain; } $data = [ 'sub_domains' => $sub_domain_array, 'domains' => $values ]; $res_array[] = $data; } return $res_array; } }