PHP ActiveRecord::getDataBySQL方法代码示例

 public function setDatabase()
 {
     set_time_limit(0);
     if (!$this->buildDatabaseValidator()->isValid()) {
         return new ActionRedirectResponse('install', 'database');
     }
     $type = function_exists('mysql_connect') ? 'mysql' : 'mysqli';
     $dsn = $type . '://' . $this->request->get('username') . ($this->request->get('password') ? ':' . $this->request->get('password') : '') . '@' . $this->request->get('server') . '/' . $this->request->get('name');
     ClassLoader::import('library.activerecord.ActiveRecord');
     ActiveRecord::resetDBConnection();
     ActiveRecord::setDSN($dsn);
     try {
         $conn = ActiveRecord::getDBConnection();
         // test if InnoDB tables can be created
         $table = 'TestInnoDB';
         $create = 'CREATE TABLE ' . $table . ' (ID INTEGER) ENGINE = INNODB';
         $drop = 'DROP TABLE ' . $table;
         ActiveRecord::executeUpdate($create);
         $data = ActiveRecord::getDataBySQL('SHOW TABLE STATUS');
         ActiveRecord::executeUpdate($drop);
         foreach ($data as $row) {
             if (strtolower($row['Name']) == strtolower($table)) {
                 if (strtolower($row['Engine']) != 'innodb') {
                     throw new SQLException('', $this->translate('_err_innodb_not_available'));
                 }
             }
         }
         $dsnFile = $this->getDsnFile();
         if (!file_exists(dirname($dsnFile))) {
             mkdir(dirname($dsnFile), 0777, true);
         }
         ActiveRecord::beginTransaction();
         //ActiveRecord::executeUpdate('SET FOREIGN_KEY_CHECKS = 0');
         //ActiveRecord::executeUpdate('DROP TABLE `AccessControlAssociation`, `AdAdvertiser`, `AdAdvertiserUser`, `AdBanner`, `AdBannerStats`, `AdCampaign`, `AdCampaignCondition`, `AdZone`, `Author`, `AuthorImage`, `BillingAddress`, `Category`, `CategoryImage`, `CategoryPresentation`, `CategorySubscribeCategory`, `CategorySubscribeQueue`, `CategorySubscribeUser`, `Currency`, `CustomerOrder`, `DeliveryZone`, `DeliveryZoneAddressMask`, `DeliveryZoneCityMask`, `DeliveryZoneCountry`, `DeliveryZoneRealTimeService`, `DeliveryZoneState`, `DeliveryZoneWarehouse`, `DeliveryZoneZipMask`, `Discount`, `DiscountAction`, `DiscountCondition`, `DiscountConditionRecord`, `EavDateValue`, `EavField`, `EavFieldGroup`, `EavItem`, `EavNumericValue`, `EavObject`, `EavStringValue`, `EavValue`, `ExpressCheckout`, `Filter`, `FilterGroup`, `HelpComment`, `Language`, `Manufacturer`, `ManufacturerImage`, `NewsletterMessage`, `NewsletterSentMessage`, `NewsletterSubscriber`, `NewsPost`, `OrderCoupon`, `OrderDiscount`, `OrderedItem`, `OrderedItemOption`, `OrderLog`, `OrderNote`, `PostalCode`, `Product`, `ProductBundle`, `ProductCategory`, `ProductFile`, `ProductFileGroup`, `ProductImage`, `ProductList`, `ProductListItem`, `ProductOption`, `ProductOptionChoice`, `ProductPrice`, `ProductRating`, `ProductRatingSummary`, `ProductRatingType`, `ProductRelationship`, `ProductRelationshipGroup`, `ProductReview`, `ProductVariation`, `ProductVariationTemplate`, `ProductVariationType`, `ProductVariationValue`, `ProductWarehouse`, `PurchasePointsItemOrder`, `PurchasePointsOrder`, `PurchasePointsUser`, `RecurringProductPeriod`, `RewardPointsOrder`, `RewardPointsUser`, `Role`, `SearchLog`, `SessionData`, `Shipment`, `ShipmentTax`, `ShipmentWarehouse`, `ShippingAddress`, `ShippingRate`, `ShippingService`, `SpecField`, `SpecFieldGroup`, `SpecFieldValue`, `SpecificationDateValue`, `SpecificationItem`, `SpecificationNumericValue`, `SpecificationStringValue`, `State`, `StaticPage`, `Tax`, `TaxRate`, `Transaction`, `User`, `UserAddress`, `UserGroup`, `Warehouse`');
         // import schema
         Installer::loadDatabaseDump(file_get_contents(ClassLoader::getRealPath('installdata.sql') . '/create.sql'), true);
         // create root category
         Installer::loadDatabaseDump(file_get_contents(ClassLoader::getRealPath('installdata.sql') . '/initialData.sql'), true);
         // states
         Installer::loadDatabaseDump(file_get_contents(ClassLoader::getRealPath('installdata.sql.state') . '/all.sql'), true);
         file_put_contents($dsnFile, '<?php return ' . var_export($dsn, true) . '; ?>');
         ActiveRecord::commit();
         return new ActionResponse();
         //return new ActionRedirectResponse('install', 'admin');
     } catch (SQLException $e) {
         $validator = $this->buildDatabaseValidator();
         $validator->triggerError('connect', $e->getNativeError());
         $validator->saveState();
         return new ActionResponse('step', 'database');
         //return new ActionRedirectResponse('install', 'database');
     }
 }

 public function setFileList($files)
 {
     // get file list from store
     $url = 'http://' . $this->domain->get() . '/storeSync/fileList';
     $params = array('password' => $this->apiKey->get());
     //$u = $url . '?password=' . $this->apiKey->get();
     //$res = file_get_contents($u);
     echo '<span style="font-size: 2px">.</span>';
     $res = $this->httpPost($url, $params);
     if (is_null($res)) {
         return;
     }
     echo '<span style="font-size: 2px">.</span>';
     $res = json_decode($res, true);
     if (is_null($res)) {
         return;
     }
     echo '<span style="font-size: 2px">.</span>';
     // get ID's of images actually used
     $ids = array();
     foreach (ActiveRecord::getDataBySQL('SELECT ID FROM galssess_snapshot_import.ProductImage') as $id) {
         $ids[$id['ID']] = true;
     }
     echo '<span style="font-size: 2px">.</span>';
     // filter out files that are not needed to update
     foreach ($files as $file => $time) {
         if (!empty($res[$file]) && $res[$file] >= $time) {
             unset($files[$file]);
         } else {
             if (strpos($file, 'roductimage')) {
                 list($productId, $id, $foo) = explode('-', $file, 3);
                 if (!isset($ids[$id])) {
                     unset($files[$file]);
                 }
             }
         }
     }
     echo '<span style="font-size: 2px">.</span>';
     $cd = getcwd();
     chdir(ClassLoader::getRealPath('public.upload'));
     echo '<span style="font-size: 2px">.</span>';
     $tar = $this->getQueryFile('.tar');
     exec('tar cvf "' . $tar . '" --files-from /dev/null');
     echo '<span style="font-size: 2px">.</span>';
     foreach ($files as $file => $time) {
         exec('tar rvf "' . $tar . '" ' . $file);
         echo '<span style="font-size: 2px">.</span>';
     }
     echo '<span style="font-size: 2px">.</span>';
     chdir($cd);
     echo '<span style="font-size: 2px">.</span>';
 }

 private function getRecipientData($data)
 {
     $query = $this->getRecipientQuery($data, true);
     if (!$query) {
         return array();
     }
     return ActiveRecord::getDataBySQL('SELECT email, userID, subscriberID FROM (' . $query . ') AS recipients GROUP BY email');
 }

 public function processDataArray($orders, $displayedColumns)
 {
     $orders = parent::processDataArray($orders, $displayedColumns);
     $ids = array();
     foreach ($orders as &$order) {
         $ids[$order['ID']] =& $order;
         foreach ($order as $field => &$value) {
             if ('status' == $field) {
                 switch ($order[$field]) {
                     case 1:
                         $value = $this->translate('_status_processing');
                         break;
                     case 2:
                         $value = $this->translate('_STATUS_AWAITING');
                         break;
                     case 3:
                         $value = $this->translate('_status_shipped');
                         break;
                     case 4:
                         $value = $this->translate('_status_canceled');
                         break;
                     default:
                         $value = $this->translate('_status_new');
                         break;
                 }
             }
             if ('totalAmount' == $field || 'capturedAmount' == $field) {
                 if (empty($value)) {
                     $value = '0';
                 }
                 if (isset($order['Currency'])) {
                     $value .= ' ' . $order['Currency']['ID'];
                 }
             }
             if ('dateCompleted' == $field && !$value) {
                 $value = '-';
             }
         }
     }
     if ($orders && (isset($displayedColumns['CustomerOrder.taxAmount']) || 1)) {
         $sql = 'SELECT SUM(ShipmentTax.amount) AS taxAmount, Shipment.orderID AS orderID FROM ShipmentTax LEFT JOIN Shipment ON ShipmentTax.shipmentID=Shipment.ID WHERE Shipment.orderID IN(' . implode(', ', array_keys($ids)) . ') GROUP BY Shipment.orderID';
         foreach (ActiveRecord::getDataBySQL($sql) as $row) {
             $ids[$row['orderID']]['taxAmount'] = round($row['taxAmount'], 2);
         }
     }
     return $orders;
 }

 public function getProductCount(ProductFilter $productFilter)
 {
     $query = new ARSelectQueryBuilder();
     $query->includeTable('Product');
     $query->addField('COUNT(*) AS cnt');
     $filter = $this->getProductsFilter($productFilter);
     $filter->setLimit(0);
     $query->setFilter($filter);
     $data = ActiveRecord::getDataBySQL($query->getPreparedStatement(ActiveRecord::getDBConnection()));
     return $data[0]['cnt'];
 }

 public function testMultipleSelectValues()
 {
     // create a multiple value select attribute
     $multiSel = SpecField::getNewInstance($this->productCategory, SpecField::DATATYPE_NUMBERS, SpecField::TYPE_NUMBERS_SELECTOR);
     $multiSel->isMultiValue->set(true);
     $multiSel->setValueByLang('name', 'en', 'Select multiple values');
     $multiSel->setValueByLang('name', 'lt', 'Pasirinkite kelias reiksmes');
     $multiSel->save();
     $values = array();
     for ($k = 0; $k < 5; $k++) {
         $inst = SpecFieldValue::getNewInstance($multiSel);
         $inst->setValueByLang('value', 'en', $k);
         $inst->setValueByLang('value', 'lt', 'Blaah');
         $inst->save();
         $values[] = $inst;
     }
     // assign the multiselect values
     $this->product->setAttributeValue($multiSel, $values[1]);
     $this->product->setAttributeValue($multiSel, $values[3]);
     $this->product->save();
     $array = $this->product->toArray();
     $this->assertEqual(2, count($array['attributes'][$multiSel->getID()]['values']));
     // assign one more multiselect value
     $this->product->setAttributeValue($multiSel, $values[2]);
     $this->product->save();
     $array = $this->product->toArray();
     $this->assertEqual(3, count($array['attributes'][$multiSel->getID()]['values']));
     // remove the first multiselect value
     $this->product->removeAttributeValue($multiSel, $values[1]);
     $this->product->save();
     $array = $this->product->toArray();
     $this->assertEqual(2, count($array['attributes'][$multiSel->getID()]['values']));
     // check for the number of SpecificationItem instances matching this field/product in database
     $query = 'SELECT COUNT(*) FROM SpecificationItem WHERE productID=' . $this->product->getID() . ' AND specFieldID=' . $multiSel->getID();
     $data = ActiveRecord::getDataBySQL($query);
     $this->assertEqual(2, array_shift(array_shift($data)));
     // remove the multiselect value altogether
     $this->product->removeAttribute($multiSel);
     $this->product->save();
     // check for the number of SpecificationItem instances matching this field/product in database.
     // shouldn't be any after the value removal
     $query = 'SELECT COUNT(*) FROM SpecificationItem WHERE productID=' . $this->product->getID() . ' AND specFieldID=' . $multiSel->getID();
     $data = ActiveRecord::getDataBySQL($query);
     $this->assertEqual(0, array_shift(array_shift($data)));
     // set the values back, so we could test how the data is restored from DB
     $this->product->setAttributeValue($multiSel, $values[1]);
     $this->product->setAttributeValue($multiSel, $values[2]);
     $this->product->save();
 }

<?php

include '../application/Initialize.php';
ClassLoader::import('application.LiveCart');
new LiveCart();
ClassLoader::import('application.model.product.ProductImage');
$dir = ClassLoader::getRealPath('public.upload.productimage');
if (!file_exists($dir)) {
    return false;
}
$ids = array();
foreach (ActiveRecord::getDataBySQL('SELECT ID FROM ProductImage') as $id) {
    $ids[$id['ID']] = true;
}
chdir($dir);
$deleted = array();
$deletedCnt = 0;
foreach (glob('*') as $file) {
    list($productId, $id, $foo) = explode('-', $file, 3);
    if (!isset($ids[$id])) {
        $deleted[$id] = true;
        $deletedCnt++;
        unlink($file);
    }
}
echo 'Image cleanup completed. Deleted ' . $deletedCnt . ' image files for ' . count($deleted) . ' product images';

 private function getRecordCounts($table)
 {
     return array(array_shift(array_shift(ActiveRecord::getDataBySQL('SELECT COUNT(*) FROM ' . $table))), array_shift(array_shift(ActiveRecord::getDataBySQL('SELECT COUNT(*) FROM testCloning_import.' . $table))));
 }

 private function createFiltersInGroupsCountArray(ARSet $filtersGroupsSet)
 {
     $filterGroupIds = array();
     $filtersGroupsArray = array();
     foreach ($filtersGroupsSet as $filterGroup) {
         $filterGroupIds[] = $filterGroup->getID();
     }
     if (!empty($filterGroupIds)) {
         $db = ActiveRecord::getDBConnection();
         $filterGroupIdsString = implode(',', $filterGroupIds);
         $filtersResultArray = ActiveRecord::getDataBySQL("SELECT filterGroupID, COUNT(*) AS filtersCount FROM Filter WHERE filterGroupID IN ({$filterGroupIdsString}) GROUP BY filterGroupID");
         $filtersResultCount = count($filtersResultArray);
         $specFieldValuesResultArray = ActiveRecord::getDataBySQL("SELECT specFieldID, COUNT(specFieldID) AS filtersCount FROM SpecFieldValue WHERE specFieldID IN (SELECT specFieldID FROM FilterGroup WHERE ID in ({$filterGroupIdsString})) GROUP BY specFieldID");
         $specFieldValuesResultCount = count($specFieldValuesResultArray);
         foreach ($filtersGroupsSet as $filterGroup) {
             $filterGroupArray = $filterGroup->toArray();
             $filterGroupArray['filtersCount'] = 0;
             $field = $filterGroup->specField->get();
             if ($field->isDate() || $field->isSimpleNumbers()) {
                 for ($i = 0; $i < $filtersResultCount; $i++) {
                     if ($filtersResultArray[$i]['filterGroupID'] == $filterGroupArray['ID']) {
                         $filterGroupArray['filtersCount'] = $filtersResultArray[$i]['filtersCount'];
                     }
                 }
             } else {
                 for ($i = 0; $i < $specFieldValuesResultCount; $i++) {
                     if ($specFieldValuesResultArray[$i]['specFieldID'] == $filterGroupArray['SpecField']['ID']) {
                         $filterGroupArray['filtersCount'] = $specFieldValuesResultArray[$i]['filtersCount'];
                     }
                 }
             }
             $filtersGroupsArray[] = $filterGroupArray;
         }
     }
     return $filtersGroupsArray;
 }

 protected function prepareUpdateQueries(ClonedStore $store)
 {
     ActiveRecord::executeUpdate('UPDATE ' . $this->importDatabase . '.Manufacturer SET protectedFields="|defaultImageID|"');
     foreach ($this->tables as $table) {
         $rows = ActiveRecord::getDataBySQL('SELECT * FROM ' . $this->importDatabase . '.' . $table);
         foreach ($this->insertTable($table, $rows) as $query) {
             $store->addQuery($query);
         }
     }
 }