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);
}
