00001 <?php
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011
00012
00013
00014
00015
00016
00017
00018
00019
00020
00021
00022
00023
00024
00025
00026
00027
00028
00029
00030
00031
00032
00033
00034
00035 class Mage_Reports_Model_Mysql4_Product_Collection extends Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Collection
00036 {
00037 const SELECT_COUNT_SQL_TYPE_CART = 1;
00038
00039 protected $_productEntityId;
00040 protected $_productEntityTableName;
00041 protected $_productEntityTypeId;
00042 protected $_selectCountSqlType = 0;
00043
00044
00045
00046
00047
00048
00049
00050 public function setSelectCountSqlType($type)
00051 {
00052 $this->_selectCountSqlType = $type;
00053 return $this;
00054 }
00055
00056 public function setProductEntityId($value)
00057 {
00058 $this->_productEntityId = $value;
00059 return $this;
00060 }
00061
00062 public function getProductEntityId()
00063 {
00064 return $this->_productEntityId;
00065 }
00066
00067 public function setProductEntityTableName($value)
00068 {
00069 $this->_productEntityTableName = $value;
00070 return $this;
00071 }
00072
00073 public function getProductEntityTableName()
00074 {
00075 return $this->_productEntityTableName;
00076 }
00077
00078 public function setProductEntityTypeId($value)
00079 {
00080 $this->_productEntityTypeId = $value;
00081 return $this;
00082 }
00083
00084 public function getProductEntityTypeId()
00085 {
00086 return $this->_productEntityTypeId;
00087 }
00088
00089 public function __construct()
00090 {
00091 $product = Mage::getResourceSingleton('catalog/product');
00092
00093 $this->setProductEntityId($product->getEntityIdField());
00094 $this->setProductEntityTableName($product->getEntityTable());
00095 $this->setProductEntityTypeId($product->getTypeId());
00096
00097 parent::__construct();
00098 }
00099
00100 protected function _joinFields()
00101 {
00102 $this->_totals = new Varien_Object();
00103
00104 $this->addAttributeToSelect('entity_id')
00105 ->addAttributeToSelect('name')
00106 ->addAttributeToSelect('price');
00107
00108
00109
00110
00111
00112
00113
00114 }
00115
00116 public function getSelectCountSql()
00117 {
00118 if ($this->_selectCountSqlType == self::SELECT_COUNT_SQL_TYPE_CART) {
00119 $countSelect = clone $this->getSelect();
00120 $countSelect->reset()
00121 ->from(array('quote_item_table' => $this->getTable('sales/quote_item')), 'COUNT(DISTINCT quote_item_table.product_id)')
00122 ->join(
00123 array('quote_table' => $this->getTable('sales/quote')),
00124 'quote_table.entity_id = quote_item_table.quote_id AND quote_table.is_active = 1',
00125 array()
00126 );
00127 return $countSelect->__toString();
00128 }
00129
00130 $countSelect = clone $this->getSelect();
00131 $countSelect->reset(Zend_Db_Select::ORDER);
00132 $countSelect->reset(Zend_Db_Select::LIMIT_COUNT);
00133 $countSelect->reset(Zend_Db_Select::LIMIT_OFFSET);
00134 $countSelect->reset(Zend_Db_Select::COLUMNS);
00135 $countSelect->reset(Zend_Db_Select::GROUP);
00136 $countSelect->reset(Zend_Db_Select::HAVING);
00137 $countSelect->from("", "count(DISTINCT e.entity_id)");
00138 $sql = $countSelect->__toString();
00139 return $sql;
00140 }
00141
00142 public function addCartsCount()
00143 {
00144 $countSelect = clone $this->getSelect();
00145 $countSelect->reset();
00146
00147 $countSelect->from(array("quote_items" => $this->getTable('sales/quote_item')), "count(*)")
00148 ->join(array('quotes' => $this->getTable('sales/quote')),
00149 'quotes.entity_id = quote_items.quote_id AND quotes.is_active = 1',
00150 array())
00151 ->where("quote_items.product_id = e.entity_id");
00152
00153 $this->getSelect()
00154 ->from("", array("carts" => "({$countSelect})"))
00155 ->group("e.{$this->getProductEntityId()}")
00156 ->having('carts > 0');
00157
00158 return $this;
00159 }
00160
00161 public function addOrdersCount($from = '', $to = '')
00162 {
00163 $this->getSelect()
00164 ->joinLeft(array("order_items" => $this->getTable('sales/order_item')),
00165 "order_items.product_id = e.{$this->getProductEntityId()}", array())
00166 ->from("", array("orders" => "count(`order_items2`.item_id)"))
00167 ->group("e.{$this->getProductEntityId()}");
00168
00169 if ($from != '' && $to != '') {
00170 $dateFilter = " and order_items2.created_at BETWEEN '{$from}' AND '{$to}'";
00171 } else {
00172 $dateFilter = '';
00173 }
00174
00175 $this->getSelect()
00176 ->joinLeft(array("order_items2" => $this->getTable('sales/order_item')),
00177 "order_items2.item_id = order_items.item_id".$dateFilter, array());
00178
00179 return $this;
00180 }
00181
00182 public function addOrderedQty($from = '', $to = '')
00183 {
00184 $qtyOrderedTableName = $this->getTable('sales/order_item');
00185 $qtyOrderedFieldName = 'qty_ordered';
00186
00187 $productIdTableName = $this->getTable('sales/order_item');
00188 $productIdFieldName = 'product_id';
00189
00190 $compositeTypeIds = Mage::getSingleton('catalog/product_type')->getCompositeTypes();
00191 $productTypes = $this->getConnection()->quoteInto(' AND (e.type_id NOT IN (?))', $compositeTypeIds);
00192
00193 if ($from != '' && $to != '') {
00194 $dateFilter = " AND `order`.created_at BETWEEN '{$from}' AND '{$to}'";
00195 } else {
00196 $dateFilter = "";
00197 }
00198
00199 $this->getSelect()->reset()->from(
00200 array('order_items' => $qtyOrderedTableName),
00201 array('ordered_qty' => "SUM(order_items.{$qtyOrderedFieldName})")
00202 );
00203
00204 $order = Mage::getResourceSingleton('sales/order');
00205
00206 $stateAttr = $order->getAttribute('state');
00207 if ($stateAttr->getBackend()->isStatic()) {
00208
00209 $_joinCondition = $this->getConnection()->quoteInto(
00210 'order.entity_id = order_items.order_id AND order.state<>?', Mage_Sales_Model_Order::STATE_CANCELED
00211 );
00212 $_joinCondition .= $dateFilter;
00213
00214 $this->getSelect()->joinInner(
00215 array('order' => $this->getTable('sales/order')),
00216 $_joinCondition,
00217 array()
00218 );
00219 } else {
00220
00221 $_joinCondition = 'order.entity_id = order_state.entity_id';
00222 $_joinCondition .= $this->getConnection()->quoteInto(' AND order_state.attribute_id=? ', $stateAttr->getId());
00223 $_joinCondition .= $this->getConnection()->quoteInto(' AND order_state.value<>? ', Mage_Sales_Model_Order::STATE_CANCELED);
00224
00225 $this->getSelect()
00226 ->joinInner(
00227 array('order' => $this->getTable('sales/order')),
00228 'order.entity_id = order_items.order_id' . $dateFilter,
00229 array())
00230 ->joinInner(
00231 array('order_state' => $stateAttr->getBackend()->getTable()),
00232 $_joinCondition,
00233 array());
00234 }
00235
00236 $this->getSelect()
00237 ->joinInner(array('e' => $this->getProductEntityTableName()),
00238 "e.entity_id = order_items.{$productIdFieldName} AND e.entity_type_id = {$this->getProductEntityTypeId()}{$productTypes}")
00239 ->group('e.entity_id')
00240 ->having('ordered_qty > 0');
00241
00242 return $this;
00243 }
00244
00245 public function setOrder($attribute, $dir='desc')
00246 {
00247 switch ($attribute)
00248 {
00249 case 'carts':
00250 case 'orders':
00251 case 'ordered_qty':
00252 $this->getSelect()->order($attribute . ' ' . $dir);
00253 break;
00254 default:
00255 parent::setOrder($attribute, $dir);
00256 }
00257
00258 return $this;
00259 }
00260
00261 public function addViewsCount($from = '', $to = '')
00262 {
00263
00264
00265
00266 foreach (Mage::getModel('reports/event_type')->getCollection() as $eventType) {
00267 if ($eventType->getEventName() == 'catalog_product_view') {
00268 $productViewEvent = $eventType->getId();
00269 break;
00270 }
00271 }
00272
00273 $this->getSelect()->reset()
00274 ->from(
00275 array('_table_views' => $this->getTable('reports/event')),
00276 array('views' => 'COUNT(_table_views.event_id)'))
00277 ->join(array('e' => $this->getProductEntityTableName()),
00278 "e.entity_id = _table_views.object_id AND e.entity_type_id = {$this->getProductEntityTypeId()}")
00279 ->where('_table_views.event_type_id = ?', $productViewEvent)
00280 ->group('e.entity_id')
00281 ->order('views desc')
00282 ->having('views > 0');
00283
00284 if ($from != '' && $to != '') {
00285 $this->getSelect()
00286 ->where('logged_at >= ?', $from)
00287 ->where('logged_at <= ?', $to);
00288 }
00289
00290 return $this;
00291 }
00292 }