Checking a query, help please.

[Helena 0]

Hello everyone,

 

Could someone to help me out with this query check?

 

This is the script I'm using (see bottom), currently, the script checks if there any any open support tickets (@cu or @checkunread).

However, it lists all the tickets I have... so say I have a total of 9 tickets, no matter open, closed, resolved or whatnot, it'll list all the 9 tickets in the @cu or @checkunread message

 

what I want is for it to not list closed and resolved tickets in the "You currently have %s unread support tickets" message.

 

The status (see image) shows the statuses of the tickets, to clarify, 0 = close, and 2 = resolved. The @checkunread needs to exclude these tickets in the message.

So say, I have 9 tickets but 2 are closed and 1 is resolved, then when I use @checkunread, it'll show the remaining 6 tickets.

 

 

 

I was thinking of something like this, but it's not working....

 

(Thank you so much for whoever is able to help me! You have my forever gratitude)

 

set .@unreadmsg$, "You have %d unread support tickets.";

set .@sql$, "SELECT status FROM cp_support_tickets"; // <- this is the table.

if (getgmlevel() > 0) {

query_sql "select status from cp_support_tickets", .@status;

if ( .@status == "0" ) {

set .@sql$, .@sql$ - "1";

 

 

Here is the whole script:

 

-	script	atcmd_ticket_notify	-1,{	OnInit:		bindatcmd("checkunread", "atcmd_ticket_notify::OnAtcommand");		bindatcmd("cu", "atcmd_ticket_notify::OnAtcommand");	end;		OnAtcommand:		set .@unreadmsg$, "You have %d unread support tickets.";		set .@sql$, "SELECT id, department, ticket_read FROM cp_support_tickets WHERE ";				if (getgmlevel() > 0) {			set .@sql$, .@sql$ + "1";		} else {			set .@sql$, .@sql$ + "account_id = " + getcharid(3) + " AND unread = 1";		}		set .@count, query_sql(.@sql$, @id, @department, @ticket_read$);		if (.@count) {			if (getgmlevel() == 0) {				dispbottom(sprintf(.@unreadmsg$, .@count));				end;			}						if (.@count > 1) {				set .@ticket_count, 0;								for (set .@i, 0; .@i < getarraysize(@id); .@i++) {					set .@isRead, 0;										query_sql("SELECT group_id FROM cp_support_dep WHERE id = " + @department[.@i], @group_id);										if (@ticket_read$ != "") {						explode(.@tickets_read$, @ticket_read$[.@i], ",");												for (set .@j, 0; .@j < getarraysize(.@tickets_read$); .@j++) {							if (atoi(.@tickets_read$[.@j]) == getcharid(3)) {								set .@isRead, 1;								break;							}						}					}										if (@group_id <= getgmlevel() && .isRead == 0) {						.@ticket_count++;					}				}				dispbottom(sprintf(.@unreadmsg$, .@count));				end;			} else {				query_sql("SELECT group_id FROM cp_support_dep WHERE id = " + @department, @group_id);				set .@isRead, 0;								if (@ticket_read$ != "") {					explode(.@tickets_read$, @ticket_read$, ",");											for (set .@j, 0; .@j < getarraysize(.@tickets_read$); .@j++) {						if (atoi(.@tickets_read$[.@j]) == getcharid(3)) {							set .@isRead, 1;							break;						}					}				}								if (@group_id <= getgmlevel() && .@isRead == 0) {					dispbottom(sprintf(.@unreadmsg$, .@count));					end;				} else {					dispbottom(sprintf(.@unreadmsg$, 0));					end;				}			}		} else {			dispbottom(sprintf(.@unreadmsg$, 0));			end;		}	end;}

Edited February 1, 2014 by Helena

[jaBote 438]

You're not marking the tickets as read on the database, just player side on a temporary script var, so it'll always be gone once the NPC is finished.

 

You should send an UPDATE query to make the read tickets be marked as read.