/** * import data for containers without ATA (shipments, line items, flows) */ private function syncContainerWithoutAta($fetchType) { $i = 0; $flowStorage = $shipmentStorage = []; //container flow $query = "SELECT c.*, so.PBL, po.Name as PoName, so.Status, SUM(soi.QTY_PCS) as QTY_PCS,\n SUM(soi.QTY_CNTS) as QTY_CNTS, SUM(soi.NetWeight) as NetWeight, SUM(soi.GrossWeight) as GrossWeight, SUM(soi.Volume) as Volume\n FROM container c\n JOIN so ON so.ContainerID = c.ID\n JOIN so_items soi ON soi.SOID = so.ID\n JOIN po ON po.ID = soi.POID\n WHERE trim(c.Name) != '' AND c.ATA = '0000-00-00'\n AND c.ShippingLine IN (:maersk, :cma, :fesco, :hmm, :apl)"; if ($fetchType == self::FETCH_TYPE_DAILY) { $query .= $this->dailyCondition; } $query .= " GROUP BY so.ID, po.ID"; $stmt = $this->pdo->prepare($query); $stmt->execute(['apl' => self::APL, 'maersk' => self::MAERSK, 'cma' => self::CMA, 'fesco' => self::FESCO, 'hmm' => self::HMM]); $subcontractorMap = [self::CMA => $this->_em->getRepository('CoreBundle:Subcontractor')->findOneBy(['code' => self::CMA_CODE]), self::MAERSK => $this->_em->getRepository('CoreBundle:Subcontractor')->findOneBy(['code' => self::MAERSK_CODE]), self::FESCO => $this->_em->getRepository('CoreBundle:Subcontractor')->findOneBy(['code' => self::FESCO_CODE]), self::HMM => $this->_em->getRepository('CoreBundle:Subcontractor')->findOneBy(['code' => self::HMM_CODE]), self::APL => $this->_em->getRepository('CoreBundle:Subcontractor')->findOneBy(['code' => self::APL_CODE])]; $uomQty = $this->_em->getRepository('CoreBundle:UOM')->findOneBy(['code' => 'PC']); $uomPlaces = $this->_em->getRepository('CoreBundle:UOM')->findOneBy(['code' => 'CT']); $uomWeight = $this->_em->getRepository('CoreBundle:UOM')->findOneBy(['code' => 'KG']); $uomVolume = $this->_em->getRepository('CoreBundle:UOM')->findOneBy(['code' => 'CBM']); $qb = $this->_em->getRepository('CoreBundle:ContainerFlow')->createQueryBuilder('cf'); $qbl = $this->_em->getRepository('CoreBundle:BookingLineItem')->createQueryBuilder('l'); $poBuilder = $this->_em->getRepository('CoreBundle:PO')->createQueryBuilder('po'); $statusCancel = $this->_em->getRepository('CoreBundle:Status')->findOneBy(['code' => 'CXL']); $statusPublished = $this->_em->getRepository('CoreBundle:Status')->findOneBy(['code' => 'PBV']); while (($row = $stmt->fetch(\PDO::FETCH_ASSOC)) == true) { $container = $this->_em->getRepository('CoreBundle:Container')->findOneBy(['name' => $row['Name']]); if (!$container) { continue; } /** @var Subcontractor $subcontractor */ $subcontractor = $subcontractorMap[$row['ShippingLine']]; if (($subcontractor->getService() && $subcontractor->getService()->getCode() == 'CONTAINERLEASING') == false) { $prefixString = substr($row['Name'], 0, 4); /** @var ContainerPrefix $prefix */ $prefix = $this->_em->getRepository('CoreBundle:ContainerPrefix')->findOneBy(['name' => $prefixString]); if (!$prefix) { $this->messageService->addMessage("Unknown prefix {$prefixString} for container {$row['Name']}", Message::ERROR); continue; } if ($prefix->getSubcontractor()->getId() != $subcontractor->getId() && ($prefix->getSubcontractor()->getService() && $prefix->getSubcontractor()->getService()->getCode() != 'CONTAINERLEASING')) { $this->messageService->addMessage("Subcontractor in spm is not equal patools for container {$row['Name']}", Message::ERROR); continue; } } $po = $poBuilder->where('po.customer = :cust AND (po.name = :po1 OR po.customText1 = :po2)')->setParameters(['cust' => $this->sportmaster->getId(), 'po1' => $row['PoName'], 'po2' => $row['PoName']])->getQuery()->getOneOrNullResult(); if (!$po) { $this->messageService->addMessage("Container name = {$row['Name']} PO {$row['PoName']} was not found, skipping", Message::ERROR); continue; } /** @var ContainerFlow $flow */ $flow = isset($flowStorage[$row['ID']]) ? $flowStorage[$row['ID']] : null; if (!$flow) { $flow = $qb->where('cf.customInt1 = :number')->setParameter('number', $row['ID'])->getQuery()->getOneOrNullResult(); //if flow has ATA, skip if ($flow && $flow->getATA()) { continue; } } if (!$flow) { $flow = new ContainerFlow(); $flow->setContainer($container)->setSubcontractor($subcontractor); } $flow->setCustomInt1($row['ID']); if (!isset($flowStorage[$row['ID']])) { $flowStorage[$row['ID']] = $flow; } $shipment = isset($shipmentStorage[$row['PBL']]) ? $shipmentStorage[$row['PBL']] : null; if (!$shipment) { $shipment = $this->_em->getRepository('CoreBundle:Shipment')->findOneBy(['hbl' => $row['PBL'], 'customer' => $this->sportmaster->getId()]); } if (!$shipment) { //if deleted, skip if ($row['Status'] == -1) { continue; } $shipment = new Shipment(); $shipment->setHbl($row['PBL'])->setCustomer($this->sportmaster); $flow->addShipment($shipment); } else { if (!isset($shipmentStorage[$row['PBL']])) { $flowShipmentLink = $this->_em->getConnection()->fetchAll("select * from container_flow_shipment where shipment_id = :id", ['id' => $shipment->getId()]); if (!$flowShipmentLink) { $flow->addShipment($shipment); } } } $mbl = trim($row['MBL']); if ($row['ShippingLine'] == self::MAERSK) { preg_match('/(\\d{9,11})/', $mbl, $matches); if (isset($matches[1])) { $mbl = $matches[1]; } } if ($shipment->getMbl() != $mbl) { $shipment->setMbl($row['MBL']); } $this->_em->persist($flow); if (!isset($shipmentStorage[$row['PBL']])) { if ($shipment->getId() && $shipment->getBooking()) { $qbl->update('CoreBundle:BookingLineItem', 'l')->set('l.status', '?1')->where('l.booking = ?2')->setParameter(1, $statusCancel->getId())->setParameter(2, $shipment->getBooking()->getId())->getQuery()->execute(); } $shipmentStorage[$row['PBL']] = $shipment; } $booking = $shipment->getBooking(); if (!$booking) { $booking = new Booking(); $booking->setCustomer($this->sportmaster)->setVendorBooking($shipment->getHbl()); $shipment->setBooking($booking); } if ($row['Status'] == -1) { $shipment->setStatus($statusCancel); $booking->setStatus($statusCancel); } else { $shipment->setStatus($statusPublished); $booking->setStatus($statusPublished); } $this->_em->persist($booking); $this->_em->persist($shipment); if ($row['Status'] == -1) { continue; } $line = null; if ($shipment->getBooking()->getId()) { $line = $this->_em->getRepository('CoreBundle:BookingLineItem')->findOneBy(['booking' => $shipment->getBooking()->getId(), 'po' => $po->getId()]); } if (!$line) { $line = new BookingLineItem(); } $line->setPlacesCount($row['QTY_CNTS'])->setQuantity($row['QTY_PCS'])->setNetWeight($row['NetWeight'])->setGrossWeight($row['GrossWeight'])->setVolume($row['Volume'])->setQuantityUOM($uomQty)->setWeightUOM($uomWeight)->setVolumeUOM($uomVolume)->setPlacesUOM($uomPlaces)->setPo($po)->setStatus($statusPublished)->setBooking($shipment->getBooking()); $this->_em->persist($line); $i++; if ($i % self::BATCH_SIZE == 0) { $this->_em->flush(); // $this->_em->clear(); } } $this->_em->flush(); $this->_em->clear(); unset($flowStorage); unset($shipmentStorage); }
protected function execute(InputInterface $input, OutputInterface $output) { $this->_em = $this->getContainer()->get('doctrine')->getManager(); $cronLog = new CronLog(); $cronLog->setStartAt(new DateTime()); $cronLog->setDescription($this->getDescription()); $cronLog->setScript($this->getName()); $qb = $this->_em->getRepository('CoreBundle:ContainerFlow')->createQueryBuilder('cf'); /** * @var ContainerFlow[] $flows */ $flows = $qb->innerJoin('cf.subcontractor', 'sub')->innerJoin('cf.container', 'container')->where('sub.code IN (:maersk, :cma, :fesco, :hmm, :apl) AND cf.ata IS NULL AND (cf.customDate1 IS NULL OR cf.customDate1 != :now)')->setMaxResults(self::SINGLE_FETCH)->setParameter('maersk', self::MAERSK_CODE)->setParameter('cma', self::CMA_CODE)->setParameter('fesco', self::FESCO_CODE)->setParameter('hmm', self::HMM_CODE)->setParameter('apl', self::APL_CODE)->setParameter('now', date('Y-m-d'))->getQuery()->getResult(); foreach ($flows as $flow) { $parser = $this->getParserForCarrier($flow->getSubcontactor()->getCode()); $classParts = explode('\\', get_class($parser)); $service = array_pop($classParts); try { $result = $parser->getData($flow->getContainer()->getName()); //try to find by bill of landing for CmaCgm & HMM if (is_null($result) && in_array($service, ['CmaCgm', 'HMM', 'MaerskClassic'])) { $mbl = ''; foreach ($flow->getShipments() as $shipment) { $mbl = $shipment->getMbl(); if ($mbl) { $result = $parser->getData($mbl, ExternalTruckingAbstract::SEARCH_BOL); break; } } } if ($result) { if ($result->getBillOfLanding()) { $this->checkMBL($result->getBillOfLanding(), $flow); } if ($result->getEta()) { $flow->setETA(new DateTime($result->getEta())); } if ($result->getAta()) { $flow->setATA(new DateTime($result->getAta())); } if ($result->getLoadOnVesselAt()) { $flow->setLoadOnVesselAt(new DateTime($result->getLoadOnVesselAt())); } foreach ($result->getEvents() as $event) { $vehicle = $this->addVehicle($event->getVessel(), $flow->getSubcontactor()); $flowEvent = $this->_em->getRepository('CoreBundle:ContainerFlowEvent')->findOneBy(['containerFlow' => $flow->getId(), 'details' => $event->getStatus(), 'locationDetails' => $event->getLocation(), 'occurredAt' => new DateTime($event->getDate())]); if (!$flowEvent) { $flowEvent = new ContainerFlowEvent(); $flowEvent->setContainerFlow($flow); } $flowEvent->setDetails(strtolower($event->getStatus())); $flowEvent->setLocationDetails($event->getLocation()); $flowEvent->setOccurredAt(new DateTime($event->getDate())); $flowEvent->setVehicle($vehicle); $flowEvent->setVoyage($event->getVoyage()); $this->_em->persist($flowEvent); $this->_em->flush(); } } else { $this->messageService->addMessage("Service {$service}: Container data not found for {$flow->getContainer()->getName()}", Message::ERROR); } } catch (\Exception $e) { $this->messageService->addMessage("Service {$service}, container {$flow->getContainer()->getName()}: {$e->getMessage()}; file {$e->getFile()}; line {$e->getLine()}", Message::ERROR); } //set date of last check, next check will be tomorrow $flow->setCustomDate1(new DateTime('now')); $this->_em->persist($flow); $this->_em->flush(); } if ($this->messageService->hasCategory(Message::ERROR)) { $message = \Swift_Message::newInstance()->setSubject('Container flow external trucking update')->setFrom('*****@*****.**')->setTo(['*****@*****.**', '*****@*****.**'])->setBody($this->messageService->dumpAsHtml(), 'text/html'); $this->getContainer()->get('mailer')->send($message); $this->getContainer()->get('swiftmailer.command.spool_send')->run(new ArgvInput(array()), new ConsoleOutput()); } $cronLog->setEndAt(new DateTime()); $this->_em->persist($cronLog); $this->_em->flush(); $output->writeln('Completed'); }