Archive for the ‘Magento 1.x’ Category

Update Customer name and surname and customer address name and surname

<pre lang="sql"> — Firstname —   SELECT * FROM customer_entity_varchar c2 INNER JOIN customer_entity c1 ON c1.entity_id = c2.entity_id AND c2.entity_type_id = 1 AND c2.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code=’firstname’ AND entity_type_id=1) INNER JOIN mytable m1 ON m1.magento_id = c2.entity_id AND firstname=’First name’ AND lastname=’Last name’ AND migrated=1 AND c2.VALUE=’First Name’;     […]

Db set default billing and shipping address

— start default_billing —   /** CREATE DEFAULT BILLING INSERTS CHUNK */   CREATE TEMPORARY TABLE my_default_billing_customer_entity_int SELECT 1 AS entity_type_id, (SELECT attribute_id FROM eav_attribute WHERE attribute_code=’default_billing’ AND entity_type_id=1) AS attribute_id, my.magento_id AS entity_id, a1.entity_id AS VALUE FROM mytable my INNER JOIN customer_entity c1 ON my.magento_id = c1.entity_id INNER JOIN customer_address_entity a1 ON c1.entity_id = […]

Update region id in magento customer address

  /* STEP 1 :: UPDATE ONLY ADDRESSES WHICH HAVE TURKEY AS A COUNTRY IN MAGENTO DB */ UPDATE mytable my INNER JOIN customer_address_entity c1 ON my.magento_id = c1.parent_id INNER JOIN customer_address_entity_varchar c3 ON c1.entity_id = c3.entity_id AND c3.entity_type_id = 2 AND c3.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code=’country_id’ AND entity_type_id=2) INNER JOIN customer_address_entity_int […]

Database join with group

$limit = 1000; $d_collection = Mage::getResourceModel(’modulename/tablename_collection’) ->addFieldToFilter(’main_table_field’, true) ->setOrder(’entity_id’, ‘asc’) ->setPageSize($limit);   $d_collection->getSelect()->join( array(’tablename’=> Mage::getSingleton(’core/resource’)->getTableName(’modulename/tablename’)), ‘tablename.entity_id = main_table.entity_id’, array()); $d_collection->addFieldToFilter(’tablename.table_field’, 0); $d_collection->getSelect()->group(’main_table.entity_id’); $d_collection->addFieldToSelect(’entity_id’);

Adding sql script to add column to existing d b table

filename: mysql4-upgrade-1.1.1-1.1.2.php <?php   $installer = $this; $installer->startSetup();   $connection = $installer->getConnection();   $tableName = $installer->getTable(’your_table_name’); $column = ‘newcolumn_id’; $comment = ‘Newcolumn ID’;   if (!$connection->tableColumnExists($tableName, $column)) { $connection->addColumn($tableName, $column, array( "type" => Varien_Db_Ddl_Table::TYPE_INTEGER, "nullable" => true, "length" => 10, "after" => "customer_id", "comment" => $comment )); }   $installer->endSetup();

Adding sql script to create database table

filename: mysql4-install-1.1.1.php <?php   $installer = $this; $installer->startSetup();   $tableName = $installer->getTable(’your_table_name’);   // Check if the table already exists if ($installer->getConnection()->isTableExists($tableName) != true) {   $table = $installer->getConnection() ->newTable($installer->getTable($tableName)) ->addColumn(’column_id’, Varien_Db_Ddl_Table::TYPE_INTEGER, null, array( ‘identity’ => true, ‘unsigned’ => true, ‘nullable’ => false, ‘primary’ => true, ), ‘Email Stats Id’) ->addColumn(’customer_id’, Varien_Db_Ddl_Table::TYPE_INTEGER, null, array( ‘identity’ […]

Magento create Creditmemo to refund online or offline

<?php   $orderId =’123456789′; $invoiceId = ‘1234’;   $data = array( ‘items’ => array( ‘12345’ => array(’qty’ => ‘1’), ‘12346’ => array(’qty’ => ‘2’) ), ‘comment_text’ => null, ‘do_offline’ => 1, ‘shipping_amount’ => 0, ‘adjustment_positive’ => ‘0’, ‘adjustment_negative’ => ‘0’, ‘refund_customerbalance_return_enable’ => 1, ‘refund_customerbalance_return’ => 39.9 );   // load order and invoice $order = […]

Magento get order invoice id and order items

<?php $orderId = ‘1234567890’; $order = Mage::getModel(’sales/order’)->load($orderId);   if ($order->hasInvoices()) { $invoiceIds = $order->getInvoiceCollection()->getAllIds(); $invoiceId = $invoiceIds[0]; echo ‘invoiceId: ‘.$invoiceId."\n"; }   foreach ($order->getAllItems() as $item) { echo $item->getId().’->’.$item->getQtyOrdered()."\n"; }

Images media folder

Magento keeps images under media/catalog/product/ folder, full path to the file in adminhtml can be for example media/catalog/product/[1st_letter_of_filename]/[2nd_letter_of_filename]/filename.jpg In frontend images loaded from media/catalog/product/cache/ folder, full path to cache file in frontend can be for example media/catalog/product/cache/image/*, media/catalog/product/cache/small_image/* and media/catalog/product/cache/thumbnail/* When we do System -> Cache Management -> Flush Catalog Images Cache it deleted entire […]

Magento update order billing or shipping address

$shippingAddress = Mage::getModel(’sales/order_address’)->load($order->getShippingAddress()->getId());   $shippingAddress ->setFirstname("value") ->setMiddlename("value") ->setLastname("value") ->setSuffix("value") ->setCompany("value") ->setStreet("value")) ->setCity("value") ->setCountry_id("value") ->setRegion("value") ->setRegion_id("value") ->setPostcode("value") ->setTelephone("value") ->setFax("value")->save();