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; } })); }