Need help on writing magento code for fetching data for this custom mysql query

Question

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

Answer 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;