866 lines
40 KiB
PHP
866 lines
40 KiB
PHP
|
<?php
|
||
|
|
if (! defined ( 'sugarEntry' ) || ! sugarEntry)
|
||
|
|
die ( 'Not A Valid Entry Point' );
|
||
|
|
|
||
|
|
/*****************************************************/
|
||
|
|
/*********************** PREPARE *********************/
|
||
|
|
/*****************************************************/
|
||
|
|
$db = $GLOBALS ['db'];
|
||
|
|
|
||
|
|
if($_GET['selectUser']!="")
|
||
|
|
{
|
||
|
|
$selectUser=$_GET['selectUser'];
|
||
|
|
}else{
|
||
|
|
$selectUser="";
|
||
|
|
}
|
||
|
|
|
||
|
|
$data = array();
|
||
|
|
$categoryArray = array();
|
||
|
|
|
||
|
|
$sum = array();
|
||
|
|
$sumContracor = array();
|
||
|
|
$sumCategory = array();
|
||
|
|
$sumSubCategory = array();
|
||
|
|
if(!$_GET['date_from'])
|
||
|
|
$date_from = date("Y-m-01");
|
||
|
|
else
|
||
|
|
$date_from = $_GET["date_from"];
|
||
|
|
|
||
|
|
if(!$_GET['date_to'])
|
||
|
|
$date_to = date("Y-m-d");
|
||
|
|
else
|
||
|
|
$date_to = $_GET["date_to"];
|
||
|
|
|
||
|
|
$searchByType = $_GET['type'];
|
||
|
|
|
||
|
|
$date_from_to_query = new DateTime($date_from);
|
||
|
|
$date_to_to_query = new DateTime($date_to);
|
||
|
|
|
||
|
|
$datausers = array();
|
||
|
|
$users = array();
|
||
|
|
$queryUsers="SELECT
|
||
|
|
first_name as 'first',
|
||
|
|
last_name as 'last',
|
||
|
|
id
|
||
|
|
FROM users
|
||
|
|
where deleted= 0
|
||
|
|
and status = 'Active';";
|
||
|
|
$rowsUsers= $db->query ($queryUsers);
|
||
|
|
while($rowUser = $db->fetchByAssoc ( $rowsUsers ))
|
||
|
|
{
|
||
|
|
$users["first"] = $rowUser["first"];
|
||
|
|
$users["last"] = $rowUser["last"];
|
||
|
|
$users["id"] = $rowUser["id"];
|
||
|
|
$datausers [] = $users;
|
||
|
|
}
|
||
|
|
|
||
|
|
$query = "SELECT
|
||
|
|
faktura.parent_name as 'parent',
|
||
|
|
acco.parent_id as 'parent_id',
|
||
|
|
faktura.parent_id as 'cotructor_id',
|
||
|
|
pozycja.name as 'name',
|
||
|
|
pozycja.ecmproduct_id as 'id',
|
||
|
|
produkt.code,
|
||
|
|
faktura.type as 'type',
|
||
|
|
sum(
|
||
|
|
CASE WHEN faktura.type!='correct'
|
||
|
|
THEN
|
||
|
|
CASE WHEN faktura.currency_value is null or faktura.currency_value='' or faktura.currency_value=0
|
||
|
|
THEN
|
||
|
|
pozycja.subtotal
|
||
|
|
ELSE
|
||
|
|
pozycja.subtotal*faktura.currency_value
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
|
||
|
|
CASE WHEN faktura.currency_value is null or faktura.currency_value='' or faktura.currency_value=0
|
||
|
|
THEN
|
||
|
|
pozycja.subtotal-pozycja.old_subtotal
|
||
|
|
ELSE
|
||
|
|
(pozycja.subtotal-pozycja.old_subtotal)*faktura.currency_value
|
||
|
|
END
|
||
|
|
|
||
|
|
END
|
||
|
|
) as netto,
|
||
|
|
sum(
|
||
|
|
CASE WHEN faktura.type!='correct'
|
||
|
|
THEN
|
||
|
|
pozycja.quantity
|
||
|
|
ELSE
|
||
|
|
pozycja.quantity-pozycja.old_quantity
|
||
|
|
END
|
||
|
|
) as ilosc,
|
||
|
|
sum(
|
||
|
|
CASE WHEN faktura.type!='correct'
|
||
|
|
THEN
|
||
|
|
pozycja.purchase_price*pozycja.quantity
|
||
|
|
ELSE
|
||
|
|
0
|
||
|
|
END
|
||
|
|
) as koszt
|
||
|
|
FROM
|
||
|
|
ecminvoiceoutitems pozycja
|
||
|
|
JOIN
|
||
|
|
ecminvoiceouts faktura ON pozycja.ecminvoiceout_id = faktura.id
|
||
|
|
JOIN
|
||
|
|
ecmproducts produkt ON pozycja.ecmproduct_id = produkt.id
|
||
|
|
JOIN
|
||
|
|
accounts acco ON acco.id = faktura.parent_id
|
||
|
|
WHERE
|
||
|
|
faktura.register_date BETWEEN
|
||
|
|
'".$date_from_to_query->format('Y-m-d')."' AND
|
||
|
|
'".$date_to_to_query->format('Y-m-d')."'
|
||
|
|
and faktura.type like '".$searchByType."'
|
||
|
|
and faktura.canceled = 0
|
||
|
|
and faktura.deleted= 0
|
||
|
|
and pozycja.deleted= 0
|
||
|
|
GROUP BY pozycja.id
|
||
|
|
ORDER BY faktura.parent_name
|
||
|
|
COLLATE utf8_polish_ci;";
|
||
|
|
|
||
|
|
/*****************************************************/
|
||
|
|
/*************** 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["type"] = $r["type"];
|
||
|
|
|
||
|
|
$userBool=1;
|
||
|
|
if($selectUser!="")
|
||
|
|
{
|
||
|
|
$userBool=0;
|
||
|
|
$querySelectUsers="SELECT user.id,
|
||
|
|
first_name as 'first',
|
||
|
|
last_name as 'last'
|
||
|
|
FROM
|
||
|
|
accounts acc
|
||
|
|
JOIN
|
||
|
|
users user ON acc.assigned_user_id=user.id
|
||
|
|
WHERE acc.id='".$r["cotructor_id"]."';";
|
||
|
|
$rowsSelectUsers = $db->query ($querySelectUsers);
|
||
|
|
while($rowSelectUser = $db->fetchByAssoc ( $rowsSelectUsers ))
|
||
|
|
{
|
||
|
|
if($rowSelectUser["id"]==$selectUser)
|
||
|
|
{
|
||
|
|
$userBool=1;
|
||
|
|
}else{
|
||
|
|
$userBool=0;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
if($userBool==1)
|
||
|
|
{
|
||
|
|
|
||
|
|
if($r["parent_id"]==1249)
|
||
|
|
{
|
||
|
|
$row["parent"] = "Media Saturn Holding";
|
||
|
|
}else{
|
||
|
|
$row["parent"] = $r["parent"];
|
||
|
|
}
|
||
|
|
|
||
|
|
$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 category.deleted=0
|
||
|
|
and bean.deleted = '0'
|
||
|
|
and bean.position = 1;";
|
||
|
|
$rowsSubCategory = $db->query ($querySubCategory);
|
||
|
|
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 category.deleted=0
|
||
|
|
and bean.deleted = '0'
|
||
|
|
and bean.position = 0;";
|
||
|
|
$rowscategory = $db->query ($queryCategory);
|
||
|
|
while($rowcategory = $db->fetchByAssoc ( $rowscategory ))
|
||
|
|
{
|
||
|
|
//echo "assadad";
|
||
|
|
$row["kategoria"] = $rowcategory["kategoria"];
|
||
|
|
}
|
||
|
|
|
||
|
|
$row["ilosc"] = $r["ilosc"];
|
||
|
|
$row["netto"] = $r["netto"];
|
||
|
|
$row["srednia"] = $row["netto"]/$row["ilosc"];
|
||
|
|
$row["koszt"] = $r["koszt"];
|
||
|
|
if($row["netto"]>$row["koszt"])
|
||
|
|
{
|
||
|
|
$row["marza"] = ($row["netto"]-$row["koszt"])/($row["netto"])*100;
|
||
|
|
}else{
|
||
|
|
$row["marza"] = 0;
|
||
|
|
}
|
||
|
|
|
||
|
|
$data [] = $row;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
if($searchByType!="")
|
||
|
|
{
|
||
|
|
|
||
|
|
////////////////// SUM /////////////////////////////////
|
||
|
|
foreach( $data as $key=>&$element )
|
||
|
|
{
|
||
|
|
if($element["netto"]!=0)
|
||
|
|
{
|
||
|
|
$categoryArray[$element["parent"] == "" ? "Nieznany" : $element["parent"]][$element["kategoria"] == "" ? "Inne" : $element["kategoria"]][$element["podkategoria"] == "" ? "Reszta" : $element["podkategoria"]][$key] = $element;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
foreach( $categoryArray as $parent=>&$elementParent )
|
||
|
|
{
|
||
|
|
foreach( $elementParent as $category=>&$elementCategory )
|
||
|
|
{
|
||
|
|
foreach( $elementCategory as $subcategory=>&$elementSubCategory )
|
||
|
|
{
|
||
|
|
foreach( $elementSubCategory as $product=>&$elementProduct )
|
||
|
|
{
|
||
|
|
$sumContracor["IloscSum"][$parent] += $elementProduct["ilosc"];
|
||
|
|
$sumContracor["SoldSum"][$parent] += $elementProduct["netto"];
|
||
|
|
if($elementProduct["type"]=="normal")
|
||
|
|
{
|
||
|
|
$sumContracor["SoldSumNormal"][$parent] += $elementProduct["netto"];
|
||
|
|
$sumContracor["IloscNormal"][$parent] += $elementProduct["ilosc"];
|
||
|
|
}else{
|
||
|
|
$sumContracor["SoldSumNormal"][$parent] += 0;
|
||
|
|
$sumContracor["IloscNormal"][$parent] += 0;
|
||
|
|
}
|
||
|
|
$sumContracor["KosztSum"][$parent] += $elementProduct["koszt"];
|
||
|
|
|
||
|
|
$sumCategory["IloscSum"][$parent][$category] += $elementProduct["ilosc"];
|
||
|
|
$sumCategory["SoldSum"][$parent][$category] += $elementProduct["netto"];
|
||
|
|
if($elementProduct["type"]=="normal")
|
||
|
|
{
|
||
|
|
$sumCategory["SoldSumNormal"][$parent][$category] += $elementProduct["netto"];
|
||
|
|
$sumCategory["IloscNormal"][$parent][$category] += $elementProduct["ilosc"];
|
||
|
|
}else{
|
||
|
|
$sumCategory["SoldSumNormal"][$parent][$category] += 0;
|
||
|
|
$sumCategory["IloscNormal"][$parent][$category] += 0;
|
||
|
|
}
|
||
|
|
$sumCategory["KosztSum"][$parent][$category] += $elementProduct["koszt"];
|
||
|
|
|
||
|
|
$sumSubCategory["IloscSum"][$parent][$category][$subcategory] += $elementProduct["ilosc"];
|
||
|
|
$sumSubCategory["SoldSum"][$parent][$category][$subcategory] += $elementProduct["netto"];
|
||
|
|
if($elementProduct["type"]=="normal")
|
||
|
|
{
|
||
|
|
$sumSubCategory["SoldSumNormal"][$parent][$category][$subcategory] += $elementProduct["netto"];
|
||
|
|
$sumSubCategory["IloscNormal"][$parent][$category][$subcategory] += $elementProduct["ilosc"];
|
||
|
|
}else{
|
||
|
|
$sumSubCategory["SoldSumNormal"][$parent][$category][$subcategory] += 0;
|
||
|
|
$sumSubCategory["IloscNormal"][$parent][$category][$subcategory] += 0;
|
||
|
|
}
|
||
|
|
$sumSubCategory["KosztSum"][$parent][$category][$subcategory] += $elementProduct["koszt"];
|
||
|
|
}
|
||
|
|
$sumSubCategory["MarzaSum"][$parent][$category][$subcategory] = ($sumSubCategory["SoldSumNormal"][$parent][$category][$subcategory]-$sumSubCategory["KosztSum"][$parent][$category][$subcategory])/$sumSubCategory["SoldSumNormal"][$parent][$category][$subcategory]*100;
|
||
|
|
$sumSubCategory["SredniaSum"][$parent][$category][$subcategory] = $sumSubCategory["SoldSumNormal"][$parent][$category][$subcategory]/$sumSubCategory["IloscNormal"][$parent][$category][$subcategory];
|
||
|
|
|
||
|
|
$categoryArray[$parent][$category][$subcategory]["IloscSum"] = $sumSubCategory["IloscSum"][$parent][$category][$subcategory];
|
||
|
|
$categoryArray[$parent][$category][$subcategory]["SoldSum"] = $sumSubCategory["SoldSum"][$parent][$category][$subcategory];
|
||
|
|
$categoryArray[$parent][$category][$subcategory]["SoldSumNormal"] = $sumSubCategory["SoldSumNormal"][$parent][$category][$subcategory];
|
||
|
|
$categoryArray[$parent][$category][$subcategory]["IloscNormal"] = $sumSubCategory["IloscNormal"][$parent][$category][$subcategory];
|
||
|
|
$categoryArray[$parent][$category][$subcategory]["SredniaSum"] = $sumSubCategory["SredniaSum"][$parent][$category][$subcategory];
|
||
|
|
$categoryArray[$parent][$category][$subcategory]["KosztSum"] = $sumSubCategory["KosztSum"][$parent][$category][$subcategory];
|
||
|
|
$categoryArray[$parent][$category][$subcategory]["MarzaSum"] = $sumSubCategory["MarzaSum"][$parent][$category][$subcategory];
|
||
|
|
}
|
||
|
|
$sumCategory["MarzaSum"][$parent][$category] = ($sumCategory["SoldSumNormal"][$parent][$category]-$sumCategory["KosztSum"][$parent][$category])/$sumCategory["SoldSumNormal"][$parent][$category]*100;
|
||
|
|
$sumCategory["SredniaSum"][$parent][$category] = $sumCategory["SoldSumNormal"][$parent][$category]/$sumCategory["IloscNormal"][$parent][$category];
|
||
|
|
|
||
|
|
$categoryArray[$parent][$category]["IloscSum"] = $sumCategory["IloscSum"][$parent][$category];
|
||
|
|
$categoryArray[$parent][$category]["SoldSum"] = $sumCategory["SoldSum"][$parent][$category];
|
||
|
|
$categoryArray[$parent][$category]["SoldSumNormal"] = $sumCategory["SoldSumNormal"][$parent][$category];
|
||
|
|
$categoryArray[$parent][$category]["IloscNormal"] = $sumCategory["IloscNormal"][$parent][$category];
|
||
|
|
$categoryArray[$parent][$category]["SredniaSum"] = $sumCategory["SredniaSum"][$parent][$category];
|
||
|
|
$categoryArray[$parent][$category]["KosztSum"] = $sumCategory["KosztSum"][$parent][$category];
|
||
|
|
$categoryArray[$parent][$category]["MarzaSum"] = $sumCategory["MarzaSum"][$parent][$category];
|
||
|
|
}
|
||
|
|
$sumContracor["MarzaSum"][$parent] = ($sumContracor["SoldSumNormal"][$parent]-$sumContracor["KosztSum"][$parent])/$sumContracor["SoldSumNormal"][$parent]*100;
|
||
|
|
$sumContracor["SredniaSum"][$parent] = $sumContracor["SoldSumNormal"][$parent]/$sumContracor["IloscNormal"][$parent];
|
||
|
|
|
||
|
|
$categoryArray[$parent]["IloscSum"] = $sumContracor["IloscSum"][$parent];
|
||
|
|
$categoryArray[$parent]["SoldSum"] = $sumContracor["SoldSum"][$parent];
|
||
|
|
$categoryArray[$parent]["SoldSumNormal"] = $sumContracor["SoldSumNormal"][$parent];
|
||
|
|
$categoryArray[$parent]["IloscNormal"] = $sumContracor["IloscNormal"][$parent];
|
||
|
|
$categoryArray[$parent]["SredniaSum"] = $sumContracor["SredniaSum"][$parent];
|
||
|
|
$categoryArray[$parent]["KosztSum"] = $sumContracor["KosztSum"][$parent];
|
||
|
|
$categoryArray[$parent]["MarzaSum"] = $sumContracor["MarzaSum"][$parent];
|
||
|
|
|
||
|
|
}
|
||
|
|
foreach( $categoryArray as $parent=>&$elementParent )
|
||
|
|
{
|
||
|
|
$sum["IloscSumSum"] += $sumContracor["IloscSum"][$parent];
|
||
|
|
$sum["SoldSumSum"] += $sumContracor["SoldSum"][$parent];
|
||
|
|
$sum["SoldSumNormal"] += $sumContracor["SoldSumNormal"][$parent];
|
||
|
|
$sum["IloscNormal"] += $sumContracor["IloscNormal"][$parent];
|
||
|
|
$sum["KosztSumSum"] += $sumContracor["KosztSum"][$parent];
|
||
|
|
}
|
||
|
|
$sum["MarzaSumSum"] = ($sum["SoldSumNormal"]-$sum["KosztSumSum"])/$sum["SoldSumNormal"]*100;
|
||
|
|
$sum["SredniaSumSum"] = $sum["SoldSumNormal"]/$sum["IloscNormal"];
|
||
|
|
|
||
|
|
function cmp($a, $b)
|
||
|
|
{
|
||
|
|
if ($a["name"] == $b["name"]) {
|
||
|
|
return 0;
|
||
|
|
}
|
||
|
|
return ($a["name"] < $b["name"]) ? -1 : 1;
|
||
|
|
}
|
||
|
|
|
||
|
|
// grupowanie po kontrahentach
|
||
|
|
foreach($categoryArray as $parent=>&$elementParent )
|
||
|
|
{
|
||
|
|
$tmp2[$parent]["IloscSum"] = $categoryArray[$parent]["IloscSum"];
|
||
|
|
$tmp2[$parent]["SoldSum"] = $categoryArray[$parent]["SoldSum"];
|
||
|
|
$tmp2[$parent]["SoldSumNormal"] = $categoryArray[$parent]["SoldSumNormal"];
|
||
|
|
$tmp2[$parent]["IloscNormal"] = $categoryArray[$parent]["IloscNormal"];
|
||
|
|
$tmp2[$parent]["KosztSum"] = $categoryArray[$parent]["KosztSum"];
|
||
|
|
$tmp2[$parent]["SredniaSum"] = 0;
|
||
|
|
$tmp2[$parent]["SredniaSum"] = $categoryArray[$parent]["SredniaSum"];
|
||
|
|
$tmp2[$parent]["MarzaSum"] = $categoryArray[$parent]["MarzaSum"];
|
||
|
|
foreach($elementParent as $category=>&$elementCategory)
|
||
|
|
{
|
||
|
|
$tmp2[$parent][$category]["IloscSum"] = $categoryArray[$parent][$category]["IloscSum"];
|
||
|
|
$tmp2[$parent][$category]["SoldSum"] = $categoryArray[$parent][$category]["SoldSum"];
|
||
|
|
$tmp2[$parent][$category]["SoldSumNormal"] = $categoryArray[$parent][$category]["SoldSumNormal"];
|
||
|
|
$tmp2[$parent][$category]["IloscNormal"] = $categoryArray[$parent][$category]["IloscNormal"];
|
||
|
|
$tmp2[$parent][$category]["KosztSum"] = $categoryArray[$parent][$category]["KosztSum"];
|
||
|
|
$tmp2[$parent][$category]["SredniaSum"] = $categoryArray[$parent][$category]["SredniaSum"];
|
||
|
|
$tmp2[$parent][$category]["MarzaSum"] = $categoryArray[$parent][$category]["MarzaSum"];
|
||
|
|
foreach( $elementCategory as $subcategory=>&$elementSubCategory )
|
||
|
|
{
|
||
|
|
$tmp2[$parent][$category][$subcategory]["IloscSum"] = $categoryArray[$parent][$category][$subcategory]["IloscSum"];
|
||
|
|
$tmp2[$parent][$category][$subcategory]["SoldSum"] = $categoryArray[$parent][$category][$subcategory]["SoldSum"];
|
||
|
|
$tmp2[$parent][$category][$subcategory]["SoldSumNormal"] = $categoryArray[$parent][$category][$subcategory]["SoldSumNormal"];
|
||
|
|
$tmp2[$parent][$category][$subcategory]["IloscNormal"] = $categoryArray[$parent][$category][$subcategory]["IloscNormal"];
|
||
|
|
$tmp2[$parent][$category][$subcategory]["KosztSum"] = $categoryArray[$parent][$category][$subcategory]["KosztSum"];
|
||
|
|
$tmp2[$parent][$category][$subcategory]["SredniaSum"] = $categoryArray[$parent][$category][$subcategory]["SredniaSum"];
|
||
|
|
$tmp2[$parent][$category][$subcategory]["MarzaSum"] = $categoryArray[$parent][$category][$subcategory]["MarzaSum"];
|
||
|
|
|
||
|
|
|
||
|
|
usort($elementSubCategory, "cmp");
|
||
|
|
$i=0;
|
||
|
|
$tmp2[$parent][$category][$subcategory][0]= array ('name' => null);
|
||
|
|
foreach($elementSubCategory as $count=>&$element3)
|
||
|
|
{
|
||
|
|
if($element3["name"]!=$tmp2[$parent][$category][$subcategory][$i]["name"])
|
||
|
|
{
|
||
|
|
if($tmp2[$parent][$category][$subcategory][$i]["name"]!=null)
|
||
|
|
{
|
||
|
|
$i++;
|
||
|
|
}
|
||
|
|
$tmp2[$parent][$category][$subcategory][$i]["id"]=$element3["id"];
|
||
|
|
$tmp2[$parent][$category][$subcategory][$i]["name"]=$element3["name"];
|
||
|
|
$tmp2[$parent][$category][$subcategory][$i]["code"]=$element3["code"];
|
||
|
|
$tmp2[$parent][$category][$subcategory][$i]["parent_id"]=$element3["parent_id"];
|
||
|
|
$tmp2[$parent][$category][$subcategory][$i]["stan"] = $element3["stan"];
|
||
|
|
$tmp2[$parent][$category][$subcategory][$i]["wartosc"] = $element3["wartosc"];
|
||
|
|
|
||
|
|
$tmp2[$parent]["StanSum"] += $element3["stan"];
|
||
|
|
$tmp2[$parent]["WartoscSum"] += $element3["wartosc"];
|
||
|
|
|
||
|
|
$tmp2[$parent][$category]["StanSum"] += $element3["stan"];
|
||
|
|
$tmp2[$parent][$category]["WartoscSum"] += $element3["wartosc"];
|
||
|
|
|
||
|
|
$tmp2[$parent][$category][$subcategory]["StanSum"] += $element3["stan"];
|
||
|
|
$tmp2[$parent][$category][$subcategory]["WartoscSum"] += $element3["wartosc"];
|
||
|
|
}
|
||
|
|
if($element3["type"]=="normal")
|
||
|
|
{
|
||
|
|
$tmp2[$parent][$category][$subcategory][$i]["nettoNormal"] += $element3["netto"];
|
||
|
|
$tmp2[$parent][$category][$subcategory][$i]["IloscNormal"] += $element3["ilosc"];
|
||
|
|
}
|
||
|
|
$tmp2[$parent][$category][$subcategory][$i]["ilosc"] += $element3["ilosc"];
|
||
|
|
$tmp2[$parent][$category][$subcategory][$i]["netto"] += $element3["netto"];
|
||
|
|
$tmp2[$parent][$category][$subcategory][$i]["koszt"] += $element3["koszt"];
|
||
|
|
}
|
||
|
|
if(is_array($tmp2[$parent][$category][$subcategory]["SredniaSum"]) || $tmp2[$parent][$category][$subcategory]["SredniaSum"]=="")
|
||
|
|
{
|
||
|
|
$tmp2[$parent][$category][$subcategory]["SredniaSum"] = 0;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
if(is_array($tmp2[$parent][$category]["SredniaSum"]))
|
||
|
|
{
|
||
|
|
$tmp2[$parent][$category]["SredniaSum"] = 0;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
if(is_array($tmp2[$parent]["SredniaSum"]))
|
||
|
|
{
|
||
|
|
$tmp2[$parent]["SredniaSum"] = 0;
|
||
|
|
}
|
||
|
|
|
||
|
|
$categoryArray1=$tmp2;
|
||
|
|
}
|
||
|
|
////////////////// SORT sumSubCategory /////////////////////////////////
|
||
|
|
foreach( $categoryArray1 as $parent=>&$elementParent )
|
||
|
|
{
|
||
|
|
foreach( $elementParent as $category=>&$elementCategory )
|
||
|
|
{
|
||
|
|
foreach( $elementCategory as $SubCategory1=>&$elementSubCategory1)
|
||
|
|
{
|
||
|
|
foreach( $elementCategory as $SubCategory2=>&$elementSubCategory2 )
|
||
|
|
{
|
||
|
|
if($sumSubCategory["SoldSum"][$parent][$category][$SubCategory1]>$sumSubCategory["SoldSum"][$parent][$category][$SubCategory2])
|
||
|
|
{
|
||
|
|
$tmp=$sumSubCategory["SoldSum"][$parent][$category][$SubCategory1];
|
||
|
|
$sumSubCategory["SoldSum"][$parent][$category][$SubCategory1]=$sumSubCategory["SoldSum"][$parent][$category][$SubCategory2];
|
||
|
|
$sumSubCategory["SoldSum"][$parent][$category][$SubCategory2]=$tmp;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
////////////////// SORT sumCategory /////////////////////////////////
|
||
|
|
foreach( $categoryArray1 as $parent=>&$elementParent )
|
||
|
|
{
|
||
|
|
foreach( $elementParent as $category1=>&$elementCategory1 )
|
||
|
|
{
|
||
|
|
foreach( $elementParent as $category2=>&$elementCategory2 )
|
||
|
|
{
|
||
|
|
if($sumCategory["SoldSum"][$parent][$category1]>$sumCategory["SoldSum"][$parent][$category2])
|
||
|
|
{
|
||
|
|
$tmp=$sumCategory["SoldSum"][$parent][$category1];
|
||
|
|
$sumCategory["SoldSum"][$parent][$category1]=$sumCategory["SoldSum"][$parent][$category2];
|
||
|
|
$sumCategory["SoldSum"][$parent][$category2]=$tmp;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
////////////////// SORT $sumContracor /////////////////////////////////
|
||
|
|
foreach( $categoryArray1 as $parent1=>&$elementParent1 )
|
||
|
|
{
|
||
|
|
foreach( $categoryArray1 as $parent2=>&$elementParent2 )
|
||
|
|
{
|
||
|
|
if($sumContracor["SoldSum"][$parent1]>$sumContracor["SoldSum"][$parent2])
|
||
|
|
{
|
||
|
|
$tmp=$sumContracor["SoldSum"][$parent1];
|
||
|
|
$sumContracor["SoldSum"][$parent1]=$sumContracor["SoldSum"][$parent2];
|
||
|
|
$sumContracor["SoldSum"][$parent2]=$tmp;
|
||
|
|
}
|
||
|
|
|
||
|
|
}
|
||
|
|
|
||
|
|
}
|
||
|
|
////////////////// SORT array at sumContracor/////////////////////////////////
|
||
|
|
$newArray2;
|
||
|
|
foreach( $categoryArray1 as $parent1=>&$elementParent1 )
|
||
|
|
{
|
||
|
|
foreach( $categoryArray1 as $parent2=>&$elementParent2 )
|
||
|
|
{
|
||
|
|
if($sumContracor["SoldSum"][$parent1]==$categoryArray1[$parent2]["SoldSum"])
|
||
|
|
{
|
||
|
|
$newArray2[$parent2]=$categoryArray1[$parent2];
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
////////////////// SORT array at sumCategory/////////////////////////////////
|
||
|
|
$newArray1;
|
||
|
|
foreach( $newArray2 as $parent=>&$elementParent )
|
||
|
|
{
|
||
|
|
foreach( $elementParent as $category1=>&$elementCategory1 )
|
||
|
|
{
|
||
|
|
foreach( $elementParent as $category2=>&$elementCategory2 )
|
||
|
|
{
|
||
|
|
if($sumCategory["SoldSum"][$parent][$category1]==$newArray2[$parent][$category2]["SoldSum"])
|
||
|
|
{
|
||
|
|
$newArray1[$parent][$category2]=$newArray2[$parent][$category2];
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
////////////////// SORT array at sumSubCategory/////////////////////////////////
|
||
|
|
$newArray;
|
||
|
|
foreach( $newArray1 as $parent=>&$elementParent )
|
||
|
|
{
|
||
|
|
foreach( $elementParent as $category=>&$elementCategory )
|
||
|
|
{
|
||
|
|
foreach( $elementCategory as $subcategory1=>&$elementSubCategory )
|
||
|
|
{
|
||
|
|
foreach( $elementCategory as $subcategory2=>&$elementSubCategory )
|
||
|
|
{
|
||
|
|
if($sumSubCategory["SoldSum"][$parent][$category][$subcategory1]==$newArray1[$parent][$category][$subcategory2]["SoldSum2"])
|
||
|
|
{
|
||
|
|
$newArray[$parent][$category][$subcategory2]=$newArray1[$parent][$category][$subcategory2];
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
$newArray[$parent]["IloscSum"]=$newArray1[$parent]["IloscSum"];
|
||
|
|
$newArray[$parent]["SoldSum"]=$newArray1[$parent]["SoldSum"];
|
||
|
|
$newArray[$parent]["SredniaSum"]=$newArray1[$parent]["SredniaSum"];
|
||
|
|
$newArray[$parent]["KosztSum"]=$newArray1[$parent]["KosztSum"];
|
||
|
|
$newArray[$parent]["MarzaSum"]=$newArray1[$parent]["MarzaSum"];
|
||
|
|
}
|
||
|
|
|
||
|
|
//wyliczanie dokładne marży i średniej dla produktów
|
||
|
|
foreach( $newArray as $parent=>&$elementParent )
|
||
|
|
{
|
||
|
|
foreach( $elementParent as $category=>&$elementCategory )
|
||
|
|
{
|
||
|
|
foreach( $elementCategory as $subcategory=>&$elementSubCategory )
|
||
|
|
{
|
||
|
|
foreach( $elementSubCategory as $key=>&$element )
|
||
|
|
{
|
||
|
|
$newArray[$parent][$category][$subcategory][$key]["marza"] = ($newArray[$parent][$category][$subcategory][$key]["nettoNormal"]-$newArray[$parent][$category][$subcategory][$key]["koszt"])/$newArray[$parent][$category][$subcategory][$key]["nettoNormal"]*100;
|
||
|
|
$newArray[$parent][$category][$subcategory][$key]["srednia"] = $newArray[$parent][$category][$subcategory][$key]["nettoNormal"]/$newArray[$parent][$category][$subcategory][$key]["IloscNormal"];
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
/*****************************************************/
|
||
|
|
/***************** EXPORT TO EXCEL *******************/
|
||
|
|
/*****************************************************/
|
||
|
|
/**
|
||
|
|
* PHPExcel
|
||
|
|
*
|
||
|
|
* Copyright (C) 2006 - 2014 PHPExcel
|
||
|
|
*
|
||
|
|
* This library is free software; you can redistribute it and/or
|
||
|
|
* modify it under the terms of the GNU Lesser General Public
|
||
|
|
* License as published by the Free Software Foundation; either
|
||
|
|
* version 2.1 of the License, or (at your option) any later version.
|
||
|
|
*
|
||
|
|
* This library is distributed in the hope that it will be useful,
|
||
|
|
* but WITHOUT ANY WARRANTY; without even the implied warranty of
|
||
|
|
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
|
||
|
|
* Lesser General Public License for more details.
|
||
|
|
*
|
||
|
|
* You should have received a copy of the GNU Lesser General Public
|
||
|
|
* License along with this library; if not, write to the Free Software
|
||
|
|
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
|
||
|
|
*
|
||
|
|
* @category PHPExcel
|
||
|
|
* @package PHPExcel
|
||
|
|
* @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
|
||
|
|
* @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
|
||
|
|
* @version 1.8.0, 2014-03-02
|
||
|
|
*/
|
||
|
|
|
||
|
|
/** PHPExcel_IOFactory */
|
||
|
|
require_once dirname(__FILE__) . '/PhpExcell/Classes/PHPExcel/IOFactory.php';
|
||
|
|
|
||
|
|
// Create new PHPExcel object
|
||
|
|
echo date('H:i:s') , " Create new PHPExcel object" , EOL;
|
||
|
|
$objPHPExcel = new PHPExcel();
|
||
|
|
|
||
|
|
// Set document properties
|
||
|
|
echo date('H:i:s') , " Set document properties" , EOL;
|
||
|
|
$objPHPExcel->getProperties()->setCreator("E5")
|
||
|
|
->setLastModifiedBy("E5")
|
||
|
|
->setTitle("E5 - Report Sales")
|
||
|
|
->setSubject("E5 - Report Document")
|
||
|
|
->setDescription("Report Sales - description")
|
||
|
|
->setKeywords("e5, report, report sales")
|
||
|
|
->setCategory("Reports");
|
||
|
|
|
||
|
|
/*****************************************************/
|
||
|
|
/******************* EXCELL Styles *******************/
|
||
|
|
/*****************************************************/
|
||
|
|
|
||
|
|
// Set default widths and heights
|
||
|
|
$objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(15);
|
||
|
|
$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20);
|
||
|
|
|
||
|
|
$headerStyle = array(
|
||
|
|
'alignment' => array(
|
||
|
|
'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
|
||
|
|
'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
|
||
|
|
),
|
||
|
|
'font' => array(
|
||
|
|
'bold' => true,
|
||
|
|
'color' => array('rgb' => 'FFFFFF'),
|
||
|
|
'name' => 'Verdana',
|
||
|
|
),
|
||
|
|
'fill' => array(
|
||
|
|
'type' => PHPExcel_Style_Fill::FILL_SOLID,
|
||
|
|
'startcolor' => array(
|
||
|
|
'rgb' => '272822',
|
||
|
|
),
|
||
|
|
),
|
||
|
|
);
|
||
|
|
|
||
|
|
$categoryStyle = array(
|
||
|
|
'alignment' => array(
|
||
|
|
'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
|
||
|
|
'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
|
||
|
|
),
|
||
|
|
'font' => array(
|
||
|
|
'bold' => true,
|
||
|
|
'color' => array('rgb' => 'FFFFFF'),
|
||
|
|
'name' => 'Verdana',
|
||
|
|
),
|
||
|
|
'fill' => array(
|
||
|
|
'type' => PHPExcel_Style_Fill::FILL_SOLID,
|
||
|
|
'startcolor' => array(
|
||
|
|
'rgb' => '4E5044',
|
||
|
|
),
|
||
|
|
),
|
||
|
|
);
|
||
|
|
|
||
|
|
|
||
|
|
$subCategoryStyle = array(
|
||
|
|
'alignment' => array(
|
||
|
|
'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
|
||
|
|
'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
|
||
|
|
),
|
||
|
|
'font' => array(
|
||
|
|
'bold' => true,
|
||
|
|
'color' => array('rgb' => 'FFFFFF'),
|
||
|
|
'name' => 'Verdana',
|
||
|
|
),
|
||
|
|
'fill' => array(
|
||
|
|
'type' => PHPExcel_Style_Fill::FILL_SOLID,
|
||
|
|
'startcolor' => array(
|
||
|
|
'rgb' => '8F937D',
|
||
|
|
),
|
||
|
|
),
|
||
|
|
);
|
||
|
|
|
||
|
|
$subSubCategoryStyle = array(
|
||
|
|
'alignment' => array(
|
||
|
|
'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
|
||
|
|
'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
|
||
|
|
),
|
||
|
|
'font' => array(
|
||
|
|
'bold' => true,
|
||
|
|
'color' => array('rgb' => 'FFFFFF'),
|
||
|
|
'name' => 'Verdana',
|
||
|
|
),
|
||
|
|
'fill' => array(
|
||
|
|
'type' => PHPExcel_Style_Fill::FILL_SOLID,
|
||
|
|
'startcolor' => array(
|
||
|
|
'rgb' => 'ACB197',
|
||
|
|
),
|
||
|
|
),
|
||
|
|
);
|
||
|
|
|
||
|
|
$columnAlignRight = array(
|
||
|
|
'alignment' => array(
|
||
|
|
'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
|
||
|
|
'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
|
||
|
|
),
|
||
|
|
);
|
||
|
|
|
||
|
|
$columnAlignLeft = array(
|
||
|
|
'alignment' => array(
|
||
|
|
'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
|
||
|
|
'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
|
||
|
|
),
|
||
|
|
);
|
||
|
|
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getRowDimension('1')->setRowHeight(25);
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(100);
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(18);
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(20);
|
||
|
|
|
||
|
|
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle("B2:F4000")->applyFromArray($columnAlignRight);
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle("C2:F4000")->applyFromArray($columnAlignRight);
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle("E2:F4000")->applyFromArray($columnAlignRight);
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle("F2:F4000")->applyFromArray($columnAlignRight);
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle("A1:F1")->applyFromArray($headerStyle);
|
||
|
|
|
||
|
|
/*****************************************************/
|
||
|
|
/********************* EXCELL Data *******************/
|
||
|
|
/*****************************************************/
|
||
|
|
|
||
|
|
// Add header
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)
|
||
|
|
->setCellValue('A1', $mod_strings['LBL_CONTRACTOR'] )
|
||
|
|
->setCellValue('B1', $mod_strings['LBL_SOLD_AMOUNT'])
|
||
|
|
->setCellValue('C1', $mod_strings['LBL_VALUE_SALES'])
|
||
|
|
->setCellValue('D1', $mod_strings['LBL_AVERAGE_PRICE'])
|
||
|
|
->setCellValue('E1', $mod_strings['LBL_COST'])
|
||
|
|
->setCellValue('F1', $mod_strings['LBL_MARGIN']);
|
||
|
|
|
||
|
|
|
||
|
|
/*****************************************************/
|
||
|
|
/********************* EXCELL Data *******************/
|
||
|
|
/*****************************************************/
|
||
|
|
|
||
|
|
// Add all data
|
||
|
|
$lastRowNumber = 2;
|
||
|
|
$rowNumber = 2;
|
||
|
|
foreach ( $newArray as $contractorName => $contractor )
|
||
|
|
{
|
||
|
|
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)
|
||
|
|
->setCellValue('A'.($rowNumber), $contractorName)
|
||
|
|
->setCellValue('B'.($rowNumber), number_format($contractor["IloscSum"], 2, '.', ''))
|
||
|
|
->setCellValue('C'.($rowNumber), number_format($contractor["SoldSum"], 2, '.', ''))
|
||
|
|
->setCellValue('D'.($rowNumber), number_format($contractor["SredniaSum"], 2, '.', ''))
|
||
|
|
->setCellValue('E'.($rowNumber), number_format($contractor["KosztSum"], 2, '.', ''))
|
||
|
|
->setCellValue('F'.($rowNumber), number_format($contractor["MarzaSum"], 2, '.', '')." %")
|
||
|
|
->getStyle("A".$rowNumber.":F".$rowNumber)->applyFromArray($categoryStyle);
|
||
|
|
|
||
|
|
// Formatowanie walutowe
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle('B'.($rowNumber))->getNumberFormat()->setFormatCode("#");
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle('C'.($rowNumber))->getNumberFormat()->setFormatCode("#,#0.#0 zł");
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle('D'.($rowNumber))->getNumberFormat()->setFormatCode("#,#0.#0 zł");
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle('E'.($rowNumber))->getNumberFormat()->setFormatCode("#,#0.#0 zł");
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle('F'.($rowNumber))->getNumberFormat()->setFormatCode("#0.#0");
|
||
|
|
|
||
|
|
// Nazwa kategorii wyrównane do lewej
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle("A".$rowNumber)->applyFromArray($columnAlignLeft);
|
||
|
|
|
||
|
|
// Wyświetla kontrahentów
|
||
|
|
foreach ( $contractor as $categoryName => $category )
|
||
|
|
{
|
||
|
|
if ( $categoryName != "IloscSum" && $categoryName != "SoldSum" && $categoryName != "SredniaSum" &&
|
||
|
|
$categoryName != "KosztSum" && $categoryName != "MarzaSum" && $categoryName != "StanSum" &&
|
||
|
|
$categoryName != "WartoscSum")
|
||
|
|
{
|
||
|
|
$rowNumber++;
|
||
|
|
$lastRowNumber++;
|
||
|
|
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)
|
||
|
|
->setCellValue('A'.($rowNumber), " ".$categoryName)
|
||
|
|
->setCellValue('B'.($rowNumber), number_format($category["IloscSum"], 2, '.', ''))
|
||
|
|
->setCellValue('C'.($rowNumber), number_format($category["SoldSum"], 2, '.', ''))
|
||
|
|
->setCellValue('D'.($rowNumber), number_format($category["SredniaSum"], 2, '.', ''))
|
||
|
|
->setCellValue('E'.($rowNumber), number_format($category["KosztSum"], 2, '.', ''))
|
||
|
|
->setCellValue('F'.($rowNumber), number_format($category["MarzaSum"], 2, '.', '')." %")
|
||
|
|
->getStyle("A".$rowNumber.":F".$rowNumber)->applyFromArray($subCategoryStyle);
|
||
|
|
|
||
|
|
// Formatowanie walutowe
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle('B'.($rowNumber))->getNumberFormat()->setFormatCode("#");
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle('C'.($rowNumber))->getNumberFormat()->setFormatCode("#,#0.#0 zł");
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle('D'.($rowNumber))->getNumberFormat()->setFormatCode("#,#0.#0 zł");
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle('E'.($rowNumber))->getNumberFormat()->setFormatCode("#,#0.#0 zł");
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle('F'.($rowNumber))->getNumberFormat()->setFormatCode("#0.#0");
|
||
|
|
|
||
|
|
// Nazwa kategorii wyrównane do lewej
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle("A".$rowNumber)->applyFromArray($columnAlignLeft);
|
||
|
|
|
||
|
|
// Wyświetla podkategorie
|
||
|
|
foreach ( $category as $subCategoryName => $subCategory )
|
||
|
|
{
|
||
|
|
if ( $subCategoryName != "IloscSum" && $subCategoryName != "SoldSum" && $subCategoryName != "SredniaSum"
|
||
|
|
&& $subCategoryName != "KosztSum" && $subCategoryName != "MarzaSum" && $subCategoryName != ""
|
||
|
|
&& $subCategoryName != "SoldSumNormal" && $subCategoryName != "IloscNormal" && $subCategoryName != "StanSum" && $subCategoryName != "WartoscSum")
|
||
|
|
{
|
||
|
|
$rowNumber++;
|
||
|
|
$lastRowNumber++;
|
||
|
|
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)
|
||
|
|
->setCellValue('A'.($rowNumber), " ".$subCategoryName)
|
||
|
|
->setCellValue('B'.($rowNumber), number_format($subCategory["IloscSum"], 2, '.', ''))
|
||
|
|
->setCellValue('C'.($rowNumber), number_format($subCategory["SoldSum"], 2, '.', ''))
|
||
|
|
->setCellValue('D'.($rowNumber), number_format($subCategory["SredniaSum"], 2, '.', ''))
|
||
|
|
->setCellValue('E'.($rowNumber), number_format($subCategory["KosztSum"], 2, '.', ''))
|
||
|
|
->setCellValue('F'.($rowNumber), number_format($subCategory["MarzaSum"], 2, '.', '')." %")
|
||
|
|
->getStyle("A".$rowNumber.":F".$rowNumber)->applyFromArray($subSubCategoryStyle);
|
||
|
|
|
||
|
|
// Formatowanie walutowe
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle('B'.($rowNumber))->getNumberFormat()->setFormatCode("#");
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle('C'.($rowNumber))->getNumberFormat()->setFormatCode("#,#0.#0 zł");
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle('D'.($rowNumber))->getNumberFormat()->setFormatCode("#,#0.#0 zł");
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle('E'.($rowNumber))->getNumberFormat()->setFormatCode("#,#0.#0 zł");
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle('F'.($rowNumber))->getNumberFormat()->setFormatCode("#0.#0");
|
||
|
|
|
||
|
|
// Nazwa produktu wyrównane do lewej
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle("A".$rowNumber)->applyFromArray($columnAlignLeft);
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getRowDimension($rowNumber)->setOutlineLevel(1);
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getRowDimension($rowNumber)->setVisible(true);
|
||
|
|
|
||
|
|
// Wyświetla produkty
|
||
|
|
foreach ( $subCategory as $productName => $product )
|
||
|
|
{
|
||
|
|
if ( $product["name"] != "IloscSum" && $product["name"] != "SoldSum" && $product["name"] != "SredniaSum"
|
||
|
|
&& $product["name"] != "KosztSum" && $product["name"] != "MarzaSum" && $product["name"] != "" )
|
||
|
|
{
|
||
|
|
$rowNumber++;
|
||
|
|
$lastRowNumber++;
|
||
|
|
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)
|
||
|
|
->setCellValue('A'.($rowNumber), " ".$product["name"])
|
||
|
|
->setCellValue('B'.($rowNumber), number_format($product["ilosc"], 2, '.', ''))
|
||
|
|
->setCellValue('C'.($rowNumber), number_format($product["netto"], 2, '.', ''))
|
||
|
|
->setCellValue('D'.($rowNumber), number_format($product["srednia"], 2, '.',''))
|
||
|
|
->setCellValue('E'.($rowNumber), number_format($product["koszt"], 2, '.', ''))
|
||
|
|
->setCellValue('F'.($rowNumber), number_format($product["marza"], 2, '.', '')." %");
|
||
|
|
|
||
|
|
// Formatowanie walutowe
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle('B'.($rowNumber))->getNumberFormat()->setFormatCode("#");
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle('C'.($rowNumber))->getNumberFormat()->setFormatCode("#,#0.#0 zł");
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle('D'.($rowNumber))->getNumberFormat()->setFormatCode("#,#0.#0 zł");
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle('E'.($rowNumber))->getNumberFormat()->setFormatCode("#,#0.#0 zł");
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle('F'.($rowNumber))->getNumberFormat()->setFormatCode("#0.#0");
|
||
|
|
|
||
|
|
// Nazwa produktu wyrównane do lewej
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle("A".$rowNumber)->applyFromArray($columnAlignLeft);
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getRowDimension($rowNumber)->setOutlineLevel(2);
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getRowDimension($rowNumber)->setVisible(false);
|
||
|
|
|
||
|
|
}
|
||
|
|
}
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getRowDimension($rowNumber+1)->setCollapsed(true);
|
||
|
|
}
|
||
|
|
|
||
|
|
}
|
||
|
|
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
$rowNumber++;
|
||
|
|
$lastRowNumber++;
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
// Sumy --------------------------------------------------------------------------------------------------
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)
|
||
|
|
->setCellValue('A'.($lastRowNumber), "Podsumowanie: ")
|
||
|
|
->setCellValue('B'.($lastRowNumber), number_format($sum['IloscSumSum'], 2, '.', ''))
|
||
|
|
->setCellValue('C'.($lastRowNumber), number_format($sum['SoldSumSum'], 2, '.', ''))
|
||
|
|
->setCellValue('D'.($lastRowNumber), number_format($sum['SredniaSumSum'], 2, '.', ''))
|
||
|
|
->setCellValue('E'.($lastRowNumber), number_format($sum['KosztSumSum'], 2, '.', ''))
|
||
|
|
->setCellValue('F'.($lastRowNumber), number_format($sum['MarzaSumSum'], 2, '.', ''));
|
||
|
|
|
||
|
|
// Formatowanie walutowe
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle('B'.($lastRowNumber))->getNumberFormat()->setFormatCode("#");
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle('C'.($lastRowNumber))->getNumberFormat()->setFormatCode("#,#0.#0 zł");
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle('D'.($lastRowNumber))->getNumberFormat()->setFormatCode("#,#0.#0 zł");
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle('E'.($lastRowNumber))->getNumberFormat()->setFormatCode("#,#0.#0 zł");
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle('F'.($lastRowNumber))->getNumberFormat()->setFormatCode("#0.#0");
|
||
|
|
|
||
|
|
// Formatowanie wiersza sum -----------------------------------------------------------------------------
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle("A".$lastRowNumber.":F".$lastRowNumber)->applyFromArray($headerStyle);
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle("A".$lastRowNumber)->applyFromArray($columnAlignRight);
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle("B".$lastRowNumber)->applyFromArray($columnAlignRight);
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle("C".$lastRowNumber)->applyFromArray($columnAlignRight);
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle("D".$lastRowNumber)->applyFromArray($columnAlignRight);
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle("E".$lastRowNumber)->applyFromArray($columnAlignRight);
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0)->getStyle("F".$lastRowNumber)->applyFromArray($columnAlignRight);
|
||
|
|
|
||
|
|
|
||
|
|
// Rename worksheet
|
||
|
|
$objPHPExcel->getActiveSheet()->setTitle('Raport sprzedaży');
|
||
|
|
|
||
|
|
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0);
|
||
|
|
|
||
|
|
// Save Excel 2007 file
|
||
|
|
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
|
||
|
|
$objWriter->save( __DIR__ . "/ExcelFiles/ReportSalesByContractor.xls");
|
||
|
|
|
||
|
|
|
||
|
|
?>
|