"""
    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

        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 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.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):     # Number of elements in data set is odd.
          return cells[midpt]
        else:         # Number of elements in data set is even.
          return (cells[midpt-1] + cells[midpt])/2.0

    def __setitem__(self, key, formula):
        self._cells[key.lower()] = 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)
                break
                
        if bCache: self._cache = None
        return rv

    def cellrange(self, keys):
        m = re.search('([a-z]+)(\d+):([a-z])+(\d+)?', keys)
        
        result = []
        if m == None:
            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:
                cellname = col_names[c]+str(r)
                cell = cell.strip()
                
                # 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:]
                        cell = cell.strip()
                else:
                    formats[(r,c)] = ""
    
                # compatibility with other spreadsheet syntax
                # case insensitivity for everything after an '='
                m = re.search('=@?(.+)', cell)
                nonstr_match = re.match('^[\d+-\.\{]', cell)
                if m == None and nonstr_match == None:
                    cell = '"' + cell + '"'
                elif m != None:
                    cell = m.group(1).lower()

                # cell ranges are parsed as strings
                m = re.search('(\w+).(\w+:\w+).', cell)
                if m != None:
                    cell = m.group(1) + "('" + m.group(2) + "')"

                # lowercase variable names
                m = re.search('\{(.+)\}(.+)', cell)
                if m != None:
                    cell = m.group(2).lower()
                    self.ss[m.group(1)] = cellname

                # '=' and '@' are unnecessary after the parsing above
                cell = re.sub('=@?', '', cell)

                self.ss[cellname] = cell

                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)

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

                if show_formular == 0:
                    nonstr_match = re.match('^[\d+-\.\{]', cell)
                    if nonstr_match != None:
                        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)