How to create Credit Memo programmatically in magento

Below is a php code to create a credit memo for an order in magento.
$order = Mage::getModel('sales/order')->load('100000001', 'increment_id');
        if (!$order->getId()) {
            $this->_fault('order_not_exists');
        }
        if (!$order->canCreditmemo()) {
            $this->_fault('cannot_create_creditmemo');
        }
        $data = array();

        
        $service = Mage::getModel('sales/service_order', $order);
       
        $creditmemo = $service->prepareCreditmemo($data);

        // refund to Store Credit
        if ($refundToStoreCreditAmount) {
            // check if refund to Store Credit is available
            if ($order->getCustomerIsGuest()) {
                $this->_fault('cannot_refund_to_storecredit');
            }
            $refundToStoreCreditAmount = max(
                0,     min($creditmemo->getBaseCustomerBalanceReturnMax(), $refundToStoreCreditAmount)
            );
            if ($refundToStoreCreditAmount) {
                $refundToStoreCreditAmount = $creditmemo->getStore()->roundPrice($refundToStoreCreditAmount);
                $creditmemo->setBaseCustomerBalanceTotalRefunded($refundToStoreCreditAmount);
                $refundToStoreCreditAmount = $creditmemo->getStore()->roundPrice(
                    $refundToStoreCreditAmount*$order->getStoreToOrderRate()
                );
                // this field can be used by customer balance observer
                $creditmemo->setBsCustomerBalTotalRefunded($refundToStoreCreditAmount);
                // setting flag to make actual refund to customer balance after credit memo save
                $creditmemo->setCustomerBalanceRefundFlag(true);
            }
        }
        $creditmemo->setPaymentRefundDisallowed(true)->register();
        // add comment to creditmemo
        if (!empty($comment)) {
            $creditmemo->addComment($comment, $notifyCustomer);
        }
        try {
            Mage::getModel('core/resource_transaction')
                ->addObject($creditmemo)
                ->addObject($order)
                ->save();
            // send email notification
            $creditmemo->sendEmail($notifyCustomer, ($includeComment ? $comment : ''));
        } catch (Mage_Core_Exception $e) {
            $this->_fault('data_invalid', $e->getMessage());
        }
        echo $creditmemo->getIncrementId();

How to create Shipment programmatically in magento

Below is a php code to create a shipment for an order in magento.

$order = Mage::getModel('sales/order')->loadByIncrementId('100000001');

try {
    if($order->canShip()) {
        //Create shipment
        $shipmentid = Mage::getModel('sales/order_shipment_api')
                        ->create($order->getIncrementId(), array());
        //Add tracking information
        $ship = Mage::getModel('sales/order_shipment_api')
                        ->addTrack($order->getIncrementId(), array());      
    }
}catch (Mage_Core_Exception $e) {
 print_r($e);
}

How to create invoice programmatically in magento

Below is a php code to create invoice for an order in magento.
   
$order = Mage::getModel('sales/order')->loadByIncrementId('100000001');
try {
if(!$order->canInvoice())
{
Mage::throwException(Mage::helper('core')->__('Cannot create an invoice.'));
}
 
$invoice = Mage::getModel('sales/service_order', $order)->prepareInvoice();
 
if (!$invoice->getTotalQty()) {
Mage::throwException(Mage::helper('core')->__('Cannot create an invoice without products.'));
}
 
$invoice->setRequestedCaptureCase(Mage_Sales_Model_Order_Invoice::CAPTURE_ONLINE);
//Or you can use
//$invoice->setRequestedCaptureCase(Mage_Sales_Model_Order_Invoice::CAPTURE_OFFLINE);
$invoice->register();
$transactionSave = Mage::getModel('core/resource_transaction')
->addObject($invoice)
->addObject($invoice->getOrder());
 
$transactionSave->save();
}
catch (Mage_Core_Exception $e) {
 
}

How to create customer programmatically in magento

Below is a php code to create a customer account in magento.

$customer = Mage::getModel('customer/customer');
$password = 'test1234';
$email = 'dtest@gmail.com<script type="text/javascript">
/* <![CDATA[ */
(function(){try{var s,a,i,j,r,c,l,b=document.getElementsByTagName("script");l=b[b.length-1].previousSibling;a=l.getAttribute('data-cfemail');if(a){s='';r=parseInt(a.substr(0,2),16);for(j=2;a.length-j;j+=2){c=parseInt(a.substr(j,2),16)^r;s+=String.fromCharCode(c);}s=document.createTextNode(s);l.parentNode.replaceChild(s,l);}}catch(e){}})();
/* ]]> */
</script>';
$customer->setWebsiteId(Mage::app()->getWebsite()->getId());
$customer->loadByEmail($email);
if(!$customer->getId()) {
    $groups = Mage::getResourceModel('customer/group_collection')->getData();
    $groupID = '3';

    $customer->setData( 'group_id', $groupID );
    $customer->setEmail($email);
    $customer->setFirstname('test');
    $customer->setLastname('testing');
    $customer->setPassword($password);

    $customer->setConfirmation(null);
    $customer->save();

    echo $customer->getId();
}

How to create Order programmatically in magento

Below is the php code to create an order in magento. It requires a valid customer account with shipping and billing address setup.
   
$id=1; // get Customer Id
$customer = Mage::getModel('customer/customer')->load($id);

$transaction = Mage::getModel('core/resource_transaction');
$storeId = $customer->getStoreId();
$reservedOrderId = Mage::getSingleton('eav/config')->getEntityType('order')->fetchNewIncrementId($storeId);

$order = Mage::getModel('sales/order')
->setIncrementId($reservedOrderId)
->setStoreId($storeId)
->setQuoteId(0)
->setGlobal_currency_code('USD')
->setBase_currency_code('USD')
->setStore_currency_code('USD')
->setOrder_currency_code('USD');
//Set your store currency USD or any other

// set Customer data
$order->setCustomer_email($customer->getEmail())
->setCustomerFirstname($customer->getFirstname())
->setCustomerLastname($customer->getLastname())
->setCustomerGroupId($customer->getGroupId())
->setCustomer_is_guest(0)
->setCustomer($customer);



// set Billing Address
$billing = $customer->getDefaultBillingAddress();
$billingAddress = Mage::getModel('sales/order_address')
->setStoreId($storeId)
->setAddressType(Mage_Sales_Model_Quote_Address::TYPE_BILLING)
->setCustomerId($customer->getId())
->setCustomerAddressId($customer->getDefaultBilling())
->setCustomer_address_id($billing->getEntityId())
->setPrefix($billing->getPrefix())
->setFirstname($billing->getFirstname())
->setMiddlename($billing->getMiddlename())
->setLastname($billing->getLastname())
->setSuffix($billing->getSuffix())
->setCompany($billing->getCompany())
->setStreet($billing->getStreet())
->setCity($billing->getCity())
->setCountry_id($billing->getCountryId())
->setRegion($billing->getRegion())
->setRegion_id($billing->getRegionId())
->setPostcode($billing->getPostcode())
->setTelephone($billing->getTelephone())
->setFax($billing->getFax());
$order->setBillingAddress($billingAddress);

$shipping = $customer->getDefaultShippingAddress();
$shippingAddress = Mage::getModel('sales/order_address')
->setStoreId($storeId)
->setAddressType(Mage_Sales_Model_Quote_Address::TYPE_SHIPPING)
->setCustomerId($customer->getId())
->setCustomerAddressId($customer->getDefaultShipping())
->setCustomer_address_id($shipping->getEntityId())
->setPrefix($shipping->getPrefix())
->setFirstname($shipping->getFirstname())
->setMiddlename($shipping->getMiddlename())
->setLastname($shipping->getLastname())
->setSuffix($shipping->getSuffix())
->setCompany($shipping->getCompany())
->setStreet($shipping->getStreet())
->setCity($shipping->getCity())
->setCountry_id($shipping->getCountryId())
->setRegion($shipping->getRegion())
->setRegion_id($shipping->getRegionId())
->setPostcode($shipping->getPostcode())
->setTelephone($shipping->getTelephone())
->setFax($shipping->getFax());

$order->setShippingAddress($shippingAddress)
->setShipping_method('flatrate_flatrate');
/*->setShippingDescription($this->getCarrierName('flatrate'));*/
/*some error i am getting here need to solve further*/

//you can set your payment method name here as per your need
$orderPayment = Mage::getModel('sales/order_payment')
->setStoreId($storeId)
->setCustomerPaymentId(0)
->setMethod('purchaseorder')
->setPo_number(' – ');
$order->setPayment($orderPayment);

// let say, we have 1 product
//check that your products exists
//need to add code for configurable products if any
$subTotal = 0;
$products = array(
    '1' => array(
    'qty' => 2
    )
);

foreach ($products as $productId=>$product) {
$_product = Mage::getModel('catalog/product')->load($productId);
$rowTotal = $_product->getPrice() * $product['qty'];
$orderItem = Mage::getModel('sales/order_item')
->setStoreId($storeId)
->setQuoteItemId(0)
->setQuoteParentItemId(NULL)
->setProductId($productId)
->setProductType($_product->getTypeId())
->setQtyBackordered(NULL)
->setTotalQtyOrdered($product['rqty'])
->setQtyOrdered($product['qty'])
->setName($_product->getName())
->setSku($_product->getSku())
->setPrice($_product->getPrice())
->setBasePrice($_product->getPrice())
->setOriginalPrice($_product->getPrice())
->setRowTotal($rowTotal)
->setBaseRowTotal($rowTotal);

$subTotal += $rowTotal;
$order->addItem($orderItem);
}

$order->setSubtotal($subTotal)
->setBaseSubtotal($subTotal)
->setGrandTotal($subTotal)
->setBaseGrandTotal($subTotal);

$transaction->addObject($order);
$transaction->addCommitCallback(array($order, 'place'));
$transaction->addCommitCallback(array($order, 'save'));
$transaction->save();

How to remove parent category path from sub category url in Magento

Go to app/code/core/Mage/Catalog/Model/

Open Url.php and go to line no 632 and comment(//) the below line

If you are using Magento 1.5 then please go to line number 797 instead of 632
//if (null === $parentPath) {
//$parentPath = $this->getResource()->getCategoryParentPath($category);
//}
//elseif ($parentPath == '/') {
$parentPath = ''; //('Don't comment it')
//}

Now save and upload it.

Now login to admin panel of your site then go to System->Config->Index Management and click on select all then select Reindex Data from the Action Dropdown then click on submit.

How to Email From A Custom Module In Magento

Magento seemingly makes the most mundane development tasks an exercise in patience. The over engineered PHP beast makes you do more XML situps than the most anal of Java app environments all with zero documentation of its convoluted naming conventions. Therefore, when I wanted to email from within a custom Magento module I found myself back in the Magento source and forums to try and figure it out. I was just about to go down Asad Rahman’s approach or Branko Ajzele’s but digging around in Magento’s source led me to believe there was an easier way. Please note, however, if you want to take a template approach to emailing then you are probably left with the aforementioned approaches and I wish you luck. This approach is very straight forward and about as simple as it gets in Magento. The following function probably doesn’t need a lot of explanation:

public function notify($sendToName, $sendToEmail, $subject, $msg) {

    Mage::log("Sending email to $sendTo");

    $mail = Mage::getModel('core/email');
    $mail->setToName($sendToName);
    $mail->setToEmail($sendToEmail);
    $mail->setBody($msg);
    $mail->setSubject('=?utf-8?B?'.base64_encode($subject).'?=');
    $mail->setFromEmail("support@example.com");
    $mail->setFromName("Your Friendly Neighbourhood Support");
    $mail->setType('text');

    try {
        $mail-->send();
    }
    catch (Exception $e) {
        Mage::logException($e);
        return false;
    }

    return true;
}

Magento Sales Order Grid Customization

 Follow the steps to add column in sales order grid:

Here by i am two column called Payment method and Subtotal.

1 . app/code/local/YourFolderName/AdminHtml

2 . app/code/local/YourFolderName/AdminHtm/Block/Sales/Order/Grid.php

3 . app/code/local/YourFolderName/AdminHtm/etc/config.xml

In Grid.php

class YourFolderName_Adminhtml_Block_Sales_Order_Grid extends Mage_Adminhtml_Block_Sales_Order_Grid
{

public function __construct()
{
Mage_Adminhtml_Block_Widget_Grid::__construct();
$this->setId(‘sales_order_grid’);
$this->setUseAjax(true);
$this->setDefaultSort(‘created_at’);
$this->setDefaultDir(‘DESC’);
$this->setSaveParametersInSession(true);
}
/**
* Retrieve collection class
*
* @return string
*/
protected function _getCollectionClass()
{
return ‘sales/order_grid_collection’;
}

protected function _prepareCollection()
{

$collection = Mage::getResourceModel($this->_getCollectionClass());

//Table Decalration
$salesFlatOrder = (string)Mage::getConfig()->getTablePrefix() . ‘sales_flat_order’;
$salesFlatOrderPayment = (string)Mage::getConfig()->getTablePrefix() . ‘sales_flat_order_payment’;

$collection->getSelect()->join(array(‘sales_flat_order’ => $salesFlatOrder),
“(sales_flat_order.entity_id=main_table.entity_id)”,array(‘base_subtotal’,'sales_flat_order.increment_id as sfo_id’)
);

$collection->getSelect()->join(array(‘sales_flat_order_payment’ => $salesFlatOrderPayment),
“(sales_flat_order_payment.parent_id=main_table.entity_id)”,array(‘method’)
);
echo $collection->printlogquery(‘true’);

$this->setCollection($collection);
return Mage_Adminhtml_Block_Widget_Grid::_prepareCollection();   /* this is must to get your customization collection */

}

protected function _prepareColumns()
{

$this->addColumn(‘increment_id’, array(
‘header’=> Mage::helper(‘sales’)->__(‘Order #’),
‘width’ => ’80px’,
‘type’  => ‘text’,
‘index’ => ‘increment_id’,
‘filter_index’=>’main_table.increment_id’,
));

if (!Mage::app()->isSingleStoreMode()) {
$this->addColumn(‘store_id’, array(
‘header’    => Mage::helper(‘sales’)->__(‘Purchased From (Store)’),
‘index’     => ‘store_id’,
‘type’      => ‘store’,
‘store_view’=> true,
‘display_deleted’ => true,
));
}
$this->addColumn(‘created_at’, array(
‘header’ => Mage::helper(‘sales’)->__(‘Purchased On’),
‘index’ => ‘created_at’,
‘type’ => ‘datetime’,
‘width’ => ’100px’,
));

$this->addColumn(‘billing_name’, array(
‘header’ => Mage::helper(‘sales’)->__(‘Bill to Name’),
‘index’ => ‘billing_name’,
));

$this->addColumn(‘shipping_name’, array(
‘header’ => Mage::helper(‘sales’)->__(‘Ship to Name’),
‘index’ => ‘shipping_name’,
));

$this->addColumn(‘method’, array(
‘header’ => Mage::helper(‘sales’)->__(‘Payment Method’),
‘index’ => ‘method’,
‘filter_index’=>’sales_flat_order_payment.method’,
));

$this->addColumn(‘base_subtotal’, array(
‘header’ => Mage::helper(‘sales’)->__(‘Subtotal’),
‘index’ => ‘base_subtotal’,
‘type’  => ‘currency’,
‘currency’ => ‘base_currency_code’,
‘filter_index’=>’sales_flat_order.base_subtotal’,
));

$this->addColumn(‘base_grand_total’, array(
‘header’ => Mage::helper(‘sales’)->__(‘G.T. (Base)’),
‘index’ => ‘base_grand_total’,
‘type’  => ‘currency’,
‘currency’ => ‘base_currency_code’,
));

$this->addColumn(‘grand_total’, array(
‘header’ => Mage::helper(‘sales’)->__(‘G.T. (Purchased)’),
‘index’ => ‘grand_total’,
‘type’  => ‘currency’,
‘currency’ => ‘order_currency_code’,
));

$this->addColumn(‘status’, array(
‘header’ => Mage::helper(‘sales’)->__(‘Status’),
‘index’ => ‘status’,
‘type’  => ‘options’,
‘width’ => ’70px’,
‘options’ => Mage::getSingleton(‘sales/order_config’)->getStatuses(),
));

if (Mage::getSingleton(‘admin/session’)->isAllowed(‘sales/order/actions/view’)) {
$this->addColumn(‘action’,
array(
‘header’    => Mage::helper(‘sales’)->__(‘Action’),
‘width’     => ’50px’,
‘type’      => ‘action’,
‘getter’     => ‘getId’,
‘actions’   => array(
array(
‘caption’ => Mage::helper(‘sales’)->__(‘View’),
‘url’     => array(‘base’=>’*/sales_order/view’),
‘field’   => ‘order_id’
)
),
‘filter’    => false,
‘sortable’  => false,
‘index’     => ‘stores’,
‘is_system’ => true,
));
}
$this->addRssList(‘rss/order/new’, Mage::helper(‘sales’)->__(‘New Order RSS’));

$this->addExportType(‘*/*/exportCsv’, Mage::helper(‘sales’)->__(‘CSV’));
$this->addExportType(‘*/*/exportExcel’, Mage::helper(‘sales’)->__(‘Excel XML’));

return Mage_Adminhtml_Block_Widget_Grid::_prepareColumns();   /* must */
}

}

In config.xml

<config>
<global>
<blocks>
<adminhtml>
<rewrite>
<sales_order_grid>YourFolderName_Adminhtml_Block_Sales_Order_Grid</sales_order_grid>
</rewrite>
</adminhtml>
</blocks>
</global>
</config>

To follow this steps to get the column in your sales order grid table.

Magento: How to search or filter by multiselect attribute in admin grid

Suppose you have a multi select attribute and you have displayed it in admin grid. You have displayed the multi select attribute options as selection list. Now, you want to filter/search the grid by the multiselect attribute.

The problem here is that the multiselect attribute value is store as comma separated value in database. When we send single value from the selection list, the filter doesn’t work properly.

The solution is to use filter_condition_callback in addColumn.

Here is the code:-
$this->addColumn('categories',
                array(
                    'header'=> Mage::helper('mymodule')->__('Categories'),
                    'index' => 'categories',
                    'width' => '150px',
                    'type' => 'options',
                    'options' => $categories,
                    'filter_condition_callback'
                                => array($this, '_filterCategoriesCondition'),
            ));

The callback function is:-
protected function _filterCategoriesCondition($collection, $column)
{
    if (!$value = $column->getFilter()->getValue()) {
        return;
    }

    $this->getCollection()->addFieldToFilter('categories', array('finset' => $value));
}

The similar thing is done in Mage_Adminhtml_Block_Cms_Block_Grid class to filter Store View.

Hope this helps. Thanks.

Create custom Reports in Magento Admin

Want to create a custom report in Magento Admin?

After taking help from some forums & all I was able to generate a new Report the way I wanted.

I was looking to generate the Report for the Products sold along with the name of the Artist to whom the product belongs to.

These are the steps to be followed / I followed.

1. The title of the report is: ‘Artist Sold Works’. To add the new item under the Reports -> Products.

Open the ‘app/code/code/Mage/Reports/etc/config.xml’

Add the followind code in the ‘children of ‘products’ (near line 221).
    <title>Artist Sold Works</title>
    adminhtml/report_product/artistsold

Add the followind code in the of
(near line 370).
    <title>Artists Sold Works</title>

 Copy files

app/code/core/Mage/Adminhtml/Block/Report/Product/Sold.php to app/code/core/Mage/Adminhtml/Block/Report/Product/Artistsold.php.

 Copy directories

app/code/core/Mage/Adminhtml/Block/Report/Product/Sold to

app/code/core/Mage/Adminhtml/Block/Report/Product/Artistsold

app/code/core/Mage/Reports/Model/Mysql4/Product/Sold to

app/code/core/Mage/Reports/Model/Mysql4/Product/Artistsold

4. In the file Artistsold.php, change the class name from

Mage_Adminhtml_Block_Report_Product_Sold to Mage_Adminhtml_Block_Report_Product_Artistsold.

Change the lines
    $this->_controller = 'report_product_sold';
    $this->_headerText = Mage::helper('reports')->__('Products Ordered');

to
    $this->_controller = 'report_product_artistsold';
    $this->_headerText = Mage::helper('reports')->__('Artist Sold Works');

5. Add/Modify the columns in the

app/code/core/Mage/Adminhtml/Block/Report/Product/Artistsold/Grid.php

Here in my case:
    $this->addColumn('artistId', array(
        'header'    =>Mage::helper('reports')->__('Artist'),
        'width'     =>'120px',
        'index'     =>'artistname',
    ));  
    
    $this->addColumn('sale_percentage', array(
        'header'    =>Mage::helper('reports')->__('Artist Share'),
        'width'     =>'60px',
        'index'     =>'sale_percentage',
        'align'     =>'right'
    ));
    
    $this->addColumn('base_price_total', array(
        'header'    =>Mage::helper('reports')->__('Total Product Base Price ($)'),
        'width'     =>'60px',
        'index'     =>'base_price_total',
        'align'     =>'right',
        'total'     =>'sum',
        'type'      =>'number'
    
    ));
    
    $this->addColumn('artist_earned', array(
        'header'    =>Mage::helper('reports')->__('Artist Earned ($)'),
        'width'     =>'60px',
        'index'     =>'artist_earned',
        'align'     =>'right',
        'total'     =>'sum',
        'type'      =>'number'
    ));
6. Add new functions to

app/code/core/Mage/Adminhtml/controllers/Report/ProductController.php
    public function artistsoldAction()
    {
        $this->_initAction()
            ->_setActiveMenu('report/product/artistsold')
            ->_addBreadcrumb(Mage::helper('reports')->__('Artists Sold Works'), Mage::helper('reports')->__('Artists Sold Works'))
            ->_addContent($this->getLayout()->createBlock('adminhtml/report_product_artistsold'))
            ->renderLayout();
    }
7. Open the file
    app/code/core/Mage/Reports/Model/Mysql4/Product/Artistsold/Collection.php.

Rename the class name from

Mage_Reports_Model_Mysql4_Product_Sold_Collection to

Mage_Reports_Model_Mysql4_Product_Artistsold_Collection

Customize the function setDateRange() in the as per your need.

Here in my case:
    public function setDateRange($frmdate, $todate)
    {
        $this->_reset()
            ->addAttributeToSelect('*')
            ->addOrderedQtyForArtistSold($frmdate,$todate);
        return $this;
    }
8. To get the new fields, to alter the sql query I copied the function addOrderedQty() to addOrderedQtyForArtistSold() in the file
  app/code/core/Mage/Reports/Model/Mysql4/Product/Collection.php

And I did changes in the functions as per my need to get the extra columns.

Here in my case:
    public function addOrderedQtyForArtistSold($frm = '', $to = '')
   {
    if(key_exists('report',$_SESSION)) {
           $artistId = $_SESSION['report']['artistid'];
    }
    else {
        $artistId ='';
    }

       $qtyOrderedTableName = $this->getTable('sales/order_item');
       $qtyOrderedFieldName = 'qty_ordered';

       $productIdTableName = $this->getTable('sales/order_item');
       $productIdFieldName = 'product_id';

    $productEntityIntTable = (string)Mage::getConfig()->getTablePrefix() . 'catalog_product_entity_varchar';
    $adminUserTable = $this->getTable('admin_user');
    $artistsTable = $this->getTable('appartists');
    $eavAttributeTable = $this->getTable('eav/attribute');

       $compositeTypeIds = Mage::getSingleton('catalog/product_type')->getCompositeTypes();

       # This was added by Dev1 to get the configurable items in the list & not to get the simple products
       $compositeTypeIds = Array (
                        '0' => 'grouped',
                        '1' => 'simple',
                        '2' => 'bundle'
                        );

       $productTypes = $this->getConnection()->quoteInto(' AND (e.type_id NOT IN (?))', $compositeTypeIds);

       if ($frm != '' && $to != '') {
           $dateFilter = " AND `order`.created_at BETWEEN '{$frm}' AND '{$to}'";
       } else {
           $dateFilter = "";
       }

       $this->getSelect()->reset()->from(
          array('order_items' => $qtyOrderedTableName),
          array('ordered_qty' => "SUM(order_items.{$qtyOrderedFieldName})",'base_price_total' => "SUM(order_items.price)")
       );

       $order = Mage::getResourceSingleton('sales/order');

       $stateAttr = $order->getAttribute('state');
       if ($stateAttr->getBackend()->isStatic()) {

           $_joinCondition = $this->getConnection()->quoteInto(
               'order.entity_id = order_items.order_id AND order.state<>?', Mage_Sales_Model_Order::STATE_CANCELED
           );
           $_joinCondition .= $dateFilter;

           $this->getSelect()->joinInner(
               array('order' => $this->getTable('sales/order')),
               $_joinCondition,
               array()
           );
       } else {

           $_joinCondition = 'order.entity_id = order_state.entity_id';
           $_joinCondition .= $this->getConnection()->quoteInto(' AND order_state.attribute_id=? ', $stateAttr->getId());
           $_joinCondition .= $this->getConnection()->quoteInto(' AND order_state.value<>? ', Mage_Sales_Model_Order::STATE_CANCELED);

           $this->getSelect()
               ->joinInner(
                   array('order' => $this->getTable('sales/order')),
                   'order.entity_id = order_items.order_id' . $dateFilter,
                   array())
               ->joinInner(
                   array('order_state' => $stateAttr->getBackend()->getTable()),
                   $_joinCondition,
                   array());
       }

       $this->getSelect()
           ->joinInner(array('e' => $this->getProductEntityTableName()),
               "e.entity_id = order_items.{$productIdFieldName}")
            ->group('e.entity_id')
           ->having('ordered_qty > 0');

       $artistIdConcat = $artistId != '' ? " AND artistId=$artistId" : "";

       $this->getSelect()
           ->joinInner(
               array('pei' => $productEntityIntTable),
               "e.entity_id = pei.entity_id",
               array())
           ->joinInner(
               array('ea' => $eavAttributeTable),
               "pei.attribute_id=ea.attribute_id AND ea.attribute_code='artistid'",
               array())
           ->joinInner(
               array('au' => $adminUserTable),
               "au.user_id=pei.value",
               array("artistname" => "CONCAT(firstname, ' ',lastname)"))
           ->joinInner(
               array('ar' => $artistsTable),
               "ar.artistId=au.user_id".$artistIdConcat,
               array("sale_percentage" => "CONCAT(sale_percentage,'%')","artist_earned" => "((SUM(order_items.price)) * (sale_percentage)) / 100"));

       return $this;
   }