Welcome to the Cumulus Support forum.

Latest Cumulus MX release v3.3.0 build 3063 - 10 January 2020.
    Legacy Cumulus 1 release v1.9.4 (build 1099) - 28 November 2014

    See the Wiki for downloading either version.

    Question aboute MySQL and WU-Graphs

    Other discussion about creating web sites for Cumulus that doesn't have a specific subforum

    Moderator: daj

    User avatar
    steve
    Cumulus Author
    Posts: 26717
    Joined: Mon 02 Jun 2008 6:49 pm
    Weather Station: None
    Operating System: None
    Location: Vienne, France
    Contact:

    Re: Question aboute MySQL and WU-Graphs

    Post by steve » Sat 13 Apr 2013 7:52 am

    gemini06720 wrote:OK, I have waited a few days t reply to your message ... so I could cool off somewhat.

    I really hate when you take such a defensive attitude
    What I really hate is when people read things into my posts that aren't there.
    Steve

    User avatar
    duke
    Posts: 840
    Joined: Sun 03 Jul 2011 6:23 pm
    Weather Station: Davis VP2 Plus - 24hr FARS
    Operating System: Windows 10 - 64 bit
    Location: Eastleigh, Hampshire, UK
    Contact:

    Re: Question aboute MySQL and WU-Graphs

    Post by duke » Sat 13 Apr 2013 9:25 am

    steve wrote:
    BCJKiwi wrote:@Steve - the WUG-settings.php file has the clue to the WU/WD/SQL variants here;

    Code: Select all

    $dataSource = 'wunderground'; // 'mysql' - weather display mysql database ; 'wunderground' - wunderground server ; 'wutowdmysql' - combined WU->WD MySQL
    That seems to be saying WU or WD MySQL or a combination of both; no mention of WD files.
    From 'configurator.php'
    $hourGraphs = "'.$_POST['hourGraphs'].'";
    $clientRawHpath = "'.rmdq(eslash($_POST['clientRawHpath'])).'";
    Lines 273/274. 'clientraw' is required to enable the hour graphs option if not using MySQL.

    And (configurator.php)
    You do not have the required permissions to view the files attached to this post.
    Duke

    Nightingale Weather
    Eastleigh, Hampshire, UK
    ------------------------------------------------------
    Validate your pages - often!

    User avatar
    N0BGS
    Posts: 200
    Joined: Sat 10 Nov 2012 2:26 am
    Weather Station: Davis Vantage Pro 2
    Operating System: Win7vm,VMWare ESXi 6.5
    Location: Hermon, Maine USA
    Contact:

    Re: Question aboute MySQL and WU-Graphs

    Post by N0BGS » Sat 15 Jun 2013 4:37 am

    Or, you could try this:

    Code: Select all

    <?php
    /**
     * Project:   WU GRAPHS
     * Module:    wdmysql-h.php 
     * Copyright: (C) 2010 Radomir Luza
     * Email: luzar(a-t)post(d-o-t)cz
     * WeatherWeb: http://pocasi.hovnet.cz
     * Cumulus V 1.1 Modified for use with Cumulus by Kurt Wesseling, 6/15/2013
     * using the MySQL 'Realtime' table created by Mark Crossley's ImportCumulusFile,
     * http://wiki.sandaysoft.com/a/ImportCumulusFile
     * Note: Original script solar UV values have NOT been changed, and WILL NOT work properly unless they are.
     */
    ################################################################################
    # This program is free software; you can redistribute it and/or
    # modify it under the terms of the GNU General Public License
    # as published by the Free Software Foundation; either version 3
    # of the License, or (at your option) any later version.
    #
    # This program is distributed in the hope that it will be useful,
    # but WITHOUT ANY WARRANTY; without even the implied warranty of
    # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    # GNU General Public License for more details.
    #
    # You should have received a copy of the GNU General Public License
    # along with this program. If not, see <http://www.gnu.org/licenses/>. 
    ################################################################################
    
    //$debug = true;
    if ($debug) {
      $stimer = explode(' ', microtime());
      $stimer = $stimer[1] + $stimer[0];
    }
    
    // connect to db
    mysql_connect($dbhost, $dbuser, $dbpass) or die("Unable to connect");
    mysql_select_db($dbname) or die("Unable to select database");
    
    // LOAD/RECALCULATE FROM DB
    
    // conversion ratios 
    $r_wind = $db_wind == 'kts' && $metric ? 1.851 : 1; // kts to kmh
    $r_wind = $db_wind == 'mph' && $metric ? 1.61 : $r_wind; // mph to kmh
    $r_wind = $db_wind == 'kts' && !$metric ? 1.15 : $r_wind; // kts to mph
    $r_wind = $db_wind == 'kmh' && !$metric ? 0.62 : $r_wind; // kmh to mph
    $r_rain = $db_rain == 'mm' && !$metric ? 1/25.4 : 1;  // mm to inch
    $r_rain = $db_rain == 'inch' && $metric ? 25.4 : $r_rain;  // inch to mm
    $r_baro = $db_baro == 'hPa' && !$metric ? 0.0295 : 1; // hPa to inHg
    $r_baro = $db_baro == 'inHg' && $metric ? 33.863 : $r_baro; // inHg to hPa
    $r_rate = $db_rate == 'mm' && !$metric ? 1/25.4 : 1;  // mm to inch
    $r_rate = $db_rate == 'inch' && $metric ? 25.4 : $r_rate;  // inch to mm
    
    function convcf ($cval) { // convert Celsius to Farenheit
      global $metric, $db_temp;
      if ($db_temp == 'C' && !$metric) {
        return ($cval * 9) / 5 + 32; // C to F
      } elseif ($db_temp == 'F' && $metric) {
        return ($cval - 32) / 9 * 5; // F to C
      } else {
        return $cval;
      }
    }
    
    $start = date('Y-m-d H:i', time()-60*60).':00';
    $end = date('Y-m-d H:i').':00';
    
      // used values
      //Select columns which be added to query (lower column count = better performance/speed)
        $thisPag = substr(substr($_SERVER["SCRIPT_NAME"],strrpos($_SERVER["SCRIPT_NAME"],"/")+1),5,-5);
        switch ($thisPag) {
            case 'h1':
            case 'd1':
                $datalist = array('temp','intemp','dew');
                break;
            case 'm1':
            case 'y1':      
                $datalist = array('temp','intemp');
                break;
            case 'h3':
            case 'd3':
            case 'm3':
            case 'y3':
                $datalist = array('hum');
                break;
            case 'h5':
            case 'd5':
            case 'm4':
            case 'y4':
                $datalist = array('press');
                break;
            case 'd4':
            case 'h4':
                $datalist = array('wgust','wspeed');
                break;
            case 'm5':
            case 'y5':
                $datalist = array('wgust','wspeed','bearing');
                break;
            case 'd2':
            case 'h2':
                $datalist = array('bearing');
                break;
            case 'm6':
            case 'y6':
                $datalist = array('rfall');
                break;
            case 'h6':
            case 'd6':
            case 'm7':
            case 'y7':
                $datalist = array('actual_solar_reading', 'davis_vp_uv', 'current_weather_desc');
                break;
            case 'h7':
            case 'd7':
                $datalist = array('rfall', 'rrate');
                break;
            case 'm2':
            case 'y2':
                $datalist = array('dew');
        }
        //$datalist = array('temp', 'intemp', 'dew', 'baro', 'bearing', 'wspeed', 'wgust', 'hum', 'rrate', 'rfall', 'actual_solar_reading', 'current_weather_desc', 'davis_vp_uv');
    foreach ($datalist as $val) {
      $datacol .= $val.", ";
    }
    $datacol = substr($datacol,0,-2);
    
    
    switch ($datetime_col) {
        case 'yes':
            $que = "SELECT LogDateTime, $datacol FROM $dbtable WHERE LogDateTime BETWEEN '$start' AND '$end' ORDER BY LogDateTime ASC";
            break;
        case 'no':
            $que = "SELECT LogDateTime, $datacol FROM(SELECT CONCAT(date,' ',time)as LogDateTime, $datacol from $  dbtable) as temp WHERE LogDateTime BETWEEN '$start' AND '$end' ORDER BY LogDateTime ASC";            
    }
    
    $sql = mysql_query($que);
    if (!$sql) {
      echo "mysql query error: ".mysql_error().'<br>';
      $db_caching = false;
    }
    
    date_default_timezone_set('UTC'); // Set timezone offset to 0
    
    //Proceed data
    while ($row = mysql_fetch_array($sql, MYSQL_ASSOC)) {
      $dTimeArray .= (strtotime($row['LogDateTime'])*1000).',';
      $dTemp .= convcf($row['temp']).',';
      $dDP .= convcf($row['dew']).',';
      $dBaro .= round($row['press']*$r_baro, 2).',';
      $dWindDir .= $row['bearing'].',';
      $dAvgWS .= round($row['wspeed']*$r_wind*$windcon, 1).',';
      $dGustWS .= round($row['wgust']*$r_wind*$windcon, 1).',';
      $dHum .= $row['hum'].',';
      $dRainSpd .= round($row['rrate']*$r_rate, 2).',';
      $dRainT .= round($row['rfall']*$r_rain, 2).',';
      $dSolar .= $row['actual_solar_reading'].',';
      $dCond .= '"'.str_replace('_',' ',$row['current_weather_desc']).'",';
      $dUV .= $row['davis_vp_uv'].',';  
      $dIndTemp .= convcf($row['intemp']).',';  
    }
    
    date_default_timezone_set($TZconf); // Back to configured timezone
    
    if (mysql_affected_rows() < 2) { // we need at least two values for drawing a line in graph.
      $emptyGraph = true; 
    }
    
    // metric/imperial units conversion for spike correcting
    $baroSpike = $metric ? 1  : 33.86;
    $tempSpike = $metric ? 1  : 1.8 ;
    $rainSpike = $metric ? 1  : 2.54;
    $windSpike = $metric ? 1  : 0.6214;
    
    $outData = 'var dTemp    = ['.rmSpike(substr($dTemp, 0, -1), $dsp['temp']*$tempSpike).'];
    var dDP      = ['.rmSpike(substr($dDP, 0, -1), $dsp['temp']*$tempSpike).'];
    var dBaro    = ['.rmSpike(substr($dBaro, 0, -1), $dsp['baro']/$baroSpike).'];
    var dWindDir = ['.substr($dWindDir, 0, -1).'];
    var dAvgWS   = ['.substr($dAvgWS, 0, -1).'];
    var dGustWS  = ['.substr($dGustWS, 0, -1).'];
    var dHum     = ['.rmSpike(substr($dHum, 0, -1), $dsp['humi']).'];
    var dRainSpd = ['.rmSpike(substr($dRainSpd, 0, -1), $dsp['rrate']/$rainSpike).'];
    var dRainT   = ['.rmSpike(substr($dRainT, 0, -1), $dsp['rain_total']/$rainSpike).'];
    var dSolar   = ['.substr($dSolar, 0, -1).'];
    var dCond    = ['.substr($dCond, 0, -1).'];
    var dUV      = ['.substr($dUV, 0, -1).'];
    var dIndTemp    = ['.rmSpike(substr($dIndTemp, 0, -1), $dsp['temp']*$tempSpike).'];
    var timeArray = ['.substr($dTimeArray, 0, -1).'];';
    
    if (strlen($outData) < 242) { // empty data detection for cached value
      $emptyGraph = true;
    }
    
    $JSdata = $outData.'
    
    // Function for creating graph array
    function comArr(unitsArray) { 
        var outarr = [];
        for (var i = 0; i < timeArray.length; i++) {
         outarr[i] = [timeArray[i], unitsArray[i]];
        }
      return outarr;
    }  
    ';
    // zero before digits
    function zero_bd ($value) {
      if ($value <= 9) {
        return "0".$value;
      } else {
        return $value;
      }
    }
    // Remove spikes
    function rmSpike ($data, $diff, $maxBadVal = 3) {
      global $removeSpikes;
      if ($removeSpikes) {
          $array = explode(",", $data);
          $c = 0;
          $Ncorr = 0; // after $maxBadVal times is bad value ignored, so $maxBadVal is maximum nr. of bad values 
          foreach ($array as $value) {
            if (!isset($lv1)) {
              $lv1 = $value;
              $c++;
              continue;
            } 
            if ( abs($value-$lv1) > $diff and $Ncorr <= $maxBadVal ) { //    
              $array[$c] = $lv1; // replaces the current value of the last one
              $Ncorr++;
            } else {
              $lv1 = $value;
              $Ncorr = 0;
            }
            $c++;
          }
          return implode(",",$array);
      } else {
        return $data;
      }
    }
    
    if ($debug) {
      $etimer = explode(' ', microtime());
      $etimer = $etimer[1] + $etimer[0];
      printf("Script timer: <b>%f</b> seconds.", ($etimer - $stimer));
    } 
    ?>
    You can see it here: http://cheesewhiz.dyndns.org/wxwugraphs.php

    After following Radomir's original directions, replace his wdmysql-h.php with this one.

    Works with the ImportCumulusFile Mark Crossley did, but does not update automatically; i.e. not in real time.

    Working for me, but will almost certainly require modification by others since I am not a coder.

    If there is any reason I should not have posted this, please let me know.

    --Kurt
    Blitzortung Station 1809

    Post Reply