2 namespace Stock\Service;
11 class Stock extends \Stock\DataBin\Stock
16 * Initialize a new item.
17 * @param \Stock\Entity\Stock $stock
19 public function initNewItem(\Stock\Entity\Stock $stock) { }
22 * Route newly added item to initial state.
23 * @param \Stock\Entity\Stock $stock
24 * @param string|null $previousState
25 * @param array|null $routingData
28 public function routeNewItem(\Stock\Entity\Stock $stock, $previousState, $routingData)
30 if (\Utility\Registry::checkOnce('NewStockItem.FromValuation'))
32 return 'This.Valuation';
35 'Destination' => \Utility\Registry::checkOnce('NewStockItem.Status', 'This.Stock'),
36 'Data' => \Utility\Registry::checkOnce('Service.Stock.Context', array())
41 * Route to Retail and set reset time.
42 * @param \Stock\Entity\Stock $stock
43 * @param string $previousState
45 public function directRouteSendToRetail(\Stock\Entity\Stock $stock, $previousState)
47 $stock->reset = new \DateTime(date('Y-m-d H:i:s', time() + 86400));
54 * Route to Trade Centre
55 * @param \Stock\Entity\Stock $stock
56 * @param string $previousState
58 public function directRouteSendToTradeCenter(\Stock\Entity\Stock $stock, $previousState)
61 //Write hit to Stats logs (need stock or valuation stock id and profile id)
62 $today = new \DateTime();
64 //\Utility\Debug::errorLog('directRouteSendToTradeCenter new', $test);
66 $em = \Utility\Registry::getEntityManager();
67 $loghit = new \Statistical\Entity\Statistical();
69 'stock' => $em->getRepository('\Stock\Entity\Stock')->find($stock->id),
71 'dealnotdone' => false,
72 'tradecentre' => true,
73 'fromProfile' => $em->getRepository('\User\Entity\Profile')->find($stock->createdBy->id),
77 $loghit->fromArray($loghitData);
78 $em->persist($loghit);
81 return 'This.TradeCenter';
85 * Route to Retail and set reset time..
86 * @param \Stock\Entity\Stock $stock
87 * @param string $previousState
89 public function directRouteReclaimFromRetail(\Stock\Entity\Stock $stock, $previousState)
97 * Route item that is received back from Auction Workflow.
98 * @param \Stock\Entity\Stock $stock
99 * @param string $previousState
100 * @param array|null $routingData
102 public function routeAuctionHandover(\Stock\Entity\Stock $stock, $previousState, $routingData)
104 if ('Sold' == $stock->auction->jobState
105 || (time() > $stock->auction->endDate->getTimestamp()
106 && $stock->auction->reservePrice < $stock->auction->currentBidPrice && 'Undone' != $stock->auction->jobState))
112 if ($stock->previousState == 'Trade Center')
114 $stock->previousState = 'Auction';
116 return 'This.TradeCenter';
120 $stock->previousState = 'Auction';
127 * ExecuteAfter: Create.
128 * Add item to queue for mobile.
130 * @param object|null $jobRecord
131 * @param object|null $record
132 * @param \Workspace\Utility\ServiceInputParams $contract
135 public function generateHistoryList($meta, $jobRecord, $record, \Workspace\Utility\ServiceInputParams $contract)
137 $record = null == $jobRecord
139 : $jobRecord->toArray();
141 $StockID = $record['id'];
142 $stockTypeID = $record['type'];
144 #-> Clear any existing history for this vehicle.
145 $this->em->createQuery(
146 'DELETE FROM \Stock\Entity\PricingHistory pricingHistory '
147 . 'WHERE pricingHistory.stockId = '
152 #-> Establish history.
153 $result = $this->em->createQuery(
154 'SELECT stock, st, vy '
155 . 'FROM \Stock\Entity\Stock stock '
156 . 'LEFT JOIN stock.type st '
157 . 'LEFT JOIN stock.vehicleYear vy '
158 . 'WHERE stock.id = :StockID '
160 ->setParameter('StockID', $StockID)
163 $stockTypeID = $result[0]['type']['id'];
164 $vehicleYearID = $result[0]['vehicleYear']['id'];
167 $today = new \DateTime();
168 $dateFrom = clone $today;
169 $dateFrom->modify('-3 months');
171 $result = $this->em->createQuery(
172 'SELECT stock, priceguide, auction, st, po, cb, bids '
173 . 'FROM \Stock\Entity\Stock stock '
174 . 'LEFT JOIN stock.priceGuide priceguide WITH priceguide.created BETWEEN :dateFrom AND :dateTo '
175 . 'LEFT JOIN stock.auction auction WITH auction.created BETWEEN :dateFrom AND :dateTo '
176 . 'LEFT JOIN stock.type st '
177 . 'LEFT JOIN priceguide.offers po '
178 . 'LEFT JOIN auction.currentBid cb '
179 . 'LEFT JOIN auction.bids bids '
180 . 'WHERE st.id = :stockTypeID '
181 . 'AND IDENTITY(stock.vehicleYear) = :vehicleYearID '
182 . 'AND (stock.highestBid > 0 OR stock.highestOffer > 0) '
183 . 'AND stock.archived = 0 '
184 . 'ORDER by auction.created, priceguide.created ASC '
186 ->setParameter('stockTypeID', $stockTypeID)
187 ->setParameter('vehicleYearID', $vehicleYearID)
188 ->setParameter('dateFrom', $dateFrom)
189 ->setParameter('dateTo', $today)
195 foreach ($result as $pricingHistory)
197 $StockDamageTotal = $pricingHistory['damageTotal'];
198 $StockTradePrice = $pricingHistory['tradePrice'];
199 $StockListPrice = $pricingHistory['listPrice'];
200 $StockKMS = $pricingHistory['km'];
201 if ($StockKMS == '' || is_null($StockKMS))
206 $PriceguideHighestOffer = $pricingHistory['highestOffer'];
208 $highestBid = $pricingHistory['highestBid'];
210 // Priceguide && Auction
211 if ($PriceguideHighestOffer != 0 && $highestBid != 0 )
213 $priceGuideOffers = $pricingHistory['priceGuide']['offers'];
214 if (is_array($priceGuideOffers))
217 foreach ($priceGuideOffers as $priceGuideOffer)
219 if ($priceGuideOffer['amount'] > $highestAmnt)
221 $highestAmnt = $priceGuideOffer['amount'];
222 $priceGuideOffersCreatedDate = $priceGuideOffer['created'];
227 'date' => $priceGuideOffersCreatedDate,
228 'trade' => $StockTradePrice,
229 'listPrice' => $StockListPrice,
230 'totalRecon' => $StockDamageTotal,
232 'pricegOffer' => $PriceguideHighestOffer,
233 'auctionWinningBid' => $highestBid,
234 'stockId' => $StockID
236 $hash = md5(serialize($data));
237 if (!isset($existing[$hash]))
239 $existing[$hash] = true;
240 $entry = new \Stock\Entity\PricingHistory();
241 $entry->fromArray($data);
242 $this->em->persist($entry);
243 $this->em->flush($entry);
249 if ($PriceguideHighestOffer != 0 && $highestBid == 0 )
251 $priceGuideOffers = $pricingHistory['priceGuide']['offers'];
252 if (is_array($priceGuideOffers))
255 foreach ($priceGuideOffers as $priceGuideOffer)
257 if ($priceGuideOffer['amount'] > $highestAmnt)
259 $highestAmnt = $priceGuideOffer['amount'];
260 $priceGuideOffersCreatedDate = $priceGuideOffer['created'];
264 'date' => $priceGuideOffersCreatedDate, // changes all the time
265 'trade' => $StockTradePrice,
266 'listPrice' => $StockListPrice,
267 'totalRecon' => $StockDamageTotal,
269 'pricegOffer' => $PriceguideHighestOffer,
270 'auctionWinningBid' => 0.0,
271 'stockId' => $StockID
274 $hash = md5(serialize($data));
275 if (!isset($existing[$hash]))
277 $existing[$hash] = true;
278 $entry = new \Stock\Entity\PricingHistory();
279 $entry->fromArray($data);
280 $this->em->persist($entry);
281 $this->em->flush($entry);
288 if ($PriceguideHighestOffer == 0 && $highestBid != 0)
290 $date = $pricingHistory['auction']['created'];
292 'date' => $date, // changes all the time
293 'trade' => $StockTradePrice,
294 'listPrice' => $StockListPrice,
295 'totalRecon' => $StockDamageTotal,
297 'pricegOffer' => 0.0,
298 'auctionWinningBid' => $highestBid,
299 'stockId' => $StockID
301 $hash = md5(serialize($data));
302 if (!isset($existing[$hash]))
304 $existing[$hash] = true;
305 $entry = new \Stock\Entity\PricingHistory();
306 $entry->fromArray($data);
307 $this->em->persist($entry);
308 $this->em->flush($entry);
320 public function updateCustomVehicles($meta, $jobRecord, $contract, $data)
322 $contract->data = $this->addCustomVehicles($meta, $contract->data);
323 return $contract->data;
327 * ExecuteBefore: Create
331 public function addCustomVehicles($meta, $data)
333 #-> Check if we have a custom vehicle
334 if ($data->Stock['category'] == '8')
336 #-> Get latest version.
337 $versions = $this->em->createQuery(
338 'SELECT MAX(vehicleType.createVersion) AS createVersion,'
339 . ' MAX(vehicleType.updateVersion) AS updateVersion '
340 . 'FROM Stock\\Entity\\Type vehicleType'
343 $newVersion = $versions[0]['createVersion'] > $versions[0]['updateVersion']
344 ? $versions[0]['createVersion'] + 1
345 : $versions[0]['updateVersion'] + 1;
347 #-> Create make, model, type entries
348 $makeManual = $data->Stock['makeManual'];
349 $modelManual = $data->Stock['modelManual'];
350 $typeManual = $data->Stock['typeManual'];
351 $category = $data->Stock['category'];
352 $today = new \DateTime();
353 $em = \Utility\Registry::getEntityManager();
355 error_log('Make: ' . $data->Stock['make'] . ' New: ' . $makeManual . "/n");
356 error_log('Model: ' . $data->Stock['model'] . ' New: ' . $modelManual . "/n");
357 error_log('Type: ' . $data->Stock['type'] . ' New: ' . $typeManual . "/n");
359 // Do a check if this make exists
360 if ('' != $makeManual && $makeManual != $data->Stock['make'])
362 $make = $em->getRepository('\Stock\Entity\Make')
364 'name' => $makeManual
369 // Else add Make Manual
370 $make = new \Stock\Entity\Make();
372 'name' => $makeManual,
374 'createVersion' => $newVersion
377 $make->fromArray($makeData);
386 $makeId = $data->Stock['make'];
391 if ('' != $modelManual && $modelManual != $data->Stock['model'])
393 // Do a check if this model exists
394 $model = $em->getRepository('\Stock\Entity\Model')
397 'name' => $makeManual
403 $model = new \Stock\Entity\Model();
405 'make' => $em->getRepository('\Stock\Entity\Make')->find($makeId),
406 'name' => $modelManual,
408 'createVersion' => $newVersion
412 $model->fromArray($modelData);
413 $em->persist($model);
417 $modelId = $model->id;
421 $modelId = $data->Stock['model'];
426 //\Utility\Debug::errorLog('Stock $typeManual', $typeManual);
427 if ('' != $typeManual && $typeManual != $data->Stock['type'])
429 // Do a check if this type exists
430 $type = $em->getRepository('\Stock\Entity\Type')
433 'category' => $category,
434 'name' => $typeManual
440 $type = new \Stock\Entity\Type();
442 'model' => $em->getRepository('\Stock\Entity\Model')->find($modelId),
443 'category' => $em->getRepository('\Stock\Entity\Category')->find($category),
444 'name' => $typeManual,
446 'introYear' => $em->getRepository('\Stock\Entity\Year')->find(1),
449 'createVersion' => $newVersion
453 $type->fromArray($typeData);
460 //\Utility\Debug::errorLog('Stock $typeId 1', $typeId);
461 #-> Update data with new ids
462 $data->Stock['type'] = $typeId;
467 //\Utility\Debug::errorLog('Stock typeID 2', $data->Stock['type']);
472 * CRON functionality: Upload stock images.
475 public function uploadImages()
478 $stack = $this->em->getRepository('Stock\\Entity\\StockImages')
492 $image = new \Utility\Upload\Image(array(), false);
493 foreach ($stack as $item)
495 $image = new \Utility\Upload\Image(array(), false);
497 foreach ($fields as $fieldBase)
499 $urlField = $fieldBase . 'Url';
500 if (is_null($item->$fieldBase)
501 && !is_null($item->$urlField)
502 && 0 < strlen($item->$urlField))
504 $downloaded = $image->externalDownload($item->$urlField);
507 $ref = $this->em->getReference('Utility\\Entity\\Image', $downloaded->id);
508 $item->$fieldBase = $ref;
509 $item->stock->$fieldBase = $ref;
519 * CRON functionality: Clean up database
520 * - Change the status of vehicles in valuation that have a status of "New Valuation" that were loaded before $valuationDate and change status to archived
521 * - Change the status of vehicles in stock that were loaded before $stockDate and change the status to archived
522 * @param $valuationDate
525 function cleanUpStockAndValuationDatabase($valuationDate,$stockDate)
527 error_log('-------------------------------------------------');
528 error_log('Database clean up for stock and valuation : START');
531 * variable declaration
533 // $valuationLookupStatus = 'New Valuation,Pending Valuation,Complete Valuation,Stock';
535 $valuationLookupStatus = array('New Valuation','Pending Valuation','Complete Valuation','Stock');
537 $projectRoot = \Utility\Registry::getConfigParam('ProjectRootPath');
540 $recordValuationCount = 0;
541 $recordStockCount = 0;
543 error_log('-----------------');
544 error_log('Valuation : START');
548 $valuationEntry = $this->em->createQuery(
549 'SELECT valuation.id,valuation.jobState '
550 . 'FROM Valuation\\Entity\\Valuation valuation '
551 . 'WHERE valuation.created <= :createdDate '
552 . ' AND valuation.jobState in (:currentStatus)'
553 . ' AND valuation.jobState != :notArchived'
554 . ' AND valuation.archived = :archived'
556 ->setParameter('createdDate', $valuationDate)
557 ->setParameter('currentStatus', $valuationLookupStatus)
558 ->setParameter('notArchived', 'Archived')
559 ->setParameter('archived', 0);
561 \Utility\Debug::errorLog('$valuationEntry->getSQL()',$valuationEntry->getSQL());
562 \Utility\Debug::errorLog('$valuationEntry->getParameters()',$valuationEntry->getParameters());
564 $valuationResults = $valuationEntry->getArrayResult();
566 if(!empty($valuationResults))
568 foreach($valuationResults as $valuationEntryValue)
570 $recordValuationCount++;
573 "php " . $projectRoot . "public/index.php clean up database worker "
574 . $valuationEntryValue['id'] . " '" . $valuationEntryValue['jobState'] . "' valuation >>/log/php.log",
581 error_log('Valuation : END');
582 error_log('---------------');
584 error_log(print_r("total valuation records " . $recordValuationCount));
589 error_log('-----------------');
590 error_log('Stock : START');
592 $stockEntry = $this->em->createQuery(
593 'SELECT stock.id,stock.jobState '
594 . 'FROM Stock\\Entity\\Stock stock '
595 . 'WHERE stock.created <= :createdDate '
596 . ' AND stock.jobState != :notArchived'
597 . ' AND stock.archived = :archived'
599 ->setParameter('createdDate', $stockDate)
600 ->setParameter('notArchived', 'Archived')
601 ->setParameter('archived', 0);
603 \Utility\Debug::errorLog('$stockEntry->getSQL()',$stockEntry->getSQL());
604 \Utility\Debug::errorLog('$stockEntry->getParameters()',$stockEntry->getParameters());
606 $stockResults = $stockEntry->getArrayResult();
608 if (!empty($stockResults))
610 foreach ($stockResults as $stockEntryValue)
615 "php " . $projectRoot . "public/index.php clean up database worker "
616 . $stockEntryValue['id'] . " '" . $stockEntryValue['jobState'] . "' stock >>/log/php.log",
622 error_log('Stock : END');
623 error_log('---------------');
625 error_log(print_r("total stock records " . $recordStockCount));
627 error_log('Database clean up for stock and valuation : END');
628 error_log('-----------------------------------------------');
634 * CRON functionality: Clean up database worker
635 * - Change the status of vehicles in valuation that have a status of "New Valuation" that were loaded before
636 * $valuationDate and change status to archived
637 * - Change the status of vehicles in stock that were loaded before $stockDate and change the status to archived
639 * @param $currentStatus
642 function cleanUpStockAndValuationDatabaseWorker($id, $currentStatus, $table)
644 error_log('id : ' . $id);
645 error_log('currentStatus : ' . $currentStatus);
646 error_log('table : ' . $table);
648 $updateEntity = array();
650 if('valuation' == $table)
652 $updateEntity = $this->em->getRepository('\\Valuation\\Entity\\Valuation')
656 if('stock' == $table)
658 $updateEntity = $this->em->getRepository('\\Stock\\Entity\\Stock')
662 if(!empty($updateEntity))
666 $updateEntity->previousState = $currentStatus;
667 $updateEntity->jobState = 'Archived';
668 // $updateEntity->archived = 1;
669 $this->em->persist($updateEntity);
672 // error_log($id . ' updated successfully');
674 catch (\Exception $e)
676 error_log('Error could not update ' . $table . ' with id ' . $id);
677 error_log(print_r($e));
687 * Route item to Archived state.
688 * @param \Stock\Entity\Stock $stock
689 * @param string $previousState
691 public function directRouteMoveToArchive(\Stock\Entity\Stock $stock, $previousState)
693 $return_value='This.Archived';
694 $userId = \Utility\Registry::isAuthenticated()
695 ? \Utility\Registry::getAuthParam('id')
698 if((integer)$previousState!==3)
700 exec("php /var/www/namibia/public/index.php stock change 3 " . $stock->id . " $userId > /dev/null &");
703 $query=$this->em->createQuery("UPDATE Auction\Entity\Auction auction SET auction.jobState='' WHERE auction.stock='".$stock->id."' ");
708 return $return_value;
712 * Route item to Archived state.
713 * @param \Stock\Entity\Stock $stock
714 * @param string $previousState
716 public function directRouteMoveToStock(\Stock\Entity\Stock $stock, $previousState)
718 $return_value='This.Stock';
719 $userId = \Utility\Registry::isAuthenticated()
720 ? \Utility\Registry::getAuthParam('id')
723 if((integer)$previousState!==1)
725 exec("php /var/www/namibia/public/index.php stock change 1 " . $stock->id . " $userId > /dev/null &");
728 $query=$this->em->createQuery("UPDATE Auction\Entity\Auction auction SET auction.jobState='' WHERE auction.stock='".$stock->id."' ");
732 return $return_value;
739 * ExecuteAfter: Create.
740 * Push change to mobile devices.
742 * @param object|null $jobRecord
743 * @param object|null $record
744 * @param \Workspace\Utility\ServiceInputParams $contract
747 public function pushCreateAction($meta, $jobRecord, $record, \Workspace\Utility\ServiceInputParams $contract)
749 //-- Pass to backend function so we don't hold up the user response.
750 $userId = \Utility\Registry::isAuthenticated()
751 ? \Utility\Registry::getAuthParam('id')
753 exec("php /var/www/namibia/public/index.php stock change 1 " . $record->id . " $userId > /dev/null &");
757 * ExecuteAfter: Update.
758 * Push change to mobile devices.
760 * @param object|null $jobRecord
761 * @param object|null $record
764 public function pushUpdateAction($meta, $jobRecord, $record)
766 //-- Pass to backend function so we don't hold up the user response.
767 $userId = \Utility\Registry::isAuthenticated()
768 ? \Utility\Registry::getAuthParam('id')
770 exec("php /var/www/namibia/public/index.php stock change 2 " . $record->id . " $userId > /dev/null &");
774 * ExecuteAfter: Delete.
775 * Push change to mobile devices.
777 * @param object|null $jobRecord
778 * @param object|null $record
779 * @param \Workspace\Utility\ServiceInputParams $contract
782 public function pushDeleteAction($meta, $jobRecord, $record, \Workspace\Utility\ServiceInputParams $contract)
784 //-- Pass to backend function so we don't hold up the user response.
785 $userId = \Utility\Registry::isAuthenticated()
786 ? \Utility\Registry::getAuthParam('id')
788 exec("php /var/www/namibia/public/index.php stock change 3 " . $record->id . " $userId > /dev/null &");
792 * Push change notifications for stock item change.
797 public function pushChange($changeCode, $id, $skipId)
800 $stock = $this->em->find('Stock\\Entity\\Stock', $id);
802 $minDate = new \DateTime('last month');
803 if ($minDate->getTimestamp() > $stock->created->getTimestamp()
804 || 8 == $stock->type->category->id
807 //-- Nothing to do, old record.
811 //-- Send it to relevant people linked to the company.
812 $registrations = $this->em->getRepository('Company\\Entity\\DeviceRegistration')
815 'company' => $stock->company->id
819 foreach ($registrations as $registration)
821 if (/*$registration->profile->id != $skipId
823 0 < strlen($registration->device->registrationId)
826 $ids[] = $registration->device->registrationId;
829 \Utility\Comms\Gcm::send(
832 'changeCode' => (int)$changeCode,
833 'changeId' => (int)$stock->uvi
837 //-- Send it to relevant people linked to the trade center.
838 if ('Trade Center' == $stock->jobState && !is_null($stock->company->tradeCenter))
840 $registrations = $this->em->getRepository('Company\\Entity\\DeviceRegistration')
843 'company' => $stock->company->tradeCenter->id
847 foreach ($registrations as $registration)
849 if (0 < strlen($registration->device->registrationId)
852 $ids[] = $registration->device->registrationId;
855 \Utility\Comms\Gcm::send(
858 'changeCode' => (int)$changeCode,
859 'changeId' => (int)$stock->uvi
866 * CRON functionality: export stock and auction data to file and put it on GEMS ftp server
868 function exportDataForGems()
870 \Utility\Debug::errorLog('----------------','Export data for DMD start');
872 ini_set('memory_limit','-1');
876 * 1. find all users where permission is true = etpGemDmd (exportThirdPartiesGemDmd)
877 * 2. loop through all users to find the company group
878 * 3. run the query to export data to a file
879 * 4. put the exported file on GEM DMD FTP server
882 $inGroupId = array();
883 $fileName = 'gems-export.csv';
885 // $filePath = '/var/www/NIRPH/Bid4Cars2Repo/data/';
886 $dateAndTime = new \DateTime("now");
887 $dateAndTime->modify('-7 day');
888 // $dateAndTime->modify('-500 day');
889 $selectDate = $dateAndTime->format('Y-m-d H:i:s');
891 // \Utility\Debug::errorLog('$selectDate',$selectDate);
894 $queryResult = $this->em->createQuery(
895 'SELECT profile.id as profileId,company.id as id '
896 . 'FROM \User\Entity\Profile profile '
897 . 'JOIN profile.override override '
898 . 'LEFT JOIN profile.company company '
899 // . 'LEFT JOIN company.group companyGroup '
900 . 'WHERE override.etpGemDmd = true '
901 . 'AND profile.archived = 0 '
902 . 'AND company.archived = 0 '
903 // . 'AND companyGroup.archived = 0 '
904 . 'GROUP BY company.id '
907 // \Utility\Debug::errorLog('$queryResult->getSQL()',$queryResult->getSQL());
908 // \Utility\Debug::errorLog('$queryResult->getParameters()',$queryResult->getParameters());
910 $result = $queryResult->getArrayResult();
913 for($i = 0; $i <= count($result); $i ++)
915 if(!empty($result[$i]))
917 $inCompanyId[] = $result[$i]['id'];
921 // \Utility\Debug::errorLog('$inCompanyId',$inCompanyId);
923 if('' != $inCompanyId)
925 $config = $this->em->getConfiguration();
926 $config->addCustomStringFunction('GROUP_CONCAT', 'UVd\DoctrineFunction\GroupConcat');
927 $config->addCustomStringFunction('DATE_FORMAT', 'UVd\DoctrineFunction\DateFormat');
928 $host = \Utility\Registry::getConfigParam('Host');
929 $exportQuery = $this->em->createQuery(
931 . 'DATE_FORMAT(a.endDate ,\'%Y-%m-%d %H:%i:%s\') AS auction_end_date,'
932 . 'c.name AS company_name, '
933 . 's.registrationNumber as registration_number, '
934 . 't.mmCode as mm_code, '
935 . 's.stockNumber as stock_no, '
936 . 'xc.name AS colour, '
938 . 's.km AS mileage, '
939 . 'DATE_FORMAT(s.created ,\'%Y-%m-%d %H:%i:%s\') AS stock_date,'
940 . 's.vinNumber as vin_number, '
941 . 'GROUP_CONCAT(acc.name) AS accessories, '
942 . 'r.name AS region, '
943 . 's.tradePrice as trade_price, '
944 . 's.retailPrice as retail_price, '
945 . "CONCAT('" . $host . "/img/bin/', mi.filename) as main_image_url, "
946 . "CONCAT('" . $host . "/img/bin/', fi.filename) as front_image_url, "
947 . 'condition.name as condition_name, '
948 . 's.fshNotes as fsh_notes '
949 . 'FROM \Stock\Entity\Stock s '
950 . 'JOIN s.auction a '
951 . 'LEFT JOIN s.mainImage mi '
952 . 'LEFT JOIN s.frontImage fi '
953 . 'LEFT JOIN s.company c '
954 . 'LEFT JOIN s.type t '
955 . 'LEFT JOIN s.vehicleYear y '
956 . 'LEFT JOIN s.condition condition '
957 . 'LEFT JOIN c.city ct '
958 . 'LEFT JOIN ct.region r '
959 . 'LEFT JOIN c.group cg '
960 . 'LEFT JOIN s.exteriorColour xc '
961 . 'LEFT JOIN s.accessories scc '
962 . 'LEFT JOIN scc.accessory acc '
963 . 'WHERE a.endDate > :auctionEndDate '
964 . 'AND a.jobState = :auctionStatus '
965 . 'AND c.id IN (:companyId) '
969 $exportQuery->setParameter('auctionEndDate', $selectDate);
970 $exportQuery->setParameter('auctionStatus', 'Relist');
971 $exportQuery->setParameter('companyId', $inCompanyId);
973 // \Utility\Debug::errorLog('$exportQuery->getSQL()',$exportQuery->getSQL());
974 // \Utility\Debug::errorLog('$exportQuery->getParameters()',$exportQuery->getParameters());
976 $exportResults = $exportQuery->getArrayResult();
978 // \Utility\Debug::errorLog('count $exportResults',count($exportResults));
980 $fp = fopen($filePath.$fileName, 'w');
982 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'),';');
985 foreach($exportResults as $exportResult)
987 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']),';');
993 $ftp = new \Utility\Remote\CurlFtpUploader('waws-prod-db3-001.ftp.azurewebsites.windows.net', 'dmd-ftp', 'CQi02Y05ZsiX4NegDf8maMlde0MrefZBdcpzbaTQMtHN5Kp01tCyYiDFk7EP');
995 $ftp->setSslVerifyHost(2);
998 if($ftp->uploadFile($filePath.$fileName,'/uploads/b4c/'.$fileName))
1000 \Utility\Debug::errorLog($fileName,"File uploaded");
1004 \Utility\Debug::errorLog('File could not be uploaded ',$ftp->_errorNo . ' ' . $ftp->_error);
1007 \Utility\Debug::errorLog('----------------','Export data for DMD end');