/**
  * 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');
 }