public function load_xls($files = NULL, $gid, $offset = 11, $limit = 20) { $material = new Model_Material('groups'); $base = new Model_Base(); $group = $material->getGroup($gid); $insertdata = array(); $fieldset = $material->getFieldsSet($gid); if (is_array($files)) { if (isset($files) && count($files) > 0) { $files_size = $files['xls_price']['size']; } else { $files_size = $_GET['files_size']; } if (isset($files) && count($files) > 0) { $temp = $files['xls_price']['name']; } else { $temps = explode('_', $_GET['files_name']); $temp = implode(' ', $temps); } $name_arr = explode('.', $temp); $ext = array_pop($name_arr); if (isset($files) && count($files) > 0) { $tmp_name = $_FILES['xls_price']['tmp_name']; } else { $tmp_name = $_GET['files_tmp']; } } else { $files_size = filesize($files); $ext = pathinfo($files, PATHINFO_EXTENSION); $loadedfilename = $files; } $create_categories = TRUE; $load_data = TRUE; if ($create_categories == TRUE) { if ($files_size > 0 && ($ext == 'xls' || $ext == 'xlsx')) { //echo $files['xls_price']['size']; //get upload file if (!isset($loadedfilename)) { $uploadfile = $this->uploads_dir() . $temp; } else { $uploadfile = $loadedfilename; $tmp_name = $loadedfilename; } //if lile moved if (move_uploaded_file($tmp_name, $uploadfile) || isset($_GET['files_name']) || isset($loadedfilename)) { //load file $objPHPExcel = PHPExcel_IOFactory::load($uploadfile); //foreach pages foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) { $worksheetTitle = $worksheet->getTitle(); $highestRow = $worksheet->getHighestRow(); // например, 10 $highestColumn = $worksheet->getHighestColumn(); // например, 'F' $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); $nrColumns = ord($highestColumn) - 64; //получаем все поля для группы //$fieldset = $material->getFieldsSet($gid); //перебираем страницы файла foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) { $fields_index = array(); $worksheetTitle = $worksheet->getTitle(); $highestRow = $worksheet->getHighestRow(); // например, 10 $highestColumn = $worksheet->getHighestColumn(); // например, 'F' $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); $nrColumns = ord($highestColumn) - 64; if ($highestRow > $limit) { $highestRow = $offset + $limit; } foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) { for ($row = $offset; $row <= $highestRow; ++$row) { $group_name = Arr::get($group, 'title_field', 'A') . $row; $group_nameCell = $worksheet->getCell($group_name); $insertdata[$row]['group_name'] = $group_nameCell->getValue(); $title = 'D' . $row; $titleCell = $worksheet->getCell($title); $insertdata[$row]['title'] = $titleCell->getValue(); $insertdata[$row]['url'] = Request::str2url($insertdata[$row]['title']); $insertdata[$row]['description'] = $insertdata[$row]['title']; $article = 'B' . $row; $articleCell = $worksheet->getCell($article); $insertdata[$row]['article'] = array($articleCell->getValue()); $count = 'P' . $row; $countCell = $worksheet->getCell($count); $insertdata[$row]['count'] = array($countCell->getValue()); $made_in = 'E' . $row; $made_inCell = $worksheet->getCell($made_in); $insertdata[$row]['madein'] = array($made_inCell->getValue()); $trademark = 'F' . $row; $trademarkCell = $worksheet->getCell($trademark); $insertdata[$row]['trademark'] = array($trademarkCell->getValue()); $photos = 'G' . $row; $photosCell = $worksheet->getCell($photos)->getHyperlink()->getUrl(); $insertdata[$row]['photos'] = array($photosCell); $price = 'S' . $row; $priceCell = $worksheet->getCell($price); $insertdata[$row]['price'] = $priceCell->getOldCalculatedValue(); } } } } // echo '<pre>'; // var_dump($insertdata); // echo '</pre>'; $params = array(0 => array('type' => 'select', 'title' => 'Страна производителя', 'name' => 'madein', 'ordering' => '3'), 1 => array('type' => 'select', 'title' => 'Торговая марка', 'name' => 'trademark', 'ordering' => '4'), 2 => array('type' => 'photoalbum', 'title' => 'Фото', 'name' => 'photos', 'ordering' => '1'), 3 => array('type' => 'price', 'title' => 'Цена', 'name' => 'price', 'ordering' => '2')); foreach ($insertdata as $id => $group) { $names = Arr::get($group, 'group_name'); $groupnames = explode('\\', $names); $parent = '602'; $groupnames = array_reverse($groupnames); $i = 1; foreach ($groupnames as $name) { if ($name != '') { $groups = new Model_Material('groups'); if ($parent) { //проверяем наличие такой группы у родителя $res = $groups->getGroupChildren($parent, $name); if (!$res) { //если нет создаем //$newGroupId = $groups->groupInsert($parent, $parent, array('name' => $name)); //$parent = $newGroupId; } else { if ($load_data == TRUE) { //загружаем данные в группу при условии, что это последняя группа if ($i == count($groupnames)) { // $fieldset = $material->getFieldsSet($res['id']); $result = $material->unique_url($group['url'], $res['id']); // foreach($fieldset as $field) // { // if(isset($group[$field['name']])) // { // $gname = $field['type'].'_'.$field['id']; // $gvalue = $group[$field['name']]; // $group[$gname] = $gvalue; // unset($group[$field['name']]); // } // } // // echo "<pre>"; // var_dump($group); // echo "</pre>"; if ($result) { $fieldset = $material->getFieldsSet($res['id']); foreach ($fieldset as $field) { if (isset($group[$field['name']])) { $gname = $field['type'] . '_' . $field['id']; $gvalue = $group[$field['name']]; $group[$gname] = $gvalue; unset($group[$field['name']]); } } unset($group['group_name']); $result = $material->newMaterial($group, $res['id'], TRUE, TRUE); } // if(!$res) // { // $group['mid'] = $material->getMaterialIdByUrl($group['url'], $res['id']); // if(isset($group['sleep'])) // unset($group['sleep']); // // $result = $material->saveMaterial($group, $res['id'], TRUE, TRUE); // } // else // { // $result = $material->newMaterial($group, $res['id'], TRUE, TRUE); // } } } $parent = $res['id']; } } } //создаем стандартный набор параметров if ($i == count($groupnames)) { foreach ($params as $param) { $param['gid'] = $parent; //получаем параметры группы $groups->addParam($param); } } ++$i; } } // if(is_array($insertdata)) // { // foreach($insertdata as $item) // { // $id = $material->unique_url($item['url'], $gid); // if(!$id) // { // $item['mid'] = $material->getMaterialIdByUrl($item['url'], $gid); // if(isset($item['sleep'])) // unset($item['sleep']); // // $result = $material->saveMaterial($item, $gid, TRUE); // } // else // { // $result = $material->newMaterial($item, $gid, TRUE); // } // } // } } } else { echo '<div class="error_box">Не выбран файл или он неверного формата</div>'; } } else { if ($files_size > 0 && ($ext == 'xls' || $ext == 'xlsx')) { //echo $files['xls_price']['size']; //get upload file $uploadfile = $this->uploads_dir() . $temp; //if lile moved if (move_uploaded_file($tmp_name, $uploadfile) || isset($_GET['files_name'])) { //load file $objPHPExcel = PHPExcel_IOFactory::load($uploadfile); //foreach pages foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) { $worksheetTitle = $worksheet->getTitle(); $highestRow = $worksheet->getHighestRow(); // например, 10 $highestColumn = $worksheet->getHighestColumn(); // например, 'F' $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); $nrColumns = ord($highestColumn) - 64; //получаем все поля для группы $fieldset = $material->getFieldsSet($gid); //перебираем страницы файла foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) { $fields_index = array(); $worksheetTitle = $worksheet->getTitle(); $highestRow = $worksheet->getHighestRow(); // например, 10 $highestColumn = $worksheet->getHighestColumn(); // например, 'F' $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); $nrColumns = ord($highestColumn) - 64; foreach ($fieldset as $field) { if ($worksheetTitle === $field['xls_page']) { $fields_index[$field['xls_col']] = $field['type'] . '_' . $field['id']; } } for ($row = Arr::get($group, 'first_row', 0); $row <= $highestRow; ++$row) { //формируем заголовоки массива $title = Arr::get($group, 'title_field', 'А') . $row; $titleCell = $worksheet->getCell($title); $desc = Arr::get($group, 'desc_field', 'А') . $row; $descCell = $worksheet->getCell($desc); $insertdata[$row]['title'] = Request::clearText($titleCell->getValue()); // $insertdata[$row]['title'] = $streetCell->getValue().' '.$houseCell->getValue().$korpus.$liter; $insertdata[$row]['url'] = Request::str2url($insertdata[$row]['title']); $insertdata[$row]['description'] = Request::clearText($descCell->getValue()); foreach ($fields_index as $key => $value) { $cell = $worksheet->getCell($key . $row); $ftype = explode('_', $value); if (Arr::get($ftype, 0) == 'chars' || Arr::get($ftype, 0) == 'texts') { $insertdata[$row][$value] = strval($cell->getValue()); } elseif (Arr::get($ftype, 0) == 'select' || Arr::get($ftype, 0) == 'date' || Arr::get($ftype, 0) == 'files' || Arr::get($ftype, 0) == 'photoalbum') { $param = $material->getParam(Arr::get($ftype, 1)); if ($param && isset($param['delimetr']) && $param['delimetr'] != '') { $arr = explode($param['delimetr'], strval($cell->getValue())); $arr = array_map('trim', $arr); $insertdata[$row][$value] = $arr; } else { $insertdata[$row][$value] = array(strval($cell->getValue())); } } elseif (Arr::get($ftype, 0) == 'price') { $insertdata[$row][$value] = round($cell->getValue(), 2); } } } } } // echo '<pre>'; // var_dump($insertdata); // echo '</pre>'; if (is_array($insertdata)) { foreach ($insertdata as $item) { $id = $material->unique_url($item['url'], $gid); if (!$id) { $item['mid'] = $material->getMaterialIdByUrl($item['url'], $gid); if (isset($item['sleep'])) { unset($item['sleep']); } $result = $material->saveMaterial($item, $gid, TRUE); } else { $result = $material->newMaterial($item, $gid, TRUE); } } } } } else { echo '<div class="error_box">Не выбран файл или он неверного формата</div>'; } } }