<?php
use \Psr\Http\Message\ServerRequestInterface as Request;
use \Psr\Http\Message\ResponseInterface as Response;
$app = new \Slim\App;
$app->options('/{routes:.+}', function ($request, $response, $args) {
    return $response;
});
$app->add(function ($req, $res, $next) {
    $response = $next($req, $res);
    return $response->withHeader('Access-Control-Allow-Origin', '*')->withHeader('Access-Control-Allow-Headers', 'X-Requested-With, Content-Type, Accept, Origin, Authorization')->withHeader('Access-Control-Allow-Methods', 'GET, POST, PUT, DELETE, OPTIONS');
});
$app->add(new \Slim\SessionMiddleware(['name' => 'MySessionName']));

/* ==============================================
API: v1.0
- Session
- Admin
- Dashboard
- Client
- Provider
- Product
- Price
- Custom
- Order

================================================ */
/* ------------------
Session
--------------------*/
$app->get('/session/user', function (Request $request, Response $response) {
		$session = new \Slim\Session();
		if($session->user != null){
		  echo json_encode($session->user);
		} else {
		  echo '{"error": {"text": "La session user n\'est pas definie"}}';
		}
});
$app->get('/session/basket', function (Request $request, Response $response) {
		$session = new \Slim\Session();
		if($session->basket != null){
		  echo json_encode($session->basket);
		} else {
		  echo '{"error": {"text": "La session basket n\'est pas definie"}}';
		}
});
$app->get('/session/destroy', function (Request $request, Response $response) {
\Slim\Session::destroy();
		if($session->basket == null && $session->user == null){
		  echo '{"notice": {"text": "La session est d&eacute;truite"}}';
		} else {
		  echo '{"error": {"text": "La session n\'est pas d&eacute;truite"}}';
		}
});
/* ------------------
Admin
--------------------*/
// Check Admin
$app->get('/admin/check', function (Request $request, Response $response) {
    $sql = "SELECT * FROM admin";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->execute();
        $count = $stmt->rowCount();
        $db = null;
        echo $count;
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Add Admin
$app->post('/admin/add', function (Request $request, Response $response) {
    $username = $request->getParam('username');
    $password = md5($request->getParam('password'));
    $email = $request->getParam('email');
    $sql = "INSERT INTO admin (username, password, email) VALUES (:username, :password, :email)";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->bindParam(':username', $username);
        $stmt->bindParam(':password', $password);
        $stmt->bindParam(':email', $email);
        $stmt->execute();
        $id = $db->lastInsertId();
        $db = null;
        echo '{"notice": {"text": "Admin Added", "id": ' . $id . '}}';
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Login Admin
$app->post('/admin/login', function (Request $request, Response $response) {
    $username = $request->getParam('username');
    $password = md5($request->getParam('password'));
    $sql = "SELECT * FROM admin WHERE username = :username AND password = :password";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->bindParam(':username', $username);
        $stmt->bindParam(':password', $password);
        $stmt->execute();
        $result = $stmt->fetch();
        $count = $stmt->rowCount();
        //User Found
        if ($count) {
            $arrRtn['id'] = $result["id"];
            $arrRtn['username'] = $username;
            $db = null;
            echo json_encode($arrRtn);
        } else {
            $db = null;
            echo '{"notice": {"text": "Votre identifiant ou votre mot de passe est incorrect ! Essayez &agrave; nouveau."}}';
        }
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Reset Login Password
$app->put('/admin/reset', function (Request $request, Response $response) {
    $email = $request->getParam('email');
    $sql_select = "SELECT * FROM admin WHERE email = :email";
    $sql_update = "UPDATE admin SET password = :password WHERE email = :email";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql_select);
        $stmt->bindParam(':email', $email);
        $stmt->execute();
        $result = $stmt->fetch();
        $count = $stmt->rowCount();
        //User Found
        if ($count) {
            $Caracteres = 'AaBbCcDdEeFfGgHhIiJjKkLlMmNnOoPpQqRrSsTtUuVvWwXxYyZz0123456789';
            $QuantidadeCaracteres = strlen($Caracteres);
            $QuantidadeCaracteres--;
            $Hash = NULL;
            for ($x = 1;$x <= 8;$x++) {
                $Posicao = rand(0, $QuantidadeCaracteres);
                $Hash.= substr($Caracteres, $Posicao, 1);
            }
            $newPassword = md5($Hash);
            //Update Password
            $stmt = $db->prepare($sql_update);
            $stmt->bindParam(':password', $newPassword);
            $stmt->bindParam(':email', $email);
            $stmt->execute();
            //Send Password
            $db = null;
            echo '{"notice": {"text": "New password has been sent. ' . $Hash . '"}}';
        } else {
            $db = null;
            echo '{"notice": {"text": "L\'adresse e-mail n\'existe pas dans notre base de données."}}';
        }
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
/* ------------------
Dashboard
--------------------*/
// Get All Stats
$app->get('/dashboard', function (Request $request, Response $response) {

    $sql = "SELECT 
    (SELECT COUNT(*) FROM clients) AS clients,
    (SELECT COUNT(*) FROM products) AS products,
    (SELECT COUNT(*) FROM providers) AS providers,
    (SELECT COUNT(*) FROM orders) AS orders";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $stats = $stmt->fetch(PDO::FETCH_OBJ);
        $db = null;
        echo json_encode($stats);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
/* ------------------
Client
--------------------*/
// Get All Clients
$app->get('/clients', function (Request $request, Response $response) {
    $sql = "SELECT * FROM clients";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $clients = $stmt->fetchAll(PDO::FETCH_OBJ);
        $db = null;
        echo json_encode($clients);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Get DISTINCT Clients
$app->get('/clients/distinct', function (Request $request, Response $response) {
    $sql = 'SELECT DISTINCT `companyName` FROM `clients` WHERE `companyName` NOT IN ("Le Palmier sarl","Smoggy sarl","Alimentation chez Wadii Sàrl","VenteComptoire")';
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $clients = $stmt->fetchAll(PDO::FETCH_OBJ);
        $db = null;
        echo json_encode($clients);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Get Single Client
$app->get('/client/{clientId}', function (Request $request, Response $response) {
    $clientId = $request->getAttribute('clientId');
    $sql = "SELECT * FROM clients WHERE clientId = $clientId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $clients = $stmt->fetch(PDO::FETCH_OBJ);
        $db = null;
        echo json_encode($clients);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Login Client
$app->post('/client/login', function (Request $request, Response $response) {
    $username = $request->getParam('username');
    $password = md5($request->getParam('password'));
    $sql = "SELECT * FROM clients WHERE username = :username AND password = :password";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->bindParam(':username', $username);
        $stmt->bindParam(':password', $password);
        $stmt->execute();
        $result = $stmt->fetch();
        $count = $stmt->rowCount();
        //User Found
        if ($count) {
			if($result["isActive"] != 0){
				$arrRtn['id'] = $result["clientId"];
				$arrRtn['username'] = $username;
				$arrRtn['companyName'] = $result["companyName"];
				$arrRtn['street'] = $result["street"];
				$arrRtn['postalCode'] = $result["postalCode"];
				$arrRtn['city'] = $result["city"];
				$session = new \Slim\Session();
				$session->set('user', $arrRtn);
				$db = null;
				echo json_encode($arrRtn);
			} else {
				$db = null;
				echo '{"error": {"text": "Votre compte est en cours d\'activation ! Essayez plus tard."}}';
			}
        } else {
            $db = null;
            echo '{"notice": {"text": "Votre identifiant ou votre mot de passe est incorrect ! Essayez &agrave; nouveau."}}';
        }
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});

// Add Client
$app->post('/client/add', function (Request $request, Response $response) {
    $username = $request->getParam('username');
    $password = md5($request->getParam('password'));
    $companyName = $request->getParam('companyName');
    $email = $request->getParam('email');
    $phone = $request->getParam('phone');
    $street = $request->getParam('street');
    $postalCode = $request->getParam('postalCode');
    $city = $request->getParam('city');
    $country = $request->getParam('country');
    $sql_select = "SELECT * FROM clients WHERE username = :username";
    $sql_insert = "INSERT INTO clients (username, password, companyName, email, phone, street, postalCode, city, country, isActive) VALUES (:username, :password, :companyName, :email, :phone, :street, :postalCode, :city, :country, 1)";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql_select);
        $stmt->bindParam(':username', $username);
        $stmt->execute();
        $count = $stmt->rowCount();
        //Client Not Found
        if ($count) {
            echo '{"error": {"text": "Username ' . $username . ' already exist. Please choose another one"}}';
        } else {
            $stmt = $db->prepare($sql_insert);
            $stmt->bindParam(':username', $username);
            $stmt->bindParam(':password', $password);
            $stmt->bindParam(':companyName', $companyName);
            $stmt->bindParam(':email', $email);
            $stmt->bindParam(':phone', $phone);
            $stmt->bindParam(':street', $street);
            $stmt->bindParam(':postalCode', $postalCode);
            $stmt->bindParam(':city', $city);
            $stmt->bindParam(':country', $country);
            $stmt->execute();
            $id = $db->lastInsertId();
            $db = null;
            echo '{"notice": {"text": "Client Added", "id": ' . $id . '}}';
        }
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Update State Client
$app->put('/client/activate/{clientId}', function (Request $request, Response $response) {
    $clientId = $request->getAttribute('clientId');
    $isActive = $request->getParam('isActive');
    $sql = "UPDATE clients SET
                isActive = :isActive
            WHERE clientId = $clientId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->bindParam(':isActive', $isActive);
        $stmt->execute();
        $db = null;
        echo '{"notice": {"text": "Client Updated"}}';
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Update Client
$app->put('/client/update/{clientId}', function (Request $request, Response $response) {
    $clientId = $request->getAttribute('clientId');
    $username = $request->getParam('username');
    $password = md5($request->getParam('password'));
    $companyName = $request->getParam('companyName');
    $email = $request->getParam('email');
    $phone = $request->getParam('phone');
    $street = $request->getParam('street');
    $postalCode = $request->getParam('postalCode');
    $city = $request->getParam('city');
    $country = $request->getParam('country');
    $sql = "UPDATE clients SET
                username = :username,
                password = :password,
                companyName = :companyName,
                email = :email,
                phone = :phone,
                street = :street,
                postalCode = :postalCode,
                city = :city,
                country = :country
            WHERE clientId = $clientId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->bindParam(':username', $username);
        $stmt->bindParam(':password', $password);
        $stmt->bindParam(':companyName', $companyName);
        $stmt->bindParam(':email', $email);
        $stmt->bindParam(':phone', $phone);
        $stmt->bindParam(':street', $street);
        $stmt->bindParam(':postalCode', $postalCode);
        $stmt->bindParam(':city', $city);
        $stmt->bindParam(':country', $country);
        $stmt->execute();
        $db = null;
        echo '{"notice": {"text": "Client Updated"}}';
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Delete Client
$app->delete('/client/delete/{clientId}', function (Request $request, Response $response) {
    $clientId = $request->getAttribute('clientId');
    $sql = "DELETE FROM clients WHERE clientId = $clientId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->execute();
        $db = null;
        echo '{"notice": {"text": "Client Deleted"}}';
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
/* ------------------
Provider
--------------------*/
// Get All Providers
$app->get('/providers', function (Request $request, Response $response) {
    $sql = "SELECT * FROM providers";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $providers = $stmt->fetchAll(PDO::FETCH_OBJ);
        $db = null;
        echo json_encode($providers);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Get Single Provider
$app->get('/provider/{providerId}', function (Request $request, Response $response) {
    $providerId = $request->getAttribute('providerId');
    $sql = "SELECT * FROM providers WHERE providerId = $providerId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $providers = $stmt->fetch(PDO::FETCH_OBJ);
        $db = null;
        echo json_encode($providers);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Add Provider
$app->post('/provider/add', function (Request $request, Response $response) {
    $name = $request->getParam('name');
    $city = $request->getParam('city');
    $country = $request->getParam('country');
    $sql = "INSERT INTO providers (name, city, country) VALUES (:name, :city, :country)";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->bindParam(':name', $name);
        $stmt->bindParam(':city', $city);
        $stmt->bindParam(':country', $country);
        $stmt->execute();
        $id = $db->lastInsertId();
        $db = null;
        echo '{"notice": {"text": "Provider Added", "id": ' . $id . '}}';
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Update Provider
$app->put('/provider/update/{providerId}', function (Request $request, Response $response) {
    $providerId = $request->getAttribute('providerId');
    $name = $request->getParam('name');
    $city = $request->getParam('city');
    $country = $request->getParam('country');
    $sql = "UPDATE providers SET
                name = :name,
                city = :city,
                country = :country
            WHERE providerId = $providerId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->bindParam(':name', $name);
        $stmt->bindParam(':city', $city);
        $stmt->bindParam(':country', $country);
        $stmt->execute();
        $db = null;
        echo '{"notice": {"text": "Provider Updated"}}';
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Delete Provider
$app->delete('/provider/delete/{providerId}', function (Request $request, Response $response) {
    $providerId = $request->getAttribute('providerId');
    $sql = "DELETE FROM providers WHERE providerId = $providerId";
    $sql2 = "UPDATE products SET providerId = 0 WHERE providerId = $providerId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->execute();
        $stmt2 = $db->prepare($sql2);
        $stmt2->execute();
        $db = null;
        echo '{"notice": {"text": "Provider Deleted"}}';
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
/* ------------------
Stocks
--------------------*/
// Get All Products in stock
$app->get('/stocks', function (Request $request, Response $response) {
    //$sql = "SELECT * FROM products LEFT OUTER JOIN providers ON products.providerId = providers.providerId LEFT OUTER JOIN stocks ON products.productId = stocks.productId";
		$sql = "SELECT * FROM products,providers,stocks,achats WHERE products.productId = stocks.productId AND products.providerId = providers.providerId AND products.productId = achats.productId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $products = $stmt->fetchAll(PDO::FETCH_OBJ);
        $result = array();
        $index = 0;
        foreach ($products as $product) {
            $result[$index]['productId'] = $product->productId;
            if (($product->providerId != 0)) {
							$result[$index]['provider'] = array("providerId" => $product->providerId, "name" => $product->name, "city" => $product->city, "country" => $product->country);
            }
            $result[$index]['code'] = $product->code;
            $result[$index]['designation'] = $product->designation;
            $result[$index]['description'] = $product->description;
            $result[$index]['category'] = $product->category;
            $result[$index]['price'] = $product->price;
						$result[$index]['purchased'] = $product->purchased;
						$result[$index]['date'] = $product->lastDate;
						$result[$index]['TVA'] = $product->TVA;
            $result[$index]['isActive'] = $product->isActive;
            $result[$index]['isFeatured'] = $product->isFeatured;
						$result[$index]['quantity'] = $product->quantity;
						$result[$index]['alert'] = $product->alert;
            $index++;
        }
        $db = null;
        echo json_encode($result);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Add Product
$app->post('/stocks/add', function (Request $request, Response $response) {
    $providerId = $request->getParam('providerId');
    $code = $request->getParam('code');
    $designation = $request->getParam('designation');
    $description = $request->getParam('description');
    $category = $request->getParam('category');
    $price = $request->getParam('price');
    $tags = explode(",", $request->getParam('tags'));
    $sql = "INSERT INTO products (providerId, code, designation, description, price, category, isActive, isFeatured) VALUES (:providerId, :code, :designation, :description, :price, :category, 1, 0)";
    $sql2 = "INSERT INTO customs (productId, description, commodityCode, statisticalCode, grossMass, netMass, permitObligation, commercialGood, customsClearanceType, nonCustomsLawObligation, statisticalValue, preference, vatCode, packagingType, packagingReferenceNumber, originCountry) VALUES (:productId, '', '', '', 0, 0, '', '', '', '', 0, 0, '', '', '', '')";
    $sql3 = "INSERT INTO tags (productId, keyword) VALUES (:productId, :keyword)";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->bindParam(':providerId', $providerId);
        $stmt->bindParam(':code', $code);
        $stmt->bindParam(':designation', $designation);
        $stmt->bindParam(':description', $description);
        $stmt->bindParam(':category', $category);
        $stmt->bindParam(':price', $price);
        $stmt->execute();
        $id = $db->lastInsertId();
        $stmt = $db->prepare($sql2);
        $stmt->bindParam(':productId', $id);
        $stmt->execute();
        foreach ($tags as $tag) {
            $stmt = $db->prepare($sql3);
            $stmt->bindParam(':productId', $id);
            $stmt->bindParam(':keyword', $tag);
            $stmt->execute();
        }
        $db = null;
        echo '{"notice": {"text": "Product Added", "id": ' . $id . '}}';
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
/* ------------------
Product
--------------------*/
// Get All Products
$app->get('/products', function (Request $request, Response $response) {
    $sql = "SELECT * FROM products LEFT OUTER JOIN providers ON products.providerId = providers.providerId";
    $sql2 = "SELECT * FROM prices WHERE productId = :productId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $products = $stmt->fetchAll(PDO::FETCH_OBJ);
        $result = array();
        $index = 0;
        foreach ($products as $product) {
            $result[$index]['productId'] = $product->productId;
            if (($product->providerId != 0)) {
                $result[$index]['provider'] = array("providerId" => $product->providerId, "name" => $product->name, "city" => $product->city, "country" => $product->country);
            }
            $result[$index]['code'] = $product->code;
            $result[$index]['designation'] = $product->designation;
            $result[$index]['description'] = $product->description;
            $result[$index]['category'] = $product->category;
            $result[$index]['price'] = $product->price;
            $result[$index]['isActive'] = $product->isActive;
            $result[$index]['isFeatured'] = $product->isFeatured;
            $stmt = $db->prepare($sql2);
            $stmt->bindParam(':productId', $product->productId);
            $stmt->execute();
            $prices = $stmt->fetchAll(PDO::FETCH_OBJ);
            $result[$index]['countPrices'] = $stmt->rowCount();
            $index++;
        }
        $db = null;
        echo json_encode($result);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Get All Products By Client
$app->get('/products/{clientId}', function (Request $request, Response $response) {
    $clientId = $request->getAttribute('clientId');
    $sql = "SELECT * FROM products LEFT OUTER JOIN providers ON products.providerId = providers.providerId ORDER BY products.designation";
    $sql2 = "SELECT * FROM prices WHERE clientId = $clientId";
    $sql3 = "SELECT keyword FROM tags WHERE productId = :productId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $products = $stmt->fetchAll(PDO::FETCH_OBJ);
        $result = array();
        $index = 0;
        foreach ($products as $product) {
            $result[$index]['productId'] = $product->productId;
            if (($product->providerId != 0)) {
                $result[$index]['provider'] = array("providerId" => $product->providerId, "name" => $product->name, "city" => $product->city, "country" => $product->country);
            }
            $result[$index]['designation'] = $product->designation;
            $result[$index]['description'] = $product->description;
            $result[$index]['category'] = $product->category;
            $result[$index]['price'] = $product->price;
            $result[$index]['isActive'] = $product->isActive;
            $stmt2 = $db->prepare($sql3);
            $stmt2->bindParam(':productId', $product->productId);
            $stmt2->execute();
            $tags = $stmt2->fetchAll(PDO::FETCH_OBJ);
            $keywords = array();
            foreach ($tags as $tag) {
                array_push($keywords, $tag->keyword);
            }
            $result[$index]['tags'] = $keywords;
            $index++;
        }
        $stmt = $db->query($sql2);
        $prices = $stmt->fetchAll(PDO::FETCH_OBJ);
        foreach ($prices as $price) {
            $index = array_search($price->productId, array_column($result, 'productId'));
            $result[$index]['price'] = $price->specialPrice;
        }
        $db = null;
        echo json_encode($result);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Get Products Categories
$app->get('/categories', function (Request $request, Response $response){
    $sql = "SELECT category, count(*) AS count FROM products WHERE products.isActive = 1 GROUP BY category";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $categories = $stmt->fetchAll(PDO::FETCH_OBJ);
        $db = null;
        echo json_encode($categories);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Get Single Product
$app->get('/product/{productId}', function (Request $request, Response $response) {
    $productId = $request->getAttribute('productId');
    $sql = "SELECT * FROM products WHERE productId = $productId";
    $sql2 = "SELECT keyword FROM tags WHERE productId = $productId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $product = $stmt->fetch(PDO::FETCH_OBJ);
        $result = array();
        $result['productId'] = $product->productId;
        $result['providerId'] = $product->providerId;
        $result['code'] = $product->code;
        $result['designation'] = $product->designation;
        $result['description'] = $product->description;
        $result['price'] = $product->price;
        $result['category'] = $product->category;
        $stmt2 = $db->prepare($sql2);
        $stmt2->execute();
        $tags = $stmt2->fetchAll(PDO::FETCH_OBJ);
        $keywords = array();
        foreach ($tags as $tag) {
            array_push($keywords, $tag->keyword);
        }
        $tags = join(",", $keywords);
        $result['tags'] = $tags;
        $db = null;
        echo json_encode($result);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Add Product
$app->post('/product/add', function (Request $request, Response $response) {
    $providerId = $request->getParam('providerId');
    $code = $request->getParam('code');
    $designation = $request->getParam('designation');
    $description = $request->getParam('description');
    $category = $request->getParam('category');
    $price = $request->getParam('price');
    $tags = explode(",", $request->getParam('tags'));
    $sql = "INSERT INTO products (providerId, code, designation, description, price, category, isActive, isFeatured) VALUES (:providerId, :code, :designation, :description, :price, :category, 1, 0)";
    $sql2 = "INSERT INTO customs (productId, description, commodityCode, statisticalCode, grossMass, netMass, permitObligation, commercialGood, customsClearanceType, nonCustomsLawObligation, statisticalValue, preference, vatCode, packagingType, packagingReferenceNumber, originCountry) VALUES (:productId, '', '', '', 0, 0, '', '', '', '', 0, 0, '', '', '', '')";
    $sql3 = "INSERT INTO tags (productId, keyword) VALUES (:productId, :keyword)";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->bindParam(':providerId', $providerId);
        $stmt->bindParam(':code', $code);
        $stmt->bindParam(':designation', $designation);
        $stmt->bindParam(':description', $description);
        $stmt->bindParam(':category', $category);
        $stmt->bindParam(':price', $price);
        $stmt->execute();
        $id = $db->lastInsertId();
        $stmt = $db->prepare($sql2);
        $stmt->bindParam(':productId', $id);
        $stmt->execute();
        foreach ($tags as $tag) {
            $stmt = $db->prepare($sql3);
            $stmt->bindParam(':productId', $id);
            $stmt->bindParam(':keyword', $tag);
            $stmt->execute();
        }
        $db = null;
        echo '{"notice": {"text": "Product Added", "id": ' . $id . '}}';
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Update Product
$app->put('/product/update/{productId}', function (Request $request, Response $response) {
    $productId = $request->getAttribute('productId');
    $providerId = $request->getParam('providerId');
    $code = $request->getParam('code');
    $designation = $request->getParam('designation');
    $description = $request->getParam('description');
    $category = $request->getParam('category');
    $price = $request->getParam('price');
    $tags = explode(",", $request->getParam('tags'));
    $sql = "UPDATE products SET
                providerId = :providerId,
				code = :code,
                designation = :designation,
                description = :description,
                category =  :category,
                price = :price
            WHERE productId = $productId";
    $sql2 = "DELETE FROM tags WHERE productId = $productId";
    $sql3 = "INSERT INTO tags (productId, keyword) VALUES (:productId, :keyword)";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->bindParam(':providerId', $providerId);
        $stmt->bindParam(':code', $code);
        $stmt->bindParam(':designation', $designation);
        $stmt->bindParam(':description', $description);
        $stmt->bindParam(':category', $category);
        $stmt->bindParam(':price', $price);
        $stmt->execute();
        $stmt = $db->prepare($sql2);
        $stmt->execute();
        foreach ($tags as $tag) {
            $stmt = $db->prepare($sql3);
            $stmt->bindParam(':productId', $productId);
            $stmt->bindParam(':keyword', $tag);
            $stmt->execute();
        }
        $db = null;
        echo '{"notice": {"text": "Product Updated"}}';
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Active Product
$app->put('/product/activate/{productId}', function (Request $request, Response $response) {
    $productId = $request->getAttribute('productId');
    $isActive = $request->getParam('isActive');
    $sql = "UPDATE products SET
                isActive = :isActive
            WHERE productId = $productId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->bindParam(':isActive', $isActive);
        $stmt->execute();
        $db = null;
        echo '{"notice": {"text": "Product Updated"}}';
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Featured Product
$app->put('/product/featured/{productId}', function (Request $request, Response $response) {
    $productId = $request->getAttribute('productId');
    $isFeatured = $request->getParam('isFeatured');
    $sql = "UPDATE products SET
                isFeatured = :isFeatured
            WHERE productId = $productId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->bindParam(':isFeatured', $isFeatured);
        $stmt->execute();
        $db = null;
        echo '{"notice": {"text": "Product Updated"}}';
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Delete Product
$app->delete('/product/delete/{productId}', function (Request $request, Response $response) {
    $productId = $request->getAttribute('productId');
    $sql = "DELETE FROM products WHERE productId = $productId";
    $sql2 = "DELETE FROM prices WHERE productId = $productId";
    $sql3 = "DELETE FROM customs WHERE productId = $productId";
    $sql4 = "DELETE FROM tags WHERE productId = $productId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->execute();
        $stmt = $db->prepare($sql2);
        $stmt->execute();
        $stmt = $db->prepare($sql3);
        $stmt->execute();
        $stmt = $db->prepare($sql4);
        $stmt->execute();
        $db = null;
        echo '{"notice": {"text": "Product Deleted"}}';
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
/* ------------------
Price
--------------------*/
// Get All Prices By Product
$app->get('/prices/{productId}', function (Request $request, Response $response) {
    $productId = $request->getAttribute('productId');
    $sql = "SELECT * FROM prices WHERE productId = $productId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $prices = $stmt->fetchAll(PDO::FETCH_OBJ);
        $db = null;
        echo json_encode($prices);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Add Price
$app->post('/price/add', function (Request $request, Response $response) {
    $productId = $request->getParam('productId');
    $clientId = $request->getParam('clientId');
    $specialPrice = $request->getParam('specialPrice');
    $sql = "INSERT INTO prices (productId, clientId, specialPrice) VALUES (:productId, :clientId, :specialPrice)";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->bindParam(':productId', $productId);
        $stmt->bindParam(':clientId', $clientId);
        $stmt->bindParam(':specialPrice', $specialPrice);
        $stmt->execute();
        $db = null;
        echo '{"notice": {"text": "Price Added"}}';
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Update Price
$app->put('/price/update/{productId}/{clientId}', function (Request $request, Response $response) {
    $productId = $request->getAttribute('productId');
    $clientId = $request->getAttribute('clientId');
    $specialPrice = $request->getParam('specialPrice');
    $sql = "UPDATE prices SET
                specialPrice = :specialPrice
            WHERE productId = $productId AND clientId = $clientId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->bindParam(':specialPrice', $specialPrice);
        $stmt->execute();
        $db = null;
        echo '{"notice": {"text": "Price Updated"}}';
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Delete Price
$app->delete('/price/delete/{productId}/{clientId}', function (Request $request, Response $response) {
    $productId = $request->getAttribute('productId');
    $clientId = $request->getAttribute('clientId');
    $sql = "DELETE FROM prices WHERE productId = $productId AND clientId = $clientId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->execute();
        $db = null;
        echo '{"notice": {"text": "Price Deleted"}}';
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
/* ------------------
Custom
--------------------*/
// Get All Customs
$app->get('/customs', function (Request $request, Response $response) {
    $sql = "SELECT * FROM customs";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $customs = $stmt->fetchAll(PDO::FETCH_OBJ);
        $db = null;
        echo json_encode($customs);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Get Single Custom By Product
$app->get('/custom/{productId}', function (Request $request, Response $response) {
    $productId = $request->getAttribute('productId');
    $sql = "SELECT * FROM customs WHERE productId = $productId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $customs = $stmt->fetch(PDO::FETCH_OBJ);
        $db = null;
        echo json_encode($customs);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Update Custom
$app->put('/custom/update/{productId}', function (Request $request, Response $response) {
    $productId = $request->getAttribute('productId');
    $description = $request->getParam('description');
    $commodityCode = $request->getParam('commodityCode');
    $statisticalCode = $request->getParam('statisticalCode');
    $grossMass = $request->getParam('grossMass');
    $netMass = $request->getParam('netMass');
    $permitObligation = $request->getParam('permitObligation');
    $nonCustomsLawObligation = $request->getParam('nonCustomsLawObligation');
    $customsClearanceType = $request->getParam('customsClearanceType');
    $commercialGood = $request->getParam('commercialGood');
    $statisticalValue = $request->getParam('statisticalValue');
    $preference = $request->getParam('preference');
    $packagingType = $request->getParam('packagingType');
    $packagingReferenceNumber = $request->getParam('packagingReferenceNumber');
    $vatCode = $request->getParam('vatCode');
    $originCountry = $request->getParam('originCountry');
    $sql = "UPDATE customs SET
                    description = :description,
                    commodityCode = :commodityCode,
                    statisticalCode = :statisticalCode,
                    grossMass = :grossMass,
                    netMass = :netMass,
                    permitObligation = :permitObligation,
                    nonCustomsLawObligation = :nonCustomsLawObligation,
                    customsClearanceType  = :customsClearanceType,
                    commercialGood = :commercialGood,
                    statisticalValue = :statisticalValue,
                    preference = :preference,
                    packagingType = :packagingType,
                    packagingReferenceNumber = :packagingReferenceNumber,
                    vatCode = :vatCode,
                    originCountry = :originCountry
                 WHERE productId = $productId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->bindParam(':description', $description);
        $stmt->bindParam(':commodityCode', $commodityCode);
        $stmt->bindParam(':statisticalCode', $statisticalCode);
        $stmt->bindParam(':grossMass', $grossMass);
        $stmt->bindParam(':netMass', $netMass);
        $stmt->bindParam(':permitObligation', $permitObligation);
        $stmt->bindParam(':nonCustomsLawObligation', $nonCustomsLawObligation);
        $stmt->bindParam(':customsClearanceType', $customsClearanceType);
        $stmt->bindParam(':commercialGood', $commercialGood);
        $stmt->bindParam(':statisticalValue', $statisticalValue);
        $stmt->bindParam(':preference', $preference);
        $stmt->bindParam(':packagingType', $packagingType);
        $stmt->bindParam(':packagingReferenceNumber', $packagingReferenceNumber);
        $stmt->bindParam(':vatCode', $vatCode);
        $stmt->bindParam(':originCountry', $originCountry);
        $stmt->execute();
        $db = null;
        echo '{"notice": {"text": "Custom Updated"}}';
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
/* ------------------
Order
--------------------*/
// Get All Orders
$app->get('/orders', function (Request $request, Response $response) {
    $sql = "SELECT * FROM orders";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $orders = $stmt->fetchAll(PDO::FETCH_OBJ);
        $db = null;
        echo json_encode($orders);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Get All Orders By Client
$app->get('/orders/client/{clientName}', function (Request $request, Response $response) {
    $clientName = addslashes(urldecode($request->getAttribute('clientName')));
    $sql = "SELECT * FROM orders WHERE clientName = '$clientName'";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $orders = $stmt->fetchAll(PDO::FETCH_OBJ);
        $db = null;
        echo json_encode($orders);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Add Order
$app->post('/order/add', function (Request $request, Response $response) {
    $basket = $request->getParam('basket');
    $companyName = $request->getParam('companyName');
    $street = $request->getParam('street');
    $postalCode = $request->getParam('postalCode');
    $city = $request->getParam('city');
    $total = $request->getParam('total');
    $sql = "INSERT INTO orders(clientName, street, postalCode, city, date, total, state) VALUES (:clientName,:street,:postalCode,:city, NOW(),:total,0)";
    $sql2 = "INSERT INTO articles(orderId, designation, price, tva, quantity, packagingType, providerName, state) VALUES (:orderId, :designation, :price, :tva, :quantity, 'RR', :providerName, 0)";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->bindParam(':clientName', $companyName);
        $stmt->bindParam(':street', $street);
        $stmt->bindParam(':postalCode', $postalCode);
        $stmt->bindParam(':city', $city);
        $stmt->bindParam(':total', $total);
        $stmt->execute();
        $orderId = $db->lastInsertId();
        foreach ($basket as $article) {
            $stmt = $db->prepare($sql2);
            $stmt->bindParam(':orderId', $orderId);
            $stmt->bindParam(':designation', $article['designation']);
            $stmt->bindParam(':price', $article['price']);
            $stmt->bindParam(':tva', $article['TVA']);
            $stmt->bindParam(':quantity', $article['quantity']);
            $stmt->bindParam(':providerName', $article['providerName']);
            $stmt->execute();
        }
		$session = new \Slim\Session();
		$session->set('basket', null);
        $db = null;
        echo '{"notice": {"text": "Order Added"}, "orderId": '.$orderId.'}';
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Get All Articles By Order
$app->get('/articles/{orderId}/{clientId}', function (Request $request, Response $response) {
    $orderId = $request->getAttribute('orderId');
    $clientId = $request->getAttribute('clientId');
    if ($clientId == "admin"){
    	$sql = "SELECT * FROM articles WHERE orderId = $orderId";
    } else {
    	$sql = "SELECT * FROM `articles`,clients,orders WHERE articles.orderId = orders.orderId AND clients.companyName = orders.clientName AND clients.clientId = $clientId AND orders.orderId= $orderId";
    }
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $articles = $stmt->fetchAll(PDO::FETCH_OBJ);
        $db = null;
        echo json_encode($articles);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Get Exports By Orders
$app->get('/exports', function (Request $request, Response $response) {
    $sql = "SELECT * FROM orders WHERE state <> 2";
    $sql2 = "SELECT * FROM articles WHERE orderId = :orderId AND state = 0 ORDER BY providerName";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $orders = $stmt->fetchAll(PDO::FETCH_OBJ);
        $result = array();
        $index = 0;
        foreach ($orders as $order) {
            $result[$index]['orderId'] = $order->orderId;
            $stmt = $db->prepare($sql2);
            $stmt->bindParam(':orderId', $order->orderId);
            $stmt->execute();
            $articles = $stmt->fetchAll(PDO::FETCH_OBJ);
            $result[$index]['articles'] = $articles;
            $index++;
        }
        $db = null;
        echo json_encode($result);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Get Exports By Providers
$app->get('/exports2', function (Request $request, Response $response) {
    $sql = "SELECT DISTINCT providerName FROM articles WHERE state = 0 ORDER BY providerName";
    $sql2 = "SELECT * FROM articles WHERE providerName = :providerName AND state = 0 ORDER BY orderId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $providers = $stmt->fetchAll(PDO::FETCH_OBJ);
        $result = array();
        $index = 0;
        foreach ($providers as $provider) {
            $result[$index]['providerName'] = $provider->providerName;
            $stmt = $db->prepare($sql2);
            $stmt->bindParam(':providerName', $provider->providerName);
            $stmt->execute();
            $articles = $stmt->fetchAll(PDO::FETCH_OBJ);
            $result[$index]['articles'] = $articles;
            $index++;
        }
        $db = null;
        echo json_encode($result);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Update Articles And Orders
$app->put('/orders/update', function (Request $request, Response $response) {
    $articles = $request->getParam('articles');
    $articles_ids = join(',', $articles);
    $sql = "UPDATE articles SET state = 1 WHERE articleId IN ($articles_ids)";
    $sql5 = "SELECT DISTINCT orderId FROM articles WHERE articleId IN ($articles_ids)";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        // Update Articles
        $stmt = $db->prepare($sql);
        $stmt->execute();
        // Select Orders To Update
        $stmt = $db->prepare($sql5);
        $stmt->execute();
        $orders = $stmt->fetchAll(PDO::FETCH_OBJ);
        $orders_update_ids = array();
        $orders_array = array();
        foreach ($orders as $order) {
            array_push($orders_update_ids, $order->orderId);
        }
        $orders_ids = join(',', $orders_update_ids);
        // Select Orders To Update
        $sql4 = "SELECT orderId, state, COUNT(*) AS nbr FROM articles WHERE state IN (0,1) AND orderId IN ($orders_ids) GROUP BY orderId, state";
        $stmt = $db->prepare($sql4);
        $stmt->execute();
        $orders = $stmt->fetchAll(PDO::FETCH_OBJ);
        $orders_update_1 = array();
        $orders_update_2 = array();
        $index = 0;
        $previousId = '';
        foreach ($orders as $order) {
            if ($previousId != $order->orderId) {
                if ($order->state == 0) {
                    $temp = array("orderId" => $order->orderId, "state0" => intval($order->nbr), "state1" => 0);
                } else {
                    $temp = array("orderId" => $order->orderId, "state0" => 0, "state1" => intval($order->nbr));
                }
                array_push($orders_array, $temp);
            } else {
                $orders_array[$index]['state1'] = $order->nbr;
                $index++;
            }
            $previousId = $order->orderId;
        }
        foreach ($orders_array as $order) {
            if ($order['state0'] >= 1 && $order['state1'] >= 1) {
                array_push($orders_update_1, $order['orderId']);
            }
            if ($order['state0'] == 0 && $order['state1'] != 0) {
                array_push($orders_update_2, $order['orderId']);
            }
        }
        // Update Orders 1
        if (sizeof($orders_update_1) != 0) {
            $orders_ids_1 = join(',', $orders_update_1);
            $sql2 = "UPDATE orders SET state = 1 WHERE orderId IN ($orders_ids_1)";
            $stmt = $db->prepare($sql2);
            $stmt->execute();
        }
        // Update Orders 2
        if (sizeof($orders_update_2) != 0) {
            $orders_ids_2 = join(',', $orders_update_2);
            $sql3 = "UPDATE orders SET state = 2 WHERE orderId IN ($orders_ids_2)";
            $stmt = $db->prepare($sql3);
            $stmt->execute();
        }
        echo '{"notice": {"text": "Article Updated"}}';
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Load Basket Favorite
$app->POST('/basketLoading/{orderId}/{clientId}', function (Request $request, Response $response) {
    $orderId = $request->getAttribute('orderId');
    $clientId = $request->getAttribute('clientId');
    $sql = "SELECT designation, quantity FROM articles WHERE articles.orderId = $orderId";
    $sql2 = "SELECT * FROM products LEFT OUTER JOIN providers ON products.providerId = providers.providerId WHERE products.designation = :designation";
    $sql3 = "SELECT packagingType, vatCode FROM customs WHERE productId = :productId";
    $sql4 = "SELECT specialPrice FROM prices WHERE productId = :productId AND clientId = $clientId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $session = new \Slim\Session();
        $result = array();
        $sessionBasket = array();
        while ($articles = $stmt->fetch(PDO::FETCH_OBJ)) {
            $basket = array();
            $stmt2 = $db->prepare($sql2);
            $stmt2->bindParam(':designation', $articles->designation);
            $stmt2->execute();
            $product = $stmt2->fetch(PDO::FETCH_OBJ);
            $stmt2 = $db->prepare($sql3);
            $stmt2->bindParam(':productId', $product->productId);
            $stmt2->execute();
            $custom = $stmt2->fetch(PDO::FETCH_OBJ);
            $stmt2 = $db->prepare($sql4);
            $stmt2->bindParam(':productId', $product->productId);
            $stmt2->execute();
            $price = $stmt2->fetch(PDO::FETCH_OBJ);
            $count = $stmt2->rowCount();
            if($product->isActive == 1)  {
            $basket['productId'] = $product->productId;
            $basket['designation'] = $product->designation;
            $basket['description'] = $product->description;
            if ($count == 0) {
                $basket['price'] = $product->price;
            } else {
                $basket['price'] = $price->specialPrice;
            }
            $basket['quantity'] = $articles->quantity;
            $basket['packagingType'] = $custom->packagingType;
            $basket['vatCode'] = $product->TVA;
            $basket['providerName'] = $product->name;

            array_push($sessionBasket,$basket); 
            }

        }
        $session->set('basket',$sessionBasket);
        $result['basket'] = $sessionBasket;
        $db = null;
        echo json_encode($result);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Add Product Basket
$app->POST('/basket/{productId}/{clientId}', function (Request $request, Response $response) {
    $productId = $request->getAttribute('productId');
    $clientId = $request->getAttribute('clientId');
	$quantity = $request->getParam('quantity');
    $sql = "SELECT * FROM products  LEFT OUTER JOIN providers ON products.providerId = providers.providerId WHERE products.productId = $productId";
    //$sql2 = "SELECT packagingType, vatCode FROM customs WHERE productId = $productId";
    $sql3 = "SELECT specialPrice FROM prices WHERE productId = $productId AND clientId = $clientId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $product = $stmt->fetch(PDO::FETCH_OBJ);
        //$stmt = $db->query($sql2);
        //$custom = $stmt->fetch(PDO::FETCH_OBJ);
        $stmt = $db->query($sql3);
        $price = $stmt->fetch(PDO::FETCH_OBJ);
        $count = $stmt->rowCount();
        $result = array();
        $result['productId'] = $product->productId;
        $result['designation'] = $product->designation;
        $result['description'] = $product->description;
		$result['TVA'] = $product->TVA;
        if ($count == 0) {
            $result['price'] = $product->price;
        } else {
            $result['price'] = $price->specialPrice;
        }
        $result['quantity'] = $quantity;
        //$result['packagingType'] = $custom->packagingType;
        //$result['vatCode'] = $custom->vatCode;
        $result['providerName'] = $product->name;
		$session = new \Slim\Session();
		$sessionBasket = $session->basket;
        if ($sessionBasket == null) {
			$sessionBasket = array();
	    }
		else {
			$i = 0;
foreach ($sessionBasket as $article) {
	
    if($article['productId'] == $product->productId){
		break;
	} 
			$i++;
		
}
		}
		
		if($i < sizeOf($sessionBasket) && sizeOf($sessionBasket)>=1){
		$sessionBasket[$i]['quantity'] = $sessionBasket[$i]['quantity'] + $quantity ;
		}
		else{
			
		array_push($sessionBasket,$result);	
		
		}
		$session->set('basket',$sessionBasket);
		$result['basket'] = $sessionBasket;
		$db = null;
        echo json_encode($result);
		
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Edit Product Basket
$app->post('/basket/edit', function (Request $request, Response $response) {
    $productId = $request->getParam('productId');
    $quantity = $request->getParam('quantity');
 	$session = new \Slim\Session();
	$sessionBasket = $session->basket;
	$i = 0;

	foreach ($sessionBasket as $article) {
		if($article['productId'] == $productId){
			break;
		}
		$i++;
		
	}	
	if($quantity == 0){
			$test = array();
	foreach ($sessionBasket as $article) {
		if($article['productId'] != $productId){
			array_push($test,$article);
		}
		$sessionBasket = $test;
	}	
	} else {
		$sessionBasket[$i]['quantity'] = $quantity;		
	}
	$session->set('basket',$sessionBasket);
			  echo json_encode($sessionBasket);
});
// Invoice control
$app->get('/invoiceControl', function (Request $request, Response $response) {
	$sql = "SELECT code,PA,TVA FROM products";
	try {
			// Get DB Object
			$db = new db();
			// Connect
			$db = $db->connect();
			$stmt = $db->query($sql);
			$products = $stmt->fetchAll(PDO::FETCH_OBJ);
			$result = array();
			$index = 0;
			foreach ($products as $product) {
					$result[$index]['code'] = $product->code;
					$result[$index]['PA'] = $product->PA;
					$result[$index]['TVA'] = $product->TVA;
					$index++;
			}
			$db = null;
			echo json_encode($result);
	}
	catch(PDOException $e) {
			echo '{"error": {"text": "' . $e->getMessage() . '"}}';
	}
});
