//===== 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")){case1:goto L_NewTicket;case2:goto L_ListTickets;case3: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])){case1:goto L_Main;case2:set @view, 0;goto L_ViewTicket;case3:set @view, 1;goto L_ViewTicket;case4:set @view, 2;goto L_ViewTicket;case5:set @view, 3;goto L_ViewTicket;case6:set @view, 4;goto L_ViewTicket;case7:set @view, 5;goto L_ViewTicket;case8:set @view, 6;goto L_ViewTicket;case9:set @view, 7;goto L_ViewTicket;case10:set @view, 8;goto L_ViewTicket;case11: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")){case1: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;case2: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;case3: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")){case1: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")){case1:set @temptic, 1;goto L_GMListTickets;case2:set @temptic, 2;goto L_GMListTickets;case3:set @temptic, 3;goto L_GMListTickets;case4:set @temptic, 4;goto L_GMListTickets;case5:}case2:set @temptic, 0;switch(select("By Title","By Content","By Ticket ID","By Char Name","By Char ID","Back")){case1: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;case2: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;case3: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;case4: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;case5: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;case6:}case3:close;case4: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];elseif( @tickets > 1)set @display$[1], @charname$[@temp+1]+", "+ @title2$[@temp+1];elseif( @tickets > 2)set @display$[2], @charname$[@temp+2]+", "+ @title2$[@temp+2];elseif( @tickets > 3)set @display$[3], @charname$[@temp+3]+", "+ @title2$[@temp+3];elseif( @tickets > 4)set @display$[4], @charname$[@temp+4]+", "+ @title2$[@temp+4];elseif( @tickets > 5)set @display$[5], @charname$[@temp+5]+", "+ @title2$[@temp+5];elseif( @tickets > 6)set @display$[6], @charname$[@temp+6]+", "+ @title2$[@temp+6];elseif( @tickets > 7)set @display$[7], @charname$[@temp+7]+", "+ @title2$[@temp+7];elseif( @tickets > 8)set @display$[8], @charname$[@temp+8]+", "+ @title2$[@temp+8];elseset @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])){case1:goto L_GM;case2: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;case3:set @view, @temp;goto L_GMViewTicket;case4:set @view, @temp+1;goto L_GMViewTicket;case5:set @view, @temp+2;goto L_GMViewTicket;case6:set @view, @temp+3;goto L_GMViewTicket;case7:set @view, @temp+4;goto L_GMViewTicket;case8:set @view, @temp+5;goto L_GMViewTicket;case9:set @view, @temp+6;goto L_GMViewTicket;case10:set @view, @temp+7;goto L_GMViewTicket;case11:set @view, @temp+8;goto L_GMViewTicket;case12: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")){case1: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;case2: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;case3:mes"[Support Tickets]";mes"Are you POSITIVe you want to delete this ticket?!";next;switch(select("No","Yes")){case1:goto L_GMViewTickets2;case2: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;}case4:next;goto L_GMListTickets2;}OnWhisperGlobal:if(getgmlevel() < 40)goto L_Main;goto L_GM;}