IoT CAS Frameworks/IOT_Server/Report_Generator_Framework/source/DashBoard-JSONgenerator-ArbitraryDate/src/com/merit/dashboard/queryexecuter/QueryExecuterDirectDB.java
public String generateServicesLeftGridJson(String cCustomer,
            String startDate, String endDate, String timestampselection,
            String customer) throws IOException, SQLException {
        String szServiceJson = "";
        ResultSet rs = null;
        String cquery = "";
        String addedJson = "";
        try {
            serviceMetricInfo = new LinkedHashMap<String, String>();
            ticketInfo = 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;
            String szQueryMetric = "select dService.service as ServiceName, 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 <> 'null' and customerid=(select id from customerinfo where customername='" + cCustomer
                    + "')) as dService left outer join (select service,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 <> 'null' "
                    + "and timestamp1 between " + smilli + " and " + emilli + "  and metricvalue is not null and "
                    + "customerid=(select id from customerinfo where customername='" + cCustomer + "') and "
                    + "metrictype not in (" + DBUtilHelper.getMetrics_mapping_properties().getProperty("MetrictypeNotUsedInChart")
                    + ") group by customerid,service,resourcetype,resourceid,metrictype) as hService using(service) group by ServiceName order by health";
//            Connection dataBaseConnectionMetric = ConnectionDAO.getConnection(customer);
            String[] headingNamesMetric = {"ServiceName", "Health"};
            serviceMetricInfo = generateJsonFromGivenQuery(szQueryMetric, headingNamesMetric, customer);
            serviceMetricInfo = modifyHashMap(serviceMetricInfo);
            log.debug("Default Service Metric Query /" + timestampselection + "/ \n" + szQueryMetric);

            String resTypes = DBUtilHelper.getMetrics_mapping_properties().getProperty("ResourceName");
            resTypes = "'" + resTypes.replaceAll(",", "','") + "'";
            log.info("resTypes==>>" + resTypes);
            //String szAvailJson = GetAvailability.getAvailabilityByPeriod(emilli, smilli, 
            //"'Desktop','server','DataBase','Network','JVM'", customer, timestampselection);
            //String szAvailJson = GetAvailability.getAvailabilityByPeriod(emilli, smilli, resTypes, cCustomer, null, customer, timestampselection);
            String szAvailJson = GetAvailability.getAvailabilityByPeriod(emilli, smilli, resTypes, customer, timestampselection,
                    cCustomer, null);
            org.json.JSONArray arrJobj = null;
            org.json.JSONObject temp = null;
            String tempKey = "";
            String chartType = "[\"0\",\"1\"]";
            String resourceId = "[\"Default\"]";
            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");
                    log.info("rekha  ServiceName==>>" + tempKey);
                    //if (!(tempKey.equals("null"))){
                    if (serviceMetricInfo.get(tempKey) == null || temp.getString("Health").equals("CRITICAL")) {
                        availValue = "\"ServiceName\":\"" + temp.getString("ServiceName") + "\",\"Health\":\""
                                + temp.getString("Health") + "\",\"ResourceChartType\":" + chartType + ",\"ResourceNames\":"
                                + resourceId;
                        serviceMetricInfo.put(tempKey, availValue);
                    }
                    //}
                }
            } catch (Exception e) {
                e.printStackTrace();
            }


            String szQueryTicket = "select service as ServiceName,case when sum(summary)>0 then 'CRITICAL' when sum(summary)=0 "
                    + "then 'OK' else 'Default' end as Alert from  (select attributes4 as service,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 customerid='" + cCustomer + "' group by summary,attributes4,attributes10) as dTicket "
                    + "group by ServiceName";
//            Connection dataBaseConnectionTicket = ConnectionDAO.getConnection(customer + "");
            String[] headingNamesTicket = {"ServiceName", "Alert"};
            ticketInfo = generateJsonFromGivenQuery(szQueryTicket, headingNamesTicket, customer);
            log.debug("Default Service Metric Query /" + timestampselection + "/ \n" + szQueryTicket);

            szServiceJson = bizlogic.generateDefaultServiceResourcetypeJson(serviceMetricInfo, ticketInfo);
            cquery = "select distinct service,customerid from hostinfo where customerid=(select id from customerinfo where customername='" + cCustomer
                    + "'" + ");";
            addedJson = ",{\"ResourceNames\":[\"Default\"],\"ServiceName\":\"?\",\"Alert\":\"Default\",\"ResourceChartType\":[\"0\",\"1\"],\"MetricUOM\":[\"\"],\"Health\":\"Default\"}]";
            rs = ConnectionDAO.executerQuery(cquery, customer);

            while (rs.next()) {
                log.info("service =" + rs.getString(1));
                if (szServiceJson.contains(rs.getString(1))) {
                    log.info("present =" + rs.getString(1));
                } else {
                    log.info("not present =" + rs.getString(1));
                    szServiceJson = szServiceJson.substring(0, szServiceJson.length() - 1) + addedJson.replace("?", rs.getString(1));
                    log.info(szServiceJson);
                }
            }

//            dataBaseConnectionMetric = null;
//            dataBaseConnectionTicket = null;
            ticketInfo = null;
            serviceMetricInfo = null;
            formatter1 = null;
        } catch (Exception e) {
            e.printStackTrace();
            log.error("DashBoard Generate Default Service \n" + e.getMessage());
        } finally {
            try {
                if (rs != null) {

                    rs.close();
                    rs = null;
                } else if (cquery != null) {
                    cquery = null;
                } else if (addedJson != null) {
                    addedJson = null;
                }

            } catch (Exception e) {
                e.printStackTrace();
            }

        }
        return szServiceJson;
    }
/**
     * ***************************************************************************************************
     * This method is giving default services 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,health and Alert
     *
     * ***************************************************************************************************
     */
This method is giving default services with Health and Alert status for
Specified period and Generating JSON with this method
generateDefaultServiceResourcetypeJson()


This method is giving default services with Health and Alert status for Specified period and Generating JSON with this method generateDefaultServiceResourcetypeJson ()
public String generateServicesLeftGridJson(String cCustomer,
            String startDate, String endDate, String timestampselection,
            String customer) throws IOException, SQLException {
        String szServiceJson = "";
        ResultSet rs = null;
        String cquery = "";
        String addedJson = "";
        try {
            serviceMetricInfo = new LinkedHashMap<String, String>();
            ticketInfo = 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;
            String szQueryMetric = "select dService.service as ServiceName, 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 <> 'null' and customerid=(select id from customerinfo where customername='" + cCustomer
                    + "')) as dService left outer join (select service,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 <> 'null' "
                    + "and timestamp1 between " + smilli + " and " + emilli + "  and metricvalue is not null and "
                    + "customerid=(select id from customerinfo where customername='" + cCustomer + "') and "
                    + "metrictype not in (" + DBUtilHelper.getMetrics_mapping_properties().getProperty("MetrictypeNotUsedInChart")
                    + ") group by customerid,service,resourcetype,resourceid,metrictype) as hService using(service) group by ServiceName order by health";
            String[] headingNamesMetric = {"ServiceName", "Health"};
            serviceMetricInfo = generateJsonFromGivenQuery(szQueryMetric, headingNamesMetric, customer);
            serviceMetricInfo = modifyHashMap(serviceMetricInfo);
            log.debug("Default Service Metric Query /" + timestampselection + "/ \n" + szQueryMetric);
            String resTypes = DBUtilHelper.getMetrics_mapping_properties().getProperty("ResourceName");
            resTypes = "'" + resTypes.replaceAll(",", "','") + "'";
            log.info("resTypes==>>" + resTypes);
            resTypes, cCustomer, null, customer, timestampselection);
            String szAvailJson = GetAvailability.getAvailabilityByPeriod(emilli, smilli, resTypes, customer, timestampselection,
                    cCustomer, null);
            org.json.JSONArray arrJobj = null;
            org.json.JSONObject temp = null;
            String tempKey = "";
            String chartType = "[\"0\",\"1\"]";
            String resourceId = "[\"Default\"]";
            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");
                    log.info("rekha  ServiceName==>>" + tempKey);
                    if (!(tempKey.equals("null"))){
                    if (serviceMetricInfo.get(tempKey) == null || temp.getString("Health").equals("CRITICAL")) {
                        availValue = "\"ServiceName\":\"" + temp.getString("ServiceName") + "\",\"Health\":\""
                                + temp.getString("Health") + "\",\"ResourceChartType\":" + chartType + ",\"ResourceNames\":"
                                + resourceId;
                        serviceMetricInfo.put(tempKey, availValue);
                    }
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            String szQueryTicket = "select service as ServiceName,case when sum(summary)>0 then 'CRITICAL' when sum(summary)=0 "
                    + "then 'OK' else 'Default' end as Alert from  (select attributes4 as service,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 customerid='" + cCustomer + "' group by summary,attributes4,attributes10) as dTicket "
                    + "group by ServiceName";
            Connection dataBaseConnectionTicket = ConnectionDAO.getConnection(customer + "");
            String[] headingNamesTicket = {"ServiceName", "Alert"};
            ticketInfo = generateJsonFromGivenQuery(szQueryTicket, headingNamesTicket, customer);
            log.debug("Default Service Metric Query /" + timestampselection + "/ \n" + szQueryTicket);
            szServiceJson = bizlogic.generateDefaultServiceResourcetypeJson(serviceMetricInfo, ticketInfo);
            cquery = "select distinct service,customerid from hostinfo where customerid=(select id from customerinfo where customername='" + cCustomer
                    + "'" + ");";
            addedJson = ",{\"ResourceNames\":[\"Default\"],\"ServiceName\":\"?\",\"Alert\":\"Default\",\"ResourceChartType\":[\"0\",\"1\"],\"MetricUOM\":[\"\"],\"Health\":\"Default\"}]";
            rs = ConnectionDAO.executerQuery(cquery, customer);
            while (rs.next()) {
                log.info("service =" + rs.getString(1));
                if (szServiceJson.contains(rs.getString(1))) {
                    log.info("present =" + rs.getString(1));
                } else {
                    log.info("not present =" + rs.getString(1));
                    szServiceJson = szServiceJson.substring(0, szServiceJson.length() - 1) + addedJson.replace("?", rs.getString(1));
                    log.info(szServiceJson);
                }
            }
            dataBaseConnectionMetric = null;
            dataBaseConnectionTicket = null;
            ticketInfo = null;
            serviceMetricInfo = null;
            formatter1 = null;
        } catch (Exception e) {
            e.printStackTrace();
            log.error("DashBoard Generate Default Service \n" + e.getMessage());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                    rs = null;
                } else if (cquery != null) {
                    cquery = null;
                } else if (addedJson != null) {
                    addedJson = null;
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return szServiceJson;
    }
public String generateServicesLeftGridJson ( String cCustomer , String startDate , String endDate , String timestampselection , String customer ) throws IOException , SQLException { String szServiceJson = "" ; ResultSet rs = null ; String cquery = "" ; String addedJson = "" ; try { serviceMetricInfo = new LinkedHashMap < String , String > ( ) ; ticketInfo = 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 ; String szQueryMetric = "select dService.service as ServiceName, 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 <> 'null' and customerid=(select id from customerinfo where customername='" + cCustomer + "')) as dService left outer join (select service,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 <> 'null' " + "and timestamp1 between " + smilli + " and " + emilli + "  and metricvalue is not null and " + "customerid=(select id from customerinfo where customername='" + cCustomer + "') and " + "metrictype not in (" + DBUtilHelper . getMetrics_mapping_properties ( ) . getProperty ( "MetrictypeNotUsedInChart" ) + ") group by customerid,service,resourcetype,resourceid,metrictype) as hService using(service) group by ServiceName order by health" ; String [ ] headingNamesMetric = { "ServiceName" , "Health" } ; serviceMetricInfo = generateJsonFromGivenQuery ( szQueryMetric , headingNamesMetric , customer ) ; serviceMetricInfo = modifyHashMap ( serviceMetricInfo ) ; log . debug ( "Default Service Metric Query /" + timestampselection + "/ \n" + szQueryMetric ) ; 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 , null ) ; org . json . JSONArray arrJobj = null ; org . json . JSONObject temp = null ; String tempKey = "" ; String chartType = "[\"0\",\"1\"]" ; String resourceId = "[\"Default\"]" ; 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" ) ; log . info ( "rekha  ServiceName==>>" + tempKey ) ; if ( serviceMetricInfo . get ( tempKey ) == null || temp . getString ( "Health" ) . equals ( "CRITICAL" ) ) { availValue = "\"ServiceName\":\"" + temp . getString ( "ServiceName" ) + "\",\"Health\":\"" + temp . getString ( "Health" ) + "\",\"ResourceChartType\":" + chartType + ",\"ResourceNames\":" + resourceId ; serviceMetricInfo . put ( tempKey , availValue ) ; } } } catch ( Exception e ) { e . printStackTrace ( ) ; } String szQueryTicket = "select service as ServiceName,case when sum(summary)>0 then 'CRITICAL' when sum(summary)=0 " + "then 'OK' else 'Default' end as Alert from  (select attributes4 as service,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 customerid='" + cCustomer + "' group by summary,attributes4,attributes10) as dTicket " + "group by ServiceName" ; String [ ] headingNamesTicket = { "ServiceName" , "Alert" } ; ticketInfo = generateJsonFromGivenQuery ( szQueryTicket , headingNamesTicket , customer ) ; log . debug ( "Default Service Metric Query /" + timestampselection + "/ \n" + szQueryTicket ) ; szServiceJson = bizlogic . generateDefaultServiceResourcetypeJson ( serviceMetricInfo , ticketInfo ) ; cquery = "select distinct service,customerid from hostinfo where customerid=(select id from customerinfo where customername='" + cCustomer + "'" + ");" ; addedJson = ",{\"ResourceNames\":[\"Default\"],\"ServiceName\":\"?\",\"Alert\":\"Default\",\"ResourceChartType\":[\"0\",\"1\"],\"MetricUOM\":[\"\"],\"Health\":\"Default\"}]" ; rs = ConnectionDAO . executerQuery ( cquery , customer ) ; while ( rs . next ( ) ) { log . info ( "service =" + rs . getString ( 1 ) ) ; if ( szServiceJson . contains ( rs . getString ( 1 ) ) ) { log . info ( "present =" + rs . getString ( 1 ) ) ; } else { log . info ( "not present =" + rs . getString ( 1 ) ) ; szServiceJson = szServiceJson . substring ( 0 , szServiceJson . length ( ) - 1 ) + addedJson . replace ( "?" , rs . getString ( 1 ) ) ; log . info ( szServiceJson ) ; } } ticketInfo = null ; serviceMetricInfo = null ; formatter1 = null ; } catch ( Exception e ) { e . printStackTrace ( ) ; log . error ( "DashBoard Generate Default Service \n" + e . getMessage ( ) ) ; } finally { try { if ( rs != null ) { rs . close ( ) ; rs = null ; } else if ( cquery != null ) { cquery = null ; } else if ( addedJson != null ) { addedJson = null ; } } catch ( Exception e ) { e . printStackTrace ( ) ; } } return szServiceJson ; }
This method is giving default services with Health and Alert status for
 Specified period and Generating JSON with this method
 generateDefaultServiceResourcetypeJson()
This method is giving default services with Health and Alert status for Specified period and Generating JSON with this method generateDefaultServiceResourcetypeJson ()
//            Connection dataBaseConnectionMetric = ConnectionDAO.getConnection(customer); //String szAvailJson = GetAvailability.getAvailabilityByPeriod(emilli, smilli,  //"'Desktop','server','DataBase','Network','JVM'", customer, timestampselection); //String szAvailJson = GetAvailability.getAvailabilityByPeriod(emilli, smilli, resTypes, cCustomer, null, customer, timestampselection); // //if (!(tempKey.equals("null"))){ //} //            Connection dataBaseConnectionTicket = ConnectionDAO.getConnection(customer + ""); //            dataBaseConnectionMetric = null; //            dataBaseConnectionTicket = null;