PHP DB::Query方法代码示例

 protected function _execute()
 {
     // Clean up id columns to be bigint(20)
     $this->_db_update_table_column('accounts', 'id', '`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT');
     $this->_db_update_table_column('settings', 'id', '`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT');
     $this->_db_update_table_column('taxes', 'id', '`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT');
     // Ensure that all foreign key columns are bigint(20)
     $this->_db_update_table_column('accounts', 'parent_account_id', '`parent_account_id` bigint(20) unsigned NULL DEFAULT NULL');
     $this->_db_update_table_column('account_reconciles', 'account_id', '`account_id` bigint(20) unsigned NULL DEFAULT NULL');
     $this->_db_update_table_column('entities', 'default_account_id', '`default_account_id` bigint(20) unsigned NULL DEFAULT NULL');
     $this->_db_update_table_column('users', 'role_id', '`role_id` bigint(20) unsigned NULL DEFAULT NULL');
     // Add indexes
     foreach ($this->_table_column_add_indexes as $table_name => $columns) {
         foreach ($columns as $column_name) {
             try {
                 $key_exist_check = DB::Query(Database::SELECT, 'SELECT COUNT(TABLE_NAME) as exist_check ' . 'FROM INFORMATION_SCHEMA.STATISTICS WHERE ' . 'TABLE_NAME = "' . $table_name . '" ' . 'AND COLUMN_NAME = "' . $column_name . '"')->execute()->as_array();
                 if ($key_exist_check[0]['exist_check'] == '0') {
                     DB::Query(NULL, 'ALTER TABLE `' . $table_name . '` ' . 'ADD INDEX(`' . $column_name . '`);')->execute();
                 }
             } catch (Exception $e) {
                 throw new Exception('An error occurred when adding an index (' . $table_name . '.' . $column_name . ') to the database: ' . $e->getMessage());
             }
         }
     }
     // Update quantity to three decimal places.
     $this->_db_update_table_column('form_lines', 'quantity', '`quantity` decimal( 13, 3 ) NULL DEFAULT NULL');
     return (object) array();
 }

 protected function _execute()
 {
     // SELECT DISTINCT DESCRIPTIONS FIRST.
     $description_query = 'SELECT form_lines.description as description FROM form_lines RIGHT JOIN forms ON form_lines.form_id = forms.id ';
     $description_query .= ' WHERE forms.type = "sale" ';
     if ($this->_search_description) {
         foreach (explode(' ', $this->_search_description) as $search_description_term) {
             if (trim($search_description_term)) {
                 $description_query .= ' AND form_lines.description LIKE "%' . $search_description_term . '%" ';
             }
         }
     }
     $description_query .= ' GROUP BY form_lines.description LIMIT 10';
     $description_results = DB::Query(Database::SELECT, $description_query)->execute()->as_array();
     $sale_lines = array();
     foreach ($description_results as $description_result) {
         $line_query = 'SELECT form_lines.account_id as account_id, form_lines.amount as amount, form_lines.quantity as quantity, form_lines.total as total FROM form_lines RIGHT JOIN forms ON form_lines.forM_id = forms.id ';
         $line_query .= ' WHERE form_lines.description = "' . $description_result['description'] . '" ';
         $line_query .= ' AND forms.type = "sale" ';
         $line_query .= ' AND form_lines.amount >= 0 ';
         $line_query .= ' ORDER BY forms.date_created DESC LIMIT 1';
         $line_result = DB::Query(Database::SELECT, $line_query)->execute()->as_array();
         $sale_lines[] = (object) array('description' => $description_result['description'], 'amount' => $line_result[0]['amount'], 'quantity' => $line_result[0]['quantity'], 'total' => $line_result[0]['total'], 'account_id' => $line_result[0]['account_id']);
     }
     usort($sale_lines, array($this, '_sort_lines_by_description'));
     return (object) array("sale_lines" => $sale_lines);
 }

    public function action_index()
    {
        $driverposition = ORM::factory("driverposition");
        if (isset($_POST["delete"])) {
            DB::Query(Database::DELETE, '
				DELETE FROM driverpositions
				WHERE id != 1')->execute();
        } else {
            if (isset($_POST["driver_id"])) {
                $driver_id = json_decode($_POST["driver_id"]);
                $client_id = json_decode($_POST["client_id"]);
                $lat = json_decode($_POST["lat"]);
                $lng = json_decode($_POST["lng"]);
                $driverposition->driver_id = $driver_id;
                $driverposition->client_id = $client_id;
                $driverposition->lat = $lat;
                $driverposition->lng = $lng;
                $driverposition->notes = $notes;
                $driverposition->time = time();
                $driverposition->save();
            }
        }
        $drivers = ORM::factory("driverposition")->find_all();
        $view = View::factory('driverpositionupdate/index')->set('drivers', $drivers);
        $this->response->body($view);
    }

 public function __construct($controller, $name, $show_actions = true)
 {
     $TempBasketID = Store_BasketController::get_temp_basket_id();
     $order_id = DB::Query("SELECT id FROM `order` WHERE (`TempBasketID`='" . $TempBasketID . "')")->value();
     /* Basket GridField */
     $config = new GridFieldConfig();
     $dataColumns = new GridFieldDataColumns();
     $dataColumns->setDisplayFields(array('getPhoto' => "Photo", 'Title' => 'Product', 'Price' => 'Item Price', 'Quantity' => 'Quantity', 'productPrice' => 'Total Price', 'getfriendlyTaxCalculation' => 'Tax Inc/Exc', 'TaxClassName' => 'Tax'));
     $config->addComponent($dataColumns);
     $config->addComponent(new GridFieldTitleHeader());
     $basket = GridField::create("BasketItems", "", DataObject::get("Order_Items", "(OrderID='" . $order_id . "')"), $config);
     /* Basket Subtotal */
     $subtotal = new Order();
     $subtotal = $subtotal->calculateSubTotal($order_id);
     $subtotal = ReadonlyField::create("SubTotal", "Basket Total (" . Product::getDefaultCurrency() . ")", $subtotal);
     /* Fields */
     $fields = FieldList::create($basket, $subtotal, ReadonlyField::create("Tax", "Tax", "Calculated on the Order Summary page."));
     /* Actions */
     $actions = FieldList::create(CompositeField::create(FormAction::create('continueshopping', 'Continue Shopping'), FormAction::create('placeorder', 'Place Order')));
     /* Required Fields */
     $required = new RequiredFields(array());
     /*
      * Now we create the actual form with our fields and actions defined 
      * within this class.
      */
     return parent::__construct($controller, $name, $fields, $show_actions ? $actions : FieldList::create(), $required);
 }

 /**
  * Connect to a secondary MySQL database, execute the query and set the database to the default connection
  **/
 public static function query_remote($query)
 {
     self::connect_remote();
     $res = DB::Query($query);
     self::$insert_id = DB::getConn()->getGeneratedID('RestDataObject');
     self::return_to_default_config();
     return $res;
 }

 /**
  * MAGIC METHOD called when property does not exist in config class
  * will save property values in config table based off of $column
  *
  * @param		string			$column
  * @param		string			$value
  * @return 		void
  */
 public function __set($column, $value)
 {
     if (array_key_exists($column, $this->config)) {
         $query = DB::Query(Database::UPDATE, 'UPDATE `config` SET `value` = :value WHERE `key` = :key');
         $query->param(':key', $column)->param(':value', $value);
         $query->execute('default');
     }
 }

 /**
  * 修改团购下线时间 
  */
 function edit_product_end_time($product_num, $end_time)
 {
     $team_id = DB::Exist('team', array('serv_code' => $product_num));
     if (!$team_id) {
         return $this->response('edit_product_end_time', '1006', '合作方无此产品的信息');
     }
     DB::Query("update team set end_time = " . $end_time . " where serv_code = " . $product_num);
     return $this->response('edit_product_end_time', '0000', '修改团购下线时间成功');
 }

 private function _check_calibrate_forms()
 {
     $calibrate_transaction_ids_query = 'SELECT forms_transactions.* FROM (  ' . '    SELECT transactions.id as transaction_id,  ' . '        forms.id as form_id, ' . '        forms.type as form_type, ' . '        forms.create_transaction_id as create_transaction_id, ' . '        forms.invoice_transaction_id as invoice_transaction_id, ' . '        forms.cancel_transaction_id as cancel_transaction_id ' . '    FROM  ' . '    transactions INNER JOIN forms ' . '    ON transactions.form_id = forms.id  ' . ') forms_transactions  ' . 'WHERE ' . 'NOT(forms_transactions.transaction_id <=> forms_transactions.create_transaction_id) AND ' . 'NOT(forms_transactions.transaction_id <=> forms_transactions.invoice_transaction_id) AND ' . 'NOT(forms_transactions.transaction_id <=> forms_transactions.cancel_transaction_id) ';
     $calibrate_transaction_ids = DB::Query(Database::SELECT, $calibrate_transaction_ids_query)->execute()->as_array();
     if ($calibrate_transaction_ids && count($calibrate_transaction_ids)) {
         return TRUE;
     }
     return FALSE;
 }

 protected function _execute()
 {
     $fye_date = $this->_get_books_closed_date();
     $purchase_ids = DB::Query(Database::SELECT, ' SELECT ' . ' id ' . ' FROM forms ' . ' WHERE ' . ' type = "purchase" AND ' . ' date_created > DATE("' . $fye_date . '") AND ' . ' ( ' . '   ( create_transaction_id IS NULL ) OR ' . '   ( date_billed IS NOT NULL AND invoice_transaction_id IS NULL ) OR ' . '   ( date_cancelled IS NOT NULL AND cancel_transaction_id IS NULL ) ' . ' ) ')->execute()->as_array();
     $ids = array();
     foreach ($purchase_ids as $purchase_id) {
         $ids[] = $purchase_id['id'];
     }
     return (object) array('ids' => $ids);
 }

 /**
  * ImageGalleryAlbumItems()
  * @note gets ImageGalleryItems for an ImageGalleryAlbum record
  * @note we don't use the ORM here as the image_gallery module may no longer exist in the code base
  */
 protected function ImageGalleryAlbumItems($album_id)
 {
     $items = array();
     if ($results = DB::Query("SELECT i.* FROM ImageGalleryItem i WHERE i.AlbumID = {$album_id}")) {
         foreach ($results as $record) {
             $items[] = $record;
         }
     }
     return $items;
 }

 /**
  * update_temp_order_addresses
  * Given the parameters, update an order record 
  * to use the correct addresses from the customers
  * address book.
  *
  * @param String $TempBasketID The TempBasketID for the current basket
  * @param String $AddressID The address to use
  * @param String $AddressType The address to update. Either shipping|billing.
  *
  * @return Boolean
  */
 public static function update_temp_order_addresses($TempBasketID, $AddressID, $AddressType)
 {
     /* The column to update */
     $Column = $AddressType == 'billing' ? "BillingAddressID" : "ShippingAddressID";
     /* Run the DB::Query */
     if (DB::Query("UPDATE `order` SET {$Column}='{$AddressID}' WHERE TempBasketID='{$TempBasketID}'")) {
         return true;
     } else {
         return false;
     }
 }

function zuitu_upgrade($action, $version = 'V1.0')
{
    $result = zuitu_action($action, $version);
    if (is_array($result) && 'db' == $action) {
        foreach ($result as $onesql) {
            $r = DB::Query($onesql);
        }
        return true;
    }
    return $result;
}

 function action_index()
 {
     $this->request->title = "Schedule Page";
     $hourHeight = 50;
     $borderWidth = 1;
     $db = Database::instance();
     $query = DB::Query(Database::SELECT, 'SELECT * FROM eventTypes', 'Model_User');
     $result = $query->execute();
     $eventTypes = $result->as_array('id');
     $query = DB::Query(Database::SELECT, "SELECT UNIX_TIMESTAMP(date(startTime)) as day,        max(hour(endTime)) as maxHour,        max(minute(endTime)) as maxMin,        min(hour(startTime)) as minHour,        min(minute(startTime)) as minMin        from events group by date(startTime)");
     $result = $query->execute();
     $hourData = $result->as_array('day');
     $query = DB::Query(Database::SELECT, "SELECT e.*,r.name AS roomName,date(e.startTime) as eventDate FROM events e JOIN rooms r ON (e.roomId=r.id)");
     $result = $query->execute();
     do {
         $day = strtotime($result->get('eventDate'));
         $startTime = strtotime($result->get('startTime'));
         $realEndTime = strtotime($result->get('endTime'));
         $endTime = $realEndTime;
         do {
             $sameDay = date('z', $startTime) == date('z', $endTime);
             if (!$sameDay) {
                 $endTime = strtotime(date('Y-m-d', $startTime) . 'T23:59:59' . date('O', $startTime));
             }
             $eventData = array('name' => $result->get('name'), 'length' => 1, 'startTime' => $startTime, 'endTime' => $endTime, 'startTimeString' => date('c', $startTime), 'endTimeString' => date('c', $endTime));
             foreach (range(1, 4) as $i) {
                 $eventTypeName = $result->get('eventType' . $i) ? $eventTypes[$result->get('eventType' . $i)]['nameKey'] : 'closed';
                 $eventData['type'][$eventTypeName] = 1;
             }
             $eventData['type'] = array_keys($eventData['type']);
             $eventData['length'] = (int) floor(($eventData['endTime'] - $eventData['startTime']) / 60 / 30);
             $data[$result->get('roomName')][$day][$startTime] = $eventData;
             if (!$sameDay) {
                 $hourData[$day]['maxHour'] = 23;
                 $hourData[$day]['maxMin'] = 00;
                 $startTime += 60 * 60 * 24;
                 /* = 1 day*/
                 $day += 60 * 60 * 24;
                 /* = 1 day*/
                 $startTime = strtotime(date('Y-m-d', $startTime) . 'T00:00:00' . date('O', $startTime));
                 $endTime = $realEndTime;
                 $hourData[$day]['minHour'] = 00;
                 $hourData[$day]['minMin'] = 00;
             }
         } while (!$sameDay);
     } while ($result->next() && $result->valid());
     $days = array_keys($hourData);
     $rooms = array_keys($data);
     sort($rooms);
     $this->template->content = View::factory('schedule/index', array('eventTypes' => $eventTypes, 'borderWidth' => $borderWidth, 'days' => $days, 'rooms' => $rooms, 'hourData' => $hourData, 'hourHeight' => $hourHeight, 'data' => $data));
     if (class_exists('DebugToolbar')) {
         echo DebugToolbar::render();
     }
 }

function backup_import($fname)
{
    global $db;
    $sqls = file($fname);
    foreach ($sqls as $sql) {
        str_replace("\r", "", $sql);
        str_replace("\n", "", $sql);
        DB::Query($sql);
    }
    return true;
}

 protected function _execute()
 {
     if ($this->_customer_id and !$this->_load_customer($this->_customer_id)->loaded()) {
         throw new Exception("Invalid report customer ID: customer not found.");
     }
     // Look up all sale IDs
     $sale_ids_query = 'SELECT id FROM forms WHERE type = "sale" AND date_due IS NULL AND date_cancelled IS NULL ';
     if ($this->_customer_id) {
         $sale_ids_query .= ' AND entity_id = "' . $this->_customer_id . '" ';
     }
     if ($this->_days_old_minimum) {
         $sale_ids_query .= ' AND date_created <= DATE("' . date("Y-m-d", strtotime("-" . $this->_days_old_minimum . " Days")) . '") ';
     }
     if ($this->_balance_filter) {
         if ($this->_balance_filter == "unpaid") {
             $sale_ids_query .= ' AND ( balance + total ) = 0 ';
         } else {
             if ($this->_balance_filter == "paid") {
                 $sale_ids_query .= ' AND ( balance + total ) != 0 ';
             } else {
                 throw new Exception("Invalid balance_filter: must be paid or unpaid.");
             }
         }
     }
     $sale_ids_query .= ' ORDER BY date_created ASC, id ASC ';
     $sale_ids = DB::Query(Database::SELECT, $sale_ids_query)->execute()->as_array();
     $customers = array();
     $timestamp_today = strtotime(date("Y-m-d"));
     $total_total = 0.0;
     $paid_total = 0.0;
     $balance_total = 0.0;
     foreach ($sale_ids as $sale_id) {
         $sale = ORM::Factory('form_sale', $sale_id);
         if (!isset($customers[$sale->entity_id])) {
             $customers[$sale->entity_id] = new stdClass();
             $customers[$sale->entity_id]->customer_name = $sale->entity->first_name . ' ' . $sale->entity->last_name;
             $customers[$sale->entity_id]->customer_company_name = $sale->entity->company_name;
             $customers[$sale->entity_id]->customer_phone_number = $sale->entity->phone_number;
             $customers[$sale->entity_id]->sales = array();
             $customers[$sale->entity_id]->total_total = 0.0;
             $customers[$sale->entity_id]->paid_total = 0.0;
             $customers[$sale->entity_id]->balance_total = 0.0;
         }
         $report_sale = (object) array('id' => $sale->id, 'date_created' => $sale->date_created, 'date_due' => $sale->date_due, 'sale_number' => $sale->code, 'balance' => $sale->balance * -1, 'total' => $sale->total, 'paid' => $sale->total - $sale->balance * -1, 'days_late' => round(($timestamp_today - strtotime($sale->date_created)) / 86400));
         $customers[$sale->entity_id]->total_total = $this->_beans_round($customers[$sale->entity_id]->total_total + $report_sale->total);
         $customers[$sale->entity_id]->paid_total = $this->_beans_round($customers[$sale->entity_id]->paid_total + $report_sale->paid);
         $customers[$sale->entity_id]->balance_total = $this->_beans_round($customers[$sale->entity_id]->balance_total + $report_sale->balance);
         $total_total = $this->_beans_round($total_total + $report_sale->total);
         $paid_total = $this->_beans_round($paid_total + $report_sale->paid);
         $balance_total = $this->_beans_round($balance_total + $report_sale->balance);
         $customers[$sale->entity_id]->sales[] = $report_sale;
     }
     return (object) array('date' => date("Y-m-d"), 'customer_id' => $this->_customer_id, 'days_old_minimum' => $this->_days_old_minimum, 'balance_filter' => $this->_balance_filter, 'customers' => $customers, 'total_customers' => count($customers), 'total_total' => $total_total, 'paid_total' => $paid_total, 'balance_total' => $balance_total);
 }