<?php
final class DModelcatalogcategory extends Model {

    public function getCategory($category_id, $status = 1) {

        $query = $this->db->query("SELECT DISTINCT * FROM " . DB_PREFIX . "category c LEFT JOIN " . DB_PREFIX . "category_description cd ON (c.category_id = cd.category_id) LEFT JOIN " . DB_PREFIX . "category_to_store c2s ON (c.category_id = c2s.category_id) WHERE c.category_id = '" . (int)$category_id . "' AND cd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND c2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND c.status = '".$status."' ");

        return $query->row;
    }


    public function hasChildren($parent, $status = 1) {

        $query = $this->db->query("SELECT DISTINCT * FROM " . DB_PREFIX . "category c"
            . " LEFT JOIN " . DB_PREFIX . "category_to_store c2s ON (c.category_id = c2s.category_id)"
            . " WHERE c.parent_id = '" . (int)$parent . "' AND c2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND c.status = '".$status."' ");

        return count($query->row) > 0;
    }


    public function getCategoryStatus($category_id) {
        $query = $this->db->query("SELECT c.status FROM " . DB_PREFIX . "category c WHERE c.category_id = '".$category_id."' ");

        return $query->row['status'];
    }


    public function getCategorysTreeProduto($produto_id)
    {
        $return = array();

        $result = $this->db->query("SELECT
                nsub.category_id sub_category_id,
                nsub.tipo_grupo_subgrupo sub_category_type,
                nsub_d.name sub_category_name,
                ncat.category_id category_id,
                ncat.tipo_grupo_subgrupo category_type,
                ncat_d.name category_name,
                ndep.category_id dept_id,
                ndep.tipo_grupo_subgrupo dept_type,
                ndep_d.name dept_name
            FROM oc_product p
            INNER JOIN oc_category nsub ON (p.category_id  = nsub.category_id)
            INNER JOIN oc_category_description nsub_d ON (nsub_d.category_id = nsub.category_id)
            INNER JOIN oc_category ncat ON (nsub.parent_id = ncat.category_id)
            INNER JOIN oc_category_description ncat_d ON (ncat_d.category_id = ncat.category_id)
            LEFT  JOIN oc_category ndep ON (ncat.parent_id = ndep.category_id)
            LEFT  JOIN oc_category_description ndep_d ON (ndep_d.category_id = ndep.category_id)
            WHERE p.product_id = '" . (int)$produto_id . "'");

        if($result->num_rows>0){
            $result = $result->row;
            $return[$result['category_type']] = array(
                'category_id'   =>  $result['category_id'],
                'category_name' =>  $result['category_name']
            );
            $return[$result['sub_category_type']] = array(
                'category_id'   =>  $result['sub_category_id'],
                'category_name' =>  $result['sub_category_name']
            );
            if ($result['dept_id'] !== null){
                $return[$result['dept_type']] = array(
                    'category_id'   =>  $result['dept_id'],
                    'category_name' =>  $result['dept_name']
                );
            }
        }

        return $return;

    }

}