<?php

/*------------------------------------------------------------------------
# com_invoices - Invoice Manager for Joomla
# ------------------------------------------------------------------------
# author				Germinal Camps
# copyright 			Copyright (C) 2012 - 2016 JoomlaThat.com. All Rights Reserved.
# @license				http://www.gnu.org/licenses/gpl-2.0.html GNU/GPL
# Websites: 			http://www.joomlathat.com
# Technical Support:	Forum - http://www.joomlathat.com/support
-------------------------------------------------------------------------*/

//no direct access
defined('_JEXEC') or die('Restricted access.');

jimport( 'joomla.application.component.model' );

class QuotesModelQuotes extends JModelLegacy
{

	var $_data;
	var $_total = null;
	var $_pagination = null;
	var $_keywords = null;

	var $input;

	function __construct(){
		parent::__construct();

		$mainframe = JFactory::getApplication();
		$this->input = $mainframe->input;

		switch($this->input->getInt('type', 1)){
			case 2:
			$scope = "quotes";
			break;
			default:
			$scope = "invoices";
			break;
		}

		// Get pagination request variables
		if($this->input->get('task') != 'save'){
			$this->external_type = $this->input->get('external_type', '', 'string');
			$this->external_id = $this->input->get('external_id', 0, 'int');
			$this->external_ref = $this->input->get('external_ref', '', 'string');
		}
		$limit 				= $mainframe->getUserStateFromRequest('invoices.quotes.limit', 'limit', 20, 'int');
		$limitstart 		= $mainframe->getUserStateFromRequest('invoices.quotes.limitstart', 'limitstart', 0, 'int');
		$keywords 			= trim($mainframe->getUserStateFromRequest('invoices.quotes.keywords','keywords','','string'));
		$filter_order     	= $mainframe->getUserStateFromRequest('invoices.quotes.filter_order', 'filter_order', 'i.id', 'cmd' );
		$filter_order_Dir 	= $mainframe->getUserStateFromRequest('invoices.quotes.filter_order_Dir', 'filter_order_Dir', 'DESC', 'word' );
		$cal_start 			= $mainframe->getUserStateFromRequest('invoices.quotes.cal_start','cal_start','','cmd');
		$cal_end 			= $mainframe->getUserStateFromRequest('invoices.quotes.cal_end','cal_end','','cmd');
		$status_id 			= $mainframe->getUserStateFromRequest('invoices.quotes.status_id','status_id','','cmd');
		$currency_id 			= $mainframe->getUserStateFromRequest('invoices.'.$scope.'.filter_currency_id','filter_currency_id',0,'int');

		$this->contact_id 			= $this->input->getInt('contact_id');

		$this->setState('filter_order', $filter_order);
		$this->setState('filter_order_Dir', $filter_order_Dir);
		$this->setState('limit', $limit);
		$this->setState('limitstart', $limitstart);
		$this->setState('status_id', $status_id);
		$this->setState('currency_id', $currency_id);
		$this->setState('keywords', $keywords);
		$this->setState('cal_start', $cal_start);
		$this->setState('cal_end', $cal_end);

	}

	function getTotal()
	{
 	// Load the content if it doesn't already exist
		if (empty($this->_total)) {
			$query = $this->_buildQuery();
			$this->_total = $this->_getListCount($query);
		}
		return $this->_total;
	}

	function getPagination()
	{
 	// Load the content if it doesn't already exist
		if (empty($this->_pagination)) {
			jimport('joomla.html.pagination');
			$this->_pagination = new JPagination($this->getTotal(), $this->getState('limitstart'), $this->getState('limit') );
		}
		return $this->_pagination;
	}

	function getCalstart(){
		if (empty($this->cal_start)) {
			$this->cal_start = $this->getState('cal_start')	;
		}
		return $this->cal_start;
	}
	function getCalend(){
		if (empty($this->cal_end)) {
			$this->cal_end = $this->getState('cal_end')	;
		}
		return $this->cal_end;
	}

	function getStatusId(){
		if (empty($this->status_id)) {
			$this->status_id = $this->getState('status_id')	;
		}
		return $this->status_id;
	}

	function getKeywords(){
		if (empty($this->_keywords)) {
			$this->_keywords = $this->getState('keywords')	;
		}
		return $this->_keywords;
	}


	function getFilterOrder(){
		return  $this->getState('filter_order') ;
	}
	function getFilterOrderDir(){
		return  $this->getState('filter_order_Dir') ;
	}

	function getTaxes(){

		if (empty( $this->taxes )){
			$query = 	' SELECT * FROM #__invoices_taxes WHERE show_column = 1 ORDER BY ordering, name ';
			$this->_db->setQuery( $query );
			$this->taxes = $this->_db->loadObjectList();
			//print_r($this->_db);die();
		}

		return $this->taxes;

	}

	/**
	* Get an array of all the taxes defined in the system
	* @return array An array of objects, each containing the tax element from the DB
	*/
	function getAllTaxes(){

		if (empty( $this->alltaxes )){
			$query = 	' SELECT * FROM #__invoices_taxes ORDER BY ordering, name ';
			$this->_db->setQuery( $query );
			$this->alltaxes = $this->_db->loadObjectList();
		}

		return $this->alltaxes;

	}

	function getStatus(){

		if (empty( $this->status )){
			$this->status = InvoicesHelper::getQuoteStatus();

		}
		//print_r($this->songs);die();
		return $this->status;

	}

	function getCurrencyId(){
		if (empty($this->currency_id)) {
			$this->currency_id = $this->getState('currency_id')	;
		}
		return $this->currency_id;
	}


	function _buildContentOrderBy()
	{

		$filter_order     = $this->getState('filter_order' ) ;
		$filter_order_Dir = $this->getState('filter_order_Dir') ;

		$orderby = ' ORDER BY '.$filter_order.' '.$filter_order_Dir . ' ';

		return $orderby;
	}

	function getTemplatesData()
		{
			// Lets load the data if it doesn't already exist
			if (empty( $this->_templates_data )){
				$query = ' SELECT * FROM #__invoices_templates '.
						 			' ORDER BY name ';
				$this->_db->setQuery( $query );
				$this->_templates_data = $this->_db->loadObjectList();
			}

		return $this->_templates_data;

	}

	function _buildQuery()
	{

		$keywords = $this->getKeywords();
		$cal_start = $this->getCalstart();
		$cal_end = $this->getCalend();
		$status_id = $this->getStatusId();

		$where_clause = array();
		$where_clause2 = array();
		$where_clause3 = array();

		if ($keywords != ""){
			$where_clause[] = ' ( i.invoice_num LIKE "%'.$keywords.'%" OR i.to_name LIKE "%'.$keywords.'%" OR i.to_company LIKE "%'.$keywords.'%" OR i.to_email LIKE "%'.$keywords.'%" OR i.to_address LIKE "%'.$keywords.'%" OR u.username LIKE "%'.$keywords.'%" OR co.name LIKE "%'.$keywords.'%" ) ';
		}
		if ($cal_start != ""){
			$where_clause[] = ' i.invoice_date >= "'.$cal_start.'" ';
			$where_clause2[] = ' pa.payment_datetime >= "'.$cal_start.'" ';
			$where_clause3[] = ' pa.payment_datetime >= "'.$cal_start.'" ';
		}
		if ($cal_end != ""){
			$where_clause[] = ' i.invoice_date <= "'.$cal_end.'" ';
			$where_clause2[] = ' pa.payment_datetime <= "'.$cal_end.'" ';
			$where_clause3[] = ' pa.payment_datetime <= "'.$cal_end.'" ';
		}
		if ($status_id != ""){
			$where_clause[] = ' i.status = "'.$status_id.'" ';
		}

		$where_clause[] = ' i.type = 2 ' ;

		$orderby = $this->_buildContentOrderBy();

		$where_clause2[] = " payment_status = 1 " ;
		$where_clause3[] = " ( payment_status = 0 OR payment_status = 2 ) " ;
		$where_clause2 = (count($where_clause2) ? ' WHERE '.implode(' AND ', $where_clause2) : '');
		$where_clause3 = (count($where_clause3) ? ' WHERE '.implode(' AND ', $where_clause3) : '');

		// Build the where clause of the content record query
		$where_clause = (count($where_clause) ? ' WHERE '.implode(' AND ', $where_clause) : '');

		$query = ' SELECT i.*, u.name as username, co.name as contact_name, co.user_id as joomla_user_id '
		.' FROM #__invoices_invoices as i '
		.' LEFT JOIN #__invoices_contacts as co ON co.id = i.user_id '
		.' LEFT JOIN #__users as u ON u.id = co.user_id '
		.$where_clause
		.' GROUP BY i.id '
		.$orderby
		;

		$this->query2 = 	 ' SELECT SUM(pa.payment_amount) AS total_income '
		.' FROM #__invoices_payments as pa '
		.' LEFT JOIN #__invoices_invoices as i ON i.id = pa.invoice_id '
		.' LEFT JOIN #__invoices_contacts as co ON co.id = i.user_id '
		.' LEFT JOIN #__users as u ON u.id = co.user_id '
		.$where_clause2
		;
		$this->query3 = 	 ' SELECT SUM(pa.payment_amount) AS total_income '
		.' FROM #__invoices_payments as pa '
		.' LEFT JOIN #__invoices_invoices as i ON i.id = pa.invoice_id '
		.' LEFT JOIN #__invoices_contacts as co ON co.id = i.user_id '
		.' LEFT JOIN #__users as u ON u.id = co.user_id '
		.$where_clause3
		;

		return $query;
	}

	function getTotals(){
		$totals = new stdClass();

		$totals->num_invoices = $this->getTotal();
		//echo $this->query2; die;
		$this->_db->setQuery($this->query2);
		$totals->total_income = $this->_db->loadResult();

		$this->_db->setQuery($this->query3);
		$totals->pending_income = $this->_db->loadResult();
		//print_r($totals->total_income);die;

		return $totals;
	}

	function getData(){

		if (empty( $this->_data )){
			$query = $this->_buildQuery();

			if($this->input->get('task') == "export") $this->_data = $this->_getList($query);
			else $this->_data = $this->_getList($query, $this->getState('limitstart'), $this->getState('limit'));

			//print_r($this->_db);die;
			$taxes = $this->getTaxes();

			for($i = 0; $i < count($this->_data) ; $i++){

				$this->_data[$i]->subtotal = InvoicesHelper::get_subtotal($this->_data[$i]->id);

				$subtotal1 = $this->_data[$i]->subtotal ;

				foreach($taxes as $tax){
					$row_taxes = explode(",", $this->_data[$i]->taxes);
					if(in_array($tax->id, $row_taxes)){

						switch($tax->type){
							case "percent":
								//$taxes_value = ( $tax->value / 100 ) * $this->_data[$i]->subtotal ;

								if($tax->ordering == 1){ //first group of taxes
									$taxes_value = ( $tax->value / 100 ) * $this->_data[$i]->subtotal ;

								}
								else if($tax->ordering == 2){ //second group of taxes
									$taxes_value = ( $tax->value / 100 ) * $subtotal1 ;

								}


								break;
								case "static":
								$taxes_value = $tax->value ;
								break;

							}

							if($tax->ordering == 1){
								$subtotal1 += $taxes_value ;
							}

							$this->_data[$i]->display_taxes[$tax->id] = $taxes_value ;
						}
					}

					$this->_data[$i]->total = InvoicesHelper::get_total($this->_data[$i]);

				//payments
					$query = ' SELECT SUM(pa.payment_amount) AS total_paid, COUNT(DISTINCT pa.id) AS paid_payments FROM #__invoices_payments AS pa WHERE pa.invoice_id = '.$this->_data[$i]->id.' AND pa.payment_status = 1 ' ;
					$this->_db->setQuery($query);
					$paid_temp = $this->_db->loadObject();
					$this->_data[$i]->total_paid = $paid_temp->total_paid;
					$this->_data[$i]->paid_payments = $paid_temp->paid_payments;

					$query = ' SELECT SUM(pa.payment_amount) AS total_unpaid, COUNT(DISTINCT pa.id) AS unpaid_payments FROM #__invoices_payments AS pa WHERE pa.invoice_id = '.$this->_data[$i]->id.' AND pa.payment_status = 0 ' ;
					$this->_db->setQuery($query);
					$unpaid_temp = $this->_db->loadObject();
					$this->_data[$i]->total_unpaid = $unpaid_temp->total_unpaid;
					$this->_data[$i]->unpaid_payments = $unpaid_temp->unpaid_payments;

					$query = ' SELECT SUM(pa.payment_amount) AS total_unpaid_ontime, COUNT(DISTINCT pa.id) AS unpaid_payments_ontime FROM #__invoices_payments AS pa WHERE pa.invoice_id = '.$this->_data[$i]->id.' AND pa.payment_status = 0 AND (pa.payment_duedate > NOW() OR pa.payment_duedate = "0000-00-00 00:00:00") ' ;
					$this->_db->setQuery($query);
					$unpaidontime_temp = $this->_db->loadObject();
					$this->_data[$i]->total_unpaid_ontime = $unpaidontime_temp->total_unpaid_ontime;
					$this->_data[$i]->unpaid_payments_ontime = $unpaidontime_temp->unpaid_payments_ontime;

				}
			}

			return $this->_data;


		}

	}
