/** * Get locations grouped by Organization id * @return array */ protected function getVendors() { $organizations = $this->_em->getRepository('CoreBundle:Organization')->createQueryBuilder('org')->leftJoin('org.locations', 'l')->where('org.customer = :cust AND org.customerCode IS NOT NULL')->setParameter('cust', $this->customer->getId())->getQuery()->getResult(); $groupByOrganization = []; /** * @var Entity\Organization $org * @var Entity\OrganizationLocation $loc */ foreach ($organizations as $org) { if (!isset($groupByOrganization[$org->getCustomerCode()])) { $groupByOrganization[$org->getCustomerCode()]['org'] = $org; $groupByOrganization[$org->getCustomerCode()]['locs'] = []; } foreach ($org->getLocations() as $loc) { $groupByOrganization[$org->getCustomerCode()]['locs'][$loc->getCustomerCode()] = $loc; } } return $groupByOrganization; }
/** * 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); }
/** * @param Schema $schema */ public function up(Schema $schema) { /** * @var EntityManager $em */ $em = $this->container->get('doctrine')->getManager(); $decType = $em->getRepository('CoreBundle:DocumentType')->findOneBy(['code' => 'DeclOfConf']); if (!$decType) { $decType = new DocumentType(); $decType->setName('Declaration of Conformity')->setCode('DeclOfConf'); $em->persist($decType); } $certType = $em->getRepository('CoreBundle:DocumentType')->findOneBy(['code' => 'CertOfConf']); if (!$certType) { $certType = new DocumentType(); $certType->setName('Certificate of Conformity')->setCode('CertOfConf'); $em->persist($certType); } $customer = new Customer(); $customer->setName('LPP S.A.')->setAbbr('lpp')->setIsActive(false)->setAddress(''); $em->persist($customer); $excel = \PHPExcel_IOFactory::load(__DIR__ . '/files/documents.xlsx'); $sheets = $excel->getAllSheets(); foreach ($sheets as $sheet) { $title = $sheet->getTitle(); $documentType = preg_match('/ДС/ui', $title) ? $decType : $certType; preg_match('/\\((?P<tag>.+?)\\)/ui', $title, $matches); $tag = null; //add tag if (isset($matches['tag']) && $matches['tag']) { $tag = $em->getRepository('CoreBundle:Tag')->findOneBy(['name' => $matches['tag']]); if (!$tag) { $tag = new Tag(); $tag->setName($matches['tag']); $em->persist($tag); $em->flush(); } } $i = 0; $field = null; $mapping = []; while (($field = $sheet->getCellByColumnAndRow($i, 1)->getValue()) == true) { $mapping[$field] = $i; $i++; } $lastColumn = $i - 1; $readSheet = true; $r = 3; while ($readSheet) { $document = new Document(); $document->setCustomer($customer)->setType($documentType); if (isset($tag)) { $document->addTag($tag); } $isEmpty = false; foreach ($mapping as $field => $col) { $value = trim($sheet->getCellByColumnAndRow($col, $r)->getValue()); if (!$value && $field == 'name') { $isEmpty = true; break; } if (\PHPExcel_Shared_Date::isDateTime($sheet->getCellByColumnAndRow($col, $r))) { $value = \PHPExcel_Shared_Date::ExcelToPHPObject($value); } $method = 'set' . ucfirst($field); if ($value) { $document->{$method}($value); } } if ($isEmpty) { $readSheet = false; } else { $em->persist($document); } $r++; } } $em->flush(); }
/** * Returns array [ * [ * 'product' => Product, * 'before' => ProductStatus, * 'after' => ProductStatus, * 'user' => User, * 'customer' => Customer, * ], * ... * ] * @param \DateTime $date * @param Customer $customer * @param User $user * @param ProductsSet $productSet * @return type */ public function getForProductsByDate(\DateTime $date, Customer $customer = null, User $user = null, ProductsSet $productSet = null, ProductStatus $statusAfter = null) { $em = $this->_em; $connection = $em->getConnection(); $conditions = []; $conditionValues = []; $conditions[] = 'date = :date'; $conditionValues[':date'] = $date->format('Y-m-d'); if (!empty($user)) { $conditions[] = 'user_id = :user_id'; $conditionValues[':user_id'] = $user->getId(); } if (!empty($customer)) { $conditions[] = 'customer_id = :customer_id'; $conditionValues[':customer_id'] = $customer->getId(); } if (!empty($productSet)) { $conditions[] = 'product_set_id = :product_set_id'; $conditionValues[':product_set_id'] = $productSet->getId(); } if (!empty($statusAfter)) { $conditions[] = 'status_after_change_id = :status_after_change_id'; $conditionValues[':status_after_change_id'] = $statusAfter->getId(); } $sql = ' SELECT DISTINCT customer_id, product_id, status_before_change_id, status_after_change_id, user_id FROM statistics_product_status_changes WHERE ' . implode(' AND ', $conditions) . ' '; $stmt = $connection->prepare($sql); foreach ($conditionValues as $key => $value) { $stmt->bindValue($key, $value); } $stmt->execute(); $rows = $stmt->fetchAll(); $result = []; foreach ($rows as $row) { $result[] = ['product' => empty($row['product_id']) ? null : $em->getRepository('CoreBundle:Product')->find($row['product_id']), 'before' => empty($row['status_before_change_id']) ? null : $em->getRepository('CoreBundle:ProductStatus')->find($row['status_before_change_id']), 'after' => empty($row['status_after_change_id']) ? null : $em->getRepository('CoreBundle:ProductStatus')->find($row['status_after_change_id']), 'user' => empty($row['user_id']) ? null : $em->getRepository('UsersBundle:User')->find($row['user_id']), 'customer' => empty($row['customer_id']) ? null : $em->getRepository('CoreBundle:Customer')->find($row['customer_id'])]; } return $result; }