bundles/org.simantics.history/src/org/simantics/history/csv/CSVFormatter.java

   1 /*******************************************************************************
   2  * Copyright (c) 2011 Association for Decentralized Information Management in
   3  * Industry THTH ry.
   4  * All rights reserved. This program and the accompanying materials
   5  * are made available under the terms of the Eclipse Public License v1.0
   6  * which accompanies this distribution, and is available at
   7  * http://www.eclipse.org/legal/epl-v10.html
   8  *
   9  * Contributors:
  10  *     VTT Technical Research Centre of Finland - initial API and implementation
  11  *******************************************************************************/
  12 package org.simantics.history.csv;
  13
  14 import java.io.IOException;
  15 import java.math.BigDecimal;
  16 import java.text.DecimalFormat;
  17 import java.text.DecimalFormatSymbols;
  18 import java.text.Format;
  19 import java.util.ArrayList;
  20 import java.util.Collections;
  21 import java.util.List;
  22 import java.util.Locale;
  23
  24 import org.simantics.databoard.accessor.StreamAccessor;
  25 import org.simantics.databoard.accessor.error.AccessorException;
  26 import org.simantics.history.HistoryException;
  27 import org.simantics.history.HistoryManager;
  28 import org.simantics.history.util.HistoryExportUtil;
  29 import org.simantics.history.util.ProgressMonitor;
  30 import org.simantics.history.util.StreamIterator;
  31 import org.simantics.history.util.ValueBand;
  32
  33 /**
  34  * CSV writer for history items.
  35  *
  36  * @author Toni Kalajainen
  37  * @author Tuukka Lehtonen
  38  */
  39 public class CSVFormatter {
  40
  41         /**
  42          * This is the tolerance used to decide whether or not the last data point of
  43          * the exported items is included in the exported material or not. If
  44          * <code>0 <= (t - t(lastDataPoint) < {@value #RESAMPLING_END_TIMESTAMP_INCLUSION_TOLERANCE}</code>
  45          * is true, then the last exported data point will be
  46          * <code>lastDataPoint</code>, with timestamp <code>t(lastDataPoint)</code> even
  47          * if <code>t > t(lastDataPoint)</code>.
  48          *
  49          * <p>
  50          * This works around problems where floating point inaccuracy causes a data
  51          * point to be left out from the the export when it would be fair for the user
  52          * to expect the data to be exported would contain a point with time stamp
  53          * <code>9.999999999999996</code> when sampling with time-step <code>1.0</code>
  54          * starting from time <code>0.0</code>.
  55          */
  56         private static final double RESAMPLING_END_TIMESTAMP_INCLUSION_TOLERANCE = 1e-13;
  57
  58         List<Item> items = new ArrayList<Item>();
  59         double from = -Double.MAX_VALUE;
  60         double end  =  Double.MAX_VALUE;
  61         double startTime = 0.0;
  62         double timeStep = 0.0;
  63         ColumnSeparator columnSeparator;
  64         DecimalSeparator decimalSeparator;
  65         boolean resample;
  66         String lineFeed;
  67         Locale locale;
  68
  69         Format timeFormat;
  70         Format floatFormat;
  71         Format numberFormat;
  72
  73         Formatter timeFormatter;
  74         Formatter floatFormatter;
  75         Formatter numberFormatter;
  76
  77         ExportInterpolation numberInterpolation = ExportInterpolation.LINEAR_INTERPOLATION;
  78
  79         public CSVFormatter() {
  80                 this.lineFeed = resolvePlatformLineFeed();
  81                 this.locale = Locale.getDefault(Locale.Category.FORMAT);
  82
  83                 DecimalFormat defaultFormat = new DecimalFormat();
  84                 defaultFormat.setGroupingUsed(false);
  85                 setTimeFormat(defaultFormat);
  86                 setFloatFormat(defaultFormat);
  87                 setNumberFormat(defaultFormat);
  88         }
  89
  90         /**
  91          * Add item to formatter
  92          *
  93          * @param historyItemId
  94          * @param label
  95          * @param variableReference
  96          * @param unit
  97          */
  98         public void addItem( HistoryManager history, String historyItemId, String label, String variableReference, String unit ) {
  99                 Item i = new Item();
 100                 i.history = history;
 101                 i.label = label!=null?label:"";
 102                 i.variableReference = variableReference!=null?variableReference:"";
 103                 i.variableReference = URIs.safeUnescape(i.variableReference);
 104                 i.historyItemId = historyItemId;
 105                 i.unit = unit;
 106                 if ( !items.contains(i) ) items.add( i );
 107         }
 108
 109         /**
 110          * Sort items by variableId, label1, label2
 111          */
 112         public void sort() {
 113                 Collections.sort(items);
 114         }
 115
 116         public void setTimeRange( double from, double end ) {
 117                 this.from = from;
 118                 this.end = end;
 119         }
 120
 121         public void setStartTime( double startTime ) {
 122                 this.startTime = startTime;
 123         }
 124
 125         public void setTimeStep( double timeStep ) {
 126                 this.timeStep = timeStep;
 127         }
 128
 129         void openHistory() throws HistoryException {
 130                 try {
 131                         for (Item item : items) item.open();
 132                 } catch (HistoryException e) {
 133                         for (Item item : items) item.close();
 134                         throw e;
 135                 }
 136         }
 137
 138         void closeHistory() {
 139                 for (Item item : items) item.close();
 140         }
 141
 142         /**
 143      * Reads visible data of all variables and formats as CSV lines (Comma
 144      * Separated Values). Line Feed is \n, variable separator is \t, and
 145      * decimal separator locale dependent.
 146      *
 147      * ReadData1 outputs separate time and value columns for each variable
 148      *
 149      * Variable1 Time | Variable1 Value | Variable2 Time | Variable2 Value
 150      * 0.0            | 1.0             | 0.1            | 23423.0
 151          *
 152          * @param monitor
 153          * @param sb
 154          * @throws HistoryException
 155          * @throws IOException
 156          */
 157         /*
 158         public void formulate1( ProgressMonitor monitor, Appendable sb ) throws HistoryException, IOException {
 159                 if (items.isEmpty()) return;
 160         boolean adaptComma = decimalSeparatorInLocale != decimalSeparator;
 161                 openHistory();
 162                 try {
 163                 // Prepare columns: First time
 164                 for (Item item : items)
 165                 {
 166                 if (monitor.isCanceled())
 167                     return;
 168                         if (item.stream.isEmpty()) continue;
 169                         Double firstTime = (Double) item.stream.getFirstTime( Bindings.DOUBLE );
 170                         if (from <= firstTime) {
 171                                 item.time = firstTime;
 172                         } else {
 173                                 item.time = (Double) item.stream.getFloorTime(Bindings.DOUBLE, from);
 174                         }
 175                 }
 176
 177                 // Write Headers
 178                 for (Item i : items)
 179                 {
 180                     if (monitor.isCanceled())
 181                         return;
 182                     boolean lastColumn = i == items.get( items.size()-1 );
 183                     sb.append(i.label + " Time");
 184                     sb.append( columnSeparator );
 185                     sb.append(i.label + " Value");
 186                     sb.append(lastColumn ? lineFeed : columnSeparator );
 187                 }
 188
 189                 // Iterate until endTime is met for all variables
 190                 int readyColumns;
 191                 do {
 192                 if (monitor.isCanceled())
 193                     return;
 194
 195                 readyColumns = 0;
 196                     for (Item i : items)
 197                     {
 198                             boolean lastColumn = i == items.get( items.size()-1 );
 199
 200                         if (i.time == null || i.time > end) {
 201                             readyColumns++;
 202                             sb.append( lastColumn ? columnSeparator+lineFeed : columnSeparator+columnSeparator);
 203                             continue;
 204                         }
 205
 206                         sb.append("");
 207
 208                         // Write time
 209                         String timeStr = format.format( i.time );
 210                         if ( adaptComma ) timeStr = timeStr.replace(decimalSeparatorInLocale, decimalSeparator);
 211                         sb.append( timeStr );
 212                         sb.append( columnSeparator );
 213
 214                         // Write value
 215                         i.value = i.stream.getValue(Bindings.DOUBLE, i.time);
 216                         if (i.value instanceof Number) {
 217                                 String str = format.format( i.value );
 218                                 if ( adaptComma ) str = str.replace(decimalSeparatorInLocale, decimalSeparator);
 219                             sb.append( str );
 220                         } else if (i.value instanceof Boolean) {
 221                                 sb.append( (Boolean)i.value ? "1": "0");
 222                         } else {
 223                             sb.append( i.value.toString() );
 224                         }
 225                         sb.append(lastColumn ? lineFeed : columnSeparator);
 226
 227                         // Get next time
 228                         i.time = (Double) i.stream.getHigherTime(Bindings.DOUBLE, i.time);
 229                     }
 230
 231                 } while (readyColumns < items.size());
 232                 } finally {
 233                         closeHistory();
 234                 }
 235         }*/
 236
 237         /**
 238      * Reads visible data of all variables and formats as CSV lines (Comma
 239      * Separated Values). Line Feed is \n, variable separator is \t, and
 240      * decimal separator locale dependent.
 241      *
 242      * ReadData2 outputs one shared time and one value column for each variable
 243      *
 244      * Time | Variable1 Label | Variable3 Label
 245      *      | Variable1 Id    | Variable3 Id
 246      *      | Variable1 Unit  | Variable3 Unit
 247      * 0.0  | 1.0             | 0.1
 248      *
 249      * @param monitor
 250      * @param sb
 251      * @throws HistoryException
 252          * @throws IOException
 253      */
 254     public void formulate2( ProgressMonitor monitor, Appendable sb ) throws HistoryException, IOException
 255     {
 256         if ( items.isEmpty() ) return;
 257
 258         timeFormatter = evaluateFormatter(timeFormat, decimalSeparator);
 259         floatFormatter = evaluateFormatter(floatFormat, decimalSeparator);
 260         numberFormatter = evaluateFormatter(numberFormat, decimalSeparator);
 261
 262         openHistory();
 263         try {
 264                 // What is the time range of all items combined
 265                 double allFrom = Double.MAX_VALUE;
 266                 double allEnd = -Double.MAX_VALUE;
 267                 for (Item i : items) {
 268                         if (i.iter.isEmpty()) continue;
 269                         allFrom = Math.min(allFrom, i.iter.getFirstTime());
 270                         allEnd = Math.max(allEnd, i.iter.getLastTime());
 271                 }
 272
 273                 // Write Headers
 274                 for (int hl = 0; hl < 3; ++hl) {
 275                         switch(hl) {
 276                         case 0: sb.append("Time"); break;
 277                         case 1: sb.append("----"); break;
 278                         case 2: sb.append("Unit"); break;
 279                         }
 280                     sb.append( columnSeparator.preference );
 281                     for (Item i : items)
 282                     {
 283                            boolean lastColumn = i == items.get( items.size()-1 );
 284                        switch (hl) {
 285                            case 0:
 286                                sb.append(i.label != null ? i.label : "");
 287                                break;
 288                            case 1:
 289                                sb.append(i.variableReference != null ? i.variableReference : "");
 290                                break;
 291                            case 2:
 292                                sb.append(i.unit==null?"no unit":i.unit);
 293                                break;
 294                        }
 295                        if (!lastColumn) sb.append( columnSeparator.preference );
 296                     }
 297                     sb.append( lineFeed );
 298                 }
 299
 300                 // Prepare time
 301                 boolean hasAnyValues = allFrom != Double.MAX_VALUE && allEnd != -Double.MAX_VALUE;
 302
 303                 // Make intersection of actual data range (allFrom, allEnd) and requested data (from, end)
 304                 double _from = Double.MAX_VALUE, _end = -Double.MAX_VALUE;
 305                 if (hasAnyValues) {
 306                         _from = Math.max(allFrom, from);
 307                         _end = Math.min(allEnd, end);
 308                 }
 309
 310                 if (!hasAnyValues) return;
 311
 312                         // Iterate until endTime is met for all variables
 313                         double time = _from;
 314
 315                         if(!resample) {
 316
 317                                 // If resample is false then all samples are reported as is. The code achieves this by setting startTime to _from and timeStep to 0.0
 318                                 time = _from;
 319                                 timeStep = 0.0;
 320
 321                         } else {
 322
 323                                 // time = startTime + n*timeStep
 324
 325                                 // Sampling based on given startTime and timeStep
 326                                 if(timeStep > 0) {
 327
 328                                         // Find the first sample time that contains data if startTime < _from
 329                                         double n = Math.max(0, Math.ceil((_from-startTime) / timeStep));
 330                                         time = startTime + n*timeStep;
 331
 332                                 } else {
 333
 334                                         // Start sampling from startTime but make sure that it is not less than _from
 335                                         if(startTime > _from) time = startTime;
 336
 337                                 }
 338
 339
 340                         }
 341
 342                         // Must convert double times to String when initializing BigDecimal.
 343                         // Otherwise BigDecimal will pick up inaccuracies from beyond 15 precise digits
 344                         // thus making a mess of the time step calculations.
 345
 346                         BigDecimal bigTime = new BigDecimal(String.valueOf(time));
 347                         BigDecimal bigTimeStep = new BigDecimal(String.valueOf(timeStep));
 348
 349                         // Loop kill-switch for the case where timeStep > 0
 350                         boolean breakAfterNextWrite = false;
 351
 352 //                      System.out.println("time:     " + time);
 353 //                      System.out.println("timeStep: " + timeStep);
 354 //                      System.out.println("_end:     " + Double.toString(_end));
 355
 356                         for (Item i : items) i.iter.gotoTime(time);
 357                         do {
 358                                 if ( monitor!=null && monitor.isCanceled() ) return;
 359
 360                                 // Write time
 361                                 String timeStr = timeFormatter.format( time );
 362                                 //System.out.println("SAMPLING TIME: " + time);
 363                                 sb.append( timeStr );
 364
 365                                 // Write values
 366                                 for (Item i : items)
 367                                 {
 368                                         sb.append( columnSeparator.preference );
 369
 370                                         // Write value
 371                                         if ( i.iter.hasValidValue() ) {
 372                                                 Object value = i.iter.getValueBand().getValue();
 373                                                 if (value instanceof Number) {
 374                                                         if (value instanceof Float || value instanceof Double) {
 375                                                                 switch (numberInterpolation) {
 376                                                                 case PREVIOUS_SAMPLE:
 377                                                                         sb.append( formatNumber(value) );
 378                                                                         break;
 379
 380                                                                 case LINEAR_INTERPOLATION:
 381                                                                         if (time != i.iter.getValueBand().getTimeDouble() && i.iter.hasNext()) {
 382
 383                                                                                 // Interpolate
 384                                                                                 int currentIndex = i.iter.getIndex();
 385                                                                                 ValueBand band = i.iter.getValueBand();
 386                                                                                 //double t1 = band.getTimeDouble();
 387                                                                                 Number v1 = (Number) value;
 388                                                                                 double t12 = band.getEndTimeDouble();
 389                                                                                 i.iter.next();
 390                                                                                 double t2 = i.iter.getValueBand().getTimeDouble();
 391                                                                                 Number v2 = (Number) i.iter.getValueBand().getValue();
 392                                                                                 i.iter.gotoIndex(currentIndex);
 393
 394                                                                                 double vs = v1.doubleValue();
 395                                                                                 if(time > t12)
 396                                                                                         vs = HistoryExportUtil.biglerp(t12, v1.doubleValue(), t2, v2.doubleValue(), time);
 397
 398                                                                                 sb.append( formatDouble(vs) );
 399                                                                         } else {
 400                                                                                 // Exact timestamp match, or last sample.
 401                                                                                 // Don't interpolate nor extrapolate.
 402                                                                                 sb.append( formatNumber(value) );
 403                                                                         }
 404                                                                         break;
 405                                                                 default:
 406                                                                         throw new UnsupportedOperationException("Unsupported interpolation: " + numberInterpolation);
 407                                                                 }
 408                                                         } else {
 409                                                                 sb.append( value.toString() );
 410                                                         }
 411                                                 } else if (value instanceof Boolean) {
 412                                                         sb.append( (Boolean)value ? "1": "0");
 413                                                 } else {
 414                                                         sb.append( value.toString() );
 415                                                 }
 416                                         }
 417                                 }
 418
 419                                 sb.append( lineFeed );
 420
 421                                 if (breakAfterNextWrite)
 422                                         break;
 423
 424                                 // Read next values, and the following times
 425                                 if ( timeStep>0.0 ) {
 426                                         bigTime = bigTime.add(bigTimeStep);
 427                                         time = bigTime.doubleValue();
 428
 429                                         // gitlab #529: prevent last data point from getting dropped
 430                                         // due to small imprecisions in re-sampling mode.
 431                                         double diff = time - _end;
 432                                         if (diff > 0 && diff <= RESAMPLING_END_TIMESTAMP_INCLUSION_TOLERANCE) {
 433                                                 time = _end;
 434                                                 breakAfterNextWrite = true;
 435                                                 // Take floating point inaccuracy into account when re-sampling
 436                                                 // to prevent the last data point from being left out if there
 437                                                 // is small-enough imprecision in the last data point time stamp
 438                                                 // to be considered negligible compared to expected stepped time.
 439                                         }
 440
 441                     } else {
 442                         // Get smallest end time that is larger than current time
 443                         Double nextTime = null;
 444 //                      System.out.println("time = "+time);
 445                         for (Item i : items) {
 446                                 Double itemNextTime = i.iter.getNextTime( time );
 447 //                              System.err.println("  "+i.label+" nextTime="+itemNextTime);
 448                                 if ( itemNextTime == null ) continue;
 449                                 if ( itemNextTime < time ) continue;
 450                                 if ( nextTime == null || ( nextTime > itemNextTime && !itemNextTime.equals( time ) ) ) nextTime = itemNextTime;
 451                         }
 452                         if ( nextTime == null || nextTime.equals( time ) ) break;
 453                         time = nextTime;
 454                     }
 455
 456                     boolean hasMore = false;
 457
 458                 for (Item i : items) {
 459                         i.iter.proceedToTime(time);
 460                         if(contains(i, time)) hasMore = true;
 461                 }
 462
 463                 //System.out.println("hasMore @ " + time + " (" + bigTime + ") = " + hasMore);
 464                 if(!hasMore) break;
 465
 466                 } while (time<=_end);
 467         } finally {
 468                 closeHistory();
 469         }
 470     }
 471
 472     private boolean contains(Item item, double time) {
 473         double start = item.iter.getStartTime();
 474         double end = item.iter.getEndTime();
 475         // A special case, where start == end => accept
 476         if(time == start) return true;
 477         else if(time < start) return false;
 478         else if(time >= end) return false;
 479         else return true;
 480     }
 481
 482         private CharSequence formatNumber(Object value) {
 483                 return value instanceof Float
 484                                 ? floatFormatter.format( value )
 485                                 : numberFormatter.format( value );
 486         }
 487
 488         private CharSequence formatDouble(double value) {
 489                 return numberFormatter.format( value );
 490         }
 491
 492         public void setDecimalSeparator(DecimalSeparator separator) {
 493                 this.decimalSeparator = separator;
 494         }
 495
 496         public void setColumnSeparator(ColumnSeparator separator) {
 497                 this.columnSeparator = separator;
 498         }
 499
 500         public void setResample(boolean resample) {
 501                 this.resample = resample;
 502         }
 503
 504         public void setLineFeed( String lf ) {
 505                 this.lineFeed = lf;
 506         }
 507
 508         public void setTimeFormat(Format format) {
 509                 this.timeFormat = format;
 510         }
 511
 512         public void setFloatFormat(Format format) {
 513                 this.floatFormat = format;
 514         }
 515
 516         public void setNumberFormat(Format format) {
 517                 this.numberFormat = format;
 518         }
 519
 520         public void setLocale(Locale locale) {
 521                 this.locale = locale;
 522         }
 523
 524         public void setNumberInterpolation(ExportInterpolation interpolation) {
 525                 this.numberInterpolation = interpolation;
 526         }
 527
 528         private static String resolvePlatformLineFeed() {
 529                 String osName = System.getProperty("os.name", "");
 530                 osName = osName.toLowerCase();
 531                 if (osName.contains("windows"))
 532                         return "\r\n";
 533                 return "\n";
 534         }
 535
 536     private class Item implements Comparable<Item> {
 537         // Static data
 538         String label;                                   // Label
 539         String variableReference;               // Label
 540         HistoryManager history;         // History source for this item
 541         String historyItemId;
 542         String unit;
 543
 544         // State data
 545         StreamAccessor accessor;                        // Stream accessor
 546         StreamIterator iter;
 547
 548         public void open() throws HistoryException {
 549                 accessor = history.openStream(historyItemId, "r");
 550                 iter = new StreamIterator( accessor );
 551         }
 552
 553         public void close() {
 554                 if (accessor!=null) {
 555                         try {
 556                                         accessor.close();
 557                                 } catch (AccessorException e) {
 558                                 }
 559                 }
 560                 accessor = null;
 561                 iter = null;
 562         }
 563
 564                 @Override
 565                 public int compareTo(Item o) {
 566                         int i;
 567                         i = label.compareTo(o.label);
 568                         if (i!=0) return i;
 569                         i = variableReference.compareTo(o.variableReference);
 570                         if (i!=0) return i;
 571                         i = historyItemId.compareTo(o.historyItemId);
 572                         if (i!=0) return i;
 573                         return 0;
 574                 }
 575
 576                 @Override
 577                 public int hashCode() {
 578                         int code = 0x2304;
 579                         code = 13*code + variableReference.hashCode();
 580                         code = 13*code + label.hashCode();
 581                         code = 13*code + historyItemId.hashCode();
 582                         code = 13*code + history.hashCode();
 583                         return code;
 584                 }
 585
 586                 @Override
 587                 public boolean equals(Object obj) {
 588                         if ( obj == null ) return false;
 589                         if ( obj instanceof Item == false ) return false;
 590                         Item other = (Item) obj;
 591                         if ( !other.label.equals(label) ) return false;
 592                         if ( !other.variableReference.equals(variableReference) ) return false;
 593                         if ( !other.history.equals(history) ) return false;
 594                         if ( !other.historyItemId.equals(historyItemId) ) return false;
 595                         return true;
 596                 }
 597
 598     }
 599
 600     static interface Formatter {
 601         String format(Object number);
 602     }
 603
 604     static class NopFormatter implements Formatter {
 605         private final Format format;
 606         public NopFormatter(Format format) {
 607             this.format = format;
 608         }
 609         public String format(Object number) {
 610             return format.format(number);
 611         }
 612     }
 613
 614     static class ReplacingFormatter implements Formatter {
 615         private final Format format;
 616         private final char from;
 617         private final char to;
 618         public ReplacingFormatter(Format format, char from, char to) {
 619             this.format = format;
 620             this.from = from;
 621             this.to = to;
 622         }
 623         public String format(Object number) {
 624             return format.format(number).replace(from, to);
 625         }
 626     }
 627
 628     private Formatter evaluateFormatter(Format format, DecimalSeparator target) {
 629         // Probe decimal separator
 630         String onePointTwo = format.format(1.2);
 631         //System.out.println("formatted zeroPointOne: " + onePointTwo);
 632
 633         DecimalSeparator formatSeparator;
 634         if (onePointTwo.indexOf('.') != -1) {
 635             formatSeparator = DecimalSeparator.DOT;
 636         } else if (onePointTwo.indexOf(',') != -1) {
 637             formatSeparator = DecimalSeparator.COMMA;
 638         } else {
 639             DecimalFormatSymbols symbols = DecimalFormatSymbols.getInstance(locale);
 640             formatSeparator = DecimalSeparator.fromChar(symbols.getDecimalSeparator());
 641         }
 642
 643         switch (formatSeparator) {
 644         case COMMA:
 645             switch (target) {
 646             case COMMA:
 647                 return new NopFormatter(format);
 648             case DOT:
 649                 return new ReplacingFormatter(format, ',', '.');
 650             }
 651         case DOT:
 652             switch (target) {
 653             case COMMA:
 654                 return new ReplacingFormatter(format, '.', ',');
 655             case DOT:
 656                 return new NopFormatter(format);
 657             }
 658         }
 659         return new NopFormatter(format);
 660     }
 661
 662 }