jugements.py

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