protected function compile() { $periodFactory = new PeriodFactory(); $arrSession = \Session::getInstance()->get('iso_reports'); $strPeriod = (string) $arrSession[$this->name]['period']; $intColumns = (int) $arrSession[$this->name]['columns']; $blnVariants = (bool) $arrSession[$this->name]['variants']; $intStatus = (int) $arrSession[$this->name]['iso_status']; if ($arrSession[$this->name]['from'] == '') { $intStart = strtotime('-' . ($intColumns - 1) . ' ' . $strPeriod); } else { $intStart = (int) $arrSession[$this->name]['from']; } $period = $periodFactory->create($strPeriod); $intStart = $period->getPeriodStart($intStart); $dateFrom = $period->getKey($intStart); $dateTo = $period->getKey(strtotime('+ ' . ($intColumns - 1) . ' ' . $strPeriod, $intStart)); $arrData = array('rows' => array()); $arrData['header'] = $this->getHeader($period, $intStart, $intColumns); $groupVariants = $blnVariants ? 'p1.id' : 'IF(p1.pid=0, p1.id, p1.pid)'; $objProducts = \Database::getInstance()->query("\n SELECT\n IFNULL({$groupVariants}, i.product_id) AS product_id,\n IFNULL(p1.name, i.name) AS variant_name,\n IFNULL(p2.name, i.name) AS product_name,\n p1.sku AS product_sku,\n p2.sku AS variant_sku,\n IF(p1.pid=0, p1.type, p2.type) AS type,\n i.configuration AS product_configuration,\n SUM(i.quantity) AS quantity,\n SUM(i.tax_free_price * i.quantity) AS total,\n " . $period->getSqlField($this->strDateField) . " AS dateGroup\n FROM " . ProductCollectionItem::getTable() . " i\n LEFT JOIN " . ProductCollection::getTable() . " o ON i.pid=o.id\n LEFT JOIN " . OrderStatus::getTable() . " os ON os.id=o.order_status\n LEFT OUTER JOIN " . Product::getTable() . " p1 ON i.product_id=p1.id\n LEFT OUTER JOIN " . Product::getTable() . " p2 ON p1.pid=p2.id\n WHERE o.type='order' AND o.order_status>0 AND o.locked!=''\n " . ($intStatus > 0 ? " AND o.order_status=" . $intStatus : '') . "\n " . $this->getProductProcedure('p1') . "\n " . $this->getConfigProcedure('o', 'config_id') . "\n GROUP BY dateGroup, product_id\n HAVING dateGroup>={$dateFrom} AND dateGroup<={$dateTo}\n "); // Cache product types so call to findByPk() will trigger the registry ProductType::findMultipleByIds($objProducts->fetchEach('type')); $arrRaw = array(); $objProducts->reset(); // Prepare product data while ($objProducts->next()) { $arrAttributes = array(); $arrVariantAttributes = array(); $blnHasVariants = false; // Can't use it without a type if ($objProducts->type > 0 && ($objType = ProductType::findByPk($objProducts->type)) !== null) { /** @type ProductType $objType */ $arrAttributes = $objType->getAttributes(); $arrVariantAttributes = $objType->getVariantAttributes(); $blnHasVariants = $objType->hasVariants(); $product_type_name = $objType->name; } $arrOptions = array('name' => $objProducts->variant_name); // Use product title if name is not a variant attribute if ($blnHasVariants && !in_array('name', $arrVariantAttributes)) { $arrOptions['name'] = $objProducts->product_name; } $strSku = $blnHasVariants ? $objProducts->variant_sku : $objProducts->product_sku; if (in_array('sku', $arrAttributes) && $strSku != '') { $arrOptions['name'] = sprintf('%s <span style="color:#b3b3b3; padding-left:3px;">[%s]</span>', $arrOptions['name'], $strSku); } if ($blnVariants && $blnHasVariants) { if (in_array('sku', $arrVariantAttributes) && $objProducts->product_sku != '') { $arrOptions['name'] = sprintf('%s <span style="color:#b3b3b3; padding-left:3px;">[%s]</span>', $arrOptions['name'], $objProducts->product_sku); } foreach (deserialize($objProducts->product_configuration, true) as $strName => $strValue) { if (isset($GLOBALS['TL_DCA']['tl_iso_product']['fields'][$strName])) { $strValue = $GLOBALS['TL_DCA']['tl_iso_product']['fields'][$strName]['options'][$strValue] ? $GLOBALS['TL_DCA']['tl_iso_product']['fields'][$strName]['options'][$strValue] : $strValue; $strName = $GLOBALS['TL_DCA']['tl_iso_product']['fields'][$strName]['label'][0] ? $GLOBALS['TL_DCA']['tl_iso_product']['fields'][$strName]['label'][0] : $strName; } $arrOptions[] = '<span class="variant">' . $strName . ': ' . $strValue . '</span>'; } } $arrOptions['name'] = '<span class="product">' . $arrOptions['name'] . '</span>'; $arrRaw[$objProducts->product_id]['name'] = implode('<br>', $arrOptions); $arrRaw[$objProducts->product_id]['product_type_name'] = $product_type_name; $arrRaw[$objProducts->product_id][$objProducts->dateGroup] = (double) $arrRaw[$objProducts->product_id][$objProducts->dateGroup] + (double) $objProducts->total; $arrRaw[$objProducts->product_id][$objProducts->dateGroup . '_quantity'] = (int) $arrRaw[$objProducts->product_id][$objProducts->dateGroup . '_quantity'] + (int) $objProducts->quantity; $arrRaw[$objProducts->product_id]['total'] = (double) $arrRaw[$objProducts->product_id]['total'] + (double) $objProducts->total; $arrRaw[$objProducts->product_id]['quantity'] = (int) $arrRaw[$objProducts->product_id]['quantity'] + (int) $objProducts->quantity; } // Prepare columns $arrColumns = array(); for ($i = 0; $i < $intColumns; $i++) { $arrColumns[] = $period->getKey($intStart); $intStart = $period->getNext($intStart); } $arrFooter = array(); // Sort the data if ($arrSession[$this->name]['tl_sort'] == 'product_name') { usort($arrRaw, function ($a, $b) { return strcasecmp($a['name'], $b['name']); }); } else { usort($arrRaw, function ($a, $b) { return $a['total'] == $b['total'] ? 0 : ($a['total'] < $b['total'] ? 1 : -1); }); } // Generate data foreach ($arrRaw as $arrProduct) { $arrRow = array(array('value' => array($arrProduct['name'], sprintf('<span style="color:#b3b3b3;">[%s]</span>', $arrProduct['product_type_name'])))); $arrFooter[0] = array('value' => $GLOBALS['TL_LANG']['ISO_REPORT']['sums']); foreach ($arrColumns as $i => $column) { $arrRow[$i + 1] = array('value' => Isotope::formatPriceWithCurrency($arrProduct[$column]) . ($arrProduct[$column . '_quantity'] !== null ? '<br><span class="variant">' . Isotope::formatItemsString($arrProduct[$column . '_quantity']) . '</span>' : '')); $arrFooter[$i + 1] = array('total' => $arrFooter[$i + 1]['total'] + $arrProduct[$column], 'quantity' => $arrFooter[$i + 1]['quantity'] + $arrProduct[$column . '_quantity']); } $arrRow[$i + 2] = array('value' => Isotope::formatPriceWithCurrency($arrProduct['total']) . ($arrProduct['quantity'] !== null ? '<br><span class="variant">' . Isotope::formatItemsString($arrProduct['quantity']) . '</span>' : '')); $arrFooter[$i + 2] = array('total' => $arrFooter[$i + 2]['total'] + $arrProduct['total'], 'quantity' => $arrFooter[$i + 2]['quantity'] + $arrProduct['quantity']); $arrData['rows'][] = array('columns' => $arrRow); } for ($i = 1; $i < count($arrFooter); $i++) { $arrFooter[$i]['value'] = Isotope::formatPriceWithCurrency($arrFooter[$i]['total']) . '<br><span class="variant">' . Isotope::formatItemsString($arrFooter[$i]['quantity']) . '</span>'; unset($arrFooter[$i]['total']); } $arrData['footer'] = $arrFooter; $this->Template->data = $arrData; }