]> Git — Sourcephile - reloto-libreoffice.git/blob - jugements.py
Polissage documents Jugement Majoritaire.
[reloto-libreoffice.git] / 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 rankMax = base ** lenBallots - 1
266 debug("digits:"+str(digits))
267 debug("rank:"+str(rank))
268 debug("rankMax:"+str(rankMax))
269 value.insert(1, rank / rankMax)
270
271 valueRange = valueSheet.getCellRangeByPosition \
272 ( 0, 1, len(valueData[0]) - 1, len(valueData) )
273 valueRange.DataArray = valueData
274
275 # valueHeader: choice
276 valueHeader = valueSheet.getCellByPosition(0, 0)
277 valueHeader.HoriJustify = 2
278 valueHeader.CharWeight = 150
279 valueHeader.String = "Choix"
280
281 # valueHeader: choices
282 valueChoicesRange = valueSheet.getCellRangeByPosition(0, 1, 0, lenChoices)
283 for choice in range(lenChoices):
284 valueChoiceCell = valueChoicesRange.getCellByPosition(0, choice)
285 choiceCell = choicesRange.getCellByPosition(0, choice)
286 valueChoiceCell.CellBackColor = choiceCell.CellBackColor
287 valueChoiceCell.CharWeight = choiceCell.CharWeight
288 valueChoiceCell.HoriJustify = 1
289 valueChoiceCell.VertJustify = 1
290
291 # valueHeader: rank
292 valueHeader = valueSheet.getCellByPosition(1, 0)
293 valueHeader.HoriJustify = 2
294 valueHeader.CharWeight = 150
295 valueHeader.String = "Rang"
296
297 # valueHeader: values
298 valueHeader = valueSheet.getCellRangeByPosition(2, 0, 2 + lenBallots - 1, 0)
299 valueHeader.merge(True)
300 valueHeader = valueSheet.getCellByPosition(2, 0)
301 valueHeader.HoriJustify = 2
302 valueHeader.CharWeight = 150
303 valueHeader.String = "Valeur"
304
305 def inBase(base, digits):
306 acc = 0
307 for digit in digits:
308 acc = digit + (base * acc)
309 return acc
310
311 def intOfCell(cell):
312 return int(cell) if cell != '' else 0
313 def sumCells(cells):
314 return reduce(lambda x,y: intOfCell(x) + intOfCell(y), cells)
315 def getUsedArea(sheet):
316 cursor = sheet.createCursor()
317 cursor.gotoStartOfUsedArea(False)
318 cursor.gotoEndOfUsedArea(True)
319 return cursor
320 def clearSheet(sheet):
321 cursor = sheet.createCursor()
322 cursor.gotoStartOfUsedArea(False)
323 cursor.gotoEndOfUsedArea(True)
324 rangeAddress = cursor.RangeAddress
325 range = sheet.getCellRangeByPosition \
326 ( rangeAddress.StartColumn
327 , rangeAddress.StartRow
328 , rangeAddress.EndColumn
329 , rangeAddress.EndRow )
330 flags = CellFlags.VALUE | \
331 CellFlags.DATETIME | \
332 CellFlags.STRING | \
333 CellFlags.ANNOTATION | \
334 CellFlags.FORMULA | \
335 CellFlags.HARDATTR | \
336 CellFlags.STYLES | \
337 CellFlags.OBJECTS | \
338 CellFlags.EDITATTR
339 range.clearContents(flags)
340 def MsgBox(msgtype, buttons, title, message):
341 (model, context) = getModel()
342 parentwin = model.CurrentController.Frame.ContainerWindow
343 toolkit = parentwin.getToolkit()
344 msgbox = toolkit.createMessageBox(parentwin, msgtype, buttons, title, message)
345 ret = msgbox.execute()
346 msgbox.dispose()
347 return ret
348
349 def rm(path):
350 if os.path.exists(path):
351 shutil.rmtree(path)
352 def mkdir(path):
353 if not os.path.exists(path):
354 os.makedirs(path)
355 def exportPDF(model, ballotSheet, ballotOutput):
356 # DOC: https://wiki.openoffice.org/wiki/API/Tutorials/PDF_export
357 model.storeToURL \
358 ( "file://"+ballotOutput
359 , ( PropertyValue("FilterName", 0, "calc_pdf_Export", 0)
360 , PropertyValue \
361 ( "FilterData", 0
362 , uno.Any( "[]com.sun.star.beans.PropertyValue" \
363 , ( PropertyValue \
364 ( "Selection", 0
365 , ballotSheet.getCellRangeByName("bulletin"), 0 )
366 , ) )
367 , 0 )
368 , PropertyValue("Overwrite", 0, True, 0)
369 , PropertyValue("SelectPdfVersion", 0, 0, 0) # PDF 1.4
370 )
371 )
372
373 g_exportedScripts = MakeBallots, MakeResults