001package ca.bc.webarts.tools; 002 003import java.util.Vector; 004import java.util.Arrays; 005import java.lang.Float; 006import java.lang.Thread.*; 007import java.text.DecimalFormat; 008import java.util.concurrent.ArrayBlockingQueue; 009import java.util.concurrent.TimeUnit; 010import java.io.File; 011 012import javax.xml.parsers.DocumentBuilderFactory; 013import javax.xml.parsers.DocumentBuilder; 014import org.w3c.dom.Document; 015import org.w3c.dom.NodeList; 016// import org.w3c.dom.Node; 017import org.w3c.dom.Element; 018 019 020//import jxl.*; 021//import jxl.write.*; 022 023import ca.bc.webarts.tools.SqlQuery; 024import ca.bc.webarts.widgets.Util; 025 026/** 027 * Class (bean) to override the generic SqlQuery class specifically for accessing the WorkTracker. 028 * <ul><li>The user can instantiate a WorkTrackerQuery and then call its query methods API 029 * <br /><b>OR</b /></li> 030 * <li>you can use this class from the commandline</li></ul>Commandline Syntax is as Follows:<br /> 031 * <pre>SYNTAX: 032 * ca.bc.webarts.tools.WorkTrackerQuery [command] [commandOptions] 033 * 034 * Available Commands: 035 * --> sql {an sql statement} 036 * --> sqlFile sqlFile 037 * --> exportworkouts userId 038 * Example Usage: 039 * java ca.bc.webarts.tools.WorkTrackerQuery exportworkouts tgutwin 040 * 041 * </pre> 042 **/ 043public class WorkTrackerQuery extends SqlQuery 044{ 045 private String className_ = "WorkTrackerQuery"; 046 047 /** Prod Or Test DB **/ 048 private boolean useProduction = true; 049 050 /** The Threaded Multi-Process Process Manager. 051 * It manages with a job queue, the processes that get forked to run riskA. 052 **/ 053 //RunManager runManager_ = new RunManager(); 054 055 private boolean wakeUp_ = false; 056 057 /** This is the filename that the main method uses to save results to **/ 058 private String resultsFileName_ = "wtResults.csv"; 059 060 /** Gets a Log instance in case any calling classes are using it. */ 061 private static Log log_ = Log.getInstance(); 062 063 /* Bean variables */ 064 private String userId = ""; 065 private String workoutDate = ""; 066 private String exerciseType = ""; 067 private String exerciseSubType = ""; 068 private boolean indoorOutdoor = true; // indoor=true outdoor=false 069 private String equipment = ""; 070 private double distance_km; 071 private int calories ; 072 private int duration_MIN; 073 private double elevation_M; 074 private String programType = ""; 075 private int heartRate_AVG=0; 076 private int heartRate_MAX=0; 077 private int heartRate_MIN=0; 078 079 080 /** Constructor for the SqlQuery object */ 081 public WorkTrackerQuery() 082 { 083 //super(); 084 setUseDerbyDB(); 085 useProduction = true; 086 testDbConnectString = getDbConnectString(); 087 }// -- Constructor 088 089 090 /** Constructor for the SqlQuery object */ 091 public WorkTrackerQuery(ca.bc.webarts.tools.Log log) 092 { 093 super(); 094 log_ = log; 095 setUseDerbyDB(); 096 }// -- Constructor 097 098 099 /** 100 * Returns the value of programType. 101 */ 102 public String getProgramType() { 103 return programType; 104 } 105 106 107 /** 108 * Sets the value of programType. 109 * @param programType The value to assign programType. 110 */ 111 public void setProgramType(String programType) { 112 this.programType = programType; 113 } 114 115 116 /** 117 * Returns the value of elevation_M. 118 */ 119 public double getElevation_M() { 120 return elevation_M; 121 } 122 123 124 /** 125 * Sets the value of elevation_M. 126 * @param elevation_M The value to assign elevation_M. 127 */ 128 public void setElevation_M(double elevation_M) { 129 this.elevation_M = elevation_M; 130 } 131 132 133 /** 134 * Returns the value of duration_MIN. 135 */ 136 public int getDuration_MIN() { 137 return duration_MIN; 138 } 139 140 141 /** 142 * Sets the value of duration_SEC. 143 * @param duration_MIN The value to assign duration_MIN. 144 */ 145 public void setDuration_MIN(int duration_MIN) { 146 this.duration_MIN = duration_MIN; 147 } 148 149 150 /** 151 * Returns the value of heartRate_AVG. 152 */ 153 public int getHeartRate_AVG() { 154 return heartRate_AVG; 155 } 156 157 158 /** 159 * Sets the value of heartRate_AVG. 160 * @param heartRate_AVG The value to assign heartRate_AVG. 161 */ 162 public void setHeartRate_AVG(int heartRate_AVG) { 163 this.heartRate_AVG = heartRate_AVG; 164 } 165 166 167 /** 168 * Returns the value of heartRate_MIN. 169 */ 170 public int getHeartRate_MIN() { 171 return heartRate_MIN; 172 } 173 174 175 /** 176 * Sets the value of heartRate_MIN. 177 * @param heartRate_MIN The value to assign heartRate_MIN. 178 */ 179 public void setHeartRate_MIN(int heartRate_MIN) { 180 this.heartRate_MIN = heartRate_MIN; 181 } 182 183 184 /** 185 * Returns the value of heartRate_MAX. 186 */ 187 public int getHeartRate_MAX() { 188 return heartRate_MAX; 189 } 190 191 192 /** 193 * Sets the value of heartRate_MAX. 194 * @param heartRate_MAX The value to assign heartRate_MAX. 195 */ 196 public void setHeartRate_MAX(int heartRate_MAX) { 197 this.heartRate_MAX = heartRate_MAX; 198 } 199 200 201 /** 202 * Returns the value of calories. 203 */ 204 public int getCalories() { 205 return calories; 206 } 207 208 209 /** 210 * Sets the value of calories. 211 * @param calories The value to assign calories. 212 */ 213 public void setCalories(int calories) { 214 this.calories = calories; 215 } 216 217 218 /** 219 * Returns the value of distance_km. 220 */ 221 public double getDistance_km() { 222 return distance_km; 223 } 224 225 226 /** 227 * Sets the value of distance_km. 228 * @param distance_km The value to assign distance_km. 229 */ 230 public void setDistance_km(double distance_km) { 231 System.out.println("Setting distance_km:"+distance_km); 232 this.distance_km = distance_km; 233 } 234 235 236 /** 237 * Returns the value of equipment. 238 */ 239 public String getEquipment() { 240 return equipment; 241 } 242 243 244 /** 245 * Sets the value of equipment. 246 * @param equipment The value to assign equipment. 247 */ 248 public void setEquipment(String equipment) { 249 this.equipment = equipment; 250 } 251 252 253 /** 254 * Returns the value of exerciseType. 255 */ 256 public String getExerciseType() { 257 return exerciseType; 258 } 259 260 261 /** 262 * Sets the value of exerciseType. 263 * @param exerciseType The value to assign exerciseType. 264 */ 265 public void setExerciseType(String exerciseType) { 266 System.out.println("Setting exerciseType:"+exerciseType); 267 this.exerciseType = exerciseType; 268 } 269 270 271 /** 272 * Returns the value of exerciseSubType. 273 */ 274 public String getExerciseSubType() { 275 return exerciseSubType; 276 } 277 278 279 /** 280 * Sets the value of exerciseSubType. 281 * @param exerciseSubType The value to assign exerciseSubType. 282 */ 283 public void setExerciseSubType(String exerciseSubType) { 284 this.exerciseSubType = exerciseSubType; 285 } 286 287 288 /** 289 * Returns the value of indoorOutdoor. 290 */ 291 public boolean getIndoorOutdoor() { 292 return indoorOutdoor; 293 } 294 295 296 /** 297 * Sets the value of indoorOutdoor. 298 * @param indoorOutdoor The value to assign indoorOutdoor. 299 */ 300 public void setIndoorOutdoor(boolean indoorOutdoor) { 301 System.out.println("Setting indoorOutdoor:"+indoorOutdoor); 302 this.indoorOutdoor = indoorOutdoor; 303 } 304 305 306 /** 307 * Sets the value of indoorOutdoor. 308 * @param indoorOutdoor The value to assign indoorOutdoor. 309 */ 310 public void setIndoorOutdoor(String indoorOutdoor) { 311 System.out.println("Setting indoorOutdoor:"+indoorOutdoor); 312 this.indoorOutdoor = Boolean.parseBoolean(indoorOutdoor); 313 } 314 315 316 /** 317 * Returns the value of workoutDate. 318 */ 319 public String getWorkoutDate() { 320 return workoutDate; 321 } 322 323 324 /** 325 * Sets the value of workoutDate. 326 * @param workoutDate The value to assign workoutDate. 327 */ 328 public void setWorkoutDate(String workoutDate) { 329 System.out.println("Setting workoutDate:"+workoutDate); 330 this.workoutDate = workoutDate; 331 } 332 333 334 /** 335 * Returns the value of userId. 336 */ 337 public String getUserId() { 338 return userId; 339 } 340 341 342 /** 343 * Sets the value of userId. 344 * @param userId The value to assign userId. 345 */ 346 public void setUserId(String userId) { 347 this.userId = userId; 348 } 349 350 /** 351 * Gets the resultsFileName_ var. 352 * 353 * @return String - The value the class field 'resultsFileName_'. 354 * 355 **/ 356 public String getResultsFileName() 357 { 358 return resultsFileName_; 359 } // getDbUser Method 360 361 362 /** 363 * Sets the name to use for results files (if requested). 364 * 365 * @param resultsFileName String - filename 366 * 367 **/ 368 public void setResultsFileName(String resultsFileName) 369 { 370 this.resultsFileName_ = resultsFileName; 371 } 372 373 374 /** 375 * Get Method for class field 'dbUser'. 376 * 377 * @return String - The value the class field 'dbUser'. 378 * 379 **/ 380 public String getDbUser() 381 { 382 return getWtDbUser(); 383 } // getDbUser Method 384 385 386 /** 387 * Returns the DB Userid 388 * 389 * @return String - The FDP DB Userid 390 * 391 **/ 392 public String getWtDbUser() 393 { 394 return "wTracker"; 395 } 396 397 /** 398 * Get Method for class field 'dbPassword'. 399 * 400 * @return String - The value the class field 'dbPassword'. 401 * 402 **/ 403 public String getDbPassword() 404 { 405 return getWTDbPassword(); 406 } 407 408 409 /** 410 * Returns the DB password 411 * 412 * @return String - The FDP DB user password 413 * 414 **/ 415 public String getWTDbPassword() 416 { 417 return "wTracker"; 418 } // getDbPassword Method 419 420 421 /** 422 * Get Method for class field 'dbUser'. 423 * 424 * @return String - The value the class field 'dbUser'. 425 * 426 **/ 427 public String getDbIP() 428 { 429 return getWTDbIP(); 430 } // getDbUser Method 431 432 433 /** 434 * Get WT IP (host) for the db 435 * 436 * @return String - The value the FDP IP for the db. 437 * 438 **/ 439 public String getWTDbIP() 440 { 441 return (useProduction? 442 "localhost": 443 "warp4.webarts.bc.ca" 444 ) ; // should be 172.20.7.43 445 } // getDbUser Method 446 447 448 449 /** 450 * Get Method for class field 'dbName'. 451 * 452 * @return String - The value the class field 'dbUser'. 453 * 454 **/ 455 public String getDbName() 456 { 457 return getWTDbName(); 458 } // getDbName Method 459 460 461 /** 462 * Get FDP IP (host) dor the db 463 * 464 * @return String - The value the FDP IP for the db. 465 * 466 **/ 467 public String getWTDbName() 468 { 469 return (useProduction? 470 "wTracker": 471 "wTracker") ; 472 } // getWTDbName Method 473 474 /** 475 * Set true to use the Production WT DB. 476 * If false, all queries will hit the test DB. 477 * 478 * @param useProduction true or false to use or not to use PROD 479 **/ 480 public void setUseProduction(boolean useProduction) 481 { 482 this.useProduction = useProduction; 483 } // setUseProduction Method 484 485 486 /** 487 * Are we using the Prod or Test DB. 488 * 489 * @return boolean - true if the queries will use the Prod DB as opposed to the Test DB. 490 **/ 491 public boolean getUseProduction() 492 { 493 return useProduction; 494 } // getUseProduction Method 495 496 497 /** 498 * Test if we can connect. 499 * 500 * @return true if a connection is possible and succesful. 501 **/ 502 public boolean testConnection() 503 { 504 boolean retVal = canConnect(getUseProduction()); 505 506 return retVal; 507 } 508 509 public int getNextWorkoutRef(){ return getNextTableRef("workout");} 510 public int getNextUsersRef(){ return getNextTableRef("users");} 511 public int getNextUserWeightProfileRef(){ return getNextTableRef("userWeightProfile");} 512 public int getNextEquipmentRef(){ return getNextTableRef("equipment");} 513 public int getNextProgramTypeRef(){ return getNextTableRef("programType");} 514 public int getNextExerciseTypeRef(){ return getNextTableRef("exerciseType");} 515 public int getNextEquipmentTypeRef(){ return getNextTableRef("equipmentType");} 516 public int getNextUserGroupRef(){ return getNextTableRef("userGroup");} 517 518 public int getNextTableRef(String tableName) 519 { 520 int retVal = 1; 521 String [][] idArray = {{""}}; 522 String sql = "select ID from "+tableName+" order by ID desc \n"; 523 StringBuffer sb = query(sql,getUseProduction() ); 524 //System.out.println(sb.toString()); 525 idArray = sbToDataArray(sb); 526 if (idArray!=null && !idArray[0][0].equals("")) 527 retVal = Integer.parseInt(idArray[0][0]) +1; 528 return retVal; 529 } 530 531 532 public int getUserTableRef(String userid) 533 { 534 int retVal = 1; 535 String [][] idArray = {{""}}; 536 String sql = "select ID from users where users.userid='"+userid+"' \n"; 537 StringBuffer sb = query(sql,getUseProduction() ); 538 //System.out.println(sb.toString()); 539 idArray = sbToDataArray(sb); 540 if (idArray!=null && !idArray[0][0].equals("")) 541 retVal = Integer.parseInt(idArray[0][0]) ; 542 return retVal; 543 } 544 545 546 public int getEquipmentTableRef(int exerId) 547 { 548 int retVal = 0; 549 String [][] idArray = {{""}}; 550 String sql = "select ID from equipment where equipment.exerciseTypeID="+exerId+" \n"; 551 StringBuffer sb = query(sql,getUseProduction() ); 552 //System.out.println(sb.toString()); 553 debugOut=true; 554 idArray = sbToDataArray(sb); 555 debugOut=false; 556 if (idArray!=null && !idArray[0][0].equals("")) 557 retVal = Integer.parseInt(idArray[0][0]) ; 558 return retVal; 559 } 560 561 562 public int getEquipmentTableRef(String make, String model) 563 { 564 int retVal = 0; 565 String [][] idArray = {{""}}; 566 String sql = "select ID from equipment where equipment.make='"+make+"' AND equipment.model='"+model+"' \n"; 567 StringBuffer sb = query(sql,getUseProduction() ); 568 //System.out.println(sb.toString()); 569 debugOut=true; 570 idArray = sbToDataArray(sb); 571 debugOut=false; 572 if (idArray!=null && !idArray[0][0].equals("")) 573 retVal = Integer.parseInt(idArray[0][0]) ; 574 return retVal; 575 } 576 577 578 public int getExercisetypeTableRef(String type, String subType) 579 { 580 int retVal = 0; 581 String [][] idArray = {{""}}; 582 if(type!=null && !type.equals("") && 583 subType!=null && !subType.equals("")) 584 { 585 String sql = "select ID from exercisetype where exercisetype.type='"+type+"' AND exercisetype.sub_type='"+subType+"' \n"; 586 debugOut = true; 587 StringBuffer sb = query(sql,getUseProduction() ); 588 debugOut = false; 589 System.out.println(sb.toString()); 590 idArray = sbToDataArray(sb); 591 if (idArray!=null && !idArray[0][0].equals("")) 592 retVal = Integer.parseInt(idArray[0][0]) ; 593 } 594 595 return retVal; 596 } 597 598 599 public int getNumberOfExerciseTypes(String userid) 600 { 601 int retVal = 1; 602 int userRef = getUserTableRef(userid); 603 604 String [][] idArray = {{""}}; 605 String sql = "select distinct exercisetypeid from workout where userID="+userRef; 606 debugOut = false; 607 StringBuffer sb = query(sql,getUseProduction() ); 608 debugOut = false; 609 //System.out.println(sb.toString()); 610 idArray = sbToDataArray(sb); 611 retVal = idArray[0].length; 612 613 return retVal; 614 } 615 616 617 public String queryEquipMake(int equipmentId) 618 { 619 String retVal = ""; 620 621 String [][] idArray = {{""}}; 622 String sql = "select make from equipment where id="+equipmentId; 623 debugOut = false; 624 StringBuffer sb = query(sql,getUseProduction() ); 625 debugOut = false; 626 //System.out.println(sb.toString()); 627 idArray = sbToDataArray(sb); 628 retVal = idArray[0][0]; 629 630 return retVal; 631 } 632 633 634 public String queryEquipModel(int equipmentId) 635 { 636 String retVal = ""; 637 638 String [][] idArray = {{""}}; 639 String sql = "select model from equipment where id="+equipmentId; 640 debugOut = false; 641 StringBuffer sb = query(sql,getUseProduction() ); 642 debugOut = false; 643 //System.out.println(sb.toString()); 644 idArray = sbToDataArray(sb); 645 retVal = idArray[0][0]; 646 647 return retVal; 648 } 649 650 651 public String queryExerType(int exerId) 652 { 653 String retVal = ""; 654 655 String [][] idArray = {{""}}; 656 String sql = "select type from exercisetype where id="+exerId; 657 debugOut = false; 658 StringBuffer sb = query(sql,getUseProduction() ); 659 debugOut = false; 660 //System.out.println(sb.toString()); 661 idArray = sbToDataArray(sb); 662 retVal = idArray[0][0]; 663 664 return retVal; 665 } 666 667 668 public String queryExerSubType(int exerId) 669 { 670 String retVal = ""; 671 672 String [][] idArray = {{""}}; 673 String sql = "select sub_type from exercisetype where id="+exerId; 674 debugOut = false; 675 StringBuffer sb = query(sql,getUseProduction() ); 676 debugOut = false; 677 //System.out.println(sb.toString()); 678 idArray = sbToDataArray(sb); 679 retVal = idArray[0][0]; 680 681 return retVal; 682 } 683 684 685 public boolean loadFromDirectory(String workoutDirectory, boolean recurse) 686 { 687 // this method simply reads the dir for the xml files and then calls the 688 // loadFromDOMfile method. 689 boolean retVal = true; 690 String [] filesToLoad = null; 691 int filesCount = 0; 692 File dirFile = new File(workoutDirectory); 693 if (dirFile != null && dirFile.exists() && dirFile.isDirectory()) 694 { 695 String srcFileNames[] = dirFile.list(); 696 if (srcFileNames != null) 697 { 698 System.out.println("Looking in Dir: "+workoutDirectory); 699 filesToLoad = new String[countXmlsInDir(workoutDirectory)]; 700 File currFile = null; 701 for (int i = 0; i < srcFileNames.length; i++) 702 { 703 currFile = new File(srcFileNames[i]); 704 if (currFile != null && 705 !currFile.isDirectory() && 706 isThisAWrkFile(currFile)) 707 { 708 filesToLoad[filesCount++] = workoutDirectory +java.io.File.separator + currFile.getName(); 709 System.out.println("Found File: "+currFile.getName()); 710 } 711 } 712 713 if (recurse) 714 { 715 for (int i = 0; i < srcFileNames.length; i++) 716 { 717 currFile = new File(workoutDirectory +java.io.File.separator +srcFileNames[i]); 718 //System.out.println(currFile.getName()); 719 if (currFile != null && currFile.isDirectory()) loadFromDirectory(workoutDirectory +java.io.File.separator +srcFileNames[i], recurse); 720 } 721 } 722 723 for (int i = 0; i < filesCount; i++) 724 if (!loadFromDOMfile(filesToLoad[i])) retVal = false; 725 } 726 } 727 else 728 { 729 retVal = false; 730 System.out.println( 731 " parameter specified is NOT a directoryname: "+ workoutDirectory); 732 } 733 734 return retVal; 735 } 736 737 738 /** 739 *Counts the XMLs in a given dir. 740 @return the number of XML files in the spec'd directory 741 **/ 742 public int countXmlsInDir(String xmlDirectory) 743 { 744 File dirFile = new File(xmlDirectory); 745 int filesCount = 0; 746 if (dirFile != null && dirFile.isDirectory()) 747 { 748 String srcFileNames[] = dirFile.list(); 749 if (srcFileNames != null) 750 { 751 for (int i = 0; i < srcFileNames.length; i++) 752 { 753 File currFile = new File(srcFileNames[i]); 754 if (currFile != null && 755 !currFile.isDirectory() && 756 isThisAWrkFile(currFile)) 757 { 758 filesCount++; 759 } 760 } 761 } 762 } 763 return filesCount; 764 } 765 766 767 /** Checks if the passed File is a XML file (ie has a xml extension). 768 **/ 769 protected boolean isThisAWrkFile(File fileToCheck) 770 { 771 boolean retVal = false; 772 773 try 774 { 775 String filename = fileToCheck.getAbsolutePath(); 776 if (filename.trim().toLowerCase().endsWith(".xml") || 777 filename.trim().toLowerCase().endsWith(".wrk")) 778 { 779 retVal = true; 780 } 781 } 782 catch (Exception exception) 783 { 784 retVal = false; 785 System.err.println(exception.getClass().getName() + ": " 786 + exception.getMessage()); 787 } 788 789 return retVal; 790 } 791 792 793 /** gets the calorie data [col][row] - one row has 3 entries - date, calories, running average **/ 794 public int getTotalCalories(String userid) {return getTotalCalories(userid,"","");} 795 796 797 /** gets the calorie data [col][row] - one row has 3 entries - date, calories, running average **/ 798 public int getTotalCalories(String userid,String exerciseType, String exerciseSubType ) 799 { 800 int userRef = getUserTableRef(userid); 801 DecimalFormat df = new DecimalFormat( "#####0.0" ); 802 803 int exerId = getExercisetypeTableRef(exerciseType,exerciseSubType); 804 805 String sql = "select workout.calories\n" 806 + "from workout \n" 807 + "where \n" 808 + " workout.userID="+ userRef+" \n"; 809 if(exerId>0) 810 sql += " and workout.exercisetypeID="+ exerId+" \n"; 811 debugOut = true; 812 StringBuffer sb = query(sql, getUseProduction()); 813 debugOut = false; 814 String [][] idArray = {{""}}; // [col][row] 815 idArray = sbToDataArray(sb); 816 int num = 0; 817 if(idArray!=null && idArray[0]!=null) num = idArray[0].length; 818 819 int sumCals = 0; 820 for (int currRow = 0; currRow< num; currRow++) 821 { 822 sumCals += Integer.parseInt(idArray[0][currRow]); 823 } 824 825 return sumCals; 826 } 827 828 829 /** gets the calorie data [col][row] - one row has 3 entries - date, calories, running average **/ 830 public double getTotalKms(String userid ){return getTotalKms(userid,"","");} 831 832 833 /** gets the calorie data [col][row] - one row has 3 entries - date, calories, running average **/ 834 public double getTotalKms(String userid,String exerciseType, String exerciseSubType ) 835 { 836 int userRef = getUserTableRef(userid); 837 DecimalFormat df = new DecimalFormat( "#####0.0" ); 838 839 int exerId = getExercisetypeTableRef(exerciseType,exerciseSubType); 840 841 String sql = "select workout.distance_km\n" 842 + "from workout \n" 843 + "where \n" 844 + " workout.userID="+ userRef+" \n"; 845 if(exerId>0) 846 sql += " and workout.exercisetypeID="+ exerId+" \n"; 847 debugOut = false; 848 StringBuffer sb = query(sql, getUseProduction()); 849 debugOut = false; 850 String [][] idArray = {{""}}; // [col][row] 851 idArray = sbToDataArray(sb); 852 int num = idArray[0].length; 853 854 double sumKms = 0; 855 for (int currRow = 0; currRow< num; currRow++) 856 { 857 sumKms += Double.parseDouble(idArray[0][currRow]); 858 } 859 860 return sumKms; 861 } 862 863 864 /** gets the total minutes data. **/ 865 public double getTotalMins(String userid ){return getTotalMins(userid,"","");} 866 867 868 /** gets the total minutes for spec'd exer type **/ 869 public double getTotalMins(String userid,String exerciseType, String exerciseSubType ) 870 { 871 int userRef = getUserTableRef(userid); 872 DecimalFormat df = new DecimalFormat( "#####0.00" ); 873 874 int exerId = getExercisetypeTableRef(exerciseType,exerciseSubType); 875 876 String sql = "select workout.DURATION_SEC\n" 877 + "from workout \n" 878 + "where \n" 879 + " workout.userID="+ userRef+" \n"; 880 if(exerId>0) 881 sql += " and workout.exercisetypeID="+ exerId+" \n"; 882 debugOut = false; 883 StringBuffer sb = query(sql, getUseProduction()); 884 debugOut = false; 885 String [][] idArray = {{""}}; // [col][row] 886 idArray = sbToDataArray(sb); 887 int num = idArray[0].length; 888 889 double sumMins = 0; 890 for (int currRow = 0; currRow< num; currRow++) 891 { 892 sumMins += Double.parseDouble(idArray[0][currRow])/60.0; 893 } 894 895 return sumMins; 896 } 897 898 899 /** gets the calorie data [col][row] - one row has 3 entries - date, calories, running average **/ 900 public String[][] getCaloriesGoogleData(String userid) 901 { 902 /* 903 var data = google.visualization.arrayToDataTable([ 904 ['Month', 'Bolivia', 'Ecuador', 'Madagascar', 'Papua Guinea','Rwanda', 'Average'], 905 ['2004/05', 165, 938, 522, 998, 450, 614.6], 906 ['2005/06', 135, 1120, 599, 1268, 288, 682], 907 ['2006/07', 157, 1167, 587, 807, 397, 623], 908 ['2007/08', 139, 1110, 615, 968, 215, 609.4], 909 ['2008/09', 136, 691, 629, 1026, 366, 569.6] 910 ]); 911 912 var options = 913 { 914 title : 'Monthly Coffee Production by Country', 915 vAxis: {title: "Calories"}, 916 hAxis: {title: "Date"}, 917 seriesType: "bars", 918 series: {5: {type: "line"}} 919 }; 920 */ 921 int userRef = getUserTableRef(userid); 922 DecimalFormat df = new DecimalFormat( "#####0.0" ); 923 924 String sql = "select workout.workoutDate,workout.calories,\n" 925 + "exercisetype.sub_type||'-'||exercisetype.type as exercisetype ,equipmenttype.type as equipmenttype \n" 926 + "from workout,exercisetype,equipmenttype \n" 927 + "where \n" 928 + " workout.userID="+ userRef+" \n" 929 + " and workout.exercisetypeID=exercisetype.ID \n" 930 + " and workout.equipmentID=equipmenttype.ID \n" 931 + "order by workoutDate,equipmenttype.type\n"; 932 debugOut = false; 933 StringBuffer sb = query(sql, getUseProduction()); 934 debugOut = false; 935 String [][] idArray = {{""}}; // [col][row] 936 idArray = sbToDataArray(sb); 937 int num = idArray[0].length; 938 939 String [][] retVal = new String[4][num+1]; 940 941 retVal[0][0] = "Date"; 942 retVal[1][0] = "Calories"; 943 retVal[2][0] = "Total"; 944 retVal[3][0] = "Average"; 945 int sumCals = 0; 946 for (int currRow = 0; currRow< num; currRow++) 947 { 948 retVal[0][currRow+1] = idArray[0][currRow]; 949 retVal[1][currRow+1] = idArray[1][currRow]; 950 sumCals += Integer.parseInt(idArray[1][currRow]); 951 retVal[2][currRow+1] = ""+df.format(sumCals); 952 retVal[3][currRow+1] = ""+df.format((sumCals/(currRow+1))); 953 } 954 955 return retVal; 956 } 957 958 959 /** gets the calorie data in one long string that can be directly pt into a google data array. **/ 960 public String getStackedDistanceKmGoogleDataStr(String userid) 961 { 962 String retVal = "["; 963 int userRef = getUserTableRef(userid); 964 DecimalFormat df = new DecimalFormat( "#####0.000" ); 965 966 String sql = "select workoutDate,distance_km,\n" 967 + "exercisetype.sub_type||'-'||exercisetype.type as exercisetype ,equipmenttype.type as equipmenttype \n" 968 + "from workout,exercisetype,equipmenttype \n" 969 + "where \n" 970 + " workout.userID="+ userRef+" \n" 971 + " and workout.exercisetypeID=exercisetype.ID \n" 972 + " and workout.equipmentID=equipmenttype.ID \n" 973 + "order by workoutDate,equipmenttype.type\n"; 974 debugOut = true; 975 StringBuffer sb = query(sql, getUseProduction()); 976 debugOut = false; 977 String [][] idArray = {{""}}; // [col][row] 978 idArray = sbToDataArray(sb); 979 int num = idArray[0].length; 980 String currDate = ""; 981 String nextDate = ""; 982 int dupes = 0; 983 int totalDupes = 0; 984 int dateCount = 0; 985 double sumKms = 0; 986 987 retVal+="['Date', 'Distance (km)', 'Average km']"; 988 for (int currRow = 0; currRow< num; currRow++) 989 { 990 currDate= idArray[0][currRow]; 991 dateCount++; 992 dupes = 0; 993 retVal+=",['"+idArray[0][currRow]+"'"; 994 for (int nextRow = currRow+1; nextRow< num; nextRow++) 995 { 996 nextDate = idArray[0][nextRow]; 997 if (nextDate.equals(currDate)) 998 { 999 if (dupes==0) 1000 { 1001 sumKms += Double.parseDouble(idArray[1][currRow]); 1002 retVal+=", ["+idArray[1][currRow]; 1003 } 1004 // add a stacked entry 1005 dupes++; 1006 sumKms += Double.parseDouble(idArray[1][nextRow]); 1007 retVal+=", "+idArray[1][nextRow]; 1008 } 1009 else // add currRow on its own 1010 { 1011 sumKms += Double.parseDouble(idArray[1][currRow]); 1012 retVal+=", "+idArray[1][currRow]; 1013 nextRow = num; // exit inner loop 1014 } 1015 } 1016 if (dupes>0) retVal+="]"; 1017 totalDupes+=dupes; 1018 retVal+=","+df.format((sumKms/(currRow-totalDupes+1)))+"]\n"; 1019 currRow += dupes; // jump over those rows 1020 } 1021 1022 retVal += "]"; 1023 return retVal; 1024 } 1025 1026 1027 /** gets the calorie data in one long string that can be directly pt into a google data array. **/ 1028 public String getCaloriesPieGoogleDataStr(String userid) 1029 { 1030 String retVal = "[['ExerciseType','Calories'],"; 1031 int userRef = getUserTableRef(userid); 1032 DecimalFormat df = new DecimalFormat( "#####0.0" ); 1033 1034 String sql = "select\n" 1035 + "exercisetype.sub_type||'-'||exercisetype.type as exercisetype \n" 1036 + ",sum(calories) as TotalCalories\n" 1037 + "from workout,exercisetype \n" 1038 + "where \n" 1039 + " workout.userID="+ userRef+" and calories>0 \n" 1040 + " and workout.exercisetypeID=exercisetype.ID \n" 1041 + "group by exercisetype.sub_type||'-'||exercisetype.type\n" 1042 + "order by exercisetype.sub_type||'-'||exercisetype.type\n"; 1043 debugOut = true; 1044 StringBuffer sb = query(sql, getUseProduction()); 1045 System.out.println(sb.toString()); 1046 debugOut = false; 1047 String [][] idArray = {{""}}; // [col][row] 1048 idArray = sbToDataArray(sb); 1049 int num = idArray[0].length; 1050 for (int currRow = 0; currRow< num; currRow++) 1051 { 1052 if (currRow>0) retVal+=" "; 1053 retVal+= "['"+idArray[0][currRow]+"', "+df.format(Double.parseDouble(idArray[1][currRow]))+" ]"; 1054 if (currRow< (num-1)) retVal+=",\n"; 1055 } 1056 retVal+= "]"; 1057 //System.out.println("getCaloriesPieGoogleDataStr:\n"+retVal); 1058 return retVal; 1059 } 1060 1061 1062 /** gets the exercise time data in one long string that can be directly pt into a google data array. **/ 1063 public String getTimePieGoogleDataStr(String userid) 1064 { 1065 String retVal = "[['ExerciseType','TimeMinutes'],"; 1066 int userRef = getUserTableRef(userid); 1067 DecimalFormat df = new DecimalFormat( "#####0.0" ); 1068 1069 String sql = "select\n" 1070 + "exercisetype.sub_type||'-'||exercisetype.type as exercisetype \n" 1071 + ",sum(workout.DURATION_SEC) as TotalSeconds \n" 1072 + "from workout,exercisetype \n" 1073 + "where \n" 1074 + " workout.userID="+ userRef+" and calories>0 \n" 1075 + " and workout.exercisetypeID=exercisetype.ID \n" 1076 + "group by exercisetype.sub_type||'-'||exercisetype.type\n" 1077 + "order by exercisetype.sub_type||'-'||exercisetype.type\n"; 1078 debugOut = true; 1079 StringBuffer sb = query(sql, getUseProduction()); 1080 System.out.println(sb.toString()); 1081 debugOut = false; 1082 String [][] idArray = {{""}}; // [col][row] 1083 idArray = sbToDataArray(sb); 1084 int num = idArray[0].length; 1085 for (int currRow = 0; currRow< num; currRow++) 1086 { 1087 if (currRow>0) retVal+=" "; 1088 retVal+= "['"+idArray[0][currRow]+"', "+df.format(Double.parseDouble(idArray[1][currRow])/60.0)+" ]"; 1089 if (currRow< (num-1)) retVal+=",\n"; 1090 } 1091 retVal+= "]"; 1092 //System.out.println("getTimePieGoogleDataStr:\n"+retVal); 1093 return retVal; 1094 } 1095 1096 1097 /** gets the calorie data in one long string that can be directly pt into a google data array. **/ 1098 public String getDistanceKmPieGoogleDataStr(String userid) 1099 { 1100 String retVal = "[['ExerciseType','Distance_km'],"; 1101 int userRef = getUserTableRef(userid); 1102 DecimalFormat df = new DecimalFormat( "#####0.0" ); 1103 1104 String sql = "select\n" 1105 + "exercisetype.sub_type||'-'||exercisetype.type as exercisetype \n" 1106 + ",sum(distance_km) as TotalKm\n" 1107 + "from workout,exercisetype \n" 1108 + "where \n" 1109 + " workout.userID="+ userRef+" and distance_km>0 \n" 1110 + " and workout.exercisetypeID=exercisetype.ID \n" 1111 + "group by exercisetype.sub_type||'-'||exercisetype.type\n" 1112 + "order by exercisetype.sub_type||'-'||exercisetype.type\n"; 1113 debugOut = true; 1114 StringBuffer sb = query(sql, getUseProduction()); 1115 System.out.println(sb.toString()); 1116 debugOut = false; 1117 String [][] idArray = {{""}}; // [col][row] 1118 idArray = sbToDataArray(sb); 1119 int num = idArray[0].length; 1120 for (int currRow = 0; currRow< num; currRow++) 1121 { 1122 if (currRow>0) retVal+=" "; 1123 retVal+= "['"+idArray[0][currRow]+"', "+df.format(Double.parseDouble(idArray[1][currRow]))+" ]"; 1124 if (currRow< (num-1)) retVal+=",\n"; 1125 } 1126 retVal+= "]"; 1127 //System.out.println("getCaloriesPieGoogleDataStr:\n"+retVal); 1128 return retVal; 1129 } 1130 1131 1132 /** gets the calories data in one long string that can be directly pt into a google data array. **/ 1133 public String getCaloriesGoogleDataStr(String userid) 1134 { 1135 return getDbFieldGoogleDataStr(userid, "calories"); 1136 } 1137 1138 1139 /** gets the distance_kn data in one long string that can be directly pt into a google data array. **/ 1140 public String getDistanceKmGoogleDataStr(String userid) 1141 { 1142 return getDbFieldGoogleDataStr(userid, "distance_km"); 1143 } 1144 1145 1146 /** gets db field data in one long string that can be directly pt into a google data array. **/ 1147 public String getDbFieldGoogleDataStr(String userid, String dataField) 1148 { 1149 String retVal = "[\n"; 1150 int userRef = getUserTableRef(userid); 1151 DecimalFormat df = new DecimalFormat( "#####0.000" ); 1152 boolean ignoringZeros = true; 1153 1154 String sql = "select workoutDate,"+dataField+",\n" 1155 + "exercisetype.sub_type||'-'||exercisetype.type as exercisetype \n" 1156 + "from workout,exercisetype \n" 1157 + "where \n" 1158 + " workout.userID="+ userRef+" \n" 1159 + " and workout.exercisetypeID=exercisetype.ID \n"; 1160 if (ignoringZeros) sql += " and workout."+dataField+">0\n"; 1161 sql += "order by workoutDate,exercisetype.sub_type||'-'||exercisetype.type\n"; 1162 1163 String sql2 = "select distinct exercisetype.sub_type||'-'||exercisetype.type as exercisetype\n" 1164 + "from workout,exercisetype \n" 1165 + "where \n" 1166 + " workout.userID="+ userRef+" \n" 1167 + " and workout.exercisetypeID=exercisetype.ID \n" 1168 + "order by exercisetype.sub_type||'-'||exercisetype.type \n"; 1169 debugOut = true; 1170 StringBuffer sb = query(sql, getUseProduction()); 1171 System.out.println(sb.toString()); 1172 debugOut = false; 1173 String [][] idArray = {{""}}; // [col][row] 1174 idArray = sbToDataArray(sb); 1175 debugOut = true; 1176 sb = query(sql2, getUseProduction()); 1177 System.out.println(sb.toString()); 1178 debugOut = false; 1179 String [][] idArray2 = {{""}}; // [col][row] 1180 idArray2 = sbToDataArray(sb); 1181 int num = idArray[0].length; 1182 int numExerTypes = idArray2[0].length; 1183 String currDate = ""; 1184 String nextDate = ""; 1185 int dupes = 0; 1186 int totalDupes = 0; 1187 int dateCount = 0; 1188 double sumKms = 0; 1189 String [] exerTypes = new String[numExerTypes]; 1190 double [] exerKms = new double[numExerTypes]; 1191 1192 retVal+=" ['Date'"; 1193 for(int exerType=0; exerType<numExerTypes;exerType++) 1194 { 1195 retVal+=", '"+idArray2[0][exerType]+"'"; 1196 exerTypes[exerType] = idArray2[0][exerType]; 1197 } 1198 retVal+=", 'Average "+dataField+"']\n"; 1199 1200 // count the unique dates 1201 for (int currRow = 0; currRow< num; currRow++) 1202 { 1203 dateCount++; 1204 currDate= idArray[0][currRow]; 1205 for (int i=currRow+1;i<num;i++) 1206 if(currDate.equals(idArray[0][i])) currRow++; //skip rows with dupes 1207 } 1208 1209 // clear the results matrix 1210 double [][] matrix = new double[numExerTypes][dateCount]; 1211 for (int i =0;i<numExerTypes;i++) 1212 for (int j=0;j<dateCount;j++) 1213 matrix[i][j]=0.0; 1214 1215 int dateRow = 0; 1216 for(int currRow = 0; currRow< num; currRow++) 1217 { 1218 currDate=idArray[0][currRow]; 1219 // save currRow Data 1220 for (int j=0;j<numExerTypes; j++) 1221 if(idArray[2][currRow].equals(exerTypes[j])) 1222 matrix[j][dateRow]+=Double.parseDouble(idArray[1][currRow]); 1223 for(int dupeRow = currRow+1; dupeRow< num; dupeRow++) 1224 { 1225 if (currDate.equals(idArray[0][dupeRow])) 1226 { 1227 dupes++; 1228 currRow++; 1229 for (int j=0;j<numExerTypes; j++) 1230 if(idArray[2][dupeRow].equals(exerTypes[j])) 1231 matrix[j][dateRow]+=Double.parseDouble(idArray[1][dupeRow]); 1232 } 1233 else dupeRow=num; 1234 } 1235 1236 // now sum up and save teh outputString for the data 1237 retVal+=",['"+currDate+"'"; 1238 for (int i=0;i<numExerTypes;i++) 1239 { 1240 sumKms+=matrix[i][dateRow]; 1241 retVal+=" , "+df.format(matrix[i][dateRow]); 1242 } 1243 dateRow++; 1244 retVal += ", "+df.format(sumKms/dateRow); 1245 retVal += "]\n"; 1246 } 1247 1248 retVal += "]"; 1249 System.out.println(dataField+"GoogleDataStr:\n"+retVal); 1250 return retVal; 1251 } 1252 1253 1254 /** gets the calorie data [col][row] - one row has 3 entries - date, calories, running average **/ 1255 public String[][] getDistanceKmGoogleData(String userid) 1256 { 1257 /* 1258 var data = google.visualization.arrayToDataTable([ 1259 ['Month', 'Bolivia', 'Ecuador', 'Madagascar', 'Papua Guinea','Rwanda', 'Average'], 1260 ['2004/05', 165, 938, 522, 998, 450, 614.6], 1261 ['2005/06', 135, 1120, 599, 1268, 288, 682], 1262 ['2006/07', 157, 1167, 587, 807, 397, 623], 1263 ['2007/08', 139, 1110, 615, 968, 215, 609.4], 1264 ['2008/09', 136, 691, 629, 1026, 366, 569.6] 1265 ]); 1266 1267 var options = 1268 { 1269 title : 'Monthly Coffee Production by Country', 1270 vAxis: {title: "Calories"}, 1271 hAxis: {title: "Date"}, 1272 seriesType: "bars", 1273 series: {5: {type: "line"}} 1274 }; 1275 */ 1276 int userRef = getUserTableRef(userid); 1277 DecimalFormat df = new DecimalFormat( "#####0.000" ); 1278 1279 String sql = "select workoutDate,distance_km,\n" 1280 + "exercisetype.sub_type||'-'||exercisetype.type as exercisetype ,equipmenttype.type as equipmenttype \n" 1281 + "from workout,exercisetype,equipmenttype \n" 1282 + "where \n" 1283 + " workout.userID="+ userRef+" \n" 1284 + " and workout.exercisetypeID=exercisetype.ID \n" 1285 + " and workout.equipmentID=equipmenttype.ID \n" 1286 + "order by workoutDate\n"; 1287 debugOut = true; 1288 StringBuffer sb = query(sql, getUseProduction()); 1289 debugOut = false; 1290 String [][] idArray = {{""}}; // [col][row] 1291 idArray = sbToDataArray(sb); 1292 int num = idArray[0].length; 1293 1294 String [][] retVal = new String[4][num+1]; 1295 1296 retVal[0][0] = "Date"; 1297 retVal[1][0] = "Distance km"; 1298 retVal[2][0] = "Total km"; 1299 retVal[3][0] = "Average"; 1300 double sumKms = 0; 1301 for (int currRow = 0; currRow< num; currRow++) 1302 { 1303 retVal[0][currRow+1] = idArray[0][currRow]; 1304 retVal[1][currRow+1] = idArray[1][currRow]; 1305 sumKms += Double.parseDouble(idArray[1][currRow]); 1306 retVal[2][currRow+1] = ""+df.format(sumKms); 1307 retVal[3][currRow+1] = ""+df.format((sumKms/(currRow+1))); 1308 } 1309 1310 return retVal; 1311 } 1312 1313 1314 /** exports all workkout data to a xml file. 1315 * 1316 * @return the directory where the xml filenames got exported**/ 1317 public String exportWorkouts(String userid, String dirName) 1318 { 1319 int userRef = getUserTableRef(userid); 1320 DecimalFormat df = new DecimalFormat( "#####0.000" ); 1321 DecimalFormat df2 = new DecimalFormat( "00" ); 1322 1323 String sql = "SELECT ID,USERID,WORKOUTDATE,EXERCISETYPEID,EQUIPMENTID,DISTANCE_KM,\n" 1324 + "CALORIES,DURATION_SEC,ELEVATION_M,PROGRAMTYPEID\n" 1325 + ",HEARTRATE_AVG,HEARTRATE_MAX,HEARTRATE_MIN\n" 1326 + "FROM WORKOUT\nwhere\n" 1327 + " workout.userID="+ userRef 1328 + " order by workoutDate,EXERCISETYPEID,DISTANCE_KM desc\n"; 1329 debugOut = true; 1330 StringBuffer sb = query(sql, getUseProduction()); 1331 debugOut = false; 1332 String [][] idArray = {{""}}; // [col][row] 1333 idArray = sbToDataArray(sb); 1334 int num = idArray[0].length; 1335 String separator = java.io.File.separator; 1336 Util.ensureFolderExists(dirName); 1337 1338 StringBuffer xmlStr = null; 1339 for (int currRow = 0; currRow< num; currRow++) 1340 { 1341 xmlStr = new StringBuffer("<?xml version=\"1.0\" encoding=\"utf-8\"?>\n"); 1342 xmlStr.append("<workout>\n"); 1343 1344 xmlStr.append(" <userid>"); 1345 xmlStr.append(userid); 1346 xmlStr.append("</userid>\n"); 1347 1348 xmlStr.append(" <make>"); 1349 xmlStr.append(queryEquipMake(Integer.parseInt(idArray[4][currRow])).trim()); 1350 xmlStr.append("</make>\n"); 1351 1352 xmlStr.append(" <model>"); 1353 xmlStr.append(queryEquipModel(Integer.parseInt(idArray[4][currRow])).trim()); 1354 xmlStr.append("</model>\n"); 1355 1356 xmlStr.append(" <type>"); 1357 xmlStr.append(queryExerType(Integer.parseInt(idArray[3][currRow])).trim()); 1358 xmlStr.append("</type>\n"); 1359 1360 xmlStr.append(" <subtype>"); 1361 xmlStr.append(queryExerSubType(Integer.parseInt(idArray[3][currRow])).trim()); 1362 xmlStr.append("</subtype>\n\n"); 1363 1364 xmlStr.append(" <summary>\n"); 1365 1366 xmlStr.append(" <Year>"); 1367 xmlStr.append(""+idArray[2][currRow].substring(0,4).trim()); 1368 xmlStr.append("</Year>\n"); 1369 1370 xmlStr.append(" <Month>"); 1371 xmlStr.append((""+Integer.parseInt(idArray[2][currRow].substring(5,7))).trim()); 1372 xmlStr.append("</Month>\n"); 1373 1374 xmlStr.append(" <Date>"); 1375 xmlStr.append((""+Integer.parseInt(idArray[2][currRow].substring(8,10))).trim()); 1376 xmlStr.append("</Date>\n"); 1377 1378 xmlStr.append(" <Calories>"); 1379 xmlStr.append(""+idArray[6][currRow].trim().trim()); 1380 xmlStr.append("</Calories>\n"); 1381 1382 xmlStr.append(" <Distances>"); 1383 xmlStr.append(""+idArray[5][currRow].trim()); 1384 xmlStr.append("</Distances>\n"); 1385 1386 xmlStr.append(" <TotalTime>"); 1387 xmlStr.append(""+idArray[7][currRow].trim()); 1388 xmlStr.append("</TotalTime>\n"); 1389 1390 xmlStr.append(" <HeartRate_AVG>"); 1391 xmlStr.append(""+idArray[10][currRow].trim()); 1392 xmlStr.append("</HeartRate_AVG>\n"); 1393 1394 xmlStr.append(" <HeartRate_MIN>"); 1395 xmlStr.append(""+idArray[12][currRow].trim()); 1396 xmlStr.append("</HeartRate_MIN>\n"); 1397 1398 xmlStr.append(" <HeartRate_MAX>"); 1399 xmlStr.append(""+idArray[11][currRow].trim()); 1400 xmlStr.append("</HeartRate_MAX>\n"); 1401 1402 xmlStr.append(" </summary>\n</workout>"); 1403 1404 Util.writeStringToFile(xmlStr.toString(),dirName+separator+"workout-"+df2.format(currRow)+".wrk"); 1405 } 1406 1407 1408 1409 return dirName; 1410 } 1411 1412 1413 /** 1414 * Adds a workout entry to the DB based on the BEAN variables obtained from a web form. 1415 **/ 1416 public void addWorkout() 1417 { 1418 // gets called after the bean is filled 1419 /* 1420 String userId; 1421 String workoutDate; 1422 String exerciseType; 1423 String exerciseSubType; 1424 String equipment; 1425 double distance_km; 1426 int calories ; 1427 int duration_SEC; 1428 double elevation_M; 1429 String programType; 1430 int heartRate_AVG; 1431 int heartRate_MIN; 1432 int heartRate_MAX; 1433 */ 1434 1435 System.out.println("Beans are holding:"); 1436 System.out.println(" userId: "+userId); 1437 System.out.println(" workoutDate: "+workoutDate); 1438 System.out.println(" exerciseType: "+exerciseType); 1439 System.out.println("exerciseSubType: "+exerciseSubType); 1440 System.out.println(" indoor: "+indoorOutdoor); 1441 System.out.println(" equipment: "+equipment); 1442 System.out.println(" distance_km: "+distance_km); 1443 System.out.println(" calories: "+calories); 1444 System.out.println(" duration_MIN: "+duration_MIN); 1445 System.out.println(" elevation_M: "+elevation_M); 1446 System.out.println(" programType: "+programType); 1447 System.out.println(" heartRate_AVG: "+heartRate_AVG); 1448 System.out.println(" heartRate_MIN: "+heartRate_MIN); 1449 System.out.println(" heartRate_MAX: "+heartRate_MAX); 1450 int yr; 1451 int mo; 1452 int dt; 1453 int calories = 0; 1454 double miles = 0.0; 1455 double km = 0.0; 1456 double elev = 0.0; 1457 double elev_m = 0.0; 1458 double time_sec = 0.0; 1459 //String userId = "tgutwin"; 1460 int equpimentId = 0; 1461 int exerId = 1; 1462 String exerType = ""; 1463 1464 String equipTag = this.equipment; 1465 String equipModelTag = ""; 1466 if (this.equipment.equals("Livestrong")) 1467 { 1468 exerciseType="Running"; 1469 exerciseSubType="Indoor"; 1470 equipModelTag = "LSPRO2"; 1471 } 1472 if (this.equipment.equals("Schwinn")) 1473 { 1474 exerciseType="Cycling"; 1475 exerciseSubType="Indoor"; 1476 equipModelTag = "212"; 1477 } 1478 if (exerciseType.equals("Cardio") && exerciseSubType.equals("")) exerciseSubType="Kick-boxing"; 1479 if ((exerciseType.equals("Running") || exerciseType.equals("Cycling") || exerciseType.equals("Walking") ) 1480 && exerciseSubType.equals("")) 1481 exerciseSubType=(indoorOutdoor?"Indoor":"Outdoor"); 1482 equpimentId =getEquipmentTableRef(equipTag,equipModelTag); 1483 exerId = getExercisetypeTableRef(exerciseType,exerciseSubType); 1484 if (equpimentId==0 && exerId>0 ) equpimentId = getEquipmentTableRef( exerId); 1485 equipment=queryEquipMake(equpimentId); 1486 1487 yr = Integer.parseInt(workoutDate.substring(0,4)); 1488 mo = Integer.parseInt(workoutDate.substring(5,7)); 1489 dt = Integer.parseInt(workoutDate.substring(8,10)); 1490 calories = Integer.parseInt(""+this.calories); 1491 km = Double.parseDouble(""+this.distance_km); 1492 miles = km * 0.62; 1493 time_sec = Double.parseDouble(""+(this.duration_MIN*60.0)); 1494 1495 System.out.println("Beans are NOW holding:"); 1496 System.out.println(" userId: "+userId); 1497 System.out.println(" workoutDate: "+workoutDate); 1498 System.out.println(" exerciseType: "+exerciseType); 1499 System.out.println("exerciseSubType: "+exerciseSubType); 1500 System.out.println(" indoor: "+indoorOutdoor); 1501 System.out.println(" equipment: "+equipment); 1502 System.out.println(" distance_km: "+distance_km); 1503 System.out.println(" calories: "+calories); 1504 System.out.println(" duration_MIN: "+duration_MIN); 1505 System.out.println(" elevation_M: "+elevation_M); 1506 System.out.println(" programType: "+programType); 1507 System.out.println(" heartRate_AVG: "+heartRate_AVG); 1508 System.out.println(" heartRate_MIN: "+heartRate_MIN); 1509 System.out.println(" heartRate_MAX: "+heartRate_MAX); 1510 1511 queryAddWorkout(this.userId, yr, mo, dt, exerId, equpimentId, calories, km, elev_m, time_sec, ""); 1512 } 1513 1514 /** 1515 * Executes a SQL statement to add the data in the WorkoutFile to the DB. 1516 * 1517 * @return a StringBuffer containing the ResultSet rows with <b/>ONLY</b> the feeder_cd one result per line. 1518 **/ 1519 public StringBuffer queryAddWorkout(String userid, int yr, int mo, int dt, int exer, int equip, int cal, double km, double elev_m, double time_sec, String prog) 1520 { 1521 return queryAddWorkout(userid, yr, mo, dt, exer, equip, cal, km, elev_m, time_sec, prog, heartRate_AVG, heartRate_MIN, heartRate_MAX); 1522 } 1523 1524 1525 /** 1526 * Executes a SQL statement to add the data in the WorkoutFile to the DB. 1527 * 1528 * @return a StringBuffer containing the ResultSet rows with <b/>ONLY</b> the feeder_cd one result per line. 1529 **/ 1530 public StringBuffer queryAddWorkout(String userid, int yr, int mo, int dt, int exer, 1531 int equip, int cal, double km, double elev_m, double time_sec, String prog, 1532 int hrAvg, int hrMin, int hrMax) 1533 { 1534 int nextId = getNextWorkoutRef(); 1535 String dateStamp = (yr<100?"20":"")+yr+"-"+(mo<10?"0":"")+mo+"-"+(dt<10?"0":"")+dt;// DATE - format: YYYY-MM-DD 1536 int userRef = getUserTableRef(userid); 1537 DecimalFormat df = new DecimalFormat( "###0.000" ); 1538 if(exer>0 && equip <1) equip = getEquipmentTableRef( exer); 1539 1540 // Check if it is already in the table 1541 String sql = "select ID, userID, workoutDate, exerciseTypeID, equipmentID from workout where \n" 1542 + " userID="+ userRef+ " and \n" 1543 + " workoutDate='"+dateStamp+"'"+ " and \n" 1544 + "exerciseTypeID="+exer+ " and \n" 1545 + " calories="+cal+ " and \n" 1546 + " duration_SEC="+time_sec+ " and \n" 1547 + " equipmentID="+equip+"\n"; 1548 1549 debugOut = true; 1550 StringBuffer retVal = query(sql, getUseProduction()); 1551 debugOut = false; 1552 String [][] idArray = {{""}}; 1553 idArray = sbToDataArray(retVal); 1554 1555 // This only inserts the data if it is NOT already in the table 1556 if (idArray[0][0].equals("")) 1557 { 1558 sql = "insert into workout( ID, userID, workoutDate, exerciseTypeID, equipmentID, distance_km, calories , "+ 1559 " duration_SEC, elevation_M, programTypeID, heartRate_AVG, heartRate_MIN, heartRate_MAX ) \n"+ 1560 "VALUES (" 1561 + nextId+ ", " 1562 + userRef+ ", " 1563 + "'"+dateStamp+"'"+ ", " 1564 + (exer>0?exer:"null")+ ", " 1565 + (equip>0?equip:"null")+ ", " 1566 + df.format(km)+ ", " 1567 + cal+ ", " 1568 + time_sec+ ", " 1569 + df.format(elev_m)+ ", " 1570 + "1" + ", " 1571 + hrAvg+ ", " + hrMin+ ", " + hrMax 1572 +")\n"; 1573 1574 debugOut = true; 1575 retVal = query(sql, getUseProduction()); 1576 debugOut = false; 1577 } 1578 else retVal = new StringBuffer(""); 1579 1580 return retVal; 1581 } 1582 1583 1584 private String getTagValue(String sTag, Element eElement) 1585 { 1586 String retVal = ""; 1587 NodeList mainNList = eElement.getElementsByTagName(sTag); 1588 if (mainNList!=null && mainNList.item(0)!=null) 1589 { 1590 NodeList nlList = mainNList.item(0).getChildNodes(); 1591 org.w3c.dom.Node nValue = (org.w3c.dom.Node) nlList.item(0); 1592 retVal = nValue.getNodeValue(); 1593 } 1594 return retVal; 1595 } 1596 1597 1598 public boolean loadFromDOMfile( String fXmlFilename) 1599 { 1600 boolean retVal = true; 1601 String dateStr = ""; 1602 int yr; 1603 int mo; 1604 int dt; 1605 int calories = 0; 1606 double miles = 0.0; 1607 double km = 0.0; 1608 double elev = 0.0; 1609 double elev_m = 0.0; 1610 double time_sec = 0.0; 1611 String userId = "tgutwin"; 1612 int equpimentId = 1; 1613 int exerId = 1; 1614 String exerType = ""; 1615 try 1616 { 1617 File fXmlFile = new File(fXmlFilename); 1618 DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance(); 1619 DocumentBuilder dBuilder = dbFactory.newDocumentBuilder(); 1620 Document doc = dBuilder.parse(fXmlFile); 1621 doc.getDocumentElement().normalize(); 1622 1623 System.out.println("Root element :" + doc.getDocumentElement().getNodeName()); 1624 Element eElement = (Element) doc.getDocumentElement(); 1625 String userTag = getTagValue("userid", eElement); 1626 if (userTag.equalsIgnoreCase("USER1")) userId="mtourlas"; 1627 else if (userTag.equalsIgnoreCase("USER2")) userId="tgutwin"; 1628 else userId=userTag; 1629 String equipTag = getTagValue("make", eElement); 1630 String equipModelTag = getTagValue("model", eElement); 1631 if (equipModelTag.equalsIgnoreCase("LSPRO2")) equpimentId =1; 1632 else equpimentId =getEquipmentTableRef(equipTag,equipModelTag); 1633 String exerTypeTag = getTagValue("type", eElement); 1634 String exerSubTypeTag = getTagValue("subtype", eElement); 1635 if (exerSubTypeTag.equals("")) exerSubTypeTag="Indoor"; 1636 if (!exerTypeTag.equals("")) 1637 { 1638 exerId = getExercisetypeTableRef(exerTypeTag, exerSubTypeTag); 1639 } 1640 1641 NodeList nList = doc.getElementsByTagName("summary"); 1642 org.w3c.dom.Node nNode = nList.item(0); 1643 System.out.println("-----------------------"+ nNode.getNodeName()); 1644 if (nNode.getNodeType() == org.w3c.dom.Node.ELEMENT_NODE) 1645 { 1646 eElement = (Element) nNode; 1647 System.out.println("Year : " + getTagValue("Year", eElement)); 1648 yr = Integer.parseInt(getTagValue("Year", eElement)); 1649 System.out.println("Month : " + getTagValue("Month", eElement)); 1650 mo = Integer.parseInt(getTagValue("Month", eElement)); 1651 System.out.println("Date : " + getTagValue("Date", eElement)); 1652 dt = Integer.parseInt(getTagValue("Date", eElement)); 1653 dateStr = ""+yr+"_"+mo+"_"+dt; 1654 System.out.println("Avgpace : " + getTagValue("Avgpace", eElement)); 1655 System.out.println("Calories : " + getTagValue("Calories", eElement)); 1656 calories = Integer.parseInt(getTagValue("Calories", eElement)); 1657 System.out.print("Distances : " + getTagValue("Distances", eElement)+ " miles"); 1658 miles = Double.parseDouble(getTagValue("Distances", eElement)); 1659 km = miles / 0.62; 1660 System.out.println(" ("+km+" km)"); 1661 System.out.println("Totalelev : " + getTagValue("Totalelev", eElement)); 1662 if (!(getTagValue("Totalelev", eElement).equals(""))) 1663 { 1664 elev = Double.parseDouble(getTagValue("Totalelev", eElement)); 1665 elev_m = elev *0.3048; 1666 } 1667 System.out.println("TotalTime : " + getTagValue("TotalTime", eElement)); 1668 if (!(getTagValue("TotalTime", eElement).equals(""))) 1669 time_sec = Double.parseDouble(getTagValue("TotalTime", eElement)); 1670 1671 requestHR(); 1672 queryAddWorkout(userId, yr, mo, dt, exerId, equpimentId, calories, km, elev_m, time_sec, "",getHeartRate_AVG(), getHeartRate_MIN(), getHeartRate_MAX() ); 1673 } 1674 //fXmlFile.close(); 1675 } 1676 catch (Exception e) 1677 { 1678 e.printStackTrace(); 1679 } 1680 1681 1682 return retVal; 1683 } 1684 1685 1686 /** 1687 * Reads a DOM object and puts the conained data into the DB. 1688 * 1689 * @param doc is the Document holding the exercise data. 1690 * @return true if succesful load into DB. 1691 **/ 1692 public boolean loadFromDOM(org.w3c.dom.Document doc) 1693 { 1694 boolean retVal = true; 1695 String dateStr = ""; 1696 int calories = 0; 1697 double miles = 0; 1698 double km = 0; 1699 String separator = java.io.File.separator; 1700 try 1701 { 1702 //doc.getDocumentElement().normalize(); 1703 1704 System.out.println("Root element :" + doc.getDocumentElement().getNodeName()); 1705 org.w3c.dom.NodeList nList = (org.w3c.dom.NodeList) doc.getElementsByTagName("summary"); 1706 //org.w3c.dom.Element summElement = doc.getElement("summary"); 1707 System.out.println("-----------"+(nList==null)+"------------"); 1708 org.w3c.dom.Node nNode = ( org.w3c.dom.Node) nList.item(0); 1709 if (nNode!=null && nNode.getNodeType() == org.w3c.dom.Node.ELEMENT_NODE) 1710 { 1711 Element eElement = (Element) nNode; 1712 System.out.println("Year : " + getTagValue("Year", eElement)); 1713 System.out.println("Month : " + getTagValue("Month", eElement)); 1714 System.out.println("Date : " + getTagValue("Date", eElement)); 1715 dateStr = getTagValue("Year", eElement)+"_"+getTagValue("Month", eElement)+"_"+getTagValue("Date", eElement); 1716 System.out.println("Avgpace : " + getTagValue("Avgpace", eElement)); 1717 System.out.println("Calories : " + getTagValue("Calories", eElement)); 1718 calories = Integer.parseInt(getTagValue("Calories", eElement)); 1719 System.out.print("Distances : " + getTagValue("Distances", eElement)+ " miles"); 1720 miles = Double.parseDouble(getTagValue("Distances", eElement)); 1721 km = miles / 0.62; 1722 System.out.println(" ("+km+" km)"); 1723 System.out.println("Totalelev : " + getTagValue("Totalelev", eElement)); 1724 System.out.println("TotalTime : " + getTagValue("TotalTime", eElement)); 1725 1726 //requestHR(); 1727 //queryAddWorkout(userId, yr, mo, dt, exerId, equpimentId, calories, km, elev_m, time_sec, "",getHeartRate_AVG(), getHeartRate_MIN(), getHeartRate_MAX() ); 1728 } 1729 else 1730 { 1731 // null somewhere 1732 retVal = false; 1733 System.out.println("nList null?"+(nList==null)); 1734 if (nList!=null) System.out.println("nList.size="+nList.getLength() ); 1735 //System.out.println("summElement null?"+(summElement==null)); 1736 //if (summElement!=null) System.out.println("summElement="+summElement.toString()); 1737 System.out.println("nNode null?"+(nNode==null)); 1738 } 1739 //fXmlFile.close(); 1740 } 1741 catch (Exception e) 1742 { 1743 retVal = false; 1744 e.printStackTrace(); 1745 } 1746 1747 return retVal; 1748 } 1749 1750 1751 1752 /** 1753 * Go to commandline and asks for heartRate_AVG, heartRate_MAX. heartRate_MIN. 1754 * 1755 * @return true if succesful request. 1756 **/ 1757 public boolean requestHR() 1758 { 1759 boolean retVal = false; 1760 1761 java.util.Scanner scan = new java.util.Scanner(System.in); 1762 String doRead = "n"; 1763 int numAnswer = 0; 1764 1765 System.out.println("\n? ? ? ? ? ?"); 1766 System.out.print("Would you like to add heartRate values to this workout? (y/n)? "); 1767 try 1768 { 1769 doRead = scan.next(); 1770 } 1771 catch (java.util.InputMismatchException wrong) 1772 { 1773 System.out.println("\nHuh?\nLets try that again..."); 1774 System.out.print("Would you like to add heartRate values to this workout? (y/n)? "); 1775 try 1776 { 1777 scan = new java.util.Scanner(System.in); 1778 doRead = scan.next(); 1779 } 1780 catch (java.util.InputMismatchException wrong2) 1781 { 1782 // I guess not 1783 System.out.println("\n? ? ? ? ? ? \nStill do not get ya... \nProceeding without."); 1784 } 1785 } 1786 1787 if (doRead!=null && !"".equals(doRead) && 1788 ("y".equalsIgnoreCase(doRead) || 1789 "yes".equalsIgnoreCase(doRead) || 1790 "1".equalsIgnoreCase(doRead) || 1791 "ok".equalsIgnoreCase(doRead) ) 1792 ) 1793 { 1794 setHeartRate_AVG(0); 1795 setHeartRate_MIN(0); 1796 setHeartRate_MAX(0); 1797 System.out.print("Heart Rate AVG? "); 1798 try 1799 { 1800 scan = new java.util.Scanner(System.in); 1801 numAnswer = scan.nextInt(); 1802 setHeartRate_AVG(numAnswer); 1803 } 1804 catch (java.util.InputMismatchException wrong) 1805 { 1806 System.out.println("\nHuh?\nLets try that again..."); 1807 System.out.print("Heart Rate AVG? "); 1808 try 1809 { 1810 scan = new java.util.Scanner(System.in); 1811 numAnswer = scan.nextInt(); 1812 setHeartRate_AVG(numAnswer); 1813 } 1814 catch (java.util.InputMismatchException wrong2) 1815 { 1816 // I guess not 1817 System.out.println("\n? ? ? ? ? ? \nStill do not get ya... \nProceeding without it."); 1818 } 1819 } 1820 1821 System.out.print("Heart Rate MAX? "); 1822 try 1823 { 1824 scan = new java.util.Scanner(System.in); 1825 numAnswer = scan.nextInt(); 1826 setHeartRate_MAX(numAnswer); 1827 } 1828 catch (java.util.InputMismatchException wrong) 1829 { 1830 System.out.println("\nHuh?\nLets try that again..."); 1831 System.out.print("Heart Rate MAX? "); 1832 try 1833 { 1834 scan = new java.util.Scanner(System.in); 1835 numAnswer = scan.nextInt(); 1836 setHeartRate_MAX(numAnswer); 1837 } 1838 catch (java.util.InputMismatchException wrong2) 1839 { 1840 // I guess not 1841 System.out.println("\n? ? ? ? ? ? \nStill do not get ya... \nProceeding without it."); 1842 } 1843 } 1844 1845 System.out.print("Heart Rate MIN? "); 1846 try 1847 { 1848 scan = new java.util.Scanner(System.in); 1849 numAnswer = scan.nextInt(); 1850 setHeartRate_MIN(numAnswer); 1851 } 1852 catch (java.util.InputMismatchException wrong) 1853 { 1854 System.out.println("\nHuh?\nLets try that again..."); 1855 System.out.print("Heart Rate MIN? "); 1856 try 1857 { 1858 scan = new java.util.Scanner(System.in); 1859 numAnswer = scan.nextInt(); 1860 setHeartRate_MIN(numAnswer); 1861 } 1862 catch (java.util.InputMismatchException wrong2) 1863 { 1864 // I guess not 1865 System.out.println("\n? ? ? ? ? ? \nStill do not get ya... \nProceeding without it."); 1866 } 1867 } 1868 1869 } 1870 1871 return retVal; 1872 } 1873 1874 1875 private static StringBuffer createHelpMessage() 1876 { 1877 StringBuffer helpMsg = new StringBuffer(SYSTEM_LINE_SEPERATOR); 1878 helpMsg.append("---------------------------------------------------------------"); 1879 helpMsg.append(SYSTEM_LINE_SEPERATOR); 1880 helpMsg.append("---- WorkTracker Database Query Helper Class ------------------------"); 1881 helpMsg.append(SYSTEM_LINE_SEPERATOR); 1882 helpMsg.append("---------------------------------------------------------------"); 1883 helpMsg.append(SYSTEM_LINE_SEPERATOR); 1884 helpMsg.append(SYSTEM_LINE_SEPERATOR); 1885 helpMsg.append("SYNTAX: "); 1886 helpMsg.append(SYSTEM_LINE_SEPERATOR); 1887 helpMsg.append("ca.bc.webarts.tools.WorkTrackerQuery [command] [commandOptions]"); 1888 helpMsg.append(SYSTEM_LINE_SEPERATOR); 1889 helpMsg.append(SYSTEM_LINE_SEPERATOR); 1890 helpMsg.append("Available Commands:"); 1891 helpMsg.append(SYSTEM_LINE_SEPERATOR); 1892 helpMsg.append("--> sql {an sql statement}"); 1893 helpMsg.append(SYSTEM_LINE_SEPERATOR); 1894 helpMsg.append("--> sqlFile sqlFile"); 1895 helpMsg.append(SYSTEM_LINE_SEPERATOR); 1896 helpMsg.append("--> exportWorkouts userid dirname"); 1897 helpMsg.append(SYSTEM_LINE_SEPERATOR); 1898 helpMsg.append(" --> exports all the DB workouts into individual files in the dirname"); 1899 helpMsg.append(SYSTEM_LINE_SEPERATOR); 1900 helpMsg.append("--> distancegoogleString userid "); 1901 helpMsg.append(SYSTEM_LINE_SEPERATOR); 1902 helpMsg.append(" --> creates a Google Data representation of the distances data for spec'd userid"); 1903 helpMsg.append(SYSTEM_LINE_SEPERATOR); 1904 helpMsg.append("--> caloriesPieGoogleString userid "); 1905 helpMsg.append(SYSTEM_LINE_SEPERATOR); 1906 helpMsg.append(" --> creates a Google Data Pie representation of the calories data for spec'd userid"); 1907 helpMsg.append(SYSTEM_LINE_SEPERATOR); 1908 helpMsg.append("--> timePieGoogleString userid "); 1909 helpMsg.append(SYSTEM_LINE_SEPERATOR); 1910 helpMsg.append(" --> creates a Google Data Pie representation of the time data for spec'd userid"); 1911 helpMsg.append(SYSTEM_LINE_SEPERATOR); 1912 helpMsg.append(SYSTEM_LINE_SEPERATOR); 1913 helpMsg.append("Example Usage:"); 1914 helpMsg.append(SYSTEM_LINE_SEPERATOR); 1915 helpMsg.append("java ca.bc.webarts.tools.WorkTrackerQuery exercises username "); 1916 helpMsg.append(SYSTEM_LINE_SEPERATOR); 1917 helpMsg.append("---------------------------------------------------------"); 1918 helpMsg.append("----------------------"); 1919 helpMsg.append(SYSTEM_LINE_SEPERATOR); 1920 1921 return helpMsg; 1922 } 1923 1924 1925 /** 1926 * Main Entry to query the WorkTracker from the commandline. 1927 * <pre>SYNTAX: 1928 *ca.bc.webarts.tools.WorkTrackerQuery [command] [commandOptions] 1929 * 1930 * Available Commands: 1931 * --> sql {an sql statement} 1932 * --> sqlFile sqlFile 1933 * --> districts 1934 * --> dtors fiscalYears fdr1 [fdr2] [fdr3] [fdr4] ... 1935 * where fiscalYears is a comma seperated list of the years (NO SPACES) 1936 * for example: 2006,2007,2008 1937 * where The fdr values *can* have spaces for example: 1260 NVR 1261 NVR 1938 * would query for feeder_nms: '1260 NVR' and '1261 NVR' 1939 * Example Usage: 1940 * java ca.bc.webarts.tools.WorkTrackerQuery exercises userName 1941 * 1942 * </pre> 1943 **/ 1944 public static void main(String [] args) 1945 { 1946 StringBuffer helpMsg = createHelpMessage(); 1947 1948 int numCmdArgs = args.length; 1949 int numCmdStart = 1; 1950 debugOut = true; 1951 WorkTrackerQuery instance = new WorkTrackerQuery(); 1952 String xlsFilename = "wtResults-"; 1953 String separator = java.io.File.separator; 1954 1955 String command = ""; 1956 if (args != null && numCmdArgs>0) command = args[0].toLowerCase(); 1957 1958 if ( false && !instance.canConnect(instance.getUseProduction()) ) 1959 { 1960 helpMsg.append("ERROR: Cannot connect to the WT DB."); 1961 helpMsg.append(SYSTEM_LINE_SEPERATOR); 1962 helpMsg.append("---- "+(instance.getUseProduction()?"Production ":"Test")+" DB Connection "+ 1963 instance.getDbConnectString()+" ----"); 1964 System.out.println(helpMsg.toString()); 1965 } 1966 else if (args == null || numCmdArgs<1 ) 1967 { 1968 //System.out.println("Loading workoutFiles from dir: "+workoutFileDir); // 0021-E75E 1969 String workoutFileDir = separator+"media"+separator+"TGLIVESTRNG"+separator+"LS"+separator+"USERS"; //+separator+"USER2"; 1970 instance.loadFromDirectory(workoutFileDir, true); 1971 workoutFileDir = separator+"media"+separator+"NO NAME"+separator+"LS"+separator+"USERS"; //+separator+"USER2"; 1972 instance.loadFromDirectory(workoutFileDir, true); 1973 workoutFileDir = separator+"media"+separator+"0021-E75E"+separator+"LS"+separator+"USERS"; //+separator+"USER2"; 1974 instance.loadFromDirectory(workoutFileDir, true); 1975 workoutFileDir = separator+"run"+separator+"media"+separator+System.getProperty("user.name")+separator+"0021-E75E"+separator+"LS"+separator+"USERS"; //+separator+"USER2"; 1976 instance.loadFromDirectory(workoutFileDir, true); 1977 workoutFileDir = separator+"Volumes"+separator+"TGLIVESTRNG"+separator+"LS"+separator+"USERS"; //+separator+"USER2"; 1978 instance.loadFromDirectory(workoutFileDir, true); 1979 workoutFileDir = separator+"Volumes"+separator+"NO NAME"+separator+"LS"+separator+"USERS"; //+separator+"USER2"; 1980 instance.loadFromDirectory(workoutFileDir, true); 1981 workoutFileDir = "c:"+separator+"LS"+separator+"USERS"; //+separator+"USER2"; 1982 instance.loadFromDirectory(workoutFileDir, true); 1983 workoutFileDir = "d:"+separator+"LS"+separator+"USERS"; //+separator+"USER2"; 1984 instance.loadFromDirectory(workoutFileDir, true); 1985 workoutFileDir = "e:"+separator+"LS"+separator+"USERS"; //+separator+"USER2"; 1986 instance.loadFromDirectory(workoutFileDir, true); 1987 } 1988 else if (args == null || numCmdArgs<1 || 1989 (numCmdArgs>0 && 1990 ( command.toLowerCase().equals("-h") || 1991 command.toLowerCase().equals("-help") || 1992 command.toLowerCase().equals("-?") ))) 1993 { 1994 helpMsg.append("---- "+(instance.getUseProduction()?"Production ":"Test")+" DB Connection "+ 1995 instance.getDbConnectString()+" ----"); 1996 helpMsg.append(SYSTEM_LINE_SEPERATOR); 1997 System.out.println(helpMsg.toString()); 1998 numCmdArgs--; 1999 numCmdStart++; 2000 } 2001 // 1st check for some initial commandline options such as verbose or threadcount 2002 else 2003 { 2004 if (command.toLowerCase().equals("-v")) 2005 { 2006 debugOut = true; 2007 if ( numCmdArgs>1) 2008 { 2009 command = args[1].toLowerCase(); 2010 numCmdArgs--; 2011 numCmdStart++; 2012 } 2013 else 2014 { 2015 command = ""; 2016 //System.out.println(helpMsg.toString()); 2017 } 2018 } 2019 2020 2021 // *********************************************** 2022 if (command.toLowerCase().equals("sql")) 2023 { 2024 if (numCmdArgs >1) 2025 { 2026 StringBuffer sql = null; 2027 int i = numCmdStart; 2028 2029 while (i < numCmdArgs) 2030 { 2031 sql = new StringBuffer(); 2032 for (; i < numCmdArgs; i++) 2033 { 2034 //String [] splits = args[i].split(" |;"); 2035 // build the sql query until a ';' is found 2036 sql.append(args[i]); 2037 sql.append(" "); 2038 //System.out.print(args[i] + " "); 2039 if (args[i].endsWith(";")) 2040 break; 2041 } 2042 2043 System.out.print("Executing SQL query: "); 2044 System.out.println(sql.toString()); 2045 if (debugOut) System.out.print(""+(instance.getUseProduction()?"Production ":"Test")+" DB Connection "+" ("+instance.getDbUser()+":"+instance.getDbPassword()+ "): "); 2046 if (debugOut) System.out.println(instance.getDbConnectString()); 2047 StringBuffer sqlResultSet = instance.query(sql.toString(),instance.getUseProduction()); 2048 System.out.println(sqlResultSet); 2049 if(sbToDataArray(sqlResultSet)==null) 2050 { 2051 System.out.println("sbToDataArray is null? YES"); 2052 } 2053 else 2054 { 2055 System.out.println("sbToDataArray is null? no"); 2056 String [][] idArray = {{""}}; 2057 idArray = sbToDataArray(sqlResultSet); 2058 int num = idArray[0].length; 2059 System.out.println("dataArray size = "+ num + " rows of "+idArray.length + " columns" ); 2060 } 2061 i++; 2062 } 2063 } 2064 else 2065 { 2066 helpMsg.append("ERROR: The sql command requires an sql statement to follow."); 2067 System.out.println(helpMsg.toString()); 2068 } 2069 } 2070 // *********************************************** 2071 else if (command.toLowerCase().equals("sqlfile")) 2072 { 2073 if (numCmdArgs >1) 2074 { 2075 String sql = Util.readFileToString(args[1]); 2076 System.out.println("Executing SQL File query: "+ args[1]); 2077 //System.out.println(sql); 2078 if (debugOut) System.out.print("DB Connection ("+instance.getDbUser()+":"+instance.getDbPassword()+ "): "); 2079 if (debugOut) System.out.println(instance.getDbConnectString()); 2080 StringBuffer sqlResultSet = instance.query(sql.toString()); 2081 System.out.println(sqlResultSet.toString()); 2082 if(sbToDataArray(sqlResultSet)==null) 2083 { 2084 System.out.println("sbToDataArray is null? YES"); 2085 } 2086 else 2087 { 2088 System.out.println("sbToDataArray is null? no"); 2089 String [][] idArray = {{""}}; 2090 idArray = sbToDataArray(sqlResultSet); 2091 int num = idArray[0].length; 2092 System.out.println("dataArray size = "+ num + " rows of "+idArray.length + " columns" ); 2093 } 2094 //instance.sbToXLS(sqlResultSet, xlsFilename); 2095 } 2096 else 2097 { 2098 helpMsg.append("ERROR: The countFilesInDir method requires 1"); 2099 helpMsg.append(" directory name to operate."); 2100 System.out.println(helpMsg.toString()); 2101 } 2102 } 2103 // *********************************************** 2104 else if (command.toLowerCase().equals("caloriespiegooglestring")) 2105 { 2106 if (numCmdArgs == 2) 2107 { 2108 System.out.println(instance.getCaloriesPieGoogleDataStr(args[1])); 2109 } 2110 else 2111 { 2112 helpMsg.append("ERROR: The distanceGoogleString command one commandline option - userid."); 2113 System.out.println(helpMsg.toString()); 2114 } 2115 } 2116 // *********************************************** 2117 else if (command.toLowerCase().equals("timepiegooglestring")) 2118 { 2119 if (numCmdArgs == 2) 2120 { 2121 System.out.println(instance.getTimePieGoogleDataStr(args[1])); 2122 } 2123 else 2124 { 2125 helpMsg.append("ERROR: The timePieGoogleString command one commandline option - userid."); 2126 System.out.println(helpMsg.toString()); 2127 } 2128 } 2129 // *********************************************** 2130 else if (command.toLowerCase().equals("distancegooglestring")) 2131 { 2132 if (numCmdArgs == 2) 2133 { 2134 System.out.println(instance.getDistanceKmGoogleDataStr(args[1])); 2135 } 2136 else 2137 { 2138 helpMsg.append("ERROR: The distanceGoogleString command one commandline option - userid."); 2139 System.out.println(helpMsg.toString()); 2140 } 2141 } 2142 // *********************************************** 2143 else if (command.toLowerCase().equals("exportworkouts")) 2144 { 2145 if (numCmdArgs ==3) 2146 { 2147 System.out.println(instance.exportWorkouts(args[1],args[2])); 2148 } 2149 else 2150 { 2151 helpMsg.append("ERROR: The exportworkouts command two commandline option - userid and the direname for where to save files."); 2152 System.out.println(helpMsg.toString()); 2153 } 2154 } 2155 else 2156 { 2157 helpMsg.append("ERROR: Incorrect Command requested. I don't know how to '"+command+"'"); 2158 System.out.println(helpMsg.toString()); 2159 } 2160 } 2161 }// main method 2162 2163} // WorkTrackerQuery Class 2164