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 | } |
---|