*********************************************************************
  *
  * Sample application code which uses Nebula tools to 
  * generate Excel XML (SpreadsheetML).
  * Copyright 2007 Nebula Research and Development, All Rights Reserved
  *
  * Summary: This code is written by developers to define specific
  * XML documents which get populated with data.  The data and XML
  * definitions get passed to another program which actually creates
  * the formatted XML.  
  *
  *********************************************************************
  
  INCLUDE XXML.INC.EQU.01 ; * Standard include contain common EQUates
  OPEN 'XXML.DATA' TO F.DATA ELSE STOP 201,'XXML.DATA' ; * temp data file
  
  * Init all data passed to BUILD.XXML, the routine that generates the XML
  ALL.STYLES = ""
  MAT CELL.DATA = ""
  MAT CELL.STYLES = ""
  WORKBOOK.HEADERS = ""
  CUSTOM.HEADER = ""
  DOC = ""
  MISC = ""
  COL.A = 1 ; * These aren't required, just convenient
  COL.B = 2
  COL.C = 3
  COL.D = 4
  COL.E = 5
  COL.F = 6
  HIGH.SAMPLE = 23100 ; * just used to highlight unusually high sample data
  
  GOSUB DEFINE.STYLES
  GOSUB DEFINE.WORKBOOK.HEADERS
  GOSUB DEFINE.SHEET.HEADERS
  GOSUB DEFINE.CELL.DATA
  
  *  No Need to write out the data generated here, might help for debugging though:
  *  WRITE SHEET.DATA ON F.DATA,'MV.DATA'
  *  WRITE STYLE.DATA ON F.DATA,'MV.STYLES'
  
  * Generate the XML, document is returned in DOC, errors and other data in MISC
  CALL XXML.BUILD(ALL.STYLES, MAT CELL.DATA, MAT CELL.STYLES, WORKBOOK.HEADERS, DOC, MISC)  
  WRITE DOC ON F.DATA,'SAMPLE01.XML' ; * Excel opens this like any XLS file
  STOP
  
DEFINE.STYLES: *
  ******************************************************************
  *
  * Assign familiar names to styles.  The names can then be assigned
  * to columns, rows, and cells.
  *
  ******************************************************************
  
  ST.MAIN.HEADING = "custom01"  ; * "customXX" IDs are internal, not used in app code
  ST.HEAD.FOOT = "custom02"
  ST.SUB.TOTAL.LINE = "custom03"
  ST.HIGH.NUMBER = "custom04"
  ST.LEGEND = "custom05"
  ST.CENTERED = "custom06"
  ST.SUB.TOTAL.DATA = "custom07"
  ST.DATE.COLUMN = "custom08"
  ST.TIME.COLUMN = "custom09"
  ST.GRAND.TOTAL = "custom10" ; * fancy version of sub total style demonstrates inheritence
  ST.NORMAL = "" ; * no style for this yet, easy to change everything in one shot though
  
  ******************************************************************
  *
  * Styles have many properties including the font name and family,
  * font color and size, bold and underline settings.  The cell
  * around the text is set with interior style data.
  * All of this data can be coded in each program, saved in another
  * program item and Included by other programs, or stored in a data
  * file and read dynamically for each execution.
  *
  ******************************************************************
  
  ASTYLE = ""
  ASTYLE<1,X.STYLE.NAME> = "custom01"
  ASTYLE<1,X.STYLE.FONT.COLOR> = "#0000FF"
  ASTYLE<1,X.STYLE.FONT.SIZE> = 14
  ASTYLE<1,X.STYLE.ALIGN.H> = X.CENTER
  ASTYLE<1,X.STYLE.ALIGN.V> = X.CENTER
  ASTYLE<1,X.STYLE.INTERIOR.COLOR> = "#C0C0C0"
  ASTYLE<1,X.STYLE.INTERIOR.PATTERN> = X.SOLID ; * Not required, this is default
  ALL.STYLES<-1> = ASTYLE
  
  ASTYLE = ""
  ASTYLE<1,X.STYLE.NAME> = "custom02"
  ASTYLE<1,X.STYLE.FONT.COLOR> = "#CCFFFF"
  ASTYLE<1,X.STYLE.FONT.FAMILY> = X.SWISS
  ASTYLE<1,X.STYLE.FONT.BOLD> = 1
  ASTYLE<1,X.STYLE.INTERIOR.COLOR> = "#3366FF"
  ALL.STYLES<-1> = ASTYLE
  
  ASTYLE = ""
  ASTYLE<1,X.STYLE.NAME> = "custom03"
  ASTYLE<1,X.STYLE.FONT.FAMILY> = X.SWISS
  ASTYLE<1,X.STYLE.FONT.BOLD> = 1
  ASTYLE<1,X.STYLE.INTERIOR.COLOR> = "#00CCFF"
  ALL.STYLES<-1> = ASTYLE
  
  ASTYLE = ""
  ASTYLE<1,X.STYLE.NAME> = "custom04"
  ASTYLE<1,X.STYLE.FONT.COLOR> = "#FFFF00"
  ASTYLE<1,X.STYLE.FONT.FAMILY> = X.SWISS
  ASTYLE<1,X.STYLE.FONT.BOLD> = 1
  ASTYLE<1,X.STYLE.INTERIOR.COLOR> = "#FF0000"
  ALL.STYLES<-1> = ASTYLE
  
  ASTYLE = ""
  ASTYLE<1,X.STYLE.NAME> = "custom05"
  ASTYLE<1,X.STYLE.INTERIOR.COLOR> = "#C0C0C0"
  ASTYLE<1,X.BORDER.BOTTOM,X.LINE.STYLE> = X.DOUBLE
  ASTYLE<1,X.BORDER.BOTTOM,X.WEIGHT> = 3
  ASTYLE<1,X.BORDER.TOP,X.LINE.STYLE> = X.DOUBLE
  ASTYLE<1,X.BORDER.TOP,X.WEIGHT> = 3  
  ALL.STYLES<-1> = ASTYLE
  
  ASTYLE = ""
  ASTYLE<1,X.STYLE.NAME> = "custom06"
  ASTYLE<1,X.STYLE.ALIGN.H> = X.CENTER
  ASTYLE<1,X.STYLE.ALIGN.V> = X.BOTTOM
  ALL.STYLES<-1> = ASTYLE
  
  ASTYLE = ""
  ASTYLE<1,X.STYLE.NAME> = "custom07"
  ASTYLE<1,X.STYLE.NUMBER.FORMAT> = "#,##0"
  ASTYLE<1,X.STYLE.INTERIOR.COLOR> = "#00CCFF"
  ASTYLE<1,X.STYLE.FONT.FAMILY> = X.SWISS
  ASTYLE<1,X.STYLE.FONT.BOLD> = 1
  ALL.STYLES<-1> = ASTYLE
  SAVE.STYLE.07 = ASTYLE
  
  ASTYLE = ""
  ASTYLE<1,X.STYLE.NAME> = "custom08"
  ASTYLE<1,X.STYLE.NUMBER.FORMAT> = X.DD.MMM.YY
  ALL.STYLES<-1> = ASTYLE  
  
  ASTYLE = ""
  ASTYLE<1,X.STYLE.NAME> = "custom09"
  ASTYLE<1,X.STYLE.NUMBER.FORMAT> = X.TIME.HMS.AP
  ALL.STYLES<-1> = ASTYLE
  
  * As an example of inheritence, modify a saved style and give it a new name.
  * Changing details in custom07 will affect this style too.
  ASTYLE = SAVE.STYLE.07
  ASTYLE<1,X.STYLE.NAME> = "custom10"
  ASTYLE<1,X.BORDER.BOTTOM,X.LINE.STYLE> = X.DOUBLE
  ASTYLE<1,X.BORDER.BOTTOM,X.WEIGHT> = 3
  ASTYLE<1,X.BORDER.TOP,X.LINE.STYLE> = X.DOUBLE
  ASTYLE<1,X.BORDER.TOP,X.WEIGHT> = 3  
  ASTYLE<1,X.BORDER.LEFT,X.LINE.STYLE> = X.DOUBLE
  ASTYLE<1,X.BORDER.LEFT,X.WEIGHT> = 3
  ASTYLE<1,X.BORDER.RIGHT,X.LINE.STYLE> = X.DOUBLE
  ASTYLE<1,X.BORDER.RIGHT,X.WEIGHT> = 3  
  ALL.STYLES<-1> = ASTYLE  
  RETURN
  
DEFINE.WORKBOOK.HEADERS: *
  ******************************************************************
  *
  * Workbook headers share the var with the Sheet Headers.
  * The workbook headers must be built before the sheet headers so
  * that there is no conflict with sheet data
  *
  ******************************************************************
  
  WORKBOOK.HEADERS<X.AUTHOR> = "Joe Camel"
  WORKBOOK.HEADERS<X.AUTHOR.LAST> = "Tom Camel"
  WORKBOOK.HEADERS<X.CREATED.DATE> = DATE()
  WORKBOOK.HEADERS<X.CREATED.TIME> = TIME() - 30 ; * non-default time for testing
  WORKBOOK.HEADERS<X.COMPANY> = "Camel & Sons"
  RETURN
  
DEFINE.SHEET.HEADERS: *
  ******************************************************************
  *
  * Sheet Headers include the sheet name and width and style specs
  * for columns.
  *
  ******************************************************************
  
  WS = 1 ; * only doing one sheet for now
  WORKBOOK.HEADERS<WS,X.SHEET.NAME> = "Weekly Samples"
  WORKBOOK.HEADERS<WS,X.COLUMN.WIDTHS,COL.A> = "100px" ; * width of column 1
  WORKBOOK.HEADERS<WS,X.COLUMN.WIDTHS,COL.B> = "77px"
  WORKBOOK.HEADERS<WS,X.COLUMN.STYLES,COL.B> = ST.DATE.COLUMN
  WORKBOOK.HEADERS<WS,X.COLUMN.WIDTHS,COL.C> = "99px"
  WORKBOOK.HEADERS<WS,X.COLUMN.STYLES,COL.C> = ST.TIME.COLUMN
  WORKBOOK.HEADERS<WS,X.COLUMN.WIDTHS,COL.D> = "105px"
  WORKBOOK.HEADERS<WS,X.COLUMN.WIDTHS,COL.E> = "68px"
  RETURN
  
DEFINE.CELL.DATA: *
  ******************************************************************
  *
  * Small routine progressively builds a sheet from the top-down,
  * then adds the sheet and style info to an array.  There is
  * currently a maximum of 50 sheets per workbook - easily changed.
  *
  ******************************************************************
  
  SHEET.DATA = ""
  STYLE.DATA = ""
  ROWS = 0
  WS = 1
  GOSUB DO.HEADER
  GOSUB DO.DETAIL
  GOSUB DO.FOOTER
  CELL.DATA(WS) = SHEET.DATA
  CELL.STYLES(WS) = STYLE.DATA
  RETURN
  
DO.HEADER: *
  ******************************************************************
  *
  * Most business sheets have a main header and column headers.
  *
  ******************************************************************
  
  ROW = 1
  SHEET.DATA<COL.B,ROW> = "Periodic Sample Data by Date"
  STYLE.DATA<COL.B,ROW,X.CELL.MERGE> = 4
  STYLE.DATA<COL.B,ROW,X.CELL.STYLE> = ST.MAIN.HEADING
  * set the row height
  WORKBOOK.HEADERS<WS,X.ROW.HEIGHT,ROW> = "40px"
  
  ROW = 3
  SHEET.DATA<COL.B,ROW> = "Date"
  STYLE.DATA<COL.B,ROW,X.CELL.STYLE> = ST.HEAD.FOOT
  SHEET.DATA<COL.C,ROW> = "Time"
  STYLE.DATA<COL.C,ROW,X.CELL.STYLE> = ST.HEAD.FOOT
  SHEET.DATA<COL.D,ROW> = "Sample Code"
  STYLE.DATA<COL.D,ROW,X.CELL.STYLE> = ST.HEAD.FOOT
  SHEET.DATA<COL.E,ROW> = "Number"
  STYLE.DATA<COL.E,ROW,X.CELL.STYLE> = ST.HEAD.FOOT
  SHEET.DATA<COL.F,ROW> = "Totals"
  STYLE.DATA<COL.F,ROW,X.CELL.STYLE> = ST.HEAD.FOOT
  RETURN
  
DO.DETAIL: * 
  ******************************************************************
  *
  * This routine just loops through all items and returns when done.
  * Actual processing of each item is left to another routine.
  *
  ******************************************************************
  
  STMT = \SSELECT XXML.DATA WITH F5 = "T2"\
  EXECUTE STMT CAPTURING OUT
  EOF = 0
  LAST.DATE = ""
  START.ROW = 3
  ROW = START.ROW
  ROWS = 0
  LAST.BREAK = START.ROW
  LOOP
    READNEXT ID ELSE EOF = 1
  UNTIL EOF DO
    ROWS = ROWS + 1
    GOSUB PROCESS.RECORD
  REPEAT
  * Show sub-totals for last control group
  GOSUB PROCESS.BREAK
  RETURN
  
PROCESS.RECORD: *
  ******************************************************************
  *
  * Format each row as it's read from the database.  Special cases
  * like control breaks seem to be handled best outside of this
  * focused routine.
  *
  ******************************************************************
  
  READ REC FROM F.DATA,ID ELSE RETURN
  IF REC<1> # LAST.DATE AND LAST.DATE # "" THEN GOSUB PROCESS.BREAK
  ROW = ROW + 1
  COL = COL.B
  IF REC<1> # LAST.DATE THEN
    SHEET.DATA<COL,ROW> = REC<1>
    STYLE.DATA<COL,ROW,X.DATA.STYLE> = X.DATE
    STYLE.DATA<COL,ROW,X.CELL.STYLE> = ST.NORMAL
    LAST.DATE = REC<1>
  END
  
  COL = COL.C
  SHEET.DATA<COL,ROW> = REC<2>
  STYLE.DATA<COL,ROW,X.DATA.STYLE> = X.TIME
  STYLE.DATA<COL,ROW,X.CELL.STYLE> = ST.NORMAL
  
  COL = COL.D
  SHEET.DATA<COL,ROW> = REC<3>
  STYLE.DATA<COL,ROW,X.CELL.STYLE> = ST.CENTERED
  
  COL = COL.E
  SHEET.DATA<COL,ROW> = REC<4>
  STYLE.DATA<COL,ROW,X.DATA.STYLE> = X.NUMBER
  STYLE.DATA<COL,ROW,X.CELL.STYLE> = ST.NORMAL
  IF REC<4> > 23100 THEN
    STYLE.DATA<COL,ROW,X.CELL.STYLE> = ST.HIGH.NUMBER
  END
  RETURN
  
PROCESS.BREAK: *
  ******************************************************************
  *
  * Create a new row with special text and styles to profile a
  * control break.  Dynamically calculate the formula required to
  * produce a total.  Static sheets can use hard-coded totals but
  * if users are going to manipulate the data they will want to see
  * totals adjusted with formulas.  Note that the forumla cannot
  * be in format $R$C but must be in RxCy.  Relative references are
  * accomplished as R[-x]C[y].
  *
  ******************************************************************
  
  ROW = ROW + 1
  ROWS = ROWS + 1
  SHEET.DATA<COL.A,ROW> = "Sub-Total Date" ; * would be nice to show date
  WORKBOOK.HEADERS<WS,X.ROW.STYLE,ROW> = ST.SUB.TOTAL.LINE
  FROM.HERE = LAST.BREAK+1
  TO.HERE = ROW-1
  * split up building formula to avoid confusion
  FORMULA = "=SUM("
  FORMULA = FORMULA : "R": FROM.HERE :"C":COL.E
  FORMULA = FORMULA :":"
  FORMULA = FORMULA : "R": TO.HERE   :"C":COL.E
  FORMULA = FORMULA :")"
  SHEET.DATA<COL.F,ROW> = FORMULA
  STYLE.DATA<COL.F,ROW,X.CELL.STYLE> = ST.SUB.TOTAL.DATA
  STYLE.DATA<COL.F,ROW,X.DATA.STYLE> = X.NUMBER
  
  RETURN
  
DO.FOOTER: *
  ******************************************************************
  *
  * When all detail has been processed, this routine will generate
  * forumulas for totals at the bottom of the sheet or anywhere
  * else.  Even though this is a footer, we can still change any
  * cell in the current sheet or any sheet in the workbook.
  *
  ******************************************************************
  
  * Add an extra row at bottom to show "Totals ... ####"
  
  ROW = START.ROW + ROWS + 1
  SHEET.DATA<COL.A,ROW> = "Totals"
  STYLE.DATA<COL.A,ROW,X.CELL.STYLE> = ST.HEAD.FOOT
  * Note this formula wasn't broken up like the one above and could be more cryptic
  IF ROW <= (START.ROW+1) THEN
    FORMULA = 0
  END ELSE
    FORMULA = "=SUM(R":(START.ROW+1):"C":COL.F:":R":(ROW-1):"C":COL.F:")"
  END
  SHEET.DATA<COL.F,ROW> = FORMULA
  STYLE.DATA<COL.F,ROW,X.CELL.STYLE> = ST.GRAND.TOTAL
  STYLE.DATA<COL.F,ROW,X.DATA.STYLE> = X.NUMBER
  
  * Add final row(s) with additional message(s)
  LAST.ROW = ROW + 1
  MESSAGES = "Red indicates sample is unusually high, over ":HIGH.SAMPLE
  FOR M.ATB = 1 TO DCOUNT(MESSAGES,@AM)
    ROW = LAST.ROW + M.ATB
    WORKBOOK.HEADERS<WS,X.ROW.STYLE,ROW> = ST.LEGEND
    SHEET.DATA<COL.A,ROW> = MESSAGES<M.ATB>
  NEXT M.ATB
  
  RETURN
  
  END