<?php
//--No direct access
defined('_JEXEC') or die('=;)');

jimport('joomla.application.component.model');
jimport('tcpdf.tcpdf');


class CustomerInvoicesSummaryPDF extends TCPDF {
	//Page header
	var $month ="";
	var $year ="";
	var $sortsummary = false;
	var $sortkind = 'byinvoice';
	
	public function Header() {
		
		$this->SetFont('helvetica', 'B', 8);
		
		$this->Cell(0, 0,'Invoices summary '.$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 vxcModelCustomerInvoicing extends JModel
{
	var $_data;
	var $_total = null;
	var $_pagination = null;
	
	var $_margin_left = 20;
	var $_margin_top = 20;
	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->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' );
		
		
		
		
		$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(i.generation_date) = ". (int)$this->searchmonth;
		
		if ($this->searchyear)
			$where[] = " YEAR(i.generation_date) = ". (int)$this->searchyear;
	
		if ($this->searchbatch)
		{
			$where[] = " o.batch = ".$this->searchbatch;
		}
		
		if ($this->searchorder)
		{
			$where[] = " i.orderid = ".$this->searchorder;
		}
		
		if ($this->searchcustomername)
		{
            $cond = ' (( 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.'%').')';

            if (LegacyHelper::getNewMode())
                $cond .=" OR (LOWER(CONCAT_WS(' ',atfirst1.value,atmiddle1.value,atlast1.value)) LIKE ".$this->_db->Quote('%'.$this->searchcustomername.'%').')';

            $cond .=  ')';

            $newJoins = '';

            if (LegacyHelper::getNewMode())
                $newJoins = "
                    LEFT JOIN jos_vxc_attribute atfirst1 ON atfirst1.orderid = o1.id AND atfirst1.name = 'patient.firstName'
                    LEFT JOIN jos_vxc_attribute atmiddle1 ON atmiddle1.orderid = o1.id AND atmiddle1.name = 'patient.middleName'
                    LEFT JOIN jos_vxc_attribute atlast1 ON atlast1.orderid = o1.id AND atlast1.name = 'patient.lastName'
                    ";



            $where[] = " o.batch IN ( ".
				" (SELECT o1.batch
					FROM #__vxc_order o1 
					LEFT JOIN #__vxc_customer c1 ON c1.id = o1.customer $newJoins
					WHERE (	$cond  ) 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 'byuser':					
				if ($this->searchuserid)					
					$where[] = " o.user = ".$this->searchuserid;
			break;
			case 'myuser':
			default:
				$userId = LegacyHelper::getUserId();
				$where[] = " o.user = $userId";

		}		
	
	
		
			
		$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 i.paid DESC , 
								i.returned ASC ";
								
			if ($this->sortkind=='byinvoice')
				$orderby .=", i.reference ASC";
			else if ($this->sortkind=='byuser')
                $orderby .=", u.name ASC";
            else
				$orderby .=", o.id ASC";
		}
		else
		{
			$orderby = '';	
		
			$orders = array('id','reference','username','paid','returned','total','batch');
			
			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,'')) customerfullname,";

            $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,'')) customerfullname,";
        }

		$this->_query = " SELECT i.id, $newModeFields
								o.batch batch, $oldModeFields
								o.user user,
								o.id orderid,
								o.reference orderreference,
								i.reference reference,
								i.orderid orderid,
								i.generation_date generation_date,
								i.total total, i.paid paid,
								i.returned returned,
								pm.name payment_methodname,								
								u.name username
				FROM #__vxc_customerinvoice i
				LEFT JOIN #__vxc_order o ON o.id = i.orderid
				LEFT JOIN #__vxc_customer c ON c.id = o.customer
				LEFT JOIN #__vxc_batch b ON b.id = o.batch
				LEFT JOIN #__vxc_user u ON u.id = o.user
				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 getList()
	{
		// table ordering
		$lists['order_Dir']	= $this->filter_order_Dir;
		$lists['order']		= $this->filter_order;

		// search filter
		$lists['searchmonth']= $this->searchmonth;
		$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['users'] = $this->getUsers();		
				
		return $lists;
	}

	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');
			$totalCount = $this->getTotal();			
			$this->_pagination = new JPagination($totalCount, $this->getState('limitstart'), $this->getState('limit') );			
		}
		
		return $this->_pagination;
	}
	
	
	function printSummaryPDF(&$pdf)
	{	
		$invoices =& $this->getData();
				
		$lang = &JFactory::getLanguage();
		$font = $lang->getPdfFontName();
		$font = ($font) ? $font : 'freesans';

		$pdf = new InvoicesSummaryPDF('P', '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->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,'VoxelWorks S.L',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();
		$col1W = 30;
		$col2W = 30;
		$col3W = 35;
		$col4W = 30;
		$col5W = 20;
		$col6W = 35;
		
		$pdf->SetLineWidth(0.3);
		$pdf->SetFont('helvetica','b',12);
		$headerHeight = 13;		
		$pdf->writeHTMLCell($col1W,$headerHeight,'','','REF.','TB',0,0,true,'C');
		$pdf->writeHTMLCell($col5W,$headerHeight,'','','PEDIDO','TB',0,0,true,'C');
		$pdf->writeHTMLCell($col6W,$headerHeight,'','','PACIENTE','TB',0,0,true,'C');
		$pdf->writeHTMLCell($col2W,$headerHeight,'','','FECHA','TB',0,0,true,'C');
		$pdf->writeHTMLCell($col3W,$headerHeight,'','','USUARIO','TB',0,0,true,'C');
		$pdf->writeHTMLCell($col4W,$headerHeight,'','','TOTAL','TB',0,0,true,'C');
		$pdf->SetLineWidth(0.2);
		$pdf->Ln();
	
		$pdf->SetFont('helvetica','',12);
		$pdf->SetLineWidth(0.1);
		$oldtype = -1;
		foreach( $invoices as $inv)
		{			
			if ($inv->paid ==1) 
				$type = 0;
			else if ($inv->returned)
				$type = 1;
			else
				$type = 2;
				
			if ($oldtype!=$type)
			{
				switch($type)
				{
					case 0: $titulo = "Facturas pagadas"; break;
					case 1: $titulo = "Facturas devueltas"; break;
					case 2: $titulo = "Facturas impagadas"; break;					
				}
			
				if ($oldtype!=-1)
				{			
					$pdf->Cell($col1W+$col2W+$col3W+$col5W+ $col6W,0,'TOTAL:','TB',0,'R');
					$pdf->Cell($col4W,0,sprintf("%0.02f",$amount). " €",'TB',0,'R');				
					$pdf->Ln();			
					$pdf->Ln();
				}
				
				$pdf->SetFont('helvetica','b',12);
				$pdf->Cell($col1W+$col2W+$col3W+$col4W+$col5W + $col6W,0,$titulo,'TB',0,'C');
				$pdf->SetFont('helvetica','',12);
				$pdf->Ln();
				$amount = 0;
			
			}
			$oldtype = $type;
			
			if ($inv->generation_date)			
			{				
				$date = new JDate($inv->generation_date);
				$date->setOffset(date('Z')/3600.0);
				$dateS = $date->toFormat('%d/%m/%Y');										
			}
			else
				$dateS = '';
						
			$amount += $inv->total;
			$total = sprintf("%0.02f",$inv->total). " €";			
			$pdf->Cell($col1W,0,$inv->reference,'TB',0,'L',false,'',1);
			$pdf->Cell($col5W,0,$inv->orderid,'TB',0,'L',false,'',1);
			
			
			$patientInfo = array();
			if ($inv->customerfullname)
				$patientInfo[] = $inv->customerfullname;
			if ($inv->orderreference)
				$patientInfo[] = $inv->orderreference;
				
			
			$pdf->Cell($col6W,0,implode(' / ',$patientInfo),'TB',0,'L',false,'',1);			
			$pdf->Cell($col2W,0,$dateS,'TB',0,'L',false,'',1);
			$pdf->Cell($col3W,0,$inv->username,'TB',0,'L',false,'',1);
			$pdf->Cell($col4W,0,$total,'TB',0,'R',false,'',1);
			
			$pdf->Ln();	
		}
		
		if ($oldtype!=-1)
		{			
			$pdf->Cell($col1W+$col2W+$col3W+$col5W+$col6W,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();
	
	
	}

    function printSummaryXLS(&$workbook,$filename) {
        require_once 'Spreadsheet/Excel/Writer.php';
        //$workbook = new Spreadsheet_Excel_Writer($filename);
        $workbook = new Spreadsheet_Excel_Writer();
        $workbook->setVersion(8);

// Creating a worksheet
        $worksheet =& $workbook->addWorksheet($filename);
        $worksheet->setInputEncoding('utf-8');

// The actual data

        $invoices =& $this->getData();


        $Arial8Bold =& $workbook->addFormat(array('Size' => 8,'VAlign' => 'top', 'Align' => 'left','Color' => 'black','Pattern' => 1,'FgColor' => 'white'));
        $Arial8Bold->setFontFamily('Arial');
        $Arial8Bold->setBold(700);
        $Arial8Bold->setTextWrap();

        $Arial8 =& $workbook->addFormat(array('Size' => 8,'VAlign' => 'top', 'Align' => 'left','Color' => 'black','Pattern' => 1,'FgColor' => 'white'));
        $Arial8->setFontFamily('Arial');
        $Arial8->setTextWrap();

        $Arial12 =& $workbook->addFormat(array('Size' => 12,'VAlign' => 'top', 'Align' => 'left','Color' => 'black','Pattern' => 1,'FgColor' => 'white'));
        $Arial12->setFontFamily('Arial');
        $Arial12->setTextWrap();

        $Arial12Bold =& $workbook->addFormat(array('Size' => 12,'VAlign' => 'top', 'Align' => 'left','Color' => 'black','Pattern' => 1,'FgColor' => 'white'));
        $Arial12Bold->setFontFamily('Arial');
        $Arial12Bold->setBold(700);
        $Arial12Bold->setTextWrap();


        $worksheet->setColumn(0,0,30);
        $worksheet->setColumn(1,1,30);
        $worksheet->setColumn(2,2,35);
        $worksheet->setColumn(3,3,30);
        $worksheet->setColumn(4,4,20);
        $worksheet->setColumn(5,5,35);



        $worksheet->writeString(0,0,$this->searchuserid,$Arial8);
        $worksheet->mergeCells(0,0,0,1);


        $worksheet->writeString(1,0,'Pablo Iglesias, 129 1º',$Arial12);
        $worksheet->mergeCells(1,0,1,1);

        $worksheet->writeString(2,0,'03600 ELDA',$Arial12);
        $worksheet->mergeCells(2,0,2,1);

        $worksheet->writeString(3,0,'SPAIN',$Arial12Bold);
        $worksheet->mergeCells(3,0,3,1);


        $worksheet->writeString(3,4,'C.E.E. n: ES-B53905667',$Arial12);
        $worksheet->mergeCells(3,4,3,5);



        $worksheet->writeString(5,0,'REF.',$Arial12Bold);
        $worksheet->writeString(5,1,'PEDIDO',$Arial12Bold);
        $worksheet->writeString(5,2,'PACIENTE',$Arial12Bold);
        $worksheet->writeString(5,3,'FECHA',$Arial12Bold);
        $worksheet->writeString(5,4,'USUARIO',$Arial12Bold);
        $worksheet->writeString(5,5,'TOTAL',$Arial12Bold);




        $oldtype = -1;
        $currentRow = 6;
        $amount =0;
        foreach( $invoices as $inv)  {
            if ($inv->paid ==1)
                $type = 0;
            else if ($inv->returned)
                $type = 1;
            else
                $type = 2;

            if ($oldtype!=$type)
            {
                switch($type)
                {
                    case 0: $titulo = "Facturas pagadas"; break;
                    case 1: $titulo = "Facturas devueltas"; break;
                    case 2: $titulo = "Facturas impagadas"; break;
                }

                if ($oldtype!=-1)  {

                    $worksheet->writeString($currentRow,4,'TOTAL:',$Arial8Bold);
                    $worksheet->writeNumber($currentRow,5,$amount,$Arial8);
                    $currentRow+=2;
                }


                $worksheet->writeString($currentRow,0,$titulo,$Arial12Bold);
                $worksheet->mergeCells($currentRow,0,$currentRow,5);
                $currentRow++;

                $amount = 0;

            }
            $oldtype = $type;

            if ($inv->generation_date)
            {
                $date = new JDate($inv->generation_date);
                $date->setOffset(date('Z')/3600.0);
                $dateS = $date->toFormat('%d/%m/%Y');
            }
            else
                $dateS = '';

            $amount += $inv->total;

            $patientInfo = array();
            if ($inv->customerfullname)
                $patientInfo[] = $inv->customerfullname;
            if ($inv->orderreference)
                $patientInfo[] = $inv->orderreference;


            $worksheet->writeString($currentRow,0,$inv->reference,$Arial8);
            $worksheet->writeString($currentRow,1,$inv->orderid,$Arial8);
            $worksheet->writeString($currentRow,2,implode(' / ',$patientInfo),$Arial8);
            $worksheet->writeString($currentRow,3,$dateS,$Arial8);
            $worksheet->writeString($currentRow,4,$inv->username,$Arial8);
            $worksheet->writeNumber($currentRow,5,$inv->total,$Arial8);


            $currentRow++;
        }

        if ($oldtype!=-1) {
            $worksheet->writeString($currentRow,4,'TOTAL:',$Arial8Bold);
            $worksheet->writeNumber($currentRow,5,$amount,$Arial8);
        }


    }

	
}// class