/**
* Set up the payment array
* @author Reto Kohli <*****@*****.**>
* @since 2.1.0
*/
static function init()
{
global $objDatabase;
$arrSqlName = \Text::getSqlSnippets('`payment`.`id`', FRONTEND_LANG_ID, 'Shop', array('name' => self::TEXT_NAME));
$query = "\n SELECT `payment`.`id`, `payment`.`processor_id`,\n `payment`.`fee`, `payment`.`free_from`,\n `payment`.`ord`, `payment`.`active`, " . $arrSqlName['field'] . "\n FROM `" . DBPREFIX . "module_shop" . MODULE_INDEX . "_payment` AS `payment`" . $arrSqlName['join'] . "\n ORDER BY id";
$objResult = $objDatabase->Execute($query);
if (!$objResult) {
return self::errorHandler();
}
self::$arrPayments = array();
if ($objResult->EOF) {
return true;
}
while ($objResult && !$objResult->EOF) {
$id = $objResult->fields['id'];
$strName = $objResult->fields['name'];
if ($strName === null) {
$objText = \Text::getById($id, 'Shop', self::TEXT_NAME);
if ($objText) {
$strName = $objText->content();
}
}
self::$arrPayments[$id] = array('id' => $id, 'processor_id' => $objResult->fields['processor_id'], 'name' => $strName, 'fee' => $objResult->fields['fee'], 'free_from' => $objResult->fields['free_from'], 'ord' => $objResult->fields['ord'], 'active' => $objResult->fields['active']);
$objResult->MoveNext();
}
return true;
}
/**
* Initialise the Manufacturer array
*
* Uses the FRONTEND_LANG_ID constant to determine the language.
* The array has the form
* array(
* 'id' => Manufacturer ID,
* 'name' => Manufacturer name,
* 'url' => Manufacturer URI,
* )
* @static
* @param string $order The optional sorting order.
* Defaults to null (unsorted)
* @return boolean True on success, false otherwise
* @global ADONewConnection $objDatabase
* @global array $_ARRAYLANG
* @todo Order the Manufacturers by their name
*/
static function init($order = null)
{
global $objDatabase;
$arrSql = \Text::getSqlSnippets('`manufacturer`.`id`', FRONTEND_LANG_ID, 'Shop', array('name' => self::TEXT_NAME, 'url' => self::TEXT_URI));
$query = "\n SELECT `manufacturer`.`id`, " . $arrSql['field'] . "\n FROM `" . DBPREFIX . "module_shop" . MODULE_INDEX . "_manufacturer` AS `manufacturer`" . $arrSql['join'] . ($order ? " ORDER BY {$order}" : '');
$objResult = $objDatabase->Execute($query);
if (!$objResult) {
return self::errorHandler();
}
self::$arrManufacturer = array();
while (!$objResult->EOF) {
$id = $objResult->fields['id'];
$strName = $objResult->fields['name'];
// Replace Text in a missing language by another, if available
if ($strName === null) {
$strName = \Text::getById($id, 'Shop', self::TEXT_NAME)->content();
}
$strUrl = $objResult->fields['url'];
if ($strUrl === null) {
$strUrl = \Text::getById($id, 'Shop', self::TEXT_URI)->content();
}
self::$arrManufacturer[$id] = array('id' => $id, 'name' => $strName, 'url' => $strUrl);
$objResult->MoveNext();
}
return true;
}
/**
* Initialize shippers and shipment conditions
*
* Use $all=true for the backend settings.
* Reads the shipping options from the shipper (s) and shipment_cost (c)
* tables. For each shipper, creates array entries like:
* arrShippers[s.id] = array (
* name => s.name,
* status => s.status
* )
* arrShipments[s.id][c.id] = array (
* max_weight => c.max_weight,
* free_from => c.free_from,
* fee => c.fee
* )
* Note that the table module_shop_shipment has been replaced by
* module_shop_shipper (id, name, status) and
* module_shop_shipment_cost (id, shipper_id, max_weight, fee, free_from)
* as of version 1.1.
* @global ADONewConnection
* @param boolean $all If true, includes inactive records.
* Defaults to false.
* @return void
* @since 1.1
*/
static function init($all = false)
{
global $objDatabase;
//DBG::log("Shipment::init(): language ID ".FRONTEND_LANG_ID);
$arrSqlName = \Text::getSqlSnippets('`shipper`.`id`', FRONTEND_LANG_ID, 'Shop', array('name' => self::TEXT_NAME));
$objResult = $objDatabase->Execute("\n SELECT `shipper`.`id`, `shipper`.`active`, " . $arrSqlName['field'] . "\n FROM `" . DBPREFIX . "module_shop" . MODULE_INDEX . "_shipper` AS `shipper`" . $arrSqlName['join'] . ($all ? '' : ' WHERE `shipper`.`active`=1') . "\n ORDER BY `shipper`.`id` ASC");
if (!$objResult) {
return self::errorHandler();
}
while (!$objResult->EOF) {
$shipper_id = $objResult->fields['id'];
$strName = $objResult->fields['name'];
// Replace Text in a missing language by another, if available
if ($strName === null) {
$objText = \Text::getById($shipper_id, 'Shop', self::TEXT_NAME);
if ($objText) {
$strName = $objText->content();
}
}
self::$arrShippers[$shipper_id] = array('id' => $objResult->fields['id'], 'name' => $strName, 'active' => $objResult->fields['active']);
$objResult->MoveNext();
}
// Now get the associated shipment conditions from shipment_cost
$objResult = $objDatabase->Execute("\n SELECT `shipment`.`id`, `shipment`.`shipper_id`,\n `shipment`.`max_weight`, `shipment`.`fee`, `shipment`.`free_from`\n FROM `" . DBPREFIX . "module_shop" . MODULE_INDEX . "_shipment_cost` AS `shipment`\n INNER JOIN `" . DBPREFIX . "module_shop" . MODULE_INDEX . "_shipper` AS `shipper`\n ON `shipper`.`id`=`shipper_id`");
if (!$objResult) {
return self::errorHandler();
}
while (!$objResult->EOF) {
self::$arrShipments[$objResult->fields['shipper_id']][$objResult->fields['id']] = array('max_weight' => Weight::getWeightString($objResult->fields['max_weight']), 'free_from' => $objResult->fields['free_from'], 'fee' => $objResult->fields['fee']);
$objResult->MoveNext();
}
return true;
}
/**
* Initialize currencies
*
* Sets up the Currency array, and picks the selected Currency from the
* 'currency' request parameter, if available.
* @author Reto Kohli <*****@*****.**>
* @static
*/
static function init($active_currency_id = 0)
{
global $objDatabase;
$arrSqlName = \Text::getSqlSnippets('`currency`.`id`', FRONTEND_LANG_ID, 'Shop', array('name' => self::TEXT_NAME));
$query = "\n SELECT `currency`.`id`, `currency`.`code`, `currency`.`symbol`,\n `currency`.`rate`, `currency`.`increment`,\n `currency`.`ord`,\n `currency`.`active`, `currency`.`default`, " . $arrSqlName['field'] . "\n FROM `" . DBPREFIX . "module_shop" . MODULE_INDEX . "_currencies` AS `currency`" . $arrSqlName['join'] . "\n ORDER BY `currency`.`id` ASC";
$objResult = $objDatabase->Execute($query);
if (!$objResult) {
return self::errorHandler();
}
while (!$objResult->EOF) {
$id = $objResult->fields['id'];
$strName = $objResult->fields['name'];
if ($strName === null) {
$strName = \Text::getById($id, 'Shop', self::TEXT_NAME)->content();
}
self::$arrCurrency[$objResult->fields['id']] = array('id' => $objResult->fields['id'], 'code' => $objResult->fields['code'], 'symbol' => $objResult->fields['symbol'], 'name' => $strName, 'rate' => $objResult->fields['rate'], 'increment' => $objResult->fields['increment'], 'ord' => $objResult->fields['ord'], 'active' => $objResult->fields['active'], 'default' => $objResult->fields['default']);
if ($objResult->fields['default']) {
self::$defaultCurrencyId = $objResult->fields['id'];
}
$objResult->MoveNext();
}
if (!isset($_SESSION['shop'])) {
$_SESSION['shop'] = array();
}
if (isset($_REQUEST['currency'])) {
$currency_id = intval($_REQUEST['currency']);
$_SESSION['shop']['currencyId'] = isset(self::$arrCurrency[$currency_id]) ? $currency_id : self::$defaultCurrencyId;
}
if (!empty($active_currency_id)) {
$_SESSION['shop']['currencyId'] = isset(self::$arrCurrency[$active_currency_id]) ? $active_currency_id : self::$defaultCurrencyId;
}
if (empty($_SESSION['shop']['currencyId'])) {
$_SESSION['shop']['currencyId'] = self::$defaultCurrencyId;
}
self::$activeCurrencyId = intval($_SESSION['shop']['currencyId']);
return true;
}
/**
* Initialises all Zones (but no relation)
* @return boolean True on success, false otherwise
* @static
*/
static function init()
{
global $objDatabase;
$arrSqlName = \Text::getSqlSnippets('`zone`.`id`', FRONTEND_LANG_ID, 'Shop', array('name' => self::TEXT_NAME));
$query = "\n SELECT `zone`.`id`, `zone`.`active`, " . $arrSqlName['field'] . "\n FROM `" . DBPREFIX . "module_shop" . MODULE_INDEX . "_zones` AS `zone`" . $arrSqlName['join'] . "\n ORDER BY `name` ASC";
$objResult = $objDatabase->Execute($query);
if (!$objResult) {
return self::errorHandler();
}
self::$arrZone = array();
while (!$objResult->EOF) {
$id = $objResult->fields['id'];
$strName = $objResult->fields['name'];
if ($strName === null) {
$objText = \Text::getById($id, 'Shop', self::TEXT_NAME);
if ($objText) {
$strName = $objText->content();
}
}
self::$arrZone[$id] = array('id' => $id, 'name' => $strName, 'active' => $objResult->fields['active']);
$objResult->MoveNext();
}
return true;
}
/**
* Get an array with image type data.
*
* The $key argument may be empty (defaults to false), a single string,
* or an array of strings. Only matching keys are returned in the array.
* The array returned looks like
* array(
* key => array(
* 'width' => width,
* 'height' => height,
* 'quality' => quality,
* 'width_thumb' => thumbnail width,
* 'height_thumb' => thumbnail height,
* 'quality_thumb' => thumbnail quality,
* 'text_id' => Image type Text ID,
* 'name' => Image type name,
* ),
* ... more ...
* )
* The array elements are ordered by key, ascending.
* Uses the MODULE_ID constant.
* @static
* @param string $key The optional type key or key array
* @return array The type data array on success,
* false otherwise
* @global mixed $objDatabase Database object
* @author Reto Kohli <*****@*****.**>
*/
static function getArray($key = false)
{
global $objDatabase;
//echo("ImageType::getArray($key): Entered<br />");
if (self::$last_key !== '' && self::$last_key !== $key) {
self::reset();
}
if (!is_array(self::$arrImagetypes)) {
//echo("ImageType::getArray($key): Entered<br />");
$arrSqlName = \Text::getSqlSnippets('`imagetype`.`text_id`', FRONTEND_LANG_ID, MODULE_ID, self::TEXT_IMAGETYPE);
$query = "\n SELECT `imagetype`.`key`,\n `imagetype`.`width`,\n `imagetype`.`height`,\n `imagetype`.`quality`,\n `imagetype`.`width_thumb`,\n `imagetype`.`height_thumb`,\n `imagetype`.`quality_thumb`\n " . $arrSqlName['field'] . "\n FROM `" . DBPREFIX . "core_imagetype` AS `imagetype`" . $arrSqlName['join'] . "\n WHERE 1" . (MODULE_ID ? ' AND `imagetype`.`module_id`=' . MODULE_ID : '') . ($key ? ' AND `imagetype`.`key`' . (is_array($key) ? " IN ('" . join("','", array_map('addslashes', $key)) . "')" : "='" . addslashes($key) . "'") : '') . "\n ORDER BY `imagetype`.`key` ASC";
//echo("ImageType::getArray($key): query $query<br />");
$objResult = $objDatabase->Execute($query);
//echo("ImageType::getArray($key): query ran, result ".var_export($objResult, true)."<br />");
if (!$objResult) {
return self::errorHandler();
}
//die("ImageType::getArray($key): No error<br />");
self::$arrImagetypes = array();
while (!$objResult->EOF) {
$strName = $objResult->fields[$arrSqlName['text']];
if ($strName === null) {
//echo("No text<br />");
$strName = '';
}
$key = $objResult->fields['key'];
self::$arrImagetypes[$key] = array('width' => $objResult->fields['width'], 'height' => $objResult->fields['height'], 'quality' => $objResult->fields['quality'], 'width_thumb' => $objResult->fields['width_thumb'], 'height_thumb' => $objResult->fields['height_thumb'], 'quality_thumb' => $objResult->fields['quality_thumb'], 'text_id' => $objResult->fields[$arrSqlName['id']], 'name' => $strName);
$objResult->MoveNext();
}
self::$last_key = $key;
//die("ImageType::getArray($key): got ".var_export(self::$arrImagetypes, true)."<hr />");
}
//echo("ImageType::getArray(): Array ".var_export(self::$arrImagetypes, true)."<br />");
return self::$arrImagetypes;
}
/**
* Select a Product by ID from the database.
* @static
* @param integer $id The Product ID
* @return Product The Product object on success,
* false otherwise
* @global ADONewConnection
* @author Reto Kohli <*****@*****.**>
*/
static function getById($id)
{
global $objDatabase;
if (!$id) {
return NULL;
}
$arrSql = \Text::getSqlSnippets('`product`.`id`', FRONTEND_LANG_ID, 'Shop', array('name' => self::TEXT_NAME, 'short' => self::TEXT_SHORT, 'long' => self::TEXT_LONG, 'keys' => self::TEXT_KEYS, 'code' => self::TEXT_CODE, 'uri' => self::TEXT_URI));
$query = "\n SELECT `product`.`id`, `product`.`category_id`,\n `product`.`ord`, `product`.`active`, `product`.`weight`,\n `product`.`picture`,\n `product`.`normalprice`, `product`.`resellerprice`,\n `product`.`discountprice`, `product`.`discount_active`,\n `product`.`stock`, `product`.`stock_visible`,\n `product`.`distribution`,\n `product`.`date_start`, `product`.`date_end`,\n `product`.`manufacturer_id`,\n `product`.`b2b`, `product`.`b2c`,\n `product`.`vat_id`,\n `product`.`flags`,\n `product`.`usergroup_ids`,\n `product`.`group_id`, `product`.`article_id`,\n `product`.`minimum_order_quantity`, " . $arrSql['field'] . "\n FROM `" . DBPREFIX . "module_shop" . MODULE_INDEX . "_products` AS `product`" . $arrSql['join'] . "\n WHERE `product`.`id`={$id}";
$objResult = $objDatabase->Execute($query);
if (!$objResult) {
return self::errorHandler();
}
if ($objResult->RecordCount() != 1) {
return false;
}
$id = $objResult->fields['id'];
$strCode = $objResult->fields['code'];
if ($strCode === null) {
$strCode = \Text::getById($id, 'Shop', self::TEXT_CODE)->content();
}
$strName = $objResult->fields['name'];
if ($strName === null) {
$strName = \Text::getById($id, 'Shop', self::TEXT_NAME)->content();
}
$strShort = $objResult->fields['short'];
if ($strShort === null) {
$strShort = \Text::getById($id, 'Shop', self::TEXT_SHORT)->content();
}
$strLong = $objResult->fields['long'];
if ($strLong === null) {
$strLong = \Text::getById($id, 'Shop', self::TEXT_LONG)->content();
}
$strUri = $objResult->fields['uri'];
if ($strUri === null) {
$strUri = \Text::getById($id, 'Shop', self::TEXT_URI)->content();
}
$strKeys = $objResult->fields['keys'];
if ($strKeys === null) {
$strKeys = \Text::getById($id, 'Shop', self::TEXT_KEYS)->content();
}
$objProduct = new Product($strCode, $objResult->fields['category_id'], $strName, $objResult->fields['distribution'], $objResult->fields['normalprice'], $objResult->fields['active'], $objResult->fields['ord'], $objResult->fields['weight'], $objResult->fields['id']);
$objProduct->pictures = $objResult->fields['picture'];
$objProduct->resellerprice = floatval($objResult->fields['resellerprice']);
$objProduct->short = $strShort;
$objProduct->long = $strLong;
$objProduct->stock($objResult->fields['stock']);
$objProduct->stock_visible($objResult->fields['stock_visible']);
$objProduct->discountprice = floatval($objResult->fields['discountprice']);
$objProduct->discount_active($objResult->fields['discount_active']);
$objProduct->b2b($objResult->fields['b2b']);
$objProduct->b2c($objResult->fields['b2c']);
$objProduct->date_start($objResult->fields['date_start']);
$objProduct->date_end($objResult->fields['date_end']);
$objProduct->manufacturer_id = $objResult->fields['manufacturer_id'];
$objProduct->uri = $strUri;
$objProduct->vat_id = $objResult->fields['vat_id'];
$objProduct->flags = $objResult->fields['flags'];
$objProduct->usergroup_ids = $objResult->fields['usergroup_ids'];
$objProduct->group_id = $objResult->fields['group_id'];
$objProduct->article_id = $objResult->fields['article_id'];
$objProduct->keywords = $strKeys;
$objProduct->minimum_order_quantity = $objResult->fields['minimum_order_quantity'];
// Fetch the Product Attribute relations
$objProduct->arrRelations = Attributes::getRelationArray($objProduct->id);
//die("dfhreh: ".$objProduct->category_id());
return $objProduct;
}
/**
* Returns an array with the IDs and names of all ShopCategories marked
* as virtual.
*
* The array structure is
* array(
* ID => Category name,
* ... more ...
* )
* Note that the array elements are ordered according to the
* ordinal value.
* @return array The array of virtual ShopCategory IDs/names
* @static
* @author Reto Kohli <*****@*****.**>
*/
static function getVirtualCategoryIdNameArray()
{
global $objDatabase;
$arrSqlName = \Text::getSqlSnippets('`categories`.`id`', FRONTEND_LANG_ID, 'Shop', array('name' => ShopCategory::TEXT_NAME));
$query = "\n SELECT `category`.`id`, " . $arrSqlName['field'] . "\n FROM " . DBPREFIX . "module_shop" . MODULE_INDEX . "_categories AS `category`" . $arrSqlName['join'] . "\n WHERE `category`.`flags` LIKE '%__VIRTUAL__%'\n ORDER BY `category`.`ord` ASC";
$objResult = $objDatabase->Execute($query);
if (!$objResult) {
return false;
}
$arrVirtual = array();
while (!$objResult->EOF) {
$arrVirtual[$objResult->fields['id']] = $objResult->fields['name'];
$objResult->MoveNext();
}
return $arrVirtual;
}
/**
* Return the name of the option selected by its ID
* from the database.
*
* Returns false on error, or the empty string if the value cannot be
* found.
* @param integer $option_id The option ID
* @return mixed The option name on success,
* or false otherwise.
* @static
* @global mixed $objDatabase Database object
*/
static function getOptionNameById($option_id)
{
global $objDatabase;
$arrSqlValue = \Text::getSqlSnippets('`option`.`id`', FRONTEND_LANG_ID, 'Shop', array('name' => Attribute::TEXT_OPTION_NAME));
$query = "\n SELECT 1, " . $arrSqlValue['field'] . "\n FROM `" . DBPREFIX . "module_shop" . MODULE_INDEX . "_option` AS `option`" . $arrSqlValue['join'] . "\n WHERE `option`.`id`={$option_id}";
$objResult = $objDatabase->Execute($query);
if (!$objResult || $objResult->EOF) {
return false;
}
$strName = $objResult->fields['name'];
if (is_null($strName)) {
$strName = \Text::getById($option_id, 'Shop', Attribute::TEXT_OPTION_NAME)->content();
}
return $strName;
}
/**
* Return the option ID corresponding to the given value name,
* if found, false otherwise.
*
* If there is more than one value of the same name, only the
* first ID found is returned, with no guarantee that it will
* always return the same.
* This method is awkwardly named because of the equally awkward
* names given to the database fields.
* @param string $value The option name
* @return integer The first matching option ID found,
* or false.
* @global ADONewConnection $objDatabase Database connection object
* @static
*/
static function getValueIdByName($value)
{
global $objDatabase;
$arrSqlValue = \Text::getSqlSnippets('`option`.`id`', FRONTEND_LANG_ID, 'Shop', array('name' => self::TEXT_OPTION_NAME));
$query = "\n SELECT `id`\n FROM `" . DBPREFIX . "module_shop" . MODULE_INDEX . "_option` AS `option`" . $arrSqlValue['join'] . "\n WHERE `name`='" . addslashes($value) . "'";
$objResult = $objDatabase->Execute($query);
if (!$objResult || $objResult->EOF) {
return false;
}
return $objResult->fields['id'];
}
/**
* Returns an array of Product names from the database
*
* The array is indexed by the Product ID and ordered by the names
* and ID, ascending.
* The names array is kept in this method statically between calls.
* @static
* @param boolean $activeonly Optional. Include active (true) or
* inactive (false) Products only.
* Ignored if null. Defaults to null
* @param string $format The optional sprintf() format
* @return array The array of Product names
* on success, false otherwise
* @global ADONewConnection
* @author Reto Kohli <*****@*****.**>
*/
static function getNameArray($activeonly = false, $format = '%2$s')
{
global $objDatabase;
$arrSqlName = \Text::getSqlSnippets('`product`.`id`', FRONTEND_LANG_ID, 'Shop', array('name' => Product::TEXT_NAME));
$query = "\n SELECT `product`.`id`, " . $arrSqlName['field'] . "\n FROM `" . DBPREFIX . "module_shop" . MODULE_INDEX . "_products` AS `product`" . $arrSqlName['join'] . (isset($activeonly) ? ' WHERE `product`.`active`=' . ($activeonly ? 1 : 0) : '') . "\n ORDER BY `name` ASC, `product`.`id` ASC";
$objResult = $objDatabase->Execute($query);
if (!$objResult) {
return Product::errorHandler();
}
$arrName = array();
while (!$objResult->EOF) {
$id = $objResult->fields['id'];
$strName = $objResult->fields['name'];
if ($strName === null) {
$objText = \Text::getById($id, 'Shop', Product::TEXT_NAME);
if ($objText) {
$strName = $objText->content();
}
}
$arrName[$objResult->fields['id']] = sprintf($format, $id, $strName);
$objResult->MoveNext();
}
//\DBG::log("Products::getNameArray(): Made ".var_export($arrName, true));
return $arrName;
}
/**
* Sets up the Order statistics
* @param \Cx\Core\Html\Sigma $objTemplate The optional Template,
* by reference
* @global ADONewConnection $objDatabase
* @global array $_ARRAYLANG
* @todo Rewrite the statistics in a seperate class, extending Order
* @static
*/
static function view_statistics(&$objTemplate = null)
{
global $objDatabase, $_ARRAYLANG;
if (!$objTemplate || !$objTemplate->blockExists('no_order')) {
$objTemplate = new \Cx\Core\Html\Sigma(\Cx\Core\Core\Controller\Cx::instanciate()->getCodeBaseModulePath() . '/Shop/View/Template/Backend');
$objTemplate->loadTemplateFile('module_shop_statistic.html');
}
$objTemplate->setGlobalVariable($_ARRAYLANG);
// Get the first order date; if its empty, no order has been placed yet
$time_first_order = Order::getFirstOrderTime();
if (!$time_first_order) {
$objTemplate->touchBlock('no_order');
return $objTemplate;
}
$year_first_order = date('Y', $time_first_order);
$month_first_order = date('m', $time_first_order);
$start_month = $end_month = $start_year = $end_year = NULL;
if (isset($_REQUEST['submitdate'])) {
// A range is requested
$start_month = intval($_REQUEST['startmonth']);
$end_month = intval($_REQUEST['stopmonth']);
$start_year = intval($_REQUEST['startyear']);
$end_year = intval($_REQUEST['stopyear']);
} else {
// Default range to one year, or back to the first order if less
$start_month = $month_first_order;
$end_month = Date('m');
$start_year = $end_year = Date('Y');
if ($year_first_order < $start_year) {
$start_year -= 1;
if ($year_first_order < $start_year || $month_first_order < $start_month) {
$start_month = $end_month;
}
}
}
$objTemplate->setVariable(array('SHOP_START_MONTH' => Shopmanager::getMonthDropdownMenu($start_month), 'SHOP_END_MONTH' => Shopmanager::getMonthDropdownMenu($end_month), 'SHOP_START_YEAR' => Shopmanager::getYearDropdownMenu($start_year, $year_first_order), 'SHOP_END_YEAR' => Shopmanager::getYearDropdownMenu($end_year, $year_first_order)));
$start_date = date(ASCMS_DATE_FORMAT_INTERNATIONAL_DATETIME, mktime(0, 0, 0, $start_month, 1, $start_year));
// mktime() will fix the month from 13 to 01, see example 2
// on http://php.net/manual/de/function.mktime.php.
// Mind that this is exclusive and only used in the queries below
// so that Order date < $end_date!
$end_date = date(ASCMS_DATE_FORMAT_INTERNATIONAL_DATETIME, mktime(0, 0, 0, $end_month + 1, 1, $end_year));
$selectedStat = isset($_REQUEST['selectstats']) ? intval($_REQUEST['selectstats']) : 0;
if ($selectedStat == 2) {
// Product statistic
$objTemplate->setVariable(array('TXT_COLUMN_1_DESC' => $_ARRAYLANG['TXT_PRODUCT_NAME'], 'TXT_COLUMN_2_DESC' => $_ARRAYLANG['TXT_COUNT_ARTICLES'], 'TXT_COLUMN_3_DESC' => $_ARRAYLANG['TXT_STOCK'], 'SHOP_ORDERS_SELECTED' => '', 'SHOP_ARTICLES_SELECTED' => \Html::ATTRIBUTE_SELECTED, 'SHOP_CUSTOMERS_SELECTED' => ''));
$arrSql = \Text::getSqlSnippets('`B`.`id`', FRONTEND_LANG_ID, 'Shop', array('title' => Product::TEXT_NAME));
$query = "\n SELECT A.product_id AS id,\n A.quantity AS shopColumn2,\n A.price AS sum,\n B.stock AS shopColumn3,\n C.currency_id, " . $arrSql['field'] . "\n FROM " . DBPREFIX . "module_shop" . MODULE_INDEX . "_order_items AS A\n JOIN " . DBPREFIX . "module_shop" . MODULE_INDEX . "_orders AS C\n ON A.order_id=C.id\n JOIN " . DBPREFIX . "module_shop" . MODULE_INDEX . "_products AS B\n ON A.product_id=B.id" . $arrSql['join'] . "\n WHERE C.date_time>='{$start_date}'\n AND C.date_time<'{$end_date}'\n AND ( C.status=" . Order::STATUS_CONFIRMED . "\n OR C.status=" . Order::STATUS_COMPLETED . ")\n ORDER BY shopColumn2 DESC";
} elseif ($selectedStat == 3) {
// Customer statistic
$objTemplate->setVariable(array('TXT_COLUMN_1_DESC' => $_ARRAYLANG['TXT_NAME'], 'TXT_COLUMN_2_DESC' => $_ARRAYLANG['TXT_COMPANY'], 'TXT_COLUMN_3_DESC' => $_ARRAYLANG['TXT_COUNT_ARTICLES'], 'SHOP_ORDERS_SELECTED' => '', 'SHOP_ARTICLES_SELECTED' => '', 'SHOP_CUSTOMERS_SELECTED' => \Html::ATTRIBUTE_SELECTED));
$query = "\n SELECT A.sum AS sum,\n A.currency_id AS currency_id,\n sum(B.quantity) AS shopColumn3,\n A.customer_id AS id\n FROM " . DBPREFIX . "module_shop" . MODULE_INDEX . "_orders AS A\n JOIN " . DBPREFIX . "module_shop" . MODULE_INDEX . "_order_items AS B\n ON A.id=B.order_id\n WHERE A.date_time>='{$start_date}'\n AND A.date_time<'{$end_date}'\n AND ( A.status=" . Order::STATUS_CONFIRMED . "\n OR A.status=" . Order::STATUS_COMPLETED . ")\n GROUP BY B.order_id\n ORDER BY sum DESC";
} else {
// Order statistic (default); sales per month
$objTemplate->setVariable(array('TXT_COLUMN_1_DESC' => $_ARRAYLANG['TXT_DATE'], 'TXT_COLUMN_2_DESC' => $_ARRAYLANG['TXT_COUNT_ORDERS'], 'TXT_COLUMN_3_DESC' => $_ARRAYLANG['TXT_COUNT_ARTICLES'], 'SHOP_ORDERS_SELECTED' => \Html::ATTRIBUTE_SELECTED, 'SHOP_ARTICLES_SELECTED' => '', 'SHOP_CUSTOMERS_SELECTED' => ''));
$query = "\n SELECT SUM(A.quantity) AS shopColumn3,\n COUNT(A.order_id) AS shopColumn2,\n B.currency_id,\n B.sum AS sum,\n DATE_FORMAT(B.date_time, '%m') AS month,\n DATE_FORMAT(B.date_time, '%Y') AS year\n FROM " . DBPREFIX . "module_shop" . MODULE_INDEX . "_order_items AS A,\n " . DBPREFIX . "module_shop" . MODULE_INDEX . "_orders AS B\n WHERE A.order_id=B.id\n AND B.date_time>='{$start_date}'\n AND B.date_time<'{$end_date}'\n AND ( B.status=" . Order::STATUS_CONFIRMED . "\n OR B.status=" . Order::STATUS_COMPLETED . ")\n GROUP BY B.id\n ORDER BY year DESC, month DESC";
}
$arrayResults = array();
$objResult = $objDatabase->Execute($query);
if (!$objResult) {
return Order::errorHandler();
}
$sumColumn3 = $sumColumn4 = 0;
$sumColumn2 = '';
if ($selectedStat == 2) {
// Product statistc
while (!$objResult->EOF) {
// set currency id
Currency::setActiveCurrencyId($objResult->fields['currency_id']);
$key = $objResult->fields['id'];
if (!isset($arrayResults[$key])) {
$arrayResults[$key] = array('column1' => '<a href="index.php?cmd=Shop' . MODULE_INDEX . '&act=products&tpl=manage&id=' . $objResult->fields['id'] . '" title="' . $objResult->fields['title'] . '">' . $objResult->fields['title'] . '</a>', 'column2' => 0, 'column3' => $objResult->fields['shopColumn3'], 'column4' => 0);
}
$arrayResults[$key]['column2'] += +$objResult->fields['shopColumn2'];
$arrayResults[$key]['column4'] += +$objResult->fields['shopColumn2'] * Currency::getDefaultCurrencyPrice($objResult->fields['sum']);
$objResult->MoveNext();
}
if (is_array($arrayResults)) {
foreach ($arrayResults as $entry) {
$sumColumn2 = $sumColumn2 + $entry['column2'];
$sumColumn3 = $sumColumn3 + $entry['column3'];
$sumColumn4 = $sumColumn4 + $entry['column4'];
}
rsort($arrayResults);
}
} elseif ($selectedStat == 3) {
// Customer statistic
while (!$objResult->EOF) {
Currency::setActiveCurrencyId($objResult->fields['currency_id']);
$key = $objResult->fields['id'];
if (!isset($arrayResults[$key])) {
$objUser = \FWUser::getFWUserObject()->objUser;
$objUser = $objUser->getUser($key);
$company = '';
$name = $_ARRAYLANG['TXT_SHOP_CUSTOMER_NOT_FOUND'];
if ($objUser) {
$company = $objUser->getProfileAttribute('company');
$name = $objUser->getProfileAttribute('firstname') . ' ' . $objUser->getProfileAttribute('lastname');
}
$arrayResults[$key] = array('column1' => '<a href="index.php?cmd=Shop' . MODULE_INDEX . '&act=customerdetails&customer_id=' . $objResult->fields['id'] . '">' . $name . '</a>', 'column2' => $company, 'column3' => 0, 'column4' => 0);
}
$arrayResults[$key]['column3'] += $objResult->fields['shopColumn3'];
$arrayResults[$key]['column4'] += Currency::getDefaultCurrencyPrice($objResult->fields['sum']);
$sumColumn3 += $objResult->fields['shopColumn3'];
$sumColumn4 += Currency::getDefaultCurrencyPrice($objResult->fields['sum']);
$objResult->MoveNext();
}
} else {
// Order statistic (default)
$arrayMonths = explode(',', $_ARRAYLANG['TXT_MONTH_ARRAY']);
while (!$objResult->EOF) {
$key = $objResult->fields['year'] . '.' . $objResult->fields['month'];
if (!isset($arrayResults[$key])) {
$arrayResults[$key] = array('column1' => '', 'column2' => 0, 'column3' => 0, 'column4' => 0);
}
$arrayResults[$key]['column1'] = $arrayMonths[intval($objResult->fields['month']) - 1] . ' ' . $objResult->fields['year'];
$arrayResults[$key]['column2'] = $arrayResults[$key]['column2'] + 1;
$arrayResults[$key]['column3'] = $arrayResults[$key]['column3'] + $objResult->fields['shopColumn3'];
$arrayResults[$key]['column4'] = $arrayResults[$key]['column4'] + Currency::getDefaultCurrencyPrice($objResult->fields['sum']);
$sumColumn2 = $sumColumn2 + 1;
$sumColumn3 = $sumColumn3 + $objResult->fields['shopColumn3'];
$sumColumn4 = $sumColumn4 + Currency::getDefaultCurrencyPrice($objResult->fields['sum']);
$objResult->MoveNext();
}
krsort($arrayResults, SORT_NUMERIC);
}
$objTemplate->setCurrentBlock('statisticRow');
$i = 0;
if (is_array($arrayResults)) {
foreach ($arrayResults as $entry) {
$objTemplate->setVariable(array('SHOP_ROWCLASS' => 'row' . (++$i % 2 + 1), 'SHOP_COLUMN_1' => $entry['column1'], 'SHOP_COLUMN_2' => $entry['column2'], 'SHOP_COLUMN_3' => $entry['column3'], 'SHOP_COLUMN_4' => Currency::formatPrice($entry['column4']) . ' ' . Currency::getDefaultCurrencySymbol()));
$objTemplate->parse('statisticRow');
}
}
$query_currency = "\n SELECT currency_id, sum,\n DATE_FORMAT(date_time, '%m') AS month,\n DATE_FORMAT(date_time, '%Y') AS year\n FROM " . DBPREFIX . "module_shop" . MODULE_INDEX . "_orders\n WHERE status=" . Order::STATUS_CONFIRMED . "\n OR status=" . Order::STATUS_COMPLETED . "\n ORDER BY date_time DESC";
$objResult = $objDatabase->Execute($query_currency);
if (!$objResult) {
return Order::errorHandler();
}
$totalSoldProducts = 0;
$query_totalproducts = "\n SELECT sum(A.quantity) AS shopTotalSoldProducts\n FROM " . DBPREFIX . "module_shop" . MODULE_INDEX . "_order_items AS A,\n " . DBPREFIX . "module_shop" . MODULE_INDEX . "_orders AS B\n WHERE A.order_id=B.id\n AND ( B.status=" . Order::STATUS_CONFIRMED . "\n OR B.status=" . Order::STATUS_COMPLETED . ")";
$objResult = $objDatabase->SelectLimit($query_totalproducts, 1);
if ($objResult) {
if (!$objResult->EOF) {
$totalSoldProducts = $objResult->fields['shopTotalSoldProducts'];
$objResult->MoveNext();
}
}
$totalOrderSum = 0;
$totalOrders = 0;
$bestMonthSum = 0;
$bestMonthDate = '';
$arrShopMonthSum = array();
$objResult = $objDatabase->Execute($query);
while (!$objResult->EOF) {
$orderSum = Currency::getDefaultCurrencyPrice($objResult->fields['sum']);
if (!isset($arrShopMonthSum[$objResult->fields['year']][$objResult->fields['month']])) {
$arrShopMonthSum[$objResult->fields['year']][$objResult->fields['month']] = 0;
}
$arrShopMonthSum[$objResult->fields['year']][$objResult->fields['month']] += $orderSum;
$totalOrderSum += $orderSum;
$totalOrders++;
$objResult->MoveNext();
}
$months = explode(',', $_ARRAYLANG['TXT_MONTH_ARRAY']);
foreach ($arrShopMonthSum as $year => $arrMonth) {
foreach ($arrMonth as $month => $sum) {
if ($bestMonthSum < $sum) {
$bestMonthSum = $sum;
$bestMonthDate = $months[$month - 1] . ' ' . $year;
}
}
}
$objTemplate->setVariable(array('SHOP_ROWCLASS' => 'row' . (++$i % 2 + 1), 'SHOP_TOTAL_SUM' => Currency::formatPrice($totalOrderSum) . ' ' . Currency::getDefaultCurrencySymbol(), 'SHOP_MONTH' => $bestMonthDate, 'SHOP_MONTH_SUM' => Currency::formatPrice($bestMonthSum) . ' ' . Currency::getDefaultCurrencySymbol(), 'SHOP_TOTAL_ORDERS' => $totalOrders, 'SHOP_SOLD_ARTICLES' => $totalSoldProducts, 'SHOP_SUM_COLUMN_2' => $sumColumn2, 'SHOP_SUM_COLUMN_3' => $sumColumn3, 'SHOP_SUM_COLUMN_4' => Currency::formatPrice($sumColumn4) . ' ' . Currency::getDefaultCurrencySymbol()));
return true;
}
/**
* Initialize the mail template array for the current module
*
* Uses the given language ID $lang_id if not empty, or all active
* frontend languages otherwise.
* The $limit value defaults to the value of the
* mailtemplate_per_page_backend setting from the core settings
* (@see \Cx\Core\Setting\Controller\Setting}.
* @param integer $section The section
* @param integer $lang_id The optional language ID
* @param string $order The optional sorting order string,
* SQL syntax
* @param integer $position The optional position offset,
* defaults to zero
* @param integer $limit The optional limit for returned
* templates
* @param integer $count The actual count of templates
* available in total, by reference
* @return boolean True on success, false otherwise
*/
static function init($section, $lang_id = null, $order = '', $position = 0, $limit = -1, &$count = 0)
{
global $objDatabase;
if (empty($section)) {
die("MailTemplate::init(): Empty section!");
}
$arrLanguageId = null;
if ($lang_id) {
// Init one language
$arrLanguageId = array($lang_id);
} else {
// Load all languages if none is specified
$arrLanguageId = \FWLanguage::getIdArray();
}
self::reset();
if (empty($limit)) {
$limit = \Cx\Core\Setting\Controller\Setting::getValue('mailtemplate_per_page_backend');
}
if (empty($limit)) {
$limit = 25;
}
$query_from = null;
self::$arrTemplates = array();
foreach ($arrLanguageId as $lang_id) {
$arrSql = \Text::getSqlSnippets('`mail`.`text_id`', $lang_id, $section, array('name' => self::TEXT_NAME, 'from' => self::TEXT_FROM, 'sender' => self::TEXT_SENDER, 'reply' => self::TEXT_REPLY, 'to' => self::TEXT_TO, 'cc' => self::TEXT_CC, 'bcc' => self::TEXT_BCC, 'subject' => self::TEXT_SUBJECT, 'message' => self::TEXT_MESSAGE, 'message_html' => self::TEXT_MESSAGE_HTML, 'attachments' => self::TEXT_ATTACHMENTS, 'inline' => self::TEXT_INLINE));
$query_from = "\n FROM `" . DBPREFIX . "core_mail_template` AS `mail`" . $arrSql['join'] . "\n WHERE `mail`.`section`" . (isset($section) ? "='" . addslashes($section) . "'" : ' IS NULL');
$query_order = $order ? " ORDER BY {$order}" : '';
// The count of available templates needs to be initialized to zero
// in case there is a problem with one of the queries ahead.
// Ignore the code analyzer warning.
$count = 0;
$objResult = $objDatabase->SelectLimit("\n SELECT `mail`.`key`, `mail`.`text_id`, `mail`.`protected`, `mail`.`html`, " . $arrSql['field'] . $query_from . $query_order, $limit, $position);
if (!$objResult) {
return self::errorHandler();
}
while (!$objResult->EOF) {
$available = true;
$key = $objResult->fields['key'];
$text_id = $objResult->fields['text_id'];
$strName = $objResult->fields['name'];
if ($strName === null) {
$strName = \Text::getById($text_id, $section, self::TEXT_NAME)->content();
if ($strName) {
$available = false;
}
}
$strFrom = $objResult->fields['from'];
if ($strFrom === null) {
$strFrom = \Text::getById($text_id, $section, self::TEXT_FROM)->content();
if ($strFrom) {
$available = false;
}
}
$strSender = $objResult->fields['sender'];
if ($strSender === null) {
$strSender = \Text::getById($text_id, $section, self::TEXT_SENDER)->content();
if ($strSender) {
$available = false;
}
}
$strReply = $objResult->fields['reply'];
if ($strReply === null) {
$strReply = \Text::getById($text_id, $section, self::TEXT_REPLY)->content();
if ($strReply) {
$available = false;
}
}
$strTo = $objResult->fields['to'];
if ($strTo === null) {
$strTo = \Text::getById($text_id, $section, self::TEXT_TO)->content();
if ($strTo) {
$available = false;
}
}
$strCc = $objResult->fields['cc'];
if ($strCc === null) {
$strCc = \Text::getById($text_id, $section, self::TEXT_CC)->content();
if ($strCc) {
$available = false;
}
}
$strBcc = $objResult->fields['bcc'];
if ($strBcc === null) {
$strBcc = \Text::getById($text_id, $section, self::TEXT_BCC)->content();
if ($strBcc) {
$available = false;
}
}
$strSubject = $objResult->fields['subject'];
if ($strSubject === null) {
$strSubject = \Text::getById($text_id, $section, self::TEXT_SUBJECT)->content();
if ($strSubject) {
$available = false;
}
}
$strMessage = $objResult->fields['message'];
if ($strMessage === null) {
$strMessage = \Text::getById($text_id, $section, self::TEXT_MESSAGE)->content();
if ($strMessage) {
$available = false;
}
}
$strMessageHtml = $objResult->fields['message_html'];
if ($strMessageHtml === null) {
$strMessageHtml = \Text::getById($text_id, $section, self::TEXT_MESSAGE_HTML)->content();
if ($strMessageHtml) {
$available = false;
}
}
$strAttachments = $objResult->fields['attachments'];
if ($strAttachments === null) {
$strAttachments = \Text::getById($text_id, $section, self::TEXT_ATTACHMENTS)->content();
if ($strAttachments) {
$available = false;
}
}
$strInline = $objResult->fields['inline'];
if ($strInline === null) {
$strInline = \Text::getById($text_id, $section, self::TEXT_INLINE)->content();
if ($strInline) {
$available = false;
}
}
// TODO: Hard to decide which should be mandatory, as any of them may
// be filled in "just in time". -- Time will tell.
// if ( $strName == ''
// || $strFrom == ''
// || $strSender == ''
// || $strReply == ''
// || $strTo == ''
// || $strCc == ''
// || $strBcc == ''
// || $strSubject == ''
// || $strMessage == ''
// || $strMessageHtml == ''
// || $strAttachments == ''
// || $strInline == ''
// ) {
// $available = false;
// }
self::$arrTemplates[$lang_id][$key] = array('key' => $key, 'text_id' => $text_id, 'name' => $strName, 'protected' => $objResult->fields['protected'], 'html' => $objResult->fields['html'], 'from' => $strFrom, 'sender' => $strSender, 'reply' => $strReply, 'to' => $strTo, 'cc' => $strCc, 'bcc' => $strBcc, 'subject' => $strSubject, 'message' => $strMessage, 'message_html' => $strMessageHtml, 'attachments' => eval("{$strAttachments};"), 'inline' => eval("{$strInline};"), 'available' => $available);
$objResult->MoveNext();
}
}
$objResult = $objDatabase->Execute("\n SELECT COUNT(*) AS `count` {$query_from}");
if (!$objResult) {
return self::errorHandler();
}
$count += $objResult->fields['count'];
// Remember the module used
self::$section = $section;
return true;
}
/**
* Initializes all static Discount data
* @return boolean True on success, false otherwise
*/
static function init()
{
global $objDatabase;
$arrSql = \Text::getSqlSnippets('`discount`.`id`', FRONTEND_LANG_ID, 'Shop', array('name' => self::TEXT_NAME_GROUP_COUNT, 'unit' => self::TEXT_UNIT_GROUP_COUNT));
$query = "\n SELECT `discount`.`id`, " . $arrSql['field'] . "\n FROM `" . DBPREFIX . "module_shop" . MODULE_INDEX . "_discountgroup_count_name` AS `discount`\n " . $arrSql['join'] . "\n ORDER BY `discount`.`id` ASC";
$objResult = $objDatabase->Execute($query);
if (!$objResult) {
return self::errorHandler();
}
self::$arrDiscountCountName = array();
while (!$objResult->EOF) {
$group_id = $objResult->fields['id'];
$strName = $objResult->fields['name'];
if (is_null($strName)) {
$strName = \Text::getById($group_id, 'Shop', self::TEXT_NAME_GROUP_COUNT)->content();
}
$strUnit = $objResult->fields['unit'];
if (is_null($strUnit)) {
$strUnit = \Text::getById($group_id, 'Shop', self::TEXT_UNIT_GROUP_COUNT)->content();
}
self::$arrDiscountCountName[$group_id] = array('name' => $strName, 'unit' => $strUnit);
$objResult->MoveNext();
}
// Note that the ordering is significant here.
// Some methods rely on it to find the applicable rate.
$query = "\n SELECT `group_id`, `count`, `rate`\n FROM `" . DBPREFIX . "module_shop" . MODULE_INDEX . "_discountgroup_count_rate`\n ORDER by `count` DESC";
$objResult = $objDatabase->Execute($query);
if (!$objResult) {
return self::errorHandler();
}
self::$arrDiscountCountRate = array();
while (!$objResult->EOF) {
self::$arrDiscountCountRate[$objResult->fields['group_id']][$objResult->fields['count']] = $objResult->fields['rate'];
$objResult->MoveNext();
}
$arrSqlName = \Text::getSqlSnippets('`discount`.`id`', FRONTEND_LANG_ID, 'Shop', array('customer' => self::TEXT_NAME_GROUP_CUSTOMER));
$query = "\n SELECT `discount`.`id`, " . $arrSqlName['field'] . "\n FROM `" . DBPREFIX . "module_shop" . MODULE_INDEX . "_customer_group` AS `discount`\n " . $arrSqlName['join'] . "\n ORDER BY `discount`.`id` ASC";
$objResult = $objDatabase->Execute($query);
if (!$objResult) {
return self::errorHandler();
}
self::$arrCustomerGroup = array();
while (!$objResult->EOF) {
$group_id = $objResult->fields['id'];
$strName = $objResult->fields['customer'];
if (is_null($strName)) {
$strName = \Text::getById($group_id, 'Shop', self::TEXT_NAME_GROUP_CUSTOMER)->content();
}
self::$arrCustomerGroup[$group_id] = array('name' => $strName);
$objResult->MoveNext();
}
$arrSqlName = \Text::getSqlSnippets('`discount`.`id`', FRONTEND_LANG_ID, 'Shop', array('article' => self::TEXT_NAME_GROUP_ARTICLE));
$query = "\n SELECT `discount`.`id`, " . $arrSqlName['field'] . "\n FROM `" . DBPREFIX . "module_shop" . MODULE_INDEX . "_article_group` AS `discount`\n " . $arrSqlName['join'] . "\n ORDER BY `discount`.`id` ASC";
$objResult = $objDatabase->Execute($query);
if (!$objResult) {
return self::errorHandler();
}
self::$arrArticleGroup = array();
while (!$objResult->EOF) {
$group_id = $objResult->fields['id'];
$strName = $objResult->fields['article'];
if (is_null($strName)) {
$strName = \Text::getById($group_id, 'Shop', self::TEXT_NAME_GROUP_ARTICLE)->content();
}
self::$arrArticleGroup[$group_id] = array('name' => $strName);
$objResult->MoveNext();
}
//DBG::log("Discount::init(): Made \$arrArticleGroup: ".var_export(self::$arrArticleGroup, true));
$query = "\n SELECT `customer_group_id`, `article_group_id`, `rate`\n FROM `" . DBPREFIX . "module_shop" . MODULE_INDEX . "_rel_discount_group`";
$objResult = $objDatabase->Execute($query);
if (!$objResult) {
return self::errorHandler();
}
self::$arrDiscountRateCustomer = array();
while (!$objResult->EOF) {
self::$arrDiscountRateCustomer[$objResult->fields['customer_group_id']][$objResult->fields['article_group_id']] = $objResult->fields['rate'];
$objResult->MoveNext();
}
return true;
}
/**
* Returns an array of two arrays; one with countries in the given zone,
* the other with the remaining countries.
*
* The array looks like this:
* array(
* 'in' => array( // Countries in the zone
* country ID => array(
* 'id' => country ID,
* 'name' => country name,
* ),
* ... more ...
* ),
* 'out' => array( // Countries not in the zone
* country ID => array(
* 'id' => country ID,
* 'name' => country name,
* ),
* ... more ...
* ),
* );
* @param integer $zone_id The zone ID
* @return array Countries array, as described above
*/
static function getArraysByZoneId($zone_id)
{
global $objDatabase;
if (empty(self::$arrCountries)) {
self::init();
}
// Query relations between zones and countries:
// Get all country IDs and names associated with that zone ID
$arrSqlName = \Text::getSqlSnippets('`country`.`id`', FRONTEND_LANG_ID, 'Shop', array('name' => self::TEXT_NAME));
$query = "\n SELECT `country`.`id`, `relation`.`country_id`, `zone_id`, " . $arrSqlName['field'] . "\n FROM `" . DBPREFIX . "module_shop" . MODULE_INDEX . "_countries` AS `country`" . $arrSqlName['join'] . "\n LEFT JOIN `" . DBPREFIX . "module_shop" . MODULE_INDEX . "_rel_countries` AS `relation`\n ON `country`.`id`=`relation`.`country_id`\n WHERE `country`.`active`=1\n ORDER BY `name` ASC";
// AND `relation`.`zone_id`=$zone_id
$objResult = $objDatabase->Execute($query);
if (!$objResult) {
return false;
}
// Initialize the array to avoid notices when one or the other is empty
$arrZoneCountries = array('in' => array(), 'out' => array());
while (!$objResult->EOF) {
$id = $objResult->fields['countries_id'];
$name = $objResult->fields['name'];
$country_zone_id = $objResult->fields['zone_id'];
// Country may only be in the Zone if it exists and is active
// if ( empty(self::$arrCountries[$id])
// || empty(self::$arrCountries[$id]['active']))
// continue;
$arrZoneCountries[$zone_id == $country_zone_id ? 'in' : 'out'][$id] = array('id' => $id, 'name' => $name);
$objResult->MoveNext();
}
return $arrZoneCountries;
}
/**
* Initialize the Vat object with current values from the database.
*
* Set up two class array variables, one called $arrVatClass, like
* (ID => "class", ...)
* and the other called $arrVatRate, like
* (ID => rate)
* Plus initializes the various object variables.
* May die() with a message if it fails to access its settings.
* @global ADONewConnection $objDatabase Database connection object
* @return void
* @static
*/
static function init()
{
global $objDatabase;
$arrSqlClass = \Text::getSqlSnippets('`vat`.`id`', FRONTEND_LANG_ID, 'Shop', array('name' => self::TEXT_CLASS));
$query = "\n SELECT `vat`.`id`, `vat`.`rate`, " . $arrSqlClass['field'] . "\n FROM " . DBPREFIX . "module_shop" . MODULE_INDEX . "_vat as `vat`" . $arrSqlClass['join'];
$objResult = $objDatabase->Execute($query);
if (!$objResult) {
return self::errorHandler();
}
self::$arrVat = array();
while (!$objResult->EOF) {
$id = $objResult->fields['id'];
$strClass = $objResult->fields['name'];
// Replace Text in a missing language by another, if available
if ($strClass === null) {
$objText = \Text::getById($id, 'Shop', self::TEXT_CLASS);
if ($objText) {
$strClass = $objText->content();
}
}
self::$arrVat[$id] = array('id' => $id, 'rate' => $objResult->fields['rate'], 'class' => $strClass);
$objResult->MoveNext();
}
self::$arrVatEnabled = array(0 => array(0 => \Cx\Core\Setting\Controller\Setting::getValue('vat_enabled_foreign_customer', 'Shop'), 1 => \Cx\Core\Setting\Controller\Setting::getValue('vat_enabled_foreign_reseller', 'Shop')), 1 => array(0 => \Cx\Core\Setting\Controller\Setting::getValue('vat_enabled_home_customer', 'Shop'), 1 => \Cx\Core\Setting\Controller\Setting::getValue('vat_enabled_home_reseller', 'Shop')));
self::$arrVatIncluded = array(0 => array(0 => \Cx\Core\Setting\Controller\Setting::getValue('vat_included_foreign_customer', 'Shop'), 1 => \Cx\Core\Setting\Controller\Setting::getValue('vat_included_foreign_reseller', 'Shop')), 1 => array(0 => \Cx\Core\Setting\Controller\Setting::getValue('vat_included_home_customer', 'Shop'), 1 => \Cx\Core\Setting\Controller\Setting::getValue('vat_included_home_reseller', 'Shop')));
self::$vatDefaultId = \Cx\Core\Setting\Controller\Setting::getValue('vat_default_id', 'Shop');
self::$vatDefaultRate = self::getRate(self::$vatDefaultId);
self::$vatOtherId = \Cx\Core\Setting\Controller\Setting::getValue('vat_other_id', 'Shop');
return true;
}
/**
* Returns an array of two arrays; one with countries in the given zone,
* the other with the remaining countries.
*
* If $zone_id is empty, includes Countries for all Zones present.
* The array looks like this:
* array(
* 'in' => array( // Countries in the zone
* country ID => array(
* 'id' => country ID,
* 'name' => country name,
* ),
* ... more ...
* ),
* 'out' => array( // Countries not in the zone
* country ID => array(
* 'id' => country ID,
* 'name' => country name,
* ),
* ... more ...
* ),
* );
* @todo Shop use only (should be moved back there)!
* @param integer $zone_id The optional Zone ID
* @return array Countries array, as described above
*/
static function getArraysByZoneId($zone_id = null)
{
global $objDatabase;
if (is_null(self::$arrCountries)) {
self::init();
}
// Query relations between zones and countries:
// Get all country IDs and names
// associated with that zone ID
$arrSqlName = \Text::getSqlSnippets('`country`.`id`', FRONTEND_LANG_ID, 'core', array('name' => self::TEXT_NAME));
$query = "\n SELECT `country`.`id`, " . $arrSqlName['field'] . "\n FROM `" . DBPREFIX . "core_country` AS `country`\n INNER JOIN `" . DBPREFIX . "module_shop" . MODULE_INDEX . "_rel_countries` AS `relation`\n ON `country`.`id`=`relation`.`country_id`\n " . $arrSqlName['join'] . "\n WHERE `country`.`active`=1\n " . ($zone_id ? "AND `relation`.`zone_id`={$zone_id}" : '') . "\n ORDER BY `name` ASC";
$objResult = $objDatabase->Execute($query);
if (!$objResult) {
return false;
}
// Initialize the array to avoid notices when one or the other is empty
$arrZoneCountries = array('in' => array(), 'out' => array());
while (!$objResult->EOF) {
$id = $objResult->fields['id'];
$strName = $objResult->fields['name'];
if ($strName === null) {
//DBG::log(("MISSING Name for ID $id"));
$objText = \Text::getById($id, 'core', self::TEXT_NAME, 0);
//DBG::log(("GOT Name for Text ID $id: ".$objText->content()));
if ($objText) {
$strName = $objText->content();
}
}
//DBG::log(("IN zone: ID $id - $strName"));
$arrZoneCountries['in'][$id] = array('id' => $id, 'name' => $strName);
$objResult->MoveNext();
}
foreach (self::$arrCountries as $id => $arrCountry) {
// Country may only be available for the Zone if it's
// not in yet and it's active
if (empty($arrZoneCountries['in'][$id]) && $arrCountry['active']) {
//DBG::log(("OUT zone: ID $id - {$arrCountry['name']}"));
$arrZoneCountries['out'][$id] = array('id' => $id, 'name' => $arrCountry['name']);
}
}
return $arrZoneCountries;
}
/**
* Returns a ShopCategory selected by its ID from the database.
*
* Returns null if the Category does not exist.
* @static
* @param integer $category_id The Shop Category ID
* @return ShopCategory The Shop Category object on success,
* false on failure, or null otherwise.
* @global ADONewConnection $objDatabase Database connection object
* @author Reto Kohli <*****@*****.**>
*/
static function getById($category_id)
{
global $objDatabase;
$category_id = intval($category_id);
if ($category_id <= 0) {
return null;
}
$arrSql = \Text::getSqlSnippets('`category`.`id`', FRONTEND_LANG_ID, 'Shop', array('name' => self::TEXT_NAME, 'description' => self::TEXT_DESCRIPTION));
$query = "\n SELECT `category`.`id`,\n `category`.`parent_id`,\n `category`.`active`,\n `category`.`ord`,\n `category`.`picture`,\n `category`.`flags`, " . $arrSql['field'] . "\n FROM `" . DBPREFIX . "module_shop" . MODULE_INDEX . "_categories` AS `category`" . $arrSql['join'] . "\n WHERE `category`.`id`={$category_id}";
$objResult = $objDatabase->Execute($query);
if (!$objResult) {
return self::errorHandler();
}
if ($objResult->EOF) {
return null;
}
$id = $objResult->fields['id'];
$strName = $objResult->fields['name'];
if ($strName === null) {
$objText = \Text::getById($id, 'Shop', self::TEXT_NAME);
if ($objText) {
$strName = $objText->content();
}
}
$strDescription = $objResult->fields['description'];
if ($strDescription === null) {
$objText = \Text::getById($id, 'Shop', self::TEXT_DESCRIPTION);
if ($objText) {
$strDescription = $objText->content();
}
}
//DBG::log("ShopCategory::getById($category_id): Loaded '$strName' / '$strDescription'");
$objCategory = new ShopCategory($strName, $strDescription, $objResult->fields['parent_id'], $objResult->fields['active'], $objResult->fields['ord'], $category_id);
$objCategory->picture($objResult->fields['picture']);
$objCategory->flags($objResult->fields['flags']);
return $objCategory;
}
/**
* Initialize the arrays of extensions and mime types on request
*
* The arrays look like this:
* $arrExtensions2Mimetypes = array(
* Extension => array(
* 'id' => ID,
* 'text_id' => Text ID,
* 'name' => Name,
* 'extension' => Extension,
* 'mimetype' => array(
* MIME Type,
* ... more ...
* ),
* ),
* ... more ...
* );
*
* $arrMimetypes2Extensions = array(
* MIME Type => array(
* 'id' => ID,
* 'text_id' => Text ID,
* 'name' => Name,
* 'mimetype' => MIME Type,
* 'extension' => array(
* Extension,
* ... more ...
* ),
* ),
* ... more ...
* );
* @author Reto Kohli <*****@*****.**>
* @return boolean True on success, false otherwise
* @static
*/
static function init()
{
global $objDatabase;
$arrSqlName = Text::getSqlSnippets('`filetype`.`text_name_id`', FRONTEND_LANG_ID, 0, self::TEXT_NAME);
$query = "\n SELECT `filetype`.`id`,\n `filetype`.`extension`, `filetype`.`mimetype`" . $arrSqlName['field'] . "\n FROM " . DBPREFIX . "core_filetype AS `filetype`" . $arrSqlName['join'] . "\n ORDER BY `filetype`.`ord` ASC";
$objResult = $objDatabase->Execute($query);
if (!$objResult) {
return self::errorHandler();
}
self::$arrExtensions2Mimetypes = array();
while (!$objResult->EOF) {
$id = $objResult->fields['id'];
$text_id = $objResult->fields[$arrSqlName['id']];
$strName = $objResult->fields[$arrSqlName['text']];
if ($strName === null) {
$objText = Text::getById($id, 0);
if ($objText) {
$strName = $objText->getText();
}
}
if (empty(self::$arrExtensions2Mimetypes[$objResult->fields['extension']])) {
self::$arrExtensions2Mimetypes[$objResult->fields['extension']] = array('id' => $id, 'text_id' => $text_id, 'name' => $strName, 'extension' => $objResult->fields['extension']);
}
self::$arrExtensions2Mimetypes[$objResult->fields['extension']]['mimetype'][] = $objResult->fields['mimetype'];
if (empty(self::$arrMimetypes2Extensions[$objResult->fields['mimetype']])) {
self::$arrMimetypes2Extensions[$objResult->fields['mimetype']] = array('id' => $id, 'text_id' => $text_id, 'name' => $strName, 'mimetype' => $objResult->fields['mimetype']);
}
self::$arrMimetypes2Extensions[$objResult->fields['mimetype']]['extension'][] = $objResult->fields['extension'];
$objResult->MoveNext();
}
return true;
}
