protected function _prepareCollection()
{
$filter = $this->getParam($this->getVarNameFilter(), null);
if (is_null($filter)) {
$filter = $this->_defaultFilter;
}
if (is_string($filter)) {
$data = array();
$filter = base64_decode($filter);
parse_str(urldecode($filter), $data);
$this->_setFilterValues($data);
} else {
if ($filter && is_array($filter)) {
$this->_setFilterValues($filter);
} else {
if (0 !== sizeof($this->_defaultFilter)) {
$this->_setFilterValues($this->_defaultFilter);
}
}
}
$collection = Mage::getModel('catalog/product')->getCollection()->addAttributeToSelect(array('name', 'sku', 'ean', 'color', 'size', 'gender', 'price', 'special_price'));
$filterStockFrom = $this->getFilter('product_stock_from');
$filterStockTo = $this->getFilter('product_stock_to');
$filterCategory = $this->getFilter('product_category');
$filterSubCategory = $this->getFilter('product_sub_category');
$filterProductType = $this->getFilter('product_type');
if (!empty($filterCategory)) {
$category = Mage::getModel('catalog/category')->load($filterCategory);
$collection->addCategoryFilter($category);
}
if (!empty($filterSubCategory)) {
$subcategory = Mage::getModel('catalog/category')->load($filterSubCategory);
$collection->addCategoryFilter($subcategory);
}
$cdt = "";
if ($filterStockFrom != '') {
//$collection->addAttributeToFilter('qty', array('gteq' => $filterStockFrom));
$cdt .= " and `qty` >= " . $filterStockFrom;
}
if ($filterStockTo != '') {
//$collection->addAttributeToFilter('qty', array('lteq' => $filterStockTo));
$cdt .= " and `qty` <= " . $filterStockTo;
}
$collection->getSelect()->join(array('stock' => 'cataloginventory_stock_item'), 'stock.product_id = e.entity_id' . $cdt, array('stock.qty'));
//$collection->getSelect()->joinLeft(array('config'=>'catalog_product_super_link'),'e.entity_id = config.product_id', array('ifnull(`config`.`parent_id`, `e`.`entity_id`) as super'));
if (!empty($filterProductType)) {
$collection->addAttributeToFilter('type_id', $filterProductType);
}
//$collection->addAttributeToFilter('type_id', 'simple');
//->load();
//$collection->getSelect()->order( array('super ASC', 'type_id ASC') );
$collection->getSelect()->order(array(`e` . 'entity_id'));
$this->setCollection($collection);
return parent::_prepareCollection();
}
protected function _prepareCollection()
{
$filter = $this->getParam($this->getVarNameFilter(), null);
if (is_null($filter)) {
$filter = $this->_defaultFilter;
}
if (is_string($filter)) {
$data = array();
$filter = base64_decode($filter);
parse_str(urldecode($filter), $data);
$this->_setFilterValues($data);
} else {
if ($filter && is_array($filter)) {
$this->_setFilterValues($filter);
} else {
if (0 !== sizeof($this->_defaultFilter)) {
$this->_setFilterValues($this->_defaultFilter);
}
}
}
$collection = Mage::getModel('catalog/product')->getCollection()->addAttributeToSelect('*');
//Get Filters from url
$filterCategory = $this->getFilter('product_category');
$filterSubCategory = $this->getFilter('product_sub_category');
$filterVisibility = $this->getFilter('product_visibility');
$filterName = $this->getFilter('product_name');
$filterStock = $this->getFilter('product_stock');
$filterSku = $this->getFilter('product_sku');
$filterProductType = $this->getFilter('product_type');
//Filter Collection by Category/SubCategory
if (!empty($filterCategory)) {
$category = Mage::getModel('catalog/category')->load($filterCategory);
$collection->addCategoryFilter($category);
}
if (!empty($filterSubCategory)) {
$subcategory = Mage::getModel('catalog/category')->load($filterSubCategory);
$collection->addCategoryFilter($subcategory);
}
//Filter Collection by ProductName
if (!empty($filterName)) {
$collection->addAttributeToFilter(array(array('attribute' => 'name', 'like' => '%' . $filterName . '%')));
}
//Filter Collection by Sku
if (!empty($filterSku)) {
$collection->addAttributeToFilter(array(array('attribute' => 'sku', 'like' => '%' . $filterSku . '%')));
}
$collection->addExpressionAttributeToSelect('frontendvisibitity', 'IF(`at_status`.`value` = 1, IF((type_id=\'configurable\' and (`at_visibility`.`value`=2 OR `at_visibility`.`value`=3 OR `at_visibility`.`value`=4) and is_in_stock = 1) OR (type_id=\'simple\' and (`at_visibility`.`value`=1) and `cataloginventory_stock_item`.`qty` > 0 and is_in_stock = 1), \'Yes\', \'No\'), \'No\')', array('type_id', 'status', 'visibility'));
$collection->addExpressionAttributeToSelect('stockstatus', 'IF((type_id=\'configurable\' and is_in_stock = 1) OR (type_id=\'simple\' and `cataloginventory_stock_item`.`qty` > 0 and is_in_stock = 1), \'Yes\', \'No\')', array('type_id'));
//Filter Collection by Visibility
if (isset($filterVisibility)) {
if ($filterVisibility == 1) {
//Yes
$collection->addAttributeToFilter('frontendvisibitity', array('eq' => 'Yes'));
} elseif ($filterVisibility == 2) {
//No
$collection->addAttributeToFilter('frontendvisibitity', array('eq' => 'No'));
}
}
$cond = null;
$collection->joinTable('cataloginventory/stock_item', 'product_id = entity_id', array('qty', 'is_in_stock'), $cond);
//Filter Collection by Stock
if (isset($filterStock)) {
if ($filterStock == 1) {
//Yes
$collection->addAttributeToFilter('stockstatus', array('eq' => 'Yes'));
} elseif ($filterStock == 2) {
//No
$collection->addAttributeToFilter('stockstatus', array('eq' => 'No'));
}
}
//$collection->getSelect()->joinLeft(array('config'=>'catalog_product_super_link'),'e.entity_id = config.product_id', array('ifnull(`config`.`parent_id`, `e`.`entity_id`) as super'));
if (!empty($filterProductType)) {
$collection->addAttributeToFilter('type_id', $filterProductType);
}
//$collection->getSelect()->group(`e`.'sku');
//$collection->getSelect()->order( array('super ASC', 'type_id ASC') );
$collection->getSelect()->order(array(`e` . 'entity_id'));
//print $collection->getSelect();
$this->setCollection($collection);
return parent::_prepareCollection();
}
protected function _prepareCollection()
{
$filter = $this->getParam($this->getVarNameFilter(), null);
if (is_null($filter)) {
$filter = $this->_defaultFilter;
}
if (is_string($filter)) {
$data = array();
$filter = base64_decode($filter);
parse_str(urldecode($filter), $data);
$this->_setFilterValues($data);
} else {
if ($filter && is_array($filter)) {
$this->_setFilterValues($filter);
} else {
if (0 !== sizeof($this->_defaultFilter)) {
$this->_setFilterValues($this->_defaultFilter);
}
}
}
$filterFrom = $this->getFilter('from');
$filterTo = $this->getFilter('to');
$filterSku = $this->getFilter('sku');
$filterCustomerEmail = $this->getFilter('customer_email');
$filterCouponCode = $this->getFilter('coupon_code');
$filterCustomerPostcode = $this->getFilter('customer_postcode');
$filterCategory = $this->getFilter('product_category');
$filterSubCategory = $this->getFilter('product_sub_category');
$filterPaymentMethod = $this->getFilter('payment_method');
$filterPaymentGateway = $this->getFilter('payment_gateway');
$filterDcStatus = $this->getFilter('dc_status');
$filterLatestStatus = $this->getFilter('latest_status');
$filterCourier = $this->getFilter('courier');
$product_type = $this->getFilter('product_type');
if (empty($product_type)) {
$product_type = 'simple';
}
$product_name = $this->getFilter('product_name');
$order_id = $this->getFilter('order_id');
$_collection = Mage::getResourceModel('sales/order_collection');
$orderJoinCondition = array('order.entity_id = order_items.order_id');
$addressJoinCondition = array('a.entity_id = order.billing_address_id', 'a.address_type = "billing"');
$addressJoinConditionShipping = array('b.entity_id = order.shipping_address_id', 'b.address_type = "shipping"');
$shipmentTrackCondition = array('shipment_track.order_id = order.entity_id');
$paymentCondition = array('payment.parent_id = order.entity_id');
$categoryCondition = array('category_product.product_id = order_items.product_id');
$_collection->getSelect()->reset()->from(array('order' => 'sales_flat_order'), array('order_increment_id' => 'order.increment_id', 'customer_balance_amount' => 'order.customer_balance_amount', 'latest_status' => 'order.status', 'dc_status' => 'order.sent_to_erp', 'coupon_code' => 'order.coupon_code', 'coupon_rule_name' => 'order.coupon_rule_name', 'customer_id' => 'order.customer_id', 'grand_total' => 'order.grand_total', 'customer_name' => "CONCAT(COALESCE(order.customer_firstname, ''), ' ', COALESCE(order.customer_lastname, ''))", 'customer_email' => "order.customer_email", 'gift_cards_amount' => 'order.gift_cards_amount', 'source' => 'order.source', 'campaign' => 'order.campaign'))->joinRight(array('order_items' => 'sales_flat_order_item'), implode(' AND ', $orderJoinCondition), array('item_name' => 'order_items.name', 'sku' => 'order_items.sku', 'created_at' => 'order_items.created_at', 'qty' => 'order_items.qty_ordered', 'item_id' => 'order_items.item_id'))->joinLeft(array('shipment_track' => 'sales_flat_shipment_track'), implode(' AND ', $shipmentTrackCondition), array('courier' => "shipment_track.title", 'awb_no' => "shipment_track.track_number", 'shipment_date' => "shipment_track.created_at"), array())->joinLeft(array('payment' => 'sales_flat_order_payment'), implode(' AND ', $paymentCondition), array('payment_gateway' => "payment.method"), array())->joinLeft(array('category_product' => 'catalog_category_product'), implode(' AND ', $categoryCondition), array(), array());
if ($product_type == 'all') {
$_collection->addAttributeToFilter('product_type', array('IN' => array('simple', 'giftcard')));
} else {
$_collection->addAttributeToFilter('product_type', $product_type);
}
if (!empty($product_name)) {
$_collection->addAttributeToFilter('name', array('like' => '%' . $product_name . '%'));
}
if (!empty($order_id)) {
$_collection->addAttributeToFilter('order.increment_id', array('eq' => $order_id));
}
if (!empty($filterFrom)) {
list($dd, $mm, $yyyy) = explode("-", $filterFrom);
$time = mktime('00', '00', '00', $mm, $dd, $yyyy);
$dateFrom = date('Y-m-d H:i:s', $time - 60 * 60 * 5 - 60 * 30);
//$timestamp = strtotime($filterFrom);
//$timestamp = Mage::getModel('core/date')->gmtDate($timestamp);
//$dateFrom = date("Y-m-d H:i:s", $timestamp);
$_collection->addAttributeToFilter('order.created_at', array('gteq' => $dateFrom));
}
if (!empty($filterTo)) {
list($dd, $mm, $yyyy) = explode("-", $filterTo);
$time = mktime('23', '59', '59', $mm, $dd, $yyyy);
$dateTo = date('Y-m-d H:i:s', $time - 60 * 60 * 5 - 60 * 30);
//$timestamp = strtotime($filterTo);
//$timestamp = mktime('23', '59', '59', date("m", $timestamp), date("d", $timestamp), date("Y", $timestamp));
//$timestamp = Mage::getModel('core/date')->gmtDate($timestamp);
//$dateTo = date("Y-m-d H:i:s", $timestamp);
$_collection->addAttributeToFilter('order.created_at', array('lteq' => $dateTo));
}
if (!empty($filterSku)) {
$_collection->addAttributeToFilter('sku', $filterSku);
}
if (!empty($filterCustomerEmail)) {
$_collection->addAttributeToFilter('customer_email', $filterCustomerEmail);
}
if (!empty($filterCouponCode)) {
$_collection->addAttributeToFilter('coupon_code', $filterCouponCode);
}
if (!empty($filterCustomerPostcode)) {
$_collection->addAttributeToFilter('a.postcode', $filterCustomerPostcode);
}
if (!empty($filterSubCategory)) {
$_collection->addAttributeToFilter('category_product.category_id', $filterSubCategory);
} elseif (!empty($filterCategory)) {
$_collection->addAttributeToFilter('category_product.category_id', $filterCategory);
}
if (!empty($filterPaymentMethod)) {
if ($filterPaymentMethod == 'postpaid') {
$_collection->addAttributeToFilter('payment.method', 'cashondelivery');
} else {
$_collection->addAttributeToFilter('payment.method', array('neq' => 'cashondelivery'));
}
}
if (!empty($filterPaymentGateway)) {
$_collection->addAttributeToFilter('payment.method', array('eq' => $filterPaymentGateway));
}
if ($filterDcStatus != "" && $filterDcStatus != '-1') {
$_collection->addAttributeToFilter('order.sent_to_erp', $filterDcStatus);
}
if ($filterLatestStatus != "" && $filterLatestStatus != '0') {
$_collection->addAttributeToFilter('order.status', $filterLatestStatus);
}
if (!empty($filterCourier) && $filterCourier != '0') {
$_collection->addAttributeToFilter('shipment_track.title', $filterCourier);
}
$_collection->getSelect()->group('order_items.item_id');
//Mage::log('SQL: ' . $_collection->getSelect()->__toString());
$this->setCollection($_collection);
return parent::_prepareCollection();
}
