<?php
defined( '_VALID_MOS' ) or die( 'Direct Access to this location is not allowed.' ); 
/**
* @version $Id: ps_csv.php,v 1.11 2005/02/09 17:14:41 soeren_nb Exp $
* @package mambo-phpShop
* @copyright (C) John Syben (webme.co.nz)
*
* Conversion to Mambo, modifications and CSV export:
* @copyright (C) 2004-2005 Soeren Eberhardt
*
* @license http://www.gnu.org/copyleft/gpl.html GNU/GPL
* mambo-phpShop is Free Software.
* mambo-phpShop comes with absolute no warranty.
*
* www.mambo-phpshop.net
*/

/* The ps_csv class
*
* This class allows for the adding of multiple
* products and categories from a csv file
*************************************************************************/

class ps_csv {
  var $classname = "ps_csv";

  /**************************************************************************
  ** name: upload_csv()
  ** created by: John Syben
  ** modified by: nhyde
  ** A db table named 'mos_pshop_csv' must exist with the product fields
  ** allocated their relative positions in the csv line
  ***************************************************************************/

  function upload_csv(&$d) {
  
    // handle the upload here
    if (false == $this->handle_csv_upload($d) )
        return false;


    // Get row positions of each element as set in csv table
    $db = new ps_DB;
    $q = "SELECT * FROM #__pshop_csv ";
    $db->query($q);
    $db->next_record();
    
    $dbc = new ps_DB;
    $q = "SELECT vendor_currency FROM #__pshop_vendor WHERE vendor_id='1' ";
    $dbc->query($q);
    $dbc->next_record();
    $product_currency = $dbc->f("vendor_currency");
    
    // Open csv file
    $file = $_FILES["file"]["tmp_name"];
    $fp = fopen ($file,"r");
    $this_error="";
    $d["message"] = "";
    $line=1;
    $enclosure = stripslashes(@$d['csv_enclosurechar']);

    if ( ((float)substr(phpversion(), 0, 3) >= 4.3) && !empty($enclosure)) {
      $greater43 = true;
      $data = fgetcsv($fp, 4096, $d['csv_delimiter'], $enclosure);
    } 
    else {
      $greater43 = false;
      $data = fgetcsv($fp, 4096, $d['csv_delimiter']);
    }
    
      // Run through each line of file
      while ($data) {
      
          //$data = $this->fgetcsvfromline( $csv_line, 18, $d['csv_delimiter'], $enclosure) );
          //array_shift( $data );
          // Check for SKU - required
          if (!$data[$db->f("csv_product_sku")-1]) 
            $this_error .= "No SKU, ";
          else 
            $product_sku = $data[$db->f("csv_product_sku")-1];

          // Check for Product Name - required
          if (!$data[$db->f("csv_product_name")-1]) 
            $this_error .= "No Product Name, ";
          else 
            $product_name = $data[$db->f("csv_product_name")-1];

          // Check for Price - required
          if (!$data[$db->f("csv_product_price")-1]) 
            $this_error .= "No Price, ";
          else 
            $product_price = $data[$db->f("csv_product_price")-1];

          // Check for Category Path - required
          if (!$data[$db->f("csv_category_path")-1]) 
            $this_error .= "No Category Path, ";
          else 
            $category_path = $data[$db->f("csv_category_path")-1];

          // If a required field was missing, add to error to main message and start next line
          // Otherwise add or update product
          if (!empty($this_error)) {
            $d['message'] .= "Line $line $this_error<br />";
            $this_error = "";
          }
          else { 
            $timestamp = time();

            // See if sku exists. If so, update product - otherwise add product
            $dbp = new ps_DB;
            $q = "SELECT * FROM #__pshop_product ";
            $q .= "WHERE product_sku='$product_sku'";
            $dbp->query($q);
              if ($dbp->next_record()) { // SKU exists - update product
                // Update product information
                $q = "UPDATE #__pshop_product SET ";
                $q .= "product_s_desc='" . $data[$db->f("csv_product_s_desc")-1] . "',";
                $q .= "product_desc='" . $data[$db->f("csv_product_desc")-1] . "',";
                $q .= "product_thumb_image='" . $data[$db->f("csv_product_thumb_image")-1] . "',";
                $q .= "product_full_image='" . $data[$db->f("csv_product_full_image")-1] . "',";
                $q .= "product_weight='" . $data[$db->f("csv_product_weight")-1] . "',";
                $q .= "product_weight_uom='" . $data[$db->f("csv_product_weight_uom")-1] . "',";
                $q .= "product_length='" . $data[$db->f("csv_product_length")-1] . "',";
                $q .= "product_width='" . $data[$db->f("csv_product_width")-1] . "',";
                $q .= "product_height='" . $data[$db->f("csv_product_height")-1] . "',";
                $q .= "product_lwh_uom='" . $data[$db->f("csv_product_lwh_uom")-1] . "',";
                $q .= "product_in_stock='" . $data[$db->f("csv_product_in_stock")-1] . "',";
                $q .= "product_available_date='" . $data[$db->f("csv_product_available_date")-1] . "',";
                $q .= "product_special='" . $data[$db->f("csv_product_special")-1] . "',";
                $q .= "product_discount_id='" . $data[$db->f("csv_product_discount_id")-1] . "',";
                $q .= "product_name='" . $product_name . "', ";
                $q .= "mdate='" . $timestamp . "' ";
                $q .= "WHERE product_sku='" . $product_sku . "'";
                $dbu = new ps_DB;
                $dbu->query($q);

                // Get default shopper group ID
                $q = "SELECT shopper_group_id FROM #__pshop_shopper_group ";
                $q .= "WHERE `default`='1'";
                $dbsg = new ps_DB;
                $dbsg->query($q);
                $dbsg->next_record();

                // Update product price for default shopper group   
                $q = "UPDATE #__pshop_product_price SET ";
                $q .= "product_price='" . $product_price . "',";
                $q .= "product_currency='" . $product_currency . "',";
                $q .= "shopper_group_id='" . $dbsg->f("shopper_group_id") . "', ";
                $q .= "mdate='" . $timestamp . "' ";
                $q .= "WHERE product_id='" . $dbp->f("product_id") . "'";
                $dbpp = new ps_DB;
                $dbpp->query($q);
                
                // Use csv_category() method to confirm/add category tree for this product
                // Modification: $category_id now is an array
                $category_id = $this->csv_category($data[$db->f("csv_category_path")-1]);
                
                $dbcat = new ps_DB;
                // Delete old entries
                $q  = "DELETE FROM #__pshop_product_category_xref WHERE product_id =";
                $q .= " '".$dbp->f("product_id")."'";
                $dbcat->query($q);
                
                // Insert new product/category relationships
                foreach( $category_id as $value ) {
                  $q  = "INSERT INTO #__pshop_product_category_xref (category_id, product_id ) VALUES (";
                  $q .= "'$value', '".$dbp->f("product_id")."')";
                  $dbcat->query($q);
                }
                // Add report for this line to message
                $d["message"] .= "Line: $line Updated Product SKU: $product_sku<br />";
              }
              else { // SKU does not exist - add new product
                // Add product information
                $q  = "INSERT INTO #__pshop_product (vendor_id,product_parent_id,product_sku,";
                $q .= "product_name,product_desc,product_s_desc,";
                $q .= "product_thumb_image,product_full_image,";
                $q .= "product_publish,product_weight,product_weight_uom,";
                $q .= "product_length,product_width,product_height,product_lwh_uom,";
                $q .= "product_in_stock,";
                $q .= "product_available_date,product_special,product_discount_id,";
                $q .= "cdate,mdate) ";
                $q .= "VALUES ('1','0','";
                $q .= $data[$db->f("csv_product_sku")-1] . "','" . $data[$db->f("csv_product_name")-1] . "','";
                $q .= $data[$db->f("csv_product_desc")-1] . "','" . $data[$db->f("csv_product_s_desc")-1] . "','";
                $q .= $data[$db->f("csv_product_thumb_image")-1] . "','";
                $q .= $data[$db->f("csv_product_full_image")-1] . "','Y','";
                $q .= $data[$db->f("csv_product_weight")-1] . "','" . $data[$db->f("csv_product_weight_uom")-1] . "','";
                $q .= $data[$db->f("csv_product_length")-1] . "','" . $data[$db->f("csv_product_width")-1] . "','";
                $q .= $data[$db->f("csv_product_height")-1] . "','" . $data[$db->f("csv_product_lwh_uom")-1] . "','";
                $q .= $data[$db->f("csv_product_in_stock")-1] . "','";
                $q .= $data[$db->f("csv_product_available_date")-1] . "','";
                $q .= $data[$db->f("csv_product_special")-1] . "','";
                $q .= $data[$db->f("csv_product_discount_id")-1] . "','$timestamp','$timestamp')";
                $dbu = new ps_DB;
                $dbu->query($q);
                
                // Get the product ID we just created
                $q  = "SELECT product_id FROM #__pshop_product ";
                $q .= "WHERE product_sku = '" . $product_sku . "' ";
                $q .= "AND vendor_id = '1' ";
                $q .= "AND cdate = $timestamp";
                $dbpi = new ps_DB;
                $dbpi->query($q);
                $dbpi->next_record();
                $product_id = $dbpi->f("product_id");
                
                $dbcat = new ps_DB;
                // Store the manufacturer ID and create a
                // product <-> manufacturer relationship
                $q = "INSERT INTO #__pshop_product_mf_xref VALUES (";
                $q .= "'$product_id', '".$data[$db->f("csv_manufacturer_id")-1]."')";
                $dbcat->setQuery($q);  $dbcat->query();
                
                // Use csv_category() method to confirm/add category tree for this product
                $category_id = $this->csv_category($data[$db->f("csv_category_path")-1]);

                // Insert new product/category relationships
                foreach( $category_id as $value ) {
                  $q  = "INSERT INTO #__pshop_product_category_xref (category_id, product_id ) VALUES (";
                  $q .= "'$value', '$product_id')";
                  $dbcat->query($q);
                }

                // Get default shopper group ID
                $q = "SELECT shopper_group_id FROM #__pshop_shopper_group ";
                $q .= "WHERE `default`='1'";
                $dbsg = new ps_DB;
                $dbsg->query($q);
                $dbsg->next_record();

                // Add  product price for default shopper group
                $q = "INSERT INTO #__pshop_product_price ";
                $q .= "(product_price,product_currency,product_id,shopper_group_id,mdate) ";
                $q .= "VALUES ('";
                $q .= $product_price . "','";
                $q .= $product_currency . "','";
                $q .= $product_id . "','";
                $q .= $dbsg->f("shopper_group_id") . "','";
                $q .= $timestamp . "') ";
                $dbpp = new ps_DB;
                $dbpp->query($q);

                // Add report for this line to message
                $d['message'] .=  "Line: $line Added Product SKU: $product_sku<br />";
              }
          }
        $line++;
        if ($greater43) {
          $data = fgetcsv($fp, 4096, $d['csv_delimiter'], $enclosure);
        } 
        else {
          $data = fgetcsv($fp, 4096, $d['csv_delimiter']);
        }
      } // End while
    fclose ($fp);
    return True;

  } //End function upload_csv


  /**************************************************************************
  ** name: csv_category()
  ** created by: John Syben
  ** Creates categories from slash delimited line
  ***************************************************************************/
  function csv_category($line) {
  
    // New: Get all categories in this field,
    // delimited with |
    $categories = explode("|", $line);
    foreach( $categories as $line ) {
      // Explode slash delimited category tree into array
      $category_list = explode("/", $line);
      $category_count = count($category_list);
  
      $db = new ps_DB;
      $category_parent_id = '0';
  
        // For each category in array
        for($i = 0; $i < $category_count; $i++) {
          // See if this category exists with it's parent in xref
          $q = "SELECT #__pshop_category.category_id FROM #__pshop_category,#__pshop_category_xref ";
          $q .= "WHERE #__pshop_category.category_name='" . $category_list[$i] . "' ";
          $q .= "AND #__pshop_category_xref.category_child_id=#__pshop_category.category_id ";
          $q .= "AND #__pshop_category_xref.category_parent_id='$category_parent_id'";
          $db->query($q);
            // If it does not exist, create it
            if ($db->next_record()) { // Category exists
              $category_id = $db->f("category_id");
            }
            else { // Category does not exist - create it
              $hash_secret="PHPShopIsCool";
              $category_id = md5(uniqid($hash_secret));
              $timestamp = time();
              
              // Let's find out the last category in
              // the level of the new category
              $q = "SELECT MAX(list_order) AS list_order FROM #__pshop_category_xref,#__pshop_category ";
              $q .= "WHERE category_parent_id='".$category_parent_id."' ";
              $q .= "AND category_child_id=category_id ";
              $db->query( $q );
              $db->next_record();
              
              $list_order = intval($db->f("list_order"))+1;
              
              // Add category
              $q = "INSERT INTO #__pshop_category ";
              $q .= "(category_id,vendor_id,category_name, category_publish,cdate,mdate,list_order) ";
              $q .= "VALUES ('";
              $q .= $category_id . "','";
              $q .= "1', '";
              $q .= $category_list[$i] . "', '";
              $q .= "Y', '";
              $q .= $timestamp . "', '";
              $q .= $timestamp . "', '$list_order')";
              $db->query($q);
  
              // Create xref with parent
              $q = "INSERT INTO #__pshop_category_xref ";
              $q .= "(category_parent_id, category_child_id) ";
              $q .= "VALUES ('";
              $q .= $category_parent_id . "', '";
              $q .= $category_id . "')";
              $db->query($q);
            }
          // Set this category as parent of next in line
          $category_parent_id = $category_id;
        } // end for
    $category[] = $category_id;
    }
    // Return an array with the last category_ids which is where the product goes
    return $category;

  } // End function csv_category
	
	/**
	  * Handle the upload of file "file".
	  *
	  * Longer, multi-line description here.
	  * 
	  * @name handle_csv_upload
	  * @author Nathan Hyde <nhyde@bigdrift.com>
	  * @param array d posted items crammed into 1 arr
	  * @returns boolean True of False
	  */
	function handle_csv_upload(&$d) {
		$allowed_suffixes_arr = array(
			0=> 'csv'
			,1 => 'txt'
			// add more here if needed
		);
		
		$allowed_mime_types_arr = array(0 => 'text/html'
                                        ,1 => 'text/plain'
                                        ,2 => 'application/octet-stream'
                                        ,3 => 'application/vnd.ms-excel'
                                        ,4 => 'application/force-download'
                                        // add more here if needed
    );
		
    $error = ""; 
    $fileinfo = pathinfo($_FILES["file"]["name"]); 
    $extension = $fileinfo["extension"]; 
  
      if (!in_array($extension, $allowed_suffixes_arr) ) {
        $d["error"] = "Suffix not allowed. Valid suffixes are: " . join(", ",$allowed_suffixes_arr);
        return False;
      }
      // test the mime type here
      if (!in_array($_FILES["file"]["type"], $allowed_mime_types_arr) ) {
        $d["error"] = "Mime type not accepted. Type for file uploaded: ".$_FILES["file"]["type"];
        return False;
      }
      
      // do the moovin here :)
      // not necessary for us to do cause it's a temporary file, right?
      /** 
      if (is_uploaded_file($d['file_tmp_name']) ) {
        copy($d['file_tmp_name'], "/place/to/put/uploaded/file");
      } else {
        echo "Possible file upload attack. Filename: " . $_FILES['userfile']['name'];
      }
      // ...or... 
      move_uploaded_file($d["file_tmp_name"], "/place/to/put/uploaded/file");
      
      **/
      
  
      return True;
    }
    
	/**
	  * Handle the parsing of a string containing csv fields.
	  *
	  * @name fgetcsvfromline
	  * @author dawa at did-it dot com, posted at www.php.net
	  * @param string line
    * @param 
	  * @returns array $matches
    * The first field contains the whole line
	  */
    function fgetcsvfromline ($line, $columnCount, $delimiterChar = ',', $enclosureChar = '"') {
        $regExpSpecialChars = array (
            "|" => "\\|",
            "&" => "\\&",
            "$" => "\\$",
            "(" => "\\(",
            ")" => "\\)",
            "^" => "\\^",
            "[" => "\\[",
            "]" => "\\]",
            "{" => "\\{",
            "}" => "\\}",
            "." => "\\.",
            "*" => "\\*",
            "\\" => "\\\\",
            "/" => "\\/"
        );
       $matches = array();
       $delimiterChar = strtr($delimiterChar, $regExpSpecialChars);
       $enclosureChar = strtr($enclosureChar, $regExpSpecialChars);
       $cutpoint = strlen($delimiterChar)+1;
       $regExp = "/^";
       for ($i = 0; $i < $columnCount; $i++) {
            $regExp .= $enclosureChar.'?(.*?)'.$enclosureChar.'?'.$delimiterChar;
       }
       $regExp = substr($regExp,0,-$cutpoint).'/';
       if (preg_match($regExp, $line, $matches)) {
            return $matches;
       }
       return 0;
    }
    
	/**
	  * Handle the export of product records in a csv file
	  *
	  * @name export_csv
	  * @author soeren
	  * @param array d
	  * @returns void
    * 
	  */
    function export_csv( &$d ) {
        global $mosConfig_sitename;
        $db = new ps_DB;

        // Get default shopper group ID for prices
        $q = "SELECT shopper_group_id FROM #__pshop_shopper_group ";
        $q .= "WHERE `default`='1'";
        $db->query($q);
        $db->next_record();
        $shopper_group_id = $db->f("shopper_group_id");
        
        /** Get all products - including items!!! 
        *  PLEASE NOTE THAT THE CSV IMPORT SCRIPT
        * WILL NOT SEPARATE ITEMS FROM PRODUCTS
        * SO ITEMS ARE INSERTED WITHOUT A
        * PARENT PRODUCT WHEN RE-IMPORTING !!! **/
        $sql = 'SELECT #__pshop_product.product_id, product_sku, product_s_desc, product_desc, product_thumb_image, product_full_image,'
            . ' product_weight, product_weight_uom, product_length, product_width, product_height, product_lwh_uom, product_in_stock, product_available_date'
            . ', product_special, product_discount_id, product_name, product_price, manufacturer_id'
            . ' FROM #__pshop_product, #__pshop_product_price, #__pshop_product_mf_xref'
            . ' WHERE vendor_id = \'1\' AND shopper_group_id = \'5\' AND #__pshop_product.product_id = #__pshop_product_price.product_id AND #__pshop_product.product_id = #__pshop_product_mf_xref.product_id';
        
        $db->query( $sql );
        $delim = $d['csv_delimiter'];
        $encl = stripslashes(@$d['csv_enclosurechar']);
        
        if(empty($encl) && !isset($d['csv_enclosurechar'])) $encl = "\"";
        
        $contents = "";
        /** Loop through all records 
        * and create the csv file - line after line ***/
        while( $db->next_record() ) {
            $contents .= $encl . $db->f("product_sku"). $encl
                                . $delim . $encl . $db->f("product_s_desc") . $encl
                                . $delim . $encl . str_replace("\r", "", str_replace("\n", "", str_replace("\r\n", "", addslashes($db->f("product_desc"))))) . $encl
                                . $delim . $encl . $db->f("product_thumb_image") . $encl
                                . $delim . $encl . $db->f("product_full_image") . $encl
                                . $delim . $encl . $db->f("product_weight") . $encl
                                . $delim . $encl . $db->f("product_weight_uom") . $encl
                                . $delim . $encl . $db->f("product_length") . $encl
                                . $delim . $encl . $db->f("product_width") . $encl
                                . $delim . $encl . $db->f("product_height") . $encl
                                . $delim . $encl . $db->f("product_lwh_uom") . $encl
                                . $delim . $encl . $db->f("product_in_stock") . $encl
                                . $delim . $encl . $db->f("product_available_date") . $encl
                                . $delim . $encl . $db->f("product_special") . $encl
                                . $delim . $encl . $db->f("product_discount_id") . $encl
                                . $delim . $encl . $db->f("product_name") . $encl
                                . $delim . $encl . $db->f("product_price") . $encl
                                . $delim . $encl . $this->get_category_path( $db->f("product_id") ) . $encl
                                . $delim . $encl . $db->f("manufacturer_id") . $encl . "\n";
        }

        $filename = "mambo-phpShop_" .date("jmYHis"). ".csv";

        if (ereg('Opera(/| )([0-9].[0-9]{1,2})', $_SERVER['HTTP_USER_AGENT'])) {
          $UserBrowser = "Opera";
        }
        elseif (ereg('MSIE ([0-9].[0-9]{1,2})', $_SERVER['HTTP_USER_AGENT'])) {
          $UserBrowser = "IE";
        } else {
          $UserBrowser = '';
        }
        $mime_type = ($UserBrowser == 'IE' || $UserBrowser == 'Opera') ? 'application/octetstream' : 'application/octet-stream';
        
        // dump anything in the buffer
        @ob_end_clean();
        ob_start();
        header('Content-Type: ' . $mime_type);
        header('Expires: ' . gmdate('D, d M Y H:i:s') . ' GMT');
  
        if ($UserBrowser == 'IE') {
          header('Content-Disposition: inline; filename="' . $filename . '"');
          header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
          header('Pragma: public');
        } else {
          header('Content-Disposition: attachment; filename="' . $filename . '"');
          header('Pragma: no-cache');
        }
         /*** Now dump the data!! ***/
				echo $contents;
				ob_end_flush();
				ob_start();
				// do nothin' more
				exit();
    }
    
	/**
	  * Get the slash delimited category path of a product
	  *
	  * @name get_category_path
	  * @author soeren
	  * @param int $product_id
	  * @returns String category_path
	  */
    function get_category_path( $product_id ) {
        global $database;
        $db = new ps_DB;
        $q = "SELECT #__pshop_product.product_id, #__pshop_product.product_parent_id, category_name,#__pshop_category_xref.category_parent_id "
                ."FROM #__pshop_category, #__pshop_product, #__pshop_product_category_xref,#__pshop_category_xref "
                ."WHERE #__pshop_product.product_id='$product_id' "
                ."AND #__pshop_category_xref.category_child_id=#__pshop_category.category_id "
                ."AND #__pshop_category_xref.category_child_id = #__pshop_product_category_xref.category_id "
                ."AND #__pshop_product.product_id = #__pshop_product_category_xref.product_id";
        $database->setQuery( $q );
        $rows = $database->loadObjectList();
        $k = 1;
        $category_path = "";
        
        foreach( $rows as $row ) {
          $category_name = Array();
          
          /** Check for product or item **/
          if ( $row->category_name ) {
              $category_parent_id = $row->category_parent_id;
              $category_name[] = $row->category_name;
          }
          else {
              /** must be an item 
              * So let's search for the category path of the
              * parent product **/
              $q = "SELECT product_parent_id FROM #__pshop_product WHERE product_id='$product_id'";
              $db->query( $q );
              $db->next_record();
              
              $q  = "SELECT #__pshop_product.product_id, #__pshop_product.product_parent_id, category_name,#__pshop_category_xref.category_parent_id "
                  ."FROM #__pshop_category, #__pshop_product, #__pshop_product_category_xref,#__pshop_category_xref "
                  ."WHERE #__pshop_product.product_id='".$db->f("product_parent_id")."' "
                  ."AND #__pshop_category_xref.category_child_id=#__pshop_category.category_id "
                  ."AND #__pshop_category_xref.category_child_id = #__pshop_product_category_xref.category_id "
                  ."AND #__pshop_product.product_id = #__pshop_product_category_xref.product_id";
              $db->query( $q );
              $db->next_record();
              $category_parent_id = $db->f("category_parent_id");
              $category_name[] = $db->f("category_name");
          }
          if( $category_parent_id == "") $category_parent_id = "0";
          
          while( $category_parent_id != "0" ) {
              $q = "SELECT category_name, category_parent_id "
                      ."FROM #__pshop_category, #__pshop_category_xref "
                      ."WHERE #__pshop_category_xref.category_child_id=#__pshop_category.category_id "
                      ."AND #__pshop_category.category_id='$category_parent_id'";
              $db->query( $q );
              $db->next_record();
              $category_parent_id = $db->f("category_parent_id");
              $category_name[] = $db->f("category_name");
          }
          if ( sizeof( $category_name ) > 1 ) {
            for ($i = sizeof($category_name)-1; $i >= 0; $i--) {
                $category_path .= $category_name[$i];
                if( $i >= 1) $category_path .= "/";
            }
          }
          else
            $category_path .= $category_name[0];
            
          if( $db->num_rows() >= $k++ )
            $category_path .= "|";
        }
        return $category_path;
    }
}
?>
