<?php


if ($_REQUEST ['date_from'] != "") {
	$date_from = $_REQUEST ['date_from'];
} else {
	$date = new DateTime ();
	$date->modify ( "-1 month" );
	$date_from = $date->format ( "01.m.Y" );
}

if ($_REQUEST ['date_to'] != "") {
	$date_to = $_REQUEST ['date_to'];
} else {
	$date = new DateTime ();
	$date->modify ( "-1 month" );
	$date_to = $date->format ( "t.m.Y" );
}

if ($_REQUEST ['file_id'] != "") {
	$file_id = $_REQUEST ['file_id'];
} else {
	$file_id='';
}

$query="select file_id,file_name,user_id,file_date from orders group by file_id";
$res=$GLOBALS['db']->query($query);
$files = [];
while($dane=$GLOBALS['db']->fetchByAssoc($res)){
	$user = new User();
	$user->retrieve($dane['user_id']);
	$dane['user_name']=$user->full_name;
	$files[]=$dane;
}

if ($_REQUEST ['submit']) {
	
	$query = "SELECT
			    p.name,
				p.code,
			    o.product_id,
				o.parent_id,
			    o.buy_quantity,
			    p.price_msh  as sell_price,
			    o.sell_quantity,
				o.sell_price as purchase_price

			FROM
			    orders o
			        INNER JOIN
			    ecmproducts p ON p.id = o.product_id
    
			WHERE
			    o.date >= '".date("Y-m-d 00:00:00",strtotime($date_from))."'
			        AND o.date <= '".date("Y-m-d 23:59:59",strtotime($date_to))."'
					AND o.file_id = '".$file_id."'
			GROUP BY o.id
			";
	/*     INNER JOIN
    ecminvoiceoutitems i ON i.ecmproduct_id = p.id
    */
//	echo $query;
	$res=$GLOBALS['db']->query($query);

	$products=[];
	while($row=$GLOBALS['db']->fetchByAssoc($res)){
		
		
		$row['total_purchase']=$row['sell_quantity']*$row['sell_price'];
		$row['total_sell']=$row['purchase_price']*$row['sell_quantity'];
		$products[$row['parent_id']]['produkty'][]=$row;
		if($row['sell_price']==""){
			
			$products[$row['parent_id']]['brak_ceny_odsprzedazy']=1;
		}
		if($row['purchase_price']==""){
		
			$products[$row['parent_id']]['brak_ceny_zakupu']=1;
		}
		$products[$row['parent_id']]['total_sell']=$row['total_sell']+$products[$row['parent_id']]['total_sell'];

	}
	
	$query = "SELECT
			    a.name,
			    o.parent_id,
			    SUM(p.price_msh * o.sell_quantity) AS 'total_purchase',
			    SUM(o.buy_quantity) AS 'purchase_quantity',
			    SUM(o.purchase_price * o.sell_quantity) AS 'total_sell',
			    SUM(o.sell_quantity) AS 'sell_quantity',
p.price_msh
			FROM
			    orders o
			        INNER JOIN
			    accounts a ON a.id = o.parent_id

        INNER JOIN
    ecmproducts p ON p.id = o.product_id
			WHERE
			    o.date >= '".date("Y-m-d 00:00:00",strtotime($date_from))."'
			        AND o.date <= '".date("Y-m-d 23:59:59",strtotime($date_to))."'
					AND o.file_id = '".$file_id."'
			GROUP BY parent_id
			ORDER BY total_sell DESC";

	$res=$GLOBALS['db']->query($query);
	
	$rows=[];
	$totals=[];
	while($row=$GLOBALS['db']->fetchByAssoc($res)){
		$row['products']=$products[$row['parent_id']]['produkty'];
		$row['total_sell']=$products[$row['parent_id']]['total_sell'];
		$row['brak_ceny_odsprzedazy']=$products[$row['parent_id']]['brak_ceny_odsprzedazy'];
		$row['brak_ceny_zakupu']=$products[$row['parent_id']]['brak_ceny_zakupu'];
		$rows[$row['parent_id']]=$row;
		$totals['total_sell']+=$row['total_sell'];
		$totals['sell_quantity']+=$row['sell_quantity'];
		$totals['total_purchase']+=$row['total_purchase'];
		$totals['purchase_quantity']+=$row['purchase_quantity'];
	}
}

$smarty = new Sugar_Smarty ();
$smarty->assign ( "rows", $rows);
$smarty->assign ( 'MOD', $mod_strings );
$smarty->assign ( 'files', $files );
$smarty->assign ( 'file_id', $file_id );

$smarty->assign ( 'uploaded', $uploaded );
$smarty->assign ( 'date_from', $date_from );
$smarty->assign ( 'total',$totals) ;
$smarty->assign ( 'date_to', $date_to );
$smarty->assign ( 'dateFormat', '%d.%m.%Y' );

echo $smarty->fetch ( "modules/EcmReports/tpls/RaportMM.html" );
?>