source: contrib/ProjectManager/inc/class.soprojectelements.inc.php @ 3594

<?php
/**
 * ProjectManager - Elements storage object
 *
 * @link http://www.egroupware.org
 * @author Ralf Becker <RalfBecker-AT-outdoor-training.de>
 * @package projectmanager
 * @copyright (c) 2005/6 by Ralf Becker <RalfBecker-AT-outdoor-training.de>
 * @license http://opensource.org/licenses/gpl-license.php GPL - GNU General Public License
 * @version $Id: class.soprojectelements.inc.php 25098 2008-03-17 08:49:22Z ralfbecker $ 
 */

include_once(PHPGW_INCLUDE_ROOT.'/etemplate/inc/class.so_sql.inc.php');

/**
 * Elements storage object of the projectmanager
 *
 * Tables: phpgw_pm_elements, phpgw_links
 *
 * A project P is the parent of an other project C, if link_id1=P.pm_id and link_id2=C.pm_id !
 */
class soprojectelements extends so_sql
{
        /**
         * Table name 'phpgw_links'
         * 
         * @var string
         */
        var $links_table = 'phpgw_links';
        /**
         * Join in the links table
         *
         *  @var string
         */
        var $links_join = ',phpgw_links WHERE pe_id=link_id';
        /**
         * Extracolumns from the links table
         * 
         * @var array
         */
        var $links_extracols = array(
                // postgres 8.3 requires cast as link_idx is varchar and pm_id an integer, the cast should be no problem for other DB's
                "CASE WHEN link_app1='projectmanager' AND link_id1=CAST(pm_id AS CHAR) THEN link_app2 ELSE link_app1 END AS pe_app",
                "CASE WHEN link_app1='projectmanager' AND link_id1=CAST(pm_id AS CHAR) THEN link_id2 ELSE link_id1 END AS pe_app_id",
                'link_remark AS pe_remark',
        );
        /**
         * Default share in minutes (on the whole project), used if no planned time AND no pe_share set
         * 
         * @var int
         */
        var $default_share = 240; // minutes
        /**
         * Id of the project
         *
         * @var int
         */
        var $pm_id;

        /**
         * Constructor, calls the constructor of the extended class
         * 
         * It is sufficent to give just the pe_id, as it is unique!
         *
         * @param int $pm_id pm_id of the project to use, default null
         * @param int $pe_id pe_id of the project-element to load, default null
         * @return soprojectelements
         */
        function soprojectelements($pm_id=null,$pe_id=null)
        {
                $this->so_sql('projectmanager','phpgw_pm_elements');

                if ((int) $pm_id || (int) $pe_id) 
                {
                        $this->pm_id = (int) $pm_id;
                        
                        if ((int) $pe_id)
                        {
                                if ($this->read($pe_id)) $this->pm_id = $this->data['pm_id'];
                        }
                }
        }
        
        /**
         * Summarize the information of all elements of a project: min(start-time), sum(time), avg(completion), ...
         *
         * @param int/array $pm_id=null int project-id, array of project-id's or null to use $this->pm_id
         * @param array $filter=array() columname => value pairs to filter, eg. '
         * @return array/boolean with summary information (keys as for a single project-element), false on error
         */
        function summary($pm_id=null,$filter=array())
        {
                if (is_null($pm_id)) $pm_id = $this->pm_id;

                if ($this->project->data['pm_id'] != $pm_id)
                {
                        $save_data = $this->project->data;
                        $this->project->read($pm_id);
                }
                if ($this->project->data['pm_accounting_type'] == 'status')     // we dont have a times!
                {
                        $share = "CASE WHEN pe_share IS NULL THEN $this->default_share ELSE pe_share END";
                }
                else
                {
                        $share = "CASE WHEN pe_share IS NULL AND pe_planned_time IS NULL THEN $this->default_share WHEN pe_share IS NULL THEN pe_planned_time ELSE pe_share END";
                }
                if ($save_data) $this->project->data = $save_data;

                if (!isset($filter['pm_id'])) $filter['pm_id'] = $pm_id;
                if (!isset($filter['pe_status'])) $filter[] = "pe_status != 'ignore'";
                // fix some special filters: resources, cats
                $filter = $this->_fix_filter($filter);

                $this->db->select($this->table_name,array(
                        "SUM(pe_completion * ($share)) AS pe_sum_completion_shares",
                        "SUM(CASE WHEN pe_completion IS NULL THEN NULL ELSE ($share) END) AS pe_total_shares",
//                      'AVG(pe_completion) AS pe_completion',
                        'SUM(pe_used_time) AS pe_used_time',
                        'SUM(pe_planned_time) AS pe_planned_time',
                        'SUM(pe_used_budget) AS pe_used_budget',
                        'SUM(pe_planned_budget) AS pe_planned_budget',
                        'MIN(pe_real_start) AS pe_real_start',
                        'MIN(pe_planned_start) AS pe_planned_start',
                        'MAX(pe_real_end) AS pe_real_end',
                        'MAX(pe_planned_end) AS pe_planned_end',
                ),$filter,__LINE__,__FILE__,false,'',false,0,$this->links_join);
                
                if (!($data = $this->db->row(true)))
                {
                        return false;
                }
                if ($data['pe_total_shares'])
                {
                        $data['pe_completion'] = round($data['pe_sum_completion_shares'] / $data['pe_total_shares'],1);
                }
                return $this->db2data($data);
        }

        /**
         * search elements, reimplemented to join in some information from the links table and fix some filters
         *
         * @param array/string $criteria array of key and data cols, OR a SQL query (content for WHERE), fully quoted (!)
         * @param boolean $only_keys True returns only keys, False returns all cols
         * @param string $order_by fieldnames + {ASC|DESC} separated by colons ','
         * @param string/array $extra_cols string or array of strings to be added to the SELECT, eg. "count(*) as num"
         * @param string $wildcard appended befor and after each criteria
         * @param boolean $empty False=empty criteria are ignored in query, True=empty have to be empty in row
         * @param string $op defaults to 'AND', can be set to 'OR' too, then criteria's are OR'ed together
         * @param int/boolean $start if != false, return only maxmatch rows begining with start
         * @param array $filter if set (!=null) col-data pairs, to be and-ed (!) into the query without wildcards
         * @param string/boolean $join=true default join with links-table or string as in so_sql
         * @return array of matching rows (the row is an array of the cols) or False
         */
        function search($criteria,$only_keys=True,$order_by='',$extra_cols='',$wildcard='',$empty=False,$op='AND',$start=false,$filter=null,$join=true)
        {
                if ($join === true)     // add join with links-table and extra-columns
                {
                        $join = $this->links_join;
                        
                        if (!$extra_cols)
                        {
                                $extra_cols = $this->links_extracols;
                        }
                        else
                        {
                                $extra_cols = array_merge($this->links_extracols,
                                        is_array($extra_cols) ? $extra_cols : explode(',',$extra_cols));
                        }
                        $order_by = "(link_app1='projectmanager' AND link_app2='projectmanager') DESC".($order_by ? ','.$order_by : '');
                }
                // fix some special filters: resources, cats
                $filter = $this->_fix_filter($filter);

                return parent::search($criteria,$only_keys,$order_by,$extra_cols,$wildcard,$empty,$op,$start,$filter,$join);
        }
        
        /**
         * Fix some special filters: resources, cats, ...
         *
         * @param array $filter
         * @return array
         */
        function _fix_filter($filter)
        {
                // handle search for a single resource in comma-separated pe_resources column
                if (isset($filter['pe_resources']))
                {
                        if ($filter['pe_resources'])
                        {
                                $filter[] = $this->db->concat("','",'pe_resources',"','").' LIKE '.$this->db->quote('%,'.$filter['pe_resources'].',%');
                        }
                        unset($filter['pe_resources']);
                }
                // include sub-categories in the search
                if ($filter['cat_id'])
                {
                        if (!is_object($GLOBALS['phpgw']->categories))
                        {
                                $GLOBALS['phpgw']->categories =& CreateObject('phpgwapi.categories');
                        }
                        $filter['cat_id'] = $GLOBALS['phpgw']->categories->return_all_children($filter['cat_id']);
                }
                // remove pseudo filter
                unset($filter['cumulate']);
                
                return $filter;
        }

        /**
         * reads one project-element specified by $keys, reimplemented to use $this->pm_id, if no pm_id given
         *
         * @param array $keys array with keys in form internalName => value, may be a scalar value if only one key
         * @param string/array $extra_cols string or array of strings to be added to the SELECT, eg. "count(*) as num"
         * @param string/boolean $join=true default join with links-table or string as in so_sql
         * @return array/boolean data if row could be retrived else False
        */
        function read($keys,$extra_cols='',$join=true)
        {
                if ($this->pm_id && !isset($keys['pm_id']))
                {
                        if (!is_array($keys) && (int) $keys) $keys = array('pe_id' => (int) $keys);
                        $keys['pm_id'] = $this->pm_id;
                }
                if ($join === true)     // add join with links-table and extra-columns
                {
                        $join = $this->links_join;
                        
                        if (!$extra_cols) $extra_cols = $this->links_extracols;
                }
                return parent::read($keys,$extra_cols,$join);
        }
}