]> Git — Sourcephile - reloto-libreoffice.git/blob - jugements.py
Ajout début de macro pour tirages au sort publiquement vérifiables.
[reloto-libreoffice.git] / 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