4

I am building a custom reports module. I need to add a column which will represent a percent based on the corresponding sale column and total for that column. Bellow is the select I am using for getting all needed data.

public function addTotalSalesByDepartment($frm = '', $to = '')
{
    $qtyOrderedTableName = $this->getTable('sales/order_item');
    $qtyOrderedFieldName = 'qty_ordered';

    $productIdTableName = $this->getTable('catalog/product');
    $productIdFieldName = 'product_id';

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


    $frm = date('Y-m-d G:i:s', strtotime($frm));
    $to = date('Y-m-d G:i:s', strtotime($frm . '+' . '1 day'));

    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_items.{$qtyOrderedFieldName})",
                    'sms_dept_id' => "pei.value", 
                    'sale_percentage' => '**THIS IS THE VALUE I NEED TO CALCULATE**'
   );

   $_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()
   );

   $this->getSelect()
       ->joinInner(array('e' => $productIdTableName),
           "e.entity_id = order_items.{$productIdFieldName}")
       ->order('pei.value ASC')
       ->group('pei.value')
       ->having('SUM(order_items.qty_ordered) > 0');

   $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='sms_dept_id'",
           array());

    Mage::log('SQL: '.$this->getSelect()->__toString());
    return $this;
}

percent column

EDIT: I put as you can see in my comment. How can I deal with undefined index warning for my custom columns when trying to sort after these fields? It is related to my original question and I hope it is fine to put it here.

  • Please share if you have an idea how to achieve what I need or if you need some more explanations – Denisa Nov 17 '14 at 23:47
  • Adding the logged query you've got question should help get an answer: Mage::log('SQL: '.$this->getSelect()->__toString()); – B00MER Nov 25 '14 at 11:27
  • @BOOMER: I tried that but doesn't seem helpful to me. I tried to do something like $query = 'SELECT SUM(base_subtotal) as total FROM ' . $resource->getTableName('sales/order') . ' WHERE created_at > "' . $frm . '" AND created_at < "' . $to . '"'; $results = $readConnection->fetchRow($query); $total = $results['total']; but I think this is not really good for database. – Denisa Nov 26 '14 at 10:58
2

Correct e if I'm wrong but I don't think that the calculation will work due to (my?)SQL limitations. Either you use a subselect or you use the result set as a data source for another select query.

Example for 1) select *, select (select sum(sales) from ...) / (select sum(x) from ...)

Example for 2) select *, ((foo/bar) * 100) as percentage from your result_set with foo and bar as separate columns in your result set.

But I'm not sure if it will work properly with Magento's DBAL.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.