# -*- coding: utf-8 -*-
"""/****************************************************************************
Copyright (c) 2007- 2009 Norman Bakker 

This library is free software; you can redistribute it and/or
modify it under the terms of the GNU Library General Public
License as published by the Free Software Foundation; either
version 2 of the License, or (at your option) any later version.

This library is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU Library General Public License for more details.

You should have received a copy of the GNU Library General Public License along with this library; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.
 ****************************************************************************/"""


# -*- coding: utf-8 -*-
import uno
import unohelper

#for WIN32
#import sys
#sys.path.append('C:\\Python26\\Lib\\site-packages')
#end for WIN32

#for LINUX
#import sys
#sys.path.append('.')
#import transfer  

from math import *
import itertools

from com.sun.star.task import XJobExecutor
#based on the initial example provided by:
#http://www.linuxjournal.com/content/python-pyuno-hello-world-addon-openoffice 20090206 Mitch Frazier
#
# Implement a UNO component by deriving from the standard
# unohelper.Base class and from the interface(s) you want to implement.

from string import ascii_letters, ascii_uppercase, digits, upper
import cPickle

class Ant():
    #trial of inclusion of another class for transferdemoJob to inherit
    def __init__(self) :
        pass

    def isAlpha(self,x) :
        """returns True if first character is alph a-z;A-Z  False otherwise.
        - using str.isalpha() builtin gives an error spliced list elements """
        #trap a '' as this would return True otherwise
        if x[:1] == '' : return False
        return  x[:1] in ascii_letters
        
    def isNum(self,x) :
        """returns True if first character is a digit (0..9) False otherwise.
        """
        #trap a '' as this would return True otherwise
        if x[:1] == '' : return False
        return  x[:1] in digits

    def zeroNullsInList(self,x) :
        """replaces a '' in list x with a 0"""
        for i,y in enumerate(x):
            if not y : x[i] = 0

    def sum_list(self,ll) :
        """sums the elements in a list, replacing null entries with 0"""
        total = 0
        for n in range(0,len(ll)) :
            if ll[n] : total += ll[n]
        return total
        
    def cumsum(self,x):
        """return the cumulative sums of x in a list where first entry by definition is zero l[0] = 0"""
        #faster than return [sum(x[:i+1] for i in len(x)] by factor of 2 for 10 element list but see c++
        s=0
        l=[]
        for i in x:
            if i == '' : i = 0
            l.append(s)
            s += i
        return l

    def erat2(self,n): 
        """generates n number of primes starting with 2"""
        #from python cookbook o'reilly 2nd ed: p670 
        D = {}
        o  = [2]
        c = 1
        if n == 1 : return o  #yield 2
        for q in itertools.islice(itertools.count(3),0,None,2) :
            p = D.pop(q, None)
            if p is None:
                c += 1
                D[q*q] = q
                if c > n : return o
                o.append(q) #yield q
            else:
                x = p + q
                while x in D or not (x&1):
                    x += p
                D[x] = p

    def primes_wang(self,n):
        """generate primes between 2 and n"""
        #http://code.activestate.com/recipes/366178/ wensheng wang  fastest on athlon
        if n==2: return [2]
        elif n<2: return []
        s=range(3,n+1,2)
        mroot = n ** 0.5
        half=(n+1)/2-1
        i=0
        m=3
        while m <= mroot:
            if s[i]:
                j=(m*m-3)/2
                s[j]=0
                while j<half:
                    s[j]=0
                    j+=m
            i=i+1
            m=2*i+3
        return [2]+[x for x in s if x]
    
    def linInterpolation(self,x,y) :
        """Python version. Slower than c++ extension. x is a list, y can be that or a tuple. Fill in any blank entries in x by 
        linearly interpolating on cumulative y. Assume 0 at start and end if no data given. 
        Modify x in place"""
        #determine cumulative y
        cumy = self.cumsum(y)
        end_ = len(x) -1
        if x[0] == '' : x[0] = 0
        if x[end_] == '' : x[end_] = 0
        istart, iend = 0,0
        while istart < end_ :
            #find first nonblank, ignoring x[0] which is preset
            for i in range(istart+1,end_+1) :
                if x[i] != '' : break
            iend = i
                #interpolate if 
            if (iend - istart) > 1 :
                xin = x[istart]
                xout = x[iend]
                delx = xout - xin
                y_total = cumy[iend]-cumy[istart]
                for j in range(istart+1,iend):
                    if y_total : x[j] = xin + delx * (cumy[j]-cumy[istart])/ y_total
                    else : x[j] = x[j-1]
            istart = iend
        
    def strip_allold(self,x, char= ' ') :
        """Strip all char (default blanks) fr tuple x"""
        return ''.join(str(x).split(char))

    def strip_all(self,x) :
        """Strip all blanks fr list x; can handle non string elements in x """
        y=[]
        for row in x :
            y.append(''.join(str(row).split()))
        return y
        
    def sortedLofL(self,l_of_l,col, reverse = False) :
        """sort a list of lists according to the contents of column col, default is ascending order"""
        try:
            return sorted(l_of_l,key = lambda x: x[col])
        except IndexError:
            print 'sortedLofL error: col index outside of range of list of list columns'
            return []

    def convert_Seq_to_Seq_of_Tuples_rows(self,tt,cols) :
        """convert a sequence to a sequence of tuples filling across the rows each containing len(tt)/cols elements
            - eg to convert tt to a tuple of tuples supply argument as a tuple
                 to convert tt to a list of tuples supply argument as a list"""
        rows = len(tt) /cols
        z = ['']*rows
        for n in range(0,rows) :
            s = n * cols
            z[n] = tt[s : s + cols]
        return tuple(z)

    def convert_List_of_Lists_to_Tuple_of_Tuples(self,ll) :
        """convert a list of lists to a tuple of tuples"""
        return tuple([tuple(ll[n]) for n in range(0,len(ll))])

    def putCell(self, addr, message):
        """show the message at cell ref provided (by addr)"""
        cells = self.sheet.getCellRangeByName(addr)
        cells.String = message

    def putCellVal(self, addr, val):
        """show the message at cell ref provided (by addr)"""
        cells = self.sheet.getCellRangeByName(addr)
        cells.Value = val
     
    def getCell(self, addr):
        """gets the contents of cell ref provided (by addr)"""
        cells = self.sheet.getCellRangeByName(addr)
        return cells.String

    def getCellVal(self, addr):
        """gets the contents of cell ref provided (by addr)"""
        cells = self.sheet.getCellRangeByName(addr)
        return cells.Value

    def putCellPos(self, col, row, message):
        """show the message at cell ref provided (by column and row numbers)"""
        cells = self.sheet.getCellByPosition(col,row)
        cells.String = message
    
    def putCellPosVal(self, col,row,val):
        """gets the contents of cell ref provided (by column and row numbers)"""
        cells = self.sheet.getCellByPosition(col,row)
        cells.Value = val

    def getCellPos(self, col,row):
        """gets the contents of cell ref provided (by column and row numbers)"""
        cells = self.sheet.getCellByPosition(col,row)
        return cells.String
    
    def getCellPosVal(self, col,row):
        """gets the contents of cell ref provided (by column and row numbers)"""
        cells = self.sheet.getCellByPosition(col,row)
        return cells.Value
        
    def getSelectedRange(self):
        """returns tuple of startrow, col and endrow, col of selected 
           range on sheets as 0 origin numbers"""
        ref = self.spreadsheet.CurrentSelection.getRangeAddress()
        return ref.StartColumn,ref.StartRow,ref.EndColumn,ref.EndRow
        
    def getLastActiveCell(self):
        """returns the last used column and row of the current sheet 
        (ie the last cell in the range containing something other than '')"""
        #create a cursor for the whole sheet using OO interface XSheetCellRange 
        cursor = self.sheet.createCursor()
        cursor.gotoEndOfUsedArea(True)
        address = cursor.RangeAddress
        endcol = address.EndColumn
        endrow = address.EndRow
        return endcol,endrow

    def intToColLabel(self,n) :
        """converts integer column index to alpha label up to 18277 = 26*26*26 + 26*26+25 ('ZZZ')
        - after that returns str(n) """
        #messy!
        if n <= 25 :
            return ascii_uppercase[n]
        elif n <= 701:
            return ascii_uppercase[(n-1)/26 -1] + ascii_uppercase[n % 26]
        elif n <= 18277: 
            f = n /677 -1
            g = (n - (f+1)*676)/26 -1
            return  ascii_uppercase[f] + ascii_uppercase[g] + ascii_uppercase[n % 26]
        else : return str(n)

    def colLabeltoInt(self,label) :
        """converts columlabel to int up to ZZZ (= 18277)"""
        #messy!
        au = list(ascii_uppercase)
        ul = upper(label)
        l = len(ul)
        if l == 1 : return au.index(ul)
        elif l == 2: return (1+au.index(ul[0]))*26 + au.index(ul[1])
        elif l == 3: return ((1+au.index(ul[0])))*676 + (1+au.index(ul[1]))*26 + au.index(ul[2])
        else : return ul

    def ClearSelectedArea(self):
        """clears selected sheet area by filling with tot full of ''  """
        print 'clearing selection of data'
        c0,r0,c1,r1 = self.getSelectedRange()
        oCellRange = self.sheet.getCellRangeByPosition(c0,r0,c1,r1)
        #create a tuple of tuples of the same size and shape as oCellRange
        oCellRange.setDataArray((('',)*(1+c1-c0),)* (1+r1-r0))
        self.putCell(self.msgcell,'clear selection of data')
       
class transferdemoJob(unohelper.Base, XJobExecutor, Ant):
#
# class globals specified here
    #cell name indices 
    A_,B_,C_,D_,E_,F_,G_,H_,I_,J_,K_,L_,M_,N_,O_,P_,Q_,R_,S_,T_,U_,V_,W_,X_,Y_,Z_ = range(0,26)
    C0 = A_ # use this to setup a work area
    R0 = 5  # ditto
    lotcol = '' #list of tuples : array of totrow data rearranged by columns
    totrow = '' #tuple of tuple data as read in by getDataFromFileArraY()  : row arrays
    #these can be converted each to the other using :
    #            self.totrow = tuple(zip(*self.lotcol))
    #       or   self.lotcol = zip(*self.totrow)
    msgcell = 'a1'
    #data variables if any


    def __init__(self, ctx):
        #initiate various actions common to all the following members
        # Retrieve the desktop object
        self.desktop = ctx.ServiceManager.createInstanceWithContext("com.sun.star.frame.Desktop", ctx)
        # Get the current spreadsheet.
        self.spreadsheet = self.desktop.getCurrentComponent()
        # Get the collection of sheets in the spreadsheet.
        self.sheets = self.spreadsheet.getSheets()
        #get a specific sheet (the first in this case, it could be any)
        self.sheet = self.sheets.getByIndex(0)
        # include Ant class ,and provide the cell position of any message
        Ant.__init__(self)
#==============================================================================================
    def trigger(self, args):
        #trigger acts as a main function, and 
        #other functions can be called from within its scope (see examples below)
        #haven't worked out how to pass an argument on calling the Add-on
 
        
        #get run instructions from cell 'I1' by inserting the INDEX shown below
        """
        For testing                    INDEX = 0
        get data from file                     1
        save sheet data to file                2
        update internal arrays                 3
        fill cells with numbers                5
        interpolate / fill data on sheet       6
        print all in selected range            7
        clear a selected area on the sheet     8
        multiply the range by a number in j1  20
        """
        #determine which program to run
        runno = self.getCellVal('i1')
        print "Run no read from i1", runno
        #get data file details from sheet cell B30,B31 in case needed
        path,filename = self.getCell('b2'),self.getCell('b3')
        fullfilename = ''.join([path,filename])
        print '\nfile path and name',fullfilename
        print '\nrun no read in from sheet row', runno
        #do something based on index in cell i1
        if runno == 0 : 
            #used for initial testing 
            #self.putCell(self.msgcell,''.join(['started transferdemo_oocalc  - args : ']))
            self.putCell(self.msgcell,'testing')
            self.fillRange_with_primes(0)
        elif runno == 1: 
            #read in totrow data from file in path and name shown on sheet 
            #to internal array tuple of tuples in row format  : totrow
            #any exception in getDataFromFile stops further execution
            self.totrow = self.getDataFromFile(path+filename)
            endcol = len(self.totrow[0])-1
            endrow = len(self.totrow)-1
            self.putCell(self.msgcell,''.join(['read ',str(len(self.totrow)),'data lines']))
            print 'read ',len(self.totrow),'data lines'
            #update sheet as well
            #place cursor on sheet to select starting cell to write data to (minimum is self.C0,self.R0)
            c0,r0,c1,r1 = self.getSelectedRange()
            if c0 < self.C0 : c0 = self.C0
            if r0 < self.R0 : r0 = self.R0
            self.UpdateSheet(c0,r0,c0+endcol,r0+endrow)
        elif runno == 2:
            #save data as specified in workspace area (starting at A_,R0) on sheet to file with path+filename
            endcol,endrow = self.getLastActiveCell()
            c0,r0,c1,r1 = self.C0,self.R0,endcol,endrow
            self.transferDataRange_to_totrow(0,0,c1,r1)
            starting_at_cell =  ''.join([ascii_uppercase[c0],str(r0+1)])
            self.saveDataToFile(fullfilename,self.totrow)
            self.putCell(self.msgcell,''.join(['saved ',str(c1-c0+1),' cols & ',str(r1-r0+1),' rows starting at ', starting_at_cell,' to file : ',fullfilename]))
        elif runno == 3:
            endcol,endrow = self.getLastActiveCell()
            print 'last active col,row', endcol,endrow
            self.putCell(self.msgcell,''.join(['last active cell : ',self.intToColLabel(endcol),str(endrow+1)]))
        elif runno == 4:
            pass
        elif runno == 5:
            #fill cells selected with their index numbers (assuming counting across the rows)
            self.fillcells(0, option = 1, startvalue = 0, step = 1)
        elif  runno == 6 :
            #interpolate data to fill in any empty cell in the rows and columns in the selected rectangular range
            #leaves existing data in place and starts with first and last columns first - then fills in rows
            #any empty border cells c0,r0; c0,r1; c1,r0; c1,r1 are assumed to contain 0
            c0,r0,c1,r1 = self.getSelectedRange()
            #now get selected rectangle and interpolate/fill it
            self.transferDataRange_to_totrow_lotcol(c0,r0,c1,r1)
            #create a dummy intervals tuple for strict linear interpolation (can change here)
            intervals = (1,) * (1+r1-r0)
            #provide first column as a list to linInterpolation- for this we need lotcol
            cc = list(self.lotcol[0])
            #use python 'transfer' extension function linInterpolation 
            self.linInterpolation(cc,intervals)
            #show this first column on the sheet (convert cc to a tuple first)]
            self.transferATupleToASheetColumn(tuple(cc), c0 , r0)
            #provide last column as a list to linInterpolation
            cc = list(self.lotcol[c1-c0])
            self.linInterpolation(cc,intervals)
            #show this on the sheet (convert cc to a tuple first)]
            self.transferATupleToASheetColumn(tuple(cc), c1 , r0)

            #now interpolate across the columns by row
            self.transferDataRange_to_totrow(c0,r0,c1,r1) #for this we can make do with totrow
            #create a dummy intervals tuple for strict linear interpolation (free to change here)
            intervals = (1,) * (1+c1-c0)
            for n in range(0,r1-r0+1):
                #need to provide current row as a list to linInterpolation
                cr = list(self.totrow[n])
                self.linInterpolation(cr,intervals)
                #next function needs data as a tuple
                self.transferATupleToASheetRow(tuple(cr),c0,r0+n)
            self.putCell(self.msgcell,'Range interpolated data done')
        elif runno == 7 :
            #set range
            c0,r0,c1,r1 = self.getSelectedRange()
            #get range into totrow
            self.transferDataRange_to_totrow(c0,r0,c1,r1)
            #print range
            self.printArray(self.totrow)    
            self.putCell(self.msgcell,''.join(['Numbers from spreadsheet in range ',self.intToColLabel(c0),str(r0+1), \
            ' to ',self.intToColLabel(c1),str(r1+1)]))
        elif runno == 8:
            self.ClearSelectedArea()        
        elif runno == 9: 
            pass
        elif runno == 10 : 
            pass
        elif runno == 20: #multiply cell range selected by a constant factor; default 2
            c0,r0,c1,r1 = self.getSelectedRange()
            #read range into lotcol 
            self.transferDataRange_to_totrow(c0,r0,c1,r1)
            #read factor from column j1 (or default to 2)
            factor = self.getCellVal('j1')
            if factor == 0 : factor = 2
            print 'factor', factor
            for n in range(0,r1-r0+1):
                #as this will be modified in place need a list sequence
                cc = list(self.totrow[n])
                ll = [cc[i] * factor for i in range(0,len(cc))]
                self.transferATupleToASheetRow(tuple(ll), c0 , r0 + n)
        else :
            print self.getSelectedRange()            
            print 'done'

    def printArray(self,ray) :
        """ Prints the totrow array on the console
            """
        print
        print 'Current totrow array'
        print 
        for l in ray :
            for c in l :
                print c,
            print
        print

    def transferDataRange_to_totrow(self, C0, R0, C1, R1) :
        """read in all the data from the defined range on the sheet to totrow, a tuple of tuples in row format"""
        oCellRange = self.sheet.getCellRangeByPosition(C0,R0,C1,R1) 
        #fetch all the data in the range
        self.totrow = oCellRange.getDataArray()
        self.noDataRows = len(self.totrow)
        print 'no of rows read from sheet : ', len(self.totrow)
        self.putCell(self.msgcell,'data placed in totrow')

    def transferDataRange_to_totrow_lotcol(self, C0, R0, C1, R1):
        """read in all the data from the defined range to internal python arrays totrow and lotcol"""
        self.transferDataRange_to_totrow(C0, R0, C1, R1)
        #convert data to column format and store in global lotcol
        self.lotcol = zip(*self.totrow) 
        #using lotcol[n] lot faster than list comprehension : [self.totrow[m][n] for m in range(0,len(self.totrow))]
        print 'no of rows read from sheet : ', len(self.totrow)
        self.putCell(self.msgcell,'data placed in totrow, lotcol')

    def convertTupletoLotcol(tt,cols) :
        """convert a tuple to a tuple of tuples in column arrangement each row containing len(tt)/cols elements"""
        rows = len(tt) /cols
        z = ['']*rows
        for n in range(0,rows) :
            s = n * cols
            z[n] = tt[s : s + cols]
        return zip(*z)

    def transferTupleToSheetRange(self, tupledata, c0,r0,c1,r1) :
        """generic : displays contents of tupledata on the sheet in range shown"""
        oCellRange = self.sheet.getCellRangeByPosition(c0,r0,c1,r1)
        #tuple data needs to be in tuple of tuple format for setDataArray- convertTuple does this
        oCellRange.setDataArray(self.convert_Seq_to_Seq_of_Tuples_rows(tupledata,1 + c1 -c0))
        
    def fillinColumnStaticData(self, tupdata, name = 'variable', default = 0) :
        """fills in all rows of a column of data, filling in 
        empty rows with a pre-existing value in an earlier row and 
        accepting any row with a valid entry. Returns a tuple.
        """
        data = list(tupdata)
        dc = data[0]
        # always provide a default value for first row (0 or other)
        if dc == '' : dc = default
        for d in data:   
            if d == '' : data[data.index(d)] = dc
            else : dc = d
        return tuple(data)

    def transferATupleToASheetColumn(self, tupledata, col0,row0) :
        """displays contents of a data tuple in the relevant column on the sheet"""
        oCellRange = self.sheet.getCellRangeByPosition(col0,row0,col0,row0+len(tupledata)-1)
        #convert tupledata to format acceptable for a column for setDataArray ie a tuple of tuples ((219,),(219,), ...))
        tzt = tuple(zip(tupledata))
        oCellRange.setDataArray(tzt)
        self.putCell(self.msgcell,'data written to column')

    def transferATupleToASheetRow(self, tp, c0,r0) :
        """generic : writes a row of data in single tuple on the sheet starting at c0, r0"""
        r1 = r0
        c1 = c0 + len(tp) -1
        oCellRange = self.sheet.getCellRangeByPosition(c0,r0,c1,r1)
        #tuple data needs to be in tuple of tuple format for setDataArray- convertTuple does this
        oCellRange.setDataArray(tuple([tuple(tp) for n in range(1)]))
        self.putCell(self.msgcell,'data written to row')
    
    def UpdateSheet(self, c0,r0,c1,r1 ) :
        """displays contents of all the internal tuple array (tupledata=totrow) on the sheet"""
        #currently columns of interest are A to Z
        oCellRange = self.sheet.getCellRangeByPosition(c0,r0,c1,r1)
        oCellRange.setDataArray(self.totrow)
        self.putCell(self.msgcell,'sheet updated')
    
    def transferTupleOfTuples_to_SheetSelection(self, tupledata, c0,r0,c1,r1) :
        """generic : displays contents of tuple of tupledata on the sheet in range shown"""
        oCellRange = self.sheet.getCellRangeByPosition(c0,r0,c1,r1)
        #tuple data needs to be in tuple of tuple format for setDataArray- convertTuple does this
        oCellRange.setDataArray(tupledata)


    def PlaceDataInCell(self, cr, tupledata) :
        """places contents of tupledata  of form : ((1,2,3,...),) in cell c,r"""
        oCellRange = self.sheet.getCellRangeByName(cr)
        oCellRange.setDataArray(tupledata)
        self.putCell(self.msgcell,'data written to cell')
        
        
    def saveDataToFile(self, name, data) :
        """save data in totrow in file named name"""
        print 'name of savefile', name
        try:
            f = open(name,'wb') 
            cPickle.dump(data,f)
        except IOError :
            print '\nunable to open file', name, 'for saving'            
        finally:
            f.close()
        self.putCell(self.msgcell,'saved data to file' + name)
    
    def getDataFromFile(self, name) :
        """save data in totrow in file named name"""
        try:
            f = open(name,'rb') 
            data = cPickle.load(f)
        except IOError :
            print '\nunable to open file', name, 'for reading'
            pass
        finally:
            f.close()
        print '\nread data from file', name
        return data

    def getColumn(self, col) :
        """return contents of sheet data column col in list format"""
        l = []
        for c in self.lotcol[col]:
            l.append(c)
        return l

    def fillcells(self, specifiedrange, option = 0, startvalue = 0, step = 1):
        """fill a range of cells cell by cell with dummy data option = 0, string,option = 1 numbers;
           These two options start with startvalue = as set ,and  step = as set.
           """
        if specifiedrange == 0 :
            c0,r0,c1,r1 = self.getSelectedRange()
        else :
            c0,r0,c1,r1 = specifiedrange
        for r in range(r0, r1+1):
            for c in range(c0, c1+1):
                cells = self.sheet.getCellByPosition(c, r)
                if (option == 0 ) :
                    cells.String = 'a-' + str(startvalue) #note use of String attribute, could also try Value for numerical data
                elif (option == 1) :
                    cells.Value = startvalue
                startvalue += step

    def fillRange_with_primes(self, specifiedrange) :
        """fills selected range with prime numbers starting from 2"""
        if specifiedrange == 0 :
            c0,r0,c1,r1 = self.getSelectedRange()
        else :
            c0,r0,c1,r1 = specifiedrange
        ll = self.erat2((1+c1-c0)* (1+r1-r0))
        self.transferTupleToSheetRange(tuple(ll),c0,r0,c1,r1)

# pythonloader looks for a static g_ImplementationHelper variable  : 
g_ImplementationHelper = unohelper.ImplementationHelper()

g_ImplementationHelper.addImplementation( \
        transferdemoJob,                                # UNO object class
        "org.openoffice.comp.pyuno.demo.transferdemo",  # Implementation name
        ("com.sun.star.task.Job",),)                    # List of implemented services
