Hobbybrauer.de

Code: Alles auswählen

SELECT  Data1.Timestamp, 
        Data1.Angle, 
        Data1.Gravity, 
        Data1.Temperature, 
        (SELECT SUM(Data2.Angle) / COUNT(Data2.Angle) 
        FROM Data AS Data2 
        WHERE TIMESTAMPDIFF(MINUTE, Data2.Timestamp, Data1.Timestamp) BETWEEN 0 and 60 ) AS MV_Angle 
        FROM Data AS Data1 
        WHERE Data1.Timestamp >= (Select max(Data1.Timestamp) FROM Data AS Data1 WHERE Data1.ResetFlag = 1);

Code: Alles auswählen

function getChartValuesPlato4_ma($conn, $iSpindleID='iSpindel000', $timeFrameHours=defaultTimePeriod, $reset=defaultReset)
{
    $isCalibrated = 0;  // is there a calbration record for this iSpindle?
    $valAngle = '';
    $valTemperature = '';
    $valDens = '';
    $const1 = 0;
    $const2 = 0;
    $const3 = 0;
    $where_ma='';
   if ($reset)
   {
   	$where="WHERE Data1.Name = '".$iSpindleID."' 
            AND Data1.Timestamp >= (Select max(Timestamp) FROM Data  WHERE ResetFlag = true AND Name = '".$iSpindleID."')";
	$where_ma="Data2.Timestamp >= (Select max(Data2.Timestamp) FROM Data AS Data2  WHERE Data2.ResetFlag = true) AND";
   }  
   else
   {
   	$where ="WHERE Data1.Name = '".$iSpindleID."' 
            AND Data1.Timestamp >= date_sub(NOW(), INTERVAL ".$timeFrameHours." HOUR) 
            AND Data1.Timestamp <= NOW()";
   }  
   $q_sql = mysqli_query($conn, "SELECT UNIX_TIMESTAMP(Data1.Timestamp) as unixtime, Data1.temperature, Data1.angle,
                           (SELECT SUM(Data2.Angle) / COUNT(Data2.Angle)
							FROM Data AS Data2
                            WHERE "
				.$where_ma
				." TIMESTAMPDIFF(MINUTE, Data2.Timestamp, Data1.Timestamp) BETWEEN 0 and 120 ) AS mv_angle
						    FROM Data AS Data1 " 
                           .$where 
                          ." ORDER BY Data1.Timestamp ASC") or die(mysqli_error($conn));
                     
    // retrieve number of rows
    $rows = mysqli_num_rows($q_sql);
    if ($rows > 0)
    {
     // get unique hardware ID for calibration
     $u_sql = mysqli_query($conn, "SELECT ID FROM Data WHERE Name = '".$iSpindleID."' ORDER BY Timestamp DESC LIMIT 1") or die(mysqli_error($conn));
     $rowsID = mysqli_num_rows($u_sql);
     if ($rowsID > 0)
     {
        // try to get calibration for iSpindle hardware ID
        $r_id = mysqli_fetch_array($u_sql);
        $uniqueID = $r_id['ID'];
        $f_sql = mysqli_query($conn, "SELECT const1, const2, const3 FROM Calibration WHERE ID = '$uniqueID' ") or die(mysqli_error($conn));
        $rows_cal = mysqli_num_rows($f_sql);
        if ($rows_cal > 0)
        {
            $isCalibrated = 1;
            $r_cal = mysqli_fetch_array($f_sql);
            $const1 = $r_cal['const1'];
            $const2 = $r_cal['const2'];
            $const3 = $r_cal['const3'];
        }
     }
     // retrieve and store the values as CSV lists for HighCharts
     while($r_row = mysqli_fetch_array($q_sql))
     {
         $jsTime = $r_row['unixtime'] * 1000;
         $angle = $r_row['mv_angle'];
         $dens = $const1 * pow($angle, 2) + $const2 * $angle + $const3;   // complete polynome from database
                         
         $valAngle         .= '['.$jsTime.', '.$angle.'],';
         $valDens          .= '['.$jsTime.', '.$dens.'],';
         $valTemperature   .= '['.$jsTime.', '.$r_row['temperature'].'],';
     }
     // remove last comma from each CSV
     $valAngle         = delLastChar($valAngle);
     $valTemperature   = delLastChar($valTemperature);
     return array($isCalibrated, $valDens, $valTemperature, $valAngle);
    }
 }