public int checkAndStoreSensorData()
{
int result = 0;
//getting now time
DateTime nowTime = DateTime.Now;
//getting latest time in database
TableFk tb = new TableFk(); //To prepare iMISID field
long theId = 1;
theId = tb.GetFk("dbo", "etc", "recordNum");
string strtheId = Convert.ToString(theId - 1);
//get the latest time based on the main key
string latedDatabaseTime = "";
TableSelect tbSelect = new TableSelect();
DataTable dt = tbSelect.DoSelect("etc", "dbo", "recordNum,ts", " recordNum=" + strtheId, "recordNum");
if (dt.Rows.Count == 0)
{
////Display("The database is empty. Transferring all data from the hosting server."); //this will never happer because some records should be imported in the initial stage.
}
else
{
//Get the latest time of database
latedDatabaseTime = dt.Rows[0]["ts"].ToString();
}
DateTime latestDatabaseTime = Convert.ToDateTime(latedDatabaseTime);
TimeSpan hoursSpan = nowTime - latestDatabaseTime;
double passedHours = hoursSpan.TotalHours;
double iPassedHours = Math.Round(passedHours);
TimeSpan hourIntervel = new TimeSpan(0, 1, 0, 0); //one hour
DateTime tempDateTime = DateTime.Now;
string theYear = "";
string theMonth = "";
string theDay = "";
string theHour = "";
string theFileName = "";
if (passedHours >= 1)
{
tempDateTime = latestDatabaseTime;
for (int i = 1; i <= iPassedHours; i++)
{
tempDateTime = tempDateTime + hourIntervel;
theYear = Convert.ToString(tempDateTime.Year);
if (tempDateTime.Month > 9)
{
theMonth = Convert.ToString(tempDateTime.Month);
}
else
{
theMonth = "0" + Convert.ToString(tempDateTime.Month);
}
if (tempDateTime.Day > 9)
{
theDay = Convert.ToString(tempDateTime.Day);
}
else
{
theDay = "0" + Convert.ToString(tempDateTime.Day);
}
if (tempDateTime.Hour > 9)
{
theHour = Convert.ToString(tempDateTime.Hour);
}
else
{
theHour = "0" + Convert.ToString(tempDateTime.Hour);
}
theYear = Convert.ToString(tempDateTime.Year);
theFileName = "Waverley_" + theYear + "-" + theMonth + "-" + theDay + "_" + theHour + "-00_FluxData" + ".dat";
string path1 = ReadSetting("etDatafilePath");
//string filePath = @"e:\etcData";
string filePath = path1;
string dataLine = "";
string errors = "";
////localhostETC.etcWebService etcService = new localhostETC.etcWebService();
try
{
dataLine = ReadTextData(filePath, theFileName);
}
catch (Exception ex)
{
errors = ex.ToString();
}
if (dataLine != "")// transferred the .dat file data into database
{
string[] stringArray = dataLine.Split(',');
//processing stringArray[0]
string stringArray0 = stringArray[0];
stringArray0 = stringArray0.Substring(1, stringArray0.Length - 2);
stringArray[0] = stringArray0;
TableInsert tbinsert = new TableInsert();
int operationResultStat = 0;
string tableName = "etc";
string userName = "******";
string tbfileds = "recordNum,ts";
for (int j = 2; j < dt.Columns.Count; j++)
{
tbfileds = tbfileds + "," + dt.Columns[i];
}
tbfileds = tbfileds + ",[cov_h2o_Ux_Avg],[cov_h2o_Uy_Avg],[cov_h2o_Uz_Avg],[cov_h2o_Uz_Tot],[cov_co2_Ux_Avg],[cov_co2_Uy_Avg],[cov_co2_Uz_Avg],[cov_co2_Uz_Tot]" +
",[wnd_dir_compass_Avg],[wnd_dir_csat3_Avg],[wnd_spd_Avg],[rslt_wnd_spd_Avg],[Hs],[Fc_wpl],[LE_wpl],[Hc],[tau],[u_star]" +
",[Ts_mean],[stdev_Ts],[cov_Ts_Ux],[cov_Ts_Uy],[cov_Ts_Uz],[co2_mean],[stdev_co2],[cov_co2_Ux],[cov_co2_Uy],[cov_co2_Uz]" +
",[h2o_Avg],[stdev_h2o],[cov_h2o_Ux],[cov_h2o_Uy],[cov_h2o_Uz],[Ux_Avg],[stdev_Ux],[cov_Ux_Uy],[cov_Ux_Uz],[Uy_Avg]" +
",[stdev_Uy],[cov_Uy_Uz],[Uz_Avg],[stdev_Uz],[press_mean],[rho_a_mean],[wnd_dir_compass],[wnd_dir_csat3],[wnd_spd],[rslt_wnd_spd]" +
",[std_wnd_dir],[Fc_irga],[LE_irga],[co2_wpl_LE],[co2_wpl_H],[h2o_wpl_LE],[h2o_wpl_H],[SHF1_raw_Avg],[SHF2_raw_Avg],[SHF_mean_Avg]" +
",[SR01Up_Avg] ,[SR01Dn_Avg],[IR01Up_Avg],[IR01Dn_Avg],[NR01TC_Avg],[NR01TK_Avg],[NetRs_Avg],[NetRl_Avg],[Albedo_Avg],[UpTot_Avg]" +
",[DnTot_Avg],[NetTot_Avg],[IR01UpCo_Avg],[IR01DnCo_Avg],[Canopy_Temp_Avg],[AirTC1_Avg],[RH1_Avg],[e_kPa1_Avg],[e_Sat1_Avg],[VPD1_Avg]" +
",[AirTC2_Avg],[RH2_Avg],[e_kPa2_Avg],[e_Sat2_Avg],[VPD2_Avg],[n_Tot],[csat_warnings],[irga_warnings],[del_T_f_Tot],[sig_lck_f_Tot]" +
",[amp_h_f_Tot],[amp_l_f_Tot],[chopper_f_Tot],[detector_f_Tot],[pll_f_Tot],[sync_f_Tot],[agc_Avg],[panel_temp_Avg],[batt_volt_Avg]";
string recordValues = Convert.ToString(stringArray[1]) + ",'" + Convert.ToString(stringArray[0]) + "'";
for (int j = 2; j < stringArray.Length; j++)
{
recordValues = recordValues + "," + Convert.ToString(stringArray[j]);
}
operationResultStat = tbinsert.Insert(tableName, userName, tbfileds, recordValues);
result = operationResultStat;
}
////TextBox1.Text = dataLine;
}
}
//Display("New ID number is created.");
return(result);
}
public ActionResult InputRainfallData(rainfallData model)
{
string userInput = model.theDate + "|| " + Convert.ToString(model.theHour) + " : " + Convert.ToString(model.theData);
int result = 0;
DateTime inputDate = Convert.ToDateTime(model.theDate);
string stringMinDate = "2010-01-01";
DateTime minDate = Convert.ToDateTime(stringMinDate);
DateTime tempDateTime = inputDate;
string theHour = Convert.ToString(model.theHour);
string inputRainfall = Convert.ToString(model.theData);
string theYear = "";
string theMonth = "";
string theDay = "";
theYear = Convert.ToString(tempDateTime.Year);
if (tempDateTime.Month > 9)
{
theMonth = Convert.ToString(tempDateTime.Month);
}
else
{
theMonth = "0" + Convert.ToString(tempDateTime.Month);
}
if (tempDateTime.Day > 9)
{
theDay = Convert.ToString(tempDateTime.Day);
}
else
{
theDay = "0" + Convert.ToString(tempDateTime.Day);
}
string theFinalInputDateTime = theYear + "-" + theMonth + "-" + theDay + " " + theHour + ":00:00.000";
//check if this hour data has been stored in the database. If no insert else update
//preparing to insert data
TableFk tb = new TableFk(); //To prepare iMISID field
long theId = 1;
theId = tb.GetFk("dbo", "rainfall", "recordNum");
string strtheId = Convert.ToString(theId);
//check if this data has been inserted
TableSelect tbSelect = new TableSelect();
DataTable dt = tbSelect.DoSelect("rainfall", "dbo", "recordNum,ts", " ts='" + theFinalInputDateTime + "'", "recordNum");
if (minDate < inputDate)
{
if ((dt != null) && (dt.Rows.Count > 0))
{
//updating
int theresult = 0;
TableUpdate updatetable = new TableUpdate();
string tableName = "rainfall";
string userName = "******";
string fieldvalue = "rainfall=" + inputRainfall;
string conditions = "ts='" + theFinalInputDateTime + "'";
theresult = updatetable.Update(tableName, userName, fieldvalue, conditions);
}
else
{
//inserting
TableInsert tbinsert = new TableInsert();
int operationResultStat = 0;
string tableName = "rainfall";
string userName = "******";
string tbfileds = "recordNum,ts,rainfall";
string recordValues = strtheId + ",'" + theFinalInputDateTime + "'," + inputRainfall;
operationResultStat = tbinsert.Insert(tableName, userName, tbfileds, recordValues);
result = operationResultStat;
}
//get the 24 hour data of the date to display
string dateTime1 = theYear + "-" + theMonth + "-" + theDay + " 00:00:00";
string dateTime2 = theYear + "-" + theMonth + "-" + theDay + " 23:00:00";
dt = tbSelect.DoSelect("rainfall", "dbo", "ts,rainfall", " ts>='" + dateTime1 + "' and ts<='" + dateTime2 + "'", "recordNum");
ViewData["oneDayData"] = dt;
//ViewBag.theDayData = dt;
}
ViewBag.fromControllerMessage = "The user input is " + userInput;
return(View());
}
public ActionResult getTimeSpan(ETtimeSpan model)
{
string userInput = model.start + " ; " + model.end;
string startDate = model.start;
string endDate = model.end;
//////Checking all the hourly data record to confirm if it has been stored in database one by one
//get time span to get file name of each hourly data file within the time span
if (startDate == null)
{
startDate = Convert.ToString(DateTime.Now.AddMonths(-1)); //default to check if delete hourly data from one month ago to one week agao
}
DateTime theStartDate = Convert.ToDateTime(startDate);
theStartDate = theStartDate.AddDays(-1);
//get the day before the startDate data for daily et calcualting
startDate = Convert.ToString(theStartDate);
string strYear5 = "";
string strMonth5 = "";
string strDay5 = "";
strYear5 = Convert.ToString(theStartDate.Year);
if (theStartDate.Month < 10)
{
strMonth5 = "0" + Convert.ToString(theStartDate.Month);
}
else
{
strMonth5 = Convert.ToString(theStartDate.Month);
}
if (theStartDate.Day < 10)
{
strDay5 = "0" + Convert.ToString(theStartDate.Day);
}
else
{
strDay5 = Convert.ToString(theStartDate.Day);
}
startDate = strYear5 + "-" + strMonth5 + "-" + strDay5;
if (endDate == null)
{
endDate = Convert.ToString(DateTime.Now.AddDays(-7));
//default end date is one week ago
}
DateTime theEndDate = Convert.ToDateTime(endDate);
string strYear6 = "";
string strMonth6 = "";
string strDay6 = "";
strYear6 = Convert.ToString(theEndDate.Year);
if (theEndDate.Month < 10)
{
strMonth6 = "0" + Convert.ToString(theEndDate.Month);
}
else
{
strMonth6 = Convert.ToString(theEndDate.Month);
}
if (theEndDate.Day < 10)
{
strDay6 = "0" + Convert.ToString(theEndDate.Day);
}
else
{
strDay6 = Convert.ToString(theEndDate.Day);
}
endDate = strYear6 + "-" + strMonth6 + "-" + strDay6;
DateTime dateTime1 = Convert.ToDateTime(startDate + " " + "00:00:00");
DateTime dateTime2 = Convert.ToDateTime(endDate + " " + "23:00:00");
TimeSpan hoursSpan = dateTime2 - dateTime1;
double passedHours = hoursSpan.TotalHours;
double iPassedHours = Math.Round(passedHours);
TimeSpan hourIntervel = new TimeSpan(0, 1, 0, 0); //one hour
DateTime tempDateTime = dateTime1.AddHours(-1);
string theYear = "";
string theMonth = "";
string theDay = "";
string theHour = "";
string theFileName = "";
if (passedHours >= 1)
{
for (int i = 1; i <= iPassedHours; i++)
{
tempDateTime = tempDateTime + hourIntervel;
theYear = Convert.ToString(tempDateTime.Year);
if (tempDateTime.Month > 9)
{
theMonth = Convert.ToString(tempDateTime.Month);
}
else
{
theMonth = "0" + Convert.ToString(tempDateTime.Month);
}
if (tempDateTime.Day > 9)
{
theDay = Convert.ToString(tempDateTime.Day);
}
else
{
theDay = "0" + Convert.ToString(tempDateTime.Day);
}
if (tempDateTime.Hour > 9)
{
theHour = Convert.ToString(tempDateTime.Hour);
}
else
{
theHour = "0" + Convert.ToString(tempDateTime.Hour);
}
theYear = Convert.ToString(tempDateTime.Year);
theFileName = "Waverley_" + theYear + "-" + theMonth + "-" + theDay + "_" + theHour + "-00_FluxData" + ".dat";
// Check and call etDataFileDelete if the hourly data file has been stored in database
TableSelect tbSelect = new TableSelect();
DataTable dt = tbSelect.DoSelect("etc", "dbo", "recordNum,ts", " ts='" + theYear + "-" + theMonth + "-" + theDay + " " + theHour + ":00:00'", "recordNum");
int deleteOperationResult = 1;
if (!((dt != null) && (dt.Rows.Count > 0)))
{
//// The hourly data has not been stored in database.
}
else
{
////The hourly data has been stored in database.
// call etDataFileDelete()
deleteOperationResult = DeleteEtDataFile(theFileName);
}
if (deleteOperationResult == 1)
{
ViewBag.deleteOperationResponse = "Deleting hourly data file is unsuccessful.";
}
}
}
//////return Content(deleteResponse.ToString());
//ViewBag.fromControllerMessage = "The user input is " + userInput;
//ViewBag.test = "This is test__";
return(View());
}
public int storeRainfallData(string theFileName)
{
int result = 0;
//string filePath = @"D:\etc\etc\uploadedFiles";
string path1 = ReadSetting("rainFallDatafilePath");
//string filePath = @"C:\2016codes\etc\etc\uploadedFiles";
string filePath = path1;
string[] dataLine = null;
string errors = "";
////localhostETC.etcWebService etcService = new localhostETC.etcWebService();
try
{
dataLine = ReadTextData1(filePath, theFileName);
if (theFileName.Length > 0)
{
int theOperationResultStat = DeleteRainfallDataFile(theFileName);
result = theOperationResultStat;
}
}
catch (Exception ex)
{
errors = ex.ToString();
}
if (dataLine != null)// transferred the .dat file data into database
{
//check and insert rainfall data
string[] theSeparator2 = { "," };
string theYear = "";
string theMonth = "";
string theDay = "";
for (int i = 1; i < dataLine.Length; i++)
{
string[] lineData = dataLine[i].Split(theSeparator2, StringSplitOptions.None);
string lineDate = lineData[0];
DateTime theLineDate = Convert.ToDateTime(lineDate);
DateTime tempDateTime = theLineDate;
theYear = Convert.ToString(tempDateTime.Year);
if (tempDateTime.Month > 9)
{
theMonth = Convert.ToString(tempDateTime.Month);
}
else
{
theMonth = "0" + Convert.ToString(tempDateTime.Month);
}
if (tempDateTime.Day > 9)
{
theDay = Convert.ToString(tempDateTime.Day);
}
else
{
theDay = "0" + Convert.ToString(tempDateTime.Day);
}
string theDateTime = theYear + "-" + theMonth + "-" + theDay + " " + lineData[1];
string totalRain = lineData[14];
// select the dateTime in the rainfall table, if not exite, insert it into the table
// select
//preparing to insert data
TableFk tb = new TableFk(); //To prepare iMISID field
long theId = 1;
theId = tb.GetFk("dbo", "rainfall", "recordNum");
string strtheId = Convert.ToString(theId);
//check if this data has been inserted
TableSelect tbSelect = new TableSelect();
DataTable dt = tbSelect.DoSelect("rainfall", "dbo", "recordNum,ts", " ts='" + theDateTime + "'", "recordNum");
if ((dt != null) && (dt.Rows.Count > 0))
{
//nothing to do
}
else
{
//inserting
TableInsert tbinsert = new TableInsert();
int operationResultStat = 0;
string tableName = "rainfall";
string userName = "******";
string tbfileds = "recordNum,ts,rainfall";
string recordValues = strtheId + ",'" + theDateTime + "'," + totalRain;
operationResultStat = tbinsert.Insert(tableName, userName, tbfileds, recordValues);
result = operationResultStat;
}
} //for
int test = 1;
}
return(result);
}