Ein Beispiel für ein kleines Ticketsystem für eine etwaige Supportanfrage;
Zumindest die Funktionen, entsprechende Funktionsaufrufe und HTMLdetails hab ich nicht beigefügt. Jedoch können die Funktionen evtl. eine gute Hilfestellung sein.
-> Kann auch gelöscht werden, da dies ja kein "richtiges Tutorial" ist.
Tabelle
Customerfunktionen
Adminfunktionen
Zumindest die Funktionen, entsprechende Funktionsaufrufe und HTMLdetails hab ich nicht beigefügt. Jedoch können die Funktionen evtl. eine gute Hilfestellung sein.
-> Kann auch gelöscht werden, da dies ja kein "richtiges Tutorial" ist.
Tabelle
Code:
CREATE TABLE IF NOT EXISTS `tickets` ( `id` int(11) unsigned NOT NULL auto_increment, `customerid` varchar(4) NOT NULL, `prefixid` int(11) NOT NULL, `ticketid` int(4) NOT NULL, `subject` varchar(250) NOT NULL, `priority` int(1) NOT NULL, `msg` text NOT NULL, `opened` int(11) NOT NULL, `closed` int(11) NOT NULL, `deleted` int(11) NOT NULL, `support_msg` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
PHP-Code:
<?php
class customer
{
function checkTicket($details)
{
$priority=array(1, 2, 3);
if(trim($details['subject'])!=''
&& trim($details['msg'])!=''
&& in_array($details['priority'], $priority))
{
return true;
}
else
{
$error=array();
foreach($details as $key => $value)
{
if(trim($value)=='' && in_array($key, array('msg', 'subject')))
{
$error[]=$key;
}
}
return $error;
}
}
function insertTicket($details)
{
$sql=new mysql;
$sql->connect();
$prefixid=rand(1000, 9999);
$prefixid_check=mysql_query("SELECT prefixid FROM tickets WHERE prefixid='".$prefixid."' LIMIT 1");
$prefixid_check=mysql_fetch_row($prefixid_check);
while($prefixid_check[0]!=false)
{
$prefixid=rand(1000, 9999);
$prefixid_check=mysql_query("SELECT prefixid FROM tickets WHERE prefixid='".$prefixid."' LIMIT 1");
$prefixid_check=mysql_fetch_row($prefixid_check);
}
foreach($details as $key => $value)
{
$details[$key]=htmlspecialchars($value);
}
$details['msg']=nl2br($details['msg']);
$details['subject']=nl2br($details['subject']);
mysql_query("INSERT INTO tickets
(customerid, prefixid, ticketid, subject, priority, msg, opened)
VALUES ('".$details['customerid']."', '".$prefixid."', '".rand(1000, 9999)."', '".mysql_real_escape_string($details['subject'])."', '".$details['priority']."', '".mysql_real_escape_string($details['msg'])."', '".mktime()."')");
return $prefixid;
$sql->close();
}
function getTicketList($customerid)
{
$sql=new mysql;
$sql->connect();
$data=array();
$diff_prefixids=array();
$i=0;
$result=mysql_query("SELECT * FROM tickets WHERE customerid='".$customerid."' ORDER BY id");
while($row=mysql_fetch_assoc($result))
{
if(!in_array($row['prefixid'], $diff_prefixids))
{
$diff_prefixids[]=$row['prefixid'];
$data[$i]['prefixid']=$row['prefixid'];
$data[$i]['ticketid']=$row['ticketid'];
$data[$i]['subject']=$row['subject'];
$data[$i]['priority']=$row['priority'];
$data[$i]['opened']=$row['opened'];
$data[$i]['closed']=$row['closed'];
$i++;
}
}
return $data;
}
function getTicketsDetails($prefixid)
{
$sql=new mysql;
$sql->connect();
$i=0;
$result=mysql_query("SELECT * FROM tickets WHERE prefixid='".mysql_real_escape_string($prefixid)."' ORDER BY id");
if(mysql_num_rows($result)!=0)
{
while($row=mysql_fetch_assoc($result))
{
if($i==0)
{
$data[0]['customerid']=$row['customerid'];
$data[0]['prefixid']=$row['prefixid'];
$data[0]['ticketid']=$row['ticketid'];
$data[0]['subject']=$row['subject'];
$data[0]['priority']=$row['priority'];
}
if($row['msg']!='')
{
$data[$i]['msg']=$row['msg'];
$data[$i]['opened']=$row['opened'];
}
else
{
$data[$i]['support_msg']=$row['support_msg'];
$data[$i]['closed']=$row['closed'];
}
$i++;
}
return $data;
}
else
{
return false;
}
}
function answerTicket($customerid, $prefixid, $msg)
{
$sql=new mysql;
$sql->connect();
$result=mysql_query("SELECT COUNT(customerid) FROM tickets
WHERE prefixid='".mysql_real_escape_string($prefixid)."' && customerid='".$customerid."' LIMIT 1");
$num=mysql_fetch_row($result);
if($num[0]==0)
{
return false;
}
$msg=htmlspecialchars($msg);
$msg=nl2br($msg);
mysql_query("INSERT INTO tickets
(customerid, prefixid, ticketid, msg, opened)
VALUES ('".$customerid."', '".$prefixid."', '".rand(1000, 9999)."', '".mysql_real_escape_string($msg)."', '".mktime()."')");
return true;
$sql->close();
}
function getCustomersInfo($id)
{
$sql=new mysql;
$sql->connect();
$result=mysql_query("SELECT * FROM customers WHERE id='".$id."' LIMIT 1");
return mysql_fetch_assoc($result);
$sql->close();
}
function closedOrOpened($prefixid)
{
$sql=new mysql;
$sql->connect();
$result=mysql_query("SELECT closed, opened FROM tickets WHERE prefixid='".$prefixid."' ORDER BY id DESC");
$row=mysql_fetch_assoc($result);
return ($row['opened']!=0) ? true : false; // Wenn Support eine Antwort schreibt wird der timestamp in Spalte closed eingetragen und Ticket ist geschlossen; opened bleibt leer.
$sql->close();
}
}
?>
PHP-Code:
<?php
class admin
{
function getAllTickets()
{
$sql=new mysql;
$sql->connect();
$data=array();
$diff_prefixids=array();
$i=0;
$result=mysql_query("SELECT * FROM tickets ORDER BY id");
while($row=mysql_fetch_assoc($result))
{
if(!in_array($row['prefixid'], $diff_prefixids))
{
$diff_prefixids[]=$row['prefixid'];
$data[$i]['customerid']=$row['customerid'];
$data[$i]['prefixid']=$row['prefixid'];
$data[$i]['ticketid']=$row['ticketid'];
$data[$i]['subject']=$row['subject'];
$data[$i]['priority']=$row['priority'];
$data[$i]['opened']=$row['opened'];
$data[$i]['closed']=$row['closed'];
$i++;
}
}
return $data;
}
function getTicketsDetails($prefixid)
{
$sql=new mysql;
$sql->connect();
$i=0;
$result=mysql_query("SELECT * FROM tickets WHERE prefixid='".mysql_real_escape_string($prefixid)."' ORDER BY id");
if(mysql_num_rows($result)!=0)
{
while($row=mysql_fetch_assoc($result))
{
if($i==0)
{
$data[0]['customerid']=$row['customerid'];
$data[0]['prefixid']=$row['prefixid'];
$data[0]['ticketid']=$row['ticketid'];
$data[0]['subject']=$row['subject'];
$data[0]['priority']=$row['priority'];
}
if($row['msg']!='')
{
$data[$i]['msg']=$row['msg'];
$data[$i]['opened']=$row['opened'];
}
else
{
$data[$i]['support_msg']=$row['support_msg'];
$data[$i]['closed']=$row['closed'];
}
$i++;
}
return $data;
}
else
{
return false;
}
}
function getCustomersInfo($id)
{
$sql=new mysql;
$sql->connect();
$data=mysql_query("SELECT * FROM customers WHERE id='".$id."' LIMIT 1");
$data=mysql_fetch_assoc($data);
foreach($data as $key => $value)
{
$data[$key]=htmlentities($data[$key]);
}
return $data;
$sql->close();
}
function answerTicket($customerid, $prefixid, $msg)
{
$sql=new mysql;
$sql->connect();
$msg=htmlspecialchars($msg);
$msg=nl2br($msg);
mysql_query("INSERT INTO tickets
(customerid, prefixid, ticketid, support_msg, closed)
VALUES ('".$customerid."', '".$prefixid."', '".rand(1000, 9999)."', '".mysql_real_escape_string($msg)."', '".mktime()."')");
return true;
$sql->close();
}
function closedOrOpened($prefixid)
{
$sql=new mysql;
$sql->connect();
$result=mysql_query("SELECT closed, opened FROM tickets WHERE prefixid='".$prefixid."' ORDER BY id DESC");
$row=mysql_fetch_assoc($result);
return ($row['opened']!=0) ? true : false; // Wenn Support eine Antwort schreibt wird der timestamp in Spalte closed eingetragen und Ticket ist geschlossen; opened bleibt leer.
$sql->close();
}
function getAllCustomers()
{
$sql=new mysql;
$sql->connect();
$result=mysql_query("SELECT id, offer, salutation, name FROM customers ORDER by id");
$data=array();
$i=0;
while($row=mysql_fetch_assoc($result))
{
$data[$i]['id']=$row['id'];
$data[$i]['salutation']=$row['salutation'];
$data[$i]['name']=htmlentities($row['name']);
$i++;
}
return $data;
$sql->close();
}
}
?>
Kommentar