PHP oxDb类代码示例

 /**
  * Adds selection lists to article.
  */
 public function addSel()
 {
     $aAddSel = $this->_getActionIds('oxselectlist.oxid');
     $soxId = oxRegistry::getConfig()->getRequestParameter('synchoxid');
     // adding
     if (oxRegistry::getConfig()->getRequestParameter('all')) {
         $sSLViewName = $this->_getViewName('oxselectlist');
         $aAddSel = $this->_getAll($this->_addFilter("select {$sSLViewName}.oxid " . $this->_getQuery()));
     }
     if ($soxId && $soxId != "-1" && is_array($aAddSel)) {
         $oDb = oxDb::getDb();
         foreach ($aAddSel as $sAdd) {
             $oNew = oxNew("oxbase");
             $oNew->init("oxobject2selectlist");
             $sObjectIdField = 'oxobject2selectlist__oxobjectid';
             $sSelectetionIdField = 'oxobject2selectlist__oxselnid';
             $sOxSortField = 'oxobject2selectlist__oxsort';
             $oNew->{$sObjectIdField} = new oxField($soxId);
             $oNew->{$sSelectetionIdField} = new oxField($sAdd);
             $sSql = "select max(oxsort) + 1 from oxobject2selectlist where oxobjectid =  {$oDb->quote($soxId)} ";
             $oNew->{$sOxSortField} = new oxField((int) $oDb->getOne($sSql, false, false));
             $oNew->save();
         }
     }
 }

 public function tearDown()
 {
     oxDb::getDb()->execute("delete from oxcategories where oxid like '_test%'");
     oxDb::getDb()->execute("delete from oxarticles where oxid like '_test%'");
     oxDb::getDb()->execute("delete from oxobject2category where oxobjectid like '_test%'");
     oxDb::getDb()->execute("delete from oxseo where oxobjectid like '_test%'");
 }

 /**
  * Vergibt eine ArtikelID für den Pfandartikel und schreibt den Pfandpreis in die DB
  */
 protected function PfandArtikelID($price)
 {
     /* $oxLang = oxLang::getInstance(); */
     // bis CE 4.8.9
     $oxLang = oxRegistry::getLang();
     // ab CE 4.9.0
     $title = $oxLang->translateString('VTEC_PFAND', 0);
     /* $vtec_mwst = oxConfig::getInstance()->getConfigParam('vtec_pfand_mwst');  */
     // bis CE 4.8.9
     $vtec_mwst = oxRegistry::getConfig()->getConfigParam('vtec_pfand_mwst');
     // ab CE 4.9.0
     $sSelect = "SELECT oxid FROM oxarticles WHERE oxtitle = '" . $title . "' AND oxprice = '" . $price . "' LIMIT 1";
     $qResult = oxDb::getDb(ADODB_FETCH_ASSOC)->getOne($sSelect);
     if ($qResult == false || $qResult == null) {
         $oArticle = oxNew("oxarticle");
         $aLangs = $oxLang->getLanguageIds();
         $oArticle->assign(array('oxarticles__active' => 1, 'oxarticles__oxissearch' => 0, 'oxarticles__oxprice' => $price, 'oxarticles__oxpricea' => $price, 'oxarticles__oxpriceb' => $price, 'oxarticels__oxpricec' => $price, 'oxarticles__oxpic1' => 'pfand.jpg', 'oxarticles__oxvat' => $vtec_mwst));
         $oArticle->save();
         //foreach ($aLangs as $iLang){
         for ($i = 0; $i < count($aLangs); $i++) {
             $oArticle->setLanguage($i);
             $oArticle->assign(array("oxarticles__oxtitle" => $oxLang->translateString('VTEC_PFAND', $i)));
             $oArticle->save();
         }
         $qResult = $oArticle->oxarticles__oxid->value;
     }
     return $qResult;
 }

 /**
  * Get state title by id
  *
  * @param integer|string $iStateId
  *
  * @return string
  */
 public function getTitleById($iStateId)
 {
     $oDb = oxDb::getDb();
     $sQ = "SELECT oxtitle FROM " . getViewName("oxstates") . " WHERE oxid = " . $oDb->quote($iStateId);
     $sStateTitle = $oDb->getOne($sQ);
     return (string) $sStateTitle;
 }

 /**
  *  Setup: Prepare data - create need tables
  */
 public function setUp()
 {
     oePayPalEvents::addOrderPaymentsTable();
     oePayPalEvents::addOrderTable();
     oxDb::getDb()->execute('TRUNCATE TABLE `oepaypal_order`');
     oxDb::getDb()->execute('TRUNCATE TABLE `oepaypal_orderpayments`');
 }

 /**
  * Tear down the fixture.
  *
  * @return null
  */
 protected function tearDown()
 {
     $this->cleanUpTable('oxuser');
     oxDb::getDb()->execute("delete from oxremark where oxparentid = '_testArt'");
     oxDb::getDb()->execute("delete from oxnewssubscribed where oxuserid = '_testArt'");
     parent::tearDown();
 }

 /**
  * Adds category to Attributes list
  */
 public function addCatToAttr()
 {
     $aAddCategory = $this->_getActionIds('oxcategories.oxid');
     $soxId = oxRegistry::getConfig()->getRequestParameter('synchoxid');
     $oAttribute = oxNew("oxattribute");
     // adding
     if (oxRegistry::getConfig()->getRequestParameter('all')) {
         $sCatTable = $this->_getViewName('oxcategories');
         $aAddCategory = $this->_getAll($this->_addFilter("select {$sCatTable}.oxid " . $this->_getQuery()));
     }
     if ($oAttribute->load($soxId) && is_array($aAddCategory)) {
         $oDb = oxDb::getDb();
         foreach ($aAddCategory as $sAdd) {
             $oNewGroup = oxNew("oxbase");
             $oNewGroup->init("oxcategory2attribute");
             $sOxSortField = 'oxcategory2attribute__oxsort';
             $sObjectIdField = 'oxcategory2attribute__oxobjectid';
             $sAttributeIdField = 'oxcategory2attribute__oxattrid';
             $sOxIdField = 'oxattribute__oxid';
             $oNewGroup->{$sObjectIdField} = new oxField($sAdd);
             $oNewGroup->{$sAttributeIdField} = new oxField($oAttribute->{$sOxIdField}->value);
             $sSql = "select max(oxsort) + 1 from oxcategory2attribute where oxobjectid = '{$sAdd}' ";
             $oNewGroup->{$sOxSortField} = new oxField((int) $oDb->getOne($sSql, false, false));
             $oNewGroup->save();
         }
     }
     $this->resetContentCache();
 }

 /**
  * Is called on module deactivation. Deletes the theme settings. Note that after deactivation the settings
  * will be lost.
  */
 public static function onDeactivate()
 {
     $iShopId = oxRegistry::getConfig()->getShopId();
     $sThemeName = self::_getThemeName();
     $sDeleteSQL = "\n            DELETE\n                oxconfig.*,\n                oxconfigdisplay.*\n            FROM `oxconfig`\n                LEFT JOIN `oxconfigdisplay`\n                    ON ( `oxconfig`.`OXID` = `oxconfigdisplay`.`OXID` )\n            WHERE `oxconfig`.`OXMODULE` = ? AND `oxconfig`.`oxshopid` = ?\n        ";
     oxDb::getDb()->Execute($sDeleteSQL, array('theme:' . $sThemeName, $iShopId));
 }

 /**
  * Set up
  */
 protected function setUp()
 {
     parent::setUp();
     // fix for state ID compatability between editions
     $sSqlState = "REPLACE INTO `oxstates` (`OXID`, `OXCOUNTRYID`, `OXTITLE`, `OXISOALPHA2`, `OXTITLE_1`, `OXTITLE_2`, `OXTITLE_3`, `OXTIMESTAMP`) " . "VALUES ('333', '8f241f11096877ac0.98748826', 'USA last state', 'SS', 'USA last state', '', '', CURRENT_TIMESTAMP);";
     oxDb::getDb()->execute($sSqlState);
 }

 /**
  * Executes parent method parent::render(), creates oxpricealarm object
  * and passes it's data to Smarty engine. Returns name of template file
  * "pricealarm_main.tpl".
  *
  * @return string
  */
 public function render()
 {
     $config = $this->getConfig();
     parent::render();
     $shopId = $config->getShopId();
     //articles price in subshop and baseshop can be different
     $this->_aViewData['iAllCnt'] = 0;
     $query = "\n            SELECT oxprice, oxartid\n            FROM oxpricealarm\n            WHERE oxsended = '000-00-00 00:00:00' AND oxshopid = '{$shopId}' ";
     $result = oxDb::getDb()->execute($query);
     if ($result != false && $result->recordCount() > 0) {
         $simpleCache = array();
         while (!$result->EOF) {
             $price = $result->fields[0];
             $articleId = $result->fields[1];
             if (isset($simpleCache[$articleId])) {
                 if ($simpleCache[$articleId] <= $price) {
                     $this->_aViewData['iAllCnt'] += 1;
                 }
             } else {
                 $article = oxNew("oxArticle");
                 if ($article->load($articleId)) {
                     $articlePrice = $simpleCache[$articleId] = $article->getPrice()->getBruttoPrice();
                     if ($articlePrice <= $price) {
                         $this->_aViewData['iAllCnt'] += 1;
                     }
                 }
             }
             $result->moveNext();
         }
     }
     return "pricealarm_mail.tpl";
 }

 /**
  * returns delivery set id
  *
  * @param string $sTitle delivery name
  *
  * @return string
  */
 public function getIdByName($sTitle)
 {
     $oDb = oxDb::getDb();
     $sQ = "SELECT `oxid` FROM `" . getViewName('oxdeliveryset') . "` WHERE  `oxtitle` = " . $oDb->quote($sTitle);
     $sId = $oDb->getOne($sQ);
     return $sId;
 }

 /**
  * Executes parent method parent::render(), creates oxuser, oxlist and
  * oxRemark objects, passes data to Smarty engine and returns name of
  * template file "user_remark.tpl".
  *
  * @return string
  */
 public function render()
 {
     parent::render();
     $soxId = $this->getEditObjectId();
     $sRemoxId = oxRegistry::getConfig()->getRequestParameter("rem_oxid");
     if ($soxId != "-1" && isset($soxId)) {
         // load object
         $oUser = oxNew("oxuser");
         $oUser->load($soxId);
         $this->_aViewData["edit"] = $oUser;
         // all remark
         $oRems = oxNew("oxlist");
         $oRems->init("oxremark");
         $sQuotedUserId = oxDb::getDb()->quote($oUser->getId());
         $sSelect = "select * from oxremark where oxparentid=" . $sQuotedUserId . " order by oxcreate desc";
         $oRems->selectString($sSelect);
         foreach ($oRems as $key => $val) {
             if ($val->oxremark__oxid->value == $sRemoxId) {
                 $val->selected = 1;
                 $oRems[$key] = $val;
                 break;
             }
         }
         $this->_aViewData["allremark"] = $oRems;
         if (isset($sRemoxId)) {
             $oRemark = oxNew("oxRemark");
             $oRemark->load($sRemoxId);
             $this->_aViewData["remarktext"] = $oRemark->oxremark__oxtext->value;
             $this->_aViewData["remarkheader"] = $oRemark->oxremark__oxheader->value;
         }
     }
     return "user_remark.tpl";
 }

 /**
  * Returns ongr version id.
  *
  * @return int
  */
 public function getONGRVersionId()
 {
     $oDb = oxDb::getDb();
     $sql = 'SELECT ID FROM ongr_sync_jobs WHERE OXID=';
     $sql .= $oDb->quote($this->getId()) . ' ORDER BY ID DESC LIMIT 1';
     return (int) $oDb->getOne($sql);
 }

 public function jxGetCategoryList($sParent, $sNoPath, $sCatPath)
 {
     $myConfig = $this->getConfig();
     if (!empty($sNoPath)) {
         $sNoPath .= '.';
         $sCatPath .= ' / ';
     }
     $sWhere = "";
     if ($myConfig->getConfigParam('sJxGTaxoDisplayInactive') == FALSE) {
         $sWhere .= "AND c.oxactive = 1 ";
     }
     if ($myConfig->getConfigParam('sJxGTaxoDisplayHidden') == FALSE) {
         $sWhere .= "AND c.oxhidden = 0 ";
     }
     $sSql = "SELECT c.oxid, c.oxtitle, c.oxactive, c.oxhidden, " . "(SELECT COUNT(*) FROM oxobject2category o2c WHERE o2c.oxcatnid = c.oxid) AS artcount, " . "(SELECT COUNT(*) FROM oxcategories c1 WHERE c1.oxparentid=c.oxid) AS count, c.jxamazoncategory AS taxonomy " . "FROM oxcategories c " . "WHERE c.oxparentid = '{$sParent}' " . $sWhere . "ORDER BY c.oxtitle";
     $oDb = oxDb::getDb(oxDB::FETCH_MODE_ASSOC);
     $rs = $oDb->Execute($sSql);
     $i = 1;
     while (!$rs->EOF) {
         $aCols = $rs->fields;
         $aCols['path'] = $sNoPath . $i;
         $aCols['oxtitle'] = $sCatPath . $aCols['oxtitle'];
         array_push($this->aCategories, $aCols);
         if ($aCols['count'] != 0) {
             $this->jxGetCategoryList($aCols['oxid'], $aCols['path'], $aCols['oxtitle']);
         }
         $rs->MoveNext();
         $i++;
     }
     return;
 }

 /**
  * Executes the current command.
  *
  * @param InputInterface $input An InputInterface instance
  * @param OutputInterface $output An OutputInterface instance
  */
 protected function execute(InputInterface $input, OutputInterface $output)
 {
     // allow empty password
     $dbPwd = \oxRegistry::getConfig()->getConfigParam('dbPwd');
     if (!empty($dbPwd)) {
         $dbPwd = '-p' . $dbPwd;
     }
     $file = $input->getOption('file');
     if (!empty($file)) {
         $file = "> " . $file;
     } else {
         $file = "";
     }
     if ($input->getOption('ignoreViews')) {
         $dbName = \oxRegistry::getConfig()->getConfigParam('dbName');
         $viewsResultArray = \oxDb::getDb()->getArray("SHOW FULL TABLES IN {$dbName} WHERE TABLE_TYPE LIKE 'VIEW'");
         $ignoreViewTables = array();
         foreach ($viewsResultArray as $viewArray) {
             $ignoreViewTables[] = '--ignore-table=' . $dbName . '.' . $viewArray[0];
         }
         $ignoreViewTables = implode(' ', $ignoreViewTables);
     }
     $exec = sprintf("mysqldump -h%s %s -u%s %s %s %s 2>&1", \oxRegistry::getConfig()->getConfigParam('dbHost'), $dbPwd, \oxRegistry::getConfig()->getConfigParam('dbUser'), \oxRegistry::getConfig()->getConfigParam('dbName'), $ignoreViewTables, $file);
     exec($exec, $commandOutput, $returnValue);
     if ($returnValue > 0) {
         $output->writeln('<error>' . implode(PHP_EOL, $commandOutput) . '</error>');
         return;
     }
     if (!empty($file)) {
         $output->writeln("<info>Dump {$input->getOption('file')} created.</info>");
     } else {
         $output->writeln($commandOutput);
     }
 }