<?php
namespace Stock\Service;


/**
 * Manage stock data.
 * @author andre.fourie
 */

class Stock extends \Stock\DataBin\Stock
{


	/**
	 * Initialize a new item.
	 * @param \Stock\Entity\Stock $stock
	 */
	public function initNewItem(\Stock\Entity\Stock $stock) { }

	/**
	 * Route newly added item to initial state.
	 * @param \Stock\Entity\Stock $stock
	 * @param string|null $previousState
	 * @param array|null $routingData
	 * @return string
	 */
	public function routeNewItem(\Stock\Entity\Stock $stock, $previousState, $routingData)
	{
		if (\Utility\Registry::checkOnce('NewStockItem.FromValuation'))
		{
			return 'This.Valuation';
		}
		return array(
				'Destination' => \Utility\Registry::checkOnce('NewStockItem.Status', 'This.Stock'),
				'Data' => \Utility\Registry::checkOnce('Service.Stock.Context', array())
		);
	}

	/**
	 * Route to Retail and set reset time.
	 * @param \Stock\Entity\Stock $stock
	 * @param string $previousState
	 */
	public function directRouteSendToRetail(\Stock\Entity\Stock $stock, $previousState)
	{
		$stock->reset = new \DateTime(date('Y-m-d H:i:s', time() + 86400));
		$this->em->flush();
		return 'This.Retail';
	}


	/**
	 * Route to Trade Centre
	 * @param \Stock\Entity\Stock $stock
	 * @param string $previousState
	 */
	public function directRouteSendToTradeCenter(\Stock\Entity\Stock $stock, $previousState)
	{

		//Write hit to Stats logs (need stock or valuation stock id and profile id)
		$today = new \DateTime();
		//$test = "123";
		//\Utility\Debug::errorLog('directRouteSendToTradeCenter new', $test);

		$em = \Utility\Registry::getEntityManager();
		$loghit = new \Statistical\Entity\Statistical();
		$loghitData = array(
				'stock'      		=> $em->getRepository('\Stock\Entity\Stock')->find($stock->id),
				'dealdone'			=> false,
				'dealnotdone'    	=> false,
				'tradecentre'      	=> true,
				'fromProfile'      	=> $em->getRepository('\User\Entity\Profile')->find($stock->createdBy->id),
				'created'     		=> $today
		);

		$loghit->fromArray($loghitData);
		$em->persist($loghit);
		$em->flush();

		return 'This.TradeCenter';
	}

	/**
	 * Route to Retail and set reset time..
	 * @param \Stock\Entity\Stock $stock
	 * @param string $previousState
	 */
	public function directRouteReclaimFromRetail(\Stock\Entity\Stock $stock, $previousState)
	{
		$stock->reset = null;
		$this->em->flush();
		return 'This.Stock';
	}

	/**
	 * Route item that is received back from Auction Workflow.
	 * @param \Stock\Entity\Stock $stock
	 * @param string $previousState
	 * @param array|null $routingData
	 */
	public function routeAuctionHandover(\Stock\Entity\Stock $stock, $previousState, $routingData)
  {
		if ('Sold' == $stock->auction->jobState
			|| (time() > $stock->auction->endDate->getTimestamp()
				&& $stock->auction->reservePrice < $stock->auction->currentBidPrice && 'Undone' != $stock->auction->jobState))
		{
      $this->em->flush();
			return 'This.Sold';
		}
    
    if ($stock->previousState == 'Trade Center')
    {
      $stock->previousState = 'Auction';
      $this->em->flush();
      return 'This.TradeCenter';
    }
    else
    {
      $stock->previousState = 'Auction';
      $this->em->flush();
      return 'This.Stock';
    }
	}

	/**
	 * ExecuteAfter: Create.
	 * Add item to queue for mobile.
	 * @param array $meta
	 * @param object|null $jobRecord
	 * @param object|null $record
	 * @param \Workspace\Utility\ServiceInputParams $contract
	 * @return array
	 */
	public function generateHistoryList($meta, $jobRecord, $record, \Workspace\Utility\ServiceInputParams $contract)
	{
		$record = null == $jobRecord
			? $record->toArray()
			: $jobRecord->toArray();

 		$StockID = $record['id'];
 		$stockTypeID = $record['type'];

		#-> Clear any existing history for this vehicle.
		$this->em->createQuery(
				'DELETE FROM \Stock\Entity\PricingHistory pricingHistory '
				. 'WHERE pricingHistory.stockId = '
				. $StockID
				)
			->execute();

		#-> Establish history.
 		$result = $this->em->createQuery(
 				'SELECT stock, st, vy '
 				. 'FROM \Stock\Entity\Stock stock '
 				. 'LEFT JOIN stock.type st '
 				. 'LEFT JOIN stock.vehicleYear vy '
 				. 'WHERE stock.id = :StockID '
	 		)
	 		->setParameter('StockID', $StockID)
	 		->getArrayResult();

 		$stockTypeID = $result[0]['type']['id'];
 		$vehicleYearID = $result[0]['vehicleYear']['id'];


 		$today = new \DateTime();
 		$dateFrom = clone $today;
 		$dateFrom->modify('-3 months');

 		$result = $this->em->createQuery(
 				'SELECT stock, priceguide, auction, st, po, cb, bids '
 				. 'FROM \Stock\Entity\Stock stock '
				. 'LEFT JOIN stock.priceGuide priceguide WITH priceguide.created BETWEEN :dateFrom AND :dateTo '
				. 'LEFT JOIN stock.auction auction WITH auction.created BETWEEN :dateFrom AND :dateTo '
 				. 'LEFT JOIN stock.type st '
 				. 'LEFT JOIN priceguide.offers po '
 				. 'LEFT JOIN auction.currentBid cb '
 				. 'LEFT JOIN auction.bids bids '
 				. 'WHERE st.id = :stockTypeID '
 				. 'AND IDENTITY(stock.vehicleYear) = :vehicleYearID '
 				. 'AND (stock.highestBid > 0 OR stock.highestOffer > 0) '
 				. 'AND stock.archived = 0 '
 				. 'ORDER by auction.created, priceguide.created ASC '
	 		)
	 		->setParameter('stockTypeID', $stockTypeID)
	 		->setParameter('vehicleYearID', $vehicleYearID)
	 		->setParameter('dateFrom', $dateFrom)
	 		->setParameter('dateTo', $today)
	 		->getArrayResult();

 		$i = 0;
 		$x = 0;
		$existing = array();
		foreach ($result as $pricingHistory)
		{
			$StockDamageTotal = $pricingHistory['damageTotal'];
			$StockTradePrice = $pricingHistory['tradePrice'];
            $StockListPrice = $pricingHistory['listPrice'];
			$StockKMS = $pricingHistory['km'];
			if ($StockKMS == '' || is_null($StockKMS))
			{
				$StockKMS = 0;
			}
			// price guide
			$PriceguideHighestOffer = $pricingHistory['highestOffer'];
			// Auction
			$highestBid = $pricingHistory['highestBid'];

			// Priceguide && Auction
			if ($PriceguideHighestOffer != 0 && $highestBid != 0 )
			{
				$priceGuideOffers = $pricingHistory['priceGuide']['offers'];
				if (is_array($priceGuideOffers))
				{
					$highestAmnt = 0.0;
					foreach ($priceGuideOffers as $priceGuideOffer)
					{
						if ($priceGuideOffer['amount'] > $highestAmnt)
						{
							$highestAmnt = $priceGuideOffer['amount'];
							$priceGuideOffersCreatedDate = $priceGuideOffer['created'];
						}
					}

					$data = array(
							'date'					=> $priceGuideOffersCreatedDate,
							'trade'					=> $StockTradePrice,
                            'listPrice'				=> $StockListPrice,
							'totalRecon'			=> $StockDamageTotal,
							'km'					=> $StockKMS,
							'pricegOffer'			=> $PriceguideHighestOffer,
							'auctionWinningBid' 	=> $highestBid,
							'stockId' 				=> $StockID
					);
					$hash = md5(serialize($data));
					if (!isset($existing[$hash]))
					{
						$existing[$hash] = true;
						$entry = new \Stock\Entity\PricingHistory();
						$entry->fromArray($data);
						$this->em->persist($entry);
						$this->em->flush($entry);
						$x++;
					}
				}
			}
			// Price Guide only
			if ($PriceguideHighestOffer != 0 && $highestBid == 0 )
			{
				$priceGuideOffers = $pricingHistory['priceGuide']['offers'];
				if (is_array($priceGuideOffers))
				{
					$highestAmnt = 0.0;
					foreach ($priceGuideOffers as $priceGuideOffer)
					{
						if ($priceGuideOffer['amount'] > $highestAmnt)
						{
							$highestAmnt = $priceGuideOffer['amount'];
							$priceGuideOffersCreatedDate = $priceGuideOffer['created'];
						}
					}
					$data = array(
							'date'					=> $priceGuideOffersCreatedDate, // changes all the time
							'trade'					=> $StockTradePrice,
                            'listPrice'				=> $StockListPrice,
							'totalRecon'			=> $StockDamageTotal,
							'km'					=> $StockKMS,
							'pricegOffer'			=> $PriceguideHighestOffer,
							'auctionWinningBid' 	=> 0.0,
							'stockId' 				=> $StockID

					);
					$hash = md5(serialize($data));
					if (!isset($existing[$hash]))
					{
						$existing[$hash] = true;
						$entry = new \Stock\Entity\PricingHistory();
						$entry->fromArray($data);
						$this->em->persist($entry);
						$this->em->flush($entry);
						$x++;
					}
				}
			}

			// Auction only
			if ($PriceguideHighestOffer == 0 && $highestBid != 0)
			{
				$date = $pricingHistory['auction']['created'];
				$data = array(
						'date'					=> $date, // changes all the time
						'trade'					=> $StockTradePrice,
                        'listPrice'				=> $StockListPrice,
						'totalRecon'			=> $StockDamageTotal,
						'km'					=> $StockKMS,
						'pricegOffer'			=> 0.0,
						'auctionWinningBid' 	=> $highestBid,
						'stockId' 				=> $StockID
				);
				$hash = md5(serialize($data));
				if (!isset($existing[$hash]))
				{
					$existing[$hash] = true;
					$entry = new \Stock\Entity\PricingHistory();
					$entry->fromArray($data);
					$this->em->persist($entry);
					$this->em->flush($entry);
					$x++;
				}
			}

			if ($x > 10)
			{
				break;
			}
		}
	}

	public function updateCustomVehicles($meta, $jobRecord, $contract, $data)
	{
		$contract->data = $this->addCustomVehicles($meta, $contract->data);
		return $contract->data;
	}

	/**
	 * ExecuteBefore: Create
	 * @param array $meta
	 * @param array $data
	 */
	public function addCustomVehicles($meta, $data)
	{
		#-> Check if we have a custom vehicle
		if ($data->Stock['category'] == '8')
		{
			#-> Get latest version.
			$versions = $this->em->createQuery(
					'SELECT MAX(vehicleType.createVersion) AS createVersion,'
					. ' MAX(vehicleType.updateVersion) AS updateVersion '
					. 'FROM Stock\\Entity\\Type vehicleType'
				)
				->getScalarResult();
			$newVersion = $versions[0]['createVersion'] > $versions[0]['updateVersion']
				? $versions[0]['createVersion'] + 1
				: $versions[0]['updateVersion'] + 1;

			#-> Create make, model, type entries
			$makeManual = $data->Stock['makeManual'];
			$modelManual = $data->Stock['modelManual'];
			$typeManual = $data->Stock['typeManual'];
			$category = $data->Stock['category'];
			$today = new \DateTime();
			$em = \Utility\Registry::getEntityManager();

      error_log('Make: ' . $data->Stock['make'] . ' New: ' . $makeManual . "/n");
      error_log('Model: ' . $data->Stock['model'] . ' New: ' . $modelManual . "/n");
      error_log('Type: ' . $data->Stock['type'] . ' New: ' . $typeManual . "/n");
			// Make
			// Do a check if this make exists
			if ('' != $makeManual && $makeManual != $data->Stock['make'])
			{
				$make = $em->getRepository('\Stock\Entity\Make')
					->findOneBy(array(
							'name' => $makeManual
					));

				if (is_null($make))
				{
					// Else add Make Manual
					$make = new \Stock\Entity\Make();
					$makeData = array(
							'name'      		=> $makeManual,
							'created'			=> $today,
							'createVersion'		=> $newVersion
					);

					$make->fromArray($makeData);
					$em->persist($make);
					$em->flush();
				}
				// get make id back
				$makeId = $make->id;
			}
			else
			{
				$makeId = $data->Stock['make'];
			}


			// Model
			if ('' != $modelManual && $modelManual != $data->Stock['model'])
			{
				// Do a check if this model exists
				$model = $em->getRepository('\Stock\Entity\Model')
					->findOneBy(array(
							'make' => $makeId,
							'name' => $makeManual
					));

				if (is_null($model))
				{
					// Add Model Manual
					$model = new \Stock\Entity\Model();
					$modelData = array(
							'make'      		=> $em->getRepository('\Stock\Entity\Make')->find($makeId),
							'name'				=> $modelManual,
							'created'			=> $today,
							'createVersion'		=> $newVersion

					);

					$model->fromArray($modelData);
					$em->persist($model);
					$em->flush();
				}
				//get model id back
				$modelId = $model->id;
			}
			else
			{
				$modelId = $data->Stock['model'];
			}

			// Type

			//\Utility\Debug::errorLog('Stock $typeManual', $typeManual);
			if ('' != $typeManual && $typeManual != $data->Stock['type'])
			{
				// Do a check if this type exists
				$type = $em->getRepository('\Stock\Entity\Type')
					->findOneBy(array(
							'model' => $modelId,
							'category' => $category,
							'name' => $typeManual
					));

				if (is_null($type))
				{
					// Add Type Manual
					$type = new \Stock\Entity\Type();
					$typeData = array(
							'model'      		=> $em->getRepository('\Stock\Entity\Model')->find($modelId),
							'category'			=> $em->getRepository('\Stock\Entity\Category')->find($category),
							'name'				=> $typeManual,
							'mmCode'			=> 'other',
							'introYear'			=> $em->getRepository('\Stock\Entity\Year')->find(1),
							'introMonth'		=> '1',
							'created'			=> $today,
							'createVersion'		=> $newVersion

					);

					$type->fromArray($typeData);
					$em->persist($type);
					$em->flush();

				}
				//get model id back
				$typeId = $type->id;
				//\Utility\Debug::errorLog('Stock $typeId 1', $typeId);
				#-> Update data with new ids
				$data->Stock['type'] = $typeId;
			}


		}
		//\Utility\Debug::errorLog('Stock typeID 2', $data->Stock['type']);
		return $data;
	}

	/**
	 * CRON functionality: Upload stock images.
	 * @param array $meta
	 */
	public function uploadImages()
	{
    	set_time_limit(0);
		$stack = $this->em->getRepository('Stock\\Entity\\StockImages')
				->findBy(array(
						'archived' => 0
				));
		$fields = array(
				'mainImage',
				'frontImage',
				'rightImage',
				'leftImage',
				'backImage',
				'interiorImage',
				'engineImage',
				'natisImage'
		);
		$image = new \Utility\Upload\Image(array(), false);
		foreach ($stack as $item)
		{
			$image = new \Utility\Upload\Image(array(), false);

			foreach ($fields as $fieldBase)
			{
				$urlField = $fieldBase . 'Url';
				if (is_null($item->$fieldBase)
					&& !is_null($item->$urlField)
					&& 0 < strlen($item->$urlField))
				{
					$downloaded = $image->externalDownload($item->$urlField);
					if ($downloaded->id)
					{
						$ref = $this->em->getReference('Utility\\Entity\\Image', $downloaded->id);
						$item->$fieldBase = $ref;
						$item->stock->$fieldBase = $ref;
					}
				}
			}
			$item->archived = 1;
			$this->em->flush();
		}
	}

    /**
     * CRON functionality: Clean up database
     * - Change the status of vehicles in valuation that have a status of "New Valuation" that were loaded before $valuationDate and change status to archived
     * - Change the status of vehicles in stock that were loaded before $stockDate and change the status to archived
     * @param $valuationDate
     * @param $stockDate
     */
    function cleanUpStockAndValuationDatabase($valuationDate,$stockDate)
    {
        error_log('-------------------------------------------------');
        error_log('Database clean up for stock and valuation : START');

        /*
         * variable declaration
         */
//        $valuationLookupStatus  = 'New Valuation,Pending Valuation,Complete Valuation,Stock';

        $valuationLookupStatus = array('New Valuation','Pending Valuation','Complete Valuation','Stock');

        $projectRoot = \Utility\Registry::getConfigParam('ProjectRootPath');
        $output = array();
        $execResult = 0;
        $recordValuationCount = 0;
        $recordStockCount = 0;

        error_log('-----------------');
        error_log('Valuation : START');
        /*
         * valuation update
         */
        $valuationEntry = $this->em->createQuery(
            'SELECT valuation.id,valuation.jobState '
            . 'FROM Valuation\\Entity\\Valuation valuation '
            . 'WHERE valuation.created <= :createdDate '
            . ' AND valuation.jobState in (:currentStatus)'
            . ' AND valuation.jobState != :notArchived'
            . ' AND valuation.archived = :archived'
        )
        ->setParameter('createdDate', $valuationDate)
        ->setParameter('currentStatus', $valuationLookupStatus)
        ->setParameter('notArchived', 'Archived')
        ->setParameter('archived', 0);

        \Utility\Debug::errorLog('$valuationEntry->getSQL()',$valuationEntry->getSQL());
        \Utility\Debug::errorLog('$valuationEntry->getParameters()',$valuationEntry->getParameters());

        $valuationResults = $valuationEntry->getArrayResult();

        if(!empty($valuationResults))
        {
            foreach($valuationResults as $valuationEntryValue)
            {
                $recordValuationCount++;

                exec(
                    "php " . $projectRoot . "public/index.php clean up database worker "
                    . $valuationEntryValue['id'] . " '" . $valuationEntryValue['jobState'] . "' valuation >>/log/php.log",
                    $output,
                    $execResult
                );
            }
        }

		error_log('Valuation : END');
		error_log('---------------');

		error_log(print_r("total valuation records " . $recordValuationCount));

		/*
		* stock update
		*/
		error_log('-----------------');
		error_log('Stock : START');

		$stockEntry = $this->em->createQuery(
			'SELECT stock.id,stock.jobState '
			. 'FROM Stock\\Entity\\Stock stock '
			. 'WHERE stock.created <= :createdDate '
			. ' AND stock.jobState != :notArchived'
			. ' AND stock.archived = :archived'
		)
			->setParameter('createdDate', $stockDate)
			->setParameter('notArchived', 'Archived')
			->setParameter('archived', 0);

        \Utility\Debug::errorLog('$stockEntry->getSQL()',$stockEntry->getSQL());
        \Utility\Debug::errorLog('$stockEntry->getParameters()',$stockEntry->getParameters());

        $stockResults = $stockEntry->getArrayResult();

		if (!empty($stockResults))
		{
			foreach ($stockResults as $stockEntryValue)
			{
				$recordStockCount++;

				exec(
					"php " . $projectRoot . "public/index.php clean up database worker "
					. $stockEntryValue['id'] . " '" . $stockEntryValue['jobState'] . "' stock >>/log/php.log",
					$output,
					$execResult
				);
			}
		}
		error_log('Stock : END');
		error_log('---------------');

		error_log(print_r("total stock records " . $recordStockCount));

		error_log('Database clean up for stock and valuation : END');
		error_log('-----------------------------------------------');

	}
                	
    /**
	 * 
	 * CRON functionality: Clean up database worker
	 * - Change the status of vehicles in valuation that have a status of "New Valuation" that were loaded before
	 * $valuationDate and change status to archived
	 * - Change the status of vehicles in stock that were loaded before $stockDate and change the status to archived
	 * @param $id
	 * @param $currentStatus
	 * @param $table
	 */
	function cleanUpStockAndValuationDatabaseWorker($id, $currentStatus, $table)
	{
        error_log('id : ' . $id);
        error_log('currentStatus : ' . $currentStatus);
        error_log('table : ' . $table);

        $updateEntity = array();

        if('valuation' == $table)
        {
            $updateEntity = $this->em->getRepository('\\Valuation\\Entity\\Valuation')
                    ->find($id);
        }

        if('stock' == $table)
        {
            $updateEntity = $this->em->getRepository('\\Stock\\Entity\\Stock')
                ->find($id);
        }

        if(!empty($updateEntity))
        {

            try{
                $updateEntity->previousState = $currentStatus;
                $updateEntity->jobState = 'Archived';
//                $updateEntity->archived = 1;
                $this->em->persist($updateEntity);
                $this->em->flush();

//                error_log($id . ' updated successfully');
            }
            catch (\Exception $e)
            {
                error_log('Error could not update ' . $table . ' with id ' . $id);
                error_log(print_r($e));
            }

        }

	}


	/**
	 * Route item to Archived state.
	 * @param \Stock\Entity\Stock $stock
	 * @param string              $previousState
	 */
	public function directRouteMoveToArchive(\Stock\Entity\Stock $stock, $previousState)
	{
		$return_value='This.Archived';
		$userId = \Utility\Registry::isAuthenticated()
			? \Utility\Registry::getAuthParam('id')
			: 0;

		if((integer)$previousState!==3)
		{
			exec("php /var/www/B4C2/public/index.php stock change 3 " . $stock->id . " $userId  > /dev/null &");
			//undo relist

			$query=$this->em->createQuery("UPDATE Auction\Entity\Auction auction SET auction.jobState='' WHERE auction.stock='".$stock->id."' ");
			$query->getResult();
		}


		return $return_value;
	}

	/**
	 * Route item to Archived state.
	 * @param \Stock\Entity\Stock $stock
	 * @param string              $previousState
	 */
	public function directRouteMoveToStock(\Stock\Entity\Stock $stock, $previousState)
	{
		$return_value='This.Stock';
		$userId = \Utility\Registry::isAuthenticated()
			? \Utility\Registry::getAuthParam('id')
			: 0;

		if((integer)$previousState!==1)
		{
			exec("php /var/www/B4C2/public/index.php stock change 1 " . $stock->id . " $userId  > /dev/null &");
			//undo relist

			$query=$this->em->createQuery("UPDATE Auction\Entity\Auction auction SET auction.jobState='' WHERE auction.stock='".$stock->id."' ");
			$query->getResult();
		}

		return $return_value;

	}


	/**
	 * ExecuteAfter: Create.
	 * Push change to mobile devices.
	 * @param array                                 $meta
	 * @param object|null                           $jobRecord
	 * @param object|null                           $record
	 * @param \Workspace\Utility\ServiceInputParams $contract
	 * @return array
	 */
	public function pushCreateAction($meta, $jobRecord, $record, \Workspace\Utility\ServiceInputParams $contract)
	{
		//-- Pass to backend function so we don't hold up the user response.
		$userId = \Utility\Registry::isAuthenticated()
			? \Utility\Registry::getAuthParam('id')
			: 0;
		exec("php /var/www/B4C2/public/index.php stock change 1 " . $record->id . " $userId  > /dev/null &");
	}

	/**
	 * ExecuteAfter: Update.
	 * Push change to mobile devices.
	 * @param array                                 $meta
	 * @param object|null                           $jobRecord
	 * @param object|null                           $record
	 * @return array
	 */
	public function pushUpdateAction($meta, $jobRecord, $record)
	{
		//-- Pass to backend function so we don't hold up the user response.
		$userId = \Utility\Registry::isAuthenticated()
			? \Utility\Registry::getAuthParam('id')
			: 0;
		exec("php /var/www/B4C2/public/index.php stock change 2 " . $record->id . " $userId  > /dev/null &");
	}

	/**
	 * ExecuteAfter: Delete.
	 * Push change to mobile devices.
	 * @param array                                 $meta
	 * @param object|null                           $jobRecord
	 * @param object|null                           $record
	 * @param \Workspace\Utility\ServiceInputParams $contract
	 * @return array
	 */
	public function pushDeleteAction($meta, $jobRecord, $record, \Workspace\Utility\ServiceInputParams $contract)
	{
		//-- Pass to backend function so we don't hold up the user response.
		$userId = \Utility\Registry::isAuthenticated()
			? \Utility\Registry::getAuthParam('id')
			: 0;
		exec("php /var/www/B4C2/public/index.php stock change 3 " . $record->id . " $userId  > /dev/null &");
	}

	/**
	 * Push change notifications for stock item change.
	 * @param $changeCode
	 * @param $id
	 * @param $skipId
	 */
	public function pushChange($changeCode, $id, $skipId)
	{
		//-- Safety check.
		$stock = $this->em->find('Stock\\Entity\\Stock', $id);

		$minDate = new \DateTime('last month');
		if ($minDate->getTimestamp() > $stock->created->getTimestamp()
		    || 8 == $stock->type->category->id
		)
		{
			//-- Nothing to do, old record.
			return;
		}

		//-- Send it to relevant people linked to the company.
		$registrations = $this->em->getRepository('Company\\Entity\\DeviceRegistration')
			->findBy(
				array(
					'company' => $stock->company->id
				)
			);
		$ids           = array();
		foreach ($registrations as $registration)
		{
			if (/*$registration->profile->id != $skipId
			    && */
				0 < strlen($registration->device->registrationId)
			)
			{
				$ids[] = $registration->device->registrationId;
			}
		}
		\Utility\Comms\Gcm::send(
			$ids,
			array(
				'changeCode' => (int)$changeCode,
				'changeId'   => (int)$stock->uvi
			)
		);

		//-- Send it to relevant people linked to the trade center.
		if ('Trade Center' == $stock->jobState && !is_null($stock->company->tradeCenter))
		{
			$registrations = $this->em->getRepository('Company\\Entity\\DeviceRegistration')
				->findBy(
					array(
						'company' => $stock->company->tradeCenter->id
					)
				);
			$ids           = array();
			foreach ($registrations as $registration)
			{
				if (0 < strlen($registration->device->registrationId)
				)
				{
					$ids[] = $registration->device->registrationId;
				}
			}
			\Utility\Comms\Gcm::send(
				$ids,
				array(
					'changeCode' => (int)$changeCode,
					'changeId'   => (int)$stock->uvi
				)
			);
		}
	}

    /**
     * CRON functionality: export stock and auction data to file and put it on GEMS ftp server
     */
    function exportDataForGems()
    {
        \Utility\Debug::errorLog('----------------','Export data for DMD start');

        ini_set('memory_limit','-1');

        /**
         * @todo
         * 1. find all users where permission is true = etpGemDmd (exportThirdPartiesGemDmd)
         * 2. loop through all users to find the company group
         * 3. run the query to export data to a file
         * 4. put the exported file on GEM DMD FTP server
         */

        $inGroupId = array();
        $fileName = 'gems-export.csv';
        $filePath = '/tmp/';
//        $filePath = '/var/www/NIRPH/Bid4Cars2Repo/data/';
        $dateAndTime =  new \DateTime("now");
        $dateAndTime->modify('-7 day');
//        $dateAndTime->modify('-500 day');
        $selectDate = $dateAndTime->format('Y-m-d H:i:s');

//        \Utility\Debug::errorLog('$selectDate',$selectDate);


        $queryResult = $this->em->createQuery(
            'SELECT profile.id as profileId,company.id as id '
            . 'FROM \User\Entity\Profile profile '
            . 'JOIN profile.override override '
            . 'LEFT JOIN profile.company company '
//            . 'LEFT JOIN company.group companyGroup '
            . 'WHERE override.etpGemDmd = true '
            . 'AND profile.archived = 0 '
            . 'AND company.archived = 0 '
//            . 'AND companyGroup.archived = 0 '
            . 'GROUP BY company.id '
        );

//        \Utility\Debug::errorLog('$queryResult->getSQL()',$queryResult->getSQL());
//        \Utility\Debug::errorLog('$queryResult->getParameters()',$queryResult->getParameters());

        $result = $queryResult->getArrayResult();


        for($i = 0; $i <= count($result); $i ++)
        {
            if(!empty($result[$i]))
            {
                $inCompanyId[] = $result[$i]['id'];
            }
        }

//        \Utility\Debug::errorLog('$inCompanyId',$inCompanyId);

        if('' != $inCompanyId)
        {
            $config = $this->em->getConfiguration();
            $config->addCustomStringFunction('GROUP_CONCAT', 'UVd\DoctrineFunction\GroupConcat');
            $config->addCustomStringFunction('DATE_FORMAT', 'UVd\DoctrineFunction\DateFormat');
            $host = \Utility\Registry::getConfigParam('Host');
            $exportQuery = $this->em->createQuery(
                'SELECT '
                . 'DATE_FORMAT(a.endDate ,\'%Y-%m-%d %H:%i:%s\') AS auction_end_date,'
                . 'c.name AS company_name, '
                . 's.registrationNumber as registration_number, '
                . 't.mmCode as mm_code, '
                . 's.stockNumber as stock_no, '
                . 'xc.name AS colour, '
                . 'y.name AS year, '
                . 's.km AS mileage, '
                . 'DATE_FORMAT(s.created ,\'%Y-%m-%d %H:%i:%s\') AS stock_date,'
                . 's.vinNumber as vin_number, '
                . 'GROUP_CONCAT(acc.name) AS accessories, '
                . 'r.name AS region, '
                . 's.tradePrice as trade_price, '
                . 's.retailPrice as retail_price, '
                . "CONCAT('" . $host . "/img/bin/', mi.filename) as main_image_url, "
                . "CONCAT('" . $host . "/img/bin/', fi.filename) as front_image_url, "
                . 'condition.name as condition_name, '
                . 's.fshNotes as fsh_notes '
                . 'FROM \Stock\Entity\Stock s '
                . 'JOIN s.auction a '
                . 'LEFT JOIN s.mainImage mi '
                . 'LEFT JOIN s.frontImage fi '
                . 'LEFT JOIN s.company c '
                . 'LEFT JOIN s.type t '
                . 'LEFT JOIN s.vehicleYear y '
                . 'LEFT JOIN s.condition condition '
                . 'LEFT JOIN c.city ct '
                . 'LEFT JOIN ct.region r '
                . 'LEFT JOIN c.group cg '
                . 'LEFT JOIN s.exteriorColour xc '
                . 'LEFT JOIN s.accessories scc '
                . 'LEFT JOIN scc.accessory acc '
                . 'WHERE a.endDate > :auctionEndDate '
                . 'AND a.jobState = :auctionStatus '
                . 'AND c.id IN (:companyId) '
                . 'GROUP BY s.id '
            );

            $exportQuery->setParameter('auctionEndDate', $selectDate);
            $exportQuery->setParameter('auctionStatus', 'Relist');
            $exportQuery->setParameter('companyId', $inCompanyId);

//            \Utility\Debug::errorLog('$exportQuery->getSQL()',$exportQuery->getSQL());
//            \Utility\Debug::errorLog('$exportQuery->getParameters()',$exportQuery->getParameters());

            $exportResults = $exportQuery->getArrayResult();

//            \Utility\Debug::errorLog('count $exportResults',count($exportResults));

            $fp = fopen($filePath.$fileName, 'w');

            fputcsv($fp, array('Auction End Date','Company Name','Registration Number','MM Code','Stock No','Colour','Year','Mileage','Stock Date','Vin Number','Accessories','Region','Trade Price','Retail Price','FSH Notes','Condition','Main Image','Front Image'),';');


            foreach($exportResults as $exportResult)
            {
                fputcsv($fp, array($exportResult['auction_end_date'] , $exportResult['company_name'] , $exportResult['registration_number'] , $exportResult['mm_code'] , $exportResult['stock_no'] , $exportResult['colour'] , $exportResult['year'] , $exportResult['mileage'] , $exportResult['stock_date'] , $exportResult['vin_number'] , $exportResult['accessories'] , $exportResult['region'] , $exportResult['trade_price'] , $exportResult['retail_price'] , $exportResult['fsh_notes'] , $exportResult['condition_name'] , $exportResult['main_image_url'] , $exportResult['front_image_url']),';');

            }

            fclose($fp);

            $ftp = new \Utility\Remote\CurlFtpUploader('waws-prod-db3-001.ftp.azurewebsites.windows.net', 'dmd-ftp', 'CQi02Y05ZsiX4NegDf8maMlde0MrefZBdcpzbaTQMtHN5Kp01tCyYiDFk7EP');

            $ftp->setSslVerifyHost(2);


            if($ftp->uploadFile($filePath.$fileName,'/uploads/b4c/'.$fileName))
            {
                \Utility\Debug::errorLog($fileName,"File uploaded");
            }
            else
            {
                \Utility\Debug::errorLog('File could not be uploaded ',$ftp->_errorNo . ' ' . $ftp->_error);
            }
        }
        \Utility\Debug::errorLog('----------------','Export data for DMD end');


    }
}