1 # -*- coding: utf-8 -*-
2 from __future__
import unicode_literals
3 from functools
import reduce
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
15 if 'XSCRIPTCONTEXT' in globals():
17 return (XSCRIPTCONTEXT
.getDocument(), XSCRIPTCONTEXT
)
21 from connect_to_libre_office
import getModel
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")
37 ballots
= intOfCell(ballotsSheet
.getCellByPosition(0, 0).Value
)
39 MsgBox(ERRORBOX
, BUTTONS_OK
, "MakeBallots", \
40 "ERROR: set $"+ballotsSheet
.Name
+"$A$1 to the number of ballots.")
43 # Set bulletins NamedRange
44 ballotsRange
= ballotsSheet
.getCellRangeByPosition(0, 4, 0, 4 + ballots
- 1)
45 model
.NamedRanges
.removeByName("bulletins")
46 model
.NamedRanges
.addNewByName \
48 , ballotsRange
.AbsoluteName
49 , ballotsSheet
.getCellByPosition(0,0).CellAddress
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]
62 [ ballotsHeaderChoices
63 , ballotsHeaderSumByChoice
64 , ballotsHeaderSumByGrade
65 , ballotsHeaderGrades
]
67 ballotDir
= "./bulletins"
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
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
94 for choice
in range(len(choices
)):
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
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)
117 for grade
in range(len(grades
)):
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
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
)
132 def MakeResults(*args
):
133 (model
, context
) = getModel()
135 ballotSheet
= model
.Sheets
[0]
136 ballotsSheet
= model
.Sheets
[1]
137 valueSheet
= model
.Sheets
[2]
138 rankingSheet
= model
.Sheets
[3]
140 gradesRange
= ballotSheet
.getCellRangeByName("mentions")
141 choicesRange
= ballotSheet
.getCellRangeByName("choix")
142 ballotsRange
= ballotsSheet
.getCellRangeByName("bulletins")
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
)
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
))
158 clearSheet(valueSheet
)
159 clearSheet(rankingSheet
)
161 #model.NamedRanges.addNewByName("bulletins", 2, 3, 4)
162 #debug("model:"+str(ballotsSheet.getCellRangeByName("bulletins")))
164 sumByGradeByChoice
= []
165 for choice
in range(lenChoices
):
166 debug("choice:"+str(choices
[choice
]))
167 choiceGrades
= list(map(intOfCell
, sumByGrade
[choice
*lenGrades
:choice
*lenGrades
+ lenGrades
]))
168 sumByGradeByChoice
.append([choices
[choice
]] + choiceGrades
.copy())
169 debug(" choiceGrades:"+str(choiceGrades
))
170 choiceBallots
= int(sumByChoice
[choice
*lenGrades
])
171 debug(" choiceBallots:"+str(choiceBallots
))
173 # Check ballots counting consistency
174 if choiceBallots
!= lenBallots
:
175 MsgBox(ERRORBOX
, BUTTONS_OK
, "MakeResults", \
176 "Choice "+str(choices
[choice
])+" has "+str(int(choiceBallots
))+" ballots" \
177 + "\nbut the total number of ballots is "+str(lenBallots
)+"." \
178 + "\nAborting. Fix the ballots counting and retry.")
184 while nJudgesLower
*2 < lenBallots
:
185 medianGrade
= medianGrade
+ 1
186 nJudgesLower
= nJudgesLower
+ intOfCell(choiceGrades
[medianGrade
])
187 debug(" medianGrade:"+str(medianGrade
))
189 # Extend choiceValue with the overflow of the lower judgments
190 choiceValue
= [choices
[choice
]]
192 lowerMedianGrade
= medianGrade
193 higherMedianGrade
= medianGrade
+ 1
194 nJudgesHigher
= lenBallots
- nJudgesLower
195 while nJudgesLower
> nJudgesHigher
:
196 while intOfCell(choiceGrades
[lowerMedianGrade
]) == 0:
197 lowerMedianGrade
-= 1
198 choiceGrades
[lowerMedianGrade
] -= 1
199 choiceValue
.append(lowerMedianGrade
)
203 # Extend choiceValue by alterning between lower and higher judgments
204 while ballot
< lenBallots
:
206 while lowerMedianGrade
> 0 and intOfCell(choiceGrades
[lowerMedianGrade
]) == 0:
207 lowerMedianGrade
-= 1
208 while higherMedianGrade
< lenGrades
- 1 and intOfCell(choiceGrades
[higherMedianGrade
]) == 0:
209 higherMedianGrade
+= 1
210 choiceValue
.append(lowerMedianGrade
)
211 choiceValue
.append(higherMedianGrade
)
212 choiceGrades
[lowerMedianGrade
] -= 1
213 choiceGrades
[higherMedianGrade
] -= 1
216 valueData
.append(choiceValue
)
219 rankingData
= list(zip(sumByGradeByChoice
, valueData
))
220 rankingData
.sort(key
=lambda data
: data
[1][1:lenBallots
+1])
221 rankingData
.reverse()
222 valueData
= list(map(lambda data
: data
[1], rankingData
))
223 rankingData
= [[""] + list(grades
)] + list(map(lambda data
: \
224 map(lambda c
: "" if c
== 0 else c
, data
[0]), rankingData
))
225 rankingRange
= rankingSheet
.getCellRangeByPosition \
226 ( 0, 0, len(rankingData
[0]) - 1, len(rankingData
) - 1 )
227 rankingRange
.DataArray
= rankingData
229 # rankingHeader: choices
230 rankingChoicesRange
= rankingSheet
.getCellRangeByPosition(0, 1, 0, lenChoices
)
231 for choice
in range(lenChoices
):
232 rankingChoiceCell
= rankingChoicesRange
.getCellByPosition(0, choice
)
233 choiceCell
= choicesRange
.getCellByPosition(0, choice
)
234 rankingChoiceCell
.CellBackColor
= choiceCell
.CellBackColor
235 rankingChoiceCell
.CharWeight
= choiceCell
.CharWeight
236 rankingChoiceCell
.HoriJustify
= 1
237 rankingChoiceCell
.VertJustify
= 1
239 # rankingHeader: grades
240 rankingGradesRange
= rankingSheet
.getCellRangeByPosition(1, 0, lenGrades
, 0)
241 for grade
in range(lenGrades
):
242 rankingGradeCell
= rankingGradesRange
.getCellByPosition(grade
, 0)
243 choiceCell
= gradesRange
.getCellByPosition(grade
, 0)
244 rankingGradeCell
.CellBackColor
= choiceCell
.CellBackColor
245 rankingGradeCell
.CharWeight
= choiceCell
.CharWeight
246 rankingGradeCell
.HoriJustify
= 1
247 rankingGradeCell
.VertJustify
= 1
250 valueDataRange
= valueSheet
.getCellRangeByPosition(2, 1, 2 + lenBallots
- 1, lenChoices
)
251 for choice
in range(lenChoices
):
252 for ballot
in range(lenBallots
):
253 grade
= valueData
[choice
][1+ballot
]
254 valueCell
= valueDataRange
.getCellByPosition(ballot
, choice
)
255 gradeCell
= gradesRange
.getCellByPosition(grade
, 0)
256 valueCell
.CellBackColor
= gradeCell
.CellBackColor
259 for value
in valueData
:
261 debug("base:"+str(base
))
262 digits
= list(map(lambda v
: v
, value
[1:lenBallots
+1]))
263 rank
= inBase(base
, digits
)
264 rankMax
= base
** lenBallots
- 1
265 debug("digits:"+str(digits
))
266 debug("rank:"+str(rank
))
267 debug("rankMax:"+str(rankMax
))
268 value
.insert(1, rank
/ rankMax
)
270 valueData
= valueData
271 valueRange
= valueSheet
.getCellRangeByPosition \
272 ( 0, 1, len(valueData
[0]) - 1, len(valueData
) )
273 valueRange
.DataArray
= valueData
275 # valueHeader: choice
276 valueHeader
= valueSheet
.getCellByPosition(0, 0)
277 valueHeader
.HoriJustify
= 2
278 valueHeader
.CharWeight
= 150
279 valueHeader
.String
= "Choix"
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
292 valueHeader
= valueSheet
.getCellByPosition(1, 0)
293 valueHeader
.HoriJustify
= 2
294 valueHeader
.CharWeight
= 150
295 valueHeader
.String
= "Rang"
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
= "Valeurs"
305 def inBase(base
, digits
):
308 acc
= digit
+ (base
* acc
)
312 return int(cell
) if cell
!= '' else 0
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)
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 | \
333 CellFlags
.ANNOTATION | \
334 CellFlags
.FORMULA | \
335 CellFlags
.HARDATTR | \
337 CellFlags
.OBJECTS | \
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()
350 if os
.path
.exists(path
):
353 if not os
.path
.exists(path
):
355 def exportPDF(model
, ballotSheet
, ballotOutput
):
356 # DOC: https://wiki.openoffice.org/wiki/API/Tutorials/PDF_export
358 ( "file://"+ballotOutput
359 , ( PropertyValue("FilterName", 0, "calc_pdf_Export", 0)
362 , uno
.Any( "[]com.sun.star.beans.PropertyValue" \
365 , ballotSheet
.getCellRangeByName("bulletin"), 0 )
368 , PropertyValue("Overwrite", 0, True, 0)
369 , PropertyValue("SelectPdfVersion", 0, 0, 0) # PDF 1.4
373 g_exportedScripts
= MakeBallots
, MakeResults