<?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 PaymentsModelPayments 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;

		// Get pagination request variables
		$limit = $mainframe->getUserStateFromRequest('invoices.payments.limit', 'limit', 20, 'int');
		$limitstart = $mainframe->getUserStateFromRequest('invoices.payments.limitstart', 'limitstart', 0, 'int');
		$keywords = trim($mainframe->getUserStateFromRequest('invoices.payments.keywords','keywords','','string'));
		$filter_order     = $mainframe->getUserStateFromRequest('invoices.payments.filter_order', 'filter_order', 'pa.id', 'cmd' );
		$filter_order_Dir = $mainframe->getUserStateFromRequest('invoices.payments.filter_order_Dir', 'filter_order_Dir', 'DESC', 'word' );
		$cal_start = $mainframe->getUserStateFromRequest('invoices.payments.cal_start','cal_start','','cmd');
		$cal_end = $mainframe->getUserStateFromRequest('invoices.payments.cal_end','cal_end','','cmd');
		$status_id = $mainframe->getUserStateFromRequest('invoices.payments.status_id','status_id','','cmd');
		$invoice_id = $mainframe->getUserStateFromRequest('invoices.payments.invoice_id','invoice_id','','invoice_id', 'int');
		$currency_id 			= $mainframe->getUserStateFromRequest('invoices.payments.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('invoice_id', $invoice_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 getInvoiceId(){
		if (empty($this->invoice_id)) {
			$this->invoice_id = $this->getState('invoice_id')	;
		}
		return $this->invoice_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 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', 'pa.id' ) ;
		$filter_order_Dir = $this->getState('filter_order_Dir', 'DESC') ;

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

		return $orderby;
	}

	function _buildQuery()
	{

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

		$where_clause = array();
		$where_clause2 = array();
		$where_clause3 = array();
		$where_clause4 = 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.'%" OR pa.payment_description LIKE "%'.$keywords.'%" ) ';
			$where_clause2[] = ' ( 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.'%" OR pa.payment_description LIKE "%'.$keywords.'%" ) ';
			$where_clause3[] = ' ( 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.'%" OR pa.payment_description LIKE "%'.$keywords.'%" ) ';
			$where_clause4[] = ' ( 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.'%" OR pa.payment_description LIKE "%'.$keywords.'%" ) ';
		}
		if ($cal_start != ""){
			$where_clause[] = ' pa.created_datetime >= "'.$cal_start.'" ';
			$where_clause2[] = ' pa.created_datetime >= "'.$cal_start.'" ';
			$where_clause3[] = ' pa.created_datetime >= "'.$cal_start.'" ';
			$where_clause4[] = ' pa.created_datetime >= "'.$cal_start.'" ';
		}
		if ($cal_end != ""){
			$where_clause[] = ' pa.created_datetime <= "'.$cal_end.'" ';
			$where_clause2[] = ' pa.created_datetime <= "'.$cal_end.'" ';
			$where_clause3[] = ' pa.created_datetime <= "'.$cal_end.'" ';
			$where_clause4[] = ' pa.created_datetime <= "'.$cal_end.'" ';
		}
		if ($status_id != ""){
			if($status_id == "paid") $status_id = "1";
			elseif($status_id == "unpaid") $status_id = "0";
			elseif($status_id == "pending") $status_id = "2";

			$where_clause[] = ' pa.payment_status = "'.$status_id.'" ';
		}
		if ($invoice_id != ""){
			$where_clause[] = ' pa.invoice_id = '.$invoice_id.' ';
			$where_clause2[] = ' pa.invoice_id = '.$invoice_id.' ';
			$where_clause3[] = ' pa.invoice_id = '.$invoice_id.' ';
			$where_clause4[] = ' pa.invoice_id = '.$invoice_id.' ';
		}
		if (!empty($this->contact_id)) {

			$where_clause[] = ' i.user_id = '.$this->contact_id.' ';
			$where_clause2[] = ' i.user_id = '.$this->contact_id.' ';
			$where_clause3[] = ' i.user_id = '.$this->contact_id.' ';
			$where_clause4[] = ' i.user_id = '.$this->contact_id.' ';

		}

		$orderby = $this->_buildContentOrderBy();

		$where_clause2[] = " payment_status = 1 " ;
		$where_clause3[] = " payment_status = 0 " ;
		$where_clause4[] = " payment_status = 0 " ;

		$where_clause3[] = " ( pa.payment_duedate = '0000-00-00 00:00:00'  OR (pa.payment_duedate != '0000-00-00 00:00:00' AND pa.payment_duedate > NOW() ) ) ";
		$where_clause4[] = " pa.payment_duedate != '0000-00-00 00:00:00' AND pa.payment_duedate < NOW() ";

		$where_clause2 = (count($where_clause2) ? ' WHERE '.implode(' AND ', $where_clause2) : '');
		$where_clause3 = (count($where_clause3) ? ' WHERE '.implode(' AND ', $where_clause3) : '');
		$where_clause4 = (count($where_clause4) ? ' WHERE '.implode(' AND ', $where_clause4) : '');

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

		$query = ' SELECT pa.*, i.*, pa.id AS id, u.name as username, co.name as contact_name, co.user_id as joomla_user_id '
		. ' 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_clause
		.$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
		;
		$this->query4 = 	 ' 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_clause4
		;

		return $query;
	}

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

		$totals->num_payments = $this->getTotal();
		if(!$totals->num_payments) $totals->num_payments = 0;

		$this->_db->setQuery($this->query2);
		$totals->total_paid = $this->_db->loadResult();
		if(!$totals->total_paid) $totals->total_paid = 0;

		$this->_db->setQuery($this->query3);
		$totals->total_pending = $this->_db->loadResult();
		if(!$totals->total_pending) $totals->total_pending = 0;

		$this->_db->setQuery($this->query4);
		$totals->total_late = $this->_db->loadResult();
		if(!$totals->total_late) $totals->total_late = 0;

		$timespan = "";

		if($this->cal_start) $timespan .= JText::_( 'FROM' ) ." ". JHTML::_('date', $this->cal_start, JText::_('DATE_FORMAT_PAYMENTS')) ;
		if($this->cal_end) $timespan .= " " . JText::_( 'TO' ) ." ". JHTML::_('date', $this->cal_end, JText::_('DATE_FORMAT_PAYMENTS')) ;
		if(!$this->cal_start && !$this->cal_end) $timespan .= JText::_( 'ALL_TIME' ) ;

		$totals->timespan = $timespan;

		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'));

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

				$row =& $this->_data[$i] ;

				//AMOUNT
				$row->invoice_total = InvoicesHelper::get_total_from_id($row->invoice_id);

			}

		}

		return $this->_data;

	}

}
