 
"""
    MoinMoin - sstable a Processor for spread sheet calculations using only Python.

    @license: GNU GPL, see COPYING for details.

    PURPOSE:
        This processor is used to do some spread sheet calculation in a regular wiki
    table using only Python. The first column/first line coordinate is A0.

    This code is based on the spreadsheet code posted by Raymond Hettinger and
    Richard Copeland at http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/355045
    It is also based on the sctable parser by Reimar Bauer (R.Bauer AT fz-juelich.de).

    CALLING SEQUENCE:
       {{{
       #!sstable [-column_header, -row_header, -show_formular, -format,
                  -input_separator, -output_separator ]
       }}}

    OPTIONAL INPUTS:
       -column_header: additional in the result the column header is shown
       -row_header: additional in the result the line number header is shown
       -show_formular: if set the formular instead of the result is shown,
                       data is arranged in textmode. Blanks in formulars are removed
       -format: is used to the set the number of digits for the column values
       -input_separator: used to read tables delimitted by something other than the 
                         default '||'
       -output_separator: used to write tables delimitted by something other than the 
                          default '||'


    EXAMPLE:
{{{
SUM over columns}}}
{{{
#!sstable
||1||2||=A0+B0||
||10||20||=@sum(A1:B1)||
}}}

RESULT:
||<)>1.00||<)>2.00||<)>3.00||
||<)>10.00||<)>20.00||<)>30.00||
-----

{{{
cell B1 no data}}}

{{{
#!sstable
||A||B||C||D||
||1||||2||=A1+C1||
}}}

RESULT:
||<(>A||<(>B||<(>C||<(>D||
||<)>1.00||<)>||<)>2.00||<)>3.00||

-----
{{{
SUM over rows}}}
{{{
#!sstable
||1||2||=A0+B0||
||10||20||30||
||=@sum(A0:A1)||=@sum(B0:B1)||=@sum(C0:C1)||
}}}

RESULT:
||<)>1.00||<)>2.00||<)>3.00||
||<)>10.00||<)>20.00||<)>30.00||
||<)>11.00||<)>22.00||<)>33.00||

-----
{{{
SUM over rows and columns}}}
{{{
#!sstable
||A||B||C||
||1||2||=A1+B1||
||10||20||=@sum(A2:B2)||
||=@sum(A1:A2)||=@sum(B1:B2)||=@sum(C1:C2)||
}}}

RESULT:
||<(>A||<(>B||<(>C||
||<)>1.00||<)>2.00||<)>3.00||
||<)>10.00||<)>20.00||<)>30.00||
||<)>11.00||<)>22.00||<)>33.00||

-----
{{{
-column_header}}}
{{{
#!sstable  -column_header
||1||2||
||3||4||
||5||6||
}}}

RESULT:
||<:#CCCCCC>'''A'''||<:#CCCCCC>'''B'''||
||<)>1.00||<)>2.00||
||<)>3.00||<)>4.00||
||<)>5.00||<)>6.00||


-----
{{{
-row_header}}}
{{{
#!sstable -row_header
||1||2||
||3||4||
||5||6||
}}}

RESULT:
||<)5%#CCCCCC>'''0'''||<)>1.00||<)>2.00||
||<)5%#CCCCCC>'''1'''||<)>3.00||<)>4.00||
||<)5%#CCCCCC>'''2'''||<)>5.00||<)>6.00||

-----
{{{
-column_header  -row_header}}}
{{{
#!sstable  -column_header  -row_header
||1||2||
||3||4||
||5||6||
}}}

RESULT:
||<:5%#CCCCCC> ||<:#CCCCCC>'''A'''||<:#CCCCCC>'''B'''||
||<)5%#CCCCCC>'''0'''||<)>1.00||<)>2.00||
||<)5%#CCCCCC>'''1'''||<)>3.00||<)>4.00||
||<)5%#CCCCCC>'''2'''||<)>5.00||<)>6.00||

-----
{{{
-show_formular  -column_header  -row_header}}}
{{{
#!sstable -show_formular  -column_header  -row_header
||m||p||
||1||=A1 * 5||
||2||=A2-3||
||3||4||
}}}

RESULT:
||<:5%#CCCCCC> ||<:#CCCCCC>'''A'''||<:#CCCCCC>'''B'''||
||<)5%#CCCCCC>'''0'''||<(>m||<(>p||
||<)5%#CCCCCC>'''1'''||<(>1||<(>=A1*5||
||<)5%#CCCCCC>'''2'''||<(>2||<(>=A2-3||
||<)5%#CCCCCC>'''3'''||<(>3||<(>4||

-----
{{{
-column_header and blanks in cells}}}
{{{
#!sstable -column_header
||Name Vorname||  ||  || 3 || || 5||
||Name Vorname|| 1 || 2 ||  || 4 || 5||
||Name Vorname|| 1 || 2 || || || 5||
}}}

RESULT:
||<:#CCCCCC>'''A'''||<:#CCCCCC>'''B'''||<:#CCCCCC>'''C'''||<:#CCCCCC>'''D'''||<:#CCCCCC>'''E'''||<:#CCCCCC>'''F'''||
||<(>Name Vorname||    ||    ||<)>3.00||    ||<)>5.00||
||<(>Name Vorname||<)>1.00||<)>2.00||    ||<)>4.00||<)>5.00||
||<(>Name Vorname||<)>1.00||<)>2.00||    ||    ||<)>5.00||

-----
{{{
-format 1,1}}}
{{{
#!sstable -format 1,1
||1||2||
||3||4||
||=@sum(a0:a1)||=a2*4||
}}}

RESULT:
||<)>1.0||<)>2.0||
||<)>3.0||<)>4.0||
||<)>4.0||<)>16.0||

-----
{{{ useage of variable names -show_formular -column_header  -row_header
}}}
{{{#!sstable -show_formular -column_header  -row_header
||A||B||C||
||1||{two}2||=A1+two||
||10||20||=@sum(A2:B2)||
||=@sum(A1:A2)||=@sum(B1:B2)||=@sum(C1:C2)||
}}}


RESULT:
||<:5%#CCCCCC> ||<:#CCCCCC>'''A'''||<:#CCCCCC>'''B'''||<:#CCCCCC>'''C'''||
||<)5%#CCCCCC>'''0'''||<(>A||<(>B||<(>C||
||<)5%#CCCCCC>'''1'''||<(>1||<(>{two}2||<(>=A1+two||
||<)5%#CCCCCC>'''2'''||<(>10||<(>20||<(>=@sum(A2:B2)||
||<)5%#CCCCCC>'''3'''||<(>=@sum(A1:A2)||<(>=@sum(B1:B2)||<(>=@sum(C1:C2)||

and if we calculate [[BR]]
RESULT:
{{{#!sstable -column_header  -row_header
||A||B||C||
||1||{two}2||=A1+two||
||10||20||=@sum(A2:B2)||
||=@sum(A1:A2)||=@sum(B1:B2)||=@sum(C1:C2)||
}}}
-----
{{{color in cells
}}}
{{{#!sstable
||<:rowbgcolor=lightcyan>'''A'''||<:>'''B'''||<:>'''C'''||
||<)#dddddd>1||<)#dddddd>{two}2||<)#cccccc>=A1+two||
||<(>10||<)>20||<:#dddddd>=@sum(A2:B2)||
||<rowbgcolor="#cc99ff">=@sum(A1:A2)||=@sum(B1:B2)||<bgcolor=magenta>=@sum(C1:C2)||
}}}

RESULT:
||<:rowbgcolor=lightcyan>'''A'''||<:>'''B'''||<:>'''C'''||
||<)#dddddd>1.00||<)#dddddd>2.00||<)#cccccc>3.00||
||<(>10.00||<)>20.00||<:#dddddd>30.00||
||<rowbgcolor="#cc99ff">11.00||22.00||<bgcolor=magenta>33.00||
-----
{{{delimit input with ','
}}}
{{{
#!sstable -column_header -input_separator ,
,1,2,
,3,4,
,5,6,
}}}

RESULT:
||<:#CCCCCC>'''A'''||<:#CCCCCC>'''B'''||
||<)>1.00||<)>2.00||
||<)>3.00||<)>4.00||
||<)>5.00||<)>6.00||
-----
{{{delimit input and output with ','
}}}
{{{
#!sstable -column_header -row_header -input_separator , -output_separator ,
1,2
3,4
5,6
}}}
      
RESULT:
{{{
,''' ''','''A''','''B''',
,'''0''', 1.00, 2.00,
,'''1''', 3.00, 4.00,
,'''2''', 5.00, 6.00,
}}}
-----



    PROCEDURE:
      All formulars have to start by a "=" sign.
      All formulars do not need to start with a "@", but they can.

      Please remove the version number from the routine name!


    MODIFICATION:
       @copyright: 2006-03-25 by Andrew Shewmaker (agshew AT gmail) sstable-1.0
                   2008 MoinMoin:ReimarBauer (slightly changed for 1.7.1)

        1.0.4: improved regular expression that detects numbers - Andrew Shewmaker
               moved formula parsing into SpreadSheet class - Andrew Shewmaker
               return 0 when eval results in type error in SpreadSheet class - Andrew Shewmaker
        1.0.3: improved compatibility with other spreadsheets - Andrew Shewmaker
        1.0.2: improved cell range functions - Andrew Shewmaker
               correct special character handling - Reimar Bauer
        1.0.1: use unicode function - Andrew Shewmaker
        1.0: based on sctable-1.5.2-5 by Reimar Bauer (R.Bauer AT fz-juelich.de)
"""

Dependencies = []
import re
from math import *
from MoinMoin.parser import text_moin_wiki as wiki
from MoinMoin.action import AttachFile
from MoinMoin.Page import Page
from MoinMoin import wikiutil

class SpreadSheet:
    _cells = {}
    tools = {}
    _cache = None
    def __init__(self):
      self.eval_re = re.compile('^=')
      self.num_re = re.compile('^-?(\d*)\.?[\d]+$')
      self.range_re = re.compile('(\w+).(\w+:\w+).')
      self.var_re = re.compile('\{(.+)\}(.+)')
      self.tic_re = re.compile("^'(.?)'?")
      self.tools.update({'__builtins__':None})
      pmath = {'ceil': ceil, 'floor': floor,
               'fabs': fabs,
               'fmod': fmod, 'modf': modf,
               'frexp': frexp, 'ldexp': ldexp,
               'exp': exp,
               'log': log, 'log10': log10,
               'pow': pow,
               'sqrt': sqrt,
               'acos': acos, 'asin': asin, 'atan': atan, 'atan2': atan2,
               'cos': cos, 'sin': sin, 'tan': tan,
               'cosh': cosh, 'sinh': sinh, 'tanh': tanh,
               'hypot': hypot,
               'degrees': degrees, 'radians': radians,
               'pi': pi, 'e': e,
               'cmp': cmp,
               'len': len,
               'round': round,
               'hex': hex, 'oct': oct,
               'max': self.max, 'min': self.min,
               'sum': self.sum, 'average': self.average, 'median': self.median,
               }
      self.tools.update(pmath)

    def getformula(self, key):
        return self._cells[key]

    def max(self, keys):
        return max(map(self.__getitem__, self.cellrange(keys)))

    def min(self, keys):
        return min(map(self.__getitem__, self.cellrange(keys)))

    def sum(self, keys):
        return sum(map(self.__getitem__, self.cellrange(keys)))

    def average(self, keys):
        cells = map(self.__getitem__, self.cellrange(keys))
        return sum(cells)/float(len(cells))

    def median(self, keys):
        cells = map(self.__getitem__, self.cellrange(keys))
        n = len(cells)
        midpt = n / 2
        if (n % 2):
          return cells[midpt]
        else:
          return (cells[midpt-1] + cells[midpt]) / 2.0

    def __setitem__(self, key, formula):
        key = key.lower()

        # save variable name to cell name mapping
        m = self.var_re.search(formula)
        if m:
            formula = m.group(2)
            self._cells[m.group(1).lower()] = key

        if self.eval_re.match(formula):
            # case insensitivity for everything after an '='
            formula = formula.lower()

            # cell ranges are passed as strings
            m = self.range_re.search(formula)
            if m:
                formula = m.group(1) + "('" + m.group(2) + "')"

            # '=' and '@' are unwanted for eval
            formula = re.sub('^=@?', '', formula)
        elif not self.num_re.match(formula):
            m = self.tic_re.match(formula)
            if m:
                formula = m.group(1)
            formula = "'" + formula + "'"

        self._cells[key] = formula

    def __getitem__(self, key):
        bCache = self._cache is None
        if bCache: self._cache = {}
        while True:
            try:
                if (self._cells[key] == None or self._cells[key] == ''):
                    rv = ''
                else:
                    rv = eval(self._cells[key], self.tools, self._cache)
                break
            except NameError, ne:
                name = ne.args[0][6:-16] # Extract name from NameError
                if name in self._cells:
                    self._cache[name] = self[name]
                else:
                    self._cache[key] = None
                    self._cells[key] = None
            except SyntaxError, se:
                rv = self._cells[key]
                break
            except TypeError, te:
                #rv = 'type error: ' + self._cells[key] + ', ' + str(te)
                rv = 0
                break

        if bCache: self._cache = None
        return rv

    def cellrange(self, keys):
        m = re.search('([a-z]+)(\d+):([a-z])+(\d+)?', keys)

        result = []
        if not m:
            result = re.split(',', keys)
            return result

        (c1, r1, c2, r2) = m.groups()

        mincol = min(c1, c2)
        maxcol = max(c1, c2)
        minrow = min(int(r1), int(r2))
        maxrow = max(int(r1), int(r2))

        col_names = 'abcdefghijklmnopqrstuvwxyz'
        for i in range(col_names.index(mincol), col_names.index(maxcol)+1):
            for j in range(minrow, maxrow+1):
                result.append(col_names[i] + str(j))

        return result


class Parser:

    def __init__(self, raw, request, **kw):
        self.ss = SpreadSheet()
        self.raw = raw
        self.request = request
        self.form = request.form
        self._ = request.getText
        self.kw = []
        for arg in kw.get('format_args', '').split():
            self.kw.append(arg)


    def format(self, formatter):
        lines = self.raw.split('\n')

        kw = self.kw
        column_header = 0
        row_header = 0
        show_formular = 0
        format = ''
        insep = '||'
        outsep = '||'
        right_format = '<)>'
        left_format = '<(>'
        colheader_format = '<:#CCCCCC>'
        rowheader_format = '<)5%#CCCCCC>'
        zt = 0
        for test in kw:
             if test == '-column_header': column_header = 1
             if test == '-row_header': row_header = 1
             if test == '-show_formular': show_formular = 1
             if test == '-format': format = re.split(',', kw[zt + 1])
             if test == '-input_separator': insep = kw[zt + 1]
             if test == '-output_separator':
                 outsep = kw[zt + 1]
                 right_format = ''
                 left_format = ''
                 colheader_format = ''
                 rowheader_format = ''
             zt += 1

        formats = {} # { (row,col): '<wikiformat>', ... }
        r = 0 # row counter
        c = 0 # column counter
        col_names = 'abcdefghijklmnopqrstuvwxyz'
        maxcolumns = 0

        for txt in lines:
            txt = txt.lstrip()
            if txt == '':
                continue
            columns = txt.split(insep)
            if re.search('^' + insep, txt):
                columns = columns[1:]
            if re.search(insep + '$', txt):
                columns = columns[:-1]

            if len(columns) > maxcolumns:
                maxcolumns = len(columns)

            c = 0
            for cell in columns:
                # check for wiki formatting string at beginning of ss data: "<format>..."
                if cell.startswith('<'):
                    p = cell.find('>') + 1
                    if p > 1:
                        formats[(r, c)], cell = cell[:p], cell[p:]
                else:
                    formats[(r, c)] = ""

                self.ss[col_names[c]+str(r)] = cell.strip()

                c += 1

            r += 1

        maxrows = r
        result = ""

        if outsep != '||':
            self.request.write('<pre>')

        if column_header == 1:
            header_names = ' ABCDEFGHIJKLMNOPQRSTUVWXYZ'
            result += '\n' + outsep
            if row_header == 1:
                start = 0
            else:
                start = 1

            for name in header_names[start:maxcolumns+1]:
                result += colheader_format + "'''" + name + "'''" + outsep

        for r in range(maxrows):
            result += '\n' + outsep

            if row_header == 1:
                result += rowheader_format + "'''" + str(r) + "'''" + outsep

            for c in range(maxcolumns):
                cellname = col_names[c]+str(r)

                cell = str(self.ss[cellname])
                if cell == '':
                    cell = ' '

                if show_formular == 0:
                    num_match = self.ss.num_re.match(cell)
                    if num_match:
                        fmt = right_format
                        if formats[(r,c)] != '':
                            fmt = formats[(r,c)]

                        if format == '':
                            cell = '%s %.*f' % (fmt, 2, float(self.ss[cellname]))
                        else:
                            cell = '%s %.*f' % (fmt, int(format[c-1]), float(self.ss[cellname]))

                elif show_formular == 1:
                    cell = self.ss.getformula(cellname)
                else:
                    if formats[(r, c)] == '':
                        cell = left_format + cell
                    else:
                        cell = formats[(r, c)] + cell

                result += cell + outsep

        if outsep != '||':
            self.request.write(result + '</pre>')
        else:
            result = result[1:]
            result = wikiutil.unquoteWikiname(result)
            wikiizer = wiki.Parser(result, self.request) # parser for wiki tabular
            wikiizer.format(formatter)

