crm.e5.pl/modules/EcmCharts/Dashlets/MyContractorsChartsSalesDashlet/MyContractorChartSalesHelper.php

<?php 

/*
 * Klasa reprezentująca kontrahenta
 */
class Contractor {
	// Nazwa kontrahenta
	var $name;
	// Cena netto 
	var $subtotal;
	// Koszty
	var $cost;

	public function __construct($name, $subtotal, $cost) 
	{
		$this->name = $name;
		$this->subtotal = $subtotal;
		$this->cost = $cost;
	}

}

/*
 * Klasa pomocnicza przy generowaniu wykresów dla MyContractorsChartsSalesDashlet
 */
class MyContractorChartSalesHelper {
	
	// Global database connection instance
	var $db_connection;
	// Chart options
	var $options;
	// Tablica kontrahentów
	var $contractors = array();
	// Dane porównawcze
	var $compData = null;

	// Constructor
	public function __construct($db_connection, $options, $comparativeData = null)
	{
		//$this->log("Parametry dashletu: ", $options );
		$this->db_connection = $db_connection;
		$this->options = $options;
		$this->compData = $comparativeData;

		$this->getData();
	}
	
	public function haveComparativeData()
	{
		if( $this->compData != null )
			return true;
		else
			return false;
	}
	

	//----------------------------------------
	// Private methods
	//----------------------------------------

	/**
	* Metoda pobiera wszystkie dane o kontrahentach
	*/
	private function getData()
	{
		
		$date_from = $this->prepareDateToQuery( new DateTime($this->options["date_from"]) );
		$date_to = $this->prepareDateToQuery( new DateTime($this->options["date_to"]) );
		$type = $this->options["type"];
		$count = $this->options["count"];
		
		
		$query = " 
				SELECT 
			    acco.name as 'name',
			    acco.parent_id as 'parent',
			        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.total_netto
			                ELSE
			                    pozycja.total_netto*faktura.currency_value
			            END
			        ELSE  
			        CASE WHEN faktura.currency_value is null or faktura.currency_value='' or faktura.currency_value=0
			                THEN
			                    pozycja.total_netto-pozycja.old_total_netto
			                ELSE
			                    (pozycja.total_netto-pozycja.old_total_netto)*faktura.currency_value
			        END   			
				END
			    ) as netto,
			    sum(
		            CASE WHEN faktura.type!='correct' 
	                    THEN 
                            pozycja.price_purchase*pozycja.quantity 
	                    ELSE 
                            0 
		            END
			    ) as cost
		        FROM
		            ecminvoiceoutitems pozycja
		                JOIN
		            ecminvoiceouts faktura ON pozycja.ecminvoiceout_id = faktura.id
		                JOIN
		            accounts acco ON acco.id = faktura.parent_id
		        WHERE
		            faktura.register_date BETWEEN 
		            '$date_from' AND '$date_to'
		                    and faktura.type like '$type'
								and faktura.canceled = 0
									and faktura.deleted= 0
										and pozycja.deleted= 0";

		        if( $this->options["group_media_saturn_holding"] == 'enabled' )
		        	$query .= " GROUP BY IFNULL(acco.parent_id, acco.id) ";
		        else
		        	$query .= " GROUP BY acco.id ";

		        $query .= "ORDER BY netto DESC
		        LIMIT $count;				    
				";

		$results = $this->db_connection->query( $query );
		$allContractors = array();

		while( $result = $this->db_connection->fetchByAssoc( $results ) )
		{
			if( $result['parent'] == "1249" && $this->options["group_media_saturn_holding"] == 'enabled')
				$name = "Media Saturn Holding";
			else
				$name = $result["name"];

			$contractor = new Contractor($name, 
								   $result["netto"], 
								   $result["cost"]);
		

			if( $contractor != null )
				$allContractors[] = $contractor;
		}

		$this->contractors = $allContractors;
	}

	public function showContractors()
	{
		print_r( $this->contractors );
	}

	public function getContractors()
	{
		return $this->contractors;
	}

	/**
	* Metoda przygotowuje datę do wstawienia w zapytanie SQL
	*/
	private function prepareDateToQuery( DateTime $d )
	{
		return $d->format('Y-m-d');
	}

	/**
	* Render data to Google Charts options
	*/
	public function renderGoogleChartOptions()
	{
		$data = "";

    	for( $i = 0; $i < count($this->contractors); $i++)
    	{
    		if( !$this->contractors[$i]->isEmpty ) {
    			$data .= "['".$this->contractors[$i]->name."', ".$this->contractors[$i]->subtotal;
    			if( $this->options["comparativeData"] == 'enabled' )
    				$data .= ", ".$this->compData[$i]->subtotal."0],";
    			else 
    				$data .= "],";
    			//echo "['".$this->categories[$i]->name."', ".$this->categories[$i]->subtotal.", ".$this->compData[$i]->subtotal."],<br>";
    		}
    	}

	    $data = rtrim($data, ",");
	    return $data;
	}


} // end class


?>