private void CreateException(int HighRiskRecord_Id)
{
// Create insert query
InsertQuery insert = new InsertQuery();
insert.SetTable("checkpointhighriskexception");
insert.SetFields(new string[] { "checkpointhighrisk_id", "hreDate", "hreTime" });
insert.AddRowValues(new string[] {
HighRiskRecord_Id.ToString(),
Utility.Now(this.gmtOffset).ToString("yyyy-MM-dd"),
Utility.Now(this.gmtOffset).ToString("HH:mm:ss")
});
// Database
Database db = new Database(this.database);
db.Insert(insert);
}
/// <summary>
/// Insert data into exception
/// </summary>
/// <param name="loopExceptionId"></param>
/// <param name="checkpointId"></param>
private void InsertIntoException(int loopExceptionId, int checkpointId)
{
// Connect to the database
Database db = new Database(this.database);
// Create insert query
InsertQuery insert = new InsertQuery();
insert.SetTable("loopexceptioncheckpoint");
insert.SetFields(new string[2] {
"loopexception_id", "checkpoint_id"
});
insert.AddRowValues(new string[2] {
loopExceptionId.ToString(),
checkpointId.ToString()
});
// Insert
db.Insert(insert);
}
/// <summary>
/// Creates an exception and inserts it to the database.
/// </summary>
private void CreateException()
{
// Create insert query
InsertQuery insert = new InsertQuery();
insert.SetTable("welfarecheckexception");
insert.SetFields(new string[4] {
"welfarecheck_id", "excDate", "excTime", "excRSN"
});
insert.AddRowValues(new string[4] {
this.welfareId.ToString(),
Utility.Now(this.gmtOffset).ToString("yyyy-MM-dd"),
Utility.Now(this.gmtOffset).ToString("HH:mm:ss"),
this.recorderSerial.ToString()
});
// Database
Database db = new Database(this.database);
db.Insert(insert);
}
/// <summary>
/// Creates a loop exception and returns its ID
/// </summary>
private int CreateException()
{
// Connect to the database
Database db = new Database(this.database);
// Create insert query
InsertQuery insert = new InsertQuery();
insert.SetTable("loopexception");
insert.SetFields(new string[3] {
"loop_id", "lexDate", "lexTime"
});
insert.AddRowValues(new string[3] {
this.loopId.ToString(),
Utility.Now(this.gmtOffset).ToString("yyyy-MM-dd"),
Utility.Now(this.gmtOffset).ToString("HH:mm:ss")
});
// Insert
db.Insert(insert);
// Return last insert ID
return(db.GetLastColumn("loopexception", "id"));
}
/// <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);
}
}
public void ProcessRequest(HttpContext ctx)
{
string json;
using (var reader = new System.IO.StreamReader(ctx.Request.InputStream))
{
json = reader.ReadToEnd();
obj.Write_ErrorLog("1:" + json);
json = System.Web.HttpUtility.UrlDecode(json);
if (json.Contains("DeviceData="))
{
json = json.Remove(0, 11);
}
}
ctx.Response.ContentType = "text/plain";
if (!string.IsNullOrEmpty(json))
{
try
{
try
{
// Parse JSON into dynamic object, convenient!
System.Collections.Generic.Dictionary <string, object> values = JsonConvert.DeserializeObject <System.Collections.Generic.Dictionary <string, object> >(json);
Newtonsoft.Json.Linq.JObject results = Newtonsoft.Json.Linq.JObject.Parse(json);
int recard_count = 0;
string DateUTC = "";
foreach (var result in results["Records"])
{
string Reason = (string)result["Reason"];
// DateUTC = (string)result["DateUTC"];
if (Reason == "17")
{
Newtonsoft.Json.Linq.JObject results1 = Newtonsoft.Json.Linq.JObject.Parse(Convert.ToString(result));
foreach (var result1 in results1["Fields"])
{
string cdata = (string)result1["Data"];
if (!string.IsNullOrEmpty(Convert.ToString(cdata)))
{
recard_count = recard_count + 1;
}
}
}
}
if (recard_count != 0)
{
string dev_SerNo = (string)results["SerNo"];
G60_Data obj_DB_name = new G60_Data();
DataTable dt_database_name = new DataTable();
dt_database_name = obj_DB_name.get_database_name(dev_SerNo);
if (dt_database_name.Rows.Count > 0)
{
string databaseName = dt_database_name.Rows[0]["schCode"].ToString();
// Add data
UniGuardLib.Data data = new UniGuardLib.Data(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 = (Convert.ToDateTime(DateTime.Now)).ToString();
string newImportDate = data.AdjustTimezone(importDateTime, "yyyy-MM-dd", dev_SerNo);
string newImportTime = data.AdjustTimezone(importDateTime, "HH:mm:ss", dev_SerNo);
//string newImportDate = (Convert.ToDateTime(importDateTime)).ToString("yyyy-MM-dd");
//string newImportTime = (Convert.ToDateTime(importDateTime)).ToString("HH:mm:ss");
// Add rows
insertImport.AddRowValues(new string[4] {
newImportDate, newImportTime, recard_count.ToString(), "1"
});
// Insert the import and retrieve the import Id
Data objdata = new Data(databaseName);
int importId = objdata.InsertImport(insertImport);
/*********************************************
* INSERT PATROL RECORDS
*********************************************/
if (recard_count > 0)
{
foreach (var result in results["Records"])
{
if (((string)result["DateUTC"]) != null)
{
DateUTC = (string)result["DateUTC"];
}
// this can be a string or null
string Reason = (string)result["Reason"];
if (Reason == "17")
{
Newtonsoft.Json.Linq.JObject results1 = Newtonsoft.Json.Linq.JObject.Parse(Convert.ToString(result));
foreach (var result1 in results1["Fields"])
{
string cdata = (string)result1["Data"];
if (!string.IsNullOrEmpty(Convert.ToString(cdata)))
{
var base64 = cdata;
var base64_data = Convert.FromBase64String(base64);
string hexValue = ByteArrayToString(base64_data);
int decValue = int.Parse(hexValue, System.Globalization.NumberStyles.HexNumber);
// Prepare new InserQuery for patrol data
InsertQuery insertPatrol = new InsertQuery();
insertPatrol.SetTable("patrol");
insertPatrol.SetFields(new string[5] {
"import_id", "patTSN", "patDate", "patTime", "patRSN"
});
//string date = (Convert.ToDateTime(DateUTC)).ToString("yyyy-MM-dd");
//string time = (Convert.ToDateTime(DateUTC)).ToString("HH:mm:ss");
//string date = newImportDate1;
//string time = newImportTime1;
string importDateTime1 = (Convert.ToDateTime(DateUTC)).ToString();
string date = data.AdjustTimezone(importDateTime1, "yyyy-MM-dd", dev_SerNo);
string time = data.AdjustTimezone(importDateTime1, "HH:mm:ss", dev_SerNo);
insertPatrol.AddRowValues(new string[5] {
importId.ToString(), Convert.ToString(decValue), date, time, Convert.ToString(dev_SerNo)
});
data.InsertPatrol(insertPatrol);
}
}
}
}
}
}
}
}
catch (Exception ex)
{
obj.Write_ErrorLog("2:" + ex.ToString());
System.Collections.Generic.Dictionary <string, object> values = JsonConvert.DeserializeObject <System.Collections.Generic.Dictionary <string, object> >(json);
string logFolder = @"C:\g60";
string serialNumber = values["SerNo"].ToString();
string fileName = string.Format("{0} - {1}.txt", serialNumber, DateTime.Now.ToString("yyyyMMddHHmmss"));
System.IO.File.WriteAllText(System.IO.Path.Combine(logFolder, fileName), json);
obj.Write_ErrorLog("3:" + json);
ctx.Response.Write("Insert successful");
}
}
catch (Exception ex)
{
obj.Write_ErrorLog("4:" + ex.ToString());
// System.Collections.Generic.Dictionary<string, object> values = JsonConvert.DeserializeObject<System.Collections.Generic.Dictionary<string, object>>(json);
string logFolder = @"C:\g60";
string serialNumber = "1234";
string fileName = string.Format("{0} - {1}.txt", serialNumber, DateTime.Now.ToString("yyyyMMddHHmmss"));
System.IO.File.WriteAllText(System.IO.Path.Combine(logFolder, fileName), json);
obj.Write_ErrorLog("4:" + json);
ctx.Response.Write("Insert successful");
ctx.Response.StatusCode = (int)System.Net.HttpStatusCode.InternalServerError;
ctx.Response.Write(string.Format("ERROR: {0}", ex.Message));
}
}
else
{
ctx.Response.TrySkipIisCustomErrors = true;
ctx.Response.StatusCode = (int)System.Net.HttpStatusCode.InternalServerError;
obj.Write_ErrorLog("6:" + "ERROR: No POST Data!");
ctx.Response.Write(string.Format("ERROR: No POST Data!"));
}
}
