function getAll()
{
$reader = new Spreadsheet_Excel_Reader();
$reader->setUTFEncoder('iconv');
$reader->setOutputEncoding('UTF-8');
$reader->read($this->filename);
$data = array();
$index_row = 1;
if ($this->have_header) {
$index_row = 2;
}
$iterator = -1;
for ($i = $index_row; $i <= $reader->sheets[$this->sheet_index]['numRows']; $i++) {
$iterator++;
$data[$iterator] = array();
for ($j = 1; $j <= $reader->sheets[$this->sheet_index]['numCols']; $j++) {
if ($this->have_header) {
$data[$iterator][$this->headers[$j - 1]] = $reader->sheets[0]['cells'][$i][$j];
} else {
$data[$iterator][$j - 1] = $reader->sheets[0]['cells'][$i][$j];
}
}
}
return $data;
}
function get_student_schedule($filename, $teacherSchedule)
{
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('UTF8');
$data->setUTFEncoder('mb');
$data->read($filename);
return get_result_student($data, $teacherSchedule);
}
function TestBackupExcel()
{
$filename = 'products_test.xls';
$this->p_ex->DownloadIdMot = 1;
// We will create file with articles, that are connected with id_mot=1
$this->p_ex->DownloadRubriques = array(1);
// We will create file with articles, that lie in rubrique 1
$this->p_ex->BackupExcel($filename);
$full_path = $this->p_ex->GetTmpDir() . $filename;
$this->assertTrue(file_exists($full_path), 'Excel file hasn\'t been created!');
$reader = new Spreadsheet_Excel_Reader();
$reader->setUTFEncoder('iconv');
$reader->setOutputEncoding('UTF-8');
$reader->read($full_path);
$this->assertEqual(1, count($reader->sheets));
// Document must contain 1 sheet
$rows = $reader->sheets[0]["cells"];
$this->assertEqual(3, count($rows));
// Document must contain 1 heading + 1 data = 2 rows
if (file_exists($full_path)) {
unlink($full_path);
}
}
public function importAction()
{
$result = array('success' => true, 'info' => '导入成功');
$request = $this->getRequest()->getParams();
$now = date('Y-m-d H:i:s');
if (isset($_FILES['csv'])) {
$now = date('Y-m-d H:i:s');
$user_session = new Zend_Session_Namespace('user');
$user_id = $user_session->user_info['user_id'];
$file = $_FILES['csv'];
$file_extension = strrchr($file['name'], ".");
$h = new Application_Model_Helpers();
$tmp_file_name = $h->getMicrotimeStr() . $file_extension;
$savepath = "../temp/";
$tmp_file_path = $savepath . $tmp_file_name;
move_uploaded_file($file["tmp_name"], $tmp_file_path);
$plist = new Product_Model_Productlist();
// 读取文件
require_once LIBRARY_PATH . "/Excel/reader.php";
$reader = new Spreadsheet_Excel_Reader();
$reader->setOutputEncoding('UTF-8');
$reader->setUTFEncoder('mb');
$reader->setRowColOffset(1);
$reader->read($tmp_file_path);
error_reporting(E_ALL ^ E_NOTICE);
$data = array();
for ($i = 1; $i <= $reader->sheets[0]['numRows']; $i++) {
$row = array();
for ($j = 1; $j <= $reader->sheets[0]['numCols']; $j++) {
$row[] = $reader->sheets[0]['cells'][$i][$j];
}
$data[] = $row;
}
// 数据校验
if (count($data) <= 1) {
$result['success'] = false;
$result['info'] = "文件中无数据！";
echo Zend_Json::encode($result);
exit;
}
$cols = array("sn", "code", "step", "description", "is_bom_exists", "bom_apply_time", "bom_archive_time", "product_code", "bosa", "bosa_supply", "tosa", "tosa_supply", "rosa", "rosa_supply", "pcb", "pcba", "dg02", "dg01", "product_label", "barcode_label", "label_print_rule", "tooling_model", "sample_send_time", "pra", "trial_produce_qa1", "pmr", "dl", "ipa", "cri", "ds", "dd", "pl", "pes", "pcb_file", "icd", "smt", "mp", "sqr", "dvs", "dvp", "dvr", "dvt", "rtr", "emr", "mtb", "tsq", "sqc", "ed", "pts", "sp", "ep", "fep", "fsp", "ld", "pd", "pg", "nfc", "frm", "pfc", "wi", "other", "create_time", "create_user", "update_time", "update_user");
for ($i = 1; $i < count($data); $i++) {
$num = $i + 1;
$row = $data[$i];
if (count($row) < 2 || !$row[1]) {
continue;
}
$code = $row[1];
if (!$this->checkExists($code)) {
$insertData = array();
$k = 0;
foreach ($cols as $c) {
$insertData[$c] = $row[$k];
$k++;
}
try {
$plist->insert($insertData);
} catch (Exception $e) {
$result['result'] = false;
$result['info'] = $e->getMessage();
echo Zend_Json::encode($result);
exit;
}
} else {
$errors[] = $code;
}
}
$result['error'] = $errors;
}
echo Zend_Json::encode($result);
exit;
}
function extract_text($file, $file0, $source_type, $url, $chrSet)
{
global $db_con, $tmp_dir, $pdftotext_path, $catdoc_path, $xls2csv_path, $op_system, $mb, $debug;
global $catppt_path, $home_charset, $command_line, $no_log, $clear, $converter_dir, $cl, $index_xmeta;
$result = array();
$home_charset1 = str_ireplace('iso-', '', $home_charset);
$charset_int = str_ireplace('iso', '', $home_charset1);
$temp_file = "tmp_file";
$filename = $tmp_dir . "/" . $temp_file;
if ($source_type == 'ods') {
$filename .= "." . $source_type . "";
}
if ($source_type == 'doc') {
$filename .= "." . $source_type . "";
}
if ($source_type == 'docx') {
$filename .= "." . $source_type . "";
}
if ($source_type == 'xlsx') {
$filename .= "." . $source_type . "";
}
if (!($handle = fopen($filename, 'w'))) {
die("Cannot open file {$filename} in temp folder");
}
mysqltest();
if (fwrite($handle, $file) === FALSE) {
die("Cannot write to file {$filename} in temp folder");
}
fclose($handle);
mysqltest();
// for PDF documents enter here
if ($source_type == 'pdf') {
/*
include('../converter/pdf2text.php');
$a = new PDF2Text();
$a->setFilename($filename);
$a->decodePDF();
echo $a->output();
*/
if (!($handle = fopen($pdftotext_path, 'rb'))) {
printStandardReport('errorNoPDFConv', $command_line);
$result[] = 'ERROR';
} else {
// prepare command line for PDF converter
if ($op_system != 'win') {
$command = "" . $pdftotext_path . " -enc UTF-8 " . $filename . "";
} else {
$command = "" . $pdftotext_path . " -cfg xpdfrc " . $filename . " -";
}
$a = exec($command, $result, $retval);
// convert the PDF document
if ($retval != '0') {
// error handler for PDF file converter
if ($retval == '1' || $retval == '3' || $retval == '127') {
if ($retval == '1') {
printStandardReport('errorOpenPDF', $command_line);
}
if ($retval == '3') {
printStandardReport('permissionError', $command_line);
}
if ($retval == '127') {
printStandardReport('noConverter', $command_line);
}
} else {
printStandardReport('ufoError', $command_line);
}
$result[] = 'ERROR';
}
$result = implode(' ', $result);
}
// for DOC and RTF files enter here
} else {
if ($source_type == 'doc' || $source_type == 'rtf') {
/*
echo "\r\n\r\n<br /> op_system: '$op_system'<br />\r\n";
echo "\r\n\r\n<br /> catdoc_path: '$catdoc_path'<br />\r\n";
echo "\r\n\r\n<br /> charset_int: '$charset_int'<br />\r\n";
echo "\r\n\r\n<br /> filename: '$filename'<br />\r\n";
*/
if ($op_system == 'win') {
$command = "" . $catdoc_path . " -s " . $charset_int . " -d utf-8 -x " . $filename . "";
$a = exec($command, $result, $retval);
if (stristr($result[0], "catdoc.exe")) {
printDocReport($result[0], $cl);
}
} else {
$message = " Indexing of .doc and .rtf documents is currently not supported on LINUX OS.";
printDocReport($message, $cl);
/*
$retval = '';
$catdoc_path = str_ireplace("catdoc.exe", "catdoc.lin", $catdoc_path);
//$command = "".$catdoc_path." -cfg xpdfrc ".$filename." -";
$command = "".$catdoc_path." -s ".$charset_int." -d utf-8 -w -x ".$filename."";
$a = exec($command, $result, $retval); // convert the DOC document
//echo "\r\n\r\n<br /> retval: '$retval'<br />\r\n";
//echo "\r\n\r\n<br>result Array:<br><pre>";print_r($result);echo "</pre>\r\n";
if ($retval) {
$result = 'ERROR';
//echo "\r\n\r\n<br /> retval: '$retval'<br />\r\n";
if($retval == '2') {
$message = " File to be converted not found";
printDocReport($message, $cl);
}
else if($retval == '3') {
$message = " Path to file not found.";
printDocReport($message, $cl);
}
else if($retval == '11') {
$message = " The executable is corrupted.";
printDocReport($message, $cl);
}
else if($retval == '12') {
$message = " Out of memory execution.";
printDocReport($message, $cl);
}
else if($retval == '22') {
$message = " dll error";
printDocReport($message, $cl);
}
else if($retval == '31') {
$message = " The association is missing, use Shell to try the OpenWith dialog.";
printDocReport($message, $cl);
}
else if($retval == '32') {
$message = " File could not be opened.";
printDocReport($message, $cl);
}
else if($retval == '126') {
$message = " Command invoked cannot execute (Permission problem or command is not an executable).";
printDocReport($message, $cl);
}
else if($retval == 127) {
$message = " Command not found.";
printDocReport($message, $cl);
}
else if($retval == 128) {
$message = " Invalid argument to exit. Exit takes only integer range 0 – 255.";
printDocReport($message, $cl);
}
else if($retval > 128 && $retval < 255) {
$message = " Fatal error code $retval";
printDocReport($message, $cl);
}
else if($retval == 255) {
$message = " Exit status out of range. Exit takes only integer range 0 – 255.";
printDocReport($message, $cl);
} else {
$message = " Unknown error code $retval.";
printDocReport($message, $cl);
}
}
*/
}
// for PPT files enter here
} else {
if ($source_type == 'ppt') {
// currently unsupported,as a failure was encountered for large PowerPoint presentations
$a = '';
/*
$command = $catppt_path." -s $charset_int -d utf-8 $filename";
$a = exec($command, $result, $retval);
*/
// for XLS spreadsheets enter here
} else {
if ($source_type == 'xls') {
$error = '';
require_once "" . $converter_dir . "/xls_reader.php";
$data = new Spreadsheet_Excel_Reader();
if ($mb == '1') {
// if extention exists, change 'iconv' to mb_convert_encoding:
$data->setUTFEncoder('mb');
}
// set output encoding.
$data->setOutputEncoding('UTF-8');
// read this document
$data->read($filename);
$error = $data->_ole->error;
if ($error == '1') {
printStandardReport('xlsError', $command_line, $no_log);
$result = 'ERROR';
} else {
$result = '';
$boundsheets = array();
$sheets = array();
$boundsheets = $data->boundsheets;
// get all tables in this file
$sheets = $data->sheets;
// get content of all sheets in all tables
if ($boundsheets) {
foreach ($boundsheets as &$bs) {
$result .= "" . $bs['name'] . ", ";
// collect all table names in this file
}
if ($sheets) {
foreach ($sheets as &$sheet) {
$cells = $sheet['cells'];
if ($cells) {
// ignore all empty cells
foreach ($cells as &$cell) {
foreach ($cell as &$content) {
$result .= "" . $content . ", ";
// collect content of all cells
}
}
}
}
}
if (strtoupper($home_charset) == 'ISO-8859-1') {
$result = utf8_encode($result);
}
}
}
// for ODS spreadsheets enter here
} else {
if ($source_type == 'ods') {
require_once "" . $converter_dir . "/ods_reader.php";
$reader = ods_reader::reader($filename);
$sheets = $reader->read($filename);
if ($sheets) {
$result = '';
foreach ($sheets as &$sheet) {
if ($sheet) {
foreach ($sheet as &$cell) {
if ($cell) {
// ignore all empty cells
foreach ($cell as &$content) {
$result .= "" . $content . " ";
// collect content of all cells
}
}
}
}
}
} else {
$result = 'ERROR';
}
// for ODT documents enter here
} else {
if ($source_type == 'odt') {
require_once "" . $converter_dir . "/odt_reader.php";
$x = new odt_reader();
// Unzip the document
$u = $x->odt_unzip($filename, false);
// read the document
$result = $x->odt_read($u[0], 2);
// create some blanks around the <div> tags
$result = str_replace("<", " <", $result);
$result = str_replace(">", "> ", $result);
//echo "\r\n\r\n<br /> odt result: $result<br />\r\n";
// for DOCX files enter here
} else {
if ($source_type == 'docx') {
// converter class supplied by http://www.phpdocx.com
$options = array('paragraph' => false, 'list' => false, 'table' => false, 'footnote' => false, 'endnote' => false, 'chart' => 0);
$docx_file = "docx.txt";
$result = '';
require_once "" . $converter_dir . "/docx/CreateDocx.inc";
CreateDocx::DOCX2TXT($filename, $tmp_dir . "/" . $docx_file, $options);
if ($file = @file_get_contents($tmp_dir . "/" . $docx_file)) {
$result = "{$file} ";
}
if ($index_xmeta) {
require_once "" . $converter_dir . "/xmeta_converter.php";
$docxmeta = new x_metadata();
$docxmeta->setDocument($filename);
/*
echo "Title : " . $docxmeta->getTitle() . "<br>";
echo "Subject : " . $docxmeta->getSubject() . "<br>";
echo "Creator : " . $docxmeta->getCreator() . "<br>";
echo "Keywords : " . $docxmeta->getKeywords() . "<br>";
echo "Description : " . $docxmeta->getDescription() . "<br>";
echo "Last Modified By : " . $docxmeta->getLastModifiedBy() . "<br>";
echo "Revision : " . $docxmeta->getRevision() . "<br>";
echo "Date Created : " . $docxmeta->getDateCreated() . "<br>";
echo "Date Modified : " . $docxmeta->getDateModified() . "<br>";
*/
$result .= $docxmeta->getTitle() . $docxmeta->getSubject() . $docxmeta->getCreator() . $docxmeta->getKeywords() . $docxmeta->getDescription() . $docxmeta->getLastModifiedBy() . $docxmeta->getRevision() . $docxmeta->getDateCreated() . $docxmeta->getDateModified();
}
@unlink($tmp_dir . "/" . $docx_file);
/*
if($result && $chrSet != "UTF-8") {
$result = @mb_convert_encoding($result, "UTF-8", $chrSet);
}
*/
// for XLSX spreadsheets enter here
} else {
if ($source_type == 'xlsx') {
$result = '';
$i = 1;
$name = '';
$finished = false;
$names = array();
require_once "" . $converter_dir . "/xlsx_reader.php";
$xlsx = new SimpleXLSX($filename);
$names = $xlsx->sheetNames();
//echo "\r\n\r\n<br>names array:<br><pre>";print_r($names);echo "</pre>\r\n";
if ($debug == 2 && $names) {
printXLSXreport(count($names), $cl);
}
foreach ($names as $my_name) {
$result .= $my_name . " ";
if ($debug == 2) {
printActKeyword($my_name);
}
}
while (!$finished) {
// get all sheets
if ($rows = $xlsx->rows($i)) {
foreach ($rows as $key) {
foreach ($key as $val) {
if ($val) {
$result .= " " . $val;
// add value of each cell
}
}
}
} else {
$finished = true;
// no more sheets found
}
//$my_name = $xlsx->sheetName($i);
//echo "\r\n\r\n<br /> sheet name $i: '$my_name'<br />\r\n";
$i++;
// try to get next sheet
}
if ($index_xmeta) {
require_once "" . $converter_dir . "/xmeta_converter.php";
$xlscxmeta = new x_metadata();
$xlscxmeta->setDocument($filename);
/*
echo "Title : " . $xlscxmeta->getTitle() . "<br>";
echo "Subject : " . $xlscxmeta->getSubject() . "<br>";
echo "Creator : " . $xlscxmeta->getCreator() . "<br>";
echo "Keywords : " . $xlscxmeta->getKeywords() . "<br>";
echo "Description : " . $xlscxmeta->getDescription() . "<br>";
echo "Last Modified By : " . $xlscxmeta->getLastModifiedBy() . "<br>";
echo "Revision : " . $xlscxmeta->getRevision() . "<br>";
echo "Date Created : " . $xlscxmeta->getDateCreated() . "<br>";
echo "Date Modified : " . $xlscxmeta->getDateModified() . "<br>";
*/
$result .= $xlscxmeta->getTitle() . $xlscxmeta->getSubject() . $xlscxmeta->getCreator() . $xlscxmeta->getKeywords() . $xlscxmeta->getDescription() . $xlscxmeta->getLastModifiedBy() . $xlscxmeta->getRevision() . $xlscxmeta->getDateCreated() . $xlscxmeta->getDateModified();
}
/*
if($result && $chrSet != "UTF-8") {
$result = @mb_convert_encoding($result, "UTF-8", $chrSet);
}
*/
// for JavaScript enter here
} else {
if ($source_type == 'js') {
$result = extract_js($file);
}
}
}
}
}
}
}
}
}
if ($result != 'ERROR') {
if (is_array($result)) {
$result = implode(" ", $result);
}
$count = strlen($result);
if ($count == '0') {
// if there was not one word found, print warning message
if ($source_type == 'js') {
printStandardReport('jsEmpty', $command_line, $no_log);
} else {
printStandardReport('nothingFound', $command_line, $no_log);
}
$result = 'ERROR';
}
}
unlink($filename);
mysqltest();
if ($clear == 1) {
unset($command, $retval, $a, $file, $count);
}
return $result;
}
public static function xls2Array($file, $DOCUMENT_ROOT, $setOutput = false)
{
mb_internal_encoding("ISO-8859-1");
require_once $DOCUMENT_ROOT . '/vendor/excel/excel_reader2.php';
$data = new Spreadsheet_Excel_Reader();
if ($setOutput) {
$data->setOutputEncoding('CP-1251');
}
$data->setUTFEncoder('mb');
$data->read($file);
$arStrings = $data->sheets;
return $arStrings[0]["cells"];
}
$file = "{$url_path}{$file}";
$short_desc = '';
$title = '';
$required = '';
$disallowed = '';
$can_leave_domain = '';
$parent_num = "0";
$lines = array();
// read .xls files into an array
if (stristr($file, ".xls")) {
$error = '';
require_once "" . $converter_dir . "/xls_reader.php";
$data = new Spreadsheet_Excel_Reader();
if ($mb == '1') {
// if extention exists, change 'iconv' to mb_convert_encoding:
$data->setUTFEncoder('mb');
}
// set output encoding.
$data->setOutputEncoding('UTF-8');
// read this document
$data->read($file);
$error = $data->_ole->error;
if ($error == '1') {
printStandardReport('xlsError', $command_line, $no_log);
$result = 'ERROR';
} else {
$result = ' ';
$boundsheets = array();
$sheets = array();
$boundsheets = $data->boundsheets;
// get all tables in this file
public function uploadAction()
{
error_reporting(E_ALL);
$this->_initLayoutMessages('core/session');
$messages = Mage::getSingleton("customer/session")->getMessages();
$post_data = Mage::app()->getRequest()->getPost();
$uploadFiletype = $post_data['type'];
$redirectUrl = $post_data['admin_url'];
$file_name = $_FILES["file"]["name"];
$file_type = $_FILES["file"]["type"];
// File Type check
if ($file_type == 'application/vnd.ms-excel') {
// Code for File upload
if (isset($_FILES['file']['name']) && $_FILES['file']['name'] != "") {
$uploader = new Varien_File_Uploader("file");
$uploader->setAllowedExtensions(array('xls'));
$uploader->setAllowRenameFiles(false);
$uploader->setFilesDispersion(false);
$path = Mage::getBaseDir("media") . DS . "upload" . DS;
$uploader->save($path);
}
$excel_reader_path = Mage::getBaseDir("media") . DS . "Excel" . DS;
$file_sourece = $path . $_FILES['file']['name'];
require_once $excel_reader_path . 'excel_reader2.php';
$excel_reader = new Spreadsheet_Excel_Reader();
$excel_reader->setUTFEncoder('iconv');
$excel_reader->setOutputEncoding('CP1251');
$file = $excel_reader->read($file_sourece, "UTF-16");
$file_row = 3;
$column_count = $excel_reader->sheets[0]['numCols'];
$row_count = $excel_reader->sheets[0]['numRows'];
$row_check = 0;
for ($file_row; $file_row <= $excel_reader->sheets[0]['numRows']; $file_row++) {
@($data1 = addslashes($excel_reader->sheets[0]['cells'][$file_row][1]));
@($data2 = addslashes($excel_reader->sheets[0]['cells'][$file_row][2]));
@($data3 = addslashes($excel_reader->sheets[0]['cells'][$file_row][3]));
@($data4 = $excel_reader->sheets[0]['cells'][$file_row][4]);
@($data5 = addslashes($excel_reader->sheets[0]['cells'][$file_row][5]));
@($data6 = addslashes($excel_reader->sheets[0]['cells'][$file_row][6]));
$pincode = $data4;
$collection = Mage::getModel('fileupload/fileupload')->getCollection();
$collection->addFieldToFilter('pincode', array('like' => '%' . $pincode . '%'));
$getDatas = $collection->getData();
/* echo "<pre>";
print_r($getDatas);
echo "Count ".count($getDatas);
echo "</pre>";
exit;*/
$connection = Mage::getSingleton('core/resource')->getConnection('core_write');
$connection->beginTransaction();
$sql = 'INSERT INTO fileupload (`location`, `branch_detail`, `served_by`, `pincode`, `area`, `file_type`, `state`) VALUES("' . $data1 . '","' . $data2 . '","' . $data3 . '","' . $data4 . '","' . $data5 . '","' . $uploadFiletype . '", "' . $data6 . '")';
$sql;
$checkData = $connection->query($sql);
$connection->commit();
$row_check++;
}
$this->_redirect('fileupload/adminhtml_fileupload/index/key/' . $redirectUrl . '?fstatus=1&data_cnt=' . $row_check);
} else {
$this->_redirect('fileupload/adminhtml_fileupload/index/key/' . $redirectUrl . '?fstatus=2');
}
}
/**
* Update products from given Excel file
* @param string $file_path Excel file path
*/
public function updateProductsFromExcel($file_path, $primary_key = 'id_article', $create_new = false, $id_reserv = 0)
{
require_once _DIR_PLUGIN_EXCEL . "pear/Spreadsheet/Excel/Reader.php";
ini_set('max_execution_time', 0);
switch ($primary_key) {
case 'id_article':
$col_primary = 1;
break;
case 'sku':
$col_primary = 2;
break;
default:
$col_primary = 1;
break;
}
$path_info = pathinfo($file_path);
$type_file = $path_info['extension'];
if ($type_file == 'csv') {
//echo '<h1>THIS CSV FILE!!!</h1>';
$fd = fopen($file_path, "r");
$i = 0;
while (!feof($fd)) {
$fields_win1251 = fgets($fd);
$fields_UTF8 = iconv("windows-1251", "UTF-8", $fields_win1251);
$fields = explode(";", $fields_UTF8);
$product_article = $fields[0] != 0 ? $fields[0] : $field[4];
$product_name = $fields[1];
$product_manufacture = $fields[2];
$product_price = $fields[3];
$product_key = $fields[4];
$product_id_rubrique = $fields[5];
$product_id_extended_rubrique = $fields[6];
$id_article = false;
$primary_val = $row["{$col_primary}"];
// First cell has index 1!
//if ($primary_val) {
$product_data = array();
$product_data['sku'] = $product_article;
$product_data['titre'] = $product_name;
$product_data['extra1'] = $product_manufacture;
$product_data['extra2'] = $product_key;
$product_data['price'] = $product_price;
$article_data = array();
$article_data['titre'] = $product_name;
// Update article status?
if ($this->UpdateStatus) {
$quantity = intval($product_data['quantity']);
if ($quantity > 0) {
$article_data['statut'] = 'publie';
} else {
$article_data['statut'] = 'prepa';
}
}
// To avoid ambiguity select by id if $primary_key = 'id_article'
if ($primary_key == 'id_article' && $primary_val) {
$product_exist = $this->product_mdl->select_by_id(intval($primary_val));
} else {
if ($primary_val) {
$product_exist = $this->product_mdl->select($primary_key . " = '" . mysql_real_escape_string($primary_val) . "'");
if ($product_exist) {
$product_exist = $product_exist[0];
}
} else {
$product_exist = false;
}
}
if ((!isset($product_exist) || !$product_exist) && $create_new) {
// Create new product?
$id_rubrique = intval($product_id_extended_rubrique);
if (!$id_rubrique) {
$id_rubrique = intval($product_id_rubrique);
if (!$id_rubrique) {
$id_rubrique = $id_reserv;
}
// to do make reserv
}
$rubrique = $this->rubrique_mdl->select_by_id($id_rubrique);
if (!$rubrique) {
$rubrique = $this->rubrique_mdl2->get_rubric_by_id_import($id_rubrique);
$id_rubrique = $rubrique['id_rubrique'];
}
//spip_log($rubrique, 'excel.'._LOG_ERREUR);
//echo '<pre>';
//print_r($rubrique);
//echo '</pre>';
//exit;
if (!$rubrique) {
continue;
}
$article_data['id_rubrique'] = $id_rubrique;
$article_data['id_secteur'] = $rubrique['id_secteur'];
$article_data['statut'] = 'prepa';
// For new article statut must be defined
$article_data['lang'] = $rubrique['lang'];
// Insert article
if (!($id_article = $this->article_mdl->insert($article_data))) {
throw new Exception("Error while inserting article " . $primary_val);
}
// Insert product
$product_data['id_article'] = $id_article;
if (!$this->product_mdl->insert($product_data)) {
throw new Exception("Error while inserting product #" . $id_article);
}
} else {
if ($product_exist) {
// Update old product
$id_article = $product_exist['id_article'];
$id_rubrique = intval($product_id_extended_rubrique);
if (!$id_rubrique) {
$id_rubrique = intval($product_id_rubrique);
if (!$id_rubrique) {
$id_rubrique = $id_reserv;
}
// to do make reserv
}
$rubrique = $this->rubrique_mdl->select_by_id($id_rubrique);
if (!$rubrique) {
$rubrique = $this->rubrique_mdl2->get_rubric_by_id_import($id_rubrique);
$id_rubrique = $rubrique['id_rubrique'];
}
if (!$rubrique) {
continue;
}
$article_data['id_rubrique'] = $id_rubrique;
$article_data['id_secteur'] = $rubrique['id_secteur'];
// Update product
if (!$this->product_mdl->update_by_id($id_article, $product_data)) {
throw new Exception("Error while updating product #" . $id_article);
}
// Update article
if (!$this->article_mdl->update_by_id($id_article, $article_data)) {
throw new Exception("Error while updating article #" . $id_article);
}
}
}
$i++;
spip_log("{$i}", 'excel.' . _LOG_ERREUR);
}
fclose($fd);
} else {
$reader = new Spreadsheet_Excel_Reader();
$reader->setUTFEncoder('iconv');
$reader->setOutputEncoding('UTF-8');
$reader->read($file_path);
spip_log("EXCEL IMPORT START", 'excel.' . _LOG_ERREUR);
foreach ($reader->sheets as $k => $data) {
// $reader->boundsheets[$k]['name']; // Sheet name
foreach ($data["cells"] as $row_num => $row) {
if ($row_num == 1 || $row_num == 2) {
spip_log($row_num, 'excel.' . _LOG_ERREUR);
spip_log('row_0' . $row[0], 'excel.' . _LOG_ERREUR);
spip_log('row_1' . $row[1], 'excel.' . _LOG_ERREUR);
spip_log('row_2' . $row[2], 'excel.' . _LOG_ERREUR);
spip_log('row_3' . $row[3], 'excel.' . _LOG_ERREUR);
spip_log('row_4' . $row[4], 'excel.' . _LOG_ERREUR);
spip_log('row_5' . $row[5], 'excel.' . _LOG_ERREUR);
spip_log('row_6' . $row[6], 'excel.' . _LOG_ERREUR);
spip_log('row_7' . $row[7], 'excel.' . _LOG_ERREUR);
}
if ($row_num == 1) {
continue;
}
//omit first row
$id_article = false;
$primary_val = $row["{$col_primary}"];
// First cell has index 1!
//if ($primary_val) {
$product_data = array();
$product_data['sku'] = $row["2"];
//$product_data['titre'] = $row["4"];
$product_data['quantity'] = $row["6"];
$product_data['price'] = $row["7"];
$product_data['priceold'] = $row["8"];
$article_data = array();
$article_data['titre'] = $row["5"];
// Update article status?
if ($this->UpdateStatus) {
$quantity = intval($product_data['quantity']);
if ($quantity > 0) {
$article_data['statut'] = 'publie';
} else {
$article_data['statut'] = 'prepa';
}
}
// To avoid ambiguity select by id if $primary_key = 'id_article'
if ($primary_key == 'id_article' && $primary_val) {
$product_exist = $this->product_mdl->select_by_id(intval($primary_val));
} else {
if ($primary_val) {
$product_exist = $this->product_mdl->select($primary_key . " = '" . mysql_real_escape_string($primary_val) . "'");
if ($product_exist) {
$product_exist = $product_exist[0];
}
} else {
$product_exist = false;
}
}
if ((!isset($product_exist) || !$product_exist) && $create_new) {
// Create new product?
$id_rubrique = intval($row['3']);
if (!$id_rubrique) {
$id_rubrique = intval($row['4']);
}
spip_log('ID_RUBRIQUE:' . $id_rubrique, 'excel.' . _LOG_ERREUR);
if (!$id_rubrique) {
continue;
}
$rubrique = $this->rubrique_mdl->select_by_id($id_rubrique);
if (!$rubrique) {
$rubrique = $this->rubrique_mdl2->get_rubric_by_id_import($id_rubrique);
$id_rubrique = $rubrique['id_rubrique'];
}
//spip_log($rubrique, 'excel.'._LOG_ERREUR);
//echo '<pre>';
//print_r($rubrique);
//echo '</pre>';
//exit;
if (!$rubrique) {
continue;
}
$article_data['id_rubrique'] = $id_rubrique;
$article_data['id_secteur'] = $rubrique['id_secteur'];
$article_data['statut'] = 'prepa';
// For new article statut must be defined
$article_data['lang'] = $rubrique['lang'];
// Insert article
if (!($id_article = $this->article_mdl->insert($article_data))) {
throw new Exception("Error while inserting article " . $primary_val);
}
// Insert product
$product_data['id_article'] = $id_article;
if (!$this->product_mdl->insert($product_data)) {
throw new Exception("Error while inserting product #" . $id_article);
}
} else {
if ($product_exist) {
// Update old product
$id_article = $product_exist['id_article'];
// Update product
if (!$this->product_mdl->update_by_id($id_article, $product_data)) {
throw new Exception("Error while updating product #" . $id_article);
}
// Update article
if (!$this->article_mdl->update_by_id($id_article, $article_data)) {
throw new Exception("Error while updating article #" . $id_article);
}
}
}
//}
}
}
// end foreach woorkbook
}
}
/**
* импорт товаров
*/
function importAction()
{
if ($_POST) {
// коннектим функцию
require_once ADMIN_PATH . '/plagin/excel/reader.php';
// ExcelFile($filename, $encoding);
$data = new Spreadsheet_Excel_Reader();
// Set output Encoding.
$data->setOutputEncoding('UTF8');
$data->setUTFEncoder('mb');
$data->read($_FILES['file1']['tmp_name']);
error_reporting(E_ALL ^ E_NOTICE);
$_str = array();
// С отключением товара
if ($_POST['type_import'] == 0) {
// обнуляем склад
$_sql = "UPDATE `catalog` SET `sklad`='0' WHERE (`supplier`='" . $_POST['supplier'] . "')";
$result = mysql::just_query($_sql, 0);
}
for ($i = 2; $i <= $data->sheets[0]['numRows']; $i++) {
for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {
$_str[] = $data->sheets[0]['cells'][$i][$j];
}
$_sql = "SELECT * FROM `catalog` WHERE (`artikul`='" . $_str[0] . "')";
$result_artikul = mysql::query_one($_sql, 0);
if ($result_artikul) {
// работаем с ценой
if ($_str[2] == "") {
$_sql = "UPDATE `catalog` \n\t\t\t\t\t\t\t\tSET `name`='" . $_str[1] . "', `sklad`='2'\n\t\t\t\t\t\t\t\tWHERE (`artikul`='" . $_str[0] . "')";
$result = mysql::just_query($_sql, 0);
} else {
$_sql = "UPDATE `catalog` \n\t\t\t\t\t\t\t\tSET `name`='" . $_str[1] . "', `sklad`='1'\n\t\t\t\t\t\t\t\tWHERE (`artikul`='" . $_str[0] . "')";
$result = mysql::just_query($_sql, 0);
}
// C обновленимем цены
if ($_POST['type_import1'] == 0) {
$_sql = "UPDATE `catalog` \n\t\t\t\t\t\t\t\tSET `cost`='" . $_str[3] . "'\n\t\t\t\t\t\t\t\tWHERE (`artikul`='" . $_str[0] . "')";
$result = mysql::just_query($_sql, 0);
}
} else {
// товара нет на сайте
if ($_str[2] == "") {
$_sklad = 2;
} else {
$_sklad = 1;
}
$_sql = "INSERT INTO `catalog` (`artikul`, `name`, `cost`, `id_parent`, `status`, `created_at`, `sklad`, `supplier`) \n\t\t\t\t\t\t\tVALUES ('" . $_str[0] . "', '" . $_str[1] . "', '" . $_str[3] . "', '1', '1', '" . date('Y-m-d') . "', '" . $_sklad . "', '" . $_POST['supplier'] . "')";
$result = mysql::just_query($_sql, 0);
}
$_str = "";
}
}
// строим запрос
$_sql = 'SELECT * FROM `supplier` where status=1 order by sort';
// выполняем запрос + при необходимости выводим сам запрос
$select = mysql::query($_sql, 0);
return system::show_tpl(array('msg' => $this->msg, 'tpl_folder' => $this->tpl_folder, 'select' => $select), $this->tpl_folder . '/import.php');
}
