IoT DAS Frameworks - IT Infra Monitoring and Analytics/IOT_Applications/Performance_Dashboard_Application(Reference App)/source/SA-DashBoard/src/java/com/merit/dashboard/queryexecuter/QueryExecuter.java
public LinkedHashMap<String,String> collectTicketdata(String customer,String resourceType,String date, String date1,String selection) throws IOException, SQLException {
		 ticketInfo=new LinkedHashMap<String, String>();
		try {
			uniqueconnection = ConnectionDAO.getConnection(customer+"_177");
			stat = uniqueconnection.createStatement();
			String szQuery="";

			if(selection.equals("CriticalAlerts"))
			szQuery="select servicename as ServiceName,resourcetype as ResourceType,host as ServerName,array_to_string(array_agg(summaryname),',') as ResourceNames,array_to_string(array_agg(summarycount),',') as ResourceValues,case when sum(criticalcheck)>0 then 'CRITICAL' else 'OK' end as Health,resourceid as ResourceID from (select count(*) as summarycount,attributes6 as host,attributes10 as resourcetype,attributes4 as ServiceName,attributes12 as resourceID,substring(task_summary from position('[' in task_summary) for 25) as summaryname,CASE WHEN substring(task_summary from position('[' in task_summary) for 25) like '%HSTDWN%' THEN 1 ELSE 0 END as criticalcheck from gatask where created_date between  '"+date+"' and '"+date1+"' and status!='Closed' and task_type!='SOX' and attributes10='"+resourceType+"' group by attributes6,summaryname,attributes4,attributes12,attributes10) as cAlert group by servicename, resourcetype, host,resourceid";
			else if(selection.equals("AlertsByAssignee"))
			szQuery="select servicename as ServiceName,resourcetype as ResourceType,host as ServerName,array_to_string(array_agg(assigneenames),',') as ResourceNames,array_to_string(array_agg(ticketcount),',') as ResourceValues,case when host is not null then 'OK' end as Health,resourceid as ResourceID from (select count(*)as ticketcount,g2.attributes6 as host ,g2.attributes10 as resourcetype ,g2.attributes4 as ServiceName,g1.assignee as assigneenames,g2.attributes12 as ResourceID from gatask g2 ,gatasktypeassignee g1 where created_date between  '"+date+"' and '"+date1+"' and g1.taskid=g2.task_id and action_assignee=1 and g2.attributes10='"+resourceType+"'  group by g2.attributes6,g1.assignee,attributes4,g2.attributes10,g2.attributes12) as assig group by servicename, resourcetype, host,resourceid";
			else
			szQuery="select servicename as ServiceName,resourcetype as ResourceType,host as ServerName,array_to_string(array_agg(statusnames),',') as ResourceNames,array_to_string(array_agg(statuscount),',') as ResourceValues,case when host is not null then 'OK' end as Health,resourceid as ResourceID from (select count(*) as statuscount,attributes6 as host,attributes10 as resourcetype,attributes4 as ServiceName,status as statusnames,attributes12 as resourceID from gatask where created_date between  '"+date+"' and '"+date1+"' and attributes6!='none' and attributes10='"+resourceType+"' and attributes10!='none' and attributes6 is not null and attributes12 is not null group by attributes6,attributes4,status,attributes10,attributes12) as sAlert group by servicename, resourcetype, host,resourceid";

	/**
	 * ***************************************************************************************************
	 * This Query will give Modified result from all necessary tables like gatasktypeassignee, gatask table ..
	 * here concatenating the result came from  GAtask for Summary Count and checking hostdown status
	 * array_agg function in postgresql  below 8 version we have to create by following command:
		create aggregate array_agg ( sfunc = array_append,  basetype = anyelement,  stype = anyarray,  initcond = '{}'  );
	 * ***************************************************************************************************
	 */
			log.debug("Ticket Query /"+resourceType+"/\n"+szQuery);

			ResultSet rs = stat.executeQuery(szQuery);

			while (rs.next()) {
				String alertCount="[]";
				String alertName="";
				String alertHealth="[]";
				String nameOfAlert="";
				String serverName=rs.getString("ServerName");
				String resourceID=rs.getString("ResourceID");
				String serviceName=rs.getString("ServiceName");
				 resourceType=rs.getString("ResourceType");

	/**
	 * ************************************************************************************************
	 * Default Assignee that having no Ticket Assigned for specific host
	 * ************************************************************************************************
	 * */

		 String sz_query_assignee_list = "select distinct(user_id) as status from gaoperator where user_id !='Admin' and user_id!='None' and  user_id not in (select g1.assignee from gatask g2 ,gatasktypeassignee g1 where  created_date between '"+date+"' and '"+date1+"'  and g1.taskid=g2.task_id and action_assignee=1 and g2.attributes10='"+resourceType+"'  and attributes6='"+serverName+"') order by user_id";
		 String assigneeList= getDefaultAssigneeStatus(sz_query_assignee_list,uniqueconnection);

	 /** Default Status for specific host */

		 String sz_query_status_list = " select distinct(status) as status from gastatus where status not in (select status from gatask where created_date between '"+date+"' and '"+date1+"'  and attributes6!='none' and attributes10='"+resourceType+"'  and attributes10!='none' and  attributes6 is not null and  attributes12 is not null   and attributes6='"+serverName+"') order by status";
         String statusList= getDefaultAssigneeStatus(sz_query_status_list,uniqueconnection);

         	if(rs.getString("ResourceValues")!=null)
         		alertCount="["+rs.getString("ResourceValues")+"]";
			if(rs.getString("ResourceNames")!=null)
				alertName="\'"+rs.getString("ResourceNames").replace(",","\',\'")+"\'";
			if(rs.getString("Health")!=null)
				alertHealth="[\""+rs.getString("Health")+"\"]";
			if(selection.equals("AlertsByAssignee")){
			if(alertName.equals("[]"))
				nameOfAlert="["+assigneeList.substring(1)+"]";
			else
				nameOfAlert="["+alertName+assigneeList+"]";

			}
			else if(selection.equals("AlertsByStatus")){
			if(alertName.equals("[]"))
				nameOfAlert="["+statusList.substring(1)+"]";
			else
				nameOfAlert="["+alertName+statusList+"]";

			}
			String summaryname1="";
			String summarynameconcat="";

	/**
	 * **************************************************************************************************
	 * Default Task Summary accepted from properties: File name is TaskSummay.Properties
	 * For specific resourceType and Checking Task-Summary is present or not.
	 * if present then changing from the properties(MetricMapping.prperties) file
	 * **************************************************************************************************
	 */
			if(selection.equals("CriticalAlerts")){

		String[] splitsummaryname=alertName.split(",");
		if(splitsummaryname.length>0 && !splitsummaryname[0].equalsIgnoreCase("")){
		for(int b=0;b<splitsummaryname.length;b++){
			if(resourceType.equals("server")){
				if(!splitsummaryname[b].toLowerCase().contains("fnsp") && !splitsummaryname[b].toLowerCase().contains("tem"))
					summarynameconcat=summarynameconcat+",\"" + DBUtilHelper.getMetrics_mapping_properties().getProperty(splitsummaryname[b]).trim() + "\"";
				}
				else{
					summarynameconcat=summarynameconcat+",\"" + DBUtilHelper.getMetrics_mapping_properties().getProperty(splitsummaryname[b]).trim() + "\"";
				}
			}

		summarynameconcat=summarynameconcat.substring(1);
		summaryname1=summarynameconcat;
		}

	/**
	 * **************************************************************************************************
	 * Changing Task-Summary Name Present in Properties(TaskSummary.properties) with some useful meaning
	 * from other properties(MetricMapping.properties) files
	 * **************************************************************************************************
	 * */

		String[] szArrayCriticalNames=DBUtilHelper.getProperties().getProperty(resourceType).split(",");
		for(int c=0;c<szArrayCriticalNames.length;c++){
			  if (!summaryname1.contains(DBUtilHelper.getMetrics_mapping_properties().getProperty(szArrayCriticalNames[c]).trim())) {
				  summaryname1 =summaryname1 + ",\"" + DBUtilHelper.getMetrics_mapping_properties().getProperty(szArrayCriticalNames[c]).trim() + "\"";
              }
		}
			}
		if(selection.equals("CriticalAlerts"))
		nameOfAlert="["+summaryname1+"]";

	/** Generating Map for Chart Generation  */

			ticketInfo.put(serverName+","+resourceID,"\"ServiceName\":\""+serviceName+"\",\"ResourceType\":\""+resourceType+"\",\"ServerName\":\""+serverName+"\",\"ResourceNames\":"+nameOfAlert+",\"ResourceValues\":"+alertCount+",\"SlaValues\":[10000],\"Health\":"+alertHealth+",\"ResourceID\":\""+resourceID+"\"");
		//ticketInfo.put(host+","+resourceId,"\"ServerName\":\""+host+"\",\"ServiceName\":\""+serviceName+"\",\"ResourceID\":\""+resourceId+"\",\"ResourceType\":\""+resourceType+"\",\"summarycount\":"+summarycount+",\"summaryname\":"+criticalSummaryName+",\"hstdwnstatus\":"+hstdwnstatus+",\"ticketcount\":"+ticketcount+",\"assigneenames\":"+assigneenames+",\"statusnames\":"+statusnames+",\"statuscount\":"+statuscount);
	}
	rs=null;
	stat=null;
	if(ticketInfo.isEmpty())
	ticketInfo.put("DefaultMapValue","\"summarycount\":[],\"summaryname\":[],\"hstdwnstatus\":[],\"ticketcount\":[],\"assigneenames\":[],\"statusnames\":[],\"statuscount\":[]");
	} catch (Exception e) {
		e.printStackTrace();
		log.error("DashBoard Ticket Query /"+resourceType+"/\n"+e.getMessage());
	}
	finally{

	}
	return ticketInfo;
}
/**
	 * **************************************************************************************************
	 * This method is creating Query to get necessay information to generate chart
	 * from these tables like gatasktypeassignee and gatask.
	 *
	 * Query is generated to keep in mind like:
	 * 1.One IP in one Row, All information related to this IP should be present in that row
	 * 2.For generating Alert information in DashBoard these count is required:
	 * Task-Summary count, Status count,Assignee Count
	 *
	 * These all above count's query, Joining with the help of left outer join and we are using
	 * user_defined/InBuilt function like array_agg() for concatenating a column and gives array for generating JSON.
	 * array_agg function in postgresql below 8 version we have to create by following command:

		create aggregate array_agg ( sfunc = array_append,  basetype = anyelement,  stype = anyarray,  initcond = '{}'  );

	 * @param customer Selected customerId
	 * @param resourceType Different category as Desktop,Server,DataBase,Network,JVM
	 * @param date from date as subtract(endDate-timestampselection) in Date Format
	 * @param date1 Current date in Date Format
	 * @return ticketInfo Ticket generated Information

	 *  **************************************************************************************************
	 */
This method is creating Query to get necessay information to generate chart
from these tables like gatasktypeassignee and gatask.

Query is generated to keep in mind like:
1.One IP in one Row, All information related to this IP should be present in that row
2.For generating Alert information in DashBoard these count is required:
Task-Summary count, Status count,Assignee Count

These all above count's query, Joining with the help of left outer join and we are using
user_defined/InBuilt function like array_agg() for concatenating a column and gives array for generating JSON.
array_agg function in postgresql below 8 version we have to create by following command.

create aggregate array_agg ( sfunc = array_append,  basetype = anyelement,  stype = anyarray,  initcond = '{}'  ).


This method is creating Query to get necessay information to generate chart from these tables like gatasktypeassignee and gatask . Query is generated to keep in mind like : 1 . One IP in one Row All information related to this IP should be present in that row 2 . For generating Alert information in DashBoard these count is required : Task - Summary count Status count Assignee Count These all above count ' s query Joining with the help of left outer join and we are using user_defined / InBuilt function like array_agg () for concatenating a column and gives array for generating JSON . array_agg function in postgresql below 8 version we have to create by following command . create aggregate array_agg ( sfunc = array_append basetype = anyelement stype = anyarray initcond = ' {} ' ) .
public LinkedHashMap<String,String> collectTicketdata(String customer,String resourceType,String date, String date1,String selection) throws IOException, SQLException {
		 ticketInfo=new LinkedHashMap<String, String>();
		try {
			uniqueconnection = ConnectionDAO.getConnection(customer+"_177");
			stat = uniqueconnection.createStatement();
			String szQuery="";
			if(selection.equals("CriticalAlerts"))
			szQuery="select servicename as ServiceName,resourcetype as ResourceType,host as ServerName,array_to_string(array_agg(summaryname),',') as ResourceNames,array_to_string(array_agg(summarycount),',') as ResourceValues,case when sum(criticalcheck)>0 then 'CRITICAL' else 'OK' end as Health,resourceid as ResourceID from (select count(*) as summarycount,attributes6 as host,attributes10 as resourcetype,attributes4 as ServiceName,attributes12 as resourceID,substring(task_summary from position('[' in task_summary) for 25) as summaryname,CASE WHEN substring(task_summary from position('[' in task_summary) for 25) like '%HSTDWN%' THEN 1 ELSE 0 END as criticalcheck from gatask where created_date between  '"+date+"' and '"+date1+"' and status!='Closed' and task_type!='SOX' and attributes10='"+resourceType+"' group by attributes6,summaryname,attributes4,attributes12,attributes10) as cAlert group by servicename, resourcetype, host,resourceid";
			else if(selection.equals("AlertsByAssignee"))
			szQuery="select servicename as ServiceName,resourcetype as ResourceType,host as ServerName,array_to_string(array_agg(assigneenames),',') as ResourceNames,array_to_string(array_agg(ticketcount),',') as ResourceValues,case when host is not null then 'OK' end as Health,resourceid as ResourceID from (select count(*)as ticketcount,g2.attributes6 as host ,g2.attributes10 as resourcetype ,g2.attributes4 as ServiceName,g1.assignee as assigneenames,g2.attributes12 as ResourceID from gatask g2 ,gatasktypeassignee g1 where created_date between  '"+date+"' and '"+date1+"' and g1.taskid=g2.task_id and action_assignee=1 and g2.attributes10='"+resourceType+"'  group by g2.attributes6,g1.assignee,attributes4,g2.attributes10,g2.attributes12) as assig group by servicename, resourcetype, host,resourceid";
			else
			szQuery="select servicename as ServiceName,resourcetype as ResourceType,host as ServerName,array_to_string(array_agg(statusnames),',') as ResourceNames,array_to_string(array_agg(statuscount),',') as ResourceValues,case when host is not null then 'OK' end as Health,resourceid as ResourceID from (select count(*) as statuscount,attributes6 as host,attributes10 as resourcetype,attributes4 as ServiceName,status as statusnames,attributes12 as resourceID from gatask where created_date between  '"+date+"' and '"+date1+"' and attributes6!='none' and attributes10='"+resourceType+"' and attributes10!='none' and attributes6 is not null and attributes12 is not null group by attributes6,attributes4,status,attributes10,attributes12) as sAlert group by servicename, resourcetype, host,resourceid";
	/**
	 * ***************************************************************************************************
	 * This Query will give Modified result from all necessary tables like gatasktypeassignee, gatask table ..
	 * here concatenating the result came from  GAtask for Summary Count and checking hostdown status
	 * array_agg function in postgresql  below 8 version we have to create by following command:
		create aggregate array_agg ( sfunc = array_append,  basetype = anyelement,  stype = anyarray,  initcond = '{}'  );
	 * ***************************************************************************************************
	 */
			log.debug("Ticket Query /"+resourceType+"/\n"+szQuery);
			ResultSet rs = stat.executeQuery(szQuery);
			while (rs.next()) {
				String alertCount="[]";
				String alertName="";
				String alertHealth="[]";
				String nameOfAlert="";
				String serverName=rs.getString("ServerName");
				String resourceID=rs.getString("ResourceID");
				String serviceName=rs.getString("ServiceName");
				 resourceType=rs.getString("ResourceType");
	/**
	 * ************************************************************************************************
	 * Default Assignee that having no Ticket Assigned for specific host
	 * ************************************************************************************************
	 * */
		 String sz_query_assignee_list = "select distinct(user_id) as status from gaoperator where user_id !='Admin' and user_id!='None' and  user_id not in (select g1.assignee from gatask g2 ,gatasktypeassignee g1 where  created_date between '"+date+"' and '"+date1+"'  and g1.taskid=g2.task_id and action_assignee=1 and g2.attributes10='"+resourceType+"'  and attributes6='"+serverName+"') order by user_id";
		 String assigneeList= getDefaultAssigneeStatus(sz_query_assignee_list,uniqueconnection);
	 /** Default Status for specific host */
		 String sz_query_status_list = " select distinct(status) as status from gastatus where status not in (select status from gatask where created_date between '"+date+"' and '"+date1+"'  and attributes6!='none' and attributes10='"+resourceType+"'  and attributes10!='none' and  attributes6 is not null and  attributes12 is not null   and attributes6='"+serverName+"') order by status";
         String statusList= getDefaultAssigneeStatus(sz_query_status_list,uniqueconnection);
         	if(rs.getString("ResourceValues")!=null)
         		alertCount="["+rs.getString("ResourceValues")+"]";
			if(rs.getString("ResourceNames")!=null)
				alertName="\'"+rs.getString("ResourceNames").replace(",","\',\'")+"\'";
			if(rs.getString("Health")!=null)
				alertHealth="[\""+rs.getString("Health")+"\"]";
			if(selection.equals("AlertsByAssignee")){
			if(alertName.equals("[]"))
				nameOfAlert="["+assigneeList.substring(1)+"]";
			else
				nameOfAlert="["+alertName+assigneeList+"]";
			}
			else if(selection.equals("AlertsByStatus")){
			if(alertName.equals("[]"))
				nameOfAlert="["+statusList.substring(1)+"]";
			else
				nameOfAlert="["+alertName+statusList+"]";
			}
			String summaryname1="";
			String summarynameconcat="";
	/**
	 * **************************************************************************************************
	 * Default Task Summary accepted from properties: File name is TaskSummay.Properties
	 * For specific resourceType and Checking Task-Summary is present or not.
	 * if present then changing from the properties(MetricMapping.prperties) file
	 * **************************************************************************************************
	 */
			if(selection.equals("CriticalAlerts")){
		String[] splitsummaryname=alertName.split(",");
		if(splitsummaryname.length>0 && !splitsummaryname[0].equalsIgnoreCase("")){
		for(int b=0;b<splitsummaryname.length;b++){
			if(resourceType.equals("server")){
				if(!splitsummaryname[b].toLowerCase().contains("fnsp") && !splitsummaryname[b].toLowerCase().contains("tem"))
					summarynameconcat=summarynameconcat+",\"" + DBUtilHelper.getMetrics_mapping_properties().getProperty(splitsummaryname[b]).trim() + "\"";
				}
				else{
					summarynameconcat=summarynameconcat+",\"" + DBUtilHelper.getMetrics_mapping_properties().getProperty(splitsummaryname[b]).trim() + "\"";
				}
			}
		summarynameconcat=summarynameconcat.substring(1);
		summaryname1=summarynameconcat;
		}
	/**
	 * **************************************************************************************************
	 * Changing Task-Summary Name Present in Properties(TaskSummary.properties) with some useful meaning
	 * from other properties(MetricMapping.properties) files
	 * **************************************************************************************************
	 * */
		String[] szArrayCriticalNames=DBUtilHelper.getProperties().getProperty(resourceType).split(",");
		for(int c=0;c<szArrayCriticalNames.length;c++){
			  if (!summaryname1.contains(DBUtilHelper.getMetrics_mapping_properties().getProperty(szArrayCriticalNames[c]).trim())) {
				  summaryname1 =summaryname1 + ",\"" + DBUtilHelper.getMetrics_mapping_properties().getProperty(szArrayCriticalNames[c]).trim() + "\"";
              }
		}
			}
		if(selection.equals("CriticalAlerts"))
		nameOfAlert="["+summaryname1+"]";
	/** Generating Map for Chart Generation  */
			ticketInfo.put(serverName+","+resourceID,"\"ServiceName\":\""+serviceName+"\",\"ResourceType\":\""+resourceType+"\",\"ServerName\":\""+serverName+"\",\"ResourceNames\":"+nameOfAlert+",\"ResourceValues\":"+alertCount+",\"SlaValues\":[10000],\"Health\":"+alertHealth+",\"ResourceID\":\""+resourceID+"\"");
	}
	rs=null;
	stat=null;
	if(ticketInfo.isEmpty())
	ticketInfo.put("DefaultMapValue","\"summarycount\":[],\"summaryname\":[],\"hstdwnstatus\":[],\"ticketcount\":[],\"assigneenames\":[],\"statusnames\":[],\"statuscount\":[]");
	} catch (Exception e) {
		e.printStackTrace();
		log.error("DashBoard Ticket Query /"+resourceType+"/\n"+e.getMessage());
	}
	finally{
	}
	return ticketInfo;
}
public LinkedHashMap < String , String > collectTicketdata ( String customer , String resourceType , String date , String date1 , String selection ) throws IOException , SQLException { ticketInfo = new LinkedHashMap < String , String > ( ) ; try { uniqueconnection = ConnectionDAO . getConnection ( customer + "_177" ) ; stat = uniqueconnection . createStatement ( ) ; String szQuery = "" ; if ( selection . equals ( "CriticalAlerts" ) ) szQuery = "select servicename as ServiceName,resourcetype as ResourceType,host as ServerName,array_to_string(array_agg(summaryname),',') as ResourceNames,array_to_string(array_agg(summarycount),',') as ResourceValues,case when sum(criticalcheck)>0 then 'CRITICAL' else 'OK' end as Health,resourceid as ResourceID from (select count(*) as summarycount,attributes6 as host,attributes10 as resourcetype,attributes4 as ServiceName,attributes12 as resourceID,substring(task_summary from position('[' in task_summary) for 25) as summaryname,CASE WHEN substring(task_summary from position('[' in task_summary) for 25) like '%HSTDWN%' THEN 1 ELSE 0 END as criticalcheck from gatask where created_date between  '" + date + "' and '" + date1 + "' and status!='Closed' and task_type!='SOX' and attributes10='" + resourceType + "' group by attributes6,summaryname,attributes4,attributes12,attributes10) as cAlert group by servicename, resourcetype, host,resourceid" ; else if ( selection . equals ( "AlertsByAssignee" ) ) szQuery = "select servicename as ServiceName,resourcetype as ResourceType,host as ServerName,array_to_string(array_agg(assigneenames),',') as ResourceNames,array_to_string(array_agg(ticketcount),',') as ResourceValues,case when host is not null then 'OK' end as Health,resourceid as ResourceID from (select count(*)as ticketcount,g2.attributes6 as host ,g2.attributes10 as resourcetype ,g2.attributes4 as ServiceName,g1.assignee as assigneenames,g2.attributes12 as ResourceID from gatask g2 ,gatasktypeassignee g1 where created_date between  '" + date + "' and '" + date1 + "' and g1.taskid=g2.task_id and action_assignee=1 and g2.attributes10='" + resourceType + "'  group by g2.attributes6,g1.assignee,attributes4,g2.attributes10,g2.attributes12) as assig group by servicename, resourcetype, host,resourceid" ; else szQuery = "select servicename as ServiceName,resourcetype as ResourceType,host as ServerName,array_to_string(array_agg(statusnames),',') as ResourceNames,array_to_string(array_agg(statuscount),',') as ResourceValues,case when host is not null then 'OK' end as Health,resourceid as ResourceID from (select count(*) as statuscount,attributes6 as host,attributes10 as resourcetype,attributes4 as ServiceName,status as statusnames,attributes12 as resourceID from gatask where created_date between  '" + date + "' and '" + date1 + "' and attributes6!='none' and attributes10='" + resourceType + "' and attributes10!='none' and attributes6 is not null and attributes12 is not null group by attributes6,attributes4,status,attributes10,attributes12) as sAlert group by servicename, resourcetype, host,resourceid" ; /**
	 * ***************************************************************************************************
	 * This Query will give Modified result from all necessary tables like gatasktypeassignee, gatask table ..
	 * here concatenating the result came from  GAtask for Summary Count and checking hostdown status
	 * array_agg function in postgresql  below 8 version we have to create by following command:
		create aggregate array_agg ( sfunc = array_append,  basetype = anyelement,  stype = anyarray,  initcond = '{}'  );
	 * ***************************************************************************************************
	 */ log . debug ( "Ticket Query /" + resourceType + "/\n" + szQuery ) ; ResultSet rs = stat . executeQuery ( szQuery ) ; while ( rs . next ( ) ) { String alertCount = "[]" ; String alertName = "" ; String alertHealth = "[]" ; String nameOfAlert = "" ; String serverName = rs . getString ( "ServerName" ) ; String resourceID = rs . getString ( "ResourceID" ) ; String serviceName = rs . getString ( "ServiceName" ) ; resourceType = rs . getString ( "ResourceType" ) ; /**
	 * ************************************************************************************************
	 * Default Assignee that having no Ticket Assigned for specific host
	 * ************************************************************************************************
	 * */ String sz_query_assignee_list = "select distinct(user_id) as status from gaoperator where user_id !='Admin' and user_id!='None' and  user_id not in (select g1.assignee from gatask g2 ,gatasktypeassignee g1 where  created_date between '" + date + "' and '" + date1 + "'  and g1.taskid=g2.task_id and action_assignee=1 and g2.attributes10='" + resourceType + "'  and attributes6='" + serverName + "') order by user_id" ; String assigneeList = getDefaultAssigneeStatus ( sz_query_assignee_list , uniqueconnection ) ; /** Default Status for specific host */ String sz_query_status_list = " select distinct(status) as status from gastatus where status not in (select status from gatask where created_date between '" + date + "' and '" + date1 + "'  and attributes6!='none' and attributes10='" + resourceType + "'  and attributes10!='none' and  attributes6 is not null and  attributes12 is not null   and attributes6='" + serverName + "') order by status" ; String statusList = getDefaultAssigneeStatus ( sz_query_status_list , uniqueconnection ) ; if ( rs . getString ( "ResourceValues" ) != null ) alertCount = "[" + rs . getString ( "ResourceValues" ) + "]" ; if ( rs . getString ( "ResourceNames" ) != null ) alertName = "\'" + rs . getString ( "ResourceNames" ) . replace ( "," , "\',\'" ) + "\'" ; if ( rs . getString ( "Health" ) != null ) alertHealth = "[\"" + rs . getString ( "Health" ) + "\"]" ; if ( selection . equals ( "AlertsByAssignee" ) ) { if ( alertName . equals ( "[]" ) ) nameOfAlert = "[" + assigneeList . substring ( 1 ) + "]" ; else nameOfAlert = "[" + alertName + assigneeList + "]" ; } else if ( selection . equals ( "AlertsByStatus" ) ) { if ( alertName . equals ( "[]" ) ) nameOfAlert = "[" + statusList . substring ( 1 ) + "]" ; else nameOfAlert = "[" + alertName + statusList + "]" ; } String summaryname1 = "" ; String summarynameconcat = "" ; /**
	 * **************************************************************************************************
	 * Default Task Summary accepted from properties: File name is TaskSummay.Properties
	 * For specific resourceType and Checking Task-Summary is present or not.
	 * if present then changing from the properties(MetricMapping.prperties) file
	 * **************************************************************************************************
	 */ if ( selection . equals ( "CriticalAlerts" ) ) { String [ ] splitsummaryname = alertName . split ( "," ) ; if ( splitsummaryname . length > 0 && ! splitsummaryname [ 0 ] . equalsIgnoreCase ( "" ) ) { for ( int b = 0 ; b < splitsummaryname . length ; b ++ ) { if ( resourceType . equals ( "server" ) ) { if ( ! splitsummaryname [ b ] . toLowerCase ( ) . contains ( "fnsp" ) && ! splitsummaryname [ b ] . toLowerCase ( ) . contains ( "tem" ) ) summarynameconcat = summarynameconcat + ",\"" + DBUtilHelper . getMetrics_mapping_properties ( ) . getProperty ( splitsummaryname [ b ] ) . trim ( ) + "\"" ; } else { summarynameconcat = summarynameconcat + ",\"" + DBUtilHelper . getMetrics_mapping_properties ( ) . getProperty ( splitsummaryname [ b ] ) . trim ( ) + "\"" ; } } summarynameconcat = summarynameconcat . substring ( 1 ) ; summaryname1 = summarynameconcat ; } /**
	 * **************************************************************************************************
	 * Changing Task-Summary Name Present in Properties(TaskSummary.properties) with some useful meaning
	 * from other properties(MetricMapping.properties) files
	 * **************************************************************************************************
	 * */ String [ ] szArrayCriticalNames = DBUtilHelper . getProperties ( ) . getProperty ( resourceType ) . split ( "," ) ; for ( int c = 0 ; c < szArrayCriticalNames . length ; c ++ ) { if ( ! summaryname1 . contains ( DBUtilHelper . getMetrics_mapping_properties ( ) . getProperty ( szArrayCriticalNames [ c ] ) . trim ( ) ) ) { summaryname1 = summaryname1 + ",\"" + DBUtilHelper . getMetrics_mapping_properties ( ) . getProperty ( szArrayCriticalNames [ c ] ) . trim ( ) + "\"" ; } } } if ( selection . equals ( "CriticalAlerts" ) ) nameOfAlert = "[" + summaryname1 + "]" ; /** Generating Map for Chart Generation  */ ticketInfo . put ( serverName + "," + resourceID , "\"ServiceName\":\"" + serviceName + "\",\"ResourceType\":\"" + resourceType + "\",\"ServerName\":\"" + serverName + "\",\"ResourceNames\":" + nameOfAlert + ",\"ResourceValues\":" + alertCount + ",\"SlaValues\":[10000],\"Health\":" + alertHealth + ",\"ResourceID\":\"" + resourceID + "\"" ) ; } rs = null ; stat = null ; if ( ticketInfo . isEmpty ( ) ) ticketInfo . put ( "DefaultMapValue" , "\"summarycount\":[],\"summaryname\":[],\"hstdwnstatus\":[],\"ticketcount\":[],\"assigneenames\":[],\"statusnames\":[],\"statuscount\":[]" ) ; } catch ( Exception e ) { e . printStackTrace ( ) ; log . error ( "DashBoard Ticket Query /" + resourceType + "/\n" + e . getMessage ( ) ) ; } finally { } return ticketInfo ; }
This method is creating Query to get necessay information to generate chart
 from these tables like gatasktypeassignee and gatask.
This method is creating Query to get necessay information to generate chart from these tables like gatasktypeassignee and gatask .
//ticketInfo.put(host+","+resourceId,"\"ServerName\":\""+host+"\",\"ServiceName\":\""+serviceName+"\",\"ResourceID\":\""+resourceId+"\",\"ResourceType\":\""+resourceType+"\",\"summarycount\":"+summarycount+",\"summaryname\":"+criticalSummaryName+",\"hstdwnstatus\":"+hstdwnstatus+",\"ticketcount\":"+ticketcount+",\"assigneenames\":"+assigneenames+",\"statusnames\":"+statusnames+",\"statuscount\":"+statuscount);