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