<?php
//--No direct access
defined('_JEXEC') or die('=;)');



jimport('joomla.application.component.model');

include_once(JPATH_BASE."/components/com_vxc/helpers/siteoptionshelper.php");
include_once(JPATH_BASE."/components/com_vxc/helpers/legacy.php");


class vxcModelManufacturingOrders extends JModel
{
	var $_data;
	var $_total = null;
	var $_pagination = null;
	var $_maxShownOrders = 100;

	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' );

		if ($this->filter_order=='payment_date' && !$this->filter_order_Dir)
			$this->filter_order_Dir = 'asc';

		$this->searchreference	= $mainframe->getUserStateFromRequest( "$option.$view.searchreference", 'searchreference', '', 'string' );
		$this->searchreference	= JString::strtolower( $this->searchreference );

		$this->generatingManuReport = false;
        $this->searchstates = '';
		$this->searchcustomer	= $mainframe->getUserStateFromRequest( "$option.$view.searchcustomer", 'searchcustomer', '', 'string' );
		$this->searchcustomer	= JString::strtolower( $this->searchcustomer );

		$this->searchuser	= $mainframe->getUserStateFromRequest( "$option.$view.searchuser", 'searchuser', '', 'string' );
		$this->searchuser	= JString::strtolower( $this->searchuser );

		$this->searchstate	= $mainframe->getUserStateFromRequest( "$option.$view.searchstate", 'searchstate', 'CHECKED', 'string' );
		$this->searchmanustate	= $mainframe->getUserStateFromRequest( "$option.$view.searchmanustate", 'searchmanustate', '', 'string' );
		$this->searchid = $mainframe->getUserStateFromRequest( "$option.$view.searchid", 'searchid', '', 'string' );


		$this->searchtomanudate	= $mainframe->getUserStateFromRequest( "$option.$view.searchtomanudate", 'searchtomanudate', '', 'string' );
		$this->searchtomanudateend	= $mainframe->getUserStateFromRequest( "$option.$view.searchtomanudateend", 'searchtomanudateend', '', 'string' );

		$this->searchtoshippingdate	= $mainframe->getUserStateFromRequest( "$option.$view.searchtoshippingdate", 'searchtoshippingdate', '', 'string' );
		$this->searchtoshippingdateend	= $mainframe->getUserStateFromRequest( "$option.$view.searchtoshippingdateend", 'searchtoshippingdateend', '', 'string' );



		$this->searchmat	= $mainframe->getUserStateFromRequest( "$option.$view.searchmat", 'searchmat', '', 'string' );

		$this->searchfranchise = $mainframe->getUserStateFromRequest( "$option.$view.searchfranchise", 'searchfranchise', '', 'string' );
		if (!$this->searchfranchise)
		{
            $userId =LegacyHelper::getUserId();
			$this->_db->setQuery("SELECT uf.franchise id
								FROM #__vxc_userfranchise uf
								WHERE uf.userid = ".$userId);

			$this->searchfranchise = $this->_db->loadResult();
		}

        $this->searchids = null;


		$customerId = (int) JRequest::getVar('customerId');

		if ($customerId)
			$this->searchcustomerid = $customerId;

		$limit = $mainframe->getUserStateFromRequest( 'global.list.limit', 'limit', 10, 'int' );

		if (!$limit) {
			$this->setState('limit', $limit);
			$limit = 10;
		}

		if ($limit==-1) {
			$limit= $this->_maxShownOrders;
			$this->setState('limit', $limit);
		}
		$limitstart = JRequest::getVar('limitstart',0);
		$limitstart = ($limit != 0 ? (floor($limitstart / $limit) * $limit) : 0);
		$this->setState('limit', $limit);
		$this->setState('limitstart', $limitstart);
	}

	function getFranchises()
	{
		$sql = "SELECT f.id id , f.name name
				FROM #__vxc_franchise f
				ORDER BY f.name";
		$this->_db->setQuery($sql);
		return $this->_db->loadObjectList();
	}



	function _buildQuery()
	{
		$where = array();

		if ($this->searchid)
			$where[] = ' o.id = '. $this->searchid;

		if ($this->searchcustomerid)
			$where[] = ' o.customer = '.$this->searchcustomerid;

		if ($this->searchreference)
			$where[] = ' LOWER(o.reference) LIKE '. $this->_db->Quote('%'.$this->searchreference.'%') ;

		if ($this->searchuser)
			$where[] = " o.user = ".$this->searchuser;

        if ($this->searchcustomer) {
            $cond = " ((LOWER(CONCAT_WS(' ',c.name,c.middlename,c.surname)) LIKE ".$this->_db->Quote('%'.$this->searchcustomer.'%').')';

            if (LegacyHelper::updatedDB())
                $cond .=" OR (LOWER(CONCAT_WS(' ',atfirst.value,atmiddle.value,atlast.value)) LIKE ".$this->_db->Quote('%'.$this->searchcustomer.'%').')';

            $cond .=  ')';
            $where[] = $cond;
        }

		if ($this->searchstates && is_array($this->searchstates) ) {
			$clause ='';

			foreach($this->searchstates as $ss)	{
				if ($clause) $clause .= ' OR ';
				$clause .= '(os.nameid = '. $this->_db->Quote($ss). ')';
			}

			if ($clause)
				$where[] = '('.$clause.')';
		}else if ($this->searchstate && $this->searchstate!='ALL')
			$where[] = " os.nameid = ". $this->_db->Quote($this->searchstate);
		else if  ($this->searchstate!='ALL')
			$where[] = " os.nameid = 'CHECKED' ";


		if ($this->searchfranchise)
			$where[] = " uf.franchise = ". $this->searchfranchise;

		if ($this->generatingManuReport)
			$where[] = " o.manufacturing_date IS NOT NULL ";

		if ($this->searchtomanudate)
		{
			$dateS =str_replace('-','/',trim($this->searchtomanudate));
			$dateArray = explode('/',$dateS);
			$dateCount = count($dateArray);


			if ($dateCount==3)
			{
				$day = (int) $dateArray[0];
				$month = (int) $dateArray[1];
				$year = (int) $dateArray[2];

				$where[] = " o.manufacturing_date >= ".$this->_db->Quote($year . "-". $month."-". $day);
			}
		}

		if ($this->searchtomanudateend)
		{
			$dateS = str_replace('-','/',trim($this->searchtomanudateend));
			$dateArray = explode('/',$dateS);
			$dateCount = count($dateArray);

			if ($dateCount==3)
			{
				$day = (int) $dateArray[0];
				$month = (int) $dateArray[1];
				$year = (int) $dateArray[2];

				$where[] = " o.manufacturing_date <= ".$this->_db->Quote($year . "-". $month."-". $day);
			}
		}

		if ($this->searchtoshippingdate)
		{
			$dateS =str_replace('-','/',trim($this->searchtoshippingdate));
			$dateArray = explode('/',$dateS);
			$dateCount = count($dateArray);


			if ($dateCount==3)
			{
				$day = (int) $dateArray[0];
				$month = (int) $dateArray[1];
				$year = (int) $dateArray[2];

				$where[] = " b.est_shipping_date >= ".$this->_db->Quote($year . "-". $month."-". $day);
			}
		}

		if ($this->searchtoshippingdateend)
		{
			$dateS = str_replace('-','/',trim($this->searchtoshippingdateend));
			$dateArray = explode('/',$dateS);
			$dateCount = count($dateArray);

			if ($dateCount==3)
			{
				$day = (int) $dateArray[0];
				$month = (int) $dateArray[1];
				$year = (int) $dateArray[2];

				$where[] = " b.est_shipping_date <= ".$this->_db->Quote($year . "-". $month."-". $day);
			}
		}



		if ($this->searchmanustate)
		{
			if ($this->searchmanustate=='NULL')
				$where[] = " ms.nameid IS NULL ";
			else
				$where[] = " ms.nameid = ".$this->_db->Quote($this->searchmanustate);
		}


		if ($this->searchmat) {

			$mats = array();

			switch($this->searchmat) {
				case 'TERMOCONFORMADO+POSITIVO':
					$mats[] = 'POSITIVO';
				case 'TERMOCONFORMADO':
					$mats[] = 'TERMOPOLIPRO2';
					$mats[] = 'TERMOPOLIPRO3';
					$mats[] = 'TERMOPOLIPRO4';
					$mats[] = 'TERMOPOLIPRO5';
					$mats[] = 'TERMOCARBONO15';
					$mats[] = 'TERMORESINAFLUX';
					$mats[] = 'TERMORESINAFLEX';
					$mats[] = 'TERMORESINAFLUXFLEX';
					$mats[] = 'TERMORESINAHERFLEX';
					$mats[] = 'TERMOPOLIET3';
					$mats[] = 'TERMOPOLIET4';
				break;
				case 'EVA':
					$mats[] = '3DCONFORTMONO30';
					$mats[] = '3DCONFORTMONO40';
					$mats[] = '3DCONFORTBI3055';
                    $mats[] ='3DCONFORTBI4055SPORT';
					$mats[] = 'EVAMONO30';
					$mats[] = 'EVAMONO40';
					$mats[] = 'EVAMONO55';
					$mats[] = 'EVABI3055';
				break;
				case 'MECANIZADO':
					$mats[] = 'MECAPOLIPRO';
					$mats[] = 'MECAABS';
					$mats[] = 'MECAPOLIEHD';
					$mats[] = 'MECAPOLIPRO3SIDED';
				break;
                default:
					$mats[] = $this->searchmat;
			}

            $productOptions = array();
            $evaType = array();

            switch($this->searchmat) {

                case 'POSITIVO':
                case 'TERMOCONFORMADO+POSITIVO':
                    $productOptions[] = 'positivo';
                    break;
                case 'EVA':
                    $productOptions[] = 'eva3dcomfort';
                    $productOptions[] = 'evastandard';
                    $productOptions[] = 'palmilla';
                    $productOptions[] = 'evaweb';
                    $productOptions[] = 'flat';
                    break;
                case 'MECANIZADO':
                case 'MECAPOLIPRO':
                    $productOptions[] = 'mecapoli';
                    break;
                case '3DCONFORTMONO30':
                    $productOptions[] = 'eva3dcomfort';
                    $evaType[] = "mono30";
                    break;
                case 'EVAMONO30':
                    $productOptions[] = 'evastandard';
                    $productOptions[] = 'evaweb';
                    $productOptions[] = 'palmilla';
                    $evaType[] = "mono30";
                    break;

                case 'EVAMONO40':
                    $productOptions[] = 'evastandard';
                    $productOptions[] = 'evaweb';
                    $productOptions[] = 'palmilla';
                    $evaType[] = "mono40";
                    break;

                case  '3DCONFORTMONO40':
                    $productOptions[] = 'eva3dcomfort';
                    $evaType[] = "mono40";
                    break;

                case '3DCONFORTBI3055':
                    $productOptions[] = 'eva3dcomfort';
                    $evaType[] = "bidensity";
                    break;
                case '3DCONFORTBI4055SPORT':
                    $productOptions[] ='eva3dcomfort';
                    $evaType[] = 'bidensity4055sport';
                    break;

                case 'EVABI3055':
                    $productOptions[] = 'evastandard';
                    $productOptions[] = 'palmilla';
                    $productOptions[] = 'evaweb';
                    $evaType[] = "bidensity";
                    break;

            }


            $cond = array();

			$sqlMats = '';
			foreach($mats as $mat) {
				if ($sqlMats)
					$sqlMats .=" OR ";
				$sqlMats .=" vl.name = ".$this->_db->Quote($mat) . " OR " ." vr.name = ".$this->_db->Quote($mat)  ;
			}


			if ($sqlMats) {
				$sqlMats = " EXISTS( SELECT *
									FROM #__vxc_attribute at
									LEFT JOIN #__vxc_attributetype ty ON ty.id = at.type AND ty.name = 'InsoleOptions'
									LEFT JOIN #__vxc_attributetypevalue vl ON vl.id = at.value_left
									LEFT JOIN #__vxc_attributetypevalue vr ON vr.id = at.value_right
									WHERE at.orderid = o.id AND ($sqlMats) ) ";

                $cond[] = "(".$sqlMats.")";
			}


            $sqlMats2 = '';
            $sqlMats3 = '';

            if (count($productOptions)>0)
                foreach($productOptions as $mat) {
                    if ($sqlMats2)
                        $sqlMats2 .=" OR ";
                    $sqlMats2 .=" at.value = ".$this->_db->Quote($mat) ;
                }

            if (count($evaType)>0)
                foreach($evaType as $mat) {
                    if ($sqlMats3)
                        $sqlMats3 .=" OR ";
                    $sqlMats3 .=" at.value = ".$this->_db->Quote($mat) ;
                }



            if ($sqlMats2 || $sqlMats3)  {

                $auxArray = array();

                if ($sqlMats2) {
                    $sqlMats2 = " EXISTS( SELECT *
								FROM #__vxc_attribute at
								WHERE at.orderid = o.id AND at.name=".$this->_db->Quote("general.product"). " AND  ($sqlMats2) ) ";

                    $auxArray[] = "(".$sqlMats2.")";
                }

                if ($sqlMats3) {
                    $sqlMats3 = " EXISTS( SELECT *
								FROM #__vxc_attribute at
								WHERE at.orderid = o.id AND at.name=".$this->_db->Quote("insoleOptions.evaType"). " AND  ($sqlMats3) ) ";

                    $auxArray[] = "(".$sqlMats3.")";
                }


                if (count($auxArray)>0)
                    $cond[] ="(". implode(' AND ',$auxArray) . ")";
            }

            if (count($cond)>0)
                $where[] =  "(". implode(" OR ",$cond). ")";
		}


		if (is_array($this->searchids)) {
            if ($this->searchids)
                $where[] = " o.id IN (". implode(',',$this->searchids). ") ";
            else
                $where[] = " 0=1 ";
        }


		$where[] = ' o.deleted IS NOT TRUE ';
		$where[] = ' o.archived IS NOT TRUE ';


		$where =( count($where) ) ? ' WHERE ' . implode( ' AND ', $where ) : '';
		$orderby = '';


		$orders = array('id','batch','username','customerfullname',
						'reference','payment_date','manufactured_date',
						'est_shipping_date','manufacturing_date');

		if (!in_array($this->filter_order,$orders))
			$this->filter_order = 'manufacturing_date';



		if (($this->filter_order) && ($this->filter_order_Dir))
		{
			$orderby 	= ' ORDER BY '. $this->filter_order .' '. $this->filter_order_Dir;
		}

        $newModeFields = '';
        $newJoins = '';
        $oldModeFields ='';
        if (LegacyHelper::updatedDB())  {
            $newModeFields = "o.uuid uuid, atlocalid.value localid, 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'
            LEFT JOIN jos_vxc_attribute atlocalid ON atlocalid.orderid = o.id AND atlocalid.name = 'patient.localid'
            ";
        }
        else {
            $oldModeFields = "CONCAT_WS(' ',NULLIF(c.name,''),NULLIF(c.middlename,''),NULLIF(c.surname,'')) customerfullname,";
        }

		$this->_query = " SELECT o.id id,o.user user,$oldModeFields
								o.batch batch,
								u.name username,$newModeFields
								u.address useraddress,
								u.city usercity,
								u.province userprovince,
								u.pcode userpcode,
								u.phone userphone,
								u.location userlocation,
								b.payment_date payment_date,
								b.payment_method payment_method,
								b.est_shipping_date est_shipping_date,
								b.taxpercent batch_taxpercent,
								b.discount batch_discount,
								b.payment_inc_cost batch_payment_inc_cost,
								b.shipping_cost batch_shipping_cost,
								b.shipping_vat batch_shipping_vat,
								b.shipping_address shipping_address,
								b.forwarded forwarded,
								(SELECT sum(o1.price)
								  from #__vxc_order o1
								  where o1.batch = b.id ) batch_price,
								(SELECT COUNT(*)
								FROM #__vxc_order o2
								WHERE o2.batch = b.id AND o2.checked_date IS NULL) nmissingcheck,
								pm.nameid  payment_methodnameid,
								o.customer customer, o.reference reference,
								o.creationdate creationdate,
								o.currentStep currentStep,
								o.completedSteps completedSteps,
								o.manufactured_date manufactured_date,
								o.manu_state manu_state,
								o.manufacturing_date manufacturing_date,
								ms.name manu_statename,
								ms.nameid menu_statenameid,
								f.name franchisename,
								uf.franchise franchise,
								a.name agencyname,
								r.id receiptid,
								r.paid receiptpaid,
								ua.company shipping_address_company,
								ua.address shipping_address_address,
								ua.city shipping_address_city,
								ua.province shipping_address_province,
								ua.pcode shipping_address_pcode,
								ua.country shipping_address_country,
								ua.phone shipping_address_phone,
								c.birthdate customerbirthdate,
								(SELECT COUNT(*) FROM #__vxc_order o2 WHERE o2.batch = o.batch) norders

								 "
				. ' FROM #__vxc_order o
					LEFT JOIN #__vxc_user u ON u.id = o.user
					LEFT JOIN #__vxc_userfranchise uf ON uf.userid = u.id
					LEFT JOIN #__vxc_franchise f ON uf.franchise = f.id
					LEFT JOIN #__vxc_orderstate os ON os.id = o.state
					LEFT JOIN #__vxc_customer c ON c.id = o.customer
					LEFT JOIN #__vxc_batch b ON b.id = o.batch
					LEFT JOIN #__vxc_useraddress ua ON ua.id = b.shipping_address
					LEFT JOIN #__vxc_paymentmethod pm ON pm.id = b.payment_method
					LEFT JOIN #__vxc_manustate ms ON ms.id = o.manu_state
					LEFT JOIN #__vxc_agency a ON a.id = b.agency
					LEFT JOIN #__vxc_receipt r ON r.orderid = o.id '.$newJoins . ' '
				. $where
				. $orderby
				;


		return $this->_query;
	}

	function getManufacturingReportData()
	{
		$this->searchstate = '';
		$this->searchstates = array('CHECKED','MANUFACTURING');
		$this->filter_order = 'o.manufacturing_date asc, batch';
		$this->filter_order_Dir = 'asc';

		$this->generatingManuReport = true;

		$this->searchreference	= null;
		$this->searchcustomer	= null;
		$this->searchuser	= null;
		$this->searchid	= null;
		$this->searchids = null;

		$this->searchmanustate	= null;
		$this->searchmat	= null;
		$this->searchcustomerid = null;

        $this->setState('limit', -1);
        $this->setState('limitstart', 0);

		return $this->getData();
	}

	function getManufacturingLabelsData($ids=array()) {
		$this->searchstate = 'ALL';
		$this->searchstates = ''; //array('CHECKED','MANUFACTURING');
		$this->filter_order = 'id';
		$this->filter_order_Dir = 'asc';

		$this->generatingManuReport = false;

		$this->searchtomanudate = null;
		$this->searchtomanudateend = null;
		$this->searchtoshippingdate = null;
		$this->searchtoshippingdateend = null;
		$this->searchreference	= null;
		$this->searchcustomer	= null;
		$this->searchuser	= null;
		$this->searchid	= null;
		$this->searchids = $ids;
		$this->searchmanustate	= null;
		$this->searchmat	= null;
		$this->searchcustomerid = null;

        $this->setState('limit', -1);
        $this->setState('limitstart', 0);

        return $this->getData();
	}



	function getData()
	{
 		if (empty($this->_data))
 		{
			$query = $this->_buildQuery();

			if ($this->searchstate == 'MANUFACTURING' || (is_array($this->searchstates) && count($this->searchstates)>0))
				$this->_db->setQuery( $query,$this->getState('limitstart'), $this->_maxShownOrders );
			else
				$this->_db->setQuery( $query,$this->getState('limitstart'), $this->getState('limit') );


			$this->_data = $this->_db->loadObjectList('id');


			$ids = array();
			if (count($this->_data)>0)
			{
				foreach($this->_data as $order)
				{
					$order->readOnly = true;
					$ids[] = $order->id;
				}
			}

			if (count($ids))
			{
				$attributes =& $this->getAttributes($ids);


				foreach($attributes as $attribute)
				{

					if (!$this->_data[$attribute->orderid]->attributes)
						$this->_data[$attribute->orderid]->attributes=array();
					$this->_data[$attribute->orderid]->attributes[]=$attribute;

				}
			}
		}


		return $this->_data;
	}

	function getAttributes($ids)
	{
		$idsStr = implode(',',$ids);

        $newModeFields = '';
        if (LegacyHelper::updatedDB())
            $newModeFields = "at.value value, at.name name,";


        $sql = "
		SELECT o.id orderid, $newModeFields
				o.batch batch,
				at.id id,
				ty.name tyname ,
				dt.name dtname,
				at.value_left valueleft, at.value_right valueright,
				atvl.name mvalueleft, atvr.name mvalueright,
				at.memo_left memoleft, at.memo_right memoright
		FROM #__vxc_attribute at
		LEFT JOIN #__vxc_order o on o.id =at.orderid
		LEFT JOIN #__vxc_attributetype ty ON ty.id = at.type
		LEFT JOIN #__vxc_datatype dt ON dt.id = ty.datatype
		LEFT JOIN #__vxc_attributetypevalue atvl ON atvl.id=at.value_left
		LEFT JOIN #__vxc_attributetypevalue atvr ON atvr.id=at.value_right
		WHERE o.id IN ($idsStr)";


		$this->_db->setQuery($sql);
		return $this->_db->loadObjectList();
	}

	function getMaterials($includeDoctor=false)
	{
		$abbs = array();
		$doctorAbbs = array();
		switch(SiteOptionsHelper::getFranchiseName())
		{
			case 'india':
			case 'tecnoinsole':
            case 'velasco':


			$doctorAbbs = array(
				'TI_PS_AntepieBalanceInv' => array('type' => 'degintdepend' , 'abb' => 'BALINV','text' => JText::_('Balance Invertido')),
				'TI_PS_AntepieBalanceEver' => array('type' => 'degintdepend' , 'abb' => 'BALEVER','text' => JText::_('Balance Evertido')),

				'TI_PS_RetropieLateralHS'=> array('type' => 'mmdegdepend' , 'abb' => 'LHS','text' => JText::_('Lateral Heel Skive')),
				'TI_PS_RetropieMedialHS'=> array('type' => 'mmdegdepend' , 'abb' => 'MHS','text' => JText::_('Medial Heel Skive')),
				'TI_PS_RetropieRecrecido'=> array('type' => 'mmdepend' , 'abb' => 'RECRESUB','text' => JText::_('Recrecido subcalcáneo')),

				'TI_PS_PartesBlandasMedial'=> array('type' => 'mmdepend' , 'abb' => 'ADIPBLANDAM','text' => JText::_('Adición partes blandas medial')),
				'TI_PS_PartesBlandasLateral'=> array('type' => 'mmdepend' , 'abb' => 'ADIPBLANDAL','text' => JText::_('Adición partes blandas lateral')),
				'TI_PS_PartesBlandasTalonLateral'=> array('type' => 'mmdepend' , 'abb' => 'ADIPBLANDATL','text' => JText::_('Adición partes blandas talón lateral')),

				'TERMOPOSTMEDIFASCIAL' => array('abb'=>'ACOMOFASCIAL','text'=>JText::_('Acomodación Fascial')),

				'TI_PS_MediopieEstALEMM' => array('type' => 'mm', 'abb' => 'REFALEMM','text' => JText::_('Altura refuerzo ALE')),

				'TI_PS_MediopieRefArcoWaveMM'=> array('type' => 'mm', 'abb' => 'REFALEMM','text' => JText::_('Altura refuerzo ALI medial waves')),
				'TI_PS_MediopieRefArcoSolMM'=> array('type' => 'mm', 'abb' => 'REFALEMM','text' => JText::_('Altura refuerzo ALI sólido')),

				'TI_PS_MediopieDescensoArcoALI'=> array('type' => 'mmdepend', 'abb' => 'DESARCALI', 'text' => JText::_('Ajuste altura arco interno')),
				'TI_PS_MediopieDescensoArcoALE'=> array('type' => 'mmdepend', 'abb' => 'DESARCALE', 'text' => JText::_('Ajuste altura arco externo')),

				'TI_PS_AntepieDescHerraEVA' => array('abb'=>'DESCHERRMETAEVA','text'=>JText::_('Descarga en herradura de metatarsianos centrales')),

				'TI_PS_AntepieBarra'=> array('type' => 'mmdepend', 'abb' => 'BARRAINT', 'text' => JText::_('Barra intrínseca')),
				'TI_PS_AntepieAlmendra'=> array('type' => 'mmdepend', 'abb' => 'ALMENDRAINT', 'text' => JText::_('Almendra intrínseca'))



				);




			$abbs = array(
					'POSITIVO' => array('abb'=>'PU','text'=>JText::_('Poliuretano')),
					'TERMOPOLIPRO2' => array('abb'=>'PP2','text'=>JText::_('Polipropileno 2mm')),
					'TERMOPOLIPRO3' => array('abb'=>'PP3','text'=>JText::_('Polipropileno 3mm')),
					'TERMOPOLIPRO4' => array('abb'=>'PP4','text'=>JText::_('Polipropileno 4mm')),
					'TERMOPOLIPRO5' => array('abb'=>'PP5','text'=>JText::_('Polipropileno 5mm')),
					'TERMOCARBONO15' => array('abb'=>'FIB','text'=>JText::_('Fibra de carbono')),
					'TERMORESINAHERFLEX' => array('abb'=>'RHERFLEX','text'=>JText::_('Herflex 1.9 blanco + Herflux Mac 1.3 Carne')),
					'TERMORESINAFLUX' => array('abb'=>'RFLUX','text'=>JText::_('Resina Podiaflux')),
					'TERMORESINAFLEX' => array('abb'=>'RFLEX','text'=>JText::_('Resina Podiaflex')),
					'TERMORESINAFLUXFLEX' => array('abb'=>'RFLUXFLEX','text'=>JText::_('Resina Podiaflux/Podiaflex')),

					'TERMOPOSTMEDICACU' => array('abb'=>'PMCACU','text'=>JText::_('Posteado Mediopié Estabilizador CA/CU')),
					'TERMOPOSTMEDIALI' => array('abb'=>'PMALI','text'=>JText::_('Posteado Mediopié Estabilizador ALI')),
					'TI_PS_RetropieTaloPoron' => array('abb'=>'RETROTALOPORON', 'text'=>JText::_('Talonera porón Shockicel Transpira 3mm azul')),
                    'TI_PS_FORRADOEvaGrip25_3mm' => array('abb'=>'FEVAGRIP25_NARANJA_3','text'=>JText::_('Forro Técnico EVAGRIP 25 3mm. Color Naranja')),
                   'TI_PS_FORRADOTEC35RF25GRIS' => array('abb'=>'FEVARB25_GRIS_3,5', 'text'=>JText::_('Forro técnico de EVA modificado RF25 Gris de espesor 3,5mm')),
                   'TI_PS_FORRADOTEC2SANVERDE' => array('abb'=>'FEVASANIT_VERDE_2', 'text'=>JText::_('Forro técnico de EVA modificado SANIT de espesor 2mm')),
                   'TI_PS_FORRADOTEC3SANVERDE' => array('abb'=>'FEVASANIT_VERDE_3', 'text'=>JText::_('Forro técnico de EVA modificado SANIT de espesor 3mm')),
                   'TI_PS_FORRADOTEC3AZULCOB' => array('abb'=>'FEVASP25_COBALTO_3', 'text'=>JText::_('Forro técnico de EVA modificado AZUL COBALTO de espesor 3mm')),
                   'TI_PS_FORRADOTEC3VARIOS' => array('abb'=>'FEVATECNVARIOS_3', 'text'=>JText::_('Forro técnico de EVA modificado Varios Colores de espesor 3mm')),
                   'TI_PS_FORRADOEST2VARIOS' => array('abb'=>'FEVAVARIOS_2', 'text'=>JText::_('Forro de EVA estándar varios colores sin especificar, de espesor 2mm')),
                   'TI_PS_FORRADOEST3VARIOS' => array('abb'=>'FEVAVARIOS_3', 'text'=>JText::_('Forro de EVA estándar varios colores sin especificar, de espesor 3mm')),
                   'TI_PS_FORRADOMICFCAMEL' => array('abb'=> 'FTEXTCAMEL', 'text'=>JText::_('Forro de microfibra textil color Camel')),
                   'TI_PS_FORRADOMICFTABACO' => array('abb'=>'FTEXTTABACO', 'text'=>JText::_('Forro de microfibra textil color Tabaco')),
                   'TI_PS_FORRADOMICFCRUDO' => array('abb'=> 'FTEXTCRUDO', 'text'=>JText::_('Forro de microfibra textil color Blanco Crudo')),
                   'TI_PS_FORRADOMICFPERFBEIGE' => array('abb'=> 'FTEXTPERFBEIGE', 'text'=>JText::_('Forro de microfibra textil perforada color Beige')),
                   'TI_PS_FORRADOMICFVARIOS' => array('abb'=>'FTEXTVARIOS', 'text'=>JText::_('Forro de microfibra textil colores varios sin especificar')),
                   'TI_PS_FORRADOPIEL' => array('abb'=>'FPIEL', 'text'=>JText::_('Forro de microfibra textil piel calidad superior')),

                    'TI_PS_PolyMaterialColor' => array('type'=>'multi','single'=>true,'abb'=>'POLYSHELLCOLOR', 'text'=>JText::_('Color del polipropileno')),

					'TI_PS_RetropieRecrecidoPoron' => array('abb'=>'RETRORECRPORON', 'text'=>JText::_('Relleno de porón 3 mm en recrecido subcalcáneo de retropie')),
					'TI_PS_RetropiePostEsta' => array('type' => 'pseudoint', 'options' =>
							array(1 => array('abb'=>'RETROPOSTESTAEVA', 'text'=>JText::_('Posteado estabilizador EVA alta densidad en retropié')),
								  2 => array('abb'=>'RETROPOSTESTAFUSION', 'text'=>JText::_('Posteado estabilizador POLIPROPILENO en retropié')))
								  ),
					'TI_PS_MecaArmazonGaitPlate' => array('abb'=>'ARMAZONGAITPLATE','text'=>JText::_('Armazón tipo Gait Plate')),
					'TI_PS_MecaArmazonGaitPlateType' => array('type'=>'multi','abb'=>'GAITTYPE','text'=>JText::_('Tipo de Gait Plate')),

					'TI_PS_AntepieKineticWedge' => array('abb'=>'ANTKINETIC','text'=>JText::_('Kinetic Wedge')),
					'TI_PS_AntepieCabezaM1' => array('abb'=>'ANTDESCARGADECM1','text' => JText::_('Descarga tipo decrecido M1')),
					'TI_PS_AntepieCabezaM2' => array('abb'=>'ANTDESCARGADECM2','text' => JText::_('Descarga tipo decrecido M2')),
					'TI_PS_AntepieCabezaM3' => array('abb'=>'ANTDESCARGADECM3','text' => JText::_('Descarga tipo decrecido M3')),
					'TI_PS_AntepieCabezaM4' => array('abb'=>'ANTDESCARGADECM4','text' => JText::_('Descarga tipo decrecido M4')),
					'TI_PS_AntepieCabezaM5' => array('abb'=>'ANTDESCARGADECM5','text' => JText::_('Descarga tipo decrecido M5')),




					'TI_PS_AntepieCabezaCutOutM1' => array('abb'=>'ANTDESCARGACUTOUTM1','text' => JText::_('Descarga tipo cut-out M1')),
					'TI_PS_AntepieCabezaCutOutM2' => array('abb'=>'ANTDESCARGACUTOUTM2','text' => JText::_('Descarga tipo cut-out M2')),
					'TI_PS_AntepieCabezaCutOutM3' => array('abb'=>'ANTDESCARGACUTOUTM3','text' => JText::_('Descarga tipo cut-out M3')),
					'TI_PS_AntepieCabezaCutOutM4' => array('abb'=>'ANTDESCARGACUTOUTM4','text' => JText::_('Descarga tipo cut-out M4')),
					'TI_PS_AntepieCabezaCutOutM5' => array('abb'=>'ANTDESCARGACUTOUTM5','text' => JText::_('Descarga tipo cut-out M5')),

					'TI_PS_MediopieEstALE' =>  array('type'=>'mmdepend','abb'=>'MEDESTALE','text' => JText::_('Estabilizador ALE de Mediopie')),

					'TI_PS_AntepieDescHerrEVA' =>  array('type'=>'mmdepend','abb'=>'ANTDESCHERREVA','text' => JText::_('Descarga en herradura de EVA')),
					'TI_PS_AntepieDescHerrEVAMetas' => array('type'=>'multi','abb'=>'ANTDESCHERREVAMETAS','text'=>JText::_('Descarga en herradura de EVA (Metas)')),


					'TI_PS_AntepieDescSubDec' =>  array('type'=>'mmdepend','abb'=>'ANTDESCSUBDEC','text' => JText::_('Descarga tipo decrecido')),
					'TI_PS_AntepieDescSubCutOut' =>  array('type'=>'mmdepend','abb'=>'ANTDESCSUBCUTOUT','text' => JText::_('Descarga tipo cut-out')),

					'TI_PS_RetropieInternalHeelCup' => array('type'=>'mmdepend','abb'=>'RETROINTHEELCUP','text' => JText::_('Internal Heel Cup en Retropie')),
					'TI_PS_RetropieTotalHeelCup' => array('type'=>'mmdepend','abb'=>'RETROTOTALHEELCUP','text' => JText::_('Total Heel Cup en Retropie')),
					'TI_PS_RetropieExternalHeelCup' => array('type'=>'mmdepend','abb'=>'RETROEXTHEELCUP','text' => JText::_('External Heel Cup en Retropie')),

					'TI_PS_RetropieTaloCorta' =>  array( 'type' => 'mmdepend', 'abb' => 'RETROTALOCORTA','text' => JText::_('Talonera corta')),

					'TI_PS_RetropieAlza' => array( 'type' => 'mmdepend' , 'abb' => 'RETROALZA','text' => JText::_('Alza EVA 65º')),

					'TI_PS_AntepieCunaSup' => array('type' => 'mmdepend', 'abb' => 'ANTCUNASUP', 'text' => JText::_('Cuña supinadora de antepie EVA 65º')),

					'TI_PS_AntepieCunaPro' => array('type' => 'mmdepend', 'abb' => 'ANTCUNAPRO', 'text' => JText::_('Cuña pronadora de antepie EVA 65º')),

					'TI_PS_PolycupThicknessMM' => array('type' => 'mm', 'abb' => 'THICK', 'text' => JText::_('Grosor polycup')),


					'TI_PS_MediopieAletaInterna' => array('abb' => 'MEDALETAINTERNA','text'=> JText::_('Aleta interna en mediopié')),
					'TI_PS_MediopieAletaExterna' => array('abb' => 'MEDALETAEXTERNA','text'=> JText::_('Aleta externa en mediopié')),


					'TI_PS_AntepieMortonRigid' => array('abb' => 'ANTMORTONRIGID','text' => JText::_('Extensión de Morton de material rígido')),

					'TI_PS_AntepieMortonEVA' => array('type' => 'mmdepend','abb' => 'ANTMORTONEVA','text' => JText::_('Extensión de Morton de EVA 65º sore')),

                    'TI_PS_Unfinished' => array('abb' => 'SEMIACABADA','text' => JText::_('Plantilla semiacabada')),

					'TI_PS_AntepieDescargas' => array('type' => 'pseudoint', 'options' =>
							array(	1 => array('abb'=>'ANTHERRARIGIDA', 'text'=>JText::_('Herradura con material rígido en antepié')),
									2 => array('abb'=>'ANTHERRAEVA', 'text'=>JText::_('Herradura semidura en antepié')),
									3 => array('abb'=>'ANTDESCARGADEC', 'text'=>JText::_('Descarga subcapital tipo decrecido en antepié')),
									4 => array('abb'=>'ANTDESCARGATOTPORON', 'text'=>JText::_('Apoyo subcapital total de porón en antepié')),
									5 => array('abb'=>'ANTDESCARGACUTOUT', 'text'=>JText::_('Descarga subcapital tipo Cut-out en antepié'))
									)
								  ),

					'TI_PS_MecaArmazonTipo' => array('type' => 'pseudoint', 'options' =>
						array(	'0' => array('abb'=>'ARMAZONSTANDARD', 'text'=>JText::_('Armazón estandard')),
								'1' => array('abb'=>'ARMAZONFUNCIONAL', 'text'=>JText::_('Armazón funcional'))
							  )),


					'TI_PS_AntepieDescHerrEVAPoron' => array('abb'=>'ANTDESCHERREVAPORON','text' => JText::_('Relleno de porón en descarga en herradura de EVA')),
					'TI_PS_AntepieDescSubDecPoron' => array('abb'=>'ANTDESCSUBDECPORON','text' => JText::_('Relleno de porón en descarga tipo decrecido')),
					'TI_PS_AntepieDescSubCutOutPoron' => array('abb'=>'ANTDESCSUBCUTOUTPORON','text' => JText::_('Relleno de porón en descarga tipo cut-out')),

					'TI_PS_AntepieBarraMetaExt' => array('abb'=>'ANTBARRAEXT','text'=>JText::_('Barra metatarsal extrínseca semiblanda (5 mm) en antepie')),
					'TI_PS_AntepieBarraMetaExtMetas' => array('type'=>'multi','abb'=> 'ANTBARRAEXT','text'=>JText::_('Barra metatarsal extrínseca semiblanda (5 mm) en antepie (Metas)')),

					'TI_PS_AntepieExpansion' => array('abb'=>'ANTEXPANSION','text' => JText::_('Expansión de 2º-5º mtt hasta sulcus en 3 mm')),



					'TI_PS_AntepieAlmendraMetaExt' => array('abb'=>'ANTALMENDRAMETAEXT','text' => JText::_('Almendra Metatarsal Extrínseca en antepie')),
					'TI_PS_AntepieAlmendraMetaExtMetas' => array('type'=>'multi','abb'=> 'ANTALMENDRAMETAEXT','text'=>JText::_('Almendra Metatarsal Extrínseca en antepie (Metas)')),


					'TI_PS_FORRADO34' => array('abb' => 'FORRADO34','text' => JText::_('Forrado a 3/4')),



					'TI_PS_FORRADOColorEVA2' => array('type'=>'multi','abb'=>'FORREVA2','text' => JText::_('Color Forro EVA 2 mm')),
					'TI_PS_FORRADOColorEVA3' => array('type'=>'multi','abb'=>'FORREVA3','text' => JText::_('Color Forro EVA 3 mm')),
					'TI_PS_FORRADOColorShockIcel' => array('type'=>'multi','abb'=>'FORRSHOCK','text' => JText::_('Color Forro Shockicel')),
					'TI_PS_FiberMaterial' => array('type'=>'multi','abb'=>'FIBMAT','text'=>JText::_('Tipo de material de la fibra')),
					'TI_PS_MediopieRellenoAFascial' => array('abb'=>'MEDRELLACOFASC','text'=> JText::_('Relleno de porón Memory Rojo en acomodación fascial de mediopié')),
					'MECAABS' => array('abb'=>'MECAABS','text'=>JText::_('Mecanizado ABS')),
					'MECAPOLIPRO' => array('abb'=>'MECAPOLIPRO','text'=>JText::_('Mecanizado Polipropileno')),
					'MECAPOLIEHD' => array('abb'=>'MECAPOLIE','text'=>JText::_('Mecanizado Polietileno HD')),
					'MECAPOLIPRO3SIDED' => array('abb'=>'MECAPOLIPRO3SIDED','text'=>JText::_('Mecanizado Polipropileno a 3 caras')),
					'EVAMONO30' => array('abb'=>'EV30','text'=>JText::_('EVA Monodensidad 30mm')),
					'EVAMONO40' => array('abb'=>'EV40','text'=>JText::_('EVA Monodensidad 40mm')),
					'EVAMONO55' => array('abb'=>'EV55','text'=>JText::_('EVA Monodensidad 55mm')),
					'3DCONFORTMONO30' => array('abb'=>'3DCONFORTEV30','text'=>JText::_('EVA 3D Confort Monodensidad 30mm')),
					'3DCONFORTMONO40' => array('abb'=>'3DCONFORTEV40','text'=>JText::_('EVA 3D Confort Monodensidad 40mm')),
					'3DCONFORTBI3055' => array('abb'=>'3DCONFORTEVB3055','text'=>JText::_('EVA 3D Confort Bidensidad 30-55mm')),
                    '3DCONFORTBI4055SPORT' => array('abb'=>'3DCONFORTEVB4055SPORT','text'=>JText::_('EVA 3D Confort Bidensidad 40-55mm Sport')),
					'EVABI3055' => array('abb'=>'EVB3055','text'=>JText::_('EVA Bidensidad 30-55mm')),
					'FORRADOEVA2' => array('abb'=>'FEV2','text'=>JText::_('Forrado EVA 2 mm 30º Sore A')),
					'FORRADOEVA3' => array('abb'=>'FEV3','text'=>JText::_('Forrado EVA 3 mm 30º Sore A')),
					'FORRADOPIEL' => array('abb'=>'FPIEL','text'=>JText::_('Forrado Piel')),
					'FORRADOSHOCKICEL' =>array('abb'=>'FSHOCK','text'=>JText::_('Forrado Shockicel Transpira')),
					'FORRADOPORONMEMORY' =>array('abb'=>'FMEMORY','text'=>JText::_('Forrado Porón Memory')),
					'FORRADOPLASTAZOTE' => array('abb'=>'FPLASTAZOTE','text'=>JText::_('Forrado Plastazote 3mm')),
					'InsoleCADCoverProject' => array('abb'=>JText::_('MILLEDCOVER'))
					);
			break;

			case 'lafoot':

				$abbs = array('Sports general' => array('abb'=>'SG','text'=>JText::_('Basis Model: Sports general')),
					'Running' => array('abb'=>'R','text'=>JText::_('Basis Model: Running')),
					'IMBLOCKOHNE' => array('abb'=>'BLKOHNE','text'=>JText::_('Im Block Ohne Decke')),
					'IMBLOCKLOSE' => array('abb'=>'BLKLOSE','text'=>JText::_('Im Block Lose Decke')),
					'EMFORMTOHNE' => array('abb'=>'EBOHNE','text'=>JText::_('Enformt+Beschliffen Ohne Decke')),
					'EMFORMTLOSE' => array('abb'=>'EBLOSE','text'=>JText::_('Enformt+Beschliffen Ohne Decke')),
					'EMFORMTVOLL' => array('abb'=>'EBVOLL','text'=>JText::_('Enformt+Beschliffen Vollverklebt'))

					);
			break;

			case 'rotterdam':
				$abbs = array(
					'ROT_MO_CoveredInsole' => array('abb'=>'COVERED','text' => JText::_('Covered')),
					'ROT_MO_Material' => array('type'=>'multi','abb'=>'MATERIAL','text'=>JText::_('Material')),
					'ROT_MO_Covering' => array('type'=>'multi','abb'=>'COVERING','text'=>JText::_('Covering'))
				);
			break;

			case 'jjpodo':
				$abbs = array(
						'PODO_Finishing' => array('abb'=>'FINISH','text'=>JText::_('Finishing'))
					);
			break;

			case 'voxelcare':
			case 'ppr':

				$abbs = array(
					'IMBLOCKOHNE' => array('abb'=>'BLKOHNE','text'=>JText::_('Im Block Ohne Decke')),
					'IMBLOCKLOSE' => array('abb'=>'BLKLOSE','text'=>JText::_('Im Block Lose Decke')),
					'EMFORMTOHNE' => array('abb'=>'EBOHNE','text'=>JText::_('Enformt+Beschliffen Ohne Decke')),
					'EMFORMTLOSE' => array('abb'=>'EBLOSE','text'=>JText::_('Enformt+Beschliffen Ohne Decke')),
					'EMFORMTVOLL' => array('abb'=>'EBVOLL','text'=>JText::_('Enformt+Beschliffen Vollverklebt')),
					'PPR_EinlagenVarianten' => array('type'=>'multi','abb'=>'EINVAR','text'=>JText::_('Einlagenvarianten'),'single'=>true)
					);
			break;

			case 'jjcorne':
				$abbs = array(
					'JJC_OrderType' => array('type'=>'string','abb'=>'ORDERTYPE','text'=>JText::_('Order Type')),
					'JJC_BlockColor' => array('type'=>'string','abb'=>'BLCKCLR','text'=>JText::_('Block Color')),
					'JJC_ShoeSizeType' => array('type'=>'string','abb'=>'','text'=>JText::_('Shoe size type')),
					'JJC_BrandSoleType' => array('type'=>'string','abb'=>'BRAND','text'=>JText::_('Brandsole type')),
					'JJC_Weight' => array('type'=>'string','abb'=>'W','text'=>JText::_('Weight')),
					'JJC_Activity' => array('type'=>'string','abb'=>'ACTVITY','text'=>JText::_('Activity')),
					'JJC_ShoeType' => array('type'=>'string','abb'=>'SHOETYPE','text'=>JText::_('Shoe type')),
					'JJC_Cover' => array('type'=>'string','abb'=>'COVER','text'=>JText::_('Cover'))
				);
			break;

		}

		if ($includeDoctor)
		{
			foreach($doctorAbbs as $key=>$value)
			{
				$abbs[$key] = $value;
			}
		}

		return $abbs;
	}
	function getCustomers()
	{
		$userId = $this->searchuser;
		if (!$userId) return array();

		$db =& $this->_db;
		$sql = "SELECT id id, CONCAT_WS(' ',name,middlename,surname) fullname
				FROM #__vxc_customer
				WHERE user = $userId AND deleted IS NOT TRUE";
		$db->setQuery($sql);
		$customers = $db->loadObjectList();
		return $customers;
	}

	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 uf.userid = u.id
				WHERE uf.franchise = $franchiseId AND uf.deleted IS NOT TRUE
				ORDER BY u.name";
		$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['searchreference']= $this->searchreference;
		$lists['searchcustomer']= $this->searchcustomer;
		$lists['searchstate']= $this->searchstate;
		$lists['searchmanustate']= $this->searchmanustate;
		$lists['searchmat']= $this->searchmat;
		$lists['searchuser'] = $this->searchuser;
		$lists['searchtomanudate'] = $this->searchtomanudate;
		$lists['searchtomanudateend'] = $this->searchtomanudateend;
		$lists['searchtoshippingdate'] = $this->searchtoshippingdate;
		$lists['searchtoshippingdateend'] = $this->searchtoshippingdateend;

		$lists['searchfranchise']= $this->searchfranchise;
		$lists['searchid']= $this->searchid;

		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');
			if ($this->searchstate == 'MANUFACTURING')
				$this->_pagination = new JPagination($this->getTotal(), $this->getState('limitstart'), $this->_maxShownOrders);
			else
				$this->_pagination = new JPagination($this->getTotal(), $this->getState('limitstart'), $this->getState('limit') );

		}

		return $this->_pagination;
	}

	function getManuReportSummary()
	{
		$sql = $this->_buildSummaryQuery();

		$this->_db->setQuery($sql);

		return $this->_db->loadObjectList();


	}

	function _buildSummaryQuery()
	{
		$where = array();


		$where[] = " (os.nameid = 'MANUFACTURING' OR os.nameid = 'CHECKED') ";

		if ($this->searchfranchise)
			$where[] = " uf.franchise = ". $this->searchfranchise;

		$where[] = " o.manufacturing_date IS NOT NULL ";


		if ($this->searchtomanudate)
		{
			$dateS =str_replace('-','/',trim($this->searchtomanudate));
			$dateArray = explode('/',$dateS);
			$dateCount = count($dateArray);

			$dateClauses= array();

			$day = $month = $year = -1;

			if ($dateCount==3)
			{
				$day = (int) $dateArray[0];
				$month = (int) $dateArray[1];
				$year = (int) $dateArray[2];

				$where[] = " o.manufacturing_date >= ".$this->_db->Quote($year . "-". $month."-". $day);
			}
		}


		if ($this->searchtomanudateend)
		{
			$dateS = str_replace('-','/',trim($this->searchtomanudateend));
			$dateArray = explode('/',$dateS);
			$dateCount = count($dateArray);

			$dateClauses= array();

			$day = $month = $year = -1;

			if ($dateCount==3)
			{
				$day = (int) $dateArray[0];
				$month = (int) $dateArray[1];
				$year = (int) $dateArray[2];

				$where[] = " o.manufacturing_date <= ".$this->_db->Quote($year . "-". $month."-". $day);
			}
		}


		if ($this->searchtoshippingdate)
		{
			$dateS =str_replace('-','/',trim($this->searchtoshippingdate));
			$dateArray = explode('/',$dateS);
			$dateCount = count($dateArray);

			$dateClauses= array();

			$day = $month = $year = -1;

			if ($dateCount==3)
			{
				$day = (int) $dateArray[0];
				$month = (int) $dateArray[1];
				$year = (int) $dateArray[2];

				$where[] = " b.est_shipping_date >= ".$this->_db->Quote($year . "-". $month."-". $day);
			}
		}


		if ($this->searchtoshippingdateend)
		{
			$dateS = str_replace('-','/',trim($this->searchtoshippingdateend));
			$dateArray = explode('/',$dateS);
			$dateCount = count($dateArray);

			$dateClauses= array();

			$day = $month = $year = -1;

			if ($dateCount==3)
			{
				$day = (int) $dateArray[0];
				$month = (int) $dateArray[1];
				$year = (int) $dateArray[2];

				$where[] = " b.est_shipping_date <= ".$this->_db->Quote($year . "-". $month."-". $day);
			}
		}


		$where[] = ' o.deleted IS NOT TRUE ';
	//	$where[] = ' (r.id IS NULL OR (r.id IS NOT NULL AND r.paid = TRUE)) ';

		$where =( count($where) ) ? ' WHERE ' . implode( ' AND ', $where ) : '';


		$query = " SELECT
								u.id userid,
								u.name username,
								o.manufacturing_date manufacturing_date,
								r.id receiptid,
								r.paid receiptpaid,
								count(*) insolecount
								 "
				. ' FROM #__vxc_order o
					LEFT JOIN #__vxc_user u ON u.id = o.user
					LEFT JOIN #__vxc_userfranchise uf ON uf.userid = u.id
					LEFT JOIN #__vxc_franchise f ON uf.franchise = f.id
					LEFT JOIN #__vxc_orderstate os ON os.id = o.state
					LEFT JOIN #__vxc_customer c ON c.id = o.customer
					LEFT JOIN #__vxc_batch b ON b.id = o.batch
					LEFT JOIN #__vxc_paymentmethod pm ON pm.id = b.payment_method
					LEFT JOIN #__vxc_manustate ms ON ms.id = o.manu_state
					LEFT JOIN #__vxc_agency a ON a.id = b.agency
					LEFT JOIN #__vxc_receipt r ON r.orderid = o.id '
				. $where
				. " GROUP BY o.manufacturing_date, u.id
					ORDER BY o.manufacturing_date ASC , u.id ASC "
				;


		return $query;
	}



}// class