GdeSlon

<?php
ini_set('memory_limit', '1G');
date_default_timezone_set('Europe/Moscow');
require_once 'Db.php';

function import() {
    $stmt = Db::getInstance()->prepare('SELECT id, gdeslon_id FROM gdeslon_products');
    $stmt->execute();
    $gdeslonProductIds = array();
    print "\n" . date('Y-m-d H:i:s') . "\tFetching existing product ids";
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $gdeslonProductIds[$row['gdeslon_id']] = $row['id'];
    }
    print "\n" . date('Y-m-d H:i:s') . "\tDone fetching existing product ids";
    $xmlFile = 'import.xml';
    $xmlReader = new XMLReader();
    $r = $xmlReader->open($xmlFile);
    print "\nStarted to read XML file\t" . date('Y-m-d H:i:s') . "\n";
    $offersAdded = 0;
    $currentOffersAdded = 0;
    while ($xmlReader->read()) {
        if ($xmlReader->nodeType == XMLReader::ELEMENT && $xmlReader->name == 'category') {
            $categoryId = $xmlReader->getAttribute('id');
            $parentId = $xmlReader->getAttribute('parentId');
            $name = html_entity_decode($xmlReader->readString());
            $sql = "INSERT INTO gdeslon_categories_raw (gdeslon_cat_id, gdeslon_parent_cat_id, name) " .
                    "VALUES (:gdeslon_cat_id, :gdeslon_parent_cat_id, :name) ON DUPLICATE KEY UPDATE name = :name";
            $sth = Db::getInstance()->prepare($sql);
            $queryParams = array(':gdeslon_cat_id' => $categoryId, ':gdeslon_parent_cat_id' => $parentId,
                                 ':name' => $name);
            $sth->execute($queryParams);
        }
        if ($xmlReader->nodeType == XMLReader::ELEMENT && $xmlReader->name == 'offer') {
            $gdeslonCategoryId = $xmlReader->getAttribute('gs_category_id');
            $merchantId = $xmlReader->getAttribute('merchant_id');
            $gdeslonId = $xmlReader->getAttribute('id');
            unset($gdeslonProductIds[$gdeslonId]);
            $dom = new DOMDocument();
            $dom->loadXML('<offer>' . trim($xmlReader->readInnerXml()) . '</offer>');
            $thumbnail = $dom->getElementsByTagName('thumbnail');
            if ($thumbnail->length > 0) {
                $thumbnail = $thumbnail->item(0)->nodeValue;
                $picture = $dom->getElementsByTagName('picture')->item(0)->nodeValue;
                $url = $dom->getElementsByTagName('url')->item(0)->nodeValue;
                $urlHash = sha1($url);
                $price = $dom->getElementsByTagName('price')->item(0)->nodeValue;
                $currency = $dom->getElementsByTagName('currencyId')->item(0)->nodeValue;
                $name = html_entity_decode($dom->getElementsByTagName('name')->item(0)->textContent);
                $description = html_entity_decode($dom->getElementsByTagName('description')->item(0)->textContent);
                $categoryId = $dom->getElementsByTagName('categoryId')->item(0)->nodeValue;
                $vendor = html_entity_decode($dom->getElementsByTagName('vendor')->item(0)->textContent);
                $sql = "INSERT INTO gdeslon_products (name, description, category_id, currency, gdeslon_id, gs_category_id, merchant_id, " .
                       " picture, price, thumbnail, vendor, out_url, out_url_hash) VALUES (:name, :description, :category_id, :currency, :gdeslon_id, " .
                       ":gs_category_id, :merchant_id, :picture, :price, :thumbnail, :vendor, :out_url, :out_url_hash)" .
                       " ON DUPLICATE KEY UPDATE name = :name, description = :description, price = :price, oldprice = VALUES(price)";
                $sth = Db::getInstance()->prepare($sql);
                $queryParams = array(':category_id' => $categoryId, ':currency' => $currency, ':gdeslon_id' => $gdeslonId,
                                     ':gs_category_id' => $gdeslonCategoryId, ':merchant_id' => $merchantId, ':out_url' => $url,
                                     ':out_url_hash' => $urlHash, ':picture' => $picture, ':price' => $price,
                                     ':thumbnail' => $thumbnail, ':vendor' => $vendor, ':name' => $name, ':description' => $description,
                    );
                $sth->execute($queryParams);
                $offersAdded++;
                $currentOffersAdded++;
                if ($currentOffersAdded == 10000) {
                    print "\n" . date("H:i:s") . "\tAdded:\t" . $offersAdded;
                    $currentOffersAdded = 1;
                }
            }
        }
    }
    print "\n" . date('Y-m-d H:i:s') . "\tFinished to read XML ($offersAdded added)";
    if (count($gdeslonProductIds)) {
        print "\n" . date('Y-m-d H:i:s') . "\tDeleting old products:\t" . count($gdeslonProductIds);
        $sql = "DELETE FROM gdeslon_products WHERE id IN (" . implode(',', array_values($gdeslonProductIds)) . ")";
//        print $sql;
        $stmt = Db::getInstance()->exec($sql);
        var_dump($stmt);
    }
    updateProductsQuantity();
}

function updateProductsQuantity() {
    print "\n" . date('Y-m-d H:i:s') . "\tUpdating number of products";
    $sql = "SELECT id, gdeslon_cat_id, gdeslon_parent_cat_id, name FROM gdeslon_categories_raw";
    $stmt = Db::getInstance()->prepare($sql);
    $stmt->execute();
    $categoriesUpdated = 0;
    print "\n" . date('Y-m-d H:i:s') . "\tGetting categories from DB";
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $allCategories[] = $row;

    }
    print "\n" . date('Y-m-d H:i:s') . "\tUpdating categories";
    $sql = 'update gdeslon_categories_raw set products_num = 0, parent_cat_products_num = 0';
    Db::getInstance()->exec($sql);
    if (count($allCategories)) {
        foreach ($allCategories as $k => $row) {
            $data = getProductsQuantity($row['gdeslon_cat_id'], $row['gdeslon_parent_cat_id']);
            print "\n" . date('Y-m-d H:i:s') . "\tUpdating {$row['name']}\tcurrent: {$data['cat']}, parent: {$data['parent']}";
            $sql = "UPDATE gdeslon_categories_raw SET products_num = {$data['cat']}, parent_cat_products_num = {$data['parent']}" .
                   " WHERE id = {$row['id']}";
            $stmt = Db::getInstance()->exec($sql);
            $categoriesUpdated++;
            print "\t$categoriesUpdated categories updated";
        }
    }
}

function getProductsQuantity($catId, $parentCatId) {
    $stmt = Db::getInstance()->prepare("SELECT COUNT(*) AS num FROM gdeslon_products WHERE category_id = :category_id");
    $stmt->execute(array(':category_id' => $catId));
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    $numCat = $row['num'];
    $stmt = Db::getInstance()->prepare("SELECT COUNT(*) AS num FROM gdeslon_products WHERE gs_category_id = :category_id");
    $stmt->execute(array(':category_id' => $parentCatId));
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    $numParentCat = $row['num'];
    return array('cat' => $numCat, 'parent' => $numParentCat);
}

function downloadFile() {
    $cmd = "wget 'http://exporter.gdeslon.ru/uploads/exports/6542c8e30ef0b1731413cea0c288e40d033f1f83.xml.zip' -O import.xml.zip";
    exec($cmd);
    exec('unzip import.xml.zip');
    exec('mv 6542c8e30ef0b1731413cea0c288e40d033f1f83.xml import.xml');
}

//updateProductsQuantity();
//downloadFile();
import();