Custom Access control report from Planning Cloud 2

package com.oracle.ceal
@Grab('org.apache.derby:derby:10.9.1.0')
@Grab('commons-io:commons-io:2.6')
@GrabConfig(systemClassLoader=true)
import javax.net.ssl.HostnameVerifier
import javax.net.ssl.HttpsURLConnection
import javax.net.ssl.SSLContext
import javax.net.ssl.TrustManager
import javax.net.ssl.X509TrustManager
import java.net.HttpURLConnection
import java.util.zip.ZipFile
import static java.sql.ResultSet.*
import java.nio.file.Files
import java.nio.file.Paths
import groovy.json.*
import groovy.sql.Sql
import groovy.io.FileType
class PbcsRestClient {
    private HttpURLConnection connection
    private def planningUrl
    private def interopUrl
    private def proxyHost
    private def proxyPort
    private def user
    private def pwd
    private def appName
    private def domain
    private def ignoreSSLCertsErrors
    public PbcsRestClient(planningServerUrl, interopServerUrl,httpProxyHost, httpProxyPort, identityDomain,username, password, applicationName, ignoreSSLCertificationPathErrors) {
        planningUrl=planningServerUrl
        interopUrl=interopServerUrl
        proxyHost=httpProxyHost
        proxyPort=httpProxyPort
        domain=identityDomain
        user=username
        pwd=password
        appName=applicationName
        ignoreSSLCertsErrors=ignoreSSLCertificationPathErrors
    }
    def setProxyParams() {
        Properties systemProperties = System.getProperties()
        systemProperties.setProperty("http.proxyHost",proxyHost)
        systemProperties.setProperty("http.proxyPort",proxyPort)
        systemProperties.setProperty("https.proxyHost",proxyHost)
        systemProperties.setProperty("https.proxyPort",proxyPort)
    }
    def setSSLParams() {
        if (ignoreSSLCertsErrors !=null && ignoreSSLCertsErrors.toUpperCase()=="TRUE") {
            println "Ignoring SSL certification path errors"
            // Disable SSL cert validation
            def hostnameVerifier = [
                verify: { hostname, session -> true }
            ]
            def trustManager = [
                    checkServerTrusted: { chain, authType -> },
                    checkClientTrusted: { chain, authType -> },
                    getAcceptedIssuers: { null }
            ]
            HttpsURLConnection.setDefaultHostnameVerifier(hostnameVerifier as HostnameVerifier)
            SSLContext context = SSLContext.getInstance("SSL")
            context.init(null, [trustManager as X509TrustManager] as TrustManager[], null)
            HttpsURLConnection.setDefaultSSLSocketFactory(context.getSocketFactory())

        }
    }
    def openConnection(restUrl,method,localFileNameWithPathForStorage) {
        println "Opening connection to $restUrl with method:$method"
        int statusCode
        setProxyParams()
        setSSLParams()
        URL newUrl
        newUrl=new URL(restUrl)
        connection = (HttpURLConnection) newUrl.openConnection()
        connection.setDoOutput(true)
        connection.setDoInput(true)
        connection.setUseCaches(false)
        if (method=="")
            connection.setRequestMethod("GET")
        else
            connection.setRequestMethod(method)
        connection.setRequestProperty("Content-Type","application/x-www-form-urlencoded")
        String basicAuth = "Basic ${javax.xml.bind.DatatypeConverter.printBase64Binary("$domain.$user:$pwd".bytes)}"
        connection.setRequestProperty("Authorization", basicAuth)
        String response=""
        try {
            statusCode = connection.responseCode
            println "Connection status code: $statusCode "
            if (statusCode==401) {
                println "Not authorized"
            }
            if (statusCode==200) {
                println "Authentication succeeded"
                println "Server response:"
                println "-----"
                response=displayServerResponse(connection,localFileNameWithPathForStorage)
                println "-----"
            }
            if (statusCode==400) {
                println "Bad request"
                println "Server response:"
                println "-----"
                response=displayServerResponse(connection,"")
                println "-----"
            }
        } catch (Exception e) {
            println "Error connecting to the URL"
            println e.getMessage()
        } finally {
            if (connection != null) {
                connection.disconnect();
            }
        }
        return response
    }
    def displayServerResponse(connection,localFileNameWithPathForStorage) {
        InputStream is;
        if (connection.getResponseCode()==200) {
            is=connection.getInputStream();
        } else {
            is=connection.getErrorStream();
        }
        println "Response Content-Type:${connection.getContentType()}"
        if (connection.getContentType().contains("application/json")) {
            BufferedReader br = new BufferedReader(new InputStreamReader(is));
            StringBuilder sb = new StringBuilder();
            String line;
            while ((line = br.readLine()) != null) {
                sb.append(line+"\n");
            }
            br.close();
            println sb
            return sb.toString()
        } else {
        if (connection.getResponseCode()==200) {
        //storing content
            final int BUFFER_SIZE = 5 * 1024 * 1024;
            def fileExt = connection.getHeaderField("fileExtension");
            println "Downloading file with fileExtension header:$fileExt"
            if (fileExt!=null) {
                def saveFilePath = localFileNameWithPathForStorage;
                File f = new File(saveFilePath);
                is = connection.getInputStream();
                FileOutputStream outputStream = new FileOutputStream(f);
                int bytesRead = -1;
                byte[] buffer = new byte[BUFFER_SIZE];
                while ((bytesRead = is.read(buffer)) != -1) {
                    outputStream.write(buffer, 0, bytesRead);
                }
                println "Downloaded file to $localFileNameWithPathForStorage"
                outputStream.close()
                return localFileNameWithPathForStorage
            } else {
                println "Could not find fileExtension header"
            }
        }
        }
        is.close()
        return ""
    }
    def downloadFile(serverFileName,localFolderForStorage) {
        println "**Downloading file**"
        def restUrl="$interopUrl/applicationsnapshots/$serverFileName/contents"
        def response
        response=openConnection(restUrl,"GET","$localFolderForStorage/$serverFileName")
        println "****"
    }

    def runJob(payload) {
        println "**Exporting security**"
        def restUrl="$planningUrl/applications/$appName/jobs/"
        def response
        println "Payload is $payload"
        response=openConnectionWithPayload(restUrl,"POST",payload as String,"application/json")
        println "****"
        return response
    }
    def getJobStatus(jobId) {
        println "**get Job status**"
        def restUrl="$planningUrl/applications/$appName/jobs/$jobId"
        def response
        response=openConnection(restUrl,"GET","")
        println "****"
        return response
    }
    
    def openConnectionWithPayload(restUrl,method,payload, contentType) {
        println "Opening connection to $restUrl with method:$method"
        int statusCode
        setProxyParams()
        setSSLParams()
        URL newUrl
        newUrl=new URL(restUrl)
        connection = (HttpURLConnection) newUrl.openConnection()
        connection.setDoOutput(true)
        connection.setDoInput(true)
        connection.setUseCaches(false)
        if (method)
            connection.setRequestMethod(method)
        else
            connection.setRequestMethod("GET")
        connection.setRequestProperty("Content-Type",contentType)
        String basicAuth = "Basic " + javax.xml.bind.DatatypeConverter.printBase64Binary("$domain.$user:$pwd".bytes)
        connection.setRequestProperty("Authorization", basicAuth)
        if (payload){
            connection.getOutputStream().write(payload.getBytes("UTF-8"))
           }
        String response=""
        try {
            statusCode = connection.responseCode
            println "Connection status code: $statusCode "
            if (statusCode==401) {
                println "Not authorized"
            }
            if (statusCode==200) {
                println "Authentication succeeded"
                println "Server response:"
                println "-----"
                response=displayServerResponse(connection,localFileNameWithPathForStorage)
                println "-----"
            }
            if (statusCode==400) {
                println "Bad request"
                println "Server response:"
                println "-----"
                response=displayServerResponse(connection,"")
                println "-----"
            }
            if (statusCode==201){
                println "Authentication succeeded"
                println "Server response:"
                println "-----"
                InputStream is=connection.getInputStream()
                BufferedReader br = new BufferedReader(new InputStreamReader(is))
                def object = new JsonSlurper().parseText(br.readLines())
                br.close()
                println "Processing job"
                println "Checking status"
                response=object.jobId
                println "-----"
            }
        } catch (Exception e) {
            println "Error connecting to the URL"
            println e.getMessage()
        } finally {
            if (connection != null) {
                connection.disconnect();
            }
        }
        return response
    }
    
    
    def executeLCMExport(snapshotName) {
        println "**Exporting snapshot**"
        def typeExport="{type:export}"
        def restUrl="$interopUrl/applicationsnapshots/$snapshotName/migration?q=$typeExport"
        def response
        response=openConnection(restUrl,"POST","")
        println "****"
        return response
    }
    def getLCMStatus(restUrl) {
        println "**Getting snapshot status**"
        def response
        response=openConnection(restUrl,"GET","")
        println "****"
        return response
    }
    // Helper functions
    def unZipWithFolders(fileName, destinationFolder,overWrite){
        def ant = new AntBuilder()
        def ovrWrite
        if (overWrite) {
            ovrWrite=overWrite
        } else {
            ovrWrite = "false"
        }
        ant.unzip( src:fileName,
                    dest:destinationFolder,
                    overwrite:ovrWrite )
    }
}
class WaitForCode {
    static retry( sleepTime, nbOfRetries, Closure logicToRun){
      Throwable catched = null
      for(int i=0; i<nbOfRetries; i++){
          try {
              return logicToRun.call()
          } catch(Throwable t){
              catched = t
              println ("Retrying...")
              Thread.sleep(sleepTime)
          }
      }
      println ("Retry count limit exceeded. Stopping check.")
      throw catched
    }
}
    // Check whether table exists, if not create it
    def createDerbyTable(Sql sql,sqlCols, tableName){
        def metadata = sql.connection.getMetaData()
        def table = metadata.getTables(null, null, tableName.toUpperCase(), null)
        if (!table.next()) {
             sql.execute """CREATE TABLE APP."${tableName.toUpperCase()}" ( ${sqlCols.join(" ,")}) """ as String
        } else {
             sql.execute """TRUNCATE TABLE APP."${tableName.toUpperCase()}" """ as String
        }
    }
    // derby bulk insert file
    def derbyBulkInsert (Sql sql, String tableName, String fullFileName, String columnDelimiter) {
        println """**Loading Derby database table $tableName with data from $fullFileName and $columnDelimiter as delimiter **"""
        println "-----"
        // runs in replace mode since the last argument is a non-zero
        sql.execute("CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(null,'" + tableName.toUpperCase() + "','" + fullFileName + "','" + columnDelimiter + "',null,null,1)");
        println "Load completed"
        println "-----"
        println "****"
    }
    def loadDimensionsAndCreateReport(sql,lcmFolder, reportPath,delimiter, filter) {
        try {
            // read the listing file for all the dimensions from the listing  xml
            plnDIMXML = new XmlParser().parse(new File("$lcmFolder/info/listing.xml").toURI().toString())
            // find the names of the dimensions and their paths and add to a map
            allDims=plnDIMXML.resource.findAll{it.@path.matches("(.*)Dimensions(.*)")}.collectEntries{[it.@name,it.@path]}
            if (allDims){
                allDims.each { dims ->
                    dimName = dims.key
                    dimPath = "$lcmFolder/resource/${dims.value}/${dims.key}.csv"
                    sqlCols=[]
                    def dimLine=0
                    def inputStream = new FileInputStream(dimPath)
                    def reader = new BufferedReader(new InputStreamReader(inputStream))
                    // All dimension files got XML code in it.
                    // The csv information starts after a specific pattern
                    // We are checking what line the pattern is in
                    new File(dimPath).eachLine(){line, lineNumber ->
                        if(line.contains("#--!")) {
                            dimLine=lineNumber
                        }
                    }
                    // Only going to use two columns for this (Child and Parent)
                    ['Child','Parent'].each{
                       sqlCols << """"${it}" VARCHAR(2500)"""
                    }
                    // Create the table if it doesn't exist
                    // If it does then truncate the table
                    createDerbyTable(sql, sqlCols, dimName)
                    
                    // Using SQL Perpared Statment to load data in bulk
                    def insertDIMStmt = """ INSERT INTO "${dimName.toUpperCase()}" ("Child","Parent") values (?,?)""" as String
                    // read each line from the CSV start line
                    // Planning adds formula to multiple lines, the trick I've done is
                    // To check for " in the the lines, if it is got a ", then don't use that line
                    reader.eachLine(){line,lineNumber ->
                      if (lineNumber > dimLine+1) {
                          def mbrLines=line.split(",")
                          if (mbrLines.size()>=2) {
                            if (!(mbrLines[0].matches("(.*)\"(.*)") || mbrLines[1].matches("(.*)\"(.*)"))){
                                
                                sql.withBatch(500, insertDIMStmt) { stmt ->
                                    stmt.addBatch(mbrLines[0], mbrLines[1])
                                }
                            }
                          }
                      }
                    }
                    reader.close()
                    inputStream.close()
                    
                    // Create an INDEX on Child column
                    sql.execute("""CREATE INDEX "IDX_$dimName" ON "${dimName.toUpperCase()}" ("Child") """ as String)
                }
                
                createAccessReport(sql, reportPath, delimiter, filter)
            } else {
                println "No dimensions in the LCM"
            }
        } finally {
            sql.close()
        }
    }
    def createAccessReport(sql, reportPath,delimiter, filter) {
        // if you are looking for dimension access info
        // join with the respective table to find out which dimension it came from
        def secRows
        if (filter == "SL_DIMENSION"){
            def secSQL = []
            allDims.keySet().each{
            secSQL << """
                SELECT distinct '$it' as Dimension, a.* FROM SECACCESS a, "${it.toUpperCase()}" b
                WHERE a."OBJECTNAME" = b."Child"
                """
            }
            writeReportFile = new File(reportPath)
            secRows = sql.rows(secSQL.join("UNION ALL"))
        } else {
            secRows = sql.rows("""SELECT * FROM SECACCESS WHERE "OBJECTTYPE"='$filter' """ as String)
        }
        if (secRows){
            writeReportFile.write(secRows[0].keySet().join(delimiter)) // keySet gives us the column names
            secRows.each{result->
                      cols = result.size()
                      // Check whether the column type is CLOB, if it is then get the char stream's text
                      // Groovy ternary operator in action
                      vals = (0..<cols).collect{(result[it].getClass().name == "oracle.sql.CLOB" ? result[it].getCharacterStream().text.trim() : "${result[it].trim()}")}
                      writeReportFile.append(System.getProperty("line.separator") + vals.join(delimiter))
           }
            println "Access report generated"
           
        } else {
            println "No access defined"
        }
    }
// Read configuration script for the environment info.
def keyConfig = new ConfigSlurper().parse(new File('D:/groovy_scripts/Env_Info.config').toURL())
epbcsURL=keyConfig.serverEnvs.pbcs.epbcsURL
epbcsUserName=keyConfig.serverEnvs.pbcs.epbcsUserName
epbcsPassword=new String(keyConfig.serverEnvs.pbcs.epbcsPassword.decodeBase64())
epbcsDomain=keyConfig.serverEnvs.pbcs.epbcsDomain
appName=keyConfig.serverEnvs.pbcs.appName
apiVersion=keyConfig.serverEnvs.pbcs.apiVersion
lcmVersion=keyConfig.serverEnvs.pbcs.lcmVersion
PbcsRestClient pbcsClient=new PbcsRestClient("$epbcsURL/HyperionPlanning/rest/$apiVersion","$epbcsURL/interop/rest/$lcmVersion","","",epbcsDomain,epbcsUserName, epbcsPassword,appName,"TRUE")
// run export security job
// you can filter either users or groups
// "exportGroups": "group1,group2"
// "exportUsers": "user1,user2"
// you cannot sepcify them together
def secJobId=pbcsClient.runJob(JsonOutput.toJson(["jobType" : "Export Security", "jobName" : "ExportSecurity", "parameters":["fileName":"SecurityRecordsFile.csv"]]))
// run LCM export
def lcmJobResponse=pbcsClient.executeLCMExport("Export_SecDimensions")
def lcmObject = new JsonSlurper().parseText(lcmJobResponse)
// get the status of the lcm
// we cannot use job id to check the status as this is migration job
// only one link will have job status link, hence .find
def lcmJobLink = lcmObject.links.find{it["rel"] == "Job Status"}["href"]
// keep checking the status of sec export and dimension LCM file still it is complete
def secJobStatus="Processing"
while (secJobStatus=="Processing"){
    def response = pbcsClient.getJobStatus(secJobId)
    def object = new JsonSlurper().parseText(response)
    secJobStatus=object.descriptiveStatus
    sleep 10*1000 //sleep for 10 seconds
}
println "Export security Job $secJobStatus"
def jobStatus=-1
while (jobStatus == -1){
   def object = new JsonSlurper().parseText(pbcsClient.getLCMStatus(lcmJobLink))
   jobStatus=object.status
   sleep 10*1000 //sleep for 10 seconds
}
println "LCM Export status $jobStatus"
if (secJobStatus == "Completed" && jobStatus == 0){
    pbcsClient.downloadFile("SecurityRecordsFile.csv", "D:/groovy_scripts")
    pbcsClient.downloadFile("Export_SecDimensions", "D:/groovy_scripts")
    pbcsClient.unZipWithFolders("D:/groovy_scripts/Export_SecDimensions","D:/groovy_scripts/Dimension Security","true")
    // create an in memory instance of derby database
    def sql = Sql.newInstance(
        url:'jdbc:derby:memory:epbcsAccessDB;create=true',
        driver:'org.apache.derby.jdbc.EmbeddedDriver'
    )
    // Create the table to capture Access
    // following are the columns that are present in Export security
    def secSQLCols =[]
    ["OBJECTNAME","NAME","PARENT","ISUSER","OBJECTTYPE","ACCESSTYPE","ACCESSMODE","REMOVE"].each{
        secSQLCols << """"${it.trim()}" VARCHAR(2500)"""
    }
    createDerbyTable(sql, secSQLCols, "SECACCESS")    
    derbyBulkInsert(sql, "SECACCESS","D:/groovy_scripts/SecurityRecordsFile.csv",",")
    sql.execute("""CREATE INDEX "IDX_SECACCESS" ON "SECACCESS" ("OBJECTNAME") """ as String)
    /* The following object types are supported in filter */
    /* Object Type:
    SL_FORM - Form
    SL_COMPOSITE - Composite Form
    SL_TASKLIST - Tasklist
    SL_CALCRULE - Rule
    SL_FORMFOLDER - Form Folder
    SL_CALCFOLDER - Rule Folder
    SL_DIMENSION - Dimension
    SL_CALCTEMPLATE - Template
    SL_REPORT - Management Report
    SL_REPORTSSHOT - Management Report Snapshot
    */
    
    loadDimensionsAndCreateReport(sql,"D:/groovy_scripts/Dimension Security/HP-$appName", 'D:/groovy_scripts/AccessReport.txt',",","SL_DIMENSION")
} else {
    println "Export process failed"
}

package com.oracle.ceal
@Grab('org.apache.derby:derby:10.9.1.0')
@Grab('commons-io:commons-io:2.6')
@GrabConfig(systemClassLoader=true)
import javax.net.ssl.HostnameVerifier
import javax.net.ssl.HttpsURLConnection
import javax.net.ssl.SSLContext
import javax.net.ssl.TrustManager
import javax.net.ssl.X509TrustManager
import java.net.HttpURLConnection
import java.util.zip.ZipFile
import static java.sql.ResultSet.*
import java.nio.file.Files
import java.nio.file.Paths
import groovy.json.JsonSlurper
import groovy.sql.Sql
import groovy.io.FileType
class PbcsRestClient {
    private HttpURLConnection connection
    private def planningUrl
    private def interopUrl
    private def proxyHost
    private def proxyPort
    private def user
    private def pwd
    private def domain
    private def ignoreSSLCertsErrors
    public PbcsRestClient(planningServerUrl, interopServerUrl,httpProxyHost, httpProxyPort, identityDomain,username, password, ignoreSSLCertificationPathErrors) {
        planningUrl=planningServerUrl
        interopUrl=interopServerUrl
        proxyHost=httpProxyHost
        proxyPort=httpProxyPort
        domain=identityDomain
        user=username
        pwd=password
        ignoreSSLCertsErrors=ignoreSSLCertificationPathErrors
    }
    def setProxyParams() {
        Properties systemProperties = System.getProperties()
        systemProperties.setProperty("http.proxyHost",proxyHost)
        systemProperties.setProperty("http.proxyPort",proxyPort)
        systemProperties.setProperty("https.proxyHost",proxyHost)
        systemProperties.setProperty("https.proxyPort",proxyPort)
    }
    def setSSLParams() {
        if (ignoreSSLCertsErrors !=null && ignoreSSLCertsErrors.toUpperCase()=="TRUE") {
            println "Ignoring SSL certification path errors"
            // Disable SSL cert validation
            def hostnameVerifier = [
                verify: { hostname, session -> true }
            ]
            def trustManager = [
                    checkServerTrusted: { chain, authType -> },
                    checkClientTrusted: { chain, authType -> },
                    getAcceptedIssuers: { null }
            ]
            HttpsURLConnection.setDefaultHostnameVerifier(hostnameVerifier as HostnameVerifier)
            SSLContext context = SSLContext.getInstance("SSL")
            context.init(null, [trustManager as X509TrustManager] as TrustManager[], null)
            HttpsURLConnection.setDefaultSSLSocketFactory(context.getSocketFactory())

        }
    }
    def openConnection(restUrl,method,localFileNameWithPathForStorage) {
        println "Opening connection to $restUrl with method:$method"
        int statusCode
        setProxyParams()
        setSSLParams()
        URL newUrl
        newUrl=new URL(restUrl)
        connection = (HttpURLConnection) newUrl.openConnection()
        connection.setDoOutput(true)
        connection.setDoInput(true)
        connection.setUseCaches(false)
        if (method=="")
            connection.setRequestMethod("GET")
        else
            connection.setRequestMethod(method)
        connection.setRequestProperty("Content-Type","application/x-www-form-urlencoded")
        String basicAuth = "Basic ${javax.xml.bind.DatatypeConverter.printBase64Binary("$domain.$user:$pwd".bytes)}"
        connection.setRequestProperty("Authorization", basicAuth)
        String response=""
        try {
            statusCode = connection.responseCode
            println "Connection status code: $statusCode "
            if (statusCode==401) {
                println "Not authorized"
            }
            if (statusCode==200) {
                println "Authentication succeeded"
                println "Server response:"
                println "-----"
                response=displayServerResponse(connection,localFileNameWithPathForStorage)
                println "-----"
            }
            if (statusCode==400) {
                println "Bad request"
                println "Server response:"
                println "-----"
                response=displayServerResponse(connection,"")
                println "-----"
            }
        } catch (Exception e) {
            println "Error connecting to the URL"
            println e.getMessage()
        } finally {
            if (connection != null) {
                connection.disconnect();
            }
        }
        return response
    }
    def displayServerResponse(connection,localFileNameWithPathForStorage) {
        InputStream is;
        if (connection.getResponseCode()==200) {
            is=connection.getInputStream();
        } else {
            is=connection.getErrorStream();
        }
        println "Response Content-Type: ${connection.getContentType()}"
        if (connection.getContentType().contains("application/json")) {
            BufferedReader br = new BufferedReader(new InputStreamReader(is));
            StringBuilder sb = new StringBuilder();
            String line;
            while ((line = br.readLine()) != null) {
                sb.append(line+"\n");
            }
            br.close();
            println sb
            return sb.toString()
        } else {
        if (connection.getResponseCode()==200) {
        //storing content
            final int BUFFER_SIZE = 5 * 1024 * 1024;
            def fileExt = connection.getHeaderField("fileExtension");
            println "Downloading file with fileExtension header:$fileExt"
            if (fileExt!=null) {
                def saveFilePath = localFileNameWithPathForStorage;
                File f = new File(saveFilePath);
                is = connection.getInputStream();
                FileOutputStream outputStream = new FileOutputStream(f);
                int bytesRead = -1;
                byte[] buffer = new byte[BUFFER_SIZE];
                while ((bytesRead = is.read(buffer)) != -1) {
                    outputStream.write(buffer, 0, bytesRead);
                }
                println "Downloaded file to $localFileNameWithPathForStorage"
                outputStream.close()
                return localFileNameWithPathForStorage
            } else {
                println "Could not find fileExtension header"
            }
        }
        }
        is.close()
        return ""
    }
    def downloadFile(serverFileName,localFolderForStorage) {
        println "**Downloading file**"
        def restUrl="$interopUrl/applicationsnapshots/$serverFileName/contents"
        def response
        response=openConnection(restUrl,"GET","$localFolderForStorage/$serverFileName")
        println "****"
    }

    def executeLCMExport(snapshotName) {
        println "**Exporting snapshot**"
        def typeExport="{type:export}"
        def restUrl="$interopUrl/applicationsnapshots/$snapshotName/migration?q=$typeExport"
        def response
        response=openConnection(restUrl,"POST","")
        println "****"
        return response
    }
    def getLCMStatus(restUrl) {
        println "**Getting snapshot status**"
        def response
        response=openConnection(restUrl,"GET","")
        println "****"
        return response
    }
    // Helper functions
    def unZipWithFolders(fileName, destinationFolder,overWrite){
        def ant = new AntBuilder()
        def ovrWrite
        if (overWrite) {
            ovrWrite=overWrite
        } else {
            ovrWrite = "false"
        }
        ant.unzip(  src:fileName,
                    dest:destinationFolder,
                    overwrite:ovrWrite )
    }
}
class WaitForCode {
    static retry( sleepTime, nbOfRetries, Closure logicToRun){
      Throwable catched = null
      for(int i=0; i<nbOfRetries; i++){
          try {
              return logicToRun.call()
          } catch(Throwable t){
              catched = t
              println ("Retrying...")
              Thread.sleep(sleepTime)
          }
      }
      println ("Retry count limit exceeded. Stopping check.")
      throw catched
    }
}
// Check whether table exists, if not create it
def createDerbyTable(Sql sql,sqlCols, tableName){
    def metadata = sql.connection.getMetaData()
    def table = metadata.getTables(null, null, tableName.toUpperCase(), null)
    if (!table.next()) {
         sql.execute """CREATE TABLE APP."${tableName.toUpperCase()}" ( ${sqlCols.join(" ,")}) """ as String
    } else {
         sql.execute """TRUNCATE TABLE APP."${tableName.toUpperCase()}" """ as String
    }
}
def createAccessReport(sql,lcmFolder,reportPath,delimiter, filter) {
    try {
        // read the listing file for all the dimensions from the listing  xml
        plnDIMXML = new XmlParser().parse(new File("$lcmFolder/info/listing.xml").toURI().toString())
        // find the names of the dimensions and their paths and add to a map
        allDims=plnDIMXML.resource.findAll{it.@path.matches("(.*)Dimensions(.*)")}.collectEntries{[it.@name,it.@path]}
        if (allDims){
            allDims.each { dims ->
                dimName = dims.key
                dimPath = "$lcmFolder/resource/${dims.value}/${dims.key}.csv"
                sqlCols=[]
                def dimLine=0
                def inputStream = new FileInputStream(dimPath)
                def reader = new BufferedReader(new InputStreamReader(inputStream))
                // All dimension files got XML code in it.
                // The csv information starts after a specific pattern
                // We are checking what line the pattern is in
                new File(dimPath).eachLine(){line, lineNumber ->
                    if(line.contains("#--!")) {
                        dimLine=lineNumber
                    }
                }
                // Only going to use two columns for this (Child and Parent)
                ['Child','Parent'].each{
                   sqlCols << """"${it}" VARCHAR(2500)"""
                }
                // Create the table if it doesn't exist
                // If it does then truncate the table
                createDerbyTable(sql, sqlCols, dimName)
                
                // Using SQL Perpared Statment to load data in bulk
                def insertDIMStmt = """ INSERT INTO "${dimName.toUpperCase()}" ("Child","Parent") values (?,?)""" as String
                // read each line from the CSV start line
                // Planning adds formula to multiple lines, the trick I've done is
                // To check for " in the the lines, if it is got a ", then don't use that line
                reader.eachLine(){line,lineNumber ->
                  if (lineNumber > dimLine+1) {
                      def mbrLines=line.split(",")
                      if (mbrLines.size()>=2) {
                        if (!(mbrLines[0].matches("(.*)\"(.*)") || mbrLines[1].matches("(.*)\"(.*)"))){
                            
                            sql.withBatch(500, insertDIMStmt) { stmt ->
                                stmt.addBatch(mbrLines[0], mbrLines[1])
                            }
                        }
                      }
                  }
                }
                reader.close()
                inputStream.close()
                
                // Create an INDEX on Child column
                sql.execute("""CREATE INDEX "IDX_$dimName" ON "${dimName.toUpperCase()}" ("Child") """ as String)
            }
            
            // Create the table to capture Access
            def secSQLCols =[]
            ['NAME','OBJECTNAME','OBJECTTYPE','ACCESSMODE','FLAG','ISUSER'].each{
                secSQLCols << """"${it.trim()}" VARCHAR(2500)"""
            }
            createDerbyTable(sql, secSQLCols, "SECACCESS")
            
            // Read the access XML files
            def insertSECStmt = 'INSERT INTO SECACCESS (NAME, OBJECTNAME, OBJECTTYPE, ACCESSMODE, FLAG, ISUSER) values (?,?,?,?,?,?)'
            new File("$lcmFolder/resource/Security/Access Permissions").eachFileRecurse(FileType.FILES){
                plnSECXML = new XmlParser().parse(it.toURI().toString())
                sql.withBatch(500, insertSECStmt) { stmt ->
                    plnSECXML.acl.each{
                        stmt.addBatch(it.name.text(), it.objectName.text(), it.objectType.text(), it.accessMode.text(), it.flag.text(), it.isUser.text())
                    }
                }
            }
            sql.execute("""CREATE INDEX "IDX_SECACCESS" ON "SECACCESS" ("OBJECTNAME") """ as String)
            
            // if you are looking for dimension access info
            // join with the respective table to find out which dimension it came from
            def secRows
            if (filter == "SL_DIMENSION"){
                def secSQL = []
                allDims.keySet().each{
                secSQL << """
                    SELECT distinct '$it' as Dimension, a.* FROM SECACCESS a, "${it.toUpperCase()}" b
                    WHERE a."OBJECTNAME" = b."Child"
                    """
                }
                writeReportFile = new File(reportPath)
                secRows = sql.rows(secSQL.join("UNION ALL"))
            } else {
                secRows = sql.rows("""SELECT * FROM SECACCESS WHERE "OBJECTTYPE"='$filter' """ as String)
            }
            if (secRows){
                writeReportFile.write(secRows[0].keySet().join(delimiter)) // keySet gives us the column names
                secRows.each{result->
                          cols = result.size()
                          // Check whether the column type is CLOB, if it is then get the char stream's text
                          // Groovy ternary operator in action
                          vals = (0..<cols).collect{(result[it].getClass().name == "oracle.sql.CLOB" ? result[it].getCharacterStream().text.trim() : "${result[it].trim()}")}
                          writeReportFile.append(System.getProperty("line.separator") + vals.join(delimiter))
               }
            }
            println "Access report generated"
        } else {
            println "No dimensions in the LCM"
        }
    } finally {
        sql.close()
    }
}
// Read configuration script for the environment info.
def keyConfig = new ConfigSlurper().parse(new File('D:/groovy_scripts/Env_Info.config').toURL())
epbcsURL=keyConfig.serverEnvs.pbcs.epbcsURL
epbcsUserName=keyConfig.serverEnvs.pbcs.epbcsUserName
epbcsPassword=new String(keyConfig.serverEnvs.pbcs.epbcsPassword.decodeBase64())
epbcsDomain=keyConfig.serverEnvs.pbcs.epbcsDomain
appName=keyConfig.serverEnvs.pbcs.appName
apiVersion=keyConfig.serverEnvs.pbcs.apiVersion
lcmVersion=keyConfig.serverEnvs.pbcs.lcmVersion
PbcsRestClient pbcsClient=new PbcsRestClient("$epbcsURL/HyperionPlanning/rest/$apiVersion","$epbcsURL/interop/rest/$lcmVersion","","",epbcsDomain,epbcsUserName, epbcsPassword, "TRUE")
// run LCM export
def lcmJobResponse=pbcsClient.executeLCMExport("Export_SecDimensions")
def lcmObject = new JsonSlurper().parseText(lcmJobResponse)
// get the status of the lcm
// we cannot use job id to check the status as this is migration job
// only one link will have job status link, hence .find
def lcmJobLink = lcmObject.links.find{it["rel"] == "Job Status"}["href"]
def jobStatus=-1
while (jobStatus == -1){
   def object = new JsonSlurper().parseText(pbcsClient.getLCMStatus(lcmJobLink))
   jobStatus=object.status
   println jobStatus
   sleep 10*1000 //sleep for 10 seconds
}
println "LCM Export status $jobStatus"
if(jobStatus == 0) {
    pbcsClient.downloadFile("Export_SecDimensions", "D:/groovy_scripts")
    pbcsClient.unZipWithFolders("D:/groovy_scripts/Export_SecDimensions","D:/groovy_scripts/Security Extract","true")
    // create an in memory instance of derby database
    def sql = Sql.newInstance(
        url:'jdbc:derby:memory:epbcsAccessDB;create=true',
        driver:'org.apache.derby.jdbc.EmbeddedDriver'
    )
    /* The following object types are supported in filter */
    /* Object Type:
    SL_FORM - Form
    SL_COMPOSITE - Composite Form
    SL_TASKLIST - Tasklist
    SL_CALCRULE - Rule
    SL_FORMFOLDER - Form Folder
    SL_CALCFOLDER - Rule Folder
    SL_DIMENSION - Dimension
    SL_CALCTEMPLATE - Template
    SL_REPORT - Management Report
    SL_REPORTSSHOT - Management Report Snapshot
    */
    createAccessReport(sql,"D:/groovy_scripts/Security Extract/HP-$appName",'D:/groovy_scripts/AccessReport.txt',",","SL_DIMENSION")
} else {
    println "Export process failed"
}