<?php

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

class Product_model extends MY_Model {

  public $rating_label = array(
      '1' => array("Worst Product", "Disappointed", "Waste of money"),
      '2' => array("Bad Product", "Dissatisfied"),
      '3' => array("Average Product", "Value for money", "Workable"),
      '4' => array("Nice Product", "Recommended"),
      '5' => array('Excellent', "Great Product", "Best in the market", "Highly recommended")
  );

  public function get_products($limit = '10', $offset = '0', $param = array()) {
    $sort_by = $this->db->escape_str(trim($this->input->get_post('sort_by', TRUE)));
    $category_id = @$param['category_id'];



    $status = @$param['status'];



    $productid = @$param['productid'];



    $orderby = @$param['orderby'];



    $groupby = @$param['groupby'];



    $where = @$param['where'];



    $price = @$param['price'];



    $newarrival = @$param['newarrival'];



    $bestseller = @$param['bestseller'];



    $popular = @$param['popular'];



    $color = @$param['color'];



    $size = @$param['size'];

    $brand = @$param['brand'];
    $style = @$param['style'];
    $room = @$param['room'];
    $latest_product = @$param['latest_product'];



    $keyword = trim($this->input->get_post('keyword', TRUE));

    $keyword = $this->db->escape_str($keyword);



    $search_keyword = trim($this->input->get_post('keywordSearch', TRUE));

    $search_keyword = $this->db->escape_str($search_keyword);







    if (!empty($newarrival)) {

      $this->db->where("wlp.newarrival_product = '" . $newarrival . "'");
    }

    //used in sitepanel only//
    if (@$sort_by != '') {
      if ($sort_by == 'Out Of Stock') {
        $this->db->where("wlp.product_qty = '0'");
      } else if ($sort_by == 'Low Stock') {
        $this->db->where("wlp.product_qty <= 10");
      } else if ($sort_by == 'In Stock') {
        $this->db->where("wlp.product_qty > 0");
      }
    }
    //used in sitepanel only//

    if (!empty($bestseller)) {

      $this->db->where("wlp.bestseller_product = '" . $bestseller . "'");
    }

    if (!empty($popular)) {

      $this->db->where("wlp.popular_product = '" . $popular . "'");
    }

    if (!empty($latest_product)) {

      $this->db->where("wlp.latest_product = '" . $latest_product . "'");
    }

    if (!empty($price)) {

      $price = explode('-', $price);

      if ($price[0] <= 0) {

        $this->db->where("wlp.product_price <= '$price[1]'");
      } elseif ($price[1] <= 0) {

        $this->db->where("wlp.product_price >= '$price[0]'");
      } else {

        $this->db->where("wlp.product_price between '$price[0]' AND '$price[1]'");
      }
    }

    if (!empty($color)) {

      if (strstr($color, ',')) {

        $cl = explode(',', $color);

        $clStr = "(";

        foreach ($cl as $c => $cval) {

          $clStr .= 'FIND_IN_SET (' . $cval . ',wlp.color_ids) OR ';
        }

        $clStr = substr($clStr, 0, -3);

        $clStr .= ")";

        $this->db->where($clStr);
      } else {

        $this->db->where("FIND_IN_SET ($color,wlp.color_ids)");
      }
    }

    if (!empty($size)) {

      if (strstr($size, ',')) {

        $cl = explode(',', $size);

        $clStr = '(';

        foreach ($cl as $c => $cval) {

          $clStr .= 'FIND_IN_SET (' . $cval . ',wlp.size_ids) OR ';
        }

        $clStr = substr($clStr, 0, -3);

        $clStr .= ')';

        $this->db->where($clStr);
      } else {

        $this->db->where("FIND_IN_SET ($size,wlp.size_ids)");
      }
    }

    if (!empty($brand)) {

      if (strstr($brand, ',')) {

        $brandcl = explode(',', $brand);

        $clStr = '(';

        foreach ($brandcl as $c => $brandcval) {

          $clStr .= 'FIND_IN_SET (' . $brandcval . ',wlp.brand_id) OR ';
        }

        $clStr = substr($clStr, 0, -3);

        $clStr .= ')';

        $this->db->where($clStr);
      } else {

        $this->db->where("FIND_IN_SET ($brand,wlp.brand_id)");
      }
    }

    if (!empty($style)) {

      if (strstr($style, ',')) {

        $stylecl = explode(',', $style);

        $clStr = '(';

        foreach ($stylecl as $c => $stylecval) {

          $clStr .= 'FIND_IN_SET (' . $stylecval . ',wlp.brand_id) OR ';
        }

        $clStr = substr($clStr, 0, -3);

        $clStr .= ')';

        $this->db->where($clStr);
      } else {

        $this->db->where("FIND_IN_SET ($style,wlp.style_id)");
      }
    }

    if (!empty($room)) {
      if (strstr($room, ',')) {
        $roomcl = explode(',', $room);
        $clStr = '(';
        foreach ($roomcl as $c => $roomcval) {
          $clStr .= 'FIND_IN_SET (' . $roomcval . ',wlp.room_id) OR ';
        }
        $clStr = substr($clStr, 0, -3);
        $clStr .= ')';
        $this->db->where($clStr);
      } else {
        $this->db->where("FIND_IN_SET ($room,wlp.room_id)");
      }
    }

    if ($category_id != '') {

      $this->db->where("FIND_IN_SET('" . $category_id . "',wlp.category_links) AND category_id!=''");
    }

    if ($productid != '') {

      $this->db->where("wlp.products_id  ", "$productid");
    }

    if ($status != '') {

      $this->db->where("wlp.status", "$status");
    }

    if ($where != '') {

      $this->db->where($where);
    }

    if ($keyword != '') {

      $this->db->where("(LOWER(wlp.product_name) LIKE '%" . strtolower($keyword) . "%' OR LOWER(wlp.product_code) LIKE '%" . strtolower($keyword) . "%')");
    }

    // if ($search_keyword != '') {
    //   $this->db->where("(LOWER(wlp.product_name) LIKE '%" . strtolower($search_keyword) . "%' OR LOWER(wlp.product_code) LIKE '%" . strtolower($search_keyword) . "%')");
    // }

    if ($search_keyword != '') {

      $this->db->where("(LOWER(wlp.product_name) LIKE '%" . strtolower($search_keyword) . "%' OR LOWER(wlp.product_code) LIKE '%" . strtolower($search_keyword) . "%')");
      $user_id = $this->session->userdata('user_id');
      if (!empty($user_id)) {
        $custom_id = $user_id;
      } else {
        $custom_id = '';
      }
      $data = array(
          'search_name' => $search_keyword,
          'customer_id' => $custom_id,
          'receive_date' => date('Y-m-d H:i:s'),
      );
      $enquiry_id = $this->safe_insert('wps_search_history', $data, FALSE);
    }

    if ($orderby != '') {

      $this->db->order_by($orderby);
    }

    if ($limit > 0) {

      if (applyFilter('NUMERIC_WT_ZERO', $offset) == -1) {

        $offset = 0;
      }

      $this->db->limit($limit, $offset);
    }



    if ($groupby) {

      $this->db->group_by($groupby);
    } else {

      $this->db->group_by("wlp.products_id");
    }

    $this->db->select('SQL_CALC_FOUND_ROWS wlp.*,wlpm.media,wlpm.media_type,wlpm.is_default', FALSE);

    $this->db->from('wps_products as wlp');

    if ($this->uri->segment(1) != 'sitepanel') {

      $this->db->where('wlp.status !=', '2');
    }
    $this->db->where('wlpm.is_default =', 'Y');

    $this->db->join('wps_products_media AS wlpm', 'wlp.products_id=wlpm.products_id', 'left');

    $q = $this->db->get();

    //echo_sql(); die;

    $result = $q->result_array();

    return $result;
  }

  public function get_product_media($limit = '5', $offset = '0', $param = array()) {

    $default = @$param['default'];

    $productid = @$param['productid'];

    $media_type = @$param['media_type'];



    if (is_array($param) && !empty($param)) {

      $this->db->select('SQL_CALC_FOUND_ROWS *', FALSE);

      $this->db->limit($limit, $offset);

      $this->db->from('wps_products_media');

      $this->db->where('products_id', $productid);
      $this->db->order_by("id", "asc");



      if ($default != '') {

        $this->db->where('is_default', $default);
      }

      if ($media_type != '') {

        $this->db->where('media_type', $media_type);
      }



      $q = $this->db->get();

      $result = $q->result_array();

      $result = ($limit == '1') ? $result[0] : $result;

      return $result;
    }
  }

  public function update_viewed($id, $counter = 0) {

    $id = (int) $id;

    if ($id > 0) {

      $posted_data = array(
          'products_viewed' => ($counter + 1)
      );

      $where = "products_id = '" . $id . "'";

      $this->category_model->safe_update('wps_products', $posted_data, $where, FALSE);
    }
  }

  public function get_related_products($condition) {

    $condtion = (!empty($condition)) ? "status !='2'  $condition" : "status !='2'";

    $fetch_config = array(
        'condition' => $condtion,
        'order' => "products_id DESC",
        'limit' => 'NULL',
        'start' => 'NULL',
        'debug' => FALSE,
        'return_type' => "array"
    );

    $result = $this->findAll('wps_products', $fetch_config);

    return $result;
  }

  public function related_products($res, $limit = 'NULL', $start = 'NULL') {

    $condtion = array();

    $condtion['where'] = "wlp.status ='1' AND wlp.products_id IN(SELECT wpr.related_id FROM wps_products_related as wpr WHERE wpr.product_id ='" . $res['products_id'] . "') ";

    $res_data = $this->get_products($limit, $start, $condtion);

    return $res_data;
  }

  public function product_attributes($param = array()) {

    $res_data = array();

    $where = @$param['where'];

    $limit = @$param['limit'];

    $offset = @$param['offset'];

    $query_attr = "SELECT * FROM wps_product_attributes  as wlc WHERE status!='2' AND ";

    if ($where != '') {

      $query_attr .= $where;
    }

    $query_attr = trim($query_attr, "AND");

    if ($limit > 0) {

      $query_attr .= " LIMIT $offset,$limit";
    }

    $q = $this->db->query($query_attr);

    $result = $q->result_array();

    //$res_total =  $this->db->query("Select FOUND_ROWS() as total")->row_array();
    //$this->total_recs = $res_total['total'];

    return $result;
  }

  public function get_product_base_price($param = array()) {

    $where = @$param['where'];

    $this->db->select('*', FALSE);

    $this->db->from('wps_product_attributes');

    if ($where != '') {

      $this->db->where($where);
    }

    $q = $this->db->get();

    // echo_sql();

    $result = $q->row_array();

    return $result;
  }

  public function related_sizes($param = array()) {

    $where = @$param['where'];

    $limit = @$param['limit'];

    $offset = @$param['offset'];



    $query_size = "SELECT SQL_CALC_FOUND_ROWS wls.size_name,wls.size_id,wls.status as size_status FROM wps_sizes as wls WHERE wls.status!='2' AND ";

    if ($where != '') {

      $query_size .= $where;
    }

    $query_size = trim($query_size, "AND");

    if ($limit > 0) {

      $query_size .= " LIMIT $offset,$limit";
    }

    $q = $this->db->query($query_size);

    $result = $q->result_array();

    $res_total = $this->db->query("Select FOUND_ROWS() as total")->row_array();

    $this->total_recs = $res_total['total'];

    return $result;
  }

  public function related_colors($param = array()) {

    $res_data = array();

    $where = @$param['where'];

    $limit = @$param['limit'];

    $offset = @$param['offset'];

    $query_size = "SELECT SQL_CALC_FOUND_ROWS wlc.color_name,wlc.status as color_status,wlc.color_code,wlc.color_id FROM wps_colors as wlc WHERE wlc.status!='2' AND ";

    if ($where != '') {

      $query_size .= $where;
    }

    $query_size = trim($query_size, "AND");

    if ($limit > 0) {

      $query_size .= " LIMIT $offset,$limit";
    }

    $q = $this->db->query($query_size);

    $result = $q->result_array();

    $res_total = $this->db->query("Select FOUND_ROWS() as total")->row_array();

    $this->total_recs = $res_total['total'];

    return $result;
  }

  public function get_size_quantity_old($colorId, $productId) {

    $qty = $this->db->query("SELECT * FROM wps_product_attributes WHERE product_id ='" . $productId . "'")->result_array();

    // print_r($qty); die;

    $size_quantity = array();

    $size = array();

    foreach ($qty as $key => $value) {

      $res_size = $this->db->query("SELECT size_name FROM wps_sizes WHERE status='1' AND size_id ='" . $value['size_id'] . "'")->row_array();

      $size_quantity['sizeName'] = $res_size['size_name'];

      $size_quantity['sizeId'] = $value['size_id'];

      $size_quantity['sizeQuantity'] = $value['quantity'];

      $size_quantity['price'] = $value['product_price'];

      $size_quantity['discountedPrice'] = $value['product_discounted_price'];

      $size[] = $size_quantity;
    }

    return $size;
  }

  public function get_size_quantity($colorId, $productId) {

    $qty = get_db_field_value("wps_products", "size_ids", "WHERE products_id ='" . $productId . "'");

    $sizes = explode(',', $qty);

    $size_quantity = array();

    $size = array();

    foreach ($sizes as $key => $value) {

      $res_size = $this->db->query("SELECT size_name FROM wps_sizes WHERE status='1' AND size_id ='" . $value . "'")->row_array();

      $sizeQty = $this->db->query("SELECT * FROM wps_product_attributes WHERE product_id ='" . $productId . "' AND color_id = '" . $colorId . "' AND size_id = '" . $value . "'")->row_array();

      $size_quantity['sizeName'] = $res_size['size_name'];

      $size_quantity['sizeId'] = $value;

      $size_quantity['sizeQuantity'] = (isset($sizeQty['quantity']) && $sizeQty['quantity'] > 0) ? $sizeQty['quantity'] : 0;

      $size_quantity['price'] = (isset($sizeQty['product_price']) && $sizeQty['product_price'] > 0) ? $sizeQty['product_price'] : 0;

      $size_quantity['discountedPrice'] = (isset($sizeQty['product_discounted_price']) && $sizeQty['product_discounted_price'] > 0) ? $sizeQty['product_discounted_price'] : 0;

      $size[] = $size_quantity;
    }

    return $size;
  }

  public function get_rating_by_product_user($productId, $userId) {

    return $this->db->select('ads_rating as rating')->from('wps_review')->where('product_id', $productId)->where('customer_id', $userId)->where('status', '1')->get()->row_array();
  }

  public function get_reviews_by_product($productId) {

    $reviews = $this->db->select('*')->from('wps_review')->where('product_id', $productId)->where('text !=', '')->where('ads_rating !=', '0')->where('status', '1')->get()->result_array();



    $reviews_with_ratings = $this->db->select('*')->from('wps_review')->where('product_id', $productId)->where('ads_rating !=', '0')->where('status', '1')->get()->result_array();



    $reviews_count = $this->db->select('COUNT(*) as total_reviews')->where('ads_rating !=', '0')->from('wps_review')->where('product_id', $productId)->where('status', '1')->where('text !=', '')->get()->result_array();

    $average_rating = $this->db->select('AVG(ads_rating) as average_review_rating')->from('wps_review')->where('product_id', $productId)->where('status', '1')->get()->result_array();



    $ratings_count = $this->db->select('COUNT(*) as total_ratings')->from('wps_review')->where('product_id', $productId)->where('status', '1')->where('text', '')->get()->result_array();

    // print_r($ratings_count[0]['total_ratings']);

    if (count($reviews_with_ratings) > 0) {

      $rating_detailed_count = array();

      $count_one = 0;

      $count_two = 0;

      $count_three = 0;

      $count_four = 0;

      $count_five = 0;

      $review_array = array();

      foreach ($reviews_with_ratings as $review_rating) {

        switch ($review_rating['ads_rating']) {

          case '1': {

              $count_one++;

              break;
            }

          case '2': {

              $count_two++;

              break;
            }

          case '3': {

              $count_three++;

              break;
            }

          case '4': {

              $count_four++;

              break;
            }

          case '5': {

              $count_five++;

              break;
            }

          default : break;
        }
      }

      if ($reviews > 0) {

        foreach ($reviews as $review) {

          $review_images = get_reviews_media($review['review_id']);

          $review_array[] = array(
              'reviewId' => $review['review_id'],
              'productId' => $review['product_id'],
              'comment' => $review['text'],
              'productRating' => $review['ads_rating'],
              'reviewImages' => $review_images,
              'userName' => $review['author'],
              'ratingLabel' => $review['rating_label']
          );
        }
      }

      $review_detailed_count = array(
          'oneCount' => $count_one,
          'twoCount' => $count_two,
          'threeCount' => $count_three,
          'fourCount' => $count_four,
          'fiveCount' => $count_five,
          'totalReviewsCount' => $reviews_count[0]['total_reviews'],
          'totalRatingsCount' => $ratings_count[0]['total_ratings'],
          'averageReviewsRatingCount' => $average_rating[0]['average_review_rating']
      );

      return $data = array('reviews' => $review_array, 'reviews_count' => $review_detailed_count);
    }
  }

  public function get_review_by_user($userId) {

    $reviews = $this->db->select('*')->from('wps_review')->where('customer_id', $userId)->where('status', '1')->where('text !=', '')->where('ads_rating !=', '0')->get()->result_array();

    if (count($reviews) > 0) {

      $review_array = array();

      foreach ($reviews as $review) {

        $product = get_product_by_id($review['product_id']);

        // print_r($product); die;

        $review_images = get_reviews_media($review['review_id']);

        $user = $this->users_model->get_member_row($review['customer_id']);

        // print_r($review_images); die;

        $review_array[] = array(
            'reviewId' => $review['review_id'],
            'productId' => $review['product_id'],
            'productName' => $review['author'],
            'productImage' => $product['image'],
            'comment' => $review['text'],
            'productRating' => $review['ads_rating'],
            'reviewImages' => $review_images,
            'userName' => $user['first_name'],
            'ratingLabel' => $review['rating_label']
        );
      }

      return $review_array;
    } else {

      return '0';
    }
  }

  public function remove_review_image($reviewImageId) {

    $this->db->set('status', '0');

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

    $this->db->update('wps_reviews_media');
  }

  public function getPrice($subcatId) {

    return $review = $this->db->select('max(product_price) as maxPrice, min(product_price) as minPrice')->from('wps_products')->where('category_id', $subcatId)->where('status', '1')->get()->row_array();
  }

  public function send_enquiry($name, $email, $mobile, $size, $comment) {
    $data = array(
        'type' => 2,
        'email' => $email,
        'first_name' => $name,
        'mobile_number' => $mobile,
        'message' => $comment,
        'product_size' => $size,
        'custom_image' => '',
        'receive_date' => date('Y-m-d H:i:s'),
    );
    $enquiry_id = $this->safe_insert('wps_enquiry', $data, FALSE);
    if (count($_FILES) > 0) {
      foreach ($_FILES["image"]["tmp_name"] as $key => $tmp_name) {
        $file_name = $_FILES["image"]["name"][$key];
        $file_tmp = $_FILES["image"]["tmp_name"][$key];
        $ext = pathinfo($file_name, PATHINFO_EXTENSION);
        $filename = basename($file_name, $ext);
        $newFileName = $filename . time() . "." . $ext;
        move_uploaded_file($file_tmp = $_FILES["image"]["tmp_name"][$key], "uploaded-files/custom_design/" . $newFileName);
        $uploaded_file[] = $newFileName;
      }
      $data2 = array(
          'custom_image' => implode(',', $uploaded_file),
      );
      $this->db->where(array('id' => $enquiry_id));
      $this->db->update('wps_enquiry', $data2);
    }
  }

  public function review($productId, $rating, $userId, $comment) {

    $userDets = get_db_single_row("wps_customers", "first_name, last_name, email", "customers_id = '" . $userId . "'");

    $label_for_rating = '';

    foreach ($this->rating_label as $prating => $rating_label) {

      if ($prating == $rating) {

        $label_for_rating = $rating_label[rand(0, count($rating_label) - 1)];
      }
    }

    $review = $this->db->select('*')->from('wps_review')->where('customer_id', $userId)->where('product_id', $productId)->where('status', '1')->get()->row_array();
    $reviewId = $review['review_id'];

    if (count($review) > 0) {

      $data = array(
          'ads_rating' => $rating,
          'text' => $comment,
          'rating_label' => $label_for_rating
      );

      $this->db->where(array('customer_id' => $userId, 'product_id' => $productId));

      $this->db->update('wps_review', $data);
    } else {

      $data = array(
          'product_id' => $productId,
          'ads_rating' => $rating,
          'customer_id' => $userId,
          'text' => $comment,
          'author' => $userDets['first_name'] . ' ' . $userDets['last_name'],
          'author_email' => $userDets['email'],
          'entity_type' => 'product',
          'rating_label' => $label_for_rating,
          'review_date' => date('Y-m-d'),
      );

      $reviewId = $this->safe_insert('wps_review', $data, FALSE);
    }

    if (count($_FILES) > 0) {

      foreach ($_FILES["image"]["tmp_name"] as $key => $tmp_name) {

        $file_name = $_FILES["image"]["name"][$key];

        $file_tmp = $_FILES["image"]["tmp_name"][$key];

        $ext = pathinfo($file_name, PATHINFO_EXTENSION);

        $filename = basename($file_name, $ext);

        $newFileName = $filename . time() . "." . $ext;

        move_uploaded_file($file_tmp = $_FILES["image"]["tmp_name"][$key], "uploaded-files/review_images/" . $newFileName);

        $data_media = array(
            'review_id' => $reviewId,
            'media_type' => 'photo',
            'media' => $newFileName
        );

        $this->safe_insert('wps_reviews_media', $data_media, FALSE);
      }
    }
  }

  public function deleteReview($reviewId, $userId) {

    $review = $this->db->select('*')->from('wps_review')->where('review_id', $reviewId)->where('customer_id', $userId)->get()->row_array();

    if (count($review) > 0) {

      $this->db->set('status', '0');

      $this->db->where('review_id', $reviewId);

      $this->db->update('wps_review');

      return true;
    } else {

      return '0';
    }
  }

  public function add_bulk_upload_product($worksheet) {
    
    for ($i = 2; $i <= count($worksheet); $i++) {

      $imageLink = $filename = '';
      $imageLink2 = $filename2 = '';
      $imageLink3 = $filename3 = '';
      $imageLink4 = $filename4 = '';

      echo $category_ids = (!isset($worksheet[$i][1])) ? '' : addslashes(trim($worksheet[$i][1]));
      exit;
      $product_name = (!isset($worksheet[$i][2])) ? '' : addslashes(trim($worksheet[$i][2]));
      $product_code = (!isset($worksheet[$i][3])) ? '' : addslashes(trim($worksheet[$i][3]));
      $price = (!isset($worksheet[$i][4])) ? '' : addslashes(trim($worksheet[$i][4]));
      $discounted_price = (!isset($worksheet[$i][5])) ? '' : addslashes(trim($worksheet[$i][5]));

      $qty = (!isset($worksheet[$i][6])) ? '' : addslashes(trim($worksheet[$i][6]));
      $dimension = (!isset($worksheet[$i][7])) ? '' : addslashes(trim($worksheet[$i][7]));
      $color = (!isset($worksheet[$i][8])) ? '' : addslashes(trim($worksheet[$i][8]));
      $youtubeLink = (!isset($worksheet[$i][9])) ? '' : addslashes(trim($worksheet[$i][9]));
      $size = (!isset($worksheet[$i][10])) ? '' : addslashes(trim($worksheet[$i][10]));
      $sizeVal = (!isset($worksheet[$i][11])) ? '' : addslashes(trim($worksheet[$i][11]));
      $sizeValArr = explode(',', $sizeVal);
      $weight = (!isset($worksheet[$i][12])) ? '' : addslashes(trim($worksheet[$i][12]));
      $weightVal = (!isset($worksheet[$i][13])) ? '' : addslashes(trim($worksheet[$i][13]));
      $weightValArr = explode(',', $weightVal);

      $sgst = (!isset($worksheet[$i][14])) ? '' : addslashes(trim($worksheet[$i][14]));
      $cgst = (!isset($worksheet[$i][15])) ? '' : addslashes(trim($worksheet[$i][15]));
      $igst = (!isset($worksheet[$i][16])) ? '' : addslashes(trim($worksheet[$i][16]));

      $shortdescription = (!isset($worksheet[$i][17])) ? '' : addslashes(trim($worksheet[$i][17]));
      $description = (!isset($worksheet[$i][18])) ? '' : addslashes(trim($worksheet[$i][18]));
      $disclaimer = (!isset($worksheet[$i][19])) ? '' : addslashes(trim($worksheet[$i][19]));
      $techSpecs = (!isset($worksheet[$i][20])) ? '' : addslashes(trim($worksheet[$i][20]));

      $imageLink = (!isset($worksheet[$i][21])) ? '' : addslashes(trim($worksheet[$i][21]));
      $imageLink2 = (!isset($worksheet[$i][22])) ? '' : addslashes(trim($worksheet[$i][22]));
      $imageLink3 = (!isset($worksheet[$i][23])) ? '' : addslashes(trim($worksheet[$i][23]));
      $imageLink4 = (!isset($worksheet[$i][24])) ? '' : addslashes(trim($worksheet[$i][24]));

      $qtyArr = explode(',', $qty);
      $priArr = explode(',', $price);
      $dprArr = explode(',', $discounted_price);

      $postCategory = explode(',', $category_ids);
//      trace($postCategory);
//        exit;
      $category_links = "";
      $catlink = $ctl = "";
      foreach ($postCategory as $ctv) {
        $catlink = get_parent_categories($ctv, "AND status='1'", "category_id,parent_id");
        $ctl = array_keys($catlink);
        //trace($ctl);
        $category_links .= implode(",", $ctl) . ',';
      }
      $category_links = substr($category_links, 0, -1);
      $seo_url = seo_url_title($product_name);

      if ($imageLink != '') {
        $imageLink = str_replace('?dl=0', '?dl=1', $imageLink);
        $url = $imageLink;
        $image_ext = explode('.', $imageLink);
        $image_ext = array_reverse($image_ext);
        $filename = seo_url_title($product_name) . '_' . date('Ymhi') . rand() . '.' . str_replace('?dl=1', '', $image_ext[0]);
        //get File content and save to destination path
        $blob = file_get_contents($url);
        $image = base64_encode($blob);
        $dataImg = base64_decode($image);
        //echo '<img src="data:image/jpg;base64,'.$image.'">';
        $success = file_put_contents(UPLOAD_DIR . '/product_images/' . $filename, $dataImg);
        //print $success ? $file : 'Unable to save the file.';
        //die;
      }
      if ($imageLink2 != '') {
        $imageLink2 = str_replace('?dl=0', '?dl=1', $imageLink2);
        $url = $imageLink2;
        $image_ext = explode('.', $imageLink2);
        $image_ext = array_reverse($image_ext);
        $filename2 = seo_url_title($product_name) . '_' . date('Ymhi') . rand() . '.' . str_replace('?dl=1', '', $image_ext[0]);
        //get File content and save to destination path
        $blob = file_get_contents($url);
        $image = base64_encode($blob);
        $dataImg = base64_decode($image);
        //echo '<img src="data:image/jpg;base64,'.$image.'">';
        $success = file_put_contents(UPLOAD_DIR . '/product_images/' . $filename2, $dataImg);
        //print $success ? $file : 'Unable to save the file.';
        //die;
      }
      if ($imageLink3 != '') {
        $imageLink3 = str_replace('?dl=0', '?dl=1', $imageLink3);
        $url = $imageLink3;
        $image_ext = explode('.', $imageLink3);
        $image_ext = array_reverse($image_ext);
        $filename3 = seo_url_title($product_name) . '_' . date('Ymhi') . rand() . '.' . str_replace('?dl=1', '', $image_ext[0]);
        //get File content and save to destination path
        $blob = file_get_contents($url);
        $image = base64_encode($blob);
        $dataImg = base64_decode($image);
        //echo '<img src="data:image/jpg;base64,'.$image.'">';
        $success = file_put_contents(UPLOAD_DIR . '/product_images/' . $filename3, $dataImg);
        //print $success ? $file : 'Unable to save the file.';
        //die;
      }
      if ($imageLink4 != '') {
        $imageLink4 = str_replace('?dl=0', '?dl=1', $imageLink4);
        $url = $imageLink4;
        $image_ext = explode('.', $imageLink4);
        $image_ext = array_reverse($image_ext);
        $filename4 = seo_url_title($product_name) . '_' . date('Ymhi') . rand() . '.' . str_replace('?dl=1', '', $image_ext[0]);
        //get File content and save to destination path
        $blob = file_get_contents($url);
        $image = base64_encode($blob);
        $dataImg = base64_decode($image);
        //echo '<img src="data:image/jpg;base64,'.$image.'">';
        $success = file_put_contents(UPLOAD_DIR . '/product_images/' . $filename4, $dataImg);
        //print $success ? $file : 'Unable to save the file.';
        //die;
      }

      if ($product_code == "") {
        $product_code = str_pad(mt_rand(0, 999999), 6, '0', STR_PAD_LEFT);
        $product_code = 'Toyzoo' . $product_code . date('Ym');
      }

      $posted_data = array(
          'category_id' => $category_ids,
          'category_links' => $category_links,
          'product_name' => $product_name,
          'friendly_url' => $seo_url,
          'product_code' => $product_code,
          //'product_qty' => $qty,
          'size_ids' => $size,
          'size_vals' => $sizeVal,
          'weight_ids' => $weight,
          'weight_vals' => $weightVal,
          //'product_price' => $price,
          'youtube_link' => $youtubeLink,
          'dimension' => $dimension,
          'color' => $color,
          //'product_discounted_price' => $discounted_price,
          'sgst' => $sgst,
          'cgst' => $cgst,
          'igst' => $igst,
          'product_alt' => $product_name,
          'products_description' => $description,
          'short_description' => $shortdescription,
          'products_custom_description' => $disclaimer,
          'products_tech_description' => $techSpecs,
          'product_added_date' => $this->config->item('config.date.time')
      );
      //trace($data);
      //die;
      $product_id = $this->safe_insert('wl_products', $posted_data, FALSE);

      //Update Media
      if ($product_id > 0) {
        $productId = $product_id;
        //Update Stock
        if ($sizeVal != '') {
          $sl = 0;
          foreach ($sizeValArr as $wk => $wval) {
            if ($wval > 0) {
              $posted_data_size = array(
                  'product_id' => $productId,
                  'size_id' => $wval,
                  'quantity' => $qtyArr[$wk],
                  'product_price' => $priArr[$wk],
                  'product_discounted_price' => $dprArr[$wk],
              );
              //trace($posted_data);
              $this->product_model->safe_insert('wl_product_attributes', $posted_data_size, FALSE);
              if ($sl == 0) {
                $this->db->query("UPDATE wl_products SET product_qty = '" . $qtyArr[$wk] . "', product_price = '" . $priArr[$wk] . "', product_discounted_price = '" . $dprArr[$wk] . "' WHERE products_id = '" . $productId . "'");
              }
              $sl++;
            }
          }
        } else {
          $sl = 0;
          foreach ($weightValArr as $wk => $wval) {
            if ($wval > 0) {
              $posted_data_weight = array(
                  'product_id' => $productId,
                  'weight_id' => $wval,
                  'quantity' => $qtyArr[$wk],
                  'product_price' => $priArr[$wk],
                  'product_discounted_price' => $dprArr[$wk],
              );
              //trace($posted_data);
              $this->product_model->safe_insert('wl_product_attributes', $posted_data_weight, FALSE);
              if ($sl == 0) {
                $this->db->query("UPDATE wl_products SET product_qty = '" . $qtyArr[$wk] . "', product_price = '" . $priArr[$wk] . "', product_discounted_price = '" . $dprArr[$wk] . "' WHERE products_id = '" . $productId . "'");
              }
              $sl++;
            }
          }
        }
        //End Here

        if ($filename != '') {
          $dataMedia = array(
              'products_id' => $product_id,
              'media' => $filename,
              'is_default' => 'Y',
              'media_date_added' => $this->config->item('config.date.time'),
          );
          $this->safe_insert('wl_products_media', $dataMedia, FALSE);
        }
        if ($filename2 != '') {
          $dataMedia = array(
              'products_id' => $product_id,
              'media' => $filename2,
              'is_default' => 'N',
              'media_date_added' => $this->config->item('config.date.time'),
          );
          $this->safe_insert('wl_products_media', $dataMedia, FALSE);
        }
        if ($filename3 != '') {
          $dataMedia = array(
              'products_id' => $product_id,
              'media' => $filename3,
              'is_default' => 'N',
              'media_date_added' => $this->config->item('config.date.time'),
          );
          $this->safe_insert('wl_products_media', $dataMedia, FALSE);
        }
        if ($filename4 != '') {
          $dataMedia = array(
              'products_id' => $product_id,
              'media' => $filename4,
              'is_default' => '4',
              'media_date_added' => $this->config->item('config.date.time'),
          );
          $this->safe_insert('wl_products_media', $dataMedia, FALSE);
        }

        //Create Meta
        $redirect_url = "products/detail";
        $meta_array = array(
            'entity_type' => $redirect_url,
            'entity_id' => $product_id,
            'page_url' => $seo_url,
            'meta_title' => get_text($product_name, 80),
            'meta_description' => get_text($description),
            'meta_keyword' => get_keywords($description)
        );
        create_meta($meta_array);
      }
    }
    //exit;
    return true;
  }

  public function add_bulk_edit_product($worksheet) {
    for ($i = 2; $i <= count($worksheet); $i++) {

      $product_code = (!isset($worksheet[$i][1])) ? '' : addslashes(trim($worksheet[$i][1]));
      $price = (!isset($worksheet[$i][1])) ? '' : addslashes(trim($worksheet[$i][2]));
      $discounted_price = (!isset($worksheet[$i][3])) ? '' : addslashes(trim($worksheet[$i][3]));
      $qty = (!isset($worksheet[$i][4])) ? '' : addslashes(trim($worksheet[$i][4]));

      $youtubeLink = (!isset($worksheet[$i][5])) ? '' : addslashes(trim($worksheet[$i][5]));
      $size = (!isset($worksheet[$i][6])) ? '' : addslashes(trim($worksheet[$i][6]));
      $sizeVal = (!isset($worksheet[$i][7])) ? '' : addslashes(trim($worksheet[$i][7]));
      $sizeValArr = explode(',', $sizeVal);

      $weight = (!isset($worksheet[$i][8])) ? '' : addslashes(trim($worksheet[$i][8]));
      $weightVal = (!isset($worksheet[$i][9])) ? '' : addslashes(trim($worksheet[$i][9]));
      $weightValArr = explode(',', $weightVal);

      $sgst = (!isset($worksheet[$i][10])) ? '' : addslashes(trim($worksheet[$i][10]));
      $cgst = (!isset($worksheet[$i][11])) ? '' : addslashes(trim($worksheet[$i][11]));
      $igst = (!isset($worksheet[$i][12])) ? '' : addslashes(trim($worksheet[$i][12]));

      $techSpecs = (!isset($worksheet[$i][13])) ? '' : addslashes(trim($worksheet[$i][13]));
      $l = (!isset($worksheet[$i][14])) ? '' : addslashes(trim($worksheet[$i][14]));
      $h = (!isset($worksheet[$i][15])) ? '' : addslashes(trim($worksheet[$i][15]));
      $w = (!isset($worksheet[$i][16])) ? '' : addslashes(trim($worksheet[$i][16]));

      $qtyArr = explode(',', $qty);
      $priArr = explode(',', $price);
      $dprArr = explode(',', $discounted_price);

      if ($product_code != "") {
        $prList = $this->db->query("SELECT products_id FROM wl_products WHERE product_code = '" . $product_code . "'")->result_array();
        if (is_array($prList) && !empty($prList)) {
          foreach ($prList as $proVal) {

            $productId = $proVal['products_id'];
            $posted_data = array(
                'size_ids' => $size,
                'size_vals' => $sizeVal,
                'weight_ids' => $weight,
                'weight_vals' => $weightVal,
                'youtube_link' => $youtubeLink,
                'sgst' => $sgst,
                'cgst' => $cgst,
                'igst' => $igst,
                'products_tech_description' => $techSpecs,
                'package_length' => $l,
                'package_height' => $h,
                'package_width' => $w,
            );
            //trace($posted_data);
            //die;
            $where = "products_id = '" . $productId . "'";
            $this->safe_update('wl_products', $posted_data, $where, FALSE);

            //Update Stock
            if ($sizeVal != '') {
              $sl = 0;
              if (is_array($sizeValArr) && !empty($sizeValArr)) {
                //Delete Previous Records
                $this->db->query("DELETE FROM wl_product_attributes WHERE product_id = '" . $productId . "'");
                foreach ($sizeValArr as $wk => $wval) {
                  if ($wval > 0) {
                    $posted_data_size = array(
                        'product_id' => $productId,
                        'size_id' => $wval,
                        'quantity' => $qtyArr[$wk],
                        'product_price' => $priArr[$wk],
                        'product_discounted_price' => $dprArr[$wk],
                    );
                    //trace($posted_data);
                    $this->product_model->safe_insert('wl_product_attributes', $posted_data_size, FALSE);
                    if ($sl == 0) {
                      $this->db->query("UPDATE wl_products SET product_qty = '" . $qtyArr[$wk] . "', product_price = '" . $priArr[$wk] . "', product_discounted_price = '" . $dprArr[$wk] . "' WHERE products_id = '" . $productId . "'");
                    }
                    $sl++;
                  }
                }
              }
            } else {
              $sl = 0;
              if (is_array($weightValArr) && !empty($weightValArr)) {
                $this->db->query("DELETE FROM wl_product_attributes WHERE product_id = '" . $productId . "'");
                foreach ($weightValArr as $wk => $wval) {
                  if ($wval > 0) {
                    $posted_data_weight = array(
                        'product_id' => $productId,
                        'weight_id' => $wval,
                        'quantity' => $qtyArr[$wk],
                        'product_price' => $priArr[$wk],
                        'product_discounted_price' => $dprArr[$wk],
                    );
                    //trace($posted_data);
                    $this->product_model->safe_insert('wl_product_attributes', $posted_data_weight, FALSE);
                    if ($sl == 0) {
                      $this->db->query("UPDATE wl_products SET product_qty = '" . $qtyArr[$wk] . "', product_price = '" . $priArr[$wk] . "', product_discounted_price = '" . $dprArr[$wk] . "' WHERE products_id = '" . $productId . "'");
                    }
                    $sl++;
                  }
                }
              }
            }
            //End Here
          }
        }
      }
    }
    //exit;
    return true;
  }

}
