/*
*  File: xmlExcelWriter.cls
*  Description: Writes an XML Excel WorkSheet
*  Author: Hugo Alberto Lujan Chavez
*  Created: 2009/Feb/04
*/
CLASS XML.xmlExcelWriter:

    /* --- Data Members --- */
    /* Temp Table with the Field names and labels for the first row
       of the Excel Worksheet */    
    DEFINE PRIVATE TEMP-TABLE ttXMLReference NO-UNDO
        FIELD FieldName   AS CHARACTER
        FIELD FieldLabel  AS CHARACTER
        FIELD FieldFormat AS CHARACTER.

    /* The number of rows in the XML Excel Worksheet */
    DEFINE PRIVATE VARIABLE viRowCount AS INTEGER NO-UNDO.
    
    /* The number of columns in the XML Excel Worksheet */    
    DEFINE PRIVATE VARIABLE viColumnCount AS INTEGER NO-UNDO.
    /* XML Work variables */
    DEFINE PRIVATE VARIABLE hXdoc       AS HANDLE NO-UNDO.
    DEFINE PRIVATE VARIABLE hxInstru    AS HANDLE NO-UNDO.
    DEFINE PRIVATE VARIABLE hxRoot      AS HANDLE NO-UNDO.
    DEFINE PRIVATE VARIABLE hxHeader    AS HANDLE NO-UNDO.
    DEFINE PRIVATE VARIABLE hxWorksheet AS HANDLE NO-UNDO.
    DEFINE PRIVATE VARIABLE hxTable     AS HANDLE NO-UNDO.
    DEFINE PRIVATE VARIABLE hxRow       AS HANDLE NO-UNDO.
    DEFINE PRIVATE VARIABLE hxCell      AS HANDLE NO-UNDO.
    DEFINE PRIVATE VARIABLE hxData      AS HANDLE NO-UNDO.
    DEFINE PRIVATE VARIABLE hxValue     AS HANDLE NO-UNDO.
    DEFINE PRIVATE VARIABLE hxTMP       AS HANDLE NO-UNDO.

/* --- Constructor --- */
CONSTRUCTOR PUBLIC xmlExcelWriter():    
    ASSIGN
        viColumnCount = 0.
    
    CATCH eSysError AS Progress.Lang.SysError:
        MESSAGE eSysError:GetMessage(1) VIEW-AS ALERT-BOX BUTTONS OK.
        DELETE OBJECT eSysError.
    END CATCH.
END CONSTRUCTOR.
/* --- Destructor --- */
DESTRUCTOR PUBLIC xmlExcelWriter():
    EMPTY TEMP-TABLE ttXMLReference.
    
    CATCH eSysError AS Progress.Lang.SysError:
        MESSAGE eSysError:GetMessage(1) VIEW-AS ALERT-BOX BUTTONS OK.
        DELETE OBJECT eSysError.
    END CATCH.
END DESTRUCTOR.

/* Methods ---                                                          */
METHOD PUBLIC VOID AddReferenceRow(
    INPUT ipcFieldName   AS CHARACTER,
    INPUT ipcFieldLabel  AS CHARACTER,
    INPUT ipcFieldFormat AS CHARACTER):
    CASE TRUE:
        WHEN ipcFieldName EQ ?  OR
        WHEN ipcFieldLabel EQ ? OR
        WHEN TRIM(ipcFieldName) EQ "" OR
        WHEN TRIM(ipcFieldLabel) EQ "" THEN
        DO:
            RETURN ERROR "Empty parameters received".
        END.
    END CASE.
    CREATE ttXMLReference.
    ASSIGN ttXMLReference.FieldName   = ipcFieldName
           ttXMLReference.FieldLabel  = ipcFieldLabel
           ttXMLReference.FieldFormat = ipcFieldFormat.
    ASSIGN 
        viColumnCount = viColumnCount + 1.

    CATCH eSysError AS Progress.Lang.SysError:
        MESSAGE eSysError:GetMessage(1) VIEW-AS ALERT-BOX BUTTONS OK.
        DELETE OBJECT eSysError.
    END CATCH.
END METHOD.

METHOD PUBLIC VOID WriteXML(
    /* Filename to generate include .XML appended at the end */
    INPUT ipcFileName AS CHARACTER,
    /* Name to use in The SpreadSheet tab */
    INPUT ipcSSName AS CHARACTER,
/* Handle to the Temp-Table with the data to be written to the XML
   Excel Worksheet */    
    INPUT iphTempTable AS HANDLE): 
DEFINE VARIABLE vcColumnLabel AS CHARACTER NO-UNDO.
DEFINE VARIABLE vcColumnName  AS CHARACTER NO-UNDO.
DEFINE VARIABLE vhField  AS HANDLE.
DEFINE VARIABLE vhBuffer AS HANDLE.
DEFINE VARIABLE vhQuery  AS HANDLE.
DEFINE VARIABLE vcTempTableName AS CHARACTER NO-UNDO.
DEFINE VARIABLE vlttXMLReferencePresent AS LOGICAL NO-UNDO.
DEFINE VARIABLE viRow AS INTEGER NO-UNDO.
DEFINE VARIABLE viField AS INTEGER NO-UNDO.

    ASSIGN
        vlttXMLReferencePresent = TRUE.    
    IF NOT CAN-FIND(FIRST ttXMLReference) THEN
    DO:
        /* Empty XML Reference table received */
        ASSIGN            
            vlttXMLReferencePresent = FALSE.
    END.
    IF VALID-HANDLE(iphTempTable) THEN
    DO:
        IF NOT iphTempTable:HAS-RECORDS THEN
            RETURN ERROR "Empty Temp-table received".
        ASSIGN
            /* get the name of the the temp-table */
            vcTempTableName = iphTempTable:NAME
            /* get the buffer handle for the temp-table */
            vhBuffer = iphTempTable:DEFAULT-BUFFER-HANDLE        
            viRowCount = 1 + GetRowCount(iphTempTable).
        IF NOT vlttXMLReferencePresent THEN
            ASSIGN
                viColumnCount = vhBuffer:NUM-FIELDS.
    END.
    ELSE
        RETURN ERROR "Empty Temp-table received".

    CREATE X-DOCUMENT hXdoc.

    /* Create X-noderef object to represent the root node */
    CREATE X-NODEREF hxRoot.
    
    /* Create other X-noderef objects */    
    CREATE X-NODEREF hxHeader.
    CREATE X-NODEREF hxWorksheet.
    CREATE X-NODEREF hxTable.
    CREATE X-NODEREF hxRow.
    CREATE X-NODEREF hxCell.
    CREATE X-NODEREF hxData.
    CREATE X-NODEREF hxValue.
    CREATE X-NODEREF hxInstru.
    CREATE X-NODEREF hxTMP.
    
    /*set up a node*/
    hXdoc:CREATE-NODE(hxInstru,"mso-application","PROCESSING-INSTRUCTION").
    
    /* After the node is created, you must append it to its parent */
    hxDoc:APPEND-CHILD(hxInstru).
    /* Set PROCESSING-INSTRUCTION attributes */
    hxInstru:NODE-VALUE = 'progid="Excel.Sheet"'.
        
    /*set up a root node*/
    hXdoc:CREATE-NODE(hxRoot,"Workbook","ELEMENT").
    hxDoc:APPEND-CHILD(hxRoot).
    
    /* Set Workbook attributes */
    hxRoot:SET-ATTRIBUTE("xmlns","urn:schemas-microsoft-com:office:spreadsheet").
    hxRoot:SET-ATTRIBUTE("xmlns:o","urn:schemas-microsoft-com:office:office").
    hxRoot:SET-ATTRIBUTE("xmlns:x","urn:schemas-microsoft-com:office:excel").
    hxRoot:SET-ATTRIBUTE("xmlns:ss","urn:schemas-microsoft-com:office:spreadsheet").
    hxRoot:SET-ATTRIBUTE("xmlns:html","http://www.w3.org/TR/REC-html40").
    
    /*set up a header node*/
    hXdoc:CREATE-NODE(hxHeader,"DocumentProperties","ELEMENT":U).    
    hxRoot:APPEND-CHILD(hxHeader).
    
    /* Set DocumentProperties attributes */    
    hxHeader:SET-ATTRIBUTE("xmlns","urn:schemas-microsoft-com:office:office").
    /*---Styles--*/
    /*set up a header node*/
    hXdoc:CREATE-NODE(hxHeader,"Styles","ELEMENT":U).    
    hxRoot:APPEND-CHILD(hxHeader).
    
    /*set up a header node*/
    hXdoc:CREATE-NODE(hxValue,"Style","ELEMENT":U).    
    hxHeader:APPEND-CHILD(hxValue).
    
    /* Set DocumentProperties attributes */    
    hxValue:SET-ATTRIBUTE("ss:ID","s21").    
    
    /*set up a node for Value */
    hxDoc:CREATE-NODE(hxTMP, "Font", "ELEMENT":U).
    
    /* attach the value to the Data */
    hxValue:APPEND-CHILD(hxTMP).
    hxTMP:SET-ATTRIBUTE("ss:Bold","1").
    
    /*---Styles--*/
    /*set up a node for Worksheet */
    hXdoc:CREATE-NODE(hxWorksheet,"Worksheet","ELEMENT":U).
    hxRoot:APPEND-CHILD(hxWorksheet).

    /* Set Worksheet attributes */
    hxWorksheet:SET-ATTRIBUTE("ss:Name",ipcSSName).
    
    /*set up a node for table */
    hXdoc:CREATE-NODE(hxTable,"Table","ELEMENT":U).
    hxWorksheet:APPEND-CHILD(hxTable).
    
    /* Set Table attributes */
    hxTable:SET-ATTRIBUTE("ss:ExpandedColumnCount",STRING(viColumnCount)).
    hxTable:SET-ATTRIBUTE("ss:ExpandedRowCount",STRING(viRowCount)).
    hxTable:SET-ATTRIBUTE("x:FullColumns","1").
    hxTable:SET-ATTRIBUTE("x:FullRows","1").

    /*set up a node for Header Row */
    hXdoc:CREATE-NODE(hxRow,"Row","ELEMENT":U).
    hxTable:APPEND-CHILD(hxRow).    
    IF vlttXMLReferencePresent THEN
    DO: /* If a reference Table is present */
        FOR EACH ttXMLReference:
            /*set up a node for Cell */
            hXdoc:CREATE-NODE(hxCell,"Cell","ELEMENT":U).
            hxRow:APPEND-CHILD(hxCell).
        
            /* Set Cell attributes BOLD */ 
            hxCell:SET-ATTRIBUTE("ss:StyleID","s21":U).
        
            /*set up a node for Data */
            hXdoc:CREATE-NODE(hxData,"Data","ELEMENT":U).
            hxCell:APPEND-CHILD(hxData).
        
            /* Set Data attributes */
            hxData:SET-ATTRIBUTE("ss:Type","String":U).
        
            /*set up a node for Value */
            hxDoc:CREATE-NODE(hxValue, "", "TEXT":U).

            /* attach the value to the Data */
            hxData:APPEND-CHILD(hxValue).
            hxValue:NODE-VALUE = ttXMLReference.FieldLabel.
        END. /* FOR EACH ttXMLReference */
        /* run a query to access the temp-table */
        CREATE QUERY vhQuery.
        vhQuery:SET-BUFFERS(vhBuffer).
        vhQuery:QUERY-PREPARE("FOR EACH " + vcTempTableName).
        vhQuery:QUERY-OPEN().
        REPEAT:
            vhQuery:GET-NEXT().    
            IF vhQuery:QUERY-OFF-END THEN
                LEAVE.
            /*set up a node for a new Row */
            hXdoc:CREATE-NODE(hxRow,"Row","ELEMENT":U).
            hxTable:APPEND-CHILD(hxRow).
            FOR EACH ttXMLReference:
                ASSIGN
                    vhField = vhBuffer:BUFFER-FIELD(ttXMLReference.FieldName).
                /*set up a node for Cell */
                hXdoc:CREATE-NODE(hxCell,"Cell":U,"ELEMENT":U).
                hxRow:APPEND-CHILD(hxCell).
            
                /*set up a node for Data */
                hXdoc:CREATE-NODE(hxData,"Data":U,"ELEMENT":U).
                hxCell:APPEND-CHILD(hxData).
            
                /* Set Data attributes */
                hxData:SET-ATTRIBUTE("ss:Type":U,"String":U).
                
                /*set up a node for Value */
                hxDoc:CREATE-NODE(hxValue, "", "TEXT":U).
    
                /* attach the value to the Data */
                hxData:APPEND-CHILD(hxValue).
                IF ttXMLReference.FieldFormat NE ? OR TRIM(ttXMLReference.FieldFormat) NE "" THEN
                    hxValue:NODE-VALUE = STRING(vhField:BUFFER-VALUE(),ttXMLReference.FieldFormat).
                ELSE
                    hxValue:NODE-VALUE = STRING(vhField:BUFFER-VALUE()).
            END. /* FOR EACH ttXMLReference */
        END. /* REPEAT */
    END.        
    ELSE
    DO: /* If a reference Table is not present uses the temp-table native information */
        ASSIGN 
            viRow = 1.
        /* run a query to access the temp-table */
        CREATE QUERY vhQuery.
        vhQuery:SET-BUFFERS(vhBuffer).
        vhQuery:QUERY-PREPARE("FOR EACH " + vcTempTableName).
        vhQuery:QUERY-OPEN().
        REPEAT:
            vhQuery:GET-NEXT().    
            IF vhQuery:QUERY-OFF-END THEN
                LEAVE.
            IF viRow EQ 1 THEN
            DO: /* Creates the first Row or the Header of the WorkSheet */
                DO viField = 1 TO vhBuffer:NUM-FIELDS:
                    ASSIGN
                        vhField = vhBuffer:BUFFER-FIELD(viField).
                    /*set up a node for Cell */
                    hXdoc:CREATE-NODE(hxCell,"Cell","ELEMENT":U).
                    hxRow:APPEND-CHILD(hxCell).
                
                    /* Set Cell attributes BOLD */ 
                    hxCell:SET-ATTRIBUTE("ss:StyleID","s21":U).
                
                    /*set up a node for Data */
                    hXdoc:CREATE-NODE(hxData,"Data","ELEMENT":U).
                    hxCell:APPEND-CHILD(hxData).
                
                    /* Set Data attributes */
                    hxData:SET-ATTRIBUTE("ss:Type","String":U).
                    
                    /*set up a node for Value */
                    hxDoc:CREATE-NODE(hxValue, "", "TEXT":U).
        
                    /* attach the value to the Data */
                    hxData:APPEND-CHILD(hxValue).
                    hxValue:NODE-VALUE = vhField:NAME.                    
                END. /* DO viField = 1 TO vhBuffer(NUM-FIELDS) */
            END.
            /*set up a node for a new Row */
            hXdoc:CREATE-NODE(hxRow,"Row","ELEMENT":U).
            hxTable:APPEND-CHILD(hxRow).
            DO viField = 1 TO vhBuffer:NUM-FIELDS:
                ASSIGN
                    vhField = vhBuffer:BUFFER-FIELD(viField).
                /*set up a node for Cell */
                hXdoc:CREATE-NODE(hxCell,"Cell":U,"ELEMENT":U).
                hxRow:APPEND-CHILD(hxCell).
            
                /*set up a node for Data */
                hXdoc:CREATE-NODE(hxData,"Data":U,"ELEMENT":U).
                hxCell:APPEND-CHILD(hxData).
            
                /* Set Data attributes */
                hxData:SET-ATTRIBUTE("ss:Type":U,"String":U).
                
                /*set up a node for Value */
                hxDoc:CREATE-NODE(hxValue, "", "TEXT":U).
    
                /* attach the value to the Data */
                hxData:APPEND-CHILD(hxValue).
                IF vhField:FORMAT NE ? OR TRIM(vhField:FORMAT) NE "" THEN
                    hxValue:NODE-VALUE = STRING(vhField:BUFFER-VALUE(),vhField:FORMAT).
                ELSE
                    hxValue:NODE-VALUE = STRING(vhField:BUFFER-VALUE()).
            END. /* DO viField = 1 TO vhBuffer(NUM-FIELDS) */            
            ASSIGN 
                viRow = viRow + 1.
        END. /* REPEAT */
    END. /* No Reference table */
    /*write the XML node tree to an xml file*/
    hxDoc:SAVE("file":U,ipcFileName).
    /* Delete used objects */
    DELETE OBJECT hxDoc.
    DELETE OBJECT hxInstru.
    DELETE OBJECT hxRoot.
    DELETE OBJECT hxHeader.
    DELETE OBJECT hxWorksheet.
    DELETE OBJECT hxTable.
    DELETE OBJECT hxRow.
    DELETE OBJECT hxCell.
    DELETE OBJECT hxData.
    DELETE OBJECT hxValue.
    DELETE OBJECT hxTMP.

    CATCH eSysError AS Progress.Lang.SysError:
        MESSAGE eSysError:GetMessage(1) VIEW-AS ALERT-BOX BUTTONS OK.
        DELETE OBJECT eSysError.
    END CATCH.
END METHOD.
METHOD PRIVATE INTEGER GetRowCount(
    INPUT iphTempTable AS HANDLE):
DEFINE VARIABLE vhBuffer AS HANDLE.
DEFINE VARIABLE vhQuery AS HANDLE.
DEFINE VARIABLE vcTempTableName AS CHARACTER NO-UNDO.
/* The number of rows in the TempTable */
DEFINE VARIABLE vittRowCount AS INTEGER NO-UNDO.
DEFINE VARIABLE vcQueryText AS CHARACTER NO-UNDO.
DEFINE VARIABLE vhField  AS HANDLE.
DEFINE VARIABLE vcFieldName AS CHARACTER NO-UNDO.
DEFINE VARIABLE vcFieldValue AS CHARACTER NO-UNDO.
DEFINE VARIABLE viQResultNum AS INTEGER NO-UNDO.

FOR EACH iphTempTable:DEFAULT-BUFFER-HANDLE:
END.
    ASSIGN
        /* get the name of the the temp-table */
        vcTempTableName = iphTempTable:NAME
        /* get the buffer handle for the temp-table */
        vhBuffer = iphTempTable:DEFAULT-BUFFER-HANDLE
        vittRowCount = 0
        vcQueryText = "FOR EACH " + vcTempTableName.

    /* run a query to access the temp-table */
    CREATE QUERY vhQuery.
    vhQuery:SET-BUFFERS(vhBuffer).
    vhQuery:QUERY-PREPARE(vcQueryText).
    vhQuery:QUERY-OPEN().
    ASSIGN 
        viQResultNum = vhQuery:NUM-RESULTS.
    MESSAGE viQResultNum VIEW-AS ALERT-BOX.
    REPEAT:
        vhQuery:GET-NEXT().    
        IF vhQuery:QUERY-OFF-END THEN
            LEAVE.
        ASSIGN
            vhField = vhBuffer:BUFFER-FIELD(1)
            vcFieldName = vhField:NAME
            vcFieldValue = STRING(vhField:BUFFER-VALUE())
            vittRowCount = vittRowCount + 1.
    END.
    vhQuery:QUERY-CLOSE().
    vhBuffer:BUFFER-RELEASE().
    DELETE OBJECT vhQuery.
    RETURN vittRowCount.

    CATCH eSysError AS Progress.Lang.SysError:
        MESSAGE eSysError:GetMessage(1) VIEW-AS ALERT-BOX BUTTONS OK.
        DELETE OBJECT eSysError.
    END CATCH.
END METHOD.
END CLASS.
