/**
* 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);
}
public function importShipment($shipmentId)
{
$this->_customer = $this->getCustomerByAbbr('RUSPM');
if (($shipment = $this->isShipmentExist($shipmentId)) === false) {
$shipment = new Entity\Shipment();
$shipment->setStatus($this->getStatusByCode('PBV'));
$shipment->setCustomer($this->_customer);
$shipment->setMbl($shipmentId);
} else {
$shipment->setStatus($this->getStatusByCode('UBV'));
}
$shipFrom = $this->getLocation($this->getValueByAttribute('ShipFrom', 'InternalId'));
$shipment->setShipFrom($shipFrom);
$shipment->setAtd($this->getDateByAttribute('ShipmentInfo', 'Shipped'));
$shipment->setEtd($this->getDateByAttribute('ShipmentInfo', 'EstShip'));
$shipment->setEta($this->getDateByAttribute('ShipmentInfo', 'EstDeliver'));
$shipment->setCustomInt1($this->getValueByAttribute('Measures', 'Qty', 'ShipmentInfo'));
$shipment->setCustomInt2($this->getValueByAttribute('Measures', 'Cases', 'ShipmentInfo'));
$shipmentInfo = $this->getTagByName('ShipmentInfo')->item(0);
$shipment->setCustomFloat1($this->getValueByAttribute('EstWeight', 'Value', $this->getChildElement($shipmentInfo, 'Measures')));
$shipment->setCustomFloat2($this->getValueByAttribute('EstVolume', 'Value', $this->getChildElement($shipmentInfo, 'Measures')));
$shipment->setHbl('SPM' . $shipmentId);
$pbl = strtoupper($this->getValueByAttribute('MiscInfo', 'Misc5', 'ShipmentInfo'));
$shipment->setCustomString1($pbl);
$shipment->setCustomString2($this->getValueByAttribute('MiscInfo', 'Misc8', 'ShipmentInfo'));
$shipment->setType();
#$vehicle = $this->getTagByName('Vehicle')->item(0);
$shipment->setCustomString3($this->getValueByAttribute('Vehicle', 'Id', 'ShipmentInfo'));
$shipment->setCustomString4($this->getValueByAttribute('Seal', 'Id'));
$shipment->setCustomInt3($this->getValueByAttribute('PartnerId1', 'InternalLocnId'));
$shipTo = $this->getLocation($this->getValueByAttribute('ShipTo', 'InternalId'));
$shipment->setShipTo($shipTo);
$this->_em->persist($shipment);
#Добавляем LPN
$lpns = $this->getTagByName('LPN');
foreach ($lpns as $lpn) {
#добавляем коробки
$package = $this->importPackage($lpn, $shipment);
#добавляем lineItem
$this->importShipmentLineItem($lpn, $shipment, $package);
}
$this->_po = array_values(array_unique($this->_po));
#определяем букинг
if (false === ($booking = $this->detectBooking($pbl))) {
$this->_error[] = "Booking for shipment with PBL#{$pbl} not found";
} else {
$shipment->setBooking($booking);
}
if (!empty($this->_error)) {
$subject = "Exceptions were occured in import from EEM";
@mail('ivan.popov@panalpina.com,stanislav.biryukov@panalpina.com,margarita.slyusareva@panalpina.com', $subject, implode("\n\r", $this->_error));
}
}