protected static RowColmnPair GetFirstMatchedCellByRow(Range excelRange, List <string> searchTerms, int statRowIndex, int maxRows, int startColumnIndex, int endColumnIndex)
{
RowColmnPair firstIndicatorCell = null;
for (var rowIndex = statRowIndex; rowIndex <= maxRows; rowIndex++)
{
for (var colIndex = endColumnIndex; colIndex >= startColumnIndex; colIndex--)
{
var value = getCellValue(excelRange, rowIndex, colIndex);
if (string.IsNullOrWhiteSpace(value))
{
continue;
}
if (searchTerms.Contains(value))
{
firstIndicatorCell = new RowColmnPair()
{
Column = colIndex,
Row = rowIndex
};
break;
}
}
if (firstIndicatorCell != null)
{
break;
}
}
return(firstIndicatorCell);
}
protected static Dictionary <string, RowColmnPair> GetCellsInColumnContaining(Range excelRange, int columnIndex, List <string> searchTerms,
int startRowIndex, int maxRows)
{
var indicatorCells = new Dictionary <string, RowColmnPair>();
for (var rowIndex = startRowIndex; rowIndex <= maxRows; rowIndex++)
{
var value = getCellValue(excelRange, rowIndex, columnIndex);
if (string.IsNullOrWhiteSpace(value))
{
continue;
}
if (searchTerms.Contains(value))
{
indicatorCells[value] = new RowColmnPair()
{
Column = columnIndex, Row = rowIndex
};
}
}
return(indicatorCells);
}
protected static Dictionary <string, List <RowColmnPair> > GetMatchedCellsInRow(Range excelRange, List <string> searchTerms,
int rowIndex, int startColumnIndex, int endColumnIndex)
{
var alternateAgeGroups = PageController.Instance.AlternateAgegroups;
//we convert our search terms to standard
var standardSearchTerms = new Dictionary <string, string>();
foreach (var ageDisagg in searchTerms)
{
var stdAgeDisagg = string.Empty;
if (!alternateAgeGroups.TryGetValue(ageDisagg.toCleanAge(), out stdAgeDisagg))
{
//we throw exception as our master dictionary does not have thids
throw new ArgumentOutOfRangeException("Database does not have this alternate age disaggregation " + ageDisagg);
}
standardSearchTerms.Add(stdAgeDisagg, ageDisagg);
}
var toReturn = new Dictionary <string, List <RowColmnPair> >();
for (var colmnId = startColumnIndex; colmnId <= endColumnIndex; colmnId++)
{
var value = getCellValue(excelRange, rowIndex, colmnId);
//people might have other columns, so we pick what we want
if (string.IsNullOrWhiteSpace(value) || value.Length > 40)
{
//we try getting the row before as header might be merged
if (rowIndex == 1)
{
continue;
}
value = getCellValue(excelRange, rowIndex - 1, colmnId);
if (string.IsNullOrWhiteSpace(value) || value.Length > 40)
{
continue;
}
}
//we convert the value read to standard term and skip if not in alternate age groups
//Skipping because it could be other fields contained in the sheet
var cleanedValue = value.toCleanAge();
if (alternateAgeGroups.ContainsKey(cleanedValue))
{
var stdExlAgeGrp = alternateAgeGroups[cleanedValue];
if (!standardSearchTerms.ContainsKey(stdExlAgeGrp))
{
//we only want the age disaggregations that match our field dictionary
continue;
}
//we get all locations for our desired row
if (!toReturn.ContainsKey(stdExlAgeGrp))
{
toReturn[stdExlAgeGrp] = new List <RowColmnPair>();
}
var list = toReturn[stdExlAgeGrp];
var rowColmnPair = new RowColmnPair()
{
Column = colmnId, Row = rowIndex
};
rowColmnPair.Index = list.Count + 1;
list.Add(rowColmnPair);
}
else
{
//perhaps we log that we skipped this field
}
}
//we do some extra validations
//we check if all our search terms are matched
var searchTermsUnmatched = new List <string>();
foreach (var searchTerm in standardSearchTerms)
{
if (!toReturn.ContainsKey(searchTerm.Key))
{
searchTermsUnmatched.Add(searchTerm.Key);
}
}
if (searchTermsUnmatched.Count > 0)
{
//convert to string
var asString = string.Join(",", searchTermsUnmatched);
throw new ArgumentOutOfRangeException("Could not find equivalent Age Disaggregations for the following: " + asString);
}
return(toReturn);
}
protected DataValue getCellValue(ProgramAreaDefinition dataElement, string indicatorId, Range xlrange, KeyValuePair <string, RowColmnPair> rowObject, KeyValuePair <string, List <RowColmnPair> > indicatorAgeGroupCells, RowColmnPair indicatorAgeGroupCell)
{
var value = getCellValue(xlrange, rowObject.Value.Row, indicatorAgeGroupCell.Column, Constants.NULLVALUE);
if (value == Constants.NULLVALUE)
{
return(null);
}
var knownProblematicValues =
new List <string>()
{
"iud", "jaddel", "hiv negative", "hiv positive", "hiv status unknown"
};
//value can be Constants.NULLVALUE for cells that are merged and return a null
if (knownProblematicValues.Contains(value.ToLowerInvariant()))
{
//we continue
//jaddel is FP4 Male Total and IUD is FP4 Female Total
//HIV Negative
//HIV Positive
//HIV Status Unknown
return(null);
}
var asDouble = 0d;
try
{
asDouble = value.ToDouble();
//if (asDouble == 0)
// return null;
if (asDouble == -2146826273 || asDouble == -2146826281)
{
ShowErrorAndAbort(value, rowObject.Key, dataElement.ProgramArea, rowObject.Value.Row, indicatorAgeGroupCell.Column);
//return null;
}
}
catch
{
ShowErrorAndAbort(value, rowObject.Key, dataElement.ProgramArea, rowObject.Value.Row, indicatorAgeGroupCell.Column);
//return null;
}
if (asDouble == Constants.NOVALUE)
{
return(null);
}
var sex = dataElement.Gender;
if (dataElement.Gender == "both")
{
if (indicatorAgeGroupCell.Index == 1)
{
sex = "Male";
}
else
{
sex = "Female";
}
}
var dataValue = new DataValue()
{
IndicatorValue = asDouble,
IndicatorId = indicatorId, //rowObject.Key,
ProgramArea = dataElement.ProgramArea,
AgeGroup = indicatorAgeGroupCells.Key,
Sex = sex,
};
return(dataValue);
}
