# -*- coding: cp1252 -*-
import os
import os.path
from xml.etree import ElementTree
import win32com.client
from win32com.client import constants as c
import cPickle
 
excel = win32com.client.Dispatch("Excel.Application")
path = os.getcwd()
asusbook = excel.Workbooks.Open(os.path.join(path,"asus", "output", "asus.xlsx"))
sbook = excel.Workbooks.Open(os.path.join(path,"s", "output", "s.xlsx"))
sbevelbook = excel.Workbooks.Open(os.path.join(path,"sbevel", "output", "sbevel.xlsx"))

extra = excel.Workbooks.Add()

books = [asusbook, sbook, sbevelbook]
bookwide = excel.Workbooks.Add()
booklong = excel.Workbooks.Add()

DEBUG_PRINT = False
spssRow = 2

dependents = ["start", "end", "duration", "interval", "Length", "Taps",
                  "Incorrect Land and Lift","Just Incorrect Land", "Just Incorrect Lift",
                  "All incorrect Lands","Position Errors","Frequency","Tap length",
                  "Interval length","Automatic detection","Primitive Errors", "Distance to Center"]



dependentsStart = 0
dependentsEnd = 17
current = dependentsStart + 8
device = 1

wide_format = "spsswide.xlsx"
long_format = "spsslong.xlsx"

names = ['Agripina Ramos', 'Alexandre Tavares', 'Amaro Costa', 'Ana Dias', 'Ana Serodio',
'Antonio Barros', 'Antonio Goncalves', 'Antonio Pinto', 'Artur Henriques', 'Carla Alves',
'Carlos Carsane', 'Carlos Correia', 'Claudia Carmo', 'David Amorim', 'Edite Ferreira',
'Elisabete Marques', 'Isabel Cardoso', 'Isabel Soares', 'Isaurindo Fonseca', 'Joao Pedro Fonseca',
'Joao Remedios', 'Jorge Patricio', 'Jose Antonio', 'Jose Maria', 'Lina Marino', 'Luisa Cruz', 'Manuel Cardoso',
'Manuela Silva', 'Margarida Carreira', 'Maria dos Anjos', 'Maria Goreti', 'Natalia Almeida', 'Olegario Ferreira',
'Paulo Leandro', 'Paulo Magalhaes','Rebeca Campos', 'Rui Oliveira', 'Rui Silva', 'Sandra Santos', 'Sara', 'Vitor Nunes']

def debug(obj):
    if DEBUG_PRINT:
        print obj

def writeWide():
    global spssRow
    global bookwide
    global books
    global current
    for variable in dependents[dependentsStart:dependentsEnd]:
        spssSheet = bookwide.Sheets.Add()
        spssSheet.Name = variable
        print variable

        #First Row
        column = 2
        for device in ['asus', 's', 'sbevel']:
            for size in ['6', '12']:
                for primitive in ['tap', 'long', 'double']:
                    spssSheet.Cells(1, column).Value = device + '_' + size + '_' + primitive
                    column = column + 1
                        
        #First Column
        for name in names:
            spssSheet.Cells(spssRow, 1).Value = name
            spssRow = spssRow + 1
        spssRow = 2

        device = 1
        for b in books:
            user = 1
            for name in names:
                try:
                    sheet = b.Sheets[name]
                except:
                    sheet= extra.Sheets.Add()
                condition = 1
                for row in range(78, 84):
                    value = sheet.Cells(row, current).Value
                    if value == None or int(value) < 0:
                        value = ""
                    spssSheet.Cells(user + 1, device + condition ).Value = value
                    condition = condition + 1
                user = user + 1
            device = device + 6
        current = current + 1

def writeLong():
    global spssRow
    global booklong
    global books
    global current
    for variable in dependents[dependentsStart:dependentsEnd]:
        spssSheet = booklong.Sheets.Add()
        spssSheet.Name = variable
        print variable

        #First Row
        spssSheet.Cells(1, 1).Value = "Subject"
        spssSheet.Cells(1, 2).Value = "Device"
        spssSheet.Cells(1, 3).Value = "Size"
        spssSheet.Cells(1, 4).Value = "Primitive"
        spssSheet.Cells(1, 5).Value = variable
    
        devices = ['asus', 's', 'sbevel']
        sizes = ['6', '12']
        primitives = ['tap', 'long', 'double']

                        
        #First Column
        for name in names:
            spssSheet.Cells(spssRow, 1).Value = name
            spssRow = spssRow + 1
        spssRow = 2

        device = 0         
        for b in books:
            user = 1
            for name in names:
                try:
                    sheet = b.Sheets[name]
                except:
                    sheet= extra.Sheets.Add()
                condition = 1
                for row in range(78, 84):
                    value = sheet.Cells(row, current).Value
                    d = devices[device]
                    g = sheet.Cells(row, 3).Value
                    p = sheet.Cells(row, 4).Value               
                    if value == None or int(value) < 0:
                        value = ""

                    spssSheet.Cells(spssRow, 1).Value = name    
                    spssSheet.Cells(spssRow, 2).Value = d
                    spssSheet.Cells(spssRow, 3).Value = g
                    spssSheet.Cells(spssRow, 4).Value = p
                    spssSheet.Cells(spssRow, 5).Value = value                
                    spssRow = spssRow + 1 
                user = user + 1
            device = device + 1
        current = current + 1



#writeLong()
try:
    writeWide()
except:
    print "There was an error"
    pass
        
if os.path.exists(os.path.join(path, wide_format)):
    os.remove(os.path.join(path, wide_format))
bookwide.SaveAs(os.path.join(path, wide_format))

##if os.path.exists(os.path.join(path, long_format)):
##    os.remove(os.path.join(path, long_format))
##booklong.SaveAs(os.path.join(path, long_format))


spssSheet = None
sheet = None
bookwide = None
booklong = None
asusbook= None
sbook= None
sbevelbook = None
excel.Quit()
excel = None