# -*- coding: utf-8 -*-
# Author: Julien Moutinho <julm@autogeree.net>
# License: GNU GPLv3 (or later, at your choice)

from __future__ import unicode_literals
from functools import reduce
import datetime
import os
import random
import shutil
import uno
from com.sun.star.awt.MessageBoxType    import MESSAGEBOX, INFOBOX, WARNINGBOX, ERRORBOX, QUERYBOX
from com.sun.star.awt.MessageBoxButtons import BUTTONS_OK, BUTTONS_OK_CANCEL, BUTTONS_YES_NO, BUTTONS_YES_NO_CANCEL, BUTTONS_RETRY_CANCEL, BUTTONS_ABORT_IGNORE_RETRY
from com.sun.star.awt.MessageBoxResults import OK, YES, NO, CANCEL
from com.sun.star.beans import PropertyValue
from com.sun.star.sheet import CellFlags

if 'XSCRIPTCONTEXT' in globals():
	def getModel():
		return (XSCRIPTCONTEXT.getDocument(), XSCRIPTCONTEXT.getComponentContext())
	def debug(msg):
		return
else:
	from connect_to_libre_office import getModel
	def debug(msg):
		print(msg)

def MakeBallots(*args):
	(model, context) = getModel()
	FunctionAccess = context.ServiceManager.createInstanceWithContext("com.sun.star.sheet.FunctionAccess", context)
	today = datetime.date.today()
	ballotSheet  = model.Sheets[0]
	ballotsSheet = model.Sheets[1]
	ballotDate   = today.strftime("%Y-%m")
	ballotIDCell = ballotSheet.getCellRangeByName("bulletinID")
	gradesRange  = ballotSheet.getCellRangeByName("mentions")
	choicesRange = ballotSheet.getCellRangeByName("choix")
	
	# Number of ballots
	ballots = intOfCell(ballotsSheet.getCellByPosition(0, 0).Value)
	if not (ballots > 0):
		MsgBox(ERRORBOX, BUTTONS_OK, "MakeBallots", \
		 "ERROR: set $"+ballotsSheet.Name+"$A$1 to the number of ballots.")
		return -1
	
	# Set bulletins NamedRange
	ballotsRange = ballotsSheet.getCellRangeByPosition(0, 4, 0, 4 + ballots - 1)
	model.NamedRanges.removeByName("bulletins")
	model.NamedRanges.addNewByName \
	 ( "bulletins"
	 , ballotsRange.AbsoluteName
	 , ballotsSheet.getCellByPosition(0,0).CellAddress
	 , 0 )
	
	grades  = gradesRange.DataArray[0]
	choices = choicesRange.DataArray
	ballotsHeaderGrades      = ["Mentions :"]          + list(grades * len(choices))
	ballotsHeaderChoices     = [ballots]               + ([""] * (len(ballotsHeaderGrades) - 1))
	ballotsHeaderSumByChoice = ["Somme par choix :"]   + ([""] * (len(ballotsHeaderGrades) - 1))
	ballotsHeaderSumByGrade  = ["Somme par mention :"] + ([""] * (len(ballotsHeaderGrades) - 1))
	for choice in range(len(choices)):
		ballotsHeaderChoices[1 + choice * len(grades)] = choices[choice][0]
	
	ballotsHeader = \
	 [ ballotsHeaderChoices
	 , ballotsHeaderSumByChoice
	 , ballotsHeaderSumByGrade
	 , ballotsHeaderGrades ]
	ballotsList = []
	ballotDir   = "./bulletins"
	rm(ballotDir)
	mkdir(ballotDir)
	IDdigits = 8
	randomIDs = random.sample(range(0, 10 ** IDdigits - 1), ballots)
	for i in range(1,ballots+1):
		ballot = randomIDs.pop()
		ballotID = "SS-"+ballotDate+"-"+("%0*i" % (IDdigits, ballot))
		ballotIDCell.String = ballotID
		ballotsList.append([ballotID] + [""] * len(grades) * len(choices))
		debug("ballotID:"+str(ballotID))
		exportPDF(model, ballotSheet, os.getcwd()+"/"+ballotDir+"/"+("%0*i" % (IDdigits, ballot))+".pdf")
	ballotsList.sort(key=lambda data: data[0])
	ballotIDCell.String = "<identifiant-anonyme>"
	clearSheet(ballotsSheet)
	ballotsData = ballotsHeader + ballotsList
	ballotsRange = ballotsSheet.getCellRangeByPosition \
	 ( 0, 0, len(ballotsData[0]) - 1, len(ballotsData) - 1 )
	ballotsRange.DataArray = ballotsData
	
	# ballotsSheet headers
	headerCell = ballotsSheet.getCellByPosition(0, 0)
	headerCell.HoriJustify = 2
	for header in range(1,4):
		headerCell = ballotsSheet.getCellByPosition(0, header)
		headerCell.HoriJustify = 3
	
	for choice in range(len(choices)):
		# Merge choice title
		choiceCellOrig = choicesRange.getCellByPosition(0, choice)
		choiceRange = ballotsSheet.getCellRangeByPosition \
		 ( 1 + choice * len(grades), 0
		 , 1 + choice * len(grades) + len(grades) - 1, 0 )
		choiceRange.merge(True)
		choiceRange.CellBackColor = choiceCellOrig.CellBackColor
		choiceRange.CharWeight    = choiceCellOrig.CharWeight
		choiceRange.HoriJustify   = 1
		choiceRange.VertJustify   = 1
		
		# Merge SUM() by choice
		startSum = FunctionAccess.callFunction("ADDRESS", [3, 2 + choice * len(grades), 4])
		endSum   = FunctionAccess.callFunction("ADDRESS", [3, 2 + choice * len(grades) + len(grades) - 1, 4])
		sumByGradeCell = ballotsSheet.getCellByPosition \
		 ( 1 + choice * len(grades), 1 )
		sumByGradeCell.Formula = "=SUM(%s:%s)" % (startSum, endSum)
		sumByChoiceRange = ballotsSheet.getCellRangeByPosition \
		 ( 1 + choice * len(grades), 1
		 , 1 + choice * len(grades) + len(grades) - 1, 1 )
		sumByChoiceRange.merge(True)
		
		for grade in range(len(grades)):
			# Copy grade color
			gradeCellOrig = gradesRange.getCellByPosition(grade, 0)
			gradeCell = ballotsSheet.getCellByPosition \
			 ( 1 + choice * len(grades) + grade, 3 )
			gradeCell.CellBackColor = gradeCellOrig.CellBackColor
			gradeCell.CharWeight    = gradeCellOrig.CharWeight
			
			# Write SUM() by grade
			startSum = FunctionAccess.callFunction("ADDRESS", [5, 2 + choice * len(grades) + grade, 4])
			endSum   = FunctionAccess.callFunction("ADDRESS", [5 + len(ballotsList) - 1, 2 + choice * len(grades) + grade, 4])
			sumByGradeCell = ballotsSheet.getCellByPosition \
			 ( 1 + choice * len(grades) + grade, 2 )
			sumByGradeCell.Formula = "=SUM(%s:%s)" % (startSum, endSum)

def MakeResults(*args):
	(model, context) = getModel()
	
	ballotSheet  = model.Sheets[0]
	ballotsSheet = model.Sheets[1]
	valueSheet   = model.Sheets[2]
	rankingSheet = model.Sheets[3]
	
	gradesRange  = ballotSheet.getCellRangeByName("mentions")
	choicesRange = ballotSheet.getCellRangeByName("choix")
	ballotsRange = ballotsSheet.getCellRangeByName("bulletins")
	
	grades     = gradesRange.DataArray[0]
	choices    = list(map(lambda x: x[0], choicesRange.DataArray))
	ballots    = list(map(lambda x: x[0], ballotsRange.DataArray))
	lenGrades  = len(grades)
	lenChoices = len(choices)
	lenBallots = len(ballots)
	
	sumByChoice = ballotsSheet.getCellRangeByPosition \
	 (1, 1, lenChoices * lenGrades, 1).DataArray[0]
	sumByGrade = ballotsSheet.getCellRangeByPosition \
	 (1, 2, lenChoices * lenGrades, 2).DataArray[0]
	debug("sumByChoice:"+str(sumByChoice))
	debug("sumByGrade:"+str(sumByGrade))
	
	clearSheet(valueSheet)
	clearSheet(rankingSheet)
	
	valueData = []
	sumByGradeByChoice = []
	for choice in range(lenChoices):
		debug("choice:"+str(choices[choice]))
		choiceGrades = list(map(intOfCell, sumByGrade[choice*lenGrades:choice*lenGrades + lenGrades]))
		sumByGradeByChoice.append([choices[choice]] + choiceGrades.copy())
		debug("  choiceGrades:"+str(choiceGrades))
		choiceBallots = int(sumByChoice[choice*lenGrades])
		debug("  choiceBallots:"+str(choiceBallots))
		
		# Check ballots counting consistency
		if choiceBallots != lenBallots:
			MsgBox(ERRORBOX, BUTTONS_OK, "MakeResults", \
			 "Choice "+str(choices[choice])+" has "+str(int(choiceBallots))+" ballots" \
			 + "\nbut the total number of ballots is "+str(lenBallots)+"." \
			 + "\nAborting. Fix the ballots counting and retry.")
			return -1
		
		# Seek medianGrade
		nJudgesLower = 0
		medianGrade  = -1
		while nJudgesLower*2 < lenBallots:
			medianGrade = medianGrade + 1
			nJudgesLower = nJudgesLower + intOfCell(choiceGrades[medianGrade])
		debug("  medianGrade:"+str(medianGrade))
		
		# Extend choiceValue with the overflow of the lower judgments
		choiceValue = [choices[choice]]
		ballot = 1
		lowerMedianGrade  = medianGrade
		higherMedianGrade = medianGrade + 1
		nJudgesHigher = lenBallots - nJudgesLower
		while nJudgesLower > nJudgesHigher:
			while intOfCell(choiceGrades[lowerMedianGrade]) == 0:
				lowerMedianGrade -= 1
			choiceGrades[lowerMedianGrade] -= 1
			choiceValue.append(lowerMedianGrade)
			nJudgesLower -= 1
			ballot += 1
		
		# Extend choiceValue by alterning between lower and higher judgments
		while ballot < lenBallots:
			ballot += 2
			while lowerMedianGrade > 0 and intOfCell(choiceGrades[lowerMedianGrade]) == 0:
				lowerMedianGrade -= 1
			while higherMedianGrade < lenGrades - 1 and intOfCell(choiceGrades[higherMedianGrade]) == 0:
				higherMedianGrade += 1
			choiceValue.append(lowerMedianGrade)
			choiceValue.append(higherMedianGrade)
			choiceGrades[lowerMedianGrade]  -= 1
			choiceGrades[higherMedianGrade] -= 1
		
		# Fill valueData
		valueData.append(choiceValue)
	
	# Rank
	rankingData = list(zip(sumByGradeByChoice, valueData))
	rankingData.sort(key=lambda data: data[1][1:lenBallots+1])
	rankingData.reverse()
	valueData = list(map(lambda data: data[1], rankingData))
	rankingData = [[""] + list(grades)] + list(map(lambda data: \
		map(lambda c: "" if c == 0 else c, data[0]), rankingData))
	rankingRange = rankingSheet.getCellRangeByPosition \
	 ( 0, 0, len(rankingData[0]) - 1, len(rankingData) - 1 )
	rankingRange.DataArray = rankingData
	
	# rankingHeader: choices
	rankingChoicesRange = rankingSheet.getCellRangeByPosition(0, 1, 0, lenChoices)
	for choice in range(lenChoices):
		rankingChoiceCell = rankingChoicesRange.getCellByPosition(0, choice)
		choiceCell        = choicesRange.getCellByPosition(0, choice)
		rankingChoiceCell.CellBackColor = choiceCell.CellBackColor
		rankingChoiceCell.CharWeight    = choiceCell.CharWeight
		rankingChoiceCell.HoriJustify   = 1
		rankingChoiceCell.VertJustify   = 1
	
	# rankingHeader: grades
	rankingGradesRange = rankingSheet.getCellRangeByPosition(1, 0, lenGrades, 0)
	for grade in range(lenGrades):
		rankingGradeCell = rankingGradesRange.getCellByPosition(grade, 0)
		choiceCell       = gradesRange.getCellByPosition(grade, 0)
		rankingGradeCell.CellBackColor = choiceCell.CellBackColor
		rankingGradeCell.CharWeight    = choiceCell.CharWeight
		rankingGradeCell.HoriJustify   = 1
		rankingGradeCell.VertJustify   = 1
	
	# Color values
	valueDataRange = valueSheet.getCellRangeByPosition(2, 1, 2 + lenBallots - 1, lenChoices)
	for choice in range(lenChoices):
		for ballot in range(lenBallots):
			grade = valueData[choice][1+ballot]
			valueCell = valueDataRange.getCellByPosition(ballot, choice)
			gradeCell = gradesRange.getCellByPosition(grade, 0)
			valueCell.CellBackColor = gradeCell.CellBackColor
	
	# Compute rank
	for value in valueData:
		base = lenGrades
		debug("base:"+str(base))
		digits = list(map(lambda v: v, value[1:lenBallots+1]))
		rank = inBase(base, digits)
		# FIXME: this algorithm is completely wrong,
		# see https://hackage.haskell.org/package/hjugement-2.0.2.20190414/docs/Majority-Rank.html
		rankMax = base ** lenBallots - 1
		debug("digits:"+str(digits))
		debug("rank:"+str(rank))
		debug("rankMax:"+str(rankMax))
		value.insert(1, rank / rankMax)
	
	valueRange = valueSheet.getCellRangeByPosition \
	 ( 0, 1, len(valueData[0]) - 1, len(valueData) )
	valueRange.DataArray = valueData
	
	# valueHeader: choice
	valueHeader             = valueSheet.getCellByPosition(0, 0)
	valueHeader.HoriJustify = 2
	valueHeader.CharWeight  = 150
	valueHeader.String      = "Choix"
	
	# valueHeader: choices
	valueChoicesRange = valueSheet.getCellRangeByPosition(0, 1, 0, lenChoices)
	for choice in range(lenChoices):
		valueChoiceCell = valueChoicesRange.getCellByPosition(0, choice)
		choiceCell      = choicesRange.getCellByPosition(0, choice)
		valueChoiceCell.CellBackColor = choiceCell.CellBackColor
		valueChoiceCell.CharWeight    = choiceCell.CharWeight
		valueChoiceCell.HoriJustify   = 1
		valueChoiceCell.VertJustify   = 1
	
	# valueHeader: rank
	valueHeader             = valueSheet.getCellByPosition(1, 0)
	valueHeader.HoriJustify = 2
	valueHeader.CharWeight  = 150
	valueHeader.String      = "Rang"
	
	# valueHeader: values
	valueHeader = valueSheet.getCellRangeByPosition(2, 0, 2 + lenBallots - 1, 0)
	valueHeader.merge(True)
	valueHeader             = valueSheet.getCellByPosition(2, 0)
	valueHeader.HoriJustify = 2
	valueHeader.CharWeight  = 150
	valueHeader.String      = "Valeur"

def inBase(base, digits):
	acc = 0
	for digit in digits:
		acc = digit + (base * acc)
	return acc

def intOfCell(cell):
	return int(cell) if cell != '' else 0
def sumCells(cells):
	return reduce(lambda x,y: intOfCell(x) + intOfCell(y), cells)
def getUsedArea(sheet):
	cursor = sheet.createCursor()
	cursor.gotoStartOfUsedArea(False)
	cursor.gotoEndOfUsedArea(True)
	return cursor
def clearSheet(sheet):
	cursor = sheet.createCursor()
	cursor.gotoStartOfUsedArea(False)
	cursor.gotoEndOfUsedArea(True)
	rangeAddress = cursor.RangeAddress
	range = sheet.getCellRangeByPosition \
	 ( rangeAddress.StartColumn
	 , rangeAddress.StartRow
	 , rangeAddress.EndColumn
	 , rangeAddress.EndRow )
	flags = CellFlags.VALUE | \
	        CellFlags.DATETIME | \
	        CellFlags.STRING | \
	        CellFlags.ANNOTATION | \
	        CellFlags.FORMULA | \
	        CellFlags.HARDATTR | \
	        CellFlags.STYLES | \
	        CellFlags.OBJECTS | \
	        CellFlags.EDITATTR
	range.clearContents(flags)
def MsgBox(msgtype, buttons, title, message):
	(model, context) = getModel()
	parentwin = model.CurrentController.Frame.ContainerWindow
	toolkit = parentwin.getToolkit()
	msgbox = toolkit.createMessageBox(parentwin, msgtype, buttons, title, message)
	ret = msgbox.execute()
	msgbox.dispose()
	return ret

def rm(path):
	if os.path.exists(path):
		shutil.rmtree(path)
def mkdir(path):
	if not os.path.exists(path):
		os.makedirs(path)
def exportPDF(model, ballotSheet, ballotOutput):
	# DOC: https://wiki.openoffice.org/wiki/API/Tutorials/PDF_export
	model.storeToURL \
	 ( "file://"+ballotOutput
	 , ( PropertyValue("FilterName", 0, "calc_pdf_Export", 0)
	   , PropertyValue \
	     ( "FilterData", 0
	     , uno.Any( "[]com.sun.star.beans.PropertyValue" \
	              , ( PropertyValue \
	                   ( "Selection", 0
	                   , ballotSheet.getCellRangeByName("bulletin"), 0 )
	                , ) )
	     , 0 )
	   , PropertyValue("Overwrite", 0, True, 0)
	   , PropertyValue("SelectPdfVersion", 0, 0, 0) # PDF 1.4
	   )
	 )

g_exportedScripts = MakeBallots, MakeResults