<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class search_model extends CI_Model {

    function __construct() {
        parent::__construct();
    }

    /* This function get all search in database sort by order asc. */

    function get_new_one($id) {

        $this->db->where('fa_id', $id);

        $result = $this->db->get('d_search');

        return $result->row();
    }

    //////////////frontend//////////////////////////////////////////////////////////

    function show_new($id) {

        $result = $this->db->query("SELECT * , COUNT( d_comments_search.cn_new_id ) as count

                        FROM d_search

                        left JOIN d_comments_search ON d_search.fa_id = d_comments_search.cn_new_id and d_search.ne_hide='1'

                        inner join d_search_category on d_search_category.nc_id = d_search.ne_category_id and d_search_category.nc_hide= '1'

                        and d_search.fa_id= $id group by d_search.fa_id");

        return $result->row();
    }

    function generate_results($keyword, $row = 0) {

        $result1 = $this->db->query("SELECT * FROM d_family where (fa_name like '%$keyword%' or fa_product_desc like '%$keyword%') and ne_hide = '1'");
        $result2 = $this->db->query("SELECT * FROM d_partitions where (par_title like '%$keyword%' or par_topic like '%$keyword%') and par_hide = '1'");
        $results = array_merge($result1->result_array(), $result2->result_array());

        return $results;
    }

    // get total number of users
    function getNumUsersAdmin() {
        $fa_name = $this->session->userdata('fa_name');
        $pt_id = $this->session->userdata('pt_id');
        $ar_id = $this->session->userdata('ar_id');
        $ct_id = $this->session->userdata('ct_id');
        $fa_product_desc = $this->session->userdata('fa_product_desc');
        $conditions = array();

        if ($fa_name) {
            $conditions[] = 'fa_name LIKE "%' . $fa_name . '%"';
        }
        if ($pt_id) {
            $conditions[] = 'pt_id = "' . $pt_id . '"';
        }
        if ($ar_id) {
            $conditions[] = 'ar_id = "' . $ar_id . '"';
        }
        if ($ct_id) {
            $conditions[] = 'ct_id = "' . $ct_id . '"';
        }
        if ($fa_product_desc) {
            $conditions[] = 'fa_product_desc LIKE "%' . $fa_product_desc . '%"';
        }



        $sqlStatement = 'CREATE VIEW paging  AS SELECT
                    d_city_category.ct_name,
                    d_area_category.ar_name,
                    d_product_type_category.pt_name,
                    d_family.*
                  FROM  d_family
                    LEFT JOIN d_city_category
                      ON d_city_category.ct_id = d_family.fa_city_id
                    LEFT JOIN d_area_category
                      ON d_area_category.ar_id = d_family.fa_area_id
                    INNER JOIN d_product_type_category
                      ON d_product_type_category.pt_id = d_family.fa_product_type_id WHERE ' . implode(' AND ', $conditions)
        ;
        $result = $this->db->query($sqlStatement);

        return $result->num_rows();
    }

    function drop_view() {
        $this->db->query('DROP VIEW IF EXISTS paging');
    }

    function create_view() {
        $fa_name = $this->input->post('fa_name');
        $pt_id = $this->input->post('parent_id');
        $ar_id = $this->input->post('ar_id');
        $ct_id = $this->input->post('ct_id');
        $fa_product_desc = $this->input->post('fa_product_desc');
        $conditions = array();

        if ($fa_name) {
            $conditions[] = 'fa_name LIKE "%' . $fa_name . '%"';
        }
        if ($pt_id) {
            $conditions[] = 'pt_id = "' . $pt_id . '"';
        }
        if ($ar_id) {
            $conditions[] = 'ar_id = "' . $ar_id . '"';
        }
        if ($ct_id) {
            $conditions[] = 'ct_id = "' . $ct_id . '"';
        }
        if ($fa_product_desc) {
            $conditions[] = 'fa_product_desc LIKE "%' . $fa_product_desc . '%"';
        }



        $sqlStatement = 'CREATE VIEW paging  as SELECT
                    d_city_category.ct_name,
                    d_area_category.ar_name,
                    d_product_type_category.pt_name,
                    d_family.*
                  FROM  d_family
                    LEFT JOIN d_city_category
                      ON d_city_category.ct_id = d_family.fa_city_id
                    LEFT JOIN d_area_category
                      ON d_area_category.ar_id = d_family.fa_area_id
                    INNER JOIN d_product_type_category
                      ON d_product_type_category.pt_id = d_family.fa_product_type_id WHERE ' . implode(' AND ', $conditions)
        ;
        $result = $this->db->query($sqlStatement);

        return $result;
    }

    function create_view_android() {
        $fa_name = $this->input->post('fa_name');
        $pt_id = $this->input->post('parent_id');
        $ar_id = $this->input->post('ar_id');
        $ct_id = $this->input->post('ct_id');
        $fa_product_desc = $this->input->post('fa_product_desc');
        $conditions = array();

        if ($fa_name) {
            $conditions[] = 'fa_name LIKE "%' . $fa_name . '%"';
        }
        if ($pt_id) {
            $conditions[] = 'pt_id = "' . $pt_id . '"';
        }
        if ($ar_id) {
            $conditions[] = 'ar_id = "' . $ar_id . '"';
        }
        if ($ct_id) {
            $conditions[] = 'ct_id = "' . $ct_id . '"';
        }
        if ($fa_product_desc) {
            $conditions[] = 'fa_product_desc LIKE "%' . $fa_product_desc . '%"';
        }



        $sqlStatement = 'CREATE VIEW paging  as SELECT
                    d_city_category.ct_name,
                    d_area_category.ar_name,
                    d_product_type_category.pt_name,d_product_type_category.pt_img,
                    d_photos.ph_url,
                    d_photos.ph_name,
                    d_family.*
                  FROM  d_family
                    LEFT JOIN d_photos
                      ON d_family.fa_id = d_photos.ph_dept_photos_id
                    LEFT JOIN d_city_category
                      ON d_city_category.ct_id = d_family.fa_city_id
                    LEFT JOIN d_area_category
                      ON d_area_category.ar_id = d_family.fa_area_id
                    INNER JOIN d_product_type_category
                      ON d_product_type_category.pt_id = d_family.fa_product_type_id WHERE ' . implode(' AND ', $conditions) . 'ORDER BY RAND()  limit 5';
        $result = $this->db->query($sqlStatement);

        return $result;
    }

    // get view records
    function search_view($row) {
        $sqlStatement = "SELECT * from paging limit $row,10";
        $result = $this->db->query($sqlStatement);
        return $result->result_array();
    }

    // get view records
    function search_view_json() {
        $sqlStatement = "SELECT * from paging";
        $result = $this->db->query($sqlStatement);
        return $result->result_array();
    }

    // get total number in views
    function getNumviews() {
        $sqlStatement = 'SELECT * FROM paging';
        $result = $this->db->query($sqlStatement);
        return $result->num_rows();
    }

    //////////////////////////////////end paging///////////////////
}
