crea_csv_mapping_categorie_magento_google_merchant.php

<?php
require_once('app/Mage.php'); //Path to Magento
umask(0);
Mage::app();

error_reporting(E_ALL);
ini_set('display_errors', 1);

//
$dbr    =  Mage::getSingleton('core/resource')->getConnection('core_read');

//
$default_category_id = 2;


//scopro il entity_type_id di catalog_category (generalmente: 3)
$query = "
            SELECT
              entity_type_id
            FROM
              eav_entity_type
            WHERE
              entity_type_code  = 'catalog_category';
        ";

$category_entity_type_id = $dbr->fetchOne($query);


//scopro l'attribute_id di "nome" per categoria (generalmente: 41)
$query = "
            SELECT
              attribute_id
            FROM
              eav_attribute
            WHERE
              entity_type_id  = :category_entity_type_id AND
              attribute_code  = 'name'
        ";

$arrParam = [ 'category_entity_type_id' => $category_entity_type_id ];
$category_name_attribute_id = $dbr->fetchOne($query, $arrParam);

//estraggo ogni categoria di livello XX con il suo nome
$query = "
            SELECT
                catalog_category_entity.entity_id AS id, catalog_category_entity_varchar.value AS name, 
                (catalog_category_entity.level - 1) AS level
            FROM
                catalog_category_entity
            INNER JOIN
                catalog_category_entity_varchar
            ON
                catalog_category_entity.entity_id = catalog_category_entity_varchar.entity_id
            WHERE
                parent_id = :category_parent_id AND
                catalog_category_entity_varchar.store_id      = 0 AND
                catalog_category_entity_varchar.attribute_id  = " . $category_name_attribute_id . "
            ORDER BY
                name ASC
        ";

$arrParam = [ 'category_parent_id' => $default_category_id ];
$arrCategory["root"] = $dbr->fetchAll($query, $arrParam);

$data = 'Tipologia categoria,Categoria ID,GENITORE,CATEGORIA DA MAPPARE,ID CATEGORIA GOOGLE,COMMENTI' . PHP_EOL;

//categorie primo livello
foreach($arrCategory["root"] as $category) {

    $data .= getCategoryFullTree($category, $query, $dbr);
}

file_put_contents(Mage::getBaseDir('media') . DS . "mapping_categorie.csv", $data);


function getCategoryFullTree($category, $query, $dbr, $genitore = '')
{
    $output = '';

    switch ($category["level"]) {

        case "1": {

            $level = "CATEGORIA PRIMO LIVELLO";

        } break;

        case "2": {

            $level = "CATEGORIA SECONDO LIVELLO";

        } break;

        case "3": {

            $level = "CATEGORIA TERZO LIVELLO";

        } break;

    }


    //Tipologia categoria,Categoria ID,GENITORE,CATEGORIA DA MAPPARE,ID CATEGORIA GOOGLE,COMMENTI
    $output .= $level . "," . $category["id"] . "," . escapeCell($genitore) . "," . escapeCell($category["name"]) . ",," . PHP_EOL;

    $arrParam = [ 'category_parent_id' => $category["id"] ];
    $arrSubcats = $dbr->fetchAll($query, $arrParam);

    foreach($arrSubcats as $subcat) {

        $output .= getCategoryFullTree($subcat, $query, $dbr, $category["name"]);
    }


    //$output .= PHP_EOL;
    return $output;
}


function escapeCell($text)
{
    if( strpos($text, ",") !== false ) {

        $text = '"' . $text . '"';
    }

    return $text;
}