<?php

class Common_Model extends CI_Model
{
    public function __construct()
    {
        parent::__construct();
        $this->load->database();
    }

    public function loadBanners()
    {
        $banners=$this->db->query("select * from banners WHERE status = 1 ORDER by position ASC");
        return $banners->result();
    }


    public function loadLocations($province=null)
    {
        if($province==null) {
            $result = $this->db->query("select * from locations");
        }
        else if($province>15)
        {
            $result = $this->db->query("select locations.*,province.province from locations
JOIN province ON (province.id=locations.province_id) 
 WHERE locations.id='$province'
");
        }
        else {
            $result = $this->db->query("select locations.*,province.province from locations
JOIN province ON (province.id=locations.province_id) 
 WHERE locations.province_id='$province'
");
        }
            return $result->result();
    }


    public function loadPrvinces()
    {
        $result=$this->db->query("select * from province where isActive=1 ORDER by province ASC");
        
        return $result->result();
    }
   
   
   public function loadDefaultPrvinces()
   {
       
       $query = $this->db->get_where('province', array('isDefault' => 1,'isActive'=>1));
       
       return $query->row();
   }


    public function loadPackages($province_id,$limit='')
    {
        $limit_string="";
        if(!empty($limit)) {
            $limit_string="LIMIT ".$limit;
        }
        
        $result=$this->db->query("select packages.*,categories.category from packages
 JOIN categories ON (categories.id=packages.category_id)
 where province_id='$province_id' ORDER BY categories.id ASC,packages.id ASC ".$limit_string);
 
        //return $this->db->last_query();
        return $result->result();
    }


    public function loadBiggerPackages($province_id, $catInArr = [])
    {
        $this->db->select('*')->from('packages');

        // Filter by category IDs if array is passed
        if (!empty($catInArr) && is_array($catInArr)) {
            $this->db->where_in('category_id', $catInArr);
        }

        $this->db->where('province_id', $province_id)->where('display_package', 1);

        $query = $this->db->get();
        $records = $query->result();

        // Attach packages to each category
        foreach ($records as &$row) {
            $row->category = $this->getCategory($row->category_id);
        }

        return $records;
    }

    public function getCategory($category_id)
    {
        $result=$this->db->query("select * from categories where id='$category_id'");
        return $result->row(1);
    }


    public function loadSchedule($province_id, $month = null,$syear=null)
    {
        $year=date('Y');

        if ($month > 0) {

            $q = "select schedule.* from schedule
    JOIN schedule_province ON (schedule_province.schedule_id=schedule.id)
    where schedule_province.province_id='$province_id'
    AND schedule.month = '$month' AND schedule.status='1' AND schedule.year='$syear'  GROUP BY schedule.id 
    order BY schedule.month ASC,schedule.position ASC";

        } else {
            $currentDate = (int)date("m");
            $q = "select schedule.* from schedule
        JOIN schedule_province ON (schedule_province.schedule_id=schedule.id)
        where schedule_province.province_id='$province_id' AND schedule.month >= '$currentDate' AND                 schedule.status='1' AND schedule.year='$year' GROUP BY schedule.id 
        order BY schedule.month ASC,schedule.position ASC";

        }

        $result = $this->db->query($q);
        return $result->result();


    }


    public function loadNextYearSchedule($province_id, $month = null)
    {
        /* $year=date('Y');

        if ($month > 0 ) {

            $q = "select schedule.* from schedule
    JOIN schedule_province ON (schedule_province.schedule_id=schedule.id)
    where schedule_province.province_id='$province_id'
    AND schedule.month = '$month' AND schedule.status='1' AND schedule.year='$year'  GROUP BY schedule.id 
    order BY schedule.month ASC,schedule.position ASC";

        } else {
            $currentDate = 1;
            $q = "select schedule.* from schedule
        JOIN schedule_province ON (schedule_province.schedule_id=schedule.id)
        where schedule_province.province_id='$province_id' AND schedule.month >= '$currentDate' AND 
                        schedule.status='1' AND schedule.year='2021' GROUP BY schedule.id 
        order BY schedule.month ASC,schedule.position ASC";

        }

        $result = $this->db->query($q);
        
        //return $this->db->last_query();
        
        return $result->result(); */
        
        /*changes made 27-12-2021 */
         $year=date('Y');

        if ($month > 0 ) {

            $q = "select schedule.* from schedule
    JOIN schedule_province ON (schedule_province.schedule_id=schedule.id)
    where schedule_province.province_id='$province_id'
    AND schedule.month = '$month' AND schedule.status='1' AND schedule.year='$year'  GROUP BY schedule.id 
    order BY schedule.month ASC,schedule.position ASC";

        } else {
            $currentDate = 1;
            $q = "select schedule.* from schedule
        JOIN schedule_province ON (schedule_province.schedule_id=schedule.id)
        where schedule_province.province_id='$province_id' AND schedule.month >= '$currentDate' AND 
                        schedule.status='1' AND schedule.year=".($year+1)." GROUP BY schedule.id 
        order BY schedule.month ASC,schedule.position ASC";

        }

        $result = $this->db->query($q);
        return $result->result();

        /*end changes */
    }




    public function loadHomepageSchedule($province_id)
    {
        $currentDate = (int)date("m");
        $nextMonth=(int)date("m",strtotime("+1 MONTH"));
        $q = "select schedule.* from schedule
        JOIN schedule_province ON (schedule_province.schedule_id=schedule.id)
        where schedule_province.province_id='$province_id' AND  schedule.status='1' 
        AND (schedule.month >= '$currentDate' OR schedule.month='$nextMonth')
        GROUP BY schedule.id 
        ORDER BY schedule.month ASC,schedule.position ASC LIMIT 6";
        $result = $this->db->query($q);
        return $result->result();
    }





    public function loadScheduleMonths($province_id)
    {
        $year=date('Y');
        $nextYear=(int) (date('Y'))+1;
        $currentData=(int)date("m");
       
        /* $result=$this->db->query("select schedule.* from schedule
        JOIN schedule_province ON (schedule_province.schedule_id=schedule.id)
        where schedule_province.province_id='$province_id'
 AND schedule.month >= '$currentData' AND schedule.status='1' AND schedule.year='$year' GROUP BY schedule.month
 order BY schedule.month,schedule.id DESC"); */
 
 
        $result=$this->db->query("select schedule.month,schedule.year from schedule
        JOIN schedule_province ON (schedule_province.schedule_id=schedule.id)
        where schedule_province.province_id='$province_id'
        AND schedule.month >= '$currentData' AND schedule.status='1' AND schedule.year='$year' GROUP BY schedule.month,schedule.year
        order BY schedule.month DESC");
 
 
        //return $this->db->last_query();
 
        return $result->result();
    }


    public function loadFaq()
    {
        $result=$this->db->query("select * from faqs");
        return $result->result();
    }

    public function loadABoutUs()
    {
        $result=$this->db->query("select description from about_us where id='1'");
        return $result->row(1);
    }

    public function loadPackageById($packageId)
    {
        $result=$this->db->query("select * from packages where id='$packageId'");
        return $result->row(1);
    }

    public function checkCertification($filterArr) {
        $this->db->select('certification.*, certification_status.status as cStatus');
        $this->db->from('certification');
        $this->db->join('certification_status', 'certification_status.id = certification.status');
        $province_id = (isset($filterArr['province_id']) && $filterArr['province_id'] > 0) ? $filterArr['province_id'] : 0;
        if($province_id > 0){
            $this->db->where('certification.province_id', $province_id);
        }
        $certification_id = (isset($filterArr['certification_id']) && $filterArr['certification_id'] > 0) ? $filterArr['certification_id'] : 0;
        if($certification_id > 0){
            $this->db->where('certification.certification_id', $certification_id);
        }
        $firstname = ($filterArr['firstname'] != '') ? $filterArr['firstname'] : '';
        $lastname = ($filterArr['lastname'] != '') ? $filterArr['lastname'] : '';
        if($firstname != ""){
            $this->db->group_start(); // opens a bracket (
                $this->db->like('certification.firstname', $firstname);
                $this->db->or_like('certification.firstname', $lastname);
            $this->db->group_end(); // closes the bracket )
        }
        
        $query = $this->db->get();

        // Print the final executed query
        // echo "<pre>Executed Query:\n" . $this->db->last_query() . "</pre>";

        // return $query->row(); // returns a single row (object) or use ->row_array() for array
        return $query->result(); // Return all matched rows
    }



    public function loadEmailer($emailerId)
    {
        $result=$this->db->query("select * from emailer where id='$emailerId'");
        return $result->row(1);
    }


    public function insertData($tableName,$data=array())
    {
        $this->db->insert($tableName,$data);
        return true;
    }

    public function updateData($tableName, $data = array(), $where = array())
    {
        if (!empty($where)) {
            $this->db->where($where);
        }
        $this->db->update($tableName, $data);
        return true;
    }

    public function insertDataReturnId($tableName, $data = array())
    {
        $this->db->insert($tableName, $data);
        return $this->db->insert_id(); // Returns the last inserted ID
    }


 public function loadLocationEmail($locationid)
    {
        $result=$this->db->query("select email from locations where id='$locationid'");
        return $result->row(1);
    }


    public function loadScheduleCms($locationId)
    {
        $result=$this->db->query("select * from schedule_cms where location='$locationId'");
        return $result->row(1);
    }
    
    public function loadUpcomingcourses($provinceId){
        
        $result=$this->db->query("select * from trn_upcoming_courses where province_id='$provinceId'");
        return $result->result_array();
    }

    
      public function getProvincename($provinceId)
    {
        $result=$this->db->query("select province from province where id='$provinceId'");
        return $result->row();
    }
    

     public function getProvinceId($provinceName)
    {
        $result=$this->db->query("select id from province where province='$provinceName'");
        return $result->row_array();
    }

    public function getRegistrationById($dataId)
    {
        $result=$this->db->query("select * from registrations where id='$dataId'");
        return $result->row();
    }
    
    /**
     * Check and allow only phone codes 
     * return boolean as true or false
     */
    public function checkAllowedPhoneNumberCode($requestPhoneNumber)
    {
        // Clean the input phone number and extract the first three digits
        $cleanedPhoneNumber = preg_replace('/[^0-9]/', '', $requestPhoneNumber);
        $phoneAreaCode = substr($cleanedPhoneNumber, 0, 3);
    
        $ontario = [905, 289, 365, 519, 226, 548, 705, 249, 613, 343, 807, 416, 647, 437];
    
        $allowedPhoneCodeArr = $ontario;
    
        // Remove duplicate values
        $allowedPhoneCodeArr = array_unique($allowedPhoneCodeArr);
    
        // Sort the array
        sort($allowedPhoneCodeArr);
    
        if (in_array((int)$phoneAreaCode, $allowedPhoneCodeArr)) {
            return true;
        }
    
        return false;
    }

}