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