<?php
if (! defined('sugarEntry') || ! sugarEntry)
	die('Not A Valid Entry Point');

/**
 * **************************************************
 */
/**
 * ********************* PREPARE ********************
 */
/**
 * **************************************************
 */
$db = $GLOBALS['db'];

$data = array();
$categoryArray = array();

$sum = array();
$sumSub = array();
$sumSub2 = array();

$KSGROUPS = array();

$group['id'] = '1';
$group['name'] = 'Towar handlowy';
$KSGROUPS[] = $group;

$group['id'] = '2';
$group['name'] = 'Wyrób gotowy';
$KSGROUPS[] = $group;

$group['id'] = '3';
$group['name'] = 'Surowiec';
$KSGROUPS[] = $group;

$group['id'] = '4';
$group['name'] = 'Transport';
$KSGROUPS[] = $group;

if ($_GET['selectProductActive'] != "") {
	$selectProductActive = $_GET['selectProductActive'];
} else {
	$selectProductActive = "";
}

if ($_GET['selectProductEol'] != "") {
	$selectProductEol = $_GET['selectProductEol'];
} else {
	$selectProductEol = "";
}

if ($_GET['selectStock'] != "") {
	$selectStock = $_GET['selectStock'];
} else {
	$selectStock = "";
}

$datastocks = array();
$users = array();
$queryStocks = "SELECT
					name,
					id
					FROM ecmstocks
					where deleted= 0";
$rowsStocks = $db->query($queryStocks);

while ($rowStocks = $db->fetchByAssoc($rowsStocks)) {
	$stocks["name"] = $rowStocks["name"];
	$stocks["id"] = $rowStocks["id"];
	$datastocks[] = $stocks;
}

$query = "SELECT 
    p.id, p.name, p.code, SUM(ss.quantity) as quantity, SUM(ss.quantity * ss.price) as price
FROM
    ecmproducts as p
INNER JOIN ecmstockstates AS ss
ON ss.product_id = p.id
WHERE
    p.deleted = '0' AND ss.quantity IS NOT NULL";

if ($_GET['selectStock'] != "")
	$query .= " AND ss.stock_id = '" . $_GET["selectStock"] . "' ";

if ($_GET['selectProductActive'] != "")
	$query .= " AND p.product_active = '" . $_GET["selectProductActive"] . "' ";

if ($_GET['selectProductEol'] != "") {
	if ($_GET['selectProductEol'] == "1")
		$query .= " AND p.status='end_of_line' ";
	if ($_GET['selectProductEol'] == "0")
		$query .= " AND p.status!='end_of_line' ";
}
if ($_GET['productKS'] != '') {
	$query .= " AND p.group_ks = '" . $_GET["productKS"] . "' ";
}
$query .= " GROUP BY p.id";

/**
 * **************************************************
 */
/**
 * ************* GET DATA FROM DB********************
 */
/**
 * **************************************************
 */
$rows = $db->query($query);

// prepare data for Smarty
while ($r = $db->fetchByAssoc($rows)) {
	$row = array();
	$row["id"] = $r["id"];
	$row["name"] = $r["name"];
	$row["code"] = $r["code"];
	$row["quantity"] = $r["quantity"];
	$row["price"] = $r["price"];

	// stock addresses
	$a = $db->query("SELECT stock_address FROM ecmproducts_stock_addresses WHERE ecmproduct_id='" . $r['id'] . "' ORDER BY stock_address");
	$addresses = array();
	while ($addr = $db->fetchByAssoc($a)) {
		$addresses[] = $addr['stock_address'];
	}
	$row['stock_address'] = implode(", ", $addresses);

	$querySubCategory = "SELECT category.name as 'podkategoria'
											FROM 			
												ecmproductcategories_bean bean
													JOIN
												ecmproductcategories category ON bean.ecmproductcategory_id = category.id
											WHERE bean.bean_id='" . $row["id"] . "'
											and bean.position = '1'
											and bean.deleted = '0'
											and category.deleted = '0';";
	$rowsSubCategory = $db->query($querySubCategory);
	$row["podkategoria"] = "";
	while ($rowSubCategory = $db->fetchByAssoc($rowsSubCategory)) {
		$row["podkategoria"] = $rowSubCategory["podkategoria"];
	}

	$queryCategory = "SELECT category.name as 'kategoria'
											FROM 			
												ecmproductcategories_bean bean
													JOIN
												ecmproductcategories category ON bean.ecmproductcategory_id = category.id
											WHERE bean.bean_id='" . $row["id"] . "'
											and bean.position = '0'
										    and bean.deleted = '0'
											and category.deleted = '0';";
	$rowscategory = $db->query($queryCategory);
	$row["kategoria"] = "";
	while ($rowcategory = $db->fetchByAssoc($rowscategory)) {
		$row["kategoria"] = $rowcategory["kategoria"];
	}
	$data[] = $row;
}

// //////////////// SUM /////////////////////////////////
foreach ($data as $key => &$element) {
	$categoryArray1[$element["kategoria"] == "" ? "Inne" : $element["kategoria"]][$element["podkategoria"] == "" ? "Reszta" : $element["podkategoria"]][$key] = $element;
}
function cmp($a, $b)
{
	if ($a["name"] == $b["name"]) {
		return 0;
	}
	return ($a["name"] < $b["name"]) ? -1 : 1;
}

// grupowanie po kategoriach
foreach ($categoryArray1 as $key1 => &$element1) {
	foreach ($element1 as $key2 => &$element2) {
		usort($element2, "cmp");
		$i = 0;
		$tmp2[$key1][$key2][0] = array(
			'name' => null
		);
		$counter = 0;

		foreach ($element2 as $count => &$element3) {
			if ($element3["id"] != $tmp2[$key1][$key2][$i]["id"]) {

				if ($tmp2[$key1][$key2][$i]["id"] != null) {
					$i++;
				}

				$tmp2[$key1][$key2][$i]["id"] = $element3["id"];
				$tmp2[$key1][$key2][$i]["name"] = $element3["name"];
				$tmp2[$key1][$key2][$i]["code"] = $element3["code"];
				$tmp2[$key1][$key2][$i]["stock_address"] = $element3["stock_address"];
				$tmp2[$key1][$key2][$i]["quantity"] = $element3["quantity"];
				$tmp2[$key1][$key2][$i]["price"] = $element3["price"];
			}

			$counter += 1;

			/*
			if ($element2 [$count] ["name"] != $element2 [$count + 1] ["name"]) {
				$tmp2 [$key1] [$key2] [$i] ["marza"] = $tmp2 [$key1] [$key2] [$i] ["marza"] / $counter;
				$tmp2 [$key1] [$key2] [$i] ["srednia"] = $tmp2 [$key1] [$key2] [$i] ["srednia"] / $counter;
				$counter = 0;
			}
			*/
		}
		$categoryArray = $tmp2;
	}
}
foreach ($categoryArray as $key => &$element) {
	$count = 0;
	foreach ($element as $keyy => &$elementt) {
		foreach ($elementt as $keyyy => &$elementtt) {
			$sumSub["QuantitySum"][$key] += $elementtt["quantity"];
			$sumSub["PriceSum"][$key] += $elementtt["price"];

			$sumSub2["QuantitySum"][$key][$keyy] += $elementtt["quantity"];
			$sumSub2["PriceSum"][$key][$keyy] += $elementtt["price"];
		}
		$count += sizeof($elementt);

		$categoryArray[$key][$keyy]["QuantitySum2"] = $sumSub2["QuantitySum"][$key][$keyy];
		$categoryArray[$key][$keyy]["PriceSum2"] = $sumSub2["PriceSum"][$key][$keyy];
	}
	$categoryArray[$key]["QuantitySum"] = $sumSub["QuantitySum"][$key];
	$categoryArray[$key]["PriceSum"] = $sumSub["PriceSum"][$key];
}

foreach ($categoryArray as $key => &$element) {
	$sum["QuantitySumSum"] += $sumSub["QuantitySum"][$key];
	$sum["PriceSumSum"] += $sumSub["PriceSum"][$key];
}

foreach ($categoryArray as $key1 => &$element) {
	foreach ($element as $key2 => &$elements) {
		foreach ($element as $key3 => &$element2) {
			if ($sumSub2["PriceSum"][$key1][$key2] > $sumSub2["PriceSum"][$key1][$key3]) {
				$tmp = $sumSub2["PriceSum"][$key1][$key2];
				$sumSub2["PriceSum"][$key1][$key2] = $sumSub2["PriceSum"][$key1][$key3];
				$sumSub2["PriceSum"][$key1][$key3] = $tmp;
			}
		}
	}
}

foreach ($sumSub["PriceSum"] as $key1 => &$element) {
	foreach ($sumSub["PriceSum"] as $key2 => &$element2) {
		if ($sumSub["PriceSum"][$key1] > $sumSub["PriceSum"][$key2]) {
			$tmp = $sumSub["PriceSum"][$key1];
			$sumSub["PriceSum"][$key1] = $sumSub["PriceSum"][$key2];
			$sumSub["PriceSum"][$key2] = $tmp;
		}
	}
}

// //////////////// SORT array /////////////////////////////////
$newArray2;
foreach ($sumSub["PriceSum"] as $key1 => &$element1) {
	foreach ($categoryArray as $key2 => &$element2) {
		if ($sumSub["PriceSum"][$key1] == $categoryArray[$key2]["PriceSum"]) {
			$newArray2[$key2] = $categoryArray[$key2];
		}
	}
}
$newArray1;
foreach ($newArray2 as $key1 => &$element1) {
	foreach ($element1 as $key2 => &$element2) {
		foreach ($element1 as $key3 => &$element3) {
			if ($sumSub2["PriceSum"][$key1][$key2] == $newArray2[$key1][$key3]["PriceSum2"]) {
				$newArray1[$key1][$key3] = $newArray2[$key1][$key3];
			}
		}
	}
}


/**
 * **************************************************
 */
/**
 * ******************** SMARTY **********************
 */
/**
 * **************************************************
 */
// create & execute smarty
$smarty = new Sugar_Smarty();
global $mod_strings;
$smarty->assign("MOD", $mod_strings);
$smarty->assign("DATA", $newArray1);
$smarty->assign("SUM", $sum);
$smarty->assign('KSGROUPS', $KSGROUPS);
$smarty->assign("STOCKS", $datastocks);
$smarty->assign("selectStock", $selectStock);
$smarty->assign('selectKS', $_GET['productKS']);
$smarty->assign("selectProductActive", $selectProductActive);
$smarty->assign("selectProductEol", $selectProductEol);
echo $smarty->display('modules/EcmReports/tpls/ReportStocks.tpl');