jugements.py

   1 # -*- coding: utf-8 -*-
   2 # Author: Julien Moutinho <julm@autogeree.net>
   3 # License: GNU GPLv3 (or later, at your choice)
   4
   5 from __future__ import unicode_literals
   6 from functools import reduce
   7 import datetime
   8 import os
   9 import random
  10 import shutil
  11 import uno
  12 from com.sun.star.awt.MessageBoxType    import MESSAGEBOX, INFOBOX, WARNINGBOX, ERRORBOX, QUERYBOX
  13 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
  14 from com.sun.star.awt.MessageBoxResults import OK, YES, NO, CANCEL
  15 from com.sun.star.beans import PropertyValue
  16 from com.sun.star.sheet import CellFlags
  17
  18 if 'XSCRIPTCONTEXT' in globals():
  19         def getModel():
  20                 return (XSCRIPTCONTEXT.getDocument(), XSCRIPTCONTEXT.getComponentContext())
  21         def debug(msg):
  22                 return
  23 else:
  24         from connect_to_libre_office import getModel
  25         def debug(msg):
  26                 print(msg)
  27
  28 def MakeBallots(*args):
  29         (model, context) = getModel()
  30         FunctionAccess = context.ServiceManager.createInstanceWithContext("com.sun.star.sheet.FunctionAccess", context)
  31         today = datetime.date.today()
  32         ballotSheet  = model.Sheets[0]
  33         ballotsSheet = model.Sheets[1]
  34         ballotDate   = today.strftime("%Y-%m")
  35         ballotIDCell = ballotSheet.getCellRangeByName("bulletinID")
  36         gradesRange  = ballotSheet.getCellRangeByName("mentions")
  37         choicesRange = ballotSheet.getCellRangeByName("choix")
  38
  39         # Number of ballots
  40         ballots = intOfCell(ballotsSheet.getCellByPosition(0, 0).Value)
  41         if not (ballots > 0):
  42                 MsgBox(ERRORBOX, BUTTONS_OK, "MakeBallots", \
  43                  "ERROR: set $"+ballotsSheet.Name+"$A$1 to the number of ballots.")
  44                 return -1
  45
  46         # Set bulletins NamedRange
  47         ballotsRange = ballotsSheet.getCellRangeByPosition(0, 4, 0, 4 + ballots - 1)
  48         model.NamedRanges.removeByName("bulletins")
  49         model.NamedRanges.addNewByName \
  50          ( "bulletins"
  51          , ballotsRange.AbsoluteName
  52          , ballotsSheet.getCellByPosition(0,0).CellAddress
  53          , 0 )
  54
  55         grades  = gradesRange.DataArray[0]
  56         choices = choicesRange.DataArray
  57         ballotsHeaderGrades      = ["Mentions :"]          + list(grades * len(choices))
  58         ballotsHeaderChoices     = [ballots]               + ([""] * (len(ballotsHeaderGrades) - 1))
  59         ballotsHeaderSumByChoice = ["Somme par choix :"]   + ([""] * (len(ballotsHeaderGrades) - 1))
  60         ballotsHeaderSumByGrade  = ["Somme par mention :"] + ([""] * (len(ballotsHeaderGrades) - 1))
  61         for choice in range(len(choices)):
  62                 ballotsHeaderChoices[1 + choice * len(grades)] = choices[choice][0]
  63
  64         ballotsHeader = \
  65          [ ballotsHeaderChoices
  66          , ballotsHeaderSumByChoice
  67          , ballotsHeaderSumByGrade
  68          , ballotsHeaderGrades ]
  69         ballotsList = []
  70         ballotDir   = "./bulletins"
  71         rm(ballotDir)
  72         mkdir(ballotDir)
  73         IDdigits = 8
  74         randomIDs = random.sample(range(0, 10 ** IDdigits - 1), ballots)
  75         for i in range(1,ballots+1):
  76                 ballot = randomIDs.pop()
  77                 ballotID = "SS-"+ballotDate+"-"+("%0*i" % (IDdigits, ballot))
  78                 ballotIDCell.String = ballotID
  79                 ballotsList.append([ballotID] + [""] * len(grades) * len(choices))
  80                 debug("ballotID:"+str(ballotID))
  81                 exportPDF(model, ballotSheet, os.getcwd()+"/"+ballotDir+"/"+("%0*i" % (IDdigits, ballot))+".pdf")
  82         ballotsList.sort(key=lambda data: data[0])
  83         ballotIDCell.String = "<identifiant-anonyme>"
  84         clearSheet(ballotsSheet)
  85         ballotsData = ballotsHeader + ballotsList
  86         ballotsRange = ballotsSheet.getCellRangeByPosition \
  87          ( 0, 0, len(ballotsData[0]) - 1, len(ballotsData) - 1 )
  88         ballotsRange.DataArray = ballotsData
  89
  90         # ballotsSheet headers
  91         headerCell = ballotsSheet.getCellByPosition(0, 0)
  92         headerCell.HoriJustify = 2
  93         for header in range(1,4):
  94                 headerCell = ballotsSheet.getCellByPosition(0, header)
  95                 headerCell.HoriJustify = 3
  96
  97         for choice in range(len(choices)):
  98                 # Merge choice title
  99                 choiceCellOrig = choicesRange.getCellByPosition(0, choice)
 100                 choiceRange = ballotsSheet.getCellRangeByPosition \
 101                  ( 1 + choice * len(grades), 0
 102                  , 1 + choice * len(grades) + len(grades) - 1, 0 )
 103                 choiceRange.merge(True)
 104                 choiceRange.CellBackColor = choiceCellOrig.CellBackColor
 105                 choiceRange.CharWeight    = choiceCellOrig.CharWeight
 106                 choiceRange.HoriJustify   = 1
 107                 choiceRange.VertJustify   = 1
 108
 109                 # Merge SUM() by choice
 110                 startSum = FunctionAccess.callFunction("ADDRESS", [3, 2 + choice * len(grades), 4])
 111                 endSum   = FunctionAccess.callFunction("ADDRESS", [3, 2 + choice * len(grades) + len(grades) - 1, 4])
 112                 sumByGradeCell = ballotsSheet.getCellByPosition \
 113                  ( 1 + choice * len(grades), 1 )
 114                 sumByGradeCell.Formula = "=SUM(%s:%s)" % (startSum, endSum)
 115                 sumByChoiceRange = ballotsSheet.getCellRangeByPosition \
 116                  ( 1 + choice * len(grades), 1
 117                  , 1 + choice * len(grades) + len(grades) - 1, 1 )
 118                 sumByChoiceRange.merge(True)
 119
 120                 for grade in range(len(grades)):
 121                         # Copy grade color
 122                         gradeCellOrig = gradesRange.getCellByPosition(grade, 0)
 123                         gradeCell = ballotsSheet.getCellByPosition \
 124                          ( 1 + choice * len(grades) + grade, 3 )
 125                         gradeCell.CellBackColor = gradeCellOrig.CellBackColor
 126                         gradeCell.CharWeight    = gradeCellOrig.CharWeight
 127
 128                         # Write SUM() by grade
 129                         startSum = FunctionAccess.callFunction("ADDRESS", [5, 2 + choice * len(grades) + grade, 4])
 130                         endSum   = FunctionAccess.callFunction("ADDRESS", [5 + len(ballotsList) - 1, 2 + choice * len(grades) + grade, 4])
 131                         sumByGradeCell = ballotsSheet.getCellByPosition \
 132                          ( 1 + choice * len(grades) + grade, 2 )
 133                         sumByGradeCell.Formula = "=SUM(%s:%s)" % (startSum, endSum)
 134
 135 def MakeResults(*args):
 136         (model, context) = getModel()
 137
 138         ballotSheet  = model.Sheets[0]
 139         ballotsSheet = model.Sheets[1]
 140         valueSheet   = model.Sheets[2]
 141         rankingSheet = model.Sheets[3]
 142
 143         gradesRange  = ballotSheet.getCellRangeByName("mentions")
 144         choicesRange = ballotSheet.getCellRangeByName("choix")
 145         ballotsRange = ballotsSheet.getCellRangeByName("bulletins")
 146
 147         grades     = gradesRange.DataArray[0]
 148         choices    = list(map(lambda x: x[0], choicesRange.DataArray))
 149         ballots    = list(map(lambda x: x[0], ballotsRange.DataArray))
 150         lenGrades  = len(grades)
 151         lenChoices = len(choices)
 152         lenBallots = len(ballots)
 153
 154         sumByChoice = ballotsSheet.getCellRangeByPosition \
 155          (1, 1, lenChoices * lenGrades, 1).DataArray[0]
 156         sumByGrade = ballotsSheet.getCellRangeByPosition \
 157          (1, 2, lenChoices * lenGrades, 2).DataArray[0]
 158         debug("sumByChoice:"+str(sumByChoice))
 159         debug("sumByGrade:"+str(sumByGrade))
 160
 161         clearSheet(valueSheet)
 162         clearSheet(rankingSheet)
 163
 164         valueData = []
 165         sumByGradeByChoice = []
 166         for choice in range(lenChoices):
 167                 debug("choice:"+str(choices[choice]))
 168                 choiceGrades = list(map(intOfCell, sumByGrade[choice*lenGrades:choice*lenGrades + lenGrades]))
 169                 sumByGradeByChoice.append([choices[choice]] + choiceGrades.copy())
 170                 debug("  choiceGrades:"+str(choiceGrades))
 171                 choiceBallots = int(sumByChoice[choice*lenGrades])
 172                 debug("  choiceBallots:"+str(choiceBallots))
 173
 174                 # Check ballots counting consistency
 175                 if choiceBallots != lenBallots:
 176                         MsgBox(ERRORBOX, BUTTONS_OK, "MakeResults", \
 177                          "Choice "+str(choices[choice])+" has "+str(int(choiceBallots))+" ballots" \
 178                          + "\nbut the total number of ballots is "+str(lenBallots)+"." \
 179                          + "\nAborting. Fix the ballots counting and retry.")
 180                         return -1
 181
 182                 # Seek medianGrade
 183                 nJudgesLower = 0
 184                 medianGrade  = -1
 185                 while nJudgesLower*2 < lenBallots:
 186                         medianGrade = medianGrade + 1
 187                         nJudgesLower = nJudgesLower + intOfCell(choiceGrades[medianGrade])
 188                 debug("  medianGrade:"+str(medianGrade))
 189
 190                 # Extend choiceValue with the overflow of the lower judgments
 191                 choiceValue = [choices[choice]]
 192                 ballot = 1
 193                 lowerMedianGrade  = medianGrade
 194                 higherMedianGrade = medianGrade + 1
 195                 nJudgesHigher = lenBallots - nJudgesLower
 196                 while nJudgesLower > nJudgesHigher:
 197                         while intOfCell(choiceGrades[lowerMedianGrade]) == 0:
 198                                 lowerMedianGrade -= 1
 199                         choiceGrades[lowerMedianGrade] -= 1
 200                         choiceValue.append(lowerMedianGrade)
 201                         nJudgesLower -= 1
 202                         ballot += 1
 203
 204                 # Extend choiceValue by alterning between lower and higher judgments
 205                 while ballot < lenBallots:
 206                         ballot += 2
 207                         while lowerMedianGrade > 0 and intOfCell(choiceGrades[lowerMedianGrade]) == 0:
 208                                 lowerMedianGrade -= 1
 209                         while higherMedianGrade < lenGrades - 1 and intOfCell(choiceGrades[higherMedianGrade]) == 0:
 210                                 higherMedianGrade += 1
 211                         choiceValue.append(lowerMedianGrade)
 212                         choiceValue.append(higherMedianGrade)
 213                         choiceGrades[lowerMedianGrade]  -= 1
 214                         choiceGrades[higherMedianGrade] -= 1
 215
 216                 # Fill valueData
 217                 valueData.append(choiceValue)
 218
 219         # Rank
 220         rankingData = list(zip(sumByGradeByChoice, valueData))
 221         rankingData.sort(key=lambda data: data[1][1:lenBallots+1])
 222         rankingData.reverse()
 223         valueData = list(map(lambda data: data[1], rankingData))
 224         rankingData = [[""] + list(grades)] + list(map(lambda data: \
 225                 map(lambda c: "" if c == 0 else c, data[0]), rankingData))
 226         rankingRange = rankingSheet.getCellRangeByPosition \
 227          ( 0, 0, len(rankingData[0]) - 1, len(rankingData) - 1 )
 228         rankingRange.DataArray = rankingData
 229
 230         # rankingHeader: choices
 231         rankingChoicesRange = rankingSheet.getCellRangeByPosition(0, 1, 0, lenChoices)
 232         for choice in range(lenChoices):
 233                 rankingChoiceCell = rankingChoicesRange.getCellByPosition(0, choice)
 234                 choiceCell        = choicesRange.getCellByPosition(0, choice)
 235                 rankingChoiceCell.CellBackColor = choiceCell.CellBackColor
 236                 rankingChoiceCell.CharWeight    = choiceCell.CharWeight
 237                 rankingChoiceCell.HoriJustify   = 1
 238                 rankingChoiceCell.VertJustify   = 1
 239
 240         # rankingHeader: grades
 241         rankingGradesRange = rankingSheet.getCellRangeByPosition(1, 0, lenGrades, 0)
 242         for grade in range(lenGrades):
 243                 rankingGradeCell = rankingGradesRange.getCellByPosition(grade, 0)
 244                 choiceCell       = gradesRange.getCellByPosition(grade, 0)
 245                 rankingGradeCell.CellBackColor = choiceCell.CellBackColor
 246                 rankingGradeCell.CharWeight    = choiceCell.CharWeight
 247                 rankingGradeCell.HoriJustify   = 1
 248                 rankingGradeCell.VertJustify   = 1
 249
 250         # Color values
 251         valueDataRange = valueSheet.getCellRangeByPosition(2, 1, 2 + lenBallots - 1, lenChoices)
 252         for choice in range(lenChoices):
 253                 for ballot in range(lenBallots):
 254                         grade = valueData[choice][1+ballot]
 255                         valueCell = valueDataRange.getCellByPosition(ballot, choice)
 256                         gradeCell = gradesRange.getCellByPosition(grade, 0)
 257                         valueCell.CellBackColor = gradeCell.CellBackColor
 258
 259         # Compute rank
 260         for value in valueData:
 261                 base = lenGrades
 262                 debug("base:"+str(base))
 263                 digits = list(map(lambda v: v, value[1:lenBallots+1]))
 264                 rank = inBase(base, digits)
 265                 # FIXME: this algorithm is completely wrong,
 266                 # see https://hackage.haskell.org/package/hjugement-2.0.2.20190414/docs/Majority-Rank.html
 267                 rankMax = base ** lenBallots - 1
 268                 debug("digits:"+str(digits))
 269                 debug("rank:"+str(rank))
 270                 debug("rankMax:"+str(rankMax))
 271                 value.insert(1, rank / rankMax)
 272
 273         valueRange = valueSheet.getCellRangeByPosition \
 274          ( 0, 1, len(valueData[0]) - 1, len(valueData) )
 275         valueRange.DataArray = valueData
 276
 277         # valueHeader: choice
 278         valueHeader             = valueSheet.getCellByPosition(0, 0)
 279         valueHeader.HoriJustify = 2
 280         valueHeader.CharWeight  = 150
 281         valueHeader.String      = "Choix"
 282
 283         # valueHeader: choices
 284         valueChoicesRange = valueSheet.getCellRangeByPosition(0, 1, 0, lenChoices)
 285         for choice in range(lenChoices):
 286                 valueChoiceCell = valueChoicesRange.getCellByPosition(0, choice)
 287                 choiceCell      = choicesRange.getCellByPosition(0, choice)
 288                 valueChoiceCell.CellBackColor = choiceCell.CellBackColor
 289                 valueChoiceCell.CharWeight    = choiceCell.CharWeight
 290                 valueChoiceCell.HoriJustify   = 1
 291                 valueChoiceCell.VertJustify   = 1
 292
 293         # valueHeader: rank
 294         valueHeader             = valueSheet.getCellByPosition(1, 0)
 295         valueHeader.HoriJustify = 2
 296         valueHeader.CharWeight  = 150
 297         valueHeader.String      = "Rang"
 298
 299         # valueHeader: values
 300         valueHeader = valueSheet.getCellRangeByPosition(2, 0, 2 + lenBallots - 1, 0)
 301         valueHeader.merge(True)
 302         valueHeader             = valueSheet.getCellByPosition(2, 0)
 303         valueHeader.HoriJustify = 2
 304         valueHeader.CharWeight  = 150
 305         valueHeader.String      = "Valeur"
 306
 307 def inBase(base, digits):
 308         acc = 0
 309         for digit in digits:
 310                 acc = digit + (base * acc)
 311         return acc
 312
 313 def intOfCell(cell):
 314         return int(cell) if cell != '' else 0
 315 def sumCells(cells):
 316         return reduce(lambda x,y: intOfCell(x) + intOfCell(y), cells)
 317 def getUsedArea(sheet):
 318         cursor = sheet.createCursor()
 319         cursor.gotoStartOfUsedArea(False)
 320         cursor.gotoEndOfUsedArea(True)
 321         return cursor
 322 def clearSheet(sheet):
 323         cursor = sheet.createCursor()
 324         cursor.gotoStartOfUsedArea(False)
 325         cursor.gotoEndOfUsedArea(True)
 326         rangeAddress = cursor.RangeAddress
 327         range = sheet.getCellRangeByPosition \
 328          ( rangeAddress.StartColumn
 329          , rangeAddress.StartRow
 330          , rangeAddress.EndColumn
 331          , rangeAddress.EndRow )
 332         flags = CellFlags.VALUE | \
 333                 CellFlags.DATETIME | \
 334                 CellFlags.STRING | \
 335                 CellFlags.ANNOTATION | \
 336                 CellFlags.FORMULA | \
 337                 CellFlags.HARDATTR | \
 338                 CellFlags.STYLES | \
 339                 CellFlags.OBJECTS | \
 340                 CellFlags.EDITATTR
 341         range.clearContents(flags)
 342 def MsgBox(msgtype, buttons, title, message):
 343         (model, context) = getModel()
 344         parentwin = model.CurrentController.Frame.ContainerWindow
 345         toolkit = parentwin.getToolkit()
 346         msgbox = toolkit.createMessageBox(parentwin, msgtype, buttons, title, message)
 347         ret = msgbox.execute()
 348         msgbox.dispose()
 349         return ret
 350
 351 def rm(path):
 352         if os.path.exists(path):
 353                 shutil.rmtree(path)
 354 def mkdir(path):
 355         if not os.path.exists(path):
 356                 os.makedirs(path)
 357 def exportPDF(model, ballotSheet, ballotOutput):
 358         # DOC: https://wiki.openoffice.org/wiki/API/Tutorials/PDF_export
 359         model.storeToURL \
 360          ( "file://"+ballotOutput
 361          , ( PropertyValue("FilterName", 0, "calc_pdf_Export", 0)
 362            , PropertyValue \
 363              ( "FilterData", 0
 364              , uno.Any( "[]com.sun.star.beans.PropertyValue" \
 365                       , ( PropertyValue \
 366                            ( "Selection", 0
 367                            , ballotSheet.getCellRangeByName("bulletin"), 0 )
 368                         , ) )
 369              , 0 )
 370            , PropertyValue("Overwrite", 0, True, 0)
 371            , PropertyValue("SelectPdfVersion", 0, 0, 0) # PDF 1.4
 372            )
 373          )
 374
 375 g_exportedScripts = MakeBallots, MakeResults