 
"""
    MoinMoin - sctable a Processor for spread sheet calculations by sc
    @license: GNU GPL, see COPYING for details.

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

    CALLING SEQUENCE:
       {{{
       #!sctable [-column_header, -row_header, -show_formular, -format  ]
       }}}

    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


    EXAMPLE:
{{{
SUM over columns}}}
{{{
#!sctable
||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}}}

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

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

-----
{{{
SUM over rows}}}
{{{
#!sctable
||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}}}
{{{
#!sctable
||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}}}
{{{
#!sctable  -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}}}
{{{
#!sctable -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}}}
{{{
#!sctable  -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}}}
{{{
#!sctable -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}}}
{{{
#!sctable -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}}}
{{{
#!sctable -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
}}}
{{{#!sctable -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:
{{{#!sctable -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
}}}
{{{#!sctable
||<: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||
-----


    PROCEDURE:
      This processor needs the external sc (http://freshmeat.net/projects/sc/) routine.
      It is necessary to have a tmp directory in the wiki data dir.
      All formulars have to start by a "=" sign.

      Please remove the version number from the routine name!

    RESTRICTIONS:
      * german umlauts e.g. äöü are not implemented by now. 
        they need to be masked because sc is not able to proceed calculation with such signs.
	 
      

    MODIFICATION:
       @copyright: 2004-09-19 by Reimar Bauer (R.Bauer@fz-juelich.de) sctable-1.2.3-1
       1.2.3-2 : (RB) bug fixed line #!sctable was not found by giving input parameters
       1.2.3-3 : (RB) input parameter -show_formular added, column width is set to 200 chars
               :      if this parameter is used
               : (RB) bug removed (already) #!sctable position could be different from 0
	       :      but always greater -1
      1.2.3-4  : 2004-10-05 RB format always extended for sc call to 200 signs.
                 bug with blanks in names removed, more as one blank in a cell handled as one blank.
      1.2.3-5  : 2004-10-16 RB format codes for colors and cells removed before calculations
                 -format optional input var added

      1.3      : 2004-11-13 RB changed to PARSER
                 bug fixed: strings with blanks are formatted to the left
		 feature added: column width of row numbers is set to 5%
		 some examples fixed

      1.3.3-2 patch and examples from towi AT geocities DOT SPAM com implemented
              : format and colors are used by now!!

      	FUNCTIONAL ADDITIONS

  	* a cell can now start with an wiki format string that will format
    	  the cell according to wiki table formatting,
          examples:
          <:>@sum(A3:a6)
          <rowbgcolor=cyan>Title

       * you can define a name for a cell and use it later (no range defines yet):
         when combinign with <format> the format must come first.
         examples:
         {income}20000
         <:>{outcome}=income/2

    DISCUSSION (need help):
      * we need a better routine to destinguish between strings and numbers, i have still some problems with e.g
        def is_number(txt):
        	ItIs = True
        try:
                f =float(txt)
        except TypeError:
                ItIs = False

        return(ItIs)

	This doesn't work with formulars =@sum(a0:a2)

        what I do at the moment is to scan digits and signs from a string. This does not right identify a dot (.)
	in a string. This will be formatted as number. Thats the only bad thing I know on.

	ideas are welcome.

        1.3.5-3: 2005-08-05 RB tmp path set relativ to installation and created if it isn't there
	                       space before and after a cell entry by now ignored
                


"""

Dependencies = []
import sys, os, re, sha
from MoinMoin.parser import wiki
from MoinMoin.action import AttachFile
from MoinMoin.Page import Page
import string,os

config_external_sc="/usr/local/bin/sc"

def table2sc(lines,format,show_formular,request):

	result=[]
	formats = {} # { (row,col): '<wikiformat>', ... }

	r=0
	name="=" # searchstring
	col_names=' ABCDEFGHIJKLMNOPQRSTUVWXYZ'

	first_line=lines[0]
	col_list=first_line.split('||')
        digit="2"
	c=0
	form=[]

	for value in col_list:
		if (len(value.lstrip()) > 0):
  			if (format=="") :
              			digit="2"
	   		else:
	      			digit=format[c-1]

           		f="%(command)s %(column)s %(arg)s" %{
	     			"command":"format",
	     			"column":col_names[c],
	     			"arg":"200 " + digit + " 0"
	     		}
	   		form.append(f)

        	c=c+1
    	result.append(form)


	for txt in lines:
		n_name=txt.count(name)
		txt=txt.lstrip()
		
		sargs=txt.split('||')
		n=len(sargs)
		sargs=sargs[0:n-1]
                
		c=0      # linecounter
		digits="=-.0123456789"
		for arg in sargs:
		        
		        arg=arg.strip()
			
			# check for wiki formatting string at beginning of sc data: "<format>..."
         		if arg.startswith('<'):
            			p = arg.find('>')+1
            			if p>1:
                			formats[(r,c)] , arg = arg[:p] , arg[p:]
					arg=arg.strip()
			# check for a sc column name define: "{defname}..."
         		defname = None
			if (show_formular == 0):
         			if arg.startswith('{'):
            				p = arg.find('}')
            				if p>0:
                				defname , arg = arg[1:p] , arg[p+1:]
						arg=arg.strip()

			# sc data
        		if (len(arg) > 0):
	    			if arg[0] in digits:
	       				if (arg.find(name) == 0):
                  				if (show_formular == 0):
                      					sargs[c]="%(command)s %(column)s %(arg)s" %{
						        	"command":'let ',
					      			"column":col_names[c]+str(r),
			      					"arg":string.strip(arg)
			    				}
                  				else:
		      					arg=string.replace(arg,"="," =")
                      					sargs[c]="%(command)s %(column)s=%(arg)s" %{
		              					"command":'leftstring',
			      					"column":col_names[c]+str(r),
			      					"arg":'"'+string.replace(arg,' ','')+'"'
			    				}

	       				else:
	         				if (show_formular == 0):
                    					sargs[c]="%(command)s %(column)s=%(arg)s" %{
		              						"command":'let ',
			      						"column":col_names[c]+str(r),
			      						"arg":string.strip(arg)
			     				}
		 				else:
                    					sargs[c]="%(command)s %(column)s=%(arg)s" %{
			  						"command":'leftstring',
									"column":col_names[c]+str(r),
			      						"arg":'"'+string.replace(arg,' ','')+'"'
								}


            			else:
	       				if (c > 0):
						if (string.strip(arg) == ""):
		      					arg="Blank!@"
		  				else:
		      					arg=string.strip(arg)
		      					arg=string.replace(arg," ","Blank!@")

                  				sargs[c]="%(command)s %(column)s=%(arg)s" %{
		              				"command":'leftstring ',
			      				"column":col_names[c]+str(r),
			      				"arg":'"'+arg+'"'
			    			}

			else:
				if (c > 0):
					sargs[c]="%(command)s %(column)s=%(arg)s" %{
		              			"command":'leftstring ',
				      		"column":col_names[c]+str(r),
				      		"arg":'" Blank!@"'
			    		}
			if defname:
				sargs[c] = '%s\ndefine "%s" %s%s' % (sargs[c], defname, col_names[c], r)
			c=c+1
		result.append(sargs)
		r=r+1

	return(result,formats)

class Parser:

        extensions = ['.sc']
	
	
	
	def __init__(self, raw, request, **kw):

        	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):
                config_sc_vartmp_dir = os.path.join(self.request.rootpage.getPagePath(),'tmp')
		
		if not os.path.exists(config_sc_vartmp_dir):
		   os.mkdir(config_sc_vartmp_dir)

		   
		
		lines = self.raw.split('\n')
		
		zt=0
		for txt in lines:
		    
		    txt=txt.replace(u'\xc3\x84','AE')
		    lines[zt]=txt
		    zt=zt+1
		
		kw=self.kw

    		column_header=0
    		row_header=0
    		show_formular=0
    		format=''

    		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=string.split(kw[zt+1],",")
      			zt=zt+1


    		matrix = []

    		textstr = '\n'.join(lines).strip()

    		tmpname = re.sub('\s+', ' ', textstr)
		
		
		tmpname=sha.new(tmpname.encode('utf8','replace')).hexdigest().upper()
    		
		#tmpname=sha.new(tmpname).hexdigest()
		
		
    		tmpname = tmpname + "_sc"
    		tmpfile = "%s/%s.sc" % (config_sc_vartmp_dir, tmpname)
		
		
		textstr, formats=table2sc(lines,format,show_formular,self.request)
		


    		data = open(tmpfile, "w")
    		
    		i=0
    		for txt in textstr:
        		
			if (len(textstr[i]) > 0):
            			tmpstr=string.join(textstr[i],'\n')
			        data.write('%s\n' % tmpstr.encode('latin-1'))
        		i=i+1
    		data.close()
		

    		cmd = "%(external_sc)s %(argument)s %(file)s " % {
             		"external_sc": config_external_sc,
             		"argument": " -W% ",
	     		"file":tmpfile
          		}


    		f=os.popen(cmd,'r')# popen to get the result of the calculation
		sys.stderr.write(cmd)
    		result=f.readlines()
    		#f.flush()
    		os.unlink(tmpfile) # remove the tmpfile

    		right_format='<)>'
    		left_format='<(>'

    		for txt in result:
		        
      			txt=string.join(txt,'')
      			cells=string.split(txt,' ')
      			zres=[]
      			c=1
      			for value in cells:
        			value=string.join(value,'')
        			value=string.strip(value)
        			strlen=len(value)

				if (strlen > 0) :
					v = ord(value[0])
	   				if ord('a')<=v<=ord('z') or ord('A')<=v<=ord('Z') or value[0] in " ":
						format=left_format
	   				else:
	      					if (show_formular == 0) :
	         					format=right_format
	      					else:
              			 			format=left_format

           				if (value == "Blank!@"):
						value=" "

	   				if string.find(value,"Blank!@"):
	      					value=string.replace(value,"Blank!@", " ")

					zres.append( [format, value] )

					c=c+1
      			matrix.append(zres)
			# post formatting of cells according to cut out wiki format strings
    			# - matrix is here: [ [  [format,content], ... ], ... ]
    		for cellname, cellformat in formats.items():
        		row,col = cellname
        		m = matrix[row][col-1]
			m[0] = cellformat
    		matrix = map(lambda row: "||" + "||".join([f+c for f,c in row]) + "||", matrix)

    		# post processing of over all matrix
    		# - matrix is here: [ "||cell||cell||...||", "||cell||...||", ... ]

    		if (len(matrix[0]) == 2):  # das ist noch nicht die beste Loesung (workaround)
       			matrix=matrix[1:]

    		if (row_header == 1):
      			y=[]
      			r=len(matrix)
      			lines=range(r)

      			i=0

      			for no in lines:
			        matrix[i]="||<)5%#CCCCCC>'''"+str(no)+"'''"+matrix[i]
		 		i=i+1

    		if (column_header == 1):
       			col_names=' ABCDEFGHIJKLMNOPQRSTUVWXYZ'
       			x='||'
       			if(row_header == 1):
          			start=0
       			else:
          			start=1
       			for name in col_names[start:c]:
           			x = x + "%(format)s %(value)s %(tab)s" % {
	                    		"format": '<:#CCCCCC>',
	                    		"value": "'''"+name+"'''",
		            		"tab":'||'}

       			matrix.insert(0,x)

		wikiizer = wiki.Parser(unicode(string.join(matrix,"\n"),'latin-1'),self.request) # parser for wiki tabular
		wikiizer.format(formatter)
