<?php
class ModelToolUniversalFeed extends Model{

    public function &getProducts($data){

        $sql = "SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, 
        (SELECT price FROM ps_product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, 
        p.sort_order FROM ps_product p 
        LEFT JOIN ps_product_description pd ON (p.product_id = pd.product_id) 
        LEFT JOIN ps_manufacturer m ON (p.manufacturer_id = m.manufacturer_id) 
        WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() ";

        if (!empty($data['manufacturer'])) {
            $sql .= " AND p.manufacturer_id = '" . (int)$data['manufacturer'] . "'";
        }

        $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];

        $rows = $this->db->query($sql)->rows;

        foreach ($rows as &$row) {
            $row = array(
                'product_id' => $row['product_id'],
                'name' => $row['name'],
                'description' => $row['description'],
                'meta_description' => $row['meta_description'],
                'model' => $row['model'],
                'sku' => $row['sku'],
                'upc' => $row['upc'],
                'ean' => $row['ean'],
                'jan' => $row['jan'],
                'isbn' => $row['isbn'],
                'mpn' => $row['mpn'],
                'quantity' => $row['quantity'],
                'stock_status' => 1,
                'image' => $row['image'],
                'manufacturer' => $row['manufacturer'],
                'price' => ($row['discount'] ? $row['discount'] : $row['price']),
                'special' => $row['special'],
                'tax_class_id' => $row['tax_class_id'],
                'date_added' => $row['date_added'],
                'date_modified' => $row['date_modified'],
            );
        }

        return $rows;
    }

    public function getTotalProducts($data = array()){
        $sql = "SELECT COUNT(DISTINCT p.product_id) AS total";

        if (!empty($data['filter_category_id'])) {
            if (!empty($data['filter_sub_category'])) {
                $sql .= " FROM " . DB_PREFIX . "category_path cp LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (cp.category_id = p2c.category_id)";
            } else {
                $sql .= " FROM " . DB_PREFIX . "product_to_category p2c";
            }

            if (!empty($data['filter_filter'])) {
                $sql .= " LEFT JOIN " . DB_PREFIX . "product_filter pf ON (p2c.product_id = pf.product_id) LEFT JOIN " . DB_PREFIX . "product p ON (pf.product_id = p.product_id)";
            } else {
                $sql .= " LEFT JOIN " . DB_PREFIX . "product p ON (p2c.product_id = p.product_id)";
            }
        } else {
            $sql .= " FROM " . DB_PREFIX . "product p";
        }

        $sql .= " LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'";

        if (!empty($data['filter_category_id'])) {
            if (!empty($data['filter_sub_category'])) {
                $sql .= " AND cp.path_id = '" . (int)$data['filter_category_id'] . "'";
            } else {
                $sql .= " AND p2c.category_id = '" . (int)$data['filter_category_id'] . "'";
            }

            if (!empty($data['filter_filter'])) {
                $implode = array();

                $filters = explode(',', $data['filter_filter']);

                foreach ($filters as $filter_id) {
                    $implode[] = (int)$filter_id;
                }

                $sql .= " AND pf.filter_id IN (" . implode(',', $implode) . ")";
            }
        }

        if (!empty($data['filter_name']) || !empty($data['filter_tag'])) {
            $sql .= " AND (";

            if (!empty($data['filter_name'])) {
                $implode = array();

                $words = explode(' ', trim(preg_replace('/\s+/', ' ', $data['filter_name'])));

                foreach ($words as $word) {
                    $implode[] = "pd.name LIKE '%" . $this->db->escape($word) . "%'";
                }

                if ($implode) {
                    $sql .= " " . implode(" AND ", $implode) . "";
                }

                if (!empty($data['filter_description'])) {
                    $sql .= " OR pd.description LIKE '%" . $this->db->escape($data['filter_name']) . "%'";
                }
            }

            if (!empty($data['filter_name']) && !empty($data['filter_tag'])) {
                $sql .= " OR ";
            }

            if (!empty($data['filter_tag'])) {
                $sql .= "pd.tag LIKE '%" . $this->db->escape(utf8_strtolower($data['filter_tag'])) . "%'";
            }

            if (!empty($data['filter_name'])) {
                $sql .= " OR LCASE(p.model) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
                $sql .= " OR LCASE(p.sku) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
                $sql .= " OR LCASE(p.upc) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
                $sql .= " OR LCASE(p.ean) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
                $sql .= " OR LCASE(p.jan) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
                $sql .= " OR LCASE(p.isbn) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
                $sql .= " OR LCASE(p.mpn) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
            }

            $sql .= ")";
        }

        if (!empty($data['filter_manufacturer_id'])) {
            $sql .= " AND p.manufacturer_id = '" . (int)$data['filter_manufacturer_id'] . "'";
        }

        $query = $this->db->query($sql);

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

    public function getProductImages($product_id)
    {
        $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_image WHERE product_id = '" . (int)$product_id . "' ORDER BY sort_order ASC");

        return $query->rows;
    }

    public function getProductAttributes($product_id)
    {
        $product_attribute_data = array();

        $product_attribute_query = $this->db->query("SELECT pa.attribute_id FROM " . DB_PREFIX . "product_attribute pa WHERE pa.product_id = '" . (int)$product_id . "' GROUP BY pa.attribute_id");

        foreach ($product_attribute_query->rows as $product_attribute) {
            $product_attribute_description_data = array();

            $product_attribute_description_query = $this->db->query(
                "SELECT pa.text, pa.language_id, ad.name, agd.name as 'group'
          FROM " . DB_PREFIX . "product_attribute pa
           LEFT JOIN " . DB_PREFIX . "attribute a ON (pa.attribute_id = a.attribute_id)
           LEFT JOIN " . DB_PREFIX . "attribute_description ad ON (pa.attribute_id = ad.attribute_id AND pa.language_id = ad.language_id)
           LEFT JOIN " . DB_PREFIX . "attribute_group_description agd ON (a.attribute_group_id = agd.attribute_group_id AND pa.language_id = agd.language_id)
          WHERE pa.product_id = '" . (int)$product_id . "'
           AND pa.attribute_id = '" . (int)$product_attribute['attribute_id'] . "'");

            foreach ($product_attribute_description_query->rows as $product_attribute_description) {
                $product_attribute_description_data[$product_attribute_description['language_id']] = array(
                    'group' => $product_attribute_description['group'],
                    'attribute' => $product_attribute_description['name'],
                    'value' => $product_attribute_description['text'],
                );
            }

            $product_attribute_data[] = $product_attribute_description_data;
        }


        $res = array();

        // get formatted string for CSV, take only default language
        foreach ($product_attribute_data as $langs) {
            foreach ($langs as $lang => $item) {
                if ($lang != $this->config->get('config_language_id')) continue;

                $res[] = array(
                    'group' => $item['group'],
                    'name' => $item['attribute'],
                    'value' => $item['value'],
                );
            }
        }

        return $res;
    }

    public function getProductAttributes_($product_id)
    {
        $product_attribute_group_data = array();

        $product_attribute_group_query = $this->db->query("SELECT ag.attribute_group_id, agd.name FROM " . DB_PREFIX . "product_attribute pa LEFT JOIN " . DB_PREFIX . "attribute a ON (pa.attribute_id = a.attribute_id) LEFT JOIN " . DB_PREFIX . "attribute_group ag ON (a.attribute_group_id = ag.attribute_group_id) LEFT JOIN " . DB_PREFIX . "attribute_group_description agd ON (ag.attribute_group_id = agd.attribute_group_id) WHERE pa.product_id = '" . (int)$product_id . "' AND agd.language_id = '" . (int)$this->config->get('config_language_id') . "' GROUP BY ag.attribute_group_id ORDER BY ag.sort_order, agd.name");

        foreach ($product_attribute_group_query->rows as $product_attribute_group) {
            $product_attribute_data = array();

            $product_attribute_query = $this->db->query("SELECT a.attribute_id, ad.name, pa.text FROM " . DB_PREFIX . "product_attribute pa LEFT JOIN " . DB_PREFIX . "attribute a ON (pa.attribute_id = a.attribute_id) LEFT JOIN " . DB_PREFIX . "attribute_description ad ON (a.attribute_id = ad.attribute_id) WHERE pa.product_id = '" . (int)$product_id . "' AND a.attribute_group_id = '" . (int)$product_attribute_group['attribute_group_id'] . "' AND ad.language_id = '" . (int)$this->config->get('config_language_id') . "' AND pa.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY a.sort_order, ad.name");

            foreach ($product_attribute_query->rows as $product_attribute) {
                $product_attribute_data[] = array(
                    'attribute_id' => $product_attribute['attribute_id'],
                    'name' => $product_attribute['name'],
                    'text' => $product_attribute['text']
                );
            }

            $product_attribute_group_data[] = array(
                'attribute_group_id' => $product_attribute_group['attribute_group_id'],
                'name' => $product_attribute_group['name'],
                'attribute' => $product_attribute_data
            );
        }

        return $product_attribute_group_data;
    }

    public function getFullPath($product_id)
    {
        $path = array();
        $categories = $this->db->query("SELECT c.category_id, c.parent_id, cd.name AS current_name, cdp.name FROM " . DB_PREFIX . "product_to_category p2c LEFT JOIN " . DB_PREFIX . "category c ON (p2c.category_id = c.category_id) LEFT JOIN " . DB_PREFIX . "category_description cd ON (c.category_id = cd.category_id) AND cd.language_id = " . $this->config->get('config_language_id') . " LEFT JOIN " . DB_PREFIX . "category_description cdp ON (c.parent_id = cdp.category_id) AND cdp.language_id = " . $this->config->get('config_language_id') . " WHERE product_id = '" . (int)$product_id . "'")->rows;

        foreach ($categories as $key => $category) {
            $path[$key] = '';
            $title[$key] = '';
            if (!$category) continue;
            $path[$key] = $category['category_id'];
            $title[$key] = $category['current_name'];

            while ($category['parent_id']) {
                $path[$key] = $category['parent_id'] . '_' . $path[$key];
                $title[$key] = $category['name'] . ' &gt;&gt; ' . $title[$key];
                $category = $this->db->query("SELECT c.category_id, c.parent_id, cd.name FROM " . DB_PREFIX . "category c LEFT JOIN " . DB_PREFIX . "category_description cd ON (c.parent_id = cd.category_id) AND cd.language_id = " . $this->config->get('config_language_id') . " WHERE c.category_id = '" . $category['parent_id'] . "'")->row;
            }

            $banned_cats = $this->config->get('full_product_path_categories');

            if (count($banned_cats) && (count($categories) > 1)) {
                //if(preg_match('#[_=](\d+)&$#', $path[$key], $cat))
                if (preg_match('#[_=](\d+)$#', $path[$key], $cat)) {
                    if (in_array($cat[1], $banned_cats))
                        unset($path[$key]);
                }
            }
        }

        if (!count($path)) return null;

        // which one is the largest ?
        $whichone = array_map('strlen', $path);
        asort($whichone);
        $whichone = array_keys($whichone);

        if (true) { //largest
            $whichone = array_pop($whichone);
        } else {
            $whichone = array_shift($whichone);
        }

        return array('path' => $path[$whichone], 'title' => $title[$whichone]);
    }

    public function write_csv($fh, array $fields, $delimiter = ',', $enclosure = '"', $mysql_null = false)
    {
        $delimiter_esc = preg_quote($delimiter, '/');
        $enclosure_esc = preg_quote($enclosure, '/');

        $output = array();
        foreach ($fields as $field) {
            if ($field === null && $mysql_null) {
                $output[] = 'NULL';
                continue;
            }

            $output[] = preg_match("/(?:${delimiter_esc}|${enclosure_esc}|\s)/", $field) ? (
                $enclosure . str_replace($enclosure, $enclosure . $enclosure, $field) . $enclosure
            ) : $field;
        }

        fwrite($fh, join($delimiter, $output) . "\n");
    }

    public function sanitize($string)
    {
        $string = html_entity_decode($string, ENT_QUOTES, 'UTF-8');
        $string = strip_tags($string);
        $string = htmlentities($string, ENT_QUOTES, 'UTF-8', 0);

        $string = str_replace("&nbsp;", ' ', $string);
        $string = str_replace("\t", '', $string);
        $string = str_replace(PHP_EOL . PHP_EOL, ' ', $string);

        return $string;
    }

    public function truncate($string, $limit, $break = '.', $pad = '...')
    {
        if (strlen($string) <= $limit) return $string;

        if (false !== ($breakpoint = strpos($string, $break, $limit))) {
            if ($breakpoint < strlen($string) - 1) {
                $string = substr($string, 0, $breakpoint) . $pad;
            }
        }

        $string = preg_replace("/ +/", " ", $string);
        $string = preg_replace("/^ +/", "", $string);

        return $string;
    }
}