<?php
class Sales {
	var $date_from;
	var $date_to;
	var $category_id;
	var $product_id;
	var $product_active;
	var $account_id;
	var $product_status;
	var $invoice_kind;
	function getSalesByCategory() {
		$wh = array ();
		$wh [] = "e.deleted='0'";
		$wh [] = "i.deleted='0'";
		$wh [] = "(w.deleted is null or w.deleted='0')";
		$wh [] = "(p.deleted is null or p.deleted='0')";
		$wh [] = "e.canceled!='1'";
		// $wh [] = "e.type='correct'";
		if ($this->invoice_kind == 'foreign')
			$wh [] = "e.pdf_type!='K'";
		if ($this->invoice_kind == 'country')
			$wh [] = "e.pdf_type='K'";
		if ($this->date_from)
			$wh [] = "e.register_date>='" . $this->date_from . "'";
		if ($this->date_to)
			$wh [] = "e.register_date<='" . $this->date_to . "'";
			// $wh [] = "e.register_date<='2013-11-01'";
		if ($this->product_active) {
			if ($this->product_active == "active")
				$wh [] = "p.product_active='1'";
			if ($this->product_active == "inactive")
				$wh [] = "p.product_active='0'";
		}
		if ($this->group_ks)
			$wh [] = "p.group_ks = '" . $this->group_ks . "'";
		if ($this->product_status) {
			$wh [] = "p.status='" . $this->product_status . "'";
		}
		if ($this->account_id)
			$wh [] = "(e.parent_id='" . $this->account_id . "' or w.parent_id='" . $this->account_id . "')";
		$q = "select
		sum(
			CASE WHEN e.type!='correct'
				THEN
					CASE WHEN e.currency_value_nbp is null or e.currency_value_nbp='' or e.currency_value_nbp=0
						THEN
							i.total_netto
						ELSE
							i.total_netto*e.currency_value_nbp
					END
				ELSE
					CASE WHEN i.old_total_netto IS null OR i.old_total_netto='' THEN
						0
					ELSE
						CASE WHEN e.currency_value_nbp is null or e.currency_value_nbp='' or e.currency_value_nbp=0
							THEN
								i.total_netto-i.old_total_netto
							ELSE
								(i.total_netto-i.old_total_netto)*e.currency_value_nbp
						END
				END				
			END
		) as sales,
		sum(
			CASE WHEN e.type!='correct' 
				THEN 
					i.quantity
				ELSE 
					i.quantity-i.old_quantity 
			END
		) as quantity,
		sum(
			CASE WHEN e.type!='correct' 
				THEN 
					i.price_purchase*i.quantity 
				ELSE 
					0 
			END
		) as purchase,
		count(e.id) as inv_qty,
		p.product_category_name,
		p.product_category_id,
		MONTH(e.register_date) as month
		from ecminvoiceoutitems as i
		inner join ecminvoiceouts as e on e.id=i.ecminvoiceout_id
		left join ecmstockdocouts as w on e.wz_id=w.id
		left join ecmproducts as p on p.id=i.ecmproduct_id
		where " . implode ( " and ", $wh ) . "
		group by p.product_category_id,MONTH(e.register_date)";
		echo $q;
		$query = $GLOBALS ['db']->query ( $q );
		while ( $row = $GLOBALS ['db']->fetchByAssoc ( $query ) ) {
			$data [$row ['month']] [$row ['product_category_id']] = $row;
		}
		
		return $data;
	}
	function getSalesByProduct() {
		$wh = array ();
		$wh [] = "e.deleted='0'";
		$wh [] = "i.deleted='0'";
		$wh [] = "p.product_category_id='" . $this->category_id . "'";
		$wh [] = "(w.deleted is null or w.deleted='0')";
		$wh [] = "(p.deleted is null or p.deleted='0')";
		$wh [] = "e.canceled!='1'";
		// $wh[] = "e.type='correct'";
		// $wh[]="e.status='accepted'";
		if ($this->date_from)
			$wh [] = "e.register_date>='" . $this->date_from . "'";
		if ($this->date_to)
			$wh [] = "e.register_date<='" . $this->date_to . "'";
		if ($this->product_active) {
			if ($this->product_active == "active")
				$wh [] = "p.product_active='1'";
			if ($this->product_active == "inactive")
				$wh [] = "p.product_active='0'";
		}
		if ($this->product_status) {
			$wh [] = "p.status='" . $this->product_status . "'";
		}
		if ($this->account_id)
			$wh [] = "(e.parent_id='" . $this->account_id . "' or w.parent_id='" . $this->account_id . "')";
		$q = "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.subtotal
						ELSE
							i.subtotal*e.currency_value
					END
				ELSE
					CASE WHEN i.old_subtotal IS null OR i.old_subtotal='' THEN
						0
					ELSE
						CASE WHEN e.currency_value is null or e.currency_value='' or e.currency_value=0
							THEN
								i.subtotal-i.old_subtotal
							ELSE
								(i.subtotal-i.old_subtotal)*e.currency_value
						END
				END
			END
		) as sales,
		sum(
			CASE WHEN e.type!='correct'
				THEN
					i.quantity
				ELSE
					i.quantity-i.old_quantity
			END
		) as quantity,
		sum(
			CASE WHEN e.type!='correct'
				THEN
					i.purchase_price*i.quantity
				ELSE
					0
			END
		) as purchase,
		count(e.id) as inv_qty,
		p.product_category_name,
		p.product_category_id,
		p.id,
		MONTH(e.register_date) as month
		from ecminvoiceoutitems as i
		inner join ecminvoiceouts as e on e.id=i.ecminvoiceout_id
		left join ecmstockdocouts as w on e.wz_id=w.id
		left join ecmproducts as p on p.id=i.ecmproduct_id
		where " . implode ( " and ", $wh ) . "
		group by p.id, month(e.register_date)";
		
		// echo 'from class.sales.php <br>'.$q.'<br>---<br>';
		$query = $GLOBALS ['db']->query ( $q );
		echo mysql_error ();
		while ( $row = $GLOBALS ['db']->fetchByAssoc ( $query ) ) {
			$data [$row ['month']] [$row ['id']] = $row;
		}
		return $data;
	}
	function getSalesByProductForecast() {
		$wh = array ();
		$wh [] = "e.deleted='0'";
		$wh [] = "i.deleted='0'";
		$wh [] = "(w.deleted is null or w.deleted='0')";
		$wh [] = "(p.deleted is null or p.deleted='0')";
		$wh [] = "(ci.deleted is null or ci.deleted='0')";
		// $wh[]="e.status='accepted'";
		if ($this->date_from)
			$wh [] = "e.register_date>='" . $this->date_from . "'";
		if ($this->date_to)
			$wh [] = "e.register_date<='" . $this->date_to . "'";
		if ($this->product_active) {
			if ($this->product_active == "active")
				$wh [] = "p.product_active='1'";
			if ($this->product_active == "inactive")
				$wh [] = "p.product_active='0'";
		}
		if ($this->account_id)
			$wh [] = "(e.parent_id='" . $this->account_id . "' or w.parent_id='" . $this->account_id . "')";
		$q = "select
		sum(
			CASE WHEN e.type!='correct' 
				THEN 
					CASE WHEN e.currency_value is null or e.currency_value='' 
						THEN 		
							i.price*i.quantity 
						ELSE 
							i.price*i.quantity*e.currency_value 
					END 
				ELSE 
				CASE WHEN ci.price is null or ci.quantity is null 
					THEN 
						0 
					ELSE 
						i.price*i.quantity-ci.price*ci.quantity 
				END 
			END
		) as sales,
		sum(
			CASE WHEN e.type!='correct' 
				THEN 
					i.purchase_price*i.quantity 
				ELSE 
				CASE WHEN ci.quantity is null 
					THEN 
						0 
					ELSE 
						ci.purchase_price*i.quantity-ci.purchase_price*ci.quantity 
				END 
			END
		) as purchase,
		sum(
			CASE WHEN e.type!='correct' 
				THEN 
					i.quantity
				ELSE 
				CASE WHEN ci.quantity is null 
					THEN 
						0 
					ELSE 
						i.quantity-ci.quantity 
				END 
			END
		) as quantity,
		avg(i.price) as avg_price,
		p.id
		from ecminvoiceoutitems as i 
		left join ecminvoiceoutitems as ci on i.ecminvoiceoutitem_id=ci.id
		inner join ecminvoiceouts as e on e.id=i.ecminvoiceout_id
		left join ecmstockdocouts as w on e.wz_id=w.id
		left join ecmproducts as p on p.id=i.ecmproduct_id
		where 
		" . implode ( " and ", $wh ) . " 
		group by p.id";
		$query = $GLOBALS ['db']->query ( $q );
		echo mysql_error ();
		while ( $row = $GLOBALS ['db']->fetchByAssoc ( $query ) ) {
			$data [$row ['id']] = $row;
		}
		return $data;
	}
	function getValueByCode($data, $code, $type = "") {
		$r = $GLOBALS ['db']->fetchByAssoc ( $GLOBALS ['db']->query ( "select conversion_rate from currencies where name like 'USD'" ) );
		for($i = 0; $i < count ( $data ); $i ++) {
			if (eregi ( "_", $data [$i] ['productcode'] )) {
				$e = explode ( "_", $data [$i] ['productcode'] );
				$code1 = trim ( $e [0] );
			} else
				$code1 = trim ( $data [$i] ['productcode'] );
			if (strtoupper ( $code1 ) == strtoupper ( $code )) {
				if ($type) {
					if ($data [$i] ['postatus'] == $type) {
						$value += $r ['conversion_rate'] * $data [$i] ['quantity'] * $data [$i] ['listprice'] / 0.8;
						$qty += $data [$i] ['quantity'];
					}
				} else {
					$value += $r ['conversion_rate'] * $data [$i] ['quantity'] * $data [$i] ['listprice'] / 0.8;
					$qty += $data [$i] ['quantity'];
				}
			}
		}
		return array (
				"value" => $value,
				"qty" => $qty 
		);
	}
	function getSumLast($account_id, $product_active) {
		$wh [] = "e.register_date>'" . date ( "Y-m-d", mktime () - 3 * 30 * 24 * 3600 ) . "'";
		$wh [] = "e.deleted='0'";
		$wh [] = "i.deleted='0'";
		$wh [] = "p.deleted='0'";
		// $wh[]="e.status='accepted'";
		if ($account_id)
			$wh [] = "(e.parent_id='" . $account_id . "' or w.parent_id='" . $account_id . "')";
		if ($product_active && $product_active == "active")
			$wh [] = "p.product_active='1'";
		elseif ($product_active && $product_active == "inactive")
			$wh [] = "p.product_active='0'";
		elseif ($product_active && $product_active == "inactive")
			$wh = " and p.product_active='0'";
		
		$w = $GLOBALS ['db']->query ( "select i.purchase_price,i.quantity,i.ecmproduct_id,p.product_category_id from ecminvoiceoutitems as i inner join ecminvoiceouts as e on e.id=i.ecminvoiceout_id inner join ecmproducts as p on i.ecmproduct_id=p.id where " . implode ( " and ", $wh ) );
		// echo "select i.purchase_price,i.quantity,i.ecmproduct_id,p.product_category_id from ecminvoiceoutitems as i inner join ecminvoiceouts as e on e.id=i.ecminvoiceout_id inner join ecmproducts as p on i.ecmproduct_id=p.id where " . implode ( " and ", $wh );
		while ( $r = $GLOBALS ['db']->fetchByAssoc ( $w ) ) {
			$arr [$r ['product_category_id']] += $r ['purchase_price'] * $r ['quantity'];
			$arr [$r ['ecmproduct_id']] += $r ['purchase_price'] * $r ['quantity'];
		}
		return $arr;
	}
}
?>