private static void GetChecksInfo(ChecksFileInfo fileInfo, RandomizedSubjecsChecksCompletedRowsModel model) { //copy file into memory stream var ms = new MemoryStream(); using (var fs = File.OpenRead(fileInfo.FullName)) { fs.CopyTo(ms); } var rangeNames = GetDefinedNames(ms); DateTime?dtLastVal = null; var row = 2; using (SpreadsheetDocument document = SpreadsheetDocument.Open(ms, false)) { while (true) { if (rangeNames.ContainsKey(TimeAccepted)) { var rangeVal = rangeNames[TimeAccepted]; var colName = GetRangeNameCol(rangeVal); var colRow = colName + row; var cellVal = GetCellValue(document.WorkbookPart, WorkSheet, colRow); if (string.IsNullOrEmpty(cellVal)) { model.CksLastDate = dtLastVal; model.CksRowsCompleted = row - 2; break; } var dbl = Double.Parse(cellVal); var dt = DateTime.FromOADate(dbl); if (row == 2) { model.CksFirstDate = dt; } dtLastVal = dt; row++; } else { break; } } } }
private static List <RandomizedSubjecsChecksCompletedRowsModel> GetListFromDb(int siteId) { var list = new List <RandomizedSubjecsChecksCompletedRowsModel>(); SqlDataReader rdr = null; var strConn = ConfigurationManager.ConnectionStrings["Halfpint"].ToString(); using (var conn = new SqlConnection(strConn)) { try { var cmd = new SqlCommand("", conn) { CommandType = CommandType.StoredProcedure, CommandText = ("GetRandomizedSubjectsChecksCompletedRows") }; var param = new SqlParameter("@siteId", siteId); cmd.Parameters.Add(param); conn.Open(); rdr = cmd.ExecuteReader(); while (rdr.Read()) { var model = new RandomizedSubjecsChecksCompletedRowsModel(); var pos = rdr.GetOrdinal("ID"); model.StudyId = rdr.GetInt32(pos); pos = rdr.GetOrdinal("StudyID"); model.SubjectId = rdr.GetString(pos).Trim(); pos = rdr.GetOrdinal("DateRandomized"); model.DateRandomized = rdr.GetDateTime(pos); pos = rdr.GetOrdinal("DateCompleted"); model.ScDateCompleted = rdr.GetDateTime(pos); pos = rdr.GetOrdinal("ChecksImportCompleted"); if (!rdr.IsDBNull(pos)) { model.ScChecksImportCompleted = rdr.GetBoolean(pos); } pos = rdr.GetOrdinal("ChecksLastRowImported"); if (!rdr.IsDBNull(pos)) { model.ScChecksLastRowImported = rdr.GetInt32(pos); } pos = rdr.GetOrdinal("ChecksRowsCompleted"); if (!rdr.IsDBNull(pos)) { model.ScChecksRowsCompleted = rdr.GetInt32(pos); } pos = rdr.GetOrdinal("FirstCgmDate"); if (!rdr.IsDBNull(pos)) { model.CgmFirstDate = rdr.GetDateTime(pos); } pos = rdr.GetOrdinal("LastCgmDate"); if (!rdr.IsDBNull(pos)) { model.CgmLastDate = rdr.GetDateTime(pos); } pos = rdr.GetOrdinal("ChecksDbRows"); model.DbRows = rdr.GetInt32(pos); list.Add(model); } rdr.Close(); } catch (Exception ex) { Nlogger.LogError(ex); } finally { if (rdr != null) { rdr.Close(); } } } return(list); }