PHP Foundry::db方法代码示例

 public function main()
 {
     $db = Foundry::db();
     $sql = $db->sql();
     $query = "update `#__social_stream` as a";
     $query .= ' inner join `#__social_clusters` as b on a.`cluster_id` = b.`id`';
     $query .= ' set a.`cluster_access` = b.`type`';
     $query .= ' where a.`cluster_id` != 0 and a.`cluster_access` = 1';
     $sql->raw($query);
     $db->setQuery($sql);
     $state = $db->query();
     return $state;
 }

    public function getProductsCount($userId, $storeid = '')
    {
        $db = Foundry::db();
        $sql = $db->sql();
        $sql = 'SELECT COUNT(i.item_id)
		 FROM `#__kart_items` as i, `#__kart_store` as s
		 WHERE s.id = i.store_id
		 AND i.state = 1
		 AND `owner` = ' . $userId;
        if ($storeid) {
            $sql .= ' AND i.store_id = ' . $storeid;
        }
        $sql .= " AND i.parent = 'com_quick2cart'";
        $db->setQuery($sql);
        $result = $db->loadResult();
        return $result;
    }

 public function main()
 {
     // determine what privacy rule to used for each context_type
     $context = array();
     // $context['photos'] = 'photos.view';
     // $context['discuss'] = 'core.view';
     // $context['kunena'] = 'core.view';
     // $context['k2'] = 'core.view';
     // $context['komento'] = 'core.view';
     // $context['blog'] = 'easyblog.blog.view';
     // $context['badges'] = 'core.view';
     // $context['friends'] = 'core.view';
     // $context['links'] = 'story.view';
     // $context['profiles'] = 'core.view';
     // $context['shares'] = 'core.view';
     // $context['story'] = 'story.view';
     // $context['calendar'] = 'core.view';
     // $context['users'] = 'core.view';
     // $context['facebook'] = 'core.view';
     // $context['apps'] = 'core.view';
     // $context['article'] = 'core.view';
     // $context['feeds'] = 'core.view';
     // $context['followers'] = 'followers.view';
     // $context['notes'] = 'core.view';
     // $context['relationship'] = 'core.view';
     //
     $context['photos'] = 'photos.view';
     $context['blog'] = 'easyblog.blog.view';
     $context['links'] = 'story.view';
     $context['story'] = 'story.view';
     $context['followers'] = 'followers.view';
     $state = true;
     $db = Foundry::db();
     $sql = $db->sql();
     // stored function created. lets get the privacy rules.
     $query = "select `id`, concat( `type`, '.', `rule`) as `rule`, `value` from `#__social_privacy`";
     $sql->raw($query);
     $db->setQuery($sql);
     $results = $db->loadObjectList();
     $privacy = array();
     foreach ($results as $item) {
         $privacy[$item->rule] = $item;
     }
     // lets update the privacy based on user defined privacy which having the one-to-one relationship in stream table vs stream_item table.
     $query = "update `#__social_stream` as s";
     $query .= "    inner join (select b.`uid`, c.`value`, c.`privacy_id` from `#__social_stream_item` as b";
     $query .= "        inner join `#__social_privacy_items` as c on c.`uid` = b.`context_id` and c.`type` = b.`context_type`";
     $query .= "        where `type` != 'story'";
     $query .= "        group by b.`uid`, c.`value`, c.`privacy_id` having (count(b.`uid`) = 1) ) as x on s.`id` = x.`uid`";
     $query .= "    set s.`access` = x.`value`,";
     $query .= "        s.`privacy_id` = x.`privacy_id`";
     $query .= "    where s.`privacy_id` = 0";
     $sql->clear();
     $sql->raw($query);
     $db->setQuery($sql);
     $db->query();
     // update story privacy based on user defined value for only story type which the privacy is lower than custom
     $query = "update `#__social_stream` as a";
     $query .= " inner join `#__social_stream_item` as b on a.`id` = b.`uid`";
     $query .= " inner join `#__social_privacy_items` as c on c.`uid` = b.`uid` and c.`type` IN ('story', 'links')";
     $query .= " set a.`access` = c.`value`,";
     $query .= "     a.`privacy_id` = c.`privacy_id`";
     $query .= " where a.`privacy_id` = 0";
     $query .= " and c.`value` < 100";
     $sql->clear();
     $sql->raw($query);
     $db->setQuery($sql);
     $db->query();
     // update privacy based on user defined value for only story type.
     $query = "update `#__social_stream` as a";
     $query .= " inner join `#__social_stream_item` as b on a.`id` = b.`uid`";
     $query .= " inner join `#__social_privacy_items` as c on c.`uid` = b.`uid` and c.`type` IN ('story', 'links')";
     $query .= " set a.`access` = c.`value`,";
     $query .= "     a.`privacy_id` = c.`privacy_id`,";
     $query .= "     a.`custom_access` = (" . $this->genCustomItemAccess('c.`privacy_id`', 'b.`actor_id`', 'b.`uid`', 'b.`context_type`') . ")";
     $query .= " where a.`privacy_id` = 0";
     $query .= " and c.`value` = 100";
     $sql->clear();
     $sql->raw($query);
     $db->setQuery($sql);
     $db->query();
     // // update privacy based on user defined value for only story type.
     $query = "update `#__social_stream` as a";
     $query .= "  inner join `#__social_stream_item` as b on a.`id` = b.`uid`";
     $query .= "  inner join `#__social_privacy_items` as c on c.`uid` = b.`id` and c.`type` = 'activity'";
     $query .= " set a.`access` = c.`value`,";
     $query .= "     a.`privacy_id` = c.`privacy_id`,";
     $query .= "     a.`custom_access` = (" . $this->genCustomItemAccess('c.`privacy_id`', 'b.`actor_id`', 'b.`uid`', 'b.`context_type`') . ")";
     $query .= " where a.`privacy_id` = 0";
     $query .= " and c.`value` = 100";
     $sql->clear();
     $sql->raw($query);
     $db->setQuery($sql);
     $db->query();
     // // update stream for type other than story custom access
     $query = "update `#__social_stream` as a";
     $query .= " inner join `#__social_stream_item` as b on a.`id` = b.`uid`";
     $query .= "     set a.`custom_access` = (" . $this->genCustomItemAccess('a.`privacy_id`', 'a.`actor_id`', 'b.`context_id`', 'b.`context_type`') . ")";
     $query .= "     where a.`context_type` != 'story'";
     $query .= "     and a.`access` = 100";
//.........这里部分代码省略.........

 private function processFBConnect()
 {
     $db = Foundry::db();
     $sql = $db->sql();
     // update user type
     $query = "update `#__social_users` set `type` = 'facebook' where `user_id` in (select `userid` from `#__community_connect_users` where `type` = 'facebook')";
     $sql->raw($query);
     $db->setQuery($sql);
     $db->query();
     // now we need to add the records into oauth table.
     $query = "insert into `#__social_oauth` (`oauth_id`, `uid`, `type`, `client`, `pull`, `push`, `created`)";
     $query .= " select b.`connectid`, b.`userid`, 'user', b.`type`, '0', '0', now() from `#__community_connect_users` as b";
     $query .= " \twhere b.`userid` not in ( select c.`uid` from `#__social_oauth` as c where c.`type` = 'user' and c.`client` = 'facebook' )";
     $sql->clear();
     $sql->raw($query);
     $db->setQuery($sql);
     $db->query();
     return null;
 }