PHP DbHelper类代码示例

 /**
  * @inheritDoc IElementType::modifyElementsQuery()
  *
  * @param DbCommand $query
  * @param ElementCriteriaModel $criteria
  *
  * @return mixed
  */
 public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
 {
     $query->addSelect('globalsets.name, globalsets.handle, globalsets.fieldLayoutId')->join('globalsets globalsets', 'globalsets.id = elements.id');
     if ($criteria->handle) {
         $query->andWhere(DbHelper::parseParam('globalsets.handle', $criteria->handle, $query->params));
     }
 }

    public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
    {
        $query->addSelect('
				neoblocks.fieldId,
				neoblocks.ownerId,
				neoblocks.ownerLocale,
				neoblocks.typeId,
				neoblocks.collapsed
			')->join('neoblocks neoblocks', 'neoblocks.id = elements.id')->leftJoin('neoblockstructures neoblockstructures', ['and', 'neoblockstructures.ownerId = neoblocks.ownerId', 'neoblockstructures.fieldId = neoblocks.fieldId', ['or', 'neoblockstructures.ownerLocale = neoblocks.ownerLocale', ['and', 'neoblockstructures.ownerLocale is null', 'neoblocks.ownerLocale is null']]])->leftJoin('structureelements structureelements', ['and', 'structureelements.structureId = neoblockstructures.structureId', 'structureelements.elementId = neoblocks.id']);
        if ($criteria->fieldId) {
            $query->andWhere(DbHelper::parseParam('neoblocks.fieldId', $criteria->fieldId, $query->params));
        }
        if ($criteria->ownerId) {
            $query->andWhere(DbHelper::parseParam('neoblocks.ownerId', $criteria->ownerId, $query->params));
        }
        if ($criteria->ownerLocale) {
            $query->andWhere(DbHelper::parseParam('neoblocks.ownerLocale', $criteria->ownerLocale, $query->params));
        }
        if ($criteria->typeId) {
            $query->andWhere(DbHelper::parseParam('neoblocks.typeId', $criteria->typeId, $query->params));
        } else {
            if ($criteria->type) {
                $query->join('neoblocktypes neoblocktypes', 'neoblocktypes.id = neoblocks.typeId');
                $query->andWhere(DbHelper::parseParam('neoblocktypes.handle', $criteria->type, $query->params));
            }
        }
    }

 private static function importSales($file)
 {
     $error = array();
     $row = 0;
     if (($handle = fopen($file['tmp_name'], "r")) !== false) {
         $cols = array(SALE_ORDER_NUMBER, SALE_ORDER_CHARGED_DATE, SALE_ORDER_CHARGED_TIMESTAMP, SALE_FINANCIAL_STATUS, SALE_DEVICE_MODEL, SALE_PRODUCT_TITLE, SALE_PRODUCT_ID, SALE_PRODUCT_TYPE, SALE_SKU_ID, SALE_CURRENCY_CODE, SALE_ITEM_PRICE, SALE_TAXES_COLLECTED, SALE_CHARGED_AMOUNT, SALE_BUYER_CITY, SALE_BUYER_STATE, SALE_BUYER_POSTAL_CODE, SALE_BUYER_COUNTRY, SALE_APP_ID);
         while (($data = fgetcsv($handle, 1000, ",")) !== false) {
             if ($row > 0) {
                 $rowCount = count($data);
                 if ($rowCount != CHECKOUT_SALES_FILE_COL_COUNT) {
                     $error[] = 'Row #' . $row . ' has invalid column count ' . $rowCount . '/' . CHECKOUT_SALES_FILE_COL_COUNT;
                 } else {
                     $values = array();
                     for ($colIdx = 0; $colIdx < $rowCount; ++$colIdx) {
                         $values[$cols[$colIdx]] = $data[$colIdx];
                     }
                 }
                 $res = DbHelper::insertSale($values);
                 if ($res != null) {
                     $error[] = 'Row #' . $row . ' insertion failed : ' . $res;
                 }
             }
             ++$row;
         }
         fclose($handle);
     }
     return $error;
 }

 public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
 {
     $query->addSelect('formbuilder_entries.formId, formbuilder_entries.title, formbuilder_entries.data')->join('formbuilder_entries formbuilder_entries', 'formbuilder_entries.id = elements.id');
     if ($criteria->formId) {
         $query->andWhere(DbHelper::parseParam('formbuilder_entries.formId', $criteria->formId, $query->params));
     }
 }

 public static function addtoNewsLetterSubscriptionList($email = '')
 {
     global $tableprefix;
     if (!empty($email)) {
         // Check email address exists in subscribers list
         $resCat = DbHelper::execute("SELECT nId FROM " . $tableprefix . "newsletter_subscribers WHERE vEmail ='" . mysql_real_escape_string($email) . "' ");
         $data = DbHelper::fetchOne($resCat);
         // If alraedy exists return false
         if ($data) {
             $emailSubscribed = 'exists';
         } else {
             // Insert Email id to subscriberlist
             $resInsert = DbHelper::execute("INSERT INTO " . $tableprefix . "newsletter_subscribers\n                                                (vEmail)VALUES('" . mysql_real_escape_string($email) . "')");
             $constantcontactSettings = getconstantcontactSettings();
             $_SESSION['constantaction'] = 'Add Email';
             $userinfo = array();
             $userinfo['emailAddress'] = mysql_real_escape_string($email);
             $userinfo['firstName'] = '';
             $userinfo['lastName'] = '';
             $userinfo['lists'] = array($constantcontactSettings['constantcontactlistId']);
             $_SESSION['constantparam']['redirecturl'] = SITE_URL . '/checkout.php';
             //header("location:".$constantcontactSettings['verificationURL']);
             $emailSubscribed = 'added';
         }
         return $emailSubscribed;
     }
 }

 public function defineContentAttribute()
 {
     $maxLength = $this->getSettings()->maxLength;
     if (!$maxLength) {
         $columnType = ColumnType::Text;
     } else {
         $columnType = DbHelper::getTextualColumnTypeByContentLength($maxLength);
     }
     return array(AttributeType::String, 'column' => $columnType, 'maxLength' => $maxLength);
 }

 public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
 {
     $query->addSelect('submissions.formId')->join('formerly_submissions submissions', 'submissions.id = elements.id');
     if ($criteria->formId) {
         $query->andWhere(DbHelper::parseParam('submissions.formId', $criteria->formId, $query->params));
     }
     if ($criteria->form) {
         $query->join('formerly_forms forms', 'forms.id = submissions.formId');
         $query->andWhere(DbHelper::parseParam('formerly_forms.handle', $criteria->form, $query->params));
     }
 }

 /**
  * Save a list of models, each model may be inserted or updated depend on its existence.
  * This method could be used to achieve better performance during insertion/update of the large
  * amount of data to the database table.
  * @param \yii\db\ActiveRecord[] $models list of models to be saved.
  * If a key is not a valid column name, the corresponding value will be ignored.
  * @param array $attributeNames name list of attributes that need to be update. Defaults to empty,
  * meaning all fields of corresponding active record will be saved.
  * This parameter is ignored in the case of insertion
  * @param int $mode the save mode flag.
  * If this flag value is set to 0, any model that have a PK value is NULL will be inserted, otherwise it will be update.
  * If this flag value is set to 1, all models will be inserted regardless to PK values.
  * If this flag value is set to 2, all models will be updated regardless to PK values
  * @return \stdClass An instance of stdClass that may have one of the following fields:
  * - The 'lastId' field is the last model ID (auto-incremental primary key) inserted.
  * - The 'insertCount' is the number of rows inserted.
  * - The 'updateCount' is the number of rows updated.
  */
 public static function batchSave($models, $attributeNames = [], $mode = DbHelper::SAVE_MODE_AUTO)
 {
     $returnModels = [];
     $a = DbHelper::batchSave($models, $attributeNames, $mode, $returnModels);
     if (isset($a)) {
         $insertModels = isset($returnModels['inserted']) ? $returnModels['inserted'] : null;
         $updateModels = isset($returnModels['updated']) ? $returnModels['updated'] : null;
         static::afterBatchSave($attributeNames, $mode, $insertModels, $updateModels);
     }
     return $a;
 }

 public function __construct(ConnectionInterface $db, $table, array $fields, $isTemp = true)
 {
     if (!$table) {
         throw new ImporterException('Не задана таблица для импорта');
     }
     if (!$fields) {
         throw new ImporterException('Не заданы поля для импорта.');
     }
     $this->db = $db;
     $this->fields = $fields;
     $this->table = $table;
     if ($isTemp) {
         $this->table .= '_xml_importer';
         DbHelper::createTable($this->db, $this->table, $this->fields, $isTemp);
     }
 }

 /**
  * Any migration code in here is wrapped inside of a transaction.
  *
  * @return bool
  */
 public function safeUp()
 {
     if (!craft()->db->tableExists('searchindex')) {
         // Taking the scenic route here so we can get to MysqlSchema's $engine argument
         $table = DbHelper::addTablePrefix('searchindex');
         $columns = array('elementId' => DbHelper::generateColumnDefinition(array('column' => ColumnType::Int, 'null' => false)), 'attribute' => DbHelper::generateColumnDefinition(array('column' => ColumnType::Varchar, 'maxLength' => 25, 'null' => false)), 'fieldId' => DbHelper::generateColumnDefinition(array('column' => ColumnType::Int, 'null' => false)), 'locale' => DbHelper::generateColumnDefinition(array('column' => ColumnType::Locale, 'null' => false)), 'keywords' => DbHelper::generateColumnDefinition(array('column' => ColumnType::Text, 'null' => false)));
         $this->execute(craft()->db->getSchema()->createTable($table, $columns, null, 'MyISAM'));
         // Give it a composite primary key
         $this->addPrimaryKey('searchindex', 'elementId,attribute,fieldId,locale');
         // Add the FULLTEXT index on `keywords`
         $this->execute('CREATE FULLTEXT INDEX ' . craft()->db->quoteTableName(DbHelper::getIndexName('searchindex', 'keywords')) . ' ON ' . craft()->db->quoteTableName($table) . ' ' . '(' . craft()->db->quoteColumnName('keywords') . ')');
         Craft::log('Successfully added the `searchindex` table with a fulltext index on `keywords`.', LogLevel::Info, true);
     } else {
         Craft::log('Tried to add the `searchindex` table, but it already exists.', LogLevel::Warning, true);
     }
     return true;
 }

 public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
 {
     $query->addSelect('supertableblocks.fieldId, supertableblocks.ownerId, supertableblocks.ownerLocale, supertableblocks.typeId, supertableblocks.sortOrder')->join('supertableblocks supertableblocks', 'supertableblocks.id = elements.id');
     if ($criteria->fieldId) {
         $query->andWhere(DbHelper::parseParam('supertableblocks.fieldId', $criteria->fieldId, $query->params));
     }
     if ($criteria->ownerId) {
         $query->andWhere(DbHelper::parseParam('supertableblocks.ownerId', $criteria->ownerId, $query->params));
     }
     if ($criteria->ownerLocale) {
         $query->andWhere(DbHelper::parseParam('supertableblocks.ownerLocale', $criteria->ownerLocale, $query->params));
     }
     if ($criteria->type) {
         $query->join('supertableblocktypes supertableblocktypes', 'supertableblocktypes.id = supertableblocks.typeId');
         $query->andWhere(DbHelper::parseParam('supertableblocktypes.handle', $criteria->type, $query->params));
     }
 }

 public static function checkCouponcodeExists($couponcode = '', $userId = "")
 {
     global $tableprefix;
     $currentDate = date('Y-m-d');
     if (!empty($couponcode)) {
         // Check email address exists in subscribers list
         $query = "SELECT o.couponCode FROM " . $tableprefix . "orders o\n                        WHERE  o.user_id = '" . mysql_real_escape_string($userId) . "' AND\n                              o.couponCode ='" . mysql_real_escape_string($couponcode) . "'  ";
         $resCat = DbHelper::execute($query);
         if (mysql_num_rows($resCat) == 0) {
             $query = "SELECT cc.* FROM " . $tableprefix . "couponcode cc WHERE cc.ccCode='" . mysql_real_escape_string($couponcode) . "' AND\n                              cc.ccStatus='Y' AND cc.subscriptionStatus='Y'\n                              AND cc.ccStartDate<='" . mysql_real_escape_string($currentDate) . "' AND\n                              cc.ccEndDate>='" . mysql_real_escape_string($currentDate) . "'";
             $resCat = DbHelper::execute($query);
             $data = DbHelper::fetchRow($resCat);
         }
         // If couopon code valid then return coupon percenatage
         if ($data) {
             return $data;
         }
     }
 }

 /**
  * @inheritDoc IFieldType::defineContentAttribute()
  *
  * @return mixed
  */
 public function defineContentAttribute()
 {
     if ($this->multi) {
         $options = $this->getSettings()->options;
         // See how much data we could possibly be saving if everything was selected.
         $length = 0;
         foreach ($options as $option) {
             if (!empty($option['value'])) {
                 // +3 because it will be json encoded. Includes the surrounding quotes and comma.
                 $length += strlen($option['value']) + 3;
             }
         }
         if ($length) {
             // Add +2 for the outer brackets and -1 for the last comma.
             $length += 1;
             $columnType = DbHelper::getTextualColumnTypeByContentLength($length);
         } else {
             $columnType = ColumnType::Varchar;
         }
         return array(AttributeType::Mixed, 'column' => $columnType, 'default' => $this->getDefaultValue());
     } else {
         return array(AttributeType::String, 'column' => ColumnType::Varchar, 'maxLength' => 255, 'default' => $this->getDefaultValue());
     }
 }

 /**
  * @inheritDoc IElementType::modifyElementsQuery()
  *
  * @param DbCommand            $query
  * @param ElementCriteriaModel $criteria
  *
  * @return bool|false|null|void
  */
 public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
 {
     $query->addSelect('entries.sectionId, entries.typeId, entries.authorId, entries.postDate, entries.expiryDate')->join('entries entries', 'entries.id = elements.id')->join('sections sections', 'sections.id = entries.sectionId')->leftJoin('structures structures', 'structures.id = sections.structureId')->leftJoin('structureelements structureelements', array('and', 'structureelements.structureId = structures.id', 'structureelements.elementId = entries.id'));
     if ($criteria->ref) {
         $refs = ArrayHelper::stringToArray($criteria->ref);
         $conditionals = array();
         foreach ($refs as $ref) {
             $parts = array_filter(explode('/', $ref));
             if ($parts) {
                 if (count($parts) == 1) {
                     $conditionals[] = DbHelper::parseParam('elements_i18n.slug', $parts[0], $query->params);
                 } else {
                     $conditionals[] = array('and', DbHelper::parseParam('sections.handle', $parts[0], $query->params), DbHelper::parseParam('elements_i18n.slug', $parts[1], $query->params));
                 }
             }
         }
         if ($conditionals) {
             if (count($conditionals) == 1) {
                 $query->andWhere($conditionals[0]);
             } else {
                 array_unshift($conditionals, 'or');
                 $query->andWhere($conditionals);
             }
         }
     }
     if ($criteria->type) {
         $typeIds = array();
         if (!is_array($criteria->type)) {
             $criteria->type = array($criteria->type);
         }
         foreach ($criteria->type as $type) {
             if (is_numeric($type)) {
                 $typeIds[] = $type;
             } else {
                 if (is_string($type)) {
                     $types = craft()->sections->getEntryTypesByHandle($type);
                     if ($types) {
                         foreach ($types as $type) {
                             $typeIds[] = $type->id;
                         }
                     } else {
                         return false;
                     }
                 } else {
                     if ($type instanceof EntryTypeModel) {
                         $typeIds[] = $type->id;
                     } else {
                         return false;
                     }
                 }
             }
         }
         $query->andWhere(DbHelper::parseParam('entries.typeId', $typeIds, $query->params));
     }
     if ($criteria->postDate) {
         $query->andWhere(DbHelper::parseDateParam('entries.postDate', $criteria->postDate, $query->params));
     } else {
         if ($criteria->after) {
             $query->andWhere(DbHelper::parseDateParam('entries.postDate', '>=' . $criteria->after, $query->params));
         }
         if ($criteria->before) {
             $query->andWhere(DbHelper::parseDateParam('entries.postDate', '<' . $criteria->before, $query->params));
         }
     }
     if ($criteria->expiryDate) {
         $query->andWhere(DbHelper::parseDateParam('entries.expiryDate', $criteria->expiryDate, $query->params));
     }
     if ($criteria->editable) {
         $user = craft()->userSession->getUser();
         if (!$user) {
             return false;
         }
         // Limit the query to only the sections the user has permission to edit
         $editableSectionIds = craft()->sections->getEditableSectionIds();
         $query->andWhere(array('in', 'entries.sectionId', $editableSectionIds));
         // Enforce the editPeerEntries permissions for non-Single sections
         $noPeerConditions = array();
         foreach (craft()->sections->getEditableSections() as $section) {
             if ($section->type != SectionType::Single && !$user->can('editPeerEntries:' . $section->id)) {
                 $noPeerConditions[] = array('or', 'entries.sectionId != ' . $section->id, 'entries.authorId = ' . $user->id);
             }
         }
         if ($noPeerConditions) {
             array_unshift($noPeerConditions, 'and');
             $query->andWhere($noPeerConditions);
         }
     }
     if ($criteria->section) {
         if ($criteria->section instanceof SectionModel) {
             $criteria->sectionId = $criteria->section->id;
             $criteria->section = null;
         } else {
//.........这里部分代码省略.........

<?php

defined('DIRECT_ACCESS_CHECK') or die('DIRECT ACCESS NOT ALLOWED');
/**
 * Copyright (c) 2013 EIRL DEVAUX J. - Medialoha.
 * All rights reserved. This program and the accompanying materials
 * are made available under the terms of the GNU Public License v3.0
 * which accompanies this distribution, and is available at
 * http://www.gnu.org/licenses/gpl.html
 *
 * Contributors:
 *     EIRL DEVAUX J. - Medialoha - initial API and implementation
 */
// get reports preferences
$cfg = CfgHelper::getInstance();
$mAppArr = DbHelper::selectRows(TBL_APPLICATIONS, null, APP_NAME . ' ASC', '*', null, null, false);
$mSelectedAppId = $mNavCtl->getParam('app', '-1');
$mSelectedAppName = "All Applications";
$mSelectedAppPackage = null;
// build applications dropdown items array
$mDropdownItems = array('<li><a href="#" onclick="setSelectedAppId(this, -1)" >All Applications</a></li>');
foreach ($mAppArr as $app) {
    $mDropdownItems[] = '<li><a href="#" onclick="setSelectedAppId(this, ' . $app[APP_ID] . ')" >' . $app[APP_NAME] . '</a></li>';
    if ($mSelectedAppId == $app[APP_ID]) {
        $mSelectedAppName = $app[APP_NAME];
        $mSelectedAppPackage = $app[APP_PACKAGE];
    }
}
?>
<div class="navbar">
  <div class="navbar-inner">