diff options
Diffstat (limited to 'ecomp-sdk/epsdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/util/SQLCorrector.java')
| -rw-r--r-- | ecomp-sdk/epsdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/util/SQLCorrector.java | 343 |
1 files changed, 0 insertions, 343 deletions
diff --git a/ecomp-sdk/epsdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/util/SQLCorrector.java b/ecomp-sdk/epsdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/util/SQLCorrector.java deleted file mode 100644 index 531ea2f8..00000000 --- a/ecomp-sdk/epsdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/util/SQLCorrector.java +++ /dev/null @@ -1,343 +0,0 @@ -/*- - * ================================================================================ - * ECOMP Portal SDK - * ================================================================================ - * Copyright (C) 2017 AT&T Intellectual Property - * ================================================================================ - * Licensed under the Apache License, Version 2.0 (the "License"); - * you may not use this file except in compliance with the License. - * You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, software - * distributed under the License is distributed on an "AS IS" BASIS, - * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - * See the License for the specific language governing permissions and - * limitations under the License. - * ================================================================================ - */ -package org.openecomp.portalsdk.analytics.util; -import java.util.ArrayList; -import java.util.HashMap; -import java.util.Iterator; -import java.util.Map; -import java.util.Set; -import java.util.regex.Matcher; -import java.util.regex.Pattern; - -import org.openecomp.portalsdk.core.logging.logic.EELFLoggerDelegate; - -public class SQLCorrector { - - static EELFLoggerDelegate logger = EELFLoggerDelegate.getLogger(SQLCorrector.class); - - - public String fixSQL(StringBuffer sql) { - - int pos = 0; - //int pos_f_select_from = 0; - int pos_f_select_to = 0; - int pos_s_select_from = 0; - int pos_s_select_to = 0; - int pos_dup_select = 0; - int pos_dup_select1 = 0; - int pos_f_format = 0; - int pos_t_format = 0; - int pos_alias_format = 0; - int pos_alias = 0; - String format = ""; - String alias = null; - String sql2 = Utils.replaceInString(sql.toString(), "\n", " "); - sql2 = Utils.replaceInString(sql2, "\t", " "); - sql = new StringBuffer(sql2); - if (sql.indexOf("FROM", 2) != -1) { - pos = sql.indexOf("FROM", 2); - pos_f_select_to = sql.indexOf("FROM", 2); - if (sql.indexOf("SELECT", pos)!=-1) { - pos = sql.indexOf("SELECT", pos); - pos_s_select_from = pos; - pos_dup_select1 = pos; - //System.out.println(pos); - if (sql.indexOf("FROM", pos)!=-1) { - pos = sql.indexOf("FROM", pos); - pos_dup_select = sql.lastIndexOf("SELECT",pos); - while(pos_dup_select > pos_dup_select1) { - pos_dup_select1 = pos_dup_select; - pos = sql.indexOf("FROM", pos + 2); - pos_dup_select = sql.lastIndexOf("SELECT",pos); - } - pos_s_select_to = pos; - - } - } - } - - String o_sql = sql.substring(0, pos_f_select_to-1); - String i_sql = sql.substring(pos_s_select_from, pos_s_select_to-1); - o_sql = o_sql.toUpperCase(); - i_sql = i_sql.toUpperCase(); - String outer_sql = o_sql.substring(o_sql.indexOf("SELECT")+7); - String inner_sql = i_sql.substring(i_sql.indexOf("SELECT")+7); - logger.debug(EELFLoggerDelegate.debugLogger, ("|"+inner_sql+"|")); - String outer_cols[] = outer_sql.split(","); - //inner_sql = inner_sql.replaceAll(", '", ",'"); - - String inner_cols[] = inner_sql.split(", "); - inner_cols = removeExtraSpace(inner_cols); - logger.debug(EELFLoggerDelegate.debugLogger, ("*******OuterCols ********")); - printArray(outer_cols); - logger.debug(EELFLoggerDelegate.debugLogger, ("\n*******InnerCols********")); - printArray(inner_cols); - logger.debug(EELFLoggerDelegate.debugLogger, ("\n********Replacing Elements*****")); - ArrayList elements = findSum(outer_cols); - logger.debug(EELFLoggerDelegate.debugLogger, ("In Fix SQL " + elements.size())); - printArrayList(elements); - HashMap elementFormatMap = matchAndGetFormatInInnerCol(elements, inner_cols); - ArrayList outerReplacedCols = replaceOuterCols(elementFormatMap, outer_cols); - String finalSql = generateSQL(outerReplacedCols, sql.toString()); - return finalSql; - } - /** - * @param args - */ - public static void main(String[] args) { - - SQLCorrector s = new SQLCorrector(); - StringBuffer strBuf = new StringBuffer(); - strBuf.append("SELECT NULL TOTAL_BILLMONTH, NULL TOTAL_ACCOUNTNUMBER, NULL TOTAL_SECTORCODE, NULL TOTAL_BUSINESS_UNIT_CODE, NULL TOTAL_BILLINGSITEID, NULL TOTAL_SITEADDRESS, NULL TOTAL_SITECITY, NULL TOTAL_SITESTATE, NULL TOTAL_VENDORNAME, NULL TOTAL_INVOICENUMBER, NULL TOTAL_INVOICEDATE, NULL TOTAL_SERVICEDESCRIPTION, SUM(INVOICEAMOUNT) TOTAL_INVOICEAMOUNT FROM (SELECT '2006/09/16 - 2006/10/15' BillMonth, account_number AccountNumber, (select distinct sector_code FROM billing_site where business_unit_code = BU) SectorCode, BU||' - '||(select distinct business_unit_name FROM billing_site where business_unit_code = BU) Business_Unit_Code, Site_ID BillingSiteID, site_address1 SiteAddress, site_city SiteCity, site_state SiteState, Vendor_Site_Name VendorName, invoice_number InvoiceNumber, to_char(invoice_date,'YYYY/MM/DD') InvoiceDate, billing_service_description ServiceDescription, to_char(payment_amount,'9,999,999.99') InvoiceAmount"); - strBuf.append(" FROM cis_gm_passthrough "); - strBuf.append(" where "); - strBuf.append(" BU = decode(upper('ALL'), 'ALL', BU, substr('ALL', 1, 3)) "); - strBuf.append(" and BU in (SELECT distinct business_unit_code FROM billing_site where "); - strBuf.append(" sector_code = decode('ALL', 'ALL', sector_code, 'ALL') "); - strBuf.append(" and CHECK_USER_SECURITY(10, 'BUSINESS_UNIT_CODE', business_unit_code)='Y') "); - strBuf.append(" and to_char(bill_cycle_start_date, 'YYYY/MM/DD') = substr('2006/09/16 - 2006/10/15', 1, 10) "); - strBuf.append(" and to_char(bill_cycle_end_date, 'YYYY/MM/DD') = substr('2006/09/16 - 2006/10/15', 14, 10)"); - strBuf.append(" order by business_unit_code, sitecity, sitestate, invoicedate, servicedescription ) totalSQL "); - String sql = strBuf.toString(); - String fix_sql = s.fixSQL(new StringBuffer(sql.toUpperCase())); - logger.debug(EELFLoggerDelegate.debugLogger, (fix_sql)); - // TODO Auto-generated method stub - - } - - private String[] removeExtraSpace (String[] inner_cols) { - String [] fixed_cols = new String[inner_cols.length]; - for (int i = 0; i < inner_cols.length; i++) { - fixed_cols[i] = inner_cols[i].replaceAll(", '", ",'"); - } - return fixed_cols; - } - - private void printArray (String[] arr) { - logger.debug(EELFLoggerDelegate.debugLogger, ("")); - for (int i = 0; i < arr.length; i++) { - System.out.print(arr[i].trim()); - if(i<arr.length-1) - logger.debug(EELFLoggerDelegate.debugLogger, (",")); - } - } - - private void printArrayList (ArrayList arrList) { - logger.debug(EELFLoggerDelegate.debugLogger, (" ArrayList ")); - for (int i = 0; i < arrList.size(); i++) { - System.out.print(arrList.get(i)); - if(i<arrList.size()-1) - logger.debug(EELFLoggerDelegate.debugLogger, (",")); - } - } - - private ArrayList findSum (String[] arr ) { - CharSequence inputString = null; - Pattern pattern = null; - Matcher matcher = null; - ArrayList elements = new ArrayList(); - boolean b = false; - for (int i = 0; i < arr.length; i++) { - //System.out.print(arr[i].trim()); - inputString = arr[i].trim(); - //debugLogger.debug(inputString); - //String keyPattern = "/SUM(|AVG|COUNT|STDDEV/"; - String keyPattern = "SUM\\(|AVG\\(|COUNT\\(|STDDEV\\(|VARIANCE\\(|SUM \\(|AVG \\(|COUNT \\(|STDDEV \\(|VARIANCE \\("; - pattern = Pattern.compile(keyPattern); - matcher = pattern.matcher(inputString); - b = matcher.find(); - //debugLogger.debug(b); - if(b) { - elements.add(arr[i].trim()); - } - } - //debugLogger.debug("In Find Sum " + elements.size()); - return elements; - - } - - - private HashMap matchAndGetFormatInInnerCol(ArrayList arrList, String[] inner_cols) { - HashMap elementFormat = new HashMap(); - String totalElement = null; - String extractedElement = null; - String format = null; - int pos = 0; - //debugLogger.debug("arrList.size() " + arrList.size()); - for (int i = 0; i < arrList.size(); i++) { - totalElement = (String) arrList.get(i); - //debugLogger.debug("\nTotalElement " + totalElement); - extractedElement = totalElement.substring(totalElement.indexOf("(")+1, totalElement.indexOf(")") ); - for (int j = 0; j < inner_cols.length; j++) { - //debugLogger.debug("Format " + inner_cols[j] + " " + extractedElement); - if(inner_cols[j].lastIndexOf(extractedElement)!=-1) { - if(inner_cols[j].indexOf("999")!=-1 && inner_cols[j].indexOf("TO_CHAR")!=-1) { - pos = inner_cols[j].indexOf("TO_CHAR"); - pos = inner_cols[j].lastIndexOf(",'"); - if(pos == -1) - pos = inner_cols[j].lastIndexOf(", '"); - //debugLogger.debug("Format before " + inner_cols[j] + "\n*** " + pos+ " " + (inner_cols[j].substring(pos+2, inner_cols[j].indexOf("'", pos+2)))); - format = inner_cols[j].substring(pos+2, inner_cols[j].indexOf("'", pos+2)); - } - } - } - if(format!=null) { - //debugLogger.debug("Match and Get Format In Inner " + totalElement + " " + format); - elementFormat.put(totalElement, format); - - } - format = null; - } - - return elementFormat; - } - - private ArrayList replaceOuterCols (HashMap hashMap, String[] outer_cols) { - Set mapSet = hashMap.entrySet(); - String element = "", value = ""; - ArrayList finalElements = new ArrayList(); - Map.Entry me; - int flag=0; - for (int i = 0; i < outer_cols.length; i++) { - flag = 0; - for (Iterator iter = mapSet.iterator(); iter.hasNext();) { - me=(Map.Entry)iter.next(); - element = (String) me.getKey(); - value = (String) me.getValue(); - //debugLogger.debug("Replace Map entry " + element + " " + value); - //debugLogger.debug("Replace " + outer_cols[i] + " " + element); - if(outer_cols[i].trim().equals(element.trim())) { - flag = 1; - //debugLogger.debug("I am here " + element + " " + value ); - finalElements.add(addFormat(element,value)); - } - } - if(flag == 0) - finalElements.add(outer_cols[i]); - } - return finalElements; - } - - private String addFormat (String element, String value) { - StringBuffer elementBuf = new StringBuffer (element); - StringBuffer finalElement = new StringBuffer(""); - String extractedElement = elementBuf.substring(elementBuf.indexOf("(")+1,elementBuf.indexOf(")")); - String alias = elementBuf.substring(elementBuf.lastIndexOf(" ")+1); - String operation = elementBuf.substring(0,elementBuf.indexOf("(")); - //debugLogger.debug("Add Format " + alias + " "+ extractedElement); - finalElement.append("TO_CHAR (") - .append(operation) - .append("(TO_NUMBER (") - .append(extractedElement) - .append(",'" + value +"'") - .append(")),'"+value + "') ") - .append(alias); -/* elementBuf.insert(0, "TO_CHAR ("); - elementBuf.insert(elementBuf.lastIndexOf(")")+1,value+"')"); - debugLogger.debug("FORMAT " + element); - debugLogger.debug("FORMAT BUF " + elementBuf.toString()); - debugLogger.debug("Format " + finalElement.toString()); -*/ - //debugLogger.debug("Final Element " + finalElement.toString()); - return finalElement.toString(); - } - - private String generateSQL( ArrayList outerSql, String sql) { - - StringBuffer finalSql = new StringBuffer("SELECT ") ; - - for (int i = 0; i < outerSql.size(); i++) { - finalSql.append(outerSql.get(i)); - if ( i < outerSql.size()-1 ) - finalSql.append(","); - } - finalSql.append(" "+sql.substring(sql.indexOf("FROM"))); - logger.debug(EELFLoggerDelegate.debugLogger, (" ---" + finalSql.toString())); - return finalSql.toString(); - } - - -/* public String fixCrosstabSQL1(StringBuffer sql) { - int pos = 0; - int pos_f_format = 0; - int pos_t_format = 0; - int pos_alias = 0; - String format = ""; - String alias = null; - if(sql.indexOf("SELECT", 7)!= -1) { - pos = sql.indexOf("SELECT", 7); - if(sql.indexOf("TO_CHAR", pos)!= -1){ - pos = sql.indexOf("TO_CHAR", pos); - debugLogger.debug("pos" + pos); - if(sql.indexOf("999",pos)!= -1) { - pos = sql.indexOf("999",pos); - pos_f_format = sql.lastIndexOf(", '", pos); - debugLogger.debug("pos_f_format" + pos_f_format); - if(pos_f_format == -1 || (pos - pos_f_format > 10)) { - pos_f_format = sql.lastIndexOf(",'", pos); - pos_f_format -= 1; - } - pos = pos_f_format; - if(sql.indexOf("')", pos)!= -1) { - pos_t_format = sql.indexOf("')", pos); - debugLogger.debug("pos_t - " + pos_t_format + " " + pos); - if(pos_t_format == -1 || (pos_t_format - pos > 20)) { - pos_t_format = sql.indexOf("' )", pos); - pos_t_format += 3; - } - else if (pos_t_format != -1) - pos_t_format += 2; - format = sql.substring(pos_f_format+3, pos_t_format); - //alias = sql.substring(pos_t_format+3, pos_t_format+6); - pos_alias = sql.indexOf(" ", pos_t_format); - alias = sql.substring(pos_alias+1, pos_alias+4); - } - } - } - - if(sql.indexOf(alias)!=-1) { - pos = sql.indexOf(alias); - debugLogger.debug(pos + " " + alias.length()+1 + "\n" + sql); - sql.delete(pos,pos+4); - sql.insert(pos, "TO_NUMBER("+alias+", '"+format+"')),'"+ format + "')"); - pos = sql.lastIndexOf("SUM", pos); - if(pos==-1) - pos = sql.lastIndexOf("AVG", pos); - else if (pos==-1) - pos = sql.lastIndexOf("COUNT", pos); - else if (pos == -1) - pos = sql.lastIndexOf("STDDEV", pos); - else if (pos == -1) - pos = sql.lastIndexOf("VARIANCE", pos); - sql.insert(pos, "TO_CHAR ("); - } - - } - - debugLogger.debug("Alias|" + alias + "| Format " + format); - debugLogger.debug(sql.toString()); - return sql.toString(); - } // FixSQL -*/ - - -} - - |