/**
* getList
*
* @param array $pListHash
* @access public
* @return TRUE on success, FALSE on failure - mErrors will contain reason for failure
*/
function getList(&$pListHash)
{
if (empty($pListHash['sort_mode'])) {
$pListHash['sort_mode'] = 'word_desc';
}
BitBase::prepGetList($pListHash);
$whereSql = '';
$bindVars = $ret = array();
if (!empty($pListHash['find'])) {
$whereSql .= empty($whereSql) ? ' WHERE ' : ' AND ';
$whereSql .= " UPPER( hw.`word` ) LIKE ? ";
$bindVars[] = '%' . strtoupper($pListHash['find']) . '%';
}
$query = "\n\t\t\tSELECT hw.`word`, hw.`url`\n\t\t\tFROM `" . BIT_DB_PREFIX . "hotwords` hw\n\t\t\t{$whereSql}\n\t\t\tORDER BY " . $this->mDb->convertSortmode($pListHash['sort_mode']);
$result = $this->mDb->query($query, $bindVars, $pListHash['max_records'], $pListHash['offset']);
while ($aux = $result->fetchRow()) {
$ret[] = $aux;
}
$query = "\n\t\t\tSELECT COUNT( hw.`word` )\n\t\t\tFROM `" . BIT_DB_PREFIX . "hotwords` hw\n\t\t\t{$whereSql}";
$pListHash['cant'] = $this->mDb->getOne($query, $bindVars);
BitBase::postGetList($pListHash);
return $ret;
}
$interests = $gBitCustomer->getInterests();
$gBitSmarty->assign_by_ref('interestsList', $interests);
$gBitSmarty->assign('interestsName', $interests[$_REQUEST['interests_id']]);
$_REQUEST['orders_products'] = TRUE;
$_REQUEST['orders_status_comparison'] = '>';
$_REQUEST['orders_status_id'] = 0;
$gBitSmarty->assign('listOrders', order::getList($_REQUEST));
$gBitSystem->display('bitpackage:bitcommerce/admin_revenue_interest.tpl', tra('Revenue By Interest') . ' ' . $interests[$_REQUEST['interests_id']] . ' : ' . $_REQUEST['timeframe'], array('display_mode' => 'admin'));
} elseif (!empty($_REQUEST['referer'])) {
foreach (array('period', 'timeframe', 'referer', 'exclude', 'include', 'new_reg') as $param) {
if (!empty($_REQUEST[$param])) {
$_REQUEST['listInfo']['parameters'][$param] = $_REQUEST[$param];
}
}
$statsByReferer = $stats->getRevenueByReferer($_REQUEST);
BitBase::postGetList($_REQUEST);
$_REQUEST['listInfo']['block_pages'] = 3;
$_REQUEST['listInfo']['item_name'] = 'coupons';
$_REQUEST['listInfo']['page_records'] = count($statsByReferer['hosts']);
$gBitSmarty->assign_by_ref('listInfo', $_REQUEST['listInfo']);
$gBitSmarty->assign('statsByReferer', $stats->getRevenueByReferer($_REQUEST));
$gBitSystem->display('bitpackage:bitcommerce/admin_revenue_referer.tpl', 'Revenue By Referer', array('display_mode' => 'admin'));
} elseif (!empty($_REQUEST['timeframe'])) {
$statsByType = $stats->getRevenueByType($_REQUEST);
$gBitSmarty->assign_by_ref('statsByType', $statsByType);
$statsByOption = $stats->getRevenueByOption($_REQUEST);
foreach ($statsByOption as $stat) {
@($statsByOptionTotalUnits[$stat['products_options_id']] += $stat['total_units']);
}
$gBitSmarty->assign('statsByOption', $statsByOption);
$gBitSmarty->assign('statsByOptionTotalUnits', $statsByOptionTotalUnits);
/**
* search
*
* @param array $pSearchHash basically the same parameters as a regular list
* @access public
* @return TRUE on success, FALSE on failure - mErrors will contain reason for failure
*/
function search(&$pSearchHash)
{
// PHP compatability issues
include_once UTIL_PKG_PATH . 'PHP_Compat/Compat/Function/stripos.php';
global $gLibertySystem, $gBitSystem, $gBitUser, $gBitDbType;
// initiate stuff
BitBase::prepGetList($pSearchHash);
$ret = $bindVars = array();
$selectSql = $whereSql = $orderSql = $joinSql = '';
// if all content has been selected, there is an empty value in the array
if (isset($pSearchHash['content_type_guid']) && !is_array($pSearchHash['content_type_guid'])) {
$pSearchHash['content_type_guid'] = array($pSearchHash['content_type_guid']);
}
if (!empty($pSearchHash['content_type_guid']) && in_array('', $pSearchHash['content_type_guid'])) {
$pSearchHash['content_type_guid'] = array();
}
// check if the user has the required permissions to view the requested content type
foreach ($gLibertySystem->mContentTypes as $contentType) {
if ((empty($pSearchHash['content_type_guid']) || in_array($contentType["content_type_guid"], $pSearchHash['content_type_guid'])) && $this->hasViewPermission($contentType["content_type_guid"])) {
$allowed[] = $contentType["content_type_guid"];
}
}
if (in_array('bitcomment', $allowed)) {
$pSearchHash['include_comments'] = TRUE;
}
if (!empty($allowed)) {
$whereSql .= empty($whereSql) ? ' WHERE ' : ' AND ';
$whereSql .= " lc.`content_type_guid` IN( " . implode(',', array_fill(0, count($allowed), '?')) . " ) ";
$bindVars = array_merge($bindVars, $allowed);
} else {
$this->mErrors['permission'] = tra("You don't have the required permissions to search the requested content types.");
}
// create valid search SQL
if ($errors = $this->prepareSearchSql($pSearchHash, $whereSql, $bindVars)) {
$this->mErrors = $errors;
}
// get service SQL
LibertyContent::getServicesSql('content_list_sql_function', $selectSql, $joinSql, $whereSql, $bindVars, NULL, $pSearchHash);
if (!empty($pSearchHash['sort_mode'])) {
$orderSql = " ORDER BY lc." . $this->mDb->convertSortmode($pSearchHash['sort_mode']);
}
// only continue if we haven't choked so far
if (empty($this->mErrors)) {
$query = "\n\t\t\t\tSELECT \n\t\t\t\tuue.`login` AS `modifier_user`,\n\t\t\t\tuue.`real_name` AS `modifier_real_name`,\n\t\t\t\tuue.`user_id` AS `modifier_user_id`,\n\t\t\t\tuuc.`login` AS `creator_user`,\n\t\t\t\tuuc.`real_name` AS `creator_real_name`,\n\t\t\t\tuuc.`user_id` AS `creator_user_id`,\n\t\t\t\tlc.`data`, \n\t\t\t\tlc.`content_id`, \n\t\t\t\tlc.`title`, \n\t\t\t\tlcds.`data` AS `summary`, \n\t\t\t\tlct.`content_name`, \n\t\t\t\tlct.`content_name_plural`, \n\t\t\t\tlch.`hits`, \n\t\t\t\tlc.`last_modified`,\n\t\t\t\tlc.`created`,\n\t\t\t\tlc.`content_type_guid`\n\t\t\t\t{$selectSql}\n\t\t\t\tFROM `" . BIT_DB_PREFIX . "liberty_content` lc\n\t\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "users_users` uuc ON (lc.`user_id`=uuc.`user_id`)\n\t\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "users_users` uue ON (lc.`modifier_user_id`=uue.`user_id`)\n\t\t\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "liberty_content_data` lcds ON ( lc.`content_id` = lcds.`content_id` AND lcds.`data_type` = 'summary' )\n\t\t\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "liberty_content_types` lct ON ( lc.`content_type_guid` = lct.`content_type_guid` )\n\t\t\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "liberty_content_hits` lch ON ( lc.`content_id` = lch.`content_id` )\n\t\t\t\t{$joinSql} {$whereSql} {$orderSql}";
$result = $this->mDb->query($query, $bindVars, $pSearchHash['max_records'], $pSearchHash['offset']);
while ($aux = $result->fetchRow()) {
$data = $aux['summary'] . "\n" . $aux['data'];
$aux['len'] = strlen($data);
$lines = explode("\n", strip_tags($data));
foreach ($pSearchHash['findHash'] as $val) {
$val = trim($val, "%");
$i = 0;
foreach ($lines as $number => $line) {
if ($i < 3 && !empty($line) && stripos($line, $val) !== FALSE) {
$aux['display_lines'][$number + 1] = encode_email_addresses($line);
$i++;
}
}
if (!empty($aux['display_lines'])) {
ksort($aux['display_lines']);
}
}
$aux['display_url'] = BIT_ROOT_URL . "index.php?content_id=" . $aux['content_id'];
$ret[] = $aux;
}
// do some custom sorting
usort($ret, 'ilike_relevance_sort');
$query = "\n\t\t\t\tSELECT COUNT( lc.`content_id` )\n\t\t\t\tFROM `" . BIT_DB_PREFIX . "liberty_content` lc\n\t\t\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "liberty_content_data` lcds ON ( lc.`content_id` = lcds.`content_id` )\n\t\t\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "liberty_content_types` lct ON ( lc.`content_type_guid` = lct.`content_type_guid` )\n\t\t\t\t{$joinSql} {$whereSql}";
$pSearchHash['cant'] = $this->mDb->getOne($query, $bindVars);
BitBase::postGetList($pSearchHash);
return $ret;
} else {
return FALSE;
}
}
public static function getList(&$pListHash)
{
global $gBitDb;
$whereSql = '';
$bindVars = array($_SESSION['languages_id']);
$ret = array();
if (!empty($pListHash['coupon_type'])) {
$whereSql .= ' WHERE cc.`coupon_active`=? ';
$bindVars = $pListHash['status'];
} else {
// By default all non-gift coupons
$whereSql .= " WHERE `coupon_type` != 'G' ";
}
if (!empty($pListHash['uses'])) {
if (is_numeric($pListHash['uses'])) {
$whereSql .= ' AND cc.`uses_per_coupon`=? ';
$bindVars[] = $pListHash['uses'];
} elseif ($pListHash['uses'] == '+') {
$whereSql .= ' AND cc.`uses_per_coupon`>1 ';
}
}
if (!empty($pListHash['status'])) {
$whereSql .= ' AND cc.`coupon_active`=? ';
$bindVars[] = $pListHash['status'];
}
if (empty($_REQUEST['sort_mode'])) {
$_REQUEST['sort_mode'] = 'coupon_start_date_desc';
}
BitBase::prepGetList($pListHash);
/*
coupon_minimum_order
uses_per_coupon
uses_per_user
restrict_to_products
restrict_to_categories
restrict_to_customers
date_created
date_modified
restrict_to_shipping
restrict_to_quantity
*/
$sql = "SELECT cc.`coupon_id`, cc.`coupon_code`, cc.`coupon_type`, cc.`coupon_amount`, cc.`coupon_start_date`, cc.`coupon_expire_date`, cc.`uses_per_coupon`, cc.`uses_per_user`, cc.`coupon_active`, cc.`admin_note`, ccd.`coupon_name`, ccd.`coupon_description`, MAX( `redeem_date` ) AS `redeemed_first_date`, MIN( `redeem_date` ) AS `redeemed_last_date`, COALESCE( SUM( cot.`orders_value` ), 0 ) AS `redeemed_sum`, COALESCE( COUNT( cot.`orders_value` ), 0 ) AS `redeemed_count`, COALESCE( SUM( cot2.`orders_value` ), 0 ) AS `redeemed_revenue`\n\t\t\t\tFROM " . TABLE_COUPONS . " cc\n\t\t\t\t\tLEFT OUTER JOIN " . TABLE_COUPONS_DESCRIPTION . " ccd ON (cc.`coupon_id`=ccd.`coupon_id` AND ccd.`language_id`=?)\n\t\t\t\t\tLEFT OUTER JOIN " . TABLE_COUPON_REDEEM_TRACK . " ccrt ON (ccrt.`coupon_id`=cc.`coupon_id`)\n\t\t\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "users_users` uu ON (ccrt.`customer_id`=uu.`user_id`)\n\t\t\t\t\tLEFT OUTER JOIN " . TABLE_ORDERS_TOTAL . " cot ON (ccrt.`order_id`=cot.`orders_id` AND cot.`class`='ot_coupon' AND UPPER(cot.`title`) LIKE '%'||UPPER(cc.`coupon_code`)||'%')\n\t\t\t\t\tLEFT OUTER JOIN " . TABLE_ORDERS_TOTAL . " cot2 ON (ccrt.`order_id`=cot2.`orders_id` AND cot2.`class`='ot_total' )\n\t\t\t\t{$whereSql}\n\t\t\t\tGROUP BY cc.`coupon_id`, cc.`coupon_code`, cc.`coupon_type`, cc.`coupon_amount`, cc.`coupon_start_date`, cc.`coupon_expire_date`, cc.`uses_per_coupon`, cc.`uses_per_user`, cc.`coupon_active`, cc.`admin_note`, ccd.`coupon_name`, ccd.`coupon_description`, cc.`coupon_start_date`\n\t\t\t\tORDER BY " . $gBitDb->convertSortmode($_REQUEST['sort_mode']);
if ($rs = $gBitDb->query($sql, $bindVars, $pListHash['max_records'], $pListHash['offset'])) {
while ($row = $rs->fetchRow()) {
$ret[$row['coupon_id']] = $row;
$ret[$row['coupon_id']]['restrictions_count'] = $gBitDb->getOne("SELECT COUNT(ccr.`coupon_id`) AS `restrictions_count` FROM " . TABLE_COUPON_RESTRICT . " ccr WHERE `coupon_id`=?", array($row['coupon_id']));
}
$pListHash['cant'] = $gBitDb->getOne("SELECT COUNT(cc.`coupon_id`) FROM " . TABLE_COUPONS . " cc LEFT OUTER JOIN " . TABLE_COUPONS_DESCRIPTION . " ccd ON (cc.`coupon_id`=ccd.`coupon_id` AND ccd.`language_id`=?) {$whereSql} ", $bindVars);
} else {
$pListHash['cant'] = 0;
}
BitBase::postGetList($pListHash);
if (empty($_REQUEST['listInfo']['sort_mode'])) {
$_REQUEST['listInfo']['sort_mode'] = 'coupon_start_date_asc';
}
return $ret;
}
/**
* @param array $pParamHash
* @return array list of objects of this type in the DB, sorted, filtering and paging dealt with.
*/
public static function getList(&$pParamHash)
{
global $gBitSystem;
$bindVars = array();
$whereSql = '';
if (!isset($pParamHash['sort_mode'])) {
$pParamHash['sort_mode'] = 'country_name_asc';
}
parent::prepGetList($pParamHash);
if (isset($pParamHash['only_active'])) {
$whereSql .= " AND (d.`is_active` LIKE 'y') ";
}
if (isset($pParamHash['find'])) {
$whereSql .= " AND (UPPER(d.`country_name`) LIKE ?) ";
$bindVars[] = '%' . strtoupper($pParamHash['find']) . '%';
}
$query = "\n\t\t\tSELECT `country_id` AS `hash_key`, d.*\n\t\t\tFROM `" . BIT_DB_PREFIX . self::DATA_TBL . "` d\n\t\t\tWHERE TRUE {$whereSql}\n\t\t\tORDER BY " . $gBitSystem->mDb->convertSortmode($pParamHash['sort_mode']);
$result = $gBitSystem->mDb->query($query, $bindVars, $pParamHash['max_records'], $pParamHash['offset']);
$ret = array();
while ($res = $result->fetchRow()) {
$ret[] = $res;
}
$query_cant = "\n\t\t\tSELECT COUNT(d.*)\n\t\t\tFROM `" . BIT_DB_PREFIX . self::DATA_TBL . "` d\n\t\t\tWHERE TRUE {$whereSql}\n\t\t\t";
$pParamHash['cant'] = $gBitSystem->mDb->getOne($query_cant, $bindVars);
parent::postGetList($pParamHash);
return $ret;
}
