<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of cls_products
 *
 * @author Asha S
 */
class Products{
    //put your code here
     // Check product already added to favourites list
    public static function getProductBasicInfo($productID,$productOptionID)
    {
            global $tableprefix;
            
            $favourites_count   =   0;
            
            $objResult                           = new stdClass();

            if(!empty($productOptionID))
                {
                    $QueryCartItems="SELECT p.vdigital_product,c.gift_certificate_flag,p.product_artist_id as seller_id
                                        FROM ".$tableprefix."products p
                                        INNER JOIN ".$tableprefix."product_options po ON po.product_id = p.product_id
                                        INNER JOIN ".$tableprefix."categories  c ON c.category_id = p.product_category
                                        WHERE p.product_id ='".$productID."'
                                        AND po.product_option_id= '".$productOptionID."' ";
                    $resSet = DbHelper::execute($QueryCartItems);
                    $data = DbHelper::fetchRow($resSet);
                     if($data) {  
                        $objResult->status                  =    SUCCESS;
                        $objResult->data                    =     $data; 
                     }else{
                         $objResult->status                 =    ERROR;
                     }
                    
                }
                
                return $objResult;
            
    }
    
    
    // Function to check whether the product is free shipped 
           
     public static function is_free_ship_product($productID)
    {
                 $objResult                      = new stdClass();
                 $tableName                      = DbHelper::setTablePrefix('products');
                 
                 if(!empty($productID)) {
                 $selectCase                     = "product_id =".generalMethods::escapeString($productID);
                 $objResultRow                   = DbHelper::selectSingleValue($tableName,'allow_free_shipping',$selectCase);
         // If user exists then assign user data to result object
                 }
                 if($objResultRow) {
                      $objResult->status         =    SUCCESS;
                      $objResult->data           =   $objResultRow; // Assign user record  to object
                      
                 }
                 else{
                     $objResult->status          =  ERROR;
                  }

         return $objResult;

    }
    
    
     public static function check_free_ship_product_text($productID)
    {    
                $freeShipText       =   '';
                 if(!empty($productID)) {
                        $freeshipObj    =   self::is_free_ship_product($productID);
                        if($freeshipObj->status==SUCCESS)
                        {
                            $FreeshipFlag  = $freeshipObj->data; 
                            
                            if($FreeshipFlag=='Y')
                            {
                                $freeShipText   =  "( ". FREE_SHIP_TEXT ." )";
                            }
                        }
         // If user exists then assign user data to result object
                 }
                 
         return $freeShipText;

    }


    // Function to check whether the product is subscription
     public static function get_product_subscription_deatils($productID)
    {
                 $objResult                      = new stdClass();
                 $tableName                      = DbHelper::setTablePrefix('products');
                 if(!empty($productID)) {
                 $selectCase                     = "product_id =".generalMethods::escapeString($productID);
                 $objResultRow                   = DbHelper::selectRecord($tableName,'allow_subscription,subscription_mode,product_artist_id',$selectCase);
         // If user exists then assign user data to result object
                 }
                 if($objResultRow) {
                      $objResult->status         =    SUCCESS;
                      $objResult->data           =   $objResultRow; // Assign user record  to object

                 }
                 else{
                     $objResult->status          =  ERROR;
                  }

         return $objResult;

    }
    
    
    // Function get flat rate value 
    
       public static function getFlatrateValue($sellerid)
    {
    	$objResult                      =   new stdClass();
    	$tableName                      =   DbHelper::setTablePrefix('flat_rate_seller');
    	$queryFlatRate                  =   "SELECT * FROM ".$tableName." WHERE seller_id ='".$sellerid." order by first_Item_charge ASC LIMIT 1'";
        $objResultRow                   =   DbHelper::customRecord($queryFlatRate);
    	//Check Cart Details exist for the selected  Cart ID
    	if($objResultRow)
    		// Assign Cart details to object
        {
                $objResult->status        =    SUCCESS;
    		$objResult->data          =    $objResultRow;
        }
    	else {
                $tableName                      =   DbHelper::setTablePrefix('flat_rate_default');
    		$queryFlatRate                  =   "SELECT * FROM ".$tableName." order by first_Item_charge ASC LIMIT 1";
                $objResultRow                   =   DbHelper::customRecord($queryFlatRate);
                
                if($objResultRow)
                       // Assign Cart details to object
               {
                       $objResult->status        =    SUCCESS;
                       $objResult->data          =    $objResultRow;
               }
    		
    	}
    	return $objResult;

    }


    // function to check type  is a subscription product


      // Function to check whether the product is subscription
     public static function is_subscription_product($productID)
    {
          /*       $objResult                      = new stdClass();
                 $tableName                      = DbHelper::setTablePrefix('products');

                 $enable_subscription           =   getSettingsValue('enable_subscription_products');

                 $subscriptionFlag = false;

              if($enable_subscription=='Y') {
                         if(!empty($productID)) {
                         $selectCase                     = "product_id =".generalMethods::escapeString($productID);
                         $objResultRow                   = DbHelper::selectSingleValue($tableName,'allow_subscription',$selectCase);
                 // If user exists then assign user data to result object
                         if($objResultRow) {

                                      $objResult->data           =   $objResultRow; // Assign user record  to object

                                      if($objResult->data=='Y')
                                              {

                                              $subscriptionFlag  = true;
                                      }else{

                                            $subscriptionFlag = false;
                                      }
                         }


                         }
              } */
                
           $subscriptionFlag = false;   
              
          return $subscriptionFlag;

    }



    // Get product count  per seller / store


    // Function to check whether the product is subscription
     public static function get_product_count_per_seller($sellerID)
    {
                 $objResult                      = new stdClass();
                 $tableName                      = DbHelper::setTablePrefix('products');
                 if(!empty($sellerID)) {
                 $selectCase                     = "product_artist_id =".generalMethods::escapeString($sellerID);
                 $objResultRow                   = DbHelper::selectRecord($tableName,'COUNT(product_id) as productCount ',$selectCase);
         // If user exists then assign user data to result object
                 }
                 if($objResultRow) {
                      $objResult->status         =    SUCCESS;
                      $objResult->data           =   $objResultRow; // Assign user record  to object

                 }
                 else{
                     $objResult->status          =  ERROR;
                  }

         return $objResult;

    }

    // Function to chek the count of seller products added to favorites
     public static function get_favourites_count_per_seller($sellerID)
    {
                 $objResult                      = new stdClass();
                 $tableName                      = DbHelper::setTablePrefix('favourites');
                 if(!empty($sellerID)) {
                 $selectCase                     = "seller_id =".generalMethods::escapeString($sellerID);
                 $objResultRow                   = DbHelper::selectRecord($tableName,'COUNT(nId) as favritesCount ',$selectCase);
         // If user exists then assign user data to result object
                 }
                 if($objResultRow) {
                      $objResult->status         =    SUCCESS;
                      $objResult->data           =   $objResultRow; // Assign user record  to object

                 }
                 else{
                     $objResult->status          =  ERROR;
                  }

         return $objResult;

    }


    // Update seller vacation mode in product table

      public static function updateProduct_Vacation_Mode($productObjVo)
    {
            $objResult                           = new stdClass();
            $tableName                           = DbHelper::setTablePrefix('products');
            $where                               = "product_artist_id = ".generalMethods::escapeString($productObjVo->seller_id). " ";
            $sellerId                              = DbHelper::update($tableName,array('seller_vacation_mode' => generalMethods::escapeString($productObjVo->seller_vacation_mode)),$where);
         if($sellerId) {
            $objResult->status                  =    SUCCESS;
            $objResult->data                    =    $sellerId;
         }else{
             $objResult->status                 =    ERROR;
         }

         return $objResult;
    }


    // Update Favourites Vacation Mode

          public static function updateFavourites_Vacation_Mode($productObjVo)
    {
            $objResult                           = new stdClass();
            $tableName                           =  DbHelper::setTablePrefix('favourites');
            $where                               =  "seller_id = ".generalMethods::escapeString($productObjVo->seller_id). " ";
            $favouriteId                            = DbHelper::update($tableName,array('status' => generalMethods::escapeString($productObjVo->status)),$where);
         if($favouriteId) {
            $objResult->status                  =    SUCCESS;
            $objResult->data                    =    $favouriteId;
         }else{
             $objResult->status                 =    ERROR;
         }

         return $objResult;
    }
    
// Update Favourites Vacation Mode for seller

          public static function updateFavourites_Vacation_Mode_Seller($productObjVo)
    {
            $objResult                           = new stdClass();
            $tableName                           =  DbHelper::setTablePrefix('seller_favourites');
            $where                               =  "nSellerId = ".generalMethods::escapeString($productObjVo->seller_id). " ";
           
            $favouriteId                            = DbHelper::update($tableName,array('status' => generalMethods::escapeString($productObjVo->status)),$where);
         if($favouriteId) {
            $objResult->status                  =    SUCCESS;
            $objResult->data                    =    $favouriteId;
         }else{
             $objResult->status                 =    ERROR;
         }

         return $objResult;
    }    
    
    
          public static function manageSellerVacationSettingsMode($sellerbjVo)
    {
            $objResult                           = new stdClass();
            $sellerid                            =  $sellerbjVo->seller_id;
            $samesellerItemCount                 =   0;
            $sellerFavItemCount                  =   0;

            //Remove seller cart items if vacation mode enabeled
          if($sellerbjVo->seller_vacation_mode=='Y')  {
             
                $sellerCartItemCount    =   self::getSellerCartItems($sellerid); 
                if($sellerCartItemCount>0) {
                    $cartItems              =    self::remove_seller_cart_items($sellerid);
                }
         }
            //Get seller prodcts and set vacation mode
             $sellerCartObj      =    self::get_product_count_per_seller($sellerid);
                    //echopre($sellerCartObj);
        if($sellerCartObj->status==SUCCESS)
                {
                     $sellerCartObjVal      =   $sellerCartObj->data;
                     $samesellerItemCount   =   $sellerCartObjVal->productCount;
                     if($samesellerItemCount>0)
                         {
                                $productObjVo   =   new stdClass();

                                $productObjVo->seller_vacation_mode     =   $sellerbjVo->seller_vacation_mode;
                                $productObjVo->seller_id                =   $sellerid;
                                $sellerupdateid       =    self::updateProduct_Vacation_Mode($productObjVo);

                         }
                }
          //get Seller favourite items added and update status
             $favouritesCartObj      =    self::get_favourites_count_per_seller($sellerid);
             if($favouritesCartObj->status==SUCCESS)
            {
                 $favouritesCartObjVal      =   $favouritesCartObj->data;
                 $sellerFavItemCount        =   $favouritesCartObjVal->favritesCount;
                 if($sellerFavItemCount>0)
                     {
                            $favouriteObjVo   =   new stdClass();
                            if($sellerbjVo->seller_vacation_mode=='Y')
                                {
                                    $favouriteStatus    =    'N';   // Inactive
                            }else{
                                    $favouriteStatus    =    'Y';  // Active
                            }
                        $favouriteObjVo->status     =   $favouriteStatus;
                        $favouriteObjVo->seller_id  =    $sellerid;
                        $favcount                   =    self::updateFavourites_Vacation_Mode($favouriteObjVo);

                    }

            }
           //Update seller favorites
            $favouritesSellerCount      =    self::getFavoritesellerCount($sellerid);             
            if($favouritesSellerCount>0)
                {
                       $favouriteObjVo   =   new stdClass();
                       if($sellerbjVo->seller_vacation_mode=='Y')
                           {
                               $favouriteStatus    =    'N';   // Inactive
                       }else{
                               $favouriteStatus    =    'Y';  // Active
                       }
                   $favouriteObjVo->status     =   $favouriteStatus;
                   $favouriteObjVo->seller_id  =    $sellerid;
                   $favcount                   =    self::updateFavourites_Vacation_Mode_Seller($favouriteObjVo);

               }

                     
           
        
    }

    public static function getFavoritesellerCount($sellerId){
        
                $objResult                      = new stdClass();
                 $tableName                      = DbHelper::setTablePrefix('seller_favourites');

                 if(!empty($sellerId)) {
                     $selectCase                     = " nSellerId = ".generalMethods::escapeString($sellerId);
                     $objResultRow                   =  DbHelper::selectSingleValue($tableName,'COUNT(nId) as seller_fav_count',$selectCase);
                     // If user exists then assign user data to result object
                     return $objResultRow;
                } 
        
    }

    // Remove Seller items from Cart if vacatioon mode is enabled

    public static function remove_seller_cart_items($sellerId)
    {
            //clear the seller product from carts table
            $where                          = "seller_id = ".$sellerId;
            $tableName                      = DbHelper::setTablePrefix('cart');
            $objResultRow                   = DbHelper::delete($tableName,$where);
            //
    }


    // Get default option id of the product

     public static function getDefaultProductOptionId($productid)
    {
                 $objResult                      = new stdClass();
                 $tableName                      = DbHelper::setTablePrefix('product_options');

                 if(!empty($productid)) {
                     $selectCase                     = " product_id =".generalMethods::escapeString($productid)." AND default_option ='YES' ";
                     $objResultRow                   =  DbHelper::selectSingleValue($tableName,'product_option_id',$selectCase);
                     // If user exists then assign user data to result object
                     return $objResultRow;
                }
                 
        

    }



     public static function getProductOptionStock($product_option_id)
    {
                 $objResult                      = new stdClass();
                 $tableName                      = DbHelper::setTablePrefix('product_options');

                 if(!empty($product_option_id)) {
                     $selectCase                     = " product_option_id =".generalMethods::escapeString($product_option_id)."  ";
                     $objResultRow                   =  DbHelper::selectSingleValue($tableName,'product_stock',$selectCase);
                     // If user exists then assign user data to result object
                     return $objResultRow;
                }

    }



    // function to get product seller id

     public static function getProductSellerId($productid)
    {
                 $objResult                         = new stdClass();
                 $tableName                         = DbHelper::setTablePrefix('products');
                 $productid                         =   strip_tags(trim($productid));

                 if(!empty($productid)) {
                     $selectCase                     = " product_id =".generalMethods::escapeString($productid)." ";
                     $objResultRow                   =  DbHelper::selectSingleValue($tableName,'product_artist_id',$selectCase);
                     // If user exists then assign user data to result object
                     return $objResultRow;
                }

    }


    //*********************** Multicart Scalabity Modifications Statrt ****************************************
    //
    //Function to get Product Defalut option Valuses


     public static function getProduct_Default_Option_Values($productid)
    {
                 $objResult                         = new stdClass();
                 $tableName                         = DbHelper::setTablePrefix('product_options');
                 $productid                         =   strip_tags(trim($productid));
                 if(!empty($productid)) {
                     $selectCase                     = " product_id =".generalMethods::escapeString($productid)." AND default_option='YES' ";
                     $objResultRow                   =  DbHelper::selectRecord($tableName,'*',$selectCase);
                     // If user exists then assign user data to result object
                     return $objResultRow;
                }

    }
    
    //Update Product default option image

     public static function updateProduct_Default_Option_Image($productObjVo)
    {
            $objResult                           =  new stdClass();
            $tableName                           =  DbHelper::setTablePrefix('products');
            $productStock                        =  self::getAllProductStock($productObjVo->product_id);
            $sellerName                          =  self::getProduct_Seller_UserName($productObjVo->product_id);            
            $where                               = "product_id = ".generalMethods::escapeString($productObjVo->product_id)." ";
            $productId                           = DbHelper::update($tableName,array('default_image_small' => generalMethods::escapeString($productObjVo->default_image_small)),$where);
                                                          
         if($productId) {
            $objResult->status                  =    SUCCESS;
            $objResult->data                    =    $productId;
         }else{
             $objResult->status                 =    ERROR;
         }

         return $objResult;
    }


    //Update Product default option Values

     public static function updateProduct_Default_Option_Value($productObjVo)
    {
            $objResult                           =  new stdClass();
            $tableName                           =  DbHelper::setTablePrefix('products');
            $productStock                        =  self::getAllProductStock($productObjVo->product_id);
            $sellerName                          =  self::getProduct_Seller_UserName($productObjVo->product_id);
            
            $where                               = "product_id = ".generalMethods::escapeString($productObjVo->product_id)." ";
            $productId                              = DbHelper::update($tableName,array('default_image_small' => generalMethods::escapeString($productObjVo->default_image_small),
                                                            'default_stock' => generalMethods::escapeString($productStock),
                                                            'default_additional_price' => generalMethods::escapeString($productObjVo->default_additional_price),
                                                            'default_shipping_weight' => generalMethods::escapeString($productObjVo->default_shipping_weight),
                                                            'default_discount' => generalMethods::escapeString($productObjVo->default_discount),
                                                            'default_discount_description' => generalMethods::escapeString($productObjVo->default_discount_description),
                                                            'seller_name' => generalMethods::escapeString($sellerName),
                                                            'default_option_id' => generalMethods::escapeString($productObjVo->default_option_id)),$where);
         if($productId) {
            $objResult->status                  =    SUCCESS;
            $objResult->data                    =    $productId;
         }else{
             $objResult->status                 =    ERROR;
         }

         return $objResult;
    }


    // Get Sum of product stock 
     public static function getAllProductStock($productid)
    {
                 $objResult                         = new stdClass();
                 $tableName                         = DbHelper::setTablePrefix('product_options');
                 $productid                         =   strip_tags(trim($productid));
                 if(!empty($productid)) {
                     $selectCase                     = " product_id =".generalMethods::escapeString($productid)." ";
                     $objResultRow                   =  DbHelper::selectSingleValue($tableName,'sum(product_stock) as productStock',$selectCase);
                     // If user exists then assign user data to result object
                     return $objResultRow;
                }

    }


    // Get product seller user name
     public static function getProduct_Seller_UserName($productid)
    {
             global $tableprefix;
                 $productid                         =   strip_tags(trim($productid));
                 if(!empty($productid)) {
                    $QueryCartItems="SELECT s.artist_name
                                        FROM ".$tableprefix."products p
                                        INNER JOIN ".$tableprefix."artists s ON s.artist_id = p.product_artist_id
                                        WHERE p.product_id ='".$productid."' ";
                    $resSet = DbHelper::execute($QueryCartItems);
                    $data   = DbHelper::fetchOne($resSet);
                    return $data;
                }

    }


    // Function to return the default product listing query

    public static function getProduct_list_Query($featured='',$order='')
    {
       global $tableprefix;
       
       $productListingFee       = getSettingsValue('product_listing_fee');
       $whereCondition =   '';
        $sql    =   "SELECT p.*, (p.product_price + p.default_additional_price) AS product_price FROM " . $tableprefix . "products p INNER JOIN " . $tableprefix . "artists a ON a.artist_id = p.product_artist_id where p.vapproved = 'Y' ";
        $whereCondition.=" AND p.deleted='N' AND a.plan_expired = 'N' ";  // check deleted status
        $whereCondition.=" AND p.publish_status='Y' ";  // check published status
        $whereCondition.=" AND p.seller_vacation_mode='N' ";  // check seller vacation ode settings
        $whereCondition.=" AND p.vSellerStatus='Y' ";  // check seller approved or plan expiry status
        if ($productListingFee > 0) {
                $whereCondition .= " AND p.payment_status='COMPLETED' ";  // Check seller payment completed
            }
        if($featured =='Y')
            {
                $whereCondition .= " AND p.featured='Y' ";
            }

            if(!empty($order))
                {
                    $orderBy     =  " ORDER BY p." .$order;
                }

        $sql    =   $sql .$whereCondition . $orderBy;

         return $sql;

    }


    //Update  prodct table stock

       public static function updateProduct_Stock($productid , $quantity,$prefix=NULL)
    {
           global $tableprefix;
           $tableprefix = $tableprefix!=''?$tableprefix:$prefix;
           $sqlUpdate       =   "UPDATE  " . $tableprefix . "products SET default_stock = default_stock - $quantity where product_id='".$productid."' ";           
           $resSet          =   DbHelper::execute($sqlUpdate);
           $data            =   DbHelper::fetchOne($resSet);
         return $objResult;
    }


    //Update product seller status in product tbale if seller deactivated / deleted / planexpired

     public static function updateProductSellerStatus($productObjVo)
    {
            $objResult                           =  new stdClass();
            $tableName                           =  DbHelper::setTablePrefix('products');
            $where                               = "product_artist_id = ".generalMethods::escapeString($productObjVo->seller_id)." ";
            $productId                           =  DbHelper::update($tableName,array('vSellerStatus' => generalMethods::escapeString($productObjVo->seller_vacation_mode)),$where);
         if($productId) {
            $objResult->status                  =    SUCCESS;
            $objResult->data                    =    $productId;
         }else{
             $objResult->status                 =    ERROR;
         }

         return $objResult;
    }


    //

     public static function getProduct_list_Join_Search_Query($featured='',$order='')
    {
       global $tableprefix;

       $productListingFee       = getSettingsValue('product_listing_fee');
       $whereCondition =   '';
        $sql            =   "SELECT p.*, (p.product_price + p.default_additional_price) AS product_price FROM " . $tableprefix . "products p
                            INNER JOIN " . $tableprefix . "artists s ON s.artist_id =p.product_artist_id where p.vapproved = 'Y' ";
        $whereCondition.=" AND p.deleted='N' AND s.plan_expired ='N' ";  // check deleted status
        $whereCondition.=" AND p.publish_status='Y' ";  // check published status
        $whereCondition.=" AND p.seller_vacation_mode='N' ";  // check seller vacation ode settings
        $whereCondition.=" AND p.vSellerStatus='Y' ";  // check seller approved or plan expiry status
        if ($productListingFee > 0) {
                $whereCondition .= " AND p.payment_status='COMPLETED' ";  // Check seller payment completed
            }
        if($featured =='Y')
            {
                $whereCondition .= " AND p.featured='Y' ";
            }

            if(!empty($order))
                {
                    $orderBy     =  " ORDER BY p." .$order;
                }

         $sql    =   $sql .$whereCondition . $orderBy;

         return $sql;

    }
     public static function getProduct_list_Join_Search_Query_Count($featured='',$order='')
    {
       global $tableprefix;

       $productListingFee       = getSettingsValue('product_listing_fee');
       $whereCondition =   '';
        $sql            =   "SELECT count(p.product_id) as cnt FROM " . $tableprefix . "products p
                            INNER JOIN " . $tableprefix . "artists s ON s.artist_id =p.product_artist_id where p.vapproved = 'Y' ";
        $whereCondition.=" AND p.deleted='N' AND s.plan_expired = 'N' ";  // check deleted status
        $whereCondition.=" AND p.publish_status='Y' ";  // check published status
        $whereCondition.=" AND p.seller_vacation_mode='N' ";  // check seller vacation ode settings
        $whereCondition.=" AND p.vSellerStatus='Y' ";  // check seller approved or plan expiry status
        if ($productListingFee > 0) {
                $whereCondition .= " AND p.payment_status='COMPLETED' ";  // Check seller payment completed
            }
        if($featured =='Y')
            {
                $whereCondition .= " AND p.featured='Y' ";
            }

            if(!empty($order))
                {
                    $orderBy     =  " ORDER BY p." .$order;
                }

         $sql    =   $sql .$whereCondition . $orderBy;

         return $sql;

    }
    
   public static function getProduct_list_Seller($sellerId)
    {
        $objResult = new stdClass();
        $tableName = DbHelper::setTablePrefix('products');
        $product_list = '';
        if ($sellerId != '') {
            $where = "product_artist_id = " . generalMethods::escapeString($sellerId);
            $objResultRow = DbHelper::selectResult($tableName, 'product_id,product_name', $where);

            foreach ($objResultRow as $value) {
                $product_list[$value->product_id] = $value->product_name;
            }
        }
        return $product_list;
    }
    
   public static function getCategory_list_Seller($sellerId)
    {
        $objResult = new stdClass();
        $productsTable = DbHelper::setTablePrefix('products');
        $categoryTable = DbHelper::setTablePrefix('categories');
        $category_list = '';
        if ($sellerId != '') {
            $where = " product_artist_id = " . generalMethods::escapeString($sellerId);
            $objResultRow = DbHelper::selectResult($productsTable, 'DISTINCT(product_category)', $where);
            if(!empty($objResultRow)){            
                foreach ($objResultRow as $value) {
                    $categories[] = $value->product_category;
                }
                $category_ids = implode(",", $categories);
                $where2 = "category_id IN (" . $category_ids.")";
                $objResult = DbHelper::selectResult($categoryTable, 'category_id,category_name', $where2);
                foreach ($objResult as $value) {
                    $category_list[$value->category_id] = $value->category_name;
                }                
            }            
        }
        return $category_list;
    } 
    
    
    
    public static function getProduct_list_QueryCount($featured='',$order='')
    {
       global $tableprefix;
       
       $productListingFee       = getSettingsValue('product_listing_fee');
       $whereCondition =   '';
        $sql    =   "SELECT count(product_id)as productCount FROM " . $tableprefix . "products p where p.vapproved = 'Y' ";
        $whereCondition.=" AND p.deleted='N' ";  // check deleted status
        $whereCondition.=" AND p.publish_status='Y' ";  // check published status
        $whereCondition.=" AND p.seller_vacation_mode='N' ";  // check seller vacation ode settings
        $whereCondition.=" AND p.vSellerStatus='Y' ";  // check seller approved or plan expiry status
        if ($productListingFee > 0) {
                $whereCondition .= " AND p.payment_status='COMPLETED' ";  // Check seller payment completed
            }
        if($featured =='Y')
            {
                $whereCondition .= " AND p.featured='Y' ";
            }

            if(!empty($order))
                {
                    $orderBy     =  " ORDER BY p." .$order;
                }

        $sql    =   $sql .$whereCondition . $orderBy;

         return $sql;

    }
    
      public static function getSellerCartItems($sellerid)
    {
                 $objResult                      = new stdClass();
                 $tableName                      = DbHelper::setTablePrefix('cart');
                 $selectCase                     = "seller_id = '".generalMethods::escapeString($sellerid)."'   ";
                 $objResultRow                   = DbHelper::selectSingleValue($tableName,'count(cart_id)as cartCount',$selectCase);
                 return $objResultRow;

    }
    
    
     public static function chekProductOptionsExist($productid)
    {
                 $objResult                      = new stdClass();
                 $tableName                      = DbHelper::setTablePrefix('product_options');

                 if(!empty($productid)) {
                     $selectCase                     = " product_id =".generalMethods::escapeString($productid)." AND default_option ='YES' ";
                     $objResultRow                   =  DbHelper::selectSingleValue($tableName,'product_option_id',$selectCase);
                     // If user exists then assign user data to result object
                     return $objResultRow;
                }
                 
        

    }
    
     public static function removeProductOptions($product_option_id)
    {
            //clear the seller product from carts table
            $where                          = "product_option_id = ".$product_option_id;
            $tableName                      = DbHelper::setTablePrefix('product_options');
            $objResultRow                   = DbHelper::delete($tableName,$where);
            //
    }
    
     public static function getCategoryHierarchy($txtSearch, $categoryid='', $category_list)
    {
            if($categoryid == "")
               $where = 'category_name LIKE "%'.$txtSearch.'%"'; 
            else
                $where = 'parent_id="'.$categoryid.'"';
            
            $objResult = new stdClass();
            $categoryTable = DbHelper::setTablePrefix('categories');
        
            //$category_list = '';
            $objResultRow = DbHelper::selectResult($categoryTable, 'category_id', $where);
            //print_r($objResultRow);//exit;
            if(empty($objResultRow))
            {
               return $category_list;
            }
        
            foreach($objResultRow as $key=>$val)
            {   //echo $val->category_id;
                    array_push($category_list,$val->category_id);
                    $category_list = Products::getCategoryHierarchy($txtSearch,$val->category_id,$category_list);                       //print_r($category_list);exit;
            }
            //print_r($category_list);
            return $category_list;
    }
    
    
      public static function getAllParentCategoryList()
    {
            
            $where = "parent_id=0 AND gift_certificate_flag!='Y' "; 
            $objResult = new stdClass();
            $categoryTable = DbHelper::setTablePrefix('categories');
            //$category_list = '';
            $objResultRow = DbHelper::selectResult($categoryTable, 'category_id,category_name', $where);
            return $objResultRow;
      
    }
    
    
    public static function getCategoryChild($id) {
    $where = "parent_id ='".DbHelper::escapeString($id)."' "; 
    $objResult = new stdClass();
    $categoryTable = DbHelper::setTablePrefix('categories');
    //$category_list = '';
    $objResultRow = DbHelper::selectResult($categoryTable, 'category_id,category_name', $where);
    $children = array();
    if(!empty($objResultRow))
    {
        #It has children, let's get them.
        foreach($objResultRow as $row)
        {          
            #Add the child to the list of children, and get its subchildren
            $children[$row->category_id]['name'] = $row->category_name;
            $children[$row->category_id]['id']   = $row->category_id;
            $arr = self::getCategoryChild($row->category_id);
            if(count($arr) > 0)
            {
                $children[$row->category_id]['child'] = self::getCategoryChild($row->category_id);
            } 
        }
    }
    
    return $children;
}


public static function make_cat_list($p_cid,$space=''){
    
 	global $tableprefix;
        $q="SELECT * FROM ".$tableprefix."categories WHERE parent_id='$p_cid' AND gift_certificate_flag!='Y' ";
	$r=mysql_query($q) or die(mysql_error());
	$count=mysql_num_rows($r);
        $optGroup = '';
        $optVals  = '';
	if($p_cid==0){
		$space='';
                
	}else{
		$space .="&nbsp;&nbsp;&nbsp;&nbsp;";
	   }
	if($count > 0){
            
		while($row=mysql_fetch_array($r)){
                    
                    $subCategoryCount = self::getSubCategoryCount($row['category_id']);
                    
                    if($existCategory==$row['category_id'])
                    {
                        $selected = 'selected';
                    }else{
                        $selected = '';
                    }
                    
                    if($row['parent_id']==0)
                    {
                        $optVals = "<optgroup value=".$row['category_id']." label=".$row['category_name']." >";
                        
                        if($subCategoryCount==0)
                        {
                            $optVals.="<option value='".$row['category_id']."' ".$selected.">".$space.$row['category_name']." ";
                            $optVals.="</option>";
                        }
                        
                    }
             if($subCategoryCount>0)
                {       
                    $optVals.="<option value='".$row['category_id']."' ".$selected.">".$space.$row['category_name']." ";
                    $optVals.="</option>";
                }      
                $optVals.="</optgroup>"; 
                   
                   echo $optVals;
                   
			self::make_cat_list($row['category_id'],$space);
		}
		
	}

}



public static function getSubCategoryCount($categoryID)
    {
                 $objResult                      = new stdClass();
                 $tableName                      = DbHelper::setTablePrefix('categories');
                 
                 if(!empty($categoryID)) {
                 $selectCase                     = "parent_id =".generalMethods::escapeString($categoryID);
                 $objResultRow                   = DbHelper::selectSingleValue($tableName,'count(category_id) as CategoyCnt',$selectCase);
         // If user exists then assign user data to result object
                 }
                

         return $objResultRow;

    }

    
    
public static function getOptionsCount($product_id)
    {
                 $objResult                      = new stdClass();
                 $tableName                      = DbHelper::setTablePrefix('product_options');
                 
                 if(!empty($product_id)) {
                 $selectCase                     = "product_id =".generalMethods::escapeString($product_id)."  AND default_option='NO' ";
                 
                 $objResultRow                   = DbHelper::selectSingleValue($tableName,'count(product_option_id) as OptionsCount',$selectCase);
         // If user exists then assign user data to result object
                 }
                

         return $objResultRow;

    }
    
    
    public static function getCategoryOptionsValues($categoryId)
    {
            $objResult                      =   new stdClass();
            $tableName                      =   DbHelper::setTablePrefix('category_options');
            $where                          =   "category_id='".$categoryId."' "; 
            //$category_list = '';
            $objResultRow                   =   DbHelper::selectResult($tableName, 'option_id', $where);
            return $objResultRow;

    }
    
    
        public static function updateCancelledQuantityToProductStock($productid,$product_option_id,$quantity,$prefix=NULL)
    {
           global $tableprefix;
           $tableprefix = $tableprefix!=''?$tableprefix:$prefix;
           
            //Check the Option is default option
           $default_option_id = self::getDefaultProductOptionId($productid);
           
           //Update Products Default Stock
           if($default_option_id==$product_option_id)
           {
               self::updateCancelledQuantityToProductDefaultStock($productid,$quantity);
           }
                     
           $sqlUpdate       =   "UPDATE ". $tableprefix."product_options SET product_stock = product_stock + $quantity where product_option_id='".$product_option_id."' ";           
           $resSet          =   DbHelper::execute($sqlUpdate);
           
           //Update Default Options St
        
    }
    
    
        public static function updateCancelledQuantityToProductDefaultStock($productid,$quantity,$prefix=NULL)
    {
           global $tableprefix;
           $tableprefix = $tableprefix!=''?$tableprefix:$prefix;
                     
           $sqlUpdate       =   "UPDATE ". $tableprefix."products SET default_stock = default_stock + $quantity where product_id='".$productid."' ";           
           $resSet          =   DbHelper::execute($sqlUpdate);
          
    }
    
    
     public static function getProductInfo($productid)
    {
    	
    	$tableName                      =   DbHelper::setTablePrefix('products');
    	$andCase                        =   '';
    	 if($productid!='')
                 $andCase.= ' product_id = '.generalMethods::escapeString($productid).'   '; 
        
    	$objResultRow                   =  DbHelper::selectRecord($tableName,'*',$andCase);
    
    	return $objResultRow;
    	 
    }
    
    
    
    
}