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, 343 insertions, 0 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 new file mode 100644 index 00000000..15b4042a --- /dev/null +++ b/ecomp-sdk/epsdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/util/SQLCorrector.java @@ -0,0 +1,343 @@ +/*- + * ================================================================================ + * 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 +*/ + + +} + + |