<commit_before><?php
/**
 *  ocs-webserver
 *
 *  Copyright 2016 by pling GmbH.
 *
 *    This file is part of ocs-webserver.
 *
 *    This program is free software: you can redistribute it and/or modify
 *    it under the terms of the GNU Affero General Public License as
 *    published by the Free Software Foundation, either version 3 of the
 *    License, or (at your option) any later version.
 *
 *    This program is distributed in the hope that it will be useful,
 *    but WITHOUT ANY WARRANTY; without even the implied warranty of
 *    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *    GNU Affero General Public License for more details.
 *
 *    You should have received a copy of the GNU Affero General Public License
 *    along with this program.  If not, see <http://www.gnu.org/licenses/>.
 **/
class Default_Model_DbTable_ProjectPlings extends Zend_Db_Table_Abstract
{

    protected $_name = "project_plings";


    public function getPlings($project_id)
    {
        $statement = $this->select()
            ->where('project_id=?', $project_id)
            ->where('is_deleted=?', 0);
        return $this->fetchAll($statement);
    }


    public function getPling($project_id,$member_id)
    {
        $statement = $this->select()
            ->where('project_id=?', $project_id)
            ->where('member_id=?', $member_id)
            ->where('is_deleted=?', 0);
        return $this->fetchRow($statement);
    }

    public function setDelete($id)
    {
        /*
        $updateValues = array(
            'is_deleted' => 1,
            'deleted_at' => new Zend_Db_Expr('Now()')
        );
        $this->update($updateValues,  array('project_plings_id = ?' => $id));
    */
        $sql = "update project_plings set is_deleted = 1, deleted_at = now() where project_plings_id = :id";

        $result = Zend_Db_Table::getDefaultAdapter()->query($sql, array('id' => $id))->execute();
       
        return $result;
    }

    public function countPlingsHeGave($member_id)
    {        
        $sql ="
                SELECT count(*) AS count 
                FROM project_plings f   
                WHERE  f.member_id =:member_id and f.is_deleted = 0 and f.is_active = 1
        ";
        $resultRow = $this->_db->fetchRow($sql, array('member_id' => $member_id));
        return $resultRow['count'];
    }

    public function getPlingsAmount($project_id)
    {

       $config = Zend_Registry::get('config');
       $member_id = $config->settings->member->plingcat->id;
       $sql ="
                SELECT count(*) AS count 
                FROM project_plings f   
                WHERE  f.project_id =:project_id and f.is_deleted = 0 and f.is_active = 1 and f.member_id<> :sysuser
        ";
        $resultRow = $this->_db->fetchRow($sql, array('project_id' => $project_id, 'sysuser' => $member_id));
        return $resultRow['count'];
    }

    public function countPlingsHeGotAll($member_id)
    {       
        $sql ="
                SELECT count(*) AS count 
                FROM project_plings f  
                inner join stat_projects p on p.project_id = f.project_id and p.status = 100 
                WHERE  p.member_id =:member_id and f.is_deleted = 0 and f.is_active = 1
        ";
        $resultRow = $this->_db->fetchRow($sql, array('member_id' => $member_id));
        return $resultRow['count'];

    }

    public function countPlingsHeGot($member_id)
    {       
        $sql ="
                SELECT count(*) AS count 
                FROM project_plings f  
                inner join stat_projects p on p.project_id = f.project_id and p.status = 100 
                inner join member m on f.member_id = m.member_id and m.roleid <> 500 
                WHERE  p.member_id =:member_id and f.is_deleted = 0 and f.is_active = 1
        ";
        $resultRow = $this->_db->fetchRow($sql, array('member_id' => $member_id));
        return $resultRow['count'];

    }

}<commit_msg>sql better performance<commit_after><?php
/**
 *  ocs-webserver
 *
 *  Copyright 2016 by pling GmbH.
 *
 *    This file is part of ocs-webserver.
 *
 *    This program is free software: you can redistribute it and/or modify
 *    it under the terms of the GNU Affero General Public License as
 *    published by the Free Software Foundation, either version 3 of the
 *    License, or (at your option) any later version.
 *
 *    This program is distributed in the hope that it will be useful,
 *    but WITHOUT ANY WARRANTY; without even the implied warranty of
 *    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *    GNU Affero General Public License for more details.
 *
 *    You should have received a copy of the GNU Affero General Public License
 *    along with this program.  If not, see <http://www.gnu.org/licenses/>.
 **/
class Default_Model_DbTable_ProjectPlings extends Zend_Db_Table_Abstract
{

    protected $_name = "project_plings";


    public function getPlings($project_id)
    {
        $statement = $this->select()
            ->where('project_id=?', $project_id)
            ->where('is_deleted=?', 0);
        return $this->fetchAll($statement);
    }


    public function getPling($project_id,$member_id)
    {
        $statement = $this->select()
            ->where('project_id=?', $project_id)
            ->where('member_id=?', $member_id)
            ->where('is_deleted=?', 0);
        return $this->fetchRow($statement);
    }

    public function setDelete($id)
    {
        /*
        $updateValues = array(
            'is_deleted' => 1,
            'deleted_at' => new Zend_Db_Expr('Now()')
        );
        $this->update($updateValues,  array('project_plings_id = ?' => $id));
    */
        $sql = "update project_plings set is_deleted = 1, deleted_at = now() where project_plings_id = :id";

        $result = Zend_Db_Table::getDefaultAdapter()->query($sql, array('id' => $id))->execute();
       
        return $result;
    }

    public function countPlingsHeGave($member_id)
    {        
        $sql ="
                SELECT count(*) AS count 
                FROM project_plings f   
                WHERE  f.member_id =:member_id and f.is_deleted = 0 and f.is_active = 1
        ";
        $resultRow = $this->_db->fetchRow($sql, array('member_id' => $member_id));
        return $resultRow['count'];
    }

    public function getPlingsAmount($project_id)
    {

       $config = Zend_Registry::get('config');
       $member_id = $config->settings->member->plingcat->id;
       $sql ="
                SELECT count(*) AS count 
                FROM project_plings f   
                WHERE  f.project_id =:project_id and f.is_deleted = 0 and f.is_active = 1 and f.member_id<> :sysuser
        ";
        $resultRow = $this->_db->fetchRow($sql, array('project_id' => $project_id, 'sysuser' => $member_id));
        return $resultRow['count'];
    }

    public function countPlingsHeGotAll($member_id)
    {       
        $sql ="
                SELECT count(*) AS count 
                FROM project_plings f  
                inner join stat_projects p on p.project_id = f.project_id and p.status = 100 
                WHERE  p.member_id =:member_id and f.is_deleted = 0 and f.is_active = 1
        ";
        $resultRow = $this->_db->fetchRow($sql, array('member_id' => $member_id));
        return $resultRow['count'];

    }

    public function countPlingsHeGot($member_id)
    {       

        // $sql ="
        //         SELECT count(*) AS count 
        //         FROM project_plings f  
        //         inner join stat_projects p on p.project_id = f.project_id and p.status = 100 
        //         inner join member m on f.member_id = m.member_id and m.roleid <> 500 
        //         WHERE  p.member_id =:member_id and f.is_deleted = 0 and f.is_active = 1
        // ";
        $config = Zend_Registry::get('config');
        $plingcat_id = $config->settings->member->plingcat->id;
         $sql = "
                    select sum(cntplings) AS count 
                    from
                    (
                        select 
                        p.project_id
                        ,(select count(1) from project_plings f where f.project_id = p.project_id and f.is_deleted = 0 and f.is_active = 1 and f.member_id <> :plingcat_id) cntplings
                        from stat_projects p where p.member_id =:member_id and p.status = 100
                    ) tt
         ";       
         $resultRow = $this->_db->fetchRow($sql, array('member_id' => $member_id,'plingcat_id' => $plingcat_id));
        return $resultRow['count'];
    }

}
{'fraction_non_alphanumeric': 0.11621150493898896, 'fraction_numerical': 0.006740267286461359, 'mean_word_length': 2.086800573888092, 'pattern_counts': {'":': 0, '<': 12, '<?xml version=': 0, '>': 81, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '415392', 'n_tokens_mistral': 2654, 'n_tokens_neox': 2492, 'n_words': 681}