<?php include('inc/header.php'); ?>
<h1>Ticketsystem - Backend</h1>
<div class="container">
	<script type="text/javascript">
	function toggleMe(a){var e=document.getElementById(a);
	  if(!e)return true;
		  if(e.style.display=="none"){e.style.display=""}
		  else {e.style.display="none"}
	  return true;
	}
	</script>
	<table width="100%" cellpadding="2" cellspacing="0">
<?php
@odbc_exec($odbc_connect, 'USE [' . $_CONFIG['db_databases']['web'] . ']');
if (isset($_GET['edit'])) {
	$ticket_a = @odbc_fetch_array(@odbc_exec($odbc_connect, 'SELECT * FROM [TICKET_THREADS] WHERE [id] = '.$_GET['edit'].' ORDER BY [status] ASC, [time] DESC'));
	$ticket_answer_q = @odbc_exec($odbc_connect, 'SELECT [text], [time], [account] FROM [TICKET_POSTS] WHERE [tid] = '.$_GET['edit'].' ORDER BY [id] ASC');
	
	if(($ticket_a['status'] == 0 OR $ticket_a['status'] == 2) AND $_GET['edit'] == $ticket_a['id']) {
		@odbc_exec($odbc_connect, '
			UPDATE [TICKET_THREADS]
			SET 	[status] = 2, 
					[modtime] = '.time().', 
					[byadmin] = \''.$_SESSION['user'].'\', 
					[byadminip] = \''.$_SERVER['REMOTE_ADDR'].'\' 
			WHERE [id] = '.$ticket_a['id']
		);
		
			if (isset($_POST['answer']) OR isset($_POST['close'])) {
				if(empty($_POST['problem'])) {echo '<div class="fail">Du musst das Feld ausfüllen, um eine Antwort auf das Ticket abzusenden.</div>';}
				else {
					$problem = @mssql_escape_string($_POST['problem']);
					$ip = $_SERVER['REMOTE_ADDR'];
					
					if (isset($_POST['answer'])) {$statusid = 1;} if (isset($_POST['close'])) {$statusid = 3;}
					odbc_exec($odbc_connect, '
						UPDATE [TICKET_THREADS]
						SET 	[status] = '.$statusid.', 
								[byadmin] = \''.$_SESSION['user'].'\', 
								[byadminip] = \''.$ip.'\', 
								[modtime] = '.time().' 
						WHERE [id] = '.$ticket_a['id'] . '
						
						INSERT INTO [TICKET_POSTS] (
							[tid],
							[account], 
							[text], 
							[ip],
							[time]
						) VALUES (
							'.$ticket_a['id'].', 
							\''.$_SESSION['user'].'\',
							\''.$problem.'\',
							\''.$ip.'\',
							\''.time().'\')'
					);
					print '<meta http-equiv="refresh" content="0; URL=ticketsys.php">';
				}
			}
echo '		<tr>
				<td>
					<table><tr>
						<td><b>'.$ticket_a['account'].'</b> übermittelte am <b>'.date('d.m.Y', $ticket_a['time']).' um '.date('H:i', $ticket_a['time']).'</b> Uhr folgendes Ticket:
					</tr></table>
					<table width="100%" style="border-bottom: #707070 1px dotted; padding:10px 0px 10px 20px;">
						<tr><td><b>'.$ticket_a['title'].'</b></td></tr>
						<tr><td>'.nl2br(html_entity_decode($ticket_a['text'])).'</td></tr>
					</table>';
		if (@odbc_num_rows($ticket_answer_q) > 0) {
			while ($ticket_answer_a = @odbc_fetch_array($ticket_answer_q)) {
				if ($ticket_answer_a['account'] == $_SESSION['user']) {$ticket_answer_a['account'] = 'Du';} else {$ticket_answer_a['account'] = $ticket_answer_a['account'];}
			
			echo '	<table width="100%" style="padding:10px 0px 10px 20px; border-bottom: #707070 1px dotted;">
						<tr><td><b>'.$ticket_answer_a['account'].':</b></td><td align="right"><i>'.date('d.m.Y', $ticket_answer_a['time']).', '.date('H:i', $ticket_answer_a['time']).' Uhr</i></td></tr>
						<tr><td style="padding-left:30px;" colspan="2">'.nl2br(html_entity_decode($ticket_answer_a['text'])).'</td></tr>
					</table>';
			}
		}
echo '			</td>
			</tr>
			<tr>
				<td width="100%" align="center" valign="top">
					<form method="post"><br />
						<textarea name="problem" cols="90" rows="5" style="width:100%; height:300px;"></textarea>
						<br /><input style="margin-top:5px;" type="submit" name="answer" value="Antworten und Ticket geöffnet lassen"/> <input style="margin-top:5px;" type="submit" name="close" value="Antworten und Ticket schließen"/>
					</form>
				</td>
			</tr>';
	} else {print '<meta http-equiv="refresh" content="0; URL=ticketsys.php">';}
}
else {
	if(isset($_GET['all'])) 		{echo '<b>Alle Tickets</b> | <a href="ticketsys.php">Normale Ansicht</a> | <a href="ticketsys.php?new">Nur neue Tickets</a> | <a href="ticketsys.php?deleted">Gelöschte Tickets</a>'; $ticket_q = @odbc_exec($odbc_connect, 'SELECT * FROM [TICKET_THREADS] WHERE [status] < 4 ORDER BY [status] ASC, [modtime] DESC, [time] DESC');}
	elseif(isset($_GET['deleted'])) {echo '<a href="ticketsys.php?all">Alle Tickets</a> | <a href="ticketsys.php">Normale Ansicht</a> | <a href="ticketsys.php?new">Nur neue Tickets</a> | <b>Gelöschte Tickets</b>'; $ticket_q = @odbc_exec($odbc_connect, 'SELECT * FROM [TICKET_THREADS] WHERE [status] = 4 ORDER BY [status] ASC, [modtime] DESC, [time] DESC');}
	elseif(isset($_GET['new'])) 	{echo '<a href="ticketsys.php?all">Alle Tickets</a> | <a href="ticketsys.php">Normale Ansicht</a> | <b>Nur neue Tickets</b> | <a href="ticketsys.php?deleted">Gelöschte Tickets</a>'; $ticket_q = @odbc_exec($odbc_connect, 'SELECT * FROM [TICKET_THREADS] WHERE [status] = 0 or [status] = 2 ORDER BY [status] ASC, [modtime] DESC, [time] DESC');}
	else						 	{echo '<a href="ticketsys.php?all">Alle Tickets</a> | <b>Normale Ansicht</b> | <a href="ticketsys.php?new">Nur neue Tickets</a> | <a href="ticketsys.php?deleted">Gelöschte Tickets</a>'; $ticket_q = @odbc_exec($odbc_connect, 'SELECT * FROM [TICKET_THREADS] WHERE [status] <= 0 or [status] < 3 ORDER BY [status] ASC, [modtime] DESC, [time] DESC');}

	$searchTag = htmlspecialchars(mssql_escape_string($_POST['searchinput']));
echo '	<tr><td colspan="7"><h2>Suche</h2></td></tr>
		<tr><td colspan="7"><form method="post"><input type="submit" value="IP Suche" name="searchIPTickets"/> <input type="text" name="searchinput" style="width:150px;" value="'.$searchTag.'"/> <input type="submit" value="Textsuche" name="searchTextTickets"/></form></td></tr>';
	if(isset($_POST['searchTextTickets']) OR isset($_POST['searchIPTickets'])) {
		if(isset($_POST['searchTextTickets'])) {
			$searchQuery = odbc_exec($odbc_connect, '
				SELECT T.[id], T.[title], T.[account], T.[time], T.[status], T.[byadmin], COUNT(T.[id]) as hits FROM [TICKET_THREADS] AS T
					LEFT JOIN [TICKET_POSTS] AS P ON P.[tid] = T.[id]
				WHERE T.[text] LIKE \'%'.$searchTag.'%\' OR P.[text] LIKE \'%'.$searchTag.'%\' OR T.[account] LIKE \'%'.$searchTag.'%\'
				GROUP BY T.[id], T.[time], T.[title], T.[account], T.[time], T.[status], T.[byadmin]
				ORDER BY T.[time] DESC');
		}
		if(isset($_POST['searchIPTickets'])) {
			$searchQuery = odbc_exec($odbc_connect, '
				SELECT T.[id], T.[title], T.[account], T.[time], T.[status], T.[byadmin], COUNT(T.[id]) as hits FROM [TICKET_THREADS] AS T
					LEFT JOIN [TICKET_POSTS] AS P ON P.[tid] = T.[id]
				WHERE T.[ip] LIKE \'%'.$searchTag.'%\' OR P.[ip] LIKE \'%'.$searchTag.'%\'
				GROUP BY T.[id], T.[time], T.[title], T.[account], T.[time], T.[status], T.[byadmin]
				ORDER BY T.[time] DESC');
		}
echo '	<tr>
			<td width="10%" align="left">ID</td>
			<td width="10%" align="left">Account</td>
			<td width="30%" align="left">Betreff</td>
			<td width="20%" align="center">Letztes Update</td>
			<td width="15%" align="center">Status</td>
			<td width="15%" align="center">Letzter Bearbeiter</td>
		</tr>';
		if(odbc_num_rows($searchQuery) > 0) {
			while($searchResult = @odbc_fetch_array($searchQuery)) {
				if ($searchResult['status'] == 0) {$status = '<font class="red">Offen</font>';} 
				elseif ($searchResult['status'] == 1) {$status = 'Auf Antwort wartend';} 
				elseif ($searchResult['status'] == 2) {
					if($searchResult['byadmin'] == $_SESSION['user']) {$status = '<font class="red blink">Hier noch antworten</font>';}
					else {$status = '<font class="red blink">'.$searchResult['byadmin'].' antwortet gerade</font>';}
				}
				elseif ($searchResult['status'] == 3) {$status = '<i>Geschlossen</i>';}
				elseif ($searchResult['status'] == 4) {$status = '<i>Gelöscht</i>';}
			$searchResultText = @odbc_result(@odbc_exec($odbc_connect, 'SELECT [text] FROM [TICKET_THREADS] WHERE [id] = '.$searchResult['id']), 'text');
			$searchResultPosts = @odbc_exec($odbc_connect, 'SELECT [text], [time], [account] FROM [TICKET_POSTS] WHERE [tid] = '.$searchResult['id'].' ORDER BY [postid] ASC');

echo '	<tr class="row" onclick="return toggleMe(\'search'.$searchResult['id'].'\')" style="cursor:pointer;">
			<td align="left" style="border-top: #c0c0c0 1px solid;"><b>#'.$searchResult['id'].'</b> <i>('.$searchResult['hits'].'x)</i></td>
			<td align="left" style="border-top: #c0c0c0 1px solid; font-weight:none;">'.$searchResult['account'].'</td>
			<td align="left" style="border-top: #c0c0c0 1px solid; font-weight:none;">'.$searchResult['title'].'</td>
			<td align="center" style="border-top: #c0c0c0 1px solid; font-weight:bold;">'.date('d.m.Y, H:i', $searchResult['time']).' Uhr</td>
			<td align="center" style="border-top: #c0c0c0 1px solid; font-weight:bold;">'.$status.'</td>
			<td align="center" style="border-top: #c0c0c0 1px solid; font-weight:bold;">'.$searchResult['byadmin'].'</td>
		</tr>
		<tr id="search'.$searchResult['id'].'" style="font-size:11px; display:none;">
			<td></td>
			<td colspan="4" valign="top">
				<table width="100%" style="border-top: #707070 1px dotted; padding:10px 0px;">
					<tr><td>'.nl2br(html_entity_decode($searchResultText)).'</td></tr>
				</table>';
				
			if (@odbc_num_rows($searchResultPosts) > 0) {
				while ($searchResultConversation = @odbc_fetch_array($searchResultPosts)) {
					if ($searchResultConversation['account'] == $_SESSION['user']) {$searchResultConversation['account'] = 'von dir';} else {$searchResultConversation['account'] = 'von <b>'.$searchResultConversation['account'].'</b>';}
					
echo '			<table width="100%" style="padding:10px 0px; border-top: #707070 1px dotted;">
					<tr><td><b>'.date("d.m.Y", $searchResultConversation['time']).'</b>, <b>'.date("H:i", $searchResultConversation['time']).' Uhr</b> -  Antwort '.$searchResultConversation['account'].':</td></tr>
					<tr><td style="padding-left:30px;">'.nl2br(html_entity_decode($searchResultConversation['text'])).'</td></tr>
				</table>';
				}
			}
			
echo '		</td>
		</tr>';
			}
		} else {echo '<tr class="row"><td colspan="6">Es wurden keine Tickets gefunden, die den Suchtag <b>'.$account.'</b> beinhalten.</td></tr>';}
	} else {$account = '';}
	
echo '</table>
	<br />
	<br />
	<table width="100%" cellpadding="2" cellspacing="0">
		<tr><td colspan=7""><h2>Tickets</h2></td></tr>
		<tr>
			<td valign="top" align="left">ID</td>
			<td valign="top" align="left">Account</td>
			<td width="30%" align="left">Betreff</td>
			<td valign="top" align="left">IP Addresse</td>
			<td valign="top" align="center">Letztes Update</td>
			<td valign="top" align="center">Status</td>
			<td valign="top" align="center">Letzter Bearbeiter</td>
			<td valign="top" align="center"></td>
		</tr>';
			
	while ($ticket_a = @odbc_fetch_array($ticket_q)) {
		$ticket_answer_q = @odbc_exec($odbc_connect, 'SELECT [text], [time], [account] FROM [TICKET_POSTS] WHERE [tid] = '.$ticket_a['id'].' ORDER BY [id] ASC');
	
		if (!empty($_GET['close']) AND $_GET['close'] == $ticket_a['id']) {
				@odbc_exec($odbc_connect, 'UPDATE [TICKET_THREADS] SET [status] = 3, [byadmin] = \''.$_SESSION['user'].'\', [byadminip] = \''.$ip.'\', [modtime] = '.time().' WHERE [id] = '.$ticket_a['id'].'');
				@odbc_exec($odbc_connect, 'INSERT INTO [TICKET_POSTS] ([tid], [account], [text], [ip], [time]) VALUES ('.$ticket_a['id'].', \''.$_SESSION['user'].'\', \''.$problem.'\', \''.$ip.'\', '.$time.')');
				print '<meta http-equiv="refresh" content="0; URL=ticketsys.php">';
		}
		if (!empty($_GET['delete']) AND $_GET['delete'] == $ticket_a['id']) {
				$ip = $_SERVER['REMOTE_ADDR']; $time = time(); 
				@odbc_exec($odbc_connect, 'UPDATE [TICKET_THREADS] SET [status] = 4, [byadmin] = \''.$_SESSION['user'].'\', [byadminip] = \''.$ip.'\', [modtime] = '.time().' WHERE [id] = '.$ticket_a['id'].'');
				print '<meta http-equiv="refresh" content="0; URL=ticketsys.php">';
		}
	
		$style = ''; $schloss = ''; $bearbeitung = ''; $onclick = 'return toggleMe(\'ticketsys'.$ticket_a['id'].'\')'; $delete = '';
		if ($ticket_a['status'] == 0) {$status = '<font class="red">Offen</font>'; $schloss = '<a href="ticketsys.php?close='.$ticket_a['id'].'"><img src="../../images/icons/misc/schloss.png" title="Ticket schließen"/></a>'; $bearbeitung = '<a href="ticketsys.php?edit='.$ticket_a['id'].'"><img src="../images/edit.png" title="Ticket bearbeiten"/></a>'; $delete = '<a href="ticketsys.php?delete='.$ticket_a['id'].'"><img src="../images/delete.png" title="Ticket löschen"/></a>';} elseif ($ticket_a['status'] == 1) {$status = 'Auf Antwort wartend'; $schloss = '<a href="ticketsys.php?close='.$ticket_a['id'].'"><img src="../../images/icons/misc/schloss.png" title="Ticket schließen"/></a>'; $style = 'display:none;'; $delete = '<a href="ticketsys.php?delete='.$ticket_a['id'].'"><img src="../images/delete.png" title="Ticket löschen"/></a>';} elseif ($ticket_a['status'] == 2) {if($ticket_a['byadmin'] == $_SESSION['user']) {$status = '<font class="red blink">Hier noch antworten</font>';} else {$status = '<font class="red blink">'.$ticket_a['byadmin'].' antwortet gerade</font>';} $style = 'display:none;'; $bearbeitung = '<a href="ticketsys.php?edit='.$ticket_a['id'].'"><img src="../images/edit.png" title="Ticket bearbeiten"/></a>'; $schloss ='<a href="ticketsys.php?close='.$ticket_a['id'].'"><img src="../../images/icons/misc/schloss.png" title="Ticket schließen"/></a>';} elseif ($ticket_a['status'] == 3) {$status = '<i>Geschlossen</i>'; $style = 'display:none;'; $delete = '<a href="ticketsys.php?delete='.$ticket_a['id'].'"><img src="../images/delete.png" title="Ticket löschen"/></a>';}
		
		if ($ticket_a['byadmin'] == $_SESSION['user']) {$bearbeiter = 'Du';} elseif (!empty($ticket_a['byadmin'])) {$bearbeiter = $ticket_a['byadmin'];} else {$bearbeiter = '-';}
echo '	<tr onclick="'.$onclick.'" class="row">
			<td align="left" style="border-top: #c0c0c0 1px solid; font-weight:bold; cursor:pointer;">#'.$ticket_a['id'].'</td>
			<td align="left" style="border-top: #c0c0c0 1px solid; font-weight:none; cursor:pointer;">'.$ticket_a['account'].'</td>
			<td align="left" style="border-top: #c0c0c0 1px solid; font-weight:none; cursor:pointer;">'.$ticket_a['title'].'</td>
			<td align="left" style="border-top: #c0c0c0 1px solid; font-weight:none; cursor:pointer;">'.$ticket_a['ip'].'</td>
			<td align="center" style="border-top: #c0c0c0 1px solid; font-weight:bold; cursor:pointer;">'.date('d.m.Y, H:i', $ticket_a['time']).' Uhr</td>
			<td align="center" style="border-top: #c0c0c0 1px solid; font-weight:bold; cursor:pointer;">'.$status.'</td>
			<td align="center" style="border-top: #c0c0c0 1px solid; font-weight:bold; cursor:pointer;">'.$bearbeiter.'</td>
			<td align="right" style="border-top: #c0c0c0 1px solid;">'.$bearbeitung.' '.$schloss.' '.$delete.'</td>
		</tr>
		<tr id="ticketsys'.$ticket_a['id'].'" style="display:none;">
			<td></td>
			<td colspan="7" valign="top" style="font-size:11px; padding-bottom:40px;">
				<table width="100%" style="border-top: #707070 1px dotted; padding:10px 0px;">
					<tr><td>'.nl2br(html_entity_decode($ticket_a['text'])).'</td></tr>
				</table>';
		if (@odbc_num_rows($ticket_answer_q) > 0) {
			if (isset($_POST['addpost'.$ticket_a['id']])) {
				if ($_POST['problem'] == '' OR $_POST['problem'] == 'Schnellantwort') {echo '<div class="fail">Du musst das Feld ausfüllen, um einen Post abzusenden.</div>';}
				elseif (!isset($_POST['date']) or !is_numeric($_POST['date']) or intval($_POST['date']) > time()-6 or intval($_POST['date']) < time()-6*3600) {echo '<div class="fail">Es trat ein Fehler auf. Informiere bitte den Seitenadministrator und versuche es anschließend erneut.</div>';}
				elseif (strlen($_POST['problem']) > 2650) {echo '<div class="fail">Du hast zuviele Zeichen ('.strlen($_POST['problem']).') verwendet. Wenn du beispielsweise deine LOG-Daten hochladen möchtest, so nutze bitte <a href="http://pastebin.com/" target="_blank">diesen externen Uploaddienst</a> und vermerke den Link, mit dem man deine Datei ansehen kann, in dem Ticket.</div>';}
				else {
					$problem = htmlspecialchars(mssql_escape_string($_POST['problem']));
					$ip = $_SERVER['REMOTE_ADDR'];
					@odbc_exec($odbc_connect, 'UPDATE [TICKET_THREADS] SET [status] = 1, [modtime] = '.time().', [byadmin] = \''.$_SESSION['user'].'\', [byadminip] = \''.$_SERVER['REMOTE_ADDR'].'\' WHERE [id] = '.$ticket_a['id']);
					@odbc_exec($odbc_connect, 'INSERT INTO [TICKET_POSTS] ([tid], [account], [text], [ip], [time]) VALUES ('.$ticket_a['id'].', \''.$_SESSION['user'].'\', \''.$problem.'\', \''.$ip.'\', \''.time().'\')');
					print '<meta http-equiv="refresh" content="0; URL='.$_SERVER['REQUEST_URI'].'">';
				}
			}
			while ($ticket_answer_a = @odbc_fetch_array($ticket_answer_q)) {
				if ($ticket_answer_a['account'] == $_SESSION['user']) {$ticket_answer_a['account'] = 'von dir';} else {$ticket_answer_a['account'] = 'von <b>'.$ticket_answer_a['account'].'</b>';}
echo '			<table width="100%" style="padding:10px 0px; border-top: #707070 1px dotted;">
					<tr><td><b>'.date("d.m.Y", $ticket_answer_a['time']).'</b>, <b>'.date("H:i", $ticket_answer_a['time']).' Uhr</b> -  Antwort '.$ticket_answer_a['account'].':</td></tr>
					<tr><td style="padding-left:30px;">'.nl2br(html_entity_decode($ticket_answer_a['text'])).'</td></tr>
				</table>';
			}
			if($ticket_a['status'] < 3 AND !empty($ticket_a['byadmin'])) {
echo'			<table width="100%" style="padding:10px 0px; border-top: #707070 1px dotted;">
					<tr><td>
						<form method="post"><br />
							<input name="date" type="hidden" value="'. time() .'" />
							<textarea name="problem" cols="75" rows="5" style="width:800px; height:120px;">Schnellantwort</textarea><br /><input type="submit" name="addpost'.$ticket_a['id'].'" value="Schnellantwort anhängen"/>
						</form>
					</td></tr>
				</table>';
			}
		}
echo '		</td>
		</tr>';
	}
}
?>
	</table>
</div>
<?php include('inc/footer.php'); ?>