Examples with ReportInstance com.serotonin.m2m2.reports.vo.ReportInstance used on opensource projects

Example 6 with ReportInstance

use of com.serotonin.m2m2.reports.vo.ReportInstance in project ma-modules-public by infiniteautomation.

the class ReportDao method runReportNoSQL.

/**
 * Generate a report using the NoSQL DB for point value storage
 * @param instance
 * @param points
 * @return
 */
public int runReportNoSQL(final ReportInstance instance, List<PointInfo> points) {
    PointValueDao pointValueDao = Common.databaseProxy.newPointValueDao();
    final MappedCallbackCounter count = new MappedCallbackCounter();
    final NoSQLDao dao = Common.databaseProxy.getNoSQLProxy().createNoSQLDao(ReportPointValueTimeSerializer.get(), "reports");
    // The timestamp selection code is used multiple times for different tables
    long startTime, endTime;
    String timestampSql;
    Object[] timestampParams;
    if (instance.isFromInception() && instance.isToNow()) {
        timestampSql = "";
        timestampParams = new Object[0];
        startTime = 0l;
        endTime = Common.timer.currentTimeMillis();
    } else if (instance.isFromInception()) {
        timestampSql = "and ${field}<?";
        timestampParams = new Object[] { instance.getReportEndTime() };
        startTime = 0l;
        endTime = instance.getReportEndTime();
    } else if (instance.isToNow()) {
        timestampSql = "and ${field}>=?";
        timestampParams = new Object[] { instance.getReportStartTime() };
        startTime = instance.getReportStartTime();
        endTime = Common.timer.currentTimeMillis();
    } else {
        timestampSql = "and ${field}>=? and ${field}<?";
        timestampParams = new Object[] { instance.getReportStartTime(), instance.getReportEndTime() };
        startTime = instance.getReportStartTime();
        endTime = instance.getReportEndTime();
    }
    // For each point.
    List<Integer> pointIds = new ArrayList<Integer>();
    // Map the pointId to the Report PointId
    final Map<Integer, Integer> pointIdMap = new HashMap<Integer, Integer>();
    // the reports table/data store
    for (PointInfo pointInfo : points) {
        DataPointVO point = pointInfo.getPoint();
        pointIds.add(point.getId());
        int dataType = point.getPointLocator().getDataTypeId();
        DataValue startValue = null;
        if (!instance.isFromInception()) {
            // Get the value just before the start of the report
            PointValueTime pvt = pointValueDao.getPointValueBefore(point.getId(), instance.getReportStartTime());
            if (pvt != null)
                startValue = pvt.getValue();
            // Make sure the data types match
            if (DataTypes.getDataType(startValue) != dataType)
                startValue = null;
        }
        // Insert the reportInstancePoints record
        String name = Functions.truncate(point.getName(), 100);
        int reportPointId = doInsert(REPORT_INSTANCE_POINTS_INSERT, new Object[] { instance.getId(), point.getDeviceName(), name, pointInfo.getPoint().getXid(), dataType, DataTypes.valueToString(startValue), SerializationHelper.writeObject(point.getTextRenderer()), pointInfo.getColour(), pointInfo.getWeight(), boolToChar(pointInfo.isConsolidatedChart()), pointInfo.getPlotType() }, new int[] { Types.INTEGER, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.INTEGER, Types.VARCHAR, Types.BLOB, Types.VARCHAR, Types.FLOAT, Types.CHAR, Types.INTEGER });
        // Keep the info in the map
        pointIdMap.put(pointInfo.getPoint().getId(), reportPointId);
        // Insert the reportInstanceDataAnnotations records
        ejt.update(// 
        "insert into reportInstanceDataAnnotations " + // 
        "  (pointValueId, reportInstancePointId, textPointValueShort, textPointValueLong, sourceMessage) " + // 
        "  select rd.pointValueId, rd.reportInstancePointId, pva.textPointValueShort, " + // 
        "    pva.textPointValueLong, pva.sourceMessage " + // 
        "  from reportInstanceData rd " + // 
        "    join reportInstancePoints rp on rd.reportInstancePointId = rp.id " + // 
        "    join pointValueAnnotations pva on rd.pointValueId = pva.pointValueId " + "  where rp.id = ?", new Object[] { reportPointId });
        // Insert the reportInstanceEvents records for the point.
        if (instance.getIncludeEvents() != ReportVO.EVENTS_NONE) {
            String eventSQL = // 
            "insert into reportInstanceEvents " + // 
            "  (eventId, reportInstanceId, typeName, subtypeName, typeRef1, typeRef2, activeTs, " + // 
            "   rtnApplicable, rtnTs, rtnCause, alarmLevel, message, ackTs, ackUsername, " + // 
            "   alternateAckSource)" + "  select e.id, " + instance.getId() + // 
            ", e.typeName, e.subtypeName, e.typeRef1, " + // 
            "    e.typeRef2, e.activeTs, e.rtnApplicable, e.rtnTs, e.rtnCause, e.alarmLevel, " + // 
            "    e.message, e.ackTs, u.username, e.alternateAckSource " + // 
            "  from events e join userEvents ue on ue.eventId=e.id " + // 
            "    left join users u on e.ackUserId=u.id " + // 
            "  where ue.userId=? " + // 
            "    and e.typeName=? " + "    and e.typeRef1=? ";
            if (instance.getIncludeEvents() == ReportVO.EVENTS_ALARMS)
                eventSQL += "and e.alarmLevel > 0 ";
            eventSQL += StringUtils.replaceMacro(timestampSql, "field", "e.activeTs");
            ejt.update(eventSQL, appendParameters(timestampParams, instance.getUserId(), EventType.EventTypeNames.DATA_POINT, point.getId()));
        }
        // Insert the reportInstanceUserComments records for the point.
        if (instance.isIncludeUserComments()) {
            String commentSQL = // 
            "insert into reportInstanceUserComments " + // 
            "  (reportInstanceId, username, commentType, typeKey, ts, commentText)" + "  select " + instance.getId() + ", u.username, " + UserCommentVO.TYPE_POINT + // 
            ", " + reportPointId + // 
            ", uc.ts, uc.commentText " + // 
            "  from userComments uc " + // 
            "    left join users u on uc.userId=u.id " + "  where uc.commentType=" + // 
            UserCommentVO.TYPE_POINT + "    and uc.typeKey=? ";
            // Only include comments made in the duration of the report.
            commentSQL += StringUtils.replaceMacro(timestampSql, "field", "uc.ts");
            ejt.update(commentSQL, appendParameters(timestampParams, point.getId()));
        }
    }
    // end for all points
    // Insert the data into the NoSQL DB and track first/last times
    // The series name is reportInstanceId_reportPointId
    final AtomicLong firstPointTime = new AtomicLong(Long.MAX_VALUE);
    final AtomicLong lastPointTime = new AtomicLong(-1l);
    final String reportId = Integer.toString(instance.getId()) + "_";
    pointValueDao.getPointValuesBetween(pointIds, startTime, endTime, new MappedRowCallback<IdPointValueTime>() {

        @Override
        public void row(final IdPointValueTime ipvt, int rowId) {
            dao.storeData(reportId + Integer.toString(pointIdMap.get(ipvt.getId())), ipvt);
            count.increment();
            if (ipvt.getTime() < firstPointTime.get())
                firstPointTime.set(ipvt.getTime());
            if (ipvt.getTime() > lastPointTime.get())
                lastPointTime.set(ipvt.getTime());
        }
    });
    // Insert the reportInstanceUserComments records for the selected events
    if (instance.isIncludeUserComments()) {
        String commentSQL = // 
        "insert into reportInstanceUserComments " + // 
        "  (reportInstanceId, username, commentType, typeKey, ts, commentText)" + "  select " + instance.getId() + ", u.username, " + UserCommentVO.TYPE_EVENT + // 
        ", uc.typeKey, " + // 
        "    uc.ts, uc.commentText " + // 
        "  from userComments uc " + // 
        "    left join users u on uc.userId=u.id " + // 
        "    join reportInstanceEvents re on re.eventId=uc.typeKey " + "  where uc.commentType=" + // 
        UserCommentVO.TYPE_EVENT + "    and re.reportInstanceId=? ";
        ejt.update(commentSQL, new Object[] { instance.getId() });
    }
    // If the report had undefined start or end times, update them with values from the data.
    if (instance.isFromInception() || instance.isToNow()) {
        if (instance.isFromInception()) {
            if (firstPointTime.get() != Long.MAX_VALUE)
                instance.setReportStartTime(firstPointTime.get());
        }
        if (instance.isToNow()) {
            instance.setReportEndTime(lastPointTime.get());
        }
    }
    return count.getCount();
}

Example 7 with ReportInstance

use of com.serotonin.m2m2.reports.vo.ReportInstance in project ma-modules-public by infiniteautomation.

the class ReportDao method purgeReportsBefore.

public int purgeReportsBefore(final long time) {
    // Check to see if we are using NoSQL
    if (Common.databaseProxy.getNoSQLProxy() == null) {
        return ejt.update("delete from reportInstances where runStartTime<? and preventPurge=?", new Object[] { time, boolToChar(false) });
    } else {
        // We need to get the report instances to delete first
        List<ReportInstance> instances = query(REPORT_INSTANCE_SELECT + "where runStartTime<? and preventPurge=?", new Object[] { time, boolToChar(false) }, new ReportInstanceRowMapper());
        final NoSQLDao dao = Common.databaseProxy.getNoSQLProxy().createNoSQLDao(ReportPointValueTimeSerializer.get(), "reports");
        for (ReportInstance instance : instances) {
            List<ExportPointInfo> points = this.getReportInstancePoints(instance.getId());
            // Drop the series for these
            for (ExportPointInfo point : points) {
                dao.deleteStore(instance.getId() + "_" + point.getReportPointId());
            }
        }
        int deleted = ejt.update("delete from reportInstances where runStartTime<? and preventPurge=?", new Object[] { time, boolToChar(false) });
        instanceCountMonitor.addValue(-deleted);
        return deleted;
    }
}

Example 8 with ReportInstance

use of com.serotonin.m2m2.reports.vo.ReportInstance in project ma-modules-public by infiniteautomation.

the class ReportChartCreator method createContent.

/**
 * Uses the given parameters to create the data for the fields of this class. Once the content has been created the
 * getters for the fields can be used to retrieve.
 *
 * @param host - Mango's hostname
 * @param port - Mango's port
 * @param reportInstance
 * @param reportDao
 * @param inlinePrefix
 *            if this is non-null, it implies that the content should be inline.
 * @param createExportFile
 */
public void createContent(String host, int port, ReportInstance reportInstance, ReportDao reportDao, String inlinePrefix, boolean createExportFile) {
    this.inlinePrefix = inlinePrefix;
    reportInstance.setTranslations(translations);
    // Use a stream handler to get the report data from the database.
    StreamHandler handler = new StreamHandler(host, port, reportInstance.getXidMap(), reportInstance.getReportStartTime(), reportInstance.getReportEndTime(), IMAGE_WIDTH, createExportFile, translations);
    // Process the report content with the handler.
    if (Common.databaseProxy.getNoSQLProxy() == null)
        reportDao.reportInstanceDataSQL(reportInstance.getId(), handler);
    else
        reportDao.reportInstanceDataNoSQL(reportInstance.getId(), handler);
    pointStatistics = handler.getPointStatistics();
    devices = handler.getDevices();
    pointMap = handler.getStatisticsMap();
    UsedImagesDirective inlineImages = new UsedImagesDirective();
    SubjectDirective subjectDirective = new SubjectDirective(translations);
    // Prepare the model for the content rendering.
    Map<String, Object> model = new HashMap<String, Object>();
    model.put("fmt", new MessageFormatDirective(translations));
    model.put("subject", subjectDirective);
    model.put("img", inlineImages);
    model.put("instance", reportInstance);
    model.put("timezone", timeZone.getID());
    model.put("points", pointStatistics);
    model.put("inline", inlinePrefix == null ? "" : "cid:");
    model.put("devices", devices);
    model.put("mapped", pointMap);
    model.put("ALPHANUMERIC", DataTypes.ALPHANUMERIC);
    model.put("BINARY", DataTypes.BINARY);
    model.put("MULTISTATE", DataTypes.MULTISTATE);
    model.put("NUMERIC", DataTypes.NUMERIC);
    model.put("IMAGE", DataTypes.IMAGE);
    // Create the individual point charts
    for (PointStatistics pointStat : pointStatistics) {
        PointTimeSeriesCollection ptsc = new PointTimeSeriesCollection(timeZone);
        if (pointStat.getNumericTimeSeries() != null)
            ptsc.addNumericTimeSeries(pointStat.getNumericTimeSeries());
        else if (pointStat.getDiscreteTimeSeries() != null)
            ptsc.addDiscreteTimeSeries(pointStat.getDiscreteTimeSeries());
        if (ptsc.hasData()) {
            if (inlinePrefix != null)
                model.put("chartName", inlinePrefix + pointStat.getChartName());
            pointStat.setImageData(ImageChartUtils.getChartData(ptsc, POINT_IMAGE_WIDTH, POINT_IMAGE_HEIGHT, reportInstance.getReportStartTime(), reportInstance.getReportEndTime()));
        }
        // in the report I'll add it here while we are already iterating over the points that are included in the report
        if (pointStat.getDataType() == DataTypes.IMAGE) {
            ValueChangeCounter pointStatisticsGenerator = (ValueChangeCounter) pointStat.getStats();
            ImageValue img = (ImageValue) (pointStatisticsGenerator.getLastValue());
            if (img != null) {
                try {
                    pointStat.setImageData(img.getImageData());
                    if (inlinePrefix != null)
                        model.put("chartName", inlinePrefix + pointStat.getChartName());
                    else {
                        // serve up the image using the reportImageChart servlet instead of the imageValueServlet that is used on flipbook page
                        // The path comes from the servlet path definition in web.xml.
                        model.put("chartName", IMAGE_SERVLET + pointStat.getChartName());
                    }
                } catch (IOException e) {
                    LOG.error("failed to retrieve image data", e);
                }
            }
        }
    }
    // consolidated chart
    PointTimeSeriesCollection ptsc = handler.getPointTimeSeriesCollection();
    if (ptsc.hasData()) {
        if (inlinePrefix != null)
            model.put("chartName", inlinePrefix + IMAGE_CONTENT_ID);
        else {
            chartName = "r" + reportInstance.getId() + ".png";
            // The path comes from the servlet path definition in web.xml.
            model.put("chartName", IMAGE_SERVLET + chartName);
        }
        imageData = ImageChartUtils.getChartData(ptsc, true, IMAGE_WIDTH, IMAGE_HEIGHT, reportInstance.getReportStartTime(), reportInstance.getReportEndTime());
    }
    List<EventInstance> events = null;
    if (reportInstance.getIncludeEvents() != ReportVO.EVENTS_NONE) {
        events = reportDao.getReportInstanceEvents(reportInstance.getId());
        model.put("includeEvents", true);
        model.put("events", events);
    } else
        model.put("includeEvents", false);
    List<ReportUserComment> comments = null;
    if (reportInstance.isIncludeUserComments()) {
        comments = reportDao.getReportInstanceUserComments(reportInstance.getId());
        // Only provide the list of point comments to the report. The event comments have already be correlated
        // into the events list.
        List<ReportUserComment> pointComments = new ArrayList<ReportUserComment>();
        for (ReportUserComment c : comments) {
            if (c.getCommentType() == UserCommentVO.TYPE_POINT)
                pointComments.add(c);
        }
        model.put("includeUserComments", true);
        model.put("userComments", pointComments);
    } else
        model.put("includeUserComments", false);
    // Create the template.
    Template ftl;
    StringWriter writer = new StringWriter();
    FileReader reader = null;
    try {
        File templateFile = ReportCommon.instance.getTemplateFile(reportInstance.getTemplateFile());
        reader = new FileReader(templateFile);
        ftl = new Template(reportInstance.getName(), reader, Common.freemarkerConfiguration);
        ftl.process(model, writer);
    } catch (FileNotFoundException e) {
        LOG.error("Unable to find report template file: " + reportInstance.getName());
    } catch (IOException e) {
        // Couldn't load the template?
        throw new ShouldNeverHappenException(e);
    } catch (Exception e) {
        // Error processing the FTL?
        throw new ShouldNeverHappenException(e);
    } finally {
        if (reader != null)
            try {
                reader.close();
            } catch (IOException e) {
                LOG.error("Error closing template file reader: " + e.getMessage(), e);
            }
    }
    // Save the content
    html = writer.toString();
    subject = subjectDirective.getSubject();
    inlineImageList = inlineImages.getImageList();
    // Save the export file (if any)
    exportFile = handler.exportFile;
    if (createExportFile && events != null) {
        try {
            eventFile = File.createTempFile("tempEventCSV", ".csv");
            new EventCsvStreamer(new PrintWriter(new FileWriter(eventFile)), events, translations);
        } catch (IOException e) {
            LOG.error("Failed to create temp event file", e);
        }
    }
    if (createExportFile && comments != null) {
        try {
            commentFile = File.createTempFile("tempCommentCSV", ".csv");
            new UserCommentCsvStreamer(new PrintWriter(new FileWriter(commentFile)), comments, translations);
        } catch (IOException e) {
            LOG.error("Failed to create temp comment file", e);
        }
    }
}

Example 9 with ReportInstance

use of com.serotonin.m2m2.reports.vo.ReportInstance in project ma-modules-public by infiniteautomation.

the class ReportChartHandler method handleRequest.

@Override
public View handleRequest(HttpServletRequest request, HttpServletResponse response, Map<String, Object> model) throws Exception {
    int instanceId = Integer.parseInt(request.getParameter("instanceId"));
    ReportInstance instance = ReportDao.instance.getReportInstance(instanceId);
    User user = Common.getUser(request);
    ReportCommon.ensureReportInstancePermission(user, instance);
    ReportChartCreator creator = new ReportChartCreator(ControllerUtils.getTranslations(request), user.getTimeZoneInstance());
    creator.createContent(request.getServerName(), request.getLocalPort(), instance, ReportDao.instance, null, false);
    Map<String, byte[]> imageData = new HashMap<String, byte[]>();
    imageData.put(creator.getChartName(), creator.getImageData());
    for (ReportChartCreator.PointStatistics pointStatistics : creator.getPointStatistics()) imageData.put(pointStatistics.getChartName(), pointStatistics.getImageData());
    user.setAttribute(ReportChartServlet.IMAGE_DATA_KEY, imageData);
    return new ReportChartView(creator.getHtml());
}

Example 10 with ReportInstance

use of com.serotonin.m2m2.reports.vo.ReportInstance in project ma-modules-public by infiniteautomation.

the class ReportWorkItem method execute.

@Override
public void execute() {
    try {
        ReportLicenseChecker.checkLicense();
    } catch (LicenseViolatedException e) {
        LOG.error("Your core license doesn't permit you to use the reports module.");
        reportInstance.setReportStartTime(Common.timer.currentTimeMillis());
        reportInstance.setReportEndTime(Common.timer.currentTimeMillis());
        reportInstance.setRecordCount(-1);
        reportDao.saveReportInstance(reportInstance);
        return;
    }
    LOG.debug("Running report with id " + reportConfig.getId() + ", instance id " + reportInstance.getId());
    reportInstance.setRunStartTime(System.currentTimeMillis());
    reportDao.saveReportInstance(reportInstance);
    Translations translations = Common.getTranslations();
    // Create a list of DataPointVOs to which the user has permission.
    DataPointDao dataPointDao = DataPointDao.instance;
    List<ReportDao.PointInfo> points = new ArrayList<ReportDao.PointInfo>(reportConfig.getPoints().size());
    for (ReportPointVO reportPoint : reportConfig.getPoints()) {
        DataPointVO point = dataPointDao.getDataPoint(reportPoint.getPointId());
        if (point != null && Permissions.hasDataPointReadPermission(user, point)) {
            String colour = null;
            try {
                if (!StringUtils.isBlank(reportPoint.getColour()))
                    colour = ColorUtils.toHexString(reportPoint.getColour()).substring(1);
            } catch (InvalidArgumentException e) {
            // Should never happen since the colour would have been validated on save, so just let it go
            // as null.
            }
            points.add(new ReportDao.PointInfo(point, colour, reportPoint.getWeight(), reportPoint.isConsolidatedChart(), reportPoint.getPlotType()));
        }
    }
    int recordCount = 0;
    try {
        if (!points.isEmpty()) {
            if (Common.databaseProxy.getNoSQLProxy() == null)
                recordCount = reportDao.runReportSQL(reportInstance, points);
            else
                recordCount = reportDao.runReportNoSQL(reportInstance, points);
        }
    } catch (RuntimeException e) {
        recordCount = -1;
        throw e;
    } catch (Throwable e) {
        recordCount = -1;
        throw new RuntimeException("Report instance failed", e);
    } finally {
        reportInstance.setRunEndTime(System.currentTimeMillis());
        reportInstance.setRecordCount(recordCount);
        reportDao.saveReportInstance(reportInstance);
    }
    if (reportConfig.isEmail()) {
        String inlinePrefix = "R" + System.currentTimeMillis() + "-" + reportInstance.getId() + "-";
        // TODO should we create different instances of the email based upon language and timezone?
        // We are creating an email from the result. Create the content.
        final ReportChartCreator creator = new ReportChartCreator(translations, TimeZone.getDefault());
        creator.createContent(host, port, reportInstance, reportDao, inlinePrefix, reportConfig.isIncludeData());
        // Create the to list
        Set<String> addresses = MailingListDao.instance.getRecipientAddresses(reportConfig.getRecipients(), new DateTime(reportInstance.getReportStartTime()));
        String[] toAddrs = addresses.toArray(new String[0]);
        // Create the email content object.
        EmailContent emailContent = new EmailContent(null, creator.getHtml(), Common.UTF8);
        // Add the consolidated chart
        if (creator.getImageData() != null)
            emailContent.addInline(new EmailInline.ByteArrayInline(inlinePrefix + ReportChartCreator.IMAGE_CONTENT_ID, creator.getImageData(), ImageChartUtils.getContentType()));
        // Add the point charts
        for (PointStatistics pointStatistics : creator.getPointStatistics()) {
            if (pointStatistics.getImageData() != null)
                emailContent.addInline(new EmailInline.ByteArrayInline(inlinePrefix + pointStatistics.getChartName(), pointStatistics.getImageData(), ImageChartUtils.getContentType()));
        }
        // Add optional images used by the template.
        for (String s : creator.getInlineImageList()) addImage(emailContent, s);
        // Check if we need to attach the data.
        if (reportConfig.isIncludeData()) {
            addFileAttachment(emailContent, reportInstance.getName() + ".csv", creator.getExportFile());
            addFileAttachment(emailContent, reportInstance.getName() + "Events.csv", creator.getEventFile());
            addFileAttachment(emailContent, reportInstance.getName() + "Comments.csv", creator.getCommentFile());
        }
        PostEmailRunnable[] postEmail = null;
        if (reportConfig.isIncludeData()) {
            // See that the temp file(s) gets deleted after the email is sent.
            PostEmailRunnable deleteTempFile = new PostEmailRunnable() {

                @Override
                public void run() {
                    for (File file : filesToDelete) {
                        if (!file.delete())
                            LOG.warn("Temp file " + file.getPath() + " not deleted");
                    }
                }
            };
            postEmail = new PostEmailRunnable[] { deleteTempFile };
        }
        try {
            TranslatableMessage lm = new TranslatableMessage("ftl.scheduledReport", reportConfig.getName());
            String subject = creator.getSubject();
            if (subject == null)
                subject = lm.translate(translations);
            EmailWorkItem.queueEmail(toAddrs, subject, emailContent, postEmail);
        } catch (AddressException e) {
            LOG.error(e);
        }
        if (reportConfig.isSchedule()) {
            // Delete the report instance.
            reportDao.deleteReportInstance(reportInstance.getId(), user.getId());
        }
    }
    LOG.debug("Finished running report with id " + reportConfig.getId() + ", instance id " + reportInstance.getId());
}