private async Task <IActionResult> addLoggerLayout101(ge_data data, string [] lines)
{
dbConnectDetails cd = await getConnectDetails(data.projectId, gINTTables.DB_DATA_TYPE);
if (cd == null)
{
return(NotFound());
}
int gINTProjectID = cd.ProjectId;
string POINT_ID = "PointId";
string MOND_ID = "Monitoring Id";
string WATER_DEPTH = "Water Depth";
string DATE_TIME = "DateTime";
measurement mm = dict_measurement[WATER_DEPTH];
string[] header = lines[0].Split(",");
int colPOINT_ID = header.findColumn(POINT_ID);
int colMOND_ID = header.findColumn(MOND_ID);
int colDATE_TIME = header.findColumn(DATE_TIME);
int colWATER_DEPTH = header.findColumn(WATER_DEPTH);
int row_start = 1;
int col_start = colDATE_TIME + 1;
for (int i = row_start; i < lines.Length; i++)
{
string[] line = lines[i].Split(",");
try {
var newMOND = new MOND();
newMOND.gINTProjectID = gINTProjectID;
newMOND.PointID = line[colPOINT_ID];
newMOND.ItemKey = line[colMOND_ID];
MONG.sqlWhere("gINTProjectID=" + gINTProjectID + " and PointID='" + newMOND.PointID + "' and ItemKey='" + newMOND.ItemKey + "'");
MONG.getDataTable();
if (!MONG.EOF())
{
DataRow row = MONG.FirstRow();
newMOND.MONG_DIS = Convert.ToDouble(row["MONG_DIS"]);
}
newMOND.DateTime = Convert.ToDateTime(line[colDATE_TIME]);
newMOND.MOND_RDNG = line[colWATER_DEPTH];
newMOND.MOND_TYPE = mm.Type;
newMOND.MOND_NAME = mm.Name;
newMOND.MOND_UNIT = mm.Units;
AddMOND(newMOND, true);
} catch (Exception e) {
Console.Write(e.Message);
}
}
return(Ok());
}
private async Task <List <ge_log_file> > GetFiles(Guid dataId,
Boolean IncludeReadings = true)
{
if (dataId == null)
{
return(null);
}
var _logger = await _context.ge_data
.Include(d => d.project)
.AsNoTracking()
.SingleOrDefaultAsync(m => m.Id == dataId);
dbConnectDetails cd = await getConnectDetails(_logger.projectId, logTables.DB_DATA_TYPE);
if (cd == null)
{
return(null);
}
string dbConnectStr = cd.AsConnectionString();
var dt_file2 = await Task.Run(() =>
{
using (SqlConnection cnn = new SqlConnection(dbConnectStr))
{
cnn.Open();
dsTable <ge_log_file> ds_files = new logTables().file;
ds_files.setConnection(cnn);
ds_files.sqlWhere("dataId='" + dataId.ToString() + "'");
ds_files.getDataSet();
DataTable dt_file = ds_files.getDataTable();
return(dt_file);
}
});
List <ge_log_file> local_log_files = new List <ge_log_file>();
foreach (DataRow row in dt_file2.Rows)
{
ge_log_file file = new ge_log_file();
get_log_file_values(row, file);
if (IncludeReadings == true)
{
file = await GetFile(file.dataId, file.channel, true);
}
local_log_files.Add(file);
}
return(local_log_files);
}
private async Task <int> UpdateChannel(Guid[] Id, Guid dataId, string header, float [] values)
{
//value_header vh = Json.Convert<value_header>(header);
int NOT_OK = -1;
int ret = 0;
if (dataId == null)
{
return(NOT_OK);
}
var _logger = await _context.ge_data
.Include(d => d.project)
.SingleOrDefaultAsync(m => m.Id == dataId);
if (_logger == null)
{
return(NOT_OK);
}
dbConnectDetails cd = await getConnectDetails(_logger.projectId, logTables.DB_DATA_TYPE);
if (cd == null)
{
return(NOT_OK);
}
string dbConnectStr = cd.AsConnectionString();
return(await Task.Run(() =>
{
using (SqlConnection cnn = new SqlConnection(dbConnectStr))
{
dsTable <ge_log_reading> ds_readings = new logTables().reading;
cnn.Open();
ds_readings.setConnection(cnn);
ds_readings.getDataTable();
ds_readings.sqlWhere("Id='" + Id.ToString() + "'");
ds_readings.getDataSet();
DataTable dt_readings = ds_readings.getDataTable();
DataRow row = dt_readings.Rows[0];
// if (Valid!=null) row["Valid"] = Valid;
// if (Include!=null) row["Include"] = Include;
// if (pflag!=null) row["pflag"] = pflag;
// if (NotDry!=null) row["NotDry"] = NotDry;
// if (Remark!=null) row["Remark"] = Remark;
ret = ds_readings.Update();
return ret;
}
}));
}
public static void FixLTC_OtherDatabase(ge_DbContext context)
{
dbConnectDetails cd1 = new dbConnectDetails()
{
Type = gINTTables.DB_DATA_TYPE,
Server = "EUDTC1PSQLW001",
Database = "ltc_ags4",
UserId = "ltc_ags4",
Password = "******",
ProjectId = 3
};
dbConnectDetails cd2 = new dbConnectDetails()
{
Type = logTables.DB_DATA_TYPE,
Server = "EUDTC1PSQLW001",
Database = "ltc_monitoring",
UserId = "ltc_monitoring",
Password = "******",
};
context.SaveChanges();
}
private async Task <dbConnectDetails> getConnectDetails(Guid projectId, string dbType)
{
if (projectId == null)
{
return(null);
}
var project = await _context.ge_project
.Include(p => p.group)
.FirstOrDefaultAsync(m => m.Id == projectId);
if (project == null)
{
return(null);
}
if (project.otherDbConnectId == null)
{
return(null);
}
var cs = await new ge_dataController(_context,
_authorizationService,
_userManager,
_env,
_ge_config).getDataAsClass <OtherDbConnections>(project.otherDbConnectId.Value);
if (cs == null)
{
return(null);
}
dbConnectDetails cd = cs.getConnectType(dbType);
return(cd);
}
public static void SeedLowerThamesCrossing(ge_DbContext context)
{
var manager1 = context.Users.Where(a => a.UserName == "*****@*****.**").Single();
var user1 = context.Users.Where(a => a.UserName == "*****@*****.**").Single();
var g1 = new ge_group {
Id = Guid.NewGuid(),
locName = "Lower Thames Crossing Ground Investigation",
createdDT = DateTime.Now,
locAddress = "",
locPostcode = "",
datumProjection = datumProjection.NONE,
managerId = manager1.Id,
operations = "Read;Update;Delete",
project_operations = "Create;Read;Update;Delete",
projects = new List <ge_project>(),
users = new List <ge_user_ops>()
};
g1.users.Add(new ge_user_ops {
groupId = g1.Id,
userId = manager1.Id,
user_operations = "Read;Download;Create;Update;Delete;Approve;Admin"
});
g1.users.Add(new ge_user_ops {
groupId = g1.Id,
userId = user1.Id,
user_operations = "Read;Download;Create;Update;Delete"
});
dbConnectDetails cd1 = new dbConnectDetails()
{
Type = gINTTables.DB_DATA_TYPE,
Server = "EUDTC1PSQLW001",
Database = "ltc_ags4",
DataSource = "172.17.136.103",
UserId = "ltc_ags4",
Password = "******"
};
#region CreateProjects
var p1 = new ge_project {
Id = Guid.NewGuid(),
locName = "Lower Thames Crossing Package A",
locAddress = "",
locPostcode = "",
description = "Monitoring Data for Lower Thames Crossing Package A",
keywords = "",
createdId = user1.Id,
createdDT = DateTime.Now,
editedId = user1.Id,
editedDT = DateTime.Now,
start_date = new DateTime(2017, 11, 15),
pstatus = PublishStatus.Uncontrolled,
cstatus = ConfidentialityStatus.RequiresClientApproval,
datumProjection = datumProjection.NONE,
managerId = manager1.Id,
operations = "Read;Update;Delete",
data_operations = "Read;Create;Update;Delete",
data = new List <ge_data>(),
users = new List <ge_user_ops>()
};
p1.users.Add(new ge_user_ops {
projectId = p1.Id,
userId = manager1.Id,
user_operations = "Create;Read;Update;Delete;Approve;Admin"
});
p1.users.Add(new ge_user_ops {
projectId = p1.Id,
userId = user1.Id,
user_operations = "Create;Read;Update;Delete"
});
var p2 = new ge_project {
Id = Guid.NewGuid(),
locName = "Lower Thames Crossing Package B",
locAddress = "",
locPostcode = "",
description = "Monitoring Data for Lower Thames Crossing Package B",
createdId = user1.Id,
createdDT = DateTime.Now,
editedId = user1.Id,
editedDT = DateTime.Now,
keywords = "",
start_date = new DateTime(2017, 11, 15),
pstatus = PublishStatus.Uncontrolled,
cstatus = ConfidentialityStatus.Owned,
datumProjection = datumProjection.NONE,
managerId = manager1.Id,
operations = "Read;Update;Delete",
data_operations = "Read;Update;Delete",
data = new List <ge_data>(),
users = new List <ge_user_ops>()
};
p2.users.Add(new ge_user_ops {
projectId = p1.Id,
userId = manager1.Id,
user_operations = "Create;Read;Update;Delete;Approve;Admin"
});
p2.users.Add(new ge_user_ops {
projectId = p1.Id,
userId = user1.Id,
user_operations = "Create;Read;Update;Delete"
});
var p3 = new ge_project {
Id = Guid.NewGuid(),
locName = "Lower Thames Crossing Package C",
locAddress = "",
locPostcode = "",
description = "Monitoring Data for Lower Thames Crossing Package C",
createdId = user1.Id,
createdDT = DateTime.Now,
editedId = user1.Id,
editedDT = DateTime.Now,
keywords = "",
start_date = new DateTime(2017, 11, 15),
pstatus = PublishStatus.Uncontrolled,
cstatus = ConfidentialityStatus.Owned,
datumProjection = datumProjection.NONE,
managerId = manager1.Id,
operations = "Read;Update;Delete",
data_operations = "Read;Update;Delete",
data = new List <ge_data>(),
users = new List <ge_user_ops>()
};
p3.users.Add(new ge_user_ops {
projectId = p3.Id,
userId = manager1.Id,
user_operations = "Create;Read;Update;Delete;Approve;Admin"
});
p3.users.Add(new ge_user_ops {
projectId = p3.Id,
userId = user1.Id,
user_operations = "Create;Read;Update;Delete"
});
string folder = @"\\eu.aecomnet.com\euprojectvol\UKCRD1-TI\Projects\14\geotech1\Projects\Lower Thames Crossing\Package C\02 WIP\LTM Data Round0";
AddFile(p3, folder + "\\" + "Logger_Data 2019-11-14 to 2019-12-16.csv", user1);
AddFile(p3, folder + "\\" + "Package B Round0 Long Term Monitoring data 2020-01-31.csv", user1);
var p4 = new ge_project {
Id = Guid.NewGuid(),
locName = "Lower Thames Crossing Package D",
locAddress = "",
locPostcode = "",
description = "Monitoring Data for Lower Thames Crossing Package D",
createdId = user1.Id,
createdDT = DateTime.Now,
editedId = user1.Id,
editedDT = DateTime.Now,
keywords = "",
start_date = new DateTime(2017, 11, 15),
pstatus = PublishStatus.Uncontrolled,
cstatus = ConfidentialityStatus.Owned,
datumProjection = datumProjection.NONE,
managerId = manager1.Id,
operations = "Read;Update;Delete",
data_operations = "Read;Update;Delete",
data = new List <ge_data>(),
users = new List <ge_user_ops>()
};
p4.users.Add(new ge_user_ops {
projectId = p4.Id,
userId = manager1.Id,
user_operations = "Create;Read;Update;Delete;Approve;Admin"
});
p4.users.Add(new ge_user_ops {
projectId = p4.Id,
userId = user1.Id,
user_operations = "Create;Read;Update;Delete"
});
#endregion
g1.projects.Add(p1);
g1.projects.Add(p2);
g1.projects.Add(p3);
g1.projects.Add(p4);
context.ge_group.Add(g1);
context.SaveChanges();
}
public LogUnitOfWork(dbConnectDetails connect)
{
_connect = connect;
_connection = new SqlConnection(connect.AsConnectionString());
}
public async Task <IActionResult> AddData(Guid id)
{
if (id == null)
{
return(NotFound());
}
var _data = await _context.ge_data
.Include(d => d.project)
.SingleOrDefaultAsync(m => m.Id == id);
if (_data == null)
{
return(NotFound());
}
var user = GetUserAsync().Result;
if (user != null)
{
int IsDownloadAllowed = _context.IsOperationAllowed(Constants.DownloadOperationName, _data.project, _data);
Boolean CanUserDownload = _context.DoesUserHaveOperation(Constants.DownloadOperationName, _data.project, user.Id);
int IsCreateAllowed = _context.IsOperationAllowed(Constants.CreateOperationName, _data.project, _data);
Boolean CanUserCreate = _context.DoesUserHaveOperation(Constants.CreateOperationName, _data.project, user.Id);
if (IsDownloadAllowed != geOPSResp.Allowed)
{
return(RedirectToPageMessage(msgCODE.DATA_DOWNLOAD_PROHIBITED));
}
if (!CanUserDownload)
{
return(RedirectToPageMessage(msgCODE.DATA_DOWNLOAD_USER_PROHIBITED));
}
if (IsCreateAllowed != geOPSResp.Allowed)
{
return(RedirectToPageMessage(msgCODE.DATA_CREATE_PROHIBITED));
}
if (!CanUserCreate)
{
return(RedirectToPageMessage(msgCODE.DATA_CREATE_USER_PROHIBITED));
}
}
var _data_big = await _context.ge_data_file.SingleOrDefaultAsync(m => m.Id == id);
if (_data_big == null)
{
return(NotFound());
}
var ContentType = _data.filetype;
var filename = _data.filename;
var encode = _data.GetEncoding();
MemoryStream memory = _data_big.getMemoryStream(encode);
string[] lines = Encoding.ASCII.GetString(memory.ToArray()).
Split(new string[] { Environment.NewLine }, StringSplitOptions.None);
dataLayout dl = getDataLayoutName(lines[0]);
if (dl == null)
{
return(NotFound());
}
dbConnectDetails cd = await getConnectDetails(_data.projectId, gINTTables.DB_DATA_TYPE);
if (cd == null)
{
return(null);
}
string dbConnectStr = cd.AsConnectionString();
cnn = new SqlConnection(dbConnectStr);
cnn.Open();
MOND.setConnection(cnn);
MONG.setConnection(cnn);
if (dl == dataLayout.DataDipper101)
{
addDipperKeys_v101(dict_measurement);
addDipperLayout101(_data, lines);
}
if (dl == dataLayout.DataLogger101)
{
addLoggerKeys_v101(dict_measurement);
addLoggerLayout101(_data, lines);
}
cnn.Close();
return(Ok());
}
public async Task <IActionResult> CreateAGS(Guid id, DateTime fromDate, DateTime toDate, string version = "4.04", string format = "view", Boolean save = false)
{
if (id == null)
{
return(NotFound());
}
var _project = await _context.ge_project
.Include(p => p.group)
.SingleOrDefaultAsync(m => m.Id == id);
if (_project == null)
{
return(NotFound());
}
dbConnectDetails cd = await getConnectDetails(id, gINTTables.DB_DATA_TYPE);
if (cd == null)
{
return(null);
}
int?_gINTProjectID = cd.ProjectId;
if (_gINTProjectID == null)
{
return(NotFound());
}
var _data = new ge_data();
_data.project = _project;
var user = GetUserAsync();
if (user == null)
{
return(RedirectToPageMessage(msgCODE.USER_NOTFOUND));
}
String userId = user.Result.Id;
int IsCreateAllowed = _context.IsOperationAllowed(Constants.CreateOperationName, _project, _data);
Boolean CanUserCreate = _context.DoesUserHaveOperation(Constants.CreateOperationName, _project, userId);
if (IsCreateAllowed != geOPSResp.Allowed)
{
return(RedirectToPageMessage(msgCODE.DATA_CREATE_PROHIBITED));
}
if (!CanUserCreate)
{
return(RedirectToPageMessage(msgCODE.DATA_CREATE_USER_PROHIBITED));
}
string dbConnectStr = cd.AsConnectionString();
cnn = new SqlConnection(dbConnectStr);
cnn.Open();
MOND.setConnection(cnn);
MOND.sqlWhere("gINTProjectID=" + _gINTProjectID + " and DateTime>='" + fromDate.ToString(DATE_FORMAT) + "' and DateTime<='" + toDate.ToString(DATE_FORMAT) + "'");
MOND.getDataTable();
String s1 = getAGS404Table(MOND);
_data = new ge_data {
Id = Guid.NewGuid(),
createdId = userId,
createdDT = DateTime.Now,
editedDT = DateTime.Now,
editedId = userId,
filename = "AGS MOND Table " + fromDate.ToString(FILE_NAME_DATE_FORMAT) + " to " + toDate.ToString(FILE_NAME_DATE_FORMAT) + ".ags",
filesize = s1.Length,
fileext = ".ags",
filetype = "text/plain",
filedate = DateTime.Now,
encoding = "ascii",
datumProjection = datumProjection.NONE,
pstatus = PublishStatus.Uncontrolled,
cstatus = ConfidentialityStatus.RequiresClientApproval,
version = "P01.1",
vstatus = VersionStatus.Intermediate,
qstatus = QualitativeStatus.AECOMFactual,
description = _project.name + " AGS MOND table data (" + fromDate.ToString(FILE_NAME_DATE_FORMAT) + " to " + toDate.ToString(FILE_NAME_DATE_FORMAT) + ")",
operations = "Read;Download;Update;Delete",
file = new ge_data_file {
data_string = s1
}
};
if (save)
{
_project.data = new List <ge_data>();
_project.data.Add(_data);
_context.SaveChanges();
}
byte[] ags = Encoding.ASCII.GetBytes(s1);
if (format == "download")
{
return(File(ags, "text/plain", _data.filename));
}
if (format == null || format == "view")
{
return(File(ags, "text/plain"));
}
return(NotFound());
}
private async Task <int> UpdateFile(ge_log_file file, Boolean IncludeReadings)
{
int NOT_OK = -1;
int ret = 0;
if (file.dataId == null)
{
return(NOT_OK);
}
file.packFieldHeaders();
file.packFileHeader();
var _logger = await _context.ge_data
.Include(d => d.project)
.SingleOrDefaultAsync(m => m.Id == file.dataId);
dbConnectDetails cd = await getConnectDetails(_logger.projectId, logTables.DB_DATA_TYPE);
if (cd == null)
{
return(NOT_OK);
}
string dbConnectStr = cd.AsConnectionString();
return(await Task.Run(() =>
{
using (SqlConnection cnn = new SqlConnection(dbConnectStr))
{
dsTable <ge_log_reading> ds_readings = new logTables().reading;
dsTable <ge_log_file> ds_file = new logTables().file;
cnn.Open();
ds_file.setConnection(cnn);
ds_file.getDataTable();
ds_readings.setConnection(cnn);
ds_readings.getDataTable();
ds_file.sqlWhere("Id='" + file.Id + "'");
ds_file.getDataSet();
DataTable dt_file = ds_file.getDataTable();
if (dt_file == null)
{
return NOT_OK;
}
if (dt_file.Rows.Count == 0)
{
return NOT_OK;
}
DataRow file_row = dt_file.Rows[0];
set_log_file_values(file, file_row);
ret = ds_file.Update();
if (IncludeReadings)
{
ds_readings.sqlWhere("FileId='" + file.Id.ToString() + "'");
ds_readings.getDataSet();
DataTable dt_readings = ds_readings.getDataTable();
Boolean checkExisting = false;
if (dt_readings.Rows.Count > 0)
{
checkExisting = true;
}
foreach (ge_log_reading reading in file.readings)
{
DataRow row = null;
if (checkExisting == true)
{
if (reading.Id != Guid.Empty)
{
row = dt_readings.Select($"Id='{reading.Id}'").SingleOrDefault();
}
if (row == null)
{
row = dt_readings.Select($"ReadingDateTime='{String.Format("{0:yyyy-MM-dd HH:mm:ss.ffff}",reading.ReadingDateTime)}'").SingleOrDefault();
}
}
if (row == null)
{
row = ds_readings.NewRow();
reading.Id = Guid.NewGuid();
reading.fileId = file.Id;
ds_readings.addRow(row);
}
else
{
reading.Id = (Guid)row["Id"];
reading.fileId = file.Id;
}
set_log_reading_values(reading, row);
}
//what if there are other records (more) in dt_readings from a previous version of the ge_log_file?
// mark for deletion all records not 'new' or 'updated'
if (file.readings.Count() < dt_readings.Rows.Count)
{
foreach (DataRow row in dt_readings.Rows)
{
if (row.RowState == DataRowState.Added |
row.RowState != DataRowState.Modified)
{
row.Delete();
}
}
}
ret = ret + ds_readings.Update();
return ret;
}
return ret;
}
}));
}
private async Task <ge_log_file> GetFile(Guid dataId,
string table = "data_pressure",
Boolean IncludeReadings = true)
{
if (dataId == null)
{
return(null);
}
var _logger = await _context.ge_data
.Include(d => d.project)
.AsNoTracking()
.SingleOrDefaultAsync(m => m.Id == dataId);
dbConnectDetails cd = await getConnectDetails(_logger.projectId, logTables.DB_DATA_TYPE);
if (cd == null)
{
return(null);
}
string dbConnectStr = cd.AsConnectionString();
return(await Task.Run(() =>
{
using (SqlConnection cnn = new SqlConnection(dbConnectStr))
{
cnn.Open();
dsTable <ge_log_reading> ds_readings = new logTables().reading;
dsTable <ge_log_file> ds_file = new logTables().file;
ds_file.setConnection(cnn);
ds_readings.setConnection(cnn);
//Multichannel transducer have upto 8 tables which will all have the same dataId
if (string.IsNullOrEmpty(table))
{
ds_file.sqlWhere("dataId='" + dataId.ToString() + "' and (channel is null or channel='')");
}
else
{
ds_file.sqlWhere("dataId='" + dataId.ToString() + "' and channel='" + table + "'");
}
ds_file.getDataSet();
DataTable dt_file = ds_file.getDataTable();
if (dt_file == null)
{
return null;
}
if (dt_file.Rows.Count == 0)
{
return null;
}
ge_log_file file = new ge_log_file();
DataRow row = dt_file.Rows[0];
get_log_file_values(row, file);
if (IncludeReadings)
{
ds_readings.sqlWhere("FileId='" + file.Id.ToString() + "'");
ds_readings.getDataSet();
DataTable dt_readings = ds_readings.getDataTable();
file.readings = new List <ge_log_reading>();
foreach (DataRow rrow in dt_readings.Rows)
{
ge_log_reading r = new ge_log_reading();
get_log_reading_values(rrow, r);
file.readings.Add(r);
}
file.OrderReadings();
}
file.unpack_exist_file();
return file;
}
}));
}
private async Task <int> AddNewFile(ge_log_file file)
{
int NOT_OK = -1;
int ret = 0;
if (file == null)
{
return(NOT_OK);
}
file.packFieldHeaders();
file.packFileHeader();
var _data = await _context.ge_data
.Include(d => d.project)
.SingleOrDefaultAsync(m => m.Id == file.dataId);
dbConnectDetails cd = await getConnectDetails(_data.projectId, logTables.DB_DATA_TYPE);
if (cd == null)
{
return(NOT_OK);
}
string dbConnectStr = cd.AsConnectionString();
return(await Task.Run(() =>
{
using (SqlConnection cnn = new SqlConnection(dbConnectStr))
{
dsTable <ge_log_reading> ds_readings = new logTables().reading;
dsTable <ge_log_file> ds_file = new logTables().file;
cnn.Open();
ds_file.setConnection(cnn);
ds_file.Reset();
ds_readings.setConnection(cnn);
ds_readings.Reset();
DataTable dt_file = ds_file.getDataTable();
DataRow file_row = dt_file.NewRow();
file.Id = Guid.NewGuid();
set_log_file_values(file, file_row);
ds_file.addRow(file_row);
ret = ds_file.Update();
DataTable dt_readings = ds_readings.getDataTable();
foreach (ge_log_reading reading in file.readings)
{
DataRow row = dt_readings.NewRow();
reading.Id = Guid.NewGuid();
reading.fileId = file.Id;
set_log_reading_values(reading, row);
ds_readings.addRow(row);
}
ret = ret + ds_readings.Update();
return ret;
}
}));
}
