/** * Add selected data * * @return \Magento\Sales\Model\Resource\Report\Refunded\Collection\Order */ protected function _initSelect() { $this->getSelect()->from($this->getResource()->getMainTable(), $this->_getSelectedColumns()); if (!$this->isTotals()) { $this->getSelect()->group($this->_periodFormat); } return parent::_initSelect(); }
/** * @return $this */ protected function _beforeLoad() { $this->getSelect()->columns($this->_getSelectedColumns()); if (!$this->isTotals()) { $this->getSelect()->group($this->_periodFormat); $this->getSelect()->having('SUM(orders_count) > 0'); } return parent::_beforeLoad(); }
/** * Add selected data * * @return $this */ protected function _initSelect() { $this->getSelect()->from($this->getResource()->getMainTable(), $this->_getSelectedColumns()); if (!$this->isTotals() && !$this->isSubTotals()) { $this->getSelect()->group([$this->_periodFormat, 'shipping_description']); } if ($this->isSubTotals()) { $this->getSelect()->group([$this->_periodFormat]); } return parent::_initSelect(); }
/** * Apply custom columns before load * * @return $this */ protected function _beforeLoad() { $this->getSelect()->from($this->getResource()->getMainTable(), $this->_getSelectedColumns()); if (!$this->isTotals() && !$this->isSubTotals()) { $this->getSelect()->group([$this->_periodFormat, 'code', 'percent']); } if ($this->isSubTotals()) { $this->getSelect()->group([$this->_periodFormat]); } return parent::_beforeLoad(); }
/** * Apply collection custom filter * * @return \Magento\Sales\Model\Resource\Report\Collection\AbstractCollection */ protected function _applyCustomFilter() { $this->_applyRulesFilter(); return parent::_applyCustomFilter(); }
/** * Redeclare parent method for applying filters after parent method * but before adding unions and calculating totals * * @return $this|\Magento\Framework\Model\Resource\Db\Collection\AbstractCollection * @SuppressWarnings(PHPMD.CyclomaticComplexity) * @SuppressWarnings(PHPMD.NPathComplexity) * @SuppressWarnings(PHPMD.ExcessiveMethodLength) */ protected function _beforeLoad() { parent::_beforeLoad(); $this->_applyStoresFilter(); if ($this->_period) { $selectUnions = []; // apply date boundaries (before calling $this->_applyDateRangeFilter()) $periodFrom = !is_null($this->_from) ? new \DateTime($this->_from) : null; $periodTo = !is_null($this->_to) ? new \DateTime($this->_to) : null; if ('year' == $this->_period) { if ($periodFrom) { // not the first day of the year if ($periodFrom->format('m') != 1 || $periodFrom->format('d') != 1) { $dtFrom = clone $periodFrom; // last day of the year $dtTo = clone $periodFrom; $dtTo->setDate($dtTo->format('Y'), 12, 31); if (!$periodTo || $dtTo < $periodTo) { $selectUnions[] = $this->_makeBoundarySelect($dtFrom->format('Y-m-d'), $dtTo->format('Y-m-d')); // first day of the next year $this->_from = clone $periodFrom; $this->_from->modify('+1 year'); $this->_from->setDate($this->_from->format('Y'), 1, 1); $this->_from = $this->_from->format('Y-m-d'); } } } if ($periodTo) { // not the last day of the year if ($periodTo->format('m') != 12 || $periodTo->format('d') != 31) { $dtFrom = clone $periodTo; $dtFrom->setDate($dtFrom->format('Y'), 1, 1); // first day of the year $dtTo = clone $periodTo; if (!$periodFrom || $dtFrom > $periodFrom) { $selectUnions[] = $this->_makeBoundarySelect($dtFrom->format('Y-m-d'), $dtTo->format('Y-m-d')); // last day of the previous year $this->_to = clone $periodTo; $this->_to->modify('-1 year'); $this->_to->setDate($this->_to->format('Y'), 12, 31); $this->_to = $this->_to->format('Y-m-d'); } } } if ($periodFrom && $periodTo) { // the same year if ($periodTo->format('Y') == $periodFrom->format('Y')) { $dtFrom = clone $periodFrom; $dtTo = clone $periodTo; $selectUnions[] = $this->_makeBoundarySelect($dtFrom->format('Y-m-d'), $dtTo->format('Y-m-d')); $this->getSelect()->where('1<>1'); } } } elseif ('month' == $this->_period) { if ($periodFrom) { // not the first day of the month if ($periodFrom->format('d') != 1) { $dtFrom = clone $periodFrom; // last day of the month $dtTo = clone $periodFrom; $dtTo->modify('+1 month'); $dtTo->setDate($dtTo->format('Y'), $dtTo->format('m'), 1); $dtTo->modify('-1 day'); if (!$periodTo || $dtTo < $periodTo) { $selectUnions[] = $this->_makeBoundarySelect($dtFrom->format('Y-m-d'), $dtTo->format('Y-m-d')); // first day of the next month $this->_from = clone $periodFrom; $this->_from->modify('+1 month'); $this->_from->setDate($this->_from->format('Y'), $this->_from->format('m'), 1); $this->_from = $this->_from->format('Y-m-d'); } } } if ($periodTo) { // not the last day of the month if ($periodTo->format('d') != $periodTo->format('t')) { $dtFrom = clone $periodTo; $dtFrom->setDate($dtFrom->format('Y'), $dtFrom->format('m'), 1); // first day of the month $dtTo = clone $periodTo; if (!$periodFrom || $dtFrom > $periodFrom) { $selectUnions[] = $this->_makeBoundarySelect($dtFrom->format('Y-m-d'), $dtTo->format('Y-m-d')); // last day of the previous month $this->_to = clone $periodTo; $this->_to->setDate($this->_to->format('Y'), $this->_to->format('m'), 1); $this->_to->modify('-1 day'); $this->_to = $this->_to->format('Y-m-d'); } } } if ($periodFrom && $periodTo) { // the same month if ($periodTo->format('Y') == $periodFrom->format('Y') && $periodTo->format('m') == $periodFrom->format('m')) { $dtFrom = clone $periodFrom; $dtTo = clone $periodTo; $selectUnions[] = $this->_makeBoundarySelect($dtFrom->format('Y-m-d'), $dtTo->format('Y-m-d')); $this->getSelect()->where('1<>1'); } } } $this->_applyDateRangeFilter(); // add unions to select if ($selectUnions) { $unionParts = []; $cloneSelect = clone $this->getSelect(); $unionParts[] = '(' . $cloneSelect . ')'; foreach ($selectUnions as $union) { $unionParts[] = '(' . $union . ')'; } $this->getSelect()->reset()->union($unionParts, \Zend_Db_Select::SQL_UNION_ALL); } if ($this->isTotals()) { // calculate total $cloneSelect = clone $this->getSelect(); $this->getSelect()->reset()->from($cloneSelect, $this->getAggregatedColumns()); } else { // add sorting $this->getSelect()->order(['period ASC', $this->getOrderedField() . ' DESC']); } } return $this; }
/** * Redeclare parent method for applying filters after parent method * but before adding unions and calculating totals * * @return $this */ protected function _beforeLoad() { parent::_beforeLoad(); $this->_applyStoresFilter(); if ($this->_period) { // $selectUnions = array(); // apply date boundaries (before calling $this->_applyDateRangeFilter()) $dtFormat = \Magento\Framework\Stdlib\DateTime::DATE_INTERNAL_FORMAT; $periodFrom = !is_null($this->_from) ? new \Magento\Framework\Stdlib\DateTime\Date($this->_from, $dtFormat) : null; $periodTo = !is_null($this->_to) ? new \Magento\Framework\Stdlib\DateTime\Date($this->_to, $dtFormat) : null; if ('year' == $this->_period) { if ($periodFrom) { // not the first day of the year if ($periodFrom->toValue(\Zend_Date::MONTH) != 1 || $periodFrom->toValue(\Zend_Date::DAY) != 1) { $dtFrom = $periodFrom->getDate(); // last day of the year $dtTo = $periodFrom->getDate()->setMonth(12)->setDay(31); if (!$periodTo || $dtTo->isEarlier($periodTo)) { $selectUnions[] = $this->_makeBoundarySelect($dtFrom->toString($dtFormat), $dtTo->toString($dtFormat)); // first day of the next year $this->_from = $periodFrom->getDate()->addYear(1)->setMonth(1)->setDay(1)->toString($dtFormat); } } } if ($periodTo) { // not the last day of the year if ($periodTo->toValue(\Zend_Date::MONTH) != 12 || $periodTo->toValue(\Zend_Date::DAY) != 31) { $dtFrom = $periodTo->getDate()->setMonth(1)->setDay(1); // first day of the year $dtTo = $periodTo->getDate(); if (!$periodFrom || $dtFrom->isLater($periodFrom)) { $selectUnions[] = $this->_makeBoundarySelect($dtFrom->toString($dtFormat), $dtTo->toString($dtFormat)); // last day of the previous year $this->_to = $periodTo->getDate()->subYear(1)->setMonth(12)->setDay(31)->toString($dtFormat); } } } if ($periodFrom && $periodTo) { // the same year if ($periodFrom->toValue(\Zend_Date::YEAR) == $periodTo->toValue(\Zend_Date::YEAR)) { $dtFrom = $periodFrom->getDate(); $dtTo = $periodTo->getDate(); $selectUnions[] = $this->_makeBoundarySelect($dtFrom->toString($dtFormat), $dtTo->toString($dtFormat)); $this->getSelect()->where('1<>1'); } } } elseif ('month' == $this->_period) { if ($periodFrom) { // not the first day of the month if ($periodFrom->toValue(\Zend_Date::DAY) != 1) { $dtFrom = $periodFrom->getDate(); // last day of the month $dtTo = $periodFrom->getDate()->addMonth(1)->setDay(1)->subDay(1); if (!$periodTo || $dtTo->isEarlier($periodTo)) { $selectUnions[] = $this->_makeBoundarySelect($dtFrom->toString($dtFormat), $dtTo->toString($dtFormat)); // first day of the next month $this->_from = $periodFrom->getDate()->addMonth(1)->setDay(1)->toString($dtFormat); } } } if ($periodTo) { // not the last day of the month if ($periodTo->toValue(\Zend_Date::DAY) != $periodTo->toValue(\Zend_Date::MONTH_DAYS)) { $dtFrom = $periodTo->getDate()->setDay(1); // first day of the month $dtTo = $periodTo->getDate(); if (!$periodFrom || $dtFrom->isLater($periodFrom)) { $selectUnions[] = $this->_makeBoundarySelect($dtFrom->toString($dtFormat), $dtTo->toString($dtFormat)); // last day of the previous month $this->_to = $periodTo->getDate()->setDay(1)->subDay(1)->toString($dtFormat); } } } if ($periodFrom && $periodTo) { // the same month if ($periodFrom->toValue(\Zend_Date::YEAR) == $periodTo->toValue(\Zend_Date::YEAR) && $periodFrom->toValue(\Zend_Date::MONTH) == $periodTo->toValue(\Zend_Date::MONTH)) { $dtFrom = $periodFrom->getDate(); $dtTo = $periodTo->getDate(); $selectUnions[] = $this->_makeBoundarySelect($dtFrom->toString($dtFormat), $dtTo->toString($dtFormat)); $this->getSelect()->where('1<>1'); } } } $this->_applyDateRangeFilter(); // add unions to select if ($selectUnions) { $unionParts = array(); $cloneSelect = clone $this->getSelect(); $unionParts[] = '(' . $cloneSelect . ')'; foreach ($selectUnions as $union) { $unionParts[] = '(' . $union . ')'; } $this->getSelect()->reset()->union($unionParts, \Zend_Db_Select::SQL_UNION_ALL); } if ($this->isTotals()) { // calculate total $cloneSelect = clone $this->getSelect(); $this->getSelect()->reset()->from($cloneSelect, $this->getAggregatedColumns()); } else { // add sorting $this->getSelect()->order(array('period ASC', 'qty_ordered DESC')); } } return $this; }