1: <?php 
  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: