<?php
//--No direct access
defined('_JEXEC') or die('=;)');

jimport('joomla.application.component.model');
jimport('tcpdf.tcpdf');

class IndianFeesSummaryPDF extends TCPDF {
	//Page header
	var $month ="";
	var $year ="";
	var $sortsummary = false;
	
	public function Header() {
		
		$this->SetFont('helvetica', 'B', 8);
		
		$this->Cell(0, 0,'Fee listing '.$this->year. '-'.$this->month, 0, 0, 'L');
		$this->Ln();
		$margins = $this->getMargins();
		$this->Line($margins['left'],$this->getY(),$this->getPageWidth()-$margins['right'],$this->getY(),array('width'=>0.3));			
	}
	
	// Page footer
	public function Footer() 
	{
		// Position at 1.5 cm from bottom
		$this->SetY(-15);
		$margins = $this->getMargins();
		$this->Line($margins['left'],$this->getY(),$this->getPageWidth()-$margins['right'],$this->getY(),array('width'=>0.3));	
		$this->SetFont('helvetica', '', 8);
		$this->Cell(0, 10, JText::_('Page').' '.$this->getAliasNumPage().'/'.$this->getAliasNbPages(), 0, 0, 'C');
	}
}

class vxcModelIndiaFees extends JModel
{
	var $_data;
	var $_total = null;
	var $_pagination = null;
	
	var $_margin_left = 10;
	var $_margin_top = 15;
	var $_margin_right = 20;
	var $_margin_bottom = 15;
	var $_margin_header = 10;
	var $_margin_footer = 10;
	

	function __construct()
	{
		parent::__construct();
		global $mainframe,$option;
		$this->setDBO(VoxelCareDB::getDatabase());	
		
		$view  =JRequest::getVar('view');		
		//global $option;
		$this->filter_order_Dir	= $mainframe->getUserStateFromRequest( $option.$view.'.filter_order_Dir', 'filter_order_Dir', '', 'word' );
		$this->filter_order	= $mainframe->getUserStateFromRequest( $option.$view.'.filter_order', 'filter_order',	'payment_date', 'cmd' );
		
		
		$this->searchmonth	= $mainframe->getUserStateFromRequest( "$option.$view.searchmonth", 'searchmonth', '', 'int' );
		$this->searchyear	= $mainframe->getUserStateFromRequest( "$option.$view.searchyear", 'searchyear', '', 'int' );
		$this->searchpaymethod	= $mainframe->getUserStateFromRequest( "$option.$view.searchpaymethod", 'searchpaymethod', '', 'string' );
	
		
		$this->searchfranchise = $mainframe->getUserStateFromRequest( "$option.$view.searchfranchise", 'searchfranchise', '', 'string' );
		if (!$this->searchfranchise)
		{
			$userId =LegacyHelper::getUserId();
			$this->_db->setQuery("SELECT uf.franchise id 
								FROM jos_vxc_userfranchise uf 
								WHERE uf.userid = ".$userId);
			
			$this->searchfranchise = $this->_db->loadResult();		
			
				
		}
		
		$this->searchkind	= $mainframe->getUserStateFromRequest( "$option.$view.searchkind", 'searchkind', 'MYUSER', 'string' );
		$this->searchkind	= JString::strtolower( $this->searchkind );

		if ($this->searchkind == 'byuser')
		{
			
			$this->searchuserid	= $mainframe->getUserStateFromRequest( "$option.$view.searchuserid", 'searchuserid', '0', 'string' );
			$this->searchuserid	= JString::strtolower( $this->searchuserid );
			
		}
		
		$this->searchbatch = $mainframe->getUserStateFromRequest( "$option.$view.searchbatch", 'searchbatch', '', 'string' );
		$this->searchorder = $mainframe->getUserStateFromRequest( "$option.$view.searchorder", 'searchorder', '', 'string' );
		$this->searchcustomername = $mainframe->getUserStateFromRequest( "$option.$view.searchcustomername", 'searchcustomername', '', 'string' );
		
		$this->searchgroup = $mainframe->getUserStateFromRequest( "$option.$view.searchgroup", 'searchgroup', '', 'string' );
		$this->searchgroupuserid = $mainframe->getUserStateFromRequest( "$option.$view.searchgroupuserid", 'searchgroupuserid', '', 'string' );
		
		
		
		
		$limit	= $mainframe->getUserStateFromRequest( 'global.list.limit', 'limit', 10, 'int' );		
		
		$limitstart = JRequest::getVar('limitstart',0);
		$limitstart = ($limit != 0 ? (floor($limitstart / $limit) * $limit) : 0);
	
		$this->setState('limit', $limit);
		$this->setState('limitstart', $limitstart);
	}
	
	function _buildQuery()
	{
		$where = array();
		$groupby= array();
		$having=array();

		if ($this->searchmonth)
			$where[] = " MONTH(b.payment_date) = ". (int)$this->searchmonth;
		
		if ($this->searchyear)
			$where[] = " YEAR(b.payment_date) = ". (int)$this->searchyear;
	
		if ($this->searchbatch)
		{
			$where[] = " o.batch = ".$this->searchbatch;
		}
		
		if ($this->searchpaymethod)
			$where[] = " pm.nameid = " .$this->_db->Quote($this->searchpaymethod);
		
		if ($this->searchorder)
		{
			$where[] = " o.id = ".$this->searchorder;
		}
		
		if ($this->searchcustomername)
		{
			$where[] = " o.batch IN ( ".
				" (SELECT o1.batch
					FROM #__vxc_order o1 
					LEFT JOIN #__vxc_customer c1 ON c1.id = o1.customer
					WHERE 				
					 (					 
						( LOWER(o1.reference) LIKE ". $this->_db->Quote('%'.$this->searchcustomername.'%') .')'.
						" OR ".
						"(LOWER(CONCAT_WS(' ',c1.name,c1.middlename,c1.surname)) LIKE ".$this->_db->Quote('%'.$this->searchcustomername.'%').')'.
				  	 ") AND o1.batch = o.batch
				))";
		}
		
		switch($this->searchkind)
		{
			case 'all':
			break;
			case 'myfranchise':
			case 'byfranchise':
				if ($this->searchfranchise)
					$where[] = ' uf.franchise = '. $this->searchfranchise;
			break;		
			case 'mygroups':

				$userId = LegacyHelper::getUserId();
				if ($userId)
				{					
					$where[] = " g.manager = " . $userId;
				}
			break;
			case 'bygroup':
				if ($this->searchgroup)
					$where[] = ' ug.group = '. $this->searchgroup;
			break;
			case 'bygroupuser':
				if ($this->searchgroupuserid)
					$where[] = 'b.payment_user= '. $this->searchgroupuserid;
			break;						
			case 'byuser':					
				if ($this->searchuserid)					
					$where[] = " b.payment_user = ".$this->searchuserid;
			break;
			case 'myuser':
			default:

				$userId = LegacyHelper::getUserId();
				$where[] = " b.payment_user = $userId";

		}		
	
	
		$where[] = " b.id is not null ";
        $where[] = " b.payment_date is not null ";

	//	$where[] = "(oi.design_fee + oi.usage_fee) >0 ";
			
		$where =( count($where) ) ? ' WHERE ' . implode( ' AND ', $where ) : '';
		$having =( count($having) ) ? ' HAVING ' . implode( ' AND ', $having ) : '';
		$groupby =( count($groupby) ) ? ' GROUP BY ' . implode( ' , ', $groupby ) : '';
		
		
		if ($this->sortsummary)
		{
			$orderby = " ORDER BY o.id ASC ";
		}
		else
		{
			$orderby = '';	
		
			$orders = array('id','batch','payment_date','customerfullname','username','totalfee','reference','design_fee','usage_fee');
			
			if (!in_array($this->filter_order,$orders))
				$this->filter_order = 'id';

			if (($this->filter_order) && ($this->filter_order_Dir))
			{
				$orderby 	= ' ORDER BY '. $this->filter_order .' '. $this->filter_order_Dir;
			}		
		}

        $newModeFields = '';
        $newJoins = '';
        $oldModeFields ='';
        if (LegacyHelper::getNewMode())
        {
            $newModeFields = "o.uuid uuid, CONCAT_WS(' ',NULLIF(CONCAT_WS(' ',NULLIF(c.name,''),NULLIF(c.middlename,''),NULLIF(c.surname,'')),''),NULLIF(atfirst.value,''),NULLIF(atmiddle.value,''),NULLIF(atlast.value,'')) fullcustomername,";

            $newJoins = "
            LEFT JOIN jos_vxc_attribute atfirst ON atfirst.orderid = o.id AND atfirst.name = 'patient.firstName'
            LEFT JOIN jos_vxc_attribute atmiddle ON atmiddle.orderid = o.id AND atmiddle.name = 'patient.middleName'
            LEFT JOIN jos_vxc_attribute atlast ON atlast.orderid = o.id AND atlast.name = 'patient.lastName'
            ";
        }
        else {
            $oldModeFields = "CONCAT_WS(' ',NULLIF(c.name,''),NULLIF(c.middlename,''),NULLIF(c.surname,'')) fullcustomername,";
        }

        $this->_query = " SELECT o.id, $newModeFields
								o.reference reference,
								o.batch batch,
								$oldModeFields
								b.payment_date payment_date,								
								b.payment_user user,
								pm.name payment_methodname,								
								u.name username,
								oi.usage_fee,
								oi.design_type,
								oi.design_fee,
								(oi.design_fee + oi.usage_fee) totalfee
				FROM #__vxc_order o
				LEFT JOIN #__vxc_batch b ON b.id = o.batch
				LEFT JOIN #__vxc_customer c ON c.id = o.customer
				LEFT JOIN #__vxc_orderindia oi ON oi.orderid = o.id
				LEFT JOIN #__vxc_user u ON u.id = b.payment_user 				
				LEFT JOIN #__vxc_usergroup ug ON u.id = ug.user
				LEFT JOIN #__vxc_group g ON ug.group = g.id
				LEFT JOIN #__vxc_paymentmethod pm ON pm.id = b.payment_method 
				LEFT JOIN #__vxc_userfranchise uf ON uf.userid = u.id
				LEFT JOIN #__vxc_franchise f ON uf.franchise = f.id
					"
                . $newJoins . ' '
				. $where
				. $groupby
				. $having
				. $orderby
				;
        				
						
		return $this->_query;	
	}
	

	function getData() 
	{
 		if (empty($this->_data))
 		{
			$query = $this->_buildQuery();				
			$this->_db->setQuery( $query,$this->getState('limitstart'), $this->getState('limit') );
			$this->_data = $this->_db->loadObjectList('id');
		}
		return $this->_data;
	}	
	function getPaymentMethods()
	{
		$db =& $this->_db;
		
		$sql = "SELECT pm.nameid value , pm.name text
				FROM #__vxc_paymentmethod  pm ";
		$db->setQuery($sql);
	
		return $db->loadObjectList();	
	
	
	}	
	function getList()
	{
		// table ordering
		$lists['order_Dir']	= $this->filter_order_Dir;
		$lists['order']		= $this->filter_order;

		// search filter
		$lists['searchmonth']= $this->searchmonth;
		$lists['searchpaymethod'] = $this->searchpaymethod;
		$lists['paymentmethods'] = $this->getPaymentMethods();
		$lists['searchyear']= $this->searchyear;
		$lists['searchuserid']= $this->searchuserid;
		$lists['searchkind']= $this->searchkind;
		$lists['searchbatch']= $this->searchbatch;		
		$lists['searchfranchise']= $this->searchfranchise;
		$lists['searchorder'] = $this->searchorder;
		$lists['searchcustomername'] = $this->searchcustomername;
		$lists['searchgroup'] = $this->searchgroup;
		$lists['searchgroupuserid'] = $this->searchgroupuserid;
		
		$lists['users'] = $this->getUsers();
		$lists['groupusers'] = $this->getGroupUsers();
		$lists['groups'] = $this->getGroups();
		$lists['franchises'] = $this->getFranchises();	
		
				
		return $lists;
	}
	
	function getFranchises()
	{
		$db =& $this->_db;
		
		$sql = "SELECT f.id id, f.name name
				FROM #__vxc_franchise f
				ORDER BY f.name";
				
		$db->setQuery($sql);
	
		return $db->loadObjectList();
	
	}
	
	function getGroups()
	{
		$franchiseId = $this->searchfranchise;
		if (!$franchiseId ) return array();

		$userId =LegacyHelper::getUserId();
		if (!$userId) return array();
		
		
		$db =& $this->_db;
		
		$sql = "SELECT g.id id, g.name name
				FROM #__vxc_group g				
				WHERE g.franchise = $franchiseId 
				AND (g.manager IS NULL OR g.manager = ".$userId.")
				ORDER BY g.name";
				
		$db->setQuery($sql);
	
		return $db->loadObjectList();
	}
	
	function getGroupUsers()
	{	
		$groupId = $this->searchgroup;
		
		if (!$groupId ) return array();
				
		$db =& $this->_db;
		
		$sql = "SELECT u.id id , u.name name
				FROM #__vxc_usergroup ug				
				LEFT JOIN #__vxc_user u ON u.id = ug.user
				WHERE ug.group = $groupId
				ORDER BY u.name";
								
		$db->setQuery($sql);
	
		return $db->loadObjectList();	
	}

	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 printFeesPDF(&$pdf)
	{	
		$fees =& $this->getData();
				
		$lang = &JFactory::getLanguage();
		$font = $lang->getPdfFontName();
		$font = ($font) ? $font : 'freesans';

		$pdf = new IndianFeesSummaryPDF('L', 'mm', 'A4', true, 'UTF-8', false); 


		$pdf->year = $this->searchyear;
		$pdf->month = $this->searchmonth;
		
		$pdf->SetMargins($this->_margin_left, $this->_margin_top, $this->_margin_right);		
		
		$pdf->SetAutoPageBreak(TRUE, $this->_margin_bottom);		
		//$pdf->SetAutoPageBreak(false,10);		

		// $pdf->setPrintHeader(true);
		$pdf->setPrintFooter(true);
		
		$pdf->SetFooterMargin($this->_margin_footer);
		// $pdf->SetHeaderMargin($this->_margin_header);							
		
		$pdf->setRTL($lang->isRTL());

		$pdf->setHeaderFont(array($font, '', 10));
		$pdf->setFooterFont(array($font, '', 8));  
		
		$pdf->AliasNbPages();
		$pdf->AddPage(); 
		
		$pdf->SetFont('helvetica','b',18);
		
				
		$pdf->Cell(0,0,'Fee Report',0,1);		
		/*
		$pdf->SetFont('helvetica','',8);
		$pdf->Cell(0,0,$this->searchuserid,0,1);
		
		$pdf->SetFont('helvetica','',12);
		$pdf->Cell(0,0,'Pablo Iglesias, 129 1º',0,1);
		$pdf->Cell(0,0,'03600 ELDA',0,1);
		
		$pdf->SetFont('helvetica','bu',12);		
		$pdf->Cell(40,0,'SPAIN',0,0);
		$pdf->SetFont('helvetica','',12);		
		$pdf->Cell(0,0,'C.E.E. n: ES-B53905667',0,0,'R');
		
		$pdf->Ln(10);
		*/
		$margins = $pdf->getMargins();
		//$pdf->Line($margins['left'],$pdf->getY(),$pdf->getPageWidth()-$margins['right'],$pdf->getY(),array('width'=>0.3));	
		
		$pdf->Ln();
		
		$colInfos = array(		
						array('width'=>20,'text'=>'ORDER'),
						array('width'=>45,'text'=>'USER'),
						array('width'=>45,'text'=>'PATIENT'),
						array('width'=>45,'text'=>'REF.'),
						array('width'=>25,'text'=>'P.DATE'),
						array('width'=>25,'text'=>'USA. FEE'),
						array('width'=>35,'text'=>'DES. TYPE'),
						array('width'=>25,'text'=>'DES. FEE')
						// array('width'=>30,'text'=>'TOTAL')
						);
						
				
		$pdf->SetLineWidth(0.3);
		$pdf->SetFont('helvetica','b',12);
		
		
		
		$totalWidth = 0;
		$rowcount = -1;
		foreach($colInfos as $colInfo)
		{
			$rowcount = max($rowcount,$pdf->getNumLines($colInfo['text'],$colInfo['width']));
			$totalWidth += $colInfo['width'];
		}
		
		foreach($colInfos as $colInfo)		
			$pdf->MultiCell($colInfo['width'],$rowcount * 6,$colInfo['text'],'TB','C',0,0);
			
		$pdf->SetLineWidth(0.2);
		$pdf->Ln();
	
		$pdf->SetFont('helvetica','',12);
		$pdf->SetLineWidth(0.1);
		
		$dimensions = $pdf->getPageDimensions();		
		$hasborder = false; //flag for fringe case
		
		$singleRowHeight = 6.0;
		$amountDesign = 0.0; 
		$amountUsage = 0.0; 
		foreach( $fees  as $fee)
		{		
		
			if ($fee->payment_date)			
			{				
				$date = new JDate($fee->payment_date);
				$date->setOffset(date('Z')/3600.0);
				$dateS = $date->toFormat('%d/%m/%Y');										
			}
			else
				$dataS = '';
						
						
						
						
			$colInfos[0]['text'] =  $fee->id;						
			$colInfos[0]['align'] = 'C';
			
			$colInfos[1]['text'] =  $fee->username;
			$colInfos[1]['align'] = 'L';
			$colInfos[2]['text'] =  $fee->fullcustomername;
			$colInfos[2]['align'] = 'L';
			$colInfos[3]['text'] =  $fee->reference;
			$colInfos[3]['align'] = 'L';
			$colInfos[4]['text'] =  $dateS;
			$colInfos[4]['align'] = 'C';
			$colInfos[5]['text'] =  sprintf("%0.2f",$fee->usage_fee). " €";
			$colInfos[5]['align'] = 'R';
			$colInfos[6]['text'] =  $fee->design_type;
			$colInfos[6]['align'] = 'C';
			$colInfos[7]['text'] =  sprintf("%0.2f",$fee->design_fee). " €";
			$colInfos[7]['align'] = 'R';
			/*
			$colInfos[8]['text'] =  sprintf("%0.2f",$fee->totalfee). " €";
			$colInfos[8]['align'] = 'R';
			*/
			
			
			$rowcount = -1;
			foreach($colInfos as $colInfo)
				$rowcount = max($rowcount,$pdf->getNumLines($colInfo['text'],$colInfo['width']));
						
			$startY = $pdf->GetY();
			 
			if (($startY + $rowcount * $singleRowHeight) + $dimensions['bm'] > ($dimensions['hk'])) {
				//this row will cause a page break, draw the bottom border on previous row and give this a top border
				//we could force a page break and rewrite grid headings here
				if ($hasBorder) {
                    $hasBorder = false;
				} else {
				
					 $pdf->Cell($totalWidth,0,'','T'); //draw bottom border on previous row
					$pdf->Ln();
				}
				$borders = 'LTR';
			} elseif ((ceil($startY) + $rowcount * $singleRowHeight) + $dimensions['bm'] == floor($dimensions['hk'])) {
				//fringe case where this cell will just reach the page break
				//draw the cell with a bottom border as we cannot draw it otherwise
				$borders = 'LRB';
                $hasBorder = true; //stops the attempt to draw the bottom border on the next row
			} else {
				//normal cell
				$borders = 'LR';
			}
			
			// $borders = 'LTBR';
					

			foreach($colInfos as $colInfo)
				$pdf->MultiCell($colInfo['width'],$rowcount*$singleRowHeight,$colInfo['text'],$borders,$colInfo['align'],0,0);
			$pdf->Ln();	
			
			$amountDesign += round($fee->design_fee,2);
			$amountUsage += round($fee->usage_fee,2);									
		}
		$pdf->Cell($totalWidth,0,'','T'); //draw bottom border on previous row
			$pdf->Ln();
		// Fila de subtotales
		$colInfos[0]['text'] =  '';//$fee->id;						
		$colInfos[0]['align'] = 'C';		
		$colInfos[1]['text'] =  '';//$fee->username;
		$colInfos[1]['align'] = 'L';
		$colInfos[2]['text'] =  '';//$fee->fullcustomername;
		$colInfos[2]['align'] = 'L';
		$colInfos[3]['text'] =  '';//$fee->reference;
		$colInfos[3]['align'] = 'L';
		$colInfos[4]['text'] =  'TOT. USAGE:';//$dateS;
		$colInfos[4]['align'] = 'R';
		$colInfos[5]['text'] =  sprintf("%0.2f",$amountUsage). " €";
		$colInfos[5]['align'] = 'R';
		$colInfos[6]['text'] =  'TOT. DESIGN:';//$fee->design_type;
		$colInfos[6]['align'] = 'R';
		$colInfos[7]['text'] =  sprintf("%0.2f",$amountDesign). " €";
		$colInfos[7]['align'] = 'R';
		/*$colInfos[8]['text'] =  sprintf("%0.2f",round($amountUsage + $amountDesign, 2)). " €";
		$colInfos[8]['align'] = 'R';*/
		
		$borders = 'LRTB';
		
		$rowcount = -1;
		foreach($colInfos as $colInfo)
			$rowcount = max($rowcount,$pdf->getNumLines($colInfo['text'],$colInfo['width']));
			
		foreach($colInfos as $colInfo)
			$pdf->MultiCell($colInfo['width'],$rowcount*$singleRowHeight,$colInfo['text'],$borders,$colInfo['align'],0,0);
		$pdf->Ln();	
	
		// Fila de total
		$colInfos[0]['text'] =  '';//$fee->id;						
		$colInfos[0]['align'] = 'C';		
		$colInfos[1]['text'] =  '';//$fee->username;
		$colInfos[1]['align'] = 'L';
		$colInfos[2]['text'] =  '';//$fee->fullcustomername;
		$colInfos[2]['align'] = 'L';
		$colInfos[3]['text'] =  '';//$fee->reference;
		$colInfos[3]['align'] = 'L';
		$colInfos[4]['text'] =  '';//$dateS;
		$colInfos[4]['align'] = 'C';
		$colInfos[5]['text'] =  ''; //sprintf("%0.2f",$amountUsage). " €";
		$colInfos[5]['align'] = 'R';
		/*
		$colInfos[6]['text'] =  //'TOT. DESIGN:';//$fee->design_type;
		$colInfos[6]['align'] = 'C';
		$colInfos[6]['text'] =  'TOTAL:';//sprintf("%0.2f",$amountDesign). " €";
		$colInfos[7]['align'] = 'R';
		$colInfos[8]['text'] =  sprintf("%0.2f",round($amountUsage + $amountDesign, 2)). " €";
		$colInfos[8]['align'] = 'R';
		*/
		
		$colInfos[6]['text'] =  'TOTAL:'; //'TOT. DESIGN:';//$fee->design_type;
		$colInfos[6]['align'] = 'R';
		$colInfos[7]['text'] =  sprintf("%0.2f",round($amountUsage + $amountDesign, 2)). " €";//sprintf("%0.2f",$amountDesign). " €";
		$colInfos[7]['align'] = 'R';
		
		$borders = 'LRTB';
		
		$rowcount = -1;
		foreach($colInfos as $colInfo)
			$rowcount = max($rowcount,$pdf->getNumLines($colInfo['text'],$colInfo['width']));
			
		foreach($colInfos as $colInfo)
			$pdf->MultiCell($colInfo['width'],$rowcount*$singleRowHeight,$colInfo['text'],$borders,$colInfo['align'],0,0);
		$pdf->Ln();	
	
		
		/*
		if ($oldtype!=-1)
		{			
			$pdf->Cell($col1W+$col2W+$col3W,0,'TOTAL:','TB',0,'R');
			$pdf->Cell($col4W,0,sprintf("%0.02f",$amount). " €",'TB',0,'R');				
			$pdf->Ln();			
			$pdf->Ln();
		}*/	
	}
	
	function getUsers()
	{
		$franchiseId = $this->searchfranchise;
		if (!$franchiseId ) return array();
		
		$db =& $this->_db;
		
		$sql = "SELECT u.id id , u.name name
				FROM #__vxc_user u
				LEFT JOIN #__vxc_userfranchise uf ON u.id = uf.userid
				WHERE uf.franchise = $franchiseId AND uf.deleted IS NOT TRUE
				ORDER BY u.name";
		$db->setQuery($sql);
	
		return $db->loadObjectList();
	
	}
	
	
}// class