Posted on

Magento Development: Delete Orders, Reset Dashboard

Delete Orders

Found this great extension that does it. I’ve used it on EE 1.12, and from the reviews I gather it works on CE 1.7.0.2 as well. YMMV, use with caution, etc. I can’t imagine installing this in anything other than a development environment.  Seamless Delete Order.

Reset Dashboard Statistics

To remove “Bestsellers” from my dashboard, I add this stored procedure to my development MySQL environments, courtesy of this post at PHP Bugs.

DROP PROCEDURE IF EXISTS reset_dashboard;
delimiter //
CREATE PROCEDURE reset_dashboard()
BEGIN
  TRUNCATE sales_bestsellers_aggregated_daily;
  TRUNCATE sales_bestsellers_aggregated_monthly;
  TRUNCATE sales_bestsellers_aggregated_yearly;
  DELETE FROM report_event WHERE event_type_id IN (SELECT event_type_id FROM report_event_types WHERE event_name IN ('catalog_product_view'));
END //
delimiter ;

The DELETE FROM report_event code I stumbled across shortly after I originally posted this. I found it here at DesignersSandBox.

Posted on

Drop MySQL Tables by Table Prefix

Problem:

I’ve access to only 1 MySQL database, but I have multiple Magento Installations in it – so naturally, I’m using Table Prefixes to keep them separated. In my case, I have Magento Community Editions 1.6.2.0 and 1.7.0.2.

My table prefixes are ce1620_ and ce1702_, which is simple and easy to understand. The problem is how can I simply remove all tables for only 1 installation?

Solution:

The answer came in the form of a stored procedure. Most of the heavy lifting was done by a nice poster over on StackOverflow.

My little extra ingredient was to disable foreign key constraints, so I could obliterate the whole darn thing in one pass.

As always, be careful and back up before doing something as drastic as this.

DROP PROCEDURE IF EXISTS droplike;
delimiter //
CREATE PROCEDURE droplike(pattern VARCHAR(20))
BEGIN
 SET foreign_key_checks = 0;
 SET group_concat_max_len = 65535;
 SELECT @DROP:= concat( 'drop table ', group_concat(TABLE_NAME) , ';' ) FROM information_schema.tables WHERE table_schema = "YOUR_DATABASE_NAME_HERE" AND TABLE_NAME LIKE pattern;
 PREPARE statement FROM @DROP;
 EXECUTE statement;
 SET foreign_key_checks = 1;
END //
delimiter ;
Posted on

Magento Image Information via SOAP API

Here’s a little snippet that’ll dump out image information for … well, as written here, every active SKU in your Magento Installation.

The reason for the for loop breaking SKUs down based on the first letter is to keep from blowing up my PHP memory with too many products. Your mileage may vary.

try {
    $proxy = new SoapClient('http://magento.store.url/index.php/api/?wsdl');
    $sid = $proxy->login('username', '********');
}
catch( Exception $e ) {
    echo "Can't establish connection: " . $e->getMessage() . "\n";
    exit(-3);
}
 
for( $cat = 'A', $i=0; $i < 26; $i++, $cat++ ) {     $filter = array(         'status' => array( '='=>'1' ),
        'sku' => array( 'like' => $cat.'%' )
    );
    echo $cat.".";
    try {
        $productList = $proxy->call( $sid, 'product.list', array($filter) );
    }
    catch( Exception $e ) {
        echo "Can't retrieve product listing: " . $e->getMessage() . "\n";
        exit(-4);
    }
    foreach( $productList as $prodInfo ) {
        echo $prodInfo['sku'] . "\t" . $prodInfo['name'] . "\n";
        $media = $proxy->call($sid, 'product_media.list', $prodInfo['sku']);
        print_r($media);
    }
}
exit(0);