Magento

Tuesday, December 8, 2015

How to clear Magento Database after testing

I am currently doing a lot of tests in Magento so I can get familiar with it. Unfortunately the database is extremely cluttered with my tests. I wish to clear up the database so I can start over again. This means doing the following



SET FOREIGN_KEY_CHECKS=0;



TRUNCATE `sales_flat_creditmemo`;

TRUNCATE `sales_flat_creditmemo_comment`;

TRUNCATE `sales_flat_creditmemo_grid`;

TRUNCATE `sales_flat_creditmemo_item`;

TRUNCATE `sales_flat_invoice`;

TRUNCATE `sales_flat_invoice_comment`;

TRUNCATE `sales_flat_invoice_grid`;

TRUNCATE `sales_flat_invoice_item`;

TRUNCATE `sales_flat_order`;

TRUNCATE `sales_flat_order_address`;

TRUNCATE `sales_flat_order_grid`;

TRUNCATE `sales_flat_order_item`;

TRUNCATE `sales_flat_order_payment`;

TRUNCATE `sales_flat_order_status_history`;

TRUNCATE `sales_flat_quote`;

TRUNCATE `sales_flat_quote_address`;

TRUNCATE `sales_flat_quote_address_item`;

TRUNCATE `sales_flat_quote_item`;

TRUNCATE `sales_flat_quote_item_option`;

TRUNCATE `sales_flat_quote_payment`;

TRUNCATE `sales_flat_quote_shipping_rate`;

TRUNCATE `sales_flat_shipment`;

TRUNCATE `sales_flat_shipment_comment`;

TRUNCATE `sales_flat_shipment_grid`;

TRUNCATE `sales_flat_shipment_item`;

TRUNCATE `sales_flat_shipment_track`;

TRUNCATE `sales_invoiced_aggregated`;           

TRUNCATE `sales_invoiced_aggregated_order`;       

TRUNCATE `log_quote`;



ALTER TABLE `sales_flat_creditmemo_comment` AUTO_INCREMENT=1;

ALTER TABLE `sales_flat_creditmemo_grid` AUTO_INCREMENT=1;

ALTER TABLE `sales_flat_creditmemo_item` AUTO_INCREMENT=1;

ALTER TABLE `sales_flat_invoice` AUTO_INCREMENT=1;

ALTER TABLE `sales_flat_invoice_comment` AUTO_INCREMENT=1;

ALTER TABLE `sales_flat_invoice_grid` AUTO_INCREMENT=1;

ALTER TABLE `sales_flat_invoice_item` AUTO_INCREMENT=1;

ALTER TABLE `sales_flat_order` AUTO_INCREMENT=1;

ALTER TABLE `sales_flat_order_address` AUTO_INCREMENT=1;

ALTER TABLE `sales_flat_order_grid` AUTO_INCREMENT=1;

ALTER TABLE `sales_flat_order_item` AUTO_INCREMENT=1;

ALTER TABLE `sales_flat_order_payment` AUTO_INCREMENT=1;

ALTER TABLE `sales_flat_order_status_history` AUTO_INCREMENT=1;

ALTER TABLE `sales_flat_quote` AUTO_INCREMENT=1;

ALTER TABLE `sales_flat_quote_address` AUTO_INCREMENT=1;

ALTER TABLE `sales_flat_quote_address_item` AUTO_INCREMENT=1;

ALTER TABLE `sales_flat_quote_item` AUTO_INCREMENT=1;

ALTER TABLE `sales_flat_quote_item_option` AUTO_INCREMENT=1;

ALTER TABLE `sales_flat_quote_payment` AUTO_INCREMENT=1;

ALTER TABLE `sales_flat_quote_shipping_rate` AUTO_INCREMENT=1;

ALTER TABLE `sales_flat_shipment` AUTO_INCREMENT=1;

ALTER TABLE `sales_flat_shipment_comment` AUTO_INCREMENT=1;

ALTER TABLE `sales_flat_shipment_grid` AUTO_INCREMENT=1;

ALTER TABLE `sales_flat_shipment_item` AUTO_INCREMENT=1;

ALTER TABLE `sales_flat_shipment_track` AUTO_INCREMENT=1;

ALTER TABLE `sales_invoiced_aggregated` AUTO_INCREMENT=1;

ALTER TABLE `sales_invoiced_aggregated_order` AUTO_INCREMENT=1;

ALTER TABLE `log_quote` AUTO_INCREMENT=1;



#########################################

# DOWNLOADABLE PURCHASED

#########################################

TRUNCATE `downloadable_link_purchased`;

TRUNCATE `downloadable_link_purchased_item`;



ALTER TABLE `downloadable_link_purchased` AUTO_INCREMENT=1;

ALTER TABLE `downloadable_link_purchased_item` AUTO_INCREMENT=1;



#########################################

# RESET ID COUNTERS

#########################################

TRUNCATE `eav_entity_store`;

ALTER TABLE  `eav_entity_store` AUTO_INCREMENT=1;





################for categories################

TRUNCATE TABLE `catalog_category_entity`;

TRUNCATE TABLE `catalog_category_entity_datetime`;

TRUNCATE TABLE `catalog_category_entity_decimal`;

TRUNCATE TABLE `catalog_category_entity_int`;

TRUNCATE TABLE `catalog_category_entity_text`;

TRUNCATE TABLE `catalog_category_entity_varchar`;

TRUNCATE TABLE `catalog_category_product`;

TRUNCATE TABLE `catalog_category_product_index`;



INSERT  INTO `catalog_category_entity`

(`entity_id`,`entity_type_id`,`attribute_set_id`,`parent_id`,`created_at`,`updated_at`,`path`,`POSITION`,`level`,`children_count`) 

VALUES (1,3,0,0,'0000-00-00 00:00:00','2009-02-20 00:25:34','1',1,0,1),

(2,3,3,0,'2009-02-20 00:25:34','2009-02-20 00:25:34','1/2',1,1,0);

INSERT  INTO `catalog_category_entity_int`(`value_id`,`entity_type_id`,`attribute_id`,`store_id`,`entity_id`,`value`) 

VALUES (1,3,32,0,2,1),(2,3,32,1,2,1);

INSERT  INTO `catalog_category_entity_varchar`(`value_id`,`entity_type_id`,`attribute_id`,`store_id`,`entity_id`,`value`) 

VALUES (1,3,31,0,1,'Root Catalog'),(2,3,33,0,1,'root-catalog'),(3,3,31,0,2,'Default Category'),

(4,3,39,0,2,'PRODUCTS'),(5,3,33,0,2,'default-category');



################for customers################







TRUNCATE customer_address_entity;

TRUNCATE customer_address_entity_datetime;

TRUNCATE customer_address_entity_decimal;

TRUNCATE customer_address_entity_int;

TRUNCATE customer_address_entity_text;

TRUNCATE customer_address_entity_varchar;

TRUNCATE customer_entity;

TRUNCATE customer_entity_datetime;

TRUNCATE customer_entity_decimal;

TRUNCATE customer_entity_int;

TRUNCATE customer_entity_text;

TRUNCATE customer_entity_varchar;

TRUNCATE log_customer;

TRUNCATE log_visitor;

TRUNCATE log_visitor_info;

TRUNCATE `tag`;

TRUNCATE `tag_relation`;

TRUNCATE `tag_summary`;

TRUNCATE `tag_properties`;            

TRUNCATE `wishlist`;

TRUNCATE `log_customer`;





ALTER TABLE customer_address_entity AUTO_INCREMENT=1;

ALTER TABLE customer_address_entity_datetime AUTO_INCREMENT=1;

ALTER TABLE customer_address_entity_decimal AUTO_INCREMENT=1;

ALTER TABLE customer_address_entity_int AUTO_INCREMENT=1;

ALTER TABLE customer_address_entity_text AUTO_INCREMENT=1;

ALTER TABLE customer_address_entity_varchar AUTO_INCREMENT=1;

ALTER TABLE customer_entity AUTO_INCREMENT=1;

ALTER TABLE customer_entity_datetime AUTO_INCREMENT=1;

ALTER TABLE customer_entity_decimal AUTO_INCREMENT=1;

ALTER TABLE customer_entity_int AUTO_INCREMENT=1;

ALTER TABLE customer_entity_text AUTO_INCREMENT=1;

ALTER TABLE customer_entity_varchar AUTO_INCREMENT=1;

ALTER TABLE log_customer AUTO_INCREMENT=1;

ALTER TABLE log_visitor AUTO_INCREMENT=1;

ALTER TABLE log_visitor_info AUTO_INCREMENT=1;

ALTER TABLE `tag` AUTO_INCREMENT=1;

ALTER TABLE `tag_relation` AUTO_INCREMENT=1;

ALTER TABLE `tag_summary` AUTO_INCREMENT=1;

ALTER TABLE `tag_properties` AUTO_INCREMENT=1;

ALTER TABLE `wishlist` AUTO_INCREMENT=1;

ALTER TABLE `log_customer` AUTO_INCREMENT=1;







################ For product################



TRUNCATE  `catalog_product_bundle_option`;

TRUNCATE  `catalog_product_bundle_option_value`;

TRUNCATE  `catalog_product_bundle_selection`;

TRUNCATE  `catalog_product_entity_datetime`;

TRUNCATE  `catalog_product_entity_decimal`;

TRUNCATE  `catalog_product_entity_gallery`;

TRUNCATE  `catalog_product_entity_int`;

TRUNCATE  `catalog_product_entity_media_gallery`;

TRUNCATE  `catalog_product_entity_media_gallery_value`;

TRUNCATE  `catalog_product_entity_text`;

TRUNCATE  `catalog_product_entity_tier_price`;

TRUNCATE  `catalog_product_entity_varchar`;

TRUNCATE  `catalog_product_flat_1`;

TRUNCATE  `catalog_product_link`;

TRUNCATE  `catalog_product_link_attribute`;

TRUNCATE  `catalog_product_link_attribute_decimal`;

TRUNCATE  `catalog_product_link_attribute_int`;

TRUNCATE  `catalog_product_link_attribute_varchar`;

TRUNCATE  `catalog_product_link_type`;

TRUNCATE  `catalog_product_option`;

TRUNCATE  `catalog_product_option_price`;

TRUNCATE  `catalog_product_option_title`;

TRUNCATE  `catalog_product_option_type_price`;

TRUNCATE  `catalog_product_option_type_title`;

TRUNCATE  `catalog_product_option_type_value`;

TRUNCATE  `catalog_product_super_attribute_label`;

TRUNCATE  `catalog_product_super_attribute_pricing`;

TRUNCATE  `catalog_product_super_attribute`;

TRUNCATE  `catalog_product_super_link`;

TRUNCATE  `catalog_product_enabled_index`;

TRUNCATE  `catalog_product_website`;

TRUNCATE  `catalog_product_relation`;

TRUNCATE  `catalog_category_product_index`;

TRUNCATE  `catalog_category_product`;

TRUNCATE  `cataloginventory_stock_item`;

TRUNCATE  `cataloginventory_stock_status`;

TRUNCATE  `cataloginventory_stock_status_idx`;

TRUNCATE  `cataloginventory_stock`;

TRUNCATE  `core_url_rewrite`;



INSERT  INTO `catalog_product_link_type`(`link_type_id`,`code`) VALUES (1,'relation'),

(2,'bundle'),(3,'super'),(4,'up_sell'),(5,'cross_sell');

INSERT  INTO `catalog_product_link_attribute`(`product_link_attribute_id`,`link_type_id`,`product_link_attribute_code`,`data_type`) 

VALUES (1,2,'qty','decimal'),(2,1,'position','int'),(3,4,'position','int'),(4,5,'position','int'),

(6,1,'qty','decimal'),(7,3,'position','int'),(8,3,'qty','decimal');

INSERT  INTO `cataloginventory_stock`(`stock_id`,`stock_name`) VALUES (1,'Default');

TRUNCATE TABLE `catalog_product_entity`;





##############################

# ADDITIONAL LOGS

##############################

TRUNCATE `log_url`;

TRUNCATE `log_url_info`;

TRUNCATE `log_visitor`;

TRUNCATE `log_visitor_info`;

TRUNCATE `report_event`;

TRUNCATE `report_viewed_product_index`;

TRUNCATE `sendfriend_log`;

TRUNCATE `log_summary`



ALTER TABLE `log_url` AUTO_INCREMENT=1;

ALTER TABLE `log_url_info` AUTO_INCREMENT=1;

ALTER TABLE `log_visitor` AUTO_INCREMENT=1;

ALTER TABLE `log_visitor_info` AUTO_INCREMENT=1;

ALTER TABLE `report_event` AUTO_INCREMENT=1;

ALTER TABLE `report_viewed_product_index` AUTO_INCREMENT=1;

ALTER TABLE `sendfriend_log` AUTO_INCREMENT=1;

ALTER TABLE `log_summary` AUTO_INCREMENT=1;

SET FOREIGN_KEY_CHECKS = 1;

Thursday, October 15, 2015

Update record with condition in custom module in magento

I have create a Model and for some development i had to implement Update method. It is very easy. I also try for conditional update. But i am using the following code which will fulfill your requirement for conditional update.

foreach($cart as $item_id=>$item){

$olddata_id = Mage::getModel('custom/csaabandonedcart')->getCollection()

->addFieldToFilter('csa_id', Mage::getSingleton('customer/session')->getCustomerId())

->addFieldToFilter('customer_id', $retailer)->addFieldToFilter('product_id', $item_id)

->getColumnValues('id');

$abandonedcart = Mage::getModel('custom/csaabandonedcart')->load($olddata_id[0]);

$abandonedcart->setQuantity($item['qty']);

$abandonedcart->save();

}

Delete record with condition in custom module in magento

I have create a Model and for some development i had to implement delete method. It is very easy. I also try for conditional delete. But i am using the following code which will fulfill your requirement for conditional delete.

foreach($pid as $spid){

$items = Mage::getModel('custom/csaabandonedcart')->getCollection()

->addFieldToFilter('csa_id', Mage::getSingleton('customer/session')->getCustomerId())

->addFieldToFilter('customer_id', $retailer)->addFieldToFilter('product_id', $spid);

foreach($items as $item){

$item->delete();

}

Creating Magento order programmatically

If you’re working with Magento , most probably you’ll face a situation when you need to create orders programmatically, It’s not relevant to create orders (or customers) using the Magento interface, as you can do it programmatically, which takes less time and effort.
This action can be of great help if you need to create a number of orders quickly to test your store features.
Now, in this article I’ll explain now to create Magento orders programmatically, also adding information on creating customers programmatically, as these two actions are closely connected.

$order_list = array('32'=>array('12'=>3));
//here 32 is my customer id, 12 is my product id, 3 is my QTY for that product.

foreach($order_list as $retailer_id=>$retailer_order){

$customer = Mage::getModel('customer/customer')->load($retailer_id);

$storeId = $customer->getStoreId();

$quote = Mage::getModel('sales/quote')->setStoreId($storeId);

$quote->assignCustomer($customer);

foreach($retailer_order as $item_id=>$item_qty){

$product = Mage::getModel('catalog/product')->load($item_id);

$params = array();

$params['qty'] = $item_qty;

$request = new Varien_Object();

$request->setData($params);

$quote->addProduct($product, $request);

}

$customerAddressId = $customer->getDefaultBilling();

$address = Mage::getModel('customer/address')->load($customerAddressId);

$addressData = array (

'prefix' => $address->getPrefix(),

'firstname' => $address->getFirstname(),

'lastname' => $address->getLastname(),

'street' => $address->getStreet(),

'city' => $address->getCity(),

'region_id' => $address->getRegionId(),

'region' => $address->getRegion(),

'postcode' => $address->getPostcode(),

'country_id' => $address->getCountryId(),

'telephone' => $address->getTelephone(),

'email' => $address->getEmail()

);

$billingAddress = $quote->getBillingAddress()->addData($addressData);

$shippingAddress = $quote->getShippingAddress()->addData($addressData);

$shippingAddress->setFreeShipping( true )

->setCollectShippingRates(true)->collectShippingRates()

->setShippingMethod('freeshipping_freeshipping')

->setPaymentMethod('checkmo');

$quote->getPayment()->importData(array('method' => 'checkmo'));

$quote->collectTotals()->save();

$service = Mage::getModel('sales/service_quote', $quote);

$service->submitAll();

$order = $service->getOrder();

}