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