/**
 * $Id$
 *
 * Copyright (C) 2006 - 2009 iba Consulting Gesellschaft mbH & Co. KG
 * All Rights Reserved.
 *
 *
 * This module containts the HaL4 (http://iba-cg.de/hal4.html) tutorial.
 * 
 * Steps of the tutorial:
 * {@orderedList 
 *  {@item showing of real world cal examples@}
 *  {@item calling of static java methods@}
 *  {@item find a file in classpath@}
 *  {@item convert a Excel file into [[String]]@}
 *  {@item parsing of rows into own data structure @}
 *  {@item implementing a instance of the show typeclass @}
 *  {@item aggregation of rows with equal coordinates@}
 *  {@item conversion of the result to xml using the XmlBuilder @}
 *  {@item outputing a string into a file using a UTF8 encoding @}
 *  {@item using the assert function to test the whole code @}
 * @}
 * 
 * To follow the tutorial read the file from top and execute the ex1 .. ex15 in ice.
 *
 */
module Ibacg.ExcelConverter;

import Cal.Core.Prelude using
    typeConstructor = Boolean, Double, Either, Int, JList, Maybe, String;
    typeClass = Eq, Ord, Outputable;
    function =
        assert, equals, error, field1, field2, fromJust, fromLeft, fromRight,
        intToString, isEmpty, isJust, isLeft, isRight, not, seq, strict,
        stringToDouble, toInt, upFrom, upFromTo;
    dataConstructor = Just, Left, Nothing, Right;
    ;

import Cal.Core.Debug using
    typeClass = Show;
    function = show;
    ;

import Cal.Utilities.XmlBuilder using
    typeConstructor = XmlNode;
    function =
        makeXmlAttribute, makeXmlAttributeWithNamespace, makeXmlDocument,
        makeXmlElement, makeXmlNamespace, xmlDocumentToString;
    ;

import Cal.Collections.List using
    function =
        all, any, concatMap, filter, groupBy, head, inputList, join, length,
        map, sort, subscript, sum, zip;
    ;

import Cal.Collections.Set using
    function = difference, fromList, toList;
    ;

import Cal.Experimental.Utilities.RegEx using
    function = makeRegEx, matches;
    ;

import Cal.IO.File using
    function = makeFileName, writeFileBinary;
    ;

import Cal.Core.String using
    function = unlines;
    ;

import Cal.Experimental.Utilities.Encoding using
    function = encode;
    ;

/*
 * --------------------------------------------------
 *  import java excel reader and classpath helper
 */
/**
 * import the static java method, notice that the result is JList which is
 * similar to List<Object> in Java.
 */
foreign unsafe import jvm "static method de.ibacg.xls.XlsReader.readSheet"
    private jReadXls :: String -> JList;

/** convert/cast JList to [[String]] using inputList and the *right type signature* */
readXlsIO :: String -> [[String]];
public readXlsIO = inputList # jReadXls;

/**
 * import the findFilenameInClassPath from Java, notice the usage of List to handle the error case.
 */
foreign unsafe import jvm "static method de.ibacg.xls.XlsReader.findFilenameInClassPath"
    private jFindFilenameInClassPath :: String -> JList;

/**
 * find the given filename in classpath
 */
findFilenameInClassPath :: String -> Maybe String;
public findFilenameInClassPath f =
    let
        input = (inputList $ jFindFilenameInClassPath f) :: [String];
    in
        if isEmpty input then
            Nothing
        else
            Just (head input)
    ;

// ICE
// :ldw hal4.cws
// :sm ExcelConverter

// error case
ex1 = findFilenameInClassPath "nothing.xls";
ex2 = findFilenameInClassPath "bookings.xls";
ex3 = readXlsIO $ fromJust $ findFilenameInClassPath "bookings.xls";
ex4 =
    showList $ readXlsIO $ fromJust $ findFilenameInClassPath "bookings.xls";

testFindFilenameInClassPath =
    assert (ex1 == Nothing) && assert (isJust ex2);

testReadXlsIO = assert (List.length ex3 == 489);

/*--------------------------------------------------
 *  Helper
 */
/** Convert a string to double and the result to an int. */
public doubleStringToInt = toInt # stringToDouble;

/** Convert a double to an account nr string. */
public doubleStringToAccount = show # doubleStringToInt;

testDoubleStringToAccount =
    assert (doubleStringToAccount "6.8E04" == "68000");

public showList = unlines # map show;
public quote s = "\"" ++ s ++ "\"";
public matchesRegEx re = matches (makeRegEx re);

testMakeRegEx =
    assert (matchesRegEx "[A-Z]*" "ABZ")
    && assert (not $ matchesRegEx "^[A-Z]*$" "A1BZ")
    ;

periodToMonthNr :: Int -> Int;
public periodToMonthNr periodeInt =
    (1 `upFromTo` 12) `subscript` ((periodeInt + 5) % 12);

testPeriodToMonthNr =
    assert (periodToMonthNr 1 == 7) && assert (periodToMonthNr 12 == 6);

public writeFileUtf8 fileName contentsString =
    let
        contents_utf8 = encode "UTF8" contentsString;
    in
        writeFileBinary fileName contents_utf8
    ;

/*--------------------------------------------------
 * Datastructure
 */

/**
 * A Booking Entry with fields. (Examples behind the :)
 *
 * Year : 2009
 *
 * Month : 1 .. 12
 *
 * CostCenter : "DE0000123"
 *
 * Account: "6300000"
 *
 * BookingType: Budget
 *
 * Value : 11231.0
 */
data Entry =
    // hidden Constructor
    private Entry
        record ::
            {
                year :: Int,
                month :: Int,
                costcenter :: String,
                account :: String,
                type :: EntryType,
                value :: Double
            }
    deriving Show, Outputable, Ord, Eq
    ;

/** Type of a booking. */
data EntryType =
    public Real |
    public Budget
    deriving Outputable, Ord, Eq
    ;

/** Implement a custom show for EntryType. */
instance Show EntryType where
    show = showEntryType;
    ;
private showEntryType et =
    case et of
    Real -> "REAL";
    Budget -> "BUDGET";
    ;

/**
 * Shorthand to get the record of an Entry
 * @arg e - the entry
 * @return the record
 */
getRecord ::
    Entry
    ->
    {
        year :: Int,
        month :: Int,
        costcenter :: String,
        account :: String,
        type :: EntryType,
        value :: Double
    };
public getRecord e = e.Entry.record;

/** Construct a entry. */
makeEntry ::
    {
        year :: Int,
        month :: Int,
        costcenter :: String,
        account :: String,
        type :: EntryType,
        value :: Double
    }
    -> Entry;

public makeEntry = Entry;

/*-------------------------
 * Excel Helper
 */

/**
 * Read a cell from a String list (row) and validate the contains again a
 * regular expression.
 *
 * @arg columnIndex - column index (Zero based)
 * @arg regex - regular expression for validate cell contains
 * @arg row - the a row of the sheet as String list
 *
 *   @return either - if validation matches then the cell contains as Right and
 *   an error message as Left otherwise
 */
readCellAndValidate :: Int -> String -> [String] -> Either String String;
public readCellAndValidate columnIndex regex !row =
    // check that column index exists in line
    if not (0 <= columnIndex && columnIndex <= length row) then
        Left
            (
                "ColumnIdx "
                ++ show columnIndex
                ++ " out of boundary "
                ++ show (length row)
                ++ " RAW: "
                ++ show row
            )
    else
        let
            cellString = row `subscript` columnIndex;
        in
            // validate contains
            if matchesRegEx regex cellString then
                Right cellString
            else
                // return the regex if no match
                Left
                $ "cellString at column index "
                    ++ intToString columnIndex
                    ++ " :"
                    ++ quote cellString
                    ++ " did not match RegEx"
                    ++ quote regex
    ;

/**
 * Build a parse validation error message.
 *
 * @arg line - orginal input row
 * @arg results - parse results
 *
 *   @return error message - joint left strings
 */
buildValidationError :: [String] -> [Either String a] -> String;
private buildValidationError line !results =
    (join "; " $ map fromLeft $ filter isLeft results)
    ++ "\n"
    ++ "input line was >>>> "
    ++ show line
    ++ " <<<<"
    ;

parseRowStrings :: EntryType -> [String] -> Either String Entry;
private parseRowStrings !type !line =
    let
        year1 = readCellAndValidate 0 "[12][0..9][0..9][0..9]" line;
        month1 = readCellAndValidate 1 "[01]?[0..9]" line;
        costcenter1 = readCellAndValidate 2 "^DE[0-9]+" line;
        account1 = readCellAndValidate 3 "[0..9]+" line;
        value1 = readCellAndValidate 4 "-?[0..9].?[0..9]*" line;
        results =
            [year1, month1, costcenter1, account1, value1]
                :: [Either String String]
            ;
    in
        if isRight `all` results then
            Right
            $ makeEntry
                {
                    year = toInt $ stringToDouble $ fromRight year1,
                    month =
                    periodToMonthNr
                    $ toInt
                    $ stringToDouble
                    $ fromRight month1,
                    costcenter = fromRight costcenter1,
                    account = doubleStringToAccount $ fromRight account1,
                    type = type,
                    value = stringToDouble $ fromRight value1
                }
        else
            Left $ buildValidationError line results
    ;

// ICE
// test parsing ex5 is bad and ex 6 is good
ex5 =
    parseRowStrings
        Budget
        ["2009.0", "11.0", "1DE0000117", "7.100101E7", "1541.0"]
    ;
ex6 =
    parseRowStrings
        Budget
        ["2009.0", "11.0", "DE0000117", "7.100101E7", "1541.0"]
    ;

testParseRowStrings =
    assert (isLeft ex5)
    && assert
        (
            fromRight ex6
            == Entry
                {
                    year = 2009,
                    month = 5,
                    account = "71001010",
                    costcenter = "DE0000117",
                    type = Budget,
                    value = 1541.0
                }
        )
    ;

// parse all rows
ex7 =
    showList
    $ map (parseRowStrings Budget)
    $ readXlsIO
    $ fromJust
    $ findFilenameInClassPath "bookings.xls"
    ;

/*--------
 *  Aggregation of values for equal coordinates.
 */

/**
 * Convert an entry record into a coordinate (year,month,costcenter,account).
 * The coordinate is used to group entries.
 */
public coord r =
    let
        line = getRecord r;
    in
        (line.year, line.month, line.costcenter, line.account)
    ;

val :: Entry -> Double;
public val e = (getRecord e).value;

eqCoord :: Entry -> Entry -> Boolean;
public eqCoord l1 l2 = coord l1 == coord l2;

sumAccounts :: [Entry] -> [Entry];
public sumAccounts =
    let
        updateCoordinateValue coord s =
            Entry {getRecord coord | value := s};
    in
        map (\b -> updateCoordinateValue (head b) (sum $ map val b))
        # groupBy eqCoord
        # sort
    ;

// test aggregation
ex8a =
    map
        (fromRight # parseRowStrings Budget)
        [
            ["2009.0", "11.0", "DE0000117", "7.100101E7", "1541.0"],
            ["2009.0", "11.0", "DE0000117", "7.100101E7", "1541.0"]
        ]
    ;

// example data before aggregation
ex8 = showList ex8a;

// example data after aggregation
ex9 = showList $ sumAccounts ex8a;

// parsed data
ex10a =
    map (fromRight # parseRowStrings Budget)
    $ readXlsIO
    $ fromJust
    $ findFilenameInClassPath "bookings.xls"
    ;

// length of parsed data
ex10 = length ex10a;

// length of aggregated parsed data
ex11 = length $ sumAccounts ex10a;

/**
 * Invariant for account aggregation.
 *
 * a) total sum before and after must be the same
 *
 * b) row count before must be greater or equal as after
 * 
 */
sumAccountsInvariant entries =
    let
        aggregatedEntries = sumAccounts entries;
        sumValues = sum # map val;
    in
        sumValues entries == sumValues aggregatedEntries
        && length entries >= length aggregatedEntries
    ;

testSumAccounts =
    // invariant
    assert (sumAccountsInvariant ex8a)
    && assert (length ex8a == 2)
    && assert (length (sumAccounts ex8a) == 1)
    && assert (sumAccountsInvariant ex10a)
    && assert (length ex10a > length (sumAccounts ex10a))
    ;

/** read xls and parse all rows */
importEntryDataFromXlsIO :: String -> [Entry];
public importEntryDataFromXlsIO filename =
    let
        startRow = 2 :: Int;
        columnCount = 5 :: Int;
        rows = readXlsIO filename;

        // add row number to rows
        numberedRows :: [(Int, [String])];
        numberedRows = zip (upFrom startRow) rows;

        wrongRows :: [(Int, [String])];
        wrongRows =
            // check that a row has 5 columns
            (not # equals columnCount # length # field2)
            `filter`
            numberedRows
            ;

        parsedRows :: [(Int, Either String Entry)];
        parsedRows =
            // convert all rows to Either errorMessage [Entry]
            map
                (\p -> (field1 p, parseRowStrings Budget (field2 p)))
                numberedRows
            ;
        buildErrorMsg pair =
            "Row: " ++ show (field1 pair) ++ "" ++ fromLeft (field2 pair);
    in
        if not $ isEmpty wrongRows then
            error $ "Rows:" ++ showList wrongRows ++ " has wrong column count"
        else if (isLeft # field2) `any` parsedRows then
            error
            $ join "\n"
            $ map buildErrorMsg
            $ filter (isLeft # field2)
            $ parsedRows
        else
            map fromRight $ map field2 parsedRows
    ;

// import, aggregate and sort all
ex12a =
    sumAccounts
    $ sort
    $ importEntryDataFromXlsIO
    $ fromJust
    $ findFilenameInClassPath "bookings.xls"
    ;

testImportEntryDataFromXlsIO = assert (length ex12a == 349);

ex12 = showList ex12a;

/*------------------
 * Build XML output
 */

/**
 * Build a xml node for the given entry.
 *
 * Example: <entry year="2009" account="71001010" costCenter="DE0000117"
 * month="5" type="BUDGET" amount="1541.0" />
 */
buildEntryXmlNode :: Entry -> XmlNode;
public buildEntryXmlNode entry =
    let
        r = getRecord entry;
        attrs =
            [
                makeXmlAttribute "year" (show r.year),
                makeXmlAttribute "account" r.account,
                makeXmlAttribute "costCenter" r.costcenter,
                makeXmlAttribute "month" (show r.month),
                makeXmlAttribute "type" (show r.type),
                makeXmlAttribute "amount" (show r.value)
            ]
            ;
    in
        makeXmlElement Nothing "entry" attrs []
    ;

ex13 =
    show
    $ (buildEntryXmlNode # fromRight # parseRowStrings Budget)
        ["2009.0", "11.0", "DE0000117", "7.100101E7", "1541.0"]
    ;

testBuildEntryXmlNode =
    assert
        (
            ex13
            == "<entry year=\"2009\" account=\"71001010\" costCenter=\"DE0000117\" month=\"5\" type=\"BUDGET\" amount=\"1541.0\" />"
        )
    ;

/** Build the root tag "data" and the document from the given xml nodes. */
private buildEntryXmlDoc nodes =
    let
        attrs =
            [
                makeXmlAttributeWithNamespace
                    (makeXmlNamespace
                        "xsi"
                        "http://www.w3.org/2001/XMLSchema-instance"
                    )
                    "noNamespaceSchemaLocation"
                    "realdata.xsd"
            ]
            ;
    in
        makeXmlDocument [makeXmlElement Nothing "data" attrs nodes]
    ;

ex14 =
    xmlDocumentToString
    $ buildEntryXmlDoc
    $ map
        (buildEntryXmlNode # fromRight # parseRowStrings Budget)
        [
            ["2009.0", "11.0", "DE0000117", "7.100101E7", "1541.0"],
            ["2009.0", "11.0", "DE0000117", "7.100101E7", "1541.0"]
        ]
    ;

public aggregatedEntriesXmlStringIO =
    xmlDocumentToString
    # buildEntryXmlDoc
    # map buildEntryXmlNode
    # importEntryDataFromXlsIO
    ;

public writeAggregatedEntriesXmlToFileIO filename =
    writeFileUtf8 (makeFileName $ filename ++ ".xml")
    $ aggregatedEntriesXmlStringIO filename
    ;

ex15 =
    writeAggregatedEntriesXmlToFileIO
    $ fromJust
    $ findFilenameInClassPath "bookings.xls"
    ;

testExcelConverterModule =
    testFindFilenameInClassPath
    && testReadXlsIO
    && testMakeRegEx
    && testPeriodToMonthNr
    && testDoubleStringToAccount
    && testParseRowStrings
    && testSumAccounts
    && testImportEntryDataFromXlsIO
    && testBuildEntryXmlNode
    ;