EVOLUTION-NINJA

Edit File: Nparrots_Model.php

<?php

namespace App\Models\Admin;

use CodeIgniter\Model;

class Nparrots_Model extends Model

{

protected $table = 'login';

// protected $table = 'sub_domains';

protected $primaryKey = 'id'; // Assuming 'id' is the primary key column name

protected $allowedFields = [

'domain_id',

'sub_domain',

'delete_status',

// Add other fields here if needed

];

// You can define validation rules if necessary

protected $validationRules = [

'domain_id' => 'required',

'sub_domain' => 'required',

'delete_status' => 'required'

// Define validation rules for other fields if needed

];

// You can define custom validation error messages if necessary

protected $validationMessages = [

'domain_id' => [

'required' => 'The domain ID field is required.'

'sub_domain' => [

'required' => 'The subdomain field is required.'

'delete_status' => [

'required' => 'The delete status field is required.'

]

// Define custom validation messages for other fields if needed

];

public function getWhereUser($email, $password)

{

return $this->select('*')

->from('login A')

->join('user_type B', 'B.user_type_id = A.user_type_id')

->where('A.email', $email)

->where('A.password', $password)

->where('A.delete_status', 'ACTIVE')

->where('A.verified_status', 'VERIFIED')

->where('A.user_type_id', '1')

->get()

->getRow(); // Use getRow() to return a single row

}

public function updateData($where, $table, $data)

{

$builder = $this->db->table($table);

$builder->where($where);

return $builder->update($data);

}

public function updateData1($where, $data)

{

$builder = $this->db->table('feedback');

$builder->where($where)->update($data);

return $this->db->affectedRows();

}

public function updateData2($where_login, $login_table, $data)

{

$builder = $this->db->table($login_table);

$builder->where($where_login)->update($data);

return $this->db->affectedRows();

}

public function updateCourse($table, $where, $data)

{

$builder = $this->db->table('courses'); // Use the specified table name

// Set the table name dynamically and apply the WHERE condition

$builder->where($where);

// Set the data to be updated

$builder->set($data);

// Perform the update operation

$builder->update();

// Return the number of affected rows after the update

return $this->db->affectedRows();

}

public function getUserRegistrations()

{

// Create a query builder for the 'user_course_mapping' table with alias 'A'

$builder = $this->db->table('user_course_mapping A');

// Select all columns from the 'A' table

$builder->select('*');

// Join the 'login' table with alias 'B' using the 'login_id' and 'user_id' columns

$builder->join('login B', 'B.login_id = A.user_id');

// Group the results by 'user_id'

$builder->groupBy('A.user_id');

// Apply filters to the query

$builder->where('A.payment_status', 'PAID');

$builder->where('B.user_type_id', 3);

$builder->where('A.delete_status', 'ACTIVE');

$builder->where('B.delete_status', 'ACTIVE');

$builder->where('A.institution_id', 0); // No need for quotes for integer comparison

// Execute the query and retrieve the results

$query = $builder->get();

// Return the result set

return $query->getResult();

}

public function getWhereResult($table, $where)

{

$builder = $this->db->table($table);

$builder->select('*');

$builder->where($where);

$query = $builder->get();

return $query->getResult();

}

public function get_where_result($table, $where)

{

$builder = $this->table($table);

$builder = $this->where($where);

$query = $builder->get();

echo $query->getLastQuery();

return $query->getResult();

}

public function getUserSubdomains($array)

{

$session = session();

$login_id = $session->get('institution_login_id');

$user_type_id = $session->get('institution_user_type_id');

$builder = $this->db->table('sub_domains A');

$builder->select('*');

$builder->join('domains B', 'B.domain_id = A.domain_id');

$builder->where('A.delete_status', 'ACTIVE');

$builder->where('B.delete_status', 'ACTIVE');

$builder->orderBy('B.domain_name', 'ASC');

$builder->where('A.created_by', $login_id);

$builder->where('A.user_type_id', $user_type_id);

$builder->whereNotIn('A.sub_domain_id', $array);

return $builder->get()->getResult();

}

public function getInstitutesDetails()

{

$query = $this->select('*')

->join('login B', 'B.institution_id = A.institution_id')

->from('institutions A')

->where('A.delete_status', 'ACTIVE')

->where('B.delete_status', 'ACTIVE')

->where('B.user_type_id', '4')

->where('B.verified_status', 'VERIFIED')

->get()

->getResult();

$array = [];

foreach ($query as $val) {

$data = [

'login_id' => $val->login_id,

'username' => $val->username,

'email' => $val->email,

'inst_phone' => $val->inst_phone,

'created_at' => date("d-m-Y H:i:s", strtotime($val->created_at)),

'approval_status' => $val->approval_status,

'return_status' => $val->return_status,

'address' => $val->address,

'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($array, $data);

}

return $array;

}

public function getFacultyFeedbackList($login_id)

{

$builder = $this->db->table('feedback A');

$builder->select('A.*, B.course_title, C.username');

$builder->join('courses B', 'B.course_id = A.course_id');

$builder->join('login C', 'C.login_id = A.created_by');

$builder->where('A.delete_status', 'ACTIVE');

$builder->where('A.faculty_id', $login_id);

$builder->where('B.delete_status', 'ACTIVE');

$query = $builder->get();

$results = $query->getResultArray();

$array = array();

foreach ($results as $val) {

$data['feedback_id'] = $val['feedback_id'];

$data['feedback'] = $val['feedback'];

$data['username'] = $val['username'];

$data['course_title'] = $val['course_title'];

$data['created_at'] = date("d-m-Y", strtotime($val['added_on']));

array_push($array, $data);

}

return $array;

}

// public function deleteData($table, $where)

// {

//     return $this->db->table('topics')->where($where)->delete();

// }

public function deleteData($table, $where)
    {
        // Delete data from the specified table based on the provided conditions
        $this->table = $table; // Set the table dynamically
        $this->where($where); // Apply the conditions

// Perform the delete operation
        try {
            $this->delete();
            return true; // Return true on successful deletion
        } catch (\Exception $e) {
            // Handle any exceptions or errors
            log_message('error', 'Error deleting data: ' . $e->getMessage());
            return false; // Return false on failure
        }
    }

public function getEnrolledStudents($login_id)

{

$builder = $this->db->table('faculty_subject_mapping A');

$builder->select('A.*, B.full_name, B.phone_number, C.course_title, A.course_price, C.sub_domain_id, D.email, A.created_at as added_on');

$builder->join('user_course_mapping A', 'A.user_id = A.login_id');

$builder->join('user_registrations B', 'A.user_id = B.login_id');

$builder->join('courses C', 'A.course_id = C.course_id');

$builder->join('login D', 'B.login_id = D.login_id');

$builder->where('A.enroll_status', 'ENROLLED');

$builder->where('A.payment_status', 'PAID');

$builder->where('A.delete_status', 'ACTIVE');

$builder->where('B.delete_status', 'ACTIVE');

$builder->where('C.delete_status', 'ACTIVE');

$builder->where('D.delete_status', 'ACTIVE');

$builder->where('D.verified_status', 'VERIFIED');

$query = $builder->get();

$results = $query->getResultArray();

$res_array = array();

foreach ($results as $val) {

$sub_domain_id_array = json_decode($val['sub_domain_id']);

$sub_domain = '';

if (!empty($sub_domain_id_array)) {

foreach ($sub_domain_id_array as $ids) {

$builder = $this->db->table('sub_domains D');

$builder->select('D.sub_domain');

$builder->where('D.sub_domain_id', $ids);

$builder->where('D.delete_status', 'ACTIVE');

$sub_query = $builder->get();

$sub_result = $sub_query->getRow();

if ($sub_result) {

if (empty($sub_domain)) {

$sub_domain = $sub_result->sub_domain;

} else {

$sub_domain .= ', ' . $sub_result->sub_domain;

}

$data['subjects'] = $sub_domain;

$data['user_name'] = $val['full_name'];

$data['phone_number'] = $val['phone_number'];

$type_array = array();

foreach (json_decode($val['session_type']) as $value) {

array_push($type_array, ucfirst($value));

}

$data['session_type'] = implode(', ', $type_array);

$data['course_title'] = $val['course_title'];

if ($val['institution_id'] == '0') {

$data['course_price'] = $val['course_price'];

} else {

$data['course_price'] = '';

}

$data['created_at'] = date("d-m-Y H:i:s", strtotime($val['added_on']));

$data['email'] = $val['email'];

array_push($res_array, $data);

}

return $res_array;

}

public function getWhereRow($table, $where)

{

$builder = $this->db->table($table);

$builder->select('*');

$builder->where($where);

$query = $builder->get();

return $query->getRow();

}

protected function getSubDomain($id)

{

$builder = $this->db->table('sub_domains D');

$builder->select('D.sub_domain');

$builder->join('domains E', 'D.domain_id = E.domain_id');

$builder->where('D.sub_domain_id', $id);

$query = $builder->get();

$sub_domain = $query->getRow();

if ($sub_domain) {

return $sub_domain->sub_domain;

}

return '';

}

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 getSubCategoriesList()

{

try {

$login_id = session('login_id');

$user_type_id = session('user_type_id');

$builder = $this->db->table('sub_domains A');

$builder->select('*');

$builder->join('domains B', 'B.domain_id = A.domain_id');

$builder->where('A.delete_status', 'ACTIVE');

$builder->where('B.delete_status', 'ACTIVE');

$builder->orderBy('B.domain_name', 'ASC');

$builder->whereIn('A.user_type_id', ['1', '2']);

$query = $builder->get();

if ($query->getResult()) {

return $query->getResult();

} else {

// Log the query or any error message here

log_message('error', 'No results found in getSubCategoriesList()');

return false;

}

} catch (Exception $e) { // Catch the generic Exception

// Log the exception here

log_message('error', 'Error in getSubCategoriesList(): ' . $e->getMessage());

return false;

}

public function getActiveDomains($login_id, $user_type_id)

{

return $this->where('delete_status', 'ACTIVE')

->where('created_by', $login_id)

->where('user_type_id', $user_type_id)

->orderBy('domain_name', 'ASC')

->get(); // Use 'get' instead of 'findAll'

}

public function getWhereOrderByRow($table, $where, $orderBy)

{

$builder = $this->db->table($table);

$builder->select('*');

$builder->where($where);

$builder->orderBy($orderBy, 'DESC');

return $builder->get()->getRow();

}

public function getWhereResultOrderBy($table, $where, $order_by)

{

return $this->db->table($table)->where($where)->orderBy($order_by)->get()->getResultArray();

}

public function insertData($table, $data, $returnID = false)

{

$builder = $this->db->table($table);

$builder->insertBatch($data);

if ($returnID) {

return $this->db->insertID();

}

return true; // Successfully inserted

}

public function getWhereTrainingPlans($sub_domain_id, $login_id)

{

$db = \Config\Database::connect();

$builder = $db->table('training_plans A');

$builder->select('*');

$builder->join('sub_domains B', 'B.sub_domain_id = A.sub_domain_id');

$builder->join('domains C', 'B.domain_id = C.domain_id');

$builder->where('A.delete_status', 'ACTIVE');

$builder->where('B.delete_status', 'ACTIVE');

$builder->where('C.delete_status', 'ACTIVE');

$builder->where('A.created_by', $login_id);

$builder->where('A.sub_domain_id', $sub_domain_id);

$query = $builder->get();

$training_result = $query->getResultArray();

$res_array = [];

foreach ($training_result as $value) {

$chapter_ids = json_decode($value['chapter_ids']);

$chapter_array = [];

foreach ($chapter_ids as $val) {

$builder = $db->table('syllabus_chapter B');

$builder->select('*');

$builder->where('B.delete_status', 'ACTIVE');

$builder->where('B.chapter_id', $val);

$query = $builder->get();

$row = $query->getRow();

array_push($chapter_array, $row->chapter_name);

}

$data['chapters'] = implode(', ', $chapter_array);

$data['sub_domain'] = $value['sub_domain'];

$data['session_time'] = $value['session_time'];

$data['training_plan_id'] = $value['training_plan_id'];

$data['approval_status'] = $value['approval_status'];

$data['return_status'] = $value['return_status'];

$res_array[] = $data;

}

return $res_array;

}

public function getFacultySessionDetails($training_plan_id)

{

$db = db_connect(); // Establish a database connection

$trainingPlansTable = 'training_plans';

$syllabusChapterTable = 'syllabus_chapter';

$trainingPlansBuilder = $db->table($trainingPlansTable . ' A');

$trainingPlansBuilder->select('*');

$trainingPlansBuilder->join('login B', 'B.login_id = A.created_by');

$trainingPlansBuilder->where('A.delete_status', 'ACTIVE');

$trainingPlansBuilder->where('B.delete_status', 'ACTIVE');

$trainingPlansBuilder->where('A.training_plan_id', $training_plan_id);

$trainingQuery = $trainingPlansBuilder->get();

$trainingResult = $trainingQuery->getRow();

$chapterIds = json_decode($trainingResult->chapter_ids);

$chapterArray = [];

foreach ($chapterIds as $val) {

$syllabusChapterBuilder = $db->table($syllabusChapterTable . ' B');

$syllabusChapterBuilder->select('*');

$syllabusChapterBuilder->where('B.delete_status', 'ACTIVE');

$syllabusChapterBuilder->where('B.chapter_id', $val);

$chapterQuery = $syllabusChapterBuilder->get();

$row = $chapterQuery->getRow();

array_push($chapterArray, $row->chapter_name);

}

$data['chapters'] = implode(', ', $chapterArray);

$data['username'] = $trainingResult->username;

$data['email'] = $trainingResult->email;

return $data;

}

public function getMaterialDetailsAdmin($subDomainId, $loginId)

{

$db = \Config\Database::connect();

$builder = $db->table('subject_materials A');

$builder->select('A.*, B.sub_domain, C.username, C.login_id, D.chapter_id, D.chapter_name, E.topic_name');

$builder->join('sub_domains B', 'A.sub_domain_id = B.sub_domain_id');

$builder->join('login C', 'C.login_id = A.created_by');

$builder->join('syllabus_chapter D', 'D.chapter_id = A.chapter_id');

$builder->join('topics E', 'E.topic_id = A.topic_id');

$builder->where('A.delete_status', 'ACTIVE');

$builder->where('A.sub_domain_id', $subDomainId);

$builder->where('A.created_by', $loginId);

$builder->where('C.login_id', $loginId);

$builder->where('B.delete_status', 'ACTIVE');

$builder->where('D.delete_status', 'ACTIVE');

$builder->where('E.delete_status', 'ACTIVE');

$builder->groupBy('A.chapter_id');

$query = $builder->get();

return $query->getResult();

}

public function getTopicWiseMaterials($loginId, $subDomainId, $chapterId)

{

$builder = $this->db->table('subject_materials A');

$builder->select('A.*, B.login_id, B.username, C.sub_domain_id, C.sub_domain, D.chapter_id, D.chapter_name, F.topic_name');

$builder->join('login B', 'B.login_id = A.created_by');

$builder->join('sub_domains C', 'C.sub_domain_id = A.sub_domain_id');

$builder->join('syllabus_chapter D', 'D.chapter_id = A.chapter_id');

$builder->join('topics F', 'F.topic_id = A.topic_id');

$builder->where('A.delete_status', 'ACTIVE');

$builder->where('A.chapter_id', $chapterId);

$builder->where('B.delete_status', 'ACTIVE');

$builder->where('C.delete_status', 'ACTIVE');

$builder->where('C.sub_domain_id', $subDomainId);

$builder->where('F.delete_status', 'ACTIVE');

$builder->where('A.created_by', $loginId);

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

$resultArray = [];

foreach ($query as $val) {

$data = [

'material_id' => $val->material_id,

'sub_domain_id' => $val->sub_domain_id,

'chapter_id' => $val->chapter_id,

'created_by' => $val->created_by,

'sub_domain' => $val->sub_domain,

'chapter_name' => $val->chapter_name,

'username' => $val->username,

'topic_name' => $val->topic_name,

'created_at' => date("d-m-Y H:i:s", strtotime($val->created_at)),

'approval_status' => ($val->return_status === 'RETURNED') ? 'RETURNED' : $val->approval_status,

];

$resultArray[] = $data;

}

return $resultArray;

}

public function getSubjectWiseSyllabusList($sub_domain_id)

{

$builder = $this->db->table('syllabus_chapter A');

$builder->select('A.*, B.sub_domain, C.username');

$builder->join('sub_domains B', 'A.sub_domain_id = B.sub_domain_id');

$builder->join('login C', 'A.created_by = C.login_id');

$builder->where('A.delete_status', 'ACTIVE');

$builder->where('B.delete_status', 'ACTIVE');

$builder->where('C.delete_status', 'ACTIVE');

$builder->where('A.sub_domain_id', $sub_domain_id);

$query = $builder->get();

$result = $query->getResultArray();

$syllabusArray = [];

foreach ($result as $val) {

$updatedBy = $val['updated_by'];

if (($updatedBy != 0) && ($updatedBy != '')) {

$editedBy = $this->db->table('login A')->select('*')->where('A.delete_status', 'ACTIVE')->where('A.login_id', $updatedBy)->get()->getRowArray();

$data['updated_by'] = $editedBy['username'];

} else {

$data['updated_by'] = '';

}

$data['chapter_id'] = $val['chapter_id'];

$data['chapter_names'] = $val['chapter_name'];

$data['username'] = $val['username'];

$data['created_at'] = date("d-m-Y H:i:s", strtotime($val['created_at']));

$data['syllabus_id'] = $val['syllabus_id'];

$syllabusArray[] = $data;

}

return $syllabusArray;

}

public function getTopicMaterialDetailsAdmin($sub_domain_id, $login_id, $chapter_id)

{

return $this->select('A.*, B.sub_domain, C.username, D.chapter_id, D.chapter_name, E.topic_name')

->from('subject_materials A')

->join('sub_domains B', 'A.sub_domain_id = B.sub_domain_id')

->join('login C', 'C.login_id = A.created_by')

->join('syllabus_chapter D', 'D.chapter_id = A.chapter_id')

->join('topics E', 'E.topic_id = A.topic_id')

->where('A.delete_status', 'ACTIVE')

->where('A.sub_domain_id', $sub_domain_id)

->where('A.created_by', $login_id)

->where('C.login_id', $login_id)

->where('A.chapter_id', $chapter_id)

->where('B.delete_status', 'ACTIVE')

->where('D.delete_status', 'ACTIVE')

->where('E.delete_status', 'ACTIVE')

->get()

->getResult();

}

public function getQuestionDetails($loginId, $chapterId)

{

$builder = $this->db->table('questions A');

$builder->join('syllabus_chapter B', 'B.chapter_id = A.chapter_id');

$builder->select('A.qid, B.chapter_name, A.questions, A.no_of_options, A.ans1, A.ans2, A.ans3, A.ans4, A.ans, A.explanation');

$builder->where('A.faculty_id', $loginId);

$builder->where('A.chapter_id', $chapterId);

$builder->where('A.status', 'ACTIVE');

$builder->where('B.delete_status', 'ACTIVE');

$query = $builder->get();

return $query->getResultArray();

}

public function deleteTimings($table, $where)

{

$data = [

'delete_status' => 'DELETED', // or any appropriate value

];

return $this->table($table)->updateData($where, $table, $data);

}

public function insertTimings($table, $data)

{

$this->db->table('course_available_timings')->insert($data);

return $this->db->insertID(); // If you want to return the inserted ID

}

public function getNotEnrolledFacTimings($facId, $subDomainId, $domainId)

{

$subDomainIds = json_encode($subDomainId);

$facultyId = json_encode($facId);

$builder = $this->db->table('user_course_mapping');

$builder->select('*');

$builder->like('faculty_ids', $facultyId);

$builder->like('sub_domain_ids', $subDomainIds);

$builder->where('delete_status', 'ACTIVE');

$query = $builder->get();

$res = $query->getResult();

if (!empty($res)) {

$array = [];

foreach ($res as $result) {

$userId = $result->user_id;

$facultyIds = json_decode($result->faculty_ids);

$facultyFromTime = $result->from_time;

$facultyToTime = $result->to_time;

$f = trim($facultyFromTime, '["');

$facFromTime = trim($f, '"]');

$t = trim($facultyToTime, '["');

$facToTime = trim($t, '"]');

if ($facFromTime != "" && $facToTime != "") {

$builder = $this->db->table('faculty_subject_mapping A');

$builder->join('faculty_available_timings B', 'B.faculty_id = A.login_id AND B.subject_id = A.sub_domain_id');

$builder->where('A.domain_id', $domainId);

$builder->like('B.course_type', 'live');

$builder->whereIn('B.faculty_id', $facultyIds);

$builder->like('B.subject_id', $subDomainIds);

$builder->where('B.from_time !=', $facFromTime);

$builder->where('B.to_time !=', $facToTime);

$builder->where('A.approval_status', 'APPROVED');

$builder->where('B.approval_status', 'APPROVED');

$builder->groupBy('B.from_time');

$builder->groupBy('B.to_time');

$builder->where('A.delete_status', 'ACTIVE');

$builder->where('B.delete_status', 'ACTIVE');

$query = $builder->get();

$res1 = $query->getResult();

$tempArray = [];

foreach ($res1 as $result1) {

$data['available_type'] = $result1->available_type;

$data['from_time'] = $result1->from_time;

$data['to_time'] = $result1->to_time;

$data['from_to_time'] = $data['from_time'] . ' - ' . $data['to_time'];

$tempArray[] = $data;

}

$array[] = $tempArray;

} else {

$builder = $this->db->table('faculty_subject_mapping A');

$builder->join('faculty_available_timings B', 'B.faculty_id = A.login_id AND B.subject_id = A.sub_domain_id');

$builder->where('A.domain_id', $domainId);

$builder->like('B.course_type', 'live');

$builder->whereIn('B.faculty_id', $facultyIds);

$builder->like('B.subject_id', $subDomainIds);

$builder->where('A.approval_status', 'APPROVED');

$builder->where('B.approval_status', 'APPROVED');

$builder->groupBy('B.from_time');

$builder->groupBy('B.to_time');

$builder->where('A.delete_status', 'ACTIVE');

$builder->where('B.delete_status', 'ACTIVE');

$query = $builder->get();

$res1 = $query->getResult();

$tempArray = [];

foreach ($res1 as $result1) {

$data['available_type'] = $result1->available_type;

$data['from_time'] = $result1->from_time;

$data['to_time'] = $result1->to_time;

$data['from_to_time'] = $data['from_time'] . ' - ' . $data['to_time'];

$tempArray[] = $data;

}

$array[] = $tempArray;

}

return $array;

} else {

$builder = $this->db->table('faculty_available_timings');

$builder->where('faculty_id', $facId);

$builder->like('subject_id', $subDomainId);

$builder->where('approval_status', 'APPROVED');

$builder->where('delete_status', 'ACTIVE');

$query = $builder->get();

$res = $query->getResult();

$array = [];

foreach ($res as $result1) {

$data['available_type'] = $result1->available_type;

$data['from_time'] = $result1->from_time;

$data['to_time'] = $result1->to_time;

$data['from_to_time'] = $data['from_time'] . ' - ' . $data['to_time'];

$array[] = $data;

}

return $array;

}

public function getSyllabusDetails($chapterId)

{

$loginId = session()->get('login_id');

$builder = $this->db->table('syllabus');

$builder->select('*');

$builder->join('syllabus_chapter B', "A.{$this->primaryKey} = B.syllabus_id");

$builder->where('A.delete_status', 'ACTIVE');

$builder->where('B.chapter_id', $chapterId);

$builder->where('A.created_by', $loginId);

$query = $builder->get();

$row = $query->getRow();

$result = $query->getResult();

$data['syllabus'] = $row;

$data['syllabus_chapter'] = $result;

return $data;

}

public function getWhereMaterialDetailsAdmin($material_id)

{

$builder = $this->db->table('subject_materials A')

->select('A.*, B.sub_domain')

->join('sub_domains B', 'A.sub_domain_id = B.sub_domain_id')

->where('A.delete_status', 'ACTIVE')

->where('B.delete_status', 'ACTIVE')

->where('A.material_id', $material_id)

->get();

return $builder->getRow();

}

public function getWhereBatches($batch_id)

{

$builder = $this->db->table('batches A');

$builder->select('A.*, B.login_id, B.fullname, C.sub_domain_id, C.sub_domain, D.*, E.*, A.domain_id as batch_domain_id');

$builder->join('faculty_registration B', 'B.login_id = A.faculty_id');

$builder->join('sub_domains C', 'C.sub_domain_id = A.sub_domain_id');

$builder->join('domains D', 'D.domain_id = A.domain_id');

$builder->join('courses E', 'E.course_id = A.course_id', 'left');

$builder->where('A.batch_id', $batch_id);

$builder->where('A.delete_status', 'ACTIVE');

$builder->where('B.delete_status', 'ACTIVE');

$builder->where('C.delete_status', 'ACTIVE');

$builder->where('D.delete_status', 'ACTIVE');

$query = $builder->get();

return $query->getRow();

}