<?php

header('Content-Type: text/html; charset=utf-8');
date_default_timezone_set('Europe/Berlin');

// Load params
$store_id = intval($_GET['store_id']);
$_sort = $_GET['sort'];
$_order = strtolower($_GET['order']);
$page = 0 + intval($_GET['page']);
$per_page = 0 + $_GET['per_page'];
$offset = ($page - 1) * $per_page;
$currency = '&euro;';

if ($store_id == 7 || $store_id == 8) {
    $currency = 'CHF';
} else if ($store_id == 14) {
    $currency = 'kr';
} else if ($store_id == 16) {
    $currency = 'zł';
}


if ($_order != 'asc' && $_order != 'desc') {
    $_order = 'desc';
}

if ($_sort != 'review_summary' && $_sort != 'price' && $_sort != 'points' && $_sort != 'date_online') {
    $_sort = 'points';
}

// Gender
$gender = array();
if (array_key_exists('gender', $_GET)) {
    if (trim($_GET['gender']) != '') {
        $genderValues = explode(',', $_GET['gender']);
        for ($i = 0; $i < count($genderValues); $i++) {
            if ((int) 0 + $genderValues[$i] > 0) {
                $gender[] = 0 + $genderValues[$i];
            }
        }
    }
}

// Price
$price = array();
if (array_key_exists('price', $_GET)) {
    if (trim($_GET['price']) != '') {
        $price = explode(',', $_GET['price']);
    }
}

// Occasions
$occasions = array();
if (array_key_exists('occasions', $_GET)) {
    if (trim($_GET['occasions']) != '') {
        $occasionsValues = explode(',', $_GET['occasions']);
        for ($i = 0; $i < count($occasionsValues); $i++) {
            if ((int) 0 + $occasionsValues[$i] > 0) {
                $occasions[] = 0 + $occasionsValues[$i];
            }
        }
    }
}

// Gift type
$gifttypes = array();
if (array_key_exists('gifttype', $_GET)) {
    if (trim($_GET['gifttype']) != '') {
        $gifttypeValues = explode(',', $_GET['gifttype']);
        for ($i = 0; $i < count($gifttypeValues); $i++) {
            if ((int) 0 + $gifttypeValues[$i] > 0) {
                $gifttypes[] = 0 + $gifttypeValues[$i];
            }
        }
    }
}

// Anlass
$anlasses = array();
if (array_key_exists('anlass', $_GET)) {
    if (trim($_GET['anlass']) != '') {
        $anlassesValues = explode(',', $_GET['anlass']);
        for ($i = 0; $i < count($anlassesValues); $i++) {
            if ((int) 0 + $anlassesValues[$i] > 0) {
                $anlasses[] = 0 + $anlassesValues[$i];
            }
        }
    }
}

// Skus
$sku = array();
if (array_key_exists('sku', $_GET)) {
    $skuArray = array();
    if (trim($_GET['sku']) != '') {
        $skuArray = explode(',', $_GET['sku']);
    }
}

$db = new mysqli("194.6.209.192", "db-user-2", "j8evRXVTCbyghKUr01aH", "db-2");
if ($db->connect_errno) {
    printf("Connect failed: %s\n", $db->connect_error);
    exit();
}

// Get products according to conditions
$priceCondition = '';
$genderCondition = '';
$occasionCondition = '';
$gifttypeCondition = '';
$anlassesCondition = '';
$queryTables = array();
$queryTablesJoins = array();
$results = array();

// genders
if (count($gender) > 0) {
    $queryResult = array();
    $queryResults = mysqli_query($db, "SELECT product_id FROM mhdirekt_giftsearch_products_genders WHERE gender_id IN (" . implode(',', $gender) . ") AND store_id = $store_id");
    while ($r = mysqli_fetch_array($queryResults)) {
        $queryResult[] = $r['product_id'];
    }
    $results[] = array_unique($queryResult);
}

// prices 
if ((count($price) < 3) && (count($price)) > 0) {
    $price_conditions = array();
    for ($i = 0; $i < count($price); $i++) {
        $price_breaks = explode('/', $price[$i]);
        $pfrom = 0 + $price_breaks[0];
        $pto = 0 + $price_breaks[1];
        if ($pfrom != $pto) {
            $price_conditions[] = "p.price >= " . (0.00 + $price_breaks[0]) . " AND p.price <= " . (0.00 + $price_breaks[1]);
        }
    }
    if (count($price_conditions) > 0) {
        $priceCondition = " AND ((" . implode(') OR (', $price_conditions) . "))";
    }
    $queryResult = array();
    $queryResults = mysqli_query($db, "SELECT p.product_id FROM mhdirekt_giftsearch_products p WHERE p.store_id = $store_id" . $priceCondition);
    while ($r = mysqli_fetch_array($queryResults)) {
        $queryResult[] = $r['product_id'];
    }
    $results[] = array_unique($queryResult);
}

// occassions
if (count($occasions) > 0) {
    $queryResult = array();
    $queryResults = mysqli_query($db, "SELECT product_id FROM mhdirekt_giftsearch_products_occassions WHERE occassion_id IN (" . implode(',', $occasions) . ") AND store_id = $store_id");
    while ($r = mysqli_fetch_array($queryResults)) {
        $queryResult[] = $r['product_id'];
    }
    $results[] = array_unique($queryResult);
}

// gifttypes
if (count($gifttypes) > 0) {
    $queryResult = array();
    $queryResults = mysqli_query($db, "SELECT product_id FROM mhdirekt_giftsearch_products_types WHERE type_id IN (" . implode(',', $gifttypes) . ") AND store_id = $store_id");
    while ($r = mysqli_fetch_array($queryResults)) {
        $queryResult[] = $r['product_id'];
    }
    $results[] = array_unique($queryResult);
}

// anlasses
if (count($anlasses) > 0) {
    $queryResult = array();
    $queryResults = mysqli_query($db, "SELECT product_id FROM mhdirekt_giftsearch_products_anlass WHERE anlass_id IN (" . implode(',', $anlasses) . ") AND store_id = $store_id");
    while ($r = mysqli_fetch_array($queryResults)) {
        $queryResult[] = $r['product_id'];
    }
    $results[] = array_unique($queryResult);
}

// get IDs of results
$resultIds = array();
if (count($results) > 1) {
    for ($i = 0; $i < (count($results) - 1); $i++) {
        if ($i == 0) {
            $resultIds = array_intersect($results[0], $results[1]);
        } else {
            $resultIds = array_intersect($resultIds, $results[$i + 1]);
        }
    }
} else if (count($results) == 1) {
    $resultIds = $results[0];
}

/* Start SKUs */
if (count($skuArray) > 0) {
    $allSkus = mysqli_query($db, "SELECT sku FROM mhdirekt_giftsearch_products WHERE store_id = $store_id");
    $allSkusArray = array();
    while ($s = mysqli_fetch_array($allSkus)) {
        $allSkusArray[] = $s['sku'];
    }
    for ($y = 0; $y < count($skuArray); $y++) {
        if (in_array(strtoupper(trim($skuArray[$y])), $allSkusArray)) {
            $sku[] = strtoupper(trim($skuArray[$y]));
        }
    }
}

$sql = "SELECT p.product_id FROM mhdirekt_giftsearch_products p WHERE store_id = $store_id";
$sql_limited = "SELECT p.product_id,p.badge,p.url_key,p.name,p.product_type,p.wishlist_count,p.thumbnail_image,p.price,short_description,p.is_in_stock,p.nextstatus,p.normal_price FROM mhdirekt_giftsearch_products p WHERE store_id = $store_id";

// Check results
if (count($results) > 0) {
    if (count($resultIds) > 0) {
        $sql .= " AND product_id IN (" . implode(',', $resultIds) . ")";
        $sql_limited .= " AND product_id IN (" . implode(',', $resultIds) . ")";
    } else {
        $sql .= " AND product_id IN (0)";
        $sql_limited .= " AND product_id IN (0)";
    }
}

// Add SKU condition only for page 1
$_productsSku = null;
if ($page == 1 && count($sku) > 0) {
    $sku_query = "SELECT product_id,badge,url_key,name,product_type,wishlist_count,thumbnail_image,price,short_description,is_in_stock,nextstatus,normal_price FROM mhdirekt_giftsearch_products WHERE store_id = $store_id AND sku IN ('" . implode("','", $sku) . "')";
    $_productsSku = mysqli_query($db, $sku_query);
    $per_page -= mysqli_num_rows($_productsSku);
    $i = mysqli_num_rows($_productsSku) + 1;
    // Remove product(s) from base query
    $sql .= " AND sku NOT IN ('" . implode("','", $sku) . "')";
    $sql_limited .= " AND sku NOT IN ('" . implode("','", $sku) . "')";
} else if (count($sku) > 0) {
    // Remove product(s) from base query
    $sql .= " AND sku NOT IN ('" . implode("','", $sku) . "')";
    $sql_limited .= " AND sku NOT IN ('" . implode("','", $sku) . "')";
}

// Add sort / limit
$sql_limited .= " ORDER BY is_in_stock desc, $_sort $_order LIMIT $per_page OFFSET $offset";

$i = 1;

if ($per_page == 0 && $offset == 0) {
    echo '0:';
    mysqli_close($db);
} else {

    $_products = mysqli_query($db, $sql);
    $_products_limited = mysqli_query($db, $sql_limited);

// Response
    $response = '';
    $configurable_word = array(3 => 'A partir de ', 6 => 'Ab ', 7 => 'Ab ', 8 => 'A partir de ', 9 => 'Da ', 10 => 'Vanaf ', 11 => 'Ab ', 12 => 'Vanaf ', 13 => 'A partir de ');
    $wishlist_word = array(3 => ' retenu', 6 => ' mal gemerkt', 7 => ' mal gemerkt', 8 => ' retenu', 9 => ' memorizzato ', 10 => ' opgemerkt', 11 => ' mal gemerkt', 12 => ' opgemerkt', 13 => ' retenu');

    if (!is_null($_productsSku)) {
        while ($_product = mysqli_fetch_array($_productsSku)) {
            $price = '';
            if ($_product['price'] < $_product['normal_price']) {
                $price .= '<span class="special-price">' . number_format($_product['price'], 2) . ' ' . $currency . '</span><span class="old-price"><span class="price-label"></span>' . number_format($_product['normal_price'], 2) . ' ' . $currency . '</span>';
            } else {
                $price .= number_format($_product['price'], 2) . ' ' . $currency;
            }

            $response .= '<div class="product-block">'
                    . '  <div class = "product-wrapper">'
                    . '      <div class="product-over-layer"></div>'
                    . 			'<img src="' . $_product['thumbnail_image'] . '" alt="' . htmlentities($_product['name']) . '" />'
                    . '      <div class="product-desc-info">
                                    <div class="product-desc-info-content">
                                        <div class="product-desc-info-content-item">
                                            <div class="product-name">' . htmlentities(mb_convert_encoding($_product['name'], 'UTF-8', 'ASCII'), ENT_SUBSTITUTE, "UTF-8") . '</div>
                                            <div class="product-des-cat">' . htmlentities(mb_convert_encoding(strip_tags($_product['short_description']), 'UTF-8', 'ASCII'), ENT_SUBSTITUTE, "UTF-8") . '</div>
                                            <div class="product-price">
                                                <div class="price-box">
                                                    <span id="product-price-67296" class="regular-price">
                                                        ' . (($_product['product_type'] == 'configurable') ? '<span class="configurable-label">' . $configurable_word[$store_id] . '</span>' : '') . $price .
                    '</span>
                                                </div>
                                                <div class="stock-info' . (((int) $_product['is_in_stock'] == 0) ? 'out-of-stock' : '') . '"></div>
                                            </div>
                                        </div>
                                    </div>
                                </div>
                            </div>
                             <a class="new-item-inner-hover product-link" onclick="redirect_with_params(\'' . $_product['url_key'] . '\');return false;" href="'.$_product['url_key'].'"></a>
                        </div>';
            $i++;
        }
    }

    while ($_product = mysqli_fetch_array($_products_limited)) {
        $price = '';
        if ($_product['price'] < $_product['normal_price']) {
            $price .= '<span class="special-price">' . number_format($_product['price'], 2) . ' ' . $currency . '</span><span class="old-price"><span class="price-label"></span>' . number_format($_product['normal_price'], 2) . ' ' . $currency . '</span>';
        } else {
            $price .= number_format($_product['price'], 2) . ' ' . $currency;
        }

        $response .= '<div class="product-block">'
                . '  <div class = "product-wrapper">'
                . '      <div class="product-over-layer"></div>'
                . 			'<img src="' . $_product['thumbnail_image'] . '" alt="' . htmlentities($_product['name']) . '" />'
                . '      <div class="product-desc-info">
                                <div class="product-desc-info-content">
                                    <div class="product-desc-info-content-item">
                                        <div class="product-name">' . htmlentities(mb_convert_encoding($_product['name'], 'UTF-8', 'ASCII'), ENT_SUBSTITUTE, "UTF-8") . '</div>
                                        <div class="product-des-cat">' . htmlentities(mb_convert_encoding(strip_tags($_product['short_description']), 'UTF-8', 'ASCII'), ENT_SUBSTITUTE, "UTF-8") . '</div>
                                        <div class="product-price">
                                            <div class="price-box">
                                                <span id="product-price-67296" class="regular-price">
                                                    ' . (($_product['product_type'] == 'configurable') ? '<span class="configurable-label">' . $configurable_word[$store_id] . '</span>' : '') . $price .
                '</span>
                                            </div>
                                            <div class="stock-info' . (((int) $_product['is_in_stock'] == 0) ? 'out-of-stock' : '') . '"></div>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                         <a class="new-item-inner-hover product-link" onclick="redirect_with_params(\'' . $_product['url_key'] . '\');return false;" href="'.$_product['url_key'].'"></a>
                    </div>';
        $i++;
    }

    echo (0 + mysqli_num_rows($_products)) . ':' . $response;
    mysqli_close($db);
}