[3594] | 1 | <?php |
---|
| 2 | /** |
---|
| 3 | * ProjectManager - Elements storage object |
---|
| 4 | * |
---|
| 5 | * @link http://www.egroupware.org |
---|
| 6 | * @author Ralf Becker <RalfBecker-AT-outdoor-training.de> |
---|
| 7 | * @package projectmanager |
---|
| 8 | * @copyright (c) 2005/6 by Ralf Becker <RalfBecker-AT-outdoor-training.de> |
---|
| 9 | * @license http://opensource.org/licenses/gpl-license.php GPL - GNU General Public License |
---|
| 10 | * @version $Id: class.soprojectelements.inc.php 25098 2008-03-17 08:49:22Z ralfbecker $ |
---|
| 11 | */ |
---|
| 12 | |
---|
| 13 | include_once(PHPGW_INCLUDE_ROOT.'/etemplate/inc/class.so_sql.inc.php'); |
---|
| 14 | |
---|
| 15 | /** |
---|
| 16 | * Elements storage object of the projectmanager |
---|
| 17 | * |
---|
| 18 | * Tables: phpgw_pm_elements, phpgw_links |
---|
| 19 | * |
---|
| 20 | * A project P is the parent of an other project C, if link_id1=P.pm_id and link_id2=C.pm_id ! |
---|
| 21 | */ |
---|
| 22 | class soprojectelements extends so_sql |
---|
| 23 | { |
---|
| 24 | /** |
---|
| 25 | * Table name 'phpgw_links' |
---|
| 26 | * |
---|
| 27 | * @var string |
---|
| 28 | */ |
---|
| 29 | var $links_table = 'phpgw_links'; |
---|
| 30 | /** |
---|
| 31 | * Join in the links table |
---|
| 32 | * |
---|
| 33 | * @var string |
---|
| 34 | */ |
---|
| 35 | var $links_join = ',phpgw_links WHERE pe_id=link_id'; |
---|
| 36 | /** |
---|
| 37 | * Extracolumns from the links table |
---|
| 38 | * |
---|
| 39 | * @var array |
---|
| 40 | */ |
---|
| 41 | var $links_extracols = array( |
---|
| 42 | // 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 |
---|
| 43 | "CASE WHEN link_app1='projectmanager' AND link_id1=CAST(pm_id AS CHAR) THEN link_app2 ELSE link_app1 END AS pe_app", |
---|
| 44 | "CASE WHEN link_app1='projectmanager' AND link_id1=CAST(pm_id AS CHAR) THEN link_id2 ELSE link_id1 END AS pe_app_id", |
---|
| 45 | 'link_remark AS pe_remark', |
---|
| 46 | ); |
---|
| 47 | /** |
---|
| 48 | * Default share in minutes (on the whole project), used if no planned time AND no pe_share set |
---|
| 49 | * |
---|
| 50 | * @var int |
---|
| 51 | */ |
---|
| 52 | var $default_share = 240; // minutes |
---|
| 53 | /** |
---|
| 54 | * Id of the project |
---|
| 55 | * |
---|
| 56 | * @var int |
---|
| 57 | */ |
---|
| 58 | var $pm_id; |
---|
| 59 | |
---|
| 60 | /** |
---|
| 61 | * Constructor, calls the constructor of the extended class |
---|
| 62 | * |
---|
| 63 | * It is sufficent to give just the pe_id, as it is unique! |
---|
| 64 | * |
---|
| 65 | * @param int $pm_id pm_id of the project to use, default null |
---|
| 66 | * @param int $pe_id pe_id of the project-element to load, default null |
---|
| 67 | * @return soprojectelements |
---|
| 68 | */ |
---|
| 69 | function soprojectelements($pm_id=null,$pe_id=null) |
---|
| 70 | { |
---|
| 71 | $this->so_sql('projectmanager','phpgw_pm_elements'); |
---|
| 72 | |
---|
| 73 | if ((int) $pm_id || (int) $pe_id) |
---|
| 74 | { |
---|
| 75 | $this->pm_id = (int) $pm_id; |
---|
| 76 | |
---|
| 77 | if ((int) $pe_id) |
---|
| 78 | { |
---|
| 79 | if ($this->read($pe_id)) $this->pm_id = $this->data['pm_id']; |
---|
| 80 | } |
---|
| 81 | } |
---|
| 82 | } |
---|
| 83 | |
---|
| 84 | /** |
---|
| 85 | * Summarize the information of all elements of a project: min(start-time), sum(time), avg(completion), ... |
---|
| 86 | * |
---|
| 87 | * @param int/array $pm_id=null int project-id, array of project-id's or null to use $this->pm_id |
---|
| 88 | * @param array $filter=array() columname => value pairs to filter, eg. ' |
---|
| 89 | * @return array/boolean with summary information (keys as for a single project-element), false on error |
---|
| 90 | */ |
---|
| 91 | function summary($pm_id=null,$filter=array()) |
---|
| 92 | { |
---|
| 93 | if (is_null($pm_id)) $pm_id = $this->pm_id; |
---|
| 94 | |
---|
| 95 | if ($this->project->data['pm_id'] != $pm_id) |
---|
| 96 | { |
---|
| 97 | $save_data = $this->project->data; |
---|
| 98 | $this->project->read($pm_id); |
---|
| 99 | } |
---|
| 100 | if ($this->project->data['pm_accounting_type'] == 'status') // we dont have a times! |
---|
| 101 | { |
---|
| 102 | $share = "CASE WHEN pe_share IS NULL THEN $this->default_share ELSE pe_share END"; |
---|
| 103 | } |
---|
| 104 | else |
---|
| 105 | { |
---|
| 106 | $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"; |
---|
| 107 | } |
---|
| 108 | if ($save_data) $this->project->data = $save_data; |
---|
| 109 | |
---|
| 110 | if (!isset($filter['pm_id'])) $filter['pm_id'] = $pm_id; |
---|
| 111 | if (!isset($filter['pe_status'])) $filter[] = "pe_status != 'ignore'"; |
---|
| 112 | // fix some special filters: resources, cats |
---|
| 113 | $filter = $this->_fix_filter($filter); |
---|
| 114 | |
---|
| 115 | $this->db->select($this->table_name,array( |
---|
| 116 | "SUM(pe_completion * ($share)) AS pe_sum_completion_shares", |
---|
| 117 | "SUM(CASE WHEN pe_completion IS NULL THEN NULL ELSE ($share) END) AS pe_total_shares", |
---|
| 118 | // 'AVG(pe_completion) AS pe_completion', |
---|
| 119 | 'SUM(pe_used_time) AS pe_used_time', |
---|
| 120 | 'SUM(pe_planned_time) AS pe_planned_time', |
---|
| 121 | 'SUM(pe_used_budget) AS pe_used_budget', |
---|
| 122 | 'SUM(pe_planned_budget) AS pe_planned_budget', |
---|
| 123 | 'MIN(pe_real_start) AS pe_real_start', |
---|
| 124 | 'MIN(pe_planned_start) AS pe_planned_start', |
---|
| 125 | 'MAX(pe_real_end) AS pe_real_end', |
---|
| 126 | 'MAX(pe_planned_end) AS pe_planned_end', |
---|
| 127 | ),$filter,__LINE__,__FILE__,false,'',false,0,$this->links_join); |
---|
| 128 | |
---|
| 129 | if (!($data = $this->db->row(true))) |
---|
| 130 | { |
---|
| 131 | return false; |
---|
| 132 | } |
---|
| 133 | if ($data['pe_total_shares']) |
---|
| 134 | { |
---|
| 135 | $data['pe_completion'] = round($data['pe_sum_completion_shares'] / $data['pe_total_shares'],1); |
---|
| 136 | } |
---|
| 137 | return $this->db2data($data); |
---|
| 138 | } |
---|
| 139 | |
---|
| 140 | /** |
---|
| 141 | * search elements, reimplemented to join in some information from the links table and fix some filters |
---|
| 142 | * |
---|
| 143 | * @param array/string $criteria array of key and data cols, OR a SQL query (content for WHERE), fully quoted (!) |
---|
| 144 | * @param boolean $only_keys True returns only keys, False returns all cols |
---|
| 145 | * @param string $order_by fieldnames + {ASC|DESC} separated by colons ',' |
---|
| 146 | * @param string/array $extra_cols string or array of strings to be added to the SELECT, eg. "count(*) as num" |
---|
| 147 | * @param string $wildcard appended befor and after each criteria |
---|
| 148 | * @param boolean $empty False=empty criteria are ignored in query, True=empty have to be empty in row |
---|
| 149 | * @param string $op defaults to 'AND', can be set to 'OR' too, then criteria's are OR'ed together |
---|
| 150 | * @param int/boolean $start if != false, return only maxmatch rows begining with start |
---|
| 151 | * @param array $filter if set (!=null) col-data pairs, to be and-ed (!) into the query without wildcards |
---|
| 152 | * @param string/boolean $join=true default join with links-table or string as in so_sql |
---|
| 153 | * @return array of matching rows (the row is an array of the cols) or False |
---|
| 154 | */ |
---|
| 155 | function search($criteria,$only_keys=True,$order_by='',$extra_cols='',$wildcard='',$empty=False,$op='AND',$start=false,$filter=null,$join=true) |
---|
| 156 | { |
---|
| 157 | if ($join === true) // add join with links-table and extra-columns |
---|
| 158 | { |
---|
| 159 | $join = $this->links_join; |
---|
| 160 | |
---|
| 161 | if (!$extra_cols) |
---|
| 162 | { |
---|
| 163 | $extra_cols = $this->links_extracols; |
---|
| 164 | } |
---|
| 165 | else |
---|
| 166 | { |
---|
| 167 | $extra_cols = array_merge($this->links_extracols, |
---|
| 168 | is_array($extra_cols) ? $extra_cols : explode(',',$extra_cols)); |
---|
| 169 | } |
---|
| 170 | $order_by = "(link_app1='projectmanager' AND link_app2='projectmanager') DESC".($order_by ? ','.$order_by : ''); |
---|
| 171 | } |
---|
| 172 | // fix some special filters: resources, cats |
---|
| 173 | $filter = $this->_fix_filter($filter); |
---|
| 174 | |
---|
| 175 | return parent::search($criteria,$only_keys,$order_by,$extra_cols,$wildcard,$empty,$op,$start,$filter,$join); |
---|
| 176 | } |
---|
| 177 | |
---|
| 178 | /** |
---|
| 179 | * Fix some special filters: resources, cats, ... |
---|
| 180 | * |
---|
| 181 | * @param array $filter |
---|
| 182 | * @return array |
---|
| 183 | */ |
---|
| 184 | function _fix_filter($filter) |
---|
| 185 | { |
---|
| 186 | // handle search for a single resource in comma-separated pe_resources column |
---|
| 187 | if (isset($filter['pe_resources'])) |
---|
| 188 | { |
---|
| 189 | if ($filter['pe_resources']) |
---|
| 190 | { |
---|
| 191 | $filter[] = $this->db->concat("','",'pe_resources',"','").' LIKE '.$this->db->quote('%,'.$filter['pe_resources'].',%'); |
---|
| 192 | } |
---|
| 193 | unset($filter['pe_resources']); |
---|
| 194 | } |
---|
| 195 | // include sub-categories in the search |
---|
| 196 | if ($filter['cat_id']) |
---|
| 197 | { |
---|
| 198 | if (!is_object($GLOBALS['phpgw']->categories)) |
---|
| 199 | { |
---|
| 200 | $GLOBALS['phpgw']->categories =& CreateObject('phpgwapi.categories'); |
---|
| 201 | } |
---|
| 202 | $filter['cat_id'] = $GLOBALS['phpgw']->categories->return_all_children($filter['cat_id']); |
---|
| 203 | } |
---|
| 204 | // remove pseudo filter |
---|
| 205 | unset($filter['cumulate']); |
---|
| 206 | |
---|
| 207 | return $filter; |
---|
| 208 | } |
---|
| 209 | |
---|
| 210 | /** |
---|
| 211 | * reads one project-element specified by $keys, reimplemented to use $this->pm_id, if no pm_id given |
---|
| 212 | * |
---|
| 213 | * @param array $keys array with keys in form internalName => value, may be a scalar value if only one key |
---|
| 214 | * @param string/array $extra_cols string or array of strings to be added to the SELECT, eg. "count(*) as num" |
---|
| 215 | * @param string/boolean $join=true default join with links-table or string as in so_sql |
---|
| 216 | * @return array/boolean data if row could be retrived else False |
---|
| 217 | */ |
---|
| 218 | function read($keys,$extra_cols='',$join=true) |
---|
| 219 | { |
---|
| 220 | if ($this->pm_id && !isset($keys['pm_id'])) |
---|
| 221 | { |
---|
| 222 | if (!is_array($keys) && (int) $keys) $keys = array('pe_id' => (int) $keys); |
---|
| 223 | $keys['pm_id'] = $this->pm_id; |
---|
| 224 | } |
---|
| 225 | if ($join === true) // add join with links-table and extra-columns |
---|
| 226 | { |
---|
| 227 | $join = $this->links_join; |
---|
| 228 | |
---|
| 229 | if (!$extra_cols) $extra_cols = $this->links_extracols; |
---|
| 230 | } |
---|
| 231 | return parent::read($keys,$extra_cols,$join); |
---|
| 232 | } |
---|
| 233 | } |
---|