Sales Estimates Report Parser

reporter.groovy

/*
 * Cache for FX rates
 */
def fxCache = new HashMap()

/**
 *
 * @param fxCache cache
 * @param date date in format YYYY-MM-DD
 * @param currency iso currency code
 * @return exchange rate per one currency unit
 */
def getExchangeRateForDateAndCurrency(Map fxCache, String date, String currency) {
    if (currency == "CZK") return 1
    def key = date + "/" + currency
    def result = fxCache[key]
    if (result == null) {
        def dateChunks = date.split("-")
        def dateCzechFormat = dateChunks[2] + "." + dateChunks[1] + "." + dateChunks[0]
        ("http://www.cnb.cz/cs/financni_trhy/devizovy_trh/kurzy_devizoveho_trhu/denni_kurz.txt?date=" + dateCzechFormat).toURL().splitEachLine("\\|") {
            if (it.size() == 5) {
               if (it[2].isNumber()) {
                   def rate = it[4].replace(",",".").toBigDecimal()
                   def rateNet = rate/it[2].toBigDecimal()
                   def parsedCurrency = it[3]
                   fxCache[date + "/" + parsedCurrency] = rateNet
               }
            }
        }
        result = fxCache[key]
    }
    return result
}


// ----------------------------------------------- START OF THE SCRIPT -------------------------------------------------
if (args.length == 2) {

    println("Processing ${args[0]} and ${args[1]}...")

    // load orders from previous month --------------------------------------------------------------------------------
    def f= new File(args[1]);
    def previousMonthOrderIds = new HashSet()
    f.splitEachLine(',') {
        if (it[0].isNumber()) {
            previousMonthOrderIds << it[0]
        }
    }
    println("Number of orders from previous month: " + previousMonthOrderIds.size())

    // load orders from current month ---------------------------------------------------------------------------------
    f = new File(args[0]);

    // prepare variables for sums
    def forMeSumCZK_EU = "0.0".toBigDecimal()
    def forMeSumCZK_NonEU = "0.0".toBigDecimal()
    def vatSumCZK = "0.0".toBigDecimal()

    // process all lines of the payout report
    f.splitEachLine(',') {
        // if row is not header or some other garbage
        if (it[0].isNumber()) {
            // print order number
            print it[0]
            // if it was included in previous month then skip it
            if (previousMonthOrderIds.contains(it[0])) {
                // println(" ... skipping.")
            } else {
                // extract date and currency from CSV
                def csvDate = it[1]
                def csvCurrency = it[9]

                BigDecimal csvPrice
                BigDecimal csvVAT

                // --- nasty hack for "1, 000" --------------------------------
                if (it[10].indexOf('"') >= 0) {
                     csvPrice = ((it[10].replace('"','').toString() + (it[11].replace('"','')))).toBigDecimal()
                     csvVAT = it[12].toBigDecimal()
                } else {
                    csvPrice = it[10].toBigDecimal()
                    csvVAT = it[11].toBigDecimal()
                }
                // --- end of nasty hack for "1, 000" -------------------------

                BigDecimal csvPriceForMe = csvPrice * "0.7".toBigDecimal()
                BigDecimal csvPriceForGoogle = csvPrice * "0.3".toBigDecimal()

                def fxRate = getExchangeRateForDateAndCurrency(fxCache, csvDate, csvCurrency)
                println ", ${csvDate}, ${csvCurrency}, ${csvPrice}, ${csvVAT}, ${fxRate} .... ${csvPriceForMe*fxRate} CZK  + ${csvPriceForGoogle*fxRate} CZK + ${csvVAT * fxRate} CZK VAT)"

                if (csvVAT > 0) {
                    forMeSumCZK_EU += csvPriceForMe * fxRate
                    vatSumCZK += csvVAT * fxRate
                } else {
                    forMeSumCZK_NonEU += csvPriceForMe * fxRate
                }

            }
        }
    }

    println("----------------------------------------------------------------------------------------------------------")
    println("EU\t${forMeSumCZK_EU} CZK\t${vatSumCZK} CZK")
    println("Non EU \t${forMeSumCZK_NonEU} CZK\t 0.0 CZK")
    println("----------------------------------------------------------------------------------------------------------")
    println("Total\t${forMeSumCZK_NonEU + forMeSumCZK_EU} CZK\t ${vatSumCZK} CZK")
    println("Total\t${forMeSumCZK_NonEU + forMeSumCZK_EU + vatSumCZK} CZK")


} else {
    println("Usage: reporter.groovy <sales report> <payout report from previous month>")
}