public function __construct(Container $container)
{
parent::__construct($container);
$this->_phpexcel = new \PHPExcel();
$this->_sheet = $this->_phpexcel->getActiveSheet();
//посчитаем размер строчек в TO, нужно для определения размера Merge колонок
foreach ($this->header as $key => $value) {
if (strstr($key, 'to_')) {
$this->_sizeTO++;
}
}
}
public function uploadAction(Request $request)
{
$form = $this->createForm(new Report\Upload());
$form->handleRequest($request);
$error = null;
$truckOrder = null;
if ($request->isMethod("POST")) {
if ($form->isValid()) {
foreach ($request->files as $uploadedFile) {
$file = $uploadedFile['file'];
$filename = $file->getClientOriginalName();
$file->move(Queue::getUploadReportDir(), $filename);
break;
}
$filepath = Queue::getUploadReportDir() . '/' . $filename;
$this->objExcel = \PHPExcel_IOFactory::load($filepath);
$this->sheet = $this->objExcel->getActiveSheet();
//загружаем в БД данные
$type = $request->request->get('uploadForm')['documentType'];
$repository = $this->getDoctrine()->getRepository('AppTruckingBundle:TruckOrder');
$pudRepository = $this->getDoctrine()->getRepository('AppTruckingBundle:Pud');
$em = $this->getDoctrine()->getManager();
if ($type == 'truck') {
$eta = array();
$atd = array();
$message = array();
foreach ($this->sheet->getRowIterator() as $row) {
if ($row->getRowIndex() < self::START_ROWS) {
continue;
}
$pudId = $this->getCellValue('B', $row->getRowIndex());
try {
$rowIndex = $row->getRowIndex();
$truckId = $this->getCellValue('D', $row->getRowIndex());
$em->getConnection()->beginTransaction();
// в случае объединения ячеек не нужно искать и обновлять TruckOrder
if (is_null($truckOrder) || is_object($truckOrder) && $truckOrder->getId() != $truckId) {
//данная переменная нужна для того чтобы обозначить границы TO
$trackOrderStartAt = $row->getRowIndex();
$truckOrder = $repository->find($truckId);
if (!$truckOrder) {
$logger = $this->get('logger');
$error = "TruckOrder {$truckId} not found";
$logger->error($error);
break;
}
#$truckOrder = new Entity\TruckOrder();
$truckOrder->setVehiclePlate($this->getCellValue('W', $rowIndex, $trackOrderStartAt));
$truckOrder->setDriver($this->getCellValue('X', $rowIndex, $trackOrderStartAt));
$truckOrder->setPhone($this->getCellValue('Y', $rowIndex, $trackOrderStartAt));
//$truckOrder->setVehicleWorkingHours($this->getCellValue('AC', $rowIndex));
$truckOrder->setDemurage($this->getCellValue('AE', $rowIndex, $trackOrderStartAt));
$truckOrder->setActualCost($this->getCellValue('AI', $rowIndex, $trackOrderStartAt));
$truckOrder->setCarrierInvoice($this->getCellValue('AJ', $rowIndex, $trackOrderStartAt));
//change status if fileds not empty
if ($truckOrder->getStatus()->getName() == 'Planned' && strlen($truckOrder->getDriver()) && strlen($truckOrder->getVehiclePlate())) {
$truckOrder->setStatus($this->getStatus('Confirmed'));
}
if (!empty($eta) && !empty($atd)) {
$this->updateWorkingHours($eta, $atd);
$eta = array();
$atd = array();
}
}
$pud = $pudRepository->find($pudId);
if (!$pud) {
$logger = $this->get('logger');
$error = "PUD {$pudId} not found";
$logger->error($error);
break;
}
$pudsInTruck = $pudRepository->findByTruckOrder($truckOrder->getId());
$cost = $this->getAvgCost($truckOrder, count($pudsInTruck));
//обновляем цены в PUD
$pud->setActualCost($cost['actualCost']);
$pud->setActualSecurityCost($cost['actualSecurityCost']);
$pud->setEstimatedCost($cost['estimatedCost']);
$pud->setEstimatedSecurityCost($cost['estimatedSecurityCost']);
//загружаем остальную информацию из Excel
$shipperAta = $this->renderDateString($this->getCellValue('Z', $rowIndex));
$consigneeAtd = $this->renderDateString($this->getCellValue('AC', $rowIndex));
$shipperEta = $pud->getShipperEta();
if ($shipperEta != $shipperAta && is_object($shipperAta)) {
$id = $pud->getId();
$timeEta = $shipperEta->format('d.m.Y H:i:s');
$timeAta = $shipperAta->format('d.m.Y H:i:s');
$message[] = "PUD#{$id} has ShipperETA#{$timeEta} that not equal ShipperATA#{$timeAta}";
}
$pud->setShipperAta($shipperAta);
$pud->setShipperAtd($this->renderDateString($this->getCellValue('AA', $rowIndex)));
$pud->setConsigneeAta($this->renderDateString($this->getCellValue('AB', $rowIndex)));
$pud->setConsigneeAtd($consigneeAtd);
if ($shipperEta instanceof \DateTime && $consigneeAtd instanceof \DateTime) {
$eta[$truckId][] = $shipperEta;
$atd[$truckId][] = $consigneeAtd;
}
//if truckOrder have status Confirm that set pud status Confirm
if ($truckOrder->getStatus()->getName() == 'Confirmed') {
$pud->setStatus($this->getStatus('Confirmed'));
}
$em->flush();
$em->getConnection()->commit();
} catch (\Exception $ex) {
$em->getConnection()->rollback();
$logger = $this->get('logger');
$logger->error($ex->getMessage());
$error = $ex->getMessage();
}
}
if (is_null($error)) {
if (!empty($eta) && !empty($atd)) {
$this->updateWorkingHours($eta, $atd);
}
$message[] = "The file name is {$filename} was upload successfully";
}
} else {
if ($type == 'security') {
foreach ($this->sheet->getRowIterator() as $row) {
if ($row->getRowIndex() < self::START_ROWS) {
continue;
}
$pudId = $this->getCellValue('B', $row->getRowIndex());
$pud = $pudRepository->find($pudId);
if (!$pud) {
$logger = $this->get('logger');
$error = "The PUD {$pudId} not found";
$logger->error($error);
break;
}
try {
$em->getConnection()->beginTransaction();
$rowIndex = $row->getRowIndex();
#$pud = new Entity\Pud();
$pud->setActualSecurityCost($this->getCellValue('U', $rowIndex));
$pud->setSecurityInvoice($this->getCellValue('V', $rowIndex));
$em->flush();
$em->getConnection()->commit();
$em->getConnection()->close();
} catch (Doctrine\DBAL\DBALException $ex) {
$em->getConnection()->rollback();
$em->getConnection()->close();
$logger = $this->get('logger');
$logger->error($ex->getMessage());
}
}
if (is_null($error)) {
$message[] = "The file name is {$filename} was upload successfully";
}
} else {
$error = "File type is not set";
}
}
}
}
return $this->render('AppTruckingBundle:Report:upload.html.twig', ['form' => isset($form) ? $form->createView() : null, 'message' => isset($message) ? $message : null, 'error' => $error]);
}
public function __construct(Container $container)
{
parent::__construct($container);
$this->_phpexcel = new \PHPExcel();
$this->_sheet = $this->_phpexcel->getActiveSheet();
}
public function uploadAction(Request $request)
{
$form = $this->createForm(new Pud\Upload());
$form->handleRequest($request);
if ($request->isMethod("POST")) {
if ($form->isValid()) {
try {
foreach ($request->files as $uploadedFile) {
$file = $uploadedFile['file'];
$filename = $file->getClientOriginalName();
$file->move(QA\TDQueueAbstract::getUploadReportDir(), $filename);
break;
}
$filepath = QA\TDQueueAbstract::getUploadReportDir() . '/' . $filename;
$this->objExcel = \PHPExcel_IOFactory::load($filepath);
$this->sheet = $this->objExcel->getActiveSheet();
$pudRepository = $this->getDoctrine()->getRepository('AppTruckingBundle:Pud');
$em = $this->getDoctrine()->getManager();
//массив ошибок, если они есть
$error = null;
foreach ($this->sheet->getRowIterator() as $row) {
$rowIndex = $row->getRowIndex();
if ($rowIndex < $this->startRows) {
continue;
}
$row = null;
$result = $this->isImportRowCorrect($rowIndex);
$row = $result['RESULT'];
//проверка нужна на случай если есть незаполненые строки в импортируемом файле.
if (!count($row)) {
continue;
}
//если есть ошибки, прерываем загрузку
if ($result['SUCCESS'] == false) {
$error = $row;
break;
}
$pud = new Entity\Pud();
$pud->setCustomerId($row['CUSTOMER']);
$pud->setPackingId($row['PACKAGE_TYPE']);
$pud->setCargoType($row['TRUCKING_CARGO_TYPE']);
$pud->setCargoDescription($this->getCellValue('D', $rowIndex));
$pud->setLength($this->getCellValue('E', $rowIndex));
$pud->setWidth($this->getCellValue('F', $rowIndex));
$pud->setHeight($this->getCellValue('G', $rowIndex));
$volume = isset($row['TOTAL_VOLUME']) ? $row['TOTAL_VOLUME'] : $this->getCellValue('H', $rowIndex);
$pud->setVolume($volume);
$quantity = isset($row['ITEM_QUANTITY']) ? $row['ITEM_QUANTITY'] : $this->getCellValue('I', $rowIndex);
$pud->setCountPlaces($quantity);
$pud->setSummaryWeight($row['TOTAL_WEIGHT']);
$pud->setStackFlag($row['STACKABILITY']);
$pud->setShipper($row['SHIPPER']);
$pud->setShipperAddress($row['SHIPPER_ADDRESS']);
$pud->setLoadTimeSlot($this->renderDateString($row['LOADING_TIME_SLOT']));
$pud->setConsignee($row['CONSIGNEE']);
$pud->setConsigneeAddress($row['CONSIGNEE_ADDRESS']);
$pud->setUnloadTimeSlot($this->renderDateString($row['UNLOADING_TIME_SLOT']));
$pud->setSecurityFlag($row['SECURITY']);
$pud->setSecurityType($row['SECURITY_TYPE']);
$pud->setComments($this->getCellValue('T', $rowIndex));
$pud->setStatus($this->getStatus('Uploaded'));
$pud->setUserId($this->getUser());
$em->persist($pud);
$em->flush();
$message = "The file name is {$filename} was upload successfully";
}
} catch (\Exception $ex) {
$message = 'Error: ' . $ex->getMessage() . '. Line: ' . $ex->getLine() . ' File: ' . $ex->getFile();
$logger = $this->get('logger');
$logger->error($message);
$error[] = $message;
}
}
}
return $this->render('AppTruckingBundle:Pud:upload.html.twig', ['form' => isset($form) ? $form->createView() : null, 'message' => isset($message) ? $message : null, 'error' => isset($error) ? $error : null]);
}
