IoT CAS Frameworks/IOT_Server/Report_Generator_Framework/source/DashBoard-JSONgenerator-ArbitraryDate/src/com/merit/dashboard/queryexecuter/QueryExecuterDirectDB.java
public String generateDefaultResourceTypeJson(String resourceType, String cCustomer, String service,
            String startDate, String endDate, String timestampselection,
            String customer) throws IOException, SQLException {
        String szResourceTypeJson = "";
        try {
            ticketInfo = new LinkedHashMap<String, String>();
            serviceMetricInfo = new LinkedHashMap<String, String>();
            SimpleDateFormat formatter1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Date dateStart = formatter1.parse(startDate);
            Date dateEnd = formatter1.parse(endDate);
            String date = formatter1.format(dateStart);
            String date1 = formatter1.format(dateEnd);
            long smilli = dateStart.getTime() / 1000;
            long emilli = dateEnd.getTime() / 1000;
            //            Connection dataBaseConnectionMetric = ConnectionDAO.getConnection(customer);
            //            Connection dataBaseConnectionTicket = ConnectionDAO.getConnection(customer + "");
            ticketInfo.clear();
            serviceMetricInfo.clear();
            String szQueryMetric = "select dService.service as ServiceName, resourcetype as ResourceType ,case when sum(health)>0 "
                    + "then 'CRITICAL' when sum(health)=0 then 'OK' else 'Default' end as  Health from "
                    + "(select distinct service from servicemetrics where resourcetype in (" + "\'"
                    + DBUtilHelper.getMetrics_mapping_properties().getProperty("ResourceName").replaceAll(",", "\',\'") + "\'"
                    + ") and service='" + service + "' and service!=resourcetype) as dService left outer join (select service,resourcetype,CASE WHEN (metrictype in ("
                    + DBUtilHelper.getMetrics_mapping_properties().getProperty("MetricTypeBelowThresholdIsDanger") + ") and "
                    + "avg(metricvalue) < avg(sla)) or (metrictype not in (" + DBUtilHelper.getMetrics_mapping_properties()
                    .getProperty("MetricTypeBelowThresholdIsDanger") + ") and avg(metricvalue) > avg(sla)) THEN 1 WHEN "
                    + "(metrictype not in (" + DBUtilHelper.getMetrics_mapping_properties().
                    getProperty("MetricTypeBelowThresholdIsDanger") + ") and avg(metricvalue) < avg(sla)) or (metrictype in ("
                    + DBUtilHelper.getMetrics_mapping_properties().getProperty("MetricTypeBelowThresholdIsDanger") + ") and "
                    + "avg(metricvalue) > avg(sla)) THEN 0 END as health from servicemetrics where service='" + service
                    + "' and service!=resourcetype and timestamp1  between " + smilli + " and " + emilli + "  and metricvalue is not null and metrictype not in ("
                    + DBUtilHelper.getMetrics_mapping_properties().getProperty("MetrictypeNotUsedInChart")
                    + ") and customerid=(select id from customerinfo where customername='" + cCustomer + "') and "
                    + "service='" + service + "' and service!=resourcetype "
                    + "group by service,resourcetype,metrictype) as hService using(service) group by ServiceName,ResourceType";
            String[] headingNamesMetric1 = {"ServiceName", "ResourceType", "Health"};
            log.debug("Default ResourceType Metric Query /" + timestampselection + "/ \n" + szQueryMetric);
            serviceMetricInfo = generateJsonFromGivenQuery(szQueryMetric, headingNamesMetric1, customer);
            serviceMetricInfo = modifyHashMap(serviceMetricInfo);

            String resTypes = DBUtilHelper.getMetrics_mapping_properties().getProperty("ResourceName");
            resTypes = "'" + resTypes.replaceAll(",", "','") + "'";
            //String szAvailJson = GetAvailability.getAvailabilityByPeriod(emilli, smilli, 
            //"'Desktop','server','DataBase','Network','JVM'", customer, timestampselection);
            log.info("resTypes==>>" + resTypes);
            //String szAvailJson = GetAvailability.getAvailabilityByPeriod(emilli, smilli, resTypes, cCustomer, service, customer, timestampselection);
            String szAvailJson = GetAvailability.getAvailabilityByPeriod(emilli, smilli, resTypes, customer, timestampselection, cCustomer,
                    service);
            org.json.JSONArray arrJobj = null;
            org.json.JSONObject temp = null;
            String tempKey = "";
            try {
                arrJobj = new org.json.JSONArray(szAvailJson);
                String availValue;//
                for (int p = 0; p < arrJobj.length(); p++) {
                    temp = arrJobj.getJSONObject(p);
                    tempKey = temp.getString("ServiceName") + "," + temp.getString("ResourceType");
                    if (serviceMetricInfo.get(tempKey) == null || temp.getString("Health").equals("CRITICAL")) {
                        serviceMetricInfo.remove(temp.getString("ServiceName") + ",null");
                        availValue = "\"ServiceName\":\"" + temp.getString("ServiceName") + "\",\"ResourceType\":\"" + temp.getString("ResourceType") + "\",\"Health\":\"" + temp.getString("Health") + "\"";
                        serviceMetricInfo.put(tempKey, availValue);
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }

            String szQueryTicket = "select service as ServiceName,ResourceType,case when sum(summary)>0 then 'CRITICAL' when "
                    + "sum(summary)=0 then 'OK' else 'Default' end as Alert from  (select attributes4 as service,attributes10 "
                    + "as ResourceType,CASE WHEN substring(task_summary from position('[' in task_summary) for 25) like '%HSTDWN%' "
                    + "THEN 1 ELSE 0 END as summary from gatask where created_date between '" + date + "' and '" + date1
                    + "' and status!='Closed' and task_type!='SOX' and attributes4!='null' and attributes4='" + service + "' group by summary,attributes4,attributes10) "
                    + "as dTicket group by ServiceName,ResourceType";

            String[] headingNamesTicket1 = {"ServiceName", "ResourceType", "Alert"};
            log.debug("Default ResourceType Ticket Query /" + timestampselection + "/ \n" + szQueryTicket);

            ticketInfo = generateJsonFromGivenQuery(szQueryTicket, headingNamesTicket1, customer);

            szResourceTypeJson = bizlogic.generateDefaultServiceResourcetypeJson(serviceMetricInfo, ticketInfo);
            //            dataBaseConnectionMetric = null;
            //            dataBaseConnectionTicket = null;
            ticketInfo = null;
            serviceMetricInfo = null;
            formatter1 = null;
        } catch (Exception e) {
            e.printStackTrace();
            log.error("DashBoard Generate DefaultResourceType \n" + e.getMessage());
        }
        log.info("Returning defaultResourceType.json==>>" + szResourceTypeJson);
        return szResourceTypeJson;
    }
/**
     * ***************************************************************************************************
     * This method is giving default Service ,ResourceType with Health and Alert
     * status for Specified period and Generating JSON with this method
     * generateDefaultServiceResourcetypeJson()
     *
     * @param resourceType Different category as
     * Desktop,Server,DataBase,Network,JVM
     * @param startDate sub(endDate-timestampselection) in Date Format
     * @param endDate Current date in Date Format
     * @param timestampselection Selected Time Period
     * @param customer Selected customerID
     * @return szResourceTypeJson Json having service,Resourcetype,health and
     * Alert
     *
     * ***************************************************************************************************
     */
This method is giving default Service ,ResourceType with Health and Alert
status for Specified period and Generating JSON with this method
generateDefaultServiceResourcetypeJson()


This method is giving default Service ResourceType with Health and Alert status for Specified period and Generating JSON with this method generateDefaultServiceResourcetypeJson ()
public String generateDefaultResourceTypeJson(String resourceType, String cCustomer, String service,
            String startDate, String endDate, String timestampselection,
            String customer) throws IOException, SQLException {
        String szResourceTypeJson = "";
        try {
            ticketInfo = new LinkedHashMap<String, String>();
            serviceMetricInfo = new LinkedHashMap<String, String>();
            SimpleDateFormat formatter1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Date dateStart = formatter1.parse(startDate);
            Date dateEnd = formatter1.parse(endDate);
            String date = formatter1.format(dateStart);
            String date1 = formatter1.format(dateEnd);
            long smilli = dateStart.getTime() / 1000;
            long emilli = dateEnd.getTime() / 1000;
            ticketInfo.clear();
            serviceMetricInfo.clear();
            String szQueryMetric = "select dService.service as ServiceName, resourcetype as ResourceType ,case when sum(health)>0 "
                    + "then 'CRITICAL' when sum(health)=0 then 'OK' else 'Default' end as  Health from "
                    + "(select distinct service from servicemetrics where resourcetype in (" + "\'"
                    + DBUtilHelper.getMetrics_mapping_properties().getProperty("ResourceName").replaceAll(",", "\',\'") + "\'"
                    + ") and service='" + service + "' and service!=resourcetype) as dService left outer join (select service,resourcetype,CASE WHEN (metrictype in ("
                    + DBUtilHelper.getMetrics_mapping_properties().getProperty("MetricTypeBelowThresholdIsDanger") + ") and "
                    + "avg(metricvalue) < avg(sla)) or (metrictype not in (" + DBUtilHelper.getMetrics_mapping_properties()
                    .getProperty("MetricTypeBelowThresholdIsDanger") + ") and avg(metricvalue) > avg(sla)) THEN 1 WHEN "
                    + "(metrictype not in (" + DBUtilHelper.getMetrics_mapping_properties().
                    getProperty("MetricTypeBelowThresholdIsDanger") + ") and avg(metricvalue) < avg(sla)) or (metrictype in ("
                    + DBUtilHelper.getMetrics_mapping_properties().getProperty("MetricTypeBelowThresholdIsDanger") + ") and "
                    + "avg(metricvalue) > avg(sla)) THEN 0 END as health from servicemetrics where service='" + service
                    + "' and service!=resourcetype and timestamp1  between " + smilli + " and " + emilli + "  and metricvalue is not null and metrictype not in ("
                    + DBUtilHelper.getMetrics_mapping_properties().getProperty("MetrictypeNotUsedInChart")
                    + ") and customerid=(select id from customerinfo where customername='" + cCustomer + "') and "
                    + "service='" + service + "' and service!=resourcetype "
                    + "group by service,resourcetype,metrictype) as hService using(service) group by ServiceName,ResourceType";
            String[] headingNamesMetric1 = {"ServiceName", "ResourceType", "Health"};
            log.debug("Default ResourceType Metric Query /" + timestampselection + "/ \n" + szQueryMetric);
            serviceMetricInfo = generateJsonFromGivenQuery(szQueryMetric, headingNamesMetric1, customer);
            serviceMetricInfo = modifyHashMap(serviceMetricInfo);
            String resTypes = DBUtilHelper.getMetrics_mapping_properties().getProperty("ResourceName");
            resTypes = "'" + resTypes.replaceAll(",", "','") + "'";
            log.info("resTypes==>>" + resTypes);
            resTypes, cCustomer, service, customer, timestampselection);
            String szAvailJson = GetAvailability.getAvailabilityByPeriod(emilli, smilli, resTypes, customer, timestampselection, cCustomer,
                    service);
            org.json.JSONArray arrJobj = null;
            org.json.JSONObject temp = null;
            String tempKey = "";
            try {
                arrJobj = new org.json.JSONArray(szAvailJson);
                String availValue;
                for (int p = 0; p < arrJobj.length(); p++) {
                    temp = arrJobj.getJSONObject(p);
                    tempKey = temp.getString("ServiceName") + "," + temp.getString("ResourceType");
                    if (serviceMetricInfo.get(tempKey) == null || temp.getString("Health").equals("CRITICAL")) {
                        serviceMetricInfo.remove(temp.getString("ServiceName") + ",null");
                        availValue = "\"ServiceName\":\"" + temp.getString("ServiceName") + "\",\"ResourceType\":\"" + temp.getString("ResourceType") + "\",\"Health\":\"" + temp.getString("Health") + "\"";
                        serviceMetricInfo.put(tempKey, availValue);
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            String szQueryTicket = "select service as ServiceName,ResourceType,case when sum(summary)>0 then 'CRITICAL' when "
                    + "sum(summary)=0 then 'OK' else 'Default' end as Alert from  (select attributes4 as service,attributes10 "
                    + "as ResourceType,CASE WHEN substring(task_summary from position('[' in task_summary) for 25) like '%HSTDWN%' "
                    + "THEN 1 ELSE 0 END as summary from gatask where created_date between '" + date + "' and '" + date1
                    + "' and status!='Closed' and task_type!='SOX' and attributes4!='null' and attributes4='" + service + "' group by summary,attributes4,attributes10) "
                    + "as dTicket group by ServiceName,ResourceType";
            String[] headingNamesTicket1 = {"ServiceName", "ResourceType", "Alert"};
            log.debug("Default ResourceType Ticket Query /" + timestampselection + "/ \n" + szQueryTicket);
            ticketInfo = generateJsonFromGivenQuery(szQueryTicket, headingNamesTicket1, customer);
            szResourceTypeJson = bizlogic.generateDefaultServiceResourcetypeJson(serviceMetricInfo, ticketInfo);
                        dataBaseConnectionMetric = null;
                        dataBaseConnectionTicket = null;
            ticketInfo = null;
            serviceMetricInfo = null;
            formatter1 = null;
        } catch (Exception e) {
            e.printStackTrace();
            log.error("DashBoard Generate DefaultResourceType \n" + e.getMessage());
        }
        log.info("Returning defaultResourceType.json==>>" + szResourceTypeJson);
        return szResourceTypeJson;
    }
public String generateDefaultResourceTypeJson ( String resourceType , String cCustomer , String service , String startDate , String endDate , String timestampselection , String customer ) throws IOException , SQLException { String szResourceTypeJson = "" ; try { ticketInfo = new LinkedHashMap < String , String > ( ) ; serviceMetricInfo = new LinkedHashMap < String , String > ( ) ; SimpleDateFormat formatter1 = new SimpleDateFormat ( "yyyy-MM-dd HH:mm:ss" ) ; Date dateStart = formatter1 . parse ( startDate ) ; Date dateEnd = formatter1 . parse ( endDate ) ; String date = formatter1 . format ( dateStart ) ; String date1 = formatter1 . format ( dateEnd ) ; long smilli = dateStart . getTime ( ) / 1000 ; long emilli = dateEnd . getTime ( ) / 1000 ; ticketInfo . clear ( ) ; serviceMetricInfo . clear ( ) ; String szQueryMetric = "select dService.service as ServiceName, resourcetype as ResourceType ,case when sum(health)>0 " + "then 'CRITICAL' when sum(health)=0 then 'OK' else 'Default' end as  Health from " + "(select distinct service from servicemetrics where resourcetype in (" + "\'" + DBUtilHelper . getMetrics_mapping_properties ( ) . getProperty ( "ResourceName" ) . replaceAll ( "," , "\',\'" ) + "\'" + ") and service='" + service + "' and service!=resourcetype) as dService left outer join (select service,resourcetype,CASE WHEN (metrictype in (" + DBUtilHelper . getMetrics_mapping_properties ( ) . getProperty ( "MetricTypeBelowThresholdIsDanger" ) + ") and " + "avg(metricvalue) < avg(sla)) or (metrictype not in (" + DBUtilHelper . getMetrics_mapping_properties ( ) . getProperty ( "MetricTypeBelowThresholdIsDanger" ) + ") and avg(metricvalue) > avg(sla)) THEN 1 WHEN " + "(metrictype not in (" + DBUtilHelper . getMetrics_mapping_properties ( ) . getProperty ( "MetricTypeBelowThresholdIsDanger" ) + ") and avg(metricvalue) < avg(sla)) or (metrictype in (" + DBUtilHelper . getMetrics_mapping_properties ( ) . getProperty ( "MetricTypeBelowThresholdIsDanger" ) + ") and " + "avg(metricvalue) > avg(sla)) THEN 0 END as health from servicemetrics where service='" + service + "' and service!=resourcetype and timestamp1  between " + smilli + " and " + emilli + "  and metricvalue is not null and metrictype not in (" + DBUtilHelper . getMetrics_mapping_properties ( ) . getProperty ( "MetrictypeNotUsedInChart" ) + ") and customerid=(select id from customerinfo where customername='" + cCustomer + "') and " + "service='" + service + "' and service!=resourcetype " + "group by service,resourcetype,metrictype) as hService using(service) group by ServiceName,ResourceType" ; String [ ] headingNamesMetric1 = { "ServiceName" , "ResourceType" , "Health" } ; log . debug ( "Default ResourceType Metric Query /" + timestampselection + "/ \n" + szQueryMetric ) ; serviceMetricInfo = generateJsonFromGivenQuery ( szQueryMetric , headingNamesMetric1 , customer ) ; serviceMetricInfo = modifyHashMap ( serviceMetricInfo ) ; String resTypes = DBUtilHelper . getMetrics_mapping_properties ( ) . getProperty ( "ResourceName" ) ; resTypes = "'" + resTypes . replaceAll ( "," , "','" ) + "'" ; log . info ( "resTypes==>>" + resTypes ) ; String szAvailJson = GetAvailability . getAvailabilityByPeriod ( emilli , smilli , resTypes , customer , timestampselection , cCustomer , service ) ; org . json . JSONArray arrJobj = null ; org . json . JSONObject temp = null ; String tempKey = "" ; try { arrJobj = new org . json . JSONArray ( szAvailJson ) ; String availValue ; for ( int p = 0 ; p < arrJobj . length ( ) ; p ++ ) { temp = arrJobj . getJSONObject ( p ) ; tempKey = temp . getString ( "ServiceName" ) + "," + temp . getString ( "ResourceType" ) ; if ( serviceMetricInfo . get ( tempKey ) == null || temp . getString ( "Health" ) . equals ( "CRITICAL" ) ) { serviceMetricInfo . remove ( temp . getString ( "ServiceName" ) + ",null" ) ; availValue = "\"ServiceName\":\"" + temp . getString ( "ServiceName" ) + "\",\"ResourceType\":\"" + temp . getString ( "ResourceType" ) + "\",\"Health\":\"" + temp . getString ( "Health" ) + "\"" ; serviceMetricInfo . put ( tempKey , availValue ) ; } } } catch ( Exception e ) { e . printStackTrace ( ) ; } String szQueryTicket = "select service as ServiceName,ResourceType,case when sum(summary)>0 then 'CRITICAL' when " + "sum(summary)=0 then 'OK' else 'Default' end as Alert from  (select attributes4 as service,attributes10 " + "as ResourceType,CASE WHEN substring(task_summary from position('[' in task_summary) for 25) like '%HSTDWN%' " + "THEN 1 ELSE 0 END as summary from gatask where created_date between '" + date + "' and '" + date1 + "' and status!='Closed' and task_type!='SOX' and attributes4!='null' and attributes4='" + service + "' group by summary,attributes4,attributes10) " + "as dTicket group by ServiceName,ResourceType" ; String [ ] headingNamesTicket1 = { "ServiceName" , "ResourceType" , "Alert" } ; log . debug ( "Default ResourceType Ticket Query /" + timestampselection + "/ \n" + szQueryTicket ) ; ticketInfo = generateJsonFromGivenQuery ( szQueryTicket , headingNamesTicket1 , customer ) ; szResourceTypeJson = bizlogic . generateDefaultServiceResourcetypeJson ( serviceMetricInfo , ticketInfo ) ; ticketInfo = null ; serviceMetricInfo = null ; formatter1 = null ; } catch ( Exception e ) { e . printStackTrace ( ) ; log . error ( "DashBoard Generate DefaultResourceType \n" + e . getMessage ( ) ) ; } log . info ( "Returning defaultResourceType.json==>>" + szResourceTypeJson ) ; return szResourceTypeJson ; }
This method is giving default Service ,ResourceType with Health and Alert
 status for Specified period and Generating JSON with this method
 generateDefaultServiceResourcetypeJson()
This method is giving default Service ResourceType with Health and Alert status for Specified period and Generating JSON with this method generateDefaultServiceResourcetypeJson ()
//            Connection dataBaseConnectionMetric = ConnectionDAO.getConnection(customer); //            Connection dataBaseConnectionTicket = ConnectionDAO.getConnection(customer + ""); //String szAvailJson = GetAvailability.getAvailabilityByPeriod(emilli, smilli,  //"'Desktop','server','DataBase','Network','JVM'", customer, timestampselection); //String szAvailJson = GetAvailability.getAvailabilityByPeriod(emilli, smilli, resTypes, cCustomer, service, customer, timestampselection); // //            dataBaseConnectionMetric = null; //            dataBaseConnectionTicket = null;