<?php
//--No direct access
defined('_JEXEC') or die('=;)');

jimport('joomla.application.component.model');
jimport('tcpdf.tcpdf');


class InvoicesSummaryPDF extends TCPDF {
	//Page header
	var $month ="";
	var $year ="";
	var $sortsummary = false;
	var $summarysorting =0; 
	
	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 vxcModelInvoicing 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;
	
	var $_isFranchiseManager = false;
	
	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' );
		
		
		$this->summarysorting = $mainframe->getUserStateFromRequest( "$option.$view.summarysorting", 'summarysorting', 0, '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[] = " i.batch = ".$this->searchbatch;
		
		if ($this->searchpaymethod)
			$where[] = " pm.nameid = " .$this->_db->Quote($this->searchpaymethod);
		
		if ($this->searchorder)
		{
			$where[] = " i.batch = ".
				" (SELECT o1.batch
					FROM #__vxc_order o1 
					WHERE o1.id = ".$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(' ',atfirst.value,atmiddle.value,atlast.value)) LIKE ".$this->_db->Quote('%'.$this->searchcustomername.'%').')';

            $cond .=  ')';

            $newJoins = '';

            if (LegacyHelper::getNewMode())
                $newJoins = "
                    LEFT JOIN jos_vxc_attribute atfirst ON atfirst.orderid = o1.id AND atfirst.name = 'patient.firstName'
                    LEFT JOIN jos_vxc_attribute atmiddle ON atmiddle.orderid = o1.id AND atmiddle.name = 'patient.middleName'
                    LEFT JOIN jos_vxc_attribute atlast ON atlast.orderid = o1.id AND atlast.name = 'patient.lastName'
                    ";

			$where[] = " i.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 = i.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 =( count($where) ) ? ' WHERE ' . implode( ' AND ', $where ) : '';
		$having =( count($having) ) ? ' HAVING ' . implode( ' AND ', $having ) : '';
		$groupby =( count($groupby) ) ? ' GROUP BY ' . implode( ' , ', $groupby ) : '';
		
		
		if ($this->sortsummary)
		{
			if ($this->summarysorting==0)
			$orderby = " ORDER BY i.paid DESC , 
								i.returned ASC , 
								b.payment_date ASC ";
			else
			$orderby = " ORDER BY i.paid DESC , 
								i.returned ASC , 
								u.name 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;
			}		
		}
			
		
		$this->_query = " SELECT i.id,b.payment_user user,
								i.reference reference,
								i.batch batch,
								i.generation_date generation_date,
								i.total total,
								(SELECT SUM(il.amount*il.price) FROM #__vxc_invoiceline il WHERE il.invoice = i.id AND il.vat = 0) baseal0,
								(SELECT SUM(il.amount*il.price) FROM #__vxc_invoiceline il WHERE il.invoice = i.id AND il.vat = 8) baseal8,
								(SELECT SUM(il.amount*il.price) FROM #__vxc_invoiceline il WHERE il.invoice = i.id AND il.vat = 18) baseal18,
								(SELECT SUM(il.amount*il.price) FROM #__vxc_invoiceline il WHERE il.invoice = i.id AND il.vat = 10) baseal10,
								(SELECT SUM(il.amount*il.price) FROM #__vxc_invoiceline il WHERE il.invoice = i.id AND il.vat = 21) baseal21,
								i.paid paid,
								i.shipping_cost shipping_cost,
								i.equiv_charges equiv_charges,
								i.returned returned,
								pm.name payment_methodname,								
								u.name username,
								u.cif usercif,
								u.address useraddress,
								u.city usercity,
								u.province userprovince,
								u.pcode userpcode,
								u.email useremail

				FROM #__vxc_invoice i
				LEFT JOIN #__vxc_batch b ON b.id = i.batch
				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
					"
				. $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['summarysorting'] = $this->summarysorting;
		
		$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;
		
		
		
		if ($this->_isFranchiseManager) 
		{
			$sql = "SELECT g.id id, g.name name
					FROM #__vxc_group g				
					WHERE g.franchise = $franchiseId 				
					ORDER BY g.name";		
		}
		else
		{		
			$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 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 = 60;
		$col4W = 30;
		
		$pdf->SetLineWidth(0.3);
		$pdf->SetFont('helvetica','b',12);
		$headerHeight = 13;		
		$pdf->writeHTMLCell($col1W,$headerHeight,'','','REF.','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;
        $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)
				{			
					$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();
				}
				
				$pdf->SetFont('helvetica','b',12);
				$pdf->Cell($col1W+$col2W+$col3W+$col4W,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
				$dataS = '';
						
			$amount += $inv->total;
			$total = sprintf("%0.02f",$inv->total). " €";			
			$pdf->Cell($col1W,0,$inv->reference,'TB',0,'L');
			$pdf->Cell($col2W,0,$dateS,'TB',0,'L');
			$pdf->Cell($col3W,0,$inv->username,'TB',0,'L');
			$pdf->Cell($col4W,0,$total,'TB',0,'R');
			
			$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();
	
	}

    function printSummaryXLS(&$workbook,$filename) {

        require_once 'Spreadsheet/Excel/Writer.php';
        $franchiseName = SiteOptionsHelper::getFranchiseName();

        //$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->setColumn(6,6,35);
        $worksheet->setColumn(7,7,35);
        $worksheet->setColumn(8,8,35);
        $worksheet->setColumn(9,9,35);
        $worksheet->setColumn(10,10,35);
        $worksheet->setColumn(11,11,35);
        $worksheet->setColumn(12,12,35);



        if ($franchiseName=='tecnoinsole') {

            $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,'FECHA',$Arial12Bold);
        $worksheet->writeString(5,2,'USUARIO',$Arial12Bold);

        if ($franchiseName=='tecnoinsole') {
            $worksheet->writeString(5,3,'BI0%',$Arial12Bold);
            $worksheet->writeString(5,4,'BI8%',$Arial12Bold);
            $worksheet->writeString(5,5,'IVA8%',$Arial12Bold);
            $worksheet->writeString(5,6,'BI10%',$Arial12Bold);
            $worksheet->writeString(5,7,'IVA10%',$Arial12Bold);
            $worksheet->writeString(5,8,'BI18%',$Arial12Bold);
            $worksheet->writeString(5,9,'IVA18%',$Arial12Bold);
            $worksheet->writeString(5,10,'BI21%',$Arial12Bold);
            $worksheet->writeString(5,11,'IVA21%',$Arial12Bold);
            $worksheet->writeString(5,12,'TOTAL',$Arial12Bold);
        }
        else
            $worksheet->writeString(5,3,'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)  {

                    if ($franchiseName=='tecnoinsole') {
                        $worksheet->writeString($currentRow,11,'TOTAL:',$Arial8Bold);
                        $worksheet->writeNumber($currentRow,12,$amount,$Arial8);
                    }
                    else{
                        $worksheet->writeString($currentRow,2,'TOTAL:',$Arial8Bold);
                        $worksheet->writeNumber($currentRow,3,$amount,$Arial8);
                    }
                    $currentRow+=2;
                }


                $worksheet->writeString($currentRow,0,$titulo,$Arial12Bold);
                $worksheet->mergeCells($currentRow,0,$currentRow,12);
                $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;



            $worksheet->writeString($currentRow,0,$inv->reference,$Arial8);
            $worksheet->writeString($currentRow,1,$dateS,$Arial8);
            $worksheet->writeString($currentRow,2,$inv->username,$Arial8);

            if ($franchiseName=='tecnoinsole') {
                $worksheet->writeNumber($currentRow,3,round($inv->baseal0+$inv->shipping_cost+$inv->equiv_charges,2),$Arial8);

                $worksheet->writeNumber($currentRow,4,round($inv->baseal8,2),$Arial8);
                $worksheet->writeNumber($currentRow,5,round($inv->baseal8*0.08,2),$Arial8);

                $worksheet->writeNumber($currentRow,6,round($inv->baseal10,2),$Arial8);
                $worksheet->writeNumber($currentRow,7,round($inv->baseal10*0.1,2),$Arial8);

                $worksheet->writeNumber($currentRow,8,round($inv->baseal18,2),$Arial8);
                $worksheet->writeNumber($currentRow,9,round($inv->baseal18*0.18,2),$Arial8);


                $worksheet->writeNumber($currentRow,10,round($inv->baseal21,2),$Arial8);
                $worksheet->writeNumber($currentRow,11,round($inv->baseal21*0.21,2),$Arial8);
                $worksheet->writeNumber($currentRow,12,round($inv->total,2),$Arial8);
            }
            else
                $worksheet->writeNumber($currentRow,3,round($inv->total,2),$Arial8);




            $currentRow++;
        }

        if ($oldtype!=-1) {
            if ($franchiseName=='tecnoinsole') {
                $worksheet->writeString($currentRow,11,'TOTAL:',$Arial8Bold);
                $worksheet->writeNumber($currentRow,12,$amount,$Arial8);
            }
            else{
                $worksheet->writeString($currentRow,2,'TOTAL:',$Arial8Bold);
                $worksheet->writeNumber($currentRow,3,$amount,$Arial8);
            }
        }


    }

    /**
     * Funcion que genera los apuntes diarios y subcuentas de un conjunto de facturas
     * @param $contents Parametro de salida con los contenidos del fichero
     */
    function printSummaryContaplus(&$diarioContents, &$subcuentasContents) {

        $diarioContents ='';
        $subcuentasContents='';
        // Obtenemos el conjunto de facturas a generar
        $invoices =& $this->getData();

        // Creamos variables para almacenar las subcuentas y los apuntes
        $subcuentas = array();
        $apuntes = array();

        // Comenzamos el numero de asientos siempre por el 1
        $numAsiento = 1;

        // Recorremos cada factura
        foreach( $invoices as $inv)  {
            if ($inv->paid !=1) // Generamos apunte solo si la factura ha sido pagada
                continue;

            // Utilizamos la fecha de generacion de la factura
            if ($inv->generation_date)
            {
                $date = new JDate($inv->generation_date);
                $date->setOffset(date('Z')/3600.0);
                $dateS = $date->toFormat('%Y%m%d');
            }
            else
                $dateS = '';


            // Informacion del cliente
            $userName = $inv->username;
            $userCif = $inv->usercif;
            $userAddress = $inv->useraddress;
            $userCity= $inv->usercity;
            $userProvince= $inv->userprovince;
            $userPCode= $inv->userpcode;
            $userEmail= $inv->useremail;

            // A veces nos ponen la provincia en el campo de ciudad, intentamos extraer la provincia de ahi
            $startProvinceInCity = strpos($userCity,'(');
            if (!$userProvince && $startProvinceInCity!==FALSE) {

                $userProvince = substr($userCity,$startProvinceInCity+1, strpos($userCity,')')-$startProvinceInCity-1);
                $userCity = substr($userCity,0, $startProvinceInCity-1);

            }

            // Redondeamos las cantidades a 2 decimales
            $base0 = round($inv->baseal0,2);
            $equiv_charges = round($inv->equiv_charges,2);
            $shipping_cost = round($inv->shipping_cost,2);

            $base10 = round($inv->baseal10,2);
            $iva10 = round($inv->baseal10*0.1,2);

            $base21 = round($inv->baseal21,2);
            $iva21 = round($inv->baseal21*0.21,2);
            $total = round($inv->total,2);
            if (!$total) continue; // La factura ha de tener un importe distinto a 0

            // Calculamos el codigo de cliente
            $codCliente = sprintf("4300%'04d",$inv->user);
            $concepto = strtoupper('N/FRA '.$userName);
            $refFactura = $inv->reference;


            // Generamos el apunte del debe con el total de la factura
            $apuntes[] =  array('asiento'=>$numAsiento,'fecha'=>$dateS,'subcta'=>$codCliente,'concepto'=>$concepto,'refFactura'=>$refFactura,'euroDebe'=>$total);


            // Generamos el apunte relacionado con la base imponible de la venta
            $haberVentas = $base0 + $base10 + $base21 + $shipping_cost;
            $apuntes[] =  array('asiento'=>$numAsiento,'fecha'=>$dateS,'subcta'=>'70000000','concepto'=>$concepto,'refFactura'=>$refFactura,'euroHaber'=>$haberVentas);

            // Apuntes con el IVA reducido
            if ($base10) {
                if ($equiv_charges) {
                    if (!$subcuentas['47700011'])
                        $subcuentas['47700011'] = array('titulo'=>'IVA REPERCUTIDO NAC10%R.EQUIV1.4%','gValue'=>'G','relAccount'=>'47500011','subCtaAux2'=>10,'subCtaAux3'=>1.4);

                    $apuntes[] =  array('asiento'=>$numAsiento,'fecha'=>$dateS,'contra'=>$codCliente,'subcta'=>'47700011','concepto'=>$concepto,'refFactura'=>$refFactura,'euroHaber'=>$iva10,'baseEuro'=>$base10,'IVA'=>10 ,'RE'=>1.4);

                    if (!$subcuentas['47500011'])
                        $subcuentas['47500011'] = array('titulo'=>'IVA REPERCUTIDO10%REC.EQUIV1.4%'  );

                    $apuntes[] =  array('asiento'=>$numAsiento,'fecha'=>$dateS,'subcta'=>'47500011','concepto'=>$concepto,'refFactura'=>$refFactura,'euroHaber'=>$equiv_charges);
                }
                else {

                    if (!$subcuentas['47700010'])
                        $subcuentas['47700010'] = array('titulo'=>'IVA REPERCUTIDO 10%NACIONA','gValue'=>'G','subCtaAux2'=>10);


                    $apuntes[] =  array('asiento'=>$numAsiento,'fecha'=>$dateS,'contra'=>$codCliente,'subcta'=>'47700010','concepto'=>$concepto,'refFactura'=>$refFactura,'euroHaber'=>$iva10,'baseEuro'=>$base10,'IVA'=>10 );
                }
            }

            // Apuntes de IVA al 0%
            $baseEuro0 =$base0+$shipping_cost;

            if ($baseEuro0) {
                if (!$subcuentas['47700001'])
                    $subcuentas['47700001'] = array('titulo'=>'IVA NACIONAL 0%' ,'gValue'=>'G' );

                $apuntes[] =  array('asiento'=>$numAsiento,'fecha'=>$dateS,'subcta'=>'47700001','contra'=>$codCliente, 'concepto'=>$concepto,'refFactura'=>$refFactura,'baseEuro'=>$baseEuro0);
            }

            // Apuntes del IVA al 0%
            if ($base21) {
                if (!$subcuentas['47700021'])
                    $subcuentas['47700021'] = array('titulo'=>'IVA REPERCUTIDO 21% NACIONAL' ,'gValue'=>'G','subCtaAux2'=>21);

                $apuntes[] =  array('asiento'=>$numAsiento,'fecha'=>$dateS,'contra'=>$codCliente,'subcta'=>'47700021','concepto'=>$concepto,'refFactura'=>$refFactura,'euroHaber'=>$iva21,'baseEuro'=>$base21,'IVA'=>21 );
            }

            // Creo la subcuenta de ventas si procede
            if (!$subcuentas['70000000']) {
                $subcuentas['70000000'] = array('titulo'=>'Ventas', 'subCtaAux1'=>'T','subCtaAux5'=>'Ventas');
            }
            // Creo la subcuenta del cliente si procede

            if (!$subcuentas[$codCliente]) {
                $subcuentas[$codCliente] = array('titulo'=>strtoupper($userName), 'nif'=>strtoupper(str_replace('-','',$userCif)), 'domicilio'=>strtoupper($userAddress),
                    'poblacion'=>strtoupper($userCity), 'provincia'=>strtoupper($userProvince),'codpost' => $userPCode, 'subCtaAux4'=>$userEmail,'subCtaAux6'=>1);
            }

            $numAsiento++; // Generamos un asiento por factura
        }



        foreach($apuntes as $apunte) {

            $numAsiento = ''.$apunte['asiento'];
            $fecha = utf8_decode($apunte['fecha']);
            $subcta = utf8_decode($apunte['subcta']);
            $contra = utf8_decode($apunte['contra']);
            $concepto = utf8_decode($apunte['concepto']);


            $IVA = utf8_decode($apunte['IVA']);
            if (!$IVA) $IVA=0;
            $RE = utf8_decode($apunte['RE']);
            if (!$RE) $RE=0;

            $refFactura = utf8_decode($apunte['refFactura']);

            $euroDebe = utf8_decode($apunte['euroDebe']);
            if (!$euroDebe) $euroDebe=0;
            $euroHaber = utf8_decode($apunte['euroHaber']);
            if (!$euroHaber) $euroHaber =0;
            $baseEuro = utf8_decode($apunte['baseEuro']);
            if (!$baseEuro) $baseEuro = 0;



            $linea = sprintf("%6.6s%8.8s%-12.12s%-12.12s%16s%-25.25s%40s%5.02f%5.02f%-25.25s%84s%16.02f%16.02f%16.02fF",$numAsiento,$fecha,$subcta,$contra,'0.00',
                $concepto,'            0.00       0            0.00',$IVA,$RE,$refFactura,
            '00     0        0.000000            0.00            0.00                       0.002',$euroDebe,$euroHaber,$baseEuro);
            $diarioContents .= $linea . "\r\n";
        }



        foreach($subcuentas as $codigo => $subcuenta) {

            $titulo = utf8_decode($subcuenta['titulo']);
            $nif = utf8_decode($subcuenta['nif']);
            $domicilio = utf8_decode($subcuenta['domicilio']);
            $poblacion = utf8_decode($subcuenta['poblacion']);
            $provincia = utf8_decode($subcuenta['provincia']);
            $codpost = utf8_decode($subcuenta['codpost']);
            $divisa = utf8_decode($subcuenta['divisa']);
            if (!$divisa) $divisa = 'F';
            $codDivisa = utf8_decode($subcuenta['codDivisa']);
            $document = utf8_decode($subcuenta['document']);
            if (!$document) $document = 'F';
            $ajustame = utf8_decode($subcuenta['ajustame']);
            if (!$ajustame) $ajustame = 'F';

            $gValue = utf8_decode($subcuenta['gValue']);
            $relAccount = utf8_decode($subcuenta['relAccount']);
            $subCtaAux1 = utf8_decode($subcuenta['subCtaAux1']);
            if (!$subCtaAux1) $subCtaAux1 = 'F';
            $subCtaAux2 = utf8_decode($subcuenta['subCtaAux2']);
            $subCtaAux3 = utf8_decode($subcuenta['subCtaAux3']);
            $subCtaAux4 = utf8_decode($subcuenta['subCtaAux4']);
            $subCtaAux5 = utf8_decode($subcuenta['subCtaAux5']);
            $subCtaAux6 = utf8_decode($subcuenta['subCtaAux6']);
            if (!$subCtaAux6) $subCtaAux6 = 0;

            $linea = sprintf("%-12.12s%-40.40s%-15.15s%-35.35s%-25.25s%-20.20s%-5.5s%1.1s%-5.5s%1.1s%1.1s%-10.10s%-24.24s%-13.13s%5.02f%5.02f%15.15s%-50.50s%-100.100s%1.1d%51.51s%s",
                $codigo,$titulo,$nif,$domicilio,$poblacion,$provincia,
                $codpost,$divisa,$codDivisa,$document,$ajustame,$gValue,$relAccount,
                $subCtaAux1,$subCtaAux2,$subCtaAux3,'',$subCtaAux4,$subCtaAux5,$subCtaAux6,'','FFFFF            0.00 0FF');
            $subcuentasContents .= $linea . "\r\n";
        }


    }


}// class