File "data_insert_t.php"
Full path: /www/arduino/2/OLD/data_insert_t.php
File size: 12.78 KiB (13087 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";
$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";
//$Tb1 計算用テーブル名1
//$Tb2 計算用テーブル名2
//$OutFile 計算結果の出力ファイル名
//$CalcPath 計算プログラムのパス名
//$InputFile 計算プログラムから出力されたファイル名(読み込ませる)
function spectrum_calc($Tb1,$Tb2,$OutFile,$CalcPath,$InputFile)
{
//①各テーブルごとにデータの平均値を計算
$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);
//読み込んだファイルを元に計算を行う
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());
}
}
//================================================
// データベース接続
// 備考 ：
// 更新 ： 2013/03/12 KMK-Yokosuka
//================================================
$conn = pg_connect("
host = ". $HOST. "
dbname = ". $DB. "
user = ". $USER. "
password= ". $PASS. "
");
/*
$now_time = time();
$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());
}
}
}
*/
//-------------------------------------------------------------------------------------------------------------------------------------------
/*
//■■各テーブルからデータを計算してファイル出力
//①各テーブルごとにデータの平均値を計算
//200m毎(平均)
$sql_avg = "SELECT avg((ax/100)*9.8) FROM " .$DATA_TABLE2;
$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];
//400ms毎(平均)
$sql_avg2 = "SELECT avg((ax/100)*9.8) FROM " .$DATA_TABLE3;
$res_avg2 = pg_query($conn,$sql_avg2)or die("SQL ERROR!!<br />". $sql_avg2.pg_last_error());
$row_avg2 = pg_fetch_array($res_avg2,0);
$avg2_ = $row_avg2[0];
//600ms毎(平均)
$sql_avg3 = "SELECT avg((ax/100)*9.8) FROM " .$DATA_TABLE4;
$res_avg3 = pg_query($conn,$sql_avg3)or die("SQL ERROR!!<br />". $sql_avg3.pg_last_error());
$row_avg3 = pg_fetch_array($res_avg3,0);
$avg3_ = $row_avg3[0];
//②各テーブルごとにデータ-平均の計算を行う
//200ms毎
$sql_acc = "SELECT ax-(" .$avg_ .") FROM " .$DATA_TABLE2 ." 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);
//200ms毎
$sql_acc2 = "SELECT ax-(" .$avg2_ .") FROM " .$DATA_TABLE3 ." order by time";
$res_acc2 = pg_query($conn,$sql_acc2)or die("SQL ERROR!!<br />". $sql_acc2.pg_last_error());
$acc2_ct = pg_num_rows($res_acc2);
//200ms毎
$sql_acc3 = "SELECT ax-(" .$avg3_ .") FROM " .$DATA_TABLE4 ." order by time";
$res_acc3 = pg_query($conn,$sql_acc3)or die("SQL ERROR!!<br />". $sql_acc3.pg_last_error());
$acc3_ct = pg_num_rows($res_acc3);
//③ファイル出力処理
$fp1 = fopen($file_out_1 ,"w");
$fp2 = fopen($file_out_2 ,"w");
$fp3 = fopen($file_out_3 ,"w");
print $acc_ct ."\n";
print $acc2_ct ."\n";
print $acc3_ct ."\n";
for($i = 0;$i < $acc_ct;$i++)
{
$row = pg_fetch_array($res_acc,$i);
fwrite($fp1,$row[0] ."\n");
}
for($i = 0;$i < $acc2_ct;$i++)
{
$row2 = pg_fetch_array($res_acc2,$i);
fwrite($fp2,$row2[0] ."\n");
}
for($i = 0;$i < $acc3_ct;$i++)
{
$row3 = pg_fetch_array($res_acc3,$i);
fwrite($fp3,$row3[0] ."\n");
}
fclose($fp1);
fclose($fp2);
fclose($fp3);
*/
//---------------------------------------------------------------------------------------------------------------------------
//スペクトラル解析の計算(外部コマンド実行)
/*
chdir($spec_calc_1);
exec("./spec_calc.exe");
chdir($spec_calc_2);
exec("./spec_calc.exe");
chdir($spec_calc_3);
exec("./spec_calc.exe");
*/
//スペクトル解析の結果のファイルを読み込む
$file_read_1 = fopen($file_input_1, "r");
$file_read_2 = fopen($file_input_2, "r");
$file_read_3 = fopen($file_input_3, "r");
//CSVファイルを配列へ(200ms)
if( $file_read_1 )
{
while (($data = fgetcsv($file_read_1, 0, ",")) !== FALSE)
{
$csv_1[] = $data;
}
}
fclose($file_read_1);
$ct1 = count($csv_1);
//CSVファイルを配列へ(400ms)
if( $file_read_2 )
{
while (($data = fgetcsv($file_read_2, 0, ",")) !== FALSE)
{
$csv_2[] = $data;
}
}
fclose($file_read_2);
$ct2 = count($csv_2);
//CSVファイルを配列へ(600ms)
if( $file_read_3 )
{
while (($data = fgetcsv($file_read_3, 0, ",")) !== FALSE)
{
$csv_3[] = $data;
}
}
fclose($file_read_3);
$ct3 = count($csv_3);
//計算用テーブルへデータを追加
//$sql_ins = "INSERT INTO " .$DATA_TABLE2_2 ." (f,bouy_acc,co_eff,buoy_dis,m0,m1) ";
//$sql_ins .= "VALUES ('" .$row[0] ."','" .$row[1] ."','" .$row[2] ."'," .$row[3] ."," .$row[4] ."," .$row[5] .");";
//$result = pg_query($conn,$sql_ins)or die("SQL ERROR!!<br />". $sql_ins.pg_last_error());
$del = "DELETE FROM " .$DATA_TABLE2_2;
$del_res = pg_query($conn,$del)or die("SQL ERROR!!<br />". $del.pg_last_error());
for($ii = 0;$ii< $ct1;$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_1[$ii][0]+0;
$buoy_acc = $csv_1[$ii][1]+0;
$co_eff = 1/(1216.6 * pow($f,3.9181));
if($ii+1 != $ct1)
{
$buoy_dis = $buoy_acc * $co_eff;
$m0 = $buoy_dis * ($csv_1[$ii+1][0] - $csv_1[$ii][0]);
$m1 = ($buoy_dis/$csv_1[$ii][0]) * ($csv_1[$ii+1][0] - $csv_1[$ii][0]);
}
else
{
$buoy_dis = $buoy_acc * $co_eff;
$m0 = $buoy_dis * (0 - $csv_1[$ii][0]);
$m1 = ($buoy_dis/$csv_1[$ii][0]) * (0 - $csv_1[$ii][0]);
}
//計算用テーブルへデータを追加
$sql_ins = "INSERT INTO " .$DATA_TABLE2_2 ." (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());
}
/*
//計算用テーブルへデータを追加
$sql_ins = "INSERT INTO " .$DATA_TABLE2_2 ." (f,bouy_acc,co_eff,buoy_dis,m0,m1) ";
$sql_ins .= "VALUES ('" .$row[0] ."','" .$row[1] ."','" .$row[2] ."'," .$row[3] ."," .$row[4] ."," .$row[5] .");";
$result = pg_query($conn,$sql_ins)or die("SQL ERROR!!<br />". $sql_ins.pg_last_error());
//計算用テーブルへデータを追加(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());
*/
//}
pg_close($conn);
?>