Post by joongpark » Tue Oct 15, 2013 4:45 am

We use POS system and it creates CSV file at the end of the day with price and quantity nightly.

"sku","price","qty","is_in_stock",""
"-0500046043","0.00","0","0",""
"ACC-09181","1.99","103","1",""

And so on....

I use above file to update stock on Magento nightly.

Due to such high resource usage, I have moved to Opencart and I have not been able to do update quantity and price automatically.

Follwing is Magento Script that I made mixing from a few source to update stock automatically.
It does it job beautifully running from con job. It also creates error messages when codes do not match.

Is there any way to do this automatically for Open Cart using the same CSV file?
(I can't change the format of CSV because it's old DOS based POS)

I would appreciate if there is such a code existing or help with writing the code as alot of people will benefit from it.


Thanks.

Following code may be a mass as I used 2 difference code from 2 difference people. It updates price then updates quantity separately as I had no idea how to combine the codes.

Code: Select all

<?
 define('MAGENTO', realpath(dirname(__FILE__)));
 require_once MAGENTO . '/app/Mage.php';
 umask(0);
 Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);
 $count = 0;
 $file = fopen(MAGENTO . '/CSV/WEBINF0.CSV', 'r');
 while (($line = fgetcsv($file)) !== FALSE) {
 if ($count == 0) {
 foreach ($line as $key=>$value) {
 $cols[$value] = $key;
 }
 }
 $count++;
 if ($count == 1) continue;
 #Convert the lines to cols
 if ($count > 0) {
 foreach($cols as $col=>$value) {
 unset(${$col});
 ${$col} = $line[$value];
 }
 }
 
 // Check if SKU exists
 $product = Mage::getModel('catalog/product')->loadByAttribute('sku',$sku);
 if ( $product ) {
 $productId = $product->getId();
 $stockItem = Mage::getModel('cataloginventory/stock_item')->loadByProduct($productId);
 $stockItemId = $stockItem->getId();
 $stock = array();
 if (!$stockItemId) {
 $stockItem->setData('product_id', $product->getId());
 $stockItem->setData('stock_id', 1);
 } else {
 $stock = $stockItem->getData();
 }
 foreach($cols as $col=>$value) {
 $stock[$col] = $line[$value];
 }
 foreach($stock as $field => $value) {
 $stockItem->setData($field, $value?$value:0);
 }
 $stockItem->save();
 unset($stockItem);
 unset($product);
 }
 echo "<br />Stock updated $sku";
 }
 fclose($file);
?>

<?php
/**
 * @author      MagePsycho <info@magepsycho.com>
 * @website     http://www.magepsycho.com
 * @category    Export / Import
 */
$mageFilename = 'app/Mage.php';
require_once $mageFilename;
Mage::setIsDeveloperMode(true);
ini_set('display_errors', 1);
umask(0);
Mage::app('admin');
Mage::register('isSecureArea', 1);
Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);
 
set_time_limit(0);
ini_set('memory_limit','1024M');
 
/***************** UTILITY FUNCTIONS ********************/
function _getConnection($type = 'core_read'){
    return Mage::getSingleton('core/resource')->getConnection($type);
}
 
function _getTableName($tableName){
    return Mage::getSingleton('core/resource')->getTableName($tableName);
}
 
function _getAttributeId($attribute_code = 'price'){
    $connection = _getConnection('core_read');
    $sql = "SELECT attribute_id
                FROM " . _getTableName('eav_attribute') . "
            WHERE
                entity_type_id = ?
                AND attribute_code = ?";
    $entity_type_id = _getEntityTypeId();
    return $connection->fetchOne($sql, array($entity_type_id, $attribute_code));
}
 
function _getEntityTypeId($entity_type_code = 'catalog_product'){
    $connection = _getConnection('core_read');
    $sql        = "SELECT entity_type_id FROM " . _getTableName('eav_entity_type') . " WHERE entity_type_code = ?";
    return $connection->fetchOne($sql, array($entity_type_code));
}
 
function _getIdFromSku($sku){
    $connection = _getConnection('core_read');
    $sql        = "SELECT entity_id FROM " . _getTableName('catalog_product_entity') . " WHERE sku = ?";
    return $connection->fetchOne($sql, array($sku));
 
}
 
function _checkIfSkuExists($sku){
    $connection = _getConnection('core_read');
    $sql        = "SELECT COUNT(*) AS count_no FROM " . _getTableName('catalog_product_entity') . " WHERE sku = ?";
    $count      = $connection->fetchOne($sql, array($sku));
    if($count > 0){
        return true;
    }else{
        return false;
    }
}
 
function _updatePrices($data){
    $connection     = _getConnection('core_write');
    $sku            = $data[0];
    $newPrice       = $data[1];
    $productId      = _getIdFromSku($sku);
    $attributeId    = _getAttributeId();
 
    $sql = "UPDATE " . _getTableName('catalog_product_entity_decimal') . " cped
                SET  cped.value = ?
            WHERE  cped.attribute_id = ?
            AND cped.entity_id = ?";
    $connection->query($sql, array($newPrice, $attributeId, $productId));
}
/***************** UTILITY FUNCTIONS ********************/
 
$csv                = new Varien_File_Csv();
$data               = $csv->getData('CSV/WEBINF0.CSV'); //path to csv
array_shift($data);
 
$message = '';
$count   = 1;
foreach($data as $_data){
    if(_checkIfSkuExists($_data[0])){
        try{
            _updatePrices($_data);
            $message .= $count . '> Success:: While Updating Price (' . $_data[1] . ') of Sku (' . $_data[0] . '). <br />';
 
        }catch(Exception $e){
            $message .=  $count .'> Error:: While Upating  Price (' . $_data[1] . ') of Sku (' . $_data[0] . ') => '.$e->getMessage().'<br />';
        }
    }else{
        $message .=  $count .'> Error:: Product with Sku (' . $_data[0] . ') does\'t exist.<br />';
    }
    $count++;
}
echo $message;


Newbie

Posts

Joined
Mon May 14, 2012 12:00 pm

Post by MarketInSG » Tue Oct 15, 2013 1:04 pm

I would recommend you to hire someone to write up a custom script for you to do it. It's possible, but quite a bit of work. Feel free to drop me a PM if you would like.


User avatar
Guru Member

Posts

Joined
Wed Nov 16, 2011 11:53 am
Location - Singapore

Post by DeepaGovind » Thu Dec 19, 2013 3:29 pm

Did you get this done? Just curious

-- Deepa Govind
Zoho Consultant


User avatar
Newbie

Posts

Joined
Thu Dec 19, 2013 6:46 am
Location - Bangalore

Post by justinv » Sat Feb 22, 2014 8:35 am

This is possible with HostJars Total Import PRO and CSV Import PRO. This is probably the perfect use case for our modules.

Documentation: OpenCart User Guide
Mods: Total Import PRO | CSV Import PRO | Ecom Tracking | Any Feed | Autosuggest | OpenCart CDN
Image


Active Member

Posts

Joined
Tue Oct 12, 2010 1:24 pm
Who is online

Users browsing this forum: Bing [Bot], OSWorX and 27 guests