File "data_insert_t.php"
Full path: /www/db_bk/bk/2/data_insert_t.php
File size: 9.21 KiB (9427 bytes)
MIME-type: text/x-php
Charset: utf-8
<?php
$control_no = 1;
$DATA_TABLE = "wave_height.data_01_log";
$DATA_TABLE2 = "wave_height.data_01_tmp_200";
$DATA_TABLE3 = "wave_height.data_01_tmp_400";
$DATA_TABLE4 = "wave_height.data_01_tmp_600";
$DATA_TABLE2_2 = "wave_height.data_01_tmp_200_2";
$DATA_TABLE3_2 = "wave_height.data_01_tmp_400_2";
$DATA_TABLE4_2 = "wave_height.data_01_tmp_600_2";
$TEST_TABLE = "wave_height.data_01_tmp_t";
$REGIST_TABLE = "wave_height.data_01_cal";
$HOST = "192.168.100.153";
$DB = "arduino";
$USER = "postgres";
$PASS = "postgres";
$cf_path = "/www/arduino/2/spec_calc";
$file_out_1 = $cf_path ."/200/acc_200.txt";
$file_out_2 = $cf_path ."/400/acc_400.txt";
$file_out_3 = $cf_path ."/600/acc_600.txt";
$spec_calc_1 = $cf_path ."/200";
$spec_calc_2 = $cf_path ."/400";
$spec_calc_3 = $cf_path ."/600";
$file_input_1 = $cf_path ."/200/spec_200.txt";
$file_input_2 = $cf_path ."/400/spec_400.txt";
$file_input_3 = $cf_path ."/600/spec_600.txt";
//================================================
// データベース接続
// 備考 ：
// 更新 ： 2013/03/12 KMK-Yokosuka
//================================================
$conn = pg_connect("
host = ". $HOST. "
dbname = ". $DB. "
user = ". $USER. "
password= ". $PASS. "
");
//■計算関数
function spectrum_calc($conn,$Tb1,$Tb2,$OutFile,$CalcPath,$InputFile,$control_no,$data_dist,$now_datetime,$RegistTable)
{
//$conn データベースの接続先
//$Tb1 計算用テーブル名1
//$Tb2 計算用テーブル名2
//$OutFile 計算結果の出力ファイル名
//$CalcPath 計算プログラムのパス名
//$InputFile 計算プログラムから出力されたファイル名(読み込ませる)
//$control_no 機器No(数台同時に動作させる場合の識別番号:デフォルトは1)
//$data_dist 使用する計測データの間隔(200ms,400ms,600ms)
//$now_datatime 登録する日付時刻
//$RegistTable 全ての計算結果を登録するテーブル名
//①各テーブルごとにデータの平均値を計算
$sql_avg = "SELECT avg((ax/100)*9.8) FROM " .$Tb1;
$res_avg = pg_query($conn,$sql_avg)or die("SQL ERROR!!<br />". $sql_avg.pg_last_error());
$row_avg = pg_fetch_array($res_avg,0);
$avg_ = $row_avg[0];
//②各テーブルごとにデータ-平均の計算を行う
$sql_acc = "SELECT ax-(" .$avg_ .") FROM " .$Tb1 ." order by time";
$res_acc = pg_query($conn,$sql_acc)or die("SQL ERROR!!<br />". $sql_acc.pg_last_error());
$acc_ct = pg_num_rows($res_acc);
//③ファイル出力処理
$fp = fopen($OutFile ,"w");
for($i = 0;$i < $acc_ct;$i++)
{
$row = pg_fetch_array($res_acc,$i);
fwrite($fp,$row[0] ."\n");
}
fclose($fp);
//---------------------------------------------------------------------------------------
//スペクトラル解析の計算(外部コマンド実行)
// chdir($CalcPath);
// exec("./spec_calc.exe");
//スペクトル解析の結果のファイルを読み込む
$file_read = fopen($InputFile, "r");
//読み込んだファイルを配列へ格納する
if( $file_read )
{
while (($data = fgetcsv($file_read, 0, ",")) !== FALSE)
{
$csv[] = $data;
}
}
fclose($file_read);
$ct = count($csv);
$del = "DELETE FROM " .$Tb2;
$del_res = pg_query($conn,$del)or die("SQL ERROR!!<br />". $del.pg_last_error());
//読み込んだファイルを元に計算を行う
for($ii = 0;$ii< $ct;$ii++)
{
// $csv[$ii][0] = $csv[$ii][0]+0;
// $csv[$ii][1] = $csv[$ii][1]+0;
// fwrite($fp,$csv[$ii][0]+0 ."," .$csv[$ii][1] ."\n");
// print $csv[$ii][0] ."_______" .$csv[$ii][1] ."\n";
$f = $csv[$ii][0]+0;
$buoy_acc = $csv[$ii][1]+0;
$co_eff = 1/(1216.6 * pow($f,3.9181));
if($ii+1 != $ct)
{
$buoy_dis = $buoy_acc * $co_eff;
$m0 = $buoy_dis * ($csv[$ii+1][0] - $csv[$ii][0]);
$m1 = ($buoy_dis/$csv[$ii][0]) * ($csv[$ii+1][0] - $csv[$ii][0]);
}
else
{
$buoy_dis = $buoy_acc * $co_eff;
$m0 = $buoy_dis * (0 - $csv[$ii][0]);
$m1 = ($buoy_dis/$csv[$ii][0]) * (0 - $csv[$ii][0]);
}
//計算用テーブルへデータを追加
$sql_ins = "INSERT INTO " .$Tb2 ." (f,buoy_acc,co_eff,buoy_dis,m0,m1) ";
$sql_ins .= "VALUES (" .$f ."," .$buoy_acc ."," .$co_eff ."," .$buoy_dis ."," .$m0 ."," .$m1 .");";
$result = pg_query($conn,$sql_ins)or die("SQL ERROR!!<br />". $sql_ins.pg_last_error());
}
//---------------------------------------------------------------------------------------
//Σの計算を行う (x=Σm0,y=Σm1)
//①各テーブルごとにデータの平均値を計算
$sql_xy = "SELECT sum(m0) as x,sum(m1) as y FROM " .$Tb2 ." WHERE f > 0.1 AND f < 0.3;";
$res_xy = pg_query($conn,$sql_xy)or die("SQL ERROR!!<br />". $sql_xy.pg_last_error());
$row_xy = pg_fetch_array($res_xy,0);
$x_ = $row_xy[0];
$y_ = $row_xy[1];
$h_ = 4.004 * sqrt($row_xy[0]);
$t_ = $y_ / $x_;
//計算用テーブルへデータを追加
$sql_reg = "INSERT INTO " .$RegistTable ." (datetime,control_no,dt,x,y,h,t) ";
$sql_reg .= "VALUES ('".$now_datetime ."'," .$control_no ."," .$data_dist ."," .$x_ ."," .$y_ ."," .$h_ ."," .$t_ .");";
$result = pg_query($conn,$sql_reg)or die("SQL ERROR!!<br />". $sql_reg.pg_last_error());
}
$now_time = time();
$now_datetime = date("Y-m-d H:i:0" ,$now_time -1200);
$date_ = date("Y-m-d" ,$now_time -1200);
$time_start = date("H:i:0" ,$now_time - 1200);
$time_end = date("H:i:0" ,$now_time);
$time_flag = date("Hi" ,$now_time);
//計算用テーブルへ追加するデータを抽出
if($time_flag != 0)
{
$sql = "SELECT datetime,date,time,ax,ay,az FROM " .$DATA_TABLE ." WHERE " ."date ='" .$date_ ."' AND time >= '" .$time_start ."' AND time < '" .$time_end ."' order by time";
}
else
{
$sql = "SELECT datetime,date,time,ax,ay,az FROM " .$DATA_TABLE ." WHERE " ."date = '" .$date_ ."' AND time >= '" .$time_start ."' order by time";
}
//対象のデータが存在するかチェック(データ数を取得)
$result_c = pg_query($conn,$sql)or die("SQL ERROR!!<br />". $sql.pg_last_error());
$data_count = pg_num_rows($result_c);
//対象のデータが存在する場合のみ処理
if($data_count != 0)
{
//計算用テーブルを初期化
$del = "DELETE FROM " .$DATA_TABLE2;
$del_res = pg_query($conn,$del)or die("SQL ERROR!!<br />". $del.pg_last_error());
$del = "DELETE FROM " .$DATA_TABLE3;
$del_res = pg_query($conn,$del)or die("SQL ERROR!!<br />". $del.pg_last_error());
$del = "DELETE FROM " .$DATA_TABLE4;
$del_res = pg_query($conn,$del)or die("SQL ERROR!!<br />". $del.pg_last_error());
$del = "DELETE FROM " .$DATA_TABLE2_2;
$del_res = pg_query($conn,$del)or die("SQL ERROR!!<br />". $del.pg_last_error());
$del = "DELETE FROM " .$DATA_TABLE3_2;
$del_res = pg_query($conn,$del)or die("SQL ERROR!!<br />". $del.pg_last_error());
$del = "DELETE FROM " .$DATA_TABLE4_2;
$del_res = pg_query($conn,$del)or die("SQL ERROR!!<br />". $del.pg_last_error());
//計算用テーブルへデータを追加
$sql_ins = "INSERT INTO " .$DATA_TABLE2 ." (datetime, date, time, ax, ay, az) ";
$sql_ins .= $sql;
$result1 = pg_query($conn,$sql_ins)or die("SQL ERROR!!<br />". $sql_ins.pg_last_error());
//計算用テーブルから400ms毎､600ms毎のデータを抽出しテーブルに追加
$sql_cal = "SELECT * FROM " .$DATA_TABLE2 ." order by time";
$cal_res = pg_query($conn,$sql_cal)or die("SQL ERROR!!<br />". $del.pg_last_error());
$data_ct = pg_num_rows($cal_res);
for($i = 0;$i < $data_ct;$i++)
{
$row = pg_fetch_array($cal_res,$i);
if($i == 0)
{
//計算用テーブルへデータを追加(400ms,600ms)
$sql_ins2 = "INSERT INTO " .$DATA_TABLE3 ." (datetime, date, time, ax, ay, az) ";
$sql_ins2 .= "VALUES ('" .$row[0] ."','" .$row[1] ."','" .$row[2] ."'," .$row[3] ."," .$row[4] ."," .$row[5] .");";
$result2 = pg_query($conn,$sql_ins2)or die("SQL ERROR!!<br />". $sql_ins2.pg_last_error());
$sql_ins3 = "INSERT INTO " .$DATA_TABLE4 ." (datetime, date, time, ax, ay, az) ";
$sql_ins3 .= "VALUES ('" .$row[0] ."','" .$row[1] ."','" .$row[2] ."'," .$row[3] ."," .$row[4] ."," .$row[5] .");";
$result3 = pg_query($conn,$sql_ins3)or die("SQL ERROR!!<br />". $sql_ins3.pg_last_error());
}
else
{
if(($i%2) == 0)
{
$sql_ins2 = "INSERT INTO " .$DATA_TABLE3 ." (datetime, date, time, ax, ay, az) ";
$sql_ins2 .= "VALUES ('" .$row[0] ."','" .$row[1] ."','" .$row[2] ."'," .$row[3] ."," .$row[4] ."," .$row[5] .");";
$result2 = pg_query($conn,$sql_ins2)or die("SQL ERROR!!<br />". $sql_ins2.pg_last_error());
}
if(($i%3) == 0)
{
$sql_ins3 = "INSERT INTO " .$DATA_TABLE4 ." (datetime, date, time, ax, ay, az) ";
$sql_ins3 .= "VALUES ('" .$row[0] ."','" .$row[1] ."','" .$row[2] ."'," .$row[3] ."," .$row[4] ."," .$row[5] .");";
$result3 = pg_query($conn,$sql_ins3)or die("SQL ERROR!!<br />". $sql_ins3.pg_last_error());
}
}
}
//■計算処理
//spectrum_calc($conn,$DATA_TABLE2,$TEST_TABLE,$file_out_1,$spec_calc_1,$file_input_1,$control_no,0.2,$now_datetime,$REGIST_TABLE);
spectrum_calc($conn,$DATA_TABLE2,$DATA_TABLE2_2,$file_out_1,$spec_calc_1,$file_input_1,$control_no,0.2,$now_datetime,$REGIST_TABLE);
spectrum_calc($conn,$DATA_TABLE3,$DATA_TABLE3_2,$file_out_2,$spec_calc_2,$file_input_2,$control_no,0.4,$now_datetime,$REGIST_TABLE);
spectrum_calc($conn,$DATA_TABLE4,$DATA_TABLE3_2,$file_out_3,$spec_calc_3,$file_input_3,$control_no,0.6,$now_datetime,$REGIST_TABLE);
}
pg_close($conn);
?>