PHP BitBase::prepGetList方法代码示例

 public static function prepGetList(&$pListHash)
 {
     if (!empty($pListHash['period'])) {
         $pListHash['period_format'] = BitDb::getPeriodFormat($pListHash['period']);
     }
     parent::prepGetList($pListHash);
 }

function feed_get_status($pListHash)
{
    global $gBitDb;
    $whereSql = '';
    $bindVars = array();
    $statuses = array();
    BitBase::prepGetList($pListHash);
    if (!empty($pListHash['user_id'])) {
        $whereSql = " WHERE lal.user_id = ? ";
        $bindVars[] = $pListHash['user_id'];
    } else {
        $whereSql = "WHERE 1=1 ";
    }
    $query = "SELECT lal.content_id, lal.user_id, lal.log_message, MAX(lal.last_modified) AS last_modified, lc.data, uu.login, uu.real_name, uu.email\n\t\t\t  FROM liberty_action_log lal\n\t\t\t  INNER JOIN liberty_content lc ON (lc.content_id=lal.content_id)\n\t\t\t  INNER JOIN liberty_comments lcs ON (lcs.content_id = lc.content_id)\n\t\t\t  INNER JOIN users_users uu ON (uu.user_id=lal.user_id)\n\t\t\t  {$whereSql} AND lc.content_type_guid = 'feedstatus'\n\t\t\t  GROUP BY lal.content_id, lal.user_id, uu.login, uu.real_name, uu.email, lal.log_message,lc.data\n\t\t\t  ORDER BY MAX(lal.last_modified) DESC";
    $res = $gBitDb->query($query, $bindVars, $pListHash['max_records']);
    $user = new BitUser($pListHash['user_id']);
    $user->load();
    while ($status = $res->fetchRow()) {
        $avatarUrl = $user->getThumbnailUrl();
        if (empty($avatarUrl)) {
            $avatarUrl = USERS_PKG_URI . "icons/silhouette.png";
        }
        $status['feed_icon_url'] = $avatarUrl;
        $comment = new LibertyComment(NULL, $status['content_id']);
        $replies = $comment->getComments($status['content_id'], null, null, 'commentDate_asc');
        $status['replies'] = $replies;
        foreach ($status['replies'] as &$reply) {
            $replyUser = new BitUser($reply['user_id']);
            $replyUser->load();
            $replyAvatarUrl = $replyUser->getThumbnailUrl();
            if (empty($replyAvatarUrl)) {
                $replyAvatarUrl = USERS_PKG_URI . "icons/silhouette.png";
            }
            $reply['feed_icon_url'] = $replyAvatarUrl;
        }
        //after loading up the thumbnails in the prior array layout for ease, we break up the array to split long comment threads
        $MAX_SHOWN_REPLIES = 3;
        if (count($status['replies']) > $MAX_SHOWN_REPLIES) {
            $maxIteration = count($status['replies']) - $MAX_SHOWN_REPLIES;
            $i = 0;
            foreach ($status['replies'] as $excess) {
                if ($i < $maxIteration) {
                    $status['replies_excess'][$excess['content_id']] = $excess;
                    //use content_id to index for consistency with normal replies array
                    unset($status['replies'][$excess['content_id']]);
                    //remove from the normal replies array
                } else {
                    break;
                }
                $i++;
            }
        }
        $statuses[] = $status;
    }
    return $statuses;
}

 /**
  * 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;
 }

 /**
  * getList
  *
  * @param array $pListHash
  * @access public
  * @return TRUE on success, FALSE on failure - mErrors will contain reason for failure
  */
 function getList(&$pListHash)
 {
     global $gBitSystem, $gBitUser;
     BitBase::prepGetList($pListHash);
     if (!empty($pListHash['find'])) {
         $findesc = '%' . $pListHash['find'] . '%';
         $mid = " (`parent_id` is null or `parent_id`=0) and (lc.`title` like ?)";
         $bindVars = array($findesc);
     } else {
         $mid = " (`parent_id` is null or `parent_id`=0) ";
         $bindVars = array();
     }
     if (@$this->verifyId($pListHash['user_id'])) {
         $mid .= " AND lc.`user_id` = ? ";
         array_push($bindVars, $pListHash['user_id']);
     }
     if (empty($pListHash['sort_mode'])) {
         $pListHash['sort_mode'] = 'last_modified_desc';
     }
     if (!empty($pListHash['content_type_guid'])) {
         $mid .= " AND lc.`content_type_guid`=? ";
         array_push($bindVars, $pListHash['content_type_guid']);
     }
     $query = "SELECT ls.`structure_id`, ls.`parent_id`, ls.`content_id`, `page_alias`, `pos`, lc.`title`, `data`, `last_modified`, lc.`modifier_user_id`, `ip`, lc.`user_id` AS `creator_user_id`, uu.`login` AS `creator_user`, uu.`real_name` , uu.`email`\n\t\t\t\t  FROM `" . BIT_DB_PREFIX . "liberty_structures` ls INNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON ( ls.`content_id` = lc.`content_id` ) INNER JOIN `" . BIT_DB_PREFIX . "users_users` uu ON ( lc.`user_id` = uu.`user_id` )\n\t\t\t\t  WHERE {$mid}\n\t\t\t\t  ORDER BY " . $this->mDb->convertSortmode($pListHash['sort_mode']);
     $query_cant = "SELECT count(*)\n\t\t\t\t\t   FROM `" . BIT_DB_PREFIX . "liberty_structures` ls INNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON ( ls.`content_id` = lc.`content_id` )\n\t\t\t\t\t   WHERE {$mid}";
     $result = $this->mDb->query($query, $bindVars, $pListHash['max_records'], $pListHash['offset']);
     $cant = $this->mDb->getOne($query_cant, $bindVars);
     $ret = array();
     while ($res = $result->fetchRow()) {
         if ($gBitSystem->isPackageActive('bithelp') && file_exists(BITHELP_PKG_PATH . $res['title'] . '/index.html')) {
             $res['webhelp'] = 'y';
         } else {
             $res['webhelp'] = 'n';
         }
         $ret[] = $res;
     }
     $retval = array();
     $retval["data"] = $ret;
     $retval["cant"] = $cant;
     return $retval;
 }

 /**
  * 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;
     if (empty($pParamHash["sort_mode"])) {
         $pListHash['sort_mode'] = 'created_desc';
     }
     BitBase::prepGetList($pListHash);
     $bindVars = array();
     $joinSql = '';
     $mid = '';
     if (@BitBase::verifyId($pListHash['nl_id'])) {
         $mid .= ' AND n.nl_id=? ';
         $bindVars[] = $pListHash['nl_id'];
     }
     if (!empty($pListHash['find'])) {
         $findesc = '%' . $pListHash['find'] . '%';
         $mid .= " AND (`name` like ? or `description` like ?)";
         $bindVars[] = $findesc;
         $bindVars[] = $findesc;
     }
     if (!empty($pListHash['registration_optin'])) {
         $joinSql = " INNER JOIN `" . BIT_DB_PREFIX . "liberty_content_prefs` lcp ON (lcp.`content_id`=n.`content_id` AND lcp.`pref_name`='registration_optin' AND lcp.`pref_value`='y') ";
     }
     $query = "SELECT *\n\t\t\t\t  FROM `" . BIT_DB_PREFIX . "newsletters` n INNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON( n.`content_id`=lc.`content_id`)\n\t\t\t\t\t{$joinSql}\n\t\t\t\t  WHERE n.`content_id`=lc.`content_id` {$mid}\n\t\t\t\t  ORDER BY " . $gBitDb->convertSortmode($pListHash['sort_mode']);
     $result = $gBitDb->query($query, $bindVars, $pListHash['max_records'], $pListHash['offset']);
     $query_cant = "select count(*) from `" . BIT_DB_PREFIX . "newsletters` {$mid}";
     $ret = array();
     while ($res = $result->fetchRow()) {
         $res['display_url'] = BitNewsletter::getDisplayUrlFromHash($res);
         $res["confirmed"] = $gBitDb->getOne("SELECT COUNT(*) FROM `" . BIT_DB_PREFIX . "mail_subscriptions` WHERE `unsubscribe_date` IS NULL and `content_id`=?", array((int) $res['content_id']));
         $res["unsub_count"] = $gBitDb->getOne("SELECT COUNT(*) FROM `" . BIT_DB_PREFIX . "mail_subscriptions` WHERE `content_id`=?", array((int) $res['content_id']));
         $ret[$res['content_id']] = $res;
     }
     return $ret;
 }

 function getRevenueByReferer(&$pParamHash)
 {
     $ret = array();
     $bindVars = array();
     $whereSql = '';
     if (empty($pParamHash['sort_mode'])) {
         $pParamHash['sort_mode'] = 'revenue_desc';
     }
     BitBase::prepGetList($pParamHash);
     if (!empty($pParamHash['period']) && !empty($pParamHash['timeframe'])) {
         $whereSql .= ' AND ' . $this->mDb->SQLDate($pParamHash['period'], '`date_purchased`') . ' = ?';
         $bindVars[] = $pParamHash['timeframe'];
         if (!empty($pParamHash['new_reg'])) {
             $whereSql .= ' AND ' . $this->mDb->SQLDate($pParamHash['period'], $this->mDb->SQLIntToTimestamp('uu.`registration_date`')) . ' = ?';
             $bindVars[] = $pParamHash['timeframe'];
         }
     }
     if (!empty($pParamHash['delivery_country'])) {
         $whereSql .= ' AND co.`delivery_country`=? ';
         $bindVars[] = $pParamHash['delivery_country'];
     }
     if (!empty($pParamHash['include'])) {
         $includes = explode(',', $pParamHash['include']);
         foreach ($includes as $in) {
             $whereSql .= " AND LOWER( `referer_url` ) LIKE ? ";
             $bindVars[] = '%' . strtolower(trim(urlencode($in))) . '%';
         }
         /*
         			// OR the terms
         
         			$max = count( $includes );
         			for( $i = 0; $i < $max; $i++ ) {
         				$whereSql .= " LOWER( `referer_url` ) LIKE ? ";
         				$bindVars[] = '%'.strtolower( trim( urlencode( $includes[$i] ) ) ).'%';
         				if( $i <  $max - 1 ) {
         					$whereSql .= ' OR ';
         				}
         			}
         			$whereSql .= ' ) ';
         */
     }
     if (!empty($pParamHash['exclude'])) {
         $excludes = explode(',', $pParamHash['exclude']);
         foreach ($excludes as $ex) {
             $whereSql .= " AND LOWER( `referer_url` ) NOT LIKE ? ";
             $bindVars[] = '%' . strtolower(trim(urlencode($ex))) . '%';
         }
     }
     $sql = "SELECT sru.`referer_url`, co.`customers_id`, SUM(cop.`products_quantity` * cop.`products_price`) AS `total_revenue`, SUM(cop.`products_quantity`) AS `total_units`, COUNT(co.`orders_id`) AS `total_orders`\n\t\t\t\tFROM " . TABLE_ORDERS . " co\n\t\t\t\t\tINNER JOIN " . TABLE_ORDERS_PRODUCTS . " cop ON(co.`orders_id`=cop.`orders_id`)\n\t\t\t\t\tINNER JOIN " . TABLE_PRODUCTS . " cp ON(cp.`products_id`=cop.`products_id`)\n\t\t\t\t\tINNER JOIN " . TABLE_PRODUCT_TYPES . " cpt ON(cpt.`type_id`=cp.`products_type`)\n\t\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "users_users` uu ON (uu.`user_id`=co.`customers_id`) \n\t\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "stats_referer_users_map` srum ON (srum.`user_id`=co.`customers_id`) \n\t\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "stats_referer_urls` sru ON (sru.`referer_url_id`=srum.`referer_url_id`) \n\t\t\t\tWHERE co.`orders_status` > 0 {$whereSql}\n\t\t\t\tGROUP BY sru.`referer_url`, co.`customers_id`\n\t\t\t\tORDER BY SUM(cop.`products_quantity` * cop.`products_price`)";
     $rows = $this->mDb->getAll($sql, $bindVars);
     foreach ($rows as $row) {
         if ($urlHash = parse_url($row['referer_url'])) {
             @($ret['hosts'][$urlHash['host']]['revenue'] += $row['total_revenue']);
             @($ret['hosts'][$urlHash['host']]['units'] += $row['total_units']);
             @($ret['hosts'][$urlHash['host']]['orders'] += $row['total_orders']);
             @$ret['hosts'][$urlHash['host']]['customers']++;
             if (!empty($urlHash['query']) && ($searchTerm = strpos($urlHash['query'], 'q=') !== FALSE ? 'q' : (strpos($urlHash['query'], 'p=') !== FALSE ? 'p' : FALSE))) {
                 parse_str($urlHash['query']);
                 if (!empty(${$searchTerm})) {
                     $urlKey = ${$searchTerm};
                 }
             } else {
                 $urlKey = $row['referer_url'];
                 @$ret['hosts'][$urlHash['host']]['referer_urls'][$row['referer_url']]++;
             }
             @($ret['hosts'][$urlHash['host']]['refs'][$urlKey]['revenue'] += $row['total_revenue']);
             @($ret['hosts'][$urlHash['host']]['refs'][$urlKey]['units'] += $row['total_units']);
             @($ret['hosts'][$urlHash['host']]['refs'][$urlKey]['orders'] += $row['total_orders']);
             @$ret['hosts'][$urlHash['host']]['refs'][$urlKey]['customers']++;
             @($ret['totals']['revenue'] += $row['total_revenue']);
             @($ret['totals']['units'] += $row['total_units']);
             @($ret['totals']['orders'] += $row['total_orders']);
             @$ret['totals']['customers']++;
         }
     }
     if (!empty($ret['hosts'])) {
         $sortFunction = 'commerce_statistics_referer_sort_' . $pParamHash['sort_mode'];
         if (function_exists($sortFunction)) {
             uasort($ret['hosts'], $sortFunction);
             foreach (array_keys($ret['hosts']) as $key) {
                 uasort($ret['hosts'][$key]['refs'], $sortFunction);
             }
         }
     }
     return $ret;
 }

 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;
 }

        if ($gCoupon->store($_REQUEST)) {
            $feedback['success'] = tra('Coupon Saved:') . ' ' . $gCoupon->getField('coupon_code');
        } else {
            $feedback = $gCoupon->mFeedback;
            $getAction = 'edit';
        }
        break;
}
switch ($getAction) {
    case 'report':
        // pagination sucks for pages like this
        $_REQUEST['max_records'] = 1000;
        if (empty($_REQUEST['sort_mode'])) {
            $_REQUEST['sort_mode'] = 'redeem_date_desc';
        }
        BitBase::prepGetList($_REQUEST);
        $joinSql = '';
        $selectSql = '';
        if ($gBitSystem->isPackageActive('stats')) {
            $joinSql .= " LEFT OUTER JOIN `" . BIT_DB_PREFIX . "stats_referer_users_map` srum ON (srum.`user_id`=uu.`user_id`) \n\t\t\t\t\t  LEFT OUTER JOIN `" . BIT_DB_PREFIX . "stats_referer_urls` sru ON (srum.`referer_url_id`=sru.`referer_url_id`)";
            $selectSql .= ", sru.`referer_url`";
        }
        $sql = "SELECT ccrt.`unique_id`, ccrt.`order_id`, ccrt.`redeem_ip`, ccrt.`redeem_date`, cot.`orders_value` AS `coupon_value`, co.`order_total`, uu.`user_id`, uu.`login`, uu.`real_name`, uu.`email` {$selectSql}\n\t\t\tFROM " . TABLE_COUPON_REDEEM_TRACK . " ccrt\n\t\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "users_users` uu ON (ccrt.`customer_id`=uu.`user_id`)\n\t\t\t\tLEFT OUTER JOIN " . TABLE_ORDERS . " co ON (ccrt.`order_id`=co.`orders_id`)\n\t\t\t\tLEFT OUTER JOIN " . TABLE_COUPONS . " cc ON (ccrt.`coupon_id`=cc.`coupon_id`)\n\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{$joinSql}\n\t\t\tWHERE ccrt.`coupon_id` = ?\n\t\t\tORDER BY " . $gBitDb->convertSortmode($_REQUEST['sort_mode']);
        $bindVars = array($_REQUEST['cid']);
        if (empty($_REQUEST['page'])) {
            $_REQUEST['page'] = 0;
        }
        $_REQUEST['offset'] = $_REQUEST['page'] ? ($_REQUEST['page'] - 1) * $_REQUEST['max_records'] : 0;
        $summary = array();
        if ($rs = $gBitDb->query($sql, $bindVars, $_REQUEST['max_records'], $_REQUEST['offset'])) {
            while ($row = $rs->fetchRow()) {

 /**
  * @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;
 }