<?php
	if (! defined ( 'sugarEntry' ) || ! sugarEntry)
		die ( 'Not A Valid Entry Point' );
	
	/*****************************************************/
	/*********************** PREPARE *********************/
	/*****************************************************/
	$db = $GLOBALS ['db'];
	$data = array();
	$sum = 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"];
	
	$contractorName = $_GET['account_name'];
	$contractorId = $_GET['account_id'];
	$searchByType = $_GET['type'];
	
	$date_from_to_query = new DateTime($date_from);
	$date_to_to_query = new DateTime($date_to);
	
	// Get value pdfType from multiple select
	$pdfTypeU = false;
	$pdfTypeK = false;
	$pdfTypeE = false;  
	
	if( $_GET['pdfType'] && $_GET['pdfType'] != "")
	foreach( $_GET['pdfType'] as $selectedOption )
	{
		switch($selectedOption)
		{
			case "U":
				$pdfTypeU = true;
				break;
			case "K":
				$pdfTypeK = true;
				break;
			case "E":
				$pdfTypeE = true;
				break;
		}
	}
	

	/*****************************************************/
	/******************* QUERY MODEL *********************/
	/*****************************************************/
	
			$query = "SELECT 
					    (SELECT 
					            name
					        FROM
					            accounts
					        WHERE
					            id = e.parent_id) as contractorName,
					    e.id,
					    e.parent_id,
					    e.purchase_price,
					    e.total_brutto as 'total',
					    e.total_netto as 'subtotal',
					    e.document_no,
					    e.type,
					    e.pdf_type,
					    e.ecminvoiceout_id,
					    e.register_date,
					    e.currency_value,
					    e.discount,
					    e.deleted,
					    e.canceled,
					
					    (SELECT 
							sum(
					            CASE WHEN e.type!='correct'
					                THEN
				                        CASE WHEN e.currency_value is null or e.currency_value='' or e.currency_value=0
			                                THEN
		                                        i.total_netto
			                                ELSE
		                                        (i.total_netto)*e.currency_value
				                        END
					                ELSE
									CASE WHEN i.old_ecminvoiceoutitem_id is null OR i.old_ecminvoiceoutitem_id = ''
										THEN
											0
										ELSE
			                                CASE WHEN e.currency_value is null or e.currency_value='' or e.currency_value=0
		                                        THEN
	                                                i.total_netto_corrected
		                                        ELSE
	                                                i.total_netto_corrected*e.currency_value
			                                END
					                END				
					        END)
							FROM
								ecminvoiceoutitems i
									inner join
								ecmproducts p ON i.ecmproduct_id = p.id
							WHERE
								i.ecminvoiceout_id = e.id AND p.group_ks = '1' 					   
								AND e.canceled='0' 
					   				AND e.deleted='0'
										AND i.deleted='0' 
							GROUP BY
								i.ecminvoiceout_id) as 'subPriceGroupKS1',
					
					    (SELECT 
							sum(
					            CASE WHEN e.type!='correct'
					                THEN
				                        CASE WHEN e.currency_value is null or e.currency_value='' or e.currency_value=0
			                                THEN
		                                        i.total_netto
			                                ELSE
	                                        	i.total_netto*e.currency_value
				                        END
					                ELSE
									CASE WHEN i.old_ecminvoiceoutitem_id is null OR i.old_ecminvoiceoutitem_id = ''
										THEN
											0
				                        ELSE
			                                CASE WHEN e.currency_value is null or e.currency_value='' or e.currency_value=0
		                                        THEN
	                                                i.total_netto_corrected
		                                        ELSE
	                                                i.total_netto_corrected*e.currency_value
			                                END					
					                END				
					        END)
							FROM
								ecminvoiceoutitems i
									inner join
								ecmproducts p ON i.ecmproduct_id = p.id
							WHERE
								i.ecminvoiceout_id = e.id AND p.group_ks = '2'
								AND e.canceled='0' 
					   				AND e.deleted='0'
										AND i.deleted='0' 
							GROUP BY
								i.ecminvoiceout_id) as 'subPriceGroupKS2',
					
					    (SELECT 
							sum(
					            CASE WHEN e.type!='correct'
					                THEN
					                        CASE WHEN e.currency_value is null or e.currency_value='' or e.currency_value=0
				                                THEN
				                                        i.total_netto
				                                ELSE
				                                        i.total_netto*e.currency_value
					                        END
										
					                ELSE
										CASE WHEN i.old_ecminvoiceoutitem_id is null OR i.old_ecminvoiceoutitem_id = ''
										THEN
											0
										ELSE 
				                                CASE WHEN e.currency_value is null or e.currency_value='' or e.currency_value=0
				                                        THEN
				                                                i.total_netto_corrected
				                                        ELSE
				                                                i.total_netto_corrected*e.currency_value
				                                END
						                	
										END				
					        END)
							FROM
								ecminvoiceoutitems i
									inner join
								ecmproducts p ON i.ecmproduct_id = p.id
							WHERE
								i.ecminvoiceout_id = e.id AND p.group_ks = '3'
								AND e.canceled='0' 
					   				AND e.deleted='0'
										AND i.deleted='0' 
							GROUP BY
								i.ecminvoiceout_id) as 'subPriceGroupKS3',
					
						    (SELECT 
								sum(
						            CASE WHEN e.type!='correct'
						                THEN
						                        CASE WHEN e.currency_value is null or e.currency_value='' or e.currency_value=0
					                                THEN
					                                        i.total_netto
					                                ELSE
					                                        i.total_netto*e.currency_value
						                        END
											
						                ELSE
											CASE WHEN i.old_ecminvoiceoutitem_id is null OR i.old_ecminvoiceoutitem_id = ''
											THEN
												0
											ELSE 
					                                CASE WHEN e.currency_value is null or e.currency_value='' or e.currency_value=0
					                                        THEN
					                                                i.total_netto_corrected
					                                        ELSE
					                                                i.total_netto_corrected*e.currency_value
					                                END
							                	
											END				
						        END)
								FROM
									ecminvoiceoutitems i
										inner join
									ecmproducts p ON i.ecmproduct_id = p.id
								WHERE
									i.ecminvoiceout_id = e.id AND p.group_ks = '4'
									AND e.canceled='0' 
						   				AND e.deleted='0'
											AND i.deleted='0' 
								GROUP BY
									i.ecminvoiceout_id) as 'subPriceGroupKS4',

					    (SELECT 
							sum(i.quantity * i.price_purchase)
							FROM
								ecminvoiceoutitems i
									inner join
								ecmproducts p ON i.ecmproduct_id = p.id
							WHERE
								i.ecminvoiceout_id = e.id AND p.group_ks = '1'
								AND e.canceled='0' 
					   				AND e.deleted='0'
										AND i.deleted='0' 
							GROUP BY
								i.ecminvoiceout_id) as 'purchasePriceGroupKS1',
					
					    (SELECT 
							sum(i.quantity * i.price_purchase)
							FROM
								ecminvoiceoutitems i
									inner join
								ecmproducts p ON i.ecmproduct_id = p.id
							WHERE
								i.ecminvoiceout_id = e.id AND p.group_ks = '2'
								AND e.canceled='0' 
					   				AND e.deleted='0'
										AND i.deleted='0' 
							GROUP BY
								i.ecminvoiceout_id) as 'purchasePriceGroupKS2',
					
					    (SELECT 
							sum(i.quantity * i.price_purchase)
							FROM
								ecminvoiceoutitems i
									inner join
								ecmproducts p ON i.ecmproduct_id = p.id
							WHERE
								i.ecminvoiceout_id = e.id AND p.group_ks = '3'
								AND e.canceled='0' 
					   				AND e.deleted='0'
										AND i.deleted='0' 
							GROUP BY
								i.ecminvoiceout_id) as 'purchasePriceGroupKS3',

					    (SELECT 
							sum(i.quantity * i.price_purchase)
							FROM
								ecminvoiceoutitems i
									inner join
								ecmproducts p ON i.ecmproduct_id = p.id
							WHERE
								i.ecminvoiceout_id = e.id AND p.group_ks = '4'
								AND e.canceled='0' 
					   				AND e.deleted='0'
										AND i.deleted='0' 
							GROUP BY
								i.ecminvoiceout_id) as 'purchasePriceGroupKS4',

					    (SELECT 
	                	sum(
						    CASE WHEN e.type!='correct'
						        THEN
						            CASE WHEN e.currency_value is null or e.currency_value='' or e.currency_value=0
						                THEN
						                    i.total_netto
						                ELSE
						                	i.total_netto*e.currency_value
						            END
						        ELSE
								CASE WHEN i.old_ecminvoiceoutitem_id is null OR i.old_ecminvoiceoutitem_id = ''
									THEN
										0
									ELSE
						                CASE WHEN e.currency_value is null or e.currency_value='' or e.currency_value=0
						                    THEN
						                        i.total_netto_corrected
						                    ELSE
						                        i.total_netto_corrected*e.currency_value
						                END
									
						        END				
						END)
						FROM
							ecminvoiceoutitems i
						WHERE
							i.ecmvat_value = '0'
							AND e.id = i.ecminvoiceout_id 					   
								AND e.canceled='0' 
					   				AND e.deleted='0'
										AND i.deleted='0'
						) as 'vata',
				
					    (SELECT 
						sum(
						    CASE WHEN e.type!='correct'
						        THEN
						            CASE WHEN e.currency_value is null or e.currency_value='' or e.currency_value=0
						                THEN
						                    i.total_netto
						                ELSE
						                	i.total_netto*e.currency_value
						            END
						        ELSE
								CASE WHEN i.old_ecminvoiceoutitem_id is null OR i.old_ecminvoiceoutitem_id = ''
									THEN
										0
									ELSE
						                CASE WHEN e.currency_value is null or e.currency_value='' or e.currency_value=0
						                    THEN
						                        i.total_netto_corrected
						                    ELSE
						                        i.total_netto_corrected*e.currency_value
						                END
								
						        END				
						END)
						FROM
							ecminvoiceoutitems i
						WHERE
							i.ecmvat_value = '22'
							AND e.id = i.ecminvoiceout_id 					   
								AND e.canceled='0' 
					   				AND e.deleted='0'
										AND i.deleted='0'
						) as 'vatb',

					    (SELECT sum(
						    CASE WHEN e.type!='correct'
						        THEN
						            CASE WHEN e.currency_value is null or e.currency_value='' or e.currency_value=0
						                THEN
						                    i.total_netto
						                ELSE
						                	i.total_netto*e.currency_value
						            END
						        ELSE
								CASE WHEN i.old_ecminvoiceoutitem_id is null OR i.old_ecminvoiceoutitem_id = ''
									THEN
										0
									ELSE
						                CASE WHEN e.currency_value is null or e.currency_value='' or e.currency_value=0
						                    THEN
						                        i.total_netto_corrected
						                    ELSE
						                        i.total_netto_corrected*e.currency_value
						                END
									
						        END				
						END)
						FROM ecminvoiceoutitems i
						WHERE
						i.ecmvat_value = '23'
							AND e.id = i.ecminvoiceout_id					   
								AND e.canceled='0' 
						   			AND e.deleted='0'
										AND i.deleted='0') as 'vatc'
						
					FROM
					    ecminvoiceouts e
					WHERE
					   e.canceled=0 
					   	AND e.deleted=0
								AND e.register_date BETWEEN '".$date_from_to_query->format('Y-m-d')."' 
									AND '".$date_to_to_query->format('Y-m-d')."'
								
			";
	
	
	// vata = 0%
	// vatb = 22%
	// vatc = 23%
	// If user want to see invoices from only one contractor
	if($contractorName && $contractorId) 
	{
		$query .= " AND e.parent_id = '".$contractorId."'";
	}
	// If user want to see invoices by type
	if($searchByType)
	{
		$query .= " AND e.type = '".$searchByType."'";
	}
	// If user want to see invoices by pdfType
	if($pdfTypeU || $pdfTypeK || $pdfTypeE) 
	{
		// One selection
		if($pdfTypeU && !$pdfTypeK && !$pdfTypeE)
			$query .= " AND e.pdf_type = 'U'";
		if(!$pdfTypeU && $pdfTypeK && !$pdfTypeE)
			$query .= " AND e.pdf_type = 'K'";
		if(!$pdfTypeU && !$pdfTypeK && $pdfTypeE)
			$query .= " AND e.pdf_type = 'E'";
		
		// Two selections
		if($pdfTypeU && $pdfTypeK && !$pdfTypeE)
			$query .= " AND (e.pdf_type = 'U' OR e.pdf_type = 'K')";
		if($pdfTypeU && !$pdfTypeK && $pdfTypeE)
			$query .= " AND (e.pdf_type = 'U' OR e.pdf_type = 'E')";
		if(!$pdfTypeU && $pdfTypeK && $pdfTypeE)
			$query .= " AND (e.pdf_type = 'K' OR e.pdf_type = 'E')";
		
		// Three selections
		if($pdfTypeU && $pdfTypeK && $pdfTypeE)
			$query .= " AND (e.pdf_type = 'U' OR e.pdf_type = 'K' OR e.pdf_type = 'E')";
	}

		

	/*****************************************************/
	/*************** GET DATA FROM DB*********************/
	/*****************************************************/	
	$normalInvoicesCount = 0;
	$allInvoicesCount = 0;
	$rows = $db->query ($query);
	
	// prepare data for Smarty
	while( $r = $db->fetchByAssoc ( $rows ))
	{
		$row = array();
		// If invoice is canceled or dleted don't display
		if(!$r["deleted"] && !$r["canceled"])
		{
			$row["id"] = $r["parent_id"]; 
			$row["invoiceoutId"] = $r["id"];
			$row["purchase_price"] = $r["purchase_price"];
			$row["document_no"] = $r["document_no"];
			$row["type"] = $r["type"];
			$row["pdf_type"] = $r["pdf_type"];
			$row["invoiceout_id"] = $r["ecminvoiceout_id"];
			$row["register_date"] = $r["register_date"];
			$row["vata"] = $r["vata"]; // 0%
			$row["vatb"] = $r["vatb"]; // 22%
			$row["vatc"] = $r["vatc"]; // 23%
			/////////////////////////////////////////////////////////
			$row["currency_value"] = $r["currency_value"] == 0 ? 1 : $r["currency_value"];
			$row["subtotal"] = $r["subtotal"]*$row["currency_value"];
			$row["total"] = $r["total"]*$row["currency_value"];

			/////////////////////////////////////////////////////////
			$row["discount"] = $r["discount"] * $row["currency_value"];
			$row["contractorName"] = $r["contractorName"];
			/////////////////// MARGIN AND COST /////////////////////
			if( $r['type'] == 'correcttttt' ) 
			{
				$row["margin"] = 0; 
				$allInvoicesCount++;
			}
			else 
			{
				$normalInvoicesCount++;
				$allInvoicesCount++;
				$row["margin"] = ($row['subtotal'] - $row['purchase_price'])/($row['subtotal'])*100; 
			}
				
			$row["cost"] = $r['purchase_price']; 
			////////////////// Price sum in GroupKS ////////////////

				$row["subPriceGroupKS1"] = $r["subPriceGroupKS1"];
				$row["subPriceGroupKS2"] = $r["subPriceGroupKS2"];
				$row["subPriceGroupKS3"] = $r["subPriceGroupKS3"];
				$row["subPriceGroupKS4"] = $r["subPriceGroupKS4"];
				$row["purchasePriceGroupKS1"] = $r["purchasePriceGroupKS1"];
				$row["purchasePriceGroupKS2"] = $r["purchasePriceGroupKS2"];
				$row["purchasePriceGroupKS3"] = $r["purchasePriceGroupKS3"];
				$row["purchasePriceGroupKS4"] = $r["purchasePriceGroupKS4"];

			
			$data [] = $row;
		}
	}
	
	////////////////// SUM /////////////////////////////////
	foreach( $data as &$element )
	{

			$sum["subtotalSum"] 				+= $element["subtotal"];
			$sum["subPriceGroupKS1Sum"] 		+= $element["subPriceGroupKS1"];
			$sum["subPriceGroupKS2Sum"] 		+= $element["subPriceGroupKS2"];
			$sum["subPriceGroupKS3Sum"] 		+= $element["subPriceGroupKS3"];
			$sum["subPriceGroupKS4Sum"] 		+= $element["subPriceGroupKS4"];
			$sum["totalSum"] 					+= $element["total"];
			$sum["costSum"] 					+= $element["cost"];
			$sum["purchasePriceGroupKS1Sum"] 	+= $element["purchasePriceGroupKS1"];
			$sum["purchasePriceGroupKS2Sum"] 	+= $element["purchasePriceGroupKS2"];
			$sum["purchasePriceGroupKS3Sum"] 	+= $element["purchasePriceGroupKS3"];
			$sum["purchasePriceGroupKS4Sum"] 	+= $element["purchasePriceGroupKS4"];
			$sum["marginSum"] 					+= $element["margin"];
			$sum["vataSum"]						+= $element["vata"];
			$sum["vatbSum"]						+= $element["vatb"];
			$sum["vatcSum"]						+= $element["vatc"];
	}

	$sum["marginSum"] = ($sum["subtotalSum"] - $sum["costSum"]) / ($sum['subtotalSum']) * 100;
?>