PHP sDB::query方法代码示例

 public function screen()
 {
     if (!current_user_can('shopp_promotions')) {
         wp_die(__('You do not have sufficient permissions to access this page.'));
     }
     $table = ShoppDatabaseObject::tablename(ShoppPromo::$table);
     $defaults = array('page' => false, 'status' => false, 'type' => false, 'paged' => 1, 'per_page' => 20, 's' => '');
     $args = array_merge($defaults, $_GET);
     extract($args, EXTR_SKIP);
     $url = add_query_arg(array_merge($_GET, array('page' => $this->page)), admin_url('admin.php'));
     $f = array('action', 'selected', 's');
     $url = remove_query_arg($f, $url);
     $pagenum = absint($paged);
     $start = $per_page * ($pagenum - 1);
     $where = array();
     if (!empty($s)) {
         $where[] = "name LIKE '%{$s}%'";
     }
     if ($status) {
         $datesql = ShoppPromo::activedates();
         switch (strtolower($status)) {
             case 'active':
                 $where[] = "status='enabled' AND {$datesql}";
                 break;
             case 'inactive':
                 $where[] = "status='enabled' AND NOT {$datesql}";
                 break;
             case 'enabled':
                 $where[] = "status='enabled'";
                 break;
             case 'disabled':
                 $where[] = "status='disabled'";
                 break;
         }
     }
     if ($type) {
         switch (strtolower($type)) {
             case 'catalog':
                 $where[] = "target='Catalog'";
                 break;
             case 'cart':
                 $where[] = "target='Cart'";
                 break;
             case 'cartitem':
                 $where[] = "target='Cart Item'";
                 break;
         }
     }
     $select = sDB::select(array('table' => $table, 'columns' => 'SQL_CALC_FOUND_ROWS *', 'where' => $where, 'orderby' => 'created DESC', 'limit' => "{$start},{$per_page}"));
     $Promotions = sDB::query($select, 'array');
     $count = sDB::found();
     $num_pages = ceil($count / $per_page);
     $ListTable = ShoppUI::table_set_pagination($this->id, $count, $num_pages, $per_page);
     $states = array('active' => __('Active', 'Shopp'), 'inactive' => __('Not Active', 'Shopp'), 'enabled' => __('Enabled', 'Shopp'), 'disabled' => __('Disabled', 'Shopp'));
     $types = array('catalog' => __('Catalog Discounts', 'Shopp'), 'cart' => __('Cart Discounts', 'Shopp'), 'cartitem' => __('Cart Item Discounts', 'Shopp'));
     $num_pages = ceil($count / $per_page);
     $page_links = paginate_links(array('base' => add_query_arg('pagenum', '%#%'), 'format' => '', 'total' => $num_pages, 'current' => $pagenum));
     include $this->ui('discounts.php');
 }

 public function delete()
 {
     if (empty($this->id)) {
         return;
     }
     $price = $this->id;
     parent::delete();
     // clean up meta entries for deleted price
     $metatable = ShoppDatabaseObject::tablename('meta');
     $query = "DELETE FROM {$metatable} WHERE context='price' and parent={$price}";
     sDB::query($query);
 }

 public function screen()
 {
     $Shopp = Shopp::object();
     if (!current_user_can('shopp_settings_checkout')) {
         wp_die(__('You do not have sufficient permissions to access this page.'));
     }
     $purchasetable = ShoppDatabaseObject::tablename(ShoppPurchase::$table);
     $next = sDB::query("SELECT IF ((MAX(id)) > 0,(MAX(id)+1),1) AS id FROM {$purchasetable} LIMIT 1");
     $next_setting = shopp_setting('next_order_id');
     if ($next->id > $next_setting) {
         $next_setting = $next->id;
     }
     $term_recount = false;
     if (!empty($_POST['save'])) {
         check_admin_referer('shopp-setup-management');
         $next_order_id = $_POST['settings']['next_order_id'] = intval($_POST['settings']['next_order_id']);
         if ($next_order_id >= $next->id) {
             if (sDB::query("ALTER TABLE {$purchasetable} AUTO_INCREMENT=" . sDB::escape($next_order_id))) {
                 $next_setting = $next_order_id;
             }
         }
         $_POST['settings']['order_shipfee'] = Shopp::floatval($_POST['settings']['order_shipfee']);
         // Recount terms when this setting changes
         if (isset($_POST['settings']['inventory']) && $_POST['settings']['inventory'] != shopp_setting('inventory')) {
             $term_recount = true;
         }
         shopp_set_formsettings();
         $this->notice(Shopp::__('Management settings saved.'), 'notice', 20);
     }
     if ($term_recount) {
         $taxonomy = ProductCategory::$taxon;
         $terms = get_terms($taxonomy, array('hide_empty' => 0, 'fields' => 'ids'));
         if (!empty($terms)) {
             wp_update_term_count_now($terms, $taxonomy);
         }
     }
     $states = array(__('Map the label to an order state:', 'Shopp') => array_merge(array('' => ''), Lookup::txnstatus_labels()));
     $statusLabels = shopp_setting('order_status');
     $statesLabels = shopp_setting('order_states');
     $reasonLabels = shopp_setting('cancel_reasons');
     if (empty($reasonLabels)) {
         $reasonLabels = array(__('Not as described or expected', 'Shopp'), __('Wrong size', 'Shopp'), __('Found better prices elsewhere', 'Shopp'), __('Product is missing parts', 'Shopp'), __('Product is defective or damaaged', 'Shopp'), __('Took too long to deliver', 'Shopp'), __('Item out of stock', 'Shopp'), __('Customer request to cancel', 'Shopp'), __('Item discontinued', 'Shopp'), __('Other reason', 'Shopp'));
     }
     $promolimit = array('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '15', '20', '25');
     $lowstock = shopp_setting('lowstock_level');
     if (empty($lowstock)) {
         $lowstock = 0;
     }
     include $this->ui('management.php');
 }

/**
 * shopp_orders - get a list of purchases
 *
 * @api
 * @since 1.2
 *
 * @param mixed $from (optional) mktime or SQL datetime, get purchases after this date/time.
 * @param mixed $to (optional) mktime or SQL datetime, get purchased before this date/time.
 * @param bool $items (optional default:true) load purchased items into the records, slightly slower operation
 * @param array $customers (optional) list of int customer ids to limit the purchases to.  All customers by default.
 * @param int $limit (optional default:false) maximimum number of results to get, false for no limit
 * @param string $order (optional default:DESC) DESC or ASC, for sorting in ascending or descending order.
 * @param string $orderby (optional) The column used to sort records
 * @param bool $paidonly (optional) Restrict to orders where payment has been completed
 * @param bool $downloads (optional) Restrict to orders that have downloads
 * @return array of Purchase objects
 **/
function shopp_orders($from = false, $to = false, $items = true, array $customers = array(), $limit = false, $order = 'DESC', $orderby = 'id', $paidonly = false, $downloads = false)
{
    $pt = ShoppDatabaseObject::tablename(ShoppPurchase::$table);
    $pd = ShoppDatabaseObject::tablename(ShoppPurchased::$table);
    $op = '<';
    $where = array();
    $dateregex = '/^([0-9]{2,4})-([0-1][0-9])-([0-3][0-9]) (?:([0-2][0-9]):([0-5][0-9]):([0-5][0-9]))?$/';
    foreach (array($from, $to) as $datetime) {
        if (!$datetime) {
            continue;
        }
        if (1 == preg_match($dateregex, $datetime)) {
            $where[] = "'{$datetime}' {$op} p.created";
        } else {
            if (is_int($datetime)) {
                $where[] = "FROM_UNIXTIME({$datetime}) {$op} p.created";
            }
        }
        $op = '>=';
    }
    if (!empty($customers)) {
        $set = sDB::escape(implode(',', $customers));
        $where[] = "0 < FIND_IN_SET(p.customer,'" . $set . "')";
    }
    if ($paidonly) {
        $where[] = "p.txnstatus='captured'";
    }
    if ($items && $downloads) {
        $where[] = " pd.download > 0";
    }
    $where = empty($where) ? '' : 'WHERE ' . implode(' AND ', $where);
    if ((int) $limit > 0) {
        $limit = " LIMIT {$limit}";
    } else {
        $limit = '';
    }
    if (!in_array(strtolower($orderby), array('id', 'created', 'modified'))) {
        $orderby = 'id';
    }
    if ($items) {
        $query = "SELECT pd.* FROM {$pd} AS pd INNER JOIN {$pt} AS p ON pd.purchase = p.id {$where} " . $limit;
        $purchased = sDB::query($query, 'array', '_shopp_order_purchased');
        $orders = sDB::query("SELECT * FROM {$pt} WHERE FIND_IN_SET(id,'" . join(',', array_keys($purchased)) . "') ORDER BY {$orderby} " . ('DESC' == $order ? 'DESC' : 'ASC'), 'array', '_shopp_order_purchase', $purchased);
    } else {
        $query = "SELECT * FROM {$pt} AS p {$where} ORDER BY {$orderby} " . ('DESC' == $order ? 'DESC' : 'ASC') . $limit;
        $orders = sDB::query($query, 'array', '_shopp_order_purchase');
    }
    return $orders;
}

 private function __construct()
 {
     $ImageSetting = new ShoppImageSetting();
     $table = $ImageSetting->_table;
     $where = array("type='{$ImageSetting->type}'", "context='{$ImageSetting->context}'");
     $options = compact('table', 'where');
     $query = sDB::select($options);
     $this->populate(sDB::query($query, 'array', array($ImageSetting, 'loader'), false, 'name'));
     $this->found = sDB::found();
 }

 function load()
 {
     $args = func_get_args();
     if (empty($args[0])) {
         return false;
     }
     if (!is_array($args[0])) {
         return false;
     }
     $where = "";
     foreach ($args[0] as $key => $id) {
         $where .= ($where == "" ? "" : " AND ") . "{$key}='" . sDB::escape($id) . "'";
     }
     $r = sDB::query("SELECT * FROM {$this->_table} WHERE {$where}", 'array');
     foreach ($r as $row) {
         $meta = new ShoppMetaObject();
         $meta->populate($row, '', array());
         $this->meta[$meta->id] = $meta;
         $this->named[$meta->name] =& $this->meta[$meta->id];
     }
     if (isset($row) && count($row) == 0) {
         $this->_loaded = false;
     }
     $this->_loaded = true;
     return $this->_loaded;
 }

 /**
  * Load the binary data of a specified resource
  *
  * @author Jonathan Davis
  * @since 1.1
  *
  * @param string $uri The URI for the resource
  * @return string Binary data
  **/
 public function load($uri)
 {
     if (!$uri) {
         return false;
     }
     $file = sDB::query("SELECT * FROM {$this->_table} WHERE {$this->_key}='{$uri}' LIMIT 1");
     if (empty($file)) {
         shopp_error(Shopp::__('The requested asset could not be loaded from the database.'), SHOPP_ADMIN_ERR);
         return false;
     }
     return $file->data;
 }

 /**
  * Load the report data
  *
  * @author Jonathan Davis
  * @since 1.3
  *
  * @return void
  **/
 public function load()
 {
     extract($this->options);
     // Map out time period based reports with index matching keys and period values
     if ($this->periods) {
         $this->timereport($starts, $ends, $scale);
     }
     $this->setup();
     $query = $this->query();
     if (empty($query)) {
         return;
     }
     $loaded = sDB::query($query, 'array', array($this, 'process'));
     if ($this->periods && $this->Chart) {
         foreach ($this->data as $index => &$record) {
             if (count(get_object_vars($record)) <= 1) {
                 foreach ($this->columns as $column) {
                     $record->{$column} = null;
                 }
             }
             foreach ($this->chartseries as $series => $column) {
                 $data = isset($record->{$column}) ? $record->{$column} : 0;
                 $this->chartdata($series, $record->period, $data);
             }
         }
     } else {
         $this->data = $loaded;
         $this->total = count($loaded);
     }
 }

 /**
  * Load active promotions
  *
  * @author Jonathan Davis
  * @since 1.3
  *
  * @return array List of loaded ShoppOrderPromo objects
  **/
 public function load()
 {
     if ($this->loaded) {
         return;
     }
     // Don't load twice in one request
     $table = ShoppDatabaseObject::tablename(ShoppPromo::$table);
     $where = array("status='enabled'", ShoppPromo::activedates(), "target IN ('" . join("','", self::$targets) . "')");
     $orderby = 'target DESC';
     $queryargs = compact('table', 'where', 'orderby');
     $query = sDB::select($queryargs);
     $loaded = sDB::query($query, 'array', array('ShoppPromotions', 'loader'));
     if (!$loaded || 0 == count($loaded)) {
         return;
     }
     $this->populate($loaded);
     $this->loaded = true;
 }

/**
 * Destroy the entire product search index
 *
 * @api
 * @since 1.3
 *
 * @return void
 **/
function shopp_empty_search_index()
{
    $index_table = ShoppDatabaseObject::tablename(ContentIndex::$table);
    if (sDB::query("DELETE FROM {$index_table}")) {
        return true;
    }
    return false;
}

 /**
  * Verify the loaded record actually exists in the database
  *
  * @author Jonathan Davis
  * @since 1.1
  *
  * @return boolean
  **/
 public function exists($verify = false)
 {
     $key = $this->_key;
     if (empty($this->{$key})) {
         return false;
     }
     if ($verify) {
         $id = $this->{$key};
         $exists = sDB::query("SELECT id FROM {$this->_table} WHERE {$key}='{$id}' LIMIT 1", 'auto', 'col', 'id');
         return !empty($exists);
     }
     return true;
 }

 /**
  * Interface processor for the customer list screen
  *
  * Handles processing customer list actions and displaying the
  * customer list screen
  *
  * @author Jonathan Davis
  * @return void
  **/
 function memeberships()
 {
     $Shopp = Shopp::object();
     $defaults = array('page' => false, 'deleting' => false, 'delete' => false, 'pagenum' => 1, 'per_page' => 20, 's' => '');
     $args = array_merge($defaults, $_GET);
     extract($args, EXTR_SKIP);
     if ($page == $this->Admin->pagename('memberships') && !empty($deleting) && !empty($delete) && is_array($delete)) {
         foreach ($delete as $deletion) {
             $MemberPlan = new MemberPlan($deletion);
             $MemberPlan->delete();
         }
     }
     if (!empty($_POST['save'])) {
         check_admin_referer('shopp-save-membership');
         if ($_POST['id'] != "new") {
             $MemberPlan = new MemberPlan($_POST['id']);
         } else {
             $MemberPlan = new MemberPlan();
         }
         $MemberPlan->updates($_POST);
         $MemberPlan->save();
         $MemberPlan->load_stages();
         $stages = array_keys($MemberPlan->stages);
         // Process updates
         foreach ($_POST['stages'] as $i => $stage) {
             if (empty($stage['id'])) {
                 $Stage = new MemberStage($MemberPlan->id);
                 $stage['parent'] = $MemberPlan->id;
             } else {
                 $Stage = new MemberStage($MemberPlan->id, $stage['id']);
             }
             $Stage->updates($stage);
             $Stage->sortorder = $i;
             $Stage->save();
             $Stage->content = array();
             $stage_updates[] = $Stage->id;
             // If the stage data did not save, go to the next stage record
             if (empty($Stage->id)) {
                 continue;
             }
             foreach ($stage['rules'] as $type => $rules) {
                 foreach ($rules as $rule) {
                     $AccessRule = new MemberAccess($Stage->id, $type, $rule['access']);
                     if (empty($AccessRule->id)) {
                         $AccessRule->save();
                     }
                     // If access rule applies to all content, skip content cataloging
                     if (strpos($AccessRule->value, '-all') !== false) {
                         continue;
                     }
                     // Catalog content access rules for this access taxonomy
                     foreach ($rule['content'] as $id => $name) {
                         $CatalogEntry = new MemberContent($id, $AccessRule->id, $Stage->id);
                         if (empty($CatalogEntry->id)) {
                             $CatalogEntry->save();
                         }
                         $Stage->content[$AccessRule->id][$id] = $name;
                     }
                     // endforeach $rule['content']
                 }
                 // endforeach $rules
             }
             // endforeach $stage['rules']
             $Stage->save();
             // Secondary save for specific content rules
         }
         // endforeach $_POST['stages']
     }
     $stageids = array_diff($stages, $stage_updates);
     if (!empty($stageids)) {
         $stagelist = join(',', $stageids);
         // Delete Catalog entries
         $ContentRemoval = new MemberContent();
         sDB::query("DELETE FROM {$ContentRemoval->_table} WHERE 0 < FIND_IN_SET(parent,'{$stagelist}')");
         // Delete Access taxonomies
         $AccessRemoval = new MemberAccess();
         sDB::query("DELETE FROM {$AccessRemoval->_table} WHERE 0 < FIND_IN_SET(parent,'{$stagelist}')");
         // Remove old stages
         $StageRemoval = new MemberStage();
         sDB::query("DELETE FROM {$StageRemoval->_table} WHERE 0 < FIND_IN_SET(id,'{$stagelist}')");
     }
     $pagenum = absint($pagenum);
     if (empty($pagenum)) {
         $pagenum = 1;
     }
     if (!$per_page || $per_page < 0) {
         $per_page = 20;
     }
     $index = $per_page * ($pagenum - 1);
     if (!empty($start)) {
         $startdate = $start;
//.........这里部分代码省略.........

 public function query($request = array())
 {
     if (empty($request)) {
         $request = $_GET;
     }
     if (!empty($request['start'])) {
         list($month, $day, $year) = explode("/", $request['start']);
         $starts = mktime(0, 0, 0, $month, $day, $year);
     }
     if (!empty($request['end'])) {
         list($month, $day, $year) = explode("/", $request['end']);
         $ends = mktime(0, 0, 0, $month, $day, $year);
     }
     $where = "WHERE c.id IS NOT NULL ";
     if (isset($request['s']) && !empty($request['s'])) {
         $where .= " AND (id='{$request['s']}' OR firstname LIKE '%{$request['s']}%' OR lastname LIKE '%{$request['s']}%' OR CONCAT(firstname,' ',lastname) LIKE '%{$request['s']}%' OR transactionid LIKE '%{$request['s']}%')";
     }
     if (!empty($request['start']) && !empty($request['end'])) {
         $where .= " AND  (UNIX_TIMESTAMP(c.created) >= {$starts} AND UNIX_TIMESTAMP(c.created) <= {$ends})";
     }
     $customer_table = ShoppDatabaseObject::tablename(Customer::$table);
     $billing_table = ShoppDatabaseObject::tablename(BillingAddress::$table);
     $shipping_table = ShoppDatabaseObject::tablename(ShippingAddress::$table);
     $offset = $this->set * $this->limit;
     $c = 0;
     $columns = array();
     foreach ($this->selected as $column) {
         $columns[] = "{$column} AS col" . $c++;
     }
     $query = "SELECT " . join(",", $columns) . " FROM {$customer_table} AS c LEFT JOIN {$billing_table} AS b ON c.id=b.customer LEFT JOIN {$shipping_table} AS s ON c.id=s.customer {$where} GROUP BY c.id ORDER BY c.created ASC LIMIT {$offset},{$this->limit}";
     $this->data = sDB::query($query, 'array');
 }

 /**
  * Interface processor for the product editor
  *
  * @author Jonathan Davis
  * @return void
  **/
 public function screen()
 {
     $Shopp = Shopp::object();
     if (!current_user_can('shopp_products')) {
         wp_die(__('You do not have sufficient permissions to access this page.'));
     }
     if (empty($Shopp->Product)) {
         $Product = new ShoppProduct();
         $Product->status = "publish";
     } else {
         $Product = $Shopp->Product;
     }
     $Product->slug = apply_filters('editable_slug', $Product->slug);
     $permalink = trailingslashit(Shopp::url());
     $Price = new ShoppPrice();
     $priceTypes = ShoppPrice::types();
     $billPeriods = ShoppPrice::periods();
     $workflows = array('continue' => Shopp::__('Continue Editing'), 'close' => Shopp::__('Products Manager'), 'new' => Shopp::__('New Product'), 'next' => Shopp::__('Edit Next'), 'previous' => Shopp::__('Edit Previous'));
     $taglist = array();
     foreach ($Product->tags as $tag) {
         $taglist[] = $tag->name;
     }
     if ($Product->id && !empty($Product->images)) {
         $ids = join(',', array_keys($Product->images));
         $CoverImage = reset($Product->images);
         $image_table = $CoverImage->_table;
         $Product->cropped = sDB::query("SELECT * FROM {$image_table} WHERE context='image' AND type='image' AND '2'=SUBSTRING_INDEX(SUBSTRING_INDEX(name,'_',4),'_',-1) AND parent IN ({$ids})", 'array', 'index', 'parent');
     }
     $shiprates = shopp_setting('shipping_rates');
     if (!empty($shiprates)) {
         ksort($shiprates);
     }
     $uploader = shopp_setting('uploader_pref');
     if (!$uploader) {
         $uploader = 'flash';
     }
     $process = empty($Product->id) ? 'new' : $Product->id;
     $_POST['action'] = add_query_arg(array_merge($_GET, array('page' => ShoppAdmin::pagename('products'))), admin_url('admin.php'));
     $post_type = ShoppProduct::posttype();
     // Re-index menu options to maintain order in JS #2930
     if (isset($Product->options['v']) || isset($Product->options['a'])) {
         $options = array_keys($Product->options);
         foreach ($options as $type) {
             foreach ($Product->options[$type] as $id => $menu) {
                 $Product->options[$type][$type . $id] = $menu;
                 $Product->options[$type][$type . $id]['options'] = array_values($menu['options']);
                 unset($Product->options[$type][$id]);
             }
         }
     } else {
         foreach ($Product->options as &$menu) {
             $menu['options'] = array_values($menu['options']);
         }
     }
     do_action('add_meta_boxes', ShoppProduct::$posttype, $Product);
     do_action('add_meta_boxes_' . ShoppProduct::$posttype, $Product);
     do_action('do_meta_boxes', ShoppProduct::$posttype, 'normal', $Product);
     do_action('do_meta_boxes', ShoppProduct::$posttype, 'advanced', $Product);
     do_action('do_meta_boxes', ShoppProduct::$posttype, 'side', $Product);
     include $this->ui('editor.php');
 }

 /**
  * Provides markup for a unordered list widget of recent shoppers
  *
  * @api `shopp('storefront.recent-shoppers')`
  * @since 1.1
  *
  * @param string          $result  The output
  * @param array           $options The options
  * - **abbr**: `firstname` (firstname,lastname) Choose which name to abbreviate to maintain customer privacy
  * - **city**: `on` (on,off) Include the customer's city name
  * - **state**: `on` (on,off) Include the customer's state abbreviation
  * - **avatar**: `on` (on,off) Include the customer's avatar
  * - **size**: `48` Set the size of the customer avatar shown
  * - **show**: `5` Set the limit of the number of recent shoppers to show
  * @param ShoppStorefront $O       The working object
  * @return string The markup for the widget
  **/
 public static function recent_shoppers($result, $options, $O)
 {
     $defaults = array('abbr' => 'firstname', 'city' => true, 'state' => true, 'avatar' => true, 'size' => 48, 'show' => 5);
     $options = array_merge($defaults, $options);
     extract($options);
     $pt = ShoppDatabaseObject::tablename(ShoppPurchase::$table);
     $shoppers = sDB::query("SELECT firstname,lastname,email,city,state FROM {$pt} AS pt GROUP BY customer ORDER BY created DESC LIMIT {$show}", 'array');
     if (empty($shoppers)) {
         return '';
     }
     $_ = array();
     $_[] = '<ul>';
     foreach ($shoppers as $shopper) {
         if ('' == $shopper->firstname . $shopper->lastname) {
             continue;
         }
         if ('lastname' == $abbr) {
             $name = "{$shopper->firstname} " . $shopper->lastname[0] . ".";
         } else {
             $name = $shopper->firstname[0] . ". {$shopper->lastname}";
         }
         $img = '';
         if ($avatar) {
             $img = get_avatar($shopper->email, $size, '', $name);
         }
         $loc = '';
         if ($state || $province) {
             $loc = $shopper->state;
         }
         if ($city) {
             $loc = "{$shopper->city}, {$loc}";
         }
         $_[] = "<li><div>{$img}</div>{$name} <em>{$loc}</em></li>";
     }
     $_[] = '</ul>';
     return join('', $_);
 }