/// <summary>
/// Reads the current working file and stores it to the database,
/// then deletes the (temporary) file.
/// </summary>
private bool storeDataFromFile(bool liveData = false)
{
// If it's a uif file, move it to temp
// Get the new file
FileInfo newFile = new FileInfo(TEMPPATH + file.Name);
Data data = new Data(this.databaseName);
string line;
string recorderSerial = null;
List<string> lines = new List<string>();
int totalRecords = Utility.TotalLines(newFile.FullName);
// Kill if total records = 0
if (totalRecords == 0) return false;
// Let's read the first line and get the serial number
try
{
string firstLine = null;
using (StreamReader reader = new StreamReader(newFile.FullName))
{
firstLine = reader.ReadLine();
reader.Close();
}
// Let's get the serial number
string[] sampleData = firstLine.Split(',');
recorderSerial = sampleData[5];
}
catch (Exception ex)
{
Log.Error("File read error: " + ex.ToString());
}
string dt = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
string currentDate = data.AdjustTimezone(dt, "yyyy-MM-dd", recorderSerial);
string currentTime = data.AdjustTimezone(dt, "HH:mm:ss", recorderSerial);
// Initialise the InsertQuery method for Imports
InsertQuery insertImport = new InsertQuery();
insertImport.SetTable("import");
insertImport.SetFields(new string[] { "impDate", "impTime", "impRecordCount", "impLiveData" });
// Add rows
insertImport.AddRowValues(new string[] {
currentDate,
currentTime,
totalRecords.ToString(),
liveData ? "1" : "0"
});
// Add data
int importId = data.InsertImport(insertImport);
// Prepare a new InserQuery for shock data
InsertQuery insertShock = new InsertQuery();
insertShock.SetTable("shock");
insertShock.SetFields(new string[] { "import_id", "shocktype_id", "shkDate", "shkTime", "shkRSN" });
// Prepare new InserQuery for patrol data
InsertQuery insertPatrol = new InsertQuery();
insertPatrol.SetTable("patrol");
insertPatrol.SetFields(new string[] { "import_id", "patTSN", "patDate", "patTime", "patRSN" });
// Prepare a new InsertQuery for lowvoltage data
InsertQuery insertVoltage = new InsertQuery();
insertVoltage.SetTable("lowvoltage");
insertVoltage.SetFields(new string[] { "import_id", "lowReading", "lowDate", "lowTime", "lowRSN"});
// Read the file line by line
try
{
// Initialise StreamReader
using (StreamReader reader = new StreamReader(newFile.FullName))
{
int patrolCounter = 0;
int patrolShock = 0;
int patrolVoltage = 0;
while ((line = reader.ReadLine()) != null)
{
// Split by commas
string[] patrolData = line.Split(',');
DateTime date = new DateTime();
try
{
// Check if data comes in dd-mm-yyyy
string text = patrolData[3];
string patt = @"(\d){2}-(\d){2}-(\d){4}";
Regex regex = new Regex(patt, RegexOptions.IgnoreCase);
Match match = regex.Match(text);
if (match.ToString() == patrolData[3])
{
text = text.Replace('-', '/');
}
// Convert the date from dd/MM/yy to yyyy-MM-dd
string[] dateParams = text.Split('/');
date = new DateTime(
Convert.ToInt32(dateParams[2]),
Convert.ToInt32(dateParams[1]),
Convert.ToInt32(dateParams[0])
);
}
catch (Exception ex)
{
Log.Warning("Decrypt Test Failed " + ex.ToString());
}
// If patrolData[0] is 1, then it is a normal tag read
if (patrolData[0] == "1")
{
// Insert patrol data
insertPatrol.AddRowValues(new string[5] {
// MUST change this field in the Data() class if also adding to OLD databases,
// best to use MySQL's in-built last_insert_id() to get the other ID
importId.ToString(),
patrolData[2],
date.ToString("yyyy-MM-dd"),
patrolData[4],
patrolData[5]
});
// Increase patrol counter
patrolCounter++;
// Make sections of 1000 and store them into db
if (patrolCounter > 1000)
{
// Insert it
data.InsertPatrol(insertPatrol);
// Clear it
insertPatrol.SetRowValues(new List<string[]>());
// Reset the counter
patrolCounter = 0;
}
}
// If patrolData[o] is 2, then its a shock log
if (patrolData[0] == "2")
{
// Insert patrol data
insertShock.AddRowValues(new string[5] {
// MUST change this field in the Data() class if also adding to OLD databases,
// best to use MySQL's in-built last_insert_id() to get the other ID
importId.ToString(),
patrolData[2],
date.ToString("yyyy-MM-dd"),
patrolData[4],
patrolData[5]
});
// Increase patrol counter
patrolShock++;
// Make sections of 1000 and store them into db
if (patrolShock > 1000)
{
// Insert it
data.InsertShock(insertShock);
// Clear it
insertShock.SetRowValues(new List<string[]>());
// Reset the counter
patrolShock = 0;
}
}
// If patrolData[0] is 4, then the record is low voltage
if (patrolData[0] == "4")
{
// Get number
double battReading = Convert.ToDouble(patrolData[2]) / 1000;
// Insert patrol data
insertVoltage.AddRowValues(new string[5] {
importId.ToString(),
battReading.ToString(),
date.ToString("yyyy-MM-dd"),
patrolData[4],
patrolData[5]
});
// Increase patrol counter
patrolVoltage++;
// Make sections of 1000 and store them into db
if (patrolVoltage > 1000)
{
// Insert it
data.InsertVoltage(insertVoltage);
// Clear it
insertVoltage.SetRowValues(new List<string[]>());
// Reset the counter
patrolVoltage = 0;
}
}
}
// Close the reader
reader.Close();
}
// Insert left overs if necessary
if (insertPatrol.GetAllValues().Count > 0) data.InsertPatrol(insertPatrol);
if (insertShock.GetAllValues().Count > 0) data.InsertShock(insertShock);
if (insertVoltage.GetAllValues().Count > 0) data.InsertVoltage(insertVoltage);
// Initialise the InsertQuery method for UploadActivity
InsertQuery insertUploadActivity = new InsertQuery();
insertUploadActivity.SetTable("uploadactivity");
insertUploadActivity.SetFields(new string[] { "account_id", "uplDate", "uplTime", "uplRecords", "uplLiveData" });
// Add rows
insertUploadActivity.AddRowValues(new string[] {
data.GetAccountIdForDatabase().ToString(),
currentDate,
currentTime,
totalRecords.ToString(),
liveData ? "1" : "0"
});
// Insert it
data.InsertUploadActivity(insertUploadActivity);
}
catch (Exception ex)
{
Log.Warning("Attempted to decrypt file which does not exist: " + newFile.FullName + "\r\n" + ex.ToString());
return false;
}
// Attempt to delete the file
try
{
newFile.Delete();
}
catch (Exception ex)
{
Log.Error("Could not delete temporary file: " + newFile.FullName + "\r\n" + ex.ToString());
return false;
}
return true;
}
/// <summary>
/// Stores GPRS Data to database
/// </summary>
public void StoreData()
{
// Count everything
string[][] nr = normalRecords.ToArray();
string[][] ar = alarmRecords.ToArray();
string[][] cr = customRecords.ToArray();
string[][] lr = lowVoltageRecords.ToArray();
int nrc = nr.Length;
int arc = ar.Length;
int crc = cr.Length;
int lrc = lr.Length;
int totalRecords = nrc + arc + crc + lrc;
bool storedData = false;
// Add data
Data data = new Data(this.databaseName);
/*********************************************
* INSERT IMPORT DATA
*********************************************/
InsertQuery insertImport = new InsertQuery();
insertImport.SetTable("import");
insertImport.SetFields(new string[4] { "impDate", "impTime", "impRecordCount", "impLiveData" });
// Adjust time if necessary
string importDateTime = this.importDate + " " + this.importTime;
string newImportDate = data.AdjustTimezone(importDateTime, "yyyy-MM-dd", this.recorderSerial);
string newImportTime = data.AdjustTimezone(importDateTime, "HH:mm:ss", this.recorderSerial);
// Add rows
insertImport.AddRowValues(new string[4] { newImportDate, newImportTime, totalRecords.ToString(), "1" });
// Insert the import and retrieve the import Id
int importId = data.InsertImport(insertImport);
/*********************************************
* INSERT PATROL RECORDS
*********************************************/
if (nrc > 0)
{
// Prepare new InserQuery for patrol data
InsertQuery insertPatrol = new InsertQuery();
insertPatrol.SetTable("patrol");
insertPatrol.SetFields(new string[5] { "import_id", "patTSN", "patDate", "patTime", "patRSN" });
// Queue normal records for insertion
for (int i = 0; i < nrc; ++i)
{
string date = data.AdjustTimezone(nr[i][1], "yyyy-MM-dd", nr[i][2]);
string time = data.AdjustTimezone(nr[i][1], "HH:mm:ss", nr[i][2]);
if (data.CheckPatrol(nr[i][0], date, time, nr[i][2]))
insertPatrol.AddRowValues(new string[5] { importId.ToString(), nr[i][0], date, time, nr[i][2] });
}
// Insert it
if (insertPatrol.Count > 0)
{
data.InsertPatrol(insertPatrol);
storedData = true;
}
}
/*********************************************
* INSERT EVENT RECORDS
*********************************************/
if ((arc + crc) > 0)
{
// Prepare new InsertQuery for event data
InsertQuery insertEvent = new InsertQuery();
insertEvent.SetTable("event");
insertEvent.SetFields(new string[5] { "import_id", "eventtype_id", "evnDate", "evnTime", "evnRSN" });
// Queue alarm records for insertion
if (arc > 0)
{
for (int i = 0; i < arc; ++i)
{
string date = data.AdjustTimezone(ar[i][0], "yyyy-MM-dd", ar[i][1]);
string time = data.AdjustTimezone(ar[i][0], "HH:mm:ss", ar[i][1]);
insertEvent.AddRowValues(new string[5] { importId.ToString(), 1.ToString(), date, time, ar[i][1] });
}
}
// Queue custom records for insertion
if (crc > 0)
{
for (int i = 0; i < crc; ++i)
{
string date = data.AdjustTimezone(cr[i][0], "yyyy-MM-dd", cr[i][1]);
string time = data.AdjustTimezone(cr[i][0], "HH:mm:ss", cr[i][1]);
insertEvent.AddRowValues(new string[5] { importId.ToString(), 2.ToString(), date, time, cr[i][1] });
}
}
// Insert it
if (insertEvent.Count > 0)
{
data.InsertEvent(insertEvent);
storedData = true;
}
}
/*********************************************
* INSERT LOW VOLTAGE RECORDS
*********************************************/
if (lrc > 0)
{
// Prepare new InsertQuery
InsertQuery insertVoltage = new InsertQuery();
insertVoltage.SetTable("lowvoltage");
insertVoltage.SetFields(new string[5] { "import_id", "lowReading", "lowDate", "lowTime", "lowRSN" });
// Queue low voltage records for insertion
for (int i = 0; i < lrc; ++i)
{
string date = data.AdjustTimezone(lr[i][0], "yyyy-MM-dd", lr[i][1]);
string time = data.AdjustTimezone(lr[i][0], "HH:mm:ss", lr[i][1]);
insertVoltage.AddRowValues(new string[5] { importId.ToString(), lr[i][2], date, time, lr[i][1] });
}
// Insert it
if (insertVoltage.Count > 0)
{
data.InsertLowVoltage(insertVoltage);
storedData = true;
}
}
/*********************************************
* INSERT UPLOAD ACTIVITY
*********************************************/
// Only required for new legacy databases
InsertQuery insertUploadActivity = new InsertQuery();
insertUploadActivity.SetTable("uploadactivity");
insertUploadActivity.SetFields(new string[5] { "account_id", "uplDate", "uplTime", "uplRecords", "uplLiveData" });
// Add rows
string accountId = data.GetAccountIdForDatabase().ToString();
insertUploadActivity.AddRowValues(new string[5] { accountId, importDate, importTime, totalRecords.ToString(), "1" });
// Insert it
if (storedData)
data.InsertUploadActivity(insertUploadActivity);
else
// Delete the import if not used
data.DeleteImport(importId);
}