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