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