Hi, I want to delete a filter using mysql query... Even though it is deleting from database then filter still shows in Favorite Filters. When I click on Admin clog and select delete it says it is already deleted and removed from the favorite lists. But I want that to happen automate this thing. How to resolve this issue.
import groovy.sql.Sql
import java.sql.*
import org.apache.log4j.Logger
import org.apache.log4j.Level
import com.atlassian.jira.ComponentManager
import com.atlassian.mail.Email
import com.atlassian.mail.server.MailServerManager
import com.atlassian.mail.server.SMTPMailServer
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.user.ApplicationUser
def log = Logger.getLogger("test.connector")
log.setLevel(Level.DEBUG)
def driver = Class.forName('com.mysql.jdbc.Driver').newInstance() as Driver
def props = new Properties()
props.setProperty("user", "xxxx")
props.setProperty("password", "xxxxx!")
def conn = driver.connect("xxxxxx/jiradb?useUnicode=true&characterEncoding=UTF8&sessionVariables=storage_engine=InnoDB", props)
def sql = new Sql(conn)
if(sql!=null){
log.debug("connected")
try {
List<String> filternames=[];
List<String> filtername_username=[];
List<String> filterid=[];
def usermanager = ComponentAccessor.getUserManager()
def flag = false
// returns if there is a empty filter
sql.eachRow("select * from searchrequest where reqcontent = ''") {row ->
log.debug("Empty Filter Name: "+row.filtername)
filternames.push((String)row.filtername)
filtername_username.push((String)row.username)
filterid.push((String)row.ID)
}
log.debug(filternames)
log.debug(filtername_username)
log.debug(filterid)
//executes if there is a empty
if(filternames.size()>0){
ComponentManager componentManager = ComponentManager.getInstance()
SMTPMailServer mailServer = ComponentAccessor.getMailServerManager().getDefaultSMTPMailServer()
for(int j=0;j<filternames.size();j++){
String table="";
def filter_user = usermanager.getUserByKey(filtername_username[j])
table+="Filter ID: "+filterid[j]+" FilterName: "+filternames[j]+ " Created By : "+filtername_username[j]+ " \n"
table+= "\n \n Hello "+filter_user.getDisplayName()+", \n \n You have created a empty filter named "+filternames[j]+" which will impact the jira performance. So, We are removing it. Please create a filter with some query in it.\n"
table+= "\n\nThanks and Regards, \nJira Admins \n \n"
try{
log.debug(filterid[j])
log.debug(filternames[j])
//deleting from favoritesassociations
sql.execute("delete from favouriteassociations where entitytype = 'SearchRequest' AND entityid = "+filterid[j]+"")
//deleeting from searchrequest
sql.execute("delete from searchrequest where reqcontent = '' and username = '"+filtername_username[j]+"' and filtername='"+filternames[j]+"'");
log.debug("filter deleted")
flag = false
}
catch (Exception e) {
log.debug("filter not deleted");
flag = true
}
if (mailServer&&flag==false) {
Email email = new Email(filter_user.getEmailAddress())
email.setSubject("Empty Filter Alert ")
String content = table
email.setBody(content)
mailServer.send(email)
log.debug("Email Sent")
log.debug(table)
List<String> admins = ["xxx","xxxx","xxxx"]
for(int i=0;i<admins.size();i++){
SMTPMailServer mailServer1 = ComponentAccessor.getMailServerManager().getDefaultSMTPMailServer()
if(mailServer1){
try{
def admin = usermanager.getUserByKey(admins[i])
log.debug(admin)
def admin_msg=""
admin_msg+= "Hello "+admin.getDisplayName()+" , \n \n"
admin_msg+="We have a empty filter named '"+filternames[j]+"' created by '"+usermanager.getUserByKey(filtername_username[j]).getDisplayName()+"'("+filtername_username[j]+"). We have automatically deleted it and notified the user. \n \n "
admin_msg+="Thanks & Regards, \n"
admin_msg+="Jira Admins"
log.debug(admin_msg)
Email adminemail = new Email("xxxxxxxx")
adminemail.setSubject("Automatic Deletion of Empty Filter Alert -- JIRA TEST ")
String admincontent = admin_msg
adminemail.setBody(admincontent)
mailServer1.send(adminemail)
log.debug("Admin Email Sent")
}
catch (Exception e) {
log.debug("admin not found");
}
}
}
}
else {
log.error("No SMTP mail server defined")
}
}
}
} finally {
sql.close()
conn.close()
}
}
