--- /dev/null
+/*******************************************************************************
+ * Copyright � Igor Barma, Alexandre Desoubeaux, Christian Martel, Eric Brun, Mathieu Amblard, Gwenael Gevet, Pierre Guillot, 2012
+ * Copyright Alexandre Desoubeaux, Christian Martel, Cedric Lecarpentier, Alexandre Lefevre, Marc Salvat 2014-2016
+ * Copyright Alexandre Desoubeaux, Christian Martel, Cedric Lecarpentier, Marc Salvat, Marc Suarez, Harifetra Ramamonjy 2017
+ *
+ * This file is part of the work and learning management system Pentila Nero.
+ *
+ * Pentila Nero is free software. You can redistribute it and/or modify since
+ * you respect the terms of either (at least one of the both license) :
+ * - under the terms of the GNU Affero General Public License as
+ * published by the Free Software Foundation, either version 3 of the
+ * License, or (at your option) any later version.
+ * - the CeCILL-C as published by CeCILL-C; either version 1 of the
+ * License, or any later version
+ * - the GNU Lesser General Public License as published by the
+ * Free Software Foundation, either version 3 of the license,
+ * or (at your option) any later version.
+ *
+ * There are special exceptions to the terms and conditions of the
+ * licenses as they are applied to this software. View the full text of
+ * the exception in file LICENSE-PROJECT.txt in the directory of this software
+ * distribution.
+ *
+ * Pentila Nero is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+ * Licenses for more details.
+ *
+ * You should have received a copy of the GNU Affero General Public License
+ * and the CeCILL-C and the GNU Lesser General Public License along with
+ * Pentila Nero. If not, see :
+ * <http://www.gnu.org/licenses/> and
+ * <http://www.cecill.info/licences.fr.html>.
+ ******************************************************************************/
+package com.pentila.entSavoie.utils;
+
+import java.io.File;
+import java.text.ParseException;
+import java.text.SimpleDateFormat;
+import java.util.ArrayList;
+import java.util.Collections;
+import java.util.Date;
+import java.util.List;
+import java.util.Locale;
+import java.util.ResourceBundle;
+
+import javax.naming.NamingException;
+import javax.portlet.ResourceRequest;
+import javax.portlet.ActionResponse;
+
+import jxl.CellView;
+import jxl.Workbook;
+import jxl.WorkbookSettings;
+import jxl.format.Alignment;
+import jxl.format.Border;
+import jxl.format.BorderLineStyle;
+import jxl.format.Colour;
+import jxl.format.VerticalAlignment;
+import jxl.write.Label;
+import jxl.write.WritableCellFormat;
+import jxl.write.WritableFont;
+import jxl.write.WritableSheet;
+import jxl.write.WritableWorkbook;
+
+import com.liferay.portal.kernel.exception.PortalException;
+import com.liferay.portal.kernel.exception.SystemException;
+import com.liferay.portal.kernel.json.JSONFactoryUtil;
+import com.liferay.portal.kernel.json.JSONObject;
+import com.liferay.portal.kernel.log.Log;
+import com.liferay.portal.kernel.log.LogFactoryUtil;
+import com.liferay.portal.kernel.util.FileUtil;
+import com.liferay.portal.kernel.util.HtmlUtil;
+import com.liferay.portal.kernel.util.ParamUtil;
+import com.liferay.portal.kernel.util.WebKeys;
+import com.liferay.portal.model.CompanyConstants;
+import com.liferay.portal.model.Group;
+import com.liferay.portal.model.Role;
+import com.liferay.portal.model.RoleConstants;
+import com.liferay.portal.model.User;
+import com.liferay.portal.service.GroupLocalServiceUtil;
+import com.liferay.portal.service.RoleLocalServiceUtil;
+import com.liferay.portal.theme.ThemeDisplay;
+import com.liferay.portal.util.PortalUtil;
+import com.liferay.portlet.documentlibrary.store.DLStoreUtil;
+import com.pentila.entSavoie.ENTRoleUtilFactory;
+import com.pentila.entSavoie.ENTRolesConstants;
+import com.pentila.entSavoie.cahierDeTexte.model.EventCDT;
+import com.pentila.entSavoie.cahierDeTexte.service.EventCDTLocalServiceUtil;
+import com.pentila.entSavoie.cahierDeTexte.util.CDTUtil;
+import com.pentila.entSavoie.directory.OrganizationFinderServiceUtil;
+
+public class ExportXLS {
+
+ static SimpleDateFormat classicDateFormat = new SimpleDateFormat("yyyy-MM-dd");
+ static SimpleDateFormat textFrenchDateFormat = new SimpleDateFormat("EEEE dd MMM yyyy", Locale.FRENCH);
+
+ static List<Long> processUserExport = new ArrayList<Long>();
+
+ // Export XLS
+ public static JSONObject exportXLS(ResourceRequest resourceRequest) throws NamingException, Exception {
+ JSONObject result = JSONFactoryUtil.createJSONObject();
+ result.put("success", false);
+
+ User currUser = null;
+
+ try {
+
+ currUser = PortalUtil.getUser(resourceRequest);
+
+ if (processUserExport.contains(currUser.getUserId())) {
+ 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é.");
+ result.put("success", true);
+ return result;
+ }
+ else {
+ processUserExport.add(currUser.getUserId());
+ }
+
+ ResourceBundle messages = ResourceBundle.getBundle("content.Language", currUser.getLocale());
+ final String startDay = ParamUtil.getString(resourceRequest, "startDay");
+ final String endDay = ParamUtil.getString(resourceRequest, "endDay");
+
+ Date startDayDate = classicDateFormat.parse(startDay);
+ Date endDayDate = classicDateFormat.parse(endDay);
+
+ boolean sortByDate = ParamUtil.getBoolean(resourceRequest, "sortByDate", false);
+ boolean sortByUser = ParamUtil.getBoolean(resourceRequest, "sortByUser", false);
+ boolean sortByCalendar = ParamUtil.getBoolean(resourceRequest, "sortByCalendar", false);
+ // boolean sortBySubject = ParamUtil.getBoolean(resourceRequest, "sortBySubject", false);
+
+ ThemeDisplay themeDisplay = (ThemeDisplay)resourceRequest.getAttribute(WebKeys.THEME_DISPLAY);
+
+ User user = PortalUtil.getUser(resourceRequest);
+ Role principalRole = ENTRoleUtilFactory.getInstance(user.getCompanyId()).getRole(ENTRolesConstants.NATIONAL_4);
+ boolean isPrincipal = RoleLocalServiceUtil.hasUserRole(user.getUserId(), principalRole.getRoleId());
+
+ Role adminRole = ENTRoleUtilFactory.getInstance(user.getCompanyId()).getRole(RoleConstants.ADMINISTRATOR);
+ boolean isAdmin = RoleLocalServiceUtil.hasUserRole(user.getUserId(), adminRole.getRoleId());
+
+ long filteredEtabOrgId = 0;
+ if (isPrincipal || isAdmin) {
+ filteredEtabOrgId = OrganizationFinderServiceUtil.getEtabRatachement(user).getOrganizationId();
+ }
+
+ byte[] export = sortByDate?exportCDTByDate(resourceRequest, filteredEtabOrgId):(sortByUser?exportCDTByUser(resourceRequest, filteredEtabOrgId):(sortByCalendar?exportCDTByCalendar(resourceRequest, filteredEtabOrgId):exportCDTBySubject(resourceRequest, filteredEtabOrgId)));
+
+ if(export!=null){
+ String folderName = ExportUtil.sendToCasier(resourceRequest, currUser, export, startDay, endDay, true);
+ 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>";
+
+ processUserExport.remove(currUser.getUserId());
+ result.put("msg", messages.getString("un-export-xls-a-ete-depose-dans-votre-casier") + " : " + hrefXLS);
+ result.put("success", true);
+ } else {
+ result.put("success", false);
+ }
+
+ } catch (Exception e) {
+ if (_log.isErrorEnabled()) {
+ _log.error(e);
+ }
+ result.put("success", false);
+ }
+ finally {
+ processUserExport.remove(currUser.getUserId());
+ }
+
+ return result;
+ }
+
+ public static byte[] exportCDTByDate(ResourceRequest resourceRequest, long filteredEtabOrgId) throws ParseException, PortalException, SystemException{
+ ThemeDisplay themeDisplay = (ThemeDisplay)resourceRequest.getAttribute(WebKeys.THEME_DISPLAY);
+ User currUser = PortalUtil.getUser(resourceRequest);
+ ResourceBundle messages = ResourceBundle.getBundle("content.Language", currUser.getLocale());
+
+ final String startDay = ParamUtil.getString(resourceRequest, "startDay");
+ final String endDay = ParamUtil.getString(resourceRequest, "endDay");
+ Date startDayDate = classicDateFormat.parse(startDay);
+ Date endDayDate = classicDateFormat.parse(endDay);
+ return exportCDTByDate(currUser, themeDisplay, messages, startDayDate, endDayDate, filteredEtabOrgId);
+ }
+
+ public static byte[] exportCDTByDate(User user, ThemeDisplay themeDisplay, ResourceBundle messages, Date startDayDate, Date endDayDate, long filteredEtabOrgId){
+
+ try {
+ User currUser = user;
+
+
+ File file = FileUtil.createTempFile("xls");
+
+ WorkbookSettings wbSettings = new WorkbookSettings();
+ wbSettings.setLocale(new Locale("en", "EN"));
+
+ WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);
+ workbook.createSheet("Export CDT", 0);
+
+ WritableSheet excelSheet = workbook.getSheet(0);
+
+ WritableFont times14ptHeaderPrincipal = new WritableFont(WritableFont.TIMES, 14, WritableFont.BOLD);
+ WritableCellFormat times14HeaderPrincipal = new WritableCellFormat(times14ptHeaderPrincipal);
+ times14HeaderPrincipal.setBorder(Border.ALL, BorderLineStyle.NONE);
+ times14HeaderPrincipal.setAlignment(Alignment.LEFT);
+ times14HeaderPrincipal.setVerticalAlignment(VerticalAlignment.CENTRE);
+
+ 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));
+ excelSheet.mergeCells(0, 0, 6, 0);
+
+ WritableFont times13pt = new WritableFont(WritableFont.TIMES, 13, WritableFont.BOLD);
+ WritableCellFormat times13 = new WritableCellFormat(times13pt);
+ times13.setBorder(Border.ALL, BorderLineStyle.HAIR);
+ times13.setAlignment(Alignment.CENTRE);
+ times13.setVerticalAlignment(VerticalAlignment.CENTRE);
+ times13.setBackground(Colour.PALE_BLUE);
+ times13.setWrap(true);
+
+ excelSheet.addCell(new Label(0, 4, "Date", times13));
+ excelSheet.addCell(new Label(1, 4, "Heure", times13));
+ excelSheet.addCell(new Label(2, 4, "Classe", times13));
+ excelSheet.addCell(new Label(3, 4, "Mati\u00e8re", times13));
+ excelSheet.addCell(new Label(4, 4, "Enseignant", times13));
+ excelSheet.addCell(new Label(5, 4, "Sujet", times13));
+ excelSheet.addCell(new Label(6, 4, "Description", times13));
+
+ WritableFont times12ptBold = new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD);
+ WritableCellFormat times12Bold = new WritableCellFormat(times12ptBold);
+ times12Bold.setBorder(Border.ALL, BorderLineStyle.HAIR);
+ times12Bold.setVerticalAlignment(VerticalAlignment.CENTRE);
+ times12Bold.setAlignment(Alignment.CENTRE);
+ times12Bold.setWrap(true);
+
+ WritableFont times12pt = new WritableFont(WritableFont.TIMES, 12);
+ WritableCellFormat times12 = new WritableCellFormat(times12pt);
+ times12.setBorder(Border.ALL, BorderLineStyle.HAIR);
+ times12.setVerticalAlignment(VerticalAlignment.CENTRE);
+ times12.setAlignment(Alignment.CENTRE);
+ times12.setWrap(true);
+
+ WritableCellFormat times12Left = new WritableCellFormat(times12pt);
+ times12Left.setBorder(Border.ALL, BorderLineStyle.HAIR);
+ times12Left.setVerticalAlignment(VerticalAlignment.TOP);
+ times12Left.setAlignment(Alignment.LEFT);
+ times12Left.setWrap(true);
+
+ //On passe les colonnes en autosize
+ CellView cv = new CellView();
+ cv.setAutosize(true);
+ for(int i=0;i<=5;i++){
+ excelSheet.setColumnView(i, cv);
+ }
+
+ CellView cv2 = new CellView();
+ cv2.setSize(150000);
+ excelSheet.setColumnView(6, cv2);
+
+ List<EventCDT> listEvt = CDTUtil.calendarEvent_list(currUser.getUserId(), 0, -1l, "[-1]", false, startDayDate, endDayDate, true, false, false, false, filteredEtabOrgId);
+
+ int row = 5;
+ int beginRow = 5;
+ Boolean firstEvent = true;
+ String currentDate = "";
+ for (EventCDT evt : listEvt) {
+ String eventDate = textFrenchDateFormat.format(evt.getStartDay());
+ if (currentDate.equals(eventDate)) {
+ // on est sur le meme jour que le precedent
+ firstEvent = false;
+ }
+ else {
+ if (!firstEvent) {
+ excelSheet.mergeCells(0, beginRow, 0, row - 1);
+ beginRow = row;
+ }
+ firstEvent = false;
+ currentDate = eventDate;
+ }
+
+ excelSheet.addCell(new Label(0, row, eventDate, times12Bold));
+ excelSheet.addCell(new Label(1, row, evt.getStartEvtTime().replace(':', 'h')+" - "+evt.getEndEvtTime().replace(':', 'h'), times12));
+ Group group = GroupLocalServiceUtil.getGroup(evt.getGroupId());
+ excelSheet.addCell(new Label(2, row, ExportUtil.getClasseNameCached(group), times12));
+ excelSheet.addCell(new Label(3, row, ExportUtil.getMatiereNameCached(evt.getSubjectCDT()), times12));
+ excelSheet.addCell(new Label(4, row, ExportUtil.getUserNameCached(evt.getCreator()), times12));
+ excelSheet.addCell(new Label(5, row, evt.getSubject(), times12));
+ excelSheet.addCell(new Label(6, row, getDescription(evt, themeDisplay, currUser), times12Left));
+
+ row++;
+ }
+
+ excelSheet.mergeCells(0, beginRow, 0, row - 1);
+
+ workbook.write();
+ workbook.close();
+
+ return FileUtil.getBytes(file);
+ } catch (Exception e){
+ if (_log.isErrorEnabled()) {
+ _log.error(e);
+ }
+ return null;
+ }
+
+ }
+
+ public static byte[] exportCDTByUser(ResourceRequest resourceRequest, long filteredEtabOrgId) throws ParseException, PortalException, SystemException{
+ ThemeDisplay themeDisplay = (ThemeDisplay)resourceRequest.getAttribute(WebKeys.THEME_DISPLAY);
+ User currUser = PortalUtil.getUser(resourceRequest);
+ ResourceBundle messages = ResourceBundle.getBundle("content.Language", currUser.getLocale());
+
+ final String startDay = ParamUtil.getString(resourceRequest, "startDay");
+ final String endDay = ParamUtil.getString(resourceRequest, "endDay");
+ Date startDayDate = classicDateFormat.parse(startDay);
+ Date endDayDate = classicDateFormat.parse(endDay);
+ return exportCDTByUser(currUser, themeDisplay, messages, startDayDate, endDayDate, filteredEtabOrgId);
+ }
+
+
+ public static byte[] exportCDTByUser(User user, ThemeDisplay themeDisplay, ResourceBundle messages, Date startDayDate, Date endDayDate, long filteredEtabOrgId){
+
+ try {
+
+ User currUser = user;
+
+
+ File file = FileUtil.createTempFile("xls");
+
+ WorkbookSettings wbSettings = new WorkbookSettings();
+ wbSettings.setLocale(new Locale("en", "EN"));
+
+ WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);
+ workbook.createSheet("Export CDT", 0);
+
+ WritableSheet excelSheet = workbook.getSheet(0);
+
+ WritableFont times14ptHeaderPrincipal = new WritableFont(WritableFont.TIMES, 14, WritableFont.BOLD);
+ WritableCellFormat times14HeaderPrincipal = new WritableCellFormat(times14ptHeaderPrincipal);
+ times14HeaderPrincipal.setBorder(Border.ALL, BorderLineStyle.NONE);
+ times14HeaderPrincipal.setAlignment(Alignment.LEFT);
+ times14HeaderPrincipal.setVerticalAlignment(VerticalAlignment.CENTRE);
+
+ 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));
+ excelSheet.mergeCells(0, 0, 6, 0);
+
+ WritableFont times13pt = new WritableFont(WritableFont.TIMES, 13, WritableFont.BOLD);
+ WritableCellFormat times13 = new WritableCellFormat(times13pt);
+ times13.setBorder(Border.ALL, BorderLineStyle.HAIR);
+ times13.setAlignment(Alignment.CENTRE);
+ times13.setVerticalAlignment(VerticalAlignment.CENTRE);
+ times13.setBackground(Colour.PALE_BLUE);
+ times13.setWrap(true);
+
+ excelSheet.addCell(new Label(0, 4, "Enseignant", times13));
+ excelSheet.addCell(new Label(1, 4, "Date", times13));
+ excelSheet.addCell(new Label(2, 4, "Heure", times13));
+ excelSheet.addCell(new Label(3, 4, "Classe", times13));
+ excelSheet.addCell(new Label(4, 4, "Mati\u00e8re", times13));
+ excelSheet.addCell(new Label(5, 4, "Sujet", times13));
+ excelSheet.addCell(new Label(6, 4, "Description", times13));
+
+ WritableFont times12ptBold = new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD);
+ WritableCellFormat times12Bold = new WritableCellFormat(times12ptBold);
+ times12Bold.setBorder(Border.ALL, BorderLineStyle.HAIR);
+ times12Bold.setVerticalAlignment(VerticalAlignment.CENTRE);
+ times12Bold.setAlignment(Alignment.CENTRE);
+ times12Bold.setWrap(true);
+
+ WritableFont times12pt = new WritableFont(WritableFont.TIMES, 12);
+ WritableCellFormat times12 = new WritableCellFormat(times12pt);
+ times12.setBorder(Border.ALL, BorderLineStyle.HAIR);
+ times12.setVerticalAlignment(VerticalAlignment.CENTRE);
+ times12.setAlignment(Alignment.CENTRE);
+ times12.setWrap(true);
+
+ WritableCellFormat times12Left = new WritableCellFormat(times12pt);
+ times12Left.setBorder(Border.ALL, BorderLineStyle.HAIR);
+ times12Left.setVerticalAlignment(VerticalAlignment.TOP);
+ times12Left.setAlignment(Alignment.LEFT);
+ times12Left.setWrap(true);
+
+ //On passe les colonnes en autosize
+ CellView cv = new CellView();
+ cv.setAutosize(true);
+ for(int i=0;i<=5;i++){
+ excelSheet.setColumnView(i, cv);
+ }
+
+ CellView cv2 = new CellView();
+ cv2.setSize(150000);
+ excelSheet.setColumnView(6, cv2);
+
+ List<EventCDT> listEvt = CDTUtil.calendarEvent_list(currUser.getUserId(), 0, -1l, "[-1]", false, startDayDate, endDayDate, false, true, false, false, filteredEtabOrgId);
+
+ int row = 5;
+ int beginRow = 5;
+ Boolean firstEvent = true;
+ String currentUser = "";
+ for (EventCDT evt : listEvt) {
+ String eventUser = ExportUtil.getUserNameCached(evt.getCreator());
+ if (currentUser.equals(eventUser)) {
+ // on est sur le meme jour que le precedent
+ firstEvent = false;
+ }
+ else {
+ if (!firstEvent) {
+ excelSheet.mergeCells(0, beginRow, 0, row - 1);
+ beginRow = row;
+ }
+ firstEvent = false;
+ currentUser = eventUser;
+ }
+
+ excelSheet.addCell(new Label(0, row, eventUser, times12Bold));
+ excelSheet.addCell(new Label(1, row, textFrenchDateFormat.format(evt.getStartDay()), times12));
+ excelSheet.addCell(new Label(2, row, evt.getStartEvtTime().replace(':', 'h')+" - "+evt.getEndEvtTime().replace(':', 'h'), times12));
+ Group group = GroupLocalServiceUtil.getGroup(evt.getGroupId());
+ excelSheet.addCell(new Label(3, row, ExportUtil.getClasseNameCached(group), times12));
+ excelSheet.addCell(new Label(4, row, ExportUtil.getMatiereNameCached(evt.getSubjectCDT()), times12));
+ excelSheet.addCell(new Label(5, row, evt.getSubject(), times12));
+ excelSheet.addCell(new Label(6, row, getDescription(evt, themeDisplay, currUser), times12Left));
+
+ row++;
+ }
+
+ excelSheet.mergeCells(0, beginRow, 0, row - 1);
+
+ workbook.write();
+ workbook.close();
+
+ return FileUtil.getBytes(file);
+ } catch (Exception e){
+ if (_log.isErrorEnabled()) {
+ _log.error(e);
+ }
+ return null;
+ }
+
+ }
+
+ public static byte[] exportCDTByCalendar(ResourceRequest resourceRequest, long filteredEtabOrgId) throws ParseException, PortalException, SystemException{
+ ThemeDisplay themeDisplay = (ThemeDisplay)resourceRequest.getAttribute(WebKeys.THEME_DISPLAY);
+ User currUser = PortalUtil.getUser(resourceRequest);
+ ResourceBundle messages = ResourceBundle.getBundle("content.Language", currUser.getLocale());
+
+ final String startDay = ParamUtil.getString(resourceRequest, "startDay");
+ final String endDay = ParamUtil.getString(resourceRequest, "endDay");
+ Date startDayDate = classicDateFormat.parse(startDay);
+ Date endDayDate = classicDateFormat.parse(endDay);
+ return exportCDTByCalendar(currUser, themeDisplay, messages, startDayDate, endDayDate, filteredEtabOrgId);
+ }
+
+
+ public static byte[] exportCDTByCalendar(User user, ThemeDisplay themeDisplay, ResourceBundle messages, Date startDayDate, Date endDayDate, long filteredEtabOrgId){
+ try {
+ User currUser = user;
+
+ File file = FileUtil.createTempFile("xls");
+
+ WorkbookSettings wbSettings = new WorkbookSettings();
+ wbSettings.setLocale(new Locale("en", "EN"));
+
+ WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);
+ workbook.createSheet("Export CDT", 0);
+
+ WritableSheet excelSheet = workbook.getSheet(0);
+
+ WritableFont times14ptHeaderPrincipal = new WritableFont(WritableFont.TIMES, 14, WritableFont.BOLD);
+ WritableCellFormat times14HeaderPrincipal = new WritableCellFormat(times14ptHeaderPrincipal);
+ times14HeaderPrincipal.setBorder(Border.ALL, BorderLineStyle.NONE);
+ times14HeaderPrincipal.setAlignment(Alignment.LEFT);
+ times14HeaderPrincipal.setVerticalAlignment(VerticalAlignment.CENTRE);
+
+ 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));
+ excelSheet.mergeCells(0, 0, 6, 0);
+
+ WritableFont times13pt = new WritableFont(WritableFont.TIMES, 13, WritableFont.BOLD);
+ WritableCellFormat times13 = new WritableCellFormat(times13pt);
+ times13.setBorder(Border.ALL, BorderLineStyle.HAIR);
+ times13.setAlignment(Alignment.CENTRE);
+ times13.setVerticalAlignment(VerticalAlignment.CENTRE);
+ times13.setBackground(Colour.PALE_BLUE);
+ times13.setWrap(true);
+
+ excelSheet.addCell(new Label(0, 4, "Classe", times13));
+ excelSheet.addCell(new Label(1, 4, "Date", times13));
+ excelSheet.addCell(new Label(2, 4, "Heure", times13));
+ excelSheet.addCell(new Label(3, 4, "Mati\u00e8re", times13));
+ excelSheet.addCell(new Label(4, 4, "Enseignant", times13));
+ excelSheet.addCell(new Label(5, 4, "Sujet", times13));
+ excelSheet.addCell(new Label(6, 4, "Description", times13));
+
+ WritableFont times12ptBold = new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD);
+ WritableCellFormat times12Bold = new WritableCellFormat(times12ptBold);
+ times12Bold.setBorder(Border.ALL, BorderLineStyle.HAIR);
+ times12Bold.setVerticalAlignment(VerticalAlignment.CENTRE);
+ times12Bold.setAlignment(Alignment.CENTRE);
+ times12Bold.setWrap(true);
+
+ WritableFont times12pt = new WritableFont(WritableFont.TIMES, 12);
+ WritableCellFormat times12 = new WritableCellFormat(times12pt);
+ times12.setBorder(Border.ALL, BorderLineStyle.HAIR);
+ times12.setVerticalAlignment(VerticalAlignment.CENTRE);
+ times12.setAlignment(Alignment.CENTRE);
+ times12.setWrap(true);
+
+ WritableCellFormat times12Left = new WritableCellFormat(times12pt);
+ times12Left.setBorder(Border.ALL, BorderLineStyle.HAIR);
+ times12Left.setVerticalAlignment(VerticalAlignment.TOP);
+ times12Left.setAlignment(Alignment.LEFT);
+ times12Left.setWrap(true);
+
+ //On passe les colonnes en autosize
+ CellView cv = new CellView();
+ cv.setAutosize(true);
+ for(int i=0;i<=5;i++){
+ excelSheet.setColumnView(i, cv);
+ }
+
+ CellView cv2 = new CellView();
+ cv2.setSize(150000);
+ excelSheet.setColumnView(6, cv2);
+
+ List<EventCDT> listEvt = CDTUtil.calendarEvent_list(currUser.getUserId(), 0, -1l, "[-1]", false, startDayDate, endDayDate, false, false, true, false, filteredEtabOrgId);
+
+ int row = 5;
+ int beginRow = 5;
+ Boolean firstEvent = true;
+ String currentCalendar = "";
+ for (EventCDT evt : listEvt) {
+ Group group = GroupLocalServiceUtil.getGroup(evt.getGroupId());
+ String eventCalendar = ExportUtil.getClasseNameCached(group);
+ if (currentCalendar.equals(eventCalendar)) {
+ // on est sur le meme jour que le precedent
+ firstEvent = false;
+ }
+ else {
+ if (!firstEvent) {
+ excelSheet.mergeCells(0, beginRow, 0, row - 1);
+ beginRow = row;
+ }
+ firstEvent = false;
+ currentCalendar = eventCalendar;
+ }
+
+ excelSheet.addCell(new Label(0, row, eventCalendar, times12Bold));
+ excelSheet.addCell(new Label(1, row, textFrenchDateFormat.format(evt.getStartDay()), times12));
+ excelSheet.addCell(new Label(2, row, evt.getStartEvtTime().replace(':', 'h')+" - "+evt.getEndEvtTime().replace(':', 'h'), times12));
+ excelSheet.addCell(new Label(3, row, ExportUtil.getMatiereNameCached(evt.getSubjectCDT()), times12));
+ excelSheet.addCell(new Label(4, row, ExportUtil.getUserNameCached(evt.getCreator()), times12));
+ excelSheet.addCell(new Label(5, row, evt.getSubject(), times12));
+ excelSheet.addCell(new Label(6, row, getDescription(evt, themeDisplay, currUser), times12Left));
+
+ row++;
+ }
+
+ excelSheet.mergeCells(0, beginRow, 0, row - 1);
+
+ workbook.write();
+ workbook.close();
+
+ return FileUtil.getBytes(file);
+ } catch (Exception e){
+ if (_log.isErrorEnabled()) {
+ _log.error(e);
+ }
+ return null;
+ }
+
+ }
+
+ public static byte[] exportCDTBySubject(ResourceRequest resourceRequest, long filteredEtabOrgId) throws ParseException, PortalException, SystemException{
+ ThemeDisplay themeDisplay = (ThemeDisplay)resourceRequest.getAttribute(WebKeys.THEME_DISPLAY);
+ User currUser = PortalUtil.getUser(resourceRequest);
+ ResourceBundle messages = ResourceBundle.getBundle("content.Language", currUser.getLocale());
+
+ final String startDay = ParamUtil.getString(resourceRequest, "startDay");
+ final String endDay = ParamUtil.getString(resourceRequest, "endDay");
+ Date startDayDate = classicDateFormat.parse(startDay);
+ Date endDayDate = classicDateFormat.parse(endDay);
+ return exportCDTBySubject(currUser, themeDisplay, messages, startDayDate, endDayDate, filteredEtabOrgId);
+ }
+
+
+ public static byte[] exportCDTBySubject(User user, ThemeDisplay themeDisplay, ResourceBundle messages, Date startDayDate, Date endDayDate, long filteredEtabOrgId){
+ try {
+ User currUser = user;
+
+ File file = FileUtil.createTempFile("xls");
+
+ WorkbookSettings wbSettings = new WorkbookSettings();
+ wbSettings.setLocale(new Locale("en", "EN"));
+
+ WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);
+ workbook.createSheet("Export CDT", 0);
+
+ WritableSheet excelSheet = workbook.getSheet(0);
+
+ WritableFont times14ptHeaderPrincipal = new WritableFont(WritableFont.TIMES, 14, WritableFont.BOLD);
+ WritableCellFormat times14HeaderPrincipal = new WritableCellFormat(times14ptHeaderPrincipal);
+ times14HeaderPrincipal.setBorder(Border.ALL, BorderLineStyle.NONE);
+ times14HeaderPrincipal.setAlignment(Alignment.LEFT);
+ times14HeaderPrincipal.setVerticalAlignment(VerticalAlignment.CENTRE);
+
+ 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));
+ excelSheet.mergeCells(0, 0, 6, 0);
+
+ WritableFont times13pt = new WritableFont(WritableFont.TIMES, 13, WritableFont.BOLD);
+ WritableCellFormat times13 = new WritableCellFormat(times13pt);
+ times13.setBorder(Border.ALL, BorderLineStyle.HAIR);
+ times13.setAlignment(Alignment.CENTRE);
+ times13.setVerticalAlignment(VerticalAlignment.CENTRE);
+ times13.setBackground(Colour.PALE_BLUE);
+ times13.setWrap(true);
+
+ excelSheet.addCell(new Label(0, 4, "Mati\u00e8re", times13));
+ excelSheet.addCell(new Label(1, 4, "Date", times13));
+ excelSheet.addCell(new Label(2, 4, "Heure", times13));
+ excelSheet.addCell(new Label(3, 4, "Classe", times13));
+ excelSheet.addCell(new Label(4, 4, "Enseignant", times13));
+ excelSheet.addCell(new Label(5, 4, "Sujet", times13));
+ excelSheet.addCell(new Label(6, 4, "Description", times13));
+
+ WritableFont times12ptBold = new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD);
+ WritableCellFormat times12Bold = new WritableCellFormat(times12ptBold);
+ times12Bold.setBorder(Border.ALL, BorderLineStyle.HAIR);
+ times12Bold.setVerticalAlignment(VerticalAlignment.CENTRE);
+ times12Bold.setAlignment(Alignment.CENTRE);
+ times12Bold.setWrap(true);
+
+ WritableFont times12pt = new WritableFont(WritableFont.TIMES, 12);
+ WritableCellFormat times12 = new WritableCellFormat(times12pt);
+ times12.setBorder(Border.ALL, BorderLineStyle.HAIR);
+ times12.setVerticalAlignment(VerticalAlignment.CENTRE);
+ times12.setAlignment(Alignment.CENTRE);
+ times12.setWrap(true);
+
+ WritableCellFormat times12Left = new WritableCellFormat(times12pt);
+ times12Left.setBorder(Border.ALL, BorderLineStyle.HAIR);
+ times12Left.setVerticalAlignment(VerticalAlignment.TOP);
+ times12Left.setAlignment(Alignment.LEFT);
+ times12Left.setWrap(true);
+
+ //On passe les colonnes en autosize
+ CellView cv = new CellView();
+ cv.setAutosize(true);
+ for(int i=0;i<=5;i++){
+ excelSheet.setColumnView(i, cv);
+ }
+
+ CellView cv2 = new CellView();
+ cv2.setSize(150000);
+ excelSheet.setColumnView(6, cv2);
+
+ List<EventCDT> listEvt = CDTUtil.calendarEvent_list(currUser.getUserId(), 0, -1l, "[-1]", false, startDayDate, endDayDate, false, false, false, true, filteredEtabOrgId);
+
+ int row = 5;
+ int beginRow = 5;
+ Boolean firstEvent = true;
+ String currentSubject = "";
+ for (EventCDT evt : listEvt) {
+ String eventSubject = ExportUtil.getMatiereNameCached(evt.getSubjectCDT());
+ if (currentSubject.equals(eventSubject)) {
+ // on est sur le meme jour que le precedent
+ firstEvent = false;
+ }
+ else {
+ if (!firstEvent) {
+ excelSheet.mergeCells(0, beginRow, 0, row - 1);
+ beginRow = row;
+ }
+ firstEvent = false;
+ currentSubject = eventSubject;
+ }
+
+ excelSheet.addCell(new Label(0, row, eventSubject, times12Bold));
+ excelSheet.addCell(new Label(1, row, textFrenchDateFormat.format(evt.getStartDay()), times12));
+ excelSheet.addCell(new Label(2, row, evt.getStartEvtTime().replace(':', 'h')+" - "+evt.getEndEvtTime().replace(':', 'h'), times12));
+ Group group = GroupLocalServiceUtil.getGroup(evt.getGroupId());
+ excelSheet.addCell(new Label(3, row, ExportUtil.getClasseNameCached(group), times12));
+ excelSheet.addCell(new Label(4, row, ExportUtil.getUserNameCached(evt.getCreator()), times12));
+ excelSheet.addCell(new Label(5, row, evt.getSubject(), times12));
+ excelSheet.addCell(new Label(6, row, getDescription(evt, themeDisplay, currUser), times12Left));
+
+ row++;
+ }
+
+ excelSheet.mergeCells(0, beginRow, 0, row - 1);
+
+ workbook.write();
+ workbook.close();
+
+ return FileUtil.getBytes(file);
+ } catch (Exception e){
+ if (_log.isErrorEnabled()) {
+ _log.error(e);
+ }
+ return null;
+ }
+
+ }
+
+ private static String getDescription(EventCDT evt, ThemeDisplay themeDisplay, User user) throws PortalException, SystemException{
+ String description = "";
+
+ // Description
+ if (evt.getBroadcast() || (user.getUserId() == evt.getCreator())) {
+ if (!evt.getDescription().isEmpty()) {
+ description += "Description : \n";
+ description += HtmlUtil.extractText(evt.getDescription());
+ }
+ }
+
+ // PJ
+ if (evt.getBroadcast() || (user.getUserId() == evt.getCreator())) {
+ String[] eventCDTAttachments = EventCDTLocalServiceUtil.getAttachFilesNameToEventCDT(user.getCompanyId(), evt.getEvtId());
+ String href = "";
+ for (int i = 0; i < eventCDTAttachments.length; i++) {
+ String fileName = FileUtil.getShortFileName(eventCDTAttachments[i]);
+ long fileSize = DLStoreUtil.getFileSize(user.getCompanyId(), CompanyConstants.SYSTEM, eventCDTAttachments[i])/1000;
+ if (fileSize==0) {
+ fileSize = 1;
+ }
+ href += fileName + " (" + fileSize + " kb)" + ", ";
+ }
+
+ if (eventCDTAttachments.length>0) {
+ description += "\n\nPi\u00e8ces jointes : " + href.substring(0, href.length()-2);
+ }
+ }
+
+ // Devoirs a rendre
+ List<EventCDT> evtCDTDevoir = new ArrayList<EventCDT>();
+ evtCDTDevoir.addAll(EventCDTLocalServiceUtil.getToEventCDT(evt.getEvtId()));
+ List<EventCDT> filteredEventToDo = new ArrayList<EventCDT>();
+
+ Collections.sort(evtCDTDevoir);
+
+ if (user.getUserId() != evt.getCreator()) {
+
+ for (EventCDT devoirARendre : evtCDTDevoir) {
+ if (devoirARendre.getBroadcast()) {
+ filteredEventToDo.add(devoirARendre);
+ }
+ }
+ }
+ else {
+ filteredEventToDo.addAll(evtCDTDevoir);
+ }
+
+ if (!filteredEventToDo.isEmpty()) {
+ description += "\n\nDevoirs \u00e0 rendre : \n";
+
+ for (EventCDT devoir : evtCDTDevoir) {
+ try {
+ EventCDT evtFrom = EventCDTLocalServiceUtil.getEventCDT(devoir.getFromEvt());
+ description += "\u0020\u0020\u0020\u0020- Devoir donn\u00e9 le " + (new SimpleDateFormat("EEEE dd MMM yyyy", user.getLocale())).format(evtFrom.getStartDay()) + "\n";
+ }
+ catch (Exception exc1){}
+
+ if (!devoir.getSubject().isEmpty()) {
+ description += "\u0020\u0020\u0020\u0020\u0020\u0020" + devoir.getSubject() + " : \n";
+ }
+ if (!devoir.getDescription().isEmpty()) {
+ description += "\u0020\u0020\u0020\u0020\u0020\u0020" + HtmlUtil.extractText(devoir.getDescription()) + "\n";
+ }
+ else{
+ description += "\n";
+ }
+ String[] eventCDTDevoirAttachments = EventCDTLocalServiceUtil.getAttachFilesNameToEventCDT(user.getCompanyId(), devoir.getEvtId());
+ String hrefDevoir = "";
+ for (int i = 0; i < eventCDTDevoirAttachments.length; i++) {
+ String fileName = FileUtil.getShortFileName(eventCDTDevoirAttachments[i]);
+ long fileSize = DLStoreUtil.getFileSize(user.getCompanyId(), CompanyConstants.SYSTEM, eventCDTDevoirAttachments[i])/1000;
+ if (fileSize==0) {
+ fileSize = 1;
+ }
+ hrefDevoir += fileName + " (" + fileSize + " kb), ";
+ }
+ if (eventCDTDevoirAttachments.length>0) {
+ description += "\u0020\u0020\u0020\u0020\u0020\u0020Pi\u00e8ces jointes : " + hrefDevoir.substring(0, hrefDevoir.length()-2);
+ description += "\n";
+ }
+ }
+ }
+
+ // Devoirs donnes
+ if (evt.getBroadcast() || (user.getUserId() == evt.getCreator())) {
+ evtCDTDevoir = EventCDTLocalServiceUtil.getFromEventCDT(evt.getEvtId());
+ if (!evtCDTDevoir.isEmpty()) {
+ description += "\n\nDevoirs donn\u00e9s : \n";
+
+ for (EventCDT devoir : evtCDTDevoir) {
+ description += "\u0020\u0020\u0020\u0020- Devoirs \u00e0 rendre pour le " + (new SimpleDateFormat("EEEE dd MMM yyyy", user.getLocale())).format(devoir.getStartDay()) + "\n";
+ if (!devoir.getSubject().isEmpty()) {
+ description += "\u0020\u0020\u0020\u0020\u0020\u0020" + devoir.getSubject() + " : \n";
+ }
+ if (!devoir.getDescription().isEmpty()) {
+ description += "\u0020\u0020\u0020\u0020\u0020\u0020" + HtmlUtil.extractText(devoir.getDescription()) + "\n";
+ }
+ else{
+ description += "\n";
+ }
+
+ String[] eventCDTDevoirAttachments = EventCDTLocalServiceUtil.getAttachFilesNameToEventCDT(user.getCompanyId(), devoir.getEvtId());
+ String hrefDevoir = "";
+ for (int i = 0; i < eventCDTDevoirAttachments.length; i++) {
+ String fileName = FileUtil.getShortFileName(eventCDTDevoirAttachments[i]);
+ long fileSize = DLStoreUtil.getFileSize(user.getCompanyId(), CompanyConstants.SYSTEM, eventCDTDevoirAttachments[i])/1000;
+ if (fileSize==0) {
+ fileSize = 1;
+ }
+ hrefDevoir += fileName + " (" + fileSize + " kb), ";
+ }
+ if (eventCDTDevoirAttachments.length>0) {
+ description += "\u0020\u0020\u0020\u0020\u0020\u0020Pi\u00e8ces jointes : " + hrefDevoir.substring(0, hrefDevoir.length()-2);
+ description += "\n";
+ }
+ }
+ }
+ }
+
+ return description;
+ }
+
+ private static Log _log = LogFactoryUtil.getLog(ExportXLS.class);
+}