1 /*******************************************************************************
2 * Copyright � Igor Barma, Alexandre Desoubeaux, Christian Martel, Eric Brun, Mathieu Amblard, Gwenael Gevet, Pierre Guillot, 2012
3 * Copyright Alexandre Desoubeaux, Christian Martel, Cedric Lecarpentier, Alexandre Lefevre, Marc Salvat 2014-2016
4 * Copyright Alexandre Desoubeaux, Christian Martel, Cedric Lecarpentier, Marc Salvat, Marc Suarez, Harifetra Ramamonjy 2017
6 * This file is part of the work and learning management system Pentila Nero.
8 * Pentila Nero is free software. You can redistribute it and/or modify since
9 * you respect the terms of either (at least one of the both license) :
10 * - under the terms of the GNU Affero General Public License as
11 * published by the Free Software Foundation, either version 3 of the
12 * License, or (at your option) any later version.
13 * - the CeCILL-C as published by CeCILL-C; either version 1 of the
14 * License, or any later version
15 * - the GNU Lesser General Public License as published by the
16 * Free Software Foundation, either version 3 of the license,
17 * or (at your option) any later version.
19 * There are special exceptions to the terms and conditions of the
20 * licenses as they are applied to this software. View the full text of
21 * the exception in file LICENSE-PROJECT.txt in the directory of this software
24 * Pentila Nero is distributed in the hope that it will be useful,
25 * but WITHOUT ANY WARRANTY; without even the implied warranty of
26 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
27 * Licenses for more details.
29 * You should have received a copy of the GNU Affero General Public License
30 * and the CeCILL-C and the GNU Lesser General Public License along with
31 * Pentila Nero. If not, see :
32 * <http://www.gnu.org/licenses/> and
33 * <http://www.cecill.info/licences.fr.html>.
34 ******************************************************************************/
35 package com.pentila.entSavoie.utils;
38 import java.text.ParseException;
39 import java.text.SimpleDateFormat;
40 import java.util.ArrayList;
41 import java.util.Collections;
42 import java.util.Date;
43 import java.util.List;
44 import java.util.Locale;
45 import java.util.ResourceBundle;
47 import javax.naming.NamingException;
48 import javax.portlet.ResourceRequest;
49 import javax.portlet.ActionResponse;
53 import jxl.WorkbookSettings;
54 import jxl.format.Alignment;
55 import jxl.format.Border;
56 import jxl.format.BorderLineStyle;
57 import jxl.format.Colour;
58 import jxl.format.VerticalAlignment;
59 import jxl.write.Label;
60 import jxl.write.WritableCellFormat;
61 import jxl.write.WritableFont;
62 import jxl.write.WritableSheet;
63 import jxl.write.WritableWorkbook;
65 import com.liferay.portal.kernel.exception.PortalException;
66 import com.liferay.portal.kernel.exception.SystemException;
67 import com.liferay.portal.kernel.json.JSONFactoryUtil;
68 import com.liferay.portal.kernel.json.JSONObject;
69 import com.liferay.portal.kernel.log.Log;
70 import com.liferay.portal.kernel.log.LogFactoryUtil;
71 import com.liferay.portal.kernel.util.FileUtil;
72 import com.liferay.portal.kernel.util.HtmlUtil;
73 import com.liferay.portal.kernel.util.ParamUtil;
74 import com.liferay.portal.kernel.util.WebKeys;
75 import com.liferay.portal.model.CompanyConstants;
76 import com.liferay.portal.model.Group;
77 import com.liferay.portal.model.Role;
78 import com.liferay.portal.model.RoleConstants;
79 import com.liferay.portal.model.User;
80 import com.liferay.portal.service.GroupLocalServiceUtil;
81 import com.liferay.portal.service.RoleLocalServiceUtil;
82 import com.liferay.portal.theme.ThemeDisplay;
83 import com.liferay.portal.util.PortalUtil;
84 import com.liferay.portlet.documentlibrary.store.DLStoreUtil;
85 import com.pentila.entSavoie.ENTRoleUtilFactory;
86 import com.pentila.entSavoie.ENTRolesConstants;
87 import com.pentila.entSavoie.cahierDeTexte.model.EventCDT;
88 import com.pentila.entSavoie.cahierDeTexte.service.EventCDTLocalServiceUtil;
89 import com.pentila.entSavoie.cahierDeTexte.util.CDTUtil;
90 import com.pentila.entSavoie.directory.OrganizationFinderServiceUtil;
92 public class ExportXLS {
94 static SimpleDateFormat classicDateFormat = new SimpleDateFormat("yyyy-MM-dd");
95 static SimpleDateFormat textFrenchDateFormat = new SimpleDateFormat("EEEE dd MMM yyyy", Locale.FRENCH);
97 static List<Long> processUserExport = new ArrayList<Long>();
100 public static JSONObject exportXLS(ResourceRequest resourceRequest) throws NamingException, Exception {
101 JSONObject result = JSONFactoryUtil.createJSONObject();
102 result.put("success", false);
104 User currUser = null;
108 currUser = PortalUtil.getUser(resourceRequest);
110 if (processUserExport.contains(currUser.getUserId())) {
111 result.put("msg", "Un export est déjà en cours pour votre compte, vous ne pouvez en réaliser qu´un seul à la fois.<br/> Veuillez renouveler cette opération lorsque celui-ci sera terminé.");
112 result.put("success", true);
116 processUserExport.add(currUser.getUserId());
119 ResourceBundle messages = ResourceBundle.getBundle("content.Language", currUser.getLocale());
120 final String startDay = ParamUtil.getString(resourceRequest, "startDay");
121 final String endDay = ParamUtil.getString(resourceRequest, "endDay");
123 Date startDayDate = classicDateFormat.parse(startDay);
124 Date endDayDate = classicDateFormat.parse(endDay);
126 boolean sortByDate = ParamUtil.getBoolean(resourceRequest, "sortByDate", false);
127 boolean sortByUser = ParamUtil.getBoolean(resourceRequest, "sortByUser", false);
128 boolean sortByCalendar = ParamUtil.getBoolean(resourceRequest, "sortByCalendar", false);
129 // boolean sortBySubject = ParamUtil.getBoolean(resourceRequest, "sortBySubject", false);
131 ThemeDisplay themeDisplay = (ThemeDisplay)resourceRequest.getAttribute(WebKeys.THEME_DISPLAY);
133 User user = PortalUtil.getUser(resourceRequest);
134 Role principalRole = ENTRoleUtilFactory.getInstance(user.getCompanyId()).getRole(ENTRolesConstants.NATIONAL_4);
135 boolean isPrincipal = RoleLocalServiceUtil.hasUserRole(user.getUserId(), principalRole.getRoleId());
137 Role adminRole = ENTRoleUtilFactory.getInstance(user.getCompanyId()).getRole(RoleConstants.ADMINISTRATOR);
138 boolean isAdmin = RoleLocalServiceUtil.hasUserRole(user.getUserId(), adminRole.getRoleId());
140 long filteredEtabOrgId = 0;
141 if (isPrincipal || isAdmin) {
142 filteredEtabOrgId = OrganizationFinderServiceUtil.getEtabRatachement(user).getOrganizationId();
145 byte[] export = sortByDate?exportCDTByDate(resourceRequest, filteredEtabOrgId):(sortByUser?exportCDTByUser(resourceRequest, filteredEtabOrgId):(sortByCalendar?exportCDTByCalendar(resourceRequest, filteredEtabOrgId):exportCDTBySubject(resourceRequest, filteredEtabOrgId)));
148 String folderName = ExportUtil.sendToCasier(resourceRequest, currUser, export, startDay, endDay, true);
149 String hrefXLS = "<a href=\"" + themeDisplay.getURLPortal().toString() + "/" + themeDisplay.getPathMain() + "/document_library/get_file?p_l_id=" + themeDisplay.getPlid() + folderName + "\" target=\"_blank\"> <br /> <br /> <u> " + messages.getString("cahier-de-texte") + " : " + messages.getString("export-du") + " " + textFrenchDateFormat.format(startDayDate) + " au " + textFrenchDateFormat.format(endDayDate) +".xls </u> </a>";
151 processUserExport.remove(currUser.getUserId());
152 result.put("msg", messages.getString("un-export-xls-a-ete-depose-dans-votre-casier") + " : " + hrefXLS);
153 result.put("success", true);
155 result.put("success", false);
158 } catch (Exception e) {
159 if (_log.isErrorEnabled()) {
162 result.put("success", false);
165 processUserExport.remove(currUser.getUserId());
171 public static byte[] exportCDTByDate(ResourceRequest resourceRequest, long filteredEtabOrgId) throws ParseException, PortalException, SystemException{
172 ThemeDisplay themeDisplay = (ThemeDisplay)resourceRequest.getAttribute(WebKeys.THEME_DISPLAY);
173 User currUser = PortalUtil.getUser(resourceRequest);
174 ResourceBundle messages = ResourceBundle.getBundle("content.Language", currUser.getLocale());
176 final String startDay = ParamUtil.getString(resourceRequest, "startDay");
177 final String endDay = ParamUtil.getString(resourceRequest, "endDay");
178 Date startDayDate = classicDateFormat.parse(startDay);
179 Date endDayDate = classicDateFormat.parse(endDay);
180 return exportCDTByDate(currUser, themeDisplay, messages, startDayDate, endDayDate, filteredEtabOrgId);
183 public static byte[] exportCDTByDate(User user, ThemeDisplay themeDisplay, ResourceBundle messages, Date startDayDate, Date endDayDate, long filteredEtabOrgId){
186 User currUser = user;
189 File file = FileUtil.createTempFile("xls");
191 WorkbookSettings wbSettings = new WorkbookSettings();
192 wbSettings.setLocale(new Locale("en", "EN"));
194 WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);
195 workbook.createSheet("Export CDT", 0);
197 WritableSheet excelSheet = workbook.getSheet(0);
199 WritableFont times14ptHeaderPrincipal = new WritableFont(WritableFont.TIMES, 14, WritableFont.BOLD);
200 WritableCellFormat times14HeaderPrincipal = new WritableCellFormat(times14ptHeaderPrincipal);
201 times14HeaderPrincipal.setBorder(Border.ALL, BorderLineStyle.NONE);
202 times14HeaderPrincipal.setAlignment(Alignment.LEFT);
203 times14HeaderPrincipal.setVerticalAlignment(VerticalAlignment.CENTRE);
205 excelSheet.addCell(new Label(0, 0, messages.getString("export-du")+ " " + (new SimpleDateFormat("EEEE dd MMM yyyy", currUser.getLocale())).format(startDayDate) + " " + messages.getString("au") + " " + (new SimpleDateFormat("EEEE dd MMM yyyy", currUser.getLocale())).format(endDayDate), times14HeaderPrincipal));
206 excelSheet.mergeCells(0, 0, 6, 0);
208 WritableFont times13pt = new WritableFont(WritableFont.TIMES, 13, WritableFont.BOLD);
209 WritableCellFormat times13 = new WritableCellFormat(times13pt);
210 times13.setBorder(Border.ALL, BorderLineStyle.HAIR);
211 times13.setAlignment(Alignment.CENTRE);
212 times13.setVerticalAlignment(VerticalAlignment.CENTRE);
213 times13.setBackground(Colour.PALE_BLUE);
214 times13.setWrap(true);
216 excelSheet.addCell(new Label(0, 4, "Date", times13));
217 excelSheet.addCell(new Label(1, 4, "Heure", times13));
218 excelSheet.addCell(new Label(2, 4, "Classe", times13));
219 excelSheet.addCell(new Label(3, 4, "Mati\u00e8re", times13));
220 excelSheet.addCell(new Label(4, 4, "Enseignant", times13));
221 excelSheet.addCell(new Label(5, 4, "Sujet", times13));
222 excelSheet.addCell(new Label(6, 4, "Description", times13));
224 WritableFont times12ptBold = new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD);
225 WritableCellFormat times12Bold = new WritableCellFormat(times12ptBold);
226 times12Bold.setBorder(Border.ALL, BorderLineStyle.HAIR);
227 times12Bold.setVerticalAlignment(VerticalAlignment.CENTRE);
228 times12Bold.setAlignment(Alignment.CENTRE);
229 times12Bold.setWrap(true);
231 WritableFont times12pt = new WritableFont(WritableFont.TIMES, 12);
232 WritableCellFormat times12 = new WritableCellFormat(times12pt);
233 times12.setBorder(Border.ALL, BorderLineStyle.HAIR);
234 times12.setVerticalAlignment(VerticalAlignment.CENTRE);
235 times12.setAlignment(Alignment.CENTRE);
236 times12.setWrap(true);
238 WritableCellFormat times12Left = new WritableCellFormat(times12pt);
239 times12Left.setBorder(Border.ALL, BorderLineStyle.HAIR);
240 times12Left.setVerticalAlignment(VerticalAlignment.TOP);
241 times12Left.setAlignment(Alignment.LEFT);
242 times12Left.setWrap(true);
244 //On passe les colonnes en autosize
245 CellView cv = new CellView();
246 cv.setAutosize(true);
247 for(int i=0;i<=5;i++){
248 excelSheet.setColumnView(i, cv);
251 CellView cv2 = new CellView();
253 excelSheet.setColumnView(6, cv2);
255 List<EventCDT> listEvt = CDTUtil.calendarEvent_list(currUser.getUserId(), 0, -1l, "[-1]", false, startDayDate, endDayDate, true, false, false, false, filteredEtabOrgId);
259 Boolean firstEvent = true;
260 String currentDate = "";
261 for (EventCDT evt : listEvt) {
262 String eventDate = textFrenchDateFormat.format(evt.getStartDay());
263 if (currentDate.equals(eventDate)) {
264 // on est sur le meme jour que le precedent
269 excelSheet.mergeCells(0, beginRow, 0, row - 1);
273 currentDate = eventDate;
276 excelSheet.addCell(new Label(0, row, eventDate, times12Bold));
277 excelSheet.addCell(new Label(1, row, evt.getStartEvtTime().replace(':', 'h')+" - "+evt.getEndEvtTime().replace(':', 'h'), times12));
278 Group group = GroupLocalServiceUtil.getGroup(evt.getGroupId());
279 excelSheet.addCell(new Label(2, row, ExportUtil.getClasseNameCached(group), times12));
280 excelSheet.addCell(new Label(3, row, ExportUtil.getMatiereNameCached(evt.getSubjectCDT()), times12));
281 excelSheet.addCell(new Label(4, row, ExportUtil.getUserNameCached(evt.getCreator()), times12));
282 excelSheet.addCell(new Label(5, row, evt.getSubject(), times12));
283 excelSheet.addCell(new Label(6, row, getDescription(evt, themeDisplay, currUser), times12Left));
288 excelSheet.mergeCells(0, beginRow, 0, row - 1);
293 return FileUtil.getBytes(file);
294 } catch (Exception e){
295 if (_log.isErrorEnabled()) {
303 public static byte[] exportCDTByUser(ResourceRequest resourceRequest, long filteredEtabOrgId) throws ParseException, PortalException, SystemException{
304 ThemeDisplay themeDisplay = (ThemeDisplay)resourceRequest.getAttribute(WebKeys.THEME_DISPLAY);
305 User currUser = PortalUtil.getUser(resourceRequest);
306 ResourceBundle messages = ResourceBundle.getBundle("content.Language", currUser.getLocale());
308 final String startDay = ParamUtil.getString(resourceRequest, "startDay");
309 final String endDay = ParamUtil.getString(resourceRequest, "endDay");
310 Date startDayDate = classicDateFormat.parse(startDay);
311 Date endDayDate = classicDateFormat.parse(endDay);
312 return exportCDTByUser(currUser, themeDisplay, messages, startDayDate, endDayDate, filteredEtabOrgId);
316 public static byte[] exportCDTByUser(User user, ThemeDisplay themeDisplay, ResourceBundle messages, Date startDayDate, Date endDayDate, long filteredEtabOrgId){
320 User currUser = user;
323 File file = FileUtil.createTempFile("xls");
325 WorkbookSettings wbSettings = new WorkbookSettings();
326 wbSettings.setLocale(new Locale("en", "EN"));
328 WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);
329 workbook.createSheet("Export CDT", 0);
331 WritableSheet excelSheet = workbook.getSheet(0);
333 WritableFont times14ptHeaderPrincipal = new WritableFont(WritableFont.TIMES, 14, WritableFont.BOLD);
334 WritableCellFormat times14HeaderPrincipal = new WritableCellFormat(times14ptHeaderPrincipal);
335 times14HeaderPrincipal.setBorder(Border.ALL, BorderLineStyle.NONE);
336 times14HeaderPrincipal.setAlignment(Alignment.LEFT);
337 times14HeaderPrincipal.setVerticalAlignment(VerticalAlignment.CENTRE);
339 excelSheet.addCell(new Label(0, 0, messages.getString("export-du")+ " " + (new SimpleDateFormat("EEEE dd MMM yyyy", currUser.getLocale())).format(startDayDate) + " " + messages.getString("au") + " " + (new SimpleDateFormat("EEEE dd MMM yyyy", currUser.getLocale())).format(endDayDate), times14HeaderPrincipal));
340 excelSheet.mergeCells(0, 0, 6, 0);
342 WritableFont times13pt = new WritableFont(WritableFont.TIMES, 13, WritableFont.BOLD);
343 WritableCellFormat times13 = new WritableCellFormat(times13pt);
344 times13.setBorder(Border.ALL, BorderLineStyle.HAIR);
345 times13.setAlignment(Alignment.CENTRE);
346 times13.setVerticalAlignment(VerticalAlignment.CENTRE);
347 times13.setBackground(Colour.PALE_BLUE);
348 times13.setWrap(true);
350 excelSheet.addCell(new Label(0, 4, "Enseignant", times13));
351 excelSheet.addCell(new Label(1, 4, "Date", times13));
352 excelSheet.addCell(new Label(2, 4, "Heure", times13));
353 excelSheet.addCell(new Label(3, 4, "Classe", times13));
354 excelSheet.addCell(new Label(4, 4, "Mati\u00e8re", times13));
355 excelSheet.addCell(new Label(5, 4, "Sujet", times13));
356 excelSheet.addCell(new Label(6, 4, "Description", times13));
358 WritableFont times12ptBold = new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD);
359 WritableCellFormat times12Bold = new WritableCellFormat(times12ptBold);
360 times12Bold.setBorder(Border.ALL, BorderLineStyle.HAIR);
361 times12Bold.setVerticalAlignment(VerticalAlignment.CENTRE);
362 times12Bold.setAlignment(Alignment.CENTRE);
363 times12Bold.setWrap(true);
365 WritableFont times12pt = new WritableFont(WritableFont.TIMES, 12);
366 WritableCellFormat times12 = new WritableCellFormat(times12pt);
367 times12.setBorder(Border.ALL, BorderLineStyle.HAIR);
368 times12.setVerticalAlignment(VerticalAlignment.CENTRE);
369 times12.setAlignment(Alignment.CENTRE);
370 times12.setWrap(true);
372 WritableCellFormat times12Left = new WritableCellFormat(times12pt);
373 times12Left.setBorder(Border.ALL, BorderLineStyle.HAIR);
374 times12Left.setVerticalAlignment(VerticalAlignment.TOP);
375 times12Left.setAlignment(Alignment.LEFT);
376 times12Left.setWrap(true);
378 //On passe les colonnes en autosize
379 CellView cv = new CellView();
380 cv.setAutosize(true);
381 for(int i=0;i<=5;i++){
382 excelSheet.setColumnView(i, cv);
385 CellView cv2 = new CellView();
387 excelSheet.setColumnView(6, cv2);
389 List<EventCDT> listEvt = CDTUtil.calendarEvent_list(currUser.getUserId(), 0, -1l, "[-1]", false, startDayDate, endDayDate, false, true, false, false, filteredEtabOrgId);
393 Boolean firstEvent = true;
394 String currentUser = "";
395 for (EventCDT evt : listEvt) {
396 String eventUser = ExportUtil.getUserNameCached(evt.getCreator());
397 if (currentUser.equals(eventUser)) {
398 // on est sur le meme jour que le precedent
403 excelSheet.mergeCells(0, beginRow, 0, row - 1);
407 currentUser = eventUser;
410 excelSheet.addCell(new Label(0, row, eventUser, times12Bold));
411 excelSheet.addCell(new Label(1, row, textFrenchDateFormat.format(evt.getStartDay()), times12));
412 excelSheet.addCell(new Label(2, row, evt.getStartEvtTime().replace(':', 'h')+" - "+evt.getEndEvtTime().replace(':', 'h'), times12));
413 Group group = GroupLocalServiceUtil.getGroup(evt.getGroupId());
414 excelSheet.addCell(new Label(3, row, ExportUtil.getClasseNameCached(group), times12));
415 excelSheet.addCell(new Label(4, row, ExportUtil.getMatiereNameCached(evt.getSubjectCDT()), times12));
416 excelSheet.addCell(new Label(5, row, evt.getSubject(), times12));
417 excelSheet.addCell(new Label(6, row, getDescription(evt, themeDisplay, currUser), times12Left));
422 excelSheet.mergeCells(0, beginRow, 0, row - 1);
427 return FileUtil.getBytes(file);
428 } catch (Exception e){
429 if (_log.isErrorEnabled()) {
437 public static byte[] exportCDTByCalendar(ResourceRequest resourceRequest, long filteredEtabOrgId) throws ParseException, PortalException, SystemException{
438 ThemeDisplay themeDisplay = (ThemeDisplay)resourceRequest.getAttribute(WebKeys.THEME_DISPLAY);
439 User currUser = PortalUtil.getUser(resourceRequest);
440 ResourceBundle messages = ResourceBundle.getBundle("content.Language", currUser.getLocale());
442 final String startDay = ParamUtil.getString(resourceRequest, "startDay");
443 final String endDay = ParamUtil.getString(resourceRequest, "endDay");
444 Date startDayDate = classicDateFormat.parse(startDay);
445 Date endDayDate = classicDateFormat.parse(endDay);
446 return exportCDTByCalendar(currUser, themeDisplay, messages, startDayDate, endDayDate, filteredEtabOrgId);
450 public static byte[] exportCDTByCalendar(User user, ThemeDisplay themeDisplay, ResourceBundle messages, Date startDayDate, Date endDayDate, long filteredEtabOrgId){
452 User currUser = user;
454 File file = FileUtil.createTempFile("xls");
456 WorkbookSettings wbSettings = new WorkbookSettings();
457 wbSettings.setLocale(new Locale("en", "EN"));
459 WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);
460 workbook.createSheet("Export CDT", 0);
462 WritableSheet excelSheet = workbook.getSheet(0);
464 WritableFont times14ptHeaderPrincipal = new WritableFont(WritableFont.TIMES, 14, WritableFont.BOLD);
465 WritableCellFormat times14HeaderPrincipal = new WritableCellFormat(times14ptHeaderPrincipal);
466 times14HeaderPrincipal.setBorder(Border.ALL, BorderLineStyle.NONE);
467 times14HeaderPrincipal.setAlignment(Alignment.LEFT);
468 times14HeaderPrincipal.setVerticalAlignment(VerticalAlignment.CENTRE);
470 excelSheet.addCell(new Label(0, 0, messages.getString("export-du")+ " " + (new SimpleDateFormat("EEEE dd MMM yyyy", currUser.getLocale())).format(startDayDate) + " " + messages.getString("au") + " " + (new SimpleDateFormat("EEEE dd MMM yyyy", currUser.getLocale())).format(endDayDate), times14HeaderPrincipal));
471 excelSheet.mergeCells(0, 0, 6, 0);
473 WritableFont times13pt = new WritableFont(WritableFont.TIMES, 13, WritableFont.BOLD);
474 WritableCellFormat times13 = new WritableCellFormat(times13pt);
475 times13.setBorder(Border.ALL, BorderLineStyle.HAIR);
476 times13.setAlignment(Alignment.CENTRE);
477 times13.setVerticalAlignment(VerticalAlignment.CENTRE);
478 times13.setBackground(Colour.PALE_BLUE);
479 times13.setWrap(true);
481 excelSheet.addCell(new Label(0, 4, "Classe", times13));
482 excelSheet.addCell(new Label(1, 4, "Date", times13));
483 excelSheet.addCell(new Label(2, 4, "Heure", times13));
484 excelSheet.addCell(new Label(3, 4, "Mati\u00e8re", times13));
485 excelSheet.addCell(new Label(4, 4, "Enseignant", times13));
486 excelSheet.addCell(new Label(5, 4, "Sujet", times13));
487 excelSheet.addCell(new Label(6, 4, "Description", times13));
489 WritableFont times12ptBold = new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD);
490 WritableCellFormat times12Bold = new WritableCellFormat(times12ptBold);
491 times12Bold.setBorder(Border.ALL, BorderLineStyle.HAIR);
492 times12Bold.setVerticalAlignment(VerticalAlignment.CENTRE);
493 times12Bold.setAlignment(Alignment.CENTRE);
494 times12Bold.setWrap(true);
496 WritableFont times12pt = new WritableFont(WritableFont.TIMES, 12);
497 WritableCellFormat times12 = new WritableCellFormat(times12pt);
498 times12.setBorder(Border.ALL, BorderLineStyle.HAIR);
499 times12.setVerticalAlignment(VerticalAlignment.CENTRE);
500 times12.setAlignment(Alignment.CENTRE);
501 times12.setWrap(true);
503 WritableCellFormat times12Left = new WritableCellFormat(times12pt);
504 times12Left.setBorder(Border.ALL, BorderLineStyle.HAIR);
505 times12Left.setVerticalAlignment(VerticalAlignment.TOP);
506 times12Left.setAlignment(Alignment.LEFT);
507 times12Left.setWrap(true);
509 //On passe les colonnes en autosize
510 CellView cv = new CellView();
511 cv.setAutosize(true);
512 for(int i=0;i<=5;i++){
513 excelSheet.setColumnView(i, cv);
516 CellView cv2 = new CellView();
518 excelSheet.setColumnView(6, cv2);
520 List<EventCDT> listEvt = CDTUtil.calendarEvent_list(currUser.getUserId(), 0, -1l, "[-1]", false, startDayDate, endDayDate, false, false, true, false, filteredEtabOrgId);
524 Boolean firstEvent = true;
525 String currentCalendar = "";
526 for (EventCDT evt : listEvt) {
527 Group group = GroupLocalServiceUtil.getGroup(evt.getGroupId());
528 String eventCalendar = ExportUtil.getClasseNameCached(group);
529 if (currentCalendar.equals(eventCalendar)) {
530 // on est sur le meme jour que le precedent
535 excelSheet.mergeCells(0, beginRow, 0, row - 1);
539 currentCalendar = eventCalendar;
542 excelSheet.addCell(new Label(0, row, eventCalendar, times12Bold));
543 excelSheet.addCell(new Label(1, row, textFrenchDateFormat.format(evt.getStartDay()), times12));
544 excelSheet.addCell(new Label(2, row, evt.getStartEvtTime().replace(':', 'h')+" - "+evt.getEndEvtTime().replace(':', 'h'), times12));
545 excelSheet.addCell(new Label(3, row, ExportUtil.getMatiereNameCached(evt.getSubjectCDT()), times12));
546 excelSheet.addCell(new Label(4, row, ExportUtil.getUserNameCached(evt.getCreator()), times12));
547 excelSheet.addCell(new Label(5, row, evt.getSubject(), times12));
548 excelSheet.addCell(new Label(6, row, getDescription(evt, themeDisplay, currUser), times12Left));
553 excelSheet.mergeCells(0, beginRow, 0, row - 1);
558 return FileUtil.getBytes(file);
559 } catch (Exception e){
560 if (_log.isErrorEnabled()) {
568 public static byte[] exportCDTBySubject(ResourceRequest resourceRequest, long filteredEtabOrgId) throws ParseException, PortalException, SystemException{
569 ThemeDisplay themeDisplay = (ThemeDisplay)resourceRequest.getAttribute(WebKeys.THEME_DISPLAY);
570 User currUser = PortalUtil.getUser(resourceRequest);
571 ResourceBundle messages = ResourceBundle.getBundle("content.Language", currUser.getLocale());
573 final String startDay = ParamUtil.getString(resourceRequest, "startDay");
574 final String endDay = ParamUtil.getString(resourceRequest, "endDay");
575 Date startDayDate = classicDateFormat.parse(startDay);
576 Date endDayDate = classicDateFormat.parse(endDay);
577 return exportCDTBySubject(currUser, themeDisplay, messages, startDayDate, endDayDate, filteredEtabOrgId);
581 public static byte[] exportCDTBySubject(User user, ThemeDisplay themeDisplay, ResourceBundle messages, Date startDayDate, Date endDayDate, long filteredEtabOrgId){
583 User currUser = user;
585 File file = FileUtil.createTempFile("xls");
587 WorkbookSettings wbSettings = new WorkbookSettings();
588 wbSettings.setLocale(new Locale("en", "EN"));
590 WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);
591 workbook.createSheet("Export CDT", 0);
593 WritableSheet excelSheet = workbook.getSheet(0);
595 WritableFont times14ptHeaderPrincipal = new WritableFont(WritableFont.TIMES, 14, WritableFont.BOLD);
596 WritableCellFormat times14HeaderPrincipal = new WritableCellFormat(times14ptHeaderPrincipal);
597 times14HeaderPrincipal.setBorder(Border.ALL, BorderLineStyle.NONE);
598 times14HeaderPrincipal.setAlignment(Alignment.LEFT);
599 times14HeaderPrincipal.setVerticalAlignment(VerticalAlignment.CENTRE);
601 excelSheet.addCell(new Label(0, 0, messages.getString("export-du")+ " " + (new SimpleDateFormat("EEEE dd MMM yyyy", currUser.getLocale())).format(startDayDate) + " " + messages.getString("au") + " " + (new SimpleDateFormat("EEEE dd MMM yyyy", currUser.getLocale())).format(endDayDate), times14HeaderPrincipal));
602 excelSheet.mergeCells(0, 0, 6, 0);
604 WritableFont times13pt = new WritableFont(WritableFont.TIMES, 13, WritableFont.BOLD);
605 WritableCellFormat times13 = new WritableCellFormat(times13pt);
606 times13.setBorder(Border.ALL, BorderLineStyle.HAIR);
607 times13.setAlignment(Alignment.CENTRE);
608 times13.setVerticalAlignment(VerticalAlignment.CENTRE);
609 times13.setBackground(Colour.PALE_BLUE);
610 times13.setWrap(true);
612 excelSheet.addCell(new Label(0, 4, "Mati\u00e8re", times13));
613 excelSheet.addCell(new Label(1, 4, "Date", times13));
614 excelSheet.addCell(new Label(2, 4, "Heure", times13));
615 excelSheet.addCell(new Label(3, 4, "Classe", times13));
616 excelSheet.addCell(new Label(4, 4, "Enseignant", times13));
617 excelSheet.addCell(new Label(5, 4, "Sujet", times13));
618 excelSheet.addCell(new Label(6, 4, "Description", times13));
620 WritableFont times12ptBold = new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD);
621 WritableCellFormat times12Bold = new WritableCellFormat(times12ptBold);
622 times12Bold.setBorder(Border.ALL, BorderLineStyle.HAIR);
623 times12Bold.setVerticalAlignment(VerticalAlignment.CENTRE);
624 times12Bold.setAlignment(Alignment.CENTRE);
625 times12Bold.setWrap(true);
627 WritableFont times12pt = new WritableFont(WritableFont.TIMES, 12);
628 WritableCellFormat times12 = new WritableCellFormat(times12pt);
629 times12.setBorder(Border.ALL, BorderLineStyle.HAIR);
630 times12.setVerticalAlignment(VerticalAlignment.CENTRE);
631 times12.setAlignment(Alignment.CENTRE);
632 times12.setWrap(true);
634 WritableCellFormat times12Left = new WritableCellFormat(times12pt);
635 times12Left.setBorder(Border.ALL, BorderLineStyle.HAIR);
636 times12Left.setVerticalAlignment(VerticalAlignment.TOP);
637 times12Left.setAlignment(Alignment.LEFT);
638 times12Left.setWrap(true);
640 //On passe les colonnes en autosize
641 CellView cv = new CellView();
642 cv.setAutosize(true);
643 for(int i=0;i<=5;i++){
644 excelSheet.setColumnView(i, cv);
647 CellView cv2 = new CellView();
649 excelSheet.setColumnView(6, cv2);
651 List<EventCDT> listEvt = CDTUtil.calendarEvent_list(currUser.getUserId(), 0, -1l, "[-1]", false, startDayDate, endDayDate, false, false, false, true, filteredEtabOrgId);
655 Boolean firstEvent = true;
656 String currentSubject = "";
657 for (EventCDT evt : listEvt) {
658 String eventSubject = ExportUtil.getMatiereNameCached(evt.getSubjectCDT());
659 if (currentSubject.equals(eventSubject)) {
660 // on est sur le meme jour que le precedent
665 excelSheet.mergeCells(0, beginRow, 0, row - 1);
669 currentSubject = eventSubject;
672 excelSheet.addCell(new Label(0, row, eventSubject, times12Bold));
673 excelSheet.addCell(new Label(1, row, textFrenchDateFormat.format(evt.getStartDay()), times12));
674 excelSheet.addCell(new Label(2, row, evt.getStartEvtTime().replace(':', 'h')+" - "+evt.getEndEvtTime().replace(':', 'h'), times12));
675 Group group = GroupLocalServiceUtil.getGroup(evt.getGroupId());
676 excelSheet.addCell(new Label(3, row, ExportUtil.getClasseNameCached(group), times12));
677 excelSheet.addCell(new Label(4, row, ExportUtil.getUserNameCached(evt.getCreator()), times12));
678 excelSheet.addCell(new Label(5, row, evt.getSubject(), times12));
679 excelSheet.addCell(new Label(6, row, getDescription(evt, themeDisplay, currUser), times12Left));
684 excelSheet.mergeCells(0, beginRow, 0, row - 1);
689 return FileUtil.getBytes(file);
690 } catch (Exception e){
691 if (_log.isErrorEnabled()) {
699 private static String getDescription(EventCDT evt, ThemeDisplay themeDisplay, User user) throws PortalException, SystemException{
700 String description = "";
703 if (evt.getBroadcast() || (user.getUserId() == evt.getCreator())) {
704 if (!evt.getDescription().isEmpty()) {
705 description += "Description : \n";
706 description += HtmlUtil.extractText(evt.getDescription());
711 if (evt.getBroadcast() || (user.getUserId() == evt.getCreator())) {
712 String[] eventCDTAttachments = EventCDTLocalServiceUtil.getAttachFilesNameToEventCDT(user.getCompanyId(), evt.getEvtId());
714 for (int i = 0; i < eventCDTAttachments.length; i++) {
715 String fileName = FileUtil.getShortFileName(eventCDTAttachments[i]);
716 long fileSize = DLStoreUtil.getFileSize(user.getCompanyId(), CompanyConstants.SYSTEM, eventCDTAttachments[i])/1000;
720 href += fileName + " (" + fileSize + " kb)" + ", ";
723 if (eventCDTAttachments.length>0) {
724 description += "\n\nPi\u00e8ces jointes : " + href.substring(0, href.length()-2);
729 List<EventCDT> evtCDTDevoir = new ArrayList<EventCDT>();
730 evtCDTDevoir.addAll(EventCDTLocalServiceUtil.getToEventCDT(evt.getEvtId()));
731 List<EventCDT> filteredEventToDo = new ArrayList<EventCDT>();
733 Collections.sort(evtCDTDevoir);
735 if (user.getUserId() != evt.getCreator()) {
737 for (EventCDT devoirARendre : evtCDTDevoir) {
738 if (devoirARendre.getBroadcast()) {
739 filteredEventToDo.add(devoirARendre);
744 filteredEventToDo.addAll(evtCDTDevoir);
747 if (!filteredEventToDo.isEmpty()) {
748 description += "\n\nDevoirs \u00e0 rendre : \n";
750 for (EventCDT devoir : evtCDTDevoir) {
752 EventCDT evtFrom = EventCDTLocalServiceUtil.getEventCDT(devoir.getFromEvt());
753 description += "\u0020\u0020\u0020\u0020- Devoir donn\u00e9 le " + (new SimpleDateFormat("EEEE dd MMM yyyy", user.getLocale())).format(evtFrom.getStartDay()) + "\n";
755 catch (Exception exc1){}
757 if (!devoir.getSubject().isEmpty()) {
758 description += "\u0020\u0020\u0020\u0020\u0020\u0020" + devoir.getSubject() + " : \n";
760 if (!devoir.getDescription().isEmpty()) {
761 description += "\u0020\u0020\u0020\u0020\u0020\u0020" + HtmlUtil.extractText(devoir.getDescription()) + "\n";
766 String[] eventCDTDevoirAttachments = EventCDTLocalServiceUtil.getAttachFilesNameToEventCDT(user.getCompanyId(), devoir.getEvtId());
767 String hrefDevoir = "";
768 for (int i = 0; i < eventCDTDevoirAttachments.length; i++) {
769 String fileName = FileUtil.getShortFileName(eventCDTDevoirAttachments[i]);
770 long fileSize = DLStoreUtil.getFileSize(user.getCompanyId(), CompanyConstants.SYSTEM, eventCDTDevoirAttachments[i])/1000;
774 hrefDevoir += fileName + " (" + fileSize + " kb), ";
776 if (eventCDTDevoirAttachments.length>0) {
777 description += "\u0020\u0020\u0020\u0020\u0020\u0020Pi\u00e8ces jointes : " + hrefDevoir.substring(0, hrefDevoir.length()-2);
784 if (evt.getBroadcast() || (user.getUserId() == evt.getCreator())) {
785 evtCDTDevoir = EventCDTLocalServiceUtil.getFromEventCDT(evt.getEvtId());
786 if (!evtCDTDevoir.isEmpty()) {
787 description += "\n\nDevoirs donn\u00e9s : \n";
789 for (EventCDT devoir : evtCDTDevoir) {
790 description += "\u0020\u0020\u0020\u0020- Devoirs \u00e0 rendre pour le " + (new SimpleDateFormat("EEEE dd MMM yyyy", user.getLocale())).format(devoir.getStartDay()) + "\n";
791 if (!devoir.getSubject().isEmpty()) {
792 description += "\u0020\u0020\u0020\u0020\u0020\u0020" + devoir.getSubject() + " : \n";
794 if (!devoir.getDescription().isEmpty()) {
795 description += "\u0020\u0020\u0020\u0020\u0020\u0020" + HtmlUtil.extractText(devoir.getDescription()) + "\n";
801 String[] eventCDTDevoirAttachments = EventCDTLocalServiceUtil.getAttachFilesNameToEventCDT(user.getCompanyId(), devoir.getEvtId());
802 String hrefDevoir = "";
803 for (int i = 0; i < eventCDTDevoirAttachments.length; i++) {
804 String fileName = FileUtil.getShortFileName(eventCDTDevoirAttachments[i]);
805 long fileSize = DLStoreUtil.getFileSize(user.getCompanyId(), CompanyConstants.SYSTEM, eventCDTDevoirAttachments[i])/1000;
809 hrefDevoir += fileName + " (" + fileSize + " kb), ";
811 if (eventCDTDevoirAttachments.length>0) {
812 description += "\u0020\u0020\u0020\u0020\u0020\u0020Pi\u00e8ces jointes : " + hrefDevoir.substring(0, hrefDevoir.length()-2);
822 private static Log _log = LogFactoryUtil.getLog(ExportXLS.class);