1

Customizing IWD_SalesRepresentative extension in their reports I want more data than they give by default

Their default code:

    $colunms = $this->_getSelectedColumns();
    $this->getSelect()->from(array('main_table' => $this->getTable('salesrep/sales')))
        ->joinLeft(array('order_table' => $this->getTable('sales/order')), 'main_table.iwd_order_id=order_table.entity_id', $this->_selectedColumns)
        ->joinLeft(array('user_table' => $this->getTable('admin/user')), 'main_table.iwd_user_id=user_table.user_id', "CONCAT(`firstname`,' ', `lastname`) as username")
        ->joinLeft(array('link_user' => $this->getTable('salesrep/users')), 'link_user.iwd_user_id=user_table.user_id', array('iwd_rate_type_order', 'iwd_percent_rate_order', 'iwd_fixed_rate_order'));

    $this->_applyDateRangeFilter();
    $this->_applyOrderStatusFilter();
    $this->_applyOrderStatusFilter();
    $this->_applyStoreFilter();
    $this->_applyUserFilter();

    $this->getSelect()->order('created_at ASC');

    return $this;

which on echo $this->getSelect() returns this mysql query

SELECT `main_table`.*, `order_table`.`billing_address_id`, `order_table`.`shipping_address_id`, `order_table`.`created_at`, `order_table`.`base_subtotal`, `order_table`.`base_total_invoiced`, `order_table`.`base_total_refunded`, `order_table`.`base_tax_amount`, `order_table`.`base_shipping_amount`, `order_table`.`base_discount_amount`, `order_table`.`base_subtotal_canceled`, `order_table`.`increment_id`, CASE WHEN order_table.base_total_refunded IS NOT NULL THEN CASE WHEN iwd_rate_type_order = '1' THEN iwd_percent_rate_order * (order_table.base_total_invoiced-order_table.base_total_refunded) / 100 WHEN iwd_rate_type_order = '2' THEN iwd_fixed_rate_order END WHEN order_table.base_total_refunded IS NULL THEN CASE WHEN iwd_rate_type_order = '1' THEN iwd_percent_rate_order * order_table.base_total_invoiced / 100 WHEN iwd_rate_type_order = '2' THEN iwd_fixed_rate_order END END AS `earned`, CONCAT(`firstname`,' ', `lastname`) AS `username`, `link_user`.`iwd_rate_type_order`, `link_user`.`iwd_percent_rate_order`, `link_user`.`iwd_fixed_rate_order` FROM `iwd_sales_representative` AS `main_table` LEFT JOIN `sales_flat_order` AS `order_table` ON main_table.iwd_order_id=order_table.entity_id LEFT JOIN `admin_user` AS `user_table` ON main_table.iwd_user_id=user_table.user_id LEFT JOIN `iwd_sales_representative_users` AS `link_user` ON link_user.iwd_user_id=user_table.user_id WHERE (order_table.created_at >= '2017-06-01 07:00:00') AND (order_table.created_at <= '2017-06-13 06:59:59') AND (order_table.store_id IN (1, 2, 3)) AND (user_table.user_id = '10') AND (order_table.created_at >= '2017-06-01 07:00:00') AND (order_table.created_at <= '2017-06-13 06:59:59') ORDER BY `created_at` ASC 

& I want to fire query like this

SELECT CONCAT(`orderaddr`.`firstname`,' ', `orderaddr`.`lastname`) AS `billname`, `orderaddr`.`region` AS `billstate`, CONCAT(`ordershipaddr`.`firstname`,' ', `ordershipaddr`.`lastname`) AS `shipname`, `ordershipaddr`.`region` AS `shipstate`, SUBQUERY1.* from ( SELECT `main_table`.*, `order_table`.`billing_address_id`, `order_table`.`shipping_address_id`, `order_table`.`created_at`, `order_table`.`base_subtotal`, `order_table`.`base_total_invoiced`, `order_table`.`base_total_refunded`, `order_table`.`base_tax_amount`, `order_table`.`base_shipping_amount`, `order_table`.`base_discount_amount`, `order_table`.`base_subtotal_canceled`, `order_table`.`increment_id`, `order_table`.`entity_id`, CASE WHEN order_table.base_total_refunded IS NOT NULL THEN CASE WHEN iwd_rate_type_order = '1' THEN iwd_percent_rate_order * (order_table.base_total_invoiced-order_table.base_total_refunded) / 100 WHEN iwd_rate_type_order = '2' THEN iwd_fixed_rate_order END WHEN order_table.base_total_refunded IS NULL THEN CASE WHEN iwd_rate_type_order = '1' THEN iwd_percent_rate_order * order_table.base_total_invoiced / 100 WHEN iwd_rate_type_order = '2' THEN iwd_fixed_rate_order END END AS `earned`, CONCAT(`firstname`,' ', `lastname`) AS `username`, `link_user`.`iwd_rate_type_order`, `link_user`.`iwd_percent_rate_order`, `link_user`.`iwd_fixed_rate_order` FROM `iwd_sales_representative` AS `main_table` LEFT JOIN `sales_flat_order` AS `order_table` ON main_table.iwd_order_id=order_table.entity_id LEFT JOIN `admin_user` AS `user_table` ON main_table.iwd_user_id=user_table.user_id LEFT JOIN `iwd_sales_representative_users` AS `link_user` ON link_user.iwd_user_id=user_table.user_id WHERE (order_table.created_at >= '2017-06-01 07:00:00') AND (order_table.created_at <= '2017-06-13 06:59:59') AND (order_table.store_id IN (1, 2, 3)) AND (user_table.user_id = '10') AND (order_table.created_at >= '2017-06-01 07:00:00') AND (order_table.created_at <= '2017-06-13 06:59:59') ORDER BY `created_at` ASC ) AS SUBQUERY1 LEFT JOIN `sales_flat_order_address` AS `orderaddr` ON `orderaddr`.`entity_id`=`SUBQUERY1`.`billing_address_id` LEFT JOIN `sales_flat_order_address` AS `ordershipaddr` ON `ordershipaddr`.`entity_id`=`SUBQUERY1`.`shipping_address_id`

For that I tried core query like

$resource = Mage::getSingleton('core/resource');
$select =$resource->getConnection('core_read');
$query = "same above query which I customized & want to fire";
$data = $select->fetchAll($query);

which results Syntax error or access violation: 1065 Query was empty in var/report

So, what would be the magento code for $this->getselect() can on help on this?

Note: Using magento Magento ver. 1.9.3.0

1

In case any one wants same thing, posting the answer by myself

$colunms = $this->_getSelectedColumns();
$this->getSelect()->from(array('main_table' => $this->getTable('salesrep/sales')))
    ->joinLeft(array(
        'order_table' => $this->getTable('sales/order')),
        'main_table.iwd_order_id = order_table.entity_id',
        $this->_selectedColumns
    )
    ->joinLeft(array(
        'order_address_table' => $this->getTable('sales/order_address')),
        'order_table.billing_address_id = order_address_table.entity_id',
        array(
            'order_address_table.region as bill_region',
            "CONCAT(`order_address_table`.`firstname`,' ', `order_address_table`.`lastname`) as bill_name"
        )
    )
    ->joinLeft(array(
        'order_address_table1' => $this->getTable('sales/order_address')),
        'order_table.shipping_address_id = order_address_table1.entity_id',
        array(
            'order_address_table1.region as ship_region',
            "CONCAT(`order_address_table1`.`firstname`,' ', `order_address_table1`.`lastname`) as ship_name"
        )
    )
    ->joinLeft(array(
        'user_table' => $this->getTable('admin/user')),
        'main_table.iwd_user_id = user_table.user_id',
        "CONCAT(`user_table`.`firstname`,' ', `user_table`.`lastname`) as username"
    )
    ->joinLeft(array(
        'link_user' => $this->getTable('salesrep/users')),
        'link_user.iwd_user_id = user_table.user_id',
        array('iwd_rate_type_order', 'iwd_percent_rate_order', 'iwd_fixed_rate_order')
    );

$this->_applyDateRangeFilter();
$this->_applyOrderStatusFilter();
$this->_applyStoreFilter();
$this->_applyUserFilter();

$this->getSelect()->order('created_at ASC');
return $this;
| improve this answer | |

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.