public function convert($input) { if (!$input) { return; } if (is_numeric($input) && $input < 100000) { //Date may be 42338 (=> 30.11.2015 $date = \PHPExcel_Shared_Date::ExcelToPHPObject($input); return $this->formatOutput($date); } return parent::convert($input); }
private static function _coupFirstPeriodDate($settlement, $maturity, $frequency, $next) { $months = 12 / $frequency; $result = PHPExcel_Shared_Date::ExcelToPHPObject($maturity); $eom = self::_lastDayOfMonth($result); while ($settlement < PHPExcel_Shared_Date::PHPToExcel($result)) { $result->modify('-' . $months . ' months'); } if ($next) { $result->modify('+' . $months . ' months'); } if ($eom) { $result->modify('-1 day'); } return PHPExcel_Shared_Date::PHPToExcel($result); }
/** * YEAR * * @param long $dateValue Excel date serial value or a standard date string * @return int Year */ public static function YEAR($dateValue = 1) { $dateValue = PHPExcel_Calculation_Functions::flattenSingleValue($dateValue); if (is_string($dateValue = self::_getDateValue($dateValue))) { return PHPExcel_Calculation_Functions::VALUE(); } elseif ($dateValue < 0.0) { return PHPExcel_Calculation_Functions::NaN(); } // Execute function $PHPDateObject = PHPExcel_Shared_Date::ExcelToPHPObject($dateValue); return (int) $PHPDateObject->format('Y'); }
/** * Convert a value in a pre-defined format to a PHP string * * @param mixed $value Value to format * @param string $format Format code * @param array $callBack Callback function for additional formatting of string * @return string Formatted string */ public static function toFormattedString($value = '', $format = '', $callBack = null) { // For now we do not treat strings although section 4 of a format code affects strings if (!is_numeric($value)) { return $value; } // For 'General' format code, we just pass the value although this is not entirely the way Excel does it, // it seems to round numbers to a total of 10 digits. if ($format === PHPExcel_Style_NumberFormat::FORMAT_GENERAL || $format === PHPExcel_Style_NumberFormat::FORMAT_TEXT) { return $value; } // Get the sections, there can be up to four sections $sections = explode(';', $format); // Fetch the relevant section depending on whether number is positive, negative, or zero? // Text not supported yet. // Here is how the sections apply to various values in Excel: // 1 section: [POSITIVE/NEGATIVE/ZERO/TEXT] // 2 sections: [POSITIVE/ZERO/TEXT] [NEGATIVE] // 3 sections: [POSITIVE/TEXT] [NEGATIVE] [ZERO] // 4 sections: [POSITIVE] [NEGATIVE] [ZERO] [TEXT] switch (count($sections)) { case 1: $format = $sections[0]; break; case 2: $format = $value >= 0 ? $sections[0] : $sections[1]; $value = abs($value); // Use the absolute value break; case 3: $format = $value > 0 ? $sections[0] : ($value < 0 ? $sections[1] : $sections[2]); $value = abs($value); // Use the absolute value break; case 4: $format = $value > 0 ? $sections[0] : ($value < 0 ? $sections[1] : $sections[2]); $value = abs($value); // Use the absolute value break; default: // something is wrong, just use first section $format = $sections[0]; break; } // Save format with color information for later use below $formatColor = $format; // Strip color information $color_regex = '/^\\[[a-zA-Z]+\\]/'; $format = preg_replace($color_regex, '', $format); // Let's begin inspecting the format and converting the value to a formatted string if (preg_match('/^(\\[\\$[A-Z]*-[0-9A-F]*\\])*[hmsdy]/i', $format)) { // datetime format // dvc: convert Excel formats to PHP date formats // strip off first part containing e.g. [$-F800] or [$USD-409] // general syntax: [$<Currency string>-<language info>] // language info is in hexadecimal $format = preg_replace('/^(\\[\\$[A-Z]*-[0-9A-F]*\\])/i', '', $format); // OpenOffice.org uses upper-case number formats, e.g. 'YYYY', convert to lower-case $format = strtolower($format); $format = strtr($format, self::$_dateFormatReplacements); if (!strpos($format, 'A')) { // 24-hour time format $format = strtr($format, self::$_dateFormatReplacements24); } else { // 12-hour time format $format = strtr($format, self::$_dateFormatReplacements12); } $dateObj = PHPExcel_Shared_Date::ExcelToPHPObject($value); $value = $dateObj->format($format); } else { if (preg_match('/%$/', $format)) { // % number format if ($format === self::FORMAT_PERCENTAGE) { $value = round(100 * $value, 0) . '%'; } else { if (preg_match('/\\.[#0]+/i', $format, $m)) { $s = substr($m[0], 0, 1) . (strlen($m[0]) - 1); $format = str_replace($m[0], $s, $format); } if (preg_match('/^[#0]+/', $format, $m)) { $format = str_replace($m[0], strlen($m[0]), $format); } $format = '%' . str_replace('%', 'f%%', $format); $value = sprintf($format, 100 * $value); } } else { if ($format === self::FORMAT_CURRENCY_EUR_SIMPLE) { $value = 'EUR ' . sprintf('%1.2f', $value); } else { // In Excel formats, "_" is used to add spacing, which we can't do in HTML $format = preg_replace('/_./', '', $format); // Some non-number characters are escaped with \, which we don't need $format = preg_replace("/\\\\/", '', $format); // Some non-number strings are quoted, so we'll get rid of the quotes, likewise any positional * symbols $format = str_replace(array('"', '*'), '', $format); // Find out if we need thousands separator // This is indicated by a comma enclosed by a digit placeholder: // #,# or 0,0 $useThousands = preg_match('/(#,#|0,0)/', $format); if ($useThousands) { $format = preg_replace('/0,0/', '00', $format); $format = preg_replace('/#,#/', '##', $format); } // Scale thousands, millions,... // This is indicated by a number of commas after a digit placeholder: // #, or 0.0,, $scale = 1; // same as no scale $matches = array(); if (preg_match('/(#|0)(,+)/', $format, $matches)) { $scale = pow(1000, strlen($matches[2])); // strip the commas $format = preg_replace('/0,+/', '0', $format); $format = preg_replace('/#,+/', '#', $format); } if (preg_match('/#?.*\\?\\/\\?/', $format, $m)) { //echo 'Format mask is fractional '.$format.' <br />'; if ($value != (int) $value) { $sign = $value < 0 ? '-' : ''; $integerPart = floor(abs($value)); $decimalPart = trim(fmod(abs($value), 1), '0.'); $decimalLength = strlen($decimalPart); $decimalDivisor = pow(10, $decimalLength); $GCD = PHPExcel_Calculation_MathTrig::GCD($decimalPart, $decimalDivisor); $adjustedDecimalPart = $decimalPart / $GCD; $adjustedDecimalDivisor = $decimalDivisor / $GCD; if (strpos($format, '0') !== false || strpos($format, '#') !== false || substr($format, 0, 3) == '? ?') { if ($integerPart == 0) { $integerPart = ''; } $value = "{$sign}{$integerPart} {$adjustedDecimalPart}/{$adjustedDecimalDivisor}"; } else { $adjustedDecimalPart += $integerPart * $adjustedDecimalDivisor; $value = "{$sign}{$adjustedDecimalPart}/{$adjustedDecimalDivisor}"; } } } else { // Handle the number itself // scale number $value = $value / $scale; // Strip # $format = preg_replace('/\\#/', '', $format); $n = "/\\[[^\\]]+\\]/"; $m = preg_replace($n, '', $format); $number_regex = "/(0+)(\\.?)(0*)/"; if (preg_match($number_regex, $m, $matches)) { $left = $matches[1]; $dec = $matches[2]; $right = $matches[3]; // minimun width of formatted number (including dot) $minWidth = strlen($left) + strlen($dec) + strlen($right); if ($useThousands) { $value = number_format($value, strlen($right), PHPExcel_Shared_String::getDecimalSeparator(), PHPExcel_Shared_String::getThousandsSeparator()); } else { $sprintf_pattern = "%0{$minWidth}." . strlen($right) . "f"; $value = sprintf($sprintf_pattern, $value); } $value = preg_replace($number_regex, $value, $format); } } if (preg_match('/\\[\\$(.*)\\]/u', $format, $m)) { // Currency or Accounting $currencyFormat = $m[0]; $currencyCode = $m[1]; list($currencyCode) = explode('-', $currencyCode); if ($currencyCode == '') { $currencyCode = PHPExcel_Shared_String::getCurrencyCode(); } $value = preg_replace('/\\[\\$([^\\]]*)\\]/u', $currencyCode, $value); } } } } // Additional formatting provided by callback function if ($callBack !== null) { list($writerInstance, $function) = $callBack; $value = $writerInstance->{$function}($value, $formatColor); } return $value; }
private static function _formatAsDate(&$value, &$format) { // dvc: convert Excel formats to PHP date formats // strip off first part containing e.g. [$-F800] or [$USD-409] // general syntax: [$<Currency string>-<language info>] // language info is in hexadecimal $format = preg_replace('/^(\\[\\$[A-Z]*-[0-9A-F]*\\])/i', '', $format); // OpenOffice.org uses upper-case number formats, e.g. 'YYYY', convert to lower-case $format = strtolower($format); $format = strtr($format, self::$_dateFormatReplacements); if (!strpos($format, 'A')) { // 24-hour time format $format = strtr($format, self::$_dateFormatReplacements24); } else { // 12-hour time format $format = strtr($format, self::$_dateFormatReplacements12); } $dateObj = PHPExcel_Shared_Date::ExcelToPHPObject($value); $value = $dateObj->format($format); }
protected function convertCellDate($cellValue) { if ($cellValue == '') { return null; } if (is_numeric($cellValue)) { $d = PHPExcel_Shared_Date::ExcelToPHPObject($cellValue); } else { $cellValue = str_replace(array('/', '.'), '-', $cellValue); $a = explode('-', $cellValue); if (count($a) == 3 && is_numeric($a[0]) && is_numeric($a[1]) && is_numeric($a[2])) { $d = DateTime::createFromFormat('Y-m-d', "{$a[2]}-{$a[1]}-{$a[0]}"); } else { $this->logger->warning(_("Data non valida \"{$cellValue}\"")); return null; } } return $d->format('Y-m-d'); }
/** * * @param string $uid * * @return Response */ public function editPostAction($uid) { $urlFrom = $this->getReferer(); if (null == $urlFrom || trim($urlFrom) == '') { $urlFrom = $this->generateUrl('_admin_company_list'); } $em = $this->getEntityManager(); try { $mbpurchase = $em->getRepository('AcfDataBundle:MBPurchase')->find($uid); if (null == $mbpurchase) { $this->flashMsgSession('warning', $this->translate('MBPurchase.edit.notfound')); } else { $traces = $em->getRepository('AcfDataBundle:Trace')->getAllByEntityId($mbpurchase->getId(), Trace::AE_MBPURCHASE); $this->gvars['traces'] = array_reverse($traces); $mbpurchaseUpdateCountForm = $this->createForm(MBPurchaseUpdateCountTForm::class, $mbpurchase); $buy = new Buy(); $buy->setMonthlyBalance($mbpurchase); $buyNewForm = $this->createForm(BuyNewTForm::class, $buy, array('monthlybalance' => $mbpurchase)); $buyImportForm = $this->createForm(BuyImportTForm::class); $mbpurchaseUpdateDocsForm = $this->createForm(MBPurchaseUpdateDocsTForm::class, $mbpurchase, array('company' => $mbpurchase->getCompany())); $doc = new Doc(); $doc->setCompany($mbpurchase->getCompany()); $docNewForm = $this->createForm(DocNewTForm::class, $doc, array('company' => $mbpurchase->getCompany())); $this->gvars['tabActive'] = $this->getSession()->get('tabActive', 2); $this->getSession()->remove('tabActive'); $this->gvars['stabActive'] = $this->getSession()->get('stabActive', 1); $this->getSession()->remove('stabActive'); $request = $this->getRequest(); $reqData = $request->request->all(); $cloneMBPurchase = clone $mbpurchase; if (isset($reqData['BuyImportForm'])) { $this->gvars['tabActive'] = 2; $this->getSession()->set('tabActive', 2); $this->gvars['stabActive'] = 1; $this->getSession()->set('stabActive', 1); $buyImportForm->handleRequest($request); if ($buyImportForm->isValid()) { ini_set('memory_limit', '4096M'); ini_set('max_execution_time', '0'); $extension = $buyImportForm['excel']->getData()->guessExtension(); if ($extension == 'zip') { $extension = 'xlsx'; } $filename = uniqid() . '.' . $extension; $buyImportForm['excel']->getData()->move($this->getParameter('adapter_files'), $filename); $fullfilename = $this->getParameter('adapter_files'); $fullfilename .= '/' . $filename; $excelObj = $this->get('phpexcel')->createPHPExcelObject($fullfilename); $log = ''; $iterator = $excelObj->getWorksheetIterator(); $activeSheetIndex = -1; $i = 0; foreach ($iterator as $worksheet) { $worksheetTitle = $worksheet->getTitle(); $highestRow = $worksheet->getHighestRow(); // e.g. 10 $highestColumn = $worksheet->getHighestColumn(); // e.g 'F' $highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn); $log .= "Feuille : '" . $worksheetTitle . "' trouvée contenant " . $highestRow . ' lignes et ' . $highestColumnIndex . ' colonnes avec comme plus grand index ' . $highestColumn . ' <br>'; if (\trim($worksheetTitle) == 'Sage') { $activeSheetIndex = $i; } $i++; } if ($activeSheetIndex == -1) { $log .= "Aucune Feuille de Titre 'Sage' trouvée tentative d'import depuis le première Feuille<br>"; $activeSheetIndex = 0; } $excelObj->setActiveSheetIndex($activeSheetIndex); $suppliersConstStr = $em->getRepository('AcfDataBundle:ConstantStr')->findOneBy(array('name' => 'suppliersPrefix')); if (null == $suppliersConstStr) { $suppliersConstStr = new ConstantStr(); $suppliersConstStr->setName('suppliersPrefix'); $suppliersConstStr->setValue('401'); $em->persist($suppliersConstStr); $em->flush(); } $suppliersPrefix = $suppliersConstStr->getValue(); $suppliersPrefixNum = \intval($suppliersPrefix) * 1000000000; $worksheet = $excelObj->getActiveSheet(); $highestRow = $worksheet->getHighestRow(); $lineRead = 0; $buysNew = 0; $lineUnprocessed = 0; $lineError = 0; $company = $mbpurchase->getCompany(); $accounts = $em->getRepository('AcfDataBundle:Account')->getAllByCompany($company); $suppliers = $em->getRepository('AcfDataBundle:Supplier')->getAllByCompany($company); $companyNatures = $em->getRepository('AcfDataBundle:CompanyNature')->getAllByCompany($company); $withholdings = $em->getRepository('AcfDataBundle:Withholding')->getAllByCompany($company); for ($row = 1; $row <= $highestRow; $row++) { $lineRead++; $dtActivation = \PHPExcel_Shared_Date::ExcelToPHPObject($worksheet->getCellByColumnAndRow(1, $row)->getValue()); $bill = \trim(\strval($worksheet->getCellByColumnAndRow(2, $row)->getValue())); $supplierNum = \intval($worksheet->getCellByColumnAndRow(4, $row)->getValue()); $label = \trim(\strval($worksheet->getCellByColumnAndRow(5, $row)->getValue())); $vat = \floatval($worksheet->getCellByColumnAndRow(7, $row)->getValue()); $stamp = \floatval($worksheet->getCellByColumnAndRow(8, $row)->getValue()); $balanceTtc = \floatval($worksheet->getCellByColumnAndRow(9, $row)->getValue()); $regime = \trim(\strval($worksheet->getCellByColumnAndRow(10, $row)->getValue())); $withholdingValue = \trim(\strval($worksheet->getCellByColumnAndRow(12, $row)->getValue() * 100)); $balanceNet = \floatval($worksheet->getCellByColumnAndRow(13, $row)->getValue()); $paymentType = \trim(\strval($worksheet->getCellByColumnAndRow(14, $row)->getValue())); $dtPayment = \PHPExcel_Shared_Date::ExcelToPHPObject($worksheet->getCellByColumnAndRow(15, $row)->getValue()); $accountLabel = \trim(\strval($worksheet->getCellByColumnAndRow(16, $row)->getValue())); $natureLabel = \trim(\strval($worksheet->getCellByColumnAndRow(17, $row)->getValue())); $status = \trim(\strval($worksheet->getCellByColumnAndRow(18, $row)->getValue())); $otherInfos = \trim(\strval($worksheet->getCellByColumnAndRow(19, $row)->getValue())); if ($supplierNum != '' && \is_numeric($supplierNum)) { $supplierNum = \intval($supplierNum) - $suppliersPrefixNum; } $haserror = false; if (null == $dtActivation) { $haserror = true; $log .= 'ligne ' . $lineRead . ", erreur : Date d'activation<br>"; } if ($bill == '') { $haserror = true; $log .= 'ligne ' . $lineRead . ', erreur : Numéro Facture<br>'; } if ($supplierNum == '' || $supplierNum <= 0) { $haserror = true; $oldsuppnum = $worksheet->getCellByColumnAndRow(4, $row)->getValue(); $log .= 'ligne ' . $lineRead . ', erreur : Numéro Fournisseur (' . $oldsuppnum . ')<br>'; } else { $supplier = null; $knownSupplier = false; foreach ($suppliers as $s) { if ($s->getNumber() == $supplierNum) { $knownSupplier = true; $supplier = $s; } } if ($knownSupplier == false) { $haserror = true; $log .= 'ligne ' . $lineRead . ', erreur : Fournisseur Inconnu<br>'; } } if ($label == '') { $haserror = true; $log .= 'ligne ' . $lineRead . ', erreur : Libélé<br>'; } if ($vat < 0) { $haserror = true; $log .= 'ligne ' . $lineRead . ', erreur : TVA<br>'; } if ($stamp < 0) { $haserror = true; $log .= 'ligne ' . $lineRead . ', erreur : Timbre<br>'; } if ($balanceTtc < 0) { $haserror = true; $log .= 'ligne ' . $lineRead . ', erreur : TTC<br>'; } if ($regime == $this->translate('Buy.regime.0')) { $regime = 0; } elseif ($regime == $this->translate('Buy.regime.1')) { $regime = 1; } elseif ($regime == $this->translate('Buy.regime.2')) { $regime = 2; } elseif ($regime == $this->translate('Buy.regime.3')) { $regime = 3; } elseif ($regime == $this->translate('Buy.regime.4')) { $regime = 4; } elseif ($regime == $this->translate('Buy.regime.5')) { $regime = 5; } else { $regime = 0; $log .= 'ligne ' . $lineRead . ', erreur (ignorée) : Régime inconnu => ' . $this->translate('Buy.regime.0') . '<br>'; } $withholding = null; $knownWithholding = false; foreach ($withholdings as $w) { if ($w->getValue() == $withholdingValue || $w->getLabel() == $withholdingValue) { $knownWithholding = true; $withholding = $w; } } if ($knownWithholding == false) { $haserror = true; $log .= 'ligne ' . $lineRead . ', erreur : Retenue Inconnue ' . $withholdingValue . '<br>'; } if ($balanceNet < 0) { $haserror = true; $log .= 'ligne ' . $lineRead . ', erreur : Net à Payer<br>'; } if ($paymentType == $this->translate('Transaction.paymentType.0')) { $paymentType = 0; } elseif ($paymentType == $this->translate('Transaction.paymentType.1')) { $paymentType = 1; } elseif ($paymentType == $this->translate('Transaction.paymentType.2')) { $paymentType = 2; } elseif ($paymentType == $this->translate('Transaction.paymentType.3')) { $paymentType = 3; } elseif ($paymentType == $this->translate('Transaction.paymentType.4')) { $paymentType = 4; } else { $paymentType = 0; $log .= 'ligne ' . $lineRead . ', erreur (ignorée) : Type de Paiement inconnu => ' . $this->translate('Transaction.paymentType.0') . '<br>'; } if (null == $dtPayment) { $haserror = true; $log .= 'ligne ' . $lineRead . ', erreur : Date de paiement<br>'; } $account = null; $knownAccount = false; foreach ($accounts as $a) { if ($a->getLabel() == $accountLabel) { $knownAccount = true; $account = $a; } } if ($knownAccount == false) { $haserror = true; $log .= 'ligne ' . $lineRead . ', erreur : Banque/Caisse Inconnue<br>'; } $nature = null; foreach ($companyNatures as $n) { if ($n->getLabel() == $natureLabel) { $nature = $n; } } if ($status == $this->translate('Transaction.transactionStatus.0')) { $status = 0; } elseif ($status == $this->translate('Transaction.transactionStatus.1')) { $status = 1; } elseif ($status == $this->translate('Transaction.transactionStatus.10')) { $status = 10; } else { $status = 0; $log .= 'ligne ' . $lineRead . ', erreur (ignorée) : Etat inconnu => ' . $this->translate('Transaction.transactionStatus.0') . '<br>'; } if ($haserror == false) { $buy = $em->getRepository('AcfDataBundle:Buy')->findOneBy(array('monthlyBalance' => $mbpurchase, 'bill' => $bill)); $exist = false; if (null == $buy) { $buysNew++; $buy = new Buy(); } else { $lineUnprocessed++; $log .= "l'Achat à la ligne " . $lineRead . ' existe déjà et sera donc remplacé<br>'; $exist = true; } $buy->setMonthlyBalance($mbpurchase); if (!$exist) { $buy->setNumber($mbpurchase->getCount()); } $buy->setDtActivation($dtActivation); $buy->setBill($bill); $buy->setRelation($supplier); $buy->setLabel($label); $buy->setVat($vat); $buy->setVatDevise($vat); $buy->setStamp($stamp); $buy->setStampDevise($stamp); $buy->setBalanceTtc($balanceTtc); $buy->setBalanceTtcDevise($balanceTtc); $buy->setRegime($regime); $buy->setWithholding($withholding); $buy->setBalanceNet($balanceNet); $buy->setBalanceNetDevise($balanceNet); $buy->setPaymentType($paymentType); $buy->setDtPayment($dtPayment); $buy->setAccount($account); $buy->setNature($nature); $buy->setTransactionStatus($status); $buy->setOtherInfos($otherInfos); $em->persist($buy); if (!$exist) { $mbpurchase->updateCount(); } $em->persist($mbpurchase); } else { $lineError++; $log .= 'la ligne ' . $lineRead . ' contient des erreurs<br>'; } } $em->flush(); $log .= $lineRead . ' lignes lues<br>'; $log .= $buysNew . ' nouveaux Achat<br>'; $log .= $lineUnprocessed . ' Achats déjà dans la base<br>'; $log .= $lineError . ' lignes contenant des erreurs<br>'; // */ $this->flashMsgSession('log', $log); $this->flashMsgSession('success', $this->translate('Buy.import.success')); $this->gvars['tabActive'] = 1; $this->getSession()->set('tabActive', 1); return $this->redirect($urlFrom); } else { $em->refresh($mbpurchase); $this->flashMsgSession('error', $this->translate('Buy.import.failure')); } } elseif (isset($reqData['BuyNewForm'])) { $this->gvars['tabActive'] = 2; $this->getSession()->set('tabActive', 2); $this->gvars['stabActive'] = 2; $this->getSession()->set('stabActive', 2); $buyNewForm->handleRequest($request); if ($buyNewForm->isValid()) { $buy->setNumber($mbpurchase->getCount()); if ($buy->getDevise() == 'TND') { $buy->setConversionRate(1); $buy->setVatDevise($buy->getVat()); $buy->setStampDevise($buy->getStamp()); $buy->setBalanceTtcDevise($buy->getBalanceTtc()); $buy->setBalanceNetDevise($buy->getBalanceNet()); } else { $buy->setVat($buy->getVatDevise() * $buy->getConversionRate()); $buy->setStamp($buy->getStampDevise() * $buy->getConversionRate()); $buy->setBalanceTtc($buy->getBalanceTtcDevise() * $buy->getConversionRate()); $buy->setBalanceNet($buy->getBalanceNetDevise() * $buy->getConversionRate()); } foreach ($buyNewForm->get('docs') as $docNewForm) { $docFile = $docNewForm['fileName']->getData(); $docDir = $this->getParameter('kernel.root_dir') . '/../web/res/docs'; $originalName = $docFile->getClientOriginalName(); $fileName = sha1(uniqid(mt_rand(), true)) . '.' . strtolower($docFile->getClientOriginalExtension()); $mimeType = $docFile->getMimeType(); $docFile->move($docDir, $fileName); $size = filesize($docDir . '/' . $fileName); $md5 = md5_file($docDir . '/' . $fileName); $doc = $docNewForm->getData(); $doc->setCompany($mbpurchase->getCompany()); $doc->setFileName($fileName); $doc->setOriginalName($originalName); $doc->setSize($size); $doc->setMimeType($mimeType); $doc->setMd5($md5); $doc->setDescription($docNewForm['description']->getData()); $em->persist($doc); $buy->addDoc($doc); } $em->persist($buy); $em->flush(); $mbpurchase->updateCount(); $em->persist($mbpurchase); $em->flush(); $this->flashMsgSession('success', $this->translate('Buy.add.success', array('%buy%' => $buy->getNumber()))); $this->gvars['tabActive'] = 1; $this->getSession()->set('tabActive', 1); $this->gvars['stabActive'] = 1; $this->getSession()->set('stabActive', 1); return $this->redirect($urlFrom); } else { $em->refresh($mbpurchase); $this->flashMsgSession('error', $this->translate('Buy.add.failure')); } } elseif (isset($reqData['MBPurchaseUpdateCountForm'])) { $this->gvars['tabActive'] = 3; $this->getSession()->set('tabActive', 3); $mbpurchaseUpdateCountForm->handleRequest($request); if ($mbpurchaseUpdateCountForm->isValid()) { $em->persist($mbpurchase); $em->flush(); $this->flashMsgSession('success', $this->translate('MBPurchase.edit.success', array('%mbpurchase%' => $mbpurchase->getRef()))); $this->traceEntity($cloneMBPurchase, $mbpurchase); return $this->redirect($urlFrom); } else { $em->refresh($mbpurchase); $this->flashMsgSession('error', $this->translate('MBPurchase.edit.failure', array('%mbpurchase%' => $mbpurchase->getRef()))); } } elseif (isset($reqData['DocNewForm'])) { $this->gvars['tabActive'] = 4; $this->getSession()->set('tabActive', 4); $this->gvars['stabActive'] = 1; $this->getSession()->set('stabActive', 1); $docNewForm->handleRequest($request); if ($docNewForm->isValid()) { $docFiles = $docNewForm['fileName']->getData(); $docs = array(); $docDir = $this->getParameter('kernel.root_dir') . '/../web/res/docs'; $docNames = ''; foreach ($docFiles as $docFile) { $originalName = $docFile->getClientOriginalName(); $fileName = sha1(uniqid(mt_rand(), true)) . '.' . strtolower($docFile->getClientOriginalExtension()); $mimeType = $docFile->getMimeType(); $docFile->move($docDir, $fileName); $size = filesize($docDir . '/' . $fileName); $md5 = md5_file($docDir . '/' . $fileName); $doc = new Doc(); $doc->setCompany($mbpurchase->getCompany()); $doc->setFileName($fileName); $doc->setOriginalName($originalName); $doc->setSize($size); $doc->setMimeType($mimeType); $doc->setMd5($md5); $doc->setDescription($docNewForm['description']->getData()); $em->persist($doc); $mbpurchase->addDoc($doc); $docNames .= $doc->getOriginalName() . ' '; $docs[] = $doc; } $em->persist($mbpurchase); $em->flush(); $this->flashMsgSession('success', $this->translate('Doc.add.success', array('%doc%' => $docNames))); $from = $this->getParameter('mail_from'); $fromName = $this->getParameter('mail_from_name'); $subject = $this->translate('_mail.newdocsCloud.subject', array(), 'messages'); $company = $mbpurchase->getCompany(); $acfCloudRole = $em->getRepository('AcfDataBundle:Role')->findOneBy(array('name' => 'ROLE_CLIENT1')); $users = array(); foreach ($company->getUsers() as $user) { if ($user->hasRole($acfCloudRole)) { $users[] = $user; } } if (\count($users) != 0) { foreach ($users as $user) { $mvars = array(); $mvars['company'] = $company; $mvars['docs'] = $docs; $message = \Swift_Message::newInstance(); $message->setFrom($from, $fromName); $message->addTo($user->getEmail(), $user->getFullname()); $message->setSubject($subject); $mvars['logo'] = $message->embed(\Swift_Image::fromPath($this->getParameter('kernel.root_dir') . '/../web/bundles/acfres/images/logo_acf.jpg')); $message->setBody($this->renderView('AcfAdminBundle:Doc:sendmail.html.twig', $mvars), 'text/html'); $this->sendmail($message); } } $this->gvars['stabActive'] = 3; $this->getSession()->set('stabActive', 3); $this->traceEntity($cloneMBPurchase, $mbpurchase); return $this->redirect($urlFrom); } else { $em->refresh($mbpurchase); $this->flashMsgSession('error', $this->translate('Doc.add.failure')); } } elseif (isset($reqData['MBPurchaseUpdateDocsForm'])) { $this->gvars['tabActive'] = 4; $this->getSession()->set('tabActive', 4); $this->gvars['stabActive'] = 2; $this->getSession()->set('stabActive', 2); $mbpurchaseUpdateDocsForm->handleRequest($request); if ($mbpurchaseUpdateDocsForm->isValid()) { $em->persist($mbpurchase); $em->flush(); $this->flashMsgSession('success', $this->translate('MBPurchase.edit.success', array('%mbpurchase%' => $mbpurchase->getRef()))); $this->gvars['stabActive'] = 3; $this->getSession()->set('stabActive', 3); $this->traceEntity($cloneMBPurchase, $mbpurchase); return $this->redirect($urlFrom); } else { $em->refresh($mbpurchase); $this->flashMsgSession('error', $this->translate('MBPurchase.edit.failure', array('%mbpurchase%' => $mbpurchase->getRef()))); } } $this->gvars['mbpurchase'] = $mbpurchase; $this->gvars['buy'] = $buy; $this->gvars['doc'] = $doc; $this->gvars['BuyNewForm'] = $buyNewForm->createView(); $this->gvars['BuyImportForm'] = $buyImportForm->createView(); $this->gvars['MBPurchaseUpdateCountForm'] = $mbpurchaseUpdateCountForm->createView(); $this->gvars['MBPurchaseUpdateDocsForm'] = $mbpurchaseUpdateDocsForm->createView(); $this->gvars['DocNewForm'] = $docNewForm->createView(); $suppliersConstStr = $em->getRepository('AcfDataBundle:ConstantStr')->findOneBy(array('name' => 'suppliersPrefix')); if (null == $suppliersConstStr) { $suppliersConstStr = new ConstantStr(); $suppliersConstStr->setName('suppliersPrefix'); $suppliersConstStr->setValue('401'); $em->persist($suppliersConstStr); $em->flush(); } $suppliersPrefix = $suppliersConstStr->getValue(); $this->gvars['suppliersPrefix'] = $suppliersPrefix; $this->gvars['pagetitle'] = $this->translate('pagetitle.mbpurchase.edit', array('%mbpurchase%' => $mbpurchase->getRef())); $this->gvars['pagetitle_txt'] = $this->translate('pagetitle.mbpurchase.edit.txt', array('%mbpurchase%' => $mbpurchase->getRef())); return $this->renderResponse('AcfAdminBundle:MBPurchase:edit.html.twig', $this->gvars); } } catch (\Exception $e) { $logger = $this->getLogger(); $logger->addCritical($e->getLine() . ' ' . $e->getMessage() . ' ' . $e->getTraceAsString()); } return $this->redirect($urlFrom); }
/** * * @return \Symfony\Component\HttpFoundation\RedirectResponse */ public function importPostAction() { if (!$this->hasRole('ROLE_SUPERADMIN')) { return $this->redirect($this->generateUrl('_admin_homepage')); } $urlFrom = $this->getReferer(); if (null == $urlFrom || trim($urlFrom) == '') { $urlFrom = $this->generateUrl('_admin_bulletinInfo_list'); } $bulletinInfoImportForm = $this->createForm(BulletinInfoImportTForm::class); $request = $this->getRequest(); $reqData = $request->request->all(); if (isset($reqData['BulletinInfoImportForm'])) { $bulletinInfoImportForm->handleRequest($request); if ($bulletinInfoImportForm->isValid()) { ini_set('memory_limit', '4096M'); ini_set('max_execution_time', '0'); $extension = $bulletinInfoImportForm['excel']->getData()->guessExtension(); if ($extension == 'zip') { $extension = 'xlsx'; } $filename = uniqid() . '.' . $extension; $bulletinInfoImportForm['excel']->getData()->move($this->getParameter('adapter_files'), $filename); $fullfilename = $this->getParameter('adapter_files'); $fullfilename .= '/' . $filename; $excelObj = $this->get('phpexcel')->createPHPExcelObject($fullfilename); $log = ''; $iterator = $excelObj->getWorksheetIterator(); $activeSheetIndex = -1; $i = 0; $bulletinInfo = new BulletinInfo(); foreach ($iterator as $worksheet) { $worksheetTitle = $worksheet->getTitle(); $highestRow = $worksheet->getHighestRow(); // e.g. 10 $highestColumn = $worksheet->getHighestColumn(); // e.g 'F' $highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn); $log .= "Feuille : '" . $worksheetTitle . "' trouvée contenant " . $highestRow . ' lignes et ' . $highestColumnIndex . ' colonnes avec comme plus grand index ' . $highestColumn . ' <br>'; if (\trim($worksheetTitle) == 'BulletinInfo') { $activeSheetIndex = $i; } $i++; } if ($activeSheetIndex == -1) { $log .= "Aucune Feuille de Titre 'BulletinInfo' trouvée tentative d'import depuis le première Feuille<br>"; $activeSheetIndex = 0; } $excelObj->setActiveSheetIndex($activeSheetIndex); $worksheet = $excelObj->getActiveSheet(); $highestRow = $worksheet->getHighestRow(); $lineRead = 0; $lineUnprocessed = 0; $bulletinInfoTitleNew = 0; $bulletinInfoContentNew = 0; $lineError = 0; $haserror = false; if ($highestRow < 3) { $log .= 'Fichier Excel Invalide<br>'; $haserror = true; } else { $lineRead++; $num = \trim(\intval($worksheet->getCellByColumnAndRow(8, 3)->getValue())); $dtStart = \PHPExcel_Shared_Date::ExcelToPHPObject($worksheet->getCellByColumnAndRow(10, 3)->getValue()); if ($num <= 0) { $log .= 'Numéro de bulletin illisible (' . $num . ')<br>'; $haserror = true; $lineError++; } if (!$dtStart instanceof \DateTime) { $log .= 'Date du bulletin illisible (' . $dtStart . ')<br>'; $haserror = true; $lineError++; } } if (!$haserror) { $em = $this->getEntityManager(); $bulletinInfoTest = $em->getRepository('AcfDataBundle:BulletinInfo')->findOneBy(array('num' => $num)); if (null != $bulletinInfoTest) { $log .= 'Numéro de bulletin déjà existant<br>'; $haserror = true; } } if (!$haserror) { $bulletinInfo->setNum($num); $bulletinInfo->setDtStart($dtStart); $em->persist($bulletinInfo); $lineRead = 5; $isTitle = false; $isContent = false; $titles = array(); // $logger = $this->getLogger(); for ($row = $lineRead - 1; $row <= $highestRow; $row++) { $isTitle = false; $isContent = false; $canBeTitle = false; $canBeContent = false; try { $testNum = \trim(\strval($worksheet->getCellByColumnAndRow(1, $row)->getValue())); if ($testNum != '') { if (\stripos($testNum, '-')) { $canBeContent = true; } elseif (\intval($testNum) > 0) { $canBeTitle = true; } } if ($canBeTitle) { if (!\array_key_exists($testNum, $titles)) { $isTitle = true; } else { $lineError++; $log .= 'Titre : Numéro de Titre ' . $testNum . ' à la ligne ' . $lineRead . ' existe déja<br>'; } } if ($canBeContent) { $titleContent = \explode('-', $testNum); if (\count($titleContent) == 2) { $titleNum = \intval($titleContent[0]); $contentNum = \intval($titleContent[1]); if ($titleNum > 0 && $titleContent > 0) { if (!\array_key_exists($titleNum, $titles)) { $lineError++; $log .= 'Contenu : Numéro de Titre ' . $titleNum . ' à la ligne ' . $lineRead . ' inexistant<br>'; } elseif (!\array_key_exists($contentNum, $titles[$titleNum]['contents'])) { $isContent = true; } else { $lineError++; $log .= 'Contenu : Numéro de Contenu ' . $contentNum . ' à la ligne ' . $lineRead . ' existe déjà<br>'; } } } } if ($isTitle) { $btTitle = \trim(\strval($worksheet->getCellByColumnAndRow(2, $row)->getValue())); if ($btTitle != '') { $bulletinInfoTitle = new BulletinInfoTitle(); $bulletinInfoTitle->setBulletinInfo($bulletinInfo); $bulletinInfoTitle->setTitle($btTitle); $em->persist($bulletinInfoTitle); $titles[$testNum] = array('bulletinInfoTitle' => $bulletinInfoTitle, 'contents' => array()); $bulletinInfoTitleNew++; } else { $lineError++; $log .= 'Titre : Numéro de nouveau Titre ' . $testNum . ' trouvé à la ligne ' . $lineRead . ' mais texte vide<br>'; } } if ($isContent) { $bcTitle = \trim(\strval($worksheet->getCellByColumnAndRow(2, $row)->getValue())); if ($bcTitle != '') { $bulletinInfoTitle = $titles[$titleNum]['bulletinInfoTitle']; $bulletinInfoContent = new BulletinInfoContent(); $bulletinInfoContent->setBulletinInfoTitle($bulletinInfoTitle); $bulletinInfoContent->setTitle($bcTitle); $row++; $content = \trim(\strval($worksheet->getCellByColumnAndRow(3, $row)->getValue())); if ($content != '') { $bulletinInfoContent->setContent($content); } $theme = \trim(\strval($worksheet->getCellByColumnAndRow(8, $row)->getValue())); if ($theme != '') { $bulletinInfoContent->setTheme($theme); } $jort = \trim(\strval($worksheet->getCellByColumnAndRow(9, $row)->getValue())); if ($jort != '') { $bulletinInfoContent->setJort($jort); } $txtNum = \trim(\strval($worksheet->getCellByColumnAndRow(10, $row)->getValue())); if ($txtNum != '') { $bulletinInfoContent->setTxtNum($txtNum); } $artTxt = \trim(\strval($worksheet->getCellByColumnAndRow(11, $row)->getValue())); if ($artTxt != '') { $bulletinInfoContent->setArtTxt($artTxt); } $dtTxt = \trim(\strval($worksheet->getCellByColumnAndRow(12, $row)->getValue())); if ($dtTxt != '') { $bulletinInfoContent->setDtTxt($dtTxt); } $artCode = \trim(\strval($worksheet->getCellByColumnAndRow(13, $row)->getValue())); if ($artCode != '') { $bulletinInfoContent->setArtCode($artCode); } $companyType = \trim(\strval($worksheet->getCellByColumnAndRow(14, $row)->getValue())); if ($companyType != '') { $bulletinInfoContent->setCompanyType($companyType); } $dtApplication = \trim(\strval($worksheet->getCellByColumnAndRow(15, $row)->getValue())); if ($dtApplication != '') { $bulletinInfoContent->setDtApplication($dtApplication); } $em->persist($bulletinInfoContent); $titles[$titleNum]['contents'][$contentNum] = $bulletinInfoContent; $bulletinInfoContentNew++; } else { $lineError++; $log .= 'Contenu : Numéro de Contenu ' . $testNum . ' trouvé à la ligne ' . $lineRead . ' mais texte vide<br>'; } } else { $lineUnprocessed++; } } catch (\Exception $e) { $lineError++; $log .= 'Erreur de lecture à la ligne ' . $lineRead . ' : ' . $e->getMessage() . '<br>'; } $lineRead++; } $em->flush(); } $log .= $lineRead . ' lignes lues<br>'; $log .= $lineUnprocessed . ' lignes non traités<br>'; $log .= $bulletinInfoTitleNew . " nouveaux Titres de Bulletin d'Informations<br>"; $log .= $bulletinInfoContentNew . " nouveaux Contenus de Bulletin d'Informations<br>"; $log .= $lineError . ' lignes contenant des erreurs<br>'; $this->flashMsgSession('log', $log); if (!$haserror) { $this->flashMsgSession('success', $this->translate('BulletinInfo.import.success', array('%bulletinInfo%' => $bulletinInfo->getId()))); return $this->redirect($this->generateUrl('_admin_bulletinInfo_editGet', array('uid' => $bulletinInfo->getId()))); } } } $this->flashMsgSession('error', $this->translate('BulletinInfo.import.failure')); return $this->redirect($urlFrom); }
public function exceltravel_upload() { if (Input::file('file') == '') { return View::make('admin.upexceltravel', array('status' => 'ไม่สามารถอัพข้อมูลได้')); } $destinationPath = 'upload_excel'; $filename = Input::file('file')->getClientOriginalName(); $uploadSuccess = Input::file('file')->move($destinationPath, $filename); $objPHPExcel = PHPExcel_IOFactory::load("upload_excel/" . $filename); $isheet = 0; $isheetrow = 0; foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) { $isheet++; $worksheetTitle = $worksheet->getTitle(); $highestRow = $worksheet->getHighestRow(); // e.g. 10 $highestColumn = $worksheet->getHighestColumn(); // e.g 'F' $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); $nrColumns = ord($highestColumn) - 64; for ($row = 3; $row <= $highestRow; ++$row) { $isheetrow++; $val = array(); for ($col = 0; $col < $highestColumnIndex; ++$col) { $cell = $worksheet->getCellByColumnAndRow($col, $row); $val[] = $cell->getValue(); } $dateObj = PHPExcel_Shared_Date::ExcelToPHPObject($val[0]); $datetravel = $dateObj->format('Y') - 543 . '-' . $dateObj->format('m-d'); $cid = $val[1]; $money = $val[5]; $Data = array('cid' => $cid, 'money' => $money, 'datetravel' => $datetravel); if ($Data['cid'] != null || $Data['cid'] != '') { $result = DB::insert('insert into s_travel_temp ( cid, money, datetravel ) values ( ? , ?, ? )', array($Data['cid'], $Data['money'], $Data['datetravel'])); } //if check cid null } } //end foreach $dataall = DB::table('s_travel_temp')->select('cid', DB::raw('month(datetravel) as travel_m'), DB::raw('year(datetravel) as travel_y'), DB::raw('sum(money) as total_money'))->groupby('cid')->groupby(DB::raw('month(datetravel)'))->orderby(DB::raw('year(datetravel)'), 'asc')->orderby(DB::raw('month(datetravel)'), 'asc')->get(); foreach ($dataall as $key => $value) { $Data_update = array('u_travel' => $value->total_money); $p = DB::table('n_position_salary')->where('cid', '=', $value->cid)->select('level')->orderBy('salaryID', 'desc')->limit(1)->first(); if ($p->level == 'ข้าราชการ' || $p->level == 'ลูกจ้างประจำ') { $result = DB::table('s_salary_ocsc_detail')->where('cid', '=', $value->cid)->where(DB::raw('year(order_date)'), $value->travel_y)->where(DB::raw('month(order_date)'), $value->travel_m)->update($Data_update); } else { $result = DB::table('s_salary_detail')->where('cid', '=', $value->cid)->where(DB::raw('year(order_date)'), $value->travel_y)->where(DB::raw('month(order_date)'), $value->travel_m)->update($Data_update); } } // end foreach update u_travel 2 table //clear table s_travel_temp //DB::table('s_travel_temp')->delete(); DB::table('s_travel_temp')->truncate(); return View::make('admin.upexceltravel', array('status' => 'อัพโหลดข้อมูลเรียบร้อย')); }
/** * 独立商城订单导入页面 */ public function act_insertDresslinkOrder() { //独立商城的扩展表尚未建立 return false; if (isset($_FILES['cndlAccountListFile']['tmp_name'])) { $filePath = $_FILES['cndlAccountListFile']['tmp_name']; $PHPExcel = E('PHPExcel'); $PHPReader = new PHPExcel_Reader_Excel2007(); if (!$PHPReader->canRead($filePath)) { $PHPReader = new PHPExcel_Reader_Excel5(); if (!$PHPReader->canRead($filePath)) { self::$errMsg[] = get_promptmsg(10058); return false; } } $PHPExcel = $PHPReader->load($filePath); $currentSheet = $PHPExcel->getSheet(0); $orderid = array(); $orderData = array(); F('order'); //引入lib/funciond的order_functions $account = $_POST['cndlAccountId']; //accountId if (intval($account) <= 0) { self::$errMsg[] = get_promptmsg(10054); return false; } $platformId = get_platFromidbyaccountid($account); if (intval($platformId) <= 0) { self::$errMsg[] = get_promptmsg(10121); return false; } // $carrierList = M('InterfaceTran')->getCarrierList(2);//获取所有的运输方式 // $carrierKV = array(); // foreach($carrierList as $key => $trans) { // $carrierKV[$trans['id']] = $trans['carrierNameCn']; // } $c = 2; $dresslinks = array(); $ebay_fedex_remark = array(); $ChineseDescs = array(); while (true) { $aa = 'A' . $c; $bb = 'B' . $c; $cc = 'C' . $c; $dd = 'D' . $c; $ee = 'E' . $c; $ff = 'F' . $c; $gg = 'G' . $c; $hh = 'H' . $c; $ii = 'I' . $c; $jj = 'J' . $c; $kk = 'K' . $c; $ll = 'L' . $c; $mm = 'M' . $c; $nn = 'N' . $c; $oo = 'O' . $c; $pp = 'P' . $c; $qq = 'Q' . $c; $rr = 'R' . $c; $ss = 'S' . $c; $tt = 'T' . $c; $uu = 'U' . $c; $vv = 'V' . $c; $ww = 'W' . $c; $xx = 'X' . $c; $yy = 'Y' . $c; $zz = 'Z' . $c; $aaa = 'AA' . $c; $abb = 'AB' . $c; $acc = 'AC' . $c; $add = 'AD' . $c; $aee = 'AE' . $c; $aff = 'AF' . $c; $agg = 'AG' . $c; $ahh = 'AH' . $c; $aii = 'AI' . $c; $ajj = 'AJ' . $c; $akk = 'AK' . $c; $all = 'AL' . $c; $amm = 'AM' . $c; $ann = 'AN' . $c; $aww = 'AW' . $c; $recordNumber = trim($currentSheet->getCell($aa)->getValue()); //订单号 if (empty($recordNumber)) { break; } /***************判断订单是否已存在***************/ if (M('OrderAdd')->checkIsExists(array('recordNumber' => $recordNumber, 'accountId' => $account))) { self::$errMsg[] = get_promptmsg(10043, $recordNumber); //"该recordNumber已经存在<br/>"; continue; } /**************/ $is_order = intval($currentSheet->getCell($bb)->getValue()); //1代表为订单,0代表订单明细 if ($is_order != 0) { //为订单 //这个验证可以不用 //if($cndlAccounts[$account]=="dresslink.com"){ // $str = substr($recordNumber,0,2); // if($str!=="DL"){ // $message .= "<font color=red> {$recordNumber}不在账号{$cndlAccounts[$account]}中!</font><br>"; // continue; // } // }elseif($cndlAccounts[$account]=="cndirect.com"){ // $str = substr($recordNumber,0,2); // if($str!=="CN"){ // $message .= "<font color=red> {$recordNumber}不在账号{$cndlAccounts[$account]}中!</font><br>"; // continue; // } // } $platformUsername = mysql_real_escape_string(trim($currentSheet->getCell($cc)->getValue())); $email = mysql_real_escape_string(trim($currentSheet->getCell($dd)->getValue())); $transId = mysql_real_escape_string(trim($currentSheet->getCell($ee)->getValue())); $ordersTime = (array) PHPExcel_Shared_Date::ExcelToPHPObject(trim($currentSheet->getCell($ll)->getValue())); $paymentTime = (array) PHPExcel_Shared_Date::ExcelToPHPObject(trim($currentSheet->getCell($mm)->getValue())); $shippingFee = round_num(trim($currentSheet->getCell($oo)->getValue()), 2); $calcWeight = round_num(trim($currentSheet->getCell($ahh)->getValue()), 3); $actualTotal = round_num(trim($currentSheet->getCell($pp)->getValue()), 2); $onlineTotal = round_num(trim($currentSheet->getCell($aff)->getValue()), 2); $currency = mysql_real_escape_string(trim($currentSheet->getCell($qq)->getValue())); //$orders['ebay_orderqk'] = round_num(trim($currentSheet->getCell($rr)->getValue()), 2); $note = mysql_real_escape_string(trim($currentSheet->getCell($ss)->getValue())); $username = mysql_real_escape_string(trim($currentSheet->getCell($tt)->getValue())); $countryName = mysql_real_escape_string(trim($currentSheet->getCell($uu)->getValue())); $state = mysql_real_escape_string(trim($currentSheet->getCell($vv)->getValue())); $city = mysql_real_escape_string(trim($currentSheet->getCell($ww)->getValue())); $street = mysql_real_escape_string(trim($currentSheet->getCell($xx)->getValue())); $address2 = mysql_real_escape_string(trim($currentSheet->getCell($yy)->getValue())); $zipCode = mysql_real_escape_string(trim($currentSheet->getCell($zz)->getValue())); $phone = mysql_real_escape_string(trim($currentSheet->getCell($abb)->getValue())); $landline = mysql_real_escape_string(trim($currentSheet->getCell($aaa)->getValue())); // if($account == 400){ //dresslink.com // $feedback = mysql_real_escape_string(trim($currentSheet->getCell($ann)->getValue())); // }elseif($account == 410){ //cndirect.com // $feedback = mysql_real_escape_string(trim($currentSheet->getCell($akk)->getValue())); // } $carrierNameCn = strtolower(mysql_real_escape_string(trim($currentSheet->getCell($kk)->getValue()))); $payment_method = mysql_real_escape_string(trim($currentSheet->getCell($ff)->getValue())); $payment_module = mysql_real_escape_string(trim($currentSheet->getCell($gg)->getValue())); $bank_account = mysql_real_escape_string(trim($currentSheet->getCell($hh)->getValue())); $bank_country = mysql_real_escape_string(trim($currentSheet->getCell($ii)->getValue())); $shipping_method = mysql_real_escape_string(trim($currentSheet->getCell($jj)->getValue())); $shipping_module = mysql_real_escape_string(trim($currentSheet->getCell($kk)->getValue())); //这个dresslinks_info表在新系统已经废除了 //$dresslinks['payment_method'] = $payment_method; // $dresslinks['payment_module'] = $payment_module; // $dresslinks['bank_account'] = $bank_account; // $dresslinks['bank_country'] = $bank_country; // $dresslinks['shipping_method'] = $shipping_method; // $dresslinks['shipping_module'] = $shipping_module; $PayPalPaymentId = $transId; $ordersTime = strtotime($ordersTime['date']); $paymentTime = strtotime($paymentTime['date']); /***************BEGIN 订单表数据***************/ //order信息 $orderData[$recordNumber]['order']['recordNumber'] = $recordNumber; $orderData[$recordNumber]['order']['platformId'] = $platformId; //这里要根据accountId得到对应的platformId $orderData[$recordNumber]['order']['accountId'] = $account; $orderData[$recordNumber]['order']['orderStatus'] = 0; $orderData[$recordNumber]['order']['ordersTime'] = $ordersTime; $orderData[$recordNumber]['order']['paymentTime'] = $paymentTime; $orderData[$recordNumber]['order']['onlineTotal'] = $onlineTotal; $orderData[$recordNumber]['order']['actualTotal'] = $actualTotal; //$actualTotal+$shippingFee $orderData[$recordNumber]['order']['actualShipping'] = $shippingFee; $orderData[$recordNumber]['order']['currency'] = $currency; //取默认,待以后确认处理 $orderData[$recordNumber]['order']['transportId'] = 0; //走拦截流程,取消默认 $orderData[$recordNumber]['order']['pmId'] = 0; //包材ID,默认0 $orderData[$recordNumber]['order']['channelId'] = 0; //渠道ID,默认0 $orderData[$recordNumber]['order']['orderAddTime'] = time(); $orderData[$recordNumber]['order']['calcShipping'] = $shippingFee; $orderData[$recordNumber]['order']['orderType'] = 0; $orderData[$recordNumber]['order']['completeTime'] = 0; //完结时间,默认0 $orderData[$recordNumber]['order']['ORtransport'] = $carrierNameCn; //order扩展信息 //user信息 $orderData[$recordNumber]['orderUserInfo']['platformUsername'] = $platformUsername; $orderData[$recordNumber]['orderUserInfo']['username'] = $username; $orderData[$recordNumber]['orderUserInfo']['email'] = $email; $orderData[$recordNumber]['orderUserInfo']['countryName'] = $countryName; $orderData[$recordNumber]['orderUserInfo']['currency'] = $currency; //$currency; $orderData[$recordNumber]['orderUserInfo']['state'] = $state; $orderData[$recordNumber]['orderUserInfo']['city'] = $city; $orderData[$recordNumber]['orderUserInfo']['county'] = ' '; //区县默认空 $orderData[$recordNumber]['orderUserInfo']['address1'] = $street; $orderData[$recordNumber]['orderUserInfo']['mobilePhone'] = $landline; $orderData[$recordNumber]['orderUserInfo']['phone'] = $phone; $orderData[$recordNumber]['orderUserInfo']['zipCode'] = $zipCode; //detail信息 $orderData[$recordNumber]['orderDetail'][$sku]['orderDetail']['sku'] = $sku; $orderData[$recordNumber]['orderDetail'][$sku]['orderDetail']['amount'] = $amount; $orderData[$recordNumber]['orderDetail'][$sku]['orderDetail']['itemPrice'] = $itemPrice; $orderData[$recordNumber]['orderDetail'][$sku]['orderDetail']['createdTime'] = time(); $orderData[$recordNumber]['orderDetail'][$sku]['orderDetail']['shippingFee'] = $shippingFee; $orderData[$recordNumber]['orderDetail'][$sku]['orderDetail']['recordNumber'] = $recordNumber; $orderData[$recordNumber]['orderDetail'][$sku]['orderDetailExtension']['itemTitle'] = $itemTitle; /***************END 订单表数据***************/ /***************BEGIN 订单扩展表数据***************/ $orderData[$recordNumber]['orderExtension']['feedback'] = $note; $orderData[$recordNumber]['orderExtension']['payPalPaymentId'] = $transId; $orderData[$recordNumber]['orderExtension']['paymentStatus'] = "Complete"; $orderData[$recordNumber]['orderExtension']['transId'] = $transId; $orderData[$recordNumber]['orderExtension']['PayPalPaymentId'] = $PayPalPaymentId; $orderData[$recordNumber]['orderExtension']['paymentMethod'] = $payment_method; $orderData[$recordNumber]['orderExtension']['paymentModule'] = $payment_module; $orderData[$recordNumber]['orderExtension']['shippingMethod'] = $shipping_method; $orderData[$recordNumber]['orderExtension']['ShippingModule'] = $shipping_module; $orderData[$recordNumber]['orderExtension']['currency'] = $currency; $orderData[$recordNumber]['orderExtension']['feedback'] = $feedback; //客户留言 /***************END 订单扩展表数据***************/ /***************BEGIN 订单用户表数据***************/ $orderData[$recordNumber]['orderUserInfo']['username'] = $username; $orderData[$recordNumber]['orderUserInfo']['platformUsername'] = $platformUsername; $orderData[$recordNumber]['orderUserInfo']['email'] = $email; $orderData[$recordNumber]['orderUserInfo']['countryName'] = $countryName; $orderData[$recordNumber]['orderUserInfo']['currency'] = $currency; $orderData[$recordNumber]['orderUserInfo']['state'] = $state; // 省 $orderData[$recordNumber]['orderUserInfo']['city'] = $city; // 市 $orderData[$recordNumber]['orderUserInfo']['street'] = $street; $orderData[$recordNumber]['orderUserInfo']['address2'] = $address2; $orderData[$recordNumber]['orderUserInfo']['landline'] = $landline; // 座机电话 $orderData[$recordNumber]['orderUserInfo']['phone'] = $phone; // 手机 $orderData[$recordNumber]['orderUserInfo']['zipCode'] = $zipCode; // 邮编 /*************END 订单用户表数据***************/ //note信息 if (!empty($note)) { $orderData[$recordNumber]['orderNote']['content'] = $note; } } else { $sku = mysql_real_escape_string(trim($currentSheet->getCell($acc)->getValue())); $itemTitle = mysql_real_escape_string(trim($currentSheet->getCell($add)->getValue())); $itemPrice = round_num(trim($currentSheet->getCell($aff)->getValue()), 2); $amount = intval(trim($currentSheet->getCell($agg)->getValue())); $shipingfee = round_num(trim($currentSheet->getCell($ahh)->getValue()), 2); /***************BEGIN 订单详细数据***************/ $orderData[$recordNumber]['orderDetail'][$c]['orderDetail']['recordNumber'] = $recordnumber; $orderData[$recordNumber]['orderDetail'][$c]['orderDetail']['sku'] = $sku; $orderData[$recordNumber]['orderDetail'][$c]['orderDetail']['itemPrice'] = $itemPrice; $orderData[$recordNumber]['orderDetail'][$c]['orderDetail']['amount'] = $amount; $orderData[$recordNumber]['orderDetail'][$c]['orderDetail']["shippingFee"] = $shipingfee; $orderData[$recordNumber]['orderDetail'][$c]['orderDetail']['createdTime'] = time(); /*************END 订单详细数据***************/ /***************BEGIN 订单详细扩展表数据***************/ $orderData[$recordNumber]['orderDetail'][$c]['orderDetailExtension']['itemTitle'] = $itemTitle; $orderData[$recordNumber]['orderDetail'][$c]['orderDetailExtension']['transId'] = $transId; //$orderData['orderDetail']['orderDetailExtenData']['note'] = $value[10]; $categoryName = trim($currentSheet->getCell($ajj)->getValue()); $customCode = trim($currentSheet->getCell($akk)->getValue()); $material = trim($currentSheet->getCell($all)->getValue()); $ShenBaoQuantity = trim($currentSheet->getCell($amm)->getValue()); $ShenBaoUnitPrice = trim($currentSheet->getCell($ann)->getValue()); $ChineseDesc = trim($currentSheet->getCell($aww)->getValue()); //$salePrice = round_num(mysql_real_escape_string(trim($detail['SalePrice'])), 2); //实际SKU付款价 /*************END 订单详细扩展表数据***************/ $ebay_fedex_remark[$recordNumber][$categoryName][] = array('real_price' => $ShenBaoUnitPrice, 'qty' => $ShenBaoQuantity, 'hamcodes' => $customCode, 'detail' => $material); $orderData[$recordNumber]['fedexRemark'] = $ebay_fedex_remark[$recordNumber]; } } //fedexRemark 逻辑处理 foreach ($orderData as $recordNumber => $tmpArr) { $fedexRemarkArr = $tmpArr['fedexRemark']; $transportId = $orderData['order']['transportId']; if (!empty($fedexRemarkArr)) { $tmpArr1 = array(); foreach ($fedexRemarkArr as $k => $v) { $tmpArr1['description'] = trim("[No Brand]" . $k . "{$v[0]['detail']}"); if (in_array($carrierKV[$transportId], array('FedEx'))) { $tmpArr1['type'] = 1; } elseif (in_array($carrierKV[$transportId], array('DHL', 'EMS', 'UPS美国专线'))) { $tmpArr1['type'] = 2; } else { break; } $sku_price = 0; $qty = 0; foreach ($v as $v0) { $sku_price += $v0['real_price']; $qty += $v0['qty']; } $tmpArr1['price'] = round($sku_price / $qty, 2); $tmpArr1['amount'] = $qty; $tmpArr1['hamcodes'] = $v[0]['hamcodes']; if (in_array($carrierKV[$transportId], array('DHL', 'EMS', 'UPS美国专线'))) { $tmpArr1['price'] = round($sku_price, 2); } } $orderData[$recordNumber]['fedexRemark'] = $tmpArr1; } } var_dump($orderData); exit; //return $this->act_insertOrder($orderData); } else { self::$errMsg[] = get_promptmsg(10053); return false; } }
/** * * @param mixed $value * @param string $originalFieldName * @return \DateTime|null * @throws PhpExcelException */ private function getValueAsDate($value, $originalFieldName) { if (strlen($value) > 0 && !is_numeric($value)) { throw new PhpExcelException("Invalid date value '{$value}'. " . 'Make sure the cell is formatted as date. ' . "Field '{$originalFieldName}', " . "Row '" . $this->currentRow . "'"); } return strlen($value) > 0 ? PhpOffice_PHPExcel_Shared_Date::ExcelToPHPObject($value) : null; }
public function view_dresslinkOrderImport() { include_once WEB_PATH . "lib/PHPExcel.php"; //phpexcel include_once WEB_PATH . "conf/scripts/script.ebay.config.php"; $toptitle = 'dresslink线下订单导入'; //头部title $this->smarty->assign('toptitle', $toptitle); $this->smarty->assign('toplevel', 2); $this->smarty->assign('secondlevel', 210); $OmAccountAct = new OmAccountAct(); $dresslinkAccountList = $OmAccountAct->act_dresslinkaccountAllList(); //$dresslinkAccountList = array(array('id'=>8,'account'=>'dresslink.com'),array('id'=>10,'account'=>'cndirect.com')); $this->smarty->assign("dresslinkAccountList", $dresslinkAccountList); if (isset($_FILES['cndlFile']['tmp_name']) && isset($_POST['cndlAccount'])) { $filePath = $_FILES['cndlFile']['tmp_name']; $account = $_POST['cndlAccount']; $PHPExcel = new PHPExcel(); $PHPReader = new PHPExcel_Reader_Excel2007(); if (!$PHPReader->canRead($filePath)) { $PHPReader = new PHPExcel_Reader_Excel5(); if (!$PHPReader->canRead($filePath)) { echo 'no Excel'; return; } } $PHPExcel = $PHPReader->load($filePath); $currentSheet = $PHPExcel->getSheet(0); $allRow = $currentSheet->getHighestRow(); /**从第二行开始输出,因为excel表中第一行为列名*/ /**取得最大的列号*/ $allColumn = $currentSheet->getHighestColumn(); $orderData = array(); $cndlAccounts = array(); $transportation = CommonModel::getCarrierList(); //所有的 $transportationList = array(); foreach ($transportation as $tranValue) { $transportationList[$tranValue['id']] = $tranValue['carrierNameCn']; } //账号对应 foreach ($dresslinkAccountList as $accounts) { $cndlAccounts[$accounts['id']] = $accounts['account']; } $c = 2; $dresslinks = array(); $ebay_fedex_remark = array(); $ChineseDescs = array(); for ($c = 2; $c <= $allRow; $c++) { $aa = 'A' . $c; $bb = 'B' . $c; $cc = 'C' . $c; $dd = 'D' . $c; $ee = 'E' . $c; $ff = 'F' . $c; $gg = 'G' . $c; $hh = 'H' . $c; $ii = 'I' . $c; $jj = 'J' . $c; $kk = 'K' . $c; $ll = 'L' . $c; $mm = 'M' . $c; $nn = 'N' . $c; $oo = 'O' . $c; $pp = 'P' . $c; $qq = 'Q' . $c; $rr = 'R' . $c; $ss = 'S' . $c; $tt = 'T' . $c; $uu = 'U' . $c; $vv = 'V' . $c; $ww = 'W' . $c; $xx = 'X' . $c; $yy = 'Y' . $c; $zz = 'Z' . $c; $aaa = 'AA' . $c; $abb = 'AB' . $c; $acc = 'AC' . $c; $add = 'AD' . $c; $aee = 'AE' . $c; $aff = 'AF' . $c; $agg = 'AG' . $c; $ahh = 'AH' . $c; $aii = 'AI' . $c; $ajj = 'AJ' . $c; $akk = 'AK' . $c; $all = 'AL' . $c; $amm = 'AM' . $c; $ann = 'AN' . $c; $aww = 'AW' . $c; $recordnumber = trim($currentSheet->getCell($aa)->getValue()); //订单号 $recordNumber = $recordnumber; $is_order = intval($currentSheet->getCell($bb)->getValue()); //1代表为订单,0代表订单明细 if (empty($recordnumber)) { $message .= "<font color=red> 第{$c}行recordnumber为空!</font><br>"; break; } /***************判断订单是否已存在***************/ $where = "where recordnumber='{$recordnumber}'"; $orderinfo = cndlModel::selectOrder($where); if ($orderinfo) { if ($is_order != 0) { $message .= "<font color='blue'>订单 {$recordnumber}已存在!</font><br>"; } continue; } /**************/ if ($is_order != 0) { if ($cndlAccounts[$account] == "dresslink.com") { $str = substr($recordnumber, 0, 2); if ($str !== "DL") { $message .= "<font color=red> {$recordnumber}不在账号{$cndlAccounts[$account]}中!</font><br>"; continue; } } elseif ($cndlAccounts[$account] == "cndirect.com") { $str = substr($recordnumber, 0, 2); if ($str !== "CN") { $message .= "<font color=red> {$recordnumber}不在账号{$cndlAccounts[$account]}中!</font><br>"; continue; } } $platformUsername = mysql_real_escape_string(trim($currentSheet->getCell($cc)->getValue())); $email = mysql_real_escape_string(trim($currentSheet->getCell($dd)->getValue())); $transId = mysql_real_escape_string(trim($currentSheet->getCell($ee)->getValue())); $ordersTime = (array) PHPExcel_Shared_Date::ExcelToPHPObject(trim($currentSheet->getCell($ll)->getValue())); $paymentTime = (array) PHPExcel_Shared_Date::ExcelToPHPObject(trim($currentSheet->getCell($mm)->getValue())); $shippingFee = round_num(trim($currentSheet->getCell($oo)->getValue()), 2); $calcWeight = round_num(trim($currentSheet->getCell($ahh)->getValue()), 3); $actualTotal = round_num(trim($currentSheet->getCell($pp)->getValue()), 2); $onlineTotal = round_num(trim($currentSheet->getCell($aff)->getValue()), 2); $currency = mysql_real_escape_string(trim($currentSheet->getCell($qq)->getValue())); //$orders['ebay_orderqk'] = round_num(trim($currentSheet->getCell($rr)->getValue()), 2); $note = mysql_real_escape_string(trim($currentSheet->getCell($ss)->getValue())); $username = mysql_real_escape_string(trim($currentSheet->getCell($tt)->getValue())); $countryName = mysql_real_escape_string(trim($currentSheet->getCell($uu)->getValue())); $state = mysql_real_escape_string(trim($currentSheet->getCell($vv)->getValue())); $city = mysql_real_escape_string(trim($currentSheet->getCell($ww)->getValue())); $street = mysql_real_escape_string(trim($currentSheet->getCell($xx)->getValue())); $address2 = mysql_real_escape_string(trim($currentSheet->getCell($yy)->getValue())); $zipCode = mysql_real_escape_string(trim($currentSheet->getCell($zz)->getValue())); $phone = mysql_real_escape_string(trim($currentSheet->getCell($abb)->getValue())); $landline = mysql_real_escape_string(trim($currentSheet->getCell($aaa)->getValue())); if ($account == "dresslink.com") { $feedback = mysql_real_escape_string(trim($currentSheet->getCell($ann)->getValue())); } elseif ($account == "cndirect.com") { $feedback = mysql_real_escape_string(trim($currentSheet->getCell($akk)->getValue())); } $carrierNameCn = strtolower(mysql_real_escape_string(trim($currentSheet->getCell($kk)->getValue()))); $carrierNameCn = cndlModel::carrier($carrierNameCn); $payment_method = mysql_real_escape_string(trim($currentSheet->getCell($ff)->getValue())); $payment_module = mysql_real_escape_string(trim($currentSheet->getCell($gg)->getValue())); $bank_account = mysql_real_escape_string(trim($currentSheet->getCell($hh)->getValue())); $bank_country = mysql_real_escape_string(trim($currentSheet->getCell($ii)->getValue())); $shipping_method = mysql_real_escape_string(trim($currentSheet->getCell($jj)->getValue())); $shipping_module = mysql_real_escape_string(trim($currentSheet->getCell($kk)->getValue())); $dresslinks['payment_method'] = $payment_method; $dresslinks['payment_module'] = $payment_module; $dresslinks['bank_account'] = $bank_account; $dresslinks['bank_country'] = $bank_country; $dresslinks['shipping_method'] = $shipping_method; $dresslinks['shipping_module'] = $shipping_module; $PayPalPaymentId = $transId; /***************BEGIN 订单表数据***************/ $orderData[$recordNumber]['orderData']['recordNumber'] = $recordnumber; if ($cndlAccounts[$account] == "dresslink.com") { $orderData[$recordNumber]['orderData']['platformId'] = 10; $orderData[$recordNumber]['orderData']['accountId'] = $account; } elseif ($cndlAccounts[$account] == "cndirect.com") { $orderData[$recordNumber]['orderData']['platformId'] = 8; $orderData[$recordNumber]['orderData']['accountId'] = $account; } $orderData[$recordNumber]['orderData']['orderStatus'] = C('STATEPENDING'); $orderData[$recordNumber]['orderData']['orderType'] = C('STATEPENDING_INITIAL'); $orderData[$recordNumber]['orderData']['ordersTime'] = strtotime($ordersTime['date']); //平台下单时间 $orderData[$recordNumber]['orderData']['paymentTime'] = strtotime($paymentTime['date']); $orderData[$recordNumber]['orderData']['onlineTotal'] = $onlineTotal; //线上总金额 $orderData[$recordNumber]['orderData']['actualTotal'] = $actualTotal; //付款总金额 $orderData[$recordNumber]['orderData']['isFixed'] = 1; $orderData[$recordNumber]['orderData']['calcShipping'] = $shippingFee; //物流费用 $orderData[$recordNumber]['orderData']['orderAddTime'] = time(); $orderData[$recordNumber]['orderData']['isNote'] = empty($note) ? 0 : 1; foreach ($transportation as $tranValue) { if ($tranValue['carrierNameCn'] == $carrierNameCn) { $orderData[$recordNumber]['orderData']['transportId'] = $tranValue['id']; //运输方式id break; } } /***************END 订单表数据***************/ /***************BEGIN 订单扩展表数据***************/ $orderData[$recordNumber]['orderExtenData']['paymentStatus'] = "Complete"; $orderData[$recordNumber]['orderExtenData']['transId'] = $transId; $orderData[$recordNumber]['orderExtenData']['PayPalPaymentId'] = $PayPalPaymentId; $orderData[$recordNumber]['orderExtenData']['paymentMethod'] = $payment_method; $orderData[$recordNumber]['orderExtenData']['paymentModule'] = $payment_module; $orderData[$recordNumber]['orderExtenData']['shippingMethod'] = $shipping_method; $orderData[$recordNumber]['orderExtenData']['ShippingModule'] = $shipping_module; $orderData[$recordNumber]['orderExtenData']['currency'] = $currency; $orderData[$recordNumber]['orderExtenData']['feedback'] = $feedback; //客户留言 /***************END 订单扩展表数据***************/ /***************BEGIN 订单用户表数据***************/ $orderData[$recordNumber]['orderUserInfoData']['username'] = $username; $orderData[$recordNumber]['orderUserInfoData']['platformUsername'] = $platformUsername; $orderData[$recordNumber]['orderUserInfoData']['email'] = $email; $orderData[$recordNumber]['orderUserInfoData']['countryName'] = $countryName; $orderData[$recordNumber]['orderUserInfoData']['currency'] = $currency; $orderData[$recordNumber]['orderUserInfoData']['state'] = $state; // 省 $orderData[$recordNumber]['orderUserInfoData']['city'] = $city; // 市 $orderData[$recordNumber]['orderUserInfoData']['street'] = $street; $orderData[$recordNumber]['orderUserInfoData']['address2'] = $address2; $orderData[$recordNumber]['orderUserInfoData']['landline'] = $landline; // 座机电话 $orderData[$recordNumber]['orderUserInfoData']['phone'] = $phone; // 手机 $orderData[$recordNumber]['orderUserInfoData']['zipCode'] = $zipCode; // 邮编 /*************END 订单用户表数据***************/ //note信息 if (!empty($note)) { $orderData[$recordNumber]['orderNote'][$c]['content'] = $note; $orderData[$recordNumber]['orderNote'][$c]['userId'] = $_SESSION['sysUserId']; } } else { $sku = mysql_real_escape_string(trim($currentSheet->getCell($acc)->getValue())); $itemTitle = mysql_real_escape_string(trim($currentSheet->getCell($add)->getValue())); $itemPrice = round_num(trim($currentSheet->getCell($aff)->getValue()), 2); $amount = intval(trim($currentSheet->getCell($agg)->getValue())); $shipingfee = round_num(trim($currentSheet->getCell($ahh)->getValue()), 2); /***************BEGIN 订单详细数据***************/ $orderData[$recordNumber]['orderDetail'][$c]['orderDetailData']['recordNumber'] = $recordnumber; $orderData[$recordNumber]['orderDetail'][$c]['orderDetailData']['sku'] = $sku; $orderData[$recordNumber]['orderDetail'][$c]['orderDetailData']['itemPrice'] = $itemPrice; $orderData[$recordNumber]['orderDetail'][$c]['orderDetailData']['amount'] = $amount; $orderData[$recordNumber]['orderDetail'][$c]['orderDetailData']["shippingFee"] = $shipingfee; $orderData[$recordNumber]['orderDetail'][$c]['orderDetailData']['createdTime'] = time(); /*************END 订单详细数据***************/ /***************BEGIN 订单详细扩展表数据***************/ $orderData[$recordNumber]['orderDetail'][$c]['orderDetailExtenData']['itemTitle'] = $itemTitle; $orderData[$recordNumber]['orderDetail'][$c]['orderDetailExtenData']['transId'] = $transId; //$orderData['orderDetail']['orderDetailExtenData']['note'] = $value[10]; $categoryName = trim($currentSheet->getCell($ajj)->getValue()); $customCode = trim($currentSheet->getCell($akk)->getValue()); $material = trim($currentSheet->getCell($all)->getValue()); $ShenBaoQuantity = trim($currentSheet->getCell($amm)->getValue()); $ShenBaoUnitPrice = trim($currentSheet->getCell($ann)->getValue()); $ChineseDesc = trim($currentSheet->getCell($aww)->getValue()); //$salePrice = round_num(mysql_real_escape_string(trim($detail['SalePrice'])), 2); //实际SKU付款价 /*************END 订单详细扩展表数据***************/ $ebay_fedex_remark[$recordNumber][$categoryName][] = array('real_price' => $ShenBaoUnitPrice, 'qty' => $ShenBaoQuantity, 'hamcodes' => $customCode, 'detail' => $material); $orderData[$recordNumber]['fedexRemark'] = $ebay_fedex_remark[$recordNumber]; $orderData[$recordNumber]['dresslinkInfo'] = $dresslinks; $ChineseDescs[$recordNumber][$categoryName] = $ChineseDesc; } } if ($orderData) { foreach ($orderData as $id => $order) { $msg = commonModel::checkOrder($id); if ($msg) { $message .= "<font color='red'>订单{$id}已存在!</font><br>"; continue; } //计算订单属性 if (count($order['orderDetail']) == 1) { $detail = current($order['orderDetail']); if ($detail['orderDetailData']['amount'] == 1) { $orderData[$id]['orderData']['orderAttribute'] = 1; } else { $orderData[$id]['orderData']['orderAttribute'] = 2; } } else { $orderData[$id]['orderData']['orderAttribute'] = 3; } //计算订单重量及包材 $obj_order_detail_data = array(); foreach ($order['orderDetail'] as $sku => $detail) { $obj_order_detail_data[] = $detail['orderDetailData']; } $weightfee = commonModel::calcOrderWeight($obj_order_detail_data); $orderData[$id]['orderData']['calcWeight'] = $weightfee[0]; $orderData[$id]['orderData']['pmId'] = $weightfee[1]; //计算运费 $calcShippingInfo = CommonModel::calcAddOrderShippingFee($insertOrder, $orderData[$id]['orderData']['isFixed']); //计算运费 $orderData[$id]['orderData']['channelId'] = $calcShippingInfo['fee']['channelId']; $orderData[$id]['orderData']['calcShipping'] = $calcShippingInfo['fee']; $ChineseDescs_arr = array_filter(array_unique($ChineseDescs[$id])); $orderData[$id]['orderNote'] = array('content' => join(' ', $ChineseDescs_arr), 'userId' => $_SESSION['sysUserId'], 'createdTime' => time()); /*//缺货拦截 $orderData[$id] = AutoModel :: auto_contrast_intercept($orderData[$id]); //$orderData[$id] = cndlModel :: auto_contrast_intercept($orderData[$id]);*/ //插入订单OrderAddModel::insertAllOrderRow($orderData[$id],'cndl'); //调用旧系统接口,先插入数据到旧系统 //echo "<pre>"; var_dump($orderData[$id]); exit; $rtn = OldsystemModel::orderErpInsertorder($orderData[$id]); //echo "<pre>";print_r($rtn);exit; $insertData = array(); if (empty($rtn)) { $message .= "<font color='red'>订单{$id}同步ERP发生异常,跳过!</font><br>"; continue; } if ($rtn['errcode'] == 200) { $rtn_data = $rtn['data']; $orderId = $rtn_data['orderId']; $message .= "<font color='green'>插入老系统成功,订单编号 [{$orderId}]</font><br>"; $pmId = $rtn_data['pmId']; $totalweight = $rtn_data['totalweight']; $shipfee = $rtn_data['shipfee']; $carrier = $rtn_data['carrier']; $carrierId = $rtn_data['carrierId']; $status = $rtn_data['status']; $orderData[$id]['orderData']['id'] = $orderId; //赋予新系统订单编号,一切数据已老系统返回的为准 if ($orderData[$id]['orderData']['calcWeight'] != $totalweight) { $insertData['old_totalweight'] = $totalweight; $insertData['new_totalweight'] = $orderData[$id]['orderData']['calcWeight']; $orderData[$id]['orderData']['calcWeight'] = $totalweight; } if ($orderData[$id]['orderData']['pmId'] != $pmId) { $insertData['old_pmId'] = $pmId; $insertData['new_pmId'] = $orderData[$id]['orderData']['pmId']; $orderData[$id]['orderData']['pmId'] = $pmId; } if ($orderData[$id]['orderData']['calcShipping'] != $shipfee) { $insertData['old_shippfee'] = $shipfee; $insertData['new_shippfee'] = $orderData[$id]['orderData']['calcShipping']; $orderData[$id]['orderData']['calcShipping'] = $shipfee; } if ($orderData[$id]['orderData']['transportId'] != $carrierId) { $insertData['old_carrierId'] = $carrierId; $insertData['new_carrierId'] = $orderData[$id]['orderData']['transportId']; $orderData[$id]['orderData']['transportId'] = $carrierId; } if (!empty($insertData)) { $insertData['ebay_id'] = $orderId; $insertData['addtime'] = time(); OldsystemModel::insertTempSyncRecords($insertData); // 插入临时对比记录表 } //缺货拦截 $orderData[$id] = AutoModel::auto_contrast_intercept($orderData[$id]); //插入订单 $info = OrderAddModel::insertAllOrderRowNoEvent($orderData[$id]); if ($info) { $dresslinkinfos = $orderData[$id]['dresslinkInfo']; $dresslinkinfos['omOrderId'] = $orderId; if (DresslinkinfoModel::insertDresslinkinfoList($dresslinkinfos)) { $message .= "<font color='green'>订单{$id}上传dresslinkInfo成功!</font><br>"; } else { $message .= "<font color='red'>订单{$id}上传dresslinkInfo失败!</font><br>"; } $message .= "<font color='green'>新系统订单{$id}添加成功!</font><br>"; } else { $message .= "<font color='red'>新系统订单{$id}添加失败!</font><br>"; } } else { $message .= "<font color='red'>添加失败,原因为:{$rtn['msg']}!</font><br>"; } if ($orderId) { foreach ($ebay_fedex_remark[$id] as $k => $v) { $fedex_remark = array(); $fedex_remark['description'] = trim("[No Brand]" . $k . "{$v[0]['detail']}"); if (in_array($transportationList[$order['orderData']['transportId']], array('FedEx'))) { //$fedex_remark['description'] = "[No Brand]". $k."({$v[0]['detail']})"; $fedex_remark['type'] = 1; } else { if (in_array($transportationList[$order['orderData']['transportId']], array('DHL', 'EMS', 'UPS美国专线'))) { //$fedex_remark['description'] = trim($k); $fedex_remark['type'] = 2; } else { continue; } } $sku_price = 0; $qty = 0; foreach ($v as $v0) { $sku_price += $v0['real_price']; $qty += $v0['qty']; } //$fedex_remark['ebay_ordersn'] = $order['ebay_ordersn']; $fedex_remark['price'] = round($sku_price / $qty, 2); $fedex_remark['amount'] = $qty; $fedex_remark['hamcodes'] = $v[0]['hamcodes']; if (in_array($transportationList[$order['orderData']['transportId']], array('DHL', 'EMS', 'UPS美国专线'))) { $fedex_remark['price'] = round($sku_price, 2); } $fedex_remark['createdTime'] = time(); $fedex_remark['omOrderId'] = $orderId; $fedex_remark['creatorId'] = $_SESSION['sysUserId']; //$insert_fedex_sql = "INSERT INTO fedex_remark set ".array2sql($fedex_remark); $info = OmAvailableModel::insertRow("om_express_remark", " set " . array2sql_bak($fedex_remark)); if ($info) { $message .= "<font color=green> {$id} 导入海关记录成功!</font><br>"; //echo "----<font color=green> {$order['recordnumber']} 导入海关记录成功!</font><br>"; } else { //echo $insert_fedex_sql; echo "<br>"; $message .= "<font color=green> {$id} 导入海关记录失败!</font><br>"; //echo "----<font color=red>{$order['recordnumber']} 导入海关记录失败!</font><br>"; //$fail_order[] = $order['orderData']['recordnumber']; } } } } } $this->smarty->assign("showerrorinfo", $message); } $this->smarty->display('dresslinkOrderImport.htm'); }
$value = $sheet->getCell("E$i")->getValue(); if ($value!="m" && $value!="f") error ("Invalid gender ('$value') at cell E$i"); //echo "<td>$value</td>"; $gender = $value; $value = $sheet->getCell("F$i")->getValue(); if (!$value) error ("Birth date can't be blank at cell F$i"); if (!is_numeric($value)) { $birthday = $value; if (!checkdate((int)substr($birthday,5,2),(int)substr($birthday,8,2),(int)substr($birthday,0,4))) error ("Invalid birth date format ('$value') at cell F$i"); } else { $date = PHPExcel_Shared_Date::ExcelToPHPObject($value); //echo "<td>".$date->format("Y-m-d")."</td>"; $birthday = $date->format("Y-m-d"); } $r = addCom($wcaid,$name,$birthday,$countryid,$gender,true); if (!is_numeric($r)) error ("Error inserting in database: $r"); //echo "</tr>"; $i++; } //echo "</table>"; echo " Done! <b>".($i-4)." competitors</b> imported.<p>"; $lastabbr = ""; $round = 0;
/** * @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(); }
/** * get the data * * @access public */ public function read($valuetypes = array(), $skip = array(), $emptyvalues = FALSE) { /** * @var array $array_data save parsed data from spreadsheet */ $array_data = array(); foreach ($this->_worksheet->getRowIterator() as $i => $row) { $cellIterator = $row->getCellIterator(); //skip rows in array if (!empty($skip) and in_array($i, $skip)) { continue; } //if ($skip[$i] == $row->getRowIndex()) continue; $rowIndex = $row->getRowIndex(); $values = array(); /** * @var PHPExcel_Cell $cell */ foreach ($cellIterator as $cell) { if (!empty($valuetypes) and array_key_exists($cell->getColumn(), $valuetypes)) { $format = explode(':', $valuetypes[$cell->getColumn()]); switch ($format[0]) { case 'date': $date = PHPExcel_Shared_Date::ExcelToPHPObject($cell->getValue()); $array_data[$rowIndex][$cell->getColumn()] = $date->format($format[1]); break; } } else { // check if is_null or empty $value = $cell->getValue(); $array_data[$rowIndex][$cell->getColumn()] = (strtolower($value) == 'null' or empty($value)) ? null : $cell->getCalculatedValue(); } // For check empty values $values[] = $cell->getValue(); } // Remove rows with all empty cells if ($emptyvalues) { $chechvalues = implode('', $values); if (empty($chechvalues)) { // Delete last array with empty values array_pop($array_data); } } } return (array) $array_data; }
private static function _adjustDateByMonths($dateValue = 0, $adjustmentMonths = 0) { // Execute function $PHPDateObject = PHPExcel_Shared_Date::ExcelToPHPObject($dateValue); $oMonth = (int) $PHPDateObject->format('m'); $oYear = (int) $PHPDateObject->format('Y'); $adjustmentMonthsString = (string) $adjustmentMonths; if ($adjustmentMonths > 0) { $adjustmentMonthsString = '+' . $adjustmentMonths; } if ($adjustmentMonths != 0) { $PHPDateObject->modify($adjustmentMonthsString . ' months'); } $nMonth = (int) $PHPDateObject->format('m'); $nYear = (int) $PHPDateObject->format('Y'); $monthDiff = $nMonth - $oMonth + ($nYear - $oYear) * 12; if ($monthDiff != $adjustmentMonths) { $adjustDays = (int) $PHPDateObject->format('d'); $adjustDaysString = '-' . $adjustDays . ' days'; $PHPDateObject->modify($adjustDaysString); } return $PHPDateObject; }
private static function formatAsDate(&$value, &$format) { // strip off first part containing e.g. [$-F800] or [$USD-409] // general syntax: [$<Currency string>-<language info>] // language info is in hexadecimal $format = preg_replace('/^(\\[\\$[A-Z]*-[0-9A-F]*\\])/i', '', $format); // OpenOffice.org uses upper-case number formats, e.g. 'YYYY', convert to lower-case; // but we don't want to change any quoted strings $format = preg_replace_callback('/(?:^|")([^"]*)(?:$|")/', array('self', 'setLowercaseCallback'), $format); // Only process the non-quoted blocks for date format characters $blocks = explode('"', $format); foreach ($blocks as $key => &$block) { if ($key % 2 == 0) { $block = strtr($block, self::$dateFormatReplacements); if (!strpos($block, 'A')) { // 24-hour time format $block = strtr($block, self::$dateFormatReplacements24); } else { // 12-hour time format $block = strtr($block, self::$dateFormatReplacements12); } } } $format = implode('"', $blocks); // escape any quoted characters so that DateTime format() will render them correctly $format = preg_replace_callback('/"(.*)"/U', array('self', 'escapeQuotesCallback'), $format); $dateObj = PHPExcel_Shared_Date::ExcelToPHPObject($value); $value = $dateObj->format($format); }
function import_excel($WebID = "", $file = "") { global $xoopsDB, $xoopsTpl; if (empty($file) or empty($file)) { return; } $myts = MyTextSanitizer::getInstance(); include_once XOOPS_ROOT_PATH . '/modules/tadtools/PHPExcel/IOFactory.php'; $reader = PHPExcel_IOFactory::createReader('Excel5'); $PHPExcel = $reader->load($file); // 檔案名稱 $sheet = $PHPExcel->getSheet(0); // 讀取第一個工作表(編號從 0 開始) $highestRow = $sheet->getHighestRow(); // 取得總列數 $main = ""; // 一次讀取一列 for ($row = 1; $row <= $highestRow; $row++) { $all = ""; $continue = false; for ($column = 0; $column <= 5; $column++) { if (PHPExcel_Shared_Date::isDateTime($sheet->getCellByColumnAndRow($column, $row))) { $val = PHPExcel_Shared_Date::ExcelToPHPObject($sheet->getCellByColumnAndRow($column, $row)->getValue())->format('Y-m-d'); } else { $val = $sheet->getCellByColumnAndRow($column, $row)->getCalculatedValue(); } if ($column == 0 and $val == _MD_TCW_MEM_NUM) { $continue = true; } if ($column <= 4 and empty($val)) { $continue = true; } if ($row == 1 and $column == 1 and $val == _MD_TCW_DEMO_NAME) { $continue = true; } if ($column == 3 and strlen($val) == 6) { $y = substr($val, 0, 2) + 1911; $m = substr($val, 2, 2); $d = substr($val, 4, 2); $val = "{$y}-{$m}-{$d}"; } if ($column == 10 and strlen($val) == 9 and substr($val, 0, 1) == 9) { $val = "0{$val}"; } $val = $myts->addSlashes($val); $all .= "\n <td>\n <input type='text' name='c[{$row}][{$column}]' value='{$val}' class='form-control span12'>\n </td>\n "; } if ($continue) { continue; } $main .= "<tr>{$all}</tr>"; } $xoopsTpl->assign('op', 'import_excel'); $xoopsTpl->assign('main', $main); }