Update_HCPCS_Part2.gradle

/*
****************************
* HCPCS Vocabulary update  *
* Version 0.0.6            *
****************************
*/

group 'com.sci.vocabulary'
version '1.0-SNAPSHOT'

apply plugin: 'java'

sourceCompatibility = 1.8

buildscript {
    repositories {
        mavenLocal()
        mavenCentral()
    }
    dependencies {
        classpath 'org.apache.poi:poi:3.9'
        classpath 'org.apache.poi:poi-ooxml:3.9'
    }
}

import groovy.io.FileType
import org.apache.poi.ss.usermodel.Cell
import org.apache.poi.ss.usermodel.Row
import org.apache.poi.ss.usermodel.Workbook
import org.apache.poi.ss.usermodel.WorkbookFactory
import org.apache.poi.ss.usermodel.Sheet

/*
task compileOne(type: JavaCompile) {
    source = sourceSets.main.java.srcDir
    include 'inc.odysseus.utils/StringBuilderWrapper.java'
    classpath = sourceSets.main.output.classesDir
    destinationDir = sourceSets.main.output.classesDir
}
compileOne.options.compilerArgs = ["-sourcepath", "$projectDir/src/main/java"]
*/

/**
 * Auxiliary classes. TODO: Further should be used java-defined and compiled only
 */
public class StringBuilderWrapper implements java.lang.Appendable {
    private static final String IDENT_CHAR = " " *4
    private StringBuilder wrapped

    public StringBuilderWrapper() {
        wrapped = new StringBuilder()
    }
    public StringBuilderWrapper(String value) {
        this()
        wrapped.append(IDENT_CHAR).append(value)
    }

    @Override
    Appendable append(CharSequence csq) throws IOException {
        wrapped.append(IDENT_CHAR).append(csq)
        return this
    }
    @Override
    Appendable append(CharSequence csq, int start, int end) throws IOException {
        wrapped.append(IDENT_CHAR).append(csq, start, end)
        return this
    }
    @Override
    Appendable append(char c) throws IOException {
        wrapped.append(IDENT_CHAR).append(c)
        return this
    }
    @Override
    String toString() {
        return wrapped.toString()
    }
}

// Load HCPCS properties
Properties props = new Properties()
props.load(new FileInputStream("$project.rootDir/Update_HCPCS.properties"))
props.each {prop ->
    project.ext.set(prop.key, prop.value)
}
props.load(new FileInputStream("$project.rootDir/Update_HCPCS_Part2.properties"))
props.each {prop ->
    project.ext.set(prop.key, prop.value)
}

def tempDir = File.createTempDir()
tempDir.deleteOnExit()

/**
 * Just renew user schema and prepare it for further vocabulary update
 */
task fastRecreateSchema(description: project.properties['fastRecreate.description']) << {
    println '\n\n***********************************************'
    println '*                                             *'
    println "*       '${description}' action started...     *"
    println '*                                             *'
    println '***********************************************'

    // Start action checkpoint
    def timeStart = System.currentTimeMillis()

    def log_file = "${tempDir.getPath()}\\${project.properties['fastRecreate.logFileName']}"
    def stream_log_file = "${tempDir.getPath()}\\${project.properties['fastRecreate.streamLogFileName']}"
    def stream_error_file = "${tempDir.getPath()}\\${project.properties['fastRecreate.streamErrFileName']}"

    def db = [un: project.properties['db.userName'],
              pw: project.properties['db.password'],
              tns: project.properties['db.tnsName'],
              sqlfile: "${project.properties['basePath']}\\${project.properties['fastRecreate.sqlFileName']}",
              logfile: log_file]
    def sout = new StringBuilderWrapper(), serr = new StringBuilderWrapper()
    def sqlplus= """sqlplus.exe ${db.un}/${db.pw}@${db.tns} @${db.sqlfile} ${db.logfile}""".execute()
    sqlplus.consumeProcessOutput(sout, serr)
    sqlplus.waitFor()

    // Finish action checkpoint
    def timeFinish = System.currentTimeMillis()

    // Write to console
    System.out.println(sout.toString())

    // Write to logfile
    File f = new File(stream_log_file)
    f.write(sout.toString())

    // Write to error file
    f = new File(stream_error_file)
    f.write(serr.toString())

    /* Check for errors */
    if (project.properties['stop.on.error'].toBoolean() && (sqlplus.exitValue() != 0 || (serr != null && serr.toString().length() > 0))) {
        throw new GradleException(String.format(project.properties['error.message.with.code'], sqlplus.exitValue(), name))
    }

    // Measure time execution
    def timeElapsed = (timeFinish - timeStart) / 1000
    println "Time elapsed: ${timeElapsed} seconds."

    println "*** '${description}' action done ***"
}

/**
 * This task will prepare user schema for vocabulary update routine.
 * It will create auxiliary table - the "ANWEB_V2" in our case, for example.
 */
task createSourceTable(dependsOn: fastRecreateSchema, description: project.properties['createSourceTable.description']) << {
    println '\n\n***********************************************'
    println '*                                             *'
    println "*   '${description}' action started...   *"
    println '*                                             *'
    println '***********************************************'

    // Start action checkpoint
    def timeStart = System.currentTimeMillis()

    def log_file = "${tempDir.getPath()}\\${project.properties['createSourceTable.logFileName']}"
    def stream_log_file = "${tempDir.getPath()}\\${project.properties['createSourceTable.streamLogFileName']}"
    def stream_error_file = "${tempDir.getPath()}\\${project.properties['createSourceTable.streamErrFileName']}"

    def db = [un: project.properties['db.userName'],
              pw: project.properties['db.password'],
              tns: project.properties['db.tnsName'],
              sqlfile: "${project.properties['basePath']}\\${project.properties['createSourceTable.sqlFileName']}",
              logfile: log_file]
    def sout = new StringBuilderWrapper(), serr = new StringBuilderWrapper()
    def sqlplus= """sqlplus.exe ${db.un}/${db.pw}@${db.tns} @${db.sqlfile} ${db.logfile}""".execute()
    sqlplus.consumeProcessOutput(sout, serr)
    sqlplus.waitFor()

    // Finish action checkpoint
    def timeFinish = System.currentTimeMillis()

    // Write to console
    System.out.println(sout.toString())

    // Write to logfile
    File f = new File(stream_log_file)
    f.write(sout.toString())

    // Write to error file
    f = new File(stream_error_file)
    f.write(serr.toString())

    /* Check for errors */
    if (project.properties['stop.on.error'].toBoolean() && (sqlplus.exitValue() != 0 || (serr != null && serr.toString().length() > 0))) {
        throw new GradleException(String.format(project.properties['error.message.with.code'], sqlplus.exitValue(), name))
    }

    // Measure time execution
    def timeElapsed = (timeFinish - timeStart) / 1000
    println "Time elapsed: ${timeElapsed} seconds."

    println "*** '${description}' action done ***"
}

/**
 * Task which will download vocabulary update package from source host.
 * This one - 'http://www.cms.gov/Medicare/Coding/HCPCSReleaseCodeSets/' is our case.
 */
task downloadUpdatePack(dependsOn: createSourceTable, description: project.properties['downloadUpdatePack.description']) << {
    println '\n\n***********************************************'
    println '*                                             *'
    println "*        '${description}' action started...         *"
    println '*                                             *'
    println '***********************************************'

    /* Downloading file */
    // Start action checkpoint
    def timeStart = System.currentTimeMillis()

    println new StringBuilderWrapper('downloading file...')
    println new StringBuilderWrapper("stored to: ${tempDir.getPath()}")
    ant.get(src: project.properties['downloadUpdatePack.fileUrl'], dest: tempDir, verbose: true)
    println new StringBuilderWrapper('download completed')

    // Finish action checkpoint
    def timeFinish = System.currentTimeMillis()
    def timeElapsed = (timeFinish - timeStart) / 1000
    println "Time elapsed: ${timeElapsed} seconds."

    println "*** '${description}' action done ***"
}

task extractFiles(dependsOn: downloadUpdatePack, description: project.properties['extractFiles.description']) << {
    println '\n\n***********************************************'
    println '*                                             *'
    println "*        '${description}' action started...          *"
    println '*                                             *'
    println '***********************************************'

    // Start action checkpoint
    def timeStart = System.currentTimeMillis()

    /* Extracting file */
    def fileList = []
    def dir = tempDir
    dir.eachFile(FileType.FILES) { file ->
        fileList << file
        if (file.getPath().endsWith('zip')) {
            ant.unzip(src: file.getPath(), dest: tempDir, overwrite: false)
            println new StringBuilderWrapper('data exctracted...')
        }
    }

    // Finish action checkpoint
    def timeFinish = System.currentTimeMillis()

    // Measure time execution
    def timeElapsed = (timeFinish - timeStart) / 1000
    println "Time elapsed: ${timeElapsed} seconds."

    println "*** '${description}' action done ***"
}

/**
 * This task will convert a XLS data file to CSV format.
 */
task xls2Csv(dependsOn: extractFiles, description: project.properties['xls2Csv.description']) << {
    println '\n\n*************************************************'
    println '*                                               *'
    println "*  '${description}' action started...  *"
    println '*                                               *'
    println '*************************************************'

    def inputFile = "${tempDir.getPath()}\\${project.properties['xls2Csv.srcFileName']}"
    def outputFile = "${tempDir.getPath()}\\${project.properties['xls2Csv.destFileName']}"

    // Start action checkpoint
    def timeStart = System.currentTimeMillis()
    def timeFinish = timeStart

    // For storing data into CSV files
    StringBuffer data = new StringBuffer();
    try {
        FileOutputStream fos = new FileOutputStream(outputFile);

        // Get the workbook object for XLS file
        Workbook workbook = WorkbookFactory.create(new FileInputStream(inputFile));
        // Get first sheet from the workbook
        Sheet sheet = workbook.getSheetAt(0);
//        Cell cell;
//        Row row;

        for (int i = 0; i < sheet.getLastRowNum(); i++){
            Row row = sheet.getRow(i);

            if (row!= null){
                for (int j = 0; j < row.getLastCellNum(); j++){
                    Cell cell = row.getCell(j);
                    if (cell == null){
                        data.append(";");
                    } else {
                        cell.setCellType(Cell.CELL_TYPE_STRING)
                        data.append("\"" + cell. toString().trim().replaceAll("\"", "'") + "\"" + ";");
//                        switch (cell.getCellType()) {
//                            case Cell.CELL_TYPE_BOOLEAN:
//                                data.append("\"" + cell.getBooleanCellValue() + "\"" + ";");
//                                break;
//
//                            case Cell.CELL_TYPE_NUMERIC:
//                                data.append("\"" + cell.cgetNumericCellValue() + "\"" + ";");
//                                break;
//
//                            case Cell.CELL_TYPE_STRING:
////                                if (!cell.getStringCellValue().contains("\"")){
//                                println cell.toString()
//                                    data.append("\"" + cell.getStringCellValue().trim().replaceAll("\"", "'") + "\"" + ";");
////                                } else {
////                                    data.append(";");
////                                }
//                                break;
//
//                            case Cell.CELL_TYPE_BLANK:
//                                data.append("" + ";");
//                                break;
//
//                            default:
//                                data.append(cell + ";");
//                        }



                    }
                }
                data.append('\n');
            } else {

            }
        }

        // Iterate through each rows from first sheet
//        Iterator<Row> rowIterator = sheet.iterator();
//        while (rowIterator.hasNext()) {
//            row = rowIterator.next();
//            // For each row, iterate through each columns
//            Iterator<Cell> cellIterator = row.cellIterator();
//            while (cellIterator.hasNext()) {
//                cell = cellIterator.next();
//
//                switch (cell.getCellType()) {
//                    case Cell.CELL_TYPE_BOOLEAN:
//                        data.append("\"" + cell.getBooleanCellValue() + "\"" + ";");
//                        break;
//
//                    case Cell.CELL_TYPE_NUMERIC:
//                        data.append("\"" + cell.getNumericCellValue() + "\"" + ";");
//                        break;
//
//                    case Cell.CELL_TYPE_STRING:
//                        data.append("\"" + cell.getStringCellValue().trim() + "\"" + ";");
//                        break;
//
//                    case Cell.CELL_TYPE_BLANK:
//                        data.append("" + ";");
//                        break;
//
//                    default:
//                        data.append(cell + ";");
//                }
//
//            }
//
//        }
        println new StringBuilderWrapper('files converted...')
        fos.write(data.toString().getBytes());
        fos.close()

        // Finish action checkpoint
        timeFinish = System.currentTimeMillis()

    }
    catch (FileNotFoundException e) {
        e.printStackTrace();
    }
    catch (IOException e) {
        e.printStackTrace();
    }

    // Measure time execution
    def timeElapsed = (timeFinish - timeStart) / 1000
    println "Time elapsed: ${timeElapsed} seconds."

    println "*** '${description}' action done ***"
}

/**
 * Task performed import CSV data to database
 */
task importData(dependsOn: xls2Csv, description: project.properties['import.description']) << {
    println '\n\n***********************************************'
    println '*                                             *'
    println "*         '${description}' action started...          *"
    println '*                                             *'
    println '***********************************************'

    // Start action checkpoint
    def timeStart = System.currentTimeMillis()

    def dataFile = "${tempDir.getPath()}\\${project.properties['importData.dataFileName']}"
    def logFile = "${tempDir.getPath()}\\${project.properties['importData.logFileName']}"
    def discardFile = "${tempDir.getPath()}\\${project.properties['importData.discardFileName']}"
    def badFile = "${tempDir.getPath()}\\${project.properties['importData.badFileName']}"
    def stream_log_file = "${tempDir.getPath()}\\${project.properties['importData.streamLogFileName']}"
    def stream_error_file = "${tempDir.getPath()}\\${project.properties['importData.streamErrFileName']}"

    def bosOut = new ByteArrayOutputStream(), bosErr = new ByteArrayOutputStream()
    def sout = new StringBuilderWrapper(), serr = new StringBuilderWrapper()
    exec {
        executable "sqlldr.exe"
        args "USERID=${project.properties['db.userName']}/${project.properties['db.password']}@${project.properties['db.tnsName']} " +
                "CONTROL=${project.properties['basePath']}\\${project.properties['import.controlFileName']} DATA=$dataFile LOG=$logFile DISCARD=$discardFile BAD=$badFile"
        standardOutput = bosOut
        errorOutput = bosErr
    }
    def bisOut = new ByteArrayInputStream(bosOut.toByteArray())
    bisOut.withReader { out->
        out.eachLine {
            sout.append(it).append('\n')
        }
    }
    def bisErr = new ByteArrayInputStream(bosErr.toByteArray())
    bisErr.withReader { out ->
        out.eachLine {
            serr.append(it).append('\n')
        }
    }

    // Write to console
    System.out.println(sout.toString())

    // Write to logfile
    File f = new File(stream_log_file)
    f.write(sout.toString())

    // Write to error file
    f = new File(stream_error_file)
    f.write(serr.toString())

    // Finish action checkpoint
    def timeFinish = System.currentTimeMillis()

    // Measure time execution
    def timeElapsed = (timeFinish - timeStart) / 1000
    println "Time elapsed: ${timeElapsed} seconds."

    println "*** '${description}' action done ***"
}

/**
 * This task will run 'Load stage' script.
 * Seems it's is specific for each vocabulary.
 */
task runLoadStage(dependsOn: importData, description: project.properties['loadStage.description']) << {
    println '\n\n***********************************************'
    println '*                                             *'
    println "*        '${description}' action started...       *"
    println '*                                             *'
    println '***********************************************'

    // Start action checkpoint
    def timeStart = System.currentTimeMillis()

    def log_file = "${tempDir.getPath()}\\${project.properties['loadStage.logFileName']}"
    def stream_log_file = "${tempDir.getPath()}\\${project.properties['loadStage.streamLogFileName']}"
    def stream_error_file = "${tempDir.getPath()}\\${project.properties['loadStage.streamErrFileName']}"
    def log_file_procedure_drug = "${tempDir.getPath()}\\${project.properties['loadStage.logProcedureDrugFileName']}"
    def log_file_map_drug_vocabulary = "${tempDir.getPath()}\\${project.properties['loadStage.logMapDrugVocabularyFileName']}"

    def db = [un: project.properties['db.userName'],
              pw: project.properties['db.password'],
              tns: project.properties['db.tnsName'],
              sqlfile: "${project.properties['basePath']}\\${project.properties['loadStage.sqlFileName']}",
              logfile: log_file,
              contextPath: "${project.properties['basePath']}",
              logfileProcedureDrug: log_file_procedure_drug,
              logfileMapDrugVocabulary: log_file_map_drug_vocabulary]
    def sout = new StringBuilderWrapper(), serr = new StringBuilderWrapper()
    def sqlplus= """sqlplus.exe ${db.un}/${db.pw}@${db.tns} @${db.sqlfile} ${db.logfile} ${db.contextPath} ${db.logfileProcedureDrug} ${db.logfileMapDrugVocabulary}""".execute()
    sqlplus.consumeProcessOutput(sout, serr)
    sqlplus.waitFor()

    // Finish action checkpoint
    def timeFinish = System.currentTimeMillis()

    // Write to console
    System.out.println(sout.toString())

    // Write to logfile
    File f = new File(stream_log_file)
    f.write(sout.toString())

    // Write to error file
    f = new File(stream_error_file)
    f.write(serr.toString())

    /* Check for errors */
    if (project.properties['stop.on.error'].toBoolean() && (sqlplus.exitValue() != 0 || (serr != null && serr.toString().length() > 0))) {
        throw new GradleException(String.format(project.properties['error.message.with.code'], sqlplus.exitValue(), name))
    }

    // Measure time execution
    def timeElapsed = (timeFinish - timeStart) / 1000
    println "Time elapsed: ${timeElapsed} seconds."

    println "*** '${description}' action done ***"
}

/**
 * This task will run 'Genetic update' script.
 * It's common for all updated vocabularies and located in '\Vocabulary-v5.0\working\generic_update.sql' file.
 */
task runGenericUpdate(dependsOn: runLoadStage, description: project.properties['genericUpdate.description']) << {
    println '\n\n***********************************************'
    println '*                                             *'
    println "*     '${description}' action started...      *"
    println '*                                             *'
    println '***********************************************'

    // Start action checkpoint
    def timeStart = System.currentTimeMillis()

    def log_file = "${tempDir.getPath()}\\${project.properties['genericUpdate.logFileName']}"
    def stream_log_file = "${tempDir.getPath()}\\${project.properties['genericUpdate.streamLogFileName']}"
    def stream_error_file = "${tempDir.getPath()}\\${project.properties['genericUpdate.streamErrFileName']}"

    def db = [un: project.properties['db.userName'],
              pw: project.properties['db.password'],
              tns: project.properties['db.tnsName'],
              sqlfile: "${project.properties['basePath']}\\${project.properties['genericUpdate.sqlFileName']}",
              logfile: log_file]
    def sout = new StringBuilderWrapper(), serr = new StringBuilderWrapper()
    def sqlplus= """sqlplus.exe ${db.un}/${db.pw}@${db.tns} @${db.sqlfile} ${db.logfile}""".execute()
    sqlplus.consumeProcessOutput(sout, serr)
    sqlplus.waitFor()

    // Finish action checkpoint
    def timeFinish = System.currentTimeMillis()

    // Write to console
    System.out.println(sout.toString())

    // Write to logfile
    File f = new File(stream_log_file)
    f.write(sout.toString())

    // Write to error file
    f = new File(stream_error_file)
    f.write(serr.toString())

    /* Check for errors */
    if (project.properties['stop.on.error'].toBoolean() && (sqlplus.exitValue() != 0 || (serr != null && serr.toString().length() > 0))) {
        throw new GradleException(String.format(project.properties['error.message.with.code'], sqlplus.exitValue(), name))
    }

    // Measure time execution
    def timeElapsed = (timeFinish - timeStart) / 1000
    println "Time elapsed: ${timeElapsed} seconds."

    println "*** '${description}' action done ***"
}

/**
 * This task will run scripts which allows to see the vocabulary update results
 */
task checkUpdateResults(dependsOn: runGenericUpdate, description: project.properties['checkUpdateResults.description']) << {
    println '\n\n***********************************************'
    println '*                                             *'
    println "*  '${description}' action started...   *"
    println '*                                             *'
    println '***********************************************'

    // Start action checkpoint
    def timeStart = System.currentTimeMillis()

    def log_file = "${tempDir.getPath()}\\${project.properties['checkUpdateResults.logFileName']}"
    def stream_log_file = "${tempDir.getPath()}\\${project.properties['checkUpdateResults.streamLogFileName']}"
    def stream_error_file = "${tempDir.getPath()}\\${project.properties['checkUpdateResults.streamErrFileName']}"

    def db = [un: project.properties['db.userName'],
              pw: project.properties['db.password'],
              tns: project.properties['db.tnsName'],
              sqlfile: "${project.properties['basePath']}\\${project.properties['checkUpdateResults.sqlFileName']}",
              logfile: log_file]
    def sout = new StringBuilderWrapper(), serr = new StringBuilderWrapper()
    def sqlplus= """sqlplus.exe ${db.un}/${db.pw}@${db.tns} @${db.sqlfile} ${db.logfile}""".execute()
    sqlplus.consumeProcessOutput(sout, serr)
    sqlplus.waitFor()

    // Finish action checkpoint
    def timeFinish = System.currentTimeMillis()

    // Write to console
    System.out.println(sout.toString())

    // Write to log file
    File f = new File(stream_log_file)
    f.write(sout.toString())

    // Write to error file
    f = new File(stream_error_file)
    f.write(serr.toString())

    /* Check for errors */
    if (project.properties['stop.on.error'].toBoolean() && (sqlplus.exitValue() != 0 || (serr != null && serr.toString().length() > 0))) {
        throw new GradleException(String.format(project.properties['error.message.with.code'], sqlplus.exitValue(), name))
    }

    // Measure time execution
    def timeElapsed = (timeFinish - timeStart) / 1000
    println "Time elapsed: ${timeElapsed} seconds."

    println "*** '${description}' action done ***"
}

/**
 * Bootstrap action
 *
 * TODO: Needs to find solution to measure whole task chain execution.
 * TODO: This one via doFirst/doLast actions isn't working.
 */
task startUpdate(dependsOn: checkUpdateResults, description: project.properties['startUpdate.description']) {
    println '\n\n***********************************************'
    println '*                                             *'
    println "*      '${description}' action started...       *"
    println '*                                             *'
    println '***********************************************'

    println "Updating of '${project.properties['vocabulary.display.name']}' vocabulary..."

    // Start action checkpoint
    def timeStart = System.currentTimeMillis()

    doLast {
        // Finish action checkpoint
        def timeFinish = System.currentTimeMillis()

        // Measure time execution
        def timeElapsed = (timeFinish - timeStart) / 1000
        println "\n\nTotal time elapsed: ${timeElapsed} seconds."

        println "Updating of '${project.properties['vocabulary.display.name']}' vocabulary completed successfully..."
        println '***********************************************'
        println '*                                             *'
        println "*      '${description}' action is done...       *"
        println '*                                             *'
        println '***********************************************\n'
    }
}