<?php
require 'Slim/Slim.php';

$app = new Slim();
$app->contentType('application/json; charset=utf-8');

//$app->get('/token/:username/:password', 'getToken');
$app->post('/token', 'getToken');
$app->get('/shipping/:cp/:branch/:kilos', 'getShipping');
$app->get('/order/status/:order', 'getOrderStatus');
$app->post('/orden', 'addOrder');
$app->get('/sucursales', 'getSucursales');
/*$app->get('/sucursales', function() {
$sucursales["sucursales"] = array(
			"10" => "México D.F.",
			"20" => "Puebla",
			"30" => "Merida",
			"40" => "Monterrey",
			"50" => "León",
			"55" => "Querétaro",
			"60" => "Tijuana",
			"70" => "Hermosillo",
			"80" => "Guadalajara"		
		);
	
	echo json_encode($sucursales);
});*/

$app->get('/wines/search/:query', 'findByName');



$app->get('/categoria/:id',	'getCategory');
$app->get('/categorias', 'getCategories_new');
$app->get('/categorias/parent_id/:id', 'getCategoriesByParent');
$app->get('/categorias_per_client/:id', 'getCategoriasPorCliente');

$app->get('/marca/:id',	'getMarca');
$app->get('/marcas', 'getBrands_new');
$app->get('/marcas_', 'getBrands_new');
$app->get('/marcas_per_client/:id', 'getMarcasPorCliente');

$app->get('/fichas', 'getFichas');
$app->get('/ficha/:sku', 'getFicha');
$app->get('/fichas/id_fabricante/:id', 'getFichasByManufacturer');
$app->get('/fichas/id_subcategoria/:id', 'getFichasBySubCategory');

$app->get('/imagenes', 'getImagenes');
$app->get('/imagen/:sku', 'getImagen');
$app->get('/imagenes/id_fabricante/:id', 'getImagenesByManufacturer');
$app->get('/imagenes/id_subcategoria/:id', 'getImagenesBySubCategory');

$app->get('/producto/:sku',	'getProducto');
$app->get('/productos', 'getProducts_new');
$app->get('/productos/id_fabricante/:id', 'getProductosByManufacturer');
$app->get('/productos/id_subcategoria/:id', 'getProductosBySubCategory');
$app->get('/productos_per_client/:id', 'getProductosPorCliente');

$app->get('/productos.recientes', 'getProductsL');
$app->get('/stock/:sku', 'getStockBySku');
$app->get('/stock', 'getStock');
$app->get('/precios', 'getStock');
$app->get('/stock.recientes', 'getStockL');
$app->put('/wines/:id', 'updateWine');
$app->get('/image_exist/:sku',	'getImageExist');
$app->delete('/wines/:id',	'deleteWine');

$app->run();
function validateIp($ip){
	$db = getConnection();
	$query = $db->prepare("SELECT * FROM tokens WHERE ip_address =:ip_address");
	$query->bindParam("ip_address", $ip);	
	$query->execute();
	if($query->rowCount()){
		return true;
	}else{
		$error_response["result"] = "error";
		$error_response["text"] = "ip address not allowed";
		echo json_encode($error_response);
		die();
	}
	$db = null;
}
function getImageExist($sku = NULL) {

	$images = array();
	$files = glob('../../images/*.jpg');
	foreach ($files as $file) {
		$foto = str_replace("../../images/", "", $file);
		$imagen = $foto;
		$foto = explode(".", $foto);


		$pos = strpos($foto[0], $sku);

		if ($pos === false) {
		   
		} else {
		   $images[] = "https://mx.bdicentralserver.com/images/".$imagen;
		}
	}
	sort($images);
	echo json_encode($images);
}

function getToken() {
	$request = Slim::getInstance()->request();
	$auth = json_decode($request->getBody());
	if (!empty($auth)) {
		$token_response = array();	
		if ($auth->username=="digibyte" && $auth->password=="4DdiGiByt.1") {
			$token = md5(uniqid(microtime(), true));
	  		$token_time = time();
	  		$_SESSION['csrf'][$auth->password.'_token'] = array('token'=>$token, 'time'=>$token_time); 
			$token_response["result"] = "success";
			$token_response["token"] = $token;
			$token_response["ip_address_allowed"] = $_SERVER['REMOTE_ADDR'];
			$sql1 = "INSERT INTO tokens (username,token,ip_address) VALUES (:username,:token,:ip_address)";
			$db1 = getConnection();
			$stmt1 = $db1->prepare($sql1);  
			$stmt1->bindParam("username", $auth->username);
			$stmt1->bindParam("token", $token);
			$stmt1->bindParam("ip_address", $_SERVER['REMOTE_ADDR']);
			$stmt1->execute();		
		}else{
			$token_response["result"] = "error";
			$token_response["text"] = "invalid username and password";
		}
	}else{
			$token_response["result"] = "error";
			$token_response["text"] = "invalid username and password";

	}
	echo json_encode($token_response);	
}
/*function getToken($username,$password) {
	$token_response = array();	
	if ($username=="telcomx" && $password=="e1074fbe489c67c.1") {
		$token = md5(uniqid(microtime(), true));
  		$token_time = time();
  		$_SESSION['csrf'][$password.'_token'] = array('token'=>$token, 'time'=>$token_time); 
		$token_response["result"] = "success";
		$token_response["token"] = $token;
		$token_response["ip_address_allowed"] = $_SERVER['REMOTE_ADDR'];
		$sql1 = "INSERT INTO tokens (username,token,ip_address) VALUES (:username,:token,:ip_address)";
		$db1 = getConnection();
		$stmt1 = $db1->prepare($sql1);  
		$stmt1->bindParam("username", $username);
		$stmt1->bindParam("token", $token);
		$stmt1->bindParam("ip_address", $_SERVER['REMOTE_ADDR']);
		$stmt1->execute();		
	}else{
		$token_response["result"] = "error";
		$token_response["text"] = "invalid username and password";
	}
	echo json_encode($token_response);
}*/

function getShipping($cp,$branch,$kilos) {
	validateIp($_SERVER['REMOTE_ADDR']);
	$sql = "SELECT zona,ABS(:cp - cp) AS delta FROM cp_to_zone WHERE branch = :branch ORDER BY delta ASC LIMIT 1";
	try {
		$cp = substr($cp,0,2);
		$cp = "%$cp%"; 
		$db = getConnection();
		$stmt = $db->prepare($sql);  
		$stmt->bindParam("cp",  $cp);
	    $stmt->bindParam("branch", $branch);
		$stmt->execute();
		$postalcode = $stmt->fetchObject();
		
		if ($kilos==0) { $kilos=1;}
		if ($kilos>1 && $kilos<10) { $kilos= $kilos;}
		if ($kilos>10 && $kilos<15) { $kilos=10;}
		if ($kilos>15 && $kilos<20) { $kilos=15;}
		if ($kilos>20 && $kilos<25) { $kilos=20;}
		if ($kilos>25 && $kilos<30) { $kilos=25;}
		if ($kilos>30 && $kilos<35) { $kilos=30;}
		if ($kilos>35 && $kilos<40) { $kilos=35;}
		if ($kilos>40 && $kilos<45) { $kilos=40;}
		if ($kilos>45 && $kilos<50) { $kilos=45;}
		if ($kilos>50 && $kilos<60) { $kilos=50;}
		if ($kilos>60 && $kilos<70) { $kilos=60;}
		if ($kilos>70 && $kilos<80) { $kilos=70;}
		if ($kilos>80 && $kilos<90) { $kilos=80;}
		if ($kilos>90 && $kilos<100) { $kilos=90;}
		if ($kilos>100) { $kilos=100;}
		
		$kilos = round($kilos);
		$sql2 = "SELECT estafeta FROM tarifa WHERE zona = :zona AND peso >= :peso order by fedex asc";
		$stmt2 = $db->prepare($sql2);  
		$stmt2->bindParam("zona",  $postalcode->zona);
	    $stmt2->bindParam("peso", $kilos);		
		$stmt2->execute();
		$tarifa = $stmt2->fetchObject();	    
		$db = null;
		echo json_encode($tarifa); 
	} catch(PDOException $e) {
		echo '{"error":{"text":'. $e->getMessage() .'}}'; 
	}
}
function getOrderStatus($order) {
	validateIp($_SERVER['REMOTE_ADDR']);
								function httpsPost($Url, $strRequest){
									   $ch=curl_init();
									   curl_setopt($ch, CURLOPT_URL, $Url);
									   curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
									   curl_setopt($ch, CURLOPT_POST, 1) ;
									   curl_setopt($ch, CURLOPT_POSTFIELDS, $strRequest);
									   curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0);
									   curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 0);
$headers = array("Content-type: text/xml", "Content-length: " . strlen($strRequest), "Connection: close");
curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);									   
									   $result = curl_exec($ch);
									   curl_close($ch);
									   return $result;
								}
                $string = '<?xml version="1.0" encoding="ISO-8859-1"?>';
                $string.= '<OrderStatusRequest>';
                $string.= '<Version>2.0</Version>';
                $string.= '<TransactionHeader>';
                $string.= '<SenderID>GLOBAL REWARD</SenderID>';
                $string.= '<ReceiverID>INGRAM MICRO</ReceiverID>';
                $string.= '<CountryCode>MX</CountryCode>';
                $string.= '<LoginID>GlReS4gDd</LoginID>';
                $string.= '<Password>JaIFr062217</Password>';
                $string.= '<TransactionID>{A0DEA52B-341F-40C3-9C80-77A4A84F9EB7}</TransactionID>';
                $string.= '</TransactionHeader>';
                $string.= '<OrderHeaderInfo>';
                $string.= '<CustomerPO>'.$order.'</CustomerPO>';
                $string.= '</OrderHeaderInfo>';
                $string.= '</OrderStatusRequest>';
                $url = 'https://newport.ingrammicro.com/MUSTANG';

                $strRequest = utf8_encode($string);
                $doc1 = new DOMDocument();
                $doc1->loadXML($strRequest);
                $OSReq = "statuses/order-status-req.xml";
                $doc1->save("$OSReq");

                $Response = httpsPost($url, $strRequest);
                
                $doc = new DOMDocument();
                $doc->loadXML($Response);
                $OSResp = "statuses/order-status.xml";
                $doc->save("$OSResp");

                $order_status = simplexml_load_file("statuses/order-status.xml");
                $order_information = $order_status->OrderStatusInformation;
                $order_customer_po = $order_information->CustomerPO;
                $response_status = array();
                if ($order_customer_po) {
					$order_info = $order_customer_po->OrderInfo;
	                $order_suffix = $order_info->OrderSuffix;
	                $status = $order_suffix->OrderStatus; 
	                $response_status["status"] = $status;
	                $response_status["order"] = $order;
	                //echo '{"status": ' . $status . ',"order":'.$order.'}'; 
	                echo json_encode($response_status);
                }else{
	                $response_status["status"] = $status;
	                $response_status["order"] = $order;
                	//echo '{"status": "NOT FOUND","order":'.$order.'}';  
                	echo json_encode($response_status);
                } 
               
}

function getSucursales() {
	validateIp($_SERVER['REMOTE_ADDR']);
	$sucursales = array(
			"10" => "México D.F.",
			"20" => "Puebla",
			"30" => "Merida",
			"40" => "Monterrey",
			"50" => "León",
			"55" => "Querétaro",
			"60" => "Tijuana",
			"80" => "Guadalajara"		
		);
	
	//header('Content-Type: application/json');
	echo json_encode($sucursales);
	//http_response_code(200);
}
function getFichas() {
	validateIp($_SERVER['REMOTE_ADDR']);
	$sql = "SELECT SKU AS sku, CONCAT('http://www-1.dmcentralserver.com/fichas/', sku, '.json') as url FROM PricefileMumbai WHERE isactive = 1 AND hasTechNotes = 1";
	try {
		$db = getConnection();
		$stmt = $db->query($sql);  
		$productos = $stmt->fetchAll(PDO::FETCH_OBJ);
		$db = null;
		echo '{"productos": ' . json_encode($productos) . '}';
	} catch(PDOException $e) {
		echo '{"error":{"text":'. $e->getMessage() .'}}'; 
	}
}


function getFicha($query) {
	validateIp($_SERVER['REMOTE_ADDR']);
	$sql = "SELECT SKU AS sku, CONCAT('http://www-1.dmcentralserver.com/fichas/', sku, '.json') as url FROM PricefileMumbai WHERE isactive = 1 AND hasTechNotes = 1 AND UPPER(SKU) LIKE :query";	
	try {
		$db = getConnection();
		$stmt = $db->prepare($sql);
		$query = "%".$query."%";  
		$stmt->bindParam("query", $query);
		$stmt->execute();
		$producto = $stmt->fetchAll(PDO::FETCH_OBJ);
		$db = null;
		
	} catch(PDOException $e) {
		echo '{"error":{"text":'. $e->getMessage() .'}}'; 
	}
	echo json_encode($producto); 
}

function getFichasByManufacturer($id_fabricante = NULL) {
	validateIp($_SERVER['REMOTE_ADDR']);
	$sql = "SELECT SKU AS sku, CONCAT('http://www-1.dmcentralserver.com/fichas/', sku, '.json') as url FROM PricefileMumbai WHERE isactive = 1 AND hasTechNotes = 1 AND id_fabricante =:id_fabricante";	
	try {
		$db = getConnection();
		$stmt = $db->prepare($sql);  
		$stmt->bindParam("id_fabricante", $id_fabricante);
		$stmt->execute();
		$productos = $stmt->fetchAll(PDO::FETCH_OBJ);
		$db = null;
	} catch(PDOException $e) {
		$productos["message"] = "brand not found";
	}
	echo json_encode($productos); 
}
function getFichasBySubCategory($id_subcategoria = NULL) {
	validateIp($_SERVER['REMOTE_ADDR']);
	$sql = "SELECT SKU AS sku, CONCAT('http://www-1.dmcentralserver.com/fichas/', sku, '.json') as url FROM PricefileMumbai WHERE isactive = 1 AND hasTechNotes = 1 AND id_subcategoria =:id_subcategoria";	
	try {
		$db = getConnection();
		$stmt = $db->prepare($sql);  
		$stmt->bindParam("id_subcategoria", $id_subcategoria);
		$stmt->execute();
		$productos = $stmt->fetchAll(PDO::FETCH_OBJ);
		$db = null;
	} catch(PDOException $e) {
		$productos["message"] = "category not found";
	}
	echo json_encode($productos); 
}


function getImagenes() {
	validateIp($_SERVER['REMOTE_ADDR']);
	$sql = "SELECT SKU AS sku, CONCAT('http://www-1.dmcentralserver.com/images/', sku, '.jpg') as url  FROM PricefileMumbai WHERE isactive = 1 AND hasImage = 1";
	try {
		$db = getConnection();
		$stmt = $db->query($sql);  
		$productos = $stmt->fetchAll(PDO::FETCH_OBJ);
		$db = null;
		echo json_encode($productos);
	} catch(PDOException $e) {
		echo '{"error":{"text":'. $e->getMessage() .'}}'; 
	}
}
function getImagen($query) {
	validateIp($_SERVER['REMOTE_ADDR']);
	$sql = "SELECT SKU AS sku, CONCAT('http://www-1.dmcentralserver.com/images/', sku, '.jpg') as url FROM PricefileMumbai WHERE isactive = 1 AND hasImage = 1 AND UPPER(SKU) LIKE :query";	
	try {
		$db = getConnection();
		$stmt = $db->prepare($sql);
		$query = "%".$query."%";  
		$stmt->bindParam("query", $query);
		$stmt->execute();
		$producto = $stmt->fetchAll(PDO::FETCH_OBJ);
		$db = null;
		
	} catch(PDOException $e) {
		echo '{"error":{"text":'. $e->getMessage() .'}}'; 
	}
	echo json_encode($producto); 
}

function getImagenesByManufacturer($id_fabricante = NULL) {
	validateIp($_SERVER['REMOTE_ADDR']);
	$sql = "SELECT SKU AS sku, CONCAT('http://www-1.dmcentralserver.com/images/', sku, '.jpg') as url FROM PricefileMumbai WHERE isactive = 1 AND hasImage = 1 AND id_fabricante =:id_fabricante";	
	try {
		$db = getConnection();
		$stmt = $db->prepare($sql);  
		$stmt->bindParam("id_fabricante", $id_fabricante);
		$stmt->execute();
		$productos = $stmt->fetchAll(PDO::FETCH_OBJ);
		$db = null;
	} catch(PDOException $e) {
		$productos["message"] = "brand not found";
	}
	echo json_encode($productos); 
}
function getImagenesBySubCategory($id_subcategoria = NULL) {
	validateIp($_SERVER['REMOTE_ADDR']);
	$sql = "SELECT SKU AS sku, CONCAT('http://www-1.dmcentralserver.com/images/', sku, '.jpg') as url FROM PricefileMumbai WHERE isactive = 1 AND hasImage = 1 AND id_subcategoria =:id_subcategoria";	
	try {
		$db = getConnection();
		$stmt = $db->prepare($sql);  
		$stmt->bindParam("id_subcategoria", $id_subcategoria);
		$stmt->execute();
		$productos = $stmt->fetchAll(PDO::FETCH_OBJ);
		$db = null;
	} catch(PDOException $e) {
		$productos["message"] = "category not found";
	}
	echo json_encode($productos); 
}

function getProductosPorCliente($id = NULL) {
	
	$sql = "SELECT pf.SKU, pf.MFRPartNumber, pf.UPCNumber, pf.ProductDescription1, pf.ProductDescription2,pf.ProductWeight,pf.ProductHeight,pf.ProductWidth,pf.ProductLength,pf.id_marca,pf.id_subcategoria, pf.hasImagePhysical, pf.marketing, (select marca from manufacturer_mx where id = pf.id_marca) as marca, (select nombre from categorias_mx where id = pf.id_subcategoria) as categoria FROM pricefile_mx AS pf INNER JOIN manufacturer_xref_user_mx AS mxu ON mxu.manufacturer_id_xref = pf.id_marca INNER JOIN category_xref_user_mx cxu ON cxu.category_id = pf.id_subcategoria WHERE mxu.user_id = ".$id." AND cxu.user_id = ".$id." and pf.isactive = 1 and pf.disponibilidad > 0";
	try {
		$db = getConnection();
		$stmt = $db->query($sql);  
		$products = $stmt->fetchAll(PDO::FETCH_OBJ);
		$db = null;
		$a["status"]="exitoso";
		$a["total"]=$stmt->rowCount();
		
		$productos = array();
		foreach ($products as $producto) {


			$line = array(
						  "nombre"=>utf8_encode($producto->ProductDescription1." ".$producto->ProductDescription2),
						  "sku"=>utf8_encode($producto->SKU),
						  "modelo"=>utf8_encode($producto->MFRPartNumber),
						  "upc"=>$producto->UPCNumber,
						  "weight"=>$producto->ProductWeight,
						  "height"=>$producto->ProductHeight,
						  "width"=>$producto->ProductWidth,
						  "length"=>$producto->ProductLength,
						  "id_marca"=>$producto->id_marca,
						  "marca"=>$producto->marca,
						  "id_categoria"=>$producto->id_subcategoria,
						  "categoria"=>$producto->categoria,
						  "imagen"=>$producto->hasImagePhysical,
						  "ficha"=>$producto->marketing
						 );

			$productos[] = $line;
		}
		echo json_encode($productos);
	} catch(PDOException $e) {
		echo '{"error":{"text":'. $e->getMessage() .'}}'; 
	}
}

function getProducts_new() {
	validateIp($_SERVER['REMOTE_ADDR']);
	$sql = "SELECT SKU AS sku,UPCNumber as codigo_barras,MFRPartNumber as codigo_fabricante, CONCAT_WS(' ',ProductDescription1,ProductDescription2) AS titulo,id_fabricante as id_marca,id_subcategoria as id_categoria, ProductLength as length, ProductWidth as width, ProductHeight as height, ProductWeight as weight FROM PricefileMumbai WHERE isactive=1 and disponibilidad>0";
	try {
		$db = getConnection();
		$stmt = $db->query($sql);  
		$productos = $stmt->fetchAll(PDO::FETCH_OBJ);
		$db = null;
		echo json_encode($productos);
	} catch(PDOException $e) {
		echo '{"error":{"text":'. $e->getMessage() .'}}'; 
	}
}
function getProductosByManufacturer($id_fabricante = NULL) {
	validateIp($_SERVER['REMOTE_ADDR']);
	$sql = "SELECT SKU AS sku,UPCNumber as codigo_barras,MFRPartNumber as codigo_fabricante, CONCAT_WS(' ',ProductDescription1,ProductDescription2) AS titulo,id_fabricante as id_marca, id_subcategoria as id_categoria, ProductLength as length, ProductWidth as width, ProductHeight as height, ProductWeight as weight FROM PricefileMumbai WHERE precio>0 AND id_fabricante =:id_fabricante";	
	//$sql = "SELECT SKU AS sku,UPCNumber as codigo_barras,MFRPartNumber as codigo_fabricante, CONCAT_WS(' ',ProductDescription1,ProductDescription2) AS titulo,id_fabricante as id_marca, VendorNameDisplayed as marca,id_subcategoria as id_categoria, SubCategory as categoria, ProductLength as length, ProductWidth as width, ProductHeight as height, ProductWeight as weight FROM PricefileMumbai WHERE precio>0 AND id_fabricante =:id_fabricante";
	try {
		$db = getConnection();
		$stmt = $db->prepare($sql);  
		$stmt->bindParam("id_fabricante", $id_fabricante);
		$stmt->execute();
		$productos = $stmt->fetchAll(PDO::FETCH_OBJ);
		$db = null;
	} catch(PDOException $e) {
		$productos["message"] = "brand not found";
	}
	echo json_encode($productos); 
}
function getProductosBySubCategory($id_subcategoria = NULL) {
	validateIp($_SERVER['REMOTE_ADDR']);
	$sql = "SELECT SKU AS sku,UPCNumber as codigo_barras,MFRPartNumber as codigo_fabricante, CONCAT_WS(' ',ProductDescription1,ProductDescription2) AS titulo,id_fabricante as id_marca,id_subcategoria as id_categoria, ProductLength as length, ProductWidth as width, ProductHeight as height, ProductWeight as weight FROM PricefileMumbai WHERE precio>0 AND id_subcategoria =:id_subcategoria";
	try {
		$db = getConnection();
		$stmt = $db->prepare($sql);  
		$stmt->bindParam("id_subcategoria", $id_subcategoria);
		$stmt->execute();
		$productos = $stmt->fetchAll(PDO::FETCH_OBJ);
		$db = null;
	} catch(PDOException $e) {
		$productos["message"] = "category not found";
	}
	echo json_encode($productos); 
}

function getCategory($id) {
	validateIp($_SERVER['REMOTE_ADDR']);
	$sql = "SELECT id, nombre as category, parent_id FROM categorias_mx WHERE id=:id";
	try {
		$db = getConnection();
		$stmt = $db->prepare($sql);  
		$stmt->bindParam("id", $id);
		$stmt->execute();
		$category = $stmt->fetchObject();  
		$db = null;
	} catch(PDOException $e) {
		$category["message"] = "category not found";
		echo json_encode($category); 
	}
	echo json_encode($category); 
}
function getStockBySku($sku) {
	validateIp($_SERVER['REMOTE_ADDR']);
	$sql = "SELECT SKU,disponibilidad_10 as stock_df,disponibilidad_20 as stock_puebla,disponibilidad_30 as stock_merida,disponibilidad_40 as stock_mty,disponibilidad_50 as stock_leon,disponibilidad_55 as stock_qto,disponibilidad_60 as stock_tijuana,disponibilidad_70 as stock_hmo, disponibilidad_80 as stock_gdl, precio FROM PricefileMumbai WHERE SKU =:sku";
	try {
		$db = getConnection();
		$stmt = $db->prepare($sql);  
		$stmt->bindParam("sku", $sku);
		$stmt->execute();
		$producto = $stmt->fetchObject();  
		$db = null;
	} catch(PDOException $e) {
		$producto["message"] = "product not found";
	}
	echo json_encode($producto); 
}

function getMarca($id) {
	validateIp($_SERVER['REMOTE_ADDR']);
	$sql = "SELECT id, nombre as brand FROM fabricantes_ingram WHERE id=:id";
	try {
		$db = getConnection();
		$stmt = $db->prepare($sql);  
		$stmt->bindParam("id", $id);
		$stmt->execute();
		$marca = $stmt->fetchObject();  
		$db = null;
	} catch(PDOException $e) {
		$marca["message"] = "brand not found";
		echo json_encode($marca); 
	}
	echo json_encode($marca); 
}

function getProducto($query) {
	validateIp($_SERVER['REMOTE_ADDR']);
	$sql = "SELECT SKU AS sku,UPCnumber as codigo_barras,MFRPartNumber as codigo_fabricante, CONCAT_WS(' ',ProductDescription1,ProductDescription2) AS titulo,id_fabricante as id_marca,id_subcategoria as id_categoria, ProductLength as length, ProductWidth as width, ProductHeight as height, ProductWeight as weight FROM PricefileMumbai WHERE UPPER(SKU) LIKE :query";	
	//$sql = "SELECT SKU as sku, MFRPartNumber as part_number, UPCNumber as upc, ProductDescription1 AS title, id_fabricante as id_marca, id_subcategoria as id_categoria, precio as price, disponibilidad as quantity FROM PricefileMumbai WHERE UPPER(SKU) LIKE :query";
	try {
		$db = getConnection();
		$stmt = $db->prepare($sql);
		$query = "%".$query."%";  
		$stmt->bindParam("query", $query);
		$stmt->execute();
		$producto = $stmt->fetchAll(PDO::FETCH_OBJ);
		$db = null;
		
	} catch(PDOException $e) {
		echo '{"error":{"text":'. $e->getMessage() .'}}'; 
	}
	echo json_encode($producto); 
}
function getCategoriasPorCliente($user_id = NULL) {
	$sql = "SELECT ci.id,ci.nombre,ci.parent_id,(SELECT nombre FROM categorias_mx WHERE id = ci.parent_id) AS parent FROM categorias_mx ci inner join category_xref_user_mx cxu on cxu.category_id = ci.id AND cxu.user_id = ".$user_id;
	try {
		$db = getConnection();
		$stmt = $db->query($sql);  
		$categorias = $stmt->fetchAll(PDO::FETCH_OBJ);
		$db = null;
		$productos = array();
		foreach ($categorias as $categoria) {
			$line = array("id"=>$categoria->id,"nombre"=>$categoria->nombre,"parent_id"=>$categoria->parent_id,"parent"=>$categoria->parent);
			$productos[] = $line;
		}
		echo json_encode($productos);
	} catch(PDOException $e) {
		echo '{"error":{"text":'. $e->getMessage() .'}}'; 
	}
}

function getCategories_new() {
	validateIp($_SERVER['REMOTE_ADDR']);
	$sql = "SELECT id,nombre as category,parent_id FROM categorias_mx";
	try {
		$db = getConnection();
		$stmt = $db->query($sql);  
		$categorias = $stmt->fetchAll(PDO::FETCH_OBJ);
		$db = null;
		echo '{"categorias": ' . json_encode($categorias) . '}';
	} catch(PDOException $e) {
		echo '{"error":{"text":'. $e->getMessage() .'}}'; 
	}
}
function getCategoriesByParent($parent_id = NULL) {
	validateIp($_SERVER['REMOTE_ADDR']);
	$sql = "SELECT id,nombre as category,parent_id FROM categorias_mx WHERE parent_id = :parent_id";
	try {
		$db = getConnection();
		$stmt = $db->prepare($sql);  
		$stmt->bindParam("parent_id", $parent_id);
		$stmt->execute();
		$categorias = $stmt->fetchAll(PDO::FETCH_OBJ);
		$db = null;
	} catch(PDOException $e) {
		$categorias["message"] = "parent_id not found";
	}
	echo json_encode($categorias); 
}
function getMarcasPorCliente($user_id = NULL) {
	$sql = "SELECT fi.id,fi.marca,fi.codigo FROM manufacturer_mx fi INNER JOIN manufacturer_xref_user_mx mxu ON mxu.manufacturer_id_xref = fi.id WHERE mxu.user_id = ".$user_id;
	try {
		$db = getConnection();
		$stmt = $db->query($sql);  
		$fabricantes = $stmt->fetchAll(PDO::FETCH_OBJ);
		$db = null;
		$marcas = array();
		foreach ($fabricantes as $fabricante) {
			$line = array("id"=>$fabricante->id,"nombre"=>$fabricante->marca);
			$marcas[] = $line;
		}
		echo  json_encode($marcas);
	} catch(PDOException $e) {
		echo '{"error":{"text":'. $e->getMessage() .'}}'; 
	}
}

function getBrands_new() {
	//validateIp($_SERVER['REMOTE_ADDR']);
	$sql = "SELECT id,marca as brand FROM manufacturer_mx";
	try {
		$db = getConnection();
		$stmt = $db->query($sql);  
		$marcas = $stmt->fetchAll(PDO::FETCH_OBJ);
		$db = null;
		echo '{"marcas": ' . json_encode($marcas) . '}';
	} catch(PDOException $e) {
		echo '{"error":{"text":'. $e->getMessage() .'}}'; 
	}
}
function getWines() {
	$sql = "select * FROM wine ORDER BY name";
	try {
		$db = getConnection();
		$stmt = $db->query($sql);  
		$wines = $stmt->fetchAll(PDO::FETCH_OBJ);
		$db = null;
		echo '{"wine": ' . json_encode($wines) . '}';
	} catch(PDOException $e) {
		echo '{"error":{"text":'. $e->getMessage() .'}}'; 
	}
}

function getWine($id) {
	$sql = "SELECT * FROM wine WHERE id=:id";
	try {
		$db = getConnection();
		$stmt = $db->prepare($sql);  
		$stmt->bindParam("id", $id);
		$stmt->execute();
		$wine = $stmt->fetchObject();  
		$db = null;
		echo json_encode($wine); 
	} catch(PDOException $e) {
		echo '{"error":{"text":'. $e->getMessage() .'}}'; 
	}
}

function addOrder() {
	validateIp($_SERVER['REMOTE_ADDR']);
	$request = Slim::getInstance()->request();
	$orden = json_decode($request->getBody());
	//error_log($orden, 3, 'php.log');
	//$sql = "INSERT INTO wine (name, grapes, country, region, year, description) VALUES (:name, :grapes, :country, :region, :year, :description)";
	//try {
		$order_response = array();
		if (isset($orden->usuario) && !empty($orden->usuario) && $orden->usuario=="digibyte" && isset($orden->password) && !empty($orden->password) && $orden->password=="4DdiGiByt.1") {    
		    $exito = false;
		    if (isset($orden->orden) && !empty($orden->orden)) {
		    	$exito = true;
		    }else{
		    	$exito = false;
		    	$mensaje_error_id = 1;
		    	$mensaje_error = "Folio de orden no fue recibido o inválido";
		    }
		    if (isset($orden->carrier) && !empty($orden->carrier)) {
		    	$carrier = $orden->carrier;
		    	if ($carrier == "fedex") {
		    		$carrier = "E1";
		    	}
		    	if ($carrier == "estafeta") {
		    		$carrier = "E1";
		    	}
		    }else{
		    	$carrier = "E1";
		    }
		    if (isset($orden->nombre) && !empty($orden->nombre) && $exito==true) {
		    	$exito = true;
		    }elseif (!isset($mensaje_error_id)) {
		    	$exito = false;
		    	$mensaje_error_id = 2;
		    	$mensaje_error = "Nombre de Cliente no fue recibido";
		    }    
		    if (isset($orden->sucursal) && !empty($orden->sucursal) && $exito==true) {
		    	$sucursal = $orden->sucursal;
		    }else{
		    	$sucursal = 10;
		    }   
		    if (isset($orden->calle) && !empty($orden->calle) && $exito==true) {
		    	$exito = true;
		    }elseif (!isset($mensaje_error_id)) {
		    	$exito = false;
		    	$mensaje_error_id = 3;
		    	$mensaje_error = "Nombre de la calle no fue recibida";
		    }    

		    if (isset($orden->colonia) && !empty($orden->colonia) && $exito==true) {
		    	$exito = true;
		    }elseif (!isset($mensaje_error_id)) {
		    	$exito = false;
		    	$mensaje_error_id = 4;
		    	$mensaje_error = "Nombre de la colonia no fue recibida";
		    }    

		    if (isset($orden->telefono) && !empty($orden->telefono) && $exito==true) {
		    	$exito = true;
		    }elseif (!isset($mensaje_error_id)) {
		    	$exito = false;
		    	$mensaje_error_id = 5;
		    	$mensaje_error = "Telefono del cliente no fue recibido";
		    }  

		    if (isset($orden->ciudad) && !empty($orden->ciudad) && $exito==true) {
		    	$exito = true;
		    }elseif (!isset($mensaje_error_id)) {
		    	$exito = false;
		    	$mensaje_error_id = 6;
		    	$mensaje_error = "Nombre de la ciudad del cliente no fue recibido";
		    }  

		    if (isset($orden->estado) && !empty($orden->estado) && $exito==true) {
		    	$exito = true;
		    }elseif (!isset($mensaje_error_id)) {
		    	$exito = false;
		    	$mensaje_error_id = 7;
		    	$mensaje_error = "Nombre del estado no fue recibido";
		    }  

		    if (isset($orden->cp) && !empty($orden->cp) && $exito==true) {
		    	$exito = true;
		    }elseif (!isset($mensaje_error_id)) {
		    	$exito = false;
		    	$mensaje_error_id = 8;
		    	$mensaje_error = "Código Postal Vacío";
		    }  

		    if (isset($orden->productos) && !empty($orden->productos) && $exito==true) {
		    	$productos = array();
		    	$i=0;
		    	$skus = explode(',', $orden->productos);
		    	if(sizeof($skus)>1){
		    		foreach ($skus as $sku) {
		    			$skus_with_stock = explode(':', $sku);
		    			if (sizeof($skus_with_stock)>0) {
		    				array_push($productos, array('sku'=>$skus_with_stock[0],'stock'=>$skus_with_stock[1]));
		    				$i++;
		    			}else{
					    	$exito = false;
					    	$mensaje_error_id = 9;
					    	$mensaje_error = "No se encontro la cantidad de: ".$sku;      				
					    	exit;
		    			}
		    		}
		    	}else{
		   			$skus_with_stock = explode(':', $orden->productos);
		   			if (sizeof($skus_with_stock)>1) {
		   				array_push($productos, array('sku'=>$skus_with_stock[0],'stock'=>$skus_with_stock[1]));
		   			}else{
				    	$exito = false;
				    	$mensaje_error_id = 10;
				    	$mensaje_error = "No especificado el stock de: ".$skus_with_stock[0];          				
		   			}
		   			
		    	}
		    	if (sizeof($productos>0) && $exito==true) {
		    		$exito = true;
		    	}else{
			    	$exito = false;
			    	$mensaje_error_id = 11;
			    	$mensaje_error = "Revisar sintaxis de productos, separado por comas y guiones";    		
		    	}
			}elseif (!isset($mensaje_error_id)) {
			  	$exito = false;
		   		$mensaje_error_id = 12;
		   		$mensaje_error = "Listado de productos vacío";
			}		
		}else{
		  	$exito = false;
	   		$mensaje_error_id = 0;
	   		$mensaje_error = "Usuario y Contrasenia Incorrectos";
		}
		if ($exito==true) {
			$sql = "SELECT * FROM ingram_orders WHERE orden=:orden";
			$db = getConnection();
			$stmt = $db->prepare($sql);  
			$stmt->bindParam("orden", $orden->orden);
			$stmt->execute();
			$result = $stmt->fetch(PDO::FETCH_ASSOC);
			$db = null;
			if($result){
				$exito = false;
			    $mensaje_error_id = 13;
			    $mensaje_error = "No. de Orden ".$orden->orden." encontrada, evita replicar.";
			}else{
				$PCG_SENDER   = "DIGIBYTE";
				$PGC_LOGIN    = "GoGoT4gDd";
				$PCG_PASS     = "SaLeS092618";
				$atention_to  = utf8_decode($orden->nombre);
				$address1     = utf8_decode($orden->calle);
				$address2     = utf8_decode($orden->colonia);
				$address3     = utf8_decode($orden->telefono);									
				$city         = utf8_decode($orden->ciudad);
				$state        = utf8_decode($orden->estado);
				$postal_code  = utf8_decode($orden->cp);
				$xml_ingram = "<OrderRequest>
								<Version>2.0</Version>";
							$xml_ingram .= "<TransactionHeader>
										<SenderID>".$PCG_SENDER."</SenderID>
										<ReceiverID>INGRAM MICRO</ReceiverID>
										<CountryCode>MX</CountryCode>
										<LoginID>".$PGC_LOGIN."</LoginID>
										<Password>".$PCG_PASS."</Password>
										<TransactionID>{8C1C3C30-63CD-4638-A623-AC888856E3CC}</TransactionID>
										</TransactionHeader>";
								$xml_ingram .= "<OrderHeaderInformation>
											<BillToSuffix>000</BillToSuffix>
											<AddressingInformation>
												<CustomerPO>WEB_".$orden->orden."</CustomerPO>
												<ShipToAttention>$atention_to</ShipToAttention>
												<EndUserPO/>
												<ShipTo>
													<Address>
														<ShipToAddress1>$address1</ShipToAddress1>
														<ShipToAddress2>$address2</ShipToAddress2>
														<ShipToAddress3>$address3</ShipToAddress3>
														<ShipToCity>$city</ShipToCity>
														<ShipToProvince>$state</ShipToProvince>
														<ShipToPostalCode>$postal_code</ShipToPostalCode>
													</Address>
												</ShipTo>
											</AddressingInformation>";
								$xml_ingram .= "	<ProcessingOptions>
												<CarrierCode>".$carrier."</CarrierCode>
												<AutoRelease>0</AutoRelease>
												<ShipmentOptions>
													<BackOrderFlag>Y</BackOrderFlag>
													<SplitShipmentFlag>Y</SplitShipmentFlag>
													<SplitLine>N</SplitLine>
													<ShipFromBranches>".$sucursal."</ShipFromBranches>
												</ShipmentOptions>
											</ProcessingOptions>
										</OrderHeaderInformation>
										<OrderLineInformation>";					
								foreach($productos as $producto)
								{
									$xml_ingram .=	"
											<ProductLine>
												<SKU>".$producto["sku"]."</SKU> 
												<Quantity>".$producto["stock"]."</Quantity> 
												<CustomerLineNumber/> 
											</ProductLine>
											";				
								}
											$xml_ingram .= "<CommentLine>
														<CommentText>NO ENTREGAR FACTURA USUARIO FINAL</CommentText>
													</CommentLine>";
								$xml_ingram .= "</OrderLineInformation>
										<ShowDetail>1</ShowDetail>
									</OrderRequest>";
								$url = 'https://newport.ingrammicro.com/MUSTANG';
								function httpsPost($Url, $strRequest){
									   $ch=curl_init();
									   curl_setopt($ch, CURLOPT_URL, $Url);
									   curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
									   curl_setopt($ch, CURLOPT_POST, 1) ;
									   curl_setopt($ch, CURLOPT_POSTFIELDS, $strRequest);
									   curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0);
									   curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 0);
$headers = array("Content-type: text/xml", "Content-length: " . strlen($strRequest), "Connection: close");
curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);									   
									   $result = curl_exec($ch);
									   curl_close($ch);
									   return $result;
								}							
								$strRequest = utf8_encode($xml_ingram);
								$Response = httpsPost($url, $strRequest);
								$doc = new DOMDocument();
								$doc->loadXML(utf8_decode($Response));
								$OResp = "OResponse.xml";
								$doc->save("$OResp");	

								$ErrorStatus = $doc->getElementsByTagName("ErrorStatus");
								$ErrorNumber = $ErrorStatus->item(0)->getAttribute("ErrorNumber");
								if(strlen($ErrorNumber)<=0){
									$BranchOrderNumber = $doc->getElementsByTagName( "BranchOrderNumber" );
									$order_from_ingram = $BranchOrderNumber->item(0)->nodeValue;

									$sql1 = "INSERT INTO ingram_orders (orden,ingram,fecha_alta) VALUES (:ordenhollmar,:ordeningram,NOW())";
									$db1 = getConnection();
									$stmt1 = $db1->prepare($sql1);  
									$stmt1->bindParam("ordenhollmar", $orden->orden);
									$stmt1->bindParam("ordeningram", $order_from_ingram);
									$stmt1->execute();
									//$result1 = $stmt1->fetch(PDO::FETCH_ASSOC);
									$db1 = null;								

							    	$exito = true;
							    	$mensaje_error_id = 14;
							    	$mensaje_error = "ORDEN GRABADA CON EXITO";							
								}elseif($ErrorNumber!='20079'){
								    $exito = false;
								    $mensaje_error_id = 15;
								    $mensaje_error = "ERROR FATAL registrando orden de compras, contactar a administradores";							
								}
			}		
			if ($exito==true) {

				$order_response["result"] = "success";
				$order_response["code"] = $mensaje_error_id;
				$order_response["text"] = $mensaje_error;
				$order_response["ingram_branch_order"] = $order_from_ingram;
				$order_response["ingram_purchase_order"] = "WEB_".$orden->orden;
				$order_response["retail_order"] = $orden->orden;

				//echo '{"exito":{"codigo":'.$mensaje_error_id.',"text":'.$mensaje_error.'}}'; 
			}else{

				$order_response["result"] = "error";
				$order_response["code"] = $mensaje_error_id;
				$order_response["text"] = $mensaje_error;

				//echo '{"error":{"codigo":'.$mensaje_error_id.',"text":'.$mensaje_error.'}}'; 	
			}
		}else{
			$order_response["result"] = "error";
			$order_response["code"] = $mensaje_error_id;
			$order_response["text"] = $mensaje_error;			
			//echo '{"error":{"codigo":'.$mensaje_error_id.',"text":'.$mensaje_error.'}}'; 
		}
	
	echo json_encode($order_response); 
	
}
function getCategories() {
	
	$sql = "SELECT jos_vm_category.category_id AS id, jos_vm_category.category_name AS category, jos_vm_category.category_full_image AS image, jos_vm_category_xref.category_parent_id as parent_id  FROM jos_vm_category INNER JOIN jos_vm_category_xref ON jos_vm_category_xref.category_child_id =  jos_vm_category.category_id ORDER BY category_id";
	try {
		$db = getConnection();
		$stmt = $db->query($sql);  
		$categorias = $stmt->fetchAll(PDO::FETCH_OBJ);
		$db = null;
		echo '{"categorias": ' . json_encode($categorias) . '}';
	} catch(PDOException $e) {
		echo '{"error":{"text":'. $e->getMessage() .'}}'; 
	}
}
function getBrands() {
	$sql = "select manufacturer_id as id, mf_name as brand FROM jos_vm_manufacturer ORDER BY manufacturer_id";
	try {
		$db = getConnection();
		$stmt = $db->query($sql);  
		$marcas = $stmt->fetchAll(PDO::FETCH_OBJ);
		$db = null;
		echo '{"marcas": ' . json_encode($marcas) . '}';
	} catch(PDOException $e) {
		echo '{"error":{"text":'. $e->getMessage() .'}}'; 
	}
}
function getProducts() {
	//$sql = "select sku,codigo_barras,codigo_fabricante,descripcion_corta,descripcion_larga,id_marca,id_categoria,disponibilidad_10,disponibilidad_20,disponibilidad_30,disponibilidad_40,disponibilidad_50,disponibilidad_55,disponibilidad_60,disponibilidad_70,disponibilidad_80,precio,ficha_tecnica,imagen_full FROM price_file WHERE precio>0";
	$sql = "SELECT sku,codigo_barras,codigo_fabricante,descripcion_corta,descripcion_larga,id_marca, marca, id_categoria, jos_vm_category.category_name AS categoria, ficha_tecnica,imagen_full, length, width, height, weight FROM price_file INNER JOIN jos_vm_category ON jos_vm_category.category_id = price_file.id_categoria WHERE precio>0";
	$sql = "SELECT sku,codigo_barras,codigo_fabricante,descripcion_corta,descripcion_larga,id_marca, marca, id_categoria, jos_vm_category.category_name AS categoria, length, width, height, weight FROM price_file INNER JOIN jos_vm_category ON jos_vm_category.category_id = price_file.id_categoria WHERE precio>0";
	try {
		$db = getConnection();
		$stmt = $db->query($sql);  
		$productos = $stmt->fetchAll(PDO::FETCH_OBJ);
		$db = null;
		echo '{"productos": ' . json_encode($productos) . '}';
	} catch(PDOException $e) {
		echo '{"error":{"text":'. $e->getMessage() .'}}'; 
	}
}
function getProductsL() {
	//$sql = "select sku,codigo_barras,codigo_fabricante,descripcion_corta,descripcion_larga,id_marca,id_categoria,disponibilidad_10,disponibilidad_20,disponibilidad_30,disponibilidad_40,disponibilidad_50,disponibilidad_55,disponibilidad_60,disponibilidad_70,disponibilidad_80,precio,ficha_tecnica,imagen_full FROM price_file WHERE precio>0";
	$sql = "SELECT sku,codigo_barras,codigo_fabricante,descripcion_corta,descripcion_larga,id_marca, marca, id_categoria, jos_vm_category.category_name AS categoria, ficha_tecnica,imagen_full, length, width, height, weight FROM price_file INNER JOIN jos_vm_category ON jos_vm_category.category_id = price_file.id_categoria WHERE precio > 0 AND fecha_creacion > DATE_SUB(NOW(), INTERVAL 1 WEEK)";
	try {
		$db = getConnection();
		$stmt = $db->query($sql);  
		$productos = $stmt->fetchAll(PDO::FETCH_OBJ);
		$db = null;
		echo '{"productos": ' . json_encode($productos) . '}';
	} catch(PDOException $e) {
		echo '{"error":{"text":'. $e->getMessage() .'}}'; 
	}
}
function getStock($sku =NULL) {
	//$sql = "select sku,codigo_barras,codigo_fabricante,descripcion_corta,descripcion_larga,id_marca,id_categoria,disponibilidad_10,disponibilidad_20,disponibilidad_30,disponibilidad_40,disponibilidad_50,disponibilidad_55,disponibilidad_60,disponibilidad_70,disponibilidad_80,precio,ficha_tecnica,imagen_full FROM price_file WHERE precio>0";
	$sql = "SELECT SKU,disponibilidad_10 as stock_df,disponibilidad_20 as stock_puebla,disponibilidad_30 as stock_merida,disponibilidad_40 as stock_mty,disponibilidad_50 as stock_leon,disponibilidad_55 as stock_qto,disponibilidad_60 as stock_tijuana,disponibilidad_70 as stock_hmo, disponibilidad_80 as stock_gdl, precio FROM PricefileMumbai";
	try {
		$db = getConnection();
		$stmt = $db->query($sql);  
		$productos = $stmt->fetchAll(PDO::FETCH_OBJ);
		$db = null;
		$productoss = array();
		foreach ($productos as $producto) {
			$total = $producto->stock_df+$producto->stock_puebla+$producto->stock_merida+$producto->stock_mty+$producto->stock_leon+$producto->stock_qto+$producto->stock_tijuana+$producto->stock_hmo+$producto->stock_gdl;		
			$a = array("sku"=>$producto->SKU,"stock_total"=>$total,"stock_df"=>$producto->stock_df,"stock_puebla"=>$producto->stock_puebla,"stock_merida"=>$producto->stock_merida,"stock_mty"=>$producto->stock_mty,"stock_leon"=>$producto->stock_leon,"stock_qto"=>$producto->stock_qto,"stock_tijuana"=>$producto->stock_tijuana,"stock_hmo"=>$producto->stock_hmo,"stock_gdl"=>$producto->stock_gdl,"precio"=>number_format($producto->precio,2));
			array_push($productoss, $a);
		}
		echo '{"productos": ' . json_encode($productoss) . '}';
	} catch(PDOException $e) {
		echo '{"error":{"text":'. $e->getMessage() .'}}'; 
	}
}
/*
function getStock() {
	//$sql = "select sku,codigo_barras,codigo_fabricante,descripcion_corta,descripcion_larga,id_marca,id_categoria,disponibilidad_10,disponibilidad_20,disponibilidad_30,disponibilidad_40,disponibilidad_50,disponibilidad_55,disponibilidad_60,disponibilidad_70,disponibilidad_80,precio,ficha_tecnica,imagen_full FROM price_file WHERE precio>0";
	$sql = "select sku,disponibilidad_10 as stock_df,disponibilidad_20 as stock_puebla,disponibilidad_30 as stock_merida,disponibilidad_40 as stock_mty,disponibilidad_50 as stock_leon,disponibilidad_55 as stock_qto,disponibilidad_60 as stock_tijuana,disponibilidad_70 as stock_hmo, disponibilidad_80 as stock_gdl, precio FROM price_file";
	try {
		$db = getConnection();
		$stmt = $db->query($sql);  
		$productos = $stmt->fetchAll(PDO::FETCH_OBJ);
		$db = null;
		$productoss = array();
		foreach ($productos as $producto) {
			$total = $producto->stock_df+$producto->stock_puebla+$producto->stock_merida+$producto->stock_mty+$producto->stock_leon+$producto->stock_qto+$producto->stock_tijuana+$producto->stock_hmo+$producto->stock_gdl;		
			$a = array("sku"=>$producto->sku,"stock_total"=>$total,"stock_df"=>$producto->stock_df,"stock_puebla"=>$producto->stock_puebla,"stock_merida"=>$producto->stock_merida,"stock_mty"=>$producto->stock_mty,"stock_leon"=>$producto->stock_leon,"stock_qto"=>$producto->stock_qto,"stock_tijuana"=>$producto->stock_tijuana,"stock_hmo"=>$producto->stock_hmo,"stock_gdl"=>$producto->stock_gdl,"precio"=>number_format($producto->precio,2));
			array_push($productoss, $a);
		}
		echo '{"productos": ' . json_encode($productoss) . '}';
	} catch(PDOException $e) {
		echo '{"error":{"text":'. $e->getMessage() .'}}'; 
	}
}*/
function getStockL() {
	//$sql = "select sku,codigo_barras,codigo_fabricante,descripcion_corta,descripcion_larga,id_marca,id_categoria,disponibilidad_10,disponibilidad_20,disponibilidad_30,disponibilidad_40,disponibilidad_50,disponibilidad_55,disponibilidad_60,disponibilidad_70,disponibilidad_80,precio,ficha_tecnica,imagen_full FROM price_file WHERE precio>0";
	$sql = "SELECT sku,disponibilidad_10 as stock_df,disponibilidad_20 as stock_puebla,disponibilidad_30 as stock_merida,disponibilidad_40 as stock_mty,disponibilidad_50 as stock_leon,disponibilidad_55 as stock_qto,disponibilidad_60 as stock_tijuana,disponibilidad_70 as stock_hmo, disponibilidad_80 as stock_gdl, precio FROM price_file WHERE is_active = 1";
	try {
		$db = getConnection();
		$stmt = $db->query($sql);  
		$productos = $stmt->fetchAll(PDO::FETCH_OBJ);
		$db = null;
		$productoss = array();
		foreach ($productos as $producto) {
			$total = $producto->stock_df+$producto->stock_puebla+$producto->stock_merida+$producto->stock_mty+$producto->stock_leon+$producto->stock_qto+$producto->stock_tijuana+$producto->stock_hmo+$producto->stock_gdl;		
			$a = array("sku"=>$producto->sku,"stock_total"=>$total,"stock_df"=>$producto->stock_df,"stock_puebla"=>$producto->stock_puebla,"stock_merida"=>$producto->stock_merida,"stock_mty"=>$producto->stock_mty,"stock_leon"=>$producto->stock_leon,"stock_qto"=>$producto->stock_qto,"stock_tijuana"=>$producto->stock_tijuana,"stock_hmo"=>$producto->stock_hmo,"stock_gdl"=>$producto->stock_gdl,"precio"=>number_format($producto->precio,2));
			array_push($productoss, $a);
		}
		echo '{"productos": ' . json_encode($productoss) . '}';
	} catch(PDOException $e) {
		echo '{"error":{"text":'. $e->getMessage() .'}}'; 
	}
}
function updateWine($id) {
	$request = Slim::getInstance()->request();
	$body = $request->getBody();
	$wine = json_decode($body);
	$sql = "UPDATE wine SET name=:name, grapes=:grapes, country=:country, region=:region, year=:year, description=:description WHERE id=:id";
	try {
		$db = getConnection();
		$stmt = $db->prepare($sql);  
		$stmt->bindParam("name", $wine->name);
		$stmt->bindParam("grapes", $wine->grapes);
		$stmt->bindParam("country", $wine->country);
		$stmt->bindParam("region", $wine->region);
		$stmt->bindParam("year", $wine->year);
		$stmt->bindParam("description", $wine->description);
		$stmt->bindParam("id", $id);
		$stmt->execute();
		$db = null;
		echo json_encode($wine); 
	} catch(PDOException $e) {
		echo '{"error":{"text":'. $e->getMessage() .'}}'; 
	}
}

function deleteWine($id) {
	$sql = "DELETE FROM wine WHERE id=:id";
	try {
		$db = getConnection();
		$stmt = $db->prepare($sql);  
		$stmt->bindParam("id", $id);
		$stmt->execute();
		$db = null;
	} catch(PDOException $e) {
		echo '{"error":{"text":'. $e->getMessage() .'}}'; 
	}
}

function findByName($query) {
	$sql = "SELECT * FROM wine WHERE UPPER(name) LIKE :query ORDER BY name";
	try {
		$db = getConnection();
		$stmt = $db->prepare($sql);
		$query = "%".$query."%";  
		$stmt->bindParam("query", $query);
		$stmt->execute();
		$wines = $stmt->fetchAll(PDO::FETCH_OBJ);
		$db = null;
		echo '{"wine": ' . json_encode($wines) . '}';
	} catch(PDOException $e) {
		echo '{"error":{"text":'. $e->getMessage() .'}}'; 
	}
}

function getConnection() {
	$dbhost="localhost";
	$dbuser="bdicentralserver_sc";
	$dbpass="Myl-PGfSbzcf";
	$dbname="bdicentralserver_sc";
	$dbh = new PDO("mysql:host=$dbhost;dbname=$dbname;charset=utf8", $dbuser, $dbpass);	
	$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	return $dbh;
}

?>