1

I am working on a segmented version of Magento's Invoiced Totals report. Currently without my additional filters the report reflects what Magento's Core report shows, good.

I am trying to add a condition that will narrow the results to orders whose customer_email column is in a customer email list. In the resource below I have added...

    if ($getRegisteredBusinessData == true):
        $businessAccounts = Mage::getModel("etre_reports/customersegmented")->getRegisteredBusinessAccounts();
        $businessEmailList = Mage::getModel("etre_reports/customersegmented")->getAccountsEmailsArr($businessAccounts);
        $select->where('customer_email IN (?)', $businessEmailList);
        //echo $filterSubSelect->__toString(); die;
    endif;

Basically this interprets to $businessEmailList = ["email@1.com",email@2.com] and the filter being $select->where('customer_email IN (?), $businessEmailList);

My problem is what my results include orders that are not in my $businessEmailList array.

Full Resource:

/**
 * Aggregate Invoiced data by invoice created_at as period
 *
 * @param mixed $from
 * @param mixed $to
 * @return Etre_Reports_Model_Resource_Report_Segment_Invoiced
 */
protected function _aggregateByInvoiceCreatedAt($from, $to, $segmentId = 0)
{
    $table = $this->getTable('etre_reports/segmented_invoiced_aggregated');
    $sourceTable = $this->getTable('sales/invoice');
    $orderTable = $this->getTable('sales/order');
    $helper = Mage::getResourceHelper('core');
    $adapter = $this->_getWriteAdapter();

    $adapter->beginTransaction();

    try {
        if ($from !== null || $to !== null) {
            $subSelect = $this->_getTableDateRangeRelatedSelect(
                $sourceTable, $orderTable, array('order_id' => 'entity_id'),
                'created_at', 'updated_at', $from, $to
            );
        } else {
            $subSelect = null;
        }

        $this->_clearTableByDateRange($table, $from, $to, $subSelect);
        // convert dates from UTC to current admin timezone
        $periodExpr = $adapter->getDatePartSql(
            $this->getStoreTZOffsetQuery(
                array('source_table' => $sourceTable),
                'source_table.created_at', $from, $to
            )
        );
        $columns = array(
            // convert dates from UTC to current admin timezone
            'segment_id' => new Zend_Db_Expr($segmentId), /* Magento expects to find these values in the sales_order table - work around or add segment id to ALL orders :/ */
            'increment_ids' => new Zend_Db_Expr('GROUP_CONCAT(source_table.increment_id SEPARATOR ",")'),
            'period' => $periodExpr,
            'store_id' => 'order_table.store_id',
            'order_status' => 'order_table.status',
            'orders_count' => new Zend_Db_Expr('COUNT(order_table.entity_id)'),
            'orders_invoiced' => new Zend_Db_Expr('COUNT(order_table.entity_id)'),
            'invoiced' => new Zend_Db_Expr('SUM(order_table.base_total_invoiced'
                . ' * order_table.base_to_global_rate)'),
            'invoiced_captured' => new Zend_Db_Expr('SUM(order_table.base_total_paid'
                . ' * order_table.base_to_global_rate)'),
            'invoiced_not_captured' => new Zend_Db_Expr(
                'SUM((order_table.base_total_invoiced - order_table.base_total_paid)'
                . ' * order_table.base_to_global_rate)')
        );

        $select = $adapter->select();
        $select->from(array('source_table' => $sourceTable), $columns)
            ->joinInner(
                array('order_table' => $orderTable),
                $adapter->quoteInto(
                    'source_table.order_id = order_table.entity_id AND order_table.state <> ?',
                    Mage_Sales_Model_Order::STATE_CANCELED),
                array()
            );
        $filterSubSelect = $adapter->select();
        $getRegisteredBusinessData = true;
        $filterSubSelect->from(array('filter_source_table' => $sourceTable), 'MAX(filter_source_table.entity_id)')
            ->where('filter_source_table.order_id = source_table.order_id');

        if ($subSelect !== null) {
            $select->having($this->_makeConditionFromDateRangeSelect($subSelect, 'period'));
        }

        $select->where('source_table.entity_id = (?)', new Zend_Db_Expr($filterSubSelect));

        if ($getRegisteredBusinessData == true):
            $businessAccounts = Mage::getModel("etre_reports/customersegmented")->getRegisteredBusinessAccounts();
            $businessEmailList = Mage::getModel("etre_reports/customersegmented")->getAccountsEmailsArr($businessAccounts);
            $select->where('customer_email IN (?)', $businessEmailList);
            //echo $filterSubSelect->__toString(); die;
        endif;
        unset($filterSubSelect);

        $adapter->query("SET group_concat_max_len = {$this->ordersAllowedInMysqlConcat()}");
        $select->group(array(
            $periodExpr,
            'order_table.store_id',
            'order_table.status'
        ));

        $select->having('orders_count > 0');
        zend_debug::dump($adapter->fetchAssoc($select));
        die;
        unset($columns['increment_ids']);
        $insertQuery = $helper->getInsertFromSelectUsingAnalytic($select, $table, array_keys($columns));
        $adapter->query($insertQuery);
        $select->reset();

        $columns = array(
            'segment_id' => new Zend_Db_Expr($segmentId), /* Magento expects to find these values in the sales_order table - work around or add segment id to ALL orders :/ */
            'period' => 'period',
            'store_id' => new Zend_Db_Expr(Mage_Core_Model_App::ADMIN_STORE_ID),
            'order_status' => 'order_status',
            'orders_count' => new Zend_Db_Expr('SUM(orders_count)'),
            'orders_invoiced' => new Zend_Db_Expr('SUM(orders_invoiced)'),
            'invoiced' => new Zend_Db_Expr('SUM(invoiced)'),
            'invoiced_captured' => new Zend_Db_Expr('SUM(invoiced_captured)'),
            'invoiced_not_captured' => new Zend_Db_Expr('SUM(invoiced_not_captured)')
        );

        $select
            ->from($table, $columns)
            ->where('store_id <> ?', 0);

        if ($subSelect !== null) {
            $select->where($this->_makeConditionFromDateRangeSelect($subSelect, 'period'));
        }

        $select->group(array(
            'period',
            'order_status'
        ));

        $insertQuery = $helper->getInsertFromSelectUsingAnalytic($select, $table, array_keys($columns));
        $adapter->query($insertQuery);
        $adapter->commit();
    } catch (Exception $e) {
        $adapter->rollBack();
        throw $e;
    }

    return $this;
}
2

i think output you want put filter in condition but you are missing $businessEmailList->getSelect()

$businessEmailList = Mage::getModel("etre_reports/customersegmented")->getAccountsEmailsArr($businessAccounts);
        $businessEmailList->getSelect()->where('customer_email IN (?)', $businessEmailList);

or you can add filter like that

        $newlist=explode(',',$businessEmailList);
        $businessEmailList = Mage::getModel("etre_reports/customersegmented")->getAccountsEmailsArr($businessAccounts);
          $businessEmailList->addFieldToFilter('customer_email', array('in'=>$newlist)));
  • Well, I have two different queries. One creates my $businessEmailList array and my other query is in $select, which can be seen in the full function in my original post. I want to add a filter to $select so that the results only include orders created by emails in $businessEmailList. This returns orders that are not in $businessEmailList: $select->where('customer_email IN (?)', $businessEmailList); – TylersSN Jan 11 '16 at 14:33

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.