<?php

/**
 * Store Model
 *
 * PHP version 5.6
 *
 * @category  Opencart
 * @package   Opencart
 * @author    Mukesh Pradhan <mukeshp@riaxe.com>
 * @copyright 2019-2020 Riaxe Systems
 * @license   http://www.php.net/license/3_0.txt  PHP License 3.0
 * @link      http://inkxe-v10.inkxe.io/xetool/admin
 */

namespace ComponentStoreSpace\Models;

/**
 * Opencart Model Class
 *
 * @category Opencart
 * @package  Opencart
 * @author   Mukesh Pradhan <mukeshp@riaxe.com>
 * @license  http://www.gnu.org/copyleft/gpl.html GNU General Public License
 * @link     http://inkxe-v10.inkxe.io/xetool/admin
 */
class Product
{
    /**
     * Initialize Constructor
     */
    public function __construct($con, $store, $color, $size)
    {
        $this->con = $con;
        $this->store = $store;
        $this->color = $color;
        $this->size = $size;
    }

    public function getStoreProductVariant($filter)
    {
        $resopnse = [];
        $productId = $filter['product_id'];
        $sql = "SELECT option_value_id FROM " . DB_PREFIX . "product_option_value WHERE product_id = ? AND product_option_value_id = ?  LIMIT 1";
        $params = array();
        $params[] = 'ii';
        $params[] = &$productId;
        $params[] = &$filter['product_option_value_id'];
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $query = $stmt->get_result();
        $row = mysqli_fetch_array($query);
        $optionValueId = $row['option_value_id'];

        $sqlVariant = "SELECT product_id FROM " . DB_PREFIX . "product_variant WHERE variant_id = ? AND product_id IN(SELECT product_id FROM " . DB_PREFIX . "product_option_value WHERE option_value_id=" . $optionValueId . " and product_id !=" . $productId . ")  LIMIT 1";
        $params = array();
        $params[] = 'i';
        $params[] = &$productId;
        $stmts = $this->con->prepare($sqlVariant);
        call_user_func_array([$stmts, 'bind_param'], $params);
        $stmts->execute();
        $query = $stmts->get_result();
        $rows = mysqli_fetch_array($query);
        if (!empty($rows)) {
            $resopnse['variant_id'] = $rows['product_id'];
        } else {
            $resopnse['variant_id'] = $productId;
        }
        return $resopnse;
    }

    /**
     * Fetch Categories
     *
     * @author mukeshp@riaxe.com
     * @date   22 Apr 2020
     * @return array of categories with sub categories
     */
    public function getCategories($filter)
    {
        $store = $this->store;
        $categories = array();
        $sql = "SELECT c.category_id, cd.name, c.parent_id FROM " . DB_PREFIX . "category c INNER JOIN " . DB_PREFIX . "category_description cd ON (cd.category_id = c.category_id) INNER JOIN `" . DB_PREFIX . "category_to_store` cts ON (c.category_id = cts.category_id) WHERE cts.store_id = ? ORDER BY c.category_id DESC";
        $params = array();
        $params[] = 's';
        $params[] = &$store;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $query = $stmt->get_result();
        $i = 0;
        while ($row = mysqli_fetch_array($query)) {
            $categories[$i]['id'] = $row['category_id'];
            $categories[$i]['name'] = $row['name'];
            $categories[$i]['parent_id'] = $row['parent_id'];
            $i++;
        }
        return $categories;
    }

    /**
     * Fetch Products
     *
     * @author mukeshp@riaxe.com
     * @date   22 April 2020
     * @return array of products
     */
    public function getProducts($data)
    {
        $products = array();
        $sql = "SELECT p.product_id,p.sku,p.image as thumbnail,pd.name, pd.description, p.price, p.quantity FROM " . DB_PREFIX . "product p ";
        $sql .= "INNER JOIN " . DB_PREFIX . "product_description pd ON(p.product_id = pd.product_id) ";
        $sql .= "INNER JOIN " . DB_PREFIX . "product_to_store pts ON (p.product_id = pts.product_id) WHERE p.product_id != '' AND p.is_variant='0' AND pts.store_id = ? ";
        $params = array();
        $params[0] = 's';
        $params[] = &$this->store;
        // For catalog
        $option_id = $this->getOptionIdByName("is_catalog");

        if (!(isset($data->fetch) && $data->fetch == 'all')) {
            // For only Catalog products
            if (isset($data->is_catalog) && $data->is_catalog == '1') {
                $sql .= "  AND p.product_id IN(SELECT po.product_id FROM " . DB_PREFIX . "product_option po WHERE po.option_id = ? and po.value=1)";
                $params[0] .= 's';
                $params[] = &$option_id;
            } else {
                $sql .= "  AND p.product_id NOT IN(SELECT po.product_id FROM " . DB_PREFIX . "product_option po WHERE po.option_id = ? and po.value=1)";
                $params[0] .= 's';
                $params[] = &$option_id;
            }
            // For only decorated products
            $refid_option_id = $this->getOptionIdByName("refid");
            if (isset($data->is_customize) && $data->is_customize == '1') {
                $sql .= "  AND p.product_id IN(SELECT po.product_id FROM " . DB_PREFIX . "product_option po WHERE po.option_id = ? and po.value!='')";
                $params[0] .= 's';
                $params[] = &$refid_option_id;
            }
            // All products except decorated and catalog products
            if (isset($data->is_customize) && $data->is_customize == '0') {
                $sql .= "  AND p.product_id NOT IN(SELECT po.product_id FROM " . DB_PREFIX . "product_option po WHERE po.option_id = ? and po.value!='')";
                $params[0] .= 's';
                $params[] = &$refid_option_id;
            }
        }
        if ((isset($data->category) && $data->category != "") && (isset($data->subcategory) && $data->subcategory != "")) {
            $cat_array = array($data->category, $data->subcategory);
            $sql .= " AND p.product_id IN(SELECT p2c.product_id FROM " . DB_PREFIX . "product_to_category p2c WHERE p2c.category_id IN (" . implode(',', $cat_array) . "))";
        }
        if (isset($data->category) && $data->category != "") {
            $cat_ids = $this->getCategoryList($data->category);
            $sql .= " AND p.product_id IN(SELECT p2c.product_id FROM " . DB_PREFIX . "product_to_category p2c INNER JOIN `" . DB_PREFIX . "category_to_store` c2s ON p2c.category_id = c2s.category_id WHERE p2c.category_id IN (" . implode(',', $cat_ids) . "))";
        }
        if (isset($data->search) && $data->search != "") {
            $sql .= " AND pd.name like ? ";
            $datasearchstring = "%" . $data->search . "%";
            $params[0] .= 's';
            $params[] = &$datasearchstring;
        }

        if (isset($data->order_by) && $data->order_by == "post_date") {
            $sql .= " ORDER BY product_id ";
        } elseif (isset($data->order_by) && $data->order_by == "name") {
            $sql .= " ORDER BY pd.name ";
        } else {
            $sql .= " ORDER BY product_id ";
        }
        if (isset($data->order) && $data->order != "") {
            $sql .= $data->order;
        } else {
            $sql .= 'DESC';
        }

        $limit = '';
        $start = (int) $data->range * ((int) $data->page - 1);
        if (isset($data->page) && isset($data->range)) {
            $limit = ' limit ? , ?';
            $params[0] .= 'ss';
            $params[] = &$start;
            $params[] = &$data->range;
        }
        $sql .= $limit;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $query = $stmt->get_result();
        $count = mysqli_num_rows($query);
        $i = 0;
        while ($row = mysqli_fetch_array($query)) {
            if (isset($data->fetch) && $data->fetch == 'all') {
                $data = $this->getIsDesignAndCustomDesignValue($row['product_id']);
                if ($data[0]['xe_is_design']) {
                    $products[$i]['is_redesign'] = $data[0]['xe_is_design'];
                } else {
                    $products[$i]['is_redesign'] = 0;
                }
                if ($data[0]['refid']) {
                    $products[$i]['custom_design_id'] = $data[0]['refid'];
                } else {
                    $products[$i]['custom_design_id'] = 0;
                }
            }
            $products[$i]['id'] = $row['product_id'];
            $products[$i]['name'] = $row['name'];
            $products[$i]['price'] = $row['price'];
            $products[$i]['sku'] = $row['sku'];
            $products[$i]['variation_id'] = $this->getVariantId($row['product_id']);
            if ($products[$i]['variation_id'] !== $products[$i]['id']) {
                $type = "variable";
                $products[$i]['is_sold_out'] = $this->isSoldOut($row['product_id']);
            } else {
                $type = "simple";
                if ($row['quantity'] > 0) {
                    $products[$i]['is_sold_out'] = false;
                } else {
                    $products[$i]['is_sold_out'] = true;
                }
            }
            $products[$i]['stock'] = $row['quantity'];
            $products[$i]['type'] = $type;
            $products[$i]['image'][] = HTTP_SERVER . 'image/' . $row['thumbnail'];
            $i++;
        }
        return array('data' => $products, 'records' => $count);
    }

    public function getVariantId($productId)
    {
        $variant_id = 0;
        $sqlVariant = "SELECT product_id FROM " . DB_PREFIX . "product_variant WHERE variant_id = ? LIMIT 1";
        $params = array();
        $params[] = 'i';
        $params[] = &$productId;
        $stmts = $this->con->prepare($sqlVariant);
        call_user_func_array([$stmts, 'bind_param'], $params);
        $stmts->execute();
        $query = $stmts->get_result();
        $rows = mysqli_fetch_array($query);
        if (!empty($rows)) {
            $variant_id = $rows['product_id'];
        } else {
            $variant_id = $productId;
        }
        return $variant_id;
    }

    public function getCategoryList($categoryid)
    {
        $categories[] = $categoryid;
        $sql = "SELECT c.category_id FROM " . DB_PREFIX . "category c WHERE c.parent_id=?";
        $params = array();
        $params[] = 'i';
        $params[] = &$categoryid;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $query = $stmt->get_result();
        while ($row = mysqli_fetch_array($query)) {
            $categories[] = $row['category_id'];
        }
        return $categories;
    }

    public function getProductById($id)
    {
        $product_info = array();
        $sql = "SELECT * FROM " . DB_PREFIX . "product p WHERE p.product_id = ? and p.is_variant='0'";
        $params = array();
        $params[] = 'i';
        $params[] = &$id;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $query = $stmt->get_result();
        if (mysqli_num_rows($query) > 0) {
            $config_id = (int) $id;
        } else {
            $sql = "SELECT variant_id FROM " . DB_PREFIX . "product_variant WHERE product_id = ?";
            $params = array();
            $params[] = 'i';
            $params[] = &$id;
            $stmt = $this->con->prepare($sql);
            call_user_func_array([$stmt, 'bind_param'], $params);
            $stmt->execute();
            $query = $stmt->get_result();
            $row = mysqli_fetch_array($query);
            $config_id = $row['variant_id'];
        }

        $variant_id = ($config_id != (int) $id) ? (int) $id : '';
        $sql = "SELECT p.product_id,p.sku,p.price,p.quantity,p.image,pd.name,pd.description FROM " . DB_PREFIX . "product p INNER JOIN " . DB_PREFIX . "product_description pd ON(p.product_id = pd.product_id) WHERE p.product_id=?";
        $params = array();
        $params[] = 'i';
        $params[] = &$config_id;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $query = $stmt->get_result();
        $row = mysqli_fetch_array($query);
        if (!empty($row)) {
            $variants = $this->getProductVariants($row['product_id'], $variant_id);
            $product_info['id'] = $row['product_id'];
            $product_info['type'] = (isset($variants['type'])) ? $variants['type'] : 'configurable';
            $product_info['name'] = $row['name'];
            $product_info['sku'] = $row['sku'];
            $product_info['description'] = strip_tags(html_entity_decode($row['description']));
            $product_info['categories'] = $this->getCategoriesByProduct($row['product_id']);
            $product_info['pvid'] = $variants['id'];
            $product_info['pvname'] = $variants['name'];
            $product_info['stock_quantity'] = $variants['quantity'];
            $product_info['price'] = $variants['price'];
            $product_info['taxrate'] = $variants['tax'];
            $product_info['tier_prices'] = $this->getTierPrice($id, $variants['price']);
        }
        return $product_info;
    }

    public function getProductVariants($product_id, $variant_id)
    {
        $sql = "SELECT * FROM " . DB_PREFIX . "product_variant pv LEFT JOIN " . DB_PREFIX . "product p ON (p.product_id = pv.product_id) INNER JOIN `" . DB_PREFIX . "product_to_store` pts ON p.product_id = pts.product_id LEFT JOIN " . DB_PREFIX . "product_description pd ON (pd.product_id = p.product_id) WHERE pv.variant_id= ? AND pts.store_id=? ORDER BY p.product_id";
        $params = array();
        $params[0] = 'ii';
        $params[] = &$product_id;
        $params[] = &$this->store;
        if ($variant_id != '') {
            $sql .= " AND pv.product_id=?";
            $params[0] .= 'i';
            $params[] = &$variant_id;
        }
        $sql .= " LIMIT 1";
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $query = $stmt->get_result();

        if (mysqli_num_rows($query) > 0) {
            $sql = "SELECT * FROM " . DB_PREFIX . "setting s WHERE s.key = ?";
            $configTax = 'config_tax';
            $params = array();
            $params[] = 's';
            $params[] = &$configTax;
            $stmt = $this->con->prepare($sql);
            call_user_func_array([$stmt, 'bind_param'], $params);
            $stmt->execute();
            $res = $stmt->get_result();
            $exe = mysqli_fetch_array($res);
            while ($row = mysqli_fetch_array($query)) {
                if ($row['tax_class_id'] != 0 && $exe['value'] == 1) {
                    $sql = "SELECT * FROM " . DB_PREFIX . "tax_rate trt LEFT JOIN " . DB_PREFIX . "tax_rule trl ON (trt.tax_rate_id = trl.tax_rate_id) WHERE trl.tax_rate_id=?";
                    $params = array();
                    $params[] = 's';
                    $params[] = &$row['tax_class_id'];
                    $stmt = $this->con->prepare($sql);
                    call_user_func_array([$stmt, 'bind_param'], $params);
                    $stmt->execute();
                    $que = $stmt->get_result();
                    $tax_row = mysqli_fetch_array($que);
                    $tax_price = 0;
                    foreach ($tax_row as $tax_rate) {
                        if ($tax_rate['type'] == 'F') {
                            //$tax_price += $tax_rate['rate'];
                            $tax_price = $tax_rate['rate'];
                        } elseif ($tax_rate['type'] == 'P') {
                            $tax_price += $tax_rate['rate']; //($row['price'] / 100 * $tax_rate['rate']);
                        } else {
                            continue;
                        }
                    }
                } else {
                    $tax_price = 0;
                }

                $variants['type'] = 'variable';
                $variants['id'] = $row['product_id'];
                $variants['name'] = $row['name'];
                $variants['quantity'] = $row['quantity'];
                $variants['price'] = $row['price'];
                $variants['tax'] = $tax_price;
                $variants['minQuantity'] = $row['minimum'];
                $variants['tier_prices'] = $this->getTierPrice($row['product_id'], $row['price']);
            }
        } else {
            $sql = "SELECT * FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (pd.product_id = p.product_id) INNER JOIN `" . DB_PREFIX . "product_to_store` pts ON p.product_id = pts.product_id WHERE p.product_id= ? AND pts.store_id=?";
            $params = array();
            $params[] = 'is';
            $params[] = &$product_id;
            $params[] = &$this->store;
            $stmt = $this->con->prepare($sql);
            call_user_func_array([$stmt, 'bind_param'], $params);
            $stmt->execute();
            $query = $stmt->get_result();
            $row = mysqli_fetch_array($query);
            if ($row['tax_class_id'] != 0 && $exe['value'] == 1) {
                $sql = "SELECT * FROM " . DB_PREFIX . "tax_rate trt LEFT JOIN " . DB_PREFIX . "tax_rule trl ON (trt.tax_rate_id = trl.tax_rate_id) WHERE trl.tax_rate_id=?";
                $params = array();
                $params[] = 's';
                $params[] = &$row['tax_class_id'];
                $stmt = $this->con->prepare($sql);
                call_user_func_array([$stmt, 'bind_param'], $params);
                $stmt->execute();
                $que = $stmt->get_result();
                $tax_row = mysqli_fetch_array($que);
                $tax_price = 0;
                if (!empty($tax_row)) {
                    foreach ($tax_row as $tax_rate) {
                        if ($tax_rate['type'] == 'F') {
                            $tax_price += $tax_rate['rate'];
                        } elseif ($tax_rate['type'] == 'P') {
                            $tax_price += ($row['price'] / 100 * $tax_rate['rate']);
                        } else {
                            continue;
                        }
                    }
                }
            } else {
                $tax_price = 0;
            }
            $variants['type'] = 'simple';
            $variants['id'] = $row['product_id'];
            $variants['name'] = $row['name'];
            $variants['quantity'] = $row['quantity'];
            $variants['price'] = $row['price'];
            $variants['tax'] = $tax_price;
            $refid = $this->getProductOptionsModified($row['product_id'], 'refid');
            $variants['refid'] = isset($refid['refid']) ? $refid['refid'] : '';
        }
        return $variants;
    }

    /**
     *
     * date created (dd-mm-yy)
     * date modified 24-05-2017(dd-mm-yy)
     * Get all product options
     *
     * @param (int)product_id
     * @return array data
     *
     */
    public function getProductOptions($product_id)
    {
        $productOptionData = array();
        $priceSql = "SELECT price, sku FROM " . DB_PREFIX . "product WHERE product_id=?";
        $params = array();
        $params[] = 'i';
        $params[] = &$product_id;
        $stmt = $this->con->prepare($priceSql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $priceQuery = $stmt->get_result();
        $priceResult = mysqli_fetch_array($priceQuery);
        $sql = "SELECT * FROM " . DB_PREFIX . "product_option po LEFT JOIN `" . DB_PREFIX . "option` o ON (po.option_id = o.option_id) LEFT JOIN " . DB_PREFIX . "option_description od ON (o.option_id = od.option_id) WHERE po.product_id = ? ORDER BY o.sort_order";
        $params = array();
        $params[] = 'i';
        $params[] = &$product_id;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $productOptionQuery = $stmt->get_result();
        $i = 0;
        while ($row = mysqli_fetch_array($productOptionQuery)) {
            if ($row['name'] != 'refid' && $row['name'] != 'xe_is_design' && $row['name'] != 'disable_addtocart') {
                if ($row['type'] == 'select' || $row['type'] == 'radio' || $row['type'] == 'checkbox' || $row['type'] == 'image') {
                    $productOptionData[$i]['option_title'] = $row['name'];
                    $productOptionData[$i]['option_id'] = $row['product_option_id'];
                    $productOptionData[$i]['type'] = $row['type'];
                    $productOptionData[$i]['is_require'] = $row['required'];
                    $sql = "SELECT pov.quantity, pov.option_value_id, pov.price, pov.price_prefix, ov.sort_order, ovd.name FROM " . DB_PREFIX . "product_option_value pov LEFT JOIN " . DB_PREFIX . "option_value ov ON (pov.option_value_id = ov.option_value_id) LEFT JOIN " . DB_PREFIX . "option_value_description ovd ON (ov.option_value_id = ovd.option_value_id) WHERE pov.product_id = ? AND pov.product_option_id = ?  ORDER BY ov.sort_order";
                    $params = array();
                    $params[] = 'ii';
                    $params[] = &$product_id;
                    $params[] = &$row['product_option_id'];
                    $stmt = $this->con->prepare($sql);
                    call_user_func_array([$stmt, 'bind_param'], $params);
                    $stmt->execute();
                    $optionQuery = $stmt->get_result();
                    $j = 0;
                    while ($optionResult = mysqli_fetch_array($optionQuery)) {
                        $price = ($optionResult['price_prefix'] == '+') ? $priceResult['price'] + $optionResult['price'] : $priceResult['price'] - $optionResult['price'];
                        $productOptionData[$i]['option_values'][$j]['option_type_id'] = $optionResult['option_value_id'];
                        $productOptionData[$i]['option_values'][$j]['sku'] = $priceResult['sku'];
                        $productOptionData[$i]['option_values'][$j]['sort_order'] = $optionResult['sort_order'];
                        $productOptionData[$i]['option_values'][$j]['title'] = $optionResult['name'];
                        $productOptionData[$i]['option_values'][$j]['price'] = $price;
                        $productOptionData[$i]['option_values'][$j]['quantity'] = $optionResult['quantity'];
                        $j++;
                    }
                }
                $i++;
            }
        }
        $option = array();
        foreach ($productOptionData as $data) {
            $option[] = $data;
        }
        return $option;
    }

    public function getProductOptionsModified($product_id, $type, $extra = 0)
    {
        $sql = "SELECT * FROM " . DB_PREFIX . "product_option po LEFT JOIN `" . DB_PREFIX . "option` o ON (po.option_id = o.option_id) LEFT JOIN " . DB_PREFIX . "option_description od ON (o.option_id = od.option_id) WHERE po.product_id = ? ORDER BY o.sort_order";
        $params = array();
        $params[] = 'i';
        $params[] = &$product_id;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $query = $stmt->get_result();
        $color = array();
        $size = array();
        $refid = array();
        $attributeArray = array();
        while ($row = mysqli_fetch_array($query)) {
            if ($row['type'] == 'select' || $row['type'] == 'radio' || $row['type'] == 'checkbox' || $row['type'] == 'image') {
                $sql = "SELECT pov.option_value_id, pov.price, pov.price_prefix, ovd.name FROM " . DB_PREFIX . "product_option_value pov LEFT JOIN " . DB_PREFIX . "option_value ov ON (pov.option_value_id = ov.option_value_id) LEFT JOIN " . DB_PREFIX . "option_value_description ovd ON (ov.option_value_id = ovd.option_value_id) WHERE pov.product_id = ? AND pov.product_option_id = ? ORDER BY ov.sort_order";
                $params = array();
                $params[] = 'ii';
                $params[] = &$product_id;
                $params[] = &$row['product_option_id'];
                $stmt = $this->con->prepare($sql);
                call_user_func_array([$stmt, 'bind_param'], $params);
                $stmt->execute();
                $query2 = $stmt->get_result();
                $i = 0;
                while ($row2 = mysqli_fetch_array($query2)) {
                    if ($row['name'] == $this->color) {
                        $color[$row['name']] = $row2['name'];
                        $color['option_value_id'] = $row2['option_value_id'];
                    } elseif ($row['name'] == 'Size') {
                        $size[$i]['name'] = $row2['name'];
                        $size[$i]['price'] = $row2['price'];
                        $size[$i]['price_prefix'] = $row2['price_prefix'];
                        $size[$i]['option_value_id'] = $row2['option_value_id'];
                        $i++;
                    } elseif ($extra == 0) {
                        $attributeArray[$row['name']] = $row2['name'];
                        $attributeArray[$row['name'] . '_id'] = $row2['option_value_id'];
                    } else {
                        echo "Error";
                    }
                }
            } elseif ($row['type'] == 'text') {
                /*if (strtolower($row['name']) == $this->color) {
            $color[$row['name']] = $row['value'];
            } elseif (strtolower($row['name']) == 'refid') {
            $refid[$row['name']] = $row['value'];
            } elseif (strtolower($row['name'] != 'xe_is_design') && strtolower($row['name'] != 'disable_addtocart')) {
            $attributeArray[$row['name']] = $row['value'];
            $attributeArray[$row['name'] . '_id'] = $row['option_id'];
            }*/
            } else {
                continue;
            }
        }
        if ($type == 'color') {
            return $color;
        } elseif ($type == 'size') {
            return $size;
        } elseif ($type == 'refid') {
            return $refid;
        } elseif ($type == 'all') {
            return $attributeArray;
        }
    }

    public function getProductImages($product_id)
    {
        $images = array();
        // For main product images
        $sql = "SELECT image FROM " . DB_PREFIX . "product p WHERE p.product_id = ?";
        $params = array();
        $params[] = 'i';
        $params[] = &$product_id;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $query = $stmt->get_result();
        if (mysqli_num_rows($query) > 0) {
            $z = 0;
            $image = mysqli_fetch_array($query);
            if ($image['image']) {
                $images[$z]['src'] = HTTP_SERVER . 'image/' . $image['image'];
                $images[$z]['thumbnail'] = HTTP_SERVER . 'image/' . $image['image'];
                $z++;
            }
            $sqlAditionalImg = "SELECT * FROM " . DB_PREFIX . "product_image WHERE product_id=? ORDER BY sort_order ASC";
            $params = array();
            $params[] = 'i';
            $params[] = &$product_id;
            $stmt = $this->con->prepare($sqlAditionalImg);
            call_user_func_array([$stmt, 'bind_param'], $params);
            $stmt->execute();
            $additional_images = $stmt->get_result();
            while ($row = mysqli_fetch_array($additional_images)) {
                $images[$z]['src'] = HTTP_SERVER . 'image/' . $row['image'];
                $images[$z]['thumbnail'] = HTTP_SERVER . 'image/' . $row['image'];
                $z++;
            }
        }
        if (empty($images)) {
            // For parent Product
            $sql = "SELECT variant_id FROM " . DB_PREFIX . "product_variant WHERE product_id = ?";
            $params = array();
            $params[] = 'i';
            $params[] = &$product_id;
            $stmt = $this->con->prepare($sql);
            call_user_func_array([$stmt, 'bind_param'], $params);
            $stmt->execute();
            $query = $stmt->get_result();
            $rowpv = mysqli_fetch_array($query);
            $parent_pro_id = $rowpv['variant_id'];

            $sql = "SELECT image FROM " . DB_PREFIX . "product p WHERE p.product_id = ?";
            $params = array();
            $params[] = 'i';
            $params[] = &$parent_pro_id;
            $stmt = $this->con->prepare($sql);
            call_user_func_array([$stmt, 'bind_param'], $params);
            $stmt->execute();
            $query = $stmt->get_result();
            if (mysqli_num_rows($query) > 0) {
                $z = 0;
                $image = mysqli_fetch_array($query);
                if ($image['image']) {
                    $images[$z]['src'] = HTTP_SERVER . 'image/' . $image['image'];
                    $images[$z]['thumbnail'] = HTTP_SERVER . 'image/' . $image['image'];
                    $z++;
                }
                $sqlAditionalImg = "SELECT * FROM " . DB_PREFIX . "product_image WHERE product_id=? ORDER BY sort_order ASC";
                $params = array();
                $params[] = 'i';
                $params[] = &$parent_pro_id;
                $stmt = $this->con->prepare($sqlAditionalImg);
                call_user_func_array([$stmt, 'bind_param'], $params);
                $stmt->execute();
                $additional_images = $stmt->get_result();
                while ($row = mysqli_fetch_array($additional_images)) {
                    $images[$z]['src'] = HTTP_SERVER . 'image/' . $row['image'];
                    $images[$z]['thumbnail'] = HTTP_SERVER . 'image/' . $row['image'];
                    $z++;
                }
            }
        }
        return $images;
    }

    public function getProductThumbImages($product_id)
    {
        $images = array();
        $sql = "SELECT * FROM " . DB_PREFIX . "product_image WHERE product_id=? ORDER BY sort_order ASC";
        $params = array();
        $params[] = 's';
        $params[] = &$product_id;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $additional_images = $stmt->get_result();
        $z = 1;
        while ($row = mysqli_fetch_array($additional_images)) {
            $images[] = HTTP_SERVER . 'image/' . $row['image'];
            $z++;
        }
        return $images;
    }

    public function getCategoriesByProduct($product_id)
    {
        $categories = array();
        $sql = "SELECT c.category_id, cd.name, c.parent_id FROM " . DB_PREFIX . "category c INNER JOIN " . DB_PREFIX . "product_to_category ptc ON (ptc.category_id = c.category_id) INNER JOIN " . DB_PREFIX . "category_description cd ON (cd.category_id = c.category_id) INNER JOIN " . DB_PREFIX . "category_to_store cts ON (c.category_id = cts.category_id) WHERE ptc.product_id = ? AND cts.store_id = ? ORDER BY c.category_id";
        $params = array();
        $params[] = 'ii';
        $params[] = &$product_id;
        $params[] = &$this->store;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $query = $stmt->get_result();
        $i = 0;
        while ($row = mysqli_fetch_array($query)) {
            $categories[$i]['name'] = $row['name'];
            $categories[$i]['slug'] = $row['name'];
            $categories[$i]['parent_id'] = $row['parent_id'];
            $categories[$i]['id'] = $row['category_id'];
            $i++;
        }
        return $categories;
    }

    /**
     *
     * date of created 06-06-2017(dd-mm-yy)
     * date of Modified (dd-mm-yy)
     * get all Product options
     *
     * @return Array  data
     *
     */
    public function getAllOptions()
    {
        $product_option_data = array();
        $language_id = $this->getLanguageId();
        $sql = "SELECT o.*, od.* FROM `" . DB_PREFIX . "option` o LEFT JOIN `" . DB_PREFIX . "option_description` od ON (o.option_id = od.option_id) WHERE language_id = ? ORDER BY o.option_id";
        $params = array();
        $params[] = 'i';
        $params[] = &$language_id;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $query = $stmt->get_result();
        $i = 0;
        while ($row = mysqli_fetch_array($query)) {
            if ($row['type'] == "select") {
                $product_option_data[$i]['id'] = $row['option_id'];
                $product_option_data[$i]['name'] = $row['name'];
                $product_option_data[$i]['type'] = $row['type'];
                $i++;
            }
        }
        return $product_option_data;
    }

    public function getOptionValuesById($id = 0)
    {
        $options = [];
        $language_id = $this->getLanguageId();

        $sql = "SELECT * FROM " . DB_PREFIX . "option_value ov LEFT JOIN  " . DB_PREFIX . "option_value_description ovd ON (ov.option_value_id = ovd.option_value_id) WHERE ov.option_id = ? AND ovd.language_id = ?";
        $params = array();
        $params[] = 'ii';
        $params[] = &$id;
        $params[] = &$language_id;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $query = $stmt->get_result();
        $i = 0;
        while ($result = mysqli_fetch_array($query)) {
            $options[$i]['id'] = $result['option_value_id'];
            $options[$i]['name'] = $result['name'];
            $i++;
        }
        return $options;
    }

    public function getLanguageId()
    {
        $language_id = 0;
        $sql = "SELECT language_id FROM `" . DB_PREFIX . "language` WHERE status=?";
        $status_id = 1;
        $params = array();
        $params[] = 'i';
        $params[] = &$status_id;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $query = $stmt->get_result();
        $row = mysqli_fetch_array($query);
        if (!empty($row)) {
            $language_id = $row['language_id'];
        }

        return $language_id;
    }

    public function checkExistingOptionValue($id, $name)
    {
        $result = false;
        $language_id = $this->getLanguageId();
        $sql = "SELECT * FROM " . DB_PREFIX . "option_value_description WHERE option_id = ? AND language_id = ? AND name = ?";
        $params = array();
        $params[] = 'iis';
        $params[] = &$language_id;
        $params[] = &$name;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $query = $stmt->get_result();
        $result = mysqli_fetch_array($query);
        if (!empty($result)) {
            $result = true;
        }
        return $result;
    }

    public function addOptionValue($id, $name)
    {
        $result = [];
        $languageId = $this->getLanguageId();
        $optionId = $id;
        $optionValue = $name;
        $sortOrder = 0;
        $sql = "INSERT INTO " . DB_PREFIX . "option_value SET option_id = ?, image = '', sort_order = ?";
        $params = array();
        $params[] = 'ii';
        $params[] = &$optionId;
        $params[] = &$sortOrder;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $stmt->get_result();
        $optionValueId = mysqli_insert_id($this->con);
        $sql = "INSERT INTO " . DB_PREFIX . "option_value_description SET option_value_id = ?, language_id = ?, option_id = ?, name = ?";
        $params = array();
        $params[] = 'iiis';
        $params[] = &$optionValueId;
        $params[] = &$languageId;
        $params[] = &$optionId;
        $params[] = &$optionValue;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $status = $stmt->execute();
        $query = $stmt->get_result();
        if ($status) {
            $result['id'] = $optionValueId;
        }
        return $result;
    }

    public function getProductAllOptionDetails($productId, $isTool)
    {
        $options = array();
        if ($productId > 0) {
            $sql = "SELECT po.*, od.name FROM " . DB_PREFIX . "product_option po LEFT JOIN " . DB_PREFIX . "option_description od ON (po.option_id = od.option_id) WHERE po.product_id = ?";
            $params = array();
            $params[] = 'i';
            $params[] = &$productId;
            $stmt = $this->con->prepare($sql);
            call_user_func_array([$stmt, 'bind_param'], $params);
            $stmt->execute();
            $query = $stmt->get_result();
            $i = 0;
            while ($row = mysqli_fetch_array($query)) {
                if ($row['name'] != "xe_is_design" && $row['name'] != "refid") {
                    $options[$i]['id'] = $row['option_id'];
                    $options[$i]['name'] = $row['name'];
                    if ($isTool) {
                        $options[$i]['options'] = $this->getOptionValuesByProductOptId($row['product_option_id']);
                        $i++;
                    } else {
                        $options[$i]['terms'] = $this->getOptionValuesByProductOptId($row['product_option_id']);
                        $i++;
                    }
                }
            }
        } else {
            $language_id = $this->getLanguageId();
            $sql = "SELECT o.*, od.name FROM `" . DB_PREFIX . "option` o LEFT JOIN `" . DB_PREFIX . "option_description` od ON (o.option_id = od.option_id) WHERE language_id = ? ORDER BY o.option_id";
            $params = array();
            $params[] = 'i';
            $params[] = &$language_id;
            $stmt = $this->con->prepare($sql);
            call_user_func_array([$stmt, 'bind_param'], $params);
            $stmt->execute();
            $query = $stmt->get_result();
            $i = 0;
            while ($row = mysqli_fetch_array($query)) {
                if ($row['name'] != "xe_is_design" && $row['name'] != "refid" && $row['type'] == "select") {
                    $options[$i]['id'] = $row['option_id'];
                    $options[$i]['name'] = $row['name'];
                    $options[$i]['type'] = $row['type'];
                    $options[$i]['terms'] = $this->getOptionValuesById($row['option_id']);
                    $i++;
                }
            }
        }
        return $options;
    }

    public function getOptionValuesByProductOptId($proOptId)
    {
        $optVals = array();
        $sql = "SELECT o.*, p.* FROM " . DB_PREFIX . "option_value_description o, " . DB_PREFIX . "product_option_value p WHERE p.option_value_id = o.option_value_id and p.product_option_id = ?";
        $params = array();
        $params[] = 'i';
        $params[] = &$proOptId;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $query = $stmt->get_result();
        $i = 0;
        while ($row = mysqli_fetch_array($query)) {
            $optVals[$i]['id'] = $row['option_value_id'];
            $optVals[$i]['name'] = $row['name'];
            $i++;
        }
        return $optVals;
    }

    public function getOptions($data = array())
    {
        $response = array();
        $i = 0;
        // if ($data['filter_name'] == $this->color && isset($data['product_id']) && $data['product_id'] != 0) {
        $productvariant = $this->getProductInfo($data['product_id']);
        $language_id = $this->getLanguageId();
        foreach ($productvariant as $variant) {
            $sql = "SELECT name FROM `" . DB_PREFIX . "option_value_description` WHERE language_id=? and option_value_id=?";
            $params = array();
            $params[] = 'ss';
            $params[] = &$language_id;
            $params[] = &$variant['color_id'];
            $stmt = $this->con->prepare($sql);
            call_user_func_array([$stmt, 'bind_param'], $params);
            $stmt->execute();
            $query = $stmt->get_result();
            $color = mysqli_fetch_array($query);
            $response[$i]['id'] = $variant['color_id'];
            $response[$i]['slug'] = $color['name'];
            $response[$i]['name'] = $color['name'];
            $response[$i]['variant_id'] = $variant['var_id'];
            $pvariant = $this->getProductById($variant['var_id']);
            $response[$i]['inventory']['stock'] = $pvariant['stock_quantity'];
            $response[$i]['inventory']['min_quantity'] = 1;
            $response[$i]['inventory']['max_quantity'] = $pvariant['stock_quantity'];
            $response[$i]['inventory']['quantity_increments'] = 1;
            $response[$i]['price'] = $pvariant['price'];
            $response[$i]['tier_prices'] = $this->getTierPrice($variant['var_id'], $pvariant['price']);
            $j = 0;
            $productImages = $this->getProductImages($variant['var_id']);
            if (!empty($productImages)) {
                foreach ($productImages as $imgValue) {
                    $response[$i]['sides'][$j]['image'] = $imgValue;
                    $j++;
                }
            }
            $i++;
        }

        return $response;
    }

    public function getProductInfo($conf_id)
    {
        $sql = "SELECT * FROM " . DB_PREFIX . "product p WHERE p.product_id = ? and p.is_variant='0'";
        $params = array();
        $params[] = 'i';
        $params[] = &$conf_id;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $query = $stmt->get_result();
        if (mysqli_num_rows($query) > 0) {
            $config_id = (int) $conf_id;
        } else {
            $sql = "SELECT variant_id FROM " . DB_PREFIX . "product_variant WHERE product_id = ?";
            $params = array();
            $params[] = 'i';
            $params[] = &$conf_id;
            $stmt = $this->con->prepare($sql);
            call_user_func_array([$stmt, 'bind_param'], $params);
            $stmt->execute();
            $query = $stmt->get_result();
            $row = mysqli_fetch_array($query);
            $config_id = $row['variant_id'];
        }
        $sql = "SELECT * FROM " . DB_PREFIX . "product_variant pv LEFT JOIN " . DB_PREFIX . "product p ON (p.product_id = pv.product_id) LEFT JOIN " . DB_PREFIX . "product_description pd ON (pd.product_id = p.product_id)WHERE pv.variant_id= ?";
        $params = array();
        $params[] = 'i';
        $params[] = &$config_id;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $query = $stmt->get_result();
        $i = 0;
        $variants = array();
        if ((mysqli_num_rows($query) > 0)) {
            while ($row = mysqli_fetch_array($query)) {
                $color = $this->getProductOptionsModified($row['product_id'], 'color');
                $size = $this->getProductOptionsModified($row['product_id'], 'size');
                $variants[$i]['color_id'] = $color['option_value_id'];
                $variants[$i]['var_id'] = $row['product_id'];
                $variants[$i]['sizeid'] = array();
                foreach ($size as $siz) {
                    $variants[$i]['sizeid'][] = $siz['option_value_id'];
                }
                $i++;
            }
        }
        return $variants;
    }

    /**
     * Count Products
     *
     * @author mukeshp@riaxe.com
     * @date   22 April 2020
     * @return array of products
     */
    public function totalProduct()
    {
        $sql = "SELECT p.product_id FROM " . DB_PREFIX . "product p ";
        $sql .= "INNER JOIN " . DB_PREFIX . "product_description pd ON(p.product_id = pd.product_id) ";
        $sql .= "INNER JOIN " . DB_PREFIX . "product_to_store pts ON (p.product_id = pts.product_id) WHERE p.product_id != '' AND p.is_variant='0' AND pts.store_id = ? ";
        $params = array();
        $params[0] = 's';
        $params[] = &$this->store;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $query = $stmt->get_result();
        $count = mysqli_num_rows($query);
        return $count;
    }

    public function getProductIdByNameAndSku($filter)
    {
        $productId = 0;
        $search = "";
        $sku = "";
        if (array_key_exists("search", $filter)) {
            $search = $filter['search'];
        } else {
            $sku = $filter['sku'];
        }
        $sql = "SELECT p.product_id FROM " . DB_PREFIX . "product p ";
        $sql .= "INNER JOIN " . DB_PREFIX . "product_description pd ON(p.product_id = pd.product_id) ";
        $sql .= "INNER JOIN " . DB_PREFIX . "product_to_store pts ON (p.product_id = pts.product_id) WHERE p.product_id != '' AND p.is_variant='0' AND pts.store_id = ? ";
        $params = array();
        $params[0] = 's';
        $params[] = &$this->store;

        if (isset($search) && $search != "") {
            $sql .= " AND pd.name like ? ";
            $datasearchstring = "%" . $search . "%";
            $params[0] .= 's';
            $params[] = &$datasearchstring;
        }

        if (isset($sku) && $sku != "") {
            $sql .= " AND p.sku = ? ";
            $params[0] .= 's';
            $params[] = &$sku;
        }

        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $query = $stmt->get_result();
        while ($row = mysqli_fetch_array($query)) {
            $productId = $row['product_id'];
            //break;
        }
        return $productId;
    }

    public function getProductRelatedOptions($product_id)
    {
        $options = array();
        $sql = "SELECT po.product_option_id, od.name FROM " . DB_PREFIX . "product_option po LEFT JOIN " . DB_PREFIX . "option_description od ON (po.option_id = od.option_id) WHERE po.product_id = ?";
        $params = array();
        $params[] = 'i';
        $params[] = &$product_id;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $query = $stmt->get_result();
        $i = 0;
        while ($row = mysqli_fetch_array($query)) {
            $options[$i]['id'] = $row['product_option_id'];
            $options[$i]['name'] = $row['name'];
            $i++;
        }
        return $options;
    }

    /**
     *
     * date created (dd-mm-yy)
     * date modified 24-05-2017(dd-mm-yy)
     * Get value of product option
     *
     * @param (string)option name
     * @param (int)option id
     * @return array data
     *
     */
    public function getProductOptionValue($option, $id)
    {
        $sql = "SELECT p.product_option_value_id FROM " . DB_PREFIX . "option_value_description o, " . DB_PREFIX . "product_option_value p  WHERE p.option_value_id = o.option_value_id AND o.name=? AND p.product_option_id=?";
        $params = array();
        $params[] = 'ss';
        $params[] = &$option;
        $params[] = &$id;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $query = $stmt->get_result();
        $sql = "SELECT value FROM " . DB_PREFIX . "product_option WHERE product_option_id=?";
        $params = array();
        $params[] = 's';
        $params[] = &$id;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $queryForTextOption = $stmt->get_result();
        if (mysqli_num_rows($query) > 0) {
            $row = mysqli_fetch_array($query);
            $value_id = $row['product_option_value_id'];
        } else {
            $row = mysqli_fetch_array($queryForTextOption);
            $value_id = $row['value'];
        }

        return $value_id;
    }

    public function addTemplateProducts($product_data)
    {
        $config_id = 0;
        $config_id = (isset($product_data['product_id']) && $product_data['product_id'] != 0) ? $product_data['product_id'] : $this->addConfigTemplateProduct($product_data);
        // For simple product
        if ($product_data['product_type'] == 'simple') {
            return array("id" => $config_id);
        }

        if ($config_id != 0) {
            // for creating variation product.
            $varAttrColor = [];
            $varAttrOthers = [];
            $variationIds = [];
            $colorName = "";
            $i = 0;
            if (!empty($product_data['attributes'])) {
                foreach ($product_data['attributes'] as $value) {
                    if ($value['attribute_name'] == $this->color) {
                        $varAttrColor['id'] = $value['attribute_id'];
                        $varAttrColor['name'] = $value['attribute_name'];
                        $varAttrColor['options'] = $value['attribute_options'];
                    } else {
                        $varAttrOthers[$i]['id'] = $value['attribute_id'];
                        $varAttrOthers[$i]['name'] = $value['attribute_name'];
                        $varAttrOthers[$i]['options'] = $value['attribute_options'];
                        $i++;
                    }
                }
                // If color attributes not set
                if (empty($varAttrColor)) {
                    $varAttrColor['id'] = $product_data['attributes'][0]['attribute_id'];
                    $varAttrColor['name'] = $product_data['attributes'][0]['attribute_name'];
                    $varAttrColor['options'] = $product_data['attributes'][0]['attribute_options'];
                }
            }
            if (!empty($varAttrColor) && !empty($varAttrColor['options'])) {
                // Create each color variation product.
                $j = 0;
                foreach ($varAttrColor['options'] as $optValueId) {
                    $sql = "INSERT INTO " . DB_PREFIX . "product SET model = 'Customized',sku = ?, upc = '', ean = '', jan = '', isbn = '', mpn = '', location = '', quantity = ?, minimum = ?, subtract = '1', stock_status_id = '7', date_available = NOW(), manufacturer_id = '0', shipping = '1', price = ?, points = '0', weight = '0', weight_class_id = '1', length = '0', width = '0', height = '0', length_class_id = '1', status = '1', tax_class_id = '0', sort_order = '1', date_added = NOW(), date_modified = NOW(), is_variant= '1'";
                    $product_data['mini_qty'] = empty($product_data['mini_qty']) ? '1' : $product_data['mini_qty'];
                    $params = array();
                    $params[] = 'ssss';
                    $params[] = &$product_data['sku'];
                    $params[] = &$product_data['qty'];
                    $params[] = &$product_data['mini_qty'];
                    $params[] = &$product_data['price'];
                    $stmt = $this->con->prepare($sql);
                    call_user_func_array([$stmt, 'bind_param'], $params);
                    $stmt->execute();
                    $stmt->get_result();
                    $product_id = mysqli_insert_id($this->con);
                    $variationIds[$j] = $product_id;
                    $j++;
                    $sql = "INSERT INTO `" . DB_PREFIX . "product_variant` SET `product_id` = ?, variant_id = ?";
                    $params = array();
                    $params[] = 'ii';
                    $params[] = &$product_id;
                    $params[] = &$config_id;
                    $stmt = $this->con->prepare($sql);
                    call_user_func_array([$stmt, 'bind_param'], $params);
                    $stmt->execute();
                    $query = $stmt->get_result();
                    $colorName = $this->getOptNameByid($optValueId);
                    $name = $product_data['product_name'] . "-" . $colorName;
                    $description = $product_data['description'];
                    $language_id = $this->getLanguageId();
                    $sql = "INSERT INTO " . DB_PREFIX . "product_description SET product_id = ?, language_id = ?, name = ?, description = ?, tag = '', meta_title = ?, meta_description = '', meta_keyword = ''";
                    $params = array();
                    $params[] = 'iisss';
                    $params[] = &$product_id;
                    $params[] = &$language_id;
                    $params[] = &$name;
                    $params[] = &$description;
                    $params[] = &$name;
                    $stmt = $this->con->prepare($sql);
                    call_user_func_array([$stmt, 'bind_param'], $params);
                    $stmt->execute();
                    $stmt->get_result();
                    //Insert Store Product
                    $sql = "INSERT INTO " . DB_PREFIX . "product_to_store SET product_id = ?, store_id = ?";
                    $params = array();
                    $params[] = 'ii';
                    $params[] = &$product_id;
                    $params[] = &$this->store;
                    $stmt = $this->con->prepare($sql);
                    call_user_func_array([$stmt, 'bind_param'], $params);
                    $stmt->execute();
                    $stmt->get_result();
                    $oldConfId = $product_data['parent_product_id'];
                    $pv = $this->getProductInfo($oldConfId);
                    $variant_id = 0;
                    foreach ($pv as $var) {
                        if ($var['color_id'] == $optValueId) {
                            $variant_id = $var['var_id'];
                        }
                    }
                    $sql = "SELECT image FROM `" . DB_PREFIX . "product` WHERE  product_id = ?";
                    $params = array();
                    $params[] = 'i';
                    $params[] = &$variant_id;
                    $stmt = $this->con->prepare($sql);
                    call_user_func_array([$stmt, 'bind_param'], $params);
                    $stmt->execute();
                    $query = $stmt->get_result();
                    $row = mysqli_fetch_array($query);

                    $sql = "UPDATE " . DB_PREFIX . "product SET image = ? WHERE product_id = ?";
                    $params = array();
                    $params[] = 'si';
                    $params[] = &$row['image'];
                    $params[] = &$product_id;
                    $stmt = $this->con->prepare($sql);
                    call_user_func_array([$stmt, 'bind_param'], $params);
                    $stmt->execute();
                    $stmt->get_result();
                    // Added color option to the product.
                    $option_id = $varAttrColor['id'];
                    $sql = "INSERT INTO " . DB_PREFIX . "product_option SET product_id = ?, option_id = ?, value = '0', required = '1'";
                    $params = array();
                    $params[] = 'ii';
                    $params[] = &$product_id;
                    $params[] = &$option_id;
                    $stmt = $this->con->prepare($sql);
                    call_user_func_array([$stmt, 'bind_param'], $params);
                    $stmt->execute();
                    $stmt->get_result();
                    $product_option_id = mysqli_insert_id($this->con);
                    $value = 0;
                    if ($product_option_id) {
                        $sql = "INSERT INTO " . DB_PREFIX . "product_option_value SET product_option_id = ?, product_id = ?, option_id = ?, option_value_id = ?, quantity = ?, subtract = '1', price = ?, price_prefix = '+', points = '0', points_prefix = '+', weight = ?, weight_prefix = '+'";
                        $params = array();
                        $params[] = 'iiiiiss';
                        $params[] = &$product_option_id;
                        $params[] = &$product_id;
                        $params[] = &$option_id;
                        $params[] = &$optValueId;
                        $params[] = &$product_data['qty'];
                        $params[] = &$value;
                        $params[] = &$value;
                        $stmt = $this->con->prepare($sql);
                        call_user_func_array([$stmt, 'bind_param'], $params);
                        $stmt->execute();
                        $stmt->get_result();
                    }
                    // End
                    // Insert Other Option
                    if (!empty($varAttrOthers)) {
                        foreach ($varAttrOthers as $otherAttrValue) {
                            $attrOptionId = $otherAttrValue['id'];
                            if ($attrOptionId !== $varAttrColor['id']) {
                                $sql = "INSERT INTO " . DB_PREFIX . "product_option SET product_id = ?, option_id = ?, value = '0', required = '1'";
                                $params = array();
                                $params[] = 'ii';
                                $params[] = &$product_id;
                                $params[] = &$attrOptionId;
                                $stmt = $this->con->prepare($sql);
                                call_user_func_array([$stmt, 'bind_param'], $params);
                                $stmt->execute();
                                $stmt->get_result();
                                $product_option_id = mysqli_insert_id($this->con);
                                $value = 0;
                                if ($product_option_id) {
                                    foreach ($otherAttrValue['options'] as $optKey => $optValId) {
                                        $sql = "INSERT INTO " . DB_PREFIX . "product_option_value SET product_option_id = ?, product_id = ?, option_id = ?, option_value_id = ?, quantity = ?, subtract = '1', price = ?, price_prefix = '+', points = '0', points_prefix = '+', weight = ?, weight_prefix = '+'";
                                        $params = array();
                                        $params[] = 'iiiiiss';
                                        $params[] = &$product_option_id;
                                        $params[] = &$product_id;
                                        $params[] = &$attrOptionId;
                                        $params[] = &$optValId;
                                        $params[] = &$product_data['qty'];
                                        $params[] = &$value;
                                        $params[] = &$value;
                                        $stmt = $this->con->prepare($sql);
                                        call_user_func_array([$stmt, 'bind_param'], $params);
                                        $stmt->execute();
                                        $stmt->get_result();
                                    }
                                }
                            }
                        }
                    }
                    // End
                    // Insert Refid Option
                    if ($product_data['ref_id']) {
                        $refid = $product_data['ref_id'];
                    } else {
                        $refid = "";
                    }
                    $refOptionId = $this->getOptionIdByName("refid");
                    $sql = "INSERT INTO " . DB_PREFIX . "product_option SET product_id = ?, option_id = ?, value = ?, required = '0'";
                    $params = array();
                    $params[] = 'iis';
                    $params[] = &$product_id;
                    $params[] = &$refOptionId;
                    $params[] = &$refid;
                    $stmt = $this->con->prepare($sql);
                    call_user_func_array([$stmt, 'bind_param'], $params);
                    $stmt->execute();
                    $stmt->get_result();
                    // Insert xe_is_design Option
                    if ($product_data['is_customized']) {
                        $desOptId = $this->getOptionIdByName("xe_is_design");
                        $sql = "INSERT INTO " . DB_PREFIX . "product_option SET product_id = ?, option_id = ?, value = ?, required = '0'";
                        $params = array();
                        $params[] = 'iis';
                        $params[] = &$product_id;
                        $params[] = &$desOptId;
                        $params[] = &$product_data['is_customized'];
                        $stmt = $this->con->prepare($sql);
                        call_user_func_array([$stmt, 'bind_param'], $params);
                        $stmt->execute();
                        $stmt->get_result();
                    }
                    if (isset($product_data['categories'])) {
                        foreach ($product_data['categories'] as $categories) {
                            $category_id = $categories['category_id'];
                            $sql = "INSERT INTO " . DB_PREFIX . "product_to_category SET product_id = ?, category_id = ?";
                            $params = array();
                            $params[] = 'ii';
                            $params[] = &$product_id;
                            $params[] = &$category_id;
                            $stmt = $this->con->prepare($sql);
                            call_user_func_array([$stmt, 'bind_param'], $params);
                            $stmt->execute();
                            $stmt->get_result();
                        }
                    }
                    //Insert Product Images
                    $sql = "SELECT * FROM `" . DB_PREFIX . "product_image` WHERE  product_id = ?";
                    $params = array();
                    $params[] = 'i';
                    $params[] = &$variant_id;
                    $stmt = $this->con->prepare($sql);
                    call_user_func_array([$stmt, 'bind_param'], $params);
                    $stmt->execute();
                    $query = $stmt->get_result();
                    if ((mysqli_num_rows($query) > 0)) {
                        while ($row = mysqli_fetch_array($query)) {
                            $sql = "INSERT INTO " . DB_PREFIX . "product_image SET product_id = ?, image = ?, sort_order = ?";
                            $params = array();
                            $params[] = 'iss';
                            $params[] = &$product_id;
                            $params[] = &$row['image'];
                            $params[] = &$row['sort_order'];
                            $stmt = $this->con->prepare($sql);
                            call_user_func_array([$stmt, 'bind_param'], $params);
                            $stmt->execute();
                            $stmt->get_result();
                        }
                    }
                }
            }
            return array("id" => $config_id, "vid" => $variationIds);
        } else {
            return json_encode(array("status" => "failed"));
        }
    }

    public function getOptNameByid($id)
    {
        $name = "";
        $language_id = $this->getLanguageId();
        $sql = "SELECT name FROM `" . DB_PREFIX . "option_value_description` WHERE language_id=? and option_value_id=?";
        $params = array();
        $params[] = 'ii';
        $params[] = &$language_id;
        $params[] = &$id;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $query = $stmt->get_result();
        $color = mysqli_fetch_array($query);
        $name = $color['name'];
        return $name;
    }

    /*     * *** Addition of configurable product ***** */

    public function addConfigTemplateProduct($product_data)
    {
        $sql = "INSERT INTO " . DB_PREFIX . "product SET model = 'Customized',sku = ?, upc = '', ean = '', jan = '', isbn = '', mpn = '', location = '', quantity = ?, minimum = ?, subtract = '1', stock_status_id = '7', date_available = NOW(), manufacturer_id = '0', shipping = '1', price = ?, points = '0', weight = '0', weight_class_id = '1', length = '0', width = '0', height = '0', length_class_id = '1', status = '1', tax_class_id = '0', sort_order = '1', date_added = NOW(), date_modified = NOW(), is_variant= '0'";
        $product_data['mini_qty'] = empty($product_data['mini_qty']) ? '1' : $product_data['mini_qty'];
        $params = array();
        $params[] = 'ssss';
        $params[] = &$product_data['sku'];
        $params[] = &$product_data['qty'];
        $params[] = &$product_data['mini_qty'];
        $params[] = &$product_data['price'];
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $stmt->get_result();
        $product_id = mysqli_insert_id($this->con);
        $imageArr = array();
        if (isset($product_data['images'])) {
            foreach ($product_data['images'] as $imagesFile) {
                $imageFile = $imagesFile['src'];
                if ((strpos(stripslashes($imageFile), 'products/') !== false) || (strpos(stripslashes($imageFile), 'templates/') !== false) || (strpos(stripslashes($imageFile), 'temp/') !== false)) {
                    $filename = basename($imageFile);
                    $destinationImagePath = DIR_IMAGE . 'catalog/preDecoProduct/' . $product_id;
                    $destImageFile = $destinationImagePath . '/' . $filename;
                    $contents = fileGetContentsCurl($imageFile);
                    if (!file_exists($mkDirPreviw)) {
                        $exp = explode('/', $destinationImagePath);
                        foreach ($exp as $dir) {
                            $mkDirPreviw .= $dir . "/";
                            $dira[] = $mkDirPreviw;
                            if (!file_exists($mkDirPreviw)) {
                                mkdir($mkDirPreviw, 0755, true);
                            }
                        }
                    }
                    file_put_contents($destImageFile, $contents);
                    $imageArr[] = 'catalog/preDecoProduct/' . $product_id . '/' . $filename;
                } else {
                    $filename = explode("catalog/", $imageFile);
                    $filename = $filename[1];
                    $imageArr[] = 'catalog/' . $filename;
                }
            }
            $sql = "UPDATE " . DB_PREFIX . "product SET image = ? WHERE product_id = ?";
            $params = array();
            $params[] = 'ss';
            $params[] = &$imageArr[0];
            $params[] = &$product_id;
            $stmt = $this->con->prepare($sql);
            call_user_func_array([$stmt, 'bind_param'], $params);
            $stmt->execute();
            $stmt->get_result();
        }
        $language_id = $this->getLanguageId();
        $name = $product_data['product_name'];
        $description = $product_data['description'];
        $short_description = $product_data['short_description'];

        $sql = "INSERT INTO " . DB_PREFIX . "product_description SET product_id = ?, language_id = ?, name = ?, description = ?, tag = '', meta_title = ?, meta_description = ?, meta_keyword = ''";
        $params = array();
        $params[] = 'ssssss';
        $params[] = &$product_id;
        $params[] = &$language_id;
        $params[] = &$name;
        $params[] = &$description;
        $params[] = &$name;
        $params[] = &$short_description;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $stmt->get_result();
        //Insert Store Product
        $sql = "INSERT INTO " . DB_PREFIX . "product_to_store SET product_id = ?, store_id = ?";
        $params = array();
        $params[] = 'ii';
        $params[] = &$product_id;
        $params[] = &$this->store;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $stmt->get_result();
        $option_id = 0;
        foreach ($product_data['attributes'] as $attrValue) {
            $option_id = $attrValue['attribute_id'];
            $sql = "INSERT INTO " . DB_PREFIX . "product_option SET product_id = ?, option_id = ?, value = '0', required = '1'";
            $params = array();
            $params[] = 'ii';
            $params[] = &$product_id;
            $params[] = &$option_id;
            $stmt = $this->con->prepare($sql);
            call_user_func_array([$stmt, 'bind_param'], $params);
            $stmt->execute();
            $stmt->get_result();
            $product_option_id = mysqli_insert_id($this->con);
            $value = 0;
            if ($product_option_id != '') {
                foreach ($attrValue['attribute_options'] as $optValueId) {
                    $sql = "INSERT INTO " . DB_PREFIX . "product_option_value SET product_option_id = ?, product_id = ?, option_id = ?, option_value_id = ?, quantity = ?, subtract = '1', price = ?, price_prefix = '+', points = '0', points_prefix = '+', weight = ?, weight_prefix = '+'";
                    $params = array();
                    $params[] = 'iiiiiss';
                    $params[] = &$product_option_id;
                    $params[] = &$product_id;
                    $params[] = &$option_id;
                    $params[] = &$optValueId;
                    $params[] = &$product_data['qty'];
                    $params[] = &$value;
                    $params[] = &$value;
                    $stmt = $this->con->prepare($sql);
                    call_user_func_array([$stmt, 'bind_param'], $params);
                    $stmt->execute();
                    $stmt->get_result();
                }
            }
        }
        // Insert Refid Option
        if ($product_data['ref_id']) {
            $refid = $product_data['ref_id'];
        } else {
            $refid = "";
        }
        $refOptId = $this->getOptionIdByName("refid");
        $sql = "INSERT INTO " . DB_PREFIX . "product_option SET product_id = ?, option_id = ?, value = ?, required = '0'";
        $params = array();
        $params[] = 'iis';
        $params[] = &$product_id;
        $params[] = &$refOptId;
        $params[] = &$refid;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $stmt->get_result();
        // Insert xe_is_design Option
        if ($product_data['is_customized']) {
            $desOptId = $this->getOptionIdByName("xe_is_design");
            $sql = "INSERT INTO " . DB_PREFIX . "product_option SET product_id = ?, option_id = ?, value = ?, required = '0'";
            $params = array();
            $params[] = 'iis';
            $params[] = &$product_id;
            $params[] = &$desOptId;
            $params[] = &$product_data['is_customized'];
            $stmt = $this->con->prepare($sql);
            call_user_func_array([$stmt, 'bind_param'], $params);
            $stmt->execute();
            $stmt->get_result();
        }
        if (isset($product_data['categories'])) {
            foreach ($product_data['categories'] as $categories) {
                $category_id = $categories['category_id'];
                $sql = "INSERT INTO " . DB_PREFIX . "product_to_category SET product_id = ?, category_id = ?";
                $params = array();
                $params[] = 'ii';
                $params[] = &$product_id;
                $params[] = &$category_id;
                $stmt = $this->con->prepare($sql);
                call_user_func_array([$stmt, 'bind_param'], $params);
                $stmt->execute();
                $stmt->get_result();
            }
        }
        //Insert Product Images
        if (!empty($imageArr) && count($imageArr) > 1) {
            $count = 0;
            foreach ($imageArr as $product_image) {
                if ($count > 0) {
                    $sql = "INSERT INTO " . DB_PREFIX . "product_image SET product_id = ?, image = ?, sort_order = ?";
                    $params = array();
                    $params[] = 'iss';
                    $params[] = &$product_id;
                    $params[] = &$product_image;
                    $params[] = &$count;
                    $stmt = $this->con->prepare($sql);
                    call_user_func_array([$stmt, 'bind_param'], $params);
                    $stmt->execute();
                    $stmt->get_result();
                }
                $count++;
            }
        }
        return $product_id;
    }

    public function getStoreOrderStatus()
    {
        $statusResult = array();
        $language_id = $this->getLanguageId();
        $sql = "SELECT name FROM " . DB_PREFIX . "order_status WHERE language_id = ?";
        $params = array();
        $params[] = 'i';
        $params[] = &$language_id;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $query = $stmt->get_result();
        $i = 0;
        if ((mysqli_num_rows($query) > 0)) {
            while ($row = mysqli_fetch_array($query)) {
                $statusResult[$i]['key'] = $row['name'];
                $statusResult[$i]['value'] = $row['name'];
                $i++;
            }
        }
        return $statusResult;
    }

    public function modifyStoreOrderStatus($orderId, $status)
    {
        $orderHistoryId = false;
        $statusId = 0;
        $language_id = $this->getLanguageId();
        $sql = "SELECT order_status_id FROM " . DB_PREFIX . "order_status WHERE language_id = ? AND name = ?";
        $params = array();
        $params[] = 'is';
        $params[] = &$language_id;
        $params[] = &$status;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $query = $stmt->get_result();
        if ((mysqli_num_rows($query) > 0)) {
            $row = mysqli_fetch_assoc($query);
            $statusId = $row['order_status_id'];

            $sql = "UPDATE " . DB_PREFIX . "order SET `order_status_id` = ? WHERE order_id = ?";
            $params = array();
            $params[] = 'ii';
            $params[] = &$statusId;
            $params[] = &$orderId;
            $stmt = $this->con->prepare($sql);
            call_user_func_array([$stmt, 'bind_param'], $params);
            $orderHistoryId = $stmt->execute();
            // Update Order History
            if ($orderHistoryId) {
                $sqlOderHis = "INSERT INTO " . DB_PREFIX . "order_history SET order_id = ?, order_status_id = ?, notify = ?, comment = '', date_added = NOW()";
                $notify = '0';
                $params = array();
                $params[] = 'iss';
                $params[] = &$orderId;
                $params[] = &$statusId;
                $params[] = &$notify;
                $stmtOrderHis = $this->con->prepare($sqlOderHis);
                call_user_func_array([$stmtOrderHis, 'bind_param'], $params);
                $stmtOrderHis->execute();
                $stmtOrderHis->get_result();
            }
            $stmt->get_result();
        }
        return $orderHistoryId;
    }

    public function getProductOptionValueName($product_id, $option_value_id)
    {
        $value = "";
        $sql = "SELECT o.name FROM " . DB_PREFIX . "option_value_description o, " . DB_PREFIX . "product_option_value p WHERE p.option_value_id = o.option_value_id AND p.product_id=? AND p.product_option_value_id=?";
        $params = array();
        $params[] = 'ii';
        $params[] = &$product_id;
        $params[] = &$option_value_id;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $query = $stmt->get_result();
        if (mysqli_num_rows($query) > 0) {
            $row = mysqli_fetch_assoc($query);
            $value = $row['name'];
        }

        return $value;
    }

    public function getProductIdByVariantID($variantId)
    {
        $sqlVariant = "SELECT variant_id FROM " . DB_PREFIX . "product_variant WHERE product_id = ? LIMIT 1";
        $params = array();
        $params[] = 'i';
        $params[] = &$variantId;
        $stmts = $this->con->prepare($sqlVariant);
        call_user_func_array([$stmts, 'bind_param'], $params);
        $stmts->execute();
        $query = $stmts->get_result();
        $rows = mysqli_fetch_array($query);
        if (!empty($rows)) {
            $parentProductId = $rows['variant_id'];
        } else {
            $parentProductId = $variantId;
        }
        return $parentProductId;
    }

    /*
     * Method to get the tier discount
     * return array
     */

    public function getTierPrice($productId, $productPrice)
    {
        $tier = array();
        $todayDate = date("Y-m-d");
        $sql = "SELECT price, quantity FROM " . DB_PREFIX . "product_discount WHERE product_id = ? AND (date_start IS NULL  OR date_start >= ?) AND (date_end IS NULL OR date_end <= ?) ORDER BY quantity ASC";
        $params = array();
        $params[] = 'sss';
        $params[] = &$productId;
        $params[] = &$todayDate;
        $params[] = &$todayDate;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $tierExecute = $stmt->get_result();
        if (mysqli_num_rows($tierExecute) > 0) {
            $k = 0;
            while ($tierRow = mysqli_fetch_array($tierExecute)) {
                $tier[$k]['price'] = $tierRow['price'];
                $tier[$k]['percentage'] = round(100 - $tierRow['price'] / $productPrice * 100);
                $tier[$k]['quantity'] = $tierRow['quantity'];
                $k++;
            }
        }
        return $tier;
    }

    public function addCatalogProductToStore($data)
    {
        $price = $data['price'];
        $product_data = json_decode($data['product'], true);
        $colorAttr = 'color';
        $sizeAttr = 'size';
        $arraySize = $product_data['size_data'];
        $arrayColor = $product_data['color_data'];
        foreach ($product_data['category'] as $key => $value) {
            $this->createCategory($value);
        }
        // Create Attributes For Color
        if (!empty($arrayColor)) {
            $this->createAttribute($colorAttr, $arrayColor, 'select');
        }
        // Create Attributes For Size
        if (!empty($arraySize)) {
            $this->createAttribute($sizeAttr, $arraySize, 'select');
        }
        // for visible customize button
        $this->createAttribute('xe_is_design', array(), 'text');
        // for catalog product
        $this->createAttribute('is_catalog', array(), 'text');
        // for custom design id
        $this->createAttribute('refid', array(), 'text');

        $proId = $this->createProduct($product_data, $price);
        if (($proId > 0) && ($product_data['type'] == 'variable' && !empty($product_data['variations']))) {
            $this->createProductVariation($proId, $product_data, $price);
        }

        return $proId;
    }

    public function checkCreateAttributeValue($id, $value)
    {
        $optionValId = 0;
        $sql = "SELECT * FROM " . DB_PREFIX . "option_value_description WHERE `option_id` =  ? AND `name` = ?";
        $params = array();
        $params[] = 'is';
        $params[] = &$id;
        $params[] = &$value;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $query = $stmt->get_result();
        $row = mysqli_fetch_array($query);
        if (!empty($row)) {
            $optionValId = $row['option_value_id'];
        }
        return $optionValId;
    }

    /*
    - Name : createAttribute
    - it will create a attribute in store and assign the dummy product to it
    - Return attribute details in json
     */
    public function createAttribute($name, $attr, $type)
    {
        $status = 0;
        $sort_order = 0;
        $option_id = $this->getOptionIdByName($name);
        $language_id = $this->getLanguageId();
        $attrArray = array();
        if ($option_id == 0) {
            $sqlOpt = "INSERT INTO `" . DB_PREFIX . "option` SET type = ?, sort_order = ?";
            $params = array();
            $params[] = 'si';
            $params[] = &$type;
            $params[] = &$sort_order;
            $stmtOpt = $this->con->prepare($sqlOpt);
            call_user_func_array([$stmtOpt, 'bind_param'], $params);
            $resOpt = $stmtOpt->execute();
            $stmtOpt->get_result();
            if ($resOpt) {
                $option_id = mysqli_insert_id($this->con);
                $sqlOpt = "INSERT INTO " . DB_PREFIX . "option_description SET option_id = ?, language_id = ?, name = ?";
                $params = array();
                $params[] = 'iis';
                $params[] = &$option_id;
                $params[] = &$language_id;
                $params[] = &$name;
                $stmtOpt = $this->con->prepare($sqlOpt);
                call_user_func_array([$stmtOpt, 'bind_param'], $params);
                $resOpt = $stmtOpt->execute();
                $stmtOpt->get_result();
            }
        }
        if (!empty($attr)) {
            foreach ($attr as $value) {
                $opt_val_id = $this->checkCreateAttributeValue($option_id, $value);
                if ($opt_val_id == 0) {
                    $sqlOptVal = "INSERT INTO " . DB_PREFIX . "option_value SET option_id = ?, image = '', sort_order = ?";
                    $params = array();
                    $params[] = 'ii';
                    $params[] = &$option_id;
                    $params[] = &$sort_order;
                    $stmtOptVal = $this->con->prepare($sqlOptVal);
                    call_user_func_array([$stmtOptVal, 'bind_param'], $params);
                    $resOptVal = $stmtOptVal->execute();
                    $stmtOptVal->get_result();
                    if ($resOptVal) {
                        $option_value_id = mysqli_insert_id($this->con);
                        $sqlOptVal = "INSERT INTO " . DB_PREFIX . "option_value_description SET option_value_id = ?, language_id = ?, option_id = ?, name = ?";
                        $params = array();
                        $params[] = 'iiis';
                        $params[] = &$option_value_id;
                        $params[] = &$language_id;
                        $params[] = &$option_id;
                        $params[] = &$value;
                        $stmtOptVal = $this->con->prepare($sqlOptVal);
                        call_user_func_array([$stmtOptVal, 'bind_param'], $params);
                        $resOptVal = $stmtOptVal->execute();
                        $stmtOptVal->get_result();
                    }
                }
            }
        }
        if ($option_id != 0 && $opt_val_id != 0) {
            $status = 1;
        }
        return $status;
    }

    /*
    - Name : createCategory
    - it will create a category in the store.
    - Return collection details in json
     */
    public function createCategory($name)
    {
        $msg = '';
        $status = 0;
        $colID = $this->checkCreateCategory($name);
        if ($colID == 0) {
            $language_id = $this->getLanguageId();
            $sqlCat = "INSERT INTO " . DB_PREFIX . "category SET image = ?, parent_id = ?, `top` = ?, `column` = ?, sort_order = ?, status = ?, date_modified = NOW(), date_added = NOW()";
            $oneValue = '1';
            $zeroValue = '0';
            $emptyValue = '0';
            $params = array();
            $params[] = 'ssssss';
            $params[] = &$emptyValue;
            $params[] = &$zeroValue;
            $params[] = &$oneValue;
            $params[] = &$oneValue;
            $params[] = &$zeroValue;
            $params[] = &$oneValue;
            $stmtCat = $this->con->prepare($sqlCat);
            call_user_func_array([$stmtCat, 'bind_param'], $params);
            $res = $stmtCat->execute();
            $stmtCat->get_result();
            if ($res) {
                $category_id = mysqli_insert_id($this->con);

                $sqlCatDesc = "INSERT INTO " . DB_PREFIX . "category_description SET category_id = ?, language_id = ?, name = ?, description = '', meta_title = ?, meta_description = '', meta_keyword = ''";
                $params = array();
                $params[] = 'iiss';
                $params[] = &$category_id;
                $params[] = &$language_id;
                $params[] = &$name;
                $params[] = &$name;
                $stmtCatDesc = $this->con->prepare($sqlCatDesc);
                call_user_func_array([$stmtCatDesc, 'bind_param'], $params);
                $stmtCatDesc->execute();
                $stmtCatDesc->get_result();


                $sqlCatToStore = "INSERT INTO " . DB_PREFIX . "category_to_store SET category_id = ?, store_id = 0";
                $params = array();
                $params[] = 'i';
                $params[] = &$category_id;
                $stmtCatToStore = $this->con->prepare($sqlCatToStore);
                call_user_func_array([$stmtCatToStore, 'bind_param'], $params);
                $resCatToStore = $stmtCatToStore->execute();
                $stmtCatToStore->get_result();

                if ($resCatToStore) {
                    $sqlCatPath = "INSERT INTO " . DB_PREFIX . "category_path SET category_id = ?, path_id = ?, level=0";
                    $params = array();
                    $params[] = 'ii';
                    $params[] = &$category_id;
                    $params[] = &$category_id;
                    $stmtCatPath = $this->con->prepare($sqlCatPath);
                    call_user_func_array([$stmtCatPath, 'bind_param'], $params);
                    $resCatPath = $stmtCatPath->execute();
                    $stmtCatPath->get_result();
                    if ($resCatPath) {
                        $status = 1;
                    }
                } else {
                    $msg = $name . " category not created.";
                    $this->xe_log("\n" . date("Y-m-d H:i:s") . $msg . "\n");
                    $status = 0;
                }
            }
        }
        return $status;
    }

    /*
    - Name : checkCreateCategory
    - it will check if custom collection has been created or not
    - Return status created or not
     */
    public function checkCreateCategory($colName)
    {
        $sql = "SELECT category_id FROM " . DB_PREFIX . "category_description WHERE name = ?";
        $params = array();
        $params[] = 's';
        $params[] = &$colName;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $query = $stmt->get_result();
        $thisColID = mysqli_num_rows($query);

        return $thisColID;
    }

    public function createProduct($product_data, $price)
    {
        $sql = "INSERT INTO " . DB_PREFIX . "product SET model = 'Catalog',sku = ?, upc = '', ean = '', jan = '', isbn = '', mpn = '', location = '', quantity = ?, minimum = ?, subtract = '1', stock_status_id = '7', date_available = NOW(), manufacturer_id = '0', shipping = '1', price = ?, points = '0', weight = '0', weight_class_id = '1', length = '0', width = '0', height = '0', length_class_id = '1', status = '1', tax_class_id = '0', sort_order = '1', date_added = NOW(), date_modified = NOW(), is_variant= '0'";
        $product_data['mini_qty'] = empty($product_data['mini_qty']) ? '1' : $product_data['mini_qty'];
        $product_data['quantity'] = empty($product_data['quantity']) ? '1000' : $product_data['quantity'];
        $params = array();
        $params[] = 'ssss';
        $params[] = &$product_data['sku'];
        $params[] = &$product_data['quantity'];
        $params[] = &$product_data['mini_qty'];
        $params[] = &$price;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $stmt->get_result();
        $product_id = mysqli_insert_id($this->con);
        $imageArr = array();
        if (isset($product_data['images'])) {
            foreach ($product_data['images'] as $imgKey => $imageFile) {
                if ($imgKey == "src") {
                    $filename = basename($imageFile);
                    $destinationImagePath = DIR_IMAGE . 'catalog/catalogProduct/' . $product_id;
                    $destImageFile = $destinationImagePath . '/' . $filename;
                    $contents = fileGetContentsCurl($imageFile);
                    if (!file_exists($destinationImagePath)) {
                        $exp = explode('/', $destinationImagePath);
                        foreach ($exp as $dir) {
                            $mkDirPreviw .= $dir . "/";
                            $dira[] = $mkDirPreviw;
                            if (!file_exists($mkDirPreviw)) {
                                mkdir($mkDirPreviw, 0755, true);
                            }
                        }
                    }
                    file_put_contents($destImageFile, fileGetContentsCurl(trim($imageFile)));
                    $imageArr[] = 'catalog/catalogProduct/' . $product_id . '/' . $filename;
                }
            }
            $sql = "UPDATE " . DB_PREFIX . "product SET image = ? WHERE product_id = ?";
            $params = array();
            $params[] = 'ss';
            $params[] = &$imageArr[0];
            $params[] = &$product_id;
            $stmt = $this->con->prepare($sql);
            call_user_func_array([$stmt, 'bind_param'], $params);
            $stmt->execute();
            $stmt->get_result();
        }
        $language_id = $this->getLanguageId();
        $name = $product_data['name'];
        $description = $product_data['description'];

        $sql = "INSERT INTO " . DB_PREFIX . "product_description SET product_id = ?, language_id = ?, name = ?, description = ?, tag = '', meta_title = ?, meta_description = '', meta_keyword = ''";
        $params = array();
        $params[] = 'sssss';
        $params[] = &$product_id;
        $params[] = &$language_id;
        $params[] = &$name;
        $params[] = &$description;
        $params[] = &$name;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $stmt->get_result();
        //Insert Store Product
        $sql = "INSERT INTO " . DB_PREFIX . "product_to_store SET product_id = ?, store_id = ?";
        $params = array();
        $params[] = 'ii';
        $params[] = &$product_id;
        $params[] = &$this->store;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $stmt->get_result();
        $option_id = 0;
        foreach ($product_data['attributes'] as $attrValue) {
            $attrName = $attrValue['name'];
            $option_id = $this->getOptionIdByName($attrName);
            $sql = "INSERT INTO " . DB_PREFIX . "product_option SET product_id = ?, option_id = ?, value = '0', required = '1'";
            $params = array();
            $params[] = 'ii';
            $params[] = &$product_id;
            $params[] = &$option_id;
            $stmt = $this->con->prepare($sql);
            call_user_func_array([$stmt, 'bind_param'], $params);
            $stmt->execute();
            $stmt->get_result();
            $product_option_id = mysqli_insert_id($this->con);
            $value = 0;
            if ($product_option_id != '') {
                foreach ($attrValue['options'] as $optValue) {
                    $optValueId = $this->checkCreateAttributeValue($option_id, $optValue);
                    $sql = "INSERT INTO " . DB_PREFIX . "product_option_value SET product_option_id = ?, product_id = ?, option_id = ?, option_value_id = ?, quantity = ?, subtract = '1', price = ?, price_prefix = '+', points = '0', points_prefix = '+', weight = ?, weight_prefix = '+'";
                    $params = array();
                    $params[] = 'iiiiiss';
                    $params[] = &$product_option_id;
                    $params[] = &$product_id;
                    $params[] = &$option_id;
                    $params[] = &$optValueId;
                    $params[] = &$product_data['quantity'];
                    $params[] = &$value;
                    $params[] = &$value;
                    $stmt = $this->con->prepare($sql);
                    call_user_func_array([$stmt, 'bind_param'], $params);
                    $stmt->execute();
                    $stmt->get_result();
                }
            }
        }
        // Insert Refid Option
        $refid = "";
        $refOptId = $this->getOptionIdByName("refid");
        $sql = "INSERT INTO " . DB_PREFIX . "product_option SET product_id = ?, option_id = ?, value = ?, required = '0'";
        $params = array();
        $params[] = 'iis';
        $params[] = &$product_id;
        $params[] = &$refOptId;
        $params[] = &$refid;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $stmt->get_result();
        // Insert isCatlog Option
        $isCatalog = "1";
        $catOptId = $this->getOptionIdByName("is_catalog");
        $sql = "INSERT INTO " . DB_PREFIX . "product_option SET product_id = ?, option_id = ?, value = ?, required = '0'";
        $params = array();
        $params[] = 'iis';
        $params[] = &$product_id;
        $params[] = &$catOptId;
        $params[] = &$isCatalog;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $stmt->get_result();
        // Insert xe_is_design Option
        $is_customized = "1";
        $desOptId = $this->getOptionIdByName("xe_is_design");
        $sql = "INSERT INTO " . DB_PREFIX . "product_option SET product_id = ?, option_id = ?, value = ?, required = '0'";
        $params = array();
        $params[] = 'iis';
        $params[] = &$product_id;
        $params[] = &$desOptId;
        $params[] = &$is_customized;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $stmt->get_result();
        if (isset($product_data['category'])) {
            foreach ($product_data['category'] as $catName) {
                $category_id = $this->getCategoryId($catName);
                $sql = "INSERT INTO " . DB_PREFIX . "product_to_category SET product_id = ?, category_id = ?";
                $params = array();
                $params[] = 'ii';
                $params[] = &$product_id;
                $params[] = &$category_id;
                $stmt = $this->con->prepare($sql);
                call_user_func_array([$stmt, 'bind_param'], $params);
                $stmt->execute();
                $stmt->get_result();
            }
        }
        //Insert Product Images
        if (!empty($imageArr) && count($imageArr) > 1) {
            $count = 0;
            foreach ($imageArr as $product_image) {
                if ($count > 0) {
                    $sql = "INSERT INTO " . DB_PREFIX . "product_image SET product_id = ?, image = ?, sort_order = ?";
                    $params = array();
                    $params[] = 'iss';
                    $params[] = &$product_id;
                    $params[] = &$product_image;
                    $params[] = &$count;
                    $stmt = $this->con->prepare($sql);
                    call_user_func_array([$stmt, 'bind_param'], $params);
                    $stmt->execute();
                    $stmt->get_result();
                }
                $count++;
            }
        }
        return $product_id;
    }

    public function getCategoryId($name)
    {
        $categoryId = 0;
        $sql = "SELECT c.category_id FROM " . DB_PREFIX . "category c INNER JOIN " . DB_PREFIX . "category_description cd ON(c.category_id = cd.category_id) WHERE cd.name = ?";
        $params = array();
        $params[] = 's';
        $params[] = &$name;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $query = $stmt->get_result();
        if (mysqli_num_rows($query) > 0) {
            $row = mysqli_fetch_array($query);
            $categoryId = $row['category_id'];
        }
        return $categoryId;
    }

    public function createProductVariation($proId, $product_data, $price)
    {
        $varId = [];
        if ($proId != 0) {
            foreach ($product_data['variations'] as $varKey => $variation) {
                $product_id = 0;
                $isNew = 0;
                foreach ($variation['attributes'] as $attrKey => $attrValue) {
                    $attrName = $attrKey;
                    if ($attrName == "color") {
                        $attrName = $attrKey;
                        $option_id = $this->getOptionIdByName($attrName);
                        $optValueId = $this->checkCreateAttributeValue($option_id, $attrValue);
                        $product_id = $this->getExistingColorVariantId($proId, $option_id, $optValueId);
                    }
                }
                if ($product_id == 0) {
                    $isNew = 1;
                    $sql = "INSERT INTO " . DB_PREFIX . "product SET model = 'Catalog',sku = ?, upc = '', ean = '', jan = '', isbn = '', mpn = '', location = '', quantity = ?, minimum = ?, subtract = '1', stock_status_id = '7', date_available = NOW(), manufacturer_id = '0', shipping = '1', price = ?, points = '0', weight = '0', weight_class_id = '1', length = '0', width = '0', height = '0', length_class_id = '1', status = '1', tax_class_id = '0', sort_order = '1', date_added = NOW(), date_modified = NOW(), is_variant= '1'";
                    $variation['mini_qty'] = empty($variation['mini_qty']) ? '1' : $variation['mini_qty'];
                    $params = array();
                    $params[] = 'ssss';
                    $params[] = &$variation['sku'];
                    $params[] = &$variation['quantity'];
                    $params[] = &$variation['mini_qty'];
                    $params[] = &$price;
                    $stmt = $this->con->prepare($sql);
                    call_user_func_array([$stmt, 'bind_param'], $params);
                    $stmt->execute();
                    $stmt->get_result();
                    $product_id = mysqli_insert_id($this->con);
                    $sql = "INSERT INTO `" . DB_PREFIX . "product_variant` SET `product_id` = ?, variant_id = ?";
                    $params = array();
                    $params[] = 'ii';
                    $params[] = &$product_id;
                    $params[] = &$proId;
                    $stmt = $this->con->prepare($sql);
                    call_user_func_array([$stmt, 'bind_param'], $params);
                    $stmt->execute();
                    $stmt->get_result();
                }
                // attributes
                $language_id = $this->getLanguageId();
                foreach ($variation['attributes'] as $attrKey => $attrValue) {
                    $attrName = $attrKey;
                    if ($isNew) {
                        $option_id = $this->getOptionIdByName($attrName);
                        $optValueId = $this->checkCreateAttributeValue($option_id, $attrValue);
                        $sql = "INSERT INTO " . DB_PREFIX . "product_option SET product_id = ?, option_id = ?, value = '0', required = '1'";
                        $params = array();
                        $params[] = 'ii';
                        $params[] = &$product_id;
                        $params[] = &$option_id;
                        $stmt = $this->con->prepare($sql);
                        call_user_func_array([$stmt, 'bind_param'], $params);
                        $stmt->execute();
                        $stmt->get_result();
                        $product_option_id = mysqli_insert_id($this->con);
                        $value = 0;
                        if ($product_option_id != '') {
                            $sql = "INSERT INTO " . DB_PREFIX . "product_option_value SET product_option_id = ?, product_id = ?, option_id = ?, option_value_id = ?, quantity = ?, subtract = '1', price = ?, price_prefix = '+', points = '0', points_prefix = '+', weight = ?, weight_prefix = '+'";
                            $params = array();
                            $params[] = 'iiiiiss';
                            $params[] = &$product_option_id;
                            $params[] = &$product_id;
                            $params[] = &$option_id;
                            $params[] = &$optValueId;
                            $params[] = &$variation['quantity'];
                            $params[] = &$value;
                            $params[] = &$value;
                            $stmt = $this->con->prepare($sql);
                            call_user_func_array([$stmt, 'bind_param'], $params);
                            $stmt->execute();
                            $stmt->get_result();
                        }
                    } else {
                        if ($attrName == 'size') {
                            $option_id = $this->getOptionIdByName($attrName);
                            $optValueId = $this->checkCreateAttributeValue($option_id, $attrValue);
                            $product_option_id = $this->getExistingProductOptionId($product_id, $option_id);
                            if ($product_option_id == 0) {
                                $sql = "INSERT INTO " . DB_PREFIX . "product_option SET product_id = ?, option_id = ?, value = '0', required = '1'";
                                $params = array();
                                $params[] = 'ii';
                                $params[] = &$product_id;
                                $params[] = &$option_id;
                                $stmt = $this->con->prepare($sql);
                                call_user_func_array([$stmt, 'bind_param'], $params);
                                $stmt->execute();
                                $stmt->get_result();
                                $product_option_id = mysqli_insert_id($this->con);
                            }
                            $value = 0;
                            if ($product_option_id != '') {
                                $sql = "INSERT INTO " . DB_PREFIX . "product_option_value SET product_option_id = ?, product_id = ?, option_id = ?, option_value_id = ?, quantity = ?, subtract = '1', price = ?, price_prefix = '+', points = '0', points_prefix = '+', weight = ?, weight_prefix = '+'";
                                $params = array();
                                $params[] = 'iiiiiss';
                                $params[] = &$product_option_id;
                                $params[] = &$product_id;
                                $params[] = &$option_id;
                                $params[] = &$optValueId;
                                $params[] = &$variation['quantity'];
                                $params[] = &$value;
                                $params[] = &$value;
                                $stmt = $this->con->prepare($sql);
                                call_user_func_array([$stmt, 'bind_param'], $params);
                                $stmt->execute();
                                $stmt->get_result();
                            }
                        }
                    }
                }
                if ($isNew) {
                    if (isset($variation['attributes']['color'])) {
                        $name = $variation['style_name'] . "-" . $variation['attributes']['color'];
                    } else {
                        $name = $variation['style_name'];
                    }
                    $description = $product_data['description'];

                    $sql = "INSERT INTO " . DB_PREFIX . "product_description SET product_id = ?, language_id = ?, name = ?, description = ?, tag = '', meta_title = ?, meta_description = '', meta_keyword = ''";
                    $params = array();
                    $params[] = 'iisss';
                    $params[] = &$product_id;
                    $params[] = &$language_id;
                    $params[] = &$name;
                    $params[] = &$description;
                    $params[] = &$name;
                    $stmt = $this->con->prepare($sql);
                    call_user_func_array([$stmt, 'bind_param'], $params);
                    $stmt->execute();
                    $stmt->get_result();

                    //Insert Store Product
                    $sql = "INSERT INTO " . DB_PREFIX . "product_to_store SET product_id = ?, store_id = ?";
                    $params = array();
                    $params[] = 'ii';
                    $params[] = &$product_id;
                    $params[] = &$this->store;
                    $stmt = $this->con->prepare($sql);
                    call_user_func_array([$stmt, 'bind_param'], $params);
                    $stmt->execute();
                    $stmt->get_result();

                    // Product Images
                    $imageArr = array();
                    if (isset($variation['image_path'])) {
                        $img = 0;
                        $destImageFile = "";
                        $destinationImagePath = "";
                        $mkDirPreviw = '';
                        foreach ($variation['image_path'] as $imageFile) {
                            if ($imageFile) {
                                $filename = basename($imageFile);
                                $destinationImagePath = DIR_IMAGE . 'catalog/catalogProduct/' . $product_id;
                                $destImageFile = $destinationImagePath . '/' . $filename;
                                if (!file_exists($destinationImagePath)) {
                                    $exp = explode('/', $destinationImagePath);
                                    foreach ($exp as $dir) {
                                        $mkDirPreviw .= $dir . "/";
                                        $dira[] = $mkDirPreviw;
                                        if (!file_exists($destinationImagePath)) {
                                            mkdir($destinationImagePath, 0755, true);
                                        }
                                    }
                                }
                                file_put_contents($destImageFile, fileGetContentsCurl(trim($imageFile)));
                                $imageArr[$img] = 'catalog/catalogProduct/' . $product_id . '/' . $filename;
                                $img++;
                            }
                        }
                        $sql = "UPDATE " . DB_PREFIX . "product SET image = ? WHERE product_id = ?";
                        $params = array();
                        $params[] = 'ss';
                        $params[] = &$imageArr[0];
                        $params[] = &$product_id;
                        $stmt = $this->con->prepare($sql);
                        call_user_func_array([$stmt, 'bind_param'], $params);
                        $stmt->execute();
                        $stmt->get_result();
                    }

                    // Insert Refid Option
                    $refid = "";
                    $refOptId = $this->getOptionIdByName("refid");

                    $sql = "INSERT INTO " . DB_PREFIX . "product_option SET product_id = ?, option_id = ?, value = ?, required = '0'";
                    $params = array();
                    $params[] = 'iis';
                    $params[] = &$product_id;
                    $params[] = &$refOptId;
                    $params[] = &$refid;
                    $stmt = $this->con->prepare($sql);
                    call_user_func_array([$stmt, 'bind_param'], $params);
                    $stmt->execute();
                    $stmt->get_result();
                    // Insert xe_is_design Option
                    $is_customized = "1";
                    $isDesignOptId = $this->getOptionIdByName("xe_is_design");
                    $sql = "INSERT INTO " . DB_PREFIX . "product_option SET product_id = ?, option_id = ?, value = ?, required = '0'";
                    $params = array();
                    $params[] = 'iis';
                    $params[] = &$product_id;
                    $params[] = &$isDesignOptId;
                    $params[] = &$is_customized;
                    $stmt = $this->con->prepare($sql);
                    call_user_func_array([$stmt, 'bind_param'], $params);
                    $stmt->execute();
                    $stmt->get_result();
                    // Insert isCatlog Option
                    $isCatalog = "1";
                    $catOptId = $this->getOptionIdByName("is_catalog");

                    $sql = "INSERT INTO " . DB_PREFIX . "product_option SET product_id = ?, option_id = ?, value = ?, required = '0'";
                    $params = array();
                    $params[] = 'iis';
                    $params[] = &$product_id;
                    $params[] = &$catOptId;
                    $params[] = &$isCatalog;
                    $stmt = $this->con->prepare($sql);
                    call_user_func_array([$stmt, 'bind_param'], $params);
                    $stmt->execute();
                    $stmt->get_result();
                    if (isset($product_data['category'])) {
                        foreach ($product_data['category'] as $catName) {
                            $category_id = $this->getCategoryId($catName);
                            $sql = "INSERT INTO " . DB_PREFIX . "product_to_category SET product_id = ?, category_id = ?";
                            $params = array();
                            $params[] = 'ii';
                            $params[] = &$product_id;
                            $params[] = &$category_id;
                            $stmt = $this->con->prepare($sql);
                            call_user_func_array([$stmt, 'bind_param'], $params);
                            $stmt->execute();
                            $stmt->get_result();
                        }
                    }
                    //Insert Product Images
                    if (!empty($imageArr) && count($imageArr) > 1) {
                        $count = 0;
                        foreach ($imageArr as $product_image) {
                            if ($count > 0) {
                                $sql = "INSERT INTO " . DB_PREFIX . "product_image SET product_id = ?, image = ?, sort_order = ?";
                                $params = array();
                                $params[] = 'iss';
                                $params[] = &$product_id;
                                $params[] = &$product_image;
                                $params[] = &$count;
                                $stmt = $this->con->prepare($sql);
                                call_user_func_array([$stmt, 'bind_param'], $params);
                                $stmt->execute();
                                $stmt->get_result();
                            }
                            $count++;
                        }
                    }
                }
                $varId[$varKey] = $product_id;
            }
        }
        return $varId;
    }

    public function getExistingColorVariantId($productId, $optionId, $optValId)
    {
        $variantId = 0;
        $sql = "SELECT product_id FROM " . DB_PREFIX . "product_option_value WHERE `option_id`= ? AND `option_value_id` = ? AND `product_id` IN (SELECT product_id FROM " . DB_PREFIX . "product_variant WHERE variant_id = ?)";
        $params = array();
        $params[] = 'iii';
        $params[] = &$optionId;
        $params[] = &$optValId;
        $params[] = &$productId;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $query = $stmt->get_result();
        if (mysqli_num_rows($query) > 0) {
            $row = mysqli_fetch_array($query);
            $variantId = $row['product_id'];
        }

        return $variantId;
    }

    public function getExistingProductOptionId($productId, $optionId)
    {
        $productOptionId = 0;
        $sql = "SELECT product_option_id FROM " . DB_PREFIX . "product_option WHERE `option_id`= ? AND `product_id` = ?";
        $params = array();
        $params[] = 'ii';
        $params[] = &$optionId;
        $params[] = &$productId;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $query = $stmt->get_result();
        if (mysqli_num_rows($query) > 0) {
            $row = mysqli_fetch_array($query);
            $productOptionId = $row['product_option_id'];
        }
        return $productOptionId;
    }

    public function getProductOptionValDetails($productId, $optName, $optVal)
    {
        $options = array();
        if ($optVal) {
            $sql = "SELECT po.*,od.name,oo.type,ov.option_value_id,ov.name,pov.product_option_value_id FROM " . DB_PREFIX . "product_option po LEFT JOIN " . DB_PREFIX . "option_description od ON (po.option_id = od.option_id) LEFT JOIN " . DB_PREFIX . "option oo ON (oo.option_id = od.option_id) LEFT JOIN " . DB_PREFIX . "option_value_description ov ON (ov.option_id = od.option_id) LEFT JOIN " . DB_PREFIX . "product_option_value pov ON (pov.option_value_id = ov.option_value_id) WHERE po.product_id = ? AND od.name = ? AND ov.name = ? AND pov.product_id = ?";
            $params = array();
            $params[] = 'issi';
            $params[] = &$productId;
            $params[] = &$optName;
            $params[] = &$optVal;
            $params[] = &$productId;
        } else {
            $sql = "SELECT po.*,od.name,oo.type,ov.option_value_id,ov.name FROM " . DB_PREFIX . "product_option po LEFT JOIN " . DB_PREFIX . "option_description od ON (po.option_id = od.option_id) LEFT JOIN " . DB_PREFIX . "option oo ON (oo.option_id = od.option_id) LEFT JOIN " . DB_PREFIX . "option_value_description ov ON (ov.option_id = od.option_id) WHERE po.product_id = ? AND od.name = ?";
            $params = array();
            $params[] = 'is';
            $params[] = &$productId;
            $params[] = &$optName;
        }
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $query = $stmt->get_result();
        if (mysqli_num_rows($query) > 0) {
            $row = mysqli_fetch_assoc($query);
            $options = $row;
        }
        return $options;
    }

    public function getOptionDetailsByProductOptValId($productOptValId)
    {
        $resopnse = [];
        $sql = "SELECT * FROM " . DB_PREFIX . "product_option_value WHERE product_option_value_id = ?";
        $params = array();
        $params[] = 'i';
        $params[] = &$productOptValId;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $query = $stmt->get_result();
        $row = mysqli_fetch_array($query);
        if (!empty($row)) {
            $resopnse = $row;
        }

        return $resopnse;
    }

    public function isSoldOut($productId)
    {
        $isSoldOut = true;
        $sqlVariant = "SELECT product_id FROM " . DB_PREFIX . "product_variant WHERE variant_id = ?";
        $params = array();
        $params[] = 'i';
        $params[] = &$productId;
        $stmts = $this->con->prepare($sqlVariant);
        call_user_func_array([$stmts, 'bind_param'], $params);
        $stmts->execute();
        $query = $stmts->get_result();
        if (!empty(mysqli_fetch_array($query))) {
            while ($row = mysqli_fetch_array($query)) {
                $sql = "SELECT p.quantity FROM " . DB_PREFIX . "product p WHERE p.product_id = ?";
                $params = array();
                $params[] = 'i';
                $params[] = &$row['product_id'];
                $stmt = $this->con->prepare($sql);
                call_user_func_array([$stmt, 'bind_param'], $params);
                $stmt->execute();
                $query = $stmt->get_result();
                $row = mysqli_fetch_array($query);
                if ($row['quantity'] == 0) {
                    $isSoldOut = false;
                    break;
                }
            }
        }
        return $isSoldOut;
    }

    public function getIsDesignAndCustomDesignValue($product_id)
    {
        $productOptionData = array();
        $sql = "SELECT * FROM " . DB_PREFIX . "product_option po LEFT JOIN `" . DB_PREFIX . "option` o ON (po.option_id = o.option_id) LEFT JOIN " . DB_PREFIX . "option_description od ON (o.option_id = od.option_id) WHERE po.product_id = ? AND (od.name = 'refid' OR od.name = 'xe_is_design')";
        $params = array();
        $params[] = 'i';
        $params[] = &$product_id;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $productOptionQuery = $stmt->get_result();
        $i = 0;
        while ($row = mysqli_fetch_array($productOptionQuery)) {
            if (empty($row['value'])) {
                $row['value'] = 0;
            }
            $productOptionData[$i][$row['name']] = $row['value'];
        }
        return $productOptionData;
    }

    public function getPriceById($product_id)
    {
        $price = 0;
        $sql = "SELECT p.product_id,p.price FROM " . DB_PREFIX . "product p WHERE p.product_id=?";
        $params = array();
        $params[] = 'i';
        $params[] = &$product_id;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $query = $stmt->get_result();
        $row = mysqli_fetch_array($query);
        if (!empty($row)) {
            $price = $row = $row['price'];
        }

        return $price;
    }

    public function getOptionIdByName($name)
    {
        $sql = "SELECT option_id FROM `" . DB_PREFIX . "option_description` WHERE name = ?";
        $params = array();
        $params[] = 's';
        $params[] = &$name;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $query = $stmt->get_result();
        $row = mysqli_fetch_array($query);
        $option_id = $row['option_id'];
        return $option_id;
    }

    public function updateProductQty($productId, $qty)
    {
        $status = 0;
        $sql = "UPDATE " . DB_PREFIX . "product SET quantity = ? WHERE product_id = ?";
        $params = array();
        $params[] = 'ii';
        $params[] = &$qty;
        $params[] = &$productId;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $result = $stmt->execute();
        $stmt->get_result();
        if ($result) {
            $status = 1;
        }
        return $status;
    }

    public function getWeightDetailsByProduct($productId)
    {
        $weightRes = [];
        $language_id = $this->getLanguageId();
        $sql = "SELECT p.weight, wcd.*, wc.value FROM `" . DB_PREFIX . "product` p LEFT JOIN `" . DB_PREFIX . "weight_class_description` wcd ON (wcd.weight_class_id = p.weight_class_id) LEFT JOIN `" . DB_PREFIX . "weight_class` wc ON (wc.weight_class_id = wcd.weight_class_id) WHERE product_id = ? AND wcd.language_id = ?";
        $params = array();
        $params[] = 'ii';
        $params[] = &$productId;
        $params[] = &$language_id;
        $stmt = $this->con->prepare($sql);
        call_user_func_array([$stmt, 'bind_param'], $params);
        $stmt->execute();
        $query = $stmt->get_result();
        $row = mysqli_fetch_array($query);
        if (!empty($row)) {
            $weightRes = $row;
        }
        return $weightRes;
    }
}
