private static DPRTDData populatedDPRData(ExcelWorksheet ws, int rowindex, int cntColumn)
{
try
{
int rowcount = GetRowCount(ws, 0);
DPRTDData obj = new DPRTDData();
for (int i = 0; i < 2; i++)
{
if (ws.Columns[i].Cells[rowindex].Value != null && ws.Columns[i].Cells[rowindex].Value.ToString() == "Units (M/F)")
{
obj.Units = ws.Rows[rowindex].Cells[1].Value.ToString();
}
if (ws.Columns[i].Cells[rowindex + 1].Value != null && ws.Columns[i].Cells[rowindex + 1].Value.ToString() == "Well name")
{
obj.WellName = ws.Rows[rowindex + 1].Cells[1].Value.ToString();
}
if (obj.WellName != ws.Columns[i].Cells[rowindex + 1].Value.ToString())
{
obj = populatedData(ws, i, rowindex, rowcount, cntColumn, obj);
}
}
return(obj);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw ex;
}
}
private static void CheckDaysDesc(DPRTDData obj, int rowindex)
{
ErrorMessage errorObj = new ErrorMessage();
ValidationError objerror = new ValidationError();
int? dayFirst = obj.NoOfDay.First();
int? dayLast = obj.NoOfDay.Last();
try
{
for (int i = 0; i < obj.NoOfDay.Count - 1; i++)
{
if (!(obj.NoOfDay[i] >= dayFirst && obj.NoOfDay[i] <= dayLast && obj.NoOfDay[i + 1] > obj.NoOfDay[i]))
{
// Console.WriteLine(errorObj.DaysDec);
objerror.WellName = obj.WellName;
objerror.ErrorMessage = errorObj.DaysDec;
objerror.Row = i + rowindex + 3;
obj.Errors.Add(objerror);
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw ex;
}
}
private static void CheckDaysSeq(DPRTDData obj, int rowindex)
{
ErrorMessage errorObj = new ErrorMessage();
ValidationError objerror = new ValidationError();
int? diff = obj.NoOfDay[1] - obj.NoOfDay[0];
try
{
for (int i = 0; i < obj.NoOfDay.Count - 1; i++)
{
if (!(obj.NoOfDay[i + 1] > obj.NoOfDay[i] && obj.NoOfDay[i + 1] - obj.NoOfDay[i] == diff))
{
// Console.WriteLine(errorObj.DaysSeq);
objerror.WellName = obj.WellName;
objerror.ErrorMessage = errorObj.DaysSeq;
objerror.Row = i + rowindex + 3;
obj.Errors.Add(objerror);
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw ex;
}
}
private static void CheckDepthsSpike(DPRTDData obj, int rowindex)
{
ErrorMessage errorObj = new ErrorMessage();
ValidationError objerror = new ValidationError();
try
{
for (int j = 0; j < obj.Actualdepth.Count - 1; j++)
{
if (obj.Actualdepth[j].Value <(obj.Actualdepth[j + 1].Value * 0.2) || obj.Actualdepth[j + 1].Value> (obj.Actualdepth[j].Value * 5))
{
//Console.WriteLine(errorObj.DepthsSpike);
objerror.WellName = obj.WellName;
objerror.ErrorMessage = errorObj.DepthsSpike;
objerror.Row = j + rowindex + 3;
obj.Errors.Add(objerror);
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw ex;
}
}
private static void CheckDUplicate(DPRTDData obj, int rowindex)
{
ErrorMessage errorObj = new ErrorMessage();
ValidationError objerror = new ValidationError();
var duplicates = obj.NoOfDay
.GroupBy(i => i)
.Where(g => g.Count() > 1)
.Select(g => g.Key);
if (duplicates.Count() > 0)
{
Console.WriteLine(errorObj.DaysRep);
objerror.WellName = obj.WellName;
objerror.ErrorMessage = errorObj.DaysRep;
obj.Errors.Add(objerror);
}
}
public void ParsingDPRTDData()
{
List <DPRTDData> objlist = new List <DPRTDData>();
ExcelFile exf = new ExcelFile();
int cntColumn = 0;
int rowcount = 0;
int rowindex = 0;
try
{
List <DPRFull> objFull = AssignDataInput();
exf.LoadXls(@"C:\Rushmore\Studies\Data\DPR 11\DPR Africa 11\Raw Data\Submissions\Cabinda Gulf Angola S1.xls");
ExcelWorksheet ws = exf.Worksheets["Time-depth data"];
if (ws.Cells["A2"].Value.ToString() == "Drilling Performance Review" && ws.Cells["A8"].Value.ToString() == "Units (M/F)")
{
cntColumn = ws.CalculateMaxUsedColumns();
rowindex = GetRowIndex(ws);
if (cntColumn >= 3)
{
DPRTDData obj = populatedDPRData(ws, rowindex, cntColumn);
ValidateTDDPRColumns(obj, objFull, rowindex);
objlist.Add(obj);
for (int i = 3; i < cntColumn; i++)
{
if (cntColumn - i > 2)
{
rowcount = GetRowCount(ws, i);
rowcount = rowcount + 2;
DPRTDData obj1 = new DPRTDData();
if (ws.Columns[i + 1].Cells[rowindex].Value != null)
{
obj1.Units = ws.Columns[i + 1].Cells[rowindex].Value.ToString();
}
if (ws.Columns[i + 1].Cells[rowindex + 1].Value != null)
{
obj1.WellName = ws.Columns[i + 1].Cells[rowindex + 1].Value.ToString();
}
if (obj1.Units != null)
{
obj1 = populatedData(ws, i, rowindex, rowcount, cntColumn, obj1);
ValidateTDDPRColumns(obj1, objFull, rowindex);
objlist.Add(obj1);
}
}
else
{
break;
}
}
}
else
{
DPRTDData obj = populatedDPRData(ws, rowindex, cntColumn);
}
}
ValidateDPRFullData(objlist, objFull, rowindex);
}
catch (Exception ex)
{
throw ex;
//Console.WriteLine(ex);
}
}
private static DPRTDData populatedData(ExcelWorksheet ws, int i, int rowindex, int rowcount, int cntColumn, DPRTDData obj)
{
try
{
ErrorMessage errorObj = new ErrorMessage();
int colCnt = i + 2;
for (int m = rowindex; m < rowcount; m++)
{
if (ws.Columns[i].Cells[m].Value != null && ws.Columns[i].Cells[m].Value.ToString() == "Day")
{
int cnt = m;
for (int p = 0; p < rowcount - 3; p++)
{
int day = 0;
if ((ws.Rows[cnt + 1].Cells[0].Value != null) && int.TryParse(ws.Rows[cnt + 1].Cells[0].Value.ToString(), out day))
{
obj.NoOfDay.Add(day);
}
else
{
obj.NoOfDay.Add((int?)null);
ValidationError objerror = new ValidationError();
objerror.ErrorMessage = errorObj.NonNumericDays;
objerror.WellName = obj.WellName;
objerror.Row = rowindex + 3 + p;
obj.Errors.Add(objerror);
}
cnt++;
}
}
}
for (int n = i; n <= colCnt; n++)
{
for (int p = rowindex; p < rowcount; p++)
{
if (ws.Columns[n].Cells[p].Value != null && ws.Columns[n].Cells[p].Value.ToString() == "Actual depth")
{
int cnt = p;
for (int q = 0; q < rowcount - 3; q++)
{
double depth = 0;
if ((ws.Columns[n].Cells[cnt + 1].Value != null) && double.TryParse(ws.Columns[n].Cells[cnt + 1].Value.ToString(), out depth))
{
obj.Actualdepth.Add(depth);
}
else
{
obj.Actualdepth.Add((double?)null);
ValidationError objerror = new ValidationError();
objerror.ErrorMessage = errorObj.NonNumericDepths;
objerror.WellName = obj.WellName;
objerror.Row = rowindex + 3 + q;
obj.Errors.Add(objerror);
}
cnt++;
}
}
if (ws.Columns[n].Cells[p].Value != null && ws.Columns[n].Cells[p].Value.ToString().Contains("Hole size"))
{
int cnt = p;
for (int q = 0; q < rowcount - 3; q++)
{
double size = 0;
if ((ws.Columns[n].Cells[cnt + 1].Value != null) && double.TryParse(ws.Columns[n].Cells[cnt + 1].Value.ToString(), out size))
{
obj.HoleSize.Add(size);
}
else
{
obj.HoleSize.Add((double?)null);
ValidationError objerror = new ValidationError();
objerror.ErrorMessage = errorObj.NonNumericHS;
objerror.WellName = obj.WellName;
objerror.Row = rowindex + 3 + q;
obj.Errors.Add(objerror);
}
cnt++;
}
}
}
}
return(obj);
}
catch (Exception ex)
{
throw ex;
}
}
private static void ValidateTDDPRColumns(DPRTDData obj, List <DPRFull> objDPRList, int rowindex)
{
ErrorMessage errorObj = new ErrorMessage();
ValidationError objerror = new ValidationError();
double?mtd1 = 0;
double?mtd2 = 0;
try
{
for (int j = 0; j < obj.NoOfDay.Count; j++)
{
if (obj.NoOfDay[j] == null)
{
//Console.WriteLine(errorObj.MDays);
objerror.WellName = obj.WellName;
objerror.ErrorMessage = errorObj.MDays;
objerror.Row = j + rowindex + 3;
obj.Errors.Add(objerror);
}
if (obj.Actualdepth[j] == null)
{
// Console.WriteLine(errorObj.MDepths);
objerror.WellName = obj.WellName;
objerror.ErrorMessage = errorObj.MDepths;
objerror.Row = j + rowindex + 3;
obj.Errors.Add(objerror);
}
if (obj.HoleSize[j] == null)
{
//Console.WriteLine(errorObj.MHole);
objerror.WellName = obj.WellName;
objerror.ErrorMessage = errorObj.MHole;
objerror.Row = j + rowindex + 3;
obj.Errors.Add(objerror);
}
}
CheckDUplicate(obj, rowindex);
CheckDepthsSpike(obj, rowindex);
CheckDaysSeq(obj, rowindex);
CheckDaysDesc(obj, rowindex);
for (int i = 0; i < objDPRList.Count; i++)
{
DPRFull objFull = objDPRList[i];
if (obj.WellName == objFull.FormalWellName)
{
mtd1 = objFull.Mtd + (objFull.Mtd * 0.01);
mtd2 = objFull.Mtd - (objFull.Mtd * 0.01);
if (!obj.Actualdepth.Contains(objFull.Mtd))
{
//Console.WriteLine(errorObj.MTDMissing);
objerror.WellName = obj.WellName;
objerror.Row = i + rowindex + 3;
objerror.ErrorMessage = errorObj.MTDMissing;
obj.Errors.Add(objerror);
}
double?max = obj.Actualdepth.Max();
if (!(max < mtd1 && max > mtd2))
{
//Console.WriteLine(errorObj.MTDLargest);
objerror.WellName = obj.WellName;
objerror.Row = i + rowindex + 3;
objerror.ErrorMessage = errorObj.MTDLargest;
obj.Errors.Add(objerror);
}
if (obj.HoleSize.Last() != objFull.FinalDrillBitSize)
{
//Console.WriteLine(errorObj.HSDrill);
objerror.WellName = obj.WellName;
objerror.Row = i + rowindex + 3;
objerror.ErrorMessage = errorObj.HSDrill;
obj.Errors.Add(objerror);
}
for (int j = 0; j < obj.NoOfDay.Count; j++)
{
if (obj.Actualdepth[j] < mtd1 && obj.Actualdepth[j] > mtd2)
{
obj.DepthFlag = true;
}
else
{
obj.DepthFlag = false;
}
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw ex;
}
}
