<?php
class Product_model extends CI_Model
{
	public function __construct()
	{
		parent::__construct();
	}

    function getProductsForLookUp()
    {
        $sql = 'SELECT product_row_id,
                product_description
                FROM tbl_product
                WHERE merchant_row_id = ' . getCurrentMerchantId() . '
                AND active_status = 1
				AND is_gift_certificate = 0
				AND is_editable = 1
                ORDER BY product_description';
        $resultSet = $this->db->query($sql);

        return $resultSet->result_array();
    }

    /*add a product in the product table*/
	function saveProduct ($productData, $merchant_aff_program_row_id = 0)
	{

        $termsConditions = '';
        if(isset($productData['terms_conditions'])){
            if(!empty($productData['terms_conditions'])){
                $termsConditions = $productData['terms_conditions'];
            }
            unset($productData['terms_conditions']);
        }

        if(isset($productData['longDesc']) && $productData['longDesc'])
        {
            $look_and_feel['longDesc']  = $productData['longDesc'];
        }
        if(isset($productData['shortDesc']) && $productData['shortDesc'])
        {
            $look_and_feel['shortDesc'] = $productData['shortDesc'];
        }

        unset($productData['shortDesc']);
        unset($productData['longDesc']);

        if (!empty($productData['product_row_id']))
		{
            $enc_product_row_id = md5($productData['product_row_id']);
			$productData['modified_by'] = getCurrentMerchantId();
			$productData['modified_on'] = getCurrentDateTimeForDB();

            if((isset($productData['digital_product_file_name']) && $productData['digital_product_file_name']!="" ) || (isset($productData['digital_product_file_type']) && $productData['digital_product_file_type']!="") || (isset($productData['digital_product_path']) && $productData['digital_product_path']!="") || $productData['external_digital_download_link'])
            {
                $productData['is_digital'] = 1;
            }

            $clearCartFlag = '/0';
            if($productData['clear_cart']==1)
            {
                $clearCartFlag = '/1';
            }
            $addToCartText = 'carts/shopping_cart/myShoppingCart/' . $enc_product_row_id.$clearCartFlag;
            $oneClickBuyLink = 'carts/shopping_cart/oneClickProductBuy/' . $enc_product_row_id.$clearCartFlag;
            $productData['isallowforcp'] = $productData['isallowforcp'];
            $productData['downloadLimit'] = $productData['downloadLimit'];

            $productData['addToCartText'] = $addToCartText;
            $productData['oneClickBuyLink'] = $oneClickBuyLink;
			$this->db->where('product_row_id', $productData['product_row_id']);

			$this->db->update('tbl_product', $productData);

			if($merchant_aff_program_row_id)
			{
				$this->db->set('merchant_aff_program_row_id',$merchant_aff_program_row_id);
				$this->db->where('product_row_id',$productData['product_row_id']);
				$this->db->update('tbl_aff_program_product');
			}

            $this->saveTermsAndConditions($productData['product_row_id'], $termsConditions);
            return $productData['product_row_id'];
		}
		else
		{
			foreach ($productData as $key=>$value)
			{
				if($key != 'product_row_id')
				{
					$prodData[$key] = $value;
				}
			}

            $get_merchant_id = $prodData['created_by'] ? $prodData['created_by'] : getCurrentMerchantId();
            $button_code = $this->getDefaultButtonCode($get_merchant_id);
            $prodData['button_code'] = $button_code->default_button ? $button_code->default_button : 6;
            $prodData['buy_now_button_code'] = $button_code->buy_now_button_code ? $button_code->buy_now_button_code : 6;

			$prodData['active_status'] = 1;
            if(!$prodData['created_by'])
			   $prodData['created_by'] = getCurrentMerchantId();

			$prodData['created_on'] = getCurrentDateTimeForDB();
			$prodData['modified_by'] = $prodData['created_by'];
			$prodData['modified_on'] = $prodData['created_on'];

            if(!isset($prodData['is_editable']))
            {
                $prodData['is_editable'] = 1;
            }

			$this->db->insert('tbl_product', $prodData);

			$product_row_id = $this->db->insert_id();

            if($product_row_id)
            {
                $enc_product_row_id = md5($product_row_id);

                $clearCartFlag = '/0';
                if($productData['clear_cart']==1)
                {
                    $clearCartFlag = '/1';
                }
                $addToCartText = 'carts/shopping_cart/myShoppingCart/' . $enc_product_row_id.$clearCartFlag;
                $oneClickBuyLink = 'carts/shopping_cart/oneClickProductBuy/' . $enc_product_row_id.$clearCartFlag;
			    /////// code to get the prefix of product id from basic setup////////////
			    $merchant_row_id = $productData['merchant_row_id'];
			    $getInstance = & get_instance();
			    $getInstance->load->model('carts/Cart_Setup_model', 'Cart_Setup_model', TRUE);
			    $cartBasic = $getInstance->Cart_Setup_model->get_basic_setup($merchant_row_id);
			    $product_id_prefix = $cartBasic->product_id_prefix;
			    ////////////////////////////////////////////////////////////////////////////
			    $this->load->library('Application_Utility');
			    //here 'PRO'=>prefix for product id will be taken from basic setup table
			    $product_id = $this->application_utility->generateId($product_id_prefix,$product_row_id);

			    $this->db->set('product_id', $product_id);
                $this->db->set('enc_product_row_id', $enc_product_row_id);
			    $this->db->set('addToCartText', $addToCartText);
			    $this->db->set('oneClickBuyLink', $oneClickBuyLink);
			    $this->db->where('product_row_id', $product_row_id);
			    $this->db->update('tbl_product');

                if($look_and_feel['longDesc'] || $look_and_feel['shortDesc'])
                {
                    $look_and_feel['product_row_id'] = $product_row_id;
                    $this->db->insert('tbl_product_look_and_feel', $look_and_feel);
                }

                $this->saveTermsAndConditions($product_row_id, $termsConditions);

                return $product_row_id;
            }

            return 0;
		}
	}

    /**
    * Author: Alauddin Husain
    * Purpose: Delete the assigned programs to the product
    * Save assign programs
    * Param: $product_row_id ,  $merchant_aff_program_row_ids
    */

    function assignProductToProgram($product_row_id, $merchant_affiliate_programs)
    {
        $sql = 'DELETE FROM tbl_aff_program_product WHERE product_row_id =' . $product_row_id;
        $this->db->query($sql);
        foreach($merchant_affiliate_programs as $merchant_aff_program_row_id)
        {
            $sql_values[] = "(" . $merchant_aff_program_row_id . ", " . $product_row_id  . ")";
        }

        if($sql_values)
        {
            $sql = 'INSERT INTO tbl_aff_program_product(merchant_aff_program_row_id, product_row_id) VALUES' . implode(',', $sql_values);
            $this->db->query($sql);
        }

    }
    /* add look and feel setup for a product*/
    function saveProductLookandFeel($lookAndFeelData)
    {
        if($lookAndFeelData['background_color']!='')
        {
            $lookAndFeelData['background_color'] = '#'.$lookAndFeelData['background_color'];
        }
        if($this->checkExistingProductLookAndFeel($lookAndFeelData['product_row_id']))
        {
            $this->db->where('product_row_id', $lookAndFeelData['product_row_id']);
            return $this->db->update('tbl_product_look_and_feel', $lookAndFeelData);
        }
        else
        {
            return $this->db->insert('tbl_product_look_and_feel', $lookAndFeelData);
        }
    }

    function  checkExistingProductLookAndFeel($product_row_id)
    {
        $this->db->select('COUNT(product_row_id) as cnt') ;
        $this->db->where('product_row_id', $product_row_id)  ;
        $this->db->from('tbl_product_look_and_feel');
        $result = $this->db->get();
        $count = $result->row_array();
        return $count['cnt'];
    }

    /**
     * for image upload
     *
     * @param unknown_type $fieldName
     * @return unknown
     */
    function imageUpload ($fieldName)
    {
        $config['upload_path'] = $this->config->item('product_look_and_feel_upload_path');
        $config['allowed_types'] = 'gif|jpg|png|bmp|tif';
        $config['max_size'] = '';
        $config['max_width'] = '';
        $config['max_height'] = '';
        $this->load->library('upload', $config);
        if ($this->upload->do_upload($fieldName))
        {
            $data = $this->upload->data();
            $fileName = $config['upload_path'] . $data['file_name'];
            $return = array('file_name' => $fileName , 'error' => '');
            return $return;
        } else
        {
            $err = '';
            //removing the error as image upload is not required
            if ($this->upload->display_errors() != 'You did not select a file to upload')
            {
                $err = $this->upload->display_errors();
            }
            $return = array('file_name' => '' , 'error' => $err);
            return $return;
        }
    }

    /**
     * This function updates merchant terms of service display for products of a merchant
     * @param $merchant_product_row_id
     * @return
     * @author Tania Noor
     */
    function updateMerchantTermsOfServiceOfProduct($merchant_row_id)
    {
        $this->db->set('merchant_terms_of_service_display', 0);
        $this->db->where('merchant_row_id',$merchant_row_id);
        $this->db->update('tbl_product');
    }

    /**
     * This function retrieves product info row from md5 product row id
     * @param encoded $enc_product_row_id
     * @return product row
     * @author Himel Nag Rana
     * @version 1.0
     * @package PWC v1.2
     */
    function getProductFromEncryptedID($enc_product_row_id)
    {
        if(!empty($enc_product_row_id))
        {
            $this->db->select('*');
            $this->db->from('tbl_product');
            $this->db->where('enc_product_row_id',$enc_product_row_id);
            $this->db->where('active_status', 1);
            $res = $this->db->get();
            return $res->row();
        }

        return false;
    }


	function getDigitalProductTempInfo($product_row_id)
	{
        if(!empty($product_row_id))
        {
		    $this->db->select('*');
		    $this->db->from('tbl_product_digital_temp');
		    $this->db->where('product_row_id',$product_row_id);
		    $digires = $this->db->get();
		    return $digires->result();
        }
        return false;
	}

	function setPrimaryProductForBundle ($data)
	{
		$product_row_id = $data['product_row_id'];
		$product_bundle_row_id = $data['product_bundle_row_id'];

		$this->db->set('is_primary', $data['check']);
		$this->db->where('product_row_id', $product_row_id);
		$this->db->update('tbl_product');

		$this->db->set('is_primary', $data['check']);
		$this->db->where('product_row_id', $product_row_id);
		$this->db->where('product_bundle_row_id', $product_bundle_row_id);
		$this->db->update('tbl_product_bundle_details');
	}

	function getProductPriceByProductRowId ($product_row_id)
	{
		$this->db->select('product_price');
		$this->db->from('tbl_product');
		$this->db->where('product_row_id', $product_row_id);
		$res = $this->db->get();
		$result = $res->row();
		$price = $result->product_price;
		return $price;
	}

	function uploadProductImage ($fieldName)
	{
		$config['upload_path'] = 'images/products/';
		$config['allowed_types'] = 'gif|jpg|png';
		$config['max_size'] = '1000';
		$config['max_width'] = '200';
		$config['max_height'] = '200';
		$this->load->library('upload', $config);
		if ($this->upload->do_upload($fieldName))
		{
			$data = $this->upload->data();
			$fileName = $config['upload_path'] . $data['file_name'];
			$return = array('file_name' => $fileName , 'error' => '');
			return $return;
		} else
		{
			$err = '';
			//removing the error as image upload is not required
			if ($this->upload->display_errors() != 'You did not select a file to upload')
			{
				$err = $this->upload->display_errors();
			}
			$return = array('file_name' => '' , 'error' => $err);
			return $return;
		}
	}

	function deleteProductImage ($product_row_id)
	{
        if(!empty($product_row_id))
        {
		    $allOfThis = $this->getProduct($product_row_id, 0, array('product_image'));
            if(file_exists($allOfThis['product_image']))
            {
                unlink($allOfThis['product_image']);
            }
		    $this->db->set('product_image', "");
            $this->db->set('product_thumbnail', "");
		    $this->db->where('product_row_id', $product_row_id);
		    $this->db->update('tbl_product');
        }
	}
    function updateProductImage($data)
    {
        $this->db->where('product_row_id',$data['product_row_id']);
        $this->db->update('tbl_product',$data);
    }
	/******************************  DISCOUNT  ***************************************/
	/*function to retrive product discount information, given product row id*/
	function getDiscountByProduct ($product_row_id)
	{
		$this->db->select('max(upper_limit) as maxUpper');
		$this->db->from('tbl_quantity_discount');
		$this->db->where('product_row_id', $product_row_id);
		$recordSet = $this->db->get();
		$result = $recordSet->row_array();
		return $result['maxUpper'];
	}
    /*
    * Updated by Alauddin Husain
    */
	function checkShowing ($product_row_id)
	{
        $sql = "SELECT *FROM tbl_quantity_discount WHERE quantity_discount_id = (SELECT MAX(quantity_discount_id) as maxDis FROM tbl_quantity_discount WHERE product_row_id=$product_row_id GROUP BY product_row_id)";
        $query = $this->db->query($sql);
        $result = $query->row_array();
        return $result['upper_limit'];
	}

	/**
	 * function to retrive product discount as value, given product row id
	 *
	 * @author ZERIN
	 * @param unknown_type $product_row_id
	 * @param unknown_type $product_quantity
	 * @return unknown
	 */
	function getDiscountValueByProduct ($product_row_id, $product_quantity)
    {
        $product_row_id = (int) $product_row_id;
        $product_quantity = (int) $product_quantity;

        if($product_row_id && $product_quantity)
        {
		    $resultSet = $this->db->query("SELECT * FROM tbl_quantity_discount WHERE product_row_id = '" . $product_row_id . "' AND " . $product_quantity . " >= lower_limit AND ((" . $product_quantity . " <= upper_limit) || (upper_limit=0))");
		    return $resultSet->row();
        }

        return false;
	}


    //Anjuman : 05022012 (mmddyyyy) -- Added option for No Tax and Free Shipping in qualtity discount

    function getDiscountByProductGrid ($product_row_id)
    {
        $currency_symbol=$_SESSION['currency_symbol'];

        $sql = '(select quantity_discount_id, lower_limit,(CASE upper_limit WHEN  0 THEN \'Unlimited\' ELSE upper_limit END) as upper_limit,
                 (CASE WHEN discount_type = 1 THEN CONCAT(discount_amount,\'\',\'%\') WHEN discount_type = 2 THEN CONCAT(\''.$currency_symbol.'\',\'\',discount_amount) WHEN discount_type = 3 THEN CONCAT(\'per unit '.$currency_symbol.'\',\'\',discount_amount) WHEN discount_type = 4 THEN  CONCAT(\'No Tax'.'\') ELSE CONCAT(\'Free Shipping'.'\') END) as discount,
                 \'Delete\' as delete_discount
                 from tbl_quantity_discount
                 where product_row_id =' . $product_row_id . ') AS abc';

        $this->load->library ( 'rapyd' );
        $this->rapyd->load('datagrid');
        $grid = new DataGrid('', $sql);
        $grid->order_by('lower_limit', 'asc');
        $grid->column('Lower Limit', 'lower_limit',  'align=\'left\'');
        $grid->column('Upper Limit', 'upper_limit',  'align=\'left\'');
        $grid->column('Discount', '<#discount#>',  'align=\'left\'');
        $grid->column_detail('Delete',GRID_DELETE_IMG, '#" onclick="return deleteDiscount(<#quantity_discount_id#>,'.$product_row_id.');"', 'align=\'left\'', false, '', true, 'colspan="1"');
        $grid->build();
        return $grid->output;
    }

	function getAllDiscount ($quantity_discount_id)
	{
		$this->db->select('*');
		$this->db->from('tbl_quantity_discount');
		$this->db->where('quantity_discount_id', $quantity_discount_id);
		$res = $this->db->get();
		$result = $res->row_array();
		return $result;
	}

	function getAllDiscountByProduct ($product_row_id)
	{
		$this->db->select('*');
		$this->db->from('tbl_quantity_discount');
		$this->db->where('product_row_id',$product_row_id);
		$this->db->order_by("quantity_discount_id",'asc');
		$res = $this->db->get();
		$result = $res->result_array();
		return $result;
	}

    function countDiscountsByProduct($product_row_id)
    {
        $sql = 'SELECT count(quantity_discount_id) AS total
                FROM tbl_quantity_discount
                WHERE product_row_id = ' . $product_row_id;

        $query = $this->db->query($sql);
        $row = $query->row_array();
        if($row)
        {
            return $row['total'];
        }
        return 0;
    }

	//for cart
	function getAllDiscountForCart ($product_row_id_array)
	{
		$total_product_in_cart = count($product_row_id_array);
		for ($i = 0; $i < $total_product_in_cart; $i ++)
		{
            $currProdId = $product_row_id_array[$i];
			$allOfProduct = $this->getProduct($currProdId, 0, array('product_description'));
			$product_description_array[$currProdId] = $allOfProduct['product_description'];
			$discount_array[$currProdId] = $this->getAllDiscountByProduct($currProdId);
		}
		$return_array['description'] = $product_description_array;
		$return_array['discount'] = $discount_array;
		return $return_array;
	}

	//for cart
	function checkDiscountForCart ($product_row_id)
	{
		$cn = 0;
		$discountedProducts = $this->getAllDiscountByProduct($product_row_id);
		if (count($discountedProducts) > 0)
		{
			$cn ++;
		}
		if ($cn > 0 )
		{
			return true;
		}
		else
		{
			return false;
		}
	}

	function saveDiscount ($data)
	{
		$this->db->insert('tbl_quantity_discount', $data);
	}

	function deleteDiscount ($quantity_discount_id, $product_row_id)
	{
		$this->db->where('product_row_id', $product_row_id);
		$this->db->where('quantity_discount_id', $quantity_discount_id);
		$this->db->delete('tbl_quantity_discount');
	}

	/**************************UPSELL*****************************************/
	function checkUpsell ($upsell_product_row_id, $is_bundle)
	{
		$this->db->select('count(*) as cntUpsell');
		$this->db->from('tbl_product_upsell');
		$this->db->where('upsell_product_row_id', $upsell_product_row_id);
        $this->db->where('is_bundle', $is_bundle);
		$recordSet = $this->db->get();
		$result_array = $recordSet->row_array();

		return $result_array['cntUpsell'];
	}

	function getAllUpsellByProduct ($main_product_row_id,$product_row_id)
	{
		$this->db->select('*');
		$this->db->from('tbl_product_upsell');
		$this->db->where('product_row_id', $main_product_row_id);
		$this->db->where('upsell_product_row_id', $product_row_id);
		$recordSet = $this->db->get();
		$result_array = $recordSet->row_array();
		return $result_array;
	}

	function countUpsell($main_product_row_id)
	{
		$this->db->select('count(upsell_row_id) as countUpsell');
		$this->db->from('tbl_product_upsell');
		$this->db->where('product_row_id',$main_product_row_id);
		$res = $this->db->get();
		$result = $res->row();
		$count = $result->countUpsell;
		return $count;
	}

	function uploadProductUpsellImage ($fieldName)
	{
		$config['upload_path'] = 'images/upsell/';
		$config['allowed_types'] = 'gif|jpg|png|bmp|tif';
		$config['max_size'] = '';
		$config['max_width'] = '942';
		$config['max_height'] = '';
		$this->load->library('upload', $config);
		if ($this->upload->do_upload($fieldName))
		{
			$data = $this->upload->data();
			$fileName = $config['upload_path'] . $data['file_name'];
			$return = array('file_name' => $fileName , 'error' => '');
		}
		else
		{
			$err = '';
			//removing the error as image upload is not required
			if ($this->upload->display_errors() != 'You Did Not Select A File To Upload')
			{
				$err = $this->upload->display_errors();
			}

			$return = array('file_name' => '' , 'error' => $err);
		}

		return $return;
	}

	function uploadProductUpsellImageTemp ($fieldName)
	{
		$config['upload_path'] = 'images/upsell/temp/';
		$config['allowed_types'] = 'gif|jpg|png|bmp|tif';
		$config['max_size'] = '';
		$config['max_width'] = '942';
		$config['max_height'] = '';
		$this->load->library('upload', $config);
		if ($this->upload->do_upload($fieldName))
		{
			$data = $this->upload->data();
			$fileName = $config['upload_path'] . $data['file_name'];
			$return = array('file_name' => $fileName , 'error' => '');
			return $return;
		} else
		{
			$err = '';
			//removing the error as image upload is not required
			if ($this->upload->display_errors() != 'You did not select a file to upload')
			{
				$err = $this->upload->display_errors();
			}
			$return = array('file_name' => '' , 'error' => $err);
			return $return;
		}
	}

	function saveUpsellProduct ($data, $check_exist)
	{
		if(!$check_exist)
		{
			$this->db->insert('tbl_product_upsell', $data);
		}
		else
		{
            $this->db->where('upsell_product_row_id', $data['upsell_product_row_id']);
			$this->db->where('product_row_id ', $data['product_row_id']);
			$this->db->update('tbl_product_upsell', $data);
		}

		$this->db->set('is_upsell', 1);
		$this->db->where('product_row_id', $data['product_row_id']);
		$this->db->update('tbl_product');
	}

	function updateProductPriority ($upsell_row_id,$priority)
	{
		$this->db->set('priority',$priority);
		$this->db->where('upsell_row_id',$upsell_row_id);
		$this->db->update('tbl_product_upsell');
	}

    function updateProduct($data)
    {
        if(isset($data['product_row_id']) && $data['product_row_id'])
        {
            $this->db->where('product_row_id', $data['product_row_id']);
            $this->db->update('tbl_product', $data);
        }
    }

	function checkIfExistsUpsell($product_row_id,$upsell_product_row_id, $is_bundle = 0)
	{
		$this->db->select('count(*) as noOfUpsell');
		$this->db->from('tbl_product_upsell');
		$this->db->where('upsell_product_row_id',$upsell_product_row_id);
		$this->db->where('product_row_id',$product_row_id);
        $this->db->where('is_bundle',$is_bundle);
		$res = $this->db->get();
		$result = $res->row();
        return $result->noOfUpsell;
	}

	function deleteUpsellImage ($product_upsell_row_id, $image_path,$imageFlag)
	{
		unlink($image_path);
        if($imageFlag == 1)
        {
            $this->db->set('image_path', '');
        }
        elseif($imageFlag == 2)
        {
            $this->db->set('image_path2', '');
        }
		$this->db->where('upsell_product_row_id', $product_upsell_row_id);
		$this->db->update('tbl_product_upsell');
	}

	function removeFromUpsell($product_row_id, $upsell_product_row_id = 0)
	{
		if($upsell_product_row_id)
		{
            $this->db->where('product_row_id',$product_row_id);
			$this->db->where('upsell_product_row_id',$upsell_product_row_id);
		}
		else
		{
			$this->db->where('product_row_id',$product_row_id);
		}

        $this->db->delete('tbl_product_upsell');

		$check = $this->checkIfExistsUpsell($product_row_id);

        $getInstance = & get_instance();
        $getInstance->load->model('products/Upsell_Component_model', 'ucm', TRUE);
        $advanceUpsell = $getInstance->ucm->getAdvanceUpsellFlowByProductRowId($product_row_id);

		if(!$check && !$advanceUpsell['pre_upsell_flow_id'] && !$advanceUpsell['post_upsell_flow_id'])
		{
			$this->db->set('is_upsell',0);
			$this->db->where('product_row_id',$product_row_id);
			$this->db->update('tbl_product');
		}
	}

	function getAllByUpsellRowId ($product_row_id,$upsell_product_row_id)
	{
		$this->db->select('*');
		$this->db->from('tbl_product_upsell');
		$this->db->where('product_row_id', $product_row_id);
		$this->db->where('upsell_product_row_id', $upsell_product_row_id);
		$recordSet = $this->db->get();
		$result_array = $recordSet->row_array();

		return $result_array;
	}

	function upsellInfoFromUpsellToProduct($upsell_product_row_id)
	{
		$this->db->select('*');
		$this->db->from('tbl_product_upsell');
		$this->db->where('upsell_product_row_id',$upsell_product_row_id);
		$res = $this->db->get();
		$result = $res->row();
		return $result;
	}

	function getAllByPriority ($priority,$product_row_id)
	{
		$this->db->select('*');
		$this->db->from('tbl_product_upsell');
		$this->db->where('product_row_id',$product_row_id);
		$this->db->where('priority',$priority);
		$res = $this->db->get();
		$result = $res->row();

		return $result;
	}

	function getAllupsellOfAProduct($product_row_id)
	{
		$this->db->select('*');
		$this->db->from('tbl_product_upsell');
		$this->db->where('product_row_id',$product_row_id);
		$recordSet = $this->db->get();
		$result = $recordSet->result();

		return $result;
	}

	function getMax ($product_row_id)
	{
		$this->db->select('max(priority) as maxPriority');
		$this->db->from('tbl_product_upsell');
		$this->db->where('product_row_id',$product_row_id);
		$recordSet = $this->db->get();
		$result = $recordSet->result();

		$maxPriority = $result[0]->maxPriority;
		return $maxPriority;
	}

	function getUpsellProductDetails($product_row_id, $upsell_product_row_id)
	{
		$this->db->select('*');
		$this->db->from('tbl_product_upsell');
		$this->db->where('upsell_product_row_id',$upsell_product_row_id);
		$this->db->where('product_row_id',$product_row_id);
		$res = $this->db->get();
		$result = $res->row();

		return $result;
	}

	/**
	 * get upsell product details by upsell row id
	 *
	 * @param unknown_type $upsell_row_id
	 * @return unknown
	 */
	function getUpsellProduct($upsell_row_id)
	{
        if(!$upsell_row_id)
           return false;
		$this->db->select('*');
		$this->db->from('tbl_product_upsell');
		$this->db->where('upsell_row_id',$upsell_row_id);
		$res = $this->db->get();
		return $res->row();
	}

	/**
	 * return all active upsell product by product row id
	 * added by G J Jidni
	 * @param int $product_row_id
	 * @return array
	 */
	function getActiveUpsellProducts ($product_row_id)
	{
		$this->db->select('*');
		$this->db->where('product_row_id', $product_row_id);
		$this->db->where('upsell_active_status', '1');
		$this->db->order_by('priority', 'ASC');
		$this->db->from('tbl_product_upsell ');
		$resultSet = $this->db->get();
		return $resultSet->result();
	}


    /**
	 * return all active  product of a category by categoryw id
	 * added by Zaman
	 * @param int $category_id
	 * @return array
	 */
	function getProductByCategory ($category_id, $merchant_row_id = 0)
	{
        settype($category_id, 'integer');
        settype($merchant_row_id, 'integer');

		$sql = 'SELECT product_row_id, product_description
                FROM tbl_product
                WHERE active_status = 1 ';

        if($merchant_row_id)
        {
            $sql .= ' AND merchant_row_id = ' . $this->db->escape($merchant_row_id);
        }

        if($category_id)
        {
            $sql .= ' AND category_id = ' . $this->db->escape($category_id);
        }

		$sql .= ' AND is_gift_certificate = 0
                  ORDER BY product_description ASC';

		$resultSet = $this->db->query($sql);
		return $resultSet->result();
	}

    function getAssignedBundleProduct()
    {
        $this->db->select('*');
        $this->db->from('tbl_product_upsell');
        $this->db->where('product_row_id',$product_row_id);
        $this->db->where('is_bundle', 1);
        $res = $this->db->get();
        $result = $res->result();

        for($i=0; $i<count($result);$i++)
        {
            $alreadyInUpsellList[$i] = $result[$i]->upsell_product_row_id;
        }
        return $alreadyInUpsellList;
    }

    function getUnassignedBundleProduct($bundleInUpsellList, $merchant_row_id)
    {
        if(count($bundleInUpsellList) > 0)
        {
            $sql = "SELECT *FROM tbl_product_bundle WHERE delete_state=0 AND
                    merchant_row_id = " . $merchant_row_id . " AND
                    product_bundle_row_id NOT IN (" . implode(',', $bundleInUpsellList ) . ")";
        }
        else
        {
            $sql = "SELECT *FROM tbl_product_bundle WHERE delete_state=0 AND
                    merchant_row_id = " . $merchant_row_id;
        }

        $query = $this->db->query($sql);
        return $query->result();
    }


	function getAllUpsellToProduct($product_row_id)
	{
		$this->db->select('*');
		$this->db->from('tbl_product_upsell');
		$this->db->where('product_row_id',$product_row_id);
        $this->db->where('is_bundle', 0);
		$res = $this->db->get();
		$result = $res->result();

		for($i=0; $i<count($result);$i++)
		{
			$alreadyInUpsellList[$i] = $result[$i]->upsell_product_row_id;
		}
		return $alreadyInUpsellList;
	}

	/*Retrieves all other product row id other than the given one*/
	function getOtherProductsForUpsellSetting($product_row_id,$alreadyInUpsellList)
	{
		$this->db->select('*');
		$this->db->from('tbl_product');
		$this->db->where('active_status',1);
		$this->db->where('product_row_id <>',$product_row_id);
		$this->db->where('merchant_row_id',getCurrentMerchantId());
		$this->db->where('is_gift_certificate', 0);
        $this->db->order_by('product_description ASC');
		$res = $this->db->get();

		$result = $res->result();

		$cnt = count($result);
		for($i = 0; $i < $cnt; $i++)
		{
			if(!in_array($result[$i]->product_row_id,$alreadyInUpsellList))
			{
				$otherProductsArray[$i] = $result[$i];
			}
		}

		return $otherProductsArray;
	}

    /*Zaman
    Retrieves all  product which are not in upsell*/

    function getProductNotInUpsell($categoryId,$product_row_id,$merchantRowId)
    {
        if($categoryId)
        {
           $condition = " AND category_id = " . $categoryId;
        }
        $sql='SELECT product_row_id,product_description
              FROM tbl_product
              WHERE product_row_id != '.$product_row_id.'
              AND merchant_row_id ='.$merchantRowId.'
              AND active_status =1
              AND product_row_id NOT IN(    SELECT upsell_product_row_id
                                            FROM tbl_product_upsell
                                            WHERE product_row_id = '.$product_row_id.' AND is_bundle = 0 ) ' . $condition;

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

        return $productList;
    }

    function getUnassignedBundleProductList($product_row_id,$merchantRowId)
    {
        $sql = 'SELECT product_bundle_row_id as product_row_id, offer_name as product_description FROM tbl_product_bundle WHERE
                merchant_row_id = '.$merchantRowId.'
                AND delete_state = 0
                AND product_bundle_row_id NOT IN(SELECT upsell_product_row_id
                                            FROM tbl_product_upsell
                                            WHERE product_row_id = '.$product_row_id.'  AND is_bundle = 1 )';
        $query = $this->db->query($sql);
        return $query->result();
    }

    function getUpSellProductsForGrid($product_row_id)
    {
        $sql = 'SELECT  u.upsell_row_id,
                        p.product_description,
                        u.upsell_display,
                        u.priority,
                        u.upsell_product_row_id,
                        u.is_bundle,
                        u.product_row_id
                 FROM tbl_product_upsell u
                 INNER JOIN tbl_product p ON p.product_row_id = u.upsell_product_row_id
                 WHERE u.product_row_id = ' . $product_row_id . ' AND u.is_bundle = 0

                 UNION

                 SELECT u.upsell_row_id,
                        p.offer_name as product_description,
                        u.upsell_display,
                        u.priority,
                        u.upsell_product_row_id,
                        u.is_bundle,
                        u.product_row_id
                 FROM tbl_product_upsell u
                 INNER JOIN tbl_product_bundle p ON p.product_bundle_row_id = u.upsell_product_row_id
                 WHERE u.product_row_id = ' . $product_row_id . ' AND u.is_bundle = 1
                 ORDER BY upsell_display, priority';

        $query = $this->db->query($sql);
        return $query->result();
    }

    function saveUpSellDisplay($upsellRowId, $upsellDisplay)
    {
        $sql = "UPDATE tbl_product_upsell
                SET upsell_display = $upsellDisplay
                WHERE upsell_row_id = $upsellRowId";
        $this->db->query($sql);
    }

    function getUpSellProductsByProductRowId($product_row_id, $order_temp_id, $ignoreProducts)
    {
        settype($product_row_id, 'integer');
        settype($order_temp_id, 'integer');

        if(empty($product_row_id) || empty($order_temp_id))
        {
            return false;
        }

        $condition = '';

        if(!empty($ignoreProducts))
        {
            $temp = $ignoreProducts;
            $ignoreProducts = array();

            foreach($temp as $pid)
            {
                if(!empty($pid))
                {
                    $ignoreProducts[] = $pid;
                }
            }
            unset($temp);

            if(!empty($ignoreProducts))
            {
                $condition = ' AND upsell_product_row_id NOT IN (' . implode(',', $ignoreProducts) . ') ';
            }
        }
        $sql = 'SELECT upsell_row_id, is_bundle
                FROM tbl_product_upsell
                WHERE product_row_id = ' . $product_row_id .'
                AND upsell_display = ' . $this->config->item('pre_upsell_product_display') . '
                AND upsell_product_row_id NOT IN (  SELECT product_row_id
                                                    FROM tbl_order_details_temp
                                                    WHERE order_temp_id = '. $order_temp_id . ' AND bundle_id = 0
                                                    UNION
                                                    SELECT bundle_id
                                                    FROM tbl_order_details_temp
                                                    WHERE order_temp_id = '. $order_temp_id . ' AND bundle_id != 0) '
                . $condition . '
                ORDER BY priority ASC
                LIMIT 1';

        $query = $this->db->query($sql);
        return $query->row();
    }

    function countPostUpSellProductsByOrderRowId($order_row_id, $skipProducts = '')
    {
		$result = array();

		if(!empty($order_row_id)){
			$sql = "SELECT product_row_id
					FROM tbl_order_details
					WHERE order_row_id = $order_row_id";
			$query = $this->db->query($sql);
			$result = $query->result_array();
		}
        $products = array();

        foreach($result as $res)
        {
            $products[] = $res['product_row_id'];
        }

        if($skipProducts)
        {
            $skipProducts = array_merge($products, $skipProducts);
        }
        else
        {
            $skipProducts = $products;
        }

        if(!empty($products))
        {
            $sql = 'SELECT count(u.upsell_row_id) AS total
                    FROM tbl_product_upsell u
                    INNER JOIN tbl_product p ON p.product_row_id = u.product_row_id
                    WHERE u.product_row_id IN (' . implode(',', $products) . ')
                    AND u.upsell_display = ' . $this->config->item('post_upsell_product_display');

            if(is_array($skipProducts) && count($skipProducts))
            {
                $sql .= ' AND u.upsell_product_row_id NOT IN (' . implode(',', $skipProducts) . ')';
            }

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

            if($row)
            {
                return $row['total'];
            }
        }
        return 0;
    }

    function getPostUpSellProductsByOrderRowId($order_row_id, $skipProducts = array())
    {
        $sql = "SELECT od.product_row_id, p.upsell_display
                FROM tbl_order_details od
                INNER JOIN tbl_product p ON p.product_row_id = od.product_row_id
                WHERE od.order_row_id = $order_row_id
                AND od.product_row_id IS NOT NULL
                ORDER BY order_details_id";

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

        $upsellProducts = array();

        if($result)
        {
            foreach($result as $res)
            {
                $skipProducts[] = $res['product_row_id'];
            }

            $skipProducts = array_unique($skipProducts);
            $skipProducts = implode(',', $skipProducts);

            $productsAllReadyAdded = array();

            foreach($result as $res)
            {
                if($res['upsell_display'] == $this->config->item('post_upsell_product_display') || $res['upsell_display']  == $this->config->item('pre_post_upsell_product_display'))
                {
                    $sql = "SELECT upsell_row_id, upsell_product_row_id
                            FROM tbl_product_upsell
                            WHERE product_row_id = {$res['product_row_id']}
                            AND upsell_display = " . $this->config->item('post_upsell_product_display') . "
                            AND upsell_product_row_id NOT IN ($skipProducts)
                            ORDER BY priority";

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

                    if($upSells)
                    {
                        foreach($upSells as $upSell)
                        {
                            if(!in_array($upSell['upsell_product_row_id'], $productsAllReadyAdded))
                            {
                                $upsellProducts[] = $upSell['upsell_row_id'];
                                $productsAllReadyAdded[] = $upSell['upsell_product_row_id'];
                            }
                        }
                    }
                }
            }
        }

        return $upsellProducts;
    }
	/**************************UPSELL*****************************************/

	/*show all the details info of the given product*/
	function getProduct ($product_row_id, $merchant_row_id = 0, $fields = array(), $returnArray = true)
	{
		if(!empty($product_row_id))
		{
            if(is_array($fields) && !empty($fields))
            {
                $fields = implode(',', $fields);
            }
            else
            {
                $fields = '*';
            }

		    $this->db->select($fields);
		    $this->db->from('tbl_product');
		    $this->db->where('product_row_id', $product_row_id);

            if($merchant_row_id)
            {
                $this->db->where('merchant_row_id', $merchant_row_id);
            }

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

            if($returnArray)
            {
                return $recordSet->row_array();
            }
            else
            {
                return $recordSet->row();
            }
		}
        else
        {
            if($returnArray)
            {
                return array();
            }
            else
            {
                return new stdClass();
            }
        }
	}

    /*show all the look and feel details info of the given product*/
    function getProductLookAndFeel ($product_row_id, $fields = '*')
    {
        $this->db->select($fields);
        $this->db->from('tbl_product_look_and_feel');
        $this->db->where('product_row_id', $product_row_id);
        $recordSet = $this->db->get();
        return $recordSet->row_array();
    }

    function deleteProductCartHeaderImage ($product_row_id)
    {
        $lookAndFeel = $this->getProductLookAndFeel($product_row_id, 'company_logo');

        if($lookAndFeel['company_logo'])
        {
            unlink($lookAndFeel['company_logo']);
        }
        $this->db->set('company_logo', '');
        $this->db->where('product_row_id', $product_row_id);
        $this->db->update('tbl_product_look_and_feel');
    }

    function deleteProductCartBackgroundImage ($product_row_id)
    {
        $lookAndFeel = $this->getProductLookAndFeel($product_row_id, 'background_image');
        if($lookAndFeel['background_image'])
        {
            unlink($lookAndFeel['background_image']);
        }
        $this->db->set('background_image', "");
        $this->db->where('product_row_id', $product_row_id);
        $this->db->update('tbl_product_look_and_feel');
    }
    /*show product description of the given product*/
    function getProductDescriptionByProductRowId($product_row_id)
	{
		$this->db->select('product_description');
		$this->db->from('tbl_product');
		$this->db->where('product_row_id', $product_row_id);
		$recordSet = $this->db->get();
		return $recordSet->row_array();
	}

	/*deletes the given product*/
	function deleteProduct ($product_row_id, $merchant_row_id = 0)
	{
        if(!$product_row_id)
           return false;
		$this->db->set('active_status', 0);
		$this->db->where('product_row_id', $product_row_id);

        if($merchant_row_id)
        {
            $this->db->where('merchant_row_id', $merchant_row_id);
        }

		if($this->db->update('tbl_product'))
        {
            $this->deleteRelated($product_row_id);
            if($product_row_id)
               $this->db->query("UPDATE tbl_recurring_orders SET active_status = 0 WHERE product_row_id = " . $product_row_id);
            return true;
        }
        return false;
	}

	/*change store product status the given product*/
	function changeStoreProductStatus ($product_row_id, $merchant_row_id)
	{
		if($product_row_id && $merchant_row_id) {
			$this->db->set('is_store_product', 0);
			$this->db->where('product_row_id', $product_row_id);
			$this->db->where('merchant_row_id', $merchant_row_id);
			if($this->db->update('tbl_product'))
			{
				return true;
			}
			return false;
		}
		return false;
	}

	/*this function clears the related products related to given product*/
	function deleteRelated ($product_row_id)
	{
		$this->db->where('product_row_id', $product_row_id);
		$this->db->delete('tbl_product_relation');
	}

	function deleteRelatedProduct($product_row_id,$related_product_row_id, $is_bundle)
	{
		$this->db->where('product_row_id', $product_row_id);
		$this->db->where('related_product_row_id', $related_product_row_id);
        $this->db->where('is_bundle', $is_bundle);
		$this->db->delete('tbl_product_relation');
	}

	/*show all the products of the selected merchant in a datagrid*/
	function showProductsList ($merchant_row_id)
	{
		$productIds = $this->getScheduleProductId($merchant_row_id);
        $productId = $productIds[0] ? $productIds[0] : 5;
        
        $sql = 'SELECT  product_row_id,
                        product_id,
                        category_id,
                        CONCAT(product_row_id,\'-\',is_featured) AS featured_product,
				        product_description,
                        product_code,
                        FORMAT(product_price, 2) AS product_price,
				        (CASE is_upsell
                            WHEN 1 THEN \''.TIC_IMG_ICON.'\'
                            ELSE \''.CROSS_IMG_ICON.'\'
                        END) AS is_upsell,
				        addToCartText,
				        is_featured,
                        
                       CASE product_row_id
                            WHEN ' . $productId . ' THEN \'\'
                            ELSE \''.GRID_COPY_IMG.'\'
                        END AS copy_product_link,                        

                       CASE product_row_id
                            WHEN ' . $productId . ' THEN \'\'
                            ELSE \''.GRID_DELETE_IMG.'\'
                        END AS delete_product_link,
                       CASE category_id
                            WHEN ' . $this->config->item('schedule_category_id') . ' THEN 2                                                 
                            WHEN ' . $this->config->item('club_fee_category_id') . ' THEN 1    
                            ELSE 0
                        END AS is_schedule
		       FROM tbl_product  p
                 WHERE merchant_row_id = ' . $merchant_row_id . '
				 AND active_status = 1
				 AND is_gift_certificate = 0 
				 AND is_editable = 1 ORDER BY is_schedule DESC, product_description ASC ';

		$sql = '('.$sql.' ) AS abc';

		$this->load->library ( 'rapyd' );
		$this->rapyd->load('datagrid');

		$grid = new DataGrid('', $sql);
		$grid->showFeaturedBox = true;
        $grid->currentPageNo = $this->config->item('featuredProduct');
        $grid->showProductDeleteBox = $productId;
        $grid->headerCheckboxColumn = 2;
        $grid->showHeaderCheckbox = true;
        //$grid->order_by('is_schedule', 'DESC');
		//$grid->order_by('product_description', 'ASC');
        $grid->per_page = $this->config->item('rows_per_page');
		$grid->column('Feat. Prod.', 'featured_product', 'align="left" width="30"');
        $grid->column('', 'product_row_id' , 'align="left" width="30"');
		//if(is_upsell=='Yes'){
		$grid->column('With Upsell', '<#is_upsell#>' , 'align="left" width="50"');
		//}
		$grid->column('Product ID', 'product_id', 'align="left" width="80"');
		$grid->column_orderby('Product Name', 'product_description', 'product_description', 'align="left"');
		$grid->column_orderby('SKU', 'product_code', 'product_code', 'align="left" width="80"');
		$grid->column_orderby('Price', getCurrencySymbol() . '<#product_price#>', 'product_price', 'align="left" width="80"');
        $grid->column_detail('Action', GRID_EDIT_IMG , 'products/products/editProduct/<#product_row_id#>', 'align="left" width="20"', false, 'class="block_page"', true, 'colspan="4" width="110" align="center"');
		$grid->column_detail('Copy Product', "copy_product_link" , 'products/products/copyProduct/<#product_row_id#>', 'align="left" width="20"', false, 'onclick = "return checkCopy(this.href);"', false);
		$grid->column_detail('Delete Product', "delete_product_link", 'products/products/deleteProduct/<#product_row_id#>.html" prodId="<#product_row_id#>"', 'align="left" width="20"', false, 'onclick = "return checkDelete(this.getAttribute(\'prodId\'));"', false);
        $grid->non_secure_url = true;
		$grid->column_detail('Test', 'Test', '<#addToCartText#>', 'align="left" class="link_style" width="20" ', false, 'target="_blank"', false);
        $grid->non_secure_url = false;
		$grid->build();
        //dumpvar($grid);
        $_SESSION['running_result_count'] = $grid->recordCount;
		return $grid->output;
	}

    /* get product count from grid */
    function getProductCountFromGrid()
    {
        return $_SESSION['running_result_count'];
    }

	/*get all the items of the product table*/
	function getAllProducts ()
	{
		$this->db->select('*');
		$this->db->from('tbl_product');
		$this->db->where('active_status', 1);
		$this->db->where('is_gift_certificate', 0);
		$this->db->where('is_editable', 1);
		$resultSet = $this->db->get();
		return $resultSet->result();
	}
    function getAffiliateLevelCommissionByProductRowId($product_row_id)
    {
        $sql = 'SELECT p.affiliate_first_level_commission,
                p.affiliate_second_level_commission
                FROM tbl_product p
                WHERE p.product_row_id = '.$product_row_id;
        $result = $this->db->query($sql);
        $return = $result->row();
        return $return;
    }

	/*get all products of a merchant*/
	function getThisMerchantProducts ($merchant_row_id,$numberToDisplay = '', $offset = '', $queryString = '', $fields = null)
	{
		$columns = $fields ? implode(',', $fields) : '*';
        $sql = 'SELECT ' . $columns . '
				FROM tbl_product
				WHERE active_status = 1
				AND is_editable = 1
				AND is_gift_certificate = 0
				AND merchant_row_id = '.$merchant_row_id
				.$queryString;

		//Anjuman | D: 05302012 -- order by alphabatically
        if($offset!= '' || $numberToDisplay != '')
		{
			$sql.= ' ORDER BY product_description ASC';// limit .$offset.','.$numberToDisplay;
		}
		$resultSet = $this->db->query($sql);
		return $resultSet->result_array();
	}

    function getCountThisMerchantProducts ($merchant_row_id,$numberToDisplay = '', $offset = '', $queryString = '')
    {
        $sql = 'SELECT COUNT(product_row_id) AS total
                FROM tbl_product
                WHERE active_status = 1
				AND is_editable = 1
				AND is_gift_certificate = 0
                AND merchant_row_id = '.$merchant_row_id
                .$queryString;

        $resultSet = $this->db->query($sql);
        $row = $resultSet->row_array();
        return isset($row['total']) ? $row['total'] : 0;
    }

	function getProductCount($merchant_row_id)
	{
		$this->db->select('count(product_row_id) as prCount');
		$this->db->from('tbl_product');
		$this->db->where('merchant_row_id',$merchant_row_id);
		$this->db->where('active_status', 1);
		$this->db->where('is_editable', 1);
		$this->db->where('is_gift_certificate', 0);
		$record = $this->db->get();

		$productCountObj = $record->row();
		$productCount = $productCountObj->prCount;
		return $productCount;
	}
	/**
	 * get all products of a merchant under certain searching conditions
	 */
	function getSearchedProductsFromThisMerchant ($merchant_row_id, $qs)
	{
		$sql = "SELECT * FROM tbl_product
				WHERE merchant_row_id = $merchant_row_id
				$qs
				active_status = 1
				AND is_editable = 1
				AND is_gift_certificate = 0";
		$res = $this->db->query($sql);
		$result = $res->result();
		return $result;
	}

	/*change the status of a product to featured product and vice versa*/
	function changeToFeaturedProduct ($featured_flag, $product_row_id)
	{
		$this->db->set('is_featured', $featured_flag);
		$this->db->where('product_row_id', $product_row_id);
		$this->db->update('tbl_product');
	}

	/*counts the number of featured products under the selected merchant*/
	function countFeatured ($merchant_row_id)
	{
		$this->db->select('count(product_row_id) as CNTFEAT');
		$this->db->from('tbl_product');
		$this->db->where('is_featured', 1);
		$this->db->where('active_status', 1);
		$this->db->where('merchant_row_id', $merchant_row_id);
		$resultSet = $this->db->get();
		return $resultSet->row();
	}

	/*gets all the featured products with all other information*/
	function getAllFeatured ($merchant_row_id)
	{
		$this->db->select('*');
		$this->db->from('tbl_product');
		$this->db->where('is_featured', 1);
		$this->db->where('active_status', 1);
		$this->db->where('merchant_row_id', $merchant_row_id);
		$resultSet = $this->db->get();
		return $resultSet->result();
	}

    function getOtherFeaturedProduct ($merchant_row_id, $productArray = array())
    {
        if(count($productArray))
        {
            foreach ($productArray as $item)
            {
                $product_array[] = $item['product_row_id'];
            }
            $ids = implode(',', $product_array);
            $queryString = " AND product_row_id NOT IN({$ids})";
        }

        $sql = "SELECT * FROM tbl_product WHERE is_featured=1 AND merchant_row_id={$merchant_row_id} AND active_status=1 " . $queryString;
        $query = $this->db->query($sql);
        $results = $query->result();
        foreach($results as $key => $item)
        {
            $results[$key]->enable_sales_price = $this->checkSalePrice($item->enable_sales_price, $item->sale_price_start_date, $item->sale_price_end_date);
        }
        return $results;
    }

	/* this function fetches info of all other products possessed by the merchant except the given
	 * product_row_id
	 */
	function getOtherProducts ($merchant_row_id, $product_row_id)
	{
		$rel = $this->getRelatedProducts($product_row_id);
		for ($i = 0; $i < count($rel); $i ++)
		{
			$related_product_row_id_array[$i] = $rel[$i]->related_product_row_id;
		}
		$sql = '(SELECT product_row_id,
                        product_id,
                        product_row_id as PR,
				        product_description,
                        product_code,
                        product_price
				 FROM tbl_product
                 WHERE merchant_row_id =' . $merchant_row_id . '
				 AND product_row_id <> ' . $product_row_id . '
				 AND active_status=1
				 AND is_gift_certificate = 0) AS abc';

        $this->load->library ( 'rapyd' );
		$this->rapyd->load('datagrid');
		$grid = new DataGrid('', $sql);
		$grid->showFeaturedBox = true;
		$grid->currentPageNo = $this->config->item('relatedProduct');
		$grid->relatedProductList = $related_product_row_id_array;
		$grid->order_by('product_row_id', 'asc');
		$grid->per_page = 10;
		$grid->column('Related Products', 'PR', 'align="center" width="90"');
        $grid->column_orderby('Product ID', 'product_id');
		$grid->column_orderby('Product Name', 'product_description');
		$grid->column_orderby('SKU', 'product_code', 'product_code', 'align="left"');
		$grid->column_orderby('Product Price', '$<#product_price#>', 'product_price', 'align="left"');
		$grid->build();
		return $grid->output;
	}

	/* gets all the related products related with the given product*/
	function getRelatedProducts ($productRowIds)
	{
        $sql = 'SELECT r.product_row_id,
                       r.related_product_row_id,
                       r.is_bundle,
                       p.product_description AS related_product_description,
                       p.product_price,
                       p.product_image,
                       p.addToCartText,
                       p.oneClickBuyLink,
                       p.product_code,
                       p.product_sale_price,
                       p.enable_sales_price,
                       p.sale_price_end_date,
                       p.sale_price_start_date,
                       p.sale_tag_icon
                FROM tbl_product_relation r
                INNER JOIN tbl_product p ON p.product_row_id = r.related_product_row_id
                WHERE r.product_row_id IN (' . $productRowIds . ")  AND r.is_bundle =0 ORDER BY p.product_description";

        $resultSet = $this->db->query($sql);
		return $resultSet->result();
	}

    function getRelatedBundleProducts($productRowIds)
    {
        $sql = 'SELECT r.product_row_id,
                       r.related_product_row_id,
                       r.is_bundle,
                       CONCAT(p.offer_name, \'\', \' (<b>Bundle Product</b>)\') AS related_product_description,
                       p.bundle_price AS product_price,
                       p.bundle_image,
                       p.regular_buy_link AS addToCartText,
                       p.one_click_buy_link AS oneClickBuyLink,
                       p.bundle_sku AS product_code
                FROM tbl_product_relation r
                INNER JOIN tbl_product_bundle p ON p.product_bundle_row_id = r.related_product_row_id
                WHERE r.product_row_id IN( ' . $productRowIds . ")  AND r.is_bundle = 1 ORDER BY p.offer_name";

        $resultSet = $this->db->query($sql);
        return $resultSet->result();
    }

    function getRelatedUnassignProduct($merchant_row_id, $product_row_id, $relatedProductIds)
    {
        $sql = "SELECT * FROM tbl_product WHERE
                             merchant_row_id = {$merchant_row_id} AND
                             active_status = 1 AND
                             is_gift_certificate = 0 AND
							 is_editable = 1 AND
                             product_row_id NOT IN({$relatedProductIds})  ORDER BY product_description";
        $query = $this->db->query($sql);
        return $query->result_array();
    }

	function deleteIfInRelated ($product_row_id)
	{
		$sql = "DELETE FROM tbl_product_relation WHERE related_product_row_id = $product_row_id";
		$this->db->query($sql);
	}

	/* this function sets related products to a product */
	function setRelatedProducts ($product_row_id, $related_product_row_id, $is_bundle = 0)
	{
		$data['product_row_id'] = $product_row_id;
		$data['related_product_row_id'] = $related_product_row_id;
        $data['is_bundle'] = $is_bundle;
		$this->db->insert('tbl_product_relation', $data);
	}

    function searchUnassignRelatedProduct($sql, $relatedIdsQuery = null)
    {
        $sql = $sql.$relatedIdsQuery;
        $query = $this->db->query($sql);
        return $query->result_array();
    }

	/*checks if the given related product row id is previously set to the given product row id*/
	function checkIfRelated ($product_row_id, $related_product_row_id)
	{
		$this->db->select('count(*) as CHKREL');
		$this->db->from('tbl_product_relation');
		$this->db->where('product_row_id', $product_row_id);
		$this->db->where('related_product_row_id', $related_product_row_id);
		$resultSet = $this->db->get();
		return $resultSet->result();
	}

	/*returns the array of all related products from the product array inserted*/
	function getRelatedProductArray ($productArray, $bundleArray = array())
	{
		$m = 0;
		$total_product_in_cart = count($productArray);

		foreach ($productArray as $item)
		{
			$product_array[] = $item['product_row_id'];
		}

        foreach($bundleArray as $item)
        {
            $bundle_array[] = $item['product_row_id'];
        }

        if(count($product_array) > 0)
        	$related_product_array = $this->getRelatedProducts(implode(',', $product_array));

        if(isset($bundle_array) && count($bundle_array) > 0)
        {
        	$related_bundle_array = $this->getRelatedBundleProducts(implode(',', $bundle_array));
          $related_product_array = array_merge($related_product_array, $related_bundle_array);
        }
        foreach($related_product_array as $item)
        {
            if(!$item->is_bundle)
            {
                if (! in_array($item->related_product_row_id, $product_array))
                {
                   $temp[$item->related_product_row_id] = $item;
                }
            }
            else
            {
                if (! in_array($item->related_product_row_id, $bundle_array))
                {
                   $temp[$item->related_product_row_id] = $item;
                }
            }

        }

        if (isset($temp) && $temp != null)
        {
            $products = array();
            foreach($temp AS $item)
            {
                $product['related_product_row_id']  = $item->related_product_row_id;
                $product['related_product_description']  = $item->related_product_description;
                $product['add_to_cart_link']   = NONSECUREBASEURL . $item->addToCartText;
                $product['click_to_buy_link']  = NONSECUREBASEURL . $item->oneClickBuyLink;

                $product['product_price']  = $item->product_price;
                $product['is_bundle']  = $item->is_bundle;
                $product['product_image']  = $item->product_image;
                if($item->is_bundle && $item->bundle_image)
                   $product['product_image']  = 'images/bundle/' . $item->bundle_image;

                $product['product_sale_price'] = $item->product_sale_price;
                $product['enable_sales_price'] = $this->checkSalePrice($item->enable_sales_price, $item->sale_price_start_date, $item->sale_price_end_date);
                $product['sale_tag_icon'] = $item->sale_tag_icon;
                $products[] = $product;
            }
        }
        else
        {
            $products = null;
        }

		return $products;
	}
    function checkSalePrice($status, $startDate, $endDate)
    {
        $today     = strtotime(date('Y-m-d'));
        $startDate = (substr($startDate, 0, 4) > 1) ? $startDate : '1970-01-01';
        $endDate   = (substr($endDate, 0, 4) > 1) ? $endDate :  (date('Y') + 1) . '-01-01';
        if($status && strtotime($startDate) <= $today && strtotime($endDate) >= $today){
            return true;
        }
        return false;
    }

	/*************************INVENTORY MANAGEMENT*********************************/

	/**
	 * @author Himel Nag Rana
	 *
	 * @param product_row_id, new_quantity
	 *
	 * This function reduces inventory product quantity
	 */
	function updateInventoryQuantity($product_row_id,$new_quantity, $attribute_row_id = null)
	{
        $fields = array('current_inventory', 'reorder_level', 'merchant_row_id', 'use_inventory', 'product_description');
		$productDetails = $this->getProduct($product_row_id, 0, $fields);
        $current_inventory = $productDetails['current_inventory'];
        $reorder_level = $productDetails['reorder_level'];

        $merchant_row_id = $productDetails['merchant_row_id'];

        $getInstance = &get_instance();

        if($productDetails['use_inventory'])
        {
            if($productDetails['use_inventory'] == 1)
            {
                $this->db->set('current_inventory',($current_inventory-$new_quantity));
                $this->db->where('product_row_id',$product_row_id);
                $this->db->update('tbl_product');
            }
            else if ($attribute_row_id && $productDetails['use_inventory'] == 2)
            {
                $sql = "UPDATE tbl_product_attribute_group SET quantity = quantity - {$new_quantity} WHERE product_row_id = {$product_row_id} AND attribute_row_id = '{$attribute_row_id}' AND quantity > -9999";
                $this->db->query($sql);
            }

            $getInstance->load->model('api/Notification_model', 'Notification_model', true);
            if($this->config->item('enable_stores_notification'))
            {
                $info['status'] = 'sales';

                if($attribute_row_id)
                    $info['attribute_row_id'] = $attribute_row_id;

                $info['product_row_id']   = $product_row_id;
                $info['quantity']         = $new_quantity;
                $info['merchant_row_id']  = $merchant_row_id;
                $getInstance->Notification_model->sendNotificationOnSales($info);
            }

            if( $productDetails['use_inventory'] == 1 &&  ($current_inventory - $new_quantity) < $reorder_level )
            {
                $sendAlert = true;
            }
            else if($attribute_row_id)
            {
                $getInstance->load->model('products/Attribute_model', 'Attribute_model', true);
                $options = $getInstance->Attribute_model->getOptionInfo($attribute_row_id , $product_row_id);
                if($productDetails['use_inventory'] == 2 && ($options['quantity'] - $new_quantity) < $options['reorder_quantity'])
                {
                    $sendAlert = true;
                }
            }
            if($sendAlert)
            {
                $getInstance->load->model('merchant/Merchant_model','merchant_model',TRUE);

                $merchantBasicInfo = $getInstance->merchant_model->getMerchantDetails($merchant_row_id);
                $getInstance->load->model('carts/Cart_Setup_model', 'Cart_Setup_model', TRUE);
                $cartBasic = $getInstance->Cart_Setup_model->get_basic_setup($merchant_row_id);

                $getInstance->load->model('administration/contact_profile_model','Contact_Profile_model',TRUE);
                $contact_profile_admin = $getInstance->Contact_Profile_model->getSuperAdminContactDetails();

                $getInstance->load->model('Mail_manager_model', 'Mail_manager_model', TRUE);
                $data['product_name'] = $productDetails['product_description'];

                $getInstance->Mail_manager_model->sendLowInventoryAlert($merchant_row_id, $contact_profile_admin['email_address'], $contact_profile_admin['full_name'], $cartBasic->primary_destination, $data, 1044);
            }
        }
	}

	/*************************INVENTORY MANAGEMENT*********************************/


	/***************************EXPORT************************/
	/**
	 *@author Shafiul Alam.
	 *@modified by : Himel Nag Rana.
	 *function of export data to csv format
	 */
	function exportToCSV( $sql, $delimeter = ',' )
	{
		$this->load->dbutil();
		$this->load->helper('download');

		$newline = "\r\n";
		$result = $this->db->query( $sql );
		$data = $this->dbutil->csv_from_result($result,$delimeter,$newline);
		force_download('product list.csv',$data);
	}

    function exportToCSVFormat( $sql, $delimeter = ',' )
    {

        $this->load->dbutil();
        $this->load->helper('download');

        $newline = "\r\n";
        $result = $this->db->query( $sql );
        $data = $this->cvs_format_result($result,$delimeter,$newline);
        force_download('product_list.csv',$data);
    }

    function cvs_format_result($result, $delimeter = "\t", $newline = "\n")
    {
        $i = 0;
        foreach ($result->list_fields() as $column)
        {
            $column_header[$i]['column_header'] = $column;
            $i++;
        }
        foreach($column_header as $column)
        {
            $column_header_out .= $column['column_header'].$delimeter;
        }
        $column_header_out = rtrim($column_header_out);
        $out = $column_header_out;
        $out .= $newline;

        foreach ($result->result_array() as $row)
        {
            $rowstring = '"';
            $count = 1;
            foreach ($row as $key => $item)
            {
                if($count < count($row))
                    $rowstring .=  str_replace( '"','""',html_entity_decode($item)) . '","';
                else
                    $rowstring .=  str_replace( '"','""',html_entity_decode($item)) . '"' . "\n";
                $count++;
            }
            $out .= $rowstring;
        }
        return $out;
    }
	/***************************EXPORT************************/
    function update_bulk_product($data,$data_look_and_feel, $recurringProfile)
    {                            
        if($data['product_row_id'])
        {
            $this->updateDefaultRecurringProfile($recurringProfile);
            $sql = 'SELECT product_row_id
                    FROM tbl_product
                    WHERE product_row_id = '.$data['product_row_id'].'
                    AND merchant_row_id = '.$data['merchant_row_id'];
            $res = $this->db->query($sql);
            $result = $res->row();
            if($result)
            {
                $this->db->where('product_row_id', $data['product_row_id']);
                $this->db->where('merchant_row_id', $data['merchant_row_id']);
                $this->db->update('tbl_product',$data);

                $this->db->where('product_row_id', $data['product_row_id']);
                $this->db->update('tbl_product_look_and_feel',$data_look_and_feel);
                return 'update';
            }
            else
            {
                $data['product_row_id'] = '';
            }
        }
        if(!$data['product_row_id'])
        {
           foreach ($data as $key=>$value)
                {
                    if($key != 'product_row_id')
                    {
                        $prodData[$key] = $value;
                    }
                }
                $prodData['active_status'] = 1;
				$prodData['is_editable'] = 1;
                $prodData['created_by'] = $data['merchant_row_id'];
                $prodData['created_on'] = getCurrentDateTimeForDB();
                $prodData['modified_by'] = $data['merchant_row_id'];
                $prodData['modified_on'] = getCurrentDateTimeForDB();
                $this->db->insert('tbl_product', $prodData);
                $product_row_id = $this->db->insert_id();

                if($recurringProfile['recurring_price'] > 0)
                {
                    $this->db->insert('tbl_selectable_recurring_profile', $recurringProfile);
                }

                $clearCartFlag = '/'.$prodData['clear_cart'];
                $enc_product_row_id = md5($product_row_id);
                $addToCartText = 'carts/shopping_cart/myShoppingCart/' . $enc_product_row_id.$clearCartFlag;
                $oneClickBuyLink = 'carts/shopping_cart/oneClickProductBuy/' . $enc_product_row_id.$clearCartFlag;

                $getInstance = & get_instance();
                $getInstance->load->model('carts/Cart_Setup_model', 'Cart_Setup_model', TRUE);
                $cartBasic = $getInstance->Cart_Setup_model->get_basic_setup($prodData['merchant_row_id']);
                $product_id_prefix = $cartBasic->product_id_prefix;

                //here 'PRO'=>prefix for product id will be taken from basic setup table
                $this->load->library('Application_Utility');
                $product_id = $this->application_utility->generateId($product_id_prefix,$product_row_id);

                $this->db->set('product_id', $product_id);
                $this->db->set('enc_product_row_id', $enc_product_row_id);
                $this->db->set('addToCartText', $addToCartText);
                $this->db->set('oneClickBuyLink', $oneClickBuyLink);
                $this->db->where('product_row_id', $product_row_id);
                $this->db->update('tbl_product');

                $data_look_and_feel['font_family'] = 0;
                $data_look_and_feel['product_row_id'] = $product_row_id;
                $this->db->insert('tbl_product_look_and_feel', $data_look_and_feel);
                return 'insert';
        }
    }
	/*******************************DIGITAL PRODUCT**************************/

    function uploadCSVFile($fieldName)
    {
        $config['upload_path'] = 'images/productUpload/';
        $config['allowed_types'] = 'csv';
        $this->load->library('upload', $config);

        if ($this->upload->do_upload($fieldName))
        {
            $data = $this->upload->data();
            $fileName = $config['upload_path'] . $data['file_name'];
            $return = array('file_name' => $fileName , 'error' => '');
            return $return;
        } else
        {
            $err = '';
            //removing the error as image upload is not required
            if ($this->upload->display_errors() != 'You did not select a file to upload')
            {
                $err = $this->upload->display_errors();
            }
            $return = array('file_name' => '' , 'error' => $err);
            return $return;
        }
    }
	function uploadDigitalProduct($fieldName)
	{
		$digital_product_file_types = $this->config->item('digital_product_file_types');
        $i = 0;
        foreach($digital_product_file_types as $key=>$value)
        {
            if(!is_array($value))
            {
                $typeArray[$i] = $value;
                $i++;
            }
            else
            {
                foreach($value as $val)
                {
                    $typeArray[$i] = $val;
                    $i++;
                }
            }
        }


        $fileTypeCondition = implode('|',$typeArray);
		$fileTypeCondition = $fileTypeCondition;

        $merchant_row_id = getCurrentMerchantId();
        if(!is_dir($this->config->item('digital_product_path') . $merchant_row_id))
        {
            exec('mkdir ' . $this->config->item('digital_product_path') . $merchant_row_id);
            exec('chmod 777 ' . $this->config->item('digital_product_path') . $merchant_row_id);
        }
        $config['upload_path'] = $this->config->item('digital_product_path') . $merchant_row_id . '/';

		$config['allowed_types'] = $fileTypeCondition;
        $digitalDownLoadSize = $this->config->item('allowed_digital_download');
		//$config['max_size']  = $digitalDownLoadSize[$_SESSION['m_package_id']] * 1024;;
        $config['max_size']  = '51200';
		$config['max_width'] = '';
		$config['max_height'] = '';

		$this->load->library('upload', $config);
		if ($this->upload->do_upload($fieldName,1))
		{
			$data = $this->upload->data();
			$fileName = addslashes($config['upload_path']) . $data['file_name'];
			$return = array('file_name' => $fileName , 'error' => '');
			return $return;
		}
        else
		{
			$err = '';
			//removing the error as image upload is not required
			if ($this->upload->display_errors() != 'You did not select a file to upload')
			{
				$err = $this->upload->display_errors();
			}
			$return = array('file_name' => '' , 'error' => $err);

			return $return;
		}
	}

	function updateDigitalProduct($data)
	{
		$allOfThis = $this->getProduct($data['product_row_id'], 0, array('digital_product_path'));
		$digital_product_path = $allOfThis['digital_product_path'];

		if($digital_product_path)
		{
			$url = $digital_product_path;
			unlink($url);
		}

		$this->db->set('digital_product_file_name',$data['digital_product_file_name']);
		$this->db->set('digital_product_file_type',$data['digital_product_file_type']);
		$this->db->set('digital_product_path',$data['digital_product_path']);
		$this->db->set('is_digital',1);
		$this->db->where('product_row_id',$data['product_row_id']);
		$this->db->update('tbl_product');
	}

	function deleteDigitalProduct($product_row_id)
	{
        if(!empty($product_row_id))
        {
		    $allOfThis = $this->getProduct($product_row_id, 0, array('digital_product_path', 'external_digital_download_link'));
		    $digital_product_path = $allOfThis['digital_product_path'];
		    if($digital_product_path)
		    {
			    $url = $digital_product_path;
			    unlink($url);
		    }

		    $this->db->set('digital_product_path', "");
		    $this->db->set('digital_product_file_name', "");
		    $this->db->set('digital_product_file_type', "");
		    $this->db->set('enable_digital_product', "");

            if(!$allOfThis['external_digital_download_link'])
		        $this->db->set('is_digital', "0");

            //$this->db->set('low_digital_license_level', "0");
		    //$this->db->set('no_of_used_licenses', "0");
		    $this->db->where('product_row_id', $product_row_id);
		    $this->db->update('tbl_product');
        }
	}

	function uploadDigitalProductLicense($digital)
	{
		$product_row_id = $digital['product_row_id'];
		$licenseCodeArray = $digital['codes'];
		$licenseCount = $digital['count'];
		$productInfo = $this->getProduct($product_row_id, 0, array('digital_product_file_name'));
		$check = $file_name	= $productInfo['digital_product_file_name'];

		if($file_name)
		{
			for($i=0;$i<$licenseCount;$i++)
			{
				$data['product_row_id'] = $product_row_id;
				$data['license_key'] = $licenseCodeArray[$i];

				$sql = "INSERT INTO tbl_product_digital (product_row_id,license_key) VALUES(".$product_row_id.",'".$licenseCodeArray[$i]."')";
				$this->db->query($sql);
			}
			return true;
		}
		else
		{
			return false;
		}
	}

	function getLicenseKey($product_row_id)
	{
		$this->db->select('*');
		$this->db->from('tbl_product_digital');
		$this->db->where('product_row_id',$product_row_id);
		$this->db->where('used_flag',0);
		$res =  $this->db->get();
		$result = $res->result();

        $cnt = count($result);
		for($i=0;$i<$cnt;$i++)
		{
			$licenseKey[$i] = $result[$i]->license_key;
			$product_digital_row_id[$i] = $result[$i]->product_digital_row_id;
		}

		$digital[0] = $licenseKey;
		$digital[1] = $product_digital_row_id;

		return $digital;
	}

	function countLicense($product_row_id)
	{
		$this->db->select('count(product_row_id) as CNT');
		$this->db->from('tbl_product_digital');
		$this->db->where('product_row_id',$product_row_id);
		$this->db->where('used_flag',0);
		$res =  $this->db->get();
		$result = $res->row();
		return $result->CNT;
	}

	function deleteLicense($product_row_id)
	{
		$this->db->where('product_row_id', $product_row_id);
		$this->db->delete('tbl_product_digital');
	}

	function deleteDigitalProductTempInfo($product_row_id)
	{
		$digiresult = $this->getDigitalProductTempInfo($product_row_id);
		$digital_product_temp_path = $digiresult[0]->digital_product_path;
		unlink($digital_product_temp_path);

		$this->db->where('product_row_id', $product_row_id);
		$this->db->delete('tbl_product_digital_temp');
	}

	function saveMailTrack($data)
	{
		$sql = "INSERT INTO tbl_digital_download_track
				(product_row_id,order_id,download_mail_link,mail_firing_time)
				VALUES
				('" . $data['product_row_id']. "',
                '" . $data['order_row_id'] . "',
                '" . $data['download_page_link'] . "',
                '" . getCurrentDateTimeForDB() . "')";
		$this->db->query($sql);
	}

	function checkDownload($digital_download_track_id,$digital_product_path)
	{
        if(!empty($digital_download_track_id) && !empty($digital_product_path))
        {
            $currentDateTime = getCurrentDateTimeForDB();

		    $this->db->select('HOUR(TIMEDIFF(mail_firing_time,\''.$currentDateTime.'\')) AS LENGTH');
		    $this->db->from('tbl_digital_download_track');
		    $this->db->where('digital_download_track_id',$digital_download_track_id);
            $this->db->where('download_mail_link',$digital_product_path);
		    $res = $this->db->get();
		    $result = $res->row();

		    if($result){
			    if($result->LENGTH < 24){
				    return true;
			    }
		    }
        }

        return false;
	}

	function checkLicenseKeyMailing($digital_download_track_id,$digital_product_path)
	{
		$this->db->select('license_key_mail_status');
		$this->db->from('tbl_digital_download_track');
        $this->db->where('digital_download_track_id',$digital_download_track_id);
		$this->db->where('download_mail_link',$digital_product_path);
		$res = $this->db->get();
		$result = $res->row();

		if($result){
			if($result->license_key_mail_status == 1){
				return FALSE;
			}
			else{
				return TRUE;
			}
		}

        return false;
	}

	function getDownloadTrackId($order_row_id,$product_row_id)
	{
		if($order_row_id && $product_row_id)
        {
           $result = $this->db->query("SELECT digital_download_track_id FROM tbl_digital_download_track WHERE product_row_id={$product_row_id} AND order_id={$order_row_id} ORDER BY digital_download_track_id DESC LIMIT 0, 1 ")->row();
		   return $result->digital_download_track_id;
        }
        return false;
	}

	/**
	 * This function saves or keeps track of the digital products download of whom is barred
	 * because of lack of license key.
	 *
	 * @param $order_row_id
	 * @param $product_row_id
	 */
	function savePendingTrack($order_row_id,$product_row_id)
	{
		$this->db->set('mail_link_pending_flag',1);
		$this->db->where('order_row_id',$order_row_id);
		$this->db->where('product_row_id',$product_row_id);
		$this->db->update('tbl_order_details');
	}

	/**
	 * This function will be called from the download controller after every download
	 *
	 * @param $order_row_id
	 * @param $product_row_id
	 * @return email to customer containing license key for digital product.
	 */
	function mailLicenseKey($order_row_id,$product_row_id)
	{
		$this->lang->load('message');

		$getInstance = &get_instance();
		$getInstance->load->model('merchant/Merchant_model', 'merchant_model', TRUE);

		$licenseArray = $this->getLicenseKey($product_row_id);
		$data['licenseKey'] = $licenseArray[0][0];

		$productInfo = $this->getProduct($product_row_id, 0, array('merchant_row_id', 'product_id', 'product_description'));
		$merchant_row_id = $productInfo['merchant_row_id'];
		$data['product_id'] = $productInfo['product_id'];
		$data['product_description'] = $productInfo['product_description'];

		$merchantDetails = $getInstance->merchant_model->getMerchantDetails($merchant_row_id);
		$data['merchant_row_id'] = $productInfo['merchant_row_id'];
		$data['merchant_company'] = $merchantDetails->company_name;
		$data['merchant_account_url'] = $merchantDetails->website_url;
		$data['merchant_full_name'] = $merchantDetails->first_name. ' ' .$merchantDetails->last_name;

	 	$data['contact_profile_merchant'] = $getInstance->merchant_model->getContactFooterForMerchant($merchant_row_id);

		$getInstance->load->model('carts/Cart_Setup_model', 'cart_setup_model', TRUE);
		$basicInfo = $getInstance->cart_setup_model->get_basic_setup ($merchant_row_id);
		$data['from_address'] = $basicInfo->from_address;

		/*gathering customer info from order_id*/
		$data['order_row_id'] = $order_row_id;
		$getInstance->load->model('orders/Order_model', 'order_model', TRUE);
		$orderDetails = $getInstance->order_model->getOrderByRowId($order_row_id);
		$customer_id = $orderDetails->customer_id;
		$data['order_id'] = $orderDetails->order_id;

		$getInstance->load->model('customers/Customer_Setup_model', 'customer_setup_model', TRUE);
		$customerInfo = $getInstance->customer_setup_model->getCustomer($customer_id);
		$first_name = $customerInfo->first_name;
		$last_name = $customerInfo->last_name;
		$customer_email = $customerInfo->customer_email;
		$data['customer_name'] = $first_name.' '.$last_name;
		$data['to_address'] = $customer_email;
		/*gathering customer info from order_id*/

		if (isset($data['licenseKey'])) {

            $getInstance->load->model('Mail_Manager_model', 'Mail_Manager_model', TRUE);
            $getInstance->Mail_Manager_model->sendDigitalProductLicenseEmail($merchant_row_id, $data['from_address'], $data['to_address'], $data);
		}
		$mailed_license_row_id = $licenseArray[1][0];
		$this->db->set('used_flag',1);
		$this->db->where('product_row_id',$product_row_id);
		$this->db->where('product_digital_row_id',$mailed_license_row_id);
		$this->db->update('tbl_product_digital');

		$this->db->set('license_key_mail_status',1);
        $this->db->where('product_row_id',$product_row_id);
		$this->db->where('order_id',$order_row_id);
		$this->db->update('tbl_digital_download_track');
	}

	/**
	 * this function has to be called from there where digital product license is being uploaded
		then from the output array of order_row_id previously declared digitalProductDownloadLink
		has to be called and mail_link_pending_flag field of tbl_order_details will be made 0.
	 *
	 * @param  $product_row_id
	 * @return $order_row_id_array
	 */
	function checkPending($product_row_id)
	{
		$this->db->select('*');
		$this->db->from('tbl_order_details');
		$this->db->where('product_row_id',$product_row_id);
		$this->db->where('is_digital',1);
		$this->db->where('mail_link_pending_flag',1);
		$res = $this->db->get();
		$result = $res->result();
        $cnt = count($result);

		for($i=0;$i<$cnt;$i++)
		{
			$order_row_id_array[$i] = $result[$i]->order_row_id;
		}

		return $order_row_id_array;
	}

	/**
	 * This fucntion is used to clear the pending link mails and update the database.
	 *
	 * @param $order_row_id
	 * @param $product_row_id
	 */
	function clearPendingLinkMailing($order_row_id,$product_row_id)
	{
		$productInfo = $this->getProduct($product_row_id, 0, array('merchant_row_id', 'product_description'));
		$merchant_row_id = $productInfo['merchant_row_id'];
		$data['product_description'] = $productInfo['product_description'];
		$data['product_row_id'] = $product_row_id;
		$data['order_id'] = $order_row_id;

		$getInstance = &get_instance();
		$getInstance->load->model('merchant/Merchant_model', 'merchant_model', TRUE);

		$merchantDetails = $getInstance->merchant_model->getMerchantDetails($merchant_row_id);
		$company_name = $merchantDetails->company_name;
		$website_url = $merchantDetails->website_url;

		$getInstance->load->model('carts/Cart_Setup_model', 'cart_setup_model', TRUE);
		$basicInfo = $getInstance->cart_setup_model->get_basic_setup ($merchant_row_id);
		$primary_destination = $basicInfo->primary_destination;
		$secondary_destination = $basicInfo->secondary_destination;
		$from_address = $basicInfo->from_address;

		$data['from_address'] = $from_address;
		$data['merchant_email'] = $from_address;
		$data['merchant_row_id'] = $merchant_row_id;
		$data['merchant_company'] = $company_name;
		$data['merchant_account_url'] = $website_url;

		/*gathering customer info from order_id*/
		$data['order_row_id'] = $order_row_id;
		$getInstance->load->model('orders/Order_model', 'order_model', TRUE);
		$orderDetails = $getInstance->order_model->getOrderByRowId($order_row_id);
		$customer_id = $orderDetails->customer_id;

		$getInstance->load->model('customers/Customer_Setup_model', 'customer_setup_model', TRUE);
		$customerInfo = $getInstance->customer_setup_model->getCustomer($customer_id);
		$first_name = $customerInfo->first_name;
		$last_name = $customerInfo->last_name;
		$customer_email = $customerInfo->customer_email;
		$data['customer_name'] = $first_name.' '.$last_name;
		$data['to_address'] = $customer_email;
		/*gathering customer info from order_id*/

		$this->lang->load('message');

		$data['subject'] = $this->lang->line('DIGITAL_PRODUCT_DOWNLOAD');
		$data['download_page_link'] = base_url().'download/secure/'.$order_row_id.'/'.$product_row_id.'/'.$merchant_row_id.'.html';

		$getInstance->load->model('mailData/Mailer_model','mailer_model',TRUE);
		$getInstance->mailer_model->sendOrderPlaceEmail($data,'clearPendingDownloads');

		$this->saveMailTrack($data);

		$this->db->set('mail_link_pending_flag',0);
		$this->db->where('product_row_id',$product_row_id);
		$this->db->where('is_digital',1);
		$this->db->update('tbl_order_details');
	}
	/*******************************DIGITAL PRODUCT**************************/

	/*******************************AFFILIATE PROGRAM************************/
	function getUnassignedProductRowIdArray($merchant_row_id,$merchant_aff_program_row_id)
	{
		$this->db->select('product_row_id');
		$this->db->from('tbl_aff_program_product');
		$this->db->where('merchant_aff_program_row_id',$merchant_aff_program_row_id);
		$ress = $this->db->get();
		$result = $ress->result();
		if(count($result)>0)
		{
			for($i=0;$i<count($result);$i++)
			{
				$assignedProduct.= $result[$i]->product_row_id;
				if($i!=(count($result)-1))
				$assignedProduct.=',';
			}
			$assignedProduct = '('.$assignedProduct.')';
		}
		$unassignedProductRowIdArray = array();

		$sql = "SELECT product_row_id
				FROM tbl_product
				WHERE merchant_row_id=$merchant_row_id
				AND is_gift_certificate = 0
				AND is_editable = 1";
		if($assignedProduct)
		{
			$sql = $sql.' AND product_row_id NOT IN '.$assignedProduct;
		}
		$res = $this->db->query($sql);
		$result = $res->result_array();
		$cnt = count($result);
		for($i = 0; $i < $cnt; $i++)
		{
			$unassignedProductRowIdArray[$i] = $result[$i]['product_row_id'];
		}

		return $unassignedProductRowIdArray;
	}

	function getUnassignedCategoryFromProductRowIdarray($unassignedProductRowIdArray)
	{
		$category_id_array = array();
		$j = 0;
		for($i=0;$i<count($unassignedProductRowIdArray);$i++)
		{
			$productInfo = $this->getProduct($unassignedProductRowIdArray[$i], 0, array('category_id'));
			if($productInfo['category_id'])
			{
				$category_id_array[$j] = $productInfo['category_id'];
				$j++;
			}
		}
		return $category_id_array;
	}


	function getAssignedProductRowIdArray($merchant_row_id,$merchant_aff_program_row_id)
	{
		$this->db->select('product_row_id');
		$this->db->from('tbl_aff_program_product');
		$this->db->where('merchant_aff_program_row_id',$merchant_aff_program_row_id);
		$ress = $this->db->get();
		$result = $ress->result();
		$cnt = count($result);
		if($cnt)
		{
			$lastIndex = $cnt-1;

			for($i = 0; $i < $cnt; $i++)
			{
				$assignedProduct.= $result[$i]->product_row_id;
				if($i != $lastIndex)
				{
					$assignedProduct.=',';
				}
			}
			$assignedProduct = '('.$assignedProduct.')';
		}

		$assignedProductRowIdArray = array();

		if($assignedProduct)
		{
			$sql = 'SELECT product_row_id
					FROM tbl_product
					WHERE merchant_row_id='.$merchant_row_id.'
					AND product_row_id IN '.$assignedProduct;

			$res = $this->db->query($sql);
			$result = $res->result_array();
			$cnt = count($result);
			for($i = 0; $i < $cnt; $i++)
			{
				$assignedProductRowIdArray[$i] = $result[$i]['product_row_id'];
			}
		}
		return $assignedProductRowIdArray;
	}

	function unassignedGrid($merchant_row_id,$merchant_aff_program_row_id, $first_level_commission = 0, $second_level_commission = 0)
	{
		$this->db->select('product_row_id');
		$this->db->from('tbl_aff_program_product');
		$this->db->where('merchant_aff_program_row_id',$merchant_aff_program_row_id);
		$ress = $this->db->get();
		$result = $ress->result();

		foreach($result as $item)
        {
            $assignedProduct[] = $item->product_row_id;
        }

        $first_level_commission  = $first_level_commission ? $first_level_commission . '%' : 0;
        $second_level_commission = $second_level_commission ? $second_level_commission . '%' : 0;

        if($_SESSION['category_id'] > 0)
        {
            $whereClause = ' AND p.category_id = ' . $_SESSION['category_id'];
        }
		$getInstance = & get_instance();
        $getInstance->load->model('affiliateAdmin/Affiliate_Setup_model', 'Affiliate_setup_model', TRUE);
		//$default_affiliate_program_info = $getInstance->Affiliate_setup_model->getAffiliateProgramInfo(getCurrentMerchantId());

		//$default_affiliate_program_name = $default_affiliate_program_info['program_name'];

        $sql = 'SELECT product_row_id,
                product_row_id as select_product,
                SUBSTRING(product_description,1,30) as product_description, product_code, product_price,
                IFNULL((SELECT GROUP_CONCAT(b.program_name SEPARATOR \', \') AS program_nm
                         FROM tbl_aff_program_product AS a JOIN
                                             tbl_merchant_aff_program AS b ON (a.merchant_aff_program_row_id = b.merchant_aff_program_row_id)
                                             WHERE a.product_row_id = p.product_row_id ORDER BY b.merchant_aff_program_row_id),\'N/A\')  AS program_name,

                            CASE WHEN affiliate_first_level_commission > 0 && include_commission > 0 && product_level_commission_type=2 THEN CONCAT(\''.getCurrencySymbol().'\',affiliate_first_level_commission)
                                 WHEN affiliate_first_level_commission > 0 && include_commission > 0 && product_level_commission_type=1 THEN CONCAT(affiliate_first_level_commission,\'%\')

                            ELSE \'' . $first_level_commission . '\' END AS first_level_commission,

                            CASE WHEN affiliate_second_level_commission > 0  && include_commission > 0 && product_level_commission_type=2 THEN CONCAT(\''.getCurrencySymbol().'\',affiliate_second_level_commission)
                                 WHEN affiliate_second_level_commission > 0  && include_commission > 0 && product_level_commission_type=1 THEN CONCAT(affiliate_second_level_commission,\'%\')
                            ELSE \'' . $second_level_commission . '\' END AS second_level_commission,
                category_description
                from tbl_product p LEFT OUTER JOIN tbl_category c
                ON p.category_id = c.category_id where p.merchant_row_id =' . $merchant_row_id . $whereClause . ' and active_status=1 AND p.is_editable = 1 AND is_gift_certificate = 0';

        if($assignedProduct)
		{
			$sql.= ' AND p.product_row_id NOT IN(' . implode(',', $assignedProduct) . ')';
		}
		$sql = '('.$sql.' ) AS abc';

		$this->load->library ( 'rapyd' );
		$this->rapyd->load('datagrid');
		$grid = new DataGrid('', $sql);

		$grid->showFeaturedBox = TRUE;
        $grid->showHeaderCheckbox = true;
        $grid->headerCheckboxColumn = 1;

        $grid->currentPageNo = 22;
        $grid->order_by('product_description', 'asc');
		$grid->per_page = $_SESSION['product_per_page'] ? $_SESSION['product_per_page'] : 10;

		$grid->column('', 'select_product', 'select_product', 'align=\'left\'');
		$grid->column_orderby('Product Name', '<#product_description#>', 'product_description', 'align=\'left\'');
		$grid->column_orderby('SKU', 'product_code', 'product_code', 'align=\'left\'');
		$grid->column('Category', 'category_description','align=\'left\' width=20%');
        $grid->column('Assigned Program', 'program_name','align=\'left\' width=20%');
		$grid->column_orderby('1st Level Com', '<#first_level_commission#>', 'first_level_commission', 'align=\'left\'');
		$grid->column_orderby('2nd Level Com', '<#second_level_commission#>', 'second_level_commission', 'align=\'left\'');
		$grid->column_orderby('Price', getCurrencySymbol().'<#product_price#>', 'product_price', 'align=\'left\'');
		$grid->build();
		return $grid->output;
	}

	function showProductGrid2($merchant_row_id,$merchant_aff_program_row_id, $first_level_commission = 0, $second_level_commission = 0)
	{
		$this->db->select('product_row_id');
		$this->db->from('tbl_aff_program_product');
		$this->db->where('merchant_aff_program_row_id',$merchant_aff_program_row_id);
		$ress = $this->db->get();
		$result = $ress->result();

        $first_level_commission  = $first_level_commission ? $first_level_commission . '%' : 0;
        $second_level_commission = $second_level_commission ? $second_level_commission . '%' : 0;

		$cnt = count($result);
		if($cnt)
		{
            foreach($result as $item)
            {
               $assignedProduct[] = $item->product_row_id;
            }
			$sql = '(SELECT product_row_id,
							product_row_id AS select_product2,
							product_description,
							product_code,
							product_price,
                            CASE WHEN affiliate_first_level_commission > 0 && include_commission > 0 && product_level_commission_type=2 THEN CONCAT(\''.getCurrencySymbol().'\',affiliate_first_level_commission)
                                 WHEN affiliate_first_level_commission > 0 && include_commission > 0 && product_level_commission_type=1 THEN CONCAT(affiliate_first_level_commission,\'%\')

                            ELSE \'' . $first_level_commission . '\' END AS first_level_commission,

                            CASE WHEN affiliate_second_level_commission > 0  && include_commission > 0 && product_level_commission_type=2 THEN CONCAT(\''.getCurrencySymbol().'\',affiliate_second_level_commission)
                                 WHEN affiliate_second_level_commission > 0  && include_commission > 0 && product_level_commission_type=1 THEN CONCAT(affiliate_second_level_commission,\'%\')
                            ELSE \'' . $second_level_commission . '\' END AS second_level_commission,
                            IFNULL(merchant_affiliate_program_name,\'n/a\') AS merchant_affiliate_program_name,
							category_description
					FROM tbl_product p
					LEFT OUTER JOIN tbl_category c ON p.category_id = c.category_id
					WHERE p.product_row_id IN(' . implode(',' ,$assignedProduct) . ')
					AND p.merchant_row_id =' . $merchant_row_id . '
					AND active_status = 1) AS abc';
		}
		else
		{
			$sql = '( SELECT p.*,
                             affiliate_first_level_commission as first_level_commission,
                             p.affiliate_second_level_commission as second_level_commission
                             FROM tbl_product AS p WHERE product_row_id = 0 ) AS abc';
		}

		$this->load->library ( 'rapyd' );
		$this->rapyd->load('datagrid');
		$grid = new DataGrid('', $sql);

		$grid->showFeaturedBox = TRUE;
		$grid->currentPageNo = $this->config->item('affiliateProgram2');
        $grid->order_by('product_description', 'asc');
        $grid->showHeaderCheckbox = true;
        $grid->headerCheckboxColumn = 1;
		$grid->column('', 'select_product2', 'select_product2', 'align=\'left\'');
		$grid->column_orderby('Product Name', 'product_description', 'product_description', 'align=\'left\'');
		$grid->column_orderby('SKU', 'product_code', 'product_code', 'align=\'left\'');
        $grid->column('Category', 'category_description','align=\'left\' width=20%');
		$grid->column_orderby('1st Level Com', '<#first_level_commission#>', 'first_level_commission', 'align=\'left\'');
		$grid->column_orderby('2nd Level Com', '<#second_level_commission#>', 'second_level_commission', 'align=\'left\'');
		$grid->column_orderby('Price', getCurrencySymbol().'<#product_price#>', 'product_price', 'align=\'left\'');
        $grid->column_detail('','Progressive Commission','progressive_commission/progressive_commission/index/' . $merchant_aff_program_row_id . '/<#product_row_id#>', 'align=\'center\'', false, 'class="submodal-800-570"', true, '');

		$grid->build();
		return $grid->output;
	}

	function getCountForCSV($running_sql)
	{
		$res = $this->db->query($running_sql);
		$num_rows = $res->num_rows();
		return $num_rows;
	}

    function uploadProduct ($fieldName)
	{
        $config['upload_path'] = 'images/productUpload/';
		$config['allowed_types'] = 'csv';
		#$config['max_size'] = '1000';
		#$config['max_width'] = '200';
		#$config['max_height'] = '200';
		$this->load->library('upload', $config);
		if ($this->upload->do_upload($fieldName))
		{
            $data = $this->upload->data();
			$fileName = $config['upload_path'] . $data['file_name'];
			$return = array('file_name' => $fileName , 'error' => '');
			return $return;
		} else
		{
			$err = '';
			//removing the error as image upload is not required
			if ($this->upload->display_errors() != 'You did not select a file to upload')
			{
				$err = $this->upload->display_errors();
			}
			$return = array('file_name' => '' , 'error' => $err);
			return $return;
		}
	}

	/**
     * @desc this function update the product table for Import
	 * @author Shafiul Alam
	 * @param unknown_type $insert_sql
	 * @version PWC V1.2
	 */

	function importProducts($insert_sql)
	{
		for($i=0;$i<count($insert_sql);$i++)
		{

			$this->db->query($insert_sql[$i]);

			$product_row_id = $this->db->insert_id();
            $enc_product_row_id = md5($product_row_id);

            /////// code to get the prefix of product id from basic setup////////////
			$merchant_row_id = getCurrentMerchantId();
			$getInstance = & get_instance();
			$getInstance->load->model('carts/Cart_Setup_model', 'Cart_Setup_model', TRUE);
			$cartBasic = $getInstance->Cart_Setup_model->get_basic_setup($merchant_row_id);
			$product_id_prefix = $cartBasic->product_id_prefix;
			////////////////////////////////////////////////////////////////////////////
			$this->load->library('Application_Utility');
			//here 'PRO'=>prefix for product id will be taken from basic setup table
			$product_id = $this->application_utility->generateId($product_id_prefix,$product_row_id);

            $addToCartText = 'carts/shopping_cart/myShoppingCart/' . $enc_product_row_id;
            $oneClickBuyLink = 'carts/shopping_cart/oneClickProductBuy/' . $enc_product_row_id;

			$this->db->set('product_id', $product_id);
			$this->db->set('addToCartText', $addToCartText);
			$this->db->set('oneClickBuyLink', $oneClickBuyLink);
			$this->db->set('enc_product_row_id', $enc_product_row_id);
			$this->db->set('active_status',1);
			$this->db->set('merchant_row_id',getCurrentMerchantId());
			$this->db->set('created_by',getCurrentMerchantId());
			$this->db->set('created_on', getCurrentDateTimeForDB());
			$this->db->set('modified_by',getCurrentMerchantId());
			$this->db->set('modified_on', getCurrentDateTimeForDB());
            $this->db->where('product_row_id', $product_row_id);
			$this->db->update('tbl_product');
		}
	}
	/*************************END IMPORT TO CSV******************************/

	/**
	 * This Function Updates Product Descriptions in Order Details Table If
	 * Product Description is changed
	 *
	 * @param $product_row_id
	 * @author Himel Nag Rana
	 */
	function updateProductDescriptionInOrderDetails($product_row_id,$product_description)
	{
		$this->db->set('product_description',$product_description);
		$this->db->where('product_row_id',$product_row_id);
		$this->db->update('tbl_order_details');
	}



    function copyProductOptions($productRowId, $newProductRowId)
    {
        $sql = 'SELECT attribute_row_id, sub_sku, quantity, price_change
                       FROM tbl_product_attribute_group WHERE product_row_id = ' . $productRowId;
        $query = $this->db->query($sql);
        $options = $query->result_array();
        foreach($options as $info)
        {
            $info['product_row_id'] = $newProductRowId;
            $this->db->insert('tbl_product_attribute_group', $info);
        }
    }

    function copyProductUpsells($productRowId, $newProductRowId)
    {
        $sql = 'INSERT INTO tbl_product_upsell (product_row_id,upsell_product_row_id,merchant_row_id,priority,discount_type,
                                                discount_amount,appear_after,imageOrText,image_path,design_text,imageOrText2,
                                                design_text2,image_path2,upsell_button,is_second_button,body_background_color,
                                                container_background_color,upsell_active_status, is_bundle)
                SELECT '.$newProductRowId.', upsell_product_row_id, merchant_row_id, priority, discount_type,
                        discount_amount ,appear_after , imageOrText , image_path , design_text , imageOrText2 , design_text2,
                        image_path2 , upsell_button , is_second_button , body_background_color , container_background_color , upsell_active_status, is_bundle
                        FROM tbl_product_upsell
                        WHERE product_row_id = '.$productRowId;

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

    function copyRelatedProduct($productRowId, $newProductRowId)
    {
        $sql = 'INSERT INTO tbl_product_relation (product_row_id , related_product_row_id)

                SELECT '.$newProductRowId.', related_product_row_id
                        FROM tbl_product_relation
                        WHERE product_row_id = '.$productRowId;
        $this->db->query($sql);

    }

    function copyProductDiscount($productRowId, $newProductRowId)
    {
        $sql = 'INSERT INTO tbl_quantity_discount (product_row_id , lower_limit ,upper_limit , discount_amount , discount_type)

                SELECT '.$newProductRowId.', lower_limit , upper_limit , discount_amount , discount_type
                        FROM tbl_quantity_discount
                        WHERE product_row_id = '.$productRowId;
        $this->db->query($sql);

    }

    function updateNewProductImage($newProductRowId, $productImagePath)
    {
        if($productImagePath)
        {
            $imageName = basename($productImagePath);

            $nameParts = explode('.', $imageName);

            $newImageName = md5($newProductRowId) . '.' . $nameParts[(count($nameParts) - 1)];
            $newImagePath = '/images/products/'. $newImageName;
            $baseDir = dirname(FCPATH);

            $sourcePath = $baseDir .'/'. $productImagePath;

            $destinationPath = $baseDir . $newImagePath;

            if(copy($sourcePath, $destinationPath))
            {
                $sql = 'UPDATE tbl_product
                        SET product_image = ' . $this->db->escape($newImagePath) . '
                        WHERE product_row_id = ' . $newProductRowId;
                $this->db->query($sql);
            }
        }
    }

    function updateDigitalProducts($newProductRowId, $digitalProductPath)
    {
        if($digitalProductPath)
        {
          $digitalProductName = basename($digitalProductPath);

          $nameParts = explode('.', $digitalProductName);
          $digitalProductNameWithoutExtention = $this->getFilenameWithoutExt($digitalProductName);
          $existingDigitalProductName = $digitalProductNameWithoutExtention.$this->db->escape.'.'.$nameParts[(count($nameParts) - 1)];
          $newDigitalProductName = $digitalProductNameWithoutExtention.'_'.$newProductRowId.'.'.$nameParts[(count($nameParts) - 1)];
          $newDigitalProductPath = '/images/digitalProducts/'. $newDigitalProductName;
          $baseDir = dirname(FCPATH);

          $sourcePath = $baseDir .'/'. $digitalProductPath;
          $destinationPath = $baseDir . $newDigitalProductPath;

          if(copy($sourcePath, $destinationPath))
            {
                $sql = 'UPDATE tbl_product
                        SET digital_product_path = ' . $this->db->escape($newDigitalProductPath) . ',
                        digital_product_file_name = ' . $this->db->escape($existingDigitalProductName) . '
                        WHERE product_row_id = ' . $newProductRowId;
                $this->db->query($sql);
            }
        }

    }

    function getFilenameWithoutExt($filename)
    {
        if(!strstr($filename, '.'))
        {
            return $filename;
        }
        else
        {
            $parts = explode('.', $filename);

            $totalParts = count($parts);

            //if file name has multiple (.) dots
            if($totalParts > 2)
            {
                $parts = array_pop($parts);
                return implode('.', $parts);
            }
            return $parts[0];
        }
    }

    function upsellDisplayOption($merchantRowId, $productRowId, $upSellDisplay)
    {
        $this->db->where('merchant_row_id', $merchantRowId);
        $this->db->where('product_row_id', $productRowId);
        $this->db->update('tbl_product', array('upsell_display' => $upSellDisplay));
    }

    function saveUpSellDisplaySettings($merchantRowId, $productRowId, $upSellDisplay, $updateUpSellProducts = true)
    {
        $this->db->where('merchant_row_id', $merchantRowId);
        $this->db->where('product_row_id', $productRowId);
        $this->db->update('tbl_product', array('upsell_display' => $upSellDisplay));

        if($updateUpSellProducts)
        {
            $this->db->where('product_row_id', $productRowId);
            $this->db->update('tbl_product_upsell', array('upsell_display' => $upSellDisplay));
        }
    }

    function getProductAutoresponderInfo($product_row_id)
    {
        $this->db->select('*');
        $this->db->from('tbl_product_autoresponder');
        $this->db->where('product_row_id', $product_row_id);
        $result = $this->db->get();
        return $result->row_array();
    }

    function setProductAutoresponderInfo($data)
    {
        $check = $this->getProductAutoresponderInfo($data['product_row_id']);
        if($check)
        {
            $this->db->where('product_row_id', $data['product_row_id']);
            $this->db->update('tbl_product_autoresponder', $data);

        }
        else
        {
            $this->db->insert('tbl_product_autoresponder', $data);
        }
    }

    function getProductByEncryptedProductRowId($encryptedProductRowId, $merchantRowId)
    {
        $sql = "SELECT  p.product_row_id,
                        p.product_description,
                        p.merchant_row_id,
                        p.product_price,
                        srp.recurring_profile_row_id,
                        srp.recurring_cycle,
                        srp.recurring_count,
                        srp.recurring_start_duration,
                        srp.recurring_price,
                        srp.domestic_recurring_shipping_charge,
                        srp.international_recurring_shipping_charge,
                        additional_shipping_amount,
                        srp.add_ship_to_recurring,
                        srp.add_tax_to_recurring,
                        p.add_recurring_to_shipping_module,
                        p.domestic_rate,
                        p.international_rate,
                        p.stateTaxRate,
                        p.countryTaxRate,
                        p.include_commission,
                        p.affiliate_first_level_commission,
                        p.affiliate_second_level_commission,
                        p.autoresponder_id,
                        p.is_digital
                FROM tbl_product AS p
                LEFT JOIN tbl_selectable_recurring_profile AS srp ON((srp.product_row_id = p.product_row_id) AND (srp.profile_type = 'A'))
                WHERE p.enc_product_row_id = '$encryptedProductRowId'
                AND p.merchant_row_id = $merchantRowId
                AND p.active_status = 1
                LIMIT 1";

        $query = $this->db->query($sql);
        return $query->row();
    }

    function getRecurringProfileId($productRowId, $orderRowId)
    {
        $sql = "SELECT recurring_profile_row_id 
                       FROM tbl_selectable_recurring_profile AS srp 
                       JOIN tbl_recurring_orders AS ro ON(ro.profile_type = srp.profile_type)
                       WHERE srp.product_row_id = {$productRowId} AND ro.order_row_id = {$orderRowId}";
        return $this->db->query($sql)->row_array();                                        
    }
    
    /*show all the details info of the given product_row_ids*/
    function getProductByIds ($product_row_ids)
    {
        $sql = 'SELECT product_row_id,
                       product_description
                       FROM tbl_product WHERE product_row_id IN(' . implode(',', $product_row_ids) . ')';
        $query = $this->db->query($sql);
        return $query->result_array();
    }

    function getAllProductsForAPI($merchantRowId)
    {
        if($merchantRowId)
        {
            $sql = 'SELECT p.product_row_id,
                            IFNULL(p.category_id, 0) AS category_id,
                            p.merchant_row_id,
                            p.product_description,
                            p.product_price,
                            p.product_price_private,
                            p.product_price_wholeseller,
                            p.product_price_distributor,
                            p.product_code,
                            p.enc_product_row_id,
                            p.product_image,
                            p.clear_cart,
                            p.created_on,
                            p.modified_on,
                            cb.currency
                      FROM tbl_product p
                      INNER JOIN tbl_cart_basic cb ON cb.merchant_row_id = p.merchant_row_id
                      WHERE p.merchant_row_id = ' . $this->db->escape($merchantRowId) . '
					  AND p.is_editable = 1';
            $query = $this->db->query($sql);
            return $query->result();
        }

        return false;
    }

    function isDuplicateProductDescription($productDescription, $merchantRowId, $productRowId = 0)
    {
        $sql = 'SELECT product_row_id
                FROM tbl_product
                WHERE merchant_row_id = ' . $merchantRowId . '
				AND is_editable = 1 AND active_status = 1 ';

        if($productRowId)
        {
            $sql .= ' AND product_row_id != ' . $productRowId;
        }

        $sql .= ' AND product_description = ' . $this->db->escape($productDescription);

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

        if($query)
        {
            $row = $query->row();

            if($row && $row->product_row_id)
            {
                return true;
            }
        }

        return false;
    }

    function saveProductForAPI($productInfo, $merchantRowId)
    {
        $productInfo['upsell_display'] = 1;
        $productInfo['active_status'] = 1;
		$productInfo['is_editable'] = 1;
        $productInfo['created_by'] = $merchantRowId;
        $productInfo['created_on'] = getCurrentDateTimeForDB();
        $productInfo['modified_by'] = $merchantRowId;
        $productInfo['modified_on'] = $productInfo['created_on'];
        $productInfo['add_to_shipping_module'] = 0;
        $productInfo['add_recurring_to_shipping_module'] = 0;
        //$productInfo['add_ship_to_recurring'] = 0;
        $productInfo['button_code'] = '11';
        $productInfo['buy_now_button_code'] = '13';
        $this->db->insert('tbl_product', $productInfo);

        $productRowId = $this->db->insert_id();

        if($productRowId)
        {
            $encryptedProductRowId = md5($productRowId);

            $productInfo = array();
            $productInfo['enc_product_row_id'] = $encryptedProductRowId;
            $productInfo['addToCartText'] = 'carts/shopping_cart/myShoppingCart/' . $encryptedProductRowId;
            $productInfo['oneClickBuyLink'] = 'carts/shopping_cart/oneClickProductBuy/' . $encryptedProductRowId;

            $instance = & get_instance();
            $instance->load->model('carts/Cart_Setup_model', 'Cart_Setup_model', TRUE);
            $cartBasic = $instance->Cart_Setup_model->get_basic_setup($merchantRowId);

            $this->load->library('Application_Utility');
            $productInfo['product_id'] = $this->application_utility->generateId($cartBasic->product_id_prefix,$productRowId);

            $this->db->where('product_row_id', $productRowId);
            $this->db->update('tbl_product', $productInfo);

            $instance->load->model('api/notification_model', 'Notification_model', true);

            if($instance->config->item('enable_member_pro_notification'))
            {
                if($instance->Notification_model->sendNotificationToMembersProOnProductChange($productRowId, $instance->config->item('entity_created')))
                {
                    $pData['thank_you_url'] = 'http://pwcmembers.com/thanks/order';

                    $this->db->where('product_row_id', $productRowId);
                    $this->db->update('tbl_product', $pData);
                }
            }

            if($instance->config->item('enable_project_notification'))
            {
                $instance->Notification_model->sendNotificationToProjectOnProductChange($productRowId, $instance->config->item('entity_created'));
            }

            return $productRowId;
        }

        return 0;
    }

    function deleteEasyUpsell($product_row_id, $is_bundle = 0)
    {
        if(!$is_bundle)
        {
            $sql = "DELETE FROM tbl_product_upsell WHERE product_row_id = {$product_row_id}";
            $this->db->query($sql);
        }

        $sql = "DELETE FROM tbl_product_upsell WHERE upsell_product_row_id = {$product_row_id} AND is_bundle = {$is_bundle}";
        $this->db->query($sql);
    }

    function saveOptionalImage($data)
    {
       return $this->db->insert('tbl_product_image', $data);
    }

    function get_product_price($merchant_row_id=0, $product_row_id=0)
    {
        $sql="Select product_price FROM tbl_product WHERE merchant_row_id=".$this->db->escape($merchant_row_id)."
              AND product_row_id=".$this->db->escape($product_row_id);
        $query=$this->db->query($sql);
        $result=$query->row();
        return $result->product_price;

    }

    function saveDefaultProduct($merchantRowId)
    {
        if($merchantRowId)
        {
            $currDate = getCurrentDateTimeForDB();
            $data = array('merchant_row_id' => $merchantRowId,
                            'product_description' => 'Free Form Charge',
                            'product_price' => 0.00,
                            'is_editable' => '0');

            $productRowId = $this->saveProduct($data, 0);

            if($productRowId)
            {
                $data = array('free_charge_product_row_id' => $productRowId);
                $this->db->where('merchant_row_id', $merchantRowId);
                $this->db->update('tbl_merchant', $data);
                return $productRowId;
            }
        }

        return 0;
    }

    function removeFunnelAssociation($merchantRowId, $funnelId)
    {
        if($merchantRowId && $funnelId)
        {
            $sql = 'UPDATE tbl_product
                    SET funnels = REPLACE(funnels, \'|' . $funnelId . '|\', \'|\')
                    WHERE merchant_row_id = ' . $merchantRowId;

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

    function removeSalesPathAssociation($merchantRowId, $funnelId, $salesPathId)
    {
        if($merchantRowId && $funnelId && $salesPathId)
        {
            $sql = 'UPDATE tbl_product
                    SET sales_paths = REPLACE(sales_paths, \'|' . $funnelId . '#' . $salesPathId . '|\', \'|\')
                    WHERE merchant_row_id = ' . $merchantRowId;

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

    function removeTagAssociation($merchantRowId, $tagId)
    {
        if($merchantRowId && $tagId)
        {
            $sql = 'UPDATE tbl_product
                    SET tags = REPLACE(tags, \'|' . $tagId . '|\', \'|\')
                    WHERE merchant_row_id = ' . $merchantRowId;

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

    function getReorderItem($merchantRowId)
    {
        $sql = 'SELECT product_row_id,
                       product_description,
                       product_id,
                       product_code,
                       current_inventory,
                       reorder_level,
                       product_price FROM tbl_product
                       WHERE use_inventory = 1 AND
                       current_inventory < reorder_level AND
                       current_inventory != -9999 AND
                       reorder_level != -9999 AND
                       merchant_row_id =' . $merchantRowId . '

                        UNION

                SELECT p.product_row_id,
                       p.product_description,
                       p.product_id,
                       p.product_code,
                       pa.quantity,
                       pa.reorder_quantity,
                       p.product_price FROM tbl_product AS p JOIN
                       tbl_product_attribute_group AS pa ON (pa.product_row_id = p.product_row_id)
                       WHERE pa.quantity < pa.reorder_quantity AND
                       p.use_inventory = 2 AND pa.quantity != -9999 AND pa.reorder_quantity != -9999 AND
                       p.merchant_row_id =' . $merchantRowId;

        $query = $this->db->query($sql);
        $result = $query->result_array();
        foreach($result as $item)
        {
            $products[$item['product_row_id']] = $item;
        }
        
        return isset($products) ? $products : false;
    }

    function getTermsAndConditionsByProductRowId($productRowId){
        if($productRowId){
            $this->db->where('product_row_id', $productRowId);
            $query = $this->db->get('tbl_product_terms_service');
            $row = $query->row();

            if(!empty($row->terms_conditions)){
                return html_entity_decode($row->terms_conditions, ENT_QUOTES);
            }
            return '';
        }
    }

    function getTermsAndConditionsOfMultipleProducts($productRowIds){
        if($productRowIds){
            $sql = 'SELECT *
                    FROM tbl_product_terms_service
                    WHERE product_row_id IN (' . implode(',', $productRowIds) . ')';

            $query = $this->db->query($sql);
            return $query->result();
        }
    }

    function saveTermsAndConditions($productRowId, $terms){
        if(!empty($productRowId)){
            $this->db->select('product_row_id');
            $this->db->where('product_row_id', $productRowId);
            $query = $this->db->get('tbl_product_terms_service');
            $row = $query->row();

            $terms = htmlentities($terms, ENT_QUOTES);

            if(!empty($row)){
                $this->db->where('product_row_id', $productRowId);
                $this->db->update('tbl_product_terms_service', array('terms_conditions' => $terms));
            }
            else if(!empty($terms)){
                $this->db->insert('tbl_product_terms_service', array('product_row_id' => $productRowId, 'terms_conditions' => $terms));
            }
        }
    }

   function updatequantityForLink($product_row_id,$quantity){
       $data = array(
               'quantity_for_link' => $quantity,
                );

      $this->db->update('tbl_product',$data,array('product_row_id'=>$product_row_id));
   }

   /*
   *  Added by: Anjuman | Date:07032012
   *  it will select default button code from tbl_cart_basic table.
   *  if no data found then it will return 6 as default button code
   */

   function getDefaultButtonCode($merchant_row_id)
   {
      $query = $this->db->query("SELECT default_button,buy_now_button_code FROM tbl_cart_basic WHERE merchant_row_id =".$merchant_row_id." LIMIT 1");
      return  $query->row();
   }

   ///will set default button code into tbl_cart_basic table
   //Added: 03092012
   function setDefaultButtonCode($merchant_row_id, $button_code, $buy_now_button_code)
    {
        $this->db->set('default_button',$button_code);
        $this->db->set('buy_now_button_code',$buy_now_button_code);
        $this->db->where('merchant_row_id',$merchant_row_id);
        $this->db->update('tbl_cart_basic');
    }

    ///will return default button code for edit product page from tbl_cart_basic table
   //Added: 03092012
   function checkDefaultButtonCode($merchant_row_id)
    {
         $query = $this->db->query("SELECT default_button,buy_now_button_code FROM tbl_cart_basic WHERE merchant_row_id =".$merchant_row_id." LIMIT 1");

        if ($query->num_rows() > 0)
        {
           $row = $query->row();

           $default_button =  $row->default_button;
           $buy_now_button =  $row->buy_now_button_code;

           if($default_button!=0 && $buy_now_button!=0)
           {
                    $default_button_code =  $default_button."|".$buy_now_button;
                    return $default_button_code;
           }
           else
           {
                    return 0;
           }

        }
    }

    //@Anjuman | D: 05032012 (mmddyyyy) -- get quantity discount type for cart
    function getQuantityDiscountTypeByProduct($product_row_id)
    {
        $this->db->select('*');
        $this->db->from('tbl_quantity_discount');
        $this->db->where('product_row_id',$product_row_id);
        $this->db->order_by("quantity_discount_id",'asc');
        $res = $this->db->get();
        $result = $res->result_array();
        return $result;
    }


    //06152012
    function checkDiscountByProductDiscountType($product_row_id, $product_quantity, $discount_type)
    {
        $product_row_id = (int) $product_row_id;
        $product_quantity = (int) $product_quantity;

        if($product_row_id && $product_quantity)
        {
            $resultSet = $this->db->query("SELECT * FROM tbl_quantity_discount WHERE product_row_id = '" . $product_row_id . "' AND " . $product_quantity . " >= lower_limit AND ((" . $product_quantity . " <= upper_limit) || (upper_limit=0)) AND discount_type='".$discount_type."'");
            return true;
        }

        return false;
    }

    function downloadlimit($product_row_id,$merchant_row_id)
    {
            $sql = 'SELECT isallowforcp ,
                downloadLimit,recurringallowforcp
                FROM tbl_product
                WHERE merchant_row_id = ' . $merchant_row_id . '
                AND product_row_id = '.$product_row_id ;
        $resultSet = $this->db->query($sql);

        return $resultSet->row();
    }



    function getProductFromID($product_row_id)
    {

            $this->db->select('*');
            $this->db->from('tbl_product');
            $this->db->where('product_row_id',$product_row_id);
            $this->db->where('active_status', 1);
            $res = $this->db->get();
            return $res->row();

    }

    function copyProductLookandFeel($productRowId, $newProductRowId)
    {
       $sql = 'INSERT INTO tbl_product_look_and_feel (product_row_id , background_color ,font_color, font_family , company_logo_width, company_logo_height, background_image, tophtml, bottomhtml, shortDesc, longDesc)
                SELECT '.$newProductRowId.', background_color , font_color , font_family , company_logo_width, company_logo_height, background_image, tophtml, bottomhtml, shortDesc, longDesc
                        FROM tbl_product_look_and_feel
                        WHERE product_row_id = '.$productRowId;

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

    function getScheduleProduct($merchantRowId, $fields = '*')
    {
      $sql = "SELECT {$fields} FROM tbl_product AS p JOIN
                                     tbl_category AS c ON(p.category_id = c.category_id)
                                     WHERE c.category_description = 'Scheduling System' AND
                                     c.is_editable = 0 AND
                                     p.merchant_row_id = {$merchantRowId}";
       $query = $this->db->query($sql);
       return $query->row_array();
    }

    function getScheduleProductId($merchantRowId)
    {
        if($merchantRowId) 
        {
           $sql = "SELECT product_row_id FROM tbl_product AS p JOIN
                                     tbl_category AS c ON(p.category_id = c.category_id)
                                     WHERE c.category_description = 'Scheduling System' AND
                                     c.is_editable = 0 AND                   
                                     p.merchant_row_id = {$merchantRowId}";
            $query = $this->db->query($sql);
            $result = $query->result_array();
            foreach($result as $product)
            {
               $productIds[] = $product['product_row_id']; 
            }
            return $productIds;
        }
    }
    
    function deleteRecurringProfile($productRowId)
    {
        if($productRowId)
          $this->db->query("DELETE FROM tbl_selectable_recurring_profile WHERE product_row_id = " . $productRowId);
    }
    
    function getRecurringProfileByProductId($productRowId, $fields = '*')
    {
        if(!$productRowId)
          return false;
        $select = is_array($fields) ? implode(',', $fields) : '*';
        $where = is_array($productRowId) ? " product_row_id IN(" . implode(',', $productRowId) . ") " : " product_row_id = " . $productRowId;
        $query = $this->db->query("SELECT {$select} FROM tbl_selectable_recurring_profile WHERE {$where} ORDER BY recurring_profile_row_id ASC");
        return $query->result_array();
    }
    function getRecurringProfileById($recurringProfileRowId, $fields = null)
    {
        if(!$recurringProfileRowId)
          return false;
        $fields = is_array($fields) ? implode(', ', $fields) :  'srp.*, p.recurring_logic, p.stateTaxRate, p.countryTaxRate, p.is_tax_applicable, p.product_description'; 
        
        $query = $this->db->query("SELECT {$fields} FROM tbl_selectable_recurring_profile AS srp JOIN tbl_product AS p ON(p.product_row_id = srp.product_row_id) WHERE srp.recurring_profile_row_id = " . $recurringProfileRowId);
        return $query->row_array();
    }
    function getProductWithDefaultRecurring($productRowId, $fields = '*')
    {
        $fields = is_array($fields) ? implode(', ', $fields) :  $fields; 
        $sql = "SELECT {$fields}, p.product_row_id
                FROM tbl_product AS p
                LEFT JOIN tbl_selectable_recurring_profile AS srp ON((p.product_row_id = srp.product_row_id ) AND (srp.profile_type = 'A'))
                WHERE p.product_row_id = {$productRowId}
                AND p.active_status = 1";

        $query = $this->db->query($sql);
        return $query->row();
    }
    
    function updateDefaultRecurringProfile($data)
    {
        if($data['product_row_id'])
        {
            $this->db->where('product_row_id', $data['product_row_id']);
            $this->db->where('profile_type', 'A');
            $this->db->update('tbl_selectable_recurring_profile', $data);    
        }
    }                 
    
    function saveProductOptionalSetting($data) 
    {
        $sql = "SELECT product_row_id FROM tbl_product_optional_setting WHERE product_row_id = " . $data['product_row_id'];
        $query = $this->db->query($sql);
        if($query->num_rows() > 0)
        {
            $this->db->where('product_row_id', $data['product_row_id']);
            $this->db->update('tbl_product_optional_setting', $data);
            
        } else
        {
            $this->db->insert('tbl_product_optional_setting', $data);            
        }
    }
    
    function getProductOptionalSetting($productRowId) 
    {
        if($productRowId)
        {
            $sql = "SELECT * FROM tbl_product_optional_setting WHERE product_row_id = " . $productRowId;
            $query = $this->db->query($sql);
            if($query->num_rows()> 0)
            return $query->row();
            else
            return false;            
        }  
        
    }
    
}
?>