get('config_error_display')) {
echo '' . $errors . ': ' . $errstr . ' in ' . $errfile . ' on line ' . $errline . '';
}
if ($config->get('config_error_log')) {
$log->write('PHP ' . $errors . ': ' . $errstr . ' in ' . $errfile . ' on line ' . $errline);
}
return true;
}
function fatal_error_shutdown_handler_for_export()
{
$last_error = error_get_last();
if ($last_error['type'] === E_ERROR) {
// fatal error
error_handler_for_export(E_ERROR, $last_error['message'], $last_error['file'], $last_error['line']);
}
}
class ModelSeoCategory extends Model {
function getUrl($category_id){
$query = $this->db->query("SELECT keyword FROM " . DB_PREFIX . "url_alias WHERE query = 'category_id=" . (int)$category_id . "'");
return $query->row;
}
function upload( $filename ) {
global $config;
global $log;
$config = $this->config;
$log = $this->log;
set_error_handler('error_handler_for_export',E_ALL);
register_shutdown_function('fatal_error_shutdown_handler_for_export');
$database =& $this->db;
ini_set("memory_limit","512M");
ini_set("max_execution_time",180);
//set_time_limit( 60 );
chdir( '../system/PHPExcel' );
require_once( 'Classes/PHPExcel.php' );
chdir( '../../admin' );
$inputFileType = PHPExcel_IOFactory::identify($filename);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setReadDataOnly(true);
$reader = $objReader->load($filename);
$ok = $this->validateUpload( $reader );
if (!$ok) {
return FALSE;
}
// $this->clearCache();
$ok = $this->uploadCategories( $reader, $database );
if (!$ok) {
return FALSE;
}
chdir( '../../..' );
return $ok;
}
function validateUpload( &$reader )
{
if (!$this->validateCategories( $reader )) {
error_log(date('Y-m-d H:i:s - ', time()).$this->language->get('error_categories_header')."\n",3,DIR_LOGS."error.txt");
return FALSE;
}
return TRUE;
}
function validateCategories( &$reader )
{
$expectedCategoryHeading = array
( "category_id", "name", "meta_titleTags", "meta_description", "meta_keywords", "meta_url");
$data =& $reader->getSheet(0);
return $this->validateHeading( $data, $expectedCategoryHeading );
}
// function clearCache() {
// $this->cache->delete('category_description');
// }
function uploadCategories( &$reader, &$database ) {
// find the default language id and default units
$languageId = 1;
$data = $reader->getSheet(0);
$categories = array();
$category = array();
$isFirstRow = TRUE;
$i = 0;
$k = $data->getHighestRow();
for ($i=0; $i<$k; $i+=1) {
if ($isFirstRow) {
$isFirstRow = FALSE;
continue;
}
$categoryId = trim($this->getCell($data,$i,1));
if ($categoryId=="") {
continue;
}
$name = $this->getCell($data,$i,2);
$name = htmlentities( $name, ENT_QUOTES, $this->detect_encoding($name) );
$meta_titleTags = $this->getCell($data,$i,3);
$meta_titleTags = htmlentities( $meta_titleTags, ENT_QUOTES, $this->detect_encoding($meta_titleTags) );
$meta_description = $this->getCell($data,$i,4);
$meta_description = htmlentities( $meta_description, ENT_QUOTES, $this->detect_encoding($meta_description) );
$meta_keywords = $this->getCell($data,$i,5);
$meta_keywords = htmlentities( $meta_keywords, ENT_QUOTES, $this->detect_encoding($meta_keywords) );
$meta_url = $this->getCell($data,$i,6);
$meta_url = htmlentities( $meta_url, ENT_QUOTES, $this->detect_encoding($meta_url) );
$category = array();
$category['category_id'] = $categoryId;
$category['name'] = $name;
$category['meta_titleTags'] = $meta_titleTags;
$category['meta_description'] = $meta_description;
$category['meta_keywords'] = $meta_keywords;
$category['meta_url'] = $meta_url;
$categories[$categoryId] = $category;
}
return $this->storeCategoriesIntoDatabase( $database, $categories );
}
function getCell(&$worksheet,$row,$col,$default_val='') {
$col -= 1; // we use 1-based, PHPExcel uses 0-based column index
$row += 1; // we use 0-based, PHPExcel used 1-based row index
return ($worksheet->cellExistsByColumnAndRow($col,$row)) ? $worksheet->getCellByColumnAndRow($col,$row)->getValue() : $default_val;
}
protected function detect_encoding( $str ) {
// auto detect the character encoding of a string
return mb_detect_encoding( $str, 'UTF-8,ISO-8859-15,ISO-8859-1,cp1251,KOI8-R' );
}
function validateHeading( &$data, &$expected ) {
$heading = array();
$k = PHPExcel_Cell::columnIndexFromString( $data->getHighestColumn() );
if ($k != count($expected)) {
return FALSE;
}
$i = 0;
for ($j=1; $j <= $k; $j+=1) {
$heading[] = $this->getCell($data,$i,$j);
}
$valid = TRUE;
for ($i=0; $i < count($expected); $i+=1) {
if (!isset($heading[$i])) {
$valid = FALSE;
break;
}
if (strtolower($heading[$i]) != strtolower($expected[$i])) {
$valid = FALSE;
break;
}
}
return $valid;
}
function storeCategoriesIntoDatabase( &$database, &$categories )
{
// find the default language id
$languageId = 1;
// generate and execute SQL for storing the categories
foreach ($categories as $category) {
$categoryId = $category['category_id'];
$categoryName = $database->escape($category['name']);
$meta_titleTags = $database->escape($category['meta_titleTags']);
$meta_description = $database->escape($category['meta_description']);
$meta_keywords = $database->escape($category['meta_keywords']);
$meta_url = $database->escape($category['meta_url']);
$sql = "UPDATE `".DB_PREFIX."category_description` SET
meta_description='".$meta_description."',
meta_keyword='".$meta_keywords."',
meta_titleTags='".$meta_titleTags."'
WHERE category_id = '".$categoryId."'";
$database->query($sql);
$sql1 = "DELETE FROM `".DB_PREFIX."url_alias`
WHERE query = 'category_id=".$categoryId."'";
$database->query($sql1);
if($meta_url != ""){
$sql2 = "INSERT INTO `".DB_PREFIX."url_alias` VALUES ('', 'category_id=".$categoryId."','".$meta_url."')"; $database->query($sql2);
}
}
// final commit
$database->query("COMMIT;");
return TRUE;
}
function download() {
global $config;
global $log;
$config = $this->config;
$log = $this->log;
set_error_handler('error_handler_for_export',E_ALL);
register_shutdown_function('fatal_error_shutdown_handler_for_export');
$database =& $this->db;
$languageId = 1;
// We use the package from http://pear.php.net/package/Spreadsheet_Excel_Writer/
chdir( '../system/pear' );
require_once "Spreadsheet/Excel/Writer.php";
chdir( '../../admin' );
// Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();
// $workbook->setTempDir(DIR_CACHE);
$workbook->setVersion(8); // Use Excel97/2000 BIFF8 Format
$priceFormat =& $workbook->addFormat(array('Size' => 10,'Align' => 'right','NumFormat' => '######0.00'));
$boxFormat =& $workbook->addFormat(array('Size' => 10,'vAlign' => 'vequal_space' ));
$weightFormat =& $workbook->addFormat(array('Size' => 10,'Align' => 'right','NumFormat' => '##0.00'));
$textFormat =& $workbook->addFormat(array('Size' => 10, 'NumFormat' => "@" ));
// sending HTTP headers
$workbook->send('backup_categories_SEO.xls');
// Creating the categories worksheet
$worksheet =& $workbook->addWorksheet('Categories');
$worksheet->setInputEncoding ( 'UTF-8' );
$this->populateCategoriesWorksheet( $worksheet, $database, $imageNames, $languageId, $priceFormat, $boxFormat, $weightFormat, $textFormat );
$worksheet->freezePanes(array(1, 1, 1, 1));
// Let's send the file
$workbook->close();
// Clear the spreadsheet caches
// $this->clearSpreadsheetCache();
exit;
}
function populateCategoriesWorksheet( &$worksheet, &$database, &$imageNames, $languageId, &$priceFormat, &$boxFormat, &$weightFormat, &$textFormat )
{
// Set the column widths
$j = 0;
$worksheet->setColumn($j,$j++,max(strlen('category_id'),4)+1);
$worksheet->setColumn($j,$j++,max(strlen('name'),30)+1);
$worksheet->setColumn($j,$j++,max(strlen('meta_titleTags'),32)+1,$textFormat);
$worksheet->setColumn($j,$j++,max(strlen('meta_description'),32)+1,$textFormat);
$worksheet->setColumn($j,$j++,max(strlen('meta_keywords'),32)+1,$textFormat);
$worksheet->setColumn($j,$j++,max(strlen('meta_url'),32)+1,$textFormat);
// The category headings row
$i = 0;
$j = 0;
$worksheet->writeString( $i, $j++, 'category_id', $boxFormat );
$worksheet->writeString( $i, $j++, 'name', $boxFormat );
$worksheet->writeString( $i, $j++, 'meta_titleTags', $boxFormat );
$worksheet->writeString( $i, $j++, 'meta_description', $boxFormat );
$worksheet->writeString( $i, $j++, 'meta_keywords', $boxFormat );
$worksheet->writeString( $i, $j++, 'meta_url', $boxFormat );
$worksheet->setRow( $i, 30, $boxFormat );
// The actual categories data
$i += 1;
$j = 0;
$result = $this->getCategories(0, 'ASC');
foreach ($result as $row) {
$res = $database->query("SELECT * from " . DB_PREFIX . "url_alias WHERE query = 'category_id=" . $row['category_id'] . "'");
$result1 = $res->row;
if(empty($result1)){
$result1['keyword'] = "";
}
$worksheet->setRow( $i, 26 );
$categoryId = $row['category_id'];
$worksheet->write( $i, $j++, $categoryId );
$worksheet->writeString( $i, $j++, html_entity_decode($row['name'],ENT_QUOTES,'UTF-8') );
$worksheet->write( $i, $j++, html_entity_decode($row['meta_titleTags'],ENT_QUOTES,'UTF-8'), $textFormat );
$worksheet->write( $i, $j++, html_entity_decode($row['meta_description'],ENT_QUOTES,'UTF-8'), $textFormat );
$worksheet->write( $i, $j++, html_entity_decode($row['meta_keyword'],ENT_QUOTES,'UTF-8'), $textFormat );
$worksheet->write( $i, $j++, html_entity_decode($result1['keyword'],ENT_QUOTES,'UTF-8'), $textFormat );
$i += 1;
$j = 0;
}
}
public function editCategory($data){
$this->db->query("UPDATE " . DB_PREFIX . "category_description SET
meta_description='".$data['description']."',
meta_keyword='".$data['keywords']."',
meta_titleTags='".$data['titleTags']."'
WHERE category_id = '".(int)$data['category_id']."'");
$this->db->query("DELETE FROM `".DB_PREFIX."url_alias`
WHERE query = 'category_id=".(int)$data['category_id']."'");
if($data['url'] != ""){
$this->db->query("INSERT INTO `".DB_PREFIX."url_alias` VALUES ('', 'category_id=".(int)$data['category_id']."','".$data['url']."')");
}
}
public function getTotalCategories() {
$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "category");
return $query->row['total'];
}
public function getCategories($parent_id, $order) {
$category_data = array();
$query = $this->db->query("
SELECT * FROM " . DB_PREFIX . "category c
LEFT JOIN " . DB_PREFIX . "category_description cd ON (c.category_id = cd.category_id)
WHERE c.parent_id = '" . (int)$parent_id . "'
AND cd.language_id = '" . (int)$this->config->get('config_language_id') . "'
ORDER BY cd.name ".$order);
foreach ($query->rows as $result) {
$category_data[] = array(
'category_id' => $result['category_id'],
'meta_description' => $result['meta_description'],
'meta_keyword' => $result['meta_keyword'],
'meta_titleTags' => $result['meta_titleTags'],
'name' => $this->getPath($result['category_id'], $this->config->get('config_language_id')),
'status' => $result['status'],
'sort_order' => $result['sort_order']
);
$category_data = array_merge($category_data, $this->getCategories($result['category_id'], $order));
}
return $category_data;
}
public function getPath($category_id) {
$query = $this->db->query("SELECT name, parent_id FROM " . DB_PREFIX . "category c LEFT JOIN " . DB_PREFIX . "category_description cd ON (c.category_id = cd.category_id) WHERE c.category_id = '" . (int)$category_id . "' AND cd.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY c.sort_order, cd.name ASC");
$category_info = $query->row;
if ($category_info['parent_id']) {
return $this->getPath($category_info['parent_id'], $this->config->get('config_language_id')) . $this->language->get('text_separator') . $category_info['name'];
} else {
return $category_info['name'];
}
}
public function alterTable(){
$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "setting s WHERE s.group='configSEO' AND s.key='categorySEO';");
if(!$query->row){
$this->db->query("INSERT INTO " . DB_PREFIX . "setting values ('','','configSEO','categorySEO','','');");
$this->db->query("ALTER TABLE " . DB_PREFIX . "category_description ADD meta_titleTags VARCHAR(255) NOT NULL;");
}
}
}
?>