One Hat Cyber Team

View File Name : prodcombi-csv.php

<?php 
/* For better understanding this file is divided in sections.
 * 1. include files 
 * 2. handling input variables
 * 3. make field declarations and build infofields array of displayed fields
 * 4. retrieve system settings and build php blocks for select fields 
 * 5. publish header block of html page, including javascript version of select blocks
 * 6. publish page header: menu and diagnostic information
 * 7. publish search block
 * 8. Publish the block where you can select your fields
 * 9. Mass edit
 * 10. CSV export
 * 11. CSV import
 * 12. Re-indexation
 * 13. switchform: hide/show/edit fields 
 * 14. generation of product lists
 * 15. Hidden form for copying data from one language to another
 * 16. build and execute the product query 
 * 17. Next and Prev
 * 18. warnings and explanations
 * 19. The main form: the multishop option
 * 20. The main table: the column headers 
 * 21. the main table: the products
 * 22. the main table: the combination data
*/
/* section 1: includes */
if(!@include 'approve.php') die( "approve.php was not found!");
if(!@include 'ps_sourced_code.php') die( "ps_sourced_code.php was not found!");
if(!isset($prestoolslanguage)) $prestoolslanguage = "en";
if(!include $prestoolslanguage.".php") colordie( "Language file ".$prestoolslanguage.".php was not found!");

/* section 2: handling input variables */
$input = $_GET; 
$verbose = "false";
if(!isset($input['search_txt1'])) $input['search_txt1'] = "";
$search_txt1 = trim(mysqli_real_escape_string($conn,$input['search_txt1']));
if(!isset($input['search_txt2'])) $input['search_txt2'] = "";
$search_txt2 = trim(mysqli_real_escape_string($conn,$input['search_txt2']));
if(!isset($input['search_txt3'])) $input['search_txt3'] = "";
$search_txt3 = trim(mysqli_real_escape_string($conn,$input['search_txt3']));
if(!isset($input['search_cmp1'])) $input['search_cmp1'] = "in";
if(!isset($input['search_cmp2'])) $input['search_cmp2'] = "in";
if(!isset($input['search_cmp3'])) $input['search_cmp3'] = "in";
if(!isset($input['search_fld1']) || ($input['search_fld1'] == "")) $input['search_fld1'] = "main product fields";
if(!isset($input['search_fld2']) || ($input['search_fld2'] == "")) $input['search_fld2'] = "main product fields";
if(!isset($input['search_fld3']) || ($input['search_fld3'] == "")) $input['search_fld3'] = "main product fields";
if(!isset($input['combisearch_txt'])) $input['combisearch_txt'] = "";
$combisearch_txt = mysqli_real_escape_string($conn,$input['combisearch_txt']);
if(!isset($input['startrec']) || (trim($input['startrec']) == '')) $input['startrec']="0";
$startrec = intval($input['startrec']);
/* $productedit_numrecs is set in Settings1.php */
if(!isset($input['numrecs'])) $input['numrecs']="50";
$numrecs = intval($input['numrecs']);
if(!isset($input['maxrows'])) $input['maxrows']="500";
$maxrows = intval($input['maxrows']);
if(!isset($input['id_category'])) {$id_category=0;} else {$id_category = intval($input['id_category']);}

if(isset($input['id_shop']) && (intval($input['id_shop'])!=0)) $id_shop = intval($input['id_shop']);
else $id_shop = get_configuration_value('PS_SHOP_DEFAULT');
$def_lang = get_configuration_value('PS_LANG_DEFAULT');
if(isset($input['id_lang']) && (intval($input['id_lang'])!=0)) $id_lang = intval($input['id_lang']);
else $id_lang = $def_lang;

if(!isset($input['startdate'])) $input['startdate']="";
if(!isset($input['enddate'])) $input['enddate']="";
if((!isset($input['rising'])) || ($input['rising'] == "ASC")) {$rising = "ASC";} else {$rising = "DESC";}
if(!isset($fieldsorder)) $fieldsorder = array("name");
if(!isset($input['order'])) /* sorting order */
{ if($id_category == 0) {$input['order']="id_product";} else {$input['order']="position";}
}

if(!isset($input['imgformat'])) {$input['imgformat']="";}
if(!isset($input['groupa'])) $groupa="0"; else $groupa=intval($input['groupa']); 
if(!isset($input['groupb'])) $groupb="0"; else $groupb=intval($input['groupb']); 
if(!isset($input['attributea'])) $attributea="0"; else $attributea=intval($input['attributea']); 
if(!isset($input['attributeb'])) $attributeb="0"; else $attributeb=intval($input['attributeb']);
if(isset($input["discount_included"])) $discount_included = 1; else $discount_included=0;
if(isset($input["suppliers_included"])) $suppliers_included = 1; else $suppliers_included=0;
if(isset($input["stats_included"])) $stats_included = 1; else $stats_included=0;

if(empty($input['fields'])) // if not set, set default set of active fields
  $input['fields'] = array("name","priceVAT");

$langselblock = ""; /* used in the searchblock */
$langcopyselblock = ""; /* used in "copy from other lang" mass edit option; Note: this becomes javascript */
$langids = array();
$langcodes = array();
$query = "SELECT id_lang, name, language_code, iso_code FROM ". _DB_PREFIX_."lang";
$res=dbquery($query);
while ($row=mysqli_fetch_array($res)) 
{ if($def_lang == $row["id_lang"])
  { $def_langname = $row['name'];
    $def_iso_code = $row['iso_code'];
  }
  if($id_lang == $row["id_lang"])
  { $iso_code = $row['iso_code'];
    $languagename = $row['name'];
  }
  $selected='';
  if ($row['id_lang']==$id_lang) 
    $selected=' selected="selected" ';
  else 
    $langcopyselblock .= '<option value='.$row['id_lang'].'>'.$row['language_code'].'</option>';
  $langselblock .= '<option  value="'.$row['id_lang'].'" '.$selected.'>'.$row['name'].'</option>';
  $langids[] = $row["id_lang"];
  $langcodes[$row["id_lang"]] = $row["iso_code"];
}
if(!isset($iso_code))
{ $iso_code = $def_iso_code;
  $languagename = $def_langname;
  echo "<b>Illegal value for default or selected language!</b> Using ".$languagename;
}

if($suppliers_included)
{ $query = "select id_supplier,name from ". _DB_PREFIX_."supplier ORDER BY name";  
  $res=dbquery($query);
  $supplier_names = array();
  while ($row=mysqli_fetch_array($res)) 
  { $supplier_names[$row['id_supplier']] = $row['name'];
  }
}

/* section 3: make field declarations and build infofields array of displayed fields */
  $infofields = array(); /* this will hold the displayed fields with a copy of the field_array entries */
  $if_index = 0;
   /* [0]always same as key, [1]language specific texts, [2]source, [3]display(0=not;1=yes;2=edit;), [4]fieldwidth(0=not set), 
      [5]align(0=default;1=right), [6]not used, [7]Editable, [8]tablefield (used in search block) */
  define("HIDE", 0); define("DISPLAY", 1); define("EDIT", 2);  // display
  define("LEFT", 0); define("RIGHT", 1); // align
  define("NOT_EDITABLE", 0); define("INPUT", 1); define("TEXTAREA", 2); define("DROPDOWN", 3); define("BINARY", 4); define("EDIT_BTN", 5);  /* title, keyover, source, display(0=not;1=yes;2=edit), fieldwidth(0=not set), align(0=default;1=right), sortfield */
   /* sortfield => 0=no escape removal; 1=escape removal; 2 and higher= escape removal and n lines textarea */
  $infofields[$if_index++] = array("",array("","",""), "", DISPLAY, 0, LEFT, 0,0);

  $field_array = array(
   "accessories" => array("accessories",null, "accessories", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "accessories"),
   "active" => array("active",null, "active", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "ps.active"),
   "aDeliveryT" => array("aDeliveryT",null, "additional_delivery_times", DISPLAY, 1, LEFT, null, NOT_EDITABLE, "p.additional_delivery_times"),   
   "aShipCost" => array("aShipCost",null, "additional_shipping_cost", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "ps.additional_shipping_cost"),
   "attachmnts" => array("attachmnts",null, "attachmnts", DISPLAY, 0, LEFT, null, NOT_EDITABLE, ""), 
   "available_now" => array("available_now",null, "available_now", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "pl.available_now"),
   "available_later" => array("available_later",null, "available_later", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "pl.available_later"), 
   "available_date" => array("available_date",null, "available_date", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "ps.available_date"),
   /* availorder combines two of Prestashop's datafields: available_for_order and show_price */
   "availorder" => array("availorder",null, "available_for_order", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "ps.available_for_order"),
   "carrier" => array("carrier",null, "carrier", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "cr.name"),
   "category" => array("category",null, "id_category_default", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "ps.id_category_default"),
   "condition" => array("condition",null, "condition", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "ps.condition"),
   "customizations" => array("customizations",null, "custFlds", DISPLAY, 0, LEFT, null, INPUT, "custFlds"),
   "date_add" => array("date_add",null, "date_add", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "ps.date_add"),  
   "date_upd" => array("date_upd",null, "date_upd", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "ps.date_upd"),
   "deliInStock" => array("deliInStock",null, "delivery_in_stock", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "pl.delivery_in_stock"),
   "deliOutStock" => array("deliOutStock",null, "delivery_out_stock", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "pl.delivery_out_stock"),   
   "description" => array("description",null, "description", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "pl.description"),
   "description_short" => array("description_short",null, "description_short", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "pl.description_short"),
   "discount" => array("discount",null, "discount", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "discount"),
   "ean" => array("ean",null, "ean13", DISPLAY, 200, LEFT, null, NOT_EDITABLE, "p.ean13"),
   "ecotax" => array("ecotax",null, "ecotax", DISPLAY, 200, LEFT, null, NOT_EDITABLE, "ps.ecotax"),
   "featureEdit" => array("featureEdit",null, "name", DISPLAY, 0, LEFT, null, NOT_EDITABLE, ""), // name here is a dummy that is not used
   "isbn" => array("isbn",null, "isbn", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "p.isbn"),
   "id_product" => array("id_product",array("id","id","id"), "id_product", DISPLAY, 0, RIGHT, null,NOT_EDITABLE, "p.id_product"),
   "image" => array("image",null, "name", DISPLAY, 0, LEFT, 0, EDIT_BTN, ""), // name here is a dummy that is not used
   "indexed" => array("indexed",null, "indexed", DISPLAY, 0, LEFT, 0, NOT_EDITABLE, "ps.indexed"),
   "indexes" => array("indexes",null, "indexes", DISPLAY, 0, LEFT, 0, NOT_EDITABLE, "p.indexed"),
   "link_rewrite" => array("link_rewrite",null, "link_rewrite", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "pl.link_rewrite"),
   "location" => array("location",null, "location", DISPLAY, 0, LEFT, null, INPUT, "s.location"),
   "ls_alert" => array("ls_alert",null, "low_stock_alert", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "ps.low_stock_alert"), 
   "ls_threshold" => array("ls_threshold",null, "low_stock_threshold", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "ps.low_stock_threshold"),
   "manufacturer" => array("manufacturer",null, "manufacturer", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "m.name"),
   "meta_description" => array("meta_description",null, "meta_description", DISPLAY, 0, LEFT, null, TEXTAREA, "pl.meta_description"),
   "meta_keywords" => array("meta_keywords",null, "meta_keywords", DISPLAY, 0, RIGHT, null, TEXTAREA, "pl.meta_keywords"),
   "meta_title" => array("meta_title",null, "meta_title", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "pl.meta_title"),
   "minimal_quantity" => array("minimal_quantity",null, "minimal_quantity", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "ps.minimal_quantity"),
   "mpn" => array("mpn",null, "mpn", DISPLAY, 0, LEFT, null, INPUT, "p.mpn"),
   "name" => array("name",null, "name", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "pl.name"),
   "online_only" => array("online_only",null, "online_only", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "ps.online_only"),
   "on_sale" => array("on_sale",null, "on_sale", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "ps.on_sale"),
   "out_of_stock" => array("out_of_stock",null, "out_of_stock", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "s.out_of_stock"),
   "pack_stock_type" => array("pack_stock_type",null, "pack_stock_type", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "ps.pack_stock_type"),
/* pro memory: s.physical_quantity in PS 1.7: this is automatically set when an order is placed */
   "position" => array("position",null, "position", DISPLAY, 0, RIGHT, null, NOT_EDITABLE, "cp.position"),  
   "price" => array("price",null, "price", DISPLAY, 200, LEFT, null, NOT_EDITABLE, "ps.price"),
   "priceVAT" => array("priceVAT",null, "priceVAT", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "priceVAT"),
   "quantity" => array("quantity",null, "quantity", DISPLAY, 0, LEFT, null, TEXTAREA, "s.quantity"),
   "reference" => array("reference",null, "reference", DISPLAY, 200, LEFT, null, NOT_EDITABLE, "p.reference"),
   "reserved" => array("reserved",null, "quantity", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "s.quantity"), 
   "shipdepth" => array("shipdepth",null, "depth", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "p.depth"), 
   "shipheight" => array("shipheight",null, "height", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "p.height"),
   "shipweight" => array("shipweight",null, "weight", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "p.weight"),
   "shipwidth" => array("shipwidth",null, "width", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "p.width"),
   "shopz" => array("shopz",null, "id_shop", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "ps.id_shop"),
   "show_condition" => array("show_condition",null, "show_condition", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "ps.show_condition"),
   /* stockflags combines two of Prestashop's datafields: depends_on_stock and advanced_stock_management */
   "stockflags" => array("stockflags",null, "depends_on_stock", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "s.depends_on_stock"),
   "state" => array("state",null, "state", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "p.state"),   /* STATE_TEMP=0 STATE_SAVED=1 */
   "supplier" => array("supplier",null, "supplier", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "su.name"),
   "tags" => array("tags",null, "tags", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "tg.name"),
   "unit" => array("unit",null, "unity", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "ps.unity"),
   "unitPrice" => array("unitPrice",null, "unit_price_ratio", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "ps.unit_price_ratio"),   
   "upc" => array("upc",null, "upc", DISPLAY, 200, LEFT, null, NOT_EDITABLE, "p.upc"),   
   "VAT" => array("VAT",null, "rate", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "ps.id_tax_rules_group"),
   "virtualp" => array("virtualp",null, "virtualp", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "virtualp"), /* virtual product */
   "visibility" => array("visibility",null, "visibility", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "ps.visibility"), 
   "warehousing" => array("warehousing",null, "quantity", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "w.id_warehouse"),
   "wholesaleprice" => array("wholesaleprice",null, "wholesale_price", DISPLAY, 0, LEFT, null, NOT_EDITABLE, "ps.wholesale_price")
   ); 

  /* the fields for combinations */
  /* 0=showname; 1=left/right; 2=hide/display; 3=fieldname; 4=searchable */
  $combifields = array(
	array("combination", RIGHT,DISPLAY,"",0),
//    array("id_product_attribute",RIGHT,HIDE,"id",1), /* pro memory; hardcoded in cell 1 */
	array("wholesale_price",RIGHT,HIDE,"ws_price",1),
	array("price",RIGHT,DISPLAY,"",1),
	array("priceVAT",RIGHT,DISPLAY,"",1),
	array("ecotax",RIGHT,HIDE,"",1),
	array("weight", RIGHT,DISPLAY,"",1),
	array("unit_price_impact",RIGHT,HIDE,"uprice_impact",1),
	array("default_on", RIGHT,DISPLAY,"",1),
	array("minimal_quantity",RIGHT,HIDE,"",1),
	array("available_date",RIGHT,HIDE,"avail_date",1),
	array("reference",RIGHT,DISPLAY,"",1),
	array("supplier_reference",RIGHT,HIDE,"supplier_ref",1),
	array("location", RIGHT,HIDE,"",1),
	array("ean",RIGHT,HIDE,"",1),
	array("upc",RIGHT,HIDE,"",1),
	array("quantity", RIGHT,DISPLAY,"",1),
	array("image",RIGHT,DISPLAY,"",0));
    if (version_compare(_PS_VERSION_ , "1.7.0.0", ">="))
	  $combifields[] = array("isbn",RIGHT,HIDE,"",1);
    if (version_compare(_PS_VERSION_ , "1.7.3.0", ">="))
	{ $combifields[] = array("low_stock_threshold",RIGHT,HIDE,"ls_threshold",1);
	  $combifields[] = array("low_stock_alert",RIGHT,HIDE,"ls_alert",1);
	}
    if (version_compare(_PS_VERSION_ , "1.7.7", ">="))
	  $combifields[] = array("mpn",RIGHT,HIDE,"",1);
	if($discount_included)
	  $combifields[] = array("discount", RIGHT,DISPLAY,"",1);
	if($suppliers_included)
	  $combifields[] = array("supplier", RIGHT,DISPLAY,"",1);
    if (in_array("salescnt", $input["fields"]))
	  $combifields[] = array("salescnt",RIGHT,DISPLAY,"",1);
    if (in_array("revenue", $input["fields"]))
	  $combifields[] = array("revenue",RIGHT,DISPLAY,"",1);
    if (in_array("orders", $input["fields"]))
	  $combifields[] = array("orders",RIGHT,DISPLAY,"",1);
    if (in_array("buyers", $input["fields"]))
	  $combifields[] = array("buyers",RIGHT,DISPLAY,"",1);	
  
  
  $numfields = sizeof($combifields); /* number of fields */
  for($i=0; $i<$numfields; $i++)
	  if($combifields[$i][3] == "") $combifields[$i][3] = $combifields[$i][0]; 
  
/* remove fields from the field_array that are not present in the used Prestashop version */
$x = 0;
$deleters = array();
foreach($field_array AS $key => $farray)
{ if((($key=="pack_stock_type") && (version_compare(_PS_VERSION_ , "1.6.0.12", "<")))
		 || ($key == "fearureEdit")
		 || (in_array($key, array("isbn", "show_condition","state")) && (version_compare(_PS_VERSION_ , "1.7.0", "<")))
		 || (in_array($key, array("reserved")) && (version_compare(_PS_VERSION_ , "1.7.2", "<")))
		 || (in_array($key, array("ls_threshold", "aDeliveryT", "deliInStock","deliOutStock","ls_alert")) && (version_compare(_PS_VERSION_ , "1.7.3", "<")))
		 || (in_array($key, array("location")) && (version_compare(_PS_VERSION_ , "1.7.5", "<")))
		 || (in_array($key, array("mpn")) && (version_compare(_PS_VERSION_ , "1.7.7", "<"))))
		 $deleters[] = $x;
  $x++;
}
rsort($deleters); /* we have to work from high to low as deleting the low numbers first we would need to adapt the higher ones */
foreach($deleters AS $deleter)
  array_splice($field_array,$deleter,1);
  
  /* put the language specific data at position 1 in the field array */
  /* $screentext_pe is defined in the language file */
  foreach($field_array as $key => $value)
  { if(isset($screentext_pe[$key]) && isset($screentext_pe[$key][1]))
	{ if($screentext_pe[$key][1] == "")
	    $screentext_pe[$key][1] = $screentext_pe[$key][0];
      $field_array[$key][1] = $screentext_pe[$key];
	}
	else /* when users use old translation files that don't support this entry */
	  $field_array[$key][1] = array($key,$value[2],$key);
  }
  
  /* get the infofields array with the active fields. Put the fields pre-sorted in the $fieldsorder array in Settings1.php first */
  $infofields[$if_index++] = $field_array["id_product"];
  foreach($fieldsorder AS $ofield)
  { if (in_array($ofield, $input["fields"]))
    { 	$infofields[$if_index++] = $field_array[$ofield];
	}
  }
  foreach($field_array AS $key => $value)
  { if ((in_array($key, $input["fields"])) && (!in_array($key, $fieldsorder)))
    { $infofields[$if_index++] = $value;
	}
  }

/* section 4: retrieve system settings and build php blocks for select fields */
$rewrite_settings = get_rewrite_settings();
/* the following deals with the case of customized product urls (used for the links on the product name) */
if(defined('_TB_VERSION_'))  /* Thirty Bees has this in the configuration_lang table */
  $route_product_rule = get_configuration_lang_value('PS_ROUTE_product_rule',$id_lang);
else
  $route_product_rule = get_configuration_value('PS_ROUTE_product_rule');
if($route_product_rule != null) /* null is the default */
{ $regexp = preg_quote($route_product_rule, '#');
  $keywordlist = array("id", "rewrite","ean13","category","categories","reference","meta_keywords","meta_title","manufacturer","supplier","price","tags");
  $keywords = array();
  foreach($keywordlist AS $key)
  { if(strpos($route_product_rule, $key) > 0)
	  $keywords[] = $key;
  }
  if (sizeof($keywords)>0) 
  { $transform_keywords = array();
    preg_match_all('#\\\{(([^{}]*)\\\:)?('.implode('|', $keywords).')(\\\:([^{}]*))?\\\}#', $regexp, $m);
    for ($i = 0, $total = count($m[0]); $i < $total; $i++) 
	{ $prepend = $m[2][$i];
      $keyword = $m[3][$i];
      $append = $m[5][$i];
      $transform_keywords[$keyword] = array(
                    'prepend' =>    stripslashes($prepend),
                    'append' =>     stripslashes($append),
                );
	}
  }
}

$statfields = array("salescnt", "revenue","orders","buyers");


/* now get multi-language status. If true you get product urls like www.myshop.com/en/mycat/123-prod.html */
$query='SELECT COUNT(DISTINCT l.id_lang) AS langcount FROM `'._DB_PREFIX_.'lang` l
				JOIN '._DB_PREFIX_.'lang_shop ls ON (ls.id_lang = l.id_lang AND ls.id_shop = '.(int)$id_shop.')
				WHERE l.`active` = 1';
$res=dbquery($query);
$row = mysqli_fetch_array($res);
$langcount = (int)$row['langcount'];
$langinsert = "";
if($langcount>1)
	$langinsert = $iso_code."/";

/* Get default country for the VAT tables and calculations */
$query="select l.name, id_country from ". _DB_PREFIX_."configuration f, "._DB_PREFIX_."country_lang l";
$query .= " WHERE f.name='PS_COUNTRY_DEFAULT' AND f.value=l.id_country ORDER BY id_lang IN('".$def_lang."','1') DESC"; /* the construction with the languages should select all languages with def_lang and '1' first */
$res=dbquery($query);
$row = mysqli_fetch_array($res);
$countryname = $row['name'];
$id_country = $row["id_country"];

/* get shop group and its shared_stock status */
$query="select s.id_shop_group, g.share_stock, g.name from ". _DB_PREFIX_."shop s, "._DB_PREFIX_."shop_group g";
$query .= " WHERE s.id_shop_group=g.id_shop_group and id_shop='".$id_shop."'";
$res=dbquery($query);
$row = mysqli_fetch_array($res);
$id_shop_group = $row['id_shop_group'];
$share_stock = $row["share_stock"];
$shop_group_name = $row["name"];

/* making shop block */
    $shopblock = "";
	$shops = $shop_ids = array();
	$query=" select id_shop,name from ". _DB_PREFIX_."shop ORDER BY id_shop";
	$res=dbquery($query);
	while ($row=mysqli_fetch_array($res)) 
	{ $selected='';
      if ($row['id_shop']==$id_shop) 
		$selected=' selected="selected" ';
      $shopblock .= '<option  value="'.$row['id_shop'].'" '.$selected.'>'.$row['id_shop']."-".$row['name'].'</option>';
	  $shops[] = $row['name'];
	  $shop_ids[] = $row['id_shop'];
	}
	
/* make tax block */
$ps_tax = get_configuration_value('PS_TAX'); /* flag that taxes are enabled */
$taxrates = array();
$taxrates[0] = 0;
$missing_taxgroups = array();
$done = array();
if($ps_tax)
{ $query = "SELECT rate,name,tr.id_tax_rule,g.id_tax_rules_group,g.active,";
  if(version_compare(_PS_VERSION_ , "1.6.0.10", "<"))
    $query .= "0 AS deleted";
  else
    $query .= "g.deleted";
  $query .= " FROM "._DB_PREFIX_."tax_rule tr";
  $query .= " LEFT JOIN "._DB_PREFIX_."tax t ON (t.id_tax = tr.id_tax)";
  $query .= " LEFT JOIN "._DB_PREFIX_."tax_rules_group g ON (tr.id_tax_rules_group = g.id_tax_rules_group)";
  $query .= " WHERE tr.id_country = '".$id_country."' AND tr.id_state='0' AND NOT g.id_tax_rules_group IS NULL";
  $res=dbquery($query);
  $taxblock = '<option value="0">Select VAT</option>';
  while($row = mysqli_fetch_array($res))
  { if(($row["active"]==0) ||($row["deleted"]==1)) 
	  $missing_taxgroups[$row['id_tax_rules_group']] = '<option value="'.$row['id_tax_rules_group'].'" rate="'.(float)$row['rate'].'" selected>'.str_replace("'","\'",$row['name']).'</option>';
    else
      $taxblock .= '<option value="'.$row['id_tax_rules_group'].'" rate="'.(float)$row['rate'].'">'.str_replace("'","\'",$row['name']).'</option>';
    $taxrates[$row['id_tax_rules_group']] = $row['rate'];
	$done[] = $row['id_tax_rules_group'];
  }
  /* now the taxrule groups that have no rate defined for this country */
  $query = "SELECT g.id_tax_rules_group,name,g.active,";
  if(version_compare(_PS_VERSION_ , "1.6.0.10", "<"))
    $query .= "0 AS deleted";
  else
    $query .= "g.deleted";
  $query .= " FROM "._DB_PREFIX_."tax_rules_group g";
  if(sizeof($done) > 0)
    $query .= " WHERE NOT g.id_tax_rules_group IN (".implode(",",$done).")";
  $res=dbquery($query);
  while($row = mysqli_fetch_array($res))
  { if(($row["active"]==0) ||($row["deleted"]==1)) 
	  $missing_taxgroups[$row['id_tax_rules_group']] = '<option value="'.$row['id_tax_rules_group'].'" rate="0" selected>'.str_replace("'","\'",$row['name']).'</option>';
    else
	  $taxblock .= '<option value="'.$row['id_tax_rules_group'].'" rate="0">'.str_replace("'","\'",$row['name']).'</option>';
    $taxrates[$row['id_tax_rules_group']] = 0;
  }
  $taxblock .= "</select>";
}
else
{ $taxblock = '<option value="0">Select VAT</option></select>';
  $query = "SELECT DISTINCT id_tax_rules_group FROM "._DB_PREFIX_."product_shop";
  $res=dbquery($query);
  while($row = mysqli_fetch_array($res))
  { $missing_taxgroups[$row['id_tax_rules_group']] = '<option value="'.$row['id_tax_rules_group'].'" rate="0" selected>TAX rules group '.$row['id_tax_rules_group'].'</option>';
    $taxrates[$row['id_tax_rules_group']] = 0;
  }
}  

  $availordertypes = array(0=>"available", 1=>"show price only",2=>"not available");
  $packstocktypes = array(0=>"Decrement pack only", 1=>"Decrement products in pack only",2=>"Decrement both",3=>"Default");
  $stockflagsarray = array("Manual","Adv Stock Management","ASM with Warehousing");

/* look for double category names */
  $duplos = array();
  $query = "select name,count(*) AS duplocount from ". _DB_PREFIX_."category_lang WHERE id_lang='".$id_lang."' AND id_shop='".$id_shop."' GROUP BY name HAVING duplocount > 1";
  $res=dbquery($query);
  while ($row=mysqli_fetch_array($res)) 
  {  $duplos[] = $row["name"];
  }
 
/* Get category names and rewrites */
  $query = "select c.id_category,name,link_rewrite, id_parent from "._DB_PREFIX_."category c";
  $query .= " left join "._DB_PREFIX_."category_lang cl ON c.id_category=cl.id_category AND id_lang='".$id_lang."'";
  $query .= " AND id_shop='".$id_shop."' ORDER BY name";
  $res=dbquery($query);
  $category_names = $category_rewrites = $category_parents = array();
  while ($row=mysqli_fetch_array($res)) 
  { if(in_array($row['name'], $duplos))
	  $name = $row['name'].$row['id_category'];
	else
	  $name = $row['name'];
    $category_names[$row['id_category']] = $name;
    $category_rewrites[$row['id_category']] = $row['link_rewrite'];	
    $category_parents[$row['id_category']] = $row['id_parent'];	
  } 

  /* make supplier names list */
if(in_array('supplier', $input["fields"]))
{ $query = "select id_supplier,name from ". _DB_PREFIX_."supplier ORDER BY name";  $res=dbquery($query);
  $supplier_names = array();
  $supplierblock0 = '<input type=hidden name="supplier_defaultCQX"><input type=hidden name="mysupsCQX">';
  $supplierblock0 .= '<table><tr><td><select id="supplierlistCQX" size=2>';
  $supplierblock1 = "";
  while ($row=mysqli_fetch_array($res)) 
  { $supplier_names[$row['id_supplier']] = $row['name'];
    $supplierblock1 .= '<option value="'.$row['id_supplier'].'">'.str_replace("'","\'",$row['name']).'</option>';
  }
  $supplierblock1 .= '</select>';
  $supplierblock2 = '</td><td><nobr><a href=# onClick=" Addsupplier(\\\'CQX\\\',1); reg_change(this); return false;"><img src=add.gif border=0></a> &nbsp; &nbsp; ';
  $supplierblock2 .= '<a href=# onClick="Removesupplier(\\\'CQX\\\'); reg_change(this); return false;"><img src=remove.gif border=0></a></nobr></td><td><select size=2 id="supplierselCQX"></select>';
  $supplierblock2 .= '</td><td style="vertical-align:middle"><a href="#" onclick="MakeSupplierDefault(\\\'CQX\\\'); reg_change(this); return false;"><img src="starr.jpg" border="0"></a></td></tr></table>';
}
else 
  $supplierblock0 = $supplierblock1 = $supplierblock2 = "";

/* Make blocks for features */
$query = "SELECT fl.id_feature, name FROM ". _DB_PREFIX_."feature_lang fl";
$query .= " LEFT JOIN ". _DB_PREFIX_."feature_shop fs ON fs.id_feature = fl.id_feature";
$query .= " WHERE id_lang='".$id_lang."' AND id_shop='".$id_shop."'";
$query .= " ORDER BY id_feature";
$res = dbquery($query);
$features = array();
$featurelist = array();
while($row = mysqli_fetch_array($res))
{ $features[$row['id_feature']] = $row['name'];
  if(in_array(str_replace('"','_',$row['name']), $input["fields"]))
  { $featurelist[$row['id_feature']] = $row['name'];
    $featurekeys[] = $row['id_feature'];
  }
}
$featurecount = 0;
  foreach($features AS $key => $feature)
  {	$cleanfeature = str_replace('"','_',$feature);
    if (in_array($cleanfeature, $input["fields"]))
		$featurecount++;
  }



/* section 16: build and execute the product query */
$idfields = array("accessories","p.id_product","cl.id_category","ps.id_category_default","m.id_manufacturer","p.id_supplier","ps.id_tax_rules_group"); /* in these fields you can place comma-separated id numbers */

$wheretext = "";
$manufacturer_needed = false;
for($x=1; $x<=3; $x++)
{ $search_cmp = $input["search_cmp".$x];
  $search_txt = $GLOBALS["search_txt".$x];
  $search_fld = $input["search_fld".$x];
  
//  if($search_txt == "") && (!in_array($search_fld, array("su.name","combinations","cr.name","m.name","pl.description","pl.description_short","discount","custFlds","virtualp"))) && (substr($search_fld,0,7) != "sattrib") && (substr($search_fld,0,7) != "sfeatur")) continue;
  
  if(($search_fld == "m.name") || (($search_fld == "main product fields") && ($search_txt !=""))) $manufacturer_needed = true;
  $nottext = "";
  if($search_cmp == "not_eq") $cmp = "!=";
  else if($search_cmp == "eq") $cmp = "=";
  else if($search_cmp == "gt") $cmp = ">";
  else if($search_cmp == "gte") $cmp = ">=";
  else if($search_cmp == "lt") $cmp = "<";
  else if($search_cmp == "lte") $cmp = "<="; 
  if(($search_cmp == "not_in") || ($search_cmp == "not_eq"))
	$nottext = "NOT ";
  if ($search_txt != "")
  { if($search_cmp == "gt")
	  $inc = "< '".$search_txt."'";
    else if($search_cmp == "gte")
	  $inc = "<= '".$search_txt."'";   
    else if((($search_cmp == "eq") || ($search_cmp == "not_eq")) && !in_array($search_fld,$idfields))
	  $inc = "= '".$search_txt."'"; 
    else if($search_cmp == "lte")
	  $inc = ">= '".$search_txt."'";
    else if($search_cmp == "lt")
	  $inc = "> '".$search_txt."'";
    else if(in_array($search_fld,$idfields) || (substr($search_fld,0,9) == "sidattrib") || (substr($search_fld,0,9) == "sidfeatur"))
	{ if($search_fld == "cl.id_category")
	  { $search_txt = preg_replace('/[^0-9,s]*/',"",$search_txt); /* an "s" behind the category number means "with subcategories" */
		if(strpos($search_txt,"s"))
		{ $frags = explode(",",$search_txt);
		  $catfrags = array();
		  foreach($frags AS $frag)
	      { if(stripos($frag,'s')) /* "6s" means category 6 with subcategories */
		      get_subcats(str_replace('s','',$frag), $catfrags); /* this function will place the results in the categories array */
	        else if($frag != 0)
		      $catfrags[] = $frag;
	      }
	      $search_txt = implode(",",$catfrags);
		}
	  }
	  else
	    $search_txt = preg_replace('/[^0-9,]*/',"",$search_txt);
	  if(strlen($search_txt) > 0) /* avoid mysql error */
	    $inc = " IN (".$search_txt.")";
	  else 
		 $inc = " IN (0)"; 
	}
    else   /* default = "in": also for "not_in" */
	  $inc = "like '%".$search_txt."%'";
    if($search_fld == "main product fields")
    { $wheretext .= " AND ".$nottext." (p.reference ".$inc." or pl.name ".$inc." or pl.description ".$inc."  or pl.description_short ".$inc;
      $wheretext .= " OR (m.name IS NOT NULL AND m.name ".$inc.")";
      if(is_numeric($search_txt) && (intval($search_txt) !=0)) $wheretext .= " or p.id_product='".$search_txt."' ";
	  $wheretext .= ") ";
    }  
    else if($search_fld == "accessories")
    { $wheretext .= " AND ".$nottext." EXISTS(SELECT NULL FROM ". _DB_PREFIX_."accessory ac WHERE ac.id_product_1 = p.id_product AND ac.id_product_2 ".$inc.")";
    }
    else if(($search_fld == "ps.id_category_default") || ($search_fld == "p.id_product"))
      $wheretext .= " AND ".$nottext.$search_fld." ".$inc." ";
    else if ($search_fld == "cr.name")
	  $wheretext .= " AND ".$nottext." EXISTS(SELECT NULL FROM ". _DB_PREFIX_."product_carrier pc LEFT JOIN ". _DB_PREFIX_."carrier cr ON cr.id_reference=pc.id_carrier_reference AND cr.deleted=0 WHERE pc.id_product = p.id_product AND cr.name ".$inc.")";
    else if ($search_fld == "tg.name")
    { $wheretext .= " AND ".$nottext." EXISTS (SELECT NULL FROM ". _DB_PREFIX_."tag tg";
      $wheretext .= " LEFT JOIN ". _DB_PREFIX_."product_tag pt ON pt.id_tag=tg.id_tag WHERE tg.name ".$inc." AND p.id_product=pt.id_product AND tg.id_lang='".$id_lang."') ";
    }
    else if ($search_fld == "p.indexed")
    { $wheretext .= " AND sw.word ".$nottext." ".$inc;
    }
    else if($search_fld == "su.name")
	  $wheretext .= " AND ".$nottext." EXISTS(SELECT NULL FROM ". _DB_PREFIX_."product_supplier psu LEFT JOIN ". _DB_PREFIX_."supplier su ON psu.id_supplier=su.id_supplier WHERE psu.id_product = p.id_product AND su.name ".$inc.")";
    else if($search_fld == "priceVAT")
	  $wheretext .= " AND ".$nottext." (ROUND(((rate/100)+1)*ps.price,2) ".$inc.")";
    else if($search_fld == "w.id_warehouse")
    { $wheretext .= " AND (".$nottext." EXISTS(SELECT NULL FROM ". _DB_PREFIX_."warehouse_product_location wpl LEFT JOIN ". _DB_PREFIX_."warehouse w ON wpl.id_warehouse=w.id_warehouse WHERE wpl.id_product = p.id_product AND w.name ".$inc.")";
	 $wheretext .= " OR ".$nottext." EXISTS(SELECT NULL FROM ". _DB_PREFIX_."stock st LEFT JOIN ". _DB_PREFIX_."warehouse w ON st.id_warehouse=w.id_warehouse WHERE st.id_product = p.id_product AND w.name ".$inc."))";
    }
    else if($search_fld == "cl.id_category")
    { $wheretext .= " AND ".$nottext." EXISTS(SELECT NULL FROM ". _DB_PREFIX_."category_product cpp WHERE cpp.id_product = p.id_product AND cpp.id_category ".$inc.")";
    }
    else if($search_fld == "combinations")  /* combinations */
    { $wheretext .= " AND ".$nottext." EXISTS(SELECT NULL FROM 
   ". _DB_PREFIX_."product_attribute pa LEFT JOIN ". _DB_PREFIX_."product_attribute_combination pac 
   ON pa.id_product_attribute=pac.id_product_attribute LEFT JOIN ". _DB_PREFIX_."attribute_lang al
   ON pac.id_attribute = al.id_attribute WHERE pa.id_product=p.id_product AND al.name ".$inc.")";
    }
    else if($search_fld == "p.reference") /* check here also for supplier reference */
    { $wheretext .= " AND (".$nottext.$search_fld." ".$inc." ";
   	  $wheretext .= " OR ".$nottext." EXISTS(SELECT NULL FROM ". _DB_PREFIX_."product_supplier psu WHERE psu.id_product = p.id_product AND psu.product_supplier_reference ".$inc."))";
    }
    else if(substr($search_fld,0,7) == "sattrib") /* attribute search */
    { $id_attribute_group = substr($search_fld,7);
      $wheretext .= " AND EXISTS(SELECT NULL FROM ". _DB_PREFIX_."product_attribute pa 
   LEFT JOIN ". _DB_PREFIX_."product_attribute_combination pac ON pa.id_product_attribute=pac.id_product_attribute 
   LEFT JOIN ". _DB_PREFIX_."attribute_lang al ON pac.id_attribute = al.id_attribute 
   LEFT JOIN ". _DB_PREFIX_."attribute atr ON pac.id_attribute = atr.id_attribute 
   WHERE pa.id_product=p.id_product AND atr.id_attribute_group=".$id_attribute_group." AND ".$nottext." (al.name ".$inc."))";
    }
    else if(substr($search_fld,0,9) == "sidattrib") /* attribute id search */
    { $id_attribute_group = substr($search_fld,9);
      $wheretext .= " AND EXISTS(SELECT NULL FROM ". _DB_PREFIX_."product_attribute pa 
   LEFT JOIN ". _DB_PREFIX_."product_attribute_combination pac ON pa.id_product_attribute=pac.id_product_attribute 
   LEFT JOIN ". _DB_PREFIX_."attribute atr ON pac.id_attribute = atr.id_attribute 
   WHERE pa.id_product=p.id_product AND atr.id_attribute_group=".$id_attribute_group." AND ".$nottext."  (atr.id_attribute ".$inc."))";
    }
    else if(substr($search_fld,0,7) == "sfeatur") /* feature search */
    { $id_feature = substr($search_fld,7);
      $wheretext .= " AND EXISTS(SELECT NULL FROM ". _DB_PREFIX_."feature_product fp 
   LEFT JOIN ". _DB_PREFIX_."feature_value fv ON fp.id_feature_value=fv.id_feature_value 
   LEFT JOIN ". _DB_PREFIX_."feature_value_lang fvl ON fv.id_feature_value=fvl.id_feature_value
   WHERE fp.id_product=p.id_product AND fp.id_feature=".$id_feature." AND ".$nottext." (fvl.value ".$inc."))";
    }
    else if(substr($search_fld,0,9) == "sidfeatur") /* feature search */
    { $id_feature = substr($search_fld,9);
      $wheretext .= " AND EXISTS(SELECT NULL FROM ". _DB_PREFIX_."feature_product fp 
   LEFT JOIN ". _DB_PREFIX_."feature_value fv ON fp.id_feature_value=fv.id_feature_value 
   WHERE fp.id_product=p.id_product AND fp.id_feature=".$id_feature." AND ".$nottext." (fv.id_feature_value ".$inc."))";
    }
    else if(($search_fld != "discount") && ($search_fld != "virtualp") && ($search_fld != "custFlds"))
      $wheretext .= " AND ".$nottext.$search_fld." ".$inc." ";
   }
   else /* $search_txt == "" */
   { 
     $nottexta = ""; /* here we use a different logic - so a different variable */
     if(($search_cmp == "not_eq") || ($search_cmp == "!in"))
	   $nottexta = "NOT";
     if($search_fld == "su.name")      /* supplier: this works when search_txt field is empty */
	   $wheretext .= " AND ".$nottexta." EXISTS(SELECT NULL FROM ". _DB_PREFIX_."product_supplier psu WHERE psu.id_product = p.id_product)";
     else if($search_fld == "combinations") /* combinations: this works when search_txt field is empty */
	   $wheretext .= " AND ".$nottexta." EXISTS(SELECT NULL FROM ". _DB_PREFIX_."product_attribute pax WHERE pax.id_product = p.id_product)";
     else if($search_fld == "cr.name")      /* carriers: this works when search_txt field is empty */
	   $wheretext .= " AND ".$nottexta." EXISTS(SELECT NULL FROM ". _DB_PREFIX_."product_carrier pcx WHERE pcx.id_product = p.id_product)";
     else if($search_fld == "m.name")      /* manufacturers: this works when search_txt field is empty */
	 { $wheretext .= " AND ".$nottexta." p.id_manufacturer=0";
	 }
     else if($search_fld == "pl.description") 
	   $wheretext .= " AND ".$nottexta." EXISTS(SELECT NULL FROM ". _DB_PREFIX_."product_lang plx WHERE plx.id_product = p.id_product AND plx.id_lang=".$id_lang." AND plx.description='')";
     else if($search_fld == "pl.description_short") 
	   $wheretext .= " AND ".$nottexta." EXISTS(SELECT NULL FROM ". _DB_PREFIX_."product_lang plx WHERE plx.id_product = p.id_product AND plx.id_lang=".$id_lang." AND plx.description_short='')";
     else if(substr($search_fld,0,7) == "sattrib")  /* attribute search */
     { $id_attribute_group = substr($search_fld,7);
       $wheretext .= " AND ".$nottexta." EXISTS(SELECT NULL FROM ". _DB_PREFIX_."product_attribute pa 
   LEFT JOIN ". _DB_PREFIX_."product_attribute_combination pac ON pa.id_product_attribute=pac.id_product_attribute 
   LEFT JOIN ". _DB_PREFIX_."attribute_lang al ON pac.id_attribute = al.id_attribute 
   LEFT JOIN ". _DB_PREFIX_."attribute atr ON pac.id_attribute = atr.id_attribute 
   WHERE pa.id_product=p.id_product AND atr.id_attribute_group=".$id_attribute_group.")";
     }
    else if(substr($search_fld,0,7) == "sfeatur") /* feature search */
    { $id_feature = substr($search_fld,7);
      $wheretext .= " AND ".$nottexta." EXISTS(SELECT NULL FROM ". _DB_PREFIX_."feature_product fp 
   LEFT JOIN ". _DB_PREFIX_."feature_value fv ON fp.id_feature_value=fv.id_feature_value 
   LEFT JOIN ". _DB_PREFIX_."feature_value_lang fvl ON fv.id_feature_value=fvl.id_feature_value
   WHERE fp.id_product=p.id_product AND fp.id_feature=".$id_feature.")";
    }
  }
  /* finally three fields where it doesn't matter whether search_txt is filled */
  if($search_fld == "discount") /* this works also when search_txt field is empty */
	 $wheretext .= " AND ".$nottext." EXISTS(SELECT NULL FROM ". _DB_PREFIX_."specific_price sp WHERE sp.id_product = p.id_product)";
  if($search_fld == "custFlds") /* this works also when search_txt field is empty */ 
	 $wheretext .= " AND ".$nottext." EXISTS(SELECT NULL FROM ". _DB_PREFIX_."customization_field cf WHERE cf.id_product = p.id_product)";
  if($search_fld == "virtualp")
	 $wheretext .= " AND ".$nottext." (p.is_virtual = 1)";
} /* end of for x=1; x<=3; x++; search block loop */

/* Note: we start with the query part after "from". First we count the total and then we take 100 from it */
/* DISTINCT is for when "with subcats" results in more than one occurence */
$queryterms = "DISTINCT p.*,pl.*,ps.*, cl.name AS catname,p.id_product AS ptest,pl.id_product AS pltest";
$queryterms .=  ", cl.link_rewrite AS catrewrite, pld.name AS originalname, s.quantity, s.depends_on_stock";

$query = " FROM ". _DB_PREFIX_."product_shop ps LEFT JOIN ". _DB_PREFIX_."product p ON p.id_product=ps.id_product";
$query.=" LEFT JOIN ". _DB_PREFIX_."product_lang pl ON pl.id_product=p.id_product and pl.id_lang='".$id_lang."' AND pl.id_shop='".$id_shop."'";
$query.=" LEFT JOIN ". _DB_PREFIX_."product_lang pld ON pld.id_product=p.id_product and pld.id_lang='".$def_lang."' AND pld.id_shop='".$id_shop."'"; /* This gives the name in the shop language instead of the selected language */
$query.=" LEFT JOIN ". _DB_PREFIX_."category_lang cl ON cl.id_category=ps.id_category_default AND cl.id_lang='".$id_lang."' AND cl.id_shop = '".$id_shop."'";
if($share_stock == 0)
  $query.=" LEFT JOIN ". _DB_PREFIX_."stock_available s ON s.id_product=p.id_product AND s.id_shop = '".$id_shop."' AND id_product_attribute='0'";
else
  $query.=" LEFT JOIN ". _DB_PREFIX_."stock_available s ON s.id_product=p.id_product AND s.id_shop_group = '".$id_shop_group."' AND id_product_attribute='0'";

if ($input['order']=="id_product") $order="p.id_product";
else if ($input['order']=="name") $order="pl.name";
else if ($input['order']=="position") $order="cp.position"; /* later to be refined */
else if ($input['order']=="VAT") $order="t.rate";
else if ($input['order']=="price") $order="ps.price";
else if ($input['order']=="active") $order="ps.active";
else if ($input['order']=="availorder") $order="ps.available_for_order,ps.show_price";
else if ($input['order']=="pack_stock_type") $order="ps.pack_stock_type";
else if ($input['order']=="shipweight") $order="p.weight";
else if ($input['order']=="date_upd") $order="p.date_upd";
else if ($input['order']=="image")  /* sorting on image makes only sense to get the products without an image */
{  $order="i.cover";
   $queryterms .= ",i.id_image, i.cover";
   $query.=" LEFT JOIN ". _DB_PREFIX_."image i ON i.id_product=p.id_product and i.cover=1";
}
else $order = $input['order'];

if ($id_category != 0)
{ $categories = array();
  if(isset($input['subcats']))
    get_subcats($id_category, $categories);
  else 
    $categories = array($id_category);
  $cats = join(',',$categories);
  
  if(sizeof($categories) == 1)
  { $query .= " LEFT JOIN "._DB_PREFIX_."category_product cp ON p.id_product=cp.id_product";
	$wheretext .= " AND cp.id_category='".$cats."'";
  }
  else
  { 
   $query .= " LEFT JOIN "._DB_PREFIX_."category_product cp ON p.id_product=cp.id_product";
   $query .= " INNER JOIN (SELECT MIN(id_category) AS mincat,id_product,position FROM ". _DB_PREFIX_."category_product WHERE id_category IN (".$cats.") GROUP BY id_product) cpx ON cpx.mincat=cp.id_category and cpx.id_product=p.id_product";
  }
}
else
{ $query .= " LEFT JOIN ". _DB_PREFIX_."category_product cp ON ps.id_category_default=cp.id_category AND p.id_product=cp.id_product";
}

if (($order=="cp.position") && (($id_category == 0) || (sizeof($categories)>1)))
{ $query .= " LEFT JOIN ". _DB_PREFIX_."category c ON c.id_category=cp.id_category";
  $order = "c.nleft,cp.position";
}

if(in_array("virtualp", $input["fields"]))
{ $query.=" LEFT JOIN ". _DB_PREFIX_."product_download pd ON pd.id_product=p.id_product";
  $queryterms .= ", filename, display_filename, date_expiration, nb_days_accessible, nb_downloadable, pd.active AS dl_active, is_shareable";
}

if(in_array("manufacturer", $input["fields"]) || $manufacturer_needed)
{ $query.=" LEFT JOIN ". _DB_PREFIX_."manufacturer m ON m.id_manufacturer=p.id_manufacturer";
  $queryterms .= ",m.name AS manufacturer ";
}

if((($input["search_fld1"]=="p.indexed") && ($input["search_txt1"]!=""))
  OR (($input["search_fld2"]=="p.indexed") && ($input["search_txt2"]!=""))
  OR (($input["search_fld3"]=="p.indexed") && ($input["search_txt3"]!="")))
{ $query .= " LEFT JOIN "._DB_PREFIX_."search_index si ON si.id_product=ps.id_product";
  $query .= " LEFT JOIN "._DB_PREFIX_."search_word sw ON si.id_word=sw.id_word AND ";
  $query .= " sw.id_shop=".$id_shop." AND sw.id_lang=".$id_lang;
}

if(in_array("out_of_stock", $input["fields"]))
{ $queryterms .= ", s.out_of_stock AS out_of_stock";
}

if(in_array("accessories", $input["fields"]))
{ $query.=" LEFT JOIN ( SELECT GROUP_CONCAT(id_product_2) AS accessories, id_product_1 FROM "._DB_PREFIX_."accessory GROUP BY id_product_1 ) a ON a.id_product_1=p.id_product";
  $queryterms .= ", accessories";
}

if(in_array("position", $input["fields"]))
{ $queryterms .= ", position";
}
$taxfields = array("priceVAT","t.rate","ps.id_tax_rules_group");
if(($discount_included == "1") || in_array($input["order"],$taxfields) || (in_array($input["search_fld1"],$taxfields)  == "VAT") || (in_array($input["search_fld2"],$taxfields)  == "VAT") || (in_array($input["search_fld3"],$taxfields)  == "VAT") || ((isset($input['combisearch_fld'])) AND ($input['combisearch_fld'] == "priceVAT")))
//if(1)
{ $queryterms .= ", t.rate";
  $query.=" LEFT JOIN ". _DB_PREFIX_."tax_rule tr ON tr.id_tax_rules_group=ps.id_tax_rules_group AND tr.id_country='".$id_country."' AND tr.id_state='0'";
  $query.=" LEFT JOIN ". _DB_PREFIX_."tax t ON t.id_tax=tr.id_tax";
}

$statres = dbquery("SELECT id_page_type FROM ". _DB_PREFIX_."page_type WHERE name='product' OR name='product.php'");
$pagetypes = array();
while ($statrow=mysqli_fetch_array($statres))
	$pagetypes[] = $statrow['id_page_type'];
//(ROUND(((t.rate/100)+1)*price,2)= '8.56')'
/* first filter: only relevant products */
if((($input['combisearch_txt'] != "") && ($input['combisearch_fld'] !="")) || ($attributea !="0") || ($attributeb !="0"))
{ if(($input['combisearch_txt'] != "") && ($input['combisearch_fld'] !=""))
  { $csnottext = "";
	if ($input['combisearch_fld'] == "priceVAT")
	  $fld = "(ROUND(((t.rate/100)+1)*price,2))";
	else
      $fld = preg_replace('/\s/',"",$input['combisearch_fld']);
    if($input['combisearch_cmp'] == "gt")
	  $csinc = "< '".$combisearch_txt."'";
    else if($input['combisearch_cmp'] == "gte")
	  $csinc = "<= '".$combisearch_txt."'";   
    else if(($input['combisearch_cmp'] == "eq") || ($input['combisearch_cmp'] == "not_eq"))
	  $csinc = "= '".$combisearch_txt."'"; 
    else if($input['combisearch_cmp'] == "lte")
	  $csinc = ">= '".$combisearch_txt."'";
    else if($input['combisearch_cmp'] == "lt")
	  $csinc = "> '".$combisearch_txt."'";
    else   /* default = "in": also for "not_in" */
	  $csinc = " like '%".$combisearch_txt."%'";
    if(($input['combisearch_cmp'] == "not_in") || ($input['combisearch_cmp'] == "not_eq"))
	   $csnottext = " NOT ";
    $wheretext .= ' AND EXISTS (select null from '._DB_PREFIX_.'product_attribute';
    $wheretext .= ' WHeRE id_product=p.id_product AND '.$csnottext.$fld.$csinc.')';
  }
  if($attributea !="0")
  { $wheretext .= ' AND EXISTS(SELECT NULL FROM '._DB_PREFIX_.'product_attribute_combination paca';
	$wheretext .= ' LEFT JOIN '._DB_PREFIX_.'product_attribute paa ON paa.id_product_attribute=paca.id_product_attribute';
	$wheretext .= ' LEFT JOIN '._DB_PREFIX_.'product_attribute_shop pasa ON paca.id_product_attribute=pasa.id_product_attribute';
	$wheretext .= ' WHERE paca.id_attribute='.$attributea.' AND paa.id_product=p.id_product AND pasa.id_shop="'.$id_shop.'")';
  }
  if($attributeb !="0")
  { $wheretext .= ' AND EXISTS(SELECT NULL FROM '._DB_PREFIX_.'product_attribute_combination pacb';
	$wheretext .= ' LEFT JOIN '._DB_PREFIX_.'product_attribute pab ON pab.id_product_attribute=pacb.id_product_attribute';
	$wheretext .= ' LEFT JOIN '._DB_PREFIX_.'product_attribute_shop pasb ON pacb.id_product_attribute=pasb.id_product_attribute';
	$wheretext .= ' WHERE pacb.id_attribute='.$attributeb.' AND pab.id_product=p.id_product AND pasb.id_shop="'.$id_shop.'")';
  }
}
else
{ /* check whether the product has attribute combinations */
  $wheretext .= ' AND exists(SELECT * FROM '._DB_PREFIX_.'product_attribute pa';
  $wheretext .= ' LEFT JOIN '. _DB_PREFIX_.'product_attribute_shop pas on pa.id_product_attribute=pas.id_product_attribute AND pas.id_shop="'.$id_shop.'"';
  $wheretext .= ' WHERE pa.id_product=p.id_product)';
}

$activequery = "SELECT COUNT(DISTINCT p.id_product) AS rcount ".$query." WHERE ps.active='1' AND ps.id_shop='".$id_shop."' ".$wheretext;
$res=dbquery($activequery);
$row = mysqli_fetch_array($res);
$activerecs = $row['rcount'];

$query.=" WHERE ps.id_shop='".$id_shop."' ".$wheretext;

  if(in_array($order, $statfields))
  { $ordertxt = $statz[array_search($order, $statfields)];
  }
  else
    $ordertxt = str_replace(" ","",$order);
  /* GROUP BY p.id_product is for "With subcats" when the products is in more than one of the involved categories */
  $query .= " GROUP BY p.id_product ORDER BY ".$ordertxt." ".$rising." LIMIT ".$startrec.",".$numrecs;
  
  $query= "select SQL_CALC_FOUND_ROWS ".$queryterms.$query; /* note: you cannot write here t.* as t.active will overwrite p.active without warning */
  $res=dbquery($query);
  $numrecs3 = mysqli_num_rows($res);
  $res2=dbquery("SELECT FOUND_ROWS() AS foundrows");
  $row2 = mysqli_fetch_array($res2);
  $numrecs2 = $row2['foundrows'];
//  echo $query;
  
/* section 6: write http header */
    header('Content-Description: File Transfer');
    header('Content-Type: application/octet-stream');
    header('Content-Disposition: attachment; filename=product-'.date('Y-m-d-Gis').'.csv');
    header('Expires: 0');
    header('Cache-Control: must-revalidate');
    header('Pragma: public');

/* section 7: write csv headers */
  if($input['separator'] == "comma")
  { $separator = ",";
	$subseparator = ";";
  }
  else 
  { $separator = ";";
	$subseparator = ",";
  }
  $csvline = array();  // array for the fputcsv function
  
/* section 20: The main table: the column headers */
 	$csvline[] = "id_product";
	$csvline[] = "id_product_attribute";
    for($i=2; $i<sizeof($infofields); $i++)
    { if($infofields[$i][0] == "supplier")
      { $csvline[] = "supplier";
	    $csvline[] = "supplier reference";
	    $csvline[] = "supplier price";  
	  }
	  else if($infofields[$i][0] == "category")	
      { $csvline[] = "categories";
	    $csvline[] = "category ids";
	  }
	  else if($infofields[$i][0] == "discount")
      { $csvline[] = "discount amount";
	    $csvline[] = "discount pct";
	    $csvline[] = "discount from";
	    $csvline[] = "discount to";
	  }
	  else if($infofields[$i][0] == "virtualp")
      { $csvline[] = "vp_file";
	    $csvline[] = "vp_exp_date";
	    $csvline[] = "vp_nb_days";
	    $csvline[] = "vp_nb_downloads";
	    $csvline[] = "vp_on";
	    $currentDir = dirname(__FILE__);
        $download_dir = $currentDir."/".$triplepath."download/";
	  }
	  else if($infofields[$i][0] == "redirect")	
      { $csvline[] = "redirect type";
	    $csvline[] = "redirect id";
	  }
	  else if($infofields[$i][0] == "stockflags")	
      { $csvline[] = "depends_on_stock";
	    $csvline[] = "advanced_stock_management";
	  }
	  else if($infofields[$i][0] == "link_rewrite")
      { $csvline[] = "link_rewrite";
	    $csvline[] = "url";
	  }
	  else if($infofields[$i][0] == "VAT")
      { $csvline[] = "VAT group";
	    $csvline[] = "VAT perc";
	  }
	  else if($infofields[$i][0] == "warehousing")
      {// $csvline[] = "id_warehouse";
	    $csvline[] = "warehouse";
	    $csvline[] = "whlocation";	  
	  }
	  else
        $csvline[] = $infofields[$i][0]; // or $infofields[$i][1][0];
    }

    foreach($features AS $key => $feature)
    { $cleanfeature = str_replace('"','_',$feature);
      if (in_array($cleanfeature, $input["fields"]))
	  { $csvline[] = $feature;
	  } 	
	}
    for($j=0; $j<$numfields; $j++)
    { if($combifields[$j][3] == "supplier")
      { $csvline[] = "supplier"; 
	    $csvline[] = "supplier reference";
	    $csvline[] = "supplier price"; 
	  }
	  else if($combifields[$j][3] == "discount")
      { $csvline[] = "discount amount";
	    $csvline[] = "discount pct";
	    $csvline[] = "discount from";
	    $csvline[] = "discount to";
	  }
	  else
        $csvline[] = $combifields[$j][3];
    }

  $out = fopen('php://output', 'w');
  publish_csv_line($out, $csvline, $separator);

/* section 8: write csv content */
  $x=0;
  $rowcnt = 0;
  $prodcount = 0;
  $legacy_images = get_configuration_value('PS_LEGACY_IMAGES');
  $haserrors = false;
  while (($datarow=mysqli_fetch_array($res)) && ($rowcnt < $maxrows)) 
  { $csvprodline = array();
    if(($datarow['name'] == "") || ($datarow['pltest'] === NULL) || ($datarow['ptest'] === NULL))
	{ $haserrors = true;
	  continue;
	}
	
    /* Note that trid (<tr> id) cannot be an attribute of the tr as it would get lost with sorting */

	for($i=1; $i< sizeof($infofields); $i++)
    { 	  
      if($infofields[$i][0] == "priceVAT")
		$myvalue =  number_format(((($taxrates[$datarow["id_tax_rules_group"]]/100) +1) * $datarow['price']),2, '.', '');
	  else if($infofields[$i][0] == "VAT")
		$myvalue = $taxrates[$datarow['id_tax_rules_group']];
      else if ((!in_array($infofields[$i][0], array("attachmnts","carrier","combinations","customizations",
	  "discount","indexes","supplier","tags","virtualp")))
		  && (!in_array($infofields[$i][0],array("revenue","orders","salescnt","buyers"))))
        $myvalue = $datarow[$infofields[$i][2]];
	
	  /**************************************************************************************************/
	  /* Below the fields are listed alphabetically. Those missing get the default treatment at the end */
      /* id_product is skipped here */
	  if ($infofields[$i][0] == "id_product") continue;
	  else if ($infofields[$i][0] == "accessories")
	  { if($separator == ",")
		  $myvalue = str_replace(',',';',$myvalue);
        $csvprodline[] = $myvalue;
	  }  /* end of accessories */
	  
	  else if ($infofields[$i][0] == "aDeliveryT")  // additional_delivery_times
	  { $squery = "SELECT additional_delivery_times FROM ". _DB_PREFIX_."product WHERE id_product='".$datarow['id_product']."'";
		$sres=dbquery($squery);
		$srow=mysqli_fetch_array($sres);
		if($datarow["additional_delivery_times"] == "1")
			$csvprodline[] = "default info";	
		else if($datarow["additional_delivery_times"] == "2")
			$csvprodline[] = "product info";
		else 
			$csvprodline[] = "none";	
	  }		/* end of aDeliveryT */
	  
	  else if ($infofields[$i][0] == "attachmnts")
      { $cquery = "SELECT a.file_name, a.file, a.mime, l.name, p.id_attachment FROM ". _DB_PREFIX_."product_attachment p";
		$cquery .= " LEFT JOIN ". _DB_PREFIX_."attachment a ON a.id_attachment=p.id_attachment";
	    $cquery .= " LEFT JOIN ". _DB_PREFIX_."attachment_lang l ON a.id_attachment=l.id_attachment AND l.id_lang='".$id_lang."'";
	    $cquery .= " WHERE id_product='".$datarow['id_product']."'";
		$cres=dbquery($cquery);
		$prodline = "";
		$z=0;
		while ($crow=mysqli_fetch_array($cres)) 
		{	if($z++ > 0) $prodline .=  $subseparator;
			$prodline .=  $crow['name'];
	        if(!file_exists($download_dir."/".$crow["file"])) $prodline .=  " (missing)";
			}
	    $csvprodline[] = $prodline;
		mysqli_free_result($cres);
      }	 /* end of attachmnts */
	  
	  else if ($infofields[$i][0] == "availorder")
	  { $available_for_order = $datarow['available_for_order'];
		$show_price = $datarow['show_price'];
		if($available_for_order == 1)
		  $csvprodline[] = $availordertypes[0];
		else if($show_price == 1)
		  $csvprodline[] = $availordertypes[1];
		else
		  $csvprodline[] = $availordertypes[2];
	  }		/* end of availorder */
	  
	  else if ($infofields[$i][0] == "carrier")
      { $cquery = "SELECT id_carrier_reference FROM ". _DB_PREFIX_."product_carrier WHERE id_product='".$datarow['id_product']."' AND id_shop='".$id_shop."' LIMIT 1";
		$cres=dbquery($cquery);
		if(mysqli_num_rows($cres) != 0)
		{ $cquery = "SELECT id_reference, cr.name FROM ". _DB_PREFIX_."product_carrier pc";
		  $cquery .= " LEFT JOIN ". _DB_PREFIX_."carrier cr ON cr.id_reference=pc.id_carrier_reference AND cr.deleted=0";
		  $cquery .= " WHERE id_product='".$datarow['id_product']."' AND id_shop='".$id_shop."' ORDER BY cr.name";
		  $cres=dbquery($cquery);
		  $carriers = array();
		  while ($crow=mysqli_fetch_array($cres)) 
		  { $carriers[] = $crow['name'];
		  }
		  $csvprodline[] = implode($subseparator,$carriers);
		}
		else
		  $csvprodline[] = "";
		mysqli_free_result($cres);
	  }	  /* end of carrier */
	  
      else if ($infofields[$i][0] == "category")
	  { $cquery = "select cp.id_category from ". _DB_PREFIX_."category_product cp";
		$cquery .= " LEFT JOIN ". _DB_PREFIX_."category_lang cl on cp.id_category=cl.id_category AND id_lang='".$id_lang."' AND id_shop='".$id_shop."'";
		$cquery .= " WHERE cp.id_product='".$datarow['id_product']."' ORDER BY id_category";
		$cres=dbquery($cquery);
		$z=0;
		$catnames = $category_names[$myvalue];
		$catids = $myvalue;
		while ($crow=mysqli_fetch_array($cres)) 
		{	if ($crow['id_category'] == $myvalue)
				continue;
			$catnames .= $subseparator.$category_names[$crow['id_category']]; // without the space this won't work: the categories become different fields
			$catids .= $subseparator.$crow['id_category'];
		}
	    $csvprodline[] = $catnames;
	    $csvprodline[] = $catids;
		mysqli_free_result($cres);
	  }
	  else if ($infofields[$i][0] == "combinations")
      { $cquery = "SELECT count(*) AS counter FROM ". _DB_PREFIX_."product_attribute";
	    $cquery .= " WHERE id_product='".$datarow['id_product']."'";
		$cres=dbquery($cquery);
		$crow=mysqli_fetch_array($cres);
		$csvprodline[] = $crow["counter"];
		mysqli_free_result($cres);
      }
      else if ($infofields[$i][0] == "customizations")
	  { $tmp = "";
	    $dquery = "SELECT * FROM ". _DB_PREFIX_."customization_field";
	    $dquery .= " WHERE id_product='".$datarow['id_product']."'";
		if (version_compare(_PS_VERSION_ , "1.7.3", ">="))
		  $dquery .= " AND is_deleted=0";
		$dres=dbquery($dquery);
		while ($drow=mysqli_fetch_array($dres))
		{ if($tmp != "") $tmp .= $subseparator;
		  $equery = "SELECT * FROM ". _DB_PREFIX_."customization_field_lang";
		  $equery .= " WHERE id_customization_field=".$drow["id_customization_field"];
		  if (version_compare(_PS_VERSION_ , "1.6.0.12", ">="))
			$equery .= " AND id_shop=".$id_shop;
		  $eres=dbquery($equery);
		  $custlangs = array();
		  while ($erow=mysqli_fetch_array($eres)) 
		  { $custlangs[$erow["id_lang"]] = $erow["name"];
		  }
		  foreach($langids AS $langid)
		  { if((isset($custlangs[$langid])) && ($custlangs[$langid]!=""))
			  $tmp .= $custlangs[$langid]."-";
		  }
	      if($drow["type"]==0) $tmp .=  'uploadfile';
			else $tmp .=  'textfield';
		  if($drow["required"]) $tmp .=  '-req';
		}
		$csvprodline[] = $tmp;
		mysqli_free_result($dres);
	  }  /* end of customizations */
	  else if ($infofields[$i][0] == "discount")
     { $dquery = "SELECT sp.reduction,sp.reduction_type,sp.from,sp.to";
		$dquery .= " FROM ". _DB_PREFIX_."specific_price sp";
	    $dquery .= " WHERE sp.id_product='".$datarow['id_product']."' AND (sp.id_shop='".$id_shop."' OR sp.id_shop='0') AND (sp.to >= NOW() OR sp.to = '0000-00-00 00:00:00' ) AND sp.id_product_attribute='0'";
		$dquery .= " ORDER BY sp.id_country, sp.id_group, sp.id_customer,sp.id_currency LIMIT 1"; /* order by should put zero's (=all) first */
		$dres=dbquery($dquery);
		if(mysqli_num_rows($dres) > 0)
		{ $drow=mysqli_fetch_array($dres);
		  if($drow["reduction_type"] == "pct")
		  { $csvprodline[] = "0";
		    $csvprodline[] = $drow['reduction'];
		  }
		  else
		  { $csvprodline[] = $drow['reduction'];
			$csvprodline[] = "0";
		  }
		  $csvprodline[] = $drow['from'];
		  $csvprodline[] = $drow['to'];
		}
		else
		{ $csvprodline[] = "";
		  $csvprodline[] = "";
		  $csvprodline[] = "";
		  $csvprodline[] = "";
		}
		mysqli_free_result($dres);
      }  /* end of discount */
      else if ($infofields[$i][0] == "featureEdit")
	  { $fquery = "SELECT fp.*,custom,fvl.value AS fvvalue, fl.name AS feature FROM "._DB_PREFIX_."feature_product fp";
		$fquery .= " LEFT JOIN "._DB_PREFIX_."feature_value fv ON fp.id_feature_value=fv.id_feature_value";
		$fquery .= " LEFT JOIN "._DB_PREFIX_."feature_value_lang fvl ON fp.id_feature_value=fvl.id_feature_value AND fvl.id_lang=".$id_lang;
		$fquery .= " LEFT JOIN "._DB_PREFIX_."feature_lang fl ON fv.id_feature=fl.id_feature AND fl.id_lang=".$id_lang;
		$fquery .= " WHERE fp.id_product='".$datarow['id_product']."'";
		$fres=dbquery($fquery);
		$tmp = "";
		while($frow = mysqli_fetch_assoc($fres))
		{ if($tmp != "") $tmp .= $subseparator;
		  $tmp .= $frow["feature"].": ";
		  if($frow["custom"] == '1')
		    $tmp .= "--".$frow["fvvalue"]."--";
		  else
		    $tmp .= $frow["fvvalue"];
		}
		$csvprodline[] = $tmp;
	  }
      else if ($infofields[$i][0] == "image")
      { $iquery = "SELECT id_image,cover FROM ". _DB_PREFIX_."image WHERE id_product='".$datarow['id_product']."' ORDER BY cover DESC, position";
		$ires=dbquery($iquery);
		$id_image = 0;
		$xx = 0;
		$imsize = mysqli_num_rows($ires);
		$tmp = "";
		while ($irow=mysqli_fetch_array($ires)) 
		{ $tmp .= $imagebase.'img/p'.getpath($irow['id_image']).'/'.$irow['id_image'].'.jpg';
		  $xx++;
		  if($xx < $imsize)
		    $tmp .= $subseparator; // the space is necessary
		}
		$csvprodline[] = $tmp;
		mysqli_free_result($ires);
      }
	  else if ($infofields[$i][0] == "out_of_stock")
	  { $myvalue = $datarow["out_of_stock"];
		if($myvalue == "0") $csvprodline[] = "Deny orders";
		else if($myvalue == "1") $csvprodline[] = "Allow orders";
		else $csvprodline[] = "Default";		
	  }		/* end of out_of_stock */
	  
      else if ($infofields[$i][0] == "pack_stock_type")
      { $csvprodline[] = $packstocktypes[(int)$myvalue];
      }  	/* end of pack_stock_type */
	  
	  else if ($infofields[$i][0] == "position")
	  { $csvprodline[] = $datarow["position"]; 
	  }		/* end of position */
	  
	  else if ($infofields[$i][0] == "quantity")
	  { if($myvalue == "") $myvalue = "0"; /* handle cases when there are no entries in stock_available table */
		$csvprodline[] = $myvalue;
	  }		/* end of quantity */
	  else if ($infofields[$i][0] == "reserved")
	  { $aquery = "SELECT count(*) FROM `". _DB_PREFIX_."product_attribute` WHERE id_product='".$datarow['id_product']."'";
		$ares=dbquery($aquery);
		list($combination_count) = mysqli_fetch_row($ares); 

		if($combination_count == 0)
		{ $rquery = "SELECT reserved_quantity AS reserved FROM ". _DB_PREFIX_."stock_available";
		  $rquery .= " WHERE id_product='".$datarow['id_product']."'";
		  $rres=dbquery($rquery);
		  $rrow=mysqli_fetch_array($rres);
		}
		else
		{ $rquery = "SELECT SUM(reserved_quantity) AS reserved FROM ". _DB_PREFIX_."stock_available";
		  $rquery .= " WHERE id_product='".$datarow['id_product']."' AND id_product_attribute !=0";
		  $rres=dbquery($rquery);
		  $rrow=mysqli_fetch_array($rres);
		}
        $csvprodline[] = $rrow['reserved'];	  
	  }
	  else if ($infofields[$i][0] == "revenue")
      { $csvprodline[] = $datarow['revenue'];
      }	  	/* end of revenue */
	  else if ($infofields[$i][0] == "state")
	  { if($datarow["state"] == "0") $csvprodline[] = "STATE_TEMP";
		else if($datarow["state"] == "1") $csvprodline[] = "STATE_SAVED";
		else $csvprodline[] = "INVALID";
	  }
	  else if ($infofields[$i][0] == "shopz")
      { $squery = "SELECT GROUP_CONCAT(id_shop) AS shops FROM ". _DB_PREFIX_."product_shop";
	    $squery .= " WHERE id_product = '".$datarow['id_product']."' GROUP BY id_product";
		$sres=dbquery($squery);
		$srow=mysqli_fetch_array($sres);
		$csvprodline[] = $srow["shops"];
      }	  	/* end of shops */
	  
	  else if ($infofields[$i][0] == "stockflags")
	  { $csvprodline[] = $datarow['depends_on_stock'];
		$csvprodline[] = $datarow['advanced_stock_management'];
	  }		/* end of stockflags */
	  else if ($infofields[$i][0] == "supplier")
     { $squery = "SELECT p.id_supplier,product_supplier_reference AS reference, 	product_supplier_price_te AS price FROM "._DB_PREFIX_."product_supplier ps";
		$squery .= " LEFT JOIN "._DB_PREFIX_."product p on p.id_supplier=ps.id_supplier";
		$squery .= " WHERE ps.id_product=".$datarow['id_product']." AND ps.id_product_attribute='0' LIMIT 1";
		$sres=dbquery($squery);
		if(mysqli_num_rows($sres) == 0) /* shouldn't happen */
		{ $squery = "SELECT id_supplier,product_supplier_reference AS reference, 	product_supplier_price_te AS price FROM ". _DB_PREFIX_."product_supplier WHERE id_product=".$datarow['id_product']." AND id_product_attribute='0' LIMIT 1";
		  $sres=dbquery($squery);
		}
		if(mysqli_num_rows($sres) > 0)
		{ $srow=mysqli_fetch_array($sres); 
		  $csvprodline[] = $supplier_names[$srow['id_supplier']];
		  $csvprodline[] = $srow['reference'];
		  $csvprodline[] = $srow['price'];	  
		}
		else
		{ $csvprodline[] = "";
		  $csvprodline[] = "";
		  $csvprodline[] = "";
		}
		mysqli_free_result($sres);
      }
	  
	  else if ($infofields[$i][0] == "tags")
      { $tquery = "SELECT pt.id_tag,name FROM ". _DB_PREFIX_."product_tag pt";
		$tquery .= " LEFT JOIN ". _DB_PREFIX_."tag t ON pt.id_tag=t.id_tag AND t.id_lang='".$id_lang."'";
	    $tquery .= " WHERE pt.id_product='".$datarow['id_product']."'";
		$tres=dbquery($tquery);
		$idx = 0;
		$tmp = "";
		while ($trow=mysqli_fetch_array($tres)) 
		{ if($idx++ > 0) $tmp .= $subseparator;
		  $tmp .= $trow["name"];
		  $idx++;
		}
		$csvprodline[] = $tmp;
		mysqli_free_result($tres);
	  } /* end of tags */
	  
	  else if ($infofields[$i][0] == "unitPrice")
	  { $csvprodline[] = round($datarow["price"]/$datarow["unit_price_ratio"],6);
	  }		/* end of unitPrice */
	  
      else if ($infofields[$i][0] == "VAT")
      { $csvprodline[] = $datarow["id_tax_rules_group"];
		$csvprodline[] = (float)$myvalue;
      }	/* end of VAT */
	  
      else if ($infofields[$i][0] == "virtualp")
	  {	$bgcolor = $visvirtual= $visfile = '';
	    if($datarow['is_virtual'] == 0)
		{ $csvprodline[] = "";
		  $csvprodline[] = "";
	      $csvprodline[] = "";
	      $csvprodline[] = "";
	      $csvprodline[] = "0";	 
		}	
		else if($datarow['filename'] != "") 
	    { if(!file_exists($download_dir."/".$datarow["filename"])) 
		    $csvprodline[] = "file missing";
		  else
		    $csvprodline[] = $datarow["display_filename"];		  
		  $csvprodline[] = $datarow["date_expiration"];
	      $csvprodline[] = $datarow["nb_days_accessible"];
	      $csvprodline[] = $datarow["nb_downloadable"];
	      $csvprodline[] = "1";	 
		}
	  }	  	/* end of virtualp */
	  
      else if ($infofields[$i][0] == "warehousing")
      { $wquery = "SELECT w.id_warehouse, w.name AS whname, physical_quantity, usable_quantity, price_te, location";
		$wquery .= " FROM ". _DB_PREFIX_."warehouse w";
	    $wquery .= " LEFT JOIN ". _DB_PREFIX_."stock st on w.id_warehouse=st.id_warehouse AND st.id_product='".$datarow['id_product']."'";
	    $wquery .= " LEFT JOIN ". _DB_PREFIX_."warehouse_product_location wpl on w.id_warehouse=wpl.id_warehouse AND wpl.id_product='".$datarow['id_product']."'";		  
		$wquery .= " WHERE st.id_warehouse IS NOT NULL OR wpl.id_warehouse IS NOT NULL";
		$wquery .= " ORDER BY w.name";
 		$wres=dbquery($wquery);
		$wids = $wnames = array();
		while ($wrow=mysqli_fetch_array($wres)) 
		{ $wnames[] = $wrow["whname"];
		  $wids[] = $wrow["id_warehouse"];
		  $locations[] = $wrow["location"];		  
		}
//		$csvprodline[] = implode(",",$wids);
		$csvprodline[] = implode(",",$wnames);
		$csvprodline[] = implode(",",$locations);		
		mysqli_free_result($wres);
	  }
      else
         $csvprodline[] = $myvalue;
	}
	
	foreach($features AS $key => $feature)
    { if (in_array($feature, $input["fields"]))
	  { $fquery = "SELECT fv.custom,fl.value, fp.id_feature_value FROM ". _DB_PREFIX_."feature_product fp";
	    $fquery.=" LEFT JOIN ". _DB_PREFIX_."feature_value fv ON fp.id_feature_value=fv.id_feature_value";
	    $fquery.=" LEFT JOIN ". _DB_PREFIX_."feature_value_lang fl ON fp.id_feature_value=fl.id_feature_value AND fl.id_lang='".$id_lang."'";
	    $fquery.=" WHERE fp.id_product='".$datarow['id_product']."' AND fv.id_feature='".$key."'";
	    $fres=dbquery($fquery);
	    $first = true;
		$feats = array();
	    while ($frow=mysqli_fetch_array($fres))
	    { $feats[] = $frow['value'];
		}
		$csvprodline[] = implode(",",$feats);	
	  }
	}

/* 22. the main table: the combination data   */
    $aqueryterms = "ps.*, pa.reference, pa.supplier_reference,pa.ean13";
    if (version_compare(_PS_VERSION_ , "1.7.0.0", ">="))
	  $aqueryterms .= ",pa.isbn";
    if (version_compare(_PS_VERSION_ , "1.7.3.0", ">="))
	  $aqueryterms .= ",pa.low_stock_threshold,pa.low_stock_alert";
    if (version_compare(_PS_VERSION_ , "1.7.7.0", ">="))
	  $aqueryterms .= ",pa.mpn";
    if (version_compare(_PS_VERSION_ , "1.7.5", ">=")) 
	  $aqueryterms .= ",s.location"; 
    else
	  $aqueryterms .= ",pa.location"; 
    $aqueryterms .= " ,pi.id_image,pa.upc,s.quantity,GROUP_CONCAT(pi.id_image) AS images";
    $aqueryterms .= " ,s.depends_on_stock, il.legend, positions";
    $aquery = " FROM ". _DB_PREFIX_."product_attribute pa";
    $aquery .= " LEFT JOIN ". _DB_PREFIX_."product_attribute_shop ps on pa.id_product_attribute=ps.id_product_attribute AND ps.id_shop='".$id_shop."'";
    $aquery .= " LEFT JOIN ". _DB_PREFIX_."product_attribute_image pi on pa.id_product_attribute=pi.id_product_attribute ";
    $aquery .= " LEFT JOIN (SELECT pc.id_product_attribute, GROUP_CONCAT(LPAD(at.position,4,'0')) AS positions";
	$aquery .= ", GROUP_CONCAT(CONCAT('x',at.id_attribute,'-')) AS attributes FROM ". _DB_PREFIX_."product_attribute_combination pc";
    $aquery .= " LEFT JOIN ". _DB_PREFIX_."attribute at on pc.id_attribute=at.id_attribute";
    $aquery .= " GROUP BY pc.id_product_attribute";
/*    if(($attributea !="0") || ($attributeb !="0"))
	  $aquery .= " HAVING";
    if($attributea !="0")
	  $aquery .= ' positions LIKE "%x'.$attributea.'-%"';
    if(($attributea !="0") && ($attributeb !="0")) 
	  $aquery .= " AND";
    if($attributeb !="0")
	  $aquery .= ' positions LIKE "%x'.$attributeb.'-%"';
*/	$aquery .= ") px ON px.id_product_attribute=ps.id_product_attribute";
    $aquery .= " LEFT JOIN ". _DB_PREFIX_."image_lang il on il.id_image=pi.id_image AND il.id_lang='".$id_lang."'";
    if($share_stock == 0)
	  $aquery .=" left join ". _DB_PREFIX_."stock_available s on s.id_product_attribute=pa.id_product_attribute AND s.id_shop = '".$id_shop."'";
	else
      $aquery .=" left join ". _DB_PREFIX_."stock_available s on s.id_product_attribute=pa.id_product_attribute AND s.id_shop_group = '".$id_shop_group."'";

if(in_array("revenue", $input["fields"]) OR in_array("salescnt", $input["fields"]) OR in_array("orders", $input["fields"]) OR in_array("buyers", $input["fields"])
	OR ($order=="revenue")OR ($order=="orders") OR ($order=="salescnt") OR ($order=="buyers"))
{ $aquery .= " LEFT JOIN ( SELECT product_id, product_attribute_id, SUM(product_quantity)-SUM(product_quantity_return) AS quantity, ";
  $aquery .= " ROUND(SUM(total_price_tax_incl),2) AS revenue, ";
  $aquery .= " COUNT(DISTINCT d.id_order) AS ordercount, ";
  $aquery .= " count(DISTINCT o.id_customer) AS buyercount FROM ". _DB_PREFIX_."order_detail d";
  $aquery .= " LEFT JOIN ". _DB_PREFIX_."orders o ON o.id_order = d.id_order AND o.id_shop=d.id_shop";
  $aquery .= " WHERE d.id_shop='".$id_shop."'";
  if($input['startdate'] != "")
    $aquery .= " AND TO_DAYS(o.date_add) >= TO_DAYS('".$input['startdate']."')";
  if($input['enddate'] != "")
    $aquery .= " AND TO_DAYS(o.date_add) <= TO_DAYS('".$input['enddate']."')";
  $aquery .= " AND o.valid=1";
  $aquery .= " GROUP BY d.product_id,d.product_attribute_id ) r ON pa.id_product=r.product_id AND pa.id_product_attribute=r.product_attribute_id";
  $aqueryterms .= ", revenue, r.quantity AS salescount, ordercount, buyercount ";
}
    $aqueryfull = "SELECT SQL_CALC_FOUND_ROWS ".$aqueryterms.$aquery;
    $aqueryfull .= " WHERE pa.id_product='".$datarow['id_product']."'";
	if(($input['combisearch_txt'] != "") && ($input['combisearch_fld'] !=""))
	{ if ($input['combisearch_fld'] == "priceVAT")
	    $fld = "(ROUND(((".$datarow['rate']."/100)+1)*pa.price,2))";
	  else
        $fld = "pa.".preg_replace('/\s/',"",$input['combisearch_fld']);
	  $aqueryfull .= ' AnD '.$csnottext.$fld.$csinc;
	}
	if($attributea !="0")
	  $aqueryfull .= ' AND attributes LIKE "%x'.$attributea.'-%"';
    if($attributeb !="0")
	  $aqueryfull .= ' AND attributes LIKE "%x'.$attributeb.'-%"';
    if(isset($blockers) && (sizeof($blockers) > 0))
      $aqueryfull .= " AND NOT EXISTS (SELECT * FROM ". _DB_PREFIX_."product_attribute_combination pc2 WHERE pa.id_product_attribute=pc2.id_product_attribute AND pc2.id_attribute IN ('".implode("','", $blockers)."'))";
    $aqueryfull .= " GROUP BY ps.id_product_attribute";
    $aqueryfull .= " ORDER BY positions";
    $ares=dbquery($aqueryfull);
// echo $aqueryfull."-----<p>";
    
  $VAT_rate = $taxrates[$datarow['id_tax_rules_group']];
  $prodprice = $datarow["price"];
  if(mysqli_num_rows($ares) > 0)
    $prodcount++;
  while (($row=mysqli_fetch_assoc($ares)) && ($rowcnt < $maxrows))
  { $csvline = array();
    $csvline[] = $datarow['id_product'];
	$csvline[] = $row['id_product_attribute'];
	$csvline = array_merge($csvline, $csvprodline);
	
    for($j=0; $j< sizeof($combifields); $j++)
	{   if($combifields[$j][2]==HIDE) $vis='style="display:none"'; else $vis="";
		if($combifields[$j][0] == "combination")
		{ $paquery = "SELECT GROUP_CONCAT(CONCAT(gl.name,': ',l.name) SEPARATOR '=') AS nameblock from ". _DB_PREFIX_."product_attribute pa";
		  $paquery .= " LEFT JOIN ". _DB_PREFIX_."product_attribute_combination c on pa.id_product_attribute=c.id_product_attribute";
		  $paquery .= " LEFT JOIN ". _DB_PREFIX_."attribute a on a.id_attribute=c.id_attribute";
		  $paquery .= " LEFT JOIN ". _DB_PREFIX_."attribute_lang l on l.id_attribute=c.id_attribute AND l.id_lang='".$id_lang."'";
		  $paquery .= " LEFT JOIN ". _DB_PREFIX_."attribute_group_lang gl on gl.id_attribute_group=a.id_attribute_group AND gl.id_lang='".$id_lang."'";
		  $paquery .= " WHERE pa.id_product_attribute='".$row['id_product_attribute']."' GROUP BY pa.id_product_attribute";
		  $pares=dbquery($paquery);
		  $parow = mysqli_fetch_assoc($pares);
		  $labels = explode("=", $parow['nameblock']);
		  sort($labels);
		  $csvline[] = implode($subseparator, $labels);
		}
		else if($combifields[$j][0] == "price")
		  $csvline[] = $row['price'];
		else if($combifields[$j][0] == "priceVAT")
		  $csvline[] = round(($row['price']*(100+$VAT_rate)/100),2);
		else if($combifields[$j][0] == "weight")
		  $csvline[] = $row['weight'];
		else if($combifields[$j][0] == "wholesale_price")
		  $csvline[] = $row['wholesale_price'];
		else if($combifields[$j][0] == "ecotax")
		  $csvline[] = $row['ecotax'];
		else if($combifields[$j][0] == "isbn")
		  $csvline[] = $row['isbn'];
		else if($combifields[$j][0] == "unit_price_impact")
		  $csvline[] = $row['unit_price_impact'];
		else if($combifields[$j][0] == "default_on")
		  $csvline[] = $row['default_on'];
		else if($combifields[$j][0] == "minimal_quantity")
		  $csvline[] = $row['minimal_quantity'];
		else if($combifields[$j][0] == "available_date")
		  $csvline[] = $row['available_date'];
		else if($combifields[$j][0] == "quantity")
		{ if($datarow["depends_on_stock"] == "1")
            $csvline[] = $row['quantity'];	
		  else
		    $csvline[] = $row['quantity'];
		}
		/* below the ps_product_attribute fields */
		else if($combifields[$j][0] == "reference")
		  $csvline[] = $row['reference'];
		else if($combifields[$j][0] == "supplier_reference")
		  $csvline[] = $row['supplier_reference'];
		else if($combifields[$j][0] == "location")
		  $csvline[] = $row['location'];
		else if($combifields[$j][0] == "ean")
		  $csvline[] = $row['ean13'];
		else if($combifields[$j][0] == "upc")
		  $csvline[] = $row['upc'];
		else if($combifields[$j][0] == "quantity")
		  $csvline[] = $row['quantity'];
		else if($combifields[$j][0] == "ls_alert")
		  $csvline[] = $row['low_stock_alert'];	  
		else if($combifields[$j][0] == "ls_threshold")
		  $csvline[] = $row['low_stock_threshold'];	
	    else if($combifields[$j][0] == "discount")
        { $dquery = "SELECT sp.reduction,sp.reduction_type,sp.from,sp.to";
		  $dquery .= " FROM ". _DB_PREFIX_."specific_price sp";
	      $dquery .= " WHERE sp.id_product='".$datarow['id_product']."' AND (sp.id_shop='".$id_shop."' OR sp.id_shop='0') AND (sp.to >= NOW() OR sp.to = '0000-00-00 00:00:00' ) AND sp.id_product_attribute='".$row['id_product_attribute']."'";
		  $dquery .= " ORDER BY sp.id_country, sp.id_group, sp.id_customer,sp.id_currency LIMIT 1"; /* order by should put zero's (=all) first */
		  $dres=dbquery($dquery);
		  if(mysqli_num_rows($dres) > 0)
		  { $drow=mysqli_fetch_array($dres);
		    if($drow["reduction_type"] == "pct")
		    { $csvline[] = "0";
		      $csvline[] = $drow['reduction'];
		    }
		    else
		    { $csvline[] = $drow['reduction'];
			  $csvline[] = "0";
		    }
		    $csvline[] = $drow['from'];
		    $csvline[] = $drow['to'];
		  }
		  else
		  { $csvline[] = "";
		    $csvline[] = "";
		    $csvline[] = "";
		    $csvline[] = "";
		  }
		  mysqli_free_result($dres);
        }  /* end of discount */
		else if($combifields[$j][0] == "supplier")
        { $squery = "SELECT p.id_supplier,product_supplier_reference AS reference, 	product_supplier_price_te AS price FROM "._DB_PREFIX_."product_supplier ps";
		$squery .= " LEFT JOIN "._DB_PREFIX_."product p on p.id_supplier=ps.id_supplier";
		$squery .= " WHERE ps.id_product=".$datarow['id_product']." AND ps.id_product_attribute='".$row['id_product_attribute']."'";
		  $sres=dbquery($squery);
		  if(mysqli_num_rows($sres) > 0)
		  { $srow=mysqli_fetch_array($sres); 
		    $csvline[] = $supplier_names[$srow['id_supplier']];
		    $csvline[] = $srow['reference'];
		    $csvline[] = $srow['price'];	  
		  }
		  else
		  { $csvline[] = "";
		    $csvline[] = "";
		    $csvline[] = "";
		  }
		  mysqli_free_result($sres);
        }
		/* image */
		else if($combifields[$j][0] == "image")
		{ if($separator == ",")
			$row["id_image"] = str_replace(',',';',$row["id_image"]);
		  $csvline[] = $row["id_image"];
		}
		else if($combifields[$j][0] == "buyers")
	    { $csvline[] = $row['buyercount'];
	    }
	    else if($combifields[$j][0] == "orders")
	    { $csvline[] = $row['ordercount'];
	    }
	    else if($combifields[$j][0] == "revenue")		  
	    { $csvline[] = $row['revenue'];
	    }
	    else if($combifields[$j][0] == "salescnt")	  
	    { $csvline[] = $row['salescount'];
	    }
		else 
		{ $csvline[] = $row[$combifields[$j][0]];
		}
	}

    $x++;
	$rowcnt++;
    publish_csv_line($out, $csvline, $separator);
  }
  }
  fclose($out);

/* The code of fputcsv3 comes from a forum where it was claimed to be the source for the PHP function. */
/* I have added the functionality that it will always escape strings with a semicolon or comma */
function publish_csv_line($out, $csvline, $separator)
{ fputcsv3($out, $csvline, $separator);
}
  
/* if fputcsv doesn't work this can be used as alternative. It is one of the options mentioned in the comment section of php.net for fputcsv() */
function fputcsv2 ($fh, array $fields, $delimiter = ',', $enclosure = '"', $mysql_null = false) { 
    $delimiter_esc = preg_quote($delimiter, '/'); 
    $enclosure_esc = preg_quote($enclosure, '/'); 

    $output = array(); 
    foreach ($fields as $field) { 
        if ($field === null && $mysql_null) { 
            $output[] = 'NULL'; 
            continue; 
        } 

        $output[] = preg_match("/(?:${delimiter_esc}|${enclosure_esc}|\s)/", $field) ? ( 
            $enclosure . str_replace($enclosure, $enclosure . $enclosure, $field) . $enclosure 
        ) : $field; 
    } 

    fwrite($fh, join($delimiter, $output) . "\n"); 
} 


  function fputcsv3(&$handle, $fields = array(), $delimiter = ',', $enclosure = '"') {
    $str = '';
    $escape_char = '\\';
    foreach ($fields as $value) {
      if (strpos($value, $delimiter) !== false ||
          strpos($value, $enclosure) !== false ||
          strpos($value, "\n") !== false ||
          strpos($value, "\r") !== false ||
          strpos($value, "\t") !== false ||
          strpos($value, ";") !== false ||
          strpos($value, ",") !== false ||          
		  strpos($value, ' ') !== false) {
        $str2 = $enclosure;
        $escaped = 0;
        $len = strlen($value);
        for ($i=0;$i<$len;$i++) {
          if ($value[$i] == $escape_char) {
            $escaped = 1;
          } else if (!$escaped && $value[$i] == $enclosure) {
            $str2 .= $enclosure;
          } else {
            $escaped = 0;
          }
          $str2 .= $value[$i];
        }
        $str2 .= $enclosure;
        $str .= $str2.$delimiter;
      } else {
        $str .= $value.$delimiter;
      }
    }
    $str = substr($str,0,-1);
    $str .= "\n";
    return fwrite($handle, $str);
  }