-
Notifications
You must be signed in to change notification settings - Fork 2
/
mayamysql-importer.php
125 lines (96 loc) · 4.15 KB
/
mayamysql-importer.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
<?php
// This script updates the viu hydromet mysql data base tables for just mt maya, pulls last 12 hours from ftp and updates table
require 'maya_ftp_config.php';
require 'functions.php';
require 'config.php';
# number of rows to grab from tail
define("NUMROWS", 720);
$tbl = "mountmaya";
$numToClean = 720; // was 12 hr
$ftpFilename = "ftps://".FTPUSER.":".FTPPASS."@".FTPHOST;
$conn = mysqli_connect(MYSQLHOST, MYSQLUSER, MYSQLPASS, MYSQLDB);
$ftpStreamOptions = array(
"ssl"=>array(
"verify_peer"=>false,
"verify_peer_name"=>false,
),
);
$ftpStreamContext = stream_context_create($ftpStreamOptions);
$ftpFileArray = file($ftpFilename, false, $ftpStreamContext);
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
exit;
}
if (!$ftpFileArray) {
echo "Error connecting to FTPS filename $ftpFilename";
exit;
}
# create array of data from FTP only grab num rows we need.
$fields = "DateTime,RECORD,BattV_Min,BattV_Max,BattV_Avg,PTemp_C_Avg,PTemp_C_Min,PTemp_C_Max,EnclosureTemp_Avg,
EnclosureTemp_Min,EnclosureTemp_Max,AirTC,AirTC_Avg,AirTC_Std,AirTC_Min,AirTC_Max,RH,RH_Avg,RH_Std,RH_Min,RH_Max,
DT,DT_Avg,DT_Std,DT_Min,DT_Max,Q,Q_Avg,Q_Std,Q_Min,Q_Max,TCDT,TCDT_Avg,TCDT_Std,TCDT_Min,TCDT_Max,DBTCDT,DBTCDT_Avg,
DBTCDT_Std,DBTCDT_Min,DBTCDT_Max,WS_ms_Avg,WS_ms_Std,WS_ms_Min,WS_ms_Max,WindDir,WS_ms_S_WVT,WindDir_D1_WVT,WindDir_SD1_WVT,
Rain_mm_Tot,BaroP,BaroP_Avg,BaroP_Std,BaroP_Min,BaroP_Max,SolarRad,SolarRad_Avg,SolarRad_Std,SolarRad_Min,SolarRad_Max,
PrecipGaugeLvl,PrecipGaugeLvl_Avg,PrecipGaugeLvl_Std,PrecipGaugeLvl_Min,PrecipGaugeLvl_Max,PrecipGaugeTemp,
PrecipGaugeTemp_Avg,PrecipGaugeTemp_Std,PrecipGaugeTemp_Min,PrecipGaugeTemp_Max,AirTC2,AirTC2_Avg,AirTC2_Std,AirTC2_Min,AirTC2_Max";
# get tail of ftp data. still has to load whole file first
$csv = array_slice(array_map('str_getcsv', $ftpFileArray), -NUMROWS);
$lines = 0;
foreach ($csv as $line) {
$lines++;
$linemysql = implode("','",$line);
// use the first entry of the linearray array to find the appropriate table.
$query = "insert ignore into `raw_$tbl` ($fields) values('$linemysql');";
if (!mysqli_query($conn, $query)) {
exit("Insert Query Error description: " . mysqli_error($conn));
}
}
// Then update clean table //
// temp change
// get rows from mysql
$rawRows = getMySQLRows($conn, "raw_$tbl", $numToClean);
$lineNum = 0;
foreach ($rawRows as $line) {
///// calcs //////
if ($lineNum == 0) {
//$PP_Pipe = 0;
$prevPCraw = $line["PrecipGaugeLvl_Avg"];
$lineNum++;
// need to skip line on first row so PP_pipe not set to 0
continue;
} else {
$PP_Pipe = ($line["PrecipGaugeLvl_Avg"] - $prevPCraw) * 1000;
}
// store current pc_raw val for next row
$prevPCraw = $line["PrecipGaugeLvl_Avg"];
$lineNum++;
$curDateTime = $line["DateTime"];
$curWatYr = wtr_yr($curDateTime, 10); // calc wat yr
$cleanRow = array(
"DateTime" => $line["DateTime"],
"WatYr" => $curWatYr,
"Air_Temp" => $line["AirTC_Avg"],
"Rh" => $line["RH_Avg"],
"BP" => $line["BaroP_Avg"],
"Wind_Speed" => $line["WS_ms_Avg"] * 3.6,
"Wind_Dir" => ($line["WindDir_D1_WVT"]>=180 ? $line["WindDir_D1_WVT"]-=180 : $line["WindDir_D1_WVT"] +=180), // rm young is backwards on tower
"Pk_Wind_Speed" => $line["WS_ms_Max"] * 3.6,
"PP_Tipper" => $line["Rain_mm_Tot"],
"PC_Raw_Pipe" => $line["PrecipGaugeLvl_Avg"] * 1000,
"PP_Pipe" => $PP_Pipe,
"Snow_Depth" => ((3.8 - $line["TCDT_Avg"]) * 100), // distance to ground processed on unit
"Solar_Rad" => $line["SolarRad_Avg"],
"Batt" => $line["BattV_Avg"]
);
if (count($cleanRow) > 0) {
$fields = implode(", ", array_keys($cleanRow));
$values = implode("','", array_values($cleanRow));
}
//$query = "UPDATE `clean_$tbl` SET WatYr = $curWatYr WHERE DateTime = '$curDateTime'";
$query = "INSERT IGNORE into `clean_$tbl` ($fields) values('$values')";
if (!mysqli_query($conn, $query)) {
exit("Insert Query Error description: " . mysqli_error($conn));
}
}
mysqli_close($conn);
?>