AyaSoftware

Magento has made available to its users, a collection of functions that allows them to access and modify data in a very safe way. However, saving each individual product can take a long time when updating a large set of data. 

To improve this, Dweeves (Thank you so much for this great work) , has created a set of functions that provide a great way to create a direct SQL query which could update 1000’s of products in 1 or 2 seconds.  As a response to many Magento users requests, who do not know how to install it and how to use it, we have created an extension with the following features:

Extension Features:

• Run updates periodically at 00:00 (Midnight) by Default
• Modify updates Scheduling by Picking a new date (see slideshow)
• Use Magento Cron features (see setup below)
• Easy 2-minute installation
• Easy to use
• No code changes required
• No programming Skills required
• User-friendly interface
• Provide a detailed  updates report saved to a log File.
• Download inventory file from remote server.
• Based on Dweeves script   (Follow thread )

Cron Setup - VERY IMPORTANT
For this extension to work, you have to setup a classic cron job for the file {{base_dir}}/cron.php that runs every X hours/minutes to check whether there is any pending cron. For example, the direct SQL update at at 00:00 (Midnight) requires, 2 executions of the file cron.php (one before and one after).

Extension tested on Magento CE 1.3.X.Y , 1.4.X.Y and Magento Enterprise Edition (1.8.0.0)

 View Screenshots

The extension can be downloaded through Magento Connect. (Click here)

This extension is free

Or download it here .

Here is an example of inventory feed you can use!

store,websites,attribute_set,sku,price,special_price,qty
"admin","base", "Default","AYA01",51.00,50.00,15

This extension is provided totally free to use and will remain so, however I will spend some of my free time maintaining and improving it and providing support, so if you've found it useful please consider making a donation, thanks.

Changelog:

• ( Added 14 October 2011 ): Created a new root file called ayaSQLUpdate.php. Call it from SSH or classic cron jobs to run your updates.
• ( Added 14 October 2011 ): in Magento 6.1, the column stock_status_changed_automatically is removed from cataloginventory_stock_item, therefore, you must update Massimport.php with the new updateStock function (see below) - download Direct SQL Update - 1.6.1.0.zip
• Version 1.3: added task status to check the direct SQL update status. No need anymore to check your cron_schedule table:  (see screenshot 7)
• Status: pending, error, missed or succes
  Created At,  Scheduled  and At Finished At times
• Scheduling a cron job with this extension does not work in 1.5.1 Anything you choose for a time results in the script telling you the task must be scheduled after the the current server time (fixed 10 September 2011) - download Direct SQL Update - 1.6.1.0.zip.

public function updateStock($pid, $item) {
		$updated = true;
		$csit = $this->tablename ( "cataloginventory_stock_item" );
		$sql = "INSERT INTO `$csit` 
			(`product_id`, 
 			 `stock_id`,
  			  `qty`, 
  			  `is_in_stock`, 
  			  `low_stock_date`) 
			VALUES (?,?,?,?,?)";
		$data = array ($pid, $this->website_ids [$item ["websites"]], $item ["qty"], $item ["qty"] > 0 ? 1 : 0, null );
		$this->insert($sql,$data);
	}

public function postAction() {
		$post = $this->getRequest ()->getPost ();
		$scheduling = $post ['scheduling'];
		$timecreated = strftime("%Y-%m-%d %H:%M:%S", mktime(date("H"), date("i"), date("s"), date("m"), date("d"), date("Y")));
		$sch = explode(" ", $scheduling);
		$dy = explode("-", $sch[0]);;
		$scheduling =  strftime(date("Y-m-d H:i:s", strtotime($dy[2]."-".$dy[0]."-".$dy[1]." ".$sch[1])));
		try {
			if ($timecreated > $scheduling) {
				throw new Exception ( Mage::helper ( 'sqlupdate' )->__ ( $scheduling . ' Task must be Scheduled after '.$timecreated ) );
			}
			$update = Mage::getModel ( 'sqlupdate/observer' );
			
			$update->changeScheduling ( $scheduling );
			$message = $this->__ ( 'The SQL Update has been Scheduled Successfully.' );
			Mage::getSingleton ( 'adminhtml/session' )->addSuccess ( $message );
		} catch ( Exception $e ) {
			Mage::getSingleton ( 'adminhtml/session' )->addError ( $e->getMessage () );
		}
		$this->_redirect ( '*/*' );
	}