<?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 InvoicesModelInvoices extends JModelLegacy
{

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

		$mainframe = JFactory::getApplication();

		$params = JComponentHelper::getParams( 'com_invoices' );
		$this->params = $params ;

		$this->keywords = JRequest::getVar('searchword');
		$this->orderby = JRequest::getVar('orderby');
		$date_in = $mainframe->getUserStateFromRequest('invoices.date_in','date_in','','date_in');
		$date_out = $mainframe->getUserStateFromRequest('invoices.date_out','date_out','','date_out');

		$default_list_limit = 30 ;

		// Get pagination request variables
		$limit = $mainframe->getUserStateFromRequest('invoices.items.limit', 'limit', $default_list_limit, 'int');
		//$limitstart = $mainframe->getUserStateFromRequest('muscol.search.limitstart', 'limitstart', 0, 'int');
		$limitstart = JRequest::getVar('limitstart',0);
		// In case limit has been changed, adjust it
		$limitstart = ($limit != 0 ? (floor($limitstart / $limit) * $limit) : 0);

		$this->setState('date_in', $date_in);
		$this->setState('date_out', $date_out);

		$this->setState('limit', $limit);
		$this->setState('limitstart', $limitstart);

	}

	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 getDateIn(){
		if (empty($this->date_in)) {
			$this->date_in = $this->getState('date_in')	;
		}
		return $this->date_in;
	}

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

	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 _buildContentOrderBy(){

		$mainframe = JFactory::getApplication();

		$filter_order     = $mainframe->getUserStateFromRequest( 'invoices.items.order', 'filter_order', 'i.invoice_date', 'cmd' );
		$filter_order_Dir = $mainframe->getUserStateFromRequest( 'invoices.items.order_Dir', 'filter_order_Dir', 'desc', 'word' );

		if(!$filter_order) $filter_order = "i.invoice_date";
		if(!$filter_order_Dir) $filter_order_Dir = "desc";

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

		$orderby .= ' , i.id DESC ' ;

		return $orderby;
	}

	function _buildQuery($type = 1){

		$user = JFactory::getUser();
		//echo $user->id; die();
		$keywords = $this->keywords;

		$date_in = $this->getDateIn();
		$date_out = $this->getDateOut();

		$where_clause = array();

		if ($keywords != "") $where_clause[] = ' ( i.notes LIKE "%'.$keywords.'%" OR i.invoice_num LIKE "%'.$keywords.'%" )';

		if ($date_in) {
			$where_clause[] = ' i.invoice_date >= "'. $date_in .'" ' ;
		}
		if ($date_out) {
			$where_clause[] = ' i.invoice_date <= "'. $date_out .' 23:59:59" ' ;
		}

		$now = date('Y-m-d H:i:s') ;
		$where_clause[] = ' u.id = ' . $user->id ;
		$where_clause[] = ' ( i.start_publish <= "'. $now .'" OR i.start_publish = "0000-00-00 00:00:00") ' ;
		$where_clause[] = ' ( i.end_publish >= "'. $now .'" OR i.end_publish = "0000-00-00 00:00:00") ' ;
		$where_clause[] = ' i.publish = 1 ' ;

		if($user->id) $where_clause[] = ' u.id = ' . $user->id ;
		else $where_clause[] = ' 1 = 0 '; //return nothing

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

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

		$order = $this->_buildContentOrderBy();

		$this->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
		.' GROUP BY i.id '
		.$order
		;

		return $this->query;
	}

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

			$this->_data = $this->_getList($query, $this->getState('limitstart'), $this->getState('limit'));

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

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

				$row->subtotal = InvoicesHelper::get_subtotal($row->id);
				$row->subtotal_items = InvoicesHelper::get_subtotal_items($row->id);

				$row->display_taxes = InvoicesHelper::get_display_taxes($row);

				$row->total = InvoicesHelper::get_total($row);

				$row->subtotal = $row->subtotal_items ;

				//the payments
				$query = 	' SELECT pa.* ' .
				' FROM #__invoices_payments as pa '.
				' WHERE pa.invoice_id = ' . $this->_data[$i]->id .
				' ORDER BY ordering, pa.payment_duedate '
				;

				$this->_db->setQuery($query);
				$this->_data[$i]->payments = $this->_db->loadObjectList() ;

				//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;
	}

	function getQuotes(){
		if (empty( $this->quotes )) {
			$query = $this->_buildQuery(2);

			$this->quotes = $this->_getList($query, $this->getState('limitstart'), $this->getState('limit'));

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

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

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

		}
		return $this->quotes;
	}

}
