File:
0.00.1a/core/request/RequestManager.php (
View as Code)
1: 2: /* -------------------------------------------------------------
3: This file is part of FreeDESK
4:
5: FreeDESK is (C) Copyright 2012 David Cutting
6:
7: FreeDESK is free software: you can redistribute it and/or modify
8: it under the terms of the GNU General Public License as published by
9: the Free Software Foundation, either version 3 of the License, or
10: (at your option) any later version.
11:
12: FreeDESK is distributed in the hope that it will be useful,
13: but WITHOUT ANY WARRANTY; without even the implied warranty of
14: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15: GNU General Public License for more details.
16:
17: You should have received a copy of the GNU General Public License
18: along with FreeDESK. If not, see www.gnu.org/licenses
19:
20: For more information see www.purplepixie.org/freedesk/
21: -------------------------------------------------------------- */
22:
23: /**
24: * Request Manager - handle all management of requests
25: **/
26: class RequestManager
27: {
28: /**
29: * FreeDESK instance
30: **/
31: private $DESK = null;
32:
33: /**
34: * Team List
35: **/
36: private $Teams = null;
37:
38: /**
39: * User List (Assignment)
40: **/
41: private $Users = null;
42:
43: /**
44: * Request classes list
45: **/
46: private $ClassList = null;
47:
48: /**
49: * Constructor
50: * @param mixed &$freeDESK FreeDESK instance
51: **/
52: function RequestManager(&$freeDESK)
53: {
54: $this->DESK=&$freeDESK;
55: $this->DESK->PluginManager->Register(new Plugin(
56: "Request Manager", "0.01", "Core" ));
57: // Register our permissions
58: $this->DESK->PermissionManager->Register("request_view_otherteam",false);
59: $this->DESK->PermissionManager->Register("request_view_otherteamuser",false);
60: $this->DESK->PermissionManager->Register("request_view_otheruser",false);
61: $this->DESK->PermissionManager->Register("request_view_unassigned",false);
62: $this->DESK->PermissionManager->Register("request_assign_otherteam",false);
63: $this->DESK->PermissionManager->Register("request_assign_otherteamuser",false);
64: $this->DESK->PermissionManager->Register("request_assign_otheruser",false);
65: $this->DESK->PermissionManager->Register("request_assign_unassigned",false);
66:
67: }
68:
69: /**
70: * Team and User List (Assignment and View List)
71: * @return array Mixed array with teams and users with view and assign flags
72: **/
73: function TeamUserList()
74: {
75: $out = array();
76:
77: $this->Users = array();
78: $this->Teams = array();
79:
80: $q="SELECT ".$this->DESK->Database->Field("username").",".$this->DESK->Database->Field("realname")." FROM ".$this->DESK->Database->Table("user");
81: $r=$this->DESK->Database->Query($q);
82: $users=array();
83: while($row=$this->DESK->Database->FetchAssoc($r))
84: {
85: $users[$row['username']] = $row['realname'];
86: $this->Users[$row['username']] = $row['realname'];
87: }
88: $this->DESK->Database->Free($r);
89:
90: $q="SELECT * FROM ".$this->DESK->Database->Table("team");
91: $r=$this->DESK->Database->Query($q);
92: $team=array();
93: while($row=$this->DESK->Database->FetchAssoc($r))
94: {
95: $team[$row['teamid']]=$row['teamname'];
96: $this->Teams[$row['teamid']]=$row['teamname'];
97: }
98:
99: $q="SELECT * FROM ".$this->DESK->Database->Table("teamuserlink");
100: $r=$this->DESK->Database->Query($q);
101: $teamlink=array();
102: while($row=$this->DESK->Database->FetchAssoc($r))
103: {
104: if (isset($teamlink[$row['teamid']]))
105: $teamlink[$row['teamid']][]=$row['username'];
106: else
107: $teamlink[$row['teamid']]=array( $row['username'] );
108: }
109:
110: $out[0]=array(
111: "name" => "Unassigned",
112: "id" => 0,
113: "team" => true,
114: "assign" => true,
115: "view" => true,
116: "items" => array() );
117:
118: foreach($team as $teamid => $teamname)
119: {
120: $out[$teamid]=array(
121: "name" => $teamname,
122: "id" => $teamid,
123: "team" => true,
124: "assign" => true,
125: "view" => true,
126: "items" => array() );
127:
128: if (isset($teamlink[$teamid]))
129: {
130:
131: foreach($teamlink[$teamid] as $username)
132: {
133: $out[$teamid]["items"][$username] = array (
134: "username" => $username,
135: "realname" => $users[$username],
136: "assign" => true,
137: "view" => true );
138: }
139: }
140: }
141:
142: $out['allusers']=array(
143: "name" => "All Users",
144: "id" => 0,
145: "team" => false,
146: "assign" => false,
147: "view" => false,
148: "items" => array() );
149: foreach($users as $username => $realname)
150: {
151: $out['allusers']['items'][$username] = array(
152: "username" => $username,
153: "realname" => $realname,
154: "assign" => true,
155: "view" => true );
156: }
157:
158: return $out;
159: }
160:
161: /**
162: * Return a list of possible request statuses
163: * @return array Status list
164: **/
165: function StatusList()
166: {
167: $q="SELECT * FROM ".$this->DESK->Database->Table("status");
168: $q.=" ORDER BY ".$this->DESK->Database->Field("status")." DESC";
169: $r=$this->DESK->Database->Query($q);
170: $out=array();
171: while ($row=$this->DESK->Database->FetchAssoc($r))
172: {
173: $out[$row['status']]=$row['description'];
174: }
175: $this->DESK->Database->Free($r);
176: return $out;
177: }
178:
179: /**
180: * Fetch a request by ID
181: * @param int $request Request ID
182: * @return mixed bool false if request not found or Request-type class on success
183: **/
184: function Fetch($request)
185: {
186: $q="SELECT * FROM ".$this->DESK->Database->Table("request")." WHERE ";
187: $q.=$this->DESK->Database->Field("requestid")."=".$this->DESK->Database->Safe($request);
188: $r=$this->DESK->Database->Query($q);
189: if ($row=$this->DESK->Database->FetchAssoc($r))
190: {
191: $req = $this->CreateByID($row['class']);
192: $req->ID = $row['requestid'];
193: foreach($row as $key => $val)
194: $req->Set($key, $val);
195: $assign="";
196: if ($row['assignteam']!=0)
197: {
198: $teams = $this->TeamList();
199: $assign.=$teams[$row['assignteam']];
200: }
201: if ($row['assignuser']!="")
202: {
203: if ($assign!="")
204: $assign.=" - ";
205: $users = $this->UserList();
206: $assign.=$users[$row['assignuser']];
207: }
208: if ($row['assignteam']==0 && $row['assignuser']=="")
209: $assign=$this->DESK->Lang->Get("unassigned");
210: $req->Set("assigned",$assign);
211:
212: $cq="SELECT ".$this->DESK->Database->Field("firstname").",".$this->DESK->Database->Field("lastname");
213: $cq.=" FROM ".$this->DESK->Database->Table("customer")." ";
214: $cq.="WHERE ".$this->DESK->Database->Field("customerid")."=".$this->DESK->Database->Safe($row['customer']);
215: $cq.=" LIMIT 0,1";
216: $cr=$this->DESK->Database->Query($cq);
217: $req->Set("customerid",$row['customer']);
218: if ($cust=$this->DESK->Database->FetchAssoc($cr))
219: {
220: $req->Set("customer",$cust['firstname']." ".$cust['lastname']);
221: }
222: else
223: $req->Set("customer","Unknown (".$row['customer'].")");
224: $this->DESK->Database->Free($cr);
225:
226: return $req;
227: }
228: else
229: return false;
230: }
231:
232: /**
233: * Fetch a request assignment list
234: * @param int $teamid Assigned team (optional, default 0)
235: * @param string $username Assigned username (optional, default "")
236: * @param string $sort Field to sort on
237: * @param string $order Order (ASC or DESC)
238: * @return mixed array of requests matching
239: **/
240: function FetchAssigned($teamid=0, $username="", $sort="", $order="")
241: {
242: // assignteam assignuser
243: $q="SELECT ".$this->DESK->Database->Field("requestid")." FROM ".$this->DESK->Database->Table("request")." WHERE ";
244:
245:
246: if ( ($teamid==0) && ($username!="") ) // assigned to a user for any team
247: $q.=$this->DESK->Database->Field("assignuser")."=".$this->DESK->Database->SafeQuote($username);
248: else // use both
249: {
250: $q.=$this->DESK->Database->Field("assignuser")."=".$this->DESK->Database->SafeQuote($username)." AND ";
251: $q.=$this->DESK->Database->Field("assignteam")."=".$this->DESK->Database->Safe($teamid);
252: }
253:
254: $q.=" AND ".$this->DESK->Database->Field("status").">0";
255:
256: if ($sort != "" && $sort != "assigned" && $sort != "customer")
257: {
258: $q.=" ORDER BY ".$this->DESK->Database->Field($sort)." ";
259: if ($order == "ASC")
260: $q.="ASC";
261: else
262: $q.="DESC";
263: }
264: else if ($sort == "assigned")
265: {
266: if ($order == "ASC")
267: $o="ASC";
268: else
269: $o="DESC";
270: $q.=" ORDER BY ".$this->DESK->Database->Field("assignteam")." ".$o.",";
271: $q.=$this->DESK->Database->Field("assignuser")." ".$o;
272: }
273: else if ($sort == "customer")
274: {
275: if ($order == "ASC")
276: $o="ASC";
277: else
278: $o="DESC";
279: $q.=" ORDER BY ".$this->DESK->Database->Field("customer")." ".$o;
280: }
281:
282: $out=array();
283: $r=$this->DESK->Database->Query($q);
284: while ($row=$this->DESK->Database->FetchAssoc($r))
285: {
286: $out[]=$this->Fetch($row['requestid']);
287: }
288: return $out;
289: }
290:
291: /**
292: * Fetch an array of request fields for the main list display with their default display options
293: * @return array of request fields
294: **/
295: function FetchFields()
296: {
297: $out = array(
298: "requestid" => array("Request ID", 1),
299: "customer" => array("Customer", 1),
300: "assigned" => array("Assigned To", 1),
301: "openeddt" => array("Opened", 0),
302: "class" => array("Class", 0),
303: "priority" => array("Priority", 1),
304: "status" => array("Status", 1) );
305: return $out;
306: }
307:
308: /**
309: * Fetch a list of users in form username=>realname
310: * @return array User list
311: **/
312: function UserList()
313: {
314: if (!is_array($this->Users))
315: $this->TeamUserList();
316: return $this->Users;
317: }
318:
319: /**
320: * Fetch a list of teams in form teamid=>teamname
321: * @return array Team List
322: **/
323: function TeamList()
324: {
325: if (!is_array($this->Teams))
326: $this->TeamUserList();
327: return $this->Teams;
328: }
329:
330: /**
331: * Determine is a user is in a team
332: * @param string $username Username
333: * @param int $teamid Team ID
334: * @return bool True if user is in team else false
335: **/
336: function IsUserInTeam($username, $teamid)
337: {
338: $q="SELECT ".$this->DESK->Database->Field("linkid")." FROM ";
339: $q.=$this->DESK->Database->Table("teamuserlink")." WHERE ";
340: $q.=$this->DESK->Database->Field("teamid")."=".$this->DESK->Database->Safe($teamid)." AND ";
341: $q.=$this->DESK->Database->Field("username")."=".$this->DESK->Database->SafeQuote($username);
342: $q.=" LIMIT 0,1";
343:
344: $r=$DESK->Database->Query($q);
345:
346: $inteam=false;
347:
348: if ($row=$DESK->Database->FetchAssoc($r))
349: $inteam=true;
350:
351: $DESK->Database->Free($r);
352:
353: return $inteam;
354: }
355:
356: /**
357: * Load a class list
358: **/
359: private function LoadClassList()
360: {
361: $q="SELECT * FROM ".$this->DESK->Database->Table("requestclass");
362: $r=$this->DESK->Database->Query($q);
363: $this->ClassList = array();
364: while ($row=$this->DESK->Database->FetchAssoc($r))
365: {
366: $this->ClassList[$row['classid']] = $row;
367: }
368: $this->DESK->Database->Free($r);
369: }
370:
371: /**
372: * Create a request by classid
373: * @param int $classid Class ID
374: * @return object Request object
375: **/
376: function CreateByID($classid)
377: {
378: if ($this->ClassList == null)
379: $this->LoadClassList();
380:
381: if (isset($this->ClassList[$classid]))
382: return RequestFactory::Create($this->DESK, $this->ClassList[$classid]['classclass']);
383: else
384: return RequestFactory::Create($this->DESK, "");
385: }
386:
387: /**
388: * Create a new team
389: * @param string $teamname Name of the team
390: **/
391: function CreateTeam($teamname)
392: {
393: $q="INSERT INTO ".$this->DESK->Database->Table("team")."(".$this->DESK->Database->Field("teamname").") VALUES(".
394: $q.=$this->DESK->Database->SafeQuote($teamname).")";
395: $this->DESK->Database->Query($q);
396: }
397:
398: /**
399: * Update a team name
400: * @param int $teamid ID
401: * @param string $teamname Team name
402: **/
403: function UpdateTeam($teamid, $teamname)
404: {
405: $q="UPDATE ".$this->DESK->Database->Table("team")." SET ".$this->DESK->Database->Field("teamname")."=".$this->DESK->Database->SafeQuote($teamname);
406: $q.=" WHERE ".$this->DESK->Database->Field("teamid")."=".$this->DESK->Database->Safe($teamid);
407: $this->DESK->Database->Query($q);
408: }
409:
410: /**
411: * Delete a team
412: * @param int $teamid ID
413: **/
414: function DeleteTeam($teamid)
415: {
416: $q="DELETE FROM ".$this->DESK->Database->Table("teamuserlink")." WHERE ".$this->DESK->Database->Field("teamid")."=".$this->DESK->Database->Safe($teamid);
417: $this->DESK->Database->Query($q);
418:
419: $q="DELETE FROM ".$this->DESK->Database->Table("team")." WHERE ".$this->DESK->Database->Field("teamid")."=".$this->DESK->Database->Safe($teamid);
420: $this->DESK->Database->Query($q);
421: }
422:
423: /**
424: * Create a new status
425: * @param string $name Status name
426: **/
427: function CreateStatus($name)
428: {
429: $current = $this->StatusList();
430: $high = 0;
431: foreach($current as $id => $curname)
432: if ($id > $high)
433: $high = $id;
434: $newid = $high+1;
435:
436: $q="INSERT INTO ".$this->DESK->Database->Table("status")."(".$this->DESK->Database->Field("status").",".$this->DESK->Database->Field("description").") ";
437: $q.="VALUES(".$this->DESK->Database->Safe($newid).",".$this->DESK->Database->SafeQuote($name).")";
438:
439: $this->DESK->Database->Query($q);
440: }
441:
442: /**
443: * Update a status description
444: * @param int $id Status ID
445: * @param string $name New Name
446: **/
447: function UpdateStatus($id, $name)
448: {
449: $q="UPDATE ".$this->DESK->Database->Table("status")." SET ".$this->DESK->Database->Field("description")."=".$this->DESK->Database->SafeQuote($name);
450: $q.=" WHERE ".$this->DESK->Database->Field("status")."=".$this->DESK->Database->Safe($id);
451: $this->DESK->Database->Query($q);
452: }
453:
454: /**
455: * Delete a status
456: * @param int $id Status ID
457: **/
458: function DeleteStatus($id)
459: {
460: $q="DELETE FROM ".$this->DESK->Database->Table("status")." WHERE ".$this->DESK->Database->Field("status")."=".$this->DESK->Database->Safe($id);
461: $this->DESK->Database->Query($q);
462: }
463:
464: /**
465: * Get a list of all request classes
466: * @return array Request class data
467: **/
468: function GetRequestClasses()
469: {
470: $q="SELECT * FROM ".$this->DESK->Database->Table("requestclass");
471: $r=$this->DESK->Database->Query($q);
472: $out=array();
473: while ($row=$this->DESK->Database->FetchAssoc($r))
474: $out[$row['classid']]=$row;
475: $this->DESK->Database->Free($r);
476: return $out;
477: }
478:
479: /**
480: * Save/Create a Request Class
481: * @param string $classname Name
482: * @param string $classclass Class class (concrete request class)
483: * @param int $id (optional, if present will save otherwise create)
484: **/
485: function SaveRequestClass($classname, $classclass, $id=0)
486: {
487: if ($id == 0)
488: {
489: $q="INSERT INTO ".$this->DESK->Database->Table("requestclass")."(";
490: $q.=$this->DESK->Database->Field("classname").",".$this->DESK->Database->Field("classclass").") ";
491: $q.="VALUES(";
492: $q.=$this->DESK->Database->SafeQuote($classname).",".$this->DESK->Database->SafeQuote($classclas).")";
493: $this->DESK->Database->Query($q);
494: }
495: else
496: {
497: $q="UPDATE ".$this->DESK->Database->Table("requestclass")." SET ";
498: $q.=$this->DESK->Database->Field("classname")."=".$this->DESK->Database->SafeQuote($classname).",";
499: $q.=$this->DESK->Database->Field("classclass")."=".$this->DESK->Database->SafeQuote($classclass)." ";
500: $q.="WHERE ".$this->DESK->Database->Field("classid")."=".$this->DESK->Database->Safe($id);
501: $this->DESK->Database->Query($q);
502: }
503: }
504:
505: /**
506: * Delete a request class
507: * @param int $id Request Class ID
508: **/
509: function DeleteRequestClass($id)
510: {
511: $q="DELETE FROM ".$this->DESK->Database->Table("requestclass")." WHERE ";
512: $q.=$this->DESK->Database->Field("classid")."=".$this->DESK->Database->Safe($id);
513: $this->DESK->Database->Query($q);
514: }
515:
516: /**
517: * Get a priority list
518: * @return array Priorities
519: **/
520: function GetPriorityList()
521: {
522: $q="SELECT * FROM ".$this->DESK->Database->Table("priority");
523: $r=$this->DESK->Database->Query($q);
524: $out=array();
525: while ($row=$this->DESK->Database->FetchAssoc($r))
526: {
527: $out[$row['priorityid']]=$row;
528: }
529: $this->DESK->Database->Free($r);
530: return $out;
531: }
532:
533: /**
534: * Save/Create a Request Priority
535: * @param string $priorityname Name
536: * @param int $resolutionsla Resolution SLA (seconds)
537: * @param int $schedule Schedule ID for SLA
538: * @param int $priorityid Priority ID (optional)
539: **/
540: function SavePriority($priorityname, $resolutionsla, $schedule, $priorityid=0)
541: {
542: if ($priorityid == 0)
543: {
544: $q="INSERT INTO ".$this->DESK->Database->Table("priority")." (";
545: $q.=$this->DESK->Database->Field("priorityname").",".$this->DESK->Database->Field("resolutionsla").",";
546: $q.=$this->DESK->Database->Field("schedule").") VALUES(";
547: $q.=$this->DESK->Database->SafeQuote($priorityname).",".$this->DESK->Database->Safe($resolutionsla).",".$this->DESK->Database->Safe($schedule).")";
548: $this->DESK->Database->Query($q);
549: }
550: else
551: {
552: $q="UPDATE ".$this->DESK->Database->Table("priority")." SET ";
553: $q.=$this->DESK->Database->Field("priorityname")."=".$this->DESK->Database->SafeQuote($priorityname).",";
554: $q.=$this->DESK->Database->Field("resolutionsla")."=".$this->DESK->Database->Safe($resolutionsla).",";
555: $q.=$this->DESK->Database->Field("schedule")."=".$this->DESK->Database->Safe($schedule)." ";
556: $q.="WHERE ".$this->DESK->Database->Field("priorityid")."=".$this->DESK->Database->Safe($priorityid);
557: $this->DESK->Database->Query($q);
558: }
559: }
560:
561: /**
562: * Delete a priority
563: * @param int $priorityid Priority ID
564: **/
565: function DeletePriority($priorityid)
566: {
567: $q="DELETE FROM ".$this->DESK->Database->Table("priority")." WHERE ".$this->DESK->Database->Field("priorityid")."=".$this->DESK->Database->Safe($priorityid);
568: $this->DESK->Database->Query($q);
569: }
570:
571: /**
572: * Search requests against parameters - no filtering
573: * @param array Search parameters (in form "field" "value" opt "match" [LIKE or =, default to =])
574: * @return array Request list with all raw data (no class instances)
575: **/
576: function SearchRequests($parameters)
577: {
578: $q="SELECT * FROM ".$this->DESK->Database->Table("request");
579: if (sizeof($parameters)>0)
580: {
581: $q.=" WHERE ";
582: $first=true;
583: foreach($parameters as $param)
584: {
585: if ($first)
586: $first=false;
587: else
588: $q.=" AND ";
589: $w=$this->DESK->Database->Field($param["field"]);
590: if (isset($param["match"]))
591: $w.=" ".$param["match"]." ";
592: else
593: $w.="=";
594: $w.=$this->DESK->Database->SafeQuote($param["value"]);
595: $q.=$w;
596: }
597: }
598: //echo $q;
599: $r=$this->DESK->Database->Query($q);
600: $out = array();
601: while ($row=$this->DESK->Database->FetchAssoc($r))
602: {
603: $out[]=$row;
604: }
605: $this->DESK->Database->Free($r);
606: return $out;
607: }
608:
609:
610:
611: }
612: ?>
613: