PHP SJB_DB::queryExec方法代码示例

 function getObjectInfo($sorting_fields, $inner_join = false, $relevance = false)
 {
     $SearchSqlTranslator = new SJB_GuestAlertSearchSQLTranslator($this->table_prefix);
     $sql_string = $SearchSqlTranslator->buildSqlQuery($this->criteria, $this->valid_criterion_number, $sorting_fields, $this->inner_join);
     if ($this->sorting_field !== false && $this->sorting_order !== false) {
         $sql_string .= ' ORDER BY ' . $this->sorting_field . ' ' . $this->sorting_order . ' ';
     }
     SJB_DB::queryExec($sql_string);
     $affectedRows = SJB_DB::getAffectedRows();
     if ($this->limit !== false) {
         if (isset($this->limit['limit'])) {
             $sql_string .= 'limit ' . $this->limit['limit'] . ', ' . $this->limit['num_rows'];
         } else {
             $sql_string .= 'limit ' . $this->limit . ', 100';
         }
     }
     $sql_results = SJB_DB::query($sql_string);
     $result = array();
     foreach ($sql_results as $sql_result) {
         if ($this->valid_criterion_number == 0 || $sql_result['countRows'] == $this->valid_criterion_number) {
             $result[]['object_sid'] = $sql_result['object_sid'];
         }
     }
     $this->affectedRows = $affectedRows - (SJB_DB::getAffectedRows() - count($result));
     return $result;
 }

 function getSQLValue()
 {
     $fileId = $this->property_info['id'] . '_' . $this->object_sid;
     $this->property_info['value'] = $fileId;
     SJB_DB::queryExec("UPDATE uploaded_files SET id = ?s WHERE  id = ?s LIMIT 1", $fileId, $this->property_info['id'] . '_tmp');
     if (SJB_UploadFileManager::doesFileExistByID($fileId)) {
         return $fileId;
     }
     return '';
 }

 function getObjectInfo($sorting_fields, $inner_join = false, $relevance = false)
 {
     $searchSqlTranslator = new SJB_InvoiceSearchSQLTranslator($this->table_prefix);
     $sqlString = $searchSqlTranslator->buildSqlQuery($this->criteria, $this->valid_criterion_number, $sorting_fields, $this->inner_join);
     $where = '';
     if ($this->sorting_field !== false && $this->sorting_order !== false) {
         $sqlString .= $where . "ORDER BY " . $this->sorting_field . " " . $this->sorting_order . " ";
     }
     SJB_DB::queryExec($sqlString);
     $this->affectedRows = SJB_DB::getAffectedRows();
     if ($this->limit !== false) {
         if (isset($this->limit['limit'])) {
             $sqlString .= "limit " . $this->limit['limit'] . ", " . $this->limit['num_rows'];
         } else {
             $sqlString .= "limit " . $this->limit . ", 100";
         }
     }
     return SJB_DB::query($sqlString);
 }

 public static function setListingApprovalStatus($listingSids, $status, $updateBrowsePages = true)
 {
     $statusValues = array('pending', 'approved', 'rejected');
     if (in_array($status, $statusValues)) {
         if (!is_array($listingSids)) {
             $listingSids = array($listingSids);
         }
         if ($updateBrowsePages) {
             SJB_BrowseDBManager::deleteListings($listingSids);
         }
         switch ($status) {
             case 'pending':
                 // set status to 'pending' and clear reject reason
                 SJB_DB::queryExec("UPDATE `listings` SET `status`=?s, `reject_reason` = '' WHERE `sid` IN (?l)", $status, $listingSids);
                 break;
             case 'approved':
                 SJB_DB::queryExec("UPDATE `listings` SET `status`=?s WHERE `sid` IN (?l)", $status, $listingSids);
                 break;
             case 'rejected':
                 $rejectReason = $_REQUEST['rejectReason'] != '' ? $_REQUEST['rejectReason'] : 'rejected with no reason';
                 SJB_DB::queryExec("UPDATE `listings` SET `status`=?s, `reject_reason` = ?s WHERE `sid` IN (?l)", $status, $rejectReason, $listingSids);
                 break;
         }
         SJB_Cache::getInstance()->clean('matchingAnyTag', array(SJB_Cache::TAG_LISTINGS));
         if ($updateBrowsePages && $status == 'approved') {
             SJB_BrowseDBManager::addListings($listingSids);
         }
     }
 }

 function getObjectInfo($sorting_fields, $inner_join = false, $relevance = false)
 {
     $SearchSqlTranslator = new SJB_EmailLogSearchSQLTranslator($this->table_prefix);
     $sql_string = $SearchSqlTranslator->buildSqlQuery($this->criteria, $this->valid_criterion_number, array($this->sorting_field => $this->sorting_order));
     SJB_DB::queryExec($sql_string);
     $this->affectedRows = SJB_DB::getAffectedRows();
     if ($this->limit !== false) {
         if (isset($this->limit['limit'])) {
             $sql_string .= " LIMIT " . $this->limit['limit'] . ", " . $this->limit['num_rows'];
         } else {
             $sql_string .= " LIMIT " . $this->limit . ", 100";
         }
     }
     return SJB_DB::query($sql_string);
 }

 public static function updateFeedToken($sid, $token)
 {
     SJB_DB::queryExec('UPDATE `twitter_feeds` SET `access_token` = ?s WHERE `sid` = ?n', serialize($token), $sid);
 }

 private static function updatePageTitleByUri($title, $uri)
 {
     SJB_DB::queryExec("UPDATE `pages` SET `title` = ?s WHERE `uri` = ?s", $title, $uri);
 }

 public static function addTreeItemToBeginByParentSID($field_sid, $parent_sid, $tree_item_value)
 {
     $parent_level = SJB_UserProfileFieldTreeManager::getTreeItemLevelBySID($parent_sid);
     $level = $parent_level + 1;
     $min_order = SJB_DB::queryValue("SELECT MIN(`order`) FROM user_profile_field_tree WHERE field_sid = ?n AND parent_sid = ?n", $field_sid, $parent_sid);
     $min_order = empty($min_order) ? 0 : $min_order;
     $new_order = $min_order;
     SJB_DB::queryExec("UPDATE user_profile_field_tree SET `order` = `order` + 1 WHERE parent_sid = ?n", $parent_sid);
     return SJB_DB::query("INSERT INTO user_profile_field_tree SET field_sid = ?n, parent_sid = ?n, level = ?n, caption = ?s, `order` = ?n", $field_sid, $parent_sid, $level, $tree_item_value, $new_order);
 }

 public function execute()
 {
     $tp = SJB_System::getTemplateProcessor();
     $errors = array();
     $template = 'flag_listing_settings.tpl';
     $form_submitted = SJB_Request::getVar('submit');
     $action = SJB_Request::getVar('action');
     $itemSID = SJB_Request::getVar('item_sid');
     switch ($action) {
         case 'save':
             $saveValue = trim(SJB_Request::getVar('new_value'));
             $listingTypesArray = SJB_Request::getVar('flag_listing_types');
             $typesForSave = '';
             // make string to save
             if (!empty($listingTypesArray)) {
                 $typesForSave = implode(',', $listingTypesArray);
             }
             if (empty($saveValue)) {
                 $errors['PLEASE_ENTER_FLAG_REASON'] = 'Please enter flag reason';
             }
             if (empty($errors) && empty($typesForSave)) {
                 $errors['PLEASE_SELECT_LISTING_TYPE'] = 'Please select listing type';
             }
             if (empty($errors)) {
                 if (!$itemSID) {
                     // ADD NEW ITEM
                     $result = SJB_DB::query('SELECT `sid` FROM `flag_listing_settings` WHERE `listing_type_sid` = ?s AND `value` = ?s LIMIT 1', $typesForSave, $saveValue);
                     if (!empty($result)) {
                         $errors['THIS_FLAG_REASON_ALREADY_EXISTS_IN_THE_SYSTEM'] = 'This flag reason already exists in the system';
                     } else {
                         SJB_DB::queryExec('INSERT INTO `flag_listing_settings` SET `listing_type_sid` = ?s, `value` = ?s', $typesForSave, $saveValue);
                     }
                 } else {
                     // UPDATE ITEM
                     SJB_DB::queryExec('UPDATE `flag_listing_settings` SET `value` = ?s, `listing_type_sid` = ?s WHERE `sid` = ?n', $saveValue, $typesForSave, $itemSID);
                 }
                 if ($form_submitted == 'save') {
                     SJB_HelperFunctions::redirect(SJB_System::getSystemSettings('SITE_URL') . '/flag-listing-settings/');
                 } elseif ($form_submitted == 'apply') {
                     SJB_HelperFunctions::redirect(SJB_System::getSystemSettings('SITE_URL') . '/flag-listing-settings/?item_sid=' . $itemSID . '&action=edit');
                 }
             }
             break;
         case 'delete':
             SJB_DB::query('DELETE FROM `flag_listing_settings` WHERE `sid` = ?n', $itemSID);
             SJB_HelperFunctions::redirect(SJB_System::getSystemSettings('SITE_URL') . '/flag-listing-settings/');
             break;
         case 'edit':
             $currentItem = SJB_DB::query('SELECT * FROM `flag_listing_settings` WHERE `sid` = ?n LIMIT 1', $itemSID);
             $template = 'flag_listing_settings_edit.tpl';
             if ($currentItem) {
                 $currentItem = array_pop($currentItem);
                 $typesArray = explode(',', $currentItem['listing_type_sid']);
                 $currentItem['listing_type_sid'] = $typesArray;
             }
             $tp->assign('current_setting', $currentItem);
             break;
     }
     // Need to select listing type
     $listingTypes = SJB_ListingTypeManager::getAllListingTypesInfo();
     $types = array();
     foreach ($listingTypes as $elem) {
         $types[$elem['sid']] = $elem;
     }
     $listingTypes = $types;
     $tp->assign('listing_types', $listingTypes);
     $settings = SJB_DB::query('SELECT * FROM `flag_listing_settings`');
     foreach ($settings as $key => $elem) {
         $settings[$key]['listing_type_sid'] = explode(',', $elem['listing_type_sid']);
     }
     $tp->assign('settings', $settings);
     $tp->assign('errors', $errors);
     $tp->display($template);
 }

 function getObjectInfo($sorting_fields, $inner_join = false, $relevance = false)
 {
     if (isset($this->inner_join['contracts'])) {
         $this->searchSqlTranslator->setDistinct(true);
     }
     $sql_string = $this->searchSqlTranslator->buildSqlQuery($this->criteria, $this->valid_criterion_number, $sorting_fields, $this->inner_join);
     $where = '';
     $groupBy = '';
     if ($this->sorting_field !== false && $this->sorting_order !== false) {
         $sorting = '';
         if ($this->inner_join) {
             foreach ($this->inner_join as $key => $val) {
                 if (isset($val['sort_field'])) {
                     if (isset($val['noPresix'])) {
                         $this->sorting_field = $val['sort_field'];
                     } else {
                         $this->sorting_field = "`" . $key . "`." . $val['sort_field'];
                     }
                 }
                 if (isset($val['where'])) {
                     $where .= " {$val['where']} ";
                 }
                 if (isset($val['groupBy'])) {
                     $groupBy .= " GROUP BY {$val['groupBy']} ";
                 }
                 if (isset($val['join']) && $val['join'] != 'INNER JOIN') {
                     $this->searchSqlTranslator->setDistinct(true);
                 }
             }
         }
         if (is_array($this->sorting_field)) {
             $i = 0;
             foreach ($this->sorting_field as $sorting_field) {
                 $sorting .= " {$sorting_field} {$this->sorting_order}";
                 if ($i == 0) {
                     $sorting .= ', ';
                 }
                 $i++;
             }
         } else {
             $sorting = $this->sorting_field . " " . $this->sorting_order;
         }
         $sql_string .= $where . " {$groupBy} ORDER BY  {$sorting} ";
     }
     $affectedRows = 0;
     if ($this->limit !== false) {
         SJB_DB::queryExec($sql_string);
         $affectedRows = SJB_DB::getAffectedRows();
         if (isset($this->limit['limit'])) {
             $sql_string .= "limit " . $this->limit['limit'] . ", " . $this->limit['num_rows'];
         } else {
             $sql_string .= "limit " . $this->limit . ", 100";
         }
     }
     $sql_results = SJB_DB::query($sql_string);
     if ($this->limit === false) {
         $affectedRows = SJB_DB::getAffectedRows();
     }
     $result = array();
     foreach ($sql_results as $sql_result) {
         if ($this->valid_criterion_number == 0 || $sql_result['countRows'] == $this->valid_criterion_number) {
             $result[]['object_sid'] = $sql_result['object_sid'];
         }
     }
     $this->affectedRows = $affectedRows - (SJB_DB::getAffectedRows() - count($result));
     // TODO написала это потому что в browseCompany неправильно считается общее количество компаний. Например по факту находится одна компания, но пишется, что найдено 16.
     if ($this->limitByPHP !== false) {
         $newArr = $result;
         $result = array();
         for ($i = $this->limitByPHP['limit']; $i < $this->limitByPHP['limit'] + $this->limitByPHP['num_rows']; $i++) {
             if (!isset($newArr[$i])) {
                 break;
             }
             $result[$i] = $newArr[$i];
         }
     }
     return $result;
 }

 public static function ifUserIsRegisteredByReferenceUid($referenceUid)
 {
     $result = SJB_DB::query('SELECT `sid` FROM `users` WHERE `reference_uid` = ?s', $referenceUid);
     if (!empty($result)) {
         $result = array_shift($result);
         return $result['sid'];
     } else {
         $userInfo = SJB_DB::query("SELECT `sid`,`reference_uid` FROM `users` WHERE `username` = `email` AND `email` = ?s", self::$oProfile['email']);
         $userInfo = array_pop($userInfo);
         if (!empty($userInfo)) {
             $oldGoogleID = str_replace('google_', '', $userInfo['reference_uid']);
             SJB_DB::queryExec("UPDATE `users` SET `reference_uid` = ?s WHERE `sid` = ?n", $referenceUid, $userInfo['sid']);
             SJB_DB::queryExec('UPDATE `google` SET `google_id` = ?s, `profile_info` = ?s WHERE `google_id` = ?s', self::$oProfile['id'], serialize(self::$oProfile), $oldGoogleID);
             return $userInfo['sid'];
         }
     }
     return false;
 }

 private function extraProperties($user, $userInfo, &$usersID)
 {
     $savedProperties = array('user_group' => 1, 'pictures' => 1);
     foreach ($user->getProperties() as $property) {
         if (!in_array($property->id, array('file', 'Logo', 'video'))) {
             $savedProperties[$property->id] = 1;
         }
     }
     $queryFields = '';
     foreach (array_diff_key($userInfo, $savedProperties) as $key => $value) {
         if ($key == 'id') {
             $usersID[$value] = $user->getSID();
             continue;
         }
         if ($key == 'product') {
             $products = $value ? explode(',', $value) : array();
             $i = sizeof($products);
             while (--$i != -1) {
                 $productProperties = @unserialize($products[$i]);
                 if (!$productProperties) {
                     continue;
                 }
                 $productSid = SJB_ProductsManager::getProductSidByName($productProperties['name']);
                 if (!$productSid) {
                     continue;
                 }
                 $contract = new SJB_Contract(array('product_sid' => $productSid, 'numberOfListings' => $productProperties['number_of_listings']));
                 $contract->setPrice($productProperties['price']);
                 $contract->setCreationDate($productProperties['creation_date']);
                 $contract->setExpiredDate($productProperties['expired_date']);
                 $contract->setStatus($productProperties['status']);
                 $contract->setUserSID($user->getSID());
                 $contract->saveInDB();
                 SJB_ContractSQL::updatePostingsNumber($contract->id, $productProperties['number_of_postings']);
                 SJB_ProductsManager::incrementPostingsNumber($productSid, $productProperties['number_of_postings']);
             }
             continue;
         }
         if (in_array($key, array('file', 'Logo', 'video')) && !empty($value)) {
             $property = $user->getProperty($key);
             if (!$property) {
                 continue;
             }
             $fileProperties = @unserialize($value);
             if (!$fileProperties) {
                 continue;
             }
             $fieldInfo = SJB_UserProfileFieldManager::getFieldInfoBySID($property->sid);
             switch ($key) {
                 case 'file':
                     $value = 'Resume_' . $user->getSID();
                     $this->databaseFileRegister('file', $value, $fileProperties['name'] . '.' . $fileProperties['extension'], $fileProperties['mimeType']);
                     break;
                 case 'Logo':
                     $value = 'Logo_' . $user->getSID();
                     $this->databaseFileRegister('pictures', $value, $fileProperties['name'] . '.' . $fileProperties['extension'], $fileProperties['mimeType']);
                     $this->databaseFileRegister('pictures', 'Logo_' . $user->getSID() . '_thumb', $fileProperties['name'] . '_thumb.' . $fileProperties['extension'], $fileProperties['mimeType']);
                     break;
                 case 'video':
                     $value = 'video_' . $user->getSID();
                     $this->databaseFileRegister('video', 'video_' . $user->getSID(), $fileProperties['name'] . '.' . $fileProperties['extension'], $fileProperties['mimeType']);
                     break;
             }
         }
         if ($key == 'registration_date') {
             $isValid = SJB_UserRegistrationDateValidator::isValid($userInfo['registration_date']);
             if ($isValid !== true) {
                 if (!isset($errors['registrationDate'])) {
                     $errors['registrationDate'][] = $isValid;
                 }
                 if (isset($userInfo['username'])) {
                     $errors['registrationDate'][] = $userInfo['username'] . ', ';
                 }
                 continue;
             }
         }
         if (!empty($value)) {
             $queryFields .= $queryFields ? ", `" . SJB_DB::quote($key) . "` = '" . SJB_DB::quote($value) . "'" : "`" . SJB_DB::quote($key) . "` = '" . SJB_DB::quote($value) . "'";
         }
     }
     if (!empty($queryFields)) {
         SJB_DB::queryExec("UPDATE ?w SET " . $queryFields . " WHERE `sid` = ?n", 'users', $user->getSID());
     }
 }

 public function execute()
 {
     // таблица с данными xml-feeds
     $feedsTable = 'listing_feeds';
     $action = SJB_Request::getVar('action', '');
     $feedId = SJB_Request::getVar('feedId', null);
     $feedsArray = SJB_DB::query("SELECT f.*, f.count as count_listings, l.sid as typeId, l.id as type FROM {$feedsTable} as f LEFT JOIN `listing_types` as l ON (l.sid=f.type) ORDER BY f.sid");
     $listingTypes = SJB_DB::query("SELECT * FROM `listing_types`");
     $siteURL = SJB_System::getSystemSettings('SITE_URL');
     $tmp = strrchr(SJB_System::getSystemSettings('SITE_URL'), "/");
     $siteURL = str_replace($tmp, "", $siteURL);
     $tp = SJB_System::getTemplateProcessor();
     $tp->assign('listingTypes', $listingTypes);
     // данные для создания файлов-шаблонов для feed
     $module = SJB_System::getModuleManager()->getCurrentModuleAndFunction();
     $templatePath = SJB_TemplatePathManager::getAbsoluteTemplatesPath();
     $filePath = $templatePath . "_system/" . $module[0] . "/";
     switch ($action) {
         case 'add':
             // если была отправка формы
             if (isset($_REQUEST['addFeed']) && $_REQUEST['addFeed'] != '') {
                 $feed_name = $_REQUEST['feed_name'];
                 $feed_template = $_REQUEST['feed_template'];
                 $feed_desc = $_REQUEST['feed_desc'];
                 $typeId = $_REQUEST['typeId'];
                 $count_listings = $_REQUEST['count_listings'];
                 $feed_mimetype = $_REQUEST['mime_type'];
                 // создадим шаблон для нового feed
                 $feed_template = preg_replace("/^feed_|\\.tpl\$/", "", $feed_template);
                 $feed_template = "feed_" . $feed_template . ".tpl";
                 $filePath = $filePath . $feed_template;
                 $fileSystem = new SJB_Filesystem();
                 if (!file_exists($filePath)) {
                     $fileSystem->createFile($filePath);
                 } else {
                     $errors[] = array('code' => 'FILE_ALREADY_EXISTS', 'message' => "Template '{$filePath}' already exists!");
                     $tp->assign('feed', array('name' => $feed_name, 'template' => $feed_template, 'description' => $feed_desc, 'type' => $typeId, 'count_listings' => $count_listings, 'mime_type' => $feed_mimetype));
                     $tp->assign('errors', $errors);
                     $tp->display("add_listing_feed.tpl");
                     break;
                 }
                 SJB_DB::queryExec("INSERT INTO {$feedsTable} SET `name`=?s, `template`=?s, `description`=?s, `type`=?n, `count`=?n, `mime_type`=?s", $feed_name, $feed_template, $feed_desc, $typeId, $count_listings, $feed_mimetype);
                 $site_url = SJB_System::getSystemSettings("SITE_URL");
                 SJB_HelperFunctions::redirect($site_url . "/listing-feeds/");
             }
             $tp->display("add_listing_feed.tpl");
             break;
         case 'edit':
             // если была отправка формы редактирования элемента
             if ($form_submitted = SJB_Request::getVar('submit')) {
                 $feed_name = $_REQUEST['feed_name'];
                 $feed_template = $_REQUEST['feed_template'];
                 $feed_desc = $_REQUEST['feed_desc'];
                 $feedId = $_REQUEST['feedId'];
                 $typeId = $_REQUEST['typeId'];
                 $count_listings = $_REQUEST['count_listings'];
                 $feed_mimetype = $_REQUEST['mime_type'];
                 SJB_DB::queryExec("UPDATE {$feedsTable} SET `name`=?s, `template`=?s, `description`=?s, `type`=?n, `count`=?n, `mime_type`=?s WHERE `sid`=?n LIMIT 1", $feed_name, $feed_template, $feed_desc, $typeId, $count_listings, $feed_mimetype, $feedId);
                 $site_url = SJB_System::getSystemSettings("SITE_URL");
                 if ($form_submitted == 'save_info') {
                     SJB_HelperFunctions::redirect($site_url . "/listing-feeds/");
                 }
             }
             $feed = SJB_DB::query("SELECT f.*, f.count as count_listings  FROM {$feedsTable} as f WHERE `sid`=?n", $feedId);
             $feed = array_pop($feed);
             $tp->assign("feed", $feed);
             $tp->display("edit_listing_feed.tpl");
             break;
         case 'delete':
             $feed = SJB_DB::query("SELECT f.*, f.count as count_listings FROM {$feedsTable} as f WHERE `sid`=?n", $feedId);
             $feed = array_pop($feed);
             $filePath = $filePath . $feed['template'];
             $fileSystem = new SJB_Filesystem();
             if (file_exists($filePath)) {
                 $fileSystem->deleteFile($filePath);
             } else {
                 $errors[] = array('code' => 'FILE_NOT_EXISTS', 'message' => "Template for " . $feed['name'] . ": '{$filePath}' not exists!");
             }
             SJB_DB::queryExec("DELETE FROM {$feedsTable} WHERE `sid`=?n LIMIT 1", $feedId);
             $site_url = SJB_System::getSystemSettings("SITE_URL");
             SJB_HelperFunctions::redirect($site_url . "/listing-feeds/");
             break;
     }
     if ($action == '') {
         $tp->assign('siteURL', $siteURL);
         $tp->assign('feeds', $feedsArray);
         $tp->display('listing_feeds.tpl');
     }
 }

 /**
  * When new page with function "browse" was created, save found values into table `browse` using page parameters
  * @param array $page full parammeters of site page with function "browse"
  */
 private static function addBrowse($page)
 {
     $items = self::getItems($page);
     SJB_DB::queryExec("INSERT INTO `browse` (`page_uri`, `parameters`, `data`) VALUES (?s, ?s, ?s)", $page['uri'], $page['parameters'], serialize($items));
 }

    public static function setListingExpirationDateBySid($listing_sid)
    {
        $product_info = SJB_DB::queryValue('SELECT `product_info` FROM `listings` WHERE `sid` = ?n', $listing_sid);
        if (!empty($product_info)) {
            $product_info = unserialize($product_info);
        }
        if (!empty($product_info['listing_duration'])) {
            SJB_DB::queryExec('
				UPDATE `listings` 
				SET `expiration_date` = NOW() + INTERVAL ?n DAY 
				WHERE `sid` = ?n 
				AND (`expiration_date` is NULL OR `expiration_date` < NOW() OR `expiration_date` > (NOW() + INTERVAL ?n DAY))', $product_info['listing_duration'], $listing_sid, $product_info['listing_duration']);
        }
        return true;
    }