//===== eAthena Unofficial Script ============================
//= Support Ticket System
//===== By: ================================================== 
//= [GM]Xeon
//===== Current Version: ===================================== 
//= 1.2
//===== Compatible With: ===================================== 
//= Latest versions of SQL, stable and trunk?
//===== Description: ========================================= 
//= The core engine for the Support Ticket system, please
//= ensure you first installed the MySQL script located at
//= http://svn.massemu.com/ro/branches/script/ST_1.1.sql
//===== Additional Comments: ================================= 
//= 1.0 Script created ;D [[GM]Xeon]
//= 1.1 Added missed character name display, fixed a couple
//=  other bugs [[GM]Xeon]
//= 1.2 Optimized and added delete tickets function [[GM]Xeon]
//============================================================ 

prontera,155,185,5	script	Support Tickets	4_BULLETIN_BOARD2,{
if (getgmlevel() >= 40) goto L_GM;
L_Main:
	mes "[Support Tickets]";
	mes "Hello, I'm the support ticket NPC, how can I help you?";
	next;
	switch(select("Post a new ticket","View Ticket(s)","Exit")) {
		case 1:
			goto L_NewTicket;
		case 2:
			goto L_ListTickets;
		case 3:
			close;
		}

L_NewTicket:
	mes "[Support Tickets]";
	mes "Enter the title of the new ticket (Max 24 chars.), enter one space to cancel.";
	next;
	input @msg_title$;
	set @msg_title$, escape_sql(@msg_title$);
	if (@msg_title$ == " ") goto L_Main;
	mes "[Support Tickets]";
	mes "Enter the message, enter one space to cancel.";
	next;
	input @msg_base$;
	set @msg_base$, escape_sql(@msg_base$);
	if (@msg_base$ == " ") goto L_Main;
	query_sql "insert into `support_ticket` (char_name,char_id,title,message,date) values ('"+escape_sql(strcharinfo(0))+"',"+getcharid(0)+",'"+@msg_title$+"','"+@msg_base$+"', now())";
	mes "[Support Tickets]";
	mes "Support ticket has been successfully posted.";
	next;
	goto L_Main;

L_ListTickets:
	set @tickets, query_sql("select ticket_id,status,title,message,date from `support_ticket` where char_id = "+getcharid(0)+" order by date desc limit 10",@ticket_id,@status,@title$,@message$,@date$);
	mes "[Support Tickets]";
	if (@tickets > 0) mes "Select a ticket to view";
	if (@tickets == 0) {
		mes "You currently have no support tickets";
		next;
		goto L_Main;
		}
	set @temp,0;
	while (@temp < @tickets) {
		if (@status[@temp] == 0) set @title2$[@temp], "^339900"+@title$[@temp]+"^000000";
		if (@status[@temp] == 1) set @title2$[@temp], "^FF0000"+@title$[@temp]+"^000000";
		if (@status[@temp] > 1) set @title2$[@temp], "^FF9900"+@title$[@temp]+"^000000";
		set @temp,@temp+1;
		}
	next;
	switch(select("Back",@title2$[0],@title2$[1],@title2$[2],@title2$[3],@title2$[4],@title2$[5],@title2$[6],@title2$[7],@title2$[8],@title2$[9])) {
		case 1:
			goto L_Main;
		case 2:
			set @view, 0;
			goto L_ViewTicket;
		case 3:
			set @view, 1;
			goto L_ViewTicket;
		case 4:
			set @view, 2;
			goto L_ViewTicket;
		case 5:
			set @view, 3;
			goto L_ViewTicket;
		case 6:
			set @view, 4;
			goto L_ViewTicket;
		case 7:
			set @view, 5;
			goto L_ViewTicket;
		case 8:
			set @view, 6;
			goto L_ViewTicket;
		case 9:
			set @view, 7;
			goto L_ViewTicket;
		case 10:
			set @view, 8;
			goto L_ViewTicket;
		case 11:
			set @view, 9;
			goto L_ViewTicket;
		}

L_ViewTicket:
	mes "[Support Tickets]";
	mes "^808080Title: ^9900CC"+@title$[@view]+"^000000";
	mes "^808080Message: ^9900CC"+@message$[@view]+"^000000";
	set @replies, query_sql("select reply_number,char_name,message,status_change from `support_response` where ticket_id = "+@ticket_id[@view]+" order by reply_number asc",@reply_number,@char_name$,@message_reply$,@status_change);
	set @temp, 0;
	while (@temp < @replies) {
		mes "^C9C9C9-^000000";
		if (@message$[@view] == "†" && @char_name$[@temp] == strcharinfo(0)) mes "^808080Status change from: ^339900"+@char_name$[@temp]+"^000000";
		if (@message$[@view] == "†" && @char_name$[@temp] != strcharinfo(0)) mes "^808080Status change from: ^0066FF"+@char_name$[@temp]+"^000000";
		if (@message$[@view] != "†" && @char_name$[@temp] == strcharinfo(0)) {
			mes "^808080Reply from: ^339900"+@char_name$[@temp]+"^000000";
			mes "^808080Message: ^339900"+@message_reply$[@temp]+"^000000";
			}
		if (@message$[@view] != "†" && @char_name$[@temp] != strcharinfo(0)) {
			mes "^808080Reply from: ^0066FF"+@char_name$[@temp]+"^000000";
			mes "^808080Message: ^0066FF"+@message_reply$[@temp]+"^000000";
			}
		if (@status_change[@temp] == 0) mes "^FF9900>Status changed to ^339900OPEN^000000";
		if (@status_change[@temp] == 1) mes "^FF9900>Status changed to ^FF0000CLOSED^000000";
		if (@status_change[@temp] == 2) mes "^FF9900>Status changed to WAITING^000000";
		if (@status_change[@temp] == 3) mes "^FF9900>Status changed to ON HOLD^000000";
		set @temp,@temp+1;
		}
	next;
	switch(select(((@status[@view]==1)?"^C9C9C9":"")+"Reply^000000",((@status[@view]==1)?"^C9C9C9":"")+"Close Ticket^000000","Back")) {
		case 1:
			if (@status[@view] == 1) {
				mes "[Support Tickets]";
				mes "You can't reply to closed tickets!";
				next;
				goto L_ViewTicket;
				}
			mes "[Support Tickets]";
			mes "Enter the reply, enter one space to cancel";
			input @msg$;
			set @msg$, escape_sql(@msg$);
			if (@msg$ == " ") {
				next;
				goto L_ViewTicket;
				}
			if (@status[@view] == 2) {
				query_sql "insert into `support_response` values ("+@ticket_id[@view]+","+(@replies+1)+",'"+escape_sql(strcharinfo(0))+"',"+getcharid(0)+",'"+@msg$+"',0,now())";
				query_sql "update `support_ticket` set status = 0 where ticket_id = "+@ticket_id[@view];
				} else {
				query_sql "insert into `support_response` values ("+@ticket_id[@view]+","+(@replies+1)+",'"+escape_sql(strcharinfo(0))+"',"+getcharid(0)+",'"+@msg$+"','-1',now())";
				}
			mes "[Support Tickets]";
			mes "Reply posted";
			next;
			goto L_ListTickets;
		case 2:
			if (@status[@view] == 1) {
				mes "[Support Tickets]";
				mes "This ticket is already closed!";
				next;
				goto L_ViewTicket;
				}
			query_sql "insert into `support_response` values ("+@ticket_id[@view]+","+(@replies+1)+",'"+escape_sql(strcharinfo(0))+"',"+getcharid(0)+",'†',1,now())";
			query_sql "update `support_ticket` set status = 1 where ticket_id = "+@ticket_id[@view];
			mes "[Support Tickets]";
			mes "Support ticket closed!";
			next;
			goto L_ListTickets;
		case 3:
			next;
			goto L_ListTickets;
		}

L_GM:
	deletearray @display$, 128;
	mes "[Support Tickets]";
	mes "Hello GM, how can I help?";
	next;
	switch(select("View Tickets","Search Tickets","Exit","Test")) {
		case 1:
			set @open, query_sql("select * from `support_ticket` where status = 0 order by date desc limit 128",@open_ticket_id,@open_status,@open_charname$,@open_charid,@open_title$,@open_message$,@open_date$);
			set @waiting, query_sql("select * from `support_ticket` where status = 2 order by date desc limit 128",@waiting_ticket_id,@waiting_status,@waiting_charname$,@waiting_charid,@waiting_title$,@waiting_message$,@waiting_date$);
			set @onhold, query_sql("select * from `support_ticket` where status = 3 order by date desc limit 128",@onhold_ticket_id,@onhold_status,@onhold_charname$,@onhold_charid,@onhold_title$,@onhold_message$,@onhold_date$);
			set @closed, query_sql("select * from `support_ticket` where status = 1 order by date desc limit 128",@closed_ticket_id,@closed_status,@closed_charname$,@closed_charid,@closed_title$,@closed_message$,@closed_date$);
			switch(select("^339900View Open ["+@open+"]","^FF9900View Waiting ["+@waiting+"]","^FF9900View On Hold ["+@onhold+"]","^FF0000View Closed ["+@closed+"]","^000000Back")) {
				case 1:
					set @temptic, 1;
					goto L_GMListTickets;
				case 2:
					set @temptic, 2;
					goto L_GMListTickets;
				case 3:
					set @temptic, 3;
					goto L_GMListTickets;
				case 4:
					set @temptic, 4;
					goto L_GMListTickets;
				case 5:
				}
		case 2:
			set @temptic, 0;
			switch(select("By Title","By Content","By Ticket ID","By Char Name","By Char ID","Back")) {
				case 1:
					mes "[Support Tickets]";
					mes "Enter part or the full ticket name to search";
					input @input$;
					next;
					set @tickets, query_sql("select ticket_id,char_name,char_id,status,title,message,date from `support_ticket` where title regexp '"+escape_sql(@input$)+"' order by date desc limit 128",@ticket_id,@charname$,@charid,@status,@title$,@message$,@date$);
					goto L_GMListTickets;
				case 2:
					mes "[Support Tickets]";
					mes "Enter part or the full message content to search";
					input @input$;
					next;
					set @tickets, query_sql("select ticket_id,char_name,char_id,status,title,message,date from `support_ticket` where message regexp '"+escape_sql(@input$)+"' order by date desc limit 128",@ticket_id,@charname$,@charid,@status,@title$,@message$,@date$);
					goto L_GMListTickets;
				case 3:
					mes "[Support Tickets]";
					mes "Enter the ticket ID to search for";
					input @input;
					next;
					set @tickets, query_sql("select ticket_id,char_name,char_id,status,title,message,date from `support_ticket` where ticket_id = "+@input+" order by date desc limit 128",@ticket_id,@charname$,@charid,@status,@title$,@message$,@date$);
					goto L_GMListTickets;
				case 4:
					mes "[Support Tickets]";
					mes "Enter part or the full character name";
					input @input$;
					next;
					set @tickets, query_sql("select ticket_id,char_name,char_id,status,title,message,date from `support_ticket` where char_name regexp '"+escape_sql(@input$)+"' order by date desc limit 128",@ticket_id,@charname$,@charid,@status,@title$,@message$,@date$);
					set @search, 1;
					goto L_GMListTickets;
				case 5:
					mes "[Support Tickets]";
					mes "Enter the character ID to search for";
					input @input;
					next;
					set @tickets, query_sql("select ticket_id,char_name,char_id,status,title,message,date from `support_ticket` where char_id = "+@input+" order by date desc limit 128",@ticket_id,@charname$,@charid,@status,@title$,@message$,@date$);
					goto L_GMListTickets;
				case 6:
				}
		case 3:
			close;
		case 4:
			next;
			goto L_Main;
		}

L_GMListTickets:
	//cleararray ??
	if (@temptic == 1) {
		set @tickets, @open;
		copyarray @ticket_id[0],@open_ticket_id[0],128;
		copyarray @charname$[0],@open_charname$[0],128;
		copyarray @charid[0],@open_charid[0],128;
		copyarray @status[0],@open_status[0],128;
		copyarray @title$[0],@open_title$[0],128;
		copyarray @message$[0],@open_message$[0],128;
		copyarray @date$[0],@open_date$[0],128;
		}
	if (@temptic == 2) {
		set @tickets, @waiting;
		copyarray @ticket_id[0],@waiting_ticket_id[0],128;
		copyarray @charname$[0],@waiting_charname$[0],128;
		copyarray @charid[0],@waiting_charid[0],128;
		copyarray @status[0],@waiting_status[0],128;
		copyarray @title$[0],@waiting_title$[0],128;
		copyarray @message$[0],@waiting_message$[0],128;
		copyarray @date$[0],@waiting_date$[0],128;
		}
	if (@temptic == 3) {
		set @tickets, @onhold;
		copyarray @ticket_id[0],@onhold_ticket_id[0],128;
		copyarray @charname$[0],@onhold_charname$[0],128;
		copyarray @charid[0],@onhold_charid[0],128;
		copyarray @status[0],@onhold_status[0],128;
		copyarray @title$[0],@onhold_title$[0],128;
		copyarray @message$[0],@onhold_message$[0],128;
		copyarray @date$[0],@onhold_date$[0],128;
		}
	if (@temptic == 4) {
		set @tickets, @closed;
		copyarray @ticket_id[0],@closed_ticket_id[0],128;
		copyarray @charname$[0],@closed_charname$[0],128;
		copyarray @charid[0],@closed_charid[0],128;
		copyarray @status[0],@closed_status[0],128;
		copyarray @title$[0],@closed_title$[0],128;
		copyarray @message$[0],@closed_message$[0],128;
		copyarray @date$[0],@closed_date$[0],128;
		}
L_GMListTickets2:
	if (@tickets == 0) {
		mes "[Support Tickets]";
		mes "There are no tickets to display in this category!";
		next;
		goto L_GM;
		}
	mes "[Support Tickets]";
	mes "Select a ticket to view, delete, edit or reply to";
	set @temp,0;
	while (@temp < @tickets) {
		if (@status[@temp] == 0) set @title2$[@temp], "^339900"+@title$[@temp]+"^000000";
		if (@status[@temp] == 1) set @title2$[@temp], "^FF0000"+@title$[@temp]+"^000000";
		if (@status[@temp] > 1) set @title2$[@temp], "^FF9900"+@title$[@temp]+"^000000";
		set @temp,@temp+1;
		}
	next;
	set @temp, 0;
L_GMMore:
	set @temp2, @temp+11;
	if (@temp2 > @tickets) set @temp2, @tickets;
	if (@temp+1 != @temp2) {
		mes "Viewing tickets ["+(@temp+1)+"-"+@temp2+"]";
		} else {
		mes "Viewing ticket "+(@temp+1);
		}
	if (@search == 0) {
		set @display$[0], @title2$[@temp];
		set @display$[1], @title2$[@temp+1];
		set @display$[2], @title2$[@temp+2];
		set @display$[3], @title2$[@temp+3];
		set @display$[4], @title2$[@temp+4];
		set @display$[5], @title2$[@temp+5];
		set @display$[6], @title2$[@temp+6];
		set @display$[7], @title2$[@temp+7];
		set @display$[8], @title2$[@temp+8];
		set @display$[9], @title2$[@temp+9];
		}
	if (@search == 1) {
		if ( @tickets > 0 ) set @display$[0], @charname$[@temp] + ", " + @title2$[@temp];
		else if ( @tickets > 1 ) set @display$[1], @charname$[@temp+1] + ", " + @title2$[@temp+1];
		else if ( @tickets > 2 ) set @display$[2], @charname$[@temp+2] + ", " + @title2$[@temp+2];
		else if ( @tickets > 3 ) set @display$[3], @charname$[@temp+3] + ", " + @title2$[@temp+3];
		else if ( @tickets > 4 ) set @display$[4], @charname$[@temp+4] + ", " + @title2$[@temp+4];
		else if ( @tickets > 5 ) set @display$[5], @charname$[@temp+5] + ", " + @title2$[@temp+5];
		else if ( @tickets > 6 ) set @display$[6], @charname$[@temp+6] + ", " + @title2$[@temp+6];
		else if ( @tickets > 7 ) set @display$[7], @charname$[@temp+7] + ", " + @title2$[@temp+7];
		else if ( @tickets > 8 ) set @display$[8], @charname$[@temp+8] + ", " + @title2$[@temp+8];
		else set @display$[9], @charname$[@temp+9] + ", " + @title2$[@temp+9];
		}
	set @search, 0;
	switch(select("Back","More",@display$[0],@display$[1],@display$[2],@display$[3],@display$[4],@display$[5],@display$[6],@display$[7],@display$[8],@display$[9])) {
		case 1:
			goto L_GM;
		case 2:
			if (@temp2 < 120) {
				set @temp, @temp+10;
				} else {
				mes "[Support Tickets]";
				mes "You can only view up to 128 tickets per category without changing their status'!";
				next;
				goto L_GM;
				}
			if (@temp2 + 10 >= @tickets) {
				mes "[Support Tickets]";
				mes "There is no more tickets to display!";
				next;
				goto L_GM;
				}
			set @temp, @temp+10;
			goto L_GMMore;
		case 3:
			set @view, @temp;
			goto L_GMViewTicket;
		case 4:
			set @view, @temp+1;
			goto L_GMViewTicket;
		case 5:
			set @view, @temp+2;
			goto L_GMViewTicket;
		case 6:
			set @view, @temp+3;
			goto L_GMViewTicket;
		case 7:
			set @view, @temp+4;
			goto L_GMViewTicket;
		case 8:
			set @view, @temp+5;
			goto L_GMViewTicket;
		case 9:
			set @view, @temp+6;
			goto L_GMViewTicket;
		case 10:
			set @view, @temp+7;
			goto L_GMViewTicket;
		case 11:
			set @view, @temp+8;
			goto L_GMViewTicket;
		case 12:
			set @view, @temp+9;
			goto L_GMViewTicket;
		}

L_GMViewTicket:
	mes "[Support Tickets]";
	mes "^808080Posted by: ^9900CC"+@charname$[@view]+" ["+@charid[@view]+"]^000000";
	mes "^808080Title: ^9900CC"+@title$[@view]+"^000000";
	mes "^808080Message: ^9900CC"+@message$[@view]+"^000000";
	set @replies, query_sql("select reply_number,char_name,char_id, message,status_change from `support_response` where ticket_id = "+@ticket_id[@view]+" order by reply_number asc",@reply_number,@char_name$,@char_id,@message_reply$,@status_change);
	set @temp, 0;
	while (@temp < @replies) {
		mes "^C9C9C9-^000000";
		if (@message$[@view] == "†" && @char_name$[@temp] == strcharinfo(0)) mes "^808080Status change from: ^339900"+@char_name$[@temp]+"^000000";
		if (@message$[@view] == "†" && @char_name$[@temp] != strcharinfo(0)) mes "^808080Status change from: ^0066FF"+@char_name$[@temp]+"^000000";
		if (@message$[@view] != "†" && @char_name$[@temp] == strcharinfo(0)) {
			mes "^808080Reply from: ^0066FF"+@char_name$[@temp]+" ["+ @char_id +"]^000000";
			mes "^808080Message: ^0066FF"+@message_reply$[@temp]+"^000000";
			}
		if (@message$[@view] != "†" && @char_name$[@temp] != strcharinfo(0)) {
			mes "^808080Reply from: ^339900"+@char_name$[@temp]+" ["+ @char_id +"]^000000";
			mes "^808080Message: ^339900"+@message_reply$[@temp]+"^000000";
			}
		if (@status_change[@temp] == 0) mes "^FF9900>Status changed to ^339900OPEN^000000";
		if (@status_change[@temp] == 1) mes "^FF9900>Status changed to ^FF0000CLOSED^000000";
		if (@status_change[@temp] == 2) mes "^FF9900>Status changed to WAITING^000000";
		if (@status_change[@temp] == 3) mes "^FF9900>Status changed to ON HOLD^000000";
		set @temp,@temp+1;
		}
	next;
	switch(select("Reply","Change Status [No Reply]","Delete Ticket","Back")) {
		case 1:
			mes "[Support Tickets]";
			mes "Enter the reply, enter one space to cancel";
			input @msg$;
			set @msg$, escape_sql(@msg$);
			if (@msg$ == " ") {
				next;
				goto L_GMViewTickets2;
				}
			next;
			mes "[Support Tickets]";
			mes "Enter the new status";
			next;
			menu "^FF9900Waiting",-,"^FF0000Closed",-,"^FF9900On Hold",-,"^339900Open",-,"^000000No Change",-;
			if (@menu == 1) set @stat, 2;
			if (@menu == 2) set @stat, 1;
			if (@menu == 3) set @stat, 3;
			if (@menu == 4) set @stat, 0;
			if (@menu == 5) set @stat, -1;
			if (@stat > -1 && @stat != @status[@view]) {
					query_sql "insert into `support_response` values ("+@ticket_id[@view]+","+(@replies+1)+",'"+escape_sql(strcharinfo(0))+"',"+getcharid(0)+",'"+@msg$+"',"+@stat+",now())";
					query_sql "update `support_ticket` set status = "+@stat+" where ticket_id = "+@ticket_id[@view];
				} else {
				query_sql "insert into `support_response` values ("+@ticket_id[@view]+","+(@replies+1)+",'"+escape_sql(strcharinfo(0))+"',"+getcharid(0)+",'"+@msg$+"','-1',now())";
				}
			mes "[Support Tickets]";
			mes "Reply posted";
			next;
			goto L_GMListTickets;
		case 2:
			mes "[Support Tickets]";
			mes "Enter the new status";
			next;
			menu "^FF9900Waiting",-,"^FF0000Closed",-,"^FF9900On Hold",-,"^339900Open",-,"^000000No Change",-;
			if (@menu == 1) set @stat, 2;
			if (@menu == 2) set @stat, 1;
			if (@menu == 3) set @stat, 3;
			if (@menu == 4) set @stat, 0;
			if (@menu == 5) set @stat, -1;
			if (@stat > -1 && @stat != @status[@view]) {
				query_sql "insert into `support_response` values ("+@ticket_id[@view]+","+(@replies+1)+",'"+escape_sql(strcharinfo(0))+"',"+getcharid(0)+",'†',"+@stat+",now())";
				query_sql "update `support_ticket` set status = "+@stat+" where ticket_id = "+@ticket_id[@view];
				}
			mes "[Support Tickets]";
			mes "Status changed";
			next;
			goto L_GMListTickets2;
		case 3:
			mes "[Support Tickets]";
			mes "Are you POSITIVe you want to delete this ticket?!";
			next;
			switch(select("No","Yes")) {
			case 1:
				goto L_GMViewTickets2;
			case 2:
				query_sql "delete from `support_ticket` where ticket_id = "+@ticket_id[@view];
				query_sql "delete from `support_response` where ticket_id = "+@ticket_id[@view];
				deletearray @ticket_id[@view],1;
				deletearray @charname$[@view],1;
				deletearray @charid[@view],1;
				deletearray @status[@view],1;
				deletearray @title$[@view],1;
				deletearray @message$[@view],1;
				deletearray @date$[@view],1;
				goto L_GMListTickets2;
			}
		case 4:
			next;
			goto L_GMListTickets2;
		}

OnWhisperGlobal:
	if (getgmlevel() < 40) goto L_Main;
	goto L_GM;
}