<?php
class Sale extends AppModel {
	var $name = 'Sale';
	var $order = array('Sale.date DESC');
	var $validate = array(
		/*'table_id' => array(
			'numeric' => array(
				'rule' => array('numeric'),
				'allowEmpty' => true,
			),
		),*/
		'customer_id' => array(
			'numeric' => array(
				'rule' => array('numeric'),
				'allowEmpty' => true,
			),
		),
		'price' => array(
			'numeric' => array(
				'rule' => array('numeric'),
				'allowEmpty' => true,
			),
		),
	);

	var $belongsTo = array(
		/*'Table' => array(
			'className' => 'Table',
			'foreignKey' => 'table_id',
			'conditions' => '',
			'fields' => '',
			'order' => ''
		),*/
		'User' => array(
			'className' => 'User',
			'foreignKey' => 'user_id',
			'conditions' => '',
			'fields' => '',
			'order' => ''
		),
		'Customer' => array(
			'className' => 'Customer',
			'foreignKey' => 'customer_id',
			'conditions' => '',
			'fields' => '',
			'order' => ''
		),
		'PaymentType' => array(
			'className' => 'PaymentType',
			'foreignKey' => 'payment_type_id',
			'conditions' => '',
			'fields' => '',
			'order' => ''
		),
	);

	var $hasMany = array(
		'RetailItem' => array(
			'className' => 'RetailItem',
			'foreignKey' => 'sale_id',
			'dependent' => false,
			'conditions' => '',
			'fields' => '',
			'order' => '',
			'limit' => '',
			'offset' => '',
			'exclusive' => '',
			'finderQuery' => '',
			'counterQuery' => ''
		),
		'SaleItem' => array(
			'className' => 'SaleItem',
			'foreignKey' => 'sale_id',
			'dependent' => false,
			'conditions' => '',
			'fields' => '',
			'order' => '',
			'limit' => '',
			'offset' => '',
			'exclusive' => '',
			'finderQuery' => '',
			'counterQuery' => ''
		),

	);

	function cancelSale($id = null) {
		$this->contain('SaleItem.Product.ProductItem');
		$this->read(null, $id);

		/*if ($this->data['Sale']['canceled']) {
			return false;
		} else*/ {
			foreach($this->data['SaleItem'] as $i)

{
		
			print_r($i);
		die();
		$record = $i->Model->find('first', array('condition' => array('Model.id' => $i[id])));
	
		unset($record['Model']['id']);
		$this->Model->create();
		$this->Model->saveAll($record);
		
}
			
			
			$this->save();

			return true;
		}
	}




	function saleDates($conditions = array()) {

		$dates = $this->find('all', array(
			'conditions' => $conditions,
			'contain' => array(),
			'fields' => 'DISTINCT(DATE(`Sale`.`date`)) AS `group_date`',
			'order' => array('group_date ASC'),
		));
		foreach($dates as &$d) $d = $d[0]['group_date'];

		return $dates;
	}

	function saleDatesAlt($conditions = array()) {

		// translate conditions
		$conds = array();
		if (!empty($conditions['date_start'])) $conds[] = array("DATE(`Sale`.`date`) >=" => $conditions['date_start']);
		if (!empty($conditions['date_end'])) $conds[] = array("DATE(`Sale`.`date`) <=" => $conditions['date_end']);
		if (!empty($conditions['date'])) $conds[] = array("DATE(`Sale`.`date`)" => $conditions['date']);

		return $this->saleDates($conds);
	}

	function reportPayments($conditions = array()) {

		$temp = $this->find('all', array(
			'conditions' => $conditions,
			'contain' => array('PaymentType'),
			'fields' => array('*, SUM(`price`) AS `group_price`'),
			'group' => '`Sale`.`payment_type_id`',
			//'order' => 'FIELD(`Sale`.`payment_type`, "Cash", "Visa", "American Express", "Master Card", "Diners Club")',
		));
		
		//print_r($temp);

		$result = array();
		foreach($temp as $t) {
			$result[$t['Sale']['payment_type_id']] = array ( 'gp' => $t[0]['group_price'], 'name' => $t['PaymentType']['full_name']);
		}
		unset($temp);
		
		return $result;

		/*$return = array();
		foreach($all_payment_types as $payment_type) {
			$return[$payment_type] = !empty($result[$payment_type]) ? $result[$payment_type] : array();
		}

		return $return;*/
	}
	
	function reportTaxGroupWithPaymentSubtype($conditions = array()) {

		$conds = '';
		if (!empty($conditions['date_start'])) $conds.= " AND DATE(`sales`.`date`) >= '{$conditions['date_start']}'";
		if (!empty($conditions['date_end'])) $conds.= " AND DATE(`sales`.`date`) <= '{$conditions['date_end']}'";
		if (!empty($conditions['date'])) $conds.= " AND DATE(`sales`.`date`) = '{$conditions['date']}'";
		if (!empty($conditions['paymentSubtype'])) $conds.= " AND payment_types.type = '{$conditions['paymentSubtype']}'";
		
		$temp = $this->query("
			SELECT payment_types.name,tax_groups.name, SUM(sale_items.price) AS group_price, SUM(sale_items.price*tax_groups.tax_1/(100+tax_groups.tax_1+tax_groups.tax_2)) as pdv,SUM(sale_items.price*tax_groups.tax_2/(100+tax_groups.tax_1+tax_groups.tax_2)) as pnp FROM sale_items
				LEFT JOIN sales ON sale_items.sale_id=sales.id
				LEFT JOIN products ON sale_items.product_id=products.id
				LEFT JOIN tax_groups ON products.tax_group_id=tax_groups.id
                                LEFT JOIN payment_types ON sales.payment_type_id=payment_types.id
                                WHERE 1=1 {$conds}
				GROUP BY payment_types.id,products.tax_group_id;
		");
		
		return $temp;
	}
	
	function reportNormativ($conditions = array()) {
		$conds = '';
		if (!empty($conditions['date_start'])) $conds.= " AND DATE(s.date) >= '{$conditions['date_start']}'";
		if (!empty($conditions['date_end'])) $conds.= " AND DATE(s.date) <= '{$conditions['date_end']}'";
		if (!empty($conditions['user'])) $conds.= " AND s.user_id = '{$conditions['user']}'";
		$temp = $this->query("select p.name  ,sum(si.amount) as amount, sum(si.price)/sum(si.amount) as price,sum(si.price) as total_price from sales s,sale_items si, products p where s.id=si.sale_id and si.product_id=p.id {$conds} group by p.id");
		
		return $temp;	
	}
function reportTaxGroupForPeriod($conditions = array()) {
			//die(json_encode($conditions, JSON_FORCE_OBJECT));	
		$conds = '';
		if (!empty($conditions['date_start'])) $conds.= " AND DATE(s.date) >= '{$conditions['date_start']}'";
		if (!empty($conditions['date_end'])) $conds.= " AND DATE(s.date) <= '{$conditions['date_end']}'";
		$temp = $this->query("select tg.name  ,sum(si.price*100/(100+tg.tax_1+tg.tax_2)) as base_price,sum(si.price*tg.tax_1/(100+tg.tax_1+tg.tax_2)) as pdv, sum(si.price*tg.tax_2/(100+tg.tax_1+tg.tax_2)) as pnp ,sum(si.price*(tg.tax_1+tg.tax_2)/(100+tg.tax_1+tg.tax_2)) as tax_total,sum(si.price) as price_total
from sales s,sale_items si,products p,product_groups pg, tax_groups tg where s.id=si.sale_id and si.product_id=p.id and p.product_group_id=pg.id and p.tax_group_id=tg.id {$conds}
group by tg.id;
");
		
		return $temp;	
	}
	function reportTaxGroup($conditions = array(), $include_total = true) {

		$conds = '';
		if (!empty($conditions['date_start'])) $conds.= " AND DATE(`sales`.`date`) >= '{$conditions['date_start']}'";
		if (!empty($conditions['date_end'])) $conds.= " AND DATE(`sales`.`date`) <= '{$conditions['date_end']}'";
		if (!empty($conditions['date'])) $conds.= " AND DATE(`sales`.`date`) = '{$conditions['date']}'";

		$temp = $this->query("
			SELECT tax_groups.*, SUM(sale_items.price) AS group_price FROM sale_items
				LEFT JOIN sales ON sale_items.sale_id=sales.id
				LEFT JOIN products ON sale_items.product_id=products.id
				LEFT JOIN tax_groups ON products.tax_group_id=tax_groups.id
				WHERE 1=1 {$conds}
				GROUP BY products.tax_group_id
		");

		$result = array();
		foreach($temp as $t) {
			$result[$t['tax_groups']['id']] = $t;
		}

		$return = array();

		if ($include_total) {
			$totals = array('tax_base' => '', 'tax_1' => 0, 'tax_2' => 0, 'price' => 0);
		}

		// ALL TAX GROUPS VERSION
//		$all_tax_groups = $this->SaleItem->Product->TaxGroup->find('list');
//		foreach($all_tax_groups as $tax_group_id => $tax_group) {
//			$price = (!empty($result[$tax_group_id][0]['group_price']) ? $result[$tax_group_id][0]['group_price'] : 0);
//			$tax_1 = (!empty($result[$tax_group_id]['tax_groups']['tax_1']) ? $result[$tax_group_id]['tax_groups']['tax_1'] / 100 : 0);
//			$tax_2 = (!empty($result[$tax_group_id]['tax_groups']['tax_2']) ? $result[$tax_group_id]['tax_groups']['tax_2'] / 100 : 0);
//
//			$tax_base = ($price / (1 + $tax_1 + $tax_2));
//			$return[$tax_group] = array(
//				'tax_base' => round($tax_base, 2),
//				'tax_1' => round($tax_base * $tax_1, 2),
//				'tax_2' => round($tax_base * $tax_2, 2),
//				'price' => $price,
//			);
//
//			if ($include_total) {
//				$totals['tax_1'] += round($tax_base * $tax_1, 2);
//				$totals['tax_2'] += round($tax_base * $tax_2, 2);
//				$totals['price'] += $price;
//			}
//		}

		// NOT ALL TAX GROUPS VERSION :)
		foreach($result as $tax_group) {
			$price = (!empty($tax_group[0]['group_price']) ? $tax_group[0]['group_price'] : 0);
			$tax_1 = (!empty($tax_group['tax_groups']['tax_1']) ? $tax_group['tax_groups']['tax_1'] / 100 : 0);
			$tax_2 = (!empty($tax_group['tax_groups']['tax_2']) ? $tax_group['tax_groups']['tax_2'] / 100 : 0);

			$tax_base = ($price / (1 + $tax_1 + $tax_2));
			$return[$tax_group['tax_groups']['name']] = array(
				'tax_base' => round($tax_base, 2),
				'tax_1' => round($tax_base * $tax_1, 2),
				'tax_2' => round($tax_base * $tax_2, 2),
				'price' => $price,
			);

			if ($include_total) {
				$totals['tax_1'] += round($tax_base * $tax_1, 2);
				$totals['tax_2'] += round($tax_base * $tax_2, 2);
				$totals['price'] += $price;
			}
		}

		if ($include_total) {
			$return[__('Total', true)] = $totals;
		}

		return $return;

	}

	function reportArticles($conditions = array()) {

		$conds = '';
		if (!empty($conditions['date_start'])) $conds.= " AND DATE(`sales`.`date`) >= '{$conditions['date_start']}'";
		if (!empty($conditions['date_end'])) $conds.= " AND DATE(`sales`.`date`) <= '{$conditions['date_end']}'";
		if (!empty($conditions['date'])) $conds.= " AND DATE(`sales`.`date`) = '{$conditions['date']}'";
		

		$temp = $this->query("
			SELECT articles.*, tax_groups.*, ROUND(SUM(sale_items.amount * product_items.amount), 2) AS group_amount FROM sale_items
				LEFT JOIN sales ON sale_items.sale_id=sales.id
				LEFT JOIN products ON sale_items.product_id=products.id
				LEFT JOIN tax_groups ON products.tax_group_id=tax_groups.id
				LEFT JOIN product_items ON product_items.product_id=products.id
				LEFT JOIN articles ON product_items.article_id=articles.id
				WHERE 1=1 {$conds}
				GROUP BY product_items.article_id
		");

		$result = array();
		foreach($temp as $t) {
			$result[$t['articles']['name']] = $t;
		}

		return $result;
	}

	function reportArticlesNew($conditions = array()) {

		$conds = '';
		if (!empty($conditions['date_start'])) $conds.= " AND DATE(`s`.`date`) >= '{$conditions['date_start']}'";
		if (!empty($conditions['date_end'])) $conds.= " AND DATE(`s`.`date`) <= '{$conditions['date_end']}'";
		if (!empty($conditions['date'])) $conds.= " AND DATE(`s`.`date`) = '{$conditions['date']}'";
		if (!empty($conditions['user'])) $conds.= " AND s.user_id = '{$conditions['user']}'";

		$temp = $this->query("select a.name as name, sum(si.amount*pi.amount) as amount, a.unit as unit,a.price_buy as price,sum(si.amount*pi.amount*a.price_buy) as price_total   from sales s left join sale_items si on s.id=si.sale_id left join product_items pi on si.product_id=pi.product_id left join articles a on a.id=pi.article_id where 1 {$conds} group by a.id");
		
		return $temp;
	}
	
	function reportArticlesStorage() {
		$temp = $this->query("SELECT id,name,amount,unit from articles order by tax_group_id desc, name desc");
		
		return $temp;
	}	
	
	function reportPNP($conditions = array()) {

		$conds = '';
		if (!empty($conditions['date_start'])) $conds.= " AND DATE(`s`.`date`) >= '{$conditions['date_start']}'";
		if (!empty($conditions['date_end'])) $conds.= " AND DATE(`s`.`date`) <= '{$conditions['date_end']}'";
		if (!empty($conditions['date'])) $conds.= " AND DATE(`s`.`date`) = '{$conditions['date']}'";

		$temp = $this->query("SELECT t.id,t.name,sum(si.price/(1+t.tax_2/100)) as base,t.tax_2,sum(si.price-si.price/(1+t.tax_2/100)) as pnp FROM sale_items si left join products p on si.product_id=p.id left join tax_groups t on p.tax_group_id=t.id left join sales s on si.sale_id=s.id where t.tax_2<>0 {$conds} group by t.id");
		
		return $temp;
	}	
	
	function getUsername($id) {
		$tmp=$this->query("SELECT username from users where id=".$id." limit 1");
		
		foreach($tmp as $t) return $t["users"]["username"];
	}
	
}
